W_MCAL_DAY_D & W_AP_XACT_F

Dt:20 July  2012.

Applicable to OBI Apps 7.9.6.2 and higher

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

W_AP_XACT_F.MCAL_CAL_WID= W_MCAL_DAY_D.MCAL_CAL_WID.

In the current scenario whenever  an OBIEE report is developed using this dimension,records from INVDIST,DIST flows are automatically filtered out.

If anyone out there has faced a similar issue then your comments/suggestions will be welcome.