, , , , ,

Finding all unapplied receipts in AR

Select a.cash_receipt_id, a.receipt_number, a.Amount, SUM(b.AMOUNT_APPLIED) AS “Unapplied”
from apps. AR_CASH_RECEIPTS_ALL a,
apps. AR_RECEIVABLE_APPLICATIONS_ALL b
WHERE a.ORG_ID=’118′
AND a.CURRENCY_CODE=’USD’
AND a.cash_receipt_id=b.cash_receipt_id
AND b.STATUS=’UNAPP’
GROUP BY a.cash_receipt_id, a.receipt_number, a.Amount
/by
, , , ,

Important Account Receivables Tables

RA_CUSTOMER_TRX_ALL 
Table Or View: Table
Important columns: 

CUSTOMER_TRX_ID
BATCH_SOURCE_ID
TRX_NUMBER

Description: 

Header-level information about invoices, debit memos, chargebacks, commitments and credit memos.

RA_CUSTOMER_TRX_LINES_ALL  
 Table Or View: View

Important columns: 

CUSTOMER_TRX_ID
CUSTOMER_TRX_LINE_ID
LINE_TYPE : Line, TAX or Freight
LINE_NUMBER
INVENTORY_ITEM_ID
QUANTITY_ORDERED
QUANTITY_CREDITED
QUANTITY_INVOICED
UNIT_STANDARD_PRICE
UNIT_SELLING_PRICE
SALES_ORDER
SALES_ORDER_REVISION
SALES_ORDER_LINE
TAX_EXEMPT_FLAG
SALES_TAX_ID
WAREHOUSE_ID
ORG_ID : Organization identifier

Description: 

RA_CUSTOMER_TRX_LINES_ALL contians the line level information of invoices (header informations are stored in RA_CUSTOMER_TRX_ALL).
This view contains information for all the type of lines : Line, TAX and Freight.

RA_CUST_TRX_TYPES_ALL

Table Or View: Table
Important columns: 

CUST_TRX_TYPE_ID NUMBER : Transaction type identifier
NAME : Transaction type Name
TYPE
POST_TO_GL : Indicates if transactions should be posted to the General Ledger. Y for yes, N otherwise.
ACCOUNTING_AFFECT_FLAG : Indicates if transactions affect open receivable balances. Y for yes, N otherwise.
CREDIT_MEMO_TYPE_ID : Transaction type identifier of subsequent credit memos
ORG_ID NUMBER : Organization identifier

Description: 


The RA_CUST_TRX_TYPES_ALL table stores information about each transaction type that is used for invoices, commitments, bills receivable, and credit memos. Each row includes AutoAccounting information as well as standard defaults for the invoices that result.
The POST_TO_GL column stores Y or N to indicate if this transaction can post to your General Ledger.
The ACCOUNTING_AFFECT_FLAG column stores Y or N to indicate if this transaction can update your open receivables balances. If the ACCOUNTING_AFFECT_FLAG column is Y, you can see this transaction in your agings.
The TYPE column contains:
INV – Invoice
CM – Credit Memo
DM – Debit Memo
DEP – Deposit
GUAR – Guarantee
If AutoAccounting is based on transaction type, the GL_ID_REV, GL_ID_FREIGHT, and GL_ID_REC columns store the default revenue, freight, and receivables accounts. The STATUS and CREDIT_MEMO_TYPE_ID columns are required even though they are null allowed.
The primary key for this table is CUST_TRX_TYPE_ID.

/by
, , , ,

Important Account Payables Tables -Part 2

AP_INTERFACE_REJECTIONS
Table Or View:  Table
Important columns: 

PARENT_TABLE : Reference to table in which the rejection occurred (AP_INVOICES_INTERFACE or AP_INVOICE_LINES_INTERFACE)
PARENT_ID NUMBER : Reference to invoice or invoice line identifier which was rejected (INVOICE_ID or INVOICE_LINE_ID)
REJECT_LOOKUP_CODE : Invoice rejection reason

Description: 

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.
If you use Oracle e-Commerce Gateway, you can pass information from this table to your suppliers by submitting the Payables Open Interface Outbound Advice for rejected data.
You can purge data in this table by using the Payables Open Interface Purge.

AP_CHECKS_ALL

Table Or View: Table
Important columns: 

AMOUNT : Payment amount
BANK_ACCOUNT_ID : longer used
BANK_ACCOUNT_NAME : Bank account name
CHECK_ID NUMBER : Payment identifier
CHECK_NUMBER : Payment number
VENDOR_NAME :  Supplier name
VENDOR_SITE_CODE :  Supplier site code
PAYMENT_TYPE_FLAG : Type of payment
A – Payment Process Request
M – Manual
Q – Quick payment
R – Reunfund

