create or replace PACKAGE APPS.XXXX_PO_WF_SCHEDULING_PKG
IS
--|===========================================================================|
--| Client A |
--| |
--| Description : This Package is used to schedule the PO auto approval |
--| workflow program |
--| Program Name : XXXX_PO_WF_SCHEDULING_PKG_EXE |
--| Module Name : PO |
PROCEDURE XXXX_CALLING_POAPPRV (P_errbuf out varchar2, P_retcode out varchar2, p_org_id number);
END XXXX_PO_WF_SCHEDULING_PKG;
/
SHOW ERRORS;
create or replace PACKAGE BODY APPS.XXXX_PO_WF_SCHEDULING_PKG
IS
--|===========================================================================|
--| Client A |
--| |
--| Description : This Package is used to schedule the PO auto approval |
--| workflow program |
--| Program Name : XXXX_PO_WF_SCHEDULING_PKG_EXE |
--| Module Name : PO |
PROCEDURE XXXX_CALLING_POAPPRV (P_errbuf out varchar2, P_retcode out varchar2, p_org_id number)
IS
-- Initializing the profile vaues into local variables.
ln_user_id number(30);
ln_resp_id number(30);
ln_resp_Appl_id number(30);
xv_item_key varchar2(100);
--declaring the cursor
CURSOR po_details_cur
IS
select segment1,po_header_id,default_approval_path_id,document_type_code,document_subtype,org_id,agent_id from
(SELECT pha.segment1 segment1,
pha.po_header_id po_header_id,
pdt.default_approval_path_id default_approval_path_id,
pdt.document_type_code document_type_code,
pdt.document_subtype document_subtype,
pha.org_id org_id,
pha.agent_id agent_id
--pha.authorization_status authorization_status,
--pdt.wf_approval_itemtype wf_approval_itemtype,
--pdt.wf_approval_process wf_approval_process
--pha.agent_id agent_id,
--prha.AUTHORIZATION_STATUS req_auth,
--pha.creation_Date
FROM apps.PO_HEADERS_ALL PHA
,apps.po_document_types_all pdt
,apps.PO_LINES_ALL PLA
,apps.po_line_locations_all plla
,apps.po_distributions_all pda
,apps.po_req_distributions_all prda
,apps.po_requisition_lines_All prla
,apps.po_requisition_headers_All prha
WHERE 1=1
--AND PHA.SEGMENT1 ='13033126' -- '13033211' --in ('13033163','13033161','13033162','13033145')
AND pha.authorization_status in ('INCOMPLETE') -- ,'REQUIRES REAPPROVAL')
AND pha.type_lookup_code = pdt.document_subtype
AND pha.org_id = pdt.org_id
AND pha.org_id = (select fnd_profile.value('ORG_ID') from DUAL)
AND pdt.document_type_code = 'PO'
AND PHA.PO_HEADER_ID = PLA.PO_HEADER_ID
AND PLA.PO_LINE_ID = plla.po_line_id
AND plla.line_location_id = PDA.line_location_id
AND PDA.req_distribution_id = prda.distribution_id
AND prda.REQUISITION_LINE_ID = prla.REQUISITION_LINE_ID
AND prla.REQUISITION_header_ID = prha.REQUISITION_header_ID
AND prha.TYPE_LOOKUP_CODE = 'PURCHASE'
AND prha.AUTHORIZATION_STATUS = 'APPROVED'
AND to_date((TO_CHAR(PHA.CREATION_DATE,'DD-MON-YYYY HH24:MI:SS')),'DD-MON-YYYY HH24:MI:SS') >= (SELECT to_date((TO_CHAR(REQUESTED_START_DATE,'DD-MON-YYYY HH24:MI:SS')),'DD-MON-YYYY HH24:MI:SS') FROM (SELECT REQUEST_ID,REQUESTED_START_DATE
FROM apps.FND_CONCURRENT_REQUESTS
WHERE CONCURRENT_PROGRAM_ID = (SELECT CONCURRENT_PROGRAM_ID
FROM apps.FND_CONCURRENT_PROGRAMS FCP
WHERE FCP.CONCURRENT_PROGRAM_NAME = 'XXTT_PO_WF_SCHEDULING_PKG_EXE')
AND STATUS_CODE = 'C'
ORDER BY REQUESTED_START_DATE DESC) CON_REQ_DETAILS
WHERE ROWNUM <= 1)
-- AND ((PRHA.INTERFACE_SOURCE_CODE IS NULL) or (PRHA.INTERFACE_SOURCE_CODE in ('ORDER ENTRY','INV','WIP','MSC')))
AND (PRHA.INTERFACE_SOURCE_CODE in ('ORDER ENTRY','INV','WIP','MSC'))
GROUP BY pha.po_header_id,pha.segment1,pdt.default_approval_path_id,pdt.document_subtype,pdt.document_type_code,pha.org_id,pha.agent_id
ORDER BY pha.po_header_id DESC) a
WHERE rownum <= 4;
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
-- Initializing the profile vaues into local variables.
ln_user_id := apps.fnd_profile.value('USER_ID');
ln_resp_id := apps.fnd_profile.value('RESP_ID');
ln_resp_Appl_id := apps.fnd_profile.value('RESP_APPL_ID');
apps.fnd_global.apps_initialize (user_id => ln_user_id,
resp_id => ln_resp_id,
resp_appl_id => ln_resp_Appl_id);
FOR po_details_rec IN po_details_cur
LOOP
apps.mo_global.init (po_details_rec.document_type_code);
apps.mo_global.set_policy_context ('S', po_details_rec.org_id);
SELECT po_details_rec.po_header_id || '-' || TO_CHAR (po_wf_itemkey_s.NEXTVAL) INTO xv_item_key FROM DUAL;
fnd_file.put_line(fnd_file.log,'Calling po_reqapproval_init1.start_wf_process Oracle PO=> '|| po_details_rec.segment1);
DBMS_OUTPUT.PUT_LINE('Calling po_reqapproval_init1.start_wf_process Oracle PO=> '|| po_details_rec.segment1);
apps.po_reqapproval_init1.start_wf_process (itemtype => 'POAPPRV',
itemkey => NULL, -- xv_item_key,
workflowprocess => NULL , -- 'PO_AME_APPRV_TOP',
actionoriginatedfrom => 'PO_FORM',
documentid => po_details_rec.po_header_id,
documentnumber => po_details_rec.segment1,
preparerid => po_details_rec.agent_id,
documenttypecode => po_details_rec.document_type_code,
documentsubtype => po_details_rec.document_subtype,
submitteraction => 'APPROVE',
forwardtoid => NULL,
forwardfromid => NULL,
defaultapprovalpathid => po_details_rec.default_approval_path_id, -- NULL,
note => NULL,
printflag => 'N',
faxflag => 'N',
faxnumber => NULL,
emailflag => 'N',
emailaddress => NULL,
createsourcingrule => 'N',
releasegenmethod => 'N',
updatesourcingrule => 'N',
massupdatereleases => 'N',
retroactivepricechange => 'N',
orgassignchange => 'N',
communicatepricechange => 'N',
p_background_flag => 'N',
p_initiator => NULL,
p_xml_flag => NULL,
fpdsngflag => 'N',
p_source_type_code => NULL);
fnd_file.put_line(fnd_file.log,'The PO is Approved Now =>' || po_details_rec.segment1);
fnd_file.put_line(fnd_file.output,'The PO Approved is =>' || po_details_rec.segment1);
END LOOP;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
fnd_file.put_line(fnd_file.log,'Error Message: '||sqlcode||', '||SQLERRM);
--DBMS_OUTPUT.PUT_LINE('Error Message: '||sqlcode||', '||SQLERRM);
END XXXX_CALLING_POAPPRV;
END XXXX_PO_WF_SCHEDULING_PKG;
/SHOW ERRORS;
