[Home] [Help]
Skip to content
PACKAGE BODY: APPS.ARP_RAXINV_MLS_PACKAGE
Source
1 PACKAGE body ARP_RAXINV_MLS_PACKAGE AS
2 /* $Header: ARINVMLB.pls 120.2.12010000.2 2009/05/12 07:13:41 pbapna ship $ */
3
4 function ARP_RAXINV_MLS_FUNCTION return varchar2 is
5
6 -- variables used by build_where_clause
7 P_choice varchar2(40) := NULL;
8 P_open_invoice varchar2(1) := NULL;
9 P_cust_trx_type_id number := NULL;
10 p_cust_trx_class varchar2(40) := NULL;
11 P_installment_number number := NULL;
12 P_dates_low date := NULL;
13 P_dates_high date := NULL;
14 P_customer_id number := NULL;
15 P_customer_class_code varchar2(40) := NULL;
16 P_trx_number_low varchar2(40) := NULL;
17 P_trx_number_high varchar2(40) := NULL;
18 P_batch_id number := NULL;
19 P_customer_trx_id number := NULL;
20 p_adj_number_low varchar2(20) := NULL;
21 p_adj_number_high varchar2(20) := NULL;
22 p_adj_dates_low date := NULL;
23 p_adj_dates_high date := NULL;
24
25 p_where1 varchar2(8096);
26 p_where2 varchar2(8096);
27 p_table1 varchar2(8096);
28 p_table2 varchar2(8096);
29
30 -- variables used by ARP_RAXINV_MLS_FUNCTION
31 p_userenv_lang varchar2(4);
32 p_base_lang varchar2(4);
33 retval number;
34 parm_number number;
35 parm_name varchar2(80);
39 select_sql1 varchar2(10000);
36 cr CONSTANT char(1) := '
37 ';
38 select_sql1_c number;
40 select_sql2_c number;
41 select_sql2 varchar2(10000);
42 lang_str varchar2(240);
43
44 TYPE select_rec_type IS RECORD
45 (language VARCHAR2(4));
46
47 select_rec1 select_rec_type;
48 select_rec2 select_rec_type;
49 null_rec CONSTANT select_rec_type := select_rec1;
50 l_ignore INTEGER;
51
52 BEGIN
53
54 select substr(userenv('LANG'),1,4)
55 into p_userenv_lang
56 from dual;
57
58 select language_code
59 into p_base_lang
60 from fnd_languages
61 where installed_flag = 'B';
62
63
64 arp_standard.debug('ARP_RAXINV_MLS_FUNCTION');
65
66 arp_standard.debug('P_USERENV_LANG = ' || p_userenv_lang);
67
68 /* Read in Parameter Values supplied by user */
69
70 retval := FND_REQUEST_INFO.GET_PARAM_NUMBER('Transaction Class',parm_number);
71 if retval = -1 then
72 P_CUST_TRX_CLASS := NULL;
73 else
74 P_CUST_TRX_CLASS := FND_REQUEST_INFO.GET_PARAMETER(parm_number);
75 end if;
76 arp_standard.debug('P_CUST_TRX_CLASS ='|| P_CUST_TRX_CLASS);
77
78 retval := FND_REQUEST_INFO.GET_PARAM_NUMBER('Transaction Type',parm_number);
79 if retval = -1 then
80 P_CUST_TRX_TYPE_ID := NULL;
81 else
82 P_CUST_TRX_TYPE_ID := FND_REQUEST_INFO.GET_PARAMETER(parm_number);
83 end if;
84 arp_standard.debug('P_CUST_TRX_TYPE_ID ='|| to_char(P_CUST_TRX_TYPE_ID));
85
86 retval := FND_REQUEST_INFO.GET_PARAM_NUMBER('Transaction Number Low',parm_number);
87 if retval = -1 then
88 P_TRX_NUMBER_LOW := NULL;
89 else
90 P_TRX_NUMBER_LOW := FND_REQUEST_INFO.GET_PARAMETER(parm_number);
91 end if;
92 arp_standard.debug('P_TRX_NUMBER_LOW ='|| P_TRX_NUMBER_LOW);
93
94 retval := FND_REQUEST_INFO.GET_PARAM_NUMBER('Transaction Number High',parm_number);
95 if retval = -1 then
96 P_TRX_NUMBER_HIGH := NULL;
97 else
98 P_TRX_NUMBER_HIGH := FND_REQUEST_INFO.GET_PARAMETER(parm_number);
99 end if;
100 arp_standard.debug('P_TRX_NUMBER_HIGH ='|| P_TRX_NUMBER_HIGH);
101
102 retval := FND_REQUEST_INFO.GET_PARAM_NUMBER('Print Date Low',parm_number);
103 if retval = -1 then
104 P_DATES_LOW := NULL;
105 else
106 P_DATES_LOW := fnd_date.canonical_to_date(FND_REQUEST_INFO.GET_PARAMETER(parm_number));
107 end if;
108 arp_standard.debug('P_DATES_LOW ='|| to_char(P_DATES_LOW));
109
110 retval := FND_REQUEST_INFO.GET_PARAM_NUMBER('Print Date High',parm_number);
111 if retval = -1 then
112 P_DATES_HIGH := NULL;
113 else
114 P_DATES_HIGH := fnd_date.canonical_to_date(FND_REQUEST_INFO.GET_PARAMETER(parm_number));
115 end if;
116 arp_standard.debug('P_DATES_HIGH ='|| to_char(P_DATES_HIGH));
117
118 retval := FND_REQUEST_INFO.GET_PARAM_NUMBER('Customer Class',parm_number);
119 if retval = -1 then
120 P_CUSTOMER_CLASS_CODE := NULL;
121 else
122 P_CUSTOMER_CLASS_CODE := FND_REQUEST_INFO.GET_PARAMETER(parm_number);
123 end if;
124 arp_standard.debug('P_CUSTOMER_CLASS_CODE ='|| P_CUSTOMER_CLASS_CODE);
125
126 retval := FND_REQUEST_INFO.GET_PARAM_NUMBER('Customer',parm_number);
127 if retval = -1 then
128 P_CUSTOMER_ID := NULL;
129 else
130 P_CUSTOMER_ID := FND_REQUEST_INFO.GET_PARAMETER(parm_number);
131 end if;
132 arp_standard.debug('P_CUSTOMER_ID ='|| to_char(P_CUSTOMER_ID));
133
134 retval := FND_REQUEST_INFO.GET_PARAM_NUMBER('Installment Number',parm_number);
135 if retval = -1 then
136 P_INSTALLMENT_NUMBER := NULL;
137 else
138 P_INSTALLMENT_NUMBER := FND_REQUEST_INFO.GET_PARAMETER(parm_number);
139 end if;
140 arp_standard.debug('P_INSTALLMENT_NUMBER ='|| to_char(P_INSTALLMENT_NUMBER));
141
142 retval := FND_REQUEST_INFO.GET_PARAM_NUMBER('Open Invoices Only',parm_number);
143 if retval = -1 then
144 P_OPEN_INVOICE := NULL;
145 else
146 P_OPEN_INVOICE := FND_REQUEST_INFO.GET_PARAMETER(parm_number);
147 end if;
148 arp_standard.debug('P_OPEN_INVOICE ='|| P_OPEN_INVOICE);
149
150 retval := FND_REQUEST_INFO.GET_PARAM_NUMBER('Print Choice',parm_number);
151 if retval = -1 then
152 P_CHOICE := NULL;
153 else
154 P_CHOICE := FND_REQUEST_INFO.GET_PARAMETER(parm_number);
155 end if;
156 arp_standard.debug('P_CHOICE ='|| P_CHOICE);
157
158 retval := FND_REQUEST_INFO.GET_PARAM_NUMBER('Adjustment Number Low',parm_number);
159 if retval = -1 then
160 P_ADJ_NUMBER_LOW := NULL;
161 else
162 P_ADJ_NUMBER_LOW := FND_REQUEST_INFO.GET_PARAMETER(parm_number);
163 end if;
164 arp_standard.debug('P_ADJ_NUMBER_LOW ='|| P_ADJ_NUMBER_LOW);
165
166 retval := FND_REQUEST_INFO.GET_PARAM_NUMBER('Adjustment Number High',parm_number);
167 if retval = -1 then
168 P_ADJ_NUMBER_HIGH := NULL;
169 else
170 P_ADJ_NUMBER_HIGH := FND_REQUEST_INFO.GET_PARAMETER(parm_number);
171 end if;
172 arp_standard.debug('P_ADJ_NUMBER_HIGH ='|| P_ADJ_NUMBER_HIGH);
173
174 retval := FND_REQUEST_INFO.GET_PARAM_NUMBER('Adjustment Date Low',parm_number);
175 if retval = -1 then
176 P_ADJ_DATES_LOW := NULL;
177 else
178 P_ADJ_DATES_LOW := fnd_date.canonical_to_date(FND_REQUEST_INFO.GET_PARAMETER(parm_number));
179 end if;
180 arp_standard.debug('P_ADJ_DATES_LOW ='|| to_char(P_ADJ_DATES_LOW));
181
182 retval := FND_REQUEST_INFO.GET_PARAM_NUMBER('Adjustment Date High',parm_number);
183 if retval = -1 then
184 P_ADJ_DATES_HIGH := NULL;
188 arp_standard.debug('P_ADJ_DATES_HIGH ='|| to_char(P_ADJ_DATES_HIGH));
185 else
186 P_ADJ_DATES_HIGH := fnd_date.canonical_to_date(FND_REQUEST_INFO.GET_PARAMETER(parm_number));
187 end if;
189
190 retval := FND_REQUEST_INFO.GET_PARAM_NUMBER('Batch',parm_number);
191 if retval = -1 then
192 P_BATCH_ID := NULL;
193 else
194 P_BATCH_ID := FND_REQUEST_INFO.GET_PARAMETER(parm_number);
195 end if;
196 arp_standard.debug('P_BATCH_ID ='|| to_char(P_BATCH_ID));
197
198 arp_standard.debug('P_CUSTOMER_TRX_ID = ' || to_char(P_CUSTOMER_TRX_ID));
199
200 arp_standard.debug('Will call BUILD_WHERE_CLAUSE');
201
202 ARP_TRX_SELECT_CONTROL.build_where_clause (
203 P_CHOICE, -- IN varchar2,
204 P_OPEN_INVOICE, -- IN varchar2,
205 P_CUST_TRX_TYPE_ID, -- IN number,
206 P_CUST_TRX_CLASS, -- IN varchar2,
207 P_INSTALLMENT_NUMBER, -- IN number,
208 P_DATES_LOW, -- IN date,
209 P_DATES_HIGH, -- IN date,
210 P_CUSTOMER_ID, -- IN number,
211 P_CUSTOMER_CLASS_CODE, -- IN varchar2,
212 P_TRX_NUMBER_LOW, -- IN varchar2,
213 P_TRX_NUMBER_HIGH, -- IN varchar2,
214 P_BATCH_ID, -- IN number,
215 P_CUSTOMER_TRX_ID, -- IN number,
216 P_ADJ_NUMBER_LOW, -- in varchar2,
217 P_ADJ_NUMBER_HIGH, -- in varchar2,
218 P_ADJ_DATES_LOW, -- in date,
219 P_ADJ_DATES_HIGH, -- in date,
220 P_WHERE1, -- OUT varchar2,
221 P_WHERE2, -- OUT varchar2,
222 P_TABLE1, -- OUT varchar2,
223 P_TABLE2, -- OUT varchar2,
224 'MLS' -- in varchar2
225 );
226
227
228 arp_standard.debug('done with BUILD_WHERE_CLAUSE');
229
230 ------------------------------------------------
231 -- To fix bug number 1170600 and 1158411.Changed the default
232 -- langauge from userenv lang to base lang, in case the language
233 -- of the customer is null
234 ------------------------------------------------
235
236 /*Bug 8448291,changing base_lang to p_userenv_lang*/
237
238 select_sql1 :=
239 'select distinct(nvl(rtrim(substr(loc.language,1,4)), ''' || p_userenv_lang || ''')) language ' || cr ||
240 'from ' || p_table1 || cr ||
241 'where ' || cr || p_where1;
242
243 select_sql2 :=
244 'select distinct(nvl(rtrim(substr(loc.language,1,4)), ''' || p_userenv_lang || ''')) language ' || cr ||
245 'from ' || p_table2 || cr ||
246 'where ' || cr || p_where2;
247
248 arp_standard.debug('raxinv.select_sql1 = ' || cr ||
249 select_sql1 || cr );
250
251 arp_standard.debug('raxinv.select_sql2 = ' || cr ||
252 select_sql2 || cr );
253
254
255 ------------------------------------------------
256 -- Parse sql stmts
257 ------------------------------------------------
258
259 BEGIN
260 arp_standard.debug( ' Parsing select_sql1 stmt');
261 select_sql1_c := dbms_sql.open_cursor;
262 dbms_sql.parse( select_sql1_c, select_sql1, dbms_sql.v7 );
263
264 EXCEPTION
265 WHEN OTHERS THEN
266 arp_standard.debug( 'EXCEPTION: Error parsing select_sql1 stmt' );
267 RAISE;
268 END;
269
270 BEGIN
271 arp_standard.debug( ' Parsing select_sql2 stmt');
272 select_sql2_c := dbms_sql.open_cursor;
273 dbms_sql.parse( select_sql2_c, select_sql2, dbms_sql.v7 );
274
275 EXCEPTION
276 WHEN OTHERS THEN
277 arp_standard.debug( 'EXCEPTION: Error parsing select_sql2 stmt' );
278 RAISE;
279 END;
280
281 arp_standard.debug( 'Completed parsing select stmts' );
282
283 arp_standard.debug( 'define_columns for select_sql1_c');
284 dbms_sql.define_column( select_sql1_c, 1, select_rec1.language, 4);
285
286 arp_standard.debug( 'define_columns for select_sql2_c');
287 dbms_sql.define_column( select_sql2_c, 1, select_rec2.language, 4);
288
289 arp_standard.debug( ' Executing select_sql1' );
290 BEGIN
291 l_ignore := dbms_sql.execute( select_sql1_c );
292
293 EXCEPTION
294 WHEN OTHERS THEN
295 arp_standard.debug( 'EXCEPTION: Error executing select_sql1' );
296 RAISE;
297 END;
298
299 arp_standard.debug( ' Executing select_sql2' );
300 BEGIN
301 l_ignore := dbms_sql.execute( select_sql2_c );
302
303 EXCEPTION
304 WHEN OTHERS THEN
305 arp_standard.debug( 'EXCEPTION: Error executing select_sql2' );
306 RAISE;
307 END;
308
309 --------------------------------------------------------------
310 -- Fetch rows
311 --------------------------------------------------------------
312 arp_standard.debug( ' Fetching select_sql1 stmt');
313
314 begin
315 loop
316 if (dbms_sql.fetch_rows( select_sql1_c ) > 0)
317 then
318
319 arp_standard.debug(' fetched a row' );
320 select_rec1 := null_rec;
321 ------------------------------------------------------
322 -- Get column values
323 ------------------------------------------------------
324 dbms_sql.column_value( select_sql1_c, 1, select_rec1.language );
325 arp_standard.debug( 'Language code: ' || select_rec1.language );
326
327 if (lang_str is null) then
328
329 lang_str := select_rec1.language;
330 else
331 lang_str := lang_str || ',' || select_rec1.language;
332 end if;
333
334 else
335 arp_standard.debug('Done fetching select_sql1');
336 EXIT;
337 end if;
338 end loop;
339 end;
340
341 arp_standard.debug( ' Fetching select_sql2 stmt');
342
343 -- concatenate languages from select_sql2_c if it has languages not yet in lang_str
344 begin
345 loop
346 if (dbms_sql.fetch_rows( select_sql2_c ) > 0)
347 then
348
349 arp_standard.debug(' fetched a row' );
350 select_rec2 := null_rec;
351 ------------------------------------------------------
352 -- Get column values
353 ------------------------------------------------------
354 dbms_sql.column_value( select_sql2_c, 1, select_rec2.language );
355 arp_standard.debug( 'Language code: ' || select_rec2.language );
356
357 if (lang_str is null) then
358
359 lang_str := select_rec2.language;
360 else
361 if instr(lang_str,select_rec2.language) = 0 then
362 lang_str := lang_str || ',' || select_rec2.language;
363 end if;
364 end if;
365
366 else
367 arp_standard.debug('Done fetching select_sql2');
368 EXIT;
369 end if;
370 end loop;
371 end;
372
373 return lang_str;
374
375 end ARP_RAXINV_MLS_FUNCTION;
376
377 end ARP_RAXINV_MLS_PACKAGE;