eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
MATERIALIZED VIEW: APPS.ASO_BI_QLIN_PC_MV

Source


SELECT /* 12.0: bug#4526784 */ 'GC' Umarker,
        Cat.Top_node_flag Top_node_flag,
        'Y' Category_flag,
        RSG.Parent_group_id Resource_grp_id,
        NULL Resource_id,
        'Y' Resource_grp_flag,
        CAT.Parent_id Category_id,
        NULL Master_id,
        SUM(FACT.newqot_amnt) Newqot_amnt,
        SUM(FACT.sec_newqot_amnt) sec_Newqot_amnt,
        SUM(FACT.newqot_number) Newqot_number,
        SUM(FACT.openqot_amnt) Openqot_amnt,
        SUM(FACT.sec_openqot_amnt) sec_Openqot_amnt,
        SUM(FACT.openqot_number * DECODE(Row_Type, 'OPNNGN', -1, 1)) Openqot_number,
        SUM(FACT.convqot_amnt) Convqot_amnt,
        SUM(FACT.sec_convqot_amnt) sec_Convqot_amnt,
        SUM(FACT.convqot_number) Convqot_number,
        COUNT(FACT.newqot_amnt) Newqot_amnt_cnt,
        COUNT(FACT.sec_newqot_amnt) sec_Newqot_amnt_cnt,
        COUNT(FACT.newqot_number) Newqot_number_cnt,
        COUNT(FACT.openqot_amnt) Openqot_amnt_cnt,
        COUNT(FACT.sec_openqot_amnt) sec_Openqot_amnt_cnt,
        COUNT(FACT.openqot_number * DECODE(Row_type, 'OPNNGN', -1, 1)) Openqot_number_cnt,
        COUNT(FACT.convqot_amnt) Convqot_amnt_cnt,
        COUNT(FACT.sec_convqot_amnt) sec_Convqot_amnt_cnt,
        COUNT(FACT.convqot_number) Convqot_number_cnt,
        COUNT(*) All_cnt,
        FACT.Ent_Year_id,
        FACT.Ent_qtr_id,
        FACT.Ent_period_id,
        FACT.Week_id,
        FACT.Day_id,
        DECODE(GROUPING_ID(
                  FACT.Ent_Year_id, FACT.Ent_qtr_id,
                  FACT.Ent_period_id, FACT.Week_id,
                  FACT.Day_id),
                15, 128,
                23, 64,
                27, 32,
                29, 16,
                30, 1) Period_type_id,
        DECODE(GROUPING_ID(
                  FACT.Ent_Year_id, FACT.Ent_qtr_id,
                  FACT.Ent_period_id, FACT.Week_id,
                  FACT.Day_id),
                15, FACT.Ent_year_id,
                23, FACT.Ent_qtr_id,
                27, FACT.Ent_period_id,
                29, FACT.Week_id,
                30, FACT.Day_id) TIME_id,
        GROUPING_ID(RSG.Parent_group_id,
                    CAT.Parent_id,Cat.Top_node_flag,
                    FACT.Ent_Year_id, FACT.Ent_qtr_id,
                    FACT.Ent_period_id, FACT.Week_id,
                    FACT.Day_id) Grp_id
FROM    ASO_BI_QLIN_L2_MV FACT,
        JTF.JTF_RS_GROUPS_DENORM RSG,
        JTF.JTF_RS_GROUP_USAGES USG,
        ENI.ENI_DENORM_HIERARCHIES CAT,
        INV.MTL_DEFAULT_CATEGORY_SETS MDFT
WHERE   FACT.Resource_grp_id = RSG.Group_id
AND     RSG.Latest_Relationship_Flag = 'Y'
AND     RSG.Parent_group_id = USG.Group_id
AND     USG.Usage = 'SALES'
AND     FACT.Category_id = CAT.Child_id
AND     MDFT.Functional_area_id = 11
AND     MDFT.Category_set_id = CAT.Object_id
AND     CAT.Object_type = 'CATEGORY_SET'
AND     CAT.Dbi_flag = 'Y'
GROUP BY  RSG.Parent_group_id,
          CAT.Parent_id,Cat.Top_node_flag,
          GROUPING SETS(FACT.Ent_Year_id, FACT.Ent_qtr_id,
                        FACT.Ent_period_id, FACT.Week_id, FACT.Day_id)
