AMRESH KUMARAN ORACLE APPLICATIONS: Purchase Requisition Data Loading

CREATE TABLE APPS.XXSKM_PR_UPLOAD
(
ITEM_CODE VARCHAR2(50 BYTE),
NET_REQ NUMBER(10),
LIST_PRICE_PER_UNIT NUMBER(10),
PRIMARY_UNIT_OF_MEASURE VARCHAR2(50 BYTE),
PREPARER VARCHAR2(50 BYTE),
REQUESTOR VARCHAR2(50 BYTE),
DESTINATION_ORG VARCHAR2(50 BYTE),
DELIVER_TO_LOCATION VARCHAR2(50 BYTE),
NEED_BY_DATE DATE,
CHARGE_ACCOUNTS_ID VARCHAR2(50 BYTE),
PROCESS_FLAG VARCHAR2(2 BYTE),
LAST_UPDATE_DATE DATE,
LAST_UPDATED_BY NUMBER,
CREATED_BY NUMBER,
CREATION_DATE DATE DEFAULT SYSDATE,
REQUEST_ID NUMBER
) -- this custom table

CREATE OR REPLACE PACKAGE APPS.xxskm_pr_upload_pkg
AS
--
--------------------------------------------------------------------------------

-- Program Type : Package Body
-- Conc Prog Short Name : xxskm_pr_upload_pkg
--
-- Description:
--
-- Modification History:
-- --------- ----------- ----------------------------------------------------
-- Date Author Comments
-- --------- ----------- ----------------------------------------------------
-- 21-Jul-2014 Amresh Initial Creation
--
--------------------------------------------------------------------------------
--
--
PROCEDURE main ( p_errbuf_out OUT VARCHAR2
, p_retcod_out OUT VARCHAR2
);
--
END xxskm_pr_upload_pkg;

execution file name in xxskm_pr_upload_pkg.main

================================================================
================================================================

CREATE OR REPLACE PACKAGE BODY APPS.xxskm_pr_upload_pkg
AS
--
--------------------------------------------------------------------------------
-- Owner : HCL Infosystems Ltd
-- Project : Su-Kam
-- Program Type : Package Body
-- Conc Prog Short Name : xxskm_pr_upload_pkg
--
-- Description:
--
-- Modification History:
-- --------- ----------- ----------------------------------------------------
-- Date Author Comments
-- --------- ----------- ----------------------------------------------------
-- 21-Jul-2014 Subir Initial Creation
--
--------------------------------------------------------------------------------
--
pv_conc_req_id NUMBER;
pv_request_id NUMBER := fnd_global.conc_request_id;
pv_user_id VARCHAR2(30) := NVL (fnd_profile.VALUE ('USER_ID'), '1');
--pv_resp_id VARCHAR2(30) := NVL (fnd_profile.VALUE ('RESP_ID'), '1');
--pv_resp_appl_id VARCHAR2(30) := NVL (fnd_profile.VALUE ('RESP_APPL_ID'), '1');
pv_org_id NUMBER := 101;
--
PROCEDURE xxfhl_print_audit_details (p_req_id NUMBER);
--
PROCEDURE main ( p_errbuf_out OUT VARCHAR2
, p_retcod_out OUT VARCHAR2
)
IS
--
v_item_id number;
v_uom varchar2(10);
v_net_req number;
v_list_price number;
v_date date;
v_phase VARCHAR2(200) :='';
v_status VARCHAR2(200) :='';
v_dev_phase VARCHAR2(200) :='';
v_dev_status VARCHAR2(200) :='';
v_message VARCHAR2(200) :='';
v_return BOOLEAN;
--
cursor cur_data is
select a.rowid
, a.item_code
, a.net_req
, a.list_price_per_unit
, a.primary_unit_of_measure
, a.preparer
, a.requestor
, a.destination_org
, a.deliver_to_location
, a.need_by_date
, a.charge_accounts_id
, a.process_flag
, a.last_update_date
, a.last_updated_by
, a.created_by
, a.creation_date
from xxskm_pr_upload a
where a.item_code not like 'W%'
and nvl(a.process_flag,'N') = 'N'
and request_id = pv_request_id
;
--
begin
--
fnd_file.put_line (fnd_file.log,'118');
fnd_file.put_line (fnd_file.log,'delete old records from po_requisitions_interface_all');
--
delete from po_requisitions_interface_all
where created_by = pv_user_id;
--
fnd_file.put_line (fnd_file.log,'Update need_by_date of custom table');
--
update xxskm_pr_upload a
set a.need_by_date = trunc(sysdate) + 3
, a.created_by = pv_user_id
, a.creation_date= SYSDATE
, request_id = pv_request_id
where nvl(a.process_flag,'N') = 'N' ;
--
for c1 in cur_data loop
--
fnd_file.put_line (fnd_file.log,'119');
v_net_req := c1.net_req;
--
fnd_file.put_line (fnd_file.log,'v_net_req'||'--'||v_net_req);
fnd_file.put_line (fnd_file.log,'v_list_price'||'--'||v_list_price);
--
v_date := c1.need_by_date;
--
begin
--
SELECT msi.inventory_item_id
, msi.primary_unit_of_measure
, nvl(msi.list_price_per_unit,1)
INTO v_item_id
, v_uom
, v_list_price
FROM mtl_system_items_kfv msi
WHERE msi.concatenated_segments = c1.item_code
AND organization_id = 106 -- 001
;
--
exception
when others then
--
fnd_file.put_line (fnd_file.log,'Select Statement'||' '||c1.item_code);
--
end;
--
fnd_file.put_line (fnd_file.log,'122');
--
INSERT INTO po_requisitions_interface_all
(interface_source_code
,batch_id
,transaction_id
,org_id
,destination_type_code
,authorization_status
,preparer_id
,source_type_code
,unit_of_measure
,line_type_id
,item_id
,unit_price
,quantity
,destination_organization_id
,deliver_to_location_id
,deliver_to_requestor_id
,multi_distributions
,req_dist_sequence_id
,interface_source_line_id
,charge_account_id
,need_by_date
,destination_subinventory
,creation_date
,created_by
)
values ( 'EXTERNAL' -- interface_source_code
, NULL -- batch_id
, NULL -- transaction_id
, 101 -- org_id
, 'INVENTORY' -- destination_type_code
, 'INCOMPLETE' -- authorization_status
, 7329 -- preparer_id
, 'VENDOR' -- source_type_code
, v_uom -- unit_of_measure
, 1020 -- line_type_id
, v_item_id -- item_id
, v_list_price -- unit_price
, v_net_req -- quantity
, 108 -- destination_organization_id
, 166 -- deliver_to_location_id
, 7329 -- deliver_to_requestor_id
, null -- multi_distributions
, null -- req_dist_sequence_id
, null -- interface_source_line_id
, 1551 -- charge_account_id
, v_date -- need_by_date
, 'RMS' -- destination_subinventory
, sysdate -- creation_date
, pv_user_id -- created_by
);
commit;
--
update xxskm_pr_upload a
set process_flag = 'P'
, last_update_date = SYSDATE
, last_updated_by = pv_user_id
where rowid = c1.rowid
;
--
EXIT WHEN cur_data%NOTFOUND;
fnd_file.put_line (fnd_file.log,'123');
--
end loop;
--
--------------------------
-- Run Requisition Import
--------------------------
fnd_file.put_line (fnd_file.log,' Submiting the request for Requisition Import ');
--
fnd_request.set_org_id(pv_org_id);
--
pv_conc_req_id := fnd_request.submit_request(Application => 'PO'
,Program => 'REQIMPORT'
,Description => NULL
,Start_Time => NULL
,Sub_Request => FALSE
,Argument1 => NULL
,Argument2 => NULL
,Argument3 => 'VENDOR'
,Argument4 => NULL
,Argument5 => 'N'
,Argument6 => 'N' );
COMMIT;
--
fnd_file.put_line (fnd_file.log,' Waiting for completion of Requisition Import ');
--
-- Calling function fnd_concurrent.wait_for_request so that interface gets completed
-- when the submitted request is completed.
v_return := fnd_concurrent.wait_for_request(pv_conc_req_id
,10
,0
,v_phase
,v_status
,v_dev_phase
,v_dev_status
,v_message
);
--
---------------------------
-- Output detail procedure
---------------------------
fnd_file.put_line (fnd_file.log,'Print Audit Details Start Date Time :- '|| TO_CHAR (SYSDATE, 'DD/MM/YYYY HH24:MI:SS'));
fnd_file.put_line (fnd_file.log, '');
--
xxfhl_print_audit_details (p_req_id => pv_conc_req_id);
--
fnd_file.put_line (fnd_file.log,'Print Audit Details End Date Time :- '|| TO_CHAR (SYSDATE, 'DD/MM/YYYY HH24:MI:SS'));
fnd_file.put_line (fnd_file.log, '');
--

