[Home] [Help]
PACKAGE BODY: APPS.ARP_DESCR_FLEX
Source
1 package body ARP_DESCR_FLEX as
2 /* $Header: ARPLDFSB.pls 115.3 2002/11/15 02:40:33 anukumar ship $ */
3
4
5 /*---------------------------------------------------------------------------*
6 | |
7 | PUBLIC PROCEDURE: get_concatenated_segments |
8 | |
9 | This procedure returns the concatenated segments and context values |
10 | of the descriptive flexfields in the ra_customer_trx and |
11 | ra_customer_trx_lines tables. |
12 | For now, this function hardcodes all column and flexfield information. |
13 | When PL/SQL supports dynamic SQL, this function could be rewritten to |
14 | be more generic. |
15 | |
16 | EXAMPLES |
17 | |
18 | |
19 *---------------------------------------------------------------------------*/
20
21 procedure get_concatenated_segments( p_flex_name in varchar2,
22 p_table_name in varchar2,
23 p_customer_trx_id in number,
24 p_customer_trx_line_id in number,
25 p_concatenated_segments in out NOCOPY varchar2,
26 p_context in out NOCOPY varchar2) IS
27
28
29 first boolean;
30
31 cursor ra_customer_trx_lines_C( p_flex_name in varchar2,
32 p_customer_trx_line_id in number) IS
33 select decode(u.descriptive_flexfield_name,
34 'RA_INTERFACE_LINES',
35 decode(u.application_column_name,
36 'INTERFACE_LINE_ATTRIBUTE1', l.interface_line_attribute1,
37 'INTERFACE_LINE_ATTRIBUTE2', l.interface_line_attribute2,
38 'INTERFACE_LINE_ATTRIBUTE3', l.interface_line_attribute3,
39 'INTERFACE_LINE_ATTRIBUTE4', l.interface_line_attribute4,
40 'INTERFACE_LINE_ATTRIBUTE5', l.interface_line_attribute5,
41 'INTERFACE_LINE_ATTRIBUTE6', l.interface_line_attribute6,
42 'INTERFACE_LINE_ATTRIBUTE7', l.interface_line_attribute7,
43 'INTERFACE_LINE_ATTRIBUTE8', l.interface_line_attribute8,
44 'INTERFACE_LINE_ATTRIBUTE9', l.interface_line_attribute9,
45 'INTERFACE_LINE_ATTRIBUTE10', l.interface_line_attribute10,
46 'INTERFACE_LINE_ATTRIBUTE11', l.interface_line_attribute11,
47 'INTERFACE_LINE_ATTRIBUTE12', l.interface_line_attribute12,
48 'INTERFACE_LINE_ATTRIBUTE13', l.interface_line_attribute13,
49 'INTERFACE_LINE_ATTRIBUTE14', l.interface_line_attribute14,
50 'INTERFACE_LINE_ATTRIBUTE15', l.interface_line_attribute15),
51 'RA_CUSTOMER_TRX_LINES_GOV', l.default_ussgl_transaction_code,
52 'RA_CUSTOMER_TRX_LINES', decode(u.application_column_name,
53 'ATTRIBUTE1', l.attribute1,
54 'ATTRIBUTE2', l.attribute2,
55 'ATTRIBUTE3', l.attribute3,
56 'ATTRIBUTE4', l.attribute4,
57 'ATTRIBUTE5', l.attribute5,
58 'ATTRIBUTE6', l.attribute6,
59 'ATTRIBUTE7', l.attribute7,
60 'ATTRIBUTE8', l.attribute8,
61 'ATTRIBUTE9', l.attribute9,
62 'ATTRIBUTE10', l.attribute10,
63 'ATTRIBUTE11', l.attribute11,
64 'ATTRIBUTE12', l.attribute12,
65 'ATTRIBUTE13', l.attribute13,
66 'ATTRIBUTE14', l.attribute14,
67 'ATTRIBUTE15', l.attribute15)
68 ) segment_value,
69 decode(u.descriptive_flexfield_name,
70 'RA_INTERFACE_LINES', l.interface_line_context,
71 'RA_CUSTOMER_TRX_LINES_GOV', l.default_ussgl_trx_code_context,
72 'RA_CUSTOMER_TRX_LINES', l.attribute_category,
73 '') context,
74 f.concatenated_segment_delimiter delimiter
75 from fnd_descr_flex_column_usages u,
76 fnd_descriptive_flexs f,
77 ra_customer_trx_lines l
78 where u.descriptive_flexfield_name = p_flex_name
79 and u.application_id = 222
80 and u.enabled_flag = 'Y'
81 and u.descriptive_flexfield_name = f.descriptive_flexfield_name
82 and u.application_id = f.application_id
83 and nvl(
84 decode(u.descriptive_flexfield_name,
85 'RA_INTERFACE_LINES', l.interface_line_context,
86 'RA_CUSTOMER_TRX_LINES_GOV', l.default_ussgl_trx_code_context,
87 'RA_CUSTOMER_TRX_LINES', l.attribute_category,
88 ''),
89 'Global Data Elements'
90 ) = descriptive_flex_context_code
91 and customer_trx_line_id = p_customer_trx_line_id
92 order by column_seq_num;
93
94 cursor ra_customer_trx_C( p_flex_name in varchar2,
95 p_customer_trx_id in number) IS
96 select decode(u.descriptive_flexfield_name,
97 'RA_INTERFACE_HEADER',
98 decode(u.application_column_name,
99 'INTERFACE_HEADER_ATTRIBUTE1', t.interface_header_attribute1,
100 'INTERFACE_HEADER_ATTRIBUTE2', t.interface_header_attribute2,
101 'INTERFACE_HEADER_ATTRIBUTE3', t.interface_header_attribute3,
102 'INTERFACE_HEADER_ATTRIBUTE4', t.interface_header_attribute4,
103 'INTERFACE_HEADER_ATTRIBUTE5', t.interface_header_attribute5,
104 'INTERFACE_HEADER_ATTRIBUTE6', t.interface_header_attribute6,
105 'INTERFACE_HEADER_ATTRIBUTE7', t.interface_header_attribute7,
106 'INTERFACE_HEADER_ATTRIBUTE8', t.interface_header_attribute8,
107 'INTERFACE_HEADER_ATTRIBUTE9', t.interface_header_attribute9,
108 'INTERFACE_HEADER_ATTRIBUTE10', t.interface_header_attribute10,
109 'INTERFACE_HEADER_ATTRIBUTE11', t.interface_header_attribute11,
110 'INTERFACE_HEADER_ATTRIBUTE12', t.interface_header_attribute12,
111 'INTERFACE_HEADER_ATTRIBUTE13', t.interface_header_attribute13,
112 'INTERFACE_HEADER_ATTRIBUTE14', t.interface_header_attribute14,
113 'INTERFACE_HEADER_ATTRIBUTE15', t.interface_header_attribute15),
114 'RA_CUSTOMER_TRX_GOV', t.default_ussgl_transaction_code,
115 'RA_CUSTOMER_TRX', decode(u.application_column_name,
116 'ATTRIBUTE1', t.attribute1,
117 'ATTRIBUTE2', t.attribute2,
118 'ATTRIBUTE3', t.attribute3,
119 'ATTRIBUTE4', t.attribute4,
120 'ATTRIBUTE5', t.attribute5,
121 'ATTRIBUTE6', t.attribute6,
122 'ATTRIBUTE7', t.attribute7,
123 'ATTRIBUTE8', t.attribute8,
124 'ATTRIBUTE9', t.attribute9,
125 'ATTRIBUTE10', t.attribute10,
126 'ATTRIBUTE11', t.attribute11,
127 'ATTRIBUTE12', t.attribute12,
128 'ATTRIBUTE13', t.attribute13,
129 'ATTRIBUTE14', t.attribute14,
130 'ATTRIBUTE15', t.attribute15)
131 ) segment_value,
132 decode(u.descriptive_flexfield_name,
133 'RA_INTERFACE_HEADER', t.interface_header_context,
134 'RA_CUSTOMER_TRX_GOV', t.default_ussgl_trx_code_context,
135 'RA_CUSTOMER_TRX', t.attribute_category,
136 '') context,
137 f.concatenated_segment_delimiter delimiter
138 from fnd_descr_flex_column_usages u,
139 fnd_descriptive_flexs f,
140 ra_customer_trx t
141 where u.descriptive_flexfield_name = p_flex_name
142 and u.application_id = 222
143 and u.enabled_flag = 'Y'
144 and u.descriptive_flexfield_name = f.descriptive_flexfield_name
145 and u.application_id = f.application_id
146 and nvl(
147 decode(u.descriptive_flexfield_name,
148 'RA_INTERFACE_HEADER', t.interface_header_context,
149 'RA_CUSTOMER_TRX_GOV', t.default_ussgl_trx_code_context,
150 'RA_CUSTOMER_TRX', t.attribute_category,
151 ''),
152 'Global Data Elements'
153 ) = descriptive_flex_context_code
154 and customer_trx_id = p_customer_trx_id
155 order by column_seq_num;
156
157 begin
158
159 first := true;
160 p_concatenated_segments := '';
161 p_context := '';
162
163 /*----------------------------------------------------------------+
164 | Select each segment of the flexfield in order and concatenate |
165 | it onto the previous segments. |
166 +----------------------------------------------------------------*/
167
168 if (p_table_name = 'RA_CUSTOMER_TRX_LINES')
169 then
170 FOR segments in ra_customer_trx_lines_C(p_flex_name,
171 p_customer_trx_line_id )
172 LOOP
173 if (first <> true)
174 then p_concatenated_segments := p_concatenated_segments ||
175 segments.delimiter;
176 else first := false;
177 end if;
178
179 p_concatenated_segments := p_concatenated_segments ||
180 segments.segment_value;
181
182 p_context := segments.context;
183
184 END LOOP;
185 end if;
186
187 if (p_table_name = 'RA_CUSTOMER_TRX')
188 then
189 FOR segments in ra_customer_trx_C(p_flex_name,
190 p_customer_trx_id )
191 LOOP
192 if (first <> true)
193 then p_concatenated_segments := p_concatenated_segments ||
194 segments.delimiter;
195 else first := false;
196 end if;
197
198 p_concatenated_segments := p_concatenated_segments ||
199 segments.segment_value;
200
201 p_context := segments.context;
202
203 END LOOP;
204 end if;
205
206 end;
207
208
209
210 end ARP_DESCR_FLEX;