[Home] [Help]
256: CURSOR cur_CorruptedBls IS
257: SELECT tmp.resource_assignment_id
258: ,rl.alias resource_name
259: ,tmp.txn_currency_code
260: FROM pa_fp_spread_calc_tmp tmp
261: ,pa_resource_assignments ra
262: ,pa_resource_list_members rl
263: WHERE tmp.budget_version_id = p_budget_version_id
264: AND ra.resource_assignment_id = tmp.resource_assignment_id
290: *1:SELECT STATEMENT :(cost=22,rows=1)
291: * 2:NESTED LOOPS :(cost=22,rows=1)
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)
291: * 2:NESTED LOOPS :(cost=22,rows=1)
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)
303: CURSOR cur_blDatesCheck IS
304: SELECT tmp.resource_assignment_id
305: ,rl.alias resource_name
306: ,tmp.txn_currency_code
307: FROM pa_fp_spread_calc_tmp tmp
308: ,pa_resource_assignments ra
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
808: CURSOR cur_validate IS
809: SELECT tmp.resource_assignment_id
810: ,tmp.txn_currency_code
811: ,tmp.start_date
812: FROM pa_fp_spread_calc_tmp tmp
813: WHERE tmp.budget_version_id = g_budget_version_id
814: AND (tmp.start_date is NULL OR tmp.end_date is NULL);
815:
816: l_miss_num Number := fnd_api.g_miss_num;
963: If p_pa_debug_mode = 'Y' Then
964: print_msg(' Inserting records into spread calc tmp table');
965: End If;
966: FORALL i IN x_resource_assignment_tab.FIRST .. x_resource_assignment_tab.LAST
967: INSERT INTO pa_fp_spread_calc_tmp
968: (RESOURCE_ASSIGNMENT_ID --resource_assignment_id
969: ,DELETE_BL_FLAG --delete_budget_lines_flag
970: ,SPREAD_AMTS_FLAG --spread_amts_flag
971: ,TXN_CURRENCY_CODE --txn_currency_code
1136: ,tmp.txn_currency_code
1137: ,tmp.cost_rate_override
1138: ,tmp.burden_cost_rate_override
1139: ,tmp.bill_rate_override
1140: FROM pa_fp_spread_calc_tmp tmp
1141: WHERE tmp.budget_version_id = g_budget_version_id
1142: AND tmp.cost_rate_override = 0
1143: AND tmp.burden_cost_rate_override = 0
1144: AND tmp.bill_rate_override = 1
1218: ,rlm.unit_of_measure resource_uom
1219: ,tmp.start_date
1220: ,tmp.end_date
1221: ,NVL(tmp.delete_bl_flag,'N') delete_bl_flag
1222: FROM pa_fp_spread_calc_tmp tmp
1223: ,pa_fp_spread_calc_tmp2 tmp2
1224: ,pa_resource_assignments ra
1225: ,pa_resource_list_members rlm
1226: WHERE tmp.budget_version_id = p_budget_version_id
1219: ,tmp.start_date
1220: ,tmp.end_date
1221: ,NVL(tmp.delete_bl_flag,'N') delete_bl_flag
1222: FROM pa_fp_spread_calc_tmp tmp
1223: ,pa_fp_spread_calc_tmp2 tmp2
1224: ,pa_resource_assignments ra
1225: ,pa_resource_list_members rlm
1226: WHERE tmp.budget_version_id = p_budget_version_id
1227: AND ra.resource_assignment_id = tmp.resource_assignment_id
1301: x_msg_data := NULL;
1302: IF P_PA_DEBUG_MODE = 'Y' Then
1303: print_msg('Entered Compare_bdgtLine_Values API');
1304: End If;
1305: DELETE FROM pa_fp_spread_calc_tmp2
1306: WHERE budget_version_id = p_budget_version_id;
1307: l_res_Asgn_Id_Tab.delete;
1308: l_txn_cur_code_Tab.delete;
1309: l_start_date_tab.delete;
1324: l_res_Asgn_Id_Tab
1325: ,l_txn_cur_code_Tab
1326: ,l_start_date_tab
1327: ,l_end_date_tab
1328: FROM pa_fp_spread_calc_tmp tmp
1329: WHERE tmp.budget_version_id = p_budget_version_id;
1330:
1331: IF l_res_Asgn_Id_Tab.COUNT > 0 Then --{
1332: IF P_PA_DEBUG_MODE = 'Y' Then
1332: IF P_PA_DEBUG_MODE = 'Y' Then
1333: print_msg('NumOf Lines inserted['||l_res_Asgn_Id_Tab.count||']');
1334: End If;
1335: FORALL i IN l_res_Asgn_Id_Tab.FIRST .. l_res_Asgn_Id_Tab.LAST
1336: INSERT INTO pa_fp_spread_calc_tmp2
1337: (BUDGET_VERSION_ID
1338: ,BUDGET_VERSION_TYPE
1339: ,RESOURCE_ASSIGNMENT_ID
1340: ,TXN_CURRENCY_CODE
1352: IF P_PA_DEBUG_MODE = 'Y' Then
1353: print_msg('Inserting records into sprdcalctmp2 for resource assignment context');
1354: End If;
1355: FORALL i IN l_res_Asgn_Id_Tab.FIRST .. l_res_Asgn_Id_Tab.LAST
1356: UPDATE /*+ INDEX(TMP PA_FP_SPREAD_CALC_TMP2_N1) */ pa_fp_spread_calc_tmp2 tmp
1357: SET ( tmp.quantity
1358: ,tmp.txn_raw_cost
1359: ,tmp.txn_burdened_cost
1360: ,tmp.txn_revenue
1404: WHERE tmp.resource_assignment_id = l_res_Asgn_Id_Tab(i)
1405: AND tmp.txn_currency_code = l_txn_cur_code_Tab(i);
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
1422: IF P_PA_DEBUG_MODE = 'Y' Then
1423: print_msg('Updating calcTmp2 with budgetLine values');
1424: End If;
1425: FORALL i IN l_res_Asgn_Id_Tab.FIRST .. l_res_Asgn_Id_Tab.LAST
1426: UPDATE /*+ INDEX(TMP PA_FP_SPREAD_CALC_TMP2_N1) */ pa_fp_spread_calc_tmp2 tmp
1427: SET (tmp.avg_cost_rate_override
1428: ,tmp.avg_burden_rate_override
1429: ,tmp.avg_bill_rate_override
1430: /* Bug fix:4693839 */
1461: OR tmp.start_date is NULL
1462: );
1463:
1464: FORALL i IN l_res_Asgn_Id_Tab.FIRST .. l_res_Asgn_Id_Tab.LAST
1465: UPDATE /*+ INDEX(TMP PA_FP_SPREAD_CALC_TMP2_N1) */ pa_fp_spread_calc_tmp2 tmp
1466: SET (tmp.quantity
1467: ,tmp.txn_raw_cost
1468: ,tmp.txn_burdened_cost
1469: ,tmp.txn_revenue
1599: );
1600: --print_msg('Number of budgtLines got updated['||l_res_Asgn_Id_Tab.count||']');
1601: END IF; --}
1602: /** added this for debug testing
1603: FOR tmp in (select * from pa_fp_spread_calc_tmp2 where budget_version_id = p_budget_version_id) LOOP
1604: print_msg('Res['||tmp.resource_assignment_id||']blTxncur['||tmp.txn_currency_code||'blQty['||tmp.quantity||']');
1605: print_msg('RawCst['||tmp.txn_raw_cost||']BdCst['||tmp.txn_burdened_cost||']Rev['||tmp.txn_revenue||']');
1606: print_msg('cstRt['||tmp.cost_rate||']cstRtOvr['||tmp.cost_rate_override||']BdRt['||tmp.burden_cost_rate||']');
1607: print_msg('BdRtOv['||tmp.burden_cost_rate_override||']BilRt['||tmp.bill_rate||']BilRtOvr['||tmp.bill_rate_override||']');
2061: end loop;
2062: --*/
2063:
2064: FORALL i IN l_rowid_tab.FIRST .. l_rowid_tab.LAST
2065: UPDATE pa_fp_spread_calc_tmp tmp
2066: SET tmp.QUANTITY_CHANGED_FLAG = NVL(l_quantity_ch_flag_tab(i),'N')
2067: ,tmp.COST_RATE_CHANGED_FLAG = NVL(l_costRt_ch_flag_tab(i),'N')
2068: ,tmp.BURDEN_RATE_CHANGED_FLAG = NVL(l_burdRt_ch_flag_tab(i),'N')
2069: ,tmp.BILL_RATE_CHANGED_FLAG = NVL(l_billRt_ch_flag_tab(i),'N')
2192: ,NVL(tmp.SP_FIX_DATE_CHANGE_FLAG,'N') spfix_date_change_flag
2193: ,NVL(tmp.MFC_COST_CHANGE_FLAG,'N') mfc_cost_change_flag
2194: ,NVL(tmp.RLM_ID_CHANGE_FLAG,'N') rlm_id_change_flag
2195: ,NVL(tmp.system_reference_var1 ,'N') ra_in_multi_cur_flag
2196: FROM pa_fp_spread_calc_tmp tmp
2197: ,pa_resource_assignments ra
2198: WHERE tmp.budget_version_id = p_budget_version_id
2199: AND tmp.resource_assignment_id = ra.resource_assignment_id
2200: AND ra.rate_based_flag = 'N'
2297: WHERE bl.resource_assignment_id = l_resource_assignment_tab(i)
2298: AND bl.txn_currency_code = l_txn_currency_code_tab(i);
2299:
2300: FORALL i IN l_resource_assignment_tab.FIRST .. l_resource_assignment_tab.LAST
2301: UPDATE /*+ INDEX(TMP PA_FP_SPREAD_CALC_TMP_N1) */ pa_fp_spread_calc_tmp tmp
2302: SET tmp.cost_rate_override = l_cost_rate_override_tab(i)
2303: ,tmp.burden_cost_rate_override = l_burden_rate_override_tab(i)
2304: ,tmp.bill_rate_override = l_bill_rate_override_tab(i)
2305: WHERE tmp.resource_assignment_id = l_resource_assignment_tab(i)
2360: ,NVL(tmp.MFC_COST_CHANGE_FLAG,'N') mfc_cost_change_flag
2361: ,NVL(tmp.RLM_ID_CHANGE_FLAG,'N') rlm_id_change_flag
2362: ,NVL(tmp.system_reference_var1 ,'N') ra_in_multi_cur_flag
2363: ,NVL(tmp.DELETE_BL_FLAG,'N') delete_bl_flag
2364: FROM pa_fp_spread_calc_tmp tmp
2365: ,pa_resource_assignments ra
2366: WHERE tmp.budget_version_id = p_budget_version_id
2367: AND tmp.resource_assignment_id = ra.resource_assignment_id
2368: AND nvl(ra.rate_based_flag,'N') = 'N'
2494: IF P_PA_DEBUG_MODE = 'Y' Then
2495: print_msg('1.4: Update spread calc tmp with quantity = raw cost');
2496: End If;
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
2557: ,((tmp.txn_revenue - nvl(tmp.bl_txn_init_revenue,0))/
2558: (nvl(tmp.bl_txn_raw_cost,0)-nvl(tmp.bl_txn_init_raw_cost,0)))))
2559: ,decode(nvl(tmp.burden_cost_changed_flag,'N'), 'Y'
2560: ,nvl(l_burden_rate_override_tab(i),rtx.txn_burden_cost_rate_override))
2561: from pa_fp_spread_calc_tmp tmp
2562: where tmp.resource_assignment_id = rtx.resource_assignment_id
2563: and tmp.txn_currency_code = rtx.txn_currency_code
2564: and tmp.raw_cost_changed_flag = 'Y'
2565: )
2569:
2570: End If;
2571: --print_msg('1.1.3: update spread calc tmp to rate overrides');
2572: FORALL i IN l_resource_assignment_tab.FIRST .. l_resource_assignment_tab.LAST
2573: UPDATE /*+ INDEX(TMP PA_FP_SPREAD_CALC_TMP_N1) */ pa_fp_spread_calc_tmp tmp
2574: SET tmp.re_spread_amts_flag =
2575: decode(tmp.re_spread_amts_flag,'Y','Y',l_bl_resprd_flag_tab(i))
2576: ,tmp.delete_bl_flag = decode(tmp.delete_bl_flag,'Y','Y'
2577: ,decode(l_bl_resprd_flag_tab(i),'Y','Y',tmp.delete_bl_flag))
2682: ,NVL(tmp.bill_rate_changed_flag,'N') bill_rate_changed_flag
2683: ,NVL(tmp.raw_cost_changed_flag,'N') raw_cost_changed_flag
2684: ,NVL(tmp.burden_cost_changed_flag,'N') burden_cost_changed_flag
2685: ,NVL(tmp.revenue_changed_flag,'N') revenue_changed_flag
2686: FROM pa_fp_spread_calc_tmp tmp
2687: ,pa_resource_assignments ra
2688: ,pa_resource_list_members rlm
2689: WHERE ra.budget_version_id = p_budget_version_id
2690: AND ra.resource_assignment_id = tmp.resource_assignment_id
2728: ,NVL(tmp.raw_cost_changed_flag,'N') raw_cost_changed_flag
2729: ,NVL(tmp.burden_cost_changed_flag,'N') burden_cost_changed_flag
2730: ,NVL(tmp.revenue_changed_flag,'N') revenue_changed_flag
2731: ,tmp.bill_markup_percentage
2732: FROM pa_fp_spread_calc_tmp tmp
2733: ,pa_resource_assignments ra
2734: ,pa_resource_list_members rlm
2735: WHERE ra.budget_version_id = p_budget_version_id
2736: AND ra.resource_assignment_id = tmp.resource_assignment_id
3257:
3258: /* before resetting the rate base flag check whenter budget lines exists for this planning
3259: * resource with other currency. if so abort the process
3260: */
3261: DELETE FROM pa_fp_spread_calc_tmp2;
3262: FORALL i IN l_resource_assignment_tab.FIRST .. l_resource_assignment_tab.LAST
3263: INSERT INTO pa_fp_spread_calc_tmp2 tmp2
3264: (budget_version_id
3265: ,resource_assignment_id
3259: * resource with other currency. if so abort the process
3260: */
3261: DELETE FROM pa_fp_spread_calc_tmp2;
3262: FORALL i IN l_resource_assignment_tab.FIRST .. l_resource_assignment_tab.LAST
3263: INSERT INTO pa_fp_spread_calc_tmp2 tmp2
3264: (budget_version_id
3265: ,resource_assignment_id
3266: ,txn_currency_code
3267: ,task_id
3266: ,txn_currency_code
3267: ,task_id
3268: ,resource_name
3269: )
3270: SELECT /*+ INDEX(TMP PA_FP_SPREAD_CALC_TMP_N1) */ p_budget_version_id
3271: ,tmp.resource_assignment_id
3272: ,tmp.txn_currency_code
3273: ,tmp.task_id
3274: ,tmp.resource_name
3271: ,tmp.resource_assignment_id
3272: ,tmp.txn_currency_code
3273: ,tmp.task_id
3274: ,tmp.resource_name
3275: FROM pa_fp_spread_calc_tmp tmp
3276: WHERE tmp.budget_version_id = p_budget_version_id
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'
3288: );
3289:
3290: ELSE -- source context = BUDGET LINE then
3291:
3292: DELETE FROM pa_fp_spread_calc_tmp2;
3293: FORALL i IN l_resource_assignment_tab.FIRST .. l_resource_assignment_tab.LAST
3294: INSERT INTO pa_fp_spread_calc_tmp2 tmp2
3295: (budget_version_id
3296: ,resource_assignment_id
3290: ELSE -- source context = BUDGET LINE then
3291:
3292: DELETE FROM pa_fp_spread_calc_tmp2;
3293: FORALL i IN l_resource_assignment_tab.FIRST .. l_resource_assignment_tab.LAST
3294: INSERT INTO pa_fp_spread_calc_tmp2 tmp2
3295: (budget_version_id
3296: ,resource_assignment_id
3297: ,txn_currency_code
3298: ,task_id
3299: ,resource_name
3300: ,start_date
3301: ,end_date
3302: )
3303: SELECT /*+ INDEX(TMP PA_FP_SPREAD_CALC_TMP_N1) */ p_budget_version_id
3304: ,tmp.resource_assignment_id
3305: ,tmp.txn_currency_code
3306: ,tmp.task_id
3307: ,tmp.resource_name
3306: ,tmp.task_id
3307: ,tmp.resource_name
3308: ,tmp.start_date
3309: ,tmp.end_date
3310: FROM pa_fp_spread_calc_tmp tmp
3311: WHERE tmp.budget_version_id = p_budget_version_id
3312: AND tmp.resource_assignment_id = l_resource_assignment_tab(i)
3313: AND tmp.txn_currency_code = l_txn_currency_code_tab(i)
3314: AND tmp.start_date = l_start_date_tab(i)
3330: ,tmp2.txn_currency_code
3331: ,tmp2.task_id
3332: ,tmp2.resource_name
3333: ,tmp2.start_date
3334: FROM pa_fp_spread_calc_tmp2 tmp2
3335: WHERE tmp2.budget_version_id = p_budget_version_id ) LOOP --{
3336:
3337: g_stage := 'PA_FP_RATE_BASE_QTY_REQD:RaId['||i.resource_assignment_id||']';
3338: g_stage := 'Currency['||i.txn_currency_code||']SDate['||i.start_date||']';
3427: End If;
3428: /* Now update the rollup tmp with the new qty and raw cost */
3429: print_msg('Updating tmp table with qty = rawcost or burden cost');
3430: FORALL i IN l_resource_assignment_tab.FIRST .. l_resource_assignment_tab.LAST
3431: UPDATE /*+ INDEX(TMP PA_FP_SPREAD_CALC_TMP_N1) */ pa_fp_spread_calc_tmp tmp
3432: SET tmp.quantity = NVL(l_quantity_tab(i),tmp.quantity)
3433: ,tmp.txn_raw_cost = NVL(l_raw_cost_tab(i),tmp.txn_raw_cost)
3434: ,tmp.cost_rate_override = decode(l_rwRtSetFlag_Tab(i),'Y',NULL,NVL(l_rw_cost_rate_override_tab(i),tmp.cost_rate_override))
3435: ,tmp.bill_rate_override = decode(l_bilRtSetFlag_Tab(i),'Y',NULL,NVL(l_bill_rate_override_tab(i),tmp.bill_rate_override))
3565: ,NVL(tmp.delete_bl_flag,'N') delete_bl_flag
3566: ,NVL(tmp.raw_cost_changed_flag,'N') raw_cost_changed_flag
3567: ,NVL(tmp.burden_cost_changed_flag,'N') burden_cost_changed_flag
3568: ,NVL(tmp.revenue_changed_flag,'N') revenue_changed_flag
3569: FROM pa_fp_spread_calc_tmp tmp
3570: ,pa_resource_assignments ra
3571: ,pa_resource_list_members rlm
3572: WHERE ra.budget_version_id = p_budget_version_id
3573: AND ra.resource_assignment_id = tmp.resource_assignment_id
3593: Cursor cur_tmp_chk(p_ra_id Number,p_txn_cur_code Varchar2) IS
3594: SELECT 'Y'
3595: FROM dual
3596: WHERE EXISTS (select null
3597: from pa_fp_spread_calc_tmp bl
3598: where bl.resource_assignment_id = p_ra_id
3599: /* Bug fix:4083873 and bl.txn_currency_code = p_txn_cur_code */
3600: and nvl(bl.quantity,0) <> 0
3601: );
3613: ,tmp.start_date
3614: ,tmp.end_date
3615: ,tmp.bl_quantity
3616: ,rlm.unit_of_measure uom
3617: FROM pa_fp_spread_calc_tmp tmp
3618: ,pa_resource_assignments ra
3619: ,pa_resource_list_members rlm
3620: WHERE tmp.budget_version_id = p_budget_version_id
3621: AND ra.resource_assignment_id = tmp.resource_assignment_id
3750: g_stage := 'Check_ratebased_pltrxns:102';
3751: --print_msg(g_stage);
3752: l_reset_plsql_tab_flag := 'Y';
3753: FORALL i IN l_resource_assignment_tab.FIRST ..l_resource_assignment_tab.LAST
3754: UPDATE /*+ INDEX(TMP PA_FP_SPREAD_CALC_TMP_N1) */ pa_fp_spread_calc_tmp tmp
3755: SET tmp.quantity = l_quantity_tab(i)
3756: ,tmp.txn_raw_cost = decode(nvl(tmp.txn_raw_cost,0),0,l_raw_cost_tab(i),tmp.txn_raw_cost)
3757: ,tmp.cost_rate_override = decode(l_revenue_only_flag_tab(i),'Y',0,1)
3758: ,tmp.burden_cost_rate_override = decode(l_revenue_only_flag_tab(i),'Y',0
4170: IF P_PA_DEBUG_MODE = 'Y' Then
4171: print_msg('Updating rollup tmp with qty = rawcost or burden cost');
4172: End If;
4173: FORALL i IN l_resource_assignment_tab.FIRST .. l_resource_assignment_tab.LAST
4174: UPDATE /*+ INDEX(TMP PA_FP_SPREAD_CALC_TMP_N1) */ pa_fp_spread_calc_tmp tmp
4175: SET tmp.quantity = l_quantity_tab(i)
4176: ,tmp.txn_raw_cost = decode(nvl(tmp.txn_raw_cost,0),0,l_raw_cost_tab(i),tmp.txn_raw_cost)
4177: ,tmp.cost_rate_override = decode(nvl(l_revenue_only_flag_tab(i),'N'),'Y',0
4178: ,decode(nvl(l_burden_only_flag_tab(i),'N'),'Y',1,tmp.cost_rate_override))
4261: ,DECODE(ra.spread_curve_id,6,ra.sp_fixed_date,ra.planning_end_date) planning_end_date
4262: ,tmp.task_name
4263: ,tmp.resource_name
4264: FROM pa_resource_assignments ra
4265: ,pa_fp_spread_calc_tmp tmp
4266: WHERE ra.budget_version_id = p_budget_verson_id
4267: AND ra.resource_assignment_id = tmp.resource_assignment_id
4268: AND NOT EXISTS
4269: (SELECT 'No period exist'
4354: ,tmp.txn_currency_code
4355: ,ra.planning_start_date
4356: ,ra.planning_end_date
4357: ,rl.alias resource_name
4358: FROM pa_fp_spread_calc_tmp tmp
4359: ,pa_resource_assignments ra
4360: ,pa_resource_list_members rl
4361: WHERE tmp.budget_version_id = p_budget_version_id
4362: AND ra.resource_assignment_id = tmp.resource_assignment_id
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
4415: AND NVL(tmp.plan_dates_change_flag,'N') = 'Y'
5320: x_return_status := 'S';
5321: x_msg_data := NULL;
5322: IF p_resource_assignment_id_tab.COUNT > 0 THEN
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
5337: * if planning end date is prior to MAX date then qty should be spread to planned end date
5338: * based on this logic Now update the DAT1 and DAT2 from planning dates
5339: */
5340: FORALL i IN p_resource_assignment_id_tab.FIRST .. p_resource_assignment_id_tab.LAST
5341: UPDATE /*+ INDEX(TMP PA_FP_SPREAD_CALC_TMP_N1) */ pa_fp_spread_calc_tmp tmp
5342: SET (tmp.SYSTEM_REFERENCE_DAT1
5343: ,tmp.SYSTEM_REFERENCE_DAT2) = (select decode(tmp.SYSTEM_REFERENCE_DAT1,NULL,NULL
5344: ,decode(sign(trunc(tmp.SYSTEM_REFERENCE_DAT1)-trunc(ra.planning_start_date)),-1
5345: ,ra.planning_start_date,tmp.SYSTEM_REFERENCE_DAT1))
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
5600: AND NVL(tmp.system_reference_var1,'N') = 'Y'
5604: OR NVL(tmp.sp_fix_date_change_flag,'N') = 'Y'
5605: OR NVL(tmp.rlm_id_change_flag,'N') = 'Y'
5606: )
5607: AND NOT EXISTS (select null
5608: from pa_fp_spread_calc_tmp tmp1
5609: where tmp1.budget_version_id = p_budget_version_id
5610: and tmp1.resource_assignment_id = tmp.resource_assignment_id
5611: and tmp1.txn_currency_code = bl.txn_currency_code
5612: )
5616: --print_msg('Number of ra+txn combo selected from bl['||sql%rowcount||']');
5617:
5618: IF l_raId_Tab.COUNT > 0 THEN
5619: FORALL i IN l_raId_Tab.FIRST .. l_raId_Tab.LAST
5620: INSERT INTO pa_fp_spread_calc_tmp
5621: (BUDGET_VERSION_ID
5622: ,BUDGET_VERSION_TYPE
5623: ,RESOURCE_ASSIGNMENT_ID --resource_assignment_id
5624: ,TXN_CURRENCY_CODE --txn_currency_code
5635: End If;
5636:
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
5664: ,SYSTEM_REFERENCE_VAR1
5665: ,PLAN_START_DATE_SHRUNK_FLAG
5666: ,PLAN_END_DATE_SHRUNK_FLAG
5667: ,RLM_ID_CHANGE_FLAG
5668: ) = (SELECT /*+ INDEX(TMP1 PA_FP_SPREAD_CALC_TMP_N1) */
5669: tmp1.OLD_SPREAD_CURVE_ID
5670: ,tmp1.NEW_SPREAD_CURVE_ID
5671: ,tmp1.OLD_SP_FIX_DATE
5672: ,tmp1.NEW_SP_FIX_DATE
5687: ,tmp1.SYSTEM_REFERENCE_VAR1
5688: ,tmp1.PLAN_START_DATE_SHRUNK_FLAG
5689: ,tmp1.PLAN_END_DATE_SHRUNK_FLAG
5690: ,tmp1.RLM_ID_CHANGE_FLAG
5691: FROM pa_fp_spread_calc_tmp tmp1
5692: WHERE tmp1.budget_version_id = p_budget_version_id
5693: AND tmp1.resource_assignment_id = tmp.resource_assignment_id
5694: AND tmp1.txn_currency_code <> tmp.txn_currency_code
5695: AND NVL(tmp1.SYSTEM_REFERENCE_VAR2,'N') = 'N'
5704:
5705: /* Note: when planning resource is changed, then ignore all other attribute changes on the RA
5706: * change in the planning resource should be treated as a new RA
5707: */
5708: UPDATE pa_fp_spread_calc_tmp tmp
5709: SET tmp.SP_CURVE_CHANGE_FLAG = decode(nvl(tmp.rlm_id_change_flag,'N'),'Y','N',tmp.SP_CURVE_CHANGE_FLAG)
5710: ,tmp.PLAN_DATES_CHANGE_FLAG = decode(nvl(tmp.rlm_id_change_flag,'N'),'Y','N',tmp.PLAN_DATES_CHANGE_FLAG)
5711: ,tmp.SP_FIX_DATE_CHANGE_FLAG = decode(nvl(tmp.rlm_id_change_flag,'N'),'Y','N',tmp.SP_FIX_DATE_CHANGE_FLAG)
5712: ,tmp.MFC_COST_CHANGE_FLAG = decode(nvl(tmp.rlm_id_change_flag,'N'),'Y','N',tmp.MFC_COST_CHANGE_FLAG)
5741: IF (p_calling_module NOT IN ('BUDGET_GENERATION','FORECAST_GENERATION')
5742: and NVL(g_apply_progress_flag,'N') <> 'Y'
5743: and g_source_context = 'RESOURCE_ASSIGNMENT' ) Then --{
5744: /* If multiple RA + Txn cur combo is passed, then updates the res attributes whereever it is null */
5745: UPDATE pa_fp_spread_calc_tmp tmp
5746: SET tmp.RLM_ID_CHANGE_FLAG = decode(tmp.RLM_ID_CHANGE_FLAG,NULL,(select tmp1.RLM_ID_CHANGE_FLAG
5747: from pa_fp_spread_calc_tmp tmp1
5748: where tmp1.resource_assignment_id = tmp.resource_assignment_id
5749: and tmp1.txn_currency_code <> tmp.txn_currency_code
5743: and g_source_context = 'RESOURCE_ASSIGNMENT' ) Then --{
5744: /* If multiple RA + Txn cur combo is passed, then updates the res attributes whereever it is null */
5745: UPDATE pa_fp_spread_calc_tmp tmp
5746: SET tmp.RLM_ID_CHANGE_FLAG = decode(tmp.RLM_ID_CHANGE_FLAG,NULL,(select tmp1.RLM_ID_CHANGE_FLAG
5747: from pa_fp_spread_calc_tmp tmp1
5748: where tmp1.resource_assignment_id = tmp.resource_assignment_id
5749: and tmp1.txn_currency_code <> tmp.txn_currency_code
5750: and tmp1.RLM_ID_CHANGE_FLAG is not null
5751: and rownum = 1
5750: and tmp1.RLM_ID_CHANGE_FLAG is not null
5751: and rownum = 1
5752: ),tmp.RLM_ID_CHANGE_FLAG)
5753: ,tmp.OLD_SPREAD_CURVE_ID = decode(tmp.OLD_SPREAD_CURVE_ID,NULL,(select tmp1.OLD_SPREAD_CURVE_ID
5754: from pa_fp_spread_calc_tmp tmp1
5755: where tmp1.resource_assignment_id = tmp.resource_assignment_id
5756: and tmp1.txn_currency_code <> tmp.txn_currency_code
5757: and tmp1.OLD_SPREAD_CURVE_ID is not null
5758: and rownum = 1
5757: and tmp1.OLD_SPREAD_CURVE_ID is not null
5758: and rownum = 1
5759: ),tmp.OLD_SPREAD_CURVE_ID)
5760: ,tmp.NEW_SPREAD_CURVE_ID = decode(tmp.NEW_SPREAD_CURVE_ID,NULL,(select tmp1.NEW_SPREAD_CURVE_ID
5761: from pa_fp_spread_calc_tmp tmp1
5762: where tmp1.resource_assignment_id = tmp.resource_assignment_id
5763: and tmp1.txn_currency_code <> tmp.txn_currency_code
5764: and tmp1.NEW_SPREAD_CURVE_ID is not null
5765: and rownum = 1
5764: and tmp1.NEW_SPREAD_CURVE_ID is not null
5765: and rownum = 1
5766: ),tmp.NEW_SPREAD_CURVE_ID)
5767: ,tmp.OLD_SP_FIX_DATE = decode(tmp.OLD_SP_FIX_DATE,NULL,(select tmp1.OLD_SP_FIX_DATE
5768: from pa_fp_spread_calc_tmp tmp1
5769: where tmp1.resource_assignment_id = tmp.resource_assignment_id
5770: and tmp1.txn_currency_code <> tmp.txn_currency_code
5771: and tmp1.OLD_SP_FIX_DATE is not null
5772: and rownum = 1
5771: and tmp1.OLD_SP_FIX_DATE is not null
5772: and rownum = 1
5773: ),tmp.OLD_SP_FIX_DATE)
5774: ,tmp.NEW_SP_FIX_DATE = decode(tmp.NEW_SP_FIX_DATE,NULL,(select tmp1.NEW_SP_FIX_DATE
5775: from pa_fp_spread_calc_tmp tmp1
5776: where tmp1.resource_assignment_id = tmp.resource_assignment_id
5777: and tmp1.txn_currency_code <> tmp.txn_currency_code
5778: and tmp1.NEW_SP_FIX_DATE is not null
5779: and rownum = 1
5778: and tmp1.NEW_SP_FIX_DATE is not null
5779: and rownum = 1
5780: ),tmp.NEW_SP_FIX_DATE)
5781: ,tmp.OLD_MFC_COST_TYPE_ID = decode(tmp.OLD_MFC_COST_TYPE_ID,NULL,(select tmp1.OLD_MFC_COST_TYPE_ID
5782: from pa_fp_spread_calc_tmp tmp1
5783: where tmp1.resource_assignment_id = tmp.resource_assignment_id
5784: and tmp1.txn_currency_code <> tmp.txn_currency_code
5785: and tmp1.OLD_MFC_COST_TYPE_ID is not null
5786: and rownum = 1
5785: and tmp1.OLD_MFC_COST_TYPE_ID is not null
5786: and rownum = 1
5787: ),tmp.OLD_MFC_COST_TYPE_ID)
5788: ,tmp.NEW_MFC_COST_TYPE_ID = decode(tmp.NEW_MFC_COST_TYPE_ID,NULL,(select tmp1.NEW_MFC_COST_TYPE_ID
5789: from pa_fp_spread_calc_tmp tmp1
5790: where tmp1.resource_assignment_id = tmp.resource_assignment_id
5791: and tmp1.txn_currency_code <> tmp.txn_currency_code
5792: and tmp1.NEW_MFC_COST_TYPE_ID is not null
5793: and rownum = 1
5792: and tmp1.NEW_MFC_COST_TYPE_ID is not null
5793: and rownum = 1
5794: ),tmp.NEW_MFC_COST_TYPE_ID)
5795: ,tmp.OLD_PLAN_START_DATE = decode(tmp.OLD_PLAN_START_DATE,NULL,(select tmp1.OLD_PLAN_START_DATE
5796: from pa_fp_spread_calc_tmp tmp1
5797: where tmp1.resource_assignment_id = tmp.resource_assignment_id
5798: and tmp1.txn_currency_code <> tmp.txn_currency_code
5799: and tmp1.OLD_PLAN_START_DATE is not null
5800: and rownum = 1
5799: and tmp1.OLD_PLAN_START_DATE is not null
5800: and rownum = 1
5801: ),tmp.OLD_PLAN_START_DATE)
5802: ,tmp.OLD_PLAN_END_DATE = decode(tmp.OLD_PLAN_END_DATE,NULL,(select tmp1.OLD_PLAN_END_DATE
5803: from pa_fp_spread_calc_tmp tmp1
5804: where tmp1.resource_assignment_id = tmp.resource_assignment_id
5805: and tmp1.txn_currency_code <> tmp.txn_currency_code
5806: and tmp1.OLD_PLAN_END_DATE is not null
5807: and rownum = 1
5818: --print_msg('synch_Upd: NumOfRowUpdated['||sql%rowcount||']');
5819:
5820: /* set the respective changed flags */
5821: /* Bug #5031939: remove budget_version id joins in the sub query */
5822: UPDATE pa_fp_spread_calc_tmp tmp
5823: SET tmp.SP_CURVE_CHANGE_FLAG = decode(NVL(tmp.OLD_SPREAD_CURVE_ID,1),NVL(tmp.NEW_SPREAD_CURVE_ID,1),'N','Y')
5824: ,tmp.PLAN_DATES_CHANGE_FLAG =decode(tmp.OLD_PLAN_START_DATE,NULL,'N'
5825: ,decode(tmp.NEW_PLAN_START_DATE,NULL,'N'
5826: ,decode(tmp.OLD_PLAN_START_DATE,tmp.NEW_PLAN_START_DATE,'N','Y')))
5838: GROUP BY bl.resource_assignment_id,bl.txn_currency_code
5839: ))
5840: WHERE tmp.budget_version_id = p_budget_version_id;
5841:
5842: UPDATE pa_fp_spread_calc_tmp tmp
5843: SET tmp.PLAN_DATES_CHANGE_FLAG = decode(tmp.PLAN_DATES_CHANGE_FLAG,'Y','Y'
5844: ,decode(tmp.OLD_PLAN_END_DATE,NULL,'N'
5845: ,decode(tmp.NEW_PLAN_END_DATE,NULL,'N'
5846: ,decode(tmp.OLD_PLAN_END_DATE,tmp.NEW_PLAN_END_DATE,'N','Y'))))
5847: ,tmp.system_reference_var1 = NVL(system_reference_var1,'N')
5848: WHERE tmp.budget_version_id = p_budget_version_id;
5849: --print_msg('Number of rows updated with resp resAtrbflag['||sql%rowcount||']');
5850:
5851: UPDATE pa_fp_spread_calc_tmp tmp
5852: SET tmp.PLAN_START_DATE_SHRUNK_FLAG = decode(tmp.PLAN_DATES_CHANGE_FLAG,'N','N'
5853: ,decode(tmp.new_plan_start_date,NULL,'N'
5854: ,decode(tmp.old_plan_start_date,NULL,'N'
5855: ,decode(sign(trunc(tmp.new_plan_start_date) - trunc(tmp.old_plan_start_date)),1 ,'Y','N'))))
5889: IF (p_calling_module NOT IN ('BUDGET_GENERATION','FORECAST_GENERATION')
5890: and NVL(g_apply_progress_flag,'N') <> 'Y'
5891: and g_source_context = 'RESOURCE_ASSIGNMENT' ) Then --{
5892: /* Bug #5031939: remove budget_version id joins in the sub query */
5893: UPDATE pa_fp_spread_calc_tmp tmp
5894: SET tmp.SP_CURVE_CHANGE_FLAG = (select 'Y'
5895: from dual
5896: where exists (select null
5897: from pa_fp_spread_calc_tmp tmp1
5893: UPDATE pa_fp_spread_calc_tmp tmp
5894: SET tmp.SP_CURVE_CHANGE_FLAG = (select 'Y'
5895: from dual
5896: where exists (select null
5897: from pa_fp_spread_calc_tmp tmp1
5898: where tmp1.resource_assignment_id = tmp.resource_assignment_id
5899: and tmp1.sp_curve_change_flag = 'Y' ))
5900: ,tmp.PLAN_DATES_CHANGE_FLAG = (select 'Y'
5901: from dual
5899: and tmp1.sp_curve_change_flag = 'Y' ))
5900: ,tmp.PLAN_DATES_CHANGE_FLAG = (select 'Y'
5901: from dual
5902: where exists (select null
5903: from pa_fp_spread_calc_tmp tmp1
5904: where tmp1.resource_assignment_id = tmp.resource_assignment_id
5905: and tmp1.plan_dates_change_flag = 'Y' ))
5906: ,tmp.SP_FIX_DATE_CHANGE_FLAG = (select 'Y'
5907: from dual
5905: and tmp1.plan_dates_change_flag = 'Y' ))
5906: ,tmp.SP_FIX_DATE_CHANGE_FLAG = (select 'Y'
5907: from dual
5908: where exists (select null
5909: from pa_fp_spread_calc_tmp tmp1
5910: where tmp1.resource_assignment_id = tmp.resource_assignment_id
5911: and tmp1.sp_fix_date_change_flag = 'Y' ))
5912: ,tmp.MFC_COST_CHANGE_FLAG = (select 'Y'
5913: from dual
5911: and tmp1.sp_fix_date_change_flag = 'Y' ))
5912: ,tmp.MFC_COST_CHANGE_FLAG = (select 'Y'
5913: from dual
5914: where exists (select null
5915: from pa_fp_spread_calc_tmp tmp1
5916: where tmp1.resource_assignment_id = tmp.resource_assignment_id
5917: and tmp1.mfc_cost_change_flag = 'Y' ))
5918: ,tmp.PLAN_START_DATE_SHRUNK_FLAG = (select 'Y'
5919: from dual
5917: and tmp1.mfc_cost_change_flag = 'Y' ))
5918: ,tmp.PLAN_START_DATE_SHRUNK_FLAG = (select 'Y'
5919: from dual
5920: where exists (select null
5921: from pa_fp_spread_calc_tmp tmp1
5922: where tmp1.resource_assignment_id = tmp.resource_assignment_id
5923: and tmp1.plan_start_date_shrunk_flag = 'Y'))
5924: ,tmp.PLAN_END_DATE_SHRUNK_FLAG = (select 'Y'
5925: from dual
5923: and tmp1.plan_start_date_shrunk_flag = 'Y'))
5924: ,tmp.PLAN_END_DATE_SHRUNK_FLAG = (select 'Y'
5925: from dual
5926: where exists (select null
5927: from pa_fp_spread_calc_tmp tmp1
5928: where tmp1.resource_assignment_id = tmp.resource_assignment_id
5929: and tmp1.plan_end_date_shrunk_flag = 'Y'))
5930: ,tmp.RLM_ID_CHANGE_FLAG = (select 'Y'
5931: from dual
5929: and tmp1.plan_end_date_shrunk_flag = 'Y'))
5930: ,tmp.RLM_ID_CHANGE_FLAG = (select 'Y'
5931: from dual
5932: where exists (select null
5933: from pa_fp_spread_calc_tmp tmp1
5934: where tmp1.resource_assignment_id = tmp.resource_assignment_id
5935: and tmp1.rlm_id_change_flag = 'Y'))
5936: ,(tmp.task_id,tmp.resource_name) =
5937: (select ra.task_id,rlm.alias
5956: WHERE tmp.budget_version_id = p_budget_version_id;
5957: --print_msg('Number of rows updated with resp resAtrbflag['||sql%rowcount||']');
5958:
5959: /* Now set all the resource attributs N when planning resource is changed */
5960: UPDATE pa_fp_spread_calc_tmp tmp
5961: SET tmp.SP_CURVE_CHANGE_FLAG = 'N'
5962: ,tmp.PLAN_DATES_CHANGE_FLAG = 'N'
5963: ,tmp.SP_FIX_DATE_CHANGE_FLAG = 'N'
5964: ,tmp.MFC_COST_CHANGE_FLAG = 'N'
5969: --print_msg('Number of rows updated with resp resAtrbflag['||sql%rowcount||']');
5970:
5971: ELSE
5972: /* This is done to avoid executing the cursors multiple times for passing token values */
5973: UPDATE pa_fp_spread_calc_tmp tmp
5974: SET (tmp.task_id,tmp.resource_name) =
5975: (select ra.task_id,rlm.alias
5976: from pa_resource_assignments ra
5977: ,pa_resource_list_members rlm
6117: ,NVL(tmp.system_reference_var1,'N') MultiCurrLineFlag
6118: ,NVL(tmp.plan_start_date_shrunk_flag,'N') plan_start_date_shrunk_flag
6119: ,NVL(tmp.plan_end_date_shrunk_flag,'N') plan_end_date_shrunk_flag
6120: ,NVL(tmp.rlm_id_change_flag,'N') rlm_id_change_flag
6121: FROM pa_fp_spread_calc_tmp tmp
6122: WHERE tmp.budget_version_id = p_budget_version_id
6123: AND NVL(tmp.system_reference_var1,'N') = 'Y'
6124: AND NVL(tmp.rlm_id_change_flag,'N') <> 'Y'
6125: AND (tmp.re_spread_amts_flag = 'Y'
6133: CURSOR cur_MultipleAdjustments_chk IS
6134: SELECT 'Y'
6135: FROM DUAL
6136: WHERE EXISTS (SELECT 'MultipleLAdjustments'
6137: FROM pa_fp_spread_calc_tmp tmp
6138: WHERE tmp.budget_version_id = p_budget_version_id
6139: AND ((decode(tmp.OLD_PLAN_START_DATE,NULL,'N'
6140: ,decode(tmp.NEW_PLAN_START_DATE,NULL,'N'
6141: ,decode(tmp.OLD_PLAN_START_DATE,tmp.NEW_PLAN_START_DATE,'N','Y'))) ='Y')
6162: ,tmp.old_sp_fix_date
6163: ,tmp.new_sp_fix_date
6164: ,tmp.old_mfc_cost_type_id
6165: ,tmp.new_mfc_cost_type_id
6166: FROM pa_fp_spread_calc_tmp tmp
6167: ,pa_resource_list_members rl
6168: ,pa_resource_assignments ra
6169: WHERE tmp.budget_version_id = p_budget_version_id
6170: AND ra.resource_assignment_id = tmp.resource_assignment_id
6181: GROUP BY tmp.resource_assignment_id
6182: HAVING count(*) > 1 ;
6183: *** End of bug fix**/
6184: AND EXISTS (SELECT 'MultipleLAdjustments'
6185: FROM pa_fp_spread_calc_tmp tmpb
6186: WHERE tmpb.budget_version_id = tmp.budget_version_id
6187: AND tmpb.resource_assignment_id = tmp.resource_assignment_id
6188: AND ((decode(tmpb.OLD_PLAN_START_DATE,NULL,'N'
6189: ,decode(tmpb.NEW_PLAN_START_DATE,NULL,'N'
6200: /* This cursor checks that calling api has passed duplicate set of records */
6201: CURSOR cur_chk_dupRecords IS
6202: SELECT tmp.resource_assignment_id
6203: ,tmp.txn_currency_code
6204: FROM pa_fp_spread_calc_tmp tmp
6205: WHERE tmp.budget_version_id = p_budget_version_id
6206: GROUP BY tmp.resource_assignment_id
6207: ,tmp.txn_currency_code
6208: HAVING COUNT(*) > 1 ;
6220: SELECT 'Y'
6221: INTO l_return_flg
6222: FROM DUAL
6223: WHERE EXISTS (Select null
6224: from pa_fp_spread_calc_tmp tmp
6225: where ( NVL(tmp.sp_curve_change_flag,'N') = 'Y'
6226: OR NVL(tmp.plan_dates_change_flag,'N') = 'Y'
6227: OR NVL(tmp.sp_fix_date_change_flag,'N') = 'Y'
6228: OR NVL(tmp.re_spread_amts_flag,'N') = 'Y'
6250: print_msg(' Entered populate_spreadCalc_Tmp API');
6251: End If;
6252: /* populate tmp table with the data */
6253: Init_plsqlTabs;
6254: DELETE FROM pa_fp_spread_calc_tmp;
6255: DELETE FROM pa_fp_rollup_tmp
6256: WHERE budget_version_id = p_budget_version_id;
6257:
6258: bvDetailsRec := NULL;
6364: End If;
6365: /* end of Perf Impr:5309529 */
6366: /*******
6367: FORALL i IN x_resource_assignment_tab.FIRST .. x_resource_assignment_tab.LAST
6368: INSERT INTO pa_fp_spread_calc_tmp
6369: (RESOURCE_ASSIGNMENT_ID --resource_assignment_id
6370: ,DELETE_BL_FLAG --delete_budget_lines_flag
6371: ,SPREAD_AMTS_FLAG --spread_amts_flag
6372: ,TXN_CURRENCY_CODE --txn_currency_code
6711: END IF; --}
6712: END IF; --}
6713:
6714: /** added this for debug purpose
6715: FOR i IN (select * from pa_fp_spread_calc_tmp ) LOOP
6716: print_msg('Ra['||i.resource_assignment_id||']TxnCur['||i.txn_currency_code||']');
6717: print_msg('respFlag['||i.re_spread_amts_flag||']');
6718: print_msg('spCurvFlag['||i.sp_curve_change_flag||']');
6719: print_msg('PlanDtFlag['||i.plan_dates_change_flag||']');
7028: ,x_neg_Qty_Changflag_tab
7029: ,x_neg_Raw_Changflag_tab
7030: ,x_neg_Burd_Changflag_tab
7031: ,x_neg_rev_Changflag_tab
7032: FROM PA_FP_SPREAD_CALC_TMP tmp
7033: WHERE tmp.budget_version_id = p_budget_version_id;
7034:
7035: x_return_status := l_return_status;
7036:
7080: ,tmp.cost_rate_changed_flag
7081: ,tmp.burden_rate_changed_flag
7082: ,tmp.bill_rate_changed_flag
7083: ,tmp.mfc_cost_change_flag
7084: FROM pa_fp_spread_calc_tmp tmp
7085: WHERE tmp.budget_version_id = p_budget_verson_id
7086: AND nvl(tmp.rlm_id_change_flag,'N') <> 'Y'
7087: AND (tmp.cost_rate_override is NOT NULL
7088: OR tmp.burden_cost_rate_override is NOT NULL
7095: End If;
7096: x_return_status := 'S';
7097: x_msg_data := NULL;
7098:
7099: INSERT INTO pa_fp_spread_calc_tmp1
7100: (RESOURCE_ASSIGNMENT_ID --resource_assignment_id
7101: ,BUDGET_VERSION_ID --budget_version_id
7102: ,BUDGET_VERSION_TYPE
7103: ,BUDGET_LINE_ID
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
7277: AND bl.txn_currency_code = tmp.txn_currency_code
7298: print_msg('mfcCostFlag['||i.mfc_cost_change_flag||'Ver['||i.budget_version_type||']');
7299: print_msg('costRtChFlag['||i.cost_rate_changed_flag||']Rt['||i.cost_rate_override||']');
7300: print_msg('burRtOvr['||i.burden_cost_rate_override||']');
7301: */
7302: UPDATE /*+ INDEX(TMP1 PA_FP_SPREAD_CALC_TMP1_N1) */ pa_fp_spread_calc_tmp1 tmp1
7303: SET tmp1.cost_rate_override = decode(i.budget_version_type,'REVENUE',tmp1.cost_rate_override
7304: ,decode(nvl(i.cost_rate_changed_flag,'N')
7305: ,'Y',decode(i.mfc_cost_change_flag,'Y'
7306: ,decode(nvl(i.cost_rate_override,0),0,NULL,i.cost_rate_override),i.cost_rate_override)
7607: cache.PROJFUNC_REV_RATE_DATE),
7608: tmp.PROJFUNC_REV_RATE_DATE),
7609: NULL) PROJFUNC_REV_RATE_DATE
7610: FROM pa_fp_rollup_tmp tmp
7611: ,pa_fp_spread_calc_tmp1 cache
7612: WHERE tmp.budget_version_id = p_budget_verson_id
7613: AND tmp.budget_version_id = cache.budget_version_id
7614: AND tmp.resource_assignment_id = cache.resource_assignment_id
7615: AND tmp.txn_currency_code = cache.txn_currency_code
7669: , cache.TRANSFER_PRICE_RATE
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
8050: CURSOR cur_chk_dupRecords IS
8051: SELECT tmp.resource_assignment_id
8052: ,tmp.txn_currency_code
8053: ,tmp.start_date
8054: FROM pa_fp_spread_calc_tmp tmp
8055: WHERE tmp.budget_version_id = p_budget_version_id
8056: GROUP BY tmp.resource_assignment_id
8057: ,tmp.txn_currency_code
8058: ,tmp.start_date
8073: */
8074: l_duplicate_record_exists := 'Y';
8075: IF NVL(p_source_context,'RESOURCE_ASSIGNMENT') = 'RESOURCE_ASSIGNMENT' Then
8076: g_stage := 'Validate_duplicate_records:102';
8077: DELETE FROM pa_fp_spread_calc_tmp tmp1
8078: WHERE tmp1.resource_assignment_id = i.resource_assignment_id
8079: AND tmp1.txn_currency_code = i.txn_currency_code
8080: AND tmp1.rowid NOT IN (select min(rowid) from pa_fp_spread_calc_tmp tmp2
8081: where tmp1.resource_assignment_id = tmp2.resource_assignment_id
8076: g_stage := 'Validate_duplicate_records:102';
8077: DELETE FROM pa_fp_spread_calc_tmp tmp1
8078: WHERE tmp1.resource_assignment_id = i.resource_assignment_id
8079: AND tmp1.txn_currency_code = i.txn_currency_code
8080: AND tmp1.rowid NOT IN (select min(rowid) from pa_fp_spread_calc_tmp tmp2
8081: where tmp1.resource_assignment_id = tmp2.resource_assignment_id
8082: and tmp1.txn_currency_code = tmp2.txn_currency_code
8083: group by tmp2.resource_assignment_id,tmp2.txn_currency_code
8084: having count(*) > 1
8084: having count(*) > 1
8085: )
8086: /* added this to make sure that even if code is executed multiple times this should delete the correct combo */
8087: /* this code is not required if not executed twice for the same combo*/
8088: AND EXISTS (select 'Y' from pa_fp_spread_calc_tmp tmp2
8089: where tmp1.resource_assignment_id = tmp2.resource_assignment_id
8090: and tmp1.txn_currency_code = tmp2.txn_currency_code
8091: group by tmp2.resource_assignment_id,tmp2.txn_currency_code
8092: having count(*) > 1
8092: having count(*) > 1
8093: );
8094: Elsif NVL(p_source_context,'RESOURCE_ASSIGNMENT') = 'BUDGET_LINE' Then
8095: g_stage := 'Validate_duplicate_records:103';
8096: DELETE FROM pa_fp_spread_calc_tmp tmp1
8097: WHERE tmp1.resource_assignment_id = i.resource_assignment_id
8098: AND tmp1.txn_currency_code = i.txn_currency_code
8099: AND tmp1.start_date = i.start_date
8100: AND tmp1.rowid NOT IN (select min(tmp2.rowid) from pa_fp_spread_calc_tmp tmp2
8096: DELETE FROM pa_fp_spread_calc_tmp tmp1
8097: WHERE tmp1.resource_assignment_id = i.resource_assignment_id
8098: AND tmp1.txn_currency_code = i.txn_currency_code
8099: AND tmp1.start_date = i.start_date
8100: AND tmp1.rowid NOT IN (select min(tmp2.rowid) from pa_fp_spread_calc_tmp tmp2
8101: where tmp1.resource_assignment_id = tmp2.resource_assignment_id
8102: and tmp1.txn_currency_code = tmp2.txn_currency_code
8103: and tmp1.start_date = tmp2.start_date
8104: group by tmp2.resource_assignment_id,tmp2.txn_currency_code,tmp2.start_date
8103: and tmp1.start_date = tmp2.start_date
8104: group by tmp2.resource_assignment_id,tmp2.txn_currency_code,tmp2.start_date
8105: having count(*) > 1
8106: )
8107: AND EXISTS (select 'Y' from pa_fp_spread_calc_tmp tmp2
8108: where tmp1.resource_assignment_id = tmp2.resource_assignment_id
8109: and tmp1.txn_currency_code = tmp2.txn_currency_code
8110: and tmp1.start_date = tmp2.start_date
8111: group by tmp2.resource_assignment_id,tmp2.txn_currency_code,tmp2.start_date
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
8435: AND tmp2.txn_currency_code = bl.txn_currency_code
8483: ,tmp1.burden_rejection_code
8484: ,tmp1.pfc_cur_conv_rejection_code
8485: ,tmp1.pc_cur_conv_rejection_code
8486: ,tmp1.system_reference_num1 Existing_budget_line_id
8487: FROM pa_fp_spread_calc_tmp1 tmp1
8488: WHERE tmp1.budget_version_id = p_budget_version_id;
8489:
8490: l_del_budget_line_id_tab pa_plsql_datatypes.NumTabTyp;
8491: CURSOR cur_delBlLines IS
8489:
8490: l_del_budget_line_id_tab pa_plsql_datatypes.NumTabTyp;
8491: CURSOR cur_delBlLines IS
8492: SELECT tmp1.budget_line_id
8493: FROM pa_fp_spread_calc_tmp1 tmp1
8494: WHERE tmp1.budget_version_id = p_budget_version_id;
8495:
8496:
8497: CURSOR cur_Tmp2ExblAmts(p_budget_line_id Number) IS
8503: ,tmp2.bill_rate existing_bill_rate
8504: ,tmp2.bill_rate_override existing_bill_rate_ovride
8505: ,tmp2.bill_markup_percentage existing_markup_percentage
8506: ,tmp2.system_reference_num1 existing_compile_set_id
8507: FROM pa_fp_spread_calc_tmp2 tmp2
8508: WHERE tmp2.budget_version_id = p_budget_version_id
8509: AND tmp2.budget_line_id = p_budget_line_id;
8510: ExBlRec cur_Tmp2ExblAmts%ROWTYPE;
8511:
8779: ,l_tmp2_cost_rate_ovr_tab
8780: ,l_tmp2_burden_rate_ovr_tab
8781: ,l_tmp2_compile_set_id_tab
8782: ,l_tmp2_rate_based_flag_tab /* bug fix: 4900436 */
8783: FROM pa_fp_spread_calc_tmp2 tmp2
8784: ,pa_resource_assignments ra
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;
9017: ) IS
9018: BEGIN
9019: x_return_status := 'S';
9020: print_msg('Entered Populate_blkExcpRecs API');
9021: DELETE FROM PA_FP_SPREAD_CALC_TMP1;
9022: FORALL i IN p_err_error_code_tab.FIRST .. p_err_error_code_tab.LAST
9023: INSERT INTO PA_FP_SPREAD_CALC_TMP1 tmp
9024: (tmp.budget_line_id
9025: ,tmp.budget_version_id
9019: x_return_status := 'S';
9020: print_msg('Entered Populate_blkExcpRecs API');
9021: DELETE FROM PA_FP_SPREAD_CALC_TMP1;
9022: FORALL i IN p_err_error_code_tab.FIRST .. p_err_error_code_tab.LAST
9023: INSERT INTO PA_FP_SPREAD_CALC_TMP1 tmp
9024: (tmp.budget_line_id
9025: ,tmp.budget_version_id
9026: ,tmp.resource_assignment_id
9027: ,tmp.txn_currency_code
9086: ) IS
9087: BEGIN
9088: x_return_status := 'S';
9089: print_msg('Entered Populate_ExistingBlRecs API');
9090: DELETE FROM PA_FP_SPREAD_CALC_TMP2;
9091: INSERT INTO PA_FP_SPREAD_CALC_TMP2 tmp
9092: (tmp.budget_line_id
9093: ,tmp.budget_version_id
9094: ,tmp.resource_assignment_id
9087: BEGIN
9088: x_return_status := 'S';
9089: print_msg('Entered Populate_ExistingBlRecs API');
9090: DELETE FROM PA_FP_SPREAD_CALC_TMP2;
9091: INSERT INTO PA_FP_SPREAD_CALC_TMP2 tmp
9092: (tmp.budget_line_id
9093: ,tmp.budget_version_id
9094: ,tmp.resource_assignment_id
9095: ,tmp.txn_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
9140: and tmp1.resource_assignment_id = bl.resource_assignment_id
9141: and tmp1.txn_currency_code = bl.txn_currency_code
9142: and tmp1.start_date = bl.start_date
9144: print_msg('Number of lines inserted['||sql%Rowcount||']');
9145:
9146: /* Now store the existing budget line id on tmp1 table to read the values
9147: * from both tables */
9148: UPDATE pa_fp_spread_calc_tmp1 tmp1
9149: SET tmp1.system_reference_num1 = (select tmp2.budget_line_id
9150: from pa_fp_spread_calc_tmp2 tmp2
9151: where tmp2.budget_version_id = p_budget_version_id
9152: and tmp2.resource_assignment_id = tmp1.resource_assignment_id
9146: /* Now store the existing budget line id on tmp1 table to read the values
9147: * from both tables */
9148: UPDATE pa_fp_spread_calc_tmp1 tmp1
9149: SET tmp1.system_reference_num1 = (select tmp2.budget_line_id
9150: from pa_fp_spread_calc_tmp2 tmp2
9151: where tmp2.budget_version_id = p_budget_version_id
9152: and tmp2.resource_assignment_id = tmp1.resource_assignment_id
9153: and tmp2.txn_currency_code = tmp1.txn_currency_code
9154: and tmp2.start_date = tmp1.start_date
9796: l_bl_upd_markup_percentage := newRec.bill_markup_percentage;
9797: End If;
9798:
9799:
9800: UPDATE pa_fp_spread_calc_tmp2 tmp2
9801: SET tmp2.txn_currency_code = newRec.txn_currency_code
9802: ,tmp2.quantity = nvl(tmp2.quantity,0) + newRec.quantity
9803: ,tmp2.system_reference_var1 = newRec.cost_rejection_code
9804: ,tmp2.system_reference_var2 = newRec.revenue_rejection_code
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
10843: RAISE;
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: */
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
10853: ( p_budget_verson_id IN Number
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'
10873: l_resource_assignment_id_tab pa_plsql_datatypes.Num15TabTyp;
11011: ,bl.LAST_UPDATED_BY
11012: ,bl.LAST_UPDATE_LOGIN
11013: ,bl.LAST_UPDATE_DATE
11014: )
11015: SELECT /*+ INDEX(TMP PA_FP_SPREAD_CALC_TMP1_N1) */ l_budget_line_id_tab(i)
11016: ,tmp.RESOURCE_ASSIGNMENT_ID --resource_assignment_id
11017: ,tmp.BUDGET_VERSION_ID --budget_version_id
11018: ,tmp.TXN_CURRENCY_CODE --txn_currency_code
11019: ,tmp.QUANTITY --total_qty
11096: ,tmp.BL_CREATION_DATE
11097: ,tmp.BL_CREATED_BY --last updated by
11098: ,tmp.BL_CREATED_BY -- lastupdate login
11099: ,trunc(sysdate) -- last update dated
11100: FROM PA_FP_SPREAD_CALC_TMP1 tmp
11101: WHERE tmp.resource_assignment_id = l_resource_assignment_id_tab(i)
11102: AND tmp.txn_currency_code = l_txn_cur_code_tab(i)
11103: AND tmp.start_date = l_start_date_tab(i);
11104: