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.

VIEW: APPS.PA_FCST_PROJECT_DTL_V

Source

View Text - Preformatted


SELECT "PROJECT_ID","PROJECT_NAME","PROJECT_NUMBER","ORGANIZATION_NAME","CUSTOMER_NAME","PROJECT_OWNER","PROJECT_TYPE","CLASSIFICATION","PROJECT_STATUS","AS_OF_DATE","CURRENCY_CODE","DISCOUNT_PERCENTAGE","PROBABILITY_PERCENTAGE","PROJECT_TOTAL" FROM ( SELECT pr.project_id project_id, pr.project_name project_name, pr.project_number project_number, hr.name Organization_name, pr.customer_name customer_name , pa_fcst_global.find_project_owner(pr.project_id,pr.start_date, pr.completion_date) project_owner, pr.project_type project_type, pc.class_code classification, pps.project_status_name project_status, pbv.plan_run_date as_of_date, pr.project_currency_code currency_code, to_char(ROUND(ra.average_discount_percentage,2),'999,999,990.99') Discount_percentage, to_char(ROUND(pr.PROBABILITY_PERCENTAGE,2),'999,999,990.99') probability_percentage, to_char(DECODE(pa_fcst_global.GetProjFcstShowAmount , 'LABOR_HOURS', ROUND(ra.total_plan_quantity * DECODE (pa_fcst_global.GetProbabilityPerFlag,'N',1, DECODE(nvl(pr.PROBABILITY_PERCENTAGE,1),1,1, pr.PROBABILITY_PERCENTAGE/100)),2), 'COST', ROUND(ra.total_plan_burdened_cost * DECODE (pa_fcst_global.GetProbabilityPerFlag,'N',1, DECODE(nvl(pr.PROBABILITY_PERCENTAGE,1),1,1, pr.PROBABILITY_PERCENTAGE/100)),2), 'REVENUE', ROUND(ra.total_plan_revenue * DECODE (pa_fcst_global.GetProbabilityPerFlag,'N',1, DECODE(nvl(pr.PROBABILITY_PERCENTAGE,1),1,1, pr.PROBABILITY_PERCENTAGE/100)),2), 'MARGIN', ROUND((ra.total_plan_revenue - ra.total_plan_burdened_cost) * DECODE (pa_fcst_global.GetProbabilityPerFlag,'N',1, DECODE(nvl(pr.PROBABILITY_PERCENTAGE,1),1,1, pr.PROBABILITY_PERCENTAGE/100)),2), 'MARGIN_PERCENTAGE', ROUND(DECODE(NVL(ra.total_plan_revenue,0),0,0, ((ra.total_plan_revenue - ra.total_plan_burdened_cost) * 100)/ra.total_plan_revenue) * DECODE (pa_fcst_global.GetProbabilityPerFlag,'N',1, DECODE(nvl(pr.PROBABILITY_PERCENTAGE,1),1,1, pr.PROBABILITY_PERCENTAGE/100)),2)) ,'999,999,999,999,990.99') project_total FROM pa_fcst_project_list_v pr, pa_project_statuses pps, hr_all_organization_units_tl hr, pa_budget_versions pbv, pa_resource_assignments ra, pa_project_classes pc WHERE pr.project_id =pbv.project_id AND pps.project_status_code = pr.project_status_code AND pbv.budget_type_code ='FORECASTING_BUDGET_TYPE' AND pbv.budget_version_id = ra.budget_version_id AND pbv.CURRENT_ORIGINAL_FLAG ='X' AND pbv.RESOURCE_ACCUMULATED_FLAG ='X' AND ra.resource_list_member_id = 103 AND hr.organization_id= pr.carrying_out_organization_id AND hr.language = userenv('LANG') AND pc.class_category(+) = pa_fcst_global.GetClassCatgory AND pr.project_id = pc.project_id(+) UNION SELECT pr.project_id Project_id , pr.project_name Project_name , pr.project_number project_number, hr.name Organization_name, pr.customer_name customer_name , pa_fcst_global.find_project_owner(pr.project_id,pr.start_date,pr.completion_date) project_owner, pr.project_type project_type, pc.class_code classification, pps.project_status_name project_status, sysdate as_of_date, pr.project_currency_code currency_code, to_char(0,'999,999,990.99') Discount_percentage, to_char(pr.PROBABILITY_PERCENTAGE,'999,999,990.99') probability_percentage, to_char(0,'999,999,999,999,990.99') project_total FROM pa_fcst_project_list_v pr, pa_project_statuses pps, hr_all_organization_units_tl hr, pa_project_classes pc WHERE pps.project_status_code = pr.project_status_code AND hr.organization_id= pr.carrying_out_organization_id AND hr.language = userenv('LANG') AND pc.class_category(+) = pa_fcst_global.GetClassCatgory AND pr.project_id = pc.project_id(+) AND NOT EXISTS (SELECT NULL FROM pa_budget_versions pbv, pa_resource_assignments ra WHERE pbv.project_id = pr.project_id AND pbv.budget_version_id = ra.budget_version_id AND ra.project_id = pr.project_id AND pbv.budget_type_code ='FORECASTING_BUDGET_TYPE' AND pbv.CURRENT_ORIGINAL_FLAG ='X' AND pbv.RESOURCE_ACCUMULATED_FLAG ='X') ) order by project_name,project_id