UNION ALL
SELECT  'GP' Umarker,
         NULL,
        'N' Category_flag,
        RSG.Parent_group_id Resource_grp_id,
        NULL Resource_id,
        'Y' Resource_grp_flag,
        FACT.Category_id,
        FACT.Master_id,
        SUM(FACT.newqot_amnt) Newqot_amnt,
        SUM(FACT.sec_newqot_amnt) sec_Newqot_amnt,
        SUM(FACT.newqot_number) Newqot_number,
        SUM(FACT.openqot_amnt) Openqot_amnt,
        SUM(FACT.sec_openqot_amnt) sec_Openqot_amnt,
        SUM(FACT.openqot_number * DECODE(Row_type, 'OPNNGN', -1, 1)) Openqot_number,
        SUM(FACT.convqot_amnt) Convqot_amnt,
        SUM(FACT.sec_convqot_amnt) sec_Convqot_amnt,
        SUM(FACT.convqot_number) Convqot_number,
        COUNT(FACT.newqot_amnt) Newqot_amnt_cnt,
        COUNT(FACT.sec_newqot_amnt) sec_Newqot_amnt_cnt,
        COUNT(FACT.newqot_number) Newqot_number_cnt,
        COUNT(FACT.openqot_amnt) Openqot_amnt_cnt,
        COUNT(FACT.sec_openqot_amnt) sec_Openqot_amnt_cnt,
        COUNT(FACT.openqot_number * DECODE(Row_type, 'OPNNGN', -1, 1)) Openqot_number_cnt,
        COUNT(FACT.convqot_amnt) Convqot_amnt_cnt,
        COUNT(FACT.sec_convqot_amnt) sec_Convqot_amnt_cnt,
        COUNT(FACT.convqot_number) Convqot_number_cnt,
        COUNT(*) All_cnt1,
        FACT.Ent_Year_id,
        FACT.Ent_qtr_id,
        FACT.Ent_period_id,
        FACT.Week_id,
        FACT.Day_id,
        DECODE(GROUPING_ID(
                  FACT.Ent_Year_id, FACT.Ent_qtr_id,
                  FACT.Ent_period_id, FACT.Week_id,
                  FACT.Day_id),
                15, 128,
                23, 64,
                27, 32,
                29, 16,
                30, 1) Period_type_id,
        DECODE(GROUPING_ID(
                  FACT.Ent_Year_id, FACT.Ent_qtr_id,
                  FACT.Ent_period_id, FACT.Week_id,
                  FACT.Day_id),
                15, FACT.Ent_year_id,
                23, FACT.Ent_qtr_id,
                27, FACT.Ent_period_id,
                29, FACT.Week_id,
                30, FACT.Day_id) TIME_id,
        GROUPING_ID(RSG.Parent_group_id,
                    FACT.Category_id,
                    FACT.Master_id,
                    FACT.Ent_Year_id, FACT.Ent_qtr_id,
                    FACT.Ent_period_id, FACT.Week_id,
                    FACT.Day_id) Grp_id
FROM    ASO_BI_QLIN_L2_MV FACT,
        JTF.JTF_RS_GROUPS_DENORM RSG,
        JTF.JTF_RS_GROUP_USAGES USG
WHERE   FACT.Resource_grp_id = RSG.Group_id
AND     RSG.Latest_Relationship_Flag = 'Y'
AND     RSG.Parent_group_id = USG.Group_id
AND     USG.Usage = 'SALES'
GROUP BY  RSG.Parent_group_id,
          FACT.Category_id,
          FACT.Master_id,
GROUPING SETS(FACT.Ent_Year_id, FACT.Ent_qtr_id,
              FACT.Ent_period_id, FACT.Week_id, FACT.Day_id)
UNION ALL
SELECT  'RC' Umarker,
        Cat.Top_node_flag top_node_flag,
        'Y' Category_flag,
        FACT.Resource_grp_id Resource_grp_id,
        FACT.Resource_id,
        'N' Resource_grp_flag,
        CAT.Parent_id Category_id,
        NULL Master_id,
        SUM(FACT.newqot_amnt) Newqot_amnt,
        SUM(FACT.sec_newqot_amnt) sec_Newqot_amnt,
        SUM(FACT.newqot_number) Newqot_number,
        SUM(FACT.openqot_amnt) Openqot_amnt,
        SUM(FACT.sec_openqot_amnt) sec_Openqot_amnt,
        SUM(FACT.openqot_number * DECODE(Row_type, 'OPNNGN', -1, 1)) Openqot_number,
        SUM(FACT.convqot_amnt) Convqot_amnt,
        SUM(FACT.sec_convqot_amnt) sec_Convqot_amnt,
        SUM(FACT.convqot_number) Convqot_number,
        COUNT(FACT.newqot_amnt) Newqot_amnt_cnt,
        COUNT(FACT.sec_newqot_amnt) sec_Newqot_amnt_cnt,
        COUNT(FACT.newqot_number) Newqot_number_cnt,
        COUNT(FACT.openqot_amnt) Openqot_amnt_cnt,
        COUNT(FACT.sec_openqot_amnt) sec_Openqot_amnt_cnt,
        COUNT(FACT.openqot_number * DECODE(Row_Type, 'OPNNGN', -1, 1)) Openqot_number_cnt,
        COUNT(FACT.convqot_amnt) Convqot_amnt_cnt,
        COUNT(FACT.sec_convqot_amnt) sec_Convqot_amnt_cnt,
        COUNT(FACT.convqot_number) Convqot_number_cnt,
        COUNT(*) All_cnt,
        FACT.Ent_Year_id,
        FACT.Ent_qtr_id,
        FACT.Ent_period_id,
        FACT.Week_id,
        FACT.Day_id,
        DECODE(GROUPING_ID(
                  FACT.Ent_Year_id, FACT.Ent_qtr_id,
                  FACT.Ent_period_id, FACT.Week_id,
                  FACT.Day_id),
                15, 128,
                23, 64,
                27, 32,
                29, 16,
                30, 1) Period_type_id,
        DECODE(GROUPING_ID(
                  FACT.Ent_Year_id, FACT.Ent_qtr_id,
                  FACT.Ent_period_id, FACT.Week_id,
                  FACT.Day_id),
                15, FACT.Ent_year_id,
                23, FACT.Ent_qtr_id,
                27, FACT.Ent_period_id,
                29, FACT.Week_id,
                30, FACT.Day_id) TIME_id,
        GROUPING_ID(FACT.Resource_grp_id, FACT.Resource_id,
                    CAT.Parent_id,Cat.Top_node_flag,
                    FACT.Ent_Year_id, FACT.Ent_qtr_id,
                    FACT.Ent_period_id, FACT.Week_id,
                    FACT.Day_id) Grp_id
