The AP,AR prebuilt modules utilize some of the most complex mappings.It take a week or two just to understand the flow of data into the W_AP_XACT_FS table and then finally into W_AP_XACT_F table.
Once you take efforts and understand that logic ,modify ETL & ask for a full load of the target, you assume that your job is done.But chances are that you will be told that the data has not been loaded properly and that there is a bug in the ETL customizations.
Problem with the AP,AR maps are that there are many restricting conditions and even the most experienced ETL resources in your team cannot say for sure whether a particular record in the EBS table should actually make its way to W_AP_XACT_F .
So even we are faced with one such issue and are currently in a deadlock with the BI and ETL developers blaming each other.
we have to show the invoices which are paid but couldn't able to show few of the invoices from w_ap_xact_f
Pre built Mappings were modified in custom to get project ids from distribution table .Though Business was expecting multiple projects there are only single projects connected to an invoice as Lookup transformations were used(Informatica 8.6.1) to populate the project Ids.
we have modified the existing lookup conditions to get the multiple project numbers and multiple accounting units but still we couldn't able to show few of the invoices in the report.
the problem here is payment flow is capturing invoices which are accounted and paid (from ap_invoice_hist_dist) but we have to show the invoices at payments level
i.e (we need to show the invoice which are paid at the ap_invoice_payments_level.).
could any one suggest an alternative to achieve desired results.
Please let me know if any one requires extra information.
your help will be highly appreciated.
Thanks and Regards,
You raise some good points about the design of some of the Analytics applications. Some of the mappings are not for the faint hearted, and ideally you need developers who have an understanding/appreciation of the data source (EBS) functionalty and schema. I would say however that I am surprised that it takes weeks to understand SDE and SIL mappings for a single fact table.
As with any customisations to seeded mappings, it is important that results are fully tested and reconciled in dev before being promoted to prod. I assume the issue you are having is still in a development environment.
Quick question; which version of the BI Applications are you using? Since Project Analytics was introduced (7.9.6) there are already project and task wids on the fact table w_ap_xact_f; were these insufficient?
Another thing to bear in mind is the relational data model in EBS. Project information for invoices is stored in ap_invoice_distributions_all, however, payments relate to invoice headers only (ap_invoices_all), not invoice distributions. It is therefore impossible to say how much of an invoice distribution has been paid, as the relational model in AP cannot answer this. In the past I have used analytical DB functions to pro-rate payment amounts across invoice distributions, but this is not an exact science.
Please mark if helpful / correct,
Andy, Thanks for your valuable efforts.
The actual requirement is we need to show the invoices which are paid regardless weather it is accounted or not.
The current fact w_ap_xact_f is not capturing the invoices which are not accounted.
if we see the payments flow of w_ap_xact_f the invoices are coming from ap_payments_hist_dist.
EBS experts asked us to use ap_invoice_payments_all to get the invoices in to WH instead of ap_payments_hist_dist.
AP_INVOICE_PAYMENTS_ALL contains records of invoice payments that you made to suppliers. There is one row for each payment you make for each invoice. There is one payment and one invoice for each payment in this table.
we thought of changing the source qualifer querry to get the unaccounted records into WH but the design got rejected from client.
Andy, is there any other fact in the ap module where these kind of transactions are captured?
if not any other alternative available to fix this issue.
your help is highly appreciated.
Thanks in advance for any information you may provide.
Edited by: 959345 on Oct 13, 2012 11:03 PM
Few more points which i feel use full to resolve the issue.
problem: We were going through some financial reports prepared using W_AP_XACT_F and second guessing on why a few invoices were not being displayed in the OBIEE reports.
Problem with W_AP_XACT_F is that it gets loaded by different arms like Payments,Schedule,Invoice Distribution etc.Each flow having a different level of granularity.
For example the Payments flow just records invoices for which payments are done.So if a particular invoice has not been paid it does not show up in the payment flow.Logically correct
Now consider the new calendar dimension W_MCAL_DAY_D. It has records corresponding to 3 different types of calendars with MCAL_CAL_WID taking values 1000,1001,1002 .
But every dimension table has a default value(zero wid row) which is assigned to those records in the fact for which no matching dimension is present .In case of dates it ideally refers to a scenario where no date could be found.
In W_MCAL_DAY_D though this record has a MCAL_CAL_WID=0 stating that it doesn't belong to any calendar type.
Now, W_AP_XACT_F has records from INV,DIST flow which do not have Payment due dates...For these records the fact has PAYMENT_DUE_DT_WID=0,MCAL_CAL_WID=1000.1000 being calendar type from Ledgers.
So the prebuilt payment due date dimension uses following joins
W_AP_XACT_F.PAYMENT_DUE_DT_WID = W_MCAL_DAY_D.ROW_WID
In the current scenario whenever an OBIEE report is developed using this dimension,records from INVDIST,DIST flows are automatically filtered out.
Andy,I am new to bi apps and i work as an ETL consultant desperately looking for help to finish of this issue.
i would like to request you to give a suggestion on handling prebuilt payment due date dimension to get the required invoices.
Thanks and Regards,
Edited by: 959345 on Oct 14, 2012 12:01 PM
Lots of updates there... Where shall I start... :)
Firstly, I must point out that one of the assumptions you make about AP_INVOICE_PAYMENTS_ALL is not quite correct. This table can hold multiple payments for a single invoice (one to many relationship). In Payables it is possible to partially pay an invoice. This goes back to a previous point I made about the BI Applications, it really helps to understand EBS schema and functionality.
With regards to your latest update, this is an interesting issue and one could argue that this is a design flaw. As you rightly indicate, the unspecified record in the dimension should join to all FKs in the fact where there is no surrogate key derived by the ETL. Having records filtered out during the query due to join issues is not good design.
You will notice that the row_wid in the mcal tables actually contains the calendar wid as the first four characters, so the join on calendar wid is actually superfluous. I would try removing the join on calendar wid from the RPD in a test environment and retest the issue.
Please mark all comments that were helpful / correct,