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