PO AutoCreate API

The purpose of the API is to create Approved Purchase Orders automatically from all valid internal requisitions. This API call’s several unpublished Oracle API’s, so please test full cycle on test instance first

Process Logic:

• Collect all the APPROVED Internal requisitions witch are not actually transformed into Purchase orders
• Insert into purchase Order Interface tables
• Create Purace Orders group by supplier  and APPROVE

Exceptions

• Purchased order will not be approved if the supplier information are missing in the requisition
• All other API’ related exceptions are handled.

sample code from Oracle Support

declare
l_interface_header_id number;
l_interface_line_id number;

l_po_req_line_id number := 1433626;
l_po_req_line_num number :=1;
l_process_code varchar2(20) := ‘NEW’; — NEW in case of creation of new PO, ADD incase of adding line to PO
l_action varchar2(20) := ‘NEW’; — NEW in case of creation of new PO, ADD incase of adding line to PO
l_document_id number := NULL; — assign PO number in case of adding lines to existing PO…
l_document_num number := NULL; — assign PO number in case of adding lines to existing PO…
l_agent_id number := 236;
l_vendor_id number;
l_vendor_site_id number;

—
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;

begin
mo_global.init(‘PO’);
mo_global.set_policy_context(‘S’,84);

SELECT po_headers_interface_s.nextval
INTO l_interface_header_id
FROM dual;

select vendor_id,vendor_site_id–,agent_id
into l_vendor_id, l_vendor_site_id
from po_requisition_lines_all
where requisition_line_id= l_po_req_line_id;

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
)
VALUES
( l_interface_header_id — –interface_header_id
,’PO’ — — interface_source_code
,l_interface_header_id — — x_batch_id
,l_process_code — — process code
,l_action — — action
,’PO’ — — x_document_type
,’STANDARD’ — — document_subtype
,l_document_num — — document_num
— ,p_freight_terms
— ,p_FOB
–,payment_terms
,’DEFAULT’ — — ‘DEFAULT’ / ‘REQUISITION’
,l_vendor_id — — x_vendor_id
,l_vendor_site_id — — x_vendor_site_id
,l_agent_id — — x_agent_id
,’USD’ — — x_currency_code
,SYSDATE — — x_creation_date
,-1 — — x_created_by
,SYSDATE — — x_last_update_date
,-1 — — x_last_updated_by
,1 — style_id
);

SELECT po_lines_interface_s.nextval
INTO l_interface_line_id
FROM dual;
INSERT INTO po_lines_interface
( interface_header_id
,interface_line_id
,requisition_line_id
,line_num
,creation_date
,created_by
,last_update_date
,last_updated_by
)
VALUES
( l_interface_header_id — x_interface_header_id,
,l_interface_line_id — x_interface_line_id,
,l_po_req_line_id — x_requisition_line_id, from req lines
,l_po_req_line_num — x_so_line_num, from SO lines
,SYSDATE — x_creation_date
,-1 — x_created_by
,SYSDATE — x_last_update_date
,-1 — x_last_updated_by
);

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 => l_interface_header_id,
p_req_operating_unit_id => 84,–p_req_operating_unit_id,
p_purch_operating_unit_id => 84,–p_purch_operating_unit_id,
x_document_id => l_document_id,
x_number_lines => l_num_lines_processed,
x_document_number => l_document_number,
p_document_creation_method => ‘AUTOCREATE’ — <DBI FPJ>
,p_sourcing_k_doc_type => null
,p_conterms_exist_flag => null
,p_orig_org_id => null
);
dbms_output.put_line (‘l_return_status: ‘ || l_return_status);
dbms_output.put_line (‘l_msg_count: ‘ || l_msg_count);
dbms_output.put_line (‘l_msg_data:’ || substr(l_msg_data,1,240));
dbms_output.put_line (‘l_document_id: ‘ || l_document_id);
dbms_output.put_line (‘l_document_number: ‘ || l_document_number);
end;