create Table :
CREATE TABLE APPS.XXJLN_OPM_OPERATIONS_CONV_STG
(
OPRN_NO VARCHAR2(160 CHAR),
OPRN_DESC VARCHAR2(240 CHAR),
STATUS VARCHAR2(230 CHAR),
OPRN_VERS NUMBER,
OPRN_CLASS VARCHAR2(214 CHAR),
D_EFFECTIVE_START_DATE DATE,
D_EFFECTIVE_END_DATE DATE,
OWNER_ORGN_CODE VARCHAR2(214 CHAR),
MINIMUM_TRANSFER_QTY NUMBER,
PROCESS_QTY_UOM VARCHAR2(214 CHAR),
ACTIVITY VARCHAR2(240 CHAR),
ACTIVITY_FACTOR NUMBER,
OFFSET_INTERVAL NUMBER,
BREAK_IND NUMBER,
MAX_BREAK NUMBER,
MATERIAL_IND NUMBER,
RESOURCES VARCHAR2(162 CHAR),
PROCESS_QTY NUMBER,
RESOURCE_PROCESS_UOM VARCHAR2(202 CHAR),
RESOURCE_USAGE NUMBER,
RESOURCE_USAGE_UOM VARCHAR2(142 CHAR),
CMPNT_CLASS VARCHAR2(162 CHAR),
COST_ANALYSIS_CODE VARCHAR2(142 CHAR),
PRIM_RSRC_IND VARCHAR2(262 CHAR),
RESOURCE_COUNT NUMBER,
RESOURCE_OFFSET_INTERVAL VARCHAR2(262 CHAR),
SCALE_TYPE VARCHAR2(802 CHAR),
REQ_SEQ NUMBER,
PROCESS_FLAG VARCHAR2(202 CHAR),
PROCESS_INSTRUCTION VARCHAR2(240 CHAR),
SEQUENCE_DEPENDENT_IND VARCHAR2(10 CHAR),
ERROR_MESSAGE VARCHAR2(4000 BYTE),
LAST_UPDATE_DATE DATE,
LAST_UPDATED_BY NUMBER,
CREATION_DATE DATE,
CREATED_BY NUMBER,
LAST_UPDATE_LOGIN NUMBER
);
SET DEFINE OFF;
Insert into XXJLN_OPM_OPERATIONS_CONV_STG
(OPRN_NO, OPRN_DESC, STATUS, OPRN_VERS, OPRN_CLASS,
D_EFFECTIVE_START_DATE, D_EFFECTIVE_END_DATE, OWNER_ORGN_CODE, MINIMUM_TRANSFER_QTY, PROCESS_QTY_UOM,
ACTIVITY, ACTIVITY_FACTOR, OFFSET_INTERVAL, BREAK_IND, MAX_BREAK,
MATERIAL_IND, RESOURCES, PROCESS_QTY, RESOURCE_PROCESS_UOM, RESOURCE_USAGE,
RESOURCE_USAGE_UOM, CMPNT_CLASS, COST_ANALYSIS_CODE, PRIM_RSRC_IND, RESOURCE_COUNT,
RESOURCE_OFFSET_INTERVAL, SCALE_TYPE, REQ_SEQ, PROCESS_FLAG, PROCESS_INSTRUCTION,
SEQUENCE_DEPENDENT_IND, ERROR_MESSAGE, LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE,
CREATED_BY, LAST_UPDATE_LOGIN)
Values
('1', 'MG5.PACKAGING Desc', NULL, 1, NULL,
NULL, NULL, 'FMO', 1, 'PCS',
'MG5.PACKAGING', 1, 0, NULL, NULL,
1, 'MG5.PACK_L', 1, 'PCS', 0,
NULL, 'LABOUR', 'VAL', NULL, 1,
'0', NULL, NULL, 'P', NULL,
NULL, NULL, NULL, NULL, NULL,
NULL, NULL);
COMMIT;
DECLARE
CURSOR cur_operations
IS
SELECT oprn_no, oprn_vers, oprn_desc, process_qty_uom,
d_effective_start_date, d_effective_end_date,
owner_orgn_code, status, process_instruction,
minimum_transfer_qty, oprn_class
FROM XXJLN_OPM_OPERATIONS_CONV_STG
WHERE process_flag is null
/*/(process_flag IN ('NEW', 'ERROR', 'VALIDATE')
AND (p_process_status = 'ALL')
OR process_flag = ('NEW') AND (p_process_status = 'NEW')
OR process_flag = ('ERROR') AND (p_process_status = 'ERROR')
)*/
ORDER BY oprn_no, oprn_vers;
-- This cursor extracts activities data related to operations from the staging table
--with operation no and version being IN parameters
CURSOR cur_operation_activities (
p_oprn_no IN VARCHAR2,
p_oprn_vers IN NUMBER
)
IS
SELECT oprn_no, oprn_vers, activity, NVL(offset_interval,0) offset_interval,
activity_factor, break_ind, max_break, material_ind,
process_instruction, minimum_transfer_qty, process_qty_uom,
DECODE (sequence_dependent_ind,
'Y', 1,
'N', 0,
0
) sequence_dependent_ind
FROM XXJLN_OPM_OPERATIONS_CONV_STG
WHERE oprn_no = p_oprn_no
AND oprn_vers = p_oprn_vers
AND ( process_flag is null
/* IN ('NEW', 'ERROR', 'VALIDATE')
AND (p_process_status = 'ALL')
OR process_flag = ('NEW') AND (p_process_status = 'NEW')
OR process_flag = ('ERROR')
AND (p_process_status = 'ERROR')
*/
)
ORDER BY activity, sequence_dependent_ind DESC;
-- This cursor extracts resources data related to operations from the staging table
--with operation no ,version and activity being IN parameters
CURSOR cur_operation_resources (
p_oprn_no IN VARCHAR2,
p_oprn_vers IN NUMBER,
p_oprn_activity IN VARCHAR2
)
IS
SELECT oprn_no, oprn_vers, UPPER (resources) resources,
resource_usage, resource_count, process_qty, prim_rsrc_ind,
scale_type, cost_analysis_code, cmpnt_class,
resource_offset_interval, resource_process_uom,
resource_usage_uom, process_instruction, minimum_transfer_qty,
process_qty_uom
FROM XXJLN_OPM_OPERATIONS_CONV_STG
WHERE oprn_no = p_oprn_no
AND oprn_vers = p_oprn_vers
AND activity = p_oprn_activity
AND ( process_flag is null
/*IN ('NEW', 'ERROR', 'VALIDATE')
AND (p_process_status = 'ALL')
OR process_flag = ('NEW') AND (p_process_status = 'NEW')
OR process_flag = ('ERROR') AND (p_process_status = 'ERROR')
*/
);
--------------------------------------------------------------------------------------------------------
-- Private Variable Declaration Section
--------------------------------------------------------------------------------------------------------
lv_operation_rec cur_operations%ROWTYPE;
lv_operations gmd_operations%ROWTYPE;
lv_user_name fnd_user.user_name%TYPE;
lv_oprn_no_previous gmd_operations_b.oprn_no%TYPE;
lv_oprn_vers_previous gmd_operations_b.oprn_vers%TYPE;
ln_header_id NUMBER;
lv_oprn_actv_previous gmd_operation_activities.activity%TYPE;
lv_oprn_actv_tbl gmd_operations_pub.gmd_oprn_activities_tbl_type;
lv_oprn_rsrc_tbl gmd_operation_resources_pub.gmd_oprn_resources_tbl_type;
ln_request_id NUMBER;
lv_error_flag VARCHAR2 (1) := 'N';
lv_msg_data VARCHAR2 (4000);
lv_return_status VARCHAR2 (1);
lv_error_message VARCHAR2 (3000);
lv_process_flag VARCHAR2 (1) := 'Y';
lv_status_code VARCHAR2 (30);
lv_msg_list VARCHAR2 (3000);
lv_ret_sts VARCHAR2 (30);
lv_return_sts BOOLEAN;
ln_user_id NUMBER;
ln_login_id NUMBER;
ln_msg_count NUMBER := 0;
ln_responsibility_app_id NUMBER;
ln_version NUMBER := 3;
ln_responsibility_id NUMBER := fnd_profile.VALUE ('RESP_ID');
ln_out_index NUMBER := 0;
ln_count NUMBER := 1;
ln_owner_organization_id NUMBER;
ln_prim_rsrc_ind NUMBER;
ln_scale_type NUMBER;
ln_cost_cmpntcls_id NUMBER;
ln_oprn_actv_counter NUMBER;
ln_oprn_rsrc_counter NUMBER;
ln_oprn_id NUMBER;
ln_msg_cnt NUMBER;
ln_return_value NUMBER := NULL;
--stores number returned by EMF API's
ln_proc_stg_recs NUMBER := 0;
ln_err_stg_recs NUMBER := 0;
ln_succ_stg_recs NUMBER := 0;
lv_error_cat_flag VARCHAR2 (1) := 'N';
--Flag to capture error (Y=ERROR/N=SUCCESS)
lv_warning_flag VARCHAR2 (1); --Flag to capture warning ('W')
lv_output_message VARCHAR2 (1000);
--stores the message returned from external routines
lv_level VARCHAR2 (1000);
l_conc_segment VARCHAR2 (1000);
lv_error_mesage VARCHAR2 (10000) := NULL;
cntrl_fail_exp EXCEPTION;
control_record_error_exp EXCEPTION;
BEGIN --Main Begin
fnd_file.put_line
(fnd_file.LOG,
'Staring the OPM Operations Conversion Program ...'
);
-- Initializing the basic variables
ln_request_id := fnd_global.conc_request_id;
ln_user_id := fnd_global.user_id;
ln_login_id := fnd_global.login_id;
lv_user_name := fnd_global.user_name;
fnd_file.put_line (fnd_file.LOG,'EMF initialized, header_id :' || TO_CHAR (ln_header_id));
----Begin for header validation
OPEN cur_operations;
FETCH cur_operations
INTO lv_operation_rec;
IF cur_operations%NOTFOUND
THEN
fnd_file.put_line (fnd_file.output,
'No more Operation Records to Process'
);
CLOSE cur_operations;
ELSE
CLOSE cur_operations;
FOR oprn_rec IN cur_operations
LOOP
fnd_file.put_line (fnd_file.LOG,
'Debug -> Fisrt Loop Starts for -- Operations -- : '
|| oprn_rec.oprn_no
|| ' '
|| oprn_rec.oprn_vers
|| ' '
|| oprn_rec.owner_orgn_code
);
IF ( (lv_oprn_no_previous = oprn_rec.oprn_no)
AND (lv_oprn_vers_previous = oprn_rec.oprn_vers)
)
THEN
NULL;
ELSE
fnd_file.put_line (fnd_file.LOG,
'Debug -> Before Variable initialization operations : '
);
lv_oprn_no_previous := oprn_rec.oprn_no;
lv_oprn_vers_previous := oprn_rec.oprn_vers;
lv_error_flag := 'N';
lv_return_sts := gmigutl.setup ('OPM');
fnd_global.apps_initialize (1187, --gn_user_id,
22882, --gn_resp_id,
552 -- gn_resp_app_id
);
BEGIN
------ Retrieve Organization ID
BEGIN
fnd_file.put_line (fnd_file.LOG,
'Debug -> Before getting org - owner_organization_id '
);
SELECT organization_id
INTO ln_owner_organization_id
FROM org_organization_definitions
WHERE organization_code = oprn_rec.owner_orgn_code;
fnd_file.put_line (fnd_file.LOG,
'Debug -> organization id '
|| ln_owner_organization_id
);
EXCEPTION
WHEN NO_DATA_FOUND
THEN
fnd_file.put_line
(fnd_file.LOG,
'XXAKG OPM OPERATIONS CONVERSION STG - 0001 '
|| ' '
|| 'Organization ID does not exist'
|| ' '
|| oprn_rec.oprn_no
|| ' '
|| oprn_rec.oprn_vers
);
lv_error_flag := 'Y';
lv_error_mesage := lv_error_mesage
|| '~~'
|| 'Organization ID does not exist';
WHEN OTHERS
THEN
fnd_file.put_line
(fnd_file.LOG,
'XXAKG OPM OPERATIONS CONVERSION STG - 0002 '
|| ' '
|| 'Error in Organization ID Derivation'
|| ' '
|| oprn_rec.oprn_no
|| ' '
|| oprn_rec.oprn_vers
);
lv_error_flag := 'Y';
lv_error_mesage := lv_error_mesage
|| '~~'
|| 'Error in Organization ID Derivation';
END;
fnd_file.put_line (fnd_file.LOG,
'Debug -> Before Var initialization '
);
-- This Operation Header Data
lv_operations.oprn_no := oprn_rec.oprn_no;
lv_operations.oprn_vers := oprn_rec.oprn_vers;
lv_operations.oprn_desc := oprn_rec.oprn_desc;
lv_operations.oprn_class := oprn_rec.oprn_class;
lv_operations.process_qty_uom := oprn_rec.process_qty_uom;
lv_operations.effective_start_date := oprn_rec.d_effective_start_date;
lv_operations.effective_end_date := oprn_rec.d_effective_end_date;
lv_operations.owner_organization_id := ln_owner_organization_id;
lv_operations.operation_status := oprn_rec.status;
lv_operations.minimum_transfer_qty := oprn_rec.minimum_transfer_qty;
lv_operations.creation_date := TO_DATE (SYSDATE, 'DD-MON-RR');
lv_operations.last_update_date := TO_DATE (SYSDATE, 'DD-MON-RR');
lv_operations.created_by := 1187; -- gn_user_id;
lv_operations.last_updated_by := 1187; --gn_user_id;
lv_operations.attribute1 := oprn_rec.process_instruction;
lv_operations.delete_mark := 0;
ln_oprn_actv_counter := 0;
lv_oprn_actv_tbl.DELETE;
lv_oprn_actv_previous := '';
lv_oprn_rsrc_tbl.DELETE;
ln_oprn_rsrc_counter := 0;
fnd_file.put_line (fnd_file.LOG,
'Debug -> After Var initialization '
);
FOR c_oprn_actv IN
cur_operation_activities (oprn_rec.oprn_no,
oprn_rec.oprn_vers
)
LOOP
fnd_file.put_line (fnd_file.LOG,
'Debug -> Second Loop starts for -- Activity --'
);
IF (c_oprn_actv.activity = lv_oprn_actv_previous)
THEN
NULL;
ELSE
/*fnd_file.put_line (fnd_file.LOG,
'Test Print 1 '
|| oprn_rec.oprn_no
|| ' '
|| oprn_rec.oprn_vers
);*/
lv_oprn_actv_previous := c_oprn_actv.activity;
/*fnd_file.put_line (fnd_file.LOG,
'Test Print 2 '
|| oprn_rec.oprn_no
|| ' '
|| oprn_rec.oprn_vers
);*/
ln_oprn_actv_counter := ln_oprn_actv_counter + 1;
-- Operations Activity Details
/*fnd_file.put_line (fnd_file.LOG,
'Test Print 3 '
|| oprn_rec.oprn_no
|| ' '
|| oprn_rec.oprn_vers
);*/
lv_oprn_actv_tbl (ln_oprn_actv_counter).activity := c_oprn_actv.activity;
/*fnd_file.put_line (fnd_file.LOG,
'Test Print 4 '
|| oprn_rec.oprn_no
|| ' '
|| oprn_rec.oprn_vers
);*/
lv_oprn_actv_tbl (ln_oprn_actv_counter).offset_interval := c_oprn_actv.offset_interval;
/*fnd_file.put_line (fnd_file.LOG,
'Test Print 5 '
|| oprn_rec.oprn_no
|| ' '
|| oprn_rec.oprn_vers
);*/
lv_oprn_actv_tbl (ln_oprn_actv_counter).activity_factor := c_oprn_actv.activity_factor;
/*fnd_file.put_line (fnd_file.LOG,
'Test Print 6 '
|| oprn_rec.oprn_no
|| ' '
|| oprn_rec.oprn_vers
);*/
lv_oprn_actv_tbl (ln_oprn_actv_counter).break_ind := c_oprn_actv.break_ind;
/*fnd_file.put_line (fnd_file.LOG,
'Test Print 7 '
|| oprn_rec.oprn_no
|| ' '
|| oprn_rec.oprn_vers
);*/
lv_oprn_actv_tbl (ln_oprn_actv_counter).max_break := c_oprn_actv.max_break;
/*fnd_file.put_line (fnd_file.LOG,
'Test Print 8 '
|| oprn_rec.oprn_no
|| ' '
|| oprn_rec.oprn_vers
);*/
lv_oprn_actv_tbl (ln_oprn_actv_counter).material_ind := c_oprn_actv.material_ind;
lv_oprn_actv_tbl (ln_oprn_actv_counter).sequence_dependent_ind := c_oprn_actv.sequence_dependent_ind;
/*fnd_file.put_line (fnd_file.LOG,
'Test Print 9 '
|| oprn_rec.oprn_no
|| ' '
|| oprn_rec.oprn_vers
);*/
FOR c_oprn_rsrc IN
cur_operation_resources (oprn_rec.oprn_no,
oprn_rec.oprn_vers,
c_oprn_actv.activity
)
LOOP
----- Retrieve COST_CMPNTCLS_ID for resources
BEGIN
SELECT cmb.cost_cmpntcls_id
INTO ln_cost_cmpntcls_id
FROM cm_cmpt_mst_b cmb,CR_RSRC_MST_VL crm
where cmb.COST_CMPNTCLS_ID = crm.COST_CMPNTCLS_ID
and crm.RESOURCES = UPPER (c_oprn_rsrc.resources);
/*
SELECT cost_cmpntcls_id
INTO ln_cost_cmpntcls_id
FROM cm_cmpt_mst_b
WHERE cost_cmpntcls_code = UPPER (c_oprn_rsrc.cmpnt_class);
*/
EXCEPTION
WHEN NO_DATA_FOUND
THEN
fnd_file.put_line
(fnd_file.LOG,
'XXAKG OPM OPERATIONS CONVERSION STG - 0003 '
|| ' '
-- || 'Component Class does not exist for OPERATIONS : '
|| 'RESOURCE does not exist for Component Class in OPERATIONS : '
|| ' '
|| c_oprn_rsrc.oprn_no
|| ' and OPERATIONS VERSION : '
|| c_oprn_rsrc.oprn_vers
);
lv_error_flag := 'Y';
lv_error_mesage := lv_error_mesage
|| '~~'
|| 'RESOURCE does not exist for Component Class';
WHEN OTHERS
THEN
fnd_file.put_line
(fnd_file.LOG,
'XXJLN OPM OPERATIONS CONVERSION STG - 0004 '
|| ' '
|| 'Error in RESOURCE does not exist for Component Class Retrieval for OPERATIONS :'
|| ' '
|| c_oprn_rsrc.oprn_no
|| ' and OPERATIONS VERSION : '
|| c_oprn_rsrc.oprn_vers
);
lv_error_flag := 'Y';
lv_error_mesage := lv_error_mesage
|| '~~'
|| 'Error in RESOURCE does not exist for Component Class Retrieval';
END;
----- Retrieve Scale Type for resources
BEGIN
SELECT TO_NUMBER (lookup_code)
INTO ln_scale_type
FROM fnd_lookup_values
WHERE lookup_type = 'SCALE_TYPE'
AND UPPER (meaning) =UPPER('Proportional')
-- UPPER (c_oprn_rsrc.scale_type)
AND LANGUAGE = USERENV ('LANG');
EXCEPTION
WHEN NO_DATA_FOUND
THEN
fnd_file.put_line
(fnd_file.LOG,
'XXJLN OPM OPERATIONS CONVERSION STG - 0005 '
|| ' '
|| 'Scale Type does not exist for OPERATIONS : '
|| ' '
|| c_oprn_rsrc.oprn_no
|| ' and OPERATIONS VERSION : '
|| c_oprn_rsrc.oprn_vers
);
lv_error_flag := 'Y';
lv_error_mesage := lv_error_mesage
|| '~~'
|| 'Scale Type does not exist';
WHEN OTHERS
THEN
fnd_file.put_line
(fnd_file.LOG,
'XXJLN OPM OPERATIONS CONVERSION STG - 0006 '
|| ' '
|| 'Error in Scale Type Retrieval for OPERATIONS : '
|| ' '
|| c_oprn_rsrc.oprn_no
|| ' and OPERATIONS VERSION : '
|| c_oprn_rsrc.oprn_vers
);
lv_error_flag := 'Y';
lv_error_mesage := lv_error_mesage
|| '~~'
|| 'Error in Scale Type Retrieval';
END;
----- Retrieve PRIM_RSRC_IND for resources
BEGIN
SELECT TO_NUMBER (lookup_code)
INTO ln_prim_rsrc_ind
FROM fnd_lookup_values
WHERE lookup_type = 'GMD_PRIM_RSRC_IND'
AND meaning = NVL (c_oprn_rsrc.prim_rsrc_ind,'Primary')
AND LANGUAGE = USERENV ('LANG');
/*
SELECT TO_NUMBER (lookup_code)
INTO ln_prim_rsrc_ind
FROM fnd_lookup_values
WHERE lookup_type = 'GMD_PRIM_RSRC_IND'
AND meaning = c_oprn_rsrc.prim_rsrc_ind
AND LANGUAGE = USERENV ('LANG');
*/
EXCEPTION
WHEN NO_DATA_FOUND
THEN
fnd_file.put_line
(fnd_file.LOG,
'XXJLN OPM OPERATIONS CONVERSION STG - 0007 '
|| ' '
|| 'PRIM_RSRC_IND does not exist for OPERATIONS : '
|| ' '
|| c_oprn_rsrc.oprn_no
|| ' and OPERATIONS VERSION : '
|| c_oprn_rsrc.oprn_vers
);
lv_error_flag := 'Y';
lv_error_mesage := lv_error_mesage
|| '~~'
|| 'PRIM_RSRC_IND does not exist';
WHEN OTHERS
THEN
fnd_file.put_line
(fnd_file.LOG,
'XXJLN OPM OPERATIONS CONVERSION STG - 0008 '
|| ' '
|| 'Error in PRIM_RSRC_IND Retrieval'
|| ' '
|| c_oprn_rsrc.oprn_no
|| ' '
|| c_oprn_rsrc.oprn_vers
);
lv_error_flag := 'Y';
lv_error_mesage := lv_error_mesage
|| '~~'
|| 'Error in PRIM_RSRC_IND Retrieval';
END;
/*fnd_file.put_line (fnd_file.LOG,
'Test Print 10 '
|| oprn_rec.oprn_no
|| ' '
|| oprn_rec.oprn_vers
);*/
ln_oprn_rsrc_counter := ln_oprn_rsrc_counter + 1;
---- Operation resources
/*fnd_file.put_line (fnd_file.LOG,
'Test Print 10.1 '
|| oprn_rec.oprn_no
|| ' '
|| oprn_rec.oprn_vers
);*/
lv_oprn_rsrc_tbl (ln_oprn_rsrc_counter).resources := c_oprn_rsrc.resources; --Danish 19-FB-2016 --
/*fnd_file.put_line (fnd_file.LOG,
'Test Print 10.2 '
|| oprn_rec.oprn_no
|| ' '
|| oprn_rec.oprn_vers
);*/
lv_oprn_rsrc_tbl (ln_oprn_rsrc_counter).resource_usage := c_oprn_rsrc.resource_usage;
/*fnd_file.put_line (fnd_file.LOG,
'Test Print 10.3 '
|| oprn_rec.oprn_no
|| ' '
|| oprn_rec.oprn_vers
);*/
--Danish 19-FB-2016 -- lv_oprn_rsrc_tbl (ln_oprn_rsrc_counter).resource_count := c_oprn_rsrc.resource_count;
/*fnd_file.put_line (fnd_file.LOG,
'Test Print 10.4 '
|| oprn_rec.oprn_no
|| ' '
|| oprn_rec.oprn_vers
);*/
lv_oprn_rsrc_tbl (ln_oprn_rsrc_counter).process_qty := c_oprn_rsrc.process_qty;
/*fnd_file.put_line (fnd_file.LOG,
'Test Print 10.5 '
|| oprn_rec.oprn_no
|| ' '
|| oprn_rec.oprn_vers
);*/
lv_oprn_rsrc_tbl (ln_oprn_rsrc_counter).prim_rsrc_ind :=ln_prim_rsrc_ind;
/*fnd_file.put_line (fnd_file.LOG,
'Test Print 11 '
|| oprn_rec.oprn_no
|| ' '
|| oprn_rec.oprn_vers
);*/
lv_oprn_rsrc_tbl (ln_oprn_rsrc_counter).scale_type := ln_scale_type;
lv_oprn_rsrc_tbl (ln_oprn_rsrc_counter).cost_analysis_code := c_oprn_rsrc.cost_analysis_code;
lv_oprn_rsrc_tbl (ln_oprn_rsrc_counter).cost_cmpntcls_id := ln_cost_cmpntcls_id;
lv_oprn_rsrc_tbl (ln_oprn_rsrc_counter).offset_interval := c_oprn_actv.offset_interval;
--c_oprn_rsrc.resource_offset_interval;
/*fnd_file.put_line (fnd_file.LOG,
'Test Print 12 '
|| oprn_rec.oprn_no
|| ' '
|| oprn_rec.oprn_vers
);*/
-- lv_oprn_rsrc_tbl(1).delete_mark := 0;
lv_oprn_rsrc_tbl (ln_oprn_rsrc_counter).resource_process_uom := c_oprn_rsrc.resource_process_uom;
/*fnd_file.put_line (fnd_file.LOG,
'Test Print 13 '
|| oprn_rec.oprn_no
|| ' '
|| oprn_rec.oprn_vers
);*/
lv_oprn_rsrc_tbl (ln_oprn_rsrc_counter).resource_usage_uom := c_oprn_rsrc.resource_usage_uom;
/*fnd_file.put_line (fnd_file.LOG,
'Test Print 14 '
|| oprn_rec.oprn_no
|| ' '
|| oprn_rec.oprn_vers
);*/
lv_oprn_rsrc_tbl (ln_oprn_rsrc_counter).activity := c_oprn_actv.activity;
/*fnd_file.put_line (fnd_file.LOG,
'Test Print 15 '
|| oprn_rec.oprn_no
|| ' '
|| oprn_rec.oprn_vers
);*/
-- ln_processed_recs := ln_processed_recs + 1;
END LOOP;
END IF;
--- (c_oprn_actv.activity != lv_oprn_actv_previous)
END LOOP;
--- c_oprn_actv IN cur_operation_activities loop ended
fnd_file.put_line
(fnd_file.LOG,
'oprn_rec.oprn_no ## oprn_rec.oprn_vers for OPERATIONS NAME : '
|| oprn_rec.oprn_no
|| ' and OPERATIONS VERSION : '
|| oprn_rec.oprn_vers
);
fnd_file.put_line (fnd_file.LOG,
'p_oprn_rsrc_tbl count is :'
|| lv_oprn_rsrc_tbl.COUNT
);
--- API Running
--- Will insert in following tables GMD_OPERATIONS_B
---- GMD_OPERATION_ACTIVITIES
---- GMD_OPERATION_RESOURCES
fnd_file.put_line (fnd_file.LOG,'lv_error_flag :' || lv_error_flag );
IF (lv_error_flag = 'Y')
THEN
UPDATE XXJLN_OPM_OPERATIONS_CONV_STG
SET process_flag = 'E',
error_message = lv_error_message
WHERE oprn_no = oprn_rec.oprn_no
AND oprn_vers = oprn_rec.oprn_vers;
fnd_file.put_line (fnd_file.LOG,
'lv_error_message -- Please Chink Errors'
|| lv_error_message
);
ELSE
fnd_file.put_line (fnd_file.LOG, 'Calling the OPERATIONS API for Inserting data...');
gmd_operations_pub.insert_operation
(p_api_version => 1.0,
p_init_msg_list => TRUE,
p_commit => FALSE,
p_operations => lv_operations,
p_oprn_actv_tbl => lv_oprn_actv_tbl,
x_message_count => ln_msg_count,
x_message_list => lv_msg_data,
x_return_status => lv_return_status,
p_oprn_rsrc_tbl => lv_oprn_rsrc_tbl
);
COMMIT;
fnd_file.put_line (fnd_file.LOG, lv_return_status);
-- fnd_file.put_line (fnd_file.LOG, 'Checking Error 2');
IF lv_return_status <> 'S'
THEN
fnd_file.put_line (fnd_file.LOG, 'Checking lv_return_status Error ');
BEGIN
lv_error_flag := 'Y';
-- ln_oper_api_error := ln_oper_api_error + 1;
-- ln_successful_recs := 0;
FOR i IN 1 .. ln_msg_count
LOOP
fnd_msg_pub.get
(p_msg_index => i,
p_encoded => 'F',
p_data => lv_msg_data,
p_msg_index_out => ln_out_index
);
fnd_file.put_line (fnd_file.LOG,
'Message Text : '
|| oprn_rec.oprn_no
|| ' '
|| oprn_rec.oprn_vers
|| ' '
|| SUBSTR (lv_msg_data,
1,
200
)
);
fnd_file.put_line (fnd_file.LOG,
'lv_msg_data chking erors'
|| oprn_rec.oprn_no
|| ' '
|| oprn_rec.oprn_vers
|| ' '
|| SUBSTR (lv_msg_data,
1,
200
)
);
UPDATE XXJLN_OPM_OPERATIONS_CONV_STG
SET process_flag = 'E',
error_message = lv_msg_data
WHERE oprn_no = oprn_rec.oprn_no
AND oprn_vers = oprn_rec.oprn_vers;
COMMIT;
fnd_file.put_line (fnd_file.LOG,
'Checking Error 3'
|| oprn_rec.oprn_no
|| ' '
|| oprn_rec.oprn_vers
|| ' '
|| SUBSTR (lv_msg_data,
1,
200
)
);
END LOOP;
COMMIT;
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line
(fnd_file.LOG,
'Error in the loop when API is not successful:'
);
END;
fnd_file.put_line (fnd_file.LOG,
'Debug -> Message Text Completed '
);
ELSE
-- ln_oper_api_success := ln_oper_api_success + 1;
IF oprn_rec.status IS NOT NULL
THEN
----- Call modify_Status procedure
--- retrieve the operation_id
BEGIN
SELECT oprn_id
INTO ln_oprn_id
FROM gmd_operations_b
WHERE oprn_no = oprn_rec.oprn_no
AND oprn_vers = oprn_rec.oprn_vers;
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line
(fnd_file.LOG,
'Error in operation id retrieval:'
);
END;
--- Retrieve the status code
BEGIN
SELECT status_code
INTO lv_status_code
FROM gmd_status_vl
WHERE meaning = oprn_rec.status;
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line
(fnd_file.LOG,
'Error in status_code retrieval:'
);
END;
modify_status (p_entity_name => 'Operation',
p_entity_id => ln_oprn_id,
p_status_to => lv_status_code,
x_message_count => ln_msg_cnt,
x_message_list => lv_msg_list,
x_return_status => lv_ret_sts
);
fnd_file.put_line (fnd_file.LOG,
'Checking -- modify_Status procedure for OPERATIONS ID : ' ||ln_oprn_id
||' lv_status_code: '|| lv_status_code ||' lv_ret_sts : '||lv_ret_sts);
IF lv_ret_sts <> fnd_api.g_ret_sts_success
THEN --- modify_status api failed
BEGIN
lv_error_flag := 'Y';
-- ln_successful_recs := 0;
UPDATE XXJLN_OPM_OPERATIONS_CONV_STG
SET process_flag = 'E',
error_message = lv_error_message
WHERE oprn_no = oprn_rec.oprn_no
AND oprn_vers = oprn_rec.oprn_vers;
fnd_file.put_line
(fnd_file.LOG,
'lv_error_message CHKING 123'
|| lv_error_message
);
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line
(fnd_file.LOG,
'Error in the staging table status updation:'
|| SQLERRM
);
END;
ELSE --- modify_status api success
fnd_file.put_line (fnd_file.LOG, 'Successfully data uploaded through Operations API ....');
BEGIN
UPDATE XXJLN_OPM_OPERATIONS_CONV_STG
SET process_flag = 'P',
error_message = lv_error_message
WHERE oprn_no = oprn_rec.oprn_no
AND oprn_vers = oprn_rec.oprn_vers;
fnd_file.put_line
(fnd_file.LOG,
'Successfully Custom Table is updated with process_flag = P'
|| lv_error_message
);
-- ln_successful_recs := ln_successful_recs + SQL%ROWCOUNT;
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line
(fnd_file.LOG,
'Error in the staging table status updation:'
|| SQLERRM
);
END;
END IF; --- modify_status api success IF Ended
ELSE ---oprn_rec.status IS NULL
fnd_file.put_line (fnd_file.LOG, 'Success Again');
UPDATE XXJLN_OPM_OPERATIONS_CONV_STG
SET process_flag = 'P',
error_message = lv_error_message
WHERE oprn_no = oprn_rec.oprn_no
AND oprn_vers = oprn_rec.oprn_vers;
fnd_file.put_line
(fnd_file.LOG,
'Success Again - Successfully Custom Table is updated with process_flag = P'
|| lv_error_message
);
-- ln_successful_recs := ln_successful_recs + SQL%ROWCOUNT;
END IF; ----- oprn_rec.status IS NOT NULL end.
END IF; -- lv_ret_sts <> 'S'
END IF; -- IF (lv_error_flag = 'Y') THEN
fnd_file.put_line (fnd_file.LOG, 'Debug -> Inside Loop ');
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line (fnd_file.LOG,
'Error while calling API:' || SQLERRM
);
fnd_file.put_line (fnd_file.LOG, lv_ret_sts);
fnd_file.put_line (fnd_file.LOG, ln_msg_count);
FOR i IN 1 .. ln_msg_count
LOOP
fnd_msg_pub.get (p_msg_index => i,
p_encoded => 'F',
p_data => lv_msg_data,
p_msg_index_out => ln_out_index
);
END LOOP;
END;
END IF;
END LOOP; --oprn_rec IN cur_operations loop ended
fnd_file.put_line (fnd_file.LOG, 'Debug -> Outside Loop ');
-- ln_error_recs := ln_processed_recs - ln_successful_recs;
END IF; ---- cur_operations%NOTFOUND
EXCEPTION --Main Exception
WHEN OTHERS
THEN
lv_error_flag := 'Y';
fnd_file.put_line
(fnd_file.LOG,
'Exception in Main Procedure while creating operation:'
|| SQLERRM
);
END operation_main;
/
-- END xxakg_opm_operation_conv_pkg;