Description: 

AP_CHECKS_ALL stores information about payments issued to suppliers or refunds received from suppliers.  You need one row for each payment you issue to a supplier or refund received from a supplier. Your Oracle Payables application uses this information to record payments you make to suppliers or refunds you receive from suppliers. Your Oracle Payables application stores the supplier name and bank account name for auditing purposes, in case either one is changed after you create the payment.
Your Oracle Payables application stores address information for all payments. If you allow changes to the supplier payment address on manual payments or Quick payments, your Oracle Payables application maintains the new address information in this table. Your Oracle Payables application uses BANK_ACCOUNT_NUM, BANK_NUM, and BANK_ACCOUNT_TYPE for the supplier’s bank information when you use the Electronic payment method. Your Oracle Payables application stores a dummy value for CHECK_STOCK_ID for refunds, thus, CHECK_STOCK_ID should not be treated as a foreign key to AP_CHECK_STOCKS_ALL in the case of refunds.
Payments are linked with invoices through AP_INVOICE_PAYMENTS_ALL

AP_SUPPLIERS

Table Or View: Table
Important columns: 

VENDOR_ID :  Yes Supplier unique identifier
SEGMENT1 : Supplier number

Description: 

AP_SUPPLIERS stores information about your supplier level attributes. Each row includes the purchasing, receiving, invoice, tax, classification, and general information. Oracle Purchasing uses this information to determine active suppliers.
This table replaces the old PO_VENDORS table. The supplier name, legal identifiers of the supplier will be stored in TCA and a reference to the party created in TCA will be stored in AP_SUPPLIERS.PARTY_ID, to link the party record in TCA.

 
PO_VENDORS
Table Or View: View
Important columns: 

VENDOR_ID
SEGMENT1

Description: 

PO_VENDORS is a view which contains selected columns of AP_SUPPLIERS

 

/by
, , ,

Important Account Payables Tables -Part 1

AP_INVOICES_ALL
Table Or View:  Table 
 
Important columns: 

INVOICE_ID – NUMBER : Unique internal Identifier for this record. Generated using a database sequence.
VENDOR_ID – NUMBER  : The unique internal identifier of the supplier to which the invoice belongs. Foreign key to the AP_SUPPLIERS table.
INVOICE_NUM : Invoice number for an invoice. This number entered will be unique per supplier and operating unit.
SET_OF_BOOKS_ID : Ledger unique identifier
INVOICE_CURRENCY_CODE : Currency code of the invoice. Foreign key to the FND_CURRENCIES table.
PAYMENT_CURRENCY_CODE : Currency code of payment. If the Invoice Currency is not a fixed-rate currency, then the Payment Currency will be the same as the INVOICE_CURRENCY_CODE. For fixed-rate currency such as euro or another EMU currency it can be different to  INVOICE_CURRENCY_CODE
PAYMENT_CROSS_RATE NUMBER : Yes Exchange rate between invoice and payment. The value is always 1 unless they are associated fixed-rate currencies such as euro or another EMU currency.
DOC_SEQUENCE_ID NUMBER :  Unique Sequence identifier assigned to an invoice, based on the document category of the invoice. Foreign key to the DOC_SEQUENCE_ID of the AP_DOC_SEQUENCE_AUDIT table.
DOC_SEQUENCE_VALUE NUMBER : Sequence name assigned to an invoice, based on the document category of the invoice. Foreign key to the DOC_SEQUENCE_VALUE of the AP_DOC_SEQUENCE_AUDIT table

Description: 

This table corresponds to the Invoices header block of Invoice workbench. AP_INVOICES_ALL holds information of all AP invoices whether it is a manually entered, imported, created from other products like Oracle Loans, Oracle Projects, iSupplier Portal, Refunds from Oracle Receivables etc. This table holds all type of invoices, which includes Standard, Prepayments, Credit Memo, Debit Memo, Mixed invoice, Withholding invoice, Interest Invoice, Retainage invoices, Payment Requests etc., An invoice can also have one or more scheduled payments. There will be one row for each invoice you enter.
An invoice can have one or more invoice lines and each line can have one or more invoice distributions.
An invoice of type EXPENSE REPORT must relate to a row in AP_EXPENSE_REPORT_HEADERS_ALL unless the record has been purged from AP_EXPENSE_REPORT_HEADERS_ALL.
Oracle Payables application uses the INTEREST type invoice for interest that it calculates on invoices that are overdue. Oracle Payables application links the interest invoice to the original invoice by inserting the INVOICE_ID in the AP_INVOICE_RELATIONSHIPS table.
The accounting code combinations stored in this table is meta data for creating accounting entries in Subledger Accounting. Payables provides the code combinations stored in this table to Subledger Accounting as a source for accounting attributes for deriving the accounting code combinations. Subledger Accounting generates the final accounting entries that are posted to General Ledger. The accounting code combination used in the final accounting entries can be same as that stored in AP_INVOICES_ALL table or can be different depending on the rules setup in Subledger Accounting. The XLA_AE_HEADERS and XLA_AE_LINES tables in Subledger Accounting stores the accounting entries posted to General Ledger.

