[Home] [Help]
PACKAGE BODY: APPS.IGIPMGLT
Source
4 g_date CONSTANT DATE := SYSDATE;
1 PACKAGE BODY IGIPMGLT AS
2 -- $Header: igipmgtb.pls 115.10 2003/12/01 14:57:42 sdixit ship $
3
5 g_user_id CONSTANT NUMBER := fnd_global.user_id;
6 --bug 3199481: following variables added for fnd logging changes: sdixit
7 l_debug_level number := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
8 l_state_level number := FND_LOG.LEVEL_STATEMENT;
9 l_proc_level number := FND_LOG.LEVEL_PROCEDURE;
10 l_event_level number := FND_LOG.LEVEL_EVENT;
11 l_excep_level number := FND_LOG.LEVEL_EXCEPTION;
12 l_error_level number := FND_LOG.LEVEL_ERROR;
13 l_unexp_level number := FND_LOG.LEVEL_UNEXPECTED;
14
15 PROCEDURE WriteToLog ( pp_mesg in varchar2 ) IS
16 BEGIN
17 FND_FILE.put_line ( FND_FILE.log, pp_mesg ) ;
18 END;
19
20 PROCEDURE InitGLTransfer ( p_glint_control IN OUT NOCOPY GLINT_CONTROL ) IS
21 BEGIN
22 SELECT GL_JOURNAL_IMPORT_S.nextval
23 , sp.set_of_books_id
24 , GL_INTERFACE_CONTROL_S.nextval
25 , 'S'
26 INTO p_glint_control.interface_run_id
27 , p_glint_control.set_of_books_id
28 , p_glint_control.group_id
29 , p_glint_control.status
30 FROM ap_system_parameters sp
31 ;
32 select je_source_name
33 into p_glint_control.je_source_name
34 from igi_mpp_setup
35 ;
36
37 END;
38 PROCEDURE InsertControlRec ( p_glint_control in GLINT_CONTROL )
39 IS
40 BEGIN
41
42 INSERT INTO gl_interface_control
43 ( je_source_name
44 , status
45 , interface_run_id
46 , group_id
47 , set_of_books_id)
48 VALUES
49 ( p_glint_control.je_source_name
50 , p_glint_control.status
51 , p_glint_control.interface_run_id
52 , p_glint_control.group_id
53 , p_glint_control.set_of_books_id
54 );
55
56
57 END;
58
59 PROCEDURE InsertInterfaceRec ( l_glint IN GLINT ) IS
60 BEGIN
61 --bug 3199481 fnd logging changes: sdixit: start block
62 IF (l_state_level >= l_debug_level ) THEN
63 FND_LOG.STRING (l_state_level , 'igi.pls.igipmgtb.IGIPMGLT.InsertInterfaceRec',
64 '>> >> **** Accounting date '||l_glint.accounting_date);
65 END IF;
66 --bug 3199481 fnd logging changes: sdixit: end block
67 INSERT INTO GL_INTERFACE
68 (
69 status -- not null
70 ,set_of_books_id -- not null
71 ,accounting_date -- not null
72 ,currency_code -- not null
73 ,date_created -- not null
74 ,created_by -- not null
75 ,actual_flag -- not null
76 ,user_je_category_name -- not null
77 ,user_je_source_name -- not null
78 ,currency_conversion_date
79 ,encumbrance_type_id
80 ,budget_version_id
81 ,user_currency_conversion_type
82 ,currency_conversion_rate
83 ,entered_dr
84 ,entered_cr
85 ,accounted_dr
86 ,accounted_cr
87 ,transaction_date
88 ,reference1
89 ,reference2
90 ,reference3
91 ,reference4
92 ,period_name
93 ,chart_of_accounts_id
94 ,functional_currency_code
95 ,code_combination_id
96 ,group_id
97 ) VALUES
98 (
99 'NEW' -- not null
100 ,l_glint.set_of_books_id -- not null
101 ,l_glint.accounting_date -- not null
102 ,l_glint.currency_code -- not null
103 ,l_glint.date_created -- not null
104 ,l_glint.created_by -- not null
105 ,l_glint.actual_flag -- not null
106 ,l_glint.user_je_category_name -- not null
107 ,l_glint.user_je_source_name -- not null
108 ,l_glint.currency_conversion_date
109 ,l_glint.encumbrance_type_id
110 ,l_glint.budget_version_id
111 ,l_glint.user_currency_conversion_type
112 ,l_glint.currency_conversion_rate
113 ,l_glint.entered_dr
114 ,l_glint.entered_cr
115 ,l_glint.accounted_dr
116 ,l_glint.accounted_cr
117 ,l_glint.transaction_date
118 ,l_glint.reference1
119 ,l_glint.reference2
120 ,l_glint.reference3
121 ,l_glint.reference4
122 ,l_glint.period_name
123 ,l_glint.chart_of_accounts_id
124 ,l_glint.functional_currency_code
125 ,l_glint.code_combination_id
126 ,l_glint.group_id
127 )
128 ;
129 END;
130
131
132 FUNCTION TxfrToGL ( p_period_name in varchar2
133 , p_sob_id in number
134 , p_glint_control in GLINT_CONTROL
135 )
136 RETURN NUMBER
137 IS
138
139 CURSOR c_subledger IS
140 SELECT slgr.*
141 FROM igi_mpp_subledger slgr
142 WHERE slgr.period_name = p_period_name
143 AND slgr.set_of_books_id = p_sob_id
144 AND slgr.expense_recognized_flag = 'Y'
145 AND slgr.gl_posted_flag = 'N'
146 ;
147
148 l_glint_control GLINT_CONTROL := p_glint_control;
149 l_glint GLINT ;
150
154 l_user_je_category_name
151 PROCEDURE TransferInfo ( p_slgr c_subledger%ROWTYPE
152 , p_glint in GLINT ) IS
153 l_glint GLINT := p_glint;
155 gl_je_categories.user_je_category_name%TYPE;
156 l_user_je_source_name
157 gl_je_sources.user_je_source_name%TYPE;
158
159 BEGIN
160 SELECT user_je_source_name
161 INTO l_user_je_source_name
162 FROM gl_je_sources
163 WHERE je_source_name = p_slgr.je_source_name
164 ;
165 SELECT user_je_category_name
166 INTO l_user_je_category_name
167 FROM gl_je_categories
168 WHERE je_category_name = p_slgr.je_category_name
169 ;
170
171 SELECT p_slgr.invoice_id
172 , p_slgr.distribution_line_number
176 , l_user_je_source_name
173 , p_slgr.subledger_entry_id
174 , p_slgr.currency_code
175 , p_slgr.actual_flag
177 , l_user_je_category_name
178 , p_slgr.set_of_books_id
179 , p_slgr.gl_date
180 , p_slgr.code_combination_id
181 , p_slgr.accounted_dr
182 , p_slgr.accounted_cr
183 , p_slgr.entered_dr
184 , p_slgr.entered_cr
185 , p_slgr.currency_conversion_date
186 , p_slgr.user_currency_conversion_type
187 , p_slgr.currency_conversion_rate
188 , p_slgr.period_name
189 , p_slgr.chart_of_accounts_id
190 , p_slgr.functional_currency_code
191 , p_slgr.reference1
192 , p_slgr.reference2
193 , p_slgr.reference3
194 , g_date
195 , g_user_id
196 INTO
197 l_glint.reference4
198 , l_glint.reference5
199 , l_glint.reference6
200 , l_glint.currency_code
201 , l_glint.actual_flag
202 , l_glint.user_je_source_name
203 , l_glint.user_je_category_name
204 , l_glint.set_of_books_id
205 , l_glint.accounting_date
206 , l_glint.code_combination_id
207 , l_glint.accounted_dr
208 , l_glint.accounted_cr
209 , l_glint.entered_dr
210 , l_glint.entered_cr
211 , l_glint.currency_conversion_date
212 , l_glint.user_currency_conversion_type
213 , l_glint.currency_conversion_rate
214 , l_glint.period_name
215 , l_glint.chart_of_accounts_id
216 , l_glint.functional_currency_code
217 , l_glint.reference1
218 , l_glint.reference2
219 , l_glint.reference3
220 , l_glint.date_created
221 , l_glint.created_by
222 FROM SYS.DUAL
223 ;
224
225 InsertInterfaceRec ( l_glint ) ;
226 --bug 3199481 fnd logging changes: sdixit: start block
227 IF (l_state_level >= l_debug_level ) THEN
228 FND_LOG.STRING (l_state_level ,'igi.pls.igipmgtb.IGIPMGLT.TransferInfo',
229 '>> >> >> Populated Interface control table... ');
230 END IF;
231 --bug 3199481 fnd logging changes: sdixit: end block
232
233 END TransferInfo;
234
235 BEGIN
236
237 l_glint.group_id := l_glint_control.group_id;
238 l_glint.status := 'NEW';
239 l_glint.set_of_books_id := l_glint_control.set_of_books_id;
240
241
242
243 FOR l_slgr IN C_subledger LOOP
244 TransferInfo ( l_slgr, l_glint );
245 --bug 3199481 fnd logging changes: sdixit: start block
246 IF (l_state_level >= l_debug_level ) THEN
247 FND_LOG.STRING (l_state_level , 'igi.pls.igipmgtb.IGIPMGLT.InsertInterfaceRec',
248 '>> >> >> Built the interface info... ' );
249 END IF;
250 --bug 3199481 fnd logging changes: sdixit: end block
251
252 UPDATE igi_mpp_subledger
253 SET gl_posted_flag = 'Y'
254 , date_created_in_gl = g_date
255 WHERE subledger_entry_id = l_slgr.subledger_entry_id
256 AND nvl(gl_posted_flag,'N') = 'N'
257 AND expense_recognized_flag = 'Y'
258 ;
259
260 UPDATE igi_mpp_ap_invoice_dists_det
261 SET gl_posted_flag = 'Y'
262 , gl_posted_date = g_date
263 WHERE invoice_id = l_slgr.invoice_id
264 AND distribution_line_number =
265 l_slgr.distribution_line_number
266 AND period_name = l_slgr.period_name
267 AND NVL(gl_posted_flag,'N') = 'N'
268 AND expense_recognized_flag = 'Y'
269 and EXISTS
270 ( SELECT 'x'
271 FROM igi_mpp_subledger
272 WHERE gl_posted_flag = 'Y'
273 AND expense_recognized_flag = 'Y'
274 AND invoice_id = l_slgr.invoice_id
275 AND distribution_line_number =
276 l_slgr.distribution_line_number
277 AND period_name = l_slgr.period_name
278 )
279 ;
280
281 --bug 3199481 fnd logging changes: sdixit: start block
282 IF (l_state_level >= l_debug_level ) THEN
283 FND_LOG.STRING (l_state_level , 'igi.pls.igipmgtb.IGIPMGLT.InsertInterfaceRec',
284 '>> >> >> Marked as posted... ' );
285 END IF;
286 --bug 3199481 fnd logging changes: sdixit: end block
287
288 END LOOP;
289
290 return( l_glint_control.interface_run_id );
291
292 END;
293
294 PROCEDURE SubLedgerTxfrtoGL ( errbuf out NOCOPY varchar2
295 , retcode out NOCOPY number
296 , p_set_of_books_id in number
297 , p_start_period_eff_num in number
298 , p_end_period_eff_num in number
299 , p_run_gl_import in varchar2
300 )
301 IS
302
303 l_continue BOOLEAN := TRUE;
304 l_request_id NUMBER(15) := NULL;
305 l_interface_run_id number(15) := NULL;
306 l_glint_control GLINT_CONTROL;
307
308 CURSOR c_start_date IS
309 SELECT start_date
310 from gl_period_statuses
311 where set_of_books_id = p_set_of_books_id
312 and application_id = 200
313 and adjustment_period_flag = 'N'
314 and effective_period_num = p_start_period_eff_num
315 ;
316 CURSOR c_end_date IS
317 SELECT end_date
318 from gl_period_statuses
319 where set_of_books_id = p_set_of_books_id
320 and application_id = 200
321 and adjustment_period_flag = 'N'
322 and effective_period_num = p_end_period_eff_num
323 ;
324
325 CURSOR c_periods IS
326 SELECT period_name, set_of_books_id
327 from gl_period_statuses
328 where set_of_books_id = p_set_of_books_id
329 and application_id = 200
330 and adjustment_period_flag = 'N'
331 and effective_period_num between p_start_period_eff_num and
332 p_end_period_eff_num
333 order by effective_period_num
334 ;
335
336 CURSOR c_currency ( cp_period_name in varchar2
337 , cp_sob_id in number ) IS
338 SELECT DISTINCT currency_code
339 FROM igi_mpp_subledger
340 WHERE period_name = cp_period_name
341 and set_of_books_id = cp_sob_id
342 ;
343
344 CURSOR c_verify ( cp_period_name in varchar2
345 , cp_sob_id in number
346 , cp_currency_code in varchar2
347 ) IS
348 SELECT currency_code
349 , SUM( nvl( accounted_dr, 0) ) sum_accounted_dr
350 , SUM( nvl( accounted_cr, 0) ) sum_accounted_cr
351 , SUM( nvl( entered_dr, 0) ) sum_entered_dr
352 , SUM( nvl( entered_cr, 0) ) sum_entered_cr
353 FROM igi_mpp_subledger
354 WHERE period_name = cp_period_name
355 AND expense_recognized_flag = 'Y'
356 AND gl_posted_flag = 'N'
357 AND seT_of_books_id = cp_sob_id
358 AND currency_code = cp_currency_code
359 GROUP BY currency_code
360 ;
361 FUNCTION CheckTotals ( cp_period_name in varchar2
362 , cp_sob_id in number
363 , cp_currency_code in varchar2
364 )
365 RETURN BOOLEAN
366 IS
367 BEGIN
368 FOR l_verify in c_verify ( cp_period_name, cp_sob_id, cp_currency_code )
369 LOOP
370 IF l_verify.sum_accounted_dr = l_verify.sum_accounted_cr THEN
371 return TRUE;
372 ELSIF l_verify.sum_entered_dr = l_verify.sum_entered_cr THEN
373 return TRUE;
374 else
375 return FALSE;
376 end if;
377 END LOOP;
378 return FALSE;
379 END CheckTotals;
380
381
382 BEGIN
383
384 --bug 3199481 fnd logging changes: sdixit: start block
385 IF (l_state_level >= l_debug_level ) THEN
386 FND_LOG.STRING (l_state_level , 'igi.pls.igipmgtb.IGIPMGLT.SubLedgerTrfxtoGl',
387 'BEGIN Transfer to GL.');
388 END IF;
389 InitGLTransfer ( l_glint_control ) ;
390 IF (l_state_level >= l_debug_level ) THEN
391 FND_LOG.STRING (l_state_level , 'igi.pls.igipmgtb.IGIPMGLT.SubLedgerTrfxtoGl',
392 '>> >> Initalized interface control info... ' );
393 END IF;
397 '>> >> Populated Interface control table... ' );
394 InsertControlRec ( l_glint_control ) ;
395 IF (l_state_level >= l_debug_level ) THEN
396 FND_LOG.STRING (l_state_level , 'igi.pls.igipmgtb.IGIPMGLT.SubLedgertrfxtoGl',
398 END IF;
399 --bug 3199481 fnd logging changes: sdixit: end block
400
401 FOR l_period in c_periods LOOP
402
403 --bug 3199481 fnd logging changes: sdixit: start block
404 IF (l_state_level >= l_debug_level ) THEN
405 FND_LOG.STRING (l_state_level , 'igi.pls.igipmgtb.IGIPMGLT.SubLedgerTrfxtoGl',
406 '>> Period '|| l_period.period_name );
407 FND_LOG.STRING (l_state_level , 'igi.pls.igipmgtb.IGIPMGLT.SubLedgerTrfxtoGl',
408 '>> SOB ID '|| l_period.set_of_books_id );
409 END IF;
410 --bug 3199481 fnd logging changes: sdixit: end block
411
412
413 l_continue := TRUE;
414
415 FOR l_currency in c_currency ( l_period.period_name
416 , l_period.set_of_books_id
417 )
418 LOOP
419 --bug 3199481 fnd logging changes: sdixit: start block
420 IF (l_state_level >= l_debug_level ) THEN
421 FND_LOG.STRING (l_state_level , 'igi.pls.igipmgtb.IGIPMGLT.SubLedgerTrfxtoGl',
422 '>> >> Currency '|| l_currency.currency_code );
423 END IF;
424 --bug 3199481 fnd logging changes: sdixit: end block
425
426 IF CheckTotals ( l_period.period_name, l_period.set_of_books_id
427 , l_currency.currency_code )
428 THEN
429
430 NULL;
431 ELSE
432 --bug 3199481 fnd logging changes: sdixit: start block
433 IF (l_state_level >= l_debug_level ) THEN
434 FND_LOG.STRING (l_state_level , 'igi.pls.igipmgtb.IGIPMGLT.InsertInterfaceRec',
435 '>> >> Totals unbalanced for Currency '|| l_currency.currency_code );
436 END IF;
437 --bug 3199481 fnd logging changes: sdixit: end block
438 l_continue := FALSE;
439 END IF;
440
441 END LOOP;
442
443 IF l_continue THEN
444 l_interface_run_id :=
445 TxfrtoGl ( l_period.period_name, l_period.set_of_books_id
446 , l_glint_control );
447 ELSE
448 rollback;
449 errbuf := 'Unbalanced entries found.';
450 retcode := 2;
451 END IF;
452
453 END LOOP;
454
455 if p_run_gl_import = 'Y' and l_interface_run_id <> 0 then
456
457 for l_start_date in c_start_date loop
458 for l_end_date in c_end_date loop
459 l_request_id :=
460 FND_REQUEST.SUBMIT_REQUEST
461 ( 'SQLGL'
462 , 'GLLEZL'
463 , null
464 , null
465 , FALSE
466 , l_interface_run_id
467 , p_set_of_books_id
468 , 'N' -- post_errors_to_suspense
469 , to_char(l_start_date.start_date,'YYYY/MM/DD')
470 , to_char(l_end_date.end_date,'YYYY/MM/DD')
471 , 'N'
472 , 'N' -- descriptive_flexfield_flag
473 );
474
475 end loop;
476 end loop;
477
478 end if;
479 commit;
480 --bug 3199481 fnd logging changes: sdixit: start block
481 WriteToLog ( 'END (Normal) Transfer to GL.');
482 IF (l_state_level >= l_debug_level ) THEN
483 FND_LOG.STRING (l_state_level , 'igi.pls.igipmgtb.IGIPMGLT.InsertInterfaceRec',
484 'END (Normal) Transfer to GL.');
485 END IF;
486 --bug 3199481 fnd logging changes: sdixit: end block
487
488 errbuf := 'Normal Completion';
489 retcode := 0;
490
491 EXCEPTION WHEN OTHERS THEN
492 --bug 3199481 fnd logging changes: sdixit: start block
493 FND_MESSAGE.SET_NAME('IGI', 'IGI_LOGGING_USER_ERROR'); -- Seeded Message
494 retcode := 2;
495 errbuf := Fnd_message.get;
496
497 IF ( l_unexp_level >= l_debug_level ) THEN
498
499 FND_MESSAGE.SET_NAME('IGI','IGI_LOGGING_UNEXP_ERROR');
500 FND_MESSAGE.SET_TOKEN('CODE',SQLCODE);
501 FND_MESSAGE.SET_TOKEN('MSG', SQLERRM);
502 FND_LOG.MESSAGE ( l_unexp_level,'igi.pls.igipmgtb.IGIPMGLT.InsertInterfaceRec',TRUE);
503 END IF;
504 --bug 3199481 fnd logging changes: sdixit: end block
505 END;
506 END IGIPMGLT ;