eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.PA_FP_CALC_UTILS dependencies on PA_FP_SPREAD_CALC_TMP

Line 253: FROM pa_fp_spread_calc_tmp tmp

249: CURSOR cur_CorruptedBls IS
250: SELECT tmp.resource_assignment_id
251: ,rl.alias resource_name
252: ,tmp.txn_currency_code
253: FROM pa_fp_spread_calc_tmp tmp
254: ,pa_resource_assignments ra
255: ,pa_resource_list_members rl
256: WHERE tmp.budget_version_id = p_budget_version_id
257: AND ra.resource_assignment_id = tmp.resource_assignment_id

Line 287: * 5:TABLE ACCESS BY INDEX ROWID PA_FP_SPREAD_CALC_TMP :(cost=6,rows=82)

283: *1:SELECT STATEMENT :(cost=22,rows=1)
284: * 2:NESTED LOOPS :(cost=22,rows=1)
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)

Line 288: * 6:INDEX RANGE SCAN PA_FP_SPREAD_CALC_TMP_N2 :(cost=2,rows=33)

284: * 2:NESTED LOOPS :(cost=22,rows=1)
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)

Line 300: FROM pa_fp_spread_calc_tmp tmp

296: CURSOR cur_blDatesCheck IS
297: SELECT tmp.resource_assignment_id
298: ,rl.alias resource_name
299: ,tmp.txn_currency_code
300: FROM pa_fp_spread_calc_tmp tmp
301: ,pa_resource_assignments ra
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

Line 805: FROM pa_fp_spread_calc_tmp tmp

801: CURSOR cur_validate IS
802: SELECT tmp.resource_assignment_id
803: ,tmp.txn_currency_code
804: ,tmp.start_date
805: FROM pa_fp_spread_calc_tmp tmp
806: WHERE tmp.budget_version_id = g_budget_version_id
807: AND (tmp.start_date is NULL OR tmp.end_date is NULL);
808:
809: l_miss_num Number := fnd_api.g_miss_num;

Line 960: INSERT INTO pa_fp_spread_calc_tmp

956: If p_pa_debug_mode = 'Y' Then
957: print_msg(' Inserting records into spread calc tmp table');
958: End If;
959: FORALL i IN x_resource_assignment_tab.FIRST .. x_resource_assignment_tab.LAST
960: INSERT INTO pa_fp_spread_calc_tmp
961: (RESOURCE_ASSIGNMENT_ID --resource_assignment_id
962: ,DELETE_BL_FLAG --delete_budget_lines_flag
963: ,SPREAD_AMTS_FLAG --spread_amts_flag
964: ,TXN_CURRENCY_CODE --txn_currency_code

Line 1133: FROM pa_fp_spread_calc_tmp tmp

1129: ,tmp.txn_currency_code
1130: ,tmp.cost_rate_override
1131: ,tmp.burden_cost_rate_override
1132: ,tmp.bill_rate_override
1133: FROM pa_fp_spread_calc_tmp tmp
1134: WHERE tmp.budget_version_id = g_budget_version_id
1135: AND tmp.cost_rate_override = 0
1136: AND tmp.burden_cost_rate_override = 0
1137: AND tmp.bill_rate_override = 1

Line 1215: FROM pa_fp_spread_calc_tmp tmp

1211: ,rlm.unit_of_measure resource_uom
1212: ,tmp.start_date
1213: ,tmp.end_date
1214: ,NVL(tmp.delete_bl_flag,'N') delete_bl_flag
1215: FROM pa_fp_spread_calc_tmp tmp
1216: ,pa_fp_spread_calc_tmp2 tmp2
1217: ,pa_resource_assignments ra
1218: ,pa_resource_list_members rlm
1219: WHERE tmp.budget_version_id = p_budget_version_id

Line 1216: ,pa_fp_spread_calc_tmp2 tmp2

1212: ,tmp.start_date
1213: ,tmp.end_date
1214: ,NVL(tmp.delete_bl_flag,'N') delete_bl_flag
1215: FROM pa_fp_spread_calc_tmp tmp
1216: ,pa_fp_spread_calc_tmp2 tmp2
1217: ,pa_resource_assignments ra
1218: ,pa_resource_list_members rlm
1219: WHERE tmp.budget_version_id = p_budget_version_id
1220: AND ra.resource_assignment_id = tmp.resource_assignment_id

Line 1298: DELETE FROM pa_fp_spread_calc_tmp2

1294: x_msg_data := NULL;
1295: IF P_PA_DEBUG_MODE = 'Y' Then
1296: print_msg('Entered Compare_bdgtLine_Values API');
1297: End If;
1298: DELETE FROM pa_fp_spread_calc_tmp2
1299: WHERE budget_version_id = p_budget_version_id;
1300: l_res_Asgn_Id_Tab.delete;
1301: l_txn_cur_code_Tab.delete;
1302: l_start_date_tab.delete;

Line 1321: FROM pa_fp_spread_calc_tmp tmp

1317: l_res_Asgn_Id_Tab
1318: ,l_txn_cur_code_Tab
1319: ,l_start_date_tab
1320: ,l_end_date_tab
1321: FROM pa_fp_spread_calc_tmp tmp
1322: WHERE tmp.budget_version_id = p_budget_version_id;
1323:
1324: IF l_res_Asgn_Id_Tab.COUNT > 0 Then --{
1325: IF P_PA_DEBUG_MODE = 'Y' Then

Line 1329: INSERT INTO pa_fp_spread_calc_tmp2

1325: IF P_PA_DEBUG_MODE = 'Y' Then
1326: print_msg('NumOf Lines inserted['||l_res_Asgn_Id_Tab.count||']');
1327: End If;
1328: FORALL i IN l_res_Asgn_Id_Tab.FIRST .. l_res_Asgn_Id_Tab.LAST
1329: INSERT INTO pa_fp_spread_calc_tmp2
1330: (BUDGET_VERSION_ID
1331: ,BUDGET_VERSION_TYPE
1332: ,RESOURCE_ASSIGNMENT_ID
1333: ,TXN_CURRENCY_CODE

Line 1349: UPDATE /+ INDEX(TMP PA_FP_SPREAD_CALC_TMP2_N1) / pa_fp_spread_calc_tmp2 tmp

1345: IF P_PA_DEBUG_MODE = 'Y' Then
1346: print_msg('Inserting records into sprdcalctmp2 for resource assignment context');
1347: End If;
1348: FORALL i IN l_res_Asgn_Id_Tab.FIRST .. l_res_Asgn_Id_Tab.LAST
1349: UPDATE /*+ INDEX(TMP PA_FP_SPREAD_CALC_TMP2_N1) */ pa_fp_spread_calc_tmp2 tmp
1350: SET ( tmp.quantity
1351: ,tmp.txn_raw_cost
1352: ,tmp.txn_burdened_cost
1353: ,tmp.txn_revenue

Line 1401: UPDATE /+ INDEX(TMP PA_FP_SPREAD_CALC_TMP2_N1) / pa_fp_spread_calc_tmp2 tmp

1397: WHERE tmp.resource_assignment_id = l_res_Asgn_Id_Tab(i)
1398: AND tmp.txn_currency_code = l_txn_cur_code_Tab(i);
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

Line 1419: UPDATE /+ INDEX(TMP PA_FP_SPREAD_CALC_TMP2_N1) / pa_fp_spread_calc_tmp2 tmp

1415: IF P_PA_DEBUG_MODE = 'Y' Then
1416: print_msg('Updating calcTmp2 with budgetLine values');
1417: End If;
1418: FORALL i IN l_res_Asgn_Id_Tab.FIRST .. l_res_Asgn_Id_Tab.LAST
1419: UPDATE /*+ INDEX(TMP PA_FP_SPREAD_CALC_TMP2_N1) */ pa_fp_spread_calc_tmp2 tmp
1420: SET (tmp.avg_cost_rate_override
1421: ,tmp.avg_burden_rate_override
1422: ,tmp.avg_bill_rate_override
1423: /* Bug fix:4693839 */

Line 1458: UPDATE /+ INDEX(TMP PA_FP_SPREAD_CALC_TMP2_N1) / pa_fp_spread_calc_tmp2 tmp

1454: OR tmp.start_date is NULL
1455: );
1456:
1457: FORALL i IN l_res_Asgn_Id_Tab.FIRST .. l_res_Asgn_Id_Tab.LAST
1458: UPDATE /*+ INDEX(TMP PA_FP_SPREAD_CALC_TMP2_N1) */ pa_fp_spread_calc_tmp2 tmp
1459: SET (tmp.quantity
1460: ,tmp.txn_raw_cost
1461: ,tmp.txn_burdened_cost
1462: ,tmp.txn_revenue

Line 1596: FOR tmp in (select * from pa_fp_spread_calc_tmp2 where budget_version_id = p_budget_version_id) LOOP

1592: );
1593: --print_msg('Number of budgtLines got updated['||l_res_Asgn_Id_Tab.count||']');
1594: END IF; --}
1595: /** added this for debug testing
1596: FOR tmp in (select * from pa_fp_spread_calc_tmp2 where budget_version_id = p_budget_version_id) LOOP
1597: print_msg('Res['||tmp.resource_assignment_id||']blTxncur['||tmp.txn_currency_code||'blQty['||tmp.quantity||']');
1598: print_msg('RawCst['||tmp.txn_raw_cost||']BdCst['||tmp.txn_burdened_cost||']Rev['||tmp.txn_revenue||']');
1599: print_msg('cstRt['||tmp.cost_rate||']cstRtOvr['||tmp.cost_rate_override||']BdRt['||tmp.burden_cost_rate||']');
1600: print_msg('BdRtOv['||tmp.burden_cost_rate_override||']BilRt['||tmp.bill_rate||']BilRtOvr['||tmp.bill_rate_override||']');

Line 2056: UPDATE pa_fp_spread_calc_tmp tmp

2052: end loop;
2053: --*/
2054:
2055: FORALL i IN l_rowid_tab.FIRST .. l_rowid_tab.LAST
2056: UPDATE pa_fp_spread_calc_tmp tmp
2057: SET tmp.QUANTITY_CHANGED_FLAG = NVL(l_quantity_ch_flag_tab(i),'N')
2058: ,tmp.COST_RATE_CHANGED_FLAG = NVL(l_costRt_ch_flag_tab(i),'N')
2059: ,tmp.BURDEN_RATE_CHANGED_FLAG = NVL(l_burdRt_ch_flag_tab(i),'N')
2060: ,tmp.BILL_RATE_CHANGED_FLAG = NVL(l_billRt_ch_flag_tab(i),'N')

Line 2187: FROM pa_fp_spread_calc_tmp tmp

2183: ,NVL(tmp.SP_FIX_DATE_CHANGE_FLAG,'N') spfix_date_change_flag
2184: ,NVL(tmp.MFC_COST_CHANGE_FLAG,'N') mfc_cost_change_flag
2185: ,NVL(tmp.RLM_ID_CHANGE_FLAG,'N') rlm_id_change_flag
2186: ,NVL(tmp.system_reference_var1 ,'N') ra_in_multi_cur_flag
2187: FROM pa_fp_spread_calc_tmp tmp
2188: ,pa_resource_assignments ra
2189: WHERE tmp.budget_version_id = p_budget_version_id
2190: AND tmp.resource_assignment_id = ra.resource_assignment_id
2191: AND ra.rate_based_flag = 'N'

Line 2292: UPDATE /+ INDEX(TMP PA_FP_SPREAD_CALC_TMP_N1) / pa_fp_spread_calc_tmp tmp

2288: WHERE bl.resource_assignment_id = l_resource_assignment_tab(i)
2289: AND bl.txn_currency_code = l_txn_currency_code_tab(i);
2290:
2291: FORALL i IN l_resource_assignment_tab.FIRST .. l_resource_assignment_tab.LAST
2292: UPDATE /*+ INDEX(TMP PA_FP_SPREAD_CALC_TMP_N1) */ pa_fp_spread_calc_tmp tmp
2293: SET tmp.cost_rate_override = l_cost_rate_override_tab(i)
2294: ,tmp.burden_cost_rate_override = l_burden_rate_override_tab(i)
2295: ,tmp.bill_rate_override = l_bill_rate_override_tab(i)
2296: WHERE tmp.resource_assignment_id = l_resource_assignment_tab(i)

Line 2355: FROM pa_fp_spread_calc_tmp tmp

2351: ,NVL(tmp.MFC_COST_CHANGE_FLAG,'N') mfc_cost_change_flag
2352: ,NVL(tmp.RLM_ID_CHANGE_FLAG,'N') rlm_id_change_flag
2353: ,NVL(tmp.system_reference_var1 ,'N') ra_in_multi_cur_flag
2354: ,NVL(tmp.DELETE_BL_FLAG,'N') delete_bl_flag
2355: FROM pa_fp_spread_calc_tmp tmp
2356: ,pa_resource_assignments ra
2357: WHERE tmp.budget_version_id = p_budget_version_id
2358: AND tmp.resource_assignment_id = ra.resource_assignment_id
2359: AND nvl(ra.rate_based_flag,'N') = 'N'

Line 2489: UPDATE /+ INDEX(TMP PA_FP_SPREAD_CALC_TMP_N1) / pa_fp_spread_calc_tmp tmp

2485: IF P_PA_DEBUG_MODE = 'Y' Then
2486: print_msg('1.4: Update spread calc tmp with quantity = raw cost');
2487: End If;
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

Line 2552: from pa_fp_spread_calc_tmp tmp

2548: ,((tmp.txn_revenue - nvl(tmp.bl_txn_init_revenue,0))/
2549: (nvl(tmp.bl_txn_raw_cost,0)-nvl(tmp.bl_txn_init_raw_cost,0)))))
2550: ,decode(nvl(tmp.burden_cost_changed_flag,'N'), 'Y'
2551: ,nvl(l_burden_rate_override_tab(i),rtx.txn_burden_cost_rate_override))
2552: from pa_fp_spread_calc_tmp tmp
2553: where tmp.resource_assignment_id = rtx.resource_assignment_id
2554: and tmp.txn_currency_code = rtx.txn_currency_code
2555: and tmp.raw_cost_changed_flag = 'Y'
2556: )

