[Home] [Help]
PACKAGE BODY: APPS.INV_LABEL
Source
1 PACKAGE BODY INV_LABEL AS
2 /* $Header: INVLABPB.pls 120.17.12010000.3 2008/12/15 14:03:00 satishku ship $ */
3
4 LABELS_B CONSTANT VARCHAR2(50) := '<labels';
5 LABELS_E CONSTANT VARCHAR2(50) := '</labels>'||fnd_global.local_chr(10);
6 LABEL_B CONSTANT VARCHAR2(50) := '<label';
7 LABEL_E CONSTANT VARCHAR2(50) := '</label>'||fnd_global.local_chr(10);
8 VARIABLE_B CONSTANT VARCHAR2(50) := '<variable name= "';
9 VARIABLE_E CONSTANT VARCHAR2(50) := '</variable>'||fnd_global.local_chr(10);
10 TAG_E CONSTANT VARCHAR2(50) := '>'||fnd_global.local_chr(10);
11 BPA_HEADER_B CONSTANT VARCHAR2(10) := '<bpl>';
12 BPA_HEADER_E CONSTANT VARCHAR2(10) := '</bpl>';
13
14 TRACE_LEVEL CONSTANT NUMBER := 12;
15 TRACE_PROMPT CONSTANT VARCHAR2(10) := 'LABEL_MAIN';
16
17 XML_HEADER1 VARCHAR2(100) := '<?xml version="1.0" encoding="';
18 XML_HEADER1_2 CONSTANT VARCHAR2(100) := '" standalone="no"?>'||fnd_global.local_chr(10);
19 XML_HEADER2 CONSTANT VARCHAR2(100) := '<!DOCTYPE labels SYSTEM "label.dtd">'||fnd_global.local_chr(10);
20
21 /*-----------------------------------------
22 * PRIVATE API
23 *---------------------------------------*/
24 /*************************
25 * Clear global variables
26 *************************/
27 PROCEDURE CLEAR_GLOBALS IS
28 BEGIN
29 INV_LABEL_PVT3.g_lpn_id := -1;
30 INV_LABEL_PVT8.clear_carton_count;
31 END CLEAR_GLOBALS;
32
33 /************************************
34 * Get numbers between a range
35 ************************************/
36 PROCEDURE GET_NUMBER_BETWEEN_RANGE(
37 fm_x_number IN VARCHAR2
38 , to_x_number IN VARCHAR2
39 , x_return_status OUT NOCOPY VARCHAR2
40 , x_number_table OUT NOCOPY serial_tab_type
41 ) IS
42
43 l_number_table serial_tab_type;
44 l_end_numeric NUMBER := 0;
45 l_numeric_len NUMBER := 0;
46 l_LOOP_END NUMBER := 0;
47 l_number VARCHAR2(30);
48 m NUMBER := 0;
49 l_start_prefix VARCHAR2(30);
50 l_end_prefix VARCHAR2(30);
51 l_start_numeric NUMBER;
52 BEGIN
53 x_return_status := FND_API.G_RET_STS_SUCCESS;
54
55 -- Determine the start number prefix
56 l_start_prefix := rtrim(fm_x_number, '0123456789');
57
58 -- Determine the end serial number prefix
59 l_end_prefix := rtrim(to_x_number, '0123456789');
60
61 -- Determine the base start numeric portion
62 l_start_numeric := to_number(SUBSTR(fm_x_number, nvl(length(l_start_prefix),0) + 1));
63
64 -- Determine the base end numeric portion
65 l_end_numeric := to_number(SUBSTR(to_x_number, nvl(length(l_end_prefix),0) + 1));
66
67 -- Determine length of numeric portion
68 l_numeric_len := length(SUBSTR(fm_x_number,nvl(length(l_start_prefix),0) + 1));
69
70
71 -- First serial number
72 m := 1;
73 l_number_table(m) := fm_x_number;
74 WHILE (l_start_numeric < l_end_numeric)
75 LOOP
76 l_number := l_start_prefix || lpad('000000000000000000000000000000',l_numeric_len - length(to_char(l_start_numeric + 1)))
77 || to_char(l_start_numeric + 1);
78
79 m := m + 1;
80 l_number_table(m) := l_number;
81 l_start_numeric := l_start_numeric + 1;
82 END LOOP;
83
84 x_number_table := l_number_table;
85 END GET_NUMBER_BETWEEN_RANGE;
86
87 PROCEDURE GET_TYPE_TO_PRINT(
88 x_return_status OUT NOCOPY VARCHAR2
89 , x_msg_count OUT NOCOPY VARCHAR2
90 , x_msg_data OUT NOCOPY VARCHAR2
91 , x_types_to_print OUT NOCOPY label_type_tbl_type
92 , p_business_flow IN NUMBER
93 , p_label_type_id IN NUMBER
94 , p_no_of_copies IN NUMBER
95 , p_format_id IN NUMBER -- Added for Add Format/Printer project
96 , p_printer_name IN VARCHAR2 -- Added for Add Format/Printer project
97 )
98 IS
99 CURSOR c_types IS
100 SELECT wblt.document_id label_type_id,
101 ml.meaning label_type_name,
102 wblt.level_type_code level_type_code
103 -- Bug 3836484. The following literal is not good for performance
104 -- Remove the following since it is only used for trace message
105 -- , decode(wblt.level_type_code,10001,'Site',10002,'Application',10003,'Responsibility',10004,'User') level_type_name
106 FROM wms_bflow_label_type wblt, mfg_lookups ml
107 WHERE wblt.business_flow_code = p_business_flow
108 AND wblt.level_value_id =
109 decode(wblt.level_type_code, 10001,0,10002,FND_GLOBAL.RESP_APPL_ID
110 ,10003,FND_GLOBAL.RESP_ID,10004, FND_GLOBAL.USER_ID)
111 AND nvl(wblt.enabled_flag, 'N') = 'Y'
112 AND ml.lookup_type = 'WMS_LABEL_TYPE'
113 AND ml.lookup_code = wblt.document_id
114 order by wblt.level_type_code desc;
115
116 --Bug 6716623
117 --Cursor to remove those labels from printing at Cartonization
118 --which are enabled for both cartonization and pick release business flow.
119 Cursor sel_lab is
120 SELECT wblt.document_id label_type_id,
121 ml.meaning label_type_name,
122 wblt.level_type_code level_type_code
123 FROM wms_bflow_label_type wblt, mfg_lookups ml
124 WHERE wblt.business_flow_code = 22
125 AND nvl(wblt.enabled_flag, 'N') = 'Y'
126 AND ml.lookup_type = 'WMS_LABEL_TYPE'
127 AND ml.lookup_code = wblt.document_id
128 MINUS
129 SELECT wblt.document_id label_type_id,
130 ml.meaning label_type_name,
131 wblt.level_type_code level_type_code
132 FROM wms_bflow_label_type wblt, mfg_lookups ml
133 WHERE wblt.business_flow_code = 42
134 AND nvl(wblt.enabled_flag, 'N') = 'Y'
135 AND ml.lookup_type = 'WMS_LABEL_TYPE'
136 AND ml.lookup_code = wblt.document_id
137 ORDER BY level_type_code DESC; --Added Order By clause for Bug#7214797
138
139 v_type c_types%ROWTYPE;
140 l_type sel_lab%ROWTYPE;
141
142 l_count NUMBER := 0;
143
144 l_default_printer VARCHAR2(100);
145 l_api_status VARCHAR2(100);
146 l_error_message VARCHAR2(1000);
147
148 -- Added for Add Format/Printer project
149 l_format_name VARCHAR2(100);
150
151 CURSOR c_type_name(p_label_type_id NUMBER) IS
152 SELECT meaning FROM mfg_lookups
153 WHERE lookup_type = 'WMS_LABEL_TYPE'
154 AND lookup_code = p_label_type_id;
155
156 l_prev_level NUMBER := 0;
157 --Bug 4553439. Added the local variable for the organization_id
158 l_org_id NUMBER ;
159
160 BEGIN
161 IF (l_debug = 1) THEN
162 trace(' FND_GLOBAL.USER_ID : '|| FND_GLOBAL.USER_ID, TRACE_PROMPT, TRACE_LEVEL);
163 END IF;
164
165 IF (l_debug = 1) THEN
166 trace(' In Get_type_to_print,busFlow,lableTypeID,Manualformat,ManualPrinter= '
167 ||p_business_flow||','||p_label_type_id||','||p_format_id||','||p_printer_name, TRACE_PROMPT, TRACE_LEVEL);
168 END IF;
169 x_return_status := FND_API.G_RET_STS_SUCCESS;
170
171 --Bug 4553439. Getting the value of org_id from fnd_profile
172 FND_PROFILE.GET('MFG_ORGANIZATION_ID', l_org_id );
173 IF(l_debug = 1 ) THEN
174 trace('Value of org id from the profile' || l_org_id, TRACE_PROMPT,TRACE_LEVEL);
175 END IF;
176 --End of fix for Bug 4553439
177
178 -- Start to check if this is a manual print request.
179 IF p_label_type_id IS NOT NULL THEN
180 -- Manual mode, given label type
181 -- Get the label type name..
182 OPEN c_type_name(p_label_type_id);
183 FETCH c_type_name INTO x_types_to_print(1).label_type;
184
185 IF c_type_name%NOTFOUND THEN
186 IF (l_debug = 1) THEN
187 trace(' Invalid label type ID: '|| p_label_type_id ||', no label will be printed' , TRACE_PROMPT, TRACE_LEVEL);
188 END IF;
189 CLOSE c_type_name;
190 RETURN;
191 END IF;
192
193 CLOSE c_type_name;
194
195 x_types_to_print(1).label_type_id := p_label_type_id;
196 -- fabdi GMO changes
197 x_types_to_print(1).business_flow_code := p_business_flow;
198 -- x_types_to_print(1).business_flow_code := null;
199 -- end fabdi GMO changes
200
201 -- Added for Add Format/Printer project
202 -- In case of Formats, the Default Format is derived irrespective.
203 IF p_format_id IS NOT NULL THEN
204 -- This means that the format ID is passed in..
205 -- If the format is passed in, the default
206 x_types_to_print(1).manual_format_id := p_format_id;
207 BEGIN
208 SELECT label_format_name INTO l_format_name
209 FROM WMS_LABEL_FORMATS
210 WHERE label_format_id = p_format_id;
211 EXCEPTION
212 WHEN others THEN
213 IF (l_debug = 1) THEN
214 trace('Error in getting format name for format ID '||p_format_id,TRACE_PROMPT, TRACE_LEVEL);
215 END IF;
216 END;
217 x_types_to_print(1).manual_format_name := l_format_name;
218 ELSE
219 x_types_to_print(1).manual_format_id := null;
220 x_types_to_print(1).manual_format_name := null;
221 END IF;
222 IF (l_debug = 1) THEN
223 trace(' Manual format ID '||x_types_to_print(1).manual_format_id||' Name '||x_types_to_print(1).manual_format_name, TRACE_PROMPT, TRACE_LEVEL);
224 END IF;
225
226 -- Get default format for the label irrespective.
227 GET_DEFAULT_FORMAT( p_label_type_id => p_label_type_id,
228 p_label_format => x_types_to_print(1).default_format_name,
229 p_label_format_id => x_types_to_print(1).default_format_id);
230
231 IF (l_debug = 1) THEN
232 trace(' Default format '||x_types_to_print(1).default_format_name || ',' || x_types_to_print(1).default_format_id , TRACE_PROMPT, TRACE_LEVEL);
233 END IF;
234
235 -- Add printer/Format to Manual Label Printing page.
236 -- In case of Printers, the Default Printer is derived only if the printer name passed
237 -- in, is NULL;.
238 IF p_printer_name IS NOT NULL THEN
239 -- This means that the printer name is passed in..
240 x_types_to_print(1).manual_printer := p_printer_name;
241 x_types_to_print(1).default_printer := NULL;
242 END IF;
243
244 -- Deleted the IF condition around the call to the
245 -- WSH_REPORT_PRINTERS_PVT.GET_PRINTER() as part of cleanup since irrespective of
246 -- the fact that a default format is defined or not the printer has to be derived.
247 -- The format can also be derived in the individual label API's(INVLAP*B.pls) via
248 -- the rules engine anyways. Moreover the default printer is defined based on the
249 -- document (label type in this case and not on the format)
250
251 --Get default printer
252 IF(l_debug = 1 ) THEN -- For Bug 4553439
253 trace('Value of org id before calling printer for manual mode: ' || l_org_id, TRACE_PROMPT,TRACE_LEVEL);
254 END IF;
255
256 WSH_REPORT_PRINTERS_PVT.GET_PRINTER(
257 p_concurrent_program_id=>p_label_type_id,
258 p_organization_id =>l_org_id,--Bug 4553439. Added the parameter for organization_id
259 p_user_id =>fnd_global.user_id,
260 p_responsibility_id =>fnd_global.resp_id,
261 p_application_id =>fnd_global.resp_appl_id,
262 x_printer =>l_default_printer,
263 x_api_status =>l_api_status,
264 x_error_message =>l_error_message
265 );
266
267 IF l_api_status <> 'S' THEN
268 IF (l_debug = 1) THEN
269 trace(' Error in getting the default printer: '|| l_error_message , TRACE_PROMPT, TRACE_LEVEL);
270 END IF;
271 l_default_printer := null;
272 END IF;
273
274 x_types_to_print(1).default_printer := l_default_printer;
275 x_types_to_print(1).default_no_of_copies := nvl(p_no_of_copies,1);
276
277 IF (l_debug = 1) THEN
278 trace(' Found type to print,type id-name,format id-name,Default printer,manualFormat id-name, Manual printer ' , TRACE_PROMPT, TRACE_LEVEL);
279 trace(' '|| x_types_to_print(1).label_type_id || ' '
280 || x_types_to_print(1).label_type || ' '
281 ||x_types_to_print(1).default_format_id || ' '
282 ||x_types_to_print(1).default_format_name || ' '
283 ||x_types_to_print(1).default_printer
284 ||x_types_to_print(1).manual_format_id ||' '
285 ||x_types_to_print(1).manual_format_name || ' '
286 ||x_types_to_print(1).manual_printer
287 , TRACE_PROMPT, TRACE_LEVEL);
288 END IF;
289
290 -- Get type is done for given p_label_type_id,
291 -- Return
292 RETURN;
293 END IF;
294 -- End to check if this is a manual print request.
295
296 IF (l_debug = 1) THEN
297 trace(' Check setup for types to print, start from user level ', TRACE_PROMPT, TRACE_LEVEL);
298 END IF;
299 -- Start :Giving business flow code, find label type
300 -- This is a transaction mode label print request.
301
302 --Start Bug 6715800
303 IF p_business_flow = 22 THEN
304 OPEN sel_lab;
305 FETCH sel_lab INTO l_type;
306 IF(l_debug=1) THEN
307 trace(' after opening and fetching the values', TRACE_PROMPT, TRACE_LEVEL);
308 END IF;
309 IF sel_lab%NOTFOUND THEN
310 IF(l_debug=1) THEN
311 trace(' No types found to print', TRACE_PROMPT, TRACE_LEVEL);
312 END IF;
313 CLOSE sel_lab;
314 RETURN;
315 ELSE
316 IF(l_debug=1) THEN
317 trace(' inside else', TRACE_PROMPT, TRACE_LEVEL);
318 END IF;
319 l_prev_level := l_type.level_type_code;
320 l_count := 0;
321 WHILE (sel_lab%FOUND) AND (l_type.level_type_code = l_prev_level)
322 LOOP
323 l_count := l_count + 1;
324
325 GET_DEFAULT_FORMAT
326 (p_label_type_id => l_type.label_type_id,
327 p_label_format => x_types_to_print(l_count).default_format_name,
328 p_label_format_id => x_types_to_print(l_count).default_format_id );
329
330
331 IF(l_debug = 1 ) THEN
332 trace('Value of org id before calling printer for business flow: ' || l_org_id, TRACE_PROMPT,TRACE_LEVEL);
333 END IF ;
334
335 WSH_REPORT_PRINTERS_PVT.GET_PRINTER(
336 p_concurrent_program_id=> l_type.label_type_id,
337 p_organization_id =>l_org_id,--Bug 4553439. Added the parameter for organization_id
338 p_user_id =>fnd_global.user_id,
339 p_responsibility_id =>fnd_global.resp_id,
340 p_application_id =>fnd_global.resp_appl_id,
341 x_printer =>l_default_printer,
342 x_api_status =>l_api_status,
343 x_error_message =>l_error_message
344 );
345
346 IF l_api_status <> 'S' THEN
347 IF (l_debug = 1) THEN
348 trace(' Error in getting the default printer: '|| l_error_message , TRACE_PROMPT, TRACE_LEVEL);
349 END IF;
350 l_default_printer := null;
351 END IF;
352
353 x_types_to_print(l_count).default_printer := l_default_printer;
354 x_types_to_print(l_count).default_no_of_copies := nvl(p_no_of_copies,1);
355 x_types_to_print(l_count).business_flow_code := p_business_flow;
356 x_types_to_print(l_count).label_type_id := l_type.label_type_id;
357 x_types_to_print(l_count).label_type := l_type.label_type_name;
358 IF (l_debug = 1) THEN
359 trace(' Found type to print, '||
360 ' type id='|| x_types_to_print(l_count).label_type_id ||
361 ' type name='|| x_types_to_print(l_count).label_type ||
362 ' formatId=' || x_types_to_print(l_count).default_format_id ||
363 ' formatName='||x_types_to_print(l_count).default_format_name||
364 ' printer='||x_types_to_print(l_count).default_printer
365 , TRACE_PROMPT, TRACE_LEVEL);
366 END IF;
367 l_prev_level := l_type.level_type_code;
368 FETCH sel_lab INTO l_type;
369 END LOOP;
370 CLOSE sel_lab;
371 END IF;
372 ELSE
373 --ENd Bug 6715800
374 OPEN c_types;
375 FETCH c_types INTO v_type;
376 IF c_types%NOTFOUND THEN
377 -- No types found
378 IF(l_debug=1) THEN
379 trace(' No types found to print', TRACE_PROMPT, TRACE_LEVEL);
380 END IF;
381 CLOSE c_types;
382 RETURN;
383 ELSE
384 -- Found types to print
385 l_prev_level := v_type.level_type_code;
386 l_count := 0;
387 WHILE (c_types%FOUND) AND (v_type.level_type_code = l_prev_level)
388 LOOP
389 -- Find a type, get attributes
390 l_count := l_count + 1;
391 -- first get label format
392 GET_DEFAULT_FORMAT
393 (p_label_type_id => v_type.label_type_id,
394 p_label_format => x_types_to_print(l_count).default_format_name,
395 p_label_format_id => x_types_to_print(l_count).default_format_id );
396
397 -- Deleted the IF condition around the call to the
398 -- WSH_REPORT_PRINTERS_PVT.GET_PRINTER() as part of cleanup since irrespective of
399 -- the fact that a default format is defined or not the printer has to be derived.
400 -- The format can also be derived in the individual label API's(INVLAP*B.pls) via
401 -- the rules engine anyways. Moreover the default printer is defined based on the
402 -- document (label type in this case and not on the format)
403
404 -- Get default printer
405
406 IF(l_debug = 1 ) THEN -- For Bug 4553439
407 trace('Value of org id before calling printer for business flow: ' || l_org_id, TRACE_PROMPT,TRACE_LEVEL);
408 END IF ;
409
410 WSH_REPORT_PRINTERS_PVT.GET_PRINTER(
411 p_concurrent_program_id=> v_type.label_type_id,
412 p_organization_id =>l_org_id,--Bug 4553439. Added the parameter for organization_id
413 p_user_id =>fnd_global.user_id,
414 p_responsibility_id =>fnd_global.resp_id,
415 p_application_id =>fnd_global.resp_appl_id,
416 x_printer =>l_default_printer,
417 x_api_status =>l_api_status,
418 x_error_message =>l_error_message
419 );
420
421 IF l_api_status <> 'S' THEN
422 IF (l_debug = 1) THEN
423 trace(' Error in getting the default printer: '|| l_error_message , TRACE_PROMPT, TRACE_LEVEL);
424 END IF;
425 l_default_printer := null;
426 END IF;
427
428 x_types_to_print(l_count).default_printer := l_default_printer;
429 x_types_to_print(l_count).default_no_of_copies := nvl(p_no_of_copies,1);
430 x_types_to_print(l_count).business_flow_code := p_business_flow;
431 x_types_to_print(l_count).label_type_id := v_type.label_type_id;
432 x_types_to_print(l_count).label_type := v_type.label_type_name;
433 IF (l_debug = 1) THEN
434 trace(' Found type to print, '||
435 ' type id='|| x_types_to_print(l_count).label_type_id ||
436 ' type name='|| x_types_to_print(l_count).label_type ||
437 ' formatId=' || x_types_to_print(l_count).default_format_id ||
438 ' formatName='||x_types_to_print(l_count).default_format_name||
439 ' printer='||x_types_to_print(l_count).default_printer
440 -- Bug 3836484. Remove the following line for performance reason
441 -- ||' setupLevel='||v_type.level_type_name
442 , TRACE_PROMPT, TRACE_LEVEL);
443 END IF;
444
445
446 -- Get next label type
447 l_prev_level := v_type.level_type_code;
448 FETCH c_types INTO v_type;
449 END LOOP;
450 CLOSE c_types;
451 END IF;
452 END IF;
453 -- End :Giving business flow code, find label type
454 END GET_TYPE_TO_PRINT;
455
456
457 PROCEDURE get_variable_data(
458 x_variable_content OUT NOCOPY label_tbl_type
459 , x_msg_count OUT NOCOPY NUMBER
460 , x_msg_data OUT NOCOPY VARCHAR2
461 , x_return_status OUT NOCOPY VARCHAR2
462 , p_label_type_info IN label_type_rec
463 , p_transaction_id IN NUMBER
464 , p_input_param IN MTL_MATERIAL_TRANSACTIONS_TEMP%ROWTYPE
465 , p_transaction_identifier IN NUMBER
466 ) IS
467
468 l_transaction_id NUMBER;
469 x_variable_content_long LONG;
470 BEGIN
471 -- Initialize API return status to success
472 x_return_status := FND_API.G_RET_STS_SUCCESS;
473
474 IF p_label_type_info.label_type_id = 1 THEN
475 BEGIN
476 INV_LABEL_PVT1.get_variable_data(
477 x_variable_content => x_variable_content
478 ,x_msg_count => x_msg_count
479 ,x_msg_data => x_msg_data
480 ,x_return_status => x_return_status
481 ,p_label_type_info => p_label_type_info
482 ,p_transaction_id => p_transaction_id
483 ,p_input_param => p_input_param
484 ,p_transaction_identifier => p_transaction_identifier
485 );
486 IF (l_debug = 1) THEN
487 trace('Got variable from type 1, # of rec: '|| x_variable_content.count(), TRACE_PROMPT, TRACE_LEVEL);
488 END IF;
489 EXCEPTION
490 WHEN OTHERS THEN
491 x_return_status := FND_API.G_RET_STS_ERROR;
492 IF (l_debug = 1) THEN
493 trace('ERROR CODE = ' || SQLCODE, TRACE_PROMPT, TRACE_LEVEL);
494 trace('ERROR MESSAGE = ' || SQLERRM, TRACE_PROMPT, TRACE_LEVEL);
495 END IF;
496 END;
497
498 ELSIF p_label_type_info.label_type_id = 2 THEN
499 BEGIN
500 INV_LABEL_PVT2.get_variable_data(
501 x_variable_content => x_variable_content
502 ,x_msg_count => x_msg_count
503 ,x_msg_data => x_msg_data
504 ,x_return_status => x_return_status
505 ,p_label_type_info => p_label_type_info
506 ,p_transaction_id => p_transaction_id
507 ,p_input_param => p_input_param
508 ,p_transaction_identifier => p_transaction_identifier
509 );
510 IF (l_debug = 1) THEN
511 trace('Got variable from type 2, # of rec: '|| x_variable_content.count(), TRACE_PROMPT, TRACE_LEVEL);
512 END IF;
513 EXCEPTION
514 WHEN OTHERS THEN
515 x_return_status := FND_API.G_RET_STS_ERROR;
516 IF (l_debug = 1) THEN
517 trace('ERROR CODE = ' || SQLCODE, TRACE_PROMPT, TRACE_LEVEL);
518 trace('ERROR MESSAGE = ' || SQLERRM, TRACE_PROMPT, TRACE_LEVEL);
519 END IF;
520 END;
521
522 ELSIF p_label_type_info.label_type_id = 3 THEN
523 BEGIN
524 INV_LABEL_PVT3.get_variable_data(
525 x_variable_content => x_variable_content
526 ,x_msg_count => x_msg_count
527 ,x_msg_data => x_msg_data
528 ,x_return_status => x_return_status
529 ,p_label_type_info => p_label_type_info
530 ,p_transaction_id => p_transaction_id
531 ,p_input_param => p_input_param
532 ,p_transaction_identifier => p_transaction_identifier
533 );
534 IF (l_debug = 1) THEN
535 trace('Got variable from type 3, # of rec: '|| x_variable_content.count(), TRACE_PROMPT, TRACE_LEVEL);
536 END IF;
537 EXCEPTION
538 WHEN OTHERS THEN
539 x_return_status := FND_API.G_RET_STS_ERROR;
540 IF (l_debug = 1) THEN
541 trace('ERROR CODE = ' || SQLCODE, TRACE_PROMPT, TRACE_LEVEL);
542 trace('ERROR MESSAGE = ' || SQLERRM, TRACE_PROMPT, TRACE_LEVEL);
543 END IF;
544 END;
545
546 ELSIF p_label_type_info.label_type_id = 4 THEN
547 BEGIN
548 INV_LABEL_PVT4.get_variable_data(
549 x_variable_content => x_variable_content
550 ,x_msg_count => x_msg_count
551 ,x_msg_data => x_msg_data
552 ,x_return_status => x_return_status
553 ,p_label_type_info => p_label_type_info
554 ,p_transaction_id => p_transaction_id
555 ,p_input_param => p_input_param
556 ,p_transaction_identifier => p_transaction_identifier
557 );
558 IF (l_debug = 1) THEN
559 trace('Got variable from type 4, # of rec: '|| x_variable_content.count(), TRACE_PROMPT, TRACE_LEVEL);
560 END IF;
561 EXCEPTION
562 WHEN OTHERS THEN
563 x_return_status := FND_API.G_RET_STS_ERROR;
564 IF (l_debug = 1) THEN
565 trace('ERROR CODE = ' || SQLCODE, TRACE_PROMPT, TRACE_LEVEL);
566 trace('ERROR MESSAGE = ' || SQLERRM, TRACE_PROMPT, TRACE_LEVEL);
567 END IF;
568 END;
569
570 ELSIF p_label_type_info.label_type_id = 5 THEN
571 BEGIN
572 INV_LABEL_PVT5.get_variable_data(
573 x_variable_content => x_variable_content
574 ,x_msg_count => x_msg_count
575 ,x_msg_data => x_msg_data
576 ,x_return_status => x_return_status
577 ,p_label_type_info => p_label_type_info
578 ,p_transaction_id => p_transaction_id
579 ,p_input_param => p_input_param
580 ,p_transaction_identifier => p_transaction_identifier
581 );
582 IF (l_debug = 1) THEN
583 trace('Got variable from type 5, # of rec: '|| x_variable_content.count(), TRACE_PROMPT, TRACE_LEVEL);
584 END IF;
585 EXCEPTION
586 WHEN OTHERS THEN
587 x_return_status := FND_API.G_RET_STS_ERROR;
588 IF (l_debug = 1) THEN
589 trace('ERROR CODE = ' || SQLCODE, TRACE_PROMPT, TRACE_LEVEL);
590 trace('ERROR MESSAGE = ' || SQLERRM, TRACE_PROMPT, TRACE_LEVEL);
591 END IF;
592 END;
593
594 ELSIF p_label_type_info.label_type_id = 6 THEN
595 BEGIN
596 INV_LABEL_PVT6.get_variable_data(
597 x_variable_content => x_variable_content
598 ,x_msg_count => x_msg_count
599 ,x_msg_data => x_msg_data
600 ,x_return_status => x_return_status
601 ,p_label_type_info => p_label_type_info
602 ,p_transaction_id => p_transaction_id
603 ,p_input_param => p_input_param
604 ,p_transaction_identifier => p_transaction_identifier
605 );
606 IF (l_debug = 1) THEN
607 trace('Got variable from type 6, # of rec: '|| x_variable_content.count(), TRACE_PROMPT, TRACE_LEVEL);
608 END IF;
609 EXCEPTION
610 WHEN OTHERS THEN
611 x_return_status := FND_API.G_RET_STS_ERROR;
612 IF (l_debug = 1) THEN
613 trace('ERROR CODE = ' || SQLCODE, TRACE_PROMPT, TRACE_LEVEL);
614 trace('ERROR MESSAGE = ' || SQLERRM, TRACE_PROMPT, TRACE_LEVEL);
615 END IF;
616 END;
617
618 ELSIF p_label_type_info.label_type_id = 7 THEN
619 BEGIN
620 INV_LABEL_PVT7.get_variable_data(
621 x_variable_content => x_variable_content
622 ,x_msg_count => x_msg_count
623 ,x_msg_data => x_msg_data
624 ,x_return_status => x_return_status
625 ,p_label_type_info => p_label_type_info
626 ,p_transaction_id => p_transaction_id
627 ,p_input_param => p_input_param
628 ,p_transaction_identifier => p_transaction_identifier
629 );
630 IF (l_debug = 1) THEN
631 trace('Got variable from type 7, # of rec: '|| x_variable_content.count(), TRACE_PROMPT, TRACE_LEVEL);
632 END IF;
633 EXCEPTION
634 WHEN OTHERS THEN
635 x_return_status := FND_API.G_RET_STS_ERROR;
636 IF (l_debug = 1) THEN
637 trace('ERROR CODE = ' || SQLCODE, TRACE_PROMPT, TRACE_LEVEL);
638 trace('ERROR MESSAGE = ' || SQLERRM, TRACE_PROMPT, TRACE_LEVEL);
639 END IF;
640 END;
641
642 ELSIF p_label_type_info.label_type_id = 8 THEN
643 BEGIN
644 INV_LABEL_PVT8.get_variable_data(
645 x_variable_content => x_variable_content
646 ,x_msg_count => x_msg_count
647 ,x_msg_data => x_msg_data
648 ,x_return_status => x_return_status
649 ,p_label_type_info => p_label_type_info
650 ,p_transaction_id => p_transaction_id
651 ,p_input_param => p_input_param
652 ,p_transaction_identifier => p_transaction_identifier
653 );
654 IF (l_debug = 1) THEN
655 trace('Got variable from type 8, # of rec: '|| x_variable_content.count(), TRACE_PROMPT, TRACE_LEVEL);
656 END IF;
657 EXCEPTION
658 WHEN OTHERS THEN
659 x_return_status := FND_API.G_RET_STS_ERROR;
660 IF (l_debug = 1) THEN
661 trace('Error in calling PVT8 get_variable_data, ERROR CODE = ' || SQLCODE, TRACE_PROMPT, TRACE_LEVEL);
662 trace('ERROR MESSAGE = ' || SQLERRM, TRACE_PROMPT, TRACE_LEVEL);
663 END IF;
664 END;
665
666 ELSIF p_label_type_info.label_type_id = 9 THEN
667 BEGIN
668 INV_LABEL_PVT9.get_variable_data(
669 x_variable_content => x_variable_content
670 ,x_msg_count => x_msg_count
671 ,x_msg_data => x_msg_data
672 ,x_return_status => x_return_status
673 ,p_label_type_info => p_label_type_info
674 ,p_transaction_id => p_transaction_id
675 ,p_input_param => p_input_param
676 ,p_transaction_identifier => p_transaction_identifier
677 );
678 IF (l_debug = 1) THEN
679 trace('Got variable from type 9, # of rec: '|| x_variable_content.count(), TRACE_PROMPT, TRACE_LEVEL);
680 END IF;
681 EXCEPTION
682 WHEN OTHERS THEN
683 x_return_status := FND_API.G_RET_STS_ERROR;
684 IF (l_debug = 1) THEN
685 trace('ERROR CODE = ' || SQLCODE, TRACE_PROMPT, TRACE_LEVEL);
686 trace('ERROR MESSAGE = ' || SQLERRM, TRACE_PROMPT, TRACE_LEVEL);
687 END IF;
688 END;
689
690 ELSIF p_label_type_info.label_type_id = 10 THEN
691 BEGIN
692 INV_LABEL_PVT10.get_variable_data(
693 x_variable_content => x_variable_content
694 ,x_msg_count => x_msg_count
695 ,x_msg_data => x_msg_data
696 ,x_return_status => x_return_status
697 ,p_label_type_info => p_label_type_info
698 ,p_transaction_id => p_transaction_id
699 ,p_input_param => p_input_param
700 ,p_transaction_identifier => p_transaction_identifier
701 );
702 IF (l_debug = 1) THEN
703 trace('Got variable from type 10, # of rec: '|| x_variable_content.count(), TRACE_PROMPT, TRACE_LEVEL);
704 END IF;
705 EXCEPTION
706 WHEN OTHERS THEN
707 x_return_status := FND_API.G_RET_STS_ERROR;
708 IF (l_debug = 1) THEN
709 trace('ERROR CODE = ' || SQLCODE, TRACE_PROMPT, TRACE_LEVEL);
710 trace('ERROR MESSAGE = ' || SQLERRM, TRACE_PROMPT, TRACE_LEVEL);
711 END IF;
712 END;
713
714 -- fabdi GMO changes start
715 ELSIF p_label_type_info.label_type_id in (11,12) THEN
716 BEGIN
717 INV_LABEL_PVT11.get_variable_data(
718 x_variable_content => x_variable_content
719 ,x_msg_count => x_msg_count
720 ,x_msg_data => x_msg_data
721 ,x_return_status => x_return_status
722 ,p_label_type_info => p_label_type_info
723 ,p_transaction_id => p_transaction_id
724 ,p_input_param => p_input_param
725 ,p_transaction_identifier => p_transaction_identifier
726 );
727 IF (l_debug = 1) THEN
728 trace('Got variable from type 11/12, # of rec: '|| x_variable_content.count(), TRACE_PROMPT, TRACE_LEVEL);
729 END IF;
730 EXCEPTION
731 WHEN OTHERS THEN
732 x_return_status := FND_API.G_RET_STS_ERROR;
733 IF (l_debug = 1) THEN
734 trace('ERROR CODE = ' || SQLCODE, TRACE_PROMPT, TRACE_LEVEL);
735 trace('ERROR MESSAGE = ' || SQLERRM, TRACE_PROMPT, TRACE_LEVEL);
736 END IF;
737 END;
738
739 ELSIF p_label_type_info.label_type_id IN (13,14) THEN
740 BEGIN
741 INV_LABEL_PVT13.get_variable_data(
742 x_variable_content => x_variable_content
743 ,x_msg_count => x_msg_count
744 ,x_msg_data => x_msg_data
745 ,x_return_status => x_return_status
746 ,p_label_type_info => p_label_type_info
747 ,p_transaction_id => p_transaction_id
748 ,p_input_param => p_input_param
749 ,p_transaction_identifier => p_transaction_identifier
750 );
751 IF (l_debug = 1) THEN
752 trace('Got variable from type 13/14, # of rec: '|| x_variable_content.count(), TRACE_PROMPT, TRACE_LEVEL);
753 END IF;
754 EXCEPTION
755 WHEN OTHERS THEN
756 x_return_status := FND_API.G_RET_STS_ERROR;
757 IF (l_debug = 1) THEN
758 trace('ERROR CODE = ' || SQLCODE, TRACE_PROMPT, TRACE_LEVEL);
759 trace('ERROR MESSAGE = ' || SQLERRM, TRACE_PROMPT, TRACE_LEVEL);
760 END IF;
761 END;
762
763 ELSIF p_label_type_info.label_type_id = 15 THEN
764 BEGIN
765 INV_LABEL_PVT15.get_variable_data(
766 x_variable_content => x_variable_content
767 ,x_msg_count => x_msg_count
768 ,x_msg_data => x_msg_data
769 ,x_return_status => x_return_status
770 ,p_label_type_info => p_label_type_info
771 ,p_transaction_id => p_transaction_id
772 ,p_input_param => p_input_param
773 ,p_transaction_identifier => p_transaction_identifier
774 );
775 IF (l_debug = 1) THEN
776 trace('Got variable from type 15, # of rec: '|| x_variable_content.count(), TRACE_PROMPT, TRACE_LEVEL);
777 END IF;
778 EXCEPTION
779 WHEN OTHERS THEN
780 x_return_status := FND_API.G_RET_STS_ERROR;
781 IF (l_debug = 1) THEN
782 trace('ERROR CODE = ' || SQLCODE, TRACE_PROMPT, TRACE_LEVEL);
783 trace('ERROR MESSAGE = ' || SQLERRM, TRACE_PROMPT, TRACE_LEVEL);
784 END IF;
785 END;
786 -- FABDI END (GMO changes)...
787
788 -- hjogleka, Bug #6417575, Label Printing Support for WIP Move Transactions (12.1)
789 ELSIF p_label_type_info.label_type_id = 16 THEN
790 BEGIN
791 INV_LABEL_PVT16.get_variable_data(
792 x_variable_content => x_variable_content
793 ,x_msg_count => x_msg_count
794 ,x_msg_data => x_msg_data
795 ,x_return_status => x_return_status
796 ,p_label_type_info => p_label_type_info
797 ,p_transaction_id => p_transaction_id
798 ,p_input_param => p_input_param
799 ,p_transaction_identifier => p_transaction_identifier
800 );
801 IF (l_debug = 1) THEN
802 trace('Got variable from type 16, # of rec: '|| x_variable_content.count(), TRACE_PROMPT, TRACE_LEVEL);
803 END IF;
804 EXCEPTION
805 WHEN OTHERS THEN
806 x_return_status := FND_API.G_RET_STS_ERROR;
807 IF (l_debug = 1) THEN
808 trace('ERROR CODE = ' || SQLCODE, TRACE_PROMPT, TRACE_LEVEL);
809 trace('ERROR MESSAGE = ' || SQLERRM, TRACE_PROMPT, TRACE_LEVEL);
810 END IF;
811 END;
812
813 ELSE
814 IF (l_debug = 1) THEN
815 trace(' Wrong value of label_type_id : '||p_label_type_info.label_type_id , TRACE_PROMPT, TRACE_LEVEL);
816 END IF;
817 END IF;
818 IF(x_variable_content.count() = 0) THEN
819 IF (l_debug = 1) THEN
820 trace('Did not get variable data ', TRACE_PROMPT, TRACE_LEVEL);
821 END IF;
822 END IF;
823 END get_variable_data;
824
825
826 PROCEDURE get_date_time_user IS
827 l_date VARCHAR2(20);
828 l_time VARCHAR2(20);
829 BEGIN
830 -- Get Sysdate in Date/Time
831 SELECT to_char(sysdate, G_DATE_FORMAT_MASK), to_char(sysdate, 'HH24:MI:SS')
832 INTO l_date, l_time FROM dual;
833
834 -- Set the global variables
835 G_DATE := l_date;
836 G_TIME := l_time;
837
838 SELECT user_name INTO G_USER
839 FROM FND_USER WHERE user_id = fnd_global.user_id;
840
841 EXCEPTION
842 WHEN others THEN
843 IF (l_debug = 1) THEN
844 trace('Error in get_date_time_user', TRACE_PROMPT, TRACE_LEVEL);
845 END IF;
846 END get_date_time_user;
847
848 /**************************************************
849 * Procedure to check the xml string with
850 * XML standards and replace the characters
851 * that are not legal according to the XML standard
852 * The data for the <variable> .. </variable> is checked
853 * and the replacements are
854 * & => &
855 * ' => '
856 * \ => \\
857 * " => \"
858 * < => <
859 * > => >
860 **************************************************/
861 PROCEDURE check_xml(p_xml IN OUT NOCOPY LONG) IS
862 l_xml LONG;
863 -- Fix for BUG: 4731922. Increased the length of l_substr.
864 l_substr VARCHAR2(2000); -- VARCHAR2(254);
865 -- End of Fix for BUG: 4731922.
866 i NUMBER;
867 l_last_index NUMBER;
868 l_begin_index NUMBER;
869 l_end_index NUMBER;
870
871 BEGIN
872 --Find the first <vaiable tag
873 l_last_index := instr(p_xml, '<variable');
874 IF l_last_index = 0 THEN
875 -- no variable found, return
876 return;
877 END IF;
878
879 -- Find the beginning of data
880 l_begin_index := instr(p_xml, '>', l_last_index) + 1;
881 l_xml := substr(p_xml, 1, l_begin_index -1);
882 WHILE l_begin_index <> 0 LOOP
883 l_end_index := instr(p_xml, '</variable>', l_begin_index);
884 l_substr := substr(p_xml, l_begin_index, l_end_index-l_begin_index);
885 -- replace special characters
886 l_substr := replace(l_substr, '&', '&'||'amp;');
887 l_substr := replace(l_substr, '''', '&' || 'apos;');
888 --
889 -- @@@ Start of Fix for bug 3551132
890 --l_substr := replace(l_substr, '\', '\\');
891 l_substr := replace(l_substr, '"', '&' || 'quot;');
892 --l_substr := replace(l_substr, '"', '\"');
893 -- @@@ End of Fix for bug 3551132
894 --
895 l_substr := replace(l_substr, '<', '&'||'lt;');
896 l_substr := replace(l_substr, '>', '&'||'gt;');
897
898 l_begin_index := instr(p_xml, '<variable', l_end_index);
899 IF l_begin_index <> 0 THEN
900 l_begin_index := instr(p_xml, '>', l_begin_index) + 1;
901 l_xml := l_xml || l_substr || substr(p_xml, l_end_index, l_begin_index-l_end_index);
902 ELSE
903 l_xml := l_xml || l_substr || substr(p_xml, l_end_index);
904 END IF;
905 END LOOP;
906 p_xml := l_xml ;
907 EXCEPTION
908 WHEN others THEN
909 IF (l_debug = 1) THEN
910 trace('Error in check_xml, xml unchanged', TRACE_PROMPT, TRACE_LEVEL);
911 trace('ERROR CODE = ' || SQLCODE, TRACE_PROMPT, TRACE_LEVEL);
912 trace('ERROR MESSAGE = ' || SQLERRM, TRACE_PROMPT, TRACE_LEVEL);
913 END IF;
914 p_xml := p_xml;
915 END check_xml;
916
917 /***************************************************
918 * Function to get the XML encoding
919 * based on the character set at the database level
920 * The default encoding is UTF-8 for Unicode 2.0
921 ***************************************************/
922 PROCEDURE get_xml_encoding IS
923 l_character_set VARCHAR2(50) := null;
924 l_xml_encoding VARCHAR2(50) := null;
925
926 BEGIN
927
928 l_character_set := G_CHARACTER_SET;
929 l_xml_encoding := G_XML_ENCODING;
930
931 IF (l_debug = 1) THEN
932 trace('Current charac set and encoding: '||l_character_set|| ','||l_xml_encoding, TRACE_PROMPT, TRACE_LEVEL);
933 END IF;
934
935 IF (l_character_set IS NULL) THEN
936 -- Get character set
937 SELECT value INTO l_character_set
938 FROM nls_database_parameters
939 WHERE parameter = 'NLS_CHARACTERSET';
940 IF SQL%NOTFOUND THEN
941 IF (l_debug = 1) THEN
942 trace('Error in getting character set', TRACE_PROMPT, TRACE_LEVEL);
943 END IF;
944 l_character_set := null;
945 END IF;
946 IF (l_debug = 1) THEN
947 trace('Got character set='||l_character_set, TRACE_PROMPT, TRACE_LEVEL);
948 END IF;
949
950 END IF;
951 G_CHARACTER_SET := l_character_set;
952
953 IF l_character_set IS NULL THEN
954 IF (l_debug = 1) THEN
955 trace('Character Set is null, return default xml encoding', TRACE_PROMPT, TRACE_LEVEL);
956 END IF;
957 l_xml_encoding := G_DEFAULT_XML_ENCODING;
958 ELSIF l_xml_encoding IS NULL THEN
959 -- get new xml_encoding
960 SELECT tag INTO l_xml_encoding
961 FROM FND_LOOKUP_VALUES_VL
962 WHERE LOOKUP_TYPE = 'FND_ISO_CHARACTER_SET_MAP'
963 AND LOOKUP_CODE = l_character_set;
964
965 IF SQL%NOTFOUND THEN
966 IF (l_debug = 1) THEN
967 trace('Can not find character set: '||l_character_set, TRACE_PROMPT, TRACE_LEVEL);
968 END IF;
969 l_xml_encoding := NULL;
970 ELSE
971 IF (l_debug = 1) THEN
972 trace('Found xml encoding: '||l_xml_encoding, TRACE_PROMPT, TRACE_LEVEL);
973 END IF;
974 END IF;
975
976 IF l_xml_encoding IS NULL THEN
977 l_xml_encoding := G_DEFAULT_XML_ENCODING;
978 END IF;
979 G_XML_ENCODING := l_xml_encoding;
980
981 END IF;
982
983 IF (l_debug = 1) THEN
984 trace('New charac set and encoding: '||l_character_set|| ','||l_xml_encoding, TRACE_PROMPT, TRACE_LEVEL);
985 END IF;
986
987 XML_HEADER1 := '<?xml version="1.0" encoding="' || l_xml_encoding || XML_HEADER1_2;
988 EXCEPTION
989 WHEN others THEN
990 IF (l_debug = 1) THEN
991 trace('Error in get_xml_encoding, return default UTF-8', TRACE_PROMPT, TRACE_LEVEL);
992 END IF;
993 XML_HEADER1 := '<?xml version="1.0" encoding="' || G_DEFAULT_XML_ENCODING || XML_HEADER1_2;
994 END get_xml_encoding;
995
996 /******************************
997 * Get Profile values
998 *****************************/
999 PROCEDURE get_profile_values IS
1000 l_print_mode VARCHAR2(10);
1001 l_file_prefix VARCHAR2(100);
1002 l_output_dir VARCHAR2(200);
1003 l_date_mask VARCHAR2(100);
1004 BEGIN
1005 FND_PROFILE.GET('WMS_PRINT_MODE', l_print_mode);
1006 IF (l_debug = 1) THEN
1007 trace('l_print_mode => ' || l_print_mode, TRACE_PROMPT, TRACE_LEVEL);
1008 END IF;
1009
1010 FND_PROFILE.GET('WMS_LABEL_FILE_PREFIX', l_file_prefix);
1011 IF (l_debug = 1) THEN
1012 trace('l_file_prefix => ' || l_file_prefix, TRACE_PROMPT, TRACE_LEVEL);
1013 END IF;
1014
1015 FND_PROFILE.GET('WMS_LABEL_OUTPUT_DIRECTORY', l_output_dir);
1016 IF (l_debug = 1) THEN
1017 trace('l_output_dir => ' || l_output_dir, TRACE_PROMPT, TRACE_LEVEL);
1018 END IF;
1019
1020 FND_PROFILE.GET('ICX_DATE_FORMAT_MASK',l_date_mask);
1021 IF (l_debug = 1) THEN
1022 trace('l_date_mask => ' || l_date_mask, TRACE_PROMPT, TRACE_LEVEL);
1023 END IF;
1024
1025 G_PROFILE_PRINT_MODE := to_number(l_print_mode);
1026 G_PROFILE_PREFIX := l_file_prefix;
1027 G_PROFILE_OUT_DIR := l_output_dir;
1028 G_DATE_FORMAT_MASK := l_date_mask;
1029 EXCEPTION
1030 WHEN others THEN
1031 IF (l_debug = 1) THEN
1032 trace('Error in get_profile_values', TRACE_PROMPT, TRACE_LEVEL);
1033 trace('ERROR CODE = ' || SQLCODE, TRACE_PROMPT, TRACE_LEVEL);
1034 trace('ERROR MESSAGE = ' || SQLERRM, TRACE_PROMPT, TRACE_LEVEL);
1035 END IF;
1036 END get_profile_values;
1037
1038
1039 /***************************************************
1040 * Update the label content
1041 * with the new job name, printer name, no of copies
1042 ***************************************************/
1043 FUNCTION update_label_content(
1044 p_label_content LONG
1045 , p_job_name VARCHAR2
1046 , p_printer_name VARCHAR2
1047 , p_no_of_copy NUMBER) RETURN LONG IS
1048
1049
1050 l_index NUMBER;
1051 l_begin_index NUMBER;
1052 l_end_index NUMBER;
1053
1054 l_begin_index_label NUMBER;
1055 l_end_index_label NUMBER;
1056 l_begin_index_pq NUMBER;
1057 l_end_index_pq NUMBER;
1058
1059 l_label_str VARCHAR2(1000);
1060 l_new_label_str VARCHAR2(1000);
1061 l_label_content LONG := '';
1062
1063 BEGIN
1064
1065 IF (p_job_name IS NULL) AND (p_printer_name IS NULL) AND (p_no_of_copy IS NULL) THEN
1066 -- no more change needed
1067 RETURN p_label_content;
1068 END IF;
1069
1070 --Find the beginning of JOBNAME
1071 l_index := instr(p_label_content, 'JOBNAME=');
1072 IF l_index = 0 THEN
1073 IF (l_debug = 1) THEN
1074 trace('Can not find JOBNAME, return', TRACE_PROMPT, TRACE_LEVEL);
1075 END IF;
1076 RETURN p_label_content;
1077 END IF;
1078
1079 --Find the first " and second "
1080 l_begin_index := instr(p_label_content, '"', l_index, 1);
1081 l_end_index := instr(p_label_content, '"', l_index, 2);
1082
1083 IF p_job_name IS NOT NULL THEN
1084 l_label_content := substr(p_label_content, 1, l_begin_index) || p_job_name;
1085 ELSE
1086 l_label_content := substr(p_label_content, 1, l_begin_index)
1087 || substr(p_label_content, l_begin_index+1, l_end_index-l_begin_index-1);
1088 END IF;
1089
1090
1091 IF (p_printer_name IS NULL) AND (p_no_of_copy IS NULL) THEN
1092 -- no more change needed
1093 l_label_content := l_label_content || substr(p_label_content, l_end_index);
1094 RETURN l_label_content;
1095 END IF;
1096
1097 --Find <label, starting from l_end_index
1098 l_begin_index_label := instr(p_label_content, '<label', l_end_index, 1);
1099 l_end_index_label := instr(p_label_content, '>', l_begin_index_label, 1);
1100 --Get the string of <label ....., with out the '>'
1101 l_label_str := substr(p_label_content, l_begin_index_label, l_end_index_label-l_begin_index_label);
1102
1103 IF p_printer_name IS NOT NULL THEN
1104 --Find the _PRINTERNAME part
1105 l_index := instr(l_label_str, 'PRINTERNAME', 1, 1);
1106 IF l_index = 0 THEN
1107 l_new_label_str := l_label_str || ' _PRINTERNAME="'||p_printer_name||'"';
1108 ELSE
1109 --found printername, replace
1110 l_begin_index_pq := instr(l_label_str, '"', l_index, 1);
1111 l_end_index_pq := instr(l_label_str, '"', l_index, 2);
1112 l_new_label_str := substr(l_label_str, 1, l_begin_index_pq) || p_printer_name
1113 ||substr(l_label_str, l_end_index_pq);
1114 END IF;
1115 l_label_str := l_new_label_str;
1116 END IF;
1117
1118 IF p_no_of_copy IS NOT NULL THEN
1119 --Find the _PRINTERNAME part
1120 l_index := instr(l_label_str, 'QUANTITY', 1, 1);
1121 IF l_index = 0 THEN
1122 l_new_label_str := l_label_str || ' _QUANTITY="'||p_no_of_copy||'"';
1123 ELSE
1124 --found printername, replace
1125 l_begin_index_pq := instr(l_label_str, '"', l_index, 1);
1126 l_end_index_pq := instr(l_label_str, '"', l_index, 2);
1127 l_new_label_str := substr(l_label_str, 1, l_begin_index_pq) || p_no_of_copy
1128 ||substr(l_label_str, l_end_index_pq);
1129 END IF;
1130 l_label_str := l_new_label_str;
1131 END IF;
1132
1133 l_label_content := l_label_content
1134 || substr(p_label_content, l_end_index, l_begin_index_label-l_end_index)
1135 || l_label_str
1136 || substr(p_label_content, l_end_index_label);
1137
1138 RETURN l_label_content;
1139
1140 EXCEPTION
1141 WHEN others THEN
1142 IF (l_debug = 1) THEN
1143 trace('Error in updating label content, return the same', TRACE_PROMPT, TRACE_LEVEL);
1144 trace('ERROR CODE = ' || SQLCODE, TRACE_PROMPT, TRACE_LEVEL);
1145 trace('ERROR MESSAGE = ' || SQLERRM, TRACE_PROMPT, TRACE_LEVEL);
1146 END IF;
1147 RETURN p_label_content;
1148 END update_label_content;
1149 /*-----------------------------------------
1150 * PUBLIC API
1151 *---------------------------------------*/
1152 PROCEDURE trace(p_message IN VARCHAR2,
1153 p_prompt IN VARCHAR2,
1154 p_level IN NUMBER ) IS
1155 BEGIN
1156 INV_LOG_UTIL.trace(p_message, p_prompt, p_level);
1157
1158 END trace;
1159
1160 /**************************************
1161 * Get default format for a label type
1162 **************************************/
1163 PROCEDURE GET_DEFAULT_FORMAT
1164 (p_label_type_id IN number,
1165 p_label_format OUT NOCOPY VARCHAR2,
1166 p_label_format_id OUT NOCOPY NUMBER
1167 ) IS
1168 BEGIN
1169 SELECT label_format_id,label_format_name
1170 INTO p_label_format_id,p_label_format FROM WMS_LABEL_FORMATS
1171 WHERE document_id = p_label_type_id
1172 AND default_format_flag = 'Y';
1173 IF SQL%NOTFOUND THEN
1174 p_label_format_id := null;
1175 p_label_format := null;
1176
1177 END IF;
1178 EXCEPTION
1179 when no_data_found then
1180 p_label_format_id := null;
1181 p_label_format := null;
1182 when others then
1183 p_label_format_id := null;
1184 p_label_format := null;
1185 END GET_DEFAULT_FORMAT;
1186
1187
1188 /*****************************************************
1189 * API to get the label fields defined for a specific
1190 * format. This get called from the individual label
1191 * API's often.
1192 *****************************************************/
1193 PROCEDURE GET_VARIABLES_FOR_FORMAT(
1194 x_variables OUT NOCOPY label_field_variable_tbl_type
1195 , x_variables_count OUT NOCOPY NUMBER
1196 , p_format_id IN NUMBER
1197 ) IS
1198 l_is_variable_exist VARCHAR2(1);
1199 BEGIN
1200 GET_VARIABLES_FOR_FORMAT(
1201 x_variables => x_variables
1202 , x_variables_count => x_variables_count
1203 , x_is_variable_exist => l_is_variable_exist
1204 , p_format_id => p_format_id
1205 , p_exist_variable_name => null);
1206
1207 END GET_VARIABLES_FOR_FORMAT;
1208 /******************************************************
1209 * Overloaded procedure GET_VARIABLES_FOR_FORMAT
1210 * Also it can check whether a given variable is included
1211 * in the given format
1212 * p_exist_variable_name has the name of the variable
1213 * that will be checked for existence
1214 * x_is_variable_exist returns whether the given variable exists
1215 * possible value is 'Y' or 'N'
1216 *******************************************************/
1217 PROCEDURE GET_VARIABLES_FOR_FORMAT(
1218 x_variables OUT NOCOPY label_field_variable_tbl_type
1219 , x_variables_count OUT NOCOPY NUMBER
1220 , x_is_variable_exist OUT NOCOPY VARCHAR2
1221 , p_format_id IN NUMBER
1222 , p_exist_variable_name IN VARCHAR2 DEFAULT NULL
1223 ) IS
1224
1225 ---------------------------------------------------------------------------------------------
1226 -- Project: 'Custom Labels' (A 11i10+ Project) |
1227 -- Author: Dinesh ([email protected]) |
1228 -- Change Description: |
1229 -- 1: Included sql_Stmt to cursor c_variable cursor to fetch the 'Custom Query' |
1230 -- 2: Added code in the loop to include sql_stmt also. |
1231
1232 -- R12 RFID PROJECT : p_exist_variable_name is matched against
1233 -- v_variables.column_name now
1234 ---------------------------------------------------------------------------------------------
1235 CURSOR c_variable IS
1236 select wlfv.label_field_id field_id,
1237 wlfv.field_variable_name variable_name,
1238 wlf.column_name column_name,
1239 wlf.sql_stmt
1240 from wms_label_field_variables wlfv, wms_label_fields_b wlf
1241 where wlfv.label_field_id = wlf.label_field_id
1242 and wlfv.label_format_id = p_format_id
1243 order by wlf.column_name;
1244
1245 i NUMBER;
1246
1247 BEGIN
1248 x_is_variable_exist := 'N';
1249 x_variables_count := 0;
1250 i := 1;
1251 FOR v_variables IN c_variable LOOP
1252 x_variables(i).label_field_id := v_variables.field_id;
1253 x_variables(i).variable_name := v_variables.variable_name;
1254 x_variables(i).column_name := v_variables.column_name;
1255 x_variables(i).sql_stmt := v_variables.sql_stmt;
1256 i := i+1;
1257 IF p_exist_variable_name IS NOT NULL AND
1258 UPPER(v_variables.column_name) = UPPER(p_exist_variable_name) THEN
1259 x_is_variable_exist := 'Y';
1260 END IF;
1261 END LOOP;
1262
1263 x_variables_count := i - 1 ;
1264
1265 EXCEPTION
1266 WHEN others THEN
1267 IF (l_debug = 1) THEN
1268 trace('Error in GET_VARIABLES_FOR_FORMAT', TRACE_PROMPT, TRACE_LEVEL);
1269 trace('ERROR CODE = ' || SQLCODE, TRACE_PROMPT, TRACE_LEVEL);
1270 trace('ERROR MESSAGE = ' || SQLERRM, TRACE_PROMPT, TRACE_LEVEL);
1271 END IF;
1272 END GET_VARIABLES_FOR_FORMAT;
1273
1274 PROCEDURE write_xml_header(label_type_header label_type_rec, p_label_request_id NUMBER) IS
1275 l_file_prefix VARCHAR2(100);
1276 l_job_name VARCHAR2(200);
1277 l_request_id NUMBER;
1278 BEGIN
1279 g_xml_header := XML_HEADER1 ||XML_HEADER2|| LABELS_B;
1280 IF nvl(label_type_header.manual_format_name, label_type_header.default_format_name) IS NOT NULL THEN
1281 g_xml_header := g_xml_header || ' _FORMAT="' || nvl(label_type_header.manual_format_name, label_type_header.default_format_name) || '"';
1282 END IF;
1283 IF label_type_header.default_no_of_copies IS NOT NULL then
1284 g_xml_header := g_xml_header||' _QUANTITY="'||label_type_header.default_no_of_copies||'"';
1285 END IF;
1286 IF nvl(label_type_header.manual_printer,label_type_header.default_printer) IS NOT NULL then
1287 g_xml_header := g_xml_header||' _PRINTERNAME="'|| nvl(label_type_header.manual_printer,label_type_header.default_printer) ||'"';
1288 END IF;
1289
1290 l_request_id := p_label_request_id;
1291
1292 l_job_name := G_PROFILE_PREFIX || l_request_id;
1293
1294 IF (l_debug = 1) THEN
1295 trace('l_request_id:'||l_request_id, TRACE_PROMPT, TRACE_LEVEL);
1296 END IF;
1297
1298 IF l_job_name IS NOT NULL THEN
1299 g_xml_header := g_xml_header||' _JOBNAME="'||l_job_name||'"';
1300 END IF;
1301 g_xml_header := g_xml_header||TAG_E;
1302 EXCEPTION
1303 WHEN others THEN
1304 IF (l_debug = 1) THEN
1305 trace('Error in write_xml_header', TRACE_PROMPT, TRACE_LEVEL);
1306 trace('ERROR CODE = ' || SQLCODE, TRACE_PROMPT, TRACE_LEVEL);
1307 trace('ERROR MESSAGE = ' || SQLERRM, TRACE_PROMPT, TRACE_LEVEL);
1308 END IF;
1309 END write_xml_header;
1310
1311
1312 -- This procedure is to write label xml data into LABEL_XML.log
1313 -- This is not used anymore from Patchset I (11.5.9) because
1314 -- the label data will be inserted into WMS_LABEL_REQUESTS_HIST table
1315 PROCEDURE writeIntoFile(xml LONG) IS
1316 l_dir VARCHAR2(128);
1317 l_debug_trace NUMBER;
1318 l_file_name VARCHAR2(100) := 'LABEL_XML.log';
1319 l_dir_seperator VARCHAR2(1);
1320
1321 l_length NUMBER;
1322 l_file UTL_FILE.FILE_TYPE;
1323 l_substr VARCHAR2(254);
1324 i NUMBER;
1325 l_last_index NUMBER;
1326 l_cur_index NUMBER;
1327
1328 BEGIN
1329 -- Bug 2449358 : LABELING: LABEL_XML.LOG SHOULD ONLY BE CREATED IF DEBUG IS ON
1330 -- The fnd_profile.get, gets the value for the
1331 -- input('INV_DEBUG_TRACE') based on the current fnd_global.user_id.
1332 -- According to the new design, the LABEL_XML.log will only be generated if the profile option
1333 -- INV: Debug Trace is set to 'Yes'. Hence the IF clause around the existing code.
1334
1335 -- Get INV_DEBUG_TRACE profile for the current user.
1336 fnd_profile.get('INV_DEBUG_TRACE',l_debug_trace);
1337
1338 --l_debug_trace := fnd_profile.value_specific('INV_DEBUG_TRACE',fnd_global.user_id);
1339 IF (l_debug = 1) THEN
1340 trace('INV_DEBUG_TRACE = ' || l_debug_trace, TRACE_PROMPT, TRACE_LEVEL);
1341 END IF;
1342
1343 IF (l_debug_trace = 1) THEN
1344 -- l_debug_trace = 1 means 'Yes' and 2 means 'No'
1345
1346 --Get directory from INV_DEBUG_FILE profile
1347 fnd_profile.get('INV_DEBUG_FILE',l_dir);
1348 --l_dir := fnd_profile.value_specific('INV_DEBUG_FILE',fnd_global.user_id);
1349 IF (l_debug = 1) THEN
1350 trace('INV_DEBUG_FILE = ' || l_dir, TRACE_PROMPT, TRACE_LEVEL);
1351 END IF;
1352
1353 -- Seperate the filename from the directory
1354 l_dir_seperator := '/';
1355 -- The following IF checks if the directory patch contains a '/'(forward slash) or a
1356 -- '\' (backward slash). The forward slash is the directory separator for Unix while
1357 -- the backward slash is for NT.
1358 -- Bug 2695116 : "instr(l_dir, l_dir_seperator) = 0" means that the directory is an NT
1359 -- directory. Removed hardcoded instances of '/' and replaced with "l_dir_seperator".
1360 IF(instr(l_dir, l_dir_seperator) = 0) THEN
1361 l_dir_seperator := '\';
1362 END IF;
1363 l_dir := substr(l_dir, 1, instr(l_dir, l_dir_seperator, -1, 1)-1);
1364
1365 --Write XML string to file
1366 l_length := length(xml);
1367 l_file := UTL_FILE.fopen(rtrim(l_dir,l_dir_seperator), l_file_name, 'a');
1368 utl_file.put_line(l_file, 'LABEL_XML *** New Session. : '|| To_char(Sysdate, 'DD-MON-YY HH:MI:SS') ||' ***');
1369
1370
1371 l_last_index :=1;
1372 l_cur_index := instr(xml, '>', l_last_index);
1373
1374 WHILE l_cur_index <> 0 LOOP
1375 l_substr := substr(xml, l_last_index, l_cur_index-l_last_index+1);
1376 utl_file.put_line(l_file, l_substr);
1377 l_last_index := l_cur_index + 1;
1378 IF(substr(xml, l_last_index+1,1) = 'v') THEN
1379 l_cur_index := instr(xml, '>', l_last_index, 2);
1380 ELSE
1381 l_cur_index := instr(xml, '>', l_last_index, 1);
1382 END IF;
1383 END LOOP;
1384 utl_file.fclose(l_file);
1385 ELSE
1386 NULL;
1387 END IF;
1388 EXCEPTION
1389 WHEN utl_file.invalid_path THEN
1390 IF (l_debug = 1) THEN
1391 trace(' Invalid path in ' || G_PKG_NAME||'.WriteIntoFile, can not write into file', TRACE_PROMPT, TRACE_LEVEL);
1392 END IF;
1393 IF (utl_file.IS_OPEN(l_file)) THEN
1394 utl_file.fclose(l_file);
1395 END IF;
1396 IF (l_debug = 1) THEN
1397 trace('ERROR CODE = ' || SQLCODE, TRACE_PROMPT, TRACE_LEVEL);
1398 trace('ERROR MESSAGE = ' || SQLERRM, TRACE_PROMPT, TRACE_LEVEL);
1399 END IF;
1400
1401 WHEN fnd_api.g_exc_error THEN
1402 IF (l_debug = 1) THEN
1403 trace(' Expected Error In '|| G_PKG_NAME||'.WriteIntoFile', TRACE_PROMPT, TRACE_LEVEL);
1404 END IF;
1405 IF (utl_file.IS_OPEN(l_file)) THEN
1406 utl_file.fclose(l_file);
1407 END IF;
1408 IF (l_debug = 1) THEN
1409 trace('ERROR CODE = ' || SQLCODE, TRACE_PROMPT, TRACE_LEVEL);
1410 trace('ERROR MESSAGE = ' || SQLERRM, TRACE_PROMPT, TRACE_LEVEL);
1411 END IF;
1412
1413 WHEN fnd_api.g_exc_unexpected_error THEN
1414 IF (l_debug = 1) THEN
1415 trace(' Unexpected Error In '|| G_PKG_NAME||'.WriteIntoFile', TRACE_PROMPT, TRACE_LEVEL);
1416 END IF;
1417 IF (utl_file.IS_OPEN(l_file)) THEN
1418 utl_file.fclose(l_file);
1419 END IF;
1420 IF (l_debug = 1) THEN
1421 trace('ERROR CODE = ' || SQLCODE, TRACE_PROMPT, TRACE_LEVEL);
1422 trace('ERROR MESSAGE = ' || SQLERRM, TRACE_PROMPT, TRACE_LEVEL);
1423 END IF;
1424
1425 WHEN others THEN
1426 IF (l_debug = 1) THEN
1427 trace(' Other Error In '|| G_PKG_NAME||'.WriteIntoFile', TRACE_PROMPT, TRACE_LEVEL);
1428 END IF;
1429 IF (utl_file.IS_OPEN(l_file)) THEN
1430 utl_file.fclose(l_file);
1431 END IF;
1432 IF (l_debug = 1) THEN
1433 trace('ERROR CODE = ' || SQLCODE, TRACE_PROMPT, TRACE_LEVEL);
1434 trace('ERROR MESSAGE = ' || SQLERRM, TRACE_PROMPT, TRACE_LEVEL);
1435 END IF;
1436
1437 END writeIntoFile;
1438
1439 /*************************************
1440 * Insert into WMS_LABEL_REQUESTS_HIST
1441 ************************************/
1442 PROCEDURE insert_history_record(p_history_rec WMS_LABEL_REQUESTS_HIST%ROWTYPE) IS
1443 PRAGMA AUTONOMOUS_TRANSACTION;
1444 BEGIN
1445 INSERT INTO wms_label_requests_hist
1446 ( label_request_id,
1447 LABEL_TYPE_ID ,
1448 LABEL_FORMAT_ID,
1449 ORGANIZATION_ID,
1450 INVENTORY_ITEM_ID,
1451 SUBINVENTORY_CODE,
1452 LOCATOR_ID ,
1453 LOT_NUMBER ,
1454 REVISION ,
1455 SERIAL_NUMBER ,
1456 LPN_ID ,
1457 SUPPLIER_ID ,
1458 SUPPLIER_SITE_ID ,
1459 SUPPLIER_ITEM_ID ,
1460 CUSTOMER_ID ,
1461 CUSTOMER_SITE_ID ,
1462 CUSTOMER_ITEM_ID ,
1463 CUSTOMER_CONTACT_ID ,
1464 FREIGHT_CODE ,
1465 LAST_UPDATE_DATE ,
1466 LAST_UPDATED_BY ,
1467 CREATION_DATE ,
1468 CREATED_BY ,
1469 LAST_UPDATE_LOGIN ,
1470 REQUEST_ID ,
1471 PROGRAM_APPLICATION_ID,
1472 PROGRAM_ID ,
1473 PROGRAM_UPDATE_DATE ,
1474 ATTRIBUTE_CATEGORY ,
1475 ATTRIBUTE1 ,
1476 ATTRIBUTE2 ,
1477 ATTRIBUTE3 ,
1478 ATTRIBUTE4 ,
1479 ATTRIBUTE5 ,
1480 ATTRIBUTE6 ,
1481 ATTRIBUTE7 ,
1482 ATTRIBUTE8 ,
1483 ATTRIBUTE9 ,
1484 ATTRIBUTE10 ,
1485 ATTRIBUTE11 ,
1486 ATTRIBUTE12 ,
1487 ATTRIBUTE13 ,
1488 ATTRIBUTE14 ,
1489 ATTRIBUTE15 ,
1490 PRINTER_NAME ,
1491 DELIVERY_ID ,
1492 BUSINESS_FLOW_CODE ,
1493 PACKAGE_ID ,
1494 DELIVERY_DETAIL_ID,
1495 SALES_ORDER_HEADER_ID,
1496 SALES_ORDER_LINE_ID,
1497 RULE_ID,
1498 RULE_WEIGHT,
1499 STRATEGY_ID,
1500 LABEL_CONTENT,
1501 JOB_NAME,
1502 REQUEST_MODE_CODE,
1503 REQUEST_DATE,
1504 REQUEST_USER_ID,
1505 OUTFILE_NAME,
1506 OUTFILE_DIRECTORY,
1507 NO_OF_COPY,
1508 ENCODING,
1509 ORIGINAL_REQUEST_ID,
1510 STATUS_FLAG,
1511 JOB_STATUS,
1512 PRINTER_STATUS,
1513 STATUS_TYPE,
1514 ERROR_MESSAGE
1515 )VALUES
1516 ( p_history_rec.label_request_id,
1517 p_history_rec.LABEL_TYPE_ID ,
1518 p_history_rec.LABEL_FORMAT_ID,
1519 p_history_rec.ORGANIZATION_ID,
1520 p_history_rec.INVENTORY_ITEM_ID,
1521 p_history_rec.SUBINVENTORY_CODE,
1522 p_history_rec.LOCATOR_ID ,
1523 p_history_rec.LOT_NUMBER ,
1524 p_history_rec.REVISION ,
1525 p_history_rec.SERIAL_NUMBER ,
1526 p_history_rec.LPN_ID ,
1527 p_history_rec.SUPPLIER_ID ,
1528 p_history_rec.SUPPLIER_SITE_ID ,
1529 p_history_rec.SUPPLIER_ITEM_ID ,
1530 p_history_rec.CUSTOMER_ID ,
1531 p_history_rec.CUSTOMER_SITE_ID ,
1532 p_history_rec.CUSTOMER_ITEM_ID ,
1533 p_history_rec.CUSTOMER_CONTACT_ID ,
1534 p_history_rec.FREIGHT_CODE ,
1535 p_history_rec.LAST_UPDATE_DATE ,
1536 p_history_rec.LAST_UPDATED_BY ,
1537 p_history_rec.CREATION_DATE ,
1538 p_history_rec.CREATED_BY ,
1539 p_history_rec.LAST_UPDATE_LOGIN ,
1540 p_history_rec.REQUEST_ID ,
1541 p_history_rec.PROGRAM_APPLICATION_ID,
1542 p_history_rec.PROGRAM_ID ,
1543 p_history_rec.PROGRAM_UPDATE_DATE ,
1544 p_history_rec.ATTRIBUTE_CATEGORY ,
1545 p_history_rec.ATTRIBUTE1 ,
1546 p_history_rec.ATTRIBUTE2 ,
1547 p_history_rec.ATTRIBUTE3 ,
1548 p_history_rec.ATTRIBUTE4 ,
1549 p_history_rec.ATTRIBUTE5 ,
1550 p_history_rec.ATTRIBUTE6 ,
1551 p_history_rec.ATTRIBUTE7 ,
1552 p_history_rec.ATTRIBUTE8 ,
1553 p_history_rec.ATTRIBUTE9 ,
1554 p_history_rec.ATTRIBUTE10 ,
1555 p_history_rec.ATTRIBUTE11 ,
1556 p_history_rec.ATTRIBUTE12 ,
1557 p_history_rec.ATTRIBUTE13 ,
1558 p_history_rec.ATTRIBUTE14 ,
1559 p_history_rec.ATTRIBUTE15 ,
1560 p_history_rec.PRINTER_NAME ,
1561 p_history_rec.DELIVERY_ID ,
1562 p_history_rec.BUSINESS_FLOW_CODE ,
1563 p_history_rec.PACKAGE_ID ,
1564 p_history_rec.DELIVERY_DETAIL_ID,
1565 p_history_rec.SALES_ORDER_HEADER_ID,
1566 p_history_rec.SALES_ORDER_LINE_ID,
1567 p_history_rec.RULE_ID,
1568 p_history_rec.RULE_WEIGHT,
1569 p_history_rec.STRATEGY_ID,
1570 p_history_rec.LABEL_CONTENT,
1571 p_history_rec.JOB_NAME,
1572 p_history_rec.REQUEST_MODE_CODE,
1573 p_history_rec.REQUEST_DATE,
1574 p_history_rec.REQUEST_USER_ID,
1575 p_history_rec.OUTFILE_NAME,
1576 p_history_rec.OUTFILE_DIRECTORY,
1577 p_history_rec.NO_OF_COPY,
1578 p_history_rec.ENCODING,
1579 p_history_rec.ORIGINAL_REQUEST_ID,
1580 p_history_rec.STATUS_FLAG,
1581 p_history_rec.JOB_STATUS,
1582 p_history_rec.PRINTER_STATUS,
1583 p_history_rec.STATUS_TYPE,
1584 p_history_rec.ERROR_MESSAGE
1585 );
1586
1587 COMMIT;
1588 EXCEPTION
1589 WHEN others THEN
1590 IF (l_debug = 1) THEN
1591 trace('Error in inserting into WMS_LABEL_REQUESTS_HIST record, Req ID:'|| p_history_rec.label_request_id,TRACE_PROMPT, TRACE_LEVEL);
1592 END IF;
1593 END insert_history_record;
1594
1595 /************************************
1596 * Populate Label Requests History
1597 ************************************/
1598 PROCEDURE populate_history_record(
1599 p_label_type_info label_type_rec
1600 , p_label_content LONG
1601 , p_label_request_id NUMBER
1602 , p_status_flag VARCHAR2 DEFAULT 'S'
1603 , p_error_message VARCHAR2 DEFAULT NULL
1604 ) IS
1605
1606 l_label_req_rec WMS_LABEL_REQUESTS%ROWTYPE;
1607 CURSOR label_req_rec IS
1608 SELECT * FROM WMS_LABEL_REQUESTS
1609 WHERE label_request_id = p_label_request_id;
1610
1611 l_hist_rec WMS_LABEL_REQUESTS_HIST%ROWTYPE;
1612
1613 l_job_name VARCHAR2(150) := NULL;
1614
1615 BEGIN
1616 IF (l_debug = 1) THEN
1617 trace(' Populate History Record ReqID='||p_label_request_id, TRACE_PROMPT, TRACE_LEVEL);
1618 END IF;
1619 -- Retrieve WMS_LABEL_REQUESTS record and copy information to history record
1620 OPEN label_req_rec;
1621 FETCH label_req_rec INTO l_label_req_rec;
1622 IF label_req_rec%NOTFOUND THEN
1623 IF (l_debug = 1) THEN
1624 trace('Fail to retrieve record from WMS_LABEL_REQUESTS with ID '||p_label_request_id , TRACE_PROMPT, TRACE_LEVEL);
1625 END IF;
1626 CLOSE label_req_rec;
1627 RETURN;
1628 END IF;
1629 CLOSE label_req_rec;
1630
1631 -- Retrieve other information
1632 l_job_name := G_PROFILE_PREFIX || p_label_request_id;
1633
1634 l_hist_rec.label_request_id:= l_label_req_rec.label_request_id;
1635 l_hist_rec.LABEL_TYPE_ID :=l_label_req_rec.DOCUMENT_ID ;
1636 l_hist_rec.LABEL_FORMAT_ID:=nvl(l_label_req_rec.LABEL_FORMAT_ID, p_label_type_info.default_format_id);
1637 l_hist_rec.ORGANIZATION_ID:=l_label_req_rec.ORGANIZATION_ID;
1638 l_hist_rec.INVENTORY_ITEM_ID:=l_label_req_rec.INVENTORY_ITEM_ID;
1639 l_hist_rec.SUBINVENTORY_CODE:=l_label_req_rec.SUBINVENTORY_CODE;
1640 l_hist_rec.LOCATOR_ID :=l_label_req_rec.LOCATOR_ID ;
1641 l_hist_rec.LOT_NUMBER :=l_label_req_rec.LOT_NUMBER ;
1642 l_hist_rec.REVISION :=l_label_req_rec.REVISION ;
1643 l_hist_rec.SERIAL_NUMBER :=l_label_req_rec.SERIAL_NUMBER ;
1644 l_hist_rec.LPN_ID :=l_label_req_rec.LPN_ID ;
1645 l_hist_rec.SUPPLIER_ID :=l_label_req_rec.SUPPLIER_ID ;
1646 l_hist_rec.SUPPLIER_SITE_ID :=l_label_req_rec.SUPPLIER_SITE_ID ;
1647 l_hist_rec.SUPPLIER_ITEM_ID :=l_label_req_rec.SUPPLIER_ITEM_ID ;
1648 l_hist_rec.CUSTOMER_ID :=l_label_req_rec.CUSTOMER_ID ;
1649 l_hist_rec.CUSTOMER_SITE_ID :=l_label_req_rec.CUSTOMER_SITE_ID ;
1650 l_hist_rec.CUSTOMER_ITEM_ID :=l_label_req_rec.CUSTOMER_ITEM_ID ;
1651 l_hist_rec.CUSTOMER_CONTACT_ID :=l_label_req_rec.CUSTOMER_CONTACT_ID ;
1652 l_hist_rec.FREIGHT_CODE :=l_label_req_rec.FREIGHT_CODE ;
1653 l_hist_rec.LAST_UPDATE_DATE :=l_label_req_rec.LAST_UPDATE_DATE ;
1654 l_hist_rec.LAST_UPDATED_BY :=l_label_req_rec.LAST_UPDATED_BY ;
1655 l_hist_rec.CREATION_DATE :=l_label_req_rec.CREATION_DATE ;
1656 l_hist_rec.CREATED_BY :=l_label_req_rec.CREATED_BY ;
1657 l_hist_rec.LAST_UPDATE_LOGIN :=l_label_req_rec.LAST_UPDATE_LOGIN ;
1658 l_hist_rec.REQUEST_ID :=l_label_req_rec.REQUEST_ID ;
1659 l_hist_rec.PROGRAM_APPLICATION_ID:=l_label_req_rec.PROGRAM_APPLICATION_ID;
1660 l_hist_rec.PROGRAM_ID :=l_label_req_rec.PROGRAM_ID ;
1661 l_hist_rec.PROGRAM_UPDATE_DATE :=l_label_req_rec.PROGRAM_UPDATE_DATE ;
1662 l_hist_rec.ATTRIBUTE_CATEGORY :=l_label_req_rec.ATTRIBUTE_CATEGORY ;
1663 l_hist_rec.ATTRIBUTE1 :=l_label_req_rec.ATTRIBUTE1 ;
1664 l_hist_rec.ATTRIBUTE2 :=l_label_req_rec.ATTRIBUTE2 ;
1665 l_hist_rec.ATTRIBUTE3 :=l_label_req_rec.ATTRIBUTE3 ;
1666 l_hist_rec.ATTRIBUTE4 :=l_label_req_rec.ATTRIBUTE4 ;
1667 l_hist_rec.ATTRIBUTE5 :=l_label_req_rec.ATTRIBUTE5 ;
1668 l_hist_rec.ATTRIBUTE6 :=l_label_req_rec.ATTRIBUTE6 ;
1669 l_hist_rec.ATTRIBUTE7 :=l_label_req_rec.ATTRIBUTE7 ;
1670 l_hist_rec.ATTRIBUTE8 :=l_label_req_rec.ATTRIBUTE8 ;
1671 l_hist_rec.ATTRIBUTE9 :=l_label_req_rec.ATTRIBUTE9 ;
1672 l_hist_rec.ATTRIBUTE10 :=l_label_req_rec.ATTRIBUTE10 ;
1673 l_hist_rec.ATTRIBUTE11 :=l_label_req_rec.ATTRIBUTE11 ;
1674 l_hist_rec.ATTRIBUTE12 :=l_label_req_rec.ATTRIBUTE12 ;
1675 l_hist_rec.ATTRIBUTE13 :=l_label_req_rec.ATTRIBUTE13 ;
1676 l_hist_rec.ATTRIBUTE14 :=l_label_req_rec.ATTRIBUTE14 ;
1677 l_hist_rec.ATTRIBUTE15 :=l_label_req_rec.ATTRIBUTE15 ;
1678 l_hist_rec.PRINTER_NAME :=nvl(l_label_req_rec.PRINTER_NAME,p_label_type_info.default_printer);
1679 l_hist_rec.DELIVERY_ID :=l_label_req_rec.DELIVERY_ID ;
1680 l_hist_rec.BUSINESS_FLOW_CODE :=l_label_req_rec.BUSINESS_FLOW_CODE ;
1681 l_hist_rec.PACKAGE_ID :=l_label_req_rec.package_id ;
1682 l_hist_rec.DELIVERY_DETAIL_ID :=l_label_req_rec.delivery_detail_id;
1683 l_hist_rec.SALES_ORDER_HEADER_ID := l_label_req_rec.sales_order_header_id;
1684 l_hist_rec.SALES_ORDER_LINE_ID :=l_label_req_rec.sales_order_line_id;
1685 l_hist_rec.RULE_ID :=l_label_req_rec.RULE_ID;
1686 l_hist_rec.RULE_WEIGHT :=l_label_req_rec.RULE_WEIGHT;
1687 l_hist_rec.STRATEGY_ID :=l_label_req_rec.STRATEGY_ID;
1688 l_hist_rec.LABEL_CONTENT :=p_label_content;
1689 l_hist_rec.JOB_NAME :=l_job_name;
1690 l_hist_rec.REQUEST_MODE_CODE :=G_PROFILE_PRINT_MODE;
1691 l_hist_rec.REQUEST_DATE :=sysdate;
1692 l_hist_rec.REQUEST_USER_ID :=fnd_global.user_id;
1693 l_hist_rec.OUTFILE_NAME :=null;
1694 l_hist_rec.OUTFILE_DIRECTORY :=null;
1695 l_hist_rec.NO_OF_COPY :=p_label_type_info.default_no_of_copies;
1696 l_hist_rec.ENCODING :=G_XML_ENCODING;
1697 l_hist_rec.STATUS_FLAG :=nvl(p_status_flag, 'S');
1698 l_hist_rec.JOB_STATUS :=NULL;
1699 l_hist_rec.PRINTER_STATUS :=NULL;
1700 l_hist_rec.STATUS_TYPE :=NULL;
1701 l_hist_rec.ERROR_MESSAGE :=p_error_message;
1702
1703 insert_history_record(l_hist_rec);
1704 IF (l_debug = 1) THEN
1705 trace('Record Inserted to WDRH', TRACE_PROMPT, TRACE_LEVEL);
1706 END IF;
1707 EXCEPTION
1708 WHEN others THEN
1709 IF (l_debug = 1) THEN
1710 trace('Other Error in populate_history_record', TRACE_PROMPT, TRACE_LEVEL);
1711 trace('ERROR CODE = ' || SQLCODE, TRACE_PROMPT, TRACE_LEVEL);
1712 trace('ERROR MESSAGE = ' || SQLERRM, TRACE_PROMPT, TRACE_LEVEL);
1713 END IF;
1714
1715 END populate_history_record;
1716
1717 /***************************************
1718 * Update history record
1719 ***************************************/
1720 PROCEDURE update_history_record(
1721 p_label_request_id IN NUMBER
1722 , p_status_flag IN VARCHAR2
1723 , p_job_status IN VARCHAR2
1724 , p_printer_status IN VARCHAR2
1725 , p_status_type IN VARCHAR2
1726 , p_outfile_name IN VARCHAR2
1727 , p_outfile_directory IN VARCHAR2
1728 , p_error_message IN VARCHAR2
1729 ) IS PRAGMA AUTONOMOUS_TRANSACTION;
1730
1731 BEGIN
1732 UPDATE wms_label_requests_hist
1733 SET status_flag = nvl(p_status_flag, status_flag)
1734 , job_status = nvl(p_job_status,job_status)
1735 , printer_status = nvl(p_printer_status, printer_status)
1736 , status_type = nvl(p_status_type, status_type)
1737 , outfile_name = nvl(p_outfile_name, outfile_name)
1738 , outfile_directory = nvl(p_outfile_directory, outfile_directory)
1739 , error_message = nvl(p_error_message, error_message)
1740 WHERE label_request_id = p_label_request_id;
1741 COMMIT;
1742 EXCEPTION
1743 WHEN others THEN
1744 IF (l_debug = 1) THEN
1745 trace('Error in updating history record of '||p_label_request_id, TRACE_PROMPT, TRACE_LEVEL);
1746 trace('ERROR CODE = ' || SQLCODE, TRACE_PROMPT, TRACE_LEVEL);
1747 trace('ERROR MESSAGE = ' || SQLERRM, TRACE_PROMPT, TRACE_LEVEL);
1748 END IF;
1749 END update_history_record;
1750
1751 /*******************************************
1752 * Call the rules engine to get label format
1753 * It only apply for WMS installed
1754 * It will first insert a row into wms_label_requests
1755 * then call the rules engine to get the label format
1756 ******************************************/
1757
1758 PROCEDURE GET_FORMAT_WITH_RULE
1759 (
1760 P_DOCUMENT_ID IN NUMBER,
1761 P_LABEL_FORMAT_ID IN NUMBER ,
1762 P_ORGANIZATION_ID IN NUMBER ,
1763 P_INVENTORY_ITEM_ID IN NUMBER ,
1764 P_SUBINVENTORY_CODE IN VARCHAR2 ,
1765 P_LOCATOR_ID IN NUMBER ,
1766 P_LOT_NUMBER IN VARCHAR2 ,
1767 P_REVISION IN VARCHAR2 ,
1768 P_SERIAL_NUMBER IN VARCHAR2 ,
1769 P_LPN_ID IN NUMBER ,
1770 P_SUPPLIER_ID IN NUMBER ,
1771 P_SUPPLIER_SITE_ID IN NUMBER ,
1772 P_SUPPLIER_ITEM_ID IN NUMBER ,
1773 P_CUSTOMER_ID IN NUMBER ,
1774 P_CUSTOMER_SITE_ID IN NUMBER ,
1775 P_CUSTOMER_ITEM_ID IN NUMBER ,
1776 P_CUSTOMER_CONTACT_ID IN NUMBER ,
1777 P_FREIGHT_CODE IN VARCHAR2 ,
1778 P_LAST_UPDATE_DATE IN DATE,
1779 P_LAST_UPDATED_BY IN NUMBER,
1780 P_CREATION_DATE IN DATE,
1781 P_CREATED_BY IN NUMBER,
1782 P_LAST_UPDATE_LOGIN IN NUMBER ,
1783 P_REQUEST_ID IN NUMBER ,
1784 P_PROGRAM_APPLICATION_ID IN NUMBER ,
1785 P_PROGRAM_ID IN NUMBER ,
1786 P_PROGRAM_UPDATE_DATE IN DATE ,
1787 P_ATTRIBUTE_CATEGORY IN VARCHAR2 ,
1788 P_ATTRIBUTE1 IN VARCHAR2 ,
1789 P_ATTRIBUTE2 IN VARCHAR2 ,
1790 P_ATTRIBUTE3 IN VARCHAR2 ,
1791 P_ATTRIBUTE4 IN VARCHAR2 ,
1792 P_ATTRIBUTE5 IN VARCHAR2 ,
1793 P_ATTRIBUTE6 IN VARCHAR2 ,
1794 P_ATTRIBUTE7 IN VARCHAR2 ,
1795 P_ATTRIBUTE8 IN VARCHAR2 ,
1796 P_ATTRIBUTE9 IN VARCHAR2 ,
1797 P_ATTRIBUTE10 IN VARCHAR2 ,
1798 P_ATTRIBUTE11 IN VARCHAR2 ,
1799 P_ATTRIBUTE12 IN VARCHAR2 ,
1800 P_ATTRIBUTE13 IN VARCHAR2 ,
1801 P_ATTRIBUTE14 IN VARCHAR2 ,
1802 P_ATTRIBUTE15 IN VARCHAR2 ,
1803 P_PRINTER_NAME IN VARCHAR2 ,
1804 P_DELIVERY_ID IN NUMBER ,
1805 P_BUSINESS_FLOW_CODE IN NUMBER ,
1806 P_PACKAGE_ID IN NUMBER ,
1807 p_sales_order_header_id IN NUMBER , -- bug 2326102
1808 p_sales_order_line_id IN NUMBER , -- bug 2326102
1809 p_delivery_detail_id IN NUMBER , -- bug 2326102
1810 p_use_rule_engine IN VARCHAR2,
1811 x_return_status OUT NOCOPY VARCHAR2,
1812 x_label_format_id OUT NOCOPY NUMBER,
1813 x_label_format OUT NOCOPY VARCHAR2,
1814 x_label_request_id OUT NOCOPY NUMBER
1815 )IS
1816
1817 l_wms_installed BOOLEAN := FALSE;
1818 l_return_status VARCHAR2(240);
1819 l_msg_count NUMBER;
1820 l_msg_data VARCHAR2(2000);
1821 l_label_request_id NUMBER;
1822 l_label_type NUMBER;
1823
1824
1825 BEGIN
1826 x_return_status := fnd_api.g_ret_sts_success;
1827 -- If wms is not installed, the rules engine will not
1828 -- be applied
1829 -- Get label Request ID
1830
1831 IF (l_debug = 1) THEN
1832 trace(' **Input Parameter values in the call to the Rules Engine ', TRACE_PROMPT, TRACE_LEVEL);
1833 trace(' **document_id :' || p_document_id, TRACE_PROMPT, TRACE_LEVEL);
1834 trace(' **Label Format ID : ' || P_LABEL_FORMAT_ID, TRACE_PROMPT, TRACE_LEVEL);
1835 trace(' **Organization ID : ' || p_organization_id, TRACE_PROMPT, TRACE_LEVEL);
1836 trace(' **Item ID : ' ||p_inventory_item_id, TRACE_PROMPT, TRACE_LEVEL);
1837 trace(' **Lot Number : ' ||P_LOT_NUMBER, TRACE_PROMPT, TRACE_LEVEL);
1838 trace(' **Serial Number : ' ||P_SERIAL_NUMBER, TRACE_PROMPT, TRACE_LEVEL);
1839 trace(' **Revision : '|| P_REVISION, TRACE_PROMPT, TRACE_LEVEL);
1840 trace(' **Business Flow Code : ' ||P_BUSINESS_FLOW_CODE, TRACE_PROMPT, TRACE_LEVEL);
1841 trace(' **Printer Name : ' || P_PRINTER_NAME, TRACE_PROMPT, TRACE_LEVEL);
1842 trace(' **Last Update Date : ' || P_LAST_UPDATE_DATE, TRACE_PROMPT, TRACE_LEVEL);
1843 trace(' **Last Updated By : ' || P_LAST_UPDATED_BY, TRACE_PROMPT, TRACE_LEVEL);
1844 trace(' **Creation Date : ' || P_CREATION_DATE, TRACE_PROMPT, TRACE_LEVEL);
1845 trace(' **Created By : ' || P_CREATED_BY, TRACE_PROMPT, TRACE_LEVEL);
1846 trace(' **Supplier ID : ' || P_SUPPLIER_ID, TRACE_PROMPT, TRACE_LEVEL);
1847 trace(' **Supplier Site ID : ' || P_SUPPLIER_SITE_ID, TRACE_PROMPT, TRACE_LEVEL);
1848 END IF;
1849
1850 SELECT wms_label_print_history_s.nextval into l_label_request_id from dual;
1851 x_label_request_id := l_label_request_id;
1852
1853 trace(' **Label Request ID : ' || l_label_request_id, TRACE_PROMPT, TRACE_LEVEL);
1854
1855 IF (l_debug = 1) THEN
1856 trace(' **Label Request ID inserted in the wms_label_requests table is :'|| l_label_request_id, TRACE_PROMPT, TRACE_LEVEL);
1857 END IF;
1858
1859 -- 1. insert a record into wms_label_requests
1860 -- entity to process data for label rules
1861 INSERT INTO wms_label_requests
1862 ( label_request_id,
1863 DOCUMENT_ID ,
1864 LABEL_FORMAT_ID,
1865 ORGANIZATION_ID,
1866 INVENTORY_ITEM_ID,
1867 SUBINVENTORY_CODE,
1868 LOCATOR_ID ,
1869 LOT_NUMBER ,
1870 REVISION ,
1871 SERIAL_NUMBER ,
1872 LPN_ID ,
1873 SUPPLIER_ID ,
1874 SUPPLIER_SITE_ID ,
1875 SUPPLIER_ITEM_ID ,
1876 CUSTOMER_ID ,
1877 CUSTOMER_SITE_ID ,
1878 CUSTOMER_ITEM_ID ,
1879 CUSTOMER_CONTACT_ID ,
1880 FREIGHT_CODE ,
1881 LAST_UPDATE_DATE ,
1882 LAST_UPDATED_BY ,
1883 CREATION_DATE ,
1884 CREATED_BY ,
1885 LAST_UPDATE_LOGIN ,
1886 REQUEST_ID ,
1887 PROGRAM_APPLICATION_ID,
1888 PROGRAM_ID ,
1889 PROGRAM_UPDATE_DATE ,
1890 ATTRIBUTE_CATEGORY ,
1891 ATTRIBUTE1 ,
1892 ATTRIBUTE2 ,
1893 ATTRIBUTE3 ,
1894 ATTRIBUTE4 ,
1895 ATTRIBUTE5 ,
1896 ATTRIBUTE6 ,
1897 ATTRIBUTE7 ,
1898 ATTRIBUTE8 ,
1899 ATTRIBUTE9 ,
1900 ATTRIBUTE10 ,
1901 ATTRIBUTE11 ,
1902 ATTRIBUTE12 ,
1903 ATTRIBUTE13 ,
1904 ATTRIBUTE14 ,
1905 ATTRIBUTE15 ,
1906 PRINTER_NAME ,
1907 DELIVERY_ID ,
1908 BUSINESS_FLOW_CODE ,
1909 package_id ,
1910 delivery_detail_id,
1911 sales_order_header_id,
1912 sales_order_line_id
1913 )
1914 VALUES
1915 ( l_label_request_id,
1916 P_DOCUMENT_ID ,
1917 P_LABEL_FORMAT_ID,
1918 P_ORGANIZATION_ID,
1919 P_INVENTORY_ITEM_ID,
1920 P_SUBINVENTORY_CODE,
1921 P_LOCATOR_ID ,
1922 P_LOT_NUMBER ,
1923 P_REVISION ,
1924 P_SERIAL_NUMBER ,
1925 P_LPN_ID ,
1926 P_SUPPLIER_ID ,
1927 P_SUPPLIER_SITE_ID ,
1928 P_SUPPLIER_ITEM_ID ,
1929 P_CUSTOMER_ID ,
1930 P_CUSTOMER_SITE_ID ,
1931 P_CUSTOMER_ITEM_ID ,
1932 P_CUSTOMER_CONTACT_ID ,
1933 P_FREIGHT_CODE ,
1934 sysdate,
1935 FND_GLOBAL.user_id,
1936 sysdate,
1937 FND_GLOBAL.user_id,
1938 P_LAST_UPDATE_LOGIN ,
1939 P_REQUEST_ID ,
1940 P_PROGRAM_APPLICATION_ID,
1941 P_PROGRAM_ID ,
1942 P_PROGRAM_UPDATE_DATE ,
1943 P_ATTRIBUTE_CATEGORY ,
1944 P_ATTRIBUTE1 ,
1945 P_ATTRIBUTE2 ,
1946 P_ATTRIBUTE3 ,
1947 P_ATTRIBUTE4 ,
1948 P_ATTRIBUTE5 ,
1949 P_ATTRIBUTE6 ,
1950 P_ATTRIBUTE7 ,
1951 P_ATTRIBUTE8 ,
1952 P_ATTRIBUTE9 ,
1953 P_ATTRIBUTE10 ,
1954 P_ATTRIBUTE11 ,
1955 P_ATTRIBUTE12 ,
1956 P_ATTRIBUTE13 ,
1957 P_ATTRIBUTE14 ,
1958 P_ATTRIBUTE15 ,
1959 P_PRINTER_NAME ,
1960 P_DELIVERY_ID ,
1961 P_BUSINESS_FLOW_CODE,
1962 p_package_id,
1963 p_delivery_detail_id,
1964 p_sales_order_header_id,
1965 p_sales_order_line_id
1966 );
1967
1968 IF (l_debug = 1) THEN
1969 trace('Inserted into WMS_LABEL_REQUESTS table, label_request_id=' ||l_label_request_id, TRACE_PROMPT, TRACE_LEVEL);
1970 END IF;
1971 x_label_request_id := l_label_request_id;
1972
1973 l_wms_installed := wms_install.check_install(
1974 x_return_status => l_return_status,
1975 x_msg_count => l_msg_count,
1976 x_msg_data => l_msg_data,
1977 p_organization_id => P_ORGANIZATION_ID);
1978
1979 IF p_label_format_id IS NOT NULL THEN
1980 -- The P_LABEL_FORMAT_ID(manual_format_id) is the one passed from the manual page.
1981 -- As per the design, if a format ID is passed from the manual page, the rules engine will
1982 -- not be called.
1983 x_label_format_id := p_label_format_id;
1984 BEGIN
1985 select label_format_name
1986 into x_label_format
1987 from wms_label_formats
1988 where label_format_id = x_label_format_id;
1989 EXCEPTION
1990 when others then
1991 IF (l_debug = 1) THEN
1992 trace('No format found for ID:'||x_label_format_id, TRACE_PROMPT, TRACE_LEVEL);
1993 END IF;
1994 x_label_format:= null;
1995 END;
1996
1997 ELSIF nvl(p_use_rule_engine, 'Y') <> 'N' THEN
1998 -- If indicate not to use rules engine
1999 -- Manual format is not specified. Check WMS installed
2000 -- For WMS user, call rules engine
2001 -- otherwise, return null
2002 IF (l_wms_installed = FALSE) OR (l_return_status <> 'S') THEN
2003 IF (l_debug = 1) THEN
2004 trace('WMS is not installed or enabled for org ' || P_ORGANIZATION_ID || ', will not apply rules engine.', TRACE_PROMPT, TRACE_LEVEL);
2005 END IF;
2006
2007 -- Added this as part of the fix for Bug 2810264. The Rules Engine is called only for WMS enabled Orgs.
2008 -- Earlier, in case of MSCA Orgs, the GET_FORMAT_WITH_RULE would return a null label_format_id.
2009 -- In this fix, for MSCA Orgs, the default format ID and default foamrat name is derived and passed back.
2010 -- This change only needs to be done in the main package while the individual label API's remain
2011 -- untouched.
2012 -- Get default format
2013 get_default_format
2014 ( p_label_type_id => P_DOCUMENT_ID,
2015 p_label_format => x_label_format,
2016 p_label_format_id => x_label_format_id);
2017
2018 IF (l_debug = 1) THEN
2019 trace('**Default Format ID in the get_format_with_rule ' || x_label_format_id, TRACE_PROMPT, TRACE_LEVEL);
2020 trace('**Default Format in the get_format_with_rule ' || x_label_format, TRACE_PROMPT, TRACE_LEVEL);
2021 END IF;
2022
2023 -- Bug 3841820
2024 -- When rules engine is not used, update the label request record with label format ID
2025 BEGIN
2026 update wms_label_requests
2027 set label_format_id = x_label_format_id
2028 where label_request_id = l_label_request_id;
2029 EXCEPTION
2030 WHEN others THEN
2031 IF l_debug =1 THEN
2032 trace('error when updating wms_label_requests with label format,req_id='||x_label_request_id,TRACE_PROMPT, TRACE_LEVEL);
2033 END IF;
2034 END;
2035
2036 ELSE
2037 -- WMS is installed, apply rules engine
2038 -- IF clause Added for Add format/printer for manual request
2039 IF (l_debug = 1) THEN
2040 trace(' In applying rules engine, row inserted, req_id='|| l_label_request_id, TRACE_PROMPT, TRACE_LEVEL);
2041 END IF;
2042 -- Then apply rules engine to obtain the label format
2043 WMS_RULE_PVT.ApplyLabel(
2044 p_api_version =>1.0,
2045 p_init_msg_list =>fnd_api.g_false,
2046 p_commit =>fnd_api.g_false,
2047 p_validation_level=>0,
2048 p_LABEL_REQUEST_id=>l_LABEL_REQUEST_id,
2049 x_return_status =>l_return_status,
2050 x_msg_count =>l_msg_count,
2051 x_msg_data =>l_msg_data,
2052 x_label_format_id =>x_label_format_id,
2053 x_label_format_name =>x_label_format
2054 );
2055 IF (l_debug = 1) THEN
2056 trace(' Applyed rules engine, label_format:'|| x_label_format || 'format_id='|| x_label_format_id, TRACE_PROMPT, TRACE_LEVEL);
2057 END IF;
2058 END IF;
2059 END IF;
2060
2061 --R12 label-set changes
2062 --if the format_id is label set thn delete this record
2063 IF (l_debug = 1) THEN
2064 trace('Deleting row from wms_label_request, if this is a label-set', TRACE_PROMPT, TRACE_LEVEL);
2065 END IF;
2066
2067 /* With the label-set changes in PVT1,2,3,4,5. This record can be
2068 deleted for both label-set and label-format. Because the driver to
2069 populate in the history table is returned number of records from
2070 respective PVT. But since this label set feature is NOT implemented
2071 in other remaining PVT packages and this API does not get called
2072 twice, This should only be delete for label-set.
2073
2074 FOR PVT1,2,3,4,5, there will be one extra record in the
2075 wms_label_request table for each transaction but it will NOT be
2076 transferred to hist rec or to generated xml as the respective
2077 PVT1,2,3,4,5 does not return the first pseudo record.
2078
2079 We need to insert it for each time as rules engine works of
2080 wms_label_request table
2081 */
2082
2083 DELETE FROM wms_label_requests
2084 WHERE label_request_id = l_label_request_id
2085 AND exists (SELECT label_format_id FROM wms_label_formats
2086 WHERE label_format_id = x_label_format_id
2087 AND document_id = p_document_id
2088 AND Nvl(label_entity_type,0) = 1);
2089
2090 IF (l_debug = 1) THEN
2091 trace('Number of rows deleted for label-set :'||SQL%rowcount, TRACE_PROMPT, TRACE_LEVEL);
2092 END IF;
2093
2094
2095
2096 EXCEPTION
2097 WHEN OTHERS THEN
2098 x_return_status := FND_API.G_RET_STS_ERROR;
2099 IF (l_debug = 1) THEN
2100 trace('error in calling rules engine',TRACE_PROMPT, TRACE_LEVEL);
2101 trace('ERROR CODE = ' || SQLCODE, TRACE_PROMPT, TRACE_LEVEL);
2102 trace('ERROR MESSAGE = ' || SQLERRM, TRACE_PROMPT, TRACE_LEVEL);
2103 END IF;
2104
2105 END GET_FORMAT_WITH_RULE;
2106
2107
2108 /********************************************
2109 * Wrapper API for calling printing from Java
2110 * This wrapper is for giving transaction ID
2111 *******************************************/
2112
2113 PROCEDURE PRINT_LABEL_WRAP
2114 (
2115 x_return_status OUT NOCOPY VARCHAR2
2116 , x_msg_count OUT NOCOPY NUMBER
2117 , x_msg_data OUT NOCOPY VARCHAR2
2118 , x_label_status OUT NOCOPY VARCHAR2
2119 , p_business_flow_code IN NUMBER
2120 , p_transaction_id IN number
2121 , p_transaction_identifier IN number
2122 ) IS
2123 l_return_status VARCHAR2(240);
2124 l_msg_count NUMBER;
2125 l_msg_data VARCHAR2(240);
2126 l_no_of_copies NUMBER :=1;
2127 l_label_status varchar2(300);
2128 l_transaction_id INV_LABEL.transaction_id_rec_type;
2129 l_transaction_identifier NUMBER ;
2130
2131 BEGIN
2132 l_debug := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
2133
2134 IF (l_debug = 1) THEN
2135 trace('Begin: print_label_wrap() ' || 'p_bus_flow, txn_id, p_transaction_identifier: '
2136 ||p_business_flow_code||','||p_transaction_id||','|| p_transaction_identifier , TRACE_PROMPT, TRACE_LEVEL);
2137 END IF;
2138
2139 -- Initialize API return status to success
2140 x_return_status := FND_API.G_RET_STS_SUCCESS;
2141
2142 l_transaction_id(1) := p_transaction_id;
2143 l_transaction_identifier := p_transaction_identifier;
2144
2145
2146 IF (l_transaction_identifier IS NULL) AND
2147 (p_business_flow_code = 35) THEN
2148 -- =====================================
2149 -- Transaction is processed from MMTT
2150 -- =====================================
2151 l_transaction_identifier := 1;
2152 IF (l_debug = 1) THEN
2153 trace('Business Code 35. Set transaction_identifier to 1 ' , TRACE_PROMPT, TRACE_LEVEL);
2154 END IF;
2155 END IF;
2156
2157 inv_label.print_label(
2158 x_return_status => x_return_status
2159 , x_msg_count => x_msg_count
2160 , x_msg_data => x_msg_data
2161 , x_label_status => x_label_status
2162 , p_api_version => 1.0
2163 , p_print_mode => 1
2164 , p_business_flow_code => p_business_flow_code
2165 , p_transaction_id => l_transaction_id
2166 , p_transaction_identifier => l_transaction_identifier
2167 );
2168 EXCEPTION
2169 WHEN OTHERS THEN
2170 x_return_status := FND_API.G_RET_STS_ERROR;
2171 IF (l_debug = 1) THEN
2172 trace('error in PRINT_LABEL_WRAP',TRACE_PROMPT, TRACE_LEVEL);
2173 trace('ERROR CODE = ' || SQLCODE, TRACE_PROMPT, TRACE_LEVEL);
2174 trace('ERROR MESSAGE = ' || SQLERRM, TRACE_PROMPT, TRACE_LEVEL);
2175 END IF;
2176
2177 END PRINT_LABEL_WRAP;
2178
2179
2180 /********************************************
2181 * Wrapper API for calling printing from Java
2182 * This wrapper is for Manual Mode
2183 *******************************************/
2184 PROCEDURE PRINT_LABEL_MANUAL_WRAP
2185 (
2186 x_return_status OUT NOCOPY VARCHAR2
2187 , x_msg_count OUT NOCOPY NUMBER
2188 , x_msg_data OUT NOCOPY VARCHAR2
2189 , x_label_status OUT NOCOPY VARCHAR2
2190 , p_business_flow_code IN NUMBER
2191 , p_label_type IN NUMBER
2192 , p_organization_id IN NUMBER
2193 , p_inventory_item_id IN NUMBER
2194 , p_revision IN VARCHAR2
2195 , p_lot_number IN VARCHAR2
2196 , p_fm_serial_number IN VARCHAR2
2197 , p_to_serial_number IN VARCHAR2
2198 , p_lpn_id IN NUMBER
2199 , p_subinventory_code IN VARCHAR2
2200 , p_locator_id IN NUMBER
2201 , p_delivery_id IN NUMBER
2202 , p_quantity IN NUMBER
2203 , p_uom IN VARCHAR2
2204 , p_wip_entity_id IN NUMBER --Bug #6417575,Label Printing Support for WIP Move Transactions (12.1)
2205 , p_no_of_copies IN NUMBER
2206 , p_fm_schedule_number IN VARCHAR2
2207 , p_to_schedule_number IN VARCHAR2
2208 , p_format_id IN NUMBER
2209 , p_printer_name IN VARCHAR2
2210 ) IS
2211 l_return_status VARCHAR2(240);
2212 l_msg_count NUMBER;
2213 l_msg_data VARCHAR2(240);
2214 l_label_status varchar2(300);
2215 l_input_param input_parameter_rec_type;
2216 l_range_serial_numbers serial_tab_type;
2217 i NUMBER;
2218 l_lot_number MTL_LOT_NUMBERS.LOT_NUMBER%TYPE;
2219 l_total_schedule_number NUMBER;
2220 l_schedule_number NUMBER;
2221 l_to_schedule_number NUMBER;
2222 l_wip_entity_id WIP_FLOW_SCHEDULES.WIP_ENTITY_ID%TYPE;
2223 l_range_schedule_numbers serial_tab_type;
2224
2225 l_lot_control_code NUMBER := 1;
2226
2227 BEGIN
2228
2229
2230 l_debug := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
2231 IF (l_debug = 1) THEN
2232 trace('Begin : print_label_manual_wrap() input parameters are ' , TRACE_PROMPT, TRACE_LEVEL);
2233 trace(' BusFlow,labelType: '||p_business_flow_code||','||p_label_type,TRACE_PROMPT, TRACE_LEVEL);
2234 trace(' Org,Item,Rev,Lot,fmSer,toSer: '||p_organization_id||','||p_inventory_item_id||','||p_revision||','
2235 ||p_lot_number||','||p_fm_serial_number||','||p_to_serial_number,TRACE_PROMPT, TRACE_LEVEL);
2236 trace(' LPN,Sub,Loc,Del,Qty,UOM,Copy: '||p_lpn_id||','||p_subinventory_code||','||p_locator_id
2237 ||','||p_delivery_id||','||p_quantity||','||p_uom||','||p_no_of_copies,TRACE_PROMPT, TRACE_LEVEL);
2238 trace(' fmSchedul,toSched,wipEntityId: '||p_fm_schedule_number||','||p_to_schedule_number||','
2239 ||p_wip_entity_id,TRACE_PROMPT, TRACE_LEVEL);
2240 trace(' formatID, printerName: '||p_format_id||','||p_printer_name, TRACE_PROMPT, TRACE_LEVEL);
2241 END IF;
2242
2243
2244 -- Initialize the l_input_param
2245 i := 1;
2246 l_input_param(i).organization_id := p_organization_id;
2247 l_input_param(i).inventory_item_id := p_inventory_item_id;
2248 l_input_param(i).revision := p_revision;
2249 l_input_param(i).lot_number := p_lot_number;
2250 l_input_param(i).lpn_id := p_lpn_id;
2251 l_input_param(i).subinventory_code := p_subinventory_code;
2252 l_input_param(i).locator_id := p_locator_id;
2253 l_input_param(i).transaction_temp_id := p_delivery_id;
2254 l_input_param(i).transaction_quantity := p_quantity;
2255 l_input_param(i).transaction_uom := p_uom;
2256
2257 --hjogleka, Bug #6417575,Label Printing Support for WIP Move Transactions (12.1)
2258 -- populate transaction_source_id with wip_entity_id.
2259 -- It will be read in get_variable_data of INV_LABEL_PVT2 and INV_LABEL_PVT9
2260 l_input_param(i).transaction_source_id := p_wip_entity_id;
2261
2262 IF (l_debug = 1) THEN
2263 trace('**Values to parameters passed to the PRINT_LABEL_MANUAL_WRAP',TRACE_PROMPT, TRACE_LEVEL);
2264 trace('**Org ID passed in : ' ||l_input_param(i).organization_id,TRACE_PROMPT, TRACE_LEVEL);
2265 trace('**inventory_item_id passed in : ' ||l_input_param(i).inventory_item_id,TRACE_PROMPT, TRACE_LEVEL);
2266 trace('**revision passed in : ' ||l_input_param(i).revision,TRACE_PROMPT, TRACE_LEVEL);
2267 trace('**lot_number passed in : ' ||l_input_param(i).lot_number,TRACE_PROMPT, TRACE_LEVEL);
2268 trace('**lpn_id passed in : ' ||l_input_param(i).lpn_id,TRACE_PROMPT, TRACE_LEVEL);
2269 trace('**subinventory_code passed in : ' ||l_input_param(i).subinventory_code,TRACE_PROMPT, TRACE_LEVEL);
2270 trace('**locator_id passed in : '||l_input_param(i).locator_id,TRACE_PROMPT, TRACE_LEVEL);
2271 trace('**transaction_temp_id passed in : ' ||l_input_param(i).transaction_temp_id,TRACE_PROMPT, TRACE_LEVEL);
2272 trace('**transaction_quantity passed in : ' ||l_input_param(i).transaction_quantity,TRACE_PROMPT, TRACE_LEVEL);
2273 trace('**transaction_uom passed in : ' ||l_input_param(i).transaction_uom,TRACE_PROMPT, TRACE_LEVEL);
2274 trace('**transaction_source_id passed in : ' ||l_input_param(i).transaction_source_id,TRACE_PROMPT, TRACE_LEVEL);
2275 trace('**From Serial Number pased in : ' || p_fm_serial_number,TRACE_PROMPT, TRACE_LEVEL);
2276 END IF;
2277
2278 -- Initialize API return status to success
2279 x_return_status := FND_API.G_RET_STS_SUCCESS;
2280 IF (p_fm_serial_number IS NOT NULL) THEN
2281 IF (p_to_serial_number IS NOT NULL)
2282 AND (p_fm_serial_number <> p_to_serial_number) THEN
2283 Begin
2284 -- Range serial number is provided
2285 GET_NUMBER_BETWEEN_RANGE(
2286 fm_x_number => p_fm_serial_number
2287 , to_x_number => p_to_serial_number
2288 , x_return_status => l_return_status
2289 , x_number_table => l_range_serial_numbers
2290 );
2291
2292 Exception
2293 when no_data_found then
2294 IF (l_debug = 1) THEN
2295 trace('error in GET_NUMBER_BETWEEN_RANGE : no_data_found ',TRACE_PROMPT, TRACE_LEVEL);
2296 END IF;
2297
2298 when others then
2299 IF (l_debug = 1) THEN
2300 trace('error in PRINT_LABEL_MANUAL_WRAP',TRACE_PROMPT, TRACE_LEVEL);
2301 trace('ERROR CODE = ' || SQLCODE, TRACE_PROMPT, TRACE_LEVEL);
2302 trace('ERROR MESSAGE = ' || SQLERRM, TRACE_PROMPT, TRACE_LEVEL);
2303 END IF;
2304
2305 End;
2306
2307 IF l_return_status <> 'S' THEN
2308 IF (l_debug = 1) THEN
2309 trace(' Error in getting range serial numbers from '|| p_fm_serial_number ||' to '|| p_to_serial_number || ', set serial number as null', TRACE_PROMPT, TRACE_LEVEL);
2310 END IF;
2311 l_range_serial_numbers(1) := null;
2312 ELSE
2313 IF (l_debug = 1) THEN
2314 trace(' Found ' || l_range_serial_numbers.count || ' serial numbers from '|| p_fm_serial_number ||' to '|| p_to_serial_number, TRACE_PROMPT, TRACE_LEVEL);
2315 END IF;
2316 END IF;
2317 ELSE
2318 l_range_serial_numbers(1) := p_fm_serial_number;
2319 END IF;
2320
2321 -- Check whether item is lot controlled
2322 -- Only if the item is lot controlled, then get lot_number
2323 -- joabraha :Added an exception around this select so that the exception when the item is is not Lot Cotrolled is caught
2324 -- and the API can proceed executing.
2325 Begin
2326 SELECT lot_control_code INTO l_lot_control_code
2327 FROM MTL_SYSTEM_ITEMS_B
2328 WHERE organization_id = p_organization_id
2329 AND inventory_item_id = p_inventory_item_id;
2330
2331 Exception
2332 when no_data_found then
2333 IF (l_debug = 1) THEN
2334 trace('error in getting lot_control_code : no_data_found ',TRACE_PROMPT, TRACE_LEVEL);
2335 END IF;
2336
2337 when others then
2338 IF (l_debug = 1) THEN
2339 trace('error in PRINT_LABEL_MANUAL_WRAP',TRACE_PROMPT, TRACE_LEVEL);
2340 trace('ERROR CODE = ' || SQLCODE, TRACE_PROMPT, TRACE_LEVEL);
2341 trace('ERROR MESSAGE = ' || SQLERRM, TRACE_PROMPT, TRACE_LEVEL);
2342 END IF;
2343 END;
2344
2345 IF SQL%NOTFOUND THEN
2346 IF(l_debug=1) THEN
2347 trace('No item found for Org='||p_organization_id||',itemId='||p_inventory_item_id , TRACE_PROMPT, TRACE_LEVEL);
2348 END IF;
2349 l_lot_control_code := 1;
2350 END IF;
2351
2352 FOR i IN 1..l_range_serial_numbers.count LOOP
2353 l_input_param(i).organization_id := p_organization_id;
2354 l_input_param(i).inventory_item_id:=p_inventory_item_id;
2355 l_input_param(i).serial_number := l_range_serial_numbers(i);
2356 -- Fix bug 1797307, obtain lot_number from serial number
2357 -- in manual print request
2358 l_lot_number := p_lot_number;
2359 IF (l_range_serial_numbers(i) is not null)
2360 AND (p_lot_number is null or p_lot_number='')
2361 AND (l_lot_control_code <> 1) THEN
2362 SELECT lot_number
2363 INTO l_lot_number
2364 FROM mtl_serial_numbers
2365 WHERE current_organization_id = p_organization_id
2366 AND inventory_item_id = p_inventory_item_id
2367 AND serial_number = l_range_serial_numbers(i);
2368
2369 IF SQL%NOTFOUND THEN
2370 l_lot_number := null;
2371 END IF;
2372 END IF;
2373
2374 l_input_param(i).lot_number := l_lot_number;
2375 l_input_param(i).revision := p_revision;
2376 l_input_param(i).lpn_id := p_lpn_id;
2377 l_input_param(i).subinventory_code := p_subinventory_code;
2378 l_input_param(i).locator_id := p_locator_id;
2379 l_input_param(i).transaction_temp_id := p_delivery_id;
2380 l_input_param(i).transaction_quantity := p_quantity;
2381 l_input_param(i).transaction_uom := p_uom;
2382
2383 --hjogleka, Bug #6417575,Label Printing Support for WIP Move Transactions (12.1)
2384 l_input_param(i).transaction_source_id := p_wip_entity_id;
2385 END LOOP;
2386 /* =============================================================
2387 Add Codes for Manual Print Of Flow Schedule Number
2388 =============================================================
2389 */
2390 ELSIF (p_fm_schedule_number IS NOT NULL) THEN
2391 l_range_schedule_numbers(1) := p_fm_schedule_number;
2392 IF (p_to_schedule_number IS NOT NULL)
2393 AND (p_fm_schedule_number <> p_to_schedule_number) THEN
2394 -- Range number is provided
2395 GET_NUMBER_BETWEEN_RANGE(
2396 fm_x_number => p_fm_schedule_number
2397 , to_x_number => p_to_schedule_number
2398 , x_return_status => l_return_status
2399 , x_number_table=> l_range_schedule_numbers
2400 );
2401
2402 IF l_return_status <> 'S' THEN
2403 IF (l_debug = 1) THEN
2404 trace(' Error in getting range schedule numbers from '|| p_fm_schedule_number ||' to '|| p_to_schedule_number || ', set serial number as null', TRACE_PROMPT, TRACE_LEVEL);
2405 END IF;
2406 l_range_schedule_numbers(1) := null;
2407 ELSE
2408 IF (l_debug = 1) THEN
2409 trace(' Found ' || l_range_schedule_numbers.count || ' schedule numbers from '|| p_fm_schedule_number || ' to '|| p_to_schedule_number, TRACE_PROMPT, TRACE_LEVEL);
2410 END IF;
2411 END IF;
2412 END IF;
2413
2414 FOR i IN 1..l_range_schedule_numbers.count LOOP
2415 /* retrieve wip_entity_id from wip_flow_schedule */
2416 BEGIN
2417 SELECT wip_entity_id into l_wip_entity_id
2418 FROM WIP_FLOW_SCHEDULES
2419 WHERE organization_id = p_organization_id
2420 AND schedule_number = l_range_schedule_numbers(i);
2421
2422 EXCEPTION
2423 WHEN OTHERS THEN
2424 IF (l_debug = 1) THEN
2425 trace('EXCEPTION : No data found for wip_flow_schedule', TRACE_PROMPT, TRACE_LEVEL);
2426 trace('ERROR CODE = ' || SQLCODE, TRACE_PROMPT, TRACE_LEVEL);
2427 trace('ERROR MESSAGE = ' || SQLERRM, TRACE_PROMPT, TRACE_LEVEL);
2428 END IF;
2429 l_wip_entity_id := null;
2430 END ;
2431 l_input_param(i).organization_id := p_organization_id;
2432 l_input_param(i).transaction_temp_id := l_wip_entity_id;
2433 l_schedule_number := l_schedule_number + i;
2434 END LOOP;
2435 END IF ;
2436 inv_label.print_label(
2437 x_return_status => x_return_status
2438 ,x_msg_count => x_msg_count
2439 ,x_msg_data => x_msg_data
2440 ,x_label_status => x_label_status
2441 ,p_api_version => 1.0
2442 ,p_print_mode => 2
2443 ,p_label_type_id => p_label_type
2444 ,p_business_flow_code => p_business_flow_code
2445 ,p_input_param_rec => l_input_param
2446 ,p_no_of_copies => nvl(p_no_of_copies, 1)
2447 ,p_format_id => p_format_id -- Added for the Add Printer and Format Project.
2448 ,p_printer_name => p_printer_name); -- Added for the Add Printer and Format Project.
2449 EXCEPTION
2450 WHEN OTHERS THEN
2451 x_return_status := FND_API.G_RET_STS_ERROR;
2452 IF (l_debug = 1) THEN
2453 trace('error in PRINT_LABEL_MANUAL_WRAP',TRACE_PROMPT, TRACE_LEVEL);
2454 trace('ERROR CODE = ' || SQLCODE, TRACE_PROMPT, TRACE_LEVEL);
2455 trace('ERROR MESSAGE = ' || SQLERRM, TRACE_PROMPT, TRACE_LEVEL);
2456 END IF;
2457
2458 END PRINT_LABEL_MANUAL_WRAP;
2459
2460 /*********************************************
2461 * Print Label
2462 * This can be called from transaction process or manual
2463 * p_print_mode: 1 => Transaction Driven
2464 * 2 => Manual print
2465 * If it is transaction driven, business flow code
2466 * and transaction are required
2467 * If it is manual print, label type and input record are required
2468 **********************************************/
2469 PROCEDURE PRINT_LABEL
2470 (
2471 x_return_status OUT NOCOPY VARCHAR2
2472 , x_msg_count OUT NOCOPY NUMBER
2473 , x_msg_data OUT NOCOPY VARCHAR2
2474 , x_label_status OUT NOCOPY VARCHAR2
2475 , p_api_version IN NUMBER
2476 , p_init_msg_list IN VARCHAR2 := fnd_api.g_false
2477 , p_commit IN VARCHAR2 := fnd_api.g_false
2478 , p_print_mode IN NUMBER
2479 , p_business_flow_code IN NUMBER
2480 , p_transaction_id IN transaction_id_rec_type
2481 , p_input_param_rec IN input_parameter_rec_type
2482 , p_label_type_id IN NUMBER
2483 , p_no_of_copies IN NUMBER :=1
2484 , p_transaction_identifier IN NUMBER
2485 , p_format_id IN NUMBER -- Added for the Add Printer and Format Project.
2486 , p_printer_name IN VARCHAR2 -- Added for the Add Printer and Format Project.
2487 ) IS
2488
2489
2490 l_label_request_id NUMBER;
2491
2492 BEGIN
2493
2494
2495 PRINT_LABEL
2496 (
2497 x_return_status => x_return_status
2498 , x_msg_count => x_msg_count
2499 , x_msg_data => x_msg_data
2500 , x_label_status => x_label_status
2501 , x_label_request_id => l_label_request_id
2502 , p_api_version => p_api_version
2503 , p_init_msg_list => p_init_msg_list
2504 , p_commit => p_commit
2505 , p_print_mode => p_print_mode
2506 , p_business_flow_code => p_business_flow_code
2507 , p_transaction_id => p_transaction_id
2508 , p_input_param_rec => p_input_param_rec
2509 , p_label_type_id => p_label_type_id
2510 , p_no_of_copies => p_no_of_copies
2511 , p_transaction_identifier => p_transaction_identifier
2512 , p_format_id => p_format_id
2513 , p_printer_name => p_printer_name );
2514
2515
2516 end PRINT_LABEL;
2517
2518 /*********************************************
2519 * Print Label - Overloaded version with new para added (x_label_request_id)
2520 *
2521 * Wrapper API to call the print label api above...
2522 *
2523 **********************************************/
2524
2525 PROCEDURE PRINT_LABEL
2526 (
2527 x_return_status OUT NOCOPY VARCHAR2
2528 , x_msg_count OUT NOCOPY NUMBER
2529 , x_msg_data OUT NOCOPY VARCHAR2
2530 , x_label_status OUT NOCOPY VARCHAR2
2531 , x_label_request_id OUT NOCOPY NUMBER -- added by fabdi
2532 , p_api_version IN NUMBER
2533 , p_init_msg_list IN VARCHAR2 := fnd_api.g_false
2534 , p_commit IN VARCHAR2 := fnd_api.g_false
2535 , p_print_mode IN NUMBER
2536 , p_business_flow_code IN NUMBER
2537 , p_transaction_id IN transaction_id_rec_type
2538 , p_input_param_rec IN input_parameter_rec_type
2539 , p_label_type_id IN NUMBER
2540 , p_no_of_copies IN NUMBER :=1
2541 , p_transaction_identifier IN NUMBER
2542 , p_format_id IN NUMBER -- Added for the Add Printer and Format Project.
2543 , p_printer_name IN VARCHAR2 -- Added for the Add Printer and Format Project.
2544
2545 ) IS
2546 l_api_version CONSTANT NUMBER := 1.0;
2547 l_api_name CONSTANT VARCHAR2(100) := 'PRINT_LABEL';
2548 l_msg_count NUMBER;
2549 l_msg_data VARCHAR2(240);
2550 l_return_status VARCHAR2(10);
2551 l_type_msg_count NUMBER;
2552 l_type_msg_data VARCHAR2(240);
2553 l_type_return_status VARCHAR2(10);
2554
2555 l_label_types label_type_tbl_type;
2556
2557 l_variable_data label_tbl_type;
2558 l_job_status VARCHAR2(2000); -- Bug 3328061 increased the size
2559 l_printer_status VARCHAR2(2000); -- Bug 3328061 increased the size
2560 l_status_type NUMBER;
2561
2562 l_request_id NUMBER;
2563
2564 l_xml_request_id NUMBER;
2565 l_lpn_table lpn_table_type; --BUG#3055877
2566 l_print_lpn_label VARCHAR2(10); --BUG#3055877
2567 lpn_table_populated VARCHAR2(10); --BUG#3055877
2568 l_patch_level NUMBER; -- indicates the current patchset level
2569
2570 /* bug 3417450 */
2571 l_lpn_sum_rec label_type_tbl_type;
2572 l_found_sum_rec BOOLEAN := FALSE;
2573 cntr BINARY_INTEGER;
2574
2575 BEGIN
2576
2577 l_debug := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
2578 IF (l_debug = 1) THEN
2579 trace(' **** In Print_label ****', TRACE_PROMPT, TRACE_LEVEL);
2580 trace(' p_print_mode, p_label_type_id, p_business_flow_code: '
2581 || p_print_mode ||','||p_label_type_id||','||p_business_flow_code, TRACE_PROMPT, TRACE_LEVEL);
2582 trace(' Number of p_input_param_rec, p_transaction_id: '|| p_input_param_rec.count ||','||p_transaction_id.count, TRACE_PROMPT, TRACE_LEVEL);
2583 trace(' p_no_of_copies, p_transaction_identifier, p_format_id, p_printer_name: '
2584 || p_no_of_copies||','||p_transaction_identifier ||','||p_format_id||','||p_printer_name, TRACE_PROMPT, TRACE_LEVEL);
2585
2586 FOR i IN 1..p_transaction_id.count LOOP
2587 IF (l_debug = 1) THEN
2588 trace(' For txn_id rec ' || i ||', trx_id = '|| p_transaction_id(i), TRACE_PROMPT, TRACE_LEVEL);
2589 END IF;
2590 END LOOP;
2591 END IF;
2592 /* Get the current patchset level*/
2593 IF (inv_rcv_common_apis.g_po_patch_level >= inv_rcv_common_apis.g_patchset_j_po
2594 OR inv_rcv_common_apis.g_inv_patch_level >= inv_rcv_common_apis.g_patchset_j
2595 ) THEN
2596 l_patch_level := 1; --Patchset J level or above
2597 IF (l_debug = 1) THEN
2598 trace('Patchset J level or above' , TRACE_PROMPT, TRACE_LEVEL);
2599 END IF;
2600 ELSE
2601 l_patch_level := 0; -- Below Patchset J
2602 IF (l_debug = 1) THEN
2603 trace(' Below Patchset J level ' , TRACE_PROMPT, TRACE_LEVEL);
2604 END IF;
2605 END IF;
2606
2607 IF p_print_mode =1 THEN
2608 -- Transaction driven, should provide transaction_id and business_flow_code
2609 IF (p_business_flow_code IS NULL) OR (p_business_flow_code <=0)
2610 OR (p_transaction_id.count = 0) THEN
2611 IF (l_debug = 1) THEN
2612 trace(' Not enough input, missing transaction_id or business_flow_code' , TRACE_PROMPT, TRACE_LEVEL);
2613 END IF;
2614 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2615 END IF;
2616 ELSIF p_print_mode =2 THEN
2617 -- On demand, should provide input_param_rec and label_type
2618 IF (p_input_param_rec.count = 0 ) OR
2619 ((p_label_type_id IS NULL OR p_label_type_id=0) AND (p_business_flow_code IS NULL)) THEN
2620 IF (l_debug = 1) THEN
2621 trace(' Not enough input, missing input_param or label_type' , TRACE_PROMPT, TRACE_LEVEL);
2622 END IF;
2623 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2624 END IF;
2625 ELSE
2626 IF (l_debug = 1) THEN
2627 trace(' Invalid value for p_print_mode, should be 1 or 2' , TRACE_PROMPT, TRACE_LEVEL);
2628 END IF;
2629 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2630 END IF;
2631
2632 IF (l_debug = 1) THEN
2633 trace(' Passed input parameter validation, Start ' , TRACE_PROMPT, TRACE_LEVEL);
2634 END IF;
2635 -- Standard Start of API savepoint
2636 SAVEPOINT CREATE_XML_LABEL;
2637
2638 -- Standard call to check for call compatibility.
2639 IF NOT FND_API.Compatible_API_Call
2640 ( l_api_version,
2641 p_api_version,
2642 l_api_name,
2643 G_PKG_NAME)
2644 THEN
2645 --FND_MESSAGE.SET_NAME('WMS', 'WMS_LABL_INCOMPATIBLE_API_CALL');
2646 --FND_MSG_PUB.ADD;
2647 IF (l_debug = 1) THEN
2648 trace(' Incompatible API ' , TRACE_PROMPT, TRACE_LEVEL);
2649 END IF;
2650 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2651 END IF;
2652 -- Initialize message list if p_init_msg_list is set to TRUE.
2653 IF FND_API.to_Boolean( p_init_msg_list ) THEN
2654 FND_MSG_PUB.initialize;
2655 END IF;
2656
2657 -- Initialize API return status to success
2658 x_return_status := FND_API.G_RET_STS_SUCCESS;
2659
2660 -- Get global variables
2661 IF (l_debug = 1) THEN
2662 trace(' Set and get global variables for date,time,user,encoding,profile values', TRACE_PROMPT, TRACE_LEVEL);
2663 END IF;
2664
2665 get_profile_values;
2666 get_date_time_user;
2667 get_xml_encoding;
2668
2669 IF (l_debug = 1) THEN
2670 trace(' Profile-Print Mode:'||G_PROFILE_PRINT_MODE||',Prefix: '||G_PROFILE_PREFIX
2671 ||',Out Directory: '||G_PROFILE_OUT_DIR
2672 ||',Date Mask: '||G_DATE_FORMAT_MASK, TRACE_PROMPT, TRACE_LEVEL);
2673 trace(' Request time: '||G_DATE||' '||G_TIME, TRACE_PROMPT, TRACE_LEVEL);
2674 trace(' Request user: '||G_USER, TRACE_PROMPT, TRACE_LEVEL);
2675 trace(' XML encoding: '||G_XML_ENCODING, TRACE_PROMPT, TRACE_LEVEL);
2676 END IF;
2677
2678
2679 GET_TYPE_TO_PRINT(
2680 x_return_status => l_return_status
2681 , x_msg_count => l_msg_count
2682 , x_msg_data => l_msg_data
2683 , x_types_to_print => l_label_types -- This is passed to the get_variable_data.
2684 , p_business_flow => p_business_flow_code
2685 , p_label_type_id => p_label_type_id
2686 , p_no_of_copies => p_no_of_copies
2687 , p_format_id => p_format_id -- Added for Add Format/Printer project
2688 , p_printer_name => p_printer_name -- Added for Add Format/Printer project
2689 );
2690
2691 IF (l_debug = 1) THEN
2692 trace(' Got label types, count = ' || l_label_types.count(), TRACE_PROMPT, TRACE_LEVEL);
2693 END IF;
2694 IF l_return_status <> 'S' THEN
2695 IF (l_debug = 1) THEN
2696 trace(' Get Type Failed ', TRACE_PROMPT, TRACE_LEVEL);
2697 END IF;
2698 RAISE FND_API.G_EXC_ERROR;
2699 END IF;
2700 --BUG#3055877
2701 /* inline branch the code so that we select the records from rti for *
2702 * patchset level below J, and from rt for Patchset J and above *
2703 */
2704 IF l_patch_level = 0 THEN
2705 IF p_business_flow_code in (1,2,3,4) THEN
2706 lpn_table_populated := 'N';
2707 FOR l IN 1..l_label_types.count() LOOP
2708 IF (l_label_types(l).label_type_id in (3,4,5) and lpn_table_populated = 'N') THEN
2709 IF (l_debug = 1) THEN
2710 trace(' LABEL TYPE- LPN/LPN CONTENT/LPN SUMMARY exists getting lpn_id from rti', TRACE_PROMPT, TRACE_LEVEL);
2711 END IF;
2712 FOR m IN 1..p_transaction_id.count() LOOP
2713 IF (l_debug = 1) THEN
2714 trace(' b4 patchset J: interface_transaction_id: '||p_transaction_id(m), TRACE_PROMPT, TRACE_LEVEL);
2715 END IF;
2716 SELECT nvl(DECODE(p_business_flow_code,2,rti.transfer_lpn_id,rti.lpn_id),0)
2717 INTO l_lpn_table(m)
2718 FROM rcv_transactions_interface rti
2719 WHERE rti.interface_transaction_id = p_transaction_id(m);
2720 IF (l_debug = 1) THEN
2721 trace(' Got LPN_ID: '||l_lpn_table(m), TRACE_PROMPT, TRACE_LEVEL);
2722 trace(' for interface_transaction_id: '||p_transaction_id(m), TRACE_PROMPT, TRACE_LEVEL);
2723 END IF;
2724 lpn_table_populated := 'Y';
2725 END LOOP;
2726 END IF;
2727 END LOOP;
2728 END IF;
2729 END IF;
2730 --BUG#3055877
2731
2732 /*Fix for bug 3858504. For cartonization we will store
2733 the table of LPN IDs for shipping content label */
2734 IF p_business_flow_code = 22 THEN
2735 --INV_LABEL_PVT8.g_carton_table := p_transaction_id;
2736 -- 4645826, call set_carton_count to count the total number of cartons for delivery
2737 -- Pick release can be run for multiple deliveries
2738 -- The count of p_transaction_id can be for multiple deliveries
2739 -- Need to count the total for each delivery
2740 FOR i IN 1..p_transaction_id.count LOOP
2741 INV_LABEL_PVT8.set_carton_count(p_transaction_id(i));
2742 END LOOP;
2743 END IF; --End of Fix for bug 3858504.
2744
2745
2746 -- Added for R12 RFID Compliance project
2747 -- Set value for global variable EPC_GROUP_ID
2748 -- The value will be set to null at the end of label printing code
2749 SELECT WMS_EPC_S2.nextval INTO EPC_GROUP_ID FROM DUAL;
2750 IF (l_debug = 1) THEN
2751 trace(' Set EPC_GROUP_ID = '||EPC_GROUP_ID, TRACE_PROMPT, TRACE_LEVEL);
2752 END IF;
2753
2754 IF (l_debug = 1) THEN
2755 trace(' # Start to loop and print each label ', TRACE_PROMPT, TRACE_LEVEL);
2756 END IF;
2757 IF p_print_mode = 1 THEN
2758 -- Transaction process driven
2759 g_xml_content := '';
2760 <<TxnRowLoop>>
2761 FOR i IN 1..p_transaction_id.count() LOOP
2762 <<LabelTypeLoop>>
2763 l_print_lpn_label := 'Y'; --BUG#3055877
2764 FOR j IN 1..l_label_types.count() LOOP
2765 --BUG#3055877
2766 IF l_patch_level = 0 THEN
2767 IF p_business_flow_code in (1,2,3,4) THEN
2768 IF ( (l_label_types(j).label_type_id in (3,4,5)) and i >= 2) THEN
2769 FOR k IN REVERSE 2..i LOOP
2770 IF l_lpn_table(i) = l_lpn_table(k-1) THEN
2771 IF (l_debug = 1) THEN
2772 trace('LPN releated labels(LPN/CONTENT/SUMMARY) already printed for this lpn_id: ' ||l_lpn_table(i), TRACE_PROMPT, TRACE_LEVEL);
2773 END IF;
2774 l_print_lpn_label := 'N';
2775 exit;
2776 END IF;
2777 END LOOP;
2778 END IF;
2779 END IF;
2780 END IF;
2781 --BUG#3055877
2782
2783 IF (l_debug = 1) THEN
2784 trace(' ## In Loop , for trx rec ' || i || ' , type '|| j, TRACE_PROMPT, TRACE_LEVEL);
2785 trace(' Going to get variable data ', TRACE_PROMPT, TRACE_LEVEL);
2786 trace(' value of l_print_lpn_label ' || l_print_lpn_label, TRACE_PROMPT, TRACE_LEVEL);
2787 END IF;
2788
2789 IF l_patch_level = 1 THEN
2790 l_print_lpn_label := 'Y';
2791 trace(' The value of l_print_lpn_label is now ' || l_print_lpn_label, TRACE_PROMPT, TRACE_LEVEL);
2792 END IF;
2793
2794 IF (l_label_types(j).label_type_id not in (3,4,5)) OR (l_print_lpn_label = 'Y') THEN --BUG#3055877
2795 IF (l_debug = 1) THEN
2796 trace('Custom Labels Trace [INVLABPB.pls]: At Breadcrumb 1 ', TRACE_PROMPT, TRACE_LEVEL);
2797 END IF;
2798 get_variable_data(
2799 x_variable_content => l_variable_data
2800 ,x_msg_count=>l_msg_count
2801 ,x_msg_data => l_msg_data
2802 ,x_return_status => l_return_status
2803 ,p_label_type_info => l_label_types(j)
2804 ,p_transaction_id => p_transaction_id(i)
2805 ,p_input_param => null
2806 ,p_transaction_identifier => p_transaction_identifier
2807 );
2808
2809
2810
2811 IF (l_debug = 1) THEN
2812 trace(' got variable data # rec count ='||l_variable_data.count(), TRACE_PROMPT, TRACE_LEVEL);
2813 trace(' return status = '||l_return_status, TRACE_PROMPT, TRACE_LEVEL);
2814 trace(' x_msg_count= '||l_msg_count , TRACE_PROMPT, TRACE_LEVEL);
2815 trace(' x_msg_data= '||l_msg_data, TRACE_PROMPT, TRACE_LEVEL);
2816 END IF;
2817
2818 IF l_return_status <> 'S' THEN
2819 x_msg_data := l_msg_data;
2820 IF (l_debug = 1) THEN
2821 trace('Custom Labels Trace [INVLABPB.pls]: At Breadcrumb 2 ', TRACE_PROMPT, TRACE_LEVEL);
2822 trace('Custom Labels Trace [INVLABPB.pls]: Unexpected Error returned by get_variable_data()', TRACE_PROMPT, TRACE_LEVEL);
2823 END IF;
2824 RETURN;
2825 END IF;
2826
2827
2828 FOR k IN 1..l_variable_data.count() LOOP
2829 IF (l_debug = 1) THEN
2830 trace(' write xml header,check_xml,populate_history ', TRACE_PROMPT, TRACE_LEVEL);
2831 END IF;
2832 write_xml_header(l_label_types(j),l_variable_data(k).label_request_id );
2833 x_label_request_id := l_variable_data(k).label_request_id; -- added by fabdi
2834 IF (l_debug = 1) THEN
2835 trace('x_label_request_id is >> '|| x_label_request_id, TRACE_PROMPT, TRACE_LEVEL);
2836 END IF;
2837 g_xml_content := g_xml_header || l_variable_data(k).label_content || LABELS_E;
2838 check_xml(g_xml_content);
2839 IF (l_debug = 1) THEN
2840 trace('Custom Labels Trace [INVLABPB.pls]: Before populate_history_record() ', TRACE_PROMPT, TRACE_LEVEL);
2841 trace('Custom Labels Trace [INVLABPB.pls]: l_variable_data(k).label_status: ' || l_variable_data(k).label_status, TRACE_PROMPT, TRACE_LEVEL);
2842 trace('Custom Labels Trace [INVLABPB.pls]: l_variable_data(k).error_message: ' || l_variable_data(k).error_message, TRACE_PROMPT, TRACE_LEVEL);
2843 END IF;
2844 populate_history_record(
2845 p_label_type_info => l_label_types(j)
2846 , p_label_content => g_xml_content
2847 , p_label_request_id => l_variable_data(k).label_request_id
2848 , p_status_flag => l_variable_data(k).label_status
2849 , p_error_message => l_variable_data(k).error_message
2850 );
2851
2852 IF (l_debug = 1) THEN
2853 trace('Custom Labels Trace [INVLABPB.pls]: After populate_history_record() ', TRACE_PROMPT, TRACE_LEVEL);
2854 END IF;
2855
2856 IF G_PROFILE_PRINT_MODE = 1 THEN
2857 -- Synchronize Mode, send request for each label
2858
2859 IF (l_debug = 1) THEN
2860 trace('Custom Labels Trace [INVLABPB.pls]: At Breadcrumb 3 ', TRACE_PROMPT, TRACE_LEVEL);
2861 trace('Custom Labels Trace [INVLABPB.pls]: Inside TXN Driven -> Synchronous mode code', TRACE_PROMPT, TRACE_LEVEL);
2862 trace('Custom Labels Trace [INVLABPB.pls]: l_variable_data(k).label_status: ' || l_variable_data(k).label_status, TRACE_PROMPT, TRACE_LEVEL);
2863 END IF;
2864 -- Proceed sending request for printing only if the label status is not an ERROR
2865 -- Bug Fix 5330030, put a NVL around l_variable_data(k).label_statu since null value for
2866 -- l_variable_data(k).label_status would indicate success.
2867 -- IF ( l_variable_data(k).label_status <> FND_API.G_RET_STS_ERROR ) THEN
2868 IF (nvl(l_variable_data(k).label_status, FND_API.G_RET_STS_SUCCESS) = FND_API.G_RET_STS_SUCCESS ) THEN
2869 IF (l_debug = 1) THEN
2870 trace('Custom Labels Trace [INVLABPB.pls]: At Breadcrumb 4 ', TRACE_PROMPT, TRACE_LEVEL);
2871 trace('Custom Labels Trace [INVLABPB.pls]: Before calling SYNC_PRINT_REQUEST() ', TRACE_PROMPT, TRACE_LEVEL);
2872 END IF;
2873 INV_PRINT_REQUEST.SYNC_PRINT_REQUEST(
2874 p_xml_content => g_xml_content
2875 ,x_job_status => l_job_status
2876 ,x_printer_status => l_printer_status
2877 ,x_status_type => l_status_type);
2878 IF (l_debug = 1) THEN
2879 trace('Custom Labels Trace [INVLABPB.pls]: At Breadcrumb 5 ', TRACE_PROMPT, TRACE_LEVEL);
2880 trace('Custom Labels Trace [INVLABPB.pls]: After calling SYNC_PRINT_REQUEST() ', TRACE_PROMPT, TRACE_LEVEL);
2881 trace('Custom Labels Trace [INVLABPB.pls]: Value of l_job_status is: ' || l_job_status, TRACE_PROMPT, TRACE_LEVEL);
2882 trace('Custom Labels Trace [INVLABPB.pls]: Value of l_printer_status is: ' || l_printer_status, TRACE_PROMPT, TRACE_LEVEL);
2883 trace('Custom Labels Trace [INVLABPB.pls]: Value of l_status_type is: ' || l_status_type, TRACE_PROMPT, TRACE_LEVEL);
2884 trace('Custom Labels Trace [INVLABPB.pls]: BEFORE update_history_record()', TRACE_PROMPT, TRACE_LEVEL);
2885 trace('Custom Labels Trace [INVLABPB.pls]: update WMS_LABEL_REQUEST_HIST record with job status, printer status, status type', TRACE_PROMPT, TRACE_LEVEL);
2886 END IF;
2887 update_history_record(
2888 p_label_request_id => l_variable_data(k).label_request_id
2889 --,p_status_flag => 'S'
2890 --Change made for 4179593
2891 ,p_status_flag => l_variable_data(k).label_status
2892 ,p_error_message => l_variable_data(k).error_message
2893 ,p_job_status => l_job_status
2894 ,p_printer_status => l_printer_status
2895 ,p_status_type => l_status_type);
2896 IF (l_debug = 1) THEN
2897 trace('Custom Labels Trace [INVLABPB.pls]: AFTER update_history_record()', TRACE_PROMPT, TRACE_LEVEL);
2898 END IF;
2899 ELSE
2900 -- Do nothing (Avoid sending request to the printer)
2901 IF (l_debug = 1) THEN
2902 trace('Custom Labels Trace [INVLABPB.pls]: At Breadcrumb 6 ', TRACE_PROMPT, TRACE_LEVEL);
2903 trace('Custom Labels Trace [INVLABPB.pls]: ERROR: Print Request Not Sent', TRACE_PROMPT, TRACE_LEVEL);
2904 END IF;
2905 END IF;
2906 ELSIF G_PROFILE_PRINT_MODE = 2 THEN
2907 -- Asynchronize Mode, calling write_xml to write into a xml file
2908
2909 IF (l_debug = 1) THEN
2910 trace('Custom Labels Trace [INVLABPB.pls]: At Breadcrumb 7 ', TRACE_PROMPT, TRACE_LEVEL);
2911 trace('Custom Labels Trace [INVLABPB.pls]: Inside TXN Driven -> Asynchronous mode code', TRACE_PROMPT, TRACE_LEVEL);
2912 trace('Custom Labels Trace [INVLABPB.pls]: l_variable_data(k).label_status: ' || l_variable_data(k).label_status, TRACE_PROMPT, TRACE_LEVEL);
2913 END IF;
2914 -- Proceed writing the XML file only if the label status is not an ERROR
2915 -- Bug Fix 5330030, put a NVL around l_variable_data(k).label_statu since null value for
2916 -- l_variable_data(k).label_status would indicate success.
2917 -- IF ( l_variable_data(k).label_status <> FND_API.G_RET_STS_ERROR ) THEN
2918 IF (nvl(l_variable_data(k).label_status, FND_API.G_RET_STS_SUCCESS) = FND_API.G_RET_STS_SUCCESS ) THEN
2919 IF (l_debug = 1) THEN
2920 trace('Custom Labels Trace [INVLABPB.pls]: At Breadcrumb 8 ', TRACE_PROMPT, TRACE_LEVEL);
2921 trace('Custom Labels Trace [INVLABPB.pls]: Before calling WRITE_XML() ', TRACE_PROMPT, TRACE_LEVEL);
2922 END IF;
2923 INV_PRINT_REQUEST.WRITE_XML(
2924 p_xml_content => g_xml_content
2925 ,p_request_id => l_variable_data(k).label_request_id
2926 ,x_return_status => l_return_status
2927 ,x_msg_count => l_msg_count
2928 ,x_msg_data => l_msg_data);
2929 IF (l_debug = 1) THEN
2930 trace('Custom Labels Trace [INVLABPB.pls]: At Breadcrumb 9 ', TRACE_PROMPT, TRACE_LEVEL);
2931 trace('Custom Labels Trace [INVLABPB.pls]: After calling WRITE_XML() ', TRACE_PROMPT, TRACE_LEVEL);
2932 trace('Custom Labels Trace [INVLABPB.pls]: Value of l_return_status is: ' || l_return_status, TRACE_PROMPT, TRACE_LEVEL);
2933 trace('Custom Labels Trace [INVLABPB.pls]: BEFORE update_history_record()', TRACE_PROMPT, TRACE_LEVEL);
2934 END IF;
2935 update_history_record(
2936 p_label_request_id => l_variable_data(k).label_request_id
2937 --,p_status_flag => 'S'
2938 --Change made for 4179593
2939 ,p_status_flag => l_variable_data(k).label_status
2940 ,p_error_message => l_variable_data(k).error_message
2941 ,p_outfile_name => G_PROFILE_PREFIX||l_variable_data(k).label_request_id ||'.xml'
2942 ,p_outfile_directory => G_PROFILE_OUT_DIR
2943 );
2944 IF (l_debug = 1) THEN
2945 trace('Custom Labels Trace [INVLABPB.pls]: At Breadcrumb 10 ', TRACE_PROMPT, TRACE_LEVEL);
2946 trace('Custom Labels Trace [INVLABPB.pls]: AFTER update_history_record()', TRACE_PROMPT, TRACE_LEVEL);
2947 trace('Custom Labels Trace [INVLABPB.pls]: Value of l_return_status is: ' || l_return_status, TRACE_PROMPT, TRACE_LEVEL);
2948 END IF;
2949 ELSE
2950 -- Do nothing (Avoid writing the XML file)
2951 IF (l_debug = 1) THEN
2952 trace('Custom Labels Trace [INVLABPB.pls]: At Breadcrumb 11 ', TRACE_PROMPT, TRACE_LEVEL);
2953 trace('Custom Labels Trace [INVLABPB.pls]: ERROR: XML File Not Written', TRACE_PROMPT, TRACE_LEVEL);
2954 END IF;
2955 END IF;
2956
2957 /*
2958 The following piece of the code is now moved after the call TO WRITE_XML() above.
2959 XML file will be written and the history record will now be updated only if there was
2960 no ERROR returned by the get_variable_data() from the relevant Label Type files.
2961
2962 IF nvl(l_return_status,'E') <> 'S' THEN
2963 update_history_record(
2964 p_label_request_id => l_variable_data(k).label_request_id
2965 ,p_status_flag => 'E');
2966 ELSE
2967 update_history_record(
2968 p_label_request_id => l_variable_data(k).label_request_id
2969 ,p_status_flag => 'S'
2970 ,p_outfile_name => G_PROFILE_PREFIX||l_variable_data(k).label_request_id ||'.xml'
2971 ,p_outfile_directory => G_PROFILE_OUT_DIR);
2972 */
2973
2974 -- New Printer Mode in Patchset J for Synchronize TCPIP printing
2975 ELSIF G_PROFILE_PRINT_MODE = 3 THEN
2976 -- Synchronize TCPIP Mode, calling SYNC_PRINT_TCPIP to print label through TCPIP mode
2977 IF (l_debug = 1) THEN
2978 trace('Custom Labels Trace [INVLABPB.pls]: At Breadcrumb 12 ', TRACE_PROMPT, TRACE_LEVEL);
2979 trace('Custom Labels Trace [INVLABPB.pls]: Inside TXN Driven -> TCP/IP Synchronous mode code', TRACE_PROMPT, TRACE_LEVEL);
2980 trace('Custom Labels Trace [INVLABPB.pls]: l_variable_data(k).label_status: ' || l_variable_data(k).label_status, TRACE_PROMPT, TRACE_LEVEL);
2981 END IF;
2982 -- Proceed TCP/IP printing only if the label status is not an ERROR
2983 -- Bug Fix 5330030, put a NVL around l_variable_data(k).label_statu since null value for
2984 -- l_variable_data(k).label_status would indicate success.
2985 -- IF ( l_variable_data(k).label_status <> FND_API.G_RET_STS_ERROR ) THEN
2986 IF (nvl(l_variable_data(k).label_status, FND_API.G_RET_STS_SUCCESS) = FND_API.G_RET_STS_SUCCESS ) THEN
2987 IF (l_debug = 1) THEN
2988 trace('Custom Labels Trace [INVLABPB.pls]: At Breadcrumb 13 ', TRACE_PROMPT, TRACE_LEVEL);
2989 trace('Custom Labels Trace [INVLABPB.pls]: Before calling SYNC_PRINT_TCPIP() ', TRACE_PROMPT, TRACE_LEVEL);
2990 END IF;
2991 INV_PRINT_REQUEST.SYNC_PRINT_TCPIP(
2992 p_xml_content => g_xml_content
2993 ,x_job_status => l_job_status
2994 ,x_printer_status => l_printer_status
2995 ,x_status_type => l_status_type
2996 ,x_return_status => l_return_status
2997 ,x_return_msg => l_msg_data);
2998 IF (l_debug = 1) THEN
2999 trace('Custom Labels Trace [INVLABPB.pls]: At Breadcrumb 14 ', TRACE_PROMPT, TRACE_LEVEL);
3000 trace('Custom Labels Trace [INVLABPB.pls]: After calling SYNC_PRINT_TCPIP() ', TRACE_PROMPT, TRACE_LEVEL);
3001 trace('Custom Labels Trace [INVLABPB.pls]: Value of l_job_status is: ' || l_job_status, TRACE_PROMPT, TRACE_LEVEL);
3002 trace('Custom Labels Trace [INVLABPB.pls]: Value of l_printer_status is: ' || l_printer_status, TRACE_PROMPT, TRACE_LEVEL);
3003 trace('Custom Labels Trace [INVLABPB.pls]: Value of l_status_type is: ' || l_status_type, TRACE_PROMPT, TRACE_LEVEL);
3004 trace('Custom Labels Trace [INVLABPB.pls]: BEFORE update_history_record()', TRACE_PROMPT, TRACE_LEVEL);
3005 trace('Custom Labels Trace [INVLABPB.pls]: update WMS_LABEL_REQUEST_HIST record with job status, printer status, status type', TRACE_PROMPT, TRACE_LEVEL);
3006 END IF;
3007 update_history_record(
3008 p_label_request_id => l_variable_data(k).label_request_id
3009 --,p_status_flag => 'S'
3010 --Change made for 4179593
3011 ,p_status_flag => l_variable_data(k).label_status
3012 ,p_error_message => l_variable_data(k).error_message
3013 ,p_job_status => l_job_status
3014 ,p_printer_status => l_printer_status
3015 ,p_status_type => l_status_type);
3016 ELSE
3017 -- Do nothing (Avoid sending TCP/IP request to printer)
3018 IF (l_debug = 1) THEN
3019 trace('Custom Labels Trace [INVLABPB.pls]: At Breadcrumb 15 ', TRACE_PROMPT, TRACE_LEVEL);
3020 trace('Custom Labels Trace [INVLABPB.pls]: ERROR: TCP/IP Print Request Not Sent', TRACE_PROMPT, TRACE_LEVEL);
3021 END IF;
3022 END IF;
3023 /*
3024 The following piece of the code is now moved after the call TO SYNC_PRINT_TCPIP() above.
3025 TCP/IP Print Request will be sent and the history record will now be updated only if there was
3026 were no ERROR returned by the get_variable_data() from the relevant Label Type files.
3027
3028 IF nvl(l_return_status,'E') <> 'S' THEN
3029 update_history_record(
3030 p_label_request_id => l_variable_data(k).label_request_id
3031 ,p_status_flag => 'E'
3032 ,p_error_message => l_msg_data);
3033 ELSE
3034 trace('update WMS_LABEL_REQUEST_HIST record with job status, printer status, status type', TRACE_PROMPT, TRACE_LEVEL);
3035 update_history_record(
3036 p_label_request_id => l_variable_data(k).label_request_id
3037 ,p_status_flag => 'S'
3038 ,p_job_status => l_job_status
3039 ,p_printer_status => l_printer_status
3040 ,p_status_type => l_status_type);
3041 END IF;
3042 */
3043 ELSE
3044 IF (l_debug = 1) THEN
3045 trace('wrong profile value for WMS_PRINT_MODE = '||G_PROFILE_PRINT_MODE, TRACE_PROMPT, TRACE_LEVEL);
3046 END IF;
3047 END IF;
3048 -- clear g_xml_content , ready for the next xml
3049 g_xml_content := '';
3050 END LOOP;
3051 END IF; --BUG#3055877
3052 END LOOP LabelTypeLoop;
3053 END LOOP TxnRowLoop;
3054 CLEAR_GLOBALS;
3055
3056 ELSIF p_print_mode = 2 THEN
3057 -- On Demand mode
3058 g_xml_content := '';
3059 <<InputParamLoop>>
3060 FOR i IN 1..p_input_param_rec.count() LOOP
3061 <<LabelTypeLoop2>>
3062 FOR j IN 1..l_label_types.count() LOOP
3063
3064 IF (l_debug = 1) THEN
3065 trace(' ## In Loop , for txn rec ' || i || ' , type '|| j, TRACE_PROMPT, TRACE_LEVEL);
3066 trace(' Going to get variable data ', TRACE_PROMPT, TRACE_LEVEL);
3067 END IF;
3068 /* Bug 3417450 Delete the global table g_label_request_tbl before calling
3069 * get_variable_data
3070 */
3071 g_label_request_tbl.DELETE;
3072
3073 IF (l_debug = 1) THEN
3074 trace('Custom Labels Trace [INVLABPB.pls]: At Breadcrumb 16 ', TRACE_PROMPT, TRACE_LEVEL);
3075 END IF;
3076 get_variable_data(
3077 x_variable_content => l_variable_data
3078 , x_msg_count => l_msg_count
3079 , x_msg_data => l_msg_data
3080 , x_return_status => l_return_status
3081 , p_label_type_info => l_label_types(j)
3082 , p_transaction_id => null
3083 , p_input_param => p_input_param_rec(i)
3084 , p_transaction_identifier => null
3085 );
3086
3087 IF (l_debug = 1) THEN
3088 trace(' got variable data', TRACE_PROMPT, TRACE_LEVEL);
3089 trace(' return status = '||l_return_status, TRACE_PROMPT, TRACE_LEVEL);
3090 trace(' x_msg_count='||l_msg_count , TRACE_PROMPT, TRACE_LEVEL);
3091 trace(' x_msg_data='||l_msg_data, TRACE_PROMPT, TRACE_LEVEL);
3092 END IF;
3093
3094 IF l_return_status <> 'S' THEN
3095 x_msg_data := l_msg_data;
3096 IF (l_debug = 1) THEN
3097 trace('Custom Labels Trace [INVLABPB.pls]: At Breadcrumb 17 ', TRACE_PROMPT, TRACE_LEVEL);
3098 trace('Custom Labels Trace [INVLABPB.pls]: Unexpected Error returned by get_variable_data()', TRACE_PROMPT, TRACE_LEVEL);
3099 END IF;
3100 RETURN;
3101 END IF;
3102
3103 /* Bug 3417450 Get the info for LPN Summary label, if a call was
3104 * made for LPN Content label and the table g_label_request_tbl
3105 * is already populated
3106 */
3107 IF l_label_types(j).label_type_id = 4 AND g_label_request_tbl.count() > 0 THEN
3108 IF (l_debug = 1) THEN
3109 trace('calling get_type_to_print, with label type id as 5 ',TRACE_PROMPT, TRACE_LEVEL);
3110 END IF;
3111 get_type_to_print(
3112 x_return_status => l_return_status
3113 , x_msg_count => l_msg_count
3114 , x_msg_data => l_msg_data
3115 , x_types_to_print => l_lpn_sum_rec
3116 , p_business_flow => NULL
3117 , p_label_type_id => 5
3118 , p_no_of_copies => p_no_of_copies
3119 , p_format_id => p_format_id
3120 , p_printer_name => p_printer_name);
3121 IF nvl(l_return_status,'E') <> 'S' THEN
3122 IF l_debug =1 THEN
3123 trace('returned error from get_type_to_print ' || l_msg_data,TRACE_PROMPT, TRACE_LEVEL);
3124 END IF;
3125 END IF;
3126 END IF;
3127
3128 FOR k IN 1..l_variable_data.count() LOOP
3129 IF (l_debug = 1) THEN
3130 trace(' write xml header,check_xml,populate_history ', TRACE_PROMPT, TRACE_LEVEL);
3131 END IF;
3132 /* Bug 3417450 */
3133 IF l_label_types(j).label_type_id = 4 THEN /* LPN Content label */
3134 IF (l_debug = 1) THEN
3135 trace('label type is LPN Content ',TRACE_PROMPT, TRACE_LEVEL);
3136 END IF;
3137 IF g_label_request_tbl.count() > 0 THEN
3138 IF (l_debug = 1) THEN
3139 trace('label request table is populated ' || g_label_request_tbl.count,TRACE_PROMPT, TRACE_LEVEL);
3140 END IF;
3141 cntr := g_label_request_tbl.first;
3142 /* Loop through each record in g_label_request_tbl to get
3143 * the header information for LPN SUmmary label type
3144 */
3145 LOOP
3146 IF (l_debug = 1) THEN
3147 trace(' g_label_request_tbl(cntr).label_type ' || g_label_request_tbl(cntr).label_type_id,TRACE_PROMPT, TRACE_LEVEL);
3148 trace(' g_label_request_tbl(cntr).label_request_id ' || g_label_request_tbl(cntr).label_request_id,TRACE_PROMPT, TRACE_LEVEL);
3149 END IF;
3150 IF (g_label_request_tbl(cntr).label_request_id = l_variable_data(k).label_request_id
3151 AND g_label_request_tbl(cntr).label_type_id = 5) THEN
3152 l_found_sum_rec := TRUE;
3153 END IF;
3154 EXIT WHEN cntr = g_label_request_tbl.last OR l_found_sum_rec = TRUE;
3155 cntr := g_label_request_tbl.next(cntr);
3156 END LOOP;
3157 END IF; -- end if count > 0
3158 IF l_found_sum_rec = TRUE THEN --this is an lpn summary label
3159 l_found_sum_rec := FALSE;
3160 IF (l_debug = 1) THEN
3161 trace('calling write_xml_header for lpn_sum_rec ',TRACE_PROMPT, TRACE_LEVEL);
3162 END IF;
3163 write_xml_header(l_lpn_sum_rec(1), l_variable_data(k).label_request_id);
3164 ELSE
3165 IF (l_debug = 1) THEN
3166 trace(' no summary record found. calling for lpn content label ',TRACE_PROMPT, TRACE_LEVEL);
3167 END IF;
3168 write_xml_header(l_label_types(j),l_variable_data(k).label_request_id);
3169 END IF;
3170 ELSE --label type is not 4
3171 IF (l_debug = 1) THEN
3172 trace('label type is not 4 ' || l_label_types(j).label_type_id,TRACE_PROMPT, TRACE_LEVEL);
3173 END IF;
3174 write_xml_header(l_label_types(j),l_variable_data(k).label_request_id);
3175 END IF;
3176 g_xml_content := g_xml_header || l_variable_data(k).label_content || LABELS_E;
3177 check_xml(g_xml_content);
3178 IF (l_debug = 1) THEN
3179 trace('Custom Labels Trace [INVLABPB.pls]: Before populate_history_record() ', TRACE_PROMPT, TRACE_LEVEL);
3180 trace('Custom Labels Trace [INVLABPB.pls]: l_variable_data(k).label_status: ' || l_variable_data(k).label_status, TRACE_PROMPT, TRACE_LEVEL);
3181 trace('Custom Labels Trace [INVLABPB.pls]: l_variable_data(k).error_message: ' || l_variable_data(k).error_message, TRACE_PROMPT, TRACE_LEVEL);
3182 END IF;
3183 populate_history_record(
3184 p_label_type_info => l_label_types(j)
3185 , p_label_content => g_xml_content
3186 , p_label_request_id => l_variable_data(k).label_request_id
3187 , p_status_flag => l_variable_data(k).label_status
3188 , p_error_message => l_variable_data(k).error_message
3189 );
3190
3191 IF (l_debug = 1) THEN
3192 trace('Custom Labels Trace [INVLABPB.pls]: After populate_history_record() ', TRACE_PROMPT, TRACE_LEVEL);
3193 END IF;
3194
3195 IF G_PROFILE_PRINT_MODE = 1 THEN
3196 -- Synchronize Mode, send request for each label
3197
3198 IF (l_debug = 1) THEN
3199 trace('Custom Labels Trace [INVLABPB.pls]: At Breadcrumb 18 ', TRACE_PROMPT, TRACE_LEVEL);
3200 trace('Custom Labels Trace [INVLABPB.pls]: Inside DEMAND Driven -> Synchronous mode code', TRACE_PROMPT, TRACE_LEVEL);
3201 trace('Custom Labels Trace [INVLABPB.pls]: l_variable_data(k).label_status: ' || l_variable_data(k).label_status, TRACE_PROMPT, TRACE_LEVEL);
3202 END IF;
3203 -- Proceed sending request for printing only if the label status is not an ERROR
3204
3205 -- Bug Fix 4552112, put a NVL around l_variable_data(k).label_statu since null value for
3206 -- l_variable_data(k).label_status would indicate success.
3207 --IF ( l_variable_data(k).label_status <> FND_API.G_RET_STS_ERROR ) THEN
3208 IF ( nvl(l_variable_data(k).label_status, FND_API.G_RET_STS_SUCCESS) = FND_API.G_RET_STS_SUCCESS ) THEN
3209 IF (l_debug = 1) THEN
3210 trace('Custom Labels Trace [INVLABPB.pls]: At Breadcrumb 19 ', TRACE_PROMPT, TRACE_LEVEL);
3211 trace('Custom Labels Trace [INVLABPB.pls]: Before calling SYNC_PRINT_REQUEST() ', TRACE_PROMPT, TRACE_LEVEL);
3212 END IF;
3213 INV_PRINT_REQUEST.SYNC_PRINT_REQUEST(
3214 p_xml_content => g_xml_content
3215 ,x_job_status => l_job_status
3216 ,x_printer_status => l_printer_status
3217 ,x_status_type => l_status_type);
3218 IF (l_debug = 1) THEN
3219 trace('Custom Labels Trace [INVLABPB.pls]: At Breadcrumb 20 ', TRACE_PROMPT, TRACE_LEVEL);
3220 trace('Custom Labels Trace [INVLABPB.pls]: After calling SYNC_PRINT_REQUEST() ', TRACE_PROMPT, TRACE_LEVEL);
3221 trace('Custom Labels Trace [INVLABPB.pls]: Value of l_job_status is: ' || l_job_status, TRACE_PROMPT, TRACE_LEVEL);
3222 trace('Custom Labels Trace [INVLABPB.pls]: Value of l_printer_status is: ' || l_printer_status, TRACE_PROMPT, TRACE_LEVEL);
3223 trace('Custom Labels Trace [INVLABPB.pls]: Value of l_status_type is: ' || l_status_type, TRACE_PROMPT, TRACE_LEVEL);
3224 trace('Custom Labels Trace [INVLABPB.pls]: BEFORE update_history_record()', TRACE_PROMPT, TRACE_LEVEL);
3225 trace('Custom Labels Trace [INVLABPB.pls]: update WMS_LABEL_REQUEST_HIST record with job status, printer status, status type', TRACE_PROMPT, TRACE_LEVEL);
3226 END IF;
3227 update_history_record(
3228 p_label_request_id => l_variable_data(k).label_request_id
3229 --,p_status_flag => 'S'
3230 --Change made for 4179593
3231 ,p_status_flag => l_variable_data(k).label_status
3232 ,p_error_message => l_variable_data(k).error_message
3233 ,p_job_status => l_job_status
3234 ,p_printer_status => l_printer_status
3235 ,p_status_type => l_status_type);
3236 IF (l_debug = 1) THEN
3237 trace('Custom Labels Trace [INVLABPB.pls]: After update_history_record() ', TRACE_PROMPT, TRACE_LEVEL);
3238 END IF;
3239 ELSE
3240 -- Do nothing (Avoid sending request to the printer)
3241 IF (l_debug = 1) THEN
3242 trace('Custom Labels Trace [INVLABPB.pls]: At Breadcrumb 21 ', TRACE_PROMPT, TRACE_LEVEL);
3243 trace('Custom Labels Trace [INVLABPB.pls]: ERROR: Print Request Not Sent', TRACE_PROMPT, TRACE_LEVEL);
3244 END IF;
3245 END IF;
3246 ELSIF G_PROFILE_PRINT_MODE = 2 THEN
3247 -- Asynchronize Mode, calling write_xml to write into a xml file
3248 IF (l_debug = 1) THEN
3249 trace('Custom Labels Trace [INVLABPB.pls]: At Breadcrumb 22', TRACE_PROMPT, TRACE_LEVEL);
3250 trace('Custom Labels Trace [INVLABPB.pls]: Inside DEMAND Driven -> Asynchronous mode code', TRACE_PROMPT, TRACE_LEVEL);
3251 trace('Custom Labels Trace [INVLABPB.pls]: l_variable_data(k).label_status: ' || l_variable_data(k).label_status, TRACE_PROMPT, TRACE_LEVEL);
3252 END IF;
3253 -- Proceed writing the XML file only if the label status is not an ERROR
3254 -- Bug Fix 5330030, put a NVL around l_variable_data(k).label_statu since null value for
3255 -- l_variable_data(k).label_status would indicate success.
3256 -- IF ( l_variable_data(k).label_status <> FND_API.G_RET_STS_ERROR ) THEN
3257 IF (nvl(l_variable_data(k).label_status, FND_API.G_RET_STS_SUCCESS) = FND_API.G_RET_STS_SUCCESS ) THEN
3258 IF (l_debug = 1) THEN
3259 trace('Custom Labels Trace [INVLABPB.pls]: At Breadcrumb 23 ', TRACE_PROMPT, TRACE_LEVEL);
3260 trace('Custom Labels Trace [INVLABPB.pls]: Before calling WRITE_XML() ', TRACE_PROMPT, TRACE_LEVEL);
3261 END IF;
3262 INV_PRINT_REQUEST.WRITE_XML(
3263 p_xml_content => g_xml_content
3264 ,p_request_id => l_variable_data(k).label_request_id
3265 ,x_return_status => l_return_status
3266 ,x_msg_count => l_msg_count
3267 ,x_msg_data => l_msg_data);
3268 IF (l_debug = 1) THEN
3269 trace('Custom Labels Trace [INVLABPB.pls]: At Breadcrumb 24 ', TRACE_PROMPT, TRACE_LEVEL);
3270 trace('Custom Labels Trace [INVLABPB.pls]: After calling WRITE_XML() ', TRACE_PROMPT, TRACE_LEVEL);
3271 trace('Custom Labels Trace [INVLABPB.pls]: Value of l_return_status is: ' || l_return_status, TRACE_PROMPT, TRACE_LEVEL);
3272 trace('Custom Labels Trace [INVLABPB.pls]: BEFORE update_history_record()', TRACE_PROMPT, TRACE_LEVEL);
3273 END IF;
3274 update_history_record(
3275 p_label_request_id => l_variable_data(k).label_request_id
3276 --,p_status_flag => 'S'
3277 --Change made for 4179593
3278 ,p_status_flag => l_variable_data(k).label_status
3279 ,p_error_message => l_variable_data(k).error_message
3280 ,p_outfile_name => G_PROFILE_PREFIX||l_variable_data(k).label_request_id ||'.xml'
3281 ,p_outfile_directory => G_PROFILE_OUT_DIR);
3282 IF (l_debug = 1) THEN
3283 trace('Custom Labels Trace [INVLABPB.pls]: At Breadcrumb 25 ', TRACE_PROMPT, TRACE_LEVEL);
3284 trace('Custom Labels Trace [INVLABPB.pls]: AFTER update_history_record()', TRACE_PROMPT, TRACE_LEVEL);
3285 trace('Custom Labels Trace [INVLABPB.pls]: Value of l_return_status is: ' || l_return_status, TRACE_PROMPT, TRACE_LEVEL);
3286 END IF;
3287 ELSE
3288 -- Do nothing (Avoid writing the XML file)
3289 IF (l_debug = 1) THEN
3290 trace('Custom Labels Trace [INVLABPB.pls]: At Breadcrumb 26 ', TRACE_PROMPT, TRACE_LEVEL);
3291 trace('Custom Labels Trace [INVLABPB.pls]: ERROR: XML File Not Written', TRACE_PROMPT, TRACE_LEVEL);
3292 END IF;
3293 END IF;
3294
3295 /*
3296 The following piece of the code is now moved after the call TO WRITE_XML() above.
3297 XML file will be written and the history record will now be updated only if there was
3298 no ERROR returned by the get_variable_data() from the relevant Label Type files.
3299
3300 IF nvl(l_return_status,'E') <> 'S' THEN
3301 update_history_record(
3302 p_label_request_id => l_variable_data(k).label_request_id
3303 ,p_status_flag => 'E');
3304 ELSE
3305 update_history_record(
3306 p_label_request_id => l_variable_data(k).label_request_id
3307 ,p_status_flag => 'S'
3308 ,p_outfile_name => G_PROFILE_PREFIX||l_variable_data(k).label_request_id ||'.xml'
3309 ,p_outfile_directory => G_PROFILE_OUT_DIR);
3310 */
3311
3312 -- New Printer Mode in Patchset J for Synchronize TCPIP printing
3313 ELSIF G_PROFILE_PRINT_MODE = 3 THEN
3314 -- Synchronize TCPIP Mode, calling SYNC_PRINT_TCPIP to print label through TCPIP mode
3315 IF (l_debug = 1) THEN
3316 trace('Custom Labels Trace [INVLABPB.pls]: At Breadcrumb 27 ', TRACE_PROMPT, TRACE_LEVEL);
3317 trace('Custom Labels Trace [INVLABPB.pls]: Inside DEMAND Driven -> TCP/IP Synchronous mode code', TRACE_PROMPT, TRACE_LEVEL);
3318 trace('Custom Labels Trace [INVLABPB.pls]: l_variable_data(k).label_status: ' || l_variable_data(k).label_status, TRACE_PROMPT, TRACE_LEVEL);
3319 END IF;
3320 -- Proceed TCP/IP printing only if the label status is not an ERROR
3321 -- Bug Fix 5330030, put a NVL around l_variable_data(k).label_statu since null value for
3322 -- l_variable_data(k).label_status would indicate success.
3323 --IF ( l_variable_data(k).label_status <> FND_API.G_RET_STS_ERROR ) THEN
3324 IF (nvl(l_variable_data(k).label_status, FND_API.G_RET_STS_SUCCESS) = FND_API.G_RET_STS_SUCCESS ) THEN
3325 IF (l_debug = 1) THEN
3326 trace('Custom Labels Trace [INVLABPB.pls]: At Breadcrumb 28 ', TRACE_PROMPT, TRACE_LEVEL);
3327 trace('Custom Labels Trace [INVLABPB.pls]: Before calling SYNC_PRINT_TCPIP() ', TRACE_PROMPT, TRACE_LEVEL);
3328 END IF;
3329 INV_PRINT_REQUEST.SYNC_PRINT_TCPIP(
3330 p_xml_content => g_xml_content
3331 ,x_job_status => l_job_status
3332 ,x_printer_status => l_printer_status
3333 ,x_status_type => l_status_type
3334 ,x_return_status => l_return_status
3335 ,x_return_msg => l_msg_data);
3336 IF (l_debug = 1) THEN
3337 trace('Custom Labels Trace [INVLABPB.pls]: At Breadcrumb 29 ', TRACE_PROMPT, TRACE_LEVEL);
3338 trace('Custom Labels Trace [INVLABPB.pls]: After calling SYNC_PRINT_TCPIP() ', TRACE_PROMPT, TRACE_LEVEL);
3339 trace('Custom Labels Trace [INVLABPB.pls]: Value of l_job_status is: ' || l_job_status, TRACE_PROMPT, TRACE_LEVEL);
3340 trace('Custom Labels Trace [INVLABPB.pls]: Value of l_printer_status is: ' || l_printer_status, TRACE_PROMPT, TRACE_LEVEL);
3341 trace('Custom Labels Trace [INVLABPB.pls]: Value of l_status_type is: ' || l_status_type, TRACE_PROMPT, TRACE_LEVEL);
3342 trace('Custom Labels Trace [INVLABPB.pls]: BEFORE update_history_record()', TRACE_PROMPT, TRACE_LEVEL);
3343 trace('Custom Labels Trace [INVLABPB.pls]: update WMS_LABEL_REQUEST_HIST record with job status, printer status, status type', TRACE_PROMPT, TRACE_LEVEL);
3344 END IF;
3345 update_history_record(
3346 p_label_request_id => l_variable_data(k).label_request_id
3347 --,p_status_flag => 'S'
3348 --Change made for 4179593
3349 ,p_status_flag => l_variable_data(k).label_status
3350 ,p_error_message => l_variable_data(k).error_message
3351 ,p_job_status => l_job_status
3352 ,p_printer_status => l_printer_status
3353 ,p_status_type => l_status_type);
3354 IF (l_debug = 1) THEN
3355 trace('Custom Labels Trace [INVLABPB.pls]: AFTER update_history_record()', TRACE_PROMPT, TRACE_LEVEL);
3356 END IF;
3357 ELSE
3358 -- Do nothing (Avoid sending TCP/IP request to printer)
3359 IF (l_debug = 1) THEN
3360 trace('Custom Labels Trace [INVLABPB.pls]: At Breadcrumb 30 ', TRACE_PROMPT, TRACE_LEVEL);
3361
3362 trace('Custom Labels Trace [INVLABPB.pls]: ERROR: TCP/IP Print Request Not Sent', TRACE_PROMPT, TRACE_LEVEL);
3363 END IF;
3364 END IF;
3365 /*
3366 The following piece of the code is now moved after the call TO SYNC_PRINT_TCPIP() above.
3367 TCP/IP Print Request will be sent and the history record will now be updated only if there was
3368 were no ERROR returned by the get_variable_data() from the relevant Label Type files.
3369
3370 IF nvl(l_return_status,'E') <> 'S' THEN
3371 update_history_record(
3372 p_label_request_id => l_variable_data(k).label_request_id
3373 ,p_status_flag => 'E'
3374 ,p_error_message => l_msg_data);
3375 ELSE
3376 trace('update WMS_LABEL_REQUEST_HIST record with job status, printer status, status type', TRACE_PROMPT, TRACE_LEVEL);
3377 update_history_record(
3378 p_label_request_id => l_variable_data(k).label_request_id
3379 ,p_status_flag => 'S'
3380 ,p_job_status => l_job_status
3381 ,p_printer_status => l_printer_status
3382 ,p_status_type => l_status_type);
3383 END IF;
3384 */
3385 ELSE
3386 IF (l_debug = 1) THEN
3387 trace('wrong profile value for WMS_PRINT_MODE = '||G_PROFILE_PRINT_MODE, TRACE_PROMPT, TRACE_LEVEL);
3388 END IF;
3389 END IF;
3390 --END IF;
3391 -- clear g_xml_content , ready for the next xml
3392 g_xml_content := '';
3393 END LOOP;
3394
3395 END LOOP LabelTypeLoop2;
3396 END LOOP InputParamLoop;
3397 ELSE
3398 IF (l_debug = 1) THEN
3399 trace(' Wrong value for p_print_mode ' || p_print_mode , TRACE_PROMPT, TRACE_LEVEL);
3400 END IF;
3401 END IF;
3402
3403 -- Added for R12 RFID Compliance project
3404 -- Reset the EPC_GROUP_ID
3405 EPC_GROUP_ID := null;
3406 EXCEPTION
3407 WHEN fnd_api.g_exc_error THEN
3408 IF (l_debug = 1) THEN
3409 trace(' Expected Error In '|| G_PKG_NAME||'.' || l_api_name, TRACE_PROMPT, TRACE_LEVEL);
3410 trace('ERROR CODE = ' || SQLCODE, TRACE_PROMPT, TRACE_LEVEL);
3411 trace('ERROR MESSAGE = ' || SQLERRM, TRACE_PROMPT, TRACE_LEVEL);
3412 END IF;
3413
3414 WHEN fnd_api.g_exc_unexpected_error THEN
3415 IF (l_debug = 1) THEN
3416 trace(' Unexpected Error In '|| G_PKG_NAME||'.' || l_api_name, TRACE_PROMPT, TRACE_LEVEL);
3417 trace('ERROR CODE = ' || SQLCODE, TRACE_PROMPT, TRACE_LEVEL);
3418 trace('ERROR MESSAGE = ' || SQLERRM, TRACE_PROMPT, TRACE_LEVEL);
3419 END IF;
3420
3421 WHEN others THEN
3422 IF (l_debug = 1) THEN
3423 trace(' Other Error In '|| G_PKG_NAME||'.' || l_api_name , TRACE_PROMPT, TRACE_LEVEL);
3424 trace('ERROR CODE = ' || SQLCODE, TRACE_PROMPT, TRACE_LEVEL);
3425 trace('ERROR MESSAGE = ' || SQLERRM, TRACE_PROMPT, TRACE_LEVEL);
3426 END IF;
3427
3428 END PRINT_LABEL;
3429
3430
3431 PROCEDURE RESUBMIT_LABEL_REQUEST(
3432 x_return_status OUT NOCOPY VARCHAR2
3433 , x_msg_count OUT NOCOPY NUMBER
3434 , x_msg_data OUT NOCOPY VARCHAR2
3435 , p_hist_label_request_id IN NUMBER
3436 , p_printer_name IN VARCHAR2
3437 , p_no_of_copy IN NUMBER
3438 ) IS
3439
3440 l_api_name VARCHAR2(25) := 'RESUBMIT_LABEL_REQUEST';
3441 l_return_status VARCHAR2(10);
3442 l_job_status VARCHAR2(2000); -- Bug 3328061 increased the size
3443 l_printer_status VARCHAR2(2000); -- Bug 3328061 increased the size
3444 l_status_type NUMBER;
3445
3446 l_history_rec WMS_LABEL_REQUESTS_HIST%ROWTYPE;
3447 CURSOR c_hist IS
3448 SELECT * FROM wms_label_requests_hist
3449 WHERE label_request_id = p_hist_label_request_id;
3450
3451 l_label_content LONG;
3452 l_printer_name VARCHAR2(50);
3453 l_no_of_copy NUMBER;
3454 l_msg_count NUMBER;
3455 l_msg_data VARCHAR2(4000);
3456 l_sysdate DATE;
3457
3458 BEGIN
3459 l_debug := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
3460
3461 IF (l_debug = 1) THEN
3462 trace('In resubmit label request',TRACE_PROMPT, TRACE_LEVEL);
3463 trace('resubmit, hist_request_id='||p_hist_label_request_id
3464 ||', printer_name='||p_printer_name
3465 ||', no_of_copy='||p_no_of_copy,TRACE_PROMPT, TRACE_LEVEL);
3466 END IF;
3467
3468 x_return_status := FND_API.G_RET_STS_SUCCESS;
3469
3470 get_profile_values;
3471 get_date_time_user;
3472
3473 OPEN c_hist;
3474 FETCH c_hist INTO l_history_rec;
3475 IF c_hist%NOTFOUND THEN
3476 IF (l_debug = 1) THEN
3477 trace('Can not find history record with request_id='||p_hist_label_request_id,TRACE_PROMPT, TRACE_LEVEL);
3478 END IF;
3479 CLOSE c_hist;
3480 RAISE fnd_api.g_exc_error;
3481 ELSE
3482 CLOSE c_hist;
3483 END IF;
3484 IF (l_debug = 1) THEN
3485 trace('Found history record',TRACE_PROMPT, TRACE_LEVEL);
3486 END IF;
3487 -- populate original request id and get new label_request_id;
3488 l_history_rec.original_request_id := p_hist_label_request_id;
3489 select wms_label_print_history_s.nextval into l_history_rec.label_request_id
3490 from dual;
3491 select sysdate into l_sysdate from dual;
3492 l_history_rec.request_date := l_sysdate;
3493 l_history_rec.creation_date := l_sysdate;
3494 l_history_rec.last_update_date := l_sysdate;
3495
3496 l_history_rec.request_user_id := fnd_global.user_id;
3497 l_history_rec.created_by := fnd_global.user_id;
3498 l_history_rec.last_updated_by := fnd_global.user_id;
3499
3500 IF (l_debug = 1) THEN
3501 trace('Set original request ID='||l_history_rec.original_request_id,TRACE_PROMPT, TRACE_LEVEL);
3502 trace('Set new label_request_id='||l_history_rec.label_request_id, TRACE_PROMPT, TRACE_LEVEL);
3503 END IF;
3504 l_history_rec.job_name := G_PROFILE_PREFIX||l_history_rec.label_request_id;
3505
3506 l_printer_name := p_printer_name;
3507 IF (p_printer_name IS NULL) OR
3508 (p_printer_name = l_history_rec.printer_name) THEN
3509 l_printer_name := NULL;
3510 END IF;
3511 l_no_of_copy := p_no_of_copy;
3512 IF (p_no_of_copy IS NULL) OR
3513 (p_no_of_copy<2) OR
3514 (p_no_of_copy = l_history_rec.no_of_copy) THEN
3515 l_no_of_copy := NULL;
3516 END IF;
3517
3518
3519 --Update the label_content
3520
3521 l_label_content := update_label_content(
3522 l_history_rec.label_content,
3523 l_history_rec.job_name,
3524 l_printer_name,
3525 l_no_of_copy);
3526
3527 l_history_rec.label_content := l_label_content;
3528 IF l_printer_name IS NOT NULL THEN
3529 l_history_rec.printer_name := l_printer_name;
3530 END IF;
3531 IF l_no_of_copy IS NOT NULL THEN
3532 l_history_rec.no_of_copy := l_no_of_copy;
3533 END IF;
3534
3535 --Fix for FP Bug: 4629816 Start
3536 -- Reset the default columns so that new values are recorded
3537
3538 l_history_rec.error_message := NULL;
3539 l_history_rec.status_flag := NULL;
3540 l_history_rec.job_status := NULL;
3541 l_history_rec.printer_status := NULL;
3542 l_history_rec.request_mode_code := G_PROFILE_PRINT_MODE;
3543 l_history_rec.outfile_directory := G_PROFILE_OUT_DIR;
3544 l_history_rec.outfile_name := l_history_rec.job_name || '.xml';
3545
3546 --Fix for FP Bug: 4629816 End
3547
3548 IF (l_debug = 1) THEN
3549 trace('Inserting into history table for the reprint request', TRACE_PROMPT, TRACE_LEVEL);
3550 END IF;
3551
3552 insert_history_record(l_history_rec);
3553
3554 IF (l_debug = 1) THEN
3555 trace('Send reprint request',TRACE_PROMPT, TRACE_LEVEL);
3556 END IF;
3557 IF G_PROFILE_PRINT_MODE = 1 THEN
3558 -- Synchronize Mode, send request for each label
3559 IF (l_debug = 1) THEN
3560 trace('Calling sync_print_req', TRACE_PROMPT, TRACE_LEVEL);
3561 END IF;
3562 INV_PRINT_REQUEST.SYNC_PRINT_REQUEST(
3563 p_xml_content => l_label_content
3564 , x_job_status => l_job_status
3565 , x_printer_status => l_printer_status
3566 , x_status_type => l_status_type
3567 );
3568
3569 IF (l_debug = 1) THEN
3570 trace('Status type returned from the sync_print_req ' || l_status_type, TRACE_PROMPT, TRACE_LEVEL);
3571 trace('update WMS_LABEL_REQUEST_HIST record with job status, printer status, status type', TRACE_PROMPT, TRACE_LEVEL);
3572 END IF;
3573 update_history_record(
3574 p_label_request_id => l_history_rec.label_request_id
3575 , p_status_flag => 'S'
3576 , p_job_status => l_job_status
3577 , p_printer_status => l_printer_status
3578 , p_status_type => l_status_type);
3579
3580 ELSIF G_PROFILE_PRINT_MODE = 2 THEN
3581 -- Asynchronize Mode, calling write_xml to write into a xml file
3582 IF (l_debug = 1) THEN
3583 trace('Calling WRITE_XML', TRACE_PROMPT, TRACE_LEVEL);
3584 END IF;
3585 INV_PRINT_REQUEST.WRITE_XML(
3586 p_xml_content => l_label_content
3587 , p_request_id => l_history_rec.label_request_id
3588 , x_return_status => l_return_status
3589 , x_msg_count => l_msg_count
3590 , x_msg_data => l_msg_data
3591 );
3592
3593 IF nvl(l_return_status,'E') <> 'S' THEN
3594 update_history_record(
3595 p_label_request_id => l_history_rec.label_request_id
3596 , p_status_flag => 'E'
3597 );
3598
3599 ELSE
3600 update_history_record(
3601 p_label_request_id => l_history_rec.label_request_id
3602 , p_status_flag => 'S'
3603 , p_outfile_name => l_history_rec.job_name ||'.xml'
3604 , p_outfile_directory => G_PROFILE_OUT_DIR
3605 );
3606
3607 END IF;
3608 -- New Printer Mode in Patchset J for Synchronize TCPIP printing
3609 ELSIF G_PROFILE_PRINT_MODE = 3 THEN
3610 -- Synchronize TCPIP Mode, calling SYNC_PRINT_TCPIP to print label through TCPIP mode
3611 IF (l_debug = 1) THEN
3612 trace('Calling SYNC_PRINT_TCPIP', TRACE_PROMPT, TRACE_LEVEL);
3613 END IF;
3614 INV_PRINT_REQUEST.SYNC_PRINT_TCPIP(
3615 p_xml_content => l_label_content
3616 , x_job_status => l_job_status
3617 , x_printer_status => l_printer_status
3618 , x_status_type => l_status_type
3619 , x_return_status => l_return_status
3620 , x_return_msg => l_msg_data
3621 );
3622
3623 IF (l_debug = 1) THEN
3624 trace('Called INV_PRINT_REQUEST.SYNC_PRINT_TCPIP', TRACE_PROMPT, TRACE_LEVEL);
3625 trace('x_return_status='||l_return_status||',x_return_msg='||l_msg_data, TRACE_PROMPT, TRACE_LEVEL);
3626 trace('x_job_status='||l_job_status||',x_printer_status='||l_printer_status||',x_status_type='||l_status_type, TRACE_PROMPT, TRACE_LEVEL);
3627 END IF;
3628 IF nvl(l_return_status,'E') <> 'S' THEN
3629 update_history_record(
3630 p_label_request_id => l_history_rec.label_request_id
3631 , p_status_flag => 'E'
3632 , p_error_message => l_msg_data
3633 );
3634
3635 ELSE
3636 trace('update WMS_LABEL_REQUEST_HIST record with job status, printer status, status type', TRACE_PROMPT, TRACE_LEVEL);
3637 update_history_record(
3638 p_label_request_id => l_history_rec.label_request_id
3639 , p_status_flag => 'S'
3640 , p_job_status => l_job_status
3641 , p_printer_status => l_printer_status
3642 , p_status_type => l_status_type);
3643
3644 END IF;
3645 END IF;
3646
3647
3648 EXCEPTION
3649 WHEN fnd_api.g_exc_error THEN
3650 IF (l_debug = 1) THEN
3651 trace(' Expected Error In '|| G_PKG_NAME||'.' || l_api_name, TRACE_PROMPT, TRACE_LEVEL);
3652 trace('ERROR CODE = ' || SQLCODE, TRACE_PROMPT, TRACE_LEVEL);
3653 trace('ERROR MESSAGE = ' || SQLERRM, TRACE_PROMPT, TRACE_LEVEL);
3654 END IF;
3655 x_return_status := fnd_api.g_ret_sts_error ;
3656
3657 WHEN fnd_api.g_exc_unexpected_error THEN
3658 IF (l_debug = 1) THEN
3659 trace(' Unexpected Error In '|| G_PKG_NAME||'.' || l_api_name, TRACE_PROMPT, TRACE_LEVEL);
3660 trace('ERROR CODE = ' || SQLCODE, TRACE_PROMPT, TRACE_LEVEL);
3661 trace('ERROR MESSAGE = ' || SQLERRM, TRACE_PROMPT, TRACE_LEVEL);
3662 END IF;
3663 x_return_status := fnd_api.g_ret_sts_error ;
3664
3665 WHEN others THEN
3666 IF (l_debug = 1) THEN
3667 trace(' Other Error In '|| G_PKG_NAME||'.' || l_api_name , TRACE_PROMPT, TRACE_LEVEL);
3668 trace('ERROR CODE = ' || SQLCODE, TRACE_PROMPT, TRACE_LEVEL);
3669 trace('ERROR MESSAGE = ' || SQLERRM, TRACE_PROMPT, TRACE_LEVEL);
3670 END IF;
3671 x_return_status := fnd_api.g_ret_sts_error ;
3672
3673 END RESUBMIT_LABEL_REQUEST;
3674
3675 /*************************************
3676 * Obtain Label Request Print Hist
3677 *************************************/
3678 PROCEDURE INV_LABEL_REQUESTS_REPRINT (
3679 x_label_rep_hist_inqs OUT NOCOPY t_genref,
3680 p_printer_Name IN VARCHAR2,
3681 p_bus_flow_Code IN NUMBER,
3682 p_label_type_Id IN NUMBER,
3683 p_lpn_Id IN NUMBER,
3684 p_Requests IN NUMBER,
3685 p_created_By IN NUMBER,
3686 x_Status OUT NOCOPY VARCHAR2,
3687 x_Message OUT NOCOPY VARCHAR2
3688 ) IS
3689 BEGIN
3690 l_debug := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
3691 x_Status := FND_API.G_RET_STS_SUCCESS;
3692 IF (l_debug = 1) THEN
3693 trace('Querying label request hist with Printer='|| p_printer_Name||
3694 ',BusFlow=' || p_bus_flow_Code ||
3695 ',LabelTypeID=' || p_label_type_Id ||
3696 ',LPNID=' || p_lpn_Id ||
3697 ',Requests=' || p_Requests ||
3698 ',User=' || p_created_By, 'LABEL_REPRINT_QUERY', 9);
3699 END IF;
3700
3701 OPEN x_label_rep_hist_inqs FOR
3702 select request_date, request_time, printer, label_format,
3703 lpn, item, bus_flow, label_type, label_request_id
3704 from
3705 (select to_char(wlrh.creation_date, 'DD-MON-YY') request_date,
3706 to_char(wlrh.creation_date, 'HH:MI:SS') request_time,
3707 wlrh.printer_name printer,
3708 wlf.label_format_name label_format,
3709 wlpn.license_plate_number lpn,
3710 msik.concatenated_segments item,
3711 mfglkup1.meaning bus_flow,
3712 mfglkup2.meaning label_type,
3713 wlrh.label_request_id label_request_id
3714 from wms_label_requests_hist wlrh,
3715 wms_label_formats wlf,
3716 wms_license_plate_numbers wlpn,
3717 mtl_system_items_kfv msik,
3718 mfg_lookups mfglkup1,
3719 mfg_lookups mfglkup2
3720 where wlrh.label_format_id = wlf.label_format_id (+)
3721 and wlrh.lpn_id = wlpn.lpn_id (+)
3722 and wlrh.inventory_item_id = msik.inventory_item_id (+)
3723 and wlrh.organization_id = msik.organization_id (+)
3724 and (wlrh.business_flow_code = mfglkup1.lookup_code (+)
3725 and mfglkup1.lookup_type(+) = 'WMS_BUSINESS_FLOW')
3726 and (wlrh.label_type_id = mfglkup2.lookup_code (+)
3727 and mfglkup2.lookup_type(+) = 'WMS_LABEL_TYPE')
3728 and nvl(wlrh.printer_name, '@@@') = nvl(p_printer_Name, nvl(wlrh.printer_name, '@@@'))
3729 and nvl(wlrh.business_flow_code, -99) = nvl(p_bus_flow_Code, nvl(wlrh.business_flow_code, -99))
3730 and nvl(wlrh.label_type_id, -99) = nvl(p_label_type_Id, nvl(wlrh.label_type_id, -99))
3731 and nvl(wlrh.lpn_id, -99) = nvl(p_lpn_Id, nvl(wlrh.lpn_id, -99))
3732 and wlrh.created_by = p_created_By
3733 order by wlrh.creation_date desc) wlrha
3734 where rownum <= p_Requests;
3735
3736 x_Message := 'Selection Criteria Returned Records';
3737 EXCEPTION
3738 WHEN no_data_found THEN
3739 x_Status := FND_API.G_RET_STS_UNEXP_ERROR;
3740 x_Message := 'Selection Criteria Returned No Records';
3741
3742 WHEN others THEN
3743 x_Status := FND_API.G_RET_STS_UNEXP_ERROR;
3744 x_Message := 'Selection Criteria Returned No Records';
3745
3746 END INV_LABEL_REQUESTS_REPRINT;
3747
3748
3749 -- Bug #3067059
3750 /**************************************
3751 * Checks if there is a GTIN defined for the
3752 * Item + UOM + Rev combination.
3753 * Also fetches GTIN and GTIN Desc. if it is
3754 * defined for the given Org, Item, UOM, Rev
3755 **************************************/
3756 PROCEDURE IS_ITEM_GTIN_ENABLED(
3757 x_return_status OUT NOCOPY VARCHAR2
3758 , x_gtin_enabled OUT NOCOPY BOOLEAN
3759 , x_gtin OUT NOCOPY VARCHAR2
3760 , x_gtin_desc OUT NOCOPY VARCHAR2
3761 , p_organization_id IN NUMBER
3762 , p_inventory_item_id IN NUMBER
3763 , p_unit_of_measure IN VARCHAR2
3764 , p_revision IN VARCHAR2
3765 )
3766 IS
3767 l_revision_id NUMBER;
3768 l_uom_code VARCHAR2(3);
3769 BEGIN
3770 trace('p_inventory_item_id : p_organization_id : p_unit_of_measure : p_revision'
3771 || p_inventory_item_id ||','||p_organization_id||','||p_unit_of_measure||','||p_revision, TRACE_PROMPT, TRACE_LEVEL);
3772 x_return_status := FND_API.G_RET_STS_SUCCESS;
3773 G_PROFILE_GTIN := FND_PROFILE.value('INV:GTIN_CROSS_REFERENCE_TYPE');
3774 trace('Profile INV:GTIN_CROSS_REFERENCE_TYPE : '||G_PROFILE_GTIN,TRACE_PROMPT, TRACE_LEVEL);
3775 IF(G_PROFILE_GTIN IS NOT NULL) THEN
3776
3777 SELECT MCR.CROSS_REFERENCE, MCR.DESCRIPTION, MCR.REVISION_ID, MCR.UOM_CODE
3778 INTO x_gtin, x_gtin_desc, l_revision_id, l_uom_code
3779 FROM MTL_CROSS_REFERENCES MCR, MTL_ITEM_REVISIONS_B MIR
3780 WHERE MIR.INVENTORY_ITEM_ID = MCR.INVENTORY_ITEM_ID
3781 AND MIR.REVISION_ID = NVL(MCR.REVISION_ID, MIR.REVISION_ID)
3782 AND MIR.INVENTORY_ITEM_ID = p_inventory_item_id
3783 AND (( mcr.org_independent_flag = 'Y' AND mcr.organization_id IS NULL AND MIR.organization_id = p_organization_id) OR (mcr.org_independent_flag = 'N' AND mcr.organization_id = p_organization_id AND mcr.organization_id = mir.organization_id))
3784 /* AND MIR.ORGANIZATION_ID = p_organization_id --Fixed in R12 */
3785 AND CROSS_REFERENCE_TYPE = G_PROFILE_GTIN
3786 AND NVL(MCR.UOM_CODE,NVL(p_unit_of_measure,'@@@')) = NVL(MCR.UOM_CODE,NVL(p_unit_of_measure,'@@@')) --NVL(p_unit_of_measure,'@@@') for bug 6795743
3787 AND MIR.REVISION = NVL(p_revision,MIR.REVISION);
3788
3789 trace('l_revision_id, l_uom_code : '||l_revision_id ||','||l_uom_code, TRACE_PROMPT, TRACE_LEVEL);
3790 IF(p_revision IS NOT NULL) THEN
3791 -- revision controlled item
3792 IF(l_revision_id IS NULL OR l_uom_code IS NULL) THEN
3793 x_gtin_enabled := FALSE;
3794 ELSE
3795 x_gtin_enabled := TRUE;
3796 END IF;
3797 ELSE
3798 -- non-revision controlled item
3799 IF(l_uom_code IS NULL) THEN
3800 x_gtin_enabled := FALSE;
3801 ELSE
3802 x_gtin_enabled := TRUE;
3803 END IF;
3804 END IF;
3805 END IF;
3806
3807 EXCEPTION
3808 WHEN no_data_found THEN
3809 -- this is an expected exception if no cross-reference values defined in mtl_cross_refererences
3810 x_gtin_enabled := FALSE;
3811 IF (l_debug = 1) THEN
3812 trace('No GTIN cross-reference defined ',TRACE_PROMPT, TRACE_LEVEL);
3813 END IF;
3814 NULL;
3815 WHEN OTHERS THEN
3816 -- When no data found or Mutiple Rows or others l_gtin_enabled := FASLE;
3817 x_gtin_enabled := FALSE;
3818 x_return_status := FND_API.G_RET_STS_ERROR;
3819 IF (l_debug = 1) THEN
3820 trace('error in IS_ITEM_GTIN_ENABLED',TRACE_PROMPT, TRACE_LEVEL);
3821 trace('ERROR CODE = ' || SQLCODE, TRACE_PROMPT, TRACE_LEVEL);
3822 trace('ERROR MESSAGE = ' || SQLERRM, TRACE_PROMPT, TRACE_LEVEL);
3823 END IF;
3824
3825 END IS_ITEM_GTIN_ENABLED;
3826
3827 --added for lpn status project to get the status of the lpn after the transaction has been commited
3828 FUNCTION get_txn_lpn_status
3829 (p_lpn_id IN NUMBER,
3830 p_transaction_id IN NUMBER,
3831 p_organization_id IN NUMBER,
3832 p_business_flow IN NUMBER)
3833 RETURN VARCHAR2 IS
3834 cursor wlc_cur is
3835 SELECT wlc.parent_lpn_id ,wlc.inventory_item_id , wlc.lot_number
3836 FROM wms_lpn_contents wlc
3837 WHERE wlc.parent_lpn_id IN
3838 (SELECT lpn_id
3839 FROM wms_license_plate_numbers plpn
3840 start with lpn_id = p_lpn_id
3841 connect by parent_lpn_id = prior lpn_id
3842 )
3843 ORDER BY wlc.serial_summary_entry DESC ;
3844
3845 CURSOR msnt_cur is
3846 SELECT msnt.status_id status_id
3847 FROM mtl_serial_numbers_temp msnt , mtl_serial_numbers msn
3848 WHERE msnt.transaction_temp_id = p_transaction_id
3849 AND msnt.status_id is not null
3850 UNION
3851 SELECT msn.status_id status_id
3852 FROM mtl_serial_numbers_temp msnt , mtl_serial_numbers msn
3853 WHERE msnt.transaction_temp_id = p_transaction_id
3854 AND msn.serial_number BETWEEN msnt.fm_serial_number AND msnt.to_serial_number
3855 AND msn.current_organization_id = p_organization_id
3856 AND msnt.status_id is NULL;
3857
3858 CURSOR msn_cur(l_lpn_id NUMBER) IS
3859 SELECT status_id
3860 FROM mtl_serial_numbers msn
3861 WHERE msn.lpn_id = l_lpn_id
3862 AND msn.current_organization_id = p_organization_id
3863 AND serial_number not in (select serial_number from
3864 mtl_serial_numbers msn1,mtl_serial_numbers_temp msnt
3865 where msnt.transaction_temp_id = p_transaction_id
3866 and msn1.serial_number BETWEEN msnt.fm_serial_number AND msnt.to_serial_number);
3867
3868 CURSOR mmtt_cur(l_inventory_item_id NUMBER , l_lot_number VARCHAR2) IS
3869 SELECT mmtt.organization_id , mmtt.subinventory_code , mmtt.locator_id ,
3870 NVL(mmtt.lpn_id,mmtt.content_lpn_id) lpn_id , mmtt.transaction_action_id
3871 FROM mtl_material_transactions_temp mmtt
3872 WHERE mmtt.transaction_temp_id =p_transaction_id
3873 AND mmtt.inventory_item_id = l_inventory_item_id
3874 AND Nvl(mmtt.item_lot_control_code,-99) <> 2
3875 UNION
3876 SELECT mmtt.organization_id , mmtt.subinventory_code , mmtt.locator_id ,
3877 NVL(mmtt.lpn_id,mmtt.content_lpn_id) lpn_id , mmtt.transaction_action_id
3878 FROM mtl_material_transactions_temp mmtt , mtl_transaction_lots_temp mtlt
3879 WHERE mmtt.transaction_temp_id =p_transaction_id
3880 AND mmtt.inventory_item_id = l_inventory_item_id
3881 AND Nvl(mmtt.item_lot_control_code,-99) = 2
3882 AND mtlt.transaction_temp_id = mmtt.transaction_temp_id
3883 AND nvl(mtlt.lot_number,'@@@@') = nvl(l_lot_number,'@@@@');
3884
3885 l_return_status_id NUMBER;
3886 l_return_status_code VARCHAR2(30);
3887 l_organization_id NUMBER;
3888 l_subinventory_code VARCHAR2(30);
3889 l_locator_id NUMBER ;
3890 l_lpn_context NUMBER;
3891 l_counter NUMBER := 0;
3892 l_src_status NUMBER;
3893 l_src_locator_id NUMBER;
3894 l_src_organization_id NUMBER;
3895 l_src_subinventory_code VARCHAR2(30);
3896 l_status_id NUMBER;
3897 l_lpn_id NUMBER;
3898 l_transaction_action_id NUMBER;
3899 l_src_lpn_id NUMBER;
3900 l_query_mmtt NUMBER := 1;
3901 l_serial_status_enabled NUMBER := 0;
3902 l_serial_controlled NUMBER := 0;
3903 l_lot_number VARCHAR2(30) := NULL;
3904 l_inventory_item_id NUMBER;
3905 BEGIN
3906 l_organization_id := p_organization_id;
3907
3908 IF(l_debug=1) THEN
3909 trace('inside get_txn_lpn_status', TRACE_PROMPT, TRACE_LEVEL);
3910 END IF;
3911
3912 SELECT wlpn.lpn_context , wlpn.subinventory_code , wlpn.locator_id
3913 INTO l_lpn_context , l_subinventory_code , l_locator_id
3914 FROM wms_license_plate_numbers wlpn
3915 WHERE wlpn.lpn_id = p_lpn_id;
3916 IF l_lpn_context IN (WMS_Container_PUB.LPN_CONTEXT_PREGENERATED,
3917 WMS_Container_PUB.LPN_CONTEXT_VENDOR,
3918 WMS_Container_PUB.LPN_CONTEXT_STORES,
3919 WMS_Container_PUB.LPN_CONTEXT_INTRANSIT ,
3920 WMS_Container_PUB.LPN_CONTEXT_PACKING,
3921 WMS_Container_PUB.LPN_CONTEXT_WIP,
3922 WMS_Container_PUB.LPN_CONTEXT_RCV
3923 )
3924 OR (p_transaction_id is NULL)
3925 OR (p_business_flow in (1,2,3,4))THEN
3926 -- no need to check src status for these transaction so calling get_lpn_status
3927
3928 IF(l_debug=1) THEN
3929 trace('calling get_lpn_status', TRACE_PROMPT, TRACE_LEVEL);
3930 END IF;
3931 INV_MATERIAL_STATUS_GRP.get_lpn_status
3932 (
3933 p_organization_id =>l_organization_id,
3934 p_lpn_id =>p_lpn_id ,
3935 p_sub_code =>l_subinventory_code ,
3936 p_loc_id =>l_locator_id ,
3937 p_lpn_context=>l_lpn_context ,
3938 x_return_status_id=> l_return_status_id,
3939 x_return_status_code=> l_return_status_code
3940 );
3941 RETURN l_return_status_code;
3942
3943
3944 ELSE
3945 --need to check source status for all other type of stauses
3946
3947 FOR l_wlc_cur in wlc_cur LOOP
3948 --inside wlc loop
3949 l_serial_controlled := 0;
3950 l_serial_status_enabled := 0;
3951 l_src_status := NULL;
3952 l_transaction_action_id := NULL;
3953 IF inv_cache.set_item_rec(l_organization_id, l_wlc_cur.inventory_item_id) THEN
3954 IF (inv_cache.item_rec.serial_number_control_code in (2,5)) THEN
3955 l_serial_controlled := 1; -- Item is serial controlled
3956 END IF;
3957 IF (NVL(inv_cache.item_rec.serial_status_enabled,'Y') = 'Y') THEN
3958 l_serial_status_enabled := 1;
3959 END IF;
3960 END IF;
3961 FOR l_mmtt_cur in mmtt_cur(l_wlc_cur.inventory_item_id , l_wlc_cur.lot_number) loop
3962 l_transaction_action_id := l_mmtt_cur.transaction_action_id;
3963 IF (l_serial_controlled = 1) THEN
3964 IF (l_serial_status_enabled = 1) THEN
3965 FOR l_msnt_cur IN msnt_cur LOOP
3966 --item is serial controlled and status is also enabled therefore calling msnt_cur
3967 l_counter := l_counter + 1;
3968 l_return_status_id := l_msnt_cur.status_id;
3969 IF(l_counter = 1 ) THEN
3970 IF(l_debug=1) THEN
3971 trace('status returned for first time from 1 = '||l_return_status_id, TRACE_PROMPT, TRACE_LEVEL);
3972 END IF;
3973 l_status_id := l_return_status_id;
3974 END IF;
3975 IF (l_return_status_id <> l_status_id) THEN
3976 l_return_status_id := -1;
3977 IF(l_debug=1) THEN
3978 trace('lpn has status mixed so exiting 1', TRACE_PROMPT, TRACE_LEVEL);
3979 END IF;
3980 EXIT;
3981 END IF;
3982 END LOOP;
3983 END IF;
3984
3985
3986
3987 ELSE
3988 IF l_transaction_action_id IN (inv_globals.G_ACTION_SUBXFR,
3989 inv_globals.G_ACTION_ORGXFR,
3990 inv_globals.G_ACTION_STGXFR,
3991 inv_globals.G_ACTION_CONTAINERPACK,
3992 inv_globals.G_ACTION_CONTAINERUNPACK) THEN
3993 BEGIN
3994 IF(l_debug=1) THEN
3995 trace(' querying moqd for the source status', TRACE_PROMPT, TRACE_LEVEL);
3996 trace ('source organization_id = '||l_mmtt_cur.organization_id, TRACE_PROMPT, TRACE_LEVEL);
3997 trace ('source subinventory = '||l_mmtt_cur.subinventory_code, TRACE_PROMPT, TRACE_LEVEL);
3998 trace ('source locator_id = '||l_mmtt_cur.locator_id, TRACE_PROMPT, TRACE_LEVEL);
3999 trace ('source lpn is = '||l_mmtt_cur.lpn_id, TRACE_PROMPT, TRACE_LEVEL);
4000 END IF;
4001 SELECT moqd.status_id into l_src_status
4002 FROM mtl_onhand_quantities_detail moqd
4003 WHERE moqd.inventory_item_id = l_wlc_cur.inventory_item_id
4004 AND moqd.organization_id = l_mmtt_cur.organization_id
4005 AND nvl(moqd.lpn_id,-9999) = Nvl(l_mmtt_cur.lpn_id,-9999)
4006 AND moqd.subinventory_code = l_mmtt_cur.subinventory_code
4007 AND NVL(moqd.locator_id,-9999) = NVL(l_mmtt_cur.locator_id,-9999)
4008 AND NVL(moqd.lot_number,'@@@@') = NVL(l_wlc_cur.lot_number,'@@@@')
4009 AND ROWNUM = 1;
4010 EXCEPTION
4011 WHEN No_Data_Found THEN
4012 l_src_status := NULL;
4013 if(l_debug = 1)THEN
4014 trace('here p_src_status_id =>' || l_src_status, TRACE_PROMPT, TRACE_LEVEL);
4015 END IF;
4016 END;
4017 END IF;
4018 END IF;
4019
4020
4021 END LOOP;
4022 IF (NVL(l_return_status_id , 0)<>-1)THEN
4023 --came here to check for the rest of the data
4024 IF(l_debug=1) THEN
4025 trace(' came here 1', TRACE_PROMPT, TRACE_LEVEL);
4026 END IF;
4027 IF(l_serial_controlled<>1) THEN
4028 l_counter := l_counter + 1;
4029 IF(l_debug=1) THEN
4030 trace('not serial controlled so calling INV_MATERIAL_STATUS_GRP.get_default_status', TRACE_PROMPT, TRACE_LEVEL);
4031 END IF;
4032
4033 l_return_status_id := INV_MATERIAL_STATUS_GRP.get_default_status --calling function to get the MOQD status
4034 (p_organization_id => l_organization_id,
4035 p_inventory_item_id => l_wlc_cur.inventory_item_id,
4036 p_sub_code => l_subinventory_code,
4037 p_loc_id => l_locator_id,
4038 p_lot_number => l_wlc_cur.lot_number,
4039 p_lpn_id => p_lpn_id,
4040 p_transaction_action_id=> l_transaction_action_id,
4041 p_src_status_id => l_src_status);
4042 --END;
4043 IF(l_counter = 1) THEN
4044 IF(l_debug=1) THEN
4045 trace('status returned for first time from 2= '||l_return_status_id, TRACE_PROMPT, TRACE_LEVEL);
4046 END IF;
4047 l_status_id := l_return_status_id;
4048 END IF;
4049 IF (l_return_status_id <> l_status_id) THEN
4050 l_return_status_id := -1;
4051 IF(l_debug=1) THEN
4052 trace('lpn has status mixed so exiting 2', TRACE_PROMPT, TRACE_LEVEL);
4053 END IF ;
4054 END IF;
4055 ELSE
4056 IF(l_serial_status_enabled = 1) THEN
4057 FOR l_msn_cur IN msn_cur(l_wlc_cur.parent_lpn_id) LOOP
4058 l_counter := l_counter + 1;
4059 l_return_status_id := l_msn_cur.status_id;
4060 IF (l_counter = 1) THEN
4061 IF(l_debug=1) THEN
4062 trace('status returned for first time from 3 = '||l_return_status_id, TRACE_PROMPT, TRACE_LEVEL);
4063 END IF;
4064 l_status_id := l_return_status_id;
4065 END IF;
4066 IF (l_return_status_id <> l_status_id) THEN
4067 IF(l_debug=1) THEN
4068 trace('lpn has status mixed so exiting 3', TRACE_PROMPT, TRACE_LEVEL);
4069 END IF;
4070 l_return_status_id := -1;
4071 EXIT;
4072 END IF;
4073 END LOOP;
4074 END IF;
4075 END IF;
4076 END IF;
4077 IF (l_return_status_id = -1)THEN
4078 EXIT;
4079 END IF;
4080 END LOOP;
4081 END IF;
4082 IF (l_return_status_id IS NOT NULL AND l_return_status_id <>-1) THEN
4083 BEGIN
4084 SELECT status_code into l_return_status_code
4085 from mtl_material_statuses
4086 where status_id = l_return_status_id;
4087 EXCEPTION
4088 WHEN NO_DATA_FOUND THEN
4089 l_return_status_code := NULL;
4090 END;
4091
4092 ELSIF l_return_status_id = -1 THEN
4093 l_return_status_code := fnd_message.get_string('WMS', 'WMS_LPN_STATUS_MIXED');
4094 ELSE
4095 l_return_status_code := NULL;
4096 END IF;
4097
4098
4099 RETURN l_return_status_code;
4100 END get_txn_lpn_status ;
4101
4102
4103 END INV_LABEL;