Account Payable Interface Process Explanation

                                                             --Code By Sainaatth---


AP Invoice Interface : (Open Interface Approach)
=============
-- To import the legacy supplier invoices into Oracle Payables

Process :
----------
Flat Files (2)
     |
     |--> SQL *Loader (2)
     |
Staging Tables (2)
     |
     |--> PLSQL Package(1 Procedure, 2 Cursors, 2 For Loops, 2 Inserts)
     |
AP_INVOICES_INTERFACE
AP_INVOICE_LINES_INTERFACE
     |
     |--> Payables Open Interface Import
     |
AP_INVOICES_ALL
AP_INVOICE_LINES_ALL
AP_INVOICE_DISTRIBUTIONS_ALL

To Identify Interface Tables :
-----------------------------------
SELECT * FROM ALL_OBJECTS
WHERE OWNER = 'AP'
AND OBJECT_NAME LIKE 'AP%INV%INTERFACE%'
AND OBJECT_TYPE = 'TABLE'

AP_INVOICES_INTERFACE
AP_INVOICE_LINES_INTERFACE


Single Insert Process :
--------------------------
1. Identify the Mandatory columns from the Front End

At Header :
-------------
Invoice Type
Supplier Name
Supplier Number
Supplier Site
Invoice Date
Invoice Num
Invoice Currency Code
Invoice Amount
GL Date
Payment Currency Code
Terms Date
Terms Name
Payment Method

At Lines :
-----------
Line Num
Line Type
Line Amount
GL Date

At Distributions :
--------------------
Dist Num
Dist Type
Dist Amount
GL Date
Account

2. Identify the Mandatory Columns from Back End

SELECT COLUMN_NAME
FROM ALL_TAB_COLS
WHERE TABLE_NAME = 'AP_INVOICES_ALL'
AND OWNER = 'AP'
AND NULLABLE = 'N';

At AP_INVOICES_ALL :
---------------------------
GL_DATE
APPROVAL_READY_FLAG
WFAPPROVAL_STATUS
INVOICE_ID
LAST_UPDATE_DATE
LAST_UPDATED_BY
INVOICE_NUM
SET_OF_BOOKS_ID
INVOICE_CURRENCY_CODE
PAYMENT_CURRENCY_CODE
PAYMENT_CROSS_RATE

At AP_INVOICE_LINES_ALL :
----------------------------------
LAST_UPDATED_BY
LAST_UPDATE_DATE
INVOICE_ID
LINE_NUMBER
LINE_TYPE_LOOKUP_CODE
ACCOUNTING_DATE
SET_OF_BOOKS_ID
AMOUNT
WFAPPROVAL_STATUS

At AP_INVOICE_DISTRIBUTIONS_ALL :
-----------------------------------------------
INVOICE_DISTRIBUTION_ID
ACCOUNTING_DATE
ASSETS_ADDITION_FLAG
ASSETS_TRACKING_FLAG
DISTRIBUTION_LINE_NUMBER
DIST_CODE_COMBINATION_ID
INVOICE_ID
LAST_UPDATED_BY
LAST_UPDATE_DATE
LINE_TYPE_LOOKUP_CODE
PERIOD_NAME
SET_OF_BOOKS_ID

At AP_INVOICES_INTERFACE :
-------------------------------------
INVOICE_ID

At AP_INVOICE_LINES_INTERFACE :
--------------------------------------------
INVOICE_ID


3. Check Base table Mandatory columns are exist in Interface Table or not

AP_INVOICES_ALL : (Exist in AP_INVOICES_INTERFACE)
------------------------
 GL_DATE
,INVOICE_ID
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,INVOICE_NUM
,INVOICE_CURRENCY_CODE
,PAYMENT_CURRENCY_CODE
,PAYMENT_CROSS_RATE

AP_INVOICE_LINES_ALL & AP_INVOICE_DISTRIBUTIONS_ALL :
----------------------------------------------------------------------------
(Exist in AP_INVOICE_LINES_INTERFACE)

 LAST_UPDATED_BY
,LAST_UPDATE_DATE
,INVOICE_ID
,LINE_NUMBER
,LINE_TYPE_LOOKUP_CODE
,ACCOUNTING_DATE
,AMOUNT
,ACCOUNTING_DATE
,ASSETS_TRACKING_FLAG
,DIST_CODE_COMBINATION_ID
,INVOICE_ID
,LAST_UPDATED_BY
,LAST_UPDATE_DATE
,LINE_TYPE_LOOKUP_CODE

