0 Replies Latest reply on Apr 14, 2015 5:20 AM by f20d6b2e-624f-4a5c-85e1-f0ac14b611c4

    "AP Transactions" Subject Area hitting incorrect Fact table

    f20d6b2e-624f-4a5c-85e1-f0ac14b611c4

      Hi Gurus,

       

      Requirement/Background:

       

      We have done some customization to "Financials - AP Transactions" subject area i.e. adding 3 new W_DAY_D aliases in physical layer and few custom columns in W_AP_XACT_F.

       

      I did not create any new BMM tables for new W_DAY_D aliases, just added these new tables in LTS (Fact_W_AP_XACT_F) under "Dim - AP Transaction Details", and put all the custom columns under same Logical table i.e."Dim - AP Transaction Details" mapping correctly to W_DAY_D and W_AP_XACT_F.


      Problem Statement :


      Whenever I add "Purchase Invoice Number" to the query, it hits incorrect fact table i.e.W_AP_AGING_INVOICE_A and gives incorrect results.


      Expected Query : (if I select GL Account Number, Purchase Invoice Number & AP Amount)

      select sum(T333486.AP_LOC_AMT * -1) as c1,

                     sum(T333486.AP_DOC_AMT * T333486.GLOBAL1_EXCHANGE_RATE * -1) as c2,

                     T333486.PURCH_INVOICE_NUM as c3,

                     T334337.GL_ACCOUNT_NUM as c4

                from

                     W_AP_XACT_F T333486 /* Fact_W_AP_XACT_F */ ,

                     W_GL_ACCOUNT_D T334337 /* Dim_W_GL_ACCOUNT_D */

                where  ( T333486.GL_ACCOUNT_WID = T334337.ROW_WID and T333486.PURCH_INVOICE_NUM = '1161/13 CH' and T333486.DELETE_FLG = 'N' )

                group by T333486.PURCH_INVOICE_NUM, T334337.GL_ACCOUNT_NUM

       

      Current Query it is firing :

       

      select sum(T333486.AP_LOC_AMT * -1) as c1,

                     sum(T333486.AP_DOC_AMT * T333486.GLOBAL1_EXCHANGE_RATE * -1) as c2,

                     T348360.PURCH_INVOICE_NUM as c3,

                     T334337.GL_ACCOUNT_NUM as c4

                from

                     W_AP_XACT_F T333486 /* Fact_W_AP_XACT_F */ ,

                     W_GL_ACCOUNT_D T334337 /* Dim_W_GL_ACCOUNT_D */ ,

                     W_SUPPLIER_ACCOUNT_D T336997 /* Dim_W_SUPPLIER_ACCOUNT_D */ ,

                     W_STATUS_D T338711 /* Dim_W_STATUS_D_Generic */ ,

                     W_XACT_TYPE_D T338741 /* Dim_W_XACT_TYPE_D_Financials */ ,

                     W_DAY_D T344857 /* Dim_W_DAY_D_Payment_Due_Date */ ,

                     W_DAY_D T348434 /* Dim_W_DAY_D_Purchase Invoice Date */ ,

                     W_DAY_D T351452 /* Dim_W_DAY_D_Purchase Invoice Cleared_Date */ ,

                     W_PAYMENT_METHOD_D T394960 /* Dim_W_PAYMENT_METHOD_D_AP */ ,

                     W_XACT_SOURCE_D T394984 /* Dim_W_XACT_SOURCE_D */ ,

                     W_AP_AGING_INVOICE_A T348360 /* Fact_W_AP_AGING_INVOICE_A */

                where  ( T333486.GL_ACCOUNT_WID = T334337.ROW_WID and T333486.SPLR_ACCT_WID = T336997.ROW_WID and T333486.DOC_TYPE_WID = T338741.ROW_WID and T333486.DOC_STATUS_WID = T338711.ROW_WID and T333486.INVOICED_ON_DT_WID = T348434.ROW_WID and T333486.PAY_METHOD_WID = T394960.ROW_WID and T333486.CLEARED_ON_DT_WID = T351452.ROW_WID and T333486.PAYMENT_DUE_DT_WID = T344857.ROW_WID and T333486.SOURCE_WID = T394984.ROW_WID and T333486.DELETE_FLG = 'N' and T344857.ROW_WID = T348360.PAYMENT_DUE_DT_WID and T336997.ROW_WID = T348360.SPLR_ACCT_WID and T338711.ROW_WID = T348360.DOC_STATUS_WID and T338741.ROW_WID = T348360.DOC_TYPE_WID and T348360.PURCH_INVOICE_NUM = '1161/13 CH' and T348360.PAYMENT_METHOD_WID = T394960.ROW_WID and T348360.INVOICED_ON_DT_WID = T348434.ROW_WID and T348360.CLEARED_ON_DT_WID = T351452.ROW_WID and T348360.SOURCE_WID = T394984.ROW_WID )

                group by T334337.GL_ACCOUNT_NUM, T348360.PURCH_INVOICE_NUM

       


      "Purchase Invoice Number" is out-of-box present under "Dim - AP Transaction Details" and it is mapped to 4 tables including W_AP_XACT_F & W_AP_AGING_INVOICE_A. It is also set at Detail level in AP Transactions dimension hierarchy.


      Additionally, implicit fact column has also been set. So why it is not hitting W_AP_XACT_F ???


      Thanks in advance.


      Regards,

      Ravi Desai