Thursday, January 17, 2013

Oracle Account Payables List



Ledger Information

The ledger that is assigned to the operating unit in which the invoice_id belongs is chosen and information on this ledger is displayed under Ledger Definition.
In R12, a Subledger Accounting Method (SLAM) is assigned to each ledger.
This is displayed in the report under Ledger Definition/Subledger Accounting Options
There are Oracle Seeded SLAMs for Accrual Accounting - Standard Accrual and Encumbrance Accrual. Check if the SLAM associated to the ledger is a seeded one or custom.

Information on both primary and secondary ledgers are presented.
  
Invoice Data

AP_INVOICES_ALL 

Holds invoice header information
Main columns: Invoice_id, invoice_num, invoice_amount,invoice_date,invoice_type_lookup_code, source,
amount_paid, payment_status_flag

If the invoice is cancelled, the cancellation columns that should be populated are:

cancelled_amount
cancelled_by
cancelled_date

The temp_cancelled_amount is set during the cancellation process, and should normally be null if cancellation completed correctly.

The source column shows how the invoice was entered into the system, e.g Manual Invoice Entry, Oracle Project Accounting, Withholding Tax.

If more than one row is returned in the ap_invoices_all table in the aplist, the other
invoices are typically invoices paid by the same check.


AP_INVOICE_LINES_ALL
Link using Invoice_id
Holds invoice line information

1) Line_type_lookup_code -> ITEM, TAX, MISCELLANEOUS,
FREIGHT, AWT,  PREPAY
2) Line_source ->
            MANUAL LINE ENTRY
            HEADER MATCH
            AUTO WITHHOLDING
            PREPAY APPL
            ETAX  -> When Tax calculated by E-Business Tax engine
            IMPORTED
            HEADER FREIGHT
            PO PRICE ADJUSTMENT

3) Discarded_flag - N, Y
When a line is created, the discarded_flag = N.
If the line is discarded, the distributions for the line are reversed, the line_amount is set to 0 and the discarded_Flag is set to 'Y'.

4) Cancelled_flag - 'N', 'Y'
When a line is created, the cancelled_flag = N
If invoice is cancelled, the cancelled_flag on the line is set to Y, distributions are reversed and the line amount is set to 0.


5) Generate_Dists - N, Y, D
N- Do not generate dists
Y - Generate Dists
      If flag is Y, the distrbutions for the line will be generated automatically.
      If the flag is Y, but there is not enough information for the application to generate the     
      invoice distributions, the invoice will go on 'INSUFFICIENT LINE INFO' hold.
D - Done.
      The flag moves to D if the distributions have been generated or if the distribution  
      manually entered.


6) Tax_classification_code
Tax classification code provided for an ITEM line is one way to calculate tax.

7) Accounting_date and Period_name
   Important to note in case of troubleshooting accounting issues.
   Sweep should move the accounting_date and period_name on invoice lines also.

8) Amount
    Verify that the line amount is equal to the sum of its distributions.
  

AP_INVOICE_DISTRIBUTIONS_ALL

Hold Invoice distribution informationLink using Invoice_id.
Each row in invoice distributions table should belong to a corresponding line in ap_invoice_lines_all

1)      Invoice_id
2)      Invoice_line_number -> Determine which line this dist belongs to
3)      Distribution_line_number
4)      Amount
5)      Dist_code_combination_id -> Distribution account
6)      line_type_lookup_code ->
            ITEM
            NONREC_TAX - Nonrecoverable Tax
            REC_TAX - Recoverable Tax
            ERV - Exchange Rate Variance
            TRV - Tax Rate Variance
            TIPV-  Tax IPV
            MISCELLANEOUS
            FREIGHT
            AWT - Withholding Tax
            IPV - Invoice Price Variance
            ACCRUAL - When matched to PO set to Accrue on Receipt
            RETROACCRUAL
            PREPAY - Prepay Application and Unapplication
            RETAINAGE - When invoice matched to PO with retainage
7)      accounting_date
8)      period_name
9)      Accrual_posted_flag - Y for posted, N- Unposted
10)  posted_flag - Y for posted, N- Unposted
11)  match_status_flag - A for Validated, T - Tested, N- Not Validated
12)  reversal_flag - null or Y for reversed.
13)  accounting_event_id - Links to Event_id of XLA_EVENTS table for Actual event
14)  bc_event_id - Links to Event_id of XLA_EVENTS table for the budgetary control event
15)  historical_flag - If the distribution existed prior to R12 upgrade, this flag will be Y
16)  assets_addition_flag
a.       U – “Untested” - Mass Additions Create has not been run with parameters to select this line
b.      N – “No” - Mass Additions Create ran on the invoice line, but it did not meet the criteria to be an asset
c.       Y – “Yes” - Mass Additions Create ran and the item was sent to the fa_mass_additions_gt table to be prepared and posted in FA.