FROM    ASO_BI_QLIN_L2_MV FACT,
        ENI.ENI_DENORM_HIERARCHIES CAT,
        INV.MTL_DEFAULT_CATEGORY_SETS MDFT
WHERE   FACT.Category_id = CAT.Child_id
AND     MDFT.Functional_area_id = 11
AND     MDFT.Category_set_id = CAT.Object_id
AND     CAT.Object_type = 'CATEGORY_SET'
AND     CAT.Dbi_flag = 'Y'
GROUP BY  FACT.Resource_grp_id, FACT.Resource_id,
          CAT.Parent_id,Cat.Top_node_flag,
          GROUPING SETS(FACT.Ent_Year_id, FACT.Ent_qtr_id,
                        FACT.Ent_period_id, FACT.Week_id, FACT.Day_id)
UNION ALL
SELECT  'RP' Umarker,
        NULL,
        'N' Category_flag,
        FACT.Resource_grp_id Resource_grp_id,
        FACT.Resource_id,
        DECODE(GROUPING_ID(FACT.Resource_id),
                1, 'Y',
                0, 'N') Resource_grp_flag,
        FACT.Category_id,
        FACT.Master_id,
        SUM(FACT.newqot_amnt) Newqot_amnt,
        SUM(FACT.sec_newqot_amnt) sec_Newqot_amnt,
        SUM(FACT.newqot_number) Newqot_number,
        SUM(FACT.openqot_amnt) Openqot_amnt,
        SUM(FACT.sec_openqot_amnt) sec_Openqot_amnt,
        SUM(FACT.openqot_number * DECODE(Row_type, 'OPNNGN', -1, 1)) Openqot_number,
        SUM(FACT.convqot_amnt) Convqot_amnt,
        SUM(FACT.sec_convqot_amnt) sec_Convqot_amnt,
        SUM(FACT.convqot_number) Convqot_number,
        COUNT(FACT.newqot_amnt) Newqot_amnt_cnt,
        COUNT(FACT.sec_newqot_amnt) sec_Newqot_amnt_cnt,
        COUNT(FACT.newqot_number) Newqot_number_cnt,
        COUNT(FACT.openqot_amnt) Openqot_amnt_cnt,
        COUNT(FACT.sec_openqot_amnt) sec_Openqot_amnt_cnt,
        COUNT(FACT.openqot_number * DECODE(Row_type, 'OPNNGN', -1, 1)) Openqot_number_cnt,
        COUNT(FACT.convqot_amnt) Convqot_amnt_cnt,
        COUNT(FACT.sec_convqot_amnt) sec_Convqot_amnt_cnt,
        COUNT(FACT.convqot_number) Convqot_number_cnt,
        COUNT(*) All_cnt,
        FACT.Ent_Year_id,
        FACT.Ent_qtr_id,
        FACT.Ent_period_id,
        FACT.Week_id,
        FACT.Day_id,
        DECODE(GROUPING_ID(
                  FACT.Ent_Year_id, FACT.Ent_qtr_id,
                  FACT.Ent_period_id, FACT.Week_id,
                  FACT.Day_id),
                15, 128,
                23, 64,
                27, 32,
                29, 16,
                30, 1) Period_type_id,
        DECODE(GROUPING_ID(
                  FACT.Ent_Year_id, FACT.Ent_qtr_id,
                  FACT.Ent_period_id, FACT.Week_id,
                  FACT.Day_id),
                15, FACT.Ent_year_id,
                23, FACT.Ent_qtr_id,
                27, FACT.Ent_period_id,
                29, FACT.Week_id,
                30, FACT.Day_id) TIME_id,
        GROUPING_ID(FACT.Resource_grp_id, FACT.Resource_id,
                    FACT.Category_id,
                    FACT.Master_id,
                    FACT.Ent_Year_id, FACT.Ent_qtr_id,
                    FACT.Ent_period_id, FACT.Week_id,
                    FACT.Day_id) Grp_id
FROM    ASO_BI_QLIN_L2_MV FACT
GROUP BY  FACT.Resource_grp_id, FACT.Resource_id,
          FACT.Category_id,FACT.Master_id,
GROUPING SETS(FACT.Ent_Year_id, FACT.Ent_qtr_id,
              FACT.Ent_period_id, FACT.Week_id, FACT.Day_id)