1 PACKAGE BODY AP_REPORTS_MLS_LANG AS
2 /* $Header: apxlangb.pls 120.5.12020000.2 2012/07/13 20:21:51 mkmeda ship $ */
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 ap_suppliers pvd,' || /*Bug 13630276: Changed table from po_vendors*/
250 /*' po_vendor_sites pvs,' ||*/ /*Bug 13630276:commented table*/
251 ' hz_party_sites zps,' || /*Bug 13630276: Added table*/
252 ' hz_parties zp,' || /*Bug 13630276: Added table*/
253 ' hz_locations zl,' || /*Bug 13630276: Added table*/
254 ' ap_invoices ai,' ||
255 ' fnd_languages base,' ||
256 ' fnd_languages lang' ||
257 ' WHERE ai.vendor_id = pvd.vendor_id(+)' || /*Bug 13630276: added outer join*/
258 /*' AND pvd.vendor_id = pvs.vendor_id' ||
259 ' AND ai.vendor_site_id = pvs.vendor_site_id' ||*/ /*Bug 13630276: commented the old joins*/
260 ' AND zps.party_site_id = ai.party_site_id' || /*Bug 13630276: added join*/
261 ' AND zp.party_id = ai.party_id' || /*Bug 13630276: added join*/
262 ' AND zl.location_id = zps.location_id' || /*Bug 13630276: added join*/
263 ' AND base.installed_flag = ''B'' ' ||
264 ' AND lang.language_code (+) = zl.language'; /*Bug 13630276: changed the aliasing and fnd language code*/
265
266 -- add to where clause if other parameters are specified
267 IF p_vendor_type IS NOT NULL THEN
268 selectstmt := selectstmt||' AND pvd.vendor_type_lookup_code = :p_vendor_type';
269 END IF;
270 IF p_pay_group IS NOT NULL THEN
271 selectstmt := selectstmt||' AND ai.pay_group_lookup_code = :p_pay_group';
272 END IF;
273
274 IF p_vendor_id IS NOT NULL THEN
275 selectstmt := selectstmt||' AND ai.vendor_id = :p_vendor_id';
276 END IF;
277
278 IF p_invoice_type IS NOT NULL THEN
279 selectstmt := selectstmt||' AND ai.invoice_type_lookup_code = :p_invoice_type';
280 END IF;
281
282 IF p_invoice_id IS NOT NULL THEN
283 selectstmt := selectstmt||' AND ai.invoice_id = :p_invoice_id';
284 END IF;
285
286 IF p_start_date IS NOT NULL THEN
287 selectstmt := selectstmt||' AND ai.invoice_date >= :p_start_date';
288 END IF;
289
290 IF p_end_date IS NOT NULL THEN
291 selectstmt := selectstmt||' AND ai.invoice_date <= :p_end_date';
292 END IF;
293
294 -- Open the cursor for processing
295 cursor_id := dbms_sql.open_cursor;
296
297 -- Parse the query
298 dbms_sql.parse(cursor_id, selectstmt, dbms_sql.v7);
299
300 -- Bind input variables
301 IF p_vendor_type IS NOT NULL THEN
302 dbms_sql.bind_variable(cursor_id,':p_vendor_type',p_vendor_type);
303 END IF;
304
305 IF p_pay_group IS NOT NULL THEN
306 dbms_sql.bind_variable(cursor_id,':p_pay_group',p_pay_group);
307 END IF;
308
309 IF p_vendor_id IS NOT NULL THEN
310 dbms_sql.bind_variable(cursor_id,':p_vendor_id',p_vendor_id);
311 END IF;
312
313 IF p_invoice_type IS NOT NULL THEN
314 dbms_sql.bind_variable(cursor_id,':p_invoice_type',p_invoice_type);
315 END IF;
316
317 IF p_invoice_id IS NOT NULL THEN
318 dbms_sql.bind_variable(cursor_id,':p_invoice_id',p_invoice_id);
319 END IF;
320
321 IF p_start_date IS NOT NULL THEN
322 dbms_sql.bind_variable(cursor_id,':p_start_date',p_start_date);
323 END IF;
324
325 IF p_end_date IS NOT NULL THEN
326 dbms_sql.bind_variable(cursor_id,':p_end_date',p_end_date);
327 END IF;
328
329 -- Define the output variable
330 dbms_sql.define_column(cursor_id,1,l_language,4);
331
332 -- Execute the query
333 dummy := dbms_sql.execute(cursor_id);
334
335 -- Create string of languages to be returned
336 LOOP
337
338 IF dbms_sql.fetch_rows(cursor_id) = 0 THEN
339 EXIT;
340 END IF;
341 dbms_sql.column_value( cursor_id, 1, l_language );
342
343 IF (lang_str IS NULL) THEN
344 lang_str := l_language;
345 ELSE
346 lang_str := lang_str||','||l_language;
347 END IF;
348 END LOOP;
349
350 dbms_sql.close_cursor(cursor_id);
351
352 RETURN(lang_str);
353
354 END APXINPRT;
355 ---------------------------------------------------------------
356 --MLS function for "Supplier Open Balance Report"
357 -------------------------------------------------------------
358 FUNCTION APXSOBLX RETURN VARCHAR2 IS
359
360 P_vendor_name_from po_vendors.vendor_name%TYPE;
361 p_vendor_name_to po_vendors.vendor_name%TYPE;
362 cursor_id INTEGER;
363 selectstmt VARCHAR2(1500);
364 lang_str VARCHAR2(500) := NULL;
365 l_language VARCHAR2(4);
366 dummy INTEGER;
367 retval INTEGER;
368 parm_number NUMBER;
369 BEGIN
370 retval := FND_REQUEST_INFO.GET_PARAM_NUMBER('Supplier Name From',parm_number);
371 if retval = -1 then
372 P_VENDOR_NAME_FROM := NULL;
373 else
374 P_VENDOR_NAME_FROM := FND_REQUEST_INFO.GET_PARAMETER(parm_number);
375 end if;
376 retval := FND_REQUEST_INFO.GET_PARAM_NUMBER('Supplier Name To',parm_number);
377 if retval = -1 then
378 P_VENDOR_NAME_TO := NULL;
379 else
380 P_VENDOR_NAME_TO := FND_REQUEST_INFO.GET_PARAMETER(parm_number);
381 end if;
382
383 -- Create a query string to get languages based on the parameters
384 selectstmt := 'SELECT DISTINCT(NVL(lang.language_code, base.language_code))'||
385 ' FROM po_vendors pvd,' ||
386 ' po_vendor_sites pvs,' ||
387 ' ap_invoices ai,' ||
388 ' fnd_languages base,' ||
389 ' fnd_languages lang' ||
390 ' WHERE ai.vendor_id = pvd.vendor_id' ||
391 ' AND pvd.vendor_id = pvs.vendor_id' ||
392 ' AND ai.vendor_site_id = pvs.vendor_site_id' ||
393 ' AND base.installed_flag = ''B'' ' ||
394 ' AND lang.nls_language (+) = pvs.language' ||
395 --bug 12927165
396 --' AND pvd.vendor_name between nvl(:p_vendor_name_from,''A'') and nvl(:p_vendor_name_to,''Z'')';
397 ' AND pvd.vendor_name between nvl(:p_vendor_name_from,pvd.vendor_name)
398 and nvl(:p_vendor_name_to,pvd.vendor_name)';
399
400 -- add to where clause if other parameters are specified
401 -- Open the cursor for processing
402 cursor_id := dbms_sql.open_cursor;
403
404 -- Parse the query
405 dbms_sql.parse(cursor_id, selectstmt, dbms_sql.v7);
406 DBMS_SQL.BIND_VARIABLE(cursor_id,':p_vendor_name_from',P_VENDOR_NAME_FROM);
407 DBMS_SQL.BIND_VARIABLE(cursor_id,':p_vendor_name_to',P_VENDOR_NAME_TO);
408 -- Bind input variables
409
410 -- Define the output variable
411 dbms_sql.define_column(cursor_id,1,l_language,4);
412
413 -- Execute the query
414 dummy := dbms_sql.execute(cursor_id);
415
416 -- Create string of languages to be returned
417 LOOP
418
419 IF dbms_sql.fetch_rows(cursor_id) = 0 THEN
420 EXIT;
421 END IF;
422 dbms_sql.column_value( cursor_id, 1, l_language );
423
424 IF (lang_str IS NULL) THEN
425 lang_str := l_language;
426 ELSE
427 lang_str := lang_str||','||l_language;
428 END IF;
429 END LOOP;
430
431 dbms_sql.close_cursor(cursor_id);
432
433 RETURN(lang_str);
434
435 END APXSOBLX;
436 END AP_REPORTS_MLS_LANG;
[Home] [Help]