[Home] [Help]
515: CLOSE asgn_end_date_cur;
516:
517: IF l_asgn_end_date IS NULL AND l_assignment_id IS NULL THEN
518:
519: -- fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) ||' fnd ::: If --- > assignment_id details is inserted in the table '||p_assignmnet_id);
520: -- hr_utility.trace(' hr:::If --- > assignment_id details is inserted in the table '||p_assignmnet_id);
521:
522:
523: IF p_payroll_mode = 'Y' OR p_payroll_mode IS NULL THEN
537:
538:
539: insert into psp_asg_end_dates (PAYROLL_ACTION_ID ,ASSIGNMENT_ID ,PAYROLL_ID ,BG_GRP_ID ,ASG_END_DATE ) --jnerella 26/07/2012
540: values(p_payroll_action_id,p_assignmnet_id,p_payroll_id,p_business_group_id,l_asgn_end_date);
541: -- fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) ||' fnd:::If table --- > insert '||sql%rowcount);
542: -- hr_utility.trace(' hr:::If table --- > insert '||sql%rowcount);
543:
544: commit;
545:
546: return l_asgn_end_date;
547:
548: ELSE
549:
550: -- fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) ||' fnd:::Else --> assignment_id already exists in the table'||p_assignmnet_id);
551: -- hr_utility.trace(' hr::Else --> assignment_id already exists in the table'||p_assignmnet_id);
552: return l_asgn_end_date;
553:
554: END IF;
644: CURSOR time_pct_def_cur
645: IS
646: SELECT peed.period_end_date,
647: peed.enc_end_date_id,
648: NVL(peed.prev_enc_end_date, fnd_date.canonical_to_date('4712/12/31')) prev_enc_end_date
649: FROM psp_enc_end_dates peed
650: WHERE
651: --- removed sysdate check for Bug fix 2597666
652: peed.business_group_id = p_business_group_id
1025: l_input_value_id NUMBER(15);
1026: l_formula_id NUMBER(15);
1027:
1028: -- Introduced for bug fix 3551561
1029: l_change_end_date DATE DEFAULT fnd_date.canonical_to_date('4712/12/31');
1030: l_prev_change_end_date DATE;
1031: l_prev_change_date DATE;
1032: -- End of bug fix 3551561
1033: l_annualization_factor NUMBER;
1071: ORDER BY start_date,end_date; -- Added for bug 12724506
1072:
1073: BEGIN
1074: l_proc_step:= 10;
1075: hr_utility.trace(fnd_date.date_to_canonical(SYSDATE) || ' Entering ' || l_proc_name);
1076: hr_utility.trace('p_assignment_id: ' || fnd_number.number_to_canonical(p_assignment_id) ||
1077: ' p_element_type_id: ' || fnd_number.number_to_canonical(p_element_type_id) ||
1078: ' p_payroll_id: ' || fnd_number.number_to_canonical(p_payroll_id) ||
1079: ' p_business_group_id: ' || fnd_number.number_to_canonical(p_business_group_id) ||
1147: l_enc_amount := 0;
1148: l_proc_step:= 60 + (I / 100000);
1149:
1150: hr_utility.trace('I: ' || fnd_number.number_to_canonical(I) ||
1151: ' l_period_start_date: ' || fnd_date.date_to_canonical(l_period_start_date) ||
1152: ' l_period_end_date: ' || fnd_date.date_to_canonical(l_period_end_date) ||
1153: ' l_asg_start_date: ' || fnd_date.date_to_canonical(l_asg_start_date) ||
1154: ' l_asg_end_date: ' || fnd_date.date_to_canonical(l_asg_end_date) ||
1155: ' l_bus_working_days: ' || fnd_number.number_to_canonical(l_bus_working_days));
1148: l_proc_step:= 60 + (I / 100000);
1149:
1150: hr_utility.trace('I: ' || fnd_number.number_to_canonical(I) ||
1151: ' l_period_start_date: ' || fnd_date.date_to_canonical(l_period_start_date) ||
1152: ' l_period_end_date: ' || fnd_date.date_to_canonical(l_period_end_date) ||
1153: ' l_asg_start_date: ' || fnd_date.date_to_canonical(l_asg_start_date) ||
1154: ' l_asg_end_date: ' || fnd_date.date_to_canonical(l_asg_end_date) ||
1155: ' l_bus_working_days: ' || fnd_number.number_to_canonical(l_bus_working_days));
1156:
1149:
1150: hr_utility.trace('I: ' || fnd_number.number_to_canonical(I) ||
1151: ' l_period_start_date: ' || fnd_date.date_to_canonical(l_period_start_date) ||
1152: ' l_period_end_date: ' || fnd_date.date_to_canonical(l_period_end_date) ||
1153: ' l_asg_start_date: ' || fnd_date.date_to_canonical(l_asg_start_date) ||
1154: ' l_asg_end_date: ' || fnd_date.date_to_canonical(l_asg_end_date) ||
1155: ' l_bus_working_days: ' || fnd_number.number_to_canonical(l_bus_working_days));
1156:
1157: IF (l_bus_working_days = 0) THEN
1150: hr_utility.trace('I: ' || fnd_number.number_to_canonical(I) ||
1151: ' l_period_start_date: ' || fnd_date.date_to_canonical(l_period_start_date) ||
1152: ' l_period_end_date: ' || fnd_date.date_to_canonical(l_period_end_date) ||
1153: ' l_asg_start_date: ' || fnd_date.date_to_canonical(l_asg_start_date) ||
1154: ' l_asg_end_date: ' || fnd_date.date_to_canonical(l_asg_end_date) ||
1155: ' l_bus_working_days: ' || fnd_number.number_to_canonical(l_bus_working_days));
1156:
1157: IF (l_bus_working_days = 0) THEN
1158: fnd_message.set_name('PSP', 'PSP_ENC_ZERO_WORK_DAYS_PERIOD');
1186: l_inputs.DELETE;
1187: l_outputs.DELETE;
1188:
1189: ff_exec.init_formula(l_formula_id, l_asg_start_date, l_inputs,l_outputs);
1190: hr_utility.trace('Initiated Formula l_asg_start_date: ' || fnd_date.date_to_canonical(l_asg_start_date) || ' l_asg_end_date: ' || fnd_date.date_to_canonical(l_asg_end_date));
1191:
1192: hr_utility.trace('l_inputs.COUNT: ' || l_inputs.COUNT);
1193: FOR recno IN 1..l_inputs.COUNT
1194: LOOP
1207: ELSIF l_inputs(recno).name='TIME_PERIOD_ID' THEN
1208: l_inputs(recno).value := fnd_number.number_to_canonical(r_enc_period.r_time_period_id(I));
1209: hr_utility.trace('Assigned time_period_id');
1210: ELSIF l_inputs(recno).name='ASG_START_DATE' THEN
1211: l_inputs(recno).value := fnd_date.date_to_canonical(l_asg_start_date);
1212: hr_utility.trace('Assigned asg_start_date');
1213: ELSIF l_inputs(recno).name='ASG_END_DATE' THEN
1214: l_inputs(recno).value := fnd_date.date_to_canonical(l_asg_end_date);
1215: hr_utility.trace('Assigned asg_end_date');
1210: ELSIF l_inputs(recno).name='ASG_START_DATE' THEN
1211: l_inputs(recno).value := fnd_date.date_to_canonical(l_asg_start_date);
1212: hr_utility.trace('Assigned asg_start_date');
1213: ELSIF l_inputs(recno).name='ASG_END_DATE' THEN
1214: l_inputs(recno).value := fnd_date.date_to_canonical(l_asg_end_date);
1215: hr_utility.trace('Assigned asg_end_date');
1216: ELSIF l_inputs(recno).name='BUS_DAYS_IN_CHUNK' THEN
1217: l_inputs(recno).value := fnd_number.number_to_canonical(psp_general.business_days(l_asg_start_date, l_asg_end_date,p_assignment_id));
1218: hr_utility.trace('Assigned business days in chunk');
1216: ELSIF l_inputs(recno).name='BUS_DAYS_IN_CHUNK' THEN
1217: l_inputs(recno).value := fnd_number.number_to_canonical(psp_general.business_days(l_asg_start_date, l_asg_end_date,p_assignment_id));
1218: hr_utility.trace('Assigned business days in chunk');
1219: ELSIF l_inputs(recno).name='PERIOD_START_DATE' THEN
1220: l_inputs(recno).value := fnd_date.date_to_canonical(l_period_start_date);
1221: hr_utility.trace('Assigned period_start_date');
1222: ELSIF l_inputs(recno).name='PERIOD_END_DATE' THEN
1223: l_inputs(recno).value := fnd_date.date_to_canonical(l_period_end_date);
1224: hr_utility.trace('Assigned period_end_date');
1219: ELSIF l_inputs(recno).name='PERIOD_START_DATE' THEN
1220: l_inputs(recno).value := fnd_date.date_to_canonical(l_period_start_date);
1221: hr_utility.trace('Assigned period_start_date');
1222: ELSIF l_inputs(recno).name='PERIOD_END_DATE' THEN
1223: l_inputs(recno).value := fnd_date.date_to_canonical(l_period_end_date);
1224: hr_utility.trace('Assigned period_end_date');
1225: ELSIF l_inputs(recno).name='BUS_DAYS_IN_PERIOD' THEN
1226: l_inputs(recno).value := fnd_number.number_to_canonical(psp_general.business_days(l_period_start_date, l_period_end_date,p_assignment_id));
1227: hr_utility.trace('Assigned business days in period');
1254: l_pay_basis := r_salary_proposal.r_pay_basis(J);
1255: l_proc_step:= 70 + (J / 100000);
1256:
1257: hr_utility.trace('J: ' || fnd_number.number_to_canonical(J) ||
1258: ' l_change_date: ' || fnd_date.date_to_canonical(l_change_date) ||
1259: ' l_temp_salary: ' || fnd_number.number_to_canonical(l_temp_salary) ||
1260: ' l_annualization_factor: ' || fnd_number.number_to_canonical(l_annualization_factor) ||
1261: ' l_pay_basis: ' || l_pay_basis);
1262:
1268: l_start_date := GREATEST(l_change_date, l_asg_start_date);
1269: l_bus_days_in_sched := NVL(PSP_GENERAL.BUSINESS_DAYS(l_start_date, l_end_date,p_assignment_id), 0);
1270: l_enc_amount := l_enc_amount + ROUND(((l_temp_salary * l_bus_days_in_sched) / ( l_bus_working_days )), g_ext_precision);
1271:
1272: hr_utility.trace('l_start_date: ' || fnd_date.date_to_canonical(l_start_date) ||
1273: ' l_end_date: ' || fnd_date.date_to_canonical(l_end_date) ||
1274: ' l_bus_days_in_sched: ' || fnd_number.number_to_canonical(l_bus_days_in_sched) ||
1275: ' l_enc_amount: ' || fnd_number.number_to_canonical(l_enc_amount));
1276:
1269: l_bus_days_in_sched := NVL(PSP_GENERAL.BUSINESS_DAYS(l_start_date, l_end_date,p_assignment_id), 0);
1270: l_enc_amount := l_enc_amount + ROUND(((l_temp_salary * l_bus_days_in_sched) / ( l_bus_working_days )), g_ext_precision);
1271:
1272: hr_utility.trace('l_start_date: ' || fnd_date.date_to_canonical(l_start_date) ||
1273: ' l_end_date: ' || fnd_date.date_to_canonical(l_end_date) ||
1274: ' l_bus_days_in_sched: ' || fnd_number.number_to_canonical(l_bus_days_in_sched) ||
1275: ' l_enc_amount: ' || fnd_number.number_to_canonical(l_enc_amount));
1276:
1277: l_end_date := GREATEST(l_start_date, l_change_date) - 1;
1297: l_bus_days_in_sched := NVL(PSP_GENERAL.BUSINESS_DAYS(l_start_date, l_end_date,p_assignment_id), 0);
1298: l_enc_amount := l_enc_amount + ROUND(((l_pay_amount * l_bus_days_in_sched)/( l_bus_working_days )),g_ext_precision);
1299: END IF;
1300:
1301: hr_utility.trace('l_element_start_date: ' || fnd_date.date_to_canonical(l_element_start_date) ||
1302: ' l_element_end_date: ' || fnd_date.date_to_canonical(l_element_end_date) ||
1303: ' l_pay_amount: ' || fnd_number.number_to_canonical(l_pay_amount) ||
1304: ' l_start_date: ' || fnd_date.date_to_canonical(l_start_date) ||
1305: ' l_end_date: ' || fnd_date.date_to_canonical(l_end_date) ||
1298: l_enc_amount := l_enc_amount + ROUND(((l_pay_amount * l_bus_days_in_sched)/( l_bus_working_days )),g_ext_precision);
1299: END IF;
1300:
1301: hr_utility.trace('l_element_start_date: ' || fnd_date.date_to_canonical(l_element_start_date) ||
1302: ' l_element_end_date: ' || fnd_date.date_to_canonical(l_element_end_date) ||
1303: ' l_pay_amount: ' || fnd_number.number_to_canonical(l_pay_amount) ||
1304: ' l_start_date: ' || fnd_date.date_to_canonical(l_start_date) ||
1305: ' l_end_date: ' || fnd_date.date_to_canonical(l_end_date) ||
1306: ' l_bus_days_in_sched: ' || fnd_number.number_to_canonical(l_bus_days_in_sched) ||
1300:
1301: hr_utility.trace('l_element_start_date: ' || fnd_date.date_to_canonical(l_element_start_date) ||
1302: ' l_element_end_date: ' || fnd_date.date_to_canonical(l_element_end_date) ||
1303: ' l_pay_amount: ' || fnd_number.number_to_canonical(l_pay_amount) ||
1304: ' l_start_date: ' || fnd_date.date_to_canonical(l_start_date) ||
1305: ' l_end_date: ' || fnd_date.date_to_canonical(l_end_date) ||
1306: ' l_bus_days_in_sched: ' || fnd_number.number_to_canonical(l_bus_days_in_sched) ||
1307: ' l_enc_amount: ' || fnd_number.number_to_canonical(l_enc_amount));
1308:
1301: hr_utility.trace('l_element_start_date: ' || fnd_date.date_to_canonical(l_element_start_date) ||
1302: ' l_element_end_date: ' || fnd_date.date_to_canonical(l_element_end_date) ||
1303: ' l_pay_amount: ' || fnd_number.number_to_canonical(l_pay_amount) ||
1304: ' l_start_date: ' || fnd_date.date_to_canonical(l_start_date) ||
1305: ' l_end_date: ' || fnd_date.date_to_canonical(l_end_date) ||
1306: ' l_bus_days_in_sched: ' || fnd_number.number_to_canonical(l_bus_days_in_sched) ||
1307: ' l_enc_amount: ' || fnd_number.number_to_canonical(l_enc_amount));
1308:
1309: EXIT WHEN l_element_start_date > l_asg_end_date;
1356: l_end_date := l_period_end_date;
1357: l_period_counter := 0;
1358:
1359: hr_utility.trace('I: ' || fnd_number.number_to_canonical(I) ||
1360: ' l_period_start_date: ' || fnd_date.date_to_canonical(l_period_start_date) ||
1361: ' l_period_end_date: ' || fnd_date.date_to_canonical(l_period_end_date) ||
1362: ' l_bus_working_days: ' || fnd_number.number_to_canonical(l_bus_working_days));
1363:
1364: IF (l_bus_working_days > 0) THEN
1357: l_period_counter := 0;
1358:
1359: hr_utility.trace('I: ' || fnd_number.number_to_canonical(I) ||
1360: ' l_period_start_date: ' || fnd_date.date_to_canonical(l_period_start_date) ||
1361: ' l_period_end_date: ' || fnd_date.date_to_canonical(l_period_end_date) ||
1362: ' l_bus_working_days: ' || fnd_number.number_to_canonical(l_bus_working_days));
1363:
1364: IF (l_bus_working_days > 0) THEN
1365: FOR J IN 1..r_salary_proposal.r_change_date.COUNT
1382: l_pay_basis := r_salary_proposal.r_pay_basis(J);
1383: l_proc_step:= 130 + (J / 100000);
1384:
1385: hr_utility.trace('J: ' || fnd_number.number_to_canonical(J) ||
1386: ' l_change_date: ' || fnd_date.date_to_canonical(l_change_date) ||
1387: ' l_change_end_date: ' || fnd_date.date_to_canonical(l_change_end_date) ||
1388: ' l_temp_salary: ' || fnd_number.number_to_canonical(l_temp_salary) ||
1389: ' l_annualization_factor: ' || fnd_number.number_to_canonical(l_annualization_factor) ||
1390: ' l_pay_basis: ' || l_pay_basis);
1383: l_proc_step:= 130 + (J / 100000);
1384:
1385: hr_utility.trace('J: ' || fnd_number.number_to_canonical(J) ||
1386: ' l_change_date: ' || fnd_date.date_to_canonical(l_change_date) ||
1387: ' l_change_end_date: ' || fnd_date.date_to_canonical(l_change_end_date) ||
1388: ' l_temp_salary: ' || fnd_number.number_to_canonical(l_temp_salary) ||
1389: ' l_annualization_factor: ' || fnd_number.number_to_canonical(l_annualization_factor) ||
1390: ' l_pay_basis: ' || l_pay_basis);
1391:
1416: END LOOP; -- Introduced for bug fix 3673723
1417:
1418: l_enc_amount := l_enc_amount + ROUND(((l_temp_salary * l_bus_days_in_sched)/( l_bus_working_days )), g_ext_precision);
1419: -- End of changes for bug fix 3551561
1420: hr_utility.trace('l_start_date: ' || fnd_date.date_to_canonical(l_start_date) ||
1421: ' l_end_date: ' || fnd_date.date_to_canonical(l_end_date) ||
1422: ' l_bus_days_in_sched: ' || fnd_number.number_to_canonical(l_bus_days_in_sched) ||
1423: ' l_enc_amount: ' || fnd_number.number_to_canonical(l_enc_amount));
1424: -- l_end_date := GREATEST(l_start_date, l_change_date) - 1; Commented for bug fix 3551561
1417:
1418: l_enc_amount := l_enc_amount + ROUND(((l_temp_salary * l_bus_days_in_sched)/( l_bus_working_days )), g_ext_precision);
1419: -- End of changes for bug fix 3551561
1420: hr_utility.trace('l_start_date: ' || fnd_date.date_to_canonical(l_start_date) ||
1421: ' l_end_date: ' || fnd_date.date_to_canonical(l_end_date) ||
1422: ' l_bus_days_in_sched: ' || fnd_number.number_to_canonical(l_bus_days_in_sched) ||
1423: ' l_enc_amount: ' || fnd_number.number_to_canonical(l_enc_amount));
1424: -- l_end_date := GREATEST(l_start_date, l_change_date) - 1; Commented for bug fix 3551561
1425: l_prev_change_end_date := l_change_end_date;
1527: LPAD(r_enc_period.r_encumbrance_amount(recno), 18, ' ') || ' ' ||
1528: LPAD(r_enc_period.r_period_amount(recno), 18, ' ') || ' ' ||
1529: RPAD(r_enc_period.r_reason_code(recno), 50, ' '));
1530: END LOOP;
1531: hr_utility.trace(fnd_date.date_to_canonical(SYSDATE) || ' Leaving ' || l_proc_name);
1532:
1533: p_return_status := fnd_api.g_ret_sts_success;
1534: EXCEPTION
1535: WHEN DIVIDE_BY_ZERO THEN
1534: EXCEPTION
1535: WHEN DIVIDE_BY_ZERO THEN
1536: fnd_msg_pub.add_exc_msg('PSP_ENC_CREATE_LINES', ' DETERMINE_ENC_AMOUNT ');
1537: fnd_file.put_line(fnd_file.log, fnd_number.number_to_canonical(l_proc_step) || ': ' || l_proc_name);
1538: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Leaving ' || l_proc_name);
1539: p_return_status := fnd_api.g_ret_sts_unexp_error;
1540: WHEN OTHERS THEN
1541: fnd_file.put_line(fnd_file.log,DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
1542: IF (g_error_message IS NULL) THEN
1543: g_error_message := l_proc_name || ': ' || SQLERRM;
1544: END IF;
1545: fnd_msg_pub.add_exc_msg('PSP', 'DETERMINE_ENC_AMOUNT');
1546: fnd_file.put_line(fnd_file.log, fnd_number.number_to_canonical(l_proc_step) || ': ' || l_proc_name);
1547: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Leaving ' || l_proc_name);
1548: p_return_status := fnd_api.g_ret_sts_unexp_error;
1549: END determine_enc_amount;
1550: -- End of changes for bug fix 3488734
1551:
1767: IF (p_process_flag = 'SA') THEN
1768: l_sa_reason_code := l_reason_code;
1769: END IF;
1770:
1771: hr_utility.trace(fnd_date.date_to_canonical(SYSDATE) || ' Entering process_all_hier');
1772: hr_utility.trace(' p_chunk_pointer: ' || p_chunk_pointer ||
1773: ' p_asg_start_date: ' || TO_CHAR(p_asg_start_date, 'DD-MON-RRRR') ||
1774: ' p_asg_end_date: ' || TO_CHAR(p_asg_end_date, 'DD-MON-RRRR') ||
1775: ' p_process_flag: ' || p_process_flag);
2226: END IF;
2227: END LOOP;
2228: END IF;
2229: t_poeta_gl_hier_array.DELETE;
2230: hr_utility.trace(fnd_date.date_to_canonical(SYSDATE) || ' Leaving process_all_hier');
2231: END process_all_hier;
2232:
2233: PROCEDURE log_gl_hours_message IS
2234: BEGIN
2242: BEGIN
2243: l_proc_step := 10;
2244: l_proc_name := 'CREATE_LINES';
2245:
2246: hr_utility.trace(fnd_date.date_to_canonical(SYSDATE) || ' Entering ' || l_proc_name);
2247: hr_utility.trace('p_assignment_id: ' || fnd_number.number_to_canonical(p_assignment_id) ||
2248: ' p_payroll_id: ' || fnd_number.number_to_canonical(p_payroll_id) ||
2249: ' p_element_type_id: ' || fnd_number.number_to_canonical(p_element_type_id) ||
2250: ' p_last_paid_date: ' || fnd_date.date_to_canonical(p_last_paid_date));
2246: hr_utility.trace(fnd_date.date_to_canonical(SYSDATE) || ' Entering ' || l_proc_name);
2247: hr_utility.trace('p_assignment_id: ' || fnd_number.number_to_canonical(p_assignment_id) ||
2248: ' p_payroll_id: ' || fnd_number.number_to_canonical(p_payroll_id) ||
2249: ' p_element_type_id: ' || fnd_number.number_to_canonical(p_element_type_id) ||
2250: ' p_last_paid_date: ' || fnd_date.date_to_canonical(p_last_paid_date));
2251:
2252: p_return_status := fnd_api.g_ret_sts_success;
2253:
2254: OPEN c_person_id;
2323: g_orig_pointer := 1;
2324: l_orig_pointer := 1;
2325: l_chunk_pointer := 1;
2326: l_prev_enc_lines_counter := t_enc_lines_array.r_time_period_id.COUNT + 1;
2327: l_min_start_date := fnd_date.canonical_to_date('1800/01/01');
2328: l_max_end_date := LEAST(NVL(g_actual_term_date, g_enc_org_end_date), g_enc_org_end_date);
2329: l_ignore_start := -1;
2330: l_ignore_end := -1;
2331:
2379: ' PF: ' || l_process_flag || ' EA: ' || l_earnings_amount || ' ED: ' || TO_CHAR(l_effective_date, 'DD-MON-RRRR') ||
2380: ' BD: ' || l_bus_days_in_chunk);
2381:
2382: hr_utility.trace('l_chunk_pointer: ' || fnd_number.number_to_canonical(l_chunk_pointer) ||
2383: ' l_asg_start_date: ' || fnd_date.date_to_canonical(l_asg_start_date) ||
2384: ' l_asg_end_date: ' || fnd_date.date_to_canonical(l_asg_end_date) ||
2385: ' l_period_start_date: ' || fnd_date.date_to_canonical(l_period_start_date) ||
2386: ' l_period_end_date: ' || fnd_date.date_to_canonical(l_period_end_date) ||
2387: ' l_effective_date: ' || fnd_date.date_to_canonical(l_effective_date) ||
2380: ' BD: ' || l_bus_days_in_chunk);
2381:
2382: hr_utility.trace('l_chunk_pointer: ' || fnd_number.number_to_canonical(l_chunk_pointer) ||
2383: ' l_asg_start_date: ' || fnd_date.date_to_canonical(l_asg_start_date) ||
2384: ' l_asg_end_date: ' || fnd_date.date_to_canonical(l_asg_end_date) ||
2385: ' l_period_start_date: ' || fnd_date.date_to_canonical(l_period_start_date) ||
2386: ' l_period_end_date: ' || fnd_date.date_to_canonical(l_period_end_date) ||
2387: ' l_effective_date: ' || fnd_date.date_to_canonical(l_effective_date) ||
2388: ' l_time_period_id: ' || fnd_number.number_to_canonical(l_time_period_id) ||
2381:
2382: hr_utility.trace('l_chunk_pointer: ' || fnd_number.number_to_canonical(l_chunk_pointer) ||
2383: ' l_asg_start_date: ' || fnd_date.date_to_canonical(l_asg_start_date) ||
2384: ' l_asg_end_date: ' || fnd_date.date_to_canonical(l_asg_end_date) ||
2385: ' l_period_start_date: ' || fnd_date.date_to_canonical(l_period_start_date) ||
2386: ' l_period_end_date: ' || fnd_date.date_to_canonical(l_period_end_date) ||
2387: ' l_effective_date: ' || fnd_date.date_to_canonical(l_effective_date) ||
2388: ' l_time_period_id: ' || fnd_number.number_to_canonical(l_time_period_id) ||
2389: ' l_prev_time_period_id: ' || fnd_number.number_to_canonical(l_prev_time_period_id) ||
2382: hr_utility.trace('l_chunk_pointer: ' || fnd_number.number_to_canonical(l_chunk_pointer) ||
2383: ' l_asg_start_date: ' || fnd_date.date_to_canonical(l_asg_start_date) ||
2384: ' l_asg_end_date: ' || fnd_date.date_to_canonical(l_asg_end_date) ||
2385: ' l_period_start_date: ' || fnd_date.date_to_canonical(l_period_start_date) ||
2386: ' l_period_end_date: ' || fnd_date.date_to_canonical(l_period_end_date) ||
2387: ' l_effective_date: ' || fnd_date.date_to_canonical(l_effective_date) ||
2388: ' l_time_period_id: ' || fnd_number.number_to_canonical(l_time_period_id) ||
2389: ' l_prev_time_period_id: ' || fnd_number.number_to_canonical(l_prev_time_period_id) ||
2390: ' l_process_flag: ' || l_process_flag ||
2383: ' l_asg_start_date: ' || fnd_date.date_to_canonical(l_asg_start_date) ||
2384: ' l_asg_end_date: ' || fnd_date.date_to_canonical(l_asg_end_date) ||
2385: ' l_period_start_date: ' || fnd_date.date_to_canonical(l_period_start_date) ||
2386: ' l_period_end_date: ' || fnd_date.date_to_canonical(l_period_end_date) ||
2387: ' l_effective_date: ' || fnd_date.date_to_canonical(l_effective_date) ||
2388: ' l_time_period_id: ' || fnd_number.number_to_canonical(l_time_period_id) ||
2389: ' l_prev_time_period_id: ' || fnd_number.number_to_canonical(l_prev_time_period_id) ||
2390: ' l_process_flag: ' || l_process_flag ||
2391: ' l_earnings_amount: ' || fnd_number.number_to_canonical(l_earnings_amount) ||
3360: r_enc_period.r_period_amount.DELETE;
3361: r_enc_period.r_reason_code.DELETE;
3362:
3363: l_proc_step := 210;
3364: hr_utility.trace(fnd_date.date_to_canonical(SYSDATE) || ' Leaving ' || l_proc_name);
3365: EXCEPTION
3366: WHEN suspense_autopop_failed THEN
3367: fnd_msg_pub.add_exc_msg('PSP_ENC_CREATE_LINES', 'CREATE_LINES ');
3368: p_return_status := fnd_api.g_ret_sts_unexp_error;
3367: fnd_msg_pub.add_exc_msg('PSP_ENC_CREATE_LINES', 'CREATE_LINES ');
3368: p_return_status := fnd_api.g_ret_sts_unexp_error;
3369: fnd_file.put_line(fnd_file.log, 'l_reason_code: ' || l_reason_code);
3370: fnd_file.put_line(fnd_file.log, fnd_number.number_to_canonical(l_proc_step) || ': ' || l_proc_name);
3371: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Leaving ' || l_proc_name);
3372: WHEN OTHERS THEN
3373: fnd_file.put_line(fnd_file.log,DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
3374: IF (g_error_message IS NULL) THEN
3375: g_error_message := l_proc_name || ': ' || SQLERRM;
3376: END IF;
3377: fnd_msg_pub.add_exc_msg('PSP_ENC_CREATE_LINES', 'CREATE_LINES');
3378: p_return_status := fnd_api.g_ret_sts_unexp_error;
3379: fnd_file.put_line(fnd_file.log, fnd_number.number_to_canonical(l_proc_step) || ': ' || l_proc_name);
3380: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Leaving ' || l_proc_name);
3381: END create_lines;
3382:
3383: ---------------------- I N S E R T S T A T E M E N T ------------------------------------
3384: PROCEDURE insert_into_enc_lines(
3712: l_end_date date := p_ls_end_date;
3713: i NUMBER := 1;
3714: l_bus_days_in_period NUMBER;
3715: l_bus_days_in_schedules NUMBER;
3716: l_susp_date DATE DEFAULT fnd_date.canonical_to_date('1800/01/01');
3717:
3718:
3719: DIVIDE_BY_ZERO EXCEPTION;
3720:
4284: BEGIN
4285: psp_matrix_driver_pkg.set_runid;
4286: l_run_id := psp_matrix_driver_pkg.get_run_id;
4287:
4288: hr_utility.trace(fnd_date.date_to_canonical(SYSDATE) || ' Entering ' || l_proc_name);
4289: hr_utility.trace('p_assignment_id: ' || fnd_number.number_to_canonical(p_assignment_id) ||
4290: ' p_element_type_id: ' || fnd_number.number_to_canonical(p_element_type_id) ||
4291: ' p_business_group_id: ' || fnd_number.number_to_canonical(p_business_group_id) ||
4292: ' p_set_of_books_id: ' || fnd_number.number_to_canonical(p_set_of_books_id) ||
4352: l_sub_slice_counter := 1;
4353: l_proc_step := 20 + (I / 100000);
4354:
4355: hr_utility.trace('I: ' || fnd_number.number_to_canonical(I) ||
4356: ' l_period_start_date: ' || fnd_date.date_to_canonical(l_period_start_date) ||
4357: ' l_period_end_date: ' || fnd_date.date_to_canonical(l_period_end_date) ||
4358: ' l_asg_start_date: ' || fnd_date.date_to_canonical(l_asg_start_date) ||
4359: ' l_asg_end_date: ' || fnd_date.date_to_canonical(l_asg_end_date) ||
4360: ' r_enc_period.r_time_period_id(I): ' || fnd_number.number_to_canonical(r_enc_period.r_time_period_id(I)) ||
4353: l_proc_step := 20 + (I / 100000);
4354:
4355: hr_utility.trace('I: ' || fnd_number.number_to_canonical(I) ||
4356: ' l_period_start_date: ' || fnd_date.date_to_canonical(l_period_start_date) ||
4357: ' l_period_end_date: ' || fnd_date.date_to_canonical(l_period_end_date) ||
4358: ' l_asg_start_date: ' || fnd_date.date_to_canonical(l_asg_start_date) ||
4359: ' l_asg_end_date: ' || fnd_date.date_to_canonical(l_asg_end_date) ||
4360: ' r_enc_period.r_time_period_id(I): ' || fnd_number.number_to_canonical(r_enc_period.r_time_period_id(I)) ||
4361: ' l_rec_no: ' || fnd_number.number_to_canonical(l_rec_no));
4354:
4355: hr_utility.trace('I: ' || fnd_number.number_to_canonical(I) ||
4356: ' l_period_start_date: ' || fnd_date.date_to_canonical(l_period_start_date) ||
4357: ' l_period_end_date: ' || fnd_date.date_to_canonical(l_period_end_date) ||
4358: ' l_asg_start_date: ' || fnd_date.date_to_canonical(l_asg_start_date) ||
4359: ' l_asg_end_date: ' || fnd_date.date_to_canonical(l_asg_end_date) ||
4360: ' r_enc_period.r_time_period_id(I): ' || fnd_number.number_to_canonical(r_enc_period.r_time_period_id(I)) ||
4361: ' l_rec_no: ' || fnd_number.number_to_canonical(l_rec_no));
4362:
4355: hr_utility.trace('I: ' || fnd_number.number_to_canonical(I) ||
4356: ' l_period_start_date: ' || fnd_date.date_to_canonical(l_period_start_date) ||
4357: ' l_period_end_date: ' || fnd_date.date_to_canonical(l_period_end_date) ||
4358: ' l_asg_start_date: ' || fnd_date.date_to_canonical(l_asg_start_date) ||
4359: ' l_asg_end_date: ' || fnd_date.date_to_canonical(l_asg_end_date) ||
4360: ' r_enc_period.r_time_period_id(I): ' || fnd_number.number_to_canonical(r_enc_period.r_time_period_id(I)) ||
4361: ' l_rec_no: ' || fnd_number.number_to_canonical(l_rec_no));
4362:
4363: IF (r_sch_rec.end_date.COUNT > 0) THEN
4371: -- FETCH global_element_cur INTO l_start_date, l_end_date, l_schedule_percent;
4372:
4373: hr_utility.trace('l_sub_slice_counter: ' || fnd_number.number_to_canonical(l_sub_slice_counter) ||
4374: ' l_rec_no: ' || fnd_number.number_to_canonical(l_rec_no) ||
4375: ' l_start_date: ' || fnd_date.date_to_canonical(l_start_date) ||
4376: ' l_end_date: ' || fnd_date.date_to_canonical(l_end_date) ||
4377: ' l_schedule_percent: ' || fnd_number.number_to_canonical(l_schedule_percent));
4378: -- hr_utility.trace('global_element_cur%ROWCOUNT: ' || fnd_number.number_to_canonical(global_element_cur%ROWCOUNT));
4379:
4372:
4373: hr_utility.trace('l_sub_slice_counter: ' || fnd_number.number_to_canonical(l_sub_slice_counter) ||
4374: ' l_rec_no: ' || fnd_number.number_to_canonical(l_rec_no) ||
4375: ' l_start_date: ' || fnd_date.date_to_canonical(l_start_date) ||
4376: ' l_end_date: ' || fnd_date.date_to_canonical(l_end_date) ||
4377: ' l_schedule_percent: ' || fnd_number.number_to_canonical(l_schedule_percent));
4378: -- hr_utility.trace('global_element_cur%ROWCOUNT: ' || fnd_number.number_to_canonical(global_element_cur%ROWCOUNT));
4379:
4380: -- IF (global_element_cur%NOTFOUND) THEN
4751: l_sub_slice_counter := 1;
4752: l_proc_step := 50 + (I / 100000);
4753:
4754: hr_utility.trace('I: ' || fnd_number.number_to_canonical(I) ||
4755: ' l_period_start_date: ' || fnd_date.date_to_canonical(l_period_start_date) ||
4756: ' l_period_end_date: ' || fnd_date.date_to_canonical(l_period_end_date) ||
4757: ' l_asg_start_date: ' || fnd_date.date_to_canonical(l_asg_start_date) ||
4758: ' l_asg_end_date: ' || fnd_date.date_to_canonical(l_asg_end_date) ||
4759: ' r_enc_period_tmp1.r_time_period_id(I): ' || fnd_number.number_to_canonical(r_enc_period_tmp1.r_time_period_id(I)) ||
4752: l_proc_step := 50 + (I / 100000);
4753:
4754: hr_utility.trace('I: ' || fnd_number.number_to_canonical(I) ||
4755: ' l_period_start_date: ' || fnd_date.date_to_canonical(l_period_start_date) ||
4756: ' l_period_end_date: ' || fnd_date.date_to_canonical(l_period_end_date) ||
4757: ' l_asg_start_date: ' || fnd_date.date_to_canonical(l_asg_start_date) ||
4758: ' l_asg_end_date: ' || fnd_date.date_to_canonical(l_asg_end_date) ||
4759: ' r_enc_period_tmp1.r_time_period_id(I): ' || fnd_number.number_to_canonical(r_enc_period_tmp1.r_time_period_id(I)) ||
4760: ' l_rec_no: ' || fnd_number.number_to_canonical(l_rec_no));
4753:
4754: hr_utility.trace('I: ' || fnd_number.number_to_canonical(I) ||
4755: ' l_period_start_date: ' || fnd_date.date_to_canonical(l_period_start_date) ||
4756: ' l_period_end_date: ' || fnd_date.date_to_canonical(l_period_end_date) ||
4757: ' l_asg_start_date: ' || fnd_date.date_to_canonical(l_asg_start_date) ||
4758: ' l_asg_end_date: ' || fnd_date.date_to_canonical(l_asg_end_date) ||
4759: ' r_enc_period_tmp1.r_time_period_id(I): ' || fnd_number.number_to_canonical(r_enc_period_tmp1.r_time_period_id(I)) ||
4760: ' l_rec_no: ' || fnd_number.number_to_canonical(l_rec_no));
4761:
4754: hr_utility.trace('I: ' || fnd_number.number_to_canonical(I) ||
4755: ' l_period_start_date: ' || fnd_date.date_to_canonical(l_period_start_date) ||
4756: ' l_period_end_date: ' || fnd_date.date_to_canonical(l_period_end_date) ||
4757: ' l_asg_start_date: ' || fnd_date.date_to_canonical(l_asg_start_date) ||
4758: ' l_asg_end_date: ' || fnd_date.date_to_canonical(l_asg_end_date) ||
4759: ' r_enc_period_tmp1.r_time_period_id(I): ' || fnd_number.number_to_canonical(r_enc_period_tmp1.r_time_period_id(I)) ||
4760: ' l_rec_no: ' || fnd_number.number_to_canonical(l_rec_no));
4761:
4762: IF (l_process_flag = 'Y') AND (r_sch_rec.end_date.COUNT > 0) THEN
4768: l_proc_step := 60 + (et_recno / 100000);
4769:
4770: hr_utility.trace(' l_sub_slice_counter: ' || fnd_number.number_to_canonical(l_sub_slice_counter) ||
4771: ' l_rec_no: ' || fnd_number.number_to_canonical(l_rec_no) ||
4772: ' l_start_date: ' || fnd_date.date_to_canonical(l_start_date) ||
4773: ' l_end_date: ' || fnd_date.date_to_canonical(l_end_date) ||
4774: ' l_schedule_percent: ' || fnd_number.number_to_canonical(l_schedule_percent));
4775:
4776: -- OPEN ls_matrix_cur;
4769:
4770: hr_utility.trace(' l_sub_slice_counter: ' || fnd_number.number_to_canonical(l_sub_slice_counter) ||
4771: ' l_rec_no: ' || fnd_number.number_to_canonical(l_rec_no) ||
4772: ' l_start_date: ' || fnd_date.date_to_canonical(l_start_date) ||
4773: ' l_end_date: ' || fnd_date.date_to_canonical(l_end_date) ||
4774: ' l_schedule_percent: ' || fnd_number.number_to_canonical(l_schedule_percent));
4775:
4776: -- OPEN ls_matrix_cur;
4777: -- FETCH ls_matrix_cur INTO l_start_date, l_end_date, l_schedule_percent;
5152: l_sub_slice_counter := 1;
5153: l_proc_step := 80 + (I / 100000);
5154:
5155: hr_utility.trace('I: ' || fnd_number.number_to_canonical(I) ||
5156: ' l_period_start_date: ' || fnd_date.date_to_canonical(l_period_start_date) ||
5157: ' l_period_end_date: ' || fnd_date.date_to_canonical(l_period_end_date) ||
5158: ' l_asg_start_date: ' || fnd_date.date_to_canonical(l_asg_start_date) ||
5159: ' l_asg_end_date: ' || fnd_date.date_to_canonical(l_asg_end_date) ||
5160: ' r_enc_period.r_time_period_id(I): ' || fnd_number.number_to_canonical(r_enc_period.r_time_period_id(I)) ||
5153: l_proc_step := 80 + (I / 100000);
5154:
5155: hr_utility.trace('I: ' || fnd_number.number_to_canonical(I) ||
5156: ' l_period_start_date: ' || fnd_date.date_to_canonical(l_period_start_date) ||
5157: ' l_period_end_date: ' || fnd_date.date_to_canonical(l_period_end_date) ||
5158: ' l_asg_start_date: ' || fnd_date.date_to_canonical(l_asg_start_date) ||
5159: ' l_asg_end_date: ' || fnd_date.date_to_canonical(l_asg_end_date) ||
5160: ' r_enc_period.r_time_period_id(I): ' || fnd_number.number_to_canonical(r_enc_period.r_time_period_id(I)) ||
5161: ' l_rec_no: ' || fnd_number.number_to_canonical(l_rec_no));
5154:
5155: hr_utility.trace('I: ' || fnd_number.number_to_canonical(I) ||
5156: ' l_period_start_date: ' || fnd_date.date_to_canonical(l_period_start_date) ||
5157: ' l_period_end_date: ' || fnd_date.date_to_canonical(l_period_end_date) ||
5158: ' l_asg_start_date: ' || fnd_date.date_to_canonical(l_asg_start_date) ||
5159: ' l_asg_end_date: ' || fnd_date.date_to_canonical(l_asg_end_date) ||
5160: ' r_enc_period.r_time_period_id(I): ' || fnd_number.number_to_canonical(r_enc_period.r_time_period_id(I)) ||
5161: ' l_rec_no: ' || fnd_number.number_to_canonical(l_rec_no));
5162:
5155: hr_utility.trace('I: ' || fnd_number.number_to_canonical(I) ||
5156: ' l_period_start_date: ' || fnd_date.date_to_canonical(l_period_start_date) ||
5157: ' l_period_end_date: ' || fnd_date.date_to_canonical(l_period_end_date) ||
5158: ' l_asg_start_date: ' || fnd_date.date_to_canonical(l_asg_start_date) ||
5159: ' l_asg_end_date: ' || fnd_date.date_to_canonical(l_asg_end_date) ||
5160: ' r_enc_period.r_time_period_id(I): ' || fnd_number.number_to_canonical(r_enc_period.r_time_period_id(I)) ||
5161: ' l_rec_no: ' || fnd_number.number_to_canonical(l_rec_no));
5162:
5163: IF (l_process_flag = 'Y') AND (r_sch_rec.end_date.COUNT > 0) THEN
5169: l_proc_step := 90 + (eg_recno / 100000);
5170:
5171: hr_utility.trace(' l_sub_slice_counter: ' || fnd_number.number_to_canonical(l_sub_slice_counter) ||
5172: ' l_rec_no: ' || fnd_number.number_to_canonical(l_rec_no) ||
5173: ' l_start_date: ' || fnd_date.date_to_canonical(l_start_date) ||
5174: ' l_end_date: ' || fnd_date.date_to_canonical(l_end_date) ||
5175: ' l_schedule_percent: ' || fnd_number.number_to_canonical(l_schedule_percent));
5176:
5177: -- OPEN eg_matrix_cur;
5170:
5171: hr_utility.trace(' l_sub_slice_counter: ' || fnd_number.number_to_canonical(l_sub_slice_counter) ||
5172: ' l_rec_no: ' || fnd_number.number_to_canonical(l_rec_no) ||
5173: ' l_start_date: ' || fnd_date.date_to_canonical(l_start_date) ||
5174: ' l_end_date: ' || fnd_date.date_to_canonical(l_end_date) ||
5175: ' l_schedule_percent: ' || fnd_number.number_to_canonical(l_schedule_percent));
5176:
5177: -- OPEN eg_matrix_cur;
5178: -- FETCH eg_matrix_cur INTO l_start_date, l_end_date, l_schedule_percent;
5549: l_sub_slice_counter := 1;
5550: l_proc_step := 110 + (I / 100000);
5551:
5552: hr_utility.trace('I: ' || fnd_number.number_to_canonical(I) ||
5553: ' l_period_start_date: ' || fnd_date.date_to_canonical(l_period_start_date) ||
5554: ' l_period_end_date: ' || fnd_date.date_to_canonical(l_period_end_date) ||
5555: ' l_asg_start_date: ' || fnd_date.date_to_canonical(l_asg_start_date) ||
5556: ' l_asg_end_date: ' || fnd_date.date_to_canonical(l_asg_end_date) ||
5557: ' r_enc_period_tmp1.r_time_period_id(I): ' || fnd_number.number_to_canonical(r_enc_period_tmp1.r_time_period_id(I)) ||
5550: l_proc_step := 110 + (I / 100000);
5551:
5552: hr_utility.trace('I: ' || fnd_number.number_to_canonical(I) ||
5553: ' l_period_start_date: ' || fnd_date.date_to_canonical(l_period_start_date) ||
5554: ' l_period_end_date: ' || fnd_date.date_to_canonical(l_period_end_date) ||
5555: ' l_asg_start_date: ' || fnd_date.date_to_canonical(l_asg_start_date) ||
5556: ' l_asg_end_date: ' || fnd_date.date_to_canonical(l_asg_end_date) ||
5557: ' r_enc_period_tmp1.r_time_period_id(I): ' || fnd_number.number_to_canonical(r_enc_period_tmp1.r_time_period_id(I)) ||
5558: ' l_rec_no: ' || fnd_number.number_to_canonical(l_rec_no));
5551:
5552: hr_utility.trace('I: ' || fnd_number.number_to_canonical(I) ||
5553: ' l_period_start_date: ' || fnd_date.date_to_canonical(l_period_start_date) ||
5554: ' l_period_end_date: ' || fnd_date.date_to_canonical(l_period_end_date) ||
5555: ' l_asg_start_date: ' || fnd_date.date_to_canonical(l_asg_start_date) ||
5556: ' l_asg_end_date: ' || fnd_date.date_to_canonical(l_asg_end_date) ||
5557: ' r_enc_period_tmp1.r_time_period_id(I): ' || fnd_number.number_to_canonical(r_enc_period_tmp1.r_time_period_id(I)) ||
5558: ' l_rec_no: ' || fnd_number.number_to_canonical(l_rec_no));
5559:
5552: hr_utility.trace('I: ' || fnd_number.number_to_canonical(I) ||
5553: ' l_period_start_date: ' || fnd_date.date_to_canonical(l_period_start_date) ||
5554: ' l_period_end_date: ' || fnd_date.date_to_canonical(l_period_end_date) ||
5555: ' l_asg_start_date: ' || fnd_date.date_to_canonical(l_asg_start_date) ||
5556: ' l_asg_end_date: ' || fnd_date.date_to_canonical(l_asg_end_date) ||
5557: ' r_enc_period_tmp1.r_time_period_id(I): ' || fnd_number.number_to_canonical(r_enc_period_tmp1.r_time_period_id(I)) ||
5558: ' l_rec_no: ' || fnd_number.number_to_canonical(l_rec_no));
5559:
5560: IF (l_process_flag = 'Y') AND (r_sch_rec.end_date.COUNT > 0) THEN
5566: l_proc_step := 120 + (l_rec_no / 100000);
5567:
5568: hr_utility.trace(' l_sub_slice_counter: ' || fnd_number.number_to_canonical(l_sub_slice_counter) ||
5569: ' l_rec_no: ' || fnd_number.number_to_canonical(l_rec_no) ||
5570: ' l_start_date: ' || fnd_date.date_to_canonical(l_start_date) ||
5571: ' l_end_date: ' || fnd_date.date_to_canonical(l_end_date) ||
5572: ' l_schedule_percent: ' || fnd_number.number_to_canonical(l_schedule_percent));
5573: -- OPEN ls_matrix_cur;
5574: -- FETCH ls_matrix_cur INTO l_start_date, l_end_date, l_schedule_percent;
5567:
5568: hr_utility.trace(' l_sub_slice_counter: ' || fnd_number.number_to_canonical(l_sub_slice_counter) ||
5569: ' l_rec_no: ' || fnd_number.number_to_canonical(l_rec_no) ||
5570: ' l_start_date: ' || fnd_date.date_to_canonical(l_start_date) ||
5571: ' l_end_date: ' || fnd_date.date_to_canonical(l_end_date) ||
5572: ' l_schedule_percent: ' || fnd_number.number_to_canonical(l_schedule_percent));
5573: -- OPEN ls_matrix_cur;
5574: -- FETCH ls_matrix_cur INTO l_start_date, l_end_date, l_schedule_percent;
5575: -- IF (ls_matrix_cur%NOTFOUND) THEN
5822: l_sub_slice_counter := 1;
5823: l_proc_step := 150 + (I / 100000);
5824:
5825: hr_utility.trace('I: ' || fnd_number.number_to_canonical(I) ||
5826: ' l_period_start_date: ' || fnd_date.date_to_canonical(l_period_start_date) ||
5827: ' l_period_end_date: ' || fnd_date.date_to_canonical(l_period_end_date) ||
5828: ' l_asg_start_date: ' || fnd_date.date_to_canonical(l_asg_start_date) ||
5829: ' l_asg_end_date: ' || fnd_date.date_to_canonical(l_asg_end_date) ||
5830: ' r_enc_period.r_time_period_id(I): ' || fnd_number.number_to_canonical(r_enc_period.r_time_period_id(I)) ||
5823: l_proc_step := 150 + (I / 100000);
5824:
5825: hr_utility.trace('I: ' || fnd_number.number_to_canonical(I) ||
5826: ' l_period_start_date: ' || fnd_date.date_to_canonical(l_period_start_date) ||
5827: ' l_period_end_date: ' || fnd_date.date_to_canonical(l_period_end_date) ||
5828: ' l_asg_start_date: ' || fnd_date.date_to_canonical(l_asg_start_date) ||
5829: ' l_asg_end_date: ' || fnd_date.date_to_canonical(l_asg_end_date) ||
5830: ' r_enc_period.r_time_period_id(I): ' || fnd_number.number_to_canonical(r_enc_period.r_time_period_id(I)) ||
5831: ' l_rec_no: ' || fnd_number.number_to_canonical(l_rec_no));
5824:
5825: hr_utility.trace('I: ' || fnd_number.number_to_canonical(I) ||
5826: ' l_period_start_date: ' || fnd_date.date_to_canonical(l_period_start_date) ||
5827: ' l_period_end_date: ' || fnd_date.date_to_canonical(l_period_end_date) ||
5828: ' l_asg_start_date: ' || fnd_date.date_to_canonical(l_asg_start_date) ||
5829: ' l_asg_end_date: ' || fnd_date.date_to_canonical(l_asg_end_date) ||
5830: ' r_enc_period.r_time_period_id(I): ' || fnd_number.number_to_canonical(r_enc_period.r_time_period_id(I)) ||
5831: ' l_rec_no: ' || fnd_number.number_to_canonical(l_rec_no));
5832:
5825: hr_utility.trace('I: ' || fnd_number.number_to_canonical(I) ||
5826: ' l_period_start_date: ' || fnd_date.date_to_canonical(l_period_start_date) ||
5827: ' l_period_end_date: ' || fnd_date.date_to_canonical(l_period_end_date) ||
5828: ' l_asg_start_date: ' || fnd_date.date_to_canonical(l_asg_start_date) ||
5829: ' l_asg_end_date: ' || fnd_date.date_to_canonical(l_asg_end_date) ||
5830: ' r_enc_period.r_time_period_id(I): ' || fnd_number.number_to_canonical(r_enc_period.r_time_period_id(I)) ||
5831: ' l_rec_no: ' || fnd_number.number_to_canonical(l_rec_no));
5832:
5833: IF (l_process_flag = 'Y') AND (r_sch_rec.end_date.COUNT > 0) THEN
5841: -- FETCH odls_cur INTO l_start_date, l_end_date, l_schedule_percent;
5842:
5843: hr_utility.trace('l_sub_slice_counter: ' || fnd_number.number_to_canonical(l_sub_slice_counter) ||
5844: ' l_rec_no: ' || fnd_number.number_to_canonical(l_rec_no) ||
5845: ' l_start_date: ' || fnd_date.date_to_canonical(l_start_date) ||
5846: ' l_end_date: ' || fnd_date.date_to_canonical(l_end_date) ||
5847: ' l_schedule_percent: ' || fnd_number.number_to_canonical(l_schedule_percent));
5848: -- hr_utility.trace('odls_cur%ROWCOUNT: ' || fnd_number.number_to_canonical(odls_cur%ROWCOUNT));
5849:
5842:
5843: hr_utility.trace('l_sub_slice_counter: ' || fnd_number.number_to_canonical(l_sub_slice_counter) ||
5844: ' l_rec_no: ' || fnd_number.number_to_canonical(l_rec_no) ||
5845: ' l_start_date: ' || fnd_date.date_to_canonical(l_start_date) ||
5846: ' l_end_date: ' || fnd_date.date_to_canonical(l_end_date) ||
5847: ' l_schedule_percent: ' || fnd_number.number_to_canonical(l_schedule_percent));
5848: -- hr_utility.trace('odls_cur%ROWCOUNT: ' || fnd_number.number_to_canonical(odls_cur%ROWCOUNT));
5849:
5850: -- IF (odls_cur%NOTFOUND) THEN
6102: l_sub_slice_counter := 1;
6103: l_proc_step := 180 + (I / 100000);
6104:
6105: hr_utility.trace('I: ' || fnd_number.number_to_canonical(I) ||
6106: ' l_period_start_date: ' || fnd_date.date_to_canonical(l_period_start_date) ||
6107: ' l_period_end_date: ' || fnd_date.date_to_canonical(l_period_end_date) ||
6108: ' l_asg_start_date: ' || fnd_date.date_to_canonical(l_asg_start_date) ||
6109: ' l_asg_end_date: ' || fnd_date.date_to_canonical(l_asg_end_date) ||
6110: ' r_enc_period.r_time_period_id(I): ' || fnd_number.number_to_canonical(r_enc_period.r_time_period_id(I)) ||
6103: l_proc_step := 180 + (I / 100000);
6104:
6105: hr_utility.trace('I: ' || fnd_number.number_to_canonical(I) ||
6106: ' l_period_start_date: ' || fnd_date.date_to_canonical(l_period_start_date) ||
6107: ' l_period_end_date: ' || fnd_date.date_to_canonical(l_period_end_date) ||
6108: ' l_asg_start_date: ' || fnd_date.date_to_canonical(l_asg_start_date) ||
6109: ' l_asg_end_date: ' || fnd_date.date_to_canonical(l_asg_end_date) ||
6110: ' r_enc_period.r_time_period_id(I): ' || fnd_number.number_to_canonical(r_enc_period.r_time_period_id(I)) ||
6111: ' l_rec_no: ' || fnd_number.number_to_canonical(l_rec_no));
6104:
6105: hr_utility.trace('I: ' || fnd_number.number_to_canonical(I) ||
6106: ' l_period_start_date: ' || fnd_date.date_to_canonical(l_period_start_date) ||
6107: ' l_period_end_date: ' || fnd_date.date_to_canonical(l_period_end_date) ||
6108: ' l_asg_start_date: ' || fnd_date.date_to_canonical(l_asg_start_date) ||
6109: ' l_asg_end_date: ' || fnd_date.date_to_canonical(l_asg_end_date) ||
6110: ' r_enc_period.r_time_period_id(I): ' || fnd_number.number_to_canonical(r_enc_period.r_time_period_id(I)) ||
6111: ' l_rec_no: ' || fnd_number.number_to_canonical(l_rec_no));
6112:
6105: hr_utility.trace('I: ' || fnd_number.number_to_canonical(I) ||
6106: ' l_period_start_date: ' || fnd_date.date_to_canonical(l_period_start_date) ||
6107: ' l_period_end_date: ' || fnd_date.date_to_canonical(l_period_end_date) ||
6108: ' l_asg_start_date: ' || fnd_date.date_to_canonical(l_asg_start_date) ||
6109: ' l_asg_end_date: ' || fnd_date.date_to_canonical(l_asg_end_date) ||
6110: ' r_enc_period.r_time_period_id(I): ' || fnd_number.number_to_canonical(r_enc_period.r_time_period_id(I)) ||
6111: ' l_rec_no: ' || fnd_number.number_to_canonical(l_rec_no));
6112:
6113: IF (l_process_flag IN ('BAL', 'Y')) AND (r_da.end_date_active.COUNT > 0) THEN
6123: l_proc_step := 190 + (l_rec_no / 100000);
6124:
6125: hr_utility.trace(' l_sub_slice_counter: ' || fnd_number.number_to_canonical(l_sub_slice_counter) ||
6126: ' l_rec_no: ' || fnd_number.number_to_canonical(l_rec_no) ||
6127: ' l_start_date: ' || fnd_date.date_to_canonical(l_start_date) ||
6128: ' l_end_date: ' || fnd_date.date_to_canonical(l_end_date) ||
6129: ' l_schedule_percent: ' || fnd_number.number_to_canonical(l_schedule_percent));
6130:
6131: IF (l_start_date > l_asg_end_date) THEN
6124:
6125: hr_utility.trace(' l_sub_slice_counter: ' || fnd_number.number_to_canonical(l_sub_slice_counter) ||
6126: ' l_rec_no: ' || fnd_number.number_to_canonical(l_rec_no) ||
6127: ' l_start_date: ' || fnd_date.date_to_canonical(l_start_date) ||
6128: ' l_end_date: ' || fnd_date.date_to_canonical(l_end_date) ||
6129: ' l_schedule_percent: ' || fnd_number.number_to_canonical(l_schedule_percent));
6130:
6131: IF (l_start_date > l_asg_end_date) THEN
6132: r_enc_period_tmp1.r_period_ind(l_rec_no) := r_enc_period.r_period_ind(I);
6347: l_sub_slice_counter := 1;
6348: l_proc_step := 210 + (I / 100000);
6349:
6350: hr_utility.trace('I: ' || fnd_number.number_to_canonical(I) ||
6351: ' l_period_start_date: ' || fnd_date.date_to_canonical(l_period_start_date) ||
6352: ' l_period_end_date: ' || fnd_date.date_to_canonical(l_period_end_date) ||
6353: ' l_asg_start_date: ' || fnd_date.date_to_canonical(l_asg_start_date) ||
6354: ' l_asg_end_date: ' || fnd_date.date_to_canonical(l_asg_end_date) ||
6355: ' r_enc_period_tmp1.r_time_period_id(I): ' || fnd_number.number_to_canonical(r_enc_period_tmp1.r_time_period_id(I)) ||
6348: l_proc_step := 210 + (I / 100000);
6349:
6350: hr_utility.trace('I: ' || fnd_number.number_to_canonical(I) ||
6351: ' l_period_start_date: ' || fnd_date.date_to_canonical(l_period_start_date) ||
6352: ' l_period_end_date: ' || fnd_date.date_to_canonical(l_period_end_date) ||
6353: ' l_asg_start_date: ' || fnd_date.date_to_canonical(l_asg_start_date) ||
6354: ' l_asg_end_date: ' || fnd_date.date_to_canonical(l_asg_end_date) ||
6355: ' r_enc_period_tmp1.r_time_period_id(I): ' || fnd_number.number_to_canonical(r_enc_period_tmp1.r_time_period_id(I)) ||
6356: ' l_rec_no: ' || fnd_number.number_to_canonical(l_rec_no));
6349:
6350: hr_utility.trace('I: ' || fnd_number.number_to_canonical(I) ||
6351: ' l_period_start_date: ' || fnd_date.date_to_canonical(l_period_start_date) ||
6352: ' l_period_end_date: ' || fnd_date.date_to_canonical(l_period_end_date) ||
6353: ' l_asg_start_date: ' || fnd_date.date_to_canonical(l_asg_start_date) ||
6354: ' l_asg_end_date: ' || fnd_date.date_to_canonical(l_asg_end_date) ||
6355: ' r_enc_period_tmp1.r_time_period_id(I): ' || fnd_number.number_to_canonical(r_enc_period_tmp1.r_time_period_id(I)) ||
6356: ' l_rec_no: ' || fnd_number.number_to_canonical(l_rec_no));
6357:
6350: hr_utility.trace('I: ' || fnd_number.number_to_canonical(I) ||
6351: ' l_period_start_date: ' || fnd_date.date_to_canonical(l_period_start_date) ||
6352: ' l_period_end_date: ' || fnd_date.date_to_canonical(l_period_end_date) ||
6353: ' l_asg_start_date: ' || fnd_date.date_to_canonical(l_asg_start_date) ||
6354: ' l_asg_end_date: ' || fnd_date.date_to_canonical(l_asg_end_date) ||
6355: ' r_enc_period_tmp1.r_time_period_id(I): ' || fnd_number.number_to_canonical(r_enc_period_tmp1.r_time_period_id(I)) ||
6356: ' l_rec_no: ' || fnd_number.number_to_canonical(l_rec_no));
6357:
6358: IF (l_process_flag IN ('BAL', 'Y')) AND (l_sch_pointer <= r_sa.end_date_active.COUNT) THEN
6369: l_proc_step := 220 + (l_rec_no / 100000);
6370:
6371: hr_utility.trace('l_sub_slice_counter: ' || fnd_number.number_to_canonical(l_sub_slice_counter) ||
6372: ' l_rec_no: ' || fnd_number.number_to_canonical(l_rec_no) ||
6373: ' l_start_date: ' || fnd_date.date_to_canonical(l_start_date) ||
6374: ' l_end_date: ' || fnd_date.date_to_canonical(l_end_date) ||
6375: ' l_schedule_percent: ' || fnd_number.number_to_canonical(l_schedule_percent));
6376:
6377: IF (r_sa.start_date_active(sa_recno) > l_asg_end_date) THEN
6370:
6371: hr_utility.trace('l_sub_slice_counter: ' || fnd_number.number_to_canonical(l_sub_slice_counter) ||
6372: ' l_rec_no: ' || fnd_number.number_to_canonical(l_rec_no) ||
6373: ' l_start_date: ' || fnd_date.date_to_canonical(l_start_date) ||
6374: ' l_end_date: ' || fnd_date.date_to_canonical(l_end_date) ||
6375: ' l_schedule_percent: ' || fnd_number.number_to_canonical(l_schedule_percent));
6376:
6377: IF (r_sa.start_date_active(sa_recno) > l_asg_end_date) THEN
6378: r_enc_period.r_period_ind(l_rec_no) := r_enc_period_tmp1.r_period_ind(I);
6565: LPAD(r_enc_period.r_schedule_percent(recno), 16, ' ') || ' ' ||
6566: RPAD(r_enc_period.r_reason_code(recno), 50, ' '));
6567: r_enc_period.r_encumbrance_amount(recno) := 0;
6568: END LOOP;
6569: hr_utility.trace(fnd_date.date_to_canonical(SYSDATE) || ' Leaving ' || l_proc_name);
6570:
6571: p_return_status := fnd_api.g_ret_sts_success;
6572: EXCEPTION
6573: WHEN OTHERS THEN
6578: g_error_api_path := SUBSTR(' SUB_SLICE_ASG_CHUNK:'||g_error_api_path,1,230);
6579: fnd_msg_pub.add_exc_msg('PSP_ENC_CREATE_LINES', ' SUB_SLICE_ASG_CHUNK');
6580: p_return_status := fnd_api.g_ret_sts_unexp_error;
6581: fnd_file.put_line(fnd_file.log, fnd_number.number_to_canonical(l_proc_step) || ': ' || l_proc_name);
6582: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Leaving ' || l_proc_name);
6583: END sub_slice_asg_chunk;
6584: -- End of bug fix 3462452
6585:
6586: /*
6599: */
6600:
6601: PROCEDURE cel_init(p_payroll_action_id IN NUMBER) IS
6602: BEGIN
6603: fnd_file.put_line(fnd_file.log,fnd_date.date_to_canonical(SYSDATE) || ' FND :: Initializing the profile option ');
6604: hr_utility.trace(fnd_date.date_to_canonical(SYSDATE) || ' HR :: Initializing the profile option ');
6605: g_payroll_mode := NVL(FND_PROFILE.VALUE('PSP_USING_ORACLE_PAYROLL'),'Y'); -- 13566809
6606: -- spc_track_cel('Init', NULL, 'Start');
6607: NULL;
6600:
6601: PROCEDURE cel_init(p_payroll_action_id IN NUMBER) IS
6602: BEGIN
6603: fnd_file.put_line(fnd_file.log,fnd_date.date_to_canonical(SYSDATE) || ' FND :: Initializing the profile option ');
6604: hr_utility.trace(fnd_date.date_to_canonical(SYSDATE) || ' HR :: Initializing the profile option ');
6605: g_payroll_mode := NVL(FND_PROFILE.VALUE('PSP_USING_ORACLE_PAYROLL'),'Y'); -- 13566809
6606: -- spc_track_cel('Init', NULL, 'Start');
6607: NULL;
6608: -- spc_track_cel('Init', NULL, 'End');
6639: FROM psp_enc_payrolls pep
6640: WHERE pep.business_group_id = l_business_group_id
6641: AND pep.set_of_books_id = l_set_of_books_id;
6642: BEGIN
6643: hr_utility.trace(fnd_date.date_to_canonical(SYSDATE) || ' Entering CEL_RANGE_CODE pactid: ' || pactid);
6644:
6645: l_last_update_date := SYSDATE;
6646: l_last_updated_by := NVL(FND_GLOBAL.USER_ID, -1);
6647: l_last_updated_login := NVL(FND_GLOBAL.LOGIN_ID, -1);
6655:
6656: IF (l_payroll_id = -1) THEN
6657: l_payroll_id := NULL;
6658: END IF;
6659: hr_utility.trace(fnd_date.date_to_canonical(SYSDATE) || ' l_payroll_id: ' || l_payroll_id || '
6660: process_mode: ' || l_process_mode);
6661:
6662: l_process_code := 'CEL';
6663: IF (l_process_mode = 'TERMINATE') THEN
6680:
6681: IF l_return_status <> fnd_api.g_ret_sts_success THEN
6682: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
6683: END IF;
6684: hr_utility.trace(fnd_date.date_to_canonical(SYSDATE) || ' Completed enc_pre_process for l_payroll_id: ' || l_payroll_id || ' process_mode: ' || l_process_mode);
6685: ELSE
6686: OPEN enc_payrolls_cur;
6687: LOOP
6688: FETCH enc_payrolls_cur INTO l_payroll_id;
6691: enc_pre_process(pactid, l_payroll_id, l_process_mode, l_return_status);
6692: IF l_return_status <> fnd_api.g_ret_sts_success THEN
6693: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
6694: END IF;
6695: hr_utility.trace(fnd_date.date_to_canonical(SYSDATE) || ' Completed enc_pre_process for l_payroll_id: ' || l_payroll_id || ' process_mode: ' || l_process_mode);
6696: END LOOP;
6697: CLOSE enc_payrolls_cur;
6698: l_payroll_id := NULL;
6699: END IF;
6729: p_return_status => l_return_status);
6730: RAISE NO_UPDATE_REC_FOUND;
6731: END IF;
6732:
6733: hr_utility.trace(fnd_date.date_to_canonical(SYSDATE) || ' sqlstr: ' || sqlstr);
6734: hr_utility.trace(fnd_date.date_to_canonical(SYSDATE) || ' Leaving CEL_RANGE_CODE pactid: ' || pactid);
6735: EXCEPTION
6736: WHEN NO_UPDATE_REC_FOUND THEN
6737: psp_message_s.print_error (p_mode => FND_FILE.LOG, p_print_header => FND_API.G_FALSE);
6730: RAISE NO_UPDATE_REC_FOUND;
6731: END IF;
6732:
6733: hr_utility.trace(fnd_date.date_to_canonical(SYSDATE) || ' sqlstr: ' || sqlstr);
6734: hr_utility.trace(fnd_date.date_to_canonical(SYSDATE) || ' Leaving CEL_RANGE_CODE pactid: ' || pactid);
6735: EXCEPTION
6736: WHEN NO_UPDATE_REC_FOUND THEN
6737: psp_message_s.print_error (p_mode => FND_FILE.LOG, p_print_header => FND_API.G_FALSE);
6738: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Leaving CEL_RANGE_CODE pactid: ' || pactid);
6734: hr_utility.trace(fnd_date.date_to_canonical(SYSDATE) || ' Leaving CEL_RANGE_CODE pactid: ' || pactid);
6735: EXCEPTION
6736: WHEN NO_UPDATE_REC_FOUND THEN
6737: psp_message_s.print_error (p_mode => FND_FILE.LOG, p_print_header => FND_API.G_FALSE);
6738: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Leaving CEL_RANGE_CODE pactid: ' || pactid);
6739: WHEN OTHERS THEN
6740: fnd_file.put_line(fnd_file.log,DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
6741: fnd_msg_pub.add_exc_msg('PSP_ENC_CREATE_LINES', 'CEL_RANGE_CODE: SQLCODE: ' || fnd_number.number_to_canonical(SQLCODE) || ' SQLERRM: ' || SQLERRM);
6742: psp_message_s.print_error(p_mode => FND_FILE.LOG,
6740: fnd_file.put_line(fnd_file.log,DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
6741: fnd_msg_pub.add_exc_msg('PSP_ENC_CREATE_LINES', 'CEL_RANGE_CODE: SQLCODE: ' || fnd_number.number_to_canonical(SQLCODE) || ' SQLERRM: ' || SQLERRM);
6742: psp_message_s.print_error(p_mode => FND_FILE.LOG,
6743: p_print_header => FND_API.G_TRUE);
6744: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Leaving CEL_RANGE_CODE pactid: ' || pactid);
6745: END cel_range_code;
6746:
6747: PROCEDURE cel_asg_action_code (p_pactid IN NUMBER,
6748: start_asg IN NUMBER,
6757: TYPE t_number_15 IS TABLE OF NUMBER(15) INDEX BY BINARY_INTEGER;
6758: t_asg_array t_number_15;
6759: l_asg_action_id NUMBER(15);
6760: BEGIN
6761: hr_utility.trace(fnd_date.date_to_canonical(SYSDATE) || ' Entering CEL_ASG_ACTION_CODE p_pactid: ' || p_pactid || '
6762: start_asg: ' || start_asg || '
6763: end_asg: ' || end_asg || '
6764: p_chunk_num: ' || p_chunk_num);
6765:
6766: OPEN get_assignments_cur;
6767: FETCH get_assignments_cur BULK COLLECT INTO t_asg_array;
6768: CLOSE get_assignments_cur;
6769:
6770: hr_utility.trace(fnd_date.date_to_canonical(SYSDATE) || ' t_asg_array.COUNT: ' || t_asg_array.COUNT);
6771: FOR recno IN 1..t_asg_array.COUNT
6772: LOOP
6773: SELECT pay_assignment_actions_s.NEXTVAL INTO l_asg_action_id FROM DUAL;
6774: hr_nonrun_asact.insact(l_asg_action_id,
6777: object_id => t_asg_array(recno),
6778: object_type => 'ASG',
6779: p_transient_action => TRUE);
6780: END LOOP;
6781: hr_utility.trace(fnd_date.date_to_canonical(SYSDATE) || ' Leaving CEL_ASG_ACTION_CODE payroll_action_id: ' || p_pactid || '
6782: start_asg: ' || start_asg || '
6783: end_asg: ' || end_asg || '
6784: p_chunk_num: ' || p_chunk_num);
6785: END cel_asg_action_code;
7218: p_value2 => l_organization_id,
7219: p_value3 => l_element_type_id,
7220: p_information2 => l_organization_name,
7221: p_information3 => l_element_name,
7222: p_information4 => fnd_date.date_to_canonical(cel_warnings.start_date(recno)),
7223: p_information5 => fnd_date.date_to_canonical(cel_warnings.end_date(recno)),
7224: p_information6 => cel_warnings.hierarchy_code(recno),
7225: p_information7 => cel_warnings.error_status(recno),
7226: p_return_status => l_return_status);
7219: p_value3 => l_element_type_id,
7220: p_information2 => l_organization_name,
7221: p_information3 => l_element_name,
7222: p_information4 => fnd_date.date_to_canonical(cel_warnings.start_date(recno)),
7223: p_information5 => fnd_date.date_to_canonical(cel_warnings.end_date(recno)),
7224: p_information6 => cel_warnings.hierarchy_code(recno),
7225: p_information7 => cel_warnings.error_status(recno),
7226: p_return_status => l_return_status);
7227: END LOOP;
7238: cel_warnings.percent.DELETE;
7239: END log_cel_warnings;
7240: BEGIN
7241: SAVEPOINT CEL_ARCHIVE;
7242: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Entering CEL_ARCHIVE (payroll_action_id: ' || p_payroll_action_id || ' chunk_number: ' || p_chunk_number ||')');
7243:
7244: l_set_of_books_id := FND_PROFILE.VALUE('GL_SET_OF_BKS_ID');
7245: l_business_group_id := FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID');
7246: g_set_of_books_id := l_set_of_books_id;
7290: l_enc_create := 1;
7291: l_pre_process_mode:= 'R';
7292: g_currency_code := psp_general.get_currency_code(l_business_group_id);
7293: psp_general.get_currency_precision(g_currency_code,g_precision,g_ext_precision);
7294: hr_utility.trace(fnd_date.date_to_canonical(SYSDATE) || ' g_enc_line_type: U
7295: g_request_id: ' || g_request_id || '
7296: g_payroll_action_id: ' || g_payroll_action_id || '
7297: g_currency_code: ' || g_currency_code || '
7298: g_ge_autopop: ' || g_ge_autopop || ' g_et_autopop: ' || g_et_autopop ||
7303: OPEN c_obtain_eff_date_option;
7304: FETCH c_obtain_eff_date_option INTO g_eff_date_value;
7305: CLOSE c_obtain_eff_date_option;
7306:
7307: hr_utility.trace(fnd_date.date_to_canonical(SYSDATE) || ' g_eff_date_value: ' || g_eff_date_value);
7308:
7309: obtain_enc_org_end_date(p_enc_org_end_date => l_enc_org_end_date,
7310: p_business_group_id => l_business_group_id,
7311: p_set_of_books_id => l_set_of_books_id,
7313: IF l_return_status <> fnd_api.g_ret_sts_success THEN
7314: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
7315: END IF;
7316: g_enc_org_end_date := l_enc_org_end_date ;
7317: hr_utility.trace(fnd_date.date_to_canonical(SYSDATE) || ' g_enc_org_end_date: ' || fnd_date.date_to_canonical(g_enc_org_end_date));
7318:
7319: OPEN get_asg_id_cur;
7320: FETCH get_asg_id_cur INTO l_min_asg_id, l_max_asg_id;
7321: CLOSE get_asg_id_cur;
7318:
7319: OPEN get_asg_id_cur;
7320: FETCH get_asg_id_cur INTO l_min_asg_id, l_max_asg_id;
7321: CLOSE get_asg_id_cur;
7322: hr_utility.trace(fnd_date.date_to_canonical(SYSDATE) || ' l_min_asg_id: ' || l_min_asg_id || ' l_max_asg_id: ' || l_max_asg_id);
7323:
7324: OPEN get_payroll_asg_cur;
7325: FETCH get_payroll_asg_cur BULK COLLECT INTO t_assignments.payroll_array, t_assignments.asg_array, t_assignments.term_date_array;
7326: CLOSE get_payroll_asg_cur;
7324: OPEN get_payroll_asg_cur;
7325: FETCH get_payroll_asg_cur BULK COLLECT INTO t_assignments.payroll_array, t_assignments.asg_array, t_assignments.term_date_array;
7326: CLOSE get_payroll_asg_cur;
7327:
7328: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' t_assignments.asg_array.COUNT: ' || t_assignments.asg_array.COUNT);
7329: --Bug 13036705
7330: for k in 1.. t_assignments.asg_array.COUNT
7331: loop
7332: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || k||')'||' asg: '||t_assignments.asg_array(k)
7328: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' t_assignments.asg_array.COUNT: ' || t_assignments.asg_array.COUNT);
7329: --Bug 13036705
7330: for k in 1.. t_assignments.asg_array.COUNT
7331: loop
7332: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || k||')'||' asg: '||t_assignments.asg_array(k)
7333: ||' payroll: '||t_assignments.payroll_array(k)
7334: ||' term date: '||t_assignments.term_date_array(k));
7335:
7336: select count(*)
7337: into l_asg_sl_count
7338: from psp_enc_process_assignments
7339: where assignment_id = t_assignments.asg_array(k)
7340: and assignment_status in ('S','L');
7341: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' count: '||l_asg_sl_count);
7342:
7343: end loop;
7344: --Bug 13036705
7345: FOR recno IN 1 ..t_assignments.asg_array.COUNT
7353: OPEN liq_only_asg_cur;
7354: FETCH liq_only_asg_cur INTO l_liq_only_count;
7355: CLOSE liq_only_asg_cur;
7356:
7357: hr_utility.trace(fnd_date.date_to_canonical(SYSDATE) || ' l_liq_only_count: '||l_liq_only_count);
7358:
7359: OPEN liq_all_cur;
7360: FETCH liq_all_cur INTO l_liq_all_count;
7361: CLOSE liq_all_cur;
7359: OPEN liq_all_cur;
7360: FETCH liq_all_cur INTO l_liq_all_count;
7361: CLOSE liq_all_cur;
7362:
7363: hr_utility.trace(fnd_date.date_to_canonical(SYSDATE) || ' l_liq_all_count: '||l_liq_all_count);
7364:
7365: IF ((l_liq_all_count =0) AND (l_liq_only_count > 0)) THEN
7366: delete_previous_error_log(p_assignment_id => l_assignment_id,
7367: p_payroll_id => l_payroll_id,
7376: IF g_payroll_mode = 'N' THEN
7377:
7378: -- 13566809 : For OTHER payroll we are using the same old cursor c_last_pay_run.
7379:
7380: hr_utility.trace(fnd_date.date_to_canonical(SYSDATE) || ' g_payroll_mode : N ');
7381:
7382: OPEN c_last_pay_run(t_assignments.asg_array(recno));
7383: FETCH c_last_pay_run INTO l_enc_begin_date;
7384: CLOSE c_last_pay_run;
7390: So therefore in this process at this place earlier the query C_LAST_PAY_RUN cursor was used and this has outer join.
7391: But now when calling GET_ASG_PAY_END_DATE function, this uses cursor query ORACLE_PAYROLL_ASG_END_DATE which does not
7392: have outer join and this is a common query which is already used in CEL_RANGE_CODE */
7393:
7394: hr_utility.trace(fnd_date.date_to_canonical(SYSDATE) || ' g_payroll_mode : Y ');
7395:
7396: l_enc_begin_date := get_asg_pay_end_date(NULL,p_payroll_action_id,l_assignment_id,l_business_group_id,l_payroll_id); --13566809
7397:
7398: END IF;
7396: l_enc_begin_date := get_asg_pay_end_date(NULL,p_payroll_action_id,l_assignment_id,l_business_group_id,l_payroll_id); --13566809
7397:
7398: END IF;
7399:
7400: hr_utility.trace(fnd_date.date_to_canonical(SYSDATE) || ' l_enc_begin_date 1 '||l_enc_begin_date);
7401:
7402:
7403: IF l_enc_begin_date IS NULL THEN
7404: OPEN c_tp_start_date;
7406: CLOSE c_tp_start_date;
7407: END IF;
7408:
7409: -- END IF;
7410: hr_utility.trace(fnd_date.date_to_canonical(SYSDATE) || ' l_enc_begin_date 2 '||l_enc_begin_date);
7411:
7412:
7413: OPEN earnings_element_cur(l_assignment_id);
7414: LOOP
7434:
7435: IF l_enc_create = 0 AND l_enc_create_lines = 0 THEN
7436: l_enc_create :=1;
7437: END IF;
7438: hr_utility.trace(fnd_date.date_to_canonical(SYSDATE) || ' g_actual_term_date: ' || fnd_date.date_to_canonical(g_actual_term_date) || '
7439: l_max_enc_date: ' || fnd_date.date_to_canonical(l_max_enc_date) || '
7440: l_enc_create: ' || l_enc_create);
7441:
7442: IF l_enc_create > 0 THEN
7435: IF l_enc_create = 0 AND l_enc_create_lines = 0 THEN
7436: l_enc_create :=1;
7437: END IF;
7438: hr_utility.trace(fnd_date.date_to_canonical(SYSDATE) || ' g_actual_term_date: ' || fnd_date.date_to_canonical(g_actual_term_date) || '
7439: l_max_enc_date: ' || fnd_date.date_to_canonical(l_max_enc_date) || '
7440: l_enc_create: ' || l_enc_create);
7441:
7442: IF l_enc_create > 0 THEN
7443: hr_utility.trace(fnd_date.date_to_canonical(SYSDATE) || ' p_chunk_number: ' || p_chunk_number || '
7439: l_max_enc_date: ' || fnd_date.date_to_canonical(l_max_enc_date) || '
7440: l_enc_create: ' || l_enc_create);
7441:
7442: IF l_enc_create > 0 THEN
7443: hr_utility.trace(fnd_date.date_to_canonical(SYSDATE) || ' p_chunk_number: ' || p_chunk_number || '
7444: p_assignment_id => ' || l_assignment_id || '
7445: p_payroll_id => ' || l_payroll_id || '
7446: p_element_type_id => ' || l_element_type_id || '
7447: p_business_group_id => ' || l_business_group_id || '
7458: p_return_status => l_return_status);
7459: IF l_return_status <> fnd_api.g_ret_sts_success THEN
7460: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
7461: END IF;
7462: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Completed create_lines for l_assignment_id: ' || l_assignment_id || ' payroll_id: ' || l_payroll_id || ' element_type_id: ' || l_element_type_id);
7463: log_cel_warnings;
7464: END IF;
7465: END IF;
7466: IF (g_employee_hours = 'Y') THEN
7483:
7484: IF l_enc_create = 0 AND l_enc_create_lines = 0 THEN
7485: l_enc_create :=1;
7486: END IF;
7487: hr_utility.trace(fnd_date.date_to_canonical(SYSDATE) || ' g_actual_term_date: ' || fnd_date.date_to_canonical(g_actual_term_date) || '
7488: l_max_enc_date: ' || fnd_date.date_to_canonical(l_max_enc_date) || '
7489: l_enc_create: ' || l_enc_create);
7490:
7491: IF l_enc_create > 0 THEN
7484: IF l_enc_create = 0 AND l_enc_create_lines = 0 THEN
7485: l_enc_create :=1;
7486: END IF;
7487: hr_utility.trace(fnd_date.date_to_canonical(SYSDATE) || ' g_actual_term_date: ' || fnd_date.date_to_canonical(g_actual_term_date) || '
7488: l_max_enc_date: ' || fnd_date.date_to_canonical(l_max_enc_date) || '
7489: l_enc_create: ' || l_enc_create);
7490:
7491: IF l_enc_create > 0 THEN
7492: hr_utility.trace(fnd_date.date_to_canonical(SYSDATE) || ' p_chunk_number: ' || p_chunk_number || '
7488: l_max_enc_date: ' || fnd_date.date_to_canonical(l_max_enc_date) || '
7489: l_enc_create: ' || l_enc_create);
7490:
7491: IF l_enc_create > 0 THEN
7492: hr_utility.trace(fnd_date.date_to_canonical(SYSDATE) || ' p_chunk_number: ' || p_chunk_number || '
7493: p_assignment_id => ' || l_assignment_id || '
7494: p_payroll_id => ' || l_payroll_id || '
7495: p_element_type_id => ' || l_element_type_id || '
7496: p_business_group_id => ' || l_business_group_id || '
7507: p_return_status => l_return_status);
7508: IF l_return_status <> fnd_api.g_ret_sts_success THEN
7509: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
7510: END IF;
7511: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Completed create_lines for l_assignment_id: ' || l_assignment_id || ' payroll_id: ' || l_payroll_id || ' element_type_id: ' || l_element_type_id);
7512: log_cel_warnings;
7513: END IF;
7514: END IF;
7515: END IF;
7520: fnd_file.put_line(fnd_file.log, 'No element found for encumbrance.');
7521: END IF;
7522: CLOSE earnings_element_cur;
7523:
7524: hr_utility.trace(fnd_date.date_to_canonical(SYSDATE) || ' t_enc_lines_array.r_enc_element_type_id.COUNT: ' || t_enc_lines_array.r_enc_element_type_id.COUNT);
7525: verify_changes(p_payroll_id => l_payroll_id,
7526: p_assignment_id => l_assignment_id,
7527: p_business_group_id => l_business_group_id,
7528: p_set_of_books_id => l_set_of_books_id,
7531:
7532: IF l_return_status <> fnd_api.g_ret_sts_success THEN
7533: RAISE fnd_api.g_exc_unexpected_error;
7534: END IF;
7535: hr_utility.trace(fnd_date.date_to_canonical(SYSDATE) || ' Completed verify_changes for l_assignment_id: ' || l_assignment_id || ' payroll_id: ' || l_payroll_id);
7536: ELSE
7537: IF (l_liq_all_count > 0) THEN
7538: UPDATE psp_enc_lines_history pelh
7539: SET change_flag = 'N'
7545: WHERE pesl.assignment_id = l_assignment_id
7546: AND pesl.payroll_id = l_payroll_id
7547: AND status_code = 'A'
7548: AND pesl.enc_summary_line_id = pelh.enc_summary_line_id);
7549: hr_utility.trace(fnd_date.date_to_canonical(SYSDATE) || ' Updated lines to be liquidated');
7550: DELETE psp_enc_changed_assignments
7551: WHERE assignment_id = l_assignment_id
7552: AND payroll_id = l_payroll_id
7553: AND change_type = 'ZZ';
7566: AND pesl.effective_date <= (NVL((get_payroll_pay_end_date(l_payroll_id)
7567: -- and ptp.time_period_id = ppa.time_period_id -- Added for 11661463 -- Commented for the Bug 12647364
7568: ),l_enc_begin_date)) );
7569:
7570: hr_utility.trace(fnd_date.date_to_canonical(SYSDATE) || ' Updated lines to be liquidated by regular liquidation');
7571: UPDATE psp_enc_lines_history pelh
7572: SET change_flag = 'U'
7573: WHERE assignment_id = l_assignment_id
7574: AND payroll_id = l_payroll_id
7582: AND pesl.effective_date > (NVL((get_payroll_pay_end_date(l_payroll_id)
7583: -- and ptp.time_period_id = ppa.time_period_id -- Added for 11661463 -- Commented for the Bug 12647364
7584: ),l_enc_begin_date)) );
7585:
7586: hr_utility.trace(fnd_date.date_to_canonical(SYSDATE) || ' Updated lines not to be liquidated by regular liquidation');
7587: END IF;
7588: END IF;
7589:
7590: create_liq_lines (p_payroll_action_id => p_payroll_action_id,
7597:
7598: IF l_return_status <> fnd_api.g_ret_sts_success THEN
7599: RAISE fnd_api.g_exc_unexpected_error;
7600: END IF;
7601: hr_utility.trace(fnd_date.date_to_canonical(SYSDATE) || ' Completed create_liq_lines for l_assignment_id: ' || l_assignment_id || ' payroll_id: ' || l_payroll_id);
7602:
7603: create_sum_lines (p_payroll_action_id => p_payroll_action_id,
7604: p_payroll_id => l_payroll_id,
7605: p_assignment_id => l_assignment_id,
7609:
7610: IF l_return_status <> fnd_api.g_ret_sts_success THEN
7611: RAISE fnd_api.g_exc_unexpected_error;
7612: END IF;
7613: hr_utility.trace(fnd_date.date_to_canonical(SYSDATE) || ' Completed create_sum_lines for l_assignment_id: ' || l_assignment_id || ' payroll_id: ' || l_payroll_id);
7614:
7615: update_hierarchy_dates (p_payroll_action_id => p_payroll_action_id,
7616: p_payroll_id => l_payroll_id,
7617: p_assignment_id => l_assignment_id,
7661:
7662: t_assignments.payroll_array.delete;
7663: t_assignments.asg_array.delete;
7664:
7665: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Leaving CEL_ARCHIVE (payroll_action_id: ' || p_payroll_action_id || ' chunk_number: ' || p_chunk_number ||')');
7666: EXCEPTION
7667: WHEN OTHERS THEN
7668: fnd_file.put_line(fnd_file.log,DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
7669: log_cel_warnings;
7721: END IF;
7722: END IF;
7723: fnd_msg_pub.add_exc_msg('PSP_ENC_CREATE_LINES', 'CEL_ARCHIVE: SQLCODE: ' || fnd_number.number_to_canonical(SQLCODE) || ' SQLERRM: ' || SQLERRM);
7724: psp_message_s.print_error(p_mode => FND_FILE.LOG, p_print_header => FND_API.G_TRUE);
7725: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Leaving CEL_ARCHIVE (payroll_action_id: ' || p_payroll_action_id || ' chunk_number: ' || p_chunk_number ||')');
7726: ROLLBACK TO CEL_ARCHIVE;
7727: RAISE;
7728: END cel_archive;
7729:
7771: FROM psp_enc_summary_lines
7772: WHERE payroll_action_id = p_payroll_action_id
7773: AND status_code = 'N';
7774: BEGIN
7775: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Entering CEL_DEINIT payroll_action_id: ' || p_payroll_action_id);
7776: l_last_update_date := SYSDATE;
7777: l_last_updated_by:= NVL(FND_GLOBAL.USER_ID, -1);
7778: l_request_id := fnd_global.conc_request_id;
7779:
7791: FETCH error_check_cur INTO l_error_count;
7792: CLOSE error_check_cur;
7793:
7794: IF (l_error_count > 0) THEN
7795: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Create and Update Encumbrance process has errors. Please review the Run Results Report for more details.');
7796: l_business_group_id := fnd_profile.value('PER_BUSINESS_GROUP_ID');
7797: l_set_of_books_id := fnd_profile.value('GL_SET_OF_BKS_ID');
7798: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Before submitting Encumbrance Run Results Report');
7799: l_xml_layout := fnd_request.add_layout('PSP','PSPENRSRTF','en','US','PDF');
7794: IF (l_error_count > 0) THEN
7795: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Create and Update Encumbrance process has errors. Please review the Run Results Report for more details.');
7796: l_business_group_id := fnd_profile.value('PER_BUSINESS_GROUP_ID');
7797: l_set_of_books_id := fnd_profile.value('GL_SET_OF_BKS_ID');
7798: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Before submitting Encumbrance Run Results Report');
7799: l_xml_layout := fnd_request.add_layout('PSP','PSPENRSRTF','en','US','PDF');
7800: l_request_id := fnd_request.submit_request('PSP',
7801: 'PSPENRSLT',
7802: '',
7807: TO_CHAR(l_business_group_id),
7808: TO_CHAR(l_set_of_books_id));
7809:
7810: IF l_request_id = 0 THEN
7811: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Encumbrance Run Results Report submission failed');
7812: fnd_message.set_name('PSP','PSP_TR_GL_IMP_FAILED');
7813: fnd_msg_pub.add;
7814: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
7815: END IF;
7851:
7852: fnd_stats.gather_table_stats('PSP', 'PSP_ENC_CONTROLS');
7853: fnd_stats.gather_table_stats('PSP', 'PSP_ENC_LINES');
7854: fnd_stats.gather_table_stats('PSP', 'PSP_ENC_SUMMARY_LINES');
7855: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Completed Gather Statistics');
7856:
7857: DELETE FROM psp_enc_controls pec
7858: WHERE pec.payroll_action_id = p_payroll_action_id
7859: AND NOT EXISTS (SELECT 1
7858: WHERE pec.payroll_action_id = p_payroll_action_id
7859: AND NOT EXISTS (SELECT 1
7860: FROM psp_enc_lines pel
7861: WHERE pel.enc_control_id = pec.enc_control_id);
7862: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Deleted lines in psp_enc_controls which doesnt have a line in psp_enc_lines');
7863:
7864: UPDATE psp_enc_controls pec
7865: SET (action_code,
7866: number_of_dr, number_of_cr,
7876: SUM(fnd_number.canonical_to_number(DECODE(pel.gl_project_flag, 'P', DECODE(pel.dr_cr_flag, 'C', pel.encumbrance_amount, 0), 0)))
7877: FROM psp_enc_lines pel
7878: WHERE pel.enc_control_id = pec.enc_control_id)
7879: WHERE payroll_action_id = p_payroll_action_id;
7880: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Updated summary columns in psp_enc_controls');
7881:
7882: IF (l_process_mode = 'TERMINATE') THEN
7883: UPDATE psp_enc_changed_assignments peca
7884: SET payroll_action_id = p_payroll_action_id
7895: 'U', l_last_updated_by, l_last_update_date, NULL, NVL(peca.reference_id, 0),
7896: NVL(peca.action_type, 'U'), p_payroll_action_id, change_date
7897: FROM psp_enc_changed_assignments peca
7898: WHERE payroll_action_id = p_payroll_action_id;
7899: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Copied change assignment records to psp_enc_changed_asg_history');
7900:
7901: DELETE psp_enc_changed_assignments peca
7902: WHERE peca.payroll_action_id = p_payroll_action_id;
7903: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Deleted processed change assignment records in psp_enc_change_assignments');
7899: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Copied change assignment records to psp_enc_changed_asg_history');
7900:
7901: DELETE psp_enc_changed_assignments peca
7902: WHERE peca.payroll_action_id = p_payroll_action_id;
7903: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Deleted processed change assignment records in psp_enc_change_assignments');
7904:
7905: UPDATE psp_enc_processes
7906: SET process_status = 'P'
7907: WHERE payroll_action_id = p_payroll_action_id;
7904:
7905: UPDATE psp_enc_processes
7906: SET process_status = 'P'
7907: WHERE payroll_action_id = p_payroll_action_id;
7908: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Updated process_status in psp_enc_processes');
7909:
7910: UPDATE psp_enc_processes
7911: SET process_status = 'B',
7912: process_phase = 'no_summarize_transfer'
7932: FROM psp_enc_summary_lines pesl
7933: WHERE pesl.payroll_action_id = p_payroll_action_id
7934: AND pesl.status_code = 'N'
7935: AND pesl.superceded_line_id is NOT NULL);
7936: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Updated process_phase in psp_enc_processes');
7937: END IF;
7938:
7939: l_business_group_id := fnd_profile.value('PER_BUSINESS_GROUP_ID');
7940: l_set_of_books_id := fnd_profile.value('GL_SET_OF_BKS_ID');
7937: END IF;
7938:
7939: l_business_group_id := fnd_profile.value('PER_BUSINESS_GROUP_ID');
7940: l_set_of_books_id := fnd_profile.value('GL_SET_OF_BKS_ID');
7941: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Before submitting Encumbrance Run Results Report');
7942: l_xml_layout := fnd_request.add_layout('PSP','PSPENRSRTF','en','US','PDF');
7943: l_request_id := fnd_request.submit_request('PSP',
7944: 'PSPENRSLT',
7945: '',
7950: TO_CHAR(l_business_group_id),
7951: TO_CHAR(l_set_of_books_id));
7952:
7953: IF l_request_id = 0 THEN
7954: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Encumbrance Run Results Report submission failed');
7955: fnd_message.set_name('PSP','PSP_TR_GL_IMP_FAILED');
7956: fnd_msg_pub.add;
7957: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
7958: END IF;
7963:
7964: DELETE FROM psp_asg_end_dates
7965: WHERE payroll_action_id = p_payroll_action_id;
7966:
7967: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Deleting data from the temporary dummy table'||sql%rowcount);
7968: hr_utility.trace(fnd_date.date_to_canonical(SYSDATE)|| ' Deleting data from the temporary dummy table'||sql%rowcount);
7969: IF (l_process_mode = 'TERMINATE') THEN
7970: OPEN st_count_cur;
7971: FETCH st_count_cur INTO l_st_count;
7964: DELETE FROM psp_asg_end_dates
7965: WHERE payroll_action_id = p_payroll_action_id;
7966:
7967: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Deleting data from the temporary dummy table'||sql%rowcount);
7968: hr_utility.trace(fnd_date.date_to_canonical(SYSDATE)|| ' Deleting data from the temporary dummy table'||sql%rowcount);
7969: IF (l_process_mode = 'TERMINATE') THEN
7970: OPEN st_count_cur;
7971: FETCH st_count_cur INTO l_st_count;
7972: CLOSE st_count_cur;
7974: IF (l_st_count > 0) THEN
7975: l_business_group_id := fnd_profile.value('PER_BUSINESS_GROUP_ID');
7976: l_set_of_books_id := fnd_profile.value('GL_SET_OF_BKS_ID');
7977:
7978: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Before submitting Encumbrance Summarize and Transfer');
7979: l_request_id := fnd_request.submit_request('PSP',
7980: 'PSPENSTR',
7981: '',
7982: '',
7985: TO_CHAR(l_business_group_id),
7986: TO_CHAR(l_set_of_books_id));
7987:
7988: IF l_request_id = 0 THEN
7989: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Encumbrance Summarize and Transfer submission failed');
7990: fnd_message.set_name('PSP','PSP_TR_GL_IMP_FAILED');
7991: fnd_msg_pub.add;
7992: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
7993: END IF;
7995: UPDATE psp_enc_processes
7996: SET process_phase = 'deinit_st'
7997: WHERE payroll_action_id = p_payroll_action_id
7998: AND process_code = 'ST';
7999: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Updated process_phase in psp_enc_processes');
8000:
8001: COMMIT;
8002:
8003: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Waiting for Encumbrance Summarize and Transfer request to complete');
7999: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Updated process_phase in psp_enc_processes');
8000:
8001: COMMIT;
8002:
8003: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Waiting for Encumbrance Summarize and Transfer request to complete');
8004: call_status := fnd_concurrent.wait_for_request(l_request_id, 10, 0, rphase, rstatus, dphase, dstatus, message);
8005:
8006: IF call_status = FALSE THEN
8007: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Encumbrance Summarize and Transfer failed');
8003: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Waiting for Encumbrance Summarize and Transfer request to complete');
8004: call_status := fnd_concurrent.wait_for_request(l_request_id, 10, 0, rphase, rstatus, dphase, dstatus, message);
8005:
8006: IF call_status = FALSE THEN
8007: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Encumbrance Summarize and Transfer failed');
8008: fnd_message.set_name('PSP','PSP_ENC_STR_FAILED');
8009: fnd_msg_pub.add;
8010: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
8011: END IF;
8008: fnd_message.set_name('PSP','PSP_ENC_STR_FAILED');
8009: fnd_msg_pub.add;
8010: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
8011: END IF;
8012: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Encumbrance Summarize and Transfer completed');
8013: ELSE
8014: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Encumbrance Summarize and Transfer not required as there arent any new summary lines');
8015: END IF;
8016: END IF;
8010: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
8011: END IF;
8012: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Encumbrance Summarize and Transfer completed');
8013: ELSE
8014: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Encumbrance Summarize and Transfer not required as there arent any new summary lines');
8015: END IF;
8016: END IF;
8017:
8018: COMMIT;
8015: END IF;
8016: END IF;
8017:
8018: COMMIT;
8019: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Leaving CEL_DEINIT payroll_action_id: ' || p_payroll_action_id);
8020: END cel_deinit;
8021:
8022: PROCEDURE verify_changes(p_payroll_id IN NUMBER,
8023: p_assignment_id IN NUMBER,
8127: l_enc_lines_no NUMBER(15);
8128: l_delete_flag CHAR(1);
8129: l_return_status VARCHAR2(1);
8130: BEGIN
8131: hr_utility.trace(fnd_date.date_to_canonical(SYSDATE) || ' Entering verify_changes
8132: p_payroll_id: ' || p_payroll_id || ' p_assignment_id: ' || p_assignment_id || '
8133: p_business_group_id: ' || p_business_group_id || ' p_set_of_books_id: ' || p_set_of_books_id || '
8134: p_enc_line_type: ' || p_enc_line_type);
8135:
8431: t_enc_lines_array.r_hierarchy_code.DELETE;
8432: t_enc_lines_array.r_hierarchy_start_date.DELETE;
8433: t_enc_lines_array.r_hierarchy_end_date.DELETE;
8434:
8435: hr_utility.trace(fnd_date.date_to_canonical(SYSDATE) || ' t_enc_lines_array2.r_enc_element_type_id.COUNT: ' || t_enc_lines_array2.r_enc_element_type_id.COUNT);
8436:
8437: --IF (t_enc_lines_array2.r_enc_element_type_id.COUNT = 0) THEN
8438: --delete_previous_error_log(p_assignment_id => p_assignment_id,
8439: --p_payroll_id => p_payroll_id,
8449: IF l_return_status <> fnd_api.g_ret_sts_success THEN
8450: RAISE fnd_api.g_exc_unexpected_error;
8451: END IF;
8452:
8453: hr_utility.trace(fnd_date.date_to_canonical(SYSDATE) || ' Leaving verify_changes
8454: p_payroll_id: ' || p_payroll_id || ' p_assignment_id: ' || p_assignment_id || '
8455: p_business_group_id: ' || p_business_group_id || ' p_set_of_books_id: ' || p_set_of_books_id || '
8456: p_enc_line_type: ' || p_enc_line_type);
8457: l_retcode := FND_API.G_RET_STS_SUCCESS;
8463: END IF;
8464: g_error_api_path := SUBSTR('VERIFY_CHANGES:' || g_error_api_path,1,230);
8465: fnd_msg_pub.add_exc_msg('PSP_ENC_UPDATE_LINES', 'VERIFY_CHANGES');
8466: l_retcode := fnd_api.g_ret_sts_unexp_error;
8467: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Leaving verify_changes
8468: p_payroll_id: ' || p_payroll_id || '
8469: p_assignment_id: ' || p_assignment_id || '
8470: p_business_group_id: ' || p_business_group_id || '
8471: p_set_of_books_id: ' || p_set_of_books_id || '
8548: FROM psp_enc_lines_history pelh
8549: WHERE pelh.change_flag = 'N'
8550: AND pelh.assignment_id = p_assignment_id
8551: AND pelh.payroll_id = p_payroll_id);
8552: hr_utility.trace(fnd_date.date_to_canonical(SYSDATE) || ' No of liquidation lines created: ' || SQL%ROWCOUNT);
8553:
8554: p_return_status := fnd_api.g_ret_sts_success;
8555: EXCEPTION
8556: WHEN OTHERS THEN
8719: g_actual_term_date, SYSDATE,
8720: l_last_updated_by, l_last_update_login,
8721: l_last_updated_by, SYSDATE,
8722: t_sum_lines.org_id(recno));
8723: hr_utility.trace(fnd_date.date_to_canonical(SYSDATE) || ' No of Summary lines created (t_sum_lines.enc_control_id.COUNT): ' || t_sum_lines.enc_control_id.COUNT);
8724:
8725: IF (g_dff_grouping_option = 'Y') THEN
8726: FORALL recno IN 1..t_sum_lines.enc_summary_line_id.COUNT
8727: UPDATE psp_enc_lines pel
8826: WHERE ROWNUM = 1;
8827:
8828: CURSOR action_parameters_cur IS
8829: SELECT fnd_number.canonical_to_number(NVL(argument13, '-1')),
8830: fnd_date.canonical_to_date(NVL(argument14, fnd_date.date_to_canonical(TRUNC(SYSDATE)))),
8831: fnd_number.canonical_to_number(NVL(argument15, '-1')),
8832: fnd_date.canonical_to_date(NVL(argument16, fnd_date.date_to_canonical(TRUNC(SYSDATE)))),
8833: fnd_number.canonical_to_number(NVL(argument17, '-1')),
8834: fnd_date.canonical_to_date(NVL(argument18, fnd_date.date_to_canonical(TRUNC(SYSDATE)))),
8828: CURSOR action_parameters_cur IS
8829: SELECT fnd_number.canonical_to_number(NVL(argument13, '-1')),
8830: fnd_date.canonical_to_date(NVL(argument14, fnd_date.date_to_canonical(TRUNC(SYSDATE)))),
8831: fnd_number.canonical_to_number(NVL(argument15, '-1')),
8832: fnd_date.canonical_to_date(NVL(argument16, fnd_date.date_to_canonical(TRUNC(SYSDATE)))),
8833: fnd_number.canonical_to_number(NVL(argument17, '-1')),
8834: fnd_date.canonical_to_date(NVL(argument18, fnd_date.date_to_canonical(TRUNC(SYSDATE)))),
8835: fnd_number.canonical_to_number(NVL(argument19, '-1')),
8836: fnd_date.canonical_to_date(NVL(argument20, fnd_date.date_to_canonical(TRUNC(SYSDATE)))),
8830: fnd_date.canonical_to_date(NVL(argument14, fnd_date.date_to_canonical(TRUNC(SYSDATE)))),
8831: fnd_number.canonical_to_number(NVL(argument15, '-1')),
8832: fnd_date.canonical_to_date(NVL(argument16, fnd_date.date_to_canonical(TRUNC(SYSDATE)))),
8833: fnd_number.canonical_to_number(NVL(argument17, '-1')),
8834: fnd_date.canonical_to_date(NVL(argument18, fnd_date.date_to_canonical(TRUNC(SYSDATE)))),
8835: fnd_number.canonical_to_number(NVL(argument19, '-1')),
8836: fnd_date.canonical_to_date(NVL(argument20, fnd_date.date_to_canonical(TRUNC(SYSDATE)))),
8837: fnd_number.canonical_to_number(NVL(argument21, '-1')),
8838: fnd_date.canonical_to_date(NVL(argument22, fnd_date.date_to_canonical(TRUNC(SYSDATE))))
8832: fnd_date.canonical_to_date(NVL(argument16, fnd_date.date_to_canonical(TRUNC(SYSDATE)))),
8833: fnd_number.canonical_to_number(NVL(argument17, '-1')),
8834: fnd_date.canonical_to_date(NVL(argument18, fnd_date.date_to_canonical(TRUNC(SYSDATE)))),
8835: fnd_number.canonical_to_number(NVL(argument19, '-1')),
8836: fnd_date.canonical_to_date(NVL(argument20, fnd_date.date_to_canonical(TRUNC(SYSDATE)))),
8837: fnd_number.canonical_to_number(NVL(argument21, '-1')),
8838: fnd_date.canonical_to_date(NVL(argument22, fnd_date.date_to_canonical(TRUNC(SYSDATE))))
8839: FROM psp_enc_processes pep,
8840: fnd_concurrent_requests fcr
8834: fnd_date.canonical_to_date(NVL(argument18, fnd_date.date_to_canonical(TRUNC(SYSDATE)))),
8835: fnd_number.canonical_to_number(NVL(argument19, '-1')),
8836: fnd_date.canonical_to_date(NVL(argument20, fnd_date.date_to_canonical(TRUNC(SYSDATE)))),
8837: fnd_number.canonical_to_number(NVL(argument21, '-1')),
8838: fnd_date.canonical_to_date(NVL(argument22, fnd_date.date_to_canonical(TRUNC(SYSDATE))))
8839: FROM psp_enc_processes pep,
8840: fnd_concurrent_requests fcr
8841: WHERE pep.payroll_action_id = p_payroll_action_id
8842: AND fcr.request_id = pep.request_id;
8981:
8982: asg_id_tab t_asg_id_type;
8983: -- Modifications for bug 9862281 end
8984: BEGIN
8985: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Entering ENC_PRE_PROCESS
8986: p_payroll_action_id: ' || p_payroll_action_id || '
8987: p_payroll_id: ' || p_payroll_id || '
8988: p_process_mode: ' || p_process_mode);
8989:
8989:
8990: l_request_id := FND_GLOBAL. CONC_REQUEST_ID;
8991: l_set_of_books_id := FND_PROFILE.VALUE('GL_SET_OF_BKS_ID');
8992: l_business_group_id := FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID');
8993: hr_utility.trace(fnd_date.date_to_canonical(SYSDATE) || ' l_request_id: ' || l_request_id ||'
8994: l_set_of_books_id: ' || l_set_of_books_id || '
8995: l_business_group_id: ' || l_business_group_id);
8996:
8997: IF (p_process_mode = 'REGULAR') THEN
9007: OPEN c_tp_start_date;
9008: FETCH c_tp_start_date INTO l_enc_begin_date;
9009: CLOSE c_tp_start_date;
9010: END IF;
9011: hr_utility.trace(fnd_date.date_to_canonical(SYSDATE) || ' l_enc_begin_date: ' || fnd_date.date_to_canonical(l_enc_begin_date));
9012:
9013: l_pre_process_mode := 'R';
9014: IF (l_new_cust = 0 AND psp_general.start_capturing_updates(l_business_group_id) = 'N') THEN
9015: l_pre_process_mode := 'F';
9023: p_return_status => l_return_status);
9024: IF l_return_status <> fnd_api.g_ret_sts_success THEN
9025: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
9026: END IF;
9027: hr_utility.trace(fnd_date.date_to_canonical(SYSDATE) || ' Completed psp_enc_pre_process.poeta_pre_process');
9028:
9029: IF (l_pre_process_mode <>'F') THEN
9030: psp_enc_pre_process.labor_schedule_pre_process
9031: (p_enc_line_type => 'U',
9034:
9035: IF l_return_status <> fnd_api.g_ret_sts_success THEN
9036: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
9037: END IF;
9038: hr_utility.trace(fnd_date.date_to_canonical(SYSDATE) || ' Completed psp_enc_pre_process.labor_schedule_pre_process');
9039: END IF;
9040:
9041: OPEN enc_payroll_cur;
9042: FETCH enc_payroll_cur INTO l_inc_exc_flag;
9040:
9041: OPEN enc_payroll_cur;
9042: FETCH enc_payroll_cur INTO l_inc_exc_flag;
9043: CLOSE enc_payroll_cur;
9044: hr_utility.trace(fnd_date.date_to_canonical(SYSDATE) || ' l_inc_exc_flag: ' || l_inc_exc_flag);
9045:
9046: -- Bug 7188209
9047: -- Bug 10058104
9048: select prev_enc_end_date into l_prev_enc_end_date
9049: from PSP_ENC_END_DATES_V
9050: where business_group_id = l_business_group_id
9051: and set_of_books_id = l_set_of_books_id
9052: and default_org_flag='Y';
9053: hr_utility.trace(fnd_date.date_to_canonical(SYSDATE) || ' l_prev_enc_end_date = ' || l_prev_enc_end_date);
9054:
9055: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Using Oracle Payroll Profile Value: '||FND_PROFILE.VALUE('PSP_USING_ORACLE_PAYROLL'));
9056:
9057: -- The following profile check is introduced and the insert statements are duplicated
9051: and set_of_books_id = l_set_of_books_id
9052: and default_org_flag='Y';
9053: hr_utility.trace(fnd_date.date_to_canonical(SYSDATE) || ' l_prev_enc_end_date = ' || l_prev_enc_end_date);
9054:
9055: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Using Oracle Payroll Profile Value: '||FND_PROFILE.VALUE('PSP_USING_ORACLE_PAYROLL'));
9056:
9057: -- The following profile check is introduced and the insert statements are duplicated
9058: -- to take care of the performance issues caused by outer joins added for the custoemrs
9059: -- who are using Non-Oracle Payroll as in the bug 12769510
9059: -- who are using Non-Oracle Payroll as in the bug 12769510
9060:
9061: IF NVL(FND_PROFILE.VALUE('PSP_USING_ORACLE_PAYROLL'),'Y') = 'Y' -- Oracle Payroll Profile
9062: THEN
9063: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Using Oracle Payroll');
9064:
9065: g_payroll_mode := 'Y'; --This helps to choose the query for oracle payroll in get_asg_pay_end_date function ----13566809
9066:
9067: IF l_inc_exc_flag = 'Y' THEN
9101: -- AND ptp.time_period_id = ppa.time_period_id -- Added for bug 9862281 -- Commented for the Bug 12647364
9102: ),l_enc_begin_date))
9103: AND pesl.payroll_id = p_payroll_id);
9104:
9105: hr_utility.trace(fnd_date.date_to_canonical(SYSDATE) || '10-A Inserted ' || SQL%ROWCOUNT || ' assignments into psp_enc_changed_assignments');
9106:
9107:
9108: -- Added this INSERT for bug 7188209
9109:
9137: AND pesl.status_code IN ('A', 'N')
9138: AND pesl.effective_date > l_prev_enc_end_date
9139: AND pesl.payroll_id = p_payroll_id);
9140:
9141: hr_utility.trace(fnd_date.date_to_canonical(SYSDATE) || '10-B Inserted ' || SQL%ROWCOUNT || ' assignments into psp_enc_changed_assignments');
9142:
9143: INSERT INTO psp_enc_changed_assignments
9144: (request_id, payroll_id, assignment_id, change_type, payroll_action_id)
9145: SELECT DISTINCT l_request_id, p_payroll_id, pa.assignment_id, 'ZZ', p_payroll_action_id
9172: WHERE pesl.assignment_id = pa.assignment_id
9173: AND pesl.status_code = 'N'
9174: AND pesl.payroll_id = p_payroll_id);
9175:
9176: hr_utility.trace(fnd_date.date_to_canonical(SYSDATE) || '20 Inserted ' || SQL%ROWCOUNT || ' assignments into psp_enc_changed_assignments');
9177:
9178: ELSE
9179: -- Modified the following statement for bug 9862281
9180: INSERT INTO psp_enc_changed_assignments
9212: ),l_enc_begin_date)) );
9213:
9214:
9215:
9216: hr_utility.trace(fnd_date.date_to_canonical(SYSDATE) || '30-A Inserted ' || SQL%ROWCOUNT || ' assignments into psp_enc_changed_assignments');
9217:
9218: -- Added for bug 7188209
9219: INSERT INTO psp_enc_changed_assignments
9220: (request_id, payroll_id, assignment_id, change_type, payroll_action_id)
9240: WHERE pesl.assignment_id = pa.assignment_id
9241: AND pesl.status_code IN ('A', 'N')
9242: AND pesl.effective_date > l_prev_enc_end_date);
9243:
9244: hr_utility.trace(fnd_date.date_to_canonical(SYSDATE) || '30-B Inserted ' || SQL%ROWCOUNT || ' assignments into psp_enc_changed_assignments');
9245:
9246: INSERT INTO psp_enc_changed_assignments
9247: (request_id, payroll_id, assignment_id, change_type, payroll_action_id)
9248: SELECT DISTINCT l_request_id, p_payroll_id, pa.assignment_id, 'ZZ', p_payroll_action_id
9274: FROM psp_enc_summary_lines pesl
9275: WHERE pesl.assignment_id = pa.assignment_id
9276: AND pesl.status_code = 'N'
9277: AND pesl.payroll_id = p_payroll_id);
9278: hr_utility.trace(fnd_date.date_to_canonical(SYSDATE) || '40 Inserted ' || SQL%ROWCOUNT || ' assignments into psp_enc_changed_assignments');
9279: END IF;
9280:
9281: INSERT INTO psp_enc_changed_assignments
9282: (request_id, payroll_id, assignment_id, change_type, payroll_action_id)
9302: AND pesl2.effective_date <= (NVL((get_asg_pay_end_date(g_payroll_mode,p_payroll_action_id,pesl2.assignment_id,pesl2.business_group_id,pesl2.payroll_id) --13566809
9303: -- AND ptp.time_period_id = ppa.time_period_id -- Added for bug 9862281 -- Commented for the Bug 12647364
9304: ),l_enc_begin_date))
9305: AND pesl2.payroll_id = p_payroll_id);
9306: hr_utility.trace(fnd_date.date_to_canonical(SYSDATE) || '50 Inserted ' || SQL%ROWCOUNT || ' assignments into psp_enc_changed_assignments');
9307:
9308: ELSE -- NOn-Oracle Payroll profile is set to YES (For Stanford type of customers)
9309: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Using Non-Oracle Payroll');
9310:
9305: AND pesl2.payroll_id = p_payroll_id);
9306: hr_utility.trace(fnd_date.date_to_canonical(SYSDATE) || '50 Inserted ' || SQL%ROWCOUNT || ' assignments into psp_enc_changed_assignments');
9307:
9308: ELSE -- NOn-Oracle Payroll profile is set to YES (For Stanford type of customers)
9309: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Using Non-Oracle Payroll');
9310:
9311: g_payroll_mode := 'N'; --this helps to choose the query for non oracle payroll in get_asg_pay_end_date function --13566809
9312:
9313: IF l_inc_exc_flag = 'Y' THEN
9347: -- AND ptp.time_period_id = ppa.time_period_id -- Added for bug 9862281 -- Commented for the Bug 12647364
9348: ),l_enc_begin_date)) --13566809
9349: AND pesl.payroll_id = p_payroll_id);
9350:
9351: hr_utility.trace(fnd_date.date_to_canonical(SYSDATE) || '10-A Inserted ' || SQL%ROWCOUNT || ' assignments into psp_enc_changed_assignments');
9352:
9353: -- Added this INSERT for bug 7188209
9354:
9355: INSERT INTO psp_enc_changed_assignments
9382: AND pesl.status_code IN ('A', 'N')
9383: AND pesl.effective_date > l_prev_enc_end_date
9384: AND pesl.payroll_id = p_payroll_id);
9385:
9386: hr_utility.trace(fnd_date.date_to_canonical(SYSDATE) || '10-B Inserted ' || SQL%ROWCOUNT || ' assignments into psp_enc_changed_assignments');
9387:
9388: INSERT INTO psp_enc_changed_assignments
9389: (request_id, payroll_id, assignment_id, change_type, payroll_action_id)
9390: SELECT DISTINCT l_request_id, p_payroll_id, pa.assignment_id, 'ZZ', p_payroll_action_id
9417: WHERE pesl.assignment_id = pa.assignment_id
9418: AND pesl.status_code = 'N'
9419: AND pesl.payroll_id = p_payroll_id);
9420:
9421: hr_utility.trace(fnd_date.date_to_canonical(SYSDATE) || '20 Inserted ' || SQL%ROWCOUNT || ' assignments into psp_enc_changed_assignments');
9422:
9423: ELSE
9424: -- Modified the following statement for bug 9862281
9425: INSERT INTO psp_enc_changed_assignments
9473: and ptp.payroll_id = ppa.payroll_id),l_enc_begin_date)) );
9474:
9475:
9476:
9477: hr_utility.trace(fnd_date.date_to_canonical(SYSDATE) || '30-A Inserted ' || SQL%ROWCOUNT || ' assignments into psp_enc_changed_assignments');
9478:
9479: -- Added for bug 7188209
9480: INSERT INTO psp_enc_changed_assignments
9481: (request_id, payroll_id, assignment_id, change_type, payroll_action_id)
9501: WHERE pesl.assignment_id = pa.assignment_id
9502: AND pesl.status_code IN ('A', 'N')
9503: AND pesl.effective_date > l_prev_enc_end_date);
9504:
9505: hr_utility.trace(fnd_date.date_to_canonical(SYSDATE) || '30-B Inserted ' || SQL%ROWCOUNT || ' assignments into psp_enc_changed_assignments');
9506:
9507: INSERT INTO psp_enc_changed_assignments
9508: (request_id, payroll_id, assignment_id, change_type, payroll_action_id)
9509: SELECT DISTINCT l_request_id, p_payroll_id, pa.assignment_id, 'ZZ', p_payroll_action_id
9535: FROM psp_enc_summary_lines pesl
9536: WHERE pesl.assignment_id = pa.assignment_id
9537: AND pesl.status_code = 'N'
9538: AND pesl.payroll_id = p_payroll_id);
9539: hr_utility.trace(fnd_date.date_to_canonical(SYSDATE) || '40 Inserted ' || SQL%ROWCOUNT || ' assignments into psp_enc_changed_assignments');
9540: END IF;
9541:
9542: INSERT INTO psp_enc_changed_assignments
9543: (request_id, payroll_id, assignment_id, change_type, payroll_action_id)
9563: AND pesl2.effective_date <= (NVL((get_asg_pay_end_date(g_payroll_mode,p_payroll_action_id,pesl2.assignment_id,pesl2.business_group_id,pesl2.payroll_id)--13566809
9564: -- AND ptp.time_period_id = ppa.time_period_id -- Added for bug 9862281 -- Commented for the Bug 12647364
9565: ),l_enc_begin_date))
9566: AND pesl2.payroll_id = p_payroll_id);
9567: hr_utility.trace(fnd_date.date_to_canonical(SYSDATE) || '50 Inserted ' || SQL%ROWCOUNT || ' assignments into psp_enc_changed_assignments');
9568:
9569: END IF; -- End of Oracle Payroll Profile
9570:
9571: UPDATE psp_enc_changed_assignments peca
9576: FROM psp_enc_process_assignments pepa
9577: WHERE pepa.assignment_id = peca.assignment_id
9578: AND pepa.assignment_status NOT IN ('B', 'P')
9579: AND pepa.payroll_id = peca.payroll_id);
9580: hr_utility.trace(fnd_date.date_to_canonical(SYSDATE) || ' Marked ' || SQL%ROWCOUNT || ' assignments in psp_enc_changed_assignments to be processed by this Create and Update process');
9581:
9582: /*
9583: DELETE FROM psp_enc_changed_assignments
9584: WHERE payroll_action_id = p_payroll_action_id
9598: FETCH exc_asg_cur BULK COLLECT INTO asg_id_tab;
9599: asg_id_tab := SET(asg_id_tab);
9600:
9601: FOR rec_i IN 1..asg_id_tab.COUNT LOOP
9602: fnd_file.put_line(fnd_file.log,fnd_date.date_to_canonical(SYSDATE) || ' Excluded asg ' ||asg_id_tab(rec_i));
9603:
9604: l_asg_exists := 0;
9605:
9606: BEGIN
9617: WHERE payroll_action_id = p_payroll_action_id
9618: AND payroll_id = p_payroll_id
9619: AND request_id = l_request_id
9620: and assignment_id = asg_id_tab(rec_i);
9621: fnd_file.put_line(fnd_file.log,fnd_date.date_to_canonical(SYSDATE) || ' Deleted asg ' ||asg_id_tab(rec_i));
9622: END;
9623: END LOOP;
9624:
9625: CLOSE exc_asg_cur;
9624:
9625: CLOSE exc_asg_cur;
9626: END;
9627: -- Modified the delete statement for bug 9862281 end
9628: hr_utility.trace(fnd_date.date_to_canonical(SYSDATE) || ' Deleted new excluded assignments in psp_enc_changed_assignments not to be processed by this Create and Update process');
9629:
9630: hr_utility.trace(fnd_date.date_to_canonical(SYSDATE) || ' opening in_process_asg_cur fpr payroll_id: '||p_payroll_id);
9631:
9632:
9626: END;
9627: -- Modified the delete statement for bug 9862281 end
9628: hr_utility.trace(fnd_date.date_to_canonical(SYSDATE) || ' Deleted new excluded assignments in psp_enc_changed_assignments not to be processed by this Create and Update process');
9629:
9630: hr_utility.trace(fnd_date.date_to_canonical(SYSDATE) || ' opening in_process_asg_cur fpr payroll_id: '||p_payroll_id);
9631:
9632:
9633: OPEN in_process_asg_cur;
9634: LOOP
9634: LOOP
9635: FETCH in_process_asg_cur INTO l_assignment_id, l_payroll_action_id;
9636: EXIT WHEN in_process_asg_cur%NOTFOUND;
9637:
9638: hr_utility.trace(fnd_date.date_to_canonical(SYSDATE) || ' l_assignment_id: '||l_assignment_id ||' l_payroll_action_id: '|| l_payroll_action_id);
9639:
9640: OPEN process_descr_cur;
9641: FETCH process_descr_cur INTO l_process_description;
9642: CLOSE process_descr_cur;
9640: OPEN process_descr_cur;
9641: FETCH process_descr_cur INTO l_process_description;
9642: CLOSE process_descr_cur;
9643:
9644: hr_utility.trace(fnd_date.date_to_canonical(SYSDATE) || ' l_process_description: '||l_process_description);
9645:
9646: fnd_message.set_name('PSP', 'PSP_ENC_ASG_IN_PROCESS');
9647: fnd_message.set_token('PROCESS_DESCRIPTION', l_process_description);
9648: g_warning_message := fnd_message.get;
9646: fnd_message.set_name('PSP', 'PSP_ENC_ASG_IN_PROCESS');
9647: fnd_message.set_token('PROCESS_DESCRIPTION', l_process_description);
9648: g_warning_message := fnd_message.get;
9649:
9650: hr_utility.trace(fnd_date.date_to_canonical(SYSDATE) || ' g_warning_message: '||g_warning_message);
9651:
9652: OPEN asg_number_cur (l_enc_begin_date);
9653: FETCH asg_number_cur INTO l_assignment_number, l_person_id, l_organization_id;
9654: CLOSE asg_number_cur;
9652: OPEN asg_number_cur (l_enc_begin_date);
9653: FETCH asg_number_cur INTO l_assignment_number, l_person_id, l_organization_id;
9654: CLOSE asg_number_cur;
9655:
9656: hr_utility.trace(fnd_date.date_to_canonical(SYSDATE) || ' l_assignment_number: '|| l_assignment_number|| ' l_person_id: '||l_person_id|| ' l_organization_id: '||l_organization_id);
9657:
9658: OPEN payroll_name_cur;
9659: FETCH payroll_name_cur INTO l_payroll_name;
9660: CLOSE payroll_name_cur;
9658: OPEN payroll_name_cur;
9659: FETCH payroll_name_cur INTO l_payroll_name;
9660: CLOSE payroll_name_cur;
9661:
9662: hr_utility.trace(fnd_date.date_to_canonical(SYSDATE) || ' l_payroll_name: '||l_payroll_name);
9663:
9664: OPEN person_name_cur (l_enc_begin_date);
9665: FETCH person_name_cur INTO l_full_name;
9666: CLOSE person_name_cur;
9664: OPEN person_name_cur (l_enc_begin_date);
9665: FETCH person_name_cur INTO l_full_name;
9666: CLOSE person_name_cur;
9667:
9668: hr_utility.trace(fnd_date.date_to_canonical(SYSDATE) || ' l_full_name: '||l_full_name);
9669:
9670: OPEN org_name_cur;
9671: FETCH org_name_cur INTO l_organization_name;
9672: CLOSE org_name_cur;
9669:
9670: OPEN org_name_cur;
9671: FETCH org_name_cur INTO l_organization_name;
9672: CLOSE org_name_cur;
9673: hr_utility.trace(fnd_date.date_to_canonical(SYSDATE) || ' l_organization_name: '||l_organization_name);
9674:
9675:
9676: psp_general.add_report_error
9677: (p_request_id => l_request_id,
9686: p_information1 => l_payroll_name,
9687: p_value2 => l_organization_id,
9688: p_information2 => l_organization_name,
9689: p_return_status => l_return_status);
9690: hr_utility.trace(fnd_date.date_to_canonical(SYSDATE) || ' l_return_status: '||l_return_status);
9691:
9692: END LOOP;
9693: CLOSE in_process_asg_cur;
9694: ELSE
9696: FETCH action_parameters_cur INTO l_person_id1, l_termination_date1,
9697: l_person_id2, l_termination_date2, l_person_id3, l_termination_date3,
9698: l_person_id4, l_termination_date4, l_person_id5, l_termination_date5;
9699: CLOSE action_parameters_cur;
9700: hr_utility.trace(fnd_date.date_to_canonical(SYSDATE) || ' l_person_id1: ' || l_person_id1 || ' l_termination_date1: ' || fnd_date.date_to_canonical(l_termination_date1) || '
9701: l_person_id2: ' || l_person_id2 || ' l_termination_date2: ' || fnd_date.date_to_canonical(l_termination_date2) || '
9702: l_person_id3: ' || l_person_id3 || ' l_termination_date3: ' || fnd_date.date_to_canonical(l_termination_date3) || '
9703: l_person_id4: ' || l_person_id4 || ' l_termination_date4: ' || fnd_date.date_to_canonical(l_termination_date4) || '
9704: l_person_id5: ' || l_person_id5 || ' l_termination_date5: ' || fnd_date.date_to_canonical(l_termination_date5));
9697: l_person_id2, l_termination_date2, l_person_id3, l_termination_date3,
9698: l_person_id4, l_termination_date4, l_person_id5, l_termination_date5;
9699: CLOSE action_parameters_cur;
9700: hr_utility.trace(fnd_date.date_to_canonical(SYSDATE) || ' l_person_id1: ' || l_person_id1 || ' l_termination_date1: ' || fnd_date.date_to_canonical(l_termination_date1) || '
9701: l_person_id2: ' || l_person_id2 || ' l_termination_date2: ' || fnd_date.date_to_canonical(l_termination_date2) || '
9702: l_person_id3: ' || l_person_id3 || ' l_termination_date3: ' || fnd_date.date_to_canonical(l_termination_date3) || '
9703: l_person_id4: ' || l_person_id4 || ' l_termination_date4: ' || fnd_date.date_to_canonical(l_termination_date4) || '
9704: l_person_id5: ' || l_person_id5 || ' l_termination_date5: ' || fnd_date.date_to_canonical(l_termination_date5));
9705:
9698: l_person_id4, l_termination_date4, l_person_id5, l_termination_date5;
9699: CLOSE action_parameters_cur;
9700: hr_utility.trace(fnd_date.date_to_canonical(SYSDATE) || ' l_person_id1: ' || l_person_id1 || ' l_termination_date1: ' || fnd_date.date_to_canonical(l_termination_date1) || '
9701: l_person_id2: ' || l_person_id2 || ' l_termination_date2: ' || fnd_date.date_to_canonical(l_termination_date2) || '
9702: l_person_id3: ' || l_person_id3 || ' l_termination_date3: ' || fnd_date.date_to_canonical(l_termination_date3) || '
9703: l_person_id4: ' || l_person_id4 || ' l_termination_date4: ' || fnd_date.date_to_canonical(l_termination_date4) || '
9704: l_person_id5: ' || l_person_id5 || ' l_termination_date5: ' || fnd_date.date_to_canonical(l_termination_date5));
9705:
9706: INSERT INTO PSP_ENC_CHANGED_ASSIGNMENTS
9699: CLOSE action_parameters_cur;
9700: hr_utility.trace(fnd_date.date_to_canonical(SYSDATE) || ' l_person_id1: ' || l_person_id1 || ' l_termination_date1: ' || fnd_date.date_to_canonical(l_termination_date1) || '
9701: l_person_id2: ' || l_person_id2 || ' l_termination_date2: ' || fnd_date.date_to_canonical(l_termination_date2) || '
9702: l_person_id3: ' || l_person_id3 || ' l_termination_date3: ' || fnd_date.date_to_canonical(l_termination_date3) || '
9703: l_person_id4: ' || l_person_id4 || ' l_termination_date4: ' || fnd_date.date_to_canonical(l_termination_date4) || '
9704: l_person_id5: ' || l_person_id5 || ' l_termination_date5: ' || fnd_date.date_to_canonical(l_termination_date5));
9705:
9706: INSERT INTO PSP_ENC_CHANGED_ASSIGNMENTS
9707: (request_id, assignment_id, payroll_id, change_type, payroll_action_id, change_date)
9700: hr_utility.trace(fnd_date.date_to_canonical(SYSDATE) || ' l_person_id1: ' || l_person_id1 || ' l_termination_date1: ' || fnd_date.date_to_canonical(l_termination_date1) || '
9701: l_person_id2: ' || l_person_id2 || ' l_termination_date2: ' || fnd_date.date_to_canonical(l_termination_date2) || '
9702: l_person_id3: ' || l_person_id3 || ' l_termination_date3: ' || fnd_date.date_to_canonical(l_termination_date3) || '
9703: l_person_id4: ' || l_person_id4 || ' l_termination_date4: ' || fnd_date.date_to_canonical(l_termination_date4) || '
9704: l_person_id5: ' || l_person_id5 || ' l_termination_date5: ' || fnd_date.date_to_canonical(l_termination_date5));
9705:
9706: INSERT INTO PSP_ENC_CHANGED_ASSIGNMENTS
9707: (request_id, assignment_id, payroll_id, change_type, payroll_action_id, change_date)
9708: SELECT DISTINCT l_request_id,
9720: AND NOT EXISTS (SELECT 1
9721: FROM psp_enc_summary_lines pesl2
9722: WHERE pesl2.person_id = l_person_id1
9723: AND pesl2.status_code = 'N');
9724: hr_utility.trace(fnd_date.date_to_canonical(SYSDATE) || ' Inserted termination assignments into psp_enc_changed_assignments ' || SQL%ROWCOUNT);
9725: IF (SQL%ROWCOUNT = 0) THEN
9726: OPEN in_process_term_cur(l_person_id1);
9727: LOOP
9728: FETCH in_process_term_cur INTO l_assignment_id, l_payroll_id, l_payroll_action_id;
9788: AND NOT EXISTS (SELECT 1
9789: FROM psp_enc_summary_lines pesl2
9790: WHERE pesl2.person_id = l_person_id2
9791: AND pesl2.status_code = 'N');
9792: hr_utility.trace(fnd_date.date_to_canonical(SYSDATE) || ' Inserted termination assignments into psp_enc_changed_assignments ' || SQL%ROWCOUNT);
9793: IF (SQL%ROWCOUNT = 0) THEN
9794: OPEN in_process_term_cur(l_person_id2);
9795: LOOP
9796: FETCH in_process_term_cur INTO l_assignment_id, l_payroll_id, l_payroll_action_id;
9857: AND NOT EXISTS (SELECT 1
9858: FROM psp_enc_summary_lines pesl2
9859: WHERE pesl2.person_id = l_person_id3
9860: AND pesl2.status_code = 'N');
9861: hr_utility.trace(fnd_date.date_to_canonical(SYSDATE) || ' Inserted termination assignments into psp_enc_changed_assignments ' || SQL%ROWCOUNT);
9862: IF (SQL%ROWCOUNT = 0) THEN
9863: OPEN in_process_term_cur(l_person_id3);
9864: LOOP
9865: FETCH in_process_term_cur INTO l_assignment_id, l_payroll_id, l_payroll_action_id;
9926: AND NOT EXISTS (SELECT 1
9927: FROM psp_enc_summary_lines pesl2
9928: WHERE pesl2.person_id = l_person_id4
9929: AND pesl2.status_code = 'N');
9930: hr_utility.trace(fnd_date.date_to_canonical(SYSDATE) || ' Inserted termination assignments into psp_enc_changed_assignments ' || SQL%ROWCOUNT);
9931: IF (SQL%ROWCOUNT = 0) THEN
9932: OPEN in_process_term_cur(l_person_id4);
9933: LOOP
9934: FETCH in_process_term_cur INTO l_assignment_id, l_payroll_id, l_payroll_action_id;
9995: AND NOT EXISTS (SELECT 1
9996: FROM psp_enc_summary_lines pesl2
9997: WHERE pesl2.person_id = l_person_id5
9998: AND pesl2.status_code = 'N');
9999: hr_utility.trace(fnd_date.date_to_canonical(SYSDATE) || ' Inserted termination assignments into psp_enc_changed_assignments ' || SQL%ROWCOUNT);
10000: IF (SQL%ROWCOUNT = 0) THEN
10001: OPEN in_process_term_cur(l_person_id5);
10002: LOOP
10003: FETCH in_process_term_cur INTO l_assignment_id, l_payroll_id, l_payroll_action_id;
10047: END IF;
10048: END IF;
10049:
10050: COMMIT;
10051: hr_utility.trace(fnd_date.date_to_canonical(SYSDATE) || ' Leaving ENC_PRE_PROCESS
10052: p_payroll_action_id: ' || p_payroll_action_id || '
10053: p_payroll_id: ' || p_payroll_id || '
10054: p_process_mode: ' || p_process_mode);
10055: p_return_status := fnd_api.g_ret_sts_success;
10060: g_error_message := 'PSP_ENC_PROCESS: ' || SQLERRM;
10061: END IF;
10062: fnd_msg_pub.add_exc_msg('PSP_ENC_CREATE_LINES', 'ENC_PRE_PROCESS: SQLCODE: ' || fnd_number.number_to_canonical(SQLCODE) || ' SQLERRM: ' || SQLERRM);
10063: psp_message_s.print_error(p_mode => FND_FILE.LOG, p_print_header => FND_API.G_TRUE);
10064: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Leaving ENC_PRE_PROCESS
10065: p_payroll_action_id: ' || p_payroll_action_id || '
10066: p_payroll_id: ' || p_payroll_id || '
10067: p_process_mode: ' || p_process_mode);
10068: p_return_status := fnd_api.g_ret_sts_unexp_error;
10112: AND assignment_id = p_assignment_id;
10113:
10114: l_request_id NUMBER(15);
10115: BEGIN
10116: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Entering ROLLBACK_CEL p_payroll_action_id: ' || p_payroll_action_id);
10117: l_request_id := fnd_global.conc_request_id;
10118:
10119: IF (p_person_id1 IS NULL) THEN
10120: OPEN superceded_line_cur(NULL);
10124: FORALL recno IN 1..t_superceded_lines.enc_summary_line_id.COUNT
10125: UPDATE psp_enc_lines_history
10126: SET change_flag = 'N'
10127: WHERE enc_summary_line_id = t_superceded_lines.enc_summary_line_id(recno);
10128: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Reverted respective superceded lines in psp_enc_lines_history');
10129:
10130: DELETE psp_enc_summary_lines
10131: WHERE payroll_action_id = p_payroll_action_id;
10132: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Deleted respective lines in psp_enc_summary_lines');
10128: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Reverted respective superceded lines in psp_enc_lines_history');
10129:
10130: DELETE psp_enc_summary_lines
10131: WHERE payroll_action_id = p_payroll_action_id;
10132: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Deleted respective lines in psp_enc_summary_lines');
10133:
10134: DELETE psp_enc_lines
10135: WHERE payroll_action_id = p_payroll_action_id;
10136: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Deleted respective lines in psp_enc_lines');
10132: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Deleted respective lines in psp_enc_summary_lines');
10133:
10134: DELETE psp_enc_lines
10135: WHERE payroll_action_id = p_payroll_action_id;
10136: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Deleted respective lines in psp_enc_lines');
10137:
10138: DELETE psp_enc_controls
10139: WHERE payroll_action_id = p_payroll_action_id;
10140: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Deleted respective lines in psp_enc_controls');
10136: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Deleted respective lines in psp_enc_lines');
10137:
10138: DELETE psp_enc_controls
10139: WHERE payroll_action_id = p_payroll_action_id;
10140: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Deleted respective lines in psp_enc_controls');
10141:
10142: DELETE psp_report_errors
10143: WHERE payroll_action_id = p_payroll_action_id;
10144: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Deleted respective lines in psp_report_errors');
10140: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Deleted respective lines in psp_enc_controls');
10141:
10142: DELETE psp_report_errors
10143: WHERE payroll_action_id = p_payroll_action_id;
10144: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Deleted respective lines in psp_report_errors');
10145:
10146: UPDATE psp_enc_processes
10147: SET process_status = 'B',
10148: process_phase = 'rollback'
10146: UPDATE psp_enc_processes
10147: SET process_status = 'B',
10148: process_phase = 'rollback'
10149: WHERE payroll_action_id = p_payroll_action_id;
10150: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Updated process status to ''Rollback'' in psp_enc_processes');
10151:
10152: UPDATE psp_enc_process_assignments
10153: SET assignment_status = 'B'
10154: WHERE payroll_action_id = p_payroll_action_id;
10151:
10152: UPDATE psp_enc_process_assignments
10153: SET assignment_status = 'B'
10154: WHERE payroll_action_id = p_payroll_action_id;
10155: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Updated process status to ''Rollback'' in psp_enc_process_assignments');
10156:
10157: UPDATE psp_enc_changed_assignments
10158: SET payroll_action_id = NULL
10159: WHERE payroll_action_id = p_payroll_action_id;
10156:
10157: UPDATE psp_enc_changed_assignments
10158: SET payroll_action_id = NULL
10159: WHERE payroll_action_id = p_payroll_action_id;
10160: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Updated payroll_action_id in psp_enc_changed_assignments');
10161:
10162: INSERT INTO psp_enc_changed_assignments
10163: (request_id, assignment_id, payroll_id, change_type,
10164: processed_flag, reference_id, action_type, change_date)
10166: NULL, NVL(pecah.reference_id, 0), pecah.action_type, change_date
10167: FROM psp_enc_changed_asg_history pecah
10168: WHERE payroll_action_id = p_payroll_action_id
10169: AND action_type NOT IN ('CR', 'LQ', 'TR');
10170: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Copied change assignment records to psp_enc_changed_assignments');
10171:
10172: DELETE psp_enc_changed_asg_history
10173: WHERE payroll_action_id = p_payroll_action_id;
10174: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Copied change assignment records to psp_enc_changed_assignments');
10170: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Copied change assignment records to psp_enc_changed_assignments');
10171:
10172: DELETE psp_enc_changed_asg_history
10173: WHERE payroll_action_id = p_payroll_action_id;
10174: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Copied change assignment records to psp_enc_changed_assignments');
10175: ELSE
10176: OPEN asg_cur(p_person_id1, p_assignment_id1);
10177: FETCH asg_cur BULK COLLECT INTO l_assignments;
10178: CLOSE asg_cur;
10242: FORALL recno IN 1..t_superceded_lines.enc_summary_line_id.COUNT
10243: UPDATE psp_enc_lines_history
10244: SET change_flag = 'N'
10245: WHERE enc_summary_line_id = t_superceded_lines.enc_summary_line_id(recno);
10246: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Reverted respective superceded lines in psp_enc_lines_history');
10247:
10248: DELETE psp_enc_summary_lines
10249: WHERE payroll_action_id = p_payroll_action_id
10250: AND assignment_id = l_assignments(recno);
10247:
10248: DELETE psp_enc_summary_lines
10249: WHERE payroll_action_id = p_payroll_action_id
10250: AND assignment_id = l_assignments(recno);
10251: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Deleted respective lines in psp_enc_summary_lines');
10252:
10253: DELETE psp_enc_lines
10254: WHERE payroll_action_id = p_payroll_action_id
10255: AND assignment_id = l_assignments(recno);
10252:
10253: DELETE psp_enc_lines
10254: WHERE payroll_action_id = p_payroll_action_id
10255: AND assignment_id = l_assignments(recno);
10256: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Deleted respective lines in psp_enc_lines');
10257:
10258: DELETE psp_enc_controls pec
10259: WHERE payroll_action_id = p_payroll_action_id
10260: AND NOT EXISTS (SELECT 1
10260: AND NOT EXISTS (SELECT 1
10261: FROM psp_enc_lines pel
10262: WHERE payroll_action_id = p_payroll_action_id
10263: AND pel.enc_control_id = pec.enc_control_id);
10264: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Deleted respective lines in psp_enc_controls');
10265:
10266: DELETE psp_report_errors
10267: WHERE payroll_action_id = p_payroll_action_id
10268: AND source_id = l_assignments(recno);
10265:
10266: DELETE psp_report_errors
10267: WHERE payroll_action_id = p_payroll_action_id
10268: AND source_id = l_assignments(recno);
10269: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Deleted respective lines in psp_report_errors');
10270:
10271: UPDATE psp_enc_process_assignments
10272: SET assignment_status = 'B'
10273: WHERE payroll_action_id = p_payroll_action_id
10271: UPDATE psp_enc_process_assignments
10272: SET assignment_status = 'B'
10273: WHERE payroll_action_id = p_payroll_action_id
10274: AND assignment_id = l_assignments(recno);
10275: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Updated assignment status to ''Rollback'' in psp_enc_process_assignments');
10276:
10277: UPDATE psp_enc_changed_assignments
10278: SET payroll_action_id = NULL
10279: WHERE payroll_action_id = p_payroll_action_id
10277: UPDATE psp_enc_changed_assignments
10278: SET payroll_action_id = NULL
10279: WHERE payroll_action_id = p_payroll_action_id
10280: AND assignment_id = l_assignments(recno);
10281: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Updated payroll_action_id in psp_enc_changed_assignments');
10282:
10283: INSERT INTO psp_enc_changed_assignments
10284: (request_id, assignment_id, payroll_id, change_type,
10285: processed_flag, reference_id, action_type, change_date)
10288: FROM psp_enc_changed_asg_history pecah
10289: WHERE payroll_action_id = p_payroll_action_id
10290: AND assignment_id = l_assignments(recno)
10291: AND action_type NOT IN ('CR', 'LQ', 'TR');
10292: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Copied change assignment records to psp_enc_changed_assignments');
10293:
10294: DELETE psp_enc_changed_asg_history
10295: WHERE payroll_action_id = p_payroll_action_id
10296: AND assignment_id = l_assignments(recno);
10293:
10294: DELETE psp_enc_changed_asg_history
10295: WHERE payroll_action_id = p_payroll_action_id
10296: AND assignment_id = l_assignments(recno);
10297: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Copied change assignment records to psp_enc_changed_assignments');
10298: END LOOP;
10299:
10300: UPDATE psp_enc_processes
10301: SET process_status = 'B',
10303: WHERE payroll_action_id = p_payroll_action_id
10304: AND NOT EXISTS (SELECT 1
10305: FROM psp_enc_summary_lines
10306: WHERE payroll_action_id = p_payroll_action_id);
10307: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Updated process status to ''Rollback'' in psp_enc_processes');
10308: END IF;
10309:
10310: COMMIT;
10311: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Leaving ROLLBACK_CEL p_payroll_action_id: ' || p_payroll_action_id);
10307: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Updated process status to ''Rollback'' in psp_enc_processes');
10308: END IF;
10309:
10310: COMMIT;
10311: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Leaving ROLLBACK_CEL p_payroll_action_id: ' || p_payroll_action_id);
10312: retcode := fnd_api.g_ret_sts_success;
10313: EXCEPTION
10314: WHEN OTHERS THEN
10315: fnd_file.put_line(fnd_file.log,DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
10314: WHEN OTHERS THEN
10315: fnd_file.put_line(fnd_file.log,DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
10316: fnd_msg_pub.add_exc_msg('PSP_ENC_CREATE_LINES', 'ROLLBACK_CEL: SQLCODE: ' || fnd_number.number_to_canonical(SQLCODE) || ' SQLERRM: ' || SQLERRM);
10317: psp_message_s.print_error(p_mode => FND_FILE.LOG, p_print_header => FND_API.G_TRUE);
10318: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Leaving ROLLBACK_CEL p_payroll_action_id: ' || p_payroll_action_id);
10319: retcode := fnd_api.g_ret_sts_unexp_error;
10320: END rollback_cel;
10321:
10322: PROCEDURE load_sch_hierarchy (p_assignment_id IN NUMBER,
10665: AND ( poa.gl_code_combination_id IS NOT NULL
10666: OR poa.award_id IS NOT NULL)
10667: ORDER BY poa.start_date_active, poa.end_date_active;
10668: BEGIN
10669: hr_utility.trace(fnd_date.date_to_canonical(SYSDATE) || ' Entering LOAD_SCH_HIERARCHY p_assignment_id: ' || fnd_number.number_to_canonical(p_assignment_id) || '
10670: p_element_type_id: ' || fnd_number.number_to_canonical(p_element_type_id) || '
10671: p_business_group_id: ' || fnd_number.number_to_canonical(p_business_group_id) || '
10672: p_set_of_books_id: ' || fnd_number.number_to_canonical(p_set_of_books_id));
10673:
10689: r_gee.attribute5, r_gee.attribute6,
10690: r_gee.attribute7, r_gee.attribute8,
10691: r_gee.attribute9, r_gee.attribute10, r_gee.acct_type;
10692: CLOSE global_element_cur;
10693: hr_utility.trace(fnd_date.date_to_canonical(SYSDATE) || ' r_gee.line_account_id.COUNT: ' || r_gee.line_account_id.COUNT);
10694:
10695: OPEN sch_lines_element_type_cur;
10696: FETCH sch_lines_element_type_cur BULK COLLECT INTO r_et.line_account_id,
10697: r_et.gl_code_combination_id, r_et.project_id,
10705: r_et.attribute5, r_et.attribute6,
10706: r_et.attribute7, r_et.attribute8,
10707: r_et.attribute9, r_et.attribute10, r_et.acct_type;
10708: CLOSE sch_lines_element_type_cur;
10709: hr_utility.trace(fnd_date.date_to_canonical(SYSDATE) || ' r_et.line_account_id.COUNT: ' || r_et.line_account_id.COUNT);
10710:
10711: OPEN sch_lines_element_class_cur;
10712: FETCH sch_lines_element_class_cur BULK COLLECT INTO r_ec.line_account_id,
10713: r_ec.gl_code_combination_id, r_ec.project_id,
10721: r_ec.attribute5, r_ec.attribute6,
10722: r_ec.attribute7, r_ec.attribute8,
10723: r_ec.attribute9, r_ec.attribute10, r_ec.acct_type;
10724: CLOSE sch_lines_element_class_cur;
10725: hr_utility.trace(fnd_date.date_to_canonical(SYSDATE) || ' r_ec.line_account_id.COUNT: ' || r_ec.line_account_id.COUNT);
10726:
10727: OPEN sch_lines_assignment_cur;
10728: FETCH sch_lines_assignment_cur BULK COLLECT INTO r_asg.line_account_id,
10729: r_asg.gl_code_combination_id, r_asg.project_id,
10737: r_asg.attribute5, r_asg.attribute6,
10738: r_asg.attribute7, r_asg.attribute8,
10739: r_asg.attribute9, r_asg.attribute10, r_asg.acct_type;
10740: CLOSE sch_lines_assignment_cur;
10741: hr_utility.trace(fnd_date.date_to_canonical(SYSDATE) || ' r_asg.line_account_id.COUNT: ' || r_asg.line_account_id.COUNT);
10742:
10743: OPEN asg_org_cur;
10744: FETCH asg_org_cur BULK COLLECT INTO r_asg_org.organization_id, r_asg_org.start_date, r_asg_org.end_date;
10745: CLOSE asg_org_cur;
10959: r_gsa.attribute9.DELETE;
10960: r_gsa.attribute10.DELETE;
10961: r_gsa.acct_type.DELETE;
10962: END LOOP;
10963: hr_utility.trace(fnd_date.date_to_canonical(SYSDATE) || ' r_odls.line_account_id.COUNT: ' || r_odls.line_account_id.COUNT);
10964: hr_utility.trace(fnd_date.date_to_canonical(SYSDATE) || ' r_da.line_account_id.COUNT: ' || r_da.line_account_id.COUNT);
10965: hr_utility.trace(fnd_date.date_to_canonical(SYSDATE) || ' r_sa_tmp.line_account_id.COUNT: ' || r_sa_tmp.line_account_id.COUNT);
10966:
10967: IF (r_sa_tmp.line_account_id.COUNT = 0) THEN
10960: r_gsa.attribute10.DELETE;
10961: r_gsa.acct_type.DELETE;
10962: END LOOP;
10963: hr_utility.trace(fnd_date.date_to_canonical(SYSDATE) || ' r_odls.line_account_id.COUNT: ' || r_odls.line_account_id.COUNT);
10964: hr_utility.trace(fnd_date.date_to_canonical(SYSDATE) || ' r_da.line_account_id.COUNT: ' || r_da.line_account_id.COUNT);
10965: hr_utility.trace(fnd_date.date_to_canonical(SYSDATE) || ' r_sa_tmp.line_account_id.COUNT: ' || r_sa_tmp.line_account_id.COUNT);
10966:
10967: IF (r_sa_tmp.line_account_id.COUNT = 0) THEN
10968: OPEN generic_suspense_cur;
10961: r_gsa.acct_type.DELETE;
10962: END LOOP;
10963: hr_utility.trace(fnd_date.date_to_canonical(SYSDATE) || ' r_odls.line_account_id.COUNT: ' || r_odls.line_account_id.COUNT);
10964: hr_utility.trace(fnd_date.date_to_canonical(SYSDATE) || ' r_da.line_account_id.COUNT: ' || r_da.line_account_id.COUNT);
10965: hr_utility.trace(fnd_date.date_to_canonical(SYSDATE) || ' r_sa_tmp.line_account_id.COUNT: ' || r_sa_tmp.line_account_id.COUNT);
10966:
10967: IF (r_sa_tmp.line_account_id.COUNT = 0) THEN
10968: OPEN generic_suspense_cur;
10969: FETCH generic_suspense_cur BULK COLLECT INTO r_sa.line_account_id,
10978: r_sa.attribute5, r_sa.attribute6,
10979: r_sa.attribute7, r_sa.attribute8,
10980: r_sa.attribute9, r_sa.attribute10, r_sa.acct_type;
10981: CLOSE generic_suspense_cur;
10982: hr_utility.trace(fnd_date.date_to_canonical(SYSDATE) || ' r_sa.line_account_id.COUNT: ' || r_sa.line_account_id.COUNT);
10983: ELSE
10984: OPEN generic_suspense_cur;
10985: FETCH generic_suspense_cur BULK COLLECT INTO r_gsa.line_account_id,
10986: r_gsa.gl_code_combination_id, r_gsa.project_id,
10994: r_gsa.attribute5, r_gsa.attribute6,
10995: r_gsa.attribute7, r_gsa.attribute8,
10996: r_gsa.attribute9, r_gsa.attribute10, r_gsa.acct_type;
10997: CLOSE generic_suspense_cur;
10998: hr_utility.trace(fnd_date.date_to_canonical(SYSDATE) || ' r_gsa.line_account_id.COUNT: ' || r_gsa.line_account_id.COUNT);
10999:
11000: g_sa_pointer := 0;
11001: IF (r_sa_tmp.start_date_active(1) >= r_enc_period.r_asg_start_date(1)) THEN
11002: hr_utility.trace(fnd_date.date_to_canonical(SYSDATE) ||
10998: hr_utility.trace(fnd_date.date_to_canonical(SYSDATE) || ' r_gsa.line_account_id.COUNT: ' || r_gsa.line_account_id.COUNT);
10999:
11000: g_sa_pointer := 0;
11001: IF (r_sa_tmp.start_date_active(1) >= r_enc_period.r_asg_start_date(1)) THEN
11002: hr_utility.trace(fnd_date.date_to_canonical(SYSDATE) ||
11003: ' r_sa_tmp.start_date_active(1): ' || r_sa_tmp.start_date_active(1) ||
11004: ' r_enc_period.r_asg_start_date(1): ' || r_enc_period.r_asg_start_date(1));
11005: FOR gsa_rec_no IN 1..r_gsa.line_account_id.COUNT
11006: LOOP
11031: r_sa.attribute9(g_sa_pointer) := r_gsa.attribute9(gsa_rec_no);
11032: r_sa.attribute10(g_sa_pointer) := r_gsa.attribute10(gsa_rec_no);
11033: r_sa.acct_type(g_sa_pointer) := r_gsa.acct_type(gsa_rec_no);
11034: END LOOP;
11035: hr_utility.trace(fnd_date.date_to_canonical(SYSDATE) || ' Identified Generic Suspense Lines for suspense gap before the first suspense line');
11036: END IF;
11037: g_sa_pointer := g_sa_pointer + 1;
11038: r_sa.line_account_id(g_sa_pointer) := r_sa_tmp.line_account_id(1);
11039: r_sa.gl_code_combination_id(g_sa_pointer) := r_sa_tmp.gl_code_combination_id(1);
11058: r_sa.attribute8(g_sa_pointer) := r_sa_tmp.attribute8(1);
11059: r_sa.attribute9(g_sa_pointer) := r_sa_tmp.attribute9(1);
11060: r_sa.attribute10(g_sa_pointer) := r_sa_tmp.attribute10(1);
11061: r_sa.acct_type(g_sa_pointer) := r_sa_tmp.acct_type(1);
11062: hr_utility.trace(fnd_date.date_to_canonical(SYSDATE) || ' Stamped first suspense line');
11063:
11064: IF (r_sa_tmp.line_account_id.COUNT > 1) THEN
11065: FOR sa_recno IN 2..r_sa_tmp.line_account_id.COUNT
11066: LOOP
11186: r_sa_tmp.attribute9.DELETE;
11187: r_sa_tmp.attribute10.DELETE;
11188: r_sa_tmp.acct_type.DELETE;
11189: END IF;
11190: hr_utility.trace(fnd_date.date_to_canonical(SYSDATE) || ' r_sa.line_account_id.COUNT: ' || r_sa.line_account_id.COUNT);
11191: END IF;
11192:
11193: r_gsa.line_account_id.DELETE;
11194: r_gsa.gl_code_combination_id.DELETE;
11214: r_gsa.attribute9.DELETE;
11215: r_gsa.attribute10.DELETE;
11216: r_gsa.acct_type.DELETE;
11217:
11218: g_pateo_end_date := fnd_date.canonical_to_date('1800/01/01 00:00:00');
11219:
11220: hr_utility.trace('
11221: Global Element Hierarchy');
11222: hr_utility.trace(LPAD('Element Account Id', 18, ' ') || ' ' ||
11553: END IF;
11554: g_pateo_end_date := GREATEST(g_pateo_end_date, NVL(r_sa.poeta_end_date(recno), g_pateo_end_date));
11555: END LOOP;
11556:
11557: IF ((g_pateo_end_date = fnd_date.canonical_to_date('1800/01/01 00:00:00')) OR
11558: (g_pateo_end_date < g_enc_org_end_date)) THEN
11559: g_pateo_end_date := g_enc_org_end_date;
11560: END IF;
11561: hr_utility.trace(fnd_date.date_to_canonical(SYSDATE) || ' g_pateo_end_date: ' || TO_CHAR(g_pateo_end_date, 'DD-MON-RRRR'));
11557: IF ((g_pateo_end_date = fnd_date.canonical_to_date('1800/01/01 00:00:00')) OR
11558: (g_pateo_end_date < g_enc_org_end_date)) THEN
11559: g_pateo_end_date := g_enc_org_end_date;
11560: END IF;
11561: hr_utility.trace(fnd_date.date_to_canonical(SYSDATE) || ' g_pateo_end_date: ' || TO_CHAR(g_pateo_end_date, 'DD-MON-RRRR'));
11562:
11563: hr_utility.trace(fnd_date.date_to_canonical(SYSDATE) || ' Leaving LOAD_SCH_HIERARCHY');
11564: p_return_status := fnd_api.g_ret_sts_success;
11565: EXCEPTION
11559: g_pateo_end_date := g_enc_org_end_date;
11560: END IF;
11561: hr_utility.trace(fnd_date.date_to_canonical(SYSDATE) || ' g_pateo_end_date: ' || TO_CHAR(g_pateo_end_date, 'DD-MON-RRRR'));
11562:
11563: hr_utility.trace(fnd_date.date_to_canonical(SYSDATE) || ' Leaving LOAD_SCH_HIERARCHY');
11564: p_return_status := fnd_api.g_ret_sts_success;
11565: EXCEPTION
11566: WHEN OTHERS THEN
11567: fnd_file.put_line(fnd_file.log,DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
11570: END IF;
11571: g_error_api_path := SUBSTR(' LOAD_SCH_HIERARCHY:'||g_error_api_path,1,230);
11572: fnd_msg_pub.add_exc_msg('PSP_ENC_CREATE_LINES', ' LOAD_SCH_HIERARCHY');
11573: p_return_status := fnd_api.g_ret_sts_unexp_error;
11574: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' ' || fnd_number.number_to_canonical(l_proc_step) || ': LOAD_SCH_HIERARCHY');
11575: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Leaving LOAD_SCH_HIERARCHY');
11576: END load_sch_hierarchy;
11577:
11578: PROCEDURE add_cel_warnings(p_start_date IN DATE DEFAULT NULL,
11571: g_error_api_path := SUBSTR(' LOAD_SCH_HIERARCHY:'||g_error_api_path,1,230);
11572: fnd_msg_pub.add_exc_msg('PSP_ENC_CREATE_LINES', ' LOAD_SCH_HIERARCHY');
11573: p_return_status := fnd_api.g_ret_sts_unexp_error;
11574: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' ' || fnd_number.number_to_canonical(l_proc_step) || ': LOAD_SCH_HIERARCHY');
11575: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Leaving LOAD_SCH_HIERARCHY');
11576: END load_sch_hierarchy;
11577:
11578: PROCEDURE add_cel_warnings(p_start_date IN DATE DEFAULT NULL,
11579: p_end_date IN DATE DEFAULT NULL,
11920: t_enc_lines r_enc_rec;
11921: t_enc_nlines r_enc_rec;
11922: l_nlines_counter NUMBER(15);
11923: BEGIN
11924: hr_utility.trace(fnd_date.date_to_canonical(SYSDATE) || ' Entering UPDATE_HIERARCHY_DATES');
11925: OPEN hierarchy_dates_cur;
11926: FETCH hierarchy_dates_cur BULK COLLECT INTO t_enc_lines.element_type_id, t_enc_lines.hierarchy_code,
11927: t_enc_lines.gl_ccid, t_enc_lines.project_id, t_enc_lines.task_id,
11928: t_enc_lines.award_id, t_enc_lines.exp_org_id, t_enc_lines.exp_type,
12131: AND enc_start_date <= t_enc_nlines.enc_end_date(recno)
12132: AND enc_end_date >= t_enc_nlines.enc_start_date(recno);
12133: END IF;
12134: p_return_status := fnd_api.g_ret_sts_success;
12135: hr_utility.trace(fnd_date.date_to_canonical(SYSDATE) || ' Leaving UPDATE_HIERARCHY_DATES');
12136: EXCEPTION
12137: WHEN OTHERS THEN
12138: fnd_file.put_line(fnd_file.log,DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
12139: IF (g_error_message IS NULL) THEN
12141: END IF;
12142: g_error_api_path := SUBSTR(' UPDATE_HIERARCHY_DATES:'||g_error_api_path,1,230);
12143: fnd_msg_pub.add_exc_msg('PSP_ENC_CREATE_LINES', ' UPDATE_HIERARCHY_DATES');
12144: p_return_status := fnd_api.g_ret_sts_unexp_error;
12145: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Leaving UPDATE_HIERARCHY_DATES');
12146: END update_hierarchy_dates;
12147:
12148: PROCEDURE clear_sch_hierarchy IS
12149: BEGIN