[Home] [Help]
PACKAGE BODY: APPS.IGIRCBID
Source
1 PACKAGE BODY IGIRCBID AS
2 -- $Header: igircidb.pls 120.4.12000000.2 2007/11/08 17:42:59 sguduru ship $
3
4 --following variables added for bug 3199481: fnd logging changes: sdixit
5 l_debug_level number;
6 l_state_level number;
7 l_proc_level number;
8 l_event_level number;
9 l_excep_level number;
10 l_error_level number;
11 l_unexp_level number;
12
13 PROCEDURE WritetoLog (pp_line in varchar2) IS
14 l_debug_mode BOOLEAN := FALSE;
15 BEGIN
16 IF pp_line IS NULL THEN
17 return;
18 END IF;
19 IF l_debug_mode THEN
20 fnd_file.put_line( FND_FILE.log, 'IGIRCBID '||pp_line );
21 ELSE
22 null;
23 END IF;
24 END;
25
26 PROCEDURE PostNonDistApplications( p_Prepare IN ParametersType ) IS
27
28 CURSOR CRa IS
29 SELECT DISTINCT
30 ra.receivable_application_id
31 FROM ar_receivable_applications ra,
32 ar_cash_receipts cr
33 WHERE ra.gl_date >= p_Prepare.GlDateFrom
34 AND ra.gl_date <= p_Prepare.GlDateTo
35 AND nvl(ra.postable,'Y') = 'Y'
36 AND nvl(ra.confirmed_flag,'Y') = 'Y'
37 AND ra.status <> 'APP' -- Bug 3519052
38 AND ra.application_type||'' = 'CASH'
39 AND cr.cash_receipt_id = ra.cash_receipt_id
40 AND ra.set_of_books_id = p_Prepare.SetOfBooksId
41 ;
42 --
43 l_Count NUMBER :=0;
44
45 FUNCTION IsRecordCopiedBefore ( fp_app_id in number) return boolean
46 IS
47 CURSOR c_app is
48 select 'x'
49 from igi_ar_rec_applications_all igi
50 where igi.receivable_application_id = fp_app_id
51 ;
52 BEGIN
53 FOR l_app IN c_app LOOP
54 return TRUE;
55 END LOOP;
56 return FALSE;
57 EXCEPTION WHEN OTHERS THEN
58 return FALSE;
59 END IsRecordCopiedBefore;
60
61
62 BEGIN
63
64 IF (l_proc_level >= l_debug_level ) THEN
65 FND_LOG.STRING (l_proc_level , 'igi.plsql.igircidb.PostNonDistApplications',
66 ' Begin PostNonDistApplications ');
67 END IF;
68
69 FOR LRA in CRa LOOP
70 IF (NOT IsRecordCopiedBefore( LRA.receivable_application_id ))
71 THEN
72 --
73 INSERT INTO igi_ar_rec_applications_all
74 ( receivable_application_id
75 , arc_posting_control_id
76 , last_update_date
77 , last_updated_by
78 , last_update_login
79 , creation_Date
80 , created_by
81 )
82 VALUES ( LRA.receivable_application_id
83 , -3
84 , sysdate
85 , fnd_global.user_id
86 , fnd_global.login_id
87 , sysdate
88 , fnd_global.user_id
89 );
90
91 --
92 l_Count := l_Count + 1;
93 --
94 END IF;
95 --
96 END LOOP;
97 IF (l_proc_level >= l_debug_level ) THEN
98 FND_LOG.STRING (l_proc_level , 'igi.plsql.igircidb.PostNonDistApplications',
99 ' Count : ' || l_count);
100 END IF;
101 EXCEPTION
102 WHEN OTHERS THEN
103 WritetoLog( 'Exception:PostNonDistApplications:' );
104 RAISE;
105 END;
106 --
107 PROCEDURE PostDistributedApplications( p_Prepare IN ParametersType ) IS
108 CURSOR CRa IS
109 SELECT DISTINCT
110 DECODE(
111 l.lookup_code,
112 '1', 'N',
113 '2', 'Y'
114 ) CmPsIdFlag,
115 ra.receivable_application_id
116 FROM ar_receivable_applications ra,
117 ra_cust_trx_types ctt,
118 ra_customer_trx ct,
119 ar_cash_receipts cr,
120 ar_cash_receipt_history crh,
121 ra_customer_trx ctcm,
122 ar_lookups l
123 WHERE ra.gl_date BETWEEN p_Prepare.GlDateFrom
124 AND p_Prepare.GlDateTo
125 AND nvl(ra.postable,'Y') = 'Y'
126 AND nvl(ra.confirmed_flag,'Y') = 'Y'
127 AND ra.status||'' = 'APP' -- Bug 3519052
128 AND ra.cash_receipt_id = cr.cash_receipt_id(+)
129 AND ra.cash_receipt_history_id = crh.cash_receipt_history_id(+)
130 AND ra.customer_trx_id = ctcm.customer_trx_id(+)
131 AND ctcm.previous_customer_trx_id IS NULL
132 AND ra.applied_customer_trx_id = ct.customer_trx_id
133 AND ct.cust_trx_type_id = ctt.cust_trx_type_id
134 AND ra.set_of_books_id = p_Prepare.SetOfBooksId
135 AND l.lookup_type = 'AR_CARTESIAN_JOIN'
136 AND (
137 ( l.lookup_code ='1' )
138 OR
139 ( l.lookup_code = '2'
140 AND
141 ra.application_type = 'CM' )
142 )
143 ;
144 --
145 l_Count NUMBER :=0;
146
147
148 FUNCTION IsRecordCopiedBefore ( fp_app_id in number) return boolean
149 IS
150 CURSOR c_app is
151 select 'x'
152 from igi_ar_rec_applications_all igi
153 where igi.receivable_application_id = fp_app_id
154 ;
155 BEGIN
156 FOR l_app IN c_app LOOP
157 return TRUE;
158 END LOOP;
159 return FALSE;
160 EXCEPTION WHEN OTHERS THEN
161 return FALSE;
162 END IsRecordCopiedBefore;
163
164
165 BEGIN
166 IF (l_proc_level >= l_debug_level ) THEN
167 FND_LOG.STRING (l_proc_level , 'igi.plsql.igircidb.PostDistributedApplications',
168 ' Begin PostDistributedApplications ');
169 END IF;
170 FOR LRA in CRa LOOP
171 IF (LRA.CmPsIdFlag <> 'Y') AND (NOT IsRecordCopiedBefore( LRA.receivable_application_id ))
172 THEN
173
174 INSERT INTO igi_ar_rec_applications_all
175 ( receivable_application_id
176 , arc_posting_control_id
177 , last_update_date
178 , last_updated_by
179 , last_update_login
180 , creation_Date
181 , created_by
182 )
183 VALUES ( LRA.receivable_application_id
184 , -3
185 , sysdate
186 , fnd_global.user_id
187 , fnd_global.login_id
188 , sysdate
189 , fnd_global.user_id
190 );
191
192 --
193 l_Count := l_Count + 1;
194 --
195 END IF;
196 --
197 END LOOP;
198 IF (l_proc_level >= l_debug_level ) THEN
199 FND_LOG.STRING (l_proc_level , 'igi.plsql.igircidb.PostDistributedApplications',
200 ' Count : ' || l_count);
201 END IF;
202
203 EXCEPTION
204 WHEN OTHERS THEN
205 WritetoLog( 'Exception:PostDistributedApplications:' );
206 RAISE;
207 END;
208 PROCEDURE PostCashReceiptHistory( p_Prepare IN ParametersType ) IS
209 CURSOR CCrh IS
210 SELECT distinct crh.cash_receipt_history_id
211 FROM ar_cash_receipt_history crh,
212 ar_cash_receipts cr,
213 ar_distributions d
214 WHERE crh.gl_date BETWEEN p_Prepare.GlDateFrom
215 AND p_Prepare.GlDateTo
216 AND crh.postable_flag = 'Y'
217 AND cr.cash_receipt_id = crh.cash_receipt_id
218 AND crh.cash_receipt_history_id = d.source_id
219 AND d.source_table = 'CRH' ;
220 --
221 l_Count NUMBER :=0;
222
223 FUNCTION IsRecordCopiedBefore ( fp_crh_id in number) return boolean
224 IS
225 CURSOR c_crh is
226 select 'x'
227 from igi_ar_cash_receipt_hist_all igi
228 where igi.cash_receipt_history_id = fp_crh_id
229 ;
230 BEGIN
231 FOR l_crh IN c_crh LOOP
232 return TRUE;
233 END LOOP;
234 return FALSE;
235 EXCEPTION WHEN OTHERS THEN
236 return FALSE;
237 END IsRecordCopiedBefore;
238
239 BEGIN
240 IF (l_proc_level >= l_debug_level ) THEN
241 FND_LOG.STRING (l_proc_level , 'igi.plsql.igircidb.PostCashReceiptHistory',
242 ' Begin PostCashReceiptHistory ');
243 END IF;
244 FOR lcrh in CCRH LOOP
245
246 IF IsRecordCopiedBefore ( lcrh.cash_receipt_history_id) THEN
247 NULL;
248 ELSE
249
250 BEGIN
251 INSERT into igi_ar_cash_receipt_hist_all (
252 cash_receipt_history_id
253 , last_update_date
254 , last_updated_by
255 , last_update_login
256 , creation_date
257 , created_by
258 , arc_rev_post_control_id
259 , arc_posting_control_id
260
261 ) values
262 ( lcrh.cash_receipt_history_id
263 , sysdate
264 , fnd_global.user_id
265 , fnd_global.conc_login_id -- bug 4119243
266 , sysdate
267 , fnd_global.user_id -- bug 4119243
268 , null
269 , -3
270 );
271 EXCEPTION WHEN OTHERS THEN
272 null;
273 END;
274
275 l_Count := l_Count + 1;
276 END IF;
277
278 END LOOP;
279 IF (l_proc_level >= l_debug_level ) THEN
280 FND_LOG.STRING (l_proc_level , 'igi.plsql.igircidb.PostCashReceiptHistory',
281 ' Count : ' || l_count);
282 END IF;
283
284 EXCEPTION
285 WHEN OTHERS THEN
286 WritetoLog( 'PostCashReceiptHistory:' );
287 RAISE;
288 END;
289 --
290 PROCEDURE PostRevCashReceiptHist( p_Prepare IN ParametersType ) IS
291 CURSOR CCrh IS
292 SELECT distinct crh.cash_receipt_history_id
293 FROM ar_cash_receipt_history crh,
294 ar_cash_receipts cr,
295 ar_distributions d
296 WHERE crh.reversal_gl_date BETWEEN p_Prepare.GlDateFrom
297 AND p_Prepare.GlDateTo
298 AND crh.postable_flag = 'Y'
299 AND cr.cash_receipt_id = crh.cash_receipt_id
300 AND crh.cash_receipt_history_id = d.source_id
301 AND d.source_table = 'CRH' ;
302 --
303 l_Count NUMBER :=0;
304
305 FUNCTION IsRecordCopiedBefore ( fp_crh_id in number) return boolean
306 IS
307 CURSOR c_crh is
308 select 'x'
309 from igi_ar_cash_receipt_hist_all igi
310 where igi.cash_receipt_history_id = fp_crh_id
311 ;
312 BEGIN
313 FOR l_crh IN c_crh LOOP
314 return TRUE;
315 END LOOP;
316 return FALSE;
317 EXCEPTION WHEN OTHERS THEN
318 return FALSE;
319 END IsRecordCopiedBefore;
320
321 BEGIN
322 IF (l_proc_level >= l_debug_level ) THEN
323 FND_LOG.STRING (l_proc_level , 'igi.plsql.igircidb.PostRevCashReceiptHist',
324 ' Begin PostRevCashReceiptHist ');
325 END IF;
326 FOR lcrh in CCRH LOOP
327
328 IF IsRecordCopiedBefore ( lcrh.cash_receipt_history_id) THEN
329
330 UPDATE igi_Ar_cash_receipt_hist_all
331 SET arc_rev_post_control_id = -3
332 , arc_rev_gl_posted_date = null
333 WHERE cash_receipt_history_id = lcrh.cash_receipt_history_id
334 ;
335
336 ELSE
337
341 , last_update_date
338 BEGIN
339 INSERT into igi_ar_cash_receipt_hist_all (
340 cash_receipt_history_id
342 , last_updated_by
343 , last_update_login
344 , creation_date
345 , created_by
346 , arc_posting_control_id
347 , arc_rev_post_control_id
348 ) values
349 ( lcrh.cash_receipt_history_id
350 , sysdate
351 , fnd_global.user_id
352 , fnd_global.conc_login_id -- bug 4119243
353 , sysdate
354 , fnd_global.user_id
355 , -3 -- Bug 3519052
356 , null
357 );
358 EXCEPTION WHEN OTHERS THEN
359 null;
360 END;
361
362 l_Count := l_Count + 1;
363 END IF;
364
365 END LOOP;
366 IF (l_proc_level >= l_debug_level ) THEN
367 FND_LOG.STRING (l_proc_level , 'igi.plsql.igircidb.PostRevCashReceiptHist',
368 ' Count : ' || l_count);
369 END IF;
370
371 EXCEPTION
372 WHEN OTHERS THEN
373 WritetoLog( 'PostRevCashReceiptHist:' );
374 RAISE;
375 END;
376 --
377
378 PROCEDURE PostMiscCashDistributions( p_Prepare IN ParametersType ) IS
379 CURSOR CMcd IS
380 SELECT distinct mcd.misc_cash_distribution_id
381 FROM ar_misc_cash_distributions mcd,
382 ar_cash_receipts cr
383 WHERE mcd.gl_date BETWEEN p_Prepare.GlDateFrom
384 AND p_Prepare.GlDateTo
385 AND mcd.set_of_books_id = p_Prepare.setofbooksid
386 AND cr.cash_receipt_id = mcd.cash_receipt_id ;
387 --
388
389 l_Count NUMBER :=0;
390
391 FUNCTION IsRecordCopiedBefore ( fp_mcd_id in number) return boolean
392 IS
393 CURSOR c_mcd is
394 select 'x'
395 from igi_ar_misc_cash_dists_all igi
396 where igi.misc_cash_distribution_id = fp_mcd_id
397 ;
398 BEGIN
399 FOR l_mcd IN c_mcd LOOP
400 return TRUE;
401 END LOOP;
402 return FALSE;
403 EXCEPTION WHEN OTHERS THEN
404 return FALSE;
405 END IsRecordCopiedBefore;
406 BEGIN
407 IF (l_proc_level >= l_debug_level ) THEN
408 FND_LOG.STRING (l_proc_level , 'igi.plsql.igircidb.PostMiscCashDistributions',
409 ' Begin PostMiscCashDistributions ');
410 END IF;
411
412 FOR RMcd IN CMcd
413 LOOP
414 -- first create the debit in gl_interface to the account_code_combination_id
415 IF NOT IsRecordCopiedBefore (Rmcd.misc_cash_distribution_id) THEN
416 INSERT INTO igi_ar_misc_cash_dists_all (
417 misc_cash_distribution_id
418 , arc_posting_control_id
419 , last_update_date
420 , last_updated_by
421 , last_update_login
422 , creation_date
423 , created_by
424 ) VALUES (
425 Rmcd.misc_cash_distribution_id
426 , -3
427 , sysdate
428 , fnd_global.user_id
429 , fnd_global.login_id
430 , sysdate
431 , fnd_global.user_id
432 );
433 l_Count := l_Count + 1;
434 END IF;
435
436 END LOOP;
437 IF (l_proc_level >= l_debug_level ) THEN
438 FND_LOG.STRING (l_proc_level , 'igi.plsql.igircidb.PostMiscCashDistributions',
439 ' Count : ' || l_count);
440 END IF;
441
442 EXCEPTION
443 WHEN OTHERS THEN
444 WritetoLog( 'PostMiscCashDistributions:' );
445 RAISE;
446 END;
447
448 FUNCTION ar_nls_text
449 ( p_message_name VARCHAR2
450 ) RETURN VARCHAR2 IS
451 l_message_text VARCHAR2(240);
452 BEGIN
453 SELECT message_text
454 INTO l_message_text
455 FROM fnd_new_messages
456 WHERE application_id = 222
457 AND message_name = p_message_name;
458 return(l_message_text);
459 EXCEPTION
460 WHEN OTHERS THEN
461 return(p_message_name);
462 end;
463
464 --
465 -- --------------------------------------------------------------------------
466 --
467 --
471
468
469 PROCEDURE Prepare( p_prepare IN ParametersType ) IS
470 BEGIN
472 PostCashReceiptHistory( p_prepare );
473 PostRevCashReceiptHist ( p_Prepare );
474 PostMiscCashDistributions( p_prepare );
475 PostNonDistApplications( p_prepare );
476 PostDistributedApplications( p_prepare );
477
478 EXCEPTION
479 WHEN OTHERS THEN
480 WritetoLog( 'Exception: IGIRCBID.Prepare ( p_Prepare ):'||sqlerrm );
481 RAISE_APPLICATION_ERROR( -20000, sqlerrm||'Exception: IGIRCBID.Prepare ( p_Prepare ):' );
482 END;
483
484 PROCEDURE CopyData ( p_GlDateFrom IN DATE
485 , p_GlDateTo IN DATE
486 , p_GlPostedDate IN DATE
487 , p_CreatedBy IN NUMBER
488 , p_SummaryFlag IN VARCHAR2 ) IS
489
490 l_SetOfBooksId NUMBER;
491 l_CashSetOfBooksId NUMBER;
492 l_ra_id NUMBER;
493 l_crh_id NUMBER;
494 l_mcd_id NUMBER;
495 l_balanceflag VARCHAR2(1);
496
497
498 BEGIN
499 WritetoLog ( 'IGIRCBID : Begin LOG...');
500 Prepare
501 ( p_GlDateFrom
502 , p_GlDateTo
503 , p_GlPostedDate
504 ) ;
505 WritetoLog ( 'IGIRCBID : End LOG...');
506
507 END;
508
509
510 PROCEDURE Prepare
511 (
512 p_GlDateFrom IN DATE
513 , p_GlDateTo IN DATE
514 , p_GlPostedDate IN DATE
515 ) IS
516 l_prepare ParametersType;
517 l_BalanceFlag Varchar2(1);
518 BEGIN
519 l_prepare.GlDateFrom := p_GlDateFrom;
520 l_prepare.GlDateTo := p_GlDateTo + (86399/86400);
521 l_prepare.GlPostedDate := p_GlPostedDate;
522
523 SELECT -- sob.currency_code,
524 sp.set_of_books_id
525 , igisp.arc_cash_sob_id
526 , igisp.arc_unalloc_rev_ccid
527 INTO -- l_prepare.FuncCurr ,
528 l_prepare.SetOfBooksId
529 , l_prepare.CashSetOfBooksId
530 , l_prepare.UnallocatedRevCcid
531 FROM ar_system_parameters sp
532 , igi_ar_system_options igisp
533 , gl_sets_of_books sob
534 WHERE sob.set_of_books_id = sp.set_of_books_id;
535
536 BEGIN
537
538 Prepare ( l_prepare );
539
540 EXCEPTION
541 WHEN OTHERS THEN
542 WritetoLog( 'Exception:IGIRCBID.Prepare( ... ):'||sqlerrm );
543 RAISE;
544 END;
545
546 END Prepare;
547
548 PROCEDURE Prepare(
549 p_GlDateFrom IN DATE
550 , p_GlDateTo IN DATE
551 , p_GlPostedDate IN DATE
552 , p_SetOfBooksId IN NUMBER
553 , p_CashSetOfBooksId IN NUMBER
554 ) IS
555
556 l_prepare ParametersType;
557
558 BEGIN
559
560 l_prepare.GlDateFrom := p_GlDateFrom;
561 l_prepare.GlDateTo := p_GlDateTo + (86399/86400);
562 l_prepare.GlPostedDate := p_GlPostedDate;
563 l_prepare.SetOfBooksId := p_SetOfBooksId;
564 l_prepare.CashSetOfBooksId := p_CashSetOfBooksId;
565
566 Prepare ( l_prepare );
567
568 EXCEPTION
569 WHEN OTHERS THEN
570 WritetoLog( 'Exception:IGIRCBID.Prepare( ... ):'||sqlerrm );
571 RAISE;
572 END;
573 --
574 --
575
576 BEGIN
577
578 l_debug_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
579 l_state_level := FND_LOG.LEVEL_STATEMENT;
580 l_proc_level := FND_LOG.LEVEL_PROCEDURE;
581 l_event_level := FND_LOG.LEVEL_EVENT;
582 l_excep_level := FND_LOG.LEVEL_EXCEPTION;
583 l_error_level := FND_LOG.LEVEL_ERROR;
584 l_unexp_level := FND_LOG.LEVEL_UNEXPECTED;
585
586 END IGIRCBID;