ORACLE MASTERMINDS: Discrete Job Migration Along With PO

Discrete Job Migration Along With PO

Script

CREATE OR REPLACE PACKAGE BODY APPS.XXEUR_CREATE_DISCRETE_JOB

PROCEDURE main(errbuf OUT VARCHAR2

,retcode OUT NUMBER

,p_validate_flag IN VARCHAR2

,p_load_flag IN VARCHAR2

,p_process_flag IN VARCHAR2

,p_pocreate_flag IN VARCHAR2

)

lc_errbuf VARCHAR2 (500);

ln_retcode NUMBER;

BEGIN

fnd_file.put_line (fnd_file.LOG,

' >>>>>>>>>>>>>>>>>> Executing main <<<<<<<<<<<<<<<<<<<<<<<<<'

);

fnd_file.put_line (fnd_file.LOG,' ');

--======================================

-- Executes validate customer PROCEDURE

--======================================

IF p_validate_flag = 'Y' THEN

validate_jobs;

END IF;

--======================================

-- Execute validated customer PROCEDURE

--======================================

IF p_load_flag = 'Y' THEN

fnd_file.put_line (fnd_file.LOG,

'Start Loading data into interface table ');

load_data;

END IF;

IF p_process_flag = 'Y' THEN

fnd_file.put_line (fnd_file.LOG,'Start Creating the jobs');

process_data;

fnd_file.put_line (fnd_file.LOG,'End Creating jobs');

END IF;

IF p_pocreate_flag = 'Y' THEN

fnd_file.put_line (fnd_file.LOG,'Start Creating PO');

po_auto_create;

fnd_file.put_line (fnd_file.LOG,'End Creating PO');

END IF;

fnd_file.put_line (fnd_file.LOG,' ');

fnd_file.put_line (fnd_file.LOG,

' >>>>>>>>>>>>>>>>>> Executing xxcns_customer_conv_pkg.main - Exit <<<<<<<<<<<<<<<<<<<<<<<<<'

);

EXCEPTION

WHEN OTHERS THEN

fnd_file.put_line (fnd_file.LOG,

'Error in xxcns_customer_conv_pkg.main: ' || SQLCODE|| ' - '|| SQLERRM);

END main;

PROCEDURE validate_jobs

--- =================

-- Local Variables

--=================

ln_count NUMBER := 0;

lc_cntry VARCHAR2 (50);

ln_ou_count NUMBER;

lc_cust_status VARCHAR2 (200);

lc_rec_status VARCHAR2 (10) := 'S';

lc_msg VARCHAR2 (10000) := NULL;

lc_error_msg VARCHAR2 (4000) := 'Err Msg-VL:-> ';

lc_territory VARCHAR2 (50);

ln_party_id NUMBER;

lc_scac_code VARCHAR2(4);

ln_location_id NUMBER;

ln_party_site_id NUMBER;

lc_currency_code VARCHAR2(15);

ln_organization_id NUMBER;

ln_org_id NUMBER;

ln_assembly_item_id NUMBER;

ln_routing_sequence_id NUMBER;

lc_subinventory VARCHAR2(60);

lc_class_code VARCHAR2(60);

lc_supplier_name VARCHAR2(60);

ln_vendor_id NUMBER;

lc_supplier_site_name VARCHAR2(60);

lc_job_name VARCHAR2(60);

--================================================

-- Cursor to get the record count tobe processed

--================================================

CURSOR lcu_job_data(cp_process_flag varchar2)

SELECT COUNT (*)

FROM xxeur_discrete_job_stg

WHERE NVL (status_flag, 'E') = cp_process_flag;

--==========================================

-- Cursor to get the data tobe lc_cust_status

--==========================================

CURSOR lcu_main

SELECT *

FROM xxeur_discrete_job_stg

WHERE NVL (status_flag, 'N') = 'N';

--==========================================

-- Cursor to validate the Assembly Items

--==========================================

CURSOR lcu_assembly_item(cp_assembly_item VARCHAR2,cp_organization_id

NUMBER)

SELECT assembly_item_id

FROM bom_bill_of_materials BBOM

,mtl_system_items_b MSI

WHERE BBOM.assembly_item_id = MSI.inventory_item_id

AND BBOM.organization_id = MSI.organization_id

AND MSI.segment1 = cp_assembly_item

AND MSI.organization_id =cp_organization_id;

--==========================================

------Cursor to Validate routings.

--==========================================

CURSOR lcu_routing(cp_assembly_item_id VARCHAR2,cp_organization_id NUMBER)

SELECT routing_sequence_id

