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