9 Replies Latest reply: Feb 7, 2013 5:07 AM by Veeresh Rayan RSS

    How to derive the fact "Spend"?

    Sudipta Gupta
      Hi Experts,

      I'm in a process to validate obiee report data with EBS R12 as a source. We are working in BI Apps 7.9.6.3.

      Can anyone please point me out from which table source the Spend related facts are coming from in EBS R12?

      I got to know the following RPD Calc for the metrics "Spend"

      case  when
      Dim_W_STATUS_D_AP_Invoice_Approval_Status.W_STATUS_CODE = 'APPROVED' and
      Dim_W_XACT_TYPE_D_AP_Spend_Classification.W_XACT_TYPE_CODE <> 'Unspecified' then
      Fact_W_AP_INV_DIST_F.INV_DIST_AMT Fact_W_AP_INV_DIST_F.GLOBAL1_EXCHANGE_RATE*
      else 0 end

      How can i derive the spend amount in EBS R12 source. Any idea guys?

      Thanks in advance
      Regards
      Sudipta
        • 1. Re: How to derive the fact "Spend"?
          Srini VEERAVALLI
          The lineage doc might help you to know the source table for the same.
          Check this link
          http://www.cool-bi.com/Ref/Ref.php

          If helps pls mark
          • 2. Re: How to derive the fact "Spend"?
            Sudipta Gupta
            Hi Srini,

            Thanks for your quick response. Please note I have the Data Lineage Sheet with me. But here "W_status_code" is coming from FND_LOOKUPs, and I'm struggling all day to create a simple query in EBS to match the Spend value for any specific Product Name.

            Not able to understand how to get W_Status_Code in EBS table? Also I have a confusion exactly where i need to check Spend amount, is it

            PO_LINES_ALL or
            AP_INVOICE_DISTRIBUTIONS_ALL

            Any help is highly appreciated.

            Thanks
            Sudipta
            • 3. Re: How to derive the fact "Spend"?
              Veeresh Rayan
              Hi Sudipta,

              Could you please run the report and paste the query over here.By that I will be able to get what metric you are using and what facts it is hitting.
              I will definitely route you/guide you to match the data.

              Regards,
              Veeresh Rayan
              • 4. Re: How to derive the fact "Spend"?
                Benoît
                Hi Sudipta,

                as Srini says : you can use the Data Lineage and use the Fact Table (Fact_W_AP_INV_DIST_F) to know the source in EBS.
                W_AP_INV_DIST_F is loaded from W_AP_INV_DIST_FS and W_AP_INV_DIST_FS is loaded principaly from AP_INVOICE_DISTRIBUTIONS_ALL and AP_INVOICES_ALL.
                The best way to understand the query which loads W_AP_INV_DIST_FS is to to use Informatica and see the Informatica query in the mapping.

                Regards,

                Benoit
                • 5. Re: How to derive the fact "Spend"?
                  Sudipta Gupta
                  Hi Veeresh,

                  Thanks for your time and help. I am really having tough time and wasted 2 days on this, where i need to provide them something by EOD.

                  Here is the report query as you wanted.

                  select sum(T159098.INV_DIST_QTY * T159098.STANDARD_UOM_CONV_RATE) as c1,
                  min(T159098.GLOBAL1_EXCHANGE_RATE * T159098.INVOICE_UNIT_PRICE) as c2,
                  sum(T159098.INV_DIST_AMT * T159098.GLOBAL1_EXCHANGE_RATE) as c3,
                  sum(case when T159345.W_STATUS_CODE = 'APPROVED' and T159301.W_XACT_TYPE_CODE <> 'Unspecified' then T159098.INV_DIST_AMT * T159098.GLOBAL1_EXCHANGE_RATE else 0 end ) as c4,
                  T67704.PROD_NAME as c5
                  from
                  W_DAY_D T66755 /* Dim_W_DAY_D_Common */ ,
                  W_PRODUCT_D T67704 /* Dim_W_PRODUCT_D */ ,
                  W_AP_INV_DIST_F T159098 /* Fact_W_AP_INV_DIST_F */ ,
                  W_XACT_TYPE_D T159301 /* Dim_W_XACT_TYPE_D_AP_Spend_Classification */ ,
                  W_STATUS_D T159345 /* Dim_W_STATUS_D_AP_Invoice_Approval_Status */
                  where ( T66755.ROW_WID = T159098.INVOICED_ON_DT_WID and T66755.PER_NAME_YEAR = '2012' and T67704.ROW_WID = T159098.PRODUCT_WID and T159098.APPROVAL_STATUS_WID = T159345.ROW_WID and T159098.DELETE_FLG = 'N' and T159098.SPEND_TYPE_WID = T159301.ROW_WID )
                  group by T67704.PROD_NAME


                  There are couple of metrics in this query and I think they are coming from similar tables. At least please help me recreate the EBS R12 SQL for one measure/fact. I will just take that fact and Product Name and try to match.

                  Please reply with your finding. I will wait for that.

                  Thanks in advance.
                  • 6. Re: How to derive the fact "Spend"?
                    Benoît
                    Hi Sudipta,

                    all measures provide from the same table (W_AP_INV_DIST_F) and as I say before, this table is loaded principaly with EBS table AP_INVOICE_DISTRIBUTIONS_ALL and AP_INVOICES_ALL.
                    You will find below the query which load the data from EBS R12 to OBIA (Staging table).

                    Regards,

                    Benoit


                    SELECT AP_INVOICE_DISTRIBUTIONS_ALL.DISTRIBUTION_LINE_NUMBER, AP_INVOICE_DISTRIBUTIONS_ALL.LAST_UPDATED_BY, AP_INVOICE_DISTRIBUTIONS_ALL.LAST_UPDATE_DATE, AP_INVOICE_DISTRIBUTIONS_ALL.DIST_CODE_COMBINATION_ID,
                    AP_INVOICE_DISTRIBUTIONS_ALL.CREATED_BY, AP_INVOICE_DISTRIBUTIONS_ALL.CREATION_DATE,
                    AP_INVOICES_ALL.INVOICE_ID, AP_INVOICES_ALL.INVOICE_NUM, AP_INVOICES_ALL.INVOICE_CURRENCY_CODE,
                    AP_INVOICES_ALL.VENDOR_ID,
                    AP_INVOICES_ALL.VENDOR_SITE_ID, AP_INVOICES_ALL.INVOICE_DATE, AP_INVOICE_DISTRIBUTIONS_ALL.LINE_TYPE_LOOKUP_CODE, AP_INVOICES_ALL.INVOICE_RECEIVED_DATE, AP_INVOICES_ALL.CREATION_DATE, AP_INVOICE_DISTRIBUTIONS_ALL.ORG_ID, PO_HEADERS_ALL.SEGMENT1, PO_LINES_ALL.LINE_NUM, PO_HEADERS_ALL.CREATION_DATE, AP_INVOICES_ALL.LAST_UPDATE_DATE, PO_HEADERS_ALL.LAST_UPDATE_DATE,
                    PO_LINES_ALL.LAST_UPDATE_DATE,
                    PO_LINES_ALL.ITEM_DESCRIPTION,
                    AP_INVOICE_DISTRIBUTIONS_ALL.DESCRIPTION, AP_INVOICES_ALL.INVOICE_TYPE_LOOKUP_CODE, AP_INVOICES_ALL.WFAPPROVAL_STATUS, AP_INVOICES_ALL.PAYMENT_STATUS_FLAG, AP_INVOICES_ALL.CANCELLED_DATE, AP_INVOICE_DISTRIBUTIONS_ALL.ACCOUNTING_DATE, PO_DISTRIBUTIONS_ALL.PO_DISTRIBUTION_ID, AP_INVOICE_DISTRIBUTIONS_ALL.RCV_TRANSACTION_ID, AP_INVOICE_DISTRIBUTIONS_ALL.MATCH_STATUS_FLAG,AP_INVOICE_DISTRIBUTIONS_ALL.INVOICE_DISTRIBUTION_ID,
                    AP_INVOICES_ALL.TERMS_ID, AP_INVOICE_DISTRIBUTIONS_ALL.EXCHANGE_DATE, AP_INVOICES_ALL.EXCHANGE_DATE, AP_INVOICE_DISTRIBUTIONS_ALL.EXCHANGE_RATE_TYPE, AP_INVOICES_ALL.EXCHANGE_RATE_TYPE, AP_INVOICE_DISTRIBUTIONS_ALL.EXCHANGE_RATE, AP_INVOICES_ALL.EXCHANGE_RATE, AP_INVOICE_DISTRIBUTIONS_ALL.ACCTS_PAY_CODE_COMBINATION_ID, AP_INVOICES_ALL.ACCTS_PAY_CODE_COMBINATION_ID, AP_INVOICE_DISTRIBUTIONS_ALL.SET_OF_BOOKS_ID, AP_INVOICES_ALL.SET_OF_BOOKS_ID, AP_INVOICE_DISTRIBUTIONS_ALL.QUANTITY_INVOICED, AP_INVOICE_DISTRIBUTIONS_ALL.AMOUNT,
                    AP_INVOICE_DISTRIBUTIONS_ALL.BASE_AMOUNT,
                    CASE WHEN AP_INVOICE_LINES_ALL.UNIT_MEAS_LOOKUP_CODE IS NULL THEN PO_LINES_ALL.UNIT_MEAS_LOOKUP_CODE ELSE AP_INVOICE_LINES_ALL.UNIT_MEAS_LOOKUP_CODE END, AP_INVOICE_DISTRIBUTIONS_ALL.INVOICE_PRICE_VARIANCE, AP_INVOICE_DISTRIBUTIONS_ALL.EXCHANGE_RATE_VARIANCE, PO_VENDORS.RECEIPT_REQUIRED_FLAG,
                    PO_VENDORS.INSPECTION_REQUIRED_FLAG, PO_VENDOR_SITES_ALL.PURCHASING_SITE_FLAG, PO_VENDORS.LAST_UPDATE_DATE,
                    AP_INVOICES_ALL.SOURCE,
                    AP_INVOICES_ALL.PROJECT_ID, AP_INVOICES_ALL.TASK_ID, AP_INVOICES_ALL.EXPENDITURE_TYPE,
                    AP_INVOICES_ALL.EXPENDITURE_ORGANIZATION_ID,
                    AP_INVOICES_ALL.PAYMENT_CURRENCY_CODE,
                    AP_INVOICES_ALL.PAYMENT_CROSS_RATE,
                    AP_INVOICES_ALL.REQUESTER_ID,
                    AP_INVOICE_LINES_ALL.LINE_NUMBER,
                    AP_INVOICE_LINES_ALL.LINE_TYPE_LOOKUP_CODE,
                    AP_INVOICE_LINES_ALL.REQUESTER_ID,
                    AP_INVOICE_LINES_ALL.DESCRIPTION,
                    AP_INVOICES_ALL.LEGAL_ENTITY_ID,
                    '0' as X_CUSTOM
                    FROM
                    AP_INVOICE_DISTRIBUTIONS_ALL, AP_INVOICE_LINES_ALL,
                    AP_INVOICES_ALL, PO_LINES_ALL, PO_HEADERS_ALL,
                    PO_VENDORS, PO_VENDOR_SITES_ALL,
                    PO_DISTRIBUTIONS_ALL,
                    (SELECT AP_INVOICES_ALL.INVOICE_ID
                    FROM AP_INVOICES_ALL
                    WHERE AP_INVOICES_ALL.LAST_UPDATE_DATE > TO_DATE('$$LAST_EXTRACT_DATE','MM/DD/YYYY HH24:MI:SS')
                    UNION
                    SELECT AP_INVOICE_LINES_ALL.INVOICE_ID
                    FROM AP_INVOICE_LINES_ALL
                    WHERE AP_INVOICE_LINES_ALL.LAST_UPDATE_DATE > TO_DATE('$$LAST_EXTRACT_DATE','MM/DD/YYYY HH24:MI:SS')
                    UNION
                    SELECT DISTINCT AP_HOLDS_ALL.INVOICE_ID
                    FROM AP_HOLDS_ALL
                    WHERE
                    AP_HOLDS_ALL.RELEASE_LOOKUP_CODE IS NULL AND AP_HOLDS_ALL.LAST_UPDATE_DATE > TO_DATE('$$LAST_EXTRACT_DATE','MM/DD/YYYY HH24:MI:SS')
                    UNION
                    SELECT DISTINCT AP_INVOICE_DISTRIBUTIONS_ALL.INVOICE_ID
                    FROM PO_DISTRIBUTIONS_ALL, AP_INVOICE_DISTRIBUTIONS_ALL
                    WHERE AP_INVOICE_DISTRIBUTIONS_ALL.PO_DISTRIBUTION_ID = PO_DISTRIBUTIONS_ALL.PO_DISTRIBUTION_ID
                    AND PO_DISTRIBUTIONS_ALL.LAST_UPDATE_DATE > TO_DATE ('$$LAST_EXTRACT_DATE','MM/DD/YYYY HH24:MI:SS')
                    UNION
                    SELECT DISTINCT AP_INVOICE_DISTRIBUTIONS_ALL.INVOICE_ID
                    FROM PO_DISTRIBUTIONS_ALL, AP_INVOICE_DISTRIBUTIONS_ALL,
                    PO_HEADERS_ALL
                    WHERE
                    AP_INVOICE_DISTRIBUTIONS_ALL.PO_DISTRIBUTION_ID = PO_DISTRIBUTIONS_ALL.PO_DISTRIBUTION_ID
                    AND PO_DISTRIBUTIONS_ALL.PO_HEADER_ID = PO_HEADERS_ALL.PO_HEADER_ID
                    AND PO_HEADERS_ALL.LAST_UPDATE_DATE > TO_DATE ('$$LAST_EXTRACT_DATE','MM/DD/YYYY HH24:MI:SS')
                    UNION
                    SELECT DISTINCT AP_INVOICE_DISTRIBUTIONS_ALL.INVOICE_ID
                    FROM PO_DISTRIBUTIONS_ALL, AP_INVOICE_DISTRIBUTIONS_ALL,
                    PO_LINES_ALL
                    WHERE
                    AP_INVOICE_DISTRIBUTIONS_ALL.PO_DISTRIBUTION_ID = PO_DISTRIBUTIONS_ALL.PO_DISTRIBUTION_ID
                    AND PO_DISTRIBUTIONS_ALL.PO_LINE_ID = PO_LINES_ALL.PO_LINE_ID
                    AND PO_LINES_ALL.LAST_UPDATE_DATE > TO_DATE ('$$LAST_EXTRACT_DATE','MM/DD/YYYY HH24:MI:SS')
                    ) TEMP
                    WHERE
                    AP_INVOICE_DISTRIBUTIONS_ALL.INVOICE_ID = AP_INVOICES_ALL.INVOICE_ID
                    AND AP_INVOICES_ALL.VENDOR_ID = PO_VENDORS.VENDOR_ID(+)
                    AND AP_INVOICES_ALL.VENDOR_SITE_ID = PO_VENDOR_SITES_ALL.VENDOR_SITE_ID(+)
                    AND AP_INVOICE_DISTRIBUTIONS_ALL.PO_DISTRIBUTION_ID = PO_DISTRIBUTIONS_ALL.PO_DISTRIBUTION_ID(+)
                    AND PO_DISTRIBUTIONS_ALL.PO_HEADER_ID = PO_HEADERS_ALL.PO_HEADER_ID(+)
                    AND PO_DISTRIBUTIONS_ALL.PO_HEADER_ID = PO_LINES_ALL.PO_HEADER_ID(+)
                    AND PO_DISTRIBUTIONS_ALL.PO_LINE_ID = PO_LINES_ALL.PO_LINE_ID(+)
                    AND AP_INVOICE_DISTRIBUTIONS_ALL.INVOICE_ID = TEMP.INVOICE_ID
                    AND AP_INVOICE_DISTRIBUTIONS_ALL.INVOICE_ID = AP_INVOICE_LINES_ALL.INVOICE_ID
                    AND AP_INVOICE_DISTRIBUTIONS_ALL.INVOICE_LINE_NUMBER = AP_INVOICE_LINES_ALL.LINE_NUMBER
                    • 7. Re: How to derive the fact "Spend"?
                      Veeresh Rayan
                      Hi Sudipta,

                      Please follow these steps

                      1)Take out only metric,create one report and restrict it to only one product/one invoice to narrow down where is the data problem resides.
                      2)Check table W_AP_INV_DIST_FS and W_AP_INV_DIST_F table and also W_PRODUCT_D and W_PRODUCT_DS tables to locate your product and try to restrict the data.
                      3)Checkout the SQL provided by Benoit(basically this SQL gives you staging table data load i,e W_AP_INV_DIST_FS).
                      4)Query the above sql by putting your product restriction and also W_AP_INV_DIST_FS by putting the same product.
                      5)W_STATUS_D table as you rightly said loads the FND_LOOKUPS.You can query this table by putting restriction like WFAPPROVAL_STATUS as the lookup code or lookup type(Try to match since I dont have access to APPS rt now not able to give you proper sql).
                      6)Check also AP_INVOICES_ALL table specially by putting restrictions on the product(you may need to join AP_INVOICE_DESTRIBUTIONS_ALL and LINES_ALL table as well).
                      7)Look for the column called WFAPPROVAL_STATUS in the AP_INVOICES_ALL table and check what is the status and what is its meaning in FND_LOOKPUS.

                      Hope you find this information as usefull.Right now I dont have instance access(neither OLAP not OLTP).

                      Regards,
                      Veeresh Rayan
                      • 8. Re: How to derive the fact "Spend"?
                        Sudipta Gupta
                        Thanks for your reply. But there is no WFAPPROVAL_STATUS in the table AP_INVOICE_DISTRIBUTIONS_ALL.

                        Can you tell me in which table i must look for W_AP_INV_DIST_F.*GLOBAL1_EXCHANGE_RATE*. I'm getting this in most of the tables as EXCHANGE_RATE.

                        Is it the same?
                        • 9. Re: How to derive the fact "Spend"?
                          Veeresh Rayan
                          It considers the status from AP_INVOICES_ALL     WFAPPROVAL_STATUS column.
                          Global exchange rate are nothing but currency conversion.

                          Let me explain here.

                          INVOICE_CURRENCY_CODE column of the AP_INVOICES_ALL table used in calculating the global_exchange_rate.Menaing all document currency code are gonna get converted to global_currency_code(which you would have defined in the DAC parameters).

                          Please mark if it helps..

                          Regards,
                          Veeresh Rayan