FROM bom_operational_routings BOR

WHERE BOR.assembly_item_id = cp_assembly_item_id

AND BOR.organization_id = cp_organization_id;

--==========================================

----Cursor to Validate Inventory Org

--==========================================

CURSOR lcu_org(cp_organization_code VARCHAR2)

SELECT OOD.organization_id, OOD.operating_unit

FROM org_organization_definitions OOD

WHERE UPPER(organization_code) = UPPER(TRIM(cp_organization_code));

--==========================================

-----------Cursor to validate Sub Inventory

--==========================================

CURSOR lcu_subinv(cp_subinv VARCHAR2,cp_organization_id NUMBER)

SELECT secondary_inventory_name

FROM mtl_secondary_inventories

WHERE secondary_inventory_name=cp_subinv

AND organization_id=cp_organization_id ;

--==========================================

----Cursor to validate class code

--==========================================

CURSOR lcu_class_code(cp_organization_id NUMBER)

SELECT class_code

FROM wip_accounting_classes

WHERE organization_id=cp_organization_id;

--==========================================

-- Cursor to validate the Supplier Name

--==========================================

CURSOR lcu_supplier_name(cp_vendor_name VARCHAR2)

SELECT vendor_name,vendor_id

FROM ap_suppliers

WHERE vendor_name = cp_vendor_name

AND enabled_flag = 'Y';

--==========================================

-- Cursor to validate the Supplier Site Code

--==========================================

CURSOR lcu_supplier_site_name(cp_supplier_site_name VARCHAR2,cp_org_id NUMBER,cp_vendor_id NUMBER)

SELECT vendor_site_id

FROM ap_supplier_sites_all

WHERE vendor_site_code = cp_supplier_site_name

AND org_id = cp_org_id

AND vendor_id = cp_vendor_id

AND NVL(inactive_date,TRUNC(SYSDATE)) >= TRUNC(SYSDATE);

--==========================================

-- Cursor to validate the JOB NAME

--==========================================

CURSOR lcu_job_name(cp_job_name VARCHAR2)

SELECT JOB_NAME

FROM APPS.WIP_JOB_SCHEDULE_INTERFACE_V

WHERE JOB_NAME = cp_job_name;

BEGIN

ln_count :=0;

OPEN lcu_job_data('N');

FETCH lcu_job_data

INTO ln_count;

CLOSE lcu_job_data;

fnd_file.put_line (fnd_file.LOG, RPAD (' ', 80, ' '));

fnd_file.put_line (fnd_file.LOG, ' ----- VALIDATE_CUSTOMERS -----');

fnd_file.put_line (fnd_file.LOG, RPAD (' ', 80, ' '));

fnd_file.put_line (fnd_file.LOG, ' ----- VALIDATE_CUSTOMERS -----');

fnd_file.put_line (fnd_file.LOG, RPAD (' ', 80, ' '));

fnd_file.put_line(fnd_file.LOG,

' Number of Carriers To be Validated :-> ' || ln_count);

BEGIN

-- fnd_global.apps_initialize (3072, 20634, 401);

FOR lcv_main IN lcu_main

LOOP

--==================================

-- Initializing the cursor variables

--==================================

lcv_main.error_message := 'ERRM-VL> ';

lcv_main.status_flag := 'V';

ln_org_id := NULL;

ln_vendor_id := NULL;

--DBMS_OUTPUT.put_line('Start Validation-1');

fnd_file.put_line(fnd_file.LOG,'Start Validation-1');

--VAlidation inventory org ---

IF TRIM(lcv_main.organization_code) IS NULL THEN

lcv_main.error_message := lcv_main.error_message||

' Organization Code is Required, ';

lcv_main.status_flag := 'E';

ELSE

fnd_file.put_line (fnd_file.LOG, 'TRACK1');

ln_organization_id :=NULL;

OPEN lcu_org (TRIM(lcv_main.organization_code));

FETCH lcu_org

INTO ln_organization_id,ln_org_id;

CLOSE lcu_org;

IF ln_organization_id IS NOT NULL THEN

fnd_file.put_line (fnd_file.LOG, 'ln_organization_id'||

ln_organization_id);

lcv_main.organization_id := ln_organization_id;

ELSE

lcv_main.error_message := lcv_main.error_message||

' Organization Code is Invalid, ';

lcv_main.status_flag := 'E';

END IF;

IF TRIM(lcv_main.assembly_item) IS NULL THEN

lcv_main.error_message := lcv_main.error_message||

' Assembly Item is Required, ';

lcv_main.status_flag := 'E';