AP_HOLDS_ALL 

Table Or View: Table
Important columns: 
  1. INVOICE_ID : Invoice identifier
  2. LINE_LOCATION_ID :  Purchase order line location identifier
  3. HOLD_LOOKUP_CODE : Name of hold code
  4. HELD_BY : User that placed hold on invoice
  5. HOLD_DATE : Date user placed hold on invoice
  6. HOLD_REASON : Reason for hold being placed on invoice
  7. RELEASE_LOOKUP_CODE : Name of release code
  8. RELEASE_REASON : Reason for release being placed on invoice
  9. LINE_NUMBER : Invoice line number of the applied hold
  10. HOLD_ID : Hold identifier

WF_STATUS :  Holds resolution workflow status

Description: 

AP_HOLDS_ALL contains information about holds that you or your Oracle Payables application place on an invoice. For non-matching holds, there is one row for each hold placed on an invoice. For matching holds, there is one row for each hold placed on an invoice-shipment match. An invoice may have one or more corresponding rows in this table. Your Oracle Payables application does not pay invoices that have one or more unreleased holds recorded in this table.
This table holds information referenced by the Invoice Holds window.
In the strictest sense, AP_HOLDS_ALL has no primary key. It is possible for your Oracle Payables application to place a certain type of hold on an invoice, then release it, then place another hold of the same type (if data changes before each submission of Payables Invoice Validation), which would result in a duplicate primary key. But for practical purposes, the primary key is a concatenation of INVOICE_ID, LINE_LOCATION_ID, and HOLD_LOOKUP_CODE.

AP_INVOICES_INTERFACE

Table Or View: Table
Important columns: 

INVOICE_ID : Unique identifier for this invoice within this batch. Same value should be populated in invoice’s lines in the AP_INVOICE_LINES_INTERFACE table to identify the data as belonging to the same invoice.
INVOICE_NUM :  Enter the invoice number that needs to be assigned to the invoice created in Payables from this record. The number must be unique for the supplier. If no value is populated in this column, then during import, Payables uses the system date at the time of import as a default.
PO_NUMBER : Purchase order number from PO_HEADERS.SEGMENT1. This column needs to be populated if invoice to be matched with an purchase order.
VENDOR_ID NUMBER : Unique identifier for a supplier. Validated against (PO_VENDORS.VENDOR_ID). Supplier of the invoice to be derived by value in one of the following columns in this table: VENDOR_ID, VENDOR_NUM, VENDOR_NAME, VENDOR_SITE_ID or PO_NUMBER.

Description: 

This is the open interface table for importing AP Invoices from external sources and stores header information about invoices. Invoice data comes from sources including: EDI invoices from suppliers that are loaded through Oracle e-Commerce Gateway, supplier invoices that are transferred through the Oracle XML Gateway, invoices that are loaded using Oracle SQL*Loader, lease invoices from Oracle Property Manager, Disbursements from Oracle loans, lease payments from Oracle Assets, credit card transaction data that are loaded using the Credit Card Invoice Interface Summary, Expense Report invoices from Oracle Internet Expenses, Payment Requests from Receivables, and invoices that are entered through the Invoice Gateway. There is one row for each invoice you import. Oracle Payables application uses this information to create invoice header information when Payables Open Interface program is submitted.
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

Table Or View: Table
Important columns: 

INVOICE_ID : Invoice identifier.Validated against AP_INVOICES_INTERFACE.INVOICE_ID
INVOICE_LINE_ID :  Invoice line identifier
LINE_NUMBER : Invoice line number
LINE_TYPE_LOOKUP_CODE : Type of invoice line (Item, Freight, Tax, Miscellaneous)
LINE_GROUP_NUMBER NUMBER : Value to identify each item line to which you want to prorate

Description: 

This is the lines interface table for the AP Invoice Open  Interface. Use it in conjunction with AP_INVOICE_INTERFACE table.
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. For example, if you enter a Tax line in this table and prorate it across multiple Item lines, during Open Interface Import the system may create multiple Tax invoice distributions based on the single Tax line in this table.

/by
, , , , ,

Shared Entities in Oracle EBS

Shared entities can be referenced by multiple products. These entities allow you to define broad-level structures that help you to include members when implementing the E-business suite. Which business unit will own specific data in a shared entity is at the discretion of the compnay. Employee data is always owned by human resources, provided the E-business suite is installed.

/by