VIEW: APPS.PA_FP_WEBADI_NON_PERIODIC_V

Source

View Text - Preformatted

SELECT pra.budget_version_id, ppfo.fin_plan_preference_code, pt.plan_class_code, p.project_id, p.name, p.segment1, pra.resource_assignment_id, nvl(pe.name, p.long_name) task_name, nvl(pe.element_number, p.segment1) task_number, nvl(pev.display_sequence, -1) as task_display_sequence, prlm.alias resource_name, pra.resource_list_member_id, pra.planning_start_date, pra.planning_end_date, nvl(rac.txn_currency_code,DECODE(pbv.ci_id, null, DECODE(pbv.approved_rev_plan_type_flag, 'Y', p.projfunc_currency_code, p.project_currency_code), DECODE(pbv.version_type, 'ALL', PA_FIN_PLAN_UTILS2.get_agreement_currency(pra.budget_version_id), 'REVENUE', PA_FIN_PLAN_UTILS2.get_agreement_currency(pra.budget_version_id), DECODE(pbv.approved_rev_plan_type_flag, 'Y', p.projfunc_currency_code, p.project_currency_code)))) as currency, uom.meaning, decode(nvl(nvl(ppfo.cost_time_phased_code,ppfo.revenue_time_phased_code),ppfo.all_time_phased_code), 'N',PA_FIN_PLAN_UTILS.Get_NP_RA_Description(pra.resource_assignment_id,rac.txn_currency_code),null) description, pra.spread_curve_id spread_curve_id, psc.name spread_curve, pra.mfc_cost_type_id mfc_cost_type_id, cct.COST_TYPE mfc_cost_type, pra.etc_method_code etc_method_code, etc.meaning etc_method, null delete_flag, rac.total_display_quantity, rac.total_init_quantity , rac.total_display_quantity-NVL(rac.total_init_quantity,0), PA_FP_WEBADI_UTILS.get_current_amount(ppfo.fin_plan_type_id,pt.plan_class_code,p.project_id,ppfo.fin_plan_preference_code,pra.task_id, pra.resource_list_member_id,NVL(pra.UNIT_OF_MEASURE,'HOURS'),rac.TXN_CURRENCY_CODE,'QUANTITY') current_quantity, PA_FP_WEBADI_UTILS.get_original_amount(ppfo.fin_plan_type_id,pt.plan_class_code,p.project_id,ppfo.fin_plan_preference_code,pra.task_id, pra.resource_list_member_id,NVL(pra.UNIT_OF_MEASURE,'HOURS'),rac.TXN_CURRENCY_CODE,'QUANTITY')original_quantity, PA_FP_WEBADI_UTILS.get_prior_forecast_amount(ppfo.fin_plan_type_id,pt.plan_class_code,p.project_id,ppfo.fin_plan_preference_code,pra.task_id, pra.resource_list_member_id,NVL(pra.UNIT_OF_MEASURE,'HOURS'),rac.TXN_CURRENCY_CODE,'QUANTITY') prior_forecast_quantity, rac.total_txn_raw_cost, rac.total_txn_init_raw_cost, rac.total_txn_raw_cost-NVL(rac.total_txn_init_raw_cost,0), PA_FP_WEBADI_UTILS.get_current_amount(ppfo.fin_plan_type_id,pt.plan_class_code,p.project_id,ppfo.fin_plan_preference_code,pra.task_id, pra.resource_list_member_id,NVL(pra.UNIT_OF_MEASURE,'HOURS'),rac.TXN_CURRENCY_CODE,'RAW_COST') current_raw_cost, PA_FP_WEBADI_UTILS.get_original_amount(ppfo.fin_plan_type_id,pt.plan_class_code,p.project_id,ppfo.fin_plan_preference_code,pra.task_id, pra.resource_list_member_id,NVL(pra.UNIT_OF_MEASURE,'HOURS'),rac.TXN_CURRENCY_CODE,'RAW_COST') original_raw_cost, PA_FP_WEBADI_UTILS.get_prior_forecast_amount(ppfo.fin_plan_type_id,pt.plan_class_code,p.project_id,ppfo.fin_plan_preference_code,pra.task_id, pra.resource_list_member_id,NVL(pra.UNIT_OF_MEASURE,'HOURS'),rac.TXN_CURRENCY_CODE,'RAW_COST') prior_forecast_raw_cost, rac.total_txn_burdened_cost, rac.total_txn_init_burdened_cost, rac.total_txn_burdened_cost-NVL(rac.total_txn_init_burdened_cost,0), PA_FP_WEBADI_UTILS.get_current_amount(ppfo.fin_plan_type_id,pt.plan_class_code,p.project_id,ppfo.fin_plan_preference_code,pra.task_id, pra.resource_list_member_id,NVL(pra.UNIT_OF_MEASURE,'HOURS'),rac.TXN_CURRENCY_CODE,'BURDENED_COST') current_burdened_cost, PA_FP_WEBADI_UTILS.get_original_amount(ppfo.fin_plan_type_id,pt.plan_class_code,p.project_id,ppfo.fin_plan_preference_code,pra.task_id, pra.resource_list_member_id,NVL(pra.UNIT_OF_MEASURE,'HOURS'),rac.TXN_CURRENCY_CODE,'BURDENED_COST') original_burdened_cost, PA_FP_WEBADI_UTILS.get_prior_forecast_amount(ppfo.fin_plan_type_id,pt.plan_class_code,p.project_id,ppfo.fin_plan_preference_code,pra.task_id, pra.resource_list_member_id,NVL(pra.UNIT_OF_MEASURE,'HOURS'),rac.TXN_CURRENCY_CODE,'BURDENED_COST') prior_forecast_burdened_cost, rac.total_txn_revenue, rac.total_txn_init_revenue, rac.total_txn_revenue-NVL(rac.total_txn_init_revenue,0), PA_FP_WEBADI_UTILS.get_current_amount(ppfo.fin_plan_type_id,pt.plan_class_code,p.project_id,ppfo.fin_plan_preference_code,pra.task_id, pra.resource_list_member_id,NVL(pra.UNIT_OF_MEASURE,'HOURS'),rac.TXN_CURRENCY_CODE,'REVENUE') current_revenue, PA_FP_WEBADI_UTILS.get_original_amount(ppfo.fin_plan_type_id,pt.plan_class_code,p.project_id,ppfo.fin_plan_preference_code,pra.task_id, pra.resource_list_member_id,NVL(pra.UNIT_OF_MEASURE,'HOURS'),rac.TXN_CURRENCY_CODE,'REVENUE') original_revenue, PA_FP_WEBADI_UTILS.get_prior_forecast_amount(ppfo.fin_plan_type_id,pt.plan_class_code,p.project_id,ppfo.fin_plan_preference_code,pra.task_id, pra.resource_list_member_id,NVL(pra.UNIT_OF_MEASURE,'HOURS'),rac.TXN_CURRENCY_CODE,'REVENUE') prior_forecast_revenue, DECODE(pra.rate_based_flag,'Y', rac.txn_raw_cost_rate_override,NULL), DECODE(pra.rate_based_flag,'Y', rac.txn_burden_cost_rate_override,NULL), DECODE(pra.rate_based_flag,'Y', rac.txn_bill_rate_override,NULL), DECODE(pra.rate_based_flag,'Y', rac.txn_average_raw_cost_rate, NULL), DECODE(pra.rate_based_flag,'Y', rac.txn_average_burden_cost_rate, NULL), DECODE(pra.rate_based_flag,'Y', rac.txn_average_bill_rate, NULL), DECODE(pra.rate_based_flag,'Y', rac.txn_etc_raw_cost_rate, NULL), DECODE(pra.rate_based_flag,'Y', rac.txn_etc_burden_cost_rate, NULL), DECODE(pra.rate_based_flag,'Y', rac.txn_etc_bill_rate, NULL), decode(ppfo.PROJFUNC_COST_RATE_TYPE,null,null,(select pctv1.USER_CONVERSION_TYPE from pa_conversion_types_v pctv1 where pctv1.CONVERSION_TYPE=NVL(ppfo.PROJFUNC_COST_RATE_TYPE,'Corporate'))), decode(decode(ppfo.PROJFUNC_COST_RATE_TYPE,'User',null,ppfo.PROJFUNC_COST_RATE_DATE_TYPE),null,null, (select plk_d1.meaning from pa_lookups plk_d1 where plk_d1.lookup_type='PA_FP_RATE_DATE_TYPE' and plk_d1.lookup_code=NVL(decode(ppfo.PROJFUNC_COST_RATE_TYPE,'User',null,ppfo.PROJFUNC_COST_RATE_DATE_TYPE),'FIXED_DATE'))), decode(ppfo.PROJECT_COST_RATE_TYPE,null,null, (select pctv2.USER_CONVERSION_TYPE from pa_conversion_types_v pctv2 where pctv2.CONVERSION_TYPE=NVL(ppfo.PROJECT_COST_RATE_TYPE,'Corporate'))), decode(decode(ppfo.PROJECT_COST_RATE_TYPE,'User',null,ppfo.PROJECT_COST_RATE_DATE_TYPE),null,null, (select plk_d2.meaning from pa_lookups plk_d2 where plk_d2.lookup_type='PA_FP_RATE_DATE_TYPE' and plk_d2.lookup_code=NVL(decode(ppfo.PROJECT_COST_RATE_TYPE,'User',null,ppfo.PROJECT_COST_RATE_DATE_TYPE),'FIXED_DATE'))), decode(ppfo.PROJFUNC_REV_RATE_TYPE,null,null, (select pctv3.USER_CONVERSION_TYPE from pa_conversion_types_v pctv3 where pctv3.CONVERSION_TYPE=NVL(ppfo.PROJFUNC_REV_RATE_TYPE,'Corporate'))), decode(decode(ppfo.PROJFUNC_REV_RATE_TYPE,'User',null,ppfo.PROJFUNC_REV_RATE_DATE_TYPE),null,null, (select plk_d3.meaning from pa_lookups plk_d3 where plk_d3.lookup_type='PA_FP_RATE_DATE_TYPE' and plk_d3.lookup_code=NVL(decode(ppfo.PROJFUNC_REV_RATE_TYPE,'User',null,ppfo.PROJFUNC_REV_RATE_DATE_TYPE),'FIXED_DATE'))), decode(ppfo.PROJECT_REV_RATE_TYPE,null,null, (select pctv4.USER_CONVERSION_TYPE from pa_conversion_types_v pctv4 where pctv4.CONVERSION_TYPE=NVL(ppfo.PROJECT_REV_RATE_TYPE,'Corporate'))), decode(decode(ppfo.PROJECT_REV_RATE_TYPE,'User',null,ppfo.PROJECT_REV_RATE_DATE_TYPE),null,null, (select plk_d4.meaning from pa_lookups plk_d4 where plk_d4.lookup_type='PA_FP_RATE_DATE_TYPE' and plk_d4.lookup_code=NVL(decode(ppfo.PROJECT_REV_RATE_TYPE,'User',null,ppfo.PROJECT_REV_RATE_DATE_TYPE),'FIXED_DATE'))), decode(ppfo.PROJFUNC_COST_RATE_TYPE, 'User', pftc.PROJFUNC_COST_EXCHANGE_RATE,to_number(null)), decode(ppfo.PROJFUNC_COST_RATE_DATE_TYPE,'FIXED_DATE', ppfo.PROJFUNC_COST_RATE_DATE,to_date(null)), decode(ppfo.PROJECT_COST_RATE_TYPE, 'User', pftc.PROJECT_COST_EXCHANGE_RATE,to_number(null)), decode(ppfo.PROJECT_COST_RATE_DATE_TYPE,'FIXED_DATE', ppfo.PROJECT_COST_RATE_DATE,to_date(null)), decode(ppfo.PROJFUNC_REV_RATE_TYPE, 'User', pftc.PROJFUNC_REV_EXCHANGE_RATE,to_number(null)), decode(ppfo.PROJFUNC_REV_RATE_DATE_TYPE,'FIXED_DATE', ppfo.PROJFUNC_REV_RATE_DATE,to_date(null)), decode(ppfo.PROJECT_REV_RATE_TYPE, 'User', pftc.PROJECT_REV_EXCHANGE_RATE,to_number(null)), decode(ppfo.PROJECT_REV_RATE_DATE_TYPE,'FIXED_DATE', ppfo.PROJECT_REV_RATE_DATE,to_date(null)) , PA_PLANNING_ELEMENT_UTILS.get_task_percent_complete(p.project_id,pra.budget_version_id,pev.proj_element_id,'FINANCIAL_PLANNING') physical_percent_complete, decode(pra.transaction_source_code,null,null,(select meaning from PA_LOOKUPS where LOOKUP_TYPE='PA_FP_FCST_GEN_SRC_ALL' and LOOKUP_CODE= nvl(pra.transaction_source_code, ( select lookup_code from pa_lookups where lookup_type='PA_FP_FCST_GEN_SRC_ALL' and rownum=1)))) etc_source, PA_FIN_PLAN_UTILS.Get_Change_Reason(pra.resource_assignment_id,rac.txn_currency_code, nvl(nvl(ppfo.cost_time_phased_code,ppfo.revenue_time_phased_code),ppfo.all_time_phased_code)) change_reason From pa_projects_all p, pa_resource_asgn_curr rac, pa_fin_plan_types_b pt, pa_resource_assignments pra, pa_proj_elements pe, pa_proj_element_versions pev, pa_proj_elem_ver_structure pevs, pa_resource_lists prl, pa_budget_versions pbv, pa_fp_txn_currencies pftc, pa_proj_fp_options ppfo, pa_resource_list_members prlm, pa_lookups uom, pa_spread_curves_tl psc, cst_cost_types_v cct, pa_lookups etc WHERE p.project_id = pra.project_id and rac.resource_assignment_id = pra.resource_assignment_id and rac.budget_version_id = pra.budget_version_id and rac.budget_version_id = pbv.budget_version_id and rac.txn_currency_code = pftc.txn_currency_code and pra.task_id=pev.proj_element_id (+) and pev.parent_structure_version_id(+)=pa_planning_element_utils.get_fin_struct_id(pra.project_id,pra.budget_version_id) and pev.proj_element_id = pe.proj_element_id (+) and pev.parent_structure_version_id = pevs.element_version_id (+) and pev.project_id = pevs.project_id (+) and prlm.resource_list_member_id=pra.resource_list_member_id and ppfo.project_id = pra.project_id and pt.fin_plan_type_id = pbv.fin_plan_type_id and ppfo.fin_plan_type_id = pbv.fin_plan_type_id and ppfo.fin_plan_version_id = pra.budget_version_id and ppfo.FIN_PLAN_OPTION_LEVEL_CODE = 'PLAN_VERSION' and ppfo.proj_fp_options_id=pftc.proj_fp_options_id and pbv.budget_version_id = pra.budget_version_id and prl.resource_list_id = pbv.resource_list_id and uom.lookup_type = 'UNIT' and uom.LOOKUP_CODE = NVL(prlm.UNIT_OF_MEASURE,'HOURS') and nvl(pra.spread_curve_id,1)=psc.spread_curve_id and etc.lookup_type='PA_FP_ETC_METHOD' and etc.lookup_code(+)=pra.etc_method_code and cct.COST_TYPE_ID(+) = pra.mfc_cost_type_id and psc.language = USERENV('LANG')

View Text - HTML Formatted

SELECT PRA.BUDGET_VERSION_ID
, PPFO.FIN_PLAN_PREFERENCE_CODE
, PT.PLAN_CLASS_CODE
, P.PROJECT_ID
, P.NAME
, P.SEGMENT1
, PRA.RESOURCE_ASSIGNMENT_ID
, NVL(PE.NAME
, P.LONG_NAME) TASK_NAME
, NVL(PE.ELEMENT_NUMBER
, P.SEGMENT1) TASK_NUMBER
, NVL(PEV.DISPLAY_SEQUENCE
, -1) AS TASK_DISPLAY_SEQUENCE
, PRLM.ALIAS RESOURCE_NAME
, PRA.RESOURCE_LIST_MEMBER_ID
, PRA.PLANNING_START_DATE
, PRA.PLANNING_END_DATE
, NVL(RAC.TXN_CURRENCY_CODE
, DECODE(PBV.CI_ID
, NULL
, DECODE(PBV.APPROVED_REV_PLAN_TYPE_FLAG
, 'Y'
, P.PROJFUNC_CURRENCY_CODE
, P.PROJECT_CURRENCY_CODE)
, DECODE(PBV.VERSION_TYPE
, 'ALL'
, PA_FIN_PLAN_UTILS2.GET_AGREEMENT_CURRENCY(PRA.BUDGET_VERSION_ID)
, 'REVENUE'
, PA_FIN_PLAN_UTILS2.GET_AGREEMENT_CURRENCY(PRA.BUDGET_VERSION_ID)
, DECODE(PBV.APPROVED_REV_PLAN_TYPE_FLAG
, 'Y'
, P.PROJFUNC_CURRENCY_CODE
, P.PROJECT_CURRENCY_CODE)))) AS CURRENCY
, UOM.MEANING
, DECODE(NVL(NVL(PPFO.COST_TIME_PHASED_CODE
, PPFO.REVENUE_TIME_PHASED_CODE)
, PPFO.ALL_TIME_PHASED_CODE)
, 'N'
, PA_FIN_PLAN_UTILS.GET_NP_RA_DESCRIPTION(PRA.RESOURCE_ASSIGNMENT_ID
, RAC.TXN_CURRENCY_CODE)
, NULL) DESCRIPTION
, PRA.SPREAD_CURVE_ID SPREAD_CURVE_ID
, PSC.NAME SPREAD_CURVE
, PRA.MFC_COST_TYPE_ID MFC_COST_TYPE_ID
, CCT.COST_TYPE MFC_COST_TYPE
, PRA.ETC_METHOD_CODE ETC_METHOD_CODE
, ETC.MEANING ETC_METHOD
, NULL DELETE_FLAG
, RAC.TOTAL_DISPLAY_QUANTITY
, RAC.TOTAL_INIT_QUANTITY
, RAC.TOTAL_DISPLAY_QUANTITY-NVL(RAC.TOTAL_INIT_QUANTITY
, 0)
, PA_FP_WEBADI_UTILS.GET_CURRENT_AMOUNT(PPFO.FIN_PLAN_TYPE_ID
, PT.PLAN_CLASS_CODE
, P.PROJECT_ID
, PPFO.FIN_PLAN_PREFERENCE_CODE
, PRA.TASK_ID
, PRA.RESOURCE_LIST_MEMBER_ID
, NVL(PRA.UNIT_OF_MEASURE
, 'HOURS')
, RAC.TXN_CURRENCY_CODE
, 'QUANTITY') CURRENT_QUANTITY
, PA_FP_WEBADI_UTILS.GET_ORIGINAL_AMOUNT(PPFO.FIN_PLAN_TYPE_ID
, PT.PLAN_CLASS_CODE
, P.PROJECT_ID
, PPFO.FIN_PLAN_PREFERENCE_CODE
, PRA.TASK_ID
, PRA.RESOURCE_LIST_MEMBER_ID
, NVL(PRA.UNIT_OF_MEASURE
, 'HOURS')
, RAC.TXN_CURRENCY_CODE
, 'QUANTITY')ORIGINAL_QUANTITY
, PA_FP_WEBADI_UTILS.GET_PRIOR_FORECAST_AMOUNT(PPFO.FIN_PLAN_TYPE_ID
, PT.PLAN_CLASS_CODE
, P.PROJECT_ID
, PPFO.FIN_PLAN_PREFERENCE_CODE
, PRA.TASK_ID
, PRA.RESOURCE_LIST_MEMBER_ID
, NVL(PRA.UNIT_OF_MEASURE
, 'HOURS')
, RAC.TXN_CURRENCY_CODE
, 'QUANTITY') PRIOR_FORECAST_QUANTITY
, RAC.TOTAL_TXN_RAW_COST
, RAC.TOTAL_TXN_INIT_RAW_COST
, RAC.TOTAL_TXN_RAW_COST-NVL(RAC.TOTAL_TXN_INIT_RAW_COST
, 0)
, PA_FP_WEBADI_UTILS.GET_CURRENT_AMOUNT(PPFO.FIN_PLAN_TYPE_ID
, PT.PLAN_CLASS_CODE
, P.PROJECT_ID
, PPFO.FIN_PLAN_PREFERENCE_CODE
, PRA.TASK_ID
, PRA.RESOURCE_LIST_MEMBER_ID
, NVL(PRA.UNIT_OF_MEASURE
, 'HOURS')
, RAC.TXN_CURRENCY_CODE
, 'RAW_COST') CURRENT_RAW_COST
, PA_FP_WEBADI_UTILS.GET_ORIGINAL_AMOUNT(PPFO.FIN_PLAN_TYPE_ID
, PT.PLAN_CLASS_CODE
, P.PROJECT_ID
, PPFO.FIN_PLAN_PREFERENCE_CODE
, PRA.TASK_ID
, PRA.RESOURCE_LIST_MEMBER_ID
, NVL(PRA.UNIT_OF_MEASURE
, 'HOURS')
, RAC.TXN_CURRENCY_CODE
, 'RAW_COST') ORIGINAL_RAW_COST
, PA_FP_WEBADI_UTILS.GET_PRIOR_FORECAST_AMOUNT(PPFO.FIN_PLAN_TYPE_ID
, PT.PLAN_CLASS_CODE
, P.PROJECT_ID
, PPFO.FIN_PLAN_PREFERENCE_CODE
, PRA.TASK_ID
, PRA.RESOURCE_LIST_MEMBER_ID
, NVL(PRA.UNIT_OF_MEASURE
, 'HOURS')
, RAC.TXN_CURRENCY_CODE
, 'RAW_COST') PRIOR_FORECAST_RAW_COST
, RAC.TOTAL_TXN_BURDENED_COST
, RAC.TOTAL_TXN_INIT_BURDENED_COST
, RAC.TOTAL_TXN_BURDENED_COST-NVL(RAC.TOTAL_TXN_INIT_BURDENED_COST
, 0)
, PA_FP_WEBADI_UTILS.GET_CURRENT_AMOUNT(PPFO.FIN_PLAN_TYPE_ID
, PT.PLAN_CLASS_CODE
, P.PROJECT_ID
, PPFO.FIN_PLAN_PREFERENCE_CODE
, PRA.TASK_ID
, PRA.RESOURCE_LIST_MEMBER_ID
, NVL(PRA.UNIT_OF_MEASURE
, 'HOURS')
, RAC.TXN_CURRENCY_CODE
, 'BURDENED_COST') CURRENT_BURDENED_COST
, PA_FP_WEBADI_UTILS.GET_ORIGINAL_AMOUNT(PPFO.FIN_PLAN_TYPE_ID
, PT.PLAN_CLASS_CODE
, P.PROJECT_ID
, PPFO.FIN_PLAN_PREFERENCE_CODE
, PRA.TASK_ID
, PRA.RESOURCE_LIST_MEMBER_ID
, NVL(PRA.UNIT_OF_MEASURE
, 'HOURS')
, RAC.TXN_CURRENCY_CODE
, 'BURDENED_COST') ORIGINAL_BURDENED_COST
, PA_FP_WEBADI_UTILS.GET_PRIOR_FORECAST_AMOUNT(PPFO.FIN_PLAN_TYPE_ID
, PT.PLAN_CLASS_CODE
, P.PROJECT_ID
, PPFO.FIN_PLAN_PREFERENCE_CODE
, PRA.TASK_ID
, PRA.RESOURCE_LIST_MEMBER_ID
, NVL(PRA.UNIT_OF_MEASURE
, 'HOURS')
, RAC.TXN_CURRENCY_CODE
, 'BURDENED_COST') PRIOR_FORECAST_BURDENED_COST
, RAC.TOTAL_TXN_REVENUE
, RAC.TOTAL_TXN_INIT_REVENUE
, RAC.TOTAL_TXN_REVENUE-NVL(RAC.TOTAL_TXN_INIT_REVENUE
, 0)
, PA_FP_WEBADI_UTILS.GET_CURRENT_AMOUNT(PPFO.FIN_PLAN_TYPE_ID
, PT.PLAN_CLASS_CODE
, P.PROJECT_ID
, PPFO.FIN_PLAN_PREFERENCE_CODE
, PRA.TASK_ID
, PRA.RESOURCE_LIST_MEMBER_ID
, NVL(PRA.UNIT_OF_MEASURE
, 'HOURS')
, RAC.TXN_CURRENCY_CODE
, 'REVENUE') CURRENT_REVENUE
, PA_FP_WEBADI_UTILS.GET_ORIGINAL_AMOUNT(PPFO.FIN_PLAN_TYPE_ID
, PT.PLAN_CLASS_CODE
, P.PROJECT_ID
, PPFO.FIN_PLAN_PREFERENCE_CODE
, PRA.TASK_ID
, PRA.RESOURCE_LIST_MEMBER_ID
, NVL(PRA.UNIT_OF_MEASURE
, 'HOURS')
, RAC.TXN_CURRENCY_CODE
, 'REVENUE') ORIGINAL_REVENUE
, PA_FP_WEBADI_UTILS.GET_PRIOR_FORECAST_AMOUNT(PPFO.FIN_PLAN_TYPE_ID
, PT.PLAN_CLASS_CODE
, P.PROJECT_ID
, PPFO.FIN_PLAN_PREFERENCE_CODE
, PRA.TASK_ID
, PRA.RESOURCE_LIST_MEMBER_ID
, NVL(PRA.UNIT_OF_MEASURE
, 'HOURS')
, RAC.TXN_CURRENCY_CODE
, 'REVENUE') PRIOR_FORECAST_REVENUE
, DECODE(PRA.RATE_BASED_FLAG
, 'Y'
, RAC.TXN_RAW_COST_RATE_OVERRIDE
, NULL)
, DECODE(PRA.RATE_BASED_FLAG
, 'Y'
, RAC.TXN_BURDEN_COST_RATE_OVERRIDE
, NULL)
, DECODE(PRA.RATE_BASED_FLAG
, 'Y'
, RAC.TXN_BILL_RATE_OVERRIDE
, NULL)
, DECODE(PRA.RATE_BASED_FLAG
, 'Y'
, RAC.TXN_AVERAGE_RAW_COST_RATE
, NULL)
, DECODE(PRA.RATE_BASED_FLAG
, 'Y'
, RAC.TXN_AVERAGE_BURDEN_COST_RATE
, NULL)
, DECODE(PRA.RATE_BASED_FLAG
, 'Y'
, RAC.TXN_AVERAGE_BILL_RATE
, NULL)
, DECODE(PRA.RATE_BASED_FLAG
, 'Y'
, RAC.TXN_ETC_RAW_COST_RATE
, NULL)
, DECODE(PRA.RATE_BASED_FLAG
, 'Y'
, RAC.TXN_ETC_BURDEN_COST_RATE
, NULL)
, DECODE(PRA.RATE_BASED_FLAG
, 'Y'
, RAC.TXN_ETC_BILL_RATE
, NULL)
, DECODE(PPFO.PROJFUNC_COST_RATE_TYPE
, NULL
, NULL
, (SELECT PCTV1.USER_CONVERSION_TYPE
FROM PA_CONVERSION_TYPES_V PCTV1
WHERE PCTV1.CONVERSION_TYPE=NVL(PPFO.PROJFUNC_COST_RATE_TYPE
, 'CORPORATE')))
, DECODE(DECODE(PPFO.PROJFUNC_COST_RATE_TYPE
, 'USER'
, NULL
, PPFO.PROJFUNC_COST_RATE_DATE_TYPE)
, NULL
, NULL
, (SELECT PLK_D1.MEANING
FROM PA_LOOKUPS PLK_D1
WHERE PLK_D1.LOOKUP_TYPE='PA_FP_RATE_DATE_TYPE'
AND PLK_D1.LOOKUP_CODE=NVL(DECODE(PPFO.PROJFUNC_COST_RATE_TYPE
, 'USER'
, NULL
, PPFO.PROJFUNC_COST_RATE_DATE_TYPE)
, 'FIXED_DATE')))
, DECODE(PPFO.PROJECT_COST_RATE_TYPE
, NULL
, NULL
, (SELECT PCTV2.USER_CONVERSION_TYPE
FROM PA_CONVERSION_TYPES_V PCTV2
WHERE PCTV2.CONVERSION_TYPE=NVL(PPFO.PROJECT_COST_RATE_TYPE
, 'CORPORATE')))
, DECODE(DECODE(PPFO.PROJECT_COST_RATE_TYPE
, 'USER'
, NULL
, PPFO.PROJECT_COST_RATE_DATE_TYPE)
, NULL
, NULL
, (SELECT PLK_D2.MEANING
FROM PA_LOOKUPS PLK_D2
WHERE PLK_D2.LOOKUP_TYPE='PA_FP_RATE_DATE_TYPE'
AND PLK_D2.LOOKUP_CODE=NVL(DECODE(PPFO.PROJECT_COST_RATE_TYPE
, 'USER'
, NULL
, PPFO.PROJECT_COST_RATE_DATE_TYPE)
, 'FIXED_DATE')))
, DECODE(PPFO.PROJFUNC_REV_RATE_TYPE
, NULL
, NULL
, (SELECT PCTV3.USER_CONVERSION_TYPE
FROM PA_CONVERSION_TYPES_V PCTV3
WHERE PCTV3.CONVERSION_TYPE=NVL(PPFO.PROJFUNC_REV_RATE_TYPE
, 'CORPORATE')))
, DECODE(DECODE(PPFO.PROJFUNC_REV_RATE_TYPE
, 'USER'
, NULL
, PPFO.PROJFUNC_REV_RATE_DATE_TYPE)
, NULL
, NULL
, (SELECT PLK_D3.MEANING
FROM PA_LOOKUPS PLK_D3
WHERE PLK_D3.LOOKUP_TYPE='PA_FP_RATE_DATE_TYPE'
AND PLK_D3.LOOKUP_CODE=NVL(DECODE(PPFO.PROJFUNC_REV_RATE_TYPE
, 'USER'
, NULL
, PPFO.PROJFUNC_REV_RATE_DATE_TYPE)
, 'FIXED_DATE')))
, DECODE(PPFO.PROJECT_REV_RATE_TYPE
, NULL
, NULL
, (SELECT PCTV4.USER_CONVERSION_TYPE
FROM PA_CONVERSION_TYPES_V PCTV4
WHERE PCTV4.CONVERSION_TYPE=NVL(PPFO.PROJECT_REV_RATE_TYPE
, 'CORPORATE')))
, DECODE(DECODE(PPFO.PROJECT_REV_RATE_TYPE
, 'USER'
, NULL
, PPFO.PROJECT_REV_RATE_DATE_TYPE)
, NULL
, NULL
, (SELECT PLK_D4.MEANING
FROM PA_LOOKUPS PLK_D4
WHERE PLK_D4.LOOKUP_TYPE='PA_FP_RATE_DATE_TYPE'
AND PLK_D4.LOOKUP_CODE=NVL(DECODE(PPFO.PROJECT_REV_RATE_TYPE
, 'USER'
, NULL
, PPFO.PROJECT_REV_RATE_DATE_TYPE)
, 'FIXED_DATE')))
, DECODE(PPFO.PROJFUNC_COST_RATE_TYPE
, 'USER'
, PFTC.PROJFUNC_COST_EXCHANGE_RATE
, TO_NUMBER(NULL))
, DECODE(PPFO.PROJFUNC_COST_RATE_DATE_TYPE
, 'FIXED_DATE'
, PPFO.PROJFUNC_COST_RATE_DATE
, TO_DATE(NULL))
, DECODE(PPFO.PROJECT_COST_RATE_TYPE
, 'USER'
, PFTC.PROJECT_COST_EXCHANGE_RATE
, TO_NUMBER(NULL))
, DECODE(PPFO.PROJECT_COST_RATE_DATE_TYPE
, 'FIXED_DATE'
, PPFO.PROJECT_COST_RATE_DATE
, TO_DATE(NULL))
, DECODE(PPFO.PROJFUNC_REV_RATE_TYPE
, 'USER'
, PFTC.PROJFUNC_REV_EXCHANGE_RATE
, TO_NUMBER(NULL))
, DECODE(PPFO.PROJFUNC_REV_RATE_DATE_TYPE
, 'FIXED_DATE'
, PPFO.PROJFUNC_REV_RATE_DATE
, TO_DATE(NULL))
, DECODE(PPFO.PROJECT_REV_RATE_TYPE
, 'USER'
, PFTC.PROJECT_REV_EXCHANGE_RATE
, TO_NUMBER(NULL))
, DECODE(PPFO.PROJECT_REV_RATE_DATE_TYPE
, 'FIXED_DATE'
, PPFO.PROJECT_REV_RATE_DATE
, TO_DATE(NULL))
, PA_PLANNING_ELEMENT_UTILS.GET_TASK_PERCENT_COMPLETE(P.PROJECT_ID
, PRA.BUDGET_VERSION_ID
, PEV.PROJ_ELEMENT_ID
, 'FINANCIAL_PLANNING') PHYSICAL_PERCENT_COMPLETE
, DECODE(PRA.TRANSACTION_SOURCE_CODE
, NULL
, NULL
, (SELECT MEANING
FROM PA_LOOKUPS
WHERE LOOKUP_TYPE='PA_FP_FCST_GEN_SRC_ALL'
AND LOOKUP_CODE= NVL(PRA.TRANSACTION_SOURCE_CODE
, ( SELECT LOOKUP_CODE
FROM PA_LOOKUPS
WHERE LOOKUP_TYPE='PA_FP_FCST_GEN_SRC_ALL'
AND ROWNUM=1)))) ETC_SOURCE
, PA_FIN_PLAN_UTILS.GET_CHANGE_REASON(PRA.RESOURCE_ASSIGNMENT_ID
, RAC.TXN_CURRENCY_CODE
, NVL(NVL(PPFO.COST_TIME_PHASED_CODE
, PPFO.REVENUE_TIME_PHASED_CODE)
, PPFO.ALL_TIME_PHASED_CODE)) CHANGE_REASON
FROM PA_PROJECTS_ALL P
, PA_RESOURCE_ASGN_CURR RAC
, PA_FIN_PLAN_TYPES_B PT
, PA_RESOURCE_ASSIGNMENTS PRA
, PA_PROJ_ELEMENTS PE
, PA_PROJ_ELEMENT_VERSIONS PEV
, PA_PROJ_ELEM_VER_STRUCTURE PEVS
, PA_RESOURCE_LISTS PRL
, PA_BUDGET_VERSIONS PBV
, PA_FP_TXN_CURRENCIES PFTC
, PA_PROJ_FP_OPTIONS PPFO
, PA_RESOURCE_LIST_MEMBERS PRLM
, PA_LOOKUPS UOM
, PA_SPREAD_CURVES_TL PSC
, CST_COST_TYPES_V CCT
, PA_LOOKUPS ETC
WHERE P.PROJECT_ID = PRA.PROJECT_ID
AND RAC.RESOURCE_ASSIGNMENT_ID = PRA.RESOURCE_ASSIGNMENT_ID
AND RAC.BUDGET_VERSION_ID = PRA.BUDGET_VERSION_ID
AND RAC.BUDGET_VERSION_ID = PBV.BUDGET_VERSION_ID
AND RAC.TXN_CURRENCY_CODE = PFTC.TXN_CURRENCY_CODE
AND PRA.TASK_ID=PEV.PROJ_ELEMENT_ID (+)
AND PEV.PARENT_STRUCTURE_VERSION_ID(+)=PA_PLANNING_ELEMENT_UTILS.GET_FIN_STRUCT_ID(PRA.PROJECT_ID
, PRA.BUDGET_VERSION_ID)
AND PEV.PROJ_ELEMENT_ID = PE.PROJ_ELEMENT_ID (+)
AND PEV.PARENT_STRUCTURE_VERSION_ID = PEVS.ELEMENT_VERSION_ID (+)
AND PEV.PROJECT_ID = PEVS.PROJECT_ID (+)
AND PRLM.RESOURCE_LIST_MEMBER_ID=PRA.RESOURCE_LIST_MEMBER_ID
AND PPFO.PROJECT_ID = PRA.PROJECT_ID
AND PT.FIN_PLAN_TYPE_ID = PBV.FIN_PLAN_TYPE_ID
AND PPFO.FIN_PLAN_TYPE_ID = PBV.FIN_PLAN_TYPE_ID
AND PPFO.FIN_PLAN_VERSION_ID = PRA.BUDGET_VERSION_ID
AND PPFO.FIN_PLAN_OPTION_LEVEL_CODE = 'PLAN_VERSION'
AND PPFO.PROJ_FP_OPTIONS_ID=PFTC.PROJ_FP_OPTIONS_ID
AND PBV.BUDGET_VERSION_ID = PRA.BUDGET_VERSION_ID
AND PRL.RESOURCE_LIST_ID = PBV.RESOURCE_LIST_ID
AND UOM.LOOKUP_TYPE = 'UNIT'
AND UOM.LOOKUP_CODE = NVL(PRLM.UNIT_OF_MEASURE
, 'HOURS')
AND NVL(PRA.SPREAD_CURVE_ID
, 1)=PSC.SPREAD_CURVE_ID
AND ETC.LOOKUP_TYPE='PA_FP_ETC_METHOD'
AND ETC.LOOKUP_CODE(+)=PRA.ETC_METHOD_CODE
AND CCT.COST_TYPE_ID(+) = PRA.MFC_COST_TYPE_ID
AND PSC.LANGUAGE = USERENV('LANG')