ELSE

ln_assembly_item_id := NULL;

OPEN lcu_assembly_item(lcv_main.assembly_item,lcv_main.

organization_id);

FETCH lcu_assembly_item

INTO ln_assembly_item_id;

CLOSE lcu_assembly_item;

IF ln_assembly_item_id IS NULL THEN

lcv_main.error_message := lcv_main.error_message||' '||lcv_main

.assembly_item||' is Invalid, ';

lcv_main.status_flag := 'E';

ELSE

lcv_main.assembly_item_id:=ln_assembly_item_id;

END IF;

IF lcv_main.job_name IS NULL THEN

lcv_main.job_name :=WIP_JOB_NUMBER_S.NEXTVAL;

END IF;

fnd_file.put_line (fnd_file.LOG,lcv_main.status_flag);

fnd_file.put_line (fnd_file.LOG,'Start Validation-10');

--DBMS_OUTPUT.put_line('Start Validation-10');

-- Validating the country for Customer ----

IF lcv_main.assembly_item_id IS NOT NULL THEN

ln_routing_sequence_id := NULL;

OPEN lcu_routing(lcv_main.assembly_item_id,lcv_main.

organization_id);

FETCH lcu_routing

INTO ln_routing_sequence_id;

CLOSE lcu_routing;

IF ln_routing_sequence_id IS NULL THEN

lcv_main.error_message := lcv_main.error_message||

' Routing is Not defined for this Assembly Item, ';

lcv_main.status_flag := 'E';

END IF;

IF lcv_main.start_quantity IS NULL THEN

lcv_main.error_message := lcv_main.error_message||

' Start Quantity is Required, ';

lcv_main.status_flag := 'E';

END IF;

IF lcv_main.net_quantity IS NULL THEN

lcv_main.error_message := lcv_main.error_message||

' Net Quantity is Required, ';

lcv_main.status_flag := 'E';

END IF;

IF lcv_main.first_unit_start_date IS NULL THEN

lcv_main.error_message := lcv_main.error_message||

' First Unit Start Date is Required, ';

lcv_main.status_flag := 'E';

END IF;

IF TRIM(lcv_main.completion_subinventory) IS NOT NULL THEN

lc_subinventory := NULL;

OPEN lcu_subinv(TRIM(lcv_main.completion_subinventory),lcv_main.

organization_id);

FETCH lcu_subinv

INTO lc_subinventory;

CLOSE lcu_subinv;

IF lc_subinventory IS NULL THEN

lcv_main.error_message := lcv_main.error_message||

' Sub-Inventory is Invalid, ';

lcv_main.status_flag := 'E';

END IF;

IF TRIM(lcv_main.class_code) IS NULL THEN

lcv_main.error_message := lcv_main.error_message||

' Class Code is Required, ';

lcv_main.status_flag := 'E';

ELSE

lc_class_code := NULL;

OPEN lcu_class_code(lcv_main.organization_id);

FETCH lcu_class_code

INTO lc_class_code;

CLOSE lcu_class_code;

IF lc_class_code IS NULL THEN

lcv_main.error_message := lcv_main.error_message||' '||lcv_main

.class_code||' is Invalid, ';

lcv_main.status_flag := 'E';

END IF;

IF lcv_main.net_quantity IS NULL THEN

lcv_main.error_message := lcv_main.error_message||

' Net Quantity is Required, ';

lcv_main.status_flag := 'E';

END IF;

--status_type 1.UnReleased 3. Released 4.Complete 6.On Hold 7. Cancelled

IF UPPER(TRIM(lcv_main.status_type_name)) ='UNRELEASED' THEN

lcv_main.status_type := 1;

ELSIF UPPER(TRIM(lcv_main.status_type_name)) ='RELEASED' THEN

lcv_main.status_type := 3;

ELSIF UPPER(TRIM(lcv_main.status_type_name)) ='COMPLETE' THEN

lcv_main.status_type := 4;

ELSIF UPPER(TRIM(lcv_main.status_type_name)) LIKE 'ON%HOLD%' THEN

lcv_main.status_type := 6;

ELSIF UPPER(TRIM(lcv_main.status_type_name)) = 'CANCELLED' THEN

lcv_main.status_type := 7;

ELSE

lcv_main.error_message := lcv_main.error_message||

' Status is Invalid, ';

lcv_main.status_flag := 'E';

END IF;

IF UPPER(TRIM(lcv_main.job_type)) ='STANDARD' THEN

lcv_main.load_type := 1;

ELSIF UPPER(TRIM(lcv_main.job_type)) LIKE 'NON%STANDARD%' THEN

