APPS.FII_EA_CUMUL_EXP_TREND_PKG SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 219

                ' AND  f.company_id IN (SELECT company_id
					  FROM fii_company_grants
					 WHERE user_id		  = fnd_global.user_id
					   AND report_region_code = '''||fii_ea_util_pkg.g_region_code||'''
					) ';

Line: 238

                ' AND f.cost_center_id IN (SELECT cost_center_id
                                             FROM fii_cost_center_grants
                                            WHERE user_id = fnd_global.user_id
                                              AND report_region_code = '''||fii_ea_util_pkg.g_region_code||''' ) ';

Line: 313

		     '( SELECT SUM(f.budget_g)		     FII_EA_BUDGET
			  FROM '||l_budget_table_name||'     g	-- Profile option = N, Period Type = Year
			       ,fii_gl_trend_sum_mv'||fii_ea_util_pkg.g_curr_view||'	f
			 WHERE f.time_id		= '||l_budget_time_id||'
			    '||l_company_security||l_cost_center_security||'
	                   AND top_node_fin_cat_type = ''OE''
			   AND NVL(f.budget_version_date,&BIS_CURRENT_ASOF_DATE)  <= &BIS_CURRENT_ASOF_DATE
			   AND :ASOF_DATE BETWEEN g.start_date AND g.end_date
			   AND NVL(f.budget_version_date,time.end_date) <= time.end_date

-- To choose only those versions having version dates less than or equal to report date
-- Here, time is alias of the timeRelated table used in the actual SQL,
-- wherein, these Budget/Forecast SQL strings are actually concatenated
		      )';

Line: 330

		    '( SELECT SUM( f.forecast_g)		FII_EA_FORECAST
			  FROM '||l_forecast_table_name||'	g  -- Profile option = N, Period Type = Year
			       ,fii_gl_trend_sum_mv'||fii_ea_util_pkg.g_curr_view||'	f
			 WHERE f.time_id	= '||l_forecast_time_id||'
			   '||l_company_security||l_cost_center_security||'
			   AND top_node_fin_cat_type = ''OE''
			   AND NVL(f.budget_version_date,&BIS_CURRENT_ASOF_DATE)  <= &BIS_CURRENT_ASOF_DATE
			   AND :ASOF_DATE BETWEEN g.start_date AND g.end_date
			   AND NVL(f.budget_version_date,time.end_date)	 <=  time.end_date

-- To choose only those versions having version dates less than or equal to report date
-- Here, time is alias of the timeRelated table used in the actual SQL,
-- wherein, these Budget/Forecast SQL strings are actually concatenated
		      )';

Line: 353

			'( SELECT SUM(f.budget_g)            FII_EA_BUDGET
			     FROM '||l_budget_table_name||'  g	-- Profile option = N, Period Type = Month/Quarter
				  ,fii_gl_trend_sum_mv'||fii_ea_util_pkg.g_curr_view||'		f
			    WHERE f.time_id	   = '||l_budget_time_id||'
			      '||l_company_security||l_cost_center_security||'
			      AND top_node_fin_cat_type = ''OE''
			      AND NVL(f.budget_version_date,&BIS_CURRENT_ASOF_DATE)  <= &BIS_CURRENT_ASOF_DATE
			      AND :ASOF_DATE BETWEEN g.start_date AND g.end_date
			      AND NVL(f.budget_version_date,time.report_date)  <= time.report_date

-- To choose only those versions having version dates less than or equal to report date
-- Here, time is alias of the timeRelated table used in the actual SQL,
-- wherein, these Budget/Forecast SQL strings are actually concatenated

		      )';

Line: 377

			'( SELECT SUM(f.forecast_g) 		FII_EA_FORECAST
			     FROM '||l_forecast_table_name||' 	g  -- Profile option = N, Period Type = Month/Quarter
				  ,fii_gl_trend_sum_mv'||fii_ea_util_pkg.g_curr_view||'		f
			    WHERE f.time_id	   = '||l_forecast_time_id||'
			      '||l_company_security||l_cost_center_security||'
			      AND top_node_fin_cat_type = ''OE''
			      AND NVL(f.budget_version_date,&BIS_CURRENT_ASOF_DATE)  <= &BIS_CURRENT_ASOF_DATE
			      AND :ASOF_DATE BETWEEN g.start_date AND g.end_date
			      AND NVL(f.budget_version_date,time.report_date) <= time.report_date

