[Home] [Help]
PACKAGE BODY: APPS.PSP_ENC_PRE_PROCESS
Source
1 PACKAGE BODY psp_enc_pre_process AS
2 /* $Header: PSPENPPB.pls 120.4 2008/04/16 13:38:53 amakrish noship $ */
3
4 PROCEDURE update_global_earning_elements
5 (p_pre_process_mode IN VARCHAR2,
6 p_payroll_id IN NUMBER,
7 p_business_group_id IN NUMBER,
8 p_set_of_books_id IN NUMBER,
9 p_max_pay_date IN DATE,
10 p_return_status OUT NOCOPY VARCHAR2);
11
12 PROCEDURE update_schedules (p_pre_process_mode IN VARCHAR2,
13 p_payroll_id IN NUMBER,
14 p_business_group_id IN NUMBER,
15 p_set_of_books_id IN NUMBER,
16 p_max_pay_date IN DATE,
17 p_return_status OUT NOCOPY VARCHAR2);
18
19 PROCEDURE update_default_susp_accounts
20 (p_pre_process_mode IN VARCHAR2,
21 p_payroll_id IN NUMBER,
22 p_business_group_id IN NUMBER,
23 p_set_of_books_id IN NUMBER,
24 p_max_pay_date IN DATE,
25 p_return_status OUT NOCOPY VARCHAR2);
26
27
28 PROCEDURE update_default_labor_schedules
29 (p_pre_process_mode IN VARCHAR2,
30 p_payroll_id IN NUMBER,
31 p_business_group_id IN NUMBER,
32 p_set_of_books_id IN NUMBER,
33 p_max_pay_date IN DATE,
34 p_return_status OUT NOCOPY VARCHAR2);
35
36 /***** Commented as part of bug fix 33957169
37 PROCEDURE validate_poeta (p_project_id IN NUMBER,
38 p_task_id IN NUMBER,
39 p_award_id IN NUMBER,
40 p_expenditure_type IN VARCHAR2,
41 p_expenditure_organization_id IN NUMBER,
42 p_payroll_id IN NUMBER,
43 p_start_date OUT NOCOPY DATE,
44 p_end_date OUT NOCOPY DATE,
45 p_return_status OUT NOCOPY VARCHAR2);
46 End of comment for bug fix 3957169 *****/
47
48 PROCEDURE insert_changed_assignments
49 (p_change_type IN VARCHAR2,
50 p_reference_id IN NUMBER DEFAULT NULL,
51 p_action_type IN VARCHAR2 DEFAULT NULL,
52 p_return_status OUT NOCOPY VARCHAR2);
53
54 PROCEDURE validate_transaction_controls
55 (p_project_id IN NUMBER,
56 p_payroll_id IN NUMBER,
57 p_return_status OUT NOCOPY VARCHAR2);
58
59 /* Global Variables */
60 TYPE v_line_id IS TABLE OF NUMBER(15) INDEX BY BINARY_INTEGER;
61 TYPE v_project_id IS TABLE OF NUMBER(15) INDEX BY BINARY_INTEGER;
62 TYPE v_task_id IS TABLE OF NUMBER(15) INDEX BY BINARY_INTEGER;
63 TYPE v_exp_org IS TABLE OF NUMBER(15) INDEX BY BINARY_INTEGER;
64 TYPE v_exp_type IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
65 TYPE v_award_id IS TABLE OF NUMBER(15) INDEX BY BINARY_INTEGER;
66 TYPE v_start_dt IS TABLE OF DATE INDEX BY BINARY_INTEGER;
67 TYPE v_end_dt IS TABLE OF DATE INDEX BY BINARY_INTEGER;
68 TYPE v_payroll_id IS TABLE OF NUMBER(9) INDEX BY BINARY_INTEGER;
69 TYPE v_assignment_id IS TABLE OF NUMBER(10) INDEX BY BINARY_INTEGER;
70
71 g_request_id NUMBER DEFAULT fnd_global.conc_request_id;
72 g_error_api_path VARCHAR2(230);
73
74 g_total_num_rec NUMBER DEFAULT 0;
75
76 g_final_start_date DATE;
77 g_final_end_date DATE;
78
79 g_package_name VARCHAR2(31) DEFAULT 'PSP_ENC_PRE_PROCESS.';
80
81 TYPE asg_id_array is RECORD (r_asg_id v_assignment_id,
82 r_payroll_id v_payroll_id);
83
84 r_asg_id_array asg_id_array;
85
86 PROCEDURE poeta_pre_process (p_pre_process_mode IN VARCHAR2,
87 p_payroll_id IN NUMBER,
88 p_business_group_id IN NUMBER,
89 p_set_of_books_id IN NUMBER,
90 p_return_status OUT NOCOPY VARCHAR2) IS
91 PRAGMA AUTONOMOUS_TRANSACTION;
92 l_operating_unit VARCHAR2(30);
93 l_pa_gms_install_option VARCHAR2(30);
94 l_max_pay_date DATE;
95
96 l_business_group_id NUMBER(15);
97 l_set_of_books_id NUMBER(15);
98
99 -- Introduced the following for bug fix 3434626
100 l_proc_name VARCHAR2(61) DEFAULT g_package_name || 'POETA_PRE_PROCESS';
101 l_proc_step NUMBER(20, 10) DEFAULT 0;
102 -- End of changes for bug fix 3434626
103
104 /* Cursor for selecting maximum payroll date of the payroll */
105 CURSOR payroll_date_cur IS
106 SELECT max(date_earned) from pay_payroll_actions
107 WHERE payroll_id = p_payroll_id
108 AND action_type = 'R'
109 AND action_status = 'C';
110
111 /* This cursor is declared to pass a minimum of time period date,
112 if no payroll is processed for that payroll */
113 CURSOR min_start_dt_cur IS
114 SELECT min(start_date)
115 FROM per_time_periods
116 WHERE payroll_id = p_payroll_id;
117
118 BEGIN
119 -- Introduced the following for bug fix 3434626
120 hr_utility.trace('Entering ' || l_proc_name);
121 hr_utility.trace('p_pre_process_mode: ' || p_pre_process_mode ||
122 ' p_payroll_id: ' || fnd_number.number_to_canonical(p_payroll_id) ||
123 ' p_business_group_id: ' || fnd_number.number_to_canonical(p_business_group_id) ||
124 ' p_set_of_books_id: ' || fnd_number.number_to_canonical(p_set_of_books_id));
125
126 l_proc_step := 10;
127 -- End of Changes for bug fix 3434626
128 /* Validation for GMS install option */
129 psp_general.multiorg_client_info (l_set_of_books_id,
130 l_business_group_id,
131 l_operating_unit,
132 l_pa_gms_install_option);
133
134 IF l_pa_gms_install_option <>'PA_GMS' THEN
135 p_return_status := fnd_api.g_ret_sts_success;
136 RETURN;
137 END IF;
138
139 -- Introduced the following for bug fix 3434626
140 hr_utility.trace('l_business_group_id: ' || fnd_number.number_to_canonical(l_business_group_id) ||
141 ' l_set_of_books_id: ' || fnd_number.number_to_canonical(l_set_of_books_id) ||
142 ' l_operating_unit: ' || fnd_number.number_to_canonical(l_operating_unit) ||
143 ' l_pa_gms_install_option: ' || l_pa_gms_install_option);
144
145 l_proc_step := 20;
146 -- End of changes for bug fix 3434626
147
148 -- Code chages for bug 4203036
149 -- To delete all the unwanted records in poeta pre-process
150 delete from psp_enc_changed_assignments a
151 where exists
152 (select 1 from per_all_assignments_f b
153 where b.assignment_id = a.assignment_id
154 and b.effective_end_date = to_date('31-12-4712','DD-MM-RRRR'))
155 and a.chk_asg_end_date_flag = 'Y';
156 -- End of code chages 4203036
157
158 OPEN payroll_date_cur;
159 FETCH payroll_date_cur into l_max_pay_date;
160 IF (l_max_pay_date IS NULL) THEN
161 OPEN min_start_dt_cur;
162 FETCH min_start_dt_cur into l_max_pay_date;
163 CLOSE min_start_dt_cur;
164 END IF;
165 CLOSE payroll_date_cur;
166
167
168
169 -- Introduced the following for bug fix 3434626
170 hr_utility.trace('l_max_pay_date: ' || fnd_date.date_to_canonical(l_max_pay_date));
171
172 l_proc_step := 30;
173 -- End of bug fix 3434626
174
175 update_global_earning_elements (p_pre_process_mode => p_pre_process_mode,
176 p_payroll_id => p_payroll_id,
177 p_business_group_id => p_business_group_id,
178 p_set_of_books_id => p_set_of_books_id,
179 p_max_pay_date => l_max_pay_date,
180 p_return_status => p_return_status);
181
182 IF p_return_status <> fnd_api.g_ret_sts_success THEN
183 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
184 END IF;
185
186 l_proc_step := 40; -- Introduced for bug fix 3434626
187
188 update_schedules (p_pre_process_mode => p_pre_process_mode,
189 p_payroll_id => p_payroll_id,
190 p_business_group_id => p_business_group_id,
191 p_set_of_books_id => p_set_of_books_id,
192 p_max_pay_date => l_max_pay_date,
193 p_return_status => p_return_status);
194
195 IF p_return_status <> fnd_api.g_ret_sts_success THEN
196 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
197 END IF;
198
199 l_proc_step := 50; -- Introduced for bug fix 3434626
200
201 update_default_labor_schedules (p_pre_process_mode => p_pre_process_mode,
202 p_payroll_id => p_payroll_id,
203 p_business_group_id => p_business_group_id,
204 p_set_of_books_id => p_set_of_books_id,
205 p_max_pay_date => l_max_pay_date,
206 p_return_status => p_return_status);
207
208 IF p_return_status <> fnd_api.g_ret_sts_success THEN
209 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
210 END IF;
211
212 l_proc_step := 60; -- Introduced for bug fix 3434626
213
214 update_default_susp_accounts (p_pre_process_mode => p_pre_process_mode,
215 p_payroll_id => p_payroll_id,
216 p_business_group_id => p_business_group_id,
217 p_set_of_books_id => p_set_of_books_id,
218 p_max_pay_date => l_max_pay_date,
219 p_return_status => p_return_status);
220
221 IF p_return_status <> fnd_api.g_ret_sts_success THEN
222 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
223 END IF;
224
225 l_proc_step := 70; -- Introduced for bug fix 3434626
226
227 COMMIT;
228 p_return_status := fnd_api.g_ret_sts_success;
229
230
231 -- Introduced the following for bug fix 3434626
232 hr_utility.trace('Leaving ' || l_proc_name);
233 -- End of bug fix 3434626
234 EXCEPTION
235 WHEN OTHERS THEN
236 g_error_api_path := SUBSTR(g_error_api_path,1,30);
237 fnd_msg_pub.add_exc_msg('PSP_ENC_PRE_PROCESS',g_error_api_path);
238 psp_message_s.print_error(p_mode=>FND_FILE.LOG,
239 p_print_header=>FND_API.G_TRUE);
240 -- bug fix 2597666
241 p_return_status := fnd_api.g_ret_sts_unexp_error;
242 -- Introduced the following for bug fix 3434626
243 hr_utility.trace(fnd_number.number_to_canonical(l_proc_step) || ': ' || l_proc_name);
244 hr_utility.trace('Leaving ' || l_proc_name);
245 -- End of changed for bug fix 3434626
246 END poeta_pre_process;
247
248 /****************************************************************************************************
249 Procedure Name: UPDATE_GLOBAL_EARNING_ELEMENTS
250 Purpose: This procedure is called in the main procedure and used for updating poeta
251 dates in psp_element_type_accounts for poeta CI It inserts assignments in
252 psp_enc_changed_assignments if the poeta dates are different from previous
253 poeta dates and assignments exists in psp_enc_lines_history table.
254 ****************************************************************************************************/
255
256 PROCEDURE update_global_earning_elements (p_pre_process_mode IN VARCHAR2,
257 p_payroll_id IN NUMBER,
258 p_business_group_id IN NUMBER,
259 p_set_of_books_id IN NUMBER,
260 p_max_pay_date IN DATE,
261 p_return_status OUT NOCOPY VARCHAR2)
262 IS
263
264 TYPE global_lines_rec is RECORD (
265 r_global_line_id v_line_id,
266 r_project_id v_project_id,
267 r_task_id v_task_id,
268 r_expenditure_organization_id v_exp_org,
269 r_expenditure_type v_exp_type,
270 r_award_id v_award_id,
271 r_start_date_active v_start_dt,
272 r_end_date_active v_end_dt,
273 r_poeta_start_date v_start_dt,
274 r_poeta_end_date v_end_dt);
275
276 r_global_control_rec global_lines_rec;
277
278 l_prev_project_id NUMBER(15) DEFAULT -1;
279 l_prev_task_id NUMBER(15) DEFAULT -1;
280 l_prev_award_id NUMBER(15) DEFAULT -1;
281 l_prev_exp_organization_id NUMBER(15) DEFAULT -1;
282 l_prev_expenditure_type VARCHAR2(30) DEFAULT '-1';
283 l_prev_tx_project_id NUMBER(15) DEFAULT -1;
284
285 -- Introduced for bug fix 3434626
286 l_proc_name VARCHAR2(61) DEFAULT g_package_name || 'UPDATE_GLOBAL_EARNING_ELEMENTS';
287 l_proc_step NUMBER(20, 10) DEFAULT 0;
288 -- End of changes for bug fix 3434626
289
290 CURSOR element_account_cur IS
291 SELECT peta.element_account_id,
292 peta.project_id,
293 peta.task_id,
294 peta.expenditure_organization_id,
295 peta.expenditure_type,
296 peta.award_id,
297 peta.start_date_active,
298 peta.end_date_active,
299 NVL(peta.poeta_start_date, TO_DATE('01-01-1800', 'DD-MM-YYYY')) poeta_start_date,
300 NVL(peta.poeta_end_date,TO_DATE('31-12-4712', 'DD-MM-YYYY')) poeta_end_date
301 FROM psp_element_type_accounts peta
302 WHERE peta.gl_code_combination_id is NULL
303 AND peta.end_date_active >= p_max_pay_date
304 AND peta.business_group_id = p_business_group_id
305 AND peta.set_of_books_id = p_set_of_books_id
306 ORDER BY 2,3,4,5,6;
307
308 CURSOR assignment_payroll_cur(j number) IS
309 SELECT DISTINCT pelh.assignment_id,
310 pelh.payroll_id
311 FROM psp_enc_lines_history pelh
312 WHERE pelh.element_account_id = r_global_control_rec.r_global_line_id (j)
313 AND pelh.suspense_org_account_id is NULL
314 AND pelh.change_flag = 'N';
315
316 BEGIN
317 -- Introduced the following for bug fix 3434626
318 hr_utility.trace('Entering ' || l_proc_name);
319 hr_utility.trace('p_pre_process_mode: ' || p_pre_process_mode ||
320 ' p_payroll_id: ' || fnd_number.number_to_canonical(p_payroll_id) ||
321 ' p_business_group_id: ' || fnd_number.number_to_canonical(p_business_group_id) ||
322 ' p_set_of_books_id: ' || fnd_number.number_to_canonical(p_set_of_books_id) ||
323 ' p_max_pay_date: ' || fnd_date.date_to_canonical(p_max_pay_date));
324
325 l_proc_step := 10; -- Introduced for bug fix 3434626
326 -- End of bug fix 3434626
327
328 /* Opening element_account_cur to fetch poeta for element_type_accounts */
329 OPEN element_account_cur;
330 FETCH element_account_cur BULK COLLECT INTO
331 r_global_control_rec.r_global_line_id,
332 r_global_control_rec.r_project_id,
333 r_global_control_rec.r_task_id,
334 r_global_control_rec.r_expenditure_organization_id,
335 r_global_control_rec.r_expenditure_type,
336 r_global_control_rec.r_award_id,
337 r_global_control_rec.r_start_date_active,
338 r_global_control_rec.r_end_date_active,
339 r_global_control_rec.r_poeta_start_date,
340 r_global_control_rec.r_poeta_end_date;
341 CLOSE element_account_cur;
342
343 g_total_num_rec := r_global_control_rec.r_global_line_id.count;
344
345 FOR i IN 1.. g_total_num_rec
346 LOOP
347 -- Introduced the following for bug fix 3434626
348 hr_utility.trace('i: ' || fnd_number.number_to_canonical(i) ||
349 ' r_global_control_rec.r_global_line_id: ' || fnd_number.number_to_canonical(r_global_control_rec.r_global_line_id(i)) ||
350 ' r_global_control_rec.r_project_id(i): ' || fnd_number.number_to_canonical(r_global_control_rec.r_project_id(i)) ||
351 ' r_global_control_rec.r_award_id(i): ' || fnd_number.number_to_canonical(r_global_control_rec.r_award_id(i)) ||
352 ' r_global_control_rec.r_task_id(i): ' || fnd_number.number_to_canonical(r_global_control_rec.r_task_id(i)) ||
353 ' r_global_control_rec.r_expenditure_organization_id(i): ' || fnd_number.number_to_canonical(r_global_control_rec.r_expenditure_organization_id(i)) ||
354 ' r_global_control_rec.r_poeta_start_date(i): ' || fnd_date.date_to_canonical(r_global_control_rec.r_poeta_start_date(i)) ||
355 ' r_global_control_rec.r_poeta_end_date(i): ' || fnd_date.date_to_canonical(r_global_control_rec.r_poeta_end_date(i)) ||
356 ' r_global_control_rec.r_expenditure_type(i): ' || r_global_control_rec.r_expenditure_type(i) ||
357 ' l_prev_project_id: ' || fnd_number.number_to_canonical(l_prev_project_id) ||
358 ' l_prev_award_id: ' || fnd_number.number_to_canonical(l_prev_award_id) ||
359 ' l_prev_task_id: ' || fnd_number.number_to_canonical(l_prev_task_id) ||
360 ' l_prev_exp_organization_id: ' || fnd_number.number_to_canonical(l_prev_exp_organization_id) ||
361 ' l_prev_expenditure_type: ' || l_prev_expenditure_type);
362
363 l_proc_step := 20;
364 -- End of bug fix 3434626
365
366 IF ((r_global_control_rec.r_project_id(i) <> l_prev_project_id) OR
367 (r_global_control_rec.r_expenditure_organization_id(i) <>
368 l_prev_exp_organization_id) OR
369 (r_global_control_rec.r_task_id(i) <> l_prev_task_id) OR
370 (r_global_control_rec.r_award_id(i) <> l_prev_award_id) OR
371 (r_global_control_rec.r_expenditure_type(i) <> l_prev_expenditure_type)) THEN
372 validate_poeta (p_project_id => r_global_control_rec.r_project_id(i),
373 p_task_id => r_global_control_rec.r_task_id(i),
374 p_award_id => r_global_control_rec.r_award_id(i),
375 p_expenditure_type => r_global_control_rec.r_expenditure_type(i),
376 p_expenditure_organization_id => r_global_control_rec.r_expenditure_organization_id(i),
377 p_payroll_id => p_payroll_id,
378 p_start_date => g_final_start_date,
379 p_end_date => g_final_end_date,
380 p_return_status => p_return_status);
381 IF p_return_status <> fnd_api.g_ret_sts_success THEN
382 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
383 END IF;
384
385 -- Introduced for bug fix 3434626
386 hr_utility.trace('l_poeta_start_date: ' || fnd_date.date_to_canonical(g_final_start_date) ||
387 ' l_poeta_end_date: ' || fnd_date.date_to_canonical(g_final_end_date));
388 l_proc_step := 30;
389 -- End of bug fix 3434626
390
391 l_prev_project_id := r_global_control_rec.r_project_id(i);
392 l_prev_task_id := r_global_control_rec.r_task_id(i);
393 l_prev_award_id := r_global_control_rec.r_award_id(i);
394 l_prev_exp_organization_id :=
395 r_global_control_rec.r_expenditure_organization_id(i);
396 l_prev_expenditure_type := r_global_control_rec.r_expenditure_type(i);
397 END IF;
398 /* Verifying whether current poeta dates are different from previous poeta dates */
399 IF (r_global_control_rec.r_poeta_start_date(i) <> g_final_start_date)
400 OR (r_global_control_rec.r_poeta_end_date(i) <> g_final_end_date) THEN
401 r_global_control_rec.r_poeta_start_date(i) := g_final_start_date;
402 r_global_control_rec.r_poeta_end_date(i) := g_final_end_date;
403
404 IF (p_pre_process_mode = 'R') THEN
405 /* Opening the cursor to fetch payroll_id and assignment_id into
406 respective payroll_id and assignment_id arrays */
407 OPEN assignment_payroll_cur(i);
408 FETCH assignment_payroll_cur BULK COLLECT INTO
409 r_asg_id_array.r_asg_id, r_asg_id_array.r_payroll_id;
410 CLOSE assignment_payroll_cur;
411
412 -- Introduced for bug fix 3434626
413 hr_utility.trace('r_asg_id_array.r_asg_id.count: ' || fnd_number.number_to_canonical(r_asg_id_array.r_asg_id.count));
414 l_proc_step := 40;
415 -- End of bug fix 3434626
416
417 /* Insert assignments (exists in psp_enc_lines_history and poeta dates
418 are changed) into psp_enc_changed_assignments */
419 IF r_asg_id_array.r_asg_id.count<>0 THEN
420 insert_changed_assignments (p_change_type => 'PT',
421 p_return_status => p_return_status);
422 IF (p_return_status <> fnd_api.g_ret_sts_success) THEN
423 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
424 END IF;
425 END IF;
426 END IF;
427 ELSE
428 l_proc_step := 50; -- Introduced for bug fix 3434626
429 IF (p_pre_process_mode = 'R') THEN
430 IF r_global_control_rec.r_project_id(i) <> l_prev_tx_project_id THEN
431 validate_transaction_controls
432 (p_project_id => r_global_control_rec.r_project_id(i),
433 p_payroll_id => p_payroll_id,
434 p_return_status => p_return_status);
435 IF (p_return_status <> fnd_api.g_ret_sts_success) THEN
436 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
437 END IF;
438 l_prev_tx_project_id := r_global_control_rec.r_project_id(i);
439 END IF;
440 END IF;
441
442 END IF;
443 END LOOP;
444
445 l_proc_step := 60; -- Introduced for bug fix 3434626
446
447 /* Updating poeta dates in psp_element_type_accounts */
448 FORALL i in 1 .. g_total_num_rec
449 UPDATE psp_element_type_accounts
450 SET poeta_start_date = r_global_control_rec.r_poeta_start_date(i),
451 poeta_end_date = r_global_control_rec.r_poeta_end_date(i)
452 WHERE element_account_id = r_global_control_rec.r_global_line_id(i);
453
454 p_return_status := fnd_api.g_ret_sts_success;
455
456 -- Introduced for bug fix 3434626
457 hr_utility.trace('Leaving ' || l_proc_name);
458 -- End of bug fix 3434626
459 EXCEPTION
460 WHEN OTHERS THEN
461 g_error_api_path := SUBSTR('UPDT_GLBL:'||g_error_api_path,1,30);
462 -- bug fix 2597666
463 p_return_status := fnd_api.g_ret_sts_unexp_error;
464 -- Introduced for bug fix 3434626
465 hr_utility.trace(fnd_number.number_to_canonical(l_proc_step) || ': ' || l_proc_name);
466 hr_utility.trace('Leaving ' || l_proc_name);
467 -- End of bug fix 3434626
468 END update_global_earning_elements;
469
470 /****************************************************************************************************
471 Procedure Name: UPDATE_SCHEDULES
472 Purpose: This procedure is called in the main procedure and used for updating poeta
473 dates psp_schedule_lines for poeta CI. It inserts assignments in
474 psp_enc_changed_assignments if the poeta dates are different from previous
475 poeta dates and assignments exist in psp_enc_lines_history table.
476 ****************************************************************************************************/
477
478 PROCEDURE update_schedules (p_pre_process_mode IN VARCHAR2,
479 p_payroll_id IN NUMBER,
480 p_business_group_id IN NUMBER,
481 p_set_of_books_id IN NUMBER,
482 p_max_pay_date IN DATE,
483 p_return_status OUT NOCOPY VARCHAR2)
484 IS
485
486 TYPE schedule_lines_rec is RECORD (
487 r_assignment_id v_assignment_id,
488 r_payroll_id v_payroll_id,
489 r_schedule_line_id v_line_id,
490 r_project_id v_project_id,
491 r_task_id v_task_id,
492 r_expenditure_organization_id v_exp_org,
493 r_expenditure_type v_exp_type,
494 r_award_id v_award_id,
495 r_schedule_begin_date v_start_dt,
496 r_schedule_end_date v_end_dt,
497 r_poeta_start_date v_start_dt,
498 r_poeta_end_date v_end_dt);
499
500 r_schedule_control_rec schedule_lines_rec;
501
502
503 l_prev_project_id NUMBER(15) DEFAULT -1;
504 l_prev_task_id NUMBER(15) DEFAULT -1;
505 l_prev_award_id NUMBER(15) DEFAULT -1;
506 l_prev_exp_organization_id NUMBER(15) DEFAULT -1;
507 l_prev_expenditure_type VARCHAR2(30) DEFAULT '-1';
508 l_prev_tx_project_id NUMBER(15) DEFAULT -1;
509
510 j NUMBER DEFAULT 1;
511
512 -- Introduced for bug fix 3434626
513 l_proc_name VARCHAR2(61) DEFAULT g_package_name || 'UPDATE_SCHEDULES';
514 l_proc_step NUMBER(20, 10) DEFAULT 0;
515 -- End of changes for bug fix 3434626
516
517 CURSOR schedule_line_cur IS
518 SELECT psh.assignment_id,
519 paf.payroll_id,
520 psl.schedule_line_id,
521 psl.project_id,
522 psl.task_id,
523 psl.expenditure_organization_id,
524 psl.expenditure_type,
525 psl.award_id,
526 psl.schedule_begin_date,
527 psl.schedule_end_date,
528 NVL(psl.poeta_start_date,TO_DATE('01-01-1800', 'DD-MM-YYYY')) poeta_start_date,
529 NVL(psl.poeta_end_date,TO_DATE('31-12-4712', 'DD-MM-YYYY')) poeta_start_date
530 FROM psp_schedule_hierarchy psh,
531 psp_schedule_lines psl ,
532 per_assignments_f paf
533 WHERE psl.business_group_id = p_business_group_id
534 AND psl.set_of_books_id = p_set_of_books_id
535 AND psl.schedule_hierarchy_id = psh.schedule_hierarchy_id
536 AND psl.schedule_end_date >= p_max_pay_date
537 AND psl.gl_code_combination_id IS NULL
538 AND psh.assignment_id = paf.assignment_id
539 AND psl.schedule_begin_date <= paf.effective_end_date
540 AND psl.schedule_end_date >= paf.effective_start_date
541 AND paf.period_of_service_id IS NOT NULL
542 and paf.payroll_id = p_payroll_id --bug fix 2597666 Modified NOT NULL check to current payroll check for bug fix 3099540
543 AND paf.effective_end_date >= p_max_pay_date -- Introduced for bug fix 3099540 Corrected for bug fix 3434626
544 ORDER BY 4,5,6,7,8;
545
546 BEGIN
547 -- Introduced the following for bug fix 3434626
548 hr_utility.trace('Entering ' || l_proc_name);
549 hr_utility.trace('p_pre_process_mode: ' || p_pre_process_mode ||
550 ' p_payroll_id: ' || fnd_number.number_to_canonical(p_payroll_id) ||
551 ' p_business_group_id: ' || fnd_number.number_to_canonical(p_business_group_id) ||
552 ' p_set_of_books_id: ' || fnd_number.number_to_canonical(p_set_of_books_id) ||
553 ' p_max_pay_date: ' || fnd_date.date_to_canonical(p_max_pay_date));
554
555 l_proc_step := 10; -- Introduced for bug fix 3434626
556 -- End of bug fix 3434626
557
558 /* Opening schedule_line_cur to fetch poeta for schedule_lines */
559 OPEN schedule_line_cur;
560 FETCH schedule_line_cur BULK COLLECT INTO
561 r_schedule_control_rec.r_assignment_id,
562 r_schedule_control_rec.r_payroll_id,
563 r_schedule_control_rec.r_schedule_line_id,
564 r_schedule_control_rec.r_project_id,
565 r_schedule_control_rec.r_task_id,
566 r_schedule_control_rec.r_expenditure_organization_id,
567 r_schedule_control_rec.r_expenditure_type,
568 r_schedule_control_rec.r_award_id,
569 r_schedule_control_rec.r_schedule_begin_date,
570 r_schedule_control_rec.r_schedule_end_date,
571 r_schedule_control_rec.r_poeta_start_date,
572 r_schedule_control_rec.r_poeta_end_date;
573 CLOSE schedule_line_cur;
574
575 g_total_num_rec := r_schedule_control_rec.r_schedule_line_id.count;
576
577 FOR i IN 1..g_total_num_rec
578 LOOP
579 -- Introduced the following for bug fix 3434626
580 hr_utility.trace('i: ' || fnd_number.number_to_canonical(i) ||
581 ' r_schedule_control_rec.r_schedule_line_id: ' || fnd_number.number_to_canonical(r_schedule_control_rec.r_schedule_line_id(i)) ||
582 ' r_schedule_control_rec.r_project_id(i): ' || fnd_number.number_to_canonical(r_schedule_control_rec.r_project_id(i)) ||
583 ' r_schedule_control_rec.r_award_id(i): ' || fnd_number.number_to_canonical(r_schedule_control_rec.r_award_id(i)) ||
584 ' r_schedule_control_rec.r_task_id(i): ' || fnd_number.number_to_canonical(r_schedule_control_rec.r_task_id(i)) ||
585 ' r_schedule_control_rec.r_expenditure_organization_id(i): ' || fnd_number.number_to_canonical(r_schedule_control_rec.r_expenditure_organization_id(i)) ||
586 ' r_schedule_control_rec.r_poeta_start_date(i): ' || fnd_date.date_to_canonical(r_schedule_control_rec.r_poeta_start_date(i)) ||
587 ' r_schedule_control_rec.r_poeta_end_date(i): ' || fnd_date.date_to_canonical(r_schedule_control_rec.r_poeta_end_date(i)) ||
588 ' r_schedule_control_rec.r_expenditure_type(i): ' || r_schedule_control_rec.r_expenditure_type(i) ||
589 ' l_prev_project_id: ' || fnd_number.number_to_canonical(l_prev_project_id) ||
590 ' l_prev_award_id: ' || fnd_number.number_to_canonical(l_prev_award_id) ||
591 ' l_prev_task_id: ' || fnd_number.number_to_canonical(l_prev_task_id) ||
592 ' l_prev_exp_organization_id: ' || fnd_number.number_to_canonical(l_prev_exp_organization_id) ||
593 ' l_prev_expenditure_type: ' || l_prev_expenditure_type);
594
595 l_proc_step := 20;
596 -- End of bug fix 3434626
597
598 IF ((r_schedule_control_rec.r_project_id(i) <> l_prev_project_id) OR
599 (r_schedule_control_rec.r_expenditure_organization_id(i) <>
600 l_prev_exp_organization_id) OR
601 (r_schedule_control_rec.r_task_id(i) <> l_prev_task_id) OR
602 (r_schedule_control_rec.r_award_id(i) <> l_prev_award_id) OR
603 (r_schedule_control_rec.r_expenditure_type(i) <> l_prev_expenditure_type)) THEN
604
605 /* Validating and fetching poeta dates */
606 validate_poeta (p_project_id => r_schedule_control_rec.r_project_id(i),
607 p_task_id => r_schedule_control_rec.r_task_id(i),
608 p_award_id => r_schedule_control_rec.r_award_id(i),
609 p_expenditure_type => r_schedule_control_rec.r_expenditure_type(i),
610 p_expenditure_organization_id => r_schedule_control_rec.r_expenditure_organization_id(i),
611 p_payroll_id => p_payroll_id,
612 p_start_date => g_final_start_date,
613 p_end_date => g_final_end_date,
614 p_return_status => p_return_status);
615
616 IF p_return_status <> fnd_api.g_ret_sts_success THEN
617 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
618 END IF;
619
620 -- Introduced for bug fix 3434626
621 hr_utility.trace('l_poeta_start_date: ' || fnd_date.date_to_canonical(g_final_start_date) ||
622 ' l_poeta_end_date: ' || fnd_date.date_to_canonical(g_final_end_date));
623 l_proc_step := 30;
624 -- End of bug fix 3434626
625
626 l_prev_project_id := r_schedule_control_rec.r_project_id(i);
627 l_prev_task_id := r_schedule_control_rec.r_task_id(i);
628 l_prev_award_id := r_schedule_control_rec.r_award_id(i);
629 l_prev_exp_organization_id :=
630 r_schedule_control_rec.r_expenditure_organization_id(i);
631 l_prev_expenditure_type := r_schedule_control_rec.r_expenditure_type(i);
632 END IF;
633
634 /* Verifying whether current poeta dates are different from previous poeta dates */
635 IF (r_schedule_control_rec.r_poeta_start_date(i) <> g_final_start_date)
636 OR (r_schedule_control_rec.r_poeta_end_date(i) <> g_final_end_date) THEN
637 r_schedule_control_rec.r_poeta_start_date(i) := g_final_start_date;
638 r_schedule_control_rec.r_poeta_end_date(i) := g_final_end_date;
639 IF (p_pre_process_mode = 'R') THEN
640 r_asg_id_array.r_asg_id(j) := r_schedule_control_rec.r_assignment_id(i);
641 r_asg_id_array.r_payroll_id(j) := r_schedule_control_rec.r_payroll_id(i);
642 j := j+1;
643 END IF;
644 -- Introduced for bug fix 3434626
645 hr_utility.trace('j: ' || fnd_number.number_to_canonical(j));
646 l_proc_step := 40;
647 -- End of bug fix 3434626
648
649 ELSE
650 IF (p_pre_process_mode = 'R') THEN
651 IF r_schedule_control_rec.r_project_id(i)<> l_prev_tx_project_id THEN
652 -- Introduced for bug fix 3434626
653 l_proc_step := 50;
654 -- End of bug fix 3434626
655
656 IF j>1 then
657 insert_changed_assignments (p_change_type => 'PT',
658 p_return_status => p_return_status);
659 IF (p_return_status <> fnd_api.g_ret_sts_success) THEN
660 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
661 END IF;
662 j:=1;
663
664 END IF; -- bug fix 2597666
665
666 -- Introduced for bug fix 3434626
667 l_proc_step := 60;
668 -- End of bug fix 3434626
669
670 validate_transaction_controls
671 (p_project_id => r_schedule_control_rec.r_project_id(i),
672 p_payroll_id => p_payroll_id,
673 p_return_status => p_return_status);
674 IF (p_return_status <> fnd_api.g_ret_sts_success) THEN
675 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
676 END IF;
677 l_prev_tx_project_id := r_schedule_control_rec.r_project_id(i);
678 END IF;
679 END IF;
680 END IF;
681 END LOOP;
682
683 -- Introduced for bug fix 3434626
684 l_proc_step := 70;
685 -- End of bug fix 3434626
686
687 /* Updating poeta dates in psp_schedule_lines */
688 FORALL i in 1 .. g_total_num_rec
689 UPDATE psp_schedule_lines
690 SET poeta_end_date = r_schedule_control_rec.r_poeta_end_date(i),
691 poeta_start_date = r_schedule_control_rec.r_poeta_start_date(i)
692 WHERE schedule_line_id = r_schedule_control_rec.r_schedule_line_id(i);
693
694 -- Introduced for bug fix 3434626
695 l_proc_step := 80;
696 -- End of bug fix 3434626
697
698 IF (p_pre_process_mode = 'R') THEN
699 /* Inserting into psp_enc_changed_assignments */
700 IF j> 1 then -- bug fix 2597666
701 insert_changed_assignments (p_change_type => 'PT',
702 p_return_status => p_return_status);
703 IF (p_return_status <> fnd_api.g_ret_sts_success) THEN
704 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
705 END IF;
706 END IF;
707 END IF;
708
709 p_return_status := fnd_api.g_ret_sts_success;
710
711 -- Introduced for bug fix 3434626
712 l_proc_step := 80;
713 hr_utility.trace('Leaving ' || l_proc_name);
714 -- End of bug fix 3434626
715 EXCEPTION
716 WHEN OTHERS THEN
717 g_error_api_path := SUBSTR('UPDT_SCHDLS:'||g_error_api_path,1,30);
718 -- bug fix 2597666
719 p_return_status := fnd_api.g_ret_sts_unexp_error;
720 -- Introduced for bug fix 3434626
721 hr_utility.trace(fnd_number.number_to_canonical(l_proc_step) || ': ' || l_proc_name);
722 hr_utility.trace('Leaving ' || l_proc_name);
723 -- End of bug fix 3434626
724 END update_schedules;
725
726 /****************************************************************************************************
727 Procedure Name: update_default_labor_schedules
728 Purpose: This procedure is called in the main procedure and used for updating poeta
729 dates in psp_org_default_labor_schedules table for poeta CI. It inserts
730 assignments in psp_enc_changed_assignments table if the poeta dates are
731 different from previous poeta dates and assignments exists in
732 psp_enc_lines_history table.
733 ****************************************************************************************************/
734
735 PROCEDURE update_default_labor_schedules (p_pre_process_mode IN VARCHAR2,
736 p_payroll_id IN NUMBER,
737 p_business_group_id IN NUMBER,
738 p_set_of_books_id IN NUMBER,
739 p_max_pay_date IN DATE,
740 p_return_status OUT NOCOPY VARCHAR2)
741 IS
742
743 TYPE ls_lines_rec is RECORD (
744 r_org_schedule_id v_line_id,
745 r_project_id v_project_id,
746 r_task_id v_task_id,
747 r_expenditure_organization_id v_exp_org,
748 r_expenditure_type v_exp_type,
749 r_award_id v_award_id,
750 r_start_date_active v_start_dt,
751 r_end_date_active v_end_dt,
752 r_poeta_start_date v_start_dt,
753 r_poeta_end_date v_end_dt);
754 r_ls_control_rec ls_lines_rec;
755
756 l_prev_project_id NUMBER(15) DEFAULT -1;
757 l_prev_task_id NUMBER(15) DEFAULT -1;
758 l_prev_award_id NUMBER(15) DEFAULT -1;
759 l_prev_exp_organization_id NUMBER(15) DEFAULT -1;
760 l_prev_expenditure_type VARCHAR2(30) DEFAULT '-1';
761 l_prev_tx_project_id NUMBER(15) DEFAULT -1;
762
763 /* Cursor will select distinct poeta combinations from psp_default_labor_schedules for a payroll. */
764 CURSOR org_labor_schedule_cur IS
765 SELECT pdls.org_schedule_id,
766 pdls.project_id,
767 pdls.task_id,
768 pdls.expenditure_organization_id,
769 pdls.expenditure_type,
770 pdls.award_id,
771 pdls.schedule_begin_date,
772 pdls.schedule_end_date,
773 NVL(pdls.poeta_start_date,TO_DATE('01-01-1800', 'DD-MM-YYYY')) poeta_start_date,
774 NVL(pdls.poeta_end_date,TO_DATE('31-12-4712', 'DD-MM-YYYY')) poeta_end_date
775 FROM psp_default_labor_schedules pdls
776 WHERE pdls.business_group_id = p_business_group_id
777 AND pdls.set_of_books_id = p_set_of_books_id
778 AND pdls.gl_code_combination_id IS NULL
779 AND pdls.schedule_end_date >= p_max_pay_date
780 ORDER BY 2,3,4,5,6;
781
782 CURSOR assignment_payroll_cur(j number) IS
783 SELECT DISTINCT pelh.assignment_id,pelh.payroll_id
784 FROM psp_enc_lines_history pelh
785 -- Modified default_org_account_id to org_schedule_id for bug 2334434
786 WHERE pelh.org_schedule_id = r_ls_control_rec.r_org_schedule_id (j)
787 AND pelh.suspense_org_account_id IS NULL
788 AND pelh.change_flag = 'N';
789
790 -- Introduced for bug fix 3434626
791 l_proc_name VARCHAR2(61) DEFAULT g_package_name || 'UPDATE_DEFAULT_LABOR_SCHEDULES';
792 l_proc_step NUMBER(20, 10) DEFAULT 0;
793 -- End of changes for bug fix 3434626
794
795 BEGIN
796 -- Introduced the following for bug fix 3434626
797 hr_utility.trace('Entering ' || l_proc_name);
798 hr_utility.trace('p_pre_process_mode: ' || p_pre_process_mode ||
799 ' p_payroll_id: ' || fnd_number.number_to_canonical(p_payroll_id) ||
800 ' p_business_group_id: ' || fnd_number.number_to_canonical(p_business_group_id) ||
801 ' p_set_of_books_id: ' || fnd_number.number_to_canonical(p_set_of_books_id) ||
802 ' p_max_pay_date: ' || fnd_date.date_to_canonical(p_max_pay_date));
803
804 l_proc_step := 10; -- Introduced for bug fix 3434626
805 -- End of bug fix 3434626
806
807 /* Fetching distinct poeta combinations from psp_org_default_labor_schedules table*/
808 OPEN org_labor_schedule_cur;
809 FETCH org_labor_schedule_cur BULK COLLECT INTO
810 r_ls_control_rec.r_org_schedule_id,
811 r_ls_control_rec.r_project_id,
812 r_ls_control_rec.r_task_id,
813 r_ls_control_rec.r_expenditure_organization_id,
814 r_ls_control_rec.r_expenditure_type,
815 r_ls_control_rec.r_award_id,
816 r_ls_control_rec.r_start_date_active,
817 r_ls_control_rec.r_end_date_active,
818 r_ls_control_rec.r_poeta_start_date,
819 r_ls_control_rec.r_poeta_end_date;
820 CLOSE org_labor_schedule_cur;
821 g_total_num_rec := r_ls_control_rec.r_org_schedule_id.count;
822
823 FOR i IN 1..g_total_num_rec
824 LOOP
825 -- Introduced the following for bug fix 3434626
826 hr_utility.trace('i: ' || fnd_number.number_to_canonical(i) ||
827 ' r_ls_control_rec.r_schedule_line_id: ' || fnd_number.number_to_canonical(r_ls_control_rec.r_org_schedule_id(i)) ||
828 ' r_ls_control_rec.r_project_id(i): ' || fnd_number.number_to_canonical(r_ls_control_rec.r_project_id(i)) ||
829 ' r_ls_control_rec.r_award_id(i): ' || fnd_number.number_to_canonical(r_ls_control_rec.r_award_id(i)) ||
830 ' r_ls_control_rec.r_task_id(i): ' || fnd_number.number_to_canonical(r_ls_control_rec.r_task_id(i)) ||
831 ' r_ls_control_rec.r_expenditure_organization_id(i): ' || fnd_number.number_to_canonical(r_ls_control_rec.r_expenditure_organization_id(i)) ||
832 ' r_ls_control_rec.r_poeta_start_date(i): ' || fnd_date.date_to_canonical(r_ls_control_rec.r_poeta_start_date(i)) ||
833 ' r_ls_control_rec.r_poeta_end_date(i): ' || fnd_date.date_to_canonical(r_ls_control_rec.r_poeta_end_date(i)) ||
834 ' r_ls_control_rec.r_expenditure_type(i): ' || r_ls_control_rec.r_expenditure_type(i) ||
835 ' l_prev_project_id: ' || fnd_number.number_to_canonical(l_prev_project_id) ||
836 ' l_prev_award_id: ' || fnd_number.number_to_canonical(l_prev_award_id) ||
837 ' l_prev_task_id: ' || fnd_number.number_to_canonical(l_prev_task_id) ||
838 ' l_prev_exp_organization_id: ' || fnd_number.number_to_canonical(l_prev_exp_organization_id) ||
839 ' l_prev_expenditure_type: ' || l_prev_expenditure_type);
840
841 l_proc_step := 20;
842 -- End of bug fix 3434626
843
844 IF ((r_ls_control_rec.r_project_id(i) <> l_prev_project_id) OR
845 (r_ls_control_rec.r_expenditure_organization_id(i) <> l_prev_exp_organization_id) OR
846 (r_ls_control_rec.r_task_id(i) <> l_prev_task_id) OR
847 (r_ls_control_rec.r_award_id(i) <> l_prev_award_id) OR
848 (r_ls_control_rec.r_expenditure_type(i) <> l_prev_expenditure_type)) THEN
849
850 /* Validating and fetching poeta dates */
851 validate_poeta (p_project_id => r_ls_control_rec.r_project_id(i),
852 p_task_id => r_ls_control_rec.r_task_id(i),
853 p_award_id => r_ls_control_rec.r_award_id(i),
854 p_expenditure_type => r_ls_control_rec.r_expenditure_type(i),
855 p_expenditure_organization_id => r_ls_control_rec.r_expenditure_organization_id(i),
856 p_payroll_id => p_payroll_id,
857 p_start_date => g_final_start_date,
858 p_end_date => g_final_end_date,
859 p_return_status => p_return_status);
860
861 IF p_return_status <> fnd_api.g_ret_sts_success THEN
862 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
863 END IF;
864
865 -- Introduced for bug fix 3434626
866 hr_utility.trace('l_poeta_start_date: ' || fnd_date.date_to_canonical(g_final_start_date) ||
867 ' l_poeta_end_date: ' || fnd_date.date_to_canonical(g_final_end_date));
868 l_proc_step := 30;
869 -- End of bug fix 3434626
870
871 l_prev_project_id := r_ls_control_rec.r_project_id(i);
872 l_prev_task_id := r_ls_control_rec.r_task_id(i);
873 l_prev_award_id := r_ls_control_rec.r_award_id(i);
874 l_prev_exp_organization_id := r_ls_control_rec.r_expenditure_organization_id(i);
875 l_prev_expenditure_type := r_ls_control_rec.r_expenditure_type(i);
876 END IF;
877
878 /* Verifying whether poeta dates are different from previous dates */
879 IF (r_ls_control_rec.r_poeta_start_date(i) <> g_final_start_date)
880 OR (r_ls_control_rec.r_poeta_end_date(i) <> g_final_end_date) THEN
881 r_ls_control_rec.r_poeta_start_date(i) := g_final_start_date;
882 r_ls_control_rec.r_poeta_end_date(i) := g_final_end_date;
883
884 IF (p_pre_process_mode = 'R') THEN
885 /* Opening the cursor to fetch payroll_id and assignment_id into
886 respective payroll_id and assignment_id arrays */
887 OPEN assignment_payroll_cur(i);
888 FETCH assignment_payroll_cur BULK COLLECT INTO r_asg_id_array.r_asg_id,
889 r_asg_id_array.r_payroll_id;
890 CLOSE assignment_payroll_cur;
891
892 -- Introduced for bug fix 3434626
893 hr_utility.trace('r_asg_id_array.r_asg_id.COUNT: ' || fnd_number.number_to_canonical(r_asg_id_array.r_asg_id.COUNT));
894 l_proc_step := 40;
895 -- End of bug fix 3434626
896 /* Insert assignments (exists in psp_enc_lines_history and poeta dates are
897 changed) into psp_enc_changed_assignments table*/
898 IF r_asg_id_array.r_asg_id.count<>0 THEN
899 insert_changed_assignments (p_change_type => 'PT',
900 p_return_status => p_return_status);
901 IF (p_return_status <> fnd_api.g_ret_sts_success) THEN
902 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
903 END IF;
904 END IF;
905 END IF;
906 ELSE
907 IF (p_pre_process_mode = 'R') THEN
908 -- Introduced for bug fix 3434626
909 l_proc_step := 50;
910 -- End of bug fix 3434626
911 IF r_ls_control_rec.r_project_id(i) <> l_prev_tx_project_id THEN
912 validate_transaction_controls
913 (p_project_id => r_ls_control_rec.r_project_id(i),
914 p_payroll_id => p_payroll_id,
915 p_return_status => p_return_status);
916 IF (p_return_status <> fnd_api.g_ret_sts_success) THEN
917 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
918 END IF;
919 l_prev_tx_project_id := r_ls_control_rec.r_project_id(i);
920 END IF;
921 END IF;
922 END IF;
923 END LOOP;
924
925 -- Introduced for bug fix 3434626
926 l_proc_step := 60;
927 -- End of bug fix 3434626
928
929 /*Updating poeta dates in psp_default_labor_schedule table */
930 FORALL i in 1 .. g_total_num_rec
931 UPDATE psp_default_labor_schedules
932 SET poeta_end_date = r_ls_control_rec.r_poeta_end_date(i),
933 poeta_start_date = r_ls_control_rec.r_poeta_start_date(i)
934 WHERE org_schedule_id = r_ls_control_rec.r_org_schedule_id(i);
935
936 p_return_status := fnd_api.g_ret_sts_success;
937
938 -- Introduced for bug fix 3434626
939 l_proc_step := 70;
940 hr_utility.trace('Leaving ' || l_proc_name);
941 -- End of bug fix 3434626
942 EXCEPTION
943 WHEN OTHERS THEN
944 g_error_api_path := SUBSTR('UPDT_DFLT_LBR_SCHD:'||g_error_api_path,1,30);
945 -- bug fix 2597666
946 p_return_status := fnd_api.g_ret_sts_unexp_error;
947 -- Introduced for bug fix 3434626
948 hr_utility.trace(fnd_number.number_to_canonical(l_proc_step) || ': ' || l_proc_name);
949 hr_utility.trace('Leaving ' || l_proc_name);
950 -- End of bug fix 3434626
951 END update_default_labor_schedules;
952
953 /****************************************************************************************************
954 Procedure Name: UPDATE_DEFAULT_SUSP_ACCOUNTS
955 Purpose: This procedure is called in the main procedure and used for updating poeta
956 dates in psp_organization_accounts for poeta CI. It inserts assignments in
957 psp_enc_changed_assignments table if the poeta dates are different from
958 previous poeta dates and assignments exists in psp_enc_lines_history
959 table.
960 ****************************************************************************************************/
961
962 PROCEDURE update_default_susp_accounts (p_pre_process_mode IN VARCHAR2,
963 p_payroll_id IN NUMBER,
964 p_business_group_id IN NUMBER,
965 p_set_of_books_id IN NUMBER,
966 p_max_pay_date IN DATE,
967 p_return_status OUT NOCOPY VARCHAR2)
968 IS
969
970 TYPE susp_lines_rec is RECORD
971 (r_organization_account_id v_line_id,
972 r_project_id v_project_id,
973 r_task_id v_task_id,
974 r_expenditure_organization_id v_exp_org,
975 r_expenditure_type v_exp_type,
976 r_award_id v_award_id,
977 r_start_date_active v_start_dt,
978 r_end_date_active v_end_dt,
979 r_poeta_start_date v_start_dt,
980 r_poeta_end_date v_end_dt);
981
982 r_susp_control_rec susp_lines_rec;
983
984 l_prev_project_id NUMBER(15) DEFAULT -1;
985 l_prev_task_id NUMBER(15) DEFAULT -1;
986 l_prev_award_id NUMBER(15) DEFAULT -1;
987 l_prev_exp_organization_id NUMBER(15) DEFAULT -1;
988 l_prev_expenditure_type VARCHAR2(30) DEFAULT '-1';
989 l_prev_tx_project_id NUMBER(15) DEFAULT -1;
990
991 CURSOR suspense_account_cur IS
992 SELECT poa.organization_account_id,
993 poa.project_id,
994 poa.task_id,
995 poa.expenditure_organization_id,
996 poa.expenditure_type,
997 poa.award_id,
998 poa.start_date_active,
999 poa.end_date_active,
1000 NVL(poa.poeta_start_date,TO_DATE('01-01-1800', 'DD-MM-YYYY')) poeta_start_date,
1001 NVL(poa.poeta_end_date,TO_DATE('31-12-4712', 'DD-MM-YYYY')) poeta_end_date
1002 FROM psp_organization_accounts poa
1003 WHERE poa.gl_code_combination_id IS NULL
1004 AND poa.end_date_active >= p_max_pay_date
1005 AND poa.business_group_id = p_business_group_id
1006 AND poa.set_of_books_id = p_set_of_books_id
1007 ORDER BY 2,3,4,5,6;
1008
1009 CURSOR assignment_payroll_cur(j number) IS
1010 SELECT DISTINCT pelh.assignment_id,
1011 pelh.payroll_id
1012 FROM psp_enc_lines_history pelh
1013 WHERE NVL(suspense_org_account_id, default_org_account_id) = r_susp_control_rec.r_organization_account_id (j)
1014 AND pelh.change_flag = 'N';
1015
1016 -- Introduced for bug fix 3434626
1017 l_proc_name VARCHAR2(61) DEFAULT g_package_name || 'UPDATE_DEFAULT_SUSP_ACCOUNTS';
1018 l_proc_step NUMBER(20, 10) DEFAULT 0;
1019 -- End of changes for bug fix 3434626
1020 BEGIN
1021 -- Introduced the following for bug fix 3434626
1022 hr_utility.trace('Entering ' || l_proc_name);
1023 hr_utility.trace('p_pre_process_mode: ' || p_pre_process_mode ||
1024 ' p_payroll_id: ' || fnd_number.number_to_canonical(p_payroll_id) ||
1025 ' p_business_group_id: ' || fnd_number.number_to_canonical(p_business_group_id) ||
1026 ' p_set_of_books_id: ' || fnd_number.number_to_canonical(p_set_of_books_id) ||
1027 ' p_max_pay_date: ' || fnd_date.date_to_canonical(p_max_pay_date));
1028
1029 l_proc_step := 10; -- Introduced for bug fix 3434626
1030 -- End of bug fix 3434626
1031
1032 /* Opening suspense account cur to fetch poeta lines*/
1033 OPEN suspense_account_cur;
1034 FETCH suspense_account_cur BULK COLLECT INTO
1035 r_susp_control_rec.r_organization_account_id,
1036 r_susp_control_rec.r_project_id,
1037 r_susp_control_rec.r_task_id,
1038 r_susp_control_rec.r_expenditure_organization_id,
1039 r_susp_control_rec.r_expenditure_type,
1040 r_susp_control_rec.r_award_id,
1041 r_susp_control_rec.r_start_date_active,
1042 r_susp_control_rec.r_end_date_active,
1043 r_susp_control_rec.r_poeta_start_date,
1044 r_susp_control_rec.r_poeta_end_date;
1045 CLOSE suspense_account_cur;
1046 g_total_num_rec := r_susp_control_rec.r_organization_account_id.count;
1047
1048 FOR i IN 1..g_total_num_rec
1049 LOOP
1050 -- Introduced the following for bug fix 3434626
1051 hr_utility.trace('i: ' || fnd_number.number_to_canonical(i) ||
1052 ' r_susp_control_rec.r_schedule_line_id: ' || fnd_number.number_to_canonical(r_susp_control_rec.r_organization_account_id(i)) ||
1053 ' r_susp_control_rec.r_project_id(i): ' || fnd_number.number_to_canonical(r_susp_control_rec.r_project_id(i)) ||
1054 ' r_susp_control_rec.r_award_id(i): ' || fnd_number.number_to_canonical(r_susp_control_rec.r_award_id(i)) ||
1055 ' r_susp_control_rec.r_task_id(i): ' || fnd_number.number_to_canonical(r_susp_control_rec.r_task_id(i)) ||
1056 ' r_susp_control_rec.r_expenditure_organization_id(i): ' || fnd_number.number_to_canonical(r_susp_control_rec.r_expenditure_organization_id(i)) ||
1057 ' r_susp_control_rec.r_poeta_start_date(i): ' || fnd_date.date_to_canonical(r_susp_control_rec.r_poeta_start_date(i)) ||
1058 ' r_susp_control_rec.r_poeta_end_date(i): ' || fnd_date.date_to_canonical(r_susp_control_rec.r_poeta_end_date(i)) ||
1059 ' r_susp_control_rec.r_expenditure_type(i): ' || r_susp_control_rec.r_expenditure_type(i) ||
1060 ' l_prev_project_id: ' || fnd_number.number_to_canonical(l_prev_project_id) ||
1061 ' l_prev_award_id: ' || fnd_number.number_to_canonical(l_prev_award_id) ||
1062 ' l_prev_task_id: ' || fnd_number.number_to_canonical(l_prev_task_id) ||
1063 ' l_prev_exp_organization_id: ' || fnd_number.number_to_canonical(l_prev_exp_organization_id) ||
1064 ' l_prev_expenditure_type: ' || l_prev_expenditure_type);
1065
1066 l_proc_step := 20;
1067 -- End of bug fix 3434626
1068
1069 IF ((r_susp_control_rec.r_project_id(i) <> l_prev_project_id) OR
1070 (r_susp_control_rec.r_expenditure_organization_id(i) <> l_prev_exp_organization_id) OR
1071 (r_susp_control_rec.r_task_id(i) <> l_prev_task_id) OR
1072 (r_susp_control_rec.r_award_id(i) <> l_prev_award_id) OR
1073 (r_susp_control_rec.r_expenditure_type(i) <> l_prev_expenditure_type)) THEN
1074
1075 /* Validating and fetching poeta dates*/
1076 validate_poeta (p_project_id => r_susp_control_rec.r_project_id(i),
1077 p_task_id => r_susp_control_rec.r_task_id(i),
1078 p_award_id => r_susp_control_rec.r_award_id(i),
1079 p_expenditure_type => r_susp_control_rec.r_expenditure_type(i),
1080 p_expenditure_organization_id => r_susp_control_rec.r_expenditure_organization_id(i),
1081 p_payroll_id => p_payroll_id,
1082 p_start_date => g_final_start_date,
1083 p_end_date => g_final_end_date,
1084 p_return_status => p_return_status);
1085
1086 IF p_return_status <> fnd_api.g_ret_sts_success THEN
1087 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1088 END IF;
1089
1090 -- Introduced for bug fix 3434626
1091 hr_utility.trace('l_poeta_start_date: ' || fnd_date.date_to_canonical(g_final_start_date) ||
1092 ' l_poeta_end_date: ' || fnd_date.date_to_canonical(g_final_end_date));
1093 l_proc_step := 30;
1094 -- End of bug fix 3434626
1095
1096 l_prev_project_id := r_susp_control_rec.r_project_id(i);
1097 l_prev_task_id := r_susp_control_rec.r_task_id(i);
1098 l_prev_award_id := r_susp_control_rec.r_award_id(i);
1099 l_prev_exp_organization_id := r_susp_control_rec.r_expenditure_organization_id(i);
1100 l_prev_expenditure_type := r_susp_control_rec.r_expenditure_type(i);
1101 END IF;
1102
1103
1104 /* Checking whether poeta dates are different from previous dates */
1105 IF (r_susp_control_rec.r_poeta_start_date(i) <>g_final_start_date)
1106 OR (r_susp_control_rec.r_poeta_end_date(i) <> g_final_end_date) THEN
1107 r_susp_control_rec.r_poeta_end_date(i) := g_final_end_date;
1108 r_susp_control_rec.r_poeta_start_date(i) := g_final_start_date;
1109
1110 IF (p_pre_process_mode = 'R') THEN
1111 /* Opening the cursor to fetch payroll_id and assignment_id into respective
1112 payroll_id and assignment_id arrays */
1113 OPEN assignment_payroll_cur(i);
1114 FETCH assignment_payroll_cur BULK COLLECT INTO r_asg_id_array.r_asg_id,
1115 r_asg_id_array.r_payroll_id;
1116 CLOSE assignment_payroll_cur;
1117
1118 -- Introduced for bug fix 3434626
1119 hr_utility.trace('r_asg_id_array.r_asg_id.COUNT: ' || fnd_number.number_to_canonical(r_asg_id_array.r_asg_id.COUNT));
1120 l_proc_step := 40;
1121 -- End of bug fix 3434626
1122
1123 /* Insert assignments (exists in psp_enc_lines_history and poeta dates are
1124 changed) into psp_enc_changed_assignments table */
1125 IF r_asg_id_array.r_asg_id.count<>0 THEN
1126 insert_changed_assignments (p_change_type => 'PT',
1127 p_return_status => p_return_status);
1128 IF (p_return_status <> fnd_api.g_ret_sts_success) THEN
1129 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1130 END IF;
1131 END IF;
1132 END IF;
1133 ELSE
1134 IF (p_pre_process_mode = 'R') THEN
1135 -- Introduced for bug fix 3434626
1136 l_proc_step := 50;
1137 -- End of bug fix 3434626
1138
1139 IF r_susp_control_rec.r_project_id(i) <> l_prev_tx_project_id THEN
1140 validate_transaction_controls
1141 (p_project_id => r_susp_control_rec.r_project_id(i),
1142 p_payroll_id => p_payroll_id,
1143 p_return_status => p_return_status);
1144 IF (p_return_status <> fnd_api.g_ret_sts_success) THEN
1145 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1146 END IF;
1147 l_prev_tx_project_id := r_susp_control_rec.r_project_id(i);
1148 END IF;
1149 END IF;
1150 END IF;
1151 END LOOP;
1152
1153 -- Introduced for bug fix 3434626
1154 l_proc_step := 60;
1155 -- End of bug fix 3434626
1156
1157 /* Updating poeta dates in psp_organization_accounts */
1158 FORALL i IN 1 .. g_total_num_rec
1159 UPDATE psp_organization_accounts
1160 SET poeta_end_date = r_susp_control_rec.r_poeta_end_date(i),
1161 poeta_start_date = r_susp_control_rec.r_poeta_start_date(i)
1162 WHERE organization_account_id = r_susp_control_rec.r_organization_account_id(i);
1163
1164 p_return_status := fnd_api.g_ret_sts_success;
1165
1166 -- Introduced for bug fix 3434626
1167 l_proc_step := 70;
1168 hr_utility.trace('Leaving ' || l_proc_name);
1169 -- End of bug fix 3434626
1170 EXCEPTION
1171 WHEN OTHERS THEN
1172 g_error_api_path := SUBSTR('UPDT_DFLT_SSP_ACCNT:'||g_error_api_path,1,30);
1173 -- bug fix 2597666
1174 p_return_status := fnd_api.g_ret_sts_unexp_error;
1175 -- Introduced for bug fix 3434626
1176 hr_utility.trace(fnd_number.number_to_canonical(l_proc_step) || ': ' || l_proc_name);
1177 hr_utility.trace('Leaving ' || l_proc_name);
1178 -- End of bug fix 3434626
1179 END update_default_susp_accounts;
1180
1181 /****************************************************************************************************
1182 Procedure Name: VALIDATE_POETA
1183 Purpose: This procedure is used to perform date invariant and date variant validations
1184 on poeta. After validation it fetches correct poeta start and end date that
1185 is made use of by other procedures to update respective schedule lines.
1186 Further certain date variant checks are taken care by date invariant
1187 validations.
1188
1189 Additional Information:
1190 Project_info_cur takes care of project - task link, project status, chargeable_flag etc.
1191 Award_info_cur takes care of award and project link, award is active and other
1192 award related validations
1193 Expenditure_type_info_cur takes care of validation for expenditure types
1194 Exp_org_cur takes care of expenditure organization validations
1195 ****************************************************************************************************/
1196
1197 PROCEDURE validate_poeta (p_project_id IN NUMBER,
1198 p_task_id IN NUMBER,
1199 p_award_id IN NUMBER,
1200 p_expenditure_type IN VARCHAR2,
1201 p_expenditure_organization_id IN NUMBER,
1202 p_payroll_id IN NUMBER,
1203 p_start_date OUT NOCOPY DATE,
1204 p_end_date OUT NOCOPY DATE,
1205 p_return_status OUT NOCOPY VARCHAR2)
1206 IS
1207 l_proj_start_date DATE DEFAULT fnd_date.canonical_to_date('1800/01/01');
1208 l_proj_end_date DATE DEFAULT fnd_date.canonical_to_date('4712/12/31');
1209 l_task_start_date DATE DEFAULT fnd_date.canonical_to_date('1800/01/01');
1210 l_task_end_date DATE DEFAULT fnd_date.canonical_to_date('4712/12/31');
1211 l_award_start_date DATE DEFAULT fnd_date.canonical_to_date('1800/01/01');
1212 l_award_end_date DATE DEFAULT fnd_date.canonical_to_date('4712/12/31');
1213 l_exp_type_start_date DATE DEFAULT fnd_date.canonical_to_date('1800/01/01');
1214 l_exp_type_end_date DATE DEFAULT fnd_date.canonical_to_date('4712/12/31');
1215 l_exp_org_start_date DATE DEFAULT fnd_date.canonical_to_date('1800/01/01');
1216 l_exp_org_end_date DATE DEFAULT fnd_date.canonical_to_date('4712/12/31');
1217 l_allowable_schedule_id NUMBER(15);
1218 l_proj_status_code VARCHAR2(30);
1219 l_enabled_flag VARCHAR2(1);
1220 l_enc_cr_date DATE;
1221 l_exp_type VARCHAR2(30);
1222 l_labor_enc_start_date DATE;
1223 l_labor_enc_end_date DATE;
1224
1225 /* Cursor is used to fetch project and task dates and to validate project and task*/
1226 CURSOR project_info_cur IS
1227 SELECT ppa.project_status_code,
1228 NVL(ppa.start_date, TO_DATE('1800/01/01', 'YYYY/MM/DD')),
1229 NVL(ppa.completion_date, TO_DATE('4712/12/31', 'YYYY/MM/DD')),
1230 NVL(pt.start_date, TO_DATE('1800/01/01', 'YYYY/MM/DD')),
1231 NVL(pt.completion_date, TO_DATE('4712/12/31', 'YYYY/MM/DD'))
1232 FROM pa_tasks pt,
1233 pa_projects_all ppa
1234 WHERE pt.task_id = p_task_id
1235 AND ppa.project_id = pt.project_id
1236 AND ppa.project_id = p_project_id
1237 AND ppa.project_status_code <> 'CLOSED'
1238 AND pt.chargeable_flag = 'Y';
1239
1240 /* Cursor is used to fetch award dates and to validate award */
1241 CURSOR award_info_cur IS
1242 SELECT NVL(gaw.preaward_date, NVL(gaw.start_date_active, TO_DATE('1800/01/01', 'YYYY/MM/DD'))),
1243 NVL(gaw.end_date_active, TO_DATE('4712/12/31', 'YYYY/MM/DD')),
1244 allowable_schedule_id
1245 FROM gms_awards_all gaw, --6957888
1246 gms_summary_project_fundings gspf,
1247 gms_installments gi,
1248 gms_budget_versions gbv,
1249 pa_tasks pt
1250 WHERE gaw.award_id = p_award_id
1251 AND gbv.project_id = p_project_id
1252 AND pt.task_id = p_task_id
1253 AND gbv.budget_status_code = 'B'
1254 AND gaw.status <>'CLOSED'
1255 AND gspf.project_id = gbv.project_id
1256 AND ((gspf.task_id = pt.task_id) OR (gspf.task_id IS NULL) OR (gspf.task_id = pt.top_task_id))
1257 AND gi.installment_id = gspf.installment_id
1258 AND gi.award_id = gaw.award_id
1259 AND gaw.award_template_flag = 'DEFERRED';
1260
1261 /* Cursor is used to fetch expenditure type date and to validate expenditure type */
1262 CURSOR expenditure_type_info_cur IS
1263 SELECT NVL(pet.start_date_active, TO_DATE('1800/01/01', 'YYYY/MM/DD')),
1264 NVL(pet.end_date_active, TO_DATE('4712/12/31', 'YYYY/MM/DD'))
1265 FROM gms_allowable_expenditures gae,
1266 pa_expenditure_types pet
1267 WHERE pet.expenditure_type = p_expenditure_type
1268 AND gae.expenditure_type = pet.expenditure_type
1269 AND gae.allowability_schedule_id = l_allowable_schedule_id;
1270
1271 /* Cursor is used to fetch exp org date and to validate exp org */
1272 CURSOR exp_org_cur IS
1273 SELECT NVL(poe.date_from, TO_DATE('1800/01/01', 'YYYY/MM/DD')),
1274 NVL(poe.date_to, TO_DATE('4712/12/31', 'YYYY/MM/DD'))
1275 FROM pa_organizations_expend_v poe
1276 WHERE poe.organization_id = p_expenditure_organization_id;
1277
1278 /* Cursor to validate enabled_flag from pa_project_status_controls */
1279 CURSOR project_status_csr IS
1280 SELECT ppsc.enabled_flag
1281 FROM pa_project_status_controls ppsc
1282 WHERE ppsc.project_status_code = l_proj_status_code
1283 AND ppsc.action_code = 'NEW_TXNS';
1284
1285 -- Introduced for bug fix 3434626
1286 l_proc_name VARCHAR2(61) DEFAULT g_package_name || 'VALIDATE_POETA';
1287 l_proc_step NUMBER(20, 10) DEFAULT 0;
1288 -- End of changes for bug fix 3434626
1289 BEGIN
1290 -- Introduced the following for bug fix 3434626
1291 hr_utility.trace('Entering ' || l_proc_name);
1292 hr_utility.trace('p_project_id: ' || fnd_number.number_to_canonical(p_project_id) ||
1293 ' p_award_id: ' || fnd_number.number_to_canonical(p_award_id) ||
1294 ' p_task_id: ' || fnd_number.number_to_canonical(p_task_id) ||
1295 ' p_expenditure_organization_id: ' || fnd_number.number_to_canonical(p_expenditure_organization_id) ||
1296 ' p_expenditure_type: ' || p_expenditure_type ||
1297 ' p_payroll_id: ' || fnd_number.number_to_canonical(p_payroll_id) ||
1298 ' p_start_date: ' || fnd_date.date_to_canonical(p_start_date) ||
1299 ' p_end_date: ' || fnd_date.date_to_canonical(p_end_date));
1300
1301 l_proc_step := 10;
1302 -- End of bug fix 3434626
1303
1304 p_return_status := fnd_api.g_ret_sts_success;
1305 OPEN project_info_cur ;
1306 FETCH project_info_cur into l_proj_status_code,
1307 l_proj_start_date,l_proj_end_date,l_task_start_date,l_task_end_date;
1308 IF project_info_cur%NOTFOUND THEN
1309 p_start_date := fnd_date.canonical_to_date('1800/01/01');
1310 p_end_date := fnd_date.canonical_to_date('1800/01/31');
1311 CLOSE project_info_cur;
1312 -- Introduced the following for bug fix 3434626
1313 hr_utility.trace('p_start_date: ' || fnd_date.date_to_canonical(p_start_date) ||
1314 ' p_end_date: ' || fnd_date.date_to_canonical(p_end_date));
1315
1316 l_proc_step := 20;
1317 -- End of bug fix 3434626
1318
1319 RETURN;
1320 END IF;
1321 CLOSE project_info_cur;
1322
1323 -- Introduced the following for bug fix 3434626
1324 l_proc_step := 30;
1325 -- End of bug fix 3434626
1326
1327 /*Validation for enabled_flag */
1328 OPEN project_status_csr;
1329 FETCH project_status_csr INTO l_enabled_flag;
1330 IF (project_status_csr %NOTFOUND) OR (l_enabled_flag = 'N') THEN
1331 p_start_date := fnd_date.canonical_to_date('1800/01/01');
1332 p_end_date := fnd_date.canonical_to_date('1800/01/31');
1333 CLOSE project_status_csr;
1334 -- Introduced the following for bug fix 3434626
1335 hr_utility.trace('p_start_date: ' || fnd_date.date_to_canonical(p_start_date) ||
1336 ' p_end_date: ' || fnd_date.date_to_canonical(p_end_date));
1337
1338 l_proc_step := 40;
1339 -- End of bug fix 3434626
1340
1341 RETURN;
1342 END IF;
1343 CLOSE project_status_csr;
1344
1345 -- Introduced the following for bug fix 3434626
1346 l_proc_step := 50;
1347 -- End of bug fix 3434626
1348
1349 OPEN award_info_cur;
1350 FETCH award_info_cur into l_award_start_date,l_award_end_date,l_allowable_schedule_id;
1351 IF award_info_cur%NOTFOUND then
1352 p_start_date := fnd_date.canonical_to_date('1800/01/01');
1353 p_end_date := fnd_date.canonical_to_date('1800/01/31');
1354 CLOSE award_info_cur;
1355 -- Introduced the following for bug fix 3434626
1356 hr_utility.trace('p_start_date: ' || fnd_date.date_to_canonical(p_start_date) ||
1357 ' p_end_date: ' || fnd_date.date_to_canonical(p_end_date));
1358
1359 l_proc_step := 60;
1360 -- End of bug fix 3434626
1361
1362 RETURN;
1363 END IF;
1364 CLOSE award_info_cur;
1365
1366 -- Introduced the following for bug fix 3434626
1367 l_proc_step := 70;
1368 -- End of bug fix 3434626
1369
1370 OPEN expenditure_type_info_cur;
1371 FETCH expenditure_type_info_cur into l_exp_type_start_date,l_exp_type_end_date;
1372 IF expenditure_type_info_cur %NOTFOUND THEN
1373 p_start_date := fnd_date.canonical_to_date('1800/01/01');
1374 p_end_date := fnd_date.canonical_to_date('1800/01/31');
1375 CLOSE expenditure_type_info_cur;
1376 RETURN;
1377 -- Introduced the following for bug fix 3434626
1378 hr_utility.trace('p_start_date: ' || fnd_date.date_to_canonical(p_start_date) ||
1379 ' p_end_date: ' || fnd_date.date_to_canonical(p_end_date));
1380
1381 l_proc_step := 80;
1382 -- End of bug fix 3434626
1383
1384 END IF;
1385 CLOSE expenditure_type_info_cur;
1386
1387 -- Introduced the following for bug fix 3434626
1388 l_proc_step := 90;
1389 -- End of bug fix 3434626
1390
1391 OPEN exp_org_cur;
1392 FETCH exp_org_cur into l_exp_org_start_date,l_exp_org_end_date;
1393 IF exp_org_cur% NOTFOUND THEN
1394 p_start_date := fnd_date.canonical_to_date('1800/01/01');
1395 p_end_date := fnd_date.canonical_to_date('1800/01/31');
1396 CLOSE exp_org_cur;
1397 -- Introduced the following for bug fix 3434626
1398 hr_utility.trace('p_start_date: ' || fnd_date.date_to_canonical(p_start_date) ||
1399 ' p_end_date: ' || fnd_date.date_to_canonical(p_end_date));
1400
1401 l_proc_step := 100;
1402 -- End of bug fix 3434626
1403
1404 RETURN;
1405 END IF;
1406 CLOSE exp_org_cur;
1407
1408 -- Introduced the following for bug fix 3434626
1409 l_proc_step := 110;
1410 -- End of bug fix 3434626
1411
1412 psp_st_ext.get_labor_enc_dates(p_project_id,
1413 p_task_id,
1414 p_award_id,
1415 p_expenditure_type,
1416 p_expenditure_organization_id,
1417 p_payroll_id,
1418 l_labor_enc_start_date,
1419 l_labor_enc_end_date);
1420 l_labor_enc_start_date := NVL(l_labor_enc_start_date, fnd_date.canonical_to_date('1800/01/01'));
1421 l_labor_enc_end_date := NVL(l_labor_enc_end_date, fnd_date.canonical_to_date('4712/12/31'));
1422 hr_utility.trace('l_labor_enc_start_date: ' || fnd_date.date_to_canonical(l_labor_enc_start_date) ||
1423 ' l_labor_enc_end_date: ' || fnd_date.date_to_canonical(l_labor_enc_end_date));
1424
1425 p_end_date := LEAST (l_proj_end_date,
1426 l_task_end_date,
1427 l_award_end_date,
1428 l_exp_type_end_date,
1429 l_exp_org_end_date,
1430 l_labor_enc_end_date);
1431 p_start_date := GREATEST (l_proj_start_date,
1432 l_task_start_date,
1433 l_award_start_date,
1434 l_exp_type_start_date,
1435 l_exp_org_start_date,
1436 l_labor_enc_start_date);
1437
1438 -- Introduced the following for bug fix 3434626
1439 hr_utility.trace('p_start_date: ' || fnd_date.date_to_canonical(p_start_date) ||
1440 ' p_end_date: ' || fnd_date.date_to_canonical(p_end_date));
1441
1442 l_proc_step := 120;
1443 -- End of bug fix 3434626
1444
1445 p_return_status := fnd_api.g_ret_sts_success;
1446
1447 -- Introduced for bug fix 3434626
1448 hr_utility.trace('Leaving ' || l_proc_name);
1449 -- End of bug fix 3434626
1450 EXCEPTION
1451 WHEN OTHERS THEN
1452 g_error_api_path := SUBSTR('VALIDATE_POETA:'||g_error_api_path,1,30);
1453 -- bug fix 2597666
1454 p_return_status := fnd_api.g_ret_sts_unexp_error;
1455 -- Introduced for bug fix 3434626
1456 hr_utility.trace(fnd_number.number_to_canonical(l_proc_step) || ': ' || l_proc_name);
1457 hr_utility.trace('Leaving ' || l_proc_name);
1458 -- End of bug fix 3434626
1459 END validate_poeta;
1460
1461 /****************************************************************************************************
1462 Procedure Name: VALIDATE_TRANSACTION_CONTROLS
1463 Purpose: This Procedure is used to log assignments for those records which modified in
1464 pa_transaction_controls after the last creation date of encumbrance for a
1465 payroll.
1466 ****************************************************************************************************/
1467
1468 PROCEDURE validate_transaction_controls (p_project_id IN NUMBER,
1469 p_payroll_id IN NUMBER,
1470 p_return_status OUT NOCOPY VARCHAR2)
1471 IS
1472
1473 l_enc_cr_date DATE;
1474
1475 /*CURSOR for selecting maximum creation date from psp_enc_controls for which lines are not liquidated*/
1476 CURSOR max_create_dt_cur IS
1477 SELECT max(pec.creation_date)
1478 FROM PSP_ENC_CONTROLS pec
1479 WHERE pec.payroll_id = p_payroll_id
1480 AND pec.action_code IN ('I', 'N', 'P'); -- Replaced <> 'L' with IN clause for bug fix 3099540
1481
1482 /* CURSOR to verify modification in pa_trancation_controls after the last creation date */
1483 CURSOR patc_change_cur IS
1484 SELECT patc.project_id
1485 FROM pa_transaction_controls patc
1486 WHERE patc.project_id = p_project_id
1487 AND patc.last_update_date > l_enc_cr_date
1488 AND rownum = 1;
1489
1490 /* CURSOR to find all assignments that were modified after last enc creation date in
1491 pa_transaction_controls */
1492 CURSOR tx_control_asg_cur IS
1493 SELECT pelh.assignment_id, p_payroll_id
1494 FROM psp_enc_lines_history pelh
1495 WHERE pelh.project_id = p_project_id
1496 AND pelh.payroll_id = p_payroll_id
1497 AND pelh.change_flag = 'N' --Added for bug 2334434
1498 GROUP BY pelh.assignment_id,p_payroll_id;
1499
1500 l_project_id NUMBER(10);
1501
1502 -- Introduced for bug fix 3434626
1503 l_proc_name VARCHAR2(61) DEFAULT g_package_name || 'VALIDATE_TRANSACTION_CONTROLS';
1504 l_proc_step NUMBER(20, 10) DEFAULT 0;
1505 -- End of changes for bug fix 3434626
1506
1507 BEGIN
1508 -- Introduced the following for bug fix 3434626
1509 hr_utility.trace('Entering ' || l_proc_name);
1510 hr_utility.trace('p_project_id: ' || fnd_number.number_to_canonical(p_project_id) ||
1511 ' p_payroll_id: ' || fnd_number.number_to_canonical(p_payroll_id));
1512
1513 l_proc_step := 10;
1514 -- End of bug fix 3434626
1515
1516 /* Opening the cursor to fetch maximum creation date for a particular payroll for which
1517 lines are not liquidated */
1518 OPEN max_create_dt_cur;
1519 FETCH max_create_dt_cur INTO l_enc_cr_date;
1520 CLOSE max_create_dt_cur;
1521
1522 -- Introduced the following for bug fix 3434626
1523 hr_utility.trace('l_enc_cr_date: ' || fnd_date.date_to_canonical(l_enc_cr_date));
1524
1525 l_proc_step := 20;
1526 -- End of bug fix 3434626
1527
1528 /* All the assignments for which records are modified in pa_transaction_controls after the
1529 last creation date will logged into psp_enc_changed_assignments for a particular payroll; */
1530
1531 IF l_enc_cr_date IS NOT NULL THEN
1532 OPEN patc_change_cur;
1533 FETCH patc_change_cur INTO l_project_id;
1534 CLOSE patc_change_cur;
1535
1536 -- Introduced the following for bug fix 3434626
1537 hr_utility.trace('l_project_id: ' || fnd_number.number_to_canonical(l_project_id));
1538
1539 l_proc_step := 30;
1540 -- End of bug fix 3434626
1541
1542 IF l_project_id IS NOT NULL THEN
1543 OPEN tx_control_asg_cur;
1544 FETCH tx_control_asg_cur BULK COLLECT INTO r_asg_id_array.r_asg_id,
1545 r_asg_id_array.r_payroll_id;
1546 CLOSE tx_control_asg_cur;
1547
1548 -- Introduced the following for bug fix 3434626
1549 hr_utility.trace('r_asg_id_array.r_asg_id.COUNT: ' || fnd_number.number_to_canonical(r_asg_id_array.r_asg_id.COUNT));
1550
1551 l_proc_step := 40;
1552 -- End of bug fix 3434626
1553
1554 IF r_asg_id_array.r_asg_id.count<>0 THEN
1555 insert_changed_assignments (p_change_type => 'TC',
1556 p_return_status => p_return_status);
1557 IF (p_return_status <> fnd_api.g_ret_sts_success) THEN
1558 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1559 END IF;
1560 END IF;
1561 END IF;
1562 END IF;
1563
1564 -- Introduced the following for bug fix 3434626
1565 l_proc_step := 50;
1566 -- End of bug fix 3434626
1567
1568 p_return_status := fnd_api.g_ret_sts_success;
1569
1570 -- Introduced the following for bug fix 3434626
1571 hr_utility.trace('Leaving ' || l_proc_name);
1572 -- End of bug fix 3434626
1573
1574 EXCEPTION
1575 WHEN OTHERS THEN
1576 g_error_api_path := SUBSTR('VLDT_TRNSCTN_CNTRLS:'||g_error_api_path,1,30);
1577 -- bug fix 2597666
1578 p_return_status := fnd_api.g_ret_sts_unexp_error;
1579 -- Introduced the following for bug fix 3434626
1580 hr_utility.trace(fnd_number.number_to_canonical(l_proc_step) || ': ' || l_proc_name);
1581 hr_utility.trace('Leaving ' || l_proc_name);
1582 -- End of changed for bug fix 3434626
1583 END validate_transaction_controls;
1584
1585 /****************************************************************************************************
1586 Procedure Name: INSERT_CHANGED_ASSIGNMENTS
1587 Purpose: This Procedure inserts identified assignments into psp_enc_changed_assignments
1588 table.
1589 ****************************************************************************************************/
1590
1591 PROCEDURE insert_changed_assignments (p_change_type IN VARCHAR2,
1592 p_reference_id IN NUMBER DEFAULT NULL,
1593 p_action_type IN VARCHAR2 DEFAULT NULL,
1594 p_return_status OUT NOCOPY VARCHAR2)
1595 IS
1596 -- Introduced for bug fix 3434626
1597 l_proc_name VARCHAR2(61) DEFAULT g_package_name || 'INSERT_CHANGED_ASSIGNMENTS';
1598 l_proc_step NUMBER(20, 10) DEFAULT 10;
1599 -- End of changes for bug fix 3434626
1600 BEGIN
1601 -- Introduced the following for bug fix 3434626
1602 hr_utility.trace('Entering ' || l_proc_name);
1603 hr_utility.trace('p_change_type ' || p_change_type || ' p_action_type ' || p_action_type ||
1604 ' p_reference_id: ' || fnd_number.number_to_canonical(p_reference_id) ||
1605 ' r_asg_id_array.r_asg_id.COUNT: ' || fnd_number.number_to_canonical(r_asg_id_array.r_asg_id.COUNT));
1606 -- End of bug fix 3434626
1607
1608 FORALL k in 1 .. r_asg_id_array.r_asg_id.count
1609 INSERT INTO PSP_ENC_CHANGED_ASSIGNMENTS
1610 (request_id, assignment_id, payroll_id,
1611 change_type, processed_flag, reference_id, action_type)
1612 VALUES (g_request_id, r_asg_id_array.r_asg_id(k), r_asg_id_array.r_payroll_id(k),
1613 p_change_type, NULL, p_reference_id, p_action_type);
1614
1615 r_asg_id_array.r_asg_id.delete; -- clear the array
1616 r_asg_id_array.r_payroll_id.delete;
1617
1618 p_return_status := fnd_api.g_ret_sts_success;
1619 -- Introduced the following for bug fix 3434626
1620 hr_utility.trace('Leaving ' || l_proc_name);
1621 -- End of changed for bug fix 3434626
1622 EXCEPTION
1623 WHEN OTHERS THEN
1624 g_error_api_path := SUBSTR('INSERT_CHANGED_ASSIGNMENTS:'||g_error_api_path,1,30);
1625 -- bug fix 2597666
1626 p_return_status := fnd_api.g_ret_sts_unexp_error;
1627 -- Introduced the following for bug fix 3434626
1628 hr_utility.trace(fnd_number.number_to_canonical(l_proc_step) || ': ' || l_proc_name);
1629 hr_utility.trace('Leaving ' || l_proc_name);
1630 -- End of changed for bug fix 3434626
1631 END insert_changed_assignments;
1632
1633 /****************************************************************************************************
1634 Procedure Name: LABOR_SCHEDULE_PRE_PROCESS
1635 Purpose: This procedure identifies the assignments that have to be processed during the
1636 Update run because of the changes in LS other than employee level and as well
1637 as in Enumbrance Payroll selection, Element selection forms.
1638 ****************************************************************************************************/
1639
1640 PROCEDURE labor_schedule_pre_process (p_enc_line_type IN VARCHAR2,
1641 p_payroll_id IN NUMBER,
1642 p_return_status OUT NOCOPY VARCHAR2)
1643 IS
1644 PRAGMA AUTONOMOUS_TRANSACTION;
1645 l_generic_suspense NUMBER(15) DEFAULT fnd_profile.value('PSP_GLOBAL_SUSP_ACC_ORG');
1646 l_default_account VARCHAR2(15) DEFAULT fnd_profile.value('PSP_DEFAULT_ACCOUNT');
1647 l_default_schedule VARCHAR2(15) DEFAULT fnd_profile.value('PSP_DEFAULT_SCHEDULE');
1648
1649 TYPE v_reference_id IS TABLE OF NUMBER(15);
1650 TYPE v_reference_field IS TABLE OF VARCHAR2(30);
1651 TYPE v_change_type IS TABLE OF VARCHAR2(2);
1652 TYPE v_action_type IS TABLE OF VARCHAR2(2);
1653 TYPE reference_id_array is RECORD
1654 (r_reference_id v_reference_id,
1655 r_reference_field v_reference_field,
1656 r_change_type v_change_type,
1657 r_action_type v_action_type);
1658 r_reference_id_array reference_id_array;
1659
1660 /* Following cursor finds out NOCOPY the distinct set of changes */
1661 CURSOR enc_changed_schedule_cur IS
1662 SELECT DISTINCT reference_id,
1663 pecs.reference_field,
1664 pecs.change_type,
1665 DECODE(action_type, 'D', 'U', action_type) action_type
1666 FROM psp_enc_changed_schedules pecs;
1667
1668 /* Following cursor finds out NOCOPY assignments affected by changes to Org Default LS */
1669 CURSOR assignment_payroll_ds_upd_cur(p_reference_id NUMBER) IS
1670 SELECT DISTINCT pelh.assignment_id,
1671 pelh.payroll_id
1672 FROM psp_enc_lines_history pelh
1673 WHERE pelh.change_flag = 'N'
1674 AND pelh.org_schedule_id = p_reference_id;
1675
1676 /* Following cursor finds out NOCOPY assignments affected by changes to Org Default Account */
1677 CURSOR assignment_payroll_da_upd_cur (p_reference_id NUMBER) IS
1678 SELECT DISTINCT pelh.assignment_id,
1679 pelh.payroll_id
1680 FROM psp_enc_lines_history pelh
1681 WHERE pelh.default_org_account_id = p_reference_id
1682 AND pelh.change_flag = 'N' ; --Added for bug 2334434;
1683
1684 /* Following cursor finds out NOCOPY assignments affected by changes to Org Suspense Account
1685 Same query is used for GS updates, hence this cursor will be reused */
1686 CURSOR assignment_payroll_sa_upd_cur (p_reference_id NUMBER) IS
1687 SELECT DISTINCT pelh.assignment_id,
1688 pelh.payroll_id
1689 FROM psp_enc_lines_history pelh
1690 WHERE pelh.change_flag = 'N'
1691 AND pelh.suspense_org_account_id = p_reference_id;
1692
1693 /* Following cursor finds out NOCOPY assignments affected by changes to Global Earning Elements */
1694 CURSOR assignment_payroll_ge_upd_cur (p_reference_id NUMBER) IS
1695 SELECT DISTINCT pelh.assignment_id,
1696 pelh.payroll_id
1697 FROM psp_enc_lines_history pelh
1698 WHERE pelh.change_flag = 'N'
1699 AND pelh.element_account_id = p_reference_id;
1700
1701 /* Following cursor finds out NOCOPY assignments affected by changes to Enc Payroll Assignments */
1702 CURSOR assignment_payroll_ex_upd_cur (p_reference_id NUMBER) IS
1703 SELECT DISTINCT pelh.assignment_id,
1704 pelh.payroll_id
1705 FROM psp_enc_lines_history pelh
1706 WHERE pelh.change_flag = 'N'
1707 AND pelh.assignment_id = p_reference_id;
1708
1709 /* Following cursor finds out NOCOPY impacted assignments for new Global Earning Elements */
1710 CURSOR global_element_insert_cur (p_reference_id NUMBER) IS
1711 SELECT DISTINCT pelh.assignment_id,
1712 pelh.payroll_id
1713 FROM psp_enc_lines_history pelh
1714 WHERE pelh.enc_element_type_id = p_reference_id
1715 AND pelh.element_account_id IS NULL
1716 AND pelh.change_flag = 'N';
1717
1718 /* Following cursor finds all assignments impacted because of Org. Default LS Inserts */
1719 CURSOR org_ds_insert_cur (p_reference_id NUMBER) IS
1720 SELECT DISTINCT pelh.assignment_id,
1721 pelh.payroll_id
1722 FROM psp_enc_lines_history pelh,
1723 per_assignments_f paf
1724 WHERE pelh.schedule_line_id IS NULL
1725 AND pelh.element_account_id IS NULL
1726 AND pelh.change_flag = 'N'
1727 AND pelh.assignment_id = paf.assignment_id
1728 AND paf.organization_id = p_reference_id
1729 /* Following code is modified for bug 2345584 */
1730 AND (default_reason_code IN(3,1)
1731 OR suspense_reason_code IN('LDM_NO_CI_FOUND','LDM_BAL_NOT_100_PERCENT'));
1732
1733 /* Following cursor would identify assignments impacted by Org. Default Account Inserts */
1734 CURSOR org_da_insert_cur (p_reference_id NUMBER) IS
1735 SELECT DISTINCT pelh.assignment_id,
1736 pelh.payroll_id
1737 FROM psp_enc_lines_history pelh,
1738 per_assignments_f paf
1739 WHERE pelh.suspense_org_account_id IS NOT NULL
1740 AND pelh.suspense_reason_code IN ('LDM_NO_CI_FOUND', 'LDM_BAL_NOT_100_PERCENT')
1741 AND paf.organization_id = p_reference_id
1742 AND pelh.assignment_id = paf.assignment_id
1743 AND pelh.change_flag = 'N';
1744
1745 /* Following cursor finds out NOCOPY assignments impacted because of Org. Suspense Accounts */
1746 CURSOR org_sa_insert_cur (p_reference_id NUMBER) IS
1747 SELECT DISTINCT pelh.assignment_id,
1748 pelh.payroll_id
1749 FROM psp_enc_lines_history pelh,
1750 per_assignments_f paf,
1751 psp_organization_accounts poa
1752 WHERE pelh.suspense_org_account_id = poa.organization_account_id
1753 AND poa.organization_id = l_generic_suspense
1754 AND pelh.change_flag = 'N'
1755 AND pelh.assignment_id = paf.assignment_id
1756 AND paf.organization_id = p_reference_id;
1757
1758 -- Introduced the following for bug fix 3426871
1759 l_period_end_date DATE;
1760 l_prev_enc_end_date DATE;
1761
1762 CURSOR default_end_date_cur (p_reference_id IN NUMBER) IS
1763 SELECT period_end_date,
1764 prev_enc_end_date
1765 FROM psp_enc_end_dates peed
1766 WHERE peed.enc_end_date_id = p_reference_id;
1767
1768 CURSOR default_end_date_dec_cur IS
1769 SELECT DISTINCT
1770 assignment_id,
1771 payroll_id
1772 FROM psp_enc_lines_history pelh
1773 -- psp_enc_end_dates peed Commented for bug fix 4507892
1774 WHERE pelh.change_flag = 'N'
1775 AND pelh.encumbrance_date > l_period_end_date;
1776 -- End of bug fix 3426871
1777
1778 /* Following cursor finds out NOCOPY assignments affected by Default Org. End Date changes */
1779 --CURSOR default_org_end_date_asg_cur (p_reference_id NUMBER) IS Commented for bug fix 3426871
1780 -- Removed p_reference_id for bug fix 3426871
1781 CURSOR default_org_end_date_asg_cur IS
1782 SELECT DISTINCT --Added distinct for bug 2664991.
1783 pelh.assignment_id,
1784 pelh.payroll_id
1785 FROM psp_enc_lines_history pelh,
1786 psp_default_labor_schedules pdls,
1787 psp_schedule_lines psl,
1788 psp_element_type_accounts peta,
1789 per_assignments_f paf
1790 -- psp_enc_end_dates peed Commented for bug fix 3426871
1791 --WHERE peed.enc_end_date_id = p_reference_id Commented for bug fix 3426871
1792 WHERE pelh.assignment_id = paf.assignment_id
1793 AND pelh.change_flag = 'N'
1794 AND pelh.gl_code_combination_id IS NULL
1795 AND pelh.encumbrance_date = (SELECT MIN(pelhin.encumbrance_date)
1796 FROM psp_enc_lines_history pelhin
1797 WHERE pelhin.assignment_id = pelh.assignment_id
1798 -- AND pelhin.encumbrance_date > peed.period_end_date)
1799 -- AND pelhin.encumbrance_date > peed.prev_enc_end_date) --Added for bug 2396983. Commented for bug 3426871
1800 AND pelhin.encumbrance_date > l_prev_enc_end_date) -- Introduced for bug fix 3426871
1801 AND pelh.org_schedule_id = pdls.org_schedule_id (+)
1802 AND pelh.element_account_id = peta.element_account_id (+)
1803 AND pelh.schedule_line_id = psl.schedule_line_id (+)
1804 AND pelh.encumbrance_date BETWEEN paf.effective_start_date AND paf.effective_end_date --Added for Bug 2675446
1805 GROUP BY pelh.assignment_id, pelh.payroll_id,
1806 pelh.enc_element_type_id --Added for bug 2664991.
1807 HAVING SUM(NVL(pdls.schedule_percent, 0) + NVL(psl.schedule_percent, 0) + NVL(peta.percent, 0)) <> 100
1808 AND SUM(NVL(pdls.schedule_percent, 0) + NVL(psl.schedule_percent, 0) + NVL(peta.percent, 0)) > 0;
1809
1810 --For Bug fix 2370841:Following cursor finds out NOCOPY assignments affected by Enc element Selection changes
1811 CURSOR element_ed_ins_upd_cur (p_reference_id NUMBER) IS
1812 SELECT DISTINCT pelh.assignment_id,
1813 pelh.payroll_id
1814 FROM psp_enc_lines_history pelh
1815 WHERE pelh.change_flag = 'N'
1816 AND pelh.enc_element_type_id = p_reference_id;
1817 --End of bug fix 2370841
1818
1819 -- Introduced for bug fix 3434626
1820 l_proc_name VARCHAR2(61) DEFAULT g_package_name || 'LABOR_SCHEDULE_PRE_PROCESS';
1821 l_proc_step NUMBER(20, 10) DEFAULT 0;
1822 -- End of changes for bug fix 3434626
1823 BEGIN
1824 -- Introduced the following for bug fix 3434626
1825 hr_utility.trace('Entering ' || l_proc_name);
1826 hr_utility.trace('p_enc_line_type: ' || p_enc_line_type || ' p_payroll_id: ' || fnd_number.number_to_canonical(p_payroll_id));
1827
1828 l_proc_step := 10;
1829 -- End of bug fix 3434626
1830
1831 IF (p_enc_line_type = 'Q') THEN
1832 UPDATE psp_enc_changed_assignments peca
1833 SET request_id = g_request_id
1834 WHERE peca.payroll_id = p_payroll_id
1835 AND change_type IN ('AS', 'ET', 'LS', 'QU');
1836
1837 p_return_status := fnd_api.g_ret_sts_success;
1838 -- Introduced the following for bug fix 3434626
1839 hr_utility.trace('Leaving: ' || l_proc_name);
1840 -- End of changed for bug fix 3434626
1841
1842 RETURN;
1843 ELSE
1844 UPDATE psp_enc_changed_assignments peca
1845 SET request_id = g_request_id
1846 WHERE peca.payroll_id = p_payroll_id;
1847
1848 -- Introduced the following for bug fix 3434626
1849 l_proc_step := 20;
1850 -- End of bug fix 3434626
1851 END IF;
1852
1853 -- Introduced the following for bug fix 3434626
1854 l_proc_step := 30;
1855 -- End of bug fix 3434626
1856
1857 OPEN enc_changed_schedule_cur;
1858 FETCH enc_changed_schedule_cur
1859 BULK COLLECT INTO r_reference_id_array.r_reference_id,
1860 r_reference_id_array.r_reference_field, r_reference_id_array.r_change_type,
1861 r_reference_id_array.r_action_type;
1862 CLOSE enc_changed_schedule_cur;
1863
1864 -- Introduced the following for bug fix 3434626
1865 hr_utility.trace('r_reference_id_array.r_reference_id.COUNT: ' || fnd_number.number_to_canonical(r_reference_id_array.r_reference_id.COUNT));
1866
1867 l_proc_step := 40;
1868 -- End of bug fix 3434626
1869
1870 FOR I in 1 .. r_reference_id_array.r_reference_id.COUNT
1871 LOOP
1872 -- Introduced the following for bug fix 3434626
1873 hr_utility.trace('I: ' || fnd_number.number_to_canonical(I) ||
1874 ' r_reference_id_array.r_reference_id(I): ' || fnd_number.number_to_canonical(r_reference_id_array.r_reference_id(I)) ||
1875 ' r_reference_id_array.r_reference_field(I): ' || r_reference_id_array.r_reference_field(I) ||
1876 ' r_reference_id_array.r_change_type(I): ' || r_reference_id_array.r_change_type(I) ||
1877 ' r_reference_id_array.r_action_type(I): ' || r_reference_id_array.r_action_type(I));
1878
1879 l_proc_step := 50 + (I/100000);
1880 -- End of bug fix 3434626
1881
1882 /* Verifying assignments affected by Org Default Account Changes */
1883 IF (r_reference_id_array.r_change_type(i) = 'DA') AND
1884 (r_reference_id_array.r_action_type(i) = 'U') THEN
1885 l_proc_step := 60 + (I/100000); -- Introduced for bug fix 3434626
1886
1887 IF (l_default_account = 'Y') THEN
1888 OPEN assignment_payroll_da_upd_cur(r_reference_id_array.r_reference_id(i));
1889 FETCH assignment_payroll_da_upd_cur
1890 BULK COLLECT INTO r_asg_id_array.r_asg_id, r_asg_id_array.r_payroll_id;
1891 CLOSE assignment_payroll_da_upd_cur;
1892
1893 -- Introduced the following for bug fix 3434626
1894 hr_utility.trace('r_asg_id_array.r_asg_id.COUNT: ' || fnd_number.number_to_canonical(r_asg_id_array.r_asg_id.COUNT));
1895 -- End of bug fix 3434626
1896
1897 insert_changed_assignments(p_change_type => 'DA',
1898 p_reference_id => r_reference_id_array.r_reference_id(i),
1899 p_action_type => r_reference_id_array.r_action_type(i),
1900 p_return_status => p_return_status);
1901 IF (p_return_status <> fnd_api.g_ret_sts_success) THEN
1902 RAISE fnd_api.g_exc_unexpected_error;
1903 END IF;
1904 END IF;
1905 /* Verifying Org. Default Account Inserts */
1906 ELSIF (r_reference_id_array.r_change_type(i) = 'DA') AND
1907 (r_reference_id_array.r_action_type(i) = 'I') THEN
1908 l_proc_step := 70 + (I/100000); -- Introduced for bug fix 3434626
1909
1910 IF (l_default_account = 'Y') THEN
1911 OPEN org_da_insert_cur(r_reference_id_array.r_reference_id(i));
1912 FETCH org_da_insert_cur
1913 BULK COLLECT INTO r_asg_id_array.r_asg_id,
1914 r_asg_id_array.r_payroll_id;
1915 CLOSE org_da_insert_cur;
1916
1917 -- Introduced the following for bug fix 3434626
1918 hr_utility.trace('r_asg_id_array.r_asg_id.COUNT: ' || fnd_number.number_to_canonical(r_asg_id_array.r_asg_id.COUNT));
1919 -- End of bug fix 3434626
1920
1921 insert_changed_assignments(p_change_type => 'DA',
1922 p_reference_id => r_reference_id_array.r_reference_id(i),
1923 p_action_type => r_reference_id_array.r_action_type(i),
1924 p_return_status => p_return_status);
1925 IF (p_return_status <> fnd_api.g_ret_sts_success) THEN
1926 RAISE fnd_api.g_exc_unexpected_error;
1927 END IF;
1928 END IF;
1929
1930 /* Verifying assignments affected by Org Suspense Account Changes
1931 This section also takes care of GS updates */
1932 ELSIF (r_reference_id_array.r_change_type(i) IN ('GS', 'SA')) AND
1933 (r_reference_id_array.r_action_type(i) = 'U') THEN
1934 l_proc_step := 80 + (I/100000); -- Introduced for bug fix 3434626
1935 OPEN assignment_payroll_sa_upd_cur(r_reference_id_array.r_reference_id(i));
1936 FETCH assignment_payroll_sa_upd_cur
1937 BULK COLLECT INTO r_asg_id_array.r_asg_id,
1938 r_asg_id_array.r_payroll_id;
1939 CLOSE assignment_payroll_sa_upd_cur;
1940
1941 -- Introduced the following for bug fix 3434626
1942 hr_utility.trace('r_asg_id_array.r_asg_id.COUNT: ' || fnd_number.number_to_canonical(r_asg_id_array.r_asg_id.COUNT));
1943 -- End of bug fix 3434626
1944
1945 insert_changed_assignments(p_change_type => r_reference_id_array.r_change_type(i),
1946 p_reference_id => r_reference_id_array.r_reference_id(i),
1947 p_action_type => r_reference_id_array.r_action_type(i),
1948 p_return_status => p_return_status);
1949 IF (p_return_status <> fnd_api.g_ret_sts_success) THEN
1950 RAISE fnd_api.g_exc_unexpected_error;
1951 END IF;
1952
1953 /* Verifying Org. Suspense Account Inserts */
1954 ELSIF (r_reference_id_array.r_change_type(i) = 'SA') AND
1955 (r_reference_id_array.r_action_type(i) = 'I') THEN
1956 l_proc_step := 90 + (I/100000); -- Introduced for bug fix 3434626
1957 OPEN org_sa_insert_cur(r_reference_id_array.r_reference_id(i));
1958 FETCH org_sa_insert_cur
1959 BULK COLLECT INTO r_asg_id_array.r_asg_id,
1960 r_asg_id_array.r_payroll_id;
1961 CLOSE org_sa_insert_cur;
1962
1963 -- Introduced the following for bug fix 3434626
1964 hr_utility.trace('r_asg_id_array.r_asg_id.COUNT: ' || fnd_number.number_to_canonical(r_asg_id_array.r_asg_id.COUNT));
1965 -- End of bug fix 3434626
1966
1967 insert_changed_assignments(p_change_type => 'SA',
1968 p_reference_id => r_reference_id_array.r_reference_id(i),
1969 p_action_type => r_reference_id_array.r_action_type(i),
1970 p_return_status => p_return_status);
1971 IF (p_return_status <> fnd_api.g_ret_sts_success) THEN
1972 RAISE fnd_api.g_exc_unexpected_error;
1973 END IF;
1974
1975 /* Verifying assignments affected by Org Default LS Changes */
1976 ELSIF (r_reference_id_array.r_change_type(i) = 'DS') AND
1977 (r_reference_id_array.r_action_type(i) = 'U') THEN
1978 l_proc_step := 100 + (I/100000); -- Introduced for bug fix 3434626
1979 IF (l_default_schedule = 'Y') THEN
1980 OPEN assignment_payroll_ds_upd_cur(r_reference_id_array.r_reference_id(i));
1981 FETCH assignment_payroll_ds_upd_cur
1982 BULK COLLECT INTO r_asg_id_array.r_asg_id,
1983 r_asg_id_array.r_payroll_id;
1984 CLOSE assignment_payroll_ds_upd_cur;
1985 -- Introduced the following for bug fix 3434626
1986 hr_utility.trace('r_asg_id_array.r_asg_id.COUNT: ' || fnd_number.number_to_canonical(r_asg_id_array.r_asg_id.COUNT));
1987 -- End of bug fix 3434626
1988
1989 insert_changed_assignments(p_change_type => 'DS',
1990 p_reference_id => r_reference_id_array.r_reference_id(i),
1991 p_action_type => r_reference_id_array.r_action_type(i),
1992 p_return_status => p_return_status);
1993 IF (p_return_status <> fnd_api.g_ret_sts_success) THEN
1994 RAISE fnd_api.g_exc_unexpected_error;
1995 END IF;
1996 END IF;
1997
1998 /* Verifying Org Default LS Inserts */
1999 ELSIF (r_reference_id_array.r_change_type(i) = 'DS') AND
2000 (r_reference_id_array.r_action_type(i) = 'I') THEN
2001 IF (l_default_schedule = 'Y') THEN
2002 l_proc_step := 110 + (I/100000); -- Introduced for bug fix 3434626
2003 OPEN org_ds_insert_cur(r_reference_id_array.r_reference_id(i));
2004 FETCH org_ds_insert_cur
2005 BULK COLLECT INTO r_asg_id_array.r_asg_id,
2006 r_asg_id_array.r_payroll_id;
2007 CLOSE org_ds_insert_cur;
2008
2009 -- Introduced the following for bug fix 3434626
2010 hr_utility.trace('r_asg_id_array.r_asg_id.COUNT: ' || fnd_number.number_to_canonical(r_asg_id_array.r_asg_id.COUNT));
2011 -- End of bug fix 3434626
2012
2013 insert_changed_assignments(p_change_type => 'DS',
2014 p_reference_id => r_reference_id_array.r_reference_id(i),
2015 p_action_type => r_reference_id_array.r_action_type(i),
2016 p_return_status => p_return_status);
2017 IF (p_return_status <> fnd_api.g_ret_sts_success) THEN
2018 RAISE fnd_api.g_exc_unexpected_error;
2019 END IF;
2020 END IF;
2021
2022 /* Verifying assignments affected by Global Earning Elements Changes */
2023 ELSIF (r_reference_id_array.r_change_type(i) = 'GE') AND
2024 (r_reference_id_array.r_action_type(i) = 'U') THEN
2025 l_proc_step := 120 + (I/100000); -- Introduced for bug fix 3434626
2026 OPEN assignment_payroll_ge_upd_cur(r_reference_id_array.r_reference_id(i));
2027 FETCH assignment_payroll_ge_upd_cur
2028 BULK COLLECT INTO r_asg_id_array.r_asg_id,
2029 r_asg_id_array.r_payroll_id;
2030 CLOSE assignment_payroll_ge_upd_cur;
2031 -- Introduced the following for bug fix 3434626
2032 hr_utility.trace('r_asg_id_array.r_asg_id.COUNT: ' || fnd_number.number_to_canonical(r_asg_id_array.r_asg_id.COUNT));
2033 -- End of bug fix 3434626
2034
2035 insert_changed_assignments(p_change_type => 'GE',
2036 p_reference_id => r_reference_id_array.r_reference_id(i),
2037 p_action_type => r_reference_id_array.r_action_type(i),
2038 p_return_status => p_return_status);
2039 IF (p_return_status <> fnd_api.g_ret_sts_success) THEN
2040 RAISE fnd_api.g_exc_unexpected_error;
2041 END IF;
2042
2043 /* Verifying Global Earning Elements Inserts */
2044 ELSIF (r_reference_id_array.r_change_type(i) = 'GE') AND
2045 (r_reference_id_array.r_action_type(i) = 'I') THEN
2046 l_proc_step := 130 + (I/100000); -- Introduced for bug fix 3434626
2047 OPEN global_element_insert_cur(r_reference_id_array.r_reference_id(i));
2048 FETCH global_element_insert_cur
2049 BULK COLLECT INTO r_asg_id_array.r_asg_id,
2050 r_asg_id_array.r_payroll_id;
2051 CLOSE global_element_insert_cur;
2052 -- Introduced the following for bug fix 3434626
2053 hr_utility.trace('r_asg_id_array.r_asg_id.COUNT: ' || fnd_number.number_to_canonical(r_asg_id_array.r_asg_id.COUNT));
2054 -- End of bug fix 3434626
2055
2056 insert_changed_assignments(p_change_type => 'GE',
2057 p_reference_id => r_reference_id_array.r_reference_id(i),
2058 p_action_type => r_reference_id_array.r_action_type(i),
2059 p_return_status => p_return_status);
2060 IF (p_return_status <> fnd_api.g_ret_sts_success) THEN
2061 RAISE fnd_api.g_exc_unexpected_error;
2062 END IF;
2063
2064 /* Verifying assignments affected by Enc Payroll Assignments Changes */
2065 ELSIF (r_reference_id_array.r_change_type(i) = 'EX') THEN
2066 l_proc_step := 140 + (I/100000); -- Introduced for bug fix 3434626
2067 OPEN assignment_payroll_ex_upd_cur(r_reference_id_array.r_reference_id(i));
2068 FETCH assignment_payroll_ex_upd_cur
2069 BULK COLLECT INTO r_asg_id_array.r_asg_id,
2070 r_asg_id_array.r_payroll_id;
2071 CLOSE assignment_payroll_ex_upd_cur;
2072 -- Introduced the following for bug fix 3434626
2073 hr_utility.trace('r_asg_id_array.r_asg_id.COUNT: ' || fnd_number.number_to_canonical(r_asg_id_array.r_asg_id.COUNT));
2074 -- End of bug fix 3434626
2075
2076 insert_changed_assignments(p_change_type => 'EX',
2077 p_reference_id => r_reference_id_array.r_reference_id(i),
2078 p_action_type => r_reference_id_array.r_action_type(i),
2079 p_return_status => p_return_status);
2080 IF (p_return_status <> fnd_api.g_ret_sts_success) THEN
2081 RAISE fnd_api.g_exc_unexpected_error;
2082 END IF;
2083
2084 /* Verifying assignments affected by Default Org End Date Changes */
2085 ELSIF (r_reference_id_array.r_change_type(i) = 'OE') THEN
2086 l_proc_step := 150 + (I/100000); -- Introduced for bug fix 3434626
2087 -- Introduced the following for bug fix 3426871
2088 OPEN default_end_date_cur(r_reference_id_array.r_reference_id(i));
2089 FETCH default_end_date_cur INTO l_period_end_date, l_prev_enc_end_date;
2090 CLOSE default_end_date_cur;
2091
2092 IF (l_period_end_date < l_prev_enc_end_date) THEN
2093 OPEN default_end_date_dec_cur;
2094 FETCH default_end_date_dec_cur
2095 BULK COLLECT INTO r_asg_id_array.r_asg_id,
2096 r_asg_id_array.r_payroll_id;
2097 CLOSE default_end_date_dec_cur;
2098 ELSE
2099 -- End of bug fix 3426871
2100 -- OPEN default_org_end_date_asg_cur(r_reference_id_array.r_reference_id(i)); Commented for bug 3426871
2101 OPEN default_org_end_date_asg_cur; -- Introduced for bug fix 3426871
2102 FETCH default_org_end_date_asg_cur
2103 BULK COLLECT INTO r_asg_id_array.r_asg_id,
2104 r_asg_id_array.r_payroll_id;
2105 CLOSE default_org_end_date_asg_cur;
2106 END IF; -- Introduced for bug fix 3426871
2107
2108 -- Introduced the following for bug fix 3434626
2109 hr_utility.trace('r_asg_id_array.r_asg_id.COUNT: ' || fnd_number.number_to_canonical(r_asg_id_array.r_asg_id.COUNT));
2110 -- End of bug fix 3434626
2111
2112 insert_changed_assignments(p_change_type => 'OE',
2113 p_reference_id => r_reference_id_array.r_reference_id(i),
2114 p_action_type => r_reference_id_array.r_action_type(i),
2115 p_return_status => p_return_status);
2116 IF (p_return_status <> fnd_api.g_ret_sts_success) THEN
2117 RAISE fnd_api.g_exc_unexpected_error;
2118 END IF;
2119
2120 ---For Bug fix:2370841-Verifying assignments affected by Element Selection Changes
2121 ELSIF (r_reference_id_array.r_change_type(i) = 'ED') THEN
2122 l_proc_step := 160 + (I/100000); -- Introduced for bug fix 3434626
2123 OPEN element_ed_ins_upd_cur(r_reference_id_array.r_reference_id(i));
2124 FETCH element_ed_ins_upd_cur
2125 BULK COLLECT INTO r_asg_id_array.r_asg_id,
2126 r_asg_id_array.r_payroll_id;
2127 CLOSE element_ed_ins_upd_cur;
2128
2129 -- Introduced the following for bug fix 3434626
2130 hr_utility.trace('r_asg_id_array.r_asg_id.COUNT: ' || fnd_number.number_to_canonical(r_asg_id_array.r_asg_id.COUNT));
2131 -- End of bug fix 3434626
2132
2133 insert_changed_assignments(p_change_type => 'ED',
2134 p_reference_id => r_reference_id_array.r_reference_id(i),
2135 p_action_type => r_reference_id_array.r_action_type(i),
2136 p_return_status => p_return_status);
2137 IF (p_return_status <> fnd_api.g_ret_sts_success) THEN
2138 RAISE fnd_api.g_exc_unexpected_error;
2139 END IF;
2140 --End of Changes for the bug fix
2141 END IF;
2142 END LOOP; -- End of Enc Change Schedules loop
2143
2144 l_proc_step := 170; -- Introduced for bug fix 3434626
2145
2146 FORALL I IN 1 .. r_reference_id_array.r_reference_id.COUNT
2147 INSERT INTO psp_enc_changed_sch_history
2148 (request_id, reference_id,
2149 change_type, action_type,
2150 reference_field)
2151 VALUES (g_request_id, r_reference_id_array.r_reference_id(i),
2152 r_reference_id_array.r_change_type(i), r_reference_id_array.r_action_type(i),
2153 r_reference_id_array.r_reference_field(i));
2154
2155 l_proc_step := 180; -- Introduced for bug fix 3434626
2156
2157 r_reference_id_array.r_reference_id.DELETE;
2158 r_reference_id_array.r_change_type.DELETE;
2159 r_reference_id_array.r_reference_field.DELETE;
2160 r_reference_id_array.r_action_type.DELETE;
2161
2162 DELETE psp_enc_changed_schedules;
2163
2164 l_proc_step := 190; -- Introduced for bug fix 3434626
2165
2166 COMMIT;
2167
2168 l_proc_step := 200; -- Introduced for bug fix 3434626
2169
2170 p_return_status := fnd_api.g_ret_sts_success;
2171 -- Introduced the following for bug fix 3434626
2172 hr_utility.trace('Leaving: ' || l_proc_name);
2173 -- End of changed for bug fix 3434626
2174 EXCEPTION
2175 WHEN OTHERS THEN
2176 g_error_api_path := 'PSP_ENC_PRE_PROCESS : LABOR_SCHEDULE_PRE_PROCESS';
2177 fnd_msg_pub.add_exc_msg('PSP_ENC_PRE_PROCESS', 'LABOR_SCHEDULE_PRE_PROCESS');
2178 ROLLBACK;
2179 p_return_status := fnd_api.g_ret_sts_unexp_error;
2180 -- Introduced the following for bug fix 3434626
2181 hr_utility.trace(fnd_number.number_to_canonical(l_proc_step) || ': ' || l_proc_name);
2182 hr_utility.trace('Leaving: ' || l_proc_name);
2183 -- End of changed for bug fix 3434626
2184 END labor_schedule_pre_process;
2185
2186 END psp_enc_pre_process;