MATERIALIZED VIEW: APPS.FII_FA_EXP_MV

Source

SELECT /* 12.0: bug#4526784 */  FDAY.ENT_YEAR_ID ENT_YEAR_ID, FDAY.ENT_QTR_ID ENT_QTR_ID,
		   FDAY.ENT_PERIOD_ID ENT_PERIOD_ID, FDAY.REPORT_DATE_JULIAN REPORT_DATE_JULIAN,
			DECODE(GROUPING_ID(FDAY.ENT_YEAR_ID, FDAY.ENT_QTR_ID, FDAY.ENT_PERIOD_ID, FDAY.REPORT_DATE_JULIAN),
		        0, FDAY.REPORT_DATE_JULIAN, 
				1, FDAY.ENT_PERIOD_ID, 
				3, FDAY.ENT_QTR_ID, 
				7, FDAY.ENT_YEAR_ID) TIME_ID,
    	    DECODE(GROUPING_ID(FDAY.ENT_YEAR_ID, FDAY.ENT_QTR_ID, FDAY.ENT_PERIOD_ID, FDAY.REPORT_DATE_JULIAN),
        		0, 1, 1, 32, 3, 64, 7, 128) PERIOD_TYPE_ID,
			CURRENCY_CODE,
 			SUM( AMOUNT_T ) TOT_AMOUNT_T,
			COUNT(AMOUNT_T) TOT_AMOUNT_TC,
			SUM( AMOUNT_B ) TOT_AMOUNT_B,
			COUNT(AMOUNT_B) TOT_AMOUNT_BC,
			SUM( PRIM_AMOUNT_G ) TOT_PRIM_AMOUNT_G,
			COUNT(PRIM_AMOUNT_G) TOT_PRIM_AMOUNT_GC,
			SUM( SEC_AMOUNT_G ) TOT_SEC_AMOUNT_G,
			COUNT(SEC_AMOUNT_G) TOT_SEC_AMOUNT_GC,
			COMPANY_ID,
			COST_CENTER_ID,
			NATURAL_ACCOUNT_ID,
			USER_DIM1_ID,
			USER_DIM2_ID,
			ASSET_CAT_ID,
			ASSET_CAT_MAJOR_ID,
			ASSET_CAT_MINOR_ID,
			LEDGER_ID,
			GROUPING_ID(CURRENCY_CODE,
 				COMPANY_ID,
		 		COST_CENTER_ID,
 				NATURAL_ACCOUNT_ID,
				USER_DIM1_ID,
 				USER_DIM2_ID,
		 		ASSET_CAT_ID,
 				ASSET_CAT_MAJOR_ID,
		 		ASSET_CAT_MINOR_ID,
		 		LEDGER_ID,
				FDAY.ENT_YEAR_ID, FDAY.ENT_QTR_ID, FDAY.ENT_PERIOD_ID,  FDAY.REPORT_DATE_JULIAN) GID,
			COUNT(*) CNT
FROM FII.FII_FA_EXP_F FIFA, FII.FII_TIME_DAY FDAY
WHERE FIFA.ACCOUNT_DATE = FDAY.REPORT_DATE
GROUP BY CURRENCY_CODE,
 		COMPANY_ID,
 		COST_CENTER_ID,
 		NATURAL_ACCOUNT_ID,
		USER_DIM1_ID,
 		USER_DIM2_ID,
 		ASSET_CAT_ID,
 		ASSET_CAT_MAJOR_ID,
 		ASSET_CAT_MINOR_ID,
 		LEDGER_ID,
 		ROLLUP (FDAY.ENT_YEAR_ID, FDAY.ENT_QTR_ID, FDAY.ENT_PERIOD_ID, FDAY.REPORT_DATE_JULIAN)