-- To choose only those versions having version dates less than or equal to report date
-- Here, time is alias of the timeRelated table used in the actual SQL,
-- wherein, these Budget/Forecast SQL strings are actually concatenated

			 )';

Line: 404

	'SELECT VIEWBY
	       ,FII_EA_XTD_CUMUL_EXP
	       ,FII_EA_PRIOR_XTD_CUMUL_EXP
	       ,FII_EA_BUDGET
	       ,FII_EA_FORECAST
	   FROM
	       (SELECT month_name				VIEWBY 	--SQL 1
		       ,CASE WHEN FII_EFFECTIVE_NUM > :DISPLAY_SEQUENCE
		             THEN NULL
 	 	        ELSE SUM(FII_EA_XTD_CUMUL_EXP) OVER (ORDER BY FII_EFFECTIVE_NUM
					ROWS UNBOUNDED PRECEDING)
  			END						FII_EA_XTD_CUMUL_EXP
		       ,SUM(FII_EA_PRIOR_XTD_CUMUL_EXP) OVER (ORDER BY FII_EFFECTIVE_NUM
					ROWS UNBOUNDED PRECEDING)	FII_EA_PRIOR_XTD_CUMUL_EXP
		  FROM  ( SELECT  MAX(month_name)			month_name
			 	 ,FII_EFFECTIVE_NUM			FII_EFFECTIVE_NUM
               			 ,SUM(FII_EA_XTD_CUMUL_EXP)		FII_EA_XTD_CUMUL_EXP
                		 ,SUM(FII_EA_PRIOR_XTD_CUMUL_EXP)	FII_EA_PRIOR_XTD_CUMUL_EXP
		            FROM
			    (
 	      -- Following SQL is to calculate the values for prior period
			  SELECT  time.sequence				fii_effective_num
				 ,time.name				month_name
			         ,NULL					FII_EA_XTD_CUMUL_EXP
			         ,NVL(SUM(FII_EA_PRIOR_XTD_CUMUL_EXP),0)FII_EA_PRIOR_XTD_CUMUL_EXP
			    FROM
				(
				   SELECT time.sequence 	    	FII_EFFECTIVE_NUM
		 		  	 ,time.name     	    	month_name
					 ,NULL	 		   	FII_EA_XTD_CUMUL_EXP
			 		 ,f.actual_g			FII_EA_PRIOR_XTD_CUMUL_EXP
			  	    FROM  fii_time_ent_period       				time
                       	       		 ,fii_gl_trend_sum_mv'||fii_ea_util_pkg.g_curr_view||'	f
			           WHERE  f.time_id	   = time.ent_period_id
				        '||l_company_security||l_cost_center_security||' -- To restrict MV records based on
									                 -- Company-CC security access
				     AND  f.period_type_id = 32
				     AND top_node_fin_cat_type = ''OE''
				     AND  time.start_date >= :PRIOR_PERIOD_START
                  	   	     AND  time.end_date   <= :PRIOR_PERIOD_END
				 ) inner_view
				, fii_time_ent_period         time
		           WHERE inner_view.month_name (+)  = time.name	 -- Outer join to ensure that all the months are obtained
			     AND time.start_date	   >= :PRIOR_PERIOD_START
                  	     AND time.end_date		   <= :PRIOR_PERIOD_END
		     GROUP BY time.sequence
			     ,time.name

							UNION ALL

		-- Following SQL is to calculate the values for the current period

			  SELECT  time.sequence				fii_effective_num
				 ,time.name				month_name
			         ,NVL(SUM(FII_EA_XTD_CUMUL_EXP),0)	FII_EA_XTD_CUMUL_EXP
			         ,NULL					FII_EA_PRIOR_XTD_CUMUL_EXP
			    FROM
            	         	( SELECT  time.sequence			FII_EFFECTIVE_NUM
		     	   	         ,time.name 			month_name
			      	         ,f.actual_g			FII_EA_XTD_CUMUL_EXP
			      	         ,NULL				FII_EA_PRIOR_XTD_CUMUL_EXP
			           FROM  fii_time_ent_period  	    				time
		       	       	  	,fii_gl_trend_sum_mv'||fii_ea_util_pkg.g_curr_view||'	f
			          WHERE  f.time_id	 = time.ent_period_id
         	  	  	    '||l_company_security||l_cost_center_security||'  -- To restrict MV records based on
									              -- Company-CC security access
				    AND  f.period_type_id = 32
				    AND top_node_fin_cat_type = ''OE''
				    AND	 time.start_date >= :CURR_PERIOD_START
        	  		    AND	 time.end_date   <= :CURR_PERIOD_END
                  	        ) inner_view
				, fii_time_ent_period         time
		           WHERE inner_view.month_name (+)  = time.name	 -- Outer join to ensure that all the months are obtained
			     AND time.start_date	   >= :CURR_PERIOD_START
                  	     AND time.end_date		   <= :CURR_PERIOD_END
		        GROUP BY time.sequence
			        ,time.name
			 )
			   GROUP BY FII_EFFECTIVE_NUM
			   ORDER BY FII_EFFECTIVE_NUM
		        )) inner_view1
		, ( SELECT time.name								VIEW_BY
		          ,NVL(SUM(SUM(FII_EA_BUDGET))
				   OVER (ORDER BY time.sequence rows UNBOUNDED PRECEDING),0)	FII_EA_BUDGET
			  ,NVL(SUM(SUM(FII_EA_FORECAST))
				   OVER (ORDER BY time.sequence rows UNBOUNDED PRECEDING),0)	FII_EA_FORECAST
		      FROM
		         (
			   SELECT time.name						month_name
			 	 ,f.budget_g					FII_EA_BUDGET
				 ,f.forecast_g					FII_EA_FORECAST
			     FROM fii_time_ent_period					time
				 ,fii_gl_trend_sum_mv'||fii_ea_util_pkg.g_curr_view||'	f
			    WHERE  f.time_id	    =  time.ent_period_id
				'||l_company_security||l_cost_center_security||' -- To restrict MV records based on
									         -- Company-CC security access
			      AND f.period_type_id = 32
			      AND top_node_fin_cat_type = ''OE''
			      AND time.start_date >= :CURR_PERIOD_START
			      AND time.start_date <= :CURR_PERIOD_END
			  ) inner_view
  		 	  , fii_time_ent_period         time
		    WHERE inner_view.month_name (+)  = time.name  -- Outer join to ensure that all the months are obtained
		      AND time.start_date	   >= :CURR_PERIOD_START
                      AND time.end_date		   <= :CURR_PERIOD_END
		 GROUP BY time.sequence
		         ,time.name
	         ORDER BY time.sequence
		   ) inner_view2
	  WHERE inner_view1.viewby = inner_view2.view_by
	  ';

