1 PACKAGE BODY AP_REPORTS_MLS_LANG AS
2 /* $Header: apxlangb.pls 120.4 2004/12/02 02:31:38 pjena noship $ */
3
4
5
6 ---------------------------------------------------------------
7 -- MLS function for "Invalid PO Supplier Notice" report
8 ---------------------------------------------------------------
9 FUNCTION APXVDLET RETURN VARCHAR2 IS
10
11 lang_str VARCHAR2(500) := NULL;
12 -- Bug 2633773 , to remove cartesian join in cursor languages_cursor
13 l_base FND_LANGUAGES.LANGUAGE_CODE%TYPE;
14
15 CURSOR languages_cursor is
16 SELECT DISTINCT(NVL(lang.language_code, l_base)) language_code
17 FROM po_vendor_sites pvs,
18 ap_invoices inv,
19 po_vendors pv,
20 ap_holds h,
21 fnd_languages lang
22 WHERE inv.vendor_site_id = pvs.vendor_site_id
23 AND inv.payment_status_flag in ('N', 'P')
24 AND pv.vendor_id = pvs.vendor_id
25 AND h.invoice_id = inv.invoice_id
26 AND h.hold_lookup_code = 'INVALID PO'
27 AND h.release_lookup_code is null
28 AND lang.nls_language (+) = pvs.language;
29
30 BEGIN
31
32 -- Bug 2633773 , select base language code into local variable
33 select language_code
34 INTO l_base
35 from fnd_languages
36 where installed_flag = 'B';
37
38
39 FOR languages IN languages_cursor LOOP
40
41 IF (lang_str IS NULL) THEN
42 lang_str := languages.language_code;
43 ELSE
44 lang_str := lang_str || ',' || languages.language_code;
45 END IF;
46
47 END LOOP;
48
49 RETURN (lang_str);
50
51 END APXVDLET;
52
53 -------------------------------------------------------------
54 -- MLS function for "Prepayment Remittance Report" report
55 -------------------------------------------------------------
56 FUNCTION APXPPREM RETURN VARCHAR2 IS
57
58 p_vendor_id NUMBER := NULL;
59 p_invoice_id NUMBER := NULL;
60 p_prepay_id NUMBER := NULL;
61 p_start_date DATE := NULL;
62 p_end_date DATE := NULL;
63
64 cursor_id INTEGER;
65 selectstmt VARCHAR2(1500);
66 lang_str VARCHAR2(500) := NULL;
67 l_language VARCHAR2(4);
68 dummy INTEGER;
69
70 BEGIN
71
72 p_vendor_id := to_number(fnd_request_info.get_parameter(1));
73 p_invoice_id := to_number(fnd_request_info.get_parameter(2));
74 p_prepay_id := to_number(fnd_request_info.get_parameter(3));
75 p_start_date := fnd_date.canonical_to_date(fnd_request_info.get_parameter(4));
76 p_end_date := fnd_date.canonical_to_date(fnd_request_info.get_parameter(5));
77
78 -- Create a query string to get languages based on the parameters
79 selectstmt := 'SELECT DISTINCT(NVL(lang.language_code, base.language_code))'||
80 ' FROM po_vendors pvd,'||
81 ' po_vendor_sites pvs,'||
82 ' ap_invoices ai,'||
83 ' ap_invoices pp,'||
84 ' ap_invoice_prepays aipp,'||
85 ' fnd_languages base,'||
86 ' fnd_languages lang'||
87 ' WHERE aipp.invoice_id = ai.invoice_id'||
88 ' AND aipp.prepay_id = pp.invoice_id'||
89 ' AND ai.vendor_id = pp.vendor_id'||
90 ' AND ai.vendor_id = pvd.vendor_id'||
91 ' AND pvd.vendor_id = pvs.vendor_id'||
92 ' AND pvs.vendor_site_id = ai.vendor_site_id'||
93 ' AND base.installed_flag = ''B'' '||
94 ' AND lang.nls_language (+) = pvs.language';
95
96 -- add to where clause if other parameters are specified
97 IF p_vendor_id IS NOT NULL THEN
98 selectstmt := selectstmt||' AND ai.vendor_id = :p_vendor_id';
99 END IF;
100
101 IF p_invoice_id IS NOT NULL THEN
102 selectstmt := selectstmt||' AND ai.invoice_id = :p_invoice_id';
103 END IF;
104
105 IF p_prepay_id IS NOT NULL THEN
106 selectstmt := selectstmt||' AND pp.invoice_id = :p_prepay_id';
107 END IF;
108
109 IF p_start_date IS NOT NULL THEN
110 selectstmt := selectstmt||' AND aipp.last_update_date >= :p_start_date';
111 END IF;
112
113 IF p_end_date IS NOT NULL THEN
114 selectstmt := selectstmt||' AND aipp.last_update_date <= :p_end_date';
115 END IF;
116
117 -- Added the following as part of the fix for 890934. Since the ap_invoice_prepays is obsolete
118 -- in Release 11i, we need to use ap_invoice_distributions to get the 'PREPAY' information.
119
120 selectstmt := selectstmt||' UNION SELECT DISTINCT(NVL(lang.language_code, base.language_code))'||
121 ' FROM po_vendors pvd,'||
122 ' po_vendor_sites pvs,'||
123 ' ap_invoices ai,'||
124 ' ap_invoice_distributions aid,'||
125 ' ap_invoice_distributions aid2,'|| --3984580
126 ' fnd_languages base,'||
127 ' fnd_languages lang'||
128 ' WHERE aid.invoice_id = ai.invoice_id'||
129 ' AND aid.line_type_lookup_code = ''PREPAY'' '||
130 ' AND ai.vendor_id = pvd.vendor_id'||
131 ' AND pvd.vendor_id = pvs.vendor_id'||
132 ' AND pvs.vendor_site_id = ai.vendor_site_id'||
133 ' AND base.installed_flag = ''B'' '||
134 ' AND nvl(aid.reversal_flag,''N'') != ''Y'' '||
135 ' AND lang.nls_language (+) = pvs.language'||
136 ' AND aid2.invoice_distribution_id = aid.prepay_distribution_id'||--3984580
137 ' AND aid2.line_type_lookup_code = ''ITEM'' '; --3984580
138
139 -- add to where clause if other parameters are specified
140 IF p_vendor_id IS NOT NULL THEN
141 selectstmt := selectstmt||' AND ai.vendor_id = :p_vendor_id';
142 END IF;
143
144 IF p_invoice_id IS NOT NULL THEN
145 selectstmt := selectstmt||' AND ai.invoice_id = :p_invoice_id';
146 END IF;
147
148 IF p_prepay_id IS NOT NULL THEN
149 selectstmt := selectstmt||' AND aid2.invoice_id = :p_prepay_id'; --3984580
150 END IF;
151
152 --1901963, changed it to inv.invoice_date in the 2 statements below
153 --previously it was aipp.last_update_date, which doesn't make any sense
154 --for the second query in the union
155 IF p_start_date IS NOT NULL THEN
156 selectstmt := selectstmt||' AND ai.invoice_date >= :p_start_date';
157 END IF;
158
159 IF p_end_date IS NOT NULL THEN
160 selectstmt := selectstmt||' AND ai.last_update_date <= :p_end_date';
161 END IF;
162
163 -- Open the cursor for processing
164 cursor_id := dbms_sql.open_cursor;
165
166 -- Parse the query
167 dbms_sql.parse(cursor_id, selectstmt, dbms_sql.v7);
168
169 -- Bind input variables
170 IF p_vendor_id IS NOT NULL THEN
171 dbms_sql.bind_variable(cursor_id,':p_vendor_id',p_vendor_id);
172 END IF;
173
174 IF p_invoice_id IS NOT NULL THEN
175 dbms_sql.bind_variable(cursor_id,':p_invoice_id',p_invoice_id);
176 END IF;
177
178 IF p_prepay_id IS NOT NULL THEN
179 dbms_sql.bind_variable(cursor_id,':p_prepay_id',p_prepay_id);
180 END IF;
181
182 IF p_start_date IS NOT NULL THEN
183 dbms_sql.bind_variable(cursor_id,':p_start_date',p_start_date);
184 END IF;
185
186 IF p_end_date IS NOT NULL THEN
187 dbms_sql.bind_variable(cursor_id,':p_end_date',p_end_date);
188 END IF;
189
190 -- Define the output variable
191 dbms_sql.define_column(cursor_id,1,l_language,4);
192
193 -- Execute the query
194 dummy := dbms_sql.execute(cursor_id);
195
196 -- Create string of languages to be returned
197 LOOP
198
199 IF dbms_sql.fetch_rows(cursor_id) = 0 THEN
200 EXIT;
201 END IF;
202 dbms_sql.column_value( cursor_id, 1, l_language );
203
204 IF (lang_str IS NULL) THEN
205 lang_str := l_language;
206 ELSE
207 lang_str := lang_str||','||l_language;
208 END IF;
209 END LOOP;
210
211 dbms_sql.close_cursor(cursor_id);
212
213 RETURN(lang_str);
214
215 END APXPPREM;
216
217
218 -------------------------------------------------------------
219 -- MLS function for "Print Invoice Report" report
220 -------------------------------------------------------------
221 FUNCTION APXINPRT RETURN VARCHAR2 IS
222
223 p_vendor_type VARCHAR2(25) := NULL;
224 p_pay_group VARCHAR2(25) := NULL;
225 p_invoice_type VARCHAR2(25) := NULL;
226 p_vendor_id NUMBER := NULL;
227 p_invoice_id NUMBER := NULL;
228 p_start_date DATE := NULL;
229 p_end_date DATE := NULL;
230
231 cursor_id INTEGER;
232 selectstmt VARCHAR2(1500);
233 lang_str VARCHAR2(500) := NULL;
234 l_language VARCHAR2(4);
235 dummy INTEGER;
236
237 BEGIN
238
239 p_vendor_type := fnd_request_info.get_parameter(1);
240 p_vendor_id := to_number(fnd_request_info.get_parameter(2));
241 p_pay_group := fnd_request_info.get_parameter(3);
242 p_invoice_type := fnd_request_info.get_parameter(4);
243 p_invoice_id := to_number(fnd_request_info.get_parameter(5));
244 p_start_date := fnd_date.canonical_to_date(fnd_request_info.get_parameter(6));
245 p_end_date := fnd_date.canonical_to_date(fnd_request_info.get_parameter(7));
246
247 -- Create a query string to get languages based on the parameters
248 selectstmt := 'SELECT DISTINCT(NVL(lang.language_code, base.language_code))'||
249 ' FROM po_vendors pvd,' ||
250 ' po_vendor_sites pvs,' ||
251 ' ap_invoices ai,' ||
252 ' fnd_languages base,' ||
253 ' fnd_languages lang' ||
254 ' WHERE ai.vendor_id = pvd.vendor_id' ||
255 ' AND pvd.vendor_id = pvs.vendor_id' ||
256 ' AND ai.vendor_site_id = pvs.vendor_site_id' ||
257 ' AND base.installed_flag = ''B'' ' ||
258 ' AND lang.nls_language (+) = pvs.language';
259
260 -- add to where clause if other parameters are specified
261 IF p_vendor_type IS NOT NULL THEN
262 selectstmt := selectstmt||' AND pvd.vendor_type_lookup_code = :p_vendor_type';
263 END IF;
264 IF p_pay_group IS NOT NULL THEN
265 selectstmt := selectstmt||' AND ai.pay_group_lookup_code = :p_pay_group';
266 END IF;
267
268 IF p_vendor_id IS NOT NULL THEN
269 selectstmt := selectstmt||' AND ai.vendor_id = :p_vendor_id';
270 END IF;
271
272 IF p_invoice_type IS NOT NULL THEN
273 selectstmt := selectstmt||' AND ai.invoice_type_lookup_code = :p_invoice_type';
274 END IF;
275
276 IF p_invoice_id IS NOT NULL THEN
277 selectstmt := selectstmt||' AND ai.invoice_id = :p_invoice_id';
278 END IF;
279
280 IF p_start_date IS NOT NULL THEN
281 selectstmt := selectstmt||' AND ai.invoice_date >= :p_start_date';
282 END IF;
283
284 IF p_end_date IS NOT NULL THEN
285 selectstmt := selectstmt||' AND ai.invoice_date <= :p_end_date';
286 END IF;
287
288 -- Open the cursor for processing
289 cursor_id := dbms_sql.open_cursor;
290
291 -- Parse the query
292 dbms_sql.parse(cursor_id, selectstmt, dbms_sql.v7);
293
294 -- Bind input variables
295 IF p_vendor_type IS NOT NULL THEN
296 dbms_sql.bind_variable(cursor_id,':p_vendor_type',p_vendor_type);
297 END IF;
298
299 IF p_pay_group IS NOT NULL THEN
300 dbms_sql.bind_variable(cursor_id,':p_pay_group',p_pay_group);
301 END IF;
302
303 IF p_vendor_id IS NOT NULL THEN
304 dbms_sql.bind_variable(cursor_id,':p_vendor_id',p_vendor_id);
305 END IF;
306
307 IF p_invoice_type IS NOT NULL THEN
308 dbms_sql.bind_variable(cursor_id,':p_invoice_type',p_invoice_type);
309 END IF;
310
311 IF p_invoice_id IS NOT NULL THEN
312 dbms_sql.bind_variable(cursor_id,':p_invoice_id',p_invoice_id);
313 END IF;
314
315 IF p_start_date IS NOT NULL THEN
316 dbms_sql.bind_variable(cursor_id,':p_start_date',p_start_date);
317 END IF;
318
319 IF p_end_date IS NOT NULL THEN
320 dbms_sql.bind_variable(cursor_id,':p_end_date',p_end_date);
321 END IF;
322
323 -- Define the output variable
324 dbms_sql.define_column(cursor_id,1,l_language,4);
325
326 -- Execute the query
327 dummy := dbms_sql.execute(cursor_id);
328
329 -- Create string of languages to be returned
330 LOOP
331
332 IF dbms_sql.fetch_rows(cursor_id) = 0 THEN
333 EXIT;
334 END IF;
335 dbms_sql.column_value( cursor_id, 1, l_language );
336
337 IF (lang_str IS NULL) THEN
338 lang_str := l_language;
339 ELSE
340 lang_str := lang_str||','||l_language;
341 END IF;
342 END LOOP;
343
344 dbms_sql.close_cursor(cursor_id);
345
346 RETURN(lang_str);
347
348 END APXINPRT;
349 ---------------------------------------------------------------
350 --MLS function for "Supplier Open Balance Report"
351 -------------------------------------------------------------
352 FUNCTION APXSOBLX RETURN VARCHAR2 IS
353
354 P_vendor_name_from po_vendors.vendor_name%TYPE;
355 p_vendor_name_to po_vendors.vendor_name%TYPE;
356 cursor_id INTEGER;
357 selectstmt VARCHAR2(1500);
358 lang_str VARCHAR2(500) := NULL;
359 l_language VARCHAR2(4);
360 dummy INTEGER;
361 retval INTEGER;
362 parm_number NUMBER;
363 BEGIN
364 retval := FND_REQUEST_INFO.GET_PARAM_NUMBER('Supplier Name From',parm_number);
365 if retval = -1 then
366 P_VENDOR_NAME_FROM := NULL;
367 else
368 P_VENDOR_NAME_FROM := FND_REQUEST_INFO.GET_PARAMETER(parm_number);
369 end if;
370 retval := FND_REQUEST_INFO.GET_PARAM_NUMBER('Supplier Name To',parm_number);
371 if retval = -1 then
372 P_VENDOR_NAME_TO := NULL;
373 else
374 P_VENDOR_NAME_TO := FND_REQUEST_INFO.GET_PARAMETER(parm_number);
375 end if;
376
377 -- Create a query string to get languages based on the parameters
378 selectstmt := 'SELECT DISTINCT(NVL(lang.language_code, base.language_code))'||
379 ' FROM po_vendors pvd,' ||
380 ' po_vendor_sites pvs,' ||
381 ' ap_invoices ai,' ||
382 ' fnd_languages base,' ||
383 ' fnd_languages lang' ||
384 ' WHERE ai.vendor_id = pvd.vendor_id' ||
385 ' AND pvd.vendor_id = pvs.vendor_id' ||
386 ' AND ai.vendor_site_id = pvs.vendor_site_id' ||
387 ' AND base.installed_flag = ''B'' ' ||
388 ' AND lang.nls_language (+) = pvs.language' ||
389 ' AND pvd.vendor_name between nvl(:p_vendor_name_from,''A'') and nvl(:p_vendor_name_to,''Z'')';
390
391 -- add to where clause if other parameters are specified
392 -- Open the cursor for processing
393 cursor_id := dbms_sql.open_cursor;
394
395 -- Parse the query
396 dbms_sql.parse(cursor_id, selectstmt, dbms_sql.v7);
397 DBMS_SQL.BIND_VARIABLE(cursor_id,':p_vendor_name_from',P_VENDOR_NAME_FROM);
398 DBMS_SQL.BIND_VARIABLE(cursor_id,':p_vendor_name_to',P_VENDOR_NAME_TO);
399 -- Bind input variables
400
401 -- Define the output variable
402 dbms_sql.define_column(cursor_id,1,l_language,4);
403
404 -- Execute the query
405 dummy := dbms_sql.execute(cursor_id);
406
407 -- Create string of languages to be returned
408 LOOP
409
410 IF dbms_sql.fetch_rows(cursor_id) = 0 THEN
411 EXIT;
412 END IF;
413 dbms_sql.column_value( cursor_id, 1, l_language );
414
415 IF (lang_str IS NULL) THEN
416 lang_str := l_language;
417 ELSE
418 lang_str := lang_str||','||l_language;
419 END IF;
420 END LOOP;
421
422 dbms_sql.close_cursor(cursor_id);
423
424 RETURN(lang_str);
425
426 END APXSOBLX;
427 END AP_REPORTS_MLS_LANG;
[Home] [Help]