4 Replies Latest reply on May 1, 2013 3:06 PM by Veeresh Rayan

    EBS Tables with relation for OBIA Report columns.

    Raghu Nagadasari
      Hi Friends,

      We are trying to find the required EBS Tables for the below tables of HR module report. Please find the tables in OBI query.


      *************************************

      select sum(case when T91397.GROUP_ACCOUNT_NUM = 'CONT EXP' then case when T91707.OTHER_DOC_AMT is null then 0 else T91707.OTHER_DOC_AMT * T91707.GLOBAL1_EXCHANGE_RATE end else 0 end ) as c1,
      sum(case when T91397.GROUP_ACCOUNT_NUM = 'EMP SUPP' then case when T91707.OTHER_DOC_AMT is null then 0 else T91707.OTHER_DOC_AMT * T91707.GLOBAL1_EXCHANGE_RATE end else 0 end ) as c2,
      sum(case when T91397.GROUP_ACCOUNT_NUM in ('GEN PAYROLL', 'MKTG PAYROLL', 'R&D PAYROLL', 'SLS PAYROLL') then case when T91707.OTHER_DOC_AMT is null then 0 else T91707.OTHER_DOC_AMT * T91707.GLOBAL1_EXCHANGE_RATE end else 0 end ) as c3,
      sum(case when T91397.GROUP_ACCOUNT_NUM = 'EMP BENFT' then case when T91707.OTHER_DOC_AMT is null then 0 else T91707.OTHER_DOC_AMT * T91707.GLOBAL1_EXCHANGE_RATE end else 0 end ) as c4,
      sum(case when T91397.GROUP_ACCOUNT_NUM = 'COGS' then case when T91707.OTHER_DOC_AMT is null then 0 else T91707.OTHER_DOC_AMT * T91707.GLOBAL1_EXCHANGE_RATE end else 0 end ) as c5,
      sum(case when T91397.GROUP_ACCOUNT_NUM = 'REVENUE' then case when T91707.OTHER_DOC_AMT is null then 0 else T91707.OTHER_DOC_AMT * T91707.GLOBAL1_EXCHANGE_RATE end else 0 end ) as c6,
      T66755.PER_NAME_MONTH as c7
      from

      W_DAY_D T66755 /* Dim_W_DAY_D_Common */ ,
      W_GL_ACCOUNT_D T91397 /* Dim_W_GL_ACCOUNT_D */ ,
      W_GL_OTHER_F T91707 /* Fact_W_GL_OTHER_F */ ,
      W_STATUS_D T96094 /* Dim_W_STATUS_D_Generic */ ,
      W_MCAL_DAY_D T300914 /* Dim_W_MCAL_DAY_D_Ent */

      where ( T66755.ROW_WID = T300914.MCAL_DAY_DT_WID and T66755.PER_NAME_YEAR = '2009' and T91397.ROW_WID = T91707.GL_ACCOUNT_WID and T91707.DOC_STATUS_WID = T96094.ROW_WID and T91707.DELETE_FLG = 'N' and T91707.ACCT_PERIOD_END_DT_WID = T300914.ROW_WID and (T96094.W_STATUS_CODE in ('POSTED', 'REVERSED')) )
      group by T66755.PER_NAME_MONTH

      *********************************************

      select sum(case when T299373.W_EMPLOYMENT_STAT_CODE = 'A' then T299423.HEADCOUNT else 0 end ) as c1,
      sum(T299423.HEADCOUNT) as c2,
      T100027.PER_NAME_MONTH as c3
      from

      W_MONTH_D T100027 /* Dim_W_MONTH_D */ ,
      W_EMPLOYMENT_STAT_CAT_D T299373 /* Dim_W_EMPLOYMENT_STAT_CAT_D */ ,
      W_WRKFC_BAL_A T299423 /* Fact_Agg_W_WRKFC_BAL_A */

      where ( T100027.ROW_WID = T299423.SNAPSHOT_MONTH_WID and T100027.PER_NAME_YEAR = '2009' and T299373.ROW_WID = T299423.EMPLOYMENT_STAT_CAT_WID and T299423.CAL_MONTH_START_DT <= TO_DATE('2013-05-01 00:00:00' , 'YYYY-MM-DD HH24:MI:SS') )
      group by T100027.PER_NAME_MONT

      *********************************************

      Of course, if we can go through back track like:

      OBI <-- Infa PLP <-- Infa SILOS <-- Infa SDE <-- SDE Mappletes <-- Source Qualifier <-- Sql Query.

      We are getting no.of tables for single obi table. But, we need only required tables for the above OBI queries for single report.

      Please correct me if wrong and guide me with better approach..

      Thanks in advance.. Appreciate ur inputs here.

      Best Regards,
      Raghu Nagadasari

      Edited by: Raghava Nagadasari on May 1, 2013 5:30 AM

      Edited by: Raghava Nagadasari on May 1, 2013 5:31 AM