DBA Data[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;