PO Creation API in EBS R12.2.X
This BLOG provides a step-by-step approach to creating a Purchase Order (PO) API in Oracle E-Business Suite (EBS). The API automates the conversion of approved purchase requisitions into purchase orders using PL/SQL procedures.
/* Formatted on 3/26/2025 3:56:31 PM (QP5 v5.269.14213.34769) */
DECLARE
l_return_status VARCHAR2 (1);
l_msg_count NUMBER;
l_msg_data VARCHAR2 (5000);
l_num_lines_processed NUMBER;
l_document_number po_headers_all.segment1%TYPE;
l_document_id NUMBER;
l_msg VARCHAR2 (5000);
l_msg_index_out NUMBER;
v_item_key Varchar2(100);
E_ID VARCHAR2(2000) ;
v_po_number VARCHAR2(20);
l_org_id number;
CURSOR po_interface_headers_rec
IS
SELECT DISTINCT
prl.requisition_header_id,prh.segment1,
prl.vendor_id,
prl.vendor_site_id,
prl.attribute14,
prl.destination_organization_id,
prh.org_id,
prh.preparer_id,
prl.deliver_to_location_id,
prl.destination_type_code,
SUGGESTED_VENDOR_NAME,aps.vendor_name,
SUGGESTED_VENDOR_LOCATION,
prh.description,
(SELECT invoice_currency_code
FROM ap_supplier_sites_all
WHERE vendor_site_id = prl.vendor_site_id)
currency_code,
(SELECT CONVERSION_RATE
FROM gl_daily_rates gdr, ap_supplier_sites_all aps
WHERE aps.invoice_currency_code = gdr.from_currency
AND aps.vendor_site_id = prl.vendor_site_id
AND conversion_type = 'Corporate'
AND TO_CURRENCY = 'USD'
AND TRUNC (CONVERSION_DATE) = TRUNC (SYSDATE))
Rate,
(SELECT CONVERSION_date
FROM gl_daily_rates gdr, ap_supplier_sites_all aps
WHERE aps.invoice_currency_code = gdr.from_currency
AND aps.vendor_site_id = prl.vendor_site_id
AND conversion_type = 'Corporate'
AND TO_CURRENCY = 'USD'
AND TRUNC (CONVERSION_DATE) = TRUNC (SYSDATE))
Rate_Date, ----- Added Against Issue # 14677
-- prl.rate_date,
-- prl.rate_type,
(SELECT CONVERSION_type
FROM gl_daily_rates gdr, ap_supplier_sites_all aps
WHERE aps.invoice_currency_code = gdr.from_currency
AND aps.vendor_site_id = prl.vendor_site_id
AND conversion_type = 'Corporate'
AND TO_CURRENCY = 'USD'
AND TRUNC (CONVERSION_DATE) = TRUNC (SYSDATE))
Rate_Type --- Added Against Issue # 14677
FROM po_requisition_headers_all prh,
po_requisition_lines_all prl,
po_req_distributions_all prd,
FND_LOOKUP_VALUES FLV,
AP_SUPPLIERS APS
WHERE prh.requisition_header_id = prl.requisition_header_id
AND prh.type_lookup_code = 'PURCHASE'
AND prh.authorization_status = 'APPROVED'
AND prd.requisition_line_id = prl.requisition_line_id
AND NOT EXISTS (SELECT 1 FROM po_distributions_all pda WHERE pda.req_distribution_id=prd.distribution_id)
AND FLV.lookup_type = 'BSDM_PUNCHOUT_SUPPLIERS_PO'
AND FLV.MEANING=APS.VENDOR_NAME
AND APS.VENDOR_ID=prl.VENDOR_ID
AND FLV.language = userenv('LANG')
and prl.destination_type_code='EXPENSE'
and prh.segment1=:p_Req_num;
--order by 2
-- AND prh.REQUISITION_HEADER_ID = 8717742;
CURSOR po_interface_lines_rec (
p_requistion_header_id NUMBER)
IS
SELECT prl.requisition_header_id,
prl.quantity,
prl.requisition_line_id,
prl.item_id,
prl.unit_price,
prl.line_num , --,prl.vendor_id
--,prl.vendor_site_id
(SELECT invoice_currency_code
FROM ap_supplier_sites_all
WHERE vendor_site_id = prl.vendor_site_id)
currency_code,
prl.attribute14,
prl.unit_meas_lookup_code,
prl.destination_organization_id,
prl.org_id,
prl.deliver_to_location_id,
prh.preparer_id,
prh.authorization_status, prl.need_by_date
FROM po_requisition_headers_all prh,
po_requisition_lines_all prl,
po_req_distributions_all prd
WHERE prh.requisition_header_id = prl.requisition_header_id
AND prh.type_lookup_code = 'PURCHASE'
AND prh.authorization_status = 'APPROVED'
--AND prh.closed_code<>'FINALLY CLOSED'
and prl.destination_type_code='EXPENSE'
AND prd.requisition_line_id = prl.requisition_line_id
AND NOT EXISTS (SELECT 1 FROM po_distributions_all pda WHERE pda.req_distribution_id=prd.distribution_id)
--AND prl.requisition_header_id = 8717742
and prh.segment1=:p_Req_num
;
L_USER_ID NUMBER := 28245; --2605;--fnd_profile.value('USER_ID');
L_RESP_ID NUMBER := 201; --fnd_profile.value('RESP_ID');
L_RESP_APPL_ID NUMBER := 53303; --fnd_profile.value('RESP_APPL_ID');
BEGIN
l_org_id:=135;
DBMS_OUTPUT.PUT_LINE ('CREATE PO PROCEDURE STARTS');
DBMS_OUTPUT.PUT_LINE ('PO CREATE Procedure starts here.');
fnd_global.apps_initialize (L_USER_ID,
L_RESP_ID,
L_RESP_APPL_ID);
mo_global.SET_POLICY_CONTEXT ('S', l_org_id); --change this
FOR i IN po_interface_headers_rec
LOOP
DBMS_OUTPUT.PUT_LINE ( 'requistion_header_id ');
INSERT INTO po_headers_interface (interface_header_id,
batch_id,
interface_source_code,
process_code,
action,
org_id,
document_type_code,
document_subtype,
currency_code,
agent_id,
vendor_id,
vendor_site_id,
ship_to_location_id,
-- bill_to_location_id, --,
group_code,
style_id,
comments,
rate,
rate_date,
rate_type)
VALUES (po_headers_interface_s.NEXTVAL,
po_headers_interface_s.CURRVAL,
'PO',
'APPROVED', --'PENDING',
'NEW', -- Indicates this is a new document
i.org_id, -- Org id for operating unit Vision Operations
'PO', -- Indicates a standard PO is being imported
'STANDARD',
I.CURRENCY_CODE, -- The currency to be used in the PO
i.preparer_id, -- The ID of the buyer
i.vendor_id, -- Supplier name
i.vendor_site_id, -- Supplier Site
i.deliver_to_location_id, -- 142, --i.ship_to_location, -- Ship to location name
-- 142, --i.bill_to_location,--, -- Bill to location name
'DEFAULT', --'TestPO'
1,
'PO For Punchout Suppliers ',
i.rate,
i.rate_date,
i.rate_type);
FOR j IN po_interface_lines_rec (i.requisition_header_id)
LOOP
INSERT INTO po_lines_interface (interface_line_id,
interface_header_id,
line_num,
--shipment_num,
line_type,
item_id, --item,
unit_of_measure,
quantity,
unit_price,
--ship_to_organization_code,
--ship_to_location,
requisition_line_id)
VALUES (po_lines_interface_s.NEXTVAL,
po_headers_interface_s.CURRVAL,
j.line_num,
--1,
'Goods',
j.item_id, -- Item to imported on the PO line
j.unit_meas_lookup_code, -- Unit of Measure
j.quantity,
j.unit_price, -- Line price in specified currency
j.requisition_line_id --'V1', -- Inventory Organization which will receive the shipment
--'V1- New York City'
);
DBMS_OUTPUT.put_line ('insertion done in line interface');
fnd_file.put_line (fnd_file.LOG, 'insertion done in line interface');
/* INSERT INTO
po_line_locations_interface
(interface_line_location_id,
interface_header_id,
interface_line_id,
shipment_type,
shipment_num,
ship_to_organization_id,
ship_to_location_id,
need_by_date,
promised_date,
creation_date,
created_by,
last_update_date,
last_updated_by)
values
( po_line_locations_interface_s.nextval,
po_headers_interface_s.currval,
po_lines_interface_s.currval,
'STANDARD',
j.line_num,
j.destination_organization_id, -- ship_to_organizaiton_id
j.deliver_to_location_id, -- ship_to_location_id
j.need_by_date,
j.need_by_date,
sysdate,
28245,---9751,
sysdate,
28245 ); -- 9751 );
*/
----
END LOOP;
COMMIT;
DBMS_OUTPUT.put_line ('api_calling');
fnd_file.put_line (fnd_file.LOG, 'api calling');
FND_MSG_PUB.Initialize;
DBMS_OUTPUT.put_line (
'po_headers_interface_s.currvalis' || po_headers_interface_s.CURRVAL);
PO_INTERFACE_S.create_documents (
p_api_version => 1.0,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_batch_id => po_headers_interface_s.CURRVAL,
p_req_operating_unit_id => i.org_id, --–p_req_operating_unit_id,
p_purch_operating_unit_id => i.org_id, --–p_purch_operating_unit_id,
x_document_id => l_document_id,
x_number_lines => l_num_lines_processed,
x_document_number => l_document_number, --— <DBI FPJ>
p_sourcing_k_doc_type => NULL,
p_conterms_exist_flag => NULL,
p_document_creation_method => 'AUTOCREATE',
p_orig_org_id => NULL);
DBMS_OUTPUT.PUT_LINE (l_msg_data);
DBMS_OUTPUT.PUT_LINE (l_return_status);
DBMS_OUTPUT.PUT_LINE ('po header id created '||l_document_id);
IF l_return_status = 'S' THEN
SELECT segment1
INTO v_po_number
FROM po_headers_all
WHERE org_id =l_org_id
AND po_header_id = l_document_id;
DBMS_OUTPUT.PUT_LINE ('po number created '||v_po_number);
-- Get Primary Key/Sequence
SELECT l_document_id || '-' || po_wf_itemkey_s.NEXTVAL
INTO v_item_key
FROM dual;
---------------------------Issue#14104-----------------------------
SELECT nvl(A.EMAIL_ADDRESS,'jtektpo_arch@jtekt.com') INTO E_ID FROM AP_SUPPLIER_SITES_ALL A,PO_HEADERS_ALL B
WHERE A.VENDOR_ID=B.VENDOR_ID
AND A.VENDOR_SITE_ID=B.VENDOR_SITE_ID
AND A.ORG_ID=B.ORG_ID
AND B.PO_HEADER_ID=l_document_id ; --------- Against Issue#14104
begin
-- Seeded API
po_reqapproval_init1.start_wf_process(
ItemType => 'POAPPRV'
, ItemKey => v_item_key
, WorkflowProcess => 'BSDM_POAPPRV_TOP'---POAPPRV_TOP'
, ActionOriginatedFrom => 'PO_FORM'
, DocumentID => l_document_id
, DocumentNumber => NULL -- Purchase Order Number
, PreparerID => i.preparer_id -- Buyer/Preparer_id
, DocumentTypeCode => 'PO'
, DocumentSubtype => 'STANDARD'
, SubmitterAction => 'APPROVE'
, forwardToID => NULL
, forwardFromID => NULL
, DefaultApprovalPathID => NULL
, Note => NULL
, PrintFlag => 'N'
, FaxFlag => 'N'
, FaxNumber => NULL
, EmailFlag =>'Y'-- 'N'
, EmailAddress => e_id ----- Issue#14104
, 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;
end;
end if;
END LOOP;
END;
Comments
Post a Comment