[Home] [Help]
PACKAGE BODY: APPS.IGIRCABJP
Source
1 PACKAGE BODY IGIRCABJP AS
2 -- $Header: igircajb.pls 120.8.12000000.5 2007/11/27 11:05:50 pshivara ship $
3
4 DebugMode BOOLEAN;
5
6 --following variables added for bug 3199481: fnd logging changes: sdixit
7 l_debug_level number;
8 l_state_level number;
9 l_proc_level number;
10 l_event_level number;
11 l_excep_level number;
12 l_error_level number;
13 l_unexp_level number;
14
15 l_xah_ar_application_id NUMBER := 222;
16
17 PROCEDURE WriteToLogFile (pp_mesg in varchar2) IS
18 BEGIN
19 IF DebugMode THEN
20 fnd_file.put_line( fnd_file.log , pp_mesg );
21 END IF;
22 END WriteToLogFile;
23 --
24 PROCEDURE GetAdjustments ( p_Report IN ReportParametersType ) IS
25 BEGIN
26 INSERT INTO
27 igi_ar_journal_interim
28 (
29 status,
30 actual_flag,
31 request_id,
32 created_by,
33 date_created,
34 set_of_books_id,
35 je_source_name,
36 je_category_name,
37 transaction_date,
38 accounting_date,
39 currency_code,
40 code_combination_id,
41 entered_dr,
42 entered_cr,
43 accounted_dr,
44 accounted_cr,
45 reference10,
46 reference21,
47 reference22,
48 reference23,
49 reference24,
50 reference25,
51 reference26,
52 reference27,
53 reference28,
54 reference29,
55 reference30
56 )
57 SELECT
58 'NEW' status,
59 'A' actual_flag,
60 p_Report.ReqId request_id,
61 fnd_global.user_id created_by,
62 trunc(sysdate) date_created,
63 p_Report.SetOfBooksId sob_id,
64 'Receivables' source,
65 'Adjustment' category,
66 adj.apply_date trx_date,
67 adj.gl_date gl_date,
68 ct.invoice_currency_code currency,
69 ard.code_combination_id ccid,
70 ard.amount_dr entered_dr,
71 ard.amount_cr entered_cr,
72 ard.acctd_amount_dr acctd_dr,
73 ard.acctd_amount_cr acctd_cr,
74 l_cat.meaning ref10,
75 to_char(p_Report.ReqId) ref21,
76 to_char(adj.adjustment_id) ref22,
77 to_char(ard.line_id) ref23,
78 to_char(null) ref24,
79 ct.trx_number ref25,
80 hz_cust_accounts.account_number ref26, -- Bug 3902175
81 ct.bill_to_customer_id ref27,
82 'ADJ' ref28,
83 'ADJ' || ard.source_type ref29,
84 'AR_ADJUSTMENTS' ref30
85 FROM
86 ra_customer_trx_all ct,
87 ra_cust_trx_types_all ctt,
88 ar_distributions_all ard,
89 hz_parties, -- Bug 3902175
90 hz_cust_accounts, -- Bug 3902175
91 ar_adjustments_all adj,
92 ar_lookups l_cat,
93 xla_ae_headers xah
94 WHERE
95 adj.adjustment_id +0 < p_Report.NxtAdjustmentId
96 and adj.set_of_books_id = p_Report.SetOfBooksId
97 and nvl(adj.postable,'Y') = 'Y'
98 and adj.adjustment_id = ard.source_id
99 and ard.source_table = 'ADJ'
100 and adj.customer_trx_id = ct.customer_trx_id
101 and ctt.cust_trx_type_id = ct.cust_trx_type_id
102 and hz_cust_accounts.cust_account_id = ct.bill_to_customer_id -- Bug 3902175
103 and hz_parties.party_id = hz_cust_accounts.party_id -- Bug 3902175
104 and l_cat.lookup_type = 'ARRGTA_FUNCTION_MAPPING'
105 and l_cat.lookup_code = 'ADJ_' || ard.source_type
106 and ct.invoice_currency_code = decode( p_Report.FuncCurr,
107 null,ct.invoice_currency_code,
108 p_Report.FuncCurr)
109 and p_Report.adj = 'Y'
110 and adj.gl_date between p_Report.GlDateFrom
111 and p_Report.GlDateTo
112 and adj.posting_control_id > 0
113 and xah.event_id = adj.event_id
114 and xah.application_id = l_xah_ar_application_id
115 and xah.ledger_id = adj.set_of_books_id
116 and xah.ledger_id = p_Report.SetOfBooksId
117 and xah.accounting_date between p_Report.GlDateFrom and p_Report.GlDateTo
118 and xah.gl_transfer_status_code = 'Y'
119 and xah.gl_transfer_date between p_Report.PostedDateFrom and p_Report.PostedDateTo
120 /* Added for bug 6647672 start */
121 and NOT EXISTS ( select 'Y'
122 from xla_ae_headers xah2
123 where xah2.event_id = adj.event_id
124 and xah2.application_id = l_xah_ar_application_id
125 and xah2.ledger_id = adj.set_of_books_id
126 and xah2.ledger_id = p_Report.CashSetOfBooksId
127 and xah2.accounting_date between p_Report.GlDateFrom and p_Report.GlDateTo
128 and xah2.gl_transfer_status_code = 'Y'
129 and xah2.gl_transfer_date between p_Report.PostedDateFrom and p_Report.PostedDateTo );
130 /* end bug 6647672 */
131 /* and exists ( select 'x'
132 from igi_ar_adjustments
133 where a djustment_id = adj.adjustment_id
134 and arc_posting_control_id = -3
135 )
136 and nvl(adj.gl_posted_date,to_date('01-01-1952','DD-MM-YYYY'))
137 between
138 decode(p_Report.PostedDateFrom ,
139 null, nvl(adj.gl_posted_date,to_date('01-01-1952',
140 'DD-MM-YYYY')),
141 p_Report.PostedDateFrom )
142 and
143 decode( p_Report.PostedDateTo ,
144 null, nvl(adj.gl_posted_date,to_date('01-01-1952',
145 'DD-MM-YYYY')),
146 p_Report.PostedDateTo );
147 */
148
149 EXCEPTION
150 WHEN OTHERS THEN
151
152 FND_MESSAGE.SET_NAME('IGI', 'IGI_LOGGING_USER_ERROR'); -- Seeded Message
153
154 IF ( l_unexp_level >= l_debug_level ) THEN
155
156 FND_MESSAGE.SET_NAME('IGI','IGI_LOGGING_UNEXP_ERROR');
157 FND_MESSAGE.SET_TOKEN('CODE',SQLCODE);
158 FND_MESSAGE.SET_TOKEN('MSG', SQLERRM);
159 FND_LOG.MESSAGE ( l_unexp_level,'igi.plsql.igircajb.IGIRCABJP.GetAdjustments',TRUE);
160 END IF;
161
162 RAISE;
163 END;
164 --
165 --
166 PROCEDURE GetTxnDistributions( p_Report IN ReportParametersType )
167 IS
168 BEGIN
169 INSERT INTO
170 igi_ar_journal_interim
171 (
172 status,
173 actual_flag,
174 request_id,
175 created_by,
176 date_created,
177 set_of_books_id,
178 je_source_name,
179 je_category_name,
180 transaction_date,
181 accounting_date,
182 currency_code,
183 code_combination_id,
184 entered_dr,
185 entered_cr,
186 accounted_dr,
187 accounted_cr,
188 reference10,
189 reference21,
190 reference22,
191 reference23,
192 reference24,
193 reference25,
194 reference26,
195 reference27,
196 reference28,
197 reference29,
198 reference30
199 )
200 SELECT
201 'NEW' status,
202 'A' actual_flag,
203 p_Report.ReqId request_id,
204 fnd_global.user_id created_by,
205 trunc(sysdate) date_created,
206 p_Report.SetOfBooksId sob_id,
207 'Receivables' source,
208 decode(ctt.type,
209 'CM', 'Credit Memos',
210 'DM', 'Debit Memos',
211 'CB', 'Chargebacks',
212 'Sales Invoices') category,
213 ct.trx_date trx_date,
214 ctlgd.gl_date gl_date,
215 ct.invoice_currency_code currency,
216 ctlgd.code_combination_id ccid,
217 decode(ctlgd.account_class,
218 'REC', decode(sign(nvl(ctlgd.amount,0)),
219 -1,null,nvl(ctlgd.amount,0)),
220 decode(sign(nvl(ctlgd.amount,0)),
221 -1,-nvl(ctlgd.amount,0),null)) entered_dr,
222 decode(ctlgd.account_class,
223 'REC', decode(sign(nvl(ctlgd.amount,0)),
224 -1,-nvl(ctlgd.amount,0),null),
225 decode(sign(nvl(ctlgd.amount,0)),
226 -1,null,nvl(ctlgd.amount,0))) entered_cr,
227 decode(ctlgd.account_class,
228 'REC', decode(sign(nvl(ctlgd.acctd_amount,0)),
229 -1,null,nvl(ctlgd.acctd_amount,0)),
230 decode(sign(nvl(ctlgd.acctd_amount,0)),
231 -1,-nvl(ctlgd.acctd_amount,0),null)) acctd_dr,
232 decode(ctlgd.account_class,
233 'REC', decode(sign(nvl(ctlgd.acctd_amount,0)),
234 -1,-nvl(ctlgd.acctd_amount,0),null),
235 decode(sign(nvl(ctlgd.acctd_amount,0)),
236 -1,null,nvl(ctlgd.acctd_amount,0))) acctd_cr,
237 l_cat.meaning ref10,
238 to_char(p_Report.ReqId) ref21,
239 to_char(ct.customer_trx_id) ref22,
240 to_char(ctlgd.cust_trx_line_gl_dist_id) ref23,
241 to_char(null) ref24,
242 ct.trx_number ref25,
243 hz_cust_accounts.account_number ref26, -- Bug 3902175
244 to_char(ct.bill_to_customer_id) ref27,
245 decode(ctt.type,
246 'CM', 'CM',
247 'DM', 'DM',
248 'CB', 'CB',
249 'INV') ref28,
250 decode(ctt.type,
251 'CM', 'CM_',
252 'DM', 'DM_',
253 'CB', 'CB_',
254 'INV_')||ctlgd.account_class ref29,
255 'RA_CUST_TRX_LINE_GL_DIST' ref30
256 FROM
257 ar_lookups l_cat,
258 ra_cust_trx_types ctt,
259 hz_parties, -- Bug 3902175
260 hz_cust_accounts, -- Bug 3902175
261 ra_customer_trx_all ct,
262 ra_cust_trx_line_gl_dist ctlgd,
263 xla_ae_headers xah
264 WHERE
265 ctlgd.cust_trx_line_gl_dist_id+0 < p_Report.NxtCustTrxLineGlDistId
266 and ctlgd.set_of_books_id = p_Report.SetOfBooksId
267 and ctlgd.account_set_flag = 'N'
268 and ctlgd.customer_trx_id = ct.customer_trx_id
269 and ct.complete_flag = 'Y'
270 and ct.cust_trx_type_id = ctt.cust_trx_type_id
271 and hz_parties.party_id = hz_cust_accounts.party_id -- Bug 3902175
272 and hz_cust_accounts.cust_account_id = ct.bill_to_customer_id -- Bug 3902175
276 'DM', 'DM_',
273 and l_cat.lookup_type = 'ARRGTA_FUNCTION_MAPPING'
274 and l_cat.lookup_code = decode(ctt.type,
275 'CM', 'CM_',
277 'CB', 'CB_',
278 'INV_')||nvl(ctlgd.account_class,'REV')
279 and ct.invoice_currency_code = decode( p_Report.FuncCurr,
280 null,ct.invoice_currency_code,
281 p_Report.FuncCurr)
282 and ( ('Y' = 'Y' and ctt.type in ( 'INV','GUAR','DEP' ))
283 OR
284 ('Y' = 'Y' and ctt.type = 'DM' )
285 OR
286 ('Y' = 'Y' and ctt.type = 'CB' )
287 OR
288 ('Y' = 'Y' and ctt.type = 'CM' )
289 )
290 and ctlgd.gl_date between p_Report.GlDateFrom
291 and p_Report.GlDateTo
292 and ctlgd.posting_control_id > 0
293 and xah.event_id = ctlgd.event_id
294 and xah.application_id = l_xah_ar_application_id
295 and xah.ledger_id = ctlgd.set_of_books_id
296 and xah.ledger_id = p_Report.SetOfBooksId
297 and xah.accounting_date between p_Report.GlDateFrom and p_Report.GlDateTo
298 and xah.gl_transfer_status_code = 'Y'
299 and xah.gl_transfer_date between p_Report.PostedDateFrom and p_Report.PostedDateTo
300 /* Added for bug 6647672 start */
301 and NOT EXISTS ( select 'Y'
302 from xla_ae_headers xah2
303 where xah2.event_id = ctlgd.event_id
304 and xah2.application_id = l_xah_ar_application_id
305 and xah2.ledger_id = ctlgd.set_of_books_id
306 and xah2.ledger_id = p_Report.CashSetOfBooksId
307 and xah2.accounting_date between p_Report.GlDateFrom and p_Report.GlDateTo
308 and xah2.gl_transfer_status_code = 'Y'
309 and xah2.gl_transfer_date between p_Report.PostedDateFrom and p_Report.PostedDateTo ) ;
310 /* end bug 6647672 */
311 /* and exists ( select 'x'
312 from igi_ar_cash_basis_dists_all cbd
313 where cbd.source = 'GL'
314 and cbd.source_id = ctlgd.cust_trx_line_gl_dist_id
315 and ( ( exists( select 'x'
316 from igi_ar_rec_applications_all
317 where receivable_application_id = cbd.receivable_application_id
318 and arc_posting_control_id = -3
319 )
320 ) or
321 ( cbd.receivable_application_id_cash is not null and
322 ( exists
323 ( select receivable_application_id
324 from igi_ar_rec_applications_all
325 where receivable_application_id =cbd.receivable_application_id
326 and arc_posting_control_id = -3
327 )
328 )
329 )
330 )
331 )
332 and nvl(ctlgd.gl_posted_date,to_date('01-01-1952','DD-MM-YYYY'))
333 between
334 decode( p_Report.PostedDateFrom ,
335 null, nvl(ctlgd.gl_posted_date,to_date('01-01-1952',
336 'DD-MM-YYYY')),
337 p_Report.PostedDateFrom )
338 and
339 decode( p_Report.PostedDateTo,
340 null, nvl(ctlgd.gl_posted_date,to_date('01-01-1952',
341 'DD-MM-YYYY')),
342 p_Report.PostedDateTo );
343 */
344 EXCEPTION
345 WHEN OTHERS THEN
346
347 IF ( l_unexp_level >= l_debug_level ) THEN
348
349 FND_MESSAGE.SET_NAME('IGI','IGI_LOGGING_UNEXP_ERROR');
350 FND_MESSAGE.SET_TOKEN('CODE',SQLCODE);
351 FND_MESSAGE.SET_TOKEN('MSG', SQLERRM);
352 FND_LOG.MESSAGE ( l_unexp_level,'igi.pls.igircajb.IGIRCABJB.GetTxnDistributions',TRUE);
353 END IF;
354
355 RAISE;
356 END;
357 --
358 --
359 PROCEDURE GetRxptHistory( p_Report IN ReportParametersType ) IS
360 BEGIN
361 --
362 INSERT INTO
363 igi_ar_journal_interim
364 (
365 status,
366 actual_flag,
367 request_id,
368 created_by,
369 date_created,
370 set_of_books_id,
371 je_source_name,
372 je_category_name,
373 transaction_date,
374 accounting_date,
375 currency_code,
376 code_combination_id,
377 entered_dr,
378 entered_cr,
379 accounted_dr,
380 accounted_cr,
381 reference10,
382 reference21,
383 reference22,
384 reference23,
385 reference24,
386 reference25,
387 reference26,
388 reference27,
389 reference28,
390 reference29,
391 reference30
392 )
393 SELECT
394 'NEW' status,
395 'A' actual_flag,
396 p_Report.ReqId request_id,
397 fnd_global.user_id created_by,
398 trunc(sysdate) date_created,
402 'MISC', 'Misc Receipts',
399 p_Report.SetOfBooksId sob_id,
400 'Receivables' source_name,
401 decode(cr.type,
403 'Trade Receipts') category,
404 crh.trx_date trx_date,
405 crh.gl_date gl_date,
406 cr.currency_code currency,
407 ard.code_combination_id ccid,
408 to_number(ard.amount_dr) entered_dr,
409 to_number(ard.amount_cr) entered_cr,
410 to_number(ard.acctd_amount_dr) acctd_dr,
411 to_number(ard.acctd_amount_cr) acctd_cr,
412 l_cat.meaning ref10,
413 to_char(p_Report.ReqId) ref21,
414 decode(cr.type,
415 'CASH',to_char(cr.cash_receipt_id)||'C'||
416 to_char(crh.cash_receipt_history_id),
417 'MISC',to_char(cr.cash_receipt_id)) ref22,
418 to_char(ard.line_id) ref23,
419 cr.receipt_number ref24,
420 decode(cr.type,
421 'CASH',to_char(null),
422 'MISC',to_char(crh.cash_receipt_history_id)) ref25,
423 cust.customer_number ref26, -- Bug 3902175
424 to_char(cr.pay_from_customer) ref27,
425 decode( cr.type,
426 'MISC', 'MISC',
427 'TRADE') ref28,
428 decode( cr.type,
429 'MISC', 'MISC_',
430 'TRADE_')||ard.source_type ref29,
431 'AR_CASH_RECEIPT_HISTORY' ref30
432 FROM
433 ar_lookups l_cat,
434 (Select hz_cust_accounts.account_number customer_number,hz_cust_accounts.cust_account_id customer_id
435 from hz_parties,hz_cust_accounts where hz_parties.party_id = hz_cust_accounts.party_id) cust, -- bug 3902175
436 ar_distributions ard,
437 ar_cash_receipts cr,
438 ar_cash_receipt_history_all crh,
439 xla_ae_headers xah
440 WHERE crh.cash_receipt_history_id+0 < p_Report.NxtCashReceiptHistoryId
441 and crh.cash_receipt_history_id = ard.source_id
442 and ard.source_table = 'CRH'
443 and cr.set_of_books_id = p_Report.SetOfBooksId
444 and crh.postable_flag = 'Y'
445 and crh.cash_receipt_id = cr.cash_receipt_id
446 and cust.customer_id(+) = cr.pay_from_customer -- Bug 3902175
447 and l_cat.lookup_type = 'ARRGTA_FUNCTION_MAPPING'
448 and l_cat.lookup_code = decode( cr.type,
449 'MISC', 'MISC_',
450 'TRADE_')||ard.source_type
451 and cr.currency_code = decode( p_Report.FuncCurr,
452 null,cr.currency_code,
453 p_Report.FuncCurr )
454 and ( (p_Report.trade = 'Y' and cr.type <> 'MISC')
455 OR
456 (p_Report.misc = 'Y' and cr.type = 'MISC' ))
457 and crh.gl_date between p_Report.GlDateFrom
458 and p_Report.GldateTo
459 and crh.posting_control_id > 0
460 and xah.event_id = crh.event_id
461 and xah.application_id = l_xah_ar_application_id
462 and xah.ledger_id = cr.set_of_books_id
463 and xah.ledger_id = p_Report.SetOfBooksId
464 and xah.accounting_date between p_Report.GlDateFrom and p_Report.GlDateTo
465 and xah.gl_transfer_status_code = 'Y'
466 and xah.gl_transfer_date between p_Report.PostedDateFrom and p_Report.PostedDateTo
467 /* Added for bug 6647672 start */
468 and NOT EXISTS ( select 'Y'
469 from xla_ae_headers xah2
470 where xah2.event_id = crh.event_id
471 and xah2.application_id = l_xah_ar_application_id
472 and xah2.ledger_id = cr.set_of_books_id
473 and xah2.ledger_id = p_Report.CashSetOfBooksId
474 and xah2.accounting_date between p_Report.GlDateFrom and p_Report.GlDateTo
475 and xah2.gl_transfer_status_code = 'Y'
476 and xah2.gl_transfer_date between p_Report.PostedDateFrom and p_Report.PostedDateTo ) ;
477 /* end bug 6647672 */
478 /*
479 and exists ( select 'x'
480 from igi_ar_cash_receipt_hist_all
481 where cash_receipt_history_id = crh.cash_receipt_history_id
482 and arc_posting_control_id = -3
483 )
484 and nvl(crh.gl_posted_date,to_date('01-01-1952','DD-MM-YYYY'))
485 between
486 decode( p_Report.PostedDateFrom ,
487 null, nvl(crh.gl_posted_date,to_date('01-01-1952',
488 'DD-MM-YYYY')),
489 p_Report.PostedDateFrom )
490 and
491 decode( p_Report.PostedDateTo,
492 null, nvl(crh.gl_posted_date,to_date('01-01-1952',
493 'DD-MM-YYYY')),
494 p_Report.PostedDateTo );
495 */
496
497
498 EXCEPTION
499 WHEN OTHERS THEN
500
501 IF ( l_unexp_level >= l_debug_level ) THEN
502
503 FND_MESSAGE.SET_NAME('IGI','IGI_LOGGING_UNEXP_ERROR');
507 END IF;
504 FND_MESSAGE.SET_TOKEN('CODE',SQLCODE);
505 FND_MESSAGE.SET_TOKEN('MSG', SQLERRM);
506 FND_LOG.MESSAGE ( l_unexp_level,'igi.plsql.igircajb.IGIRCABJB.GetRxptHistory',TRUE);
508
509 RAISE;
510 END;
511 --
512 --
513 PROCEDURE GetRecApplications( p_Report IN ReportParametersType ) IS
514 BEGIN
515 INSERT INTO
516 igi_ar_journal_interim
517 (
518 status,
519 actual_flag,
520 request_id,
521 created_by,
522 date_created,
523 set_of_books_id,
524 je_source_name,
525 je_category_name,
526 transaction_date,
527 accounting_date,
528 currency_code,
529 code_combination_id,
530 entered_dr,
531 entered_cr,
532 accounted_dr,
533 accounted_cr,
534 reference1,
535 reference10,
536 reference21,
537 reference22,
538 reference23,
539 reference24,
540 reference25,
541 reference26,
542 reference27,
543 reference28,
544 reference29,
545 reference30
546 )
547 SELECT
548 'NEW' status,
549 'A' actual_flag,
550 p_Report.ReqId request_id,
551 fnd_global.user_id created_by,
552 trunc(sysdate) date_created,
553 p_Report.SetOfBooksId sob_id,
554 'Receivables' source,
555 decode(ra.amount_applied_from,
556 null,'Trade Receipts','Cross Currency') category,
557 ra.apply_date trx_date,
558 ra.gl_date gl_date,
559 cr.currency_code currency,
560 ard.code_combination_id ccid,
561 ard.amount_dr entered_dr,
562 ard.amount_cr entered_cr,
563 ard.acctd_amount_dr acctd_dr,
564 ard.acctd_amount_cr acctd_cr,
565 decode(ard.source_type,
566 'EXCH_GAIN',to_char(ard.code_combination_id),
567 'EXCH_LOSS',to_char(ard.code_combination_id),
568 null) ref1,
569 l_cat.meaning ref10,
570 to_char(p_Report.ReqId) ref21,
571 decode(ra.application_type,
572 'CASH',to_char(cr.cash_receipt_id)||'C'||
573 to_char(ra.receivable_application_id),
574 'CM', to_char(ra.receivable_application_id)) ref22,
575 to_char(ard.line_id) ref23,
576 cr.receipt_number ref24,
577 ctinv.trx_number ref25,
578 cust.customer_number ref26, -- Bug 3902175
579 to_char(cr.pay_from_customer) ref27,
580 decode(ra.amount_applied_from,
581 null,'TRADE','CCURR') ref28,
582 decode(ra.amount_applied_from,
583 null, 'TRADE_',
584 'CCURR_') || ard.source_type ref29,
585 'AR_RECEIVABLE_APPLICATIONS' ref30
586 FROM
587 ar_receivable_applications ra,
588 ar_cash_receipts cr,
589 ar_distributions ard,
590 ra_customer_trx ctinv,
591 ar_lookups l_cat,
592 ar_posting_control pc,
593 ar_system_parameters sp,
594 gl_sets_of_books gl,
595 (Select hz_cust_accounts.account_number customer_number,hz_cust_accounts.cust_account_id customer_id
596 from hz_parties,hz_cust_accounts where hz_parties.party_id = hz_cust_accounts.party_id) cust, -- bug 3902175
597 xla_ae_headers xah
598 WHERE
599 ra.receivable_application_id+0 < p_Report.NxtReceivableApplicationId
600 and ard.source_table = 'RA'
601 and ard.source_id = ra.receivable_application_id
602 and nvl(ra.postable,'Y') = 'Y'
603 and nvl(ra.confirmed_flag,'Y') = 'Y'
604 and ra.cash_receipt_id = cr.cash_receipt_id(+)
605 and ra.applied_customer_trx_id = ctinv.customer_trx_id(+)
606 and cust.customer_id(+) = cr.pay_from_customer -- Bug 3902175
607 and l_cat.lookup_type = 'ARRGTA_FUNCTION_MAPPING'
608 and l_cat.lookup_code = decode(ra.amount_applied_from,
609 null, 'TRADE_',
610 'CCURR_') || ard.source_type
611 and sp.set_of_books_id = p_Report.SetOfBooksId
612 and sp.set_of_books_id = gl.set_of_books_id
613 and ra.set_of_books_id = sp.set_of_books_id
614 and pc.posting_control_id(+) = ra.posting_control_id
615 and cr.currency_code = decode( p_Report.FuncCurr,
616 null,cr.currency_code,
617 p_Report.FuncCurr)
618 and (p_Report.Trade = 'Y' OR p_Report.ccurr = 'Y')
619 and ra.gl_date between p_Report.GlDateFrom
623 and xah.application_id = l_xah_ar_application_id
620 and p_Report.GlDateFrom
621 and ra.posting_control_id > 0
622 and xah.event_id = ra.event_id
624 and xah.ledger_id = ra.set_of_books_id
625 and xah.ledger_id = p_Report.SetOfBooksId
626 and xah.accounting_date between p_Report.GlDateFrom and p_Report.GlDateTo
627 and xah.gl_transfer_status_code = 'Y'
628 and xah.gl_transfer_date between p_Report.PostedDateFrom and p_Report.PostedDateTo
629 /* Added for bug 6647672 start */
630 and NOT EXISTS ( select 'Y'
631 from xla_ae_headers xah2
632 where xah2.event_id = ra.event_id
633 and xah2.application_id = l_xah_ar_application_id
634 and xah2.ledger_id = ra.set_of_books_id
635 and xah2.ledger_id = p_Report.CashSetOfBooksId
636 and xah2.accounting_date between p_Report.GlDateFrom and p_Report.GlDateTo
637 and xah2.gl_transfer_status_code = 'Y'
638 and xah2.gl_transfer_date between p_Report.PostedDateFrom and p_Report.PostedDateTo )
639 /* end bug 6647672 */
640 /* and exists ( select 'x'
641 from igi_ar_rec_applications_all
642 where receivable_application_id = ra.receivable_application_id
643 and arc_posting_control_id = -3
644 )
645 and nvl(ra.gl_posted_date,to_date('01-01-1952','DD-MM-YYYY'))
646 between
647 decode( p_Report.PostedDateTo ,
648 null, nvl(ra.gl_posted_date,to_date('01-01-1952',
649 'DD-MM-YYYY')),
650 fnd_date.canonical_to_date(p_Report.PostedDateTo))
651 and
652 decode( p_Report.PostedDateTo,
653 null, nvl(ra.gl_posted_date,to_date('01-01-1952',
654 'DD-MM-YYYY')),
655 fnd_date.canonical_to_date(p_Report.PostedDateTo))
656 */
657 UNION ALL
658 SELECT
659 'NEW' status,
660 'A' actual_flag,
661 p_Report.ReqId request_id,
662 fnd_global.user_id created_by,
663 trunc(sysdate) date_created,
664 p_Report.SetOfBooksId sob_id,
665 'Receivables' source,
666 'Credit Memo Applications' category,
667 ra.apply_date trx_date,
668 ra.gl_date gl_date,
669 ctcm.invoice_currency_code currency,
670 ard.code_combination_id ccid,
671 ard.amount_dr entered_dr,
672 ard.amount_cr entered_cr,
673 ard.acctd_amount_dr acctd_dr,
674 ard.acctd_amount_cr acctd_cr,
675 decode(ard.source_type,
676 'EXCH_GAIN',to_char(ard.code_combination_id),
677 'EXCH_LOSS',to_char(ard.code_combination_id),
678 null) ref1,
679 l_cat.meaning ref10,
680 to_char(p_Report.ReqId) ref21,
681 to_char(ra.receivable_application_id) ref22,
682 to_char(ard.line_id) ref23,
683 ctcm.trx_number ref24,
684 ctinv.trx_number ref25,
685 hz_cust_accounts.account_number ref26, -- Bug 3902175
686 to_char(ctcm.bill_to_customer_id) ref27,
687 'CMAPP' ref28,
688 'CMAPP_' || ard.source_type ref29,
689 'AR_RECEIVABLE_APPLICATIONS' ref30
690 FROM
691 ar_receivable_applications ra,
692 ra_customer_trx ctcm,
693 ar_distributions ard,
694 ra_cust_trx_line_gl_dist ctlgdcm,
695 ra_customer_trx ctinv,
696 ar_lookups l_cat,
697 ar_posting_control pc,
698 ar_system_parameters sp,
699 gl_sets_of_books gl,
700 hz_parties,
701 hz_cust_accounts,
702 xla_ae_headers xah
703 WHERE
704 ra.receivable_application_id+0 < p_Report.NxtReceivableApplicationId
705 and ard.source_table = 'RA'
706 and ard.source_id = ra.receivable_application_id
707 and nvl(ra.postable,'Y') = 'Y'
708 and nvl(ra.confirmed_flag,'Y') = 'Y'
709 and ra.status||'' = 'APP'
710 and ra.customer_trx_id = ctcm.customer_trx_id
711 and ra.customer_trx_id = ctlgdcm.customer_trx_id
712 and ctlgdcm.account_class = 'REC'
713 and ctlgdcm.latest_rec_flag = 'Y'
714 and ra.applied_customer_trx_id = ctinv.customer_trx_id
715 and hz_parties.party_id = hz_cust_accounts.party_id -- Bug 3902175
716 and hz_cust_accounts.cust_account_id = ctcm.bill_to_customer_id -- Bug 3902175
717 and l_cat.lookup_type = 'ARRGTA_FUNCTION_MAPPING'
718 and l_cat.lookup_code = 'CMAPP_' || ard.source_type
719 and sp.set_of_books_id = p_Report.SetOfBooksId
723 and ctcm.invoice_currency_code = decode( p_Report.FuncCurr,
720 and sp.set_of_books_id = gl.set_of_books_id
721 and ra.set_of_books_id = sp.set_of_books_id
722 and pc.posting_control_id(+) = ra.posting_control_id
724 null,ctcm.invoice_currency_code,
725 p_Report.FuncCurr)
726 and p_Report.CMApp = 'Y'
727 and ra.gl_date between p_Report.GlDateFrom
728 and p_Report.GLDateTo
729 and ra.posting_control_id > 0
730 and xah.event_id = ra.event_id
731 and xah.application_id = l_xah_ar_application_id
732 and xah.ledger_id = ra.set_of_books_id
733 and xah.ledger_id = p_Report.SetOfBooksId
734 and xah.accounting_date between p_Report.GlDateFrom and p_Report.GlDateTo
735 and xah.gl_transfer_status_code = 'Y'
736 and xah.gl_transfer_date between p_Report.PostedDateFrom and p_Report.PostedDateTo
737 /* Added for bug 6647672 start */
738 and NOT EXISTS ( select 'Y'
739 from xla_ae_headers xah2
740 where xah2.event_id = ra.event_id
741 and xah2.application_id = l_xah_ar_application_id
742 and xah2.ledger_id = ra.set_of_books_id
743 and xah2.ledger_id = p_Report.CashSetOfBooksId
744 and xah2.accounting_date between p_Report.GlDateFrom and p_Report.GlDateTo
745 and xah2.gl_transfer_status_code = 'Y'
746 and xah2.gl_transfer_date between p_Report.PostedDateFrom and p_Report.PostedDateTo );
747 /* end bug 6647672 */
748 /* and exists ( select 'x'
749 from igi_ar_rec_applications_all
750 where receivable_application_id = ra.receivable_application_id
751 and arc_posting_control_id = -3
752 )
753 and nvl(ra.gl_posted_date,to_date('01-01-1952','DD-MM-YYYY'))
754 between
755 decode( p_Report.PostedDateTo ,
756 null, nvl(ra.gl_posted_date,to_date('01-01-1952',
757 'DD-MM-YYYY')),
758 fnd_date.canonical_to_date(p_Report.PostedDateTo))
759 and
760 decode( p_Report.PostedDateTo,
761 null, nvl(ra.gl_posted_date,to_date('01-01-1952',
762 'DD-MM-YYYY')),
763 fnd_date.canonical_to_date(p_Report.PostedDateTo));
764 */
765
766 EXCEPTION
767 WHEN OTHERS THEN
768
769 IF ( l_unexp_level >= l_debug_level ) THEN
770
771 FND_MESSAGE.SET_NAME('IGI','IGI_LOGGING_UNEXP_ERROR');
772 FND_MESSAGE.SET_TOKEN('CODE',SQLCODE);
773 FND_MESSAGE.SET_TOKEN('MSG', SQLERRM);
774 FND_LOG.MESSAGE ( l_unexp_level,'igi.plsql.igircajb.IGIRCABJP.GetRecApplications',TRUE);
775 END IF;
776 RAISE;
777 END;
778 --
779 PROCEDURE GetMiscCashDists( p_Report IN ReportParametersType ) IS
780 BEGIN
781 INSERT INTO
782 igi_ar_journal_interim
783 (
784 status,
785 actual_flag,
786 request_id,
787 created_by,
788 date_created,
789 set_of_books_id,
790 je_source_name,
791 je_category_name,
792 transaction_date,
793 accounting_date,
794 currency_code,
795 code_combination_id,
796 entered_dr,
797 entered_cr,
798 accounted_dr,
799 accounted_cr,
800 reference10,
801 reference21,
802 reference22,
803 reference23,
804 reference24,
805 reference25,
806 reference26,
807 reference27,
808 reference28,
809 reference29,
810 reference30
811 )
812 SELECT
813 'NEW' status,
814 'A' actual_flag,
815 p_Report.ReqId request_id,
816 fnd_global.user_id created_by,
817 trunc(sysdate) date_created,
818 p_Report.SetOfBooksId sob_id,
819 'Receivables' source_name,
820 'Misc Receipts' category,
821 mcd.apply_date trx_date,
822 mcd.gl_date gl_date,
823 cr.currency_code currency,
824 mcd.code_combination_id ccid,
825 ard.amount_dr entered_dr,
826 ard.amount_cr entered_cr,
827 ard.acctd_amount_dr acctd_dr,
828 ard.acctd_amount_cr acctd_cr,
829 l_cat.meaning ref10,
830 to_char(p_Report.ReqId) ref21,
831 to_char(cr.cash_receipt_id) ref22,
832 to_char(ard.line_id) ref23,
833 cr.receipt_number ref24,
834 to_char(mcd.misc_cash_distribution_id) ref25,
835 null ref26,
836 null ref27,
837 'MISC' ref28,
841 ar_misc_cash_distributions mcd,
838 'MISC_' || ard.source_type ref29,
839 'AR_MISC_CASH_DISTRIBUTIONS' ref30
840 FROM
842 ar_distributions ard,
843 ar_cash_receipts cr,
844 ar_lookups l_cat,
845 xla_ae_headers xah
846 WHERE mcd.misc_cash_distribution_id+0 < p_Report.NxtMiscCashDistributionId
847 and mcd.set_of_books_id = p_Report.SetOfBooksId
848 and mcd.cash_receipt_id = cr.cash_receipt_id
849 and ard.source_table = 'MCD'
850 and ard.source_id = mcd.misc_cash_distribution_id
851 and l_cat.lookup_type = 'ARRGTA_FUNCTION_MAPPING'
852 and l_cat.lookup_code = 'MISC_' || ard.source_type
853 and cr.currency_code = decode( p_Report.FuncCurr,
854 null,cr.currency_code,
855 p_Report.FuncCurr)
856 and p_Report.Misc = 'Y'
857 and mcd.gl_date between
858 p_Report.GlDateFrom
859 and
860 p_Report.GlDateTo
861 and ( mcd.posting_control_id > 0 )
862 and xah.event_id = mcd.event_id
863 and xah.application_id = l_xah_ar_application_id
864 and xah.ledger_id = mcd.set_of_books_id
865 and xah.ledger_id = p_Report.SetOfBooksId
866 and xah.accounting_date between p_Report.GlDateFrom and p_Report.GlDateTo
867 and xah.gl_transfer_status_code = 'Y'
868 and xah.gl_transfer_date between p_Report.PostedDateFrom and p_Report.PostedDateTo
869 /* Added for bug 6647672 start */
870 and NOT EXISTS ( select 'Y'
871 from xla_ae_headers xah2
872 where xah2.event_id = mcd.event_id
873 and xah2.application_id = l_xah_ar_application_id
874 and xah2.ledger_id = mcd.set_of_books_id
875 and xah2.ledger_id = p_Report.CashSetOfBooksId
876 and xah2.accounting_date between p_Report.GlDateFrom and p_Report.GlDateTo
877 and xah2.gl_transfer_status_code = 'Y'
878 and xah2.gl_transfer_date between p_Report.PostedDateFrom and p_Report.PostedDateTo );
879 /* end bug 6647672 */
880 /* and exists ( select 'x'
881 from igi_ar_misc_cash_dists_all
882 where misc_cash_distribution_id = mcd.misc_cash_distribution_id
883 and arc_posting_control_id = -3
884 )
885 and nvl(mcd.gl_posted_date,to_date('01-01-1952','DD-MM-YYYY'))
886 between
887 decode( p_Report.PostedDateTo ,
888 null, nvl(mcd.gl_posted_date,to_date('01-01-1952',
889 'DD-MM-YYYY')),
890 fnd_date.canonical_to_date(p_Report.PostedDateTo))
891 and
892 decode( p_Report.PostedDateTo,
893 null, nvl(mcd.gl_posted_date,to_date('01-01-1952',
894 'DD-MM-YYYY')),
895 fnd_date.canonical_to_date(p_Report.PostedDateTo));
896 */
897
898 EXCEPTION
899 WHEN OTHERS THEN
900
901 IF ( l_unexp_level >= l_debug_level ) THEN
902
903 FND_MESSAGE.SET_NAME('IGI','IGI_LOGGING_UNEXP_ERROR');
904 FND_MESSAGE.SET_TOKEN('CODE',SQLCODE);
905 FND_MESSAGE.SET_TOKEN('MSG', SQLERRM);
906 FND_LOG.MESSAGE ( l_unexp_level,'igi.plsql.igircajb.IGIRCABJB.GetMiscCashDates',TRUE);
907 END IF;
908
909 RAISE;
910 END;
911 --
912
913 --
914 PROCEDURE CheckBalance( p_Report IN ReportParametersType ) IS
915 CURSOR CBal IS
916 select
917 max(je_category_name) cat_name,
918 max(currency_code) curr_code,
919 max(accounting_date) acctg_date,
920 max(nvl(reference24,reference25)) trx_num,
921 reference28 cat_code,
922 decode(instr(reference22,'C'),0, reference22,
923 substr(reference22,1,instr(reference22,'C')-1))
924 balance_id,
925 nvl(sum(nvl(entered_dr,0)),0) entered_dr,
926 nvl(sum(nvl(entered_cr,0)),0) entered_cr,
927 nvl(sum(nvl(accounted_dr,0)),0) accounted_dr,
928 nvl(sum(nvl(accounted_cr,0)),0) accounted_cr
929 from igi_ar_journal_interim
930 where je_source_name = 'Receivables'
931 and set_of_books_id = p_Report.SetOfBooksId
932 and request_id = p_Report.ReqId
933 group by
934 reference28,
935 decode(instr(reference22,'C'),0, reference22,
936 substr(reference22,1,instr(reference22,'C')-1))
937 having
938 ( nvl(sum(nvl(entered_dr,0)),0)<> nvl(sum(nvl(entered_cr,0)),0)
939 OR
940 nvl(sum(nvl(accounted_dr,0)),0)<> nvl(sum(nvl(accounted_cr,0)),0));
941 --
942 BEGIN
943 --
944 --
945 FOR RBal IN CBal
946 LOOP
947 delete from igi_ar_journal_interim iaji
948 where iaji.je_source_name = 'Receivables'
949 and iaji.set_of_books_id = p_Report.SetOfBooksId
950 and iaji.request_id = p_Report.ReqID
951 and iaji.reference23 = RBal.balance_id
952 and iaji.reference28 = RBal.cat_code
953 and iaji.je_category_name = RBal.cat_name
954 ;
955 END LOOP;
956 EXCEPTION
957 WHEN OTHERS THEN
958
959 IF ( l_unexp_level >= l_debug_level ) THEN
960
964 FND_LOG.MESSAGE ( l_unexp_level,'igi.plsql.igircajb.IGIRCABJP.CheckBalance',TRUE);
961 FND_MESSAGE.SET_NAME('IGI','IGI_LOGGING_UNEXP_ERROR');
962 FND_MESSAGE.SET_TOKEN('CODE',SQLCODE);
963 FND_MESSAGE.SET_TOKEN('MSG', SQLERRM);
965 END IF;
966
967 RAISE;
968 END;
969 --
970 PROCEDURE Report( p_Report IN ReportParametersType ) IS
971 BEGIN
972
973 IF (l_proc_level >= l_debug_level ) THEN
974 FND_LOG.STRING (l_proc_level , 'igi.plsql.igircajb.IGIRCABJP.Report',
975 ' Begin Accrual reconciliation program ');
976 END IF;
977
978 GetAdjustments ( p_Report );
979 GetTxnDistributions( p_Report );
980 GetRxptHistory( p_Report );
981 GetRecApplications( p_Report );
982 GetMiscCashDists( p_Report );
983 --
984 IF p_Report.ChkBalance = 'Y' AND
985 p_Report.PostedStatus <> 'POSTED'
986 THEN
987 IF (l_state_level >= l_debug_level ) THEN
988 FND_LOG.STRING (l_state_level , 'igi.plsql.igircajb.IGIRCABJP.Report',
989 ' >> Check the Balance ');
990 END IF;
991 CheckBalance( p_Report );
992 END IF;
993
994 IF (l_proc_level >= l_debug_level ) THEN
995 FND_LOG.STRING (l_proc_level , 'igi.plsql.igircajb.IGIRCABJP.Report',
996 ' End Accrual reconciliation program ');
997 END IF;
998
999 EXCEPTION
1000 WHEN OTHERS THEN
1001
1002 IF ( l_unexp_level >= l_debug_level ) THEN
1003
1004 FND_MESSAGE.SET_NAME('IGI','IGI_LOGGING_UNEXP_ERROR');
1005 FND_MESSAGE.SET_TOKEN('CODE',SQLCODE);
1006 FND_MESSAGE.SET_TOKEN('MSG', SQLERRM);
1007 FND_LOG.MESSAGE ( l_unexp_level,'igi.plsql.igircajb.IGIRCABJP.Report',TRUE);
1008 END IF;
1009
1010 RAISE_APPLICATION_ERROR( -20000, sqlerrm||'$Revision: 120.8.12000000.5 $:Report( p_Report ):' );
1011 END;
1012 --
1013 FUNCTION SubmitReconcileReport ( p_request_id in number
1014 , p_sob_id in number
1015 , p_coa in number
1016 , p_start_period in varchar2
1017 , p_end_period in varchar2
1018 , p_start_date in date
1019 , p_end_date in date
1020 , p_account_segment_from in varchar2
1021 , p_account_segment_to in varchar2
1022 )
1023 RETURN NUMBER IS
1024 l_RequestId NUMBER(15);
1025 l_yes varchar2(1);
1026 l_segment_name varchar2(40);
1027 NOT_SUBMITTED EXCEPTION;
1028 BEGIN
1029 -- Bug 3902175 GSCC warnings Fixed
1030 l_yes := 'Y';
1031
1032 select distinct substr(application_column_name, 1, 15)
1033 into l_segment_name
1034 from fnd_segment_attribute_values
1035 where segment_attribute_type = 'GL_ACCOUNT'
1036 and attribute_value = 'Y'
1037 and id_flex_num = p_coa
1038 ;
1039
1040 l_RequestId := FND_REQUEST.SUBMIT_REQUEST
1041 ( 'IGI'
1042 , 'IGIRCCBR'
1043 , null
1044 , null
1045 , FALSE -- Is a sub request
1046 , 'P_REQUEST_ID='||p_request_id
1047 , 'P_SOB_ID='||p_sob_id
1048 , 'P_SEG_NAME='||l_segment_name
1049 , 'P_START_DATE='||p_start_date
1050 , 'P_END_DATE='||p_end_date
1051 , 'P_START_PERIOD='||p_start_period
1052 , 'P_END_PERIOD='||p_end_period
1053 , 'P_ACCOUNT_SEGMENT_FROM='||p_account_segment_from
1054 , 'P_ACCOUNT_SEGMENT_TO='||p_account_segment_to
1055 );
1056 IF l_RequestId = 0 THEN
1057 RAISE NOT_SUBMITTED;
1058 END IF;
1059
1060 commit;
1061 RETURN (l_RequestId);
1062
1063 EXCEPTION
1064 WHEN OTHERS THEN
1065 --bug 3199481 fnd logging changes: sdixit: start block
1066 --standard way to handle when-others as per FND logging guidelines
1067 --FND_MESSAGE.SET_NAME('IGI', 'IGI_LOGGING_USER_ERROR'); -- Seeded Message
1068 --retcode := 2;
1069 --errbuf := Fnd_message.get;
1070
1071 IF ( l_unexp_level >= l_debug_level ) THEN
1072
1073 FND_MESSAGE.SET_NAME('IGI','IGI_LOGGING_UNEXP_ERROR');
1074 FND_MESSAGE.SET_TOKEN('CODE',SQLCODE);
1075 FND_MESSAGE.SET_TOKEN('MSG', SQLERRM);
1076 FND_LOG.MESSAGE ( l_unexp_level,'igi.plsql.igircajb.IGIRCABJB.SubmitReconcileReport',TRUE);
1077 END IF;
1078 --bug 3199481 fnd logging changes: sdixit: end block
1079
1080 RETURN (l_RequestId);
1081 end;
1082
1083 --
1084 PROCEDURE ReportOutput
1085 ( p_Report IN ReportParametersType
1086 ) IS
1087 l_Reconcile NUMBER(15);
1088 l_wait BOOLEAN;
1089 l_phase varchar2(20);
1090 l_status varchar2(20);
1091 l_dev_phase varchar2(20);
1092 l_dev_status varchar2(20);
1093 l_message varchar2(240);
1094 begin
1095 --
1096 --
1097
1098 l_reconcile := SubmitReconcileReport
1099 ( p_report.ReqId, p_Report.SetOfBooksId
1100 , p_report.ChartOfAccountsId
1101 , p_report.StartPeriod
1102 , p_report.Endperiod
1103 , p_report.GLDateFrom
1104 , p_report.GLDateTo
1105 , p_report.AccountSegmentFrom
1106 , p_report.AccountSegmentTo
1107 );
1108
1109 --
1113 l_wait := fnd_concurrent.wait_for_request
1110 -- Update each child in turn, waiting for each to complete.
1111 --
1112 IF nvl(l_reconcile, 0) > 0 THEN
1114 ( l_reconcile
1115 , 30
1116 , 0
1117 , l_phase
1118 , l_status
1119 , l_dev_phase
1120 , l_dev_status
1121 , l_message
1122 );
1123 END IF;
1124 commit;
1125 --
1126 EXCEPTION
1127 WHEN OTHERS THEN
1128 --bug 3199481 fnd logging changes: sdixit: start block
1129 --WriteToLogFile( 'Error Submitting Output Reports' );
1130 --standard way to handle when-others as per FND logging guidelines
1131
1132 IF ( l_unexp_level >= l_debug_level ) THEN
1133
1134 FND_MESSAGE.SET_NAME('IGI','IGI_LOGGING_UNEXP_ERROR');
1135 FND_MESSAGE.SET_TOKEN('CODE',SQLCODE);
1136 FND_MESSAGE.SET_TOKEN('MSG', SQLERRM);
1137 FND_LOG.MESSAGE ( l_unexp_level,'igi.plsql.igircajb.IGIRCABJP.ReportQutput',TRUE);
1138 END IF;
1139 --bug 3199481 fnd logging changes: sdixit: end block
1140 RAISE;
1141 end;
1142
1143 PROCEDURE ReportCBR
1144 ( errbuf OUT NOCOPY VARCHAR2
1145 , retcode OUT NOCOPY NUMBER
1146 , p_DataAccessSetId NUMBER
1147 , p_SetOfBooksId NUMBER
1148 , p_CashSetOfBooksId NUMBER -- CBR AR change
1149 , p_ChartOfAccountsId NUMBER
1150 , p_PostedStatus VARCHAR2
1151 , p_PeriodFrom VARCHAR2
1152 , p_PeriodTo VARCHAR2
1153 , p_AccountSegmentFrom VARCHAR2
1154 , p_AccountSegmentTo VARCHAR2
1155 ) IS
1156 l_Report ReportParametersType;
1157 l_accrual_ct Number;
1158 l_cash_ct Number;
1159
1160 FUNCTION CountInterimJournals ( p_request_id in number ) return number
1161 IS
1162 cursor c_e is
1163 select count(*) ct
1164 from igi_ar_journal_interim
1165 where request_id = p_request_id
1166 ;
1167 BEGIN
1168 for l_e in c_e loop
1169 return l_e.ct;
1170 end loop;
1171 return 0;
1172 END CountInterimJournals;
1173
1174
1175 FUNCTION GetStartDate ( p_period_name in varchar2
1176 , p_sob_id in number
1177 )
1178 return DATE
1179 IS
1180 cursor c_date is
1181 select start_date
1182 from gl_period_statuses
1183 where set_of_books_id = p_sob_id
1184 and period_name = p_period_name
1185 and application_id = ( select application_id
1186 from fnd_application
1187 where application_short_name = 'AR'
1188 );
1189 BEGIN
1190 for l_date in c_date loop
1191 return l_date.start_date;
1192 end loop;
1193 return sysdate;
1194 END GetStartDate;
1195
1196 FUNCTION GetEndDate ( p_period_name in varchar2
1197 , p_sob_id in number
1198 )
1199 return DATE
1200 IS
1201 cursor c_date is
1202 select end_date
1203 from gl_period_statuses
1204 where set_of_books_id = p_sob_id
1205 and period_name = p_period_name
1206 and application_id = ( select application_id
1207 from fnd_application
1208 where application_short_name = 'AR'
1209 );
1210 BEGIN
1211 for l_date in c_date loop
1212 return l_date.end_date;
1213 end loop;
1214 return sysdate-1;
1215 END GetEndDate;
1216
1217
1218 BEGIN
1219 --
1220 -- Variables set by parameters passed through from post procedure
1221 --
1222 IF (l_proc_level>= l_debug_level ) THEN
1223 FND_LOG.STRING (l_proc_level , 'igi.plsql.igircajb.IGIRCABJP.ReportCBR',
1224 'Start procedure IGIRCABJP.ReportCBR');
1225 END IF;
1226
1227 l_Report.GlDateFrom := GetStartDate( p_PeriodFrom, p_setofBooksid );
1228 l_Report.GlDateTo := GetEndDate( p_PeriodTo, p_setofBooksid );
1229 l_Report.SetOfBooksId := p_SetOfBooksId;
1230 l_Report.CashSetOfBooksId := p_CashSetOfBooksId;
1231 l_Report.StartPeriod := p_PeriodFrom;
1232 l_Report.EndPeriod := p_PeriodTo;
1233
1234 select currency_code
1235 into l_Report.Currency
1236 from gl_sets_of_books
1237 where set_of_books_id = p_SetOfBooksId
1238 ;
1239
1240 select currency_code
1241 into l_Report.FuncCurr
1242 from gl_sets_of_books
1243 where set_of_books_id = p_CashSetOfBooksId
1244 ;
1245
1246 l_Report.CMApp := 'Y';
1247 l_Report.Adj := 'Y';
1248 l_Report.Trade := 'Y';
1249 l_Report.Misc := 'Y';
1250 l_Report.CCurr := 'Y';
1251
1252 l_Report.DetailByAccount := 'N';
1253 l_Report.DetailByCategory := 'N';
1254 l_Report.SummaryByAccount := 'N';
1258 l_Report.AccountSegmentTo := p_AccountSegmentTo;
1255 l_Report.SummaryByCategory := 'N';
1256 l_Report.ChartOfAccountsID := p_ChartOfAccountsId;
1257 l_Report.AccountSegmentFrom := p_AccountSegmentFrom;
1259
1260
1261 --
1262 -- Get the report request ID
1263 --
1264 FND_PROFILE.GET ('CONC_REQUEST_ID', l_report.ReqId);
1265 IF l_report.ReqId IS NULL -- Not run through conc manager
1266 THEN l_report.ReqId := 0;
1267 END IF;
1268
1269 IF (l_state_level >= l_debug_level ) THEN
1270 FND_LOG.STRING (l_state_level , 'igi.plsql.igircajb.IGIRCABJP.ReportCBR',
1271 ' ConcRequestID '|| l_Report.ReqID );
1272 END IF;
1273
1274 --
1275 -- Variables set from ar_system_parameters
1276 --
1277
1278 /* Check for arc_unalloc_rev_ccid -- *CBRAP*
1279 SELECT sp.arc_cash_sob_id
1280 , sob.currency_code
1281 , sp.arc_unalloc_rev_ccid
1282 INTO l_Report.CashSetOfBooksId
1283 , l_Report.FuncCurr
1284 , l_Report.UnallocatedRevCcid
1285 FROM igi_ar_system_options sp
1286 , gl_sets_of_books sob
1287 WHERE sp.set_of_books_id = p_SetOfBooksID
1288 AND sob.set_of_books_id = sp.set_of_books_id;
1289 Check for arc_unalloc_rev_ccid -- *CBRAP* */
1290
1291 IF l_Report.CashSetOfBooksId is null THEN
1292
1293 IF (l_state_level >= l_debug_level ) THEN
1294 FND_LOG.STRING (l_state_level , 'igi.plsql.igircajb.IGIRCABJP.ReportCBR',
1295 'Accrual Set Of books '|| p_SetOfBooksID );
1296 FND_LOG.STRING (l_state_level , 'igi.plsql.igircajb.IGIRCABJP.ReportCBR',
1297 'Cash Set of Books ID is not input');
1298 END IF;
1299
1300 errbuf := 'Cash Set of Books Parameter is not input';
1301 retcode := 2;
1302 return;
1303 END IF;
1304 --
1305 -- Set Max IDs
1306 --
1307 SELECT ar_cash_receipt_history_s.nextval
1308 , ar_receivable_applications_s.nextval
1309 , ar_misc_cash_distributions_s.nextval
1310 , ar_adjustments_s.nextval
1311 , ra_cust_trx_line_gl_dist_s.nextval
1312 INTO l_Report.NxtCashReceiptHistoryId
1313 , l_Report.NxtReceivableApplicationId
1314 , l_Report.NxtMiscCashDistributionId
1315 , l_Report.NxtAdjustmentId
1316 , l_Report.NxtCustTrxLineGlDistId
1317 FROM dual;
1318
1319 IF (l_state_level >= l_debug_level ) THEN
1320 FND_LOG.STRING (l_state_level , 'igi.plsql.igircajb.IGIRCABJP.ReportCBR',
1321 '----------------BEGIN PARAMETERS-----------------------------------');
1322 FND_LOG.STRING (l_state_level , 'igi.plsql.igircajb.IGIRCABJP.ReportCBR',
1323 ' NxtCashReceiptHistoryId '|| l_Report.NxtCashReceiptHistoryId );
1324 FND_LOG.STRING (l_state_level , 'igi.plsql.igircajb.IGIRCABJP.ReportCBR',
1325 ' NxtReceivableApplicationId '|| l_Report.NxtReceivableApplicationId );
1326 FND_LOG.STRING (l_state_level , 'igi.plsql.igircajb.IGIRCABJP.ReportCBR',
1327 ' NxtMiscCashDistributionId '|| l_Report.NxtMiscCashDistributionId );
1328 FND_LOG.STRING (l_state_level , 'igi.plsql.igircajb.IGIRCABJP.ReportCBR',
1329 ' NxtAdjustmentId '|| l_Report.NxtAdjustmentId );
1330 FND_LOG.STRING (l_state_level , 'igi.plsql.igircajb.IGIRCABJP.ReportCBR',
1331 ' NxtCustTrxLineGlDistId '|| l_Report.NxtCustTrxLineGlDistId );
1332 FND_LOG.STRING (l_state_level , 'igi.plsql.igircajb.IGIRCABJP.ReportCBR',
1333 ' Posted Status '||p_PostedStatus);
1334 FND_LOG.STRING (l_state_level , 'igi.plsql.igircajb.IGIRCABJP.ReportCBR',
1335 ' ----------------END PARAMETERS-----------------------------------');
1336 FND_LOG.STRING (l_state_level , 'igi.plsql.igircajb.IGIRCABJP.ReportCBR',
1337 ' l_Report.GlDateFrom '|| l_Report.GlDateFrom );
1338
1339 FND_LOG.STRING (l_state_level , 'igi.plsql.igircajb.IGIRCABJP.ReportCBR',
1340 ' l_Report.GlDateTo '|| l_Report.GlDateTo );
1341
1342 FND_LOG.STRING (l_state_level , 'igi.plsql.igircajb.IGIRCABJP.ReportCBR',
1343 ' l_Report.SetOfBooksId '|| l_Report.SetOfBooksId );
1344
1345 FND_LOG.STRING (l_state_level , 'igi.plsql.igircajb.IGIRCABJP.ReportCBR',
1346 ' l_Report.CashSetOfBooksId '|| l_Report.CashSetOfBooksId );
1347
1348 FND_LOG.STRING (l_state_level , 'igi.plsql.igircajb.IGIRCABJP.ReportCBR',
1349 ' l_Report.StartPeriod '|| l_Report.StartPeriod );
1350
1351 FND_LOG.STRING (l_state_level , 'igi.plsql.igircajb.IGIRCABJP.ReportCBR',
1352 ' l_Report.EndPeriod '|| l_Report.EndPeriod );
1353
1354 FND_LOG.STRING (l_state_level , 'igi.plsql.igircajb.IGIRCABJP.ReportCBR',
1355 ' l_Report.Currency '|| l_Report.Currency );
1356
1357 FND_LOG.STRING (l_state_level , 'igi.plsql.igircajb.IGIRCABJP.ReportCBR',
1358 ' l_Report.FuncCurr '|| l_Report.FuncCurr );
1359
1360 END IF;
1361
1362
1363 --
1364 -- Hard Coded variables
1365 --
1366 l_Report.ChkBalance := 'N';
1367 l_Report.CreatedBy := fnd_global.user_id;
1368 --
1369 --
1370 IF DebugMode THEN
1371 delete from igi_ar_journal_interim
1372 -- where request_id = l_Report.ReqId
1373 ;
1374 END IF;
1375
1376 IF (l_state_level >= l_debug_level ) THEN
1377 FND_LOG.STRING (l_state_level ,'igi.plsql.igircajb.IGIRCABJP.ReportCBR',
1378 'Synchronize AR data with ARC data -> IGIRCBID.prepare');
1379 END IF;
1380
1381 /* Commented below code for bug 6647672 */
1382 /* IGIRCBID.Prepare ( l_Report.GlDateFrom, l_Report.GlDateTo, l_Report.GlDateFrom,
1383 l_Report.SetOfBooksId, l_Report.CashSetOfBooksId); */
1384
1385
1386 IF (l_state_level >= l_debug_level ) THEN
1387 FND_LOG.STRING (l_state_level , 'igi.plsql.igircajb.IGIRCABJP.ReportCBR',
1388 'Get accrual data into IGI_AR_JOURNAL_INTERIM ');
1389 END IF;
1390
1391 IF l_Report.PostedDateFrom IS NULL THEN
1392 l_Report.PostedDateFrom := to_date('01-01-1952', 'DD-MM-YYYY');
1393 END IF;
1394 IF l_Report.PostedDateTo IS NULL THEN
1395 l_Report.PostedDateTo := SYSDATE;
1396 END IF;
1397
1398
1399 l_report.CallingMode := 'ARC';
1400 l_report.PostedStatus := 'POSTED';
1401 Report( l_Report ); -- Get Accrual Data
1402 l_accrual_ct := CountInterimJournals ( l_Report.reqid );
1403
1404 IF (l_state_level >= l_debug_level ) THEN
1405 FND_LOG.STRING (l_state_level , 'igi.plsql.igircajb.IGIRCABJP.ReportCBR',
1406 'Number of records processed Primary Ledger (accrual) is '||to_char(l_accrual_ct) );
1407 END IF;
1408
1409 /* Commented below code for bug 6647672 */
1410 /* l_Report.CallingMode := 'CBR';
1411 l_Report.PostedStatus := 'UNPOSTED';
1412 IGIRCBJP.Report ( l_report ); -- Get Cash Data
1413 l_cash_ct := CountInterimJournals ( l_Report.reqid );
1414 l_cash_ct := l_cash_ct - l_accrual_ct;
1415
1416 IF (l_state_level >= l_debug_level ) THEN
1417 FND_LOG.STRING (l_state_level , 'igi.plsql.igircajb.IGIRCABJP.ReportCBR',
1418 'Number of records processed Secondary Ledger (Cash) is '||to_char(l_cash_ct) );
1419 END IF; */
1420
1421 IF (l_state_level >= l_debug_level ) THEN
1422 FND_LOG.STRING (l_state_level ,'igi.plsql.igircajb.IGIRCABJP.ReportCBR','');
1423 FND_LOG.STRING (l_state_level ,'igi.plsql.igircajb.IGIRCABJP.ReportCBR',
1424 'Create data into CBR interface table -> IGI_CBR_ARC_INTERFACE_PKG.Insert_rows');
1425 END IF;
1426
1427
1428 IGI_CBR_ARC_INTERFACE_PKG.Insert_Rows (l_Report.reqid, l_Report.CashSetOfBooksId);
1429
1430 IF (l_state_level >= l_debug_level ) THEN
1431 FND_LOG.STRING (l_state_level , 'igi.plsql.igircajb.IGIRCABJP.ReportCBR',
1432 'Submit the Reports');
1433 END IF;
1434
1435 ReportOutput (l_Report);
1436
1437 IF (l_state_level >= l_debug_level ) THEN
1438 FND_LOG.STRING (l_state_level , 'igi.plsql.igircajb.IGIRCABJP.ReportCBR',
1439 'Delete records from IGI_AR_JOURNAL_INTERIM (IF not debug)');
1440 END IF;
1441
1442
1443 IF NOT DebugMode THEN
1444 delete from igi_ar_journal_interim
1445 where request_id = l_Report.ReqId
1446 ;
1447 END IF;
1448
1449 delete from igi_cbr_arc_interface
1450 where request_id = l_Report.ReqId
1451 ;
1452 commit;
1453
1454 errbuf := 'Successful Completion';
1455 retcode := 0;
1456
1457 EXCEPTION
1458 WHEN OTHERS THEN
1459
1460 FND_MESSAGE.SET_NAME('IGI', 'IGI_LOGGING_USER_ERROR'); -- Seeded Message
1461 retcode := 2;
1462 errbuf := Fnd_message.get;
1463
1464 IF ( l_unexp_level >= l_debug_level ) THEN
1465
1466 FND_MESSAGE.SET_NAME('IGI','IGI_LOGGING_UNEXP_ERROR');
1467 FND_MESSAGE.SET_TOKEN('CODE',SQLCODE);
1468 FND_MESSAGE.SET_TOKEN('MSG', SQLERRM);
1469 FND_LOG.MESSAGE ( l_unexp_level,'igi.plsql.igircajb.IGIRCABJP.ReportCBR',TRUE);
1470 END IF;
1471
1472 RAISE;
1473 END;
1474 BEGIN
1475
1476 DebugMode := FALSE;
1477
1478 l_debug_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
1479 l_state_level := FND_LOG.LEVEL_STATEMENT;
1480 l_proc_level := FND_LOG.LEVEL_PROCEDURE;
1481 l_event_level := FND_LOG.LEVEL_EVENT;
1482 l_excep_level := FND_LOG.LEVEL_EXCEPTION;
1483 l_error_level := FND_LOG.LEVEL_ERROR;
1484 l_unexp_level := FND_LOG.LEVEL_UNEXPECTED;
1485
1486 END IGIRCABJP;