AP_HOLDS_ALL

Stores information on holds on the invoice if any
Link using Invoice_id
Columns: Hold_lookup_code, hold_date, hold_reason, release_lookup_code, release_reason
Unreleased holds have null release_lookup_code and release_reason.


AP_PAYMENT_SCHEDULES_ALL

Holds Payment Schedule information for the invoice
Link using Invoice_id
1) Amount_remaining
2) gross_amount
3) due_date
4)discount_date
5)hold_flag - N or Y(Payment cannot be made)
6)Payment_status_flag - N(Not Paid), Y (Paid), P(Partial)

One row is created in this table by default when invoice is saved.
If an invoice is partially paid, payment_status_flag is set to ‘P’ and amount_remaining is set to the amount left to be paid. If multiple payment schedules are created, there will be more rows.

Tax Data

ZX_LINES_SUMMARY

Holds the summarized information of the taxlines.
This is the information displayed in invoice workbench as line type Tax
All the tax lines from zx_lines that have the same summarization criteria (mainly regime to rate information) will have a corresponding single record in zx_lines_summary

Created during Tax calculation by the Tax engine.
Link to invoice using application_id = 200 and trx_id = invoice_id.
Can also use summary_tax_line_id to link to ap_invoice_lines_all.summary_tax_line_id

Columns:
Summary_tax_line_id
Application_id
Entity_code = ‘AP_INVOICES’
Event_Class_Code
Trx_id
Trx_number
Cancel_flag
Self_assess_flag

Different values for entity_code and event_class_code

ENTITY_CODE                    EVENT_CLASS_CODE
------------------------------ -------------------
AP_INVOICES                    EXPENSE REPORTS
AP_INVOICES                    PREPAYMENT INVOICES
AP_INVOICES                    STANDARD INVOICES

 

ZX_LINES

Holds the Tax Details records for the invoice. Created by the tax engine.
Link to the invoice is using
application_id = 200 and entity_code = 'AP_INVOICES' and trx_id = invoice_id

Columns:
Tax_line_id
Application_id
Entity_code
Event_class_code
Event_type_code
Trx_id -> invoice_id
Trx_line_id
Tax_Regime_code
Tax_status_code
Tax_rate_code
Cancel_flag - 'N' , 'Y'
Self_Assessed_flag - 'N', 'Y'


ZX_LINES_DET_FACTORS

Table stores transaction related attributes for calculating tax /reporting.
Each record in this table represents a transaction line.
Products (Payables) can insert/modify records in this table during calls to tax engine and passes the information to Etax.

Columns:
trx_id - invoice_id
trx_number - invoice_num
line_level_action - action at the transaction line level (create , update, apply_from, unapply_from, delete, cancel etc)
trx_line_type - same as line type in invoice - Item, Misc, Freight)
Line_amt - Transaction Line amount

 

ZX_REC_NREC_DIST
T
able holds detailed Recoverable and Non-Recoverable Tax distributions.

For every line in ZX_LINES there can be one or more lines in ZX_REC_NREC_DIST
that stores the Recoverable and Non-recoverable amounts

Data in zx_rec_nrec_dist are mapped to zx_lines using tax_line_id, tax_line_number, summary_tax_line_id


Rec_nrec_tax_dist_id

Application_id

Entity_code

Event_class_code

Event_type_code

Tax_event_class_code

Tax_event_type_code

Trx_id

Trx_line_id

Trx_line_dist_id

recoverable_flag - Y, N
rec_nrec_rate - % recovery

recoverable_flag is 'N' - no recovery (0%recoverable)
recoverable_flag is 'Y' and rec_nrec_rate tells what % is recoverable


AP_SELF_ASSESSED_TAX_DIST_ALL

Link using invoice_id
If there is a Self assessed tax on the invoice, the distributions for the self assessed taxes are stored in this table.
accounting_event_id,accounting_date,period_name,self_assessed_flag = 'Y',invoice_distribution_id,dist_code_combination_id,amount,reversal_flag

Self assessed tax amounts are not included in the invoice amount on ap_invoices_all.


GENERAL
In all the tables columns record_type_code and historical_flag can be verified to identify if its migrated data or E-Tax created data. For upgraded data,

Record_type_code = ‘MIGRATED’
Historical_flag = ‘Y’

When tax calculation is successful - following tables will be populated
zx_lines_summary, zx_lines and zx_lines_det_factors

When tax calculation is attempted but no taxes applicable only zx_lines_det_factors will have data zx_lines and zx_lines_summary will not be populated

Incase of taxless invoice eg. No tax setup at all then no zx tables will be populated.

