PROCEDURE create_operation (p_create_operation VARCHAR2)
IS
l_operations_tbl apps.gmd_operations%ROWTYPE;
l_oprn_activities_tbl_type apps.gmd_operations_pub.gmd_oprn_activities_tbl_type;
l_oprn_resources_tbl_type apps.gmd_operation_resources_pub.gmd_oprn_resources_tbl_type;
l_count NUMBER;
l_msg_ind VARCHAR2 (50);
l_return_status VARCHAR2 (1);
l_msg_count NUMBER;
l_msg_data VARCHAR2 (2000);
ln_oprn_line_id NUMBER;
ln_operation_id NUMBER;
ln_err_count NUMBER;
ln_suc_rec_cnt NUMBER := 0;
ln_rej_rec_cnt NUMBER := 0;
ln_progress NUMBER := 0;
ln_organization_id NUMBER;
ln_count NUMBER;
lc_rsrc_process_uom VARCHAR2 (20);
lc_oper_class VARCHAR2 (20);
lc_process_usage_uom VARCHAR2 (7);
lc_resource_process_uom VARCHAR2 (7);
lc_process_qty_uom VARCHAR2 (15);
lc_activity VARCHAR2 (25);
lc_resources VARCHAR2 (25);
--===============================
--Cursor to get new records count
--===============================
CURSOR lcu_count
IS
SELECT COUNT (*)
FROM xxblr_opm_routing_stg
WHERE 1=1
AND record_status = gc_validation_flag;
--==================================
--Cursor to get Valid Operation data
--==================================
CURSOR lcu_oprn_data
IS
SELECT xors.new_oprn_no oprn_no
, xors.oprn_vers
, xors.owner_org_code
, SUBSTR(xors.oprn_desc,1,40) oprn_desc
, xors.oprn_class_desc
, xors.oprn_class
, gd_valid_date oper_valid_from
, xors.process_output_qty
, xors.process_qty_uom
, xors.organization_id
, min(transaction_id) min_txn_id
FROM xxblr_opm_routing_stg xors
WHERE 1=1
AND (new_oprn_no, oprn_vers) IN (
SELECT new_oprn_no
, oprn_vers
FROM
(
( SELECT new_oprn_no
, oprn_vers
FROM xxblr_opm_routing_stg
WHERE 1=1
AND NVL (record_status, 'N') = gc_validation_flag
GROUP BY new_oprn_no
, oprn_vers
)
MINUS
( SELECT new_oprn_no
, oprn_vers
FROM xxblr_opm_routing_stg
WHERE 1=1
AND ( NVL (record_status, 'N') = gc_newrecord_flag
OR NVL (record_status, 'N') = gc_validation_error_flag
OR NVL (record_status, 'N') = gc_error_flag
OR NVL (record_status, 'N') = gc_success_flag
OR NVL (record_status, 'N') = gc_item_error_flag
)
GROUP BY new_oprn_no
, oprn_vers
)
)
)
GROUP BY xors.new_oprn_no
, xors.oprn_vers
, xors.owner_org_code
, xors.oprn_desc
, xors.oprn_class_desc
, xors.oprn_class
--, xors.oper_valid_from
, xors.process_output_qty
, xors.process_qty_uom
, xors.organization_id
ORDER BY min_txn_id;
--=================================
--Cursor to get Valid Activity data
--=================================
CURSOR lcu_act_data (p_oprn_no VARCHAR2,p_oprn_vers NUMBER)
IS
SELECT new_oprn_no oprn_no
, activity
, oprn_vers
, min(transaction_id) min_txn_id
FROM xxblr_opm_routing_stg
WHERE 1=1
AND NVL(record_status, 'N') = gc_validation_flag
AND UPPER(new_oprn_no) = UPPER (p_oprn_no)
AND UPPER(oprn_vers) = UPPER (p_oprn_vers)
GROUP BY new_oprn_no, activity,oprn_vers
ORDER BY min_txn_id;
--==================================
--Cursor to get Valid Resource data
--==================================
CURSOR lcu_rsrc_data (p_oprn_no VARCHAR2, p_oprn_vers NUMBER)
IS
SELECT new_oprn_no oprn_no
, activity
, oprn_vers
, resources
, resource_code
, resource_count
, resource_usage
, usage_uom
, scale_type
, component_class
, owner_org_code
, process_output_qty
, process_qty_uom
, cost_analysis_code
, DECODE(plan_type,'Primary',1,DECODE(plan_type,'Auxiliary',2,NULL)) plan_type
, offset_interval
FROM xxblr_opm_routing_stg
WHERE 1=1
AND NVL(record_status, 'N') = gc_validation_flag
AND new_oprn_no = p_oprn_no
AND oprn_vers = p_oprn_vers
ORDER BY transaction_id;
--==================================
--Cursor to get Activity data
--==================================
CURSOR lcu_act (p_activity VARCHAR2)
IS
SELECT activity
FROM apps.gmd_activities_vl
WHERE 1=1
AND UPPER (activity) = UPPER (p_activity);
TYPE tbl_operation_data IS TABLE OF lcu_oprn_data%ROWTYPE INDEX BY BINARY_INTEGER;
lt_oprn_data tbl_operation_data;
l_msg VARCHAR2(2000);
BEGIN
apps.fnd_file.put_line (apps.fnd_file.LOG, RPAD ('*', 80, '*'));
apps.fnd_file.put_line(apps.fnd_file.LOG,' --------- Executing CREATE_OPERATION ---------');
apps.fnd_file.put_line (apps.fnd_file.LOG, RPAD (' ', 80, ' '));
apps.fnd_file.put_line (apps.fnd_file.output, RPAD ('*', 80, '*'));
apps.fnd_file.put_line(apps.fnd_file.output,' --------- Executing CREATE_OPERATION ---------');
apps.fnd_file.put_line (apps.fnd_file.output, RPAD (' ', 80, ' '));
OPEN lcu_count;
FETCH lcu_count
INTO ln_count;
CLOSE lcu_count;
apps.fnd_file.put_line (apps.fnd_file.LOG,'Number of Records Loaded :' || ln_count);
-------------------------------------------------------------------------
-- Bulk Insert Data into Operation.
--------------------------------------------------------------------------
BEGIN
OPEN lcu_oprn_data;
LOOP
lt_oprn_data.DELETE;
gn_bulk_err := 0;
FETCH lcu_oprn_data BULK COLLECT INTO lt_oprn_data LIMIT 50000;
IF lt_oprn_data.COUNT > 0
THEN
apps.fnd_file.put_line (apps.fnd_file.LOG,'OPERATION COUNT-->' || lt_oprn_data.COUNT);
apps.fnd_file.put_line (apps.fnd_file.LOG, 'OPRN TRACK 1.0');
l_count := 0;
BEGIN
FOR i IN 1 .. lt_oprn_data.COUNT
LOOP
l_count := l_count + 1;
apps.fnd_file.put_line (apps.fnd_file.LOG, 'TRACK 2.0 - OPRN DATA ASSIGNING - START: '||lt_oprn_data (i).oprn_no);
l_operations_tbl.oprn_no := lt_oprn_data (i).oprn_no;
l_operations_tbl.oprn_vers := lt_oprn_data (i).oprn_vers;
l_operations_tbl.process_qty_uom := lt_oprn_data (i).process_qty_uom;
l_operations_tbl.oprn_class := lt_oprn_data (i).oprn_class;
l_operations_tbl.poc_ctl_class := NULL;
l_operations_tbl.in_use := NULL;
l_operations_tbl.inactive_ind := 0;
l_operations_tbl.delete_mark := 1;
l_operations_tbl.creation_date := gd_sysdate;
l_operations_tbl.created_by := l_user_id;
l_operations_tbl.last_updated_by := l_user_id;
l_operations_tbl.last_update_date := gd_sysdate;
l_operations_tbl.last_update_login := l_user_id;
l_operations_tbl.effective_start_date := to_date(lt_oprn_data (i).oper_valid_from);
l_operations_tbl.operation_status := gn_status;
l_operations_tbl.owner_organization_id := lt_oprn_data (i).organization_id;
l_operations_tbl.oprn_desc := lt_oprn_data (i).oprn_desc;
apps.fnd_file.put_line (apps.fnd_file.LOG, 'TRACK 2.1 - OPRN DATA(LOOP) ASSIGNED - END');
l_oprn_activities_tbl_type.DELETE;
ln_count := 0;
apps.fnd_file.put_line (apps.fnd_file.LOG, 'TRACK 3.0 --> ACTVTY DATA LOOP - START: ' || ln_count);
FOR lt_act_data IN lcu_act_data(lt_oprn_data(i).oprn_no,lt_oprn_data(i).oprn_vers)
LOOP
ln_count := ln_count + 1;
-- apps.fnd_file.put_line (apps.fnd_file.LOG, 'TRACK 3.1 --> IN ACTVTY DATA LOOP ' ||lt_act_data.activity);
l_oprn_activities_tbl_type (ln_count).activity := lt_act_data.activity;
l_oprn_activities_tbl_type (ln_count).offset_interval := 0;
l_oprn_activities_tbl_type (ln_count).activity_factor := 1;
l_oprn_activities_tbl_type (ln_count).delete_mark := 0;
l_oprn_activities_tbl_type (ln_count).text_code := NULL;
l_oprn_activities_tbl_type (ln_count).creation_date := gd_sysdate;
l_oprn_activities_tbl_type (ln_count).created_by := l_user_id;
l_oprn_activities_tbl_type (ln_count).last_update_date := gd_sysdate;
l_oprn_activities_tbl_type (ln_count).last_update_login := l_user_id;
l_oprn_activities_tbl_type (ln_count).sequence_dependent_ind := 0;
l_oprn_activities_tbl_type (ln_count).break_ind := 0;
l_oprn_activities_tbl_type (ln_count).max_break := NULL;
l_oprn_activities_tbl_type (ln_count).material_ind := 0;
END LOOP;
apps.fnd_file.put_line (apps.fnd_file.LOG, 'TRACK 3.2 --> ACTVTY DATA LOOP - END: ' || ln_count);
l_oprn_resources_tbl_type.DELETE;
l_count := 0;
apps.fnd_file.put_line (apps.fnd_file.LOG, 'TRACK 5.0 --> RESOURCE DATA LOOP - START:' || ln_count || ':' || l_count);
FOR lt_rsrc_data IN lcu_rsrc_data(lt_oprn_data (i).oprn_no,lt_oprn_data (i).oprn_vers)
LOOP
l_count := l_count + 1;
-- apps.fnd_file.put_line (apps.fnd_file.LOG, 'TRACK 5.0 --> IN RESOURCE DATA LOOP:' ||lt_rsrc_data.resource_code);
l_oprn_resources_tbl_type (l_count).oprn_line_id := NULL; --ln_oprn_line_id;
l_oprn_resources_tbl_type (l_count).resources := lt_rsrc_data.resource_code;
l_oprn_resources_tbl_type (l_count).resource_usage := lt_rsrc_data.resource_usage;
l_oprn_resources_tbl_type (l_count).resource_count := lt_rsrc_data.resource_count;
l_oprn_resources_tbl_type (l_count).resource_usage_uom := NULL;--lc_resource_process_uom;
l_oprn_resources_tbl_type (l_count).process_qty := lt_rsrc_data.process_output_qty;
l_oprn_resources_tbl_type (l_count).resource_process_uom := lt_rsrc_data.process_qty_uom;
l_oprn_resources_tbl_type (l_count).prim_rsrc_ind := lt_rsrc_data.plan_type;
l_oprn_resources_tbl_type (l_count).scale_type := lt_rsrc_data.scale_type;
l_oprn_resources_tbl_type (l_count).cost_analysis_code := lt_rsrc_data.cost_analysis_code;
l_oprn_resources_tbl_type (l_count).cost_cmpntcls_id := NULL;
l_oprn_resources_tbl_type (l_count).offset_interval := 0; --lt_rsrc_data.offset_interval; -- * ---
l_oprn_resources_tbl_type (l_count).min_capacity := NULL;
l_oprn_resources_tbl_type (l_count).max_capacity := NULL;
l_oprn_resources_tbl_type (l_count).resource_capacity_uom:= NULL;
l_oprn_resources_tbl_type (l_count).attribute_category := NULL;
l_oprn_resources_tbl_type (l_count).activity := lt_rsrc_data.activity;
END LOOP;
apps.fnd_file.put_line (apps.fnd_file.LOG, 'TRACK 5.0 --> RESOURCE DATA LOOP - END:' || ln_count || ':' || l_count);
BEGIN
apps.fnd_file.put_line (apps.fnd_file.LOG, 'CALLING API - ' || lt_oprn_data (i).oprn_no);
-- apps.fnd_file.put_line (apps.fnd_file.LOG, 'TRACK 5.0 --> ACTIVITY COUNT:' ||l_oprn_activities_tbl_type.COUNT);
-- apps.fnd_file.put_line (apps.fnd_file.LOG, 'TRACK 5.0 --> RESOURCE COUNT:' ||l_oprn_resources_tbl_type.COUNT);
apps.gmd_operations_pub.insert_operation
( p_api_version => '1.0'
, p_init_msg_list => TRUE
, p_commit => FALSE
, p_operations => l_operations_tbl
, p_oprn_actv_tbl => l_oprn_activities_tbl_type
, x_message_count => l_msg_count
, x_message_list => l_msg_data
, x_return_status => l_return_status
, p_oprn_rsrc_tbl => l_oprn_resources_tbl_type
);
EXCEPTION
WHEN OTHERS THEN
apps.fnd_file.put_line(apps.fnd_file.LOG,'OPRN INSERT API RAISED exception: '|| SQLCODE|| '-'|| SQLERRM);
apps.fnd_file.put_line(apps.fnd_file.LOG,'TRACK 13.2.A'|| l_return_status);
apps.fnd_file.put_line(apps.fnd_file.LOG,'TRACK 13.2.B data:'|| l_msg_data);
apps.fnd_file.put_line(apps.fnd_file.LOG,'TRACK 13.2.C:' || l_msg_count);
l_return_status := 'U';
END;
apps.fnd_file.put_line(apps.fnd_file.LOG,'OPRN INSERT API : ' || l_return_status || ' :'|| l_msg_data || ' :' || l_msg_count);
IF l_return_status <> 'S' THEN
ln_rej_rec_cnt := ln_rej_rec_cnt + 1;
l_msg := '';
FOR i IN 1 .. l_msg_count
LOOP
l_msg := SUBSTR(l_msg || ' | ' || SUBSTR(apps.fnd_msg_pub.get(l_msg_count - i + 1, 'F'), 0, 200), 1, 2000);
END LOOP;
l_msg_data := l_msg;
apps.fnd_file.put_line(apps.fnd_file.LOG,'Error in OPRN INSERT API : ' || l_msg);
ELSE
ln_suc_rec_cnt := ln_suc_rec_cnt + 1;
END IF;
BEGIN
UPDATE xxblr_opm_routing_stg
SET record_status = l_return_status
, error_code = l_msg_count
, error_message = l_msg_data
WHERE new_oprn_no = lt_oprn_data(i).oprn_no
AND oprn_vers = lt_oprn_data(i).oprn_vers;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
apps.fnd_file.put_line(apps.fnd_file.LOG,'Error occured in staging table updation:'||sqlcode||sqlerrm);
END;
END LOOP;
EXCEPTION
WHEN OTHERS THEN
apps.fnd_file.put_line (apps.fnd_file.LOG,'ERROR ON OPRN DATA :> ' || SQLERRM || ', ' || SQLCODE);
END;
END IF;
apps.fnd_file.put_line(apps.fnd_file.LOG, ' ');
apps.fnd_file.put_line(apps.fnd_file.LOG,' +----------------------------------------------------------');
apps.fnd_file.put_line(apps.fnd_file.LOG,' | Total records processed : '|| ln_suc_rec_cnt);
apps.fnd_file.put_line(apps.fnd_file.LOG,' | Total records rejected : '|| ln_rej_rec_cnt);
apps.fnd_file.put_line(apps.fnd_file.LOG,' +----------------------------------------------------------+');
apps.fnd_file.put_line(apps.fnd_file.LOG, ' ');
apps.fnd_file.put_line(apps.fnd_file.LOG, 'TRACK 16');
EXIT WHEN lcu_oprn_data%NOTFOUND;
END LOOP;
CLOSE lcu_oprn_data;
EXCEPTION
WHEN OTHERS THEN
apps.fnd_file.put_line (apps.fnd_file.LOG,'MAIN-ERR-CRT-OPRN-CURR :> ' || SQLERRM || ', ' || SQLCODE);
END;
EXCEPTION
WHEN OTHERS
THEN
apps.fnd_file.put_line (apps.fnd_file.LOG,'MAIN-ERR-CRT-OPRN :> ' || SQLERRM || ', ' || SQLCODE);
apps.fnd_file.put_line(apps.fnd_file.LOG,' --------- Procedure CREATE_OPERATION Exit ---------');
apps.fnd_file.put_line (apps.fnd_file.LOG, RPAD (' ', 80, ' '));
apps.fnd_file.put_line (apps.fnd_file.LOG, RPAD ('*', 80, '*'));
END create_operation;
IS
l_operations_tbl apps.gmd_operations%ROWTYPE;
l_oprn_activities_tbl_type apps.gmd_operations_pub.gmd_oprn_activities_tbl_type;
l_oprn_resources_tbl_type apps.gmd_operation_resources_pub.gmd_oprn_resources_tbl_type;
l_count NUMBER;
l_msg_ind VARCHAR2 (50);
l_return_status VARCHAR2 (1);
l_msg_count NUMBER;
l_msg_data VARCHAR2 (2000);
ln_oprn_line_id NUMBER;
ln_operation_id NUMBER;
ln_err_count NUMBER;
ln_suc_rec_cnt NUMBER := 0;
ln_rej_rec_cnt NUMBER := 0;
ln_progress NUMBER := 0;
ln_organization_id NUMBER;
ln_count NUMBER;
lc_rsrc_process_uom VARCHAR2 (20);
lc_oper_class VARCHAR2 (20);
lc_process_usage_uom VARCHAR2 (7);
lc_resource_process_uom VARCHAR2 (7);
lc_process_qty_uom VARCHAR2 (15);
lc_activity VARCHAR2 (25);
lc_resources VARCHAR2 (25);
--===============================
--Cursor to get new records count
--===============================
CURSOR lcu_count
IS
SELECT COUNT (*)
FROM xxblr_opm_routing_stg
WHERE 1=1
AND record_status = gc_validation_flag;
--==================================
--Cursor to get Valid Operation data
--==================================
CURSOR lcu_oprn_data
IS
SELECT xors.new_oprn_no oprn_no
, xors.oprn_vers
, xors.owner_org_code
, SUBSTR(xors.oprn_desc,1,40) oprn_desc
, xors.oprn_class_desc
, xors.oprn_class
, gd_valid_date oper_valid_from
, xors.process_output_qty
, xors.process_qty_uom
, xors.organization_id
, min(transaction_id) min_txn_id
FROM xxblr_opm_routing_stg xors
WHERE 1=1
AND (new_oprn_no, oprn_vers) IN (
SELECT new_oprn_no
, oprn_vers
FROM
(
( SELECT new_oprn_no
, oprn_vers
FROM xxblr_opm_routing_stg
WHERE 1=1
AND NVL (record_status, 'N') = gc_validation_flag
GROUP BY new_oprn_no
, oprn_vers
)
MINUS
( SELECT new_oprn_no
, oprn_vers
FROM xxblr_opm_routing_stg
WHERE 1=1
AND ( NVL (record_status, 'N') = gc_newrecord_flag
OR NVL (record_status, 'N') = gc_validation_error_flag
OR NVL (record_status, 'N') = gc_error_flag
OR NVL (record_status, 'N') = gc_success_flag
OR NVL (record_status, 'N') = gc_item_error_flag
)
GROUP BY new_oprn_no
, oprn_vers
)
)
)
GROUP BY xors.new_oprn_no
, xors.oprn_vers
, xors.owner_org_code
, xors.oprn_desc
, xors.oprn_class_desc
, xors.oprn_class
--, xors.oper_valid_from
, xors.process_output_qty
, xors.process_qty_uom
, xors.organization_id
ORDER BY min_txn_id;
--=================================
--Cursor to get Valid Activity data
--=================================
CURSOR lcu_act_data (p_oprn_no VARCHAR2,p_oprn_vers NUMBER)
IS
SELECT new_oprn_no oprn_no
, activity
, oprn_vers
, min(transaction_id) min_txn_id
FROM xxblr_opm_routing_stg
WHERE 1=1
AND NVL(record_status, 'N') = gc_validation_flag
AND UPPER(new_oprn_no) = UPPER (p_oprn_no)
AND UPPER(oprn_vers) = UPPER (p_oprn_vers)
GROUP BY new_oprn_no, activity,oprn_vers
ORDER BY min_txn_id;
--==================================
--Cursor to get Valid Resource data
--==================================
CURSOR lcu_rsrc_data (p_oprn_no VARCHAR2, p_oprn_vers NUMBER)
IS
SELECT new_oprn_no oprn_no
, activity
, oprn_vers
, resources
, resource_code
, resource_count
, resource_usage
, usage_uom
, scale_type
, component_class
, owner_org_code
, process_output_qty
, process_qty_uom
, cost_analysis_code
, DECODE(plan_type,'Primary',1,DECODE(plan_type,'Auxiliary',2,NULL)) plan_type
, offset_interval
FROM xxblr_opm_routing_stg
WHERE 1=1
AND NVL(record_status, 'N') = gc_validation_flag
AND new_oprn_no = p_oprn_no
AND oprn_vers = p_oprn_vers
ORDER BY transaction_id;
--==================================
--Cursor to get Activity data
--==================================
CURSOR lcu_act (p_activity VARCHAR2)
IS
SELECT activity
FROM apps.gmd_activities_vl
WHERE 1=1
AND UPPER (activity) = UPPER (p_activity);
TYPE tbl_operation_data IS TABLE OF lcu_oprn_data%ROWTYPE INDEX BY BINARY_INTEGER;
lt_oprn_data tbl_operation_data;
l_msg VARCHAR2(2000);
BEGIN
apps.fnd_file.put_line (apps.fnd_file.LOG, RPAD ('*', 80, '*'));
apps.fnd_file.put_line(apps.fnd_file.LOG,' --------- Executing CREATE_OPERATION ---------');
apps.fnd_file.put_line (apps.fnd_file.LOG, RPAD (' ', 80, ' '));
apps.fnd_file.put_line (apps.fnd_file.output, RPAD ('*', 80, '*'));
apps.fnd_file.put_line(apps.fnd_file.output,' --------- Executing CREATE_OPERATION ---------');
apps.fnd_file.put_line (apps.fnd_file.output, RPAD (' ', 80, ' '));
OPEN lcu_count;
FETCH lcu_count
INTO ln_count;
CLOSE lcu_count;
apps.fnd_file.put_line (apps.fnd_file.LOG,'Number of Records Loaded :' || ln_count);
-------------------------------------------------------------------------
-- Bulk Insert Data into Operation.
--------------------------------------------------------------------------
BEGIN
OPEN lcu_oprn_data;
LOOP
lt_oprn_data.DELETE;
gn_bulk_err := 0;
FETCH lcu_oprn_data BULK COLLECT INTO lt_oprn_data LIMIT 50000;
IF lt_oprn_data.COUNT > 0
THEN
apps.fnd_file.put_line (apps.fnd_file.LOG,'OPERATION COUNT-->' || lt_oprn_data.COUNT);
apps.fnd_file.put_line (apps.fnd_file.LOG, 'OPRN TRACK 1.0');
l_count := 0;
BEGIN
FOR i IN 1 .. lt_oprn_data.COUNT
LOOP
l_count := l_count + 1;
apps.fnd_file.put_line (apps.fnd_file.LOG, 'TRACK 2.0 - OPRN DATA ASSIGNING - START: '||lt_oprn_data (i).oprn_no);
l_operations_tbl.oprn_no := lt_oprn_data (i).oprn_no;
l_operations_tbl.oprn_vers := lt_oprn_data (i).oprn_vers;
l_operations_tbl.process_qty_uom := lt_oprn_data (i).process_qty_uom;
l_operations_tbl.oprn_class := lt_oprn_data (i).oprn_class;
l_operations_tbl.poc_ctl_class := NULL;
l_operations_tbl.in_use := NULL;
l_operations_tbl.inactive_ind := 0;
l_operations_tbl.delete_mark := 1;
l_operations_tbl.creation_date := gd_sysdate;
l_operations_tbl.created_by := l_user_id;
l_operations_tbl.last_updated_by := l_user_id;
l_operations_tbl.last_update_date := gd_sysdate;
l_operations_tbl.last_update_login := l_user_id;
l_operations_tbl.effective_start_date := to_date(lt_oprn_data (i).oper_valid_from);
l_operations_tbl.operation_status := gn_status;
l_operations_tbl.owner_organization_id := lt_oprn_data (i).organization_id;
l_operations_tbl.oprn_desc := lt_oprn_data (i).oprn_desc;
apps.fnd_file.put_line (apps.fnd_file.LOG, 'TRACK 2.1 - OPRN DATA(LOOP) ASSIGNED - END');
l_oprn_activities_tbl_type.DELETE;
ln_count := 0;
apps.fnd_file.put_line (apps.fnd_file.LOG, 'TRACK 3.0 --> ACTVTY DATA LOOP - START: ' || ln_count);
FOR lt_act_data IN lcu_act_data(lt_oprn_data(i).oprn_no,lt_oprn_data(i).oprn_vers)
LOOP
ln_count := ln_count + 1;
-- apps.fnd_file.put_line (apps.fnd_file.LOG, 'TRACK 3.1 --> IN ACTVTY DATA LOOP ' ||lt_act_data.activity);
l_oprn_activities_tbl_type (ln_count).activity := lt_act_data.activity;
l_oprn_activities_tbl_type (ln_count).offset_interval := 0;
l_oprn_activities_tbl_type (ln_count).activity_factor := 1;
l_oprn_activities_tbl_type (ln_count).delete_mark := 0;
l_oprn_activities_tbl_type (ln_count).text_code := NULL;
l_oprn_activities_tbl_type (ln_count).creation_date := gd_sysdate;
l_oprn_activities_tbl_type (ln_count).created_by := l_user_id;
l_oprn_activities_tbl_type (ln_count).last_update_date := gd_sysdate;
l_oprn_activities_tbl_type (ln_count).last_update_login := l_user_id;
l_oprn_activities_tbl_type (ln_count).sequence_dependent_ind := 0;
l_oprn_activities_tbl_type (ln_count).break_ind := 0;
l_oprn_activities_tbl_type (ln_count).max_break := NULL;
l_oprn_activities_tbl_type (ln_count).material_ind := 0;
END LOOP;
apps.fnd_file.put_line (apps.fnd_file.LOG, 'TRACK 3.2 --> ACTVTY DATA LOOP - END: ' || ln_count);
l_oprn_resources_tbl_type.DELETE;
l_count := 0;
apps.fnd_file.put_line (apps.fnd_file.LOG, 'TRACK 5.0 --> RESOURCE DATA LOOP - START:' || ln_count || ':' || l_count);
FOR lt_rsrc_data IN lcu_rsrc_data(lt_oprn_data (i).oprn_no,lt_oprn_data (i).oprn_vers)
LOOP
l_count := l_count + 1;
-- apps.fnd_file.put_line (apps.fnd_file.LOG, 'TRACK 5.0 --> IN RESOURCE DATA LOOP:' ||lt_rsrc_data.resource_code);
l_oprn_resources_tbl_type (l_count).oprn_line_id := NULL; --ln_oprn_line_id;
l_oprn_resources_tbl_type (l_count).resources := lt_rsrc_data.resource_code;
l_oprn_resources_tbl_type (l_count).resource_usage := lt_rsrc_data.resource_usage;
l_oprn_resources_tbl_type (l_count).resource_count := lt_rsrc_data.resource_count;
l_oprn_resources_tbl_type (l_count).resource_usage_uom := NULL;--lc_resource_process_uom;
l_oprn_resources_tbl_type (l_count).process_qty := lt_rsrc_data.process_output_qty;
l_oprn_resources_tbl_type (l_count).resource_process_uom := lt_rsrc_data.process_qty_uom;
l_oprn_resources_tbl_type (l_count).prim_rsrc_ind := lt_rsrc_data.plan_type;
l_oprn_resources_tbl_type (l_count).scale_type := lt_rsrc_data.scale_type;
l_oprn_resources_tbl_type (l_count).cost_analysis_code := lt_rsrc_data.cost_analysis_code;
l_oprn_resources_tbl_type (l_count).cost_cmpntcls_id := NULL;
l_oprn_resources_tbl_type (l_count).offset_interval := 0; --lt_rsrc_data.offset_interval; -- * ---
l_oprn_resources_tbl_type (l_count).min_capacity := NULL;
l_oprn_resources_tbl_type (l_count).max_capacity := NULL;
l_oprn_resources_tbl_type (l_count).resource_capacity_uom:= NULL;
l_oprn_resources_tbl_type (l_count).attribute_category := NULL;
l_oprn_resources_tbl_type (l_count).activity := lt_rsrc_data.activity;
END LOOP;
apps.fnd_file.put_line (apps.fnd_file.LOG, 'TRACK 5.0 --> RESOURCE DATA LOOP - END:' || ln_count || ':' || l_count);
BEGIN
apps.fnd_file.put_line (apps.fnd_file.LOG, 'CALLING API - ' || lt_oprn_data (i).oprn_no);
-- apps.fnd_file.put_line (apps.fnd_file.LOG, 'TRACK 5.0 --> ACTIVITY COUNT:' ||l_oprn_activities_tbl_type.COUNT);
-- apps.fnd_file.put_line (apps.fnd_file.LOG, 'TRACK 5.0 --> RESOURCE COUNT:' ||l_oprn_resources_tbl_type.COUNT);
apps.gmd_operations_pub.insert_operation
( p_api_version => '1.0'
, p_init_msg_list => TRUE
, p_commit => FALSE
, p_operations => l_operations_tbl
, p_oprn_actv_tbl => l_oprn_activities_tbl_type
, x_message_count => l_msg_count
, x_message_list => l_msg_data
, x_return_status => l_return_status
, p_oprn_rsrc_tbl => l_oprn_resources_tbl_type
);
EXCEPTION
WHEN OTHERS THEN
apps.fnd_file.put_line(apps.fnd_file.LOG,'OPRN INSERT API RAISED exception: '|| SQLCODE|| '-'|| SQLERRM);
apps.fnd_file.put_line(apps.fnd_file.LOG,'TRACK 13.2.A'|| l_return_status);
apps.fnd_file.put_line(apps.fnd_file.LOG,'TRACK 13.2.B data:'|| l_msg_data);
apps.fnd_file.put_line(apps.fnd_file.LOG,'TRACK 13.2.C:' || l_msg_count);
l_return_status := 'U';
END;
apps.fnd_file.put_line(apps.fnd_file.LOG,'OPRN INSERT API : ' || l_return_status || ' :'|| l_msg_data || ' :' || l_msg_count);
IF l_return_status <> 'S' THEN
ln_rej_rec_cnt := ln_rej_rec_cnt + 1;
l_msg := '';
FOR i IN 1 .. l_msg_count
LOOP
l_msg := SUBSTR(l_msg || ' | ' || SUBSTR(apps.fnd_msg_pub.get(l_msg_count - i + 1, 'F'), 0, 200), 1, 2000);
END LOOP;
l_msg_data := l_msg;
apps.fnd_file.put_line(apps.fnd_file.LOG,'Error in OPRN INSERT API : ' || l_msg);
ELSE
ln_suc_rec_cnt := ln_suc_rec_cnt + 1;
END IF;
BEGIN
UPDATE xxblr_opm_routing_stg
SET record_status = l_return_status
, error_code = l_msg_count
, error_message = l_msg_data
WHERE new_oprn_no = lt_oprn_data(i).oprn_no
AND oprn_vers = lt_oprn_data(i).oprn_vers;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
apps.fnd_file.put_line(apps.fnd_file.LOG,'Error occured in staging table updation:'||sqlcode||sqlerrm);
END;
END LOOP;
EXCEPTION
WHEN OTHERS THEN
apps.fnd_file.put_line (apps.fnd_file.LOG,'ERROR ON OPRN DATA :> ' || SQLERRM || ', ' || SQLCODE);
END;
END IF;
apps.fnd_file.put_line(apps.fnd_file.LOG, ' ');
apps.fnd_file.put_line(apps.fnd_file.LOG,' +----------------------------------------------------------');
apps.fnd_file.put_line(apps.fnd_file.LOG,' | Total records processed : '|| ln_suc_rec_cnt);
apps.fnd_file.put_line(apps.fnd_file.LOG,' | Total records rejected : '|| ln_rej_rec_cnt);
apps.fnd_file.put_line(apps.fnd_file.LOG,' +----------------------------------------------------------+');
apps.fnd_file.put_line(apps.fnd_file.LOG, ' ');
apps.fnd_file.put_line(apps.fnd_file.LOG, 'TRACK 16');
EXIT WHEN lcu_oprn_data%NOTFOUND;
END LOOP;
CLOSE lcu_oprn_data;
EXCEPTION
WHEN OTHERS THEN
apps.fnd_file.put_line (apps.fnd_file.LOG,'MAIN-ERR-CRT-OPRN-CURR :> ' || SQLERRM || ', ' || SQLCODE);
END;
EXCEPTION
WHEN OTHERS
THEN
apps.fnd_file.put_line (apps.fnd_file.LOG,'MAIN-ERR-CRT-OPRN :> ' || SQLERRM || ', ' || SQLCODE);
apps.fnd_file.put_line(apps.fnd_file.LOG,' --------- Procedure CREATE_OPERATION Exit ---------');
apps.fnd_file.put_line (apps.fnd_file.LOG, RPAD (' ', 80, ' '));
apps.fnd_file.put_line (apps.fnd_file.LOG, RPAD ('*', 80, '*'));
END create_operation;
No comments:
Post a Comment
Note: only a member of this blog may post a comment.