[Home] [Help]
2827: l_partially_impl_flag VARCHAR2(1);
2828: l_agreement_num pa_agreements_all.agreement_num%TYPE;
2829: l_approved_fin_pt_id pa_fin_plan_types_b.fin_plan_type_id%TYPE;
2830: l_call_rep_lines_api VARCHAR2(1):='N';
2831: l_id_before_bl_insertion pa_budget_lines.budget_line_id%TYPE;
2832: l_id_after_bl_insertion pa_budget_lines.budget_line_id%TYPE;
2833: l_dummy NUMBER;
2834:
2835:
2828: l_agreement_num pa_agreements_all.agreement_num%TYPE;
2829: l_approved_fin_pt_id pa_fin_plan_types_b.fin_plan_type_id%TYPE;
2830: l_call_rep_lines_api VARCHAR2(1):='N';
2831: l_id_before_bl_insertion pa_budget_lines.budget_line_id%TYPE;
2832: l_id_after_bl_insertion pa_budget_lines.budget_line_id%TYPE;
2833: l_dummy NUMBER;
2834:
2835:
2836:
2849: l_record_version_number pa_budget_versions.record_version_number%TYPE;
2850: l_partial_factor NUMBER := 1;
2851: l_impl_amt pa_fp_merged_ctrl_items.impl_proj_revenue%TYPE;
2852: l_total_amt pa_budget_versions.total_project_revenue%TYPE;
2853: l_total_amt_in_pfc pa_budget_lines.revenue%TYPE;
2854: l_total_amt_in_pc pa_budget_lines.project_revenue%TYPE;
2855:
2856: l_src_proj_fp_options_id pa_proj_fp_options.proj_fp_options_id%TYPE;
2857: l_src_multi_curr_flag pa_proj_fp_options.plan_in_multi_curr_flag%TYPE;
2850: l_partial_factor NUMBER := 1;
2851: l_impl_amt pa_fp_merged_ctrl_items.impl_proj_revenue%TYPE;
2852: l_total_amt pa_budget_versions.total_project_revenue%TYPE;
2853: l_total_amt_in_pfc pa_budget_lines.revenue%TYPE;
2854: l_total_amt_in_pc pa_budget_lines.project_revenue%TYPE;
2855:
2856: l_src_proj_fp_options_id pa_proj_fp_options.proj_fp_options_id%TYPE;
2857: l_src_multi_curr_flag pa_proj_fp_options.plan_in_multi_curr_flag%TYPE;
2858: l_src_fin_plan_type_id pa_proj_fp_options.fin_plan_type_id%TYPE;
3095:
3096:
3097:
3098:
3099: -- Start of tables prepared to insert/update into pa_budget_lines
3100: l_bl_RESOURCE_ASIGNMENT_ID_tbl SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE();
3101: l_upd_ra_bl_dml_code_tbl SYSTEM.PA_VARCHAR2_15_TBL_TYPE := SYSTEM.PA_VARCHAR2_15_TBL_TYPE();
3102: l_bl_START_DATE_tbl SYSTEM.PA_DATE_TBL_TYPE := SYSTEM.PA_DATE_TBL_TYPE();
3103: l_bl_END_DATE_tbl SYSTEM.PA_DATE_TBL_TYPE := SYSTEM.PA_DATE_TBL_TYPE();
3186: l_bl_PC_CUR_CONV_REJ_CODE_tbl SYSTEM.PA_VARCHAR2_30_TBL_TYPE:= SYSTEM.PA_VARCHAR2_30_TBL_TYPE();
3187: l_bl_PFC_CUR_CONV_REJ_CODE_tbl SYSTEM.PA_VARCHAR2_30_TBL_TYPE:= SYSTEM.PA_VARCHAR2_30_TBL_TYPE();
3188: l_bl_rbf_flag_tbl SYSTEM.PA_VARCHAR2_1_TBL_TYPE:= SYSTEM.PA_VARCHAR2_1_TBL_TYPE();
3189:
3190: -- End of tables prepared to insert/update into pa_budget_lines
3191:
3192: l_ci_cost_version_id pa_budget_versions.budget_version_id%TYPE;
3193: l_ci_rev_version_id pa_budget_versions.budget_version_id%TYPE;
3194: l_ci_all_version_id pa_budget_versions.budget_version_id%TYPE;
3245: l_targ_pc_rawc_after_merge pa_budget_versions.total_project_raw_cost%TYPE;
3246: l_targ_pc_burdc_after_merge pa_budget_versions.total_project_burdened_cost%TYPE;
3247: l_targ_pc_rev_after_merge pa_budget_versions.total_project_revenue%TYPE;
3248: l_impl_earlier VARCHAR2(1);
3249: l_total_agr_revenue pa_budget_lines.txn_revenue%TYPE;
3250:
3251: l_partial_impl_rev_amt NUMBER;
3252: l_partial_rev_impl_flag VARCHAR2(1);
3253:
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;
3271: l_pfc_rev_merged pa_budget_lines.revenue%TYPE;
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;
3271: l_pfc_rev_merged pa_budget_lines.revenue%TYPE;
3272: l_pc_rev_for_merge pa_budget_lines.project_revenue%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;
3271: l_pfc_rev_merged pa_budget_lines.revenue%TYPE;
3272: l_pc_rev_for_merge pa_budget_lines.project_revenue%TYPE;
3273: l_pfc_rev_for_merge pa_budget_lines.revenue%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;
3271: l_pfc_rev_merged pa_budget_lines.revenue%TYPE;
3272: l_pc_rev_for_merge pa_budget_lines.project_revenue%TYPE;
3273: l_pfc_rev_for_merge pa_budget_lines.revenue%TYPE;
3274: l_src_dummy1 number;
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;
3271: l_pfc_rev_merged pa_budget_lines.revenue%TYPE;
3272: l_pc_rev_for_merge pa_budget_lines.project_revenue%TYPE;
3273: l_pfc_rev_for_merge pa_budget_lines.revenue%TYPE;
3274: l_src_dummy1 number;
3275: l_src_dummy2 pa_resource_list_members.alias%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;
3271: l_pfc_rev_merged pa_budget_lines.revenue%TYPE;
3272: l_pc_rev_for_merge pa_budget_lines.project_revenue%TYPE;
3273: l_pfc_rev_for_merge pa_budget_lines.revenue%TYPE;
3274: l_src_dummy1 number;
3275: l_src_dummy2 pa_resource_list_members.alias%TYPE;
3276: l_impl_proj_func_revenue pa_fp_merged_ctrl_items.impl_proj_func_revenue%TYPE;-- bug 4035856
3269: l_pfc_revenue_delta pa_budget_lines.revenue%TYPE;
3270: l_pc_rev_merged pa_budget_lines.project_revenue%TYPE;
3271: l_pfc_rev_merged pa_budget_lines.revenue%TYPE;
3272: l_pc_rev_for_merge pa_budget_lines.project_revenue%TYPE;
3273: l_pfc_rev_for_merge pa_budget_lines.revenue%TYPE;
3274: l_src_dummy1 number;
3275: l_src_dummy2 pa_resource_list_members.alias%TYPE;
3276: l_impl_proj_func_revenue pa_fp_merged_ctrl_items.impl_proj_func_revenue%TYPE;-- bug 4035856
3277: l_impl_proj_revenue pa_fp_merged_ctrl_items.impl_proj_revenue%TYPE;-- bug 4035856
3277: l_impl_proj_revenue pa_fp_merged_ctrl_items.impl_proj_revenue%TYPE;-- bug 4035856
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;
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
3305: WHERE pbl.resource_assignment_id = pra.resource_assignment_id)
3306: ORDER BY dispSeq DESC , rlm.alias DESC;
3307:
3308: --This cursor is same as src_delta_amt_adj_ra_cur. This will be used when the source and target
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
3320: FROM pa_budget_lines pbl
3321: WHERE pbl.resource_assignment_id = pra.resource_assignment_id)
3322: ORDER BY dispSeq DESC , rlm.alias DESC;
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:
3332:
3333: -- End of variables declared for bug 4035856
3823: nvl(sum(pbl.project_revenue),0) total_amt_in_pc
3824: INTO L_total_amt,
3825: l_total_amt_in_pfc,
3826: l_total_amt_in_pc
3827: FROM Pa_budget_lines pbl
3828: WHERE pbl.budget_Version_id= l_src_ver_id_tbl(j);
3829: --IPM Arch Enhancement Bug 4865563
3830: /*
3831: and pbl.cost_rejection_code IS NULL
3885: END IF;
3886:
3887: SELECT NVL(sum(txn_revenue),0)
3888: INTO l_partial_impl_rev_amt
3889: FROM pa_budget_lines
3890: WHERE budget_version_id=l_src_ver_id_tbl(j);
3891:
3892: IF P_PA_debug_mode = 'Y' THEN
3893: pa_debug.g_err_stage:='l_partial_impl_rev_amt derived is '||l_partial_impl_rev_amt;
5483: -- 2) If p_calling_context is generation
5484: -- a) if retain manually edited lines is Y do not update ras with transaction source code as null
5485: -- b) for ras that can be updated do not override transaction source code if there are already amounts
5486: -- if no amounts stamp transaction source code as 'CHANGE DOCUMENTS'
5487: -- Please note that the select against pa_budget_lines is unnecessary when p_calling_context is null
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
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 =
5497: (SELECT DECODE(COUNT(*),0,'CHANGE_DOCUMENTS',TRANSACTION_SOURCE_CODE)
5498: FROM pa_budget_lines pbl
5499: WHERE pbl.resource_assignment_id = pra.resource_assignment_id),
5500: RATE_BASED_FLAG = l_targ_RATE_BASED_FLAG_tbl(kk), -- bug 4171006
5501: LAST_UPDATE_DATE = sysdate,
5502: LAST_UPDATED_BY = fnd_global.user_id,
5639: sum(pbl.burdened_cost) burdened_cost,
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,
5658: sum(pbl.raw_cost) raw_cost,
5659: sum(pbl.burdened_cost) burdened_cost,
5660: sum(pbl.revenue) revenue,
5661: pbl.txn_currency_code
5662: FROM pa_budget_lines pbl
5663: WHERE pbl.budget_Version_id = p_budget_version_id
5664: GROUP BY pbl.resource_assignment_id, pbl.txn_currency_code)pblt
5665: 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(+)
5666: and pblt.txn_Currency_code(+)= pbls.txn_currency_code;
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)
5741: AND pbl.resource_assignment_id=pra.resource_assignment_id
5742: GROUP BY pra.resource_assignment_id, pra.task_id, tmp.resource_list_member_id,
5753: sum(pbl.raw_cost) raw_cost,
5754: sum(pbl.burdened_cost) burdened_cost,
5755: sum(pbl.revenue) revenue,
5756: pbl.txn_currency_code txn_currency_code
5757: FROM pa_budget_lines pbl
5758: WHERE pbl.budget_Version_id = p_budget_version_id
5759: GROUP BY pbl.resource_assignment_id, pbl.txn_currency_code)pblt
5760: 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(+)
5761: and pblt.txn_currency_code(+)= pbls.txn_currency_code;
5920:
5921: END IF;--IF l_src_time_phased_code = 'N' AND (l_targ_time_phased_code = 'P' OR l_targ_time_phased_code = 'G') THEN
5922:
5923: --Get the budget line sequence before inserting data into budget lines. After inserting the budget lines
5924: --the sequence is again compared to see the no. of budget lines that have got inserted. Since pa_budget_lines_s.currval
5925: --is used at a later part of code this SELECT should not be removed
5926: SELECT pa_budget_lines_s.nextval
5927: INTO l_id_before_bl_insertion
5928: FROM DUAL;
5922:
5923: --Get the budget line sequence before inserting data into budget lines. After inserting the budget lines
5924: --the sequence is again compared to see the no. of budget lines that have got inserted. Since pa_budget_lines_s.currval
5925: --is used at a later part of code this SELECT should not be removed
5926: SELECT pa_budget_lines_s.nextval
5927: INTO l_id_before_bl_insertion
5928: FROM DUAL;
5929:
5930: --dbms_output.put_line('I25');
5938: END IF;
5939:
5940: --dbms_output.put_line('I26');
5941: FORALL kk in L_targ_ra_id_tbl.FIRST ..L_targ_ra_id_tbl.LAST
5942: INSERT INTO PA_BUDGET_LINES(
5943: RESOURCE_ASSIGNMENT_ID,
5944: START_DATE,
5945: LAST_UPDATE_DATE,
5946: LAST_UPDATED_BY,
6141: pbl.txn_raw_cost,
6142: pbl.txn_burdened_cost,
6143: pbl.txn_revenue,
6144: pbl.bucketing_period_code,
6145: pa_budget_lines_s.nextval,
6146: pbl.budget_version_id,
6147: pbl.txn_standard_cost_rate,
6148: DECODE(l_target_version_type,
6149: 'REVENUE',pbl.txn_cost_rate_override,
6316:
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)
6347: END IF;
6348:
6349: --dbms_output.put_line('I28');
6350: FORALL kk in L_targ_ra_id_tbl.FIRST ..L_targ_ra_id_tbl.LAST
6351: INSERT INTO PA_BUDGET_LINES(RESOURCE_ASSIGNMENT_ID,
6352: START_DATE,
6353: LAST_UPDATE_DATE,
6354: LAST_UPDATED_BY,
6355: CREATION_DATE,
6547: pbl.txn_raw_cost,
6548: pbl.txn_burdened_cost,
6549: pbl.txn_revenue,
6550: pbl.bucketing_period_code,
6551: pa_budget_lines_s.nextval,
6552: pbl.budget_version_id,
6553: pbl.txn_standard_cost_rate,
6554: DECODE(l_target_version_type,
6555: 'REVENUE',pbl.txn_cost_rate_override,
6719:
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
6753:
6754: --dbms_output.put_line('I30');
6755: IF l_src_resource_list_id = l_targ_resource_list_id THEN
6756: FORALL kk in L_targ_ra_id_tbl.FIRST ..L_targ_ra_id_tbl.LAST
6757: INSERT INTO PA_BUDGET_LINES(
6758: RESOURCE_ASSIGNMENT_ID,
6759: START_DATE,
6760: LAST_UPDATE_DATE,
6761: LAST_UPDATED_BY,
6954: pbl.txn_raw_cost,
6955: pbl.txn_burdened_cost,
6956: pbl.txn_revenue,
6957: pbl.bucketing_period_code,
6958: pa_budget_lines_s.nextval,
6959: pbl.budget_version_id,
6960: pbl.txn_standard_cost_rate,
6961: DECODE(l_target_version_type,
6962: 'REVENUE',pbl.txn_cost_rate_override,
7126:
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
7159: END IF;
7160:
7161: --dbms_output.put_line('I32');
7162: FORALL kk in L_targ_ra_id_tbl.FIRST ..L_targ_ra_id_tbl.LAST
7163: INSERT INTO PA_BUDGET_LINES(
7164: RESOURCE_ASSIGNMENT_ID,
7165: START_DATE,
7166: LAST_UPDATE_DATE,
7167: LAST_UPDATED_BY,
7360: pbl.txn_raw_cost,
7361: pbl.txn_burdened_cost,
7362: pbl.txn_revenue,
7363: pbl.bucketing_period_code,
7364: pa_budget_lines_s.nextval,
7365: pbl.budget_version_id,
7366: pbl.txn_standard_cost_rate,
7367: DECODE(l_target_version_type,
7368: 'REVENUE',pbl.txn_cost_rate_override,
7529: --Bug 4224757. Code changes for bug#4224757 ends here
7530:
7531: decode(count(pbls.budget_line_id),1,max(pbls.PC_CUR_CONV_REJECTION_CODE),null) pc_cur_conv_rejection_code,
7532: decode(count(pbls.budget_line_id),1,max(pbls.PFC_CUR_CONV_REJECTION_CODE),null) pfc_cur_conv_rejection_code
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,
7785: l_bl_BURDEN_COST_RATE_tbl,
7786: l_bl_BURDEN_COST_RAT_OVRID_tbl,
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)
7786: l_bl_BURDEN_COST_RAT_OVRID_tbl,
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
8035: l_bl_BURDEN_COST_RATE_tbl,
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
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
8150: where get_mapped_dml_code(get_task_id(l_targ_plan_level_code,pbls.task_id),pbls.resource_list_member_id)='UPDATE'
8151: and pblt.budget_version_id(+) = p_budget_version_id
8390: l_bl_BURDEN_COST_RATE_tbl,
8391: l_bl_BURDEN_COST_RAT_OVRID_tbl,
8392: l_bl_PC_CUR_CONV_REJ_CODE_tbl,
8393: l_bl_PFC_CUR_CONV_REJ_CODE_tbl
8394: from pa_budget_lines pbls,
8395: pa_budget_lines pblt
8396: where get_mapped_dml_code(null,null,pbls.resource_assignment_id,l_targ_plan_level_code)='UPDATE'
8397: and pbls.budget_version_id = l_src_ver_id_tbl(j)
8398: and pblt.budget_version_id(+) = p_budget_version_id
8391: l_bl_BURDEN_COST_RAT_OVRID_tbl,
8392: l_bl_PC_CUR_CONV_REJ_CODE_tbl,
8393: l_bl_PFC_CUR_CONV_REJ_CODE_tbl
8394: from pa_budget_lines pbls,
8395: pa_budget_lines pblt
8396: where get_mapped_dml_code(null,null,pbls.resource_assignment_id,l_targ_plan_level_code)='UPDATE'
8397: and pbls.budget_version_id = l_src_ver_id_tbl(j)
8398: and pblt.budget_version_id(+) = p_budget_version_id
8399: and pblt.resource_assignment_id(+)=get_mapped_ra_id(null,null,pbls.resource_assignment_id,l_targ_plan_level_code)
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: (SELECT pbls.resource_assignment_id
8641: ,pbls.start_date
8642: ,pbls.last_update_date
8643: ,pbls.last_updated_by
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
8749: where get_mapped_dml_code(get_task_id(l_targ_plan_level_code,pbls.task_id),pbls.resource_list_member_id)='UPDATE'
8750: and pblt.budget_version_id(+) = p_budget_version_id
8848: END IF; --IF l_partial_factor <> 1
8849:
8850: --dbms_output.put_line('I43');
8851: FORALL kk in 1..l_upd_ra_bl_dml_code_tbl.count
8852: INSERT INTO PA_BUDGET_LINES(RESOURCE_ASSIGNMENT_ID,
8853: START_DATE,
8854: LAST_UPDATE_DATE,
8855: LAST_UPDATED_BY,
8856: CREATION_DATE,
9050: l_bl_TXN_RAW_COST_tbl(kk),
9051: l_bl_TXN_BURDENED_COST_tbl(kk),
9052: l_bl_TXN_REVENUE_tbl(kk),
9053: l_bl_BUCKETING_PERIOD_CODE_tbl(kk),
9054: pa_budget_lines_s.nextval,
9055: p_budget_version_id,
9056: l_bl_TXN_STD_COST_RATE_tbl(kk),
9057: DECODE(l_target_version_type,
9058: 'REVENUE',l_bl_TXN_COST_RATE_OVERIDE_tbl(kk),
9088: --dbms_output.put_line('I43');
9089:
9090: --Fix for bug 3734888. Null handled the pl/sql tbls while updating.
9091: FORALL kk in 1..l_upd_ra_bl_dml_code_tbl.count
9092: UPDATE PA_BUDGET_LINES
9093: SET LAST_UPDATE_DATE=sysdate,
9094: LAST_UPDATED_BY=fnd_global.user_id,
9095: LAST_UPDATE_LOGIN=fnd_global.login_id,
9096: QUANTITY= DECODE(l_bl_rbf_flag_tbl(kk),
9209:
9210: -- Compute the total revenue sum of target budget version in PC,PFC (TXN=PFC)
9211: SELECT nvl(sum(nvl(revenue,0)),0), nvl(sum(nvl(project_revenue,0)),0)
9212: INTO l_targ_pfc_rev_after_merge, l_targ_pc_rev_after_merge
9213: FROM pa_budget_lines
9214: WHERE budget_version_id = p_budget_version_id;
9215:
9216: -- Initialise residual amount variables to zero
9217: l_pc_revenue_delta := 0;
9324:
9325: -- Using the source res assigment and target assignment mapping update
9326: -- the target version budget line. Note for AR Versions there can be amounts
9327: -- against txn currency only
9328: UPDATE pa_budget_lines
9329: SET revenue = nvl(revenue,0) + nvl(l_pfc_revenue_delta,0),
9330: txn_revenue = nvl(revenue,0) + nvl(l_pfc_revenue_delta,0), -- TXN and PFC should be same for AR versions
9331: project_revenue = nvl(project_revenue,0) + nvl(l_pc_revenue_delta,0)
9332: WHERE resource_assignment_id =
9342:
9343: IF SQL%ROWCOUNT = 0 THEN
9344: -- If no row is updated, target must be None time phased version.
9345: -- So there would be only one line
9346: UPDATE pa_budget_lines
9347: SET revenue = nvl(revenue,0) + nvl(l_pfc_revenue_delta,0),
9348: txn_revenue = nvl(revenue,0) + nvl(l_pfc_revenue_delta,0), -- TXN and PFC should be same for AR versions
9349: project_revenue = nvl(project_revenue,0) + nvl(l_pc_revenue_delta,0)
9350: WHERE resource_assignment_id =
9380: IF l_bl_rbf_flag_tbl(kk) = 'N' THEN
9381:
9382: l_bl_QUANTITY_tbl(kk) := nvl(l_bl_TXN_REVENUE_tbl(kk),0) + nvl(l_pfc_revenue_delta,0);
9383:
9384: UPDATE pa_budget_lines
9385: SET quantity=txn_revenue
9386: WHERE budget_line_id=l_rounded_bl_id;
9387:
9388: l_qty_adjusted_flag:='Y';
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:
9407: IF l_rounded_bl_rbf ='N' THEN
9405: AND pbl.budget_line_id = l_rounded_bl_id;
9406:
9407: IF l_rounded_bl_rbf ='N' THEN
9408:
9409: UPDATE pa_budget_lines
9410: SET quantity=txn_revenue
9411: WHERE budget_line_id=l_rounded_bl_id;
9412:
9413: END IF;
9477:
9478: ----Prepare the pl/sql tbls to call the API in planning transaction utils that calls PJI API
9479: ----plan_update
9480:
9481: SELECT pa_budget_lines_s.currval
9482: INTO l_dummy
9483: FROM dual;
9484:
9485: IF l_dummy=l_id_before_bl_insertion THEN
9487: l_id_after_bl_insertion := l_id_before_bl_insertion;
9488:
9489: ELSE
9490:
9491: SELECT pa_budget_lines_s.nextval
9492: INTO l_id_after_bl_insertion
9493: FROM dual;
9494:
9495: END IF;
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;
9566:
9824: END IF;
9825: */
9826:
9827: --IPM Architecture Enhancement: Start Bug 4865563
9828: -- The PA_RESOURCE_ASGN_CURR maintenance api updates the pa_budget_lines manually.
9829: -- populate_display_quantity populates the display_quantity appropriately
9830:
9831: --This api will take care of inserting display_quantity appropriately.Not necessary to insert every time in the budget lines
9832: PA_BUDGET_LINES_UTILS.populate_display_qty
9828: -- The PA_RESOURCE_ASGN_CURR maintenance api updates the pa_budget_lines manually.
9829: -- populate_display_quantity populates the display_quantity appropriately
9830:
9831: --This api will take care of inserting display_quantity appropriately.Not necessary to insert every time in the budget lines
9832: PA_BUDGET_LINES_UTILS.populate_display_qty
9833: (p_budget_version_id => p_budget_version_id,
9834: p_context => 'FINANCIAL',
9835: --p_use_temp_table_flag => 'N',
9836: --p_resource_assignment_id_tab => l_res_assignment_id_tbl,
9838: x_return_status => l_return_status);
9839:
9840: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
9841: IF P_PA_debug_mode = 'Y' THEN
9842: pa_debug.g_err_stage:= 'Error in PA_BUDGET_LINES_UTILS.populate_display_qty';
9843: pa_debug.write(l_module_name,pa_debug.g_err_stage,l_debug_level5);
9844: END IF;
9845: RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
9846: END IF;
9978: l_impl_pc_rev_amt := l_targ_pc_rev_after_merge - l_targ_pc_rev_before_merge + l_pc_revenue_delta;
9979: l_impl_pfc_rev_amt := l_targ_pfc_rev_after_merge - l_targ_pfc_rev_before_merge + l_pfc_revenue_delta;
9980:
9981: ELSE
9982: --Derive l_pc/pfc_revenue_delta. In this case we have to go to pa_budget_lines since the amounts
9983: --would not have got rolled up
9984: SELECT sum(pbl.project_revenue) - l_targ_pc_rev_before_merge
9985: ,sum(pbl.revenue) - l_targ_pfc_rev_before_merge
9986: INTO l_impl_pc_rev_amt
9984: SELECT sum(pbl.project_revenue) - l_targ_pc_rev_before_merge
9985: ,sum(pbl.revenue) - l_targ_pfc_rev_before_merge
9986: INTO l_impl_pc_rev_amt
9987: ,l_impl_pfc_rev_amt
9988: FROM pa_budget_lines pbl
9989: WHERE budget_version_id=p_budget_version_id;
9990:
9991: END IF;
9992:
10444: IS
10445: SELECT 'Y'
10446: FROM DUAL
10447: WHERE EXISTS (SELECT 1
10448: FROM pa_budget_lines pbl
10449: WHERE pbl.budget_version_id IN (ci_ci_ver_id1, ci_ci_ver_id2)
10450: AND( pbl.cost_rejection_code IS NOT NULL
10451: OR pbl.revenue_rejection_code IS NOT NULL
10452: OR pbl.burden_rejection_code IS NOT NULL
10861:
10862: --To get the total amount.
10863: SELECT (sum(nvl(txn_revenue,0)) )
10864: INTO l_total_amount
10865: FROM pa_budget_lines
10866: WHERE budget_version_id = NVL(l_ci_all_version_id,l_ci_rev_version_id);
10867:
10868: IF l_debug_mode = 'Y' THEN
10869: pa_debug.g_err_stage:= 'Total Planned Revenue amount is:' ||l_total_amount;
11804: l_ci_already_impl_flag VARCHAR2(1) := 'N';
11805:
11806: l_final_rev_par_impl_flag pa_budget_versions.rev_partially_impl_flag%TYPE;
11807: --Bug 4136238
11808: l_partial_impl_rev_amt pa_budget_lines.txn_revenue%TYPE;
11809:
11810: BEGIN
11811: fnd_profile.get('PA_DEBUG_MODE',l_debug_mode);
11812: l_debug_mode := NVL(l_debug_mode, 'Y');
11956: ,nvl(sum(project_revenue),0) total_amt_in_pc
11957: INTO l_total_amount,
11958: l_total_amount_in_pfc,
11959: l_total_amount_in_pc
11960: FROM pa_budget_lines
11961: WHERE budget_version_id = p_ci_rev_version_id;
11962:
11963: --Bug 4136238. p_partial_impl_rev_amt will be NULL or 0 only if the full impact is being implemented. In this
11964: --funding lines should be created for the whole amount in the CI version.
12597: l_budget_ci_map_rec_tbl budget_ci_map_rec_tbl_type;
12598: l_impl_impact_type_code VARCHAR2(10);
12599: l_agreement_id pa_agreements_all.agreement_id%TYPE;
12600: l_funding_category VARCHAR2(30);
12601: l_partial_impl_rev_amt pa_budget_lines.txn_revenue%TYPE;
12602:
12603: --These two variables will be used for comparing the no. of error messages in the error message stack when the
12604: --API called and when the API is done with the processing. If the no of messages in the two pl/sql tbls are
12605: --different then the error status will be returned as E from the API