Line 2564: UPDATE /+ INDEX(TMP PA_FP_SPREAD_CALC_TMP_N1) / pa_fp_spread_calc_tmp tmp

2560:
2561: End If;
2562: --print_msg('1.1.3: update spread calc tmp to rate overrides');
2563: FORALL i IN l_resource_assignment_tab.FIRST .. l_resource_assignment_tab.LAST
2564: UPDATE /*+ INDEX(TMP PA_FP_SPREAD_CALC_TMP_N1) */ pa_fp_spread_calc_tmp tmp
2565: SET tmp.re_spread_amts_flag =
2566: decode(tmp.re_spread_amts_flag,'Y','Y',l_bl_resprd_flag_tab(i))
2567: ,tmp.delete_bl_flag = decode(tmp.delete_bl_flag,'Y','Y'
2568: ,decode(l_bl_resprd_flag_tab(i),'Y','Y',tmp.delete_bl_flag))

Line 2677: FROM pa_fp_spread_calc_tmp tmp

2673: ,NVL(tmp.bill_rate_changed_flag,'N') bill_rate_changed_flag
2674: ,NVL(tmp.raw_cost_changed_flag,'N') raw_cost_changed_flag
2675: ,NVL(tmp.burden_cost_changed_flag,'N') burden_cost_changed_flag
2676: ,NVL(tmp.revenue_changed_flag,'N') revenue_changed_flag
2677: FROM pa_fp_spread_calc_tmp tmp
2678: ,pa_resource_assignments ra
2679: ,pa_resource_list_members rlm
2680: WHERE ra.budget_version_id = p_budget_version_id
2681: AND ra.resource_assignment_id = tmp.resource_assignment_id

Line 2723: FROM pa_fp_spread_calc_tmp tmp

2719: ,NVL(tmp.raw_cost_changed_flag,'N') raw_cost_changed_flag
2720: ,NVL(tmp.burden_cost_changed_flag,'N') burden_cost_changed_flag
2721: ,NVL(tmp.revenue_changed_flag,'N') revenue_changed_flag
2722: ,tmp.bill_markup_percentage
2723: FROM pa_fp_spread_calc_tmp tmp
2724: ,pa_resource_assignments ra
2725: ,pa_resource_list_members rlm
2726: WHERE ra.budget_version_id = p_budget_version_id
2727: AND ra.resource_assignment_id = tmp.resource_assignment_id

Line 3245: DELETE FROM pa_fp_spread_calc_tmp2;

