[Home] [Help]
170: CURSOR cur_check IS
171: SELECT 'Y'
172: FROM dual
173: WHERE EXISTS ( select null
174: from pa_budget_lines bl
175: where bl.budget_version_id = p_budget_version_id
176: and bl.resource_assignment_id = p_resAsgnId
177: GROUP BY bl.resource_assignment_id
178: HAVING COUNT(*) > 1
204:
205: /* bug fix:5726773: Added for supporting neg or zero quantity spread */
206:
207: Cursor cur_asgn_bl_sumchk IS
208: SELECT /*+ INDEX(BL PA_BUDGET_LINES_U1) */
209: sum(bl.quantity) bl_sum_quantity
210: ,sum(bl.init_quantity) bl_sum_act_quantity
211: FroM pa_budget_lines bl
212: where bl.resource_assignment_id = p_ra_id
207: Cursor cur_asgn_bl_sumchk IS
208: SELECT /*+ INDEX(BL PA_BUDGET_LINES_U1) */
209: sum(bl.quantity) bl_sum_quantity
210: ,sum(bl.init_quantity) bl_sum_act_quantity
211: FroM pa_budget_lines bl
212: where bl.resource_assignment_id = p_ra_id
213: and bl.txn_currency_code = p_txn_cur_code
214: and bl.budget_version_id = p_budget_version_id;
215:
214: and bl.budget_version_id = p_budget_version_id;
215:
216:
217: Cursor cur_periodic_bl_sumchk IS
218: SELECT /*+ INDEX(BL PA_BUDGET_LINES_U1) */
219: sum(bl.quantity) bl_sum_quantity
220: ,sum(bl.init_quantity) bl_sum_act_quantity
221: FroM pa_budget_lines bl
222: where bl.resource_assignment_id = p_ra_id
217: Cursor cur_periodic_bl_sumchk IS
218: SELECT /*+ INDEX(BL PA_BUDGET_LINES_U1) */
219: sum(bl.quantity) bl_sum_quantity
220: ,sum(bl.init_quantity) bl_sum_act_quantity
221: FroM pa_budget_lines bl
222: where bl.resource_assignment_id = p_ra_id
223: and bl.txn_currency_code = p_txn_cur_code
224: and bl.budget_version_id = p_budget_version_id
225: and bl.start_date between p_start_date and p_end_date;
263: WHERE tmp.budget_version_id = p_budget_version_id
264: AND ra.resource_assignment_id = tmp.resource_assignment_id
265: AND rl.resource_list_member_id = ra.resource_list_member_id
266: AND EXISTS (SELECT NULL
267: FROM pa_budget_lines bl
268: WHERE bl.resource_assignment_id = tmp.resource_assignment_id
269: AND bl.txn_currency_code = tmp.txn_currency_code
270: /* Bug fix: 4294902 :Check zero quantity only for the open periods */
271: AND NVL(g_etc_start_date,bl.start_date) BETWEEN bl.start_date and bl.end_date
292: * 3:NESTED LOOPS :(cost=21,rows=1)
293: * 4:HASH JOIN SEMI :(cost=20,rows=1)
294: * 5:TABLE ACCESS BY INDEX ROWID PA_FP_SPREAD_CALC_TMP :(cost=6,rows=82)
295: * 6:INDEX RANGE SCAN PA_FP_SPREAD_CALC_TMP_N2 :(cost=2,rows=33)
296: * 5:TABLE ACCESS BY INDEX ROWID PA_BUDGET_LINES :(cost=13,rows=8)
297: * 6:INDEX RANGE SCAN PA_BUDGET_LINES_N3 :(cost=2,rows=1)
298: * 4:TABLE ACCESS BY INDEX ROWID PA_RESOURCE_ASSIGNMENTS :(cost=1,rows=1)
299: * 5:INDEX UNIQUE SCAN PA_RESOURCE_ASSIGNMENTS_U1 :(cost=,rows=1)
300: * 3:TABLE ACCESS BY INDEX ROWID PA_RESOURCE_LIST_MEMBERS :(cost=1,rows=1)
293: * 4:HASH JOIN SEMI :(cost=20,rows=1)
294: * 5:TABLE ACCESS BY INDEX ROWID PA_FP_SPREAD_CALC_TMP :(cost=6,rows=82)
295: * 6:INDEX RANGE SCAN PA_FP_SPREAD_CALC_TMP_N2 :(cost=2,rows=33)
296: * 5:TABLE ACCESS BY INDEX ROWID PA_BUDGET_LINES :(cost=13,rows=8)
297: * 6:INDEX RANGE SCAN PA_BUDGET_LINES_N3 :(cost=2,rows=1)
298: * 4:TABLE ACCESS BY INDEX ROWID PA_RESOURCE_ASSIGNMENTS :(cost=1,rows=1)
299: * 5:INDEX UNIQUE SCAN PA_RESOURCE_ASSIGNMENTS_U1 :(cost=,rows=1)
300: * 3:TABLE ACCESS BY INDEX ROWID PA_RESOURCE_LIST_MEMBERS :(cost=1,rows=1)
301: * 4:INDEX UNIQUE SCAN PA_RESOURCE_LIST_MEMBERS_U1 :(cost=,rows=1)
309: ,pa_resource_list_members rl
310: WHERE tmp.budget_version_id = p_budget_version_id
311: AND ra.resource_assignment_id = tmp.resource_assignment_id
312: AND rl.resource_list_member_id = ra.resource_list_member_id
313: AND EXISTS (SELECT /*+ NO_UNNEST INDEX (BL,PA_BUDGET_LINES_U1) */ NULL -- Bug#4728472
314: FROM pa_budget_lines bl
315: WHERE bl.resource_assignment_id = tmp.resource_assignment_id
316: AND bl.txn_currency_code = tmp.txn_currency_code
317: AND bl.end_date < bl.start_date
310: WHERE tmp.budget_version_id = p_budget_version_id
311: AND ra.resource_assignment_id = tmp.resource_assignment_id
312: AND rl.resource_list_member_id = ra.resource_list_member_id
313: AND EXISTS (SELECT /*+ NO_UNNEST INDEX (BL,PA_BUDGET_LINES_U1) */ NULL -- Bug#4728472
314: FROM pa_budget_lines bl
315: WHERE bl.resource_assignment_id = tmp.resource_assignment_id
316: AND bl.txn_currency_code = tmp.txn_currency_code
317: AND bl.end_date < bl.start_date
318: /* Bug:4440255 : added the following conditions reduces the FTS on pa_budget_lines and cost will reduce from 50 to 22 */
314: FROM pa_budget_lines bl
315: WHERE bl.resource_assignment_id = tmp.resource_assignment_id
316: AND bl.txn_currency_code = tmp.txn_currency_code
317: AND bl.end_date < bl.start_date
318: /* Bug:4440255 : added the following conditions reduces the FTS on pa_budget_lines and cost will reduce from 50 to 22 */
319: AND bl.budget_version_id = tmp.budget_version_id
320: /* end of bug fix:4440255 */
321: );
322: BEGIN
1406:
1407: /* update table with txn markup percentage */
1408: UPDATE /*+ INDEX(TMP PA_FP_SPREAD_CALC_TMP2_N1) */ pa_fp_spread_calc_tmp2 tmp
1409: SET tmp.bill_markup_percentage = (select AVG(bl.txn_markup_percent)
1410: from pa_budget_lines bl
1411: where bl.resource_assignment_id = tmp.resource_assignment_id
1412: and bl.txn_currency_code = tmp.txn_currency_code
1413: )
1414: WHERE tmp.budget_version_id = p_budget_version_id
1412: and bl.txn_currency_code = tmp.txn_currency_code
1413: )
1414: WHERE tmp.budget_version_id = p_budget_version_id
1415: AND EXISTS ( select null
1416: from pa_budget_lines bl1
1417: where bl1.resource_assignment_id = tmp.resource_assignment_id
1418: and bl1.txn_currency_code = tmp.txn_currency_code
1419: );
1420:
1432: ,tmp.system_reference_var2 --avg_zero_null_burden_rate
1433: ,tmp.system_reference_var3 --avg_zero_null_bill_rate
1434: ,tmp.bill_markup_percentage
1435: ) =
1436: (SELECT /*+ INDEX(BLAVGRT PA_BUDGET_LINES_U1) */
1437: AVG(DECODE((nvl(blavgrt.quantity,0) - nvl(blavgrt.init_quantity,0)),0
1438: ,NULL,blavgrt.txn_cost_rate_override)) avg_txn_cost_rate_override
1439: ,AVG(DECODE((nvl(blavgrt.quantity,0) - nvl(blavgrt.init_quantity,0)),0
1440: ,NULL,blavgrt.burden_cost_rate_override)) avg_burden_cost_rate_override
1443: ,AVG(NVL(blavgrt.txn_cost_rate_override,blavgrt.txn_standard_cost_rate))
1444: ,AVG(NVL(blavgrt.burden_cost_rate_override,blavgrt.burden_cost_rate))
1445: ,AVG(NVL(blavgrt.txn_bill_rate_override,blavgrt.txn_standard_bill_rate))
1446: ,AVG(blavgrt.txn_markup_percent)
1447: FROM pa_budget_lines blavgrt
1448: WHERE blavgrt.budget_version_id = p_budget_version_id
1449: AND blavgrt.resource_assignment_id = tmp.resource_assignment_id
1450: AND blavgrt.txn_currency_code = tmp.txn_currency_code
1451: AND ( (tmp.start_date is NULL AND tmp.end_date is NULL )
1482: ,tmp.system_reference_num2 --txnRaw
1483: ,tmp.system_reference_num3 --txnburd
1484: ,tmp.system_reference_num4 --txnrev
1485: ) =
1486: ( SELECT /*+ INDEX(BL PA_BUDGET_LINES_U1) */
1487: decode(sum(bl.quantity),0,NULL,sum(bl.quantity)) --quantity
1488: ,decode(sum(bl.txn_raw_cost),0,NULL,sum(bl.txn_raw_cost)) --txn_raw_cost
1489: ,decode(sum(bl.txn_burdened_cost),0,NULL,sum(bl.txn_burdened_cost)) --txn_burdened_cost
1490: ,decode(sum(bl.txn_revenue),0,NULL,sum(bl.txn_revenue)) --txn_revenue
1579: ,SUM(tmp.quantity) -- bl_zero_null_quantity
1580: ,SUM(tmp.txn_raw_cost) -- bl_zero_null_rawcost
1581: ,SUM(tmp.txn_burdened_cost) -- bl_zero_null_burdencost
1582: ,SUM(tmp.txn_revenue) -- bl_zero_null_revenue
1583: FROM pa_budget_lines bl
1584: WHERE bl.budget_version_id = p_budget_version_id
1585: AND bl.resource_assignment_id = tmp.resource_assignment_id
1586: AND bl.txn_currency_code = tmp.txn_currency_code
1587: AND ( (tmp.start_date is NULL AND tmp.end_date is NULL )
1773: /*
1774: Bug 6429285
1775: Below if condition will handle a corner case
1776: If a rate based resource is added as non rate based resource assignment
1777: in pa_resource_asgn_curr and pa_budget_lines quantity will be populated
1778: as raw_cost and display_quantity will be null.Now if we want to enter
1779: the quantity same as raw cost (i.e existing quantity) the above if condition will fail because
1780: the user entered quantity is same as what is alreay existing*/
1781:
2289: WHERE rtx.resource_assignment_id = l_resource_assignment_tab(i)
2290: AND rtx.txn_currency_code = l_txn_currency_code_tab(i);
2291:
2292: FORALL i IN l_resource_assignment_tab.FIRST .. l_resource_assignment_tab.LAST
2293: UPDATE pa_budget_lines bl
2294: SET bl.txn_bill_rate_override = l_bill_rate_override_tab(i)
2295: ,bl.txn_cost_rate_override = l_cost_rate_override_tab(i)
2296: ,bl.burden_cost_rate_override = l_burden_rate_override_tab(i)
2297: WHERE bl.resource_assignment_id = l_resource_assignment_tab(i)
2377: AND NVL(tmp.RLM_ID_CHANGE_FLAG,'N') = 'N'
2378: */
2379: AND NVL(tmp.DELETE_BL_FLAG,'N') = 'N'
2380: AND EXISTS ( select null
2381: from pa_budget_lines bl
2382: where bl.resource_assignment_id = tmp.resource_assignment_id
2383: and bl.txn_currency_code = tmp.txn_currency_code
2384: and ((p_source_context = 'BUDGET_LINE'
2385: and bl.start_date between tmp.start_date and tmp.end_date)
2477: (sum(nvl(bl.txn_raw_cost,0) - nvl(bl.txn_init_raw_cost,0)))))
2478: ,decode(sum(nvl(bl.txn_revenue,0)-nvl(bl.txn_init_revenue,0)),0,rtx.txn_bill_rate_override
2479: ,(sum(nvl(bl.txn_revenue,0)-nvl(bl.txn_init_revenue,0)))/
2480: (sum(nvl(bl.txn_raw_cost,0) - nvl(bl.txn_init_raw_cost,0))))
2481: from pa_budget_lines bl
2482: where bl.resource_assignment_id = rtx.resource_assignment_id
2483: and bl.txn_currency_code = rtx.txn_currency_code
2484: )
2485: WHERE rtx.resource_assignment_id = l_resource_assignment_tab(i)
2484: )
2485: WHERE rtx.resource_assignment_id = l_resource_assignment_tab(i)
2486: AND rtx.txn_currency_code = l_txn_currency_code_tab(i)
2487: AND EXISTS (select null
2488: from pa_budget_lines bl1
2489: where bl1.resource_assignment_id = rtx.resource_assignment_id
2490: and bl1.txn_currency_code = rtx.txn_currency_code
2491: );
2492:
2497: FORALL i IN l_resource_assignment_tab.FIRST .. l_resource_assignment_tab.LAST
2498: UPDATE /*+ INDEX(TMP PA_FP_SPREAD_CALC_TMP_N1) */ pa_fp_spread_calc_tmp tmp
2499: SET tmp.quantity =
2500: (select decode(sum(bl.txn_raw_cost),NULL,sum(bl.txn_revenue),sum(bl.txn_raw_cost))
2501: from pa_budget_lines bl
2502: where bl.resource_assignment_id = tmp.resource_assignment_id
2503: and bl.txn_currency_code = tmp.txn_currency_code
2504: )
2505: WHERE tmp.resource_assignment_id = l_resource_assignment_tab(i)
2504: )
2505: WHERE tmp.resource_assignment_id = l_resource_assignment_tab(i)
2506: AND tmp.txn_currency_code = l_txn_currency_code_tab(i)
2507: AND EXISTS (select null
2508: from pa_budget_lines bl1
2509: where bl1.resource_assignment_id = tmp.resource_assignment_id
2510: and bl1.txn_currency_code = tmp.txn_currency_code
2511: );
2512:
2511: );
2512:
2513: --print_msg('1.5: update budget lines set override rates to null');
2514: FORALL i IN l_resource_assignment_tab.FIRST .. l_resource_assignment_tab.LAST
2515: UPDATE pa_budget_lines bl
2516: SET bl.txn_bill_rate_override = null
2517: ,bl.burden_cost_rate_override = null
2518: ,bl.txn_cost_rate_override = 1
2519: WHERE bl.resource_assignment_id = l_resource_assignment_tab(i)
2522:
2523: If l_bilRtSetFlag_Tab.count > 0 Then --{
2524: --print_msg('1.1.1: Update budget lines to set override rates');
2525: FORALL i IN l_resource_assignment_tab.FIRST .. l_resource_assignment_tab.LAST
2526: UPDATE pa_budget_lines bl
2527: SET bl.txn_bill_rate_override = l_bill_rate_override_tab(i)
2528: /* bug fix: 5089153 stamp bill rate override only if revenue is changed along with cost
2529: decode(bl.txn_cost_rate_override,0
2530: ,decode(bl.txn_bill_rate_override,1,l_bill_rate_override_tab(i)
2746: AND NVL(ra.resource_rate_based_flag,'N') = 'N'
2747: /* bug fix:5726773: commented out this and added exist clause
2748: * reason: when budget line exists and total plan qty is zero, the above cursor fails
2749: * AND tmp.quantity is not null */
2750: AND EXISTS (select /*+ INDEX(BL PA_BUDGET_LINES_U1) */ null
2751: from pa_budget_lines bl1
2752: where bl1.resource_assignment_id = tmp.resource_assignment_id
2753: and bl1.txn_currency_code = tmp.txn_currency_code
2754: and ((g_source_context = 'BUDGET_LINE'
2747: /* bug fix:5726773: commented out this and added exist clause
2748: * reason: when budget line exists and total plan qty is zero, the above cursor fails
2749: * AND tmp.quantity is not null */
2750: AND EXISTS (select /*+ INDEX(BL PA_BUDGET_LINES_U1) */ null
2751: from pa_budget_lines bl1
2752: where bl1.resource_assignment_id = tmp.resource_assignment_id
2753: and bl1.txn_currency_code = tmp.txn_currency_code
2754: and ((g_source_context = 'BUDGET_LINE'
2755: and bl1.start_date between tmp.start_date and tmp.end_date)
3277: AND tmp.resource_assignment_id = l_resource_assignment_tab(i)
3278: AND tmp.txn_currency_code = l_txn_currency_code_tab(i)
3279: AND l_reset_rate_based_flag_tab(i) = 'Y'
3280: AND EXISTS (select null
3281: from pa_budget_lines bl
3282: WHERE tmp.resource_assignment_id = bl.resource_assignment_id
3283: AND (tmp.txn_currency_code <> bl.txn_currency_code
3284: OR
3285: ( tmp.txn_currency_code = bl.txn_currency_code
3313: AND tmp.txn_currency_code = l_txn_currency_code_tab(i)
3314: AND tmp.start_date = l_start_date_tab(i)
3315: AND l_reset_rate_based_flag_tab(i) = 'Y'
3316: AND EXISTS (select null
3317: from pa_budget_lines bl
3318: WHERE tmp.resource_assignment_id = bl.resource_assignment_id
3319: AND ( (tmp.txn_currency_code <> bl.txn_currency_code)
3320: OR (tmp.txn_currency_code = bl.txn_currency_code
3321: --and bl.start_date NOT BETWEEN tmp.start_date and tmp.end_date
3409: IF P_PA_DEBUG_MODE = 'Y' Then
3410: print_msg('reset the override rates in budgetLines');
3411: End If;
3412: FORALL i IN l_resource_assignment_tab.FIRST .. l_resource_assignment_tab.LAST
3413: UPDATE PA_BUDGET_LINES bl
3414: SET bl.txn_cost_rate_override = decode(l_rwRtSetFlag_Tab(i),'Y',NULL,bl.txn_cost_rate_override)
3415: ,bl.txn_bill_rate_override = decode(l_bilRtSetFlag_Tab(i),'Y',NULL,bl.txn_bill_rate_override)
3416: ,bl.burden_cost_rate_override = decode(l_bdRtSetFlag_Tab(i),'Y',NULL,bl.burden_cost_rate_override)
3417: WHERE bl.resource_assignment_id = l_resource_assignment_tab(i)
3580: Cursor cur_bl_chk(p_ra_id Number,p_txn_cur_code Varchar2) IS
3581: SELECT 'Y'
3582: FROM dual
3583: WHERE EXISTS (select null
3584: from pa_budget_lines bl
3585: ,pa_resource_assignments ra
3586: where ra.resource_assignment_id = p_ra_id
3587: and bl.resource_assignment_id = ra.resource_assignment_id
3588: and NVL(ra.rate_based_flag,'N') = 'Y'
3629: NVL(tmp.txn_revenue,0) <> 0
3630: )
3631: )
3632: AND NOT EXISTS ( select null
3633: from pa_budget_lines bl
3634: where bl.budget_version_id = p_budget_version_id
3635: and bl.resource_assignment_id = tmp.resource_assignment_id
3636: and bl.txn_currency_code = tmp.txn_currency_code
3637: and (p_source_context <> 'BUDGET_LINE'
4361: WHERE tmp.budget_version_id = p_budget_version_id
4362: AND ra.resource_assignment_id = tmp.resource_assignment_id
4363: AND rl.resource_list_member_id = ra.resource_list_member_id
4364: AND EXISTS ( select null
4365: from pa_budget_lines bl
4366: where bl.budget_version_id = p_budget_version_id
4367: and bl.resource_assignment_id = tmp.resource_assignment_id
4368: and bl.txn_currency_code = tmp.txn_currency_code
4369: group by bl.resource_assignment_id,bl.txn_currency_code
4370: having count(*) > 1
4371: );
4372:
4373: CURSOR cur_NonTimePhLines IS
4374: SELECT /*+ INDEX(BL PA_BUDGET_LINES_U1) */ bl.budget_line_id
4375: ,bl.resource_assignment_id
4376: ,bl.txn_currency_code
4377: ,bl.start_date
4378: ,bl.end_date
4406: ,bl.txn_init_burdened_cost
4407: ,bl.txn_init_revenue
4408: ,tmp.new_plan_start_date plan_start_date
4409: ,tmp.new_plan_end_date plan_end_date
4410: FROM pa_budget_lines bl
4411: ,pa_fp_spread_calc_tmp tmp
4412: WHERE bl.budget_version_id = p_budget_version_id
4413: AND bl.resource_assignment_id = tmp.resource_assignment_id
4414: AND bl.txn_currency_code = tmp.txn_currency_code
4554: IF l_budget_line_id_tab.COUNT > 0 THEN --{
4555: InitReturningPlsqlTabls;
4556: --print_msg('Updating budget lines with planning start and end dates for Non-TimePhase budget');
4557: FORALL i IN l_budget_line_id_tab.FIRST .. l_budget_line_id_tab.LAST
4558: UPDATE PA_BUDGET_LINES bl
4559: SET bl.start_date = NVL(l_start_date_tab(i),bl.start_date)
4560: ,bl.end_date = NVL(l_end_date_tab(i),bl.end_date)
4561: WHERE bl.budget_version_id = p_budget_version_id
4562: AND bl.budget_line_id = l_budget_line_id_tab(i)
4682: ,x_return_status OUT NOCOPY Varchar2
4683: ,x_msg_data OUT NOCOPY Varchar2
4684: ) IS
4685: /* This cursor picks all the budget lines which falls beyond the plan dates
4686: * where actual donot exists and deletes the records from pa_budget_lines table
4687: */
4688: CURSOR cur_Delbl_Lines IS
4689: SELECT /*+ INDEX(BL PA_BUDGET_LINES_U1) */ bl.budget_line_id
4690: ,bl.resource_assignment_id
4685: /* This cursor picks all the budget lines which falls beyond the plan dates
4686: * where actual donot exists and deletes the records from pa_budget_lines table
4687: */
4688: CURSOR cur_Delbl_Lines IS
4689: SELECT /*+ INDEX(BL PA_BUDGET_LINES_U1) */ bl.budget_line_id
4690: ,bl.resource_assignment_id
4691: ,bl.txn_currency_code
4692: ,bl.start_date
4693: ,bl.end_date
4720: ,bl.txn_init_raw_cost
4721: ,bl.txn_init_burdened_cost
4722: ,bl.txn_init_revenue
4723: ,tmp.start_date plan_start_date ,tmp.end_date plan_end_date
4724: FROM pa_budget_lines bl
4725: ,pa_fp_rollup_tmp tmp
4726: WHERE bl.budget_version_id = p_budget_version_id
4727: AND bl.resource_assignment_id = tmp.resource_assignment_id
4728: AND bl.txn_currency_code = tmp.txn_currency_code
4842: print_msg('Number of budgetLines deleted['||l_budget_line_id_tab.COUNT||']');
4843: /* Now delete the budget lines */
4844: IF l_budget_line_id_tab.COUNT > 0 THEN
4845: FORALL i IN l_budget_line_id_tab.FIRST .. l_budget_line_id_tab.LAST
4846: DELETE FROM PA_BUDGET_LINES bl
4847: WHERE bl.budget_version_id = p_budget_version_id
4848: AND bl.budget_line_id = l_budget_line_id_tab(i);
4849: END IF;
4850:
4879: ,x_msg_data OUT NOCOPY Varchar2
4880: ) IS
4881:
4882: CURSOR cur_Oldbl_Lines IS
4883: SELECT /*+ INDEX(BL PA_BUDGET_LINES_U1) */ bl.budget_line_id
4884: ,bl.resource_assignment_id
4885: ,bl.txn_currency_code
4886: ,bl.start_date
4887: ,bl.end_date
4914: ,bl.txn_init_raw_cost
4915: ,bl.txn_init_burdened_cost
4916: ,bl.txn_init_revenue
4917: ,tmp.quantity fp_quantity
4918: FROM pa_budget_lines bl
4919: ,pa_fp_rollup_tmp tmp
4920: WHERE bl.budget_version_id = p_budget_version_id
4921: AND bl.resource_assignment_id = tmp.resource_assignment_id
4922: AND bl.txn_currency_code = tmp.txn_currency_code
4925:
4926: /* This cursor picks all the records from rollup tmp where budget line donot exists
4927: * and insert these records into budget lines */
4928: CURSOR cur_newtmp_lines IS
4929: SELECT PA_BUDGET_LINES_S.NEXTVAL
4930: ,tmp.resource_assignment_id
4931: ,tmp.txn_currency_code
4932: ,tmp.start_date
4933: ,tmp.end_date
4938: FROM pa_fp_rollup_tmp tmp
4939: WHERE tmp.budget_version_id = p_budget_version_id
4940: AND NOT EXISTS
4941: (SELECT null
4942: FROM pa_budget_lines bl
4943: WHERE bl.budget_version_id = p_budget_version_id
4944: AND bl.resource_assignment_id = tmp.resource_assignment_id
4945: AND bl.txn_currency_code = tmp.txn_currency_code
4946: AND bl.start_date = tmp.start_date
5057: IF l_budget_line_id_tab.COUNT > 0 THEN
5058: --print_msg('Number of budget Lines updated['||l_budget_line_id_tab.COUNT||']');
5059: FORALL i IN l_budget_line_id_tab.FIRST .. l_budget_line_id_tab.LAST
5060: /* now update the budget lines with new quantity */
5061: UPDATE PA_BUDGET_LINES bl
5062: SET bl.quantity = NVL(bl.quantity,0) + NVL(l_quantity_tab(i),0)
5063: ,bl.txn_raw_cost = decode(p_budget_version_type,'REVENUE',bl.txn_raw_cost,l_txn_raw_cost_tab(i))
5064: ,bl.txn_burdened_cost = decode(p_budget_version_type,'REVENUE',bl.txn_burdened_cost,l_txn_burden_cost_tab(i))
5065: ,bl.txn_revenue = decode(p_budget_version_type,'COST',bl.txn_revenue,l_txn_revenue_tab(i))
5066: WHERE bl.budget_line_id = l_budget_line_id_tab(i)
5067: AND bl.budget_version_id = p_budget_version_id ;
5068:
5069: FORALL i IN l_budget_line_id_tab.FIRST .. l_budget_line_id_tab.LAST
5070: UPDATE PA_BUDGET_LINES bl
5071: SET bl.project_raw_cost = decode(p_budget_version_type,'REVENUE',bl.project_raw_cost
5072: ,decode(bl.project_currency_code,bl.txn_currency_code,bl.txn_raw_cost
5073: ,pa_currency.round_trans_currency_amt1((bl.txn_raw_cost * bl.project_cost_exchange_rate ),bl.project_currency_code)))
5074: ,bl.project_burdened_cost = decode(p_budget_version_type,'REVENUE',bl.project_burdened_cost
5190:
5191: IF l_budget_line_id_tab.COUNT > 0 THEN --{
5192: --print_msg('Number of New budget lines inserted ['||l_budget_line_id_tab.COUNT||']');
5193: FORALL i in l_budget_line_id_tab.FIRST .. l_budget_line_id_tab.LAST
5194: INSERT INTO PA_BUDGET_LINES(
5195: BUDGET_VERSION_ID
5196: ,BUDGET_LINE_ID
5197: ,RESOURCE_ASSIGNMENT_ID
5198: ,TXN_CURRENCY_CODE
5323: FORALL i IN p_resource_assignment_id_tab.FIRST .. p_resource_assignment_id_tab.LAST
5324: UPDATE /*+ INDEX(TMP PA_FP_SPREAD_CALC_TMP_N1) */ pa_fp_spread_calc_tmp tmp
5325: SET (tmp.SYSTEM_REFERENCE_DAT1
5326: ,tmp.SYSTEM_REFERENCE_DAT2) = (select MIN(bl.start_date),MAX(bl.end_date)
5327: from pa_budget_lines bl
5328: where bl.resource_assignment_id = tmp.resource_assignment_id
5329: and bl.txn_currency_code = tmp.txn_currency_code)
5330: WHERE tmp.resource_assignment_id = p_resource_assignment_id_tab(i)
5331: AND tmp.txn_currency_code = p_txn_currency_code_tab(i)
5464: /* Now update the rollup tmp table with the sum of bl line quantities prior to new plan start date*/
5465: UPDATE pa_fp_rollup_tmp tmp
5466: SET tmp.processed_flag = 'Y'
5467: ,tmp.quantity = (SELECT SUM(nvl(bl.quantity,0)-nvl(bl.init_quantity,0))
5468: FROM pa_budget_lines bl
5469: WHERE bl.budget_version_id = p_budget_version_id
5470: AND bl.resource_assignment_id = tmp.resource_assignment_id
5471: AND bl.txn_currency_code = tmp.txn_currency_code
5472: AND bl.end_date < ( tmp.start_date) )
5516: /* Now update the rollup tmp table with the sum of bl line quantities later than new plan end date */
5517: UPDATE pa_fp_rollup_tmp tmp
5518: SET tmp.processed_flag = 'Y'
5519: ,tmp.quantity = (SELECT SUM(nvl(bl.quantity,0)-nvl(bl.init_quantity,0))
5520: FROM pa_budget_lines bl
5521: WHERE bl.budget_version_id = p_budget_version_id
5522: AND bl.resource_assignment_id = tmp.resource_assignment_id
5523: AND bl.txn_currency_code = tmp.txn_currency_code
5524: AND bl.start_date > ( tmp.end_date) )
5585: If p_pa_debug_mode = 'Y' Then
5586: pa_debug.init_err_stack('PA_FP_CALC_UTILS.populate_blTxnCurCombo');
5587: print_msg('Entered populate_blTxnCurCombo api');
5588: End If;
5589: SELECT /*+ INDEX(BL PA_BUDGET_LINES_U1) */
5590: bl.resource_assignment_id
5591: ,bl.txn_currency_code
5592: BULK COLLECT INTO
5593: l_raId_Tab
5591: ,bl.txn_currency_code
5592: BULK COLLECT INTO
5593: l_raId_Tab
5594: ,l_TxnCur_Tab
5595: FROM pa_budget_lines bl
5596: ,pa_fp_spread_calc_tmp tmp
5597: WHERE bl.budget_version_id = p_budget_version_id
5598: AND bl.resource_assignment_id = tmp.resource_assignment_id
5599: AND bl.txn_currency_code <> tmp.txn_currency_code
5637: /* Now updates the other attributes for the newly inserted rows */
5638: FORALL i IN l_raId_Tab.FIRST .. l_raId_Tab.LAST
5639: UPDATE /*+ INDEX(TMP PA_FP_SPREAD_CALC_TMP_N1) */ pa_fp_spread_calc_tmp tmp
5640: SET tmp.quantity = (select sum(bl.quantity)
5641: from pa_budget_lines bl
5642: where bl.budget_version_id = tmp.budget_version_id
5643: and bl.resource_assignment_id = tmp.resource_assignment_id
5644: and bl.txn_currency_code = tmp.txn_currency_code)
5645: ,tmp.system_reference_var2 = NULL
5830: ,tmp.MFC_COST_CHANGE_FLAG = decode(NVL(tmp.OLD_MFC_COST_TYPE_ID,-999),NVL(tmp.NEW_MFC_COST_TYPE_ID,-999),'N','Y')
5831: ,tmp.SYSTEM_REFERENCE_VAR1 = (SELECT 'Y'
5832: FROM dual
5833: WHERE EXISTS ( select null
5834: from pa_budget_lines bl
5835: where bl.budget_version_id = tmp.budget_version_id
5836: and bl.resource_assignment_id = tmp.resource_assignment_id
5837: and bl.txn_currency_code <> tmp.txn_currency_code
5838: GROUP BY bl.resource_assignment_id,bl.txn_currency_code
6558: IF P_PA_DEBUG_MODE = 'Y' Then
6559: print_msg('Delete zero Quantity budget lines where actuals donot exists');
6560: End If;
6561: FORALL i IN x_resource_assignment_tab.FIRST .. x_resource_assignment_tab.LAST
6562: DELETE FROM pa_budget_lines bl
6563: WHERE bl.budget_version_id = p_budget_version_id
6564: AND bl.resource_assignment_id = x_resource_assignment_tab(i)
6565: AND NVL(bl.quantity,0) = 0
6566: AND (NVL(bl.init_quantity,0) = 0
7182: ,BL_CREATED_BY
7183: ,BL_CREATION_DATE
7184: ,mfc_cost_change_flag
7185: )
7186: SELECT /*+ INDEX(BL PA_BUDGET_LINES_U1) */
7187: bl.RESOURCE_ASSIGNMENT_ID --resource_assignment_id
7188: ,bl.BUDGET_VERSION_ID --budget_version_id
7189: ,tmp.BUDGET_VERSION_TYPE
7190: ,bl.BUDGET_LINE_ID
7268: ,bl.TRANSFER_PRICE_RATE
7269: ,bl.CREATED_BY
7270: ,bl.CREATION_DATE
7271: ,tmp.mfc_cost_change_flag
7272: FROM PA_BUDGET_LINES bl
7273: ,PA_FP_SPREAD_CALC_TMP tmp
7274: WHERE bl.budget_version_id = p_budget_verson_id
7275: AND bl.budget_version_id = tmp.budget_version_id
7276: AND bl.resource_assignment_id = tmp.resource_assignment_id
7365: /* Bug Fix 4332086
7366: Whenever currency is overridden along with a change in quantity in the workplan flow
7367: in Update Task Details page, the following piece of code gets executed.
7368:
7369: This code caches several attributes from pa_budget_lines table and will use them in the
7370: later part of the flow, thus causing the above bug. When ever currency code is overwritten
7371: we need to use the new currency's conversion attributes, but where as this code will use
7372: old currency's conversion attributes.
7373:
7670: , cache.BL_CREATED_BY
7671: , cache.BL_CREATION_DATE
7672: FROM pa_fp_rollup_tmp tmp
7673: ,pa_fp_spread_calc_tmp1 cache
7674: ,pa_budget_lines bl
7675: WHERE tmp.budget_version_id = p_budget_verson_id
7676: AND tmp.budget_version_id = cache.budget_version_id
7677: AND tmp.resource_assignment_id = cache.resource_assignment_id
7678: AND tmp.txn_currency_code = cache.txn_currency_code
7676: AND tmp.budget_version_id = cache.budget_version_id
7677: AND tmp.resource_assignment_id = cache.resource_assignment_id
7678: AND tmp.txn_currency_code = cache.txn_currency_code
7679: AND tmp.start_date = cache.start_date
7680: and cache.budget_version_id = bl.budget_version_id(+) --Bug 4224464. Added the join with pa_budget_lines
7681: AND cache.resource_assignment_id = bl.resource_assignment_id(+)
7682: AND cache.txn_currency_code = bl.txn_currency_code(+)
7683: AND cache.start_date = bl.start_date(+);
7684:
8138:
8139: l_debug_mode VARCHAR2(30);
8140: l_stage NUMBER;
8141: l_populate_mrc_tab_flag Varchar2(10) := 'N'; --MRC Elimination Changes:NVL(PA_FP_CALC_PLAN_PKG.G_populate_mrc_tab_flag,'N');
8142: l_existing_bl_id pa_budget_lines.budget_line_id%TYPE;
8143:
8144: l_bl_raId_tab pa_plsql_datatypes.NumTabTyp;
8145: l_bl_sDate_tab pa_plsql_datatypes.dateTabTyp;
8146: l_bl_edate_tab pa_plsql_datatypes.dateTabTyp;
8250: l_rate_base_flag_tab pa_plsql_datatypes.Char1TabTyp;
8251:
8252:
8253: CURSOR Cur_RollupLines IS
8254: SELECT /*+ INDEX(B PA_BUDGET_LINES_U2) */ r.resource_assignment_id
8255: ,b.start_date
8256: ,b.end_date
8257: ,b.period_name
8258: ,r.budget_line_id
8362: ,r.BL_CREATED_BY
8363: ,r.BL_CREATION_DATE
8364: ,NVL(ra.rate_based_flag,'N')
8365: FROM pa_fp_rollup_tmp r
8366: ,pa_budget_lines b
8367: ,pa_resource_assignments ra
8368: WHERE r.budget_line_id = b.budget_line_id
8369: AND b.budget_version_id = p_budget_version_id
8370: AND b.budget_version_id = r.budget_version_id --Bug 7520706
8393: ,bl.resource_assignment_id
8394: ,bl.txn_currency_code
8395: ,bl.project_currency_code
8396: ,bl.projfunc_currency_code
8397: FROM pa_budget_lines bl
8398: ,pa_fp_rollup_tmp r
8399: WHERE bl.budget_version_id = p_budget_version_id
8400: AND bl.budget_line_id = r.budget_line_id
8401: AND NVL(r.processed_flag,'Y') <> 'N';
8426: ,bl.resource_assignment_id
8427: ,bl.txn_currency_code
8428: ,bl.project_currency_code
8429: ,bl.projfunc_currency_code
8430: FROM pa_budget_lines bl
8431: ,pa_fp_spread_calc_tmp2 tmp2
8432: WHERE bl.budget_version_id = p_budget_version_id
8433: AND tmp2.budget_version_id = bl.budget_version_id
8434: AND tmp2.resource_assignment_id = bl.resource_assignment_id
8785: WHERE tmp2.budget_version_id = p_budget_version_id
8786: AND ra.resource_assignment_id = tmp2.resource_assignment_id;
8787: END Populate_tmp2Plsql_tab;
8788:
8789: /* This API will round off the rounding difference for the last budget line in pa_budget_lines
8790: * when a new currency line is added to the already existing resource assignment.
8791: * Bug #13788196
8792: */
8793:
8832: (nvl(bl.txn_bill_rate_override,nvl(txn_standard_bill_rate,0))))))) unrounded_txn_revenue
8833: ,to_number(NULL) diff_raw_cost
8834: ,to_number(NULL) diff_burden_cost
8835: ,to_number(NULL) diff_revenue
8836: FROM pa_budget_lines bl
8837: WHERE bl.budget_version_id = p_budget_version_id
8838: AND nvl(quantity,0) <> 0
8839: GROUP BY resource_assignment_id
8840: ,txn_currency_code ;
8943: g_stage := 'Update_rounding_diff_bl:102';
8944: print_msg('Updating pa_budget_line last line with the rounding difference amount');
8945: print_msg('Total number of resource assignments with the rounding difference amount : '||l_resource_assignment_tab.COUNT);
8946: FORALL i IN l_resource_assignment_tab.FIRST .. l_resource_assignment_tab.LAST
8947: UPDATE pa_budget_lines tmp
8948: SET tmp.txn_raw_cost = DECODE((nvl(tmp.txn_raw_cost,0)+NVL(l_diff_raw_cost_tab(i),0)),0,NULL
8949: ,(nvl(tmp.txn_raw_cost,0)+NVL(l_diff_raw_cost_tab(i),0)))
8950: ,tmp.txn_burdened_cost = DECODE((nvl(tmp.txn_burdened_cost,0)+NVL(l_diff_burden_cost_tab(i),0)),0,NULL
8951: ,(nvl(tmp.txn_burdened_cost,0)+NVL(l_diff_burden_cost_tab(i),0)))
9131: ,bl.pfc_cur_conv_rejection_code
9132: ,bl.pc_cur_conv_rejection_code
9133: ,bl.project_currency_code
9134: ,bl.projfunc_currency_code
9135: FROM pa_budget_lines bl
9136: WHERE bl.budget_version_id = p_budget_version_id
9137: AND EXISTS (select null
9138: From pa_fp_spread_calc_tmp1 tmp1
9139: where tmp1.budget_version_id = p_budget_version_id
9352:
9353: --print_msg('Bulk updating budget lines');
9354: /* Now Bulk Update the budget Lines */
9355: FORALL i IN l_bl_budget_line_id_tab.FIRST .. l_bl_budget_line_id_tab.LAST SAVE EXCEPTIONS
9356: UPDATE pa_budget_lines bl
9357: SET bl.txn_currency_code = l_bl_txn_curcode_tab(i)
9358: ,bl.quantity = l_bl_quantity_tab(i)
9359: /* Bug fix:4900436 */
9360: ,bl.display_quantity = decode(g_wp_version_flag,'Y',l_bl_quantity_tab(i)
9822: Populate_tmp2Plsql_tab;
9823: IF l_tmp2_budget_line_id_tab.COUNT > 0 THEN --{
9824: --print_msg('Bulk updating dup val index exception rows');
9825: FORALL i IN l_tmp2_budget_line_id_tab.FIRST .. l_tmp2_budget_line_id_tab.LAST
9826: UPDATE pa_budget_lines bl
9827: SET bl.txn_currency_code = l_tmp2_txn_curr_code_tab(i)
9828: ,bl.quantity = l_tmp2_quantity_tab(i)
9829: /* Bug fix:4900436 */
9830: ,bl.display_quantity = decode(g_wp_version_flag,'Y',l_tmp2_quantity_tab(i)
9850: WHERE bl.budget_line_id = l_tmp2_budget_line_id_tab(i);
9851:
9852: --print_msg(' Num Of rows UPDATED ['||sql%rowcount||']');
9853: FORALL i IN l_tmp2_budget_line_id_tab.FIRST .. l_tmp2_budget_line_id_tab.LAST
9854: UPDATE pa_budget_lines bl
9855: SET bl.txn_raw_cost = decode((nvl(bl.txn_init_raw_cost,0) + pa_currency.round_trans_currency_amt1(
9856: (nvl(bl.quantity,0) - nvl(bl.init_quantity,0)) *
9857: (nvl(bl.txn_cost_rate_override,nvl(txn_standard_cost_rate,0))),bl.txn_currency_code)),0,NULL,
9858: (nvl(bl.txn_init_raw_cost,0) + pa_currency.round_trans_currency_amt1(
9871: (nvl(bl.quantity,0) - nvl(bl.init_quantity,0)) *
9872: (nvl(bl.txn_bill_rate_override,nvl(txn_standard_bill_rate,0))),bl.txn_currency_code)))
9873: WHERE bl.budget_line_id = l_tmp2_budget_line_id_tab(i);
9874:
9875: /* Call This API to round off the rounding difference for the last budget line in pa_budget_lines
9876: * when a new currency line is added to the already existing resource assignment.
9877: * Bug #13788196 */
9878:
9879: Update_rounding_diff_bl
9917: END IF;
9918:
9919: /* Now delete the duplval budget lines */
9920: FORALL i IN l_del_budget_line_id_tab.FIRST .. l_del_budget_line_id_tab.LAST
9921: DELETE FROM pa_budget_lines bl
9922: WHERE bl.budget_line_id = l_del_budget_line_id_tab(i);
9923: END IF; --}
9924:
9925: /* MRC ehancements changes: */
9979: ,bl.projfunc_rev_rate_type
9980: ,bl.projfunc_rev_exchange_rate
9981: ,bl.projfunc_rev_rate_date_type
9982: ,bl.projfunc_rev_rate_date
9983: FROM pa_budget_lines bl
9984: WHERE bl.budget_line_id = tmp.budget_line_id
9985: )
9986: WHERE tmp.budget_line_id = l_tmp2_budget_line_id_tab(exBlId);
9987:
10054: ,bl.projfunc_rev_rate_type
10055: ,bl.projfunc_rev_exchange_rate
10056: ,bl.projfunc_rev_rate_date_type
10057: ,bl.projfunc_rev_rate_date
10058: FROM pa_budget_lines bl
10059: WHERE bl.budget_line_id = l_tmp2_budget_line_id_tab(exBlId)
10060: AND NOT EXISTS ( SELECT NULL
10061: FROM PA_FP_ROLLUP_TMP tmp1
10062: WHERE tmp1.budget_line_id = l_tmp2_budget_line_id_tab(exBlId)
10154:
10155: /* This cursor picks budget line attributes which needs to be retained and updated even if there was no change detected by
10156: core calculate api flow -- that is, no qty/amt/rate changed */
10157: CURSOR blAttribDetails IS
10158: SELECT /*+ INDEX(BL PA_BUDGET_LINES_U1) */ cache.resource_assignment_id
10159: ,cache.start_date
10160: ,cache.period_name
10161: ,cache.end_Date
10162: ,cache.budget_version_id
10257: , cache.TXN_DISCOUNT_PERCENTAGE
10258: , cache.TRANSFER_PRICE_RATE
10259: , cache.BL_CREATED_BY
10260: , cache.BL_CREATION_DATE
10261: FROM pa_fp_spread_calc_tmp1 cache, pa_budget_lines bl
10262: WHERE cache.budget_version_id = p_budget_verson_id
10263: AND cache.budget_version_id = bl.budget_version_id
10264: AND cache.resource_assignment_id = bl.resource_assignment_id
10265: AND cache.txn_currency_code = bl.txn_currency_code
10263: AND cache.budget_version_id = bl.budget_version_id
10264: AND cache.resource_assignment_id = bl.resource_assignment_id
10265: AND cache.txn_currency_code = bl.txn_currency_code
10266: AND cache.start_date = bl.start_date
10267: /*If a new budget line (not exists in pa_budget_lines) is attempted to be created with all amts/qty/rate as null but
10268: * with dffs/mc/change reason, from amg flow, that budget line would not be selected due to the join with pa_budget_lines.
10269: * This is only intended as budget lines with null amts/qty are not maintained in pa_budget_lines any more.
10270: */
10271: AND NOT EXISTS (SELECT 'X' FROM pa_fp_rollup_tmp tmp WHERE tmp.budget_version_id = cache.budget_version_id
10264: AND cache.resource_assignment_id = bl.resource_assignment_id
10265: AND cache.txn_currency_code = bl.txn_currency_code
10266: AND cache.start_date = bl.start_date
10267: /*If a new budget line (not exists in pa_budget_lines) is attempted to be created with all amts/qty/rate as null but
10268: * with dffs/mc/change reason, from amg flow, that budget line would not be selected due to the join with pa_budget_lines.
10269: * This is only intended as budget lines with null amts/qty are not maintained in pa_budget_lines any more.
10270: */
10271: AND NOT EXISTS (SELECT 'X' FROM pa_fp_rollup_tmp tmp WHERE tmp.budget_version_id = cache.budget_version_id
10272: AND tmp.resource_assignment_id = cache.resource_assignment_id
10265: AND cache.txn_currency_code = bl.txn_currency_code
10266: AND cache.start_date = bl.start_date
10267: /*If a new budget line (not exists in pa_budget_lines) is attempted to be created with all amts/qty/rate as null but
10268: * with dffs/mc/change reason, from amg flow, that budget line would not be selected due to the join with pa_budget_lines.
10269: * This is only intended as budget lines with null amts/qty are not maintained in pa_budget_lines any more.
10270: */
10271: AND NOT EXISTS (SELECT 'X' FROM pa_fp_rollup_tmp tmp WHERE tmp.budget_version_id = cache.budget_version_id
10272: AND tmp.resource_assignment_id = cache.resource_assignment_id
10273: AND tmp.txn_currency_code = cache.txn_currency_code
10844:
10845: END update_dffcols;
10846:
10847: /*Bug:4272944: Added new procedure to insert zero qty budget lines from pa_fp_spread_calc_tmp1 to
10848: *pa_budget_lines. This fix is done specific to Funding of Autobase line is failing.
10849: *donot populate or use pa_fp_spread_calc_tmp1 table for any other purpose.
10850: *Note: Calling API may populate this table only for AMG/MSP/Autobaseline purpose.
10851: */
10852: PROCEDURE InsertFunding_ReqdLines
10864: ,tmp.txn_currency_code
10865: ,tmp.start_date
10866: ,tmp.end_date
10867: ,tmp.period_name
10868: ,pa_budget_lines_s.nextval
10869: FROM pa_fp_spread_calc_tmp1 tmp
10870: WHERE tmp.budget_version_id = p_budget_verson_id;
10871:
10872: l_bl_source VARCHAR2(10) := 'AB'; --'indicates lines created for funding auto baseline'
10924: IF l_budget_line_id_tab.COUNT > 0 THEN --{
10925: BEGIN --{
10926:
10927: FORALL i IN l_budget_line_id_tab.FIRST .. l_budget_line_id_tab.LAST
10928: INSERT INTO PA_BUDGET_LINES bl
10929: (bl.BUDGET_LINE_ID
10930: ,bl.RESOURCE_ASSIGNMENT_ID --resource_assignment_id
10931: ,bl.BUDGET_VERSION_ID --budget_version_id
10932: ,bl.TXN_CURRENCY_CODE --txn_currency_code