[Home] [Help]
25: g_spread_required_flag VARCHAR2(1) := 'N';
26: g_rollup_required_flag VARCHAR2(1) := 'Y';
27: g_wp_version_flag pa_budget_versions.wp_version_flag%TYPE;
28: g_proj_structure_ver_id NUMBER;
29: g_ra_bl_txn_currency_code pa_budget_lines.txn_currency_code%TYPE := NULL;
30: g_line_start_date pa_fp_res_assignments_tmp.line_start_date%TYPE;
31: g_line_end_date pa_fp_res_assignments_tmp.line_end_date%TYPE;
32: g_source_context pa_fp_res_assignments_tmp.source_context%TYPE;
33: g_clientExtn_api_call_flag VARCHAR2(1) := 'Y' ;
284: CURSOR cur_avgBlrts(p_resource_asg_id IN NUMBER
285: ,p_txn_curr_code IN VARCHAR2
286: ,p_line_start_date IN DATE
287: ,p_line_end_date IN DATE ) IS
288: SELECT /*+ INDEX(blavgrt PA_BUDGET_LINES_U1) */
289: AVG(DECODE((nvl(blavgrt.quantity,0) - nvl(blavgrt.init_quantity,0)),0,NULL
290: ,blavgrt.txn_cost_rate_override)) avg_txn_cost_rate_override
291: ,AVG(DECODE((nvl(blavgrt.quantity,0) - nvl(blavgrt.init_quantity,0)),0,NULL
292: ,blavgrt.burden_cost_rate_override)) avg_burden_cost_rate_override
300: /*bug fix:4693839 */
301: ,AVG(NVL(blavgrt.txn_cost_rate_override,blavgrt.txn_standard_cost_rate)) avg_zero_null_cost_rate
302: ,AVG(NVL(blavgrt.burden_cost_rate_override,blavgrt.burden_cost_rate)) avg_zero_null_burden_rate
303: ,AVG(NVL(blavgrt.txn_bill_rate_override,blavgrt.txn_standard_bill_rate)) avg_zero_null_bill_rate
304: FROM pa_budget_lines blavgrt
305: WHERE blavgrt.resource_assignment_id = p_resource_asg_id
306: AND blavgrt.txn_currency_code = p_txn_curr_code
307: AND ( (p_line_start_date is NULL AND p_line_end_date is NULL )
308: OR
317: ,p_line_end_date IN DATE
318: ,p_avg_txn_cost_rate_override IN NUMBER
319: ,p_avg_burden_rate_override IN NUMBER
320: ,p_avg_bill_rate_override IN NUMBER ) IS
321: SELECT /*+ INDEX(BL PA_BUDGET_LINES_U1) */
322: decode(sum(bl.quantity),0,NULL,sum(bl.quantity)) quantity
323: ,decode( sum(bl.txn_raw_cost),0,NULL,sum(bl.txn_raw_cost)) txn_raw_cost
324: ,decode(sum(bl.txn_burdened_cost),0,NULL,sum(bl.txn_burdened_cost)) txn_burdened_cost
325: ,decode(sum(bl.txn_revenue),0,NULL,sum(bl.txn_revenue)) txn_revenue
422: ,SUM(bl.txn_raw_cost) bl_zero_null_rawcost
423: ,SUM(bl.txn_burdened_cost) bl_zero_null_burdencost
424: ,SUM(bl.txn_revenue) bl_zero_null_revenue
425: ,SUM(bl.display_quantity) display_quantity --Bug 6429285
426: FROM pa_budget_lines bl
427: WHERE bl.resource_assignment_id = p_resource_asg_id
428: AND bl.txn_currency_code = p_txn_curr_code
429: AND ( (p_line_start_date is NULL AND p_line_end_date is NULL )
430: OR
527: ,x_msg_data OUT NOCOPY Varchar2
528: ) IS
529:
530: CURSOR cur_get_rejLines IS
531: SELECT /*+ INDEX(BL PA_BUDGET_LINES_U1) */
532: bl.resource_assignment_id
533: ,bl.txn_currency_code
534: ,sum(bl.txn_revenue)
535: ,decode(p_source_context,'BUDGET_LINE',tmp.start_date,NULL) start_date
533: ,bl.txn_currency_code
534: ,sum(bl.txn_revenue)
535: ,decode(p_source_context,'BUDGET_LINE',tmp.start_date,NULL) start_date
536: ,decode(p_source_context,'BUDGET_LINE',tmp.end_date,NULL) end_date
537: FROM PA_BUDGET_LINES bl
538: ,PA_FP_SPREAD_CALC_TMP tmp
539: WHERE tmp.budget_version_id = p_budget_version_id
540: AND bl.resource_assignment_id = tmp.resource_assignment_id
541: AND bl.txn_currency_code = tmp.txn_currency_code
604: ,g_etcrevrej_end_date_tab;
605: CLOSE cur_get_rejLines;
606: If l_etcrevrej_raId_tab.COUNT > 0 Then --{
607: FORALL i IN l_etcrevrej_raId_tab.FIRST .. l_etcrevrej_raId_tab.LAST
608: UPDATE PA_BUDGET_LINES bl
609: SET bl.other_rejection_code = NULL
610: WHERE bl.resource_assignment_id = l_etcrevrej_raId_tab(i)
611: AND bl.txn_currency_code = l_etcrevrej_txnCur_tab(i)
612: AND nvl(g_etcrevrej_revenue_tab(i),0) <> nvl(l_etcrevrej_revenue_tab(i),0)
1295: END CHECK_MRC_INSTALLED;
1296:
1297: /* This API will set the statistics for global tmp tables. Since Gather_statistics and set_table_stats
1298: * does the commit in the main session, this api made as autonomous so that main session should not be impacted.
1299: * Based on the statistics collected for the pa_budget_lines and pa_resource_assignments on pjperf table
1300: * the tmp table stats will be set as follows
1301: * If number of rows < 500 then set the number of rows = 500 and num blocks to number of rows/30
1302: * if number of rows > 500 then set the number of rows to passed in value and block size to numrows / 30
1303: * This API will be called only once for all tmp tables
2834: CURSOR cur_act_exists IS
2835: SELECT 'Y'
2836: FROM dual
2837: WHERE EXISTS (select null
2838: from pa_budget_lines bl
2839: Where bl.budget_version_id = p_budget_version_id
2840: AND (nvl(bl.init_quantity,0) <> 0
2841: OR nvl(bl.txn_init_raw_cost,0) <> 0
2842: OR nvl(bl.txn_init_burdened_cost,0) <> 0
3338:
3339: IF NVL(g_time_phased_code,'N') NOT IN ('G','P') THEN
3340: /* For Non-Time phase budgets, Update the budget lines with planning start and planning end dates */
3341: g_stage :='Reset_Planning_end_date:101';
3342: UPDATE pa_budget_lines bl
3343: SET bl.end_date = (select ra.planning_end_date
3344: From pa_resource_assignments ra
3345: Where ra.resource_assignment_id = bl.resource_assignment_id)
3346: WHERE bl.budget_version_id = p_budget_version_id
3391: /* Bug fix: AND ra.sp_fixed_date is NOT NULL this is commented as in some ofthe flows,
3392: * the sp fixed date is null but spread curve is 6 */
3393: AND ra.spread_curve_id = 6
3394: AND EXISTS ( select null
3395: from pa_budget_lines bl
3396: where bl.budget_version_id = p_budget_version_id
3397: and bl.resource_assignment_id = ra.resource_assignment_id
3398: and bl.txn_currency_code = tmp.txn_currency_code
3399: group by bl.resource_assignment_id
3417: * this should not happen. Ideally when the actuals were collected for the resource if plan < actual
3418: * then plan should be made equal to actual and etc should be zero
3419: */
3420: CURSOR cur_NegEtcLines IS
3421: SELECT /*+ NO_UNNEST INDEX (BL,PA_BUDGET_LINES_U1) */ bl.budget_line_id -- bug 4873834
3422: ,bl.resource_assignment_id
3423: ,bl.txn_currency_code
3424: ,bl.start_date
3425: ,bl.end_date
3450: ,bl.project_init_revenue
3451: ,bl.init_raw_cost
3452: ,bl.init_burdened_cost
3453: ,bl.init_revenue
3454: FROM pa_budget_lines bl
3455: ,pa_fp_spread_calc_tmp tmp
3456: WHERE tmp.budget_version_id = p_budget_version_id
3457: AND bl.budget_version_id = tmp.budget_version_id
3458: AND bl.resource_assignment_id = tmp.resource_assignment_id
3570: CURSOR cur_sp_bl_chk IS
3571: SELECT 'Y'
3572: FROM DUAL
3573: WHERE EXISTS (select null
3574: from pa_budget_lines bl
3575: ,pa_resource_assignments ra
3576: where ra.resource_assignment_id = p_res_ass_id
3577: and bl.resource_assignment_id = ra.resource_assignment_id
3578: and bl.txn_currency_code = p_txn_currency_code
3752:
3753: /* clear out all the budget lines prior to ETC start date where actuals donot exists
3754: */
3755: CURSOR cur_bl_closed_period is
3756: SELECT /*+ INDEX(BL PA_BUDGET_LINES_U1) */
3757: bl.budget_line_id
3758: ,bl.resource_assignment_id
3759: ,bl.txn_currency_code
3760: ,bl.start_date
3776: ,bl.revenue_rejection_code
3777: ,bl.burden_rejection_code
3778: ,bl.pfc_cur_conv_rejection_code
3779: ,bl.pc_cur_conv_rejection_code
3780: FROM pa_budget_lines bl
3781: ,pa_fp_spread_calc_tmp tmp
3782: WHERE tmp.budget_version_id = p_budget_version_id
3783: AND bl.resource_assignment_id = tmp.resource_assignment_id
3784: AND bl.txn_currency_code = tmp.txn_currency_code
3798: /* check any of the actual lines got created before the etc start date
3799: * is having etc zero or not. If etc is not zero then throw an error
3800: */
3801: CURSOR cur_bl_corrupted is
3802: SELECT /*+ INDEX(BL PA_BUDGET_LINES_U1) */
3803: bl.budget_line_id
3804: ,bl.resource_assignment_id
3805: ,bl.txn_currency_code
3806: ,bl.start_date
3823: ,bl.revenue_rejection_code
3824: ,bl.burden_rejection_code
3825: ,bl.pfc_cur_conv_rejection_code
3826: ,bl.pc_cur_conv_rejection_code
3827: FROM pa_budget_lines bl
3828: ,pa_fp_spread_calc_tmp tmp
3829: WHERE tmp.budget_version_id = p_budget_version_id
3830: AND bl.resource_assignment_id = tmp.resource_assignment_id
3831: AND bl.txn_currency_code = tmp.txn_currency_code
3846: * transactions where actual qty <> to acutal raw cost. IE. while receiving actuals precedence rules were not
3847: * applied or not maintained
3848: */
3849: CURSOR cur_NonRatebase_corrupted_bls is
3850: SELECT /*+ INDEX(BL PA_BUDGET_LINES_U1) */
3851: bl.budget_line_id
3852: ,bl.resource_assignment_id
3853: ,bl.txn_currency_code
3854: ,bl.start_date
3871: ,bl.revenue_rejection_code
3872: ,bl.burden_rejection_code
3873: ,bl.pfc_cur_conv_rejection_code
3874: ,bl.pc_cur_conv_rejection_code
3875: FROM pa_budget_lines bl
3876: ,pa_resource_assignments ra
3877: ,pa_fp_spread_calc_tmp tmp
3878: WHERE tmp.budget_version_id = p_budget_version_id
3879: AND bl.resource_assignment_id = tmp.resource_assignment_id
4138: /** As discussed with Sanjay and Neeraj, Not to keep etc zero lines
4139: * this may cause to pick the wrong currency during the refresh rates
4140: * so delete all the budget lines instead of updating it to null
4141: **/
4142: DELETE FROM pa_budget_lines bl
4143: WHERE bl.budget_line_id = l_budget_Line_id_tab(i);
4144: --print_msg('Number of rows deleted from budgetLine['||l_budget_Line_id_tab.count||']');
4145:
4146: END If;
4264: --Correct all the budget lines prior to ETC start date. make plan = atcual and set all the currency conv attributes to NULL
4265: --print_msg('Updating closed period budget lines and correcting the plan amounts');
4266: l_corrupted_bl_update_rows := 0;
4267: FORALL i IN l_budget_Line_id_tab.FIRST .. l_budget_Line_id_tab.LAST
4268: UPDATE pa_budget_lines bl
4269: SET bl.quantity = bl.init_quantity
4270: ,bl.txn_raw_cost = bl.txn_init_raw_cost
4271: ,bl.txn_burdened_cost = bl.txn_init_burdened_cost
4272: ,bl.txn_revenue = bl.txn_init_revenue
4422: ,l_upd_revenue_rejection_tab
4423: ,l_upd_burden_rejection_tab
4424: ,l_upd_pfc_cur_conv_rej_tab
4425: ,l_upd_pc_cur_conv_rej_tab
4426: FROM pa_budget_lines bl
4427: ,pa_fp_spread_calc_tmp tmp
4428: WHERE bl.budget_version_id = p_budget_version_id
4429: AND tmp.budget_version_id = bl.budget_version_id
4430: AND bl.resource_assignment_id = tmp.resource_assignment_id
4662: * as we delete all the budget lines before the spread call. The purpose of thei API is to retain the override rates and
4663: * currency conversion attributes of the periodic line level on the budget lines. updates the rollup tmp table with the override rates
4664: */
4665: PROCEDURE Update_rollupTmp_OvrRates
4666: ( p_budget_version_id IN pa_budget_lines.budget_version_id%type
4667: ,p_calling_module IN varchar2
4668: ,p_generation_context IN varchar2 default 'SPREAD'
4669: ,x_return_status OUT NOCOPY VARCHAR2
4670: ,x_msg_count OUT NOCOPY NUMBER
4979: and then it deletes the budget lines only if there are no actuals exists.
4980: and the budget start date is greater than ETC start date
4981: */
4982: PROCEDURE delete_budget_lines
4983: ( p_budget_version_id IN pa_budget_lines.budget_version_id%type
4984: ,p_resource_assignment_id IN pa_resource_assignments.resource_assignment_id%TYPE
4985: ,p_txn_currency_code IN pa_budget_lines.txn_currency_code%TYPE
4986: ,p_line_start_date IN pa_budget_lines.start_date%TYPE
4987: ,p_line_end_date IN pa_budget_lines.end_date%TYPE
4981: */
4982: PROCEDURE delete_budget_lines
4983: ( p_budget_version_id IN pa_budget_lines.budget_version_id%type
4984: ,p_resource_assignment_id IN pa_resource_assignments.resource_assignment_id%TYPE
4985: ,p_txn_currency_code IN pa_budget_lines.txn_currency_code%TYPE
4986: ,p_line_start_date IN pa_budget_lines.start_date%TYPE
4987: ,p_line_end_date IN pa_budget_lines.end_date%TYPE
4988: ,p_source_context IN varchar2
4989: ,x_return_status OUT NOCOPY VARCHAR2
4982: PROCEDURE delete_budget_lines
4983: ( p_budget_version_id IN pa_budget_lines.budget_version_id%type
4984: ,p_resource_assignment_id IN pa_resource_assignments.resource_assignment_id%TYPE
4985: ,p_txn_currency_code IN pa_budget_lines.txn_currency_code%TYPE
4986: ,p_line_start_date IN pa_budget_lines.start_date%TYPE
4987: ,p_line_end_date IN pa_budget_lines.end_date%TYPE
4988: ,p_source_context IN varchar2
4989: ,x_return_status OUT NOCOPY VARCHAR2
4990: ,x_msg_count OUT NOCOPY NUMBER
4983: ( p_budget_version_id IN pa_budget_lines.budget_version_id%type
4984: ,p_resource_assignment_id IN pa_resource_assignments.resource_assignment_id%TYPE
4985: ,p_txn_currency_code IN pa_budget_lines.txn_currency_code%TYPE
4986: ,p_line_start_date IN pa_budget_lines.start_date%TYPE
4987: ,p_line_end_date IN pa_budget_lines.end_date%TYPE
4988: ,p_source_context IN varchar2
4989: ,x_return_status OUT NOCOPY VARCHAR2
4990: ,x_msg_count OUT NOCOPY NUMBER
4991: ,x_msg_data OUT NOCOPY VARCHAR2
5021: ,bl.revenue_rejection_code
5022: ,bl.burden_rejection_code
5023: ,bl.pfc_cur_conv_rejection_code
5024: ,bl.pc_cur_conv_rejection_code
5025: FROM pa_budget_lines bl
5026: WHERE bl.resource_assignment_id = p_resource_assignment_id
5027: AND bl.txn_currency_code = p_txn_currency_code
5028: AND ((p_line_start_date is NULL AND p_line_end_date is NULL )
5029: OR (p_line_start_date is NOT NULL AND p_line_end_date is NOT NULL
5081: ,bl.project_init_revenue
5082: ,bl.init_raw_cost
5083: ,bl.init_burdened_cost
5084: ,bl.init_revenue
5085: FROM pa_budget_lines bl
5086: WHERE bl.resource_assignment_id = p_resource_assignment_id
5087: AND bl.txn_currency_code = p_txn_currency_code
5088: AND ((p_line_start_date is NULL AND p_line_end_date is NULL )
5089: OR (p_line_start_date is NOT NULL AND p_line_end_date is NOT NULL
5258: IF l_budget_Line_id_tab.COUNT > 0 Then
5259: g_stage := 'delete_budget_lines:102';
5260: --print_msg('7820.1 Deleteing budget lines');
5261: FORALL i IN l_budget_Line_id_tab.FIRST .. l_budget_Line_id_tab.LAST
5262: DELETE FROM pa_budget_lines bl
5263: WHERE bl.budget_line_id = l_budget_Line_id_tab(i) ;
5264: x_num_rowsdeleted := sql%rowcount;
5265: --print_msg('Num Of Rows Deleted from BdugetLines['||x_num_rowsdeleted||']');
5266: END IF;
5393: If NVL(l_return_status,'S') = 'S' Then
5394: IF l_budget_Line_id_tab.COUNT > 0 Then
5395: g_stage := 'delete_budget_lines:105';
5396: FORALL i IN l_budget_Line_id_tab.FIRST .. l_budget_Line_id_tab.LAST
5397: UPDATE pa_budget_lines bl
5398: SET bl.quantity = l_init_quantity_tab(i)
5399: ,bl.txn_raw_cost = l_txn_init_raw_cost_tab(i)
5400: ,bl.txn_burdened_cost = l_txn_init_burdened_cost_tab(i)
5401: ,bl.txn_revenue = l_txn_init_revenue_tab(i)
5607: ,p_job_id IN pa_resource_assignments.job_id%TYPE
5608: ,p_resource_class IN pa_resource_assignments.resource_class_code%TYPE
5609: ,p_rate_based_flag IN pa_resource_assignments.rate_based_flag%TYPE
5610: ,p_uom IN pa_resource_assignments.unit_of_measure%TYPE
5611: ,p_quantity IN pa_budget_lines.quantity%TYPE
5612: ,p_item_date IN pa_budget_lines.start_date%TYPE
5613: ,p_non_labor_resource IN pa_resource_assignments.non_labor_resource%TYPE
5614: ,p_expenditure_org_id IN pa_resource_assignments.rate_expenditure_org_id%TYPE
5615: ,p_nlr_organization_id IN pa_resource_assignments.organization_id%TYPE
5608: ,p_resource_class IN pa_resource_assignments.resource_class_code%TYPE
5609: ,p_rate_based_flag IN pa_resource_assignments.rate_based_flag%TYPE
5610: ,p_uom IN pa_resource_assignments.unit_of_measure%TYPE
5611: ,p_quantity IN pa_budget_lines.quantity%TYPE
5612: ,p_item_date IN pa_budget_lines.start_date%TYPE
5613: ,p_non_labor_resource IN pa_resource_assignments.non_labor_resource%TYPE
5614: ,p_expenditure_org_id IN pa_resource_assignments.rate_expenditure_org_id%TYPE
5615: ,p_nlr_organization_id IN pa_resource_assignments.organization_id%TYPE
5616: ,p_cost_override_rate IN pa_fp_res_assignments_tmp.rw_cost_rate_override%TYPE
5804: ,billable_flag
5805: ,rate_based_flag
5806: ,system_reference4 -- version all revenue only entered
5807: )
5808: ( SELECT /*+ INDEX(BL PA_BUDGET_LINES_U1) */
5809: bl.budget_version_id
5810: ,bl.resource_assignment_id
5811: ,bl.start_date
5812: ,bl.end_date
5815: /* Bug Fix 4332086
5816: When ever currency is overridden in the workplan flow the exchange rates of old currency
5817: are used to derive the PFC amounts.
5818: This is happening due to the following piece of code.
5819: In the following code we are storing the pa_budget_lines attributes in pa_fp_rollup_tmp
5820: and then we are using the same in the later part of the code.
5821: This is resulting in the bug.
5822:
5823: As a fix now we added another decode condition to see if the currency is iverridden.
6086: ,decode(g_mass_adjust_flag,'N','N'
6087: ,decode(NVL(g_wp_version_flag,'N'),'Y','N'
6088: ,decode(nvl(ra.rate_based_flag,'N'),'Y','N'
6089: ,'N',decode(bl.txn_cost_rate_override,0,'Y','N'))))
6090: FROM pa_budget_lines bl
6091: ,pa_resource_assignments ra
6092: ,pa_fp_spread_calc_tmp tmp
6093: WHERE tmp.budget_version_id = p_budget_version_id
6094: AND (NVL(tmp.refresh_rates_flag,'N') in ('Y','R','C')
6729: * is use EXISTS clause or get distinct RAIDs from tmp table and hit budget lines in a loop
6730: */
6731:
6732: CURSOR rollup_bl_csr is
6733: SELECT /*+ INDEX(PBL PA_BUDGET_LINES_U1) */
6734: pbl.resource_assignment_id
6735: ,DECODE(sum(pbl.quantity),0,NULL,sum(pbl.quantity)) pfc_quantity
6736: ,DECODE(sum(pbl.raw_cost),0,NULL,sum(pbl.raw_cost)) pfc_raw_cost
6737: ,DECODE(sum(pbl.burdened_cost),0,NULL,sum(pbl.burdened_cost)) pfc_burdened_cost
6745: ,DECODE(sum(pbl.init_revenue),0,NULL,sum(pbl.init_revenue)) pfc_init_revenue
6746: ,DECODE(sum(pbl.project_init_raw_cost),0,NULL,sum(pbl.project_init_raw_cost)) project_init_raw_cost
6747: ,DECODE(sum(pbl.project_init_burdened_cost),0,NULL,sum(pbl.project_init_burdened_cost)) project_init_burdened_cost
6748: ,DECODE(sum(pbl.project_init_revenue),0,NULL,sum(pbl.project_init_revenue)) project_init_revenue
6749: FROM pa_budget_lines pbl
6750: WHERE pbl.budget_version_id = p_budget_version_id
6751: AND EXISTS (SELECT NULL
6752: FROM pa_fp_spread_calc_tmp tmp
6753: WHERE tmp.budget_version_id = p_budget_version_id
7150:
7151: PROCEDURE convert_ra_txn_currency
7152: ( p_budget_version_id IN pa_budget_versions.budget_version_id%TYPE
7153: ,p_resource_assignment_id IN pa_resource_assignments.resource_assignment_id%TYPE
7154: ,p_txn_currency_code IN pa_budget_lines.txn_currency_code%TYPE
7155: ,p_budget_line_id IN pa_budget_lines.budget_line_id%TYPE
7156: ,p_txn_raw_cost IN pa_fp_rollup_tmp.txn_raw_cost%TYPE
7157: ,p_txn_burdened_cost IN pa_fp_rollup_tmp.txn_burdened_cost%TYPE
7158: ,p_txn_revenue IN pa_fp_rollup_tmp.txn_revenue%TYPE
7151: PROCEDURE convert_ra_txn_currency
7152: ( p_budget_version_id IN pa_budget_versions.budget_version_id%TYPE
7153: ,p_resource_assignment_id IN pa_resource_assignments.resource_assignment_id%TYPE
7154: ,p_txn_currency_code IN pa_budget_lines.txn_currency_code%TYPE
7155: ,p_budget_line_id IN pa_budget_lines.budget_line_id%TYPE
7156: ,p_txn_raw_cost IN pa_fp_rollup_tmp.txn_raw_cost%TYPE
7157: ,p_txn_burdened_cost IN pa_fp_rollup_tmp.txn_burdened_cost%TYPE
7158: ,p_txn_revenue IN pa_fp_rollup_tmp.txn_revenue%TYPE
7159: ,x_projfunc_currency_code OUT NOCOPY pa_fp_rollup_tmp.projfunc_currency_code%TYPE
7523: select 'Y'
7524: from dual
7525: where EXISTS
7526: (select null
7527: from pa_budget_lines bl
7528: where bl.resource_assignment_id = p_res_ass_id
7529: and (bl.init_quantity is not null
7530: or bl.txn_init_raw_cost is not null
7531: or bl.txn_init_burdened_cost is not null
7563: ,pa_resource_assignments ra
7564: where tmp1.budget_version_id = p_budget_version_id
7565: and ra.resource_assignment_id = tmp1.resource_assignment_id
7566: and NVL(ra.rate_based_flag,'N') = 'N'
7567: and NOT EXISTS ( select /*+ INDEX(BL PA_BUDGET_LINES_U1) */ null
7568: from pa_budget_lines bl
7569: where bl.resource_assignment_id = tmp1.resource_assignment_id
7570: and bl.txn_currency_code = tmp1.txn_currency_code
7571: )
7564: where tmp1.budget_version_id = p_budget_version_id
7565: and ra.resource_assignment_id = tmp1.resource_assignment_id
7566: and NVL(ra.rate_based_flag,'N') = 'N'
7567: and NOT EXISTS ( select /*+ INDEX(BL PA_BUDGET_LINES_U1) */ null
7568: from pa_budget_lines bl
7569: where bl.resource_assignment_id = tmp1.resource_assignment_id
7570: and bl.txn_currency_code = tmp1.txn_currency_code
7571: )
7572: group by tmp1.resource_assignment_id
7603: l_period_name_tab.delete;
7604: l_proj_cur_code_tab.delete;
7605: l_projfunc_cur_code_tab.delete;
7606: FORALL i IN p_resource_assignment_tab.FIRST .. p_resource_assignment_tab.LAST
7607: DELETE FROM pa_budget_lines bl
7608: WHERE bl.resource_assignment_id = p_resource_assignment_tab(i)
7609: AND bl.budget_version_id = p_budget_version_id
7610: AND ( nvl(bl.quantity,0) = 0
7611: and nvl(bl.txn_raw_cost,0) = 0
7705: DELETE FROM pa_resource_asgn_curr rbc
7706: WHERE rbc.budget_version_id = p_budget_version_id
7707: AND rbc.resource_assignment_id = p_resource_assignment_tab(i)
7708: AND EXISTS ( SELECT null
7709: FROM pa_budget_lines bl
7710: WHERE bl.resource_assignment_id = rbc.resource_assignment_id
7711: AND bl.txn_currency_code <> rbc.txn_currency_code )
7712: AND rbc.total_quantity is NULL
7713: AND rbc.total_init_quantity is NULL
7948: l_burd_rt_miss_num_flag VARCHAR2(1) := NULL;
7949: l_bill_rt_miss_num_flag VARCHAR2(1) := NULL;
7950:
7951:
7952: --pa_budget_lines local variables---
7953: l_budget_line_id pa_budget_lines.budget_line_id%TYPE;
7954: l_bl_quantity pa_budget_lines.quantity%TYPE;
7955: l_bl_raw_cost_rate pa_budget_lines.txn_standard_cost_rate%TYPE;
7956: l_bl_standard_cost_rate pa_budget_lines.txn_standard_cost_rate%TYPE;
7949: l_bill_rt_miss_num_flag VARCHAR2(1) := NULL;
7950:
7951:
7952: --pa_budget_lines local variables---
7953: l_budget_line_id pa_budget_lines.budget_line_id%TYPE;
7954: l_bl_quantity pa_budget_lines.quantity%TYPE;
7955: l_bl_raw_cost_rate pa_budget_lines.txn_standard_cost_rate%TYPE;
7956: l_bl_standard_cost_rate pa_budget_lines.txn_standard_cost_rate%TYPE;
7957: l_bl_cost_rate_override pa_budget_lines.txn_cost_rate_override%TYPE;
7950:
7951:
7952: --pa_budget_lines local variables---
7953: l_budget_line_id pa_budget_lines.budget_line_id%TYPE;
7954: l_bl_quantity pa_budget_lines.quantity%TYPE;
7955: l_bl_raw_cost_rate pa_budget_lines.txn_standard_cost_rate%TYPE;
7956: l_bl_standard_cost_rate pa_budget_lines.txn_standard_cost_rate%TYPE;
7957: l_bl_cost_rate_override pa_budget_lines.txn_cost_rate_override%TYPE;
7958: l_bl_raw_cost pa_budget_lines.txn_raw_cost%TYPE;
7951:
7952: --pa_budget_lines local variables---
7953: l_budget_line_id pa_budget_lines.budget_line_id%TYPE;
7954: l_bl_quantity pa_budget_lines.quantity%TYPE;
7955: l_bl_raw_cost_rate pa_budget_lines.txn_standard_cost_rate%TYPE;
7956: l_bl_standard_cost_rate pa_budget_lines.txn_standard_cost_rate%TYPE;
7957: l_bl_cost_rate_override pa_budget_lines.txn_cost_rate_override%TYPE;
7958: l_bl_raw_cost pa_budget_lines.txn_raw_cost%TYPE;
7959: l_bl_avg_burden_cost_rate pa_budget_lines.burden_cost_rate%TYPE;
7952: --pa_budget_lines local variables---
7953: l_budget_line_id pa_budget_lines.budget_line_id%TYPE;
7954: l_bl_quantity pa_budget_lines.quantity%TYPE;
7955: l_bl_raw_cost_rate pa_budget_lines.txn_standard_cost_rate%TYPE;
7956: l_bl_standard_cost_rate pa_budget_lines.txn_standard_cost_rate%TYPE;
7957: l_bl_cost_rate_override pa_budget_lines.txn_cost_rate_override%TYPE;
7958: l_bl_raw_cost pa_budget_lines.txn_raw_cost%TYPE;
7959: l_bl_avg_burden_cost_rate pa_budget_lines.burden_cost_rate%TYPE;
7960: l_bl_burden_cost_rate pa_budget_lines.burden_cost_rate%TYPE;
7953: l_budget_line_id pa_budget_lines.budget_line_id%TYPE;
7954: l_bl_quantity pa_budget_lines.quantity%TYPE;
7955: l_bl_raw_cost_rate pa_budget_lines.txn_standard_cost_rate%TYPE;
7956: l_bl_standard_cost_rate pa_budget_lines.txn_standard_cost_rate%TYPE;
7957: l_bl_cost_rate_override pa_budget_lines.txn_cost_rate_override%TYPE;
7958: l_bl_raw_cost pa_budget_lines.txn_raw_cost%TYPE;
7959: l_bl_avg_burden_cost_rate pa_budget_lines.burden_cost_rate%TYPE;
7960: l_bl_burden_cost_rate pa_budget_lines.burden_cost_rate%TYPE;
7961: l_bl_burden_cost_rate_override pa_budget_lines.burden_cost_rate_override%TYPE;
7954: l_bl_quantity pa_budget_lines.quantity%TYPE;
7955: l_bl_raw_cost_rate pa_budget_lines.txn_standard_cost_rate%TYPE;
7956: l_bl_standard_cost_rate pa_budget_lines.txn_standard_cost_rate%TYPE;
7957: l_bl_cost_rate_override pa_budget_lines.txn_cost_rate_override%TYPE;
7958: l_bl_raw_cost pa_budget_lines.txn_raw_cost%TYPE;
7959: l_bl_avg_burden_cost_rate pa_budget_lines.burden_cost_rate%TYPE;
7960: l_bl_burden_cost_rate pa_budget_lines.burden_cost_rate%TYPE;
7961: l_bl_burden_cost_rate_override pa_budget_lines.burden_cost_rate_override%TYPE;
7962: l_bl_burdened_cost pa_budget_lines.txn_burdened_cost%TYPE;
7955: l_bl_raw_cost_rate pa_budget_lines.txn_standard_cost_rate%TYPE;
7956: l_bl_standard_cost_rate pa_budget_lines.txn_standard_cost_rate%TYPE;
7957: l_bl_cost_rate_override pa_budget_lines.txn_cost_rate_override%TYPE;
7958: l_bl_raw_cost pa_budget_lines.txn_raw_cost%TYPE;
7959: l_bl_avg_burden_cost_rate pa_budget_lines.burden_cost_rate%TYPE;
7960: l_bl_burden_cost_rate pa_budget_lines.burden_cost_rate%TYPE;
7961: l_bl_burden_cost_rate_override pa_budget_lines.burden_cost_rate_override%TYPE;
7962: l_bl_burdened_cost pa_budget_lines.txn_burdened_cost%TYPE;
7963: l_bl_bill_rate pa_budget_lines.txn_standard_bill_rate%TYPE;
7956: l_bl_standard_cost_rate pa_budget_lines.txn_standard_cost_rate%TYPE;
7957: l_bl_cost_rate_override pa_budget_lines.txn_cost_rate_override%TYPE;
7958: l_bl_raw_cost pa_budget_lines.txn_raw_cost%TYPE;
7959: l_bl_avg_burden_cost_rate pa_budget_lines.burden_cost_rate%TYPE;
7960: l_bl_burden_cost_rate pa_budget_lines.burden_cost_rate%TYPE;
7961: l_bl_burden_cost_rate_override pa_budget_lines.burden_cost_rate_override%TYPE;
7962: l_bl_burdened_cost pa_budget_lines.txn_burdened_cost%TYPE;
7963: l_bl_bill_rate pa_budget_lines.txn_standard_bill_rate%TYPE;
7964: l_bl_standard_bill_rate pa_budget_lines.txn_standard_bill_rate%TYPE;
7957: l_bl_cost_rate_override pa_budget_lines.txn_cost_rate_override%TYPE;
7958: l_bl_raw_cost pa_budget_lines.txn_raw_cost%TYPE;
7959: l_bl_avg_burden_cost_rate pa_budget_lines.burden_cost_rate%TYPE;
7960: l_bl_burden_cost_rate pa_budget_lines.burden_cost_rate%TYPE;
7961: l_bl_burden_cost_rate_override pa_budget_lines.burden_cost_rate_override%TYPE;
7962: l_bl_burdened_cost pa_budget_lines.txn_burdened_cost%TYPE;
7963: l_bl_bill_rate pa_budget_lines.txn_standard_bill_rate%TYPE;
7964: l_bl_standard_bill_rate pa_budget_lines.txn_standard_bill_rate%TYPE;
7965: l_bl_bill_rate_override pa_budget_lines.txn_bill_rate_override%TYPE;
7958: l_bl_raw_cost pa_budget_lines.txn_raw_cost%TYPE;
7959: l_bl_avg_burden_cost_rate pa_budget_lines.burden_cost_rate%TYPE;
7960: l_bl_burden_cost_rate pa_budget_lines.burden_cost_rate%TYPE;
7961: l_bl_burden_cost_rate_override pa_budget_lines.burden_cost_rate_override%TYPE;
7962: l_bl_burdened_cost pa_budget_lines.txn_burdened_cost%TYPE;
7963: l_bl_bill_rate pa_budget_lines.txn_standard_bill_rate%TYPE;
7964: l_bl_standard_bill_rate pa_budget_lines.txn_standard_bill_rate%TYPE;
7965: l_bl_bill_rate_override pa_budget_lines.txn_bill_rate_override%TYPE;
7966: l_bl_revenue pa_budget_lines.txn_revenue%TYPE;
7959: l_bl_avg_burden_cost_rate pa_budget_lines.burden_cost_rate%TYPE;
7960: l_bl_burden_cost_rate pa_budget_lines.burden_cost_rate%TYPE;
7961: l_bl_burden_cost_rate_override pa_budget_lines.burden_cost_rate_override%TYPE;
7962: l_bl_burdened_cost pa_budget_lines.txn_burdened_cost%TYPE;
7963: l_bl_bill_rate pa_budget_lines.txn_standard_bill_rate%TYPE;
7964: l_bl_standard_bill_rate pa_budget_lines.txn_standard_bill_rate%TYPE;
7965: l_bl_bill_rate_override pa_budget_lines.txn_bill_rate_override%TYPE;
7966: l_bl_revenue pa_budget_lines.txn_revenue%TYPE;
7967:
7960: l_bl_burden_cost_rate pa_budget_lines.burden_cost_rate%TYPE;
7961: l_bl_burden_cost_rate_override pa_budget_lines.burden_cost_rate_override%TYPE;
7962: l_bl_burdened_cost pa_budget_lines.txn_burdened_cost%TYPE;
7963: l_bl_bill_rate pa_budget_lines.txn_standard_bill_rate%TYPE;
7964: l_bl_standard_bill_rate pa_budget_lines.txn_standard_bill_rate%TYPE;
7965: l_bl_bill_rate_override pa_budget_lines.txn_bill_rate_override%TYPE;
7966: l_bl_revenue pa_budget_lines.txn_revenue%TYPE;
7967:
7968: l_actual_exists_flag VARCHAR2(60);
7961: l_bl_burden_cost_rate_override pa_budget_lines.burden_cost_rate_override%TYPE;
7962: l_bl_burdened_cost pa_budget_lines.txn_burdened_cost%TYPE;
7963: l_bl_bill_rate pa_budget_lines.txn_standard_bill_rate%TYPE;
7964: l_bl_standard_bill_rate pa_budget_lines.txn_standard_bill_rate%TYPE;
7965: l_bl_bill_rate_override pa_budget_lines.txn_bill_rate_override%TYPE;
7966: l_bl_revenue pa_budget_lines.txn_revenue%TYPE;
7967:
7968: l_actual_exists_flag VARCHAR2(60);
7969: l_resAttribChangeFlag VARCHAR2(1) := 'N';
7962: l_bl_burdened_cost pa_budget_lines.txn_burdened_cost%TYPE;
7963: l_bl_bill_rate pa_budget_lines.txn_standard_bill_rate%TYPE;
7964: l_bl_standard_bill_rate pa_budget_lines.txn_standard_bill_rate%TYPE;
7965: l_bl_bill_rate_override pa_budget_lines.txn_bill_rate_override%TYPE;
7966: l_bl_revenue pa_budget_lines.txn_revenue%TYPE;
7967:
7968: l_actual_exists_flag VARCHAR2(60);
7969: l_resAttribChangeFlag VARCHAR2(1) := 'N';
7970: l_multicur_plan_start_date DATE;
7975:
7976: CURSOR get_p_res_asn_curr_code IS
7977: SELECT resource_assignment_id
7978: ,txn_currency_code
7979: FROM pa_budget_lines bl
7980: WHERE bl.budget_version_id = p_budget_version_id
7981: GROUP BY resource_assignment_id,txn_currency_code;
7982:
7983: CURSOR get_proj_fp_options_csr IS
8011:
8012:
8013: CURSOR get_bl_currency IS
8014: SELECT txn_currency_code
8015: FROM pa_budget_lines
8016: WHERE resource_assignment_id = l_resource_assignment_id
8017: ORDER BY start_date;
8018:
8019: CURSOR get_resource_asn_csr (p_resource_asg_id IN NUMBER) IS
8164: l_pls_end_time Number;
8165:
8166: -- Start for Bug# 6781055
8167: l_rec_modified_in_cl_flag VARCHAR2(1);
8168: l_cl_init_quantity pa_budget_lines.init_quantity%TYPE;
8169: l_cl_init_raw_cost pa_budget_lines.txn_init_raw_cost%TYPE;
8170: l_cl_init_burd_cost pa_budget_lines.txn_init_burdened_cost%TYPE;
8171: l_cl_init_revenue pa_budget_lines.txn_init_revenue%TYPE;
8172: -- End for Bug# 6781055
8165:
8166: -- Start for Bug# 6781055
8167: l_rec_modified_in_cl_flag VARCHAR2(1);
8168: l_cl_init_quantity pa_budget_lines.init_quantity%TYPE;
8169: l_cl_init_raw_cost pa_budget_lines.txn_init_raw_cost%TYPE;
8170: l_cl_init_burd_cost pa_budget_lines.txn_init_burdened_cost%TYPE;
8171: l_cl_init_revenue pa_budget_lines.txn_init_revenue%TYPE;
8172: -- End for Bug# 6781055
8173:
8166: -- Start for Bug# 6781055
8167: l_rec_modified_in_cl_flag VARCHAR2(1);
8168: l_cl_init_quantity pa_budget_lines.init_quantity%TYPE;
8169: l_cl_init_raw_cost pa_budget_lines.txn_init_raw_cost%TYPE;
8170: l_cl_init_burd_cost pa_budget_lines.txn_init_burdened_cost%TYPE;
8171: l_cl_init_revenue pa_budget_lines.txn_init_revenue%TYPE;
8172: -- End for Bug# 6781055
8173:
8174: BEGIN
8167: l_rec_modified_in_cl_flag VARCHAR2(1);
8168: l_cl_init_quantity pa_budget_lines.init_quantity%TYPE;
8169: l_cl_init_raw_cost pa_budget_lines.txn_init_raw_cost%TYPE;
8170: l_cl_init_burd_cost pa_budget_lines.txn_init_burdened_cost%TYPE;
8171: l_cl_init_revenue pa_budget_lines.txn_init_revenue%TYPE;
8172: -- End for Bug# 6781055
8173:
8174: BEGIN
8175: g_ipm_ra_id_tab.delete;
9344: l_stage := 276;
9345: --print_msg(to_char(l_stage)||'CHK if l_delete_budget_lines ['||l_delete_budget_lines||']');
9346: IF l_delete_budget_lines = 'Y' THEN
9347: If P_PA_DEBUG_MODE = 'Y' Then
9348: print_msg(to_char(l_stage)||' Delete from pa_budget_lines, rollup pfc numbers, and skip record');
9349: End if;
9350: pa_fp_calc_plan_pkg.delete_budget_lines
9351: (p_budget_version_id => p_budget_version_id
9352: ,p_resource_assignment_id => l_resource_assignment_id
9625: INTO l_curr_burden_rate
9626: ,l_curr_bill_rate
9627: ,l_curr_markup_percentage
9628: ,l_curr_cost_rate
9629: FROM pa_budget_lines bl
9630: WHERE bl.resource_assignment_id = l_resource_assignment_id
9631: AND bl.txn_currency_code = l_txn_currency_code
9632: AND bl.start_date between l_bdgt_line_sDate and l_bdgt_line_eDate;
9633: If l_curr_markup_percentage is NULL Then
9819: End If;
9820:
9821: /*Corner case bug 6429285:
9822: If a rate based resource is added as non rate based resource assignment
9823: in pa_resource_asgn_curr and pa_budget_lines quantity will be populated
9824: as raw_cost and display_quantity will be null.Now if we want to enter
9825: the quantity same as raw cost (i.e existing quantity)the display_quantity will never be populated.
9826: Also in this case populating the display_quantity is not enough we need to
9827: give precedence to quantity and re-populate the raw cost,burden cost
10866: END IF;
10867:
10868: /*
10869: *Now the global temporary table pa_fp_rollup_tmp is completely populated and the
10870: *table pa_budget_lines is ready to be updated with the values stored in pa_fp_rollup_tmp
10871: *It will update pa_budget_lines with ALL rows stored in pa_fp_rollup_tmp
10872: */
10873: l_stage := 1100;
10874: /* Bug fix: 4184159 moved to bulk update pa_fp_calc_plan_pkg.update_budget_lines */
10867:
10868: /*
10869: *Now the global temporary table pa_fp_rollup_tmp is completely populated and the
10870: *table pa_budget_lines is ready to be updated with the values stored in pa_fp_rollup_tmp
10871: *It will update pa_budget_lines with ALL rows stored in pa_fp_rollup_tmp
10872: */
10873: l_stage := 1100;
10874: /* Bug fix: 4184159 moved to bulk update pa_fp_calc_plan_pkg.update_budget_lines */
10875: l_pls_start_time := dbms_utility.get_time;
14319: /*
14320: Bug 6429285
14321: Below if condition will handle a corner case
14322: If a rate based resource is added as non rate based resource assignment
14323: in pa_resource_asgn_curr and pa_budget_lines quantity will be populated
14324: as raw_cost and display_quantity will be null.Now if we want to enter
14325: the quantity same as raw cost (i.e existing quantity) the existing if condition will fail because
14326: the user entered quantity is same as what is alreay existing
14327: This check is already performed in Compare_bdgtLine_Values*/
15645: ,project_init_burdened_cost
15646: ,project_init_revenue
15647: ,billable_flag
15648: )
15649: ( SELECT /*+ INDEX(BL PA_BUDGET_LINES_U1) */
15650: bl.budget_version_id
15651: ,bl.resource_assignment_id
15652: ,bl.start_date
15653: ,bl.end_date
15703: ,bl.project_init_raw_cost
15704: ,bl.project_init_burdened_cost
15705: ,bl.project_init_revenue
15706: ,tmp.billable_flag
15707: FROM pa_budget_lines bl
15708: ,pa_resource_assignments ra
15709: ,pa_fp_spread_calc_tmp tmp
15710: ,pa_fp_rollup_tmp rlp --Bug 5203868
15711: WHERE tmp.budget_version_id = p_budget_version_id