[Home] [Help]
PACKAGE BODY: APPS.ARP_AUTO_ACCOUNTING
Source
1 PACKAGE BODY arp_auto_accounting AS
2 /* $Header: ARTEAACB.pls 120.44.12010000.4 2008/11/19 13:24:18 pbapna ship $ */
3
4 ------------------------------------------------------------------------
5 -- Inherited from other packages
6 ------------------------------------------------------------------------
7
8 --
9 -- Linefeed character
10 --
11 PG_DEBUG varchar2(1) := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
12 CRLF CONSTANT VARCHAR2(1) := arp_global.CRLF;
13
14 YES CONSTANT VARCHAR2(1) := arp_global.YES;
15 NO CONSTANT VARCHAR2(1) := arp_global.NO;
16
17 MSG_LEVEL_BASIC CONSTANT BINARY_INTEGER := arp_global.MSG_LEVEL_BASIC;
18 MSG_LEVEL_TIMING CONSTANT BINARY_INTEGER := arp_global.MSG_LEVEL_TIMING;
19 MSG_LEVEL_DEBUG CONSTANT BINARY_INTEGER := arp_global.MSG_LEVEL_DEBUG;
20 MSG_LEVEL_DEBUG2 CONSTANT BINARY_INTEGER := arp_global.MSG_LEVEL_DEBUG2;
21 MSG_LEVEL_DEVELOP CONSTANT BINARY_INTEGER := arp_global.MSG_LEVEL_DEVELOP;
22
23 MAX_CURSOR_CACHE_SIZE CONSTANT BINARY_INTEGER := 20;
24 MAX_SEGMENT_CACHE_SIZE CONSTANT BINARY_INTEGER := 1000;
25 MAX_ARRAY_SIZE CONSTANT BINARY_INTEGER := 1000;
26 STARTING_INDEX CONSTANT BINARY_INTEGER := 1;
27
28 /* Bug 2142306 - Limits for hash and linear cache */
29 MAX_LINEAR_CACHE_SIZE CONSTANT BINARY_INTEGER := 1000;
30 MAX_HASH_CACHE_SIZE CONSTANT BINARY_INTEGER := 4000;
31 HASH_START CONSTANT NUMBER := 16384;
32 HASH_MAX CONSTANT NUMBER := 1000000;
33
34 G_MAX_DATE CONSTANT DATE:= arp_global.G_MAX_DATE;
35 G_MIN_DATE CONSTANT DATE:= to_date('01-01-1952','DD-MM-YYYY');
36 G_SYS_DATE CONSTANT DATE:= TRUNC(SYSDATE);
37 -- This record holds general information used by autoaccounting and
38 -- credit memo module. Passed as argument to most functions/procs.
39 --
40 system_info arp_trx_global.system_info_rec_type :=
41 arp_trx_global.system_info;
42
43 --
44 -- This record holds profile information used by autoaccounting and
45 -- credit memo module. Passed as argument to most functions/procs.
46 --
47 profile_info arp_trx_global.profile_rec_type :=
48 arp_trx_global.profile_info;
49
50 --
51 -- This record holds accounting flexfield information used by
52 -- autoaccounting and the credit memo module. Passed as argument to
53 -- most functions/procs.
54 --
55 flex_info arp_trx_global.acct_flex_info_rec_type :=
56 arp_trx_global.flex_info;
57
58 ------------------------------------------------------------------------
59 -- Private types
60 ------------------------------------------------------------------------
61 TYPE autoacc_rec_type IS RECORD
62 (
63 type ra_account_defaults.type%type,
64 segment ra_account_default_segments.segment%type,
65 table_name ra_account_default_segments.table_name%type,
66 constant ra_account_default_segments.constant%type
67 );
68
69 --
70 -- Autoaccounting definintion cache
71 --
72 REV CONSTANT VARCHAR2(10) := 'REV';
73 REC CONSTANT VARCHAR2(10) := 'REC';
74 FREIGHT CONSTANT VARCHAR2(10) := 'FREIGHT';
75 TAX CONSTANT VARCHAR2(10) := 'TAX';
76 UNBILL CONSTANT VARCHAR2(10) := 'UNBILL';
77 UNEARN CONSTANT VARCHAR2(10) := 'UNEARN';
78 SUSPENSE CONSTANT VARCHAR2(10) := 'SUSPENSE';
79 CHARGES CONSTANT VARCHAR2(10) := 'CHARGES';
80
81 /* Bug 2142306: added variables for linear search if collision occurs
82 during hash checks
83 */
84 tab_size NUMBER := 0;
85 h_tab_size NUMBER := 0;
86
87 --
88 -- Maximum of 30 enabled segments for the accounting flex
89 -- so the gap between offsets is sufficient
90 --
91 rev_offset CONSTANT BINARY_INTEGER := 0;
92 rec_offset CONSTANT BINARY_INTEGER := 50;
93 frt_offset CONSTANT BINARY_INTEGER := 100;
94 tax_offset CONSTANT BINARY_INTEGER := 150;
95 unbill_offset CONSTANT BINARY_INTEGER := 200;
96 unearn_offset CONSTANT BINARY_INTEGER := 250;
97 suspense_offset CONSTANT BINARY_INTEGER := 300;
98 --
99 rev_count BINARY_INTEGER := 0;
100 rec_count BINARY_INTEGER := 0;
101 frt_count BINARY_INTEGER := 0;
102 tax_count BINARY_INTEGER := 0;
103 unbill_count BINARY_INTEGER := 0;
104 unearn_count BINARY_INTEGER := 0;
105 suspense_count BINARY_INTEGER := 0;
106 --
107 TYPE segment_table_type IS
108 TABLE OF ra_account_default_segments.segment%type
109 INDEX BY BINARY_INTEGER;
110 --
111 TYPE table_table_type IS
112 TABLE OF ra_account_default_segments.table_name%type
113 INDEX BY BINARY_INTEGER;
114 --
115 TYPE const_table_type IS
116 TABLE OF ra_account_default_segments.constant%type
117 INDEX BY BINARY_INTEGER;
118 --
119 autoacc_def_segment_t segment_table_type;
120 autoacc_def_table_t table_table_type;
121 autoacc_def_const_t const_table_type;
122
123 --
124 -- trx_type cache
125 --
126 TYPE trx_type_rev_table_type IS
127 TABLE OF BINARY_INTEGER
128 INDEX BY BINARY_INTEGER;
129 trx_type_rev_t trx_type_rev_table_type;
130
131 TYPE trx_type_rec_table_type IS
132 TABLE OF BINARY_INTEGER
133 INDEX BY BINARY_INTEGER;
134 trx_type_rec_t trx_type_rec_table_type;
135
136 TYPE trx_type_frt_table_type IS
137 TABLE OF BINARY_INTEGER
138 INDEX BY BINARY_INTEGER;
139 trx_type_frt_t trx_type_frt_table_type;
140
141 TYPE trx_type_tax_table_type IS
142 TABLE OF BINARY_INTEGER
143 INDEX BY BINARY_INTEGER;
144 trx_type_tax_t trx_type_tax_table_type;
145
146 TYPE trx_type_unbill_table_type IS
147 TABLE OF BINARY_INTEGER
148 INDEX BY BINARY_INTEGER;
149 trx_type_unbill_t trx_type_unbill_table_type;
150
151 TYPE trx_type_unearn_table_type IS
152 TABLE OF BINARY_INTEGER
153 INDEX BY BINARY_INTEGER;
154 trx_type_unearn_t trx_type_unearn_table_type;
155
156 TYPE trx_type_suspense_table_type IS
157 TABLE OF BINARY_INTEGER
158 INDEX BY BINARY_INTEGER;
159 trx_type_suspense_t trx_type_suspense_table_type;
160
161 --
162 -- site_uses cache
163 --
164 TYPE site_use_rev_table_type IS
165 TABLE OF BINARY_INTEGER
166 INDEX BY BINARY_INTEGER;
167 site_use_rev_t site_use_rev_table_type;
168
169 TYPE site_use_rec_table_type IS
170 TABLE OF BINARY_INTEGER
171 INDEX BY BINARY_INTEGER;
172 site_use_rec_t site_use_rec_table_type;
173
174 TYPE site_use_frt_table_type IS
175 TABLE OF BINARY_INTEGER
176 INDEX BY BINARY_INTEGER;
177 site_use_frt_t site_use_frt_table_type;
178
179 TYPE site_use_tax_table_type IS
180 TABLE OF BINARY_INTEGER
181 INDEX BY BINARY_INTEGER;
182 site_use_tax_t site_use_tax_table_type;
183
184 TYPE site_use_unbill_table_type IS
185 TABLE OF BINARY_INTEGER
186 INDEX BY BINARY_INTEGER;
187 site_use_unbill_t site_use_unbill_table_type;
188
189 TYPE site_use_unearn_table_type IS
190 TABLE OF BINARY_INTEGER
191 INDEX BY BINARY_INTEGER;
192 site_use_unearn_t site_use_unearn_table_type;
193
194 TYPE site_use_suspense_table_type IS
195 TABLE OF BINARY_INTEGER
196 INDEX BY BINARY_INTEGER;
197 site_use_suspense_t site_use_suspense_table_type;
198
199 --
200 --
201 -- salesrep cache
202 --
203 TYPE salesrep_rev_table_type IS
204 TABLE OF BINARY_INTEGER
205 INDEX BY BINARY_INTEGER;
206 salesrep_rev_t salesrep_rev_table_type;
207
208 TYPE salesrep_rec_table_type IS
209 TABLE OF BINARY_INTEGER
210 INDEX BY BINARY_INTEGER;
211 salesrep_rec_t salesrep_rec_table_type;
212
213 TYPE salesrep_frt_table_type IS
214 TABLE OF BINARY_INTEGER
215 INDEX BY BINARY_INTEGER;
216 salesrep_frt_t salesrep_frt_table_type;
217
218 --
219 -- inv_item cache
220 --
221 TYPE inv_item_rec_type IS RECORD (
222 inventory_item_id mtl_system_items.inventory_item_id%TYPE,
223 warehouse_id mtl_system_items.organization_id%TYPE ,
224 item_type mtl_system_items.item_type%TYPE,
225 sales_account mtl_system_items.sales_account%TYPE
226 );
227
228 TYPE inv_item_rev_table_type IS
229 TABLE OF inv_item_rec_type
230 INDEX BY VARCHAR2(1000);
231
232 inv_item_rev_t inv_item_rev_table_type;
233
234 g_item_ctr BINARY_INTEGER :=0;
235
236 --
237 -- memo_line cache
238 --
239 TYPE memo_line_rev_table_type IS
240 TABLE OF BINARY_INTEGER
241 INDEX BY BINARY_INTEGER;
242 memo_line_rev_t memo_line_rev_table_type;
243
244 --
245 -- code combination,segment and date caches
246 --
247 TYPE autoacc_cache_seg_type IS
248 TABLE OF varchar2(929)
249 INDEX BY BINARY_INTEGER;
250
251 TYPE autoacc_cache_id_type IS
252 TABLE OF BINARY_INTEGER
253 INDEX BY BINARY_INTEGER;
254
255 TYPE autoacc_cache_date_type IS
256 TABLE OF DATE
257 INDEX BY BINARY_INTEGER;
258
259 TYPE segment_type IS
260 TABLE OF gl_code_combinations.segment1%type
261 INDEX BY BINARY_INTEGER;
262
263 TYPE cursor_attr_tbl_type IS
264 TABLE OF VARCHAR2(100)
265 INDEX BY BINARY_INTEGER;
266
267 TYPE cursor_tbl_type IS
268 TABLE OF BINARY_INTEGER
269 INDEX BY BINARY_INTEGER;
270
271 --
272 -- Misc
273 --
274
275 -- To store segment values for binding
276 --
277 TYPE seg_table_type IS
278 TABLE OF gl_code_combinations.segment1%type
279 INDEX BY binary_integer;
280 --
281 TYPE ccid_rec_type IS RECORD
282 (
283 trx_type_ccid_rev BINARY_INTEGER := -1,
284 trx_type_ccid_rec BINARY_INTEGER := -1,
285 trx_type_ccid_frt BINARY_INTEGER := -1,
286 trx_type_ccid_tax BINARY_INTEGER := -1,
287 trx_type_ccid_unbill BINARY_INTEGER := -1,
288 trx_type_ccid_unearn BINARY_INTEGER := -1,
289 trx_type_ccid_suspense BINARY_INTEGER := -1,
290 salesrep_ccid_rev BINARY_INTEGER := -1,
291 salesrep_ccid_rec BINARY_INTEGER := -1,
292 salesrep_ccid_frt BINARY_INTEGER := -1,
293 lineitem_ccid_rev BINARY_INTEGER := -1,
294 tax_ccid_tax BINARY_INTEGER := -1,
295 agreecat_ccid_rev BINARY_INTEGER := -1,
296 interim_tax_ccid BINARY_INTEGER := -1,
297 site_use_ccid_rev BINARY_INTEGER := -1,
298 site_use_ccid_rec BINARY_INTEGER := -1,
299 site_use_ccid_frt BINARY_INTEGER := -1,
300 site_use_ccid_tax BINARY_INTEGER := -1,
301 site_use_ccid_unbill BINARY_INTEGER := -1,
302 site_use_ccid_unearn BINARY_INTEGER := -1,
303 site_use_ccid_suspense BINARY_INTEGER := -1
304 );
305
306 --
307 -- To hold values fetched from the Select stmt
308 --
309 TYPE select_rec_type IS RECORD
310 (
311 customer_trx_id NUMBER,
312 customer_trx_line_id NUMBER,
313 cust_trx_line_salesrep_id NUMBER,
314 line_amount NUMBER,
315 accounted_line_amount NUMBER,
316 percent NUMBER,
317 amount NUMBER,
318 acctd_amount NUMBER,
319 account_class VARCHAR2(20),
320 account_set_flag VARCHAR2(1),
321 cust_trx_type_id BINARY_INTEGER,
322 allow_not_open_flag VARCHAR2(1),
323 concatenated_segments VARCHAR2(240),
324 code_combination_id BINARY_INTEGER,
325 gl_date VARCHAR2(12), -- Julian format
326 original_gl_date VARCHAR2(12), -- Julian format
327 ussgl_trx_code VARCHAR2(30),
328 ussgl_trx_code_context VARCHAR2(30),
329 salesrep_id NUMBER,
330 inventory_item_id NUMBER,
331 memo_line_id NUMBER,
332 default_tax_ccid BINARY_INTEGER,
333 interim_tax_ccid BINARY_INTEGER,
334 int_concatenated_segments VARCHAR2(240),
335 int_code_combination_id BINARY_INTEGER,
336 site_use_id NUMBER,
337 warehouse_id NUMBER,
338 link_to_cust_trx_line_id NUMBER -- 1651593
339 );
340
341 --
342 -- To hold values fetched from the Select stmt
343 --
344 TYPE select_rec_tab IS RECORD
345 (
346 customer_trx_id DBMS_SQL.NUMBER_TABLE,
347 customer_trx_line_id DBMS_SQL.NUMBER_TABLE,
348 cust_trx_line_salesrep_id DBMS_SQL.NUMBER_TABLE,
349 line_amount DBMS_SQL.NUMBER_TABLE,
350 accounted_line_amount DBMS_SQL.NUMBER_TABLE,
351 percent DBMS_SQL.NUMBER_TABLE,
352 amount DBMS_SQL.NUMBER_TABLE,
353 acctd_amount DBMS_SQL.NUMBER_TABLE,
354 account_class DBMS_SQL.VARCHAR2_TABLE,
355 account_set_flag DBMS_SQL.VARCHAR2_TABLE,
356 cust_trx_type_id DBMS_SQL.NUMBER_TABLE,
357 allow_not_open_flag DBMS_SQL.VARCHAR2_TABLE,
358 concatenated_segments DBMS_SQL.VARCHAR2_TABLE,
359 code_combination_id DBMS_SQL.NUMBER_TABLE,
360 gl_date DBMS_SQL.VARCHAR2_TABLE, -- Julian format
361 original_gl_date DBMS_SQL.VARCHAR2_TABLE, -- Julian format
362 ussgl_trx_code DBMS_SQL.VARCHAR2_TABLE,
363 ussgl_trx_code_context DBMS_SQL.VARCHAR2_TABLE,
364 salesrep_id DBMS_SQL.NUMBER_TABLE,
365 inventory_item_id DBMS_SQL.NUMBER_TABLE,
366 memo_line_id DBMS_SQL.NUMBER_TABLE,
367 default_tax_ccid DBMS_SQL.NUMBER_TABLE,
368 interim_tax_ccid DBMS_SQL.NUMBER_TABLE,
369 int_concatenated_segments DBMS_SQL.VARCHAR2_TABLE,
370 int_code_combination_id DBMS_SQL.NUMBER_TABLE,
371 site_use_id DBMS_SQL.NUMBER_TABLE,
372 warehouse_id DBMS_SQL.NUMBER_TABLE,
373 link_to_cust_trx_line_id DBMS_SQL.NUMBER_TABLE -- 1651593
374 );
375
376 g_select_rec_tab select_rec_tab;
377 /* Bug-2178723 : Cached the values of detail_posting_allowed_flag and summary_flag
378 in pl/sql table to avoid the high execution count */
379
380 TYPE code_comb_rec_type IS RECORD
381 ( detail_posting_flag gl_code_combinations.detail_posting_allowed_flag%TYPE,
382 summary_flag gl_code_combinations.summary_flag%TYPE);
383
384 TYPE t_ar_code_comb_table IS TABLE OF code_comb_rec_type
385 INDEX BY BINARY_INTEGER;
386
387 pg_ar_code_comb_rec t_ar_code_comb_table;
388
389 -- set invalid segvalue to null
390 --
391 INVALID_SEGMENT CONSTANT VARCHAR2(20) := '';
392
393 --
394 -- Cursor handles
395 --
396
397 --
398 -- CCID Validation date
399 --
400 validation_date DATE := TRUNC(SYSDATE);
401
402
403 -- User-defined exceptions
404 --
405 invalid_account_class EXCEPTION;
406 invalid_table_name EXCEPTION; -- in autoacc def
407 item_and_memo_both_not_null EXCEPTION;
408 error_defaulting_gl_date EXCEPTION;
409
410
411 --
412 -- Translated error messages
413 --
414 MSG_COMPLETE_REV_ACCOUNT varchar2(2000);
415 MSG_COMPLETE_REC_ACCOUNT varchar2(2000);
416 MSG_COMPLETE_FRT_ACCOUNT varchar2(2000);
417 MSG_COMPLETE_TAX_ACCOUNT varchar2(2000);
418 MSG_COMPLETE_CHARGES_ACCOUNT varchar2(2000);
419 MSG_COMPLETE_OFFSET_ACCOUNT varchar2(2000);
420 MSG_COMPLETE_INT_TAX_ACCOUNT varchar2(2000);
421
422 MSG_FLEX_POSTING_NOT_ALLOWED varchar2(2000);
423 MSG_FLEX_NO_PARENT_ALLOWED varchar2(2000);
424
425
426 I CONSTANT VARCHAR2(1) := 'I';
427 U CONSTANT VARCHAR2(1) := 'U';
428 D CONSTANT VARCHAR2(1) := 'D';
429 G CONSTANT VARCHAR2(1) := 'G';
430
431 -- code combination segment, ID, Start and End Date caches
432 -- bug 2142306: revised cache and linear tables for autoaccounting
433
434 autoacc_hash_id_cache autoacc_cache_id_type;
435 autoacc_hash_seg_cache autoacc_cache_seg_type;
436 autoacc_hash_st_date_cache autoacc_cache_date_type;
437 autoacc_hash_end_date_cache autoacc_cache_date_type;
438
439 autoacc_lin_id_cache autoacc_cache_id_type;
440 autoacc_lin_seg_cache autoacc_cache_seg_type;
441 autoacc_lin_st_date_cache autoacc_cache_date_type;
442 autoacc_lin_end_date_cache autoacc_cache_date_type;
443
444 cursor_attr_cache cursor_attr_tbl_type;
445 cursor_cache cursor_tbl_type;
446
447 segment1_cache segment_type;
448 segment2_cache segment_type;
449 segment3_cache segment_type;
450 segment4_cache segment_type;
451 segment5_cache segment_type;
452 segment6_cache segment_type;
453 segment7_cache segment_type;
454 segment8_cache segment_type;
455 segment9_cache segment_type;
456 segment10_cache segment_type;
457 segment11_cache segment_type;
458 segment12_cache segment_type;
459 segment13_cache segment_type;
460 segment14_cache segment_type;
461 segment15_cache segment_type;
462 segment16_cache segment_type;
463 segment17_cache segment_type;
464 segment18_cache segment_type;
465 segment19_cache segment_type;
466 segment20_cache segment_type;
467 segment21_cache segment_type;
468 segment22_cache segment_type;
469 segment23_cache segment_type;
470 segment24_cache segment_type;
471 segment25_cache segment_type;
472 segment26_cache segment_type;
473 segment27_cache segment_type;
474 segment28_cache segment_type;
475 segment29_cache segment_type;
476 segment30_cache segment_type;
477
478 /* Bug 2560036 - Collectibility results table and flags */
479 /* Table for recording collectibility results */
480 t_collect ar_revenue_management_pvt.number_table;
481 /* flag that indicates if collectibility is enabled */
482 g_test_collectibility boolean;
483 /* flag that indicates if collectibility has already
484 been called in current session */
485 g_called_collectibility boolean := FALSE;
486
487 ----------------------------------------------------------------------------
488 -- Covers
489 ----------------------------------------------------------------------------
490 PROCEDURE debug( p_line IN VARCHAR2 ) IS
491 BEGIN
492 IF PG_DEBUG IN ('C','Y')
493 THEN
494 arp_util.debug( p_line );
495 END IF;
496 END;
497
498 PROCEDURE debug( p_str VARCHAR2, p_print_level BINARY_INTEGER ) IS
499 BEGIN
500 IF PG_DEBUG IN ('C','Y')
501 THEN
502 arp_util.debug( p_str, p_print_level );
503 END IF;
504 END;
505
506 PROCEDURE enable_debug IS
507 BEGIN
508 arp_util.enable_debug;
509 END;
510
511 PROCEDURE disable_debug IS
512 BEGIN
513 arp_util.disable_debug;
514 END;
515
516 PROCEDURE print_fcn_label( p_label VARCHAR2 ) IS
517 BEGIN
518 IF PG_DEBUG IN ('C', 'Y')
519 THEN
520 arp_util.print_fcn_label( p_label );
521 END IF;
522 END;
523
524 PROCEDURE print_fcn_label2( p_label VARCHAR2 ) IS
525 BEGIN
526 IF PG_DEBUG IN ('C', 'Y')
527 THEN
528 arp_util.print_fcn_label2( p_label );
529 END IF;
530 END;
531
532 PROCEDURE close_cursor( p_cursor_handle IN OUT NOCOPY INTEGER ) IS
533 BEGIN
534 arp_util.close_cursor( p_cursor_handle );
535 END;
536
537
538
539 ----------------------------------------------------------------------------
540 -- Procedures and Functions
541 ----------------------------------------------------------------------------
542
543
544 PROCEDURE insert_into_error_table(
545 p_interface_line_id binary_integer,
546 p_message_text varchar2,
547 p_invalid_value varchar2 ) IS
548
549 BEGIN
550
551 INSERT INTO ra_interface_errors
552 (interface_line_id,
553 message_text,
554 invalid_value,
555 org_id)
556 VALUES
557 (p_interface_line_id,
558 p_message_text,
559 p_invalid_value,
560 ARP_STANDARD.sysparm.org_id);
561
562 END insert_into_error_table;
563
564 PROCEDURE put_message_on_stack(
565 p_interface_line_id binary_integer,
566 p_message_text varchar2,
567 p_invalid_value varchar2,
568 p_request_id binary_integer ) IS
569
570 BEGIN
571 IF ( p_request_id IS NOT NULL ) THEN
572 IF ( p_request_id > 0)
573 THEN
574 IF p_interface_line_id < 0 THEN
575
576 -- Since, we cannot derive the receivables account for a invoice,
577 -- we insert error for each invoice line.
578
579 FOR c01_rec IN (select interface_line_id from ra_interface_lines_gt
580 WHERE customer_trx_id = -1 * p_interface_line_id
581 AND request_id = p_request_id ) LOOP
582 insert_into_error_table(
583 c01_rec.interface_line_id,
584 p_message_text,
585 p_invalid_value );
586
587 END LOOP;
588 ELSE
589 insert_into_error_table(
590 p_interface_line_id,
591 p_message_text,
592 p_invalid_value );
593 END IF;
594 -- the following code has been added by bsarkar to log the
595 -- error for Invoice API. Request_id will be always -ve
596 -- for invoice api and instead of logging into the standard
597 -- error table it will log into global error table for
598 -- invoice api.
599
600 ELSIF (p_request_id < 0 )
601 THEN
602 -- for Invoice API request id will be always -ve
603 -- get the details for which line autoaccounting failed.
604 FOR invRec IN ( select trx_header_id,trx_line_id
605 from ar_trx_lines_gt
606 where request_id = p_request_id
607 and customer_trx_line_id = p_interface_line_id
608 UNION
609 select trx_header_id, -99
610 from ar_trx_header_gt
611 where request_id = p_request_id
612 and customer_trx_id = -1 * p_interface_line_id ) loop
613 insert into ar_trx_errors_gt
614 (trx_header_id,
615 trx_line_id,
616 error_message,
617 invalid_value) values
618 ( invRec.trx_header_id,
619 decode(invRec.trx_line_id,-99,null,invRec.trx_line_id),
620 p_message_text,
621 p_invalid_value);
622 end loop;
623 END IF;
624 ELSE
625
626 FND_MESSAGE.set_name( 'AR', 'GENERIC_MESSAGE' );
627 FND_MESSAGE.set_token( 'GENERIC_TEXT', p_message_text );
628
629 END IF;
630 END put_message_on_stack;
631
632
633 ----------------------------------------------------------------------------
634 PROCEDURE get_error_message_text is
635
636 l_application_id NUMBER := 222;
637 l_msg_name VARCHAR2(100);
638
639 BEGIN
640
641 print_fcn_label( 'arp_auto_accounting.get_error_message_text()+' );
642
643 l_msg_name := '1360';
644 fnd_message.set_name('AR', l_msg_name);
645 MSG_COMPLETE_REV_ACCOUNT := fnd_message.get;
646
647 ----
648 l_msg_name := '1350';
649 fnd_message.set_name('AR', l_msg_name);
650 MSG_COMPLETE_REC_ACCOUNT := fnd_message.get;
651
652 ----
653 l_msg_name := '1370';
654 fnd_message.set_name('AR', l_msg_name);
655 MSG_COMPLETE_FRT_ACCOUNT := fnd_message.get;
656
657 ----
658 l_msg_name := 'I-120';
659 fnd_message.set_name('AR', l_msg_name);
660 MSG_COMPLETE_TAX_ACCOUNT := fnd_message.get;
661
662 ----
663 l_msg_name := '1365';
664 fnd_message.set_name('AR', l_msg_name);
665 MSG_COMPLETE_CHARGES_ACCOUNT := fnd_message.get;
666
667 ----
668 l_msg_name := 'AR_AUTOACC_COMPLETE_OFFSET';
669 fnd_message.set_name('AR', l_msg_name);
670 MSG_COMPLETE_OFFSET_ACCOUNT := fnd_message.get;
671
672 ----
673 l_msg_name := 'RA_POSTING_NOT_ALLOWED';
674 fnd_message.set_name('AR', l_msg_name);
675 MSG_FLEX_POSTING_NOT_ALLOWED := fnd_message.get;
676
677 ----
678 l_msg_name := 'FLEX-No Parent';
679 fnd_message.set_name('AR', l_msg_name);
680 MSG_FLEX_NO_PARENT_ALLOWED := fnd_message.get;
681
682 ----
683 l_msg_name := 'AR_COMPLETE_INT_TAX_ACCOUNT';
684 fnd_message.set_name('AR', l_msg_name);
685 MSG_COMPLETE_INT_TAX_ACCOUNT := fnd_message.get;
686
687 -- print
688 debug( ' MSG_COMPLETE_REV_ACCOUNT='||MSG_COMPLETE_REV_ACCOUNT,
689 MSG_LEVEL_DEBUG );
690 debug( ' MSG_COMPLETE_REC_ACCOUNT='||MSG_COMPLETE_REC_ACCOUNT,
691 MSG_LEVEL_DEBUG );
692 debug( ' MSG_COMPLETE_FRT_ACCOUNT='||MSG_COMPLETE_FRT_ACCOUNT,
693 MSG_LEVEL_DEBUG );
694 debug( ' MSG_COMPLETE_TAX_ACCOUNT='||MSG_COMPLETE_TAX_ACCOUNT,
695 MSG_LEVEL_DEBUG );
696 debug( ' MSG_COMPLETE_INT_TAX_ACCOUNT='||MSG_COMPLETE_INT_TAX_ACCOUNT,
697 MSG_LEVEL_DEBUG );
698 debug( ' MSG_COMPLETE_OFFSET_ACCOUNT='||MSG_COMPLETE_OFFSET_ACCOUNT,
699 MSG_LEVEL_DEBUG );
700
701 debug( ' MSG_FLEX_POSTING_NOT_ALLOWED='||MSG_FLEX_POSTING_NOT_ALLOWED,
702 MSG_LEVEL_DEBUG );
703 debug( ' MSG_FLEX_NO_PARENT_ALLOWED='||MSG_FLEX_NO_PARENT_ALLOWED,
704 MSG_LEVEL_DEBUG );
705
706 print_fcn_label( 'arp_auto_accounting.get_error_message_text()-' );
707
708 EXCEPTION
709 WHEN OTHERS THEN
710 debug('EXCEPTION: arp_auto_accounting.get_error_message_text()');
711 RAISE;
712 END get_error_message_text;
713
714 ----------------------------------------------------------------------------
715 PROCEDURE expand_account_class( p_account_class IN OUT NOCOPY VARCHAR2 ) IS
716 BEGIN
717
718 --
719 -- Adjust account_class to proper string
720 --
721 IF( substrb(p_account_class, 1, 3) = 'REV' ) THEN
722 p_account_class := REV;
723 ELSIF( substrb(p_account_class, 1, 3) = 'REC' ) THEN
724 p_account_class := REC;
725 ELSIF( substrb(p_account_class, 1, 3) = 'FRE' ) THEN
726 p_account_class := FREIGHT;
727 ELSIF( substrb(p_account_class, 1, 3) = 'TAX' ) THEN
728 p_account_class := TAX;
729 ELSIF( substrb(p_account_class, 1, 3) = 'UNB' ) THEN
730 p_account_class := UNBILL;
731 ELSIF( substrb(p_account_class, 1, 3) = 'UNE' ) THEN
732 p_account_class := UNEARN;
733 ELSIF( substrb(p_account_class, 1, 3) = 'SUS' ) THEN
734 p_account_class := SUSPENSE;
735 ELSIF( substrb(p_account_class, 1, 3) = 'CHA' ) THEN
736 p_account_class := CHARGES ;
737 END IF;
738
739 END expand_account_class;
740
741 ----------------------------------------------------------------------------
742 PROCEDURE dump_info IS
743 BEGIN
744
745 -- sys info
746 debug( ' coa_id='||to_char(system_info.chart_of_accounts_id),
747 MSG_LEVEL_DEBUG);
748 debug( ' curr='||system_info.base_currency, MSG_LEVEL_DEBUG);
749 debug( ' prec='||to_char(system_info.base_precision), MSG_LEVEL_DEBUG);
750 debug( ' mau='||to_char(system_info.base_min_acc_unit), MSG_LEVEL_DEBUG);
751
752 IF( system_info.rev_based_on_salesrep ) THEN
753 debug( ' rev_based_on_salesrep=TRUE', MSG_LEVEL_DEBUG );
754 ELSE
755 debug( ' rev_based_on_salesrep=FALSE', MSG_LEVEL_DEBUG );
756 END IF;
757
758 IF( system_info.tax_based_on_salesrep ) THEN
759 debug( ' tax_based_on_salesrep=TRUE', MSG_LEVEL_DEBUG );
760 ELSE
761 debug( ' tax_based_on_salesrep=FALSE', MSG_LEVEL_DEBUG );
762 END IF;
763
764 IF( system_info.unbill_based_on_salesrep ) THEN
765 debug( ' unbill_based_on_salesrep=TRUE', MSG_LEVEL_DEBUG );
766 ELSE
767 debug( ' unbill_based_on_salesrep=FALSE', MSG_LEVEL_DEBUG );
768 END IF;
769
770 IF( system_info.unearn_based_on_salesrep ) THEN
771 debug( ' unearn_based_on_salesrep=TRUE', MSG_LEVEL_DEBUG );
772 ELSE
773 debug( ' unearn_based_on_salesrep=FALSE', MSG_LEVEL_DEBUG );
774 END IF;
775
776 IF( system_info.suspense_based_on_salesrep ) THEN
777 debug( ' suspense_based_on_salesrep=TRUE', MSG_LEVEL_DEBUG );
778 ELSE
779 debug( ' suspense_based_on_salesrep=FALSE', MSG_LEVEL_DEBUG );
780 END IF;
781
782
783 -- profile info
784 debug( ' login_id='||profile_info.conc_login_id, MSG_LEVEL_DEBUG );
785 debug( ' program_id='||profile_info.conc_program_id, MSG_LEVEL_DEBUG );
786 debug( ' user_id='||profile_info.user_id, MSG_LEVEL_DEBUG );
787 debug( ' use_inv_acct='||profile_info.use_inv_acct_for_cm_flag,
788 MSG_LEVEL_DEBUG );
789 debug( ' org_id='||oe_profile.value('SO_ORGANIZATION_ID',arp_global.sysparam.org_id),
790 MSG_LEVEL_DEBUG );
791
792 -- flex info
793 debug( ' nsegs='||to_char(flex_info.number_segments), MSG_LEVEL_DEBUG);
794 debug( ' delim='||flex_info.delim, MSG_LEVEL_DEBUG);
795
796 EXCEPTION
797 WHEN OTHERS THEN
798 debug('EXCEPTION: arp_auto_accounting.dump_info()', MSG_LEVEL_BASIC);
799 debug(SQLERRM, MSG_LEVEL_BASIC);
800 RAISE;
801 END dump_info;
802
803 ----------------------------------------------------------------------------
804 --
805 -- PROCEDURE NAME: dump_ccid_record
806 --
807 -- DECSRIPTION:
808 -- Prints contents of the ccid record
809 --
810 -- ARGUMENTS:
811 -- IN:
812 -- ccid_record
813 --
814 -- IN/OUT:
815 --
816 -- OUT:
817 --
818 -- NOTES:
819 --
820 -- HISTORY:
821 --
822 PROCEDURE dump_ccid_record( p_ccid_record IN ccid_rec_type ) IS
823 BEGIN
824 print_fcn_label( 'arp_auto_accounting.dump_ccid_record()+' );
825
826 debug( ' Dumping CCID record:', MSG_LEVEL_DEBUG );
827
828 debug( ' trx_type_ccid_rev=' ||
829 to_char(p_ccid_record.trx_type_ccid_rev ), MSG_LEVEL_DEBUG );
830 debug( ' trx_type_ccid_rec=' ||
831 to_char(p_ccid_record.trx_type_ccid_rec ), MSG_LEVEL_DEBUG );
832 debug( ' trx_type_ccid_frt=' ||
833 to_char(p_ccid_record.trx_type_ccid_frt ), MSG_LEVEL_DEBUG );
834 debug( ' trx_type_ccid_tax=' ||
835 to_char(p_ccid_record.trx_type_ccid_tax ), MSG_LEVEL_DEBUG );
836 debug( ' trx_type_ccid_unbill=' ||
837 to_char(p_ccid_record.trx_type_ccid_unbill ), MSG_LEVEL_DEBUG );
838 debug( ' trx_type_ccid_unearn=' ||
839 to_char(p_ccid_record.trx_type_ccid_unearn ), MSG_LEVEL_DEBUG );
840 debug( ' trx_type_ccid_suspense=' ||
841 to_char(p_ccid_record.trx_type_ccid_suspense ), MSG_LEVEL_DEBUG );
842 debug( ' site_use_ccid_rev=' ||
843 to_char(p_ccid_record.site_use_ccid_rev ), MSG_LEVEL_DEBUG );
844 debug( ' site_use_ccid_rec=' ||
845 to_char(p_ccid_record.site_use_ccid_rec ), MSG_LEVEL_DEBUG );
846 debug( ' site_use_ccid_frt=' ||
847 to_char(p_ccid_record.site_use_ccid_frt ), MSG_LEVEL_DEBUG );
848 debug( ' site_use_ccid_tax=' ||
849 to_char(p_ccid_record.site_use_ccid_tax ), MSG_LEVEL_DEBUG );
850 debug( ' site_use_ccid_unbill=' ||
851 to_char(p_ccid_record.site_use_ccid_unbill ), MSG_LEVEL_DEBUG );
852 debug( ' site_use_ccid_unearn=' ||
853 to_char(p_ccid_record.site_use_ccid_unearn ), MSG_LEVEL_DEBUG );
854 debug( ' site_use_ccid_suspense=' ||
855 to_char(p_ccid_record.site_use_ccid_suspense ), MSG_LEVEL_DEBUG );
856 debug( ' salesrep_ccid_rev=' ||
857 to_char(p_ccid_record.salesrep_ccid_rev ), MSG_LEVEL_DEBUG );
858 debug( ' salesrep_ccid_rec=' ||
859 to_char(p_ccid_record.salesrep_ccid_rec ), MSG_LEVEL_DEBUG );
860 debug( ' salesrep_ccid_frt=' ||
861 to_char(p_ccid_record.salesrep_ccid_frt ), MSG_LEVEL_DEBUG );
862 debug( ' lineitem_ccid_rev=' ||
863 to_char(p_ccid_record.lineitem_ccid_rev ), MSG_LEVEL_DEBUG );
864 debug( ' tax_ccid_tax=' ||
865 to_char(p_ccid_record.tax_ccid_tax ), MSG_LEVEL_DEBUG );
866 debug( ' agreecat_ccid_rev=' ||
867 to_char(p_ccid_record.agreecat_ccid_rev ), MSG_LEVEL_DEBUG );
868 debug( ' interim_tax_ccid=' ||
869 to_char(p_ccid_record.interim_tax_ccid ), MSG_LEVEL_DEBUG );
870
871 print_fcn_label( 'arp_auto_accounting.dump_ccid_record()-' );
872
873 END dump_ccid_record;
874
875 ----------------------------------------------------------------------------
876 --
877 -- PROCEDURE NAME: load_autoacc_def
878 --
879 -- DECSRIPTION:
880 -- Retrieves the following autoaccounting information for each
881 -- account class:
882 -- - segment column name
883 -- - table name
884 -- - constant
885 -- and stores them in plsql tables for future use by autoaccounting.
886 -- Called on package initialization.
887 --
888 -- ARGUMENTS:
889 -- IN:
890 --
891 -- IN/OUT:
892 --
893 -- OUT:
894 --
895 -- NOTES:
896 --
897 -- HISTORY:
898 --
899 PROCEDURE load_autoacc_def IS
900 l_rev_index BINARY_INTEGER := rev_offset;
901 l_rec_index BINARY_INTEGER := rec_offset;
902 l_frt_index BINARY_INTEGER := frt_offset;
903 l_tax_index BINARY_INTEGER := tax_offset;
904 l_unbill_index BINARY_INTEGER := unbill_offset;
905 l_unearn_index BINARY_INTEGER := unearn_offset;
906 l_suspense_index BINARY_INTEGER := suspense_offset;
907 --begin anuj
908 /* Multi-Org Access Control Changes for SSA;Begin;anukumar;11/01/2002*/
909 i_cnt BINARY_INTEGER :=0;
910 /* Multi-Org Access Control Changes for SSA;Begin;anukumar;11/01/2002*/
911 --end anuj
912
913 CURSOR autoacc IS
914 SELECT
915 ad.type type,
916 ads.segment segment,
917 upper(ads.table_name) table_name,
918 ads.constant constant
919 FROM
920 ra_account_default_segments ads,
921 ra_account_defaults ad
922 WHERE ad.gl_default_id = ads.gl_default_id
923 AND ad.org_id = NVL(arp_global.sysparam.org_id, ad.org_id)
924 AND ad.type in
925 (
926 'REV', 'REC', 'FREIGHT', 'TAX', 'UNBILL', 'UNEARN', 'SUSPENSE'
927 )
928 ORDER BY
929 type,
930 segment_num;
931
932
933 PROCEDURE load( p_table_index IN OUT NOCOPY BINARY_INTEGER,
934 p_cnt IN OUT NOCOPY BINARY_INTEGER,
935 p_autoacc_rec IN autoacc%rowtype) IS
936 BEGIN
937 autoacc_def_segment_t(p_table_index) := p_autoacc_rec.segment;
938 autoacc_def_table_t(p_table_index) := p_autoacc_rec.table_name;
939 autoacc_def_const_t(p_table_index):= p_autoacc_rec.constant;
940 p_table_index := p_table_index + 1;
941 p_cnt := p_cnt + 1;
942 END;
943
944 BEGIN
945 print_fcn_label( 'arp_auto_accounting.load_autoacc_def()+' );
946 --begin anuj
947 /* Multi-Org Access Control Changes for SSA;Begin;anukumar;11/01/2002*/
948 rev_count := 0;
949 rec_count := 0;
950 frt_count := 0;
951 tax_count := 0;
952 unbill_count := 0;
953 unearn_count := 0;
954 suspense_count := 0;
955
956 i_cnt := 0;
957 while i_cnt <=300 LOOP
958
959 If (autoacc_def_segment_t.exists(i_cnt)) then
960 autoacc_def_segment_t.delete;
961 End if;
962 If (autoacc_def_table_t.exists(i_cnt) ) then
963 autoacc_def_table_t.delete;
964 End if;
965 If (autoacc_def_const_t.exists(i_cnt) ) then
966 autoacc_def_const_t.delete;
967 End if;
968 i_cnt := 50+i_cnt;
969 End Loop;
970 /* Multi-Org Access Control Changes for SSA;Begin;anukumar;11/01/2002*/
971 --end anuj
972
973 FOR autoacc_rec IN autoacc LOOP
974 IF( autoacc_rec.type in (REV, CHARGES) ) then
975 load(l_rev_index, rev_count, autoacc_rec);
976 ELSIF( autoacc_rec.type = REC ) then
977 load(l_rec_index, rec_count, autoacc_rec);
978 ELSIF( autoacc_rec.type = FREIGHT ) then
979 load(l_frt_index, frt_count, autoacc_rec);
980 ELSIF( autoacc_rec.type = TAX ) then
981 load(l_tax_index, tax_count, autoacc_rec);
982 ELSIF( autoacc_rec.type = UNBILL ) then
983 load(l_unbill_index, unbill_count, autoacc_rec);
984 ELSIF( autoacc_rec.type = UNEARN ) then
985 load(l_unearn_index, unearn_count, autoacc_rec);
986 ELSIF( autoacc_rec.type = SUSPENSE ) then
987 load(l_suspense_index, suspense_count, autoacc_rec);
988 END IF;
989 END LOOP;
990
991 print_fcn_label( 'arp_auto_accounting.load_autoacc_def()-' );
992
993 EXCEPTION
994 WHEN OTHERS THEN
995 debug('EXCEPTION: arp_auto_accounting.load_autoacc_def()',
996 MSG_LEVEL_BASIC);
997 debug(SQLERRM, MSG_LEVEL_BASIC);
998 RAISE;
999 END load_autoacc_def ;
1000
1001 ----------------------------------------------------------------------------
1002 --
1003 -- FUNCTION NAME: query_autoacc_def
1004 --
1005 -- DECSRIPTION:
1006 -- Determines whether any of a given account class segments are based
1007 -- on a given table.
1008 --
1009 -- ARGUMENTS:
1010 -- IN:
1011 -- account_class:
1012 -- 'REV', 'REC', 'FRE', 'TAX', 'UNB', 'UNE', 'SUS'
1013 -- 'CHA'
1014 -- table_name
1015 --
1016 -- IN/OUT:
1017 --
1018 -- OUT:
1019 --
1020 -- RETURNS:
1021 -- TRUE if account class is based on specified table
1022 -- FALSE otherwise
1023 --
1024 -- NOTES:
1025 -- if account_class = 'ALL', check all seven account classes in cache
1026 -- else check particular account class in cache
1027 --
1028 --
1029 -- HISTORY:
1030 --
1031 FUNCTION query_autoacc_def( p_account_class IN VARCHAR2,
1032 p_table_name IN VARCHAR2 )
1033 RETURN BOOLEAN IS
1034
1035 retval BOOLEAN;
1036 l_account_class VARCHAR2(20);
1037
1038 FUNCTION search_table( p_offset IN BINARY_INTEGER,
1039 p_cnt IN BINARY_INTEGER )
1040 RETURN BOOLEAN is
1041 BEGIN
1042 FOR i IN 0..p_cnt-1 LOOP
1043 IF( autoacc_def_table_t( p_offset + i ) = p_table_name ) THEN
1044 return TRUE;
1045 END IF;
1046 END LOOP;
1047
1048 RETURN false;
1049 END;
1050
1051 BEGIN
1052 print_fcn_label( 'arp_auto_accounting.query_autoacc_def()+' );
1053
1054 g_error_buffer := NULL;
1055
1056 --
1057 -- Adjust account_class to proper string
1058 --
1059 l_account_class := p_account_class;
1060 expand_account_class( l_account_class );
1061
1062 IF l_account_class = 'ALL' THEN
1063 retval := query_autoacc_def(REV, p_table_name) OR
1064 query_autoacc_def(REC, p_table_name) OR
1065 query_autoacc_def(FREIGHT, p_table_name) OR
1066 query_autoacc_def(TAX, p_table_name) OR
1067 query_autoacc_def(UNBILL, p_table_name) OR
1068 query_autoacc_def(UNEARN, p_table_name) OR
1069 query_autoacc_def(SUSPENSE, p_table_name);
1070 ELSE
1071 IF l_account_class in (REV, CHARGES) THEN
1072 retval := search_table( rev_offset, rev_count);
1073 ELSIF l_account_class = REC THEN
1074 retval := search_table( rec_offset, rec_count);
1075 ELSIF l_account_class = FREIGHT THEN
1076 retval := search_table( frt_offset, frt_count);
1077 ELSIF l_account_class = TAX THEN
1078 retval := search_table( tax_offset, tax_count);
1079 ELSIF l_account_class = UNBILL THEN
1080 retval := search_table( unbill_offset, unbill_count);
1081 ELSIF l_account_class = UNEARN THEN
1082 retval := search_table( unearn_offset, unearn_count);
1083 ELSIF l_account_class = SUSPENSE THEN
1084 retval := search_table( suspense_offset, suspense_count);
1085 ELSE
1086 g_error_buffer := 'Invalid account class';
1087 debug( 'EXCEPTION: '||g_error_buffer, MSG_LEVEL_BASIC );
1088 RAISE invalid_account_class;
1089 END IF;
1090 END IF;
1091
1092 print_fcn_label( 'arp_auto_accounting.query_autoacc_def()-' );
1093
1094 RETURN retval;
1095
1096
1097 EXCEPTION
1098 WHEN OTHERS THEN
1099 debug('EXCEPTION: arp_auto_accounting.query_autoacc_def('
1100 || p_account_class || ', '
1101 || p_table_name ||')', MSG_LEVEL_BASIC);
1102 debug(SQLERRM, MSG_LEVEL_BASIC);
1103 RAISE;
1104 END query_autoacc_def;
1105
1106
1107 ----------------------------------------------------------------------------
1108 --
1109 -- PROCEDURE NAME: get_trx_type_ccids
1110 --
1111 -- DECSRIPTION:
1112 -- Retrieves default ccids from the table ra_cust_trx_types
1113 -- for a given trx type.
1114 --
1115 -- ARGUMENTS:
1116 -- IN:
1117 -- trx_type_id
1118 --
1119 -- IN/OUT:
1120 -- ccid_rev
1121 -- ccid_rec
1122 -- ccid_frt
1123 -- ccid_tax
1124 -- ccid_unbill
1125 -- ccid_unearn
1126 -- ccid_suspense
1127 --
1128 -- OUT:
1129 --
1130 -- NOTES:
1131 --
1132 -- HISTORY:
1133 --
1134 PROCEDURE get_trx_type_ccids( p_trx_type_id IN BINARY_INTEGER,
1135 p_ccid_rev IN OUT NOCOPY BINARY_INTEGER,
1136 p_ccid_rec IN OUT NOCOPY BINARY_INTEGER,
1137 p_ccid_frt IN OUT NOCOPY BINARY_INTEGER,
1138 p_ccid_tax IN OUT NOCOPY BINARY_INTEGER,
1139 p_ccid_unbill IN OUT NOCOPY BINARY_INTEGER,
1140 p_ccid_unearn IN OUT NOCOPY BINARY_INTEGER,
1141 p_ccid_suspense IN OUT NOCOPY BINARY_INTEGER) IS
1142 BEGIN
1143 print_fcn_label2( 'arp_auto_accounting.get_trx_type_ccids()+' );
1144
1145 --
1146 -- initialize
1147 --
1148 p_ccid_rev := -1;
1149 p_ccid_rec := -1;
1150 p_ccid_frt := -1;
1151 p_ccid_tax := -1;
1152 p_ccid_unbill := -1;
1153 p_ccid_unearn := -1;
1154 p_ccid_suspense := -1;
1155
1156
1157 BEGIN
1158 -- see if available in cache
1159 --
1160 p_ccid_rev := trx_type_rev_t( p_trx_type_id );
1161 p_ccid_rec := trx_type_rec_t( p_trx_type_id );
1162 p_ccid_frt := trx_type_frt_t( p_trx_type_id );
1163 p_ccid_tax := trx_type_tax_t( p_trx_type_id );
1164 p_ccid_unbill := trx_type_unbill_t( p_trx_type_id );
1165 p_ccid_unearn := trx_type_unearn_t( p_trx_type_id );
1166 p_ccid_suspense := trx_type_suspense_t( p_trx_type_id );
1167
1168 debug( ' cache hit: trx_type_id='||to_char(p_trx_type_id),
1169 MSG_LEVEL_DEBUG );
1170
1171 EXCEPTION
1172 WHEN NO_DATA_FOUND THEN
1173 -- else, get it from the database
1174 --
1175 debug( ' cache miss: trx_type_id='||to_char(p_trx_type_id),
1176 MSG_LEVEL_DEBUG );
1177
1178 SELECT
1179 nvl(gl_id_rev,-1),
1180 nvl(gl_id_rec,-1),
1181 nvl(gl_id_freight,-1),
1182 nvl(gl_id_tax,-1),
1183 nvl(gl_id_unbilled,-1),
1184 nvl(gl_id_unearned,-1),
1185 nvl(gl_id_clearing,-1)
1186 INTO
1187 p_ccid_rev,
1188 p_ccid_rec,
1189 p_ccid_frt,
1190 p_ccid_tax,
1191 p_ccid_unbill,
1192 p_ccid_unearn,
1193 p_ccid_suspense
1194 FROM ra_cust_trx_types
1195 WHERE cust_trx_type_id = p_trx_type_id;
1196
1197 -- update cache
1198 trx_type_rev_t( p_trx_type_id ) := p_ccid_rev;
1199 trx_type_rec_t( p_trx_type_id ) := p_ccid_rec;
1200 trx_type_frt_t( p_trx_type_id ) := p_ccid_frt;
1201 trx_type_tax_t( p_trx_type_id ) := p_ccid_tax;
1202 trx_type_unbill_t( p_trx_type_id ) := p_ccid_unbill;
1203 trx_type_unearn_t( p_trx_type_id ) := p_ccid_unearn;
1204 trx_type_suspense_t( p_trx_type_id ) := p_ccid_suspense;
1205 END;
1206
1207
1208 print_fcn_label2( 'arp_auto_accounting.get_trx_type_ccids()-' );
1209
1210 EXCEPTION
1211 WHEN NO_DATA_FOUND THEN
1212 debug('arp_auto_accounting.get_trx_type_ccids(): no data found',
1213 MSG_LEVEL_DEBUG);
1214 WHEN OTHERS THEN
1215 debug('EXCEPTION: arp_auto_accounting.get_trx_type_ccids('
1216 || to_char(p_trx_type_id) ||')', MSG_LEVEL_BASIC);
1217 debug(SQLERRM, MSG_LEVEL_BASIC);
1218 RAISE;
1219 END get_trx_type_ccids;
1220
1221
1222 ----------------------------------------------------------------------------
1223 --
1224 -- PROCEDURE NAME: get_site_use_ccids
1225 --
1226 -- DECSRIPTION:
1227 -- Retrieves default ccids from the table hz_cust_site_uses
1228 -- for a given bill to site use id.
1229 --
1230 -- ARGUMENTS:
1231 -- IN:
1232 -- site_use_id
1233 --
1234 -- IN/OUT:
1235 -- ccid_rev
1236 -- ccid_rec
1237 -- ccid_frt
1238 -- ccid_tax
1239 -- ccid_unbill
1240 -- ccid_unearn
1241 -- ccid_suspense
1242 --
1243 -- OUT:
1244 --
1245 -- NOTES:
1246 --
1247 -- HISTORY:
1248 --
1249 PROCEDURE get_site_use_ccids( p_site_use_id IN BINARY_INTEGER,
1250 p_ccid_rev IN OUT NOCOPY BINARY_INTEGER,
1251 p_ccid_rec IN OUT NOCOPY BINARY_INTEGER,
1252 p_ccid_frt IN OUT NOCOPY BINARY_INTEGER,
1253 p_ccid_tax IN OUT NOCOPY BINARY_INTEGER,
1254 p_ccid_unbill IN OUT NOCOPY BINARY_INTEGER,
1255 p_ccid_unearn IN OUT NOCOPY BINARY_INTEGER,
1256 p_ccid_suspense IN OUT NOCOPY BINARY_INTEGER) IS
1257 BEGIN
1258 print_fcn_label2( 'arp_auto_accounting.get_site_use_ccids()+' );
1259
1260 --
1261 -- initialize
1262 --
1263 p_ccid_rev := -1;
1264 p_ccid_rec := -1;
1265 p_ccid_frt := -1;
1266 p_ccid_tax := -1;
1267 p_ccid_unbill := -1;
1268 p_ccid_unearn := -1;
1269 p_ccid_suspense := -1;
1270
1271
1272 BEGIN
1273 -- see if available in cache
1274 --
1275 p_ccid_rev := site_use_rev_t( p_site_use_id );
1276 p_ccid_rec := site_use_rec_t( p_site_use_id );
1277 p_ccid_frt := site_use_frt_t( p_site_use_id );
1278 p_ccid_tax := site_use_tax_t( p_site_use_id );
1279 p_ccid_unbill := site_use_unbill_t( p_site_use_id );
1280 p_ccid_unearn := site_use_unearn_t( p_site_use_id );
1281 p_ccid_suspense := site_use_suspense_t( p_site_use_id );
1282
1283 debug( ' cache hit: site_use_id='||to_char(p_site_use_id),
1284 MSG_LEVEL_DEBUG );
1285
1286 EXCEPTION
1287 WHEN NO_DATA_FOUND THEN
1288 -- else, get it from the database
1289 --
1290 debug( ' cache miss: site_use_id='||to_char(p_site_use_id),
1291 MSG_LEVEL_DEBUG );
1292
1293 SELECT
1294 nvl(gl_id_rev,-1),
1295 nvl(gl_id_rec,-1),
1296 nvl(gl_id_freight,-1),
1297 nvl(gl_id_tax,-1),
1298 nvl(gl_id_unbilled,-1),
1299 nvl(gl_id_unearned,-1),
1300 nvl(gl_id_clearing,-1)
1301 INTO
1302 p_ccid_rev,
1303 p_ccid_rec,
1304 p_ccid_frt,
1305 p_ccid_tax,
1306 p_ccid_unbill,
1307 p_ccid_unearn,
1308 p_ccid_suspense
1309 FROM hz_cust_site_uses
1310 WHERE site_use_id = p_site_use_id;
1311
1312 -- update cache
1313 site_use_rev_t( p_site_use_id ) := p_ccid_rev;
1314 site_use_rec_t( p_site_use_id ) := p_ccid_rec;
1315 site_use_frt_t( p_site_use_id ) := p_ccid_frt;
1316 site_use_tax_t( p_site_use_id ) := p_ccid_tax;
1317 site_use_unbill_t( p_site_use_id ) := p_ccid_unbill;
1318 site_use_unearn_t( p_site_use_id ) := p_ccid_unearn;
1319 site_use_suspense_t( p_site_use_id ) := p_ccid_suspense;
1320 END;
1321
1322
1323 print_fcn_label2( 'arp_auto_accounting.get_site_use_ccids()-' );
1324
1325 EXCEPTION
1326 WHEN NO_DATA_FOUND THEN
1327 debug('arp_auto_accounting.get_site_use_ccids(): no data found',
1328 MSG_LEVEL_DEBUG);
1329 WHEN OTHERS THEN
1330 debug('EXCEPTION: arp_auto_accounting.get_site_use_ccids('
1331 || to_char(p_site_use_id) ||')', MSG_LEVEL_BASIC);
1332 debug(SQLERRM, MSG_LEVEL_BASIC);
1333 RAISE;
1334 END get_site_use_ccids;
1335
1336 ----------------------------------------------------------------------------
1337 --
1338 -- PROCEDURE NAME: get_salesrep_ccids
1339 --
1340 -- DECSRIPTION:
1341 -- Retrieves default ccids from the table ra_salesreps
1342 -- for a given salesrep_id.
1343 --
1344 -- ARGUMENTS:
1345 -- IN:
1346 -- salesrep_id
1347 --
1348 -- IN/OUT:
1349 -- ccid_rev
1350 -- ccid_rec
1351 -- ccid_frt
1352 --
1353 -- OUT:
1354 --
1355 -- NOTES:
1356 --
1357 -- HISTORY:
1358 --
1359 --
1360 PROCEDURE get_salesrep_ccids( p_salesrep_id IN BINARY_INTEGER,
1361 p_ccid_rev IN OUT NOCOPY BINARY_INTEGER,
1362 p_ccid_rec IN OUT NOCOPY BINARY_INTEGER,
1363 p_ccid_frt IN OUT NOCOPY BINARY_INTEGER ) IS
1364 BEGIN
1365
1366 print_fcn_label2( 'arp_auto_accounting.get_salesrep_ccids()+' );
1367
1368 p_ccid_rev := -1;
1369 p_ccid_rec := -1;
1370 p_ccid_frt := -1;
1371
1372 BEGIN
1373 -- see if available in cache
1374 --
1375 p_ccid_rev := salesrep_rev_t( p_salesrep_id );
1376 p_ccid_rec := salesrep_rec_t( p_salesrep_id );
1377 p_ccid_frt := salesrep_frt_t( p_salesrep_id );
1378
1379 debug( ' cache hit: salesrep_id='||to_char(p_salesrep_id),
1380 MSG_LEVEL_DEBUG );
1381
1382 EXCEPTION
1383 WHEN NO_DATA_FOUND THEN
1384 -- else, get it from the database
1385 --
1386 debug( ' cache miss: salesrep_id='||to_char(p_salesrep_id),
1387 MSG_LEVEL_DEBUG );
1388
1389 SELECT
1390 nvl(gl_id_rev,-1),
1391 nvl(gl_id_rec,-1),
1392 nvl(gl_id_freight,-1)
1393 INTO p_ccid_rev, p_ccid_rec, p_ccid_frt
1394 FROM ra_salesreps
1395 WHERE salesrep_id = p_salesrep_id;
1396
1397 -- update cache
1398 salesrep_rev_t( p_salesrep_id ) := p_ccid_rev;
1399 salesrep_rec_t( p_salesrep_id ) := p_ccid_rec;
1400 salesrep_frt_t( p_salesrep_id ) := p_ccid_frt;
1401
1402 END;
1403
1404 print_fcn_label2( 'arp_auto_accounting.get_salesrep_ccids()-' );
1405
1406 EXCEPTION
1407 WHEN NO_DATA_FOUND THEN
1408 debug('arp_auto_accounting.get_salesrep_ccids(): no data found',
1409 MSG_LEVEL_DEBUG);
1410 WHEN OTHERS THEN
1411 debug('EXCEPTION: arp_auto_accounting.get_salesrep_ccids('
1412 || to_char(p_salesrep_id) ||')', MSG_LEVEL_BASIC);
1413 debug(SQLERRM, MSG_LEVEL_BASIC);
1414 RAISE;
1415 END get_salesrep_ccids;
1416
1417 ----------------------------------------------------------------------------
1418 --
1419 -- PROCEDURE NAME: get_inv_item_ccids
1420 --
1421 -- DECSRIPTION:
1422 -- Retrieves default ccids from the table mtl_system_items
1423 -- for a given item_id.
1424 --
1425 -- ARGUMENTS:
1426 -- IN:
1427 -- profile_info
1428 -- item_id
1429 --
1430 -- IN/OUT:
1431 -- ccid_rev
1432 -- inv_item_type
1433 --
1434 -- OUT:
1435 --
1436 -- NOTES:
1437 --
1438 -- HISTORY:
1439 --
1440 --
1441 PROCEDURE get_inv_item_ccids( p_profile_info IN
1442 arp_trx_global.profile_rec_type,
1443 p_inv_item_id IN BINARY_INTEGER,
1444 p_warehouse_id IN BINARY_INTEGER,
1445 p_ccid_rev IN OUT NOCOPY BINARY_INTEGER,
1446 p_inv_item_type IN OUT NOCOPY
1447 mtl_system_items.item_type%TYPE ) IS
1448 l_ctr BINARY_INTEGER;
1449 l_hit BOOLEAN := FALSE;
1450 t_warehouse_id BINARY_INTEGER;
1451
1452 BEGIN
1453
1454 print_fcn_label2( 'arp_auto_accounting.get_inv_item_ccids()+' );
1455
1456 p_ccid_rev := -1;
1457 p_inv_item_type := NULL;
1458 t_warehouse_id := nvl(p_warehouse_id,
1459 to_number(oe_profile.value('SO_ORGANIZATION_ID',arp_global.sysparam.org_id)));
1460 BEGIN
1461 --
1462 -- see if available in cache
1463 --
1464
1465
1466 IF ((inv_item_rev_t(p_inv_item_id || ':' || t_warehouse_id).inventory_item_id = p_inv_item_id)
1467 AND (inv_item_rev_t(p_inv_item_id || ':' || t_warehouse_id).warehouse_id
1468 = t_warehouse_id))
1469 THEN
1470
1471 l_hit := TRUE;
1472 p_inv_item_type := inv_item_rev_t(p_inv_item_id || ':' || t_warehouse_id).item_type;
1473 p_ccid_rev := inv_item_rev_t(p_inv_item_id || ':' || t_warehouse_id).sales_account;
1474
1475 debug( ' cache hit: Item Id='||to_char(p_inv_item_id),
1476 MSG_LEVEL_DEBUG );
1477 debug( ' cache hit: Warehouse_id='||
1478 t_warehouse_id,
1479 MSG_LEVEL_DEBUG );
1480 debug('Index is: ' || p_inv_item_id || ':' || t_warehouse_id);
1481 debug( ' cache hit: Item Type='|| p_inv_item_type,
1482 MSG_LEVEL_DEBUG );
1483 debug( ' cache hit: revenue account='||to_char(p_ccid_rev),
1484 MSG_LEVEL_DEBUG );
1485
1486
1487 END IF; --end if inventory_item_id:warehouse_id key matches
1488
1489
1490 --
1491 --Raise explicitly exception as item warehouse id combination did not exist in
1492 --cache, hence get from the database
1493 --
1494
1495 IF NOT l_hit THEN
1496 RAISE NO_DATA_FOUND;
1497 END IF;
1498
1499 EXCEPTION
1500 WHEN NO_DATA_FOUND THEN
1501 -- else, get it from the database
1502 --
1503 debug( ' cache miss: inv_item_id='||to_char(p_inv_item_id),
1504 MSG_LEVEL_DEBUG );
1505 debug( ' cache miss: warehouse_id='||to_char(p_warehouse_id),
1506 MSG_LEVEL_DEBUG );
1507
1508 SELECT nvl(sales_account, -1), nvl(item_type, '~')
1509 INTO p_ccid_rev,
1510 p_inv_item_type
1511 FROM mtl_system_items
1512 WHERE organization_id
1513 = t_warehouse_id
1514 AND inventory_item_id = p_inv_item_id;
1515
1516 -- update cache
1517 debug( 'Inserting into the cache: ');
1518 debug('Index is: ' || p_inv_item_id || ':' || t_warehouse_id);
1519 inv_item_rev_t(p_inv_item_id || ':' || t_warehouse_id).inventory_item_id := p_inv_item_id;
1520
1521 inv_item_rev_t(p_inv_item_id || ':' || t_warehouse_id).warehouse_id
1522 := t_warehouse_id;
1523 inv_item_rev_t(p_inv_item_id || ':' || t_warehouse_id).item_type := p_inv_item_type;
1524 --debug('Inventory Item Type is: ' || p_inv_item_type);
1525 inv_item_rev_t(p_inv_item_id || ':' || t_warehouse_id).sales_account := p_ccid_rev;
1526 --debug('Sales Account: ' || p_ccid_rev);
1527
1528 END;
1529
1530 print_fcn_label2( 'arp_auto_accounting.get_inv_item_ccids()-' );
1531
1532 EXCEPTION
1533 WHEN NO_DATA_FOUND THEN
1534 debug('arp_auto_accounting.get_inv_item_ccids(): no data found',
1535 MSG_LEVEL_DEBUG);
1536 WHEN OTHERS THEN
1537 debug('EXCEPTION: arp_auto_accounting.get_inv_item_ccids('
1538 || to_char(p_inv_item_id) ||')', MSG_LEVEL_BASIC);
1539 debug(SQLERRM, MSG_LEVEL_BASIC);
1540 RAISE;
1541 END get_inv_item_ccids;
1542
1543 ----------------------------------------------------------------------------
1544 --
1545 -- PROCEDURE NAME: get_memo_line_ccids
1546 --
1547 -- DECSRIPTION:
1548 -- Retrieves default ccids from the table ar_memo_lines
1549 -- for a given memo_line_id.
1550 --
1551 -- ARGUMENTS:
1552 -- IN:
1553 -- memo_line_id
1554 --
1555 -- IN/OUT:
1556 -- ccid_rev
1557 --
1558 -- OUT:
1559 --
1560 -- NOTES:
1561 --
1562 -- HISTORY:
1563 --
1564 --
1565 PROCEDURE get_memo_line_ccids( p_memo_line_id IN BINARY_INTEGER,
1566 p_ccid_rev IN OUT NOCOPY BINARY_INTEGER ) IS
1567 BEGIN
1568
1569 print_fcn_label2( 'arp_auto_accounting.get_memo_line_ccids()+' );
1570
1571 p_ccid_rev := -1;
1572
1573 BEGIN
1574 -- see if available in cache
1575 --
1576 p_ccid_rev := memo_line_rev_t( p_memo_line_id );
1577
1578 debug( ' cache hit: memo_line_id='||to_char(p_memo_line_id),
1579 MSG_LEVEL_DEBUG );
1580
1581 EXCEPTION
1582 WHEN NO_DATA_FOUND THEN
1583 -- else, get it from the database
1584 --
1585 debug( ' cache miss: memo_line_id='||to_char(p_memo_line_id),
1586 MSG_LEVEL_DEBUG );
1587
1588 SELECT nvl(gl_id_rev,-1)
1589 INTO p_ccid_rev
1590 FROM ar_memo_lines
1591 WHERE memo_line_id = p_memo_line_id;
1592
1593 -- update cache
1594 memo_line_rev_t( p_memo_line_id ) := p_ccid_rev;
1595
1596 END;
1597
1598 print_fcn_label2( 'arp_auto_accounting.get_memo_line_ccids()-' );
1599
1600 EXCEPTION
1601 WHEN NO_DATA_FOUND THEN
1602 debug('arp_auto_accounting.get_memo_line_ccids(): no data found',
1603 MSG_LEVEL_DEBUG);
1604 WHEN OTHERS THEN
1605 debug('EXCEPTION: arp_auto_accounting.get_memo_line_ccids('
1606 || to_char(p_memo_line_id) ||')', MSG_LEVEL_BASIC);
1607 debug(SQLERRM, MSG_LEVEL_BASIC);
1608 RAISE;
1609 END get_memo_line_ccids;
1610
1611 ----------------------------------------------------------------------------
1612 --
1613 -- PROCEDURE NAME: get_agreecat_ccids
1614 --
1615 -- DECSRIPTION:
1616 -- Retrieves default ccids for agreement/category if autoaccounting for
1617 -- revenue is based on agreement/category
1618 --
1619 -- ARGUMENTS:
1620 -- IN:
1621 -- profile_info
1622 -- line_id
1623 --
1624 -- IN/OUT:
1625 --
1626 -- OUT:
1627 -- ccid_rev
1628 --
1629 -- NOTES:
1630 --
1631 -- HISTORY:
1632 --
1633 --
1634 PROCEDURE get_agreecat_ccids( p_profile_info IN
1635 arp_trx_global.profile_rec_type,
1636 p_line_id IN BINARY_INTEGER,
1637 p_warehouse_id IN BINARY_INTEGER, --Bug#1639334
1638 p_ccid_rev OUT NOCOPY BINARY_INTEGER ) IS
1639 BEGIN
1640
1641 print_fcn_label2( 'arp_auto_accounting.get_agreecat_ccids()+' );
1642
1643 p_ccid_rev := -1;
1644
1645 SELECT nvl(c.code_combination_id,-1)
1646 INTO p_ccid_rev
1647 FROM
1648 ra_customer_trx t,
1649 ra_customer_trx_lines l,
1650 mtl_item_categories i,
1651 so_agreements a,
1652 ra_account_combinations c
1653 WHERE t.customer_trx_id = l.customer_trx_id
1654 AND l.customer_trx_line_id = p_line_id
1655 AND t.agreement_id = a.agreement_id(+)
1656 AND l.inventory_item_id = i.inventory_item_id(+)
1657 AND i.organization_id(+)
1658 = nvl(p_warehouse_id,
1659 to_number(oe_profile.value('SO_ORGANIZATION_ID',arp_global.sysparam.org_id))) --Bug#1639334
1660 AND i.category_set_id(+) = 1
1661 AND to_char(nvl(i.category_id, -1)) = c.value1
1662 AND nvl(a.agreement_type_code, -1) = nvl(c.value2 , -1);
1663
1664 print_fcn_label2( 'arp_auto_accounting.get_agreecat_ccids()-' );
1665
1666 EXCEPTION
1667 WHEN NO_DATA_FOUND THEN
1668 debug('arp_auto_accounting.get_agreecat_ccids(): no data found',
1669 MSG_LEVEL_DEBUG);
1670 WHEN OTHERS THEN
1671 debug('EXCEPTION: arp_auto_accounting.get_agreecat_ccids('
1672 || to_char(p_line_id) ||')', MSG_LEVEL_BASIC);
1673 debug(SQLERRM, MSG_LEVEL_BASIC);
1674 RAISE;
1675 END get_agreecat_ccids;
1676
1677 /* ------------------------------------------------------------------------ */
1678 /* Finds combination_id for given segment values. */
1679 /* If validation date is NULL checks all cross-validation rules. */
1680 /* Returns TRUE if combination valid, or FALSE and sets error message */
1681 /* on server using FND_MESSAGE if invalid. */
1682 /* ------------------------------------------------------------------------ */
1683 FUNCTION get_combination_id(application_short_name IN VARCHAR2,
1684 key_flex_code IN VARCHAR2,
1685 structure_number IN NUMBER,
1686 validation_date IN DATE,
1687 concat_segments IN VARCHAR2,
1688 combination_id OUT NOCOPY NUMBER)
1689 RETURN BOOLEAN IS
1690 r_value BOOLEAN := FALSE;
1691 BEGIN
1692
1693 -- Initialize messages, debugging, and number of sql strings
1694 --
1695 print_fcn_label( 'arp_auto_accounting.get_combination_id()+' );
1696
1697 r_value := FND_FLEX_KEYVAL.validate_segs('CREATE_COMBINATION',
1698 application_short_name, key_flex_code, structure_number,
1699 concat_segments, 'V',
1700 validation_date);
1701 if( r_value ) then
1702 combination_id := FND_FLEX_KEYVAL.combination_id;
1703 print_fcn_label( 'arp_auto_accounting.get_combination_id()-' );
1704 return(r_value);
1705 end if;
1706
1707 return(r_value);
1708
1709 EXCEPTION
1710 WHEN OTHERS THEN
1711 FND_MESSAGE.set_name( 'AR', 'GENERIC_MESSAGE' );
1712 FND_MESSAGE.set_token( 'GENERIC_TEXT', 'get_combination_id() exception: '||SQLERRM );
1713 return(FALSE);
1714
1715 END get_combination_id;
1716
1717 /* ------------------------------------------------------------------------ */
1718 /* Overloaded version of above for user with individual segments. */
1719 /* ------------------------------------------------------------------------ */
1720
1721 FUNCTION get_combination_id(application_short_name IN VARCHAR2,
1722 key_flex_code IN VARCHAR2,
1723 structure_number IN NUMBER,
1724 validation_date IN DATE,
1725 n_segments IN NUMBER,
1726 segments IN FND_FLEX_EXT.SegmentArray,
1727 combination_id OUT NOCOPY NUMBER)
1728 RETURN BOOLEAN IS
1729 sepchar VARCHAR2(1);
1730 catsegs VARCHAR2(2000);
1731
1732 BEGIN
1733 print_fcn_label( 'arp_auto_accounting.get_combination_id1()+' );
1734
1735 -- Concatenate the input segments, then send them to the other function.
1736 --
1737 sepchar := fnd_flex_ext.get_delimiter(application_short_name, key_flex_code,
1738 structure_number);
1739 if(sepchar is not null) then
1740 print_fcn_label( 'arp_auto_accounting.get_combination_id1()-' );
1741 return(get_combination_id(application_short_name, key_flex_code,
1742 structure_number, validation_date,
1743 FND_FLEX_EXT.concatenate_segments(n_segments, segments, sepchar),
1744 combination_id));
1745 end if;
1746 return(FALSE);
1747
1748 EXCEPTION
1749 WHEN OTHERS THEN
1750 FND_MESSAGE.set_name( 'AR', 'GENERIC_MESSAGE' );
1751 FND_MESSAGE.set_token( 'GENERIC_TEXT', 'get_combination_id() exception: '||SQLERRM );
1752 return(FALSE);
1753
1754 END get_combination_id;
1755
1756 ----------------------------------------------------------------------------
1757 PROCEDURE define_columns( p_select_c IN INTEGER,
1758 p_select_rec IN select_rec_type) IS
1759 BEGIN
1760 print_fcn_label2( 'arp_auto_accounting.define_columns()+' );
1761
1762 dbms_sql.define_column( p_select_c, 1, p_select_rec.customer_trx_id );
1763 dbms_sql.define_column( p_select_c, 2, p_select_rec.customer_trx_line_id );
1764 dbms_sql.define_column( p_select_c, 3,
1765 p_select_rec.cust_trx_line_salesrep_id );
1766 dbms_sql.define_column( p_select_c, 4, p_select_rec.line_amount );
1767 dbms_sql.define_column( p_select_c, 5,
1768 p_select_rec.accounted_line_amount );
1769 dbms_sql.define_column( p_select_c, 6, p_select_rec.percent );
1770 dbms_sql.define_column( p_select_c, 7, p_select_rec.amount );
1771 dbms_sql.define_column( p_select_c, 8, p_select_rec.acctd_amount );
1772 dbms_sql.define_column( p_select_c, 9, p_select_rec.account_class, 20 );
1773 dbms_sql.define_column( p_select_c, 10, p_select_rec.account_set_flag, 1 );
1774 dbms_sql.define_column( p_select_c, 11, p_select_rec.cust_trx_type_id );
1775 dbms_sql.define_column( p_select_c, 12,
1776 p_select_rec.allow_not_open_flag, 1 );
1777 dbms_sql.define_column( p_select_c, 13,
1778 p_select_rec.concatenated_segments, 240 );
1779 dbms_sql.define_column( p_select_c, 14, p_select_rec.code_combination_id );
1780 dbms_sql.define_column( p_select_c, 15, p_select_rec.gl_date, 12 );
1781 dbms_sql.define_column( p_select_c, 16,
1782 p_select_rec.original_gl_date, 12 );
1783 dbms_sql.define_column( p_select_c, 17, p_select_rec.ussgl_trx_code, 30 );
1784 dbms_sql.define_column( p_select_c, 18,
1785 p_select_rec.ussgl_trx_code_context, 30 );
1786 dbms_sql.define_column( p_select_c, 19, p_select_rec.salesrep_id );
1787 dbms_sql.define_column( p_select_c, 20, p_select_rec.inventory_item_id );
1788 dbms_sql.define_column( p_select_c, 21, p_select_rec.memo_line_id );
1789 dbms_sql.define_column( p_select_c, 22, p_select_rec.default_tax_ccid );
1790 dbms_sql.define_column( p_select_c, 23, p_select_rec.interim_tax_ccid );
1791 dbms_sql.define_column( p_select_c, 24, p_select_rec.site_use_id);
1792 dbms_sql.define_column( p_select_c, 25, p_select_rec.warehouse_id);
1793
1794 print_fcn_label2( 'arp_auto_accounting.define_columns()-' );
1795 EXCEPTION
1796 WHEN OTHERS THEN
1797 debug('EXCEPTION: arp_auto_accounting.define_columns()',
1798 MSG_LEVEL_BASIC);
1799 debug(SQLERRM, MSG_LEVEL_BASIC);
1800 RAISE;
1801 END define_columns;
1802 ----------------------------------------------------------------------------
1803
1804 PROCEDURE define_arrays( p_select_c IN INTEGER,
1805 p_select_tab IN select_rec_tab) IS
1806 BEGIN
1807 print_fcn_label2( 'arp_auto_accounting.define_arrays()+' );
1808
1809 dbms_sql.define_array( p_select_c, 1, p_select_tab.customer_trx_id, MAX_ARRAY_SIZE, STARTING_INDEX );
1810 dbms_sql.define_array( p_select_c, 2, p_select_tab.customer_trx_line_id, MAX_ARRAY_SIZE, STARTING_INDEX );
1811 dbms_sql.define_array( p_select_c, 3,
1812 p_select_tab.cust_trx_line_salesrep_id, MAX_ARRAY_SIZE, STARTING_INDEX );
1813 dbms_sql.define_array( p_select_c, 4, p_select_tab.line_amount, MAX_ARRAY_SIZE, STARTING_INDEX );
1814 dbms_sql.define_array( p_select_c, 5,
1815 p_select_tab.accounted_line_amount, MAX_ARRAY_SIZE, STARTING_INDEX );
1816 dbms_sql.define_array( p_select_c, 6, p_select_tab.percent, MAX_ARRAY_SIZE, STARTING_INDEX );
1817 dbms_sql.define_array( p_select_c, 7, p_select_tab.amount, MAX_ARRAY_SIZE, STARTING_INDEX );
1818 dbms_sql.define_array( p_select_c, 8, p_select_tab.acctd_amount, MAX_ARRAY_SIZE, STARTING_INDEX );
1819 dbms_sql.define_array( p_select_c, 9, p_select_tab.account_class, MAX_ARRAY_SIZE, STARTING_INDEX );
1820 dbms_sql.define_array( p_select_c, 10, p_select_tab.account_set_flag, MAX_ARRAY_SIZE, STARTING_INDEX );
1821 dbms_sql.define_array( p_select_c, 11, p_select_tab.cust_trx_type_id, MAX_ARRAY_SIZE, STARTING_INDEX );
1822 dbms_sql.define_array( p_select_c, 12,
1823 p_select_tab.allow_not_open_flag, MAX_ARRAY_SIZE, STARTING_INDEX);
1824 dbms_sql.define_array( p_select_c, 13,
1825 p_select_tab.concatenated_segments, MAX_ARRAY_SIZE, STARTING_INDEX );
1826 dbms_sql.define_array( p_select_c, 14, p_select_tab.code_combination_id, MAX_ARRAY_SIZE, STARTING_INDEX );
1827 dbms_sql.define_array( p_select_c, 15, p_select_tab.gl_date, MAX_ARRAY_SIZE, STARTING_INDEX );
1828 dbms_sql.define_array( p_select_c, 16,
1829 p_select_tab.original_gl_date, MAX_ARRAY_SIZE, STARTING_INDEX );
1830 dbms_sql.define_array( p_select_c, 17, p_select_tab.ussgl_trx_code, MAX_ARRAY_SIZE, STARTING_INDEX );
1831 dbms_sql.define_array( p_select_c, 18,
1832 p_select_tab.ussgl_trx_code_context, MAX_ARRAY_SIZE, STARTING_INDEX );
1833 dbms_sql.define_array( p_select_c, 19, p_select_tab.salesrep_id, MAX_ARRAY_SIZE, STARTING_INDEX );
1834 dbms_sql.define_array( p_select_c, 20, p_select_tab.inventory_item_id, MAX_ARRAY_SIZE, STARTING_INDEX );
1835 dbms_sql.define_array( p_select_c, 21, p_select_tab.memo_line_id, MAX_ARRAY_SIZE, STARTING_INDEX );
1836 dbms_sql.define_array( p_select_c, 22, p_select_tab.default_tax_ccid, MAX_ARRAY_SIZE, STARTING_INDEX );
1837 dbms_sql.define_array( p_select_c, 23, p_select_tab.interim_tax_ccid, MAX_ARRAY_SIZE, STARTING_INDEX );
1838 dbms_sql.define_array( p_select_c, 24, p_select_tab.site_use_id, MAX_ARRAY_SIZE, STARTING_INDEX);
1839 dbms_sql.define_array( p_select_c, 25, p_select_tab.warehouse_id, MAX_ARRAY_SIZE, STARTING_INDEX);
1840 -- 1651593
1841 dbms_sql.define_array( p_select_c, 26, p_select_tab.link_to_cust_trx_line_id, MAX_ARRAY_SIZE, STARTING_INDEX );
1842
1843 print_fcn_label2( 'arp_auto_accounting.define_arrays()-' );
1844 EXCEPTION
1845 WHEN OTHERS THEN
1846 debug('EXCEPTION: arp_auto_accounting.define_arrays()',
1847 MSG_LEVEL_BASIC);
1848 debug(SQLERRM, MSG_LEVEL_BASIC);
1849 RAISE;
1850 END define_arrays;
1851
1852
1853 ----------------------------------------------------------------------------
1854 --
1855 -- FUNCTION NAME: build_select_sql
1856 --
1857 -- DECSRIPTION:
1858 --
1859 -- ARGUMENTS:
1860 -- IN:
1861 -- system_info
1862 -- profile_info
1863 -- account_class
1864 -- customer_trx_id
1865 -- customer_trx_line_id
1866 -- cust_trx_line_salesrep_id
1867 -- request_id
1868 -- gl_date
1869 -- original_gl_date
1870 -- total_trx_amount
1871 -- code_combination_id
1872 -- force_account_set_no
1873 -- cust_trx_type_id
1874 -- primary_salesrep_id
1875 -- inventory_item_id
1876 -- memo_line_id
1877 --
1878 -- IN/OUT:
1879 --
1880 -- OUT:
1881 --
1882 -- RETURNS:
1883 -- select statement
1884 --
1885 -- NOTES:
1886 --
1887 -- HISTORY:
1888 -- 14-FEB-97 C. Tomberg Modified to use bind variables
1889 --
1890 FUNCTION build_select_sql( p_system_info IN
1891 arp_trx_global.system_info_rec_type,
1892 p_profile_info IN
1893 arp_trx_global.profile_rec_type,
1894 p_account_class IN VARCHAR2,
1895 p_customer_trx_id IN BINARY_INTEGER,
1896 p_customer_trx_line_id IN BINARY_INTEGER,
1897 p_cust_trx_line_salesrep_id IN BINARY_INTEGER,
1898 p_request_id IN BINARY_INTEGER,
1899 p_gl_date IN DATE,
1900 p_original_gl_date IN DATE,
1901 p_total_trx_amount IN NUMBER,
1902 p_code_combination_id IN BINARY_INTEGER,
1903 p_force_account_set_no IN VARCHAR2,
1904 p_cust_trx_type_id IN BINARY_INTEGER,
1905 p_primary_salesrep_id IN BINARY_INTEGER,
1906 p_inventory_item_id IN BINARY_INTEGER,
1907 p_memo_line_id IN BINARY_INTEGER )
1908 RETURN VARCHAR2 IS
1909
1910 l_based_on_salesrep_flag BOOLEAN := FALSE;
1911
1912 l_select_stmt VARCHAR2(32767);
1913
1914 l_amount_fragment VARCHAR2(200);
1915 l_ccid_fragment VARCHAR2(100);
1916 l_decode_fragment VARCHAR2(1000);
1917 l_line_type_fragment VARCHAR2(100);
1918 l_rule_id_fragment VARCHAR2(100);
1919 l_rule_fragment VARCHAR2(700);
1920 l_tax_table_fragment VARCHAR2(100);
1921
1922 l_gl_date_attribute VARCHAR2(1000);
1923 l_orig_gl_date_attribute VARCHAR2(2000);
1924 l_salesrep_attributes1 VARCHAR2(10000);
1925 l_salesrep_attributes2 VARCHAR2(1000);
1926 l_tax_attribute VARCHAR2(512);
1927
1928 l_gl_dist_table VARCHAR2(100);
1929 l_interface_lines_table VARCHAR2(100);
1930 l_inv_gl_dist_table VARCHAR2(100);
1931 l_salesreps_table VARCHAR2(100);
1932 l_tax_table VARCHAR2(100);
1933
1934 l_based_on_salesrep_pred VARCHAR2(500);
1935 l_cm_module_pred VARCHAR2(500);
1936 l_interface_table_pred VARCHAR2(500);
1937 l_inv_rec_pred VARCHAR2(500);
1938 l_line_id_pred VARCHAR2(500);
1939 l_line_salesrep_id_pred VARCHAR2(100);
1940 l_prevent_dup_rec_pred VARCHAR2(2000);
1941 l_request_id_pred VARCHAR2(2000);
1942 l_suspense_pred VARCHAR2(100);
1943 l_tax_pred VARCHAR2(500);
1944 l_trx_id_pred VARCHAR2(100);
1945 l_base_precision fnd_currencies.precision%type;
1946 l_base_min_acc_unit fnd_currencies.minimum_accountable_unit%type;
1947
1948 BEGIN
1949
1950 print_fcn_label( 'arp_auto_accounting.build_select_sql()+' );
1951
1952 l_base_precision := ARP_GLOBAL.base_precision;
1953 l_base_min_acc_unit := nvl(ARP_GLOBAL.base_min_acc_unit,1);
1954
1955 ------------------------------------------------------------------------
1956 -- Initialize building blocks
1957 ------------------------------------------------------------------------
1958 debug( ' Initialize fragments', MSG_LEVEL_DEVELOP );
1959
1960 l_amount_fragment := 'nvl(ctl.revenue_amount, ctl.extended_amount)';
1961 l_rule_id_fragment := '= nvl(ct.invoicing_rule_id, -10)';
1962 l_tax_table_fragment := 'ctl';
1963 l_tax_attribute := CRLF ||'to_number(''''),to_number(''''),';
1964
1965 ------------------------------------------------------------------------
1966 ------------------------------------------------------------------------
1967 -- Construct "building blocks" for the Select statement:
1968 -- string fragments
1969 -- attributes
1970 -- table names
1971 -- predicates
1972 ------------------------------------------------------------------------
1973 ------------------------------------------------------------------------
1974
1975 ------------------------------------------------------------------------
1976 -- Construct fragments
1977 ------------------------------------------------------------------------
1978
1979 -- If the force account set no flag is Y,
1980 -- then always treat this line as a non account set distribution
1981 -- otherwise, treat it normally.
1982 debug(' If the force account set no flag is Y', MSG_LEVEL_DEVELOP);
1983
1984
1985 l_rule_fragment :=
1986 'decode( NVL(:force_account_set_no, ''N''),
1987 ''N'', ct.invoicing_rule_id,
1988 decode(''' ||p_account_class || ''',
1989 ''UNBILL'', ct.invoicing_rule_id,
1990 ''UNEARN'', ct.invoicing_rule_id,
1991 decode(nvl(ctl.accounting_rule_duration, 0),
1992 1, decode(nvl(ctl.autorule_duration_processed, 0),
1993 0, ct.invoicing_rule_id,
1994 null),
1995 ct.invoicing_rule_id
1996 )
1997 )
1998 )';
1999
2000
2001 ------------------------------------------------------------------------
2002 -- Construct code_combination_id fragment
2003 ------------------------------------------------------------------------
2004 debug(' Construct code_combination_id fragment', MSG_LEVEL_DEVELOP);
2005
2006 IF( p_code_combination_id IS NULL ) THEN
2007
2008 -- IF Use Invoice Accounting is Yes,
2009 -- AND the account class is Receivable,
2010 -- AND no CCID was passed in
2011 -- THEN use the invoice's receivable CCID
2012 IF( p_profile_info.use_inv_acct_for_cm_flag = 'Y'
2013 AND p_account_class = REC ) THEN
2014
2015 l_ccid_fragment :=
2016 'nvl(lgd_inv_rec.code_combination_id, to_number(''''))';
2017
2018 ELSE
2019
2020 l_ccid_fragment := 'to_number('''')';
2021
2022 END IF;
2023 ELSE
2024
2025 l_ccid_fragment := ' :code_combination_id ';
2026
2027 END IF;
2028
2029
2030 ------------------------------------------------------------------------
2031 -- account_class
2032 ------------------------------------------------------------------------
2033 debug(' account_class', MSG_LEVEL_DEVELOP);
2034
2035 IF( p_account_class = REV ) THEN
2036
2037 IF( p_system_info.rev_based_on_salesrep ) THEN
2038 l_based_on_salesrep_flag := TRUE;
2039 END IF;
2040
2041 l_line_type_fragment := '= ''LINE''';
2042 l_decode_fragment :=
2043 'decode(' || l_rule_fragment || ',
2044 null, decode(ctl.extended_amount,
2045 0, 1,
2046 ctl.revenue_amount / ctl.extended_amount),
2047 1
2048 ) *';
2049
2050 ELSIF( p_account_class = REC ) THEN
2051
2052 l_amount_fragment :=
2053 'decode(:total_trx_amount,
2054 NULL, to_number(''''),
2055 to_char( :total_trx_amount )
2056 )' || CRLF;
2057
2058 l_line_type_fragment := '(+) = ''~!''';
2059
2060
2061 ELSIF( p_account_class = FREIGHT ) THEN
2062
2063 l_line_type_fragment := '= ''FREIGHT''';
2064 l_tax_table_fragment := 'ctl_line';
2065
2066 ELSIF( p_account_class = TAX ) THEN
2067
2068 IF( p_system_info.tax_based_on_salesrep ) THEN
2069 l_based_on_salesrep_flag := TRUE;
2070 END IF;
2071
2072 l_line_type_fragment := '= ''TAX''';
2073 l_tax_table_fragment := 'ctl_line';
2074
2075 /* 4558268 - Replaced source of tax and interim tax ccids */
2076 l_tax_attribute := CRLF ||
2077 'arp_etax_util.get_tax_account(ctl.customer_trx_line_id,
2078 rgd.gl_date, ''TAX''),'
2079 || CRLF ||
2080 'arp_etax_util.get_tax_account(ctl.customer_trx_line_id,
2081 rgd.gl_date, ''INTERIM''),';
2082
2083 ELSIF( p_account_class = UNBILL ) THEN
2084 /* Bug 2354293 - Consider UNEARN/UNBILL tied to salesreps
2085 if REV is... This prevents multiple UNEARN lines with 100%
2086 for a single REV line. */
2087 IF( p_system_info.unbill_based_on_salesrep or
2088 p_system_info.rev_based_on_salesrep) THEN
2089 l_based_on_salesrep_flag := TRUE;
2090 END IF;
2091
2092 l_line_type_fragment := '= ''LINE''';
2093 l_rule_id_fragment := '= -3';
2094
2095 ELSIF( p_account_class = UNEARN ) THEN
2096 /* Bug 2354293 */
2097 IF( p_system_info.unearn_based_on_salesrep or
2098 p_system_info.rev_based_on_salesrep) THEN
2099 l_based_on_salesrep_flag := TRUE;
2100 END IF;
2101
2102 l_line_type_fragment := '= ''LINE''';
2103 l_rule_id_fragment := '= -2';
2104
2105 ELSIF( p_account_class = SUSPENSE ) THEN
2106
2107 IF( p_system_info.suspense_based_on_salesrep ) THEN
2108 l_based_on_salesrep_flag := TRUE;
2109 END IF;
2110
2111 l_line_type_fragment := '= ''LINE''';
2112 l_amount_fragment := '(ctl.extended_amount - ctl.revenue_amount)';
2113 l_decode_fragment :=
2114 'decode(' || l_rule_fragment ||',
2115 null, decode( (ctl.extended_amount),
2116 0, 1,
2117 (ctl.extended_amount - ctl.revenue_amount) /
2118 ctl.extended_amount
2119 ),
2120 1
2121 ) * ';
2122
2123 ELSIF( p_account_class = CHARGES ) THEN
2124
2125 IF( p_system_info.rev_based_on_salesrep ) THEN
2126 l_based_on_salesrep_flag := TRUE;
2127 END IF;
2128
2129 l_line_type_fragment := '= ''CHARGES''';
2130
2131 END IF;
2132
2133 ------------------------------------------------------------------------
2134 -- Construct select attribute strings
2135 ------------------------------------------------------------------------
2136 debug(' Build select attribute strings', MSG_LEVEL_DEVELOP);
2137
2138 IF( l_based_on_salesrep_flag ) THEN
2139
2140 debug(' l_salesrep_attributes1', MSG_LEVEL_DEBUG);
2141 debug(' l_rule_fragment='||l_rule_fragment, MSG_LEVEL_DEBUG);
2142 debug(' l_amount_fragment='||l_amount_fragment, MSG_LEVEL_DEBUG);
2143 debug(' l_decode_fragment='||l_decode_fragment, MSG_LEVEL_DEBUG);
2144
2145 l_salesrep_attributes1 := CRLF ||
2146 'ctls.cust_trx_line_salesrep_id, /* cust_trx_line_salesrep_id */
2147 decode(' || l_rule_fragment || ',
2148 NULL, ' || l_amount_fragment || ',
2149 to_number('''')), /* line_amount */
2150 decode(' || l_rule_fragment || ',
2151 NULL, decode(' || l_base_min_acc_unit || ',
2152 NULL, round( (' || l_amount_fragment || '*
2153 nvl(ct.exchange_rate, 1)),
2154 ' || l_base_precision || '),
2155 round((' || l_amount_fragment || ' *
2156 nvl(ct.exchange_rate, 1)) /
2157 ' || l_base_min_acc_unit || ')
2158 * ' || l_base_min_acc_unit || '
2159 ),
2160 to_number('''')), /* accounted_line_amount */
2161 round(' || l_decode_fragment || '
2162 decode(ctls.salesrep_id,
2163 NULL, 100,
2164 nvl(ctls.revenue_percent_split, 0)),
2165 4), /* percent */
2166 decode(' || l_rule_fragment || ',
2167 NULL,
2168 decode(fc_foreign.minimum_accountable_unit,
2169 NULL, round ( (' || l_amount_fragment || ' *
2170 decode(ctls.salesrep_id,
2171 NULL, 100,
2172 nvl(ctls.revenue_percent_split, 0))/ 100),
2173 fc_foreign.precision),
2174 round ( (' || l_amount_fragment || ' *
2175 decode(ctls.salesrep_id,
2176 NULL, 100,
2177 nvl(ctls.revenue_percent_split,0)) / 100) /
2178 fc_foreign.minimum_accountable_unit ) *
2179 fc_foreign.minimum_accountable_unit ),
2180 to_number('''')), /* amount */
2181 decode(' || l_rule_fragment || ',
2182 NULL,
2183 decode (' || l_base_min_acc_unit || ',
2184 NULL,
2185 round (decode(fc_foreign.minimum_accountable_unit,
2186 NULL,
2187 round ((' || l_amount_fragment || ' *
2188 decode(ctls.salesrep_id,
2189 NULL, 100,
2190 nvl(ctls.revenue_percent_split,
2191 0)) / 100),
2192 fc_foreign.precision),
2193 round ((' || l_amount_fragment || ' *
2194 decode(ctls.salesrep_id,
2195 NULL, 100,
2196 nvl(ctls.revenue_percent_split,0))
2197 / 100) /
2198 fc_foreign.minimum_accountable_unit ) *
2199 fc_foreign.minimum_accountable_unit
2200 ) * nvl(ct.exchange_rate, 1),
2201 ' || l_base_precision || ' ),
2202 round ((decode( fc_foreign.minimum_accountable_unit,
2203 NULL, round(' || l_amount_fragment || ',
2204 fc_foreign.precision),
2205 round((' || l_amount_fragment || ' *
2206 decode(ctls.salesrep_id,
2207 NULL, 100,
2208 nvl(ctls.revenue_percent_split,0))
2209 / 100) /
2210 fc_foreign.minimum_accountable_unit ) *
2211 fc_foreign.minimum_accountable_unit
2212 ) * nvl(ct.exchange_rate, 1) )
2213 / ' || l_base_min_acc_unit || ' ) *
2214 ' || l_base_min_acc_unit || '
2215 ),
2216 to_number('''')), /* acctd_amount */';
2217
2218
2219 debug(' l_salesrep_attributes2', MSG_LEVEL_DEVELOP);
2220
2221 l_salesrep_attributes2 := CRLF ||
2222 'nvl(ctl.default_ussgl_transaction_code,
2223 ct.default_ussgl_transaction_code), /* ussgl_trx_code */
2224 nvl(ctl.default_ussgl_trx_code_context,
2225 ct.default_ussgl_trx_code_context), /* ussgl_trx_code_cntxt*/
2226 ctls.salesrep_id, /* salesrep_id */
2227 '|| l_tax_table_fragment ||'.inventory_item_id, /* inventory_item_id */
2228 '|| l_tax_table_fragment ||'.memo_line_id, /* memo_line_id */';
2229
2230
2231 ELSE -- not based on salesreps...
2232
2233 l_salesrep_attributes1 := CRLF ||
2234 'to_number(''''), /* cust_trx_line_salesrep_id */
2235 decode(' || l_rule_fragment || ',
2236 NULL, ' || l_amount_fragment || ',
2237 to_number('''')), /* line_amount */
2238 decode(' || l_rule_fragment || ',
2239 NULL, decode(' || l_base_min_acc_unit || ',
2240 NULL, round((' || l_amount_fragment || ' *
2241 nvl(ct.exchange_rate, 1)),
2242 ' || l_base_precision || '),
2243 round((' || l_amount_fragment || ' *
2244 nvl(ct.exchange_rate, 1)) /
2245 ' || l_base_min_acc_unit || ')
2246 * ' || l_base_min_acc_unit || '
2247 ),
2248 to_number('''')), /* accounted_line_amount */
2249 round(' || l_decode_fragment || ' 100, 4), /* percent */
2250 decode(' || l_rule_fragment || ',
2251 NULL, ' || l_amount_fragment || ',
2252 to_number('''') ), /* amount */
2253 decode(' || l_rule_fragment || ',
2254 NULL, decode( ' || l_base_min_acc_unit || ',
2255 NULL, round ((' || l_amount_fragment || ' *
2256 nvl(ct.exchange_rate, 1)),
2257 ' || l_base_precision || '),
2258 round((' || l_amount_fragment || ' *
2259 nvl(ct.exchange_rate, 1)) /
2260 ' || l_base_min_acc_unit || ' ) *
2261 ' || l_base_min_acc_unit || '
2262 ),
2263 to_number('''')), /* acctd_amt */';
2264
2265
2266 l_salesrep_attributes2 := CRLF ||
2267 'nvl(ctl.default_ussgl_transaction_code,
2268 ct.default_ussgl_transaction_code), /* ussgl_trx_code */
2269 nvl(ctl.default_ussgl_trx_code_context,
2270 ct.default_ussgl_trx_code_context), /* ussgl_trx_code_cntxt*/
2271 ct.primary_salesrep_id, /* salesrep_id */
2272 '|| l_tax_table_fragment ||'.inventory_item_id, /* inventory_item_id */
2273 '|| l_tax_table_fragment || '.memo_line_id, /* memo_line_id */';
2274
2275 END IF; -- if based on salesreps
2276
2277 debug( ' len(l_salesrep_attributes1)=' ||
2278 to_char(LENGTHB(l_salesrep_attributes1)),
2279 MSG_LEVEL_DEBUG );
2280 debug( ' len(l_salesrep_attributes2)=' ||
2281 to_char(LENGTHB(l_salesrep_attributes2)),
2282 MSG_LEVEL_DEBUG );
2283
2284
2285 ------------------------------------------------------------------------
2286 -- Construct gl_date attribute string
2287 ------------------------------------------------------------------------
2288 debug(' Construct gl_date attribute string', MSG_LEVEL_DEVELOP);
2289
2290 IF( p_gl_date IS NULL ) THEN
2291 /* 5590182 */
2292 /* 5921925 - removed request_id > 0 condition */
2293 IF( p_account_class = REC AND p_request_id IS NOT NULL)
2294 THEN
2295
2296 l_gl_date_attribute := CRLF ||
2297 'to_char(ril.gl_date, ''J''), /* gl_date */';
2298
2299 ELSE
2300 IF( p_account_class = REC AND
2301 p_request_id IS NULL)
2302 THEN
2303
2304 l_gl_date_attribute := CRLF ||
2305 'to_char(rgd.gl_date, ''J''), /* gl_date */';
2306
2307 ELSE
2308
2309 l_gl_date_attribute := CRLF ||
2310 'decode('|| l_rule_fragment ||',
2311 NULL, to_char(rgd.gl_date, ''J''),
2312 '''' ), /* gl_date */';
2313
2314 END IF;
2315 END IF;
2316 ELSE -- p_gl_date NOT NULL
2317 IF( p_account_class = REC ) THEN
2318
2319 l_gl_date_attribute := CRLF ||
2320 'to_char(nvl(rgd.gl_date, :gl_date), ''J''), /* gl_date */';
2321
2322 ELSE
2323 l_gl_date_attribute := CRLF ||
2324 'decode('|| l_rule_fragment ||',
2325 NULL, to_char(nvl(rgd.gl_date, :gl_date), ''J''),
2326 '''' ), /* gl_date */';
2327
2328 END IF;
2329
2330
2331 END IF;
2332
2333 ------------------------------------------------------------------------
2334 -- Construct original_gl_date attribute string
2335 ------------------------------------------------------------------------
2336 debug(' Construct original_gl_date attribute string',
2337 MSG_LEVEL_DEVELOP);
2338
2339 l_orig_gl_date_attribute := CRLF ||
2340 'decode( :original_gl_date,
2341 NULL, decode(' || l_rule_fragment || ',
2342 NULL, to_char(rgd.original_gl_date, ''J''),
2343 '''' ),
2344 decode(' || l_rule_fragment || ',
2345 NULL, to_char(nvl(rgd.original_gl_date,
2346 :original_gl_date), ''J''),
2347 '''' )
2348 ), /* orig_gl_date */';
2349
2350
2351 ------------------------------------------------------------------------
2352 -- Construct table strings
2353 ------------------------------------------------------------------------
2354 debug(' tables', MSG_LEVEL_DEVELOP);
2355
2356 IF( p_account_class = REC
2357 AND p_gl_date IS NULL
2358 AND p_request_id IS NOT NULL)
2359 THEN
2360 /* 5921925 - modified gl_date logic for invoice API */
2361 IF p_request_id > 0
2362 THEN
2363 l_interface_lines_table := CRLF ||'ra_interface_lines_gt ril,';
2364 ELSE
2365 l_interface_lines_table := CRLF ||'ar_trx_header_gt ril,';
2366 END IF;
2367 END IF;
2368
2369 IF( p_profile_info.use_inv_acct_for_cm_flag = 'Y'
2370 AND p_account_class = REC
2371 AND p_code_combination_id IS NULL ) THEN
2372
2373 l_inv_gl_dist_table := CRLF ||'ra_cust_trx_line_gl_dist lgd_inv_rec,';
2374
2375 END IF;
2376
2377 IF( l_based_on_salesrep_flag ) THEN
2378
2379 l_salesreps_table := CRLF ||'ra_cust_trx_line_salesreps ctls,';
2380
2381 END IF;
2382
2383 IF( p_account_class in ( TAX, FREIGHT ) ) THEN
2384
2385 /* 4558268 - Removed tax tables */
2386 l_tax_table := CRLF ||
2387 'ra_customer_trx_lines ctl_line,';
2388
2389 END IF;
2390
2391 IF( p_account_class = REC ) THEN
2392
2393 l_gl_dist_table := CRLF ||'ra_cust_trx_line_gl_dist lgd,';
2394
2395 END IF;
2396
2397 ------------------------------------------------------------------------
2398 -- Construct predicates
2399 ------------------------------------------------------------------------
2400
2401 ------------------------------------------------------------------------
2402 -- Prevent AutoAccounting from creating records that should
2403 -- be created by the credit memo module.
2404 ------------------------------------------------------------------------
2405
2406 debug(' Prevent AutoAccounting from creating records', MSG_LEVEL_DEVELOP);
2407
2408 IF( p_profile_info.use_inv_acct_for_cm_flag = 'Y'
2409 AND p_account_class <> REC ) THEN
2410
2411 l_cm_module_pred := CRLF ||
2412 ' /* Prevent AutoAccounting from creating records that should
2413 be created by the credit memo module. */
2414 AND (ct.previous_customer_trx_id is null';
2415
2416 IF( p_code_combination_id IS NOT NULL
2417 AND p_account_class = FREIGHT ) THEN
2418
2419 l_cm_module_pred := l_cm_module_pred || '
2420 or (ct.invoicing_rule_id is null)';
2421
2422 END IF;
2423
2424 l_cm_module_pred := l_cm_module_pred || ')';
2425
2426 END IF;
2427
2428 ------------------------------------------------------------------------
2429 -- Prevent duplicate records from being created
2430 ------------------------------------------------------------------------
2431 debug(' Prevent duplicate records from being created', MSG_LEVEL_DEVELOP);
2432
2433 IF( p_account_class = REC ) THEN
2434
2435 l_prevent_dup_rec_pred := CRLF ||
2436 ' /* prevent duplicate records from being created */
2437 AND ct.customer_trx_id = lgd.customer_trx_id(+)
2438 AND ''REC'' = lgd.account_class(+)
2439 AND decode(ct.invoicing_rule_id,
2440 NULL, ''N'',
2441 ''Y'' ) = lgd.account_set_flag(+)
2442 AND lgd.customer_trx_id is null';
2443
2444 ELSE
2445 l_prevent_dup_rec_pred := CRLF ||
2446 ' /* prevent duplicate records from being created */
2447 AND not exists
2448 (SELECT ';
2449 -- bug 7557904
2450 IF( p_request_id IS NOT NULL ) THEN
2451 l_prevent_dup_rec_pred := l_prevent_dup_rec_pred || ' /*+ INDEX (lgd RA_CUST_TRX_LINE_GL_DIST_N10)*/ ';
2452 ELSE
2453 l_prevent_dup_rec_pred := l_prevent_dup_rec_pred || ' /*+ INDEX (lgd RA_CUST_TRX_LINE_GL_DIST_N1)*/ ';
2454 END IF;
2455
2456 l_prevent_dup_rec_pred := l_prevent_dup_rec_pred || '
2457 ''distribution exists''
2458 FROM ra_cust_trx_line_gl_dist lgd
2459 WHERE ctl.customer_trx_id = lgd.customer_trx_id
2460 AND ctl.customer_trx_line_id = lgd.customer_trx_line_id ';
2461
2462
2463 IF( p_request_id IS NOT NULL ) THEN
2464 l_prevent_dup_rec_pred := l_prevent_dup_rec_pred || '
2465 and lgd.request_id =
2466 :request_id1 ';
2467 END IF;
2468
2469
2470 IF( p_cust_trx_line_salesrep_id IS NOT NULL ) THEN
2471 l_prevent_dup_rec_pred := l_prevent_dup_rec_pred || '
2472 and lgd.cust_trx_line_salesrep_id =
2473 :cust_trx_line_salesrep_id ';
2474 END IF;
2475
2476 l_prevent_dup_rec_pred := l_prevent_dup_rec_pred || '
2477 and '''|| p_account_class ||''' = lgd.account_class
2478 and decode(ct.invoicing_rule_id,
2479 NULL, ''N'',
2480 ''Y'' ) = lgd.account_set_flag
2481 )';
2482
2483 END IF;
2484
2485 ------------------------------------------------------------------------
2486 -- Create Tax predicate
2487 ------------------------------------------------------------------------
2488 debug(' Create Tax predicate', MSG_LEVEL_DEVELOP);
2489
2490 IF( p_account_class in (TAX, FREIGHT) ) THEN
2491
2492 /* 4558268 - Removed joins for tax tables */
2493
2494 l_tax_pred := CRLF ||
2495 'AND ctl.link_to_cust_trx_line_id =
2496 ctl_line.customer_trx_line_id(+)';
2497
2498 END IF;
2499
2500 ------------------------------------------------------------------------
2501 -- Create Suspense predicate
2502 ------------------------------------------------------------------------
2503 debug(' Create suspense predicate', MSG_LEVEL_DEVELOP);
2504
2505 IF( p_account_class = SUSPENSE ) THEN
2506
2507 l_suspense_pred := CRLF ||
2508 'AND (ctl.extended_amount - ctl.revenue_amount) <> 0';
2509
2510
2511 END IF;
2512
2513 ------------------------------------------------------------------------
2514 -- Create predicate that is based on salesrep flag
2515 ------------------------------------------------------------------------
2516 debug(' Create predicate that is based on salesrep flag',
2517 MSG_LEVEL_DEVELOP);
2518
2519 IF( l_based_on_salesrep_flag ) THEN
2520
2521 -- bug2963903 : Added condition for revenue_percent_split
2522 l_based_on_salesrep_pred := CRLF ||
2523 'AND ctl.customer_trx_id = ctls.customer_trx_id(+)
2524 AND nvl(ctl.link_to_cust_trx_line_id,
2525 ctl.customer_trx_line_id) = ctls.customer_trx_line_id(+)
2526 AND (ctls.revenue_percent_split is not null
2527 or ctls.customer_trx_line_id is null)' ;
2528
2529 END IF;
2530
2531 IF( p_account_class = REC
2532 AND p_gl_date IS NULL
2533 AND p_request_id IS NOT NULL)
2534 THEN
2535 IF p_request_id > 0
2536 THEN
2537
2538 /* 5169215 - a cartesian join between ra_interface_lines and
2539 ra_customer_trx was resulting in incorrect GL_DATE on
2540 REC distributions. This relates to bug 4483951, but the fix
2541 for that does not entirely resolve the problem */
2542 l_interface_table_pred := CRLF ||
2543 'AND ril.rowid = (SELECT /*+ no_unnest */ min(ril2.rowid)
2544 FROM ra_interface_lines_gt ril2
2545 WHERE ril2.customer_trx_id = ct.customer_trx_id
2546 AND ril2.link_to_line_id is null)
2547 AND ril.customer_trx_id = ct.customer_trx_id ';
2548
2549 ELSE
2550 /* 5921925 - invoice api gl_date issues */
2551 l_interface_table_pred := CRLF ||
2552 'AND ril.customer_trx_id = ct.customer_trx_id';
2553 END IF;
2554 END IF;
2555
2556 ------------------------------------------------------------------------
2557 -- request_id
2558 ------------------------------------------------------------------------
2559 IF( p_request_id IS NOT NULL ) THEN
2560
2561 /* Bug 2116064 - Added 'is not null' condition */
2562 l_request_id_pred := CRLF ||
2563 'AND ct.request_id = :request_id';
2564
2565 l_request_id_pred := l_request_id_pred || CRLF ||
2566 'AND ctl.request_id (+) = :request_id'; -- 7039838
2567
2568 l_request_id_pred := l_request_id_pred || CRLF ||
2569 'AND ct.request_id is not null';
2570
2571 END IF;
2572
2573 ------------------------------------------------------------------------
2574 -- customer_trx_id
2575 ------------------------------------------------------------------------
2576 IF( p_customer_trx_id IS NOT NULL ) THEN
2577
2578 l_trx_id_pred := CRLF ||
2579 'AND ct.customer_trx_id = :customer_trx_id';
2580
2581 END IF;
2582
2583 ------------------------------------------------------------------------
2584 -- customer_trx_line_id
2585 ------------------------------------------------------------------------
2586 IF( p_customer_trx_line_id IS NOT NULL ) THEN
2587
2588 l_line_id_pred := CRLF ||
2589 'AND (ctl.customer_trx_line_id = :customer_trx_line_id
2590 OR
2591 ctl.link_to_cust_trx_line_id = :customer_trx_line_id';
2592
2593 /* Bug 1793936 - Creating extra UNEARN/UNBILL rows under certain circumstances
2594
2595 -- IF( p_customer_trx_id IS NOT NULL
2596 -- OR p_request_id IS NOT NULL ) THEN
2597
2598 -- l_line_id_pred := l_line_id_pred || '
2599 -- OR ctl.link_to_cust_trx_line_id is null';
2600 --
2601 -- END IF;
2602 */
2603 l_line_id_pred := l_line_id_pred || ')';
2604
2605 END IF;
2606
2607 IF( p_cust_trx_line_salesrep_id IS NOT NULL
2608 AND l_based_on_salesrep_flag ) THEN
2609
2610 l_line_salesrep_id_pred := CRLF ||
2611 'AND ctls.cust_trx_line_salesrep_id = :cust_trx_line_salesrep_id';
2612
2613 END IF;
2614
2615
2616 ------------------------------------------------------------------------
2617 -- IF Use Invoice Accounting is Yes,
2618 -- AND the account class is Receivable,
2619 -- AND no CCID was passed in
2620 -- THEN join to the invoice's receivable record for credit memos
2621 -- to get the invoice's receivable CCID
2622 ------------------------------------------------------------------------
2623 IF( p_profile_info.use_inv_acct_for_cm_flag = 'Y'
2624 AND p_account_class = REC
2625 AND p_code_combination_id IS NULL ) THEN
2626
2627 l_inv_rec_pred := CRLF ||
2628 ' /* Join to the invoice receivable record to get the CCID */
2629 AND ct.previous_customer_trx_id = lgd_inv_rec.customer_trx_id(+)
2630 AND lgd_inv_rec.account_class(+) = ''REC''
2631 AND lgd_inv_rec.latest_rec_flag(+) = ''Y''';
2632
2633 END IF;
2634
2635 ------------------------------------------------------------------------
2636 -- Put it all together
2637 ------------------------------------------------------------------------
2638 debug(' Put it all together ', MSG_LEVEL_DEVELOP);
2639
2640 /* 7039838 - changed hints for FT tuning */
2641 IF p_request_id IS NOT NULL
2642 THEN
2643 l_select_stmt :=
2644 'SELECT /*+ index(ct,RA_CUSTOMER_TRX_N15) index(ctl,RA_CUSTOMER_TRX_LINES_N4) use_hash(ctl) */ ' || CRLF;
2645 ELSE
2646 l_select_stmt := 'SELECT' || CRLF;
2647 END IF;
2648
2649 l_select_stmt := l_select_stmt ||
2650 'ct.customer_trx_id, /* customer_trx_id */
2651 ctl.customer_trx_line_id, /* customer_trx_line_id */'
2652 || l_salesrep_attributes1
2653 || CRLF ||
2654 '''' || p_account_class || ''', /* account class */
2655 decode('|| l_rule_fragment ||',
2656 NULL, ''N'',
2657 ''Y'' ), /* account_set_flag */
2658 ct.cust_trx_type_id, /* cust_trx_type_id */
2659 decode(ct.invoicing_rule_id,
2660 -3, ''Y'',
2661 ''N''), /* allow_not_open_flag */
2662 to_char(''''), /* concatenated segments */'
2663 || CRLF
2664 || l_ccid_fragment ||', /* code_combination_id */'
2665 || l_gl_date_attribute
2666 || l_orig_gl_date_attribute
2667 || l_salesrep_attributes2
2668 || l_tax_attribute
2669 || 'ct.bill_to_site_use_id, /* Billing site id */ '
2670 || l_tax_table_fragment ||'.warehouse_id /* Warehouse id */ '
2671 || ', ctl.link_to_cust_trx_line_id /* 1651593 - tax errors */'
2672 || CRLF
2673 ||'FROM'
2674 || l_interface_lines_table
2675 || l_inv_gl_dist_table
2676 || l_salesreps_table
2677 || l_tax_table
2678 || CRLF ||
2679 'fnd_currencies fc_foreign,'
2680 || l_gl_dist_table
2681 || CRLF ||
2682 'ra_cust_trx_line_gl_dist rgd,
2683 ra_customer_trx_lines ctl,
2684 ra_customer_trx ct
2685 WHERE ct.customer_trx_id = ctl.customer_trx_id(+)
2686 AND ct.invoice_currency_code = fc_foreign.currency_code'
2687 || l_cm_module_pred
2688 || l_prevent_dup_rec_pred
2689 || CRLF ||
2690 'AND ct.customer_trx_id = rgd.customer_trx_id(+)
2691 AND ''REC'' = rgd.account_class(+)
2692 AND ''N'' = rgd.account_set_flag(+)
2693 AND ctl.line_type '|| l_line_type_fragment
2694 || l_tax_pred
2695 || CRLF ||
2696 'and nvl(ct.invoicing_rule_id,
2697 -10) '|| l_rule_id_fragment
2698 || l_suspense_pred
2699 || l_based_on_salesrep_pred
2700 || l_interface_table_pred
2701 || l_request_id_pred
2702 || l_trx_id_pred
2703 || l_line_id_pred
2704 || l_line_salesrep_id_pred
2705 || l_inv_rec_pred ;
2706
2707 debug( l_select_stmt, MSG_LEVEL_DEBUG );
2708 debug( ' len(l_select_stmt)=' ||
2709 to_char(LENGTHB(l_select_stmt)), MSG_LEVEL_DEBUG );
2710
2711
2712 print_fcn_label( 'arp_auto_accounting.build_select_sql()-' );
2713 RETURN l_select_stmt;
2714
2715
2716
2717 EXCEPTION
2718 WHEN OTHERS THEN
2719 debug('EXCEPTION: arp_auto_accounting.build_select_sql()',
2720 MSG_LEVEL_BASIC);
2721 debug(SQLERRM);
2722 RAISE;
2723 END build_select_sql;
2724
2725
2726 ----------------------------------------------------------------------------
2727 --
2728 -- FUNCTION NAME: add_segments_to_cache
2729 --
2730 -- DECSRIPTION:
2731 -- Addes the segment values for a given ccid to the segment value caches.
2732 --
2733 -- ARGUMENTS:
2734 -- IN:
2735 -- ccid
2736 -- segment_number (from the column name 'SEGMENTxx')
2737 --
2738 -- IN/OUT:
2739 --
2740 -- OUT:
2741 -- p_desired_segment
2742 --
2743 -- RETURNS:
2744 -- segment value. NULL if data not found.
2745 --
2746 -- NOTES:
2747 -- exception raised if no rows found
2748 -- I did not use record to contain these table values in order to be
2749 -- backward compatible with earlier versions of PL/SQL that did not allow
2750 -- tables of records.
2751 --
2752 -- HISTORY:
2753 --
2754 --
2755 PROCEDURE add_segments_to_cache( p_ccid IN binary_integer,
2756 p_segment_number IN binary_integer,
2757 p_desired_segment OUT NOCOPY varchar2) IS
2758
2759 l_segment1 varchar2(30);
2760 l_segment2 varchar2(30);
2761 l_segment3 varchar2(30);
2762 l_segment4 varchar2(30);
2763 l_segment5 varchar2(30);
2764 l_segment6 varchar2(30);
2765 l_segment7 varchar2(30);
2766 l_segment8 varchar2(30);
2767 l_segment9 varchar2(30);
2768 l_segment10 varchar2(30);
2769 l_segment11 varchar2(30);
2770 l_segment12 varchar2(30);
2771 l_segment13 varchar2(30);
2772 l_segment14 varchar2(30);
2773 l_segment15 varchar2(30);
2774 l_segment16 varchar2(30);
2775 l_segment17 varchar2(30);
2776 l_segment18 varchar2(30);
2777 l_segment19 varchar2(30);
2778 l_segment20 varchar2(30);
2779 l_segment21 varchar2(30);
2780 l_segment22 varchar2(30);
2781 l_segment23 varchar2(30);
2782 l_segment24 varchar2(30);
2783 l_segment25 varchar2(30);
2784 l_segment26 varchar2(30);
2785 l_segment27 varchar2(30);
2786 l_segment28 varchar2(30);
2787 l_segment29 varchar2(30);
2788 l_segment30 varchar2(30);
2789
2790 BEGIN
2791
2792 print_fcn_label2( 'arp_auto_accounting.add_segments_to_cache()+' );
2793
2794 SELECT segment1,
2795 segment2,
2796 segment3,
2797 segment4,
2798 segment5,
2799 segment6,
2800 segment7,
2801 segment8,
2802 segment9,
2803 segment10,
2804 segment11,
2805 segment12,
2806 segment13,
2807 segment14,
2808 segment15,
2809 segment16,
2810 segment17,
2811 segment18,
2812 segment19,
2813 segment20,
2814 segment21,
2815 segment22,
2816 segment23,
2817 segment24,
2818 segment25,
2819 segment26,
2820 segment27,
2821 segment28,
2822 segment29,
2823 segment30,
2824 DECODE(p_segment_number,
2825 1, segment1,
2826 2, segment2,
2827 3, segment3,
2828 4, segment4,
2829 5, segment5,
2830 6, segment6,
2831 7, segment7,
2832 8, segment8,
2833 9, segment9,
2834 10, segment10,
2835 11, segment11,
2836 12, segment12,
2837 13, segment13,
2838 14, segment14,
2839 15, segment15,
2840 16, segment16,
2841 17, segment17,
2842 18, segment18,
2843 19, segment19,
2844 20, segment20,
2845 21, segment21,
2846 22, segment22,
2847 23, segment23,
2848 24, segment24,
2849 25, segment25,
2850 26, segment26,
2851 27, segment27,
2852 28, segment28,
2853 29, segment29,
2854 30, segment30, null)
2855 INTO l_segment1,
2856 l_segment2,
2857 l_segment3,
2858 l_segment4,
2859 l_segment5,
2860 l_segment6,
2861 l_segment7,
2862 l_segment8,
2863 l_segment9,
2864 l_segment10,
2865 l_segment11,
2866 l_segment12,
2867 l_segment13,
2868 l_segment14,
2869 l_segment15,
2870 l_segment16,
2871 l_segment17,
2872 l_segment18,
2873 l_segment19,
2874 l_segment20,
2875 l_segment21,
2876 l_segment22,
2877 l_segment23,
2878 l_segment24,
2879 l_segment25,
2880 l_segment26,
2881 l_segment27,
2882 l_segment28,
2883 l_segment29,
2884 l_segment30,
2885 p_desired_segment
2886 FROM gl_code_combinations
2887 WHERE code_combination_id = p_ccid;
2888
2889
2890 /*--------------------------------------------------+
2891 | Add the selected segments to the segment cache |
2892 | only if the cache is not already full. |
2893 +--------------------------------------------------*/
2894
2895 IF ( segment1_cache.count <= MAX_SEGMENT_CACHE_SIZE )
2896 THEN
2897 segment1_cache(p_ccid) := l_segment1;
2898 segment2_cache(p_ccid) := l_segment2;
2899 segment3_cache(p_ccid) := l_segment3;
2900 segment4_cache(p_ccid) := l_segment4;
2901 segment5_cache(p_ccid) := l_segment5;
2902 segment6_cache(p_ccid) := l_segment6;
2903 segment7_cache(p_ccid) := l_segment7;
2904 segment8_cache(p_ccid) := l_segment8;
2905 segment9_cache(p_ccid) := l_segment9;
2906 segment10_cache(p_ccid) := l_segment10;
2907 segment11_cache(p_ccid) := l_segment11;
2908 segment12_cache(p_ccid) := l_segment12;
2909 segment13_cache(p_ccid) := l_segment13;
2910 segment14_cache(p_ccid) := l_segment14;
2911 segment15_cache(p_ccid) := l_segment15;
2912 segment16_cache(p_ccid) := l_segment16;
2913 segment17_cache(p_ccid) := l_segment17;
2914 segment18_cache(p_ccid) := l_segment18;
2915 segment19_cache(p_ccid) := l_segment19;
2916 segment20_cache(p_ccid) := l_segment20;
2917 segment21_cache(p_ccid) := l_segment21;
2918 segment22_cache(p_ccid) := l_segment22;
2919 segment23_cache(p_ccid) := l_segment23;
2920 segment24_cache(p_ccid) := l_segment24;
2921 segment25_cache(p_ccid) := l_segment25;
2922 segment26_cache(p_ccid) := l_segment26;
2923 segment27_cache(p_ccid) := l_segment27;
2924 segment28_cache(p_ccid) := l_segment28;
2925 segment29_cache(p_ccid) := l_segment29;
2926 segment30_cache(p_ccid) := l_segment30;
2927 END IF;
2928
2929 print_fcn_label2( 'arp_auto_accounting.add_segments_to_cache()-' );
2930
2931 EXCEPTION
2932 WHEN OTHERS THEN
2933 debug( 'EXCEPTION: arp_auto_accounting.add_segments_to_cache()',
2934 MSG_LEVEL_BASIC );
2935 debug(SQLERRM, MSG_LEVEL_BASIC);
2936 RAISE;
2937
2938 END;
2939
2940
2941 ----------------------------------------------------------------------------
2942 --
2943 -- FUNCTION NAME: get_segment_from_glcc
2944 --
2945 -- DECSRIPTION:
2946 -- Retrieves a GL code combination segment for a ccid
2947 --
2948 -- ARGUMENTS:
2949 -- IN:
2950 -- ccid
2951 -- segment_number (from the column name 'SEGMENTxx')
2952 --
2953 -- IN/OUT:
2954 --
2955 -- OUT:
2956 --
2957 -- RETURNS:
2958 -- segment value. NULL if data not found.
2959 --
2960 -- NOTES:
2961 -- exception raised if no rows found
2962 --
2963 -- HISTORY:
2964 --
2965 --
2966 FUNCTION get_segment_from_glcc( p_ccid IN BINARY_INTEGER,
2967 p_segment_number IN BINARY_INTEGER )
2968 RETURN VARCHAR2 IS
2969
2970 l_segment_value VARCHAR2(25);
2971 i BINARY_INTEGER := 0;
2972 l_bool boolean;
2973 l_ccid BINARY_INTEGER;
2974
2975 l_desired_segment varchar2(30);
2976
2977 BEGIN
2978
2979 print_fcn_label2( 'arp_auto_accounting.get_segment_from_glcc()+' );
2980
2981 BEGIN
2982
2983 if (p_segment_number = 1)
2984 then return(segment1_cache(p_ccid));
2985 elsif (p_segment_number = 2)
2986 then return(segment2_cache(p_ccid));
2987 elsif (p_segment_number = 3)
2988 then return(segment3_cache(p_ccid));
2989 elsif (p_segment_number = 4)
2990 then return(segment4_cache(p_ccid));
2991 elsif (p_segment_number = 5)
2992 then return(segment5_cache(p_ccid));
2993 elsif (p_segment_number = 6)
2994 then return(segment6_cache(p_ccid));
2995 elsif (p_segment_number = 7)
2996 then return(segment7_cache(p_ccid));
2997 elsif (p_segment_number = 8)
2998 then return(segment8_cache(p_ccid));
2999 elsif (p_segment_number = 9)
3000 then return(segment9_cache(p_ccid));
3001 elsif (p_segment_number = 10)
3002 then return(segment10_cache(p_ccid));
3003 elsif (p_segment_number = 11)
3004 then return(segment11_cache(p_ccid));
3005 elsif (p_segment_number = 12)
3006 then return(segment12_cache(p_ccid));
3007 elsif (p_segment_number = 13)
3008 then return(segment13_cache(p_ccid));
3009 elsif (p_segment_number = 14)
3010 then return(segment14_cache(p_ccid));
3011 elsif (p_segment_number = 15)
3012 then return(segment15_cache(p_ccid));
3013 elsif (p_segment_number = 16)
3014 then return(segment16_cache(p_ccid));
3015 elsif (p_segment_number = 17)
3016 then return(segment17_cache(p_ccid));
3017 elsif (p_segment_number = 18)
3018 then return(segment18_cache(p_ccid));
3019 elsif (p_segment_number = 19)
3020 then return(segment19_cache(p_ccid));
3021 elsif (p_segment_number = 20)
3022 then return(segment20_cache(p_ccid));
3023 elsif (p_segment_number = 21)
3024 then return(segment21_cache(p_ccid));
3025 elsif (p_segment_number = 22)
3026 then return(segment22_cache(p_ccid));
3027 elsif (p_segment_number = 23)
3028 then return(segment23_cache(p_ccid));
3029 elsif (p_segment_number = 24)
3030 then return(segment24_cache(p_ccid));
3031 elsif (p_segment_number = 25)
3032 then return(segment25_cache(p_ccid));
3033 elsif (p_segment_number = 26)
3034 then return(segment26_cache(p_ccid));
3035 elsif (p_segment_number = 27)
3036 then return(segment27_cache(p_ccid));
3037 elsif (p_segment_number = 28)
3038 then return(segment28_cache(p_ccid));
3039 elsif (p_segment_number = 29)
3040 then return(segment29_cache(p_ccid));
3041 elsif (p_segment_number = 30)
3042 then return(segment30_cache(p_ccid));
3043 end if;
3044
3045
3046 EXCEPTION
3047 WHEN NO_DATA_FOUND
3048 THEN
3049
3050 /*--------------------------------------------------------------+
3051 | The ccid was not in the cache. |
3052 | Select the segments from gl_code_combinations and add them |
3053 | to the cache if it is not already full. |
3054 +--------------------------------------------------------------*/
3055
3056 add_segments_to_cache(p_ccid, p_segment_number,l_desired_segment);
3057
3058 debug('getting segment ' || p_segment_number ||
3059 'for ccid ' || p_ccid ||
3060 ' from gl_code_combinations', MSG_LEVEL_DEBUG);
3061
3062 print_fcn_label2( 'arp_auto_accounting.get_segment_from_glcc()-' );
3063 RETURN(l_desired_segment);
3064
3065 WHEN OTHERS THEN RAISE;
3066
3067 END;
3068
3069
3070 EXCEPTION
3071 WHEN NO_DATA_FOUND THEN
3072 debug('arp_auto_accounting.get_segment_from_glcc(): no data found',
3073 MSG_LEVEL_DEBUG);
3074 RETURN NULL;
3075 WHEN OTHERS THEN
3076 /* debug('EXCEPTION: arp_auto_accounting.get_segment_from_glcc('
3077 || to_char(p_ccid) || ', '
3078 || to_char(p_segment_number) || ')', MSG_LEVEL_BASIC); */
3079 debug(SQLERRM, MSG_LEVEL_BASIC);
3080 RAISE;
3081 END get_segment_from_glcc;
3082
3083 ----------------------------------------------------------------------------
3084 --
3085 -- FUNCTION NAME: Get_Ccid_From_Cache
3086 --
3087 -- DECSRIPTION:
3088 -- Retrieves valid GL code combination from the cache or from the AOL
3089 -- API routine if the value is not yet in the cache.
3090 --
3091 -- ARGUMENTS:
3092 -- IN:
3093 -- p_system_info
3094 -- p_concat_segments
3095 -- p_segment_table
3096 -- p_segment_cnt
3097 -- p_account_class
3098 --
3099 -- IN/OUT:
3100 --
3101 -- OUT:
3102 -- p_result
3103 --
3104 -- RETURNS:
3105 -- ccid
3106 --
3107 -- NOTES:
3108 --
3109 -- HISTORY:
3110 /* 21-APR-03 MRAYMOND 2142306 Rewrote this routine to use hash indexes
3111 in place of linear scans. If a collision
3112 occurs, we will log the second (and
3113 successive colliding accounts in a linear
3114 table.
3115
3116 Also note that this version stores all
3117 ccids (REV, REC, etc) in a single table
3118 rather than having separate tables for
3119 each.
3120 */
3121
3122 FUNCTION Get_Ccid_From_Cache( p_system_info IN
3123 arp_trx_global.system_info_rec_type,
3124 p_concat_segments IN varchar2,
3125 p_segment_table IN fnd_flex_ext.SegmentArray,
3126 p_segment_cnt IN BINARY_INTEGER,
3127 p_account_class IN
3128 ra_cust_trx_line_gl_dist.account_class%type,
3129 p_result OUT NOCOPY BOOLEAN
3130 ) RETURN BINARY_INTEGER IS
3131
3132 hash_value NUMBER;
3133 tab_indx BINARY_INTEGER := 0;
3134 found BOOLEAN := FALSE;
3135 valid BOOLEAN := FALSE;
3136 l_ccid BINARY_INTEGER;
3137
3138 BEGIN
3139
3140 print_fcn_label2( 'arp_auto_accounting.get_ccid_from_cache()+' );
3141
3142 /*----------------------------------------------------------------+
3143 | Rewritten for bug 2142306 (23-Jan-02) - performance issues |
3144 | |
3145 | Search the cache for the concantenated segments. |
3146 | Return the ccid if it is in the cache. |
3147 | |
3148 | If not found in cache, search the linear table (where ccid's |
3149 | will go if collision on the hash table occurs). |
3150 | |
3151 | A NO_DATA_FOUND exception will be generated if the segments |
3152 | are not found in either table. This will result in a call to |
3153 | the flexfield api to get the ccid and puts it in the cache |
3154 | table if no collision occurs (and the cache is not already |
3155 | full) or the linear table if the hash value already exists in |
3156 | the hash table (and its not full). The number of rows in the |
3157 | linear table should be very small! |
3158 +----------------------------------------------------------------*/
3159
3160 hash_value := dbms_utility.get_hash_value(p_concat_segments,
3161 HASH_START, HASH_MAX);
3162
3163
3164 /* The following flow looks like this:
3165
3166 IF hash exists
3167 IF concatenated segs at hash are same
3168 IF date is valid
3169 Use ccid from hash table
3170 ENDIF
3171 ELSE concatenated segs not same (collision)
3172 LOOP through linear table
3173 IF concatenated segs at linear are same
3174 IF date is valid
3175 Use ccid from linear table
3176 ENDIF
3177 ELSE
3178 make room in linear table for new ccid
3179 ENDIF
3180 END LOOP
3181 ENDIF
3182 ENDIF
3183
3184 IF the hash for the segments is not found in the hash table,
3185 then it is a new ccid and it is added to the table.
3186 */
3187
3188
3189 IF autoacc_hash_seg_cache.exists(hash_value) THEN
3190
3191 IF autoacc_hash_seg_cache(hash_value) = p_concat_segments THEN
3192
3193 found := TRUE;
3194
3195 IF (validation_date BETWEEN autoacc_hash_st_date_cache(hash_value)
3196 and autoacc_hash_end_date_cache(hash_value))
3197 THEN
3198 l_ccid := autoacc_hash_id_cache(hash_value);
3199 valid := TRUE;
3200 END IF;
3201
3202 ELSE --- collision has occurred
3203 tab_indx := 1; -- start at top of linear table and search for match
3204
3205 WHILE ((tab_indx <= tab_size) AND (not found)) LOOP
3206
3207 IF autoacc_lin_seg_cache(tab_indx) = p_concat_segments THEN
3208
3209 found := TRUE;
3210
3211 IF (validation_date BETWEEN autoacc_lin_st_date_cache(tab_indx)
3212 and autoacc_lin_end_date_cache(tab_indx))
3213 THEN
3214 l_ccid := autoacc_lin_id_cache(tab_indx);
3215 valid := TRUE;
3216 END IF;
3217
3218 ELSE
3219
3220 tab_indx := tab_indx + 1;
3221
3222 END IF;
3223
3224 END LOOP;
3225
3226 END IF;
3227
3228 END IF;
3229
3230 /*-------------------------------------------------+
3231 | Return the ccid if it was found in the cache. |
3232 +-------------------------------------------------*/
3233 IF FOUND THEN
3234 debug('found ccid ' || l_ccid || ' for concatenated segs: ' ||
3235 p_concat_segments || ' in the cache', MSG_LEVEL_DEBUG);
3236 IF VALID THEN
3237 p_result := TRUE;
3238 print_fcn_label2( 'arp_auto_accounting.get_ccid_from_cache()-' );
3239 RETURN( l_ccid );
3240 ELSE
3241 debug(' ccid ' || l_ccid || ' not valid for date ' ||
3242 validation_date );
3243 p_result := FALSE;
3244 print_fcn_label2('arp_auto_accounting.get_ccid_from_cache()-');
3245 RETURN(NULL);
3246 END IF;
3247 ELSE
3248 debug('Getting concatenated segs: ' ||
3249 p_concat_segments || ' using the flexfield api',
3250 MSG_LEVEL_DEBUG);
3251
3252 IF (get_combination_id(
3253 'SQLGL',
3254 'GL#',
3255 p_system_info.chart_of_accounts_id,
3256 validation_date, -- CCID validation date
3257 p_segment_cnt,
3258 p_segment_table,
3259 l_ccid ) )
3260 THEN
3261
3262 /*---------------------------------------------------+
3263 | Add the ccid to the cache |
3264 | Store in hash table if hash_value does not exist |
3265 | Otherwise store in linear table. |
3266 +---------------------------------------------------*/
3267
3268 IF autoacc_hash_seg_cache.exists(hash_value) then
3269
3270 IF tab_size < MAX_LINEAR_CACHE_SIZE
3271 THEN
3272 tab_size := tab_size + 1;
3273 autoacc_lin_id_cache(tab_size) := l_ccid;
3274 autoacc_lin_seg_cache(tab_size) := p_concat_segments;
3275 autoacc_lin_st_date_cache(tab_size) :=
3276 nvl(fnd_flex_keyval.start_date, g_min_date);
3277 autoacc_lin_end_date_cache(tab_size) :=
3278 nvl(fnd_flex_keyval.end_date, g_max_date);
3279 END IF;
3280 ELSE
3281 IF h_tab_size < MAX_HASH_CACHE_SIZE
3282 THEN
3283 h_tab_size := h_tab_size + 1;
3284 autoacc_hash_id_cache(hash_value) := l_ccid;
3285 autoacc_hash_seg_cache(hash_value) := p_concat_segments;
3286 autoacc_hash_st_date_cache(hash_value) :=
3287 nvl(fnd_flex_keyval.start_date, g_min_date);
3288 autoacc_hash_end_date_cache(hash_value) :=
3289 nvl(fnd_flex_keyval.end_date, g_max_date);
3290 END IF;
3291 END IF;
3292 p_result := TRUE;
3293 print_fcn_label2( 'arp_auto_accounting.get_ccid_from_cache()-' );
3294 RETURN(l_ccid);
3295 ELSE
3296 p_result := FALSE;
3297 print_fcn_label2( 'arp_auto_accounting.get_ccid_from_cache()-' );
3298 RETURN(NULL);
3299 END IF;
3300 END IF;
3301 EXCEPTION
3302 WHEN OTHERS THEN
3303 debug( 'EXCEPTION: arp_auto_accounting.get_ccid_from_cache_cache()',
3304 MSG_LEVEL_BASIC );
3305 debug(SQLERRM, MSG_LEVEL_BASIC);
3306 RAISE;
3307 END;
3308
3309 ----------------------------------------------------------------------------
3310 --
3311 -- FUNCTION NAME: search_glcc_for_ccid
3312 --
3313 -- DECSRIPTION:
3314 -- Retrieves valid GL code combination based on passed segment values.
3315 --
3316 -- ARGUMENTS:
3317 -- IN:
3318 -- p_system_info
3319 -- p_segment_table
3320 -- p_segment_cnt -- # enabled segments
3321 -- p_account_class
3322 -- p_concat_segments
3323 --
3324 -- IN/OUT:
3325 --
3326 -- OUT:
3327 --
3328 -- RETURNS:
3329 -- ccid
3330 --
3331 -- NOTES:
3332 --
3333 -- HISTORY:
3334 -- 14-FEB-97 C. Tomberg Added p_account_class and p_concat_segments params.
3335 --
3336 --
3337 FUNCTION search_glcc_for_ccid( p_system_info IN
3338 arp_trx_global.system_info_rec_type,
3339 p_segment_table IN fnd_flex_ext.SegmentArray,
3340 p_segment_cnt IN BINARY_INTEGER,
3341 p_account_class IN
3342 ra_cust_trx_line_gl_dist.account_class%type,
3343 p_concat_segments IN VARCHAR2 )
3344
3345 RETURN BINARY_INTEGER IS
3346
3347 l_ignore INTEGER;
3348 l_ccid BINARY_INTEGER;
3349 l_detail_posting_flag VARCHAR2(1);
3350 l_summary_flag VARCHAR2(1);
3351 l_bool BOOLEAN;
3352
3353 BEGIN
3354
3355 print_fcn_label2( 'arp_auto_accounting.search_glcc_for_ccid()+' );
3356
3357 --
3358 -- part 1: call the ccid cache or the AOL api to validate and dynamically
3359 -- insert ccid
3360 --
3361
3362
3363 /*------------------------------------------------------------------+
3364 | If the p_concat_segments or p_account_class parameters are |
3365 | null, do not use the cache. |
3366 | This logic exists to maintain backward compatibility with the |
3367 | original function spec. |
3368 | |
3369 | Otherwise, get the ccid from the caches of already validiated |
3370 | code combinations. |
3371 +------------------------------------------------------------------*/
3372
3373 IF (
3374 p_concat_segments IS NOT NULL
3375 AND p_account_class IS NOT NULL)
3376 THEN
3377 l_ccid := Get_Ccid_From_Cache( p_system_info,
3378 p_concat_segments,
3379 p_segment_table,
3380 p_segment_cnt,
3381 p_account_class,
3382 l_bool);
3383 ELSE
3384 l_bool := get_combination_id(
3385 'SQLGL',
3386 'GL#',
3387 p_system_info.chart_of_accounts_id,
3388 validation_date, -- CCID validation date
3389 p_segment_cnt,
3390 p_segment_table,
3391 l_ccid );
3392
3393 END IF;
3394
3395 IF( l_bool = FALSE ) THEN
3396 ------------------------------------------------------------------
3397 -- Failed to retrieve a valid ccid or
3398 -- unable to dynamically create a ccid
3399 ------------------------------------------------------------------
3400 g_error_buffer := fnd_message.get;
3401 debug( g_error_buffer, MSG_LEVEL_BASIC );
3402
3403 print_fcn_label2( 'arp_auto_accounting.search_glcc_for_ccid()-' );
3404 RETURN -1;
3405
3406 END IF;
3407
3408 --
3409 -- part 2: check special validation
3410 -- detail_posting_flag
3411 -- summary_flag
3412 --
3413 BEGIN
3414 /* Bug-2178723 : Caching the values of detail_posting_allowed_flag and summary_flag
3415 in pl/sql table to avoid the high execution count */
3416 IF pg_ar_code_comb_rec.EXISTS(l_ccid) THEN
3417 l_detail_posting_flag := pg_ar_code_comb_rec(l_ccid).detail_posting_flag;
3418 l_summary_flag := pg_ar_code_comb_rec(l_ccid).summary_flag;
3419
3420 ELSE
3421
3422 SELECT detail_posting_allowed_flag,
3423 summary_flag
3424 INTO l_detail_posting_flag,
3425 l_summary_flag
3426 FROM gl_code_combinations
3427 WHERE code_combination_id = l_ccid;
3428
3429 pg_ar_code_comb_rec(l_ccid).detail_posting_flag := l_detail_posting_flag ;
3430 pg_ar_code_comb_rec(l_ccid).summary_flag := l_summary_flag ;
3431
3432 END IF;
3433
3434 IF( l_detail_posting_flag = NO ) THEN
3435
3436 g_error_buffer := MSG_FLEX_POSTING_NOT_ALLOWED;
3437 debug( MSG_FLEX_POSTING_NOT_ALLOWED, MSG_LEVEL_BASIC);
3438 print_fcn_label2( 'arp_auto_accounting.search_glcc_for_ccid()-' );
3439 RETURN -1;
3440
3441 ELSIF( l_summary_flag = YES ) THEN
3442
3443 g_error_buffer := MSG_FLEX_NO_PARENT_ALLOWED;
3444 debug( MSG_FLEX_NO_PARENT_ALLOWED, MSG_LEVEL_BASIC);
3445 print_fcn_label2( 'arp_auto_accounting.search_glcc_for_ccid()-' );
3446 RETURN -1;
3447
3448 END IF;
3449
3450 print_fcn_label2( 'arp_auto_accounting.search_glcc_for_ccid()-' );
3451 RETURN l_ccid;
3452
3453 EXCEPTION
3454 WHEN NO_DATA_FOUND THEN
3455 RETURN -1;
3456 WHEN OTHERS THEN
3457 debug( 'Error in binding ccid_reader', MSG_LEVEL_BASIC );
3458 debug(SQLERRM, MSG_LEVEL_BASIC);
3459 RAISE;
3460 END;
3461
3462 print_fcn_label2( 'arp_auto_accounting.search_glcc_for_ccid()-' );
3463
3464 EXCEPTION
3465 WHEN OTHERS THEN
3466 debug('EXCEPTION: arp_auto_accounting.search_glcc_for_ccid('
3467 || to_char(p_segment_cnt) ||')', MSG_LEVEL_BASIC);
3468 debug(SQLERRM, MSG_LEVEL_BASIC);
3469 RAISE;
3470 END search_glcc_for_ccid;
3471
3472 /*---------------------------------------------------------------------+
3473 | This overloaded version of the function exists to preserve |
3474 | backward compatibility with the original function specification. |
3475 +---------------------------------------------------------------------*/
3476
3477
3478 FUNCTION search_glcc_for_ccid( p_system_info IN
3479 arp_trx_global.system_info_rec_type,
3480 p_segment_table IN fnd_flex_ext.SegmentArray,
3481 p_segment_cnt IN BINARY_INTEGER )
3482 RETURN BINARY_INTEGER IS
3483
3484 BEGIN
3485 RETURN(
3486 search_glcc_for_ccid(
3487 p_system_info,
3488 p_segment_table,
3489 p_segment_cnt,
3490 NULL,
3491 NULL
3492 )
3493 );
3494
3495 END search_glcc_for_ccid;
3496
3497
3498
3499 ----------------------------------------------------------------------------
3500 --
3501 -- FUNCTION NAME: Find_Cursor_In_Cache
3502 --
3503 -- DECSRIPTION:
3504 -- Searches the cursor cache for an open cursor that matches
3505 -- the conditions in the key.
3506 --
3507 -- ARGUMENTS:
3508 -- IN:
3509 -- p_key
3510 --
3511 -- IN/OUT:
3512 --
3513 -- OUT:
3514 --
3515 -- RETURNS:
3516 -- Cursor number
3517 --
3518 -- NOTES:
3519 --
3520 -- HISTORY:
3521 -- 14-FEB-97 C. Tomberg Created
3522 --
3523
3524 FUNCTION Find_Cursor_In_Cache ( p_key IN VARCHAR2 ) RETURN BINARY_INTEGER IS
3525 BEGIN
3526
3527 print_fcn_label2( 'arp_auto_accounting.Find_Cursor_In_Cache()+' );
3528
3529 FOR l_index IN 1..cursor_attr_cache.count LOOP
3530
3531 IF ( cursor_attr_cache(l_index) = p_key )
3532 THEN
3533 print_fcn_label2(
3534 'arp_auto_accounting.Find_Cursor_In_Cache()-' );
3535 RETURN( l_index );
3536 END IF;
3537
3538 END LOOP;
3539
3540
3541 print_fcn_label2( 'arp_auto_accounting.Find_Cursor_In_Cache()-' );
3542
3543 RETURN(NULL);
3544
3545 EXCEPTION
3546 WHEN OTHERS THEN
3547 debug('EXCEPTION: arp_auto_accounting.Find_Cursor_In_Cache()',
3548 MSG_LEVEL_BASIC);
3549 debug(SQLERRM, MSG_LEVEL_BASIC);
3550 RAISE;
3551 END;
3552
3553 ----------------------------------------------------------------------------
3554 --
3555 -- FUNCTION NAME: Get_Select_Cursor
3556 --
3557 -- DECSRIPTION:
3558 -- Returns a cursor for the select statement.
3559 -- Multiple cursors are maintained for different combinations
3560 -- of input parameters. These criteria are encoded in the
3561 -- key which points to the appropriate record in the cursor
3562 -- cache. If the cursor is not found in the cache,
3563 -- a select statement is built and parsed, and the new
3564 -- cursor is added to the cache if the cache is not full.
3565 --
3566 -- ARGUMENTS:
3567 -- IN:
3568 -- p_system_info
3569 -- p_profile_info
3570 -- p_account_class
3571 -- p_customer_trx_id
3572 -- p_customer_trx_line_id
3573 -- p_cust_trx_line_salesrep_id
3574 -- p_request_id
3575 -- p_gl_date
3576 -- p_original_gl_date
3577 -- p_total_trx_amount
3578 -- p_code_combination_id
3579 -- p_force_account_set_no
3580 -- p_cust_trx_type_id
3581 -- p_primary_salesrep_id
3582 -- p_inventory_item_id
3583 -- p_memo_line_id
3584 --
3585 -- IN/OUT:
3586 --
3587 -- OUT:
3588 -- p_keep_cursor_open_flag - if the cursor is in the cache or
3589 -- was added to the cache, don't
3590 -- close it after its first use.
3591 --
3592 -- RETURNS:
3593 -- Cursor number
3594 --
3595 -- NOTES:
3596 --
3597 -- HISTORY:
3598 -- 14-FEB-97 C. Tomberg Created
3599 --
3600
3601 FUNCTION Get_Select_Cursor(
3602 p_system_info IN
3603 arp_trx_global.system_info_rec_type,
3604 p_profile_info IN
3605 arp_trx_global.profile_rec_type,
3606 p_account_class IN VARCHAR2,
3607 p_customer_trx_id IN BINARY_INTEGER,
3608 p_customer_trx_line_id IN BINARY_INTEGER,
3609 p_cust_trx_line_salesrep_id IN BINARY_INTEGER,
3610 p_request_id IN BINARY_INTEGER,
3611 p_gl_date IN DATE,
3612 p_original_gl_date IN DATE,
3613 p_total_trx_amount IN NUMBER,
3614 p_code_combination_id IN BINARY_INTEGER,
3615 p_force_account_set_no IN VARCHAR2,
3616 p_cust_trx_type_id IN BINARY_INTEGER,
3617 p_primary_salesrep_id IN BINARY_INTEGER,
3618 p_inventory_item_id IN BINARY_INTEGER,
3619 p_memo_line_id IN BINARY_INTEGER,
3620 p_keep_cursor_open_flag OUT NOCOPY BOOLEAN )
3621 RETURN BINARY_INTEGER IS
3622
3623 l_select_rec select_rec_type;
3624 l_select_tab select_rec_tab;
3625 l_key VARCHAR2(100);
3626 l_select_c BINARY_INTEGER;
3627 l_cursor_index BINARY_INTEGER;
3628 l_cursor BINARY_INTEGER;
3629 l_ignore INTEGER;
3630
3631 BEGIN
3632
3633 print_fcn_label2( 'arp_auto_accounting.Get_Select_Cursor()+' );
3634
3635 p_keep_cursor_open_flag := TRUE;
3636
3637 /*----------------------------------+
3638 | Construct the cursor cache key |
3639 +----------------------------------*/
3640
3641 l_key := p_account_class || '-';
3642
3643 IF (p_code_combination_id IS NOT NULL)
3644 THEN l_key := l_key || 'Y-';
3645 ELSE l_key := l_key || 'N-';
3646 END IF;
3647
3648 IF (p_gl_date IS NOT NULL)
3649 THEN l_key := l_key || 'Y-';
3650 ELSE l_key := l_key || 'N-';
3651 END IF;
3652
3653 IF (p_request_id IS NOT NULL)
3654 THEN l_key := l_key || 'Y-';
3655 ELSE l_key := l_key || 'N-';
3656 END IF;
3657
3658 IF (p_cust_trx_line_salesrep_id IS NOT NULL)
3659 THEN l_key := l_key || 'Y-';
3660 ELSE l_key := l_key || 'N-';
3661 END IF;
3662
3663 IF (p_customer_trx_id IS NOT NULL)
3664 THEN l_key := l_key || 'Y-';
3665 ELSE l_key := l_key || 'N-';
3666 END IF;
3667
3668 IF (p_customer_trx_line_id IS NOT NULL)
3669 THEN l_key := l_key || 'Y-';
3670 ELSE l_key := l_key || 'N-';
3671 END IF;
3672
3673 /*----------------------------------------------------+
3674 | Attempt to get the cursor from the cursor cache. |
3675 +----------------------------------------------------*/
3676
3677 l_cursor_index := Find_Cursor_In_Cache(l_key);
3678
3679
3680 /*---------------------------------------------------+
3681 | If the cursor was found, return it immediately. |
3682 +---------------------------------------------------*/
3683
3684 IF ( l_cursor_index IS NOT NULL )
3685 THEN
3686
3687 print_fcn_label2( 'arp_auto_accounting.Get_Select_Cursor()-' );
3688
3689 l_cursor := cursor_cache( l_cursor_index );
3690
3691 debug('Found cursor in cache: key ' || l_key ||
3692 ' cursor index: ' || l_cursor_index ||
3693 ' cursor number: ' ||
3694 l_cursor, MSG_LEVEL_DEBUG);
3695
3696 RETURN( l_cursor );
3697 END IF;
3698
3699
3700 /*----------------------------------------------+
3701 | If the cursor was not found in the cache, |
3702 | construct and parse the select statement. |
3703 +----------------------------------------------*/
3704
3705 debug('Reparsing cursor that was not found in the cache. Key: ' ||
3706 l_key,
3707 MSG_LEVEL_DEBUG);
3708
3709
3710 DECLARE
3711 l_select_stmt VARCHAR2(32767);
3712 l_cache_index BINARY_INTEGER;
3713
3714 BEGIN
3715
3716 l_select_c := dbms_sql.open_cursor;
3717
3718 l_cache_index := cursor_attr_cache.count + 1;
3719
3720 /*----------------------------------------------------+
3721 | Add the new cursor to the cache if the cache is |
3722 | not already full. |
3723 +----------------------------------------------------*/
3724
3725 IF ( l_cache_index <= MAX_CURSOR_CACHE_SIZE )
3726 THEN
3727 cursor_attr_cache( l_cache_index ) := l_key;
3728 cursor_cache( l_cache_index ) := l_select_c;
3729
3730 p_keep_cursor_open_flag := TRUE;
3731
3732 ELSE p_keep_cursor_open_flag := FALSE;
3733 END IF;
3734
3735
3736 l_select_stmt := build_select_sql( p_system_info,
3737 p_profile_info,
3738 p_account_class,
3739 p_customer_trx_id,
3740 p_customer_trx_line_id,
3741 p_cust_trx_line_salesrep_id,
3742 p_request_id,
3743 p_gl_date,
3744 p_original_gl_date,
3745 p_total_trx_amount,
3746 p_code_combination_id,
3747 p_force_account_set_no,
3748 p_cust_trx_type_id,
3749 p_primary_salesrep_id,
3750 p_inventory_item_id,
3751 p_memo_line_id );
3752
3753 --
3754 -- add Order By clause
3755 --
3756
3757 l_select_stmt := l_select_stmt || CRLF ||
3758 'ORDER BY 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12';
3759
3760 ------------------------------------------------------------
3761 -- Parse
3762 ------------------------------------------------------------
3763 debug( ' Parsing select stmt', MSG_LEVEL_DEBUG );
3764
3765 dbms_sql.parse( l_select_c, l_select_stmt, dbms_sql.v7);
3766
3767
3768 ------------------------------------------------------------
3769 -- Define Column Arrays
3770 ------------------------------------------------------------
3771 define_arrays( l_select_c, l_select_tab );
3772
3773
3774 EXCEPTION
3775 WHEN OTHERS THEN
3776 debug( 'Error constructing/parsing select cursor',
3777 MSG_LEVEL_BASIC );
3778 debug(SQLERRM, MSG_LEVEL_BASIC);
3779 RAISE;
3780
3781 END;
3782
3783 print_fcn_label2( 'arp_auto_accounting.Get_Select_Cursor()-' );
3784
3785 RETURN( l_select_c );
3786
3787
3788 EXCEPTION
3789 WHEN OTHERS THEN
3790
3791 debug('EXCEPTION: arp_auto_accounting.Get_Select_Cursor()',
3792 MSG_LEVEL_BASIC);
3793 debug(SQLERRM, MSG_LEVEL_BASIC);
3794 RAISE;
3795
3796 END;
3797
3798 ----------------------------------------------------------------------------
3799 --
3800 -- PROCEDURE NAME: Bind_Variable
3801 --
3802 -- DECSRIPTION:
3803 -- Bind a variable into the specified cursor.
3804 -- Ignore the 'ORA-01006 - Bind variable doesd not exist'
3805 -- error.
3806 -- This routine is overloaded to deal with different datatypes.
3807 --
3808 -- ARGUMENTS:
3809 -- IN:
3810 -- p_cursor
3811 -- p_bind_variable
3812 -- p_value
3813 --
3814 -- IN/OUT:
3815 --
3816 -- OUT:
3817 --
3818 -- RETURNS:
3819 --
3820 -- NOTES:
3821 --
3822 -- HISTORY:
3823 -- 14-FEB-97 C. Tomberg Created
3824 --
3825
3826 PROCEDURE Bind_Variable( p_cursor IN INTEGER,
3827 p_bind_variable IN VARCHAR2,
3828 p_value IN VARCHAR2
3829 ) IS
3830 BEGIN
3831 dbms_sql.bind_variable( p_cursor,
3832 p_bind_variable,
3833 p_value );
3834
3835 EXCEPTION
3836 WHEN OTHERS THEN
3837 IF (SQLCODE = -1006)
3838 THEN NULL;
3839 ELSE RAISE;
3840 END IF;
3841
3842 END;
3843
3844 PROCEDURE Bind_Variable( p_cursor IN INTEGER,
3845 p_bind_variable IN VARCHAR2,
3846 p_value IN INTEGER
3847 ) IS
3848 BEGIN
3849 dbms_sql.bind_variable( p_cursor,
3850 p_bind_variable,
3851 p_value );
3852
3853 EXCEPTION
3854 WHEN OTHERS THEN
3855 IF (SQLCODE = -1006)
3856 THEN NULL;
3857 ELSE RAISE;
3858 END IF;
3859
3860 END;
3861
3862
3863 PROCEDURE Bind_Variable( p_cursor IN INTEGER,
3864 p_bind_variable IN VARCHAR2,
3865 p_value IN DATE
3866 ) IS
3867 BEGIN
3868 dbms_sql.bind_variable( p_cursor,
3869 p_bind_variable,
3870 p_value );
3871
3872 EXCEPTION
3873 WHEN OTHERS THEN
3874 IF (SQLCODE = -1006)
3875 THEN NULL;
3876 ELSE RAISE;
3877 END IF;
3878
3879 END;
3880
3881
3882 ----------------------------------------------------------------------------
3883 --
3884 -- PROCEDURE NAME: Bind_All_Variables
3885 --
3886 -- DECSRIPTION:
3887 -- Bind all possible variable into the select cursor.
3888 -- If the cursor is invalid, rebuild and reparse the
3889 -- select statement and pass the new cursor value back
3890 -- to the calling function.
3891 --
3892 -- ARGUMENTS:
3893 -- IN:
3894 -- p_system_info
3895 -- p_profile_info
3896 -- p_account_class
3897 -- p_customer_trx_id
3898 -- p_customer_trx_line_id
3899 -- p_cust_trx_line_salesrep_id
3900 -- p_request_id
3901 -- p_gl_date
3902 -- p_original_gl_date
3903 -- p_total_trx_amount
3904 -- p_code_combination_id
3905 -- p_force_account_set_no
3906 -- p_cust_trx_type_id
3907 -- p_primary_salesrep_id
3908 -- p_inventory_item_id
3909 -- p_memo_line_id
3910 --
3911 -- IN/OUT:
3912 -- p_cursor
3913 --
3914 -- OUT:
3915 -- p_keep_cursor_open_flag
3916 --
3917 -- RETURNS:
3918 --
3919 -- NOTES:
3920 --
3921 -- HISTORY:
3922 -- 14-FEB-97 C. Tomberg Created
3923 --
3924
3925 PROCEDURE Bind_All_Variables(
3926 p_cursor IN OUT NOCOPY BINARY_INTEGER,
3927 p_system_info IN
3928 arp_trx_global.system_info_rec_type,
3929 p_profile_info IN
3930 arp_trx_global.profile_rec_type,
3931 p_account_class IN VARCHAR2,
3932 p_customer_trx_id IN BINARY_INTEGER,
3933 p_customer_trx_line_id IN BINARY_INTEGER,
3934 p_cust_trx_line_salesrep_id IN BINARY_INTEGER,
3935 p_request_id IN BINARY_INTEGER,
3936 p_gl_date IN DATE,
3937 p_original_gl_date IN DATE,
3938 p_total_trx_amount IN NUMBER,
3939 p_code_combination_id IN BINARY_INTEGER,
3940 p_force_account_set_no IN VARCHAR2,
3941 p_cust_trx_type_id IN BINARY_INTEGER,
3942 p_primary_salesrep_id IN BINARY_INTEGER,
3943 p_inventory_item_id IN BINARY_INTEGER,
3944 p_memo_line_id IN BINARY_INTEGER,
3945 p_keep_cursor_open_flag IN OUT NOCOPY BOOLEAN
3946 ) IS
3947
3948 BEGIN
3949
3950 print_fcn_label2( 'arp_auto_accounting.Bind_All_Variables()+' );
3951
3952 BEGIN
3953 Bind_Variable(
3954 p_cursor,
3955 ':force_account_set_no',
3956 p_force_account_set_no
3957 );
3958
3959 EXCEPTION
3960
3961 /*-----------------------------------------------------------+
3962 | If the cursor is invalid, the first bind will fail. |
3963 | in that case, recreate and reparse the SQL statement |
3964 | and continue processing. The new cursor is passed back |
3965 | to the calling routine since it is an IN/OUT parameter. |
3966 +-----------------------------------------------------------*/
3967
3968 WHEN INVALID_CURSOR THEN
3969
3970 debug('Handling INVALID_CURSOR exception by reparsing.',
3971 MSG_LEVEL_DEBUG);
3972
3973 p_cursor := Get_Select_Cursor(
3974 system_info,
3975 profile_info,
3976 p_account_class,
3977 p_customer_trx_id,
3978 p_customer_trx_line_id,
3979 p_cust_trx_line_salesrep_id,
3980 p_request_id,
3981 p_gl_date,
3982 p_original_gl_date,
3983 p_total_trx_amount,
3984 p_code_combination_id,
3985 p_force_account_set_no,
3986 p_cust_trx_type_id,
3987 p_primary_salesrep_id,
3988 p_inventory_item_id,
3989 p_memo_line_id,
3990 p_keep_cursor_open_flag);
3991
3992 Bind_Variable(
3993 p_cursor,
3994 ':force_account_set_no',
3995 p_force_account_set_no
3996 );
3997
3998 WHEN OTHERS THEN RAISE;
3999 END;
4000
4001
4002 Bind_Variable(
4003 p_cursor,
4004 ':total_trx_amount',
4005 p_total_trx_amount
4006 );
4007
4008
4009 Bind_Variable(
4010 p_cursor,
4011 ':original_gl_date',
4012 p_original_gl_date
4013 );
4014
4015 Bind_Variable(
4016 p_cursor,
4017 ':gl_date',
4018 p_gl_date
4019 );
4020
4021
4022 Bind_Variable(
4023 p_cursor,
4024 ':code_combination_id',
4025 p_code_combination_id
4026 );
4027
4028 Bind_Variable(
4029 p_cursor,
4030 ':request_id1',
4031 p_request_id
4032 );
4033
4034
4035 Bind_Variable(
4036 p_cursor,
4037 ':cust_trx_line_salesrep_id',
4038 p_cust_trx_line_salesrep_id
4039 );
4040
4041 Bind_Variable(
4042 p_cursor,
4043 ':request_id',
4044 p_request_id
4045 );
4046
4047
4048 Bind_Variable(
4049 p_cursor,
4050 ':customer_trx_id',
4051 p_customer_trx_id
4052 );
4053
4054
4055 Bind_Variable(
4056 p_cursor,
4057 ':customer_trx_line_id',
4058 p_customer_trx_line_id
4059 );
4060
4061 Bind_Variable(
4062 p_cursor,
4063 ':base_currency',
4064 p_system_info.base_currency
4065 );
4066
4067
4068 print_fcn_label2( 'arp_auto_accounting.Bind_All_Variables()-' );
4069
4070 EXCEPTION
4071 WHEN OTHERS THEN
4072 debug('EXCEPTION: arp_auto_accounting.Bind_All_Variables()',
4073 MSG_LEVEL_BASIC);
4074 debug(SQLERRM, MSG_LEVEL_BASIC);
4075 RAISE;
4076 END;
4077
4078 ----------------------------------------------------------------------------
4079 --
4080 -- PROCEDURE NAME: get_default_ccids
4081 --
4082 -- DECSRIPTION:
4083 -- Gets default ccids for all possible tables which autoaccounting
4084 -- may be based.
4085 --
4086 -- ARGUMENTS:
4087 -- IN:
4088 -- profile_info
4089 -- account_class
4090 -- line_id
4091 -- trx_type_id
4092 -- salesrep_id
4093 -- inv_item_id
4094 -- memo_line_id
4095 --
4096 -- IN/OUT:
4097 -- ccid_record
4098 -- inv_item_type
4099 --
4100 -- OUT:
4101 --
4102 -- NOTES:
4103 --
4104 -- HISTORY:
4105 --
4106 PROCEDURE get_default_ccids( p_profile_info IN
4107 arp_trx_global.profile_rec_type,
4108 p_account_class IN VARCHAR2,
4109 p_line_id IN BINARY_INTEGER,
4110 p_trx_type_id IN BINARY_INTEGER,
4111 p_salesrep_id IN BINARY_INTEGER,
4112 p_inv_item_id IN BINARY_INTEGER,
4113 p_memo_line_id IN BINARY_INTEGER,
4114 p_site_use_id IN BINARY_INTEGER,
4115 p_warehouse_id IN BINARY_INTEGER,
4116 p_ccid_record IN OUT NOCOPY ccid_rec_type,
4117 p_inv_item_type IN OUT NOCOPY
4118 mtl_system_items.item_type%TYPE ) IS
4119
4120
4121 BEGIN
4122 print_fcn_label2( 'arp_auto_accounting.get_default_ccids()+' );
4123
4124 --
4125 -- trx type
4126 --
4127 IF( p_trx_type_id is NOT NULL ) THEN
4128 get_trx_type_ccids( p_trx_type_id,
4129 p_ccid_record.trx_type_ccid_rev,
4130 p_ccid_record.trx_type_ccid_rec,
4131 p_ccid_record.trx_type_ccid_frt,
4132 p_ccid_record.trx_type_ccid_tax,
4133 p_ccid_record.trx_type_ccid_unbill,
4134 p_ccid_record.trx_type_ccid_unearn,
4135 p_ccid_record.trx_type_ccid_suspense );
4136
4137 END IF;
4138
4139 --
4140 -- billing site ccids
4141 --
4142 IF( p_site_use_id is NOT NULL ) THEN
4143 get_site_use_ccids( p_site_use_id,
4144 p_ccid_record.site_use_ccid_rev,
4145 p_ccid_record.site_use_ccid_rec,
4146 p_ccid_record.site_use_ccid_frt,
4147 p_ccid_record.site_use_ccid_tax,
4148 p_ccid_record.site_use_ccid_unbill,
4149 p_ccid_record.site_use_ccid_unearn,
4150 p_ccid_record.site_use_ccid_suspense );
4151
4152 END IF;
4153
4154 --
4155 -- salesrep
4156 --
4157 IF( p_salesrep_id is NOT NULL ) THEN
4158 get_salesrep_ccids( p_salesrep_id,
4159 p_ccid_record.salesrep_ccid_rev,
4160 p_ccid_record.salesrep_ccid_rec,
4161 p_ccid_record.salesrep_ccid_frt );
4162 END IF;
4163
4164 --
4165 -- lineitem
4166 --
4167 IF( p_inv_item_id is NOT NULL ) THEN
4168
4169 get_inv_item_ccids( p_profile_info,
4170 p_inv_item_id,
4171 p_warehouse_id,
4172 p_ccid_record.lineitem_ccid_rev,
4173 p_inv_item_type );
4174
4175 ELSIF( p_memo_line_id is NOT NULL ) THEN
4176
4177 get_memo_line_ccids( p_memo_line_id,
4178 p_ccid_record.lineitem_ccid_rev );
4179 END IF;
4180
4181 --
4182 -- agreement/category
4183 -- Only default if type REV/CHARGES and REV autoacc def
4184 -- is based on table 'AGREEMENT/CATEGORY'
4185 --
4186 IF( p_account_class in (REV, CHARGES) AND
4187 query_autoacc_def( REV, 'AGREEMENT/CATEGORY' ) ) THEN
4188
4189 get_agreecat_ccids( p_profile_info,
4190 p_line_id,
4191 p_warehouse_id, --Bug#1639334
4192 p_ccid_record.agreecat_ccid_rev );
4193 END IF;
4194
4195 print_fcn_label2( 'arp_auto_accounting.get_default_ccids()-' );
4196
4197 EXCEPTION
4198 WHEN OTHERS THEN
4199 debug('EXCEPTION: arp_auto_accounting.get_default_ccids('
4200 || p_account_class || ', '
4201 || to_char(p_line_id) || ', '
4202 || to_char(p_trx_type_id) || ', '
4203 || to_char(p_salesrep_id) || ', '
4204 || to_char(p_inv_item_id) || ', '
4205 || to_char(p_memo_line_id)|| ', '
4206 || to_char(p_site_use_id) || ', '
4207 || to_char(p_warehouse_id)||')', MSG_LEVEL_BASIC);
4208 debug(SQLERRM, MSG_LEVEL_BASIC);
4209 RAISE;
4210 END get_default_ccids;
4211
4212 ----------------------------------------------------------------------------
4213 --
4214 -- PROCEDURE NAME: assemble_code_combination
4215 --
4216 -- DECSRIPTION:
4217 --
4218 --
4219 -- ARGUMENTS:
4220 -- IN:
4221 -- system_info
4222 -- flex_info
4223 -- account_class
4224 -- ccid_record
4225 -- inv_item_type
4226 --
4227 -- IN/OUT:
4228 -- ccid
4229 -- assembled_segments
4230 -- int_ccid (interim tax account ccid)
4231 -- int_concat_segments (interim tax concatenated segments)
4232 -- OUT:
4233 --
4234 -- NOTES:
4235 --
4236 --
4237 -- HISTORY:
4238 --
4239 --
4240 PROCEDURE assemble_code_combination(
4241 p_system_info IN arp_trx_global.system_info_rec_type,
4242 p_flex_info IN arp_trx_global.acct_flex_info_rec_type,
4243 p_account_class IN VARCHAR2,
4244 p_ccid_record IN CCID_REC_TYPE,
4245 p_inv_item_type IN mtl_system_items.item_type%TYPE,
4246 p_ccid IN OUT NOCOPY BINARY_INTEGER,
4247 p_concat_segments IN OUT NOCOPY VARCHAR2,
4248 p_int_ccid IN OUT NOCOPY BINARY_INTEGER,
4249 p_int_concat_segments IN OUT NOCOPY VARCHAR2 ) IS
4250
4251 l_table_offset BINARY_INTEGER;
4252 l_cnt BINARY_INTEGER;
4253 l_concat_segments VARCHAR2(800);
4254 l_int_concat_segments VARCHAR2(800);
4255 l_seg ra_account_default_segments.segment%type;
4256 l_const ra_account_default_segments.constant%type;
4257 l_tbl ra_account_default_segments.table_name%type;
4258 l_ccid BINARY_INTEGER;
4259 l_seg_num BINARY_INTEGER;
4260 l_seg_value gl_code_combinations.segment1%type;
4261 l_int_seg_value gl_code_combinations.segment1%type;
4262 l_delim VARCHAR2(1);
4263
4264 -- to store segment values for binding
4265 --
4266 l_seg_table fnd_flex_ext.SegmentArray;
4267 l_int_seg_table fnd_flex_ext.SegmentArray;
4268
4269 BEGIN
4270
4271 print_fcn_label2( 'arp_auto_accounting.assemble_code_combination()+' );
4272
4273 -- get offset, count for account class (to access plsql tables)
4274 --
4275 IF( p_account_class in (REV, CHARGES) ) then
4276 --
4277 -- Charges uses autoacc definition for Revenue
4278 --
4279 IF( p_inv_item_type = 'FRT' ) then
4280 --
4281 -- use autoacc definition for FREIGHT
4282 -- if inv item is of type 'FRT'
4283 --
4284 l_table_offset := frt_offset;
4285 l_cnt := frt_count;
4286 ELSE
4287 --
4288 -- use autoacc definition for REVENUE
4289 --
4290 l_table_offset := rev_offset;
4291 l_cnt := rev_count;
4292 END IF;
4293 ELSIF( p_account_class = REC ) then
4294 l_table_offset := rec_offset;
4295 l_cnt := rec_count;
4296 ELSIF( p_account_class = FREIGHT ) then
4297 l_table_offset := frt_offset;
4298 l_cnt := frt_count;
4299 ELSIF( p_account_class = TAX ) then
4300 l_table_offset := tax_offset;
4301 l_cnt := tax_count;
4302 ELSIF( p_account_class = UNBILL ) then
4303 l_table_offset := unbill_offset;
4304 l_cnt := unbill_count;
4305 ELSIF( p_account_class = UNEARN ) then
4306 l_table_offset := unearn_offset;
4307 l_cnt := unearn_count;
4308 ELSIF( p_account_class = SUSPENSE ) then
4309 l_table_offset := suspense_offset;
4310 l_cnt := suspense_count;
4311 ELSE
4312 g_error_buffer := 'Invalid account class';
4313 debug( 'EXCEPTION: '||g_error_buffer, MSG_LEVEL_BASIC );
4314 RAISE invalid_account_class;
4315 END IF;
4316
4317 -- loop for each enabled segment
4318 --
4319 FOR i IN 0..l_cnt - 1 LOOP
4320 l_const := autoacc_def_const_t(l_table_offset + i);
4321 l_tbl := autoacc_def_table_t(l_table_offset + i);
4322 l_seg := autoacc_def_segment_t(l_table_offset + i);
4323 l_ccid := -1;
4324
4325 IF( i = 0 ) THEN
4326 l_delim := null;
4327 ELSE
4328 l_delim := p_flex_info.delim;
4329 END IF;
4330
4331 IF( l_const is NOT NULL ) THEN
4332 --
4333 -- constant
4334 --
4335 l_concat_segments := l_concat_segments || l_delim
4336 || l_const;
4337 l_seg_table(i+1) := l_const;
4338
4339 --for deferred tax
4340 l_int_concat_segments := l_int_concat_segments || l_delim
4341 || l_const;
4342 l_int_seg_table(i+1) := l_const;
4343
4344 ELSIF( l_tbl is NOT NULL ) THEN
4345 --
4346 -- table-based
4347 --
4348 IF( l_tbl = 'RA_CUST_TRX_TYPES' ) THEN
4349 --
4350 -- For all account classes except REC
4351 --
4352 IF p_account_class in (REV, CHARGES) THEN
4353
4354 IF( p_inv_item_type = 'FRT' ) THEN
4355 --
4356 -- use autoacc definition for FREIGHT
4357 -- if inv item is of type 'FRT'
4358 --
4359 l_ccid := p_ccid_record.trx_type_ccid_frt;
4360 ELSE
4361 l_ccid := p_ccid_record.trx_type_ccid_rev;
4362 END IF;
4363 ELSIF p_account_class = REC THEN
4364 l_ccid := p_ccid_record.trx_type_ccid_rec;
4365 ELSIF p_account_class = FREIGHT THEN
4366 l_ccid := p_ccid_record.trx_type_ccid_frt;
4367 ELSIF p_account_class = TAX THEN
4368 l_ccid := p_ccid_record.trx_type_ccid_tax;
4369 ELSIF p_account_class = UNBILL THEN
4370 l_ccid := p_ccid_record.trx_type_ccid_unbill;
4371 ELSIF p_account_class = UNEARN THEN
4372 l_ccid := p_ccid_record.trx_type_ccid_unearn;
4373 ELSIF p_account_class = SUSPENSE THEN
4374 l_ccid := p_ccid_record.trx_type_ccid_suspense;
4375 END IF;
4376
4377 ELSIF( l_tbl = 'RA_SITE_USES' ) THEN
4378 --
4379 -- For all account classes except REC
4380 --
4381 IF p_account_class in (REV, CHARGES) THEN
4382
4383 IF( p_inv_item_type = 'FRT' ) THEN
4384 --
4385 -- use autoacc definition for FREIGHT
4386 -- if inv item is of type 'FRT'
4387 --
4388 l_ccid := p_ccid_record.site_use_ccid_frt;
4389 ELSE
4390 l_ccid := p_ccid_record.site_use_ccid_rev;
4391 END IF;
4392 ELSIF p_account_class = REC THEN
4393 l_ccid := p_ccid_record.site_use_ccid_rec;
4394 ELSIF p_account_class = FREIGHT THEN
4395 l_ccid := p_ccid_record.site_use_ccid_frt;
4396 ELSIF p_account_class = TAX THEN
4397 l_ccid := p_ccid_record.site_use_ccid_tax;
4398 ELSIF p_account_class = UNBILL THEN
4399 l_ccid := p_ccid_record.site_use_ccid_unbill;
4400 ELSIF p_account_class = UNEARN THEN
4401 l_ccid := p_ccid_record.site_use_ccid_unearn;
4402 ELSIF p_account_class = SUSPENSE THEN
4403 l_ccid := p_ccid_record.site_use_ccid_suspense;
4404 END IF;
4405
4406 ELSIF( l_tbl = 'RA_SALESREPS' ) THEN
4407 --
4408 -- For all account classes
4409 --
4410 IF p_account_class in (REV, CHARGES) THEN
4411
4412 IF( p_inv_item_type = 'FRT' ) THEN
4413 --
4414 -- use autoacc definition for FREIGHT
4415 -- if inv item is of type 'FRT'
4416 --
4417 l_ccid := p_ccid_record.salesrep_ccid_frt;
4418 ELSE
4419 l_ccid := p_ccid_record.salesrep_ccid_rev;
4420 END IF;
4421
4422 /* Bug 2396754 - swapped salesrep_ccid_rec
4423 for salesrep_ccid_rev for UNBILL - it was
4424 an apparent typo */
4425
4426 ELSIF p_account_class = REC THEN
4427 l_ccid := p_ccid_record.salesrep_ccid_rec;
4428 ELSIF p_account_class = FREIGHT THEN
4429 l_ccid := p_ccid_record.salesrep_ccid_frt;
4430 ELSIF p_account_class = TAX THEN
4431 l_ccid := p_ccid_record.salesrep_ccid_rev;
4432 ELSIF p_account_class = UNBILL THEN
4433 l_ccid := p_ccid_record.salesrep_ccid_rev;
4434 ELSIF p_account_class = UNEARN THEN
4435 l_ccid := p_ccid_record.salesrep_ccid_rev;
4436 ELSIF p_account_class = SUSPENSE THEN
4437 l_ccid := p_ccid_record.salesrep_ccid_rev;
4438 END IF;
4439 ELSIF( l_tbl = 'RA_STD_TRX_LINES' ) THEN
4440 --
4441 -- For all account classes except REC
4442 --
4443 IF p_account_class in (REV, CHARGES) THEN
4444 l_ccid := p_ccid_record.lineitem_ccid_rev;
4445 ELSIF p_account_class = FREIGHT THEN
4446 l_ccid := p_ccid_record.lineitem_ccid_rev;
4447 ELSIF p_account_class = TAX THEN
4448 l_ccid := p_ccid_record.lineitem_ccid_rev;
4449 ELSIF p_account_class = UNBILL THEN
4450 l_ccid := p_ccid_record.lineitem_ccid_rev;
4451 ELSIF p_account_class = UNEARN THEN
4452 l_ccid := p_ccid_record.lineitem_ccid_rev;
4453 ELSIF p_account_class = SUSPENSE THEN
4454 l_ccid := p_ccid_record.lineitem_ccid_rev;
4455 END IF;
4456 ELSIF( l_tbl = 'RA_TAXES' ) THEN
4457 --
4458 -- For TAX account class only
4459 --
4460 IF p_account_class = TAX THEN
4461 l_ccid := p_ccid_record.tax_ccid_tax;
4462 END IF;
4463 ELSIF( l_tbl = 'AGREEMENT/CATEGORY' ) THEN
4464 --
4465 -- For REV, CHARGES account classes
4466 --
4467 IF p_account_class in (REV, CHARGES) THEN
4468 l_ccid := p_ccid_record.agreecat_ccid_rev;
4469 END IF;
4470 ELSE
4471 g_error_buffer := 'Invalid table name: '||l_tbl;
4472 debug( 'EXCEPTION: '||g_error_buffer, MSG_LEVEL_BASIC );
4473 RAISE invalid_table_name;
4474 END IF;
4475
4476 l_seg_num := TO_NUMBER(SUBSTRB(l_seg, LENGTHB('SEGMENT') + 1));
4477
4478 -- Only get segment if have valid ccid
4479 --
4480 IF( l_ccid = -1 ) THEN
4481 l_seg_value := INVALID_SEGMENT;
4482 ELSE
4483 l_seg_value := get_segment_from_glcc( l_ccid, l_seg_num );
4484
4485 IF( l_seg_value IS NULL ) THEN
4486 --
4487 -- assign invalid segment value if no data found
4488 --
4489 l_seg_value := INVALID_SEGMENT;
4490 END IF;
4491 END IF;
4492
4493 l_concat_segments := l_concat_segments || l_delim || l_seg_value;
4494 l_seg_table(i+1) := l_seg_value;
4495
4496 --
4497 -- Derive the interim tax account segments for deferred tax
4498 --
4499 IF (p_account_class = 'TAX')
4500 AND (p_ccid_record.interim_tax_ccid IS NOT NULL) THEN
4501
4502 IF l_tbl = 'RA_TAXES' THEN
4503 -- Only get segment if have valid ccid
4504 --
4505 IF( p_ccid_record.interim_tax_ccid = -1) THEN
4506 l_int_seg_value := INVALID_SEGMENT;
4507 ELSE
4508 l_int_seg_value :=
4509 get_segment_from_glcc( p_ccid_record.interim_tax_ccid, l_seg_num);
4510
4511 IF ( l_int_seg_value IS NULL ) THEN
4512 --
4513 -- assign invalid segment value if no data found
4514 --
4515 l_int_seg_value := INVALID_SEGMENT;
4516 END IF;
4517 END IF;
4518
4519 ELSE
4520 l_int_seg_value := l_seg_value;
4521 END IF;
4522
4523 l_int_concat_segments := l_int_concat_segments || l_delim || l_int_seg_value;
4524 l_int_seg_table(i+1) := l_int_seg_value;
4525
4526 END IF; -- if account class is TAX
4527
4528 END IF; -- if const is not null
4529 END LOOP;
4530
4531 -- call ccid reader
4532 p_ccid := search_glcc_for_ccid(
4533 system_info,
4534 l_seg_table,
4535 l_cnt,
4536 p_account_class,
4537 l_concat_segments );
4538
4539 -- return concat segs, and ccid
4540 p_concat_segments := l_concat_segments;
4541
4542 -- call ccid reader for interim tax account
4543 IF (p_account_class = 'TAX')
4544 AND (p_ccid_record.interim_tax_ccid IS NOT NULL) THEN
4545 p_int_ccid := search_glcc_for_ccid(
4546 system_info,
4547 l_int_seg_table,
4548 l_cnt,
4549 p_account_class,
4550 l_int_concat_segments );
4551
4552 -- return concat segs, and ccid for interim tax account
4553 p_int_concat_segments := l_int_concat_segments;
4554 END IF;
4555
4556 print_fcn_label2( 'arp_auto_accounting.assemble_code_combination()-' );
4557
4558 EXCEPTION
4559 WHEN OTHERS THEN
4560 debug('EXCEPTION: arp_auto_accounting.assemble_code_combination('
4561 || p_account_class || ')', MSG_LEVEL_BASIC);
4562 debug(SQLERRM, MSG_LEVEL_BASIC);
4563 RAISE;
4564 END assemble_code_combination;
4565
4566 ----------------------------------------------------------------------------
4567 --
4568 -- PROCEDURE NAME: flex_manager
4569 --
4570 -- DECSRIPTION:
4571 -- Entry point for flexfield assembly.
4572 --
4573 -- ARGUMENTS:
4574 -- IN:
4575 -- account_class
4576 -- line_id
4577 -- trx_type_id
4578 -- salesrep_id
4579 -- inv_item_id
4580 -- memo_line_id
4581 -- site_use_id
4582 -- warehouse_id
4583 -- ccid_tax
4584 --
4585 -- IN/OUT:
4586 -- ccid
4587 -- concat_segments
4588 -- int_ccid (Interim tax account ccid)
4589 -- int_concat_segments (Interim tax account concatenated segments)
4590 --
4591 -- RETURNS:
4592 -- 1 if success, 0 otherwise
4593 --
4594 -- NOTES:
4595 --
4596 -- HISTORY:
4597 --
4598 PROCEDURE flex_manager( p_account_class IN VARCHAR2,
4599 p_line_id IN BINARY_INTEGER,
4600 p_trx_type_id IN BINARY_INTEGER,
4601 p_salesrep_id IN BINARY_INTEGER,
4602 p_inv_item_id IN BINARY_INTEGER,
4603 p_memo_line_id IN BINARY_INTEGER,
4604 p_ccid_tax IN BINARY_INTEGER,
4605 p_int_ccid_tax IN BINARY_INTEGER,
4606 p_site_use_id IN BINARY_INTEGER,
4607 p_warehouse_id IN BINARY_INTEGER,
4608 p_ccid IN OUT NOCOPY BINARY_INTEGER,
4609 p_concat_segments IN OUT NOCOPY VARCHAR2,
4610 p_int_ccid IN OUT NOCOPY BINARY_INTEGER,
4611 p_int_concat_segments IN OUT NOCOPY VARCHAR2 ) IS
4612
4613 l_ccid_record ccid_rec_type;
4614 l_inv_item_type mtl_system_items.item_type%TYPE;
4615
4616 PROCEDURE print_params IS
4617 BEGIN
4618 debug('EXCEPTION: arp_auto_accounting.flex_manager('
4619 || p_account_class || ', '
4620 || to_char(p_line_id) || ', '
4621 || to_char(p_trx_type_id) || ', '
4622 || to_char(p_salesrep_id) || ', '
4623 || to_char(p_inv_item_id) || ', '
4624 || to_char(p_memo_line_id) || ', '
4625 || to_char(p_ccid_tax) || ', '
4626 || to_char(p_int_ccid_tax) || ', '
4627 || to_char(p_site_use_id) || ', '
4628 || to_char(p_warehouse_id) || ')',
4629 MSG_LEVEL_DEBUG);
4630
4631 END;
4632
4633 BEGIN
4634
4635 print_fcn_label( 'arp_auto_accounting.flex_manager()+' );
4636
4637 debug( ' account_class='||p_account_class, MSG_LEVEL_DEBUG );
4638 debug( ' line_id='||to_char(p_line_id), MSG_LEVEL_DEBUG );
4639 debug( ' trx_type_id='||to_char(p_trx_type_id), MSG_LEVEL_DEBUG );
4640 debug( ' salesrep_id='||to_char(p_salesrep_id), MSG_LEVEL_DEBUG );
4641 debug( ' inv_item_id='||to_char(p_inv_item_id), MSG_LEVEL_DEBUG );
4642 debug( ' memo_line_id='||to_char(p_memo_line_id), MSG_LEVEL_DEBUG );
4643 debug( ' ccid_tax='||to_char(p_ccid_tax), MSG_LEVEL_DEBUG );
4644 debug( ' int_ccid_tax='||to_char(p_int_ccid_tax), MSG_LEVEL_DEBUG );
4645 debug( ' site_use_id='||to_char(p_site_use_id), MSG_LEVEL_DEBUG );
4646 debug( ' warehouse_id='||to_char(p_warehouse_id), MSG_LEVEL_DEBUG );
4647
4648 --
4649 -- Initialize
4650 --
4651 p_concat_segments := NULL;
4652 p_ccid := -1;
4653
4654 --
4655 -- deferred tax is optional so initialize interim tax to null
4656 --
4657
4658 p_int_concat_segments := NULL;
4659 p_int_ccid := NULL;
4660
4661 --
4662 -- Validate inv_item_id, memo_line_id: at least one must be NULL
4663 --
4664 IF( p_inv_item_id is NOT NULL AND p_memo_line_id is NOT NULL ) THEN
4665 --
4666 -- error condition
4667 g_error_buffer := 'Either item id or memo line id must be null';
4668 debug( 'EXCEPTION: '||g_error_buffer, MSG_LEVEL_BASIC );
4669 RAISE item_and_memo_both_not_null;
4670 END IF;
4671
4672 --
4673 --
4674 --
4675 get_default_ccids( profile_info,
4676 p_account_class,
4677 p_line_id,
4678 p_trx_type_id,
4679 p_salesrep_id,
4680 p_inv_item_id,
4681 p_memo_line_id,
4682 p_site_use_id,
4683 p_warehouse_id,
4684 l_ccid_record,
4685 l_inv_item_type );
4686
4687 --
4688 -- Add tax ccid to the ccid record
4689 --
4690 IF( p_ccid_tax is null) THEN
4691 l_ccid_record.tax_ccid_tax := -1;
4692 ELSE
4693 l_ccid_record.tax_ccid_tax := p_ccid_tax;
4694 END IF;
4695
4696 --
4697 -- Add interim tax ccid to the ccid record values null or ccid
4698 --
4699
4700 l_ccid_record.interim_tax_ccid := p_int_ccid_tax;
4701
4702 -- Dump ccid record, item type
4703 --
4704 dump_ccid_record( l_ccid_record );
4705
4706 debug( ' inv_item_type='||l_inv_item_type, MSG_LEVEL_DEBUG );
4707
4708 --
4709 -- Assemble segments and get ccid
4710 --
4711 assemble_code_combination( system_info,
4712 flex_info,
4713 p_account_class,
4714 l_ccid_record,
4715 l_inv_item_type,
4716 p_ccid,
4717 p_concat_segments,
4718 p_int_ccid,
4719 p_int_concat_segments );
4720
4721
4722 debug( ' ccid= '||to_char(p_ccid), MSG_LEVEL_DEBUG );
4723 debug( ' concat_segs= '||p_concat_segments, MSG_LEVEL_DEBUG );
4724
4725 debug( ' interim_tax_ccid= '||to_char(p_int_ccid), MSG_LEVEL_DEBUG );
4726 debug( ' interim_tax_concat_segs= '||p_int_concat_segments, MSG_LEVEL_DEBUG );
4727
4728 print_fcn_label( 'arp_auto_accounting.flex_manager()-' );
4729
4730 EXCEPTION
4731 WHEN OTHERS THEN
4732 print_params;
4733 debug(SQLERRM, MSG_LEVEL_BASIC);
4734 RAISE;
4735
4736 END flex_manager;
4737
4738
4739 ----------------------------------------------------------------------------
4740 --
4741 -- FUNCTION NAME: build_delete_sql
4742 --
4743 -- DECSRIPTION:
4744 --
4745 -- ARGUMENTS:
4746 -- IN:
4747 -- system_info
4748 -- profile_info
4749 -- account_class
4750 -- customer_trx_id
4751 -- customer_trx_line_id
4752 -- cust_trx_line_salesrep_id
4753 -- request_id
4754 --
4755 -- IN/OUT:
4756 --
4757 -- OUT:
4758 --
4759 -- RETURNS:
4760 -- delete sql
4761 --
4762 -- NOTES:
4763 --
4764 -- HISTORY:
4765 --
4766 FUNCTION build_delete_sql( p_system_info IN
4767 arp_trx_global.system_info_rec_type,
4768 p_profile_info IN
4769 arp_trx_global.profile_rec_type,
4770 p_account_class IN VARCHAR2,
4771 p_customer_trx_id IN BINARY_INTEGER,
4772 p_customer_trx_line_id IN BINARY_INTEGER,
4773 p_cust_trx_line_salesrep_id IN BINARY_INTEGER,
4774 p_request_id IN BINARY_INTEGER )
4775 RETURN VARCHAR2 IS
4776
4777 l_delete_stmt VARCHAR2(1000);
4778 l_account_class_pred VARCHAR2(500);
4779 l_request_id_pred VARCHAR2(500);
4780 l_ctid_pred VARCHAR2(500);
4781 l_ctlid_pred VARCHAR2(500);
4782 l_ctlsid_pred VARCHAR2(500);
4783
4784 BEGIN
4785 print_fcn_label( 'arp_auto_accounting.build_delete_sql()+' );
4786
4787 --
4788 -- account_class
4789 --
4790 IF( p_account_class = REV ) THEN
4791 l_account_class_pred := CRLF ||
4792 'AND account_class in (''REV'', ''UNBILL'', ''UNEARN'')';
4793 ELSE
4794 l_account_class_pred := CRLF ||
4795 'AND account_class = ''' || p_account_class || '''';
4796 END IF;
4797
4798 --
4799 -- request_id
4800 --
4801 IF( p_request_id IS NOT NULL ) THEN
4802 l_request_id_pred := CRLF ||
4803 'AND request_id = ' || to_char( p_request_id );
4804 END IF;
4805
4806 --
4807 -- customer_trx_id
4808 --
4809 IF( p_customer_trx_id IS NOT NULL ) THEN
4810 l_ctid_pred := CRLF ||
4811 'AND customer_trx_id = ' || to_char( p_customer_trx_id );
4812 END IF;
4813
4814 --
4815 -- customer_trx_line_id
4816 --
4817 IF( p_customer_trx_line_id IS NOT NULL ) THEN
4818 l_ctlid_pred := CRLF ||
4819 'AND customer_trx_line_id in
4820 (
4821 SELECT customer_trx_line_id
4822 FROM ra_customer_trx_lines ctl
4823 WHERE (ctl.customer_trx_line_id = '
4824 || to_char( p_customer_trx_line_id ) || CRLF ||
4825 ' or ctl.link_to_cust_trx_line_id = '
4826 || to_char( p_customer_trx_line_id ) || ')' || CRLF ||
4827 ')';
4828 END IF;
4829
4830 --
4831 -- salesrep_id
4832 --
4833
4834 IF( p_cust_trx_line_salesrep_id IS NOT NULL ) THEN
4835
4836 /* Bug 2524140 - When REV accounts based on salesrep,
4837 autoaccounting creating multiple TAX rows
4838 with alternating +100/-100 percentages. */
4839
4840 IF ((p_account_class = 'TAX' AND
4841 p_system_info.tax_based_on_salesrep) OR
4842 p_account_class <> 'TAX') THEN
4843
4844 l_ctlsid_pred := CRLF ||
4845 'AND cust_trx_line_salesrep_id = ' || to_char(p_cust_trx_line_salesrep_id ) ;
4846
4847 END IF;
4848
4849 END IF;
4850
4851 --
4852 -- Construct the Delete Statement
4853 --
4854 l_delete_stmt :=
4855 'DELETE from ra_cust_trx_line_gl_dist gd
4856 WHERE gl_posted_date is null'
4857 || l_account_class_pred
4858 || l_request_id_pred
4859 || l_ctid_pred
4860 || l_ctlid_pred
4861 || l_ctlsid_pred
4862 || CRLF ||
4863 'AND account_set_flag = (SELECT decode(ct.invoicing_rule_id,
4864 NULL, ''N'',
4865 ''Y'')
4866 FROM ra_customer_trx ct
4867 WHERE ct.customer_trx_id = gd.customer_trx_id)';
4868
4869
4870 debug( l_delete_stmt, MSG_LEVEL_DEBUG );
4871 debug( ' len(l_delete_stmt)=' || to_char(LENGTHB(l_delete_stmt)),
4872 MSG_LEVEL_DEBUG );
4873
4874 print_fcn_label( 'arp_auto_accounting.build_delete_sql()-' );
4875
4876 RETURN l_delete_stmt;
4877
4878
4879 EXCEPTION
4880 WHEN OTHERS THEN
4881 debug('EXCEPTION: arp_auto_accounting.build_delete_sql()',
4882 MSG_LEVEL_BASIC);
4883 debug(SQLERRM, MSG_LEVEL_BASIC);
4884 RAISE;
4885 END build_delete_sql;
4886
4887
4888 ----------------------------------------------------------------------------
4889 PROCEDURE get_column_values( p_select_c IN INTEGER,
4890 p_select_rec OUT NOCOPY select_rec_type ) IS
4891 BEGIN
4892 print_fcn_label2( 'arp_auto_accounting.get_column_values()+' );
4893
4894 dbms_sql.column_value( p_select_c, 1, p_select_rec.customer_trx_id );
4895 dbms_sql.column_value( p_select_c, 2, p_select_rec.customer_trx_line_id );
4896 dbms_sql.column_value( p_select_c, 3,
4897 p_select_rec.cust_trx_line_salesrep_id );
4898 dbms_sql.column_value( p_select_c, 4, p_select_rec.line_amount );
4899 dbms_sql.column_value( p_select_c, 5,
4900 p_select_rec.accounted_line_amount );
4901 dbms_sql.column_value( p_select_c, 6, p_select_rec.percent );
4902 dbms_sql.column_value( p_select_c, 7, p_select_rec.amount );
4903 dbms_sql.column_value( p_select_c, 8, p_select_rec.acctd_amount );
4904 dbms_sql.column_value( p_select_c, 9, p_select_rec.account_class );
4905 dbms_sql.column_value( p_select_c, 10, p_select_rec.account_set_flag );
4906 dbms_sql.column_value( p_select_c, 11, p_select_rec.cust_trx_type_id );
4907 dbms_sql.column_value( p_select_c, 12,
4908 p_select_rec.allow_not_open_flag );
4909 dbms_sql.column_value( p_select_c, 13,
4910 p_select_rec.concatenated_segments );
4911 dbms_sql.column_value( p_select_c, 14, p_select_rec.code_combination_id );
4912 dbms_sql.column_value( p_select_c, 15, p_select_rec.gl_date );
4913 dbms_sql.column_value( p_select_c, 16, p_select_rec.original_gl_date );
4914 dbms_sql.column_value( p_select_c, 17, p_select_rec.ussgl_trx_code );
4915 dbms_sql.column_value( p_select_c, 18,
4916 p_select_rec.ussgl_trx_code_context );
4917 dbms_sql.column_value( p_select_c, 19, p_select_rec.salesrep_id );
4918 dbms_sql.column_value( p_select_c, 20, p_select_rec.inventory_item_id );
4919 dbms_sql.column_value( p_select_c, 21, p_select_rec.memo_line_id );
4920 dbms_sql.column_value( p_select_c, 22, p_select_rec.default_tax_ccid );
4921 dbms_sql.column_value( p_select_c, 23, p_select_rec.interim_tax_ccid );
4922 dbms_sql.column_value( p_select_c, 24, p_select_rec.site_use_id );
4923 dbms_sql.column_value( p_select_c, 25, p_select_rec.warehouse_id );
4924 -- 1651593
4925 dbms_sql.column_value( p_select_c, 26, p_select_rec.link_to_cust_trx_line_id);
4926
4927 /* 5148504 - return null value when interim_tax_ccid comes
4928 back from SELECT as -1 */
4929 IF p_select_rec.interim_tax_ccid = -1
4930 THEN
4931 p_select_rec.interim_tax_ccid := NULL;
4932 END IF;
4933
4934 print_fcn_label2( 'arp_auto_accounting.get_column_values()-' );
4935 EXCEPTION
4936 WHEN OTHERS THEN
4937 debug('EXCEPTION: arp_auto_accounting.get_column_values()',
4938 MSG_LEVEL_BASIC);
4939 debug(SQLERRM, MSG_LEVEL_BASIC);
4940 RAISE;
4941 END get_column_values;
4942
4943 ----------------------------------------------------------------------------
4944 PROCEDURE get_column_values( p_select_c IN INTEGER,
4945 p_select_tab OUT NOCOPY select_rec_tab ) IS
4946 BEGIN
4947 print_fcn_label2( 'arp_auto_accounting.get_column_values(tab)+' );
4948
4949 dbms_sql.column_value( p_select_c, 1, p_select_tab.customer_trx_id );
4950 dbms_sql.column_value( p_select_c, 2, p_select_tab.customer_trx_line_id );
4951 dbms_sql.column_value( p_select_c, 3,
4952 p_select_tab.cust_trx_line_salesrep_id );
4953 dbms_sql.column_value( p_select_c, 4, p_select_tab.line_amount );
4954 dbms_sql.column_value( p_select_c, 5,
4955 p_select_tab.accounted_line_amount );
4956 dbms_sql.column_value( p_select_c, 6, p_select_tab.percent );
4957 dbms_sql.column_value( p_select_c, 7, p_select_tab.amount );
4958 dbms_sql.column_value( p_select_c, 8, p_select_tab.acctd_amount );
4959 dbms_sql.column_value( p_select_c, 9, p_select_tab.account_class );
4960 dbms_sql.column_value( p_select_c, 10, p_select_tab.account_set_flag );
4961 dbms_sql.column_value( p_select_c, 11, p_select_tab.cust_trx_type_id );
4962 dbms_sql.column_value( p_select_c, 12,
4963 p_select_tab.allow_not_open_flag );
4964 dbms_sql.column_value( p_select_c, 13,
4965 p_select_tab.concatenated_segments );
4966 dbms_sql.column_value( p_select_c, 14, p_select_tab.code_combination_id );
4967 dbms_sql.column_value( p_select_c, 15, p_select_tab.gl_date );
4968 dbms_sql.column_value( p_select_c, 16, p_select_tab.original_gl_date );
4969 dbms_sql.column_value( p_select_c, 17, p_select_tab.ussgl_trx_code );
4970 dbms_sql.column_value( p_select_c, 18,
4971 p_select_tab.ussgl_trx_code_context );
4972 dbms_sql.column_value( p_select_c, 19, p_select_tab.salesrep_id );
4973 dbms_sql.column_value( p_select_c, 20, p_select_tab.inventory_item_id );
4974 dbms_sql.column_value( p_select_c, 21, p_select_tab.memo_line_id );
4975 dbms_sql.column_value( p_select_c, 22, p_select_tab.default_tax_ccid );
4976 dbms_sql.column_value( p_select_c, 23, p_select_tab.interim_tax_ccid );
4977 dbms_sql.column_value( p_select_c, 24, p_select_tab.site_use_id );
4978 dbms_sql.column_value( p_select_c, 25, p_select_tab.warehouse_id );
4979 -- 1651593
4980 dbms_sql.column_value( p_select_c, 26, p_select_tab.link_to_cust_trx_line_id);
4981
4982 print_fcn_label2( 'arp_auto_accounting.get_column_values(tab)-' );
4983 EXCEPTION
4984 WHEN OTHERS THEN
4985 debug('EXCEPTION: arp_auto_accounting.get_column_values()',
4986 MSG_LEVEL_BASIC);
4987 debug(SQLERRM, MSG_LEVEL_BASIC);
4988 RAISE;
4989 END get_column_values;
4990
4991
4992 ----------------------------------------------------------------------------
4993 PROCEDURE correct_rounding_errors( select_record IN OUT NOCOPY select_rec_type,
4994 total_percent IN OUT NOCOPY NUMBER,
4995 total_amount IN OUT NOCOPY NUMBER,
4996 total_acctd_amount IN OUT NOCOPY NUMBER) IS
4997 BEGIN
4998 print_fcn_label2( 'arp_auto_accounting.correct_rounding_errors()+' );
4999
5000 -- update totals
5001
5002 total_percent := total_percent + select_record.percent;
5003 total_amount := total_amount + select_record.amount;
5004 total_acctd_amount := total_acctd_amount + select_record.acctd_amount;
5005
5006 -- check total percent
5007 IF( total_percent = 100 ) THEN
5008
5009 -- entered amount
5010 select_record.amount := select_record.amount +
5011 ( select_record.line_amount - total_amount );
5012 total_amount := 0;
5013 total_percent := 0;
5014
5015 -- acctd amount
5016 select_record.acctd_amount :=
5017 select_record.acctd_amount +
5018 ( select_record.accounted_line_amount -
5019 total_acctd_amount );
5020 total_acctd_amount := 0;
5021
5022 ELSIF( total_percent = 0 ) THEN
5023
5024 -- entered amount
5025 select_record.amount := select_record.amount - total_amount;
5026 total_amount := 0;
5027 total_percent := 0;
5028
5029 -- acctd amount
5030 select_record.acctd_amount := select_record.acctd_amount -
5031 total_acctd_amount;
5032 total_acctd_amount := 0;
5033
5034 END IF;
5035
5036 print_fcn_label2( 'arp_auto_accounting.correct_rounding_errors()-' );
5037
5038 EXCEPTION
5039 WHEN OTHERS THEN
5040 debug('EXCEPTION: arp_auto_accounting.correct_rounding_errors()',
5041 MSG_LEVEL_BASIC);
5042 debug(SQLERRM, MSG_LEVEL_BASIC);
5043 RAISE;
5044 END correct_rounding_errors;
5045
5046
5047 ----------------------------------------------------------------------------
5048 PROCEDURE insert_dist_row( p_system_info IN
5049 arp_trx_global.system_info_rec_type,
5050 p_profile_info IN
5051 arp_trx_global.profile_rec_type,
5052 p_request_id IN BINARY_INTEGER,
5053 p_select_tab IN select_rec_tab,
5054 p_low IN NUMBER,
5055 p_high IN NUMBER ) IS
5056
5057 l_gl_dist_key_value_list gl_ca_utility_pkg.r_key_value_arr; /* mrc */
5058 --Bug#2750340
5059 l_xla_event arp_xla_events.xla_events_type;
5060 inext NUMBER := 0;
5061 inow NUMBER := 0;
5062
5063 BEGIN
5064 print_fcn_label2( 'arp_auto_accounting.insert_dist_row()+' );
5065
5066 /* Bug 2560036 - modified insert to set rec_offset_flag in
5067 support of directly inserted UNEARN rows for RAM-C */
5068
5069 FORALL i IN p_low..p_high
5070 INSERT into ra_cust_trx_line_gl_dist
5071 (
5072 cust_trx_line_gl_dist_id,
5073 created_by,
5074 creation_date,
5075 last_updated_by,
5076 last_update_date,
5077 set_of_books_id,
5078 request_id,
5079 customer_trx_id,
5080 customer_trx_line_id,
5081 cust_trx_line_salesrep_id,
5082 percent,
5083 amount,
5084 acctd_amount,
5085 account_class,
5086 account_set_flag,
5087 concatenated_segments,
5088 code_combination_id,
5089 gl_date,
5090 original_gl_date,
5091 ussgl_transaction_code,
5092 ussgl_transaction_code_context,
5093 posting_control_id,
5094 latest_rec_flag,
5095 collected_tax_concat_seg,
5096 collected_tax_ccid,
5097 rec_offset_flag
5098 ,org_id
5099 )
5100 VALUES
5101 (
5102 ra_cust_trx_line_gl_dist_s.nextval,
5103 p_profile_info.user_id,
5104 sysdate,
5105 p_profile_info.user_id,
5106 sysdate,
5107 p_system_info.system_parameters.set_of_books_id,
5108 p_request_id,
5109 p_select_tab.customer_trx_id(i),
5110 p_select_tab.customer_trx_line_id(i),
5111 p_select_tab.cust_trx_line_salesrep_id(i),
5112 round(nvl(p_select_tab.percent(i), 0), 4),
5113 decode(p_select_tab.account_set_flag(i),
5114 'Y', null, p_select_tab.amount(i)),
5115 decode(p_select_tab.account_set_flag(i),
5116 'Y', null, p_select_tab.acctd_amount(i)),
5117 p_select_tab.account_class(i),
5118 p_select_tab.account_set_flag(i),
5119 decode(p_select_tab.int_code_combination_id(i),
5120 '', decode(p_select_tab.code_combination_id(i),
5121 -1, p_select_tab.concatenated_segments(i),
5122 NULL ),
5123 -1, p_select_tab.int_concatenated_segments(i),
5124 NULL),
5125 decode(p_select_tab.int_code_combination_id(i),
5126 '', p_select_tab.code_combination_id(i),
5127 p_select_tab.int_code_combination_id(i)),
5128 to_date(p_select_tab.gl_date(i), 'J'),
5129 to_date(p_select_tab.original_gl_date(i), 'J'),
5130 p_select_tab.ussgl_trx_code(i),
5131 p_select_tab.ussgl_trx_code_context(i),
5132 -3,
5133 decode( p_select_tab.account_class(i),
5134 'REC', 'Y',
5135 NULL),
5136 decode(p_select_tab.int_code_combination_id(i),
5137 '',NULL,
5138 decode(p_select_tab.code_combination_id(i),
5139 -1, p_select_tab.concatenated_segments(i),
5140 NULL)),
5141 decode(p_select_tab.int_code_combination_id(i),
5142 '',NULL,
5143 p_select_tab.code_combination_id(i)),
5144 DECODE(p_select_tab.account_set_flag(i), 'Y', NULL,
5145 DECODE(p_select_tab.account_class(i), 'UNEARN', 'Y', NULL))
5146 ,arp_standard.sysparm.org_id --anuj
5147 )
5148 RETURNING cust_trx_line_gl_dist_id
5149 BULK COLLECT INTO l_gl_dist_key_value_list;
5150
5151 /* only insert the MRC gl_dist data if this has been called from
5152 forms. For autoinv this insert is handled differently by
5153 request_id.
5154 -- Added by Bsarkar
5155 The g_called_from is introduced to stop the call for Invoice Creation
5156 API. In case AUTO_ACCOUNTING is called from Tax engine for Invoice API
5157 this variable
5158 will have different value and won't execute the MRC call. The MRC call
5159 for invoice creation API is handled based on request Id and this call is
5160 not required. */
5161
5162
5163 IF (p_request_id IS NULL AND g_called_from = 'FORMS' ) THEN
5164 IF PG_DEBUG in ('Y', 'C') THEN
5165 arp_util.debug('calling mrc engine for insertion of gl dist data');
5166 END IF;
5167 --BUG#2750340
5168 FOR i IN p_low .. p_high LOOP
5169 inext := p_select_tab.customer_trx_id(i);
5170 IF inext <> inow THEN
5171 l_xla_event.xla_from_doc_id := p_select_tab.customer_trx_id(i);
5172 l_xla_event.xla_to_doc_id := p_select_tab.customer_trx_id(i);
5173 l_xla_event.xla_req_id := NULL;
5174 l_xla_event.xla_dist_id := NULL;
5175 l_xla_event.xla_doc_table := 'CT';
5176 l_xla_event.xla_doc_event := NULL;
5177 l_xla_event.xla_mode := 'O';
5178 l_xla_event.xla_call := 'B';
5179
5180 ARP_XLA_EVENTS.CREATE_EVENTS(p_xla_ev_rec => l_xla_event );
5181 inow := inext;
5182 END IF;
5183 END LOOP;
5184 END IF;
5185
5186 print_fcn_label2( 'arp_auto_accounting.insert_dist_row()-' );
5187 EXCEPTION
5188 WHEN OTHERS THEN
5189 debug('EXCEPTION: arp_auto_accounting.insert_dist_row()',
5190 MSG_LEVEL_BASIC);
5191 debug(SQLERRM, MSG_LEVEL_BASIC);
5192 RAISE;
5193 END insert_dist_row;
5194
5195 ----------------------------------------------------------------------------
5196 PROCEDURE dump_select_rec( p_select_rec IN select_rec_type ) IS
5197 BEGIN
5198 print_fcn_label2( 'arp_auto_accounting.dump_select_rec()+' );
5199
5200 debug( ' Dumping select record: ', MSG_LEVEL_DEBUG );
5201 debug( ' customer_trx_id='
5202 || to_char( p_select_rec.customer_trx_id ), MSG_LEVEL_DEBUG );
5203 debug( ' customer_trx_line_id='
5204 || to_char( p_select_rec.customer_trx_line_id ), MSG_LEVEL_DEBUG );
5205 debug( ' customer_trx_line_salesrep_id='
5206 || to_char( p_select_rec.cust_trx_line_salesrep_id ),
5207 MSG_LEVEL_DEBUG );
5208 debug( ' line_amount='
5209 || to_char( p_select_rec.line_amount ), MSG_LEVEL_DEBUG );
5210 debug( ' accounted_line_amount='
5211 || to_char( p_select_rec.accounted_line_amount ), MSG_LEVEL_DEBUG );
5212 debug( ' percent='
5213 || to_char( p_select_rec.percent ), MSG_LEVEL_DEBUG );
5214 debug( ' amount='
5215 || to_char( p_select_rec.amount ), MSG_LEVEL_DEBUG );
5216 debug( ' acctd_amount='
5217 || to_char( p_select_rec.acctd_amount ), MSG_LEVEL_DEBUG );
5218 debug( ' account_class=' || p_select_rec.account_class, MSG_LEVEL_DEBUG );
5219 debug( ' account_set_flag=' || p_select_rec.account_set_flag,
5220 MSG_LEVEL_DEBUG );
5221 debug( ' cust_trx_type_id='
5222 || to_char( p_select_rec.cust_trx_type_id ), MSG_LEVEL_DEBUG );
5223 debug( ' allow_not_open_flag=' ||
5224 p_select_rec.allow_not_open_flag, MSG_LEVEL_DEBUG );
5225 debug( ' concatenated_segments='
5226 || p_select_rec.concatenated_segments, MSG_LEVEL_DEBUG );
5227 debug( ' code_combination_id='
5228 || to_char( p_select_rec.code_combination_id ), MSG_LEVEL_DEBUG );
5229 debug( ' gl_date=' || p_select_rec.gl_date, MSG_LEVEL_DEBUG );
5230 debug( ' original_gl_date=' || p_select_rec.original_gl_date,
5231 MSG_LEVEL_DEBUG );
5232 debug( ' ussgl_trx_code=' || p_select_rec.ussgl_trx_code, MSG_LEVEL_DEBUG );
5233 debug( ' ussgl_trx_code_context='
5234 || p_select_rec.ussgl_trx_code_context, MSG_LEVEL_DEBUG );
5235 debug( ' salesrep_id='
5236 || to_char( p_select_rec.salesrep_id ), MSG_LEVEL_DEBUG );
5237 debug( ' inventory_item_id='
5238 || to_char( p_select_rec.inventory_item_id ), MSG_LEVEL_DEBUG );
5239 debug( ' memo_line_id='
5240 || to_char( p_select_rec.memo_line_id ), MSG_LEVEL_DEBUG );
5241 debug( ' default_tax_ccid='
5242 || to_char( p_select_rec.default_tax_ccid ), MSG_LEVEL_DEBUG );
5243 debug( ' interim_tax_ccid='
5244 || to_char( p_select_rec.interim_tax_ccid ), MSG_LEVEL_DEBUG );
5245 debug( ' site_use_id='
5246 || to_char( p_select_rec.site_use_id ), MSG_LEVEL_DEBUG );
5247 debug( ' warehouse_id='
5248 || to_char( p_select_rec.warehouse_id ), MSG_LEVEL_DEBUG );
5249
5250 print_fcn_label2( 'arp_auto_accounting.dump_select_rec()-' );
5251
5252 EXCEPTION
5253 WHEN OTHERS THEN
5254 debug('EXCEPTION: arp_auto_accounting.dump_select_rec()',
5255 MSG_LEVEL_BASIC);
5256 debug(SQLERRM, MSG_LEVEL_BASIC);
5257 END dump_select_rec;
5258
5259 ----------------------------------------------------------------------------
5260 PROCEDURE dump_select_tab( p_select_tab IN select_rec_tab, p_low IN NUMBER, p_high IN NUMBER ) IS
5261 BEGIN
5262 print_fcn_label2( 'arp_auto_accounting.dump_select_tab()+' );
5263
5264 /* bug 1532372 - changed parameter from l_rows_fetched to l_low and l_high
5265 this apparently corrects the 1000 REC row limit that
5266 we encountered during benchmarking. */
5267
5268 FOR i in p_low..p_high LOOP
5269 debug( ' Dumping select record: [' || i ||']', MSG_LEVEL_DEBUG );
5270 debug( ' customer_trx_id='
5271 || to_char( p_select_tab.customer_trx_id(i) ), MSG_LEVEL_DEBUG );
5272 debug( ' customer_trx_line_id='
5273 || to_char( p_select_tab.customer_trx_line_id (i)), MSG_LEVEL_DEBUG );
5274 debug( ' customer_trx_line_salesrep_id='
5275 || to_char( p_select_tab.cust_trx_line_salesrep_id(i)),
5276 MSG_LEVEL_DEBUG );
5277 debug( ' line_amount='
5278 || to_char( p_select_tab.line_amount(i) ), MSG_LEVEL_DEBUG );
5279 debug( ' accounted_line_amount='
5280 || to_char( p_select_tab.accounted_line_amount (i)), MSG_LEVEL_DEBUG );
5281 debug( ' percent='
5282 || to_char( p_select_tab.percent(i) ), MSG_LEVEL_DEBUG );
5283 debug( ' amount='
5284 || to_char( p_select_tab.amount(i) ), MSG_LEVEL_DEBUG );
5285 debug( ' acctd_amount='
5286 || to_char( p_select_tab.acctd_amount(i) ), MSG_LEVEL_DEBUG );
5287 debug( ' account_class=' || p_select_tab.account_class(i), MSG_LEVEL_DEBUG );
5288 debug( ' account_set_flag=' || p_select_tab.account_set_flag(i),
5289 MSG_LEVEL_DEBUG );
5290 debug( ' cust_trx_type_id='
5291 || to_char( p_select_tab.cust_trx_type_id(i) ), MSG_LEVEL_DEBUG );
5292 debug( ' allow_not_open_flag=' ||
5293 p_select_tab.allow_not_open_flag(i), MSG_LEVEL_DEBUG );
5294 debug( ' concatenated_segments='
5295 || p_select_tab.concatenated_segments(i), MSG_LEVEL_DEBUG );
5296 debug( ' code_combination_id='
5297 || to_char( p_select_tab.code_combination_id (i)), MSG_LEVEL_DEBUG );
5298 debug( ' gl_date=' || p_select_tab.gl_date(i), MSG_LEVEL_DEBUG );
5299 debug( ' original_gl_date=' || p_select_tab.original_gl_date(i),
5300 MSG_LEVEL_DEBUG );
5301 debug( ' ussgl_trx_code=' || p_select_tab.ussgl_trx_code(i), MSG_LEVEL_DEBUG );
5302 debug( ' ussgl_trx_code_context='
5303 || p_select_tab.ussgl_trx_code_context(i), MSG_LEVEL_DEBUG );
5304 debug( ' salesrep_id='
5305 || to_char( p_select_tab.salesrep_id (i)), MSG_LEVEL_DEBUG );
5306 debug( ' inventory_item_id='
5307 || to_char( p_select_tab.inventory_item_id (i)), MSG_LEVEL_DEBUG );
5308 debug( ' memo_line_id='
5309 || to_char( p_select_tab.memo_line_id (i)), MSG_LEVEL_DEBUG );
5310 debug( ' default_tax_ccid='
5311 || to_char( p_select_tab.default_tax_ccid(i) ), MSG_LEVEL_DEBUG );
5312 debug( ' interim_tax_ccid='
5313 || to_char( p_select_tab.interim_tax_ccid(i) ), MSG_LEVEL_DEBUG );
5314 debug( ' site_use_id='
5315 || to_char( p_select_tab.site_use_id (i)), MSG_LEVEL_DEBUG );
5316 debug( ' warehouse_id='
5317 || to_char( p_select_tab.warehouse_id (i)), MSG_LEVEL_DEBUG );
5318 END LOOP;
5319
5320 print_fcn_label2( 'arp_auto_accounting.dump_select_tab()-' );
5321
5322 EXCEPTION
5323 WHEN OTHERS THEN
5324 debug('EXCEPTION: arp_auto_accounting.dump_select_tab()',
5325 MSG_LEVEL_BASIC);
5326 debug(SQLERRM, MSG_LEVEL_BASIC);
5327 END dump_select_tab;
5328 ----------------------------------------------------------------------------
5329
5330 FUNCTION get_select_rec( p_select_tab IN select_rec_tab, p_cnt IN NUMBER )
5331 RETURN select_rec_type AS
5332
5333 p_select_rec select_rec_type;
5334 i INTEGER:=0;
5335
5336 BEGIN
5337
5338 print_fcn_label2( 'arp_auto_accounting.get_select_rec(tab)+' );
5339 debug( ' p_cnt='
5340 || p_cnt, MSG_LEVEL_DEBUG );
5341 i := 1;
5342 p_select_rec.customer_trx_id := p_select_tab.customer_trx_id(p_cnt);
5343 i := i+1;
5344 p_select_rec.customer_trx_line_id := p_select_tab.customer_trx_line_id(p_cnt);
5345 i := i+1;
5346 p_select_rec.cust_trx_line_salesrep_id := p_select_tab.cust_trx_line_salesrep_id(p_cnt);
5347 i := i+1;
5348 p_select_rec.line_amount := p_select_tab.line_amount(p_cnt);
5349 i := i+1; --5
5350 p_select_rec.accounted_line_amount := p_select_tab.accounted_line_amount(p_cnt);
5351 i := i+1;
5352 p_select_rec.percent := p_select_tab.percent(p_cnt);
5353 i := i+1;
5354 p_select_rec.amount := p_select_tab.amount(p_cnt);
5355 i := i+1;
5356 p_select_rec.acctd_amount := p_select_tab.acctd_amount(p_cnt);
5357 i := i+1;
5358 p_select_rec.account_class := p_select_tab.account_class(p_cnt);
5359 i := i+1; --10
5360 p_select_rec.account_set_flag := p_select_tab.account_set_flag(p_cnt);
5361 i := i+1;
5362 p_select_rec.cust_trx_type_id := p_select_tab.cust_trx_type_id(p_cnt);
5363 i := i+1;
5364 p_select_rec.allow_not_open_flag := p_select_tab.allow_not_open_flag(p_cnt);
5365 i := i+1;
5366 p_select_rec.concatenated_segments := p_select_tab.concatenated_segments(p_cnt);
5367 i := i+1;
5368 p_select_rec.code_combination_id := p_select_tab.code_combination_id(p_cnt);
5369 i := i+1; --15
5370 p_select_rec.gl_date := p_select_tab.gl_date(p_cnt);
5371 i := i+1;
5372 p_select_rec.original_gl_date := p_select_tab.original_gl_date(p_cnt);
5373 i := i+1;
5374 p_select_rec.ussgl_trx_code := p_select_tab.ussgl_trx_code(p_cnt);
5375 i := i+1;
5376 p_select_rec.ussgl_trx_code_context := p_select_tab.ussgl_trx_code_context(p_cnt);
5377 i := i+1;
5378 p_select_rec.salesrep_id := p_select_tab.salesrep_id(p_cnt);
5379 i := i+1; --20
5380 p_select_rec.inventory_item_id := p_select_tab.inventory_item_id(p_cnt);
5381 i := i+1;
5382 p_select_rec.memo_line_id := p_select_tab.memo_line_id(p_cnt);
5383 i := i+1;
5384 p_select_rec.default_tax_ccid := p_select_tab.default_tax_ccid(p_cnt);
5385 i := i+1;
5386
5387 /* 5148504 - Insure that interim_tax_ccid returns as null
5388 if etax/arp_etax_util returns it as -1. Otherwise, this
5389 artificially acts as if it is deferred tax. */
5390 IF p_select_tab.interim_tax_ccid(p_cnt) = -1
5391 THEN
5392 p_select_rec.interim_tax_ccid := NULL;
5393 ELSE
5394 p_select_rec.interim_tax_ccid := p_select_tab.interim_tax_ccid(p_cnt);
5395 END IF;
5396 i := i+1; --24
5397 -- p_select_rec.int_concatenated_segments := p_select_tab.int_concatenated_segments(p_cnt);
5398 i := i+1; --25
5399 -- p_select_rec.int_code_combination_id := p_select_tab.int_code_combination_id(p_cnt);
5400 i := i+1;
5401 p_select_rec.site_use_id := p_select_tab.site_use_id(p_cnt);
5402 i := i+1;
5403 p_select_rec.warehouse_id := p_select_tab.warehouse_id(p_cnt);
5404 -- 1651593
5405 i := i+1;
5406 p_select_rec.link_to_cust_trx_line_id := p_select_tab.link_to_cust_trx_line_id(p_cnt);
5407
5408 return(p_select_rec);
5409
5410 EXCEPTION
5411 WHEN OTHERS THEN
5412 debug('EXCEPTION: arp_auto_accounting.get_select_rec():'|| i, MSG_LEVEL_BASIC);
5413 debug(SQLERRM, MSG_LEVEL_BASIC);
5414 RAISE;
5415
5416 END;
5417
5418 ----------------------------------------------------------------------------
5419 PROCEDURE process_line( p_system_info IN
5420 arp_trx_global.system_info_rec_type,
5421 p_select_rec IN OUT NOCOPY select_rec_type,
5422 p_total_percent IN OUT NOCOPY NUMBER,
5423 p_total_amount IN OUT NOCOPY NUMBER,
5424 p_total_acctd_amount IN OUT NOCOPY NUMBER,
5425 p_failure_count IN OUT NOCOPY BINARY_INTEGER,
5426 p_mode IN VARCHAR2,
5427 p_request_id IN BINARY_INTEGER ) IS
5428
5429 l_boolean BOOLEAN;
5430 l_default_gl_date DATE;
5431 l_default_rule_used VARCHAR2(50);
5432 l_error_message VARCHAR2(256);
5433
5434
5435 BEGIN
5436 --
5437 -- Default gl date if in closed period
5438 --
5439 debug( ' Defaulting gl_date', MSG_LEVEL_DEBUG );
5440
5441 IF( p_select_rec.gl_date IS NOT NULL ) THEN
5442
5443 l_boolean :=
5444 arp_standard.validate_and_default_gl_date
5445 ( to_date(p_select_rec.gl_date, 'J'),
5446 to_date(p_select_rec.original_gl_date, 'J'),
5447 NULL,
5448 NULL,
5449 NULL,
5450 NULL,
5451 NULL,
5452 NULL,
5453 p_select_rec.allow_not_open_flag,
5454 NULL,
5455 p_system_info.system_parameters.set_of_books_id,
5456 222,
5457 l_default_gl_date,
5458 l_default_rule_used,
5459 l_error_message );
5460
5461 p_select_rec.gl_date := to_char( l_default_gl_date, 'J' );
5462
5463 IF( l_boolean ) THEN
5464 debug( ' Using default gl date of ' ||
5465 to_char( l_default_gl_date ), MSG_LEVEL_DEBUG );
5466 debug( ' derived by rule ' ||
5467 l_default_rule_used, MSG_LEVEL_DEBUG );
5468
5469 ELSE
5470 --
5471 -- defaulting gl_date failure
5472 --
5473 g_error_buffer := l_error_message;
5474 debug( 'EXCEPTION: '||g_error_buffer, MSG_LEVEL_BASIC );
5475 RAISE error_defaulting_gl_date;
5476 END IF;
5477 END IF;
5478
5479 --
5480 -- Correct rounding errors
5481 --
5482 debug( ' Correcting rounding errors', MSG_LEVEL_DEBUG );
5483
5484 correct_rounding_errors( p_select_rec,
5485 p_total_percent,
5486 p_total_amount,
5487 p_total_acctd_amount );
5488
5489 --
5490 -- Set the CCID validation date
5491 --
5492 /* Bug 2142306 - added NVL function */
5493 validation_date := NVL(TO_DATE(p_select_rec.gl_date, 'J'), G_SYS_DATE);
5494
5495
5496 /* Bug 2560036 - Change account class to 'UNEARN' if
5497 the transaction fails collectibility */
5498
5499 IF (p_select_rec.account_class = 'REV' AND
5500 p_select_rec.account_set_flag = 'N' AND
5501 g_test_collectibility) THEN
5502
5503 /* Bug 3440172/3446698 - Conflict between autoaccounting
5504 and collectibility causing imported DMs and on acct
5505 CMs to be missing REV distributions */
5506 /* Bug 4693399 - manually entered transactions can also be deferred
5507 by contingencies */
5508 IF (p_mode = 'I' AND
5509 t_collect.EXISTS(p_select_rec.customer_trx_line_id))
5510 THEN
5511 IF (t_collect(p_select_rec.customer_trx_line_id) =
5512 ar_revenue_management_pvt.defer) THEN
5513
5514 p_select_rec.account_class := 'UNEARN';
5515 p_select_rec.code_combination_id := NULL;
5516 END IF;
5517
5518 END IF;
5519
5520 /* We originally toyed with implementing
5521 collectibility for when a user maintains (or changes)
5522 a transaction. But it was decided that for the initial
5523 go of this, a user steps outside of collectibility if they
5524 make any changes to a collectibility-deferred transaction. */
5525
5526 END IF;
5527
5528 /* End bug 2560036 */
5529
5530 --
5531 -- Call Flex manager
5532 --
5533 IF( p_select_rec.code_combination_id IS NULL ) THEN
5534
5535 flex_manager( p_select_rec.account_class,
5536 p_select_rec.customer_trx_line_id,
5537 p_select_rec.cust_trx_type_id,
5538 p_select_rec.salesrep_id,
5539 p_select_rec.inventory_item_id,
5540 p_select_rec.memo_line_id,
5541 p_select_rec.default_tax_ccid,
5542 p_select_rec.interim_tax_ccid,
5543 p_select_rec.site_use_id,
5544 p_select_rec.warehouse_id,
5545 p_select_rec.code_combination_id,
5546 p_select_rec.concatenated_segments,
5547 p_select_rec.int_code_combination_id,
5548 p_select_rec.int_concatenated_segments );
5549 END IF;
5550
5551 IF( p_select_rec.code_combination_id = -1 ) THEN
5552
5553 -- keep track of # rows where ccid was not found
5554 -- if > 0, then need to call AOL dynamic insert
5555 -- on the client-side
5556 --
5557 p_failure_count := nvl(p_failure_count, 0) + 1;
5558
5559 debug('process_line: Failure count : '||to_char(p_failure_count),
5560 MSG_LEVEL_DEBUG);
5561 END IF;
5562
5563 --
5564 --Increment failure count if invalid ccid for interim tax account
5565 --
5566
5567 IF( p_select_rec.int_code_combination_id = -1 ) THEN
5568
5569 p_failure_count := nvl(p_failure_count, 0) + 1;
5570
5571 debug('process_line: Failure count : '||to_char(p_failure_count),
5572 MSG_LEVEL_DEBUG);
5573 END IF;
5574
5575 END process_line;
5576
5577
5578 --
5579 --
5580 -- FUNCTION NAME: do_autoaccounting
5581 --
5582 -- DECSRIPTION:
5583 -- Server-side entry point for autoaccounting.
5584 -- This is a cover function which calls the procedure do_autoaccounting
5585 -- and exists for backward compatibility. New programs should use
5586 -- the procedure instead of the function.
5587 --
5588 -- ARGUMENTS:
5589 -- IN:
5590 -- mode: May be I(nsert), U(pdate), D(elete), or (G)et
5591 -- account_class: REC, REV, FREIGHT, TAX, UNBILL, UNEARN, SUSPENSE,
5592 -- CHARGES
5593 -- customer_trx_id: NULL if not applicable
5594 -- customer_trx_line_id: NULL if not applicable
5595 -- cust_trx_line_salesrep_id: NULL if not applicable
5596 -- request_id: NULL if not applicable
5597 -- gl_date: GL date of the account assignment
5598 -- original_gl_date: Original GL date
5599 -- total_trx_amount: For Receivable account only
5600 -- passed_ccid: Code comination ID to use if supplied
5601 -- force_account_set_no:
5602 -- cust_trx_type_id:
5603 -- primary_salesrep_id
5604 -- inventory_item_id
5605 -- memo_line_id
5606 -- msg_level
5607 --
5608 -- IN/OUT:
5609 -- ccid
5610 -- concat_segments
5611 -- num_dist_rows_failed
5612 -- errorbuf
5613 --
5614 -- OUT:
5615 --
5616 -- RETURNS:
5617 -- 1 if no errors in deriving ccids and creating distributions,
5618 -- 0 if one or more rows where ccid could not be found,
5619 -- Exception raised if SQL error or other fatal error.
5620 --
5621 -- NOTES:
5622 --
5623 -- HISTORY:
5624 --
5625 FUNCTION do_autoaccounting( p_mode IN VARCHAR2,
5626 p_account_class IN VARCHAR2,
5627 p_customer_trx_id IN NUMBER,
5628 p_customer_trx_line_id IN NUMBER,
5629 p_cust_trx_line_salesrep_id IN NUMBER,
5630 p_request_id IN NUMBER,
5631 p_gl_date IN DATE,
5632 p_original_gl_date IN DATE,
5633 p_total_trx_amount IN NUMBER,
5634 p_passed_ccid IN NUMBER,
5635 p_force_account_set_no IN VARCHAR2,
5636 p_cust_trx_type_id IN NUMBER,
5637 p_primary_salesrep_id IN NUMBER,
5638 p_inventory_item_id IN NUMBER,
5639 p_memo_line_id IN NUMBER,
5640 p_ccid IN OUT NOCOPY NUMBER,
5641 p_concat_segments IN OUT NOCOPY VARCHAR2,
5642 p_num_failed_dist_rows IN OUT NOCOPY NUMBER,
5643 p_errorbuf IN OUT NOCOPY VARCHAR2,
5644 p_msg_level IN NUMBER )
5645 RETURN NUMBER IS
5646
5647 l_temp BINARY_INTEGER;
5648
5649 BEGIN
5650
5651 g_errorbuf := NULL;
5652
5653 --------------------------------------------------------------------------
5654 -- Set message level for debugging
5655 --------------------------------------------------------------------------
5656 system_info.msg_level := p_msg_level;
5657 arp_global.msg_level := p_msg_level;
5658
5659 print_fcn_label( 'arp_auto_accounting.do_autoaccounting_cover()+ ' );
5660
5661 --------------------------------------------------------------------------
5662 -- Initialize
5663 --------------------------------------------------------------------------
5664 p_errorbuf := NULL;
5665
5666 do_autoaccounting( p_mode,
5667 p_account_class,
5668 p_customer_trx_id,
5669 p_customer_trx_line_id,
5670 p_cust_trx_line_salesrep_id,
5671 p_request_id,
5672 p_gl_date,
5673 p_original_gl_date,
5674 p_total_trx_amount,
5675 p_passed_ccid,
5676 p_force_account_set_no,
5677 p_cust_trx_type_id,
5678 p_primary_salesrep_id,
5679 p_inventory_item_id,
5680 p_memo_line_id,
5681 p_ccid,
5682 p_concat_segments,
5683 p_num_failed_dist_rows );
5684
5685
5686 print_fcn_label( 'arp_auto_accounting.do_autoaccounting_cover()- ' );
5687
5688 IF (( p_mode = G AND p_ccid = -1 )
5689 OR
5690 ( p_request_id IS NOT NULL AND p_num_failed_dist_rows > 0)) THEN
5691
5692 RETURN 0; -- no ccid was created
5693
5694 END IF;
5695
5696 RETURN 1;
5697
5698
5699 EXCEPTION
5700 WHEN no_ccid THEN
5701 RETURN 0; -- could not get valid ccid, failure
5702
5703 WHEN NO_DATA_FOUND THEN
5704 RETURN 1; -- treat this as success
5705
5706 WHEN OTHERS THEN
5707 g_errorbuf := g_error_buffer;
5708 debug(SQLERRM, MSG_LEVEL_BASIC);
5709 RAISE;
5710 END do_autoaccounting;
5711
5712
5713 ----------------------------------------------------------------------------
5714 PROCEDURE do_autoaccounting_internal(
5715 p_mode IN VARCHAR2,
5716 p_account_class IN VARCHAR2,
5717 p_customer_trx_id IN NUMBER,
5718 p_customer_trx_line_id IN NUMBER,
5719 p_cust_trx_line_salesrep_id IN NUMBER,
5720 p_request_id IN NUMBER,
5721 p_gl_date IN DATE,
5722 p_original_gl_date IN DATE,
5723 p_total_trx_amount IN NUMBER,
5724 p_passed_ccid IN NUMBER,
5725 p_force_account_set_no IN VARCHAR2,
5726 p_cust_trx_type_id IN NUMBER,
5727 p_primary_salesrep_id IN NUMBER,
5728 p_inventory_item_id IN NUMBER,
5729 p_memo_line_id IN NUMBER,
5730 p_site_use_id IN NUMBER,
5731 p_warehouse_id IN NUMBER,
5732 p_ccid IN OUT NOCOPY NUMBER,
5733 p_concat_segments IN OUT NOCOPY VARCHAR2,
5734 p_failure_count IN OUT NOCOPY NUMBER )
5735 IS
5736
5737
5738 l_select_rec select_rec_type;
5739 l_select_tab select_rec_tab;
5740 l_null_rec CONSTANT select_rec_type := l_select_rec;
5741
5742 -- Cursors
5743 --
5744 l_select_c INTEGER;
5745 l_delete_c INTEGER;
5746
5747 --
5748 -- Running totals
5749 --
5750 l_total_percent NUMBER := 0;
5751 l_total_amount NUMBER := 0;
5752 l_total_acctd_amount NUMBER := 0;
5753
5754 l_rows_fetched NUMBER := 0;
5755
5756 l_ignore INTEGER;
5757 l_boolean BOOLEAN;
5758 l_first_fetch BOOLEAN;
5759 l_temp BINARY_INTEGER;
5760 l_keep_cursor_open_flag BOOLEAN := FALSE;
5761
5762 l_low INTEGER:=0;
5763 l_high INTEGER:=0;
5764
5765 gl_dist_array dbms_sql.number_table; /* MRC */
5766 l_error_count NUMBER := 0;
5767 BEGIN
5768
5769 print_fcn_label( 'arp_auto_accounting.do_autoaccounting_internal()+' );
5770 --begin anuj
5771 /* Multi-Org Access Control Changes for SSA;Begin;anukumar;11/01/2002*/
5772 init;
5773 /* Multi-Org Access Control Changes for SSA;Begin;anukumar;11/01/2002*/
5774 --end anuj
5775
5776
5777 SAVEPOINT ar_auto_accounting;
5778
5779 --------------------------------------------------------------------------
5780 -- Process modes
5781 --------------------------------------------------------------------------
5782 IF( p_mode = G ) THEN
5783 --
5784 -- Get mode, populate record immediately
5785 --
5786 l_select_rec := l_null_rec; -- start with null record
5787
5788 l_select_rec.customer_trx_line_id := p_customer_trx_line_id;
5789 l_select_rec.account_class := p_account_class;
5790 l_select_rec.cust_trx_type_id := p_cust_trx_type_id;
5791 l_select_rec.salesrep_id := p_primary_salesrep_id;
5792 l_select_rec.inventory_item_id := p_inventory_item_id;
5793 l_select_rec.memo_line_id := p_memo_line_id;
5794 l_select_rec.site_use_id := p_site_use_id;
5795 l_select_rec.warehouse_id := p_warehouse_id;
5796
5797 dump_select_rec( l_select_rec );
5798
5799 --------------------------------------------------------------------
5800 -- Default gl date if in closed period
5801 -- Correct rounding errors
5802 -- Call Flex mgr
5803 --------------------------------------------------------------------
5804
5805 process_line( system_info,
5806 l_select_rec,
5807 l_total_percent,
5808 l_total_amount,
5809 l_total_acctd_amount,
5810 p_failure_count,
5811 p_mode,
5812 p_request_id );
5813
5814 --------------------------------------------------------------------
5815 -- Update IN OUT NOCOPY parameters for output to Form fields
5816 --------------------------------------------------------------------
5817 p_ccid := l_select_rec.code_combination_id;
5818 p_concat_segments := l_select_rec.concatenated_segments;
5819
5820 ELSE -- I, U or D modes
5821
5822
5823 IF( p_mode in (U, D) ) THEN
5824 --
5825 -- Delete distributions in Update and Delete mode
5826 --
5827
5828 ----------------------------------------------------------------
5829 -- Construct delete stmt
5830 ----------------------------------------------------------------
5831 DECLARE
5832 l_delete_stmt VARCHAR2(32767);
5833
5834 BEGIN
5835
5836 l_delete_c := dbms_sql.open_cursor;
5837 l_delete_stmt := build_delete_sql( system_info,
5838 profile_info,
5839 p_account_class,
5840 p_customer_trx_id,
5841 p_customer_trx_line_id,
5842 p_cust_trx_line_salesrep_id,
5843 p_request_id );
5844
5845 l_delete_stmt := l_delete_stmt ||
5846 ' RETURNING cust_trx_line_gl_dist_id INTO :gl_dist_key_value ';
5847
5848 dbms_sql.parse( l_delete_c, l_delete_stmt, dbms_sql.v7 );
5849
5850 /*-----------------------+
5851 | bind output variable |
5852 +-----------------------*/
5853 dbms_sql.bind_array(l_delete_c,':gl_dist_key_value',
5854 gl_dist_array);
5855
5856
5857 EXCEPTION
5858 WHEN OTHERS THEN
5859 debug( 'Error constructing/parsing delete cursor',
5860 MSG_LEVEL_BASIC );
5861 debug(SQLERRM, MSG_LEVEL_BASIC);
5862 RAISE;
5863
5864 END;
5865
5866 ----------------------------------------------------------------
5867 -- Delete distributions
5868 ----------------------------------------------------------------
5869 debug( ' Deleting distributions', MSG_LEVEL_DEBUG );
5870
5871 BEGIN
5872 l_ignore := dbms_sql.execute( l_delete_c );
5873
5874 debug( to_char(l_ignore) || ' row(s) deleted',
5875 MSG_LEVEL_DEBUG );
5876
5877 IF ( l_ignore > 0) THEN
5878 /*------------------------------------------+
5879 | get RETURNING COLUMN into OUT NOCOPY bind array |
5880 +------------------------------------------*/
5881
5882 dbms_sql.variable_value( l_delete_c, ':gl_dist_key_value',
5883 gl_dist_array);
5884
5885 IF PG_DEBUG in ('Y', 'C') THEN
5886 arp_standard.debug('do_autoaccounting: ' || 'before loop for MRC processing...');
5887 END IF;
5888 FOR I in gl_dist_array.FIRST .. gl_dist_array.LAST LOOP
5889 /*---------------------------------------------------------+
5890 | call mrc engine to delete from ra_cust_trx_line_gl_dist |
5891 +---------------------------------------------------------*/
5892 IF PG_DEBUG in ('Y', 'C') THEN
5893 arp_standard.debug('do_autoaccounting: ' || 'before calling maintain_mrc ');
5894 arp_standard.debug('do_autoaccounting: ' || 'gl dist array('||to_char(I) || ') = ' ||
5895 to_char(gl_dist_array(I)));
5896 END IF;
5897
5898 ar_mrc_engine.maintain_mrc_data(
5899 p_event_mode => 'DELETE',
5900 p_table_name => 'RA_CUST_TRX_LINE_GL_DIST',
5901 p_mode => 'SINGLE',
5902 p_key_value => gl_dist_array(I));
5903 END LOOP;
5904 END IF;
5905
5906 close_cursor( l_delete_c );
5907
5908 EXCEPTION
5909 WHEN OTHERS THEN
5910 debug( 'Error executing delete stmt', MSG_LEVEL_BASIC );
5911 debug(SQLERRM, MSG_LEVEL_BASIC);
5912 RAISE;
5913
5914 END;
5915
5916
5917 END IF; -- if mode = U, D
5918
5919 IF( p_mode in (I, U) ) THEN
5920
5921 /* Bug 2560036 - Call collectibility when in INSERT mode
5922 only. */
5923
5924 IF (p_mode = 'I' AND
5925 p_account_class in ('REV','ALL') AND
5926 g_test_collectibility AND
5927 NOT g_called_collectibility) THEN
5928
5929 IF PG_DEBUG in ('Y', 'C') THEN
5930 arp_util.debug(' testing collectibility...');
5931 END IF;
5932
5933 -- the following logic was enhanced to distinguish the
5934 -- call to revenue management from invoice api and others.
5935 -- if it is being called from invoice api then we would
5936 -- like to call by passing the source and not skip
5937 -- it if it was called before.
5938 --
5939 -- ORASHID 22-Sep-2004
5940
5941 IF (g_called_from = 'AR_INVOICE_API') THEN
5942 t_collect := ar_revenue_management_pvt.line_collectibility(
5943 p_request_id => p_request_id,
5944 p_source => g_called_from,
5945 x_error_count=> l_error_count);
5946 /* Bug 4693399 - manually entered transactions can also be deferred
5947 by contingencies */
5948 ELSIF (p_request_id IS NULL and p_customer_trx_line_id IS NOT NULL) THEN
5949 t_collect := ar_revenue_management_pvt.line_collectibility(
5950 p_request_id => p_request_id,
5951 x_error_count=> l_error_count,
5952 p_customer_trx_line_id=> p_customer_trx_line_id);
5953 ELSE
5954 IF (NOT g_called_collectibility) THEN
5955 g_called_collectibility := TRUE;
5956 t_collect := ar_revenue_management_pvt.line_collectibility(
5957 p_request_id => p_request_id,
5958 x_error_count=> l_error_count);
5959 END IF;
5960
5961 END IF;
5962
5963 /* Bug 3879222 - Increase p_failure_count when
5964 collectibility rejects contingency rows in autoinv */
5965 IF (l_error_count > 0)
5966 THEN
5967
5968 IF PG_DEBUG in ('Y','C')
5969 THEN
5970 arp_util.debug('failed contingencies = ' || l_error_count);
5971 END IF;
5972
5973 p_failure_count := nvl(p_failure_count,0) +
5974 nvl(l_error_count,0);
5975 END IF;
5976
5977 ELSE
5978
5979 IF PG_DEBUG in ('Y', 'C') THEN
5980 arp_standard.debug('do_autoaccounting: ' || ' collectibility bypassed...');
5981 END IF;
5982
5983 END IF;
5984
5985 /* End bug 2560036 */
5986
5987 --
5988 -- Insert distributions in Insert and Update mode
5989 --
5990
5991 --
5992 -- Fetch records using select stmt
5993 --
5994 -- Bug 853040
5995 /*Bug 2034221:Added 'Freight' also in the clause to prevent the NULL value
5996 passed for ccid.
5997 */
5998 IF p_passed_ccid IS NOT NULL AND
5999 p_account_class in('REC','FREIGHT') THEN
6000 p_ccid := p_passed_ccid;
6001 END IF;
6002 IF arp_auto_accounting.g_deposit_flag = 'Y' and
6003 p_account_class = 'REV' THEN
6004 p_ccid := p_passed_ccid;
6005 END IF;
6006
6007
6008 l_select_c := Get_Select_Cursor(
6009 system_info,
6010 profile_info,
6011 p_account_class,
6012 p_customer_trx_id,
6013 p_customer_trx_line_id,
6014 p_cust_trx_line_salesrep_id,
6015 p_request_id,
6016 p_gl_date,
6017 p_original_gl_date,
6018 p_total_trx_amount,
6019 p_ccid,
6020 p_force_account_set_no,
6021 p_cust_trx_type_id,
6022 p_primary_salesrep_id,
6023 p_inventory_item_id,
6024 p_memo_line_id,
6025 l_keep_cursor_open_flag);
6026
6027
6028 Bind_All_Variables(
6029 l_select_c,
6030 system_info,
6031 profile_info,
6032 p_account_class,
6033 p_customer_trx_id,
6034 p_customer_trx_line_id,
6035 p_cust_trx_line_salesrep_id,
6036 p_request_id,
6037 p_gl_date,
6038 p_original_gl_date,
6039 p_total_trx_amount,
6040 p_passed_ccid,
6041 p_force_account_set_no,
6042 p_cust_trx_type_id,
6043 p_primary_salesrep_id,
6044 p_inventory_item_id,
6045 p_memo_line_id,
6046 l_keep_cursor_open_flag);
6047
6048
6049
6050 -- Initialize totals
6051 l_total_percent := 0;
6052 l_total_amount := 0;
6053 l_total_acctd_amount := 0;
6054
6055 l_first_fetch := TRUE;
6056
6057 ----------------------------------------------------------------
6058 -- Execute select stmt
6059 ----------------------------------------------------------------
6060 BEGIN
6061
6062 debug( ' Executing select stmt', MSG_LEVEL_DEBUG );
6063 --
6064 -- Execute
6065 --
6066 l_ignore := dbms_sql.execute( l_select_c );
6067
6068 EXCEPTION
6069 WHEN OTHERS THEN
6070 debug( 'Error executing select cursor', MSG_LEVEL_BASIC );
6071 debug(SQLERRM, MSG_LEVEL_BASIC);
6072 RAISE;
6073 END;
6074
6075 ---------------------------------------------------------------
6076 -- Fetch rows
6077 ---------------------------------------------------------------
6078 debug( ' Fetching select stmt', MSG_LEVEL_DEBUG );
6079
6080 LOOP -- Main Cursor Loop
6081
6082 BEGIN
6083 --
6084 -- Each call to the fetch_rows will fetch MAX_ARRAY_SIZE rows of data
6085 -- If no. of rows are < MAX_ARRAY_SIZE then exit loop after processing.
6086 --
6087
6088 l_rows_fetched := dbms_sql.fetch_rows( l_select_c );
6089
6090 l_low := l_high + 1;
6091 l_high:= l_high + l_rows_fetched;
6092
6093
6094 IF l_rows_fetched > 0 THEN
6095
6096 debug( ' Fetched a row :('|| l_rows_fetched || ')', MSG_LEVEL_DEBUG );
6097 debug( ' l_low : '|| l_low , MSG_LEVEL_DEBUG );
6098 debug( ' l_high : '|| l_high , MSG_LEVEL_DEBUG );
6099
6100
6101 l_first_fetch := FALSE;
6102
6103 l_select_rec := l_null_rec;
6104
6105
6106 get_column_values( l_select_c, l_select_tab );
6107
6108 /* bug 1532372 - changed parameter from l_rows_fetched to l_low and l_high
6109 this apparently corrects the 1000 REC row limit that
6110 we encountered during benchmarking. */
6111
6112 dump_select_tab( l_select_tab, l_low, l_high );
6113
6114 IF l_rows_fetched < MAX_ARRAY_SIZE THEN
6115 --
6116 -- no more rows to fetch
6117 --
6118 debug( ' Done fetching(if)', MSG_LEVEL_DEBUG );
6119
6120 IF ( l_keep_cursor_open_flag = FALSE )THEN
6121 close_cursor( l_select_c );
6122 END IF;
6123
6124 END IF;
6125 ELSE
6126 --
6127 -- no more rows to fetch
6128 --
6129 debug( ' Done fetching(else)', MSG_LEVEL_DEBUG );
6130
6131 IF ( l_keep_cursor_open_flag = FALSE )THEN
6132 close_cursor( l_select_c );
6133 END IF;
6134
6135 -- No rows selected
6136
6137 IF (l_first_fetch) THEN
6138
6139 debug( ' raising NO_DATA_FOUND', MSG_LEVEL_DEBUG );
6140 RAISE NO_DATA_FOUND;
6141
6142 END IF;
6143
6144 EXIT; -- Exit out NOCOPY of loop
6145
6146 END IF; -- Rows Fetched
6147
6148 EXCEPTION
6149 WHEN NO_DATA_FOUND THEN
6150 RAISE;
6151 WHEN OTHERS THEN
6152 debug( 'Error fetching select cursor', MSG_LEVEL_BASIC );
6153 debug(SQLERRM, MSG_LEVEL_BASIC);
6154 RAISE;
6155 END;
6156
6157
6158 -----------------------------------------------------------
6159 -- Default gl date if in closed period
6160 -- Correct rounding errors
6161 -- Call Flex mgr
6162 -----------------------------------------------------------
6163 FOR i IN l_low..l_high LOOP
6164
6165 l_select_rec := get_select_rec (l_select_tab, i);
6166
6167 process_line( system_info,
6168 l_select_rec,
6169 l_total_percent,
6170 l_total_amount,
6171 l_total_acctd_amount,
6172 p_failure_count,
6173 p_mode,
6174 p_request_id );
6175
6176 -- copy out NOCOPY parameters back to array
6177
6178 l_select_tab.int_concatenated_segments(i) := l_select_rec.int_concatenated_segments;
6179 l_select_tab.int_code_combination_id(i) := l_select_rec.int_code_combination_id;
6180 l_select_tab.code_combination_id(i) := l_select_rec.code_combination_id;
6181 l_select_tab.concatenated_segments(i) := l_select_rec.concatenated_segments;
6182
6183 /* Bug 2560036 - Move account class back in case
6184 we overrode it inside process_line */
6185 l_select_tab.account_class(i) := l_select_rec.account_class;
6186
6187 END LOOP;
6188
6189 -----------------------------------------------------------
6190 -- Insert row
6191 -----------------------------------------------------------
6192 BEGIN
6193 insert_dist_row( system_info,
6194 profile_info,
6195 p_request_id,
6196 l_select_tab,
6197 l_low,
6198 l_high);
6199 EXCEPTION
6200 WHEN OTHERS THEN
6201 debug( 'Error inserting distributions', MSG_LEVEL_BASIC );
6202 debug(SQLERRM, MSG_LEVEL_BASIC);
6203 RAISE;
6204 END;
6205
6206
6207 -----------------------------------------------------------
6208 -- Insert into error table if called from autoinvoice
6209 -- and didn't get ccid
6210 -----------------------------------------------------------
6211 FOR i IN l_low..l_high LOOP
6212
6213 l_select_rec := get_select_rec (l_select_tab, i);
6214
6215 -- Get CCID's from tables as get_select_rec will not copy these
6216
6217 l_select_rec.int_concatenated_segments := l_select_tab.int_concatenated_segments(i);
6218 l_select_rec.int_code_combination_id := l_select_tab.int_code_combination_id(i);
6219 l_select_rec.code_combination_id := l_select_tab.code_combination_id(i);
6220 l_select_rec.concatenated_segments := l_select_tab.concatenated_segments(i);
6221
6222
6223 IF ( l_select_rec.code_combination_id = -1 ) THEN
6224
6225 IF ( p_account_class = REV ) THEN
6226
6227 put_message_on_stack(l_select_rec.customer_trx_line_id,
6228 MSG_COMPLETE_REV_ACCOUNT,
6229 l_select_rec.concatenated_segments,
6230 p_request_id );
6231
6232 ELSIF( p_account_class = REC ) THEN
6233
6234 -- Put -ve customer trx id in the errors table if
6235 -- AutoAccounting is unable to derive the REC account.
6236 -- Validation Report will report this error for the first
6237 -- line of the Trx
6238
6239 put_message_on_stack(-1 * l_select_rec.customer_trx_id,
6240 MSG_COMPLETE_REC_ACCOUNT,
6241 l_select_rec.concatenated_segments,
6242 p_request_id );
6243
6244 ELSIF( p_account_class = FREIGHT ) THEN
6245
6246 put_message_on_stack(
6247 l_select_rec.customer_trx_line_id,
6248 MSG_COMPLETE_FRT_ACCOUNT,
6249 l_select_rec.concatenated_segments,
6250 p_request_id );
6251
6252 ELSIF( p_account_class = TAX ) THEN
6253 /* 1651593 - Point errors to parent line if one is available */
6254 put_message_on_stack(
6255 NVL(l_select_rec.link_to_cust_trx_line_id,
6256 l_select_rec.customer_trx_line_id),
6257 MSG_COMPLETE_TAX_ACCOUNT,
6258 l_select_rec.concatenated_segments,
6259 p_request_id );
6260
6261 ELSIF( p_account_class = CHARGES ) THEN
6262
6263 put_message_on_stack(
6264 l_select_rec.customer_trx_line_id,
6265 MSG_COMPLETE_CHARGES_ACCOUNT,
6266 l_select_rec.concatenated_segments,
6267 p_request_id );
6268
6269 ELSIF( p_account_class in (UNBILL, UNEARN, SUSPENSE)) THEN
6270
6271 put_message_on_stack(
6272 l_select_rec.customer_trx_line_id,
6273 MSG_COMPLETE_OFFSET_ACCOUNT,
6274 l_select_rec.concatenated_segments,
6275 p_request_id );
6276
6277 END IF;
6278
6279 END IF;
6280
6281 IF (p_account_class = 'TAX') THEN
6282
6283 --Invalid interim tax account
6284 IF l_select_rec.int_code_combination_id = -1 THEN
6285 /* 1651593 - Point tax lines to parent line for error */
6286 put_message_on_stack(
6287 NVL(l_select_rec.link_to_cust_trx_line_id,
6288 l_select_rec.customer_trx_line_id),
6289 MSG_COMPLETE_INT_TAX_ACCOUNT,
6290 l_select_rec.int_concatenated_segments,
6291 p_request_id );
6292
6293 END IF;
6294
6295 END IF;
6296
6297 END LOOP; -- For all records in an array
6298
6299 EXIT WHEN l_rows_fetched < MAX_ARRAY_SIZE; -- Exit from the loop if no. of rows fetched < array size
6300
6301 END LOOP;
6302
6303 END IF; -- IF( p_mode in (I, U) )
6304
6305 END IF; -- IF( p_mode = G )
6306
6307
6308 -- Check if failed to get any ccids
6309 --
6310 debug( ' p_failure_count='||to_char(p_failure_count), MSG_LEVEL_DEBUG);
6311
6312 IF ( l_keep_cursor_open_flag = FALSE ) THEN
6313 close_cursor( l_select_c );
6314 END IF;
6315
6316 close_cursor( l_delete_c );
6317
6318
6319 print_fcn_label( 'arp_auto_accounting.do_autoaccounting_internal()-' );
6320
6321
6322 EXCEPTION
6323 WHEN NO_DATA_FOUND THEN
6324
6325 IF ( l_keep_cursor_open_flag = FALSE )
6326 THEN close_cursor( l_select_c );
6327 END IF;
6328
6329 close_cursor( l_delete_c );
6330
6331 IF( p_mode = G ) THEN
6332 NULL; -- Don't raise for Get mode, otherwise the
6333 -- IN/OUT vars ccid, concat_segments do not
6334 -- get populated.
6335 ELSE
6336 RAISE;
6337 END IF;
6338
6339 WHEN OTHERS THEN
6340 debug( 'EXCEPTION: arp_auto_accounting.do_autoaccounting_internal()',
6341 MSG_LEVEL_BASIC );
6342 debug(SQLERRM, MSG_LEVEL_BASIC);
6343
6344 close_cursor( l_select_c );
6345 close_cursor( l_delete_c );
6346
6347 ROLLBACK TO ar_auto_accounting;
6348 RAISE;
6349
6350 END do_autoaccounting_internal;
6351
6352 ----------------------------------------------------------------------------
6353 --
6354 -- PROCEDURE NAME: do_autoaccounting
6355 --
6356 -- DECSRIPTION:
6357 -- Server-side entry point for autoaccounting.
6358 --
6359 -- ARGUMENTS:
6360 -- IN:
6361 -- mode: May be I(nsert), U(pdate), D(elete), or (G)et
6362 -- account_class: REC, REV, FREIGHT, TAX, UNBILL, UNEARN, SUSPENSE,
6363 -- CHARGES
6364 -- customer_trx_id: NULL if not applicable
6365 -- customer_trx_line_id: NULL if not applicable (G)
6366 -- cust_trx_line_salesrep_id: NULL if not applicable
6367 -- request_id: NULL if not applicable
6368 -- gl_date: GL date of the account assignment
6369 -- original_gl_date: Original GL date
6370 -- total_trx_amount: For Receivable account only
6371 -- passed_ccid: Code comination ID to use if supplied
6372 -- force_account_set_no:
6373 -- cust_trx_type_id (G)
6374 -- primary_salesrep_id (G)
6375 -- inventory_item_id (G)
6376 -- memo_line_id (G)
6377 --
6378 -- IN/OUT:
6379 -- ccid
6380 -- concat_segments
6381 -- failure_count
6382 --
6383 -- OUT:
6384 --
6385 -- NOTES:
6386 -- If mode is not (G)et, raises the exception
6387 -- arp_auto_accounting.no_ccid if autoaccounting could not derive a
6388 -- valid code combination. The public variable g_error_buffer is
6389 -- populated for more information. In (G)et mode, check the value
6390 -- assigned to p_ccid. If it is -1, then no ccid was found.
6391 --
6392 -- Raises the exception NO_DATA_FOUND if no rows were selected for
6393 -- processing.
6394 --
6395 -- Exception raised if Oracle error.
6396 -- App_exception is raised for all other fatal errors and a message
6397 -- is put on the AOL stack. The public variable g_error_buffer is
6398 -- populated for both types of errors.
6399 --
6400 -- HISTORY:
6401 --
6402 --
6403 PROCEDURE do_autoaccounting( p_mode IN VARCHAR2,
6404 p_account_class IN VARCHAR2,
6405 p_customer_trx_id IN NUMBER,
6406 p_customer_trx_line_id IN NUMBER,
6407 p_cust_trx_line_salesrep_id IN NUMBER,
6408 p_request_id IN NUMBER,
6409 p_gl_date IN DATE,
6410 p_original_gl_date IN DATE,
6411 p_total_trx_amount IN NUMBER,
6412 p_passed_ccid IN NUMBER,
6413 p_force_account_set_no IN VARCHAR2,
6414 p_cust_trx_type_id IN NUMBER,
6415 p_primary_salesrep_id IN NUMBER,
6416 p_inventory_item_id IN NUMBER,
6417 p_memo_line_id IN NUMBER,
6418 p_ccid IN OUT NOCOPY NUMBER,
6419 p_concat_segments IN OUT NOCOPY VARCHAR2,
6420 p_failure_count IN OUT NOCOPY NUMBER )
6421 IS
6422
6423
6424 l_select_rec select_rec_type;
6425 l_null_rec CONSTANT select_rec_type := l_select_rec;
6426
6427 l_ignore INTEGER;
6428 l_boolean BOOLEAN;
6429 l_temp BINARY_INTEGER;
6430 l_account_class VARCHAR2(20);
6431
6432 BEGIN
6433
6434 print_fcn_label( 'arp_auto_accounting.do_autoaccounting()+' );
6435
6436 /*
6437 ar_transaction_pub.debug('arp_auto_accounting.do_autoaccounting()+',
6438 FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR);
6439 */
6440 g_error_buffer := NULL;
6441
6442 --
6443 -- Set message level for debugging
6444 --
6445 system_info.msg_level := arp_global.msg_level;
6446
6447 debug( ' mode='||p_mode, MSG_LEVEL_DEBUG );
6448 debug( ' account_class='||p_account_class, MSG_LEVEL_DEBUG );
6449 debug( ' customer_trx_id='||to_char(p_customer_trx_id), MSG_LEVEL_DEBUG );
6450 debug( ' customer_trx_line_id='||to_char(p_customer_trx_line_id),
6451 MSG_LEVEL_DEBUG );
6452 debug( ' cust_trx_line_salesrep_id='||
6453 to_char(p_cust_trx_line_salesrep_id),
6454 MSG_LEVEL_DEBUG );
6455 debug( ' request_id='||to_char(p_request_id), MSG_LEVEL_DEBUG );
6456 debug( ' gl_date='||to_char(p_gl_date,'MM/DD/YYYY'), MSG_LEVEL_DEBUG );
6457 debug( ' original_gl_date='||to_char(p_original_gl_date,'MM/DD/YYYY'), MSG_LEVEL_DEBUG );
6458 debug( ' total_trx_amount='||to_char(p_total_trx_amount),
6459 MSG_LEVEL_DEBUG );
6460 debug( ' passed_ccid='||to_char(p_passed_ccid), MSG_LEVEL_DEBUG );
6461 debug( ' force_account_set_no='||p_force_account_set_no,
6462 MSG_LEVEL_DEBUG );
6463 debug( ' cust_trx_type_id='||to_char(p_cust_trx_type_id),
6464 MSG_LEVEL_DEBUG );
6465 debug( ' primary_salesrep_id='||to_char(p_primary_salesrep_id),
6466 MSG_LEVEL_DEBUG );
6467 debug( ' inventory_item_id='||to_char(p_inventory_item_id),
6468 MSG_LEVEL_DEBUG );
6469 debug( ' memo_line_id='||to_char(p_memo_line_id), MSG_LEVEL_DEBUG );
6470 debug( ' msg_level='||to_char(system_info.msg_level), MSG_LEVEL_DEBUG );
6471
6472 --
6473 -- Initialize
6474 --
6475 -- p_failure_count := 0;
6476
6477 --
6478 -- Adjust account_class to proper string
6479 --
6480 l_account_class := p_account_class;
6481 expand_account_class( l_account_class );
6482
6483 IF( l_account_class = 'ALL' ) THEN
6484
6485 DECLARE
6486 l_no_rows_rev BOOLEAN := FALSE;
6487 l_no_rows_rec BOOLEAN := FALSE;
6488 l_no_rows_freight BOOLEAN := FALSE;
6489 l_no_rows_tax BOOLEAN := FALSE;
6490 l_no_rows_unbill BOOLEAN := FALSE;
6491 l_no_rows_unearn BOOLEAN := FALSE;
6492 l_no_rows_suspense BOOLEAN := FALSE;
6493 l_no_rows_charges BOOLEAN := FALSE;
6494 l_invoicing_rule_id ra_customer_trx.invoicing_rule_id%type;
6495 l_create_clearing_flag ra_batch_sources.create_clearing_flag%type;
6496 l_line_type ra_customer_trx_lines.line_type%type;
6497 BEGIN
6498
6499 debug( ' Processing ALL mode...', MSG_LEVEL_DEBUG );
6500
6501 IF ( p_customer_trx_id IS NOT NULL)
6502 THEN
6503 SELECT invoicing_rule_id,
6504 create_clearing_flag
6505 INTO l_invoicing_rule_id,
6506 l_create_clearing_flag
6507 FROM ra_customer_trx t,
6508 ra_batch_sources b
6509 WHERE customer_trx_id = p_customer_trx_id
6510 AND t.batch_source_id = b.batch_source_id;
6511 END IF;
6512
6513 IF ( p_customer_trx_line_id IS NOT NULL )
6514 THEN
6515 SELECT line_type
6516 INTO l_line_type
6517 FROM ra_customer_trx_lines
6518 WHERE customer_trx_line_id = p_customer_trx_line_id;
6519 END IF;
6520
6521 BEGIN
6522 do_autoaccounting_internal(
6523 p_mode,
6524 REC,
6525 p_customer_trx_id,
6526 p_customer_trx_line_id,
6527 p_cust_trx_line_salesrep_id,
6528 p_request_id,
6529 p_gl_date,
6530 p_original_gl_date,
6531 p_total_trx_amount,
6532 p_passed_ccid,
6533 p_force_account_set_no,
6534 p_cust_trx_type_id,
6535 p_primary_salesrep_id,
6536 p_inventory_item_id,
6537 p_memo_line_id,
6538 '','',
6539 p_ccid,
6540 p_concat_segments,
6541 p_failure_count );
6542 EXCEPTION
6543 WHEN NO_DATA_FOUND THEN
6544 l_no_rows_rec := TRUE;
6545 WHEN OTHERS THEN
6546 RAISE;
6547 END;
6548
6549 BEGIN
6550 do_autoaccounting_internal(
6551 p_mode,
6552 REV,
6553 p_customer_trx_id,
6554 p_customer_trx_line_id,
6555 p_cust_trx_line_salesrep_id,
6556 p_request_id,
6557 p_gl_date,
6558 p_original_gl_date,
6559 p_total_trx_amount,
6560 p_passed_ccid,
6561 p_force_account_set_no,
6562 p_cust_trx_type_id,
6563 p_primary_salesrep_id,
6564 p_inventory_item_id,
6565 p_memo_line_id,
6566 '','',
6567 p_ccid,
6568 p_concat_segments,
6569 p_failure_count );
6570 EXCEPTION
6571 WHEN NO_DATA_FOUND THEN
6572 l_no_rows_rev := TRUE;
6573 WHEN OTHERS THEN
6574 RAISE;
6575 END;
6576
6577 IF ( NVL(l_line_type, 'CHARGES') = 'CHARGES')
6578 THEN
6579 BEGIN
6580 do_autoaccounting_internal(
6581 p_mode,
6582 CHARGES,
6583 p_customer_trx_id,
6584 p_customer_trx_line_id,
6585 p_cust_trx_line_salesrep_id,
6586 p_request_id,
6587 p_gl_date,
6588 p_original_gl_date,
6589 p_total_trx_amount,
6590 p_passed_ccid,
6591 p_force_account_set_no,
6592 p_cust_trx_type_id,
6593 p_primary_salesrep_id,
6594 p_inventory_item_id,
6595 p_memo_line_id,
6596 '','',
6597 p_ccid,
6598 p_concat_segments,
6599 p_failure_count );
6600 EXCEPTION
6601 WHEN NO_DATA_FOUND THEN
6602 l_no_rows_charges := TRUE;
6603 WHEN OTHERS THEN
6604 RAISE;
6605 END;
6606 END IF;
6607
6608 BEGIN
6609 do_autoaccounting_internal(
6610 p_mode,
6611 TAX,
6612 p_customer_trx_id,
6613 p_customer_trx_line_id,
6614 p_cust_trx_line_salesrep_id,
6615 p_request_id,
6616 p_gl_date,
6617 p_original_gl_date,
6618 p_total_trx_amount,
6619 p_passed_ccid,
6620 p_force_account_set_no,
6621 p_cust_trx_type_id,
6622 p_primary_salesrep_id,
6623 p_inventory_item_id,
6624 p_memo_line_id,
6625 '','',
6626 p_ccid,
6627 p_concat_segments,
6628 p_failure_count );
6629 EXCEPTION
6630 WHEN NO_DATA_FOUND THEN
6631 l_no_rows_tax := TRUE;
6632 WHEN OTHERS THEN
6633 RAISE;
6634 END;
6635
6636 BEGIN
6637 do_autoaccounting_internal(
6638 p_mode,
6639 FREIGHT,
6640 p_customer_trx_id,
6641 p_customer_trx_line_id,
6642 p_cust_trx_line_salesrep_id,
6643 p_request_id,
6644 p_gl_date,
6645 p_original_gl_date,
6646 p_total_trx_amount,
6647 p_passed_ccid,
6648 p_force_account_set_no,
6649 p_cust_trx_type_id,
6650 p_primary_salesrep_id,
6651 p_inventory_item_id,
6652 p_memo_line_id,
6653 '','',
6654 p_ccid,
6655 p_concat_segments,
6656 p_failure_count );
6657 EXCEPTION
6658 WHEN NO_DATA_FOUND THEN
6659 l_no_rows_freight := TRUE;
6660 WHEN OTHERS THEN
6661 RAISE;
6662 END;
6663
6664 IF ( NVL(l_create_clearing_flag, 'Y') = 'Y' )
6665 THEN
6666
6667 BEGIN
6668 do_autoaccounting_internal(
6669 p_mode,
6670 SUSPENSE,
6671 p_customer_trx_id,
6672 p_customer_trx_line_id,
6673 p_cust_trx_line_salesrep_id,
6674 p_request_id,
6675 p_gl_date,
6676 p_original_gl_date,
6677 p_total_trx_amount,
6678 p_passed_ccid,
6679 p_force_account_set_no,
6680 p_cust_trx_type_id,
6681 p_primary_salesrep_id,
6682 p_inventory_item_id,
6683 p_memo_line_id,
6684 '','',
6685 p_ccid,
6686 p_concat_segments,
6687 p_failure_count );
6688 EXCEPTION
6689 WHEN NO_DATA_FOUND THEN
6690 l_no_rows_suspense := TRUE;
6691 WHEN OTHERS THEN
6692 RAISE;
6693 END;
6694
6695 END IF;
6696
6697 BEGIN
6698 do_autoaccounting_internal(
6699 p_mode,
6700 UNBILL,
6701 p_customer_trx_id,
6702 p_customer_trx_line_id,
6703 p_cust_trx_line_salesrep_id,
6704 p_request_id,
6705 p_gl_date,
6706 p_original_gl_date,
6707 p_total_trx_amount,
6708 p_passed_ccid,
6709 p_force_account_set_no,
6710 p_cust_trx_type_id,
6711 p_primary_salesrep_id,
6712 p_inventory_item_id,
6713 p_memo_line_id,
6714 '','',
6715 p_ccid,
6716 p_concat_segments,
6717 p_failure_count );
6718 EXCEPTION
6719 WHEN NO_DATA_FOUND THEN
6720 l_no_rows_unbill := TRUE;
6721 WHEN OTHERS THEN
6722 RAISE;
6723 END;
6724
6725 BEGIN
6726 do_autoaccounting_internal(
6727 p_mode,
6728 UNEARN,
6729 p_customer_trx_id,
6730 p_customer_trx_line_id,
6731 p_cust_trx_line_salesrep_id,
6732 p_request_id,
6733 p_gl_date,
6734 p_original_gl_date,
6735 p_total_trx_amount,
6736 p_passed_ccid,
6737 p_force_account_set_no,
6738 p_cust_trx_type_id,
6739 p_primary_salesrep_id,
6740 p_inventory_item_id,
6741 p_memo_line_id,
6742 '','',
6743 p_ccid,
6744 p_concat_segments,
6745 p_failure_count );
6746 EXCEPTION
6747 WHEN NO_DATA_FOUND THEN
6748 l_no_rows_unearn := TRUE;
6749 WHEN OTHERS THEN
6750 RAISE;
6751 END;
6752
6753 IF ( l_no_rows_rev
6754 AND l_no_rows_rec
6755 AND l_no_rows_freight
6756 AND l_no_rows_tax
6757 AND l_no_rows_unbill
6758 AND l_no_rows_unearn
6759 AND l_no_rows_suspense
6760 AND l_no_rows_charges ) THEN
6761
6762 debug( ' raising NO_DATA_FOUND', MSG_LEVEL_DEBUG );
6763 RAISE NO_DATA_FOUND;
6764
6765 END IF;
6766
6767 EXCEPTION
6768 WHEN NO_DATA_FOUND THEN
6769 RAISE;
6770 WHEN OTHERS THEN
6771 debug( 'Error processing ALL account class',
6772 MSG_LEVEL_BASIC );
6773 RAISE;
6774 END;
6775
6776
6777 ELSE -- not ALL mode
6778
6779 do_autoaccounting_internal(
6780 p_mode,
6781 l_account_class,
6782 p_customer_trx_id,
6783 p_customer_trx_line_id,
6784 p_cust_trx_line_salesrep_id,
6785 p_request_id,
6786 p_gl_date,
6787 p_original_gl_date,
6788 p_total_trx_amount,
6789 p_passed_ccid,
6790 p_force_account_set_no,
6791 p_cust_trx_type_id,
6792 p_primary_salesrep_id,
6793 p_inventory_item_id,
6794 p_memo_line_id,
6795 '','',
6796 p_ccid,
6797 p_concat_segments,
6798 p_failure_count );
6799
6800
6801 END IF; -- ALL mode
6802
6803
6804 -- Check if failed to get any ccids
6805 --
6806 debug( ' p_failure_count='||to_char(p_failure_count) ,
6807 MSG_LEVEL_DEBUG);
6808
6809 IF( p_failure_count > 0 ) THEN
6810
6811 debug( ' raising no_ccid', MSG_LEVEL_DEBUG );
6812 RAISE no_ccid;
6813
6814 END IF;
6815
6816 /*ar_transaction_pub.debug('arp_auto_accounting.do_autoaccounting()-',
6817 FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR);
6818 */
6819 print_fcn_label( 'arp_auto_accounting.do_autoaccounting()-' );
6820
6821
6822 EXCEPTION
6823 WHEN no_ccid OR NO_DATA_FOUND THEN
6824
6825 IF( p_mode = G OR
6826 p_request_id IS NOT NULL ) THEN
6827
6828 NULL; -- Don't raise for Get mode or Autoinvoice,
6829 -- otherwise the IN/OUT variables
6830 -- ccid, concat_segments and failure_count
6831 -- do not get populated.
6832 ELSE
6833 RAISE;
6834 END IF;
6835
6836 WHEN OTHERS THEN
6837 debug( 'EXCEPTION: arp_auto_accounting.do_autoaccounting()',
6838 MSG_LEVEL_BASIC );
6839 debug(SQLERRM, MSG_LEVEL_BASIC);
6840
6841 IF( sqlcode = 1 ) THEN
6842 --
6843 -- User-defined exception
6844 --
6845 FND_MESSAGE.set_name( 'AR', 'GENERIC_MESSAGE' );
6846 FND_MESSAGE.set_token( 'GENERIC_TEXT', g_error_buffer );
6847 APP_EXCEPTION.raise_exception;
6848
6849 ELSE
6850 --
6851 -- Oracle error
6852 --
6853 g_error_buffer := SQLERRM;
6854
6855 RAISE;
6856
6857 END IF;
6858
6859 END do_autoaccounting;
6860
6861
6862 ----------------------------------------------------------------------------
6863 --
6864 -- PROCEDURE NAME: do_autoaccounting
6865 --
6866 -- DECSRIPTION:
6867 -- Overloaded procedure when autoaccounting is called in G or Get mode
6868 -- as warehouse id is required to be passed in and bill_to_site_use_id
6869 -- is implicitly derived.
6870 --
6871 -- ARGUMENTS:
6872 -- IN:
6873 -- mode: May be (G)et only as the routine is written for the same
6874 -- account_class: REC, REV, FREIGHT, TAX, UNBILL, UNEARN, SUSPENSE,
6875 -- CHARGES
6876 -- customer_trx_id: NULL if not applicable
6877 -- customer_trx_line_id: NULL if not applicable (G)
6878 -- cust_trx_line_salesrep_id: NULL if not applicable
6879 -- request_id: NULL if not applicable
6880 -- gl_date: GL date of the account assignment
6881 -- original_gl_date: Original GL date
6882 -- total_trx_amount: For Receivable account only
6883 -- passed_ccid: Code comination ID to use if supplied
6884 -- force_account_set_no:
6885 -- cust_trx_type_id (G)
6886 -- primary_salesrep_id (G)
6887 -- inventory_item_id (G)
6888 -- memo_line_id (G)
6889 -- warehouse_id (G)
6890 --
6891 -- IN/OUT:
6892 -- ccid
6893 -- concat_segments
6894 -- failure_count
6895 --
6896 -- OUT:
6897 --
6898 -- NOTES:
6899 -- If mode is not (G)et, raises the exception
6900 -- arp_auto_accounting.no_ccid if autoaccounting could not derive a
6901 -- valid code combination. The public variable g_error_buffer is
6902 -- populated for more information. In (G)et mode, check the value
6903 -- assigned to p_ccid. If it is -1, then no ccid was found.
6904 --
6905 -- Raises the exception NO_DATA_FOUND if no rows were selected for
6906 -- processing.
6907 --
6908 -- Exception raised if Oracle error.
6909 -- App_exception is raised for all other fatal errors and a message
6910 -- is put on the AOL stack. The public variable g_error_buffer is
6911 -- populated for both types of errors.
6912 --
6913 -- Never call this routine for ALL classes as this was specifically
6914 -- written to work in Get mode, but will also work in other modes
6915 -- provided the account class is not ALL
6916 -- HISTORY:
6917 --
6918 --
6919 PROCEDURE do_autoaccounting( p_mode IN VARCHAR2,
6920 p_account_class IN VARCHAR2,
6921 p_customer_trx_id IN NUMBER,
6922 p_customer_trx_line_id IN NUMBER,
6923 p_cust_trx_line_salesrep_id IN NUMBER,
6924 p_request_id IN NUMBER,
6925 p_gl_date IN DATE,
6926 p_original_gl_date IN DATE,
6927 p_total_trx_amount IN NUMBER,
6928 p_passed_ccid IN NUMBER,
6929 p_force_account_set_no IN VARCHAR2,
6930 p_cust_trx_type_id IN NUMBER,
6931 p_primary_salesrep_id IN NUMBER,
6932 p_inventory_item_id IN NUMBER,
6933 p_memo_line_id IN NUMBER,
6934 p_warehouse_id IN NUMBER,
6935 p_ccid IN OUT NOCOPY NUMBER,
6936 p_concat_segments IN OUT NOCOPY VARCHAR2,
6937 p_failure_count IN OUT NOCOPY NUMBER )
6938 IS
6939
6940 l_account_class VARCHAR2(20);
6941 l_bill_to_site_use_id BINARY_INTEGER;
6942
6943 BEGIN
6944
6945 print_fcn_label( 'arp_auto_accounting.do_autoaccounting overloaded get mode()+' );
6946
6947 g_error_buffer := NULL;
6948
6949 l_bill_to_site_use_id := '';
6950
6951 --
6952 -- Set message level for debugging
6953 --
6954 system_info.msg_level := arp_global.msg_level;
6955
6956 debug( ' mode='||p_mode, MSG_LEVEL_DEBUG );
6957 debug( ' account_class='||p_account_class, MSG_LEVEL_DEBUG );
6958 debug( ' customer_trx_id='||to_char(p_customer_trx_id), MSG_LEVEL_DEBUG );
6959 debug( ' customer_trx_line_id='||to_char(p_customer_trx_line_id),
6960 MSG_LEVEL_DEBUG );
6961 debug( ' cust_trx_line_salesrep_id='||
6962 to_char(p_cust_trx_line_salesrep_id),
6963 MSG_LEVEL_DEBUG );
6964 debug( ' request_id='||to_char(p_request_id), MSG_LEVEL_DEBUG );
6965 debug( ' gl_date='||to_char(p_gl_date,'MM/DD/YYYY'), MSG_LEVEL_DEBUG );
6966 debug( ' original_gl_date='||to_char(p_original_gl_date,'MM/DD/YYYY'), MSG_LEVEL_DEBUG );
6967 debug( ' total_trx_amount='||to_char(p_total_trx_amount),
6968 MSG_LEVEL_DEBUG );
6969 debug( ' passed_ccid='||to_char(p_passed_ccid), MSG_LEVEL_DEBUG );
6970 debug( ' force_account_set_no='||p_force_account_set_no,
6971 MSG_LEVEL_DEBUG );
6972 debug( ' cust_trx_type_id='||to_char(p_cust_trx_type_id),
6973 MSG_LEVEL_DEBUG );
6974 debug( ' primary_salesrep_id='||to_char(p_primary_salesrep_id),
6975 MSG_LEVEL_DEBUG );
6976 debug( ' inventory_item_id='||to_char(p_inventory_item_id),
6977 MSG_LEVEL_DEBUG );
6978 debug( ' memo_line_id='||to_char(p_memo_line_id), MSG_LEVEL_DEBUG );
6979 debug( ' warehouse_id='||to_char(p_warehouse_id), MSG_LEVEL_DEBUG );
6980 debug( ' msg_level='||to_char(system_info.msg_level), MSG_LEVEL_DEBUG );
6981
6982 --
6983 -- Adjust account_class to proper string
6984 --
6985 l_account_class := p_account_class;
6986 expand_account_class( l_account_class );
6987
6988 --
6989 --Get the billing site use id so this does not require to be passed
6990 --as a parameter to the do_autoaccounting procedure
6991 --
6992 IF ( p_customer_trx_id IS NOT NULL) THEN
6993 SELECT t.bill_to_site_use_id
6994 INTO l_bill_to_site_use_id
6995 FROM ra_customer_trx t
6996 WHERE t.customer_trx_id = p_customer_trx_id;
6997 END IF;
6998
6999 --
7000 -- Mode will always be get as this routine is specifically
7001 -- written for the same
7002 --
7003 do_autoaccounting_internal(
7004 p_mode,
7005 l_account_class,
7006 p_customer_trx_id,
7007 p_customer_trx_line_id,
7008 p_cust_trx_line_salesrep_id,
7009 p_request_id,
7010 p_gl_date,
7011 p_original_gl_date,
7012 p_total_trx_amount,
7013 p_passed_ccid,
7014 p_force_account_set_no,
7015 p_cust_trx_type_id,
7016 p_primary_salesrep_id,
7017 p_inventory_item_id,
7018 p_memo_line_id,
7019 l_bill_to_site_use_id,
7020 p_warehouse_id,
7021 p_ccid,
7022 p_concat_segments,
7023 p_failure_count );
7024
7025 -- Check if failed to get any ccids
7026 --
7027 debug( ' p_failure_count='||to_char(p_failure_count) ,
7028 MSG_LEVEL_DEBUG);
7029
7030 IF( p_failure_count > 0 ) THEN
7031
7032 debug( ' raising no_ccid', MSG_LEVEL_DEBUG );
7033 RAISE no_ccid;
7034
7035 END IF;
7036
7037 print_fcn_label( 'arp_auto_accounting.do_autoaccounting() overloaded get mode -' );
7038
7039
7040 EXCEPTION
7041 WHEN no_ccid OR NO_DATA_FOUND THEN
7042
7043 IF( p_mode = G OR
7044 p_request_id IS NOT NULL ) THEN
7045
7046 NULL; -- Don't raise for Get mode or Autoinvoice,
7047 -- otherwise the IN/OUT variables
7048 -- ccid, concat_segments and failure_count
7049 -- do not get populated.
7050 ELSE
7051 RAISE;
7052 END IF;
7053
7054 WHEN OTHERS THEN
7055 debug( 'EXCEPTION: arp_auto_accounting.do_autoaccounting()',
7056 MSG_LEVEL_BASIC );
7057 debug(SQLERRM, MSG_LEVEL_BASIC);
7058
7059 IF( sqlcode = 1 ) THEN
7060 --
7061 -- User-defined exception
7062 --
7063 FND_MESSAGE.set_name( 'AR', 'GENERIC_MESSAGE' );
7064 FND_MESSAGE.set_token( 'GENERIC_TEXT', g_error_buffer );
7065 APP_EXCEPTION.raise_exception;
7066
7067 ELSE
7068 --
7069 -- Oracle error
7070 --
7071 g_error_buffer := SQLERRM;
7072
7073 RAISE;
7074
7075 END IF;
7076
7077 END do_autoaccounting;
7078
7079 ------------------------------------------------------------------------------
7080 -- Test programs
7081 ------------------------------------------------------------------------------
7082 PROCEDURE test_load IS
7083 BEGIN
7084 -- enable_debug;
7085 --
7086 -- dump REV
7087 --
7088 debug('**REV**');
7089 FOR i IN 0..rev_count-1 LOOP
7090 debug(autoacc_def_segment_t( rev_offset + i ));
7091 debug(autoacc_def_table_t( rev_offset + i ));
7092 debug(autoacc_def_const_t( rev_offset + i ));
7093 END LOOP;
7094 --
7095 -- dump REC
7096 --
7097 debug('**REC**');
7098 FOR i in 0..rev_count-1 LOOP
7099 debug(autoacc_def_segment_t( rec_offset + i ));
7100 debug(autoacc_def_table_t( rec_offset + i ));
7101 debug(autoacc_def_const_t( rec_offset + i ));
7102 END LOOP;
7103 --
7104 -- dump FRT
7105 --
7106 debug('**FREIGHT**');
7107 FOR i in 0..rev_count-1 LOOP
7108 debug(autoacc_def_segment_t( frt_offset + i ));
7109 debug(autoacc_def_table_t( frt_offset + i ));
7110 debug(autoacc_def_const_t( frt_offset + i ));
7111 END LOOP;
7112 --
7113 -- dump TAX
7114 --
7115 debug('**TAX**');
7116 FOR i in 0..rev_count-1 LOOP
7117 debug(autoacc_def_segment_t( tax_offset + i ));
7118 debug(autoacc_def_table_t( tax_offset + i ));
7119 debug(autoacc_def_const_t( tax_offset + i ));
7120 END LOOP;
7121 --
7122 -- dump UNBILL
7123 --
7124 debug('**UNBILL**');
7125 FOR i in 0..rev_count-1 LOOP
7126 debug(autoacc_def_segment_t( unbill_offset + i ));
7127 debug(autoacc_def_table_t( unbill_offset + i ));
7128 debug(autoacc_def_const_t( unbill_offset + i ));
7129 END LOOP;
7130 --
7131 -- dump UNEARN
7132 --
7133 debug('**UNEARN**');
7134 FOR i in 0..rev_count-1 LOOP
7135 debug(autoacc_def_segment_t( unearn_offset + i ));
7136 debug(autoacc_def_table_t( unearn_offset + i ));
7137 debug(autoacc_def_const_t( unearn_offset + i ));
7138 END LOOP;
7139 --
7140 -- dump SUSPENSE
7141 --
7142 debug('**SUSPENSE**');
7143 FOR i in 0..rev_count-1 LOOP
7144 debug(autoacc_def_segment_t( suspense_offset + i ));
7145 debug(autoacc_def_table_t( suspense_offset + i ));
7146 debug(autoacc_def_const_t( suspense_offset + i ));
7147 END LOOP;
7148
7149 EXCEPTION
7150 WHEN OTHERS THEN
7151 debug(SQLERRM);
7152 debug('arp_auto_accounting.test_load()');
7153 RAISE;
7154 END test_load;
7155
7156 ----------------------------------------------------------------------------
7157 PROCEDURE test_query( p_account_class IN VARCHAR2,
7158 p_table_name IN VARCHAR2 ) IS
7159 BEGIN
7160 -- enable_debug;
7161 IF( query_autoacc_def(p_account_class, p_table_name)) THEN
7162 debug('YES');
7163 ELSE
7164 debug('NO');
7165 END IF;
7166
7167 EXCEPTION
7168 WHEN OTHERS THEN
7169 debug('arp_auto_accounting.test_query()');
7170 debug(SQLERRM);
7171 RAISE;
7172 END test_query;
7173
7174 ----------------------------------------------------------------------------
7175 PROCEDURE test_find( p_trx_type_id IN NUMBER,
7176 p_salesrep_id IN NUMBER,
7177 p_inv_item_id IN NUMBER,
7178 p_memo_line_id IN NUMBER) IS
7179
7180 l_ccid_rev BINARY_INTEGER;
7181 l_ccid_rec BINARY_INTEGER;
7182 l_ccid_frt BINARY_INTEGER;
7183 l_ccid_tax BINARY_INTEGER;
7184 l_ccid_unbill BINARY_INTEGER;
7185 l_ccid_unearn BINARY_INTEGER;
7186 l_ccid_suspense BINARY_INTEGER;
7187 l_inv_item_type mtl_system_items.item_type%TYPE;
7188
7189 BEGIN
7190 -- enable_debug;
7191
7192 if( p_trx_type_id <> -1 ) then
7193 get_trx_type_ccids(p_trx_type_id, l_ccid_rev, l_ccid_rec, l_ccid_frt,
7194 l_ccid_tax, l_ccid_unbill, l_ccid_unearn,
7195 l_ccid_suspense);
7196 debug( 'TRX_TYPE_ID='||to_char(p_trx_type_id)||
7197 ' rev:'||to_char(l_ccid_rev)||
7198 ' rec:'||to_char(l_ccid_rec)||
7199 ' frt:'||to_char(l_ccid_frt)||' tax:'||to_char(l_ccid_tax)||
7200 ' unbill:'||to_char(l_ccid_unbill)||
7201 ' unearn:'||to_char(l_ccid_unearn)||
7202 ' suspense:'||to_char(l_ccid_suspense) );
7203
7204 end if;
7205
7206 if( p_salesrep_id <> -1 ) then
7207 get_salesrep_ccids(p_salesrep_id, l_ccid_rev, l_ccid_rec, l_ccid_frt);
7208 debug( 'SALESREP_ID='||to_char(p_salesrep_id)||
7209 ' rev:'||to_char(l_ccid_rev)||
7210 ' rec:'||to_char(l_ccid_rec)||
7211 ' frt:'||to_char(l_ccid_frt));
7212 end if;
7213
7214 if( p_inv_item_id <> -1 ) then
7215 get_inv_item_ccids(profile_info,
7216 p_inv_item_id,
7217 '',
7218 l_ccid_rev, l_inv_item_type );
7219 debug( 'ORG_ID='||oe_profile.value('SO_ORGANIZATION_ID',arp_global.sysparam.org_id)||
7220 ' INV_ITEM_ID='||to_char(p_inv_item_id)||
7221 ' rev:'||to_char(l_ccid_rev) );
7222 end if;
7223
7224 if( p_memo_line_id <> -1 ) then
7225 get_memo_line_ccids(p_memo_line_id, l_ccid_rev);
7226 debug( ' MEMO_LINE_ID='||to_char(p_memo_line_id)||
7227 ' rev:'||to_char(l_ccid_rev) );
7228 end if;
7229
7230 EXCEPTION
7231 WHEN OTHERS THEN
7232 debug('arp_auto_accounting.test_find()');
7233 debug(SQLERRM);
7234 RAISE;
7235 END test_find;
7236
7237 ----------------------------------------------------------------------------
7238 PROCEDURE test_assembly IS
7239
7240 l_ccid_record ccid_rec_type;
7241 l_concat varchar2(800);
7242 l_ccid binary_integer;
7243 l_int_concat varchar2(800);
7244 l_int_ccid binary_integer;
7245 l_inv_item_type mtl_system_items.item_type%TYPE;
7246
7247 BEGIN
7248
7249 -- enable_debug;
7250
7251 l_ccid_record.trx_type_ccid_rev := 1098;
7252 l_ccid_record.trx_type_ccid_rec := 1137;
7253 l_ccid_record.trx_type_ccid_frt := 1137;
7254 l_ccid_record.trx_type_ccid_tax := 1137;
7255 l_ccid_record.trx_type_ccid_unbill := 1137;
7256 l_ccid_record.trx_type_ccid_unearn := 1137;
7257 l_ccid_record.trx_type_ccid_suspense := 1137;
7258 l_ccid_record.salesrep_ccid_rev := 1173;
7259 l_ccid_record.salesrep_ccid_rec := 1137;
7260 l_ccid_record.salesrep_ccid_frt := 1137;
7261 l_ccid_record.lineitem_ccid_rev := 1232;
7262 l_ccid_record.tax_ccid_tax := 1137;
7263 l_ccid_record.agreecat_ccid_rev := 1137;
7264
7265 assemble_code_combination( system_info,
7266 flex_info,
7267 REV,
7268 l_ccid_record,
7269 l_inv_item_type,
7270 l_ccid,
7271 l_concat,
7272 l_int_ccid,
7273 l_int_concat );
7274 debug(l_concat);
7275 debug(to_char(l_ccid));
7276
7277 debug(l_int_concat);
7278 debug(to_char(l_int_ccid));
7279
7280 EXCEPTION
7281 WHEN OTHERS THEN
7282 debug('arp_auto_accounting.test_assembly()');
7283 debug(SQLERRM);
7284 RAISE;
7285 END test_assembly;
7286
7287 ----------------------------------------------------------------------------
7288 PROCEDURE test_harness IS
7289
7290 l_segs VARCHAR2(200);
7291 l_ccid BINARY_INTEGER;
7292 l_int_segs VARCHAR2(200);
7293 l_int_ccid BINARY_INTEGER;
7294 l_errorbuf VARCHAR2(512);
7295 l_x BINARY_INTEGER;
7296
7297 BEGIN
7298
7299 -- enable_debug;
7300
7301 -- Invalid account class
7302 --
7303 BEGIN
7304 flex_manager( 'REVX', '', 1011, 1000, '', '', '', '', '','',
7305 l_ccid,
7306 l_segs,
7307 l_int_ccid,
7308 l_int_segs );
7309 EXCEPTION
7310 WHEN invalid_account_class THEN
7311 debug('invalid account class test PASSED');
7312 WHEN OTHERS THEN
7313 debug('invalid account class test FAILED');
7314 END;
7315
7316
7317 -- Invalid trx_type_id
7318 --
7319 BEGIN
7320 flex_manager( REV, '', -1011, 1000, '', '', '', '', '','',
7321 l_ccid,
7322 l_segs,
7323 l_int_ccid,
7324 l_int_segs );
7325
7326 debug('invalid trx_type_id test FAILED');
7327
7328 EXCEPTION
7329 WHEN NO_DATA_FOUND THEN
7330 debug('invalid trx_type_id test PASSED');
7331 WHEN OTHERS THEN
7332 debug('invalid trx_type_id test FAILED');
7333 END;
7334
7335
7336 -- Invalid salesrep_id
7337 --
7338 BEGIN
7339 flex_manager( REV, '', 1011, -1000, '', '', '', '', '','',
7340 l_ccid,
7341 l_segs,
7342 l_int_ccid,
7343 l_int_segs );
7344
7345 debug('invalid salesrep_id test FAILED');
7346
7347 EXCEPTION
7348 WHEN NO_DATA_FOUND THEN
7349 debug('invalid salesrep_id test PASSED');
7350 WHEN OTHERS THEN
7351 debug('invalid salesrep_id test FAILED');
7352 END;
7353
7354
7355 -- Invalid inv_item_id
7356 --
7357 BEGIN
7358 flex_manager( REV, '', 1011, 1000, -1, '', '', '', '','',
7359 l_ccid,
7360 l_segs,
7361 l_int_ccid,
7362 l_int_segs );
7363
7364 debug('invalid inv_item_id test FAILED');
7365
7366 EXCEPTION
7367 WHEN NO_DATA_FOUND THEN
7368 debug('invalid inv_item_id test PASSED');
7369 WHEN OTHERS THEN
7370 debug('invalid inv_item_id test FAILED');
7371 END;
7372
7373
7374 -- Invalid memo_line_id
7375 --
7376 BEGIN
7377 flex_manager( REV, '', 1011, 1000, '', -1, '', '', '','',
7378 l_ccid,
7379 l_segs,
7380 l_int_ccid,
7381 l_int_segs );
7382
7383 debug('invalid memo_line_id test FAILED');
7384
7385 EXCEPTION
7386 WHEN NO_DATA_FOUND THEN
7387 debug('invalid memo_line_id test PASSED');
7388 WHEN OTHERS THEN
7389 debug('invalid memo_line_id test FAILED');
7390 END;
7391
7392
7393 -- Pass both inv_item_id and memo_line_id
7394 --
7395 BEGIN
7396 flex_manager( REV, '', 1011, 1000, -1, -1, '', '', '','',
7397 l_ccid,
7398 l_segs,
7399 l_int_ccid,
7400 l_int_segs );
7401
7402 debug('item_and_memo_both_not_null test FAILED');
7403
7404 EXCEPTION
7405 WHEN item_and_memo_both_not_null THEN
7406 debug('item_and_memo_both_not_null test PASSED');
7407 WHEN OTHERS THEN
7408 debug('item_and_memo_both_not_null test FAILED');
7409 END;
7410
7411
7412 -- Invalid ccid_tax
7413 --
7414 BEGIN
7415 flex_manager( TAX, '', 1011, 1000, '', '', 1, '', '','',
7416 l_ccid,
7417 l_segs,
7418 l_int_ccid,
7419 l_int_segs );
7420
7421 debug('invalid ccid_tax test FAILED');
7422
7423 EXCEPTION
7424 WHEN NO_DATA_FOUND THEN
7425 debug('invalid ccid_tax test PASSED');
7426 WHEN OTHERS THEN
7427 debug('invalid ccid_tax test FAILED');
7428 END;
7429
7430
7431 -- Test REV (ccid not found)
7432 --
7433 BEGIN
7434 flex_manager( REV, '', 1011, 1000, '', '', '', '', '','',
7435 l_ccid,
7436 l_segs,
7437 l_int_ccid,
7438 l_int_segs );
7439
7440 IF( l_ccid = -1 AND l_segs = '01.100.4300.0.000.000' ) THEN
7441 debug('REV (ccid not found) test PASSED');
7442 ELSE
7443 debug('REV (ccid not found) test FAILED');
7444 END IF;
7445
7446 EXCEPTION
7447 WHEN OTHERS THEN
7448 debug('REV (ccid not found) test FAILED');
7449 END;
7450
7451
7452 -- Test REV (ccid found)
7453 --
7454 BEGIN
7455 flex_manager( REV, '', 1011, 1000, 1, '', '', '', '','',
7456 l_ccid,
7457 l_segs,
7458 l_int_ccid,
7459 l_int_segs );
7460
7461 IF( l_ccid = 1137 AND l_segs = '01.100.4300.000.000.000' ) THEN
7462 debug('REV (ccid found) test PASSED');
7463 ELSE
7464 debug('REV (ccid found) test FAILED');
7465 END IF;
7466
7467 EXCEPTION
7468 WHEN OTHERS THEN
7469 debug('REV (ccid found) test FAILED');
7470 END;
7471
7472
7473 -- Test REV (ccid found)
7474 --
7475 BEGIN
7476 flex_manager( REV, '', 1011, 1000, '', 1001, '', '', '','',
7477 l_ccid,
7478 l_segs,
7479 l_int_ccid,
7480 l_int_segs );
7481
7482 IF( l_ccid = 1137 AND l_segs = '01.100.4300.000.000.000' ) THEN
7483 debug('REV (ccid found) test PASSED');
7484 ELSE
7485 debug('REV (ccid found) test FAILED');
7486 END IF;
7487
7488 EXCEPTION
7489 WHEN OTHERS THEN
7490 debug('REV (ccid found) test FAILED');
7491 END;
7492
7493
7494 -- Test CHARGES (ccid found)
7495 --
7496 BEGIN
7497 flex_manager( CHARGES, '', 1011, 1000, 1, '', '', '', '','',
7498 l_ccid,
7499 l_segs,
7500 l_int_ccid,
7501 l_int_segs );
7502
7503 IF( l_ccid = 1137 AND l_segs = '01.100.4300.000.000.000' ) THEN
7504 debug('CHARGES (ccid found) test PASSED');
7505 ELSE
7506 debug('CHARGES (ccid found) test FAILED');
7507 END IF;
7508
7509 EXCEPTION
7510 WHEN OTHERS THEN
7511 debug('CHARGES (ccid found) test FAILED');
7512 END;
7513
7514
7515 -- Test FREIGHT (ccid found)
7516 --
7517 BEGIN
7518 flex_manager( FREIGHT, '', 1011, 1000, 1, '', '', '', '','',
7519 l_ccid,
7520 l_segs,
7521 l_int_ccid,
7522 l_int_segs );
7523
7524 IF( l_ccid = 1098 AND l_segs = '01.100.5650.000.000.000' ) THEN
7525 debug('FREIGHT (ccid found) test PASSED');
7526 ELSE
7527 debug('FREIGHT (ccid found) test FAILED');
7528 END IF;
7529
7530 EXCEPTION
7531 WHEN OTHERS THEN
7532 debug('FREIGHT (ccid found) test FAILED');
7533 END;
7534
7535
7536 -- Test TAX (ccid found)
7537 --
7538 BEGIN
7539 flex_manager( TAX, '', 1011, 1001, 1, '', 1098, '', '','',
7540 l_ccid,
7541 l_segs,
7542 l_int_ccid,
7543 l_int_segs );
7544
7545 IF( l_ccid = 1215 AND l_segs = '01.500.5650.000.000.000' ) THEN
7546 debug('TAX (ccid found) test PASSED');
7547 ELSE
7548 debug('TAX (ccid found) test FAILED');
7549 END IF;
7550
7551 EXCEPTION
7552 WHEN OTHERS THEN
7553 debug('TAX (ccid found) test FAILED');
7554 END;
7555
7556
7557 -- Test UNBILL (ccid found)
7558 --
7559 BEGIN
7560 flex_manager( UNBILL, '', 1011, 1001, 1, '', '', '', '','',
7561 l_ccid,
7562 l_segs,
7563 l_int_ccid,
7564 l_int_segs );
7565
7566 IF( l_ccid = 1213 AND l_segs = '01.500.1103.000.000.000' ) THEN
7567 debug('UNBILL (ccid found) test PASSED');
7568 ELSE
7569 debug('UNBILL (ccid found) test FAILED');
7570 END IF;
7571
7572 EXCEPTION
7573 WHEN OTHERS THEN
7574 debug('UNBILL (ccid found) test FAILED');
7575 END;
7576
7577 -- Test UNEARN (ccid found)
7578 --
7579 BEGIN
7580 flex_manager( UNEARN, '', 1011, 1001, 1, '', '', '', '','',
7581 l_ccid,
7582 l_segs,
7583 l_int_ccid,
7584 l_int_segs );
7585
7586 IF( l_ccid = 1216 AND l_segs = '01.500.4400.000.000.000' ) THEN
7587 debug('UNEARN (ccid found) test PASSED');
7588 ELSE
7589 debug('UNEARN (ccid found) test FAILED');
7590 END IF;
7591
7592 EXCEPTION
7593 WHEN OTHERS THEN
7594 debug('UNEARN (ccid found) test FAILED');
7595 END;
7596
7597 -- Test SUSPENSE (ccid found)
7598 --
7599 BEGIN
7600 flex_manager( SUSPENSE, '', 1011, 1000, 2, '', '', '', '','',
7601 l_ccid,
7602 l_segs,
7603 l_int_ccid,
7604 l_int_segs );
7605
7606 IF( l_ccid = 1111 AND l_segs = '01.100.5999.000.000.000' ) THEN
7607 debug('SUSPENSE (ccid found) test PASSED');
7608 ELSE
7609 debug('SUSPENSE (ccid found) test FAILED');
7610 END IF;
7611
7612 EXCEPTION
7613 WHEN OTHERS THEN
7614 debug('SUSPENSE (ccid found) test FAILED');
7615 END;
7616
7617 EXCEPTION
7618 WHEN OTHERS THEN
7619 debug('arp_auto_accounting.test_harness()');
7620 debug(SQLERRM);
7621 RAISE;
7622 END test_harness;
7623
7624 ----------------------------------------------------------------------------
7625 PROCEDURE test_wes IS
7626
7627 l_segs VARCHAR2(200);
7628 l_ccid BINARY_INTEGER;
7629 l_int_segs VARCHAR2(200);
7630 l_int_ccid BINARY_INTEGER;
7631 l_errorbuf VARCHAR2(512);
7632 l_x BINARY_INTEGER;
7633
7634 BEGIN
7635
7636 -- enable_debug;
7637
7638 -- Invalid account class
7639 --
7640
7641 BEGIN
7642 null;
7643 flex_manager( 'REVX', '', 1011, 1000, '', '', '', '', '','', l_ccid, l_segs, l_int_ccid, l_int_segs );
7644
7645 EXCEPTION
7646 WHEN invalid_account_class THEN
7647 debug('invalid account class test PASSED');
7648 WHEN OTHERS THEN
7649 debug('invalid account class test FAILED');
7650
7651 END;
7652
7653
7654 EXCEPTION
7655 WHEN OTHERS THEN
7656 debug('arp_auto_accounting.test_harness()');
7657 debug(SQLERRM);
7658 RAISE;
7659 END test_wes ;
7660
7661
7662 ----------------------------------------------------------------------------
7663 PROCEDURE test_build_sql IS
7664
7665 select_stmt VARCHAR2(32767);
7666 delete_stmt VARCHAR2(32767);
7667 mycursor integer;
7668
7669 BEGIN
7670
7671 -- enable_debug;
7672 select_stmt :=
7673 build_select_sql(system_info, profile_info,
7674 REV, 1, 2, 3, 12,
7675 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL );
7676
7677
7678 disable_debug;
7679
7680 EXCEPTION
7681 WHEN OTHERS THEN
7682 debug('EXCEPTION: arp_auto_accounting.test_build_sql()');
7683 debug(SQLERRM);
7684 RAISE;
7685 END test_build_sql;
7686
7687 ----------------------------------------------------------------------------
7688 PROCEDURE test_do_autoacc IS
7689
7690 ccid BINARY_INTEGER;
7691 concat_segments VARCHAR2(1000);
7692 x binary_integer;
7693 y binary_integer;
7694 errorbuf VARCHAR2(1000);
7695
7696 BEGIN
7697
7698 -- enable_debug;
7699
7700 debug('Insert Mode');
7701 do_autoaccounting('I', REV, 1072, 1511, null, null,
7702 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
7703 ccid, concat_segments, y );
7704
7705 debug('Update Mode');
7706 do_autoaccounting('U', REV, 1, 2, 3, 12,
7707 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
7708 ccid, concat_segments, y );
7709
7710 debug('Delete Mode');
7711 do_autoaccounting('D', REV, 1, 2, 3, 12,
7712 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
7713 ccid, concat_segments, y );
7714
7715 debug('Get Mode');
7716 do_autoaccounting('G', REV, 1, 2, 3, 12,
7717 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
7718 ccid, concat_segments, y );
7719
7720 debug('Insert Mode: ALL');
7721 do_autoaccounting('I', 'ALL', 1072, 1511, null, null,
7722 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
7723 ccid, concat_segments, y );
7724
7725 disable_debug;
7726
7727 EXCEPTION
7728 WHEN OTHERS THEN
7729 debug('EXCEPTION: arp_auto_accounting.test_do_autoacc()');
7730 debug(SQLERRM);
7731 RAISE;
7732 END test_do_autoacc;
7733
7734
7735
7736
7737 ----------------------------------------------------------------------------
7738 -- Constructor code
7739 ----------------------------------------------------------------------------
7740 --begin anuj
7741 /* Multi-Org Access Control Changes for SSA;Begin;anukumar;11/01/2002*/
7742 PROCEDURE init is
7743 BEGIN
7744 print_fcn_label( 'arp_auto_accounting.constructor()+' );
7745
7746 ------------------------------------------------------------------------
7747 -- Load autoaccounting definition into plsql tables
7748 ------------------------------------------------------------------------
7749 load_autoacc_def;
7750 system_info := arp_trx_global.system_info;
7751
7752 /* Bug 2560036 - determine if collectibility is enabled */
7753 g_test_collectibility :=
7754 ar_revenue_management_pvt.revenue_management_enabled;
7755
7756 ------------------------------------------------------------------------
7757 -- Additional system info
7758 ------------------------------------------------------------------------
7759 BEGIN
7760
7761 system_info.rev_based_on_salesrep :=
7762 query_autoacc_def( REV, 'RA_SALESREPS' );
7763
7764 system_info.tax_based_on_salesrep :=
7765 query_autoacc_def( TAX, 'RA_SALESREPS' );
7766
7767 system_info.unbill_based_on_salesrep :=
7768 query_autoacc_def( UNBILL, 'RA_SALESREPS' );
7769
7770 system_info.unearn_based_on_salesrep :=
7771 query_autoacc_def( UNEARN, 'RA_SALESREPS' );
7772
7773 system_info.suspense_based_on_salesrep :=
7774 query_autoacc_def( SUSPENSE, 'RA_SALESREPS' );
7775
7776
7777 EXCEPTION
7778 WHEN OTHERS THEN
7779 arp_util.debug('Error getting system information');
7780 RAISE;
7781 END;
7782
7783
7784 get_error_message_text;
7785
7786 dump_info;
7787
7788 print_fcn_label( 'arp_auto_accounting.constructor()-' );
7789
7790 EXCEPTION
7791 WHEN OTHERS THEN
7792 debug('EXCEPTION: arp_auto_accounting.constructor');
7793 debug(SQLERRM);
7794 RAISE;
7795 END init;
7796 BEGIN
7797 init;
7798 /* Multi-Org Access Control Changes for SSA;Begin;anukumar;11/01/2002*/
7799 --end anuj
7800
7801 END arp_auto_accounting;