lcv_main.status_type := 4;

ELSE

lcv_main.error_message := lcv_main.error_message||

' Job Type is Invalid, ';

lcv_main.status_flag := 'E';

END IF;

IF TRIM(lcv_main.supplier_name) IS NULL THEN

lcv_main.error_message := lcv_main.error_message||' Supplier Name is Required, ';

lcv_main.status_flag := 'E';

ELSE

lc_supplier_name := NULL;

OPEN lcu_supplier_name(lcv_main.supplier_name);

FETCH lcu_supplier_name

INTO lc_supplier_name,ln_vendor_id;

CLOSE lcu_supplier_name;

IF ln_vendor_id IS NULL THEN

lcv_main.error_message := lcv_main.error_message||' '||'Supplier Does Not Exists, ';

lcv_main.status_flag := 'E';

END IF;

--------------------------------------------------------------

IF TRIM(lcv_main.supplier_site_name) IS NULL THEN

lcv_main.error_message := lcv_main.error_message||' Supplier Site Name is Required, ';

lcv_main.status_flag := 'E';

ELSIF (TRIM(lcv_main.supplier_site_name) IS NOT NULL AND ln_vendor_id IS NOT NULL ) THEN

lc_supplier_site_name := NULL;

OPEN lcu_supplier_site_name(lcv_main.supplier_site_name,ln_org_id,ln_vendor_id);

FETCH lcu_supplier_site_name

INTO lc_supplier_site_name;

CLOSE lcu_supplier_site_name;

IF lc_supplier_site_name IS NULL THEN

lcv_main.error_message := lcv_main.error_message||' '||'Supplier Site Name Does Not Exists, ';

lcv_main.status_flag := 'E';

END IF;

---------------------------------------------------------------------

IF TRIM(lcv_main.job_name) IS NULL THEN

lcv_main.error_message := lcv_main.error_message||' Job Name is Required, ';

lcv_main.status_flag := 'E';

ELSE

lc_job_name := NULL;

OPEN lcu_job_name(lcv_main.job_name);

FETCH lcu_job_name

INTO lc_job_name;

CLOSE lcu_job_name;

IF lc_job_name IS NOT NULL THEN

lcv_main.error_message := lcv_main.error_message||' '||'Job Name Already Exists, ';

lcv_main.status_flag := 'E';

END IF;

---------------------------------------------------------------------

IF lcv_main.status_flag <> 'E' THEN

lcv_main.error_message := 'All Validations Passed';

lcv_main.status_flag := 'V';

ELSE

lcv_main.status_flag := 'VE';

END IF;

fnd_file.put_line (fnd_file.LOG,'lcv_main.status_flag'||lcv_main.

status_flag);

fnd_file.put_line (fnd_file.LOG,'lcv_main.error_message'||lcv_main.

error_message);

UPDATE xxeur_discrete_job_stg

SET last_update_date = gd_sysdate

,last_updated_by = gn_user_id

,assembly_item_id = lcv_main.assembly_item_id

,organization_id = lcv_main.organization_id

,job_name = lcv_main.job_name

,status_type = lcv_main.status_type

,load_type = lcv_main.load_type

,status_flag = lcv_main.status_flag

,error_message = lcv_main.error_message

WHERE record_id = lcv_main.record_id;

fnd_file.put_line (fnd_file.LOG,'lcv_main.row_id'||lcv_main.

record_id);

END LOOP;

END;

COMMIT;

ln_count := 0;

OPEN lcu_job_data('V');

FETCH lcu_job_data

INTO ln_count;

CLOSE lcu_job_data;

fnd_file.put_line(fnd_file.LOG,

' Number of Customers Validated :-> ' || ln_count);

fnd_file.put_line (fnd_file.LOG, RPAD (' ', 80, ' '));

fnd_file.put_line (fnd_file.LOG, ' ----- VALIDATE_CUSTOMERS Exit -----')

;

EXCEPTION WHEN OTHERS THEN

fnd_file.put_line (fnd_file.LOG,' Error While Loading ValidateCust - '||

SQLERRM);

--DBMS_OUTPUT.put_line('Error'||SQLERRM);

END validate_jobs;

--------------------------------------------------------------------------------------------------------------------

-- Loading the Staging Table Into Interface Tables ( wip_job_schedule_interface )

---------------------------------------------------------------------------------------------------------------------

PROCEDURE load_data

ln_progress NUMBER:=0;

ln_count NUMBER:=0;

ln_processed NUMBER:=0;

x_location_id NUMBER;

