OBIEE Step by Step Guide

January 21, 2010

BI Apps Financials — Accounts Payable Facts and Aggregates

These are OOB (out of box) OBAW facts and aggregates related to Oracle BI Apps AP (Accounts Payable) Facts and Aggregates. This is just to help you understand which fact and aggregate tables goes where in the BI Apps ocean.

Sourced from Oracle BI Apps Data Model Reference.

DIMENSION/FACT BUSINESS NAME DESCRIPTION
W_AP_AGING_INVOICE_A 

Fact Table

AP Aging Aggregate Table by Invoice  This table stores the accounts payable aging information at the invoice and association dimension levels. Each record in this table indicates the aging information at a particular date in time. The SNAPSHOT_DT_WID column indicates the date when the aging information is recorded 
W_AP_AGING_SUPPLIER_A 

Fact Table

AP Aging Aggregate Table by Supplier This table stores the accounts payable aging information at the supplier andassociation dimension levels. Each record in this table indicates the aging information at a particular date in time. The SNAPSHOT_DT_WID column indicates the date when the aging information is recorded 
W_AP_BALANCE_F 

Fact Table

Accounts payable balance  This table stores account payable balance information for supplier accounts. Only Posted records are considered for calculating the balances and Balance amts can beuse for Financial reporting 
W_AP_INV_DIST_F 

Fact Table

AP Invoice Distributions Fact table stores the AP invoice data at invoice distribution level
W_AP_XACT_F 

Fact Table

AP Transaction This fact table stores various types of Payables transactions.The types of AP transactions stored in this table are:

  • Payment schedules for various types of invoices
  • Accounting entries for payments
  • Liability accounting entries from various types of invoices

GL Reconciliation records.

The first three types of records are extracted from the source applications (ie. Oracle 11i). The fourth type of record, GL Reconciliation records, are records inserted by the source adapters. These records are the results of the “GL Reconciliation” process.

The DOC_TYPE_WID field in this table is the foreign key to W_XACT_TYPE_D table.

It distinguishes the transaction type of the record.

The DOC_STATUS_WID field is the foreign key to W_STATUS_D table. It helps identify the status of the record, whether it is “Open” or “Cleared”, “Posted” or “Unposted”.

W_AP_XACT_GRPACCT_DAY_A 

Fact Table Aggregate

AP Transaction by Day Aggregate This table is an aggregate table of W_AP_XACT_F at day level and various other dimensions. These dimensions include Company, Supplier, Supplier account, Transaction type, etc.Records from W_AP_XACT_F are aggregated to day level and various other dimensions. For instance, all invoices for supplier A posted on December 23, 2006 are aggregated into one record. The aggregation is based on POSTED_ON_DT_WID field, which is the document’s posting date.

Fields such as XACT_COUNT indicates the transaction count of this particular aggregation record. For instance, for the aggregation record of supplier “A”, for the date December 23, 2006, for the transaction type of “Invoice”, the XACT_COUNT indicates how many invoices from supplier “A”, is posted on December 23, 2006.

W_AP_XACT_GRPACCT_FSCLPRD_A 

Fact Table Aggregate

AP Transaction by Fiscal Period i.e. Month Aggregate(Would it be nice if Oracle names this fact table as W_AP_XACT_GRPACCT_FSCLMONTH_A because this truly represents fiscal month. Well, Oracle might have a better reason why they named it like this)  This table is an aggregate table of W_AP_XACT_F at fiscal month level and various other dimensions. These dimensions include Company, Supplier, Supplier account, Transaction type, etc.Records from W_AP_XACT_F are aggregated at fiscal month level and various other dimensions. For instance, all invoices for supplier “A” posted in 3rd fiscal month of fiscal year 2006 are aggregated into one record. The aggregation is based on

POSTED_ON_DT_WID field of W_AP_XACT_F, which is the document’s posting date. The fields POSTED_FROM_DT_WID and POSTED_TO_DT_WID indicates the fiscal month start date and fiscal month end date of this aggregation record.

Fields such as XACT_COUNT indicates the transaction count of this particular aggregation record. For instance, for the aggregation record of supplier “A”, for the 3rd fiscal month of fiscal year 2006, for the transaction type of ?Invoice?, theXACT_COUNT indicates how many invoices from supplier ?A?, is posted in the 3rd fiscal month of fiscal year 2006.

W_AP_XACT_GRPACCT_FSCLQTR_A 

Fact Table Aggregate

AP Transaction by Fiscal Quarter Aggregate This table is an aggregate table of W_AP_XACT_F at fiscal quarter level and various other dimensions. These dimensions include Company, Supplier, Supplier account,Transaction type, etc. Records from W_AP_XACT_F are aggregated at fiscal quarter level and various other dimensions. For instance, all invoices for supplier “A” posted in 3rd fiscal quarter of fiscal year 2006 are aggregated into one record. The aggregation is based on

POSTED_ON_DT_WID field of W_AP_XACT_F, which is the document’s posting date. The fields POSTED_FROM_DT_WID and POSTED_TO_DT_WID indicate the fiscal quarter start date and fiscal quarter end date of this aggregation record.

Fields such as XACT_COUNT indicates the transaction count of this particular aggregation record. For instance, for the aggregation record of supplier “A”, for the 3rd fiscal quarter of fiscal year 2006, for the transaction type of “Invoice”, the XACT_COUNT indicates how many invoices from supplier “A”, is posted in the 3rd fiscal quarter of fiscal year 2006.

W_AP_XACT_GRPACCT_FSCLYR_A AP Transaction by Fiscal Year Aggregate  This table is an aggregate table of W_AP_XACT_F at fiscal year level and various other dimensions. These dimensions include Company, Supplier, Supplier account, Transaction type, etc. Records from W_AP_XACT_F are aggregated at fiscal year level and various other dimensions. For instance, all invoices for supplier “A” posted in fiscal year 2006 areaggregated into one record. The aggregation is based on POSTED_ON_DT_WID field of W_AP_XACT_F, which is the document’s posting date. The fields POSTED_FROM_DT_WID and POSTED_TO_DT_WID indicate the fiscal year start date and fiscal year end date of this aggregation record.

Fields such as XACT_COUNT indicates the transaction count of this particular aggregation record. For instance, for the aggregation record of supplier “A”, for the fiscal year 2006, for the transaction type of “Invoice”, the XACT_COUNT indicates how many invoices from supplier “A”, is posted in fiscal year 2006.

 

Advertisements

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: