In Subject area: Financials - Asset Transactions: when we are pulling Asset Detail: Asset ID, Asset Number and Asset Name with Project ID, Project Number, Project Name, all relevant Project details are appearing as null. By investigating the SQL beneath it: c6 and c8 related to Project detail are null

Please note: if we remove JOIN condition between: Ledger Id from DW_FA_MC_SLA_TRX_DEPRN_CF and Ledger Id from DW_FA_MC_ASSET_SOURCE_LINES_D, Data is visible.

Reason:

Ledger Id for Assets are not matching between tables DW_FA_MC_SLA_TRX_DEPRN_CF and DW_FA_MC_ASSET_SOURCE_LINES_D

Please let me know any workaround for this.

In the Subject Area PPM - Assets: Relationships between Assets are project are working fine.

The tables used in SQL are: DW_PROJECT_ASSET_D, DW_PROJECT_D, DW_PROJECT_D_TL

Alterative workaround can be: if we know the PVO for: DW_PROJECT_ASSET_D and DW_PROJECT_D table from Fusion SaaS - we can join these tables to DW_FA_ASSET_D in following manner and get the data:

DW_PROJECT_ASSET_D.Project_Id = DW_PROJECT_D.Project_id and DW_PROJECT_ASSET_D.Asset_Id = DW_FA_ASSET_D.Asset_Id

Please let me know the PVO names for extracting data for following Warehouse tables: DW_PROJECT_ASSET_D and DW_PROJECT_D

==========================================================================================================

SELECT

  SUM(

    CASE

      WHEN NOT t187425.transaction_type_code IS NULL THEN

        t187425.xla_ledger_amount

      ELSE

        0

    END

  )           AS c1,

  t196744.asset_number  AS c3,

  t225527.project_number AS c6,

  t196744.description  AS c7,

  t202561.project_id   AS c8

FROM

  (

       oax$oac.dw_fa_asset_d t196744 /* Dim_DW_FA_ASSET_D */

    INNER JOIN oax$oac.dw_fa_mc_sla_trx_deprn_cf   t187425 /* Fact_DW_FA_MC_SLA_TRX_DEPRN_CF */ ON t187425.asset_id = t196744.asset_id

  )

  LEFT OUTER JOIN (

    oax$oac.dw_fa_mc_asset_source_lines_d t202561 /* Dim_DW_FA_MC_ASSET_SOURCE_LINES_D */

    LEFT OUTER JOIN oax$oac.dw_project_d         t225527 /* Dim_DW_PROJECT_D_Asset */ ON t202561.project_id = t225527.project_id

  ) ON t187425.asset_id = t202561.asset_id

     AND t187425.ledger_id = t202561.ledger_id

     AND t202561.date_ineffective = TO_DATE('4712/12/31', 'YYYY/MM/DD')

WHERE

  ( t187425.accounting_class_code <> 'EXCHANGE_GAIN_LOSS' 

  -- and t196744.asset_id = 70004

   )

GROUP BY

  t196744.asset_number,

  t196744.description,

  t202561.project_id,

  t225527.project_number;

========================================================================================