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