[Home] [Help]
PACKAGE BODY: APPS.ARP_AUTO_ACCOUNTING_BR
Source
1 PACKAGE BODY ARP_AUTO_ACCOUNTING_BR AS
2 /* $Header: ARTEAABB.pls 120.9 2005/11/14 06:58:28 apandit ship $ */
3
4 ------------------------------------------------------------------------
5 -- Inherited from other packages
6 ------------------------------------------------------------------------
7 PG_DEBUG varchar2(1) := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
8 g_ae_sys_rec ae_sys_rec_type;
9
10 --
11 -- Linefeed character
12 --
13 CRLF CONSTANT VARCHAR2(1) := arp_global.CRLF;
14
15 YES CONSTANT VARCHAR2(1) := arp_global.YES;
16 NO CONSTANT VARCHAR2(1) := arp_global.NO;
17
18 MSG_LEVEL_BASIC CONSTANT BINARY_INTEGER := arp_global.MSG_LEVEL_BASIC;
19 MSG_LEVEL_TIMING CONSTANT BINARY_INTEGER := arp_global.MSG_LEVEL_TIMING;
20 MSG_LEVEL_DEBUG CONSTANT BINARY_INTEGER := arp_global.MSG_LEVEL_DEBUG;
21 MSG_LEVEL_DEBUG2 CONSTANT BINARY_INTEGER := arp_global.MSG_LEVEL_DEBUG2;
22 MSG_LEVEL_DEVELOP CONSTANT BINARY_INTEGER := arp_global.MSG_LEVEL_DEVELOP;
23
24 MAX_CURSOR_CACHE_SIZE CONSTANT BINARY_INTEGER := 20;
25 MAX_CCID_CACHE_SIZE CONSTANT BINARY_INTEGER := 1000;
26 MAX_SEGMENT_CACHE_SIZE CONSTANT BINARY_INTEGER := 1000;
27
28 G_MAX_DATE CONSTANT DATE:= arp_global.G_MAX_DATE;
29 G_MIN_DATE CONSTANT DATE:= to_date('01-01-1952','DD-MM-YYYY');
30
31 -- This record holds general information used by autoaccounting and
32 -- credit memo module. Passed as argument to most functions/procs.
33 --
34 system_info arp_trx_global.system_info_rec_type :=
35 arp_trx_global.system_info;
36
37 --
38 -- This record holds profile information used by autoaccounting and
39 -- credit memo module. Passed as argument to most functions/procs.
40 --
41 profile_info arp_trx_global.profile_rec_type :=
42 arp_trx_global.profile_info;
43
44 --
45 -- This record holds accounting flexfield information used by BR
46 -- autoaccounting. Passed as argument to most functions/procs.
47 --
48 flex_info arp_trx_global.acct_flex_info_rec_type :=
49 arp_trx_global.flex_info;
50
51 ------------------------------------------------------------------------
52 -- Private types
53 ------------------------------------------------------------------------
54 TYPE autoacc_rec_type IS RECORD
55 (
56 type ra_account_defaults.type%type,
57 segment ra_account_default_segments.segment%type,
58 table_name ra_account_default_segments.table_name%type,
59 constant ra_account_default_segments.constant%type
60 );
61
62 --
63 -- Autoaccounting definintion cache
64 --
65 REC CONSTANT VARCHAR2(14) := 'REC';
66 UNPAIDREC CONSTANT VARCHAR2(14) := 'UNPAIDREC';
67 FACTOR CONSTANT VARCHAR2(14) := 'FACTOR';
68 REMITTANCE CONSTANT VARCHAR2(14) := 'REMITTANCE';
69
70 --
71 -- Maximum of 30 enabled segments for the accounting flex
72 -- so the gap between offsets is sufficient
73 --
74 rec_offset CONSTANT BINARY_INTEGER := 0;
75 unpaidrec_offset CONSTANT BINARY_INTEGER := 50;
76 factor_offset CONSTANT BINARY_INTEGER := 100;
77 remittance_offset CONSTANT BINARY_INTEGER := 150;
78 --
79 rec_count BINARY_INTEGER := 0;
80 unpaidrec_count BINARY_INTEGER := 0;
81 factor_count BINARY_INTEGER := 0;
82 remittance_count BINARY_INTEGER := 0;
83 --
84 rev_count BINARY_INTEGER := 0;
85 frt_count BINARY_INTEGER := 0;
86 tax_count BINARY_INTEGER := 0;
87 unbill_count BINARY_INTEGER := 0;
88 unearn_count BINARY_INTEGER := 0;
89 suspense_count BINARY_INTEGER := 0;
90
91 TYPE segment_table_type IS
92 TABLE OF ra_account_default_segments.segment%type
93 INDEX BY BINARY_INTEGER;
94 --
95 TYPE table_table_type IS
96 TABLE OF ra_account_default_segments.table_name%type
97 INDEX BY BINARY_INTEGER;
98 --
99 TYPE const_table_type IS
100 TABLE OF ra_account_default_segments.constant%type
101 INDEX BY BINARY_INTEGER;
102 --
103 autoacc_def_segment_t segment_table_type;
104 autoacc_def_table_t table_table_type;
105 autoacc_def_const_t const_table_type;
106
107 --
108 -- trx_type cache
109 --
110 TYPE trx_type_rec_table_type IS
111 TABLE OF BINARY_INTEGER
112 INDEX BY BINARY_INTEGER;
113 trx_type_rec_t trx_type_rec_table_type;
114
115 TYPE trx_type_unpaidrec_table_type IS
116 TABLE OF BINARY_INTEGER
117 INDEX BY BINARY_INTEGER;
118 trx_type_unpaidrec_t trx_type_unpaidrec_table_type;
119
120 TYPE trx_type_factor_table_type IS
121 TABLE OF BINARY_INTEGER
122 INDEX BY BINARY_INTEGER;
123 trx_type_factor_t trx_type_factor_table_type;
124
125 TYPE trx_type_remittance_table_type IS
126 TABLE OF BINARY_INTEGER
127 INDEX BY BINARY_INTEGER;
128 trx_type_remittance_t trx_type_remittance_table_type;
129
130 --
131 -- site_uses cache
132 --
133 TYPE site_use_rec_table_type IS
134 TABLE OF BINARY_INTEGER
135 INDEX BY BINARY_INTEGER;
136 site_use_rec_t site_use_rec_table_type;
137
138 TYPE site_use_unpaidrec_table_type IS
139 TABLE OF BINARY_INTEGER
140 INDEX BY BINARY_INTEGER;
141 site_use_unpaidrec_t site_use_unpaidrec_table_type;
142
143 TYPE site_use_factor_table_type IS
144 TABLE OF BINARY_INTEGER
145 INDEX BY BINARY_INTEGER;
146 site_use_factor_t site_use_factor_table_type;
147
148 TYPE site_use_remittance_table_type IS
149 TABLE OF BINARY_INTEGER
150 INDEX BY BINARY_INTEGER;
151 site_use_remittance_t site_use_remittance_table_type;
152
153 --
154 -- payment method bank account cache
155 -- (pym_bact) cache by bank account
156 --
157 TYPE pym_bact_factor_table_type IS
158 TABLE OF BINARY_INTEGER
159 INDEX BY BINARY_INTEGER;
160 pym_bact_factor_t pym_bact_factor_table_type;
161
162 TYPE pym_bact_remittance_table_type IS
163 TABLE OF BINARY_INTEGER
164 INDEX BY BINARY_INTEGER;
165 pym_bact_remittance_t pym_bact_remittance_table_type;
166
167 --
168 -- code combination,segment and date caches
169 --
170 TYPE autoacc_cache_seg_type IS
171 TABLE OF varchar2(929)
172 INDEX BY BINARY_INTEGER;
173
174 TYPE autoacc_cache_id_type IS
175 TABLE OF BINARY_INTEGER
176 INDEX BY BINARY_INTEGER;
177
178 TYPE autoacc_cache_date_type IS
179 TABLE OF DATE
180 INDEX BY BINARY_INTEGER;
181
182 TYPE segment_type IS
183 TABLE OF gl_code_combinations.segment1%type
184 INDEX BY BINARY_INTEGER;
185
186 TYPE cursor_attr_tbl_type IS
187 TABLE OF VARCHAR2(100)
188 INDEX BY BINARY_INTEGER;
189
190 TYPE cursor_tbl_type IS
191 TABLE OF BINARY_INTEGER
192 INDEX BY BINARY_INTEGER;
193
194 --
195 -- Misc
196 --
197
198 -- To store segment values for binding
199 --
200 TYPE seg_table_type IS
201 TABLE OF gl_code_combinations.segment1%type
202 INDEX BY binary_integer;
203 --
204 TYPE ccid_rec_type IS RECORD
205 (
206 trx_type_ccid_rec BINARY_INTEGER := -1,
207 trx_type_ccid_unpaidrec BINARY_INTEGER := -1,
208 trx_type_ccid_factor BINARY_INTEGER := -1,
209 trx_type_ccid_remittance BINARY_INTEGER := -1,
210 site_use_ccid_rec BINARY_INTEGER := -1,
211 site_use_ccid_unpaidrec BINARY_INTEGER := -1,
212 site_use_ccid_factor BINARY_INTEGER := -1,
213 site_use_ccid_remittance BINARY_INTEGER := -1,
214 pym_bact_ccid_factor BINARY_INTEGER := -1,
215 pym_bact_ccid_remittance BINARY_INTEGER := -1
216 );
217
218 --
219 -- To hold values fetched from the Select stmt
220 --
221 TYPE select_rec_type IS RECORD
222 (
223 customer_trx_id BINARY_INTEGER,
224 cust_trx_type_id BINARY_INTEGER,
225 site_use_id BINARY_INTEGER,
226 drawee_id BINARY_INTEGER,
227 bill_to_site_use_id BINARY_INTEGER,
228 br_unpaid_flag VARCHAR2(1),
229 transaction_history_id BINARY_INTEGER,
230 batch_id BINARY_INTEGER,
231 gl_date VARCHAR2(12), -- Julian format
232 source_type VARCHAR2(20), --source type
233 amount NUMBER,
234 acctd_amount NUMBER,
235 currency_code VARCHAR2(15),
236 currency_conversion_rate NUMBER,
237 currency_conversion_type VARCHAR2(30),
238 currency_conversion_date VARCHAR2(12), -- Julian format
239 receipt_method_id BINARY_INTEGER,
240 bank_account_id BINARY_INTEGER,
241 concatenated_segments VARCHAR2(240),
242 code_combination_id BINARY_INTEGER,
243 br_unpaid_ccid BINARY_INTEGER
244 );
245
246
247 -- set invalid segvalue to null
248 --
249 INVALID_SEGMENT CONSTANT VARCHAR2(20) := '';
250
251 --
252 -- Cursor handles
253 --
254
255 -- Cursor for finding a ccid given segment values
256 --
257 ccid_reader_c INTEGER;
258
259 --
260 -- CCID Validation date
261 --
262 validation_date DATE := TRUNC(SYSDATE);
263
264
265 -- User-defined exceptions
266 --
267 invalid_account_class EXCEPTION;
268 invalid_table_name EXCEPTION; -- in autoacc def
269 error_defaulting_gl_date EXCEPTION;
270
271
272 --
273 -- Translated error messages
274 --
275 MSG_COMPLETE_REC_ACCOUNT varchar2(2000);
276 MSG_COMPLETE_UNP_ACCOUNT varchar2(2000);
277 MSG_COMPLETE_FAC_ACCOUNT varchar2(2000);
278 MSG_COMPLETE_REM_ACCOUNT varchar2(2000);
279
280 MSG_FLEX_POSTING_NOT_ALLOWED varchar2(2000);
281 MSG_FLEX_NO_PARENT_ALLOWED varchar2(2000);
282
283
284 I CONSTANT VARCHAR2(1) := 'I';
285 U CONSTANT VARCHAR2(1) := 'U';
286 D CONSTANT VARCHAR2(1) := 'D';
287 G CONSTANT VARCHAR2(1) := 'G';
288
289 -- code combination segment, ID, Start and End Date caches
290 autoacc_rec_id_cache autoacc_cache_id_type;
291 autoacc_rec_seg_cache autoacc_cache_seg_type;
292 autoacc_rec_st_date_cache autoacc_cache_date_type;
293 autoacc_rec_end_date_cache autoacc_cache_date_type;
294
295 autoacc_unp_id_cache autoacc_cache_id_type;
296 autoacc_unp_seg_cache autoacc_cache_seg_type;
297 autoacc_unp_st_date_cache autoacc_cache_date_type;
298 autoacc_unp_end_date_cache autoacc_cache_date_type;
299
300 autoacc_factor_id_cache autoacc_cache_id_type;
301 autoacc_factor_seg_cache autoacc_cache_seg_type;
302 autoacc_factor_st_date_cache autoacc_cache_date_type;
303 autoacc_factor_end_date_cache autoacc_cache_date_type;
304
305 autoacc_rem_id_cache autoacc_cache_id_type;
306 autoacc_rem_seg_cache autoacc_cache_seg_type;
307 autoacc_rem_st_date_cache autoacc_cache_date_type;
308 autoacc_rem_end_date_cache autoacc_cache_date_type;
309
310 cursor_attr_cache cursor_attr_tbl_type;
311 cursor_cache cursor_tbl_type;
312
313 segment1_cache segment_type;
314 segment2_cache segment_type;
315 segment3_cache segment_type;
316 segment4_cache segment_type;
317 segment5_cache segment_type;
318 segment6_cache segment_type;
319 segment7_cache segment_type;
320 segment8_cache segment_type;
321 segment9_cache segment_type;
322 segment10_cache segment_type;
323 segment11_cache segment_type;
324 segment12_cache segment_type;
325 segment13_cache segment_type;
326 segment14_cache segment_type;
327 segment15_cache segment_type;
328 segment16_cache segment_type;
329 segment17_cache segment_type;
330 segment18_cache segment_type;
331 segment19_cache segment_type;
332 segment20_cache segment_type;
333 segment21_cache segment_type;
334 segment22_cache segment_type;
335 segment23_cache segment_type;
336 segment24_cache segment_type;
337 segment25_cache segment_type;
338 segment26_cache segment_type;
339 segment27_cache segment_type;
340 segment28_cache segment_type;
341 segment29_cache segment_type;
342 segment30_cache segment_type;
343
344
345 ----------------------------------------------------------------------------
346 -- Covers
347 ----------------------------------------------------------------------------
348 PROCEDURE debug( p_line IN VARCHAR2 ) IS
349 BEGIN
350 arp_util.debug( p_line );
351 END;
352
353 PROCEDURE debug( p_str VARCHAR2, p_print_level BINARY_INTEGER ) IS
354 BEGIN
355 arp_util.debug( p_str, p_print_level );
356 END;
357
358 PROCEDURE enable_debug IS
359 BEGIN
360 arp_util.enable_debug;
361 END;
362
363 PROCEDURE disable_debug IS
364 BEGIN
365 arp_util.disable_debug;
366 END;
367
368 PROCEDURE print_fcn_label( p_label VARCHAR2 ) IS
369 BEGIN
370 arp_util.print_fcn_label( p_label );
371 END;
372
373 PROCEDURE print_fcn_label2( p_label VARCHAR2 ) IS
374 BEGIN
375 arp_util.print_fcn_label2( p_label );
376 END;
377
378 PROCEDURE close_cursor( p_cursor_handle IN OUT NOCOPY INTEGER ) IS
379 BEGIN
380 arp_util.close_cursor( p_cursor_handle );
381 END;
382
383
384
385 ----------------------------------------------------------------------------
386 -- Procedures and Functions
387 ----------------------------------------------------------------------------
388
389
390 PROCEDURE put_message_on_stack(
391 p_message_text varchar2,
392 p_invalid_value varchar2) IS
393
394 BEGIN
395
396 FND_MESSAGE.set_name( 'AR', 'GENERIC_MESSAGE' );
397 FND_MESSAGE.set_token( 'GENERIC_TEXT', p_message_text );
398
399 END put_message_on_stack;
400
401
402 ----------------------------------------------------------------------------
403 PROCEDURE get_error_message_text is
404
405 l_application_id NUMBER := 222;
406 l_msg_name VARCHAR2(100);
407
408 BEGIN
409
410 print_fcn_label( 'arp_auto_accounting_br.get_error_message_text()+' );
411
412 l_msg_name := 'AR_COMPLETE_BR_REC_ACCOUNT';
413 fnd_message.set_name('AR', l_msg_name);
414 MSG_COMPLETE_REC_ACCOUNT := fnd_message.get;
415
416 ----
417 l_msg_name := 'AR_COMPLETE_BR_UNPAID_ACCOUNT';
418 fnd_message.set_name('AR', l_msg_name);
419 MSG_COMPLETE_UNP_ACCOUNT := fnd_message.get;
420
421 ----
422 l_msg_name := 'AR_COMPLETE_BR_FACTOR';
423 fnd_message.set_name('AR', l_msg_name);
424 MSG_COMPLETE_FAC_ACCOUNT := fnd_message.get;
425
426 ----
427 l_msg_name := 'AR_COMPLETE_BR_REMITTANCE';
428 fnd_message.set_name('AR', l_msg_name);
429 MSG_COMPLETE_REM_ACCOUNT := fnd_message.get;
430
431 ----
432 l_msg_name := 'RA_POSTING_NOT_ALLOWED';
433 fnd_message.set_name('AR', l_msg_name);
434 MSG_FLEX_POSTING_NOT_ALLOWED := fnd_message.get;
435
436 ----
437 l_msg_name := 'FLEX-No Parent';
438 fnd_message.set_name('AR', l_msg_name);
439 MSG_FLEX_NO_PARENT_ALLOWED := fnd_message.get;
440
441 -- print
442 debug( ' This is a list of messages potentially used by Autoaccounting ');
443 debug( ' MSG_COMPLETE_REC_ACCOUNT='||MSG_COMPLETE_REC_ACCOUNT,
444 MSG_LEVEL_DEBUG );
445 debug( ' MSG_COMPLETE_UNP_ACCOUNT='||MSG_COMPLETE_UNP_ACCOUNT,
446 MSG_LEVEL_DEBUG );
447 debug( ' MSG_COMPLETE_FAC_ACCOUNT='||MSG_COMPLETE_FAC_ACCOUNT,
448 MSG_LEVEL_DEBUG );
449 debug( ' MSG_COMPLETE_REM_ACCOUNT='||MSG_COMPLETE_REM_ACCOUNT,
450 MSG_LEVEL_DEBUG );
451
452 debug( ' MSG_FLEX_POSTING_NOT_ALLOWED='||MSG_FLEX_POSTING_NOT_ALLOWED,
453 MSG_LEVEL_DEBUG );
454 debug( ' MSG_FLEX_NO_PARENT_ALLOWED='||MSG_FLEX_NO_PARENT_ALLOWED,
455 MSG_LEVEL_DEBUG );
456 debug( ' End List of messages used by Autoaccounting ');
457
458 print_fcn_label( 'arp_auto_accounting_br.get_error_message_text()-' );
459
460 EXCEPTION
461 WHEN OTHERS THEN
462 debug('EXCEPTION: arp_auto_accounting_br.get_error_message_text()');
463 RAISE;
464 END get_error_message_text;
465
466 ----------------------------------------------------------------------------
467 PROCEDURE dump_info IS
468 BEGIN
469
470 -- sys info
471 debug( ' coa_id='||to_char(system_info.chart_of_accounts_id),
472 MSG_LEVEL_DEBUG);
473 debug( ' curr='||system_info.base_currency, MSG_LEVEL_DEBUG);
474 debug( ' prec='||to_char(system_info.base_precision), MSG_LEVEL_DEBUG);
475 debug( ' mau='||to_char(system_info.base_min_acc_unit), MSG_LEVEL_DEBUG);
476
477 -- profile info
478 debug( ' login_id='||profile_info.conc_login_id, MSG_LEVEL_DEBUG );
479 debug( ' program_id='||profile_info.conc_program_id, MSG_LEVEL_DEBUG );
480 debug( ' user_id='||profile_info.user_id, MSG_LEVEL_DEBUG );
481
482 -- flex info
483 debug( ' nsegs='||to_char(flex_info.number_segments), MSG_LEVEL_DEBUG);
484 debug( ' delim='||flex_info.delim, MSG_LEVEL_DEBUG);
485
486 EXCEPTION
487 WHEN OTHERS THEN
488 debug('EXCEPTION: arp_auto_accounting_br.dump_info()', MSG_LEVEL_BASIC);
489 debug(SQLERRM, MSG_LEVEL_BASIC);
490 RAISE;
491 END dump_info;
492
493 ----------------------------------------------------------------------------
494 --
495 -- PROCEDURE NAME: dump_ccid_record
496 --
497 -- DECSRIPTION:
498 -- Prints contents of the ccid record
499 --
500 -- ARGUMENTS:
501 -- IN:
502 -- ccid_record
503 --
504 -- IN/OUT:
505 --
506 -- OUT:
507 --
508 -- NOTES:
509 --
510 -- HISTORY:
511 --
512 PROCEDURE dump_ccid_record( p_ccid_record IN ccid_rec_type ) IS
513 BEGIN
514 print_fcn_label( 'arp_auto_accounting_br.dump_ccid_record()+' );
515
516 debug( ' Dumping CCID record:', MSG_LEVEL_DEBUG );
517
518 debug( ' trx_type_ccid_rec=' ||
519 to_char(p_ccid_record.trx_type_ccid_rec ), MSG_LEVEL_DEBUG );
520 debug( ' trx_type_ccid_unpaidrec=' ||
521 to_char(p_ccid_record.trx_type_ccid_unpaidrec ), MSG_LEVEL_DEBUG );
522 debug( ' trx_type_ccid_factor=' ||
523 to_char(p_ccid_record.trx_type_ccid_factor ), MSG_LEVEL_DEBUG );
524 debug( ' trx_type_ccid_remittance=' ||
525 to_char(p_ccid_record.trx_type_ccid_remittance ), MSG_LEVEL_DEBUG );
526 debug( ' site_use_ccid_rec=' ||
527 to_char(p_ccid_record.site_use_ccid_rec ), MSG_LEVEL_DEBUG );
528 debug( ' site_use_ccid_unpaidrec=' ||
529 to_char(p_ccid_record.site_use_ccid_unpaidrec ), MSG_LEVEL_DEBUG );
530 debug( ' site_use_ccid_factor=' ||
531 to_char(p_ccid_record.site_use_ccid_factor ), MSG_LEVEL_DEBUG );
532 debug( ' site_use_ccid_remittance=' ||
533 to_char(p_ccid_record.site_use_ccid_remittance ), MSG_LEVEL_DEBUG );
534 debug( ' pym_bact_ccid_factor=' ||
535 to_char(p_ccid_record.pym_bact_ccid_factor ), MSG_LEVEL_DEBUG );
536 debug( ' pym_bact_ccid_remittance=' ||
537 to_char(p_ccid_record.pym_bact_ccid_remittance ), MSG_LEVEL_DEBUG );
538
539 print_fcn_label( 'arp_auto_accounting_br.dump_ccid_record()-' );
540
541 END dump_ccid_record;
542
543 ----------------------------------------------------------------------------
544 --
545 -- PROCEDURE NAME: load_autoacc_def
546 --
547 -- DECSRIPTION:
548 -- Retrieves the following autoaccounting information for each
549 -- account class:
550 -- - segment column name
551 -- - table name
552 -- - constant
553 -- and stores them in plsql tables for future use by autoaccounting.
554 -- Called on package initialization.
555 --
556 -- ARGUMENTS:
557 -- IN:
558 --
559 -- IN/OUT:
560 --
561 -- OUT:
562 --
563 -- NOTES:
564 --
565 -- HISTORY:
566 --
567 PROCEDURE load_autoacc_def IS
568 l_rec_index BINARY_INTEGER := rec_offset;
569 l_unpaidrec_index BINARY_INTEGER := unpaidrec_offset;
570 l_factor_index BINARY_INTEGER := factor_offset;
571 l_remittance_index BINARY_INTEGER := remittance_offset;
572 --begin anuj
573 /* Multi-Org Access Control Changes for SSA;Begin;anukumar;11/01/2002*/
574 i_cnt BINARY_INTEGER :=0;
575 /* Multi-Org Access Control Changes for SSA;Begin;anukumar;11/01/2002*/
576 --end anuj
577
578 CURSOR autoacc IS
579 SELECT
580 ad.type type,
581 ads.segment segment,
582 upper(ads.table_name) table_name,
583 ads.constant constant
584 FROM
585 ra_account_default_segments ads,
586 ra_account_defaults ad
587 WHERE ad.gl_default_id = ads.gl_default_id
588 AND ad.type in
589 (
590 'BR_REC', 'BR_UNPAID_REC', 'BR_FACTOR', 'BR_REMITTANCE'
591 )
592 ORDER BY
593 type,
594 segment_num;
595
596
597 PROCEDURE load( p_table_index IN OUT NOCOPY BINARY_INTEGER,
598 p_cnt IN OUT NOCOPY BINARY_INTEGER,
599 p_autoacc_rec IN autoacc%rowtype) IS
600 BEGIN
601 autoacc_def_segment_t(p_table_index) := p_autoacc_rec.segment;
602 autoacc_def_table_t(p_table_index) := p_autoacc_rec.table_name;
603 autoacc_def_const_t(p_table_index):= p_autoacc_rec.constant;
604 p_table_index := p_table_index + 1;
605 p_cnt := p_cnt + 1;
606 END;
607
608 BEGIN
609 print_fcn_label( 'arp_auto_accounting_br.load_autoacc_def()+' );
610 --begin anuj
611 /* Multi-Org Access Control Changes for SSA;Begin;anukumar;11/01/2002*/
612 rev_count := 0;
613 rec_count := 0;
614 frt_count := 0;
615 tax_count := 0;
616 unbill_count := 0;
617 unearn_count := 0;
618 suspense_count := 0;
619
620 i_cnt := 0;
621 while i_cnt <=300 LOOP
622
623 If (autoacc_def_segment_t.exists(i_cnt)) then
624 autoacc_def_segment_t.delete;
625 End if;
626 If (autoacc_def_table_t.exists(i_cnt) ) then
627 autoacc_def_table_t.delete;
628 End if;
629 If (autoacc_def_const_t.exists(i_cnt) ) then
630 autoacc_def_const_t.delete;
631 End if;
632 i_cnt := 50+i_cnt;
633 End Loop;
634 /* Multi-Org Access Control Changes for SSA;Begin;anukumar;11/01/2002*/
635 --end anuj
636
637 FOR autoacc_rec IN autoacc LOOP
638 IF( autoacc_rec.type = 'BR_REC' ) then
639 load(l_rec_index, rec_count, autoacc_rec);
640 ELSIF( autoacc_rec.type = 'BR_UNPAID_REC' ) then
641 load(l_unpaidrec_index, unpaidrec_count, autoacc_rec);
642 ELSIF( autoacc_rec.type = 'BR_FACTOR' ) then
643 load(l_factor_index, factor_count, autoacc_rec);
644 ELSIF( autoacc_rec.type = 'BR_REMITTANCE' ) then
645 load(l_remittance_index, remittance_count, autoacc_rec);
646 END IF;
647 END LOOP;
648
649 print_fcn_label( 'arp_auto_accounting_br.load_autoacc_def()-' );
650
651 EXCEPTION
652 WHEN OTHERS THEN
653 debug('EXCEPTION: arp_auto_accounting_br.load_autoacc_def()',
654 MSG_LEVEL_BASIC);
655 debug(SQLERRM, MSG_LEVEL_BASIC);
656 RAISE;
657 END load_autoacc_def ;
658
659 ----------------------------------------------------------------------------
660 --
661 -- FUNCTION NAME: query_autoacc_def
662 --
663 -- DECSRIPTION:
664 -- Determines whether any of a given account class segments are based
665 -- on a given table. This is not used directly by Autoaccounting, however
666 -- has been retained and can be used for test purposes
667 --
668 -- ARGUMENTS:
669 -- IN:
670 -- account_class:
671 -- 'REC', 'UNPREC', 'FACTOR', 'REMITTANCE'
672 -- table_name
673 --
674 -- IN/OUT:
675 --
676 -- OUT:
677 --
678 -- RETURNS:
679 -- TRUE if account class is based on specified table
680 -- FALSE otherwise
681 --
682 -- NOTES:
683 -- check particular account class in cache
684 --
685 --
686 -- HISTORY:
687 --
688 FUNCTION query_autoacc_def( p_account_class IN VARCHAR2,
689 p_table_name IN VARCHAR2 )
690 RETURN BOOLEAN IS
691
692 retval BOOLEAN;
693 l_account_class VARCHAR2(20);
694
695 FUNCTION search_table( p_offset IN BINARY_INTEGER,
696 p_cnt IN BINARY_INTEGER )
697 RETURN BOOLEAN is
698 BEGIN
699 FOR i IN 0..p_cnt-1 LOOP
700 IF( autoacc_def_table_t( p_offset + i ) = p_table_name ) THEN
701 return TRUE;
702 END IF;
703 END LOOP;
704
705 RETURN false;
706 END;
707
708 BEGIN
709 print_fcn_label( 'arp_auto_accounting_br.query_autoacc_def()+' );
710
711 g_error_buffer := NULL;
712
713 --
714 -- Adjust account_class to proper string
715 --
716 l_account_class := p_account_class;
717
718 IF l_account_class = REC THEN
719 retval := search_table( rec_offset, rec_count);
720 ELSIF l_account_class = UNPAIDREC THEN
721 retval := search_table( unpaidrec_offset, unpaidrec_count);
722 ELSIF l_account_class = FACTOR THEN
723 retval := search_table( factor_offset, factor_count);
724 ELSIF l_account_class = REMITTANCE THEN
725 retval := search_table( remittance_offset, remittance_count);
726 ELSE
727 g_error_buffer := 'Invalid account class';
728 debug( 'EXCEPTION: '||g_error_buffer, MSG_LEVEL_BASIC );
729 RAISE invalid_account_class;
730 END IF;
731
732 print_fcn_label( 'arp_auto_accounting_br.query_autoacc_def()-' );
733
734 RETURN retval;
735
736
737 EXCEPTION
738 WHEN OTHERS THEN
739 debug('EXCEPTION: arp_auto_accounting_br.query_autoacc_def('
740 || p_account_class || ', '
741 || p_table_name ||')', MSG_LEVEL_BASIC);
742 debug(SQLERRM, MSG_LEVEL_BASIC);
743 RAISE;
744 END query_autoacc_def;
745
746
747 ----------------------------------------------------------------------------
748 --
749 -- PROCEDURE NAME: get_trx_type_ccids
750 --
751 -- DECSRIPTION:
752 -- Retrieves default ccids from the table ra_cust_trx_types
753 -- for a given trx type.
754 --
755 -- ARGUMENTS:
756 -- IN:
757 -- trx_type_id
758 --
759 -- IN/OUT:
760 -- ccid_rec
761 -- ccid_unpaidrec
762 -- ccid_factor
763 -- ccid_remittance
764 --
765 -- OUT:
766 --
767 -- NOTES:
768 --
769 -- HISTORY:
770 --
771 PROCEDURE get_trx_type_ccids( p_trx_type_id IN BINARY_INTEGER,
772 p_ccid_rec IN OUT NOCOPY BINARY_INTEGER,
773 p_ccid_unpaidrec IN OUT NOCOPY BINARY_INTEGER,
774 p_ccid_factor IN OUT NOCOPY BINARY_INTEGER,
775 p_ccid_remittance IN OUT NOCOPY BINARY_INTEGER) IS
776 BEGIN
777 print_fcn_label2( 'arp_auto_accounting_br.get_trx_type_ccids()+' );
778
779 --
780 -- initialize
781 --
782 p_ccid_rec := -1;
783 p_ccid_unpaidrec := -1;
784 p_ccid_factor := -1;
785 p_ccid_remittance := -1;
786
787 BEGIN
788 -- see if available in cache
789 --
790 p_ccid_rec := trx_type_rec_t( p_trx_type_id );
791 p_ccid_unpaidrec := trx_type_unpaidrec_t( p_trx_type_id );
792 p_ccid_factor := trx_type_factor_t( p_trx_type_id );
793 p_ccid_remittance := trx_type_remittance_t( p_trx_type_id );
794
795 debug( ' cache hit: trx_type_id='||to_char(p_trx_type_id),
796 MSG_LEVEL_DEBUG );
797
798 EXCEPTION
799 WHEN NO_DATA_FOUND THEN
800 -- else, get it from the database
801 --
802 debug( ' cache miss: trx_type_id='||to_char(p_trx_type_id),
803 MSG_LEVEL_DEBUG );
804
805 SELECT
806 nvl(gl_id_rec,-1),
807 nvl(gl_id_unpaid_rec,-1),
808 nvl(gl_id_factor,-1),
809 nvl(gl_id_remittance,-1)
810 INTO
811 p_ccid_rec,
812 p_ccid_unpaidrec,
813 p_ccid_factor,
814 p_ccid_remittance
815 FROM ra_cust_trx_types
816 WHERE cust_trx_type_id = p_trx_type_id;
817
818 -- update cache
819 trx_type_rec_t( p_trx_type_id ) := p_ccid_rec;
820 trx_type_unpaidrec_t( p_trx_type_id ) := p_ccid_unpaidrec;
821 trx_type_factor_t( p_trx_type_id ) := p_ccid_factor;
822 trx_type_remittance_t( p_trx_type_id ) := p_ccid_remittance;
823
824 debug( ' cached: trx_type_id='||to_char(p_trx_type_id),
825 MSG_LEVEL_DEBUG );
826 END;
827
828
829 print_fcn_label2( 'arp_auto_accounting_br.get_trx_type_ccids()-' );
830
831 EXCEPTION
832 WHEN NO_DATA_FOUND THEN
833 debug('arp_auto_accounting_br.get_trx_type_ccids(): no data found',
834 MSG_LEVEL_DEBUG);
835 WHEN OTHERS THEN
836 debug('EXCEPTION: arp_auto_accounting_br.get_trx_type_ccids('
837 || to_char(p_trx_type_id) ||')', MSG_LEVEL_BASIC);
838 debug(SQLERRM, MSG_LEVEL_BASIC);
839 RAISE;
840 END get_trx_type_ccids;
841
842
843 ----------------------------------------------------------------------------
844 --
845 -- PROCEDURE NAME: get_site_use_ccids
846 --
847 -- DECSRIPTION:
848 -- Retrieves default ccids from the table hz_cust_site_uses
849 -- for a given drawee to site use id.
850 --
851 -- ARGUMENTS:
852 -- IN:
853 -- site_use_id
854 --
855 -- IN/OUT:
856 -- ccid_rec
857 -- ccid_unpaidrec
858 -- ccid_factor
859 -- ccid_remittance
860 --
861 -- OUT:
862 --
863 -- NOTES:
864 --
865 -- HISTORY:
866 --
867 PROCEDURE get_site_use_ccids( p_site_use_id IN BINARY_INTEGER,
868 p_ccid_rec IN OUT NOCOPY BINARY_INTEGER,
869 p_ccid_unpaidrec IN OUT NOCOPY BINARY_INTEGER,
870 p_ccid_factor IN OUT NOCOPY BINARY_INTEGER,
871 p_ccid_remittance IN OUT NOCOPY BINARY_INTEGER) IS
872 BEGIN
873 print_fcn_label2( 'arp_auto_accounting_br.get_site_use_ccids()+' );
874
875 --
876 -- initialize
877 --
878 p_ccid_rec := -1;
879 p_ccid_unpaidrec := -1;
880 p_ccid_factor := -1;
881 p_ccid_remittance := -1;
882
883 BEGIN
884 -- see if available in cache
885 --
886 p_ccid_rec := site_use_rec_t( p_site_use_id );
887 p_ccid_unpaidrec := site_use_unpaidrec_t( p_site_use_id );
888 p_ccid_factor := site_use_factor_t( p_site_use_id );
889 p_ccid_remittance := site_use_remittance_t( p_site_use_id );
890
891 debug( ' cache hit: site_use_id='||to_char(p_site_use_id),
892 MSG_LEVEL_DEBUG );
893
894 EXCEPTION
895 WHEN NO_DATA_FOUND THEN
896 -- else, get it from the database
897 --
898 debug( ' cache miss: site_use_id='||to_char(p_site_use_id),
899 MSG_LEVEL_DEBUG );
900
901 SELECT
902 nvl(gl_id_rec,-1),
903 nvl(gl_id_unpaid_rec,-1),
904 nvl(gl_id_factor,-1),
905 nvl(gl_id_remittance,-1)
906 INTO
907 p_ccid_rec,
908 p_ccid_unpaidrec,
909 p_ccid_factor,
910 p_ccid_remittance
911 FROM hz_cust_site_uses
912 WHERE site_use_id = p_site_use_id;
913
914 -- update cache
915 site_use_rec_t( p_site_use_id ) := p_ccid_rec;
916 site_use_unpaidrec_t( p_site_use_id ) := p_ccid_unpaidrec;
917 site_use_factor_t( p_site_use_id ) := p_ccid_factor;
918 site_use_remittance_t( p_site_use_id ) := p_ccid_remittance;
919 END;
920
921 debug( ' cached: site_use_id='||to_char(p_site_use_id),
922 MSG_LEVEL_DEBUG );
923
924 print_fcn_label2( 'arp_auto_accounting_br.get_site_use_ccids()-' );
925
926 EXCEPTION
927 WHEN NO_DATA_FOUND THEN
928 debug('arp_auto_accounting_br.get_site_use_ccids(): no data found',
929 MSG_LEVEL_DEBUG);
930 WHEN OTHERS THEN
931 debug('EXCEPTION: arp_auto_accounting_br.get_site_use_ccids('
932 || to_char(p_site_use_id) ||')', MSG_LEVEL_BASIC);
933 debug(SQLERRM, MSG_LEVEL_BASIC);
934 RAISE;
935 END get_site_use_ccids;
936
937 ----------------------------------------------------------------------------
938 --
939 -- PROCEDURE NAME: get_pym_bact_ccids
940 --
941 -- DECSRIPTION:
942 -- Retrieves default ccids from the table ar_receipt_method_accounts
943 -- for a given payment methods bank account
944 --
945 -- ARGUMENTS:
946 -- IN:
947 -- receipt_method_id
948 -- bank_account_id
949 --
950 -- IN/OUT:
951 -- ccid_factor
952 -- ccid_remittance
953 --
954 -- OUT:
955 --
956 -- NOTES:
957 --
958 -- HISTORY:
959 --
960 --
961 PROCEDURE get_pym_bact_ccids( p_receipt_method_id IN BINARY_INTEGER,
962 p_bank_account_id IN BINARY_INTEGER,
963 p_ccid_factor IN OUT NOCOPY BINARY_INTEGER,
964 p_ccid_remittance IN OUT NOCOPY BINARY_INTEGER ) IS
965 BEGIN
966
967 print_fcn_label2( 'arp_auto_accounting_br.get_pym_bact_ccids()+' );
968
969 p_ccid_factor := -1;
970 p_ccid_remittance := -1;
971
972 BEGIN
973 -- see if available in cache
974 --
975 p_ccid_factor := pym_bact_factor_t( p_bank_account_id ) ;
976 p_ccid_remittance := pym_bact_remittance_t( p_bank_account_id );
977
978 debug( ' cache hit: bank_account_id ='||to_char(p_bank_account_id),
979 MSG_LEVEL_DEBUG );
980
981 EXCEPTION
982 WHEN NO_DATA_FOUND THEN
983 -- else, get it from the database
984 --
985 debug( ' cache hit: bank_account_id ='||to_char(p_bank_account_id),
986 MSG_LEVEL_DEBUG );
987
988 SELECT
989 nvl(br_factor_ccid,-1),
990 nvl(br_remittance_ccid,-1)
991 INTO p_ccid_factor, p_ccid_remittance
992 FROM ar_receipt_method_accounts
993 WHERE remit_bank_acct_use_id = p_bank_account_id
994 AND receipt_method_id = p_receipt_method_id ;
995
996 -- update cache
997 pym_bact_factor_t( p_bank_account_id ) := p_ccid_factor;
998 pym_bact_remittance_t( p_bank_account_id ) := p_ccid_remittance;
999
1000 debug( ' cached: bank_account_id ='||to_char(p_bank_account_id),
1001 MSG_LEVEL_DEBUG );
1002
1003 END;
1004
1005 print_fcn_label2( 'arp_auto_accounting_br.get_pym_bact_ccids()-' );
1006
1007 EXCEPTION
1008 WHEN NO_DATA_FOUND THEN
1009 debug('arp_auto_accounting_br.get_pym_bact_ccids(): no data found',
1010 MSG_LEVEL_DEBUG);
1011 WHEN OTHERS THEN
1012 debug('EXCEPTION: arp_auto_accounting_br.get_pym_bact_ccids('
1013 || to_char(p_receipt_method_id) || to_char(p_bank_account_id) ||')', MSG_LEVEL_BASIC);
1014 debug(SQLERRM, MSG_LEVEL_BASIC);
1015 RAISE;
1016 END get_pym_bact_ccids;
1017
1018 /* ------------------------------------------------------------------------ */
1019 /* Finds combination_id for given segment values. */
1020 /* If validation date is NULL checks all cross-validation rules. */
1021 /* Returns TRUE if combination valid, or FALSE and sets error message */
1022 /* on server using FND_MESSAGE if invalid. */
1023 /* ------------------------------------------------------------------------ */
1024 FUNCTION get_combination_id(application_short_name IN VARCHAR2,
1025 key_flex_code IN VARCHAR2,
1026 structure_number IN NUMBER,
1027 validation_date IN DATE,
1028 concat_segments IN VARCHAR2,
1029 combination_id OUT NOCOPY NUMBER)
1030 RETURN BOOLEAN IS
1031 r_value BOOLEAN := FALSE;
1032 BEGIN
1033
1034 -- Initialize messages, debugging, and number of sql strings
1035 --
1036 print_fcn_label( 'arp_auto_accounting_br.get_combination_id()+' );
1037
1038 r_value := FND_FLEX_KEYVAL.validate_segs('CREATE_COMBINATION',
1039 application_short_name, key_flex_code, structure_number,
1040 concat_segments, 'V',
1041 validation_date);
1042 if( r_value ) then
1043 combination_id := FND_FLEX_KEYVAL.combination_id;
1044 print_fcn_label( 'arp_auto_accounting_br.get_combination_id()-' );
1045 return(r_value);
1046 end if;
1047
1048 return(r_value);
1049
1050 EXCEPTION
1051 WHEN OTHERS THEN
1052 FND_MESSAGE.set_name( 'AR', 'GENERIC_MESSAGE' );
1053 FND_MESSAGE.set_token( 'GENERIC_TEXT', 'get_combination_id() exception: '||SQLERRM );
1054 return(FALSE);
1055
1056 END get_combination_id;
1057
1058 /* ------------------------------------------------------------------------ */
1059 /* Overloaded version of above for user with individual segments. */
1060 /* ------------------------------------------------------------------------ */
1061
1062 FUNCTION get_combination_id(application_short_name IN VARCHAR2,
1063 key_flex_code IN VARCHAR2,
1064 structure_number IN NUMBER,
1065 validation_date IN DATE,
1066 n_segments IN NUMBER,
1067 segments IN FND_FLEX_EXT.SegmentArray,
1068 combination_id OUT NOCOPY NUMBER)
1069 RETURN BOOLEAN IS
1070 sepchar VARCHAR2(1);
1071 catsegs VARCHAR2(2000);
1072
1073 BEGIN
1074 print_fcn_label( 'arp_auto_accounting_br.get_combination_id1()+' );
1075
1076 -- Concatenate the input segments, then send them to the other function.
1077 --
1078 sepchar := fnd_flex_ext.get_delimiter(application_short_name, key_flex_code,
1079 structure_number);
1080 if(sepchar is not null) then
1081 print_fcn_label( 'arp_auto_accounting_br.get_combination_id1()-' );
1082 return(get_combination_id(application_short_name, key_flex_code,
1083 structure_number, validation_date,
1084 FND_FLEX_EXT.concatenate_segments(n_segments, segments, sepchar),
1085 combination_id));
1086 end if;
1087 return(FALSE);
1088
1089 EXCEPTION
1090 WHEN OTHERS THEN
1091 FND_MESSAGE.set_name( 'AR', 'GENERIC_MESSAGE' );
1092 FND_MESSAGE.set_token( 'GENERIC_TEXT', 'get_combination_id() exception: '||SQLERRM );
1093 return(FALSE);
1094
1095 END get_combination_id;
1096
1097 ----------------------------------------------------------------------------
1098 PROCEDURE define_columns( p_select_c IN INTEGER,
1099 p_select_rec IN select_rec_type) IS
1100 BEGIN
1101 print_fcn_label2( 'arp_auto_accounting_br.define_columns()+' );
1102
1103 dbms_sql.define_column( p_select_c, 1, p_select_rec.customer_trx_id );
1104 dbms_sql.define_column( p_select_c, 2, p_select_rec.cust_trx_type_id);
1105 dbms_sql.define_column( p_select_c, 3, p_select_rec.site_use_id);
1106 dbms_sql.define_column( p_select_c, 4, p_select_rec.drawee_id);
1107 dbms_sql.define_column( p_select_c, 5, p_select_rec.bill_to_site_use_id);
1108 dbms_sql.define_column( p_select_c, 6, p_select_rec.br_unpaid_flag,1);
1109 dbms_sql.define_column( p_select_c, 7, p_select_rec.transaction_history_id);
1110 dbms_sql.define_column( p_select_c, 8, p_select_rec.batch_id);
1111 dbms_sql.define_column( p_select_c, 9, p_select_rec.gl_date,12);
1112 dbms_sql.define_column( p_select_c, 10, p_select_rec.source_type, 30);
1113 dbms_sql.define_column( p_select_c, 11, p_select_rec.amount);
1114 dbms_sql.define_column( p_select_c, 12, p_select_rec.acctd_amount);
1115 dbms_sql.define_column( p_select_c, 13, p_select_rec.currency_code,15);
1116 dbms_sql.define_column( p_select_c, 14, p_select_rec.currency_conversion_rate);
1117 dbms_sql.define_column( p_select_c, 15, p_select_rec.currency_conversion_type,30);
1118 dbms_sql.define_column( p_select_c, 16, p_select_rec.currency_conversion_date,12);
1119 dbms_sql.define_column( p_select_c, 17, p_select_rec.receipt_method_id);
1120 dbms_sql.define_column( p_select_c, 18, p_select_rec.bank_account_id);
1121 dbms_sql.define_column( p_select_c, 19, p_select_rec.concatenated_segments,240);
1122 dbms_sql.define_column( p_select_c, 20, p_select_rec.code_combination_id);
1123 dbms_sql.define_column( p_select_c, 21, p_select_rec.br_unpaid_ccid);
1124
1125 print_fcn_label2( 'arp_auto_accounting_br.define_columns()-' );
1126 EXCEPTION
1127 WHEN OTHERS THEN
1128 debug('EXCEPTION: arp_auto_accounting_br.define_columns()',
1129 MSG_LEVEL_BASIC);
1130 debug(SQLERRM, MSG_LEVEL_BASIC);
1131 RAISE;
1132 END define_columns;
1133
1134
1135 ----------------------------------------------------------------------------
1136 --
1137 -- FUNCTION NAME: build_select_sql
1138 --
1139 -- DECSRIPTION:
1140 --
1141 -- ARGUMENTS:
1142 -- IN:
1143 -- system_info
1144 -- profile_info
1145 -- account_class
1146 -- customer_trx_id
1147 -- receivable_application_id
1148 -- code_combination_id
1149 -- cust_trx_type_id
1150 --
1151 -- IN/OUT:
1152 --
1153 -- OUT:
1154 --
1155 -- RETURNS:
1156 -- select statement
1157 --
1158 -- NOTES:
1159 --
1160 -- HISTORY:
1161 --
1162 FUNCTION build_select_sql( p_system_info IN
1163 arp_trx_global.system_info_rec_type,
1164 p_profile_info IN
1165 arp_trx_global.profile_rec_type,
1166 p_account_class IN VARCHAR2,
1167 p_customer_trx_id IN BINARY_INTEGER,
1168 p_receivable_application_id IN BINARY_INTEGER,
1169 p_br_unpaid_ccid IN BINARY_INTEGER)
1170 RETURN VARCHAR2 IS
1171
1172 l_select_stmt VARCHAR2(32767);
1173 l_sel_trx_receipt_col VARCHAR2(400);
1174 l_receipt_batch_col VARCHAR2(400);
1175 l_ccid_col VARCHAR2(400);
1176 l_br_unpaid_ccid_col VARCHAR2(400);
1177 l_ps_app_col VARCHAR2(400);
1178 l_receipt_batch_table VARCHAR2(400);
1179 l_receipt_app_table VARCHAR2(400);
1180 l_trx_id_pred VARCHAR2(400);
1181 l_receipt_batch_pred VARCHAR2(400);
1182 l_receipt_app_pred VARCHAR2(400);
1183 l_alias VARCHAR2(4);
1184
1185 BEGIN
1186
1187 print_fcn_label( 'arp_auto_accounting_br.build_select_sql()+' );
1188
1189 ------------------------------------------------------------------------
1190 -- Initialize building blocks columns
1191 ------------------------------------------------------------------------
1192
1193 debug( ' Initialize fragments', MSG_LEVEL_DEVELOP );
1194
1195 l_receipt_batch_col := 'to_number(''''),' ||
1196 CRLF ||'to_number(''''),';
1197
1198 l_ccid_col := 'to_char(''''),' || 'to_number(''''),';
1199
1200 l_br_unpaid_ccid_col := 'to_number('''')';
1201
1202
1203 ------------------------------------------------------------------------
1204 -- Initialize building blocks tables
1205 ------------------------------------------------------------------------
1206 l_receipt_app_table := '';
1207
1208 l_receipt_batch_table := '';
1209
1210 ------------------------------------------------------------------------
1211 -- Initialize building blocks predicates
1212 ------------------------------------------------------------------------
1213 l_receipt_app_pred := '';
1214
1215 l_receipt_batch_pred := '';
1216
1217 debug( ' Construct BR Transaction document column details', MSG_LEVEL_DEVELOP );
1218
1219 ------------------------------------------------------------------------
1220 -- Get Remmitance account and Receipt Method details from Batch
1221 ------------------------------------------------------------------------
1222 IF p_account_class IN ('REC','UNPAIDREC') THEN --Receivable or Unpaid Receivable
1223
1224 l_alias := 'ps.';
1225
1226 ELSIF p_account_class IN ('FACTOR','REMITTANCE') THEN
1227
1228 l_alias := 'arb.';
1229
1230 l_receipt_batch_col := 'arb.receipt_method_id,' ||
1231 CRLF || 'arb.remit_bank_acct_use_id,';
1232
1233 IF (NVL(g_ae_sys_rec.sob_type,'P') = 'P') THEN
1234 l_receipt_batch_table := ',' || CRLF || 'ar_batches arb';
1235 --{BUG#4301323
1236 -- ELSE
1237 -- l_receipt_batch_table := ',' || CRLF || 'ar_batches_mrc_v arb';
1238 --}
1239 END IF;
1240 l_receipt_batch_pred := CRLF || 'AND th.batch_id = arb.batch_id (+)';
1241
1242 END IF;
1243
1244 ------------------------------------------------------------------------
1245 -- Exchange rate details from Transaction or Batch
1246 ------------------------------------------------------------------------
1247 debug(' Construct exchange rate details fragment', MSG_LEVEL_DEVELOP);
1248
1249 l_sel_trx_receipt_col := l_alias || 'exchange_rate,' ||
1250 CRLF || l_alias || 'exchange_rate_type,' ||
1251 CRLF || 'to_char(' || l_alias || 'exchange_date'
1252 || ',''J''),';
1253
1254 ------------------------------------------------------------------------
1255 -- Construct Unpaid ccid fragment
1256 ------------------------------------------------------------------------
1257 debug(' Construct unpaid ccid fragment', MSG_LEVEL_DEVELOP);
1258
1259 IF p_br_unpaid_ccid IS NOT NULL THEN
1260
1261 l_br_unpaid_ccid_col := ':br_unpaid_ccid';
1262
1263 END IF;
1264
1265 ------------------------------------------------------------------------
1266 -- customer_trx_id fragment
1267 ------------------------------------------------------------------------
1268 debug(' Build trx id predicate', MSG_LEVEL_DEVELOP);
1269
1270 IF( p_customer_trx_id IS NOT NULL ) THEN
1271
1272 l_trx_id_pred := 'ct.customer_trx_id = :customer_trx_id';
1273
1274 END IF;
1275
1276
1277 ----------------------------------------------------------------------------
1278 -- Receipt application get the receipt amounts due to reversal, as it must
1279 -- match the reversed application
1280 -----------------------------------------------------------------------------
1281 debug(' Construct receipt application fragment', MSG_LEVEL_DEVELOP);
1282
1283 IF( p_receivable_application_id IS NOT NULL) THEN
1284 l_ps_app_col := 'app.amount_applied,'||
1285 CRLF || 'app.acctd_amount_applied_to,';
1286
1287 IF (NVL(g_ae_sys_rec.sob_type,'P') = 'P') THEN
1288 l_receipt_app_table := ',' || CRLF || 'ar_receivable_applications app';
1289 --{BUG4301323
1290 -- ELSE
1291 -- l_receipt_app_table := ',' || CRLF || 'ar_receivable_apps_mrc_v app';
1292 --}
1293 END IF;
1294
1295 l_receipt_app_pred := CRLF || 'AND app.receivable_application_id = :application_id' ||
1296 CRLF || 'AND app.status = ''APP''' ||
1297 CRLF || 'AND nvl(app.confirmed_flag,''Y'') = ''Y''' ||
1298 CRLF || 'AND app.applied_customer_trx_id = ct.customer_trx_id';
1299 ELSE
1300 l_ps_app_col := 'ps.amount_due_remaining,' ||
1301 CRLF || 'ps.acctd_amount_due_remaining,';
1302 END IF;
1303
1304 ------------------------------------------------------------------------
1305 -- Put it all together
1306 ------------------------------------------------------------------------
1307 debug(' Put it all together ', MSG_LEVEL_DEVELOP);
1308
1309 IF (g_ae_sys_rec.sob_type = 'P') THEN
1310 l_select_stmt :=
1311 'SELECT ct.customer_trx_id,' ||
1312 CRLF || 'ct.cust_trx_type_id,' ||
1313 CRLF || 'ct.drawee_site_use_id,' ||
1314 CRLF || 'ct.drawee_id,' ||
1315 CRLF || 'ct.bill_to_site_use_id,' ||
1316 CRLF || 'ct.br_unpaid_flag,' ||
1317 CRLF || 'th.transaction_history_id,' ||
1318 CRLF || 'th.batch_id,' ||
1319 CRLF || 'to_char(th.gl_date,''J''),' ||
1320 CRLF || ':account_class,' ||
1321 CRLF || l_ps_app_col ||
1322 CRLF || 'ps.invoice_currency_code,' ||
1323 CRLF || l_sel_trx_receipt_col ||
1324 CRLF || l_receipt_batch_col ||
1325 CRLF || l_ccid_col ||
1326 CRLF || l_br_unpaid_ccid_col ||
1327 CRLF || 'FROM '|| 'ra_customer_trx ct,' ||
1328 CRLF || 'ar_transaction_history th,' ||
1329 CRLF || 'ar_payment_schedules ps' ||
1330 l_receipt_app_table ||
1331 l_receipt_batch_table ||
1332 CRLF || 'WHERE '|| l_trx_id_pred ||
1333 CRLF || 'AND th.customer_trx_id = ct.customer_trx_id' ||
1334 CRLF || 'AND th.postable_flag = ''Y''' ||
1335 CRLF || 'AND th.posting_control_id = -3' ||
1336 CRLF || 'AND nvl(th.current_record_flag,''N'') = ''Y''' ||
1337 CRLF || 'AND nvl(th.current_accounted_flag, ''N'') = ''Y''' ||
1338 CRLF || 'AND th.gl_posted_date IS NULL' ||
1339 CRLF || 'AND ps.customer_trx_id = ct.customer_trx_id' ||
1340 l_receipt_app_pred ||
1341 l_receipt_batch_pred ||
1342 CRLF || '/* prevent duplicate records from being created */' ||
1343 CRLF || 'AND not exists' ||
1344 CRLF || ' (SELECT ''distribution exists''' ||
1345 CRLF || ' FROM ar_distributions ard' ||
1346 CRLF || ' WHERE ard.source_id = th.transaction_history_id' ||
1347 CRLF || ' AND ard.source_table = ''TH''' ||
1348 CRLF || ' AND ard.source_type = :account_class)';
1349 --{BUG#4301323
1350 -- ELSE
1351 -- l_select_stmt :=
1352 -- 'SELECT ct.customer_trx_id,' ||
1353 -- CRLF || 'ct.cust_trx_type_id,' ||
1354 -- CRLF || 'ct.drawee_site_use_id,' ||
1355 -- CRLF || 'ct.drawee_id,' ||
1356 -- CRLF || 'ct.bill_to_site_use_id,' ||
1357 -- CRLF || 'ct.br_unpaid_flag,' ||
1358 -- CRLF || 'th.transaction_history_id,' ||
1359 -- CRLF || 'th.batch_id,' ||
1360 -- CRLF || 'to_char(th.gl_date,''J''),' ||
1361 -- CRLF || ':account_class,' ||
1362 -- CRLF || l_ps_app_col ||
1363 -- CRLF || 'ps.invoice_currency_code,' ||
1364 -- CRLF || l_sel_trx_receipt_col ||
1365 -- CRLF || l_receipt_batch_col ||
1366 -- CRLF || l_ccid_col ||
1367 -- CRLF || l_br_unpaid_ccid_col ||
1368 -- CRLF || 'FROM '|| 'ra_customer_trx_mrc_v ct,' ||
1369 -- CRLF || 'ar_trx_history_mrc_v th,' ||
1370 -- CRLF || 'ar_payment_schedules_mrc_v ps' ||
1371 -- l_receipt_app_table ||
1372 -- l_receipt_batch_table ||
1373 -- CRLF || 'WHERE '|| l_trx_id_pred ||
1374 -- CRLF || 'AND th.customer_trx_id = ct.customer_trx_id' ||
1375 -- CRLF || 'AND th.postable_flag = ''Y''' ||
1376 -- CRLF || 'AND th.posting_control_id = -3' ||
1377 -- CRLF || 'AND nvl(th.current_record_flag,''N'') = ''Y''' ||
1378 -- CRLF || 'AND nvl(th.current_accounted_flag, ''N'') = ''Y''' ||
1379 -- CRLF || 'AND th.gl_posted_date IS NULL' ||
1380 -- CRLF || 'AND ps.customer_trx_id = ct.customer_trx_id' ||
1381 -- l_receipt_app_pred ||
1382 -- l_receipt_batch_pred ||
1383 -- CRLF || '/* prevent duplicate records from being created */' ||
1384 -- CRLF || 'AND not exists' ||
1385 -- CRLF || ' (SELECT ''distribution exists''' ||
1386 -- CRLF || ' FROM ar_mc_distributions_all ard' ||
1387 -- CRLF || ' WHERE ard.source_id = th.transaction_history_id' ||
1388 -- CRLF || ' AND ard.set_of_books_id = ' || g_ae_sys_rec.set_of_books_id ||
1389 -- CRLF || ' AND ard.source_table = ''TH''' ||
1390 -- CRLF || ' AND ard.source_type = :account_class)';
1391 END IF;
1392
1393 debug( l_select_stmt, MSG_LEVEL_DEBUG );
1394 debug( ' len(l_select_stmt)=' ||
1395 to_char(length(l_select_stmt)), MSG_LEVEL_DEBUG );
1396
1397 print_fcn_label( 'arp_auto_accounting_br.build_select_sql()-' );
1398
1399 RETURN l_select_stmt;
1400
1401 EXCEPTION
1402 WHEN OTHERS THEN
1403 debug('EXCEPTION: arp_auto_accounting_br.build_select_sql()',
1404 MSG_LEVEL_BASIC);
1405 debug(SQLERRM);
1406 RAISE;
1407
1408 END build_select_sql;
1409
1410
1411 ----------------------------------------------------------------------------
1412 --
1413 -- FUNCTION NAME: add_segments_to_cache
1414 --
1415 -- DECSRIPTION:
1416 -- Addes the segment values for a given ccid to the segment value caches.
1417 --
1418 -- ARGUMENTS:
1419 -- IN:
1420 -- ccid
1421 -- segment_number (from the column name 'SEGMENTxx')
1422 --
1423 -- IN/OUT:
1424 --
1425 -- OUT:
1426 -- p_desired_segment
1427 --
1428 -- RETURNS:
1429 -- segment value. NULL if data not found.
1430 --
1431 -- NOTES:
1432 -- exception raised if no rows found
1433 -- I did not use record to contain these table values in order to be
1434 -- backward compatible with earlier versions of PL/SQL that did not allow
1435 -- tables of records.
1436 --
1437 -- HISTORY:
1438 --
1439 --
1440 PROCEDURE add_segments_to_cache( p_ccid IN binary_integer,
1441 p_segment_number IN binary_integer,
1442 p_desired_segment OUT NOCOPY varchar2) IS
1443
1444 l_segment1 varchar2(30);
1445 l_segment2 varchar2(30);
1446 l_segment3 varchar2(30);
1447 l_segment4 varchar2(30);
1448 l_segment5 varchar2(30);
1449 l_segment6 varchar2(30);
1450 l_segment7 varchar2(30);
1451 l_segment8 varchar2(30);
1452 l_segment9 varchar2(30);
1453 l_segment10 varchar2(30);
1454 l_segment11 varchar2(30);
1455 l_segment12 varchar2(30);
1456 l_segment13 varchar2(30);
1457 l_segment14 varchar2(30);
1458 l_segment15 varchar2(30);
1459 l_segment16 varchar2(30);
1460 l_segment17 varchar2(30);
1461 l_segment18 varchar2(30);
1462 l_segment19 varchar2(30);
1463 l_segment20 varchar2(30);
1464 l_segment21 varchar2(30);
1465 l_segment22 varchar2(30);
1466 l_segment23 varchar2(30);
1467 l_segment24 varchar2(30);
1468 l_segment25 varchar2(30);
1469 l_segment26 varchar2(30);
1470 l_segment27 varchar2(30);
1471 l_segment28 varchar2(30);
1472 l_segment29 varchar2(30);
1473 l_segment30 varchar2(30);
1474
1475 BEGIN
1476
1477 print_fcn_label2( 'arp_auto_accounting_br.add_segments_to_cache()+' );
1478
1479 SELECT segment1,
1480 segment2,
1481 segment3,
1482 segment4,
1483 segment5,
1484 segment6,
1485 segment7,
1486 segment8,
1487 segment9,
1488 segment10,
1489 segment11,
1490 segment12,
1491 segment13,
1492 segment14,
1493 segment15,
1494 segment16,
1495 segment17,
1496 segment18,
1497 segment19,
1498 segment20,
1499 segment21,
1500 segment22,
1501 segment23,
1502 segment24,
1503 segment25,
1504 segment26,
1505 segment27,
1506 segment28,
1507 segment29,
1508 segment30,
1509 DECODE(p_segment_number,
1510 1, segment1,
1511 2, segment2,
1512 3, segment3,
1513 4, segment4,
1514 5, segment5,
1515 6, segment6,
1516 7, segment7,
1517 8, segment8,
1518 9, segment9,
1519 10, segment10,
1520 11, segment11,
1521 12, segment12,
1522 13, segment13,
1523 14, segment14,
1524 15, segment15,
1525 16, segment16,
1526 17, segment17,
1527 18, segment18,
1528 19, segment19,
1529 20, segment20,
1530 21, segment21,
1531 22, segment22,
1532 23, segment23,
1533 24, segment24,
1534 25, segment25,
1535 26, segment26,
1536 27, segment27,
1537 28, segment28,
1538 29, segment29,
1539 30, segment30, null)
1540 INTO l_segment1,
1541 l_segment2,
1542 l_segment3,
1543 l_segment4,
1544 l_segment5,
1545 l_segment6,
1546 l_segment7,
1547 l_segment8,
1548 l_segment9,
1549 l_segment10,
1550 l_segment11,
1551 l_segment12,
1552 l_segment13,
1553 l_segment14,
1554 l_segment15,
1555 l_segment16,
1556 l_segment17,
1557 l_segment18,
1558 l_segment19,
1559 l_segment20,
1560 l_segment21,
1561 l_segment22,
1562 l_segment23,
1563 l_segment24,
1564 l_segment25,
1565 l_segment26,
1566 l_segment27,
1567 l_segment28,
1568 l_segment29,
1569 l_segment30,
1570 p_desired_segment
1571 FROM gl_code_combinations
1572 WHERE code_combination_id = p_ccid;
1573
1574
1575 /*--------------------------------------------------+
1576 | Add the selected segments to the segment cache |
1577 | only if the cache is not already full. |
1578 +--------------------------------------------------*/
1579
1580 IF ( segment1_cache.count <= MAX_SEGMENT_CACHE_SIZE )
1581 THEN
1582 segment1_cache(p_ccid) := l_segment1;
1583 segment2_cache(p_ccid) := l_segment2;
1584 segment3_cache(p_ccid) := l_segment3;
1585 segment4_cache(p_ccid) := l_segment4;
1586 segment5_cache(p_ccid) := l_segment5;
1587 segment6_cache(p_ccid) := l_segment6;
1588 segment7_cache(p_ccid) := l_segment7;
1589 segment8_cache(p_ccid) := l_segment8;
1590 segment9_cache(p_ccid) := l_segment9;
1591 segment10_cache(p_ccid) := l_segment10;
1592 segment11_cache(p_ccid) := l_segment11;
1593 segment12_cache(p_ccid) := l_segment12;
1594 segment13_cache(p_ccid) := l_segment13;
1595 segment14_cache(p_ccid) := l_segment14;
1596 segment15_cache(p_ccid) := l_segment15;
1597 segment16_cache(p_ccid) := l_segment16;
1598 segment17_cache(p_ccid) := l_segment17;
1599 segment18_cache(p_ccid) := l_segment18;
1600 segment19_cache(p_ccid) := l_segment19;
1601 segment20_cache(p_ccid) := l_segment20;
1602 segment21_cache(p_ccid) := l_segment21;
1603 segment22_cache(p_ccid) := l_segment22;
1604 segment23_cache(p_ccid) := l_segment23;
1605 segment24_cache(p_ccid) := l_segment24;
1606 segment25_cache(p_ccid) := l_segment25;
1607 segment26_cache(p_ccid) := l_segment26;
1608 segment27_cache(p_ccid) := l_segment27;
1609 segment28_cache(p_ccid) := l_segment28;
1610 segment29_cache(p_ccid) := l_segment29;
1611 segment30_cache(p_ccid) := l_segment30;
1612 END IF;
1613
1614 print_fcn_label2( 'arp_auto_accounting_br.add_segments_to_cache()-' );
1615
1616 EXCEPTION
1617 WHEN OTHERS THEN
1618 debug( 'EXCEPTION: arp_auto_accounting_br.add_segments_to_cache()',
1619 MSG_LEVEL_BASIC );
1620 debug(SQLERRM, MSG_LEVEL_BASIC);
1621 RAISE;
1622
1623 END;
1624
1625
1626 ----------------------------------------------------------------------------
1627 --
1628 -- FUNCTION NAME: get_segment_from_glcc
1629 --
1630 -- DECSRIPTION:
1631 -- Retrieves a GL code combination segment for a ccid
1632 --
1633 -- ARGUMENTS:
1634 -- IN:
1635 -- ccid
1636 -- segment_number (from the column name 'SEGMENTxx')
1637 --
1638 -- IN/OUT:
1639 --
1640 -- OUT:
1641 --
1642 -- RETURNS:
1643 -- segment value. NULL if data not found.
1644 --
1645 -- NOTES:
1646 -- exception raised if no rows found
1647 --
1648 -- HISTORY:
1649 --
1650 --
1651 FUNCTION get_segment_from_glcc( p_ccid IN BINARY_INTEGER,
1652 p_segment_number IN BINARY_INTEGER )
1653 RETURN VARCHAR2 IS
1654
1655 l_segment_value VARCHAR2(25);
1656 i BINARY_INTEGER := 0;
1657 l_bool boolean;
1658 l_ccid BINARY_INTEGER;
1659
1660 l_desired_segment varchar2(30);
1661
1662 BEGIN
1663
1664 print_fcn_label2( 'arp_auto_accounting_br.get_segment_from_glcc()+' );
1665
1666 BEGIN
1667
1668 if (p_segment_number = 1)
1669 then return(segment1_cache(p_ccid));
1670 elsif (p_segment_number = 2)
1671 then return(segment2_cache(p_ccid));
1672 elsif (p_segment_number = 3)
1673 then return(segment3_cache(p_ccid));
1674 elsif (p_segment_number = 4)
1675 then return(segment4_cache(p_ccid));
1676 elsif (p_segment_number = 5)
1677 then return(segment5_cache(p_ccid));
1678 elsif (p_segment_number = 6)
1679 then return(segment6_cache(p_ccid));
1680 elsif (p_segment_number = 7)
1681 then return(segment7_cache(p_ccid));
1682 elsif (p_segment_number = 8)
1683 then return(segment8_cache(p_ccid));
1684 elsif (p_segment_number = 9)
1685 then return(segment9_cache(p_ccid));
1686 elsif (p_segment_number = 10)
1687 then return(segment10_cache(p_ccid));
1688 elsif (p_segment_number = 11)
1689 then return(segment11_cache(p_ccid));
1690 elsif (p_segment_number = 12)
1691 then return(segment12_cache(p_ccid));
1692 elsif (p_segment_number = 13)
1693 then return(segment13_cache(p_ccid));
1694 elsif (p_segment_number = 14)
1695 then return(segment14_cache(p_ccid));
1696 elsif (p_segment_number = 15)
1697 then return(segment15_cache(p_ccid));
1698 elsif (p_segment_number = 16)
1699 then return(segment16_cache(p_ccid));
1700 elsif (p_segment_number = 17)
1701 then return(segment17_cache(p_ccid));
1702 elsif (p_segment_number = 18)
1703 then return(segment18_cache(p_ccid));
1704 elsif (p_segment_number = 19)
1705 then return(segment19_cache(p_ccid));
1706 elsif (p_segment_number = 20)
1707 then return(segment20_cache(p_ccid));
1708 elsif (p_segment_number = 21)
1709 then return(segment21_cache(p_ccid));
1710 elsif (p_segment_number = 22)
1711 then return(segment22_cache(p_ccid));
1712 elsif (p_segment_number = 23)
1713 then return(segment23_cache(p_ccid));
1714 elsif (p_segment_number = 24)
1715 then return(segment24_cache(p_ccid));
1716 elsif (p_segment_number = 25)
1717 then return(segment25_cache(p_ccid));
1718 elsif (p_segment_number = 26)
1719 then return(segment26_cache(p_ccid));
1720 elsif (p_segment_number = 27)
1721 then return(segment27_cache(p_ccid));
1722 elsif (p_segment_number = 28)
1723 then return(segment28_cache(p_ccid));
1724 elsif (p_segment_number = 29)
1725 then return(segment29_cache(p_ccid));
1726 elsif (p_segment_number = 30)
1727 then return(segment30_cache(p_ccid));
1728 end if;
1729
1730
1731 EXCEPTION
1732 WHEN NO_DATA_FOUND
1733 THEN
1734
1735 /*--------------------------------------------------------------+
1736 | The ccid was not in the cache. |
1737 | Select the segments from gl_code_combinations and add them |
1738 | to the cache if it is not already full. |
1739 +--------------------------------------------------------------*/
1740
1741 add_segments_to_cache(p_ccid, p_segment_number,l_desired_segment);
1742
1743 debug('getting segment ' || p_segment_number ||
1744 'for ccid ' || p_ccid ||
1745 ' from gl_code_combinations', MSG_LEVEL_DEBUG);
1746
1747 print_fcn_label2( 'arp_auto_accounting_br.get_segment_from_glcc()-' );
1748 RETURN(l_desired_segment);
1749
1750 WHEN OTHERS THEN RAISE;
1751
1752 END;
1753
1754
1755 EXCEPTION
1756 WHEN NO_DATA_FOUND THEN
1757 debug('arp_auto_accounting_br.get_segment_from_glcc(): no data found',
1758 MSG_LEVEL_DEBUG);
1759 RETURN NULL;
1760 WHEN OTHERS THEN
1761 /* debug('EXCEPTION: arp_auto_accounting_br.get_segment_from_glcc('
1762 || to_char(p_ccid) || ', '
1763 || to_char(p_segment_number) || ')', MSG_LEVEL_BASIC); */
1764 debug(SQLERRM, MSG_LEVEL_BASIC);
1765 RAISE;
1766 END get_segment_from_glcc;
1767
1768 ----------------------------------------------------------------------------
1769 --
1770 -- FUNCTION NAME: Get_Ccid_From_Cache
1771 --
1772 -- DECSRIPTION:
1773 -- Retrieves valid GL code combination from the cache or from the AOL
1774 -- API routine if the value is not yet in the cache.
1775 --
1776 -- ARGUMENTS:
1777 -- IN:
1778 -- p_system_info
1779 -- p_concat_segments
1780 -- p_segment_table
1781 -- p_segment_cnt
1782 -- p_account_class
1783 --
1784 -- IN/OUT:
1785 --
1786 -- OUT:
1787 -- p_result
1788 --
1789 -- RETURNS:
1790 -- ccid
1791 --
1792 -- NOTES:
1793 --
1794 -- HISTORY:
1795 --
1796 --
1797
1798 FUNCTION Get_Ccid_From_Cache( p_system_info IN
1799 arp_trx_global.system_info_rec_type,
1800 p_concat_segments IN varchar2,
1801 p_segment_table IN fnd_flex_ext.SegmentArray,
1802 p_segment_cnt IN BINARY_INTEGER,
1803 p_account_class IN
1804 ra_cust_trx_line_gl_dist.account_class%type,
1805 p_result OUT NOCOPY BOOLEAN
1806 ) RETURN BINARY_INTEGER IS
1807
1808 i BINARY_INTEGER := 0;
1809 l_bool boolean;
1810 l_ccid BINARY_INTEGER;
1811
1812 BEGIN
1813
1814 print_fcn_label2( 'arp_auto_accounting_br.get_ccid_from_cache()+' );
1815
1816 /*----------------------------------------------------------------+
1817 | Search the cache for the account_class for the concatenated |
1818 | segments. Return the ccid if it is in the cache. |
1819 | |
1820 | After the last record in the cache PL/SQL table is accessed, |
1821 | a NO_DATA_FOUND exception will be generated if the segments |
1822 | are not found the the cache. The NO_DATA_FOUND exception |
1823 | handler then calls the flexfield api to get the ccid and puts |
1824 | it in the cache if the cache is not already full. |
1825 +----------------------------------------------------------------*/
1826
1827 WHILE (1 = 1) LOOP
1828 i:= i + 1;
1829
1830 IF ( p_account_class = 'REC' )
1831 THEN
1832 IF (autoacc_rec_seg_cache(i) = p_concat_segments)
1833 AND (validation_date BETWEEN autoacc_rec_st_date_cache(i) AND
1834 autoacc_rec_end_date_cache(i))
1835 THEN
1836 l_ccid := autoacc_rec_id_cache(i);
1837 END IF;
1838
1839 ELSIF ( p_account_class = 'UNPAIDREC' )
1840 THEN
1841 IF (autoacc_unp_seg_cache(i) = p_concat_segments)
1842 AND (validation_date BETWEEN autoacc_unp_st_date_cache(i) AND
1843 autoacc_unp_end_date_cache(i))
1844 THEN
1845 l_ccid := autoacc_unp_id_cache(i);
1846 END IF;
1847
1848 ELSIF ( p_account_class = 'FACTOR' )
1849 THEN
1850 IF (autoacc_factor_seg_cache(i) = p_concat_segments)
1851 AND (validation_date BETWEEN autoacc_factor_st_date_cache(i) AND
1852 autoacc_factor_end_date_cache(i))
1853 THEN
1854 l_ccid := autoacc_factor_id_cache(i);
1855 END IF;
1856
1857 ELSIF ( p_account_class = 'REMITTANCE' )
1858 THEN
1859 IF (autoacc_rem_seg_cache(i) = p_concat_segments)
1860 AND (validation_date BETWEEN autoacc_rem_st_date_cache(i) AND
1861 autoacc_rem_end_date_cache(i))
1862 THEN
1863 l_ccid := autoacc_rem_id_cache(i);
1864 END IF;
1865
1866 END IF;
1867
1868 /*-------------------------------------------------+
1869 | Return the ccid if it was found in the cache. |
1870 +-------------------------------------------------*/
1871
1872 IF (l_ccid IS NOT NULL )
1873 THEN
1874
1875 debug('found ccid ' || l_ccid || ' for concatenated segs: ' ||
1876 p_concat_segments || ' in the cache', MSG_LEVEL_DEBUG);
1877
1878 p_result := TRUE;
1879
1880 print_fcn_label2( 'arp_auto_accounting_br.get_ccid_from_cache()-' );
1881 RETURN( l_ccid );
1882 END IF;
1883
1884 END LOOP;
1885
1886
1887 EXCEPTION
1888 WHEN NO_DATA_FOUND
1889 THEN
1890
1891 debug('Getting concatenated segs: ' ||
1892 p_concat_segments || ' using the flexfield api',
1893 MSG_LEVEL_DEBUG);
1894
1895 IF (get_combination_id(
1896 'SQLGL',
1897 'GL#',
1898 p_system_info.chart_of_accounts_id,
1899 validation_date, -- CCID validation date
1900 p_segment_cnt,
1901 p_segment_table,
1902 l_ccid ) )
1903 THEN
1904
1905 /*---------------------------------------------------+
1906 | Add the ccid to the cache for its account class |
1907 | if the cache is not already full. |
1908 +---------------------------------------------------*/
1909
1910 IF ( i <= MAX_CCID_CACHE_SIZE )
1911 THEN
1912 IF ( p_account_class = 'REC' )
1913 THEN
1914 autoacc_rec_id_cache(i) := l_ccid;
1915 autoacc_rec_seg_cache(i) := p_concat_segments;
1916 autoacc_rec_st_date_cache(i) := NVL(FND_FLEX_KEYVAL.start_date, G_MIN_DATE);
1917 autoacc_rec_end_date_cache(i) := NVL(FND_FLEX_KEYVAL.end_date, G_MAX_DATE);
1918 debug('REC CCID : ' || to_char(autoacc_rec_id_cache(i)) );
1919 debug('REC st Date : ' || autoacc_rec_st_date_cache(i) );
1920 debug('REC End Date : ' || autoacc_rec_end_date_cache(i) );
1921
1922 ELSIF ( p_account_class = 'UNPAIDREC' )
1923 THEN
1924 autoacc_unp_id_cache(i) := l_ccid;
1925 autoacc_unp_seg_cache(i) := p_concat_segments;
1926 autoacc_unp_st_date_cache(i) := NVL(FND_FLEX_KEYVAL.start_date, G_MIN_DATE);
1927 autoacc_unp_end_date_cache(i) := NVL(FND_FLEX_KEYVAL.end_date, G_MAX_DATE);
1928 debug('UNP CCID : ' || to_char(autoacc_unp_id_cache(i)) );
1929 debug('unp st Date : ' || autoacc_unp_st_date_cache(i) );
1930 debug('unp End Date : ' || autoacc_unp_end_date_cache(i) );
1931
1932 ELSIF ( p_account_class = 'FACTOR' )
1933 THEN
1934 autoacc_factor_id_cache(i) := l_ccid;
1935 autoacc_factor_seg_cache(i) := p_concat_segments;
1936 autoacc_factor_st_date_cache(i) := NVL(FND_FLEX_KEYVAL.start_date, G_MIN_DATE);
1937 autoacc_factor_end_date_cache(i) := NVL(FND_FLEX_KEYVAL.end_date, G_MAX_DATE);
1938 debug('fac CCID : ' || to_char(autoacc_factor_id_cache(i)) );
1939 debug('fac st Date : ' || autoacc_factor_st_date_cache(i) );
1940 debug('fac End Date : ' || autoacc_factor_end_date_cache(i) );
1941
1942 ELSIF ( p_account_class = 'REMITTANCE' )
1943 THEN
1944 autoacc_rem_id_cache(i) := l_ccid;
1945 autoacc_rem_seg_cache(i) := p_concat_segments;
1946 autoacc_rem_st_date_cache(i) := NVL(FND_FLEX_KEYVAL.start_date, G_MIN_DATE);
1947 autoacc_rem_end_date_cache(i) := NVL(FND_FLEX_KEYVAL.end_date, G_MAX_DATE);
1948 debug('rem CCID : ' || to_char(autoacc_rem_id_cache(i)) );
1949 debug('rem st Date : ' || autoacc_rem_st_date_cache(i) );
1950 debug('rem End Date : ' || autoacc_rem_end_date_cache(i) );
1951
1952 END IF;
1953
1954 END IF;
1955
1956 p_result := TRUE;
1957 print_fcn_label2( 'arp_auto_accounting_br.get_ccid_from_cache()-' );
1958
1959 RETURN(l_ccid);
1960 ELSE
1961 p_result := FALSE;
1962 print_fcn_label2( 'arp_auto_accounting_br.get_ccid_from_cache()-' );
1963
1964 RETURN(NULL);
1965 END IF;
1966
1967 WHEN OTHERS THEN
1968 debug( 'EXCEPTION: arp_auto_accounting_br.get_ccid_from_cache_cache()',
1969 MSG_LEVEL_BASIC );
1970 debug(SQLERRM, MSG_LEVEL_BASIC);
1971 RAISE;
1972 END;
1973
1974 ----------------------------------------------------------------------------
1975 --
1976 -- FUNCTION NAME: search_glcc_for_ccid
1977 --
1978 -- DECSRIPTION:
1979 -- Retrieves valid GL code combination based on passed segment values.
1980 --
1981 -- ARGUMENTS:
1982 -- IN:
1983 -- p_system_info
1984 -- p_segment_table
1985 -- p_segment_cnt -- # enabled segments
1986 -- p_account_class
1987 -- p_concat_segments
1988 --
1989 -- IN/OUT:
1990 --
1991 -- OUT:
1992 --
1993 -- RETURNS:
1994 -- ccid
1995 --
1996 -- NOTES:
1997 --
1998 -- HISTORY:
1999 --
2000 --
2001 FUNCTION search_glcc_for_ccid( p_system_info IN
2002 arp_trx_global.system_info_rec_type,
2003 p_segment_table IN fnd_flex_ext.SegmentArray,
2004 p_segment_cnt IN BINARY_INTEGER,
2005 p_account_class IN
2006 ra_cust_trx_line_gl_dist.account_class%type,
2007 p_concat_segments IN VARCHAR2 )
2008
2009 RETURN BINARY_INTEGER IS
2010
2011 l_ignore INTEGER;
2012 l_ccid BINARY_INTEGER;
2013 l_detail_posting_flag VARCHAR2(1);
2014 l_summary_flag VARCHAR2(1);
2015 l_bool BOOLEAN;
2016
2017 BEGIN
2018
2019 print_fcn_label2( 'arp_auto_accounting_br.search_glcc_for_ccid()+' );
2020
2021 --
2022 -- part 1: call the ccid cache or the AOL api to validate and dynamically
2023 -- insert ccid
2024 --
2025
2026
2027 /*------------------------------------------------------------------+
2028 | If the p_concat_segments or p_account_class parameters are |
2029 | null, do not use the cache. |
2030 | This logic exists to maintain backward compatibility with the |
2031 | original function spec. |
2032 | |
2033 | Otherwise, get the ccid from the caches of already validiated |
2034 | code combinations. |
2035 +------------------------------------------------------------------*/
2036
2037 IF (
2038 p_concat_segments IS NOT NULL
2039 AND p_account_class IS NOT NULL)
2040 THEN
2041 l_ccid := Get_Ccid_From_Cache( p_system_info,
2042 p_concat_segments,
2043 p_segment_table,
2044 p_segment_cnt,
2045 p_account_class,
2046 l_bool);
2047 ELSE
2048 l_bool := get_combination_id(
2049 'SQLGL',
2050 'GL#',
2051 p_system_info.chart_of_accounts_id,
2052 validation_date, -- CCID validation date
2053 p_segment_cnt,
2054 p_segment_table,
2055 l_ccid );
2056
2057 END IF;
2058
2059 IF( l_bool = FALSE ) THEN
2060 ------------------------------------------------------------------
2061 -- Failed to retrieve a valid ccid or
2062 -- unable to dynamically create a ccid
2063 ------------------------------------------------------------------
2064 g_error_buffer := fnd_message.get;
2065 debug( g_error_buffer, MSG_LEVEL_BASIC );
2066
2067 print_fcn_label2( 'arp_auto_accounting_br.search_glcc_for_ccid()-' );
2068 RETURN -1;
2069
2070 END IF;
2071
2072 --
2073 -- part 2: check special validation
2074 -- detail_posting_flag
2075 -- summary_flag
2076 --
2077 BEGIN
2078
2079 -- loop: bind variables
2080 -- fetch
2081 dbms_sql.bind_variable( ccid_reader_c, 'ccid',
2082 l_ccid );
2083
2084 dbms_sql.define_column( ccid_reader_c, 1, l_detail_posting_flag, 1 );
2085 dbms_sql.define_column( ccid_reader_c, 2, l_summary_flag, 1 );
2086
2087 EXCEPTION
2088 WHEN OTHERS THEN
2089 debug( 'Error in binding ccid_reader', MSG_LEVEL_BASIC );
2090 debug(SQLERRM, MSG_LEVEL_BASIC);
2091 RAISE;
2092 END;
2093
2094 BEGIN
2095
2096 l_ignore := dbms_sql.execute( ccid_reader_c );
2097
2098 IF dbms_sql.fetch_rows( ccid_reader_c ) > 0 THEN
2099
2100 dbms_sql.column_value( ccid_reader_c, 1, l_detail_posting_flag );
2101 dbms_sql.column_value( ccid_reader_c, 2, l_summary_flag );
2102
2103 IF( l_detail_posting_flag = NO ) THEN
2104
2105 g_error_buffer := MSG_FLEX_POSTING_NOT_ALLOWED;
2106 debug( MSG_FLEX_POSTING_NOT_ALLOWED, MSG_LEVEL_BASIC);
2107 print_fcn_label2( 'arp_auto_accounting_br.search_glcc_for_ccid()-' );
2108 RETURN -1;
2109
2110 ELSIF( l_summary_flag = YES ) THEN
2111
2112 g_error_buffer := MSG_FLEX_NO_PARENT_ALLOWED;
2113 debug( MSG_FLEX_NO_PARENT_ALLOWED, MSG_LEVEL_BASIC);
2114 print_fcn_label2( 'arp_auto_accounting_br.search_glcc_for_ccid()-' );
2115 RETURN -1;
2116
2117 END IF;
2118
2119 print_fcn_label2( 'arp_auto_accounting_br.search_glcc_for_ccid()-' );
2120 RETURN l_ccid;
2121
2122 ELSE
2123 --
2124 -- should not happen
2125 --
2126 RETURN -1;
2127
2128 END IF;
2129
2130 EXCEPTION
2131 WHEN OTHERS THEN
2132 debug( 'Error in executing/fetching ccid_reader',
2133 MSG_LEVEL_BASIC );
2134 debug(SQLERRM, MSG_LEVEL_BASIC);
2135 RAISE;
2136 END;
2137
2138 print_fcn_label2( 'arp_auto_accounting_br.search_glcc_for_ccid()-' );
2139
2140 EXCEPTION
2141 WHEN OTHERS THEN
2142 debug('EXCEPTION: arp_auto_accounting_br.search_glcc_for_ccid('
2143 || to_char(p_segment_cnt) ||')', MSG_LEVEL_BASIC);
2144 debug(SQLERRM, MSG_LEVEL_BASIC);
2145 RAISE;
2146 END search_glcc_for_ccid;
2147
2148 /*---------------------------------------------------------------------+
2149 | This overloaded version of the function exists to preserve |
2150 | backward compatibility with the original function specification. |
2151 +---------------------------------------------------------------------*/
2152
2153
2154 FUNCTION search_glcc_for_ccid( p_system_info IN
2155 arp_trx_global.system_info_rec_type,
2156 p_segment_table IN fnd_flex_ext.SegmentArray,
2157 p_segment_cnt IN BINARY_INTEGER )
2158 RETURN BINARY_INTEGER IS
2159
2160 BEGIN
2161 RETURN(
2162 search_glcc_for_ccid(
2163 p_system_info,
2164 p_segment_table,
2165 p_segment_cnt,
2166 NULL,
2167 NULL
2168 )
2169 );
2170
2171 END search_glcc_for_ccid;
2172
2173
2174
2175 ----------------------------------------------------------------------------
2176 --
2177 -- FUNCTION NAME: Find_Cursor_In_Cache
2178 --
2179 -- DECSRIPTION:
2180 -- Searches the cursor cache for an open cursor that matches
2181 -- the conditions in the key.
2182 --
2183 -- ARGUMENTS:
2184 -- IN:
2185 -- p_key
2186 --
2187 -- IN/OUT:
2188 --
2189 -- OUT:
2190 --
2191 -- RETURNS:
2192 -- Cursor number
2193 --
2194 -- NOTES:
2195 --
2196 -- HISTORY:
2197 --
2198
2199 FUNCTION Find_Cursor_In_Cache ( p_key IN VARCHAR2 ) RETURN BINARY_INTEGER IS
2200 BEGIN
2201
2202 print_fcn_label2( 'arp_auto_accounting_br.Find_Cursor_In_Cache()+' );
2203
2204 FOR l_index IN 1..cursor_attr_cache.count LOOP
2205
2206 IF ( cursor_attr_cache(l_index) = p_key )
2207 THEN
2208 print_fcn_label2(
2209 'arp_auto_accounting_br.Find_Cursor_In_Cache()-' );
2210 RETURN( l_index );
2211 END IF;
2212
2213 END LOOP;
2214
2215
2216 print_fcn_label2( 'arp_auto_accounting_br.Find_Cursor_In_Cache()-' );
2217
2218 RETURN(NULL);
2219
2220 EXCEPTION
2221 WHEN OTHERS THEN
2222 debug('EXCEPTION: arp_auto_accounting_br.Find_Cursor_In_Cache()',
2223 MSG_LEVEL_BASIC);
2224 debug(SQLERRM, MSG_LEVEL_BASIC);
2225 RAISE;
2226 END;
2227
2228 ----------------------------------------------------------------------------
2229 --
2230 -- FUNCTION NAME: Get_Select_Cursor
2231 --
2232 -- DECSRIPTION:
2233 -- Returns a cursor for the select statement.
2234 -- Multiple cursors are maintained for different combinations
2235 -- of input parameters. These criteria are encoded in the
2236 -- key which points to the appropriate record in the cursor
2237 -- cache. If the cursor is not found in the cache,
2238 -- a select statement is built and parsed, and the new
2239 -- cursor is added to the cache if the cache is not full.
2240 --
2241 -- ARGUMENTS:
2242 -- IN:
2243 -- p_system_info
2244 -- p_profile_info
2245 -- p_account_class
2246 -- p_customer_trx_id
2247 --
2248 -- IN/OUT:
2249 --
2250 -- OUT:
2251 -- p_keep_cursor_open_flag - if the cursor is in the cache or
2252 -- was added to the cache, don't
2253 -- close it after its first use.
2254 --
2255 -- RETURNS:
2256 -- Cursor number
2257 --
2258 -- NOTES:
2259 --
2260 -- HISTORY:
2261 --
2262
2263 FUNCTION Get_Select_Cursor(
2264 p_system_info IN
2265 arp_trx_global.system_info_rec_type,
2266 p_profile_info IN
2267 arp_trx_global.profile_rec_type,
2268 p_account_class IN VARCHAR2,
2269 p_customer_trx_id IN BINARY_INTEGER,
2270 p_receivable_application_id IN BINARY_INTEGER,
2271 p_br_unpaid_ccid IN BINARY_INTEGER,
2272 p_keep_cursor_open_flag OUT NOCOPY BOOLEAN )
2273 RETURN BINARY_INTEGER IS
2274
2275 l_select_rec select_rec_type;
2276 l_key VARCHAR2(100);
2277 l_select_c BINARY_INTEGER;
2278 l_cursor_index BINARY_INTEGER;
2279 l_cursor BINARY_INTEGER;
2280 l_ignore INTEGER;
2281
2282 BEGIN
2283
2284 print_fcn_label2( 'arp_auto_accounting_br.Get_Select_Cursor()+' );
2285
2286 p_keep_cursor_open_flag := TRUE;
2287
2288 /*----------------------------------+
2289 | Construct the cursor cache key |
2290 +----------------------------------*/
2291
2292 l_key := p_account_class || '-';
2293
2294 IF (p_customer_trx_id IS NOT NULL)
2295 THEN l_key := l_key || 'Y-';
2296 ELSE l_key := l_key || 'N-';
2297 END IF;
2298
2299 IF (p_receivable_application_id IS NOT NULL)
2300 THEN l_key := l_key || 'Y-';
2301 ELSE l_key := l_key || 'N-';
2302 END IF;
2303
2304 IF (g_ae_sys_rec.sob_type = 'R' ) THEN
2305 l_key := l_key || 'R-' || to_char(g_ae_sys_rec.set_of_books_id);
2306 ELSE
2307 l_key := l_key || 'P-';
2308 END IF;
2309
2310 /*----------------------------------------------------+
2311 | Attempt to get the cursor from the cursor cache. |
2312 +----------------------------------------------------*/
2313
2314 l_cursor_index := Find_Cursor_In_Cache(l_key);
2315
2316
2317 /*---------------------------------------------------+
2318 | If the cursor was found, return it immediately. |
2319 +---------------------------------------------------*/
2320
2321 IF ( l_cursor_index IS NOT NULL )
2322 THEN
2323
2324 print_fcn_label2( 'arp_auto_accounting_br.Get_Select_Cursor()-' );
2325
2326 l_cursor := cursor_cache( l_cursor_index );
2327
2328 debug('Found cursor in cache: key ' || l_key ||
2329 ' cursor index: ' || l_cursor_index ||
2330 ' cursor number: ' ||
2331 l_cursor, MSG_LEVEL_DEBUG);
2332
2333 RETURN( l_cursor );
2334 END IF;
2335
2336
2337 /*----------------------------------------------+
2338 | If the cursor was not found in the cache, |
2339 | construct and parse the select statement. |
2340 +----------------------------------------------*/
2341
2342 debug('Reparsing cursor that was not found in the cache. Key: ' ||
2343 l_key,
2344 MSG_LEVEL_DEBUG);
2345
2346
2347 DECLARE
2348 l_select_stmt VARCHAR2(32767);
2349 l_cache_index BINARY_INTEGER;
2350
2351 BEGIN
2352
2353 l_select_c := dbms_sql.open_cursor;
2354
2355 l_cache_index := cursor_attr_cache.count + 1;
2356
2357 /*----------------------------------------------------+
2358 | Add the new cursor to the cache if the cache is |
2359 | not already full. |
2360 +----------------------------------------------------*/
2361
2362 IF ( l_cache_index <= MAX_CURSOR_CACHE_SIZE )
2363 THEN
2364 cursor_attr_cache( l_cache_index ) := l_key;
2365 cursor_cache( l_cache_index ) := l_select_c;
2366
2367 p_keep_cursor_open_flag := TRUE;
2368
2369 ELSE p_keep_cursor_open_flag := FALSE;
2370 END IF;
2371
2372
2373 l_select_stmt := build_select_sql( p_system_info,
2374 p_profile_info,
2375 p_account_class,
2376 p_customer_trx_id,
2377 p_receivable_application_id,
2378 p_br_unpaid_ccid );
2379
2380 --Order by clause not required as only one account class processed at a time
2381 ------------------------------------------------------------
2382 -- Parse
2383 ------------------------------------------------------------
2384 debug( ' Parsing select stmt', MSG_LEVEL_DEBUG );
2385
2386 dbms_sql.parse( l_select_c, l_select_stmt, dbms_sql.v7);
2387
2388
2389 ------------------------------------------------------------
2390 -- Define columns
2391 ------------------------------------------------------------
2392 define_columns( l_select_c, l_select_rec );
2393
2394
2395 EXCEPTION
2396 WHEN OTHERS THEN
2397 debug( 'Error constructing/parsing select cursor',
2398 MSG_LEVEL_BASIC );
2399 debug(SQLERRM, MSG_LEVEL_BASIC);
2400 RAISE;
2401
2402 END;
2403
2404 print_fcn_label2( 'arp_auto_accounting_br.Get_Select_Cursor()-' );
2405
2406 RETURN( l_select_c );
2407
2408
2409 EXCEPTION
2410 WHEN OTHERS THEN
2411
2412 debug('EXCEPTION: arp_auto_accounting_br.Get_Select_Cursor()',
2413 MSG_LEVEL_BASIC);
2414 debug(SQLERRM, MSG_LEVEL_BASIC);
2415 RAISE;
2416
2417 END;
2418
2419 ----------------------------------------------------------------------------
2420 --
2421 -- PROCEDURE NAME: Bind_Variable
2422 --
2423 -- DECSRIPTION:
2424 -- Bind a variable into the specified cursor.
2425 -- Ignore the 'ORA-01006 - Bind variable doesd not exist'
2426 -- error.
2427 -- This routine is overloaded to deal with different datatypes.
2428 --
2429 -- ARGUMENTS:
2430 -- IN:
2431 -- p_cursor
2432 -- p_bind_variable
2433 -- p_value
2434 --
2435 -- IN/OUT:
2436 --
2437 -- OUT:
2438 --
2439 -- RETURNS:
2440 --
2441 -- NOTES:
2442 --
2443 -- HISTORY:
2444 --
2445
2446 PROCEDURE Bind_Variable( p_cursor IN INTEGER,
2447 p_bind_variable IN VARCHAR2,
2448 p_value IN VARCHAR2
2449 ) IS
2450 BEGIN
2451 dbms_sql.bind_variable( p_cursor,
2452 p_bind_variable,
2453 p_value );
2454
2455 EXCEPTION
2456 WHEN OTHERS THEN
2457 IF (SQLCODE = -1006)
2458 THEN NULL;
2459 ELSE RAISE;
2460 END IF;
2461
2462 END;
2463
2464 PROCEDURE Bind_Variable( p_cursor IN INTEGER,
2465 p_bind_variable IN VARCHAR2,
2466 p_value IN INTEGER
2467 ) IS
2468 BEGIN
2469 dbms_sql.bind_variable( p_cursor,
2470 p_bind_variable,
2471 p_value );
2472
2473 EXCEPTION
2474 WHEN OTHERS THEN
2475 IF (SQLCODE = -1006)
2476 THEN NULL;
2477 ELSE RAISE;
2478 END IF;
2479
2480 END;
2481
2482
2483 PROCEDURE Bind_Variable( p_cursor IN INTEGER,
2484 p_bind_variable IN VARCHAR2,
2485 p_value IN DATE
2486 ) IS
2487 BEGIN
2488 dbms_sql.bind_variable( p_cursor,
2489 p_bind_variable,
2490 p_value );
2491
2492 EXCEPTION
2493 WHEN OTHERS THEN
2494 IF (SQLCODE = -1006)
2495 THEN NULL;
2496 ELSE RAISE;
2497 END IF;
2498
2499 END;
2500
2501
2502 ----------------------------------------------------------------------------
2503 --
2504 -- PROCEDURE NAME: Bind_All_Variables
2505 --
2506 -- DECSRIPTION:
2507 -- Bind all possible variable into the select cursor.
2508 -- If the cursor is invalid, rebuild and reparse the
2509 -- select statement and pass the new cursor value back
2510 -- to the calling function.
2511 --
2512 -- ARGUMENTS:
2513 -- IN:
2514 -- p_cursor
2515 -- p_system_info
2516 -- p_profile_info
2517 -- p_account_class
2518 -- p_customer_trx_id
2519 -- p_br_unpaid_ccid
2520 --
2521 -- IN/OUT:
2522 -- p_cursor
2523 --
2524 -- OUT:
2525 -- p_keep_cursor_open_flag
2526 --
2527 -- RETURNS:
2528 --
2529 -- NOTES:
2530 --
2531 -- HISTORY:
2532 --
2533
2534 PROCEDURE Bind_All_Variables(
2535 p_cursor IN OUT NOCOPY BINARY_INTEGER,
2536 p_system_info IN
2537 arp_trx_global.system_info_rec_type,
2538 p_profile_info IN
2539 arp_trx_global.profile_rec_type,
2540 p_account_class IN VARCHAR2,
2541 p_customer_trx_id IN BINARY_INTEGER,
2542 p_receivable_application_id IN BINARY_INTEGER,
2543 p_br_unpaid_ccid IN BINARY_INTEGER,
2544 p_keep_cursor_open_flag IN OUT NOCOPY BOOLEAN
2545 ) IS
2546
2547 BEGIN
2548
2549 print_fcn_label2( 'arp_auto_accounting_br.Bind_All_Variables()+' );
2550
2551 BEGIN
2552 Bind_Variable(
2553 p_cursor,
2554 ':customer_trx_id',
2555 p_customer_trx_id
2556 );
2557
2558 EXCEPTION
2559
2560 /*-----------------------------------------------------------+
2561 | If the cursor is invalid, the first bind will fail. |
2562 | in that case, recreate and reparse the SQL statement |
2563 | and continue processing. The new cursor is passed back |
2564 | to the calling routine since it is an IN/OUT parameter. |
2565 +-----------------------------------------------------------*/
2566
2567 WHEN INVALID_CURSOR THEN
2568
2569 debug('Handling INVALID_CURSOR exception by reparsing.',
2570 MSG_LEVEL_DEBUG);
2571
2572 p_cursor := Get_Select_Cursor(
2573 system_info,
2574 profile_info,
2575 p_account_class,
2576 p_customer_trx_id,
2577 p_receivable_application_id,
2578 p_br_unpaid_ccid,
2579 p_keep_cursor_open_flag);
2580
2581 Bind_Variable(
2582 p_cursor,
2583 ':customer_trx_id',
2584 p_customer_trx_id
2585 );
2586
2587 WHEN OTHERS THEN RAISE;
2588 END;
2589
2590 Bind_Variable(
2591 p_cursor,
2592 ':br_unpaid_ccid',
2593 p_br_unpaid_ccid
2594 );
2595
2596 Bind_Variable(
2597 p_cursor,
2598 ':application_id',
2599 p_receivable_application_id
2600 );
2601
2602 Bind_Variable(
2603 p_cursor,
2604 ':account_class',
2605 p_account_class
2606 );
2607
2608 print_fcn_label2( 'arp_auto_accounting_br.Bind_All_Variables()-' );
2609
2610 EXCEPTION
2611 WHEN OTHERS THEN
2612 debug('EXCEPTION: arp_auto_accounting_br.Bind_All_Variables()',
2613 MSG_LEVEL_BASIC);
2614 debug(SQLERRM, MSG_LEVEL_BASIC);
2615 RAISE;
2616 END;
2617
2618 ----------------------------------------------------------------------------
2619 --
2620 -- PROCEDURE NAME: get_default_ccids
2621 --
2622 -- DECSRIPTION:
2623 -- Gets default ccids for all possible tables which autoaccounting
2624 -- may be based.
2625 --
2626 -- ARGUMENTS:
2627 -- IN:
2628 -- profile_info
2629 -- account_class
2630 -- trx_type_id
2631 -- site_use_id
2632 -- receipt_method_id
2633 -- bank_account_id
2634 --
2635 -- IN/OUT:
2636 -- ccid_record
2637 --
2638 -- OUT:
2639 --
2640 -- NOTES:
2641 --
2642 -- HISTORY:
2643 --
2644 PROCEDURE get_default_ccids( p_profile_info IN
2645 arp_trx_global.profile_rec_type,
2646 p_account_class IN VARCHAR2,
2647 p_trx_type_id IN BINARY_INTEGER,
2648 p_site_use_id IN BINARY_INTEGER,
2649 p_receipt_method_id IN BINARY_INTEGER,
2650 p_bank_account_id IN BINARY_INTEGER,
2651 p_ccid_record IN OUT NOCOPY ccid_rec_type ) IS
2652
2653 BEGIN
2654 print_fcn_label2( 'arp_auto_accounting_br.get_default_ccids()+' );
2655
2656 --
2657 -- trx type
2658 --
2659 IF( p_trx_type_id is NOT NULL ) THEN
2660 get_trx_type_ccids( p_trx_type_id,
2661 p_ccid_record.trx_type_ccid_rec,
2662 p_ccid_record.trx_type_ccid_unpaidrec,
2663 p_ccid_record.trx_type_ccid_factor,
2664 p_ccid_record.trx_type_ccid_remittance);
2665
2666 END IF;
2667
2668 --
2669 -- billing site ccids
2670 --
2671 IF( p_site_use_id is NOT NULL ) THEN
2672 get_site_use_ccids( p_site_use_id,
2673 p_ccid_record.site_use_ccid_rec,
2674 p_ccid_record.site_use_ccid_unpaidrec,
2675 p_ccid_record.site_use_ccid_factor,
2676 p_ccid_record.site_use_ccid_remittance);
2677
2678 END IF;
2679
2680 --
2681 -- payment method bank account ccids
2682 --
2683 IF (( p_receipt_method_id is NOT NULL ) AND ( p_bank_account_id is NOT NULL)) THEN
2684 get_pym_bact_ccids( p_receipt_method_id,
2685 p_bank_account_id,
2686 p_ccid_record.pym_bact_ccid_factor,
2687 p_ccid_record.pym_bact_ccid_remittance );
2688 END IF;
2689
2690 print_fcn_label2( 'arp_auto_accounting_br.get_default_ccids()-' );
2691
2692 EXCEPTION
2693 WHEN OTHERS THEN
2694 debug('EXCEPTION: arp_auto_accounting_br.get_default_ccids('
2695 || p_account_class || ', '
2696 || to_char(p_trx_type_id) || ', '
2697 || to_char(p_site_use_id) || ', '
2698 || to_char(p_receipt_method_id) || ', '
2699 || to_char(p_bank_account_id)||')', MSG_LEVEL_BASIC);
2700 debug(SQLERRM, MSG_LEVEL_BASIC);
2701 RAISE;
2702 END get_default_ccids;
2703
2704 ----------------------------------------------------------------------------
2705 --
2706 -- PROCEDURE NAME: assemble_code_combination
2707 --
2708 -- DECSRIPTION:
2709 --
2710 --
2711 -- ARGUMENTS:
2712 -- IN:
2713 -- system_info
2714 -- flex_info
2715 -- account_class
2716 -- ccid_record
2717 --
2718 -- IN/OUT:
2719 -- ccid
2720 -- assembled_segments
2721 -- OUT:
2722 --
2723 -- NOTES:
2724 --
2725 --
2726 -- HISTORY:
2727 --
2728 --
2729 PROCEDURE assemble_code_combination(
2730 p_system_info IN arp_trx_global.system_info_rec_type,
2731 p_flex_info IN arp_trx_global.acct_flex_info_rec_type,
2732 p_account_class IN VARCHAR2,
2733 p_ccid_record IN CCID_REC_TYPE,
2734 p_ccid IN OUT NOCOPY BINARY_INTEGER,
2735 p_concat_segments IN OUT NOCOPY VARCHAR2 ) IS
2736
2737 l_table_offset BINARY_INTEGER;
2738 l_cnt BINARY_INTEGER;
2739 l_concat_segments VARCHAR2(800);
2740 l_seg ra_account_default_segments.segment%type;
2741 l_const ra_account_default_segments.constant%type;
2742 l_tbl ra_account_default_segments.table_name%type;
2743 l_ccid BINARY_INTEGER;
2744 l_seg_num BINARY_INTEGER;
2745 l_seg_value gl_code_combinations.segment1%type;
2746 l_delim VARCHAR2(1);
2747
2748 -- to store segment values for binding
2749 --
2750 l_seg_table fnd_flex_ext.SegmentArray;
2751
2752 BEGIN
2753
2754 print_fcn_label2( 'arp_auto_accounting_br.assemble_code_combination()+' );
2755
2756 -- get offset, count for account class (to access plsql tables)
2757 --
2758 IF( p_account_class = REC ) then
2759 l_table_offset := rec_offset;
2760 l_cnt := rec_count;
2761 ELSIF( p_account_class = UNPAIDREC ) then
2762 l_table_offset := unpaidrec_offset;
2763 l_cnt := unpaidrec_count;
2764 ELSIF( p_account_class = FACTOR ) then
2765 l_table_offset := factor_offset;
2766 l_cnt := factor_count;
2767 ELSIF( p_account_class = REMITTANCE ) then
2768 l_table_offset := remittance_offset;
2769 l_cnt := remittance_count;
2770 ELSE
2771 g_error_buffer := 'Invalid account class';
2772 debug( 'EXCEPTION: '||g_error_buffer, MSG_LEVEL_BASIC );
2773 RAISE invalid_account_class;
2774 END IF;
2775
2776 -- loop for each enabled segment
2777 --
2778 FOR i IN 0..l_cnt - 1 LOOP
2779 l_const := autoacc_def_const_t(l_table_offset + i);
2780 l_tbl := autoacc_def_table_t(l_table_offset + i);
2781 l_seg := autoacc_def_segment_t(l_table_offset + i);
2782 l_ccid := -1;
2783
2784 IF( i = 0 ) THEN
2785 l_delim := null;
2786 ELSE
2787 l_delim := p_flex_info.delim;
2788 END IF;
2789
2790 IF( l_const is NOT NULL ) THEN
2791 --
2792 -- constant
2793 --
2794 l_concat_segments := l_concat_segments || l_delim
2795 || l_const;
2796 l_seg_table(i+1) := l_const;
2797
2798 ELSIF( l_tbl is NOT NULL ) THEN
2799 --
2800 -- table-based
2801 --
2802 IF( l_tbl = 'RA_CUST_TRX_TYPES' ) THEN
2803 --
2804 -- For all account classes except REC
2805 --
2806 IF p_account_class = REC THEN
2807 l_ccid := p_ccid_record.trx_type_ccid_rec;
2808 ELSIF p_account_class = UNPAIDREC THEN
2809 l_ccid := p_ccid_record.trx_type_ccid_unpaidrec;
2810 ELSIF p_account_class = FACTOR THEN
2811 l_ccid := p_ccid_record.trx_type_ccid_factor;
2812 ELSIF p_account_class = REMITTANCE THEN
2813 l_ccid := p_ccid_record.trx_type_ccid_remittance;
2814 END IF;
2815
2816 ELSIF( l_tbl = 'RA_SITE_USES' ) THEN
2817 IF p_account_class = REC THEN
2818 l_ccid := p_ccid_record.site_use_ccid_rec;
2819 ELSIF p_account_class = UNPAIDREC THEN
2820 l_ccid := p_ccid_record.site_use_ccid_unpaidrec;
2821 ELSIF p_account_class = FACTOR THEN
2822 l_ccid := p_ccid_record.site_use_ccid_factor;
2823 ELSIF p_account_class = REMITTANCE THEN
2824 l_ccid := p_ccid_record.site_use_ccid_remittance;
2825 END IF;
2826
2827 ELSIF( l_tbl = 'AR_RECEIPT_METHOD_ACCOUNTS' ) THEN
2828 IF p_account_class = FACTOR THEN
2829 l_ccid := p_ccid_record.pym_bact_ccid_factor;
2830 ELSIF p_account_class = REMITTANCE THEN
2831 l_ccid := p_ccid_record.pym_bact_ccid_remittance;
2832 END IF;
2833 ELSE
2834 g_error_buffer := 'Invalid table name: '||l_tbl;
2835 debug( 'EXCEPTION: '||g_error_buffer, MSG_LEVEL_BASIC );
2836 RAISE invalid_table_name;
2837 END IF;
2838
2839 l_seg_num := TO_NUMBER(SUBSTRB(l_seg, LENGTHB('SEGMENT') + 1));
2840
2841 -- Only get segment if have valid ccid
2842 --
2843 IF( l_ccid = -1 ) THEN
2844 l_seg_value := INVALID_SEGMENT;
2845 ELSE
2846 l_seg_value := get_segment_from_glcc( l_ccid, l_seg_num );
2847
2848 IF( l_seg_value IS NULL ) THEN
2849 --
2850 -- assign invalid segment value if no data found
2851 --
2852 l_seg_value := INVALID_SEGMENT;
2853 END IF;
2854 END IF;
2855
2856 l_concat_segments := l_concat_segments || l_delim || l_seg_value;
2857 l_seg_table(i+1) := l_seg_value;
2858
2859 END IF; -- if const is not null
2860 END LOOP;
2861
2862 debug('p_account_class ' || p_account_class);
2863 debug('l_concat_segments ' || l_concat_segments);
2864
2865 -- call ccid reader
2866 p_ccid := search_glcc_for_ccid(
2867 system_info,
2868 l_seg_table,
2869 l_cnt,
2870 p_account_class,
2871 l_concat_segments );
2872
2873 -- return concat segs, and ccid
2874 p_concat_segments := l_concat_segments;
2875
2876 print_fcn_label2( 'arp_auto_accounting_br.assemble_code_combination()-' );
2877
2878 EXCEPTION
2879 WHEN OTHERS THEN
2880 debug('EXCEPTION: arp_auto_accounting_br.assemble_code_combination('
2881 || p_account_class || ')', MSG_LEVEL_BASIC);
2882 debug(SQLERRM, MSG_LEVEL_BASIC);
2883 RAISE;
2884 END assemble_code_combination;
2885
2886 ----------------------------------------------------------------------------
2887 --
2888 -- PROCEDURE NAME: flex_manager
2889 --
2890 -- DECSRIPTION:
2891 -- Entry point for flexfield assembly.
2892 --
2893 -- ARGUMENTS:
2894 -- IN:
2895 -- account_class
2896 -- trx_type_id
2897 -- site_use_id
2898 -- receipt_method_id
2899 -- bank_account_id
2900 --
2901 -- IN/OUT:
2902 -- ccid
2903 -- concat_segments
2904 --
2905 -- RETURNS:
2906 -- 1 if success, 0 otherwise
2907 --
2908 -- NOTES:
2909 --
2910 -- HISTORY:
2911 --
2912 PROCEDURE flex_manager( p_account_class IN VARCHAR2,
2913 p_trx_type_id IN BINARY_INTEGER,
2914 p_site_use_id IN BINARY_INTEGER,
2915 p_receipt_method_id IN BINARY_INTEGER,
2916 p_bank_account_id IN BINARY_INTEGER,
2917 p_ccid IN OUT NOCOPY BINARY_INTEGER,
2918 p_concat_segments IN OUT NOCOPY VARCHAR2 ) IS
2919
2920 l_ccid_record ccid_rec_type;
2921
2922 PROCEDURE print_params IS
2923 BEGIN
2924 debug('EXCEPTION: arp_auto_accounting_br.flex_manager('
2925 || p_account_class || ', '
2926 || to_char(p_trx_type_id) || ', '
2927 || to_char(p_site_use_id) || ', '
2928 || to_char(p_receipt_method_id) || ', '
2929 || to_char(p_bank_account_id) || ') ',
2930 MSG_LEVEL_DEBUG);
2931
2932 END;
2933
2934 BEGIN
2935
2936 print_fcn_label( 'arp_auto_accounting_br.flex_manager()+' );
2937
2938 debug( ' account_class='||p_account_class, MSG_LEVEL_DEBUG );
2939 debug( ' trx_type_id='||to_char(p_trx_type_id), MSG_LEVEL_DEBUG );
2940 debug( ' site_use_id='||to_char(p_site_use_id), MSG_LEVEL_DEBUG );
2941 debug( ' receipt_method_id='||to_char(p_receipt_method_id), MSG_LEVEL_DEBUG );
2942 debug( ' bank_account_id='||to_char(p_bank_account_id), MSG_LEVEL_DEBUG );
2943
2944 --
2945 -- Initialize
2946 --
2947 p_concat_segments := NULL;
2948 p_ccid := -1;
2949
2950 --
2951 --
2952 --
2953 get_default_ccids( profile_info,
2954 p_account_class,
2955 p_trx_type_id,
2956 p_site_use_id,
2957 p_receipt_method_id,
2958 p_bank_account_id,
2959 l_ccid_record );
2960
2961 -- Dump ccid record, item type
2962 --
2963 dump_ccid_record( l_ccid_record );
2964
2965 --
2966 -- Assemble segments and get ccid
2967 --
2968 assemble_code_combination( system_info,
2969 flex_info,
2970 p_account_class,
2971 l_ccid_record,
2972 p_ccid,
2973 p_concat_segments );
2974
2975 debug( ' ccid= '||to_char(p_ccid), MSG_LEVEL_DEBUG );
2976 debug( ' concat_segs= '||p_concat_segments, MSG_LEVEL_DEBUG );
2977
2978 print_fcn_label( 'arp_auto_accounting_br.flex_manager()-' );
2979
2980 EXCEPTION
2981 WHEN OTHERS THEN
2982 print_params;
2983 debug(SQLERRM, MSG_LEVEL_BASIC);
2984 RAISE;
2985
2986 END flex_manager;
2987
2988
2989 ----------------------------------------------------------------------------
2990 --
2991 -- FUNCTION NAME: build_delete_sql
2992 --
2993 -- DECSRIPTION:
2994 --
2995 -- ARGUMENTS:
2996 -- IN:
2997 -- system_info
2998 -- profile_info
2999 -- account_class
3000 -- customer_trx_id
3001 --
3002 -- IN/OUT:
3003 --
3004 -- OUT:
3005 --
3006 -- RETURNS:
3007 -- delete sql
3008 --
3009 -- NOTES:
3010 --
3011 -- HISTORY:
3012 --
3013 FUNCTION build_delete_sql( p_system_info IN
3014 arp_trx_global.system_info_rec_type,
3015 p_profile_info IN
3016 arp_trx_global.profile_rec_type,
3017 p_account_class IN VARCHAR2,
3018 p_customer_trx_id IN BINARY_INTEGER)
3019 RETURN VARCHAR2 IS
3020
3021 l_delete_stmt VARCHAR2(1000);
3022
3023 BEGIN
3024 print_fcn_label( 'arp_auto_accounting_br.build_delete_sql()+' );
3025
3026 --
3027 -- Construct the Delete Statement
3028 --
3029 l_delete_stmt :=
3030 'DELETE from ar_distributions ard' ||
3031 CRLF || 'WHERE ard.source_id in' ||
3032 CRLF || '(SELECT th.transaction_history_id' ||
3033 CRLF || 'FROM ar_transaction_history th' ||
3034 CRLF || 'WHERE th.customer_trx_id = '||p_customer_trx_id ||
3035 CRLF || 'AND th.postable_flag = ''Y''' ||
3036 CRLF || 'AND th.posting_control_id = -3' ||
3037 CRLF || 'AND th.gl_posted_date IS NULL' ||
3038 CRLF || 'AND nvl(th.current_record_flag,''N'') = ''Y''' ||
3039 CRLF || 'AND nvl(th.current_accounted_flag, ''N'') = ''Y'')' ||
3040 CRLF || 'AND ard.source_table = ''TH''' ||
3041 CRLF || 'AND ard.source_type = ''' || p_account_class || '''';
3042
3043 debug( l_delete_stmt, MSG_LEVEL_DEBUG );
3044 debug( ' len(l_delete_stmt)=' || to_char(length(l_delete_stmt)),
3045 MSG_LEVEL_DEBUG );
3046
3047 print_fcn_label( 'arp_auto_accounting_br.build_delete_sql()-' );
3048
3049 RETURN l_delete_stmt;
3050
3051
3052 EXCEPTION
3053 WHEN OTHERS THEN
3054 debug('EXCEPTION: arp_auto_accounting_br.build_delete_sql()',
3055 MSG_LEVEL_BASIC);
3056 debug(SQLERRM, MSG_LEVEL_BASIC);
3057 RAISE;
3058 END build_delete_sql;
3059
3060
3061 ----------------------------------------------------------------------------
3062 PROCEDURE get_column_values( p_select_c IN INTEGER,
3063 p_select_rec OUT NOCOPY select_rec_type ) IS
3064 BEGIN
3065 print_fcn_label2( 'arp_auto_accounting_br.get_column_values()+' );
3066
3067 dbms_sql.column_value( p_select_c, 1, p_select_rec.customer_trx_id );
3068 dbms_sql.column_value( p_select_c, 2, p_select_rec.cust_trx_type_id);
3069 dbms_sql.column_value( p_select_c, 3, p_select_rec.site_use_id);
3070 dbms_sql.column_value( p_select_c, 4, p_select_rec.drawee_id);
3071 dbms_sql.column_value( p_select_c, 5, p_select_rec.bill_to_site_use_id);
3072 dbms_sql.column_value( p_select_c, 6, p_select_rec.br_unpaid_flag);
3073 dbms_sql.column_value( p_select_c, 7, p_select_rec.transaction_history_id);
3074 dbms_sql.column_value( p_select_c, 8, p_select_rec.batch_id);
3075 dbms_sql.column_value( p_select_c, 9, p_select_rec.gl_date);
3076 dbms_sql.column_value( p_select_c, 10, p_select_rec.source_type);
3077 dbms_sql.column_value( p_select_c, 11, p_select_rec.amount);
3078 dbms_sql.column_value( p_select_c, 12, p_select_rec.acctd_amount);
3079 dbms_sql.column_value( p_select_c, 13, p_select_rec.currency_code);
3080 dbms_sql.column_value( p_select_c, 14, p_select_rec.currency_conversion_rate);
3081 dbms_sql.column_value( p_select_c, 15, p_select_rec.currency_conversion_type);
3082 dbms_sql.column_value( p_select_c, 16, p_select_rec.currency_conversion_date);
3083 dbms_sql.column_value( p_select_c, 17, p_select_rec.receipt_method_id);
3084 dbms_sql.column_value( p_select_c, 18, p_select_rec.bank_account_id);
3085 dbms_sql.column_value( p_select_c, 19, p_select_rec.concatenated_segments);
3086 dbms_sql.column_value( p_select_c, 20, p_select_rec.code_combination_id);
3087 dbms_sql.column_value( p_select_c, 21, p_select_rec.br_unpaid_ccid);
3088
3089 print_fcn_label2( 'arp_auto_accounting_br.get_column_values()-' );
3090 EXCEPTION
3091 WHEN OTHERS THEN
3092 debug('EXCEPTION: arp_auto_accounting_br.get_column_values()',
3093 MSG_LEVEL_BASIC);
3094 debug(SQLERRM, MSG_LEVEL_BASIC);
3095 RAISE;
3096 END get_column_values;
3097
3098 ----------------------------------------------------------------------------
3099 PROCEDURE insert_dist_row( p_system_info IN
3100 arp_trx_global.system_info_rec_type,
3101 p_profile_info IN
3102 arp_trx_global.profile_rec_type,
3103 p_select_rec IN select_rec_type,
3104 p_receivable_application_id IN NUMBER ) IS
3105 l_amount_dr NUMBER;
3106 l_acctd_amount_dr NUMBER;
3107
3108 l_amount_cr NUMBER;
3109 l_acctd_amount_cr NUMBER;
3110
3111 /* Added for mrc trigger replacement */
3112 l_ae_line_rec ar_distributions%ROWTYPE;
3113 l_ae_line_rec_empty ar_distributions%ROWTYPE;
3114 l_dummy NUMBER;
3115
3116 BEGIN
3117 print_fcn_label2( 'arp_auto_accounting_br.insert_dist_row()+' );
3118
3119 /*----------------------------------------------------------------------------+
3120 | The amounts and accounted amounts hit the same Dr or Cr buckets, |
3121 | hence the OR cond is used, for all account classes other than normal |
3122 | used, for all account classes other than normal receipt reversals Dr the |
3123 | BR account with the payment schedule amount (if negative then CR). For |
3124 | For Receipt application reversals, Dr the BR account, also handle positive |
3125 | applications (sign of receivable), these are CR |
3126 +----------------------------------------------------------------------------*/
3127 IF ((sign(p_select_rec.amount) <> -1) OR
3128 (sign(p_select_rec.acctd_amount) <> -1)) THEN
3129
3130 IF (p_receivable_application_id IS NULL) THEN --Dr
3131
3132 l_amount_dr := p_select_rec.amount;
3133 l_acctd_amount_dr := p_select_rec.acctd_amount;
3134 l_amount_cr := NULL;
3135 l_acctd_amount_cr := NULL;
3136
3137 ELSE --Cr the BR account cond due to Receipt positive amount app
3138
3139 l_amount_dr := NULL;
3140 l_acctd_amount_dr := NULL;
3141 l_amount_cr := p_select_rec.amount;
3142 l_acctd_amount_cr := p_select_rec.acctd_amount;
3143
3144 END IF;
3145
3146 ELSIF ((sign(p_select_rec.amount) = -1) OR
3147 (sign(p_select_rec.acctd_amount) = -1)) THEN
3148
3149 IF (p_receivable_application_id IS NULL) THEN --Cr the BR account
3150
3151 l_amount_dr := NULL;
3152 l_acctd_amount_dr := NULL;
3153 l_amount_cr := p_select_rec.amount;
3154 l_acctd_amount_cr := p_select_rec.acctd_amount;
3155
3156 ELSE --Dr the Bills Rec account cond due to Receipt negative amount app
3157 l_amount_dr := p_select_rec.amount;
3158 l_acctd_amount_dr := p_select_rec.acctd_amount;
3159 l_amount_cr := NULL;
3160 l_acctd_amount_cr := NULL;
3161
3162 END IF;
3163
3164 END IF;
3165
3166 /*--------------------------------------------------------------------------------------+
3167 | If the unpaid flag is yes then for the unpaid bills receivable account if the account |
3168 | to be reclassified is the same as the account which is derived by autoaccounting, then|
3169 | the accounting will not be created. |
3170 +---------------------------------------------------------------------------------------*/
3171 IF ((nvl(p_select_rec.br_unpaid_flag,'N') = 'Y') AND (p_select_rec.source_type = UNPAIDREC)
3172 AND (p_select_rec.br_unpaid_ccid = p_select_rec.code_combination_id)) THEN
3173
3174 debug('Derived Unpaid account matches existing Unpaid account ');
3175 NULL;
3176
3177 ELSE
3178 debug('Creating accounting');
3179 -- Initialize
3180 l_ae_line_rec := l_ae_line_rec_empty;
3181
3182 -- assign line elements
3183 l_ae_line_rec.source_type := p_select_rec.source_type;
3184 l_ae_line_rec.source_id := p_select_rec.transaction_history_id;
3185 l_ae_line_rec.source_table := 'TH';
3186 l_ae_line_rec.code_combination_id := p_select_rec.code_combination_id;
3187 l_ae_line_rec.amount_dr := abs(l_amount_dr);
3188 l_ae_line_rec.amount_cr := abs(l_amount_cr);
3189 l_ae_line_rec.acctd_amount_dr := abs(l_acctd_amount_dr);
3190 l_ae_line_rec.acctd_amount_cr := abs(l_acctd_amount_cr);
3191 l_ae_line_rec.currency_code := p_select_rec.currency_code;
3192 l_ae_line_rec.currency_conversion_rate :=
3193 p_select_rec.currency_conversion_rate;
3194 l_ae_line_rec.currency_conversion_type :=
3195 p_select_rec.currency_conversion_type;
3196 l_ae_line_rec.currency_conversion_date :=
3197 to_date(p_select_rec.currency_conversion_date, 'J');
3198
3199 l_ae_line_rec.third_party_id := p_select_rec.drawee_id;
3200 l_ae_line_rec.third_party_sub_id := p_select_rec.site_use_id;
3201
3202 IF (g_ae_sys_rec.sob_type = 'P' ) THEN
3203
3204 /* caling table handler instead of direct insert */
3205 arp_distributions_pkg.insert_p( l_ae_line_rec, l_dummy);
3206 --{BUG#4301323
3207 -- ELSE
3208 /* need to insert records into the MRC table. Calling new
3209 mrc engine */
3210 -- IF PG_DEBUG in ('Y', 'C') THEN
3211 -- arp_standard.debug('insert_dist_row: ' || 'getting information for the mrc trigger');
3212 -- arp_standard.debug('insert_dist_row: ' || 'source type = ' || l_ae_line_rec.source_type);
3213 -- arp_standard.debug('insert_dist_row: ' || 'source table = ' || l_ae_line_rec.source_table);
3214 -- arp_standard.debug('insert_dist_row: ' || 'source_id = ' || to_char(l_ae_line_rec.source_id));
3215 -- END IF;
3216
3217 -- before we call the ar_mrc_engine, we need the line_id of
3218 -- the primary row.
3219 -- IF (l_ae_line_rec.source_type = 'EXCH_GAIN' or
3220 -- l_ae_line_rec.source_type = 'EXCH_LOSS' or
3221 -- l_ae_line_rec.source_type = 'CURR_ROUND' ) THEN
3222
3223 -- select ar_distributions_s.nextval
3224 -- into l_ae_line_rec.line_id
3225 -- from dual;
3226 -- ELSE
3227 -- select line_id
3228 -- into l_ae_line_rec.line_id
3229 -- from ar_distributions
3230 -- where source_id = l_ae_line_rec.source_id
3231 -- and source_table = l_ae_line_rec.source_table
3232 -- and source_type = l_ae_line_rec.source_type
3233 -- and source_type_secondary IS NULL;
3234 -- END IF;
3235 -- IF PG_DEBUG in ('Y', 'C') THEN
3236 -- arp_standard.debug('insert_dist_row: ' || 'calling arp_mrc_acct_main.insert_mrc_dis_data');
3237 -- END IF;
3238
3239 -- arp_mrc_acct_main.insert_mrc_dis_data
3240 -- (l_ae_line_rec,
3241 -- g_ae_sys_rec.set_of_books_id);
3242 END IF;
3243
3244 END IF;
3245 print_fcn_label2( 'arp_auto_accounting_br.insert_dist_row()-' );
3246 EXCEPTION
3247 WHEN OTHERS THEN
3248 debug('EXCEPTION: arp_auto_accounting_br.insert_dist_row()',
3249 MSG_LEVEL_BASIC);
3250 debug(SQLERRM, MSG_LEVEL_BASIC);
3251 RAISE;
3252 END insert_dist_row;
3253
3254 ----------------------------------------------------------------------------
3255 PROCEDURE dump_select_rec( p_select_rec IN select_rec_type ) IS
3256 BEGIN
3257 print_fcn_label2( 'arp_auto_accounting_br.dump_select_rec()+' );
3258
3259 debug( ' Dumping select record: ', MSG_LEVEL_DEBUG );
3260 debug( ' customer_trx_id='
3261 || to_char( p_select_rec.customer_trx_id ), MSG_LEVEL_DEBUG );
3262 debug( ' cust_trx_type_id='
3263 || to_char( p_select_rec.cust_trx_type_id), MSG_LEVEL_DEBUG );
3264 debug( ' site_use_id='
3265 || to_char( p_select_rec.site_use_id),
3266 MSG_LEVEL_DEBUG );
3267 debug( ' drawee_id='
3268 || to_char( p_select_rec.drawee_id ), MSG_LEVEL_DEBUG );
3269 debug( ' bill_to_site_use_id='
3270 || to_char( p_select_rec.bill_to_site_use_id ), MSG_LEVEL_DEBUG );
3271 debug( ' br_unpaid_flag ='
3272 || p_select_rec.br_unpaid_flag, MSG_LEVEL_DEBUG );
3273 debug( ' transaction_history_id='
3274 || to_char( p_select_rec.transaction_history_id ), MSG_LEVEL_DEBUG );
3275 debug( ' batch_id='
3276 || to_char( p_select_rec.batch_id ), MSG_LEVEL_DEBUG );
3277 debug( ' gl_date='
3278 || p_select_rec.gl_date, MSG_LEVEL_DEBUG );
3279 debug( ' source_type='
3280 || p_select_rec.source_type , MSG_LEVEL_DEBUG );
3281 debug( ' amount=' || to_char(p_select_rec.amount), MSG_LEVEL_DEBUG );
3282 debug( ' acctd_amount=' || to_char(p_select_rec.acctd_amount), MSG_LEVEL_DEBUG );
3283 debug( ' currency_code=' || p_select_rec.currency_code,
3284 MSG_LEVEL_DEBUG );
3285 debug( ' currency_conversion_rate='
3286 || to_char( p_select_rec.currency_conversion_rate ), MSG_LEVEL_DEBUG );
3287 debug( ' currency_conversion_type=' ||
3288 p_select_rec.currency_conversion_type, MSG_LEVEL_DEBUG );
3289 debug( ' currency_conversion_date=' ||
3290 p_select_rec.currency_conversion_date, MSG_LEVEL_DEBUG );
3291 debug( ' receipt_method_id=' ||
3292 p_select_rec.receipt_method_id, MSG_LEVEL_DEBUG );
3293 debug( ' bank_account_id=' ||
3294 p_select_rec.bank_account_id, MSG_LEVEL_DEBUG );
3295 debug( ' concatenated_segments='
3296 || p_select_rec.concatenated_segments, MSG_LEVEL_DEBUG );
3297 debug( ' code_combination_id='
3298 || to_char( p_select_rec.code_combination_id ), MSG_LEVEL_DEBUG );
3299 debug( ' br_unpaid_ccid='
3300 || to_char( p_select_rec.br_unpaid_ccid), MSG_LEVEL_DEBUG );
3301
3302 print_fcn_label2( 'arp_auto_accounting_br.dump_select_rec()-' );
3303
3304 EXCEPTION
3305 WHEN OTHERS THEN
3306 debug('EXCEPTION: arp_auto_accounting_br.dump_select_rec()',
3307 MSG_LEVEL_BASIC);
3308 debug(SQLERRM, MSG_LEVEL_BASIC);
3309 RAISE;
3310 END dump_select_rec;
3311
3312
3313 ----------------------------------------------------------------------------
3314 PROCEDURE process_line( p_system_info IN
3315 arp_trx_global.system_info_rec_type,
3316 p_select_rec IN OUT NOCOPY select_rec_type,
3317 p_failure_count IN OUT NOCOPY BINARY_INTEGER ) IS
3318
3319 l_boolean BOOLEAN;
3320 l_error_message VARCHAR2(256);
3321
3322
3323 BEGIN
3324 --
3325 -- Set the CCID validation date
3326 --
3327 validation_date := TO_DATE(p_select_rec.gl_date, 'J');
3328 --
3329 -- Call Flex manager
3330 --
3331 IF( p_select_rec.code_combination_id IS NULL ) THEN
3332
3333 flex_manager( p_select_rec.source_type,
3334 p_select_rec.cust_trx_type_id,
3335 p_select_rec.site_use_id,
3336 p_select_rec.receipt_method_id,
3337 p_select_rec.bank_account_id,
3338 p_select_rec.code_combination_id,
3339 p_select_rec.concatenated_segments);
3340 END IF;
3341
3342 IF( p_select_rec.code_combination_id = -1 ) THEN
3343
3344 -- keep track of # rows where ccid was not found
3345 -- if > 0, then need to call AOL dynamic insert
3346 -- on the client-side
3347 --
3348 p_failure_count := nvl(p_failure_count, 0) + 1;
3349
3350 debug('process_line: Failure count : '||to_char(p_failure_count),
3351 MSG_LEVEL_DEBUG);
3352 END IF;
3353
3354 END process_line;
3355
3356 ----------------------------------------------------------------------------
3357 PROCEDURE do_autoaccounting_internal(
3358 p_mode IN VARCHAR2,
3359 p_account_class IN VARCHAR2,
3360 p_customer_trx_id IN NUMBER,
3361 p_receivable_application_id IN NUMBER,
3362 p_br_unpaid_ccid IN NUMBER,
3363 p_cust_trx_type_id IN NUMBER,
3364 p_site_use_id IN NUMBER,
3365 p_receipt_method_id IN NUMBER,
3366 p_bank_account_id IN NUMBER,
3367 p_ccid IN OUT NOCOPY NUMBER,
3368 p_concat_segments IN OUT NOCOPY VARCHAR2,
3369 p_failure_count IN OUT NOCOPY NUMBER )
3370 IS
3371
3372
3373 l_select_rec select_rec_type;
3374 l_null_rec CONSTANT select_rec_type := l_select_rec;
3375
3376 -- Cursors
3377 --
3378 l_select_c INTEGER;
3379 l_delete_c INTEGER;
3380
3381 l_ignore INTEGER;
3382 l_boolean BOOLEAN;
3383 l_first_fetch BOOLEAN;
3384 l_temp BINARY_INTEGER;
3385 l_keep_cursor_open_flag BOOLEAN := FALSE;
3386
3387 BEGIN
3388
3389 print_fcn_label( 'arp_auto_accounting_br.do_autoaccounting_internal()+' );
3390
3391
3392 SAVEPOINT ar_auto_accounting;
3393
3394 -- MRC Trigger Replacement: Initialize new global variable.
3395 g_ae_sys_rec.sob_type := NVL(ARP_ACCT_MAIN.ae_sys_rec.sob_type,'P');
3396 g_ae_sys_rec.set_of_books_id := ARP_ACCT_MAIN.ae_sys_rec.set_of_books_id;
3397
3398 --------------------------------------------------------------------------
3399 -- Process modes
3400 --------------------------------------------------------------------------
3401 IF( p_mode = G ) THEN
3402 --
3403 -- Get mode, populate record immediately
3404 --
3405 l_select_rec := l_null_rec; -- start with null record
3406
3407 l_select_rec.customer_trx_id := p_customer_trx_id;
3408 l_select_rec.source_type := p_account_class;
3409 l_select_rec.cust_trx_type_id := p_cust_trx_type_id;
3410 l_select_rec.site_use_id := p_site_use_id;
3411 l_select_rec.receipt_method_id := p_receipt_method_id;
3412 l_select_rec.bank_account_id := p_bank_account_id;
3413
3414 dump_select_rec( l_select_rec );
3415
3416 process_line( system_info,
3417 l_select_rec,
3418 p_failure_count );
3419
3420 --------------------------------------------------------------------
3421 -- Update IN OUT NOCOPY parameters for output to Form fields
3422 --------------------------------------------------------------------
3423 p_ccid := l_select_rec.code_combination_id;
3424 p_concat_segments := l_select_rec.concatenated_segments;
3425
3426 ELSE -- I, U or D modes
3427
3428
3429 IF( p_mode in (U, D) ) THEN
3430 --
3431 -- Delete distributions in Update and Delete mode
3432 --
3433
3434 ----------------------------------------------------------------
3435 -- Construct delete stmt
3436 ----------------------------------------------------------------
3437 DECLARE
3438 l_delete_stmt VARCHAR2(32767);
3439
3440 BEGIN
3441
3442 l_delete_c := dbms_sql.open_cursor;
3443 l_delete_stmt := build_delete_sql( system_info,
3444 profile_info,
3445 p_account_class,
3446 p_customer_trx_id );
3447
3448 dbms_sql.parse( l_delete_c, l_delete_stmt, dbms_sql.v7 );
3449
3450 EXCEPTION
3451 WHEN OTHERS THEN
3452 debug( 'Error constructing/parsing delete cursor',
3453 MSG_LEVEL_BASIC );
3454 debug(SQLERRM, MSG_LEVEL_BASIC);
3455 RAISE;
3456
3457 END;
3458
3459 ----------------------------------------------------------------
3460 -- Delete distributions
3461 ----------------------------------------------------------------
3462 debug( ' Deleting distributions', MSG_LEVEL_DEBUG );
3463
3464 BEGIN
3465 l_ignore := dbms_sql.execute( l_delete_c );
3466
3467 debug( to_char(l_ignore) || ' row(s) deleted',
3468 MSG_LEVEL_DEBUG );
3469
3470 close_cursor( l_delete_c );
3471
3472 EXCEPTION
3473 WHEN OTHERS THEN
3474 debug( 'Error executing delete stmt', MSG_LEVEL_BASIC );
3475 debug(SQLERRM, MSG_LEVEL_BASIC);
3476 RAISE;
3477
3478 END;
3479
3480
3481 END IF; -- if mode = U, D
3482
3483 IF( p_mode in (I, U) ) THEN
3484
3485 --
3486 -- Insert distributions in Insert and Update mode
3487 --
3488
3489 --
3490 -- Fetch records using select stmt
3491 --
3492
3493 l_select_c := Get_Select_Cursor(
3494 system_info,
3495 profile_info,
3496 p_account_class,
3497 p_customer_trx_id,
3498 p_receivable_application_id,
3499 p_br_unpaid_ccid,
3500 l_keep_cursor_open_flag);
3501
3502
3503 Bind_All_Variables(
3504 l_select_c,
3505 system_info,
3506 profile_info,
3507 p_account_class,
3508 p_customer_trx_id,
3509 p_receivable_application_id,
3510 p_br_unpaid_ccid,
3511 l_keep_cursor_open_flag);
3512
3513 l_first_fetch := TRUE;
3514
3515 ----------------------------------------------------------------
3516 -- Execute select stmt
3517 ----------------------------------------------------------------
3518 BEGIN
3519
3520 debug( ' Executing select stmt', MSG_LEVEL_DEBUG );
3521
3522 l_ignore := dbms_sql.execute( l_select_c );
3523
3524 EXCEPTION
3525 WHEN OTHERS THEN
3526 debug( 'Error executing select cursor', MSG_LEVEL_BASIC );
3527 debug(SQLERRM, MSG_LEVEL_BASIC);
3528 RAISE;
3529 END;
3530
3531 ---------------------------------------------------------------
3532 -- Fetch rows
3533 ---------------------------------------------------------------
3534 debug( ' Fetching select stmt', MSG_LEVEL_DEBUG );
3535
3536 LOOP
3537
3538
3539 BEGIN
3540
3541 IF dbms_sql.fetch_rows( l_select_c ) > 0 THEN
3542
3543 debug( ' Fetched a row', MSG_LEVEL_DEBUG );
3544
3545 l_first_fetch := FALSE;
3546
3547 l_select_rec := l_null_rec;
3548 get_column_values( l_select_c, l_select_rec );
3549
3550 dump_select_rec( l_select_rec );
3551
3552 ELSE
3553 -- no more rows to fetch
3554 --
3555 debug( ' Done fetching', MSG_LEVEL_DEBUG );
3556
3557 IF ( l_keep_cursor_open_flag = FALSE )
3558 THEN close_cursor( l_select_c );
3559 END IF;
3560
3561 -- No rows selected
3562 IF( l_first_fetch ) THEN
3563
3564 debug( ' raising NO_DATA_FOUND',
3565 MSG_LEVEL_DEBUG );
3566 RAISE NO_DATA_FOUND;
3567
3568 END IF;
3569
3570 EXIT;
3571 END IF;
3572
3573 EXCEPTION
3574 WHEN NO_DATA_FOUND THEN
3575 RAISE;
3576 WHEN OTHERS THEN
3577 debug( 'Error fetching select cursor',
3578 MSG_LEVEL_BASIC );
3579 debug(SQLERRM, MSG_LEVEL_BASIC);
3580 RAISE;
3581
3582 END;
3583
3584 process_line( system_info,
3585 l_select_rec,
3586 p_failure_count );
3587
3588 -----------------------------------------------------------
3589 -- Insert row
3590 -----------------------------------------------------------
3591 BEGIN
3592 insert_dist_row( system_info,
3593 profile_info,
3594 l_select_rec,
3595 p_receivable_application_id );
3596 EXCEPTION
3597 WHEN OTHERS THEN
3598 debug( 'Error inserting distributions',
3599 MSG_LEVEL_BASIC );
3600 debug(SQLERRM, MSG_LEVEL_BASIC);
3601 RAISE;
3602 END;
3603
3604 IF( l_select_rec.code_combination_id = -1 ) THEN
3605
3606 IF( p_account_class = REC ) THEN
3607
3608 put_message_on_stack(
3609 MSG_COMPLETE_REC_ACCOUNT,
3610 l_select_rec.concatenated_segments );
3611
3612 ELSIF( p_account_class = UNPAIDREC ) THEN
3613
3614 put_message_on_stack(
3615 MSG_COMPLETE_UNP_ACCOUNT,
3616 l_select_rec.concatenated_segments );
3617
3618 ELSIF( p_account_class = FACTOR ) THEN
3619
3620 put_message_on_stack(
3621 MSG_COMPLETE_FAC_ACCOUNT,
3622 l_select_rec.concatenated_segments );
3623
3624 ELSIF( p_account_class = REMITTANCE ) THEN
3625
3626 put_message_on_stack(
3627 MSG_COMPLETE_REM_ACCOUNT,
3628 l_select_rec.concatenated_segments );
3629
3630 END IF; --end if account class
3631
3632 END IF; --end if ccid is -1
3633
3634 END LOOP;
3635
3636 END IF; -- IF( p_mode in (I, U) )
3637
3638 END IF; -- IF( p_mode = G )
3639
3640
3641 -- Check if failed to get any ccids
3642 --
3643 debug( ' p_failure_count='||to_char(p_failure_count) ,
3644 MSG_LEVEL_DEBUG);
3645
3646 IF ( l_keep_cursor_open_flag = FALSE )
3647 THEN close_cursor( l_select_c );
3648 END IF;
3649
3650 close_cursor( l_delete_c );
3651
3652
3653 print_fcn_label( 'arp_auto_accounting_br.do_autoaccounting_internal()-' );
3654
3655
3656 EXCEPTION
3657 WHEN NO_DATA_FOUND THEN
3658
3659 IF ( l_keep_cursor_open_flag = FALSE )
3660 THEN close_cursor( l_select_c );
3661 END IF;
3662
3663 close_cursor( l_delete_c );
3664
3665 IF( p_mode = G ) THEN
3666 NULL; -- Don't raise for Get mode, otherwise the
3667 -- IN/OUT vars ccid, concat_segments do not
3668 -- get populated.
3669 ELSE
3670 RAISE;
3671 END IF;
3672
3673 WHEN OTHERS THEN
3674 debug( 'EXCEPTION: arp_auto_accounting_br.do_autoaccounting_internal()',
3675 MSG_LEVEL_BASIC );
3676 debug(SQLERRM, MSG_LEVEL_BASIC);
3677
3678 close_cursor( l_select_c );
3679 close_cursor( l_delete_c );
3680
3681 ROLLBACK TO ar_auto_accounting;
3682 RAISE;
3683
3684 END do_autoaccounting_internal;
3685
3686 ----------------------------------------------------------------------------
3687 --
3688 -- PROCEDURE NAME: do_autoaccounting
3689 --
3690 -- DECSRIPTION:
3691 -- Entry point for autoaccounting.
3692 --
3693 -- ARGUMENTS:
3694 -- IN:
3695 -- mode: May be I(nsert), U(pdate), D(elete), or (G)et
3696 -- account_class: REC, UNPAIDREC, FACTOR, REMITTANCE
3697 -- customer_trx_id: NULL if not applicable
3698 -- br_unpaid_ccid: Unpaid ccid for reclassification
3699 -- cust_trx_type_id (G)
3700 -- site_use_id (G)
3701 -- receipt_method_id (G)
3702 -- bank_account_id(G)
3703 --
3704 -- IN/OUT:
3705 -- ccid
3706 -- concat_segments
3707 -- failure_count
3708 --
3709 -- OUT:
3710 --
3711 -- NOTES:
3712 -- If mode is not (G)et, raises the exception
3713 -- arp_auto_accounting_br.no_ccid if autoaccounting could not derive a
3714 -- valid code combination. The public variable g_error_buffer is
3715 -- populated for more information. In (G)et mode, check the value
3716 -- assigned to p_ccid. If it is -1, then no ccid was found.
3717 --
3718 -- Raises the exception NO_DATA_FOUND if no rows were selected for
3719 -- processing.
3720 --
3721 -- Exception raised if Oracle error.
3722 -- App_exception is raised for all other fatal errors and a message
3723 -- is put on the AOL stack. The public variable g_error_buffer is
3724 -- populated for both types of errors.
3725 --
3726 -- HISTORY:
3727 --
3728 --
3729 PROCEDURE do_autoaccounting( p_mode IN VARCHAR2,
3730 p_account_class IN VARCHAR2,
3731 p_customer_trx_id IN NUMBER,
3732 p_receivable_application_id IN NUMBER,
3733 p_br_unpaid_ccid IN NUMBER,
3734 p_cust_trx_type_id IN NUMBER,
3735 p_site_use_id IN NUMBER,
3736 p_receipt_method_id IN NUMBER,
3737 p_bank_account_id IN NUMBER,
3738 p_ccid IN OUT NOCOPY NUMBER,
3739 p_concat_segments IN OUT NOCOPY VARCHAR2,
3740 p_failure_count IN OUT NOCOPY NUMBER )
3741 IS
3742
3743
3744 l_select_rec select_rec_type;
3745 l_null_rec CONSTANT select_rec_type := l_select_rec;
3746
3747 l_ignore INTEGER;
3748 l_boolean BOOLEAN;
3749 l_temp BINARY_INTEGER;
3750 l_account_class VARCHAR2(20);
3751
3752 BEGIN
3753
3754 print_fcn_label( 'arp_auto_accounting_br.do_autoaccounting()+' );
3755
3756 g_error_buffer := NULL;
3757
3758 --
3759 -- Set message level for debugging
3760 --
3761 system_info.msg_level := arp_global.msg_level;
3762
3763 debug( ' mode='||p_mode, MSG_LEVEL_DEBUG );
3764 debug( ' account_class='||p_account_class, MSG_LEVEL_DEBUG );
3765 debug( ' customer_trx_id='||to_char(p_customer_trx_id), MSG_LEVEL_DEBUG );
3766 debug( ' receivable_application_id='||to_char(p_receivable_application_id),
3767 MSG_LEVEL_DEBUG );
3768 debug( ' br_unpaid_ccid='||to_char(p_br_unpaid_ccid), MSG_LEVEL_DEBUG );
3769 debug( ' cust_trx_type_id='||to_char(p_cust_trx_type_id),
3770 MSG_LEVEL_DEBUG );
3771 debug( ' site_use_id='||to_char(p_site_use_id),
3772 MSG_LEVEL_DEBUG );
3773 debug( ' receipt_method_id='||to_char(p_receipt_method_id),
3774 MSG_LEVEL_DEBUG );
3775 debug( ' bank_account_id='||to_char(p_bank_account_id), MSG_LEVEL_DEBUG );
3776 debug( ' msg_level='||to_char(system_info.msg_level), MSG_LEVEL_DEBUG );
3777
3778 --
3779 -- Initialize
3780 --
3781 -- p_failure_count := 0;
3782
3783 l_account_class := p_account_class;
3784
3785 do_autoaccounting_internal(
3786 p_mode,
3787 l_account_class,
3788 p_customer_trx_id,
3789 p_receivable_application_id,
3790 p_br_unpaid_ccid,
3791 p_cust_trx_type_id,
3792 p_site_use_id,
3793 p_receipt_method_id,
3794 p_bank_account_id,
3795 p_ccid,
3796 p_concat_segments,
3797 p_failure_count );
3798
3799 -- Check if failed to get any ccids
3800 --
3801 debug( ' p_failure_count='||to_char(p_failure_count) ,
3802 MSG_LEVEL_DEBUG);
3803
3804 IF( p_failure_count > 0 ) THEN
3805
3806 debug( ' raising no_ccid', MSG_LEVEL_DEBUG );
3807 RAISE no_ccid;
3808
3809 END IF;
3810
3811 print_fcn_label( 'arp_auto_accounting_br.do_autoaccounting()-' );
3812
3813
3814 EXCEPTION
3815 WHEN no_ccid OR NO_DATA_FOUND THEN
3816
3817 IF( p_mode = G ) THEN
3818
3819 NULL; -- Don't raise for Get mode,
3820 -- otherwise the IN/OUT variables
3821 -- ccid, concat_segments and failure_count
3822 -- do not get populated.
3823 ELSE
3824 RAISE;
3825 END IF;
3826
3827 WHEN OTHERS THEN
3828 debug( 'EXCEPTION: arp_auto_accounting_br.do_autoaccounting()',
3829 MSG_LEVEL_BASIC );
3830 debug(SQLERRM, MSG_LEVEL_BASIC);
3831
3832 IF( sqlcode = 1 ) THEN
3833 --
3834 -- User-defined exception
3835 --
3836 FND_MESSAGE.set_name( 'AR', 'GENERIC_MESSAGE' );
3837 FND_MESSAGE.set_token( 'GENERIC_TEXT', g_error_buffer );
3838 APP_EXCEPTION.raise_exception;
3839
3840 ELSE
3841 --
3842 -- Oracle error
3843 --
3844 g_error_buffer := SQLERRM;
3845
3846 RAISE;
3847
3848 END IF;
3849
3850 END do_autoaccounting;
3851
3852 ----------------------------------------------------------------------------
3853 PROCEDURE test_build_sql IS
3854
3855 select_stmt VARCHAR2(32767);
3856 delete_stmt VARCHAR2(32767);
3857 mycursor integer;
3858
3859 BEGIN
3860
3861 print_fcn_label( 'arp_auto_accounting_br.test_build_sql()+' );
3862
3863 -----Test REC account build--------------------------
3864
3865 select_stmt :=
3866 build_select_sql(system_info, profile_info,
3867 REC, 1, NULL, NULL);
3868 debug(select_stmt);
3869
3870 -----Test UNPAIDREC Account Build--------------------------
3871
3872 select_stmt :=
3873 build_select_sql(system_info, profile_info,
3874 UNPAIDREC, 1, NULL, NULL);
3875 debug(select_stmt);
3876
3877 -----Test UNPAIDREC Account Build for receipt application reversal-------
3878
3879 select_stmt :=
3880 build_select_sql(system_info, profile_info,
3881 UNPAIDREC, 1, 2, NULL);
3882 debug(select_stmt);
3883
3884 -----Test UNPAIDREC Account Build for receipt application reversal with UNPAID account-------
3885
3886 select_stmt :=
3887 build_select_sql(system_info, profile_info,
3888 UNPAIDREC, 1, 2, 3333);
3889 debug(select_stmt);
3890
3891 -----Test REMITTANCE Account Build for receipt application reversal with UNPAID account-------
3892
3893 select_stmt :=
3894 build_select_sql(system_info, profile_info,
3895 REMITTANCE, 1, NULL, NULL);
3896 debug(select_stmt);
3897
3898 -----Test FACTOR Account Build for receipt application reversal with UNPAID account-------
3899
3900 select_stmt :=
3901 build_select_sql(system_info, profile_info,
3902 FACTOR, 1, NULL, NULL);
3903 debug(select_stmt);
3904
3905 -----Test FACTOR Account Build for receipt application reversal with UNPAID account-------
3906
3907 delete_stmt :=
3908 build_delete_sql(system_info, profile_info,
3909 FACTOR, 1234);
3910 debug(select_stmt);
3911
3912 print_fcn_label( 'arp_auto_accounting_br.test_build_sql()+' );
3913
3914 EXCEPTION
3915 WHEN OTHERS THEN
3916 debug('EXCEPTION: arp_auto_accounting.test_build_sql()');
3917 debug(SQLERRM);
3918 RAISE;
3919
3920 END test_build_sql;
3921
3922 ----------------------------------------------------------------------------
3923 -- Constructor code
3924 ----------------------------------------------------------------------------
3925 PROCEDURE INIT IS
3926 BEGIN
3927 --enable_debug;
3928
3929 print_fcn_label( 'arp_auto_accounting_br.constructor()+' );
3930
3931 ------------------------------------------------------------------------
3932 -- Load autoaccounting definition into plsql tables
3933 ------------------------------------------------------------------------
3934 load_autoacc_def;
3935 system_info := arp_trx_global.system_info;
3936
3937 ------------------------------------------------------------------------
3938 -- Construct ccid reader sql
3939 ------------------------------------------------------------------------
3940 DECLARE
3941 temp varchar2(2000);
3942
3943 BEGIN
3944
3945 BEGIN
3946
3947 ccid_reader_c := dbms_sql.open_cursor;
3948
3949 temp :=
3950 'SELECT
3951 detail_posting_allowed_flag,
3952 summary_flag
3953 FROM gl_code_combinations
3954 WHERE code_combination_id = :ccid
3955 ';
3956
3957 -- debug
3958 debug( 'printing ccid_reader' );
3959 debug( 'ccid_reader='||temp );
3960
3961 EXCEPTION
3962 WHEN OTHERS THEN
3963 debug('Error constructing ccid reader');
3964 debug( 'ccid_reader='||temp );
3965 RAISE;
3966 END;
3967
3968 --------------------------------------------------------------------
3969 -- parse ccid reader
3970 --------------------------------------------------------------------
3971 BEGIN
3972
3973 debug( 'parsing' );
3974 dbms_sql.parse( ccid_reader_c, temp, dbms_sql.v7);
3975
3976 EXCEPTION
3977 WHEN OTHERS THEN
3978 debug('Error parsing ccid reader');
3979 RAISE;
3980 END;
3981
3982 END;
3983
3984 get_error_message_text;
3985
3986 dump_info;
3987
3988 print_fcn_label( 'arp_auto_accounting_br.constructor()-' );
3989
3990 EXCEPTION
3991 WHEN OTHERS THEN
3992 debug('EXCEPTION: arp_auto_accounting_br.constructor');
3993 debug(SQLERRM);
3994 RAISE;
3995 END INIT;
3996 BEGIN
3997 INIT;
3998
3999 END ARP_AUTO_ACCOUNTING_BR;