x_return_status VARCHAR2(2000);

x_msg_count NUMBER;

x_msg_data VARCHAR2(2000);

x_party_site_id NUMBER;

x_party_site_number VARCHAR2(2000);

x_cust_account_id NUMBER;

x_account_number NUMBER;

x_party_id NUMBER;

x_party_number NUMBER;

x_profile_id NUMBER;

ln_party_site_id NUMBER;

ln_party_id NUMBER;

lc_location NUMBER;

--============================================

-- To get the count of records to be processed

--============================================

CURSOR lcu_count_new(p_record_status VARCHAR2)

SELECT COUNT(*)

FROM xxapl.xxeur_discrete_job_stg

WHERE NVL(status_flag,'E') ='V';

--===============================

-- To get records to be processed

--===============================

CURSOR lcu_main

SELECT *

FROM xxeur_discrete_job_stg

WHERE NVL(status_flag,'N') ='V';

ln_location_id number;

BEGIN

ln_progress :=1;

FOR lt_load_job IN lcu_main

LOOP

lt_load_job.error_message := 'ERRM-PL> Loaded Into Interface Table';

lt_load_job.status_flag := 'PL';

fnd_file.put_line (fnd_file.LOG,'Loading into wip_job_schedule_interface table'||lt_load_job.record_id);

BEGIN

INSERT INTO wip_job_schedule_interface

(

organization_id

, primary_item_id

, job_name

, start_quantity

, net_quantity

, first_unit_start_date

, class_code

, status_type

, completion_subinventory

, GROUP_ID

, load_type

, process_phase

, process_status

, created_by

, creation_date

, last_updated_by

, last_update_date

)

VALUES

(

lt_load_job.organization_id

,lt_load_job.assembly_item_id

,lt_load_job.job_name

,lt_load_job.start_quantity

,lt_load_job.net_quantity

,lt_load_job.first_unit_start_date

,lt_load_job.class_code

,lt_load_job.status_type

--status_type 1.UnReleased 3. Released 4.Complete 6.On Hold 7. Cancelled

,lt_load_job.completion_subinventory

,9 -- group_id

,lt_load_job.load_type -- load_type

,fnd_global.user_id -- created_by

, SYSDATE -- creation_date

,fnd_global.user_id -- last_updated_by

, SYSDATE -- last_update_date

);

EXCEPTION

WHEN OTHERS THEN

lt_load_job.error_message :=SQLERRM;

lt_load_job.status_flag := 'PE';

END;

fnd_file.put_line (fnd_file.LOG,'Updating xxeur_discrete_job_stg Table');

UPDATE xxeur_discrete_job_stg

SET status_flag = lt_load_job.status_flag

,error_message = lt_load_job.error_message

WHERE record_id = lt_load_job.record_id;

END LOOP;

COMMIT;

EXCEPTION

WHEN OTHERS THEN

apps.fnd_file.put_line (apps.fnd_file.LOG,'CRT-JOB-ERR @'||ln_progress||','

|| SQLERRM);

apps.fnd_file.put_line(apps.fnd_file.output,RPAD(' ', 80, ' '));

apps.fnd_file.put_line (apps.fnd_file.LOG,' ----- Load Exit -----');

apps.fnd_file.put_line (apps.fnd_file.LOG,RPAD(' ', 80, ' '));

END load_data;

--------------------------------------------------------------------------------------------------------------------

-- Creating the JOBS using API.

---------------------------------------------------------------------------------------------------------------------

PROCEDURE process_data

ln_progress NUMBER:=-1;

ln_count NUMBER:=0;

ln_return_id NUMBER:=NULL;

lc_return_status VARCHAR2(30);

ln_msg_count NUMBER;

lc_msg_data VARCHAR2(4000);

ln_trans_count NUMBER;

ln_rec_id NUMBER;

--------------------------------------------------------------------------------------------------------------------

-- Cursor picks the record id's for all the rows which are in interface table from Staging table

---------------------------------------------------------------------------------------------------------------------

CURSOR lcu_process_data

SELECT win.job_name job_name,stg.record_id record_id, win.process_status process_status FROM

xxeur_discrete_job_stg stg, wip_job_schedule_interface win

WHERE win.job_name = stg.job_name

AND win.GROUP_ID = 9

AND win.process_status = 4

AND stg.status_flag = 'PL';

--------------------------------------------------------------------------------------------------------------------

-- Cursor picks all the records which are successfully created.

---------------------------------------------------------------------------------------------------------------------

CURSOR lcu_success_data

SELECT win.job_name job_name,stg.record_id record_id, win.process_status process_status FROM