zx_rec_nrec_dist is populated during determine_recovery - triggered through validation / tax details - distribution button / all distribution button/
or changing some tax info on tax lines (from the detail tax window)


Payments Data


AP_DOCUMENTS_PAYABLE
View to invoice/payment information
Links to the invoice/check through the following
calling_appd_id = 200 ,calling_app_doc_unique_ref1 = Check_id, calling_app_doc_unique_ref2 = invoice_id
caling_app_doc_unique_ref4 = invoice_payment_id
call_app_pay_service_req_code -> PPR name or the Quick Payment ID


AP_INVOICE_PAYMENTS_ALL

Link using invoice_id
Primary key: Invoice_payment_id
Holds payment information. If payment is voided, a reversal rows is created in this table
Main columns: Invoice_id, invoice_payment_id, check_id, accounting_Date, accounting_event_id, accrual_posted_flag, posted_flag


AP_PAYMENT_HISTORY_ALL

Link using check_id
Primary key: payment_history_id
Holds information related to the payment transactions.
Columns: check_id, payment_history_id, transaction_type, accounting_date, accounting_event_id

The different values for transaction_type are:

TRANSACTION_TYPE
-----------------
PAYMENT CREATED
PAYMENT CLEARING
MANUAL PAYMENT ADJUSTED
REFUND CANCELLED
REFUND RECORDED
PAYMENT ADJUSTED
PAYMENT CLEARING ADJUSTED
PAYMENT CANCELLED
PAYMENT UNCLEARING
PAYMENT MATURITY

For every payment event, there is a record created in this table.


AP_PAYMENT_HIST_DISTS

Link using payment_history_id
Primary Key: PAYMENT_HIST_DIST_ID
Columns: Invoice_payment_id, payment_history_id, pa_dist_lookup_code, invoice_distribution_id, pay_dist_lookup_code, accounting_event_id, bank_curr_amount, inv_dist_amount

Different types of lines are
PAY_DIST_LOOKUP_CODE
-----------------------------
CASH
BANK CHARGE
FINAL PAYMENT ROUNDING
PAYMENT TO CLEARING ROUNDING
DISCOUNT
AWT
EXCHANGE RATE VARIANCE

AP_CHECKS_ALL

Primary key: check_id
Columns: Check_id, check_number, amount, check_date, status_lookup_code, void_date
Different values for the status
STATUS_LOOKUP_CODE
-----------------------
NEGOTIABLE
VOIDED
OVERFLOW
STOP INITIATED
SET UP
CLEARED BUT UNACCOUNTED
CLEARED
RECONCILED UNACCOUNTED
RECONCILED
ISSUED

AP_INV_SELECTION_CRITERIA_ALL

The table is populated during Payment Process Request

AP_SELECTED_INVOICES_ALL

The invoices selected for the payment process request is populated in this table

IBY_DOCS_PAYABLE_ALL

Payments assembled during PPR is stored in this IBY table.
Links to Payment process Request using payment_service_request_id
Links to invoice_id using  calling_app_id = 200(Payables)
and  calling_app_doc_unique_ref2 (invoice_id)


IBY_PAYMENTS_ALL

After Build Payments runs, proposed payments are stored in this table
Links to PPR using payment_service_request_id
Links to invoice_id through iby_docs_payable_all.payment_id

IBY_PAY_INSTRUCTIONS_ALL
Continuing the PPR process after build payments will create the payment instruction
Links to PPR using payment_service_request_id


Accounting Data


XLA.XLA_TRANSACTION_ENTITIES

Link using SOURCE_ID_INT_1 that stores the invoice_id or check_id
Columns: application_id = 200, entity_id, source_id_int_1,legal_entity_id, entity_code,upg_batch_id

The different entity_code for payables are
ENTITY_CODE
------------
AP_PAYMENTS
MANUAL
AP_INVOICES

If entity_code = 'AP_INVOICES', source_id_int_1 = invoice_id
If entity_code = 'AP_PAYMENTS', source_id_int_1 = check_id

 In all XlA tables, Upg_batch_id is null for R12 transactions and not null for pre-upgrade
 transactions.


XLA_EVENTS

Link using entity_id
Stored accounting event information for invoices and payments.
Primary_key : event_id

Event_id in this table should exist in accounting_event_id in one of the base transaction tables (ap_invoice_distributions_all, ap_invoice_payments_all, ap_payment_history_all) or should exist as bc_event_id on the invoice distribution table.

Columns: application_id = 200, event_id, entity_id, event_type_code, event_date, event_status_code, process_status_code, budgetary_control_flag, upg_batch_id

