Oracle Account Payables List

Oracle Account Payables List

The ledger that is assigned to the operating unit in which the invoice_id belongs is chosen and information on th

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 Information on both primary and secondary ledgers are presented.

Invoice Data 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

Ledger Information

AP_INVOICES_ALL

The temp_cancelled_amount is set during the cancellation process, and should normally be null if cancellation c

The source column shows how the invoice was entered into the system, e.g Manual Invoice Entry, Oracle Proje

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_F

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 i

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.

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

AP_INVOICE_DISTRIBUTIONS_ALL

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 b

c. Y – “Yes” - Mass Additions Create ran and the item was sent to the fa_mass_additions_gt ta

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.

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 t

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

AP_HOLDS_ALL

AP_PAYMENT_SCHEDULES_ALL

Tax Data

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

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'

ZX_LINES

Self_Assessed_flag - 'N', 'Y'

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

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, cance

trx_line_type - same as line type in invoice - Item, Misc, Freight)

Line_amt - Transaction Line amount

ZX_LINES_DET_FACTORS

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

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_

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

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

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

or changing some tax info on tax lines (from the detail tax window)

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

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

GENERAL

In all the tables columns record_type_code and historical_flag can be verified to identify if its migrated data or

Payments Data

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_pos

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.

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_lo

Different types of lines are

PAY_DIST_LOOKUP_CODE

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

CASH

AP_PAYMENT_HISTORY_ALL

AP_PAYMENT_HIST_DISTS

BANK CHARGE

FINAL PAYMENT ROUNDING

PAYMENT TO CLEARING ROUNDING

DISCOUNT

AWT

EXCHANGE RATE VARIANCE

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

The table is populated during Payment Process Request

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

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)

After Build Payments runs, proposed payments are stored in this table

AP_CHECKS_ALL

AP_INV_SELECTION_CRITERIA_ALL

AP_SELECTED_INVOICES_ALL

IBY_DOCS_PAYABLE_ALL

IBY_PAYMENTS_ALL

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

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.

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_dist

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_s

Different values for event_type_code

Accounting Data

XLA.XLA_TRANSACTION_ENTITIES

XLA_EVENTS

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

In an instance with encumbrance AAD, when invoice is validated, two events are created, an actual INVOICE V

remains unprocessed until accounting runs. The budgetary control event is created and processed (funds reserve

will be a header and lines for this budgetary control event. This event_id exists as bc_event_id on the correspon

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_na

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 inv

Invoice validation. An Invoice Validated actual event in an encumbrance instance will have two headers. One is

accounting (balance_type_code = 'E')

Links using ae_header_id

Has accounting journal lines for the corresponding event. In R12, the accounting is not rolled back in case of er

Sum of accounted_dr should match sum of accounted_cr

The lines should have a valid code_combination_id.

XLA_AE_HEADERS

XLA_AE_LINES

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)

Links using source_entity_id to XLA_TRANSACTION_ENTITIES.entity_id

Stores the liability lines for trial balance reporting.

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)

XLA_DISTRIBUTION_LINKS

XLA_TRIAL_BALANCES

AP_PREPAY_APP_DISTS

Newer Post Home

Subscribe to: Post Comments (Atom)

Best Blogger Gadgets

Posted by Raju Chinthapatla at 7:21:00 PM

Labels: AP

Enter your comment...

Comment as: Mohammed Na

PPuubblliisshh PPrreevviieew

Create a Link

2 comments:

Anonymous said...

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

December 5, 2014 at 12:46 PM

Faizan said...

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