Saturday, September 23, 2017

AP Invoice Import Program Technical Analysis

Payables open interface import

 Below diagram will give you the brief description of “Payables open interface import” programs process.


Required and optional columns of Payables open interface import of PO matched invoices (R12)

In this post, I tried to explain the required and optional columns in the payables invoice import tables for importing PO Matched Invoices.

 Table:  AP_INVOICE_HEADER_INTERFACE 

Column Name
Validation
INVOICE_ID                            
Populated from AP_INVOICES_INTERFACE_S.NEXTVAL
INVOICE_NUM                           
Must be unique to the supplier
PO_NUMBER
An approved, not cancelled, not closed or final closed PO
VENDOR_ID or VENDOR_NUM or VENDOR_NAME
An active vendor. Validated against AP_SUPPLIERS
VENDOR_SITE_ID or VENDOR_SITE_CODE
An active pay site. Validated against AP_SUPPLIER_SITES_ALL
INVOICE_AMOUNT                        
Positive amount for 'STANDARD' type, Negative amount for 'CREDIT' type
ORG_ID                                
Required in Multi-Org Environment. Validated against AP_SYSTEM_PARAMETERS.ORG_ID
SOURCE                                
Must be in
SELECT lookup_code
  FROM ap_lookup_codes
 WHERE lookup_type = 'SOURCE';
   
 Optional Columns 

    Column Name
Validation
INVOICE_DATE
Defaulted to SYSDATE, Can able to give manual invoice_Date
INVOICE_TYPE_LOOKUP_CODE              
Defaulted to 'STANDARD'. It can be 'STANDARD' or 'CREDIT'
INVOICE_CURRENCY_CODE                 
Defaulted from AP_SUPPLIER_SITES_ALL.
INVOICE_CURRENCY_CODE
EXCHANGE_RATE_TYPE                    
Defaulted from AP_SYSTEM_PARAMETERS.
DEFAULT_EXCHANGE_RATE_TYPE
TERMS_ID or TERMS_NAME                
Defaulted from
 AP_SUPPLIER_SITES_ALL.TERMS_ID
DOC_CATEGORY_CODE                      
Only populated if using automatic voucher number
PAYMENT_METHOD_LOOKUP_CODE
Defaulted from AP_SUPPLIER_SITES_ALL
.PAYMENT_METHOD_LOOKUP_CODE
PAY_GROUP_LOOKUP_CODE
Defaulted from AP_SUPPLIER_SITES_ALL.
PAY_GROUP_LOOKUP_CODE
ACCTS_PAY_CODE_COMBINATION_ID
Defaulted from AP_SUPPLIER_SITES_ALL.
ACCTS_PAY_CODE_COMBINAITON_ID
GROUP_ID
Group identifier. Suggest to use it
STATUS
DO NOT POPULATE IT

 Table:  AP_INVOICE_LINES_INTERFACE 

 Required Columns  
Column Name
Validation
INVOICE_ID                            
Populated from AP_INVOICES_INTERFACE.INVOICE_ID
INVOICE_LINE_ID                       
Populated from AP_INVOICE_LINES_INTERFACE_S.
NEXTVAL
LINE_NUMBER        
A unique number to the invoice
TAX_CODE or TAX_CODE_ID
Validated against AP_TAX_CODES_ALL
LINE_TYPE_LOOKUP_CODE                 
'ITEM'
AMOUNT                              
Should be QUANTITY_INVOICED * UNIT_PRICE
If MATCH_OPTION is 'P', then populate
RELEASE_NUM or PO_RELEASE_ID              
For Blanket Release only,
 validated against PO_RELEASES_ALL
PO_NUMBER or PO_HEADER_ID
Validated against PO_HEADER_ALL
PO_LINE_NUMBER or PO_LINE_ID
Validated against PO_LINES_ALL
PO_SHIPMENT_NUM or PO_LINE_LOCATION_ID    
Validated against PO_LINE_LOCATIONS_ALL
If MATCH_OPTION is 'R', then populate
RECEIPT_NUMBER                            
Validated against RCV_SHIPMENT_HEADERS.RECEIPT_NUM
RCV_TRANSACTION_ID or PO_LINE_LOCATION_ID
Validated against RCV_TRANSACTIONS

