[Home] [Help]
PACKAGE BODY: APPS.INV_LABEL_PVT8
Source
1 PACKAGE BODY INV_LABEL_PVT8 AS
2 /* $Header: INVLAP8B.pls 120.20 2008/03/18 10:47:43 anagupta ship $ */
3
4
5 LABEL_B CONSTANT VARCHAR2(50) := '<label';
6 LABEL_E CONSTANT VARCHAR2(50) := '</label>'||fnd_global.local_chr(10);
7 VARIABLE_B CONSTANT VARCHAR2(50) := '<variable name= "';
8 VARIABLE_E CONSTANT VARCHAR2(50) := '</variable>'||fnd_global.local_chr(10);
9 TAG_E CONSTANT VARCHAR2(50) := '>'||fnd_global.local_chr(10);
10 G_DATE_FORMAT_MASK VARCHAR2(100) := INV_LABEL.G_DATE_FORMAT_MASK;
11 NULL_NUM CONSTANT NUMBER := -9999;
12 NULL_VAR CONSTANT VARCHAR2(10) := '$%#!@^&*';
13 l_debug NUMBER;
14
15 g_get_hash_for_insert NUMBER := 1;
16 g_get_hash_for_retrieve NUMBER := 0;
17 g_count_custom_sql NUMBER := 0;
18
19 TYPE column_element_tp IS RECORD
20 (column_name VARCHAR2(60),
21 column_content VARCHAR2(2000));
22
23
24 TYPE column_elements_tab_tp IS TABLE OF column_element_tp
25 INDEX BY BINARY_INTEGER;
26
27 g_column_elements_table column_elements_tab_tp;
28
29 TYPE field_element_tp IS RECORD
30 (column_name_with_count VARCHAR2(60),
31 variable_name VARCHAR2(60),
32 sql_stmt VARCHAR2(4000));
33
34
35 TYPE field_elements_tab_tp IS TABLE OF field_element_tp
36 INDEX BY BINARY_INTEGER;
37
38 g_field_elements_table field_elements_tab_tp;
39
40 TYPE carton_count IS RECORD
41 ( delivery_id NUMBER
42 , carton_count NUMBER
43 , carton_index NUMBER);
44
45 TYPE carton_count_tb IS TABLE OF carton_count INDEX BY BINARY_INTEGER;
46
47 g_carton_tb carton_count_tb;
48
49 PROCEDURE trace(p_message IN VARCHAR2) IS
50 BEGIN
51 inv_label.trace(p_message, 'LABEL_SHIP_CONT');
52 END trace;
53
54 /* Private API to get/set hash value for a column */
55 FUNCTION get_column_hash_value (p_input_string VARCHAR2)
56 RETURN NUMBER IS
57 l_return_hash_value NUMBER;
58 l_orig_hash_value NUMBER;
59 l_hash_base NUMBER := 2;
60 l_hash_size NUMBER := Power(2, 20);
61 BEGIN
62 l_orig_hash_value := dbms_utility.get_hash_value
63 (
64 name => p_input_string
65 ,base => l_hash_base
66 ,hash_size => l_hash_size
67 );
68
69 IF g_column_elements_table.exists(l_orig_hash_value) AND
70 g_column_elements_table(l_orig_hash_value).column_name = p_input_string THEN
71
72
73 l_return_hash_value := l_orig_hash_value;
74
75 ELSIF g_column_elements_table.exists(l_orig_hash_value) THEN
76 -- hash collision
77
78 LOOP
79 l_orig_hash_value := l_orig_hash_value + 1;
80
81 IF l_orig_hash_value > l_hash_size THEN
82 -- Don't need to check hash overflow here because the hash range
83 -- for sure is greater than the number of columns.
84 l_orig_hash_value := l_hash_base;
85 END IF;
86
87 IF g_column_elements_table.exists(l_orig_hash_value) AND
88 g_column_elements_table(l_orig_hash_value).column_name = p_input_string THEN
89
90 EXIT;
91 ELSIF NOT g_column_elements_table.exists(l_orig_hash_value) THEN
92
93 EXIT;
94 END IF;
95
96 END LOOP;
97
98 l_return_hash_value := l_orig_hash_value;
99
100 ELSE
101
102 l_return_hash_value := l_orig_hash_value;
103 END IF;
104
105 g_column_elements_table(l_return_hash_value).column_name := p_input_string;
106
107 RETURN l_return_hash_value;
108
109 END get_column_hash_value;
110
111 /* Private API to get/set hash value for a field */
112 FUNCTION get_field_hash_value (p_input_string VARCHAR2, p_get_hash_mode NUMBER)
113 RETURN NUMBER IS
114 l_return_hash_value NUMBER;
115 l_orig_hash_value NUMBER;
116 l_hash_base NUMBER := 2;
117 l_hash_size NUMBER := Power(2, 20);
118 BEGIN
119 l_orig_hash_value := dbms_utility.get_hash_value
120 (
121 name => p_input_string
122 ,base => l_hash_base
123 ,hash_size => l_hash_size
124 );
125
126 IF g_field_elements_table.exists(l_orig_hash_value) AND
127 g_field_elements_table(l_orig_hash_value).column_name_with_count = p_input_string THEN
128 l_return_hash_value := l_orig_hash_value;
129
130 ELSIF g_field_elements_table.exists(l_orig_hash_value) THEN
131 -- hash collision
132 LOOP
133 l_orig_hash_value := l_orig_hash_value + 1;
134
135 IF l_orig_hash_value > l_hash_size THEN
136 -- Don't need to check hash overflow here because the hash range
137 -- for sure is greater than the number of columns.
138 l_orig_hash_value := l_hash_base;
139 END IF;
140
141 IF g_field_elements_table.exists(l_orig_hash_value) AND
142 g_field_elements_table(l_orig_hash_value).column_name_with_count = p_input_string THEN
143
144 EXIT;
145 ELSIF NOT g_field_elements_table.exists(l_orig_hash_value) THEN
146
147 EXIT;
148 END IF;
149
150 END LOOP;
151
152 l_return_hash_value := l_orig_hash_value;
153
154 ELSE
155
156 l_return_hash_value := l_orig_hash_value;
157 END IF;
158
159 IF p_get_hash_mode = g_get_hash_for_insert THEN
160 g_field_elements_table(l_return_hash_value).column_name_with_count := p_input_string;
161 END IF;
162
163 RETURN l_return_hash_value;
164
165 END get_field_hash_value;
166
167 /* Private API to get the variable name for a given column_name */
168 FUNCTION get_variable_name(p_column_name IN VARCHAR2,
169 p_row_index IN NUMBER, p_format_id IN NUMBER) RETURN VARCHAR2 IS
170 l_variable_name VARCHAR2(100);
171 BEGIN
172 --trace('Begin get_variable_name for column '|| p_column_name || ' with p_row_index : ' || p_row_index );
173
174 BEGIN
175 l_variable_name := g_field_elements_table(get_field_hash_value(p_column_name||(p_row_index+1), g_get_hash_for_retrieve)).variable_name;
176 EXCEPTION
177 WHEN OTHERS THEN
178 l_variable_name := NULL;
179 END;
180 --IF l_variable_name is not NULL THEN
181 -- trace('get variable name '||l_variable_name||' for column '|| p_column_name);
182 --END IF;
183
184 RETURN l_variable_name;
185
186 END get_variable_name;
187
188 PROCEDURE build_format_fields_structure(p_label_format_id NUMBER) IS
189
190 CURSOR c_label_field_var IS
191 SELECT wlf.column_name,
192 wlf.sql_stmt,
193 wlfv.field_variable_name
194 FROM wms_label_field_variables wlfv,
195 wms_label_fields_vl wlf
196 WHERE wlfv.label_format_id = p_label_format_id
197 AND wlfv.label_field_id = wlf.label_field_id
198 ORDER BY wlf.column_name, wlfv.field_variable_name;
199
200 l_label_field_var c_label_field_var%ROWTYPE;
201 l_column_count NUMBER := 1;
202 l_prev_column_name VARCHAR2(60) := '';
203
204 BEGIN
205
206 OPEN c_label_field_var;
207 LOOP
208 FETCH c_label_field_var INTO l_label_field_var;
209 EXIT WHEN c_label_field_var%notfound;
210
211 IF l_prev_column_name IS NULL OR
212 l_prev_column_name <> l_label_field_var.column_name THEN
213 l_prev_column_name := l_label_field_var.column_name;
214 l_column_count := 1;
215 ELSE
216 l_column_count := l_column_count + 1;
217 END IF;
218
219 -- build the hash table with column_name concatenate count as key
220 -- trace('*********** insert into hash table '|| l_label_field_var.column_name ||l_column_count||' ************ ' || l_label_field_var.field_variable_name);
221 g_field_elements_table(get_field_hash_value(l_label_field_var.column_name||l_column_count, g_get_hash_for_insert)).variable_name := l_label_field_var.field_variable_name;
222
223 IF l_label_field_var.column_name = 'sql_stmt' THEN
224 g_count_custom_sql := g_count_custom_sql + 1; -- Added for Bug#4179391
225 g_field_elements_table(get_field_hash_value(l_label_field_var.column_name||l_column_count, g_get_hash_for_insert)).sql_stmt := l_label_field_var.sql_stmt;
226 END IF;
227
228 END LOOP;
229
230 CLOSE c_label_field_var;
231
232 END build_format_fields_structure;
233
234 ---------------------------------------------------------------------------------------------
235 -- Project: 'Custom Labels' (A 11i10+ Project) |
236 -- Author: Dinesh ([email protected]) |
237 -- Change Description: |
238 -- This function get_sql_for_variable() is newly added for the Custom Labels project to |
239 -- fetch the SQL statement from the PL/SQL table. |
240 ---------------------------------------------------------------------------------------------
241 FUNCTION get_sql_for_variable(p_column_name IN VARCHAR2, p_row_index IN NUMBER, p_format_id IN NUMBER)
242 RETURN VARCHAR2
243 IS
244
245 lv_sql_stmt VARCHAR2(4000);
246
247 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
248 BEGIN
249
250 BEGIN
251 lv_sql_stmt := g_field_elements_table(get_field_hash_value(p_column_name||(p_row_index+1), g_get_hash_for_retrieve)).sql_stmt;
252 IF (l_debug = 1) THEN
253 trace(' Inside get_sql_for_variable() lv_sql_stmt is: '|| lv_sql_stmt);
254 END IF;
255 EXCEPTION
256 WHEN OTHERS THEN
257 lv_sql_stmt := NULL;
258 IF (l_debug = 1) THEN
259 trace(' Inside Exception Block of get_sql_for_variable() ');
260 END IF;
261 END;
262 RETURN lv_sql_stmt;
263
264 END get_sql_for_variable;
265
266 /* Set value for total number of cartons for a delivery
267 Get delivery_id for a certain cartonization_id
268 Increment the total count for that delivery
269 This is called by INV_LABEL for each cartonization_id
270 */
271 PROCEDURE set_carton_count(p_cartonization_id NUMBER) IS
272
273 CURSOR c_delivery(v_cartonization_id NUMBER) IS
274 SELECT distinct wda.delivery_id
275 FROM wsh_delivery_assignments wda
276 , wsh_delivery_details wdd
277 , mtl_material_transactions_temp mmtt
278 WHERE mmtt.cartonization_id = v_cartonization_id
279 AND mmtt.move_order_line_id = wdd.move_order_line_id
280 AND wda.delivery_detail_id = wdd.delivery_detail_id;
281 l_delivery_id NUMBER;
282 i NUMBER;
283 l_found VARCHAR2(1);
284
285 BEGIN
286 --trace('In set_carton_count p_cartonization_id ='||p_cartonization_id);
287 OPEN c_delivery(p_cartonization_id);
288 FETCH c_delivery INTO l_delivery_id;
289 CLOSE c_delivery;
290
291 l_found := 'N';
292 FOR i IN 1..g_carton_tb.count LOOP
293 IF g_carton_tb(i).delivery_id = l_delivery_id THEN
294 g_carton_tb(i).carton_count := g_carton_tb(i).carton_count + 1;
295 g_carton_tb(i).carton_index := 1;
296 l_found := 'Y';
297 EXIT;
298 END IF;
299 END LOOP;
300
301 IF ((g_carton_tb.count = 0) OR (l_found = 'N')) THEN
302 i := g_carton_tb.count + 1;
303 g_carton_tb(i).delivery_id := l_delivery_id;
304 g_carton_tb(i).carton_count := 1;
305 g_carton_tb(i).carton_index := 1;
306 END IF;
307
308
309 END set_carton_count;
310
311 PROCEDURE get_carton_count(
312 p_delivery_id IN NUMBER
313 , x_carton_total OUT NOCOPY NUMBER
314 , x_carton_index OUT NOCOPY NUMBER) IS
315 i NUMBER;
316 BEGIN
317 FOR i IN 1..g_carton_tb.count LOOP
318 IF g_carton_tb(i).delivery_id = p_delivery_id THEN
319 x_carton_total := g_carton_tb(i).carton_count;
320 x_carton_index := g_carton_tb(i).carton_index;
321 g_carton_tb(i).carton_index := g_carton_tb(i).carton_index + 1;
322 EXIT;
323 END IF;
324 END LOOP;
325 END get_carton_count;
326
327 PROCEDURE clear_carton_count IS
328 BEGIN
329 g_carton_tb.delete;
330 END clear_carton_count;
331
332
333 PROCEDURE get_variable_data(
334 x_variable_content OUT NOCOPY INV_LABEL.label_tbl_type
335 , x_msg_count OUT NOCOPY NUMBER
336 , x_msg_data OUT NOCOPY VARCHAR2
337 , x_return_status OUT NOCOPY VARCHAR2
338 , p_label_type_info IN INV_LABEL.label_type_rec
339 , p_transaction_id IN NUMBER
340 , p_input_param IN MTL_MATERIAL_TRANSACTIONS_TEMP%ROWTYPE
341 , p_transaction_identifier IN NUMBER
342 ) IS
343
344
345 CURSOR c_fields_for_format(v_label_format_id NUMBER) IS
346 SELECT lbfl.column_name
347 FROM wms_label_field_variables lbvar,
348 wms_label_fields_vl lbfl
349 WHERE lbfl.label_field_id = lbvar.label_field_id
350 AND lbvar.label_format_id = v_label_format_id
351 GROUP BY lbfl.column_name;
352
353 -- Get WDD with MMTT
354 CURSOR c_wdd_mmtt(p_transaction_temp_id NUMBER, p_cartonization_id NUMBER) IS
355 SELECT
356 nvl(wdd.requested_quantity, mmtt.transaction_quantity) requested_quantity
357 , mmtt.transaction_quantity shipped_quantity
358 , mmtt.secondary_transaction_quantity shipped_quantity2
359 , mmtt.transaction_uom uom
360 , mmtt.revision revision
361 , mmtt.lot_number lot_number
362 , wdd.cancelled_quantity
363 , wdd.delivered_quantity
364 , wdd.carrier_id
365 , wdd.cust_po_number customer_purchase_order
366 , wdd.customer_id
367 , wdd.ship_method_code
368 , NULL oe_ship_method_code
369 , mmtt.organization_id
370 , mmtt.subinventory_code from_subinventory
371 , mmtt.locator_id from_locator_id
372 , milk.concatenated_segments from_locator
373 , mmtt.transfer_subinventory to_subinventory
374 , mmtt.transfer_to_location to_locator_id
375 , milk2.concatenated_segments to_locator
376 , wdd.source_header_number
377 , wdd.source_line_number
378 , wdd.tracking_number
379 , wdd.fob_code FOB
380 , mmtt.inventory_item_id
381 , wdd.customer_item_id
382 , wdd.project_id
383 , wdd.task_id
384 , wda.delivery_id
385 , wdd.ship_from_location_id
386 , wdd.ship_to_location_id
387 , wdd.ship_to_site_use_id
388 , wdd.ship_to_contact_id
389 , wdd.sold_to_contact_id
390 , wdd.deliver_to_location_id
391 , wdd.deliver_to_contact_id
392 , wdd.deliver_to_site_use_id
393 , oeol.header_id source_header_id
394 , wdd.source_line_id
395 , wdd.attribute_category
396 , wdd.attribute1
397 , wdd.attribute2
398 , wdd.attribute3
399 , wdd.attribute4
400 , wdd.attribute5
401 , wdd.attribute6
402 , wdd.attribute7
403 , wdd.attribute8
404 , wdd.attribute9
405 , wdd.attribute10
406 , wdd.attribute11
407 , wdd.attribute12
408 , wdd.attribute13
409 , wdd.attribute14
410 , wdd.attribute15
411 , wdd.tp_attribute_category
412 , wdd.tp_attribute1
413 , wdd.tp_attribute2
414 , wdd.tp_attribute3
415 , wdd.tp_attribute4
416 , wdd.tp_attribute5
417 , wdd.tp_attribute6
418 , wdd.tp_attribute7
419 , wdd.tp_attribute8
420 , wdd.tp_attribute9
421 , wdd.tp_attribute10
422 , wdd.tp_attribute11
423 , wdd.tp_attribute12
424 , wdd.tp_attribute13
425 , wdd.tp_attribute14
426 , wdd.tp_attribute15
427 , Nvl(mmtt.transfer_lpn_id, cartonization_id) outer_lpn_id
428 , NULL number_of_total
429 , NULL delivery_number -- Place holder, get later with c_delivery
430 , NULL waybill -- Place holder, get later with c_delivery
431 , NULL airbill -- Place holder, get later with c_delivery
432 , NULL bill_of_lading -- Place holder, get later with c_delivery
433 , NULL trip_number -- Place holder, get later with c_delivery
434 , NULL wnd_carrier_id -- Place holder, get later with c_delivery
435 , NULL wnd_ship_method_code -- Place holder, get later with c_delivery
436 , NULL intmed_ship_to_location_id -- Place holder, get later with c_delivery
437 , wdd.intmed_ship_to_contact_id
438
439 FROM
440 (SELECT mmtt1.inventory_item_id,
441 mmtt1.organization_id,
442 mmtt1.subinventory_code,
443 mmtt1.locator_id,
444 mmtt1.transfer_organization,
445 mmtt1.transfer_to_location,
446 mmtt1.transfer_subinventory,
447 mmtt1.move_order_line_id,
448 mmtt1.content_lpn_id,
449 mmtt1.transfer_lpn_id,
450 mmtt1.cartonization_id,
451 mmtt1.transaction_temp_id,
452 mmtt1.revision,
453 mmtt1.transaction_uom,
454 nvl(mtlt1.transaction_quantity, mmtt1.transaction_quantity) transaction_quantity,
455 nvl(mtlt1.secondary_quantity, mmtt1.secondary_transaction_quantity) secondary_transaction_quantity, --Bug# 3596990
456 mtlt1.lot_number
457 FROM
458 mtl_material_transactions_temp mmtt1,
459 mtl_transaction_lots_temp mtlt1
460 WHERE mmtt1.transaction_temp_id = mtlt1.transaction_temp_id(+)
461 ) mmtt, -- mmtt with lot number information
462 wsh_delivery_details wdd,
463 wsh_delivery_assignments_v wda,
464 mtl_item_locations_kfv milk,
465 mtl_item_locations_kfv milk2,
466 oe_order_lines_all oeol
467 WHERE ((mmtt.transaction_temp_id = p_transaction_temp_id AND
468 p_transaction_temp_id IS NOT NULL) OR
469 (mmtt.cartonization_id = p_cartonization_id AND
470 p_cartonization_id IS NOT NULL))
471 AND mmtt.move_order_line_id = wdd.move_order_line_id
472 AND wda.delivery_detail_id = wdd.delivery_detail_id
473 AND wdd.released_status = 'S'
474 AND mmtt.organization_id = milk.organization_id (+)
475 AND mmtt.locator_id = milk.inventory_location_id(+)
476 AND mmtt.transfer_organization = milk2.organization_id (+)
477 AND mmtt.transfer_to_location = milk2.inventory_location_id(+)
478 AND wdd.source_line_id = oeol.line_id(+)
479 ORDER BY mmtt.inventory_item_id, mmtt.lot_number;
480
481
482 -- Cursor to get WDD records with outermost LPN ID
483 CURSOR c_wdd_lpn(p_lpn_id NUMBER) IS
484 SELECT wdd_item.requested_quantity
485 ,wdd_item.shipped_quantity
486 ,wdd_item.shipped_quantity2
487 ,wdd_item.requested_quantity_uom uom
488 ,wdd_item.revision
489 ,wdd_item.lot_number
490 ,wdd_item.cancelled_quantity
491 ,wdd_item.delivered_quantity
492 ,wdd_item.carrier_id
493 ,wdd_item.cust_po_number customer_purchase_order
494 ,wdd_item.customer_id
495 ,wdd_item.ship_method_code
496 ,to_char(NULL) oe_ship_method_code
497 ,wdd_item.organization_id
498 ,to_char(NULL) from_subinventory
499 ,to_number(NULL) from_locator_id
500 ,to_char(NULL) from_locator
501 ,to_char(NULL) to_subinventory -- get it later from LPN
502 ,to_number(NULL) to_locator_id -- get it later from LPN
503 ,to_char(NULL) to_locator -- get it later from LPN
504 ,wdd_item.source_header_number
505 ,wdd_item.source_line_number
506 ,wdd_item.tracking_number
507 ,wdd_item.fob_code FOB
508 ,wdd_item.inventory_item_id
509 ,wdd_item.customer_item_id
510 ,wdd_item.project_id
511 ,wdd_item.task_id
512 ,wda.delivery_id
513 ,wdd_item.ship_from_location_id
514 ,wdd_item.ship_to_location_id
515 ,wdd_item.ship_to_site_use_id
516 ,wdd_item.ship_to_contact_id
517 ,wdd_item.sold_to_contact_id
518 ,wdd_item.deliver_to_location_id
519 ,wdd_item.deliver_to_contact_id
520 ,wdd_item.deliver_to_site_use_id
521 ,oeol.header_id source_header_id
522 ,wdd_item.source_line_id
523 ,wdd_item.attribute_category
524 ,wdd_item.attribute1
525 ,wdd_item.attribute2
526 ,wdd_item.attribute3
527 ,wdd_item.attribute4
528 ,wdd_item.attribute5
529 ,wdd_item.attribute6
530 ,wdd_item.attribute7
531 ,wdd_item.attribute8
532 ,wdd_item.attribute9
533 ,wdd_item.attribute10
534 ,wdd_item.attribute11
535 ,wdd_item.attribute12
536 ,wdd_item.attribute13
537 ,wdd_item.attribute14
538 ,wdd_item.attribute15
539 ,wdd_item.tp_attribute_category
540 ,wdd_item.tp_attribute1
541 ,wdd_item.tp_attribute2
542 ,wdd_item.tp_attribute3
543 ,wdd_item.tp_attribute4
544 ,wdd_item.tp_attribute5
545 ,wdd_item.tp_attribute6
546 ,wdd_item.tp_attribute7
547 ,wdd_item.tp_attribute8
548 ,wdd_item.tp_attribute9
549 ,wdd_item.tp_attribute10
550 ,wdd_item.tp_attribute11
551 ,wdd_item.tp_attribute12
552 ,wdd_item.tp_attribute13
553 ,wdd_item.tp_attribute14
554 ,wdd_item.tp_attribute15
555 ,wlpn.outermost_lpn_id outer_lpn_id
556 ,NULL number_of_total
557 ,NULL delivery_number -- Place holder, get later with c_delivery
558 ,NULL waybill -- Place holder, get later with c_delivery
559 ,NULL airbill -- Place holder, get later with c_delivery
560 ,NULL bill_of_lading -- Place holder, get later with c_delivery
561 ,NULL trip_number -- Place holder, get later with c_delivery
562 ,NULL wnd_carrier_id -- Place holder, get later with c_delivery
563 ,NULL wnd_ship_method_code -- Place holder, get later with c_delivery
564 ,NULL intmed_ship_to_location_id -- Place holder, get later with c_delivery
565 ,wdd_item.intmed_ship_to_contact_id
566
567 FROM wsh_delivery_details wdd_item -- records with item info
568 , wsh_delivery_details wdd_lpn -- records of the immediate lpn
569 , wsh_delivery_assignments_v wda
570 , oe_order_lines_all oeol
571 , wms_license_plate_numbers wlpn
572 WHERE wda.delivery_detail_id = wdd_item.delivery_detail_id
573 AND wda.parent_delivery_detail_id = wdd_lpn.delivery_detail_id
574 AND (wdd_item.inventory_item_id IS NOT NULL AND
575 wdd_item.lpn_id IS NULL)
576 AND wdd_lpn.lpn_id IN
577 (SELECT wlpn2.lpn_id
578 FROM wms_license_plate_numbers wlpn2
579 WHERE wlpn2.outermost_lpn_id = wlpn.outermost_lpn_id)
580 AND wlpn.lpn_id = p_lpn_id
581 AND wdd_item.source_line_id = oeol.line_id(+)
582 ORDER BY wdd_item.inventory_item_id, wdd_item.lot_number;
583
584 -- Get WDD records with delivery ID
585 -- WMS org: join to LPN table
586 CURSOR c_wdd_del_wms(p_delivery_id NUMBER) IS
587 SELECT wdd_item.requested_quantity
588 ,wdd_item.shipped_quantity
589 ,wdd_item.shipped_quantity2
590 ,wdd_item.requested_quantity_uom uom
591 ,wdd_item.revision
592 ,wdd_item.lot_number
593 ,wdd_item.cancelled_quantity --Added bug3952110 -- about LPN contains multiple splitted line from del det. sum qty
594 ,wdd_item.delivered_quantity
595 ,wdd_item.carrier_id
596 ,wdd_item.cust_po_number customer_purchase_order
597 ,wdd_item.customer_id
598 ,wdd_item.ship_method_code
599 ,NULL oe_ship_method_code
600 ,wdd_item.organization_id
601 ,NULL from_subinventory
602 ,NULL from_locator_id
603 ,NULL from_locator
604 ,NULL to_subinventory -- get it later from LPN
605 ,NULL to_locator_id -- get it later from LPN
606 ,NULL to_locator -- get it later from LPN
607 ,wdd_item.source_header_number
608 ,wdd_item.source_line_number
609 ,wdd_item.tracking_number
610 ,wdd_item.fob_code FOB
611 ,wdd_item.inventory_item_id
612 ,wdd_item.customer_item_id
613 ,wdd_item.project_id
614 ,wdd_item.task_id
615 ,wda.delivery_id
616 ,wdd_item.ship_from_location_id
617 ,wdd_item.ship_to_location_id
618 ,wdd_item.ship_to_site_use_id
619 ,wdd_item.ship_to_contact_id
620 ,wdd_item.deliver_to_location_id
621 ,wdd_item.deliver_to_contact_id
622 ,wdd_item.deliver_to_site_use_id
623 ,wdd_item.sold_to_contact_id
624 ,oeol.header_id source_header_id
625 ,wdd_item.source_line_id
626 ,wdd_item.attribute_category
627 ,wdd_item.attribute1
628 ,wdd_item.attribute2
629 ,wdd_item.attribute3
630 ,wdd_item.attribute4
631 ,wdd_item.attribute5
632 ,wdd_item.attribute6
633 ,wdd_item.attribute7
634 ,wdd_item.attribute8
635 ,wdd_item.attribute9
636 ,wdd_item.attribute10
637 ,wdd_item.attribute11
638 ,wdd_item.attribute12
639 ,wdd_item.attribute13
640 ,wdd_item.attribute14
641 ,wdd_item.attribute15
642 ,wdd_item.tp_attribute_category
643 ,wdd_item.tp_attribute1
644 ,wdd_item.tp_attribute2
645 ,wdd_item.tp_attribute3
646 ,wdd_item.tp_attribute4
647 ,wdd_item.tp_attribute5
648 ,wdd_item.tp_attribute6
649 ,wdd_item.tp_attribute7
650 ,wdd_item.tp_attribute8
651 ,wdd_item.tp_attribute9
652 ,wdd_item.tp_attribute10
653 ,wdd_item.tp_attribute11
654 ,wdd_item.tp_attribute12
655 ,wdd_item.tp_attribute13
656 ,wdd_item.tp_attribute14
657 ,wdd_item.tp_attribute15
658 ,wlpn.outermost_lpn_id outer_lpn_id
659 ,NULL number_of_total
660 ,NULL delivery_number -- Place holder, get later with c_delivery
661 ,NULL waybill -- Place holder, get later with c_delivery
662 ,NULL airbill -- Place holder, get later with c_delivery
663 ,NULL bill_of_lading -- Place holder, get later with c_delivery
664 ,NULL trip_number -- Place holder, get later with c_delivery
665 ,NULL wnd_carrier_id -- Place holder, get later with c_delivery
666 ,NULL wnd_ship_method_code -- Place holder, get later with c_delivery
667 ,NULL intmed_ship_to_location_id -- Place holder, get later with c_delivery
668 ,wdd_item.intmed_ship_to_contact_id
669 FROM wsh_delivery_details wdd_item -- records with item info
670 , wsh_delivery_details wdd_lpn -- records of the immediate lpn
671 , wms_license_plate_numbers wlpn
672 , wsh_delivery_assignments_v wda
673 , oe_order_lines_all oeol
674 WHERE wda.delivery_detail_id = wdd_item.delivery_detail_id
675 AND (wdd_item.inventory_item_id IS NOT NULL AND
676 wdd_item.lpn_id IS NULL)
677 AND wda.parent_delivery_detail_id = wdd_lpn.delivery_detail_id
678 AND wdd_lpn.lpn_id = wlpn.lpn_id
679 AND wda.delivery_id = p_delivery_id
680 AND wdd_item.source_line_id = oeol.line_id(+)
681 ORDER BY wlpn.outermost_lpn_id, wdd_item.inventory_item_id, wdd_item.lot_number;
682
683 -- Get WDD records with delivery ID
684 -- Non WMS org: no LPN related
685 CURSOR c_wdd_del_inv(p_delivery_id NUMBER) IS
686 SELECT wdd_item.requested_quantity
687 ,wdd_item.shipped_quantity
688 ,wdd_item.shipped_quantity2
689 ,wdd_item.requested_quantity_uom uom
690 ,wdd_item.revision
691 ,wdd_item.lot_number
692 ,wdd_item.cancelled_quantity --Added bug3952110 -- about LPN contains multiple splitted line from del det. sum qty
693 ,wdd_item.delivered_quantity
694 ,wdd_item.carrier_id
695 ,wdd_item.cust_po_number customer_purchase_order
696 ,wdd_item.customer_id
697 ,wdd_item.ship_method_code
698 ,NULL oe_ship_method_code
699 ,wdd_item.organization_id
700 ,NULL from_subinventory
701 ,NULL from_locator_id
702 ,NULL from_locator
703 ,wdd_item.subinventory to_subinventory
704 ,wdd_item.locator_id to_locator_id
705 ,milk.concatenated_segments to_locator
706 ,wdd_item.source_header_number
707 ,wdd_item.source_line_number
708 ,wdd_item.tracking_number
709 ,wdd_item.fob_code FOB
710 ,wdd_item.inventory_item_id
711 ,wdd_item.customer_item_id
712 ,wdd_item.project_id
713 ,wdd_item.task_id
714 ,wda.delivery_id
715 ,wdd_item.ship_from_location_id
716 ,wdd_item.ship_to_location_id
717 ,wdd_item.ship_to_site_use_id
718 ,wdd_item.ship_to_contact_id
719 ,wdd_item.sold_to_contact_id
720 ,wdd_item.deliver_to_location_id
721 ,wdd_item.deliver_to_contact_id
722 ,wdd_item.deliver_to_site_use_id
723 ,oeol.header_id source_header_id
724 ,wdd_item.source_line_id
725 ,wdd_item.attribute_category
726 ,wdd_item.attribute1
727 ,wdd_item.attribute2
728 ,wdd_item.attribute3
729 ,wdd_item.attribute4
730 ,wdd_item.attribute5
731 ,wdd_item.attribute6
732 ,wdd_item.attribute7
733 ,wdd_item.attribute8
734 ,wdd_item.attribute9
735 ,wdd_item.attribute10
736 ,wdd_item.attribute11
737 ,wdd_item.attribute12
738 ,wdd_item.attribute13
739 ,wdd_item.attribute14
740 ,wdd_item.attribute15
741 ,wdd_item.tp_attribute_category
742 ,wdd_item.tp_attribute1
743 ,wdd_item.tp_attribute2
744 ,wdd_item.tp_attribute3
745 ,wdd_item.tp_attribute4
746 ,wdd_item.tp_attribute5
747 ,wdd_item.tp_attribute6
748 ,wdd_item.tp_attribute7
749 ,wdd_item.tp_attribute8
750 ,wdd_item.tp_attribute9
751 ,wdd_item.tp_attribute10
752 ,wdd_item.tp_attribute11
753 ,wdd_item.tp_attribute12
754 ,wdd_item.tp_attribute13
755 ,wdd_item.tp_attribute14
756 ,wdd_item.tp_attribute15
757 ,NULL outer_lpn_id
758 ,NULL number_of_total
759 ,NULL delivery_number -- Place holder, get later with c_delivery
760 ,NULL waybill -- Place holder, get later with c_delivery
761 ,NULL airbill -- Place holder, get later with c_delivery
762 ,NULL bill_of_lading -- Place holder, get later with c_delivery
763 ,NULL trip_number -- Place holder, get later with c_delivery
764 ,NULL wnd_carrier_id -- Place holder, get later with c_delivery
765 ,NULL wnd_ship_method_code -- Place holder, get later with c_delivery
766 ,NULL intmed_ship_to_location_id -- Place holder, get later with c_delivery
767 ,wdd_item.intmed_ship_to_contact_id
768 FROM wsh_delivery_details wdd_item -- records with item info
769 , wsh_delivery_assignments_v wda
770 , mtl_item_locations_kfv milk
771 , oe_order_lines_all oeol
772 WHERE wda.delivery_detail_id = wdd_item.delivery_detail_id
773 AND (wdd_item.inventory_item_id IS NOT NULL AND
774 wdd_item.lpn_id IS NULL)
775 AND wda.delivery_id = p_delivery_id
776 AND wdd_item.organization_id = milk.organization_id (+)
777 AND wdd_item.locator_id = milk.inventory_location_id(+)
778 AND wdd_item.source_line_id = oeol.line_id(+)
779 ORDER BY wdd_item.inventory_item_id, wdd_item.lot_number;
780
781 CURSOR c_delivery(p_delivery_id NUMBER) IS
782 SELECT wnd.name delivery_number
783 , wnd.waybill waybill
784 , wnd.waybill airbill
785 , wdi.sequence_number bill_of_lading
786 , wt.name trip_number
787 -- Bug 5121507, Get carrier in the order of Trip->Delivery->Delivery Detail
788 --, nvl(wnd.carrier_id, wt.carrier_id) wnd_carrier_id
789 , nvl(wt.carrier_id, wnd.carrier_id) wnd_carrier_id
790 , wnd.ship_method_code wnd_ship_method_code
791 , wnd.intmed_ship_to_location_id
792 FROM wsh_new_deliveries wnd
793 , wsh_delivery_legs wdl
794 , wsh_document_instances wdi
795 , wsh_trip_stops wts
796 , wsh_trips wt
797 WHERE wnd.delivery_id = wdl.delivery_id(+)
798 AND wdi.entity_name (+) = 'WSH_DELIVERY_LEGS'
799 AND wdl.delivery_leg_id = wdi.entity_id (+)
800 AND wdl.pick_up_stop_id = wts.stop_id (+)
801 AND wts.trip_id = wt.trip_id (+)
802 AND wnd.delivery_id = p_delivery_id;
803
804 CURSOR c_org_code(p_organization_id NUMBER) IS
805 SELECT organization_code
806 FROM mtl_parameters
807 WHERE organization_id = p_organization_id;
808
809 CURSOR c_org_name(p_organization_id NUMBER) IS
810 SELECT hou.name organization_name
811 , loc.telephone_number_1 org_tel_num
812 , loc.telephone_number_2 org_fax_num
813 FROM hr_organization_units hou
814 , hr_locations_all_v loc
815 WHERE hou.organization_id = p_organization_id
816 AND hou.location_id = loc.location_id (+);
817
818
819 CURSOR c_wdd_outer_lpn(p_lpn_id NUMBER) IS
820 SELECT wdd.load_seq_number
821 , wdd.net_weight
822 , wdd.gross_weight
823 , wdd.tracking_number
824 , wdd.gross_weight
825 , wdd.weight_uom_code
826 , (wdd.gross_weight - wdd.net_weight) tare_weight
827 , wdd.weight_uom_code tare_weight_uom
828 , wdd.volume
829 , wdd.volume_uom_code
830 FROM wsh_delivery_details wdd
831 WHERE lpn_id = p_lpn_id;
832
833 CURSOR c_item(p_organization_id NUMBER, p_inventory_item_id NUMBER) IS
834 SELECT msik.concatenated_segments
835 ,msik.description
836 ,msik.secondary_uom_code
837 ,msik.attribute_category
838 ,msik.attribute1
839 ,msik.attribute2
840 ,msik.attribute3
841 ,msik.attribute4
842 ,msik.attribute5
843 ,msik.attribute6
844 ,msik.attribute7
845 ,msik.attribute8
846 ,msik.attribute9
847 ,msik.attribute10
848 ,msik.attribute11
849 ,msik.attribute12
850 ,msik.attribute13
851 ,msik.attribute14
852 ,msik.attribute15
853 ,poh.hazard_class
854 FROM mtl_system_items_kfv msik
855 ,po_hazard_classes poh
856 WHERE msik.organization_id = p_organization_id
857 AND msik.inventory_item_id = p_inventory_item_id
858 AND msik.hazard_class_id = poh.hazard_class_id(+);
859
860 CURSOR c_customer_item(p_customer_item_id NUMBER) IS
861 SELECT
862 mci.customer_item_number
863 , mci.attribute_category
864 , mci.attribute1
865 , mci.attribute2
866 , mci.attribute3
867 , mci.attribute4
868 , mci.attribute5
869 , mci.attribute6
870 , mci.attribute7
871 , mci.attribute8
872 , mci.attribute9
873 , mci.attribute10
874 , mci.attribute11
875 , mci.attribute12
876 , mci.attribute13
877 , mci.attribute14
878 , mci.attribute15
879 FROM mtl_customer_items mci
880 WHERE mci.customer_item_id = p_customer_item_id;
881
882 CURSOR c_lot_number(p_organization_id NUMBER, p_inventory_item_id NUMBER, p_lot_number VARCHAR2) IS
883 SELECT
884 mmst.status_code lot_number_status
885 , to_char(mln.expiration_date, G_DATE_FORMAT_MASK) lot_expiration_date
886 , mln.lot_attribute_category lot_attribute_category
887 , mln.c_attribute1 lot_c_attribute1
888 , mln.c_attribute2 lot_c_attribute2
889 , mln.c_attribute3 lot_c_attribute3
890 , mln.c_attribute4 lot_c_attribute4
891 , mln.c_attribute5 lot_c_attribute5
892 , mln.c_attribute6 lot_c_attribute6
893 , mln.c_attribute7 lot_c_attribute7
894 , mln.c_attribute8 lot_c_attribute8
895 , mln.c_attribute9 lot_c_attribute9
896 , mln.c_attribute10 lot_c_attribute10
897 , mln.c_attribute11 lot_c_attribute11
898 , mln.c_attribute12 lot_c_attribute12
899 , mln.c_attribute13 lot_c_attribute13
900 , mln.c_attribute14 lot_c_attribute14
901 , mln.c_attribute15 lot_c_attribute15
902 , mln.c_attribute16 lot_c_attribute16
903 , mln.c_attribute17 lot_c_attribute17
904 , mln.c_attribute18 lot_c_attribute18
905 , mln.c_attribute19 lot_c_attribute19
906 , mln.c_attribute20 lot_c_attribute20
907 , to_char(mln.D_ATTRIBUTE1, G_DATE_FORMAT_MASK) lot_d_attribute1
908 , to_char(mln.D_ATTRIBUTE2, G_DATE_FORMAT_MASK) lot_d_attribute2
909 , to_char(mln.D_ATTRIBUTE3, G_DATE_FORMAT_MASK) lot_d_attribute3
910 , to_char(mln.D_ATTRIBUTE4, G_DATE_FORMAT_MASK) lot_d_attribute4
911 , to_char(mln.D_ATTRIBUTE5, G_DATE_FORMAT_MASK) lot_d_attribute5
912 , to_char(mln.D_ATTRIBUTE6, G_DATE_FORMAT_MASK) lot_d_attribute6
913 , to_char(mln.D_ATTRIBUTE7, G_DATE_FORMAT_MASK) lot_d_attribute7
914 , to_char(mln.D_ATTRIBUTE8, G_DATE_FORMAT_MASK) lot_d_attribute8
915 , to_char(mln.D_ATTRIBUTE9, G_DATE_FORMAT_MASK) lot_d_attribute9
916 , to_char(mln.D_ATTRIBUTE10, G_DATE_FORMAT_MASK) lot_d_attribute10
917 , mln.n_attribute1 lot_n_attribute1
918 , mln.n_attribute2 lot_n_attribute2
919 , mln.n_attribute3 lot_n_attribute3
920 , mln.n_attribute4 lot_n_attribute4
921 , mln.n_attribute5 lot_n_attribute5
922 , mln.n_attribute6 lot_n_attribute6
923 , mln.n_attribute7 lot_n_attribute7
924 , mln.n_attribute8 lot_n_attribute8
925 , mln.n_attribute9 lot_n_attribute9
926 , mln.n_attribute10 lot_n_attribute10
927 , mln.territory_code lot_country_of_origin
928 , mln.grade_code lot_grade_code
929 , to_char(mln.ORIGINATION_DATE, G_DATE_FORMAT_MASK) lot_origination_date
930 , mln.DATE_CODE lot_date_code
931 , to_char(mln.CHANGE_DATE, G_DATE_FORMAT_MASK) lot_change_date
932 , mln.AGE lot_age
933 , to_char(mln.RETEST_DATE, G_DATE_FORMAT_MASK) lot_retest_date
934 , to_char(mln.MATURITY_DATE, G_DATE_FORMAT_MASK) lot_maturity_date
935 , mln.ITEM_SIZE lot_item_size
936 , mln.COLOR lot_color
937 , mln.VOLUME lot_volume
938 , mln.VOLUME_UOM lot_volume_uom
939 , mln.PLACE_OF_ORIGIN lot_place_of_origin
940 , to_char(mln.BEST_BY_DATE, G_DATE_FORMAT_MASK) lot_best_by_date
941 , mln.length lot_length
942 , mln.length_uom lot_length_uom
943 , mln.recycled_content lot_recycled_cont
944 , mln.thickness lot_thickness
945 , mln.thickness_uom lot_thickness_uom
946 , mln.width lot_width
947 , mln.width_uom lot_width_uom
948 , mln.curl_wrinkle_fold lot_curl
949 , mln.vendor_name lot_vendor
950 , mln.parent_lot_number parent_lot_number
951 , mln.expiration_action_date expiration_action_date
952 , ml.meaning origination_type
953 , mln.hold_date hold_date
954 , mln.expiration_action_code expiration_action_code
955 , mln.supplier_lot_number supplier_lot_number
956 FROM mtl_lot_numbers mln,
957 mtl_material_statuses_b mmsb,
958 mtl_material_statuses_tl mmst,
959 mfg_lookups ml
960 WHERE mln.organization_id = p_organization_id
961 AND mln.inventory_item_id = p_inventory_item_id
962 AND mln.lot_number = p_lot_number
963 AND mln.status_id = mmsb.status_id(+)
964 AND mmsb.status_id = mmst.status_id(+)
965 AND mmst.language(+) = USERENV('LANG')
966 AND ml.lookup_type(+) = 'MTL_LOT_ORIGINATION_TYPE'
967 AND ml.lookup_code(+) = mln.origination_type;
968
969 CURSOR c_customer(p_customer_id NUMBER) IS
970 SELECT substrb(party.party_name,1,50) customer_name,
971 cust_acct.account_number customer_number
972 FROM hz_parties party
973 , hz_cust_accounts cust_acct
974 WHERE cust_acct.party_id = party.party_id
975 AND cust_acct.cust_account_id = p_customer_id;
976
977 CURSOR c_project(p_project_id NUMBER) IS
978 SELECT name
979 FROM pa_projects_all
980 WHERE project_id = p_project_id;
981
982 CURSOR c_task(p_task_id NUMBER) IS
983 SELECT task_name
984 FROM pa_tasks
985 WHERE task_id = p_task_id;
986
987 CURSOR c_lpn(p_lpn_id NUMBER) IS
988 SELECT wlpn.license_plate_number
989 , wlpn.content_volume
990 , wlpn.content_volume_uom_code
991 , wlpn.gross_weight
992 , wlpn.gross_weight_uom_code
993 , wlpn.tare_weight
994 , wlpn.tare_weight_uom_code
995 , wlpn.subinventory_code
996 , wlpn.locator_id
997 , milk.concatenated_segments locator
998 , wlpn.attribute_category
999 , wlpn.attribute1
1000 , wlpn.attribute2
1001 , wlpn.attribute3
1002 , wlpn.attribute4
1003 , wlpn.attribute5
1004 , wlpn.attribute6
1005 , wlpn.attribute7
1006 , wlpn.attribute8
1007 , wlpn.attribute9
1008 , wlpn.attribute10
1009 , wlpn.attribute11
1010 , wlpn.attribute12
1011 , wlpn.attribute13
1012 , wlpn.attribute14
1013 , wlpn.attribute15
1014 , msik.concatenated_segments lpn_container_item
1015 FROM wms_license_plate_numbers wlpn
1016 , mtl_system_items_kfv msik
1017 , mtl_item_locations_kfv milk
1018 WHERE wlpn.lpn_id = p_lpn_id
1019 AND msik.organization_id(+) = wlpn.organization_id
1020 AND msik.inventory_item_id(+) = wlpn.inventory_item_id
1021 AND milk.organization_id(+) = wlpn.organization_id
1022 AND milk.inventory_location_id(+) = wlpn.locator_id;
1023
1024 CURSOR c_carrier(p_carrier_id NUMBER) IS
1025 SELECT carrier_name
1026 FROM wsh_carriers_v
1027 WHERE carrier_id = p_carrier_id;
1028
1029 CURSOR c_ship_method(p_ship_method_code VARCHAR2) IS
1030 SELECT meaning
1031 FROM fnd_common_lookups
1032 WHERE lookup_type='SHIP_METHOD'
1033 AND lookup_code = p_ship_method_code
1034 AND ROWNUM<2;
1035
1036 CURSOR c_address(p_location_id NUMBER) IS
1037 SELECT hr.address_line_1
1038 , hr.address_line_2
1039 , hr.address_line_3
1040 , hr.address_line_4
1041 , hr.city
1042 , hr.postal_code
1043 , hr.state
1044 , hr.county
1045 , hr.country
1046 , hr.province
1047 , hr.location_code
1048 , hr.location_description
1049 FROM (SELECT loc.location_id location_id,loc.address_line_1 address_line_1
1050 ,loc.address_line_2 address_line_2,loc.address_line_3 address_line_3
1051 ,loc.loc_information13 address_line_4,loc.town_or_city city
1052 ,loc.postal_code postal_code,loc.region_2 state,loc.region_1 county
1053 ,loc.country country,loc.region_3 province, loc.location_code location_code
1054 ,loc.description location_description
1055 FROM hr_locations_all loc
1056 UNION ALL
1057 SELECT hz.location_id location_id,hz.address1 address_line_1
1058 ,hz.address2 address_line_2,hz.address3 address_line_3
1059 ,hz.address4 address_line_4,hz.city city,hz.postal_code postal_code
1060 ,hz.state state,hz.county county,hz.country country,hz.province province
1061 ,hz.description location_code, hz.description location_description
1062 FROM hz_locations hz) hr
1063 WHERE hr.location_id = p_location_id;
1064
1065 CURSOR c_location(p_site_use_id NUMBER) IS
1066 SELECT location
1067 FROM hz_cust_site_uses_all
1068 WHERE site_use_id = p_site_use_id;
1069
1070 CURSOR c_phone_fax(p_location_id NUMBER, p_type VARCHAR2) IS
1071 SELECT hcp.phone_country_code||decode(hcp.phone_country_code, NULL, '',' ')||
1072 decode(hcp.phone_area_code,NULL,'','(')||hcp.phone_area_code||decode(hcp.phone_area_code,NULL,'',')')||
1073 hcp.phone_number customer_site_tel_number
1074 FROM hz_party_sites hps, hz_locations hl, hz_contact_points hcp
1075 WHERE hps.location_id = hl.location_id
1076 AND hcp.owner_table_name = 'HZ_PARTY_SITES'
1077 AND hcp.owner_table_id = hps.party_site_id
1078 AND (((hcp.phone_line_type IN ('PHONE','GEN')) AND (p_type = 'PHONE')) OR
1079 ((hcp.phone_line_type IN ('FAX')) AND (p_type = 'FAX')))
1080 AND hps.location_id = p_location_id;
1081
1082 CURSOR c_contact(p_contact_id NUMBER) IS
1083 SELECT ra_cont.last_name || decode(ra_cont.last_name, NULL, NULL, ', ')
1084 || ra_cont.first_name contact_name
1085 FROM ( SELECT ACCT_ROLE.cust_account_role_id contact_id,
1086 SUBSTRB(PARTY.person_last_name,1,50) last_name,
1087 SUBSTRB(PARTY.person_first_name,1,40) first_name
1088 FROM hz_cust_account_roles ACCT_ROLE,
1089 hz_parties PARTY,
1090 hz_relationships REL,
1091 hz_cust_accounts ROLE_ACCT
1092 WHERE
1093 ACCT_ROLE.party_id = REL.party_id
1094 AND ACCT_ROLE.role_type = 'CONTACT'
1095 AND REL.subject_id = PARTY.party_id
1096 AND REL.subject_table_name = 'HZ_PARTIES'
1097 AND REL.object_table_name = 'HZ_PARTIES'
1098 AND ACCT_ROLE.cust_account_id = ROLE_ACCT.cust_account_id
1099 AND ROLE_ACCT.party_id = REL.object_id
1100 ) ra_cont
1101 WHERE ra_cont.contact_id = p_contact_id;
1102
1103 CURSOR c_so_line(p_line_id NUMBER) IS
1104 SELECT
1105 to_char(oeol.SCHEDULE_SHIP_DATE, G_DATE_FORMAT_MASK)
1106 , to_char(oeol.REQUEST_DATE, G_DATE_FORMAT_MASK)
1107 , to_char(oeol.PROMISE_DATE, G_DATE_FORMAT_MASK)
1108 , oeol.SHIPMENT_PRIORITY_CODE
1109 , oeol.shipping_method_code
1110 , oeol.FREIGHT_CARRIER_CODE
1111 , to_char(oeol.SCHEDULE_ARRIVAL_DATE, G_DATE_FORMAT_MASK)
1112 , to_char(oeol.ACTUAL_SHIPMENT_DATE, G_DATE_FORMAT_MASK)
1113 , oeol.SHIPPING_INSTRUCTIONS
1114 , oeol.PACKING_INSTRUCTIONS
1115 , oeol.attribute1
1116 , oeol.attribute2
1117 , oeol.attribute3
1118 , oeol.attribute4
1119 , oeol.attribute5
1120 , oeol.attribute6
1121 , oeol.attribute7
1122 , oeol.attribute8
1123 , oeol.attribute9
1124 , oeol.attribute10
1125 , oeol.attribute11
1126 , oeol.attribute12
1127 , oeol.attribute13
1128 , oeol.attribute14
1129 , oeol.attribute15
1130 , oeol.global_attribute1
1131 , oeol.global_attribute2
1132 , oeol.global_attribute3
1133 , oeol.global_attribute4
1134 , oeol.global_attribute5
1135 , oeol.global_attribute6
1136 , oeol.global_attribute7
1137 , oeol.global_attribute8
1138 , oeol.global_attribute9
1139 , oeol.global_attribute10
1140 , oeol.global_attribute11
1141 , oeol.global_attribute12
1142 , oeol.global_attribute13
1143 , oeol.global_attribute14
1144 , oeol.global_attribute15
1145 , oeol.global_attribute16
1146 , oeol.global_attribute17
1147 , oeol.global_attribute18
1148 , oeol.global_attribute19
1149 , oeol.global_attribute20
1150 , oeol.pricing_attribute1
1151 , oeol.pricing_attribute2
1152 , oeol.pricing_attribute3
1153 , oeol.pricing_attribute4
1154 , oeol.pricing_attribute5
1155 , oeol.pricing_attribute6
1156 , oeol.pricing_attribute7
1157 , oeol.pricing_attribute8
1158 , oeol.pricing_attribute9
1159 , oeol.pricing_attribute10
1160 , oeol.industry_attribute1
1161 , oeol.industry_attribute2
1162 , oeol.industry_attribute3
1163 , oeol.industry_attribute4
1164 , oeol.industry_attribute5
1165 , oeol.industry_attribute6
1166 , oeol.industry_attribute7
1167 , oeol.industry_attribute8
1168 , oeol.industry_attribute9
1169 , oeol.industry_attribute10
1170 , oeol.industry_attribute11
1171 , oeol.industry_attribute13
1172 , oeol.industry_attribute12
1173 , oeol.industry_attribute14
1174 , oeol.industry_attribute15
1175 , oeol.industry_attribute16
1176 , oeol.industry_attribute17
1177 , oeol.industry_attribute18
1178 , oeol.industry_attribute19
1179 , oeol.industry_attribute20
1180 , oeol.industry_attribute21
1181 , oeol.industry_attribute22
1182 , oeol.industry_attribute23
1183 , oeol.industry_attribute24
1184 , oeol.industry_attribute25
1185 , oeol.industry_attribute26
1186 , oeol.industry_attribute27
1187 , oeol.industry_attribute28
1188 , oeol.industry_attribute29
1189 , oeol.industry_attribute30
1190 , oeol.return_attribute1
1191 , oeol.return_attribute2
1192 , oeol.return_attribute3
1193 , oeol.return_attribute4
1194 , oeol.return_attribute5
1195 , oeol.return_attribute6
1196 , oeol.return_attribute7
1197 , oeol.return_attribute8
1198 , oeol.return_attribute9
1199 , oeol.return_attribute10
1200 , oeol.return_attribute11
1201 , oeol.return_attribute12
1202 , oeol.return_attribute13
1203 , oeol.return_attribute14
1204 , oeol.return_attribute15
1205 , oeol.tp_attribute1
1206 , oeol.tp_attribute2
1207 , oeol.tp_attribute3
1208 , oeol.tp_attribute4
1209 , oeol.tp_attribute5
1210 , oeol.tp_attribute6
1211 , oeol.tp_attribute7
1212 , oeol.tp_attribute8
1213 , oeol.tp_attribute9
1214 , oeol.tp_attribute10
1215 , oeol.tp_attribute11
1216 , oeol.tp_attribute12
1217 , oeol.tp_attribute13
1218 , oeol.tp_attribute14
1219 , oeol.tp_attribute15
1220 , Nvl(oeol.ordered_item,
1221 Decode(oeol.item_identifier_type,
1222 'CUST', mci_oi.customer_item_number,
1223 'INT', msik_oi.concatenated_segments,
1224 msik_oi.concatenated_segments)) ordered_item
1225 FROM oe_order_lines_all oeol
1226 , mtl_customer_items mci_oi
1227 , mtl_system_items_kfv msik_oi
1228 WHERE oeol.line_id = p_line_id
1229 AND oeol.ordered_item_id = mci_oi.customer_item_id (+)
1230 AND oeol.ordered_item_id = msik_oi.inventory_item_id (+)
1231 AND oeol.org_id = msik_oi.organization_id (+);
1232
1233 CURSOR c_so_header(p_header_id NUMBER) IS
1234 SELECT
1235 oeoh.attribute1
1236 , oeoh.attribute2
1237 , oeoh.attribute3
1238 , oeoh.attribute4
1239 , oeoh.attribute5
1240 , oeoh.attribute6
1241 , oeoh.attribute7
1242 , oeoh.attribute8
1243 , oeoh.attribute9
1244 , oeoh.attribute10
1245 , oeoh.attribute11
1246 , oeoh.attribute12
1247 , oeoh.attribute13
1248 , oeoh.attribute14
1249 , oeoh.attribute15
1250 , oeoh.global_attribute1
1251 , oeoh.global_attribute2
1252 , oeoh.global_attribute3
1253 , oeoh.global_attribute4
1254 , oeoh.global_attribute5
1255 , oeoh.global_attribute6
1256 , oeoh.global_attribute7
1257 , oeoh.global_attribute8
1258 , oeoh.global_attribute9
1259 , oeoh.global_attribute10
1260 , oeoh.global_attribute11
1261 , oeoh.global_attribute12
1262 , oeoh.global_attribute13
1263 , oeoh.global_attribute14
1264 , oeoh.global_attribute15
1265 , oeoh.global_attribute16
1266 , oeoh.global_attribute17
1267 , oeoh.global_attribute18
1268 , oeoh.global_attribute19
1269 , oeoh.global_attribute20
1270 , oeoh.tp_attribute1
1271 , oeoh.tp_attribute2
1272 , oeoh.tp_attribute3
1273 , oeoh.tp_attribute4
1274 , oeoh.tp_attribute5
1275 , oeoh.tp_attribute6
1276 , oeoh.tp_attribute7
1277 , oeoh.tp_attribute8
1278 , oeoh.tp_attribute9
1279 , oeoh.tp_attribute10
1280 , oeoh.tp_attribute11
1281 , oeoh.tp_attribute12
1282 , oeoh.tp_attribute13
1283 , oeoh.tp_attribute14
1284 , oeoh.tp_attribute15
1285 , oeoh.sales_channel_code
1286 , oeoh.shipping_instructions
1287 , oeoh.packing_instructions
1288 FROM oe_order_headers_all oeoh
1289 WHERE oeoh.header_id = p_header_id;
1290
1291
1292 l_selected_fields INV_LABEL.label_field_variable_tbl_type;
1293 l_selected_fields_count NUMBER;
1294 no_of_rows_per_label NUMBER;
1295 max_no_of_rows_defined NUMBER;
1296 new_label BOOLEAN;
1297
1298 l_delivery_id NUMBER;
1299 l_outer_lpn_id NUMBER;
1300 l_organization_id NUMBER;
1301 l_wms_enabled NUMBER;
1302 l_return_status VARCHAR2(1);
1303 l_msg_data VARCHAR2(2000);
1304 l_msg_count NUMBER;
1305 l_api_status VARCHAR2(240);
1306 l_error_message VARCHAR2(240);
1307
1308 l_count_custom_sql NUMBER;
1309 l_sql_stmt VARCHAR2(4000);
1310 l_sql_stmt_result VARCHAR2(4000);
1311 TYPE sql_stmt IS REF CURSOR;
1312 c_sql_stmt sql_stmt;
1313 l_custom_sql_ret_status VARCHAR2(1);
1314 l_custom_sql_ret_msg VARCHAR2(2000);
1315
1316 l_CustSqlWarnFlagSet BOOLEAN;
1317 l_CustSqlErrFlagSet BOOLEAN;
1318 l_CustSqlWarnMsg VARCHAR2(2000);
1319 l_CustSqlErrMsg VARCHAR2(2000);
1320
1321 l_cur_wdd c_wdd_lpn%ROWTYPE;
1322 l_prev_wdd c_wdd_lpn%ROWTYPE;
1323 l_cons_wdd c_wdd_lpn%ROWTYPE;
1324 TYPE wdd_tbl_type IS TABLE OF c_wdd_lpn%ROWTYPE INDEX BY BINARY_INTEGER;
1325 l_wdd_tb wdd_tbl_type;
1326 consol_index NUMBER;
1327 l_conv_qty NUMBER;
1328 l_wdd_index NUMBER;
1329 i NUMBER;
1330 j NUMBER;
1331 l_txn_temp_id NUMBER;
1332 l_cart_id NUMBER;
1333 l_wnd_carrier_id NUMBER;
1334 l_wnd_ship_method_code VARCHAR2(30);
1335
1336 l_content_rec_index NUMBER;
1337 row_index_per_label NUMBER;
1338 l_shipping_content_data LONG;
1339 l_label_index NUMBER;
1340 l_column_name_in_format VARCHAR2(60);
1341 l_variable_name VARCHAR2(100);
1342 l_variable_list LONG; -- Modified for bug # 5465141 - VARCHAR2(2000);
1343 l_label_request_id NUMBER;
1344 l_use_rules_engine VARCHAR2(1);
1345 l_label_format_id NUMBER;
1346 l_label_format VARCHAR2(300);
1347 l_prev_format_id NUMBER;
1348 l_printer VARCHAR2(30);
1349 l_prev_lpn_id NUMBER;
1350 l_gtin_enabled BOOLEAN := FALSE;
1351 l_gtin VARCHAR2(100);
1352 l_gtin_desc VARCHAR2(240);
1353
1354 l_progress VARCHAR2(100);
1355
1356 l_total_number_of_lpns NUMBER;
1357 l_number_of_total NUMBER;
1358 l_child_lpn VARCHAR2(250);
1359
1360 --Start Bug 6696594
1361 l_qty_index NUMBER;
1362 l_to_lpn_id NUMBER;
1363 l_to_lpn VARCHAR2(30);
1364 l_process_id NUMBER;
1365 --End Bug 6696594
1366
1367 --Bug 6880623
1368 l_pick_uom VARCHAR2(30);
1369 l_pick_qty NUMBER;
1370 l_uom_string VARCHAR2(30);
1371 l_loop_counter NUMBER;
1372 --Bug 6880623
1373
1374 BEGIN
1375 -- Initialize return status as success
1376 x_return_status := FND_API.G_RET_STS_SUCCESS;
1377 -- Initialize debug
1378 l_debug := INV_LABEL.l_debug;
1379
1380
1381 IF (l_debug = 1) THEN
1382 trace('**In Shipping Content label**');
1383 trace(' Business_flow: '||p_label_type_info.business_flow_code);
1384 trace(' Transaction ID:'||p_transaction_id);
1385 trace(' P_input_param.txn_tmp_id:' || p_input_param.transaction_temp_id);
1386 trace(' P_input_param.lpn_id:' || p_input_param.lpn_id);
1387 trace(' Manual Format ID: ' || p_label_type_info.manual_format_id);
1388 trace(' Manual Format: ' || p_label_type_info.manual_format_name);
1389 END IF;
1390
1391 IF p_label_type_info.business_flow_code in (6, 18,34,21,22) AND
1392 p_transaction_id IS NULL THEN
1393 IF(l_debug = 1) THEN
1394 trace('p_transaction_id is required for business flow 18,34,21,22(Pick Load, Replenish Load, Ship Confirm, Cartonization');
1395 trace('Can not proceed');
1396 END IF;
1397 ELSE
1398 IF p_label_type_info.business_flow_code in (19, 36) AND
1399 p_input_param.lpn_id IS NULL THEN
1400 IF(l_debug = 1) THEN
1401 trace('lpn_id is required for business flow 19,36(Pick Drop, Packing Workbench');
1402 trace('Can not proceed');
1403 END IF;
1404 ELSIF p_label_type_info.business_flow_code IS NULL THEN
1405 -- Manual print
1406 -- Either delivery or the lpn has to be provided
1407 IF p_input_param.transaction_temp_id IS NULL and p_input_param.lpn_id IS NULL THEN
1408 IF(l_debug =1 ) THEN
1409 trace('Delivery ID or LPN_ID has to be provided');
1410 trace('Can not proceed');
1411 END IF;
1412 END IF;
1413 END IF;
1414 END IF;
1415
1416
1417 -- Open Driving Cursors Depends on Business Flow
1418 IF p_label_type_info.business_flow_code in (18, 34) THEN
1419 IF p_label_type_info.business_flow_code in (18, 34) THEN
1420 -- Pick load, replenish pick load
1421 l_txn_temp_id := p_transaction_id;
1422 l_cart_id := NULL;
1423 END IF;
1424 -- Use cursor c_wdd_mmtt
1425 -- Fetch records into l_wdd_tb;
1426 IF(l_debug = 1) THEN
1427 trace('Fetch from c_wdd_mmtt with txn_temp_id='||l_txn_temp_id||', cart_id='||l_cart_id);
1428 END IF;
1429 i := 0;
1430 l_wdd_tb.delete;
1431 FOR l_cur_wdd IN c_wdd_mmtt(l_txn_temp_id, l_cart_id) LOOP
1432 i := i+1;
1433 -- Get delivery information
1434 IF (l_cur_wdd.delivery_id IS NOT NULL) AND
1435 ((i=1) OR (l_cur_wdd.delivery_id <> l_wdd_tb(i-1).delivery_id)) THEN
1436 OPEN c_delivery(l_cur_wdd.delivery_id);
1437 FETCH c_delivery INTO
1438 l_cur_wdd.delivery_number
1439 ,l_cur_wdd.waybill
1440 ,l_cur_wdd.airbill
1441 ,l_cur_wdd.bill_of_lading
1442 ,l_cur_wdd.trip_number
1443 ,l_cur_wdd.wnd_carrier_id
1444 ,l_cur_wdd.wnd_ship_method_code
1445 ,l_cur_wdd.intmed_ship_to_location_id;
1446 CLOSE c_delivery;
1447 END IF;
1448 l_wdd_tb(i) := l_cur_wdd;
1449 END LOOP;
1450 IF(l_debug = 1) THEN
1451 trace('Done fetch from c_wdd_mmtt, l_wdd_tb has '||l_wdd_tb.count||' records');
1452 END IF;
1453
1454 ELSE
1455 -- Cartonization, Pick Drop, Cross Dock, Packing Workbench, Ship Confirm, Manual Print
1456 -- Fetch from driving cursor and consolidate into l_wdd_tb
1457
1458 -- First get txn_temp_id and/or delivery_id and/or lpn_id for different business flow
1459 IF p_label_type_info.business_flow_code in (22) THEN
1460 -- Cartonization
1461 l_txn_temp_id := NULL;
1462 l_cart_id := p_transaction_id;
1463 ELSIF p_label_type_info.business_flow_code in (21) THEN
1464 -- Ship Confirm
1465 l_delivery_id := p_transaction_id;
1466 --Start Bug 6696594
1467 ELSIF p_label_type_info.business_flow_code in (42) AND INV_CONTROL.G_CURRENT_RELEASE_LEVEL >= 120001 THEN
1468 -- Pick Release
1469 l_txn_temp_id := p_transaction_id;
1470 l_cart_id := NULL;
1471 --End Bug 6696594
1472 ELSIF p_label_type_info.business_flow_code in (6) THEN
1473 BEGIN
1474 SELECT lpn_id INTO l_outer_lpn_id
1475 FROM wsh_delivery_details
1476 WHERE delivery_detail_id = p_transaction_id;
1477 EXCEPTION
1478 WHEN others THEN
1479 IF (l_debug =1 ) THEN
1480 trace('Can not find delivery detail records with ID '|| p_transaction_id);
1481 END IF;
1482 RETURN;
1483 END;
1484 ELSIF p_label_type_info.business_flow_code in (19,36)
1485 OR p_label_type_info.business_flow_code IS NULL THEN
1486 -- Cross Dock, Pick Drop, Packing Workbench, Manual Print
1487 l_delivery_id := p_input_param.transaction_temp_id;
1488 l_outer_lpn_id := p_input_param.lpn_id;
1489 ELSE
1490 IF (l_debug = 1) THEN
1491 trace('Invalid business flow or input data, can not proceed');
1492 END IF;
1493 RETURN;
1494 END IF;
1495
1496 -- If only delivery_id is provided, check whether the organization
1497 -- is WMS enabled,
1498 -- For non-WMS org, open different cursor
1499 IF l_delivery_id IS NOT NULL AND l_outer_lpn_id IS NULL THEN
1500 SELECT organization_id
1501 INTO l_organization_id
1502 FROM wsh_new_deliveries
1503 WHERE delivery_id = l_delivery_id;
1504
1505 IF wms_install.check_install
1506 (x_return_status => l_return_status,
1507 x_msg_count => l_msg_count,
1508 x_msg_data => l_msg_data,
1509 p_organization_id => l_organization_id) THEN
1510
1511 l_wms_enabled := 1;
1512 ELSE
1513 l_wms_enabled := 0;
1514 END IF;
1515 ELSE
1516 l_wms_enabled := 1;
1517 END IF;
1518
1519 l_total_number_of_lpns := NULL;
1520 l_number_of_total := NULL;
1521
1522 IF l_cart_id IS NOT NULL THEN
1523 IF(l_debug = 1) THEN
1524 trace('Open c_wdd_mmtt with NULL txn_temp_id and cart_id as '|| l_cart_id);
1525 END IF;
1526 OPEN c_wdd_mmtt(l_txn_temp_id, l_cart_id);
1527 FETCH c_wdd_mmtt INTO l_cons_wdd;
1528 IF c_wdd_mmtt%NOTFOUND THEN
1529 CLOSE c_wdd_mmtt;
1530 RETURN;
1531 END IF;
1532 -- Get total number of Cartons
1533 --trace('Cartonization, delivery_id '||l_cons_wdd.delivery_id);
1534 IF l_cons_wdd.delivery_id IS NOT NULL THEN
1535 get_carton_count(l_cons_wdd.delivery_id
1536 , l_total_number_of_lpns
1537 , l_number_of_total);
1538 IF(l_debug = 1) THEN
1539 trace('Cartonization, got l_total_number_of_lpns '||l_total_number_of_lpns);
1540 trace(' l_number_of_total '||l_number_of_total);
1541 END IF;
1542 ELSE
1543 l_total_number_of_lpns := NULL;
1544 l_number_of_total := NULL;
1545 END IF;
1546 l_cons_wdd.number_of_total := l_number_of_total;
1547
1548 --Start Bug 6696594
1549 ELSIF l_txn_temp_id iS NOT NULL AND INV_CONTROL.G_CURRENT_RELEASE_LEVEL >= 120001 THEN
1550 IF(l_debug = 1) THEN
1551 trace('Before opening the cursor');
1552 END IF;
1553 OPEN c_wdd_mmtt(l_txn_temp_id, l_cart_id);
1554 FETCH c_wdd_mmtt INTO l_cons_wdd;
1555 IF c_wdd_mmtt%NOTFOUND THEN
1556 CLOSE c_wdd_mmtt;
1557 RETURN;
1558 END IF;
1559 IF(l_debug = 1) THEN
1560 trace('After opening the cursor');
1561 END IF;
1562 --End Bug 6696594
1563 ELSIF l_outer_lpn_id IS NOT NULL THEN
1564 -- Fetch data with outer LPN
1565 IF(l_debug = 1) THEN
1566 trace('Open c_wdd_lpn with outermost LPN ID '||l_outer_lpn_id);
1567 END IF;
1568 OPEN c_wdd_lpn(l_outer_lpn_id);
1569 FETCH c_wdd_lpn INTO l_cons_wdd;
1570 IF c_wdd_lpn%NOTFOUND THEN
1571 CLOSE c_wdd_lpn;
1572 RETURN;
1573 END IF;
1574
1575 ELSIF l_delivery_id IS NOT NULL THEN
1576 -- Fetch data with delivery
1577 IF l_wms_enabled = 1 THEN
1578 -- WMS org
1579 IF(l_debug = 1) THEN
1580 trace('Open c_wdd_del_wms with Delivery ID '||l_delivery_id);
1581 END IF;
1582 OPEN c_wdd_del_wms(l_delivery_id);
1583 FETCH c_wdd_del_wms INTO l_cons_wdd;
1584 IF c_wdd_del_wms%NOTFOUND THEN
1585 CLOSE c_wdd_del_wms;
1586 RETURN;
1587 END IF;
1588 -- Only calculate total number of LPNs and number of total
1589 -- when label is called for a delivery
1590 -- If LPN is provided, total number of LPNs and number of total are NULL
1591 l_total_number_of_lpns := 1;
1592 l_number_of_total := 1;
1593 l_cons_wdd.number_of_total := 1;
1594 ELSE -- Non WMS org
1595 IF(l_debug = 1) THEN
1596 trace('Open c_wdd_del_inv with Delivery ID '||l_delivery_id);
1597 END IF;
1598 OPEN c_wdd_del_inv(l_delivery_id);
1599 FETCH c_wdd_del_inv INTO l_cons_wdd;
1600 IF c_wdd_del_inv%NOTFOUND THEN
1601 CLOSE c_wdd_del_inv;
1602 RETURN;
1603 END IF;
1604 END IF;
1605 END IF;
1606
1607 -- Get Delivery Information , using c_deliver
1608 IF (l_cons_wdd.delivery_id IS NOT NULL) THEN
1609 IF (l_debug = 1) THEN
1610 trace('Use c_delivery with delivery_id '||l_cons_wdd.delivery_id||', put in l_cons_wdd');
1611 END IF;
1612
1613 OPEN c_delivery(l_cons_wdd.delivery_id);
1614 FETCH c_delivery INTO
1615 l_cons_wdd.delivery_number
1616 ,l_cons_wdd.waybill
1617 ,l_cons_wdd.airbill
1618 ,l_cons_wdd.bill_of_lading
1619 ,l_cons_wdd.trip_number
1620 ,l_cons_wdd.wnd_carrier_id
1621 ,l_cons_wdd.wnd_ship_method_code
1622 ,l_cons_wdd.intmed_ship_to_location_id;
1623 CLOSE c_delivery;
1624 END IF;
1625
1626 -- Create consolidated records
1627 -- Driving cursor c_del_lpn, c_del_wms, c_del_non_wms are ordered by outerlpn, item, lot
1628 -- Loop through each record, cumulatint the quantity for the same outerlpn/item/lot
1629 -- When finished for one outerlpn/item/lot combination, save the consolidated record
1630 -- into table l_consol_wdd_tb
1631 -- During the process, also get the common value for other columns which will be used
1632 -- to fetch additional data.
1633 -- If the consolidated records has different values, it will have value of NULL
1634 -- For example, if the same item/lot comes from different sales order header/line,
1635 -- the consolidated record will have NULL in so_header_id and so_line_id.
1636 -- If they come from the same so header/line, then the column will have the common value
1637
1638 consol_index := 1;
1639 l_wdd_tb.delete;
1640
1641 l_qty_index := 1;
1642
1643 LOOP
1644 IF c_wdd_mmtt%ISOPEN THEN
1645 -- Start Bug 6696594
1646
1647 IF p_label_type_info.business_flow_code in (42) AND INV_CONTROL.G_CURRENT_RELEASE_LEVEL >= 120001 THEN
1648 l_total_number_of_lpns := 0;
1649 l_number_of_total := NULL;
1650
1651 --Bug 6880623
1652 --Need to convert the MMTT line qty acc to the PickUOM defined for the subinventory
1653 --so that we print as many labels as the converted qty
1654 IF l_cons_wdd.shipped_quantity > 0 THEN
1655 IF (l_debug = 1) THEN
1656 trace('l_cons_wdd.from_subinventory ' || l_cons_wdd.from_subinventory);
1657 trace('l_cons_wdd.from_locator_id ' || l_cons_wdd.from_locator_id);
1658 trace('l_cons_wdd.organization_id ' || l_cons_wdd.organization_id);
1659 trace('l_cons_wdd.shipped_quantity ' || l_cons_wdd.shipped_quantity);
1660 trace('l_cons_wdd.uom ' || l_cons_wdd.uom);
1661 END IF;
1662
1663 INV_CONVERT.PICK_UOM_CONVERT
1664 (p_org_id => l_cons_wdd.organization_id,
1665 p_item_id => l_cons_wdd.inventory_item_id,
1666 p_sub_code => l_cons_wdd.from_subinventory,
1667 p_loc_id => null,
1668 p_alloc_uom => l_cons_wdd.uom,
1669 p_alloc_qty => l_cons_wdd.shipped_quantity,
1670 x_pick_uom => l_pick_uom,
1671 x_pick_qty => l_pick_qty,
1672 x_uom_string => l_uom_string,
1673 x_return_status => l_return_status,
1674 x_msg_data => l_msg_data,
1675 x_msg_count => l_msg_count);
1676
1677 IF ( l_return_status <> fnd_api.g_ret_sts_success ) THEN
1678 IF (l_debug = 1) THEN
1679 trace('Failed to get the pick UOM');
1680 END IF;
1681 l_loop_counter := l_cons_wdd.shipped_quantity;
1682 END IF;
1683
1684 IF ( l_return_status = fnd_api.g_ret_sts_success ) THEN
1685 IF (l_debug = 1) THEN
1686 trace('l_pick_uom ' || l_pick_uom);
1687 trace('l_pick_qty ' || l_pick_qty);
1688 trace('l_uom_string ' || l_uom_string);
1689 END IF;
1690 IF (trunc(l_pick_qty) > 0) THEN
1691 l_loop_counter := trunc(l_pick_qty);
1692 ELSE
1693 l_loop_counter := l_cons_wdd.shipped_quantity;
1694 END IF;
1695 END IF;
1696 END IF;
1697 --Bug 6880623
1698
1699 FOR l_qty_index IN 1..l_loop_counter LOOP
1700 FETCH c_wdd_mmtt INTO l_cur_wdd;
1701 l_wdd_tb(l_qty_index) := l_cons_wdd;
1702 l_number_of_total := l_qty_index;
1703 l_total_number_of_lpns := l_total_number_of_lpns + 1;
1704 l_wdd_tb(l_qty_index).number_of_total := l_number_of_total;
1705 IF (l_debug = 1) THEN
1706 trace('l_total_number_of_lpns '||l_total_number_of_lpns);
1707 trace('l_wdd_tb(l_qty_index).number_of_total '||l_wdd_tb(l_qty_index).number_of_total);
1708 END IF;
1709 IF (l_debug = 1) THEN
1710 trace('generate dummy LPN '|| to_char(sysdate, 'YYYY-MM-DD HH:DD:SS'));
1711 END IF;
1712 -- generate a dummy lpn
1713 wms_container_pub.generate_lpn
1714 (p_api_version => 1.0,
1715 x_return_status => l_return_status,
1716 x_msg_count => l_msg_count,
1717 x_msg_data => l_msg_data,
1718 p_organization_id => l_wdd_tb(l_qty_index).organization_id,
1719 p_lpn_out => l_to_lpn,
1720 p_lpn_id_out => l_to_lpn_id,
1721 p_process_id => l_process_id,
1722 p_validation_level => FND_API.G_VALID_LEVEL_NONE);
1723
1724 IF ( l_return_status <> fnd_api.g_ret_sts_success ) THEN
1725 IF (l_debug = 1) THEN
1726 trace('failed to genrate LPN');
1727 END IF;
1728 fnd_message.set_name('WMS', 'WMS_PRINT_LABEL_FAIL');
1729 fnd_msg_pub.ADD;
1730 END IF;
1731
1732 l_wdd_tb(l_qty_index).outer_lpn_id := l_to_lpn_id;
1733 IF (l_debug = 1) THEN
1734 trace('l_wdd_tb(l_qty_index).outer_lpn_id' || l_wdd_tb(l_qty_index).outer_lpn_id);
1735 END IF;
1736 END LOOP;
1737 IF c_wdd_mmtt%NOTFOUND THEN
1738 CLOSE c_wdd_mmtt;
1739 EXIT;
1740 END IF;
1741 ELSE
1742 --End Bug 6696594
1743 FETCH c_wdd_mmtt INTO l_cur_wdd;
1744 IF c_wdd_mmtt%NOTFOUND THEN
1745 CLOSE c_wdd_mmtt;
1746 l_wdd_tb(l_qty_index) := l_cons_wdd;
1747 EXIT;
1748 END IF;
1749 --Start Bug 6696594
1750 END IF;
1751 --End Bug 6696594
1752 ELSIF c_wdd_lpn%ISOPEN THEN
1753 FETCH c_wdd_lpn INTO l_cur_wdd;
1754 IF c_wdd_lpn%NOTFOUND THEN
1755 CLOSE c_wdd_lpn;
1756 l_wdd_tb(consol_index) := l_cons_wdd;
1757 EXIT;
1758 END IF;
1759 ELSIF c_wdd_del_wms%ISOPEN THEN
1760 FETCH c_wdd_del_wms INTO l_cur_wdd;
1761 IF c_wdd_del_wms%NOTFOUND THEN
1762 CLOSE c_wdd_del_wms;
1763 l_cons_wdd.number_of_total := l_number_of_total;
1764 l_wdd_tb(consol_index) := l_cons_wdd;
1765 EXIT;
1766 END IF;
1767 ELSIF c_wdd_del_inv%ISOPEN THEN
1768 FETCH c_wdd_del_inv INTO l_cur_wdd;
1769 IF c_wdd_del_inv%NOTFOUND THEN
1770 CLOSE c_wdd_del_inv;
1771 l_wdd_tb(consol_index) := l_cons_wdd;
1772 EXIT;
1773 END IF;
1774 END IF;
1775
1776
1777 -- Get Delivery Information , using c_deliver
1778 -- This only needs to done if delivery_id is different
1779 IF (l_cur_wdd.delivery_id IS NOT NULL) AND
1780 (l_cur_wdd.delivery_id <> l_cons_wdd.delivery_id) THEN
1781 OPEN c_delivery(l_cur_wdd.delivery_id);
1782 FETCH c_delivery INTO
1783 l_cur_wdd.delivery_number
1784 ,l_cur_wdd.waybill
1785 ,l_cur_wdd.airbill
1786 ,l_cur_wdd.bill_of_lading
1787 ,l_cur_wdd.trip_number
1788 ,l_cur_wdd.wnd_carrier_id
1789 ,l_cur_wdd.wnd_ship_method_code
1790 ,l_cur_wdd.intmed_ship_to_location_id;
1791 CLOSE c_delivery;
1792 ELSE
1793 -- Copy from l_cons_wdd
1794 l_cur_wdd.delivery_number := l_cons_wdd.delivery_number;
1795 l_cur_wdd.waybill := l_cons_wdd.waybill;
1796 l_cur_wdd.airbill := l_cons_wdd.airbill;
1797 l_cur_wdd.bill_of_lading := l_cons_wdd.bill_of_lading;
1798 l_cur_wdd.trip_number := l_cons_wdd.trip_number;
1799 l_cur_wdd.wnd_carrier_id := l_cons_wdd.wnd_carrier_id;
1800 l_cur_wdd.wnd_ship_method_code := l_cons_wdd.wnd_ship_method_code;
1801 l_cur_wdd.intmed_ship_to_location_id := l_cons_wdd.intmed_ship_to_location_id;
1802
1803 END IF;
1804
1805 IF (nvl(l_cur_wdd.outer_lpn_id, NULL_NUM) = nvl(l_cons_wdd.outer_lpn_id, NULL_NUM)) AND
1806 (l_cur_wdd.inventory_item_id = l_cons_wdd.inventory_item_id) AND
1807 (nvl(l_cur_wdd.lot_number, NULL_VAR) = nvl(l_cons_wdd.lot_number,NULL_VAR)) AND
1808 (nvl(l_cur_wdd.revision, NULL_VAR) = nvl(l_cons_wdd.revision, NULL_VAR)) THEN
1809 -- Same outer LPN, item, and lot
1810 -- Consolidate
1811 -- Add quantities, if UOMs are different, need to convert
1812
1813 IF l_cons_wdd.uom = l_cur_wdd.uom THEN
1814 l_cons_wdd.requested_quantity := l_cons_wdd.requested_quantity +
1815 l_cur_wdd.requested_quantity;
1816 ELSE
1817 l_conv_qty := inv_convert.inv_um_convert(l_cur_wdd.inventory_item_id, NULL,
1818 l_cur_wdd.requested_quantity, l_cur_wdd.uom,l_cons_wdd.uom,NULL,NULL);
1819 IF l_conv_qty <> -9999 THEN
1820 l_cons_wdd.requested_quantity := l_cons_wdd.requested_quantity + l_conv_qty;
1821 END IF;
1822 END IF;
1823
1824 -- For NULLable quantities
1825 -- if both values are NULL, keep it as NULL
1826 -- If one of the values is not NULL, then do the addition
1827 -- convert if uom is different
1828 IF (l_cons_wdd.shipped_quantity IS NOT NULL) OR
1829 (l_cur_wdd.shipped_quantity IS NOT NULL) THEN
1830 IF l_cons_wdd.uom = l_cur_wdd.uom THEN
1831 l_cons_wdd.shipped_quantity := nvl(l_cons_wdd.shipped_quantity,0) +
1832 nvl(l_cur_wdd.shipped_quantity,0);
1833 ELSIF l_cur_wdd.shipped_quantity IS NOT NULL THEN
1834 l_conv_qty := inv_convert.inv_um_convert(l_cur_wdd.inventory_item_id, NULL,
1835 l_cur_wdd.shipped_quantity, l_cur_wdd.uom,l_cons_wdd.uom,NULL,NULL);
1836 IF l_conv_qty <> -9999 THEN
1837 l_cons_wdd.shipped_quantity := nvl(l_cons_wdd.shipped_quantity,0) + l_conv_qty;
1838 END IF;
1839 END IF;
1840 END IF;
1841 IF (l_cons_wdd.shipped_quantity2 IS NOT NULL) OR
1842 (l_cur_wdd.shipped_quantity2 IS NOT NULL) THEN
1843 IF l_cons_wdd.uom = l_cur_wdd.uom THEN
1844 l_cons_wdd.shipped_quantity2 := nvl(l_cons_wdd.shipped_quantity2,0) +
1845 nvl(l_cur_wdd.shipped_quantity2,0);
1846 ELSIF l_cur_wdd.shipped_quantity2 IS NOT NULL THEN
1847 l_conv_qty := inv_convert.inv_um_convert(l_cur_wdd.inventory_item_id, NULL,
1848 l_cur_wdd.shipped_quantity2, l_cur_wdd.uom,l_cons_wdd.uom,NULL,NULL);
1849 IF l_conv_qty <> -9999 THEN
1850 l_cons_wdd.shipped_quantity2 := nvl(l_cons_wdd.shipped_quantity2,0) + l_conv_qty;
1851 END IF;
1852 END IF;
1853 END IF;
1854 IF (l_cons_wdd.cancelled_quantity IS NOT NULL) OR
1855 (l_cur_wdd.cancelled_quantity IS NOT NULL) THEN
1856 IF l_cons_wdd.uom = l_cur_wdd.uom THEN
1857 l_cons_wdd.cancelled_quantity := nvl(l_cons_wdd.cancelled_quantity,0) +
1858 nvl(l_cur_wdd.cancelled_quantity,0);
1859 ELSIF l_cur_wdd.cancelled_quantity IS NOT NULL THEN
1860 l_conv_qty := inv_convert.inv_um_convert(l_cur_wdd.inventory_item_id, NULL,
1861 l_cur_wdd.cancelled_quantity, l_cur_wdd.uom,l_cons_wdd.uom,NULL,NULL);
1862 IF l_conv_qty <> -9999 THEN
1863 l_cons_wdd.cancelled_quantity := nvl(l_cons_wdd.cancelled_quantity,0) + l_conv_qty;
1864 END IF;
1865 END IF;
1866 END IF;
1867 IF (l_cons_wdd.delivered_quantity IS NOT NULL) OR
1868 (l_cur_wdd.delivered_quantity IS NOT NULL) THEN
1869 IF l_cons_wdd.uom = l_cur_wdd.uom THEN
1870 l_cons_wdd.delivered_quantity := nvl(l_cons_wdd.delivered_quantity,0) +
1871 nvl(l_cur_wdd.delivered_quantity,0);
1872 ELSIF l_cur_wdd.delivered_quantity IS NOT NULL THEN
1873 l_conv_qty := inv_convert.inv_um_convert(l_cur_wdd.inventory_item_id, NULL,
1874 l_cur_wdd.delivered_quantity, l_cur_wdd.uom,l_cons_wdd.uom,NULL,NULL);
1875 IF l_conv_qty <> -9999 THEN
1876 l_cons_wdd.delivered_quantity := nvl(l_cons_wdd.delivered_quantity,0) + l_conv_qty;
1877 END IF;
1878 END IF;
1879 END IF;
1880
1881 -- Compare the following columns,
1882 -- If they have the common values for the consolidated WDDs,
1883 -- they will have the value populated, otherwise, it will have NULL
1884
1885 IF nvl(l_cur_wdd.carrier_id, NULL_NUM) <> nvl(l_cons_wdd.carrier_id, NULL_NUM) THEN
1886 l_cons_wdd.carrier_id := NULL;
1887 END IF;
1888 IF nvl(l_cur_wdd.customer_purchase_order, NULL_VAR) <> nvl(l_cons_wdd.customer_purchase_order, NULL_VAR) THEN
1889 l_cons_wdd.customer_purchase_order := NULL;
1890 END IF;
1891 IF nvl(l_cur_wdd.customer_id, NULL_NUM) <> nvl(l_cons_wdd.customer_id, NULL_NUM) THEN
1892 l_cons_wdd.customer_id := NULL;
1893 END IF;
1894 IF nvl(l_cur_wdd.ship_method_code, NULL_VAR) <> nvl(l_cons_wdd.ship_method_code, NULL_VAR) THEN
1895 l_cons_wdd.ship_method_code := NULL;
1896 END IF;
1897 IF nvl(l_cur_wdd.source_header_number, NULL_VAR) <> nvl(l_cons_wdd.source_header_number, NULL_VAR) THEN
1898 l_cons_wdd.source_header_number := NULL;
1899 l_cons_wdd.source_line_number := NULL;
1900 ELSIF nvl(l_cur_wdd.source_line_number, NULL_VAR) <> nvl(l_cons_wdd.source_line_number, NULL_VAR) THEN
1901 l_cons_wdd.source_line_number := NULL;
1902 END IF;
1903 IF nvl(l_cur_wdd.tracking_number, NULL_VAR) <> nvl(l_cons_wdd.tracking_number, NULL_VAR) THEN
1904 l_cons_wdd.tracking_number := NULL;
1905 END IF;
1906 IF nvl(l_cur_wdd.fob, NULL_VAR) <> nvl(l_cons_wdd.fob, NULL_VAR) THEN
1907 l_cons_wdd.fob := NULL;
1908 END IF;
1909 IF nvl(l_cur_wdd.customer_item_id, NULL_NUM) <> nvl(l_cons_wdd.customer_item_id, NULL_NUM) THEN
1910 l_cons_wdd.customer_item_id := NULL;
1911 END IF;
1912 IF nvl(l_cur_wdd.project_id, NULL_NUM) <> nvl(l_cons_wdd.project_id, NULL_NUM) THEN
1913 l_cons_wdd.project_id := NULL;
1914 END IF;
1915 IF nvl(l_cur_wdd.task_id, NULL_NUM) <> nvl(l_cons_wdd.task_id, NULL_NUM) THEN
1916 l_cons_wdd.task_id := NULL;
1917 END IF;
1918 IF nvl(l_cur_wdd.ship_from_location_id, NULL_NUM) <> nvl(l_cons_wdd.ship_from_location_id, NULL_NUM) THEN
1919 l_cons_wdd.ship_from_location_id := NULL;
1920 END IF;
1921 IF nvl(l_cur_wdd.ship_to_location_id, NULL_NUM) <> nvl(l_cons_wdd.ship_to_location_id, NULL_NUM) THEN
1922 l_cons_wdd.ship_to_location_id := NULL;
1923 END IF;
1924 IF nvl(l_cur_wdd.ship_to_site_use_id, NULL_NUM) <> nvl(l_cons_wdd.ship_to_site_use_id, NULL_NUM) THEN
1925 l_cons_wdd.ship_to_site_use_id := NULL;
1926 END IF;
1927 IF nvl(l_cur_wdd.ship_to_contact_id, NULL_NUM) <> nvl(l_cons_wdd.ship_to_contact_id, NULL_NUM) THEN
1928 l_cons_wdd.ship_to_contact_id := NULL;
1929 END IF;
1930
1931 IF nvl(l_cur_wdd.delivery_id, NULL_NUM) <> nvl(l_cons_wdd.delivery_id, NULL_NUM) THEN
1932 l_cons_wdd.delivery_id := NULL;
1933 IF nvl(l_cur_wdd.delivery_number, NULL_VAR) <> nvl(l_cons_wdd.delivery_number, NULL_VAR) THEN
1934 l_cons_wdd.delivery_number := NULL;
1935 END IF;
1936 IF nvl(l_cur_wdd.waybill, NULL_VAR) <> nvl(l_cons_wdd.waybill, NULL_VAR) THEN
1937 l_cons_wdd.waybill := NULL;
1938 END IF;
1939 IF nvl(l_cur_wdd.airbill, NULL_VAR) <> nvl(l_cons_wdd.airbill, NULL_VAR) THEN
1940 l_cons_wdd.airbill := NULL;
1941 END IF;
1942 IF nvl(l_cur_wdd.bill_of_lading, NULL_VAR) <> nvl(l_cons_wdd.bill_of_lading, NULL_VAR) THEN
1943 l_cons_wdd.bill_of_lading := NULL;
1944 END IF;
1945 IF nvl(l_cur_wdd.trip_number, NULL_VAR) <> nvl(l_cons_wdd.trip_number, NULL_VAR) THEN
1946 l_cons_wdd.trip_number := NULL;
1947 END IF;
1948 IF nvl(l_cur_wdd.wnd_carrier_id, NULL_NUM) <> nvl(l_cons_wdd.wnd_carrier_id, NULL_NUM) THEN
1949 l_cons_wdd.wnd_carrier_id := NULL;
1950 END IF;
1951 IF nvl(l_cur_wdd.wnd_ship_method_code, NULL_VAR) <> nvl(l_cons_wdd.wnd_ship_method_code, NULL_VAR) THEN
1952 l_cons_wdd.wnd_ship_method_code := NULL;
1953 END IF;
1954 IF nvl(l_cur_wdd.intmed_ship_to_location_id, NULL_NUM) <> nvl(l_cons_wdd.intmed_ship_to_location_id, NULL_NUM) THEN
1955 l_cons_wdd.intmed_ship_to_location_id := NULL;
1956 END IF;
1957 END IF;
1958 IF nvl(l_cur_wdd.intmed_ship_to_contact_id, NULL_NUM) <> nvl(l_cons_wdd.intmed_ship_to_contact_id, NULL_NUM) THEN
1959 l_cons_wdd.intmed_ship_to_contact_id := NULL;
1960 END IF;
1961 IF nvl(l_cur_wdd.sold_to_contact_id, NULL_NUM) <> nvl(l_cons_wdd.sold_to_contact_id, NULL_NUM) THEN
1962 l_cons_wdd.sold_to_contact_id := NULL;
1963 END IF;
1964 IF nvl(l_cur_wdd.deliver_to_location_id, NULL_NUM) <> nvl(l_cons_wdd.deliver_to_location_id, NULL_NUM) THEN
1965 l_cons_wdd.deliver_to_location_id := NULL;
1966 END IF;
1967 IF nvl(l_cur_wdd.deliver_to_contact_id, NULL_NUM) <> nvl(l_cons_wdd.deliver_to_contact_id, NULL_NUM) THEN
1968 l_cons_wdd.deliver_to_contact_id := NULL;
1969 END IF;
1970 IF nvl(l_cur_wdd.deliver_to_site_use_id, NULL_NUM) <> nvl(l_cons_wdd.deliver_to_site_use_id, NULL_NUM) THEN
1971 l_cons_wdd.deliver_to_site_use_id := NULL;
1972 END IF;
1973 IF nvl(l_cur_wdd.source_header_id, NULL_NUM) <> nvl(l_cons_wdd.source_header_id, NULL_NUM) THEN
1974 l_cons_wdd.source_header_id := NULL;
1975 END IF;
1976 IF nvl(l_cur_wdd.source_line_id, NULL_NUM) <> nvl(l_cons_wdd.source_line_id, NULL_NUM) THEN
1977 l_cons_wdd.source_line_id := NULL;
1978 END IF;
1979 IF nvl(l_cur_wdd.attribute_category, NULL_VAR) <> nvl(l_cons_wdd.attribute_category, NULL_VAR) THEN
1980 l_cons_wdd.attribute_category := NULL;
1981 END IF;
1982 IF nvl(l_cur_wdd.attribute_category, NULL_VAR) <> nvl(l_cons_wdd.attribute_category, NULL_VAR) THEN
1983 l_cons_wdd.attribute_category := NULL;
1984 END IF;
1985 IF nvl(l_cur_wdd.attribute1, NULL_VAR) <> nvl(l_cons_wdd.attribute1, NULL_VAR) THEN
1986 l_cons_wdd.attribute1 := NULL;
1987 END IF;
1988 IF nvl(l_cur_wdd.attribute2, NULL_VAR) <> nvl(l_cons_wdd.attribute2, NULL_VAR) THEN
1989 l_cons_wdd.attribute2 := NULL;
1990 END IF;
1991 IF nvl(l_cur_wdd.attribute3, NULL_VAR) <> nvl(l_cons_wdd.attribute3, NULL_VAR) THEN
1992 l_cons_wdd.attribute3 := NULL;
1993 END IF;
1994 IF nvl(l_cur_wdd.attribute4, NULL_VAR) <> nvl(l_cons_wdd.attribute4, NULL_VAR) THEN
1995 l_cons_wdd.attribute4 := NULL;
1996 END IF;
1997 IF nvl(l_cur_wdd.attribute5, NULL_VAR) <> nvl(l_cons_wdd.attribute5, NULL_VAR) THEN
1998 l_cons_wdd.attribute5 := NULL;
1999 END IF;
2000 IF nvl(l_cur_wdd.attribute6, NULL_VAR) <> nvl(l_cons_wdd.attribute6, NULL_VAR) THEN
2001 l_cons_wdd.attribute6 := NULL;
2002 END IF;
2003 IF nvl(l_cur_wdd.attribute7, NULL_VAR) <> nvl(l_cons_wdd.attribute7, NULL_VAR) THEN
2004 l_cons_wdd.attribute7 := NULL;
2005 END IF;
2006 IF nvl(l_cur_wdd.attribute8, NULL_VAR) <> nvl(l_cons_wdd.attribute8, NULL_VAR) THEN
2007 l_cons_wdd.attribute8 := NULL;
2008 END IF;
2009 IF nvl(l_cur_wdd.attribute9, NULL_VAR) <> nvl(l_cons_wdd.attribute9, NULL_VAR) THEN
2010 l_cons_wdd.attribute9 := NULL;
2011 END IF;
2012 IF nvl(l_cur_wdd.attribute10, NULL_VAR) <> nvl(l_cons_wdd.attribute10, NULL_VAR) THEN
2013 l_cons_wdd.attribute10 := NULL;
2014 END IF;
2015 IF nvl(l_cur_wdd.attribute11, NULL_VAR) <> nvl(l_cons_wdd.attribute11, NULL_VAR) THEN
2016 l_cons_wdd.attribute11 := NULL;
2017 END IF;
2018 IF nvl(l_cur_wdd.attribute12, NULL_VAR) <> nvl(l_cons_wdd.attribute12, NULL_VAR) THEN
2019 l_cons_wdd.attribute12 := NULL;
2020 END IF;
2021 IF nvl(l_cur_wdd.attribute13, NULL_VAR) <> nvl(l_cons_wdd.attribute13, NULL_VAR) THEN
2022 l_cons_wdd.attribute13 := NULL;
2023 END IF;
2024 IF nvl(l_cur_wdd.attribute14, NULL_VAR) <> nvl(l_cons_wdd.attribute14, NULL_VAR) THEN
2025 l_cons_wdd.attribute14 := NULL;
2026 END IF;
2027 IF nvl(l_cur_wdd.attribute15, NULL_VAR) <> nvl(l_cons_wdd.attribute15, NULL_VAR) THEN
2028 l_cons_wdd.attribute15 := NULL;
2029 END IF;
2030 IF nvl(l_cur_wdd.tp_attribute_category, NULL_VAR) <> nvl(l_cons_wdd.tp_attribute_category, NULL_VAR) THEN
2031 l_cons_wdd.tp_attribute_category := NULL;
2032 END IF;
2033 IF nvl(l_cur_wdd.tp_attribute1, NULL_VAR) <> nvl(l_cons_wdd.tp_attribute1, NULL_VAR) THEN
2034 l_cons_wdd.tp_attribute1 := NULL;
2035 END IF;
2036 IF nvl(l_cur_wdd.tp_attribute2, NULL_VAR) <> nvl(l_cons_wdd.tp_attribute2, NULL_VAR) THEN
2037 l_cons_wdd.tp_attribute2 := NULL;
2038 END IF;
2039 IF nvl(l_cur_wdd.tp_attribute3, NULL_VAR) <> nvl(l_cons_wdd.tp_attribute3, NULL_VAR) THEN
2040 l_cons_wdd.tp_attribute3 := NULL;
2041 END IF;
2042 IF nvl(l_cur_wdd.tp_attribute4, NULL_VAR) <> nvl(l_cons_wdd.tp_attribute4, NULL_VAR) THEN
2043 l_cons_wdd.tp_attribute4 := NULL;
2044 END IF;
2045 IF nvl(l_cur_wdd.tp_attribute5, NULL_VAR) <> nvl(l_cons_wdd.tp_attribute5, NULL_VAR) THEN
2046 l_cons_wdd.tp_attribute5 := NULL;
2047 END IF;
2048 IF nvl(l_cur_wdd.tp_attribute6, NULL_VAR) <> nvl(l_cons_wdd.tp_attribute6, NULL_VAR) THEN
2049 l_cons_wdd.tp_attribute6 := NULL;
2050 END IF;
2051 IF nvl(l_cur_wdd.tp_attribute7, NULL_VAR) <> nvl(l_cons_wdd.tp_attribute7, NULL_VAR) THEN
2052 l_cons_wdd.tp_attribute7 := NULL;
2053 END IF;
2054 IF nvl(l_cur_wdd.tp_attribute8, NULL_VAR) <> nvl(l_cons_wdd.tp_attribute8, NULL_VAR) THEN
2055 l_cons_wdd.tp_attribute8 := NULL;
2056 END IF;
2057 IF nvl(l_cur_wdd.tp_attribute9, NULL_VAR) <> nvl(l_cons_wdd.tp_attribute9, NULL_VAR) THEN
2058 l_cons_wdd.tp_attribute9 := NULL;
2059 END IF;
2060 IF nvl(l_cur_wdd.tp_attribute10, NULL_VAR) <> nvl(l_cons_wdd.tp_attribute10, NULL_VAR) THEN
2061 l_cons_wdd.tp_attribute10 := NULL;
2062 END IF;
2063 IF nvl(l_cur_wdd.tp_attribute11, NULL_VAR) <> nvl(l_cons_wdd.tp_attribute11, NULL_VAR) THEN
2064 l_cons_wdd.tp_attribute11 := NULL;
2065 END IF;
2066 IF nvl(l_cur_wdd.tp_attribute12, NULL_VAR) <> nvl(l_cons_wdd.tp_attribute12, NULL_VAR) THEN
2067 l_cons_wdd.tp_attribute12 := NULL;
2068 END IF;
2069 IF nvl(l_cur_wdd.tp_attribute13, NULL_VAR) <> nvl(l_cons_wdd.tp_attribute13, NULL_VAR) THEN
2070 l_cons_wdd.tp_attribute13 := NULL;
2071 END IF;
2072 IF nvl(l_cur_wdd.tp_attribute14, NULL_VAR) <> nvl(l_cons_wdd.tp_attribute14, NULL_VAR) THEN
2073 l_cons_wdd.tp_attribute14 := NULL;
2074 END IF;
2075 IF nvl(l_cur_wdd.tp_attribute15, NULL_VAR) <> nvl(l_cons_wdd.tp_attribute15, NULL_VAR) THEN
2076 l_cons_wdd.tp_attribute15 := NULL;
2077 END IF;
2078
2079 ELSE
2080 -- Check if Outer LPN changes, calculate total number of LPNs
2081 IF nvl(p_label_type_info.business_flow_code, NULL_NUM) <> 22 THEN
2082 IF nvl(l_cur_wdd.outer_lpn_id, NULL_NUM) <> nvl(l_cons_wdd.outer_lpn_id, NULL_NUM) THEN
2083 l_total_number_of_LPNs := l_total_number_of_LPNs + 1;
2084 l_number_of_total := l_number_of_total + 1;
2085 END IF;
2086 l_cur_wdd.number_of_total := l_number_of_total;
2087 ELSE
2088 l_cur_wdd.number_of_total := l_number_of_total;
2089 END IF;
2090
2091 -- Different outer LPN, item, or lot
2092 -- Put the last consolidated wdd into l_wdd_tb and start a new consolidation record
2093 l_wdd_tb(consol_index) := l_cons_wdd;
2094 l_cons_wdd := l_cur_wdd;
2095 l_cur_wdd := NULL;
2096 consol_index := consol_index + 1;
2097
2098 END IF; -- Whether l_cur_wdd is same as l_cons_wdd
2099
2100 END LOOP; -- Loop of creating consolidated records
2101
2102
2103 END IF; -- business flow code of 18, or 22, or others
2104
2105 IF (l_debug = 1) THEN
2106 trace('Finished creating WDD record table, l_wdd_tb has ' ||l_wdd_tb.count||' records');
2107 trace('Total number of LPNs: '||l_total_number_of_lpns);
2108 END IF;
2109
2110 -- Start generating labels
2111 -- Loop through each wdd record in l_wdd_tb and create labels
2112 new_label := true;
2113 l_cur_wdd := NULL;
2114
2115 l_content_rec_index := 0;
2116 l_shipping_content_data := '';
2117 row_index_per_label := 0;
2118 l_label_index := 0;
2119 l_prev_format_id := NULL_NUM;
2120 l_prev_lpn_id := NULL_NUM;
2121
2122 -- Date, Time, User
2123 g_column_elements_table(get_column_hash_value('current_date')).column_content := INV_LABEL.G_DATE ;
2124 g_column_elements_table(get_column_hash_value('current_time')).column_content := INV_LABEL.G_TIME ;
2125 g_column_elements_table(get_column_hash_value('request_user')).column_content := INV_LABEL.G_USER ;
2126
2127
2128 FOR l_wdd_index IN 1..l_wdd_tb.count LOOP
2129 l_prev_wdd := l_cur_wdd;
2130 l_cur_wdd := l_wdd_tb(l_wdd_index);
2131
2132 IF(l_debug = 1) THEN
2133 trace('In l_wdd_tb loop, l_wdd_index='||l_wdd_index);
2134 trace(' inventory_item_id '||l_cur_wdd.inventory_item_id);
2135 trace(' lot_number '||l_cur_wdd.lot_number);
2136 trace(' requested_qty '|| l_cur_wdd.requested_quantity);
2137 END IF;
2138
2139 -- Put data from l_cur_wdd to hash table
2140
2141 l_progress := 'Assign l_cur_wdd to hash table';
2142
2143 g_column_elements_table(get_column_hash_value('
2144 ')).column_content := l_cur_wdd.requested_quantity;
2145 g_column_elements_table(get_column_hash_value('shipped_quantity')).column_content := l_cur_wdd.shipped_quantity;
2146 g_column_elements_table(get_column_hash_value('shipped_quantity2')).column_content := l_cur_wdd.shipped_quantity2;
2147 g_column_elements_table(get_column_hash_value('uom')).column_content := l_cur_wdd.uom ;
2148 g_column_elements_table(get_column_hash_value('revision')).column_content := l_cur_wdd.revision;
2149 g_column_elements_table(get_column_hash_value('lot_number')).column_content := l_cur_wdd.lot_number ;
2150 g_column_elements_table(get_column_hash_value('cancelled_quantity')).column_content := l_cur_wdd.cancelled_quantity;
2151 g_column_elements_table(get_column_hash_value('delivered_quantity')).column_content := l_cur_wdd.delivered_quantity;
2152 g_column_elements_table(get_column_hash_value('customer_purchase_order')).column_content := l_cur_wdd.customer_purchase_order;
2153 g_column_elements_table(get_column_hash_value('sales_order_number')).column_content := l_cur_wdd.source_header_number ;
2154 g_column_elements_table(get_column_hash_value('sales_order_line')).column_content := l_cur_wdd.source_line_number ;
2155 g_column_elements_table(get_column_hash_value('tracking_number')).column_content := l_cur_wdd.tracking_number;
2156 g_column_elements_table(get_column_hash_value('fob')).column_content := l_cur_wdd.fob;
2157 g_column_elements_table(get_column_hash_value('del_detail_attribute_category')).column_content := l_cur_wdd.attribute_category;
2158 g_column_elements_table(get_column_hash_value('del_detail_attribute1')).column_content := l_cur_wdd.attribute1;
2159 g_column_elements_table(get_column_hash_value('del_detail_attribute2')).column_content := l_cur_wdd.attribute2;
2160 g_column_elements_table(get_column_hash_value('del_detail_attribute3')).column_content := l_cur_wdd.attribute3;
2161 g_column_elements_table(get_column_hash_value('del_detail_attribute4')).column_content := l_cur_wdd.attribute4;
2162 g_column_elements_table(get_column_hash_value('del_detail_attribute5')).column_content := l_cur_wdd.attribute5;
2163 g_column_elements_table(get_column_hash_value('del_detail_attribute6')).column_content := l_cur_wdd.attribute6;
2164 g_column_elements_table(get_column_hash_value('del_detail_attribute7')).column_content := l_cur_wdd.attribute7;
2165 g_column_elements_table(get_column_hash_value('del_detail_attribute8')).column_content := l_cur_wdd.attribute8;
2166 g_column_elements_table(get_column_hash_value('del_detail_attribute9')).column_content := l_cur_wdd.attribute9;
2167 g_column_elements_table(get_column_hash_value('del_detail_attribute10')).column_content := l_cur_wdd.attribute10;
2168 g_column_elements_table(get_column_hash_value('del_detail_attribute11')).column_content := l_cur_wdd.attribute11;
2169 g_column_elements_table(get_column_hash_value('del_detail_attribute12')).column_content := l_cur_wdd.attribute12;
2170 g_column_elements_table(get_column_hash_value('del_detail_attribute13')).column_content := l_cur_wdd.attribute13;
2171 g_column_elements_table(get_column_hash_value('del_detail_attribute14')).column_content := l_cur_wdd.attribute14;
2172 g_column_elements_table(get_column_hash_value('del_detail_attribute15')).column_content := l_cur_wdd.attribute15;
2173 g_column_elements_table(get_column_hash_value('del_detail_tp_attr_category')).column_content := l_cur_wdd.tp_attribute_category;
2174 g_column_elements_table(get_column_hash_value('del_detail_tp_attr1')).column_content := l_cur_wdd.tp_attribute1;
2175 g_column_elements_table(get_column_hash_value('del_detail_tp_attr2')).column_content := l_cur_wdd.tp_attribute2;
2176 g_column_elements_table(get_column_hash_value('del_detail_tp_attr3')).column_content := l_cur_wdd.tp_attribute3;
2177 g_column_elements_table(get_column_hash_value('del_detail_tp_attr4')).column_content := l_cur_wdd.tp_attribute4;
2178 g_column_elements_table(get_column_hash_value('del_detail_tp_attr5')).column_content := l_cur_wdd.tp_attribute5;
2179 g_column_elements_table(get_column_hash_value('del_detail_tp_attr6')).column_content := l_cur_wdd.tp_attribute6;
2180 g_column_elements_table(get_column_hash_value('del_detail_tp_attr7')).column_content := l_cur_wdd.tp_attribute7;
2181 g_column_elements_table(get_column_hash_value('del_detail_tp_attr8')).column_content := l_cur_wdd.tp_attribute8;
2182 g_column_elements_table(get_column_hash_value('del_detail_tp_attr9')).column_content := l_cur_wdd.tp_attribute9;
2183 g_column_elements_table(get_column_hash_value('del_detail_tp_attr10')).column_content := l_cur_wdd.tp_attribute10;
2184 g_column_elements_table(get_column_hash_value('del_detail_tp_attr11')).column_content := l_cur_wdd.tp_attribute11;
2185 g_column_elements_table(get_column_hash_value('del_detail_tp_attr12')).column_content := l_cur_wdd.tp_attribute12;
2186 g_column_elements_table(get_column_hash_value('del_detail_tp_attr13')).column_content := l_cur_wdd.tp_attribute13;
2187 g_column_elements_table(get_column_hash_value('del_detail_tp_attr14')).column_content := l_cur_wdd.tp_attribute14;
2188 g_column_elements_table(get_column_hash_value('del_detail_tp_attr15')).column_content := l_cur_wdd.tp_attribute15;
2189 g_column_elements_table(get_column_hash_value('from_subinventory')).column_content := l_cur_wdd.from_subinventory;
2190 g_column_elements_table(get_column_hash_value('from_locator')).column_content := l_cur_wdd.from_locator;
2191 g_column_elements_table(get_column_hash_value('to_subinventory')).column_content := l_cur_wdd.to_subinventory;
2192 g_column_elements_table(get_column_hash_value('to_locator')).column_content := l_cur_wdd.to_locator;
2193 g_column_elements_table(get_column_hash_value('total_number_of_lpns')).column_content := l_total_number_of_lpns;
2194 g_column_elements_table(get_column_hash_value('lpn_number_of_total')).column_content := l_cur_wdd.number_of_total;
2195 g_column_elements_table(get_column_hash_value('delivery_number')).column_content := l_cur_wdd.delivery_number;
2196 g_column_elements_table(get_column_hash_value('waybill')).column_content := l_cur_wdd.waybill;
2197 g_column_elements_table(get_column_hash_value('airbill')).column_content := l_cur_wdd.airbill;
2198 g_column_elements_table(get_column_hash_value('bill_of_lading')).column_content := l_cur_wdd.bill_of_lading;
2199 g_column_elements_table(get_column_hash_value('trip_number')).column_content := l_cur_wdd.trip_number;
2200
2201
2202 -- Open the following cursors to retrieve more date for labels
2203 -- Cursor only needs to be opened if the primary key value is not NULL
2204 -- and the primary key value is different than before
2205
2206
2207 IF l_cur_wdd.organization_id <> nvl(l_prev_wdd.organization_id, NULL_NUM) THEN
2208 l_progress := 'Use org cursor to hash table ';
2209 OPEN c_org_code(l_cur_wdd.organization_id);
2210 FETCH c_org_code INTO g_column_elements_table(get_column_hash_value('organization')).column_content;
2211 CLOSE c_org_code;
2212 g_column_elements_table(get_column_hash_value('organization_code')).column_content :=
2213 g_column_elements_table(get_column_hash_value('organization')).column_content;
2214
2215 OPEN c_org_name(l_cur_wdd.organization_id);
2216 FETCH c_org_name INTO
2217 g_column_elements_table(get_column_hash_value('organization_name')).column_content,
2218 g_column_elements_table(get_column_hash_value('org_tel_number')).column_content,
2219 g_column_elements_table(get_column_hash_value('org_fax_number')).column_content;
2220 CLOSE c_org_name;
2221 END IF;
2222
2223
2224 IF l_cur_wdd.outer_lpn_id IS NOT NULL AND l_cur_wdd.outer_lpn_id <> nvl(l_prev_wdd.outer_lpn_id, NULL_NUM) THEN
2225 l_progress := 'Use outer lpn cursor to hash table';
2226 OPEN c_wdd_outer_lpn(l_cur_wdd.outer_lpn_id);
2227 FETCH c_wdd_outer_lpn INTO
2228 g_column_elements_table(get_column_hash_value('del_lpn_load_seq_num')).column_content
2229 ,g_column_elements_table(get_column_hash_value('del_lpn_net_weight')).column_content
2230 ,g_column_elements_table(get_column_hash_value('del_lpn_gross_weight')).column_content
2231 ,g_column_elements_table(get_column_hash_value('del_lpn_tracking_number')).column_content
2232 ,g_column_elements_table(get_column_hash_value('shipment_gross_weight')).column_content
2233 ,g_column_elements_table(get_column_hash_value('shipment_gross_weight_uom')).column_content
2234 ,g_column_elements_table(get_column_hash_value('shipment_tare_weight')).column_content
2235 ,g_column_elements_table(get_column_hash_value('shipment_tare_weight_uom')).column_content
2236 ,g_column_elements_table(get_column_hash_value('shipment_volume')).column_content
2237 ,g_column_elements_table(get_column_hash_value('shipment_volume_uom')).column_content;
2238 CLOSE c_wdd_outer_lpn;
2239 END IF;
2240
2241 IF l_cur_wdd.inventory_item_id IS NOT NULL AND l_cur_wdd.inventory_item_id <> nvl(l_prev_wdd.inventory_item_id, NULL_NUM) THEN
2242 l_progress := 'Use item cursor to hash table';
2243 OPEN c_item(l_cur_wdd.organization_id, l_cur_wdd.inventory_item_id);
2244 FETCH c_item INTO
2245 g_column_elements_table(get_column_hash_value('item')).column_content
2246 ,g_column_elements_table(get_column_hash_value('item_description')).column_content
2247 ,g_column_elements_table(get_column_hash_value('secondary_uom_code')).column_content
2248 ,g_column_elements_table(get_column_hash_value('item_attribute_category')).column_content
2249 ,g_column_elements_table(get_column_hash_value('item_attribute1')).column_content
2250 ,g_column_elements_table(get_column_hash_value('item_attribute2')).column_content
2251 ,g_column_elements_table(get_column_hash_value('item_attribute3')).column_content
2252 ,g_column_elements_table(get_column_hash_value('item_attribute4')).column_content
2253 ,g_column_elements_table(get_column_hash_value('item_attribute5')).column_content
2254 ,g_column_elements_table(get_column_hash_value('item_attribute6')).column_content
2255 ,g_column_elements_table(get_column_hash_value('item_attribute7')).column_content
2256 ,g_column_elements_table(get_column_hash_value('item_attribute8')).column_content
2257 ,g_column_elements_table(get_column_hash_value('item_attribute9')).column_content
2258 ,g_column_elements_table(get_column_hash_value('item_attribute10')).column_content
2259 ,g_column_elements_table(get_column_hash_value('item_attribute11')).column_content
2260 ,g_column_elements_table(get_column_hash_value('item_attribute12')).column_content
2261 ,g_column_elements_table(get_column_hash_value('item_attribute13')).column_content
2262 ,g_column_elements_table(get_column_hash_value('item_attribute14')).column_content
2263 ,g_column_elements_table(get_column_hash_value('item_attribute15')).column_content
2264 ,g_column_elements_table(get_column_hash_value('item_hazard_class')).column_content;
2265 CLOSE c_item;
2266 END IF;
2267
2268 IF l_cur_wdd.customer_item_id IS NOT NULL AND l_cur_wdd.customer_item_id <> nvl(l_prev_wdd.customer_item_id, NULL_NUM) THEN
2269 l_progress := 'Use customer item cursor to hash table';
2270 OPEN c_customer_item(l_cur_wdd.customer_item_id);
2271 FETCH c_customer_item INTO
2272 g_column_elements_table(get_column_hash_value('customer_part_number')).column_content,
2273 g_column_elements_table(get_column_hash_value('cust_item_attribute_category')).column_content,
2274 g_column_elements_table(get_column_hash_value('customer_item_attribute1')).column_content,
2275 g_column_elements_table(get_column_hash_value('customer_item_attribute2')).column_content,
2276 g_column_elements_table(get_column_hash_value('customer_item_attribute3')).column_content,
2277 g_column_elements_table(get_column_hash_value('customer_item_attribute4')).column_content,
2278 g_column_elements_table(get_column_hash_value('customer_item_attribute5')).column_content,
2279 g_column_elements_table(get_column_hash_value('customer_item_attribute6')).column_content,
2280 g_column_elements_table(get_column_hash_value('customer_item_attribute7')).column_content,
2281 g_column_elements_table(get_column_hash_value('customer_item_attribute8')).column_content,
2282 g_column_elements_table(get_column_hash_value('customer_item_attribute9')).column_content,
2283 g_column_elements_table(get_column_hash_value('customer_item_attribute10')).column_content,
2284 g_column_elements_table(get_column_hash_value('customer_item_attribute11')).column_content,
2285 g_column_elements_table(get_column_hash_value('customer_item_attribute12')).column_content,
2286 g_column_elements_table(get_column_hash_value('customer_item_attribute13')).column_content,
2287 g_column_elements_table(get_column_hash_value('customer_item_attribute14')).column_content,
2288 g_column_elements_table(get_column_hash_value('customer_item_attribute15')).column_content;
2289 CLOSE c_customer_item;
2290 END IF;
2291
2292 IF l_cur_wdd.customer_id IS NOT NULL AND l_cur_wdd.customer_id <> nvl(l_prev_wdd.customer_id, NULL_NUM) THEN
2293 l_progress := 'Use customer cursor to hash table';
2294 OPEN c_customer(l_cur_wdd.customer_id);
2295 FETCH c_customer INTO
2296 g_column_elements_table(get_column_hash_value('customer')).column_content,
2297 g_column_elements_table(get_column_hash_value('customer_number')).column_content;
2298 CLOSE c_customer;
2299 END IF;
2300
2301 IF l_cur_wdd.project_id IS NOT NULL AND l_cur_wdd.project_id <> nvl(l_prev_wdd.project_id, NULL_NUM) THEN
2302 l_progress := 'Use project cursor to hash table';
2303 OPEN c_project(l_cur_wdd.project_id);
2304 FETCH c_project INTO
2305 g_column_elements_table(get_column_hash_value('project')).column_content;
2306 CLOSE c_project;
2307 END IF;
2308
2309 IF l_cur_wdd.task_id IS NOT NULL AND l_cur_wdd.task_id <> nvl(l_prev_wdd.task_id, NULL_NUM) THEN
2310 l_progress := 'Use task cursor to hash table';
2311 OPEN c_task(l_cur_wdd.task_id);
2312 FETCH c_task INTO
2313 g_column_elements_table(get_column_hash_value('task')).column_content;
2314 CLOSE c_task;
2315 END IF;
2316
2317 IF l_cur_wdd.lot_number IS NOT NULL AND l_cur_wdd.lot_number <> nvl(l_prev_wdd.lot_number, NULL_VAR) THEN
2318 l_progress := 'Use lot cursor to hash table';
2319 trace('c_lot_number '||l_cur_wdd.organization_id||' '||l_cur_wdd.inventory_item_id||' '||l_cur_wdd.lot_number);
2320 OPEN c_lot_number(l_cur_wdd.organization_id, l_cur_wdd.inventory_item_id, l_cur_wdd.lot_number);
2321 FETCH c_lot_number INTO
2322 g_column_elements_table(get_column_hash_value('lot_number_status')).column_content,
2323 g_column_elements_table(get_column_hash_value('lot_expiration_date')).column_content,
2324 g_column_elements_table(get_column_hash_value('lot_attribute_category')).column_content,
2325 g_column_elements_table(get_column_hash_value('lot_c_attribute1')).column_content,
2326 g_column_elements_table(get_column_hash_value('lot_c_attribute2')).column_content,
2327 g_column_elements_table(get_column_hash_value('lot_c_attribute3')).column_content,
2328 g_column_elements_table(get_column_hash_value('lot_c_attribute4')).column_content,
2329 g_column_elements_table(get_column_hash_value('lot_c_attribute5')).column_content,
2330 g_column_elements_table(get_column_hash_value('lot_c_attribute6')).column_content,
2331 g_column_elements_table(get_column_hash_value('lot_c_attribute7')).column_content,
2332 g_column_elements_table(get_column_hash_value('lot_c_attribute8')).column_content,
2333 g_column_elements_table(get_column_hash_value('lot_c_attribute9')).column_content,
2334 g_column_elements_table(get_column_hash_value('lot_c_attribute10')).column_content,
2335 g_column_elements_table(get_column_hash_value('lot_c_attribute11')).column_content,
2336 g_column_elements_table(get_column_hash_value('lot_c_attribute12')).column_content,
2337 g_column_elements_table(get_column_hash_value('lot_c_attribute13')).column_content,
2338 g_column_elements_table(get_column_hash_value('lot_c_attribute14')).column_content,
2339 g_column_elements_table(get_column_hash_value('lot_c_attribute15')).column_content,
2340 g_column_elements_table(get_column_hash_value('lot_c_attribute16')).column_content,
2341 g_column_elements_table(get_column_hash_value('lot_c_attribute17')).column_content,
2342 g_column_elements_table(get_column_hash_value('lot_c_attribute18')).column_content,
2343 g_column_elements_table(get_column_hash_value('lot_c_attribute19')).column_content,
2344 g_column_elements_table(get_column_hash_value('lot_c_attribute20')).column_content,
2345 g_column_elements_table(get_column_hash_value('lot_d_attribute1')).column_content,
2346 g_column_elements_table(get_column_hash_value('lot_d_attribute2')).column_content,
2347 g_column_elements_table(get_column_hash_value('lot_d_attribute3')).column_content,
2348 g_column_elements_table(get_column_hash_value('lot_d_attribute4')).column_content,
2349 g_column_elements_table(get_column_hash_value('lot_d_attribute5')).column_content,
2350 g_column_elements_table(get_column_hash_value('lot_d_attribute6')).column_content,
2351 g_column_elements_table(get_column_hash_value('lot_d_attribute7')).column_content,
2352 g_column_elements_table(get_column_hash_value('lot_d_attribute8')).column_content,
2353 g_column_elements_table(get_column_hash_value('lot_d_attribute9')).column_content,
2354 g_column_elements_table(get_column_hash_value('lot_d_attribute10')).column_content,
2355 g_column_elements_table(get_column_hash_value('lot_n_attribute1')).column_content,
2356 g_column_elements_table(get_column_hash_value('lot_n_attribute2')).column_content,
2357 g_column_elements_table(get_column_hash_value('lot_n_attribute3')).column_content,
2358 g_column_elements_table(get_column_hash_value('lot_n_attribute4')).column_content,
2359 g_column_elements_table(get_column_hash_value('lot_n_attribute5')).column_content,
2360 g_column_elements_table(get_column_hash_value('lot_n_attribute6')).column_content,
2361 g_column_elements_table(get_column_hash_value('lot_n_attribute7')).column_content,
2362 g_column_elements_table(get_column_hash_value('lot_n_attribute8')).column_content,
2363 g_column_elements_table(get_column_hash_value('lot_n_attribute9')).column_content,
2364 g_column_elements_table(get_column_hash_value('lot_n_attribute10')).column_content,
2365 g_column_elements_table(get_column_hash_value('lot_country_of_origin')).column_content,
2366 g_column_elements_table(get_column_hash_value('lot_grade_code')).column_content,
2367 g_column_elements_table(get_column_hash_value('lot_origination_date')).column_content,
2368 g_column_elements_table(get_column_hash_value('lot_date_code')).column_content,
2369 g_column_elements_table(get_column_hash_value('lot_change_date')).column_content,
2370 g_column_elements_table(get_column_hash_value('lot_age')).column_content,
2371 g_column_elements_table(get_column_hash_value('lot_retest_date')).column_content,
2372 g_column_elements_table(get_column_hash_value('lot_maturity_date')).column_content,
2373 g_column_elements_table(get_column_hash_value('lot_item_size')).column_content,
2374 g_column_elements_table(get_column_hash_value('lot_color')).column_content,
2375 g_column_elements_table(get_column_hash_value('lot_volume')).column_content,
2376 g_column_elements_table(get_column_hash_value('lot_volume_uom')).column_content,
2377 g_column_elements_table(get_column_hash_value('lot_place_of_origin')).column_content,
2378 g_column_elements_table(get_column_hash_value('lot_best_by_date')).column_content,
2379 g_column_elements_table(get_column_hash_value('lot_length')).column_content,
2380 g_column_elements_table(get_column_hash_value('lot_length_uom')).column_content,
2381 g_column_elements_table(get_column_hash_value('lot_recycled_cont')).column_content,
2382 g_column_elements_table(get_column_hash_value('lot_thickness')).column_content,
2383 g_column_elements_table(get_column_hash_value('lot_thickness_uom')).column_content,
2384 g_column_elements_table(get_column_hash_value('lot_width')).column_content,
2385 g_column_elements_table(get_column_hash_value('lot_width_uom')).column_content,
2386 g_column_elements_table(get_column_hash_value('lot_curl')).column_content,
2387 g_column_elements_table(get_column_hash_value('lot_vendor')).column_content,
2388 g_column_elements_table(get_column_hash_value('parent_lot_number')).column_content,
2389 g_column_elements_table(get_column_hash_value('expiration_action_date')).column_content,
2390 g_column_elements_table(get_column_hash_value('origination_type')).column_content,
2391 g_column_elements_table(get_column_hash_value('hold_date')).column_content,
2392 g_column_elements_table(get_column_hash_value('expiration_action_code')).column_content,
2393 g_column_elements_table(get_column_hash_value('supplier_lot_number')).column_content;
2394 CLOSE c_lot_number;
2395 END IF;
2396
2397
2398 IF l_cur_wdd.outer_lpn_id IS NOT NULL AND l_cur_wdd.outer_lpn_id <> nvl(l_prev_wdd.outer_lpn_id, NULL_NUM) THEN
2399 l_progress := 'Use lpn cursor to hash table';
2400 OPEN c_lpn(l_cur_wdd.outer_lpn_id);
2401 FETCH c_lpn INTO
2402 g_column_elements_table(get_column_hash_value('lpn')).column_content,
2403 g_column_elements_table(get_column_hash_value('lpn_volume')).column_content,
2404 g_column_elements_table(get_column_hash_value('lpn_volume_uom')).column_content,
2405 g_column_elements_table(get_column_hash_value('lpn_gross_weight')).column_content,
2406 g_column_elements_table(get_column_hash_value('lpn_gross_weight_uom')).column_content,
2407 g_column_elements_table(get_column_hash_value('lpn_tare_weight')).column_content,
2408 g_column_elements_table(get_column_hash_value('lpn_tare_weight_uom')).column_content,
2409 l_cur_wdd.to_subinventory,
2410 l_cur_wdd.to_locator_id,
2411 l_cur_wdd.to_locator,
2412 g_column_elements_table(get_column_hash_value('lpn_attribute_category')).column_content,
2413 g_column_elements_table(get_column_hash_value('lpn_attribute1')).column_content,
2414 g_column_elements_table(get_column_hash_value('lpn_attribute2')).column_content,
2415 g_column_elements_table(get_column_hash_value('lpn_attribute3')).column_content,
2416 g_column_elements_table(get_column_hash_value('lpn_attribute4')).column_content,
2417 g_column_elements_table(get_column_hash_value('lpn_attribute5')).column_content,
2418 g_column_elements_table(get_column_hash_value('lpn_attribute6')).column_content,
2419 g_column_elements_table(get_column_hash_value('lpn_attribute7')).column_content,
2420 g_column_elements_table(get_column_hash_value('lpn_attribute8')).column_content,
2421 g_column_elements_table(get_column_hash_value('lpn_attribute9')).column_content,
2422 g_column_elements_table(get_column_hash_value('lpn_attribute10')).column_content,
2423 g_column_elements_table(get_column_hash_value('lpn_attribute11')).column_content,
2424 g_column_elements_table(get_column_hash_value('lpn_attribute12')).column_content,
2425 g_column_elements_table(get_column_hash_value('lpn_attribute13')).column_content,
2426 g_column_elements_table(get_column_hash_value('lpn_attribute14')).column_content,
2427 g_column_elements_table(get_column_hash_value('lpn_attribute15')).column_content,
2428 g_column_elements_table(get_column_hash_value('lpn_container_item')).column_content;
2429 CLOSE c_lpn;
2430
2431
2432 IF l_cur_wdd.to_subinventory IS NOT NULL THEN
2433 g_column_elements_table(get_column_hash_value('to_subinventory')).column_content := l_cur_wdd.to_subinventory;
2434 END IF;
2435 IF l_cur_wdd.to_locator_id IS NOT NULL THEN
2436 g_column_elements_table(get_column_hash_value('to_locator')).column_content := l_cur_wdd.to_locator;
2437 END IF;
2438
2439 -- Get Box Count
2440 -- Box Count = Number of Immediate Child LPNs of the Outer LPN
2441 BEGIN
2442 SELECT count(lpn_id)
2443 INTO g_column_elements_table(get_column_hash_value('box_count')).column_content
2444 FROM wms_license_plate_numbers
2445 WHERE parent_lpn_id IS NOT NULL
2446 AND parent_lpn_id = l_cur_wdd.outer_lpn_id
2447 AND outermost_lpn_id = l_cur_wdd.outer_lpn_id;
2448
2449 EXCEPTION
2450 WHEN others THEN
2451 g_column_elements_table(get_column_hash_value('box_count')).column_content := NULL;
2452 END;
2453
2454 END IF;
2455
2456
2457 IF nvl(l_cur_wdd.wnd_carrier_id, l_cur_wdd.carrier_id) IS NOT NULL AND
2458 nvl(l_cur_wdd.wnd_carrier_id, l_cur_wdd.carrier_id) <> nvl(nvl(l_prev_wdd.wnd_carrier_id, l_prev_wdd.carrier_id), NULL_NUM) THEN
2459 l_progress := 'Use carrier cursor to hash table';
2460 OPEN c_carrier(nvl(l_cur_wdd.wnd_carrier_id, l_cur_wdd.carrier_id));
2461 FETCH c_carrier INTO
2462 g_column_elements_table(get_column_hash_value('carrier')).column_content;
2463 CLOSE c_carrier;
2464 END IF;
2465
2466 IF nvl(l_cur_wdd.wnd_ship_method_code, l_cur_wdd.ship_method_code) IS NOT NULL AND
2467 nvl(l_cur_wdd.wnd_ship_method_code, l_cur_wdd.ship_method_code) <> nvl(nvl(l_prev_wdd.wnd_ship_method_code, l_prev_wdd.ship_method_code), NULL_VAR) THEN
2468 l_progress := 'Use ship method cursor to hash table';
2469 OPEN c_ship_method(nvl(l_cur_wdd.wnd_ship_method_code, l_cur_wdd.ship_method_code) );
2470 FETCH c_ship_method INTO
2471 g_column_elements_table(get_column_hash_value('ship_method')).column_content;
2472 CLOSE c_ship_method;
2473 END IF;
2474
2475
2476 IF l_cur_wdd.ship_from_location_id IS NOT NULL AND l_cur_wdd.ship_from_location_id <> nvl(l_prev_wdd.ship_from_location_id, NULL_NUM) THEN
2477 l_progress := 'Use ship from cursor to hash table';
2478 OPEN c_address(l_cur_wdd.ship_from_location_id);
2479 FETCH c_address INTO
2480 g_column_elements_table(get_column_hash_value('ship_from_address1')).column_content,
2481 g_column_elements_table(get_column_hash_value('ship_from_address2')).column_content,
2482 g_column_elements_table(get_column_hash_value('ship_from_address3')).column_content,
2483 g_column_elements_table(get_column_hash_value('ship_from_address4')).column_content,
2484 g_column_elements_table(get_column_hash_value('ship_from_city')).column_content,
2485 g_column_elements_table(get_column_hash_value('ship_from_postal_code')).column_content,
2486 g_column_elements_table(get_column_hash_value('ship_from_state')).column_content,
2487 g_column_elements_table(get_column_hash_value('ship_from_county')).column_content,
2488 g_column_elements_table(get_column_hash_value('ship_from_country')).column_content,
2489 g_column_elements_table(get_column_hash_value('ship_from_province')).column_content,
2490 g_column_elements_table(get_column_hash_value('ship_from_location')).column_content,
2491 g_column_elements_table(get_column_hash_value('ship_from_location_description')).column_content;
2492 CLOSE c_address;
2493 END IF;
2494 IF l_cur_wdd.ship_to_location_id IS NOT NULL AND l_cur_wdd.ship_to_location_id <> nvl(l_prev_wdd.ship_to_location_id, NULL_NUM) THEN
2495 l_progress := 'Use ship to cursor to hash table';
2496 OPEN c_address(l_cur_wdd.ship_to_location_id);
2497 FETCH c_address INTO
2498 g_column_elements_table(get_column_hash_value('ship_to_address1')).column_content,
2499 g_column_elements_table(get_column_hash_value('ship_to_address2')).column_content,
2500 g_column_elements_table(get_column_hash_value('ship_to_address3')).column_content,
2501 g_column_elements_table(get_column_hash_value('ship_to_address4')).column_content,
2502 g_column_elements_table(get_column_hash_value('ship_to_city')).column_content,
2503 g_column_elements_table(get_column_hash_value('ship_to_postal_code')).column_content,
2504 g_column_elements_table(get_column_hash_value('ship_to_state')).column_content,
2505 g_column_elements_table(get_column_hash_value('ship_to_county')).column_content,
2506 g_column_elements_table(get_column_hash_value('ship_to_country')).column_content,
2507 g_column_elements_table(get_column_hash_value('ship_to_province')).column_content,
2508 g_column_elements_table(get_column_hash_value('customer_site')).column_content,
2509 g_column_elements_table(get_column_hash_value('ship_to_loc_desc')).column_content;
2510 CLOSE c_address;
2511
2512 OPEN c_phone_fax(l_cur_wdd.ship_to_location_id, 'PHONE');
2513 FETCH c_phone_fax INTO
2514 g_column_elements_table(get_column_hash_value('customer_site_tel_number')).column_content;
2515 CLOSE c_phone_fax;
2516
2517 OPEN c_phone_fax(l_cur_wdd.ship_to_location_id, 'FAX');
2518 FETCH c_phone_fax INTO
2519 g_column_elements_table(get_column_hash_value('customer_site_fax_number')).column_content;
2520 CLOSE c_phone_fax;
2521
2522 END IF;
2523
2524 IF l_cur_wdd.ship_to_site_use_id IS NOT NULL AND l_cur_wdd.ship_to_site_use_id <> nvl(l_prev_wdd.ship_to_site_use_id, NULL_NUM) THEN
2525 OPEN c_location(l_cur_wdd.ship_to_site_use_id);
2526 FETCH c_location INTO
2527 g_column_elements_table(get_column_hash_value('ship_to_location')).column_content;
2528 CLOSE c_location;
2529 END IF;
2530
2531 IF l_cur_wdd.sold_to_contact_id IS NOT NULL AND l_cur_wdd.sold_to_contact_id <> nvl(l_prev_wdd.sold_to_contact_id, NULL_NUM) THEN
2532 OPEN c_contact(l_cur_wdd.sold_to_contact_id);
2533 FETCH c_contact INTO
2534 g_column_elements_table(get_column_hash_value('customer_contact_name')).column_content;
2535 CLOSE c_contact;
2536 END IF;
2537
2538 IF l_cur_wdd.ship_to_contact_id IS NOT NULL AND l_cur_wdd.ship_to_contact_id <> nvl(l_prev_wdd.ship_to_contact_id, NULL_NUM) THEN
2539 OPEN c_contact(l_cur_wdd.ship_to_contact_id);
2540 FETCH c_contact INTO
2541 g_column_elements_table(get_column_hash_value('ship_to_contact_name')).column_content;
2542 CLOSE c_contact;
2543 END IF;
2544
2545 IF l_cur_wdd.deliver_to_location_id IS NOT NULL AND l_cur_wdd.deliver_to_location_id <> nvl(l_prev_wdd.deliver_to_location_id, NULL_NUM) THEN
2546 l_progress := 'Use deliver to cursor to hash table';
2547 OPEN c_address(l_cur_wdd.deliver_to_location_id);
2548 FETCH c_address INTO
2549 g_column_elements_table(get_column_hash_value('deliver_to_address1')).column_content,
2550 g_column_elements_table(get_column_hash_value('deliver_to_address2')).column_content,
2551 g_column_elements_table(get_column_hash_value('deliver_to_address3')).column_content,
2552 g_column_elements_table(get_column_hash_value('deliver_to_address4')).column_content,
2553 g_column_elements_table(get_column_hash_value('deliver_to_city')).column_content,
2554 g_column_elements_table(get_column_hash_value('deliver_to_postal_code')).column_content,
2555 g_column_elements_table(get_column_hash_value('deliver_to_state')).column_content,
2556 g_column_elements_table(get_column_hash_value('deliver_to_county')).column_content,
2557 g_column_elements_table(get_column_hash_value('deliver_to_country')).column_content,
2558 g_column_elements_table(get_column_hash_value('deliver_to_province')).column_content,
2559 g_column_elements_table(get_column_hash_value('deliver_to_site')).column_content,
2560 g_column_elements_table(get_column_hash_value('deliver_to_loc_desc')).column_content;
2561 CLOSE c_address;
2562
2563 OPEN c_phone_fax(l_cur_wdd.deliver_to_location_id, 'PHONE');
2564 FETCH c_phone_fax INTO
2565 g_column_elements_table(get_column_hash_value('deliver_to_phone')).column_content;
2566 CLOSE c_phone_fax;
2567
2568 OPEN c_phone_fax(l_cur_wdd.deliver_to_location_id, 'FAX');
2569 FETCH c_phone_fax INTO
2570 g_column_elements_table(get_column_hash_value('deliver_to_fax')).column_content;
2571 CLOSE c_phone_fax;
2572
2573 END IF;
2574
2575 IF l_cur_wdd.deliver_to_site_use_id IS NOT NULL AND l_cur_wdd.deliver_to_site_use_id <> nvl(l_prev_wdd.deliver_to_site_use_id, NULL_NUM) THEN
2576 OPEN c_location(l_cur_wdd.deliver_to_site_use_id);
2577 FETCH c_location INTO
2578 g_column_elements_table(get_column_hash_value('deliver_to_location')).column_content;
2579 CLOSE c_location;
2580 END IF;
2581
2582 IF l_cur_wdd.deliver_to_contact_id IS NOT NULL AND l_cur_wdd.deliver_to_contact_id <> nvl(l_prev_wdd.deliver_to_contact_id, NULL_NUM) THEN
2583 OPEN c_contact(l_cur_wdd.deliver_to_contact_id);
2584 FETCH c_contact INTO
2585 g_column_elements_table(get_column_hash_value('deliver_to_contact_name')).column_content;
2586 CLOSE c_contact;
2587 END IF;
2588
2589 IF l_cur_wdd.intmed_ship_to_location_id IS NOT NULL AND l_cur_wdd.intmed_ship_to_location_id <> nvl(l_prev_wdd.intmed_ship_to_location_id, NULL_NUM) THEN
2590 l_progress := 'Use intmed cursor to hash table';
2591 OPEN c_address(l_cur_wdd.intmed_ship_to_location_id);
2592 FETCH c_address INTO
2593 g_column_elements_table(get_column_hash_value('interm_ship_to_address1')).column_content,
2594 g_column_elements_table(get_column_hash_value('interm_ship_to_address2')).column_content,
2595 g_column_elements_table(get_column_hash_value('interm_ship_to_address3')).column_content,
2596 g_column_elements_table(get_column_hash_value('interm_ship_to_address4')).column_content,
2597 g_column_elements_table(get_column_hash_value('interm_ship_to_city')).column_content,
2598 g_column_elements_table(get_column_hash_value('interm_ship_to_postal_code')).column_content,
2599 g_column_elements_table(get_column_hash_value('interm_ship_to_state')).column_content,
2600 g_column_elements_table(get_column_hash_value('interm_ship_to_county')).column_content,
2601 g_column_elements_table(get_column_hash_value('interm_ship_to_country')).column_content,
2602 g_column_elements_table(get_column_hash_value('interm_ship_to_province')).column_content,
2603 g_column_elements_table(get_column_hash_value('interm_ship_to_location')).column_content,
2604 g_column_elements_table(get_column_hash_value('interm_ship_to_loc_desc')).column_content;
2605 CLOSE c_address;
2606 END IF;
2607
2608
2609 IF l_cur_wdd.intmed_ship_to_contact_id IS NOT NULL AND l_cur_wdd.intmed_ship_to_contact_id <> nvl(l_prev_wdd.intmed_ship_to_contact_id, NULL_NUM) THEN
2610 OPEN c_contact(l_cur_wdd.intmed_ship_to_contact_id);
2611 FETCH c_contact INTO
2612 g_column_elements_table(get_column_hash_value('interm_ship_to_contact_name')).column_content;
2613 CLOSE c_contact;
2614 END IF;
2615
2616 IF l_cur_wdd.source_line_id IS NOT NULL AND l_cur_wdd.source_line_id <> nvl(l_prev_wdd.source_line_id, NULL_NUM) THEN
2617 OPEN c_so_line(l_cur_wdd.source_line_id);
2618 FETCH c_so_line INTO
2619 g_column_elements_table(get_column_hash_value('schd_shp_dt_ord_lines')).column_content,
2620 g_column_elements_table(get_column_hash_value('req_dt_ord_lines')).column_content,
2621 g_column_elements_table(get_column_hash_value('promise_dt_ord_lines')).column_content,
2622 g_column_elements_table(get_column_hash_value('shpmnt_priority_cd_ord_lines')).column_content,
2623 l_cur_wdd.oe_ship_method_code,
2624 g_column_elements_table(get_column_hash_value('freight_car_cd_ord_lines')).column_content,
2625 g_column_elements_table(get_column_hash_value('schd_arr_dt_ord_lines')).column_content,
2626 g_column_elements_table(get_column_hash_value('actual_shp_dt_ord_lines')).column_content,
2627 g_column_elements_table(get_column_hash_value('shppng_instructions_ord_lines')).column_content,
2628 g_column_elements_table(get_column_hash_value('pkg_instructions_dt_ord_lines')).column_content,
2629 g_column_elements_table(get_column_hash_value('att1_order_lines')).column_content,
2630 g_column_elements_table(get_column_hash_value('att2_order_lines')).column_content,
2631 g_column_elements_table(get_column_hash_value('att3_order_lines')).column_content,
2632 g_column_elements_table(get_column_hash_value('att4_order_lines')).column_content,
2633 g_column_elements_table(get_column_hash_value('att5_order_lines')).column_content,
2634 g_column_elements_table(get_column_hash_value('att6_order_lines')).column_content,
2635 g_column_elements_table(get_column_hash_value('att7_order_lines')).column_content,
2636 g_column_elements_table(get_column_hash_value('att8_order_lines')).column_content,
2637 g_column_elements_table(get_column_hash_value('att9_order_lines')).column_content,
2638 g_column_elements_table(get_column_hash_value('att10_order_lines')).column_content,
2639 g_column_elements_table(get_column_hash_value('att11_order_lines')).column_content,
2640 g_column_elements_table(get_column_hash_value('att12_order_lines')).column_content,
2641 g_column_elements_table(get_column_hash_value('att13_order_lines')).column_content,
2642 g_column_elements_table(get_column_hash_value('att14_order_lines')).column_content,
2643 g_column_elements_table(get_column_hash_value('att15_order_lines')).column_content,
2644 g_column_elements_table(get_column_hash_value('global_att1_order_lines')).column_content,
2645 g_column_elements_table(get_column_hash_value('global_att2_order_lines')).column_content,
2646 g_column_elements_table(get_column_hash_value('global_att3_order_lines')).column_content,
2647 g_column_elements_table(get_column_hash_value('global_att4_order_lines')).column_content,
2648 g_column_elements_table(get_column_hash_value('global_att5_order_lines')).column_content,
2649 g_column_elements_table(get_column_hash_value('global_att6_order_lines')).column_content,
2650 g_column_elements_table(get_column_hash_value('global_att7_order_lines')).column_content,
2651 g_column_elements_table(get_column_hash_value('global_att8_order_lines')).column_content,
2652 g_column_elements_table(get_column_hash_value('global_att9_order_lines')).column_content,
2653 g_column_elements_table(get_column_hash_value('global_att10_order_lines')).column_content,
2654 g_column_elements_table(get_column_hash_value('global_att11_order_lines')).column_content,
2655 g_column_elements_table(get_column_hash_value('global_att12_order_lines')).column_content,
2656 g_column_elements_table(get_column_hash_value('global_att13_order_lines')).column_content,
2657 g_column_elements_table(get_column_hash_value('global_att14_order_lines')).column_content,
2658 g_column_elements_table(get_column_hash_value('global_att15_order_lines')).column_content,
2659 g_column_elements_table(get_column_hash_value('global_att16_order_lines')).column_content,
2660 g_column_elements_table(get_column_hash_value('global_att17_order_lines')).column_content,
2661 g_column_elements_table(get_column_hash_value('global_att18_order_lines')).column_content,
2662 g_column_elements_table(get_column_hash_value('global_att19_order_lines')).column_content,
2663 g_column_elements_table(get_column_hash_value('global_att20_order_lines')).column_content,
2664 g_column_elements_table(get_column_hash_value('pricing_att1_order_lines')).column_content,
2665 g_column_elements_table(get_column_hash_value('pricing_att2_order_lines')).column_content,
2666 g_column_elements_table(get_column_hash_value('pricing_att3_order_lines')).column_content,
2667 g_column_elements_table(get_column_hash_value('pricing_att4_order_lines')).column_content,
2668 g_column_elements_table(get_column_hash_value('pricing_att5_order_lines')).column_content,
2669 g_column_elements_table(get_column_hash_value('pricing_att6_order_lines')).column_content,
2670 g_column_elements_table(get_column_hash_value('pricing_att7_order_lines')).column_content,
2671 g_column_elements_table(get_column_hash_value('pricing_att8_order_lines')).column_content,
2672 g_column_elements_table(get_column_hash_value('pricing_att9_order_lines')).column_content,
2673 g_column_elements_table(get_column_hash_value('pricing_att10_order_lines')).column_content,
2674 g_column_elements_table(get_column_hash_value('industry_att1_order_lines')).column_content,
2675 g_column_elements_table(get_column_hash_value('industry_att2_order_lines')).column_content,
2676 g_column_elements_table(get_column_hash_value('industry_att3_order_lines')).column_content,
2677 g_column_elements_table(get_column_hash_value('industry_att4_order_lines')).column_content,
2678 g_column_elements_table(get_column_hash_value('industry_att5_order_lines')).column_content,
2679 g_column_elements_table(get_column_hash_value('industry_att6_order_lines')).column_content,
2680 g_column_elements_table(get_column_hash_value('industry_att7_order_lines')).column_content,
2681 g_column_elements_table(get_column_hash_value('industry_att8_order_lines')).column_content,
2682 g_column_elements_table(get_column_hash_value('industry_att9_order_lines')).column_content,
2683 g_column_elements_table(get_column_hash_value('industry_att10_order_lines')).column_content,
2684 g_column_elements_table(get_column_hash_value('industry_att11_order_lines')).column_content,
2685 g_column_elements_table(get_column_hash_value('industry_att12_order_lines')).column_content,
2686 g_column_elements_table(get_column_hash_value('industry_att13_order_lines')).column_content,
2687 g_column_elements_table(get_column_hash_value('industry_att14_order_lines')).column_content,
2688 g_column_elements_table(get_column_hash_value('industry_att15_order_lines')).column_content,
2689 g_column_elements_table(get_column_hash_value('industry_att16_order_lines')).column_content,
2690 g_column_elements_table(get_column_hash_value('industry_att17_order_lines')).column_content,
2691 g_column_elements_table(get_column_hash_value('industry_att18_order_lines')).column_content,
2692 g_column_elements_table(get_column_hash_value('industry_att19_order_lines')).column_content,
2693 g_column_elements_table(get_column_hash_value('industry_att20_order_lines')).column_content,
2694 g_column_elements_table(get_column_hash_value('industry_att21_order_lines')).column_content,
2695 g_column_elements_table(get_column_hash_value('industry_att22_order_lines')).column_content,
2696 g_column_elements_table(get_column_hash_value('industry_att23_order_lines')).column_content,
2697 g_column_elements_table(get_column_hash_value('industry_att24_order_lines')).column_content,
2698 g_column_elements_table(get_column_hash_value('industry_att25_order_lines')).column_content,
2699 g_column_elements_table(get_column_hash_value('industry_att26_order_lines')).column_content,
2700 g_column_elements_table(get_column_hash_value('industry_att27_order_lines')).column_content,
2701 g_column_elements_table(get_column_hash_value('industry_att28_order_lines')).column_content,
2702 g_column_elements_table(get_column_hash_value('industry_att29_order_lines')).column_content,
2703 g_column_elements_table(get_column_hash_value('industry_att30_order_lines')).column_content,
2704 g_column_elements_table(get_column_hash_value('return_att1_order_lines')).column_content,
2705 g_column_elements_table(get_column_hash_value('return_att2_order_lines')).column_content,
2706 g_column_elements_table(get_column_hash_value('return_att3_order_lines')).column_content,
2707 g_column_elements_table(get_column_hash_value('return_att4_order_lines')).column_content,
2708 g_column_elements_table(get_column_hash_value('return_att5_order_lines')).column_content,
2709 g_column_elements_table(get_column_hash_value('return_att6_order_lines')).column_content,
2710 g_column_elements_table(get_column_hash_value('return_att7_order_lines')).column_content,
2711 g_column_elements_table(get_column_hash_value('return_att8_order_lines')).column_content,
2712 g_column_elements_table(get_column_hash_value('return_att9_order_lines')).column_content,
2713 g_column_elements_table(get_column_hash_value('return_att10_order_lines')).column_content,
2714 g_column_elements_table(get_column_hash_value('return_att11_order_lines')).column_content,
2715 g_column_elements_table(get_column_hash_value('return_att12_order_lines')).column_content,
2716 g_column_elements_table(get_column_hash_value('return_att13_order_lines')).column_content,
2717 g_column_elements_table(get_column_hash_value('return_att14_order_lines')).column_content,
2718 g_column_elements_table(get_column_hash_value('return_att15_order_lines')).column_content,
2719 g_column_elements_table(get_column_hash_value('tp_att1_order_lines')).column_content,
2720 g_column_elements_table(get_column_hash_value('tp_att2_order_lines')).column_content,
2721 g_column_elements_table(get_column_hash_value('tp_att3_order_lines')).column_content,
2722 g_column_elements_table(get_column_hash_value('tp_att4_order_lines')).column_content,
2723 g_column_elements_table(get_column_hash_value('tp_att5_order_lines')).column_content,
2724 g_column_elements_table(get_column_hash_value('tp_att6_order_lines')).column_content,
2725 g_column_elements_table(get_column_hash_value('tp_att7_order_lines')).column_content,
2726 g_column_elements_table(get_column_hash_value('tp_att8_order_lines')).column_content,
2727 g_column_elements_table(get_column_hash_value('tp_att9_order_lines')).column_content,
2728 g_column_elements_table(get_column_hash_value('tp_att10_order_lines')).column_content,
2729 g_column_elements_table(get_column_hash_value('tp_att11_order_lines')).column_content,
2730 g_column_elements_table(get_column_hash_value('tp_att12_order_lines')).column_content,
2731 g_column_elements_table(get_column_hash_value('tp_att13_order_lines')).column_content,
2732 g_column_elements_table(get_column_hash_value('tp_att14_order_lines')).column_content,
2733 g_column_elements_table(get_column_hash_value('tp_att15_order_lines')).column_content,
2734 g_column_elements_table(get_column_hash_value('ordered_item')).column_content;
2735 CLOSE c_so_line;
2736 END IF;
2737
2738 IF l_cur_wdd.oe_ship_method_code IS NOT NULL AND l_cur_wdd.oe_ship_method_code <> nvl(l_prev_wdd.oe_ship_method_code, NULL_VAR) THEN
2739 OPEN c_ship_method(l_cur_wdd.oe_ship_method_code);
2740 FETCH c_ship_method INTO
2741 g_column_elements_table(get_column_hash_value('shppng_mthd_cd_ord_lines')).column_content;
2742 CLOSE c_ship_method;
2743 END IF;
2744
2745 IF l_cur_wdd.source_header_id IS NOT NULL AND l_cur_wdd.source_header_id <> nvl(l_prev_wdd.source_header_id, NULL_NUM) THEN
2746 OPEN c_so_header(l_cur_wdd.source_header_id);
2747 FETCH c_so_header INTO
2748 g_column_elements_table(get_column_hash_value('att1_order_headers')).column_content,
2749 g_column_elements_table(get_column_hash_value('att2_order_headers')).column_content,
2750 g_column_elements_table(get_column_hash_value('att3_order_headers')).column_content,
2751 g_column_elements_table(get_column_hash_value('att4_order_headers')).column_content,
2752 g_column_elements_table(get_column_hash_value('att5_order_headers')).column_content,
2753 g_column_elements_table(get_column_hash_value('att6_order_headers')).column_content,
2754 g_column_elements_table(get_column_hash_value('att7_order_headers')).column_content,
2755 g_column_elements_table(get_column_hash_value('att8_order_headers')).column_content,
2756 g_column_elements_table(get_column_hash_value('att9_order_headers')).column_content,
2757 g_column_elements_table(get_column_hash_value('att10_order_headers')).column_content,
2758 g_column_elements_table(get_column_hash_value('att11_order_headers')).column_content,
2759 g_column_elements_table(get_column_hash_value('att12_order_headers')).column_content,
2760 g_column_elements_table(get_column_hash_value('att13_order_headers')).column_content,
2761 g_column_elements_table(get_column_hash_value('att14_order_headers')).column_content,
2762 g_column_elements_table(get_column_hash_value('att15_order_headers')).column_content,
2763 g_column_elements_table(get_column_hash_value('global_att1_order_headers')).column_content,
2764 g_column_elements_table(get_column_hash_value('global_att2_order_headers')).column_content,
2765 g_column_elements_table(get_column_hash_value('global_att3_order_headers')).column_content,
2766 g_column_elements_table(get_column_hash_value('global_att4_order_headers')).column_content,
2767 g_column_elements_table(get_column_hash_value('global_att5_order_headers')).column_content,
2768 g_column_elements_table(get_column_hash_value('global_att6_order_headers')).column_content,
2769 g_column_elements_table(get_column_hash_value('global_att7_order_headers')).column_content,
2770 g_column_elements_table(get_column_hash_value('global_att8_order_headers')).column_content,
2771 g_column_elements_table(get_column_hash_value('global_att9_order_headers')).column_content,
2772 g_column_elements_table(get_column_hash_value('global_att10_order_headers')).column_content,
2773 g_column_elements_table(get_column_hash_value('global_att11_order_headers')).column_content,
2774 g_column_elements_table(get_column_hash_value('global_att12_order_headers')).column_content,
2775 g_column_elements_table(get_column_hash_value('global_att13_order_headers')).column_content,
2776 g_column_elements_table(get_column_hash_value('global_att14_order_headers')).column_content,
2777 g_column_elements_table(get_column_hash_value('global_att15_order_headers')).column_content,
2778 g_column_elements_table(get_column_hash_value('global_att16_order_headers')).column_content,
2779 g_column_elements_table(get_column_hash_value('global_att17_order_headers')).column_content,
2780 g_column_elements_table(get_column_hash_value('global_att18_order_headers')).column_content,
2781 g_column_elements_table(get_column_hash_value('global_att19_order_headers')).column_content,
2782 g_column_elements_table(get_column_hash_value('global_att20_order_headers')).column_content,
2783 g_column_elements_table(get_column_hash_value('tp_att1_order_headers')).column_content,
2784 g_column_elements_table(get_column_hash_value('tp_att2_order_headers')).column_content,
2785 g_column_elements_table(get_column_hash_value('tp_att3_order_headers')).column_content,
2786 g_column_elements_table(get_column_hash_value('tp_att4_order_headers')).column_content,
2787 g_column_elements_table(get_column_hash_value('tp_att5_order_headers')).column_content,
2788 g_column_elements_table(get_column_hash_value('tp_att6_order_headers')).column_content,
2789 g_column_elements_table(get_column_hash_value('tp_att7_order_headers')).column_content,
2790 g_column_elements_table(get_column_hash_value('tp_att8_order_headers')).column_content,
2791 g_column_elements_table(get_column_hash_value('tp_att9_order_headers')).column_content,
2792 g_column_elements_table(get_column_hash_value('tp_att10_order_headers')).column_content,
2793 g_column_elements_table(get_column_hash_value('tp_att11_order_headers')).column_content,
2794 g_column_elements_table(get_column_hash_value('tp_att12_order_headers')).column_content,
2795 g_column_elements_table(get_column_hash_value('tp_att13_order_headers')).column_content,
2796 g_column_elements_table(get_column_hash_value('tp_att14_order_headers')).column_content,
2797 g_column_elements_table(get_column_hash_value('tp_att15_order_headers')).column_content,
2798 g_column_elements_table(get_column_hash_value('sales_channel_code')).column_content,
2799 g_column_elements_table(get_column_hash_value('shipping_instructions')).column_content,
2800 g_column_elements_table(get_column_hash_value('packing_instructions')).column_content;
2801
2802 CLOSE c_so_header;
2803 END IF;
2804
2805
2806 -- GTIN support
2807 INV_LABEL.IS_ITEM_GTIN_ENABLED(
2808 x_return_status => l_return_status
2809 , x_gtin_enabled => l_gtin_enabled
2810 , x_gtin => l_gtin
2811 , x_gtin_desc => l_gtin_desc
2812 , p_organization_id => l_cur_wdd.organization_id
2813 , p_inventory_item_id => l_cur_wdd.inventory_item_id
2814 , p_unit_of_measure => l_cur_wdd.uom
2815 , p_revision => l_cur_wdd.revision);
2816 g_column_elements_table(get_column_hash_value('gtin')).column_content := l_gtin;
2817 g_column_elements_table(get_column_hash_value('gtin_description')).column_content := l_gtin_desc;
2818
2819
2820 -- Finished retrieving all data
2821 -- Start writing into xml
2822
2823 l_content_rec_index := l_content_rec_index + 1; -- loop for total number of records..
2824 row_index_per_label := row_index_per_label + 1; -- counter for the number of records per label
2825
2826 IF (new_label) THEN
2827 -- New Piece of label
2828 l_label_index := l_label_index + 1;
2829 IF (l_content_rec_index = 1) THEN
2830 -- First label
2831 -- Use rules engine to get format
2832 l_use_rules_engine := 'Y';
2833 ELSE
2834 l_use_rules_engine := 'N';
2835 END IF;
2836
2837 INV_LABEL.GET_FORMAT_WITH_RULE
2838 ( p_document_id =>p_label_type_info.label_type_id,
2839 p_label_format_id =>p_label_type_info.manual_format_id,
2840 p_organization_id =>l_cur_wdd.organization_id,
2841 p_inventory_item_id =>l_cur_wdd.inventory_item_id,
2842 p_subinventory_code =>nvl(l_cur_wdd.from_subinventory,l_cur_wdd.to_subinventory),
2843 p_locator_id =>nvl(l_cur_wdd.from_locator_id,l_cur_wdd.to_locator_id),
2844 p_lpn_id =>l_cur_wdd.outer_lpn_id,
2845 p_lot_number =>l_cur_wdd.lot_number,
2846 p_revision =>l_cur_wdd.revision,
2847 p_customer_id =>l_cur_wdd.customer_id,
2848 p_customer_contact_id=>l_cur_wdd.sold_to_contact_id,
2849 p_freight_code =>g_column_elements_table(get_column_hash_value('freight_car_cd_ord_lines')).column_content,
2850 p_delivery_id =>l_cur_wdd.delivery_id,
2851 p_last_update_date =>sysdate,
2852 p_last_updated_by =>fnd_global.user_id,
2853 p_creation_date =>sysdate,
2854 p_created_by =>fnd_global.user_id,
2855 p_business_flow_code => p_label_type_info.business_flow_code,
2856 p_customer_item_id => l_cur_wdd.customer_item_id,
2857 p_sales_order_header_id => l_cur_wdd.source_header_id,
2858 p_sales_order_line_id => l_cur_wdd.source_line_id,
2859 p_use_rule_engine => 'Y',
2860 x_return_status =>l_return_status,
2861 x_label_format_id =>l_label_format_id,
2862 x_label_format =>l_label_format,
2863 x_label_request_id =>l_label_request_id);
2864
2865 IF l_return_status <> 'S' THEN
2866 IF (l_debug = 1) THEN
2867 trace(' Error in applying rules engine, setting as default');
2868 END IF;
2869
2870 IF l_content_rec_index = 1 THEN
2871 l_label_format := p_label_type_info.default_format_name;
2872 l_label_format_id := p_label_type_info.default_format_id;
2873 l_prev_format_id := l_label_format_id;
2874 ELSIF (new_label) THEN
2875 l_label_format_id := l_prev_format_id;
2876
2877 END IF;
2878 ELSE
2879 l_prev_format_id := l_label_format_id;
2880 END IF;
2881
2882 IF (l_debug = 1) THEN
2883 trace(' After rules engine Label Format : '||l_label_format || ' Label Format ID :' || l_label_format_id||' label request id'||l_label_request_id);
2884 trace(' Getting selected fields ');
2885 END IF;
2886 INV_LABEL.get_variables_for_format
2887 (
2888 x_variables => l_selected_fields
2889 , x_variables_count => l_selected_fields_count
2890 , p_format_id => l_label_format_id);
2891
2892 IF (l_selected_fields_count=0) OR (l_selected_fields.count =0 ) THEN
2893 IF (l_debug = 1) THEN
2894 trace('no fields defined for this format: ' || l_label_format_id || ',' || l_label_format);
2895 trace('Can not continue to print');
2896 END IF;
2897 EXIT;
2898 END IF;
2899
2900 build_format_fields_structure (l_label_format_id);
2901
2902 -- Getting No. of rows per label
2903 BEGIN
2904 SELECT min(a.count)
2905 INTO no_of_rows_per_label
2906 FROM (SELECT wlfv.label_field_id,
2907 count(*) count
2908 FROM wms_label_field_variables wlfv
2909 WHERE wlfv.label_format_id = l_label_format_id
2910 GROUP BY wlfv.label_field_id
2911 HAVING count(*) > 1) a;
2912
2913 IF (no_of_rows_per_label IS NULL) OR (no_of_rows_per_label=0) THEN
2914 no_of_rows_per_label := 1;
2915 END IF;
2916 -- Also, get max number of rows defined.
2917 -- It might be greater than the actual number of rows per label
2918 -- For example, the user setup as
2919 -- _ITEM1, _ITEM2 and _QTY1, _QTY2, _QTY3
2920 -- Then the number of rows per label is 2 and max_no_of_rows_defined is 3.
2921
2922 SELECT max(a.count)
2923 INTO max_no_of_rows_defined
2924 FROM (SELECT wlfv.label_field_id,
2925 count(*) count
2926 FROM wms_label_field_variables wlfv
2927 WHERE wlfv.label_format_id = l_label_format_id
2928 GROUP BY wlfv.label_field_id
2929 HAVING count(*) > 1) a;
2930
2931 IF (l_debug = 1) THEN
2932 trace(' Max number of rows defined = '|| max_no_of_rows_defined);
2933 END IF;
2934 IF (max_no_of_rows_defined IS NULL ) OR (max_no_of_rows_defined=0) THEN
2935 max_no_of_rows_defined := 0;
2936 END IF;
2937
2938 EXCEPTION
2939 WHEN no_data_found THEN
2940 IF (l_debug = 1) THEN
2941 trace(' Did not find defined rows, can not proceed ');
2942 END IF;
2943 EXIT;
2944 END;
2945
2946 IF (l_debug = 1) THEN
2947 trace(' Got no. of rows per label='|| no_of_rows_per_label);
2948 trace(' Found variable defined for this format, cont = ' || l_selected_fields_count);
2949 END IF;
2950
2951 -- Getting printer with Label Format ID
2952 IF p_label_type_info.manual_printer IS NULL THEN
2953 -- The p_label_type_info.manual_printer is the one passed from the manual page.
2954 -- As per the design, if a printer is passed from the manual page, then we use that printer irrespective.
2955 WSH_REPORT_PRINTERS_PVT.GET_PRINTER(
2956 p_concurrent_program_id=>p_label_type_info.label_type_id,
2957 p_user_id =>fnd_global.user_id,
2958 p_responsibility_id =>fnd_global.resp_id,
2959 p_application_id =>fnd_global.resp_appl_id,
2960 p_organization_id =>l_cur_wdd.organization_id,
2961 p_zone =>nvl(l_cur_wdd.from_subinventory,l_cur_wdd.to_subinventory),
2962 p_format_id =>l_label_format_id,
2963 x_printer =>l_printer,
2964 x_api_status =>l_api_status,
2965 x_error_message =>l_error_message);
2966 IF l_api_status <> 'S' THEN
2967 IF (l_debug = 1) THEN
2968 trace('Error in GET_PRINTER '||l_error_message);
2969 END IF;
2970 l_printer := p_label_type_info.default_printer;
2971 END IF;
2972 ELSE
2973 l_printer := p_label_type_info.manual_printer;
2974 END IF;
2975 IF (l_debug = 1) THEN
2976 trace('Got Printer '||l_printer);
2977 END IF;
2978
2979 --Writing <LABEL ...> tag
2980 l_shipping_content_data := l_shipping_content_data || LABEL_B;
2981
2982 IF (l_label_format IS NOT NULL) AND
2983 (l_label_format_id <> nvl(p_label_type_info.default_format_id, NULL_NUM)) THEN
2984 l_shipping_content_data := l_shipping_content_data || ' _FORMAT="' || l_label_format || '"';
2985 END IF;
2986
2987 IF (l_printer IS NOT NULL) THEN
2988 -- Commented the AND condition for Bug: 5402949
2989 -- AND (l_printer <> nvl(p_label_type_info.default_printer, NULL_VAR)) THEN
2990 l_shipping_content_data := l_shipping_content_data || ' _PRINTERNAME="'||l_printer||'"';
2991 END IF;
2992
2993 l_shipping_content_data := l_shipping_content_data || TAG_E;
2994
2995 new_label := false;
2996 END IF; -- IF (new_label)
2997
2998 /* Loop for each selected fields, find the columns and write into the XML_content*/
2999 OPEN c_fields_for_format (l_label_format_id);
3000
3001 l_variable_list := '';
3002 l_custom_sql_ret_status := FND_API.G_RET_STS_SUCCESS;
3003
3004 LOOP -- Loop for c_fields_for_format
3005 FETCH c_fields_for_format
3006 INTO l_column_name_in_format;
3007 EXIT WHEN c_fields_for_format%notfound;
3008
3009 ---------------------------------------------------------------------------------------------
3010 -- Project: 'Custom Labels' (A 11i10+ Project) |
3011 -- Author: Dinesh ([email protected]) |
3012 -- Change Description: |
3013 -- For the column name 'sql_stmt', if the variable name is not null implies that the field |
3014 -- is a Custom SQL. For this variable name, get the corresponding SQL statement using the |
3015 -- function get_sql_for_variable(). Handle the sql appropriately. |
3016 ---------------------------------------------------------------------------------------------
3017 l_count_custom_sql := 0; -- Added for Bug#4179391
3018 Loop -- Added for Bug#4179391
3019 EXIT WHEN l_count_custom_sql >= g_count_custom_sql; -- Added for Bug#4179391
3020 -- Added following IF clause for bug 4190764
3021 IF l_column_name_in_format = 'sql_stmt' THEN
3022 --l_variable_name := get_variable_name('sql_stmt', row_index_per_label-1, l_label_format_id); -- Commented the statment to replace row_index_per_label with l_count_custom_sql
3023 l_variable_name := get_variable_name('sql_stmt', l_count_custom_sql, l_label_format_id); -- Added for Bug#4179391
3024 IF l_variable_name IS NOT NULL THEN
3025 --l_sql_stmt := get_sql_for_variable('sql_stmt', row_index_per_label-1, l_label_format_id); -- Commented the statment to replace row_index_per_label with l_count_custom_sql
3026 l_sql_stmt := get_sql_for_variable('sql_stmt', l_count_custom_sql, l_label_format_id); -- Added for Bug#4179391
3027 IF (l_sql_stmt IS NOT NULL) THEN
3028 IF (l_debug = 1) THEN
3029 trace('Custom Labels Trace [INVLAP8B.pls]: ------------------------- REPORT BEGIN-------------------------------------');
3030 trace('Custom Labels Trace [INVLAP8B.pls]: FIELD_VARIABLE_NAME : ' || l_variable_name);
3031 trace('Custom Labels Trace [INVLAP8B.pls]: l_sql_stmt BEFORE REQUEST_ID Filter Concatenation: ' || l_sql_stmt);
3032 END IF;
3033 l_sql_stmt := l_sql_stmt || ' AND WLR.LABEL_REQUEST_ID = :REQUEST_ID';
3034 IF (l_debug = 1) THEN
3035 trace('Custom Labels Trace [INVLAP8B.pls]: l_sql_stmt AFTER REQUEST_ID Filter Concatenation: ' || l_sql_stmt);
3036 END IF;
3037 BEGIN
3038 IF (l_debug = 1) THEN
3039 trace('Custom Labels Trace [INVLAP8B.pls]: At Breadcrumb 1');
3040 trace('Custom Labels Trace [INVLAP8B.pls]: LABEL_REQUEST_ID : ' || l_label_request_id);
3041 END IF;
3042 OPEN c_sql_stmt FOR l_sql_stmt using l_label_request_id;
3043 LOOP
3044 FETCH c_sql_stmt INTO l_sql_stmt_result;
3045 EXIT WHEN c_sql_stmt%notfound OR c_sql_stmt%rowcount >=2;
3046 END LOOP;
3047
3048 IF (c_sql_stmt%rowcount=1 AND l_sql_stmt_result IS NULL) THEN
3049 x_return_status := FND_API.G_RET_STS_SUCCESS;
3050 l_custom_sql_ret_status := INV_LABEL.G_WARNING;
3051 fnd_message.set_name('WMS','WMS_CS_NULL_VALUE_RETURNED');
3052 fnd_msg_pub.ADD;
3053 -- Fix for bug: 4179593 Start
3054 --fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false, p_count => x_msg_count, p_data => l_custom_sql_ret_msg);
3055 l_custom_sql_ret_msg := fnd_msg_pub.get(p_msg_index => fnd_msg_pub.g_last, p_encoded => fnd_api.g_false);
3056 l_CustSqlWarnMsg := l_custom_sql_ret_msg;
3057 l_CustSqlWarnFlagSet := TRUE;
3058 -- Fix for bug: 4179593 End
3059 IF (l_debug = 1) THEN
3060 trace('Custom Labels Trace [INVLAP8B.pls]: At Breadcrumb 2');
3061 trace('Custom Labels Trace [INVLAP8B.pls]: l_sql_stmt_result is: ' || l_sql_stmt_result);
3062 trace('Custom Labels Trace [INVLAP8B.pls]: WARNING: NULL value returned by the custom SQL Query.');
3063 trace('Custom Labels Trace [INVLAP8B.pls]: l_custom_sql_ret_status is set to : ' || l_custom_sql_ret_status);
3064 END IF;
3065 ELSIF c_sql_stmt%rowcount=0 THEN
3066 IF (l_debug = 1) THEN
3067 trace('Custom Labels Trace [INVLAP8B.pls]: At Breadcrumb 3');
3068 trace('Custom Labels Trace [INVLAP8B.pls]: WARNING: No row returned by the Custom SQL query');
3069 END IF;
3070 x_return_status := FND_API.G_RET_STS_SUCCESS;
3071 l_custom_sql_ret_status := INV_LABEL.G_WARNING;
3072 fnd_message.set_name('WMS','WMS_CS_NO_DATA_FOUND');
3073 fnd_msg_pub.ADD;
3074 -- Fix for bug: 4179593 Start
3075 --fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false, p_count => x_msg_count, p_data => l_custom_sql_ret_msg);
3076 l_custom_sql_ret_msg := fnd_msg_pub.get(p_msg_index => fnd_msg_pub.g_last, p_encoded => fnd_api.g_false);
3077 l_CustSqlWarnMsg := l_custom_sql_ret_msg;
3078 l_CustSqlWarnFlagSet := TRUE;
3079 -- Fix for bug: 4179593 End
3080 ELSIF c_sql_stmt%rowcount>=2 THEN
3081 IF (l_debug = 1) THEN
3082 trace('Custom Labels Trace [INVLAP8B.pls]: At Breadcrumb 4');
3083 trace('Custom Labels Trace [INVLAP8B.pls]: ERROR: Multiple values returned by the Custom SQL query');
3084 END IF;
3085 x_return_status := FND_API.G_RET_STS_SUCCESS;
3086 l_custom_sql_ret_status := FND_API.G_RET_STS_ERROR;
3087 fnd_message.set_name('WMS','WMS_CS_MULTIPLE_VALUES_RETURN');
3088 fnd_msg_pub.ADD;
3089 -- Fix for bug: 4179593 Start
3090 --fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false, p_count => x_msg_count, p_data => l_custom_sql_ret_msg);
3091 l_custom_sql_ret_msg := fnd_msg_pub.get(p_msg_index => fnd_msg_pub.g_last, p_encoded => fnd_api.g_false);
3092 l_CustSqlErrMsg := l_custom_sql_ret_msg;
3093 l_CustSqlErrFlagSet := TRUE;
3094 -- Fix for bug: 4179593 End
3095 END IF;
3096 IF (c_sql_stmt%ISOPEN) THEN
3097 CLOSE c_sql_stmt;
3098 END IF;
3099 EXCEPTION
3100 WHEN OTHERS THEN
3101 IF (c_sql_stmt%ISOPEN) THEN
3102 CLOSE c_sql_stmt;
3103 END IF;
3104 IF (l_debug = 1) THEN
3105 trace('Custom Labels Trace [INVLAP8B.pls]: At Breadcrumb 5');
3106 trace('Custom Labels Trace [INVLAP8B.pls]: Unexpected Error has occured in GET_VARIABLES_DATA');
3107 END IF;
3108 x_return_status := FND_API.G_RET_STS_ERROR;
3109 fnd_message.set_name('WMS','WMS_CS_WRONG_SQL_CONSTRUCT');
3110 fnd_msg_pub.ADD;
3111 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false, p_count => x_msg_count, p_data => x_msg_data);
3112 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3113 END;
3114 IF (l_debug = 1) THEN
3115 trace('Custom Labels Trace [INVLAP8B.pls]: At Breadcrumb 6');
3116 trace('Custom Labels Trace [INVLAP8B.pls]: Before assigning it to l_shipping_content_data');
3117 END IF;
3118 l_shipping_content_data := l_shipping_content_data
3119 || variable_b
3120 || l_variable_name
3121 || '">'
3122 || l_sql_stmt_result
3123 || variable_e;
3124 l_sql_stmt_result := NULL;
3125 l_sql_stmt := NULL;
3126 IF (l_debug = 1) THEN
3127 trace('Custom Labels Trace [INVLAP8B.pls]: At Breadcrumb 7');
3128 trace('Custom Labels Trace [INVLAP8B.pls]: After assigning it to l_shipping_content_data');
3129 trace('Custom Labels Trace [INVLAP8B.pls]: --------------------------REPORT END-------------------------------------');
3130 END IF;
3131 END IF;
3132 END IF;
3133 -- Added following END IF for bug 4190764
3134 END IF;
3135 l_count_custom_sql := l_count_custom_sql + 1; -- Added for Bug#4179391
3136 END LOOP; -- Added for Bug#4179391
3137 ------------------------End of this change for Custom Labels project code--------------------
3138
3139
3140 l_variable_name := get_variable_name(l_column_name_in_format, row_index_per_label-1, l_label_format_id);
3141 IF l_variable_name IS NOT NULL THEN
3142 IF l_column_name_in_format <> 'sql_stmt' THEN
3143 l_variable_list := l_variable_list ||','||l_variable_name;
3144 l_shipping_content_data := l_shipping_content_data || VARIABLE_B ||
3145 l_variable_name || '">' || g_column_elements_table(get_column_hash_value(l_column_name_in_format)).column_content || VARIABLE_E;
3146 END IF;
3147 END IF;
3148 END LOOP; -- Loop for c_fields_for_format
3149
3150 CLOSE c_fields_for_format;
3151
3152 IF(l_debug = 1) THEN
3153 trace('Set value for variables '||l_variable_list);
3154 END IF;
3155
3156 /*IF (l_debug = 1) THEN
3157 trace(' l_shipping_content_data = '|| l_shipping_content_data);
3158 END IF;*/
3159
3160 -- When finished writing all rows in the label
3161 -- or next LPN is a new label
3162 -- Start a new label
3163
3164 IF (row_index_per_label = no_of_rows_per_label) OR
3165 (l_wdd_index = l_wdd_tb.count) OR
3166 ((l_wdd_index < l_wdd_tb.count) AND
3167 (nvl(l_cur_wdd.outer_lpn_id, NULL_NUM) <> nvl(l_wdd_tb(l_wdd_index+1).outer_lpn_id, NULL_NUM))) THEN
3168 IF (l_debug = 1) THEN
3169 trace('This record is the end of a label.');
3170 END IF;
3171
3172 -- Finished writing all rows in a label
3173 -- Find any extra fields that needs to written with NULL value
3174 -- (this is for the case where there are ITEM1, ITEM2, but QTY1, QTY2, QTY3
3175 -- QTY3 is a extra incorrect setup and it always has value of NULL)
3176 FOR i IN (row_index_per_label+1)..max_no_of_rows_defined LOOP
3177 FOR j IN 1..l_selected_fields.count LOOP
3178 IF j=1 OR
3179 l_selected_fields(j).column_name <> l_selected_fields(j-1).column_name THEN
3180 l_variable_name := get_variable_name(l_selected_fields(j).column_name,
3181 i-1, l_label_format_id);
3182 IF l_variable_name IS NOT NULL THEN
3183 trace(' Found extra row to pass NULL=> '|| l_variable_name);
3184 l_shipping_content_data := l_shipping_content_data || VARIABLE_B ||
3185 l_variable_name || '">' ||'' || VARIABLE_E;
3186 END IF;
3187 END IF;
3188 END LOOP;
3189 END LOOP;
3190 new_label := true;
3191 -- Finished creating one label, close with '</LABEL>', save into result table
3192 l_shipping_content_data := l_shipping_content_data || LABEL_E;
3193 trace('writing into table, l_index ='||l_label_index||', req_id='||l_label_request_id);
3194 x_variable_content(l_label_index).label_content := l_shipping_content_data;
3195 x_variable_content(l_label_index).label_request_id := l_label_request_id;
3196
3197 IF (l_CustSqlWarnFlagSet) THEN
3198 l_custom_sql_ret_status := INV_LABEL.G_WARNING;
3199 l_custom_sql_ret_msg := l_CustSqlWarnMsg;
3200 END IF;
3201
3202 IF (l_CustSqlErrFlagSet) THEN
3203 l_custom_sql_ret_status := FND_API.G_RET_STS_ERROR;
3204 l_custom_sql_ret_msg := l_CustSqlErrMsg;
3205 END IF;
3206 -- Fix for bug: 4179593 End
3207
3208 x_variable_content(l_label_index).label_status := l_custom_sql_ret_status;
3209 x_variable_content(l_label_index).error_message := l_custom_sql_ret_msg;
3210
3211
3212 l_custom_sql_ret_status := NULL;
3213 l_custom_sql_ret_msg := NULL;
3214
3215 l_shipping_content_data := '';
3216 row_index_per_label := 0;
3217 END IF;
3218
3219 END LOOP; -- l_wdd_tb Loop
3220
3221 l_label_index := l_label_index - 1;
3222 IF (row_index_per_label < no_of_rows_per_label) AND (new_label=false) THEN
3223 -- Last label is partial
3224 -- Loop for the rest of the rows that don't have value,
3225 -- we need to pass NULL.
3226 FOR i IN (row_index_per_label+1)..max_no_of_rows_defined LOOP
3227 FOR j IN 1..l_selected_fields.count LOOP
3228 IF j=1 OR
3229 l_selected_fields(j).column_name <> l_selected_fields(j-1).column_name THEN
3230 l_variable_name := get_variable_name(l_selected_fields(j).column_name,
3231 i-1, l_label_format_id);
3232 IF l_variable_name IS NOT NULL THEN
3233 --trace(' Found extra row to pass NULL=> '|| l_variable_name);
3234 l_shipping_content_data := l_shipping_content_data || VARIABLE_B ||
3235 l_variable_name || '">' ||'' || VARIABLE_E;
3236 END IF;
3237 END IF;
3238 END LOOP;
3239 END LOOP;
3240 l_shipping_content_data := l_shipping_content_data || LABEL_E;
3241 x_variable_content(l_label_index).label_content := l_shipping_content_data;
3242 x_variable_content(l_label_index).label_request_id := l_label_request_id;
3243 l_shipping_content_data := '';
3244 END IF;
3245 EXCEPTION
3246 WHEN others THEN
3247 IF(l_debug=1) THEN
3248 trace('Error in INV_LABEL_PVT8.get_variable_data');
3249 trace('Progress is '||l_progress);
3250 trace('ERROR CODE = ' || SQLCODE);
3251 trace('ERROR MESSAGE = ' || SQLERRM);
3252
3253
3254 END IF;
3255 END get_variable_data;
3256
3257
3258 /* Overloaded signature which x_variable_content is a long string
3259 rather than seperate records */
3260 PROCEDURE get_variable_data(
3261 x_variable_content OUT NOCOPY LONG
3262 , x_msg_count OUT NOCOPY NUMBER
3263 , x_msg_data OUT NOCOPY VARCHAR2
3264 , x_return_status OUT NOCOPY VARCHAR2
3265 , p_label_type_info IN INV_LABEL.label_type_rec
3266 , p_transaction_id IN NUMBER
3267 , p_input_param IN MTL_MATERIAL_TRANSACTIONS_TEMP%ROWTYPE
3268 , p_transaction_identifier IN NUMBER
3269 ) IS
3270 l_variable_data_tbl INV_LABEL.label_tbl_type;
3271 BEGIN
3272 get_variable_data(
3273 x_variable_content => l_variable_data_tbl
3274 , x_msg_count => x_msg_count
3275 , x_msg_data => x_msg_data
3276 , x_return_status => x_return_status
3277 , p_label_type_info => p_label_type_info
3278 , p_transaction_id => p_transaction_id
3279 , p_input_param => p_input_param
3280 , p_transaction_identifier=> p_transaction_identifier
3281 );
3282
3283 x_variable_content := '';
3284
3285 FOR i IN 1..l_variable_data_tbl.count() LOOP
3286 x_variable_content := x_variable_content || l_variable_data_tbl(i).label_content;
3287 END LOOP;
3288
3289
3290 END get_variable_data;
3291
3292 END INV_LABEL_PVT8;