Different values for event_type_code
EVENT_TYPE_CODE
----------------------------
CREDIT MEMO VALIDATED
PREPAYMENT UNAPPLIED
PAYMENT MATURED
INVOICE VALIDATED
PREPAYMENT VALIDATED
DEBIT MEMO VALIDATED
PAYMENT CREATED
PREPAYMENT APPLIED
MANUAL
PAYMENT UNCLEARED
PAYMENT CLEARED
MANUAL PAYMENT ADJUSTED
INVOICE CANCELLED
CREDIT MEMO CANCELLED
INVOICE ADJUSTED
REFUND CANCELLED
REFUND RECORDED
PREPAYMENT CANCELLED
PAYMENT ADJUSTED
PAYMENT CLEARING ADJUSTED
PAYMENT CANCELLED

Different Values for event_status_code
-----------------------
U - Not accounted
P - Accounted
I - Incomplete

Different Values for Process_status_code
-------------------------
U - Not Processed
P - Processed
I - Incomplete
D - Draft

Budgetary_control_flag -> Y for budgetary control events created by Invoice validation and Invoice accounting if budgetary control is enabled and Encumbrance AAD is used.

In an instance with encumbrance AAD, when invoice is validated, two events are created, an actual INVOICE VALIDATED event and an encumbrance INVOICE VALIDATED event. The actual event remains unprocessed until accounting runs. The budgetary control event is created and processed (funds reserved) during invoice validation. At the end of validation, if funds reserve is successful, there will be a header and lines for this budgetary control event. This event_id exists as bc_event_id on the corresponding invoice distribution.

XLA_AE_HEADERS

Links using event_id
Primary_key: ae_header_id
Columns: event_id, ae_header_id, application_id =200, entity_id, event_type_code, accounting_date, period_name, gl_transfer_status_code, je_category_name, accounting_entry_status_code, balance_type_code

gl_transfer_status_code
--------------------
N - Not transferred to GL
Y - Transferred to GL

accounting_entry_status_code
--------------------------
I - In Error
R - Related event in error
D - Draft
F - Final

Balance_type_code
----------------
A - Actual
E - Encumbrance


The accounting header created for a budgetary_control event will have balance_type_code = 'E'. Also, when invoice is accounted, accounting should reverse the invoice encumbrance created during Invoice validation. An Invoice Validated actual event in an encumbrance instance will have two headers. One is a header for actual accounting (balance_type_code='A') and another for encumbrance accounting (balance_type_code = 'E')


XLA_AE_LINES

Links using ae_header_id
Has accounting journal lines for the corresponding event. In R12, the accounting is not rolled back in case of errors. The following can be checked in lines
Sum of accounted_dr should match sum of accounted_cr
The lines should have a valid code_combination_id.


XLA_DISTRIBUTION_LINKS

Link using source_distribution_id_num_1 = invoice_distribution_id
Stores distribution level accounting information that is then summarised in xla_ae_lines
Columns: application_id, event_id, ae_header_id, ae_line_num

EVENT_CLASS_CODE                               EVENT_TYPE_CODE
------------------------------                                   -----------------------------
PAYMENTS                                                 PAYMENTS_ALL
PREPAYMENT APPLICATIONS              PREPAYMENT APPLICATIONS_ALL
PREPAYMENTS                                           PREPAYMENTS_ALL
CREDIT MEMOS                                          CREDIT MEMOS_ALL
DEBIT MEMOS                                                        DEBIT MEMOS_ALL
RECONCILED PAYMENTS                                   RECONCILED PAYMENTS_ALL
MANUAL                                                      MANUAL
FUTURE DATED PAYMENTS                   FUTURE DATED PAYMENTS_ALL
INVOICES                                                     INVOICES_ALL
REFUNDS                                                     REFUNDS_ALL


XLA_ACCOUNTING_ERRORS

Links using entity_id or event_id
Stores the accounting errors from the Create Accounting process if error encountered for the event_id
Message_number stores the error number for the event.(Example:95340, 95353,0)

XLA_TRIAL_BALANCES

Links using source_entity_id to XLA_TRANSACTION_ENTITIES.entity_id
Stores the liability lines for trial balance reporting.

AP_PREPAY_APP_DISTS

Links using the invoice_distribution_id
Information on accounting events of prepayments that were applied to the invoice.
1)      invoice_distribution_id – the invoice_distribution_id to which the prepayment was applied.
2)      prepay_app_distribution_id – the invoice_distribution_id of the prepayment event distribution line
3)      accounting_event_id  - the accounting_event_id on the actual invoice distribution line
4)      prepay_dist_lookup_code: values are
PREPAY APPL
PREPAY APPL REC TAX
PREPAY APPL NONREC TAX
1)             

2 comments:

Anonymous said...

It's such a great effort and the information is very useful.. Hats off

Faizan said...

One word for u : SUPERB .. and thanks a lot for this information

Post a Comment

Best Blogger TipsGet Flower Effect