Optional Columns

Column Name
Validation
QUANTITY_INVOICED
Populated if different from PO shipment
UNIT_PRICE
Populated if different from PO shipment
MATCH_OPTION
'P' or 'R' or Defaulted from PO_VENDOR_SITES.MATCH_OPTION
ACCOUNTING_DATE
Defaulted from INVOICE_DATE or SYSDATE
FINAL_MATCH_FLAG
Populated 'Y' if it is final matching
INVENTORY_ITEM_ID
Validated against PO_LINES.INVENTORY_ITEM_ID
INVENTORY_DESCRIPTION
Validated against PO_LINES.INVENTORY_ITEM_DESCRIPTION
SHIP_TO_LOCATION_CODE
Populated if different from PO shipment
PRICE_CORRECTION_FLAG
Populated 'Y' if it is price correction

AP source setup 


Sample Scripts and impacted tables

-- get invoice source
SELECT lookup_code
  FROM ap_lookup_codes
 WHERE lookup_type = 'SOURCE'

-- get interface header input data 

SELECT APS.VENDOR_NAME,
       APS.VENDOR_ID,
       ASSA.VENDOR_SITE_ID,
       ASSA.VENDOR_SITE_CODE,
       ASSA.ACCTS_PAY_CODE_COMBINATION_ID,
       ASSA.PAY_GROUP_LOOKUP_CODE,
       ASSA.TERMS_ID,
       (SELECT NAME FROM AP_TERMS WHERE TERM_ID=ASSA.TERMS_ID) TERMS,
       ASSA.INVOICE_CURRENCY_CODE,
       ASSA.PAYMENT_CURRENCY_CODE,
       ASSA.ORG_ID
  FROM AP_SUPPLIERS APS, AP_SUPPLIER_SITES_ALL ASSA
 WHERE     APS.VENDOR_ID = ASSA.VENDOR_ID
       AND APS.VENDOR_NAME = 'McKesson General Medical'
       AND ASSA.ORG_ID=204
       AND ASSA.PAY_SITE_FLAG = 'Y'
       AND (ASSA.INACTIVE_DATE IS NULL OR ASSA.INACTIVE_DATE > SYSDATE)
       AND START_DATE_ACTIVE <= SYSDATE
       AND (END_DATE_ACTIVE IS NULL OR END_DATE_ACTIVE > SYSDATE)

insert into AP_INVOICES_INTERFACE (
            invoice_id,
            invoice_num,
            vendor_id,
            vendor_site_id,
            invoice_amount,
            INVOICE_CURRENCY_CODE,
            invoice_date,
            DESCRIPTION,
            PAY_GROUP_LOOKUP_CODE,
            source,
            org_id
                )
values (    ap_invoices_interface_s.nextval,
            'CINV107',
            '640',
            '1436',
            20.00,
            'USD',
            '30-DEC-2016',
            'This Invoice is created for test purpose',
            'Standard',
            'MANUAL INVOICE ENTRY',
            204
);

insert into AP_INVOICE_LINES_INTERFACE (
            invoice_id,INVOICE_LINE_ID,
            line_number,
            line_type_lookup_code,
            amount,DIST_CODE_COMBINATION_ID
            )
values     (
            ap_invoices_interface_s.currval,AP_INVOICE_LINES_INTERFACE_S.NEXTVAL,
            1,
            'ITEM',-- 'TAX','FREIGHT'
            20.00,12833
);


-- check already invoice is created for supplier,site combination to avoid duplicate invoice

SELECT * FROM AP_INVOICES_ALL WHERE VENDOR_ID=640 AND VENDOR_SITE_ID=1436 AND INVOICE_NUM='CINV107'



 -- To get clearing account 
