1 PACKAGE BODY WSH_REPORT_PRINTERS_PVT AS
2 /* $Header: WSHRPRNB.pls 120.2.12010000.2 2008/08/22 09:15:36 sankarun ship $ */
3
4
5 -- Constant
6 G_PKG_NAME CONSTANT VARCHAR2(30) := 'WSH_REPORT_PRINTERS_PVT';
7
8
9 -- FORWARD DECLARATIONS
10 -- Removed p_label_id parameter for Bug 2996792.
11 PROCEDURE Select_Printer( p_concurrent_program_id IN NUMBER,
12 p_organization_id IN NUMBER,
13 p_level_type_id IN NUMBER,
14 p_level_value_id IN VARCHAR2,
15 p_equipment_instance IN VARCHAR2,
16 x_printer OUT NOCOPY VARCHAR2);
17
18 --
19 -- Name
20 -- PROCEDURE Get_Printer
21 --
22 -- Purpose
23 -- Depending upon the set of parameters passed, this procedure tries to
24 -- get the most appropriate printer.
25 --
26 -- Input Parameters
27 -- p_concurrent_program_id => document id
28 -- p_organization_id => Organization Id
29 -- p_equipment_type => Equipment Type (currently items which have the equipment attribute turned on)
30 -- p_equipment_instance => An instance of Equipment Type. Serial Number of the equipment.
31 -- p_label_id => Label Id
32 -- p_user_id => user_id
33 -- p_zone => Warehouse zone (currently subinventory)
34 -- p_department => Department (currently BOM departments)
35 -- p_responsibility => Responsibility_Id
36 -- p_application_id => Application_Id
37 -- p_site => Site_Id -- This is really not necessary.
38 -- We will not remove it for dependency reasons
39
40 -- Output Parameters
41 -- x_printer => Printer Name
42 -- x_api_status => FND_API.G_RET_STS_SUCESSS or
43 -- FND_API.G_RET_STS_ERROR or
44 -- FND_API.G_RET_STS_UNEXP_ERROR
45 -- x_error_message => Error message
46 --
47
48 PROCEDURE Get_Printer (
49 p_concurrent_program_id IN NUMBER,
50 p_organization_id IN NUMBER default NULL,
51 p_equipment_type_id IN NUMBER default NULL,
52 p_equipment_instance IN VARCHAR2 default NULL,
53 p_label_id IN NUMBER default NULL,
54 p_user_id IN NUMBER default NULL,
55 p_zone IN VARCHAR2 default NULL,
56 p_department_id IN NUMBER default NULL,
57 p_responsibility_id IN NUMBER default NULL,
58 p_application_id IN NUMBER default NULL,
59 p_site_id IN NUMBER default NULL, /* this parameter is really not necessary */
60 p_format_id IN NUMBER default NULL,
61 x_printer OUT NOCOPY VARCHAR2,
62 x_api_status OUT NOCOPY VARCHAR2,
63 x_error_message OUT NOCOPY VARCHAR2
64 ) IS
65 counter number := 0;
66 null_program_id EXCEPTION;
67 null_levels EXCEPTION;
68 null_equipment_type EXCEPTION;
69 null_equipment_instance EXCEPTION;
70 null_organization_id EXCEPTION;
71 printer_not_found EXCEPTION;
72 l_organization_id NUMBER;
73
74 --
75 l_debug_on BOOLEAN;
76 --
77 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'GET_PRINTER';
78 --
79 BEGIN
80
81
82 -- Concurrent Program Id should not null. Check this condition first.
83 --
84 -- Debug Statements
85 --
86 --
87 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
88 --
89 IF l_debug_on IS NULL
90 THEN
91 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
92 END IF;
93 --
94 IF l_debug_on THEN
95 WSH_DEBUG_SV.push(l_module_name);
96 --
97 WSH_DEBUG_SV.log(l_module_name,'P_CONCURRENT_PROGRAM_ID',P_CONCURRENT_PROGRAM_ID);
98 WSH_DEBUG_SV.log(l_module_name,'P_ORGANIZATION_ID',P_ORGANIZATION_ID);
99 WSH_DEBUG_SV.log(l_module_name,'P_EQUIPMENT_TYPE_ID',P_EQUIPMENT_TYPE_ID);
100 WSH_DEBUG_SV.log(l_module_name,'P_EQUIPMENT_INSTANCE',P_EQUIPMENT_INSTANCE);
101 WSH_DEBUG_SV.log(l_module_name,'P_USER_ID',P_USER_ID);
102 WSH_DEBUG_SV.log(l_module_name,'P_ZONE',P_ZONE);
103 WSH_DEBUG_SV.log(l_module_name,'P_DEPARTMENT_ID',P_DEPARTMENT_ID);
104 WSH_DEBUG_SV.log(l_module_name,'P_RESPONSIBILITY_ID',P_RESPONSIBILITY_ID);
105 WSH_DEBUG_SV.log(l_module_name,'P_APPLICATION_ID',P_APPLICATION_ID);
106 WSH_DEBUG_SV.log(l_module_name,'P_SITE_ID',P_SITE_ID);
107 END IF;
108 --
109 if (p_concurrent_program_id is null )
110 then
111 RAISE null_program_id;
112 end if;
113
114
115 -- Now, check the Level parameters. If all are null, throw error.
116 -- Removed P_Label_Id from IF condition for Bug 2996792.
117 if (p_equipment_type_id is null AND
118 p_equipment_instance is null AND
119 p_user_id is null AND
120 p_zone is null AND
121 p_department_id is null AND
122 p_responsibility_id is null AND
123 p_application_id is null AND
124 p_format_id is null )
125 then
126 RAISE null_levels;
127 end if;
128
129 -- Check for equipment type and equipment instance
130 if (p_equipment_type_id is null and p_equipment_instance is null) then
131 null; -- no problem..
132 elsif (p_equipment_type_id is not null and p_equipment_instance is null) then
133 RAISE null_equipment_instance;
134 elsif (p_equipment_type_id is null and p_equipment_instance is not null) then
135 RAISE null_equipment_type;
136 end if;
137
138 -- For equipment type and zone, organization_id is a must.
139 if (p_equipment_type_id is not null or p_zone is not null) and
140 (p_organization_id is null)
141 then
142 RAISE null_organization_id;
143 end if;
144
145 /* sbhaskar 08/21/00 */
146 -- Organiation_Id is not necessary to look at if equipment id and zone are not passed.
147 -- We have to do this way since p_organization_id is IN variable and cannot be changed.
148 -- if (p_organization_id is not null and (p_equipment_type_id is null AND p_zone is null) ) then
149 -- l_organization_id := null;
150 -- else
151 -- l_organization_id := p_organization_id;
152 -- end if;
153
154 l_organization_id := p_organization_id; -- Bug 3534965(3510460 Frontport)
155
156 -- Now, Populate pl/sql table so that we can do the SELECT in a loop.
157 /* Bug 7341536 --Increased the Iteration of the for loop from 8 to 9 as there* are 9 Level
158 -- This is regression due to fix done in RFID project done in R12 in
159 version 115.13 (No Bug no specified) */
160 for i in 1..9 -- hardcoded 8 since we have 8 levels. 8th value added for Bug 3534965(3510460 Frontport)
161 loop
162
163 level_table(i).priority_seq := i; -- priority seq is not used. Populating it anyway..
164
165 if (i=1) then
166 level_table(i).level_type_id := 10007; -- Equipment Type
167 level_table(i).level_value_id := p_equipment_type_id;
168 elsif (i=2) then
169 level_table(i).level_type_id := 10009; -- Format
170 level_table(i).level_value_id := p_format_id;
171 elsif (i=3) then
172 level_table(i).level_type_id := 10004; -- User
173 level_table(i).level_value_id := p_user_id;
174 elsif (i=4) then
175 level_table(i).level_type_id := 10006; -- Zone
176 level_table(i).level_value_id := p_zone;
177 elsif (i=5) then
178 level_table(i).level_type_id := 10005; -- Department
179 level_table(i).level_value_id := p_department_id;
180 elsif (i=6) then
181 level_table(i).level_type_id := 10003; -- Responsibility
182 level_table(i).level_value_id := p_responsibility_id;
183 elsif (i=7) then
184 level_table(i).level_type_id := 10002; -- Application
185 level_table(i).level_value_id := p_application_id;
186 elsif (i=8) then
187 level_table(i).level_type_id := 10001; -- Site
188 level_table(i).level_value_id := 0; -- Since this will always be 0 for site.
189 elsif (i=9) then
190 level_table(i).level_type_id := 10008; -- Organization_id for Bug 3534965(3510460 Frontport)
191 level_table(i).level_value_id := p_organization_id ;
192 end if;
193
194
195 end loop;
196
197
198 /* Check for default printers using the following precedence :
199
200 Equipment Instance
201 User
202 Zone
203 Department
204 Responsibility
205 Application
206 Site
207
208 */
209
210
211 -- Call Select_Printer in a loop starting with equipment instance.
212 -- If you find a printer, just return. Otherwise, continue with the next
213 -- level and so on until you find an appropriate printer.
214
215
216 for i in 1..level_table.count
217 loop
218 if ( level_table(i).level_value_id is not null ) then
219 -- Pass l_organization_id instead of p_organization_id since it gets manipulated above.
220 -- Removed Label_Id parameter for Bug 2996792.
221 Select_Printer( p_concurrent_program_id, l_organization_id, level_table(i).level_type_id,
222 level_table(i).level_value_id, p_equipment_instance, x_printer);
223 if x_printer is not null then -- was able to get a printer
224 x_api_status := FND_API.G_RET_STS_SUCCESS;
225 x_error_message := null;
226 --
227 -- Debug Statements
228 --
229 IF l_debug_on THEN
230 WSH_DEBUG_SV.pop(l_module_name);
231 END IF;
232 --
233 RETURN;
234 end if;
235 end if;
236 end loop;
237
238
239 /** sbhaskar 09/13/00 */
240 -- If it falls here, it will mean that we were not able to fetch the default printer.
241 -- Could be because label_id or equipment_instance did not match with the levels.
242 -- If that is the case, we will just check at the site level.
243
244 -- Warning : We could end up having multiple records if label_id condition is not included.
245 -- Therefore, we will include the condition of "label_id is null" in the following select.
246 -- Similarly, with equipment_instance.
247 -- For safety, include rownum=1.
248
249 --Bug fix 2726195 replaced table name with view wsh_report_printers_v
250
251 if (p_equipment_instance is not null ) then
252 begin
253 -- Removed Label_Id from Query for Bug 2996792.
254 select printer_name
255 into x_printer
256 from wsh_report_printers_v
257 where nvl(default_printer_flag,'N') = 'Y'
258 and enabled_flag = 'Y'
259 and concurrent_program_id = p_concurrent_program_id
260 and level_type_id = 10001 -- site level
261 and level_value_id = 0 -- site value
262 and (equipment_instance is null and p_equipment_instance is not null)
263 and rownum = 1; -- make sure we get only 1 row.
264
265 if x_printer is not null then -- was able to get a printer
266 x_api_status := FND_API.G_RET_STS_SUCCESS;
267 x_error_message := null;
268 --
269 -- Debug Statements
270 --
271 IF l_debug_on THEN
272 WSH_DEBUG_SV.pop(l_module_name);
273 END IF;
274 --
275 RETURN;
276 end if;
277 exception
278 when no_data_found then
279 null; -- if the above query returns no data found, just do nothing.
280 end;
281 end if;
282
283 -- If it falls here, it will mean that we were not able to fetch the default printer.
284 -- Set the error flags.
285
286 RAISE printer_not_found;
287
288 --
289 -- Debug Statements
290 --
291 IF l_debug_on THEN
292 WSH_DEBUG_SV.pop(l_module_name);
293 END IF;
294 --
295 RETURN;
296
297 --
298 -- Debug Statements
299 --
300 IF l_debug_on THEN
301 WSH_DEBUG_SV.pop(l_module_name);
302 END IF;
303 --
304 EXCEPTION
305 when NULL_PROGRAM_ID then
306 x_api_status := FND_API.G_RET_STS_ERROR;
307 FND_MESSAGE.Set_Name('WSH', 'WSH_DEFPRT_NULL_PROGID');
308 x_error_message := fnd_message.get;
309
310 --
311 -- Debug Statements
312 --
313 IF l_debug_on THEN
314 WSH_DEBUG_SV.logmsg(l_module_name,'NULL_PROGRAM_ID exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
315 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:NULL_PROGRAM_ID');
316 END IF;
317 --
318 when NULL_LEVELS then
319 x_api_status := FND_API.G_RET_STS_ERROR;
320 FND_MESSAGE.Set_Name('WSH', 'WSH_DEFPRT_NULL_LEVEL');
321 x_error_message := fnd_message.get;
322
323 --
324 -- Debug Statements
325 --
326 IF l_debug_on THEN
327 WSH_DEBUG_SV.logmsg(l_module_name,'NULL_LEVELS exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
328 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:NULL_LEVELS');
329 END IF;
330 --
331 when NULL_EQUIPMENT_INSTANCE then
332 x_api_status := FND_API.G_RET_STS_ERROR;
333 FND_MESSAGE.Set_Name('WSH', 'WSH_DEFPRT_NULL_EQINST');
334 x_error_message := fnd_message.get;
335
336 --
337 -- Debug Statements
338 --
339 IF l_debug_on THEN
340 WSH_DEBUG_SV.logmsg(l_module_name,'NULL_EQUIPMENT_INSTANCE exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
341 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:NULL_EQUIPMENT_INSTANCE');
342 END IF;
343 --
344 when NULL_EQUIPMENT_TYPE then
345 x_api_status := FND_API.G_RET_STS_ERROR;
346 FND_MESSAGE.Set_Name('WSH', 'WSH_DEFPRT_NULL_EQUIP');
347 x_error_message := fnd_message.get;
348
349 --
350 -- Debug Statements
351 --
352 IF l_debug_on THEN
353 WSH_DEBUG_SV.logmsg(l_module_name,'NULL_EQUIPMENT_TYPE exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
354 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:NULL_EQUIPMENT_TYPE');
355 END IF;
356 --
357 when NULL_organization_id then
358 x_api_status := FND_API.G_RET_STS_ERROR;
359 FND_MESSAGE.Set_Name('WSH', 'WSH_DEFPRT_NULL_ORG');
360 x_error_message := fnd_message.get;
361
362 --
363 -- Debug Statements
364 --
365 IF l_debug_on THEN
366 WSH_DEBUG_SV.logmsg(l_module_name,'NULL_ORGANIZATION_ID exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
367 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:NULL_ORGANIZATION_ID');
368 END IF;
369 --
370 when PRINTER_NOT_FOUND then
371 x_api_status := FND_API.G_RET_STS_ERROR;
372 FND_MESSAGE.Set_Name('WSH', 'WSH_DEFPRT_NOTFOUND');
373 x_error_message := fnd_message.get;
374
375 --
376 -- Debug Statements
377 --
378 IF l_debug_on THEN
379 WSH_DEBUG_SV.logmsg(l_module_name,'PRINTER_NOT_FOUND exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
380 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:PRINTER_NOT_FOUND');
381 END IF;
382 --
383 when OTHERS then
384 x_api_status := FND_API.G_RET_STS_UNEXP_ERROR;
385 FND_MESSAGE.Set_Name('WSH', 'WSH_UNEXP_ERROR');
386 FND_MESSAGE.set_token ('PACKAGE',g_pkg_name);
387 FND_MESSAGE.set_token ('ORA_ERROR',to_char(sqlcode));
388 FND_MESSAGE.set_token ('ORA_TEXT','Failure in performing action');
389 x_error_message := fnd_message.get;
390
391 --
392 -- Debug Statements
393 --
394 IF l_debug_on THEN
395 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
396 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
397 END IF;
398 --
399 END Get_Printer;
400
401
402 --
403 -- 07/27/00 : Added label_id and equipment_instance parameters.
404 --
405
406 -- Removed Parameter p_label_id for Bug 2996792.
407 PROCEDURE Select_Printer( p_concurrent_program_id IN NUMBER,
408 p_organization_id IN NUMBER,
409 p_level_type_id IN NUMBER,
410 p_level_value_id IN VARCHAR2,
411 p_equipment_instance IN VARCHAR2,
412 x_printer OUT NOCOPY VARCHAR2) is
413 --
414 --Bug fix 2726195 replaced table name with view wsh_report_printers_v
415 --
416 cursor printer is
417 select printer_name
418 from wsh_report_printers_v
419 where nvl(default_printer_flag,'N') = 'Y'
420 and enabled_flag = 'Y'
421 and concurrent_program_id = p_concurrent_program_id
422 and level_type_id = p_level_type_id
423 and nvl(equipment_instance,'NOTHING') = nvl(p_equipment_instance, 'NOTHING')
424 and decode(level_type_id, 10006, subinventory, level_value_id) = p_level_value_id
425 -- consider organization_id only for Zone or Equipment type only. Bugfix 3980388
426 and decode(level_type_id, 10006, nvl(organization_id,-9),
427 10007, nvl(organization_id,-9),
428 nvl(p_organization_id,-9)) = nvl(p_organization_id,-9);
429
430
431
432 --
433 l_debug_on BOOLEAN;
434 --
435 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'SELECT_PRINTER';
436 --
437 BEGIN
438 --
439 -- Debug Statements
440 --
441 --
442 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
443 --
444 IF l_debug_on IS NULL
445 THEN
446 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
447 END IF;
448 --
449 IF l_debug_on THEN
450 WSH_DEBUG_SV.push(l_module_name);
451 --
452 WSH_DEBUG_SV.log(l_module_name,'P_CONCURRENT_PROGRAM_ID',P_CONCURRENT_PROGRAM_ID);
453 WSH_DEBUG_SV.log(l_module_name,'P_ORGANIZATION_ID',P_ORGANIZATION_ID);
454 WSH_DEBUG_SV.log(l_module_name,'P_LEVEL_TYPE_ID',P_LEVEL_TYPE_ID);
455 WSH_DEBUG_SV.log(l_module_name,'P_LEVEL_VALUE_ID',P_LEVEL_VALUE_ID);
456 WSH_DEBUG_SV.log(l_module_name,'P_EQUIPMENT_INSTANCE',P_EQUIPMENT_INSTANCE);
457 END IF;
458 --
459 open printer;
460 fetch printer into x_printer;
461 close printer;
462 --
463 -- Debug Statements
464 --
465 WSH_DEBUG_SV.log(l_module_name,'Printer Selected : ',x_printer);
466 IF l_debug_on THEN
467 WSH_DEBUG_SV.pop(l_module_name);
468 END IF;
469 --
470 RETURN; -- irrespective of whether cursor failed to fetch anything, just return.
471
472 --
473 -- Debug Statements
474 --
475 IF l_debug_on THEN
476 WSH_DEBUG_SV.pop(l_module_name);
477 END IF;
478 --
479 END Select_Printer;
480
481
482 END WSH_REPORT_PRINTERS_PVT;
[Home] [Help]