xxeur_discrete_job_stg stg, wip_job_schedule_interface win

WHERE win.job_name = stg.job_name

AND win.GROUP_ID = 9

AND win.process_status = 4;

--AND stg.status_flag = 'PL';

--------------------------------------------------------------------------------------------------------------------

-- Cursor picks all the records which are Errored.

---------------------------------------------------------------------------------------------------------------------

CURSOR lcu_error_data

SELECT win.job_name job_name,stg.record_id record_id, win.process_status process_status,wie.error error

FROM xxeur_discrete_job_stg stg, wip_job_schedule_interface win,wip_interface_errors wie

WHERE win.job_name = stg.job_name

AND win.interface_id = wie.interface_id

AND win.GROUP_ID = 9

AND win.process_status = 3;

BEGIN

apps.fnd_file.put_line(apps.fnd_file.LOG,

' --------- Procedure PROCESS_DATA_API --------');

apps.fnd_file.put_line(apps.fnd_file.LOG,RPAD(' ',80,' '));

apps.fnd_file.put_line(apps.fnd_file.output,

' --------- Procedure PROCESS_DATA_API --------');

apps.fnd_file.put_line(apps.fnd_file.output,RPAD(' ',80,' '));

BEGIN

wip_massload_pub.massloadjobs(p_groupid => 9,

p_validationlevel => 2,

p_commitflag => 1,

x_returnstatus => lc_return_status,

x_errormsg => lc_msg_data);

EXCEPTION

WHEN OTHERS THEN

apps.fnd_file.put_line(apps.fnd_file.LOG,'PROCESS-DATA-API>'||SQLERRM

||', '||SQLCODE);

END;

FOR l_rec IN lcu_success_data

LOOP

fnd_file.put_line (fnd_file.LOG,'Updating xxeur_discrete_job_stg Table'||l_rec.record_id);

UPDATE xxeur_discrete_job_stg

SET status_flag = l_rec.process_status

,error_message = 'Complete'

WHERE record_id = l_rec.record_id;

END LOOP;

COMMIT;

FOR l_rec IN lcu_error_data

LOOP

fnd_file.put_line (fnd_file.LOG,'Updating xxeur_discrete_job_stg Table'||l_rec.record_id);

UPDATE xxeur_discrete_job_stg

SET status_flag = l_rec.process_status

,error_message = l_rec.error

WHERE record_id = l_rec.record_id;

END LOOP;

COMMIT;

apps.fnd_file.put_line(apps.fnd_file.LOG,RPAD(' ',80,' '));

apps.fnd_file.put_line(apps.fnd_file.LOG,

' --------- Procedure PROCESS_DATA_API Exit --------');

apps.fnd_file.put_line(apps.fnd_file.LOG,RPAD(' ',80,' '));

apps.fnd_file.put_line(apps.fnd_file.LOG,

' --------- Procedure PROCESS_DATA_API Exit --------');

--po_auto_create;

EXCEPTION

WHEN OTHERS THEN

apps.fnd_file.put_line(apps.fnd_file.LOG,'PROCESS-DATA-API-END>'||SQLERRM ||

', '||SQLCODE);

apps.fnd_file.put_line(apps.fnd_file.LOG,RPAD(' ',80,' '));

apps.fnd_file.put_line(apps.fnd_file.LOG,

' --------- Procedure PROCESS_DATA_API Exit --------');

apps.fnd_file.put_line(apps.fnd_file.LOG,RPAD(' ',80,' '));

END process_data;

--------------------------------------------------------------------------------------------------------------------

-- Procedure to create the Purchased Order using API.

---------------------------------------------------------------------------------------------------------------------

PROCEDURE po_auto_create

CURSOR lcu_stg_data IS

SELECT * FROM xxeur_discrete_job_stg

WHERE status_flag = '4';

ln_interface_header_id NUMBER;

ln_interface_line_id NUMBER;

ln_po_req_line_id NUMBER:=0;

ln_po_req_line_num NUMBER:=0;

lc_process_code VARCHAR2(20) := 'NEW';

-- NEW in case of creation of new PO, ADD incase of adding line to PO

lc_action VARCHAR2(20) := 'NEW';

--- NEW in case of creation of new PO, ADD incase of adding line to PO

ln_document_id NUMBER := NULL;

-- assign PO number in case of adding lines to existing PO…

ln_document_num VARCHAR2(20) := NULL;

-- assign PO number in case of adding lines to existing PO…

ln_agent_id NUMBER:=0 ;

ln_vendor_id NUMBER;

