[Home] [Help]
PACKAGE BODY: APPS.PSP_SUM_ADJ
Source
1 PACKAGE BODY PSP_SUM_ADJ as
2 /* $Header: PSPADSTB.pls 120.18.12020000.9 2013/04/12 05:15:24 lkodaman ship $ */
3 g_gms_avail varchar2(1) := 'N';
4 g_fatal number;
5 g_gms_batch_name varchar2(10); /* bug 1662816 */
6 g_run_type varchar2(1); --- restart = R, normal = N, Replaces p_run_type -2444657
7
8 -- Introduced the following for bug 2259310
9 g_enable_enc_summ_gl VARCHAR2(1) DEFAULT NVL(fnd_profile.value('PSP_ENABLE_ENC_SUMM_GL'), 'N');
10
11 --Introduced for Bug no 2478000 Qubec fixes
12 -- g_currency_code VARCHAR2(15);-- for Bug no 2478000 by tbalacha Commented for bug fix 2916848
13
14 g_dff_grouping_option CHAR(1); -- Introduced for bug fix 2908859
15
16
17 -- Introduced the following for bug 6902514 -- change
18 g_create_stat_batch_in_gms VARCHAR(1);
19 g_skip_flag_gms varchar(1);
20
21
22 -- removed run_type parameter .. for 2444657.
23 PROCEDURE sum_transfer_adj(errbuf OUT NOCOPY VARCHAR2,
24 retcode OUT NOCOPY VARCHAR2,
25 p_adj_sum_batch_name IN VARCHAR2,
26 p_business_group_id IN NUMBER,
27 p_set_of_books_id IN NUMBER) IS
28
29 l_return_status VARCHAR2(10);
30 l_msg_count NUMBER;
31 l_msg_data VARCHAR2(2000);
32
33
34 /* LD Added for bug 6902514 -- change
35 Introduced "Transfer and Create STAT Batch in GMS" configuration type and the flexfield is named
36 PSP_CREATE_STAT_BATCH_IN_GMS,
37
38 If the config type value = N,
39 STAT batch is not created on GMS side but tieback happens on LD tables as if GMS batch has been created for STAT data
40
41 If the config type value = Y,
42 STAT batch gets created on GMS side, but the data cannot be distributed/costed as Grants product does not support STAT
43 transactions.
44 */
45
46 CURSOR create_stat_batch_in_gms_cur IS
47 SELECT NVL(pcv_information1,'N')
48 FROM pqp_configuration_values
49 WHERE pcv_information_category = 'PSP_CREATE_STAT_BATCH_IN_GMS'
50 AND legislation_code IS NULL
51 AND NVL(business_group_id, p_business_group_id) = p_business_group_id;
52
53
54
55 BEGIN
56
57 fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) ||' Begin Adj S&T');
58
59 g_error_api_path := '';
60 fnd_msg_pub.initialize;
61 psp_general.TRANSACTION_CHANGE_PURGEBLE;
62
63 -- g_currency_code := psp_general.get_currency_code(p_business_group_id); -- Added for Bug 2478000 commented for bug fix 2916848
64
65 g_dff_grouping_option := psp_general.get_act_dff_grouping_option(p_business_group_id); -- Introduced for bug fix 2908859
66
67 /*Added the following cursor open-fetch-close for Bug 6902514*/
68 fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) ||' open create_stat_batch_in_gms_cur');
69
70 open create_stat_batch_in_gms_cur;
71 fetch create_stat_batch_in_gms_cur into g_create_stat_batch_in_gms;
72 close create_stat_batch_in_gms_cur;
73
74 fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) ||' Calling mark_batch_begin');
75
76
77 mark_batch_begin(p_adj_sum_batch_name,
78 p_business_group_id,
79 p_set_of_books_id,
80 l_return_status);
81
82 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
83 -- Bug: 1994421.Commented the retcode and return statement as part of Zero work days enhancement.
84 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
85 --retcode := 2;
86 --return;
87 END IF;
88
89 psp_message_s.print_error(p_mode => FND_FILE.LOG,
90 p_print_header => FND_API.G_FALSE);
91
92 fnd_msg_pub.initialize;
93
94 fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) ||' Calling create_gms_sum_lines for p_adj_sum_batch_name: '||p_adj_sum_batch_name);
95
96 create_gms_sum_lines(p_adj_sum_batch_name,
97 p_business_group_id,
98 p_set_of_books_id,
99 l_return_status);
100
101 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
102 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
103 END IF;
104
105 -- call the user extension to populate attribute1 through attribute30
106 IF FND_PROFILE.VALUE('PSP_ST_EXTENSION_ENABLE') = 'Y' THEN
107 --- 2968684: added params and exception handler to proc.
108 fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) ||' calling EXT ADJ code 1');
109
110 psp_st_ext.summary_ext_adjustment(p_adj_sum_batch_name,
111 p_business_group_id ,
112 p_set_of_books_id );
113 END IF;
114
115 if g_run_type = 'R' then
116 fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) ||' Calling GMS check_interface_status');
117
118 check_interface_status('GMS', p_adj_sum_batch_name);
119 end if;
120
121 -- initiate the ogm summarization and transfer
122 if (g_gms_avail = 'Y' OR g_run_type = 'R') then
123 fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) ||' Calling transfer_to_gms_interface for p_adj_sum_batch_name: '||p_adj_sum_batch_name);
124
125 transfer_to_gms_interface(p_adj_sum_batch_name,
126 p_business_group_id,
127 p_set_of_books_id,
128 l_return_status);
129
130 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
131 fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) ||' Calling GMS check_interface_status for failure');
132
133 check_interface_status('GMS', p_adj_sum_batch_name);
134 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
135 END IF;
136
137 -- Check the target system status and do the tieback
138 fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) ||' Calling gms_tie_back');
139
140 gms_tie_back(p_adj_sum_batch_name,
141 p_business_group_id,
142 p_set_of_books_id,
143 l_return_status);
144
145 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
146 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
147 END IF;
148
149 end if; ---- end if for g_gms_avail = 'Y'
150 -- initiate the gl summarization and transfer
151 fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) ||' Calling create_gl_sum_lines for p_adj_sum_batch_name: '||p_adj_sum_batch_name);
152
153 create_gl_sum_lines(p_adj_sum_batch_name,
154 p_business_group_id,
155 p_set_of_books_id,
156 l_return_status);
157 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
158 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
159 END IF;
160
161 -- call the user extension to populate attribute1 through attribute30
162 IF FND_PROFILE.VALUE('PSP_ST_EXTENSION_ENABLE') = 'Y' THEN
163 --- 2968684: added params and exception handler to proc.
164 fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) ||' calling EXT ADJ code 2');
165
166 psp_st_ext.summary_ext_adjustment(p_adj_sum_batch_name,
167 p_business_group_id ,
168 p_set_of_books_id );
169 END IF;
170
171 if g_run_type = 'R' then
172 fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) ||' Calling GL check_interface_status ');
173
174 check_interface_status('GL', p_adj_sum_batch_name);
175 end if;
176 fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) ||' Calling transfer_to_gl_interface for p_adj_sum_batch_name: '||p_adj_sum_batch_name);
177
178 transfer_to_gl_interface(p_adj_sum_batch_name,
179 p_business_group_id,
180 p_set_of_books_id,
181 l_return_status);
182
183 IF l_return_status <> FND_API.G_RET_STS_SUCCESS
184 THEN
185 fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) ||' Calling GL check_interface_status for failure');
186
187 check_interface_status('GL', p_adj_sum_batch_name);
188 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
189 END IF;
190
191 -- dbms_output.put_line('Going to gl_tie_back');
192
193 fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) ||' Calling gl_tie_back ');
194
195 gl_tie_back(p_adj_sum_batch_name,
196 p_business_group_id,
197 p_set_of_books_id,
198 l_return_status);
199
200 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
201 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
202 end if;
203
204 fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) ||' Calling Mark batch_end ');
205
206 mark_batch_end(p_adj_sum_batch_name,
207 p_business_group_id,
208 p_set_of_books_id,
209 l_return_status);
210
211 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
212 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
213 END IF;
214
215 if fnd_msg_pub.Count_Msg > 0 then
216 psp_message_s.print_error(p_mode => FND_FILE.LOG,
217 p_print_header => FND_API.G_FALSE);
218 else
219 PSP_MESSAGE_S.Print_success;
220 end if;
221
222 retcode := FND_API.G_RET_STS_SUCCESS;
223 fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) ||' End of Adj S&T');
224
225 EXCEPTION
226 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
227 -- Bug 1776606 : Introduced Rollback ,
228 rollback;
229
230 psp_message_s.print_error(p_mode => FND_FILE.LOG,
231 p_print_header => FND_API.G_TRUE);
232 retcode := 2;
233 WHEN OTHERS THEN
234 --Bug 1776606 : Introduced Rollback
235 rollback;
236 psp_message_s.print_error(p_mode => FND_FILE.LOG,
237 p_print_header => FND_API.G_TRUE);
238 retcode := 2;
239 END;
240
241 -------------------- MARK BATCH BEGIN --------------------------------------------
242
243 PROCEDURE mark_batch_begin(p_adj_sum_batch_name IN VARCHAR2,
244 p_business_group_id IN NUMBER,
245 p_set_of_books_id IN NUMBER,
246 p_return_status OUT NOCOPY VARCHAR2) IS
247 --- replaced p_run_type with g_run_type for 244657
248 CURSOR pc_batch_cur IS
249 SELECT distinct ppc.batch_name
250 FROM psp_payroll_controls ppc,
251 psp_adjustment_control_table pact
252 WHERE ppc.status_code = decode(g_run_type, 'N', 'N', 'R', 'I')
253 AND ppc.source_type = 'A'
254 AND ppc.batch_name = pact.adjustment_batch_name
255 AND (g_run_type = 'N' OR (g_run_type = 'R' and
256 ppc.adj_sum_batch_name = p_adj_sum_batch_name))
257 AND (dist_dr_amount is not null or dist_cr_amount is not null)
258 AND ppc.business_group_id = p_business_group_id
259 AND ppc.set_of_books_id = p_set_of_books_id
260 AND (pact.approver_id is not null and pact.approver_id <> -999);
261
262 pc_batch_rec pc_batch_cur%ROWTYPE;
263
264 --- removed the pc_recover_cur ... for 2444657
265
266 CURSOR payroll_control_cur(p_batch_name IN VARCHAR2) IS
267 SELECT payroll_control_id,
268 source_type,
269 payroll_source_code,
270 time_period_id,
271 batch_name,
272 gms_phase
273 FROM psp_payroll_controls
274 WHERE source_type = 'A'
275 AND ((adj_sum_batch_name is null) OR
276 (adj_sum_batch_name is not null and run_id is not null))
277 AND (dist_dr_amount IS NOT NULL OR dist_cr_amount IS NOT NULL)
278 AND batch_name = p_batch_name
279 AND business_group_id = p_business_group_id
280 AND set_of_books_id = p_set_of_books_id
281 AND status_code in ( 'N','I'); --- added 'I' for 2444657
282
283 --- added cursor for 2444657
284 CURSOR derive_run_mode IS
285 select decode(count(*), 0, 'N', 'R')
286 from psp_payroll_controls
287 where source_type = 'A'
288 and adj_sum_batch_name = p_adj_sum_batch_name
289 and status_code = 'I';
290
291 CURSOR batch_name_exist_cur IS
292 SELECT count(*)
293 FROM psp_payroll_controls
294 WHERE adj_sum_batch_name = p_adj_sum_batch_name
295 AND source_type = 'A' -- Bug 2133056
296 AND g_run_type <> 'R';
297
298 payroll_control_rec payroll_control_cur%ROWTYPE;
299 l_batch_name_exists number;
300 l_batch_cnt NUMBER := 0;
301 l_error VARCHAR2(80);
302 l_return_status VARCHAR2(80);
303 l_batch_details_failed BOOLEAN;
304
305 BEGIN
306 -- Replaced the following lines of code for bug fix 1769610 and included new set of codes
307
308 SELECT psp_st_run_id_s.nextval
309 INTO g_run_id
310 FROM DUAL;
311
312
313 --- 2444657: derive run-mode
314 open derive_run_mode;
315 fetch derive_run_mode into g_run_type;
316 close derive_run_mode;
317
318 --dbms_output.put_line('Getting the existence of the batch name');
319 -- Replaced the following lines and included new set of lines for bug fix 1765678
320
321 OPEN batch_name_exist_cur;
322 FETCH batch_name_exist_cur INTO l_batch_name_exists;
323 IF (batch_name_exist_cur%NOTFOUND) THEN
324 CLOSE batch_name_exist_cur;
325 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
326 END IF;
327 CLOSE batch_name_exist_cur;
328
329 if l_batch_name_exists > 0 then
330 fnd_message.set_name('PSP','PSP_GB_NAME_EXISTS');
331 fnd_message.set_token('GB_NAME', p_adj_sum_batch_name);
332 fnd_msg_pub.add;
333 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
334 end if;
335
336 open pc_batch_cur;
337 loop
338 fetch pc_batch_cur into pc_batch_rec;
339 if pc_batch_cur%ROWCOUNT = 0 then
340 fnd_message.set_name('PSP', 'PSP_NO_BATCHES_EXIST');
341 fnd_msg_pub.add;
342 close pc_batch_cur;
343 exit;
344 elsif pc_batch_cur%NOTFOUND then
345 close pc_batch_cur;
346 exit;
347 end if;
348
349 l_batch_details_failed := FALSE;
350 l_batch_cnt := l_batch_cnt + 1;
351 fnd_message.set_name('PSP','PSP_GB_NAME');
352 fnd_message.set_token('GB_NAME',p_adj_sum_batch_name);
353 get_the_batch_details(pc_batch_rec.batch_name, l_return_status);
354 fnd_msg_pub.add;
355
356 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
357 l_batch_details_failed := TRUE;
358 ELSE
359 l_batch_details_failed := FALSE;
360 END IF; -- If Successful
361
362 --- removed call to obsoleted cursor from here ,,,2444657
363 OPEN payroll_control_cur(pc_batch_rec.batch_name);
364 LOOP
365 FETCH payroll_control_cur INTO payroll_control_rec;
366 IF payroll_control_cur%NOTFOUND THEN
367 CLOSE payroll_control_cur;
368 EXIT;
369 END IF;
370
371 if (l_batch_details_failed) then
372 cleanup_batch_details(payroll_control_rec.payroll_control_id,null);
373 /* Included as part of Bug fix #1776606 : Cleanup is at small batch level,
374 hence there is no need to call in loop*/
375 CLOSE payroll_control_cur;
376 EXIT;
377 else
378 if g_run_type = 'N' then
379 UPDATE psp_payroll_controls
380 SET status_code = 'I',
381 adj_sum_batch_name = p_adj_sum_batch_name,
382 run_id = g_run_id
383 WHERE payroll_control_id = payroll_control_rec.payroll_control_id;
384 fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) ||' IF updated psp_payroll_controls count: '||SQL%rowcount);
385
386 else
387 UPDATE psp_payroll_controls
388 SET run_id = g_run_id
389 WHERE payroll_control_id = payroll_control_rec.payroll_control_id;
390 fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) ||' ELSE updated psp_payroll_controls count: '||SQL%rowcount);
391
392 end if;
393 end if;
394
395 END LOOP;
396 end loop;
397 --commit;
398 p_return_status := fnd_api.g_ret_sts_success;
399 --Included as part of Bug fix #1776606
400 EXCEPTION
401 WHEN OTHERS THEN
402 fnd_msg_pub.add_exc_msg('PSP_SUM_ADJ','MARK_BATCH_BEGIN');
403 raise;
404 END;
405 -------------------- MARK BATCH END ---------------------------------------------
406
407 PROCEDURE mark_batch_end(p_adj_sum_batch_name IN VARCHAR2,
408 p_business_group_id IN NUMBER,
409 p_set_of_books_id IN NUMBER,
410 p_return_status OUT NOCOPY VARCHAR2) IS
411
412 CURSOR pc_batch_cur IS
413 SELECT distinct batch_name
414 FROM psp_payroll_controls
415 WHERE source_type = 'A'
416 AND adj_sum_batch_name = p_adj_sum_batch_name
417 AND (dist_dr_amount is not null or dist_cr_amount is not null)
418 -- AND (gl_phase = 'GL_Tie_Back' OR gms_phase = 'GMS_Tie_Back') : Bug 1776606 : Commented out
419 -- Bug 1776606 : Added the next two conditions..Phase =Null or GMS_TIE_BACK /GL_TIE_BACK
420 AND (gms_phase is null or gms_phase = 'GMS_Tie_Back')
421 AND (gl_phase is null or gl_phase = 'GL_Tie_Back')
422 AND status_code = 'I'
423 AND business_group_id = p_business_group_id
424 AND set_of_books_id = p_set_of_books_id
425 AND run_id = nvl(g_run_id, run_id);
426
427 pc_batch_rec pc_batch_cur%ROWTYPE;
428
429 CURSOR payroll_control_cur(p_batch_name IN VARCHAR2) IS
430 SELECT payroll_control_id,
431 source_type,
432 payroll_source_code,
433 time_period_id,
434 batch_name,
435 gl_phase,
436 gms_phase
437 FROM psp_payroll_controls
438 WHERE source_type = 'A'
439 AND batch_name = p_batch_name
440 AND (dist_dr_amount IS NOT NULL OR dist_cr_amount IS NOT NULL)
441 AND status_code = 'I'
442 AND run_id = g_run_id;
443
444
445 --Bug 1776606 : Introduced the two cursors
446 CURSOR sum_lines_cur(P_PAYROLL_CONTROL_ID IN NUMBER) IS
447 SELECT count(*)
448 FROM psp_summary_lines
449 WHERE payroll_control_id = p_payroll_control_id
450 AND status_code <> 'A';
451
452 CURSOR adj_lines_cur(P_PAYROLL_CONTROL_ID IN NUMBER) IS
453 SELECT count(*)
454 FROM psp_adjustment_lines
455 WHERE payroll_control_id = p_payroll_control_id
456 AND gl_code_combination_id is not null
457 AND status_code <> 'A';
458
459
460 payroll_control_rec payroll_control_cur%ROWTYPE;
461 l_errbuf VARCHAR2(2000);
462
463 --Bug 1776606 : Introducing the variables
464 l_sum_count NUMBER := 0;
465 l_adj_count NUMBER := 0;
466 retcode varchar2(500);
467 l_return_status VARCHAR2(10);
468
469 /* Start of Changes to check migration to OAF Effort Reporting before Supercedence */
470
471 l_migration_status BOOLEAN:= psp_general.is_effort_report_migrated;
472
473
474 /* End of Cahnges to check migration to OAF Effort Reporting before Supercedence */
475
476 BEGIN
477
478 --- call to supercede Effort reports
479
480
481
482 /* Start of Changes to check migration to OAF Effort Reporting before Supercedence */
483
484 IF l_migration_status THEN
485
486 fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) ||' Calling PSP_SUM_TRANS.SUPERCEDE_ER');
487
488
489 PSP_SUM_TRANS.SUPERCEDE_ER(g_run_id,
490 l_errbuf,
491 retcode,
492 'A' ,
493 'Adjustments',
494 null,
495 p_adj_sum_batch_name,
496 l_return_status);
497 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
498 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
499 END IF;
500
501
502 END IF;
503
504 /* End Changes to check migration to OAF Effort Reporting before Supercedence */
505
506 /* Bug 2133056: Moved this stmt from Tie Back process */
507
508 DELETE FROM pa_transaction_interface_all
509 -- WHERE batch_name = g_gms_batch_name
510 WHERE batch_name IN (SELECT GMS_BATCH_NAME
511 FROM psp_summary_lines
512 WHERE PAYROLL_CONTROL_ID IN(SELECT payroll_control_id
513 FROM psp_payroll_controls
514 WHERE adj_sum_batch_name =p_adj_sum_batch_name))
515 ---AND transaction_status_code = 'A' delete 'R' also, for 2445196
516 AND transaction_source in ('OLD', 'GOLD');
517
518 fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) ||' delete from pa_transaction_interface_all count : '||sql%rowcount);
519
520 -- introduced following stmnt for 2445196
521 delete from gms_transaction_interface_all
522 -- where batch_name = g_gms_batch_name
523 WHERE batch_name IN (SELECT GMS_BATCH_NAME
524 FROM psp_summary_lines
525 WHERE PAYROLL_CONTROL_ID IN(SELECT payroll_control_id
526 FROM psp_payroll_controls
527 WHERE adj_sum_batch_name =p_adj_sum_batch_name))
528 and transaction_source = 'GOLD';
529
530 fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) ||' delete from gms_transaction_interface_all count : '||sql%rowcount);
531
532 OPEN pc_batch_cur;
533 LOOP
534 FETCH pc_batch_cur into pc_batch_rec;
535 if pc_batch_cur%NOTFOUND then
536 close pc_batch_cur;
537 exit;
538 end if;
539
540
541 OPEN payroll_control_cur(pc_batch_rec.batch_name);
542 LOOP
543 FETCH payroll_control_cur INTO payroll_control_rec;
544 IF payroll_control_cur%NOTFOUND THEN
545 CLOSE payroll_control_cur;
546 EXIT;
547 END IF;
548
549 -- Bug 1776606 : Intoduced the following code
550 l_sum_count := 0;
551 l_adj_count := 0;
552 OPEN sum_lines_cur(payroll_control_rec.payroll_control_id);
553 FETCH sum_lines_cur INTO l_sum_count;
554 fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) ||' l_sum_count: '||l_sum_count);
555
556 CLOSE sum_lines_cur;
557
558 OPEN adj_lines_cur(payroll_control_rec.payroll_control_id);
559 FETCH adj_lines_cur INTO l_adj_count;
560 fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) ||' l_adj_count: '||l_adj_count);
561
562 CLOSE adj_lines_cur;
563
564 IF (l_sum_count =0) and (l_adj_count =0) THEN
565 UPDATE psp_payroll_controls
566 SET status_code = 'P'
567 WHERE payroll_control_id = payroll_control_rec.payroll_control_id;
568 fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) ||' UPDATE psp_payroll_controls status_code P count: '||sql%rowcount);
569
570 END IF;
571
572 END LOOP;
573 END LOOP;
574
575 COMMIT;
576
577 EXCEPTION
578 WHEN OTHERS THEN
579 -- Bug 1776606 : Modifying the message to be displayed
580 -- FND_MSG_PUB.ADD_EXC_MSG('PSP_SUM_ADJ', SQLERRM);
581 fnd_msg_pub.add_exc_msg('PSP_SUM_ADJ','MARK_BATCH_END');
582 raise;
583 END;
584
585
586 -------------------- CREATE GL SUM LINES -----------------------------------------------
587
588 PROCEDURE create_gl_sum_lines(p_adj_sum_batch_name IN VARCHAR2,
589 p_business_group_id IN NUMBER,
590 p_set_of_books_id IN NUMBER,
591 p_return_status OUT NOCOPY VARCHAR2) IS
592
593 CURSOR pc_batch_cur IS
594 SELECT distinct batch_name
595 FROM psp_payroll_controls
596 WHERE source_type = 'A'
597 AND status_code = 'I'
598 AND run_id = nvl(g_run_id, run_id)
599 AND gl_phase is null
600 AND adj_sum_batch_name = p_adj_sum_batch_name
601 AND business_group_id = p_business_group_id
602 AND set_of_books_id = p_set_of_books_id
603 AND (dist_dr_amount is not null or dist_cr_amount is not null);
604
605 pc_batch_rec pc_batch_cur%ROWTYPE;
606
607 CURSOR payroll_control_cur(p_batch_name IN VARCHAR2) IS
608 SELECT payroll_control_id,
609 source_type,
610 payroll_source_code,
611 time_period_id,
612 batch_name,
613 gl_posting_override_date
614 FROM psp_payroll_controls
615 WHERE batch_name = p_batch_name
616 AND (dist_dr_amount IS NOT NULL OR dist_cr_amount IS NOT NULL)
617 AND source_type = 'A'
618 AND status_code = 'I'
619 AND run_id = nvl(g_run_id, run_id);
620
621 CURSOR gl_sum_lines_cursor(P_PAYROLL_CONTROL_ID IN NUMBER) IS
622 SELECT pal.person_id,
623 pal.assignment_id,
624 pal.gl_code_combination_id gl_ccid,
625 pal.dr_cr_flag,
626 pal.effective_date,
627 psl.accounting_date, -- removed nvl on a/c date for 4734810
628 psl.exchange_rate_type,
629 pal.distribution_amount,
630 pal.adjustment_line_id distribution_line_id,
631 'A' tab_flag,
632 DECODE(g_dff_grouping_option, 'Y', pal.attribute_category, NULL) attribute_category, -- Introduced DFF columns for bug fix 2908859
633 DECODE(g_dff_grouping_option, 'Y', pal.attribute1, NULL) attribute1,
634 DECODE(g_dff_grouping_option, 'Y', pal.attribute2, NULL) attribute2,
635 DECODE(g_dff_grouping_option, 'Y', pal.attribute3, NULL) attribute3,
636 DECODE(g_dff_grouping_option, 'Y', pal.attribute4, NULL) attribute4,
637 DECODE(g_dff_grouping_option, 'Y', pal.attribute5, NULL) attribute5,
638 DECODE(g_dff_grouping_option, 'Y', pal.attribute6, NULL) attribute6,
639 DECODE(g_dff_grouping_option, 'Y', pal.attribute7, NULL) attribute7,
640 DECODE(g_dff_grouping_option, 'Y', pal.attribute8, NULL) attribute8,
641 DECODE(g_dff_grouping_option, 'Y', pal.attribute9, NULL) attribute9,
642 DECODE(g_dff_grouping_option, 'Y', pal.attribute10, NULL) attribute10
643 FROM psp_adjustment_lines pal,
644 psp_distribution_lines_history pdlh,
645 psp_summary_lines psl
646 WHERE pal.status_code = 'N'
647 AND pal.gl_code_combination_id is not null
648 AND pal.payroll_control_id = p_payroll_control_id
649 AND pal.orig_source_type = 'D'
650 AND pal.orig_line_id = pdlh.distribution_line_id
651 AND pdlh.summary_line_id = psl.summary_line_id
652 UNION --- added union to get accounting dates -- 3108109
653 SELECT pal.person_id,
654 pal.assignment_id,
655 pal.gl_code_combination_id gl_ccid,
656 pal.dr_cr_flag,
657 pal.effective_date,
658 psl.accounting_date, ---3108109
659 psl.exchange_rate_type,
660 pal.distribution_amount,
661 pal.adjustment_line_id distribution_line_id,
662 'A' tab_flag,
663 DECODE(g_dff_grouping_option, 'Y', pal.attribute_category, NULL) attribute_category, -- Introduced DFF columns for bug fix 2908859
664 DECODE(g_dff_grouping_option, 'Y', pal.attribute1, NULL) attribute1,
665 DECODE(g_dff_grouping_option, 'Y', pal.attribute2, NULL) attribute2,
666 DECODE(g_dff_grouping_option, 'Y', pal.attribute3, NULL) attribute3,
667 DECODE(g_dff_grouping_option, 'Y', pal.attribute4, NULL) attribute4,
668 DECODE(g_dff_grouping_option, 'Y', pal.attribute5, NULL) attribute5,
669 DECODE(g_dff_grouping_option, 'Y', pal.attribute6, NULL) attribute6,
670 DECODE(g_dff_grouping_option, 'Y', pal.attribute7, NULL) attribute7,
671 DECODE(g_dff_grouping_option, 'Y', pal.attribute8, NULL) attribute8,
672 DECODE(g_dff_grouping_option, 'Y', pal.attribute9, NULL) attribute9,
673 DECODE(g_dff_grouping_option, 'Y', pal.attribute10, NULL) attribute10
674 FROM psp_adjustment_lines pal,
675 psp_adjustment_lines_history palh,
676 psp_summary_lines psl
677 WHERE pal.status_code = 'N'
678 AND pal.gl_code_combination_id is not null
679 AND pal.payroll_control_id = p_payroll_control_id
680 AND pal.orig_source_type = 'A'
681 AND pal.orig_line_id = palh.adjustment_line_id
682 AND palh.summary_line_id = psl.summary_line_id
683 UNION
684 SELECT pal.person_id,
685 pal.assignment_id,
686 pal.gl_code_combination_id gl_ccid,
687 pal.dr_cr_flag,
688 pal.effective_date,
689 psl.accounting_date, ---3108109
690 psl.exchange_rate_type,
691 pal.distribution_amount,
692 pal.adjustment_line_id distribution_line_id,
693 'A' tab_flag,
694 DECODE(g_dff_grouping_option, 'Y', pal.attribute_category, NULL) attribute_category, -- Introduced DFF columns for bug fix 2908859
695 DECODE(g_dff_grouping_option, 'Y', pal.attribute1, NULL) attribute1,
696 DECODE(g_dff_grouping_option, 'Y', pal.attribute2, NULL) attribute2,
697 DECODE(g_dff_grouping_option, 'Y', pal.attribute3, NULL) attribute3,
698 DECODE(g_dff_grouping_option, 'Y', pal.attribute4, NULL) attribute4,
699 DECODE(g_dff_grouping_option, 'Y', pal.attribute5, NULL) attribute5,
700 DECODE(g_dff_grouping_option, 'Y', pal.attribute6, NULL) attribute6,
701 DECODE(g_dff_grouping_option, 'Y', pal.attribute7, NULL) attribute7,
702 DECODE(g_dff_grouping_option, 'Y', pal.attribute8, NULL) attribute8,
703 DECODE(g_dff_grouping_option, 'Y', pal.attribute9, NULL) attribute9,
704 DECODE(g_dff_grouping_option, 'Y', pal.attribute10, NULL) attribute10
705 FROM psp_adjustment_lines pal,
706 psp_pre_gen_dist_lines_history pglh,
707 psp_summary_lines psl
708 WHERE pal.status_code = 'N'
709 AND pal.gl_code_combination_id is not null
710 AND pal.payroll_control_id = p_payroll_control_id
711 AND pal.orig_source_type = 'P'
712 AND pal.orig_line_id = pglh.pre_gen_dist_line_id
713 AND pglh.summary_line_id = psl.summary_line_id
714 ORDER BY 1,2,3,4,6,7,11,12,13,14,15,16,17,18,19,20,21,5;
715 --- added 2 columns for 3108109
716 --- changed the order by clause for 6007017
717
718 -- l_sob_id NUMBER(15) := FND_PROFILE.VALUE('PSP_SET_OF_BOOKS'); --Passed as a parameter.
719 l_sob_id NUMBER(15) := p_set_of_books_id;
720 l_person_id NUMBER(9);
721 l_assignment_id NUMBER(9);
722 l_gl_ccid NUMBER(15);
723 l_dr_cr_flag VARCHAR2(1);
724 l_effective_date DATE;
725 l_distribution_amount NUMBER;
726 l_rec_count NUMBER := 0;
727 l_summary_amount NUMBER := 0;
728
729 l_summary_line_id NUMBER(10);
730 gl_sum_lines_rec gl_sum_lines_cursor%ROWTYPE;
731 TYPE dist_id IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
732 dist_line_id dist_id;
733 l_dist_line_id NUMBER;
734 i BINARY_INTEGER := 0;
735 j NUMBER;
736 l_return_status VARCHAR2(10);
737 payroll_control_rec payroll_control_cur%ROWTYPE;
738 l_msg_id number(9);
739 l_accounting_date date; --- added for 3108109
740 l_exchange_rate_type varchar2(30);
741 l_attribute_category VARCHAR2(30); -- Introduced DFF variables for bug fix 2908859
742 l_attribute1 VARCHAR2(150);
743 l_attribute2 VARCHAR2(150);
744 l_attribute3 VARCHAR2(150);
745 l_attribute4 VARCHAR2(150);
746 l_attribute5 VARCHAR2(150);
747 l_attribute6 VARCHAR2(150);
748 l_attribute7 VARCHAR2(150);
749 l_attribute8 VARCHAR2(150);
750 l_attribute9 VARCHAR2(150);
751 l_attribute10 VARCHAR2(150);
752 BEGIN
753 fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) ||' Entering create_gl_sum_lines');
754
755
756 OPEN pc_batch_cur;
757 LOOP
758 FETCH pc_batch_cur into pc_batch_rec;
759 IF pc_batch_cur%NOTFOUND THEN
760 CLOSE pc_batch_cur;
761 EXIT;
762 END IF;
763
764 OPEN payroll_control_cur(pc_batch_rec.batch_name);
765 LOOP
766 FETCH payroll_control_cur INTO payroll_control_rec;
767 IF payroll_control_cur%NOTFOUND THEN
768 CLOSE payroll_control_cur;
769 EXIT;
770 END IF;
771 /* move this procedure below for 3108109
772 -- create balancing transactions for GL
773 gl_balance_transaction(payroll_control_rec.source_type,
774 payroll_control_rec.payroll_control_id,
775 p_business_group_id,
776 p_set_of_books_id,
777 l_return_status);
778
779 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
780 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
781 END IF;
782 */
783
784 OPEN gl_sum_lines_cursor(payroll_control_rec.payroll_control_id);
785 l_rec_count := 0;
786 l_summary_amount := 0;
787 i := 0;
788 LOOP
789 FETCH gl_sum_lines_cursor INTO gl_sum_lines_rec;
790 l_rec_count := l_rec_count + 1;
791 IF gl_sum_lines_cursor%NOTFOUND THEN
792 if (l_rec_count > 1) then
793 update psp_payroll_controls
794 set gl_phase = 'Summarize_GL_Lines'
795 where payroll_control_id = payroll_control_rec.payroll_control_id;
796 fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) ||' Updating psp_payroll_controls gl_phase to Summarize_GL_Lines for control_id: '||payroll_control_rec.payroll_control_id);
797
798 end if;
799 CLOSE gl_sum_lines_cursor;
800 EXIT;
801 END IF;
802 --
803 IF l_rec_count = 1 THEN
804 l_person_id := gl_sum_lines_rec.person_id;
805 l_assignment_id := gl_sum_lines_rec.assignment_id;
806 l_gl_ccid := gl_sum_lines_rec.gl_ccid;
807 l_dr_cr_flag := gl_sum_lines_rec.dr_cr_flag;
808 l_effective_date := nvl(payroll_control_rec.gl_posting_override_date,
809 gl_sum_lines_rec.effective_date);
810 l_accounting_date := gl_sum_lines_rec.accounting_date; --- added for 3108109
811 l_exchange_rate_type := gl_sum_lines_rec.exchange_rate_type;
812 l_attribute_category := gl_sum_lines_rec.attribute_category; -- Introduced DFF variables for bug fix 2908859
813 l_attribute1 := gl_sum_lines_rec.attribute1;
814 l_attribute2 := gl_sum_lines_rec.attribute2;
815 l_attribute3 := gl_sum_lines_rec.attribute3;
816 l_attribute4 := gl_sum_lines_rec.attribute4;
817 l_attribute5 := gl_sum_lines_rec.attribute5;
818 l_attribute6 := gl_sum_lines_rec.attribute6;
819 l_attribute7 := gl_sum_lines_rec.attribute7;
820 l_attribute8 := gl_sum_lines_rec.attribute8;
821 l_attribute9 := gl_sum_lines_rec.attribute9;
822 l_attribute10 := gl_sum_lines_rec.attribute10;
823 END IF;
824
825 IF l_person_id <> gl_sum_lines_rec.person_id OR
826 l_assignment_id <> gl_sum_lines_rec.assignment_id OR
827 l_gl_ccid <> gl_sum_lines_rec.gl_ccid OR
828 l_dr_cr_flag <> gl_sum_lines_rec.dr_cr_flag OR
829 (NVL(l_attribute_category, 'NULL') <> NVL(gl_sum_lines_rec.attribute_category, 'NULL')) OR -- Introduced DFF check for bug fix 2908859
830 (NVL(l_attribute1, 'NULL') <> NVL(gl_sum_lines_rec.attribute1, 'NULL')) OR
831 (NVL(l_attribute2, 'NULL') <> NVL(gl_sum_lines_rec.attribute2, 'NULL')) OR
832 (NVL(l_attribute3, 'NULL') <> NVL(gl_sum_lines_rec.attribute3, 'NULL')) OR
833 (NVL(l_attribute4, 'NULL') <> NVL(gl_sum_lines_rec.attribute4, 'NULL')) OR
834 (NVL(l_attribute5, 'NULL') <> NVL(gl_sum_lines_rec.attribute5, 'NULL')) OR
835 (NVL(l_attribute6, 'NULL') <> NVL(gl_sum_lines_rec.attribute6, 'NULL')) OR
836 (NVL(l_attribute7, 'NULL') <> NVL(gl_sum_lines_rec.attribute7, 'NULL')) OR
837 (NVL(l_attribute8, 'NULL') <> NVL(gl_sum_lines_rec.attribute8, 'NULL')) OR
838 (NVL(l_attribute9, 'NULL') <> NVL(gl_sum_lines_rec.attribute9, 'NULL')) OR
839 (NVL(l_attribute10, 'NULL') <> NVL(gl_sum_lines_rec.attribute10, 'NULL')) OR
840 --- added accounting_date condn for 3108109, 4734810
841 nvl(l_accounting_date, fnd_date.canonical_to_date('1800/01/31')) <>
842 nvl( gl_sum_lines_rec.accounting_date, fnd_date.canonical_to_date('1800/01/31')) OR
843 nvl(l_exchange_rate_type,'-999') <>
844 nvl(gl_sum_lines_rec.exchange_rate_type,'-999') THEN
845
846 fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) ||' calling insert_into_summary_lines');
847
848 insert_into_summary_lines(
849 l_summary_line_id,
850 l_person_id,
851 l_assignment_id,
852 payroll_control_rec.time_period_id,
853 l_effective_date,
854 l_accounting_date, -- added for 3108109
855 l_exchange_rate_type,
856 payroll_control_rec.source_type,
857 payroll_control_rec.payroll_source_code,
858 l_sob_id,
859 l_gl_ccid,
860 NULL,
861 NULL,
862 NULL,
863 NULL,
864 NULL,
865 l_summary_amount,
866 l_dr_cr_flag,
867 'N',
868 payroll_control_rec.batch_name,
869 payroll_control_rec.payroll_control_id,
870 p_business_group_id,
871 l_attribute_category, -- Introduced DFF parameters for bug fix 2908859
872 l_attribute1,
873 l_attribute2,
874 l_attribute3,
875 l_attribute4,
876 l_attribute5,
877 l_attribute6,
878 l_attribute7,
879 l_attribute8,
880 l_attribute9,
881 l_attribute10,
882 l_return_status);
883
884 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
885 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
886 END IF;
887 fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) ||' calling insert_into_summary_lines');
888
889
890 FOR j IN 1 .. dist_line_id.COUNT LOOP
891
892 l_dist_line_id := dist_line_id(j);
893
894 IF gl_sum_lines_rec.tab_flag = 'A' THEN
895 UPDATE psp_adjustment_lines
896 SET summary_line_id = l_summary_line_id
897 WHERE adjustment_line_id = l_dist_line_id;
898 END IF;
899
900 END LOOP;
901 fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) ||' Updated psp_adjustment_lines 1');
902
903
904 -- initialise the summary amount and dist_line_id
905 l_summary_amount := 0;
906 dist_line_id.delete;
907 i := 0;
908 END IF;
909
910 l_person_id := gl_sum_lines_rec.person_id;
911 l_assignment_id := gl_sum_lines_rec.assignment_id;
912 l_gl_ccid := gl_sum_lines_rec.gl_ccid;
913 l_dr_cr_flag := gl_sum_lines_rec.dr_cr_flag;
914 l_effective_date := nvl(payroll_control_rec.gl_posting_override_date,
915 gl_sum_lines_rec.effective_date);
916 l_accounting_date := gl_sum_lines_rec.accounting_date; --- added for 3108109
917 l_exchange_rate_type := gl_sum_lines_rec.exchange_rate_type;
918 l_attribute_category := gl_sum_lines_rec.attribute_category; -- Introduced DFF variables for bug fix 2908859
919 l_attribute1 := gl_sum_lines_rec.attribute1;
920 l_attribute2 := gl_sum_lines_rec.attribute2;
921 l_attribute3 := gl_sum_lines_rec.attribute3;
922 l_attribute4 := gl_sum_lines_rec.attribute4;
923 l_attribute5 := gl_sum_lines_rec.attribute5;
924 l_attribute6 := gl_sum_lines_rec.attribute6;
925 l_attribute7 := gl_sum_lines_rec.attribute7;
926 l_attribute8 := gl_sum_lines_rec.attribute8;
927 l_attribute9 := gl_sum_lines_rec.attribute9;
928 l_attribute10 := gl_sum_lines_rec.attribute10;
929
930 l_summary_amount := l_summary_amount + gl_sum_lines_rec.distribution_amount;
931 i := i + 1;
932 dist_line_id(i) := gl_sum_lines_rec.distribution_line_id;
933
934 END LOOP;
935
936 IF l_rec_count > 1 THEN
937 -- insert into summary lines
938 insert_into_summary_lines(
939 l_summary_line_id,
940 l_person_id,
941 l_assignment_id,
942 payroll_control_rec.time_period_id,
943 l_effective_date,
944 l_accounting_date, --- added for 3108109
945 l_exchange_rate_type,
946 payroll_control_rec.source_type,
947 payroll_control_rec.payroll_source_code,
948 l_sob_id,
949 l_gl_ccid,
950 NULL,
951 NULL,
952 NULL,
953 NULL,
954 NULL,
955 l_summary_amount,
956 l_dr_cr_flag,
957 'N',
958 payroll_control_rec.batch_name,
959 payroll_control_rec.payroll_control_id,
960 p_business_group_id,
961 l_attribute_category, -- Introduced DFF parameters for bug fix 2908859
962 l_attribute1,
963 l_attribute2,
964 l_attribute3,
965 l_attribute4,
966 l_attribute5,
967 l_attribute6,
968 l_attribute7,
969 l_attribute8,
970 l_attribute9,
971 l_attribute10,
972 l_return_status);
973
974
975 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
976 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
977 END IF;
978 fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) ||' After insert_into_summary_lines 2 l_summary_line_id: '||l_summary_line_id);
979
980
981 FOR j IN 1 .. dist_line_id.COUNT LOOP
982 l_dist_line_id := dist_line_id(j);
983
984 IF gl_sum_lines_rec.tab_flag = 'A' THEN
985 UPDATE psp_adjustment_lines
986 SET summary_line_id = l_summary_line_id,
987 status_code = 'N'
988 WHERE adjustment_line_id = l_dist_line_id;
989 END IF;
990 END LOOP;
991 fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) ||' Updated psp_adjustment_lines 2 set status_code to N');
992
993 -- moved this code from above for 3108109
994 if dist_line_id.count > 0 then
995 fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) ||' Calling gl_balance_transaction');
996
997 gl_balance_transaction(payroll_control_rec.source_type,
998 payroll_control_rec.payroll_control_id,
999 p_business_group_id,
1000 p_set_of_books_id,
1001 l_return_status);
1002
1003 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1004 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1005 END IF;
1006 end if;
1007 dist_line_id.delete;
1008 END IF;
1009 END LOOP; -- end loop for payroll_control_cur
1010 END LOOP; -- end loop for pc_batch_cur
1011
1012 --
1013 p_return_status := fnd_api.g_ret_sts_success;
1014
1015 EXCEPTION
1016
1017 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1018 g_error_api_path := 'CREATE_GL_SUM_LINES:'||g_error_api_path;
1019 fnd_msg_pub.add_exc_msg('PSP_SUM_ADJ','CREATE_GL_SUM_LINES');
1020 p_return_status := fnd_api.g_ret_sts_unexp_error;
1021 WHEN OTHERS THEN
1022 g_error_api_path := 'CREATE_GL_SUM_LINES:'||g_error_api_path;
1023 fnd_msg_pub.add_exc_msg('PSP_SUM_ADJ','CREATE_GL_SUM_LINES');
1024 p_return_status := fnd_api.g_ret_sts_unexp_error;
1025
1026 END;
1027
1028
1029 ---------------------- I N S E R T S T A T E M E N T ------------------------------------
1030 PROCEDURE insert_into_summary_lines(
1031 P_SUMMARY_LINE_ID OUT NOCOPY NUMBER,
1032 P_PERSON_ID IN NUMBER,
1033 P_ASSIGNMENT_ID IN NUMBER,
1034 P_TIME_PERIOD_ID IN NUMBER,
1035 P_EFFECTIVE_DATE IN DATE,
1036 P_ACCOUNTING_DATE IN DATE, ---added-> 3108109
1037 P_EXCHANGE_RATE_TYPE IN VARCHAR2,
1038 P_SOURCE_TYPE IN VARCHAR2,
1039 P_SOURCE_CODE IN VARCHAR2,
1040 P_SET_OF_BOOKS_ID IN NUMBER,
1041 P_GL_CODE_COMBINATION_ID IN NUMBER,
1042 P_PROJECT_ID IN NUMBER,
1043 P_EXPENDITURE_ORGANIZATION_ID IN NUMBER,
1044 P_EXPENDITURE_TYPE IN VARCHAR2,
1045 P_TASK_ID IN NUMBER,
1046 P_AWARD_ID IN NUMBER,
1047 P_SUMMARY_AMOUNT IN NUMBER,
1048 P_DR_CR_FLAG IN VARCHAR2,
1049 P_STATUS_CODE IN VARCHAR2,
1050 P_INTERFACE_BATCH_NAME IN VARCHAR2,
1051 P_PAYROLL_CONTROL_ID IN NUMBER,
1052 P_BUSINESS_GROUP_ID IN NUMBER,
1053 p_attribute_category IN VARCHAR2, -- Introduced DFF parameters for bug fix 2908859
1054 p_attribute1 IN VARCHAR2,
1055 p_attribute2 IN VARCHAR2,
1056 p_attribute3 IN VARCHAR2,
1057 p_attribute4 IN VARCHAR2,
1058 p_attribute5 IN VARCHAR2,
1059 p_attribute6 IN VARCHAR2,
1060 p_attribute7 IN VARCHAR2,
1061 p_attribute8 IN VARCHAR2,
1062 p_attribute9 IN VARCHAR2,
1063 p_attribute10 IN VARCHAR2,
1064 P_RETURN_STATUS OUT NOCOPY VARCHAR2,
1065 P_ORG_ID IN NUMBER DEFAULT NULL -- R12 MOAc uptake
1066 ) IS
1067 l_msg_id number(9);
1068 l_gms_posting_effective_date DATE; /* Bug: 1994421 Variable Initialized. */
1069 BEGIN
1070
1071 -- Bug 1994421
1072 -- Code added for Enhancement Employee Assignment with Zero Work Days
1073 IF P_PROJECT_ID IS NOT NULL THEN
1074 l_gms_posting_effective_date:= p_effective_date;
1075 psp_general.get_gms_effective_date(p_person_id,l_gms_posting_effective_date);
1076 END IF;
1077 -- Code ended for Enhancement Employee Assignment with Zero Work Days
1078
1079
1080 SELECT PSP_SUMMARY_LINES_S.NEXTVAL
1081 INTO P_SUMMARY_LINE_ID
1082 FROM DUAL;
1083
1084 INSERT INTO PSP_SUMMARY_LINES(
1085 SUMMARY_LINE_ID,
1086 PERSON_ID,
1087 ASSIGNMENT_ID,
1088 TIME_PERIOD_ID,
1089 EFFECTIVE_DATE,
1090 ACCOUNTING_DATE,
1091 EXCHANGE_RATE_TYPE,
1092 GMS_POSTING_EFFECTIVE_DATE, /* New column added for Enhancement Employee Assignment with Zero Work Days */
1093 SOURCE_TYPE,
1094 SOURCE_CODE,
1095 SET_OF_BOOKS_ID,
1096 GL_CODE_COMBINATION_ID,
1097 PROJECT_ID,
1098 EXPENDITURE_ORGANIZATION_ID,
1099 EXPENDITURE_TYPE,
1100 TASK_ID,
1101 AWARD_ID,
1102 SUMMARY_AMOUNT,
1103 DR_CR_FLAG,
1104 STATUS_CODE,
1105 INTERFACE_BATCH_NAME,
1106 PAYROLL_CONTROL_ID,
1107 BUSINESS_GROUP_ID,
1108 LAST_UPDATE_DATE,
1109 LAST_UPDATED_BY,
1110 LAST_UPDATE_LOGIN,
1111 CREATED_BY,
1112 CREATION_DATE,
1113 ACTUAL_SUMMARY_AMOUNT, --For Bug 2496661
1114 attribute_category, -- Introduced DFF columns for bug fix 2908859
1115 attribute1,
1116 attribute2,
1117 attribute3,
1118 attribute4,
1119 attribute5,
1120 attribute6,
1121 attribute7,
1122 attribute8,
1123 attribute9,
1124 attribute10,
1125 org_id -- R12 MOAc uptake
1126 )
1127 VALUES(
1128 P_SUMMARY_LINE_ID,
1129 P_PERSON_ID,
1130 P_ASSIGNMENT_ID,
1131 P_TIME_PERIOD_ID,
1132 P_EFFECTIVE_DATE,
1133 P_ACCOUNTING_DATE, --- 3108109
1134 P_EXCHANGE_RATE_TYPE,
1135 L_GMS_POSTING_EFFECTIVE_DATE, /* New column added for Enhancement Employee Assignment with Zero Work Days */
1136 P_SOURCE_TYPE,
1137 P_SOURCE_CODE,
1138 P_SET_OF_BOOKS_ID,
1139 P_GL_CODE_COMBINATION_ID,
1140 P_PROJECT_ID,
1141 P_EXPENDITURE_ORGANIZATION_ID,
1142 P_EXPENDITURE_TYPE,
1143 P_TASK_ID,
1144 P_AWARD_ID,
1145 P_SUMMARY_AMOUNT,
1146 P_DR_CR_FLAG,
1147 P_STATUS_CODE,
1148 P_INTERFACE_BATCH_NAME,
1149 P_PAYROLL_CONTROL_ID,
1150 P_BUSINESS_GROUP_ID,
1151 SYSDATE,
1152 FND_GLOBAL.USER_ID,
1153 FND_GLOBAL.LOGIN_ID,
1154 FND_GLOBAL.USER_ID,
1155 SYSDATE,
1156 DECODE(P_PROJECT_ID, NULL,P_SUMMARY_AMOUNT, DECODE(P_DR_CR_FLAG,'C',0 - P_SUMMARY_AMOUNT,P_SUMMARY_AMOUNT)), --For Bug 2496661
1157 p_attribute_category, -- Introduced DFF columns for bug fix 2908859
1158 p_attribute1,
1159 p_attribute2,
1160 p_attribute3,
1161 p_attribute4,
1162 p_attribute5,
1163 p_attribute6,
1164 p_attribute7,
1165 p_attribute8,
1166 p_attribute9,
1167 p_attribute10,
1168 P_ORG_ID -- R12 MOAc uptake
1169 );
1170 --
1171 p_return_status := fnd_api.g_ret_sts_success;
1172 EXCEPTION
1173 WHEN OTHERS THEN
1174 g_error_api_path := 'INSERT_INTO_SUMMARY_LINES:'||g_error_api_path;
1175 fnd_msg_pub.add_exc_msg('PSP_SUM_ADJ','INSERT_INTO_SUMMARY_LINES');
1176 p_return_status := fnd_api.g_ret_sts_unexp_error;
1177 END;
1178
1179 ------------------------ GL INTERFACE --------------------------------------------------
1180
1181 PROCEDURE transfer_to_gl_interface(p_adj_sum_batch_name IN VARCHAR2,
1182 p_business_group_id IN NUMBER,
1183 p_set_of_books_id IN NUMBER,
1184 p_return_status OUT NOCOPY VARCHAR2) IS
1185
1186 CURSOR pc_batch_cur IS
1187 SELECT distinct batch_name
1188 FROM psp_payroll_controls
1189 WHERE adj_sum_batch_name = p_adj_sum_batch_name
1190 AND source_type = 'A'
1191 AND (dist_dr_amount IS NOT NULL OR dist_cr_amount IS NOT NULL)
1192 AND status_code = 'I'
1193 AND gl_phase = 'Summarize_GL_Lines'
1194 AND business_group_id = p_business_group_id
1195 AND set_of_books_id = p_set_of_books_id
1196 AND run_id = nvl(g_run_id, run_id);
1197 pc_batch_rec pc_batch_cur%ROWTYPE;
1198
1199 CURSOR gl_batch_cursor(p_batch_name IN VARCHAR2) IS
1200 SELECT payroll_control_id,
1201 source_type,
1202 payroll_source_code,
1203 time_period_id,
1204 batch_name,
1205 -- Introduced for bug fix 2916848
1206 currency_code
1207 FROM psp_payroll_controls
1208 WHERE batch_name = p_batch_name
1209 AND source_type = 'A'
1210 AND (dist_dr_amount IS NOT NULL OR dist_cr_amount IS NOT NULL)
1211 AND status_code = 'I'
1212 AND gl_phase = 'Summarize_GL_Lines'
1213 AND run_id = g_run_id;
1214
1215
1216 CURSOR gl_interface_cursor(p_payroll_control_id IN NUMBER) IS
1217 SELECT psl.summary_line_id,
1218 psl.source_code,
1219 psl.effective_date,
1220 psl.accounting_date, --- added for 3108109
1221 psl.exchange_rate_type,
1222 psl.set_of_books_id,
1223 psl.gl_code_combination_id,
1224 psl.summary_amount,
1225 psl.dr_cr_flag,
1226 psl.attribute1,
1227 psl.attribute2,
1228 psl.attribute3,
1229 psl.attribute4,
1230 psl.attribute5,
1231 psl.attribute6,
1232 psl.attribute7,
1233 psl.attribute8,
1234 psl.attribute9,
1235 psl.attribute10,
1236 psl.attribute11,
1237 psl.attribute12,
1238 psl.attribute13,
1239 psl.attribute14,
1240 psl.attribute15,
1241 psl.attribute16,
1242 psl.attribute17,
1243 psl.attribute18,
1244 psl.attribute19,
1245 psl.attribute20,
1246 psl.attribute21,
1247 psl.attribute22,
1248 psl.attribute23,
1249 psl.attribute24,
1250 psl.attribute25,
1251 psl.attribute26,
1252 psl.attribute27,
1253 psl.attribute28,
1254 psl.attribute29,
1255 psl.attribute30,
1256 psl.person_id --Included this column as part of bug fix 1828519
1257 FROM psp_summary_lines psl
1258 WHERE psl.status_code = 'N'
1259 AND psl.gl_code_combination_id IS NOT NULL
1260 AND psl.payroll_control_id = p_payroll_control_id;
1261
1262 gl_batch_rec gl_batch_cursor%ROWTYPE;
1263 gl_interface_rec gl_interface_cursor%ROWTYPE;
1264 -- l_sob_id NUMBER(15) := FND_PROFILE.VALUE('PSP_SET_OF_BOOKS');
1265 l_sob_id NUMBER(15) := p_set_of_books_id;
1266 l_user_je_source_name VARCHAR2(25);
1267 l_user_je_category_name VARCHAR2(25);
1268 l_period_name VARCHAR2(35);
1269 l_period_end_date DATE;
1270 l_encumbrance_type_id NUMBER(15);
1271 l_entered_dr NUMBER;
1272 l_entered_cr NUMBER;
1273 l_group_id NUMBER;
1274 l_int_run_id NUMBER;
1275 l_reference1 VARCHAR2(100);
1276 l_reference4 VARCHAR2(100);
1277 l_return_status VARCHAR2(10);
1278 req_id NUMBER(15);
1279 call_status BOOLEAN;
1280 rphase VARCHAR2(30);
1281 rstatus VARCHAR2(30);
1282 dphase VARCHAR2(30);
1283 dstatus VARCHAR2(30);
1284 message VARCHAR2(240);
1285 p_errbuf VARCHAR2(32767);
1286 p_retcode VARCHAR2(32767);
1287 return_back EXCEPTION;
1288 l_rec_count NUMBER := 0;
1289 l_error VARCHAR2(100);
1290 l_product VARCHAR2(3);
1291 l_value VARCHAR2(200);
1292 l_table VARCHAR2(100);
1293 l_msg_id number(9);
1294 -- l_batch_cnt NUMBER(10); Commented the variable.. Bug 1977939
1295 l_person_name VARCHAR2(240); -- Included for bug fix 1828519
1296
1297 -- Included the following cursors here for referring local variables for bug fix 1765678
1298 CURSOR time_period_cur IS
1299 SELECT period_name, end_date
1300 FROM per_time_periods
1301 WHERE time_period_id = gl_batch_rec.time_period_id;
1302
1303 /*** Commented this as part of bug fix 2916848
1304 CURSOR currency_code_cur IS
1305 SELECT currency_code
1306 FROM gl_sets_of_books
1307 WHERE set_of_books_id = l_sob_id;
1308 End of bug fix 2916848 ***/
1309
1310 --Included the flollowing cursor for bug fix 1828519
1311 CURSOR employee_name_cur IS
1312 SELECT full_name
1313 FROM per_people_f ppf
1314 WHERE ppf.person_id = (SELECT pal.person_id
1315 FROM psp_adjustment_lines pal
1316 WHERE pal.payroll_control_id = gl_batch_rec.payroll_control_id
1317 AND rownum = 1); --Bug 2133056 removed distinct and replace batch_name with payroll_control_id
1318
1319 BEGIN
1320 fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) ||' Entering transfer_to_gl_interface ');
1321
1322
1323 -- get the source name
1324 get_gl_je_sources(l_user_je_source_name,
1325 l_return_status);
1326 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1327 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1328 END IF;
1329
1330 -- get the category name
1331 get_gl_je_categories(l_user_je_category_name,
1332 l_return_status);
1333 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1334 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1335 END IF;
1336
1337 -- get the currency_code
1338 -- Replaced the following and included new set of code for bug fix 1765678
1339
1340 OPEN pc_batch_cur;
1341 LOOP
1342 FETCH pc_batch_cur into pc_batch_rec;
1343 if pc_batch_cur%NOTFOUND then
1344 close pc_batch_cur;
1345 exit;
1346 end if;
1347 -- get the group_id. Moved the group id out of payroll control id loop. Bug 1977939
1348 SELECT gl_interface_control_s.nextval
1349 INTO l_group_id
1350 FROM DUAL;
1351
1352 l_rec_count := 0; -- MOVED this stmt, from inside the payroll control loop id Bug 1977939.
1353 OPEN gl_batch_cursor(pc_batch_rec.batch_name);
1354 LOOP
1355 FETCH gl_batch_cursor INTO gl_batch_rec;
1356 IF gl_batch_cursor%NOTFOUND THEN
1357 CLOSE gl_batch_cursor;
1358 EXIT;
1359 END IF;
1360
1361
1362 -- update psp_summary_lines with group_id
1363 UPDATE psp_summary_lines
1364 SET group_id = l_group_id
1365 WHERE status_code = 'N'
1366 AND gl_code_combination_id IS NOT NULL
1367 AND payroll_control_id = gl_batch_rec.payroll_control_id;
1368 fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) ||' Updating psp_summary_lines group_id set to '||l_group_id||' for GL lines with control_id: '||gl_batch_rec.payroll_control_id);
1369
1370
1371 -- get the period_name
1372 -- Replaced the earlier 'select stmt.' code with new 'cursor' code for bug fix 1765678
1373
1374 OPEN time_period_cur;
1375 FETCH time_period_cur INTO l_period_name, l_period_end_date;
1376 IF (time_period_cur%NOTFOUND) THEN
1377 CLOSE time_period_cur;
1378 -- Included the following code for bug fix 1828519
1379 OPEN employee_name_cur;
1380 FETCH employee_name_cur INTO l_person_name;
1381 CLOSE employee_name_cur;
1382 -- End of bug fix 1828519
1383 l_value := 'Time Period Id = '||to_char(gl_batch_rec.time_period_id);
1384 l_table := 'PER_TIME_PERIODS';
1385 fnd_message.set_name('PSP','PSP_TR_VALUE_NOT_FOUND');
1386 fnd_message.set_token('VALUE',l_value);
1387 fnd_message.set_token('TABLE',l_table);
1388 fnd_message.set_token('BATCH_NAME',pc_batch_rec.batch_name); -- Included for bug fix 1828519
1389 fnd_message.set_token('PERSON_NAME',l_person_name); -- Included for bug fix 1828519
1390 fnd_msg_pub.add;
1391 -- Commented the following message for bug fix 1828519
1392 -- fnd_message.set_name('PSP', 'PSP_ADJ_GL_FAILED');
1393 -- fnd_message.set_token('ERR_NAME', 'TIME PERIOD NOT FOUND');
1394 -- get_the_batch_details(pc_batch_rec.batch_name, l_return_status);
1395 -- fnd_msg_pub.add;
1396 cleanup_batch_details(gl_batch_rec.payroll_control_id,null);
1397 -- l_batch_cnt := l_batch_cnt - 1;
1398 close gl_batch_cursor;
1399 exit;
1400 END IF;
1401 CLOSE time_period_cur;
1402
1403
1404 l_reference1 := gl_batch_rec.source_type || ':' || gl_batch_rec.payroll_source_code ||
1405 ':' || l_period_name || ':' || gl_batch_rec.batch_name;
1406
1407 IF gl_batch_rec.source_type = 'A' THEN
1408 l_reference4 := 'LD ADJUSTMENTS DISTRIBUTION';
1409 END IF;
1410
1411 OPEN gl_interface_cursor(gl_batch_rec.payroll_control_id);
1412 LOOP
1413 FETCH gl_interface_cursor INTO gl_interface_rec;
1414 IF gl_interface_cursor%NOTFOUND THEN
1415 CLOSE gl_interface_cursor;
1416 EXIT;
1417 END IF;
1418 fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) ||' gl_interface_cursor count: '||gl_interface_cursor%rowcount);
1419
1420
1421 --l_batch_cnt := l_batch_cnt + 1; commented for Bug 1977939
1422
1423 l_rec_count := l_rec_count + 1;
1424 IF gl_interface_rec.dr_cr_flag = 'D' THEN
1425 l_entered_dr := gl_interface_rec.summary_amount;
1426 l_entered_cr := NULL;
1427 ELSIF gl_interface_rec.dr_cr_flag = 'C' THEN
1428 l_entered_dr := NULL;
1429 l_entered_cr := gl_interface_rec.summary_amount;
1430 END IF;
1431
1432 -- Corrected currency_code reference and introduced exchange_rate_type and conversion_date for bug fix 2916848
1433 fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) ||' Calling insert_into_gl_interface ');
1434
1435 insert_into_gl_interface(
1436 L_SOB_ID,GL_INTERFACE_REC.EFFECTIVE_DATE, gl_batch_rec.currency_code,
1437 L_USER_JE_CATEGORY_NAME,L_USER_JE_SOURCE_NAME,L_ENCUMBRANCE_TYPE_ID,
1438 GL_INTERFACE_REC.GL_CODE_COMBINATION_ID,L_ENTERED_DR,L_ENTERED_CR,
1439 L_GROUP_ID,L_REFERENCE1,L_REFERENCE1,L_REFERENCE4,
1440 GL_INTERFACE_REC.SUMMARY_LINE_ID,L_REFERENCE4,
1441 GL_INTERFACE_REC.ATTRIBUTE1,GL_INTERFACE_REC.ATTRIBUTE2,
1442 GL_INTERFACE_REC.ATTRIBUTE3,GL_INTERFACE_REC.ATTRIBUTE4,
1443 GL_INTERFACE_REC.ATTRIBUTE5,GL_INTERFACE_REC.ATTRIBUTE6,
1444 GL_INTERFACE_REC.ATTRIBUTE7,GL_INTERFACE_REC.ATTRIBUTE8,
1445 GL_INTERFACE_REC.ATTRIBUTE9,GL_INTERFACE_REC.ATTRIBUTE10,
1446 GL_INTERFACE_REC.ATTRIBUTE11,GL_INTERFACE_REC.ATTRIBUTE12,
1447 GL_INTERFACE_REC.ATTRIBUTE13,GL_INTERFACE_REC.ATTRIBUTE14,
1448 GL_INTERFACE_REC.ATTRIBUTE15,GL_INTERFACE_REC.ATTRIBUTE16,
1449 GL_INTERFACE_REC.ATTRIBUTE17,GL_INTERFACE_REC.ATTRIBUTE18,
1450 GL_INTERFACE_REC.ATTRIBUTE19,GL_INTERFACE_REC.ATTRIBUTE20,
1451 GL_INTERFACE_REC.ATTRIBUTE21,GL_INTERFACE_REC.ATTRIBUTE22,
1452 GL_INTERFACE_REC.ATTRIBUTE23,GL_INTERFACE_REC.ATTRIBUTE24,
1453 GL_INTERFACE_REC.ATTRIBUTE25,GL_INTERFACE_REC.ATTRIBUTE26,
1454 GL_INTERFACE_REC.ATTRIBUTE27,GL_INTERFACE_REC.ATTRIBUTE28,
1455 GL_INTERFACE_REC.ATTRIBUTE29,GL_INTERFACE_REC.ATTRIBUTE30,
1456 gl_interface_rec.exchange_rate_type,
1457 GL_INTERFACE_REC.accounting_date, --- added for 3108109
1458 L_RETURN_STATUS);
1459
1460 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1461 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1462 END IF;
1463
1464 END LOOP; -- Interface table loop.
1465
1466 END LOOP; -- Payroll control id loop.
1467 --Moved the following IF..END IF stmt outside the Payroll control id loop to SB LOOP, Bug 1977939.
1468 IF l_rec_count > 0 THEN
1469
1470 -- Gather the table statistics here ...
1471
1472 -- Commented for bug 9543455
1473 /* begin
1474 FND_STATS.Gather_Table_Stats (ownname => 'GL',
1475 tabname => 'GL_INTERFACE');
1476 -- percent => 10,
1477 -- tmode => 'TEMPORARY');
1478 -- Above two parameters commented out for bug fix 2476829
1479
1480 exception
1481 when others then
1482 null;
1483
1484 end;*/
1485
1486 -- insert into gl_interface_control
1487 SELECT GL_JOURNAL_IMPORT_S.NEXTVAL
1488 INTO l_int_run_id
1489 FROM DUAL;
1490
1491 fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) ||' Calling insert_into_gl_interface ');
1492
1493 insert into gl_interface_control(
1494 je_source_name,
1495 status,
1496 interface_run_id,
1497 group_id,
1498 set_of_books_id)
1499 VALUES (
1500 l_user_je_source_name,
1501 'S',
1502 l_int_run_id,
1503 l_group_id,
1504 l_sob_id
1505 );
1506 fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) ||' Submitting Journal Import');
1507
1508
1509 req_id := fnd_request.submit_request('SQLGL',
1510 'GLLEZL',
1511 '',
1512 '',
1513 FALSE,
1514 to_char(l_int_run_id),
1515 to_char(l_sob_id),
1516 'N',
1517 '',
1518 '',
1519 g_enable_enc_summ_gl, -- Introduced as part of bug 2259310
1520 'W'); -- changed 'N' to 'W' for bug fix 2908859
1521
1522 IF req_id = 0 THEN
1523 fnd_message.set_name('PSP', 'PSP_TR_GL_IMP_FAILED');
1524 fnd_msg_pub.add;
1525 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1526 ELSE
1527 update psp_payroll_controls
1528 set gl_phase = 'Submitted_Import_Request'
1529 --where payroll_control_id = gl_batch_rec.payroll_control_id; Replaced this line for Bug 2133056
1530 where source_type = 'A'
1531 and batch_name = pc_batch_rec.batch_name;
1532 fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) ||' Submitting Journal Import');
1533
1534
1535
1536 commit;
1537 call_status := fnd_concurrent.wait_for_request(req_id, 20, 0, rphase, rstatus,
1538 dphase, dstatus, message);
1539 if call_status = FALSE then
1540 fnd_message.set_name('PSP', 'PSP_TR_GL_IMP_FAILED');
1541 fnd_msg_pub.add;
1542 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1543 end if;
1544 END IF;
1545 END IF;
1546 END LOOP; -- Small batch loop
1547 --
1548 p_return_status := fnd_api.g_ret_sts_success;
1549 EXCEPTION
1550 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1551 g_error_api_path := 'TRANSFER_TO_GL_INTERFACE:'||g_error_api_path;
1552 fnd_msg_pub.add_exc_msg('PSP_SUM_ADJ','TRANSFER_TO_GL_INTERFACE');
1553 p_return_status := fnd_api.g_ret_sts_unexp_error;
1554
1555 WHEN RETURN_BACK THEN
1556 p_return_status := fnd_api.g_ret_sts_success;
1557
1558 WHEN OTHERS THEN
1559 g_error_api_path := 'TRANSFER_TO_GL_INTERFACE:'||g_error_api_path;
1560 fnd_msg_pub.add_exc_msg('PSP_SUM_ADJ','TRANSFER_TO_GL_INTERFACE');
1561 p_return_status := fnd_api.g_ret_sts_unexp_error;
1562 END;
1563
1564 ---------------------- GET_GL_JE_SOURCES --------------------------------------------------
1565 PROCEDURE get_gl_je_sources(P_USER_JE_SOURCE_NAME OUT NOCOPY VARCHAR2,
1566 P_RETURN_STATUS OUT NOCOPY VARCHAR2) IS
1567 -- Included the following cursor for bug fix 1765678
1568 CURSOR user_je_source_name IS
1569 SELECT user_je_source_name
1570 FROM gl_je_sources
1571 WHERE je_source_name = 'OLD';
1572 l_error VARCHAR2(100);
1573 l_product VARCHAR2(3);
1574 BEGIN
1575 -- Replaced the earlier 'select stmt.' code with new 'cursor' code for bug fix 1765678
1576
1577 OPEN user_je_source_name;
1578 FETCH user_je_source_name INTO p_user_je_source_name;
1579 IF (user_je_source_name%NOTFOUND) THEN
1580 CLOSE user_je_source_name;
1581 l_error := 'JE SOURCES = OLD';
1582 l_product := 'GL';
1583 fnd_message.set_name('PSP','PSP_TR_NOT_SET_UP');
1584 fnd_message.set_token('ERROR',l_error);
1585 fnd_message.set_token('PRODUCT',l_product);
1586 fnd_msg_pub.add;
1587 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1588 END IF;
1589 CLOSE user_je_source_name;
1590
1591 EXCEPTION
1592 WHEN NO_DATA_FOUND THEN
1593 -- Obsoleted the following code as it gets handled by the new code inserted above for bug fix 1765678
1594 -- l_error := 'JE SOURCES = OLD';
1595 -- l_product := 'GL';
1596 -- fnd_message.set_name('PSP','PSP_TR_NOT_SET_UP');
1597 -- fnd_message.set_token('ERROR',l_error);
1598 -- fnd_message.set_token('PRODUCT',l_product);
1599 -- fnd_msg_pub.add;
1600 g_error_api_path := 'GL_JE_SOURCES:'||g_error_api_path;
1601 fnd_msg_pub.add_exc_msg('PSP_SUM_ADJ','GL_JE_SOURCES');
1602 p_return_status := fnd_api.g_ret_sts_unexp_error;
1603 WHEN OTHERS THEN
1604 g_error_api_path := 'GL_JE_SOURCES:'||g_error_api_path;
1605 fnd_msg_pub.add_exc_msg('PSP_SUM_ADJ','GL_JE_SOURCES');
1606 p_return_status := fnd_api.g_ret_sts_unexp_error;
1607 END;
1608
1609 ---------------------- GET_GL_CATEGORIES --------------------------------------------------
1610 PROCEDURE get_gl_je_categories(P_USER_JE_CATEGORY_NAME OUT NOCOPY VARCHAR2,
1611 P_RETURN_STATUS OUT NOCOPY VARCHAR2) IS
1612 -- Included the following cursor for bug fix 1765678
1613 CURSOR gl_je_category_cur IS
1614 SELECT user_je_category_name
1615 FROM gl_je_categories
1616 WHERE je_category_name = 'OLD';
1617 l_error VARCHAR2(100);
1618 l_product VARCHAR2(3);
1619 BEGIN
1620 OPEN gl_je_category_cur;
1621 FETCH gl_je_category_cur INTO p_user_je_category_name;
1622 IF (gl_je_category_cur%NOTFOUND) THEN
1623 CLOSE gl_je_category_cur;
1624 l_error := 'JE CATEGORY = OLD';
1625 l_product := 'GL';
1626 fnd_message.set_name('PSP','PSP_TR_NOT_SET_UP');
1627 fnd_message.set_token('ERROR',l_error);
1628 fnd_message.set_token('PRODUCT',l_product);
1629 fnd_msg_pub.add;
1630 p_return_status := fnd_api.g_ret_sts_unexp_error;
1631 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1632 END IF;
1633 CLOSE gl_je_category_cur;
1634
1635 EXCEPTION
1636 WHEN NO_DATA_FOUND THEN
1637 -- Obsoleted the following code as the exception is handled in the cursor%NOTFOUND area for bug fix 1765678
1638 -- l_error := 'JE CATEGORY = OLD';
1639 -- l_product := 'GL';
1640 -- fnd_message.set_name('PSP','PSP_TR_NOT_SET_UP');
1641 -- fnd_message.set_token('ERROR',l_error);
1642 -- fnd_message.set_token('PRODUCT',l_product);
1643 -- fnd_msg_pub.add;
1644 p_return_status := fnd_api.g_ret_sts_unexp_error;
1645 WHEN OTHERS THEN
1646 g_error_api_path := 'GL_JE_CATEGORY_NAME:'||g_error_api_path;
1647 fnd_msg_pub.add_exc_msg('PSP_SUM_ADJ','GL_JE_CATEGORY_NAME');
1648 p_return_status := fnd_api.g_ret_sts_unexp_error;
1649 END;
1650
1651 -------------------- GL TIE BACK -----------------------------------------------------
1652 PROCEDURE gl_tie_back(p_adj_sum_batch_name IN VARCHAR2,
1653 p_business_group_id IN NUMBER,
1654 p_set_of_books_id IN NUMBER,
1655 p_return_status OUT NOCOPY VARCHAR2) IS
1656
1657 CURSOR pc_batch_cur IS
1658 SELECT distinct batch_name
1659 FROM psp_payroll_controls
1660 WHERE adj_sum_batch_name = p_adj_sum_batch_name
1661 AND (dist_dr_amount is not null and dist_cr_amount is not null)
1662 AND source_type = 'A'
1663 AND status_code = 'I'
1664 AND gl_phase = 'Submitted_Import_Request'
1665 AND business_group_id = p_business_group_id
1666 AND set_of_books_id = p_set_of_books_id
1667 AND run_id = nvl(g_run_id, run_id);
1668
1669 pc_batch_rec pc_batch_cur%ROWTYPE;
1670
1671 CURSOR gl_tie_back_success_cur(p_group_id IN NUMBER,
1672 p_payroll_control_id IN NUMBER ) IS ----added for Bug 2133056
1673 SELECT summary_line_id,
1674 dr_cr_flag,summary_amount
1675 FROM psp_summary_lines
1676 WHERE group_id = p_group_id
1677 AND payroll_control_id = p_payroll_control_id;
1678
1679 CURSOR gl_reversal_cur(p_summary_line_id IN NUMBER) IS
1680 SELECT reversal_entry_flag
1681 FROM psp_adjustment_lines
1682 WHERE summary_line_id = p_summary_line_id;
1683
1684 CURSOR gl_tie_back_cur(p_batch_name IN VARCHAR2) IS
1685 SELECT payroll_control_id,
1686 source_type
1687 FROM psp_payroll_controls
1688 WHERE batch_name = p_batch_name
1689 AND (dist_dr_amount is not null and dist_cr_amount is not null)
1690 AND source_type = 'A'
1691 AND status_code = 'I'
1692 AND business_group_id = p_business_group_id
1693 AND set_of_books_id = p_set_of_books_id
1694 AND gl_phase = 'Submitted_Import_Request'
1695 AND run_id = nvl(g_run_id, run_id);
1696
1697 gl_tie_back_rec gl_tie_back_cur%ROWTYPE;
1698
1699 l_orig_org_name hr_all_organization_units_tl.name%TYPE; -- Bug 2447912: Modified declaration
1700 l_orig_org_id number;
1701
1702 l_organization_name hr_all_organization_units_tl.name%TYPE; -- Bug 2447912: Modified declaration
1703 l_organization_id NUMBER(15);
1704 l_rowid ROWID;
1705 l_assignment_id NUMBER(9);
1706 l_distribution_date DATE;
1707 l_reversal_entry_flag VARCHAR2(1);
1708 l_lines_glccid NUMBER(15);
1709 --
1710 l_organization_account_id NUMBER(9);
1711 l_susp_glccid NUMBER(15);
1712 l_project_id NUMBER(15);
1713 l_award_id NUMBER(15);
1714 l_task_id NUMBER(15);
1715 --
1716 l_status VARCHAR2(50);
1717 l_reference6 VARCHAR2(100);
1718 --
1719 l_cnt_gl_interface NUMBER;
1720 l_summary_line_id NUMBER(10);
1721 l_gl_project_flag VARCHAR2(1);
1722 l_reversal_ac_failed VARCHAR2(1) := 'N';
1723 l_summary_amount NUMBER;
1724 l_dr_summary_amount NUMBER := 0;
1725 l_cr_summary_amount NUMBER := 0;
1726 l_dr_cr_flag VARCHAR2(1);
1727 l_effective_date DATE;
1728 x_lines_glccid NUMBER(15);
1729 l_return_status VARCHAR2(10);
1730 l_msg_id number(9);
1731 l_dist_line_id number(9);
1732 l_group_id number(15);
1733 --
1734 l_adjustment_batch_name varchar2(50);
1735 l_person_id number;
1736 l_person_name varchar2(80);
1737 -- l_assignment_id number;
1738 l_assignment_number number;
1739 l_element_type_id number;
1740 --- l_element_name varchar2(80);
1741 l_distribution_start_date date;
1742 l_distribution_end_date date;
1743 l_err_status varchar2(80);
1744
1745 -- Included the following cursors here for accessing local varibales for bug fix 1765678
1746 CURSOR summary_group_cur IS
1747 SELECT PSL.group_id
1748 FROM psp_summary_lines PSL,
1749 psp_payroll_controls PPC
1750 WHERE PPC.payroll_control_id = PSL.payroll_control_id
1751 -- WHERE payroll_control_id = gl_tie_back_rec.payroll_control_id
1752 AND PPC.batch_name = pc_batch_rec.batch_name
1753 AND PPC.source_type = 'A'
1754 AND PSL.group_id IS NOT NULL
1755 AND rownum =1; -- Removed max function on group_id and introduced rownum=1 Bug 2133056
1756
1757 CURSOR gl_interface_group_cur IS
1758 SELECT count(*)
1759 FROM gl_interface
1760 WHERE group_id = l_group_id
1761 AND set_of_books_id = p_set_of_books_id
1762 AND user_je_source_name = 'OLD';
1763
1764 -- Bug 2133056: Changes related to handle situation of GL import leaving some xface recs in 'NEW'
1765 CURSOR gl_interface_status_cur IS
1766 SELECT count(*)
1767 FROM gl_interface
1768 WHERE group_id = l_group_id
1769 AND user_je_source_name = 'OLD'
1770 AND status = 'NEW';
1771 l_status_new integer;
1772
1773 BEGIN
1774 fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) ||' Entering gl_tie_back');
1775
1776
1777 OPEN pc_batch_cur;
1778 LOOP
1779 FETCH pc_batch_cur into pc_batch_rec;
1780 if pc_batch_cur%NOTFOUND then
1781 close pc_batch_cur;
1782 exit;
1783 end if;
1784
1785 /* Moved the following cursor outside payroll control id LOOP for 2133056 */
1786 OPEN summary_group_cur;
1787 FETCH summary_group_cur INTO l_group_id;
1788 IF (summary_group_cur%NOTFOUND) THEN
1789 CLOSE summary_group_cur;
1790 g_error_api_path := 'GL_TIE_BACK:'||g_error_api_path;
1791 fnd_msg_pub.add_exc_msg('PSP_SUM_ADJ','GL_TIE_BACK');
1792 p_return_status := fnd_api.g_ret_sts_unexp_error;
1793 EXIT;
1794 END IF;
1795 CLOSE summary_group_cur;
1796
1797 /* Bug 2133056: to handle situation of GL import left lines untouched */
1798 OPEN gl_interface_status_cur;
1799 FETCH gl_interface_status_cur INTO l_status_new;
1800 IF (gl_interface_status_cur%NOTFOUND) THEN
1801 CLOSE gl_interface_status_cur;
1802 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1803 END IF;
1804 CLOSE gl_interface_status_cur;
1805
1806 if l_status_new > 0 then
1807 update psp_payroll_controls
1808 set gl_phase = 'Summarize_GL_Lines'
1809 where gl_phase = 'Submitted_Import_Request'
1810 and batch_name = pc_batch_rec.batch_name; --- 3184075
1811 ---and adj_sum_batch_name = p_adj_sum_batch_name;
1812 fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) ||' updating psp_payroll_controls gl_phase to
1813 Summarize_GL_Lines for batch_name: '||pc_batch_rec.batch_name||' count: '||sql%rowcount);
1814
1815
1816 delete from gl_interface
1817 where group_id = l_group_id
1818 and user_je_source_name = 'OLD';
1819 fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) ||' deleted from gl_interface count: '||sql%rowcount);
1820
1821
1822 delete from gl_interface_control
1823 where group_id = l_group_id
1824 and je_source_name = 'OLD';
1825 fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) ||' deleted from gl_interface count: '||sql%rowcount);
1826
1827
1828 commit;
1829
1830 fnd_message.set_name('PSP', 'PSP_TR_GL_IMP_FAILED');
1831 fnd_msg_pub.add;
1832 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1833
1834 end if;
1835
1836
1837 OPEN gl_tie_back_cur(pc_batch_rec.batch_name);
1838 LOOP
1839 FETCH gl_tie_back_cur into gl_tie_back_rec;
1840 IF gl_tie_back_cur%NOTFOUND then
1841 CLOSE gl_tie_back_cur;
1842 EXIT;
1843 END IF;
1844 UPDATE psp_payroll_controls
1845 SET gl_phase = 'GL_Tie_Back'
1846 WHERE payroll_control_id = gl_tie_back_rec.payroll_control_id;
1847 fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) ||' updated psp_payroll_controls set gl_phase to GL_Tie_Back count: '||sql%rowcount||' for control_id : '||gl_tie_back_rec.payroll_control_id);
1848
1849
1850 -- Replaced the earlier 'select stmt.' code with new 'cursor' code for bug fix 1765678
1851
1852
1853 OPEN gl_interface_group_cur;
1854 FETCH gl_interface_group_cur INTO l_cnt_gl_interface;
1855 IF (gl_interface_group_cur%NOTFOUND) THEN
1856 CLOSE gl_interface_group_cur;
1857 g_error_api_path := 'GL_TIE_BACK:'||g_error_api_path;
1858 fnd_msg_pub.add_exc_msg('PSP_SUM_ADJ','GL_TIE_BACK');
1859 p_return_status := fnd_api.g_ret_sts_unexp_error;
1860 EXIT;
1861 END IF;
1862 CLOSE gl_interface_group_cur;
1863
1864 IF l_cnt_gl_interface > 0 THEN
1865
1866
1867 fnd_message.set_name('PSP','PSP_ADJ_GL_FAILED');
1868 get_the_batch_details(pc_batch_rec.batch_name, l_return_status);
1869 fnd_msg_pub.add;
1870
1871 cleanup_batch_details(gl_tie_back_rec.payroll_control_id,l_group_id); -- added group id for 2133056
1872 -- Commented the following as cursor is not at all used
1873 -- Uncommented the following code as the code would fail if more than one GL batch fails along bug fix 1828519
1874 close gl_tie_back_cur;
1875 exit;
1876
1877 ELSIF l_cnt_gl_interface = 0 THEN
1878 OPEN gl_tie_back_success_cur(l_group_id,gl_tie_back_rec.payroll_control_id); --Added control id for 2133056
1879 l_dr_summary_amount := 0; --- Bug 2133056, initialized the amounts
1880 l_cr_summary_amount := 0;
1881 LOOP
1882 FETCH gl_tie_back_success_cur INTO l_summary_line_id,
1883 l_dr_cr_flag,l_summary_amount;
1884 IF gl_tie_back_success_cur%NOTFOUND THEN
1885 CLOSE gl_tie_back_success_cur;
1886 EXIT;
1887 END IF;
1888
1889 --dbms_output.put_line('Am in the success loop');
1890
1891 -- update records in psp_summary_lines as 'A'
1892 UPDATE psp_summary_lines
1893 SET status_code = 'A'
1894 WHERE summary_line_id = l_summary_line_id;
1895
1896 IF l_dr_cr_flag = 'D' THEN
1897 l_dr_summary_amount := l_dr_summary_amount + l_summary_amount;
1898 ELSIF l_dr_cr_flag = 'C' THEN
1899 l_cr_summary_amount := l_cr_summary_amount + l_summary_amount;
1900 END IF;
1901
1902 UPDATE psp_adjustment_lines
1903 SET status_code = 'A' WHERE summary_line_id = l_summary_line_id;
1904
1905 -- move the transferred records to psp_adjustment_lines_history
1906 INSERT INTO psp_adjustment_lines_history
1907 (adjustment_line_id,person_id,assignment_id,element_type_id,
1908 distribution_date,effective_date,distribution_amount,
1909 dr_cr_flag,payroll_control_id,source_type,source_code,time_period_id,
1910 batch_name,status_code,set_of_books_id,gl_code_combination_id,project_id,
1911 expenditure_organization_id,expenditure_type,task_id,award_id,
1912 suspense_org_account_id,suspense_reason_code,effort_report_id,version_num,
1913 summary_line_id, reversal_entry_flag, original_line_flag, user_defined_field, percent,
1914 orig_source_type,
1915 orig_line_id,attribute_category,attribute1,attribute2,attribute3,attribute4,
1916 attribute5,attribute6,attribute7,attribute8,attribute9,attribute10,attribute11,
1917 attribute12,attribute13,attribute14,attribute15,last_update_date,
1918 last_updated_by,last_update_login,created_by,creation_date, business_group_id,
1919 adj_set_number, line_number) --- added cols 2634557 DA Multiple element Enh
1920 SELECT adjustment_line_id,person_id,assignment_id,element_type_id,
1921 distribution_date,effective_date,distribution_amount,
1922 dr_cr_flag,payroll_control_id,source_type,source_code,time_period_id,
1923 batch_name,status_code,set_of_books_id,gl_code_combination_id,project_id,
1924 expenditure_organization_id,expenditure_type,task_id,award_id,
1925 suspense_org_account_id,suspense_reason_code,effort_report_id,version_num,
1926 summary_line_id, reversal_entry_flag, original_line_flag, user_defined_field, percent,
1927 orig_source_type,
1928 orig_line_id,attribute_category,attribute1,attribute2,attribute3,attribute4,
1929 attribute5,attribute6,attribute7,attribute8,attribute9,attribute10,attribute11,
1930 attribute12,attribute13,attribute14,attribute15,SYSDATE,FND_GLOBAL.USER_ID,
1931 FND_GLOBAL.LOGIN_ID,FND_GLOBAL.USER_ID,SYSDATE, business_group_id,
1932 adj_set_number, line_number --- added cols 2634557 DA Multiple element Enh
1933 FROM psp_adjustment_lines
1934 WHERE status_code = 'A'
1935 AND summary_line_id = l_summary_line_id;
1936
1937 DELETE FROM psp_adjustment_lines
1938 WHERE status_code = 'A'
1939 AND summary_line_id = l_summary_line_id;
1940
1941 fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) ||' Updated psp_summary_lines, psp_adjustment_lines inserted into
1942 psp_adjustment_lines_history then deleted from psp_adjustment_lines for summary_line_id : '||l_summary_line_id);
1943
1944 END LOOP;
1945
1946 UPDATE psp_payroll_controls
1947 SET gl_dr_amount = nvl(gl_dr_amount,0) + l_dr_summary_amount,
1948 gl_cr_amount = nvl(gl_cr_amount,0) + l_cr_summary_amount
1949 WHERE payroll_control_id = gl_tie_back_rec.payroll_control_id;
1950
1951 fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) ||' Updated psp_payroll_controls set gl_dr_amount and gl_cr_amount for cotrol_id : '||gl_tie_back_rec.payroll_control_id);
1952
1953
1954 END IF;
1955 END LOOP; -- end loop for gl_tie_back_cur
1956 commit;
1957 END LOOP; -- end loop for pc_batch_cur
1958
1959 --
1960 p_return_status := fnd_api.g_ret_sts_success;
1961 EXCEPTION
1962
1963 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1964 g_error_api_path := 'GL_TIE_BACK:'||g_error_api_path;
1965 -- Included the following for bug fix 1765678
1966 fnd_msg_pub.add_exc_msg('PSP_SUM_ADJ','GL_TIE_BACK');
1967 p_return_status := fnd_api.g_ret_sts_unexp_error;
1968
1969 WHEN OTHERS THEN
1970 g_error_api_path := 'GL_TIE_BACK:'||g_error_api_path;
1971 fnd_msg_pub.add_exc_msg('PSP_SUM_ADJ','GL_TIE_BACK');
1972 p_return_status := fnd_api.g_ret_sts_unexp_error;
1973 END;
1974
1975 ------------------ CREATE BALANCING TRANSACTIONS FOR GL ------------------------------
1976
1977 PROCEDURE gl_balance_transaction(P_SOURCE_TYPE IN VARCHAR2,
1978 P_PAYROLL_CONTROL_ID IN NUMBER,
1979 P_BUSINESS_GROUP_ID IN NUMBER,
1980 P_SET_OF_BOOKS_ID IN NUMBER,
1981 P_RETURN_STATUS OUT NOCOPY VARCHAR2) IS
1982 --- changed the cursor for 3108109
1983 CURSOR ad_reversal_entry_cur(P_PAYROLL_CONTROL_ID IN NUMBER) IS
1984 SELECT person_id,
1985 assignment_id,
1986 to_number(null) element_type_id,
1987 dr_cr_flag,
1988 effective_date,
1989 accounting_date,
1990 exchange_rate_type,
1991 source_type,
1992 source_code,
1993 time_period_id,
1994 summary_amount reversal_dist_amount,
1995 interface_batch_name,
1996 summary_line_id
1997 FROM psp_summary_lines
1998 WHERE payroll_control_id = p_payroll_control_id
1999 AND gl_code_combination_id IS NOT NULL
2000 AND status_code = 'N'
2001 AND business_group_id = p_business_group_id
2002 AND set_of_books_id = p_set_of_books_id;
2003 /*
2004 GROUP BY person_id,assignment_id,element_type_id,dr_cr_flag,effective_date,
2005 source_type,source_code,time_period_id,batch_name; */
2006
2007 l_payroll_id NUMBER(9); -- Added for bug 5592964
2008
2009 -- Included the following cursor for bug fix 1765678
2010 CURSOR reversing_gl_ccid_cur IS
2011 SELECT reversing_gl_ccid
2012 FROM psp_clearing_account
2013 WHERE set_of_books_id = p_set_of_books_id
2014 AND business_group_id = p_business_group_id
2015 AND payroll_id = l_payroll_id; -- Added for bug 5592964
2016
2017
2018 -- Redundant balancing lines created for GL Balanced Batch Bug 1977893
2019 -- Added following two cursors and two variables for credit amount and debit amount
2020 cursor control_rec_debit_amount_cur is
2021 select sum(nvl(distribution_amount,0))
2022 from psp_adjustment_lines
2023 where dr_cr_flag = 'D'
2024 and gl_code_combination_id is not null
2025 and payroll_control_id = p_payroll_control_id;
2026
2027 cursor control_rec_credit_amount_cur is
2028 select sum(nvl(distribution_amount,0))
2029 from psp_adjustment_lines
2030 where dr_cr_flag = 'C'
2031 and gl_code_combination_id is not null
2032 and payroll_control_id = p_payroll_control_id;
2033
2034 l_control_rec_credit number;
2035 l_control_rec_debit number;
2036 --- Bug 1977893 changes end.
2037
2038 --- added following cursor for 3108109
2039 cursor get_element_type_id(p_summary_line_id number) is
2040 select element_type_id
2041 from psp_adjustment_lines
2042 where summary_line_id = p_summary_line_id
2043 and rownum = 1;
2044
2045
2046 ad_reversal_entry_rec ad_reversal_entry_cur%ROWTYPE;
2047 ---l_reversal_dist_amount NUMBER; Bug 1976999
2048 l_clrg_account_glccid NUMBER(15);
2049 l_msg_id number(9);
2050 l_summary_line_id NUMBER; -- added for 3108109
2051 l_return_status varchar2(10); -- added for 3108109
2052
2053 BEGIN
2054 open control_rec_debit_amount_cur;
2055 fetch control_rec_debit_amount_cur into l_control_rec_debit;
2056 close control_rec_debit_amount_cur;
2057 open control_rec_credit_amount_cur;
2058 fetch control_rec_credit_amount_cur into l_control_rec_credit;
2059 close control_rec_credit_amount_cur;
2060 if (l_control_rec_debit is null and l_control_rec_credit is not null) or
2061 (l_control_rec_debit is not null and l_control_rec_credit is null) or
2062 (l_control_rec_credit <> l_control_rec_debit) then /* Bug 1977893 */
2063 IF p_source_type = 'A' THEN
2064
2065 SELECT payroll_id INTO l_payroll_id
2066 FROM psp_payroll_controls
2067 WHERE payroll_control_id = p_payroll_control_id;
2068
2069 BEGIN
2070 -- Replaced the earlier 'select stmt.' code with new 'cursor' code , inserted new code for bug fix 1765678
2071 OPEN reversing_gl_ccid_cur;
2072 FETCH reversing_gl_ccid_cur INTO l_clrg_account_glccid;
2073 IF (reversing_gl_ccid_cur%NOTFOUND) THEN
2074 CLOSE reversing_gl_ccid_cur;
2075 fnd_message.set_name('PSP','PSP_TR_CLRG_AC_NOT_SET_UP');
2076 fnd_msg_pub.add;
2077 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2078 END IF;
2079 CLOSE reversing_gl_ccid_cur;
2080 END;
2081
2082 DELETE FROM psp_adjustment_lines
2083 WHERE reversal_entry_flag = 'Y'
2084 AND status_code = 'N'
2085 AND payroll_control_id = p_payroll_control_id;
2086
2087 -- recalculate and update the reversal sub-line amounts
2088 OPEN ad_reversal_entry_cur(p_payroll_control_id);
2089 LOOP
2090 FETCH ad_reversal_entry_cur INTO ad_reversal_entry_rec;
2091 IF ad_reversal_entry_cur%NOTFOUND THEN
2092 CLOSE ad_reversal_entry_cur;
2093 EXIT;
2094 END IF;
2095 IF ad_reversal_entry_rec.reversal_dist_amount <> 0 THEN
2096 /* bug 1976999 */
2097 if ad_reversal_entry_rec.dr_cr_flag = 'C' then
2098 ad_reversal_entry_rec.dr_cr_flag := 'D';
2099 else
2100 ad_reversal_entry_rec.dr_cr_flag := 'C';
2101 end if;
2102 --l_reversal_dist_amount := 0 - ad_reversal_entry_rec.reversal_dist_amount; Bug 1976999
2103 --END IF;
2104
2105 open get_element_type_id(ad_reversal_entry_rec.summary_line_id);
2106 fetch get_element_type_id into ad_reversal_entry_rec.element_type_id;
2107 if get_element_type_id%notfound then
2108 close get_element_type_id;
2109 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2110 end if;
2111 close get_element_type_id;
2112
2113 insert_into_summary_lines(
2114 l_summary_line_id,
2115 ad_reversal_entry_rec.person_id,
2116 ad_reversal_entry_rec.assignment_id,
2117 ad_reversal_entry_rec.time_period_id,
2118 ad_reversal_entry_rec.effective_date,
2119 ad_reversal_entry_rec.accounting_date,
2120 ad_reversal_entry_rec.exchange_rate_type,
2121 'A',
2122 ad_reversal_entry_rec.source_code,
2123 p_set_of_books_id,
2124 l_clrg_account_glccid,
2125 NULL,
2126 NULL,
2127 NULL,
2128 NULL,
2129 NULL,
2130 ad_reversal_entry_rec.reversal_dist_amount,
2131 ad_reversal_entry_rec.dr_cr_flag,
2132 'N',
2133 ad_reversal_entry_rec.interface_batch_name,
2134 p_payroll_control_id,
2135 p_business_group_id,
2136 -- Introduced NULL for DFF parameters as clearing a/c doesnt require DFF values as part bug fix 2908859
2137 NULL,
2138 NULL,
2139 NULL,
2140 NULL,
2141 NULL,
2142 NULL,
2143 NULL,
2144 NULL,
2145 NULL,
2146 NULL,
2147 NULL,
2148 l_return_status);
2149
2150 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2151 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2152 END IF;
2153
2154 -- insert the reversal entry record into distribution lines
2155 insert into psp_adjustment_lines
2156 (adjustment_line_id,person_id,assignment_id,element_type_id,
2157 distribution_date,effective_date,distribution_amount,dr_cr_flag,
2158 payroll_control_id,source_type,source_code,time_period_id,batch_name,
2159 status_code,gl_code_combination_id,reversal_entry_flag,last_update_date,last_updated_by,
2160 last_update_login,created_by,creation_date, business_group_id, set_of_books_id,
2161 summary_line_id)
2162 values
2163 (PSP_ADJUSTMENT_LINES_S.NEXTVAL,ad_reversal_entry_rec.person_id,
2164 ad_reversal_entry_rec.assignment_id,ad_reversal_entry_rec.element_type_id,
2165 ad_reversal_entry_rec.effective_date,ad_reversal_entry_rec.effective_date,
2166 ad_reversal_entry_rec.reversal_dist_amount, ----l_reversal_dist_amount, Bug 1976999
2167 ad_reversal_entry_rec.dr_cr_flag,
2168 p_payroll_control_id,ad_reversal_entry_rec.source_type,
2169 ad_reversal_entry_rec.source_code,ad_reversal_entry_rec.time_period_id,
2170 ad_reversal_entry_rec.interface_batch_name,'N',l_clrg_account_glccid,'Y',
2171 SYSDATE,FND_GLOBAL.USER_ID,FND_GLOBAL.LOGIN_ID,FND_GLOBAL.USER_ID,SYSDATE,
2172 p_business_group_id, p_set_of_books_id, l_summary_line_id);
2173 END IF;
2174 END LOOP;
2175 END IF;
2176 end if; -- control_rec_debit <> control_rec_credit
2177 --
2178 p_return_status := fnd_api.g_ret_sts_success;
2179 EXCEPTION
2180 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2181 g_error_api_path := 'GL_BALANCE_TRANSACTION:'||g_error_api_path;
2182 -- Included the following line for bug fix 1765678
2183 fnd_msg_pub.add_exc_msg('PSP_SUM_ADJ','GL_BALANCE_TRANSACTION');
2184 p_return_status := fnd_api.g_ret_sts_unexp_error;
2185
2186 WHEN OTHERS THEN
2187 g_error_api_path := 'GL_BALANCE_TRANSACTION:'||g_error_api_path;
2188 fnd_msg_pub.add_exc_msg('PSP_SUM_ADJ','GL_BALANCE_TRANSACTION');
2189 p_return_status := fnd_api.g_ret_sts_unexp_error;
2190 END;
2191
2192 ------------------ INSERT INTO GL INTERFACE -----------------------------------------------
2193
2194 PROCEDURE insert_into_gl_interface(
2195 P_SET_OF_BOOKS_ID IN NUMBER,
2196 P_ACCOUNTING_DATE IN DATE,
2197 P_CURRENCY_CODE IN VARCHAR2,
2198 P_USER_JE_CATEGORY_NAME IN VARCHAR2,
2199 P_USER_JE_SOURCE_NAME IN VARCHAR2,
2200 P_ENCUMBRANCE_TYPE_ID IN NUMBER,
2201 P_CODE_COMBINATION_ID IN NUMBER,
2202 P_ENTERED_DR IN NUMBER,
2203 P_ENTERED_CR IN NUMBER,
2204 P_GROUP_ID IN NUMBER,
2205 P_REFERENCE1 IN VARCHAR2,
2206 P_REFERENCE2 IN VARCHAR2,
2207 P_REFERENCE4 IN VARCHAR2,
2208 P_REFERENCE6 IN VARCHAR2,
2209 P_REFERENCE10 IN VARCHAR2,
2210 P_ATTRIBUTE1 IN VARCHAR2,
2211 P_ATTRIBUTE2 IN VARCHAR2,
2212 P_ATTRIBUTE3 IN VARCHAR2,
2213 P_ATTRIBUTE4 IN VARCHAR2,
2214 P_ATTRIBUTE5 IN VARCHAR2,
2215 P_ATTRIBUTE6 IN VARCHAR2,
2216 P_ATTRIBUTE7 IN VARCHAR2,
2217 P_ATTRIBUTE8 IN VARCHAR2,
2218 P_ATTRIBUTE9 IN VARCHAR2,
2219 P_ATTRIBUTE10 IN VARCHAR2,
2220 P_ATTRIBUTE11 IN VARCHAR2,
2221 P_ATTRIBUTE12 IN VARCHAR2,
2222 P_ATTRIBUTE13 IN VARCHAR2,
2223 P_ATTRIBUTE14 IN VARCHAR2,
2224 P_ATTRIBUTE15 IN VARCHAR2,
2225 P_ATTRIBUTE16 IN VARCHAR2,
2226 P_ATTRIBUTE17 IN VARCHAR2,
2227 P_ATTRIBUTE18 IN VARCHAR2,
2228 P_ATTRIBUTE19 IN VARCHAR2,
2229 P_ATTRIBUTE20 IN VARCHAR2,
2230 P_ATTRIBUTE21 IN VARCHAR2,
2231 P_ATTRIBUTE22 IN VARCHAR2,
2232 P_ATTRIBUTE23 IN VARCHAR2,
2233 P_ATTRIBUTE24 IN VARCHAR2,
2234 P_ATTRIBUTE25 IN VARCHAR2,
2235 P_ATTRIBUTE26 IN VARCHAR2,
2236 P_ATTRIBUTE27 IN VARCHAR2,
2237 P_ATTRIBUTE28 IN VARCHAR2,
2238 P_ATTRIBUTE29 IN VARCHAR2,
2239 P_ATTRIBUTE30 IN VARCHAR2,
2240 P_CURRENCY_CONVERSION_TYPE IN VARCHAR2, -- Introduced for bug fix 2916848
2241 P_CURRENCY_CONVERSION_DATE IN DATE, -- Introduced for bug fix 2916848
2242 P_RETURN_STATUS OUT NOCOPY VARCHAR2) IS
2243 l_msg_id number(9);
2244 BEGIN
2245 INSERT INTO GL_INTERFACE(
2246 STATUS,
2247 SET_OF_BOOKS_ID,
2248 ACCOUNTING_DATE,
2249 CURRENCY_CODE,
2250 DATE_CREATED,
2251 CREATED_BY,
2252 ACTUAL_FLAG,
2253 USER_JE_CATEGORY_NAME,
2254 USER_JE_SOURCE_NAME,
2255 ENCUMBRANCE_TYPE_ID,
2256 CODE_COMBINATION_ID,
2257 ENTERED_DR,
2258 ENTERED_CR,
2259 GROUP_ID,
2260 REFERENCE1,
2261 REFERENCE2,
2262 REFERENCE4,
2263 REFERENCE6,
2264 REFERENCE10,
2265 ATTRIBUTE1,
2266 ATTRIBUTE2,
2267 ATTRIBUTE3,
2268 ATTRIBUTE4,
2269 ATTRIBUTE5,
2270 ATTRIBUTE6,
2271 ATTRIBUTE7,
2272 ATTRIBUTE8,
2273 ATTRIBUTE9,
2274 ATTRIBUTE10,
2275 ATTRIBUTE11,
2276 ATTRIBUTE12,
2277 ATTRIBUTE13,
2278 ATTRIBUTE14,
2279 ATTRIBUTE15,
2280 ATTRIBUTE16,
2281 ATTRIBUTE17,
2282 ATTRIBUTE18,
2283 ATTRIBUTE19,
2284 ATTRIBUTE20,
2285 REFERENCE21,
2286 REFERENCE22,
2287 REFERENCE23,
2288 REFERENCE24,
2289 REFERENCE25,
2290 REFERENCE26,
2291 REFERENCE27,
2292 REFERENCE28,
2293 REFERENCE29,
2294 REFERENCE30,
2295 -- Introduced teh following columns for bug fix 2916848
2296 USER_CURRENCY_CONVERSION_TYPE,
2297 CURRENCY_CONVERSION_DATE)
2298 VALUES(
2299 'NEW',
2300 P_SET_OF_BOOKS_ID,
2301 P_ACCOUNTING_DATE,
2302 P_CURRENCY_CODE,
2303 SYSDATE,
2304 FND_GLOBAL.USER_ID,
2305 'A',
2306 P_USER_JE_CATEGORY_NAME,
2307 P_USER_JE_SOURCE_NAME,
2308 P_ENCUMBRANCE_TYPE_ID,
2309 P_CODE_COMBINATION_ID,
2310 P_ENTERED_DR,
2311 P_ENTERED_CR,
2312 P_GROUP_ID,
2313 P_REFERENCE1,
2314 P_REFERENCE2,
2315 P_REFERENCE4,
2316 P_REFERENCE6,
2317 P_REFERENCE10,
2318 P_ATTRIBUTE1,
2319 P_ATTRIBUTE2,
2320 P_ATTRIBUTE3,
2321 P_ATTRIBUTE4,
2322 P_ATTRIBUTE5,
2323 P_ATTRIBUTE6,
2324 P_ATTRIBUTE7,
2325 P_ATTRIBUTE8,
2326 P_ATTRIBUTE9,
2327 P_ATTRIBUTE10,
2328 P_ATTRIBUTE11,
2329 P_ATTRIBUTE12,
2330 P_ATTRIBUTE13,
2331 P_ATTRIBUTE14,
2332 P_ATTRIBUTE15,
2333 P_ATTRIBUTE16,
2334 P_ATTRIBUTE17,
2335 P_ATTRIBUTE18,
2336 P_ATTRIBUTE19,
2337 P_ATTRIBUTE20,
2338 P_ATTRIBUTE21,
2339 P_ATTRIBUTE22,
2340 P_ATTRIBUTE23,
2341 P_ATTRIBUTE24,
2342 P_ATTRIBUTE25,
2343 P_ATTRIBUTE26,
2344 P_ATTRIBUTE27,
2345 P_ATTRIBUTE28,
2346 P_ATTRIBUTE29,
2347 P_ATTRIBUTE30,
2348 -- Introduced the following columns for bug fix 2916848
2349 P_CURRENCY_CONVERSION_TYPE,
2350 DECODE(p_currency_conversion_type, NULL, NULL, P_CURRENCY_CONVERSION_DATE));
2351 --
2352 p_return_status := fnd_api.g_ret_sts_success;
2353 EXCEPTION
2354 WHEN OTHERS THEN
2355 g_error_api_path := 'INSERT_INTO_GL_INTERFACE:'||g_error_api_path;
2356 fnd_msg_pub.add_exc_msg('PSP_SUM_ADJ','INSERT_INTO_GL_INTERFACE');
2357 p_return_status := fnd_api.g_ret_sts_unexp_error;
2358 END;
2359
2360 -------------------- CREATE GMS SUM LINES -----------------------------------------------
2361 PROCEDURE create_gms_sum_lines(p_adj_sum_batch_name IN VARCHAR2,
2362 p_business_group_id IN NUMBER,
2363 p_set_of_books_id IN NUMBER,
2364 p_return_status OUT NOCOPY VARCHAR2) IS
2365
2366 CURSOR pc_batch_cur IS
2367 SELECT distinct batch_name
2368 FROM psp_payroll_controls
2369 WHERE adj_sum_batch_name = p_adj_sum_batch_name
2370 AND source_type = 'A'
2371 AND (dist_dr_amount IS NOT NULL OR dist_cr_amount IS NOT NULL)
2372 AND status_code = 'I'
2373 AND run_id = nvl(g_run_id, run_id)
2374 AND business_group_id = p_business_group_id
2375 AND set_of_books_id = p_set_of_books_id
2376 AND gms_phase is null;
2377
2378 pc_batch_rec pc_batch_cur%ROWTYPE;
2379
2380 CURSOR payroll_control_cur(p_batch_name IN VARCHAR2) IS
2381 SELECT payroll_control_id,
2382 source_type,
2383 payroll_source_code,
2384 time_period_id,
2385 batch_name
2386 FROM psp_payroll_controls
2387 WHERE batch_name = p_batch_name
2388 AND source_type = 'A'
2389 AND (dist_dr_amount IS NOT NULL OR dist_cr_amount IS NOT NULL)
2390 AND status_code = 'I'
2391 AND run_id = nvl(g_run_id, run_id);
2392
2393 CURSOR gms_sum_lines_cursor(P_PAYROLL_CONTROL_ID IN NUMBER) IS
2394 SELECT pal.person_id,
2395 pal.assignment_id,
2396 pal.project_id project_id,
2397 pal.expenditure_organization_id,
2398 pal.expenditure_type,
2399 pal.task_id,
2400 pal.award_id,
2401 pal.dr_cr_flag,
2402 pal.effective_date,
2403 psl.accounting_date, -- new column 3108109, removed nvl for 4734810
2404 psl.exchange_rate_type,
2405 pal.distribution_amount,
2406 pal.adjustment_line_id distribution_line_id,
2407 'A' tab_flag,
2408 DECODE(g_dff_grouping_option, 'Y', pal.attribute_category, NULL) attribute_category, -- Introduced DFF columns for bug fix 2908859
2409 DECODE(g_dff_grouping_option, 'Y', pal.attribute1, NULL) attribute1,
2410 DECODE(g_dff_grouping_option, 'Y', pal.attribute2, NULL) attribute2,
2411 DECODE(g_dff_grouping_option, 'Y', pal.attribute3, NULL) attribute3,
2412 DECODE(g_dff_grouping_option, 'Y', pal.attribute4, NULL) attribute4,
2413 DECODE(g_dff_grouping_option, 'Y', pal.attribute5, NULL) attribute5,
2414 DECODE(g_dff_grouping_option, 'Y', pal.attribute6, NULL) attribute6,
2415 DECODE(g_dff_grouping_option, 'Y', pal.attribute7, NULL) attribute7,
2416 DECODE(g_dff_grouping_option, 'Y', pal.attribute8, NULL) attribute8,
2417 DECODE(g_dff_grouping_option, 'Y', pal.attribute9, NULL) attribute9,
2418 DECODE(g_dff_grouping_option, 'Y', pal.attribute10, NULL) attribute10
2419 FROM psp_adjustment_lines pal,
2420 psp_distribution_lines_history pdh,
2421 psp_summary_lines psl
2422 WHERE pal.status_code = 'N'
2423 AND pal.gl_code_combination_id IS NULL
2424 AND pal.payroll_control_id = p_payroll_control_id
2425 AND pdh.distribution_line_id = pal.orig_line_id
2426 AND pal.orig_source_type = 'D'
2427 AND psl.summary_line_id = pdh.summary_line_id
2428 UNION
2429 SELECT pal.person_id,
2430 pal.assignment_id,
2431 pal.project_id project_id,
2432 pal.expenditure_organization_id,
2433 pal.expenditure_type,
2434 pal.task_id,
2435 pal.award_id,
2436 pal.dr_cr_flag,
2437 pal.effective_date,
2438 psl.accounting_date, -- new column 3108109
2439 psl.exchange_rate_type,
2440 pal.distribution_amount,
2441 pal.adjustment_line_id distribution_line_id,
2442 'A' tab_flag,
2443 DECODE(g_dff_grouping_option, 'Y', pal.attribute_category, NULL) attribute_category, -- Introduced DFF columns for bug fix 2908859
2444 DECODE(g_dff_grouping_option, 'Y', pal.attribute1, NULL) attribute1,
2445 DECODE(g_dff_grouping_option, 'Y', pal.attribute2, NULL) attribute2,
2446 DECODE(g_dff_grouping_option, 'Y', pal.attribute3, NULL) attribute3,
2447 DECODE(g_dff_grouping_option, 'Y', pal.attribute4, NULL) attribute4,
2448 DECODE(g_dff_grouping_option, 'Y', pal.attribute5, NULL) attribute5,
2449 DECODE(g_dff_grouping_option, 'Y', pal.attribute6, NULL) attribute6,
2450 DECODE(g_dff_grouping_option, 'Y', pal.attribute7, NULL) attribute7,
2451 DECODE(g_dff_grouping_option, 'Y', pal.attribute8, NULL) attribute8,
2452 DECODE(g_dff_grouping_option, 'Y', pal.attribute9, NULL) attribute9,
2453 DECODE(g_dff_grouping_option, 'Y', pal.attribute10, NULL) attribute10
2454 FROM psp_adjustment_lines pal,
2455 psp_adjustment_lines_history pal2,
2456 psp_summary_lines psl
2457 WHERE pal.status_code = 'N'
2458 AND pal.gl_code_combination_id IS NULL
2459 AND pal.payroll_control_id = p_payroll_control_id
2460 AND pal2.adjustment_line_id = pal.orig_line_id
2461 AND pal.orig_source_type = 'A'
2462 AND psl.summary_line_id = pal2.summary_line_id
2463 union
2464 SELECT pal.person_id,
2465 pal.assignment_id,
2466 pal.project_id project_id,
2467 pal.expenditure_organization_id,
2468 pal.expenditure_type,
2469 pal.task_id,
2470 pal.award_id,
2471 pal.dr_cr_flag,
2472 pal.effective_date,
2473 psl.accounting_date, -- new column 3108109
2474 psl.exchange_rate_type,
2475 pal.distribution_amount,
2476 pal.adjustment_line_id distribution_line_id,
2477 'A' tab_flag,
2478 DECODE(g_dff_grouping_option, 'Y', pal.attribute_category, NULL) attribute_category, -- Introduced DFF columns for bug fix 2908859
2479 DECODE(g_dff_grouping_option, 'Y', pal.attribute1, NULL) attribute1,
2480 DECODE(g_dff_grouping_option, 'Y', pal.attribute2, NULL) attribute2,
2481 DECODE(g_dff_grouping_option, 'Y', pal.attribute3, NULL) attribute3,
2482 DECODE(g_dff_grouping_option, 'Y', pal.attribute4, NULL) attribute4,
2483 DECODE(g_dff_grouping_option, 'Y', pal.attribute5, NULL) attribute5,
2484 DECODE(g_dff_grouping_option, 'Y', pal.attribute6, NULL) attribute6,
2485 DECODE(g_dff_grouping_option, 'Y', pal.attribute7, NULL) attribute7,
2486 DECODE(g_dff_grouping_option, 'Y', pal.attribute8, NULL) attribute8,
2487 DECODE(g_dff_grouping_option, 'Y', pal.attribute9, NULL) attribute9,
2488 DECODE(g_dff_grouping_option, 'Y', pal.attribute10, NULL) attribute10
2489 FROM psp_adjustment_lines pal,
2490 psp_pre_gen_dist_lines_history pgh,
2491 psp_summary_lines psl
2492 WHERE pal.status_code = 'N'
2493 AND pal.gl_code_combination_id IS NULL
2494 AND pal.payroll_control_id = p_payroll_control_id
2495 AND pgh.pre_gen_dist_line_id = pal.orig_line_id
2496 AND pal.orig_source_type = 'P'
2497 AND psl.summary_line_id = pgh.summary_line_id
2498 ORDER BY 1,2,3,4,5,6,7,8,10,11,15,16,17,18,19,20,21,22,23,24,25,9;
2499 --- added 2 new cols for 3108109
2500 --- changed the order by clause for 6007017
2501
2502 gms_sum_lines_rec gms_sum_lines_cursor%ROWTYPE;
2503 payroll_control_rec payroll_control_cur%ROWTYPE;
2504
2505 l_person_id NUMBER(9);
2506 l_assignment_id NUMBER(9);
2507 l_project_id NUMBER(15);
2508 l_expenditure_organization_id NUMBER(15);
2509 l_expenditure_type VARCHAR2(30);
2510 l_task_id NUMBER(15);
2511 l_award_id NUMBER(15);
2512 l_dr_cr_flag VARCHAR2(1);
2513 l_effective_date DATE;
2514 l_distribution_amount NUMBER;
2515 l_rec_count NUMBER := 0;
2516 l_summary_amount NUMBER := 0;
2517 l_summary_line_id NUMBER(10);
2518
2519 TYPE dist_id IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
2520 dist_line_id dist_id;
2521 l_dist_line_id NUMBER;
2522 i BINARY_INTEGER := 0;
2523 j NUMBER;
2524 l_return_status VARCHAR2(10);
2525 l_msg_id number(9);
2526 l_accounting_date DATE; --- added for 3108109
2527 l_exchange_rate_type VARCHAR2(30);
2528 l_attribute_category VARCHAR2(30); -- Introduced DFF variables for bug fix 2908859
2529 l_attribute1 VARCHAR2(150);
2530 l_attribute2 VARCHAR2(150);
2531 l_attribute3 VARCHAR2(150);
2532 l_attribute4 VARCHAR2(150);
2533 l_attribute5 VARCHAR2(150);
2534 l_attribute6 VARCHAR2(150);
2535 l_attribute7 VARCHAR2(150);
2536 l_attribute8 VARCHAR2(150);
2537 l_attribute9 VARCHAR2(150);
2538 l_attribute10 VARCHAR2(150);
2539
2540 -- R12 MOAC Uptake
2541 l_org_id number(15);
2542 BEGIN
2543 fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) ||' Entering create_gms_sum_lines');
2544
2545
2546 OPEN pc_batch_cur;
2547 LOOP
2548 FETCH pc_batch_cur into pc_batch_rec;
2549 IF pc_batch_cur%NOTFOUND THEN
2550 CLOSE pc_batch_cur;
2551 EXIT;
2552 END IF;
2553
2554 OPEN payroll_control_cur(pc_batch_rec.batch_name);
2555 LOOP
2556 FETCH payroll_control_cur INTO payroll_control_rec;
2557 IF payroll_control_cur%NOTFOUND THEN
2558 CLOSE payroll_control_cur;
2559 EXIT;
2560 END IF;
2561
2562 OPEN gms_sum_lines_cursor(payroll_control_rec.payroll_control_id);
2563 l_rec_count := 0;
2564 l_summary_amount := 0;
2565 i := 0;
2566 LOOP
2567 FETCH gms_sum_lines_cursor INTO gms_sum_lines_rec;
2568 l_rec_count := l_rec_count + 1;
2569 IF gms_sum_lines_cursor%NOTFOUND THEN
2570 if (l_rec_count > 1 ) then
2571 g_gms_avail := 'Y';
2572 UPDATE psp_payroll_controls
2573 SET gms_phase = 'Summarize_GMS_Lines'
2574 WHERE payroll_control_id = payroll_control_rec.payroll_control_id;
2575 fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) ||' Updating psp_payroll_controls gms_phase to Summarize_GMS_Lines for control_id: '||payroll_control_rec.payroll_control_id);
2576
2577 end if;
2578 CLOSE gms_sum_lines_cursor;
2579 EXIT;
2580 END IF;
2581 --
2582
2583 IF l_rec_count = 1 THEN
2584 l_person_id := gms_sum_lines_rec.person_id;
2585 l_assignment_id := gms_sum_lines_rec.assignment_id;
2586 l_project_id := gms_sum_lines_rec.project_id;
2587 l_expenditure_organization_id := gms_sum_lines_rec.expenditure_organization_id;
2588 l_expenditure_type := gms_sum_lines_rec.expenditure_type;
2589 l_task_id := gms_sum_lines_rec.task_id;
2590 l_award_id := gms_sum_lines_rec.award_id;
2591 l_dr_cr_flag := gms_sum_lines_rec.dr_cr_flag;
2592 l_effective_date := gms_sum_lines_rec.effective_date;
2593 l_accounting_date := gms_sum_lines_rec.accounting_date; --3108109
2594 l_exchange_rate_type := gms_sum_lines_rec.exchange_rate_type;
2595 l_attribute_category := gms_sum_lines_rec.attribute_category; -- Introduced DFF variables for bug fix 2908859
2596 l_attribute1 := gms_sum_lines_rec.attribute1;
2597 l_attribute2 := gms_sum_lines_rec.attribute2;
2598 l_attribute3 := gms_sum_lines_rec.attribute3;
2599 l_attribute4 := gms_sum_lines_rec.attribute4;
2600 l_attribute5 := gms_sum_lines_rec.attribute5;
2601 l_attribute6 := gms_sum_lines_rec.attribute6;
2602 l_attribute7 := gms_sum_lines_rec.attribute7;
2603 l_attribute8 := gms_sum_lines_rec.attribute8;
2604 l_attribute9 := gms_sum_lines_rec.attribute9;
2605 l_attribute10 := gms_sum_lines_rec.attribute10;
2606 END IF;
2607
2608 IF l_person_id <> gms_sum_lines_rec.person_id OR
2609 l_assignment_id <> gms_sum_lines_rec.assignment_id OR
2610 l_project_id <> gms_sum_lines_rec.project_id OR
2611 l_expenditure_organization_id <> gms_sum_lines_rec.expenditure_organization_id OR
2612 l_expenditure_type <> gms_sum_lines_rec.expenditure_type OR
2613 l_task_id <> gms_sum_lines_rec.task_id OR
2614 l_award_id <> gms_sum_lines_rec.award_id OR
2615 l_dr_cr_flag <> gms_sum_lines_rec.dr_cr_flag OR
2616 (NVL(l_attribute_category, 'NULL') <> NVL(gms_sum_lines_rec.attribute_category, 'NULL')) OR -- Introduced DFF check for bug fix 2908859
2617 (NVL(l_attribute1, 'NULL') <> NVL(gms_sum_lines_rec.attribute1, 'NULL')) OR
2618 (NVL(l_attribute2, 'NULL') <> NVL(gms_sum_lines_rec.attribute2, 'NULL')) OR
2619 (NVL(l_attribute3, 'NULL') <> NVL(gms_sum_lines_rec.attribute3, 'NULL')) OR
2620 (NVL(l_attribute4, 'NULL') <> NVL(gms_sum_lines_rec.attribute4, 'NULL')) OR
2621 (NVL(l_attribute5, 'NULL') <> NVL(gms_sum_lines_rec.attribute5, 'NULL')) OR
2622 (NVL(l_attribute6, 'NULL') <> NVL(gms_sum_lines_rec.attribute6, 'NULL')) OR
2623 (NVL(l_attribute7, 'NULL') <> NVL(gms_sum_lines_rec.attribute7, 'NULL')) OR
2624 (NVL(l_attribute8, 'NULL') <> NVL(gms_sum_lines_rec.attribute8, 'NULL')) OR
2625 (NVL(l_attribute9, 'NULL') <> NVL(gms_sum_lines_rec.attribute9, 'NULL')) OR
2626 (NVL(l_attribute10, 'NULL') <> NVL(gms_sum_lines_rec.attribute10, 'NULL')) OR
2627 nvl(l_accounting_date, fnd_date.canonical_to_date('1800/01/31')) <>
2628 nvl( gms_sum_lines_rec.accounting_date, fnd_date.canonical_to_date('1800/01/31')) OR
2629 nvl(l_exchange_rate_type,'-999') <> nvl(gms_sum_lines_rec.exchange_rate_type,'-999') THEN --3108109
2630
2631 IF l_dr_cr_flag = 'C' THEN
2632 l_summary_amount := 0 - l_summary_amount;
2633 END IF;
2634
2635 -- R12 MOAC Uptake
2636 l_org_id := psp_general.Get_transaction_org_id (l_project_id,l_expenditure_organization_id);
2637
2638 -- insert into summary lines
2639 insert_into_summary_lines(
2640 l_summary_line_id,
2641 l_person_id,
2642 l_assignment_id,
2643 payroll_control_rec.time_period_id,
2644 l_effective_date,
2645 l_accounting_date, --- 3108109
2646 l_exchange_rate_type,
2647 payroll_control_rec.source_type,
2648 payroll_control_rec.payroll_source_code,
2649 p_set_of_books_id,
2650 NULL,
2651 l_project_id,
2652 l_expenditure_organization_id,
2653 l_expenditure_type,
2654 l_task_id,
2655 l_award_id,
2656 l_summary_amount,
2657 l_dr_cr_flag,
2658 'N',
2659 payroll_control_rec.batch_name,
2660 payroll_control_rec.payroll_control_id,
2661 p_business_group_id,
2662 l_attribute_category, -- Introduced DFF parameters for bug fix 2908859
2663 l_attribute1,
2664 l_attribute2,
2665 l_attribute3,
2666 l_attribute4,
2667 l_attribute5,
2668 l_attribute6,
2669 l_attribute7,
2670 l_attribute8,
2671 l_attribute9,
2672 l_attribute10,
2673 l_return_status,
2674 l_org_id -- R12 MOAC Uptake
2675 );
2676
2677 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2678 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2679 END IF;
2680 fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) ||' After insert_into_summary_lines 3 l_summary_line_id: '||l_summary_line_id);
2681
2682
2683 FOR j IN 1 .. dist_line_id.COUNT LOOP
2684 l_dist_line_id := dist_line_id(j);
2685 IF gms_sum_lines_rec.tab_flag = 'A' THEN
2686 UPDATE psp_adjustment_lines
2687 SET summary_line_id = l_summary_line_id WHERE adjustment_line_id = l_dist_line_id;
2688 END IF;
2689 END LOOP;
2690 fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) ||' Updated psp_adjustment_lines 3');
2691
2692
2693 -- initialise the summary amount and dist_line_id
2694 l_summary_amount := 0;
2695 dist_line_id.delete;
2696 i := 0;
2697 END IF;
2698
2699 l_person_id := gms_sum_lines_rec.person_id;
2700 l_assignment_id := gms_sum_lines_rec.assignment_id;
2701 l_project_id := gms_sum_lines_rec.project_id;
2702 l_expenditure_organization_id := gms_sum_lines_rec.expenditure_organization_id;
2703 l_expenditure_type := gms_sum_lines_rec.expenditure_type;
2704 l_task_id := gms_sum_lines_rec.task_id;
2705 l_award_id := gms_sum_lines_rec.award_id;
2706 l_dr_cr_flag := gms_sum_lines_rec.dr_cr_flag;
2707 l_effective_date := gms_sum_lines_rec.effective_date;
2708 l_accounting_date := gms_sum_lines_rec.accounting_date; --3108109
2709 l_exchange_rate_type := gms_sum_lines_rec.exchange_rate_type;
2710 l_attribute_category := gms_sum_lines_rec.attribute_category; -- Introduced DFF variables for bug fix 2908859
2711 l_attribute1 := gms_sum_lines_rec.attribute1;
2712 l_attribute2 := gms_sum_lines_rec.attribute2;
2713 l_attribute3 := gms_sum_lines_rec.attribute3;
2714 l_attribute4 := gms_sum_lines_rec.attribute4;
2715 l_attribute5 := gms_sum_lines_rec.attribute5;
2716 l_attribute6 := gms_sum_lines_rec.attribute6;
2717 l_attribute7 := gms_sum_lines_rec.attribute7;
2718 l_attribute8 := gms_sum_lines_rec.attribute8;
2719 l_attribute9 := gms_sum_lines_rec.attribute9;
2720 l_attribute10 := gms_sum_lines_rec.attribute10;
2721 l_summary_amount := l_summary_amount + gms_sum_lines_rec.distribution_amount;
2722 i := i + 1;
2723 dist_line_id(i) := gms_sum_lines_rec.distribution_line_id;
2724
2725
2726 END LOOP;
2727
2728 IF l_dr_cr_flag = 'C' Then
2729 l_summary_amount := 0 - l_summary_amount;
2730 END IF;
2731
2732 IF l_rec_count > 1 THEN
2733 -- R12 MOAC Uptake
2734 l_org_id := psp_general.Get_transaction_org_id (l_project_id,l_expenditure_organization_id);
2735
2736 -- insert into summary lines
2737 insert_into_summary_lines(
2738 l_summary_line_id,
2739 l_person_id,
2740 l_assignment_id,
2741 payroll_control_rec.time_period_id,
2742 l_effective_date,
2743 l_accounting_date, --- added for 3108109
2744 l_exchange_rate_type,
2745 payroll_control_rec.source_type,
2746 payroll_control_rec.payroll_source_code,
2747 p_set_of_books_id,
2748 NULL,
2749 l_project_id,
2750 l_expenditure_organization_id,
2751 l_expenditure_type,
2752 l_task_id,
2753 l_award_id,
2754 l_summary_amount,
2755 l_dr_cr_flag,
2756 'N',
2757 payroll_control_rec.batch_name,
2758 payroll_control_rec.payroll_control_id,
2759 p_business_group_id,
2760 l_attribute_category, -- Introduced DFF parameters for bug fix 2908859
2761 l_attribute1,
2762 l_attribute2,
2763 l_attribute3,
2764 l_attribute4,
2765 l_attribute5,
2766 l_attribute6,
2767 l_attribute7,
2768 l_attribute8,
2769 l_attribute9,
2770 l_attribute10,
2771 l_return_status,
2772 l_org_id -- R12 MOAC Uptake
2773 );
2774
2775 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2776 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2777 END IF;
2778 fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) ||' After insert_into_summary_lines 4 l_summary_line_id: '||l_summary_line_id);
2779
2780
2781 FOR j IN 1 .. dist_line_id.COUNT LOOP
2782 l_dist_line_id := dist_line_id(j);
2783
2784 IF gms_sum_lines_rec.tab_flag = 'A' THEN
2785 UPDATE psp_adjustment_lines
2786 SET summary_line_id = l_summary_line_id,
2787 status_code = 'N'
2788 WHERE adjustment_line_id = l_dist_line_id;
2789 END IF;
2790 END LOOP;
2791 fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) ||' Updated psp_adjustment_lines 4 set status_code to N');
2792
2793 dist_line_id.delete;
2794 END IF;
2795
2796 END LOOP; --- End loop for payroll_control_cur
2797 END LOOP; --- End loop for pc_batch_cur
2798 --
2799 p_return_status := fnd_api.g_ret_sts_success;
2800
2801 EXCEPTION
2802
2803 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2804 g_error_api_path := 'CREATE_GMS_SUM_LINES:'||g_error_api_path;
2805 -- Included the following code for bug fix 1765678
2806 fnd_msg_pub.add_exc_msg('PSP_SUM_ADJ','CREATE_GMS_SUM_LINES');
2807 p_return_status := fnd_api.g_ret_sts_unexp_error;
2808 WHEN OTHERS THEN
2809 g_error_api_path := 'CREATE_GMS_SUM_LINES:'||g_error_api_path;
2810 fnd_msg_pub.add_exc_msg('PSP_SUM_ADJ','CREATE_GMS_SUM_LINES');
2811 p_return_status := fnd_api.g_ret_sts_unexp_error;
2812
2813 END;
2814
2815 ----------------------------- GMS INTERFACE ---------------------------------------------
2816 PROCEDURE transfer_to_gms_interface(p_adj_sum_batch_name IN VARCHAR2,
2817 p_business_group_id IN NUMBER,
2818 p_set_of_books_id IN NUMBER,
2819 p_return_status OUT NOCOPY VARCHAR2) IS
2820
2821 CURSOR pc_batch_cur IS
2822 SELECT distinct batch_name
2823 FROM psp_payroll_controls
2824 WHERE adj_sum_batch_name = p_adj_sum_batch_name
2825 AND source_type = 'A'
2826 AND (dist_dr_amount IS NOT NULL OR dist_cr_amount IS NOT NULL)
2827 AND gms_phase = 'Summarize_GMS_Lines'
2828 AND status_code = 'I'
2829 AND business_group_id = p_business_group_id
2830 AND set_of_books_id = p_set_of_books_id
2831 AND run_id = nvl(g_run_id, run_id);
2832
2833 pc_batch_rec pc_batch_cur%ROWTYPE;
2834
2835 CURSOR gms_batch_cursor(p_batch_name IN VARCHAR2) IS
2836 SELECT payroll_control_id,
2837 source_type,
2838 payroll_source_code,
2839 time_period_id,
2840 batch_name,
2841 -- Introduced the following for bug fix 2916848
2842 currency_code
2843 FROM psp_payroll_controls
2844 WHERE batch_name = p_batch_name
2845 AND source_type = 'A'
2846 AND (dist_dr_amount IS NOT NULL OR dist_cr_amount IS NOT NULL)
2847 AND gms_phase = 'Summarize_GMS_Lines'
2848 AND status_code = 'I'
2849 AND run_id = g_run_id;
2850
2851
2852 CURSOR gms_interface_cursor(P_PAYROLL_CONTROL_ID IN NUMBER) IS
2853 SELECT psl.summary_line_id,
2854 psl.source_code,
2855 psl.person_id,
2856 psl.assignment_id,
2857 NVL(psl.gms_posting_effective_date,psl.effective_date) effective_date, /* Bug: 1994421 Column modified for Enhancement Employee Assignment with Zero Work Days */
2858 psl.accounting_date, --- 3108109
2859 psl.exchange_rate_type,
2860 psl.project_id,
2861 psl.expenditure_organization_id,
2862 psl.expenditure_type,
2863 psl.task_id,
2864 psl.award_id,
2865 psl.summary_amount,
2866 psl.dr_cr_flag,
2867 psl.attribute1, -- Introduced attributes 1, 4 * 5 for bug fix 29098859
2868 psl.attribute2,
2869 psl.attribute3,
2870 psl.attribute4,
2871 psl.attribute5,
2872 psl.attribute6,
2873 psl.attribute7,
2874 psl.attribute8,
2875 psl.attribute9,
2876 psl.attribute10,
2877 org_id -- R12 MOAC uptake
2878 FROM psp_summary_lines psl
2879 WHERE psl.status_code = 'N'
2880 AND psl.gl_code_combination_id IS NULL
2881 AND psl.payroll_control_id = p_payroll_control_id;
2882
2883 -- Included the following cursors for bug fix 1765678
2884 CURSOR transaction_source_cur IS
2885 SELECT transaction_source
2886 FROM pa_transaction_sources
2887 WHERE transaction_source = 'OLD';
2888
2889 CURSOR site_transaction_source_cur IS
2890 SELECT transaction_source
2891 FROM pa_transaction_sources
2892 WHERE transaction_source = 'GOLD';
2893
2894 gms_batch_rec gms_batch_cursor%ROWTYPE;
2895 gms_interface_rec gms_interface_cursor%ROWTYPE;
2896 l_transaction_source VARCHAR2(30);
2897 l_expenditure_comment VARCHAR2(240);
2898 l_employee_number VARCHAR2(30);
2899 l_org_name hr_all_organization_units_tl.name%TYPE; -- Bug 2447912: Modified declaration
2900 l_segment1 VARCHAR2(25);
2901 l_task_number VARCHAR2(300); -- Bug 16391366 (27/03/2013)
2902 l_gms_batch_name VARCHAR2(10);
2903
2904 -- Bug 6902514 -- change
2905 l_gms_stat_batch_name VARCHAR2(10); -- Bug 6902514
2906 l_stat_count NUMBER; -- Bug 6902514
2907 l_not_stat_count NUMBER; -- Bug 6902514
2908
2909
2910 l_expenditure_ending_date DATE;
2911 l_period_name VARCHAR2(35);
2912 l_period_end_date DATE;
2913 l_return_status VARCHAR2(50); --- increased the size from 10 for WVU bug 2671594
2914 req_id NUMBER(15);
2915 call_status BOOLEAN;
2916 rphase VARCHAR2(30);
2917 rstatus VARCHAR2(30);
2918 dphase VARCHAR2(30);
2919 dstatus VARCHAR2(30);
2920 message VARCHAR2(240);
2921 p_errbuf VARCHAR2(32767);
2922 p_retcode VARCHAR2(32767);
2923 return_back EXCEPTION;
2924 l_rec_count NUMBER := 0;
2925 l_error VARCHAR2(100);
2926 l_product VARCHAR2(3);
2927 l_value VARCHAR2(200);
2928 l_table VARCHAR2(100);
2929 l_msg_id number(9);
2930 l_batch_cnt NUMBER(9) := 0;
2931 l_org_id NUMBER(15);
2932 l_gms_transaction_source VARCHAR2(30);
2933 l_txn_source VARCHAR2(30);
2934 gms_rec gms_transaction_interface_all%ROWTYPE;
2935 l_txn_interface_id number(15);
2936 l_person_name VARCHAR2(240); -- Included for bug fix 1828519
2937 l_gms_install BOOLEAN DEFAULT gms_install.enabled; -- Introduced for bug fix 2908859
2938
2939 -- Included the following cursor here for acessing local variables for bug fix 1765678
2940 CURSOR time_period_cur IS
2941 SELECT period_name, end_date
2942 FROM per_time_periods
2943 WHERE time_period_id = gms_batch_rec.time_period_id;
2944
2945 CURSOR employee_cur IS
2946 SELECT employee_number
2947 FROM per_people_f
2948 WHERE person_id = gms_interface_rec.person_id
2949 AND gms_interface_rec.effective_date BETWEEN effective_start_date AND effective_end_date;
2950
2951 CURSOR task_number_cur IS
2952 SELECT task_number
2953 FROM pa_tasks_expend_v -- Bug : 16391366 (20/03/2013)
2954 WHERE task_id = gms_interface_rec.task_id;
2955
2956 CURSOR emp_org_name_cur IS
2957 SELECT name -- Bug 2447912: Removed SUBSTR function
2958 FROM hr_all_organization_units hou
2959 WHERE organization_id = gms_interface_rec.expenditure_organization_id;
2960
2961 CURSOR project_number_cur IS
2962 SELECT segment1, org_id
2963 FROM pa_projects_all
2964 WHERE project_id = gms_interface_rec.project_Id;
2965
2966 -- Included the following cursor for bug fix 1828519
2967 CURSOR employee_name_cur IS
2968 SELECT full_name
2969 FROM per_people_f ppf
2970 WHERE ppf.person_id = (SELECT pal.person_id
2971 FROM psp_adjustment_lines pal
2972 WHERE pal.payroll_control_id = gms_batch_rec.payroll_control_id
2973 AND rownum = 1); -- Bug 2133056 Replaced batch_name with payroll_control_id and removed DISTINCT
2974 -- WHERE ppf.person_id = gms_interface_cur.person_id; Commented this cond. as part of 1828519
2975
2976 -- Introduced the following for bug fix 4507892
2977 TYPE t_number_15_type IS TABLE OF NUMBER(15) INDEX BY BINARY_INTEGER;
2978 TYPE payroll_control_rec IS RECORD (payroll_control_id t_number_15_type);
2979 r_payroll_controls payroll_control_rec;
2980
2981 CURSOR payroll_control_id_cur IS
2982 SELECT DISTINCT payroll_control_id
2983 FROM psp_summary_lines
2984 WHERE gms_batch_name = l_gms_batch_name;
2985 -- End of changes for bug fix 4507892
2986
2987 -- R12 MOAC Uptake
2988
2989 TYPE org_id_type IS TABLE OF NUMBER(15) INDEX BY BINARY_INTEGER;
2990 org_id_tab org_id_type;
2991
2992 TYPE gms_batch_name_type IS TABLE OF varchar2(10) INDEX BY BINARY_INTEGER;
2993 gms_batch_name_tab gms_batch_name_type;
2994
2995 TYPE req_id_type IS TABLE OF NUMBER(15) INDEX BY BINARY_INTEGER;
2996 req_id_tab req_id_type;
2997
2998 TYPE call_status_type IS TABLE OF BOOLEAN INDEX BY BINARY_INTEGER;
2999 call_status_tab call_status_type;
3000
3001 CURSOR operating_unit_csr IS
3002 SELECT distinct org_id
3003 FROM psp_payroll_controls ppc,
3004 psp_summary_lines psl
3005 WHERE ppc.payroll_control_id = psl.payroll_control_id
3006 AND ppc.adj_sum_batch_name = p_adj_sum_batch_name
3007 AND ppc.source_type = 'A'
3008 AND (ppc.dist_dr_amount IS NOT NULL OR ppc.dist_cr_amount IS NOT NULL)
3009 AND ppc.gms_phase = 'Summarize_GMS_Lines'
3010 AND ppc.status_code = 'I'
3011 AND ppc.business_group_id = p_business_group_id
3012 AND ppc.set_of_books_id = p_set_of_books_id
3013 AND run_id = nvl(g_run_id, run_id)
3014 AND psl.status_code = 'N'
3015 AND psl.gl_code_combination_id IS NULL;
3016
3017 BEGIN
3018 fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) ||' Entering transfer to gms_interface');
3019
3020 -- get the source name
3021 -- Replaced the earlier 'select stmt.' code with new 'cursor' code for bug fix 1765678
3022 OPEN transaction_source_cur;
3023 FETCH transaction_source_cur INTO l_transaction_source;
3024 IF (transaction_source_cur%NOTFOUND) THEN
3025 CLOSE transaction_source_cur;
3026 l_error := 'TRANSACTION SOURCE = OLD';
3027 l_product := 'GMS';
3028 fnd_message.set_name('PSP','PSP_TR_NOT_SET_UP');
3029 fnd_message.set_token('ERROR',l_error);
3030 fnd_message.set_token('PRODUCT',l_product);
3031 fnd_msg_pub.add;
3032 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3033 END IF;
3034 CLOSE transaction_source_cur;
3035
3036 -- get the gms source name
3037 if (l_gms_install) then -- replaced site-enabled call with l_gms_install as part of bug fix 2908859
3038 BEGIN
3039 -- Replaced the earlier 'select stmt.' code with new 'cursor' code for bug fix 1765678
3040
3041 OPEN site_transaction_source_cur;
3042 FETCH site_transaction_source_cur INTO l_gms_transaction_source;
3043 IF (site_transaction_source_cur%NOTFOUND) THEN
3044 CLOSE site_transaction_source_cur;
3045 l_error := 'TRANSACTION SOURCE = OLD';
3046 l_product := 'GMS';
3047 fnd_message.set_name('PSP','PSP_TR_NOT_SET_UP');
3048 fnd_message.set_token('ERROR',l_error);
3049 fnd_message.set_token('PRODUCT',l_product);
3050 fnd_msg_pub.add;
3051 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3052 END IF;
3053 CLOSE site_transaction_source_cur;
3054 END;
3055 end if;
3056
3057 -- R12 MOAC Uptake
3058 OPEN operating_unit_csr;
3059 FETCH operating_unit_csr BULK COLLECT INTO org_id_tab;
3060 CLOSE operating_unit_csr;
3061
3062 FOR i in 1..org_id_tab.count
3063 LOOP
3064 SELECT to_char(psp_gms_batch_name_s.nextval)
3065 INTO gms_batch_name_tab(i)
3066 FROM dual;
3067 END LOOP;
3068 /*
3069 -- get the gms_batch_name for the summary batch
3070 SELECT to_char(psp_gms_batch_name_s.nextval)
3071 INTO l_gms_batch_name
3072 FROM DUAL;
3073 */
3074 OPEN pc_batch_cur;
3075 LOOP
3076 FETCH pc_batch_cur into pc_batch_rec;
3077 IF pc_batch_cur%NOTFOUND THEN
3078 fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) ||' pc_batch_cur%NOTFOUND');
3079
3080 CLOSE pc_batch_cur;
3081 EXIT;
3082 END IF;
3083
3084 OPEN gms_batch_cursor(pc_batch_rec.batch_name);
3085 LOOP
3086 FETCH gms_batch_cursor INTO gms_batch_rec;
3087 IF gms_batch_cursor%NOTFOUND THEN
3088 fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) ||' gms_batch_cursor%NOTFOUND');
3089
3090 CLOSE gms_batch_cursor;
3091 EXIT;
3092 END IF;
3093
3094 l_batch_cnt := l_batch_cnt + 1;
3095 fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) ||' l_batch_cnt 1 : '||l_batch_cnt);
3096
3097
3098 -- R12 MOAC Uptake. Moved this code to loop
3099 FOR I in 1..org_id_tab.count
3100 LOOP
3101 UPDATE psp_summary_lines
3102 SET gms_batch_name = gms_batch_name_tab(i) -- l_gms_batch_name
3103 WHERE payroll_control_id = gms_batch_rec.payroll_control_id
3104 AND status_code = 'N'
3105 AND gl_code_combination_id IS NULL
3106 AND org_id = org_id_tab(i); -- R12 MOAC uptake
3107 END LOOP;
3108
3109 FOR i in 1..org_id_tab.count
3110 LOOP
3111 -- update psp_summary_lines with gms batch name
3112 UPDATE psp_summary_lines
3113 SET gms_batch_name = gms_batch_name_tab(i) -- l_gms_batch_name
3114 WHERE payroll_control_id = gms_batch_rec.payroll_control_id
3115 AND status_code = 'N'
3116 AND gl_code_combination_id IS NULL
3117 AND org_id = org_id_tab(i); -- R12 MOAC uptake
3118 END LOOP;
3119
3120 -- get the period_name
3121 -- Replaced the earlier 'select stmt.' code with new 'cursor' code for bug fix 1765678
3122
3123 OPEN time_period_cur;
3124 FETCH time_period_cur INTO l_period_name, l_period_end_date;
3125 IF (time_period_cur%NOTFOUND) THEN
3126 CLOSE time_period_cur;
3127 -- Included the following code for bug fix 1828519
3128 OPEN employee_name_cur;
3129 FETCH employee_name_cur INTO l_person_name;
3130 CLOSE employee_name_cur;
3131 -- End of bug fix 1828519
3132 l_value := 'Time Period Id = '||to_char(gms_batch_rec.time_period_id);
3133 l_table := 'PER_TIME_PERIODS';
3134 fnd_message.set_name('PSP','PSP_TR_VALUE_NOT_FOUND');
3135 fnd_message.set_token('VALUE',l_value);
3136 fnd_message.set_token('TABLE',l_table);
3137 fnd_message.set_token('BATCH_NAME',pc_batch_rec.batch_name); -- Included for bug fix 1828519
3138 fnd_message.set_token('PERSON_NAME',l_person_name); -- Included for bug fix 1828519
3139 fnd_msg_pub.add;
3140 -- Commented the following code for bug fix 1828519
3141 -- get_the_batch_details(pc_batch_rec.batch_name, l_return_status);
3142 -- fnd_msg_pub.add;
3143 cleanup_batch_details(gms_batch_rec.payroll_control_id,null);
3144 close gms_batch_cursor;
3145 l_batch_cnt := l_batch_cnt - 1;
3146 fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) ||' l_batch_cnt 2 : '||l_batch_cnt);
3147
3148 exit;
3149 END IF;
3150 CLOSE time_period_cur;
3151
3152 l_expenditure_comment := gms_batch_rec.source_type || ':' || gms_batch_rec.payroll_source_code
3153 || ':' || l_period_name || ':' || gms_batch_rec.batch_name;
3154
3155 OPEN gms_interface_cursor(gms_batch_rec.payroll_control_id);
3156 l_rec_count := 0;
3157 LOOP
3158 FETCH gms_interface_cursor INTO gms_interface_rec;
3159 IF gms_interface_cursor%NOTFOUND THEN
3160 -- if (l_rec_count > 0) then
3161 -- UPDATE psp_payroll_controls
3162 -- SET gms_phase = 'Transfer_GMS_Lines'
3163 -- WHERE payroll_control_id = gms_batch_rec.payroll_control_id;
3164 -- end if;
3165 CLOSE gms_interface_cursor;
3166 EXIT;
3167 END IF;
3168 fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) ||' gms_interface_cursor count: '||gms_interface_cursor%rowcount);
3169
3170
3171 -- get the employee number
3172 -- Replaced the earlier 'select stmt.' code with new 'cursor' code for bug fix 1765678
3173
3174 OPEN employee_cur;
3175 FETCH employee_cur INTO l_employee_number;
3176 IF (employee_cur%NOTFOUND)THEN
3177 CLOSE employee_cur;
3178 l_value := 'Person Id = '||to_char(gms_interface_rec.person_id);
3179 l_table := 'PER_PEOPLE_F';
3180 -- Included the following code for bug fix 1828519
3181 l_person_name := 'PERSON ID NOT FOUND';
3182 -- End of bug fix 1828519
3183 fnd_message.set_name('PSP','PSP_TR_VALUE_NOT_FOUND');
3184 fnd_message.set_token('VALUE',l_value);
3185 fnd_message.set_token('TABLE',l_table);
3186 fnd_message.set_token('BATCH_NAME',pc_batch_rec.batch_name); --Included for bug fix 1828519
3187 fnd_message.set_token('PERSON_NAME',l_person_name); --Included for bug fix 1828519
3188 fnd_msg_pub.add;
3189 l_batch_cnt := l_batch_cnt - 1;
3190 fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) ||' l_batch_cnt 3 : '||l_batch_cnt);
3191
3192 -- Commented the following message as part of bug fix 1828519
3193 -- fnd_message.set_name('PSP', 'PSP_ADJ_GMS_FAILED');
3194 -- fnd_message.set_token('ERR_NAME', 'PERSON ID NOT FOUND');
3195 -- get_the_batch_details(pc_batch_rec.batch_name, l_return_status);
3196 -- fnd_msg_pub.add;
3197 cleanup_batch_details(gms_batch_rec.payroll_control_id,null);
3198 close gms_interface_cursor;
3199 exit;
3200 END IF;
3201 CLOSE employee_cur;
3202
3203 -- get the employee's organization name
3204 -- Replaced the earlier 'select stmt.' code with new 'cursor' code for bug fix 1765678
3205
3206 OPEN emp_org_name_cur;
3207 FETCH emp_org_name_cur INTO l_org_name;
3208 IF (emp_org_name_cur%NOTFOUND) THEN
3209 CLOSE emp_org_name_cur;
3210 -- Included the following code for bug fix 1828519
3211 OPEN employee_name_cur;
3212 FETCH employee_name_cur INTO l_person_name;
3213 CLOSE employee_name_cur;
3214 -- End of bug fix 1828519
3215 l_value := 'Organization Id = '||to_char(gms_interface_rec.expenditure_organization_id);
3216 l_table := 'HR_ORGANIZATION_UNITS';
3217 fnd_message.set_name('PSP','PSP_TR_VALUE_NOT_FOUND');
3218 fnd_message.set_token('VALUE',l_value);
3219 fnd_message.set_token('TABLE',l_table);
3220 fnd_message.set_token('BATCH_NAME',pc_batch_rec.batch_name); -- Included for bug fix 1828519
3221 fnd_message.set_token('PERSON_NAME',l_person_name); -- Included for bug fix 1828519
3222 fnd_msg_pub.add;
3223 l_batch_cnt := l_batch_cnt - 1;
3224 fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) ||' l_batch_cnt 4 : '||l_batch_cnt);
3225
3226 -- Commented the following message as part of bug fix 1828519
3227 -- fnd_message.set_name('PSP', 'PSP_ADJ_GMS_FAILED');
3228 -- fnd_message.set_token('ERR_NAME', 'ORGANIZATION NOT FOUND');
3229 -- get_the_batch_details(pc_batch_rec.batch_name, l_return_status);
3230 -- fnd_msg_pub.add;
3231 cleanup_batch_details(gms_batch_rec.payroll_control_id,null);
3232 close gms_interface_cursor;
3233 exit;
3234 END IF;
3235 CLOSE emp_org_name_cur;
3236
3237 -- get the project number
3238 -- Replaced the earlier 'select stmt.' code with new 'cursor' code for bug fix 1765678
3239
3240 OPEN project_number_cur;
3241 FETCH project_number_cur INTO l_segment1, l_org_id;
3242 IF (project_number_cur%NOTFOUND) THEN
3243 CLOSE project_number_cur;
3244 -- Included the following code for bug fix 1828519
3245 OPEN employee_name_cur;
3246 FETCH employee_name_cur INTO l_person_name;
3247 CLOSE employee_name_cur;
3248 -- End of bug fix 1828519
3249 l_value := 'Project Id = '||to_char(gms_interface_rec.project_Id);
3250 l_table := 'PA_PROJECTS_ALL';
3251 fnd_message.set_name('PSP','PSP_TR_VALUE_NOT_FOUND');
3252 fnd_message.set_token('VALUE',l_value);
3253 fnd_message.set_token('TABLE',l_table);
3254 fnd_message.set_token('BATCH_NAME',pc_batch_rec.batch_name); -- Included for bug fix 1828519
3255 fnd_message.set_token('PERSON_NAME',l_person_name); -- Included for bug fix 1828519
3256 fnd_msg_pub.add;
3257 l_batch_cnt := l_batch_cnt - 1;
3258 fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) ||' l_batch_cnt 5 : '||l_batch_cnt);
3259
3260 -- Commented the following message as part of bug fix 1828519
3261 -- fnd_message.set_name('PSP', 'PSP_ADJ_GMS_FAILED');
3262 -- fnd_message.set_token('ERR_NAME', 'ORGANIZATION NOT FOUND');
3263 -- get_the_batch_details(pc_batch_rec.batch_name, l_return_status);
3264 -- fnd_msg_pub.add;
3265 cleanup_batch_details(gms_batch_rec.payroll_control_id,null);
3266 close gms_interface_cursor;
3267 exit;
3268 END IF;
3269 CLOSE project_number_cur;
3270
3271 -- get the task number
3272 -- Replaced the earlier 'select stmt.' code with new 'cursor' code for bug fix 1765678
3273
3274 OPEN task_number_cur;
3275 FETCH task_number_cur INTO l_task_number;
3276 IF (task_number_cur%NOTFOUND) THEN
3277 CLOSE task_number_cur;
3278 -- Included the following code for bug fix 1828519
3279 OPEN employee_name_cur;
3280 FETCH employee_name_cur INTO l_person_name;
3281 CLOSE employee_name_cur;
3282 -- End of bug fix 1828519
3283 l_value := 'Task Id = '||to_char(gms_interface_rec.task_id);
3284 l_table := 'PA_TASKS';
3285 fnd_message.set_name('PSP','PSP_TR_VALUE_NOT_FOUND');
3286 fnd_message.set_token('VALUE',l_value);
3287 fnd_message.set_token('TABLE',l_table);
3288 fnd_message.set_token('BATCH_NAME',pc_batch_rec.batch_name); -- Included for bug fix 1828519
3289 fnd_message.set_token('PERSON_NAME',l_person_name); -- Included for bug fix 1828519
3290 fnd_msg_pub.add;
3291 l_batch_cnt := l_batch_cnt - 1;
3292 fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) ||' l_batch_cnt 6 : '||l_batch_cnt);
3293
3294 -- Commented the following message as part of bug fix 1828519
3295 -- fnd_message.set_name('PSP', 'PSP_ADJ_GMS_FAILED');
3296 -- fnd_message.set_token('ERR_NAME', 'ORGANIZATION NOT FOUND');
3297 -- get_the_batch_details(pc_batch_rec.batch_name, l_return_status);
3298 -- fnd_msg_pub.add;
3299 cleanup_batch_details(gms_batch_rec.payroll_control_id,null);
3300 close gms_interface_cursor;
3301 exit;
3302 END IF;
3303 CLOSE task_number_cur;
3304
3305 l_rec_count := l_rec_count + 1;
3306
3307 select pa_txn_interface_s.nextval
3308 into l_txn_interface_id
3309 from dual;
3310
3311 -- set the context to single to call pa_utils function
3312 mo_global.set_policy_context('S', gms_interface_rec.org_id );
3313
3314 l_expenditure_ending_date := pa_utils.GetWeekending(gms_interface_rec.effective_date);
3315
3316 -- set the context again to multiple
3317 mo_global.set_policy_context('M', null);
3318
3319 --- moved this code from below 2671594
3320 -- if gms_interface_rec.award_id is not null then
3321 if (l_gms_install) then -- Changed award_id check to gms_install check as part of bug fix 2908859
3322 l_txn_source := l_gms_transaction_source;
3323 else
3324 l_txn_source := l_transaction_source;
3325 end if;
3326
3327
3328 FOR i in 1..org_id_tab.count
3329 LOOP
3330 IF org_id_tab(i) = gms_interface_rec.org_id THEN
3331 l_gms_batch_name := gms_batch_name_tab(i);
3332 END IF;
3333 END LOOP;
3334 g_gms_batch_name := l_gms_batch_name; /* 1662816 */
3335
3336
3337
3338
3339 -- Corrected currency code value, introduced acct_rate_type and acct_rate_date values for bug fix 2916848
3340 IF (gms_batch_rec.currency_code <> 'STAT') THEN -- code changes for bug 5167562
3341 fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) ||' calling insert_into_pa_interface for USD ');
3342
3343 insert_into_pa_interface(l_txn_interface_id,
3344 L_TXN_SOURCE,
3345 L_GMS_BATCH_NAME,
3346 L_EXPENDITURE_ENDING_DATE,
3347 L_EMPLOYEE_NUMBER,
3348 L_ORG_NAME,
3349 GMS_INTERFACE_REC.EFFECTIVE_DATE,
3350 L_SEGMENT1,
3351 --L_TASK_NUMBER, -- Commented for Bug : 16591849
3352 GMS_INTERFACE_REC.TASK_ID, -- Introduced for Bug : 16591849
3353 GMS_INTERFACE_REC.EXPENDITURE_TYPE,
3354 1,
3355 GMS_INTERFACE_REC.SUMMARY_AMOUNT,
3356 L_EXPENDITURE_COMMENT,
3357 'P',GMS_INTERFACE_REC.SUMMARY_LINE_ID,
3358 GMS_INTERFACE_REC.ORG_ID,
3359 -- GMS_INTERFACE_REC.AWARD_ID, GMS_INTERFACE_REC.ATTRIBUTE2,
3360 -- gms_attr are sent to gms_interface
3361 gms_batch_rec.currency_code,
3362 GMS_INTERFACE_REC.SUMMARY_AMOUNT,
3363 gms_interface_rec.attribute1,
3364 gms_interface_rec.attribute2,
3365 GMS_INTERFACE_REC.ATTRIBUTE3,
3366 gms_interface_rec.attribute4,
3367 GMS_INTERFACE_REC.ATTRIBUTE5,
3368 GMS_INTERFACE_REC.ATTRIBUTE6,
3369 GMS_INTERFACE_REC.ATTRIBUTE7,
3370 GMS_INTERFACE_REC.ATTRIBUTE8,
3371 GMS_INTERFACE_REC.ATTRIBUTE9,
3372 GMS_INTERFACE_REC.ATTRIBUTE10,
3373 gms_interface_rec.exchange_rate_type,
3374 GMS_INTERFACE_REC.ACCOUNTING_DATE, --- 3108109
3375 p_business_group_id, -- Introduced for the Bug fix 2935850
3376 L_RETURN_STATUS);
3377
3378 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3379 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3380 END IF;
3381 --- moved code ends
3382 ELSE
3383 fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) ||' calling insert_into_pa_interface for STAT ');
3384
3385 insert_into_pa_interface(l_txn_interface_id,
3386 L_TXN_SOURCE,
3387 L_GMS_BATCH_NAME,
3388 L_EXPENDITURE_ENDING_DATE,
3389 L_EMPLOYEE_NUMBER,
3390 L_ORG_NAME,
3391 GMS_INTERFACE_REC.EFFECTIVE_DATE,
3392 L_SEGMENT1,
3393 -- L_TASK_NUMBER, -- Commented for Bug : 16591849
3394 GMS_INTERFACE_REC.TASK_ID, -- Introduced for Bug : 16591849
3395 GMS_INTERFACE_REC.EXPENDITURE_TYPE,
3396 GMS_INTERFACE_REC.SUMMARY_AMOUNT,
3397 1,
3398 L_EXPENDITURE_COMMENT,
3399 'P',
3400 GMS_INTERFACE_REC.SUMMARY_LINE_ID,
3401 GMS_INTERFACE_REC.ORG_ID,
3402 gms_batch_rec.currency_code,
3403 GMS_INTERFACE_REC.SUMMARY_AMOUNT,
3404 gms_interface_rec.attribute1,
3405 gms_interface_rec.attribute2,
3406 GMS_INTERFACE_REC.ATTRIBUTE3,
3407 gms_interface_rec.attribute4,
3408 GMS_INTERFACE_REC.ATTRIBUTE5,
3409 GMS_INTERFACE_REC.ATTRIBUTE6,
3410 GMS_INTERFACE_REC.ATTRIBUTE7,
3411 GMS_INTERFACE_REC.ATTRIBUTE8,
3412 GMS_INTERFACE_REC.ATTRIBUTE9,
3413 GMS_INTERFACE_REC.ATTRIBUTE10,
3414 gms_interface_rec.exchange_rate_type,
3415 GMS_INTERFACE_REC.ACCOUNTING_DATE,
3416 p_business_group_id,
3417 L_RETURN_STATUS);
3418
3419 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3420 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3421 END IF;
3422 END IF;
3423
3424 if gms_interface_rec.award_id is not null then
3425
3426 GMS_REC.TXN_INTERFACE_ID := l_txn_interface_id;
3427 GMS_REC.BATCH_NAME := l_gms_batch_name;
3428 GMS_REC.TRANSACTION_SOURCE := l_gms_transaction_source;
3429 GMS_REC.EXPENDITURE_ENDING_DATE := l_expenditure_ending_date;
3430 GMS_REC.EXPENDITURE_ITEM_DATE := gms_interface_rec.effective_date;
3431 GMS_REC.PROJECT_NUMBER := l_segment1;
3432 GMS_REC.TASK_NUMBER := l_task_number;
3433 GMS_REC.AWARD_ID := gms_interface_rec.award_id;
3434 GMS_REC.EXPENDITURE_TYPE := gms_interface_rec.expenditure_type;
3435 GMS_REC.TRANSACTION_STATUS_CODE := 'P';
3436 GMS_REC.ORIG_TRANSACTION_REFERENCE := gms_interface_rec.summary_line_id;
3437 GMS_REC.ORG_ID := GMS_INTERFACE_REC.org_id;
3438 GMS_REC.SYSTEM_LINKAGE := NULL;
3439 GMS_REC.USER_TRANSACTION_SOURCE := NULL;
3440 GMS_REC.TRANSACTION_TYPE := NULL;
3441 GMS_REC.BURDENABLE_RAW_COST := gms_interface_rec.summary_amount;
3442 GMS_REC.FUNDING_PATTERN_ID := NULL;
3443
3444 gms_transactions_pub.LOAD_GMS_XFACE_API(gms_rec, l_return_status);
3445
3446 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3447 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3448 END IF;
3449
3450 end if;
3451
3452
3453 END LOOP;
3454 -- For a failed small batch spanning more than one payroll control id, Transaction import is kicked off,
3455 -- Fixed this issue thru the following code along with bug fix 1828519
3456
3457 fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) ||' l_batch_cnt 7 : '||l_batch_cnt);
3458
3459 IF (l_batch_cnt = 0) THEN
3460 CLOSE gms_batch_cursor;
3461 EXIT;
3462 END IF;
3463 END LOOP;
3464 END LOOP;
3465
3466 IF l_batch_cnt > 0 THEN -- change 6902514
3467
3468 -- Bug 6902514 Start
3469
3470 SELECT to_char(psp_gms_batch_name_s.nextval)
3471 INTO l_gms_stat_batch_name
3472 FROM DUAL;
3473
3474 UPDATE pa_transaction_interface_all
3475 SET BATCH_NAME = l_gms_stat_batch_name
3476 where DENOM_CURRENCY_CODE = 'STAT'
3477 and BATCH_NAME = l_gms_batch_name
3478 and TRANSACTION_SOURCE = l_txn_source;
3479 fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) ||' updated pa_transaction_interface_all set batch_name to '||l_gms_stat_batch_name||' for STAT records count: '||sql%rowcount);
3480
3481 UPDATE psp_summary_lines
3482 SET gms_batch_name = l_gms_stat_batch_name
3483 WHERE payroll_control_id IN(select payroll_control_id from psp_payroll_controls
3484 where run_id = g_run_id
3485 and currency_code = 'STAT')
3486 AND gms_batch_name = l_gms_batch_name
3487 AND status_code = 'N'
3488 AND gl_code_combination_id IS NULL;
3489 fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) ||' updated psp_summary_lines set gms_batch_name to '||l_gms_stat_batch_name||' for STAT records count: '||sql%rowcount);
3490
3491
3492
3493 SELECT count(*) INTO l_not_stat_count
3494 FROM pa_transaction_interface_all
3495 WHERE BATCH_NAME = l_gms_batch_name;
3496
3497 fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) ||'l_gms_batch_name'||l_gms_batch_name);
3498 fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) ||'l_gms_stat_batch_name'||l_gms_stat_batch_name);
3499 fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) ||'l_not_stat_count'||l_not_stat_count);
3500
3501
3502
3503
3504 IF l_not_stat_count<>0 THEN
3505
3506
3507 FOR I in 1..org_id_tab.count
3508 LOOP
3509 l_gms_batch_name := gms_batch_name_tab(I);
3510
3511 fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) ||'l_gms_batch_name1 : '||l_gms_batch_name);
3512
3513 -- set the context to single to submit_request
3514 mo_global.set_policy_context('S', org_id_tab(I) );
3515 fnd_request.set_org_id (org_id_tab(I) );
3516
3517 req_id_tab(i) := fnd_request.submit_request(
3518 'PA',
3519 'PAXTRTRX',
3520 NULL,
3521 NULL,
3522 FALSE,
3523 l_txn_source, ----l_transaction_source,
3524 l_gms_batch_name);
3525
3526 IF req_id = 0 THEN
3527 fnd_message.set_name('PSP','PSP_TR_GMS_IMP_FAILED');
3528 fnd_msg_pub.add;
3529 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3530
3531 END IF;
3532 END LOOP;
3533
3534 -- Added FOR LOOP for Bug : 13502385
3535
3536 FOR J in 1..org_id_tab.count --13502385
3537 LOOP --13502385
3538 l_gms_batch_name := gms_batch_name_tab(J); --13502385
3539
3540 fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) ||'l_gms_batch_name2 : '||l_gms_batch_name); --13502385
3541
3542 OPEN payroll_control_id_cur;
3543 FETCH payroll_control_id_cur BULK COLLECT INTO r_payroll_controls.payroll_control_id;
3544 CLOSE payroll_control_id_cur;
3545
3546 FORALL I IN 1..r_payroll_controls.payroll_control_id.COUNT
3547 UPDATE psp_payroll_controls
3548 SET gms_phase = 'Submitted_Import_Request'
3549 WHERE payroll_control_id = r_payroll_controls.payroll_control_id(I);
3550
3551
3552 FOR I IN 1..r_payroll_controls.payroll_control_id.COUNT
3553 LOOP
3554 fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) ||' updating psp_payroll_controls count: '||sql%bulk_rowcount(I));
3555 END LOOP;
3556
3557 END LOOP; --13502385
3558
3559
3560
3561 Commit;
3562
3563 r_payroll_controls.payroll_control_id.DELETE;
3564 -- End of changes for bug fix 4507892
3565
3566 -- set the context again to multiple
3567 mo_global.set_policy_context('M', null);
3568
3569
3570 FOR I in 1..org_id_tab.count
3571 LOOP
3572 call_status_tab(i) := fnd_concurrent.wait_for_request(req_id_tab(i), 20, 0,
3573 rphase, rstatus, dphase, dstatus, message);
3574
3575 IF call_status = FALSE then
3576 fnd_message.set_name('PSP','PSP_TR_GMS_IMP_FAILED');
3577 fnd_msg_pub.add;
3578 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3579
3580 END IF;
3581 END LOOP;
3582
3583 END IF; -- l_not_stat_count
3584
3585
3586 SELECT count(*) INTO l_stat_count -- change
3587 FROM pa_transaction_interface_all
3588 WHERE BATCH_NAME = l_gms_stat_batch_name;
3589
3590 fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) ||' l_stat_count'||l_stat_count);
3591
3592 g_skip_flag_gms := 'N';
3593
3594 l_gms_batch_name := l_gms_stat_batch_name;
3595
3596
3597 IF l_stat_count <> 0 THEN
3598
3599 g_skip_flag_gms := 'Y';
3600
3601 IF (g_create_stat_batch_in_gms = 'Y') THEN
3602 fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) ||'STAT entrance');
3603
3604 g_skip_flag_gms := 'N';
3605
3606 FOR I in 1..org_id_tab.count
3607 LOOP
3608 l_gms_batch_name := gms_batch_name_tab(I);
3609
3610 -- set the context to single to submit_request
3611 mo_global.set_policy_context('S', org_id_tab(I) );
3612 fnd_request.set_org_id (org_id_tab(I) );
3613
3614 fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) ||' Submitting PRC: Transaction Import for STAT');
3615
3616 req_id_tab(i) := fnd_request.submit_request(
3617 'PA',
3618 'PAXTRTRX',
3619 NULL,
3620 NULL,
3621 FALSE,
3622 l_txn_source, ----l_transaction_source,
3623 l_gms_batch_name);
3624
3625 IF req_id = 0 THEN
3626 fnd_message.set_name('PSP','PSP_TR_GMS_IMP_FAILED');
3627 fnd_msg_pub.add;
3628 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3629
3630 END IF;
3631 END LOOP;
3632
3633 END IF; -- g_create_stat_batch_in_gms
3634
3635 OPEN payroll_control_id_cur;
3636 FETCH payroll_control_id_cur BULK COLLECT INTO r_payroll_controls.payroll_control_id;
3637 CLOSE payroll_control_id_cur;
3638
3639 FORALL I IN 1..r_payroll_controls.payroll_control_id.COUNT
3640 UPDATE psp_payroll_controls
3641 SET gms_phase = 'Submitted_Import_Request'
3642 WHERE payroll_control_id = r_payroll_controls.payroll_control_id(I);
3643
3644 FOR I IN 1..r_payroll_controls.payroll_control_id.COUNT
3645 LOOP
3646 fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) ||' updating psp_payroll_controls count: '||sql%bulk_rowcount(I));
3647 END LOOP;
3648
3649
3650
3651
3652
3653
3654 Commit;
3655
3656 r_payroll_controls.payroll_control_id.DELETE;
3657 -- End of changes for bug fix 4507892
3658
3659 -- set the context again to multiple
3660 mo_global.set_policy_context('M', null);
3661
3662 IF g_skip_flag_gms = 'N' then
3663
3664 FOR I in 1..org_id_tab.count
3665 LOOP
3666 call_status_tab(i) := fnd_concurrent.wait_for_request(req_id_tab(i), 20, 0,
3667 rphase, rstatus, dphase, dstatus, message);
3668
3669 IF call_status = FALSE then
3670 fnd_message.set_name('PSP','PSP_TR_GMS_IMP_FAILED');
3671 fnd_msg_pub.add;
3672 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3673
3674 END IF;
3675 END LOOP;
3676
3677 END IF; -- g_skip_flag_gms
3678
3679
3680
3681
3682 END IF; -- l_stat_count
3683
3684
3685 END IF; -- l_batch_cnt -- end of changes for 6902514
3686
3687 fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) ||' g_skip_flag_gms = '||g_skip_flag_gms);
3688
3689 p_return_status := fnd_api.g_ret_sts_success;
3690 EXCEPTION
3691 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3692 g_error_api_path := 'TRANSFER_TO_GMS_INTERFACE:'||g_error_api_path;
3693 --Bug 1776606 : Building error Stack
3694 fnd_msg_pub.add_exc_msg('PSP_SUM_ADJ','TRANSFER_TO_GMS_INTERFACE');
3695 p_return_status := fnd_api.g_ret_sts_unexp_error;
3696
3697 WHEN OTHERS THEN
3698 g_error_api_path := 'TRANSFER_TO_GMS_INTERFACE:'||g_error_api_path;
3699 fnd_msg_pub.add_exc_msg('PSP_SUM_ADJ','TRANSFER_TO_GMS_INTERFACE');
3700 p_return_status := fnd_api.g_ret_sts_unexp_error;
3701 END;
3702
3703 ------------------------- GMS TIE BACK ---------------------------------------------------
3704 /* We want to maintain the control at the adjustment batch level.
3705 For the super batch, for each payroll control id check the results.
3706 If there are any rejects for a given adjustment batch, then clean up the tables.
3707 If the whole adj batch is accepted, then move them to the history.
3708 */
3709
3710 PROCEDURE gms_tie_back(p_adj_sum_batch_name IN VARCHAR2,
3711 p_business_group_id IN NUMBER,
3712 p_set_of_books_id IN NUMBER,
3713 p_return_status OUT NOCOPY VARCHAR2) IS
3714
3715 CURSOR pc_batch_cur IS
3716 SELECT distinct batch_name
3717 FROM psp_payroll_controls
3718 WHERE adj_sum_batch_name = p_adj_sum_batch_name
3719 AND (dist_dr_amount is not null and dist_cr_amount is not null)
3720 AND source_type = 'A'
3721 AND status_code = 'I'
3722 AND gms_phase = 'Submitted_Import_Request'
3723 AND business_group_id = p_business_group_id
3724 AND set_of_books_id = p_set_of_books_id
3725 AND run_id = nvl(g_run_id, run_id);
3726
3727 pc_batch_rec pc_batch_cur%ROWTYPE;
3728
3729 CURSOR gms_tie_back_cur(p_batch_name IN VARCHAR2) IS
3730 SELECT payroll_control_id,
3731 source_type,
3732 payroll_source_code,
3733 time_period_id,
3734 batch_name,
3735 currency_code -- 6902514
3736 FROM psp_payroll_controls
3737 WHERE batch_name = p_batch_name
3738 AND (dist_dr_amount is not null and dist_cr_amount is not null)
3739 AND source_type = 'A'
3740 AND status_code = 'I'
3741 AND gms_phase = 'Submitted_Import_Request'
3742 AND run_id = nvl(g_run_id, run_id);
3743
3744 gms_tie_back_rec gms_tie_back_cur%ROWTYPE;
3745
3746 CURSOR gms_tie_back_success_cur(p_gms_batch_name number, p_payroll_control_id number) IS
3747 SELECT summary_line_id,
3748 dr_cr_flag,summary_amount
3749 FROM psp_summary_lines
3750 WHERE gms_batch_name = p_gms_batch_name
3751 AND payroll_control_id = p_payroll_control_id;
3752
3753
3754 CURSOR gms_tie_back_reject_cur(p_gms_batch_name number, p_payroll_control_id number, p_txn_src varchar2) IS
3755 SELECT nvl(transaction_rejection_code,'P'),
3756 orig_transaction_reference,
3757 transaction_status_code
3758 FROM pa_transaction_interface_all
3759 WHERE batch_name = to_char(p_gms_batch_name)
3760 AND transaction_status_code in ('R', 'PO', 'PI', 'PR')
3761 AND transaction_source = p_txn_src
3762 AND orig_transaction_reference in
3763 (select to_char(summary_line_id)
3764 from psp_summary_lines
3765 where payroll_control_id = p_payroll_control_id
3766 and gms_batch_name = p_gms_batch_name);
3767
3768 l_orig_org_name1 hr_all_organization_units_tl.name%TYPE; -- Bug 2447912: Modified declaration
3769 l_orig_org_id1 number;
3770
3771 l_organization_name hr_all_organization_units_tl.name%TYPE; -- Bug 2447912: Modified declaration
3772 l_organization_id NUMBER(15);
3773 l_rowid ROWID;
3774 l_assignment_id NUMBER(9);
3775 l_distribution_date DATE;
3776 l_suspense_org_account_id NUMBER(9);
3777 --
3778 l_organization_account_id NUMBER(9);
3779 l_gl_code_combination_id NUMBER(15);
3780 l_project_id NUMBER(15);
3781 l_award_id NUMBER(15);
3782 l_task_id NUMBER(15);
3783 --
3784 l_cnt_gms_interface NUMBER;
3785 l_summary_line_id NUMBER(10);
3786 l_gl_project_flag VARCHAR2(1);
3787 l_suspense_ac_failed VARCHAR2(1) := 'N';
3788 l_suspense_ac_not_found VARCHAR2(1) := 'N';
3789 l_susp_ac_found VARCHAR2(10) := 'TRUE';
3790 l_summary_amount NUMBER;
3791 l_dr_summary_amount NUMBER := 0;
3792 l_cr_summary_amount NUMBER := 0;
3793 l_dr_cr_flag VARCHAR2(1);
3794 --
3795 l_trx_status_code VARCHAR2(1);
3796 l_trx_reject_code VARCHAR2(30);
3797 l_orig_trx_reference VARCHAR2(30);
3798 l_effective_date DATE;
3799
3800 x_susp_failed_org_name hr_all_organization_units_tl.name%TYPE; -- Bug 2447912: Modified declaration
3801 x_susp_failed_reject_code VARCHAR2(30);
3802 x_susp_failed_date DATE;
3803 x_susp_nf_org_name hr_all_organization_units_tl.name%TYPE; -- Bug 2447912: Modified declaration
3804 x_susp_nf_date DATE;
3805 l_return_status VARCHAR2(10);
3806 l_msg_id number(9);
3807 x_update_count number(9);
3808 x_insert_count number(9);
3809 x_delete_count number(9);
3810 l_dist_line_id1 number(9);
3811 l_gms_batch_name number(15);
3812 l_gms_batch_name1 varchar2(10); --Bug 6118274
3813 --
3814 l_adjustment_batch_name varchar2(50);
3815 l_person_id number;
3816 l_person_name varchar2(80);
3817 -- l_assignment_id number;
3818 l_assignment_number number;
3819 l_element_type_id number;
3820 --- l_element_name varchar2(80);
3821 l_distribution_start_date date;
3822 l_distribution_end_date date;
3823 l_trx_rejection_code varchar2(80);
3824 l_no_run number := 0;
3825 l_status_i number := 0;
3826 l_transaction_source varchar2(40);
3827 TI_DID_NOT_COMPLETE EXCEPTION;
3828 l_control_id number;
3829 l_txn_source varchar2(30);
3830
3831
3832 -- the following cursors are included here for accessing the local variables for bug fix 1765678
3833 CURSOR gms_batch_name_cur IS
3834 SELECT DISTINCT gms_batch_name
3835 FROM psp_summary_lines
3836 WHERE payroll_control_id = gms_tie_back_rec.payroll_control_id
3837 AND gms_batch_name IS NOT NULL;
3838
3839 CURSOR transaction_source_cur IS
3840 SELECT transaction_source
3841 FROM pa_transaction_interface_all
3842 WHERE batch_name = TO_CHAR(l_gms_batch_name);
3843
3844 CURSOR cnt_gms_interface_cur IS
3845 SELECT count(*)
3846 FROM pa_transaction_interface_all
3847 WHERE batch_name = TO_CHAR(l_gms_batch_name)
3848 AND transaction_source = l_txn_source
3849 AND transaction_status_code IN ('R', 'PO', 'PI', 'PR')
3850 AND orig_transaction_reference IN (SELECT TO_CHAR(summary_line_id)
3851 FROM psp_summary_lines
3852 WHERE payroll_control_id = gms_tie_back_rec.payroll_control_id
3853 AND gms_batch_name = l_gms_batch_name);
3854 FUNCTION PROCESS_COMPLETE RETURN BOOLEAN IS
3855
3856 cursor get_completion is
3857 select count(*), transaction_status_code
3858 from pa_transaction_interface_all
3859 where batch_name = to_char(l_gms_batch_name)
3860 and transaction_source = l_txn_source
3861 and transaction_status_code in ('P', 'I')
3862 group by transaction_status_code ;
3863
3864 get_completion_rec get_completion%ROWTYPE;
3865 --- Bug 2133056, purge the PA Side items, if PA post import user extension failed.
3866 CURSOR group_name_cur IS
3867 select expenditure_group
3868 from pa_expenditures_all
3869 where expenditure_id in
3870 (select expenditure_id
3871 from pa_transaction_interface_all
3872 where transaction_source in ('OLD', 'GOLD') and
3873 transaction_rejection_code is null and
3874 batch_name = l_gms_batch_name )
3875 and expenditure_group is not null;
3876
3877 l_exp_group_name varchar2(20) := NULL;
3878 begin
3879 fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) ||' Entering PROCESS_COMPLETE ');
3880
3881
3882 open get_completion;
3883 loop
3884 fetch get_completion into get_completion_rec;
3885
3886 if get_completion%ROWCOUNT = 0 then
3887 close get_completion;
3888 return TRUE;
3889 elsif get_completion%NOTFOUND then
3890 close get_completion;
3891 return FALSE;
3892 end if;
3893
3894 if get_completion_rec.transaction_status_code = 'P' then
3895 fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) ||' get_completion_rec.transaction_status_code = P');
3896
3897
3898 -- -------------------------------------------------------------------------------------------
3899 -- If transaction_status_code = 'P' then the transaction import process did not kick off
3900 -- for some reason. Return 'NOT_RUN' in this case. So cleanup the tables and try to transfer
3901 -- again after summarization in the second pass.
3902 -- -------------------------------------------------------------------------------------------
3903
3904 delete from pa_transaction_interface_all
3905 where batch_name = to_char(l_gms_batch_name)
3906 and transaction_source = l_txn_source;
3907
3908 fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) ||' deleted from pa_transaction_interface_all count: '||sql%rowcount);
3909
3910
3911 if (l_txn_source = 'GOLD') then
3912 delete from gms_transaction_interface_all
3913 where batch_name = to_char(l_gms_batch_name)
3914 and transaction_source = 'GOLD';
3915 fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) ||' deleted from gms_transaction_interface_all count: '||sql%rowcount);
3916
3917 end if;
3918
3919 /* Commented for 2133056, Let recover start from Import and not Summarize
3920 delete from psp_summary_lines
3921 where gms_batch_name = l_gms_batch_name
3922 and payroll_control_id = gms_tie_back_rec.payroll_control_id;
3923 */
3924 -- Added following update for 2133056
3925 update psp_payroll_controls
3926 set gms_phase = 'Summarize_GMS_Lines'
3927 where gms_phase is not null
3928 and adj_sum_batch_name = p_adj_sum_batch_name;
3929
3930 fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) ||' Updated psp_payroll_controls set gms_phase to Summarize_GMS_Lines
3931 for adj_sum_batch_name: '||p_adj_sum_batch_name||' count: '||sql%rowcount);
3932
3933 commit;
3934 return false;
3935 elsif get_completion_rec.transaction_status_code = 'I' then
3936 fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) ||' get_completion_rec.transaction_status_code = I');
3937
3938 -- -------------------------------------------------------------------------------------------
3939 -- If transaction_status_code = 'I' then the transaction import process did not complete
3940 -- the Post Processing extension. So return 'NOT_COMPLETE' in this case.
3941 -- In this case purging the GMS/PA side expenditures, and resetting the payroll control recs
3942 -- to fresh, so that user can run the Restart Adj S and T process.
3943 -- -------------------------------------------------------------------------------------------
3944 /* Added the delete statements for EXP, EXP items, EXP groups etc for bug 2133056 */
3945 OPEN group_name_cur;
3946 FETCH group_name_cur into l_exp_group_name ;
3947 CLOSE group_name_cur;
3948
3949 if l_exp_group_name is not null then
3950
3951 delete gms_award_distributions
3952 where document_type = 'EXP'
3953 and expenditure_item_id in
3954 ( select expenditure_item_id
3955 from pa_expenditure_items_all
3956 where transaction_source = 'GOLD'
3957 and expenditure_id in
3958 (select expenditure_id
3959 from pa_expenditures_all
3960 where expenditure_group = l_exp_group_name));
3961
3962 fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) ||' deleted from gms_award_distributions count: '||sql%rowcount);
3963
3964 delete pa_expenditure_items_all
3965 where transaction_source in ('OLD','GOLD')
3966 and expenditure_id in
3967 (select expenditure_id
3968 from pa_expenditures_all
3969 where expenditure_group = l_exp_group_name);
3970
3971 fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) ||' deleted from pa_expenditure_items_all count: '||sql%rowcount);
3972
3973 delete pa_expenditures_all
3974 where expenditure_group = l_exp_group_name;
3975
3976 fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) ||' deleted from pa_expenditures_all count: '||sql%rowcount);
3977
3978 delete pa_expenditure_Groups_all
3979 where expenditure_group = l_exp_group_name;
3980 fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) ||' deleted from pa_expenditure_Groups_all count: '||sql%rowcount);
3981
3982 end if;
3983
3984 if (l_txn_source = 'GOLD') then
3985 delete gms_transaction_interface_all
3986 where transaction_source in ('GOLD') and
3987 batch_name = l_gms_batch_name;
3988 fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) ||' deleted from gms_transaction_interface_all count: '||sql%rowcount);
3989
3990 end if;
3991
3992 delete pa_transaction_interface_all
3993 where transaction_source in ('GOLD','OLD') and
3994 batch_name = l_gms_batch_name;
3995
3996 delete psp_summary_lines
3997 where payroll_control_id in
3998 (select payroll_control_id
3999 from psp_payroll_controls
4000 where source_type = 'A'
4001 and adj_sum_batch_name = p_adj_sum_batch_name);
4002
4003 fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) ||' deleted from psp_summary_lines count: '||sql%rowcount);
4004
4005 update psp_payroll_controls
4006 set gms_phase = null
4007 where gms_phase is not null
4008 and source_type = 'A'
4009 and adj_sum_batch_name = p_adj_sum_batch_name;
4010
4011 fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) ||' updated psp_payroll_controls set gms_phase to null count: '||sql%rowcount);
4012
4013 commit;
4014 return false;
4015
4016
4017 end if;
4018
4019 end loop;
4020
4021 exception
4022 when others then
4023 fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) ||' exception = '||sqlerrm);
4024
4025 fnd_msg_pub.add_exc_msg('PSP_SUM_ADJ','GET_PROCESS');
4026 return FALSE;
4027 end PROCESS_COMPLETE;
4028
4029 BEGIN
4030
4031 fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Entering gms_tie_back');
4032
4033
4034 fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) ||' g_run_id..' || g_run_id);
4035
4036
4037
4038 open pc_batch_cur;
4039 loop
4040 fetch pc_batch_cur into pc_batch_rec;
4041 if pc_batch_cur%NOTFOUND then
4042 close pc_batch_cur;
4043 exit;
4044 end if;
4045
4046 --dbms_output.put_line('Getting the batches..' || pc_batch_rec.batch_name);
4047 fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) ||' Getting the batches..' || pc_batch_rec.batch_name);
4048
4049 -- Added for bug 9481186
4050 IF gms_tie_back_cur%ISOPEN THEN
4051 close gms_tie_back_cur;
4052 END IF;
4053
4054 open gms_tie_back_cur(pc_batch_rec.batch_name);
4055 loop
4056 fetch gms_tie_back_cur into gms_tie_back_rec;
4057 if gms_tie_back_cur%NOTFOUND then
4058 close gms_tie_back_cur;
4059 exit;
4060 end if;
4061
4062 fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) ||' Getting the payroll.. ' || to_char(gms_tie_back_rec.payroll_control_id));
4063
4064 --dbms_output.put_line('Getting the payroll.. ' || to_char(gms_tie_back_rec.payroll_control_id));
4065 -- Replaced the earlier 'select stmt.' code with new 'cursor' code for bug fix 1765678
4066
4067 -- Bug 14138307
4068 IF gms_batch_name_cur%ISOPEN THEN
4069 close gms_batch_name_cur;
4070 END IF;
4071
4072
4073 OPEN gms_batch_name_cur;
4074 LOOP
4075 FETCH gms_batch_name_cur INTO l_gms_batch_name;
4076 IF (gms_batch_name_cur%NOTFOUND) THEN
4077 CLOSE gms_batch_name_cur;
4078 EXIT;
4079 END IF;
4080 -- CLOSE gms_batch_name_cur;
4081
4082 fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) ||' before userhook - l_gms_batch_name='||l_gms_batch_name);
4083 ----- new procedure for 5463110
4084 psp_st_ext.tieback_adjustment(gms_tie_back_rec.payroll_control_id,
4085 pc_batch_rec.batch_name,
4086 l_gms_batch_name ,
4087 p_business_group_id,
4088 p_set_of_books_id );
4089 --fnd_file.put_line(fnd_file.log, 'after user hook');
4090 hr_utility.trace('after userhook - ');
4091 fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) ||'after userhook - ');
4092
4093
4094 OPEN transaction_source_cur;
4095 FETCH transaction_source_cur INTO l_txn_source;
4096 IF (transaction_source_cur%NOTFOUND) THEN
4097 CLOSE transaction_source_cur;
4098 EXIT;
4099 END IF;
4100 CLOSE transaction_source_cur;
4101
4102
4103 IF ((gms_tie_back_rec.currency_code <> 'STAT') OR -- 6902514
4104 (gms_tie_back_rec.currency_code = 'STAT' and g_create_stat_batch_in_gms = 'Y')) THEN
4105
4106
4107 if NOT PROCESS_COMPLETE then
4108
4109 ----- changed message tag for bug 2133056
4110 fnd_message.set_name('PSP','PSP_TR_GMS_IMP_FAILED');
4111 fnd_msg_pub.add;
4112 close gms_tie_back_cur;
4113 CLOSE gms_batch_name_cur;
4114 RAISE TI_DID_NOT_COMPLETE; --- Added this for Bug 2133056
4115
4116 end if;
4117
4118 END IF; -- gms_tie_back_rec.currency_code
4119
4120 -- Replaced the earlier 'select stmt.' code with new 'cursor' code for bug fix 1765678
4121
4122 OPEN cnt_gms_interface_cur;
4123 FETCH cnt_gms_interface_cur INTO l_cnt_gms_interface;
4124 IF (cnt_gms_interface_cur%NOTFOUND) THEN
4125 CLOSE cnt_gms_interface_cur;
4126 EXIT;
4127 END IF;
4128 CLOSE cnt_gms_interface_cur;
4129
4130 fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) ||' l_cnt_gms_interface : ' || to_char(l_cnt_gms_interface));
4131
4132 IF l_cnt_gms_interface > 0 THEN
4133 -- Commented the following loop for finding rejection reason code as part of bug fix 1828519
4134 -- OPEN gms_tie_back_reject_cur(l_gms_batch_name, gms_tie_back_rec.payroll_control_id, l_txn_source);
4135 -- LOOP
4136 -- FETCH gms_tie_back_reject_cur INTO l_trx_reject_code,l_orig_trx_reference,l_trx_status_code;
4137 -- IF gms_tie_back_reject_cur%NOTFOUND THEN
4138 -- CLOSE gms_tie_back_reject_cur;
4139 -- EXIT;
4140 -- END IF;
4141
4142 -- l_trx_rejection_code := l_trx_reject_code;
4143 -- END LOOP;
4144
4145 fnd_message.set_name('PSP','PSP_ADJ_GMS_FAILED');
4146 -- Commented the following token as part of bug fix 1828519
4147 -- fnd_message.set_token('ERR_NAME', l_trx_rejection_code);
4148 get_the_batch_details(pc_batch_rec.batch_name, l_return_status);
4149 fnd_msg_pub.add;
4150 /* 1685685 added update statement...to clean pa tables in cleanup_batch_details..Venkat.*/
4151 update psp_payroll_controls
4152 set gms_phase = 'GMS_Tie_Back'
4153 where payroll_control_id = gms_tie_back_rec.payroll_control_id;
4154 fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) ||' updated psp_payroll_controls for control_id = '||gms_tie_back_rec.payroll_control_id);
4155
4156 cleanup_batch_details(gms_tie_back_rec.payroll_control_id,null);
4157 close gms_tie_back_cur;
4158 CLOSE gms_batch_name_cur;
4159 exit;
4160
4161
4162 ELSIF l_cnt_gms_interface = 0 THEN
4163 fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) ||' before bulk update of psp_summary_lines in tieback - ');
4164
4165
4166 l_gms_batch_name1 := l_gms_batch_name; --Bug 6118274
4167 --- moved this stmnt from the loop below, and modified for 2445196.
4168 UPDATE psp_summary_lines PSL
4169 SET (PSL.status_code, PSL.expenditure_ending_date,PSL.expenditure_id,
4170 PSL.interface_id,PSL.expenditure_item_id,PSL.txn_interface_id) =
4171 (select 'A', PTXN.expenditure_ending_date,PTXN.expenditure_id,
4172 PTXN.interface_id,PTXN.expenditure_item_id,PTXN.txn_interface_id
4173 from pa_transaction_interface_all PTXN
4174 where PTXN.transaction_source = l_txn_source
4175 and PTXN.orig_transaction_reference= to_char(PSL.summary_line_id)
4176 and PTXN.batch_name = l_gms_batch_name1)
4177 WHERE PSL.gms_batch_name = l_gms_batch_name1; --- changed g_gms_batch_name to l_gms_batch_name for 2444657
4178
4179
4180 OPEN gms_tie_back_success_cur(l_gms_batch_name, gms_tie_back_rec.payroll_control_id);
4181 l_dr_summary_amount := 0; --- Bug 2133056, initialized the amounts
4182 l_cr_summary_amount := 0;
4183 LOOP
4184 --dbms_output.put_line('Getting the success .. ' );
4185 l_control_id := gms_tie_back_rec.payroll_control_id;
4186 FETCH gms_tie_back_success_cur INTO l_summary_line_id,
4187 l_dr_cr_flag,l_summary_amount;
4188
4189 IF gms_tie_back_success_cur%ROWCOUNT = 0 then
4190 close gms_tie_back_success_cur;
4191 exit;
4192 ELSIF gms_tie_back_success_cur%NOTFOUND THEN
4193
4194 UPDATE psp_payroll_controls
4195 SET gms_phase = 'GMS_Tie_Back'
4196 WHERE payroll_control_id = l_control_id;
4197
4198 CLOSE gms_tie_back_success_cur;
4199 EXIT;
4200 END IF;
4201
4202 -- update records in psp_summary_lines as 'A' , moved this stmnt above for 2445196
4203
4204 IF l_dr_cr_flag = 'D' THEN
4205 l_dr_summary_amount := l_dr_summary_amount + l_summary_amount;
4206 ELSIF l_dr_cr_flag = 'C' THEN
4207 l_cr_summary_amount := l_cr_summary_amount - l_summary_amount;
4208 END IF;
4209
4210 UPDATE psp_adjustment_lines
4211 SET status_code = 'A' WHERE summary_line_id = l_summary_line_id;
4212
4213 -- move the transferred records to psp_adjustment_lines_history
4214 INSERT INTO psp_adjustment_lines_history
4215 (adjustment_line_id,person_id,assignment_id,element_type_id,
4216 distribution_date,effective_date,distribution_amount,
4217 dr_cr_flag,payroll_control_id,source_type,source_code,time_period_id,
4218 batch_name,status_code,set_of_books_id,gl_code_combination_id,project_id,
4219 expenditure_organization_id,expenditure_type,task_id,award_id,
4220 suspense_org_account_id,suspense_reason_code,effort_report_id,version_num,
4221 summary_line_id, reversal_entry_flag, original_line_flag, user_defined_field, percent,
4222 orig_source_type,
4223 orig_line_id,attribute_category,attribute1,attribute2,attribute3,attribute4,
4224 attribute5,attribute6,attribute7,attribute8,attribute9,attribute10,attribute11,
4225 attribute12,attribute13,attribute14,attribute15,last_update_date,
4226 last_updated_by,last_update_login,created_by,creation_date, business_group_id,
4227 adj_set_number, line_number) --- added cols 2634557 DA Multiple element Enh
4228 SELECT adjustment_line_id,person_id,assignment_id,element_type_id,
4229 distribution_date,effective_date,distribution_amount,
4230 dr_cr_flag,payroll_control_id,source_type,source_code,time_period_id,
4231 batch_name,status_code,set_of_books_id,gl_code_combination_id,project_id,
4232 expenditure_organization_id,expenditure_type,task_id,award_id,
4233 suspense_org_account_id,suspense_reason_code,effort_report_id,version_num,
4234 summary_line_id, reversal_entry_flag, original_line_flag, user_defined_field, percent,
4235 orig_source_type,
4236 orig_line_id,attribute_category,attribute1,attribute2,attribute3,attribute4,
4237 attribute5,attribute6,attribute7,attribute8,attribute9,attribute10,attribute11,
4238 attribute12,attribute13,attribute14,attribute15,SYSDATE,FND_GLOBAL.USER_ID,
4239 FND_GLOBAL.LOGIN_ID,FND_GLOBAL.USER_ID,SYSDATE, business_group_id,
4240 adj_set_number, line_number --- added cols 2634557 DA Multiple element Enh
4241 FROM psp_adjustment_lines
4242 WHERE status_code = 'A'
4243 AND summary_line_id = l_summary_line_id
4244 AND payroll_control_id = gms_tie_back_rec.payroll_control_id;
4245
4246 DELETE FROM psp_adjustment_lines
4247 WHERE status_code = 'A'
4248 AND summary_line_id = l_summary_line_id
4249 AND payroll_control_id = gms_tie_back_rec.payroll_control_id;
4250
4251 /* Bug 2133056: Moving this statment into Mark Batch End process, for del exp stmt to work.
4252 DELETE FROM pa_transaction_interface_all
4253 WHERE orig_transaction_reference = to_char(l_summary_line_id)
4254 AND transaction_status_code = 'A'
4255 AND transaction_source = l_txn_source; */
4256 fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) ||' Updated psp_summary_lines, psp_adjustment_lines inserted into
4257 psp_adjustment_lines_history then deleted from psp_adjustment_lines for summary_line_id : '||l_summary_line_id);
4258
4259
4260 END LOOP; -- End loop for gms_tie_back_success_cur
4261
4262 UPDATE psp_payroll_controls
4263 SET ogm_dr_amount = nvl(ogm_dr_amount,0) + l_dr_summary_amount,
4264 ogm_cr_amount = nvl(ogm_cr_amount,0) + l_cr_summary_amount
4265 WHERE payroll_control_id = gms_tie_back_rec.payroll_control_id;
4266
4267 fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) ||' Updated psp_payroll_controls set ogm_dr_amount and ogm_cr_amount for cotrol_id : '||gms_tie_back_rec.payroll_control_id);
4268
4269 END IF;
4270 END LOOP; -- End Loop for gms_batch_name_cur
4271
4272 if gms_tie_back_cur%isopen then -- added for the bug 11841641
4273 NULL;
4274 --fnd_file.put_line(fnd_file.log,' gms_batch_name_cur open');
4275 else
4276 exit;
4277 end if;
4278
4279 END LOOP; -- End loop for gms_tie_back_cur
4280
4281 commit;
4282 END LOOP; -- End loop for pc_batch_cur
4283 --
4284 p_return_status := fnd_api.g_ret_sts_success;
4285 EXCEPTION
4286 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4287 g_error_api_path := 'GMS_TIE_BACK:'||g_error_api_path;
4288 fnd_msg_pub.add_exc_msg('PSP_SUM_ADJ','GMS_TIE_BACK');
4289 p_return_status := fnd_api.g_ret_sts_unexp_error;
4290
4291 WHEN TI_DID_NOT_COMPLETE THEN
4292 g_error_api_path := 'GMS_TIE_BACK:' || 'Transaction Import did not complete for some batches';
4293 fnd_msg_pub.add_exc_msg('PSP_ST_ADJ', 'GMS_TIE_BACK');
4294 p_return_status := fnd_api.g_ret_sts_unexp_error;
4295
4296 WHEN OTHERS THEN
4297 g_error_api_path := 'GMS_TIE_BACK:'||g_error_api_path;
4298 fnd_msg_pub.add_exc_msg('PSP_SUM_ADJ','GMS_TIE_BACK');
4299 p_return_status := fnd_api.g_ret_sts_unexp_error;
4300 END;
4301
4302 ------------------ INSERT INTO GMS INTERFACE -----------------------------------------------
4303
4304 PROCEDURE insert_into_pa_interface(
4305 P_INTERFACE_ID IN NUMBER,
4306 P_TRANSACTION_SOURCE IN VARCHAR2,
4307 P_BATCH_NAME IN VARCHAR2,
4308 P_EXPENDITURE_ENDING_DATE IN DATE,
4309 P_EMPLOYEE_NUMBER IN VARCHAR2,
4310 P_ORGANIZATION_NAME IN VARCHAR2,
4311 P_EXPENDITURE_ITEM_DATE IN DATE,
4312 P_PROJECT_NUMBER IN VARCHAR2,
4313 --P_TASK_NUMBER IN VARCHAR2, -- Commented for Bug : 16591849
4314 P_TASK_ID IN NUMBER, -- Introduced for Bug : 16591849
4315 P_EXPENDITURE_TYPE IN VARCHAR2,
4316 P_QUANTITY IN NUMBER,
4317 P_RAW_COST IN NUMBER,
4318 P_EXPENDITURE_COMMENT IN VARCHAR2,
4319 P_TRANSACTION_STATUS_CODE IN VARCHAR2,
4320 P_ORIG_TRANSACTION_REFERENCE IN VARCHAR2,
4321 P_ORG_ID IN NUMBER,
4322 P_DENOM_CURRENCY_CODE IN VARCHAR2,
4323 P_DENOM_RAW_COST IN NUMBER,
4324 P_ATTRIBUTE1 IN VARCHAR2,
4325 P_ATTRIBUTE2 IN VARCHAR2,
4326 P_ATTRIBUTE3 IN VARCHAR2,
4327 P_ATTRIBUTE4 IN VARCHAR2, -- Introduced attributes 4,5 for bug fix 2908859
4328 P_ATTRIBUTE5 IN VARCHAR2,
4329 P_ATTRIBUTE6 IN VARCHAR2,
4330 P_ATTRIBUTE7 IN VARCHAR2,
4331 P_ATTRIBUTE8 IN VARCHAR2,
4332 P_ATTRIBUTE9 IN VARCHAR2,
4333 P_ATTRIBUTE10 IN VARCHAR2,
4334 P_ACCT_RATE_TYPE IN VARCHAR2, -- Introduced for bug fix 2916848
4335 P_ACCT_RATE_DATE IN DATE, -- Introduced for bug fix 2916848
4336 P_PERSON_BUSINESS_GROUP_ID IN NUMBER, -- Introduced for Bug fix 2935850
4337 P_RETURN_STATUS OUT NOCOPY VARCHAR2) IS
4338 l_msg_id number(9);
4339 l_unmatched_nve_txn_flag char(1);
4340
4341 BEGIN
4342
4343 IF (p_quantity < 0) THEN
4344 l_unmatched_nve_txn_flag := 'Y';
4345 END IF;
4346
4347 /* Intoduced the following for Bug 2935850 */
4348
4349 INSERT INTO PA_TRANSACTION_INTERFACE_ALL(
4350 TXN_INTERFACE_ID,
4351 TRANSACTION_SOURCE,
4352 BATCH_NAME,
4353 EXPENDITURE_ENDING_DATE,
4354 EMPLOYEE_NUMBER,
4355 ORGANIZATION_NAME,
4356 EXPENDITURE_ITEM_DATE,
4357 PROJECT_NUMBER,
4358 --TASK_NUMBER, -- Commented for Bug : 16591849
4359 TASK_ID, -- Introduced Bug : 16591849
4360 EXPENDITURE_TYPE,
4361 QUANTITY,
4362 RAW_COST,
4363 EXPENDITURE_COMMENT,
4364 TRANSACTION_STATUS_CODE,
4365 ORIG_TRANSACTION_REFERENCE,
4366 ORG_ID,
4367 DENOM_CURRENCY_CODE,
4368 DENOM_RAW_COST,
4369 ATTRIBUTE1,
4370 ATTRIBUTE2,
4371 ATTRIBUTE3,
4372 ATTRIBUTE4, -- Introduced attributes 4,5 for bug fix 2908859
4373 ATTRIBUTE5,
4374 ATTRIBUTE6,
4375 ATTRIBUTE7,
4376 ATTRIBUTE8,
4377 ATTRIBUTE9,
4378 ATTRIBUTE10,
4379 PERSON_BUSINESS_GROUP_ID,
4380 -- Introduced the following columns for bug fix 2916848
4381 ACCT_RATE_TYPE,
4382 ACCT_RATE_DATE,
4383 UNMATCHED_NEGATIVE_TXN_FLAG)
4384 VALUES(
4385 P_INTERFACE_ID,
4386 P_TRANSACTION_SOURCE,
4387 P_BATCH_NAME,
4388 P_EXPENDITURE_ENDING_DATE,
4389 P_EMPLOYEE_NUMBER,
4390 P_ORGANIZATION_NAME,
4391 P_EXPENDITURE_ITEM_DATE,
4392 P_PROJECT_NUMBER,
4393 -- P_TASK_NUMBER, -- Commented for Bug : 16591849
4394 P_TASK_ID, -- Introduced for Bug : 16591849
4395 P_EXPENDITURE_TYPE,
4396 P_QUANTITY,
4397 P_RAW_COST,
4398 P_EXPENDITURE_COMMENT,
4399 P_TRANSACTION_STATUS_CODE,
4400 P_ORIG_TRANSACTION_REFERENCE,
4401 P_ORG_ID,
4402 P_DENOM_CURRENCY_CODE,
4403 P_DENOM_RAW_COST,
4404 P_ATTRIBUTE1,
4405 P_ATTRIBUTE2,
4406 P_ATTRIBUTE3,
4407 P_ATTRIBUTE4, -- Introduced attributes 4,5 for bug fix 2908859
4408 P_ATTRIBUTE5,
4409 P_ATTRIBUTE6,
4410 P_ATTRIBUTE7,
4411 P_ATTRIBUTE8,
4412 P_ATTRIBUTE9,
4413 P_ATTRIBUTE10,
4414 P_PERSON_BUSINESS_GROUP_ID,
4415 -- Introduced the following columns for bug fix 2916848
4416 P_ACCT_RATE_TYPE,
4417 DECODE(p_acct_rate_type, NULL, NULL, P_ACCT_RATE_DATE),
4418 l_unmatched_nve_txn_flag);
4419
4420 p_return_status := fnd_api.g_ret_sts_success;
4421
4422
4423 EXCEPTION
4424 WHEN OTHERS THEN
4425 g_error_api_path := 'INSERT_INTO_PA_INTERFACE:'||g_error_api_path;
4426 fnd_msg_pub.add_exc_msg('PSP_SUM_ADJ','INSERT_INTO_PA_INTERFACE');
4427 p_return_status := fnd_api.g_ret_sts_unexp_error;
4428 END;
4429
4430 --------------------------------------- CLEANUP BATCH DETAILS --------------------------
4431 PROCEDURE cleanup_batch_details (p_payroll_control_id IN NUMBER,
4432 p_group_id IN NUMBER) IS /* added for bug 2133056 */
4433 l_batch_name varchar2(30);
4434 l_gms_phase varchar2(30);
4435 l_gl_phase varchar2(30);
4436 l_exp_group_name varchar2(20); /* 1685685 */
4437 l_user_je_source_name VARCHAR2(25); -- added for 2133056
4438 l_return_status VARCHAR2(5); -- Added for Bug 2133056
4439
4440 cursor adj_batch_cur (p_batch_name IN VARCHAR2) IS
4441 select orig_source_type,
4442 orig_line_id
4443 from psp_adjustment_lines pal,
4444 psp_payroll_controls ppc
4445 WHERE ppc.source_type ='A'
4446 AND ppc.batch_name = p_batch_name
4447 AND pal.payroll_control_id = ppc.payroll_control_id
4448 AND pal.reversal_entry_flag IS NULL
4449 UNION ALL -- added hist table for 2133056
4450 SELECT palh.orig_source_type,
4451 palh.orig_line_id
4452 FROM psp_adjustment_lines_history palh,
4453 psp_payroll_controls ppc
4454 WHERE palh.reversal_entry_flag IS NULL
4455 AND ppc.source_type ='A'
4456 AND ppc.batch_name = p_batch_name
4457 AND palh.payroll_control_id = ppc.payroll_control_id
4458 AND palh.reversal_entry_flag IS NULL;
4459
4460
4461
4462 -- Included the following cursors for bug fix 1765678
4463 CURSOR gl_gms_phase_batch_name_cur IS
4464 SELECT gl_phase, gms_phase, batch_name
4465 FROM psp_payroll_controls
4466 WHERE payroll_control_id = p_payroll_control_id;
4467
4468 -- Tuned following stmt 2133056
4469 CURSOR group_name_cur IS
4470 select expenditure_group
4471 from pa_expenditures_all
4472 where expenditure_id in
4473 (select expenditure_id
4474 from pa_transaction_interface_all
4475 where transaction_source in ('OLD', 'GOLD') and
4476 transaction_status_code = 'A' and
4477 -- batch_name = g_gms_batch_name and
4478 batch_name IN (SELECT gms_batch_name FROM psp_summary_lines WHERE payroll_control_id = p_payroll_control_id) and
4479 rownum = 1);
4480
4481 adj_batch_rec adj_batch_cur%ROWTYPE;
4482 BEGIN
4483 -- Replaced the earlier 'select stmt.' code with new 'cursor' code for bug fix 1765678
4484
4485 OPEN gl_gms_phase_batch_name_cur;
4486 FETCH gl_gms_phase_batch_name_cur INTO l_gl_phase, l_gms_phase, l_batch_name;
4487 IF (gl_gms_phase_batch_name_cur%NOTFOUND) THEN
4488 CLOSE gl_gms_phase_batch_name_cur;
4489 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4490 END IF;
4491 CLOSE gl_gms_phase_batch_name_cur;
4492
4493 --Added the following group_name_cur for bug 1685685.
4494 OPEN group_name_cur;
4495 FETCH group_name_cur into l_exp_group_name ;
4496 CLOSE group_name_cur;
4497
4498 if (l_gms_phase = 'GMS_Tie_Back') then
4499 -- Rearranged the Delete statments, so as to delete items first and tuned DEL exp stmt for 2133056
4500
4501 delete gms_award_distributions
4502 where document_type = 'EXP'
4503 and expenditure_item_id in
4504 (select expenditure_item_id
4505 from pa_expenditure_items_all
4506 where transaction_source = 'GOLD'
4507 and orig_transaction_reference in
4508 (select to_char(summary_line_id)
4509 from psp_summary_lines
4510 where payroll_control_id in
4511 (select payroll_control_id
4512 from psp_payroll_controls
4513 where batch_name = l_batch_name
4514 and source_type='A'))); ----- Added this delete for Bug 2133056
4515
4516
4517 delete from pa_expenditure_items_all
4518 where transaction_source in ('OLD','GOLD')
4519 and orig_transaction_reference in
4520 (select to_char(summary_line_id)
4521 from psp_summary_lines
4522 where payroll_control_id in
4523 (select payroll_control_id
4524 from psp_payroll_controls
4525 where batch_name = l_batch_name
4526 and source_type='A')); --Added for bug 1685685
4527
4528 /***********************************************************************
4529 BUG 2290051 : Commenting the following -purging of Interface lines
4530 delete gms_transaction_interface_all
4531 where transaction_source in ('GOLD') and
4532 batch_name = g_gms_batch_name and
4533 orig_transaction_reference in
4534 (select to_char(summary_line_id)
4535 from psp_summary_lines
4536 where payroll_control_id in
4537 (select payroll_control_id
4538 from psp_payroll_controls
4539 where batch_name = l_batch_name
4540 and source_type = 'A'));
4541 ********************************************************************************/
4542
4543 -- Bug 2133056.. Corrected not to delete non-orphan expenditures
4544 delete pa_expenditures_all EXP
4545 where EXP.expenditure_id in
4546 (select XFACE.expenditure_id
4547 from pa_transaction_interface_all XFACE
4548 where XFACE.transaction_source in('OLD','GOLD')
4549 and XFACE.orig_transaction_reference in
4550 (select to_char(PSL.summary_line_id)
4551 from psp_summary_lines PSL
4552 where PSL.payroll_control_id in
4553 (select PPC.payroll_control_id
4554 from psp_payroll_controls PPC
4555 where PPC.batch_name=l_batch_name
4556 and PPC.source_type='A')))
4557 and 0 = (select count(*)
4558 from pa_expenditure_items_all ITEMS
4559 where ITEMS.expenditure_id = EXP.expenditure_id);
4560
4561 /*****************************************************************************
4562 Commenting the following for the Bug 2290051
4563 delete pa_transaction_interface_all
4564 where transaction_source in ('OLD','GOLD') and
4565 batch_name = g_gms_batch_name and
4566 orig_transaction_reference in
4567 (select to_char(summary_line_id)
4568 from psp_summary_lines
4569 where payroll_control_id in
4570 (select payroll_control_id
4571 from psp_payroll_controls
4572 where batch_name = l_batch_name
4573 and source_type = 'A'));
4574 ******************************************************************************/
4575 -- 1662816 start
4576 delete pa_expenditure_groups_all
4577 where transaction_source in('OLD','GOLD')
4578 and (0) = (select count(*) from pa_expenditures_all where expenditure_group=l_exp_group_name)
4579 and expenditure_group = l_exp_group_name;
4580
4581
4582 end if;
4583
4584 if (l_gl_phase = 'GL_Tie_Back') then
4585 -- get the source name -- Added for 2133056
4586 get_gl_je_sources(l_user_je_source_name,
4587 l_return_status);
4588 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4589 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4590 END IF;
4591
4592 -- Used the group_id parameter, instead of deriving it from summary lines table: Bug 2133056
4593 -- Removed all other deletion of gl tables, because of bug 2133056 fix.
4594 delete from gl_interface
4595 where user_je_source_name = l_user_je_source_name
4596 and group_id = p_group_id ;
4597
4598 --- Added this stmt for Bug 2133056
4599 delete from gl_interface_control
4600 where je_source_name = l_user_je_source_name
4601 and group_id = p_group_id;
4602
4603 end if;
4604
4605 open adj_batch_cur (l_batch_name);
4606 loop
4607 fetch adj_batch_cur into adj_batch_rec;
4608 if adj_batch_cur%NOTFOUND then
4609 close adj_batch_cur;
4610 exit;
4611 end if;
4612 if (adj_batch_rec.orig_source_type = 'D') then
4613 update psp_distribution_lines_history
4614 set adjustment_batch_name = NULL
4615 where distribution_line_id = adj_batch_rec.orig_line_id;
4616 elsif adj_batch_rec.orig_source_type = 'P' then
4617 update psp_pre_gen_dist_lines_history
4618 set adjustment_batch_name = NULL
4619 where pre_gen_dist_line_id = adj_batch_rec.orig_line_id;
4620 elsif adj_batch_rec.orig_source_type = 'A' then
4621 update psp_adjustment_lines_history
4622 set adjustment_batch_name = NULL
4623 where adjustment_line_id = adj_batch_rec.orig_line_id;
4624 end if;
4625 end loop;
4626
4627 delete from psp_adjustment_lines_history
4628 where payroll_control_id in --- Corrected this statment 2133056
4629 ( SELECT ppc.payroll_control_id
4630 FROM psp_payroll_controls ppc
4631 WHERE ppc.batch_name = l_batch_name
4632 AND ppc.source_type = 'A');
4633
4634
4635 delete from psp_adjustment_lines
4636 where batch_name = l_batch_name;
4637
4638 delete from psp_summary_lines
4639 where payroll_control_id in (select payroll_control_id
4640 from psp_payroll_controls
4641 where batch_name = l_batch_name
4642 and source_type = 'A'); --- added this condn 2133056
4643
4644 delete from psp_payroll_controls
4645 where batch_name = l_batch_name and
4646 source_type = 'A'; --- added this condn 2133056
4647
4648 delete from psp_adjustment_control_table
4649 where adjustment_batch_name = l_batch_name;
4650
4651 EXCEPTION
4652 WHEN OTHERS THEN
4653 g_error_api_path := 'CLEANUP_BATCH_DETAILS:'||g_error_api_path;
4654 fnd_msg_pub.add_exc_msg('PSP_SUM_ADJ','CLEANUP_BATCH_DETAILS');
4655 END;
4656 -------------------------------------
4657 PROCEDURE check_interface_status(p_target_name IN VARCHAR2,
4658 p_adj_sum_batch_name IN VARCHAR2) IS
4659 CURSOR pc_batch_cur IS
4660 SELECT distinct batch_name
4661 FROM psp_payroll_controls
4662 WHERE source_type = 'A'
4663 AND adj_sum_batch_name = p_adj_sum_batch_name
4664 AND status_code = 'I';
4665
4666 CURSOR payroll_control_cur(p_batch_name IN VARCHAR2) IS
4667 SELECT payroll_control_id
4668 FROM psp_payroll_controls
4669 WHERE batch_name = p_batch_name
4670 AND status_code = 'I'
4671 AND decode(p_target_name, 'GL', gl_phase, gms_phase) = 'Submitted_Import_Request'
4672 AND source_type = 'A';
4673
4674 pc_batch_rec pc_batch_cur%ROWTYPE;
4675 payroll_control_rec payroll_control_cur%ROWTYPE;
4676 l_group_id number;
4677 l_gms_batch_name number;
4678 l_status_new number;
4679 l_status_p number;
4680 l_status_i number;
4681 l_no_complete number := 0;
4682 l_transaction_source VARCHAR2(40);
4683
4684 -- Included the following cursors here for accessing the local variables for bug fix 1765678
4685 CURSOR summary_group_cur IS
4686 SELECT MAX(group_id)
4687 FROM psp_summary_lines
4688 WHERE payroll_control_id = payroll_control_rec.payroll_control_id
4689 AND group_id IS NOT NULL;
4690
4691 CURSOR gl_interface_status_cur IS
4692 SELECT count(*)
4693 FROM gl_interface
4694 WHERE group_id = l_group_id
4695 AND user_je_source_name = 'OLD'
4696 AND status = 'NEW';
4697
4698 CURSOR gms_batch_name_cur IS
4699 SELECT MAX(gms_batch_name)
4700 FROM psp_summary_lines
4701 WHERE payroll_control_id = payroll_control_rec.payroll_control_id
4702 AND gms_batch_name IS NOT NULL;
4703
4704 CURSOR pa_txn_int_status_p_cur IS
4705 SELECT count(*)
4706 FROM pa_transaction_interface
4707 WHERE batch_name = TO_CHAR(l_gms_batch_name)
4708 AND transaction_status_code = 'P'
4709 AND transaction_source in ('OLD','GOLD'); --- Added condn for Bug 2133056
4710
4711 CURSOR pa_txn_int_status_i_cur IS
4712 SELECT count(*)
4713 FROM pa_transaction_interface_all
4714 WHERE batch_name = TO_CHAR(l_gms_batch_name)
4715 AND transaction_source in ('OLD','GOLD')
4716 AND transaction_status_code = 'I'; --- Added condn for Bug 2133056
4717
4718 begin
4719 open pc_batch_cur;
4720 loop
4721 fetch pc_batch_cur into pc_batch_rec;
4722 if pc_batch_cur%NOTFOUND then
4723 close pc_batch_cur;
4724 exit;
4725 end if;
4726
4727 open payroll_control_cur(pc_batch_rec.batch_name);
4728 loop
4729 fetch payroll_control_cur into payroll_control_rec;
4730 if payroll_control_cur%NOTFOUND then
4731 close payroll_control_cur;
4732 exit;
4733 end if;
4734
4735 if (p_target_name = 'GL') then
4736 begin
4737 -- Replaced the earlier 'select stmt.' code with new 'cursor' code for bug fix 1765678
4738
4739 OPEN summary_group_cur;
4740 FETCH summary_group_cur INTO l_group_id;
4741 IF (summary_group_cur%NOTFOUND) THEN
4742 CLOSE summary_group_cur;
4743 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4744 END IF;
4745 CLOSE summary_group_cur;
4746
4747 OPEN gl_interface_status_cur;
4748 FETCH gl_interface_status_cur INTO l_status_new;
4749 IF (gl_interface_status_cur%NOTFOUND) THEN
4750 CLOSE gl_interface_status_cur;
4751 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4752 END IF;
4753 CLOSE gl_interface_status_cur;
4754
4755 if l_status_new > 0 then
4756 update psp_payroll_controls
4757 set gl_phase = 'Summarize_GL_Lines'
4758 where gl_phase = 'Submitted_Import_Request'
4759 and payroll_control_id = payroll_control_rec.payroll_control_id;
4760
4761 delete from gl_interface
4762 where group_id = l_group_id
4763 and user_je_source_name = 'OLD';
4764
4765 delete from gl_interface_control
4766 where group_id = l_group_id
4767 and je_source_name = 'OLD';
4768 end if;
4769
4770 exception
4771 when NO_DATA_FOUND then
4772 NULL;
4773 end;
4774 elsif (p_target_name = 'GMS') then
4775 begin
4776 -- Replaced the earlier 'select stmt.' code with new 'cursor' code for bug fix 1765678
4777
4778 OPEN gms_batch_name_cur;
4779 FETCH gms_batch_name_cur INTO l_gms_batch_name;
4780 IF (gms_batch_name_cur%NOTFOUND) THEN
4781 CLOSE gms_batch_name_cur;
4782 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4783 END IF;
4784 CLOSE gms_batch_name_cur;
4785
4786 OPEN pa_txn_int_status_p_cur;
4787 FETCH pa_txn_int_status_p_cur INTO l_status_p;
4788 IF (pa_txn_int_status_p_cur%NOTFOUND) THEN
4789 CLOSE pa_txn_int_status_p_cur;
4790 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4791 END IF;
4792 CLOSE pa_txn_int_status_p_cur;
4793
4794 if (l_status_p > 0) then
4795 update psp_payroll_controls
4796 set gms_phase = NULL
4797 where payroll_control_id = payroll_control_rec.payroll_control_id
4798 and gms_phase = 'Submitted_Import_Request';
4799
4800 delete from pa_transaction_interface_all
4801 where batch_name = to_char(l_gms_batch_name)
4802 and transaction_source in ('GOLD', 'OLD');
4803
4804 delete from gms_transaction_interface_all
4805 where batch_name = to_char(l_gms_batch_name)
4806 and transaction_source = 'GOLD';
4807
4808 delete from psp_summary_lines
4809 where payroll_control_id = payroll_control_rec.payroll_control_id
4810 and gms_batch_name = l_gms_batch_name;
4811
4812
4813 end if;
4814 exception
4815 when NO_DATA_FOUND then
4816 null;
4817 end;
4818 -- Replaced the earlier 'select stmt.' code with new 'cursor' code for bug fix 1765678
4819
4820 OPEN pa_txn_int_status_i_cur;
4821 FETCH pa_txn_int_status_i_cur INTO l_status_i;
4822 IF (pa_txn_int_status_i_cur%NOTFOUND) THEN
4823 CLOSE pa_txn_int_status_i_cur;
4824 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4825 END IF;
4826 CLOSE pa_txn_int_status_i_cur;
4827
4828 if l_status_i > 0 then
4829 l_no_complete := l_no_complete + 1;
4830 fnd_message.set_name('PSP','PSP_PRC_DID_NOT_COMPLETE');
4831 fnd_message.set_token('PAYROLL_CONTROL_ID',payroll_control_rec.payroll_control_id);
4832 fnd_message.set_token('GMS_BATCH_NAME', l_gms_batch_name);
4833 fnd_msg_pub.add;
4834 end if;
4835
4836 end if;
4837 end loop;
4838 end loop;
4839
4840 if (l_no_complete > 0) then
4841 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4842 end if;
4843 exception
4844 when others then
4845 fnd_msg_pub.add_exc_msg('PSP_ST_ADJ', 'CHECK_INTERFACE_STATUS');
4846 end CHECK_INTERFACE_STATUS;
4847 -----------------------------------Get the Batch Details --------------------------------
4848 PROCEDURE get_the_batch_details(p_batch_name IN VARCHAR2,
4849 p_return_status OUT NOCOPY VARCHAR2) IS
4850
4851 l_adjustment_batch_name VARCHAR2(30);
4852 l_person_id NUMBER;
4853 l_assignment_id NUMBER;
4854 l_assignment_number VARCHAR2(30);
4855 l_element_type_id NUMBER;
4856 -- For bug fix 1765678, increased the size of element name from 30 to 80.
4857 -- l_element_name VARCHAR2(80); commented for 2634557 DA Multiple element Enh
4858 l_distribution_start_date DATE;
4859 l_distribution_end_date DATE;
4860 l_person_name VARCHAR2(80);
4861 l_error VARCHAR2(200);
4862 l_currency_code psp_payroll_controls.currency_code%TYPE;
4863
4864 -- Included the following cursors here for accessing local variables for bug fix 1765678
4865 CURSOR adjustment_control_cur IS
4866 SELECT adjustment_batch_name, person_id, assignment_id, element_type_id,
4867 distribution_start_date, distribution_end_date,
4868 currency_code -- Introduced for bug fix 2916848
4869 FROM psp_adjustment_control_table
4870 WHERE adjustment_batch_name = p_batch_name;
4871
4872 CURSOR person_name_cur IS
4873 SELECT substr(full_name, 1, 80)
4874 FROM per_people_f ppf1
4875 WHERE person_id = l_person_id
4876 AND trunc(sysdate) BETWEEN effective_start_date and effective_end_date ;
4877
4878
4879 CURSOR assignment_number_cur IS
4880 SELECT assignment_number
4881 FROM per_all_assignments_f paf1
4882 WHERE assignment_id = l_assignment_id
4883 AND effective_start_date = (SELECT MAX(effective_start_date)
4884 FROM per_all_assignments_f paf2
4885 WHERE paf2.assignment_id = l_assignment_id
4886 AND paf2.effective_start_date < trunc(sysdate));
4887
4888 CURSOR element_name_cur IS
4889 SELECT element_name
4890 FROM pay_element_types_f pet1
4891 WHERE element_type_id = l_element_type_id
4892 AND effective_start_date = (SELECT MAX(effective_start_date)
4893 FROM pay_element_types_f pet2
4894 WHERE pet2.element_type_id = l_element_type_id
4895 AND pet2.effective_start_date < trunc(sysdate));
4896
4897 begin
4898 -- Replaced the earlier 'select stmt.' code with new 'cursor' code for bug fix 1765678
4899
4900 OPEN adjustment_control_cur;
4901 FETCH adjustment_control_cur INTO l_adjustment_batch_name, l_person_id, l_assignment_id, l_element_type_id,
4902 l_distribution_start_date, l_distribution_end_date,
4903 l_currency_code; -- Introduced currency for bug 2916848
4904 IF (adjustment_control_cur%NOTFOUND) THEN
4905 CLOSE adjustment_control_cur;
4906 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4907 END IF;
4908 CLOSE adjustment_control_cur;
4909
4910 l_error := 'Batch : ' || p_batch_name || ' Person Id ' || l_person_id;
4911
4912 -- for bug fix 1765678, truncated person full name to 80 characters
4913 -- Replaced the earlier 'select stmt.' code with new 'cursor' code for bug fix 1765678
4914
4915 OPEN person_name_cur;
4916 FETCH person_name_cur INTO l_person_name;
4917 IF (person_name_cur%NOTFOUND) THEN
4918 CLOSE person_name_cur;
4919 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4920 END IF;
4921 CLOSE person_name_cur;
4922
4923 l_error := 'Batch : ' || p_batch_name || ' Assign ID ' || l_assignment_id;
4924
4925 -- Replaced the earlier 'select stmt.' code with new 'cursor' code for bug fix 1765678
4926
4927 OPEN assignment_number_cur;
4928 FETCH assignment_number_cur INTO l_assignment_number;
4929 IF (assignment_number_cur%NOTFOUND) THEN
4930 CLOSE assignment_number_cur;
4931 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4932 END IF;
4933 CLOSE assignment_number_cur;
4934
4935 l_error := 'Batch : ' || p_batch_name || ' Elem Type ' || l_element_type_id;
4936
4937 -- Replaced the earlier 'select stmt.' code with new 'cursor' code for bug fix 1765678
4938 /* commented for 2634557 DA Multiple element Enh
4939 OPEN element_name_cur;
4940 FETCH element_name_cur INTO l_element_name;
4941 IF (element_name_cur%NOTFOUND) THEN
4942 CLOSE element_name_cur;
4943 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4944 END IF;
4945 CLOSE element_name_cur;
4946 */
4947
4948 l_error := 'Batch : ' || p_batch_name || ' Setting Tokens ' ;
4949
4950 fnd_message.set_token('BATCH_NAME',l_adjustment_batch_name);
4951 fnd_message.set_token('PERSON_NAME',l_person_name);
4952 fnd_message.set_token('ASSIGNMENT_NUMBER',l_assignment_number);
4953 --- fnd_message.set_token('ELEMENT_TYPE',l_element_name); commented for 2634557 DA Multiple element Enh
4954 fnd_message.set_token('DISTRIBUTION_START_DATE', to_char(l_distribution_start_date));
4955 fnd_message.set_token('DISTRIBUTION_END_DATE', to_char(l_distribution_end_date));
4956 fnd_message.set_token('CURRENCY_CODE', l_currency_code); -- Introduced for bug fix 2916848
4957 fnd_msg_pub.add;
4958
4959 p_return_status := fnd_api.g_ret_sts_success;
4960
4961 exception
4962 when no_data_found then
4963 -- raise_application_error(-20001, l_error || ' No Data Found');
4964 fnd_message.set_token('BATCH_NAME', l_error || ' No data found');
4965 -- Included the following line for bug fix 1765678
4966 fnd_msg_pub.add;
4967 p_return_status := fnd_api.g_ret_sts_unexp_error;
4968 when too_many_rows then
4969 -- raise_application_error(-20001, l_error || ' ' || sqlerrm);
4970 fnd_message.set_token('BATCH_NAME', l_error || ' Too many rows');
4971 -- Included the following line for bug fix 1765678
4972 fnd_msg_pub.add;
4973 p_return_status := fnd_api.g_ret_sts_unexp_error;
4974 when others then
4975 --Bug :1776606 : Building error stack
4976 fnd_message.set_token('BATCH_NAME',l_error||'Unexpected Oracle error occured ORA -'||sqlcode);
4977 fnd_msg_pub.add;
4978 fnd_msg_pub.add_exc_msg('PSP_SUM_ADJ','GET_THE_BATCH_DETAILS');
4979 -- Included the following line for bug fix 1765678
4980 p_return_status := fnd_api.g_ret_sts_unexp_error;
4981 raise;
4982 -- Bug 1776606 : Commented the following line
4983 -- raise_application_error(sqlcode, l_error || ' ' || sqlerrm);
4984 end get_the_batch_details;
4985 ------------------ INSERT INTO PSP_STOUT -----------------------------------------------
4986 /* DEBUGGIN PROCEDURE
4987 PROCEDURE insert_into_psp_stout(
4988 P_MSG IN VARCHAR2) IS
4989 l_msg_id number(9);
4990 BEGIN
4991 SELECT PSP_STOUT_S.NEXTVAL
4992 INTO l_msg_id
4993 FROM DUAL;
4994
4995 INSERT INTO PSP_STOUT(
4996 MSG_ID,
4997 MSG)
4998 VALUES(
4999 l_msg_id,
5000 P_MSG);
5001 END;
5002 */
5003
5004 END PSP_SUM_ADJ;