Oracle Forms Personalization Tables

Oracle Forms Personalization Tables:



SELECT * FROM FND_FORM_CUSTOM_RULES

SELECT * FROM FND_FORM_CUSTOM_ACTIONS

SELECT * FROM FND_FORM_CUSTOM_SCOPES

SELECT * FROM FND_FORM_CUSTOM_PARAMS

SELECT * FROM FND_FORM_CUSTOM_PROP_LIST

SELECT * FROM FND_FORM_CUSTOM_PROP_VALUES

Payables Open Interface Import program in Oracle apps r12

ACCOUNTS PAYABLE:

In order to create invoice automatically in oracle Accounts Payables, import data in following interface tables:

Interface Tables:

1. AP_INVOICES_INTERFACE

2. AP_INVOICE_LINES_INTERFACE

AP_INVOICES_INTERFACE:

Data in the AP_INVOICES_INTERFACE table used in conjunction with AP_INVOICE_LINES_INTERFACE table to create Payables Invoice, Invoice lines, Distributions and Schedule payments. Data in this table can be viewed and edited using ‘Open Interface Invoices’ window. The Payables Open Interface program validates each record in this interface table selected for import, and if the record contains valid data then the program creates a Payables Invoice.

AP_INVOICE_LINES_INTERFACE:

AP_INVOICE_LINES_INTERFACE stores information used to create one or more invoice distributions. Note that one row in this table may create, during the import process, more than one invoice distribution.

Interface Error table:

1. AP_INTERFACE_REJECTIONS

2. AP_INTERFACE_CONTROLS

AP_INTERFACE_REJECTIONS stores information about invoice data from the AP_INVOICES_INTERFACE and AP_INVOICE_LINES_INTERFACE tables which could not be processed by Payables Open Interface Import.

SELECT * FROM AP_INTERFACE_REJECTIONS

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

After successfully importing data in above interface tables, call the following concurrent program in order to create invoices in AP:

Request:

Payables Open Interface Import

This request after successfully run creates invoices in oracle payables.

Payables Open Interface Import commits in cycles of 100. For example, if there is an error that stops the import program when it is validating the 350th invoice record, the system will have already committed the first 300 invoices.

Base Tables:

AP_INVOICES_ALL

AP_INVOICE_DISTRIBUTIONS_ALL

AP_PAYMENT_SCHEDULES_ALL

Invoice Validation:

Once the Invoices have been created invoice validation needs to be done so that invoices can be processed further to make payments and transfer to GL. For this we need to either manually validate individual invoices from the Invoice Screen or run the Invoice Validation Program. This activity will validate the invoices and allow further processing, In case invoices fail in validation then the invoices will be put on hold. The user can see the hold details by pressing the "HOLDS" button on the invoice screen. At the backend hold details will be stored in the table: AP_INVOICE_HOLDS

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

**Payables Open Interface Outbound Advice -> standalone report to be manually submitted by the user.
This program creates notification data about e-Commerce invoices processed during Payables Open Interface Import.

**Payables Open Interface Purge -> standalone report to be manually submitted by the user.
This report is used to purge records from the Payables Open Interface tables (AP_INVOICES_INTERFACE and AP_INVOICE_LINES_INTERFACE).
We strongly recommend to purge only invoices successfully imported setting the 'Purge All' parameter to No. Setting this parameter to Yes purges all records in the Payables Open Interface tables.

Joins Between Oracle Apps Tables (GL, AP, AR, Purchasing)

Joins Between Oracle Apps Tables Like General Ledger(GL) , Account Payable(AP), Account Receivables(AR), Purchasing Order(PO) ETC

