Payables Open Interface for Project based Invoices

 Query for to get PO details:
 –select * from po_headers_all  where  PO_HEADER_ID=63845
–AND segment1=’NEX060378-002′
 –select * from po_lines_all where po_header_id=63845
–select * from po_vendors where vendor_id=11813
 –select * from po_line_locations_all where po_header_id=63845
  –select *  from PO_DISTRIBUTIONS_ALL
 –select * from ap_invoice_lines_interface
 –commit
Query to Set Org_id: 
–begin
–dbms_application_info.set_client_info(‘169’);
–end;

–select * from ap_invoices_interface where invoice_num like ‘COL%’
–select * from ap_invoice_lines_interface
— select * from  AP_TERMS
–select * from PO_DISTRIBUTIONS_ALL

DECLARE
    p_invoice_id       NUMBER;
    i                  NUMBER;
BEGIN

Query to Generate invoice_id —

    select AP_INVOICES_INTERFACE_S.nextval
    into p_invoice_id
    from dual;

Query to  Insert an invoice header —
 insert into ap_invoices_interface(INVOICE_ID,
                 INVOICE_NUM,
                   INVOICE_TYPE_LOOKUP_CODE,
                  INVOICE_DATE,
                 PO_NUMBER,
                 VENDOR_ID,
                 VENDOR_SITE_ID,
                   INVOICE_AMOUNT,
                 INVOICE_CURRENCY_CODE,
                  –TERMS_ID,
                   SOURCE,
                   GROUP_ID,
                PAYMENT_METHOD_LOOKUP_CODE,
                ORG_ID)
        VALUES  (p_invoice_id,         –INVOICE_ID,
                  ‘COL012’,              –INVOICE_NUM,
                  ‘STANDARD’,            –INVOICE_TYPE_LOOKUP_CODE,
                  sysdate,              –INVOICE_DATE,
                 ‘NEX060378-002’,                    –v_PO_NUMBER,
                11934,                 –v_VENDOR_ID, 11790
                14667,                 –v_VENDOR_SITE_ID, 14506
                10,                       –v_INVOICE_AMOUNT,  (Sanjiv Wrong Amt)10
               ‘USD’,                  — v_INVOICE_CURRENCY_CODE, usd
                –10011,                  –v_TERMS_ID, (Sanjiv)
                ‘CONVERSION’,–‘INVOICE GATEWAY’,       –v_SOURCE,
                 ‘USM’,                  — v_GROUP_ID,
               ”,                     — v_PAYMENT_METHOD_LOOKUP_CODE, CHECK
               169);                   –v_ORG_ID
    — Insert invoice line —

    for i in 1..1 loop
INSERT into ap_invoice_lines_interface (INVOICE_ID,
                                INVOICE_LINE_ID,
                                LINE_NUMBER,
                                LINE_TYPE_LOOKUP_CODE,
                                AMOUNT,
                                ACCOUNTING_DATE,
                                FINAL_MATCH_FLAG,
                                PO_HEADER_ID,
                                PO_NUMBER,
                                 PO_LINE_ID,
                                 PO_LINE_NUMBER,
                                PO_LINE_LOCATION_ID,
                                PO_SHIPMENT_NUM,
                                INVENTORY_ITEM_ID,
                                ITEM_DESCRIPTION,
                                QUANTITY_INVOICED,
                                SHIP_TO_LOCATION_CODE,
                                UNIT_PRICE,
                                PO_RELEASE_ID,
                                RELEASE_NUM,
                                PRICE_CORRECTION_FLAG,
                                RECEIPT_NUMBER,
                                MATCH_OPTION,
                                RCV_TRANSACTION_ID,
                                DIST_CODE_COMBINATION_ID,
                                PO_DISTRIBUTION_ID,
                                PO_DISTRIBUTION_NUM,
                                PROJECT_ID,
                                TASK_ID,
                                EXPENDITURE_TYPE,
                                EXPENDITURE_ORGANIZATION_ID,
                                PA_QUANTITY,
                                EXPENDITURE_ITEM_DATE
                                )
                        VALUES (
                                 p_invoice_id,     –v_INVOICE_ID,
                                 AP_INVOICE_LINES_INTERFACE_S.nextval,       –v_INVOICE_LINE_ID,
                                     i,               –v_LINE_NUMBER,  1
                                  ‘ITEM’,           — v_LINE_TYPE_LOOKUP_CODE,
                                   10,            –v_AMOUNT, (Sanjiv Wrong Amt, PO Line=82.27) 10
                                  sysdate,           — v_ACCOUNTING_DATE,
                                  ”,                –v_FINAL_MATCH_FLAG,
                                  63845,                –v_PO_HEADER_ID,
                                 ‘NEX060378-002’,             — v_PO_NUMBER,
                                 63846,              –v_PO_LINE_ID, (Sanjiv Wrong POLineID) 47245
                                  1,               –v_PO_LINE_NUMBER,
                                  63846,           –v_PO_LINE_LOCATION_ID, (Sanjiv Wrong POLineLocID)
                                    ”,                –v_PO_SHIPMENT_NUM,
                                  ”,                –v_INVENTORY_ITEM_ID,
                                  ”,                –v_ITEM_DESCRIPTION,
                                  1,                –v_QUANTITY_INVOICED,
                                  ”,                –v_SHIP_TO_LOCATION_CODE,
                                 ”,                 –v_UNIT_PRICE,
                                ”,                 –v_PO_RELEASE_ID,
                                ”,                 –v_RELEASE_NUM,
                                   ”,                 –v_PRICE_CORRECTION_FLAG,
                                ”,                 –v_RECEIPT_NUMBER,
                                   ”,                –v_MATCH_OPTION,
                                   ”,                 –v_RCV_TRANSACTION_ID
                                ”,           –v_DIST_CODE_COMBINATION_ID
                                64302,            –PO_DISTRIBUTION_ID = 64302
                                1,                  –PO_DISTRIBUTION_NUM
                                ”,–1087,               –PROJECT_ID, (Sanjiv Wrong ProjID) 1172 1087
                                ”,–1551,                –TASK_ID,
                                ”,–‘Awrd Ovation Point Redemption’,–EXPENDITURE_TYPE,
                                 ”,–169,                –EXPENDITURE_ORGANIZATION_ID,
                                 ”,–”,             –PA_QUANTITY, (Sanjiv Wrong Qty)10
                              ” );– sysdate );           –EXPENDITURE_ITEM_DATE
      end loop;

 commit;
END;

Queries to debugged and trace Interface errors: 

–select * from ap_invoices_interface where invoice_id=13634
–select * from ap_invoice_lines_interface where invoice_id=13634
–select * from user_errors where name like’USM%’;
— select * from pa_projects_all where project_id=1172 –1087
–select * from pa_projects_all where project_id in (select project_id from pa_tasks where task_number=’2.4′)
–10437
–select * from usm_po_codes
–select * from pa_projects_all where segment1 =’10437′