SELECT code_combination_id,account_type
  FROM gl_code_combinations a
 WHERE 1=1-- account_type = 'E'
   AND enabled_flag = 'Y'
   AND summary_flag = 'N'
   AND SYSDATE BETWEEN NVL(start_date_active, SYSDATE - 1)
                   AND NVL(end_date_active, SYSDATE + 1)  --   and SEGMENT1='01'                                
                   and code_combination_id=12833


select ap_invoices_interface_s.CURRVAL from dual  -- INVOICE_ID

SELECT AP_INVOICE_LINES_INTERFACE_S.NEXTVAL FROM DUAL -- INVOICE_LINE_ID


select * from AP_INVOICES_INTERFACE where invoice_id=5173446

select * from ap_invoice_lines_interface where invoice_id=5173446

SELECT * FROM AP_INTERFACE_REJECTIONS WHERE PARENT_ID=5173446

select * from ap_batches_all where batch_name='CHIDAM'

--- Submit Payables Open Invoice Import Program

select * from ap_invoices_all where invoice_num='CINV108' AND BATCH_ID=471386

select * from ap_invoice_lines_all where invoice_id=566063

SELECT * FROM AP_PAYMENT_SCHEDULES_ALL where invoice_id=566062
-- data pop while import inv

-- Invoice Validation Program 

select ACCOUNTING_EVENT_ID,A.* from ap_invoice_distributions_all A where invoice_id=566063
--ACCOUNTING_EVENT_ID 99008166 -- No data populated while invoice import, should do the validation



SELECT *
  FROM xla.xla_transaction_entities
 WHERE     APPLICATION_ID = 200
       AND ENTITY_CODE = 'AP_INVOICES'
       AND source_id_int_1 = 566062 -- invoice_id, get entity_id
-- transaction_number -- invoice number

select * from xla_events where
EVENT_ID=6193570

--entity_id=6122423
-- get event_id


-- as of now event id and entity id is populated for invoice validation program

------------

-- ONCE CREATING ACCOUNTING PROGRAM IS RUN , BELOW SLA TABLES SHOULD IMPACT

select * from xla.xla_ae_headers where EVENT_ID=6193569

SELECT * FROM XLA.XLA_AE_LINES WHERE AE_HEADER_ID=8106568

entity_id=97975138


INVOICE ERROR CORRECTION screen

Can able to correct the invoice errors in backend using updation script or can correct in interface screen.



PAYABLES OPEN INTERFACE IMPORT PARAMETERS

In this below post, I had published information about the parameters related to “Payables Open Interface Import” Program. Info from R12.1.1 instance.



Mandatory Parameters:

1.       Source: It can be any of the below list
Ø      Invoice Gateway - Invoice records you entered in the Invoice Gateway window
Ø      E-Commerce Gateway- Supplier invoices transferred through EDI
Ø      Credit Card- Procurement cards transactions from the credit card issuer
Ø      Oracle Property Manager- Lease invoices from Oracle Property Manager.
Ø      Oracle Assets- Lease payments from Oracle Assets.
Ø      User-defined- For invoice loaded from legacy Systems
2.       Invoice Batch Name
Ø      Use Batch Control Payables Profile option need to be enabled to use this parameter
Ø      Payables groups the invoices created from the invoice records you import and creates an invoice batch with the batch name you enter
Ø      While Re-importing the error invoices make sure the batch name remains the same
Optional Parameters:
3.       Operating Unit: Filtering predicate when null import program process invoice from all operating units.
4.       Hold Name: When this parameter is provided with the hold name, then the import program creates the invoices and blocks them with mentioned hold.
5.       Hold Reason: Corresponds to Hold Name
6.       GL Date: This value is used as a GL date for the invoices whose GL_DATE columns in interface tables are not populated.
7.       Purge. Enter Yes if you want Payables to delete from the Payables Open Interface tables all successfully imported invoice records that match the Source and Group ID of this import
8.       Summarize Report: Provides a Summarize report when ‘YES’

No comments:

Post a Comment

Price List Query for Item

 SELECT qph.list_header_id,        qph.name,        qph.description,        qphh.start_date_active,        qphh.currency_code,        q...