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
Table 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)