Line: 523

'SELECT days				VIEWBY
       ,CASE
	   WHEN days > TO_NUMBER('||l_display_adjustment||') THEN NULL
	ELSE
	   FII_EA_XTD_CUMUL_EXP
	END				FII_EA_XTD_CUMUL_EXP
       ,FII_EA_PRIOR_XTD_CUMUL_EXP	FII_EA_PRIOR_XTD_CUMUL_EXP
       ,inner_view2.FII_EA_BUDGET	FII_EA_BUDGET
       ,inner_view2.FII_EA_FORECAST	FII_EA_FORECAST
   FROM
       (
        SELECT 	 days							  --SQL 2
		,SUM (FII_EA_XTD_CUMUL_EXP)				FII_EA_XTD_CUMUL_EXP
		,SUM (FII_EA_PRIOR_XTD_CUMUL_EXP)			FII_EA_PRIOR_XTD_CUMUL_EXP
          FROM   (

	-- SQL to calculate the expenses for current period
		SELECT	time.report_date - '||l_current_adjustment_days||'  	DAYS
		       ,time.report_date					REPORT_DATE
		       ,NVL(SUM(SUM(FII_EA_XTD_CUMUL_EXP)) OVER (ORDER BY
				TO_NUMBER(time.report_date - '||l_current_adjustment_days||')
        					       ROWS UNBOUNDED PRECEDING
                                                   ),0)			FII_EA_XTD_CUMUL_EXP
		       ,NULL 						FII_EA_PRIOR_XTD_CUMUL_EXP
		  FROM
		     (
			SELECT time.report_date 				REPORT_DATE
			      ,f.actual_g					FII_EA_XTD_CUMUL_EXP
			      ,NULL						FII_EA_PRIOR_XTD_CUMUL_EXP
			  FROM fii_time_day		       				time
			      ,fii_gl_trend_sum_mv'||fii_ea_util_pkg.g_curr_view||'	f
			       '||l_comp_security_table||'
			       '||l_cc_security_table||'
 			 WHERE f.time_id    = time.report_date_julian
			       '||l_comp_security_days_clause||'
			       '||l_cc_security_days_clause||'		 -- To restrict MV records based on
								         -- Company-CC security access
   			   AND top_node_fin_cat_type = ''OE''
                           AND time.report_date BETWEEN :CURR_PERIOD_START AND :CURR_PERIOD_END
		     ) inner_view
		     , fii_time_day 	       			time
		 WHERE inner_view.report_date (+)  	  =	time.report_date -- Outer join to ensure that all the days are obtained
                   AND time.report_date BETWEEN :CURR_PERIOD_START AND :CURR_PERIOD_END
	      GROUP BY time.report_date - '||l_current_adjustment_days||'
		      ,time.report_date

 		UNION ALL

	-- SQL to calculate the expenses for prior period
		SELECT	time.report_date - '||l_prior_adjustment_days||'    	DAYS
		       ,time.report_date					REPORT_DATE
		       ,NULL 							FII_EA_XTD_CUMUL_EXP
		       ,NVL(SUM(SUM(FII_EA_PRIOR_XTD_CUMUL_EXP)) OVER (ORDER BY
				TO_NUMBER(time.report_date - '||l_prior_adjustment_days||')
        					       ROWS UNBOUNDED PRECEDING
                                                   ),0)				FII_EA_PRIOR_XTD_CUMUL_EXP
		  FROM
  		     ( SELECT time.report_date					REPORT_DATE
			     ,NULL						FII_EA_XTD_CUMUL_EXP
                             ,f.actual_g					FII_EA_PRIOR_XTD_CUMUL_EXP
		         FROM fii_time_day 						time
			     ,fii_gl_trend_sum_mv'||fii_ea_util_pkg.g_curr_view||'	f
			     '||l_comp_security_table||'
			     '||l_cc_security_table||'
			WHERE f.time_id    = time.report_date_julian
			     '||l_comp_security_days_clause||'
			     '||l_cc_security_days_clause||'	       -- To restrict MV records based on
								       -- Company-CC security access
			  AND top_node_fin_cat_type = ''OE''
                          AND time.report_date BETWEEN :PRIOR_PERIOD_START AND :PRIOR_PERIOD_END
		     ) inner_view
		     , fii_time_day 	       			time
		 WHERE inner_view.report_date (+)  	  =	time.report_date  -- Outer join to ensure that all the days are obtained
                   AND time.report_date BETWEEN :PRIOR_PERIOD_START AND :PRIOR_PERIOD_END
	      GROUP BY time.report_date - '||l_prior_adjustment_days||'
		      ,time.report_date
	      )
	GROUP BY days
	  )	inner_view1
	 ,(SELECT  ROUND(SUM(SUM(f.budget_g
				/(TO_NUMBER(time.ent_period_end_date - time.ent_period_start_date) + 1)))
			OVER (ORDER BY g.sequence ))	-- Done to show Budget at monthly level
								FII_EA_BUDGET
		  ,ROUND(SUM(SUM(f.forecast_g
				/(TO_NUMBER(time.ent_period_end_date - time.ent_period_start_date) + 1)))
			OVER (ORDER BY g.sequence ))	-- Done to show Forecast at monthly level
								FII_EA_FORECAST
		  ,g.sequence
		  ,time.report_date - '||l_current_adjustment_days||'
								DAYS2
	     FROM fii_time_ent_period					g
	         ,fii_time_day						time
		 ,fii_gl_trend_sum_mv'||fii_ea_util_pkg.g_curr_view||'	f
		 '||l_comp_security_table||'
		 '||l_cc_security_table||'
	   WHERE f.time_id    = g.ent_period_id
	     AND f.period_type_id = 32
	     AND top_node_fin_cat_type = ''OE''
	        '||l_comp_security_days_clause||'
		'||l_cc_security_days_clause||'		  -- To restrict MV records based on
	  						  -- Company-CC security access
	     AND g.ent_period_id    = time.ent_period_id
	     AND time.report_date BETWEEN :CURR_PERIOD_START AND :CURR_PERIOD_END
	GROUP BY g.sequence
		,time.report_date - '||l_current_adjustment_days||'
	) inner_view2
    WHERE inner_view2.days2 (+) = inner_view1.days  -- Outer join to ensure that all the days are obtained
 ORDER BY viewby

	';

