/*R12 - PO - SAMPLE SCRIPT TO APPROVE PURCHASE ORDER*/
PROCEDURE submit_po_appr_proc1( p_po_header_id NUMBER,
po_release_id NUMBER)
AS
v_item_key VARCHAR2(100);
v_resp_id NUMBER;
v_resp_appl_id NUMBER;
v_po_release_id NUMBER:=po_release_id;
po_status VARCHAR2(100);
po_msg VARCHAR2(2000);
v_user NUMBER; ----added on 4apr22-----
num NUMBER; ----added on 4apr22-----
CURSOR c_po_details IS
SELECT DISTINCT pha.org_id,
--DECODE(po_RELEASE_ID,PRA.PO_RELEASE_ID,PRA.PO_RELEASE_ID,NULL) PO_RELEASE_ID,
pdt.document_subtype,
pdt.document_type_code,
pha.created_by,
pha.last_updated_by,
pha.authorization_status,
pha.agent_id,
pra.agent_id r_agent_id ,
pha.segment1,
pha.po_header_id
FROM apps.po_headers_all pha,
apps.po_releases_all pra,
apps.po_document_types_all pdt
WHERE 1=1
AND
CASE
WHEN pha.type_lookup_code='PLANNED'
AND v_po_release_id IS NOT NULL THEN 'SCHEDULED'
ELSE pha.type_lookup_code
END = pdt.document_subtype
--and pra.RELEASE_TYPE = pdt.document_subtype
AND pha.org_id = pdt.org_id
AND pha.po_header_id=pra.po_header_id(+)
--and pha.type_lookup_code =
AND pdt.document_type_code =
CASE
WHEN pha.type_lookup_code = 'PLANNED'
AND v_po_release_id IS NULL THEN 'PO'
WHEN pha.type_lookup_code = 'PLANNED'
AND v_po_release_id IS NOT NULL THEN 'RELEASE'
WHEN pha.type_lookup_code = 'STANDARD' THEN 'PO'
WHEN pha.type_lookup_code = 'BLANKET'
AND v_po_release_id IS NOT NULL THEN 'RELEASE'
WHEN pha.type_lookup_code = 'BLANKET'
AND v_po_release_id IS NULL THEN 'PA'
END
AND pha.po_header_id = p_po_header_id
AND (
pra.po_release_id=v_po_release_id
OR v_po_release_id IS NULL);
/*
SELECT
pha.po_header_id,
pha.org_id,
pha.segment1,
pha.agent_id,
pdt.document_subtype,
pdt.document_type_code,
pha.authorization_status,
pha.CREATED_BY
FROM apps.po_headers_all pha, apps.po_document_types_all pdt
WHERE pha.type_lookup_code = pdt.document_subtype
AND pha.org_id = pdt.org_id
AND pdt.document_type_code = 'PO'
--AND NVL(authorization_status,'INCOMPLETE') in ('INCOMPLETE', 'REQUIRES REAPPROVAL')
AND pha.po_header_id = p_po_header_id
--AND pdt.org_id=83
;*/
-- Enter the Purchase Order Number
ln_request_id NUMBER;
BEGIN
FOR p_rec IN c_po_details
LOOP
SELECT fresp.responsibility_id,
fresp.application_id
INTO v_resp_id,
v_resp_appl_id
FROM fnd_responsibility_tl fresp
WHERE fresp.responsibility_name LIKE '%PURCHASING SUPERUSER C-58 (TC)%'
AND LANGUAGE = userenv('LANG')
AND ROWNUM=1;
----added on 4apr22-----
BEGIN
SELECT count(*)
INTO num
FROM fnd_user
WHERE user_id = p_rec.created_by
AND (
end_date IS NULL
OR end_date >= SYSDATE);
IF num <> 0 THEN
v_user := p_rec.created_by;
ELSE
v_user := p_rec.last_updated_by;
END IF;
EXCEPTION
WHEN OTHERS THEN
v_user := p_rec.created_by;
END;
apps.mo_global.Init ('PO');
--fnd_global.apps_initialize (user_id => p_rec.CREATED_BY,resp_id => v_resp_id,resp_appl_id => v_resp_appl_id);
fnd_global.Apps_initialize (user_id => v_user,
resp_id => v_resp_id,
resp_appl_id => v_resp_appl_id);
mo_global.Set_policy_context ('S', p_rec.org_id);
SELECT p_rec.po_header_id
||'-'
||to_char(po_wf_itemkey_s.NEXTVAL)
INTO v_item_key
FROM dual;
dbms_output.Put_line (' Calling po_reqapproval_init1.start_wf_process for po_id=>'
|| p_rec.segment1);
fnd_file.Put_line(fnd_file.log,'Calling po_reqapproval_init1.start_wf_process for po_id=>'
|| p_rec.segment1);
po_reqapproval_init1.Start_wf_process( itemtype => 'POAPPRV' ,
itemkey => v_item_key ,
workflowprocess => 'POAPPRV_TOP' ,
actionoriginatedfrom => 'PO_FORM' ,
documentid => Nvl(v_po_release_id,p_po_header_id)--p_rec.po_header_id -- po_header_id
,
documentnumber => p_rec.segment1 -- Purchase Order Number
,
preparerid => Nvl(p_rec.r_agent_id,p_rec.agent_id) -- Buyer/Preparer_id
,
documenttypecode => p_rec.document_type_code--'PO'
,
documentsubtype => p_rec.document_subtype --'STANDARD'
,
submitteraction => 'APPROVE' ,
forwardtoid => NULL ,
forwardfromid => NULL ,
defaultapprovalpathid => 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);
COMMIT;
dbms_output.Put_line ('The PO which is Approved Now =>'
|| p_rec.segment1);
fnd_file.Put_line(fnd_file.log,'The PO which is Approved Now =>'
|| p_rec.segment1);
/*ln_request_id := fnd_request.submit_request ('XXKARAM', -- application
'XXK_APPROVEPO',-- program short name
'', -- description
'', -- start time
FALSE, -- sub request
v_item_key -- argument1
);
COMMIT;
IF ln_request_id = 0
THEN
DBMS_OUTPUT.PUT_LINE ('Concurrent request failed to submit');
END IF;
po_item_key:=v_item_key;*/
END LOOP;
--PO_STATUS := 'S';
--PO_MSG := 'Workflow Completed Successfully.';
fnd_file.Put_line(fnd_file.log,'Success Status:'
||po_status);
fnd_file.Put_line(fnd_file.log,'Success Message:'
||po_msg);
EXCEPTION
WHEN OTHERS THEN
po_status := 'E';
po_msg := 'Error'
||' '
|| SQLERRM;
fnd_file.Put_line(fnd_file.log,'Error Status:'
||po_status);
fnd_file.Put_line(fnd_file.log,'Error Message:'
||po_msg);
END;
;
/*R12 - PO - SAMPLE SCRIPT TO APPROVE BLANKET PURCHASE AGREEMENT*/
DECLAREv_item_key VARCHAR2(100);
v_resp_id NUMBER;
v_resp_appl_id NUMBER;
v_user NUMBER;
num NUMBER;
CURSOR c_po_details IS
SELECT pha.org_id,
pha.po_header_id,
pha.segment1,
pha.agent_id,
pdt.document_subtype,
pdt.document_type_code,
pha.authorization_status,
pha.approved_flag,
pha.wf_item_type,
pha.wf_item_key,
pha.created_by,
PHA.last_updated_by
FROM apps.po_headers_all pha,
apps.po_document_types_all pdt
WHERE pha.type_lookup_code = pdt.document_subtype
AND pha.org_id = pdt.org_id
AND pdt.document_type_code = 'PA'
AND authorization_status IN ( 'INCOMPLETE', 'REQUIRES REAPPROVAL' )
AND segment1 = '26375500267'; -- Enter the BPA Number
BEGIN
SELECT fresp.responsibility_id,
fresp.application_id
INTO v_resp_id, v_resp_appl_id
FROM fnd_responsibility_tl fresp
WHERE fresp.responsibility_name LIKE '%PURCHASING SUPERUSER C-58 (TC)%'
AND LANGUAGE = Userenv('LANG')
AND ROWNUM = 1;
FOR p_rec IN c_po_details LOOP
BEGIN
SELECT Count(*)
INTO num
FROM fnd_user
WHERE user_id = p_rec.created_by
AND ( end_date IS NULL
OR end_date >= SYSDATE );
IF num <> 0 THEN
v_user := p_rec.created_by;
ELSE
v_user := p_rec.last_updated_by;
END IF;
EXCEPTION
WHEN OTHERS THEN
v_user := p_rec.created_by;
END;
mo_global.Init('PO');
--fnd_global.apps_initialize (user_id => p_rec.CREATED_BY,resp_id => v_resp_id,resp_appl_id => v_resp_appl_id);
fnd_global.Apps_initialize(user_id => v_user, resp_id => v_resp_id,
resp_appl_id => v_resp_appl_id);
mo_global.Set_policy_context('S', p_rec.org_id);
SELECT p_rec.po_header_id
||'-'
||To_char(po_wf_itemkey_s.NEXTVAL)
INTO v_item_key
FROM dual;
dbms_output.Put_line (
'Calling po_reqapproval_init1.start_wf_process for po_id=>'
|| p_rec.segment1);
po_reqapproval_init1.Start_wf_process(itemtype => 'POAPPRV',
itemkey => v_item_key, workflowprocess => 'POAPPRV_TOP',
actionoriginatedfrom => 'PO_FORM', documentid => p_rec.po_header_id,
documentnumber => p_rec.segment1, preparerid => p_rec.agent_id,
documenttypecode => p_rec.document_type_code,
documentsubtype => p_rec.document_subtype, submitteraction => 'APPROVE',
forwardtoid => NULL,
forwardfromid => NULL, defaultapprovalpathid => 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);
COMMIT;
dbms_output.Put_line ('The BPA which is Approved Now =>'
|| p_rec.segment1);
END LOOP;
END;
No comments:
Post a Comment