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.ISC_FS_016_MV

Source


select /* 12.0: bug#4526784 */
 den.record_type
,decode( den.record_type, 'GROUP', den.parent_prg_id, dist.GROUP_ID )  parent_district_id
,decode( den.record_type, 'GROUP', den.prg_id, null )                district_id
,decode( den.record_type, 'RESOURCE', subinv.OWNER_RESOURCE_ID, null ) resource_id
,decode( den.record_type, 'GROUP', to_char(den.prg_id), to_char(subinv.OWNER_RESOURCE_ID) || '.' || dist.GROUP_ID ) district_id_c
,icat.INV_CATEGORY_ID
,fact.INVENTORY_ITEM_ID ||'-'|| fact.ORGANIZATION_ID item_org_id
,grouping_id(icat.INV_CATEGORY_ID, fact.INVENTORY_ITEM_ID ||'-'|| fact.ORGANIZATION_ID) grp_id
,decode(grouping_id(  c.ent_year_id
                    , c.ent_qtr_id
                    , c.ent_period_id
                    , c.week_id
                    , c.report_date_julian ), 0, c.report_date_julian
                                            , 1, c.week_id
                                            , 3, c.ent_period_id
                                            , 7, c.ent_qtr_id
                                            , 15, c.ent_year_id) time_id
,decode(grouping_id( c.ent_year_id
                    , c.ent_qtr_id
                    , c.ent_period_id
                    , c.week_id
                    , c.report_date_julian ), 0, 1
                                            , 1, 16
                                            , 3, 32
                                            , 7, 64
                                            , 15, 128) period_type_id
,sum(case when subinv.CONDITION_TYPE = 'G' then fact.ONHAND_VALUE_B else 0 END)                       uonhand_value_b
,sum(case when subinv.CONDITION_TYPE = 'G' then fact.ONHAND_VALUE_B * fact.conversion_rate else 0 END)            uonhand_value_g
,sum(case when subinv.CONDITION_TYPE = 'G' then fact.ONHAND_VALUE_B * fact.sec_conversion_rate else 0 END)       uonhand_value_sg
,sum(case when subinv.CONDITION_TYPE = 'B' then fact.ONHAND_VALUE_B else 0 END)                      donhand_value_b
,sum(case when subinv.CONDITION_TYPE = 'B' then fact.ONHAND_VALUE_B * fact.conversion_rate else 0 END)            donhand_value_g
,sum(case when subinv.CONDITION_TYPE = 'B' then fact.ONHAND_VALUE_B * fact.sec_conversion_rate else 0 END)       donhand_value_sg
 /* the following columns prefixed with mv_ exist solely to enable MV to be fast refreshable */
,grouping_id( den.record_type
             ,decode( den.record_type, 'GROUP', den.parent_prg_id, dist.GROUP_ID )
             ,decode( den.record_type, 'GROUP', den.prg_id, null )
             ,decode( den.record_type, 'RESOURCE', subinv.OWNER_RESOURCE_ID, null )
         ,decode( den.record_type, 'GROUP', to_char(den.prg_id), to_char(subinv.OWNER_RESOURCE_ID) || '.' || dist.GROUP_ID)
             ,icat.INV_CATEGORY_ID
             ,fact.INVENTORY_ITEM_ID ||'-'|| fact.ORGANIZATION_ID
             ,c.ent_year_id, c.ent_qtr_id, c.ent_period_id, c.week_id, c.report_date_julian) mv_grp_id
,c.ent_year_id                     mv_ent_year_id
,c.ent_qtr_id                      mv_ent_qtr_id
,c.ent_period_id                   mv_period_id
,c.week_id                         mv_week_id
,c.report_date_julian           mv_day_id
,count(case when subinv.CONDITION_TYPE = 'G' then fact.ONHAND_VALUE_B else 0 END)                   mv_uonhand_value_b
,count(case when subinv.CONDITION_TYPE = 'G' then fact.ONHAND_VALUE_B * fact.conversion_rate else 0 END)    mv_uonhand_value_g
,count(case when subinv.CONDITION_TYPE = 'G' then fact.ONHAND_VALUE_B * fact.sec_conversion_rate else 0 END)    mv_uonhand_value_sg
,count(case when subinv.CONDITION_TYPE = 'B' then fact.ONHAND_VALUE_B else 0 END)                      mv_donhand_value_b
,count(case when subinv.CONDITION_TYPE = 'B' then fact.ONHAND_VALUE_B * fact.conversion_rate else 0 END)    mv_donhand_value_g
,count(case when subinv.CONDITION_TYPE = 'B' then fact.ONHAND_VALUE_B * fact.sec_conversion_rate else 0 END)    mv_donhand_value_sg
,count(*)                mv_count
from
 OPI.OPI_DBI_INV_VALUE_F fact
,CSP.CSP_SEC_INVENTORIES    subinv
,JTF.JTF_RS_DEFAULT_GROUPS  dist
,ENI.ENI_OLTP_ITEM_STAR     icat
,FII.FII_TIME_DAY           c
,isc_fs_002_mv     den
where
    fact.ORGANIZATION_ID = subinv.ORGANIZATION_ID
and fact.SUBINVENTORY_CODE = subinv.SECONDARY_INVENTORY_NAME
and subinv.OWNER_RESOURCE_ID = dist.RESOURCE_ID /* We will assume that all subinventories will have owners AND that all owners will be active at the time of collection, so no outer join is needed here. */
and dist.USAGE = 'FLD_SRV_DISTRICT'
and dist.end_date = to_date('31/12/4712','DD/MM/YYYY') /* We will assume the owner is active at the time of collection. So we do not actually need the last valid default because it cannot be inactive now. */
and fact.ORGANIZATION_ID = icat.ORGANIZATION_ID
and fact.INVENTORY_ITEM_ID = icat.INVENTORY_ITEM_ID
and c.report_date = fact.TRANSACTION_DATE
and dist.GROUP_ID = den.rg_id
group by
 den.record_type
,decode( den.record_type, 'GROUP', den.parent_prg_id, dist.GROUP_ID )
,decode( den.record_type, 'GROUP', den.prg_id, null )
,decode( den.record_type, 'RESOURCE', subinv.OWNER_RESOURCE_ID, null )
,decode( den.record_type, 'GROUP', to_char(den.prg_id), to_char(subinv.OWNER_RESOURCE_ID) || '.' || dist.GROUP_ID )
,rollup(icat.INV_CATEGORY_ID, fact.INVENTORY_ITEM_ID ||'-'|| fact.ORGANIZATION_ID)
,c.ent_year_id
,rollup(c.ent_qtr_id, c.ent_period_id, c.week_id, c.report_date_julian)