1 PACKAGE BODY PSP_ENC_UPDATE_LINES AS
2 /* $Header: PSPENUPB.pls 120.3 2006/02/22 05:16:55 spchakra noship $ */
3
4 -- Global Variable for referring REquest ID through out the package, Introduce for bug 2259310
5 g_request_id NUMBER DEFAULT fnd_global.conc_request_id;
6 g_liquidate_flag varchar2(1); --- 3953230
7
8 Procedure Update_Enc_lines (errbuf out NOCOPY varchar2,
9 retcode out NOCOPY varchar2,
10 p_payroll_id IN Number,
11 p_enc_line_type IN VARCHAR2,
12 p_business_group_id IN Number,
13 p_set_of_books_id IN Number) IS
14 BEGIN
15 NULL;
16 END;
17
18 Procedure verify_changes (p_payroll_id IN NUMBER,
19 p_business_group_id IN NUMBER,
20 p_set_of_books_id IN NUMBER,
21 p_enc_line_type IN VARCHAR2, --Added for bug 2143723.
22 l_retcode OUT NOCOPY VARCHAR2) IS
23 BEGIN
24 NULL;
25 END;
26
27 PROCEDURE move_qkupd_rec_to_hist( p_payroll_id IN NUMBER,
28 p_enc_line_type IN VARCHAR2,
29 p_business_group_id IN NUMBER,
30 p_set_of_books_id IN NUMBER,
31 p_return_status OUT NOCOPY VARCHAR2) IS
32 BEGIN
33 NULL;
34 END;
35
36 PROCEDURE cleanup_on_success ( p_enc_line_type IN VARCHAR2,
37 p_payroll_id IN NUMBER,
38 p_business_group_id IN NUMBER,
39 p_set_of_books_id IN NUMBER,
40 p_invalid_suspense IN VARCHAR2,
41 p_return_status OUT NOCOPY VARCHAR2) IS
42 BEGIN
43 NULL;
44 END;
45
46 PROCEDURE ROLLBACK_REJECTED_ASG (p_payroll_id in integer,
47 p_action_type in varchar2,
48 p_gms_batch_name in varchar2,
49 p_accepted_group_id in integer,
50 p_rejected_group_id in integer,
51 p_run_id in integer,
52 p_business_group_id in integer,
53 p_set_of_books_id in integer,
54 p_return_status out nocopy varchar2) IS
55 BEGIN
56 NULL;
57 END;
58
59 /***** Commented for bug fix 3434626
60 --Following cursor is added to convert select stmt into a cursor, Enh .Quick Update Design.
61 uncommented for bug fix 3684930 *** /
62 CURSOR c_unsummarized_lines IS
63 SELECT DISTINCT action_code
64 FROM psp_enc_controls
65 WHERE action_code = 'N'
66 AND payroll_id=p_payroll_id;
67
68 / * Following cursors are added for Enh. Restart Update Enc Proces * /
69 CURSOR inprogress_count_cur IS
70 --SELECT count(*) Commented for bug fix 3434626
71 SELECT DISTINCT action_code
72 FROM psp_enc_controls
73 WHERE action_type in ('U','N','Q')
74 AND action_code IN ('IU' , 'IC') -- Introduced 'IC' check for bug fix 3434626
75 / * action code = 'IU' means Failure due to db crash * /
76 AND payroll_id = p_payroll_id
77 AND business_group_id = p_business_group_id
78 AND set_of_books_id = p_set_of_books_id
79 ORDER BY action_code DESC; -- Introduced for bug fix 3434626
80
81 / ***** Changed the following cursor for bug fix 3434626
82 CURSOR action_type_inprogress IS
83 SELECT NVL(action_type, p_enc_line_type)
84 FROM psp_enc_controls
85 WHERE action_code = 'IC'
86 AND payroll_id = p_payroll_id
87 AND business_group_id = p_business_group_id
88 AND set_of_books_id = p_set_of_books_id
89 AND rownum = 1; End of comment for bug fix 3434626 ***** /
90
91 CURSOR action_type_inprogress IS
92 SELECT processed_flag
93 FROM psp_enc_changed_assignments peca
94 WHERE processed_flag IS NOT NULL
95 AND payroll_id = p_payroll_id
96 AND ROWNUM = 1;
97 -- End of changes for bug fix 3434626
98
99 --Modifying the cursor for Bug 2345813 : Introducing assignment_id check in the cursor
100 CURSOR count_change_flag IS
101 SELECT count(change_flag)
102 FROM psp_enc_lines_history pelh,
103 psp_enc_changed_assignments peca
104 WHERE pelh.change_flag = 'N'
105 AND pelh.payroll_id = p_payroll_id
106 AND rownum = 1
107 AND pelh.assignment_id = peca.assignment_id
108 AND peca.payroll_id = p_payroll_id
109 AND peca.request_id IS NOT NULL
110 AND ((p_enc_line_type = 'Q' AND peca.change_type IN ('LS', 'ET', 'AS', 'QU'))
111 OR p_enc_line_type = 'U');
112
113 l_errbuf VARCHAR2(2000);
114 l_retcode VARCHAR2(30);
115 l_msg_count NUMBER;
116 l_msg_data VARCHAR2(2000);
117 l_success VARCHAR2(1) DEFAULT 'F';
118 l_success_code VARCHAR2(200);
119 l_action_code VARCHAR2(1);
120 l_chg_count NUMBER DEFAULT 0;
121 l_new_line_count NUMBER DEFAULT 0;
122
123 / * Following Variables are added for Enh. Restart Update Enc Process * /
124 --l_inprogress_count NUMBER DEFAULT -1; -- Added for Restart, count in-progress control recs Commented for bug fix 3434626
125 l_inprogress_actioncode CHAR(2); -- Introduced for bug fixs 3434626
126 l_create_inprogress NUMBER DEFAULT 0; /* count of inprogress create control records
127 action code = 'IC' * /
128 l_enc_line_type VARCHAR2(1) DEFAULT p_enc_line_type; / * to derive Q or U for restart purposes * /
129 l_enc_line_type_dsc VARCHAR2(80);
130 -- Introduced the following for bug fix 3434626
131 CURSOR enc_line_type_cur IS
132 SELECT meaning
133 FROM fnd_lookups
134 WHERE lookup_type = 'PSP_ENC_LINE_TYPES'
135 AND lookup_code = l_enc_line_type;
136 -- End of bug fix 3434626
137
138 -- Introduced the following for bg fix 4625734
139 l_liquidate_request_id NUMBER(15);
140 l_person_id NUMBER(15);
141 l_full_name VARCHAR2(240);
142 l_termination_date DATE;
143
144 CURSOR emp_term_inprogress_cur IS
145 SELECT DISTINCT liquidate_request_id
146 FROM psp_enc_controls
147 WHERE payroll_id = p_payroll_id
148 AND action_code = 'IT';
149
150 CURSOR get_term_employee_cur IS
151 SELECT TO_NUMBER(argument3),
152 fnd_date.canonical_to_date(fnd_date.date_to_canonical(argument4))
153 FROM fnd_concurrent_requests
154 WHERE request_id = l_liquidate_request_id;
155
156 CURSOR get_full_name_cur IS
157 SELECT full_name
158 FROM per_people_f
159 WHERE person_id = l_person_id
160 AND l_termination_date BETWEEN effective_start_date and effective_end_date;
161 -- End of changes for bug fix 4625734
162 BEGIN
163 ----hr_utility.trace_on('Y','ENC');
164 -- Introduced the following for bg fix 4625734
165 OPEN emp_term_inprogress_cur;
166 FETCH emp_term_inprogress_cur INTO l_liquidate_request_id;
167 CLOSE emp_term_inprogress_cur;
168
169 IF (NVL(l_liquidate_request_id, 0) > 0) THEN
170 OPEN get_term_employee_cur;
171 FETCH get_term_employee_cur INTO l_person_id, l_termination_date;
172 CLOSE get_term_employee_cur;
173
174 OPEN get_full_name_cur;
175 FETCH get_full_name_cur INTO l_full_name;
176 CLOSE get_full_name_cur;
177
178 fnd_message.set_name('PSP', 'PSP_ENC_LIQ_TERM_EMP_PENDING');
179 fnd_message.set_token('PERSON', l_full_name);
180 fnd_message.set_token('TERMDATE', l_termination_date);
181 fnd_msg_pub.add;
182 RAISE fnd_api.g_exc_unexpected_error;
183 END IF;
184 -- End of changes for bug fix 4625734
185 / * Following code is added for Enh.Restart Update Process. * /
186 OPEN inprogress_count_cur;
187 FETCH inprogress_count_cur INTO l_inprogress_actioncode; -- Changed to actioncode for bug fix 3434626
188 -- CLOSE inprogress_count_cur; Commented for bug fix 3434626
189
190 IF (inprogress_count_cur%FOUND) THEN -- Introduced for bug fix 3434626
191 OPEN action_type_inprogress;
192 FETCH action_type_inprogress INTO l_enc_line_type;
193 CLOSE action_type_inprogress;
194
195 -- Introduced for bug fix 3434626
196 IF (l_enc_line_type <> p_enc_line_type) THEN
197 OPEN enc_line_type_cur;
198 FETCH enc_line_type_cur INTO l_enc_line_type_dsc;
199 CLOSE enc_line_type_cur;
200
201 fnd_message.set_name('PSP', 'PSP_ENC_SUBMIT_CORRECT_PROCESS');
202 fnd_message.set_token('LINE_TYPE', l_enc_line_type_dsc);
203 fnd_msg_pub.add;
204 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
205 END IF;
206 -- End of changes for bug fix 3434626
207
208 / ***** Commented for bug fix 3434626
209 IF action_type_inprogress%NOTFOUND THEN
210 IF l_inprogress_count >0 THEN
211 -- this case should not arise unless some data corruption
212 g_error_api_path := 'No_create_rec_found'||g_error_api_path;
213 fnd_msg_pub.add_exc_msg('PSP_ENC_UPDATE_LINES',g_error_api_path); --- added this line for 2444657
214 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
215 END IF;
216 ELSE
217 End of bug fix 3434626 ***** /
218 -- Introduced for bug fix 3434626
219 l_create_inprogress := -1;
220 IF (l_inprogress_actioncode = 'IC') THEN
221 l_create_inprogress := 1;
222 END IF;
223 -- End of changes for bug fix 3434626
224
225 END IF; -- Introduced for bug fix 3434626
226 CLOSE inprogress_count_cur; -- Introduced for bug fix 3434626
227
228 IF l_create_inprogress = 0 then
229 -- Introduced thr following for bug fix 3434626
230 UPDATE psp_enc_changed_assignments
231 SET processed_flag = p_enc_line_type
232 WHERE payroll_id = p_payroll_id
233 AND p_enc_line_type = 'U'
234 OR ( p_enc_line_type = 'Q'
235 AND change_type IN ('AS', 'LS', 'ET', 'QU'));
236 -- End of bug fix 3434626
237
238 / ** re-introduced following check for 3684930 ** /
239 OPEN c_unsummarized_lines;
240 FETCH c_unsummarized_lines INTO l_action_code;
241 IF c_unsummarized_lines%FOUND THEN
242 fnd_message.Set_name('PSP','PSP_ENC_NO_LIN_UPD');
243 fnd_msg_pub.add;
244 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
245 END IF;
246
247 psp_enc_create_lines.create_enc_lines (errbuf => l_errbuf,
248 retcode => l_retcode,
249 p_payroll_id => p_payroll_id,
250 p_enc_line_type => p_enc_line_type,
251 p_business_group_id => p_business_group_id,
252 p_set_of_books_id => p_set_of_books_id);
253 IF (l_retcode <> FND_API.G_RET_STS_SUCCESS ) THEN
254 g_error_api_path := 'CREATE_ENC_LINES:'||g_error_api_path;
255 fnd_message.set_name ('PSP','PSP_ENC_ENCUMBRANCE_FAILED');
256 fnd_msg_pub.add;
257 / * commented following proc Restart Update Enc process * /
258 / * clean_up_when_error; * /
259 psp_message_s.print_error(P_MODE => FND_FILE.LOG,
260 p_print_header => FND_API.G_TRUE);
261 -- CLOSE c_unsummarized_lines; Commented for bug fix 3434626
262 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
263 END IF;
264 l_create_inprogress := 1; -- Introduced fro bug fix 3434626
265 -- END IF; Commented for bug fix 3434626
266
267 END IF; / * create_inprogress_count ,Enh. Restart Update Encumbrance Proecss * /
268
269 -- IF l_inprogress_count = 0 then Commented for bug fix 3434626
270 IF (l_create_inprogress = 1) THEN
271
272 -- Gathering Statistics for Bug 3821553.
273 begin
274 FND_STATS.Gather_Table_Stats(ownname => 'PSP',
275 tabname => 'PSP_ENC_CONTROLS');
276
277 FND_STATS.Gather_Table_Stats(ownname => 'PSP',
278 tabname => 'PSP_ENC_LINES');
279
280 FND_STATS.Gather_Table_Stats(ownname => 'PSP',
281 tabname => 'PSP_ENC_LINES_HISTORY');
282
283 exception
284 when others then
285 null;
286
287 end;
288 -- End of gather statistics for Bug 3821553
289 -- no control records in limbo due to previous failed run.
290 -- If create is successful call the verify program with payroll_id
291 verify_changes(p_payroll_id,
292 p_business_group_id ,
293 p_set_of_books_id,
294 -- p_enc_line_type,
295 l_enc_line_type,
296 / * changed p_enc_line_type to l_enc_line_type for Restart * /
297 l_retcode);
298
299 IF (l_retcode <> FND_API.G_RET_STS_SUCCESS ) THEN
300 fnd_message.set_name ('PSP','PSP_ENC_VERIFY_FAILED');
301 fnd_msg_pub.add;
302 / * commented following proc Restart Update Enc process * /
303 / * clean_up_when_error; * /
304 psp_message_s.print_error(p_mode => FND_FILE.LOG,
305 p_print_header => FND_API.G_TRUE);
306 g_error_api_path := 'VERIFY_CHANGES:'||g_error_api_path;
307 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
308 END IF;
309 END IF;
310 ---- 3953230
311 If nvl(g_liquidate_flag ,'N') = 'Y' then
312 l_chg_count := 1;
313 else
314 -- Call the liquidate program with payroll_id and action_type = 'U'
315 OPEN count_change_flag ;
316 FETCH count_change_flag INTO l_chg_count;
317 CLOSE count_change_flag;
318 end if;
319 IF l_chg_count <> 0 THEN
320
321 -- Introduced the folowing code for Bug 3821553
322 -- did a gather statistics on PSP_ENC_LINES table
323
324 begin
325 FND_STATS.Gather_Table_Stats(ownname => 'PSP',
326 tabname => 'PSP_ENC_LINES_HISTORY');
327
328
329 exception
330 when others then
331 null;
332
333 end;
334
335 -- End of Code changes for Bug 3821553
336
337 psp_enc_liq_tran.enc_liq_trans(ERRBUF => l_errbuf,
338 RETCODE => l_retcode,
339 P_PAYROLL_ID => p_payroll_id,
340 / * changed p_action_type to l_enc_line_type for restart * /
341 p_action_type => l_enc_line_type,
342 P_Business_group_id => p_business_group_id,
343 p_Set_of_books_id => p_set_of_books_id);
344 -- Check for success of the liquidation program
345 IF (l_retcode NOT IN ('1', FND_API.G_RET_STS_SUCCESS)) THEN -- Introduced retcode '1' check for Enh. 2768298
346 / * commented following line for update Restart * /
347 / * clean_up_when_error; * /
348 g_error_api_path := 'ENC_LIQ_TRAN:'||g_error_api_path;
349 psp_message_s.print_error(p_mode => FND_FILE.LOG,p_print_header => FND_API.G_TRUE);
350 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
351 END IF;
352 ELSE / * l_chg_count = 0 * /
353 / * If liquidation doesn't fire, call the house keeping step explicitly * /
354 cleanup_on_success(p_enc_line_type ,
355 p_payroll_id ,
356 p_business_group_id,
357 p_set_of_books_id,
358 'N', --- invalid suspense send 'N'
359 l_retcode);
360 IF (l_retcode <> FND_API.G_RET_STS_SUCCESS ) THEN
361 g_error_api_path := 'cleanup_on_sucess'||g_error_api_path;
362 psp_message_s.print_error(p_mode => FND_FILE.LOG,
363 p_print_header => FND_API.G_TRUE);
364 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
365 ELSE
369 -- psp_message_s.print_success;
366 COMMIT;
367 END IF;
368 END IF;
370 -- Replaced Success printing message for Enh. 2768298 removal of suspense posting in enc. Liquidation
371 IF (l_retcode = '1') THEN
372 retcode := 1;
373 psp_message_s.print_error(p_mode => FND_FILE.LOG,
374 p_print_header => FND_API.G_FALSE);
375 ELSE
376 psp_message_s.print_success;
377 END IF;
378 -- End of changes for Enh. 2768298 Removal of suspense posting in Enc. Liquidation
379
380 EXCEPTION
381 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
382 g_error_api_path := 'UPDATE_ENC_LINES:'||g_error_api_path;
383 fnd_msg_pub.add_exc_msg('PSP_ENC_UPDATE_LINES',g_error_api_path || sqlerrm);
384 l_retcode := fnd_api.g_ret_sts_unexp_error;
385 retcode := 2;
386 -- Introduced the following for bug fix 3434626
387 psp_message_s.print_error(p_mode => FND_FILE.LOG,
388 p_print_header => FND_API.G_FALSE);
389 -- End of bug fix 3434626
390 WHEN OTHERS THEN
391 g_error_api_path := 'UPDATE_ENC_LINES:'||g_error_api_path||' UNEXPECTED ERROR';
392 fnd_msg_pub.add_exc_msg('PSP_ENC_UPDATE_LINES',g_error_api_path || sqlerrm);
393 l_retcode := fnd_api.g_ret_sts_unexp_error;
394 retcode := 2;
395 -- Introduced the following for bug fix 3434626
396 psp_message_s.print_error(p_mode => FND_FILE.LOG,
397 p_print_header => FND_API.G_FALSE);
398 -- End of bug fix 3434626
399 end update_enc_lines;
400
401
402 / * Change History ***********************************************************************************
403
404 Who When What
405 ddubey 07-Mar-2002 Procedure verify_changes is re written for Enh. Encumbrance
406 Redesign-Pre Process Bug #2259310.Introdced Bulk Update For
407 1. Marking chage_flag ='U'for unchanged lines in psp_enc_lines_history
408 2. Marking change_flag ='N' for unmodfied lines in psp_enc_lines_history,
409 which are having same summary_line_id as modified lines.
410
411 Introduced Bulk Delete to delete newly created lines in psp_enc_lines
412 where the corresponding history lines are flagged as unmodified
413 i.e change_flag ='U'.
414
415 lveerubh 07-May-2002 Introduced Deletion of controls when there are no corresponding lines present-Bug 2359599
416 ***************************************************************************************************** /
417
418 Procedure verify_changes(p_payroll_id IN NUMBER,
419 p_business_group_id IN NUMBER,
420 p_set_of_books_id IN NUMBER,
421 p_enc_line_type IN VARCHAR2,
422 l_retcode OUT NOCOPY VARCHAR2) IS
423
424 CURSOR new_control_recs IS
425 SELECT enc_control_id,
426 time_period_id
427 FROM psp_enc_controls
428 WHERE payroll_id = p_payroll_id
429 AND action_code = 'IC';
430
431 / * Commented for Bug 3821553
432
433 TYPE enc_control_id_tl IS
434 TABLE OF psp_enc_controls.enc_control_id%TYPE
435 INDEX BY BINARY_INTEGER;
436 l_enc_control_id_tl enc_control_id_tl;
437
438 TYPE time_period_id_tl IS
439 TABLE OF psp_enc_controls.time_period_id%TYPE
440 INDEX BY BINARY_INTEGER;
441 l_time_period_id_tl time_period_id_tl;
442
443 End of Commenting for Bug 3821553* /
444
445 l_grouping_option CHAR(1); -- Introduced for bug fix 2908859
446
447 / * Introduced the following for Bug 3821553 * /
448 TYPE time_period_id_tl IS
449 TABLE OF psp_enc_lines.time_period_id%TYPE
450 INDEX BY BINARY_INTEGER;
451 l_time_period_id_tl time_period_id_tl;
452
453 TYPE encumbrance_date_tl IS
454 TABLE OF psp_enc_lines.encumbrance_date%TYPE
455 INDEX BY BINARY_INTEGER;
456 l_encumbrance_date_tl encumbrance_date_tl;
457
458 TYPE dr_cr_flag_tl IS
459 TABLE OF psp_enc_lines.dr_cr_flag%TYPE
460 INDEX BY BINARY_INTEGER;
461 l_dr_cr_flag_tl dr_cr_flag_tl;
462
463
464 TYPE encumbrance_amount_tl IS
465 TABLE OF psp_enc_lines.encumbrance_amount%TYPE
466 INDEX BY BINARY_INTEGER;
467 l_encumbrance_amount_tl encumbrance_amount_tl;
468
469 TYPE gl_project_flag_tl IS
470 TABLE OF psp_enc_lines.gl_project_flag%TYPE
471 INDEX BY BINARY_INTEGER;
472 l_gl_project_flag_tl gl_project_flag_tl;
473
474 TYPE schedule_line_id_tl IS
475 TABLE OF psp_enc_lines.schedule_line_id%TYPE
476 INDEX BY BINARY_INTEGER;
477 l_schedule_line_id_tl schedule_line_id_tl;
478
479 TYPE org_schedule_id_tl IS
480 TABLE OF psp_enc_lines.org_schedule_id%TYPE
481 INDEX BY BINARY_INTEGER;
482 l_org_schedule_id_tl org_schedule_id_tl;
483
484 TYPE default_org_account_id_tl IS
485 TABLE OF psp_enc_lines.default_org_account_id%TYPE
486 INDEX BY BINARY_INTEGER;
487 l_default_org_account_id_tl default_org_account_id_tl;
488
489 TYPE suspense_org_account_id_tl IS
490 TABLE OF psp_enc_lines.suspense_org_account_id%TYPE
491 INDEX BY BINARY_INTEGER;
492 l_suspense_org_account_id_tl suspense_org_account_id_tl;
493
494
495 TYPE element_account_id_tl IS
496 TABLE OF psp_enc_lines.element_account_id%TYPE
500
497 INDEX BY BINARY_INTEGER;
498 l_element_account_id_tl element_account_id_tl;
499
501
502 TYPE project_id_tl IS
503 TABLE OF psp_enc_lines.project_id%TYPE
504 INDEX BY BINARY_INTEGER;
505 l_project_id_tl project_id_tl;
506
507 TYPE task_id_tl IS
508 TABLE OF psp_enc_lines.task_id%TYPE
509 INDEX BY BINARY_INTEGER;
510 l_task_id_tl task_id_tl;
511
512
513
514 TYPE award_id_tl IS
515 TABLE OF psp_enc_lines.award_id%TYPE
516 INDEX BY BINARY_INTEGER;
517 l_award_id_tl award_id_tl;
518
519 TYPE expenditure_type_tl IS
520 TABLE OF psp_enc_lines.expenditure_type%TYPE
521 INDEX BY BINARY_INTEGER;
522 l_expenditure_type_tl expenditure_type_tl;
523
524 TYPE exp_organization_id_tl IS
525 TABLE OF psp_enc_lines.expenditure_organization_id%TYPE
526 INDEX BY BINARY_INTEGER;
527 l_exp_organization_id_tl exp_organization_id_tl;
528
529 TYPE gl_code_combination_id_tl IS
530 TABLE OF psp_enc_lines.gl_code_combination_id%TYPE
531 INDEX BY BINARY_INTEGER;
532 l_gl_code_combination_id_tl gl_code_combination_id_tl;
533
534
535 TYPE assignment_id_tl IS
536 TABLE OF psp_enc_lines.assignment_id%TYPE
537 INDEX BY BINARY_INTEGER;
538 l_assignment_id_tl assignment_id_tl;
539
540 TYPE attribute1_tl IS
541 TABLE OF psp_enc_lines.attribute1%TYPE
542 INDEX BY BINARY_INTEGER;
543 l_attribute1_tl attribute1_tl;
544
545
546 TYPE attribute2_tl IS
547 TABLE OF psp_enc_lines.attribute2%TYPE
548 INDEX BY BINARY_INTEGER;
549 l_attribute2_tl attribute2_tl;
550
551 TYPE attribute3_tl IS
552 TABLE OF psp_enc_lines.attribute3%TYPE
553 INDEX BY BINARY_INTEGER;
554 l_attribute3_tl attribute3_tl;
555
556 TYPE attribute4_tl IS
557 TABLE OF psp_enc_lines.attribute4%TYPE
558 INDEX BY BINARY_INTEGER;
559 l_attribute4_tl attribute4_tl;
560
561 TYPE attribute5_tl IS
562 TABLE OF psp_enc_lines.attribute5%TYPE
563 INDEX BY BINARY_INTEGER;
564 l_attribute5_tl attribute5_tl;
565
566 TYPE attribute6_tl IS
567 TABLE OF psp_enc_lines.attribute6%TYPE
568 INDEX BY BINARY_INTEGER;
569 l_attribute6_tl attribute6_tl;
570
571 TYPE attribute7_tl IS
572 TABLE OF psp_enc_lines.attribute7%TYPE
573 INDEX BY BINARY_INTEGER;
574 l_attribute7_tl attribute7_tl;
575
576 TYPE attribute8_tl IS
577 TABLE OF psp_enc_lines.attribute8%TYPE
578 INDEX BY BINARY_INTEGER;
579 l_attribute8_tl attribute8_tl;
580
581 TYPE attribute9_tl IS
582 TABLE OF psp_enc_lines.attribute9%TYPE
583 INDEX BY BINARY_INTEGER;
584 l_attribute9_tl attribute9_tl;
585
586 TYPE attribute10_tl IS
587 TABLE OF psp_enc_lines.attribute10%TYPE
588 INDEX BY BINARY_INTEGER;
589 l_attribute10_tl attribute10_tl;
590
591 -- new cursor for fetching enc lines
592
593 cursor enc_lines_cur is
594 SELECT distinct pel.time_period_id,
595 pel.encumbrance_date,
596 pel.dr_cr_flag,
597 pel.encumbrance_amount,
598 pel.gl_project_flag,
599 NVL(pel.schedule_line_id,-99),
600 NVL(pel.org_schedule_id, -99),
601 NVL(pel.default_org_account_id, -99),
602 NVL(pel.suspense_org_account_id, -99),
603 NVL(pel.element_account_id, -99),
604 NVL(project_id, -99),
605 NVL(pel.task_id, -99),
606 NVL(pel.award_id, -99),
607 NVL(pel.expenditure_type, '-99'),
608 NVL(pel.expenditure_organization_id, -99),
609 NVL(pel.gl_code_combination_id, -99),
610 pel.assignment_id,
611 NVL(pel.attribute1,-99),
612 NVL(pel.attribute2,-99),
613 NVL(pel.attribute3,-99),
614 NVL(pel.attribute4,-99),
615 NVL(pel.attribute5,-99),
616 NVL(pel.attribute6,-99),
617 NVL(pel.attribute7,-99),
618 NVL(pel.attribute8,-99),
619 NVL(pel.attribute9,-99),
620 NVL(pel.attribute10,-99)
621 FROM psp_enc_lines pel
622 WHERE enc_control_id in (Select enc_control_id
623 FROM psp_enc_controls pec
624 WHERE payroll_id = p_payroll_id
625 AND pec.enc_control_id = pel.enc_control_id
626 AND action_code = 'IC');
627
628 -- New cursor for fetching enc_lines_history
629
630 cursor enc_lines_history_cur is
631 SELECT distinct pelh.time_period_id ,
632 pelh.encumbrance_date ,
633 pelh.dr_cr_flag,
634 pelh.encumbrance_amount ,
635 pelh.gl_project_flag,
636 NVL(pelh.schedule_line_id,-99) ,
637 NVL(pelh.org_schedule_id, -99) ,
638 NVL(pelh.default_org_account_id, -99),
639 NVL(pelh.suspense_org_account_id, -99),
640 NVL(pelh.element_account_id, -99) ,
641 NVL(pelh.project_id, -99),
642 NVL(pelh.task_id, -99) ,
643 NVL(pelh.award_id, -99),
644 NVL(pelh.expenditure_type, '-99') ,
645 NVL(pelh.expenditure_organization_id, -99) ,
646 NVL(pelh.gl_code_combination_id, -99),
647 pelh.assignment_id,
651 NVL(pelh.attribute4,-99),
648 NVL(pelh.attribute1,-99),
649 NVL(pelh.attribute2,-99),
650 NVL(pelh.attribute3,-99),
652 NVL(pelh.attribute5,-99),
653 NVL(pelh.attribute6,-99),
654 NVL(pelh.attribute7,-99),
655 NVL(pelh.attribute8,-99),
656 NVL(pelh.attribute9,-99),
657 NVL(pelh.attribute10,-99)
658 FROM psp_enc_lines_history pelh
659 where pelh.change_flag = 'U'
660 AND payroll_id = p_payroll_id;
661
662
663
664
665 / * Enc of code changes for 3821553 * /
666
667
668
669 BEGIN
670 l_grouping_option := psp_general.get_enc_dff_grouping_option(p_business_group_id); -- Introduced for bug fix 2908859
671
672 / * Commenting out this cursor For Bug 3821553
673 OPEN new_control_recs;
674 LOOP
675 FETCH new_control_recs BULK COLLECT INTO l_enc_control_id_tl, l_time_period_id_tl;
676 EXIT WHEN new_control_recs%NOTFOUND;
677 END LOOP;
678 CLOSE new_control_recs;
679 End of Commenting for Bug 3821553 * /
680
681 / * Introduced the following cursor to replace the existing control records cursor * /
682 / * Introduced for Bug 3821553 * /
683 OPEN enc_lines_cur;
684 FETCH enc_lines_cur BULK COLLECT INTO
685 l_time_period_id_tl,l_encumbrance_date_tl,l_dr_cr_flag_tl,l_encumbrance_amount_tl
686 ,l_gl_project_flag_tl,l_schedule_line_id_tl,l_org_schedule_id_tl,l_default_org_account_id_tl,
687 l_suspense_org_account_id_tl,l_element_account_id_tl,l_project_id_tl,l_task_id_tl,l_award_id_tl,
688 l_expenditure_type_tl,l_exp_organization_id_tl,l_gl_code_combination_id_tl,l_assignment_id_tl,
689 l_attribute1_tl,l_attribute2_tl,l_attribute3_tl,l_attribute4_tl,l_attribute5_tl,
690 l_attribute6_tl,l_attribute7_tl,l_attribute8_tl,l_attribute9_tl,l_attribute10_tl;
691 CLOSE enc_lines_cur;
692
693 / * End of code changes for Bug 3821553 * /
694
695
696
697 / * Following BULK update identifies all newly lines that are unchanged from the
698 earlier line created in history. * /
699 / * Commenting for Bug 3821553
700
701 IF (l_grouping_option = 'N') THEN -- Introduced IF for bug fix 2908859
702 FORALL i IN 1 .. l_enc_control_id_tl.COUNT
703 UPDATE psp_enc_lines_history pelh
704 SET change_flag='U'
705 WHERE pelh.assignment_id IN (SELECT peca.assignment_id
706 FROM psp_enc_changed_assignments peca
707 WHERE peca.payroll_id = p_payroll_id
708 -- AND peca.request_id = g_request_id) commented for bug 2330057
709 AND peca.request_id IS NOT NULL)
710 AND time_period_id = l_time_period_id_tl(i)
711 AND change_flag = 'N'
712 AND EXISTS (SELECT 1
713 FROM psp_enc_lines pel
714 WHERE pel.enc_control_id = l_enc_control_id_tl(i)
715 AND pel.time_period_id = l_time_period_id_tl(i)
716 AND pel.change_flag = 'N'
717 AND pelh.encumbrance_date = pel.encumbrance_date
718 AND pelh.dr_cr_flag = pel.dr_cr_flag
719 AND pelh.encumbrance_amount = pel.encumbrance_amount
720 AND pelh.gl_project_flag = pel.gl_project_flag
721 AND NVL(pelh.schedule_line_id,-99) = NVL(pel.schedule_line_id,-99)
722 AND NVL(pelh.org_schedule_id, -99) = NVL(pel.org_schedule_id, -99)
723 AND NVL(pelh.default_org_account_id, -99) = NVL(pel.default_org_account_id, -99)
724 AND NVL(pelh.suspense_org_account_id, -99) = NVL(pel.suspense_org_account_id, -99)
725 AND NVL(pelh.element_account_id, -99) = NVL(pel.element_account_id, -99)
726 AND NVL(pelh.project_id, -99) = NVL(project_id, -99)
727 AND NVL(pelh.task_id, -99) = NVL(pel.task_id, -99)
728 AND NVL(pelh.award_id, -99) = NVL(pel.award_id, -99)
729 AND NVL(pelh.expenditure_type, '-99') = NVL(pel.expenditure_type, '-99')
730 AND NVL(pelh.expenditure_organization_id, -99) = NVL(pel.expenditure_organization_id, -99)
731 AND NVL(pelh.gl_code_combination_id, -99) = NVL(pel.gl_code_combination_id, -99)
732 AND pelh.assignment_id = pel.assignment_id); ----added for 3230387
733 ELSE -- Introduced ELSE portion for bug fix 2908859
734 FORALL i IN 1 .. l_enc_control_id_tl.COUNT
735 UPDATE psp_enc_lines_history pelh
736 SET change_flag='U'
737 WHERE pelh.assignment_id IN (SELECT peca.assignment_id
738 FROM psp_enc_changed_assignments peca
739 WHERE peca.payroll_id = p_payroll_id
740 AND peca.request_id IS NOT NULL)
741 AND time_period_id = l_time_period_id_tl(i)
742 AND change_flag = 'N'
743 AND EXISTS (SELECT 1
744 FROM psp_enc_lines pel
745 WHERE pel.enc_control_id = l_enc_control_id_tl(i)
746 AND pel.time_period_id = l_time_period_id_tl(i)
747 AND pel.change_flag = 'N'
748 AND pelh.encumbrance_date = pel.encumbrance_date
749 AND pelh.dr_cr_flag = pel.dr_cr_flag
750 AND pelh.encumbrance_amount = pel.encumbrance_amount
751 AND pelh.gl_project_flag = pel.gl_project_flag
752 AND NVL(pelh.schedule_line_id,-99) = NVL(pel.schedule_line_id,-99)
753 AND NVL(pelh.org_schedule_id, -99) = NVL(pel.org_schedule_id, -99)
754 AND NVL(pelh.default_org_account_id, -99) = NVL(pel.default_org_account_id, -99)
755 AND NVL(pelh.suspense_org_account_id, -99) = NVL(pel.suspense_org_account_id, -99)
759 AND NVL(pelh.award_id, -99) = NVL(pel.award_id, -99)
756 AND NVL(pelh.element_account_id, -99) = NVL(pel.element_account_id, -99)
757 AND NVL(pelh.project_id, -99) = NVL(project_id, -99)
758 AND NVL(pelh.task_id, -99) = NVL(pel.task_id, -99)
760 AND NVL(pelh.expenditure_type, '-99') = NVL(pel.expenditure_type, '-99')
761 AND NVL(pelh.expenditure_organization_id, -99) = NVL(pel.expenditure_organization_id, -99)
762 AND NVL(pelh.gl_code_combination_id, -99) = NVL(pel.gl_code_combination_id, -99)
763 AND NVL(pelh.attribute_category, 'NULL') = NVL(pel.attribute_category, 'NULL')
764 AND NVL(pelh.attribute1, 'NULL') = NVL(pel.attribute1, 'NULL')
765 AND NVL(pelh.attribute2, 'NULL') = NVL(pel.attribute2, 'NULL')
766 AND NVL(pelh.attribute3, 'NULL') = NVL(pel.attribute3, 'NULL')
767 AND NVL(pelh.attribute4, 'NULL') = NVL(pel.attribute4, 'NULL')
768 AND NVL(pelh.attribute5, 'NULL') = NVL(pel.attribute5, 'NULL')
769 AND NVL(pelh.attribute6, 'NULL') = NVL(pel.attribute6, 'NULL')
770 AND NVL(pelh.attribute7, 'NULL') = NVL(pel.attribute7, 'NULL')
771 AND NVL(pelh.attribute8, 'NULL') = NVL(pel.attribute8, 'NULL')
772 AND NVL(pelh.attribute9, 'NULL') = NVL(pel.attribute9, 'NULL')
773 AND NVL(pelh.attribute10, 'NULL') = NVL(pel.attribute10, 'NULL')
774 AND pelh.assignment_id = pel.assignment_id);
775 END IF;
776
777 End of Commenting for Bug 3821553 * /
778
779 -- Introduced for Bug 3821553
780 hr_utility.trace('l_grouping_option = '|| l_grouping_option);
781 IF (l_grouping_option = 'N') THEN -- Introduced IF for bug fix 2908859
782
783 FORALL i IN 1 .. l_time_period_id_tl.COUNT
784 UPDATE psp_enc_lines_history pelh
785 SET change_flag='U'
786 WHERE time_period_id = l_time_period_id_tl(i)
787 AND change_flag = 'N'
788 AND pelh.encumbrance_date = l_encumbrance_date_tl(I)
789 AND pelh.dr_cr_flag = l_dr_cr_flag_tl(I)
790 AND pelh.encumbrance_amount = l_encumbrance_amount_tl(I)
791 AND pelh.gl_project_flag = l_gl_project_flag_tl(I)
792 AND NVL(pelh.schedule_line_id,-99) = l_schedule_line_id_tl(I)
793 AND NVL(pelh.org_schedule_id, -99) = l_org_schedule_id_tl(I)
794 AND NVL(pelh.default_org_account_id, -99) = l_default_org_account_id_tl(I)
795 AND NVL(pelh.suspense_org_account_id, -99) = l_suspense_org_account_id_tl(I)
796 AND NVL(pelh.element_account_id, -99) = l_element_account_id_tl(I)
797 AND NVL(pelh.project_id, -99) = l_project_id_tl(I)
798 AND NVL(pelh.task_id, -99) = l_task_id_tl(i)
799 AND NVL(pelh.award_id, -99) = l_award_id_tl(i)
800 AND NVL(pelh.expenditure_type, '-99') = l_expenditure_type_tl(I)
801 AND NVL(pelh.expenditure_organization_id, -99) = l_exp_organization_id_tl(i)
802 AND NVL(pelh.gl_code_combination_id, -99) = l_gl_code_combination_id_tl(i)
803 AND pelh.assignment_id = l_assignment_id_tl(i);
804
805 Else
806
807 FORALL i IN 1 .. l_time_period_id_tl.COUNT
808 UPDATE psp_enc_lines_history pelh
809 SET change_flag='U'
810 WHERE time_period_id = l_time_period_id_tl(i)
811 AND change_flag = 'N'
812 AND pelh.encumbrance_date = l_encumbrance_date_tl(I)
813 AND pelh.dr_cr_flag = l_dr_cr_flag_tl(I)
814 AND pelh.encumbrance_amount = l_encumbrance_amount_tl(I)
815 AND pelh.gl_project_flag = l_gl_project_flag_tl(I)
816 AND NVL(pelh.schedule_line_id,-99) = l_schedule_line_id_tl(I)
817 AND NVL(pelh.org_schedule_id, -99) = l_org_schedule_id_tl(I)
818 AND NVL(pelh.default_org_account_id, -99) = l_default_org_account_id_tl(I)
819 AND NVL(pelh.suspense_org_account_id, -99) = l_suspense_org_account_id_tl(I)
820 AND NVL(pelh.element_account_id, -99) = l_element_account_id_tl(I)
821 AND NVL(pelh.project_id, -99) = l_project_id_tl(I)
822 AND NVL(pelh.task_id, -99) = l_task_id_tl(i)
823 AND NVL(pelh.award_id, -99) = l_award_id_tl(i)
824 AND NVL(pelh.expenditure_type, '-99') = l_expenditure_type_tl(I)
825 AND NVL(pelh.expenditure_organization_id, -99) = l_exp_organization_id_tl(i)
826 AND NVL(pelh.gl_code_combination_id, -99) = l_gl_code_combination_id_tl(i)
827 AND pelh.assignment_id = l_assignment_id_tl(i)
828 -- removed nvl on rhs and changed NULL to -99 on lhs for 4072324
829 AND NVL(pelh.attribute1, '-99') = l_attribute1_tl(i)
830 AND NVL(pelh.attribute2, '-99') = l_attribute2_tl(i)
834 AND NVL(pelh.attribute6, '-99') = l_attribute6_tl(i)
831 AND NVL(pelh.attribute3, '-99') = l_attribute3_tl(i)
832 AND NVL(pelh.attribute4, '-99') = l_attribute4_tl(i)
833 AND NVL(pelh.attribute5, '-99') = l_attribute5_tl(i)
835 AND NVL(pelh.attribute7, '-99') = l_attribute7_tl(i)
836 AND NVL(pelh.attribute8, '-99') = l_attribute8_tl(i)
837 AND NVL(pelh.attribute9, '-99') = l_attribute9_tl(i)
838 AND NVL(pelh.attribute10, '-99') = l_attribute10_tl(i);
839
840 End if ;
841
842
843 -- Introduced the following for Bug fix 3821553
844 begin
845 FND_STATS.Gather_Table_Stats(ownname => 'PSP',
846 tabname => 'PSP_ENC_LINES_HISTORY');
847
848 exception
849 when others then
850 null;
851 end;
852
853 / * If more than one history line are summarized into one summary line, then if any one
854 of the history line is marked for liquidation then the remaining set of history lines have to be
855 liquidated as their summary line is going to be liquidated (gets superceded). * /
856
857
858 -- FORALL i IN 1 .. l_enc_control_id_tl.COUNT Commented for Bug 3821553
859 FORALL i IN 1 .. l_time_period_id_tl.COUNT -- Introduced for Bug 3821553
860 UPDATE psp_enc_lines_history pelh
861 SET change_flag='N'
862 WHERE enc_summary_line_id in (SELECT enc_summary_line_id
863 FROM psp_enc_lines_history
864 WHERE change_flag = 'N'
865 AND time_period_id = l_time_period_id_tl(i))
866 AND change_flag='U'
867 AND time_period_id=l_time_period_id_tl(i);
868
869 --- 3953230
870 if sql%rowcount > 0 then
871 if g_liquidate_flag is null then
872 g_liquidate_flag := 'Y' ;
873 end if;
874 end if;
875
876 -- Introduced the follwing for bug 3821553
877
878
879 l_time_period_id_tl.delete;
880 l_encumbrance_date_tl.delete;
881 l_dr_cr_flag_tl.delete;
882 l_encumbrance_amount_tl.delete;
883 l_gl_project_flag_tl.delete;
884 l_schedule_line_id_tl.delete;
885 l_org_schedule_id_tl.delete;
886 l_default_org_account_id_tl.delete;
887 l_suspense_org_account_id_tl.delete;
888 l_element_account_id_tl.delete;
889 l_project_id_tl.delete;
890 l_task_id_tl.delete;
891 l_award_id_tl.delete;
892 l_expenditure_type_tl.delete;
893 l_exp_organization_id_tl.delete;
894 l_gl_code_combination_id_tl.delete;
895 l_assignment_id_tl.delete;
896 l_attribute1_tl.delete;
897 l_attribute2_tl.delete;
898 l_attribute3_tl.delete;
899 l_attribute4_tl.delete;
900 l_attribute5_tl.delete;
901 l_attribute6_tl.delete;
902 l_attribute7_tl.delete;
903 l_attribute8_tl.delete;
904 l_attribute9_tl.delete;
905 l_attribute10_tl.delete;
906
907 OPEN enc_lines_history_cur;
908 FETCH enc_lines_history_cur BULK COLLECT INTO
909 l_time_period_id_tl,l_encumbrance_date_tl,l_dr_cr_flag_tl,l_encumbrance_amount_tl
910 ,l_gl_project_flag_tl,l_schedule_line_id_tl,l_org_schedule_id_tl,l_default_org_account_id_tl,
911 l_suspense_org_account_id_tl,l_element_account_id_tl,l_project_id_tl,l_task_id_tl,l_award_id_tl,
912 l_expenditure_type_tl,l_exp_organization_id_tl,l_gl_code_combination_id_tl,l_assignment_id_tl,
913 l_attribute1_tl,l_attribute2_tl,l_attribute3_tl,l_attribute4_tl,l_attribute5_tl,
914 l_attribute6_tl,l_attribute7_tl,l_attribute8_tl,l_attribute9_tl,l_attribute10_tl;
915 CLOSE enc_lines_history_cur;
916
917
918 / * Delete all those duplicate lines in psp_enc_lines that need not be summarized. * /
919 / * Commenting the code for Bug 3821553
920 IF (l_grouping_option = 'N') THEN -- Introduced IF for bug fix 2908859
921 FORALL i IN 1 .. l_enc_control_id_tl.COUNT
922 DELETE psp_enc_lines pel
923 WHERE time_period_id = l_time_period_id_tl(i)
924 AND change_flag = 'N'
925 AND EXISTS (SELECT 1
926 FROM psp_enc_lines_history pelh
927 -- WHERE pelh.enc_control_id = l_enc_control_id_tl(i)
928 WHERE pelh.time_period_id = l_time_period_id_tl(i)
929 AND pelh.change_flag = 'U'
930 AND pelh.encumbrance_date = pel.encumbrance_date
931 AND pelh.dr_cr_flag = pel.dr_cr_flag
932 AND pelh.encumbrance_amount = pel.encumbrance_amount
933 AND pelh.gl_project_flag = pel.gl_project_flag
934 AND NVL(pelh.schedule_line_id,-99) = NVL(pel.schedule_line_id,-99)
935 AND NVL(pelh.org_schedule_id, -99) = NVL(pel.org_schedule_id, -99)
936 AND NVL(pelh.default_org_account_id, -99) = NVL(pel.default_org_account_id, -99)
937 AND NVL(pelh.suspense_org_account_id, -99) = NVL(pel.suspense_org_account_id, -99)
938 AND NVL(pelh.element_account_id, -99) = NVL(pel.element_account_id, -99)
939 AND NVL(pelh.project_id, -99) = NVL(project_id, -99)
940 AND NVL(pelh.task_id, -99) = NVL(pel.task_id, -99)
941 AND NVL(pelh.award_id, -99) = NVL(pel.award_id, -99)
942 AND NVL(pelh.expenditure_type, '-99') = NVL(pel.expenditure_type, '-99')
943 AND NVL(pelh.expenditure_organization_id, -99) = NVL(pel.expenditure_organization_id, -99)
944 AND NVL(pelh.gl_code_combination_id, -99) = NVL(pel.gl_code_combination_id, -99)
945 AND pel.assignment_id = pelh.assignment_id); --- 3230387
949 WHERE time_period_id = l_time_period_id_tl(i)
946 ELSE -- Introduced ELSE portion for bug fix 2908859
947 FORALL i IN 1 .. l_enc_control_id_tl.COUNT
948 DELETE psp_enc_lines pel
950 AND change_flag = 'N'
951 AND EXISTS (SELECT 1
952 FROM psp_enc_lines_history pelh
953 WHERE pelh.time_period_id = l_time_period_id_tl(i)
954 AND pelh.change_flag = 'U'
955 AND pelh.encumbrance_date = pel.encumbrance_date
956 AND pelh.dr_cr_flag = pel.dr_cr_flag
957 AND pelh.encumbrance_amount = pel.encumbrance_amount
958 AND pelh.gl_project_flag = pel.gl_project_flag
959 AND NVL(pelh.schedule_line_id,-99) = NVL(pel.schedule_line_id,-99)
960 AND NVL(pelh.org_schedule_id, -99) = NVL(pel.org_schedule_id, -99)
961 AND NVL(pelh.default_org_account_id, -99) = NVL(pel.default_org_account_id, -99)
962 AND NVL(pelh.suspense_org_account_id, -99) = NVL(pel.suspense_org_account_id, -99)
963 AND NVL(pelh.element_account_id, -99) = NVL(pel.element_account_id, -99)
964 AND NVL(pelh.project_id, -99) = NVL(project_id, -99)
965 AND NVL(pelh.task_id, -99) = NVL(pel.task_id, -99)
966 AND NVL(pelh.award_id, -99) = NVL(pel.award_id, -99)
967 AND NVL(pelh.expenditure_type, '-99') = NVL(pel.expenditure_type, '-99')
968 AND NVL(pelh.expenditure_organization_id, -99) = NVL(pel.expenditure_organization_id, -99)
969 AND NVL(pelh.gl_code_combination_id, -99) = NVL(pel.gl_code_combination_id, -99)
970 AND NVL(pelh.attribute_category, 'NULL') = NVL(pel.attribute_category, 'NULL')
971 AND NVL(pelh.attribute1, 'NULL') = NVL(pel.attribute1, 'NULL')
972 AND NVL(pelh.attribute2, 'NULL') = NVL(pel.attribute2, 'NULL')
973 AND NVL(pelh.attribute3, 'NULL') = NVL(pel.attribute3, 'NULL')
974 AND NVL(pelh.attribute4, 'NULL') = NVL(pel.attribute4, 'NULL')
975 AND NVL(pelh.attribute5, 'NULL') = NVL(pel.attribute5, 'NULL')
976 AND NVL(pelh.attribute6, 'NULL') = NVL(pel.attribute6, 'NULL')
977 AND NVL(pelh.attribute7, 'NULL') = NVL(pel.attribute7, 'NULL')
978 AND NVL(pelh.attribute8, 'NULL') = NVL(pel.attribute8, 'NULL')
979 AND NVL(pelh.attribute9, 'NULL') = NVL(pel.attribute9, 'NULL')
980 AND NVL(pelh.attribute10, 'NULL') = NVL(pel.attribute10, 'NULL')
981 AND pel.assignment_id = pelh.assignment_id);
982 END IF;
983 End of Commenting the code for Bug 3821553 * /
984
985 -- Introduced the following for Bug 3821553
986
987 IF (l_grouping_option = 'N') THEN -- Introduced IF for bug fix 2908859
988
989 FORALL i IN 1 .. l_time_period_id_tl.COUNT
990 DELETE psp_enc_lines pel
991 WHERE time_period_id = l_time_period_id_tl(i)
992 AND pel.change_flag = 'N'
993 AND pel.encumbrance_date = l_encumbrance_date_tl(i)
994 AND pel.dr_cr_flag = l_dr_cr_flag_tl(i)
995 AND pel.encumbrance_amount = l_encumbrance_amount_tl(i)
996 AND pel.gl_project_flag = l_gl_project_flag_tl(i)
997 AND NVL(pel.schedule_line_id,-99) = l_schedule_line_id_tl(i)
998 AND NVL(pel.org_schedule_id, -99) = l_org_schedule_id_tl(i)
999 AND NVL(pel.default_org_account_id, -99) = l_default_org_account_id_tl(i)
1000 AND NVL(pel.suspense_org_account_id, -99) = l_suspense_org_account_id_tl(i)
1001 AND NVL(pel.element_account_id, -99) = l_element_account_id_tl(i)
1002 AND NVL(pel.project_id, -99) = l_project_id_tl(I)
1003 AND NVL(pel.task_id, -99) = l_task_id_tl(i)
1004 AND NVL(pel.award_id, -99) = l_award_id_tl(i)
1005 AND NVL(pel.expenditure_type, '-99') = l_expenditure_type_tl(i)
1006 AND NVL(pel.expenditure_organization_id, -99) = l_exp_organization_id_tl(i)
1007 AND NVL(pel.gl_code_combination_id, -99) = l_gl_code_combination_id_tl(i)
1008 AND pel.assignment_id = l_assignment_id_tl(i);
1009
1010 Else
1011
1012 FORALL i IN 1 .. l_time_period_id_tl.COUNT
1013 DELETE psp_enc_lines pel
1014 WHERE time_period_id = l_time_period_id_tl(i)
1015 AND pel.change_flag = 'N'
1016 AND pel.encumbrance_date = l_encumbrance_date_tl(i)
1017 AND pel.dr_cr_flag = l_dr_cr_flag_tl(i)
1018 AND pel.encumbrance_amount = l_encumbrance_amount_tl(i)
1019 AND pel.gl_project_flag = l_gl_project_flag_tl(i)
1020 AND NVL(pel.schedule_line_id,-99) = l_schedule_line_id_tl(i)
1021 AND NVL(pel.org_schedule_id, -99) = l_org_schedule_id_tl(i)
1022 AND NVL(pel.default_org_account_id, -99) = l_default_org_account_id_tl(i)
1023 AND NVL(pel.suspense_org_account_id, -99) = l_suspense_org_account_id_tl(i)
1024 AND NVL(pel.element_account_id, -99) = l_element_account_id_tl(i)
1025 AND NVL(pel.project_id, -99) = l_project_id_tl(I)
1026 AND NVL(pel.task_id, -99) = l_task_id_tl(i)
1027 AND NVL(pel.award_id, -99) = l_award_id_tl(i)
1028 AND NVL(pel.expenditure_type, '-99') = l_expenditure_type_tl(i)
1029 AND NVL(pel.expenditure_organization_id, -99) = l_exp_organization_id_tl(i)
1030 AND NVL(pel.gl_code_combination_id, -99) = l_gl_code_combination_id_tl(i)
1031 AND pel.assignment_id = l_assignment_id_tl(i)
1032 -- removed nvl on rhs, not necessary.. for 4072324
1033 AND NVL(pel.attribute1, '-99') = l_attribute1_tl(i)
1034 AND NVL(pel.attribute2, '-99') = l_attribute2_tl(i)
1035 AND NVL(pel.attribute3, '-99') = l_attribute3_tl(i)
1036 AND NVL(pel.attribute4, '-99') = l_attribute4_tl(i)
1037 AND NVL(pel.attribute5, '-99') = l_attribute5_tl(i)
1038 AND NVL(pel.attribute6, '-99') = l_attribute6_tl(i)
1039 AND NVL(pel.attribute7, '-99') = l_attribute7_tl(i)
1040 AND NVL(pel.attribute8, '-99') = l_attribute8_tl(i)
1041 AND NVL(pel.attribute9, '-99') = l_attribute9_tl(i)
1045
1042 AND NVL(pel.attribute10, '-99') = l_attribute10_tl(i);
1043
1044 End If ;
1046 -- Introduced the following for bug 3821553
1047
1048 l_time_period_id_tl.delete;
1049 l_encumbrance_date_tl.delete;
1050 l_dr_cr_flag_tl.delete;
1051 l_encumbrance_amount_tl.delete;
1052 l_gl_project_flag_tl.delete;
1053 l_schedule_line_id_tl.delete;
1054 l_org_schedule_id_tl.delete;
1055 l_default_org_account_id_tl.delete;
1056 l_suspense_org_account_id_tl.delete;
1057 l_element_account_id_tl.delete;
1058 l_project_id_tl.delete;
1059 l_task_id_tl.delete;
1060 l_award_id_tl.delete;
1061 l_expenditure_type_tl.delete;
1062 l_exp_organization_id_tl.delete;
1063 l_gl_code_combination_id_tl.delete;
1064 l_assignment_id_tl.delete;
1065 l_attribute1_tl.delete;
1066 l_attribute2_tl.delete;
1067 l_attribute3_tl.delete;
1068 l_attribute4_tl.delete;
1069 l_attribute5_tl.delete;
1070 l_attribute6_tl.delete;
1071 l_attribute7_tl.delete;
1072 l_attribute8_tl.delete;
1073 l_attribute9_tl.delete;
1074 l_attribute10_tl.delete;
1075
1076 ----For Bug 2359599 : Deleting the Controls when no lines exists
1077 DELETE FROM psp_enc_controls pec
1078 WHERE pec.action_type IN ('U','Q')
1079 AND pec.action_code = 'IC'
1080 AND pec.payroll_id = p_payroll_id
1081 AND NOT EXISTS ( SELECT 1
1082 FROM psp_enc_lines pel
1083 WHERE pel.enc_control_id = pec.enc_control_id);
1084
1085
1086 l_retcode := FND_API.G_RET_STS_SUCCESS;
1087 EXCEPTION
1088 WHEN OTHERS THEN
1089 g_error_api_path := SUBSTR('VERIFY_CHANGES:'||g_error_api_path,1,230);
1090 fnd_msg_pub.add_exc_msg('PSP_ENC_UPDATE_LINES','VERIFY_CHANGES');
1091 l_retcode := fnd_api.g_ret_sts_unexp_error;
1092 END verify_changes;
1093
1094 / *************************************************************************************************
1095 Created By : ddubey
1096
1097 Date Created By : 19-Dec-2001
1098
1099 Purpose :
1100 This procedure has been introduced for the Bug 2110930 -Quick Update Encumbrance Enhancement.
1101 The procedure shall be invoked in Q (quick update) or U (update) mode to move the processed
1102 assignments to history
1103
1104 Know limitations, enhancements or remarks
1105
1106 Change History
1107
1108 Who When What
1109 ddubey 21-Dec-01 Created
1110 ddubey 07-Mar-02 Re Engineered the procedure for Enh. Encumbrance Re design
1111 Pre process,Bug #2259310.
1112
1113 ************************************************************************************************* /
1114 PROCEDURE move_qkupd_rec_to_hist( p_payroll_id IN NUMBER,
1115 p_enc_line_type IN VARCHAR2,
1116 p_business_group_id IN NUMBER,
1117 p_set_of_books_id IN NUMBER,
1118 p_return_status OUT NOCOPY VARCHAR2)
1119 IS
1120 / * Commented the following for big fix 2324917
1121 Cursor to dislay no of assignments processed by update
1122 CURSOR get_no_asg_to_move IS
1123 SELECT COUNT(DISTINCT peca.assignment_id)
1124 FROM psp_enc_changed_assignments peca
1125 WHERE peca.request_id = g_request_id;
1126
1127 l_no_of_asg NUMBER DEFAULT 0; End of bug fix 2324917 * /
1128
1129 l_global_user_id NUMBER DEFAULT fnd_global.user_id;
1130 l_reqid NUMBER DEFAULT 0;
1131
1132 BEGIN
1133 / * Moving records to psp_enc_changed_asg_history table .Also inserting concurrent request_id
1134 into history for debugging purpose * /
1135 INSERT INTO psp_enc_changed_asg_history
1136 (request_id, assignment_id, payroll_id, change_type,processing_module, created_by
1137 ,creation_date, processed_flag, reference_id, action_type)
1138 SELECT g_request_id, peca.assignment_id, peca.payroll_id, peca.change_type,
1139 p_enc_line_type, l_global_user_id, SYSDATE, NULL, NVL(peca.reference_id, 0),
1140 NVL(peca.action_type, p_enc_line_type)
1141 FROM psp_enc_changed_assignments peca
1142 -- WHERE peca.request_id = g_request_id; commented as a part of bug 2330057
1143 --Following code is added as a part of bug fix 2334434.
1144 WHERE payroll_id =p_payroll_id
1145 AND peca.request_id IS NOT NULL
1146 AND ( (p_enc_line_type = 'Q'
1147 AND peca.change_type IN ('LS', 'ET', 'AS', 'QU'))
1148 OR p_enc_line_type = 'U');
1149
1150 / * Deleting records from psp_enc_changed_assignments table * /
1151 DELETE psp_enc_changed_assignments peca
1152 -- WHERE peca.request_id =g_request_id; commented as a part of bug 2330057
1153 / * Following code is added for bug 2330057 * /
1154 WHERE peca.payroll_id=p_payroll_id
1155 AND peca.request_id IS NOT NULL;
1156
1157 / * Introduced the following for bug fix 2324917 * /
1158 l_reqid := fnd_request.submit_request('PSP', 'PSPENASG', NULL, NULL, NULL, g_request_id);
1159
1160 / * Commented for bug fix 2324917, no message to be displayed in the log
1161 IF p_enc_line_type ='Q' THEN
1162 OPEN get_no_asg_to_move;
1163 FETCH get_no_asg_to_move INTO l_no_of_asg;
1164 CLOSE get_no_asg_to_move;
1165
1166 / * Displaying no of records moved to history in quick update mode * /
1167 fnd_message.set_name('PSP','PSP_ENC_NUM_ASG');
1168 fnd_message.set_token('NUM_ASG',l_no_of_asg);
1169 fnd_msg_pub.add;
1173
1170 psp_message_s.print_error(p_mode => FND_FILE.LOG,
1171 p_print_header => FND_API.G_FALSE);
1172 END IF; End of bug fix 2324917 * /
1174 p_return_status := fnd_api.g_ret_sts_success;
1175
1176 EXCEPTION
1177 WHEN OTHERS THEN
1178 g_error_api_path := SUBSTR('MOVE_QKUPD_REC_TO_HIST:'||g_error_api_path,1,230);
1179 fnd_msg_pub.add_exc_msg('PSP_ENC_UPDATE_LINES','MOVE_QKUPD_REC_TO_HIST');
1180 p_return_status := fnd_api.g_ret_sts_unexp_error;
1181 END move_qkupd_rec_to_hist;
1182
1183
1184 / *************************************************************
1185 The following procedure is added for Enh. Resstart Update Encumbrance process
1186 Created By : ddubey
1187
1188 Date Created By : 16-Jan-01
1189
1190 Change History
1191
1192 Who When What
1193 ddubey 16-jan-2001 Created
1194
1195 **************************************************************** /
1196 procedure cleanup_on_success ( p_enc_line_type in varchar2,
1197 p_payroll_id in number,
1198 p_business_group_id in number,
1199 p_set_of_books_id in number,
1200 p_invalid_suspense in Varchar2,
1201 p_return_status out NOCOPY varchar2) is
1202
1203 / ***************************************************************************
1204 For Bug 2359599 : Controls are deleted in the Verify changes procedure
1205 CURSOR pending_enc_lines_cur Is
1206 SELECT count(*)
1207 FROM psp_enc_lines
1208 WHERE payroll_id = p_payroll_id
1209 AND rownum = 1;
1210 l_new_line_count integer;
1211 ****************************************************************************** /
1212
1213 l_retcode varchar2(1);
1214 Begin
1215
1216 IF p_enc_line_type IN ('Q', 'U') and p_invalid_suspense = 'N' then
1217 -- Restart Update Enc related change.
1218 / ***************************************************************************
1219 For Bug 2359599 : Controls are deleted in the Verify changes procedure
1220 OPEN pending_enc_lines_cur;
1221 FETCH pending_enc_lines_cur INTO l_new_line_count;
1222 CLOSE pending_enc_lines_cur;
1223
1224
1225 IF l_new_line_count=0 then
1226 DELETE FROM psp_Enc_controls
1227 WHERE action_type = p_enc_line_type
1228 AND payroll_id = p_payroll_id
1229 AND action_code='IC';
1230 END IF;
1231 *************************************************************************************** /
1232 -- After liquidation, identify and update the liquidated records and mark the unchanged records to 'N'
1233 -- to be picked up in a subsequent update run
1234 --Modified the Update statement to include the assignment_id check for Bug 2345813
1235 UPDATE psp_enc_lines_history pelh
1236 SET pelh.change_flag='L'
1237 WHERE pelh.change_flag='N'
1238 AND pelh.payroll_id = p_payroll_id
1239 -- AND EXISTS (SELECT 1 Commented for Bug 3821553
1240 and pelh.assignment_id in (SELECT peca.assignment_id -- Introduced for bug 3821553
1241 FROM psp_enc_changed_assignments peca
1242 WHERE peca.payroll_id = p_payroll_id
1243 -- AND pelh.assignment_id = peca.assignment_id Commented for Bug 3821553
1244 AND peca.request_id IS NOT NULL
1245 AND ((p_enc_line_type = 'Q'
1246 AND peca.change_type IN ('LS', 'ET', 'AS', 'QU'))
1247 OR p_enc_line_type = 'U'));
1248
1249 --update psp_enc_lines_history set change_flag='N' where change_flag='U';
1250 UPDATE psp_enc_lines_history
1251 SET change_flag = 'N'
1252 WHERE change_flag = 'U'
1253 AND payroll_id = p_payroll_id;
1254
1255
1256 / * IF p_enc_line_type in ('Q', 'U') THEN Commented becos this check is alread done above * /
1257 move_qkupd_rec_to_hist(p_payroll_id,
1258 p_enc_line_type,
1259 p_business_group_id,
1260 p_set_of_books_id,
1261 l_retcode);
1262 IF l_retcode <> fnd_api.g_ret_sts_success THEN
1263 g_error_api_path := 'MOVE_QKUPD_REC_TO_HIST:'||g_error_api_path;
1264 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1265 END IF;
1266 / * New code added for restart update process * /
1267 UPDATE psp_enc_controls
1268 SET action_code = 'N'
1269 WHERE action_code = 'IC'
1270 AND payroll_id = p_payroll_id
1271 AND set_of_books_id = p_set_of_books_id
1272 AND business_group_id = p_business_group_id;
1273 END IF;
1274 p_return_status := fnd_api.g_ret_sts_success;
1275 EXCEPTION
1276 WHEN OTHERS THEN
1277 g_error_api_path := substr('CLEANUP_ON_SUCCESS:'||g_error_api_path,1,230);
1278 fnd_msg_pub.add_exc_msg('PSP_ENC_UPDATE_LINES',g_error_api_path);
1279 l_retcode := fnd_api.g_ret_sts_unexp_error;
1280 END cleanup_on_success;
1281
1282 --- added the procedure for 3473294
1283 PROCEDURE ROLLBACK_REJECTED_ASG (p_payroll_id in integer,
1284 p_action_type in varchar2,
1285 p_gms_batch_name in varchar2,
1286 p_accepted_group_id in integer,
1290 p_set_of_books_id in integer,
1287 p_rejected_group_id in integer,
1288 p_run_id in integer,
1289 p_business_group_id in integer,
1291 p_return_status out nocopy varchar2) is
1292
1293 l_global_user_id NUMBER DEFAULT fnd_global.user_id;
1294 l_reqid NUMBER DEFAULT 0;
1295
1296 --- update the totals of the new controls, to reflect purging of enc lines
1297 cursor get_new_control_totals is
1298 SELECT LINE.enc_control_id,
1299 count(decode(LINE.dr_cr_flag, 'D', 'x',null)) number_of_dr,
1300 count(decode(LINE.dr_cr_flag, 'C', 'x', null)) number_of_cr,
1301 sum(decode(LINE.dr_cr_flag,'D',LINE.encumbrance_amount,0)) total_dr_amount ,
1302 sum(decode(LINE.dr_cr_flag,'C',LINE.encumbrance_amount,0)) total_cr_amount,
1303 sum(decode(LINE.dr_cr_flag,'D',decode(LINE.gl_project_flag,'G',LINE.encumbrance_amount,0),0)) gl_dr_amount,
1304 sum(decode(LINE.dr_cr_flag,'C',decode(LINE.gl_project_flag,'G',LINE.encumbrance_amount,0),0)) gl_cr_amount,
1305 sum(decode(LINE.dr_cr_flag,'D',decode(LINE.gl_project_flag,'P',LINE.encumbrance_amount,0),0)) ogm_dr_amount,
1306 sum(decode(LINE.dr_cr_flag,'C',decode(LINE.gl_project_flag,'P',LINE.encumbrance_amount,0),0)) ogm_cr_amount
1307 FROM psp_enc_lines LINE
1308 WHERE LINE.assignment_id in (SELECT assignment_id FROM psp_enc_changed_assignments peca
1309 WHERE peca.payroll_id=p_payroll_id
1310 AND peca.request_id IS NOT NULL
1311 AND (p_action_type = 'U' or peca.change_type IN ('LS', 'ET', 'AS', 'QU')))
1312 AND LINE.enc_control_id in (SELECT CTRL2.enc_control_id FROM psp_enc_controls CTRL2
1313 WHERE CTRL2.action_code = 'IC' and CTRL2.payroll_id = p_payroll_id)
1314 GROUP BY LINE.enc_control_id;
1315
1316 control_rec get_new_control_totals%rowtype;
1317 begin
1318
1319 UPDATE psp_enc_lines_history pelh
1320 SET pelh.change_flag='L'
1321 WHERE pelh.change_flag='N'
1322 AND pelh.payroll_id = p_payroll_id
1323 AND pelh.enc_summary_line_id in
1324 (select superceded_line_id
1325 from psp_enc_summary_lines
1326 where status_code = 'L'
1327 and ((gms_batch_name is not null and p_gms_batch_name is not null and gms_batch_name = p_gms_batch_name)
1328 or (group_id is not null and p_accepted_group_id is not null and group_id = p_accepted_group_id))
1329 and enc_control_id in
1330 (select enc_control_id
1331 from psp_enc_controls
1332 where run_id = p_run_id
1333 and action_code in ('P','L')));
1334
1335 --update psp_enc_lines_history set change_flag='N' where change_flag='U';
1336 UPDATE psp_enc_lines_history
1337 SET change_flag = 'N'
1338 WHERE change_flag = 'U'
1339 AND payroll_id = p_payroll_id;
1340
1341 INSERT INTO psp_enc_changed_asg_history
1342 (request_id, assignment_id, payroll_id, change_type,processing_module, created_by
1343 ,creation_date, processed_flag, reference_id, action_type)
1344 SELECT g_request_id, peca.assignment_id, peca.payroll_id, peca.change_type,
1345 p_action_type, l_global_user_id, SYSDATE, NULL, NVL(peca.reference_id, 0),
1346 NVL(peca.action_type, p_action_type)
1347 FROM psp_enc_changed_assignments peca
1348 WHERE payroll_id =p_payroll_id
1349 AND peca.request_id IS NOT NULL
1350 AND ((p_action_type = 'Q' AND peca.change_type IN ('LS', 'ET', 'AS', 'QU'))
1351 OR p_action_type = 'U')
1352 AND peca.assignment_id NOT IN
1353 ( select distinct assignment_id
1354 from psp_enc_summary_lines
1355 where status_code = 'R'
1356 and ((gms_batch_name is not null and p_gms_batch_name is not null and gms_batch_name = p_gms_batch_name)
1357 or (group_id is not null and p_rejected_group_id is not null and group_id = p_rejected_group_id))
1358 and enc_control_id in
1359 (select enc_control_id
1360 from psp_enc_controls
1361 where run_id = p_run_id
1362 and action_code in ('P','L')));
1363
1364 DELETE psp_enc_changed_assignments peca
1365 WHERE peca.payroll_id=p_payroll_id
1366 AND peca.request_id IS NOT NULL
1367 AND ((p_action_type = 'Q' AND peca.change_type IN ('LS', 'ET', 'AS', 'QU'))
1368 OR p_action_type = 'U')
1369 AND peca.assignment_id NOT IN
1370 (select distinct assignment_id
1371 from psp_enc_summary_lines
1372 where status_code = 'R'
1373 and ((gms_batch_name is not null and p_gms_batch_name is not null and gms_batch_name = p_gms_batch_name)
1374 or (group_id is not null and p_rejected_group_id is not null and p_rejected_group_id = group_id))
1375 and enc_control_id in
1376 (select enc_control_id
1377 from psp_enc_controls
1378 where run_id = p_run_id
1379 and action_code in ('P','L'))) ;
1380
1381 open get_new_control_totals;
1382 loop
1383 fetch get_new_control_totals into control_rec;
1384 if get_new_Control_totals%notfound then
1385 close get_new_control_totals;
1386 exit;
1387 end if;
1388
1389 update psp_enc_controls
1390 set number_of_dr= number_of_dr - control_rec.number_of_dr,
1391 number_of_cr = number_of_cr - control_rec.number_of_cr,
1392 total_dr_amount= total_dr_amount - control_rec.total_dr_amount,
1396 ogm_dr_amount= ogm_dr_amount - control_rec.ogm_dr_amount,
1393 total_cr_amount= total_cr_amount - control_rec.total_cr_amount,
1394 gl_dr_amount = gl_dr_amount -control_rec.gl_dr_amount,
1395 gl_cr_amount = gl_cr_amount -control_rec.gl_cr_amount,
1397 ogm_cr_amount= ogm_cr_amount - control_rec.ogm_cr_amount
1398 where enc_control_id = control_rec.enc_control_id;
1399 end loop;
1400
1401 DELETE psp_enc_lines LINE
1402 WHERE LINE.assignment_id in (SELECT assignment_id FROM psp_enc_changed_assignments peca
1403 WHERE peca.payroll_id=p_payroll_id
1404 AND peca.request_id IS NOT NULL
1405 AND (p_action_type = 'U' or peca.change_type IN ('LS', 'ET', 'AS', 'QU')))
1406 AND LINE.enc_control_id in (SELECT CTRL2.enc_control_id
1407 FROM psp_enc_controls CTRL2
1408 WHERE CTRL2.action_code = 'IC' and CTRL2.payroll_id = p_payroll_id);
1409
1410
1411 DELETE psp_enc_controls CTRL
1412 WHERE CTRL.action_code = 'IC'
1413 AND CTRL.payroll_id = p_payroll_id
1414 AND NOT EXISTS ( SELECT 1
1415 FROM psp_enc_lines LINE
1416 WHERE LINE.enc_control_id = CTRL.enc_control_id);
1417
1418
1419 UPDATE psp_enc_controls
1420 SET action_code = 'N'
1421 WHERE action_code = 'IC'
1422 AND payroll_id = p_payroll_id
1423 AND set_of_books_id = p_set_of_books_id
1424 AND business_group_id = p_business_group_id;
1425
1426 l_reqid := fnd_request.submit_request('PSP', 'PSPENASG', NULL, NULL, NULL, g_request_id);
1427 p_return_status := fnd_api.g_ret_sts_success;
1428
1429 exception
1430 when others then
1431 fnd_msg_pub.add_exc_msg('PSP_ENC_UPDATE_LINES','ROLLBACK_REJECTED_ASG');
1432 fnd_msg_pub.add;
1433 end;
1434 End of comment of for Creatwe and Update multi-thread *****/
1435 END psp_enc_update_lines;