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_GEN_FCST_AMT_PUB dependencies on PA_FP_CALC_AMT_TMP1

Line 123: DELETE FROM PA_FP_CALC_AMT_TMP1;

119: ret status: '||x_return_status,
120: p_module_name => l_module_name);
121: END IF;
122:
123: DELETE FROM PA_FP_CALC_AMT_TMP1;
124: DELETE FROM PA_FP_CALC_AMT_TMP2;
125: IF p_fp_cols_rec.x_gen_incl_open_comm_flag = 'Y' THEN
126: IF p_pa_debug_mode = 'Y' THEN
127: PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG(

Line 1017: FROM PA_FP_CALC_AMT_TMP1 tmp1;

1013: tmp1.resource_assignment_id,
1014: tmp1.target_res_asg_id,
1015: tmp1.resource_list_member_id,
1016: tmp1.etc_method_code
1017: FROM PA_FP_CALC_AMT_TMP1 tmp1;
1018:
1019: l_task_id_tab2 PA_PLSQL_DATATYPES.NumTabTyp;
1020: l_gen_etc_source_code_tab2 pa_plsql_datatypes.Char30TabTyp;
1021: l_src_ra_id_tab2 PA_PLSQL_DATATYPES.NumTabTyp;

Line 1161: -- Bug 4346172: As part of the fix, added join on pa_fp_calc_amt_tmp1

1157:
1158: l_gen_etc_src_code_tab_tmp SYSTEM.pa_varchar2_30_tbl_type:=SYSTEM.pa_varchar2_30_tbl_type();
1159: l_gen_etc_src_code_tab SYSTEM.pa_varchar2_30_tbl_type:=SYSTEM.pa_varchar2_30_tbl_type();
1160:
1161: -- Bug 4346172: As part of the fix, added join on pa_fp_calc_amt_tmp1
1162: -- to get the correct source ETC method code.
1163: -- As a result of further testing, discovered that the original fix
1164: -- introduced a duplicate resource error when Commitments are included.
1165: -- PROBLEM:

Line 1167: -- PA_FP_CALC_AMT_TMP1 table. However, when Commitments are included,

1163: -- As a result of further testing, discovered that the original fix
1164: -- introduced a duplicate resource error when Commitments are included.
1165: -- PROBLEM:
1166: -- Normally, there should be 1 record per target_res_asg_id in the
1167: -- PA_FP_CALC_AMT_TMP1 table. However, when Commitments are included,
1168: -- extra PA_FP_CALC_AMT_TMP1 records are added for commitment records.
1169: -- As a result, the query returned 2 records for target resources with
1170: -- commitments and ETC.
1171: -- SOLUTION:

Line 1168: -- extra PA_FP_CALC_AMT_TMP1 records are added for commitment records.

1164: -- introduced a duplicate resource error when Commitments are included.
1165: -- PROBLEM:
1166: -- Normally, there should be 1 record per target_res_asg_id in the
1167: -- PA_FP_CALC_AMT_TMP1 table. However, when Commitments are included,
1168: -- extra PA_FP_CALC_AMT_TMP1 records are added for commitment records.
1169: -- As a result, the query returned 2 records for target resources with
1170: -- commitments and ETC.
1171: -- SOLUTION:
1172: -- Ignore records in the PA_FP_CALC_AMT_TMP1 table with transaction

Line 1172: -- Ignore records in the PA_FP_CALC_AMT_TMP1 table with transaction

1168: -- extra PA_FP_CALC_AMT_TMP1 records are added for commitment records.
1169: -- As a result, the query returned 2 records for target resources with
1170: -- commitments and ETC.
1171: -- SOLUTION:
1172: -- Ignore records in the PA_FP_CALC_AMT_TMP1 table with transaction
1173: -- source code of 'OPEN_COMMITMENTS'. This will ensure that the query
1174: -- retrieves at most 1 record per target_res_asg_id. Note that we do
1175: -- not need to worry about the scenario when there is a commitment
1176: -- record in the temp table but no ETC record, since the purpose of

Line 1211: INDEX(tmp_ra,PA_FP_CALC_AMT_TMP1_N1) */

1207:
1208: CURSOR etc_amts_cur_wp_fp_opt_same
1209: (c_gen_etc_source_code VARCHAR2 DEFAULT NULL) IS
1210: SELECT /*+ INDEX(tmp,PA_FP_CALC_AMT_TMP2_N1)
1211: INDEX(tmp_ra,PA_FP_CALC_AMT_TMP1_N1) */
1212: tmp.RESOURCE_ASSIGNMENT_ID,
1213: tmp.TARGET_RES_ASG_ID,
1214: tmp.ETC_CURRENCY_CODE,
1215: ra.rate_based_flag,

Line 1227: FROM PA_FP_CALC_AMT_TMP1 tmp_ra,

1223: SUM(tmp.ETC_TXN_REVENUE),
1224: NVL(ta.billable_flag,'Y'), /* Added for ER 4376722 */
1225: nvl(c_gen_etc_source_code, /* Added for Bug 4369741 */
1226: nvl(tmp_ra.transaction_source_code, 'NONE'))
1227: FROM PA_FP_CALC_AMT_TMP1 tmp_ra,
1228: PA_FP_CALC_AMT_TMP2 tmp,
1229: PA_RESOURCE_ASSIGNMENTS ra,
1230: pa_tasks ta /* Added for ER 4376722 */
1231: WHERE tmp.target_res_asg_id = ra.resource_assignment_id

Line 1276: INDEX(tmp_ra,PA_FP_CALC_AMT_TMP1_N1) */

1272:
1273: CURSOR etc_amts_cur_wp_fp_opt_diff
1274: (c_gen_etc_source_code VARCHAR2 DEFAULT NULL) IS
1275: SELECT /*+ INDEX(tmp,PA_FP_CALC_AMT_TMP2_N1)
1276: INDEX(tmp_ra,PA_FP_CALC_AMT_TMP1_N1) */
1277: sum(1*null),
1278: tmp.TARGET_RES_ASG_ID,
1279: tmp.ETC_CURRENCY_CODE,
1280: ra.rate_based_flag,

Line 1292: FROM PA_FP_CALC_AMT_TMP1 tmp_ra, /* Added for Bug 4369741 */

1288: SUM(tmp.ETC_TXN_REVENUE),
1289: NVL(ta.billable_flag,'Y'), /* Added for ER 4376722 */
1290: nvl(c_gen_etc_source_code, /* Added for Bug 4369741 */
1291: nvl(tmp_ra.transaction_source_code, 'NONE'))
1292: FROM PA_FP_CALC_AMT_TMP1 tmp_ra, /* Added for Bug 4369741 */
1293: PA_FP_CALC_AMT_TMP2 tmp,
1294: PA_RESOURCE_ASSIGNMENTS ra,
1295: pa_tasks ta /* Added for ER 4376722 */
1296: WHERE tmp.target_res_asg_id = ra.resource_assignment_id

Line 1329: INDEX(tmp_ra,PA_FP_CALC_AMT_TMP1_N1) */

1325: * resources with non-Workplan source. */
1326:
1327: CURSOR etc_amts_cur_wp_opt_same IS
1328: SELECT /*+ INDEX(tmp,PA_FP_CALC_AMT_TMP2_N1)
1329: INDEX(tmp_ra,PA_FP_CALC_AMT_TMP1_N1) */
1330: tmp.RESOURCE_ASSIGNMENT_ID,
1331: tmp.TARGET_RES_ASG_ID,
1332: tmp.ETC_CURRENCY_CODE,
1333: ra.rate_based_flag,

Line 1344: FROM PA_FP_CALC_AMT_TMP1 tmp_ra,

1340: SUM(tmp.ETC_TXN_BURDENED_COST),
1341: SUM(tmp.ETC_TXN_REVENUE),
1342: NVL(ta.billable_flag,'Y'), /* Added for ER 4376722 */
1343: tmp_ra.transaction_source_code /* Added for Bug 4369741 */
1344: FROM PA_FP_CALC_AMT_TMP1 tmp_ra,
1345: PA_FP_CALC_AMT_TMP2 tmp,
1346: PA_RESOURCE_ASSIGNMENTS ra,
1347: pa_tasks ta /* Added for ER 4376722 */
1348: WHERE tmp.target_res_asg_id = ra.resource_assignment_id

Line 1369: INDEX(tmp_ra,PA_FP_CALC_AMT_TMP1_N1) */

1365: NVL(ta.billable_flag,'Y'), /* Added for ER 4376722 */
1366: tmp_ra.transaction_source_code /* Added for Bug 4369741 */
1367: UNION ALL
1368: SELECT /*+ INDEX(tmp,PA_FP_CALC_AMT_TMP2_N1)
1369: INDEX(tmp_ra,PA_FP_CALC_AMT_TMP1_N1) */
1370: sum(1*null),
1371: tmp.TARGET_RES_ASG_ID,
1372: tmp.ETC_CURRENCY_CODE,
1373: ra.rate_based_flag,

Line 1384: FROM PA_FP_CALC_AMT_TMP1 tmp_ra, /* Added for Bug 4369741 */

1380: SUM(tmp.ETC_TXN_BURDENED_COST),
1381: SUM(tmp.ETC_TXN_REVENUE),
1382: NVL(ta.billable_flag,'Y'), /* Added for ER 4376722 */
1383: tmp_ra.transaction_source_code /* Added for Bug 4369741 */
1384: FROM PA_FP_CALC_AMT_TMP1 tmp_ra, /* Added for Bug 4369741 */
1385: PA_FP_CALC_AMT_TMP2 tmp,
1386: PA_RESOURCE_ASSIGNMENTS ra,
1387: pa_tasks ta /* Added for ER 4376722 */
1388: WHERE tmp.target_res_asg_id = ra.resource_assignment_id

Line 1421: INDEX(tmp_ra,PA_FP_CALC_AMT_TMP1_N1) */

1417: * resources with non Financial Plan source. */
1418:
1419: CURSOR etc_amts_cur_fp_opt_same IS
1420: SELECT /*+ INDEX(tmp,PA_FP_CALC_AMT_TMP2_N1)
1421: INDEX(tmp_ra,PA_FP_CALC_AMT_TMP1_N1) */
1422: tmp.RESOURCE_ASSIGNMENT_ID,
1423: tmp.TARGET_RES_ASG_ID,
1424: tmp.ETC_CURRENCY_CODE,
1425: ra.rate_based_flag,

Line 1436: FROM PA_FP_CALC_AMT_TMP1 tmp_ra,

1432: SUM(tmp.ETC_TXN_BURDENED_COST),
1433: SUM(tmp.ETC_TXN_REVENUE),
1434: NVL(ta.billable_flag,'Y'), /* Added for ER 4376722 */
1435: tmp_ra.transaction_source_code /* Added for Bug 4369741 */
1436: FROM PA_FP_CALC_AMT_TMP1 tmp_ra,
1437: PA_FP_CALC_AMT_TMP2 tmp,
1438: PA_RESOURCE_ASSIGNMENTS ra,
1439: pa_tasks ta /* Added for ER 4376722 */
1440: WHERE tmp.target_res_asg_id = ra.resource_assignment_id

Line 1461: INDEX(tmp_ra,PA_FP_CALC_AMT_TMP1_N1) */

1457: NVL(ta.billable_flag,'Y'), /* Added for ER 4376722 */
1458: tmp_ra.transaction_source_code /* Added for Bug 4369741 */
1459: UNION ALL
1460: SELECT /*+ INDEX(tmp,PA_FP_CALC_AMT_TMP2_N1)
1461: INDEX(tmp_ra,PA_FP_CALC_AMT_TMP1_N1) */
1462: sum(1*null),
1463: tmp.TARGET_RES_ASG_ID,
1464: tmp.ETC_CURRENCY_CODE,
1465: ra.rate_based_flag,

Line 1476: FROM PA_FP_CALC_AMT_TMP1 tmp_ra, /* Added for Bug 4369741 */

1472: SUM(tmp.ETC_TXN_BURDENED_COST),
1473: SUM(tmp.ETC_TXN_REVENUE),
1474: NVL(ta.billable_flag,'Y'), /* Added for ER 4376722 */
1475: tmp_ra.transaction_source_code /* Added for Bug 4369741 */
1476: FROM PA_FP_CALC_AMT_TMP1 tmp_ra, /* Added for Bug 4369741 */
1477: PA_FP_CALC_AMT_TMP2 tmp,
1478: PA_RESOURCE_ASSIGNMENTS ra,
1479: pa_tasks ta /* Added for ER 4376722 */
1480: WHERE tmp.target_res_asg_id = ra.resource_assignment_id

Line 2247: FROM pa_fp_calc_amt_tmp1 tmp1

2243: IN
2244: (SELECT pt.task_id
2245: FROM pa_tasks pt
2246: WHERE pt.top_task_id IN (SELECT tmp1.task_id
2247: FROM pa_fp_calc_amt_tmp1 tmp1
2248: WHERE tmp1.budget_version_id =
2249: p_etc_fp_plan_version_id)
2250: AND pt.task_id NOT IN (SELECT tmp1.task_id
2251: FROM pa_fp_calc_amt_tmp1 tmp1

Line 2251: FROM pa_fp_calc_amt_tmp1 tmp1

2247: FROM pa_fp_calc_amt_tmp1 tmp1
2248: WHERE tmp1.budget_version_id =
2249: p_etc_fp_plan_version_id)
2250: AND pt.task_id NOT IN (SELECT tmp1.task_id
2251: FROM pa_fp_calc_amt_tmp1 tmp1
2252: WHERE tmp1.budget_version_id =
2253: p_etc_fp_plan_version_id)
2254: AND pt.project_id=p_project_id
2255: AND pt.task_id<>pt.top_task_id

Line 2382: /* select count(*) into l_test from Pa_fp_CALC_AMT_TMP1;

2378: /**From latest approved version, by calling gen_map_bv_to_target_rl
2379: *we get PA_FP_CALC_AMT_TMP3 popuated, from it, we can get the
2380: *baselined cost**/
2381:
2382: /* select count(*) into l_test from Pa_fp_CALC_AMT_TMP1;
2383: hr_utility.trace('fp calc amt tmp1 tab count '||l_test);
2384: select count(*) into l_test from Pa_fp_CALC_AMT_TMP2;
2385: hr_utility.trace('fp calc amt tmp2 tab count '||l_test); */
2386:

Line 2560: --select count(*) into l_count_tmp from PA_FP_CALC_AMT_TMP1;

2556: ELSE
2557: l_gen_etc_source_code_override := NULL;
2558: END IF;
2559:
2560: --select count(*) into l_count_tmp from PA_FP_CALC_AMT_TMP1;
2561: --dbms_output.put_line('l_count_tmp:'||l_count_tmp);
2562: --hr_utility.trace('bef cursor for etc amt calc');
2563: OPEN traverse_tasks_rlm_cur(l_gen_etc_source_code_override);
2564: FETCH traverse_tasks_rlm_cur

Line 2933: /* select count(*) into l_count_tmp from PA_FP_CALC_AMT_TMP1;

2929: *ETC amounts populated in tmp2 table, now, we need to map the amounts to the target fin
2930: *plan's resource list, summ up, call calculate to spread into pa_budget_lines if time_phase
2931: *is not null, otherwise, we need to populate into pa_budget_lines by ourselves**/
2932:
2933: /* select count(*) into l_count_tmp from PA_FP_CALC_AMT_TMP1;
2934: hr_utility.trace('tmp1 count :'|| l_count_tmp);
2935: select count(*) into l_count_tmp from PA_FP_CALC_AMT_TMP2;
2936: hr_utility.trace('tmp2 count :'|| l_count_tmp); */
2937: --delete from calc_amt_tmp11;

Line 2938: --insert into calc_amt_tmp11 select * from pa_fp_calc_amt_tmp1;

2934: hr_utility.trace('tmp1 count :'|| l_count_tmp);
2935: select count(*) into l_count_tmp from PA_FP_CALC_AMT_TMP2;
2936: hr_utility.trace('tmp2 count :'|| l_count_tmp); */
2937: --delete from calc_amt_tmp11;
2938: --insert into calc_amt_tmp11 select * from pa_fp_calc_amt_tmp1;
2939:
2940: --select count(*) into l_count_tmp from PA_FP_CALC_AMT_TMP1;
2941: --hr_utility.trace('***PA_FP_CALC_AMT_TMP1.count'||l_count_tmp);
2942: --select count(*) into l_count_tmp from PA_FP_CALC_AMT_TMP2;

Line 2940: --select count(*) into l_count_tmp from PA_FP_CALC_AMT_TMP1;

2936: hr_utility.trace('tmp2 count :'|| l_count_tmp); */
2937: --delete from calc_amt_tmp11;
2938: --insert into calc_amt_tmp11 select * from pa_fp_calc_amt_tmp1;
2939:
2940: --select count(*) into l_count_tmp from PA_FP_CALC_AMT_TMP1;
2941: --hr_utility.trace('***PA_FP_CALC_AMT_TMP1.count'||l_count_tmp);
2942: --select count(*) into l_count_tmp from PA_FP_CALC_AMT_TMP2;
2943: --hr_utility.trace('**PA_FP_CALC_AMT_TMP2.count'||l_count_tmp);
2944: --hr_utility.trace('**P_FP_COLS_REC.X_TIME_PHASED_CODE:'||P_FP_COLS_REC.X_TIME_PHASED_CODE);

Line 2941: --hr_utility.trace('***PA_FP_CALC_AMT_TMP1.count'||l_count_tmp);

2937: --delete from calc_amt_tmp11;
2938: --insert into calc_amt_tmp11 select * from pa_fp_calc_amt_tmp1;
2939:
2940: --select count(*) into l_count_tmp from PA_FP_CALC_AMT_TMP1;
2941: --hr_utility.trace('***PA_FP_CALC_AMT_TMP1.count'||l_count_tmp);
2942: --select count(*) into l_count_tmp from PA_FP_CALC_AMT_TMP2;
2943: --hr_utility.trace('**PA_FP_CALC_AMT_TMP2.count'||l_count_tmp);
2944: --hr_utility.trace('**P_FP_COLS_REC.X_TIME_PHASED_CODE:'||P_FP_COLS_REC.X_TIME_PHASED_CODE);
2945:

Line 2959: SELECT /+ INDEX(tmp1,PA_FP_CALC_AMT_TMP1_N1)/

2955: IF p_fp_cols_rec.x_time_phased_code IN ('P','G') THEN
2956: l_etc_start_date :=
2957: PA_FP_GEN_AMOUNT_UTILS.GET_ETC_START_DATE(p_budget_version_id);
2958:
2959: SELECT /*+ INDEX(tmp1,PA_FP_CALC_AMT_TMP1_N1)*/
2960: DISTINCT tmp1.target_res_asg_id
2961: BULK COLLECT
2962: INTO l_res_asg_uom_update_tab
2963: FROM PA_FP_CALC_AMT_TMP1 tmp1,

Line 2963: FROM PA_FP_CALC_AMT_TMP1 tmp1,

2959: SELECT /*+ INDEX(tmp1,PA_FP_CALC_AMT_TMP1_N1)*/
2960: DISTINCT tmp1.target_res_asg_id
2961: BULK COLLECT
2962: INTO l_res_asg_uom_update_tab
2963: FROM PA_FP_CALC_AMT_TMP1 tmp1,
2964: pa_resource_assignments ra
2965: WHERE ra.budget_version_id = p_budget_version_id
2966: AND ra.resource_assignment_id = tmp1.target_res_asg_id
2967: AND ( ra.transaction_source_code IS NOT NULL

Line 2981: SELECT /+ INDEX(tmp1,PA_FP_CALC_AMT_TMP1_N1)/

2977: WHERE tmp2.target_res_asg_id = tmp1.target_res_asg_id
2978: AND tmp2.transaction_source_code = 'ETC'
2979: AND rownum = 1 );
2980: ELSIF p_fp_cols_rec.x_time_phased_code = 'N' THEN
2981: SELECT /*+ INDEX(tmp1,PA_FP_CALC_AMT_TMP1_N1)*/
2982: DISTINCT tmp1.target_res_asg_id
2983: BULK COLLECT
2984: INTO l_res_asg_uom_update_tab
2985: FROM PA_FP_CALC_AMT_TMP1 tmp1,

Line 2985: FROM PA_FP_CALC_AMT_TMP1 tmp1,

2981: SELECT /*+ INDEX(tmp1,PA_FP_CALC_AMT_TMP1_N1)*/
2982: DISTINCT tmp1.target_res_asg_id
2983: BULK COLLECT
2984: INTO l_res_asg_uom_update_tab
2985: FROM PA_FP_CALC_AMT_TMP1 tmp1,
2986: pa_resource_assignments ra
2987: WHERE ra.budget_version_id = p_budget_version_id
2988: AND ra.resource_assignment_id = tmp1.target_res_asg_id
2989: AND ( ra.transaction_source_code IS NOT NULL

Line 4733: FROM PA_FP_CALC_AMT_TMP1

4729: TARGET_RLM_ID,
4730: to_number(NULL),
4731: min(planning_start_date),
4732: max(planning_end_date)
4733: FROM PA_FP_CALC_AMT_TMP1
4734: GROUP BY mapped_fin_task_id,TARGET_RLM_ID,to_number(NULL));
4735: ELSIF P_FP_COLS_REC.X_FIN_PLAN_LEVEL_CODE = 'P' THEN
4736: -- hr_utility.trace('inside proj lvl in maintain res asg :');
4737: INSERT INTO PA_FP_PLANNING_RES_TMP1 (

Line 4748: FROM PA_FP_CALC_AMT_TMP1

4744: TARGET_RLM_ID,
4745: to_number(NULL),
4746: min(planning_start_date),
4747: max(planning_end_date)
4748: FROM PA_FP_CALC_AMT_TMP1
4749: group by 0, TARGET_RLM_ID,
4750: to_number(NULL) );
4751: ELSIF P_FP_COLS_REC.X_FIN_PLAN_LEVEL_CODE = 'T' THEN
4752: -- hr_utility.trace('inside top task in maintain res asg :');

Line 4764: FROM PA_FP_CALC_AMT_TMP1 group by

4760: TARGET_RLM_ID,
4761: to_number(NULL),
4762: min(planning_start_date),
4763: max(planning_end_date)
4764: FROM PA_FP_CALC_AMT_TMP1 group by
4765: mapped_fin_task_id,TARGET_RLM_ID,
4766: to_number(NULL) );
4767: END IF;
4768: -- select count(*) into l_count from PA_FP_PLANNING_RES_TMP1;