[Home] [Help]
3471: l_partially_impl_flag VARCHAR2(1);
3472: l_agreement_num pa_agreements_all.agreement_num%TYPE;
3473: l_approved_fin_pt_id pa_fin_plan_types_b.fin_plan_type_id%TYPE;
3474: l_call_rep_lines_api VARCHAR2(1):='N';
3475: l_id_before_bl_insertion pa_budget_lines.budget_line_id%TYPE;
3476: l_id_after_bl_insertion pa_budget_lines.budget_line_id%TYPE;
3477: l_dummy NUMBER;
3478:
3479:
3472: l_agreement_num pa_agreements_all.agreement_num%TYPE;
3473: l_approved_fin_pt_id pa_fin_plan_types_b.fin_plan_type_id%TYPE;
3474: l_call_rep_lines_api VARCHAR2(1):='N';
3475: l_id_before_bl_insertion pa_budget_lines.budget_line_id%TYPE;
3476: l_id_after_bl_insertion pa_budget_lines.budget_line_id%TYPE;
3477: l_dummy NUMBER;
3478:
3479:
3480:
3493: l_record_version_number pa_budget_versions.record_version_number%TYPE;
3494: l_partial_factor NUMBER := 1;
3495: l_impl_amt pa_fp_merged_ctrl_items.impl_proj_revenue%TYPE;
3496: l_total_amt pa_budget_versions.total_project_revenue%TYPE;
3497: l_total_amt_in_pfc pa_budget_lines.revenue%TYPE;
3498: l_total_amt_in_pc pa_budget_lines.project_revenue%TYPE;
3499:
3500: l_src_proj_fp_options_id pa_proj_fp_options.proj_fp_options_id%TYPE;
3501: l_src_multi_curr_flag pa_proj_fp_options.plan_in_multi_curr_flag%TYPE;
3494: l_partial_factor NUMBER := 1;
3495: l_impl_amt pa_fp_merged_ctrl_items.impl_proj_revenue%TYPE;
3496: l_total_amt pa_budget_versions.total_project_revenue%TYPE;
3497: l_total_amt_in_pfc pa_budget_lines.revenue%TYPE;
3498: l_total_amt_in_pc pa_budget_lines.project_revenue%TYPE;
3499:
3500: l_src_proj_fp_options_id pa_proj_fp_options.proj_fp_options_id%TYPE;
3501: l_src_multi_curr_flag pa_proj_fp_options.plan_in_multi_curr_flag%TYPE;
3502: l_src_fin_plan_type_id pa_proj_fp_options.fin_plan_type_id%TYPE;
3735:
3736:
3737:
3738:
3739: -- Start of tables prepared to insert/update into pa_budget_lines
3740: l_bl_RESOURCE_ASIGNMENT_ID_tbl SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE();
3741: l_upd_ra_bl_dml_code_tbl SYSTEM.PA_VARCHAR2_15_TBL_TYPE := SYSTEM.PA_VARCHAR2_15_TBL_TYPE();
3742: l_bl_START_DATE_tbl SYSTEM.PA_DATE_TBL_TYPE := SYSTEM.PA_DATE_TBL_TYPE();
3743: l_bl_END_DATE_tbl SYSTEM.PA_DATE_TBL_TYPE := SYSTEM.PA_DATE_TBL_TYPE();
3826: l_bl_PC_CUR_CONV_REJ_CODE_tbl SYSTEM.PA_VARCHAR2_30_TBL_TYPE:= SYSTEM.PA_VARCHAR2_30_TBL_TYPE();
3827: l_bl_PFC_CUR_CONV_REJ_CODE_tbl SYSTEM.PA_VARCHAR2_30_TBL_TYPE:= SYSTEM.PA_VARCHAR2_30_TBL_TYPE();
3828: l_bl_rbf_flag_tbl SYSTEM.PA_VARCHAR2_1_TBL_TYPE:= SYSTEM.PA_VARCHAR2_1_TBL_TYPE();
3829:
3830: -- End of tables prepared to insert/update into pa_budget_lines
3831:
3832: l_ci_cost_version_id pa_budget_versions.budget_version_id%TYPE;
3833: l_ci_rev_version_id pa_budget_versions.budget_version_id%TYPE;
3834: l_ci_all_version_id pa_budget_versions.budget_version_id%TYPE;
3885: l_targ_pc_rawc_after_merge pa_budget_versions.total_project_raw_cost%TYPE;
3886: l_targ_pc_burdc_after_merge pa_budget_versions.total_project_burdened_cost%TYPE;
3887: l_targ_pc_rev_after_merge pa_budget_versions.total_project_revenue%TYPE;
3888: l_impl_earlier VARCHAR2(1);
3889: l_total_agr_revenue pa_budget_lines.txn_revenue%TYPE;
3890:
3891: l_partial_impl_rev_amt NUMBER;
3892: l_partial_rev_impl_flag VARCHAR2(1);
3893:
3903: -- Start of variables declared for bug 4035856
3904: l_src_delta_amt_adj_task_id pa_tasks.task_id%TYPE;
3905: l_targ_delta_amt_adj_rlm_id pa_resource_list_members.resource_list_member_id%TYPE;
3906: l_src_delta_amt_adj_ra_id pa_resource_assignments.resource_assignment_id%TYPE;
3907: l_src_delta_amt_adj_start_date pa_budget_lines.start_date%TYPE;
3908: l_pc_revenue_delta pa_budget_lines.project_revenue%TYPE;
3909: l_pfc_revenue_delta pa_budget_lines.revenue%TYPE;
3910: l_pc_rev_merged pa_budget_lines.project_revenue%TYPE;
3911: l_pfc_rev_merged pa_budget_lines.revenue%TYPE;
3904: l_src_delta_amt_adj_task_id pa_tasks.task_id%TYPE;
3905: l_targ_delta_amt_adj_rlm_id pa_resource_list_members.resource_list_member_id%TYPE;
3906: l_src_delta_amt_adj_ra_id pa_resource_assignments.resource_assignment_id%TYPE;
3907: l_src_delta_amt_adj_start_date pa_budget_lines.start_date%TYPE;
3908: l_pc_revenue_delta pa_budget_lines.project_revenue%TYPE;
3909: l_pfc_revenue_delta pa_budget_lines.revenue%TYPE;
3910: l_pc_rev_merged pa_budget_lines.project_revenue%TYPE;
3911: l_pfc_rev_merged pa_budget_lines.revenue%TYPE;
3912: l_pc_rev_for_merge pa_budget_lines.project_revenue%TYPE;
3905: l_targ_delta_amt_adj_rlm_id pa_resource_list_members.resource_list_member_id%TYPE;
3906: l_src_delta_amt_adj_ra_id pa_resource_assignments.resource_assignment_id%TYPE;
3907: l_src_delta_amt_adj_start_date pa_budget_lines.start_date%TYPE;
3908: l_pc_revenue_delta pa_budget_lines.project_revenue%TYPE;
3909: l_pfc_revenue_delta pa_budget_lines.revenue%TYPE;
3910: l_pc_rev_merged pa_budget_lines.project_revenue%TYPE;
3911: l_pfc_rev_merged pa_budget_lines.revenue%TYPE;
3912: l_pc_rev_for_merge pa_budget_lines.project_revenue%TYPE;
3913: l_pfc_rev_for_merge pa_budget_lines.revenue%TYPE;
3906: l_src_delta_amt_adj_ra_id pa_resource_assignments.resource_assignment_id%TYPE;
3907: l_src_delta_amt_adj_start_date pa_budget_lines.start_date%TYPE;
3908: l_pc_revenue_delta pa_budget_lines.project_revenue%TYPE;
3909: l_pfc_revenue_delta pa_budget_lines.revenue%TYPE;
3910: l_pc_rev_merged pa_budget_lines.project_revenue%TYPE;
3911: l_pfc_rev_merged pa_budget_lines.revenue%TYPE;
3912: l_pc_rev_for_merge pa_budget_lines.project_revenue%TYPE;
3913: l_pfc_rev_for_merge pa_budget_lines.revenue%TYPE;
3914: l_src_dummy1 number;
3907: l_src_delta_amt_adj_start_date pa_budget_lines.start_date%TYPE;
3908: l_pc_revenue_delta pa_budget_lines.project_revenue%TYPE;
3909: l_pfc_revenue_delta pa_budget_lines.revenue%TYPE;
3910: l_pc_rev_merged pa_budget_lines.project_revenue%TYPE;
3911: l_pfc_rev_merged pa_budget_lines.revenue%TYPE;
3912: l_pc_rev_for_merge pa_budget_lines.project_revenue%TYPE;
3913: l_pfc_rev_for_merge pa_budget_lines.revenue%TYPE;
3914: l_src_dummy1 number;
3915: l_src_dummy2 pa_resource_list_members.alias%TYPE;
3908: l_pc_revenue_delta pa_budget_lines.project_revenue%TYPE;
3909: l_pfc_revenue_delta pa_budget_lines.revenue%TYPE;
3910: l_pc_rev_merged pa_budget_lines.project_revenue%TYPE;
3911: l_pfc_rev_merged pa_budget_lines.revenue%TYPE;
3912: l_pc_rev_for_merge pa_budget_lines.project_revenue%TYPE;
3913: l_pfc_rev_for_merge pa_budget_lines.revenue%TYPE;
3914: l_src_dummy1 number;
3915: l_src_dummy2 pa_resource_list_members.alias%TYPE;
3916: l_impl_proj_func_revenue pa_fp_merged_ctrl_items.impl_proj_func_revenue%TYPE;-- bug 4035856
3909: l_pfc_revenue_delta pa_budget_lines.revenue%TYPE;
3910: l_pc_rev_merged pa_budget_lines.project_revenue%TYPE;
3911: l_pfc_rev_merged pa_budget_lines.revenue%TYPE;
3912: l_pc_rev_for_merge pa_budget_lines.project_revenue%TYPE;
3913: l_pfc_rev_for_merge pa_budget_lines.revenue%TYPE;
3914: l_src_dummy1 number;
3915: l_src_dummy2 pa_resource_list_members.alias%TYPE;
3916: l_impl_proj_func_revenue pa_fp_merged_ctrl_items.impl_proj_func_revenue%TYPE;-- bug 4035856
3917: l_impl_proj_revenue pa_fp_merged_ctrl_items.impl_proj_revenue%TYPE;-- bug 4035856
3917: l_impl_proj_revenue pa_fp_merged_ctrl_items.impl_proj_revenue%TYPE;-- bug 4035856
3918: l_impl_quantity pa_fp_merged_ctrl_items.impl_quantity%TYPE;-- bug 4035856
3919: l_agreement_id pa_agreements_all.agreement_id%TYPE;
3920: l_agreement_currency_code pa_agreements_all.agreement_currency_code%TYPE;
3921: l_rounded_bl_id pa_budget_lines.budget_line_id%TYPE;
3922: l_rounded_bl_rbf pa_resource_assignments.rate_based_flag%TYPE;
3923: l_qty_adjusted_flag VARCHAR2(1);
3924: --These variables will be used in calling create_ci_impact_fund_lines
3925: l_impl_pc_rev_amt NUMBER;
3940: FROM pa_resource_assignments pra, pa_resource_list_members rlm
3941: WHERE pra.budget_version_id = c_budget_version_id
3942: AND rlm.resource_list_member_id = pra.resource_list_member_id
3943: AND EXISTS (SELECT 1
3944: FROM pa_budget_lines pbl
3945: WHERE pbl.resource_assignment_id = pra.resource_assignment_id)
3946: ORDER BY dispSeq DESC , rlm.alias DESC;
3947:
3948: --This cursor is same as src_delta_amt_adj_ra_cur. This will be used when the source and target
3956: WHERE pra.budget_version_id = c_budget_version_id
3957: AND pra.resource_assignment_id=tmp4.txn_source_id
3958: AND rlm.resource_list_member_id = tmp4.resource_list_member_id
3959: AND EXISTS (SELECT 1
3960: FROM pa_budget_lines pbl
3961: WHERE pbl.resource_assignment_id = pra.resource_assignment_id)
3962: ORDER BY dispSeq DESC , rlm.alias DESC;
3963:
3964: -- Cursor to identify against which period/date the adjustment amount should be placed
3965: -- Note that revenue only change orders can have amounts against agreement currency only.
3966: CURSOR src_delta_amt_adj_date_cur
3967: (c_resource_assignment_id pa_resource_assignments.resource_assignment_id%TYPE)IS
3968: SELECT max(start_date)
3969: FROM pa_budget_lines
3970: WHERE resource_assignment_id = c_resource_assignment_id;
3971:
3972:
3973: -- End of variables declared for bug 4035856
4442: nvl(sum(pbl.project_revenue),0) total_amt_in_pc
4443: INTO L_total_amt,
4444: l_total_amt_in_pfc,
4445: l_total_amt_in_pc
4446: FROM Pa_budget_lines pbl
4447: WHERE pbl.budget_Version_id= l_src_ver_id_tbl(j);
4448: --IPM Arch Enhancement Bug 4865563
4449: /*
4450: and pbl.cost_rejection_code IS NULL
4504: END IF;
4505:
4506: SELECT NVL(sum(txn_revenue),0)
4507: INTO l_partial_impl_rev_amt
4508: FROM pa_budget_lines
4509: WHERE budget_version_id=l_src_ver_id_tbl(j);
4510:
4511: IF P_PA_debug_mode = 'Y' THEN
4512: pa_debug.g_err_stage:='l_partial_impl_rev_amt derived is '||l_partial_impl_rev_amt;
6087: -- 2) If p_calling_context is generation
6088: -- a) if retain manually edited lines is Y do not update ras with transaction source code as null
6089: -- b) for ras that can be updated do not override transaction source code if there are already amounts
6090: -- if no amounts stamp transaction source code as 'CHANGE DOCUMENTS'
6091: -- Please note that the select against pa_budget_lines is unnecessary when p_calling_context is null
6092: -- Howeever, in oracle 8i select can not be used inside a decode. So, two sqls are used for better performance
6093: -- Bug 4171006: Updating UOM and rate_based_flag as well with the values already derived.
6094: IF p_calling_context IN ('BUDGET_GENERATION','FORECAST_GENERATION') THEN
6095: FORALL kk IN L_targ_ra_id_tbl.FIRST ..L_targ_ra_id_tbl.LAST
6098: PLANNING_END_DATE = l_planning_end_date_tbl(kk),
6099: UNIT_OF_MEASURE = l_targ_unit_of_measure_tbl(kk), -- bug 4171006
6100: TRANSACTION_SOURCE_CODE =
6101: (SELECT DECODE(COUNT(*),0,'CHANGE_DOCUMENTS',TRANSACTION_SOURCE_CODE)
6102: FROM pa_budget_lines pbl
6103: WHERE pbl.resource_assignment_id = pra.resource_assignment_id),
6104: RATE_BASED_FLAG = l_targ_RATE_BASED_FLAG_tbl(kk), -- bug 4171006
6105: LAST_UPDATE_DATE = sysdate,
6106: LAST_UPDATED_BY = fnd_global.user_id,
6239: sum(pbl.burdened_cost) burdened_cost,
6240: sum(pbl.revenue) revenue,
6241: DECODE(l_copy_pfc_for_txn_amt_flag,'Y',l_projfunc_currency_code,
6242: DECODE(l_same_multi_curr_flag,'Y', pbl.txn_currency_code,l_project_currency_code)) txn_currency_code
6243: FROM pa_budget_lines pbl,
6244: pa_resource_assignments pra
6245: WHERE pbl.resource_assignment_id = pra.resource_assignment_id
6246: AND pra.budget_version_id=l_src_ver_id_tbl(j)
6247: GROUP BY pra.resource_assignment_id, pra.task_id, pra.resource_list_member_id,
6258: sum(pbl.raw_cost) raw_cost,
6259: sum(pbl.burdened_cost) burdened_cost,
6260: sum(pbl.revenue) revenue,
6261: pbl.txn_currency_code
6262: FROM pa_budget_lines pbl
6263: WHERE pbl.budget_Version_id = p_budget_version_id
6264: GROUP BY pbl.resource_assignment_id, pbl.txn_currency_code)pblt
6265: where get_mapped_ra_id(get_task_id(l_targ_plan_level_code,pbls.task_id),pbls.resource_list_member_id)=pblt.resource_assignment_id(+)
6266: and pblt.txn_Currency_code(+)= pbls.txn_currency_code;
6334: DECODE(l_copy_pfc_for_txn_amt_flag,'Y',l_projfunc_currency_code,
6335: DECODE(l_same_multi_curr_flag,'Y', pbl.txn_currency_code,l_project_currency_code)) txn_currency_code
6336: FROM pa_resource_assignments pra
6337: ,pa_res_list_map_tmp4 tmp
6338: ,pa_budget_lines pbl
6339: WHERE pra.resource_assignment_id=tmp.txn_source_id
6340: AND pra.budget_version_id=l_src_ver_id_tbl(j)
6341: AND pbl.resource_assignment_id=pra.resource_assignment_id
6342: GROUP BY pra.resource_assignment_id, pra.task_id, tmp.resource_list_member_id,
6353: sum(pbl.raw_cost) raw_cost,
6354: sum(pbl.burdened_cost) burdened_cost,
6355: sum(pbl.revenue) revenue,
6356: pbl.txn_currency_code txn_currency_code
6357: FROM pa_budget_lines pbl
6358: WHERE pbl.budget_Version_id = p_budget_version_id
6359: GROUP BY pbl.resource_assignment_id, pbl.txn_currency_code)pblt
6360: where get_mapped_ra_id(get_task_id(l_targ_plan_level_code,pbls.task_id),pbls.resource_list_member_id)=pblt.resource_assignment_id(+)
6361: and pblt.txn_currency_code(+)= pbls.txn_currency_code;
6520:
6521: END IF;--IF l_src_time_phased_code = 'N' AND (l_targ_time_phased_code = 'P' OR l_targ_time_phased_code = 'G') THEN
6522:
6523: --Get the budget line sequence before inserting data into budget lines. After inserting the budget lines
6524: --the sequence is again compared to see the no. of budget lines that have got inserted. Since pa_budget_lines_s.currval
6525: --is used at a later part of code this SELECT should not be removed
6526: SELECT pa_budget_lines_s.nextval
6527: INTO l_id_before_bl_insertion
6528: FROM DUAL;
6522:
6523: --Get the budget line sequence before inserting data into budget lines. After inserting the budget lines
6524: --the sequence is again compared to see the no. of budget lines that have got inserted. Since pa_budget_lines_s.currval
6525: --is used at a later part of code this SELECT should not be removed
6526: SELECT pa_budget_lines_s.nextval
6527: INTO l_id_before_bl_insertion
6528: FROM DUAL;
6529:
6530: --dbms_output.put_line('I25');
6538: END IF;
6539:
6540: --dbms_output.put_line('I26');
6541: FORALL kk in L_targ_ra_id_tbl.FIRST ..L_targ_ra_id_tbl.LAST
6542: INSERT INTO PA_BUDGET_LINES(
6543: RESOURCE_ASSIGNMENT_ID,
6544: START_DATE,
6545: LAST_UPDATE_DATE,
6546: LAST_UPDATED_BY,
6741: pbl.txn_raw_cost,
6742: pbl.txn_burdened_cost,
6743: pbl.txn_revenue,
6744: pbl.bucketing_period_code,
6745: pa_budget_lines_s.nextval,
6746: pbl.budget_version_id,
6747: pbl.txn_standard_cost_rate,
6748: DECODE(l_target_version_type,
6749: 'REVENUE',pbl.txn_cost_rate_override,
6916:
6917:
6918: decode(count(pbls.budget_line_id),1,max(pbls.PC_CUR_CONV_REJECTION_CODE),null) pc_cur_conv_rejection_code,
6919: decode(count(pbls.budget_line_id),1,max(pbls.PFC_CUR_CONV_REJECTION_CODE),null) pfc_cur_conv_rejection_code
6920: from pa_budget_lines pbls,
6921: pa_resource_assignments pras
6922: where l_ra_dml_code_tbl(kk)='INSERT'
6923: and pras.resource_assignment_id = pbls.resource_assignment_id
6924: and pras.budget_version_id = l_src_ver_id_tbl(j)
6947: END IF;
6948:
6949: --dbms_output.put_line('I28');
6950: FORALL kk in L_targ_ra_id_tbl.FIRST ..L_targ_ra_id_tbl.LAST
6951: INSERT INTO PA_BUDGET_LINES(RESOURCE_ASSIGNMENT_ID,
6952: START_DATE,
6953: LAST_UPDATE_DATE,
6954: LAST_UPDATED_BY,
6955: CREATION_DATE,
7147: pbl.txn_raw_cost,
7148: pbl.txn_burdened_cost,
7149: pbl.txn_revenue,
7150: pbl.bucketing_period_code,
7151: pa_budget_lines_s.nextval,
7152: pbl.budget_version_id,
7153: pbl.txn_standard_cost_rate,
7154: DECODE(l_target_version_type,
7155: 'REVENUE',pbl.txn_cost_rate_override,
7319:
7320:
7321: decode(count(pbls.budget_line_id),1,max(pbls.PC_CUR_CONV_REJECTION_CODE),null) pc_cur_conv_rejection_code,
7322: decode(count(pbls.budget_line_id),1,max(pbls.PFC_CUR_CONV_REJECTION_CODE),null) pfc_cur_conv_rejection_code
7323: from pa_budget_lines pbls,
7324: pa_resource_assignments pras,
7325: pa_res_list_map_tmp4 rlmap
7326: where l_ra_dml_code_tbl(kk)='INSERT'
7327: and pras.resource_assignment_id = pbls.resource_assignment_id
7353:
7354: --dbms_output.put_line('I30');
7355: IF l_src_resource_list_id = l_targ_resource_list_id THEN
7356: FORALL kk in L_targ_ra_id_tbl.FIRST ..L_targ_ra_id_tbl.LAST
7357: INSERT INTO PA_BUDGET_LINES(
7358: RESOURCE_ASSIGNMENT_ID,
7359: START_DATE,
7360: LAST_UPDATE_DATE,
7361: LAST_UPDATED_BY,
7554: pbl.txn_raw_cost,
7555: pbl.txn_burdened_cost,
7556: pbl.txn_revenue,
7557: pbl.bucketing_period_code,
7558: pa_budget_lines_s.nextval,
7559: pbl.budget_version_id,
7560: pbl.txn_standard_cost_rate,
7561: DECODE(l_target_version_type,
7562: 'REVENUE',pbl.txn_cost_rate_override,
7726:
7727:
7728: decode(count(pbls.budget_line_id),1,max(pbls.PC_CUR_CONV_REJECTION_CODE),null) pc_cur_conv_rejection_code,
7729: decode(count(pbls.budget_line_id),1,max(pbls.PFC_CUR_CONV_REJECTION_CODE),null) pfc_cur_conv_rejection_code
7730: from pa_budget_lines pbls,
7731: pa_resource_assignments pras
7732: where l_ra_dml_code_tbl(kk)='INSERT'
7733: and pras.budget_version_id = l_src_ver_id_tbl(j)
7734: and pras.resource_assignment_id = pbls.resource_assignment_id
7759: END IF;
7760:
7761: --dbms_output.put_line('I32');
7762: FORALL kk in L_targ_ra_id_tbl.FIRST ..L_targ_ra_id_tbl.LAST
7763: INSERT INTO PA_BUDGET_LINES(
7764: RESOURCE_ASSIGNMENT_ID,
7765: START_DATE,
7766: LAST_UPDATE_DATE,
7767: LAST_UPDATED_BY,
7960: pbl.txn_raw_cost,
7961: pbl.txn_burdened_cost,
7962: pbl.txn_revenue,
7963: pbl.bucketing_period_code,
7964: pa_budget_lines_s.nextval,
7965: pbl.budget_version_id,
7966: pbl.txn_standard_cost_rate,
7967: DECODE(l_target_version_type,
7968: 'REVENUE',pbl.txn_cost_rate_override,
8129: --Bug 4224757. Code changes for bug#4224757 ends here
8130:
8131: decode(count(pbls.budget_line_id),1,max(pbls.PC_CUR_CONV_REJECTION_CODE),null) pc_cur_conv_rejection_code,
8132: decode(count(pbls.budget_line_id),1,max(pbls.PFC_CUR_CONV_REJECTION_CODE),null) pfc_cur_conv_rejection_code
8133: from pa_budget_lines pbls,
8134: (SELECT pra.task_id task_id,
8135: tmp4.resource_list_member_id resource_list_member_id,
8136: tmp4.txn_source_id resource_assignment_id
8137: FROM pa_resource_assignments pra,
8385: l_bl_BURDEN_COST_RATE_tbl,
8386: l_bl_BURDEN_COST_RAT_OVRID_tbl,
8387: l_bl_PC_CUR_CONV_REJ_CODE_tbl,
8388: l_bl_PFC_CUR_CONV_REJ_CODE_tbl
8389: from pa_budget_lines pbls,
8390: pa_budget_lines pblt,
8391: pa_resource_assignments prat
8392: where get_mapped_dml_code(null,null,pbls.resource_assignment_id,l_targ_plan_level_code)='UPDATE'
8393: and pbls.budget_version_id = l_src_ver_id_tbl(j)
8386: l_bl_BURDEN_COST_RAT_OVRID_tbl,
8387: l_bl_PC_CUR_CONV_REJ_CODE_tbl,
8388: l_bl_PFC_CUR_CONV_REJ_CODE_tbl
8389: from pa_budget_lines pbls,
8390: pa_budget_lines pblt,
8391: pa_resource_assignments prat
8392: where get_mapped_dml_code(null,null,pbls.resource_assignment_id,l_targ_plan_level_code)='UPDATE'
8393: and pbls.budget_version_id = l_src_ver_id_tbl(j)
8394: and pblt.budget_version_id(+) = p_budget_version_id
8635: l_bl_BURDEN_COST_RATE_tbl,
8636: l_bl_BURDEN_COST_RAT_OVRID_tbl,
8637: l_bl_PC_CUR_CONV_REJ_CODE_tbl,
8638: l_bl_PFC_CUR_CONV_REJ_CODE_tbl
8639: from pa_budget_lines pblt,
8640: pa_resource_assignments prat,
8641: (SELECT pbls.resource_assignment_id
8642: ,pbls.start_date
8643: ,pbls.last_update_date
8743: ,pras.task_id
8744: ,tmp4.resource_list_member_id
8745: FROM pa_resource_assignments pras,
8746: pa_res_list_map_tmp4 tmp4,
8747: pa_budget_lines pbls
8748: WHERE tmp4.txn_source_id=pras.resource_assignment_id
8749: AND pbls.resource_assignment_id=pras.resource_assignment_id) pbls
8750: where get_mapped_dml_code(get_task_id(l_targ_plan_level_code,pbls.task_id),pbls.resource_list_member_id)='UPDATE'
8751: and pblt.budget_version_id(+) = p_budget_version_id
8990: l_bl_BURDEN_COST_RATE_tbl,
8991: l_bl_BURDEN_COST_RAT_OVRID_tbl,
8992: l_bl_PC_CUR_CONV_REJ_CODE_tbl,
8993: l_bl_PFC_CUR_CONV_REJ_CODE_tbl
8994: from pa_budget_lines pbls,
8995: pa_budget_lines pblt
8996: where get_mapped_dml_code(null,null,pbls.resource_assignment_id,l_targ_plan_level_code)='UPDATE'
8997: and pbls.budget_version_id = l_src_ver_id_tbl(j)
8998: and pblt.budget_version_id(+) = p_budget_version_id
8991: l_bl_BURDEN_COST_RAT_OVRID_tbl,
8992: l_bl_PC_CUR_CONV_REJ_CODE_tbl,
8993: l_bl_PFC_CUR_CONV_REJ_CODE_tbl
8994: from pa_budget_lines pbls,
8995: pa_budget_lines pblt
8996: where get_mapped_dml_code(null,null,pbls.resource_assignment_id,l_targ_plan_level_code)='UPDATE'
8997: and pbls.budget_version_id = l_src_ver_id_tbl(j)
8998: and pblt.budget_version_id(+) = p_budget_version_id
8999: and pblt.resource_assignment_id(+)=get_mapped_ra_id(null,null,pbls.resource_assignment_id,l_targ_plan_level_code)
9235: l_bl_BURDEN_COST_RATE_tbl,
9236: l_bl_BURDEN_COST_RAT_OVRID_tbl,
9237: l_bl_PC_CUR_CONV_REJ_CODE_tbl,
9238: l_bl_PFC_CUR_CONV_REJ_CODE_tbl
9239: from pa_budget_lines pblt,
9240: (SELECT pbls.resource_assignment_id
9241: ,pbls.start_date
9242: ,pbls.last_update_date
9243: ,pbls.last_updated_by
9342: ,pras.task_id
9343: ,tmp4.resource_list_member_id
9344: FROM pa_resource_assignments pras,
9345: pa_res_list_map_tmp4 tmp4,
9346: pa_budget_lines pbls
9347: WHERE tmp4.txn_source_id=pras.resource_assignment_id
9348: AND pbls.resource_assignment_id=pras.resource_assignment_id) pbls
9349: where get_mapped_dml_code(get_task_id(l_targ_plan_level_code,pbls.task_id),pbls.resource_list_member_id)='UPDATE'
9350: and pblt.budget_version_id(+) = p_budget_version_id
9448: END IF; --IF l_partial_factor <> 1
9449:
9450: --dbms_output.put_line('I43');
9451: FORALL kk in 1..l_upd_ra_bl_dml_code_tbl.count
9452: INSERT INTO PA_BUDGET_LINES(RESOURCE_ASSIGNMENT_ID,
9453: START_DATE,
9454: LAST_UPDATE_DATE,
9455: LAST_UPDATED_BY,
9456: CREATION_DATE,
9650: l_bl_TXN_RAW_COST_tbl(kk),
9651: l_bl_TXN_BURDENED_COST_tbl(kk),
9652: l_bl_TXN_REVENUE_tbl(kk),
9653: l_bl_BUCKETING_PERIOD_CODE_tbl(kk),
9654: pa_budget_lines_s.nextval,
9655: p_budget_version_id,
9656: l_bl_TXN_STD_COST_RATE_tbl(kk),
9657: DECODE(l_target_version_type,
9658: 'REVENUE',l_bl_TXN_COST_RATE_OVERIDE_tbl(kk),
9688: --dbms_output.put_line('I43');
9689:
9690: --Fix for bug 3734888. Null handled the pl/sql tbls while updating.
9691: FORALL kk in 1..l_upd_ra_bl_dml_code_tbl.count
9692: UPDATE PA_BUDGET_LINES
9693: SET LAST_UPDATE_DATE=sysdate,
9694: LAST_UPDATED_BY=fnd_global.user_id,
9695: LAST_UPDATE_LOGIN=fnd_global.login_id,
9696: QUANTITY= DECODE(l_bl_rbf_flag_tbl(kk),
9809:
9810: -- Compute the total revenue sum of target budget version in PC,PFC (TXN=PFC)
9811: SELECT nvl(sum(nvl(revenue,0)),0), nvl(sum(nvl(project_revenue,0)),0)
9812: INTO l_targ_pfc_rev_after_merge, l_targ_pc_rev_after_merge
9813: FROM pa_budget_lines
9814: WHERE budget_version_id = p_budget_version_id;
9815:
9816: -- Initialise residual amount variables to zero
9817: l_pc_revenue_delta := 0;
9924:
9925: -- Using the source res assigment and target assignment mapping update
9926: -- the target version budget line. Note for AR Versions there can be amounts
9927: -- against txn currency only
9928: UPDATE pa_budget_lines
9929: SET revenue = nvl(revenue,0) + nvl(l_pfc_revenue_delta,0),
9930: txn_revenue = nvl(revenue,0) + nvl(l_pfc_revenue_delta,0), -- TXN and PFC should be same for AR versions
9931: project_revenue = nvl(project_revenue,0) + nvl(l_pc_revenue_delta,0)
9932: WHERE resource_assignment_id =
9942:
9943: IF SQL%ROWCOUNT = 0 THEN
9944: -- If no row is updated, target must be None time phased version.
9945: -- So there would be only one line
9946: UPDATE pa_budget_lines
9947: SET revenue = nvl(revenue,0) + nvl(l_pfc_revenue_delta,0),
9948: txn_revenue = nvl(revenue,0) + nvl(l_pfc_revenue_delta,0), -- TXN and PFC should be same for AR versions
9949: project_revenue = nvl(project_revenue,0) + nvl(l_pc_revenue_delta,0)
9950: WHERE resource_assignment_id =
9980: IF l_bl_rbf_flag_tbl(kk) = 'N' THEN
9981:
9982: l_bl_QUANTITY_tbl(kk) := nvl(l_bl_TXN_REVENUE_tbl(kk),0) + nvl(l_pfc_revenue_delta,0);
9983:
9984: UPDATE pa_budget_lines
9985: SET quantity=txn_revenue
9986: WHERE budget_line_id=l_rounded_bl_id;
9987:
9988: l_qty_adjusted_flag:='Y';
9999:
10000: SELECT pra.rate_based_flag
10001: INTO l_rounded_bl_rbf
10002: FROM pa_resource_assignments pra,
10003: pa_budget_lines pbl
10004: WHERE pra.resource_assignment_id = pbl.resource_assignment_id
10005: AND pbl.budget_line_id = l_rounded_bl_id;
10006:
10007: IF l_rounded_bl_rbf ='N' THEN
10005: AND pbl.budget_line_id = l_rounded_bl_id;
10006:
10007: IF l_rounded_bl_rbf ='N' THEN
10008:
10009: UPDATE pa_budget_lines
10010: SET quantity=txn_revenue
10011: WHERE budget_line_id=l_rounded_bl_id;
10012:
10013: END IF;
10077:
10078: ----Prepare the pl/sql tbls to call the API in planning transaction utils that calls PJI API
10079: ----plan_update
10080:
10081: SELECT pa_budget_lines_s.currval
10082: INTO l_dummy
10083: FROM dual;
10084:
10085: IF l_dummy=l_id_before_bl_insertion THEN
10087: l_id_after_bl_insertion := l_id_before_bl_insertion;
10088:
10089: ELSE
10090:
10091: SELECT pa_budget_lines_s.nextval
10092: INTO l_id_after_bl_insertion
10093: FROM dual;
10094:
10095: END IF;
10156: l_pji_prm_rbs_elem_id_tbl,
10157: l_pji_prm_res_cls_code_tbl,
10158: l_pji_prm_rbf_tbl
10159: FROM pa_resource_assignments pra,
10160: pa_budget_lines pbl
10161: WHERE pra.resource_assignment_id = pbl.resource_assignment_id
10162: AND (pbl.budget_line_id BETWEEN l_id_before_bl_insertion AND l_id_after_bl_insertion)
10163: AND pra.budget_Version_id=p_budget_version_id;
10164:
10419: END IF;
10420: */
10421:
10422: --IPM Architecture Enhancement: Start Bug 4865563
10423: -- The PA_RESOURCE_ASGN_CURR maintenance api updates the pa_budget_lines manually.
10424: -- populate_display_quantity populates the display_quantity appropriately
10425:
10426: --This api will take care of inserting display_quantity appropriately.Not necessary to insert every time in the budget lines
10427: PA_BUDGET_LINES_UTILS.populate_display_qty
10423: -- The PA_RESOURCE_ASGN_CURR maintenance api updates the pa_budget_lines manually.
10424: -- populate_display_quantity populates the display_quantity appropriately
10425:
10426: --This api will take care of inserting display_quantity appropriately.Not necessary to insert every time in the budget lines
10427: PA_BUDGET_LINES_UTILS.populate_display_qty
10428: (p_budget_version_id => p_budget_version_id,
10429: p_context => 'FINANCIAL',
10430: --p_use_temp_table_flag => 'N',
10431: --p_resource_assignment_id_tab => l_res_assignment_id_tbl,
10433: x_return_status => l_return_status);
10434:
10435: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
10436: IF P_PA_debug_mode = 'Y' THEN
10437: pa_debug.g_err_stage:= 'Error in PA_BUDGET_LINES_UTILS.populate_display_qty';
10438: pa_debug.write(l_module_name,pa_debug.g_err_stage,l_debug_level5);
10439: END IF;
10440: RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
10441: END IF;
10572: l_impl_pc_rev_amt := l_targ_pc_rev_after_merge - l_targ_pc_rev_before_merge + l_pc_revenue_delta;
10573: l_impl_pfc_rev_amt := l_targ_pfc_rev_after_merge - l_targ_pfc_rev_before_merge + l_pfc_revenue_delta;
10574:
10575: ELSE
10576: --Derive l_pc/pfc_revenue_delta. In this case we have to go to pa_budget_lines since the amounts
10577: --would not have got rolled up
10578: SELECT sum(pbl.project_revenue) - l_targ_pc_rev_before_merge
10579: ,sum(pbl.revenue) - l_targ_pfc_rev_before_merge
10580: INTO l_impl_pc_rev_amt
10578: SELECT sum(pbl.project_revenue) - l_targ_pc_rev_before_merge
10579: ,sum(pbl.revenue) - l_targ_pfc_rev_before_merge
10580: INTO l_impl_pc_rev_amt
10581: ,l_impl_pfc_rev_amt
10582: FROM pa_budget_lines pbl
10583: WHERE budget_version_id=p_budget_version_id;
10584:
10585: END IF;
10586:
11038: IS
11039: SELECT 'Y'
11040: FROM DUAL
11041: WHERE EXISTS (SELECT 1
11042: FROM pa_budget_lines pbl
11043: WHERE pbl.budget_version_id IN (ci_ci_ver_id1, ci_ci_ver_id2)
11044: AND( pbl.cost_rejection_code IS NOT NULL
11045: OR pbl.revenue_rejection_code IS NOT NULL
11046: OR pbl.burden_rejection_code IS NOT NULL
11444:
11445: --To get the total amount.
11446: SELECT (sum(nvl(txn_revenue,0)) )
11447: INTO l_total_amount
11448: FROM pa_budget_lines
11449: WHERE budget_version_id = NVL(l_ci_all_version_id,l_ci_rev_version_id);
11450:
11451: IF l_debug_mode = 'Y' THEN
11452: pa_debug.g_err_stage:= 'Total Planned Revenue amount is:' ||l_total_amount;
12328: l_ci_already_impl_flag VARCHAR2(1) := 'N';
12329:
12330: l_final_rev_par_impl_flag pa_budget_versions.rev_partially_impl_flag%TYPE;
12331: --Bug 4136238
12332: l_partial_impl_rev_amt pa_budget_lines.txn_revenue%TYPE;
12333:
12334: BEGIN
12335: fnd_profile.get('PA_DEBUG_MODE',l_debug_mode);
12336: l_debug_mode := NVL(l_debug_mode, 'Y');
12480: ,nvl(sum(project_revenue),0) total_amt_in_pc
12481: INTO l_total_amount,
12482: l_total_amount_in_pfc,
12483: l_total_amount_in_pc
12484: FROM pa_budget_lines
12485: WHERE budget_version_id = p_ci_rev_version_id;
12486:
12487: --Bug 4136238. p_partial_impl_rev_amt will be NULL or 0 only if the full impact is being implemented. In this
12488: --funding lines should be created for the whole amount in the CI version.
13121: l_budget_ci_map_rec_tbl budget_ci_map_rec_tbl_type;
13122: l_impl_impact_type_code VARCHAR2(10);
13123: l_agreement_id pa_agreements_all.agreement_id%TYPE;
13124: l_funding_category VARCHAR2(30);
13125: l_partial_impl_rev_amt pa_budget_lines.txn_revenue%TYPE;
13126:
13127: --These two variables will be used for comparing the no. of error messages in the error message stack when the
13128: --API called and when the API is done with the processing. If the no of messages in the two pl/sql tbls are
13129: --different then the error status will be returned as E from the API