8 Replies Latest reply: Apr 16, 2013 1:26 AM by 1002757 RSS

    Wid's are not populated

    1002757
      Hi,

      The WID's are not populated for the tables mentioned below for the initial full load.

      1. EXPENSE_STATUS_WID is 0 in the W_EXPENSE_F
      2. PROJECT_WID is 0 in W_AP_INV_DIST_F

      Please advice.

      Thanks,
      Babu.

      Edited by: 999754 on Apr 12, 2013 7:16 AM
        • 1. Re: Wid's are not populated
          Veeresh Rayan
          Do these things and let me know the results.

          1)Check SDE mapping for W_AP_INV_DIST_FS(SDE_ORA_AP_InvoiceDistribution_Fact) and see project_ID's are getting generated.

          2)Check SIL mapping for W_AP_INV_DIST_F (SIL_ORA_AP_InvoiceDistribution_Fact) and see why project_id's are not getting resolved to WID's.

          There wont be any WID's if the project_id's are null in stage table.

          Another thing is its not necessary that all the rows in the stage table have entries for project_id..Some rows can not have since AP_INVOICE_DISTRIBUTION_ALL table might not have entries for some of the rows.

          Please mark correct or help full in case if it helps.

          Regards,
          Veeresh Rayan
          • 2. Re: Wid's are not populated
            1002757
            Hi,

            Thanks for the update, as you mentioned i have observed that there is some issue with the mapping SIL_ORA_AP_InvoiceDistribution_Fact and i have corrected it ,now the values are populated to the PROJECT_WID for table W_AP_INV_DIST_F.

            But for the case of EXPENSE_STATUS_WID is 0 for the table W_EXPENSE_F

            the mapping is using the condition at query level

            LEFT OUTER JOIN W_STATUS_D ON
            W_EXPENSE_FS.EXPENSE_STATUS_ID = W_STATUS_D.INTEGRATION_ID AND
            W_EXPENSE_FS.DATASOURCE_NUM_ID = W_STATUS_D.DATASOURCE_NUM_ID

            but when i see the data at individual levels i have the entries

            select distinct EXPENSE_STATUS_ID from W_EXPENSE_FS;

            qry o/p:

            EXPENSE_REPORT~WITHDRAWN
            EXPENSE_REPORT~PAID
            EXPENSE_REPORT~NA
            EXPENSE_REPORT~MGRAPPR
            EXPENSE_REPORT~INVOICED
            EXPENSE_REPORT~PENDMGR

            select distinct INTEGRATION_ID from W_STATUS_D;

            Sample qry o/p:
            PURCH_APPROVE~RETURNED
            PURCH_CYCLE~APPROVED~APPROVED
            PURCH_CYCLE~AWAITING PRINTING~AWAITING PRINTING
            PURCH_CYCLE~CANCELED~CANCELED

            But the following qry is not returning any values because of which does not have any values like " EXPENSE_REPORT"

            select distinct EXPENSE_STATUS_ID from W_EXPENSE_FS
            where EXPENSE_STATUS_ID in (select distinct INTEGRATION_ID from W_STATUS_D);

            please advice.

            Thanks,
            Babu.
            • 3. Re: Wid's are not populated
              Veeresh Rayan
              Can you please post these results.

              select distinct EXPENSE_STATUS_ID from W_EXPENSE_FS;
              select distinct INTEGRATION_ID from W_STATUS_D;

              I see correspoding lookups have not been loaded properly into W_STATUS_D.

              Regards,
              Veeresh Rayan
              • 4. Re: Wid's are not populated
                1002757
                Hi,
                Thanks for the update, Please see the query results below :


                select distinct EXPENSE_STATUS_ID from W_EXPENSE_FS;

                Qry Output:
                --------------

                EXPENSE_REPORT~WITHDRAWN
                EXPENSE_REPORT~PAID
                EXPENSE_REPORT~NA
                EXPENSE_REPORT~MGRAPPR
                EXPENSE_REPORT~INVOICED
                EXPENSE_REPORT~PENDMGR

                select distinct INTEGRATION_ID from W_STATUS_D;

                Qry Output:
                --------------

                0
                ACCT_DOC_STATUS~CLEARED~POSTED
                ACCT_DOC_STATUS~CLEARED~UNPOSTED
                ACCT_DOC_STATUS~OPEN~POSTED
                ACCT_DOC_STATUS~OPEN~UNPOSTED
                ACCT_DOC_STATUS~~POSTED
                ACCT_DOC_STATUS~~UNPOSTED
                AP_INVOICE_DISTRIBUTION_APPROVAL~APPROVED
                AP_INVOICE_DISTRIBUTION_APPROVAL~CANCELLED
                AP_INVOICE_DISTRIBUTION_APPROVAL~ON HOLD
                AP_INVOICE_DISTRIBUTION_APPROVAL~REQUIRES APPROVAL
                AP_INVOICE_PAYMENT~N
                AP_INVOICE_PAYMENT~P
                AP_INVOICE_PAYMENT~Y
                PROJ_STATUS~1003
                PROJ_STATUS~1105
                PROJ_STATUS~ACTIVE
                PROJ_STATUS~APPROVED
                PROJ_STATUS~CLOSED
                PROJ_STATUS~PARTIALLY_PURGED
                PROJ_STATUS~PENDING_CLOSE
                PROJ_STATUS~PENDING_PURGE
                PROJ_STATUS~PURGED
                PROJ_STATUS~REJECTED
                PROJ_STATUS~SUBMITTED
                PROJ_STATUS~UNAPPROVED
                PURCH_APPROVE~ALL
                PURCH_APPROVE~APPROVED
                PURCH_APPROVE~CANCELLED
                PURCH_APPROVE~IN PROCESS
                PURCH_APPROVE~INCOMPLETE
                PURCH_APPROVE~PRE-APPROVED
                PURCH_APPROVE~REJECTED
                PURCH_APPROVE~REQUIRES REAPPROVAL
                PURCH_APPROVE~RETURNED
                PURCH_CYCLE~APPROVED~APPROVED
                PURCH_CYCLE~AWAITING PRINTING~AWAITING PRINTING
                PURCH_CYCLE~CANCELED~CANCELED
                PURCH_CYCLE~CANCELLED~CANCELLED
                PURCH_CYCLE~CLOSED FOR INVOICE~CANCELLED
                PURCH_CYCLE~CLOSED FOR INVOICE~CLOSED FOR INVOICE
                PURCH_CYCLE~CLOSED FOR RECEIVING~CANCELLED
                PURCH_CYCLE~CLOSED FOR RECEIVING~CLOSED FOR RECEIVING
                PURCH_CYCLE~CLOSED~CANCELLED
                PURCH_CYCLE~CLOSED~CLOSED
                PURCH_CYCLE~FINALLY CLOSED~CANCELLED
                PURCH_CYCLE~FINALLY CLOSED~FINALLY CLOSED
                PURCH_CYCLE~FROZEN~FROZEN
                PURCH_CYCLE~IN PROCESS~IN PROCESS
                PURCH_CYCLE~INCOMPLETE~INCOMPLETE
                PURCH_CYCLE~NOT ON HOLD~NOT ON HOLD
                PURCH_CYCLE~NOT RESERVED~NOT RESERVED
                PURCH_CYCLE~ON HOLD~CANCELLED
                PURCH_CYCLE~ON HOLD~ON HOLD
                PURCH_CYCLE~OPEN~CANCELLED
                PURCH_CYCLE~OPEN~OPEN
                PURCH_CYCLE~PRE-APPROVED~PRE-APPROVED
                PURCH_CYCLE~PRINTED~PRINTED
                PURCH_CYCLE~REJECTED~REJECTED
                PURCH_CYCLE~REQUIRES REAPPROVAL~REQUIRES REAPPROVAL
                PURCH_CYCLE~RESERVED~RESERVED
                PURCH_CYCLE~RETURNED~RETURNED
                PURCH_CYCLE~UNFROZEN~UNFROZEN
                PURCH_RQLNS~FULFILLMENT~FULFILLED~NOT_APPLICABLE
                PURCH_RQLNS~FULFILLMENT~NOT_APPLICABLE
                PURCH_RQLNS~FULFILLMENT~UNAPPROVED~NOT_APPLICABLE
                PURCH_RQLNS~FULFILLMENT~UNFULFILLED~FULFILLMENT_NOT_TRACKED
                PURCH_RQLNS~FULFILLMENT~UNFULFILLED~FULFILLMENT_TRACKED
                PURCH_RQLNS~FULFILLMENT~UNPROCESSED~PENDING_BUYER_SUBMIT
                PURCH_RQLNS~FULFILLMENT~UNPROCESSED~PENDING_BUYER_WORKBENCH
                PURCH_RQLNS~FULFILLMENT~UNPROCESSED~PENDING_PO_APPROVAL
                PURCH_RQLNS~FULFILLMENT~UNPROCESSED~PENDING_SOURCING
                Process~-1
                Process~1
                Process~2
                Process~3
                Process~4

                Thanks,
                Babu
                • 5. Re: Wid's are not populated
                  Veeresh Rayan
                  It clearly lists none of the expense related lookup values have been pulled onto W_STATUS_D.

                  Check the SDE_ORA_EmployeeExpenseStatusDimension output and execute the SQL used in this mapping and see why no values are getting loaded.

                  Also you can look

                  SELECT
                  LOOKUP_CODE,
                  MEANING,
                  DESCRIPTION,
                  CREATED_BY,
                  CREATION_DATE,
                  LAST_UPDATED_BY,
                  LAST_UPDATE_DATE,
                  '0' AS X_CUSTOM
                  FROM FND_LOOKUP_VALUES
                  WHERE FND_LOOKUP_VALUES.LOOKUP_TYPE= 'EXPENSE REPORT STATUS'
                  AND FND_LOOKUP_VALUES.LANGUAGE = USERENV('LANG')
                  --AND FND_LOOKUP_VALUES.LAST_UPDATE_DATE > TO_DATE('$$LAST_EXTRACT_DATE','MM/DD/YYYY HH24:MI:SS')

                  and see what are you getting..These should be entries in FND_LOOKUP_VALUES

                  Let me know the results.

                  Regards,
                  Veeresh Rayan
                  • 6. Re: Wid's are not populated
                    1002757
                    Hi,

                    Thanks for the update, the mapping SDE_ORA_EmployeeExpenseStatusDimension is not able to fetch the data due to the reason which is not meeting the condition

                    LEFT OUTER JOIN W_STATUS_D ON
                    W_EXPENSE_FS.EXPENSE_STATUS_ID = W_STATUS_D.INTEGRATION_ID AND
                    W_EXPENSE_FS.DATASOURCE_NUM_ID = W_STATUS_D.DATASOURCE_NUM_ID

                    The Query which you have mentioned i am able to see the data.

                    Thanks,
                    Babu.
                    • 7. Re: Wid's are not populated
                      Veeresh Rayan
                      Babu, I know the that condition making it to not to review data.
                      That is why I started looking into W_STATUS_D and then W_STATUS_DS.
                      Query which I have stated is used to load W_STATUS_DS dimension.If that query is returning data then stage table is loading correctly.

                      Send the SIL_StatusDimension and SDE_ORA_EmployeeExpenseStatusDimension SQL and also the output of W_STATUS_D and W_STATUS_DS to my email id

                      veereshrayan@gmail.com

                      Lets see what is making it not to work.

                      Regards,
                      Veeresh Rayan
                      • 8. Re: Wid's are not populated
                        1002757
                        Hi,
                        Thanks for the update, i have shared the details to your mail id.

                        Please advice.

                        Thanks,
                        Babu.