3241:
3242: /* before resetting the rate base flag check whenter budget lines exists for this planning
3243: * resource with other currency. if so abort the process
3244: */
3245: DELETE FROM pa_fp_spread_calc_tmp2;
3246: FORALL i IN l_resource_assignment_tab.FIRST .. l_resource_assignment_tab.LAST
3247: INSERT INTO pa_fp_spread_calc_tmp2 tmp2
3248: (budget_version_id
3249: ,resource_assignment_id

Line 3247: INSERT INTO pa_fp_spread_calc_tmp2 tmp2

3243: * resource with other currency. if so abort the process
3244: */
3245: DELETE FROM pa_fp_spread_calc_tmp2;
3246: FORALL i IN l_resource_assignment_tab.FIRST .. l_resource_assignment_tab.LAST
3247: INSERT INTO pa_fp_spread_calc_tmp2 tmp2
3248: (budget_version_id
3249: ,resource_assignment_id
3250: ,txn_currency_code
3251: ,task_id

Line 3254: SELECT /+ INDEX(TMP PA_FP_SPREAD_CALC_TMP_N1) / p_budget_version_id

3250: ,txn_currency_code
3251: ,task_id
3252: ,resource_name
3253: )
3254: SELECT /*+ INDEX(TMP PA_FP_SPREAD_CALC_TMP_N1) */ p_budget_version_id
3255: ,tmp.resource_assignment_id
3256: ,tmp.txn_currency_code
3257: ,tmp.task_id
3258: ,tmp.resource_name

Line 3259: FROM pa_fp_spread_calc_tmp tmp

3255: ,tmp.resource_assignment_id
3256: ,tmp.txn_currency_code
3257: ,tmp.task_id
3258: ,tmp.resource_name
3259: FROM pa_fp_spread_calc_tmp tmp
3260: WHERE tmp.budget_version_id = p_budget_version_id
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'

Line 3276: DELETE FROM pa_fp_spread_calc_tmp2;

3272: );
3273:
3274: ELSE -- source context = BUDGET LINE then
3275:
3276: DELETE FROM pa_fp_spread_calc_tmp2;
3277: FORALL i IN l_resource_assignment_tab.FIRST .. l_resource_assignment_tab.LAST
3278: INSERT INTO pa_fp_spread_calc_tmp2 tmp2
3279: (budget_version_id
3280: ,resource_assignment_id

Line 3278: INSERT INTO pa_fp_spread_calc_tmp2 tmp2

3274: ELSE -- source context = BUDGET LINE then
3275:
3276: DELETE FROM pa_fp_spread_calc_tmp2;
3277: FORALL i IN l_resource_assignment_tab.FIRST .. l_resource_assignment_tab.LAST
3278: INSERT INTO pa_fp_spread_calc_tmp2 tmp2
3279: (budget_version_id
3280: ,resource_assignment_id
3281: ,txn_currency_code
3282: ,task_id

Line 3287: SELECT /+ INDEX(TMP PA_FP_SPREAD_CALC_TMP_N1) / p_budget_version_id

3283: ,resource_name
3284: ,start_date
3285: ,end_date
3286: )
3287: SELECT /*+ INDEX(TMP PA_FP_SPREAD_CALC_TMP_N1) */ p_budget_version_id
3288: ,tmp.resource_assignment_id
3289: ,tmp.txn_currency_code
3290: ,tmp.task_id
3291: ,tmp.resource_name

Line 3294: FROM pa_fp_spread_calc_tmp tmp

3290: ,tmp.task_id
3291: ,tmp.resource_name
3292: ,tmp.start_date
3293: ,tmp.end_date
3294: FROM pa_fp_spread_calc_tmp tmp
3295: WHERE tmp.budget_version_id = p_budget_version_id
3296: AND tmp.resource_assignment_id = l_resource_assignment_tab(i)
3297: AND tmp.txn_currency_code = l_txn_currency_code_tab(i)
3298: AND tmp.start_date = l_start_date_tab(i)

Line 3318: FROM pa_fp_spread_calc_tmp2 tmp2

3314: ,tmp2.txn_currency_code
3315: ,tmp2.task_id
3316: ,tmp2.resource_name
3317: ,tmp2.start_date
3318: FROM pa_fp_spread_calc_tmp2 tmp2
3319: WHERE tmp2.budget_version_id = p_budget_version_id ) LOOP --{
3320:
3321: g_stage := 'PA_FP_RATE_BASE_QTY_REQD:RaId['||i.resource_assignment_id||']';
3322: g_stage := 'Currency['||i.txn_currency_code||']SDate['||i.start_date||']';

Line 3403: UPDATE /+ INDEX(TMP PA_FP_SPREAD_CALC_TMP_N1) / pa_fp_spread_calc_tmp tmp

3399: End If;
3400: /* Now update the rollup tmp with the new qty and raw cost */
3401: print_msg('Updating tmp table with qty = rawcost or burden cost');
3402: FORALL i IN l_resource_assignment_tab.FIRST .. l_resource_assignment_tab.LAST
3403: UPDATE /*+ INDEX(TMP PA_FP_SPREAD_CALC_TMP_N1) */ pa_fp_spread_calc_tmp tmp
3404: SET tmp.quantity = NVL(l_quantity_tab(i),tmp.quantity)
3405: ,tmp.txn_raw_cost = NVL(l_raw_cost_tab(i),tmp.txn_raw_cost)
3406: ,tmp.cost_rate_override = decode(l_rwRtSetFlag_Tab(i),'Y',NULL,NVL(l_rw_cost_rate_override_tab(i),tmp.cost_rate_override))
3407: ,tmp.bill_rate_override = decode(l_bilRtSetFlag_Tab(i),'Y',NULL,NVL(l_bill_rate_override_tab(i),tmp.bill_rate_override))

Line 3541: FROM pa_fp_spread_calc_tmp tmp

3537: ,NVL(tmp.delete_bl_flag,'N') delete_bl_flag
3538: ,NVL(tmp.raw_cost_changed_flag,'N') raw_cost_changed_flag
3539: ,NVL(tmp.burden_cost_changed_flag,'N') burden_cost_changed_flag
3540: ,NVL(tmp.revenue_changed_flag,'N') revenue_changed_flag
3541: FROM pa_fp_spread_calc_tmp tmp
3542: ,pa_resource_assignments ra
3543: ,pa_resource_list_members rlm
3544: WHERE ra.budget_version_id = p_budget_version_id
3545: AND ra.resource_assignment_id = tmp.resource_assignment_id

Line 3569: from pa_fp_spread_calc_tmp bl

3565: Cursor cur_tmp_chk(p_ra_id Number,p_txn_cur_code Varchar2) IS
3566: SELECT 'Y'
3567: FROM dual
3568: WHERE EXISTS (select null
3569: from pa_fp_spread_calc_tmp bl
3570: where bl.resource_assignment_id = p_ra_id
3571: /* Bug fix:4083873 and bl.txn_currency_code = p_txn_cur_code */
3572: and nvl(bl.quantity,0) <> 0
3573: );

Line 3589: FROM pa_fp_spread_calc_tmp tmp

3585: ,tmp.start_date
3586: ,tmp.end_date
3587: ,tmp.bl_quantity
3588: ,rlm.unit_of_measure uom
3589: FROM pa_fp_spread_calc_tmp tmp
3590: ,pa_resource_assignments ra
3591: ,pa_resource_list_members rlm
3592: WHERE tmp.budget_version_id = p_budget_version_id
3593: AND ra.resource_assignment_id = tmp.resource_assignment_id

Line 3726: UPDATE /+ INDEX(TMP PA_FP_SPREAD_CALC_TMP_N1) / pa_fp_spread_calc_tmp tmp

3722: g_stage := 'Check_ratebased_pltrxns:102';
3723: --print_msg(g_stage);
3724: l_reset_plsql_tab_flag := 'Y';
3725: FORALL i IN l_resource_assignment_tab.FIRST ..l_resource_assignment_tab.LAST
3726: UPDATE /*+ INDEX(TMP PA_FP_SPREAD_CALC_TMP_N1) */ pa_fp_spread_calc_tmp tmp
3727: SET tmp.quantity = l_quantity_tab(i)
3728: ,tmp.txn_raw_cost = decode(nvl(tmp.txn_raw_cost,0),0,l_raw_cost_tab(i),tmp.txn_raw_cost)
3729: ,tmp.cost_rate_override = decode(l_revenue_only_flag_tab(i),'Y',0,1)
3730: ,tmp.burden_cost_rate_override = decode(l_revenue_only_flag_tab(i),'Y',0

Line 4146: UPDATE /+ INDEX(TMP PA_FP_SPREAD_CALC_TMP_N1) / pa_fp_spread_calc_tmp tmp

4142: IF P_PA_DEBUG_MODE = 'Y' Then
4143: print_msg('Updating rollup tmp with qty = rawcost or burden cost');
4144: End If;
4145: FORALL i IN l_resource_assignment_tab.FIRST .. l_resource_assignment_tab.LAST
4146: UPDATE /*+ INDEX(TMP PA_FP_SPREAD_CALC_TMP_N1) */ pa_fp_spread_calc_tmp tmp
4147: SET tmp.quantity = l_quantity_tab(i)
4148: ,tmp.txn_raw_cost = decode(nvl(tmp.txn_raw_cost,0),0,l_raw_cost_tab(i),tmp.txn_raw_cost)
4149: ,tmp.cost_rate_override = decode(nvl(l_revenue_only_flag_tab(i),'N'),'Y',0
4150: ,decode(nvl(l_burden_only_flag_tab(i),'N'),'Y',1,tmp.cost_rate_override))

Line 4236: ,pa_fp_spread_calc_tmp tmp

4232: ,DECODE(ra.spread_curve_id,6,ra.sp_fixed_date,ra.planning_end_date) planning_end_date
4233: ,tmp.task_name
4234: ,tmp.resource_name
4235: FROM pa_resource_assignments ra
4236: ,pa_fp_spread_calc_tmp tmp
4237: WHERE ra.budget_version_id = p_budget_verson_id
4238: AND ra.resource_assignment_id = tmp.resource_assignment_id
4239: AND NOT EXISTS
4240: (SELECT 'No period exist'

Line 4329: FROM pa_fp_spread_calc_tmp tmp

4325: ,tmp.txn_currency_code
4326: ,ra.planning_start_date
4327: ,ra.planning_end_date
4328: ,rl.alias resource_name
4329: FROM pa_fp_spread_calc_tmp tmp
4330: ,pa_resource_assignments ra
4331: ,pa_resource_list_members rl
4332: WHERE tmp.budget_version_id = p_budget_version_id
4333: AND ra.resource_assignment_id = tmp.resource_assignment_id

Line 4382: ,pa_fp_spread_calc_tmp tmp

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
4386: AND NVL(tmp.plan_dates_change_flag,'N') = 'Y'

Line 5295: UPDATE /+ INDEX(TMP PA_FP_SPREAD_CALC_TMP_N1) / pa_fp_spread_calc_tmp tmp

5291: x_return_status := 'S';
5292: x_msg_data := NULL;
5293: IF p_resource_assignment_id_tab.COUNT > 0 THEN
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

Line 5312: UPDATE /+ INDEX(TMP PA_FP_SPREAD_CALC_TMP_N1) / pa_fp_spread_calc_tmp tmp

5308: * if planning end date is prior to MAX date then qty should be spread to planned end date
5309: * based on this logic Now update the DAT1 and DAT2 from planning dates
5310: */
5311: FORALL i IN p_resource_assignment_id_tab.FIRST .. p_resource_assignment_id_tab.LAST
5312: UPDATE /*+ INDEX(TMP PA_FP_SPREAD_CALC_TMP_N1) */ pa_fp_spread_calc_tmp tmp
5313: SET (tmp.SYSTEM_REFERENCE_DAT1
5314: ,tmp.SYSTEM_REFERENCE_DAT2) = (select decode(tmp.SYSTEM_REFERENCE_DAT1,NULL,NULL
5315: ,decode(sign(trunc(tmp.SYSTEM_REFERENCE_DAT1)-trunc(ra.planning_start_date)),-1
5316: ,ra.planning_start_date,tmp.SYSTEM_REFERENCE_DAT1))

Line 5567: ,pa_fp_spread_calc_tmp tmp

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
5571: AND NVL(tmp.system_reference_var1,'N') = 'Y'

Line 5579: from pa_fp_spread_calc_tmp tmp1

5575: OR NVL(tmp.sp_fix_date_change_flag,'N') = 'Y'
5576: OR NVL(tmp.rlm_id_change_flag,'N') = 'Y'
5577: )
5578: AND NOT EXISTS (select null
5579: from pa_fp_spread_calc_tmp tmp1
5580: where tmp1.budget_version_id = p_budget_version_id
5581: and tmp1.resource_assignment_id = tmp.resource_assignment_id
5582: and tmp1.txn_currency_code = bl.txn_currency_code
5583: )

Line 5591: INSERT INTO pa_fp_spread_calc_tmp

5587: --print_msg('Number of ra+txn combo selected from bl['||sql%rowcount||']');
5588:
5589: IF l_raId_Tab.COUNT > 0 THEN
5590: FORALL i IN l_raId_Tab.FIRST .. l_raId_Tab.LAST
5591: INSERT INTO pa_fp_spread_calc_tmp
5592: (BUDGET_VERSION_ID
5593: ,BUDGET_VERSION_TYPE
5594: ,RESOURCE_ASSIGNMENT_ID --resource_assignment_id
5595: ,TXN_CURRENCY_CODE --txn_currency_code

Line 5610: UPDATE /+ INDEX(TMP PA_FP_SPREAD_CALC_TMP_N1) / pa_fp_spread_calc_tmp tmp

5606: End If;
5607:
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

Line 5639: ) = (SELECT /+ INDEX(TMP1 PA_FP_SPREAD_CALC_TMP_N1) /

5635: ,SYSTEM_REFERENCE_VAR1
5636: ,PLAN_START_DATE_SHRUNK_FLAG
5637: ,PLAN_END_DATE_SHRUNK_FLAG
5638: ,RLM_ID_CHANGE_FLAG
5639: ) = (SELECT /*+ INDEX(TMP1 PA_FP_SPREAD_CALC_TMP_N1) */
5640: tmp1.OLD_SPREAD_CURVE_ID
5641: ,tmp1.NEW_SPREAD_CURVE_ID
5642: ,tmp1.OLD_SP_FIX_DATE
5643: ,tmp1.NEW_SP_FIX_DATE

Line 5662: FROM pa_fp_spread_calc_tmp tmp1

5658: ,tmp1.SYSTEM_REFERENCE_VAR1
5659: ,tmp1.PLAN_START_DATE_SHRUNK_FLAG
5660: ,tmp1.PLAN_END_DATE_SHRUNK_FLAG
5661: ,tmp1.RLM_ID_CHANGE_FLAG
5662: FROM pa_fp_spread_calc_tmp tmp1
5663: WHERE tmp1.budget_version_id = p_budget_version_id
5664: AND tmp1.resource_assignment_id = tmp.resource_assignment_id
5665: AND tmp1.txn_currency_code <> tmp.txn_currency_code
5666: AND NVL(tmp1.SYSTEM_REFERENCE_VAR2,'N') = 'N'

Line 5679: UPDATE pa_fp_spread_calc_tmp tmp

5675:
5676: /* Note: when planning resource is changed, then ignore all other attribute changes on the RA
5677: * change in the planning resource should be treated as a new RA
5678: */
5679: UPDATE pa_fp_spread_calc_tmp tmp
5680: SET tmp.SP_CURVE_CHANGE_FLAG = decode(nvl(tmp.rlm_id_change_flag,'N'),'Y','N',tmp.SP_CURVE_CHANGE_FLAG)
5681: ,tmp.PLAN_DATES_CHANGE_FLAG = decode(nvl(tmp.rlm_id_change_flag,'N'),'Y','N',tmp.PLAN_DATES_CHANGE_FLAG)
5682: ,tmp.SP_FIX_DATE_CHANGE_FLAG = decode(nvl(tmp.rlm_id_change_flag,'N'),'Y','N',tmp.SP_FIX_DATE_CHANGE_FLAG)
5683: ,tmp.MFC_COST_CHANGE_FLAG = decode(nvl(tmp.rlm_id_change_flag,'N'),'Y','N',tmp.MFC_COST_CHANGE_FLAG)

Line 5716: UPDATE pa_fp_spread_calc_tmp tmp

5712: IF (p_calling_module NOT IN ('BUDGET_GENERATION','FORECAST_GENERATION')
5713: and NVL(g_apply_progress_flag,'N') <> 'Y'
5714: and g_source_context = 'RESOURCE_ASSIGNMENT' ) Then --{
5715: /* If multiple RA + Txn cur combo is passed, then updates the res attributes whereever it is null */
5716: UPDATE pa_fp_spread_calc_tmp tmp
5717: SET tmp.RLM_ID_CHANGE_FLAG = decode(tmp.RLM_ID_CHANGE_FLAG,NULL,(select tmp1.RLM_ID_CHANGE_FLAG
5718: from pa_fp_spread_calc_tmp tmp1
5719: where tmp1.resource_assignment_id = tmp.resource_assignment_id
5720: and tmp1.txn_currency_code <> tmp.txn_currency_code

Line 5718: from pa_fp_spread_calc_tmp tmp1

5714: and g_source_context = 'RESOURCE_ASSIGNMENT' ) Then --{
5715: /* If multiple RA + Txn cur combo is passed, then updates the res attributes whereever it is null */
5716: UPDATE pa_fp_spread_calc_tmp tmp
5717: SET tmp.RLM_ID_CHANGE_FLAG = decode(tmp.RLM_ID_CHANGE_FLAG,NULL,(select tmp1.RLM_ID_CHANGE_FLAG
5718: from pa_fp_spread_calc_tmp tmp1
5719: where tmp1.resource_assignment_id = tmp.resource_assignment_id
5720: and tmp1.txn_currency_code <> tmp.txn_currency_code
5721: and tmp1.RLM_ID_CHANGE_FLAG is not null
5722: and rownum = 1

Line 5725: from pa_fp_spread_calc_tmp tmp1

5721: and tmp1.RLM_ID_CHANGE_FLAG is not null
5722: and rownum = 1
5723: ),tmp.RLM_ID_CHANGE_FLAG)
5724: ,tmp.OLD_SPREAD_CURVE_ID = decode(tmp.OLD_SPREAD_CURVE_ID,NULL,(select tmp1.OLD_SPREAD_CURVE_ID
5725: from pa_fp_spread_calc_tmp tmp1
5726: where tmp1.resource_assignment_id = tmp.resource_assignment_id
5727: and tmp1.txn_currency_code <> tmp.txn_currency_code
5728: and tmp1.OLD_SPREAD_CURVE_ID is not null
5729: and rownum = 1

Line 5732: from pa_fp_spread_calc_tmp tmp1

5728: and tmp1.OLD_SPREAD_CURVE_ID is not null
5729: and rownum = 1
5730: ),tmp.OLD_SPREAD_CURVE_ID)
5731: ,tmp.NEW_SPREAD_CURVE_ID = decode(tmp.NEW_SPREAD_CURVE_ID,NULL,(select tmp1.NEW_SPREAD_CURVE_ID
5732: from pa_fp_spread_calc_tmp tmp1
5733: where tmp1.resource_assignment_id = tmp.resource_assignment_id
5734: and tmp1.txn_currency_code <> tmp.txn_currency_code
5735: and tmp1.NEW_SPREAD_CURVE_ID is not null
5736: and rownum = 1

Line 5739: from pa_fp_spread_calc_tmp tmp1

5735: and tmp1.NEW_SPREAD_CURVE_ID is not null
5736: and rownum = 1
5737: ),tmp.NEW_SPREAD_CURVE_ID)
5738: ,tmp.OLD_SP_FIX_DATE = decode(tmp.OLD_SP_FIX_DATE,NULL,(select tmp1.OLD_SP_FIX_DATE
5739: from pa_fp_spread_calc_tmp tmp1
5740: where tmp1.resource_assignment_id = tmp.resource_assignment_id
5741: and tmp1.txn_currency_code <> tmp.txn_currency_code
5742: and tmp1.OLD_SP_FIX_DATE is not null
5743: and rownum = 1

Line 5746: from pa_fp_spread_calc_tmp tmp1

5742: and tmp1.OLD_SP_FIX_DATE is not null
5743: and rownum = 1
5744: ),tmp.OLD_SP_FIX_DATE)
5745: ,tmp.NEW_SP_FIX_DATE = decode(tmp.NEW_SP_FIX_DATE,NULL,(select tmp1.NEW_SP_FIX_DATE
5746: from pa_fp_spread_calc_tmp tmp1
5747: where tmp1.resource_assignment_id = tmp.resource_assignment_id
5748: and tmp1.txn_currency_code <> tmp.txn_currency_code
5749: and tmp1.NEW_SP_FIX_DATE is not null
5750: and rownum = 1

Line 5753: from pa_fp_spread_calc_tmp tmp1

5749: and tmp1.NEW_SP_FIX_DATE is not null
5750: and rownum = 1
5751: ),tmp.NEW_SP_FIX_DATE)
5752: ,tmp.OLD_MFC_COST_TYPE_ID = decode(tmp.OLD_MFC_COST_TYPE_ID,NULL,(select tmp1.OLD_MFC_COST_TYPE_ID
5753: from pa_fp_spread_calc_tmp tmp1
5754: where tmp1.resource_assignment_id = tmp.resource_assignment_id
5755: and tmp1.txn_currency_code <> tmp.txn_currency_code
5756: and tmp1.OLD_MFC_COST_TYPE_ID is not null
5757: and rownum = 1

Line 5760: from pa_fp_spread_calc_tmp tmp1

5756: and tmp1.OLD_MFC_COST_TYPE_ID is not null
5757: and rownum = 1
5758: ),tmp.OLD_MFC_COST_TYPE_ID)
5759: ,tmp.NEW_MFC_COST_TYPE_ID = decode(tmp.NEW_MFC_COST_TYPE_ID,NULL,(select tmp1.NEW_MFC_COST_TYPE_ID
5760: from pa_fp_spread_calc_tmp tmp1
5761: where tmp1.resource_assignment_id = tmp.resource_assignment_id
5762: and tmp1.txn_currency_code <> tmp.txn_currency_code
5763: and tmp1.NEW_MFC_COST_TYPE_ID is not null
5764: and rownum = 1

Line 5767: from pa_fp_spread_calc_tmp tmp1

5763: and tmp1.NEW_MFC_COST_TYPE_ID is not null
5764: and rownum = 1
5765: ),tmp.NEW_MFC_COST_TYPE_ID)
5766: ,tmp.OLD_PLAN_START_DATE = decode(tmp.OLD_PLAN_START_DATE,NULL,(select tmp1.OLD_PLAN_START_DATE
5767: from pa_fp_spread_calc_tmp tmp1
5768: where tmp1.resource_assignment_id = tmp.resource_assignment_id
5769: and tmp1.txn_currency_code <> tmp.txn_currency_code
5770: and tmp1.OLD_PLAN_START_DATE is not null
5771: and rownum = 1

Line 5774: from pa_fp_spread_calc_tmp tmp1

5770: and tmp1.OLD_PLAN_START_DATE is not null
5771: and rownum = 1
5772: ),tmp.OLD_PLAN_START_DATE)
5773: ,tmp.OLD_PLAN_END_DATE = decode(tmp.OLD_PLAN_END_DATE,NULL,(select tmp1.OLD_PLAN_END_DATE
5774: from pa_fp_spread_calc_tmp tmp1
5775: where tmp1.resource_assignment_id = tmp.resource_assignment_id
5776: and tmp1.txn_currency_code <> tmp.txn_currency_code
5777: and tmp1.OLD_PLAN_END_DATE is not null
5778: and rownum = 1

Line 5793: UPDATE pa_fp_spread_calc_tmp tmp

5789: --print_msg('synch_Upd: NumOfRowUpdated['||sql%rowcount||']');
5790:
5791: /* set the respective changed flags */
5792: /* Bug #5031939: remove budget_version id joins in the sub query */
5793: UPDATE pa_fp_spread_calc_tmp tmp
5794: SET tmp.SP_CURVE_CHANGE_FLAG = decode(NVL(tmp.OLD_SPREAD_CURVE_ID,1),NVL(tmp.NEW_SPREAD_CURVE_ID,1),'N','Y')
5795: ,tmp.PLAN_DATES_CHANGE_FLAG =decode(tmp.OLD_PLAN_START_DATE,NULL,'N'
5796: ,decode(tmp.NEW_PLAN_START_DATE,NULL,'N'
5797: ,decode(tmp.OLD_PLAN_START_DATE,tmp.NEW_PLAN_START_DATE,'N','Y')))

Line 5813: UPDATE pa_fp_spread_calc_tmp tmp

5809: GROUP BY bl.resource_assignment_id,bl.txn_currency_code
5810: ))
5811: WHERE tmp.budget_version_id = p_budget_version_id;
5812:
5813: UPDATE pa_fp_spread_calc_tmp tmp
5814: SET tmp.PLAN_DATES_CHANGE_FLAG = decode(tmp.PLAN_DATES_CHANGE_FLAG,'Y','Y'
5815: ,decode(tmp.OLD_PLAN_END_DATE,NULL,'N'
5816: ,decode(tmp.NEW_PLAN_END_DATE,NULL,'N'
5817: ,decode(tmp.OLD_PLAN_END_DATE,tmp.NEW_PLAN_END_DATE,'N','Y'))))

Line 5822: UPDATE pa_fp_spread_calc_tmp tmp

5818: ,tmp.system_reference_var1 = NVL(system_reference_var1,'N')
5819: WHERE tmp.budget_version_id = p_budget_version_id;
5820: --print_msg('Number of rows updated with resp resAtrbflag['||sql%rowcount||']');
5821:
5822: UPDATE pa_fp_spread_calc_tmp tmp
5823: SET tmp.PLAN_START_DATE_SHRUNK_FLAG = decode(tmp.PLAN_DATES_CHANGE_FLAG,'N','N'
5824: ,decode(tmp.new_plan_start_date,NULL,'N'
5825: ,decode(tmp.old_plan_start_date,NULL,'N'
5826: ,decode(sign(trunc(tmp.new_plan_start_date) - trunc(tmp.old_plan_start_date)),1 ,'Y','N'))))

Line 5864: UPDATE pa_fp_spread_calc_tmp tmp

5860: IF (p_calling_module NOT IN ('BUDGET_GENERATION','FORECAST_GENERATION')
5861: and NVL(g_apply_progress_flag,'N') <> 'Y'
5862: and g_source_context = 'RESOURCE_ASSIGNMENT' ) Then --{
5863: /* Bug #5031939: remove budget_version id joins in the sub query */
5864: UPDATE pa_fp_spread_calc_tmp tmp
5865: SET tmp.SP_CURVE_CHANGE_FLAG = (select 'Y'
5866: from dual
5867: where exists (select null
5868: from pa_fp_spread_calc_tmp tmp1

Line 5868: from pa_fp_spread_calc_tmp tmp1

5864: UPDATE pa_fp_spread_calc_tmp tmp
5865: SET tmp.SP_CURVE_CHANGE_FLAG = (select 'Y'
5866: from dual
5867: where exists (select null
5868: from pa_fp_spread_calc_tmp tmp1
5869: where tmp1.resource_assignment_id = tmp.resource_assignment_id
5870: and tmp1.sp_curve_change_flag = 'Y' ))
5871: ,tmp.PLAN_DATES_CHANGE_FLAG = (select 'Y'
5872: from dual

Line 5874: from pa_fp_spread_calc_tmp tmp1

5870: and tmp1.sp_curve_change_flag = 'Y' ))
5871: ,tmp.PLAN_DATES_CHANGE_FLAG = (select 'Y'
5872: from dual
5873: where exists (select null
5874: from pa_fp_spread_calc_tmp tmp1
5875: where tmp1.resource_assignment_id = tmp.resource_assignment_id
5876: and tmp1.plan_dates_change_flag = 'Y' ))
5877: ,tmp.SP_FIX_DATE_CHANGE_FLAG = (select 'Y'
5878: from dual

Line 5880: from pa_fp_spread_calc_tmp tmp1

5876: and tmp1.plan_dates_change_flag = 'Y' ))
5877: ,tmp.SP_FIX_DATE_CHANGE_FLAG = (select 'Y'
5878: from dual
5879: where exists (select null
5880: from pa_fp_spread_calc_tmp tmp1
5881: where tmp1.resource_assignment_id = tmp.resource_assignment_id
5882: and tmp1.sp_fix_date_change_flag = 'Y' ))
5883: ,tmp.MFC_COST_CHANGE_FLAG = (select 'Y'
5884: from dual

Line 5886: from pa_fp_spread_calc_tmp tmp1

5882: and tmp1.sp_fix_date_change_flag = 'Y' ))
5883: ,tmp.MFC_COST_CHANGE_FLAG = (select 'Y'
5884: from dual
5885: where exists (select null
5886: from pa_fp_spread_calc_tmp tmp1
5887: where tmp1.resource_assignment_id = tmp.resource_assignment_id
5888: and tmp1.mfc_cost_change_flag = 'Y' ))
5889: ,tmp.PLAN_START_DATE_SHRUNK_FLAG = (select 'Y'
5890: from dual

Line 5892: from pa_fp_spread_calc_tmp tmp1

5888: and tmp1.mfc_cost_change_flag = 'Y' ))
5889: ,tmp.PLAN_START_DATE_SHRUNK_FLAG = (select 'Y'
5890: from dual
5891: where exists (select null
5892: from pa_fp_spread_calc_tmp tmp1
5893: where tmp1.resource_assignment_id = tmp.resource_assignment_id
5894: and tmp1.plan_start_date_shrunk_flag = 'Y'))
5895: ,tmp.PLAN_END_DATE_SHRUNK_FLAG = (select 'Y'
5896: from dual

Line 5898: from pa_fp_spread_calc_tmp tmp1

5894: and tmp1.plan_start_date_shrunk_flag = 'Y'))
5895: ,tmp.PLAN_END_DATE_SHRUNK_FLAG = (select 'Y'
5896: from dual
5897: where exists (select null
5898: from pa_fp_spread_calc_tmp tmp1
5899: where tmp1.resource_assignment_id = tmp.resource_assignment_id
5900: and tmp1.plan_end_date_shrunk_flag = 'Y'))
5901: ,tmp.RLM_ID_CHANGE_FLAG = (select 'Y'
5902: from dual

Line 5904: from pa_fp_spread_calc_tmp tmp1

5900: and tmp1.plan_end_date_shrunk_flag = 'Y'))
5901: ,tmp.RLM_ID_CHANGE_FLAG = (select 'Y'
5902: from dual
5903: where exists (select null
5904: from pa_fp_spread_calc_tmp tmp1
5905: where tmp1.resource_assignment_id = tmp.resource_assignment_id
5906: and tmp1.rlm_id_change_flag = 'Y'))
5907: ,(tmp.task_id,tmp.resource_name) =
5908: (select ra.task_id,rlm.alias

Line 5931: UPDATE pa_fp_spread_calc_tmp tmp

5927: WHERE tmp.budget_version_id = p_budget_version_id;
5928: --print_msg('Number of rows updated with resp resAtrbflag['||sql%rowcount||']');
5929:
5930: /* Now set all the resource attributs N when planning resource is changed */
5931: UPDATE pa_fp_spread_calc_tmp tmp
5932: SET tmp.SP_CURVE_CHANGE_FLAG = 'N'
5933: ,tmp.PLAN_DATES_CHANGE_FLAG = 'N'
5934: ,tmp.SP_FIX_DATE_CHANGE_FLAG = 'N'
5935: ,tmp.MFC_COST_CHANGE_FLAG = 'N'

Line 5944: UPDATE pa_fp_spread_calc_tmp tmp

5940: --print_msg('Number of rows updated with resp resAtrbflag['||sql%rowcount||']');
5941:
5942: ELSE
5943: /* This is done to avoid executing the cursors multiple times for passing token values */
5944: UPDATE pa_fp_spread_calc_tmp tmp
5945: SET (tmp.task_id,tmp.resource_name) =
5946: (select ra.task_id,rlm.alias
5947: from pa_resource_assignments ra
5948: ,pa_resource_list_members rlm

Line 6092: FROM pa_fp_spread_calc_tmp tmp

6088: ,NVL(tmp.system_reference_var1,'N') MultiCurrLineFlag
6089: ,NVL(tmp.plan_start_date_shrunk_flag,'N') plan_start_date_shrunk_flag
6090: ,NVL(tmp.plan_end_date_shrunk_flag,'N') plan_end_date_shrunk_flag
6091: ,NVL(tmp.rlm_id_change_flag,'N') rlm_id_change_flag
6092: FROM pa_fp_spread_calc_tmp tmp
6093: WHERE tmp.budget_version_id = p_budget_version_id
6094: AND NVL(tmp.system_reference_var1,'N') = 'Y'
6095: AND NVL(tmp.rlm_id_change_flag,'N') <> 'Y'
6096: AND (tmp.re_spread_amts_flag = 'Y'

Line 6108: FROM pa_fp_spread_calc_tmp tmp

6104: CURSOR cur_MultipleAdjustments_chk IS
6105: SELECT 'Y'
6106: FROM DUAL
6107: WHERE EXISTS (SELECT 'MultipleLAdjustments'
6108: FROM pa_fp_spread_calc_tmp tmp
6109: WHERE tmp.budget_version_id = p_budget_version_id
6110: AND ((decode(tmp.OLD_PLAN_START_DATE,NULL,'N'
6111: ,decode(tmp.NEW_PLAN_START_DATE,NULL,'N'
6112: ,decode(tmp.OLD_PLAN_START_DATE,tmp.NEW_PLAN_START_DATE,'N','Y'))) ='Y')

Line 6137: FROM pa_fp_spread_calc_tmp tmp

6133: ,tmp.old_sp_fix_date
6134: ,tmp.new_sp_fix_date
6135: ,tmp.old_mfc_cost_type_id
6136: ,tmp.new_mfc_cost_type_id
6137: FROM pa_fp_spread_calc_tmp tmp
6138: ,pa_resource_list_members rl
6139: ,pa_resource_assignments ra
6140: WHERE tmp.budget_version_id = p_budget_version_id
6141: AND ra.resource_assignment_id = tmp.resource_assignment_id

Line 6156: FROM pa_fp_spread_calc_tmp tmpb

6152: GROUP BY tmp.resource_assignment_id
6153: HAVING count(*) > 1 ;
6154: *** End of bug fix**/
6155: AND EXISTS (SELECT 'MultipleLAdjustments'
6156: FROM pa_fp_spread_calc_tmp tmpb
6157: WHERE tmpb.budget_version_id = tmp.budget_version_id
6158: AND tmpb.resource_assignment_id = tmp.resource_assignment_id
6159: AND ((decode(tmpb.OLD_PLAN_START_DATE,NULL,'N'
6160: ,decode(tmpb.NEW_PLAN_START_DATE,NULL,'N'

Line 6175: FROM pa_fp_spread_calc_tmp tmp

6171: /* This cursor checks that calling api has passed duplicate set of records */
6172: CURSOR cur_chk_dupRecords IS
6173: SELECT tmp.resource_assignment_id
6174: ,tmp.txn_currency_code
6175: FROM pa_fp_spread_calc_tmp tmp
6176: WHERE tmp.budget_version_id = p_budget_version_id
6177: GROUP BY tmp.resource_assignment_id
6178: ,tmp.txn_currency_code
6179: HAVING COUNT(*) > 1 ;

Line 6195: from pa_fp_spread_calc_tmp tmp

6191: SELECT 'Y'
6192: INTO l_return_flg
6193: FROM DUAL
6194: WHERE EXISTS (Select null
6195: from pa_fp_spread_calc_tmp tmp
6196: where ( NVL(tmp.sp_curve_change_flag,'N') = 'Y'
6197: OR NVL(tmp.plan_dates_change_flag,'N') = 'Y'
6198: OR NVL(tmp.sp_fix_date_change_flag,'N') = 'Y'
6199: OR NVL(tmp.re_spread_amts_flag,'N') = 'Y'

Line 6225: DELETE FROM pa_fp_spread_calc_tmp;

6221: print_msg(' Entered populate_spreadCalc_Tmp API');
6222: End If;
6223: /* populate tmp table with the data */
6224: Init_plsqlTabs;
6225: DELETE FROM pa_fp_spread_calc_tmp;
6226: DELETE FROM pa_fp_rollup_tmp
6227: WHERE budget_version_id = p_budget_version_id;
6228:
6229: bvDetailsRec := NULL;

Line 6337: INSERT INTO pa_fp_spread_calc_tmp

6333: End If;
6334: /* end of Perf Impr:5309529 */
6335: /*******
6336: FORALL i IN x_resource_assignment_tab.FIRST .. x_resource_assignment_tab.LAST
6337: INSERT INTO pa_fp_spread_calc_tmp
6338: (RESOURCE_ASSIGNMENT_ID --resource_assignment_id
6339: ,DELETE_BL_FLAG --delete_budget_lines_flag
6340: ,SPREAD_AMTS_FLAG --spread_amts_flag
6341: ,TXN_CURRENCY_CODE --txn_currency_code

Line 6684: FOR i IN (select * from pa_fp_spread_calc_tmp ) LOOP

6680: END IF; --}
6681: END IF; --}
6682:
6683: /** added this for debug purpose
6684: FOR i IN (select * from pa_fp_spread_calc_tmp ) LOOP
6685: print_msg('Ra['||i.resource_assignment_id||']TxnCur['||i.txn_currency_code||']');
6686: print_msg('respFlag['||i.re_spread_amts_flag||']');
6687: print_msg('spCurvFlag['||i.sp_curve_change_flag||']');
6688: print_msg('PlanDtFlag['||i.plan_dates_change_flag||']');

Line 7001: FROM PA_FP_SPREAD_CALC_TMP tmp

6997: ,x_neg_Qty_Changflag_tab
6998: ,x_neg_Raw_Changflag_tab
6999: ,x_neg_Burd_Changflag_tab
7000: ,x_neg_rev_Changflag_tab
7001: FROM PA_FP_SPREAD_CALC_TMP tmp
7002: WHERE tmp.budget_version_id = p_budget_version_id;
7003:
7004: x_return_status := l_return_status;
7005:

Line 7053: FROM pa_fp_spread_calc_tmp tmp

7049: ,tmp.cost_rate_changed_flag
7050: ,tmp.burden_rate_changed_flag
7051: ,tmp.bill_rate_changed_flag
7052: ,tmp.mfc_cost_change_flag
7053: FROM pa_fp_spread_calc_tmp tmp
7054: WHERE tmp.budget_version_id = p_budget_verson_id
7055: AND nvl(tmp.rlm_id_change_flag,'N') <> 'Y'
7056: AND (tmp.cost_rate_override is NOT NULL
7057: OR tmp.burden_cost_rate_override is NOT NULL

Line 7068: INSERT INTO pa_fp_spread_calc_tmp1

7064: End If;
7065: x_return_status := 'S';
7066: x_msg_data := NULL;
7067:
7068: INSERT INTO pa_fp_spread_calc_tmp1
7069: (RESOURCE_ASSIGNMENT_ID --resource_assignment_id
7070: ,BUDGET_VERSION_ID --budget_version_id
7071: ,BUDGET_VERSION_TYPE
7072: ,BUDGET_LINE_ID

Line 7242: ,PA_FP_SPREAD_CALC_TMP tmp

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
7246: AND bl.txn_currency_code = tmp.txn_currency_code

Line 7271: UPDATE /+ INDEX(TMP1 PA_FP_SPREAD_CALC_TMP1_N1) / pa_fp_spread_calc_tmp1 tmp1

7267: print_msg('mfcCostFlag['||i.mfc_cost_change_flag||'Ver['||i.budget_version_type||']');
7268: print_msg('costRtChFlag['||i.cost_rate_changed_flag||']Rt['||i.cost_rate_override||']');
7269: print_msg('burRtOvr['||i.burden_cost_rate_override||']');
7270: */
7271: UPDATE /*+ INDEX(TMP1 PA_FP_SPREAD_CALC_TMP1_N1) */ pa_fp_spread_calc_tmp1 tmp1
7272: SET tmp1.cost_rate_override = decode(i.budget_version_type,'REVENUE',tmp1.cost_rate_override
7273: ,decode(nvl(i.cost_rate_changed_flag,'N')
7274: ,'Y',decode(i.mfc_cost_change_flag,'Y'
7275: ,decode(nvl(i.cost_rate_override,0),0,NULL,i.cost_rate_override),i.cost_rate_override)

Line 7580: ,pa_fp_spread_calc_tmp1 cache

7576: cache.PROJFUNC_REV_RATE_DATE),
7577: tmp.PROJFUNC_REV_RATE_DATE),
7578: NULL) PROJFUNC_REV_RATE_DATE
7579: FROM pa_fp_rollup_tmp tmp
7580: ,pa_fp_spread_calc_tmp1 cache
7581: WHERE tmp.budget_version_id = p_budget_verson_id
7582: AND tmp.budget_version_id = cache.budget_version_id
7583: AND tmp.resource_assignment_id = cache.resource_assignment_id
7584: AND tmp.txn_currency_code = cache.txn_currency_code

Line 7642: ,pa_fp_spread_calc_tmp1 cache

7638: , cache.TRANSFER_PRICE_RATE
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

Line 8023: FROM pa_fp_spread_calc_tmp tmp

8019: CURSOR cur_chk_dupRecords IS
8020: SELECT tmp.resource_assignment_id
8021: ,tmp.txn_currency_code
8022: ,tmp.start_date
8023: FROM pa_fp_spread_calc_tmp tmp
8024: WHERE tmp.budget_version_id = p_budget_version_id
8025: GROUP BY tmp.resource_assignment_id
8026: ,tmp.txn_currency_code
8027: ,tmp.start_date

Line 8046: DELETE FROM pa_fp_spread_calc_tmp tmp1

8042: */
8043: l_duplicate_record_exists := 'Y';
8044: IF NVL(p_source_context,'RESOURCE_ASSIGNMENT') = 'RESOURCE_ASSIGNMENT' Then
8045: g_stage := 'Validate_duplicate_records:102';
8046: DELETE FROM pa_fp_spread_calc_tmp tmp1
8047: WHERE tmp1.resource_assignment_id = i.resource_assignment_id
8048: AND tmp1.txn_currency_code = i.txn_currency_code
8049: AND tmp1.rowid NOT IN (select min(rowid) from pa_fp_spread_calc_tmp tmp2
8050: where tmp1.resource_assignment_id = tmp2.resource_assignment_id

Line 8049: AND tmp1.rowid NOT IN (select min(rowid) from pa_fp_spread_calc_tmp tmp2

8045: g_stage := 'Validate_duplicate_records:102';
8046: DELETE FROM pa_fp_spread_calc_tmp tmp1
8047: WHERE tmp1.resource_assignment_id = i.resource_assignment_id
8048: AND tmp1.txn_currency_code = i.txn_currency_code
8049: AND tmp1.rowid NOT IN (select min(rowid) from pa_fp_spread_calc_tmp tmp2
8050: where tmp1.resource_assignment_id = tmp2.resource_assignment_id
8051: and tmp1.txn_currency_code = tmp2.txn_currency_code
8052: group by tmp2.resource_assignment_id,tmp2.txn_currency_code
8053: having count(*) > 1

Line 8057: AND EXISTS (select 'Y' from pa_fp_spread_calc_tmp tmp2

8053: having count(*) > 1
8054: )
8055: /* added this to make sure that even if code is executed multiple times this should delete the correct combo */
8056: /* this code is not required if not executed twice for the same combo*/
8057: AND EXISTS (select 'Y' from pa_fp_spread_calc_tmp tmp2
8058: where tmp1.resource_assignment_id = tmp2.resource_assignment_id
8059: and tmp1.txn_currency_code = tmp2.txn_currency_code
8060: group by tmp2.resource_assignment_id,tmp2.txn_currency_code
8061: having count(*) > 1

Line 8065: DELETE FROM pa_fp_spread_calc_tmp tmp1

8061: having count(*) > 1
8062: );
8063: Elsif NVL(p_source_context,'RESOURCE_ASSIGNMENT') = 'BUDGET_LINE' Then
8064: g_stage := 'Validate_duplicate_records:103';
8065: DELETE FROM pa_fp_spread_calc_tmp tmp1
8066: WHERE tmp1.resource_assignment_id = i.resource_assignment_id
8067: AND tmp1.txn_currency_code = i.txn_currency_code
8068: AND tmp1.start_date = i.start_date
8069: AND tmp1.rowid NOT IN (select min(tmp2.rowid) from pa_fp_spread_calc_tmp tmp2

Line 8069: AND tmp1.rowid NOT IN (select min(tmp2.rowid) from pa_fp_spread_calc_tmp tmp2

8065: DELETE FROM pa_fp_spread_calc_tmp tmp1
8066: WHERE tmp1.resource_assignment_id = i.resource_assignment_id
8067: AND tmp1.txn_currency_code = i.txn_currency_code
8068: AND tmp1.start_date = i.start_date
8069: AND tmp1.rowid NOT IN (select min(tmp2.rowid) from pa_fp_spread_calc_tmp tmp2
8070: where tmp1.resource_assignment_id = tmp2.resource_assignment_id
8071: and tmp1.txn_currency_code = tmp2.txn_currency_code
8072: and tmp1.start_date = tmp2.start_date
8073: group by tmp2.resource_assignment_id,tmp2.txn_currency_code,tmp2.start_date

Line 8076: AND EXISTS (select 'Y' from pa_fp_spread_calc_tmp tmp2

8072: and tmp1.start_date = tmp2.start_date
8073: group by tmp2.resource_assignment_id,tmp2.txn_currency_code,tmp2.start_date
8074: having count(*) > 1
8075: )
8076: AND EXISTS (select 'Y' from pa_fp_spread_calc_tmp tmp2
8077: where tmp1.resource_assignment_id = tmp2.resource_assignment_id
8078: and tmp1.txn_currency_code = tmp2.txn_currency_code
8079: and tmp1.start_date = tmp2.start_date
8080: group by tmp2.resource_assignment_id,tmp2.txn_currency_code,tmp2.start_date

Line 8400: ,pa_fp_spread_calc_tmp2 tmp2

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
8404: AND tmp2.txn_currency_code = bl.txn_currency_code

Line 8456: FROM pa_fp_spread_calc_tmp1 tmp1

8452: ,tmp1.burden_rejection_code
8453: ,tmp1.pfc_cur_conv_rejection_code
8454: ,tmp1.pc_cur_conv_rejection_code
8455: ,tmp1.system_reference_num1 Existing_budget_line_id
8456: FROM pa_fp_spread_calc_tmp1 tmp1
8457: WHERE tmp1.budget_version_id = p_budget_version_id;
8458:
8459: l_del_budget_line_id_tab pa_plsql_datatypes.NumTabTyp;
8460: CURSOR cur_delBlLines IS

Line 8462: FROM pa_fp_spread_calc_tmp1 tmp1

8458:
8459: l_del_budget_line_id_tab pa_plsql_datatypes.NumTabTyp;
8460: CURSOR cur_delBlLines IS
8461: SELECT tmp1.budget_line_id
8462: FROM pa_fp_spread_calc_tmp1 tmp1
8463: WHERE tmp1.budget_version_id = p_budget_version_id;
8464:
8465:
8466: CURSOR cur_Tmp2ExblAmts(p_budget_line_id Number) IS

Line 8476: FROM pa_fp_spread_calc_tmp2 tmp2

8472: ,tmp2.bill_rate existing_bill_rate
8473: ,tmp2.bill_rate_override existing_bill_rate_ovride
8474: ,tmp2.bill_markup_percentage existing_markup_percentage
8475: ,tmp2.system_reference_num1 existing_compile_set_id
8476: FROM pa_fp_spread_calc_tmp2 tmp2
8477: WHERE tmp2.budget_version_id = p_budget_version_id
8478: AND tmp2.budget_line_id = p_budget_line_id;
8479: ExBlRec cur_Tmp2ExblAmts%ROWTYPE;
8480:

Line 8752: FROM pa_fp_spread_calc_tmp2 tmp2

8748: ,l_tmp2_cost_rate_ovr_tab
8749: ,l_tmp2_burden_rate_ovr_tab
8750: ,l_tmp2_compile_set_id_tab
8751: ,l_tmp2_rate_based_flag_tab /* bug fix: 4900436 */
8752: FROM pa_fp_spread_calc_tmp2 tmp2
8753: ,pa_resource_assignments ra
8754: WHERE tmp2.budget_version_id = p_budget_version_id
8755: AND ra.resource_assignment_id = tmp2.resource_assignment_id;
8756: END Populate_tmp2Plsql_tab;

Line 8789: DELETE FROM PA_FP_SPREAD_CALC_TMP1;

8785: ) IS
8786: BEGIN
8787: x_return_status := 'S';
8788: print_msg('Entered Populate_blkExcpRecs API');
8789: DELETE FROM PA_FP_SPREAD_CALC_TMP1;
8790: FORALL i IN p_err_error_code_tab.FIRST .. p_err_error_code_tab.LAST
8791: INSERT INTO PA_FP_SPREAD_CALC_TMP1 tmp
8792: (tmp.budget_line_id
8793: ,tmp.budget_version_id

Line 8791: INSERT INTO PA_FP_SPREAD_CALC_TMP1 tmp

8787: x_return_status := 'S';
8788: print_msg('Entered Populate_blkExcpRecs API');
8789: DELETE FROM PA_FP_SPREAD_CALC_TMP1;
8790: FORALL i IN p_err_error_code_tab.FIRST .. p_err_error_code_tab.LAST
8791: INSERT INTO PA_FP_SPREAD_CALC_TMP1 tmp
8792: (tmp.budget_line_id
8793: ,tmp.budget_version_id
8794: ,tmp.resource_assignment_id
8795: ,tmp.txn_currency_code

Line 8858: DELETE FROM PA_FP_SPREAD_CALC_TMP2;

8854: ) IS
8855: BEGIN
8856: x_return_status := 'S';
8857: print_msg('Entered Populate_ExistingBlRecs API');
8858: DELETE FROM PA_FP_SPREAD_CALC_TMP2;
8859: INSERT INTO PA_FP_SPREAD_CALC_TMP2 tmp
8860: (tmp.budget_line_id
8861: ,tmp.budget_version_id
8862: ,tmp.resource_assignment_id

Line 8859: INSERT INTO PA_FP_SPREAD_CALC_TMP2 tmp

8855: BEGIN
8856: x_return_status := 'S';
8857: print_msg('Entered Populate_ExistingBlRecs API');
8858: DELETE FROM PA_FP_SPREAD_CALC_TMP2;
8859: INSERT INTO PA_FP_SPREAD_CALC_TMP2 tmp
8860: (tmp.budget_line_id
8861: ,tmp.budget_version_id
8862: ,tmp.resource_assignment_id
8863: ,tmp.txn_currency_code

Line 8906: From pa_fp_spread_calc_tmp1 tmp1

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
8908: and tmp1.resource_assignment_id = bl.resource_assignment_id
8909: and tmp1.txn_currency_code = bl.txn_currency_code
8910: and tmp1.start_date = bl.start_date

Line 8916: UPDATE pa_fp_spread_calc_tmp1 tmp1

8912: print_msg('Number of lines inserted['||sql%Rowcount||']');
8913:
8914: /* Now store the existing budget line id on tmp1 table to read the values
8915: * from both tables */
8916: UPDATE pa_fp_spread_calc_tmp1 tmp1
8917: SET tmp1.system_reference_num1 = (select tmp2.budget_line_id
8918: from pa_fp_spread_calc_tmp2 tmp2
8919: where tmp2.budget_version_id = p_budget_version_id
8920: and tmp2.resource_assignment_id = tmp1.resource_assignment_id

Line 8918: from pa_fp_spread_calc_tmp2 tmp2

8914: /* Now store the existing budget line id on tmp1 table to read the values
8915: * from both tables */
8916: UPDATE pa_fp_spread_calc_tmp1 tmp1
8917: SET tmp1.system_reference_num1 = (select tmp2.budget_line_id
8918: from pa_fp_spread_calc_tmp2 tmp2
8919: where tmp2.budget_version_id = p_budget_version_id
8920: and tmp2.resource_assignment_id = tmp1.resource_assignment_id
8921: and tmp2.txn_currency_code = tmp1.txn_currency_code
8922: and tmp2.start_date = tmp1.start_date

Line 9566: UPDATE pa_fp_spread_calc_tmp2 tmp2

9562: l_bl_upd_markup_percentage := newRec.bill_markup_percentage;
9563: End If;
9564:
9565:
9566: UPDATE pa_fp_spread_calc_tmp2 tmp2
9567: SET tmp2.txn_currency_code = newRec.txn_currency_code
9568: ,tmp2.quantity = nvl(tmp2.quantity,0) + newRec.quantity
9569: ,tmp2.system_reference_var1 = newRec.cost_rejection_code
9570: ,tmp2.system_reference_var2 = newRec.revenue_rejection_code

Line 10015: FROM pa_fp_spread_calc_tmp1 cache, pa_budget_lines bl

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

Line 10601: /*Bug:4272944: Added new procedure to insert zero qty budget lines from pa_fp_spread_calc_tmp1 to

10597: RAISE;
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: */

Line 10603: *donot populate or use pa_fp_spread_calc_tmp1 table for any other purpose.

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
10607: ( p_budget_verson_id IN Number

Line 10623: FROM pa_fp_spread_calc_tmp1 tmp

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'
10627: l_resource_assignment_id_tab pa_plsql_datatypes.Num15TabTyp;

Line 10769: SELECT /+ INDEX(TMP PA_FP_SPREAD_CALC_TMP1_N1) / l_budget_line_id_tab(i)

10765: ,bl.LAST_UPDATED_BY
10766: ,bl.LAST_UPDATE_LOGIN
10767: ,bl.LAST_UPDATE_DATE
10768: )
10769: SELECT /*+ INDEX(TMP PA_FP_SPREAD_CALC_TMP1_N1) */ l_budget_line_id_tab(i)
10770: ,tmp.RESOURCE_ASSIGNMENT_ID --resource_assignment_id
10771: ,tmp.BUDGET_VERSION_ID --budget_version_id
10772: ,tmp.TXN_CURRENCY_CODE --txn_currency_code
10773: ,tmp.QUANTITY --total_qty

Line 10854: FROM PA_FP_SPREAD_CALC_TMP1 tmp

10850: ,tmp.BL_CREATION_DATE
10851: ,tmp.BL_CREATED_BY --last updated by
10852: ,tmp.BL_CREATED_BY -- lastupdate login
10853: ,trunc(sysdate) -- last update dated
10854: FROM PA_FP_SPREAD_CALC_TMP1 tmp
10855: WHERE tmp.resource_assignment_id = l_resource_assignment_id_tab(i)
10856: AND tmp.txn_currency_code = l_txn_cur_code_tab(i)
10857: AND tmp.start_date = l_start_date_tab(i);
10858: