[Home] [Help]
32: --resource assignment already exists.Bug 3678314
33: TYPE res_assmt_map_rec IS RECORD
34: (task_id pa_tasks.task_id%TYPE
35: ,resource_list_member_id pa_resource_list_members.resource_list_member_id%TYPE
36: ,resource_assignment_id pa_resource_assignments.resource_assignment_id%TYPE
37: ,ra_dml_code VARCHAR2(30));
38:
39: TYPE res_assmt_map_rec_tbl_type IS TABLE OF res_assmt_map_rec
40: INDEX BY BINARY_INTEGER;
2646:
2647: -- Start of functions to be used only in implement_ci_into_single_ver API
2648:
2649: FUNCTION get_task_id(p_planning_level IN pa_proj_fp_options.cost_fin_plan_level_code%TYPE,
2650: p_task_id IN pa_resource_assignments.task_id%TYPE)
2651: RETURN NUMBER IS
2652: l_temp NUMBER;
2653: BEGIN
2654: IF P_PA_DEBUG_MODE='Y' THEN
2681: END IF;
2682: return l_src_targ_task_tbl(l_temp).value;
2683: END;
2684:
2685: FUNCTION get_mapped_ra_id(p_task_id IN pa_resource_assignments.task_id%TYPE,
2686: p_rlm_id IN pa_resource_assignments.resource_list_member_id%TYPE,
2687: p_resource_assignment_id IN pa_resource_assignments.resource_assignment_id%TYPE,
2688: p_fin_plan_level_code IN pa_proj_fp_options.cost_fin_plan_level_code%TYPE
2689: )
2682: return l_src_targ_task_tbl(l_temp).value;
2683: END;
2684:
2685: FUNCTION get_mapped_ra_id(p_task_id IN pa_resource_assignments.task_id%TYPE,
2686: p_rlm_id IN pa_resource_assignments.resource_list_member_id%TYPE,
2687: p_resource_assignment_id IN pa_resource_assignments.resource_assignment_id%TYPE,
2688: p_fin_plan_level_code IN pa_proj_fp_options.cost_fin_plan_level_code%TYPE
2689: )
2690: RETURN NUMBER IS
2683: END;
2684:
2685: FUNCTION get_mapped_ra_id(p_task_id IN pa_resource_assignments.task_id%TYPE,
2686: p_rlm_id IN pa_resource_assignments.resource_list_member_id%TYPE,
2687: p_resource_assignment_id IN pa_resource_assignments.resource_assignment_id%TYPE,
2688: p_fin_plan_level_code IN pa_proj_fp_options.cost_fin_plan_level_code%TYPE
2689: )
2690: RETURN NUMBER IS
2691: l_index NUMBER;
2701: SELECT get_task_id(p_fin_plan_level_code,task_id),
2702: resource_list_member_id
2703: INTO l_task_id,
2704: l_rlm_id
2705: FROM pa_resource_assignments
2706: WHERE resource_assignment_id=p_resource_assignment_id;
2707: END IF;
2708:
2709: l_index := 1;
2725:
2726: END IF;
2727: END;
2728:
2729: FUNCTION get_mapped_dml_code(p_task_id IN pa_resource_assignments.task_id%TYPE,
2730: p_rlm_id IN pa_resource_assignments.resource_list_member_id%TYPE,
2731: p_resource_assignment_id IN pa_resource_assignments.resource_assignment_id%TYPE,
2732: p_fin_plan_level_code IN pa_proj_fp_options.cost_fin_plan_level_code%TYPE
2733:
2726: END IF;
2727: END;
2728:
2729: FUNCTION get_mapped_dml_code(p_task_id IN pa_resource_assignments.task_id%TYPE,
2730: p_rlm_id IN pa_resource_assignments.resource_list_member_id%TYPE,
2731: p_resource_assignment_id IN pa_resource_assignments.resource_assignment_id%TYPE,
2732: p_fin_plan_level_code IN pa_proj_fp_options.cost_fin_plan_level_code%TYPE
2733:
2734: )
2727: END;
2728:
2729: FUNCTION get_mapped_dml_code(p_task_id IN pa_resource_assignments.task_id%TYPE,
2730: p_rlm_id IN pa_resource_assignments.resource_list_member_id%TYPE,
2731: p_resource_assignment_id IN pa_resource_assignments.resource_assignment_id%TYPE,
2732: p_fin_plan_level_code IN pa_proj_fp_options.cost_fin_plan_level_code%TYPE
2733:
2734: )
2735: RETURN VARCHAR2 IS
2747: SELECT get_task_id(p_fin_plan_level_code,task_id),
2748: resource_list_member_id
2749: INTO l_task_id,
2750: l_rlm_id
2751: FROM pa_resource_assignments
2752: WHERE resource_assignment_id=p_resource_assignment_id;
2753: END IF;
2754:
2755: l_index := 1;
2889: l_rbs_element_id_prm_tbl_1 system.PA_NUM_TBL_TYPE:= system.PA_NUM_TBL_TYPE();
2890: -- for End bug 5291484
2891:
2892:
2893: -- Start of tables used to bulk collect and insert into pa_resource_assignments
2894: L_TARG_RLM_ID_TBL SYSTEM.PA_NUM_TBL_TYPE:=SYSTEM.PA_NUM_TBL_TYPE();
2895: L_TARG_RA_ID_TBL SYSTEM.PA_NUM_TBL_TYPE:=SYSTEM.PA_NUM_TBL_TYPE();
2896: L_TARG_CBS_ELEM_ID_TBL SYSTEM.PA_NUM_TBL_TYPE:=SYSTEM.PA_NUM_TBL_TYPE(); --bug#16200605
2897: L_RA_DML_CODE_TBL SYSTEM.PA_VARCHAR2_15_TBL_TYPE:= SYSTEM.PA_VARCHAR2_15_TBL_TYPE();
2935: l_targ_INCR_BY_RES_CLS_COD_tbl SYSTEM.PA_VARCHAR2_30_TBL_TYPE:= SYSTEM.PA_VARCHAR2_30_TBL_TYPE();
2936: l_targ_INCUR_BY_ROLE_ID_tbl SYSTEM.PA_NUM_TBL_TYPE:=SYSTEM.PA_NUM_TBL_TYPE();
2937: l_targ_sp_fixed_date_tbl SYSTEM.PA_DATE_TBL_TYPE := SYSTEM.PA_DATE_TBL_TYPE(); -- Bug 8350296
2938:
2939: -- End of tables used to bulk collect and insert into pa_resource_assignments
2940:
2941: --These pl/sql tbls will store the data corresponding to the details such as task_id, rate based flag
2942: --etc of RAs that have got updated. Bug 3678314.
2943: l_upd_ra_task_id_tbl SYSTEM.PA_NUM_TBL_TYPE:=SYSTEM.PA_NUM_TBL_TYPE();
3082: -- End of variable to be used in Calculate API Call
3083:
3084:
3085:
3086: l_proj_raw_cost pa_resource_assignments.TOTAL_PROJECT_RAW_COST%TYPE;
3087: l_proj_burdened_cost pa_resource_assignments.TOTAL_PROJECT_BURDENED_COST%TYPE;
3088: l_proj_revenue pa_resource_assignments.TOTAL_PROJECT_REVENUE%TYPE;
3089:
3090: l_projfunc_raw_cost pa_resource_assignments.TOTAL_PLAN_RAW_COST%TYPE;
3083:
3084:
3085:
3086: l_proj_raw_cost pa_resource_assignments.TOTAL_PROJECT_RAW_COST%TYPE;
3087: l_proj_burdened_cost pa_resource_assignments.TOTAL_PROJECT_BURDENED_COST%TYPE;
3088: l_proj_revenue pa_resource_assignments.TOTAL_PROJECT_REVENUE%TYPE;
3089:
3090: l_projfunc_raw_cost pa_resource_assignments.TOTAL_PLAN_RAW_COST%TYPE;
3091: l_projfunc_burdened_cost pa_resource_assignments.TOTAL_PLAN_BURDENED_COST%TYPE;
3084:
3085:
3086: l_proj_raw_cost pa_resource_assignments.TOTAL_PROJECT_RAW_COST%TYPE;
3087: l_proj_burdened_cost pa_resource_assignments.TOTAL_PROJECT_BURDENED_COST%TYPE;
3088: l_proj_revenue pa_resource_assignments.TOTAL_PROJECT_REVENUE%TYPE;
3089:
3090: l_projfunc_raw_cost pa_resource_assignments.TOTAL_PLAN_RAW_COST%TYPE;
3091: l_projfunc_burdened_cost pa_resource_assignments.TOTAL_PLAN_BURDENED_COST%TYPE;
3092: l_projfunc_revenue pa_resource_assignments.TOTAL_PLAN_REVENUE%TYPE;
3086: l_proj_raw_cost pa_resource_assignments.TOTAL_PROJECT_RAW_COST%TYPE;
3087: l_proj_burdened_cost pa_resource_assignments.TOTAL_PROJECT_BURDENED_COST%TYPE;
3088: l_proj_revenue pa_resource_assignments.TOTAL_PROJECT_REVENUE%TYPE;
3089:
3090: l_projfunc_raw_cost pa_resource_assignments.TOTAL_PLAN_RAW_COST%TYPE;
3091: l_projfunc_burdened_cost pa_resource_assignments.TOTAL_PLAN_BURDENED_COST%TYPE;
3092: l_projfunc_revenue pa_resource_assignments.TOTAL_PLAN_REVENUE%TYPE;
3093: l_labor_quantity pa_resource_assignments.TOTAL_PLAN_QUANTITY%TYPE;
3094: l_equip_quantity pa_resource_assignments.TOTAL_PLAN_QUANTITY%TYPE;
3087: l_proj_burdened_cost pa_resource_assignments.TOTAL_PROJECT_BURDENED_COST%TYPE;
3088: l_proj_revenue pa_resource_assignments.TOTAL_PROJECT_REVENUE%TYPE;
3089:
3090: l_projfunc_raw_cost pa_resource_assignments.TOTAL_PLAN_RAW_COST%TYPE;
3091: l_projfunc_burdened_cost pa_resource_assignments.TOTAL_PLAN_BURDENED_COST%TYPE;
3092: l_projfunc_revenue pa_resource_assignments.TOTAL_PLAN_REVENUE%TYPE;
3093: l_labor_quantity pa_resource_assignments.TOTAL_PLAN_QUANTITY%TYPE;
3094: l_equip_quantity pa_resource_assignments.TOTAL_PLAN_QUANTITY%TYPE;
3095:
3088: l_proj_revenue pa_resource_assignments.TOTAL_PROJECT_REVENUE%TYPE;
3089:
3090: l_projfunc_raw_cost pa_resource_assignments.TOTAL_PLAN_RAW_COST%TYPE;
3091: l_projfunc_burdened_cost pa_resource_assignments.TOTAL_PLAN_BURDENED_COST%TYPE;
3092: l_projfunc_revenue pa_resource_assignments.TOTAL_PLAN_REVENUE%TYPE;
3093: l_labor_quantity pa_resource_assignments.TOTAL_PLAN_QUANTITY%TYPE;
3094: l_equip_quantity pa_resource_assignments.TOTAL_PLAN_QUANTITY%TYPE;
3095:
3096:
3089:
3090: l_projfunc_raw_cost pa_resource_assignments.TOTAL_PLAN_RAW_COST%TYPE;
3091: l_projfunc_burdened_cost pa_resource_assignments.TOTAL_PLAN_BURDENED_COST%TYPE;
3092: l_projfunc_revenue pa_resource_assignments.TOTAL_PLAN_REVENUE%TYPE;
3093: l_labor_quantity pa_resource_assignments.TOTAL_PLAN_QUANTITY%TYPE;
3094: l_equip_quantity pa_resource_assignments.TOTAL_PLAN_QUANTITY%TYPE;
3095:
3096:
3097:
3090: l_projfunc_raw_cost pa_resource_assignments.TOTAL_PLAN_RAW_COST%TYPE;
3091: l_projfunc_burdened_cost pa_resource_assignments.TOTAL_PLAN_BURDENED_COST%TYPE;
3092: l_projfunc_revenue pa_resource_assignments.TOTAL_PLAN_REVENUE%TYPE;
3093: l_labor_quantity pa_resource_assignments.TOTAL_PLAN_QUANTITY%TYPE;
3094: l_equip_quantity pa_resource_assignments.TOTAL_PLAN_QUANTITY%TYPE;
3095:
3096:
3097:
3098:
3262:
3263: -- Start of variables declared for bug 4035856
3264: l_src_delta_amt_adj_task_id pa_tasks.task_id%TYPE;
3265: l_targ_delta_amt_adj_rlm_id pa_resource_list_members.resource_list_member_id%TYPE;
3266: l_src_delta_amt_adj_ra_id pa_resource_assignments.resource_assignment_id%TYPE;
3267: l_src_delta_amt_adj_start_date pa_budget_lines.start_date%TYPE;
3268: l_pc_revenue_delta pa_budget_lines.project_revenue%TYPE;
3269: l_pfc_revenue_delta pa_budget_lines.revenue%TYPE;
3270: l_pc_rev_merged pa_budget_lines.project_revenue%TYPE;
3278: l_impl_quantity pa_fp_merged_ctrl_items.impl_quantity%TYPE;-- bug 4035856
3279: l_agreement_id pa_agreements_all.agreement_id%TYPE;
3280: l_agreement_currency_code pa_agreements_all.agreement_currency_code%TYPE;
3281: l_rounded_bl_id pa_budget_lines.budget_line_id%TYPE;
3282: l_rounded_bl_rbf pa_resource_assignments.rate_based_flag%TYPE;
3283: l_qty_adjusted_flag VARCHAR2(1);
3284: --These variables will be used in calling create_ci_impact_fund_lines
3285: l_impl_pc_rev_amt NUMBER;
3286: l_impl_pfc_rev_amt NUMBER;
3296: CURSOR src_delta_amt_adj_ra_cur
3297: (c_budget_version_id pa_budget_versions.budget_version_id%TYPE)IS
3298: SELECT pra.task_id, pra.resource_list_member_id, pra.resource_assignment_id,
3299: PA_PROJ_ELEMENTS_UTILS.GET_DISPLAY_SEQUENCE(pra.task_id) as dispSeq, rlm.alias
3300: FROM pa_resource_assignments pra, pa_resource_list_members rlm
3301: WHERE pra.budget_version_id = c_budget_version_id
3302: AND rlm.resource_list_member_id = pra.resource_list_member_id
3303: AND EXISTS (SELECT 1
3304: FROM pa_budget_lines pbl
3311: CURSOR src_delta_amt_adj_ra_cur1
3312: (c_budget_version_id pa_budget_versions.budget_version_id%TYPE)IS
3313: SELECT pra.task_id, tmp4.resource_list_member_id, pra.resource_assignment_id,
3314: PA_PROJ_ELEMENTS_UTILS.GET_DISPLAY_SEQUENCE(pra.task_id) as dispSeq, rlm.alias
3315: FROM pa_resource_assignments pra, pa_resource_list_members rlm,pa_res_list_map_tmp4 tmp4
3316: WHERE pra.budget_version_id = c_budget_version_id
3317: AND pra.resource_assignment_id=tmp4.txn_source_id
3318: AND rlm.resource_list_member_id = tmp4.resource_list_member_id
3319: AND EXISTS (SELECT 1
3323:
3324: -- Cursor to identify against which period/date the adjustment amount should be placed
3325: -- Note that revenue only change orders can have amounts against agreement currency only.
3326: CURSOR src_delta_amt_adj_date_cur
3327: (c_resource_assignment_id pa_resource_assignments.resource_assignment_id%TYPE)IS
3328: SELECT max(start_date)
3329: FROM pa_budget_lines
3330: WHERE resource_assignment_id = c_resource_assignment_id;
3331:
4192: l_targ_unit_of_measure_tbl,
4193: l_targ_rbs_element_id_tbl,
4194: l_targ_txn_accum_header_id_tbl
4195:
4196: FROM Pa_resource_assignments prat,
4197: (SELECT
4198: rlmap.txn_spread_curve_id,
4199: rlmap.txn_etc_method_code,
4200: rlmap.resource_type_code,
4229: pra.unit_of_measure as uom,
4230: pra.sp_fixed_date AS sp_fixed_date, -- Bug 8350296
4231: pra.cbs_element_id AS cbs_element_id --bug#16200605
4232: FROM
4233: pa_resource_assignments pra,
4234: pa_res_list_map_tmp4 rlmap
4235: WHERE
4236: pra.resource_assignment_id=rlmap.txn_source_id)rlmap
4237: WHERE prat.budget_version_id(+)=p_budget_version_id
4383: l_targ_INCUR_BY_ROLE_ID_tbl,
4384: l_targ_RATE_BASED_FLAG_tbl,
4385: l_targ_RES_RATE_BASED_FLAG_tbl, --IPM Arch Enhancement Bug 4865563
4386: l_targ_unit_of_measure_tbl
4387: FROM pa_resource_assignments pras,
4388: Pa_resource_assignments prat
4389: WHERE pras.budget_version_id=l_src_ver_id_tbl(j)
4390: AND prat.budget_version_id(+)=p_budget_version_id
4391: AND prat.resource_list_member_id(+)=pras.resource_list_member_id
4384: l_targ_RATE_BASED_FLAG_tbl,
4385: l_targ_RES_RATE_BASED_FLAG_tbl, --IPM Arch Enhancement Bug 4865563
4386: l_targ_unit_of_measure_tbl
4387: FROM pa_resource_assignments pras,
4388: Pa_resource_assignments prat
4389: WHERE pras.budget_version_id=l_src_ver_id_tbl(j)
4390: AND prat.budget_version_id(+)=p_budget_version_id
4391: AND prat.resource_list_member_id(+)=pras.resource_list_member_id
4392: AND prat.task_id(+)=get_task_id(l_targ_plan_level_code,pras.task_id)
4427: so that it will be easy later to identify the resource assignment id corresponding to the given task id
4428: and resource list member id*/
4429: FOR ind IN L_targ_ra_id_tbl.FIRST..L_targ_ra_id_tbl.LAST LOOP
4430: IF L_targ_ra_id_tbl(ind) IS NULL THEN
4431: SELECT pa_resource_assignments_s.nextval
4432: INTO L_targ_ra_id_tbl(ind)
4433: FROM dual;
4434: END IF;
4435: l_res_assmt_map_rec_tbl(ind).task_id:=l_targ_task_id_tbl(ind);
5318: -- 1) If p_calling_context is null, insert transaction source code as null
5319: -- 2) If p_calling_context is generation insert transaction source code as 'CHANGE DOCUMENTS'
5320:
5321: FORALL kk in L_targ_ra_id_tbl.FIRST ..L_targ_ra_id_tbl.LAST
5322: INSERT INTO PA_RESOURCE_ASSIGNMENTS (
5323: RESOURCE_ASSIGNMENT_ID,BUDGET_VERSION_ID,PROJECT_ID,TASK_ID,RESOURCE_LIST_MEMBER_ID,CBS_ELEMENT_ID,LAST_UPDATE_DATE,LAST_UPDATED_BY,CREATION_DATE,CREATED_BY
5324: ,LAST_UPDATE_LOGIN,UNIT_OF_MEASURE,TRACK_AS_LABOR_FLAG,STANDARD_BILL_RATE,AVERAGE_BILL_RATE,AVERAGE_COST_RATE
5325: ,PROJECT_ASSIGNMENT_ID,PLAN_ERROR_CODE,TOTAL_PLAN_REVENUE,TOTAL_PLAN_RAW_COST,TOTAL_PLAN_BURDENED_COST,TOTAL_PLAN_QUANTITY
5326: ,AVERAGE_DISCOUNT_PERCENTAGE,TOTAL_BORROWED_REVENUE,TOTAL_TP_REVENUE_IN,TOTAL_TP_REVENUE_OUT,TOTAL_REVENUE_ADJ
5488: -- Howeever, in oracle 8i select can not be used inside a decode. So, two sqls are used for better performance
5489: -- Bug 4171006: Updating UOM and rate_based_flag as well with the values already derived.
5490: IF p_calling_context IN ('BUDGET_GENERATION','FORECAST_GENERATION') THEN
5491: FORALL kk IN L_targ_ra_id_tbl.FIRST ..L_targ_ra_id_tbl.LAST
5492: UPDATE pa_resource_assignments pra
5493: SET PLANNING_START_DATE = l_planning_start_date_tbl(kk),
5494: PLANNING_END_DATE = l_planning_end_date_tbl(kk),
5495: UNIT_OF_MEASURE = l_targ_unit_of_measure_tbl(kk), -- bug 4171006
5496: TRANSACTION_SOURCE_CODE =
5519: l_upd_ra_rbf_tbl,
5520: l_upd_ra_res_asmt_id_tbl;
5521: ELSE
5522: FORALL kk IN L_targ_ra_id_tbl.FIRST ..L_targ_ra_id_tbl.LAST
5523: UPDATE pa_resource_assignments pra
5524: SET PLANNING_START_DATE = l_planning_start_date_tbl(kk),
5525: PLANNING_END_DATE = l_planning_end_date_tbl(kk),
5526: UNIT_OF_MEASURE = l_targ_unit_of_measure_tbl(kk), -- bug 4171006
5527: TRANSACTION_SOURCE_CODE = null,
5640: sum(pbl.revenue) revenue,
5641: DECODE(l_copy_pfc_for_txn_amt_flag,'Y',l_projfunc_currency_code,
5642: DECODE(l_same_multi_curr_flag,'Y', pbl.txn_currency_code,l_project_currency_code)) txn_currency_code
5643: FROM pa_budget_lines pbl,
5644: pa_resource_assignments pra
5645: WHERE pbl.resource_assignment_id = pra.resource_assignment_id
5646: AND pra.budget_version_id=l_src_ver_id_tbl(j)
5647: GROUP BY pra.resource_assignment_id, pra.task_id, pra.resource_list_member_id,
5648: DECODE(l_copy_pfc_for_txn_amt_flag,'Y',l_projfunc_currency_code,
5732: sum(pbl.burdened_cost) burdened_cost,
5733: sum(pbl.revenue) revenue,
5734: DECODE(l_copy_pfc_for_txn_amt_flag,'Y',l_projfunc_currency_code,
5735: DECODE(l_same_multi_curr_flag,'Y', pbl.txn_currency_code,l_project_currency_code)) txn_currency_code
5736: FROM pa_resource_assignments pra
5737: ,pa_res_list_map_tmp4 tmp
5738: ,pa_budget_lines pbl
5739: WHERE pra.resource_assignment_id=tmp.txn_source_id
5740: AND pra.budget_version_id=l_src_ver_id_tbl(j)
6317:
6318: decode(count(pbls.budget_line_id),1,max(pbls.PC_CUR_CONV_REJECTION_CODE),null) pc_cur_conv_rejection_code,
6319: decode(count(pbls.budget_line_id),1,max(pbls.PFC_CUR_CONV_REJECTION_CODE),null) pfc_cur_conv_rejection_code
6320: from pa_budget_lines pbls,
6321: pa_resource_assignments pras
6322: where l_ra_dml_code_tbl(kk)='INSERT'
6323: and pras.resource_assignment_id = pbls.resource_assignment_id
6324: and pras.budget_version_id = l_src_ver_id_tbl(j)
6325: and PA_FP_CI_MERGE.get_mapped_ra_id(PA_FP_CI_MERGE.get_task_id(l_targ_plan_level_code,pras.task_id), pras.resource_list_member_id)= L_targ_ra_id_tbl(kk)
6720:
6721: decode(count(pbls.budget_line_id),1,max(pbls.PC_CUR_CONV_REJECTION_CODE),null) pc_cur_conv_rejection_code,
6722: decode(count(pbls.budget_line_id),1,max(pbls.PFC_CUR_CONV_REJECTION_CODE),null) pfc_cur_conv_rejection_code
6723: from pa_budget_lines pbls,
6724: pa_resource_assignments pras,
6725: pa_res_list_map_tmp4 rlmap
6726: where l_ra_dml_code_tbl(kk)='INSERT'
6727: and pras.resource_assignment_id = pbls.resource_assignment_id
6728: and pras.budget_version_id = l_src_ver_id_tbl(j)
7127:
7128: decode(count(pbls.budget_line_id),1,max(pbls.PC_CUR_CONV_REJECTION_CODE),null) pc_cur_conv_rejection_code,
7129: decode(count(pbls.budget_line_id),1,max(pbls.PFC_CUR_CONV_REJECTION_CODE),null) pfc_cur_conv_rejection_code
7130: from pa_budget_lines pbls,
7131: pa_resource_assignments pras
7132: where l_ra_dml_code_tbl(kk)='INSERT'
7133: and pras.budget_version_id = l_src_ver_id_tbl(j)
7134: and pras.resource_assignment_id = pbls.resource_assignment_id
7135: and get_mapped_ra_id(get_task_id(l_targ_plan_level_code,pras.task_id), pras.resource_list_member_id)=L_targ_ra_id_tbl(kk)
7533: from pa_budget_lines pbls,
7534: (SELECT pra.task_id task_id,
7535: tmp4.resource_list_member_id resource_list_member_id,
7536: tmp4.txn_source_id resource_assignment_id
7537: FROM pa_resource_assignments pra,
7538: pa_res_list_map_tmp4 tmp4
7539: WHERE tmp4.txn_source_id=pra.resource_assignment_id) rlmap
7540: where l_ra_dml_code_tbl(kk)='INSERT'
7541: and rlmap.resource_assignment_id = pbls.resource_assignment_id
7787: l_bl_PC_CUR_CONV_REJ_CODE_tbl,
7788: l_bl_PFC_CUR_CONV_REJ_CODE_tbl
7789: from pa_budget_lines pbls,
7790: pa_budget_lines pblt,
7791: pa_resource_assignments prat
7792: where get_mapped_dml_code(null,null,pbls.resource_assignment_id,l_targ_plan_level_code)='UPDATE'
7793: and pbls.budget_version_id = l_src_ver_id_tbl(j)
7794: and pblt.budget_version_id(+) = p_budget_version_id
7795: and pblt.resource_assignment_id(+)=get_mapped_ra_id(null,null,pbls.resource_assignment_id,l_targ_plan_level_code)
8036: l_bl_BURDEN_COST_RAT_OVRID_tbl,
8037: l_bl_PC_CUR_CONV_REJ_CODE_tbl,
8038: l_bl_PFC_CUR_CONV_REJ_CODE_tbl
8039: from pa_budget_lines pblt,
8040: pa_resource_assignments prat,
8041: (SELECT pbls.resource_assignment_id
8042: ,pbls.start_date
8043: ,pbls.last_update_date
8044: ,pbls.last_updated_by
8141: ,pbls.pfc_cur_conv_rejection_code
8142: ,pras.resource_assignment_id
8143: ,pras.task_id
8144: ,tmp4.resource_list_member_id
8145: FROM pa_resource_assignments pras,
8146: pa_res_list_map_tmp4 tmp4,
8147: pa_budget_lines pbls
8148: WHERE tmp4.txn_source_id=pras.resource_assignment_id
8149: AND pbls.resource_assignment_id=pras.resource_assignment_id) pbls
8740: ,pbls.pfc_cur_conv_rejection_code
8741: ,pras.resource_assignment_id
8742: ,pras.task_id
8743: ,tmp4.resource_list_member_id
8744: FROM pa_resource_assignments pras,
8745: pa_res_list_map_tmp4 tmp4,
8746: pa_budget_lines pbls
8747: WHERE tmp4.txn_source_id=pras.resource_assignment_id
8748: AND pbls.resource_assignment_id=pras.resource_assignment_id) pbls
9398: IF l_qty_adjusted_flag = 'N' THEN
9399:
9400: SELECT pra.rate_based_flag
9401: INTO l_rounded_bl_rbf
9402: FROM pa_resource_assignments pra,
9403: pa_budget_lines pbl
9404: WHERE pra.resource_assignment_id = pbl.resource_assignment_id
9405: AND pbl.budget_line_id = l_rounded_bl_id;
9406:
9557: l_pji_prm_rbs_elem_id_tbl,
9558: l_pji_prm_cbs_elem_id_tbl, --bug#16911079
9559: l_pji_prm_res_cls_code_tbl,
9560: l_pji_prm_rbf_tbl
9561: FROM pa_resource_assignments pra,
9562: pa_budget_lines pbl
9563: WHERE pra.resource_assignment_id = pbl.resource_assignment_id
9564: AND (pbl.budget_line_id BETWEEN l_id_before_bl_insertion AND l_id_after_bl_insertion)
9565: AND pra.budget_Version_id=p_budget_version_id;
13518:
13519: update pa_ci_supplier_details a
13520: set resource_assignment_id =
13521: (select prc.resource_assignment_id
13522: from pa_resource_asgn_curr prc, pa_resource_assignments pra
13523: where prc.resource_assignment_id = pra.resource_assignment_id
13524: and pra.budget_version_id = l_budget_version_id
13525: and pra.task_id = a.task_id
13526: and pra.resource_list_member_id = a.resource_list_member_id
13590: (select prc.resource_assignment_id,
13591: nvl(a.from_change_date, pra.planning_start_date),
13592: nvl(a.to_change_date, pra.planning_end_date),
13593: a.raw_cost * prc.txn_average_burden_cost_rate
13594: from pa_resource_asgn_curr prc, pa_resource_assignments pra
13595: where prc.resource_assignment_id = pra.resource_assignment_id
13596: and pra.budget_version_id = l_budget_version_id
13597: and pra.task_id = a.task_id
13598: and pra.resource_list_member_id = a.resource_list_member_id
13991: pcdc.quantity * nvl(pcdc.planning_resource_rate, prac.txn_average_raw_cost_rate)),
13992: decode(pcdc.quantity, null,
13993: pcdc.raw_cost * nvl(pcdc.burden_cost_rate, prac.txn_average_burden_cost_rate),
13994: pcdc.quantity * nvl(pcdc.burden_cost_rate, prac.txn_average_burden_cost_rate))
13995: from pa_resource_assignments pra, pa_resource_asgn_curr prac
13996: where pra.budget_version_id = p_bv_id
13997: and pra.task_id = pcdc.task_id
13998: and pra.resource_list_member_id = pcdc.resource_list_member_id
13999: and prac.txn_currency_code = pcdc.currency_code
14014: set (resource_assignment_id, effective_from, effective_to) =
14015: (select prac.resource_assignment_id,
14016: nvl(pcdc.effective_from, pra.planning_start_date),
14017: nvl(pcdc.effective_to, pra.planning_end_date)
14018: from pa_resource_assignments pra, pa_resource_asgn_curr prac
14019: where pra.budget_version_id = p_bv_id
14020: and pra.task_id = pcdc.task_id
14021: and pra.resource_list_member_id = pcdc.resource_list_member_id
14022: and prac.txn_currency_code = pcdc.currency_code