4. Remove the Duplicate columns from the 3rd step columns

AP_INVOICES_INTERFACE :
---------------------------------
 GL_DATE
,INVOICE_ID
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,INVOICE_NUM
,INVOICE_CURRENCY_CODE
,PAYMENT_CURRENCY_CODE
,PAYMENT_CROSS_RATE

AP_INVOICE_LINES_INTERFACE :
-----------------------------------------
 LAST_UPDATED_BY
,LAST_UPDATE_DATE
,INVOICE_ID
,LINE_NUMBER
,LINE_TYPE_LOOKUP_CODE
,ACCOUNTING_DATE
,AMOUNT
,ASSETS_TRACKING_FLAG
,DIST_CODE_COMBINATION_ID


5. Compare the 4th step (Back End Mandatory Columns) with 1st step columns
   (Front End Mandatory Columns)

Headers :
-----------
 INVOICE_NUM
,GL_DATE
,INVOICE_ID
,INVOICE_TYPE_LOOKUP_CODE
,VENDOR_ID
,VENDOR_SITE_ID
,INVOICE_DATE
,INVOICE_AMOUNT
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,INVOICE_CURRENCY_CODE
,PAYMENT_CURRENCY_CODE
,PAYMENT_CROSS_RATE
,TERMS_DATE
,TERMS_ID
,PAYMENT_METHOD_CODE
,ORG_ID
,SOURCE

Lines and Distributions :
----------------------------
 LAST_UPDATED_BY
,LAST_UPDATE_DATE
,INVOICE_ID
,LINE_NUMBER
,LINE_TYPE_LOOKUP_CODE
,ACCOUNTING_DATE
,AMOUNT
,DIST_CODE_COMBINATION_ID
,ORG_ID

6. Insert a Single Record into Interface Table then Submit Standard Program

INSERT INTO AP_INVOICES_INTERFACE
(
INVOICE_NUM
,GL_DATE,INVOICE_ID
,INVOICE_TYPE_LOOKUP_CODE,VENDOR_ID
,VENDOR_SITE_ID,INVOICE_DATE,INVOICE_AMOUNT
,LAST_UPDATE_DATE,LAST_UPDATED_BY
,INVOICE_CURRENCY_CODE,PAYMENT_CURRENCY_CODE
,PAYMENT_CROSS_RATE,TERMS_DATE
,TERMS_ID,PAYMENT_METHOD_CODE
,ORG_ID,SOURCE
)
VALUES
('TEST INV2 31MAY16'
,'31-MAY-2010', AP_INVOICES_INTERFACE_S.NEXTVAL
,'STANDARD', 600
,1414, '31-MAY-2010', 300
,SYSDATE, 1318
,'USD','USD'
,1, '31-MAY-2010'
,10002,'CHECK'
,204, 'ERS'
);

INSERT INTO AP_INVOICE_LINES_INTERFACE
(
LAST_UPDATED_BY
,LAST_UPDATE_DATE
,INVOICE_ID
,LINE_NUMBER
,LINE_TYPE_LOOKUP_CODE
,ACCOUNTING_DATE
,AMOUNT
,DIST_CODE_COMBINATION_ID
,ORG_ID
)
VALUES
(1318
,SYSDATE
,AP_INVOICES_INTERFACE_S.CURRVAL
,1
,'ITEM'
,'31-MAY-2010'
,300
,12854
,204
);

COMMIT;


SQL *Loader :
=========

'$1' - Used to pass parameter to the control file

XXAP_INV_HDR_LOAD_EXE
XXAP_INV_LINES_LOAD_EXE


XXAP Invoice Interface Header Loader Program

XXAP Invoice Interface Lines Loader Program


Note :
-------
When we are finding the Request Group name from Responsibility window,
Note the Request Group name along with Application



D:\oracle\VIS\apps\apps_st\appl\ap\12.0.0\bin

AP_INV_HDR_DATA.dat
AP_INV_LINE_DATA.dat


Interview Questions:
------------------------

1. Interface Process
(Note: Know the Tables(Int&Base), Standard Program with Parameters,
         Validations for all Interfaces and Conversions)

2. How to process Error Records
-- Generate a Report using Queries or Scripts
-- Give the rejected data to the Users or Clients
-- Users validate that data and correct it
   and they will place the new data/files into the server
-- Again run the programs for re process

DBA's will place the files into server

3. how will u get the flat file
custom_top/data/in/