CREATE OR REPLACE PROCEDURE APPS.MFZ_create_routing (p_create_routing IN VARCHAR2)
IS
l_routings_type apps.gmd_routings%ROWTYPE;
l_routings_step_tab apps.gmd_routings_pub.gmd_routings_step_tab;
l_routings_step_dep_tab apps.gmd_routings_pub.gmd_routings_step_dep_tab;
l_return_status VARCHAR2 (1);
l_msg_count NUMBER;
l_msg_data VARCHAR2 (2000);
l_count NUMBER;
l_msg_ind VARCHAR2 (30);
l_routing_id NUMBER;
l_oprn_id NUMBER;
ln_dep_type NUMBER;
ln_suc_rec_cnt NUMBER := 0;
ln_rej_rec_cnt NUMBER := 0;
ln_lcu_count NUMBER;
ln_progress NUMBER := 0;
ln_count NUMBER;
ln_organization_id NUMBER;
lc_routing_uom VARCHAR2 (7);
l_msg varchar2(2000);
l_user_id NUMBER := 1110; --NUMBER := 1187;
l_TEST VARCHAR2 (10);
/*
DROP TABLE APPS.XXBLR_OPM_ROUTING_STG CASCADE CONSTRAINTS;
CREATE TABLE APPS.XXBLR_OPM_ROUTING_STG
(
SR_NO VARCHAR2(20 BYTE),
NEW_ROUTING_NO VARCHAR2(20 BYTE),
ROUTING_VERSION VARCHAR2(20 BYTE),
ROUTING_DESCRIPTION VARCHAR2(250 BYTE),
ROUTING_CLASS VARCHAR2(20 BYTE),
ROUTING_CLASS_DESC VARCHAR2(20 BYTE),
GD_VALID_DATE DATE DEFAULT to_date(sysdate,'dd-mm-yy')
, --, to_char(to_date(valid_from,'dd-mm-yy'),'dd-mon-yy') valid_from --valid_from,
VALID_TO VARCHAR2(20 BYTE),
ROUTING_QTY FLOAT(126),
ROUTING_UOM VARCHAR2(20 BYTE),
OWNER_ORG_CODE VARCHAR2(20 BYTE),
ORGANIZATION_ID NUMBER,
TOTAL_LOSS NUMBER,
RECORD_STATUS CHAR(1 BYTE) DEFAULT 'N',
VALID_FROM DATE,
PROCESS_OUTPUT_QTY NUMBER(10),
OPRN_ID NUMBER,
STEP_NUMBER NUMBER,
NEW_OPRN_NO VARCHAR2(250 BYTE),
ERROR_MESSAGE_ROUTING VARCHAR2(2000 BYTE),
ERROR_CODE_ROUTING VARCHAR2(250 BYTE),
RECORD_STATUS_ROUTING VARCHAR2(250 BYTE)
)
TABLESPACE APPS_TS_TX_DATA
PCTUSED 0
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 64K
NEXT 1M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
LOGGING
NOCOMPRESS
NOCACHE;
Insert into XXBLR_OPM_ROUTING_STG
select distinct 10, FORMULA_NO, FORMULA_VERS, replace(FORMULA_DESC1,'Formula for ','Routing For ') FORMULA_DES, null, null, TO_DATE('10/Nov/22', 'DD/MON/YY'), null,
FORMULA_TYPE, 'PCS','FMO', 370, SCALE_TYPE,'1', TO_DATE('10/Nov/22', 'DD/MON/YY'), 1, 5740,1,'PI6.PCS_PACK', null, null,null form_org_id from fm_form_mst a , gmd_routings b
where a.formula_no = b.routing_no(+) and b.routing_no is null --and YIELD_UOM='PCS'
and exists (select 1 from fm_matl_dtl aa where a.FORMULA_ID=aa.FORMULA_ID and aa.line_type=1 and DETAIL_UOM='PCS' );
Insert into XXBLR_OPM_STEP_DEP_STG
select distinct 'FMO', FORMULA_NO, '1','1','1','1','1', null, null,null form_org_id from fm_form_mst a , gmd_routings b
where a.formula_no = b.routing_no(+) and b.routing_no is null --and YIELD_UOM='PCS'
and exists (select 1 from fm_matl_dtl aa where a.FORMULA_ID=aa.FORMULA_ID and aa.line_type=1 and DETAIL_UOM='PCS' );
commit;
*/
--===============================
--Cursor to get new records count
--===============================
CURSOR lcu_count
IS
-- create table mfz_opm_routing_stg as select * FROM xxblr_opm_routing_stg
SELECT COUNT (*)
FROM xxblr_opm_routing_stg
WHERE record_status ='1' and RECORD_STATUS_ROUTING is null
;-- gc_validation_flag;
--===============================
--Cursor to get Routing Data
--===============================
CURSOR lcu_routing_data
IS
SELECT xors.sr_no
, xors.new_routing_no routing_no
, xors.routing_version
, xors.routing_description
, xors.routing_class
, xors.routing_class_desc
, gd_valid_date valid_from
--, to_char(to_date(xors.valid_from,'dd-mm-yy'),'dd-mon-yy') valid_from --xors.valid_from
, xors.valid_to
, xors.routing_qty
, xors.routing_uom
, xors.owner_org_code
, xors.organization_id
,
--substr(xors.total_loss,1,instr(xors.total_loss,'%')-1)
total_loss --xors.total_loss
FROM xxblr_opm_routing_stg xors
WHERE 1=1 and RECORD_STATUS_ROUTING is null
GROUP BY xors.sr_no
, xors.new_routing_no
, xors.routing_version
, xors.routing_description
, xors.routing_class
, xors.routing_class_desc
, xors.gd_valid_date
, xors.valid_to
, xors.routing_qty
, xors.routing_uom
, xors.owner_org_code
, xors.organization_id
, xors.total_loss
ORDER BY new_routing_no
;
--============================
--Cursor to get Step data
--============================
CURSOR lcu_step_data (p_routing_num VARCHAR2)--,p_step_num NUMBER)
IS
SELECT new_routing_no,
new_oprn_no,
step_number,
oprn_id,
process_output_qty
FROM xxblr_opm_routing_stg
WHERE 1=1 and RECORD_STATUS_ROUTING is null
-- AND step_number = p_step_num
AND new_routing_no = p_routing_num
GROUP BY new_routing_no,
new_oprn_no,
step_number,
oprn_id,
process_output_qty
ORDER BY step_number;
--=================================
--Cursor to get Step Depency data
--=================================
CURSOR lcu_step_dep_data (p_routing_no varchar2)
IS
SELECT organization_code
, new_routing_no routing_no
, routing_version
, previous_step
, routing_step
, dependency_type
, transfer_percent
, error_code
, record_status
, error_message
FROM xxblr_opm_step_dep_stg
WHERE 1=1
AND new_routing_no = p_routing_no
GROUP BY organization_code
, new_routing_no
, routing_version
, previous_step
, routing_step
, dependency_type
, transfer_percent
, error_code
, record_status
, error_message
ORDER BY previous_step;
TYPE tbl_routing_data IS TABLE OF lcu_routing_data%ROWTYPE INDEX BY BINARY_INTEGER;
lt_per tbl_routing_data;
-- TYPE tbl_stepdep_data IS TABLE OF lcu_step_dep_data%ROWTYPE INDEX BY BINARY_INTEGER;
-- ct_per tbl_stepdep_data;
BEGIN
fnd_global.apps_initialize (user_id => 1187,
resp_id => 22882,
resp_appl_id => 552
);
apps.fnd_file.put_line (apps.fnd_file.LOG, RPAD ('*', 80, '*'));
apps.fnd_file.put_line(apps.fnd_file.LOG,' --------- Executing CREATE_ROUTING ---------');
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_ROUTING ---------');
apps.fnd_file.put_line (apps.fnd_file.output, RPAD (' ', 80, ' '));
OPEN lcu_count;
FETCH lcu_count INTO ln_lcu_count;
CLOSE lcu_count;
apps.fnd_file.put_line (apps.fnd_file.LOG,'Number of Records Loaded:' || ln_lcu_count);
-------------------------------------------------------------------------
-- Bulk Insert Data into Qualification.
--------------------------------------------------------------------------
OPEN lcu_routing_data;
LOOP
lt_per.DELETE;
FETCH lcu_routing_data BULK COLLECT INTO lt_per LIMIT 50000;
IF lt_per.COUNT > 0
THEN
apps.fnd_file.put_line (apps.fnd_file.LOG,'COUNT-->' || lt_per.COUNT);
FOR i IN lt_per.FIRST .. lt_per.LAST
LOOP
apps.fnd_file.put_line (apps.fnd_file.LOG,'TRACK2');
l_routings_type := NULL;
l_routings_type.routing_id := NULL;
l_routings_type.routing_no := lt_per (i).routing_no;
l_routings_type.routing_vers := lt_per (i).routing_version;
l_routings_type.routing_desc := lt_per (i).routing_description;
l_routings_type.routing_class := NULL; --lt_per (i).routing_class;
l_routings_type.routing_qty := lt_per (i).routing_qty;
l_routings_type.item_um := NULL;
l_routings_type.delete_mark := 0;
l_routings_type.text_code := NULL;
l_routings_type.inactive_ind := 0;
l_routings_type.in_use := 0;
l_routings_type.creation_date := sysdate;
l_routings_type.created_by := 1110; --l_user_id;
l_routings_type.last_update_login := 76532411; --l_user_id;
l_routings_type.last_update_date := sysdate;
l_routings_type.last_updated_by := 1110; -- l_user_id;
l_routings_type.effective_start_date := sysdate; -- to_date(lt_per (i).valid_from);
l_routings_type.effective_end_date := NULL; --lt_per (i).valid_to;
l_routings_type.owner_id := 1110; -- l_user_id;
l_routings_type.project_id := NULL;
l_routings_type.routing_status := 3; --gn_status;
l_routings_type.process_loss := 0; --lt_per(i).total_loss;
l_routings_type.enforce_step_dependency := 1;
l_routings_type.owner_organization_id := 370; --lt_per (i).organization_id;
l_routings_type.routing_uom := lt_per (i).routing_uom;
l_routings_type.contiguous_ind := 0;
l_routings_type.fixed_process_loss := 0; --NULL;
l_routings_type.fixed_process_loss_uom := lt_per (i).routing_uom; --NULL;
l_count := 0;
l_routings_step_tab.DELETE;
apps.fnd_file.put_line (apps.fnd_file.LOG,'TRACK3');
FOR step_rec IN lcu_step_data(lt_per (i).routing_no)--,lt_per (i).step_number)
LOOP
apps.fnd_file.put_line (apps.fnd_file.LOG,'lcu_step_data :' ||l_count);
apps.fnd_file.put_line (apps.fnd_file.LOG,'STEP NUMBER:' ||step_rec.step_number);
l_count :=l_count + 1;
l_routings_step_tab (l_count).routing_id := NULL; --l_routing_id;
l_routings_step_tab (l_count).routingstep_no := 1; --step_rec.step_number;
l_routings_step_tab (l_count).routingstep_id := NULL;
l_routings_step_tab (l_count).oprn_id := step_rec.oprn_id;
l_routings_step_tab (l_count).step_qty := 1000; --step_rec.process_output_qty;
l_routings_step_tab (l_count).steprelease_type := 1;
l_routings_step_tab (l_count).text_code := NULL;
l_routings_step_tab (l_count).last_updated_by := 1110; --l_user_id;
l_routings_step_tab (l_count).created_by := 1110; -- l_user_id;
l_routings_step_tab (l_count).last_update_date := sysdate;
l_routings_step_tab (l_count).creation_date := sysdate;
l_routings_step_tab (l_count).last_update_login := 76532411;-- l_user_id;
l_routings_step_tab (l_count).minimum_transfer_qty := NULL;
--apps.fnd_file.put_line (apps.fnd_file.LOG,'COUNT-->' || ct_per.COUNT);
END LOOP;
apps.fnd_file.put_line (apps.fnd_file.LOG,'TRACK3');
ln_count :=0; l_TEST := '227';
l_routings_step_dep_tab.DELETE;
FOR ct_per IN lcu_step_dep_data(lt_per(i).routing_no)
LOOP
ln_count := ln_count + 1;
apps.fnd_file.put_line (apps.fnd_file.LOG,'lcu_step_dep_data: ' ||ln_count);
apps.fnd_file.put_line (apps.fnd_file.LOG,'ROUTING STEP NO:' ||ct_per.routing_step);
apps.fnd_file.put_line (apps.fnd_file.LOG,'PREVIOUS STEP NO:' ||ct_per.previous_step);
--Step Dependency
l_routings_step_dep_tab (ln_count).routingstep_no := 1; --ct_per.routing_step;
l_routings_step_dep_tab (ln_count).dep_routingstep_no :=0;-- ct_per.previous_step;
l_routings_step_dep_tab (ln_count).routing_id := NULL;--l_routing_id;
l_routings_step_dep_tab (ln_count).dep_type := ct_per.dependency_type;
l_routings_step_dep_tab (ln_count).rework_code := NULL;
l_routings_step_dep_tab (ln_count).standard_delay := 0;
l_routings_step_dep_tab (ln_count).minimum_delay := 0;
l_routings_step_dep_tab (ln_count).max_delay := NULL;
l_routings_step_dep_tab (ln_count).transfer_qty := 300;
l_routings_step_dep_tab (ln_count).item_um := NULL;
l_routings_step_dep_tab (ln_count).text_code := NULL;
l_routings_step_dep_tab (ln_count).last_updated_by := 1110;-- l_user_id;
l_routings_step_dep_tab (ln_count).created_by := 1110; -- l_user_id;
l_routings_step_dep_tab (ln_count).last_update_date := sysdate;
l_routings_step_dep_tab (ln_count).creation_date := sysdate;
l_routings_step_dep_tab (ln_count).last_update_login := 76532411;-- l_user_id;
l_routings_step_dep_tab (ln_count).transfer_pct := ct_per.transfer_percent;
l_routings_step_dep_tab (ln_count).chargeable_ind := 0;
l_routings_step_dep_tab (ln_count).routingstep_no_uom := lt_per (i).routing_uom;
END LOOP;
BEGIN
apps.gmd_routings_pub.insert_routing
( p_api_version => 1.0
, p_init_msg_list => TRUE
, p_commit => FALSE
, p_routings => l_routings_type
, p_routings_step_tbl => l_routings_step_tab
, p_routings_step_dep_tbl => l_routings_step_dep_tab
, x_message_count => l_msg_count
, x_message_list => l_msg_data
, x_return_status => l_return_status
);
l_TEST := '274';
apps.fnd_file.put_line (apps.fnd_file.LOG,'RETURN_STATUS:-'|| l_return_status);
l_TEST := '278';
EXCEPTION
WHEN OTHERS
THEN
apps.fnd_file.put_line(apps.fnd_file.LOG,'API RAISED ERROR: '|| SQLCODE|| '-'|| SQLERRM);
apps.fnd_file.put_line (apps.fnd_file.LOG,'RETURN_STATUS:-'|| l_return_status);
END;
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 || ' |M- ' || 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;
apps.fnd_file.put_line (apps.fnd_file.LOG,'TRACK 14');
BEGIN
UPDATE xxblr_opm_routing_stg
SET record_status_routing = 'TEST: ' || l_TEST || ' : ' || l_return_status
, error_code_routing = l_msg_count
, error_message_routing = l_msg_data
WHERE new_routing_no = lt_per(i).routing_no
AND routing_version = lt_per(i).routing_version;
EXCEPTION
WHEN OTHERS THEN
apps.fnd_file.put_line(apps.fnd_file.LOG,'Error occured in staging table updation:'||sqlcode||sqlerrm);
END;
BEGIN
UPDATE xxblr_opm_step_dep_stg
SET record_status = l_return_status
, error_code = l_msg_count
, error_message = l_msg_data
WHERE new_routing_no = lt_per(i).routing_no
AND routing_version = lt_per(i).routing_version;
EXCEPTION
WHEN OTHERS THEN
apps.fnd_file.put_line(apps.fnd_file.LOG,'Error occured in staging table updation:'||sqlcode||sqlerrm);
END;
COMMIT;
END LOOP;
END IF;
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, ' ');
EXIT WHEN lcu_routing_data%NOTFOUND;
END LOOP;
CLOSE lcu_routing_data;
EXCEPTION
WHEN OTHERS THEN
apps.fnd_file.put_line (apps.fnd_file.LOG,'MAIN-ERR-CREATE-ROUT :> ' || SQLERRM || ', ' || SQLCODE);
apps.fnd_file.put_line (apps.fnd_file.LOG,' --------- Procedure CREATE_ROUTING Exit ---------');
apps.fnd_file.put_line (apps.fnd_file.LOG, RPAD (' ', 80, ' '));
apps.fnd_file.put_line (apps.fnd_file.LOG, RPAD ('*', 80, '*'));
END mfz_create_routing;
/
Thursday 1 December 2022
new routing creation
Sunday 30 October 2022
OPM Operation Creation API
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;
Internal location is Already assigned to another Ship-to Address
SELECT * FROM hz_cust_accounts WHERE ACCOUNT_NUMBER = 'R08'; -- CUST_ACCOUNT_ID =51401 , PARTY_ID= 235048 SELECT * FRO...
-
FND API to Delete, Add and Update value set values in Oracle Applications 01. Need to create a table like : CREATE TABLE...
-
OPM Financials Tables: TABLE NAME DESCRIPTION CM_ACER_MSG The Actual Cost Process Error Message Table will provide the user a...
-
/* opm_undo_create_accounting_by_date_range_and_LE_R12.sql +=======================================================================+ * ...