SELECT fc.name C_FORECAST_NAME,
fch.name C_TEMPLATE_NAME,
'GL Periods' C_AGING_TYPE,
null C_START_DATE,
fc.start_period C_START_PERIOD
FROM ce_forecasts fc,
ce_forecast_headers fch
WHERE fc.forecast_header_id = fch.forecast_header_id
AND fc.forecast_header_id = nvl(:P_FORECAST_HEADER_ID, fc.forecast_header_id)
AND :P_FORECAST_BY in ('D','B')
AND fch.aging_type = 'D'
AND trunc(fc.start_date) >= nvl(:P_FORECAST_START_DATE_1, trunc(fc.start_date))
and trunc(fc.start_date) <= nvl(:P_FORECAST_END_DATE_1, trunc(fc.start_date))
UNION ALL
SELECT fc.name C_FORECAST_NAME,
fch.name C_TEMPLATE_NAME,
'GL Periods' C_AGING_TYPE,
null C_START_DATE,
fc.start_period C_START_PERIOD
FROM ce_forecasts fc,
ce_forecast_headers fch
WHERE fc.forecast_header_id = fch.forecast_header_id
AND fc.forecast_header_id = nvl(:P_FORECAST_HEADER_ID, fc.forecast_header_id)
AND :P_FORECAST_BY in ('A','B')
AND fch.aging_type = 'A'
AND fc.start_period in
(
select glp.period_name
from gl_periods glp
where glp.period_set_name = fc.period_set_name
and trunc(glp.start_date) >= nvl(:P_FORECAST_START_DATE_1,trunc(glp.start_date))
and trunc(glp.start_date) <= nvl(:P_FORECAST_END_DATE_1,trunc(glp.start_date)))
ORDER BY C_FORECAST_NAME |