4 -- Name
1 PACKAGE BODY WMS_Label_PUB AS
2 /* $Header: WMSLABLB.pls 115.0 2000/07/07 15:47:27 pkm ship $ */
3 /*---------------------------------------------------------------------*/
5 -- PROCEDURE Location_Label
6 /* --------------------------------------------------------------------*/
7 --
8 -- Purpose
9 -- Create XML File for Location label
10 --
11 -- Input Parameters
12 -- p_api_version
13 -- API version number (current version is 1.0)
14 -- p_init_msg_list (optional, default FND_API.G_FALSE)
15 -- Valid values: FND_API.G_FALSE or FND_API.G_TRUE.
16 -- if set to FND_API.G_TRUE
17 -- initialize error message list
18 -- if set to FND_API.G_FALSE - not initialize error
19 -- message list
20 -- p_commit (optional, default FND_API.G_FALSE)
21 -- whether or not to commit the changes to database
22 -- p_organization_id Organization Id - Required Value
23 -- p_Zone_From Zone Range From
24 -- p_Zone_To Zone Range To
25 -- p_location_fr Location Range From
26 -- (only Segment Values are passed)
27 -- p_location_to Location Range To
28 -- (only Segment Values are passed)
29 -- p_no_of_copies No of copies of duplicate labels
30 --
31 --
32 -- Output Parameters
33 -- x_return_status
34 -- if the pick release process succeeds, the value is
35 -- fnd_api.g_ret_sts_success;
36 -- if there is an expected error, the value is
37 -- fnd_api.g_ret_sts_error;
38 -- if there is an unexpected error, the value is
39 -- fnd_api.g_ret_sts_unexp_error;
40 -- x_msg_count
41 -- if there is one or more errors, the number of error messages
42 -- in the buffer
43 -- x_msg_data
44 -- if there is one and only one error, the error message
45 -- (See fnd_api package for more details about the above output parameters)
46
47 PROCEDURE Location_Label
48 ( p_api_version IN NUMBER ,
49 p_init_msg_list IN VARCHAR2 := fnd_api.g_false ,
50 p_commit IN VARCHAR2 := fnd_api.g_false ,
51 x_return_status OUT VARCHAR2 ,
52 x_msg_count OUT NUMBER ,
53 x_msg_data OUT VARCHAR2 ,
54 p_organization_id IN NUMBER ,
55 p_zone_fr IN VARCHAR2 := fnd_api.g_miss_char ,
56 p_zone_to IN VARCHAR2 := fnd_api.g_miss_char ,
57 p_location_fr IN VARCHAR2 := fnd_api.g_miss_char ,
58 p_location_to IN VARCHAR2 := fnd_api.g_miss_char ,
59 p_no_of_copies IN NUMBER :=1
60 ) IS
61 CURSOR loc_cursor IS
62 select inventory_location_id
63 ,concatenated_segments locator
64 ,subinventory_code
65 from mtl_item_locations_kfv
66 where concatenated_segments between
67 decode(p_location_fr,null,concatenated_segments,p_location_fr)
68 and decode(p_location_to,null,concatenated_segments,p_location_to)
69 and subinventory_code between
70 decode(p_zone_fr,null,subinventory_code,p_zone_fr)
71 and decode(p_zone_to,null,subinventory_code,p_zone_to)
72 and organization_id = p_organization_id;
73 l_loc_rec loc_cursor%ROWTYPE;
74 l_rec_count NUMBER :=0;
75 l_printer_header_once VARCHAR2(1):='N';
76 --
77 begin
78 g_dir := Output_file_dir(); -- get this from profile
79 --dbms_output.put_line('directory='||g_dir);
80 g_file := 'text123.xml';
81 g_no_copies := p_no_of_copies;
82 /* wms.rules.label needs to be called here to get the correct label
83 as per the data created for the label request line
84 */
85 --g_label := wms_rules.label();
86 /*User the function Get_printer_name() to get the appropriate printer name
87 as per the data */
88 --g_printer := Get_printer_name();
89 g_label := 'Loation_Label';
90 g_printer := '3op1035ap';
91 WMS_CLABEL.setDefaultLabelInfo(g_label,g_printer,g_no_copies);
92 WMS_CLABEL.openLabelFile(g_dir,g_file);
93 FOR l_loc_rec IN loc_cursor
94 LOOP
95 IF l_printer_header_once = 'Y' then
96 WMS_CLABEL.clearHeader;
97 g_variable_name := 'Zone';
98 g_variable_value := l_loc_rec.subinventory_code;
99 WMS_CLABEL.setHeaderVariable(g_variable_name,g_variable_value);
100 g_variable_name := 'Locator';
101 g_variable_value := l_loc_rec.locator;
102 WMS_CLABEL.setHeaderVariable(g_variable_name,g_variable_value);
103 -- Note: Header is also useful to
104 -- define the static strings like "Address" on the
105 -- label themselves as variables so that language
106 -- translations can be done
107 -- on these labels and hence making it possible to
108 -- use the same label template.
109 END IF;
110 -- FOR l_loc_rec in loc_cursor
111 -- LOOP
112 l_rec_count := l_rec_count+1;
113 -- dbms_output.put_line('first row '||l_loc_rec.locator||l_loc_rec.subinventory_code);
114 WMS_CLABEL.clearLine; -- Clear all the previous line variables
115 g_printer := '3op1035ap';
116 g_label := 'Location_Label';
117 -- WMS_CLABEL.setLabelInfo(g_label,g_printer,g_no_copies); -- do this to override
118 WMS_CLABEL.setLabelInfo(null,null,null); -- do this to override
119 -- Now set all the label variables
120 g_variable_name := 'Zone';
121 g_variable_value := l_loc_rec.subinventory_code;
122 WMS_CLABEL.setLineVariable(g_variable_name,g_variable_value);
123 g_variable_name := 'Locator';
124 g_variable_value := l_loc_rec.locator;
125 WMS_CLABEL.setLineVariable(g_variable_name,g_variable_value);
126 WMS_CLABEL.writeLabel(2); -- This call writes the variables of both the header and the lines.
127 END LOOP;
128 WMS_CLABEL.closeLabelFile;
129 --dbms_output.put_line('Total records in the label '||to_char(l_rec_count));
130 x_return_status := fnd_api.g_ret_sts_success;
131 exception
132 when others then
133 x_return_status := 'E';
134 --dbms_output.put_line('Invalid Path:'||sqlcode||' '||sqlerrm);
135 end Location_Label;
136 /*---------------------------------------------------------------------*/
137 -- Name
138 -- PROCEDURE Item_Label
139 /* --------------------------------------------------------------------*/
140 --
141 -- Purpose
142 -- Create XML File for Item Label
143 --
144 -- Input Parameters
145 -- p_api_version
146 -- API version number (current version is 1.0)
147 -- p_init_msg_list (optional, default FND_API.G_FALSE)
148 -- Valid values: FND_API.G_FALSE or FND_API.G_TRUE.
149 -- if set to FND_API.G_TRUE
150 -- initialize error message list
151 -- if set to FND_API.G_FALSE - not initialize error
152 -- message list
153 -- p_commit (optional, default FND_API.G_FALSE)
154 -- whether or not to commit the changes to database
155 -- p_organization_id Organization Id - Required Value
159 -- (Segment Values are passed)
156 -- p_Zone_From Zone Range From
157 -- p_Zone_To Zone Range To
158 -- p_location_fr Location Range From
160 -- p_location_to Location Range To
161 -- (Segment Values are passed)
162 -- p_item_fr item Range From
163 -- (only Segment Values are passed)
164 -- p_item_to Location Range To
165 -- (only Segment Values are passed)
166 -- p_no_of_copies No of copies of duplicate labels
167 --
168 --
169 -- Output Parameters
170 -- x_return_status
171 -- if the pick release process succeeds, the value is
172 -- fnd_api.g_ret_sts_success;
173 -- if there is an expected error, the value is
174 -- fnd_api.g_ret_sts_error;
175 -- if there is an unexpected error, the value is
176 -- fnd_api.g_ret_sts_unexp_error;
177 -- x_msg_count
178 -- if there is one or more errors, the number of error messages
179 -- in the buffer
180 -- x_msg_data
181 -- if there is one and only one error, the error message
182 -- (See fnd_api package for more details about the above output parameters)
183
184 PROCEDURE Item_Label
185 ( p_api_version IN NUMBER ,
186 p_init_msg_list IN VARCHAR2 := fnd_api.g_false ,
187 p_commit IN VARCHAR2 := fnd_api.g_false ,
188 x_return_status OUT VARCHAR2 ,
189 x_msg_count OUT NUMBER ,
190 x_msg_data OUT VARCHAR2 ,
191 p_organization_id IN NUMBER ,
192 p_zone_fr IN VARCHAR2 := NULL ,
193 p_zone_to IN VARCHAR2 := NULL ,
194 p_location_fr IN VARCHAR2 := NULL ,
195 p_location_to IN VARCHAR2 := NULL ,
196 p_item_fr IN VARCHAR2 := NULL ,
197 p_item_to IN VARCHAR2 := NULL ,
198 p_no_of_copies IN NUMBER
199 ) IS
200 begin
201 null;
202 end Item_Label;
203 /*---------------------------------------------------------------------*/
204 -- Name
205 -- PROCEDURE Material_Movement_Label
206 /* --------------------------------------------------------------------*/
207 --
208 -- Purpose
209 -- Create XML File for Material Movement Label like Pick and Putaway
210 --
211 -- Input Parameters
212 -- p_api_version
213 -- API version number (current version is 1.0)
214 -- p_init_msg_list (optional, default FND_API.G_FALSE)
215 -- Valid values: FND_API.G_FALSE or FND_API.G_TRUE.
216 -- if set to FND_API.G_TRUE
217 -- initialize error message list
218 -- if set to FND_API.G_FALSE - not initialize error
219 -- message list
220 -- p_commit (optional, default FND_API.G_FALSE)
221 -- whether or not to commit the changes to database
222 -- p_organization_id Organization Id - Required Value
223 -- p_Move_Order_Number Move Order Number
224 -- p_Zone_From Zone Range From
225 -- p_Zone_To Zone Range To
226 -- p_location_fr Location Range From
227 -- (only Segment Values are passed)
228 -- p_location_to Location Range To
229 -- (only Segment Values are passed)
230 -- p_mo_no Mover Order Number
231 -- p_wip_job_no WIP Job Number
232 -- p_no_of_copies No of copies of duplicate labels
233 --
234 --
235 -- Output Parameters
236 -- x_return_status
237 -- if the pick release process succeeds, the value is
238 -- fnd_api.g_ret_sts_success;
239 -- if there is an expected error, the value is
240 -- fnd_api.g_ret_sts_error;
241 -- if there is an unexpected error, the value is
242 -- fnd_api.g_ret_sts_unexp_error;
243 -- x_msg_count
244 -- if there is one or more errors, the number of error messages
245 -- in the buffer
246 -- x_msg_data
247 -- if there is one and only one error, the error message
248 -- (See fnd_api package for more details about the above output parameters)
249
250 PROCEDURE Material_Movement_Label
251 ( p_api_version IN NUMBER ,
252 p_init_msg_list IN VARCHAR2 := fnd_api.g_false ,
253 p_commit IN VARCHAR2 := fnd_api.g_false ,
254 x_return_status OUT VARCHAR2 ,
255 x_msg_count OUT NUMBER ,
256 x_msg_data OUT VARCHAR2 ,
257 p_organization_id IN NUMBER ,
258 p_zone_fr IN VARCHAR2 := NULL ,
259 p_zone_to IN VARCHAR2 := NULL ,
260 p_location_fr IN VARCHAR2 := NULL ,
261 p_location_to IN VARCHAR2 := NULL ,
262 p_mo_no IN NUMBER := NULL ,
263 p_wip_job_no IN NUMBER := NULL ,
264 p_no_of_copies IN NUMBER
265 ) is
266 begin
267 null;
268 end Material_Movement_Label;
269 /*---------------------------------------------------------------------*/
270 -- Name
271 -- PROCEDURE Palette_Label
272 /* --------------------------------------------------------------------*/
273 --
274 -- Purpose
275 -- Create XML File for Palette Label
276 --
277 -- Input Parameters
278 -- p_api_version
279 -- API version number (current version is 1.0)
280 -- p_init_msg_list (optional, default FND_API.G_FALSE)
281 -- Valid values: FND_API.G_FALSE or FND_API.G_TRUE.
282 -- if set to FND_API.G_TRUE
283 -- initialize error message list
284 -- if set to FND_API.G_FALSE - not initialize error
285 -- message list
286 -- p_commit (optional, default FND_API.G_FALSE)
287 -- whether or not to commit the changes to database
288 -- p_organization_id Organization Id - Required Value
289 -- p_Move_Order_Number Move Order Number
290 -- p_Zone_From Zone Range From
291 -- p_Zone_To Zone Range To
292 -- p_Location_fr Location Range From
293 -- (only Segment Values are passed)
294 -- p_Location_to Location Range To
295 -- (only Segment Values are passed)
296 -- p_Shipment Number Shipment Number
297 -- p_Delivery_id Delivery Id
298 -- p_Delivery_line_id Delivery Line Id
299 -- p_Pslip_no Pick Slip Number
300 -- p_no_of_copies No of copies of duplicate labels
301 --
302 --
303 -- Output Parameters
304 -- x_return_status
305 -- if the pick release process succeeds, the value is
306 -- fnd_api.g_ret_sts_success;
307 -- if there is an expected error, the value is
308 -- fnd_api.g_ret_sts_error;
309 -- if there is an unexpected error, the value is
310 -- fnd_api.g_ret_sts_unexp_error;
311 -- x_msg_count
312 -- if there is one or more errors, the number of error messages
313 -- in the buffer
314 -- x_msg_data
315 -- if there is one and only one error, the error message
316 -- (See fnd_api package for more details about the above output parameters)
317
318 PROCEDURE Palette_Label
319 ( p_api_version IN NUMBER ,
320 p_init_msg_list IN VARCHAR2 := fnd_api.g_false ,
321 p_commit IN VARCHAR2 := fnd_api.g_false ,
322 x_return_status OUT VARCHAR2 ,
323 x_msg_count OUT NUMBER ,
324 x_msg_data OUT VARCHAR2 ,
325 p_organization_id IN NUMBER ,
326 p_zone_fr IN VARCHAR2 := NULL ,
327 p_zone_to IN VARCHAR2 := NULL ,
328 p_location_fr IN VARCHAR2 := NULL ,
329 p_location_to IN VARCHAR2 := NULL ,
330 p_delivery_id IN NUMBER := NULL ,
331 p_shipment_number IN VARCHAR2 := NULL ,
332 p_delivery_line_id IN NUMBER := NULL ,
333 p_pslip_number IN VARCHAR2 := NULL ,
334 p_no_of_copies IN NUMBER
335 )is
336 begin
337 null;
338 end Palette_Label;
339 /*---------------------------------------------------------------------*/
340 -- Name
341 -- PROCEDURE Kanban_Card_Label
342 /* --------------------------------------------------------------------*/
343 --
344 -- Purpose
345 -- Create XML File for Kanban Card Label
346 --
347 -- Input Parameters
348 -- p_api_version
349 -- API version number (current version is 1.0)
350 -- p_init_msg_list (optional, default FND_API.G_FALSE)
351 -- Valid values: FND_API.G_FALSE or FND_API.G_TRUE.
352 -- if set to FND_API.G_TRUE
353 -- initialize error message list
354 -- if set to FND_API.G_FALSE - not initialize error
355 -- message list
356 -- p_commit (optional, default FND_API.G_FALSE)
357 -- whether or not to commit the changes to database
358 -- p_kanban_card_id_fr Kanban Card Id Range from
359 -- p_kanban_card_id_to Kanban Card Id Range To
360 -- p_no_of_copies No of copies of duplicate labels
361 --
362 --
363 -- Output Parameters
364 -- x_return_status
365 -- if the pick release process succeeds, the value is
366 -- fnd_api.g_ret_sts_success;
367 -- if there is an expected error, the value is
368 -- fnd_api.g_ret_sts_error;
369 -- if there is an unexpected error, the value is
370 -- fnd_api.g_ret_sts_unexp_error;
371 -- x_msg_count
372 -- if there is one or more errors, the number of error messages
373 -- in the buffer
374 -- x_msg_data
375 -- if there is one and only one error, the error message
376 -- (See fnd_api package for more details about the above output parameters)
377
378 PROCEDURE Kanban_Card_Label
379 ( p_api_version IN NUMBER ,
380 p_init_msg_list IN VARCHAR2 := fnd_api.g_false ,
381 p_commit IN VARCHAR2 := fnd_api.g_false ,
382 x_return_status OUT VARCHAR2 ,
383 x_msg_count OUT NUMBER ,
384 x_msg_data OUT VARCHAR2 ,
385 p_kanban_card_id_fr IN NUMBER ,
386 p_kanban_card_id_to IN NUMBER ,
387 p_no_of_copies IN NUMBER
388 )is
389 begin
390 null;
391 end Kanban_Card_Label;
392 /*---------------------------------------------------------------------*/
393 -- Name
394 -- PROCEDURE LPN_Label
395 /* --------------------------------------------------------------------*/
396 --
397 -- Purpose
398 -- Create XML File for LPN Label
399 --
400 -- Input Parameters
401 -- p_api_version
402 -- API version number (current version is 1.0)
403 -- p_init_msg_list (optional, default FND_API.G_FALSE)
404 -- Valid values: FND_API.G_FALSE or FND_API.G_TRUE.
405 -- if set to FND_API.G_TRUE
406 -- initialize error message list
407 -- if set to FND_API.G_FALSE - not initialize error
408 -- message list
409 -- p_commit (optional, default FND_API.G_FALSE)
410 -- whether or not to commit the changes to database
411 -- p_lpn_fr LPN Range from
412 -- p_lpn_to LPN Range To
413 -- p_customer_id Customer ID
414 -- p_ship_to_loc_id Ship To Location ID
415 -- p_Include_Contents Include LPn Contents
416 -- p_no_of_copies No of copies of duplicate labels
417 --
418 --
419 -- Output Parameters
420 -- x_return_status
421 -- if the pick release process succeeds, the value is
422 -- fnd_api.g_ret_sts_success;
423 -- if there is an expected error, the value is
424 -- fnd_api.g_ret_sts_error;
425 -- if there is an unexpected error, the value is
426 -- fnd_api.g_ret_sts_unexp_error;
427 -- x_msg_count
428 -- if there is one or more errors, the number of error messages
429 -- in the buffer
430 -- x_msg_data
431 -- if there is one and only one error, the error message
432 -- (See fnd_api package for more details about the above output parameters)
433
434 PROCEDURE LPN_Label
435 ( p_api_version IN NUMBER ,
436 p_init_msg_list IN VARCHAR2 := fnd_api.g_false ,
437 p_commit IN VARCHAR2 := fnd_api.g_false ,
438 x_return_status OUT VARCHAR2 ,
439 x_msg_count OUT NUMBER ,
440 x_msg_data OUT VARCHAR2 ,
441 p_lpn_fr IN VARCHAR2 ,
442 p_lpn_to IN VARCHAR2 ,
443 p_customer_id IN NUMBER ,
444 p_ship_to_loc_id IN NUMBER ,
445 p_include_contents IN VARCHAR2 ,
446 p_no_of_copies IN NUMBER
447 )is
448 begin
449 null;
450 end LPN_Label;
451
452 /*---------------------------------------------------------------------*/
453 -- Name
454 -- PROCEDURE Shipping Label
455 /* --------------------------------------------------------------------*/
456 --
457 -- Purpose
458 -- Create XML File for Shipping Label
459 --
460 -- Input Parameters
461 -- p_api_version
462 -- API version number (current version is 1.0)
463 -- p_init_msg_list (optional, default FND_API.G_FALSE)
464 -- Valid values: FND_API.G_FALSE or FND_API.G_TRUE.
465 -- if set to FND_API.G_TRUE
466 -- initialize error message list
467 -- if set to FND_API.G_FALSE - not initialize error
468 -- message list
469 -- p_commit (optional, default FND_API.G_FALSE)
470 -- whether or not to commit the changes to database
471 -- p_organization_id Organization Id - Required Value
472 -- p_Move_Order_Number Move Order Number
473 -- p_Zone_From Zone Range From
474 -- p_Zone_To Zone Range To
475 -- p_Location_fr Location Range From
476 -- (only Segment Values are passed)
477 -- p_Location_to Location Range To
478 -- (only Segment Values are passed)
479 -- p_Shipment Number Shipment Number
480 -- p_Delivery_id Delivery Id
481 -- p_Delivery_line_id Delivery Line Id
482 -- p_Pslip_no Pick Slip Number
483 -- p_no_of_copies No of copies of duplicate labels
484 --
485 --
486 -- Output Parameters
487 -- x_return_status
488 -- if the pick release process succeeds, the value is
489 -- fnd_api.g_ret_sts_success;
490 -- if there is an expected error, the value is
491 -- fnd_api.g_ret_sts_error;
492 -- if there is an unexpected error, the value is
493 -- fnd_api.g_ret_sts_unexp_error;
494 -- x_msg_count
495 -- if there is one or more errors, the number of error messages
496 -- in the buffer
497 -- x_msg_data
498 -- if there is one and only one error, the error message
499 -- (See fnd_api package for more details about the above output parameters)
500
501 PROCEDURE Shipping_Label
502 ( p_api_version IN NUMBER ,
503 p_init_msg_list IN VARCHAR2 := fnd_api.g_false ,
504 p_commit IN VARCHAR2 := fnd_api.g_false ,
505 x_return_status OUT VARCHAR2 ,
506 x_msg_count OUT NUMBER ,
507 x_msg_data OUT VARCHAR2 ,
508 p_organization_id IN NUMBER ,
509 p_zone_fr IN VARCHAR2 := NULL ,
510 p_zone_to IN VARCHAR2 := NULL ,
511 p_location_fr IN VARCHAR2 := NULL ,
512 p_location_to IN VARCHAR2 := NULL ,
513 p_delivery_id IN NUMBER := NULL ,
514 p_shipment_number IN VARCHAR2 := NULL ,
515 p_delivery_line_id IN NUMBER := NULL ,
516 p_pslip_number IN VARCHAR2 := NULL ,
517 p_no_of_copies IN NUMBER
518 )is
519 begin
520 null;
521 end Shipping_Label;
522 /*---------------------------------------------------------------------*/
523 -- Name
524 -- FUNCTION Output_file_dir
525 /* --------------------------------------------------------------------*/
526 function Output_file_dir return varchar2
527 IS
528 v_db_name VARCHAR2(100);
529 v_log_name VARCHAR2(100);
530 v_db_name VARCHAR2(100);
531 v_st_position number(3);
532 v_end_position number(3);
533 v_w_position number(3);
534
535 BEGIN
536 select INSTR(value,',',1,2),INSTR(value,',',1,3)
537 into v_st_position,v_end_position from v$parameter
538 where upper(name) = 'UTL_FILE_DIR';
539
540 v_w_position := v_end_position - v_st_position - 1;
541
542 select substr(value,v_st_position+1,v_w_position)
543 into v_log_name from v$parameter
544 where upper(name) = 'UTL_FILE_DIR';
545 v_log_name := ltrim(v_log_name);
546 FND_FILE.PUT_NAMES(v_log_name,v_log_name,v_log_name);
547 return v_log_name;
548 EXCEPTION
549 WHEN OTHERS then
550 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
551 END Output_file_dir;
552 /*---------------------------------------------------------*/
553 END WMS_Label_PUB;