Thursday, August 26, 2010

Purchase Order Interface

Purchase Order Interface -- PO_HEADERS_INTERFACE
Interface Tables Used

1)PO_HEADERS_INTERFACE
2)PO_LINES_INTERFACE
3)PO_DISTRIBUTIONS_INTERFACE

INSERT INTO po_headers_interface
(interface_header_id
,batch_id
,action
,org_id
,document_type_code
,vendor_id
,vendor_site_code
,vendor_site_id
,vendor_doc_num
,currency_code
,agent_id
,terms_id
,approval_status
,ship_to_location_id
,effective_date
,creation_date
,created_by
,last_update_date
,last_updated_by
,attribute_category
,attribute9 --xtra information
,comments
)
VALUES (po_headers_interface_s.NEXTVAL
,po_headers_interface_s.CURRVAL
,'ORIGINAL'
,g_org_id
,'STANDARD'
,rec_get_header_info.vendor_id
,rec_get_header_info.vendor_site_code
,rec_get_header_info.vendor_site_id
,po_headers_interface_s.CURRVAL
,'USD'
,l_agent_id
,rec_get_header_info.terms_id
,'APPROVED'
,rec_get_header_info.deliver_to_location_id
,TO_DATE (SYSDATE, 'DD-MON-RRRR')
,TO_DATE (SYSDATE, 'DD-MON-RRRR')
,fnd_profile.VALUE ('USER_ID')
,TO_DATE (SYSDATE, 'DD-MON-RRRR')
,fnd_profile.VALUE ('USER_ID')
,'DS Fields'
,rec_get_header_info.attribute9 --xtra information
,l_comments
)

INSERT INTO po_lines_interface
(interface_header_id
,interface_line_id
,requisition_line_id
,line_num
,shipment_num
,line_type_id
,item
,item_description
,category_id
,unit_of_measure
,quantity
,unit_price
,ship_to_organization_id
,line_location_id
,effective_date
,need_by_date
,creation_date
,created_by
,last_update_date
,last_updated_by
,taxable_flag
)
VALUES (po.po_headers_interface_s.CURRVAL
,po.po_lines_interface_s.NEXTVAL
,rec_get_line_info.requisition_line_id
,l_line_num
,1
,rec_get_line_info.line_type_id
,rec_get_line_info.segment1
,rec_get_line_info.item_description
,rec_get_line_info.category_id
,rec_get_line_info.primary_unit_of_measure
,rec_get_line_info.quantity
,rec_get_line_info.unit_price
,rec_get_line_info.destination_organization_id
, po_line_locations_s.NEXTVAL
,TO_DATE (SYSDATE, 'DD-MON-RRRR')
,TO_DATE (rec_get_line_info.need_by_date, 'DD-MON-RRRR')
,TO_DATE (SYSDATE, 'DD-MON-RRRR')
,fnd_profile.VALUE ('USER_ID')
,TO_DATE (SYSDATE, 'DD-MON-RRRR')
,fnd_profile.VALUE ('USER_ID')
,'Y'
);


INSERT INTO po.po_distributions_interface
(interface_header_id
,interface_line_id
,interface_distribution_id
,req_distribution_id
,org_id
,distribution_num
,quantity_ordered
,destination_organization_id
,destination_type_code
,destination_subinventory
, deliver_to_location_id
,set_of_books_id
,charge_account_id
,budget_account_id
,accrual_account_id
,variance_account_id
,deliver_to_person_id
,creation_date
,created_by
,last_update_date
,last_updated_by
)
VALUES (po.po_headers_interface_s.CURRVAL
,po.po_lines_interface_s.CURRVAL
,po.po_distributions_interface_s.NEXTVAL
,rec_get_distrib_info.distribution_id
,g_org_id
,rec_get_distrib_info.distribution_num
,rec_get_distrib_info.req_line_quantity
,rec_get_distrib_info.destination_organization_id
,rec_get_distrib_info.destination_type_code
,l_destination_subinventory
, rec_get_line_info.deliver_to_location_id
,rec_get_distrib_info.set_of_books_id
,rec_get_distrib_info.code_combination_id
,rec_get_distrib_info.budget_account_id
,l_new_accrual_account_id
,rec_get_distrib_info.variance_account_id
,rec_get_line_info.to_person_id
,TO_DATE (SYSDATE, 'DD-MON-RRRR')
,fnd_profile.VALUE ('USER_ID')
,TO_DATE (SYSDATE, 'DD-MON-RRRR')
,fnd_profile.VALUE ('USER_ID')
);

Concurrent Program:
apps.fnd_request.submit_request (application => 'PO' --Application,
program => 'POXPOPDOI'--Program,
argument1 => ''--Buyer ID,
argument2 => 'STANDARD'--Document Type,
argument3 => ''--Document Subtype,
argument4 => 'N'--Process Items Flag,
argument5 => 'N'--Create Sourcing rule,
argument6 => ''--Approval Status,
argument7 => ''--Release Generation Method,
argument8 => ''--NULL,
argument9 => g_org_id--Operating Unit ID,
argument10 => ''--Global Agreement
);

Calling PO Approval workflow to approve POs

SELECT TO_CHAR (po_wf_itemkey_s.NEXTVAL)
INTO v_wf_seq
FROM SYS.DUAL;

v_itemkey := TO_CHAR (l_header.po_header_id)
|| '-'
|| v_wf_seq;
po_reqapproval_init1.start_wf_process (itemtype => 'POAPPRV'
,itemkey => v_itemkey
,workflowprocess => 'POAPPRV_TOP'
,actionoriginatedfrom => 'PO_FORM'
,documentid => l_header.po_header_id
,documentnumber => l_header.segment1
,preparerid => l_header.agent_id
,documenttypecode => 'PO'
,documentsubtype => 'STANDARD'
,submitteraction => 'APPROVE'
,forwardtoid => NULL
,forwardfromid => l_header.agent_id
,defaultapprovalpathid => 10
,note => NULL
,printflag => 'N'
,faxflag => NULL
,faxnumber => NULL
);

1 comment:

Anand said...

is this information from r12 or 11i