Line: 641

	     'SELECT month_name				VIEWBY  --SQL 3
		      ,CASE WHEN FII_EFFECTIVE_NUM > :DISPLAY_SEQUENCE
			  THEN NULL
 	 	       ELSE SUM(FII_EA_XTD_CUMUL_EXP) OVER (ORDER BY FII_EFFECTIVE_NUM
					ROWS UNBOUNDED PRECEDING)
  			END						FII_EA_XTD_CUMUL_EXP
		      ,SUM(FII_EA_PRIOR_XTD_CUMUL_EXP) OVER (ORDER BY FII_EFFECTIVE_NUM
					ROWS UNBOUNDED PRECEDING)	FII_EA_PRIOR_XTD_CUMUL_EXP
		      ,FII_EA_BUDGET					FII_EA_BUDGET
  		      ,FII_EA_FORECAST					FII_EA_FORECAST
		FROM  ( SELECT    MAX(month_name)			month_name
			         ,FII_EFFECTIVE_NUM			FII_EFFECTIVE_NUM
               		         ,NVL(SUM(FII_EA_XTD_CUMUL_EXP),0)	FII_EA_XTD_CUMUL_EXP
                		 ,NVL(SUM(FII_EA_PRIOR_XTD_CUMUL_EXP),0)FII_EA_PRIOR_XTD_CUMUL_EXP
				 ,SUM(FII_EA_BUDGET)			FII_EA_BUDGET
				 ,SUM(FII_EA_FORECAST)			FII_EA_FORECAST
                            FROM
			    (
 	             		-- Following SQL is to calculate the values for prior period

		       SELECT  time.sequence				fii_effective_num
			      ,time.name				month_name
			      ,NULL					FII_EA_XTD_CUMUL_EXP
			      ,SUM(FII_EA_PRIOR_XTD_CUMUL_EXP)		FII_EA_PRIOR_XTD_CUMUL_EXP
			      ,NULL					FII_EA_BUDGET
			      ,NULL					FII_EA_FORECAST
			 FROM
			     (SELECT  time.sequence	    		fii_effective_num
		 		     ,time.name    	    		month_name
				     ,NULL	 	   		FII_EA_XTD_CUMUL_EXP
			 	     ,f.actual_g			FII_EA_PRIOR_XTD_CUMUL_EXP
				     ,NULL				FII_EA_BUDGET
				     ,NULL				FII_EA_FORECAST
 			  	FROM  fii_time_ent_period       				time
                       	       	     ,fii_gl_trend_sum_mv'||fii_ea_util_pkg.g_curr_view||'	f
			       WHERE  f.time_id	   = time.ent_period_id
				     '||l_company_security||l_cost_center_security||'  -- To restrict MV records based on
									               -- Company-CC security access
				 AND  f.period_type_id = 32
				 AND top_node_fin_cat_type = ''OE''
				 AND  time.start_date >= :PRIOR_PERIOD_START
                  	   	 AND  time.end_date   <= :PRIOR_PERIOD_END
			      ) inner_view
			     ,fii_time_ent_period       				time
			WHERE inner_view.month_name (+)	= time.name	-- Outer join to ensure that all the months are obtained
			  AND time.start_date	       >= :PRIOR_PERIOD_START
                  	  AND time.end_date	       <= :PRIOR_PERIOD_END
		     GROUP BY time.sequence
			     ,time.name

					UNION ALL

			-- Following SQL is to calculate the values for the current period

            	         	( SELECT  time.sequence			fii_effective_num
		     	   	         ,time.name 			month_name
			      	         ,SUM(f.actual_g)		FII_EA_XTD_CUMUL_EXP
			      	         ,NULL				FII_EA_PRIOR_XTD_CUMUL_EXP
					 ,NULL				FII_EA_BUDGET
					 ,NULL				FII_EA_FORECAST
 			           FROM  fii_time_ent_period  	    				time
		       	       	  	,fii_gl_trend_sum_mv'||fii_ea_util_pkg.g_curr_view||'	f
			          WHERE  f.time_id 	  = time.ent_period_id
				      '||l_company_security||l_cost_center_security||' -- To restrict MV records based on
									               -- Company-CC security access
				    AND  f.period_type_id = 32
				    AND top_node_fin_cat_type = ''OE''
				    AND	 time.start_date >= :CURR_PERIOD_START
        	  		    AND	 time.end_date   <= :CURR_PERIOD_END
                  	       GROUP BY  time.sequence
					,time.name
		     	        )
				UNION ALL
	-- SQL to calculate budget/forecast for the whole year irrespective of As of Date chosen by the user

				  SELECT  time.sequence			FII_EFFECTIVE_NUM
		   			 ,time.name 			month_name
					 ,NULL				FII_EA_XTD_CUMUL_EXP
 					 ,NULL  			FII_EA_PRIOR_XTD_CUMUL_EXP
					 ,'||l_sql_budget_ver_year||'	FII_EA_BUDGET
                     			 ,'||l_sql_forecast_ver_year||'	FII_EA_FORECAST
 		 	            FROM  fii_time_ent_period		time
  		       	           WHERE  time.start_date >= :CURR_PERIOD_START
                  		     AND  time.end_date   <= :CURR_PERIOD_END
		       	    )
			   GROUP BY FII_EFFECTIVE_NUM
			   ORDER BY FII_EFFECTIVE_NUM
		        )';

