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.
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
