[Home] [Help]
PACKAGE BODY: APPS.ARP_TRANS_FLEX
Source
1 PACKAGE BODY ARP_TRANS_FLEX AS
2 /* $Header: ARTUFLXB.pls 115.7 2002/11/18 22:56:06 anukumar ship $ */
3
4
5 TYPE flex_context_type IS TABLE OF
6 fnd_descr_flex_contexts.descriptive_flex_context_code%type
7 INDEX by binary_integer;
8
9 TYPE flex_num_type IS TABLE OF number INDEX by binary_integer;
10
11 TYPE seg_value_type IS TABLE OF
12 ra_interface_lines.interface_line_attribute1%type
13 INDEX by binary_integer;
14
15 TYPE cursor_tbl_type IS
16 TABLE OF BINARY_INTEGER
17 INDEX BY BINARY_INTEGER;
18
19 /*-------------------------------------------------------------------------+
20 | Contexts Num_Segs Start_Loc Active Segments |
21 | |
22 | Ctx1 2 1 1 |
23 | 2 |
24 | Ctx2 1 3 5 |
25 | Ctx3 0 3 |
26 +-------------------------------------------------------------------------*/
27 pg_flex_contexts flex_context_type; -- flex context values
28 pg_ctl_cursors cursor_tbl_type; -- cursors for ra_customer_trx_lines
29 pg_ril_cursors cursor_tbl_type; -- cursors for ra_interface_lines
30 pg_active_segs flex_num_type; -- active segment numbers
31 pg_num_segs flex_num_type; -- number of segments for each context
32 pg_start_loc flex_num_type; -- for a context, index to first
33 -- segment in pg_active_segs
34 pg_ctx_count number; -- total number of contexts
35
36
37 pg_char_dummy varchar2(10) := '!#$%^&*';
38
39 /*===========================================================================+
40 | PROCEDURE |
41 | setup_descr_flex |
42 | |
43 | DESCRIPTION |
44 | Setup the flex cache. Gets the active contexts and segments for a |
45 | descriptive flex |
46 | |
47 | SCOPE - PRIVATE |
48 | |
49 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
50 | arp_util.debug |
51 | |
52 | ARGUMENTS : IN: |
53 | p_appl_id |
54 | p_desc_flex |
55 | OUT: |
56 | None |
57 | IN/ OUT: |
58 | None |
59 | |
60 | RETURNS : NONE |
61 | |
62 | NOTES |
63 | |
64 | MODIFICATION HISTORY |
65 | 24-OCT-95 Subash Chadalavada Created |
66 | |
67 +===========================================================================*/
68
69 PROCEDURE setup_descr_flex(
70 p_appl_id IN number,
71 p_desc_flex IN varchar2)
72 IS
73
74 cursor sel_active_segs( pc_appl_id in number,
75 pc_desc_flex in varchar2) is
76 select ctx.descriptive_flex_context_code context_code,
77 substr(seg.application_column_name,
78 length('INTERFACE_LINE_ATTRIBUTE')+1) segment_num
79 from fnd_descr_flex_contexts ctx,
80 fnd_descr_flex_column_usages seg
81 where ctx.application_id = pc_appl_id
82 and ctx.descriptive_flexfield_name = pc_desc_flex
83 and ctx.application_id = seg.application_id(+)
84 and ctx.descriptive_flexfield_name =
85 seg.descriptive_flexfield_name(+)
86 and ctx.descriptive_flex_context_code =
87 seg.descriptive_flex_context_code(+)
88 and seg.enabled_flag (+) = 'Y'
89 order by ctx.descriptive_flex_context_code, seg.column_seq_num;
90
91 l_prior_context fnd_descr_flex_contexts.descriptive_flex_context_code%type;
92 l_ctx_count number;
93 l_seg_count number;
94 l_total_seg_count number;
95 l_seg_number number;
96
97 BEGIN
98
99 l_prior_context := pg_char_dummy;
100 l_ctx_count := 0;
101 l_total_seg_count := 1;
102
103 arp_util.debug('arp_trans_flex.setup_descr_flex()+');
104
105 /*----------------------------------------------------------------------+
106 | get the active segment numbers for the transaction flex and store |
107 | the information in package globals |
108 +----------------------------------------------------------------------*/
109
110 FOR segs IN sel_active_segs(p_appl_id, p_desc_flex)
111 LOOP
112
113 IF (segs.context_code <> l_prior_context)
114 THEN
115
116 l_seg_count := 0;
117 l_ctx_count := l_ctx_count + 1;
118 l_prior_context := segs.context_code;
119 pg_num_segs(l_ctx_count) := 0;
120
121 pg_flex_contexts(l_ctx_count) := segs.context_code;
122 pg_start_loc(l_ctx_count) := l_total_seg_count;
123
124 END IF;
125
126 IF (segs.segment_num IS NOT NULL)
127 THEN
128 l_seg_count := l_seg_count + 1;
129
130 pg_active_segs(l_total_seg_count) := segs.segment_num;
131
132 l_total_seg_count := l_total_seg_count + 1;
133 pg_num_segs(l_ctx_count) := l_seg_count;
134
135 END IF;
136 END LOOP;
137
138 pg_ctx_count := l_ctx_count;
139
140 arp_util.debug('arp_trans_flex.setup_descr_flex()-');
141
142 EXCEPTION
143 WHEN OTHERS THEN
144 arp_util.debug('EXCEPTION : arp_trans_flex.setup_descr_flex()');
145 arp_util.debug('p_appl_id : '||p_appl_id);
146 arp_util.debug('p_desc_flex : '||p_desc_flex);
147
148 RAISE;
149 END;
150
151
152 /*===========================================================================+
153 | PROCEDURE |
154 | print_cache_contents |
155 | |
156 | DESCRIPTION |
157 | Prints the contents of the cache. Used for debugging purposes |
158 | |
159 | SCOPE - PRIVATE |
160 | |
161 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
162 | arp_util.debug |
163 | |
164 | ARGUMENTS : IN: |
165 | None |
166 | OUT: |
167 | None |
168 | IN/ OUT: |
169 | None |
170 | |
171 | RETURNS : NONE |
172 | |
173 | NOTES |
174 | |
175 | MODIFICATION HISTORY |
176 | 24-OCT-95 Subash Chadalavada Created |
177 | |
178 +===========================================================================*/
179 PROCEDURE print_cache_contents
180 IS
181 l_loc number;
182 BEGIN
183
184 arp_util.debug('arp_trans_flex.print_cache_contents()+');
185
186 arp_util.debug('Number of contexts : '||to_char(pg_ctx_count));
187
188
189 FOR i IN 1..pg_ctx_count LOOP
190
191 arp_util.debug('Context : '||pg_flex_contexts(i)||
192 ' Segs : '||to_char(pg_num_segs(i))||
193 ' Start : '||to_char(pg_start_loc(i)));
194
195 FOR j IN 1..pg_num_segs(i) LOOP
196
197 l_loc := pg_start_loc(i) + j - 1;
198
199 arp_util.debug(' '||pg_active_segs(l_loc));
200
201 END LOOP;
202 END LOOP;
203
204 arp_util.debug('arp_trans_flex.print_cache_contents()-');
205
206 EXCEPTION
207 WHEN OTHERS THEN
208 arp_util.debug('EXCEPTION: arp_trans_flex.print_cache_contents()');
209 RAISE;
210 END;
211
212 /*===========================================================================+
213 | FUNCTION |
214 | find_context |
215 | |
216 | DESCRIPTION |
217 | Search for the context in the cache and return its location in the |
218 | cache table |
219 | |
220 | SCOPE - PRIVATE |
221 | |
222 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
223 | arp_util.debug |
224 | |
225 | ARGUMENTS : IN: |
226 | p_context |
227 | OUT: |
228 | None |
229 | IN/ OUT: |
230 | None |
231 | |
232 | RETURNS : NUMBER index to cache table |
233 | |
234 | NOTES |
235 | |
236 | MODIFICATION HISTORY |
237 | 24-OCT-95 Subash Chadalavada Created |
238 | |
239 +===========================================================================*/
240
241 FUNCTION find_context(p_context IN varchar2) RETURN number
242 IS
243 BEGIN
244
245 FOR i IN 1..pg_ctx_count LOOP
246
247 IF pg_flex_contexts(i) = p_context
248 THEN
249 return(i);
250 END IF;
251
252 END LOOP;
253
254 return(0);
255
256 EXCEPTION
257 WHEN OTHERS THEN
258 arp_util.debug('EXCEPTION : arp_trans_flex.find_context()');
259 arp_util.debug('p_context : '||p_context);
260
261 RAISE;
262 END;
263
264
265 /*===========================================================================+
266 | PROCEDURE |
267 | build_where_clause |
268 | |
269 | DESCRIPTION |
270 | Constructs the where clause based on the context and the descr. flex |
271 | values |
272 | |
273 | SCOPE - PRIVATE |
274 | |
275 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
276 | arp_util.debug |
277 | |
278 | ARGUMENTS : IN: |
279 | p_context |
280 | p_context_index |
281 | OUT: |
282 | None |
283 | IN/ OUT: |
284 | p_where_clause |
285 | |
286 | RETURNS : NONE |
287 | |
288 | NOTES |
289 | The WHERE clause is constructed as follows |
290 | |
291 | If the value of the attribute parameter that corresponds to a segment |
292 | is NULL then |
293 | INTERFACE_LINE_ATTRIBUTE<seg_num> IS NULL |
294 | |
295 | Otherwise |
299 | 24-OCT-95 Subash Chadalavada Created |
296 | INTERFACE_LINE_ATTRIBUTE<seg_num> = <value passed> |
297 | |
298 | MODIFICATION HISTORY |
300 | 11-FEB-97 Charlie Tomberg Modified to keep cursors open. |
301 | |
302 +===========================================================================*/
303
304 PROCEDURE build_where_clause (
305 p_context IN varchar2,
306 p_context_index IN BINARY_INTEGER,
307 p_where_clause IN OUT NOCOPY varchar2)
308
309 IS
310 l_seg_num number;
311 l_first boolean := TRUE;
312 l_context_index BINARY_INTEGER;
313 l_context ra_customer_trx_lines.interface_line_context%type;
314
315 BEGIN
316
317 arp_util.debug('arp_trans_flex.build_where_clause()+');
318
319 l_context := p_context;
320 l_context_index := p_context_index;
321
322 p_where_clause := '';
323
324
325 /*-----------------------------------------------------------------+
326 | Process the active segments for a context. The Global context |
327 | should be processed along with all contexts as global segments |
328 | are visible under all contexts |
329 +-----------------------------------------------------------------*/
330 LOOP
331
332 IF (l_context_index > 0)
333 THEN
334
335 /*------------------------------------------------------------+
336 | For each of the active segments construct the WHERE clause |
337 | based on the corresponding attribute value. |
338 +------------------------------------------------------------*/
339
340 FOR i IN 1..pg_num_segs(l_context_index) LOOP
341
342 l_seg_num := pg_active_segs(pg_start_loc(l_context_index)+i-1);
343
344 IF (NOT l_first) THEN
345 p_where_clause := p_where_clause||' AND ';
346 ELSE
347 l_first := FALSE;
348 END IF;
349
350 /*--------------------------------------------------------------+
351 | If the attribute value is NULL, then construct the clause as |
352 | INTERFACE_LINE_ATTRIBUTE<seg_num> IS NULL |
353 | Otherwise |
354 | INTERFACE_LINE_ATTRIBUTE<seg_num> = <attribute value> |
355 +--------------------------------------------------------------*/
356
357 p_where_clause := p_where_clause||
358 'INTERFACE_LINE_ATTRIBUTE'||
359 to_char(l_seg_num);
360
361 p_where_clause := p_where_clause||
362 ' = :INTERFACE_LINE_ATTRIBUTE'||
363 to_char(l_seg_num);
364
365 END LOOP;
366 ELSE
367 arp_util.debug('AR', 'AR_INV_TRANS_FLEX_CONTEXT');
368 app_exception.raise_exception;
369 END IF;
370
371 IF (l_context = 'Global Data Elements')
372 THEN
373 EXIT;
374 ELSE
375 l_context := 'Global Data Elements';
376 l_context_index := Find_Context( l_context);
377 END IF;
378
379 END LOOP;
380
381 -- arp_util.debug('Where clause : '||p_where_clause);
382
383 arp_util.debug('arp_trans_flex.build_where_clause()-');
384
385 EXCEPTION
386 WHEN OTHERS THEN
387 arp_util.debug('EXCEPTION : arp_trans_flex.build_where_clause');
388 arp_util.debug('p_context : '||p_context);
389
390 RAISE;
391 END;
392
393 /*===========================================================================+
394 | FUNCTION |
395 | check_uniqueness |
396 | |
397 | DESCRIPTION |
398 | Check for the uniqueness of the transaction flex in the specified |
399 | table |
400 | |
401 | SCOPE - PRIVATE |
402 | |
403 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
404 | dbms_sql.open_cursor |
405 | dbms_sql.close_cursor |
406 | dbms_sql.parse |
407 | dbms_sql.execute_and_fetch |
408 | arp_util.debug |
409 | |
410 | ARGUMENTS : IN: |
411 | p_cursor |
415 | None |
412 | OUT: |
413 | None |
414 | IN/ OUT: |
416 | |
417 | RETURNS : BOOLEAN |
418 | TRUE : If no matching rows are found i.e flex is unique |
419 | FALSE: If atleast one matching row is found |
420 | |
421 | NOTES |
422 | |
423 | MODIFICATION HISTORY |
424 | 24-OCT-95 Subash Chadalavada Created |
425 | 11-FEB-97 Charlie Tomberg Modified to keep cursors open. |
426 | |
427 +===========================================================================*/
428
429 FUNCTION check_uniqueness(
430 p_cursor IN OUT NOCOPY BINARY_INTEGER,
431 p_customer_trx_id OUT NOCOPY
432 ra_customer_trx.customer_trx_id%type,
433 p_customer_trx_line_id OUT NOCOPY
434 ra_customer_trx_lines.customer_trx_line_id%type
435 ) RETURN boolean
436 IS
437 l_dummy BINARY_INTEGER;
438 l_customer_trx_id ra_customer_trx.customer_trx_id%type;
439 l_customer_trx_line_id ra_customer_trx_lines.customer_trx_line_id%type;
440 l_rows NUMBER;
441
442 BEGIN
443
444 arp_util.debug('arp_trans_flex.check_uniqueness()+');
445
446 dbms_sql.define_column( p_cursor, 1, l_customer_trx_id);
447 dbms_sql.define_column( p_cursor, 2, l_customer_trx_line_id);
448
449 l_dummy := dbms_sql.execute(p_cursor);
450 l_rows := dbms_sql.fetch_rows(p_cursor);
451
452
453 /*-------------------------------------------------------------+
454 | If any matching rows are found then the flex is non-unique, |
455 | return FALSE. Otherwise return TRUE |
456 +-------------------------------------------------------------*/
457
458 IF (l_rows > 0)
459 THEN
460 dbms_sql.column_value( p_cursor, 1, l_customer_trx_id);
461 dbms_sql.column_value( p_cursor, 2, l_customer_trx_line_id);
462 p_customer_trx_id := l_customer_trx_id;
463 p_customer_trx_line_id := l_customer_trx_line_id;
464
465 arp_util.debug('arp_trans_flex.check_uniqueness()-');
466 return(FALSE);
467 END IF;
468
469 arp_util.debug('arp_trans_flex.check_uniqueness()-');
470 return(TRUE);
471
472 EXCEPTION
473 WHEN OTHERS THEN
474 arp_util.debug('EXCEPTION : arp_trans_flex.check_uniqueness()');
475 arp_util.debug('p_cursor : '|| p_cursor);
476
477 IF (p_cursor IS NOT NULL)
478 THEN dbms_sql.close_cursor(p_cursor);
479 END IF;
480
481 RAISE;
482 END;
483
484
485
486 /*===========================================================================+
487 | FUNCTION |
488 | Get_Cursor |
489 | |
490 | DESCRIPTION |
491 | Builds and parses the SQL statement that checks for uniquness for a |
492 | given table and context and returns the cursor number. |
493 | |
494 | SCOPE - PRIVATE |
495 | |
496 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
497 | arp_util.debug |
498 | |
499 | ARGUMENTS : IN: |
500 | p_context_index |
501 | p_table_name |
502 | OUT: |
503 | None |
504 | IN/ OUT: |
505 | None |
506 | |
507 | RETURNS : NONE |
508 | |
509 | NOTES |
510 | |
511 | MODIFICATION HISTORY |
515 | the unposted records in interface |
512 | 11-FEB-97 Charlie Tomberg Created |
513 | 10-APR-01 YREDDY Bug 1677311: Added condition so that |
514 | uniqueness will be tested against only|
516 | table. |
517 | |
518 +===========================================================================*/
519
520 FUNCTION Get_Cursor(
521 p_context_index IN BINARY_INTEGER,
522 p_table_name IN VARCHAR2
523 ) RETURN BINARY_INTEGER IS
524
525 l_context ra_customer_trx_lines.interface_line_context%type;
526 l_stmt VARCHAR2(2000);
527 l_where_clause VARCHAR2(2000);
528 l_cursor BINARY_INTEGER;
529
530 BEGIN
531
532 arp_util.debug('arp_trans_flex.Get_Cursor()+');
533
534 l_context := pg_flex_contexts( p_context_index );
535
536 /*-----------------------------+
537 | construct the WHERE clause |
538 +-----------------------------*/
539 build_where_clause( l_context,
540 p_context_index,
541 l_where_clause);
542
543
544 IF (p_table_name = 'RA_INTERFACE_LINES')
545 THEN l_stmt := 'SELECT 0, 0 FROM '|| p_table_name;
546 ELSE l_stmt := 'SELECT CUSTOMER_TRX_ID, CUSTOMER_TRX_LINE_ID FROM '||
547 p_table_name;
548 END IF;
549
550 IF (l_where_clause IS NOT NULL)
551 THEN
552 /*----------------------------------------------------------------+
553 | Construct additional WHERE clause based on the table that is |
554 | being checked. |
555 | For RA_INTERFACE_LINES, the context is always filled in even |
556 | if it is a Global context and for RA_CUSTOMER_TRX_LINES the |
557 | context is NULL if it is a Global context. Also, do not |
558 | include the current row when checking the uniqueness of the |
559 | transaction flex in RA_CUSTOMER_TRX_LINES |
560 +----------------------------------------------------------------*/
561 /* Bug 1677311: Added condition interface status <> 'P' */
562
563 IF (p_table_name = 'RA_INTERFACE_LINES')
564 THEN
565 l_stmt := l_stmt||' WHERE interface_line_context = '''||
566 nvl(l_context, 'Global Data Elements')||'''';
567 l_stmt := l_stmt||' AND NVL(interface_status,''~'') <> ''P''';
568 ELSIF (p_table_name = 'RA_CUSTOMER_TRX_LINES')
569 THEN
570 IF (nvl(l_context, 'Global Data Elements') = 'Global Data Elements')
571 THEN
572 l_stmt := l_stmt||' WHERE interface_line_context IS NULL ';
573 ELSE
574 l_stmt := l_stmt||' WHERE interface_line_context = '''||
575 l_context||'''';
576 END IF;
577
578 l_stmt := l_stmt||' AND customer_trx_line_id+0 <> '||
579 'NVL(:customer_trx_line_id, -98)';
580
581 END IF;
582
583 l_stmt := l_stmt || ' AND '|| l_where_clause;
584
585 END IF;
586
587 arp_util.debug('SQL Stmt : '||l_stmt);
588
589 /*---------------------------------------------------+
590 | Open, Parse and Execute the constructed SQL stmt |
591 +---------------------------------------------------*/
592
593 l_cursor := dbms_sql.open_cursor;
594 dbms_sql.parse(l_cursor, l_stmt, dbms_sql.v7);
595
596 IF (p_table_name = 'RA_CUSTOMER_TRX_LINES')
597 THEN pg_ctl_cursors( p_context_index ) := l_cursor;
598 ELSE pg_ril_cursors( p_context_index ) := l_cursor;
599 END IF;
600
601 arp_util.debug('arp_trans_flex.Get_Cursor()-');
602
603 RETURN(l_cursor);
604
605 EXCEPTION
606 WHEN OTHERS THEN
607 arp_util.debug('EXCEPTION : arp_trans_flex.Get_Cursor()');
608 arp_util.debug('p_context_index = ' || TO_CHAR( p_context_index));
609 arp_util.debug('p_table_name = ' || p_table_name);
610
611 RAISE;
612
613 END;
614
615
616 /*===========================================================================+
617 | PROCEDURE |
618 | Bind_Variable |
619 | |
620 | DESCRIPTION |
621 | Binds a single variable to the specified cursor. |
622 | |
623 | SCOPE - PRIVATE |
624 | |
625 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
626 | arp_util.debug |
627 | |
628 | ARGUMENTS : IN: |
629 | p_bind_variable |
630 | p_value |
634 | None |
631 | OUT: |
632 | None |
633 | IN/ OUT: |
635 | |
636 | RETURNS : NONE |
637 | |
638 | NOTES |
639 | This routine traps the 1006 'Bind Variable Does Not Exist' error and |
640 | ignores it since not all columns will be used with a given context. |
641 | |
642 | MODIFICATION HISTORY |
643 | 11-FEB-97 Charlie Tomberg Created |
644 | |
645 +===========================================================================*/
646
647 PROCEDURE Bind_Variable( p_cursor IN INTEGER,
648 p_bind_variable IN VARCHAR2,
649 p_value IN VARCHAR2
650 ) IS
651 BEGIN
652
653 arp_util.debug('arp_trans_flex.Bind_Variables()+');
654
655 dbms_sql.bind_variable( p_cursor,
656 p_bind_variable,
657 p_value );
658
659 arp_util.debug('arp_trans_flex.Bind_Variables()-');
660
661 EXCEPTION
662 WHEN OTHERS THEN
663 IF (SQLCODE = -1006)
664 THEN NULL;
665 ELSE
666 arp_util.debug('EXCEPTION : arp_trans_flex.Bind_Variable()');
667 arp_util.debug('p_cursor = ' || p_cursor);
668 arp_util.debug('p_bind_variable = ' || p_bind_variable);
669 arp_util.debug('p_value = ' || p_value);
670
671 RAISE;
672 END IF;
673
674 END;
675
676
677 /*===========================================================================+
678 | PROCEDURE |
679 | Bind_All_Variables |
680 | |
681 | DESCRIPTION |
682 | Binds a all of the columns that might be referenced in the SQL |
683 | statements to their appropriate cursors. |
684 | |
685 | SCOPE - PRIVATE |
686 | |
687 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
688 | arp_util.debug |
689 | |
690 | ARGUMENTS : IN: |
691 | p_customer_trx_line_id |
692 | p_interface_line_context |
693 | p_interface_line_attribute1 |
694 | p_interface_line_attribute2 |
695 | p_interface_line_attribute3 |
696 | p_interface_line_attribute4 |
697 | p_interface_line_attribute5 |
698 | p_interface_line_attribute6 |
699 | p_interface_line_attribute7 |
700 | p_interface_line_attribute8 |
701 | p_interface_line_attribute9 |
702 | p_interface_line_attribute10 |
703 | p_interface_line_attribute11 |
704 | p_interface_line_attribute12 |
705 | p_interface_line_attribute13 |
706 | p_interface_line_attribute14 |
707 | p_interface_line_attribute15 |
708 | OUT: |
709 | None |
710 | IN/ OUT: |
711 | p_ctl_cursor |
712 | p_ril_cursor |
713 | |
714 | RETURNS : NONE |
715 | |
716 | NOTES |
717 | |
718 | MODIFICATION HISTORY |
719 | 11-FEB-97 Charlie Tomberg Created |
720 | |
721 +===========================================================================*/
722
723 PROCEDURE Bind_All_Variables(
724 p_ctl_cursor IN OUT NOCOPY BINARY_INTEGER,
725 p_ril_cursor IN OUT NOCOPY BINARY_INTEGER,
729 ra_customer_trx_lines.interface_line_context%type,
726 p_context_index IN BINARY_INTEGER,
727 p_customer_trx_line_id IN ra_customer_trx_lines.customer_trx_line_id%type,
728 p_interface_line_context IN
730 p_interface_line_attribute1 IN
731 ra_customer_trx_lines.interface_line_attribute1%type,
732 p_interface_line_attribute2 IN
733 ra_customer_trx_lines.interface_line_attribute2%type,
734 p_interface_line_attribute3 IN
735 ra_customer_trx_lines.interface_line_attribute3%type,
736 p_interface_line_attribute4 IN
737 ra_customer_trx_lines.interface_line_attribute4%type,
738 p_interface_line_attribute5 IN
739 ra_customer_trx_lines.interface_line_attribute5%type,
740 p_interface_line_attribute6 IN
741 ra_customer_trx_lines.interface_line_attribute6%type,
742 p_interface_line_attribute7 IN
743 ra_customer_trx_lines.interface_line_attribute7%type,
744 p_interface_line_attribute8 IN
745 ra_customer_trx_lines.interface_line_attribute8%type,
746 p_interface_line_attribute9 IN
747 ra_customer_trx_lines.interface_line_attribute9%type,
748 p_interface_line_attribute10 IN
749 ra_customer_trx_lines.interface_line_attribute10%type,
750 p_interface_line_attribute11 IN
751 ra_customer_trx_lines.interface_line_attribute11%type,
752 p_interface_line_attribute12 IN
753 ra_customer_trx_lines.interface_line_attribute12%type,
754 p_interface_line_attribute13 IN
755 ra_customer_trx_lines.interface_line_attribute13%type,
756 p_interface_line_attribute14 IN
757 ra_customer_trx_lines.interface_line_attribute14%type,
758 p_interface_line_attribute15 IN
759 ra_customer_trx_lines.interface_line_attribute15%type) IS
760 BEGIN
761
762 arp_util.debug('arp_trans_flex.Bind_All_Variables()+');
763
764
765 /*-------------------------------------------------------+
766 | Bind variables into the ra_customer_trx_line cursor |
767 +-------------------------------------------------------*/
768
769 BEGIN
770 Bind_Variable(
771 p_ctl_cursor,
772 ':CUSTOMER_TRX_LINE_ID',
773 p_customer_trx_line_id
774 );
775 EXCEPTION
776
777 /*-----------------------------------------------------------+
778 | If the cursor is invalid, the first bind will fail. |
779 | in that case, recreate and reparse the SQL statement |
780 | and continue processing. The new cursor is passed back |
781 | to the calling routine since it is an IN/OUT parameter. |
782 +-----------------------------------------------------------*/
783
784 WHEN INVALID_CURSOR THEN
785 arp_util.debug('Handling INVALID_CURSOR exception by reparsing');
786
787 p_ctl_cursor := Get_Cursor(
788 p_context_index,
789 'RA_CUSTOMER_TRX_LINES'
790 );
791
792 Bind_Variable(
793 p_ctl_cursor,
794 ':CUSTOMER_TRX_LINE_ID',
795 p_customer_trx_line_id
796 );
797
798 WHEN OTHERS THEN RAISE;
799 END;
800
801
802 Bind_Variable(
803 p_ctl_cursor,
804 ':INTERFACE_LINE_ATTRIBUTE1',
805 p_interface_line_attribute1
806 );
807
808 Bind_Variable(
809 p_ctl_cursor,
810 ':INTERFACE_LINE_ATTRIBUTE2',
811 p_interface_line_attribute2
812 );
813
814 Bind_Variable(
815 p_ctl_cursor,
816 ':INTERFACE_LINE_ATTRIBUTE3',
817 p_interface_line_attribute3
818 );
819
820 Bind_Variable(
821 p_ctl_cursor,
822 ':INTERFACE_LINE_ATTRIBUTE4',
823 p_interface_line_attribute4
824 );
825
826 Bind_Variable(
827 p_ctl_cursor,
828 ':INTERFACE_LINE_ATTRIBUTE5',
829 p_interface_line_attribute5
830 );
831
832 Bind_Variable(
833 p_ctl_cursor,
834 ':INTERFACE_LINE_ATTRIBUTE6',
835 p_interface_line_attribute6
836 );
837
838 Bind_Variable(
839 p_ctl_cursor,
840 ':INTERFACE_LINE_ATTRIBUTE7',
841 p_interface_line_attribute7
842 );
843
844 Bind_Variable(
845 p_ctl_cursor,
846 ':INTERFACE_LINE_ATTRIBUTE8',
847 p_interface_line_attribute8
848 );
849
850 Bind_Variable(
851 p_ctl_cursor,
852 ':INTERFACE_LINE_ATTRIBUTE9',
853 p_interface_line_attribute9
854 );
855
859 p_interface_line_attribute10
856 Bind_Variable(
857 p_ctl_cursor,
858 ':INTERFACE_LINE_ATTRIBUTE10',
860 );
861
862 Bind_Variable(
863 p_ctl_cursor,
864 ':INTERFACE_LINE_ATTRIBUTE11',
865 p_interface_line_attribute11
866 );
867
868 Bind_Variable(
869 p_ctl_cursor,
870 ':INTERFACE_LINE_ATTRIBUTE12',
871 p_interface_line_attribute12
872 );
873
874 Bind_Variable(
875 p_ctl_cursor,
876 ':INTERFACE_LINE_ATTRIBUTE13',
877 p_interface_line_attribute13
878 );
879
880 Bind_Variable(
881 p_ctl_cursor,
882 ':INTERFACE_LINE_ATTRIBUTE14',
883 p_interface_line_attribute14
884 );
885
886 Bind_Variable(
887 p_ctl_cursor,
888 ':INTERFACE_LINE_ATTRIBUTE15',
889 p_interface_line_attribute15
890 );
891
892 /*-----------------------------------------------------+
893 | Bind variables into the ra_interface_lines cursor |
894 +-----------------------------------------------------*/
895
896 BEGIN
897 Bind_Variable(
898 p_ril_cursor,
899 ':CUSTOMER_TRX_LINE_ID',
900 p_customer_trx_line_id
901 );
902
903 EXCEPTION
904
905 /*-----------------------------------------------------------+
906 | If the cursor is invalid, the first bind will fail. |
907 | in that case, recreate and reparse the SQL statement |
908 | and continue processing. The new cursor is passed back |
909 | to the calling routine since it is an IN/OUT parameter. |
910 +-----------------------------------------------------------*/
911
912 WHEN INVALID_CURSOR THEN
913 arp_util.debug('Handling INVALID_CURSOR exception by reparsing');
914
915 p_ril_cursor := Get_Cursor(
916 p_context_index,
917 'RA_INTERFACE_LINES'
918 );
919
920 Bind_Variable(
921 p_ril_cursor,
922 ':CUSTOMER_TRX_LINE_ID',
923 p_customer_trx_line_id
924 );
925
926 WHEN OTHERS THEN RAISE;
927 END;
928
929
930 Bind_Variable(
931 p_ril_cursor,
932 ':INTERFACE_LINE_ATTRIBUTE1',
933 p_interface_line_attribute1
934 );
935
936 Bind_Variable(
937 p_ril_cursor,
938 ':INTERFACE_LINE_ATTRIBUTE2',
939 p_interface_line_attribute2
940 );
941
942 Bind_Variable(
943 p_ril_cursor,
944 ':INTERFACE_LINE_ATTRIBUTE3',
945 p_interface_line_attribute3
946 );
947
948 Bind_Variable(
949 p_ril_cursor,
950 ':INTERFACE_LINE_ATTRIBUTE4',
951 p_interface_line_attribute4
952 );
953
954 Bind_Variable(
955 p_ril_cursor,
956 ':INTERFACE_LINE_ATTRIBUTE5',
957 p_interface_line_attribute5
958 );
959
960 Bind_Variable(
961 p_ril_cursor,
962 ':INTERFACE_LINE_ATTRIBUTE6',
963 p_interface_line_attribute6
964 );
965
966 Bind_Variable(
967 p_ril_cursor,
968 ':INTERFACE_LINE_ATTRIBUTE7',
969 p_interface_line_attribute7
970 );
971
972 Bind_Variable(
973 p_ril_cursor,
974 ':INTERFACE_LINE_ATTRIBUTE8',
975 p_interface_line_attribute8
976 );
977
978 Bind_Variable(
979 p_ril_cursor,
980 ':INTERFACE_LINE_ATTRIBUTE9',
981 p_interface_line_attribute9
982 );
983
984 Bind_Variable(
985 p_ril_cursor,
986 ':INTERFACE_LINE_ATTRIBUTE10',
987 p_interface_line_attribute10
988 );
989
990 Bind_Variable(
991 p_ril_cursor,
992 ':INTERFACE_LINE_ATTRIBUTE11',
993 p_interface_line_attribute11
994 );
995
996 Bind_Variable(
997 p_ril_cursor,
998 ':INTERFACE_LINE_ATTRIBUTE12',
999 p_interface_line_attribute12
1000 );
1001
1002 Bind_Variable(
1003 p_ril_cursor,
1004 ':INTERFACE_LINE_ATTRIBUTE13',
1008 Bind_Variable(
1005 p_interface_line_attribute13
1006 );
1007
1009 p_ril_cursor,
1010 ':INTERFACE_LINE_ATTRIBUTE14',
1011 p_interface_line_attribute14
1012 );
1013
1014 Bind_Variable(
1015 p_ril_cursor,
1016 ':INTERFACE_LINE_ATTRIBUTE15',
1017 p_interface_line_attribute15
1018 );
1019
1020 arp_util.debug('arp_trans_flex.Bind_All_Variables()-');
1021
1022 EXCEPTION
1023 WHEN OTHERS THEN
1024 arp_util.debug('EXCEPTION : arp_trans_flex.Bind_All_Variables');
1025
1026 arp_util.debug('p_ctl_cursor : ' || p_ctl_cursor);
1027 arp_util.debug('p_ril_cursor : ' || p_ctl_cursor);
1028 arp_util.debug('p_customer_trx_line_id : '||p_customer_trx_line_id);
1029 arp_util.debug('p_interface_line_context : '||
1030 p_interface_line_context);
1031 arp_util.debug('p_interface_line_attribute2 : '||
1032 p_interface_line_attribute2);
1033 arp_util.debug('p_interface_line_attribute3 : '||
1034 p_interface_line_attribute3);
1035 arp_util.debug('p_interface_line_attribute4 : '||
1036 p_interface_line_attribute4);
1037 arp_util.debug('p_interface_line_attribute5 : '||
1038 p_interface_line_attribute5);
1039 arp_util.debug('p_interface_line_attribute6 : '||
1040 p_interface_line_attribute6);
1041 arp_util.debug('p_interface_line_attribute7 : '||
1042 p_interface_line_attribute7);
1043 arp_util.debug('p_interface_line_attribute8 : '||
1044 p_interface_line_attribute8);
1045 arp_util.debug('p_interface_line_attribute9 : '||
1046 p_interface_line_attribute9);
1047 arp_util.debug('p_interface_line_attribute10 : '||
1048 p_interface_line_attribute10);
1049 arp_util.debug('p_interface_line_attribute11 : '||
1050 p_interface_line_attribute11);
1051 arp_util.debug('p_interface_line_attribute12 : '||
1052 p_interface_line_attribute12);
1053 arp_util.debug('p_interface_line_attribute13 : '||
1054 p_interface_line_attribute13);
1055 arp_util.debug('p_interface_line_attribute14 : '||
1056 p_interface_line_attribute14);
1057 arp_util.debug('p_interface_line_attribute15 : '||
1058 p_interface_line_attribute15);
1059
1060 RAISE;
1061
1062 END;
1063
1064
1065 /*===========================================================================+
1066 | FUNCTION |
1067 | unique_trans_flex |
1068 | |
1069 | DESCRIPTION |
1070 | Validates the transaction flexfield |
1071 | |
1072 | SCOPE - PRIVATE |
1073 | |
1074 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
1075 | arp_util.debug |
1076 | |
1077 | ARGUMENTS : IN: |
1078 | p_ctl_id |
1079 | p_interface_line_context |
1080 | p_interface_line_attribute1 |
1081 | p_interface_line_attribute2 |
1082 | p_interface_line_attribute3 |
1083 | p_interface_line_attribute4 |
1084 | p_interface_line_attribute5 |
1085 | p_interface_line_attribute6 |
1086 | p_interface_line_attribute7 |
1087 | p_interface_line_attribute8 |
1088 | p_interface_line_attribute9 |
1089 | p_interface_line_attribute10 |
1090 | p_interface_line_attribute11 |
1091 | p_interface_line_attribute12 |
1092 | p_interface_line_attribute13 |
1093 | p_interface_line_attribute14 |
1094 | p_interface_line_attribute15 |
1095 | OUT: |
1096 | None |
1097 | IN/ OUT: |
1101 | TRUE : If the transaction flex is UNIQUE |
1098 | None |
1099 | |
1100 | RETURNS : BOOLEAN |
1102 | FALSE: If the transaction flex is NOT UNIQUE |
1103 | |
1104 | NOTES |
1105 | |
1106 | MODIFICATION HISTORY |
1107 | 24-OCT-95 Subash Chadalavada Created |
1108 | 11-FEB-97 Charlie Tomberg Modified to keep cursors open. |
1109 | |
1110 +===========================================================================*/
1111
1112 FUNCTION unique_trans_flex(
1113 p_ctl_id IN
1114 ra_customer_trx_lines.customer_trx_line_id%type,
1115 p_interface_line_context IN
1116 ra_customer_trx_lines.interface_line_context%type,
1117 p_interface_line_attribute1 IN
1118 ra_customer_trx_lines.interface_line_attribute1%type,
1119 p_interface_line_attribute2 IN
1120 ra_customer_trx_lines.interface_line_attribute2%type,
1121 p_interface_line_attribute3 IN
1122 ra_customer_trx_lines.interface_line_attribute3%type,
1123 p_interface_line_attribute4 IN
1124 ra_customer_trx_lines.interface_line_attribute4%type,
1125 p_interface_line_attribute5 IN
1126 ra_customer_trx_lines.interface_line_attribute5%type,
1127 p_interface_line_attribute6 IN
1128 ra_customer_trx_lines.interface_line_attribute6%type,
1129 p_interface_line_attribute7 IN
1130 ra_customer_trx_lines.interface_line_attribute7%type,
1131 p_interface_line_attribute8 IN
1132 ra_customer_trx_lines.interface_line_attribute8%type,
1133 p_interface_line_attribute9 IN
1134 ra_customer_trx_lines.interface_line_attribute9%type,
1135 p_interface_line_attribute10 IN
1136 ra_customer_trx_lines.interface_line_attribute10%type,
1137 p_interface_line_attribute11 IN
1138 ra_customer_trx_lines.interface_line_attribute11%type,
1139 p_interface_line_attribute12 IN
1140 ra_customer_trx_lines.interface_line_attribute12%type,
1141 p_interface_line_attribute13 IN
1142 ra_customer_trx_lines.interface_line_attribute13%type,
1143 p_interface_line_attribute14 IN
1144 ra_customer_trx_lines.interface_line_attribute14%type,
1145 p_interface_line_attribute15 IN
1146 ra_customer_trx_lines.interface_line_attribute15%type,
1147 p_customer_trx_id OUT NOCOPY ra_customer_trx.customer_trx_id%type,
1148 p_customer_trx_line_id OUT NOCOPY
1149 ra_customer_trx_lines.customer_trx_line_id%type)
1150 RETURN boolean IS
1151
1152 l_seg_value seg_value_type;
1153 l_where_clause varchar2(1000);
1154 l_ctl_cursor BINARY_INTEGER;
1155 l_ril_cursor BINARY_INTEGER;
1156 l_context_index BINARY_INTEGER;
1157 l_dummy BINARY_INTEGER;
1158
1159 BEGIN
1160
1161 arp_util.debug('arp_trans_flex.unique_trans_flex()+');
1162
1163 /*------------------------------------------------+
1164 | Get the context index from the context cache |
1165 +------------------------------------------------*/
1166
1167 l_context_index := Find_Context(
1168 nvl(p_interface_line_context,
1169 'Global Data Elements')
1170 );
1171
1172 /*------------------------------------------------------------------+
1173 | Reuse the existing cursors if they exist. Otherwise, create and |
1174 | parse the SQL statements into new cursors. |
1175 +------------------------------------------------------------------*/
1176
1177 BEGIN
1178 l_ctl_cursor := pg_ctl_cursors( l_context_index );
1179 EXCEPTION
1180 WHEN NO_DATA_FOUND
1181 THEN l_ctl_cursor := Get_Cursor(
1182 l_context_index,
1183 'RA_CUSTOMER_TRX_LINES'
1184 );
1185 WHEN OTHERS THEN RAISE;
1186 END;
1187
1188
1189 BEGIN
1190 l_ril_cursor := pg_ril_cursors( l_context_index );
1191 EXCEPTION
1192 WHEN NO_DATA_FOUND
1193 THEN l_ril_cursor := Get_Cursor(
1194 l_context_index,
1195 'RA_INTERFACE_LINES' );
1196
1197 WHEN OTHERS THEN RAISE;
1198 END;
1199
1200 Bind_All_Variables(
1201 l_ctl_cursor,
1202 l_ril_cursor,
1203 l_context_index,
1204 p_ctl_id,
1205 p_interface_line_context,
1206 p_interface_line_attribute1,
1207 p_interface_line_attribute2,
1208 p_interface_line_attribute3,
1209 p_interface_line_attribute4,
1213 p_interface_line_attribute8,
1210 p_interface_line_attribute5,
1211 p_interface_line_attribute6,
1212 p_interface_line_attribute7,
1214 p_interface_line_attribute9,
1215 p_interface_line_attribute10,
1216 p_interface_line_attribute11,
1217 p_interface_line_attribute12,
1218 p_interface_line_attribute13,
1219 p_interface_line_attribute14,
1220 p_interface_line_attribute15
1221 );
1222
1223 /*----------------------------------------------------------------------+
1224 | check for the uniqueness of the flex in RA_CUSTOMER_TRX_LINES table |
1225 +----------------------------------------------------------------------*/
1226 IF (NOT check_uniqueness(
1227 l_ctl_cursor,
1228 p_customer_trx_id,
1229 p_customer_trx_line_id
1230 ) )
1231 THEN
1232 return(FALSE);
1233 END IF;
1234
1235 /*----------------------------------------------------------------------+
1236 | check for the uniqueness of the flex in RA_INTERFACE_LINES table |
1237 +----------------------------------------------------------------------*/
1238 IF (NOT check_uniqueness(
1239 l_ril_cursor,
1240 l_dummy,
1241 l_dummy
1242 ) )
1243 THEN
1244 return(FALSE);
1245 END IF;
1246
1247
1248 return(TRUE);
1249
1250 EXCEPTION
1251 WHEN OTHERS THEN
1252 arp_util.debug('EXCEPTION : arp_trans_flex.unique_trans_flex');
1253
1254 arp_util.debug('p_ctl_id : '|| p_ctl_id);
1255 arp_util.debug('p_interface_line_context : '||
1256 p_interface_line_context);
1257 arp_util.debug('p_interface_line_attribute2 : '||
1258 p_interface_line_attribute2);
1259 arp_util.debug('p_interface_line_attribute3 : '||
1260 p_interface_line_attribute3);
1261 arp_util.debug('p_interface_line_attribute4 : '||
1262 p_interface_line_attribute4);
1263 arp_util.debug('p_interface_line_attribute5 : '||
1264 p_interface_line_attribute5);
1265 arp_util.debug('p_interface_line_attribute6 : '||
1266 p_interface_line_attribute6);
1267 arp_util.debug('p_interface_line_attribute7 : '||
1268 p_interface_line_attribute7);
1269 arp_util.debug('p_interface_line_attribute8 : '||
1270 p_interface_line_attribute8);
1271 arp_util.debug('p_interface_line_attribute9 : '||
1272 p_interface_line_attribute9);
1273 arp_util.debug('p_interface_line_attribute10 : '||
1274 p_interface_line_attribute10);
1275 arp_util.debug('p_interface_line_attribute11 : '||
1276 p_interface_line_attribute11);
1277 arp_util.debug('p_interface_line_attribute12 : '||
1278 p_interface_line_attribute12);
1279 arp_util.debug('p_interface_line_attribute13 : '||
1280 p_interface_line_attribute13);
1281 arp_util.debug('p_interface_line_attribute14 : '||
1282 p_interface_line_attribute14);
1283 arp_util.debug('p_interface_line_attribute15 : '||
1284 p_interface_line_attribute15);
1285
1286 RAISE;
1287 END;
1288
1289 FUNCTION unique_trans_flex(
1290 p_ctl_id IN
1291 ra_customer_trx_lines.customer_trx_line_id%type,
1292 p_interface_line_context IN
1293 ra_customer_trx_lines.interface_line_context%type,
1294 p_interface_line_attribute1 IN
1295 ra_customer_trx_lines.interface_line_attribute1%type,
1296 p_interface_line_attribute2 IN
1297 ra_customer_trx_lines.interface_line_attribute2%type,
1298 p_interface_line_attribute3 IN
1299 ra_customer_trx_lines.interface_line_attribute3%type,
1300 p_interface_line_attribute4 IN
1301 ra_customer_trx_lines.interface_line_attribute4%type,
1302 p_interface_line_attribute5 IN
1303 ra_customer_trx_lines.interface_line_attribute5%type,
1304 p_interface_line_attribute6 IN
1305 ra_customer_trx_lines.interface_line_attribute6%type,
1306 p_interface_line_attribute7 IN
1307 ra_customer_trx_lines.interface_line_attribute7%type,
1308 p_interface_line_attribute8 IN
1309 ra_customer_trx_lines.interface_line_attribute8%type,
1310 p_interface_line_attribute9 IN
1311 ra_customer_trx_lines.interface_line_attribute9%type,
1312 p_interface_line_attribute10 IN
1313 ra_customer_trx_lines.interface_line_attribute10%type,
1314 p_interface_line_attribute11 IN
1315 ra_customer_trx_lines.interface_line_attribute11%type,
1316 p_interface_line_attribute12 IN
1317 ra_customer_trx_lines.interface_line_attribute12%type,
1318 p_interface_line_attribute13 IN
1319 ra_customer_trx_lines.interface_line_attribute13%type,
1320 p_interface_line_attribute14 IN
1321 ra_customer_trx_lines.interface_line_attribute14%type,
1322 p_interface_line_attribute15 IN
1323 ra_customer_trx_lines.interface_line_attribute15%type)
1324 RETURN BOOLEAN IS
1325
1326 l_dummy BINARY_INTEGER;
1327
1328 BEGIN
1329 RETURN(
1330 unique_trans_flex(
1331 p_ctl_id,
1332 p_interface_line_context,
1333 p_interface_line_attribute1,
1334 p_interface_line_attribute2,
1335 p_interface_line_attribute3,
1336 p_interface_line_attribute4,
1337 p_interface_line_attribute5,
1338 p_interface_line_attribute6,
1339 p_interface_line_attribute7,
1340 p_interface_line_attribute8,
1341 p_interface_line_attribute9,
1342 p_interface_line_attribute10,
1343 p_interface_line_attribute11,
1344 p_interface_line_attribute12,
1345 p_interface_line_attribute13,
1346 p_interface_line_attribute14,
1347 p_interface_line_attribute15,
1348 l_dummy,
1349 l_dummy
1350 )
1351 );
1352
1353 END;
1354
1355 BEGIN
1356
1357 /*-----------------------------------------------------------------------+
1358 | initialization section. Initialize the package PL/SQL variables with |
1359 | the transaction flex information |
1360 +-----------------------------------------------------------------------*/
1361 setup_descr_flex(222, 'RA_INTERFACE_LINES');
1362
1363 END;