VIEW: APPS.PA_FCST_PERIODIC_DTL_V

Source

View Text - Preformatted

SELECT forecast_exists_flag, project_id, y1, y1_leftattribute1, y1_leftattribute2, y1_leftattribute3, DENSE_RANK() OVER (ORDER BY y1,project_id) AS y1_seq, x1, DENSE_RANK() OVER (PARTITION BY project_id ORDER BY period_start_date ) AS x1_seq, value , period_start_date FROM ( SELECT 'Y' forecast_exists_flag, pr.project_id project_id, pr.project_name||'('||pr.project_number||')' Y1, pr.Project_org_name Y1_LEFTATTRIBUTE1, pr.customer_name Y1_LEFTATTRIBUTE2, pr.project_currency_code Y1_LEFTATTRIBUTE3, 0 y1_seq, pr.period_name X1, 0 x1_seq, pr.value value, pr.period_start_date period_start_date FROM pa_fcst_project_sum_v pr UNION ALL SELECT 'Y' forecast_exists_flag, pr.project_id project_id, pr.project_name||'('||pr.project_number||')' Y1, org.name Y1_LEFTATTRIBUTE1, pr.customer_name Y1_LEFTATTRIBUTE2, pr.project_currency_code Y1_LEFTATTRIBUTE3, 0 y1_seq, per.period_name X1, 0 x1_seq, 0 value, per.start_date period_start_date FROM pa_fcst_project_list_v pr, hr_all_organization_units_tl org, pa_fcst_periods_tmp per, (SELECT pa_fcst_global.GetProjFcstStartDate StartDate, pa_fcst_global.GetProjFcstEndDate EndDate FROM DUAL) FilterBy WHERE NOT EXISTS(select null from pa_budget_versions bv, pa_resource_assignments ra, pa_budget_lines bl WHERE bv.project_id = pr.project_id AND bv.budget_type_code = 'FORECASTING_BUDGET_TYPE' AND bv.budget_version_id = ra.budget_version_id AND ra.resource_list_member_id = 103 AND bv.CURRENT_ORIGINAL_FLAG ='X' AND bv.RESOURCE_ACCUMULATED_FLAG ='X' AND ra.resource_assignment_id = bl.resource_assignment_id AND per.period_name = bl.period_name) AND org.organization_id = pr.carrying_out_organization_id AND org.language = userenv('LANG') AND ((per.start_date BETWEEN FilterBy.StartDate AND FilterBy.EndDate) OR (per.end_date BETWEEN FilterBy.StartDate AND FilterBy.EndDate)) UNION ALL SELECT 'N' forecast_exists_flag, 0 project_id, NULL y1, NULL y1_leftattribute1, NULL y1_leftattribute2, NULL y1_leftattribute3, 0 y1_seq, per.period_name X1, 0 x1_seq, 0 value, per.start_date period_start_date FROM pa_fcst_periods_tmp per, (SELECT pa_fcst_global.GetProjFcstStartDate StartDate, pa_fcst_global.GetProjFcstEndDate EndDate FROM DUAL) FilterBy WHERE ((per.start_date BETWEEN FilterBy.StartDate AND FilterBy.EndDate) OR (per.end_date BETWEEN FilterBy.StartDate AND FilterBy.EndDate))) ORDER BY y1,project_id, period_start_date

View Text - HTML Formatted