View Text - HTML Formatted

SELECT "PROJECT_ID"
, "PROJECT_NAME"
, "PROJECT_NUMBER"
, "ORGANIZATION_NAME"
, "CUSTOMER_NAME"
, "PROJECT_OWNER"
, "PROJECT_TYPE"
, "CLASSIFICATION"
, "PROJECT_STATUS"
, "AS_OF_DATE"
, "CURRENCY_CODE"
, "DISCOUNT_PERCENTAGE"
, "PROBABILITY_PERCENTAGE"
, "PROJECT_TOTAL"
FROM ( SELECT PR.PROJECT_ID PROJECT_ID
, PR.PROJECT_NAME PROJECT_NAME
, PR.PROJECT_NUMBER PROJECT_NUMBER
, HR.NAME ORGANIZATION_NAME
, PR.CUSTOMER_NAME CUSTOMER_NAME
, PA_FCST_GLOBAL.FIND_PROJECT_OWNER(PR.PROJECT_ID
, PR.START_DATE
, PR.COMPLETION_DATE) PROJECT_OWNER
, PR.PROJECT_TYPE PROJECT_TYPE
, PC.CLASS_CODE CLASSIFICATION
, PPS.PROJECT_STATUS_NAME PROJECT_STATUS
, PBV.PLAN_RUN_DATE AS_OF_DATE
, PR.PROJECT_CURRENCY_CODE CURRENCY_CODE
, TO_CHAR(ROUND(RA.AVERAGE_DISCOUNT_PERCENTAGE
, 2)
, '999
, 999
, 990.99') DISCOUNT_PERCENTAGE
, TO_CHAR(ROUND(PR.PROBABILITY_PERCENTAGE
, 2)
, '999
, 999
, 990.99') PROBABILITY_PERCENTAGE
, TO_CHAR(DECODE(PA_FCST_GLOBAL.GETPROJFCSTSHOWAMOUNT
, 'LABOR_HOURS'
, ROUND(RA.TOTAL_PLAN_QUANTITY * DECODE (PA_FCST_GLOBAL.GETPROBABILITYPERFLAG
, 'N'
, 1
, DECODE(NVL(PR.PROBABILITY_PERCENTAGE
, 1)
, 1
, 1
, PR.PROBABILITY_PERCENTAGE/100))
, 2)
, 'COST'
, ROUND(RA.TOTAL_PLAN_BURDENED_COST * DECODE (PA_FCST_GLOBAL.GETPROBABILITYPERFLAG
, 'N'
, 1
, DECODE(NVL(PR.PROBABILITY_PERCENTAGE
, 1)
, 1
, 1
, PR.PROBABILITY_PERCENTAGE/100))
, 2)
, 'REVENUE'
, ROUND(RA.TOTAL_PLAN_REVENUE * DECODE (PA_FCST_GLOBAL.GETPROBABILITYPERFLAG
, 'N'
, 1
, DECODE(NVL(PR.PROBABILITY_PERCENTAGE
, 1)
, 1
, 1
, PR.PROBABILITY_PERCENTAGE/100))
, 2)
, 'MARGIN'
, ROUND((RA.TOTAL_PLAN_REVENUE - RA.TOTAL_PLAN_BURDENED_COST) * DECODE (PA_FCST_GLOBAL.GETPROBABILITYPERFLAG
, 'N'
, 1
, DECODE(NVL(PR.PROBABILITY_PERCENTAGE
, 1)
, 1
, 1
, PR.PROBABILITY_PERCENTAGE/100))
, 2)
, 'MARGIN_PERCENTAGE'
, ROUND(DECODE(NVL(RA.TOTAL_PLAN_REVENUE
, 0)
, 0
, 0
, ((RA.TOTAL_PLAN_REVENUE - RA.TOTAL_PLAN_BURDENED_COST) * 100)/RA.TOTAL_PLAN_REVENUE) * DECODE (PA_FCST_GLOBAL.GETPROBABILITYPERFLAG
, 'N'
, 1
, DECODE(NVL(PR.PROBABILITY_PERCENTAGE
, 1)
, 1
, 1
, PR.PROBABILITY_PERCENTAGE/100))
, 2))
, '999
, 999
, 999
, 999
, 990.99') PROJECT_TOTAL
FROM PA_FCST_PROJECT_LIST_V PR
, PA_PROJECT_STATUSES PPS
, HR_ALL_ORGANIZATION_UNITS_TL HR
, PA_BUDGET_VERSIONS PBV
, PA_RESOURCE_ASSIGNMENTS RA
, PA_PROJECT_CLASSES PC
WHERE PR.PROJECT_ID =PBV.PROJECT_ID
AND PPS.PROJECT_STATUS_CODE = PR.PROJECT_STATUS_CODE
AND PBV.BUDGET_TYPE_CODE ='FORECASTING_BUDGET_TYPE'
AND PBV.BUDGET_VERSION_ID = RA.BUDGET_VERSION_ID
AND PBV.CURRENT_ORIGINAL_FLAG ='X'
AND PBV.RESOURCE_ACCUMULATED_FLAG ='X'
AND RA.RESOURCE_LIST_MEMBER_ID = 103
AND HR.ORGANIZATION_ID= PR.CARRYING_OUT_ORGANIZATION_ID
AND HR.LANGUAGE = USERENV('LANG')
AND PC.CLASS_CATEGORY(+) = PA_FCST_GLOBAL.GETCLASSCATGORY
AND PR.PROJECT_ID = PC.PROJECT_ID(+) UNION SELECT PR.PROJECT_ID PROJECT_ID
, PR.PROJECT_NAME PROJECT_NAME
, PR.PROJECT_NUMBER PROJECT_NUMBER
, HR.NAME ORGANIZATION_NAME
, PR.CUSTOMER_NAME CUSTOMER_NAME
, PA_FCST_GLOBAL.FIND_PROJECT_OWNER(PR.PROJECT_ID
, PR.START_DATE
, PR.COMPLETION_DATE) PROJECT_OWNER
, PR.PROJECT_TYPE PROJECT_TYPE
, PC.CLASS_CODE CLASSIFICATION
, PPS.PROJECT_STATUS_NAME PROJECT_STATUS
, SYSDATE AS_OF_DATE
, PR.PROJECT_CURRENCY_CODE CURRENCY_CODE
, TO_CHAR(0
, '999
, 999
, 990.99') DISCOUNT_PERCENTAGE
, TO_CHAR(PR.PROBABILITY_PERCENTAGE
, '999
, 999
, 990.99') PROBABILITY_PERCENTAGE
, TO_CHAR(0
, '999
, 999
, 999
, 999
, 990.99') PROJECT_TOTAL
FROM PA_FCST_PROJECT_LIST_V PR
, PA_PROJECT_STATUSES PPS
, HR_ALL_ORGANIZATION_UNITS_TL HR
, PA_PROJECT_CLASSES PC
WHERE PPS.PROJECT_STATUS_CODE = PR.PROJECT_STATUS_CODE
AND HR.ORGANIZATION_ID= PR.CARRYING_OUT_ORGANIZATION_ID
AND HR.LANGUAGE = USERENV('LANG')
AND PC.CLASS_CATEGORY(+) = PA_FCST_GLOBAL.GETCLASSCATGORY
AND PR.PROJECT_ID = PC.PROJECT_ID(+)
AND NOT EXISTS (SELECT NULL
FROM PA_BUDGET_VERSIONS PBV
, PA_RESOURCE_ASSIGNMENTS RA
WHERE PBV.PROJECT_ID = PR.PROJECT_ID
AND PBV.BUDGET_VERSION_ID = RA.BUDGET_VERSION_ID
AND RA.PROJECT_ID = PR.PROJECT_ID
AND PBV.BUDGET_TYPE_CODE ='FORECASTING_BUDGET_TYPE'
AND PBV.CURRENT_ORIGINAL_FLAG ='X'
AND PBV.RESOURCE_ACCUMULATED_FLAG ='X') ) ORDER BY PROJECT_NAME
, PROJECT_ID