Joins:
GL   AND   AP
GL_CODE_COMBINATIONS                         AP_INVOICES_ALL
code_combination_id                          =              acct_pay_code_combination_id
GL_CODE_COMBINATIONS                         AP_INVOICES_DISTRIBUTIONS_ALL
code_combination_id                           =             dist_code_combination_id
GL_SETS_OF_BOOKS                                      AP_INVOICES_ALL
set_of_books_id                                   =             set_of_books_id
GL   AND AR
GL_CODE_COMBINATIONS                        RA_CUST_TRX_LINE__GL_DIST_ALL
code_combination_id                            =             code_combination_id
GL   AND INV
GL_CODE_COMBINATIONS                        MTL_SYSTEM_ITEMS_B
code_combination_id                              =          cost_of_sales_account
GL   AND PO
GL_CODE_COMBINATIONS                        PO_DISTRIBUTIONS_ALL
code_combination_id                             =           code_combination_id
PO AND AP
PO_DISTRIBUTIONS_ALL                              AP_INVOICE_DISTRIBUTIONS_ALL
Po_distribution_id                                  =             po_distribution_id
PO_VENDORS                                                   AP_INVOICES_ALL
vendor_id                                              =              vendor_id
PO AND SHIPMENTS
PO_HEADERS_ALL                                           RCV_TRANSACTIONS
Po_header_id                                       =              po_header_id
PO_DISTRIBUTIONS_ALL                              RCV_TRANSACTIONS
Po_distribution_id                                 =             po_distribution_id
SHIPMENTS AND INVOICE
RCV_TRANSACTIONS                                   AP_INVOICE_DISTRIBUTIONS_ALL
RCV_TRANSACTION_ID                  =          RCV_TRANSACTION_ID
PO AND  INV
PO_REQUISITION_LINES_ALL                  MTL_SYSTEM_ITEMS_B
item_id                                     =           inventory_item_id
org_id                                                 =           organization_id
PO AND HRMS
PO_HEADERS_ALL                                        HR_EMPLOYEES
Agent_id                                             =             employee_id
PO AND REQUISITION
PO_DISTRIBUTIONS_ALL                            PO_REQ_DISTRIBUTIONS_ALL
req_distribution_id                               =             distribution_id
SHIPMENTS AND INV
RCV_TRANSACTIONS                                  MTL_SYSTEM_ITEMS_B
Organization_id                                   =             organization_id
INV AND HRMS
MTL_SYSTEM_ITEMS_B                                HR_EMPLOYEES
buyer_id                                              =             employee_id
OM  AND  AR
OE_ORDER_HEADERS_ALL                          RA_CUSTOMER_TRX_LINES_ALL
TO_CHAR( Order_number)              =               interface_line_attribute1
OE_ORDER_LINES_ALL                                RA_CUSTOMER_TRX_LINES_ALL
TO_CHAR(Line_id)                         =                 interface_line_attribute6
OE_ORDER_LINES_ALL                                 RA_CUSTOMER_TRX_LINES_ALL
reference_customer_trx_line_id          =                customer_trx_line_id
OM AND SHIPPING
OE_ORDER_HEADERS_ALL                             WSH_DELIVARY_DETAILS
HEADER_ID                                        =             SOURCE_HEADER_ID
OE_ORDER_HEADERS_ALL                            WSH_DELIVARY_DETAILS
LINE_ID                                             =              SOURCE_LINE_ID
AP AND AR (BANKS)
AR_CASH_RECEIPTS_ALL                               AP_BANK_ACCOUNTS
REMITTANCE_BANK_ACCOUNT_ID   =                ABA.BANK_ACCOUNT_ID
AP AND AR
HZ_PARTIES                                                   AP_INVOICES_ALL
PARTY_ID                                        =               PARTY_ID
OM AND CRM
OE_ORDER_LINES_ALL                              CSI_ITEM_INSTANCES(Install Base)
LINE_ID                                       =               LAST_OE_ORDER_LINE_ID
Po_Requisition_Headers_All
Column Names                   Table Name                                  Column Name
REQUISITION_HEADER_IDPO_REQUISITION_LINES_ALL    REQUISITION_HEADER_ID
TYPE_LOOKUP_CODE        PO_DOCUMENT_TYPES             DOCUMENT_SUBTYPE
PREPARER_ID                   PER_PEOPLE_F                           PERSON_ID
ORG_ID                             MTL_SYSTEM_ITEMS               ORGANIZATION_ID
ORG_ID                             MTL_ORGANIZATIONS            ORGANIZATION_ID
Po_Requisition_Lines_All
Column Names                   Table Name                              Column Name
REQUISITION_HEADER_ID   PO_REQUISITION_HEADERS_ALL  REQUISITION_HEADER_ID
REQUISITION_LINE_ID      PO_REQ_DISTRIBUTIONS_ALL          REQUISITION_LINE_ID
LINE_TYPE_ID               PO_LINE_TYPES                                    LINE_TYPE_ID
ITEM_ID                          MTL_SYSTEM_ITEMS                        INVENTORY_ITEM_ID
ORG_ID                           MTL_SYSTEM_ITEMS                        ORGANIZATION_ID
Po_Requisition_Distributions_All
Column Names                      Table Name                                Column Name
REQUISITION_LINE_ID      PO_REQUISITION_LINES_ALL     REQUISITION_LINE_ID
DISTRIBUTION_ID            PO_DISTRIBUTIONS_ALL            REQ_DISTRIBUTION_ID
SET_OF_BOOKS_ID          GL_SETS_OF_BOOKS                     SET_OF_BOOKS_ID
CODE_COMBINATION_ID  GL_CODE-COMBINATIONS            CODE_COMBINATION_ID
Po_Distributions_All
Column Names                   Table Name                                         Column Name
PO_LINE_ID                   PO_LINES                                          PO_LINE_ID
REQ_DISTRIBUTION_ID   PO_REQ_DISTRIBUTIONS_ALL       DISTRIBUTION_ID
PO_DISTRIBUTION_ID    AP_INVOICE_DISTRIBUTIONS_ALL PO_DISTRIBUTION_ID
Po_Headers_All
Column Names                   Table Name                              Column Name
PO_HEADER_ID                PO_LINES                                 PO_HEADER_ID
PO_HEADER_ID                RCV_SHIPMENT_LINES           PO_HEADER_ID
VENDOR_ID                      PO_VENDORS                          VENDOR_ID
AGENT_ID                       PER_PEOPLE                           PERSON_ID
TYPE_LOOK_UP_CODE     PO_DOCUMENT_TYPES            DOCUMENT_SUBTYPE
Po_Lines_All
Column Names                   Table Name                                    Column Name
PO_HEADER_ID               PO_HEADERS                                PO_HEADER_ID
PO_LINE_ID                     PO_DISTRIBUTIONS_ALL           PO_LINE_ID
ITEM_ID                          MTL_SYSTEM_ITEMS                   ITEM_ID
Rcv_Shipment_Lines
Column Names                   Table Name                                  Column Name
PO_HEADER_ID                PO_HEADERS                              PO_HEADER_ID
SHIPMENT_HEADER_ID   RCV_SHIPMENT_HEADERS  SHIPMENT_HEADER_ID
Ap_Invoices_All
Column Names       Table Name                                          Column Name
INVOICE_ID             AP_INVOICE_DISTRIBUTIONS_ALL  INVOICE_ID
Oe_Order_Headers_All
Column Names                   Table Name                      Column Name
HEADER_ID                        OE_ORDER_LINES                   HEADER_ID
SOURCE_HEADER_ID          WISH_DELIVERY_DETAILS     SOURCE_HEADER_ID
PRICE_LIST_ID                   QP_LIST_HEADERS_TL           LIST_HEADER_ID
ORG_ID                              MTL_ORGANIZATIONS           ORGANIZATION_ID
SALESREP_ID                      JTF_RS_SALESREPS               SALESREP_ID
ORDER_TYPE_ID                  OE_TRANSACTION_TYPES     TRANSACTION_TYPE_ID
ORDER_SOURCE_ID             OE_ORDER_SOURCES             ORDER_SOURCE_ID
ACCOUNTING_RULE_ID        RA_RULES                               RULE_ID
PAYMENT_TERM_ID              RA_TERMS                              TERM_ID
SOLD_TO_ORG_ID               HZ_CUST_ACCOUNTS             CUST_ACCOUNT_ID
SHIP_FROM_ORG_ID            MTL_PARAMETERS                 ORGANIZATION_ID
SHIP_TO_ORG_ID                HZ_CUST_SITE_USES_ALL    SITE_USE_ID
Oe_Order_Lines_All
Column Names       Table Name                             Column Name
LINE_TYPE_ID            OE_TRANSACTION_TYPES_TL     TRANSACTION_TYPE_ID
INVOICING_RULE_ID  RA_RULES                                     RULE_ID
Hz_Parties
Column Names       Table Name                         Column Name
PATY_ID                    HZ_CUST_ACCOUNTS        PATY_ID
CUST_ACCOUNT_ID   OE_ORDER_LINES             SOLD_TO_ORG_ID
Hz_Party_Sites_All
Column Names                   Table Name                Column Name
PATY_ID                              HZ_PARTIES               PATY_ID
LOCATION_ID                     HZ_LOCATIONS         LOCATION_ID
Wsh_delivery_details
Column Names                 Table Name                              Column Name
SOURCE_HEADER_ID         OE_ORDER_HEADERS                    SOURCE_HEADER_ID
DELIVERY_DETAIL_ID        WSH_DELIVERY_ASSIGNMENTS   DELIVERY_DETAIL_ID
DELIVERY_ID                    WSH_NEW_DELIVERIES                DELIVERY_ID
INVENTORY_ITEM_ID        MTL_SYSTEM_ITEMS                     INVENTORY_ITEM_ID
RA_CUSTOMER_TRX_ALL
Column Names           Table Name                                    Column Name
CUSTOMER_TRX_ID     AR_RECEIVABLE_APPLICATIONS_ALL APPLIED_CUSTOMER_TRX_ID
TERM_ID                     RA_TERMS                                           TERM_ID
CUSTOMER_TRX_ID    RA_CUST_TRX_LINE_GL_DIST        CUSTOMER_TRX_ID
AR_CASH_REC EIPTS_ALL
Column Names                   Table Name                                   Column Name
CASH_RECEIPT_ID               AR_RECEIVABLE_APPLICATIONS_ALL  CASH_RECEIPT_ID
SET_OF_BOOKS_ID             GL_SETS_OF_BOOKS                               SET_OF_BOOKS_ID

credit goes to:
http://oracleebspro.blogspot.com/2013/04/joins-between-oracle-apps-tables-like.html