SELECT FORECAST_EXISTS_FLAG
, PROJECT_ID
, Y1
, Y1_LEFTATTRIBUTE1
, Y1_LEFTATTRIBUTE2
, Y1_LEFTATTRIBUTE3
, DENSE_RANK() OVER (ORDER BY Y1
, PROJECT_ID) AS Y1_SEQ
, X1
, DENSE_RANK() OVER (PARTITION BY PROJECT_ID ORDER BY PERIOD_START_DATE ) AS X1_SEQ
, VALUE
, PERIOD_START_DATE
FROM ( SELECT 'Y' FORECAST_EXISTS_FLAG
, PR.PROJECT_ID PROJECT_ID
, PR.PROJECT_NAME||'('||PR.PROJECT_NUMBER||')' Y1
, PR.PROJECT_ORG_NAME Y1_LEFTATTRIBUTE1
, PR.CUSTOMER_NAME Y1_LEFTATTRIBUTE2
, PR.PROJECT_CURRENCY_CODE Y1_LEFTATTRIBUTE3
, 0 Y1_SEQ
, PR.PERIOD_NAME X1
, 0 X1_SEQ
, PR.VALUE VALUE
, PR.PERIOD_START_DATE PERIOD_START_DATE
FROM PA_FCST_PROJECT_SUM_V PR UNION ALL SELECT 'Y' FORECAST_EXISTS_FLAG
, PR.PROJECT_ID PROJECT_ID
, PR.PROJECT_NAME||'('||PR.PROJECT_NUMBER||')' Y1
, ORG.NAME Y1_LEFTATTRIBUTE1
, PR.CUSTOMER_NAME Y1_LEFTATTRIBUTE2
, PR.PROJECT_CURRENCY_CODE Y1_LEFTATTRIBUTE3
, 0 Y1_SEQ
, PER.PERIOD_NAME X1
, 0 X1_SEQ
, 0 VALUE
, PER.START_DATE PERIOD_START_DATE
FROM PA_FCST_PROJECT_LIST_V PR
, HR_ALL_ORGANIZATION_UNITS_TL ORG
, PA_FCST_PERIODS_TMP PER
, (SELECT PA_FCST_GLOBAL.GETPROJFCSTSTARTDATE STARTDATE
, PA_FCST_GLOBAL.GETPROJFCSTENDDATE ENDDATE
FROM DUAL) FILTERBY
WHERE NOT EXISTS(SELECT NULL
FROM PA_BUDGET_VERSIONS BV
, PA_RESOURCE_ASSIGNMENTS RA
, PA_BUDGET_LINES BL
WHERE BV.PROJECT_ID = PR.PROJECT_ID
AND BV.BUDGET_TYPE_CODE = 'FORECASTING_BUDGET_TYPE'
AND BV.BUDGET_VERSION_ID = RA.BUDGET_VERSION_ID
AND RA.RESOURCE_LIST_MEMBER_ID = 103
AND BV.CURRENT_ORIGINAL_FLAG ='X'
AND BV.RESOURCE_ACCUMULATED_FLAG ='X'
AND RA.RESOURCE_ASSIGNMENT_ID = BL.RESOURCE_ASSIGNMENT_ID
AND PER.PERIOD_NAME = BL.PERIOD_NAME)
AND ORG.ORGANIZATION_ID = PR.CARRYING_OUT_ORGANIZATION_ID
AND ORG.LANGUAGE = USERENV('LANG')
AND ((PER.START_DATE BETWEEN FILTERBY.STARTDATE
AND FILTERBY.ENDDATE) OR (PER.END_DATE BETWEEN FILTERBY.STARTDATE
AND FILTERBY.ENDDATE)) UNION ALL SELECT 'N' FORECAST_EXISTS_FLAG
, 0 PROJECT_ID
, NULL Y1
, NULL Y1_LEFTATTRIBUTE1
, NULL Y1_LEFTATTRIBUTE2
, NULL Y1_LEFTATTRIBUTE3
, 0 Y1_SEQ
, PER.PERIOD_NAME X1
, 0 X1_SEQ
, 0 VALUE
, PER.START_DATE PERIOD_START_DATE
FROM PA_FCST_PERIODS_TMP PER
, (SELECT PA_FCST_GLOBAL.GETPROJFCSTSTARTDATE STARTDATE
, PA_FCST_GLOBAL.GETPROJFCSTENDDATE ENDDATE
FROM DUAL) FILTERBY
WHERE ((PER.START_DATE BETWEEN FILTERBY.STARTDATE
AND FILTERBY.ENDDATE) OR (PER.END_DATE BETWEEN FILTERBY.STARTDATE
AND FILTERBY.ENDDATE))) ORDER BY Y1
, PROJECT_ID
, PERIOD_START_DATE