[Home] [Help]
163: CURSOR cur_check IS
164: SELECT 'Y'
165: FROM dual
166: WHERE EXISTS ( select null
167: from pa_budget_lines bl
168: where bl.budget_version_id = p_budget_version_id
169: and bl.resource_assignment_id = p_resAsgnId
170: GROUP BY bl.resource_assignment_id
171: HAVING COUNT(*) > 1
197:
198: /* bug fix:5726773: Added for supporting neg or zero quantity spread */
199:
200: Cursor cur_asgn_bl_sumchk IS
201: SELECT /*+ INDEX(BL PA_BUDGET_LINES_U1) */
202: sum(bl.quantity) bl_sum_quantity
203: ,sum(bl.init_quantity) bl_sum_act_quantity
204: FroM pa_budget_lines bl
205: where bl.resource_assignment_id = p_ra_id
200: Cursor cur_asgn_bl_sumchk IS
201: SELECT /*+ INDEX(BL PA_BUDGET_LINES_U1) */
202: sum(bl.quantity) bl_sum_quantity
203: ,sum(bl.init_quantity) bl_sum_act_quantity
204: FroM pa_budget_lines bl
205: where bl.resource_assignment_id = p_ra_id
206: and bl.txn_currency_code = p_txn_cur_code
207: and bl.budget_version_id = p_budget_version_id;
208:
207: and bl.budget_version_id = p_budget_version_id;
208:
209:
210: Cursor cur_periodic_bl_sumchk IS
211: SELECT /*+ INDEX(BL PA_BUDGET_LINES_U1) */
212: sum(bl.quantity) bl_sum_quantity
213: ,sum(bl.init_quantity) bl_sum_act_quantity
214: FroM pa_budget_lines bl
215: where bl.resource_assignment_id = p_ra_id
210: Cursor cur_periodic_bl_sumchk IS
211: SELECT /*+ INDEX(BL PA_BUDGET_LINES_U1) */
212: sum(bl.quantity) bl_sum_quantity
213: ,sum(bl.init_quantity) bl_sum_act_quantity
214: FroM pa_budget_lines bl
215: where bl.resource_assignment_id = p_ra_id
216: and bl.txn_currency_code = p_txn_cur_code
217: and bl.budget_version_id = p_budget_version_id
218: and bl.start_date between p_start_date and p_end_date;
256: WHERE tmp.budget_version_id = p_budget_version_id
257: AND ra.resource_assignment_id = tmp.resource_assignment_id
258: AND rl.resource_list_member_id = ra.resource_list_member_id
259: AND EXISTS (SELECT NULL
260: FROM pa_budget_lines bl
261: WHERE bl.resource_assignment_id = tmp.resource_assignment_id
262: AND bl.txn_currency_code = tmp.txn_currency_code
263: /* Bug fix: 4294902 :Check zero quantity only for the open periods */
264: AND NVL(g_etc_start_date,bl.start_date) BETWEEN bl.start_date and bl.end_date
285: * 3:NESTED LOOPS :(cost=21,rows=1)
286: * 4:HASH JOIN SEMI :(cost=20,rows=1)
287: * 5:TABLE ACCESS BY INDEX ROWID PA_FP_SPREAD_CALC_TMP :(cost=6,rows=82)
288: * 6:INDEX RANGE SCAN PA_FP_SPREAD_CALC_TMP_N2 :(cost=2,rows=33)
289: * 5:TABLE ACCESS BY INDEX ROWID PA_BUDGET_LINES :(cost=13,rows=8)
290: * 6:INDEX RANGE SCAN PA_BUDGET_LINES_N3 :(cost=2,rows=1)
291: * 4:TABLE ACCESS BY INDEX ROWID PA_RESOURCE_ASSIGNMENTS :(cost=1,rows=1)
292: * 5:INDEX UNIQUE SCAN PA_RESOURCE_ASSIGNMENTS_U1 :(cost=,rows=1)
293: * 3:TABLE ACCESS BY INDEX ROWID PA_RESOURCE_LIST_MEMBERS :(cost=1,rows=1)
286: * 4:HASH JOIN SEMI :(cost=20,rows=1)
287: * 5:TABLE ACCESS BY INDEX ROWID PA_FP_SPREAD_CALC_TMP :(cost=6,rows=82)
288: * 6:INDEX RANGE SCAN PA_FP_SPREAD_CALC_TMP_N2 :(cost=2,rows=33)
289: * 5:TABLE ACCESS BY INDEX ROWID PA_BUDGET_LINES :(cost=13,rows=8)
290: * 6:INDEX RANGE SCAN PA_BUDGET_LINES_N3 :(cost=2,rows=1)
291: * 4:TABLE ACCESS BY INDEX ROWID PA_RESOURCE_ASSIGNMENTS :(cost=1,rows=1)
292: * 5:INDEX UNIQUE SCAN PA_RESOURCE_ASSIGNMENTS_U1 :(cost=,rows=1)
293: * 3:TABLE ACCESS BY INDEX ROWID PA_RESOURCE_LIST_MEMBERS :(cost=1,rows=1)
294: * 4:INDEX UNIQUE SCAN PA_RESOURCE_LIST_MEMBERS_U1 :(cost=,rows=1)
302: ,pa_resource_list_members rl
303: WHERE tmp.budget_version_id = p_budget_version_id
304: AND ra.resource_assignment_id = tmp.resource_assignment_id
305: AND rl.resource_list_member_id = ra.resource_list_member_id
306: AND EXISTS (SELECT /*+ NO_UNNEST INDEX (BL,PA_BUDGET_LINES_U1) */ NULL -- Bug#4728472
307: FROM pa_budget_lines bl
308: WHERE bl.resource_assignment_id = tmp.resource_assignment_id
309: AND bl.txn_currency_code = tmp.txn_currency_code
310: AND bl.end_date < bl.start_date
303: WHERE tmp.budget_version_id = p_budget_version_id
304: AND ra.resource_assignment_id = tmp.resource_assignment_id
305: AND rl.resource_list_member_id = ra.resource_list_member_id
306: AND EXISTS (SELECT /*+ NO_UNNEST INDEX (BL,PA_BUDGET_LINES_U1) */ NULL -- Bug#4728472
307: FROM pa_budget_lines bl
308: WHERE bl.resource_assignment_id = tmp.resource_assignment_id
309: AND bl.txn_currency_code = tmp.txn_currency_code
310: AND bl.end_date < bl.start_date
311: /* Bug:4440255 : added the following conditions reduces the FTS on pa_budget_lines and cost will reduce from 50 to 22 */
307: FROM pa_budget_lines bl
308: WHERE bl.resource_assignment_id = tmp.resource_assignment_id
309: AND bl.txn_currency_code = tmp.txn_currency_code
310: AND bl.end_date < bl.start_date
311: /* Bug:4440255 : added the following conditions reduces the FTS on pa_budget_lines and cost will reduce from 50 to 22 */
312: AND bl.budget_version_id = tmp.budget_version_id
313: /* end of bug fix:4440255 */
314: );
315: BEGIN
1399:
1400: /* update table with txn markup percentage */
1401: UPDATE /*+ INDEX(TMP PA_FP_SPREAD_CALC_TMP2_N1) */ pa_fp_spread_calc_tmp2 tmp
1402: SET tmp.bill_markup_percentage = (select AVG(bl.txn_markup_percent)
1403: from pa_budget_lines bl
1404: where bl.resource_assignment_id = tmp.resource_assignment_id
1405: and bl.txn_currency_code = tmp.txn_currency_code
1406: )
1407: WHERE tmp.budget_version_id = p_budget_version_id
1405: and bl.txn_currency_code = tmp.txn_currency_code
1406: )
1407: WHERE tmp.budget_version_id = p_budget_version_id
1408: AND EXISTS ( select null
1409: from pa_budget_lines bl1
1410: where bl1.resource_assignment_id = tmp.resource_assignment_id
1411: and bl1.txn_currency_code = tmp.txn_currency_code
1412: );
1413:
1425: ,tmp.system_reference_var2 --avg_zero_null_burden_rate
1426: ,tmp.system_reference_var3 --avg_zero_null_bill_rate
1427: ,tmp.bill_markup_percentage
1428: ) =
1429: (SELECT /*+ INDEX(BLAVGRT PA_BUDGET_LINES_U1) */
1430: AVG(DECODE((nvl(blavgrt.quantity,0) - nvl(blavgrt.init_quantity,0)),0
1431: ,NULL,blavgrt.txn_cost_rate_override)) avg_txn_cost_rate_override
1432: ,AVG(DECODE((nvl(blavgrt.quantity,0) - nvl(blavgrt.init_quantity,0)),0
1433: ,NULL,blavgrt.burden_cost_rate_override)) avg_burden_cost_rate_override
1436: ,AVG(NVL(blavgrt.txn_cost_rate_override,blavgrt.txn_standard_cost_rate))
1437: ,AVG(NVL(blavgrt.burden_cost_rate_override,blavgrt.burden_cost_rate))
1438: ,AVG(NVL(blavgrt.txn_bill_rate_override,blavgrt.txn_standard_bill_rate))
1439: ,AVG(blavgrt.txn_markup_percent)
1440: FROM pa_budget_lines blavgrt
1441: WHERE blavgrt.budget_version_id = p_budget_version_id
1442: AND blavgrt.resource_assignment_id = tmp.resource_assignment_id
1443: AND blavgrt.txn_currency_code = tmp.txn_currency_code
1444: AND ( (tmp.start_date is NULL AND tmp.end_date is NULL )
1475: ,tmp.system_reference_num2 --txnRaw
1476: ,tmp.system_reference_num3 --txnburd
1477: ,tmp.system_reference_num4 --txnrev
1478: ) =
1479: ( SELECT /*+ INDEX(BL PA_BUDGET_LINES_U1) */
1480: decode(sum(bl.quantity),0,NULL,sum(bl.quantity)) --quantity
1481: ,decode(sum(bl.txn_raw_cost),0,NULL,sum(bl.txn_raw_cost)) --txn_raw_cost
1482: ,decode(sum(bl.txn_burdened_cost),0,NULL,sum(bl.txn_burdened_cost)) --txn_burdened_cost
1483: ,decode(sum(bl.txn_revenue),0,NULL,sum(bl.txn_revenue)) --txn_revenue
1572: ,SUM(tmp.quantity) -- bl_zero_null_quantity
1573: ,SUM(tmp.txn_raw_cost) -- bl_zero_null_rawcost
1574: ,SUM(tmp.txn_burdened_cost) -- bl_zero_null_burdencost
1575: ,SUM(tmp.txn_revenue) -- bl_zero_null_revenue
1576: FROM pa_budget_lines bl
1577: WHERE bl.budget_version_id = p_budget_version_id
1578: AND bl.resource_assignment_id = tmp.resource_assignment_id
1579: AND bl.txn_currency_code = tmp.txn_currency_code
1580: AND ( (tmp.start_date is NULL AND tmp.end_date is NULL )
1766: /*
1767: Bug 6429285
1768: Below if condition will handle a corner case
1769: If a rate based resource is added as non rate based resource assignment
1770: in pa_resource_asgn_curr and pa_budget_lines quantity will be populated
1771: as raw_cost and display_quantity will be null.Now if we want to enter
1772: the quantity same as raw cost (i.e existing quantity) the above if condition will fail because
1773: the user entered quantity is same as what is alreay existing*/
1774:
2280: WHERE rtx.resource_assignment_id = l_resource_assignment_tab(i)
2281: AND rtx.txn_currency_code = l_txn_currency_code_tab(i);
2282:
2283: FORALL i IN l_resource_assignment_tab.FIRST .. l_resource_assignment_tab.LAST
2284: UPDATE pa_budget_lines bl
2285: SET bl.txn_bill_rate_override = l_bill_rate_override_tab(i)
2286: ,bl.txn_cost_rate_override = l_cost_rate_override_tab(i)
2287: ,bl.burden_cost_rate_override = l_burden_rate_override_tab(i)
2288: WHERE bl.resource_assignment_id = l_resource_assignment_tab(i)
2368: AND NVL(tmp.RLM_ID_CHANGE_FLAG,'N') = 'N'
2369: */
2370: AND NVL(tmp.DELETE_BL_FLAG,'N') = 'N'
2371: AND EXISTS ( select null
2372: from pa_budget_lines bl
2373: where bl.resource_assignment_id = tmp.resource_assignment_id
2374: and bl.txn_currency_code = tmp.txn_currency_code
2375: and ((p_source_context = 'BUDGET_LINE'
2376: and bl.start_date between tmp.start_date and tmp.end_date)
2468: (sum(nvl(bl.txn_raw_cost,0) - nvl(bl.txn_init_raw_cost,0)))))
2469: ,decode(sum(nvl(bl.txn_revenue,0)-nvl(bl.txn_init_revenue,0)),0,rtx.txn_bill_rate_override
2470: ,(sum(nvl(bl.txn_revenue,0)-nvl(bl.txn_init_revenue,0)))/
2471: (sum(nvl(bl.txn_raw_cost,0) - nvl(bl.txn_init_raw_cost,0))))
2472: from pa_budget_lines bl
2473: where bl.resource_assignment_id = rtx.resource_assignment_id
2474: and bl.txn_currency_code = rtx.txn_currency_code
2475: )
2476: WHERE rtx.resource_assignment_id = l_resource_assignment_tab(i)
2475: )
2476: WHERE rtx.resource_assignment_id = l_resource_assignment_tab(i)
2477: AND rtx.txn_currency_code = l_txn_currency_code_tab(i)
2478: AND EXISTS (select null
2479: from pa_budget_lines bl1
2480: where bl1.resource_assignment_id = rtx.resource_assignment_id
2481: and bl1.txn_currency_code = rtx.txn_currency_code
2482: );
2483:
2488: FORALL i IN l_resource_assignment_tab.FIRST .. l_resource_assignment_tab.LAST
2489: UPDATE /*+ INDEX(TMP PA_FP_SPREAD_CALC_TMP_N1) */ pa_fp_spread_calc_tmp tmp
2490: SET tmp.quantity =
2491: (select decode(sum(bl.txn_raw_cost),NULL,sum(bl.txn_revenue),sum(bl.txn_raw_cost))
2492: from pa_budget_lines bl
2493: where bl.resource_assignment_id = tmp.resource_assignment_id
2494: and bl.txn_currency_code = tmp.txn_currency_code
2495: )
2496: WHERE tmp.resource_assignment_id = l_resource_assignment_tab(i)
2495: )
2496: WHERE tmp.resource_assignment_id = l_resource_assignment_tab(i)
2497: AND tmp.txn_currency_code = l_txn_currency_code_tab(i)
2498: AND EXISTS (select null
2499: from pa_budget_lines bl1
2500: where bl1.resource_assignment_id = tmp.resource_assignment_id
2501: and bl1.txn_currency_code = tmp.txn_currency_code
2502: );
2503:
2502: );
2503:
2504: --print_msg('1.5: update budget lines set override rates to null');
2505: FORALL i IN l_resource_assignment_tab.FIRST .. l_resource_assignment_tab.LAST
2506: UPDATE pa_budget_lines bl
2507: SET bl.txn_bill_rate_override = null
2508: ,bl.burden_cost_rate_override = null
2509: ,bl.txn_cost_rate_override = 1
2510: WHERE bl.resource_assignment_id = l_resource_assignment_tab(i)
2513:
2514: If l_bilRtSetFlag_Tab.count > 0 Then --{
2515: --print_msg('1.1.1: Update budget lines to set override rates');
2516: FORALL i IN l_resource_assignment_tab.FIRST .. l_resource_assignment_tab.LAST
2517: UPDATE pa_budget_lines bl
2518: SET bl.txn_bill_rate_override = l_bill_rate_override_tab(i)
2519: /* bug fix: 5089153 stamp bill rate override only if revenue is changed along with cost
2520: decode(bl.txn_cost_rate_override,0
2521: ,decode(bl.txn_bill_rate_override,1,l_bill_rate_override_tab(i)
2737: AND NVL(ra.resource_rate_based_flag,'N') = 'N'
2738: /* bug fix:5726773: commented out this and added exist clause
2739: * reason: when budget line exists and total plan qty is zero, the above cursor fails
2740: * AND tmp.quantity is not null */
2741: AND EXISTS (select /*+ INDEX(BL PA_BUDGET_LINES_U1) */ null
2742: from pa_budget_lines bl1
2743: where bl1.resource_assignment_id = tmp.resource_assignment_id
2744: and bl1.txn_currency_code = tmp.txn_currency_code
2745: and ((g_source_context = 'BUDGET_LINE'
2738: /* bug fix:5726773: commented out this and added exist clause
2739: * reason: when budget line exists and total plan qty is zero, the above cursor fails
2740: * AND tmp.quantity is not null */
2741: AND EXISTS (select /*+ INDEX(BL PA_BUDGET_LINES_U1) */ null
2742: from pa_budget_lines bl1
2743: where bl1.resource_assignment_id = tmp.resource_assignment_id
2744: and bl1.txn_currency_code = tmp.txn_currency_code
2745: and ((g_source_context = 'BUDGET_LINE'
2746: and bl1.start_date between tmp.start_date and tmp.end_date)
3261: AND tmp.resource_assignment_id = l_resource_assignment_tab(i)
3262: AND tmp.txn_currency_code = l_txn_currency_code_tab(i)
3263: AND l_reset_rate_based_flag_tab(i) = 'Y'
3264: AND EXISTS (select null
3265: from pa_budget_lines bl
3266: WHERE tmp.resource_assignment_id = bl.resource_assignment_id
3267: AND (tmp.txn_currency_code <> bl.txn_currency_code
3268: OR
3269: ( tmp.txn_currency_code = bl.txn_currency_code
3297: AND tmp.txn_currency_code = l_txn_currency_code_tab(i)
3298: AND tmp.start_date = l_start_date_tab(i)
3299: AND l_reset_rate_based_flag_tab(i) = 'Y'
3300: AND EXISTS (select null
3301: from pa_budget_lines bl
3302: WHERE tmp.resource_assignment_id = bl.resource_assignment_id
3303: AND ( (tmp.txn_currency_code <> bl.txn_currency_code)
3304: OR (tmp.txn_currency_code = bl.txn_currency_code
3305: --and bl.start_date NOT BETWEEN tmp.start_date and tmp.end_date
3381: IF P_PA_DEBUG_MODE = 'Y' Then
3382: print_msg('reset the override rates in budgetLines');
3383: End If;
3384: FORALL i IN l_resource_assignment_tab.FIRST .. l_resource_assignment_tab.LAST
3385: UPDATE PA_BUDGET_LINES bl
3386: SET bl.txn_cost_rate_override = decode(l_rwRtSetFlag_Tab(i),'Y',NULL,bl.txn_cost_rate_override)
3387: ,bl.txn_bill_rate_override = decode(l_bilRtSetFlag_Tab(i),'Y',NULL,bl.txn_bill_rate_override)
3388: ,bl.burden_cost_rate_override = decode(l_bdRtSetFlag_Tab(i),'Y',NULL,bl.burden_cost_rate_override)
3389: WHERE bl.resource_assignment_id = l_resource_assignment_tab(i)
3552: Cursor cur_bl_chk(p_ra_id Number,p_txn_cur_code Varchar2) IS
3553: SELECT 'Y'
3554: FROM dual
3555: WHERE EXISTS (select null
3556: from pa_budget_lines bl
3557: ,pa_resource_assignments ra
3558: where ra.resource_assignment_id = p_ra_id
3559: and bl.resource_assignment_id = ra.resource_assignment_id
3560: and NVL(ra.rate_based_flag,'N') = 'Y'
3601: NVL(tmp.txn_revenue,0) <> 0
3602: )
3603: )
3604: AND NOT EXISTS ( select null
3605: from pa_budget_lines bl
3606: where bl.budget_version_id = p_budget_version_id
3607: and bl.resource_assignment_id = tmp.resource_assignment_id
3608: and bl.txn_currency_code = tmp.txn_currency_code
3609: and (p_source_context <> 'BUDGET_LINE'
4332: WHERE tmp.budget_version_id = p_budget_version_id
4333: AND ra.resource_assignment_id = tmp.resource_assignment_id
4334: AND rl.resource_list_member_id = ra.resource_list_member_id
4335: AND EXISTS ( select null
4336: from pa_budget_lines bl
4337: where bl.budget_version_id = p_budget_version_id
4338: and bl.resource_assignment_id = tmp.resource_assignment_id
4339: and bl.txn_currency_code = tmp.txn_currency_code
4340: group by bl.resource_assignment_id,bl.txn_currency_code
4341: having count(*) > 1
4342: );
4343:
4344: CURSOR cur_NonTimePhLines IS
4345: SELECT /*+ INDEX(BL PA_BUDGET_LINES_U1) */ bl.budget_line_id
4346: ,bl.resource_assignment_id
4347: ,bl.txn_currency_code
4348: ,bl.start_date
4349: ,bl.end_date
4377: ,bl.txn_init_burdened_cost
4378: ,bl.txn_init_revenue
4379: ,tmp.new_plan_start_date plan_start_date
4380: ,tmp.new_plan_end_date plan_end_date
4381: FROM pa_budget_lines bl
4382: ,pa_fp_spread_calc_tmp tmp
4383: WHERE bl.budget_version_id = p_budget_version_id
4384: AND bl.resource_assignment_id = tmp.resource_assignment_id
4385: AND bl.txn_currency_code = tmp.txn_currency_code
4525: IF l_budget_line_id_tab.COUNT > 0 THEN --{
4526: InitReturningPlsqlTabls;
4527: --print_msg('Updating budget lines with planning start and end dates for Non-TimePhase budget');
4528: FORALL i IN l_budget_line_id_tab.FIRST .. l_budget_line_id_tab.LAST
4529: UPDATE PA_BUDGET_LINES bl
4530: SET bl.start_date = NVL(l_start_date_tab(i),bl.start_date)
4531: ,bl.end_date = NVL(l_end_date_tab(i),bl.end_date)
4532: WHERE bl.budget_version_id = p_budget_version_id
4533: AND bl.budget_line_id = l_budget_line_id_tab(i)
4653: ,x_return_status OUT NOCOPY Varchar2
4654: ,x_msg_data OUT NOCOPY Varchar2
4655: ) IS
4656: /* This cursor picks all the budget lines which falls beyond the plan dates
4657: * where actual donot exists and deletes the records from pa_budget_lines table
4658: */
4659: CURSOR cur_Delbl_Lines IS
4660: SELECT /*+ INDEX(BL PA_BUDGET_LINES_U1) */ bl.budget_line_id
4661: ,bl.resource_assignment_id
4656: /* This cursor picks all the budget lines which falls beyond the plan dates
4657: * where actual donot exists and deletes the records from pa_budget_lines table
4658: */
4659: CURSOR cur_Delbl_Lines IS
4660: SELECT /*+ INDEX(BL PA_BUDGET_LINES_U1) */ bl.budget_line_id
4661: ,bl.resource_assignment_id
4662: ,bl.txn_currency_code
4663: ,bl.start_date
4664: ,bl.end_date
4691: ,bl.txn_init_raw_cost
4692: ,bl.txn_init_burdened_cost
4693: ,bl.txn_init_revenue
4694: ,tmp.start_date plan_start_date ,tmp.end_date plan_end_date
4695: FROM pa_budget_lines bl
4696: ,pa_fp_rollup_tmp tmp
4697: WHERE bl.budget_version_id = p_budget_version_id
4698: AND bl.resource_assignment_id = tmp.resource_assignment_id
4699: AND bl.txn_currency_code = tmp.txn_currency_code
4813: print_msg('Number of budgetLines deleted['||l_budget_line_id_tab.COUNT||']');
4814: /* Now delete the budget lines */
4815: IF l_budget_line_id_tab.COUNT > 0 THEN
4816: FORALL i IN l_budget_line_id_tab.FIRST .. l_budget_line_id_tab.LAST
4817: DELETE FROM PA_BUDGET_LINES bl
4818: WHERE bl.budget_version_id = p_budget_version_id
4819: AND bl.budget_line_id = l_budget_line_id_tab(i);
4820: END IF;
4821:
4850: ,x_msg_data OUT NOCOPY Varchar2
4851: ) IS
4852:
4853: CURSOR cur_Oldbl_Lines IS
4854: SELECT /*+ INDEX(BL PA_BUDGET_LINES_U1) */ bl.budget_line_id
4855: ,bl.resource_assignment_id
4856: ,bl.txn_currency_code
4857: ,bl.start_date
4858: ,bl.end_date
4885: ,bl.txn_init_raw_cost
4886: ,bl.txn_init_burdened_cost
4887: ,bl.txn_init_revenue
4888: ,tmp.quantity fp_quantity
4889: FROM pa_budget_lines bl
4890: ,pa_fp_rollup_tmp tmp
4891: WHERE bl.budget_version_id = p_budget_version_id
4892: AND bl.resource_assignment_id = tmp.resource_assignment_id
4893: AND bl.txn_currency_code = tmp.txn_currency_code
4896:
4897: /* This cursor picks all the records from rollup tmp where budget line donot exists
4898: * and insert these records into budget lines */
4899: CURSOR cur_newtmp_lines IS
4900: SELECT PA_BUDGET_LINES_S.NEXTVAL
4901: ,tmp.resource_assignment_id
4902: ,tmp.txn_currency_code
4903: ,tmp.start_date
4904: ,tmp.end_date
4909: FROM pa_fp_rollup_tmp tmp
4910: WHERE tmp.budget_version_id = p_budget_version_id
4911: AND NOT EXISTS
4912: (SELECT null
4913: FROM pa_budget_lines bl
4914: WHERE bl.budget_version_id = p_budget_version_id
4915: AND bl.resource_assignment_id = tmp.resource_assignment_id
4916: AND bl.txn_currency_code = tmp.txn_currency_code
4917: AND bl.start_date = tmp.start_date
5028: IF l_budget_line_id_tab.COUNT > 0 THEN
5029: --print_msg('Number of budget Lines updated['||l_budget_line_id_tab.COUNT||']');
5030: FORALL i IN l_budget_line_id_tab.FIRST .. l_budget_line_id_tab.LAST
5031: /* now update the budget lines with new quantity */
5032: UPDATE PA_BUDGET_LINES bl
5033: SET bl.quantity = NVL(bl.quantity,0) + NVL(l_quantity_tab(i),0)
5034: ,bl.txn_raw_cost = decode(p_budget_version_type,'REVENUE',bl.txn_raw_cost,l_txn_raw_cost_tab(i))
5035: ,bl.txn_burdened_cost = decode(p_budget_version_type,'REVENUE',bl.txn_burdened_cost,l_txn_burden_cost_tab(i))
5036: ,bl.txn_revenue = decode(p_budget_version_type,'COST',bl.txn_revenue,l_txn_revenue_tab(i))
5037: WHERE bl.budget_line_id = l_budget_line_id_tab(i)
5038: AND bl.budget_version_id = p_budget_version_id ;
5039:
5040: FORALL i IN l_budget_line_id_tab.FIRST .. l_budget_line_id_tab.LAST
5041: UPDATE PA_BUDGET_LINES bl
5042: SET bl.project_raw_cost = decode(p_budget_version_type,'REVENUE',bl.project_raw_cost
5043: ,decode(bl.project_currency_code,bl.txn_currency_code,bl.txn_raw_cost
5044: ,pa_currency.round_trans_currency_amt1((bl.txn_raw_cost * bl.project_cost_exchange_rate ),bl.project_currency_code)))
5045: ,bl.project_burdened_cost = decode(p_budget_version_type,'REVENUE',bl.project_burdened_cost
5161:
5162: IF l_budget_line_id_tab.COUNT > 0 THEN --{
5163: --print_msg('Number of New budget lines inserted ['||l_budget_line_id_tab.COUNT||']');
5164: FORALL i in l_budget_line_id_tab.FIRST .. l_budget_line_id_tab.LAST
5165: INSERT INTO PA_BUDGET_LINES(
5166: BUDGET_VERSION_ID
5167: ,BUDGET_LINE_ID
5168: ,RESOURCE_ASSIGNMENT_ID
5169: ,TXN_CURRENCY_CODE
5294: FORALL i IN p_resource_assignment_id_tab.FIRST .. p_resource_assignment_id_tab.LAST
5295: UPDATE /*+ INDEX(TMP PA_FP_SPREAD_CALC_TMP_N1) */ pa_fp_spread_calc_tmp tmp
5296: SET (tmp.SYSTEM_REFERENCE_DAT1
5297: ,tmp.SYSTEM_REFERENCE_DAT2) = (select MIN(bl.start_date),MAX(bl.end_date)
5298: from pa_budget_lines bl
5299: where bl.resource_assignment_id = tmp.resource_assignment_id
5300: and bl.txn_currency_code = tmp.txn_currency_code)
5301: WHERE tmp.resource_assignment_id = p_resource_assignment_id_tab(i)
5302: AND tmp.txn_currency_code = p_txn_currency_code_tab(i)
5435: /* Now update the rollup tmp table with the sum of bl line quantities prior to new plan start date*/
5436: UPDATE pa_fp_rollup_tmp tmp
5437: SET tmp.processed_flag = 'Y'
5438: ,tmp.quantity = (SELECT SUM(nvl(bl.quantity,0)-nvl(bl.init_quantity,0))
5439: FROM pa_budget_lines bl
5440: WHERE bl.budget_version_id = p_budget_version_id
5441: AND bl.resource_assignment_id = tmp.resource_assignment_id
5442: AND bl.txn_currency_code = tmp.txn_currency_code
5443: AND bl.end_date < ( tmp.start_date) )
5487: /* Now update the rollup tmp table with the sum of bl line quantities later than new plan end date */
5488: UPDATE pa_fp_rollup_tmp tmp
5489: SET tmp.processed_flag = 'Y'
5490: ,tmp.quantity = (SELECT SUM(nvl(bl.quantity,0)-nvl(bl.init_quantity,0))
5491: FROM pa_budget_lines bl
5492: WHERE bl.budget_version_id = p_budget_version_id
5493: AND bl.resource_assignment_id = tmp.resource_assignment_id
5494: AND bl.txn_currency_code = tmp.txn_currency_code
5495: AND bl.start_date > ( tmp.end_date) )
5556: If p_pa_debug_mode = 'Y' Then
5557: pa_debug.init_err_stack('PA_FP_CALC_UTILS.populate_blTxnCurCombo');
5558: print_msg('Entered populate_blTxnCurCombo api');
5559: End If;
5560: SELECT /*+ INDEX(BL PA_BUDGET_LINES_U1) */
5561: bl.resource_assignment_id
5562: ,bl.txn_currency_code
5563: BULK COLLECT INTO
5564: l_raId_Tab
5562: ,bl.txn_currency_code
5563: BULK COLLECT INTO
5564: l_raId_Tab
5565: ,l_TxnCur_Tab
5566: FROM pa_budget_lines bl
5567: ,pa_fp_spread_calc_tmp tmp
5568: WHERE bl.budget_version_id = p_budget_version_id
5569: AND bl.resource_assignment_id = tmp.resource_assignment_id
5570: AND bl.txn_currency_code <> tmp.txn_currency_code
5608: /* Now updates the other attributes for the newly inserted rows */
5609: FORALL i IN l_raId_Tab.FIRST .. l_raId_Tab.LAST
5610: UPDATE /*+ INDEX(TMP PA_FP_SPREAD_CALC_TMP_N1) */ pa_fp_spread_calc_tmp tmp
5611: SET tmp.quantity = (select sum(bl.quantity)
5612: from pa_budget_lines bl
5613: where bl.budget_version_id = tmp.budget_version_id
5614: and bl.resource_assignment_id = tmp.resource_assignment_id
5615: and bl.txn_currency_code = tmp.txn_currency_code)
5616: ,tmp.system_reference_var2 = NULL
5801: ,tmp.MFC_COST_CHANGE_FLAG = decode(NVL(tmp.OLD_MFC_COST_TYPE_ID,-999),NVL(tmp.NEW_MFC_COST_TYPE_ID,-999),'N','Y')
5802: ,tmp.SYSTEM_REFERENCE_VAR1 = (SELECT 'Y'
5803: FROM dual
5804: WHERE EXISTS ( select null
5805: from pa_budget_lines bl
5806: where bl.budget_version_id = tmp.budget_version_id
5807: and bl.resource_assignment_id = tmp.resource_assignment_id
5808: and bl.txn_currency_code <> tmp.txn_currency_code
5809: GROUP BY bl.resource_assignment_id,bl.txn_currency_code
6527: IF P_PA_DEBUG_MODE = 'Y' Then
6528: print_msg('Delete zero Quantity budget lines where actuals donot exists');
6529: End If;
6530: FORALL i IN x_resource_assignment_tab.FIRST .. x_resource_assignment_tab.LAST
6531: DELETE FROM pa_budget_lines bl
6532: WHERE bl.budget_version_id = p_budget_version_id
6533: AND bl.resource_assignment_id = x_resource_assignment_tab(i)
6534: AND NVL(bl.quantity,0) = 0
6535: AND (NVL(bl.init_quantity,0) = 0
7151: ,BL_CREATED_BY
7152: ,BL_CREATION_DATE
7153: ,mfc_cost_change_flag
7154: )
7155: SELECT /*+ INDEX(BL PA_BUDGET_LINES_U1) */
7156: bl.RESOURCE_ASSIGNMENT_ID --resource_assignment_id
7157: ,bl.BUDGET_VERSION_ID --budget_version_id
7158: ,tmp.BUDGET_VERSION_TYPE
7159: ,bl.BUDGET_LINE_ID
7237: ,bl.TRANSFER_PRICE_RATE
7238: ,bl.CREATED_BY
7239: ,bl.CREATION_DATE
7240: ,tmp.mfc_cost_change_flag
7241: FROM PA_BUDGET_LINES bl
7242: ,PA_FP_SPREAD_CALC_TMP tmp
7243: WHERE bl.budget_version_id = p_budget_verson_id
7244: AND bl.budget_version_id = tmp.budget_version_id
7245: AND bl.resource_assignment_id = tmp.resource_assignment_id
7334: /* Bug Fix 4332086
7335: Whenever currency is overridden along with a change in quantity in the workplan flow
7336: in Update Task Details page, the following piece of code gets executed.
7337:
7338: This code caches several attributes from pa_budget_lines table and will use them in the
7339: later part of the flow, thus causing the above bug. When ever currency code is overwritten
7340: we need to use the new currency's conversion attributes, but where as this code will use
7341: old currency's conversion attributes.
7342:
7639: , cache.BL_CREATED_BY
7640: , cache.BL_CREATION_DATE
7641: FROM pa_fp_rollup_tmp tmp
7642: ,pa_fp_spread_calc_tmp1 cache
7643: ,pa_budget_lines bl
7644: WHERE tmp.budget_version_id = p_budget_verson_id
7645: AND tmp.budget_version_id = cache.budget_version_id
7646: AND tmp.resource_assignment_id = cache.resource_assignment_id
7647: AND tmp.txn_currency_code = cache.txn_currency_code
7645: AND tmp.budget_version_id = cache.budget_version_id
7646: AND tmp.resource_assignment_id = cache.resource_assignment_id
7647: AND tmp.txn_currency_code = cache.txn_currency_code
7648: AND tmp.start_date = cache.start_date
7649: and cache.budget_version_id = bl.budget_version_id(+) --Bug 4224464. Added the join with pa_budget_lines
7650: AND cache.resource_assignment_id = bl.resource_assignment_id(+)
7651: AND cache.txn_currency_code = bl.txn_currency_code(+)
7652: AND cache.start_date = bl.start_date(+);
7653:
8107:
8108: l_debug_mode VARCHAR2(30);
8109: l_stage NUMBER;
8110: l_populate_mrc_tab_flag Varchar2(10) := 'N'; --MRC Elimination Changes:NVL(PA_FP_CALC_PLAN_PKG.G_populate_mrc_tab_flag,'N');
8111: l_existing_bl_id pa_budget_lines.budget_line_id%TYPE;
8112:
8113: l_bl_raId_tab pa_plsql_datatypes.NumTabTyp;
8114: l_bl_sDate_tab pa_plsql_datatypes.dateTabTyp;
8115: l_bl_edate_tab pa_plsql_datatypes.dateTabTyp;
8219: l_rate_base_flag_tab pa_plsql_datatypes.Char1TabTyp;
8220:
8221:
8222: CURSOR Cur_RollupLines IS
8223: SELECT /*+ INDEX(B PA_BUDGET_LINES_U2) */ r.resource_assignment_id
8224: ,b.start_date
8225: ,b.end_date
8226: ,b.period_name
8227: ,r.budget_line_id
8331: ,r.BL_CREATED_BY
8332: ,r.BL_CREATION_DATE
8333: ,NVL(ra.rate_based_flag,'N')
8334: FROM pa_fp_rollup_tmp r
8335: ,pa_budget_lines b
8336: ,pa_resource_assignments ra
8337: WHERE r.budget_line_id = b.budget_line_id
8338: AND b.budget_version_id = p_budget_version_id
8339: AND b.budget_version_id = r.budget_version_id --Bug 7520706
8362: ,bl.resource_assignment_id
8363: ,bl.txn_currency_code
8364: ,bl.project_currency_code
8365: ,bl.projfunc_currency_code
8366: FROM pa_budget_lines bl
8367: ,pa_fp_rollup_tmp r
8368: WHERE bl.budget_version_id = p_budget_version_id
8369: AND bl.budget_line_id = r.budget_line_id
8370: AND NVL(r.processed_flag,'Y') <> 'N';
8395: ,bl.resource_assignment_id
8396: ,bl.txn_currency_code
8397: ,bl.project_currency_code
8398: ,bl.projfunc_currency_code
8399: FROM pa_budget_lines bl
8400: ,pa_fp_spread_calc_tmp2 tmp2
8401: WHERE bl.budget_version_id = p_budget_version_id
8402: AND tmp2.budget_version_id = bl.budget_version_id
8403: AND tmp2.resource_assignment_id = bl.resource_assignment_id
8899: ,bl.pfc_cur_conv_rejection_code
8900: ,bl.pc_cur_conv_rejection_code
8901: ,bl.project_currency_code
8902: ,bl.projfunc_currency_code
8903: FROM pa_budget_lines bl
8904: WHERE bl.budget_version_id = p_budget_version_id
8905: AND EXISTS (select null
8906: From pa_fp_spread_calc_tmp1 tmp1
8907: where tmp1.budget_version_id = p_budget_version_id
9118: */
9119: --print_msg('Bulk updating budget lines');
9120: /* Now Bulk Update the budget Lines */
9121: FORALL i IN l_bl_budget_line_id_tab.FIRST .. l_bl_budget_line_id_tab.LAST SAVE EXCEPTIONS
9122: UPDATE pa_budget_lines bl
9123: SET bl.txn_currency_code = l_bl_txn_curcode_tab(i)
9124: ,bl.quantity = l_bl_quantity_tab(i)
9125: /* Bug fix:4900436 */
9126: ,bl.display_quantity = decode(g_wp_version_flag,'Y',l_bl_quantity_tab(i)
9588: Populate_tmp2Plsql_tab;
9589: IF l_tmp2_budget_line_id_tab.COUNT > 0 THEN --{
9590: --print_msg('Bulk updating dup val index exception rows');
9591: FORALL i IN l_tmp2_budget_line_id_tab.FIRST .. l_tmp2_budget_line_id_tab.LAST
9592: UPDATE pa_budget_lines bl
9593: SET bl.txn_currency_code = l_tmp2_txn_curr_code_tab(i)
9594: ,bl.quantity = l_tmp2_quantity_tab(i)
9595: /* Bug fix:4900436 */
9596: ,bl.display_quantity = decode(g_wp_version_flag,'Y',l_tmp2_quantity_tab(i)
9616: WHERE bl.budget_line_id = l_tmp2_budget_line_id_tab(i);
9617:
9618: --print_msg(' Num Of rows UPDATED ['||sql%rowcount||']');
9619: FORALL i IN l_tmp2_budget_line_id_tab.FIRST .. l_tmp2_budget_line_id_tab.LAST
9620: UPDATE pa_budget_lines bl
9621: SET bl.txn_raw_cost = decode((nvl(bl.txn_init_raw_cost,0) + pa_currency.round_trans_currency_amt1(
9622: (nvl(bl.quantity,0) - nvl(bl.init_quantity,0)) *
9623: (nvl(bl.txn_cost_rate_override,nvl(txn_standard_cost_rate,0))),bl.txn_currency_code)),0,NULL,
9624: (nvl(bl.txn_init_raw_cost,0) + pa_currency.round_trans_currency_amt1(
9671: END IF;
9672:
9673: /* Now delete the duplval budget lines */
9674: FORALL i IN l_del_budget_line_id_tab.FIRST .. l_del_budget_line_id_tab.LAST
9675: DELETE FROM pa_budget_lines bl
9676: WHERE bl.budget_line_id = l_del_budget_line_id_tab(i);
9677: END IF; --}
9678:
9679: /* MRC ehancements changes: */
9733: ,bl.projfunc_rev_rate_type
9734: ,bl.projfunc_rev_exchange_rate
9735: ,bl.projfunc_rev_rate_date_type
9736: ,bl.projfunc_rev_rate_date
9737: FROM pa_budget_lines bl
9738: WHERE bl.budget_line_id = tmp.budget_line_id
9739: )
9740: WHERE tmp.budget_line_id = l_tmp2_budget_line_id_tab(exBlId);
9741:
9808: ,bl.projfunc_rev_rate_type
9809: ,bl.projfunc_rev_exchange_rate
9810: ,bl.projfunc_rev_rate_date_type
9811: ,bl.projfunc_rev_rate_date
9812: FROM pa_budget_lines bl
9813: WHERE bl.budget_line_id = l_tmp2_budget_line_id_tab(exBlId)
9814: AND NOT EXISTS ( SELECT NULL
9815: FROM PA_FP_ROLLUP_TMP tmp1
9816: WHERE tmp1.budget_line_id = l_tmp2_budget_line_id_tab(exBlId)
9908:
9909: /* This cursor picks budget line attributes which needs to be retained and updated even if there was no change detected by
9910: core calculate api flow -- that is, no qty/amt/rate changed */
9911: CURSOR blAttribDetails IS
9912: SELECT /*+ INDEX(BL PA_BUDGET_LINES_U1) */ cache.resource_assignment_id
9913: ,cache.start_date
9914: ,cache.period_name
9915: ,cache.end_Date
9916: ,cache.budget_version_id
10011: , cache.TXN_DISCOUNT_PERCENTAGE
10012: , cache.TRANSFER_PRICE_RATE
10013: , cache.BL_CREATED_BY
10014: , cache.BL_CREATION_DATE
10015: FROM pa_fp_spread_calc_tmp1 cache, pa_budget_lines bl
10016: WHERE cache.budget_version_id = p_budget_verson_id
10017: AND cache.budget_version_id = bl.budget_version_id
10018: AND cache.resource_assignment_id = bl.resource_assignment_id
10019: AND cache.txn_currency_code = bl.txn_currency_code
10017: AND cache.budget_version_id = bl.budget_version_id
10018: AND cache.resource_assignment_id = bl.resource_assignment_id
10019: AND cache.txn_currency_code = bl.txn_currency_code
10020: AND cache.start_date = bl.start_date
10021: /*If a new budget line (not exists in pa_budget_lines) is attempted to be created with all amts/qty/rate as null but
10022: * with dffs/mc/change reason, from amg flow, that budget line would not be selected due to the join with pa_budget_lines.
10023: * This is only intended as budget lines with null amts/qty are not maintained in pa_budget_lines any more.
10024: */
10025: AND NOT EXISTS (SELECT 'X' FROM pa_fp_rollup_tmp tmp WHERE tmp.budget_version_id = cache.budget_version_id
10018: AND cache.resource_assignment_id = bl.resource_assignment_id
10019: AND cache.txn_currency_code = bl.txn_currency_code
10020: AND cache.start_date = bl.start_date
10021: /*If a new budget line (not exists in pa_budget_lines) is attempted to be created with all amts/qty/rate as null but
10022: * with dffs/mc/change reason, from amg flow, that budget line would not be selected due to the join with pa_budget_lines.
10023: * This is only intended as budget lines with null amts/qty are not maintained in pa_budget_lines any more.
10024: */
10025: AND NOT EXISTS (SELECT 'X' FROM pa_fp_rollup_tmp tmp WHERE tmp.budget_version_id = cache.budget_version_id
10026: AND tmp.resource_assignment_id = cache.resource_assignment_id
10019: AND cache.txn_currency_code = bl.txn_currency_code
10020: AND cache.start_date = bl.start_date
10021: /*If a new budget line (not exists in pa_budget_lines) is attempted to be created with all amts/qty/rate as null but
10022: * with dffs/mc/change reason, from amg flow, that budget line would not be selected due to the join with pa_budget_lines.
10023: * This is only intended as budget lines with null amts/qty are not maintained in pa_budget_lines any more.
10024: */
10025: AND NOT EXISTS (SELECT 'X' FROM pa_fp_rollup_tmp tmp WHERE tmp.budget_version_id = cache.budget_version_id
10026: AND tmp.resource_assignment_id = cache.resource_assignment_id
10027: AND tmp.txn_currency_code = cache.txn_currency_code
10598:
10599: END update_dffcols;
10600:
10601: /*Bug:4272944: Added new procedure to insert zero qty budget lines from pa_fp_spread_calc_tmp1 to
10602: *pa_budget_lines. This fix is done specific to Funding of Autobase line is failing.
10603: *donot populate or use pa_fp_spread_calc_tmp1 table for any other purpose.
10604: *Note: Calling API may populate this table only for AMG/MSP/Autobaseline purpose.
10605: */
10606: PROCEDURE InsertFunding_ReqdLines
10618: ,tmp.txn_currency_code
10619: ,tmp.start_date
10620: ,tmp.end_date
10621: ,tmp.period_name
10622: ,pa_budget_lines_s.nextval
10623: FROM pa_fp_spread_calc_tmp1 tmp
10624: WHERE tmp.budget_version_id = p_budget_verson_id;
10625:
10626: l_bl_source VARCHAR2(10) := 'AB'; --'indicates lines created for funding auto baseline'
10678: IF l_budget_line_id_tab.COUNT > 0 THEN --{
10679: BEGIN --{
10680:
10681: FORALL i IN l_budget_line_id_tab.FIRST .. l_budget_line_id_tab.LAST
10682: INSERT INTO PA_BUDGET_LINES bl
10683: (bl.BUDGET_LINE_ID
10684: ,bl.RESOURCE_ASSIGNMENT_ID --resource_assignment_id
10685: ,bl.BUDGET_VERSION_ID --budget_version_id
10686: ,bl.TXN_CURRENCY_CODE --txn_currency_code