ln_vendor_site_id NUMBER;

lc_return_status VARCHAR2(1);

ln_msg_count NUMBER;

lc_msg_data VARCHAR2(5000);

ln_num_lines_processed NUMBER;

lc_approval_status VARCHAR2(25):='APPROVED';

lc_document_number po_headers_all.segment1%TYPE;

BEGIN

mo_global.init('PO');

mo_global.set_policy_context('S',1256);

FOR l_rec IN lcu_stg_data

LOOP

SELECT po_headers_interface_s.NEXTVAL

INTO ln_interface_header_id

FROM DUAL;

apps.fnd_file.put_line(apps.fnd_file.LOG,'ln_interface_header_id='||ln_interface_header_id);

apps.fnd_file.put_line(apps.fnd_file.LOG,' vendor_name'||l_rec.supplier_name);

apps.fnd_file.put_line(apps.fnd_file.LOG,' vendor_site_code'||l_rec.supplier_site_name);

ln_vendor_id := NULL;

ln_vendor_site_id := NULL;

ln_agent_id := NULL;

ln_po_req_line_id := NULL;

ln_po_req_line_num := NULL;

BEGIN

SELECT PV.vendor_id,vendor_site_id

INTO ln_vendor_id,ln_vendor_site_id

FROM po_vendors PV

,po_vendor_sites_all PVS

WHERE vendor_name = l_rec.supplier_name--'AMBIOS'

AND PV.vendor_id = PVS.vendor_id

AND PVS.vendor_site_code = l_rec.supplier_site_name;--'FARMEA';

apps.fnd_file.put_line(apps.fnd_file.LOG,' vendor_name'||l_rec.supplier_name);

apps.fnd_file.put_line(apps.fnd_file.LOG,' vendor_site_code'||l_rec.supplier_site_name);

EXCEPTION WHEN OTHERS THEN

apps.fnd_file.put_line(apps.fnd_file.LOG,'Exception in Vendor Name Query');

apps.fnd_file.put_line(apps.fnd_file.LOG,RPAD(' ',80,' '));

apps.fnd_file.put_line(apps.fnd_file.LOG,SQLERRM ||', '||SQLCODE);

END;

apps.fnd_file.put_line(apps.fnd_file.LOG,'Level 1');

apps.fnd_file.put_line(apps.fnd_file.LOG,' job_name'||l_rec.job_name);

BEGIN

SELECT prl.to_person_id, prl.requisition_line_id, prl.line_num

INTO ln_agent_id, ln_po_req_line_id, ln_po_req_line_num

FROM po_requisition_lines_all prl, wip_job_schedule_interface win

WHERE prl.wip_entity_id = win.wip_entity_id

AND win.job_name = l_rec.job_name;

apps.fnd_file.put_line(apps.fnd_file.LOG,' job_name'||l_rec.job_name);

apps.fnd_file.put_line(apps.fnd_file.LOG,' vendor_site_code'||l_rec.supplier_site_name);

apps.fnd_file.put_line(apps.fnd_file.LOG,' Agent Id'||ln_agent_id);

apps.fnd_file.put_line(apps.fnd_file.LOG,' PO Req Line Id'||ln_po_req_line_id);

EXCEPTION WHEN OTHERS THEN

apps.fnd_file.put_line(apps.fnd_file.LOG,'Exception in Agent Id Query');

apps.fnd_file.put_line(apps.fnd_file.LOG,RPAD(' ',80,' '));

apps.fnd_file.put_line(apps.fnd_file.LOG,SQLERRM ||', '||SQLCODE);

END;

apps.fnd_file.put_line(apps.fnd_file.LOG,'ln_agent_id='||ln_agent_id);

/* code to get agent_id */

apps.fnd_file.put_line(apps.fnd_file.LOG,'Inserting into Header PO interface');

INSERT INTO po_headers_interface

( interface_header_id

,interface_source_code

,batch_id

,process_code

,action

,document_type_code

,document_subtype

,document_num

-- ,freight_terms

-- ,FOB

--,payment_terms

,group_code

,vendor_id

,vendor_site_id

,agent_id

,currency_code

,creation_date

,created_by

,last_update_date

,last_updated_by

,style_id

,approval_status

,approved_date

)

VALUES

