AP Invoice Interface Sample Code

 AP Invoice Interface Sample Code

The Payables Open Interface tables store invoice and supplier information for import into Payables base tables.

The Invoice Open Interface Import program builds Payables invoices based on the invoice records in the Payables Open Interface tables. After the import program builds the invoices, they can be viewed, modified, and validated in the Invoice Workbench.

--------------------- AP Invoice Interface Sample Code --------------------

---------------------- STANDARD ------------------------

---------------- HEADER INVOICE Interface --------------

'STANDARD'

 INSERT INTO AP_INVOICES_INTERFACE

(INVOICE_ID, INVOICE_NUM, INVOICE_TYPE_LOOKUP_CODE, INVOICE_DATE, PO_NUMBER,

VENDOR_ID, VENDOR_NAME, VENDOR_SITE_CODE, INVOICE_AMOUNT,

INVOICE_CURRENCY_CODE, LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE

,CREATED_BY,LAST_UPDATE_LOGIN, ATTRIBUTE1, SOURCE, PAYMENT_CURRENCY_CODE,

--ORG_ID

OPERATING_UNIT

)

VALUES

(265414,'SYNH54','STANDARD',SYSDATE,200103,35165,'Synergy Healthcare',

'OLDHAM',  --'COLCHESTER'

117.50,

'GBP',

SYSDATE,0,SYSDATE,0,0,

'www.bbc.co.uk',

'INVOICE GATEWAY',

'GBP',

--7327

'SRI Supply Chain OU'

)

------------- LINES INVOICE Interface ------------

INSERT INTO AP_INVOICE_LINES_INTERFACE

(INVOICE_ID,INVOICE_LINE_ID,LINE_NUMBER,LINE_TYPE_LOOKUP_CODE,AMOUNT,

PO_NUMBER,PO_LINE_NUMBER,QUANTITY_INVOICED,UNIT_PRICE,LAST_UPDATE_DATE,LAST_UPDATED_BY,CREATION_DATE,CREATED_BY,ORG_ID )

VALUES

(265414,

3830744,

1,

'ITEM',

117.50,

200103,

1,

10,

10,

SYSDATE,0,SYSDATE,0,

7327

)

---------------------------  'CREDIT MEMO'  ----------------------------

 ---------------- HEADER INVOICE Interface ------------------

INSERT INTO AP_INVOICES_INTERFACE

(INVOICE_ID, INVOICE_NUM, INVOICE_TYPE_LOOKUP_CODE, INVOICE_DATE, PO_NUMBER,

VENDOR_ID, VENDOR_NAME, VENDOR_SITE_CODE, INVOICE_AMOUNT,

INVOICE_CURRENCY_CODE, LAST_UPDATE_DATE,LAST_UPDATED_BY,CREATION_DATE,

CREATED_BY,LAST_UPDATE_LOGIN, ATTRIBUTE1, SOURCE, PAYMENT_CURRENCY_CODE,

--ORG_ID

OPERATING_UNIT

)

VALUES

(265415,

'SYNH55',

'CREDIT',

SYSDATE,

200055,

35164,

'Smith & Nephew',

'FACTOR',

'-250',

'GBP',

SYSDATE,0,SYSDATE,0,0,

'www.bbc.co.uk',

'INVOICE GATEWAY',

'GBP',

--7327

'SRI Supply Chain OU'

)

------------- LINES INVOICE Interface ------------

INSERT INTO AP_INVOICE_LINES_INTERFACE

(INVOICE_ID, INVOICE_LINE_ID, LINE_NUMBER, LINE_TYPE_LOOKUP_CODE,

AMOUNT, PO_NUMBER, PO_LINE_NUMBER, QUANTITY_INVOICED, UNIT_PRICE, LAST_UPDATE_DATE,LAST_UPDATED_BY,CREATION_DATE,CREATED_BY,ORG_ID

)

VALUES

(265415,

3830745,

1,

'ITEM',

'-250',

200055, --200103,

1,

'-25',

10,

SYSDATE,0,SYSDATE,0,

7327

)

--------------------------- 'PREPAYMENT' -------------------------

---------------- HEADER INVOICE Interface ------------------

INSERT INTO AP_INVOICES_INTERFACE

(INVOICE_ID, INVOICE_NUM, INVOICE_TYPE_LOOKUP_CODE, INVOICE_DATE, PO_NUMBER,

VENDOR_ID, VENDOR_NAME, VENDOR_SITE_CODE, INVOICE_AMOUNT,

INVOICE_CURRENCY_CODE, LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN, ATTRIBUTE1, SOURCE, PAYMENT_CURRENCY_CODE,

--ORG_ID

OPERATING_UNIT

)

VALUES

(265417,

'SYNH57',

'PREPAYMENT',

SYSDATE,

200055,

35164,

'Smith & Nephew',

'FACTOR',

250,

'GBP',

SYSDATE,0,SYSDATE,0,0,

'www.bbc.co.uk',

'INVOICE GATEWAY',

'GBP',

--7327

'SRI Supply Chain OU'

)

------------- LINES INVOICE Interface ------------

INSERT INTO AP_INVOICE_LINES_INTERFACE

(INVOICE_ID, INVOICE_LINE_ID, LINE_NUMBER, LINE_TYPE_LOOKUP_CODE, AMOUNT,

PO_NUMBER, PO_LINE_NUMBER, QUANTITY_INVOICED, UNIT_PRICE, LAST_UPDATE_DATE,LAST_UPDATED_BY,CREATION_DATE,CREATED_BY,ORG_ID

)

VALUES

(265417, 

3830747,

1,

'ITEM',

250,

200055, --200103,

1,

25,

10,

SYSDATE,0,SYSDATE,0,

7327

) 

************************************

SELECT * FROM AP_INVOICES_INTERFACE --WHERE INVOICE_NUM LIKE 'SAN002 CR'

WHERE CREATION_DATE BETWEEN TO_DATE('01-JAN-08','DD-MON-YY')

      AND TO_DATE('30-AUG-08','DD-MON-YY') --AND INVOICE_ID IN '260382'

      --AND SOURCE LIKE 'INVOICE GATEWAY' and INVOICE_NUM IN 'SYNH22'

ORDER BY CREATION_DATE

/

SELECT * FROM AP_INVOICE_LINES_ALL

WHERE CREATION_DATE BETWEEN TO_DATE('01-JAN-08','DD-MON-YY')

      AND TO_DATE('30-AUG-08','DD-MON-YY')

      AND INVOICE_ID IN ('158353','156573','158374')

      --AND INVOICE_TYPE_LOOKUP_CODE LIKE 'CREDIT'

      --AND INVOICE_ID IN '260382'

     --AND SOURCE LIKE 'INVOICE GATEWAY' and INVOICE_NUM IN 'SYNH22'

ORDER BY CREATION_DATE

/

SELECT * FROM PO_HEADERS_ALL WHERE PO_HEADER_ID IN '82032'

SELECT * FROM PO_VENDORS WHERE VENDOR_ID IN '35165'

SELECT * FROM PO_VENDOR_SITES_ALL WHERE VENDOR_ID IN '35165'
-------------------

APXAPRVL   --> Invoice Import