Line: 739

'SELECT days				VIEWBY
       ,CASE
	   WHEN days > TO_NUMBER('||l_display_adjustment||') THEN NULL
	ELSE
	   FII_EA_XTD_CUMUL_EXP
	END				FII_EA_XTD_CUMUL_EXP
       ,FII_EA_PRIOR_XTD_CUMUL_EXP
       ,FII_EA_BUDGET
       ,FII_EA_FORECAST
   FROM
       (SELECT 	 days						--SQL 4
		,SUM(FII_EA_XTD_CUMUL_EXP)				FII_EA_XTD_CUMUL_EXP
		,SUM(FII_EA_PRIOR_XTD_CUMUL_EXP)			FII_EA_PRIOR_XTD_CUMUL_EXP
		,SUM(FII_EA_BUDGET)					FII_EA_BUDGET
 	        ,SUM(FII_EA_FORECAST)  					FII_EA_FORECAST
       FROM   (

	-- SQL to calculate the expenses for current period
       SELECT time.report_date - '||l_current_adjustment_days||'	DAYS
             ,time.report_date						REPORT_DATE
	     ,NVL(SUM(SUM(FII_EA_XTD_CUMUL_EXP)) OVER (ORDER BY
				TO_NUMBER(time.report_date - '||l_current_adjustment_days||')
        					       ROWS UNBOUNDED PRECEDING
                                                  ),0)
			     								FII_EA_XTD_CUMUL_EXP
	     ,NULL									FII_EA_PRIOR_XTD_CUMUL_EXP
	     ,'||l_sql_budget_ver_month_qtr||'						FII_EA_BUDGET
	     ,'||l_sql_forecast_ver_month_qtr||'					FII_EA_FORECAST
	 FROM
	     (
	       SELECT	time.report_date					REPORT_DATE
		       ,f.actual_g						FII_EA_XTD_CUMUL_EXP
		       ,NULL 							FII_EA_PRIOR_XTD_CUMUL_EXP
		       ,NULL							FII_EA_BUDGET
	               ,NULL							FII_EA_FORECAST
		  FROM	fii_time_day 	       					time
                       ,fii_gl_trend_sum_mv'||fii_ea_util_pkg.g_curr_view||'	f
		       '||l_comp_security_table||'
		       '||l_cc_security_table||'
 		 WHERE	f.time_id    = time.report_date_julian
		       '||l_comp_security_days_clause||'
		       '||l_cc_security_days_clause||'		 -- To restrict MV records based on
							         -- Company-CC security access
   		   AND top_node_fin_cat_type = ''OE''
		   AND  time.report_date BETWEEN :CURR_PERIOD_START AND :CURR_PERIOD_END

	     ) inner_view
             , fii_time_day 	       			time
        WHERE inner_view.report_date (+)  	  =	time.report_date  -- Outer join to ensure that all the days are obtained
          AND time.report_date BETWEEN :CURR_PERIOD_START AND :CURR_PERIOD_END
     GROUP BY time.report_date - '||l_current_adjustment_days||'
             ,time.report_date

 		UNION ALL

				-- SQL to calculate the expenses for prior period
       SELECT time.report_date - '||l_prior_adjustment_days||'			DAYS
             ,time.report_date							REPORT_DATE
	     ,NULL								FII_EA_XTD_CUMUL_EXP
	     ,NVL(SUM(SUM(FII_EA_PRIOR_XTD_CUMUL_EXP)) OVER (ORDER BY
				TO_NUMBER(time.report_date - '||l_prior_adjustment_days||')
        					       ROWS UNBOUNDED PRECEDING
                                                  ),0)
			     								FII_EA_PRIOR_XTD_CUMUL_EXP

	     ,NULL									FII_EA_BUDGET
	     ,NULL									FII_EA_FORECAST
	 FROM
	     (
  		SELECT	time.report_date						REPORT_DATE
		       ,NULL								FII_EA_XTD_CUMUL_EXP
                       ,f.actual_g							FII_EA_PRIOR_XTD_CUMUL_EXP
 		       ,NULL								FII_EA_BUDGET
		       ,NULL								FII_EA_FORECAST
 		  FROM	fii_time_day							time
                       ,fii_gl_trend_sum_mv'||fii_ea_util_pkg.g_curr_view||'		f
		       '||l_comp_security_table||'
		       '||l_cc_security_table||'
		 WHERE	f.time_id    = time.report_date_julian
		       '||l_comp_security_days_clause||'
		       '||l_cc_security_days_clause||'		 -- To restrict MV records based on
							         -- Company-CC security access
		   AND top_node_fin_cat_type = ''OE''
		   AND	time.report_date BETWEEN :PRIOR_PERIOD_START AND :PRIOR_PERIOD_END
	     ) inner_view
              ,fii_time_day 			time
        WHERE inner_view.report_date (+)  =	time.report_date   -- Outer join to ensure that all the days are obtained
          AND time.report_date BETWEEN :PRIOR_PERIOD_START AND :PRIOR_PERIOD_END
     GROUP BY time.report_date - '||l_prior_adjustment_days||'
             ,time.report_date
	      )
  GROUP BY days
	) ORDER BY days';