Oracle Purchase Order using Interface po_headers_interface

Create Purchase Orders using standard Oracle Interface:


 
CREATE OR REPLACE PROCEDURE xxx_parts_order_create_po (p_vendor_id IN NUMBER,p_vendor_site_id IN NUMBER,p_currency_code IN VARCHAR2,p_po_type IN VARCHAR2,p_inco_terms IN VARCHAR2)
AUTHID
IS CURRENT_USERx_po_header_id NUMBER;x_po_line_id NUMBER;l_organization_id NUMBER;ln_amount NUMBER;ln_list_price NUMBER;ln_unit_cost NUMBER;ln_cust_price NUMBER;ln_op_unit_cost NUMBER;
CURSOR c2IS
SELECT inventory_item_id, item_name, item_desc description-- , 1000 amount --Put calculated value here,uom primary_uom_code

, dealer_po_line_id, order_quantityFROM gaa_dealer_po_creationWHERE NVL (create_po, 'X') = 'Y' AND NVL (po_created, 'X') <> 'Y';wc2 c2%ROWTYPE;l_user_emp_id NUMBER;l_item_account_id NUMBER;l_location_id NUMBER;l_user_id NUMBER;l_incident_number NUMBER;l_org_id NUMBER;l_sob_id NUMBER;l_sob_currency VARCHAR2 (15);l_line_no NUMBER;l_request_id
BEGINNUMBER;l_user_id := fnd_profile.VALUE ('USER_ID');l_org_id := fnd_profile.VALUE ('ORG_ID');l_organization_id := fnd_profile.VALUE ('GAA_ORGANIZATION_ID');l_sob_id := fnd_profile.VALUE ('GL_SET_OF_BKS_ID');BEGIN


SELECT usr.employee_idINTO l_user_emp_idFROM fnd_user usrWHERE usr.user_id = fnd_global.user_id;EXCEPTIONWHEN OTHERSTHENNULL;END;BEGINSELECT MAX (loc.location_id)

INTO l_location_idFROM hr_locations locWHERE loc.inventory_organization_id = l_organization_id;EXCEPTIONWHEN OTHERSTHENNULL;END;BEGIN


SELECT iac.material_accountINTO l_item_account_idFROM mtl_parameters iacWHERE iac.organization_id = l_organization_id;EXCEPTIONWHEN OTHERSTHENNULL;END;SELECT po_headers_interface_s.NEXTVAL
INTO x_po_header_idFROM DUAL;
INSERT INTO po_headers_interface(interface_header_id, document_type_code, vendor_id,vendor_site_id, currency_code, approval_required_flag,org_id, agent_id, action, interface_source_code, batch_id,attribute1
, attribute2)VALUES (x_po_header_id, 'STANDARD', p_vendor_id,p_vendor_site_id, p_currency_code, 'N',l_org_id, l_user_emp_id, 'ORIGINAL', 'VEH', 1,p_po_type
, p_inco_terms);OPEN c2;LOOP
FETCH c2INTO wc2;EXIT WHEN c2%NOTFOUND;BEGIN



SELECT PLV.unit_price, PLV.attribute3INTO ln_amount, ln_list_priceFROM po_lines_v PLVWHERE PLV.item_number = wc2.item_nameAND po_line_id =(SELECT MAX (pl.po_line_id)


FROM po_lines_v pl, po_headers_all phWHERE pl.item_number = PLV.item_numberAND ph.po_header_id = pl.po_header_idAND ph.type_lookup_code = 'QUOTATION'

AND ph.vendor_id = p_vendor_idAND ph.vendor_site_id = p_vendor_site_idAND ph.currency_code = p_currency_code);EXCEPTIONWHEN OTHERSTHENNULL;END;l_line_no := NVL (l_line_no, 0) + 1;SELECT po_lines_interface_s.NEXTVAL
INTO x_po_line_idFROM DUAL;
INSERT INTO po_lines_interface(interface_line_id, interface_header_id, line_type_id,item_id, item_description, unit_price,uom_code, quantity, note_to_vendor,shipment_num, promised_date, line_num, line_attribute14,line_attribute3
)VALUES (x_po_line_id, x_po_header_id, 1,wc2.inventory_item_id, wc2.description, ln_amount,wc2.primary_uom_code, wc2.order_quantity, wc2.description,l_line_no, SYSDATE, l_line_no, wc2.dealer_po_line_id,ln_list_price
);
INSERT INTO po_distributions_interface(interface_line_id, interface_header_id,interface_distribution_id, distribution_num,quantity_ordered
, charge_account_id, org_id)VALUES (x_po_line_id, x_po_header_id,po_distributions_interface_s.NEXTVAL, l_line_no,1
, l_item_account_id, l_org_id);
UPDATE gaa_dealer_po_creationSET po_created = 'Y'WHERE dealer_po_line_id = wc2.dealer_po_line_id;END LOOP;CLOSE c2;COMMIT;l_request_id :=fnd_request.submit_request ('PO','POXPOPDOI',NULL,NULL,FALSE,'','STANDARD','','N','','APPROVED','',1,'','',CHR (0));
ENDCOMMIT; xxx_parts_order_create_po;This was done on Oracle Apps 11.5.10.2