exception
when others then
--
fnd_file.put_line (fnd_file.log,'Records are not uploaded');
--
end main;
--
--
--------------------------------------------------------------------------------
-- Name : xxfhl_print_audit_details
-- Description:
-- This procedure prints audit details report
--
--
-- Parameters:
-- 1. p_req_id
--
-- Pseudo logic:
-- None
--------------------------------------------------------------------------------
--
PROCEDURE xxfhl_print_audit_details (p_req_id NUMBER)
IS
--
--
CURSOR cur_error_details
IS
SELECT NVL(TRIM(prh.segment1), ' ') pr_num
, prh.creation_date
, NVL(TRIM(msi.concatenated_segments), ' ') item_code
, NVL(TRIM(prl.unit_price), ' ') unit_price
, NVL(TRIM(prl.quantity), ' ') quantity
, NVL(TRIM(fu.user_name), ' ') user_name
, NULL error_message
, 'P' process_flag
FROM po_requisition_headers_all prh
, po_requisition_lines_all prl
, mtl_system_items_kfv msi
, fnd_user fu
WHERE prh.requisition_header_id = prl.requisition_header_id
AND prl.item_id = msi.inventory_item_id
AND prl.destination_organization_id = msi.organization_id
AND trunc(prh.creation_date) = trunc(sysdate)
AND fu.user_id = prh.created_by
AND prh.request_id = p_req_id
--
UNION
--
SELECT null pr_num
, pri.creation_date
, NVL(TRIM(msi.concatenated_segments), ' ') item_code
, NVL(TRIM(pri.unit_price), ' ') unit_price
, NVL(TRIM(pri.quantity), ' ') quantity
, null user_name
, NVL(TRIM(pie.error_message), ' ') error_message
, 'E' process_flag
FROM po_requisitions_interface_all pri
, po_interface_errors pie
, mtl_system_items_kfv msi
, fnd_user fu
WHERE trunc(pri.need_by_date) = trunc(sysdate)
AND pie.interface_transaction_id = pri.transaction_id
AND pri.item_id = msi.inventory_item_id
AND pri.destination_organization_id = msi.organization_id
AND pri.request_id = p_req_id
ORDER BY 1
;
--
BEGIN
--
fnd_file.put_line(fnd_file.log,'xpad-001');
fnd_file.put_line(fnd_file.log,'xxfhl_print_audit_details' );
--
fnd_file.put_line (fnd_file.output, '');
fnd_file.put_line (fnd_file.output, '');
--
fnd_file.put_line(fnd_file.log, 'xpad-050');
fnd_file.put_line (fnd_file.output, '--------------Detail------------');
fnd_file.put_line (fnd_file.output, '');
fnd_file.put_line (fnd_file.output, 'Pr Number Item Code Unit Price Quantity Created By Creation Dt Error Desc');
fnd_file.put_line (fnd_file.output, '------------- ------------------------------ ---------------------- ---------------------- ---------------------- ----------- ----------------------');
--
FOR crec IN cur_error_details
LOOP
--
fnd_file.put_line (fnd_file.output
, RPAD (crec.pr_num, 13) ||' '||
RPAD (crec.item_code, 31) ||' '||
RPAD (crec.unit_price, 22) ||' '||
RPAD (crec.quantity, 22) ||' '||
RPAD (crec.user_name, 22) ||' '||
RPAD (crec.creation_date, 22) ||' '||
crec.error_message
);
--
IF crec.process_flag = 'E'
THEN
--
update xxskm_pr_upload a
set process_flag = 'E'
where item_code = crec.item_code
and net_req = crec.quantity
;
--
END IF;
--
END LOOP;
--
COMMIT;
--
fnd_file.put_line(fnd_file.log, 'xpad-110');
fnd_file.put_line(fnd_file.output, ' ' );
fnd_file.put_line(fnd_file.log, 'xpad-999');
--
EXCEPTION
--
WHEN OTHERS THEN
--
fnd_file.put_line(fnd_file.log, 'Error in := xxfhl_print_audit_details, '||sqlerrm );
--
END xxfhl_print_audit_details;
--
END xxskm_pr_upload_pkg;
/

AMRESH KUMARAN ORACLE APPLICATIONS

Wednesday, 1 July 2015

Purchase Requisition Data Loading

No comments:

Post a Comment

Blog Archive