[Home] [Help]
PACKAGE BODY: APPS.AR_INVOICE_VALIDATE_FLEX
Source
1 PACKAGE BODY AR_INVOICE_VALIDATE_FLEX AS
2 /* $Header: ARXVINFB.pls 115.1 2003/12/30 00:30:49 anukumar noship $ */
3
4 pg_debug VARCHAR2(1) := nvl(fnd_profile.value('AFLOG_ENABLED'),'N');
5
6 PROCEDURE setup_descr_flex(
7 p_appl_id IN number,
8 p_desc_flex IN varchar2)
9 IS
10
11 cursor sel_active_segs( pc_appl_id in number,
12 pc_desc_flex in varchar2) is
13 select ctx.descriptive_flex_context_code context_code,
14 substr(seg.application_column_name,
15 length('INTERFACE_LINE_ATTRIBUTE')+1) segment_num
16 from fnd_descr_flex_contexts ctx,
17 fnd_descr_flex_column_usages seg
18 where ctx.application_id = pc_appl_id
19 and ctx.descriptive_flexfield_name = pc_desc_flex
20 and ctx.application_id = seg.application_id(+)
21 and ctx.descriptive_flexfield_name =
22 seg.descriptive_flexfield_name(+)
23 and ctx.descriptive_flex_context_code =
24 seg.descriptive_flex_context_code(+)
25 and seg.enabled_flag (+) = 'Y'
26 order by ctx.descriptive_flex_context_code, seg.column_seq_num;
27
28 l_prior_context fnd_descr_flex_contexts.descriptive_flex_context_code%type;
29 l_ctx_count number;
30 l_seg_count number;
31 l_total_seg_count number;
32 l_seg_number number;
33
34 BEGIN
35
36 l_prior_context := pg_char_dummy;
37 l_ctx_count := 0;
38 l_total_seg_count := 1;
39
40 IF pg_debug = 'Y'
41 THEN
42 ar_invoice_utils.debug('setup_descr_flex()+');
43 END IF;
44
45 /*----------------------------------------------------------------------+
46 | get the active segment numbers for the transaction flex and store |
47 | the information in package globals |
48 +----------------------------------------------------------------------*/
49
50 FOR segs IN sel_active_segs(p_appl_id, p_desc_flex)
51 LOOP
52
53 IF (segs.context_code <> l_prior_context)
54 THEN
55
56 l_seg_count := 0;
57 l_ctx_count := l_ctx_count + 1;
58 l_prior_context := segs.context_code;
59 pg_num_segs(l_ctx_count) := 0;
60
61 pg_flex_contexts(l_ctx_count) := segs.context_code;
62 pg_start_loc(l_ctx_count) := l_total_seg_count;
63
64 END IF;
65
66 IF (segs.segment_num IS NOT NULL)
67 THEN
68 l_seg_count := l_seg_count + 1;
69
70 pg_active_segs(l_total_seg_count) := segs.segment_num;
71
72 l_total_seg_count := l_total_seg_count + 1;
73 pg_num_segs(l_ctx_count) := l_seg_count;
74
75 END IF;
76 END LOOP;
77
78 pg_ctx_count := l_ctx_count;
79 IF pg_debug = 'Y'
80 THEN
81 ar_invoice_utils.debug('setup_descr_flex()-');
82 END IF;
83
84
85 EXCEPTION
86 WHEN OTHERS THEN
87 IF pg_debug = 'Y'
88 THEN
89 ar_invoice_utils.debug('EXCEPTION : setup_descr_flex()');
90 ar_invoice_utils.debug('p_appl_id : '||p_appl_id);
91 ar_invoice_utils.debug('p_desc_flex : '||p_desc_flex);
92 END IF;
93 RAISE;
94 END setup_descr_flex;
95
96 FUNCTION find_context(p_context IN varchar2) RETURN number
97 IS
98 BEGIN
99
100 FOR i IN 1..pg_ctx_count LOOP
101
102 IF pg_flex_contexts(i) = p_context
103 THEN
104 return(i);
105 END IF;
106
107 END LOOP;
108
109 return(0);
110
111 EXCEPTION
112 WHEN OTHERS THEN
113 IF pg_debug = 'Y'
114 THEN
115 ar_invoice_utils.debug('EXCEPTION : find_context()');
116 ar_invoice_utils.debug('p_context : '||p_context);
117 END IF;
118 RAISE;
119 END;
120
121 PROCEDURE build_where_clause (
122 p_context IN varchar2,
123 p_context_index IN BINARY_INTEGER,
124 p_where_clause IN OUT NOCOPY varchar2)
125
126 IS
127 l_seg_num number;
128 l_first boolean := TRUE;
129 l_context_index BINARY_INTEGER;
130 l_context ra_customer_trx_lines.interface_line_context%type;
131
132 BEGIN
133 IF pg_debug = 'Y'
134 THEN
135 ar_invoice_utils.debug('build_where_clause()+');
136 END IF;
137 l_context := p_context;
138 l_context_index := p_context_index;
139
140 p_where_clause := '';
141
142
143 /*-----------------------------------------------------------------+
144 | Process the active segments for a context. The Global context |
145 | should be processed along with all contexts as global segments |
146 | are visible under all contexts |
147 +-----------------------------------------------------------------*/
148 LOOP
149
150 IF (l_context_index > 0)
151 THEN
152
153 /*------------------------------------------------------------+
154 | For each of the active segments construct the WHERE clause |
155 | based on the corresponding attribute value. |
156 +------------------------------------------------------------*/
157
158 FOR i IN 1..pg_num_segs(l_context_index) LOOP
159
160 l_seg_num := pg_active_segs(pg_start_loc(l_context_index)+i-1);
161
162 IF (NOT l_first) THEN
163 p_where_clause := p_where_clause||' AND ';
164 ELSE
165 l_first := FALSE;
166 END IF;
167
168 /*--------------------------------------------------------------+
169 | If the attribute value is NULL, then construct the clause as |
170 | INTERFACE_LINE_ATTRIBUTE<seg_num> IS NULL |
171 | Otherwise |
172 | INTERFACE_LINE_ATTRIBUTE<seg_num> = <attribute value> |
173 +--------------------------------------------------------------*/
174
175 p_where_clause := p_where_clause||
176 'INTERFACE_LINE_ATTRIBUTE'||
177 to_char(l_seg_num);
178
179 p_where_clause := p_where_clause||
180 ' = :INTERFACE_LINE_ATTRIBUTE'||
181 to_char(l_seg_num);
182
183 END LOOP;
184 ELSE
185 IF pg_debug = 'Y'
186 THEN
187 ar_invoice_utils.debug('AR', 'AR_INV_TRANS_FLEX_CONTEXT');
188 END IF;
189 app_exception.raise_exception;
190 END IF;
191
192 IF (l_context = 'Global Data Elements')
193 THEN
194 EXIT;
195 ELSE
196 l_context := 'Global Data Elements';
197 l_context_index := Find_Context( l_context);
198 END IF;
199
200 END LOOP;
201 IF pg_debug = 'Y'
202 THEN
203 ar_invoice_utils.debug('Where clause : '||p_where_clause);
204 ar_invoice_utils.debug('build_where_clause()-');
205 END IF;
206
207 EXCEPTION
208 WHEN OTHERS THEN
209 IF pg_debug = 'Y'
210 THEN
211 ar_invoice_utils.debug('EXCEPTION : build_where_clause');
212 ar_invoice_utils.debug('p_context : '||p_context);
213 END IF;
214 RAISE;
215 END Build_where_clause;
216
217
218 FUNCTION Get_Cursor(
219 p_context_index IN BINARY_INTEGER,
220 p_table_name IN VARCHAR2
221 ) RETURN BINARY_INTEGER IS
222
223 l_context ra_customer_trx_lines.interface_line_context%type;
224 l_stmt VARCHAR2(2000);
225 l_where_clause VARCHAR2(2000);
226 l_cursor BINARY_INTEGER;
227
228 BEGIN
229 IF pg_debug = 'Y'
230 THEN
231 ar_invoice_utils.debug('Get_Cursor()+');
232 END IF;
233
234 l_context := pg_flex_contexts( p_context_index );
235
236 /*-----------------------------+
237 | construct the WHERE clause |
238 +-----------------------------*/
239 build_where_clause( l_context,
240 p_context_index,
241 l_where_clause);
242
243
244 IF (p_table_name = 'AR_TRX_LINES_GT')
245 THEN l_stmt := 'SELECT 0, 0 FROM '|| p_table_name;
246 ELSE l_stmt := 'SELECT CUSTOMER_TRX_ID, CUSTOMER_TRX_LINE_ID FROM '||
247 p_table_name;
248 END IF;
249
250 IF (l_where_clause IS NOT NULL)
251 THEN
252 /*----------------------------------------------------------------+
253 | Construct additional WHERE clause based on the table that is |
254 | being checked. |
255 | For AR_TRX_LINES_GT, the context is always filled in even |
256 | if it is a Global context and for RA_CUSTOMER_TRX_LINES the |
257 | context is NULL if it is a Global context. Also, do not |
258 | include the current row when checking the uniqueness of the |
259 | transaction flex in RA_CUSTOMER_TRX_LINES |
260 +----------------------------------------------------------------*/
261
262 IF (p_table_name = 'AR_TRX_LINES_GT')
263 THEN
264 l_stmt := l_stmt||' WHERE interface_line_context = '''||
265 nvl(l_context, 'Global Data Elements')||'''';
266 /*l_stmt := l_stmt||' AND NVL(interface_status,''~'') <> ''P'''; */
267 ELSIF (p_table_name = 'RA_CUSTOMER_TRX_LINES')
268 THEN
269 IF (nvl(l_context, 'Global Data Elements') = 'Global Data Elements')
270 THEN
271 l_stmt := l_stmt||' WHERE interface_line_context IS NULL ';
272 ELSE
273 l_stmt := l_stmt||' WHERE interface_line_context = '''||
274 l_context||'''';
275 END IF;
276
277 l_stmt := l_stmt||' AND customer_trx_line_id+0 <> '||
278 'NVL(:customer_trx_line_id, -98)';
279
280 END IF;
281
282 l_stmt := l_stmt || ' AND '|| l_where_clause;
283
284 END IF;
285 IF pg_debug = 'Y'
286 THEN
287 ar_invoice_utils.debug('SQL Stmt : '||l_stmt);
288 END IF;
289 /*---------------------------------------------------+
290 | Open, Parse and Execute the constructed SQL stmt |
291 +---------------------------------------------------*/
292
293 l_cursor := dbms_sql.open_cursor;
294 dbms_sql.parse(l_cursor, l_stmt, dbms_sql.v7);
295
296 IF (p_table_name = 'RA_CUSTOMER_TRX_LINES')
297 THEN pg_ctl_cursors( p_context_index ) := l_cursor;
298 ELSE pg_ril_cursors( p_context_index ) := l_cursor;
299 END IF;
300 IF pg_debug = 'Y'
301 THEN
302 ar_invoice_utils.debug('Get_Cursor()-');
303 END IF;
304
305 RETURN(l_cursor);
306
307 EXCEPTION
308 WHEN OTHERS THEN
309 IF pg_debug = 'Y'
310 THEN
311 ar_invoice_utils.debug('EXCEPTION : Get_Cursor()');
312 ar_invoice_utils.debug('p_context_index = ' || TO_CHAR( p_context_index));
313 ar_invoice_utils.debug('p_table_name = ' || p_table_name);
314 END IF;
315 RAISE;
316
317 END get_cursor;
318
319
320 PROCEDURE Bind_Variable( p_cursor IN INTEGER,
321 p_bind_variable IN VARCHAR2,
322 p_value IN VARCHAR2
323 ) IS
324 BEGIN
325 IF pg_debug = 'Y'
326 THEN
327 ar_invoice_utils.debug('Bind_Variables()+');
328 END IF;
329 dbms_sql.bind_variable( p_cursor,
330 p_bind_variable,
331 p_value );
332 IF pg_debug = 'Y'
333 THEN
334 ar_invoice_utils.debug('Bind_Variables()-');
335 END IF;
336
337 EXCEPTION
338 WHEN OTHERS THEN
339 IF (SQLCODE = -1006)
340 THEN NULL;
341 ELSE
342 IF pg_debug = 'Y'
343 THEN
344 ar_invoice_utils.debug('EXCEPTION : Bind_Variable()');
345 ar_invoice_utils.debug('p_cursor = ' || p_cursor);
346 ar_invoice_utils.debug('p_bind_variable = ' || p_bind_variable);
347 ar_invoice_utils.debug('p_value = ' || p_value);
348 END IF;
349 RAISE;
350 END IF;
351
352 END Bind_Variable;
353
354
355
356 PROCEDURE Bind_All_Variables(
357 p_ctl_cursor IN OUT NOCOPY BINARY_INTEGER,
358 p_ril_cursor IN OUT NOCOPY BINARY_INTEGER,
359 p_context_index IN BINARY_INTEGER,
360 p_customer_trx_line_id IN ra_customer_trx_lines.customer_trx_line_id%type,
361 p_interface_line_context IN
362 ra_customer_trx_lines.interface_line_context%type,
363 p_interface_line_attribute1 IN
364 ra_customer_trx_lines.interface_line_attribute1%type,
365 p_interface_line_attribute2 IN
366 ra_customer_trx_lines.interface_line_attribute2%type,
367 p_interface_line_attribute3 IN
368 ra_customer_trx_lines.interface_line_attribute3%type,
369 p_interface_line_attribute4 IN
370 ra_customer_trx_lines.interface_line_attribute4%type,
371 p_interface_line_attribute5 IN
372 ra_customer_trx_lines.interface_line_attribute5%type,
373 p_interface_line_attribute6 IN
374 ra_customer_trx_lines.interface_line_attribute6%type,
375 p_interface_line_attribute7 IN
376 ra_customer_trx_lines.interface_line_attribute7%type,
377 p_interface_line_attribute8 IN
378 ra_customer_trx_lines.interface_line_attribute8%type,
379 p_interface_line_attribute9 IN
380 ra_customer_trx_lines.interface_line_attribute9%type,
381 p_interface_line_attribute10 IN
382 ra_customer_trx_lines.interface_line_attribute10%type,
383 p_interface_line_attribute11 IN
384 ra_customer_trx_lines.interface_line_attribute11%type,
385 p_interface_line_attribute12 IN
386 ra_customer_trx_lines.interface_line_attribute12%type,
387 p_interface_line_attribute13 IN
388 ra_customer_trx_lines.interface_line_attribute13%type,
389 p_interface_line_attribute14 IN
390 ra_customer_trx_lines.interface_line_attribute14%type,
391 p_interface_line_attribute15 IN
392 ra_customer_trx_lines.interface_line_attribute15%type) IS
393 BEGIN
394
395 IF pg_debug = 'Y'
396 THEN
397 ar_invoice_utils.debug('Bind_All_Variables()+');
398 END IF;
399
400
401 /*-------------------------------------------------------+
402 | Bind variables into the ra_customer_trx_line cursor |
403 +-------------------------------------------------------*/
404
405 BEGIN
409 p_customer_trx_line_id
406 Bind_Variable(
407 p_ctl_cursor,
408 ':CUSTOMER_TRX_LINE_ID',
410 );
411 EXCEPTION
412
413 /*-----------------------------------------------------------+
414 | If the cursor is invalid, the first bind will fail. |
415 | in that case, recreate and reparse the SQL statement |
416 | and continue processing. The new cursor is passed back |
417 | to the calling routine since it is an IN/OUT parameter. |
418 +-----------------------------------------------------------*/
419
420 WHEN INVALID_CURSOR THEN
421 IF pg_debug = 'Y'
422 THEN
423 ar_invoice_utils.debug('Handling INVALID_CURSOR exception by reparsing');
424 END IF;
425
426 p_ctl_cursor := Get_Cursor(
427 p_context_index,
428 'RA_CUSTOMER_TRX_LINES'
429 );
430
431 Bind_Variable(
432 p_ctl_cursor,
433 ':CUSTOMER_TRX_LINE_ID',
434 p_customer_trx_line_id
435 );
436
437 WHEN OTHERS THEN RAISE;
438 END;
439
440
441 Bind_Variable(
442 p_ctl_cursor,
443 ':INTERFACE_LINE_ATTRIBUTE1',
444 p_interface_line_attribute1
445 );
446
447 Bind_Variable(
448 p_ctl_cursor,
449 ':INTERFACE_LINE_ATTRIBUTE2',
450 p_interface_line_attribute2
451 );
452
453 Bind_Variable(
454 p_ctl_cursor,
455 ':INTERFACE_LINE_ATTRIBUTE3',
456 p_interface_line_attribute3
457 );
458
459 Bind_Variable(
460 p_ctl_cursor,
461 ':INTERFACE_LINE_ATTRIBUTE4',
462 p_interface_line_attribute4
463 );
464
465 Bind_Variable(
466 p_ctl_cursor,
467 ':INTERFACE_LINE_ATTRIBUTE5',
468 p_interface_line_attribute5
469 );
470
471 Bind_Variable(
472 p_ctl_cursor,
473 ':INTERFACE_LINE_ATTRIBUTE6',
474 p_interface_line_attribute6
475 );
476
477 Bind_Variable(
478 p_ctl_cursor,
479 ':INTERFACE_LINE_ATTRIBUTE7',
480 p_interface_line_attribute7
481 );
482
483 Bind_Variable(
484 p_ctl_cursor,
485 ':INTERFACE_LINE_ATTRIBUTE8',
486 p_interface_line_attribute8
487 );
488
489 Bind_Variable(
490 p_ctl_cursor,
491 ':INTERFACE_LINE_ATTRIBUTE9',
492 p_interface_line_attribute9
493 );
494
495 Bind_Variable(
496 p_ctl_cursor,
497 ':INTERFACE_LINE_ATTRIBUTE10',
498 p_interface_line_attribute10
499 );
500
501 Bind_Variable(
502 p_ctl_cursor,
503 ':INTERFACE_LINE_ATTRIBUTE11',
504 p_interface_line_attribute11
505 );
506
507 Bind_Variable(
508 p_ctl_cursor,
509 ':INTERFACE_LINE_ATTRIBUTE12',
510 p_interface_line_attribute12
511 );
512
513 Bind_Variable(
514 p_ctl_cursor,
515 ':INTERFACE_LINE_ATTRIBUTE13',
516 p_interface_line_attribute13
517 );
518
519 Bind_Variable(
520 p_ctl_cursor,
521 ':INTERFACE_LINE_ATTRIBUTE14',
522 p_interface_line_attribute14
523 );
524
525 Bind_Variable(
526 p_ctl_cursor,
527 ':INTERFACE_LINE_ATTRIBUTE15',
528 p_interface_line_attribute15
529 );
530
531 /*-----------------------------------------------------+
532 | Bind variables into the AR_TRX_LINES_GT cursor |
533 +-----------------------------------------------------*/
534
535 BEGIN
536 Bind_Variable(
537 p_ril_cursor,
538 ':CUSTOMER_TRX_LINE_ID',
539 p_customer_trx_line_id
540 );
541
542 EXCEPTION
543
544 /*-----------------------------------------------------------+
545 | If the cursor is invalid, the first bind will fail. |
546 | in that case, recreate and reparse the SQL statement |
547 | and continue processing. The new cursor is passed back |
548 | to the calling routine since it is an IN/OUT parameter. |
549 +-----------------------------------------------------------*/
550
551 WHEN INVALID_CURSOR THEN
552 IF pg_debug = 'Y'
553 THEN
554 ar_invoice_utils.debug('Handling INVALID_CURSOR exception by reparsing');
555 END IF;
556
557 p_ril_cursor := Get_Cursor(
561
558 p_context_index,
559 'AR_TRX_LINES_GT'
560 );
562 Bind_Variable(
563 p_ril_cursor,
564 ':CUSTOMER_TRX_LINE_ID',
565 p_customer_trx_line_id
566 );
567
568 WHEN OTHERS THEN RAISE;
569 END;
570
571
572 Bind_Variable(
573 p_ril_cursor,
574 ':INTERFACE_LINE_ATTRIBUTE1',
575 p_interface_line_attribute1
576 );
577
578 Bind_Variable(
579 p_ril_cursor,
580 ':INTERFACE_LINE_ATTRIBUTE2',
581 p_interface_line_attribute2
582 );
583
584 Bind_Variable(
585 p_ril_cursor,
586 ':INTERFACE_LINE_ATTRIBUTE3',
587 p_interface_line_attribute3
588 );
589
590 Bind_Variable(
591 p_ril_cursor,
592 ':INTERFACE_LINE_ATTRIBUTE4',
593 p_interface_line_attribute4
594 );
595
596 Bind_Variable(
597 p_ril_cursor,
598 ':INTERFACE_LINE_ATTRIBUTE5',
599 p_interface_line_attribute5
600 );
601
602 Bind_Variable(
603 p_ril_cursor,
604 ':INTERFACE_LINE_ATTRIBUTE6',
605 p_interface_line_attribute6
606 );
607
608 Bind_Variable(
609 p_ril_cursor,
610 ':INTERFACE_LINE_ATTRIBUTE7',
611 p_interface_line_attribute7
612 );
613
614 Bind_Variable(
615 p_ril_cursor,
616 ':INTERFACE_LINE_ATTRIBUTE8',
617 p_interface_line_attribute8
618 );
619
620 Bind_Variable(
621 p_ril_cursor,
622 ':INTERFACE_LINE_ATTRIBUTE9',
623 p_interface_line_attribute9
624 );
625
626 Bind_Variable(
627 p_ril_cursor,
628 ':INTERFACE_LINE_ATTRIBUTE10',
629 p_interface_line_attribute10
630 );
631
632 Bind_Variable(
633 p_ril_cursor,
634 ':INTERFACE_LINE_ATTRIBUTE11',
635 p_interface_line_attribute11
636 );
637
638 Bind_Variable(
639 p_ril_cursor,
640 ':INTERFACE_LINE_ATTRIBUTE12',
641 p_interface_line_attribute12
642 );
643
644 Bind_Variable(
645 p_ril_cursor,
646 ':INTERFACE_LINE_ATTRIBUTE13',
647 p_interface_line_attribute13
648 );
649
650 Bind_Variable(
651 p_ril_cursor,
652 ':INTERFACE_LINE_ATTRIBUTE14',
653 p_interface_line_attribute14
654 );
655
656 Bind_Variable(
657 p_ril_cursor,
658 ':INTERFACE_LINE_ATTRIBUTE15',
659 p_interface_line_attribute15
660 );
661
662 IF pg_debug = 'Y'
663 THEN
664 ar_invoice_utils.debug('Bind_All_Variables()-');
665 END IF;
666
667 EXCEPTION
668 WHEN OTHERS THEN
669 IF pg_debug = 'Y'
670 THEN
671 ar_invoice_utils.debug('EXCEPTION : Bind_All_Variables');
672 ar_invoice_utils.debug('p_ctl_cursor : ' || p_ctl_cursor);
673 ar_invoice_utils.debug('p_ril_cursor : ' || p_ctl_cursor);
674 ar_invoice_utils.debug('p_customer_trx_line_id : '||p_customer_trx_line_id);
675 ar_invoice_utils.debug('p_interface_line_context : '||
676 p_interface_line_context);
677 ar_invoice_utils.debug('p_interface_line_attribute2 : '||
678 p_interface_line_attribute2);
679 ar_invoice_utils.debug('p_interface_line_attribute3 : '||
680 p_interface_line_attribute3);
681 ar_invoice_utils.debug('p_interface_line_attribute4 : '||
682 p_interface_line_attribute4);
683 ar_invoice_utils.debug('p_interface_line_attribute5 : '||
684 p_interface_line_attribute5);
685 ar_invoice_utils.debug('p_interface_line_attribute6 : '||
686 p_interface_line_attribute6);
687 ar_invoice_utils.debug('p_interface_line_attribute7 : '||
688 p_interface_line_attribute7);
689 ar_invoice_utils.debug('p_interface_line_attribute8 : '||
690 p_interface_line_attribute8);
691 ar_invoice_utils.debug('p_interface_line_attribute9 : '||
692 p_interface_line_attribute9);
693 ar_invoice_utils.debug('p_interface_line_attribute10 : '||
694 p_interface_line_attribute10);
695 ar_invoice_utils.debug('p_interface_line_attribute11 : '||
696 p_interface_line_attribute11);
697 ar_invoice_utils.debug('p_interface_line_attribute12 : '||
701 ar_invoice_utils.debug('p_interface_line_attribute14 : '||
698 p_interface_line_attribute12);
699 ar_invoice_utils.debug('p_interface_line_attribute13 : '||
700 p_interface_line_attribute13);
702 p_interface_line_attribute14);
703 ar_invoice_utils.debug('p_interface_line_attribute15 : '||
704 p_interface_line_attribute15);
705 END IF;
706 RAISE;
707
708 END Bind_All_Variables;
709
710 FUNCTION check_uniqueness(
711 p_table_name IN VARCHAR2,
712 p_cursor IN OUT NOCOPY BINARY_INTEGER,
713 p_customer_trx_id OUT NOCOPY
714 ra_customer_trx.customer_trx_id%type,
715 p_customer_trx_line_id OUT NOCOPY
716 ra_customer_trx_lines.customer_trx_line_id%type
717 ) RETURN boolean
718 IS
719 l_dummy BINARY_INTEGER;
720 l_customer_trx_id ra_customer_trx.customer_trx_id%type;
721 l_customer_trx_line_id ra_customer_trx_lines.customer_trx_line_id%type;
722 l_rows NUMBER;
723
724 BEGIN
725
726 IF pg_debug = 'Y'
727 THEN
728 ar_invoice_utils.debug('check_uniqueness()+');
729 END IF;
730
731 dbms_sql.define_column( p_cursor, 1, l_customer_trx_id);
732 dbms_sql.define_column( p_cursor, 2, l_customer_trx_line_id);
733
734 l_dummy := dbms_sql.execute(p_cursor);
735 l_rows := dbms_sql.fetch_rows(p_cursor);
736
737
738 /*-------------------------------------------------------------+
739 | If more than one matching rows are found then the flex is |
740 | non-unique, |
741 | return FALSE. Otherwise return TRUE |
742 +-------------------------------------------------------------*/
743
744 IF (l_rows > 0 and p_table_name = 'RA_CUSTOMER_TRX_LINES' )
745 THEN
746 dbms_sql.column_value( p_cursor, 1, l_customer_trx_id);
747 dbms_sql.column_value( p_cursor, 2, l_customer_trx_line_id);
748 p_customer_trx_id := l_customer_trx_id;
749 p_customer_trx_line_id := l_customer_trx_line_id;
750
751 IF pg_debug = 'Y'
752 THEN
753 ar_invoice_utils.debug('check_uniqueness()-');
754 END IF;
755 return(FALSE);
756 ELSIF ( l_rows > 1 and p_table_name = 'AR_TRX_LINES_GT' )
757 THEN
758 dbms_sql.column_value( p_cursor, 1, l_customer_trx_id);
759 dbms_sql.column_value( p_cursor, 2, l_customer_trx_line_id);
760 p_customer_trx_id := l_customer_trx_id;
761 p_customer_trx_line_id := l_customer_trx_line_id;
762 IF pg_debug = 'Y'
763 THEN
764 ar_invoice_utils.debug('check_uniqueness for ar_trx_lines_gt()-');
765 END IF;
766 return(FALSE);
767 END IF;
768
769 IF pg_debug = 'Y'
770 THEN
771 ar_invoice_utils.debug('check_uniqueness()-');
772 END IF;
773 return(TRUE);
774
775 EXCEPTION
776 WHEN OTHERS THEN
777 IF pg_debug = 'Y'
778 THEN
779 ar_invoice_utils.debug('EXCEPTION : check_uniqueness()');
780 ar_invoice_utils.debug('p_cursor : '|| p_cursor);
781 END IF;
782 IF (p_cursor IS NOT NULL)
783 THEN dbms_sql.close_cursor(p_cursor);
784 END IF;
785
786 RAISE;
787 END check_uniqueness;
788
789
790 FUNCTION unique_flex(
791 p_ctl_id IN
792 ra_customer_trx_lines.customer_trx_line_id%type,
793 p_customer_trx_id IN NUMBER,
794 p_customer_trx_line_id IN NUMBER,
795 p_int_line_rec IN
796 interface_line_rec_type )
797 RETURN boolean IS
798
799 l_seg_value seg_value_type;
800 l_where_clause varchar2(1000);
801 l_ctl_cursor BINARY_INTEGER;
802 l_ril_cursor BINARY_INTEGER;
803 l_context_index BINARY_INTEGER;
804 l_dummy BINARY_INTEGER;
805 l_customer_trx_id NUMBER;
806 l_customer_trx_line_id NUMBER;
807 BEGIN
808 IF pg_debug = 'Y'
809 THEN
810 ar_invoice_utils.debug('unique_trans_flex()+');
811 END IF;
812 setup_descr_flex(222, 'RA_INTERFACE_LINES');
813
814 /*------------------------------------------------+
815 | Get the context index from the context cache |
816 +------------------------------------------------*/
817
818 l_context_index := Find_Context(
819 nvl(p_int_line_rec.interface_line_context,
820 'Global Data Elements')
821 );
822
823 /*------------------------------------------------------------------+
824 | Reuse the existing cursors if they exist. Otherwise, create and |
825 | parse the SQL statements into new cursors. |
826 +------------------------------------------------------------------*/
827
828 BEGIN
829 l_ctl_cursor := pg_ctl_cursors( l_context_index );
830
831 EXCEPTION
832 WHEN NO_DATA_FOUND
833 THEN
834
835 l_ctl_cursor := Get_Cursor(
836 l_context_index,
837 'RA_CUSTOMER_TRX_LINES'
838 );
839
840 WHEN OTHERS THEN RAISE;
841 END;
842
843
844 BEGIN
845
849 WHEN NO_DATA_FOUND
846 l_ril_cursor := pg_ril_cursors( l_context_index );
847
848 EXCEPTION
850
851 THEN
852
853 l_ril_cursor := Get_Cursor(
854 l_context_index,
855 'AR_TRX_LINES_GT' );
856
857 WHEN OTHERS THEN RAISE;
858 END;
859
860 Bind_All_Variables(
861 l_ctl_cursor,
862 l_ril_cursor,
863 l_context_index,
864 p_ctl_id,
865 p_int_line_rec.interface_line_context,
866 p_int_line_rec.interface_line_attribute1,
867 p_int_line_rec.interface_line_attribute2,
868 p_int_line_rec.interface_line_attribute3,
869 p_int_line_rec.interface_line_attribute4,
870 p_int_line_rec.interface_line_attribute5,
871 p_int_line_rec.interface_line_attribute6,
872 p_int_line_rec.interface_line_attribute7,
873 p_int_line_rec.interface_line_attribute8,
874 p_int_line_rec.interface_line_attribute9,
875 p_int_line_rec.interface_line_attribute10,
876 p_int_line_rec.interface_line_attribute11,
877 p_int_line_rec.interface_line_attribute12,
878 p_int_line_rec.interface_line_attribute13,
879 p_int_line_rec.interface_line_attribute14,
880 p_int_line_rec.interface_line_attribute15
881 );
882 /*----------------------------------------------------------------------+
883 | check for the uniqueness of the flex in RA_CUSTOMER_TRX_LINES table |
884 +----------------------------------------------------------------------*/
885 IF (NOT check_uniqueness(
886 'RA_CUSTOMER_TRX_LINES',
887 l_ctl_cursor,
888 l_customer_trx_id,
889 l_customer_trx_line_id
890 ) )
891 THEN
892 return(FALSE);
893 END IF;
894
895 /*----------------------------------------------------------------------+
896 | check for the uniqueness of the flex in AR_TRX_LINES_GT table |
897 +----------------------------------------------------------------------*/
898 IF (NOT check_uniqueness(
899 'AR_TRX_LINES_GT',
900 l_ril_cursor,
901 l_dummy,
902 l_dummy
903 ) )
904 THEN
905 return(FALSE);
906 END IF;
907
908 return(TRUE);
909
910 EXCEPTION
911 WHEN OTHERS THEN
912 IF pg_debug = 'Y'
913 THEN
914 ar_invoice_utils.debug('EXCEPTION : arp_invoice_validate_flex.unique_flex');
915 END IF;
916 RAISE;
917
918 END unique_flex;
919
920 PROCEDURE Validate_Line_Int_Flex(
921 p_desc_flex_rec IN OUT NOCOPY interface_line_rec_type,
922 p_desc_flex_name IN VARCHAR2,
923 p_return_status IN OUT NOCOPY varchar2
924 ) IS
925
926 l_flex_name fnd_descriptive_flexs.descriptive_flexfield_name%type;
927 l_count NUMBER;
928 l_col_name VARCHAR2(50);
929 l_flex_exists VARCHAR2(1);
930 CURSOR desc_flex_exists IS
931 SELECT 'Y'
932 FROM fnd_descriptive_flexs
933 WHERE application_id = 222
934 and descriptive_flexfield_name = p_desc_flex_name;
935 BEGIN
936 IF PG_DEBUG = 'Y' THEN
937 ar_invoice_utils.debug('' || 'AR_INVOICE_UTILS.Validate_LINE_Int_Flex ()+');
938 END IF;
939 p_return_status := FND_API.G_RET_STS_SUCCESS;
940
941 OPEN desc_flex_exists;
942 FETCH desc_flex_exists INTO l_flex_exists;
943 IF desc_flex_exists%NOTFOUND THEN
944 CLOSE desc_flex_exists;
945 p_return_status := FND_API.G_RET_STS_ERROR;
946 return;
947 END IF;
948 CLOSE desc_flex_exists;
949
950 IF p_desc_flex_name = 'RA_INTERCACE_LINES'
951 THEN
952 fnd_flex_descval.set_context_value(p_desc_flex_rec.interface_line_context);
953
954 fnd_flex_descval.set_column_value('INTERFACE_LINE_ATTRIBUTE1',
955 p_desc_flex_rec.interface_LINE_attribute1);
956 fnd_flex_descval.set_column_value('INTERFACE_LINE_ATTRIBUTE2',
957 p_desc_flex_rec.interface_LINE_attribute2);
958 fnd_flex_descval.set_column_value('INTERFACE_LINE_ATTRIBUTE3',
959 p_desc_flex_rec.interface_LINE_attribute3);
960 fnd_flex_descval.set_column_value('INTERFACE_LINE_ATTRIBUTE4',
961 p_desc_flex_rec.interface_LINE_attribute4);
962 fnd_flex_descval.set_column_value('INTERFACE_LINE_ATTRIBUTE5',
963 p_desc_flex_rec.interface_LINE_attribute5);
964 fnd_flex_descval.set_column_value('INTERFACE_LINE_ATTRIBUTE6',
965 p_desc_flex_rec.interface_LINE_attribute6);
966 fnd_flex_descval.set_column_value('INTERFACE_LINE_ATTRIBUTE7',
967 p_desc_flex_rec.interface_LINE_attribute7);
968 fnd_flex_descval.set_column_value('INTERFACE_LINE_ATTRIBUTE8',
969 p_desc_flex_rec.interface_LINE_attribute8);
970 fnd_flex_descval.set_column_value('INTERFACE_LINE_ATTRIBUTE9',
971 p_desc_flex_rec.interface_LINE_attribute9);
972 fnd_flex_descval.set_column_value('INTERFACE_LINE_ATTRIBUTE10',
976 fnd_flex_descval.set_column_value('INTERFACE_LINE_ATTRIBUTE12',
973 p_desc_flex_rec.interface_LINE_attribute10);
974 fnd_flex_descval.set_column_value('INTERFACE_LINE_ATTRIBUTE11',
975 p_desc_flex_rec.interface_LINE_attribute11);
977 p_desc_flex_rec.interface_LINE_attribute12);
978 fnd_flex_descval.set_column_value('INTERFACE_LINE_ATTRIBUTE13',
979 p_desc_flex_rec.interface_LINE_attribute13);
980 fnd_flex_descval.set_column_value('INTERFACE_LINE_ATTRIBUTE14',
981 p_desc_flex_rec.interface_LINE_attribute14);
982 fnd_flex_descval.set_column_value('INTERFACE_LINE_ATTRIBUTE15',
983 p_desc_flex_rec.interface_LINE_attribute15);
984
985
986 IF ( NOT fnd_flex_descval.validate_desccols('AR',p_desc_flex_name,'I') )
987 THEN
988 p_return_status := FND_API.G_RET_STS_ERROR;
989 END IF;
990
991 l_count := fnd_flex_descval.segment_count;
992
993
994 FOR i in 1..l_count LOOP
995 l_col_name := fnd_flex_descval.segment_column_name(i);
996
997 IF l_col_name = 'INTERFACE_LINE_ATTRIBUTE1' THEN
998 p_desc_flex_rec.INTERFACE_LINE_attribute1 := fnd_flex_descval.segment_id(i);
999 ELSIF l_col_name = 'INTERFACE_LINE_CONTEXT' THEN
1000 p_desc_flex_rec.interface_LINE_context := fnd_flex_descval.segment_id(i);
1001 ELSIF l_col_name = 'INTERFACE_LINE_ATTRIBUTE2' THEN
1002 p_desc_flex_rec.INTERFACE_LINE_attribute2 := fnd_flex_descval.segment_id(i);
1003 ELSIF l_col_name = 'INTERFACE_LINE_ATTRIBUTE3' THEN
1004 p_desc_flex_rec.INTERFACE_LINE_attribute3 := fnd_flex_descval.segment_id(i);
1005 ELSIF l_col_name = 'INTERFACE_LINE_ATTRIBUTE4' THEN
1006 p_desc_flex_rec.INTERFACE_LINE_attribute4 := fnd_flex_descval.segment_id(i);
1007 ELSIF l_col_name = 'INTERFACE_LINE_ATTRIBUTE5' THEN
1008 p_desc_flex_rec.INTERFACE_LINE_attribute5 := fnd_flex_descval.segment_id(i);
1009 ELSIF l_col_name = 'INTERFACE_LINE_ATTRIBUTE6' THEN
1010 p_desc_flex_rec.INTERFACE_LINE_attribute6 := fnd_flex_descval.segment_id(i);
1011 ELSIF l_col_name = 'INTERFACE_LINE_ATTRIBUTE7' THEN
1012 p_desc_flex_rec.INTERFACE_LINE_attribute7 := fnd_flex_descval.segment_id(i);
1013 ELSIF l_col_name = 'INTERFACE_LINE_ATTRIBUTE8' THEN
1014 p_desc_flex_rec.INTERFACE_LINE_attribute8 := fnd_flex_descval.segment_id(i);
1015 ELSIF l_col_name = 'INTERFACE_LINE_ATTRIBUTE9' THEN
1016 p_desc_flex_rec.INTERFACE_LINE_attribute9 := fnd_flex_descval.segment_id(i);
1017 ELSIF l_col_name = 'INTERFACE_LINE_ATTRIBUTE10' THEN
1018 p_desc_flex_rec.INTERFACE_LINE_attribute10 := fnd_flex_descval.segment_id(i);
1019 ELSIF l_col_name = 'INTERFACE_LINE_ATTRIBUTE11' THEN
1020 p_desc_flex_rec.INTERFACE_LINE_attribute11 := fnd_flex_descval.segment_id(i);
1021 ELSIF l_col_name = 'INTERFACE_LINE_ATTRIBUTE12' THEN
1022 p_desc_flex_rec.INTERFACE_LINE_attribute12 := fnd_flex_descval.segment_id(i);
1023 ELSIF l_col_name = 'INTERFACE_LINE_ATTRIBUTE13' THEN
1024 p_desc_flex_rec.INTERFACE_LINE_attribute13 := fnd_flex_descval.segment_id(i);
1025 ELSIF l_col_name = 'INTERFACE_LINE_ATTRIBUTE14' THEN
1026 p_desc_flex_rec.INTERFACE_LINE_attribute14 := fnd_flex_descval.segment_id(i);
1027 ELSIF l_col_name = 'INTERFACE_LINE_ATTRIBUTE15' THEN
1028 p_desc_flex_rec.INTERFACE_LINE_attribute15 := fnd_flex_descval.segment_id(i);
1029 END IF;
1030
1031 IF i > l_count THEN
1032 EXIT;
1033 END IF;
1034 END LOOP;
1035 END IF;
1036
1037 END Validate_Line_Int_Flex;
1038
1039
1040
1041 PROCEDURE Validate_Int_Desc_Flex(
1042 p_desc_flex_rec IN OUT NOCOPY interface_hdr_rec_type,
1043 p_desc_flex_name IN VARCHAR2,
1044 p_return_status IN OUT NOCOPY varchar2
1045 ) IS
1046
1047 l_flex_name fnd_descriptive_flexs.descriptive_flexfield_name%type;
1048 l_count NUMBER;
1049 l_col_name VARCHAR2(50);
1050 l_flex_exists VARCHAR2(1);
1051 CURSOR desc_flex_exists IS
1052 SELECT 'Y'
1053 FROM fnd_descriptive_flexs
1054 WHERE application_id = 222
1055 and descriptive_flexfield_name = p_desc_flex_name;
1056 BEGIN
1057 IF PG_DEBUG = 'Y' THEN
1058 ar_invoice_utils.debug('' || 'AR_INVOICE_UTILS.Validate_Int_Desc_Flex ()+');
1059 END IF;
1060 p_return_status := FND_API.G_RET_STS_SUCCESS;
1061
1062 OPEN desc_flex_exists;
1063 FETCH desc_flex_exists INTO l_flex_exists;
1064 IF desc_flex_exists%NOTFOUND THEN
1065 CLOSE desc_flex_exists;
1066 p_return_status := FND_API.G_RET_STS_ERROR;
1067 return;
1068 END IF;
1069 CLOSE desc_flex_exists;
1070
1071 IF p_desc_flex_name = 'RA_INTERFACE_HEADER'
1072 THEN
1073 fnd_flex_descval.set_context_value(p_desc_flex_rec.interface_header_context);
1074
1075 fnd_flex_descval.set_column_value('INTERFACE_HEADER_ATTRIBUTE1',
1076 p_desc_flex_rec.interface_header_attribute1);
1077 fnd_flex_descval.set_column_value('INTERFACE_HEADER_ATTRIBUTE2',
1078 p_desc_flex_rec.interface_header_attribute2);
1079 fnd_flex_descval.set_column_value('INTERFACE_HEADER_ATTRIBUTE3',
1080 p_desc_flex_rec.interface_header_attribute3);
1081 fnd_flex_descval.set_column_value('INTERFACE_HEADER_ATTRIBUTE4',
1082 p_desc_flex_rec.interface_header_attribute4);
1083 fnd_flex_descval.set_column_value('INTERFACE_HEADER_ATTRIBUTE5',
1087 fnd_flex_descval.set_column_value('INTERFACE_HEADER_ATTRIBUTE7',
1084 p_desc_flex_rec.interface_header_attribute5);
1085 fnd_flex_descval.set_column_value('INTERFACE_HEADER_ATTRIBUTE6',
1086 p_desc_flex_rec.interface_header_attribute6);
1088 p_desc_flex_rec.interface_header_attribute7);
1089 fnd_flex_descval.set_column_value('INTERFACE_HEADER_ATTRIBUTE8',
1090 p_desc_flex_rec.interface_header_attribute8);
1091 fnd_flex_descval.set_column_value('INTERFACE_HEADER_ATTRIBUTE9',
1092 p_desc_flex_rec.interface_header_attribute9);
1093 fnd_flex_descval.set_column_value('INTERFACE_HEADER_ATTRIBUTE10',
1094 p_desc_flex_rec.interface_header_attribute10);
1095 fnd_flex_descval.set_column_value('INTERFACE_HEADER_ATTRIBUTE11',
1096 p_desc_flex_rec.interface_header_attribute11);
1097 fnd_flex_descval.set_column_value('INTERFACE_HEADER_ATTRIBUTE12',
1098 p_desc_flex_rec.interface_header_attribute12);
1099 fnd_flex_descval.set_column_value('INTERFACE_HEADER_ATTRIBUTE13',
1100 p_desc_flex_rec.interface_header_attribute13);
1101 fnd_flex_descval.set_column_value('INTERFACE_HEADER_ATTRIBUTE14',
1102 p_desc_flex_rec.interface_header_attribute14);
1103 fnd_flex_descval.set_column_value('INTERFACE_HEADER_ATTRIBUTE15',
1104 p_desc_flex_rec.interface_header_attribute15);
1105
1106
1107 IF ( NOT fnd_flex_descval.validate_desccols('AR',p_desc_flex_name,'I') )
1108 THEN
1109 p_return_status := FND_API.G_RET_STS_ERROR;
1110 END IF;
1111
1112 l_count := fnd_flex_descval.segment_count;
1113
1114
1115 FOR i in 1..l_count LOOP
1116 l_col_name := fnd_flex_descval.segment_column_name(i);
1117
1118 IF l_col_name = 'INTERFACE_HEADER_ATTRIBUTE1' THEN
1119 p_desc_flex_rec.INTERFACE_HEADER_attribute1 := fnd_flex_descval.segment_id(i);
1120 ELSIF l_col_name = 'INTERFACE_HEADER_CONTEXT' THEN
1121 p_desc_flex_rec.interface_header_context := fnd_flex_descval.segment_id(i);
1122 ELSIF l_col_name = 'INTERFACE_HEADER_ATTRIBUTE2' THEN
1123 p_desc_flex_rec.INTERFACE_HEADER_attribute2 := fnd_flex_descval.segment_id(i);
1124 ELSIF l_col_name = 'INTERFACE_HEADER_ATTRIBUTE3' THEN
1125 p_desc_flex_rec.INTERFACE_HEADER_attribute3 := fnd_flex_descval.segment_id(i);
1126 ELSIF l_col_name = 'INTERFACE_HEADER_ATTRIBUTE4' THEN
1127 p_desc_flex_rec.INTERFACE_HEADER_attribute4 := fnd_flex_descval.segment_id(i);
1128 ELSIF l_col_name = 'INTERFACE_HEADER_ATTRIBUTE5' THEN
1129 p_desc_flex_rec.INTERFACE_HEADER_attribute5 := fnd_flex_descval.segment_id(i);
1130 ELSIF l_col_name = 'INTERFACE_HEADER_ATTRIBUTE6' THEN
1131 p_desc_flex_rec.INTERFACE_HEADER_attribute6 := fnd_flex_descval.segment_id(i);
1132 ELSIF l_col_name = 'INTERFACE_HEADER_ATTRIBUTE7' THEN
1133 p_desc_flex_rec.INTERFACE_HEADER_attribute7 := fnd_flex_descval.segment_id(i);
1134 ELSIF l_col_name = 'INTERFACE_HEADER_ATTRIBUTE8' THEN
1135 p_desc_flex_rec.INTERFACE_HEADER_attribute8 := fnd_flex_descval.segment_id(i);
1136 ELSIF l_col_name = 'INTERFACE_HEADER_ATTRIBUTE9' THEN
1137 p_desc_flex_rec.INTERFACE_HEADER_attribute9 := fnd_flex_descval.segment_id(i);
1138 ELSIF l_col_name = 'INTERFACE_HEADER_ATTRIBUTE10' THEN
1139 p_desc_flex_rec.INTERFACE_HEADER_attribute10 := fnd_flex_descval.segment_id(i);
1140 ELSIF l_col_name = 'INTERFACE_HEADER_ATTRIBUTE11' THEN
1141 p_desc_flex_rec.INTERFACE_HEADER_attribute11 := fnd_flex_descval.segment_id(i);
1142 ELSIF l_col_name = 'INTERFACE_HEADER_ATTRIBUTE12' THEN
1143 p_desc_flex_rec.INTERFACE_HEADER_attribute12 := fnd_flex_descval.segment_id(i);
1144 ELSIF l_col_name = 'INTERFACE_HEADER_ATTRIBUTE13' THEN
1145 p_desc_flex_rec.INTERFACE_HEADER_attribute13 := fnd_flex_descval.segment_id(i);
1146 ELSIF l_col_name = 'INTERFACE_HEADER_ATTRIBUTE14' THEN
1147 p_desc_flex_rec.INTERFACE_HEADER_attribute14 := fnd_flex_descval.segment_id(i);
1148 ELSIF l_col_name = 'INTERFACE_HEADER_ATTRIBUTE15' THEN
1149 p_desc_flex_rec.INTERFACE_HEADER_attribute15 := fnd_flex_descval.segment_id(i);
1150 END IF;
1151
1152 IF i > l_count THEN
1153 EXIT;
1154 END IF;
1155 END LOOP;
1156 END IF;
1157
1158 END Validate_Int_Desc_Flex;
1159
1160
1161 PROCEDURE validate_desc_flex (
1162 p_validation_type IN VARCHAR2,
1163 x_errmsg OUT NOCOPY VARCHAR2,
1164 x_return_status OUT NOCOPY VARCHAR2 ) AS
1165
1166 --p_trx_attr_rec
1167 p_trx_attr_rec arp_util.attribute_rec_type;
1168 p_trx_hdr_int_attr_rec interface_hdr_rec_type;
1169 p_trx_line_int_attr_rec interface_line_rec_type;
1170
1171 CURSOR cFlexAttr IS
1172 SELECT attribute1, attribute2, attribute3,
1173 attribute4, attribute5, attribute6,
1174 attribute7, attribute8, attribute9,
1175 attribute10, attribute11, attribute12,
1176 attribute13, attribute14, attribute15,
1177 attribute_category,
1178 trx_header_id
1179 FROM ar_trx_header_gt
1180 WHERE attribute1 IS NOT NULL
1181 OR attribute2 IS NOT NULL
1182 OR attribute3 IS NOT NULL
1183 OR attribute4 IS NOT NULL
1184 OR attribute5 IS NOT NULL
1185 OR attribute6 IS NOT NULL
1186 OR attribute7 IS NOT NULL
1187 OR attribute8 IS NOT NULL
1191 OR attribute12 IS NOT NULL
1188 OR attribute9 IS NOT NULL
1189 OR attribute10 IS NOT NULL
1190 OR attribute11 IS NOT NULL
1192 OR attribute13 IS NOT NULL
1193 OR attribute14 IS NOT NULL
1194 OR attribute15 IS NOT NULL;
1195
1196 CURSOR cFlexIntAttr IS
1197 SELECT interface_header_attribute1,
1198 interface_header_attribute2,
1199 interface_header_attribute3,
1200 interface_header_attribute4,
1201 interface_header_attribute5,
1202 interface_header_attribute6,
1203 interface_header_attribute7,
1204 interface_header_attribute8,
1205 interface_header_attribute9,
1206 interface_header_attribute10,
1207 interface_header_attribute11,
1208 interface_header_attribute12,
1209 interface_header_attribute13,
1210 interface_header_attribute14,
1211 interface_header_attribute15,
1212 interface_header_context,
1213 trx_header_id
1214 FROM ar_trx_header_gt
1215 WHERE interface_header_attribute1 IS NOT NULL
1216 OR interface_header_attribute2 IS NOT NULL
1217 OR interface_header_attribute3 IS NOT NULL
1218 OR interface_header_attribute4 IS NOT NULL
1219 OR interface_header_attribute5 IS NOT NULL
1220 OR interface_header_attribute6 IS NOT NULL
1221 OR interface_header_attribute7 IS NOT NULL
1222 OR interface_header_attribute8 IS NOT NULL
1223 OR interface_header_attribute9 IS NOT NULL
1224 OR interface_header_attribute10 IS NOT NULL
1225 OR interface_header_attribute11 IS NOT NULL
1226 OR interface_header_attribute12 IS NOT NULL
1227 OR interface_header_attribute13 IS NOT NULL
1228 OR interface_header_attribute14 IS NOT NULL
1229 OR interface_header_attribute15 IS NOT NULL;
1230
1231 CURSOR clFlexIntAttr IS
1232 SELECT interface_line_attribute1,
1233 interface_line_attribute2,
1234 interface_line_attribute3,
1235 interface_line_attribute4,
1236 interface_line_attribute5,
1237 interface_line_attribute6,
1238 interface_line_attribute7,
1239 interface_line_attribute8,
1240 interface_line_attribute9,
1241 interface_line_attribute10,
1242 interface_line_attribute11,
1243 interface_line_attribute12,
1244 interface_line_attribute13,
1245 interface_line_attribute14,
1246 interface_line_attribute15,
1247 interface_line_context,
1248 trx_header_id,
1249 trx_line_id,
1250 customer_trx_line_id,
1251 customer_trx_id
1252 FROM ar_trx_lines_gt
1253 WHERE interface_line_attribute1 IS NOT NULL
1254 OR interface_line_attribute2 IS NOT NULL
1255 OR interface_line_attribute3 IS NOT NULL
1256 OR interface_line_attribute4 IS NOT NULL
1257 OR interface_line_attribute5 IS NOT NULL
1258 OR interface_line_attribute6 IS NOT NULL
1259 OR interface_line_attribute7 IS NOT NULL
1260 OR interface_line_attribute8 IS NOT NULL
1261 OR interface_line_attribute9 IS NOT NULL
1262 OR interface_line_attribute10 IS NOT NULL
1263 OR interface_line_attribute11 IS NOT NULL
1264 OR interface_line_attribute12 IS NOT NULL
1265 OR interface_line_attribute13 IS NOT NULL
1266 OR interface_line_attribute14 IS NOT NULL
1267 OR interface_line_attribute15 IS NOT NULL;
1268
1269 CURSOR clFlexAttr IS
1270 SELECT attribute1, attribute2, attribute3,
1271 attribute4, attribute5, attribute6,
1272 attribute7, attribute8, attribute9,
1273 attribute10, attribute11, attribute12,
1274 attribute13, attribute14, attribute15,
1275 attribute_category,
1276 trx_header_id,
1277 trx_line_id
1278 FROM ar_trx_lines_gt
1279 WHERE attribute1 IS NOT NULL
1280 OR attribute2 IS NOT NULL
1281 OR attribute3 IS NOT NULL
1282 OR attribute4 IS NOT NULL
1283 OR attribute5 IS NOT NULL
1284 OR attribute6 IS NOT NULL
1285 OR attribute7 IS NOT NULL
1286 OR attribute8 IS NOT NULL
1287 OR attribute9 IS NOT NULL
1288 OR attribute10 IS NOT NULL
1289 OR attribute11 IS NOT NULL
1290 OR attribute12 IS NOT NULL
1291 OR attribute13 IS NOT NULL
1292 OR attribute14 IS NOT NULL
1293 OR attribute15 IS NOT NULL;
1294
1295 CURSOR cdFlexAttr IS
1296 SELECT attribute1, attribute2, attribute3,
1297 attribute4, attribute5, attribute6,
1298 attribute7, attribute8, attribute9,
1299 attribute10, attribute11, attribute12,
1300 attribute13, attribute14, attribute15,
1301 attribute_category,
1302 trx_header_id,
1303 trx_line_id,
1304 trx_dist_id
1305 FROM ar_trx_dist_gt
1306 WHERE attribute1 IS NOT NULL
1307 OR attribute2 IS NOT NULL
1308 OR attribute3 IS NOT NULL
1309 OR attribute4 IS NOT NULL
1310 OR attribute5 IS NOT NULL
1311 OR attribute6 IS NOT NULL
1312 OR attribute7 IS NOT NULL
1313 OR attribute8 IS NOT NULL
1314 OR attribute9 IS NOT NULL
1315 OR attribute10 IS NOT NULL
1319 OR attribute14 IS NOT NULL
1316 OR attribute11 IS NOT NULL
1317 OR attribute12 IS NOT NULL
1318 OR attribute13 IS NOT NULL
1320 OR attribute15 IS NOT NULL;
1321
1322 CURSOR csFlexAttr IS
1323 SELECT attribute1, attribute2, attribute3,
1324 attribute4, attribute5, attribute6,
1325 attribute7, attribute8, attribute9,
1326 attribute10, attribute11, attribute12,
1327 attribute13, attribute14, attribute15,
1328 attribute_category,
1329 trx_header_id,
1330 trx_line_id,
1331 trx_salescredit_id
1332 FROM ar_trx_salescredits_gt
1333 WHERE attribute1 IS NOT NULL
1334 OR attribute2 IS NOT NULL
1335 OR attribute3 IS NOT NULL
1336 OR attribute4 IS NOT NULL
1337 OR attribute5 IS NOT NULL
1338 OR attribute6 IS NOT NULL
1339 OR attribute7 IS NOT NULL
1340 OR attribute8 IS NOT NULL
1341 OR attribute9 IS NOT NULL
1342 OR attribute10 IS NOT NULL
1343 OR attribute11 IS NOT NULL
1344 OR attribute12 IS NOT NULL
1345 OR attribute13 IS NOT NULL
1346 OR attribute14 IS NOT NULL
1347 OR attribute15 IS NOT NULL;
1348 BEGIN
1349 IF pg_debug = 'Y'
1350 THEN
1351 ar_invoice_utils.debug ('AR_INVOICE_UTILS.validate_hd_desc_flex (+)' );
1352 END IF;
1353 x_return_status := FND_API.G_RET_STS_SUCCESS;
1354
1355 -- call flex validation rtn for all header
1356 IF p_validation_type = 'HEADER'
1357 THEN
1358 FOR cFlexAttrRec IN cFlexAttr
1359 LOOP
1360 p_trx_attr_rec.attribute_category := cFlexAttrRec.attribute_category;
1361 p_trx_attr_rec.attribute1 := cFlexAttrRec.attribute1;
1362 p_trx_attr_rec.attribute2 := cFlexAttrRec.attribute2;
1363 p_trx_attr_rec.attribute3 := cFlexAttrRec.attribute3;
1364 p_trx_attr_rec.attribute4 := cFlexAttrRec.attribute4;
1365 p_trx_attr_rec.attribute5 := cFlexAttrRec.attribute5;
1366 p_trx_attr_rec.attribute6 := cFlexAttrRec.attribute6;
1367 p_trx_attr_rec.attribute7 := cFlexAttrRec.attribute7;
1368 p_trx_attr_rec.attribute8 := cFlexAttrRec.attribute8;
1369 p_trx_attr_rec.attribute9 := cFlexAttrRec.attribute9;
1370 p_trx_attr_rec.attribute10 := cFlexAttrRec.attribute10;
1371 p_trx_attr_rec.attribute11 := cFlexAttrRec.attribute11;
1372 p_trx_attr_rec.attribute12 := cFlexAttrRec.attribute12;
1373 p_trx_attr_rec.attribute13 := cFlexAttrRec.attribute13;
1374 p_trx_attr_rec.attribute14 := cFlexAttrRec.attribute14;
1375 p_trx_attr_rec.attribute15 := cFlexAttrRec.attribute15;
1376
1377 arp_util.Validate_Desc_Flexfield(
1378 p_desc_flex_rec => p_trx_attr_rec,
1379 p_desc_flex_name => 'RA_CUSTOMER_TRX',
1380 p_return_status => x_return_status );
1381
1382 IF x_return_status <> FND_API.G_RET_STS_SUCCESS
1383 THEN
1384 INSERT INTO ar_trx_errors_gt
1385 ( trx_header_id,
1386 error_message,
1387 invalid_value) values (
1388 cFlexAttrRec.trx_header_id,
1389 arp_standard.fnd_message('AR_INAPI_INVALID_DESC_FLEX'),
1390 'RA_CUSTOMER_TRX') ;
1391 END IF;
1392 END LOOP;
1393
1394 FOR cFlexIntAttrRec IN cFlexIntAttr
1395 LOOP
1396 p_trx_hdr_int_attr_rec.interface_header_context := cFlexIntAttrRec.interface_header_context;
1397 p_trx_hdr_int_attr_rec.interface_header_attribute1 := cFlexIntAttrRec.interface_header_attribute1;
1398 p_trx_hdr_int_attr_rec.interface_header_attribute2 := cFlexIntAttrRec.interface_header_attribute2;
1399 p_trx_hdr_int_attr_rec.interface_header_attribute3 := cFlexIntAttrRec.interface_header_attribute3;
1400 p_trx_hdr_int_attr_rec.interface_header_attribute4 := cFlexIntAttrRec.interface_header_attribute4;
1401 p_trx_hdr_int_attr_rec.interface_header_attribute5 := cFlexIntAttrRec.interface_header_attribute5;
1402 p_trx_hdr_int_attr_rec.interface_header_attribute6 := cFlexIntAttrRec.interface_header_attribute6;
1403 p_trx_hdr_int_attr_rec.interface_header_attribute7 := cFlexIntAttrRec.interface_header_attribute7;
1404 p_trx_hdr_int_attr_rec.interface_header_attribute8 := cFlexIntAttrRec.interface_header_attribute8;
1405 p_trx_hdr_int_attr_rec.interface_header_attribute9 := cFlexIntAttrRec.interface_header_attribute9;
1406 p_trx_hdr_int_attr_rec.interface_header_attribute10 := cFlexIntAttrRec.interface_header_attribute10;
1407 p_trx_hdr_int_attr_rec.interface_header_attribute11 := cFlexIntAttrRec.interface_header_attribute11;
1408 p_trx_hdr_int_attr_rec.interface_header_attribute12 := cFlexIntAttrRec.interface_header_attribute12;
1409 p_trx_hdr_int_attr_rec.interface_header_attribute13 := cFlexIntAttrRec.interface_header_attribute13;
1410 p_trx_hdr_int_attr_rec.interface_header_attribute14 := cFlexIntAttrRec.interface_header_attribute14;
1411 p_trx_hdr_int_attr_rec.interface_header_attribute15 := cFlexIntAttrRec.interface_header_attribute15;
1412
1413 Validate_int_Desc_Flex(
1414 p_desc_flex_rec => p_trx_hdr_int_attr_rec,
1415 p_desc_flex_name => 'RA_INTERFACE_HEADER',
1416 p_return_status => x_return_status );
1417 IF x_return_status <> FND_API.G_RET_STS_SUCCESS
1418 THEN
1419 INSERT INTO ar_trx_errors_gt
1420 ( trx_header_id,
1421 error_message,
1422 invalid_value) VALUES (
1423 cFlexIntAttrRec.trx_header_id,
1427 END LOOP;
1424 arp_standard.fnd_message('AR_INAPI_INVALID_DESC_FLEX'),
1425 'RA_INTERFACE_HEADER') ;
1426 END IF;
1428 ELSIF p_validation_type = 'LINES'
1429 THEN
1430 FOR clFlexAttrRec IN clFlexAttr
1431 LOOP
1432 p_trx_attr_rec.attribute_category := clFlexAttrRec.attribute_category;
1433 p_trx_attr_rec.attribute1 := clFlexAttrRec.attribute1;
1434 p_trx_attr_rec.attribute2 := clFlexAttrRec.attribute2;
1435 p_trx_attr_rec.attribute3 := clFlexAttrRec.attribute3;
1436 p_trx_attr_rec.attribute4 := clFlexAttrRec.attribute4;
1437 p_trx_attr_rec.attribute5 := clFlexAttrRec.attribute5;
1438 p_trx_attr_rec.attribute6 := clFlexAttrRec.attribute6;
1439 p_trx_attr_rec.attribute7 := clFlexAttrRec.attribute7;
1440 p_trx_attr_rec.attribute8 := clFlexAttrRec.attribute8;
1441 p_trx_attr_rec.attribute9 := clFlexAttrRec.attribute9;
1442 p_trx_attr_rec.attribute10 := clFlexAttrRec.attribute10;
1443 p_trx_attr_rec.attribute11 := clFlexAttrRec.attribute11;
1444 p_trx_attr_rec.attribute12 := clFlexAttrRec.attribute12;
1445 p_trx_attr_rec.attribute13 := clFlexAttrRec.attribute13;
1446 p_trx_attr_rec.attribute14 := clFlexAttrRec.attribute14;
1447 p_trx_attr_rec.attribute15 := clFlexAttrRec.attribute15;
1448
1449 arp_util.Validate_Desc_Flexfield(
1450 p_desc_flex_rec => p_trx_attr_rec,
1451 p_desc_flex_name => 'RA_CUSTOMER_TRX_LINES',
1452 p_return_status => x_return_status );
1453
1454 IF x_return_status <> FND_API.G_RET_STS_SUCCESS
1455 THEN
1456 INSERT INTO ar_trx_errors_gt
1457 ( trx_header_id,
1458 trx_line_id,
1459 error_message,
1460 invalid_value) values (
1461 clFlexAttrRec.trx_header_id,
1462 clFlexAttrRec.trx_line_id,
1463 arp_standard.fnd_message('AR_INAPI_INVALID_DESC_FLEX'),
1464 'RA_CUSTOMER_TRX_LINES') ;
1465 END IF;
1466 END LOOP;
1467
1468 FOR clFlexIntAttrRec IN clFlexIntAttr
1469 LOOP
1470 p_trx_line_int_attr_rec.interface_line_context := clFlexIntAttrRec.interface_line_context;
1471 p_trx_line_int_attr_rec.interface_line_attribute1 := clFlexIntAttrRec.interface_line_attribute1;
1472 p_trx_line_int_attr_rec.interface_line_attribute2 := clFlexIntAttrRec.interface_line_attribute2;
1473 p_trx_line_int_attr_rec.interface_line_attribute3 := clFlexIntAttrRec.interface_line_attribute3;
1474 p_trx_line_int_attr_rec.interface_line_attribute4 := clFlexIntAttrRec.interface_line_attribute4;
1475 p_trx_line_int_attr_rec.interface_line_attribute5 := clFlexIntAttrRec.interface_line_attribute5;
1476 p_trx_line_int_attr_rec.interface_line_attribute6 := clFlexIntAttrRec.interface_line_attribute6;
1477 p_trx_line_int_attr_rec.interface_line_attribute7 := clFlexIntAttrRec.interface_line_attribute7;
1478 p_trx_line_int_attr_rec.interface_line_attribute8 := clFlexIntAttrRec.interface_line_attribute8;
1479 p_trx_line_int_attr_rec.interface_line_attribute9 := clFlexIntAttrRec.interface_line_attribute9;
1480 p_trx_line_int_attr_rec.interface_line_attribute10 := clFlexIntAttrRec.interface_line_attribute10;
1481 p_trx_line_int_attr_rec.interface_line_attribute11 := clFlexIntAttrRec.interface_line_attribute11;
1482 p_trx_line_int_attr_rec.interface_line_attribute12 := clFlexIntAttrRec.interface_line_attribute12;
1483 p_trx_line_int_attr_rec.interface_line_attribute13 := clFlexIntAttrRec.interface_line_attribute13;
1484 p_trx_line_int_attr_rec.interface_line_attribute14 := clFlexIntAttrRec.interface_line_attribute14;
1485 p_trx_line_int_attr_rec.interface_line_attribute15 := clFlexIntAttrRec.interface_line_attribute15;
1486
1487 -- first validate the uniqueness
1488 IF ( NOT unique_flex(
1489 p_ctl_id => clFlexIntAttrRec.customer_trx_line_id,
1490 p_customer_trx_id => clFlexIntAttrRec.customer_trx_id,
1491 p_customer_trx_line_id => clFlexIntAttrRec.customer_trx_line_id,
1492 p_int_line_rec => p_trx_line_int_attr_rec) )
1493 THEN
1494 INSERT INTO ar_trx_errors_gt
1495 ( trx_header_id,
1496 trx_line_id,
1497 error_message,
1498 invalid_value) VALUES (
1499 clFlexIntAttrRec.trx_header_id,
1500 clFlexIntAttrRec.trx_line_id,
1501 arp_standard.fnd_message('AR_INAPI_NONUNIQUE_DESC_FLEX'),
1502 'RA_INTERFACE_LINES') ;
1503 END IF;
1504
1505 Validate_Line_Int_Flex(
1506 p_desc_flex_rec => p_trx_line_int_attr_rec,
1507 p_desc_flex_name => 'RA_INTERFACE_LINES',
1508 p_return_status => x_return_status );
1509 IF x_return_status <> FND_API.G_RET_STS_SUCCESS
1510 THEN
1511 INSERT INTO ar_trx_errors_gt
1512 ( trx_header_id,
1513 trx_line_id,
1514 error_message,
1515 invalid_value) VALUES (
1516 clFlexIntAttrRec.trx_header_id,
1517 clFlexIntAttrRec.trx_line_id,
1518 arp_standard.fnd_message('AR_INAPI_INVALID_DESC_FLEX'),
1519 'RA_INTERFACE_LINES') ;
1520 END IF;
1521 END LOOP;
1522 ELSIF p_validation_type = 'DISTRIBUTIONS'
1523 THEN
1524 FOR cdFlexAttrRec IN cdFlexAttr
1525 LOOP
1526 p_trx_attr_rec.attribute_category := cdFlexAttrRec.attribute_category;
1530 p_trx_attr_rec.attribute4 := cdFlexAttrRec.attribute4;
1527 p_trx_attr_rec.attribute1 := cdFlexAttrRec.attribute1;
1528 p_trx_attr_rec.attribute2 := cdFlexAttrRec.attribute2;
1529 p_trx_attr_rec.attribute3 := cdFlexAttrRec.attribute3;
1531 p_trx_attr_rec.attribute5 := cdFlexAttrRec.attribute5;
1532 p_trx_attr_rec.attribute6 := cdFlexAttrRec.attribute6;
1533 p_trx_attr_rec.attribute7 := cdFlexAttrRec.attribute7;
1534 p_trx_attr_rec.attribute8 := cdFlexAttrRec.attribute8;
1535 p_trx_attr_rec.attribute9 := cdFlexAttrRec.attribute9;
1536 p_trx_attr_rec.attribute10 := cdFlexAttrRec.attribute10;
1537 p_trx_attr_rec.attribute11 := cdFlexAttrRec.attribute11;
1538 p_trx_attr_rec.attribute12 := cdFlexAttrRec.attribute12;
1539 p_trx_attr_rec.attribute13 := cdFlexAttrRec.attribute13;
1540 p_trx_attr_rec.attribute14 := cdFlexAttrRec.attribute14;
1541 p_trx_attr_rec.attribute15 := cdFlexAttrRec.attribute15;
1542
1543 arp_util.Validate_Desc_Flexfield(
1544 p_desc_flex_rec => p_trx_attr_rec,
1545 p_desc_flex_name => 'RA_CUST_TRX_LINE_GL_DIST',
1546 p_return_status => x_return_status );
1547
1548 IF x_return_status <> FND_API.G_RET_STS_SUCCESS
1549 THEN
1550 INSERT INTO ar_trx_errors_gt
1551 ( trx_header_id,
1552 trx_line_id,
1553 trx_dist_id,
1554 error_message,
1555 invalid_value) values (
1556 cdFlexAttrRec.trx_header_id,
1557 cdFlexAttrRec.trx_line_id,
1558 cdFlexAttrRec.trx_dist_id,
1559 arp_standard.fnd_message('AR_INAPI_INVALID_DESC_FLEX'),
1560 'RA_CUST_TRX_LINE_GL_DIST') ;
1561 END IF;
1562 END LOOP;
1563 ELSIF p_validation_type = 'SALESREPS'
1564 THEN
1565 FOR csFlexAttrRec IN csFlexAttr
1566 LOOP
1567 p_trx_attr_rec.attribute_category := csFlexAttrRec.attribute_category;
1568 p_trx_attr_rec.attribute1 := csFlexAttrRec.attribute1;
1569 p_trx_attr_rec.attribute2 := csFlexAttrRec.attribute2;
1570 p_trx_attr_rec.attribute3 := csFlexAttrRec.attribute3;
1571 p_trx_attr_rec.attribute4 := csFlexAttrRec.attribute4;
1572 p_trx_attr_rec.attribute5 := csFlexAttrRec.attribute5;
1573 p_trx_attr_rec.attribute6 := csFlexAttrRec.attribute6;
1574 p_trx_attr_rec.attribute7 := csFlexAttrRec.attribute7;
1575 p_trx_attr_rec.attribute8 := csFlexAttrRec.attribute8;
1576 p_trx_attr_rec.attribute9 := csFlexAttrRec.attribute9;
1577 p_trx_attr_rec.attribute10 := csFlexAttrRec.attribute10;
1578 p_trx_attr_rec.attribute11 := csFlexAttrRec.attribute11;
1579 p_trx_attr_rec.attribute12 := csFlexAttrRec.attribute12;
1580 p_trx_attr_rec.attribute13 := csFlexAttrRec.attribute13;
1581 p_trx_attr_rec.attribute14 := csFlexAttrRec.attribute14;
1582 p_trx_attr_rec.attribute15 := csFlexAttrRec.attribute15;
1583
1584 arp_util.Validate_Desc_Flexfield(
1585 p_desc_flex_rec => p_trx_attr_rec,
1586 p_desc_flex_name => 'RA_CUST_TRX_LINE_SALESREPS',
1587 p_return_status => x_return_status );
1588
1589 IF x_return_status <> FND_API.G_RET_STS_SUCCESS
1590 THEN
1591 INSERT INTO ar_trx_errors_gt
1592 ( trx_header_id,
1593 trx_line_id,
1594 trx_salescredit_id,
1595 error_message,
1596 invalid_value) values (
1597 csFlexAttrRec.trx_header_id,
1598 csFlexAttrRec.trx_line_id,
1599 csFlexAttrRec.trx_salescredit_id,
1600 arp_standard.fnd_message('AR_INAPI_INVALID_DESC_FLEX'),
1601 'RA_CUST_TRX_LINE_SALESREPS') ;
1602 END IF;
1603 END LOOP;
1604 END IF; -- end of validation_level
1605 -- assign the status to success agin so that next validation
1606 -- can continue.
1607 x_return_status := FND_API.G_RET_STS_SUCCESS;
1608 END validate_desc_flex;
1609
1610
1611 END AR_INVOICE_VALIDATE_FLEX;