( ln_interface_header_id

,'CONSUMPTION_ADVICE'

,ln_interface_header_id -- x_batch_id

,lc_process_code -- process code

,lc_action --- action

,'PO' --- x_document_type

,'STANDARD' --- document_subtype

,l_rec.PURCHASE_ORDER_NUMBER--ln_document_num-- --- document_num

-- ,p_freight_terms

-- ,p_FOB

--,payment_terms

,'DEFAULT' --- 'DEFAULT' / 'REQUISITION'

,ln_vendor_id --- x_vendor_id

,ln_vendor_site_id --- x_vendor_site_id

,ln_agent_id --- x_agent_id

,'EUR' -- x_currency_code

,SYSDATE -- x_creation_date

,-1 --- x_created_by

,SYSDATE -- x_last_update_date

,-1 --- x_last_updated_by

,1 -- style_id

,lc_approval_status

,SYSDATE

);

apps.fnd_file.put_line(apps.fnd_file.LOG,'Inserting into LIne PO interface');

SELECT po_lines_interface_s.NEXTVAL

INTO ln_interface_line_id

FROM DUAL;

INSERT INTO po_lines_interface

( interface_header_id

,interface_line_id

,requisition_line_id

,line_num

,unit_price

,creation_date

,created_by

,last_update_date

,last_updated_by

)

VALUES

( ln_interface_header_id -- x_interface_header_id,

,ln_interface_line_id -- x_interface_line_id,

,ln_po_req_line_id -- x_requisition_line_id, from req lines

,ln_po_req_line_num -- x_so_line_num, from SO lines

,l_rec.price

,SYSDATE -- x_creation_date

,-1 --- x_created_by

,SYSDATE --- x_last_update_date

,-1 --- x_last_updated_by

);

COMMIT;

apps.fnd_file.put_line(apps.fnd_file.LOG,'Calling... PO_INTERFACE_S.create_documents');

PO_INTERFACE_S.create_documents (

p_api_version => 1.0

,x_return_status => lc_return_status

,x_msg_count => ln_msg_count

,x_msg_data => lc_msg_data

,p_batch_id => ln_interface_header_id

,p_req_operating_unit_id => 1256

,p_purch_operating_unit_id => 1256

,x_document_id => ln_document_id

,x_number_lines => ln_num_lines_processed

,x_document_number => ln_document_num

,p_document_creation_method => NULL

,p_sourcing_k_doc_type => NULL

,p_conterms_exist_flag => 'N'

,p_orig_org_id => NULL

);

IF lc_return_status = 'S' THEN

apps.fnd_file.put_line(apps.fnd_file.LOG,'API Success');

UPDATE xxeur_discrete_job_stg

SET status_flag = 'S'

,error_message = 'Successfully Create PO.' || ln_document_num

,PO_NUM = ln_document_num

WHERE record_id = l_rec.record_id;

--Updating the consigned_consumption_flag = NULL in the Base Table

--in order to display the PO in Purchased Order Form if it successfully created.

UPDATE po_headers_all

SET consigned_consumption_flag = NULL

WHERE po_header_id = ln_document_id;

ELSE

apps.fnd_file.put_line(apps.fnd_file.LOG,'API Error');

UPDATE xxeur_discrete_job_stg

SET status_flag = 'E'

,error_message = 'Error while Creating PO.' || SUBSTR(

lc_msg_data,1,25)

WHERE record_id = l_rec.record_id;

END IF;

END LOOP;

COMMIT;

apps.fnd_file.put_line(apps.fnd_file.LOG,'l_return_status: ' || lc_return_status);

apps.fnd_file.put_line(apps.fnd_file.LOG,'l_msg_count: ' || ln_msg_count);

apps.fnd_file.put_line(apps.fnd_file.LOG,'l_msg_data:' || SUBSTR(lc_msg_data,1,240));

apps.fnd_file.put_line(apps.fnd_file.LOG,'l_document_id: ' || ln_document_id);

apps.fnd_file.put_line(apps.fnd_file.LOG,'l_document_number: ' || ln_document_num);

EXCEPTION WHEN OTHERS THEN

apps.fnd_file.put_line(apps.fnd_file.LOG,'PROCESS-DATA-END>'||SQLERRM ||', '||SQLCODE);

apps.fnd_file.put_line(apps.fnd_file.LOG,RPAD(' ',80,' '));

apps.fnd_file.put_line(apps.fnd_file.LOG,' --------- Procedure PO AUTO CREATE Exception --------');

apps.fnd_file.put_line(apps.fnd_file.LOG,RPAD(' ',80,' '));

apps.fnd_file.put_line(apps.fnd_file.LOG,SQLERRM ||', '||SQLCODE);

END po_auto_create;

END XXEUR_CREATE_DISCRETE_JOB;

Thursday 9 June 2016

Discrete Job Migration Along With PO

No comments:

Post a Comment