[Home] [Help]
PACKAGE BODY: APPS.INV_LABEL_PVT5
Source
1 PACKAGE BODY INV_LABEL_PVT5 AS
2 /* $Header: INVLAP5B.pls 120.22.12010000.2 2008/07/29 13:40:51 ptkumar ship $ */
3
4 -- Bug 2795525 : This mask is used to mask all date fields.
5 G_DATE_FORMAT_MASK VARCHAR2(100) := INV_LABEL.G_DATE_FORMAT_MASK;
6
7 LABEL_B CONSTANT VARCHAR2(50) := '<label';
8 LABEL_E CONSTANT VARCHAR2(50) := '</label>'||fnd_global.local_chr(10);
9 VARIABLE_B CONSTANT VARCHAR2(50) := '<variable name= "';
10 VARIABLE_E CONSTANT VARCHAR2(50) := '</variable>'||fnd_global.local_chr(10);
11 TAG_E CONSTANT VARCHAR2(50) := '>'||fnd_global.local_chr(10);
12 l_debug number;
13
14 g_get_hash_for_insert NUMBER := 1;
15 g_get_hash_for_retrieve NUMBER := 0;
16 g_count_custom_sql NUMBER := 0; -- Added for Bug#4179391
17
18 ---------------------------------------------------------------------------------------------
19 -- Project: 'Custom Labels' (A 11i10+ Project) |
20 -- Author: Dinesh ([email protected]) |
21 -- Change Description: |
22 -- Included SQL_STMT to field_element_tp |
23 ---------------------------------------------------------------------------------------------
24
25 TYPE field_element_tp IS RECORD
26 (column_name_with_count VARCHAR2(60),
27 variable_name VARCHAR2(60),
28 sql_stmt VARCHAR2(4000));
29
30
31 TYPE field_elements_tab_tp IS TABLE OF field_element_tp
32 INDEX BY BINARY_INTEGER;
33
34 g_field_elements_table field_elements_tab_tp;
35
36
37
38 PROCEDURE trace(p_message IN VARCHAR2) iS
39 BEGIN
40 inv_label.trace(p_message, 'LABEL_LPN_SUM');
41 END trace;
42
43
44
45 FUNCTION get_field_hash_value (p_input_string VARCHAR2, p_get_hash_mode NUMBER)
46 RETURN NUMBER IS
47 l_return_hash_value NUMBER;
48 l_orig_hash_value NUMBER;
49 l_hash_base NUMBER := 2;
50 l_hash_size NUMBER := Power(2, 20);
51 BEGIN
52 l_orig_hash_value := dbms_utility.get_hash_value
53 (
54 name => p_input_string
55 ,base => l_hash_base
56 ,hash_size => l_hash_size
57 );
58
59 IF g_field_elements_table.exists(l_orig_hash_value) AND
60 g_field_elements_table(l_orig_hash_value).column_name_with_count = p_input_string THEN
61
62 l_return_hash_value := l_orig_hash_value;
63
64 ELSIF g_field_elements_table.exists(l_orig_hash_value) THEN
65 -- hash collision
66
67 LOOP
68 l_orig_hash_value := l_orig_hash_value + 1;
69
70 IF l_orig_hash_value > l_hash_size THEN
71 -- Don't need to check hash overflow here because the hash range
72 -- for sure is greater than the number of columns.
73 l_orig_hash_value := l_hash_base;
74 END IF;
75
76 IF g_field_elements_table.exists(l_orig_hash_value) AND
77 g_field_elements_table(l_orig_hash_value).column_name_with_count = p_input_string THEN
78
79 EXIT;
80 ELSIF NOT g_field_elements_table.exists(l_orig_hash_value) THEN
81
82 EXIT;
83 END IF;
84
85 END LOOP;
86
87 l_return_hash_value := l_orig_hash_value;
88
89 ELSE
90
91 l_return_hash_value := l_orig_hash_value;
92 END IF;
93
94 IF p_get_hash_mode = g_get_hash_for_insert THEN
95 g_field_elements_table(l_return_hash_value).column_name_with_count := p_input_string;
96 END IF;
97 RETURN l_return_hash_value;
98
99 END get_field_hash_value;
100
101 ---------------------------------------------------------------------------------------------
102 -- Project: 'Custom Labels' (A 11i10+ Project) |
103 -- Author: Dinesh ([email protected]) |
104 -- Change Description: |
105 -- Included SQL_STMT to c_label_field_var cursor |
106 ---------------------------------------------------------------------------------------------
107
108 PROCEDURE build_format_fields_structure(p_label_format_id NUMBER) IS
109
110 CURSOR c_label_field_var IS
111 SELECT wlf.column_name,
112 wlf.sql_stmt,
113 wlfv.field_variable_name
114 FROM wms_label_field_variables wlfv,
115 wms_label_fields_vl wlf
116 WHERE wlfv.label_format_id = p_label_format_id
117 AND wlfv.label_field_id = wlf.label_field_id
118 ORDER BY wlf.column_name, wlfv.field_variable_name;
119
120 l_label_field_var c_label_field_var%ROWTYPE;
121 l_column_count NUMBER := 1;
122 l_prev_column_name VARCHAR2(60) := '';
123
124 BEGIN
125
126 --Bug #3142232. +1 line.
127 --Clearing the PL/SQL table g_field_elements_table before building it new.
128 g_field_elements_table.DELETE(nvl(g_field_elements_table.first,0),nvl(g_field_elements_table.last,0));
129 OPEN c_label_field_var;
130 LOOP
131 FETCH c_label_field_var INTO l_label_field_var;
132 EXIT WHEN c_label_field_var%notfound;
133
134 IF l_prev_column_name IS NULL OR l_prev_column_name <> l_label_field_var.column_name THEN
135 l_prev_column_name := l_label_field_var.column_name;
136 l_column_count := 1;
137 ELSE
138 l_column_count := l_column_count + 1;
139 END IF;
140
141 -- build the hash table with column_name concatenate count as key
142 -- trace('*********** insert into hash table '|| l_label_field_var.column_name ||l_column_count||' ************ ' || l_label_field_var.field_variable_name);
143 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;
144
145 IF l_label_field_var.column_name = 'sql_stmt' THEN
146 g_count_custom_sql := g_count_custom_sql + 1; -- Added for Bug#4179391
147 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;
148 END IF;
149
150 END LOOP;
151
152
153 CLOSE c_label_field_var;
154
155 END build_format_fields_structure;
156
157
158
159 /****************************************************************************
160 * p_transaction_identifier :
161 ****************************************************************************/
162 PROCEDURE get_variable_data(
163 x_variable_content OUT NOCOPY INV_LABEL.label_tbl_type
164 , x_msg_count OUT NOCOPY NUMBER
165 , x_msg_data OUT NOCOPY VARCHAR2
166 , x_return_status OUT NOCOPY VARCHAR2
167 , p_label_type_info IN INV_LABEL.label_type_rec
168 , p_transaction_id IN NUMBER
169 , p_input_param IN MTL_MATERIAL_TRANSACTIONS_TEMP%ROWTYPE
170 , p_lpn_id IN NUMBER
171 , p_transaction_identifier IN NUMBER
172 ) IS
173
174 l_receipt_number varchar2(30);
175
176 -- Added for Bug 2748297
177 l_vendor_id NUMBER;
178 l_vendor_site_id NUMBER;
179
180 -- Added for UCC 128 J Bug #3067059
181 l_gtin_enabled BOOLEAN := FALSE;
182 l_gtin VARCHAR2(100);
183 l_gtin_desc VARCHAR2(240);
184
185 -- Added for patchset J enhancements
186 l_deliver_to_location_id NUMBER;
187 l_location_id NUMBER;
188
189 --Bug# 3739739
190 l_qty NUMBER;
191 l_uom MTL_MATERIAL_TRANSACTIONS_TEMP.TRANSACTION_UOM%TYPE := null;
192
193
194 -- Added vendor_id and vendor_site_id to the cursor for Bug 2748297
195 CURSOR c_rti_lpn IS
196 SELECT rti.lpn_id, rti.to_organization_id, pha.segment1 purchase_order,
197 rti.subinventory, rti.locator_id,
198 l_receipt_number receipt_number, pol.line_num po_line_number,
199 pll.quantity quantity_ordered, rti.vendor_item_num supplier_part_number,
200 pov.vendor_id vendor_id, pov.vendor_name supplier_name,
201 pvs.vendor_site_id vendor_site_id, pvs.vendor_site_code supplier_site,
202 ppf.full_name requestor, hrl1.location_code deliver_to_location,
203 hrl2.location_code location, pll.note_to_receiver note_to_receiver
204 FROM rcv_transactions_interface rti, po_headers_all pha,
205 -- MOAC : changed po_line_locations to po_line_locations_all
206 po_lines_all pol, rcv_shipment_headers rsh, po_line_locations_all pll,
207 po_vendors pov, hr_locations_all hrl1, hr_locations_all hrl2,
208 -- MOAC : changed po_vendor_sites to po_vendor_sites_all
209 po_vendor_sites_all pvs, per_people_f ppf
210 where rti.interface_transaction_id = p_transaction_id
211 AND rti.po_header_id = pha.po_header_id(+)
212 AND rsh.shipment_header_id(+) = rti.shipment_header_id
213 AND pol.po_line_id(+) = rti.po_line_id --Added outer join, bug 4918726
214 AND pol.po_header_id(+) = rti.po_header_id --Added outer join, bug 4918726
215 --AND pll.po_line_id(+) = pol.po_line_id -- bug 2372669
216 AND pll.line_location_id(+) = rti.po_line_location_id -- bug 2372669
217 AND pov.vendor_id(+) = rti.vendor_id
218 -- AND pvs.vendor_id(+) = rti.vendor_id -- Unesseccary line dherring 8/2/05
219 AND pvs.vendor_site_id(+) = rti.vendor_site_id
220 AND ppf.person_id(+) = rti.deliver_to_person_id
221 AND hrl1.location_id(+) = rti.deliver_to_location_id
222 AND hrl2.location_id(+) = rti.location_id;
223
224 -- Bug 2377796 : Added this cursor for Inspection.
225 -- Added vendor_id and vendor_site_id to the cursor for Bug 2748297
226 CURSOR c_rti_lpn_inspection IS
227 SELECT rti.transfer_lpn_id transfer_lpn_id, rti.to_organization_id to_oragnization_id,
228 pha.segment1 purchase_order , rti.subinventory, rti.locator_id,
229 l_receipt_number receipt_number, pol.line_num po_line_number, pll.quantity
230 quantity_ordered, rti.vendor_item_num supplier_part_number,
231 pov.vendor_id vendor_id, pov.vendor_name supplier_name,
232 pvs.vendor_site_id vendor_site_id,
233 pvs.vendor_site_code supplier_site, ppf.full_name requestor,
234 hrl1.location_code deliver_to_location, hrl2.location_code location,
235 pll.note_to_receiver note_to_receiver
236 FROM rcv_transactions_interface rti, po_headers_all pha,
237 -- MOAC : changed po_line_locations to po_line_locations_all
238 po_lines_all pol, rcv_shipment_headers rsh, po_line_locations_all pll,
239 po_vendors pov, hr_locations_all hrl1, hr_locations_all hrl2,
240 -- MOAC : changed po_vendor_sites to po_vendor_sites_all
241 po_vendor_sites_all pvs, per_people_f ppf
242 where rti.interface_transaction_id = p_transaction_id
243 AND rti.po_header_id = pha.po_header_id(+)
244 AND rsh.shipment_header_id(+) = rti.shipment_header_id
245 AND pol.po_line_id (+) = rti.po_line_id
246 AND pol.po_header_id (+) = rti.po_header_id
247 --AND pll.po_line_id(+) = pol.po_line_id -- bug 2372669
248 AND pll.line_location_id(+) = rti.po_line_location_id -- bug 2372669
249 AND pov.vendor_id(+) = rti.vendor_id
250 -- AND pvs.vendor_id(+) = rti.vendor_id -- Unesseccary line dherring 8/2/05
251 AND pvs.vendor_site_id(+) = rti.vendor_site_id
252 AND ppf.person_id(+) = rti.deliver_to_person_id
253 AND hrl1.location_id(+) = rti.deliver_to_location_id
254 AND hrl2.location_id(+) = rti.location_id;
255
256
257 -- Cursor for RCV flows based on NEW architecture of querying LPN data from
258 -- RCV transaction tables instead of Interface tables : J-DEV
259 -- Note: records in RT are filtered by transaction_type and business_flow_code
260 -- because it is possible for label-API to be called multiple times by RCV-TM
261 -- in the case of ROI, when multiple trx.types are present in a group
262 CURSOR c_rt_lpn IS
263 SELECT distinct all_lpn.lpn_id
264 , pha.segment1 purchase_order
265 , all_lpn.subinventory
266 , all_lpn.locator_id
267 , rsh.receipt_num
268 , pol.line_num po_line_number
269 , pll.quantity quantity_ordered
270 , rsl.vendor_item_num supplier_part_number
271 , pov.vendor_id vendor_id
272 , pvs.vendor_site_id vendor_site_id
273 , pov.vendor_name supplier_name
274 , pvs.vendor_site_code supplier_site
275 , ppf.full_name requestor
276 -- , hrl1.location_code deliver_to_location
277 -- , hrl2.location_code location
278 , pll.note_to_receiver note_to_receiver
279 , all_lpn.deliver_to_location_id
280 , all_lpn.location_id
281 -- Added for bug 3581021 by joabraha
282 , pol.item_id item_id
283 --
284 FROM(
285 -- LPN_ID
286 select lpn_id
287 , po_header_id, po_line_id
288 , subinventory, locator_id
289 , shipment_header_id, po_line_location_id
290 , vendor_id, vendor_site_id
291 , deliver_to_person_id, deliver_to_location_id
292 , location_id
293 from rcv_transactions rt
294 where rt.lpn_id is not null
295 and rt.group_id = p_transaction_id
296 AND ((rt.transaction_type IN ('ACCEPT', 'REJECT')
297 AND p_label_type_info.business_flow_code = 2)
298 OR (rt.transaction_type = 'DELIVER'
299 AND p_label_type_info.business_flow_code in (3,4))
300 OR (rt.transaction_type = 'RECEIVE'
301 --AND rt.routing_header_id <> 3 Modified for Bug: 4312020
302 AND p_label_type_info.business_flow_code = 1
303 )
304 )
305 UNION ALL
306 -- PARENT LPN of LPN_ID
307 select lpn.parent_lpn_id
308 , rt.po_header_id, rt.po_line_id
309 , rt.subinventory, rt.locator_id
310 , rt.shipment_header_id, rt.po_line_location_id
311 , rt.vendor_id, rt.vendor_site_id
312 , rt.deliver_to_person_id, rt.deliver_to_location_id deliver_to_location_id
313 , rt.location_id location_id
314 from wms_license_plate_numbers lpn,
315 rcv_transactions rt
316 where lpn.lpn_id = rt.lpn_id
317 and lpn.parent_lpn_id <> rt.lpn_id
318 and lpn.parent_lpn_id is not null
319 and rt.group_id = p_transaction_id
320 AND ((rt.transaction_type IN ('ACCEPT', 'REJECT')
321 AND p_label_type_info.business_flow_code = 2)
322 OR (rt.transaction_type = 'DELIVER'
323 AND p_label_type_info.business_flow_code in (3,4))
324 OR (rt.transaction_type = 'RECEIVE'
325 --AND rt.routing_header_id <> 3 Modified for Bug: 4312020
326 AND p_label_type_info.business_flow_code = 1
327 )
328 )
329 UNION ALL
330 -- OUTERMOSE LPN of LPN_ID, and different than the LPN and parent LPN
331 select lpn.outermost_lpn_id
332 , rt.po_header_id, rt.po_line_id
333 , rt.subinventory, rt.locator_id
334 , rt.shipment_header_id, rt.po_line_location_id
335 , rt.vendor_id, rt.vendor_site_id
336 , rt.deliver_to_person_id, rt.deliver_to_location_id deliver_to_location_id
337 , rt.location_id location_id
338 from wms_license_plate_numbers lpn, rcv_transactions rt
339 where lpn.lpn_id = rt.lpn_id
340 and lpn.outermost_lpn_id <> lpn.lpn_id
341 and lpn.outermost_lpn_id <> lpn.parent_lpn_id
342 and rt.group_id = p_transaction_id
343 AND ((rt.transaction_type IN ('ACCEPT', 'REJECT')
344 AND p_label_type_info.business_flow_code = 2)
345 OR (rt.transaction_type = 'DELIVER'
346 AND p_label_type_info.business_flow_code in (3,4))
347 OR (rt.transaction_type = 'RECEIVE'
348 --AND rt.routing_header_id <> 3 Modified for Bug: 4312020
349 AND p_label_type_info.business_flow_code = 1
350 )
351 )
352 UNION all
353 -- Transfer LPN (different than LPN)
354 select transfer_lpn_id lpn_id
355 , po_header_id, po_line_id
356 , subinventory, locator_id
357 , shipment_header_id, po_line_location_id
358 , vendor_id, vendor_site_id
359 , deliver_to_person_id, deliver_to_location_id
360 , location_id
361 from rcv_transactions rt
362 where nvl(transfer_lpn_id,-999) <> nvl(lpn_id,-999)
363 and group_id = p_transaction_id
364 AND ((rt.transaction_type IN ('ACCEPT', 'REJECT')
365 AND p_label_type_info.business_flow_code = 2)
366 OR (rt.transaction_type = 'DELIVER'
367 AND p_label_type_info.business_flow_code in (3,4))
368 OR (rt.transaction_type = 'RECEIVE'
369 --AND rt.routing_header_id <> 3 Modified for Bug: 4312020
370 AND p_label_type_info.business_flow_code = 1
371 )
372 )
373 UNION all
374 -- Parent LPN of Transfer LPN
375 select lpn.parent_lpn_id
376 , rt.po_header_id, rt.po_line_id
377 , rt.subinventory, rt.locator_id
378 , rt.shipment_header_id, rt.po_line_location_id
379 , rt.vendor_id, rt.vendor_site_id
380 , rt.deliver_to_person_id, rt.deliver_to_location_id deliver_to_location_id
381 , rt.location_id location_id
382 from wms_license_plate_numbers lpn, rcv_transactions rt
383 where lpn.lpn_id = rt.transfer_lpn_id
384 and rt.transfer_lpn_id <> rt.lpn_id
385 and lpn.parent_lpn_id is not null
386 and lpn.parent_lpn_id <> lpn.lpn_id
387 and rt.group_id = p_transaction_id
388 AND ((rt.transaction_type IN ('ACCEPT', 'REJECT')
389 AND p_label_type_info.business_flow_code = 2)
390 OR (rt.transaction_type = 'DELIVER'
391 AND p_label_type_info.business_flow_code in (3,4))
392 OR (rt.transaction_type = 'RECEIVE'
393 --AND rt.routing_header_id <> 3 Modified for Bug: 4312020
394 AND p_label_type_info.business_flow_code = 1
395 )
396 )
397 UNION ALL
398 -- Outermost LPN of Transfer LPN
399 select lpn.outermost_lpn_id
400 , rt.po_header_id, rt.po_line_id
401 , rt.subinventory, rt.locator_id
402 , rt.shipment_header_id, rt.po_line_location_id
403 , rt.vendor_id, rt.vendor_site_id
404 , rt.deliver_to_person_id, rt.deliver_to_location_id deliver_to_location_id
405 , rt.location_id location_id
406 from wms_license_plate_numbers lpn, rcv_transactions rt
407 where lpn.lpn_id = rt.transfer_lpn_id
408 and rt.transfer_lpn_id <> rt.lpn_id
409 and lpn.outermost_lpn_id <> lpn.lpn_id
410 and lpn.outermost_lpn_id <> lpn.parent_lpn_id
411 and rt.group_id = p_transaction_id
412 AND ((rt.transaction_type IN ('ACCEPT', 'REJECT')
413 AND p_label_type_info.business_flow_code = 2)
414 OR (rt.transaction_type = 'DELIVER'
415 AND p_label_type_info.business_flow_code in (3,4))
416 OR (rt.transaction_type = 'RECEIVE'
417 --AND rt.routing_header_id <> 3 Modified for Bug: 4312020
418 AND p_label_type_info.business_flow_code = 1
419 )
420 )
421 ) all_lpn
422 , po_headers_all pha
423 , po_lines_all pol
424 , rcv_shipment_headers rsh
425 , rcv_shipment_lines rsl
426 -- MOAC : changed po_line_locations to po_line_locations_all
427 , po_line_locations_all pll
428 , po_vendors pov
429 -- , hr_locations_all hrl1
430 -- , hr_locations_all hrl2
431 -- MOAC : changed po_vendor_sites to po_vendor_sites_all
432 , po_vendor_sites_all pvs
433 , per_people_f ppf
434 , wms_license_plate_numbers wlpn -- Bug 3836623
435 WHERE pha.po_header_id(+) = all_lpn.po_header_id
436 AND rsh.shipment_header_id(+) = all_lpn.shipment_header_id
437 AND rsh.shipment_header_id = rsl.shipment_header_id
438 /* Bug 5241400, Add where clause for rsl and appl_lpn location_id */
439 /* Bug 5336350, also need to consider case when po_line_location_id is null, Intransit Shipment or RMA txns */
440 AND ((rsl.po_line_location_id IS NULL and all_lpn.po_line_location_id IS NULL) OR
441 rsl.po_line_location_id = all_lpn.po_line_location_id)
442 AND pol.po_line_id (+) = all_lpn.po_line_id
443 AND pol.po_header_id (+) = all_lpn.po_header_id
444 AND pll.line_location_id(+) = all_lpn.po_line_location_id
445 AND pov.vendor_id(+) = all_lpn.vendor_id
446 -- AND pvs.vendor_id(+) = all_lpn.vendor_id -- Unesseccary line dherring 8/2/05
447 AND pvs.vendor_site_id(+) = all_lpn.vendor_site_id
448 AND ppf.person_id(+) = all_lpn.deliver_to_person_id
449 -- Bug 3836623, for receiving putaway, do not print if the
450 -- LPN is picked (11), which will be doing cross docking
451 -- label will be printed during cross docking business flow
452 AND wlpn.lpn_id = all_lpn.lpn_id
453 AND (p_label_type_info.business_flow_code <> 4 OR
454 (p_label_type_info.business_flow_code = 4 AND
455 wlpn.lpn_context <> 11))
456 -- AND hrl1.location_id(+) = all_lpn.deliver_to_location_id
457 -- AND hrl2.location_id(+) = all_lpn.location_id
458 ;
459 /* Patchset J - Create a new cursor to fetch the location_code
460 * for the given location_id and deliver_to_location_id
461 */
462 CURSOR c_hr_locations IS
463 Select
464 decode(l_deliver_to_location_id,null,null,hrl1.location_code)
465 deliver_to_location
466 , decode(l_location_id,null,null,hrl2.location_code) location
467 from hr_locations_all hrl1
468 , hr_locations_all hrl2
469 where hrl1.location_id = decode(l_deliver_to_location_id,null,hrl1.location_id,l_deliver_to_location_id)
470 AND hrl2.location_id = decode(l_location_id,null,hrl2.location_id,l_location_id)
471 and hrl1.location_id = hrl2.location_id;
472
473 CURSOR c_mmtt_lpn IS
474 SELECT mmtt.lpn_id,
475 mmtt.content_lpn_id,
476 mmtt.transfer_lpn_id,
477 mmtt.transfer_subinventory,
478 mmtt.transfer_to_location,
479 mmtt.transaction_type_id,
480 mmtt.transaction_action_id,
481 mmtt.transaction_uom --Bug# 3739739
482 -- Bug 2515486: Added transaction_type_id, transaction_action_id, inventory_item_id
483 FROM mtl_material_transactions_temp mmtt
484 WHERE mmtt.transaction_temp_id = p_transaction_id
485 AND rownum<2;
486
487 CURSOR c_mmtt_lpn_pick_load IS
488 -- Bug 4277718, pick load printing.
489 -- when pick a whole LPN and load the same LPN, transfer_lpn_id is NULL
490 -- So take the content_lpn_id
491 SELECT nvl(mmtt.transfer_lpn_id, mmtt.content_lpn_id), mmtt.organization_id, mmtt.inventory_item_id,
492 mtlt.lot_number, mmtt.revision,
493 abs(nvl(mtlt.transaction_quantity,
494 mmtt.transaction_quantity)) quantity,
495 mmtt.transaction_uom,
496 mmtt.transfer_subinventory, mmtt.transfer_to_location
497 , mmtt.subinventory_code /*from sub, to select printer*/
498 , abs(nvl(mtlt.secondary_quantity, mmtt.secondary_transaction_quantity)) secondary_quantity, -- invocnv changes
499 mmtt.secondary_uom_code -- invconv changes
500 FROM mtl_material_transactions_temp mmtt, mtl_transaction_lots_temp mtlt
501 WHERE mtlt.transaction_temp_id(+) = mmtt.transaction_temp_id
502 AND mmtt.transaction_temp_id = p_transaction_id;
503
504 CURSOR c_mmtt_cart_lpn IS
505 SELECT lpn_id, package_id, content_volume_uom_code, content_volume, gross_weight_uom_code,
506 gross_weight, inventory_item_id, parent_package_id, pack_level, parent_lpn_id,
507 header_id, packaging_mode
508 FROM wms_packaging_hist
509 WHERE lpn_id is not null
510 OR package_id is not null
511 START WITH parent_lpn_id = p_transaction_id
512 CONNECT BY PARENT_PACKAGE_ID = PRIOR PACKAGE_ID;
513
514 CURSOR c_mmtt_wip_pick_drop_lpn IS
515 SELECT transfer_lpn_id, organization_id, inventory_item_id,
516 lot_number, revision, abs(transaction_quantity), transaction_uom,
517 transfer_subinventory, transfer_to_location,
518 abs(secondary_transaction_quantity), secondary_uom_code -- invconv changes
519 FROM mtl_material_transactions_temp
520 WHERE transaction_temp_id = p_transaction_id;
521
522 CURSOR c_mmtt_pregen_lpn IS
523 SELECT lpn_id, subinventory_code, locator_id
524 FROM mtl_material_transactions_temp
525 WHERE transaction_temp_id = p_transaction_id;
526
527 -- Bug 3836623
528 -- To prevent printing duplicate labels for cross docking for serialized item
529 -- remove the joint with WDA
530 -- Obtain the Org/Sub from the LPN table because it should have the correct
531 -- value when label printing is called from cross docking
532 /*CURSOR c_wdd_lpn IS
533 SELECT wdd2.lpn_id, nvl(wdd2.organization_id, wdd1.organization_id)
534 , wdd1.subinventory
535 FROM wsh_delivery_details wdd1, wsh_delivery_details wdd2
536 , wsh_delivery_assignments_v wda
537 WHERE wdd2.delivery_detail_id = p_transaction_id
538 AND wdd1.delivery_detail_id(+) = wda.delivery_detail_id
539 AND wdd2.delivery_detail_id = wda.parent_delivery_detail_id;
540 */
541 CURSOR c_wdd_lpn IS
542 SELECT wdd.lpn_id, wlpn.organization_id, wlpn.subinventory_code
543 FROM wsh_delivery_details wdd, wms_license_plate_numbers wlpn
544 WHERE wdd.delivery_detail_id = p_transaction_id
545 AND wdd.lpn_id = wlpn.lpn_id;
546
547 CURSOR c_wnd_lpn IS
548 SELECT wdd2.lpn_id, wdd1.organization_id
549 FROM wsh_new_deliveries wnd, wsh_delivery_assignments_v wda
550 , wsh_delivery_details wdd1, wsh_delivery_details wdd2
551 WHERE wnd.delivery_id = p_transaction_id
552 AND wnd.delivery_id = wda.delivery_id
553 AND wdd1.delivery_detail_id = wda.delivery_detail_id
554 AND wdd2.delivery_detail_id = wda.parent_delivery_detail_id;
555
556 -- Bug 2825748 : WIP is passing a transaction_temp_id instead of
557 -- wip_lpn_completions,header_id for both LPN and non-LPN Completions.
558 -- Bug 4277718
559 -- for WIP completion, lpn_id is used rather than transfer_lpn_id
560 -- Changed to use c_mmtt_lpn
561 /*CURSOR c_wip_lpn IS
562 SELECT transfer_lpn_id
563 FROM mtl_material_transactions_temp mmtt
564 WHERE mmtt.transaction_temp_id = p_transaction_id;*/
565
566
567 -- For business flow code of 33, the MMTT, MTI or MOL id is passed
568 -- Depending on the txn identifier being passed,one of the
569 -- following 2 flow csrs or the generic mmtt crsr will be called
570
571 CURSOR c_flow_lpn_mol IS
572 SELECT lpn_id
573 FROM mtl_txn_request_lines
574 WHERE line_id=p_transaction_id;
575
576 CURSOR c_flow_lpn_mti IS
577 SELECT lpn_id
578 FROM mtl_transactions_interface
579 WHERE transaction_interface_id = p_transaction_id;
580
581 -- Cursor to retrieve all the LPNs (including parent and outermostLPN)
582 -- associated with a shipment_header for ASN business-flow. iSP requirements.
583 -- Note: RSH Header-level information is not queried in this cursor. Instead
584 -- it is queried just once below for ASN flow. :J-DEV
585 CURSOR c_asn_lpn IS
586 SELECT distinct
587 all_lpn.lpn_id
588 , pha.segment1 purchase_order
589 , all_lpn.subinventory_code
590 , all_lpn.locator_id
591 , nvl(pll.promised_date, pll.need_by_date) due_date
592 , all_lpn.packing_slip
593 , all_lpn.truck_num
594 , all_lpn.country_of_origin_code
595 , all_lpn.comments
596 , pol.line_num po_line_number
597 , pll.quantity quantity_ordered
598 , all_lpn.vendor_item_num supplier_part_number
599 , pov.vendor_id vendor_id
600 , pvs.vendor_site_id vendor_site_id
601 , pov.vendor_name supplier_name
602 , pvs.vendor_site_code supplier_site
603 , ppf.full_name requestor
604 , hrl1.location_code deliver_to_location
605 , hrl2.location_code location
606 , pll.note_to_receiver note_to_receiver
607 FROM(
608 select lpn.lpn_id
609 , rsl.po_header_id, rsl.po_line_id
610 , lpn.subinventory_code, lpn.locator_id
611 , rsh.shipment_header_id, rsl.po_line_location_id
612 , rsh.vendor_id, rsh.vendor_site_id
613 , rsl.deliver_to_person_id, rsl.deliver_to_location_id
614 , '' location_id
615 , rsh.packing_slip
616 , rsl.truck_num
617 , rsl.COUNTRY_OF_ORIGIN_CODE
618 , rsl.comments
619 , rsl.vendor_item_num
620 from wms_license_plate_numbers lpn,
621 rcv_shipment_headers rsh,
622 rcv_shipment_lines rsl
623 where lpn.source_name = rsh.shipment_num
624 AND lpn.lpn_context = 7
625 AND rsl.shipment_header_id = rsh.shipment_header_id
626 and rsh.shipment_header_id = p_transaction_id
627 and rsl.asn_lpn_id = lpn.lpn_id
628 AND rsh.asn_type = 'ASN'
629 UNION
630 select lpn.parent_lpn_id
631 , rsl.po_header_id, rsl.po_line_id
632 , lpn.subinventory_code, lpn.locator_id
633 , rsh.shipment_header_id, rsl.po_line_location_id
634 , rsh.vendor_id, rsh.vendor_site_id
635 , rsl.deliver_to_person_id, rsl.deliver_to_location_id
636 , '' location_id
637 , rsh.packing_slip
638 , rsl.truck_num
639 , rsl.COUNTRY_OF_ORIGIN_CODE
640 , rsl.comments
641 , rsl.vendor_item_num
642 from wms_license_plate_numbers lpn,
643 rcv_shipment_headers rsh,
644 rcv_shipment_lines rsl
645 where lpn.source_name = rsh.shipment_num
646 AND lpn.lpn_context = 7
647 AND rsl.shipment_header_id = rsh.shipment_header_id
648 and rsl.asn_lpn_id = lpn.lpn_id
649 and rsh.shipment_header_id = p_transaction_id
650 AND rsh.asn_type = 'ASN'
651 UNION
652 select lpn.outermost_lpn_id
653 , rsl.po_header_id, rsl.po_line_id
654 , lpn.subinventory_code, lpn.locator_id
655 , rsh.shipment_header_id, rsl.po_line_location_id
656 , rsh.vendor_id, rsh.vendor_site_id
657 , rsl.deliver_to_person_id, rsl.deliver_to_location_id
658 , '' location_id
659 , rsh.packing_slip
660 , rsl.truck_num
661 , rsl.COUNTRY_OF_ORIGIN_CODE
662 , rsl.comments
663 , rsl.vendor_item_num
664 from wms_license_plate_numbers lpn,
665 rcv_shipment_headers rsh,
666 rcv_shipment_lines rsl
667 where lpn.source_name = rsh.shipment_num
668 AND lpn.lpn_context = 7
669 AND rsl.shipment_header_id = rsh.shipment_header_id
670 and rsh.shipment_header_id = p_transaction_id
671 and rsl.asn_lpn_id = lpn.lpn_id
672 AND rsh.asn_type = 'ASN'
673 ) all_lpn
674 , po_headers_all pha
675 , po_lines_all pol
676 , rcv_shipment_headers rsh
677 -- MOAC : changed po_line_locations to po_line_locations_all
678 , po_line_locations_all pll
679 , po_vendors pov
680 , hr_locations_all hrl1
681 , hr_locations_all hrl2
682 -- MOAC : changed po_vendor_sites to po_vendor_sites_all
683 , po_vendor_sites_all pvs
684 , per_people_f ppf
685 WHERE pha.po_header_id(+) = all_lpn.po_header_id
686 AND rsh.shipment_header_id(+) = all_lpn.shipment_header_id
687 AND pol.po_line_id (+) = all_lpn.po_line_id
688 AND pol.po_header_id (+) = all_lpn.po_header_id
689 AND pll.line_location_id(+) = all_lpn.po_line_location_id
690 AND pov.vendor_id(+) = all_lpn.vendor_id
691 -- AND pvs.vendor_id(+) = all_lpn.vendor_id -- Unesseccary line dherring 8/2/05
692 AND pvs.vendor_site_id(+) = all_lpn.vendor_site_id
693 AND ppf.person_id(+) = all_lpn.deliver_to_person_id
694 AND hrl1.location_id(+) = all_lpn.deliver_to_location_id
695 AND hrl2.location_id(+) = all_lpn.location_id
696 AND all_lpn.lpn_id = nvl(p_lpn_id, all_lpn.lpn_id);
697
698 p_organization_id NUMBER := null;
699 p_inventory_item_id NUMBER := null;
700 p_lot_number MTL_LOT_NUMBERS.LOT_NUMBER%TYPE :=null;
701 p_revision MTL_MATERIAL_TRANSACTIONS_TEMP.REVISION%TYPE := null;
702 p_qty NUMBER := null;
703 p_uom MTL_MATERIAL_TRANSACTIONS_TEMP.TRANSACTION_UOM%TYPE := null;
704 p_cost_group_id NUMBER := null;
705
706 --Fix for 4891916
707 l_lot_number mtl_lot_numbers.lot_number%TYPE := NULL;
708 l_revision mtl_material_transactions_temp.revision%TYPE := NULL;
709 -- End of fix for 4891916
710
711 l_subinventory_code VARCHAR2(10) := null;
712 l_locator_id NUMBER :=null;
713 l_locator VARCHAR2(204):=null;
714 l_header_id NUMBER := NULL;
715 l_packaging_mode NUMBER := NULL;
716 l_lpn_id NUMBER := NULL;
717 l_package_id NUMBER := NULL;
718 l_content_volume_uom_code VARCHAR2(3);
719 l_content_volume NUMBER;
720 l_gross_weight_uom_code VARCHAR2(3);
721 l_gross_weight NUMBER;
722 l_inventory_item_id NUMBER;
723 l_parent_package_id NUMBER;
724 l_pack_level NUMBER;
725 l_parent_lpn_id NUMBER;
726 l_outermost_lpn_id NUMBER;
727 cartonization_flag NUMBER := 0;
728
729 -- invconv changes start
730 l_secondary_quantity NUMBER;
731 l_secondary_uom VARCHAR2(3) := NULL;
732 -- invconv changes end
733
734
735 CURSOR c_lpn_attributes (p_org_id NUMBER, p_lpn_id NUMBER)IS
736 SELECT lpn.LICENSE_PLATE_NUMBER lpn
737 , plpn.lpn_id parent_lpn_id
738 , plpn.license_plate_number parent_lpn
739 , olpn.license_plate_number outermost_lpn
740 , msik.INVENTORY_ITEM_ID container_item_id
741 , msik.concatenated_segments container_item
742 , nvl(lpn.CONTENT_VOLUME, l_content_volume) volume
743 , nvl(lpn.CONTENT_VOLUME_UOM_CODE, l_content_volume_uom_code) volume_uom
744 , nvl(lpn.GROSS_WEIGHT, l_gross_weight) gross_weight
745 , nvl(lpn.GROSS_WEIGHT_UOM_CODE, l_gross_weight_uom_code) gross_weight_uom
746 , nvl(lpn.TARE_WEIGHT, msik.unit_weight) tare_weight
747 , nvl(lpn.TARE_WEIGHT_UOM_CODE, msik.weight_uom_code) tare_weight_uom
748 , lpn.attribute_category lpn_attribute_category
749 , lpn.attribute1 lpn_attribute1
750 , lpn.attribute2 lpn_attribute2
751 , lpn.attribute3 lpn_attribute3
752 , lpn.attribute4 lpn_attribute4
753 , lpn.attribute5 lpn_attribute5
754 , lpn.attribute6 lpn_attribute6
755 , lpn.attribute7 lpn_attribute7
756 , lpn.attribute8 lpn_attribute8
757 , lpn.attribute9 lpn_attribute9
758 , lpn.attribute10 lpn_attribute10
759 , lpn.attribute11 lpn_attribute11
760 , lpn.attribute12 lpn_attribute12
761 , lpn.attribute13 lpn_attribute13
762 , lpn.attribute14 lpn_attribute14
763 , lpn.attribute15 lpn_attribute15
764 , nvl(wph.parent_package_id, l_parent_package_id) parent_package
765 , nvl(wph.pack_level, l_pack_level) pack_level
766 FROM WMS_LICENSE_PLATE_NUMBERS lpn
767 , WMS_PACKAGING_HIST wph
768 , WMS_LICENSE_PLATE_NUMBERS plpn
769 , WMS_LICENSE_PLATE_NUMBERS olpn
770 , MTL_SYSTEM_ITEMS_KFV msik
771 /*Commented for bug# 6334460 start
772 , DUAL d
773 WHERE d.dummy = 'X'
774 AND lpn.license_plate_number (+) <> NVL('@@@',d.dummy)
775 Commented for bug# 6334460 end */
776 WHERE lpn.lpn_id (+) = p_lpn_id
777 AND wph.lpn_id (+) = lpn.lpn_id
778 AND plpn.lpn_id (+) = NVL(lpn.parent_lpn_id, l_parent_lpn_id)
779 AND olpn.lpn_id (+) = NVL(lpn.outermost_lpn_id, l_outermost_lpn_id)
780 AND msik.organization_id (+) = p_org_id
781 AND msik.inventory_item_id (+) = NVL(lpn.inventory_item_id, l_inventory_item_id);
782
783 CURSOR c_item_attributes (p_org_id NUMBER, p_item_id NUMBER, p_lot_number VARCHAR2) IS
784 SELECT mp.organization_code organization
785 , msik.concatenated_segments item
786 , msik.description item_description
787 , msik.attribute_category item_attribute_category
788 , msik.attribute1 item_attribute1
789 , msik.attribute2 item_attribute2
790 , msik.attribute3 item_attribute3
791 , msik.attribute4 item_attribute4
792 , msik.attribute5 item_attribute5
793 , msik.attribute6 item_attribute6
794 , msik.attribute7 item_attribute7
795 , msik.attribute8 item_attribute8
796 , msik.attribute9 item_attribute9
797 , msik.attribute10 item_attribute10
798 , msik.attribute11 item_attribute11
799 , msik.attribute12 item_attribute12
800 , msik.attribute13 item_attribute13
801 , msik.attribute14 item_attribute14
802 , msik.attribute15 item_attribute15
803 , to_char(mln.expiration_date, G_DATE_FORMAT_MASK) lot_expiration_date -- Added for Bug 2795525,
804 , poh.hazard_class item_hazard_class
805 , mln.lot_attribute_category lot_attribute_category
806 , mln.c_attribute1 lot_c_attribute1
807 , mln.c_attribute2 lot_c_attribute2
808 , mln.c_attribute3 lot_c_attribute3
809 , mln.c_attribute4 lot_c_attribute4
810 , mln.c_attribute5 lot_c_attribute5
811 , mln.c_attribute6 lot_c_attribute6
812 , mln.c_attribute7 lot_c_attribute7
813 , mln.c_attribute8 lot_c_attribute8
814 , mln.c_attribute9 lot_c_attribute9
815 , mln.c_attribute10 lot_c_attribute10
816 , mln.c_attribute11 lot_c_attribute11
817 , mln.c_attribute12 lot_c_attribute12
818 , mln.c_attribute13 lot_c_attribute13
819 , mln.c_attribute14 lot_c_attribute14
820 , mln.c_attribute15 lot_c_attribute15
821 , mln.c_attribute16 lot_c_attribute16
822 , mln.c_attribute17 lot_c_attribute17
823 , mln.c_attribute18 lot_c_attribute18
824 , mln.c_attribute19 lot_c_attribute19
825 , mln.c_attribute20 lot_c_attribute20
826 , to_char(mln.D_ATTRIBUTE1, G_DATE_FORMAT_MASK) lot_d_attribute1 -- Added for Bug 2795525,
827 , to_char(mln.D_ATTRIBUTE2, G_DATE_FORMAT_MASK) lot_d_attribute2 -- Added for Bug 2795525,
828 , to_char(mln.D_ATTRIBUTE3, G_DATE_FORMAT_MASK) lot_d_attribute3 -- Added for Bug 2795525,
829 , to_char(mln.D_ATTRIBUTE4, G_DATE_FORMAT_MASK) lot_d_attribute4 -- Added for Bug 2795525,
830 , to_char(mln.D_ATTRIBUTE5, G_DATE_FORMAT_MASK) lot_d_attribute5 -- Added for Bug 2795525,
831 , to_char(mln.D_ATTRIBUTE6, G_DATE_FORMAT_MASK) lot_d_attribute6 -- Added for Bug 2795525,
832 , to_char(mln.D_ATTRIBUTE7, G_DATE_FORMAT_MASK) lot_d_attribute7 -- Added for Bug 2795525,
833 , to_char(mln.D_ATTRIBUTE8, G_DATE_FORMAT_MASK) lot_d_attribute8 -- Added for Bug 2795525,
834 , to_char(mln.D_ATTRIBUTE9, G_DATE_FORMAT_MASK) lot_d_attribute9 -- Added for Bug 2795525,
835 , to_char(mln.D_ATTRIBUTE10, G_DATE_FORMAT_MASK) lot_d_attribute10 -- Added for Bug 2795525,
836 , mln.n_attribute1 lot_n_attribute1
837 , mln.n_attribute2 lot_n_attribute2
838 , mln.n_attribute3 lot_n_attribute3
839 , mln.n_attribute4 lot_n_attribute4
840 , mln.n_attribute5 lot_n_attribute5
841 , mln.n_attribute6 lot_n_attribute6
842 , mln.n_attribute7 lot_n_attribute7
843 , mln.n_attribute8 lot_n_attribute8
844 , mln.n_attribute9 lot_n_attribute9
845 , mln.n_attribute10 lot_n_attribute10
846 , mln.TERRITORY_CODE lot_country_of_origin
847 , mln.grade_code lot_grade_code
848 , to_char(mln.ORIGINATION_DATE, G_DATE_FORMAT_MASK) lot_origination_date -- Added for Bug 2795525,
849 , mln.DATE_CODE lot_date_code
850 , to_char(mln.CHANGE_DATE, G_DATE_FORMAT_MASK) lot_change_date -- Added for Bug 2795525,
851 , mln.AGE lot_age
852 , to_char(mln.RETEST_DATE, G_DATE_FORMAT_MASK) lot_retest_date -- Added for Bug 2795525,
853 , to_char(mln.MATURITY_DATE, G_DATE_FORMAT_MASK) lot_maturity_date -- Added for Bug 2795525,
854 , mln.ITEM_SIZE lot_item_size
855 , mln.COLOR lot_color
856 , mln.VOLUME lot_volume
857 , mln.VOLUME_UOM lot_volume_uom
858 , mln.PLACE_OF_ORIGIN lot_place_of_origin
859 , to_char(mln.BEST_BY_DATE, G_DATE_FORMAT_MASK) lot_best_by_date -- Added for Bug 2795525,
860 , mln.length lot_length
861 , mln.length_uom lot_length_uom
862 , mln.recycled_content lot_recycled_cont
863 , mln.thickness lot_thickness
864 , mln.thickness_uom lot_thickness_uom
865 , mln.width lot_width
866 , mln.width_uom lot_width_uom
867 , mln.curl_wrinkle_fold lot_curl
868 , mln.vendor_name lot_vendor
869 , mmsv.status_code lot_number_status
870 , mln.parent_lot_number parent_lot_number -- invconv changes start
871 , mln.expiration_action_date expiration_action_date
872 , mln.origination_type origination_type
873 , mln.hold_date hold_date
874 , mln.expiration_action_code expiration_action_code
875 , mln.supplier_lot_number supplier_lot_number -- invconv changes end
876 FROM mtl_parameters mp
877 ,mtl_system_items_kfv msik
878 , mtl_lot_numbers mln
879 , po_hazard_classes poh
880 , mtl_material_statuses_vl mmsv
881 WHERE msik.inventory_item_id = p_item_id
882 AND msik.organization_id = p_org_id
883 AND mp.organization_id = msik.organization_id
884 AND mln.organization_id (+) = msik.organization_id
885 AND mln.inventory_item_id (+) = msik.inventory_item_id
886 AND poh.hazard_class_id (+) = msik.hazard_class_id
887 AND mln.lot_number (+) = p_lot_number
888 AND mmsv.status_id (+) = mln.status_id;
889
890 -- Added an extra parameter p_item_id NUMBER for Bug 3581021 by joabraha
891 -- Bug 4137707, performance of printing at cartonization
892 -- Break the original cursor into seperate cursor
893 -- for cartonization flow c_lpn_item_content_cart
894 -- and non-cartonization flow c_lpn_item_content
895 -- Since this is for non-cartonization flow
896 -- Removed the following information
897 -- 1. Removed input parameter p_package_id
898 -- 2. Removed the reference to l_packaging_mode because it is only relavent for cartonization
899 -- 3. Removed the union all of wms_packaging_hist part
900 CURSOR c_lpn_item_content(p_lpn_id NUMBER, p_item_id NUMBER) IS
901 SELECT
902 nvl(p_organization_id, plpn.organization_id) organization_id
903 , nvl(p_inventory_item_id, wlc.inventory_item_id) inventory_item_id
904 , nvl(p_revision, wlc.revision) revision
905 , nvl(p_lot_number,wlc.lot_number) lot_number
906 , sum(nvl(p_qty, wlc.quantity)) quantity
907 , nvl(p_uom, wlc.uom_code) uom
908 , nvl(p_cost_group_id, wlc.cost_group_id) cost_group_id
909 , ccg.cost_group cost_group
910 , milkfv.subinventory_code subinventory_code
911 , milkfv.inventory_location_id locator_id
912 , INV_PROJECT.GET_LOCSEGS(milkfv.inventory_location_id,milkfv.organization_id) locator
913 , sum(nvl(l_secondary_quantity,wlc.secondary_quantity)) secondary_quantity -- invconv fabdi
914 , wlc.secondary_uom_code secondary_uom -- invconv fabdi
915 FROM wms_lpn_contents wlc
916 , wms_license_plate_numbers plpn
917 , cst_cost_groups ccg
918 , mtl_item_locations milkfv
919 WHERE plpn.lpn_id in (select lpn_id from wms_license_plate_numbers
920 where 1=1
921 -- Bug 4137707
922 --start with lpn_id in (select nvl(p_lpn_id, -99) from dual
923 --union all
924 --select lpn_id from wms_packaging_hist
925 --where pack_level = 0
926 --and lpn_id IS not null
927 --start with parent_package_id = p_package_id
928 --connect by PARENT_PACKAGE_ID = PRIOR PACKAGE_ID)
929 start with lpn_id = p_lpn_id
930 connect by parent_lpn_id = prior lpn_id)
931 AND wlc.parent_lpn_id(+) = plpn.lpn_id
932 AND milkfv.organization_id (+) = NVL(p_organization_id, plpn.organization_id)
933 -- Added the new mode (WMS_CARTNZN_WRAP.mfg_pr_pkg_mode) for fix to Bug 2764074.
934 -- Bug 4137707
935 --AND milkfv.subinventory_code(+) =
936 -- DECODE(l_packaging_mode, WMS_CARTNZN_WRAP.PR_PKG_MODE, NULL, WMS_CARTNZN_WRAP.mfg_pr_pkg_mode, NULL,
937 -- nvl(l_subinventory_code,plpn.subinventory_code))
938 --AND milkfv.inventory_location_id(+) =
939 -- DECODE(l_packaging_mode, WMS_CARTNZN_WRAP.PR_PKG_MODE, NULL, WMS_CARTNZN_WRAP.mfg_pr_pkg_mode, NULL,
940 -- nvl(l_locator_id, plpn.locator_id))
941 AND milkfv.subinventory_code(+) = nvl(l_subinventory_code,plpn.subinventory_code)
942 AND milkfv.inventory_location_id(+) = nvl(l_locator_id, plpn.locator_id)
943 AND ccg.cost_group_id (+) = nvl(p_cost_group_id, wlc.cost_group_id)
944 -- Added the AND for fix to Bug 2764074..
945
946 --Bug 6523723 Added IS NULL condition.
947
948 AND (nvl(p_inventory_item_id, wlc.inventory_item_id) IS NOT NULL
949 OR (nvl(p_inventory_item_id, wlc.inventory_item_id) IS NULL AND
950 p_label_type_info.business_flow_code IS NULL))
951
952 -- Added for Bug 3581021 by joabraha
953 -- AND wlc.inventory_item_id = nvl(p_item_id,wlc.inventory_item_id)
954 -- Bug 4280265, Pick Load
955 -- The above where clause caused a regression problem for pick load txn
956 -- where lpn content is not packed to wlc yet.
957 -- changed to the following
958 AND nvl(wlc.inventory_item_id,-999) = nvl(p_item_id,nvl(wlc.inventory_item_id,-999))
959 -- Added the following condition for bug 4387168
960 AND nvl(wlc.lot_number,-1) = nvl(p_lot_number,nvl(wlc.lot_number,-1))
961 GROUP BY
962 nvl(p_organization_id, plpn.organization_id)
963 , nvl(p_inventory_item_id, wlc.inventory_item_id)
964 , nvl(p_revision, wlc.revision)
965 , nvl(p_lot_number,wlc.lot_number)
966 , nvl(p_uom, wlc.uom_code)
967 , nvl(p_cost_group_id, wlc.cost_group_id)
968 , ccg.cost_group
969 , milkfv.subinventory_code
970 , milkfv.inventory_location_id
971 , INV_PROJECT.GET_LOCSEGS(milkfv.inventory_location_id,milkfv.organization_id)
972 , wlc.secondary_uom_code;
973
974 --Bug 4891916 -Added the cursor to fetch from mcce
975 CURSOR mcce_lpn_cur IS
976 SELECT mcce.inventory_item_id
977 , mcce.organization_id
978 , mcce.lot_number
979 , mcce.cost_group_id
980 , mcce.count_quantity_current
981 , mcce.count_uom_current
982 , mcce.revision
983 , mcce.subinventory
984 , mcce.locator_id
985 , mcce.parent_lpn_id
986 , mcch.cycle_count_header_name
987 , ppf.full_name requestor
988 FROM mtl_cycle_count_headers mcch
989 , mtl_cycle_count_entries mcce
990 , per_people_f ppf
991 WHERE mcce.cycle_count_entry_id = p_transaction_Id
992 AND ppf.person_id(+) = mcce.counted_by_employee_id_current
993 AND mcce.cycle_count_header_id=mcch.cycle_count_header_id;
994
995 --End of fix for bug 4891916
996
997 --Bug 4891916. Added this cursor to get details like cycle count header name and
998 --counter for the entry for the label printed at the time of cycle count approval
999 CURSOR cc_det_approval IS
1000 SELECT mcch.cycle_count_header_name
1001 , ppf.full_name requestor
1002 FROM mtl_cycle_count_headers mcch
1003 , mtl_cycle_count_entries mcce
1004 , per_people_f ppf
1005 , mtl_material_transactions_temp mmtt
1006 WHERE mmtt.transaction_temp_id= p_transaction_id
1007 AND mmtt.cycle_count_id = mcce.cycle_count_entry_id
1008 AND mcce.cycle_count_header_id = mcch.cycle_count_header_id
1009 AND ppf.person_id(+) = mcce.counted_by_employee_id_current ;
1010 -- End of fix for Bug 4891916
1011
1012 -- Bug 4137707
1013 -- create new cursor for cartonization flow
1014 -- For cartonization flow, p_org.., p_inventory_item..,
1015 -- p_rev..p_lot..p_qty, p_uom, p_cg., l_subinventory, l_locator_id.are always null
1016 -- remove nvl(.) for those parameters
1017 -- Remove p_item_id because it is only used for receiving transactions
1018 CURSOR c_lpn_item_content_cart(p_lpn_id NUMBER, p_package_id NUMBER) IS
1019 SELECT
1020 plpn.organization_id organization_id
1021 , wlc.inventory_item_id inventory_item_id
1022 , wlc.revision revision
1023 , wlc.lot_number lot_number
1024 , sum(wlc.quantity) quantity
1025 , wlc.uom_code uom
1026 , wlc.cost_group_id cost_group_id
1027 , ccg.cost_group cost_group
1028 , milkfv.subinventory_code subinventory_code
1029 , milkfv.inventory_location_id locator_id
1030 , INV_PROJECT.GET_LOCSEGS(milkfv.inventory_location_id,milkfv.organization_id) locator
1031 , sum(nvl(l_secondary_quantity,wlc.secondary_quantity)) secondary_quantity -- invconv fabdi
1032 , wlc.secondary_uom_code secondary_uom -- invconv fabdi
1033 FROM wms_lpn_contents wlc
1034 , wms_license_plate_numbers plpn
1035 , cst_cost_groups ccg
1036 , mtl_item_locations milkfv
1037 WHERE plpn.lpn_id in (select lpn_id from wms_license_plate_numbers
1038 where 1=1
1039 start with lpn_id in (select nvl(p_lpn_id, -99) from dual
1040 union all
1041 select lpn_id from wms_packaging_hist
1042 where pack_level = 0
1043 and lpn_id IS not null
1044 start with parent_package_id = p_package_id
1045 connect by PARENT_PACKAGE_ID = PRIOR PACKAGE_ID)
1046 connect by parent_lpn_id = prior lpn_id)
1047 AND wlc.parent_lpn_id(+) = plpn.lpn_id
1048 AND milkfv.organization_id (+) = plpn.organization_id
1049 -- Added the new mode (WMS_CARTNZN_WRAP.mfg_pr_pkg_mode) for fix to Bug 2764074.
1050 AND milkfv.subinventory_code(+) =
1051 DECODE(l_packaging_mode, WMS_CARTNZN_WRAP.PR_PKG_MODE, NULL, WMS_CARTNZN_WRAP.mfg_pr_pkg_mode, NULL,
1052 plpn.subinventory_code)
1053 AND milkfv.inventory_location_id(+) =
1054 DECODE(l_packaging_mode, WMS_CARTNZN_WRAP.PR_PKG_MODE, NULL, WMS_CARTNZN_WRAP.mfg_pr_pkg_mode, NULL,
1055 plpn.locator_id)
1056 AND ccg.cost_group_id (+) = wlc.cost_group_id
1057 -- Added the AND for fix to Bug 2764074..
1058 -- Bug 4137707
1059 -- Do not need the where clause about p_item_id
1060 --AND nvl(p_inventory_item_id, wlc.inventory_item_id) IS NOT NULL
1061 -- Added for Bug 3581021 by joabraha
1062 -- AND wlc.inventory_item_id = nvl(p_item_id,wlc.inventory_item_id)
1063 -- Bug 4280265, Pick Load
1064 -- The above where clause caused a regression problem for pick load txn
1065 -- where lpn content is not packed to wlc yet.
1066 -- changed to the following
1067 --AND nvl(wlc.inventory_item_id,-999) = nvl(p_item_id,nvl(wlc.inventory_item_id,-999))
1068 GROUP BY
1069 plpn.organization_id
1070 , wlc.inventory_item_id
1071 , wlc.revision
1072 , wlc.lot_number
1073 , wlc.uom_code
1074 , wlc.cost_group_id
1075 , ccg.cost_group
1076 , milkfv.subinventory_code
1077 , milkfv.inventory_location_id
1078 , INV_PROJECT.GET_LOCSEGS(milkfv.inventory_location_id,milkfv.organization_id)
1079 , wlc.secondary_uom_code
1080
1081 UNION ALL
1082
1083 -- The Subinventory and location information is not required for the Outbound Stuff like Pick Release
1084 -- and Pick Confirm. Hence the decode for the sub and the loc in the where clause of this cursor.
1085
1086 SELECT
1087 wpc.organization_id organization_id
1088 , wpc.inventory_item_id inventory_item_id
1089 , wpc.revision revision
1090 , wpc.lot_number lot_number
1091 , sum(wpc.primary_quantity) quantity
1092 , msi.primary_uom_code uom
1093 , mmtt.cost_group_id cost_group_id
1094 , ccg.cost_group cost_group
1095 , milkfv.subinventory_code subinventory_code
1096 , milkfv.inventory_location_id locator_id
1097 , INV_PROJECT.GET_LOCSEGS(milkfv.inventory_location_id,milkfv.organization_id) locator
1098 , l_secondary_quantity secondary_quantity -- invconv fabdi
1099 , l_secondary_uom secondary_uom -- invconv fabdi
1100
1101 FROM wms_packaging_hist wpc
1102 , mtl_material_transactions_temp mmtt
1103 , mtl_system_items msi
1104 , cst_cost_groups ccg
1105 , mtl_item_locations milkfv
1106 -- Bug 4137707, Do not need to include this where clause,
1107 -- This will be controlled when opening this cursor
1108 -- WHERE cartonization_flag = 1 --Cartonization Flow
1109 WHERE wpc.rowid in (select rowid from wms_packaging_hist
1110 where pack_level = 0
1111 AND header_id = l_header_id
1112 AND l_packaging_mode in (WMS_CARTNZN_WRAP.PR_PKG_MODE, WMS_CARTNZN_WRAP.mfg_pr_pkg_mode)
1113 -- Added the new mode (WMS_CARTNZN_WRAP.mfg_pr_pkg_mode) for fix to Bug 2764074..
1114 AND lpn_id is null
1115 start with parent_lpn_id = p_lpn_id
1116 connect by PARENT_PACKAGE_ID = PRIOR PACKAGE_ID
1117 union all
1118 select rowid from wms_packaging_hist
1119 where pack_level = 0
1120 AND lpn_id is null
1121 start with parent_package_id = p_package_id
1122 connect by PARENT_PACKAGE_ID = PRIOR PACKAGE_ID)
1123 AND mmtt.transaction_temp_id (+) = wpc.reference_id
1124 AND msi.inventory_item_id (+) = wpc.inventory_item_id
1125 AND msi.organization_id (+) = wpc.organization_id
1126 AND milkfv.organization_id (+) = mmtt.organization_id
1127 -- Added the new mode (WMS_CARTNZN_WRAP.mfg_pr_pkg_mode) for fix to Bug 2764074..
1128 AND milkfv.subinventory_code(+) =
1129 DECODE(l_packaging_mode, WMS_CARTNZN_WRAP.PR_PKG_MODE, NULL, WMS_CARTNZN_WRAP.mfg_pr_pkg_mode, NULL,
1130 mmtt.subinventory_code)
1131 AND milkfv.inventory_location_id(+) =
1132 DECODE(l_packaging_mode, WMS_CARTNZN_WRAP.PR_PKG_MODE, NULL, WMS_CARTNZN_WRAP.mfg_pr_pkg_mode, NULL,
1133 mmtt.locator_id)
1134 AND ccg.cost_group_id (+) = mmtt.cost_group_id
1135 GROUP BY
1136 wpc.organization_id
1137 , wpc.inventory_item_id
1138 , wpc.revision
1139 , wpc.lot_number
1140 , msi.primary_uom_code
1141 , mmtt.cost_group_id
1142 , ccg.cost_group
1143 , milkfv.subinventory_code
1144 , milkfv.inventory_location_id
1145 , INV_PROJECT.GET_LOCSEGS(milkfv.inventory_location_id,milkfv.organization_id);
1146
1147
1148 /*
1149 * The following cursor has been added for bug # 4998201.
1150 * While performing Receipt and Receiving Put-Away Drop business flow for
1151 * serial, Lot Serial and Lot Serial revision controlled items, the cost_group_id
1152 * will be populated in mtl_serial_numbers table. Hence the following cursor has been
1153 * added to fetch the cost group details.
1154 */
1155
1156 CURSOR c_cost_group(p_lpn_id NUMBER
1157 , p_inventory_item_id NUMBER
1158 , p_lot_number VARCHAR) IS
1159 SELECT msn.cost_group_id
1160 , ccg.cost_group
1161 FROM mtl_serial_numbers msn
1162 , cst_cost_groups ccg
1163 WHERE msn.lpn_id = p_lpn_id
1164 AND msn.inventory_item_id = p_inventory_item_id
1165 AND msn.lot_number = p_lot_number
1166 AND msn.cost_group_id = ccg.cost_group_id;
1167
1168 -- invconv changes bug 4377633
1169 cursor c_origination_type (p_origination_type NUMBER)
1170 IS
1171 SELECT meaning
1172 FROM mfg_lookups
1173 WHERE lookup_type = 'MTL_LOT_ORIGINATION_TYPE'
1174 AND lookup_code = p_origination_type;
1175 l_origination_type mfg_lookups.meaning%TYPE;
1176
1177 l_content_lpn_id NUMBER;
1178 l_transfer_lpn_id NUMBER;
1179 l_from_lpn_id NUMBER;
1180 l_purchase_order PO_HEADERS_ALL.SEGMENT1%TYPE;
1181
1182 l_content_item_data LONG;
1183
1184 l_selected_fields INV_LABEL.label_field_variable_tbl_type;
1185 l_selected_fields_count NUMBER;
1186
1187 l_content_rec_index NUMBER := 0;
1188
1189 l_label_format_id NUMBER := null ;
1190 l_label_format VARCHAR2(100);
1191 l_printer VARCHAR2(30);
1192 l_printer_sub VARCHAR2(30) := null;
1193
1194 l_api_name VARCHAR2(20) := 'get_variable_data';
1195 l_return_status VARCHAR2(240);
1196
1197 l_error_message VARCHAR2(240);
1198 l_msg_count NUMBER;
1199 l_api_status VARCHAR2(240);
1200 l_msg_data VARCHAR2(240);
1201
1202 i NUMBER;
1203 j NUMBER;
1204
1205 new_label boolean:=true;
1206 no_of_rows_per_label NUMBER;
1207 row_index_per_label NUMBER;
1208 max_no_of_rows_defined NUMBER;
1209
1210 l_variable_name VARCHAR2(100);
1211
1212 l_cost_group_id NUMBER; -- Added for bug # 4998201
1213 l_cost_group VARCHAR2(10); -- Added for bug # 4998201
1214
1215 ---------------------------------------------------------------------------------------------
1216 -- Project: 'Custom Labels' (A 11i10+ Project) |
1217 -- Author: Dinesh ([email protected]) |
1218 -- Change Description: |
1219 -- Following variables were added (as a part of 11i10+ 'Custom Labels' Project) |
1220 -- to retrieve and hold the SQL Statement and it's result. |
1221 ---------------------------------------------------------------------------------------------
1222 l_sql_stmt VARCHAR2(4000);
1223 l_sql_stmt_result VARCHAR2(4000);
1224 TYPE sql_stmt IS REF CURSOR;
1225 c_sql_stmt sql_stmt;
1226 l_custom_sql_ret_status VARCHAR2(1);
1227 l_custom_sql_ret_msg VARCHAR2(2000);
1228
1229 -- Fix for bug: 4179593 Start
1230 l_CustSqlWarnFlagSet BOOLEAN;
1231 l_CustSqlErrFlagSet BOOLEAN;
1232 l_CustSqlWarnMsg VARCHAR2(2000);
1233 l_CustSqlErrMsg VARCHAR2(2000);
1234 -- Fix for bug: 4179593 End
1235
1236 ------------------------End of this change for Custom Labels project code--------------------
1237
1238 l_organization_id NUMBER;
1239
1240 l_lpn_table inv_label.lpn_table_type;
1241 l_lpn_table_index NUMBER;
1242 l_lpn_info lpn_data_type_rec; --lpn_data_tbl_type;
1243 l_item_info item_data_type_rec; --item_data_tbl_type;
1244
1245 l_rcv_lpn_table rcv_label_tbl_type; -- Table of LPN-level info :J-DEV
1246 l_rlpn_ndx NUMBER := 0; -- Index to table of records for l_rcv_lpn_table
1247 l_rcv_isp_header rcv_isp_header_rec ; -- Header-level info for ASN iSP
1248
1249 l_po_line_number number;
1250 l_quantity_ordered number;
1251 l_supplier_part_number varchar2(25);
1252 -- START of Bug fix for 3916663
1253 --l_supplier_name VARCHAR2(80);
1254 --l_supplier_site VARCHAR2(15);
1255 --l_requestor VARCHAR2(80);
1256 --l_deliver_to_location VARCHAR2(20);
1257 --l_location_code VARCHAR2(20);
1258 --l_note_to_receiver VARCHAR2(240);
1259
1260 -- Increased this variable size to the corresponding column size in the table.
1261 l_supplier_name po_vendors.VENDOR_NAME%TYPE;
1262 l_supplier_site po_vendor_sites.VENDOR_SITE_CODE%TYPE;
1263 l_requestor per_people_f.FULL_NAME%TYPE;
1264 l_deliver_to_location hr_locations_all.LOCATION_CODE%TYPE;
1265 l_location_code hr_locations_all.LOCATION_CODE%TYPE;
1266 l_note_to_receiver po_line_locations.NOTE_TO_RECEIVER%TYPE;
1267
1268 -- END of Bug fix for 3916663
1269
1270 -- Bug 2515486
1271 l_transaction_type_id number := 0;
1272 l_transaction_action_id number := 0;
1273
1274 l_loop_counter number := 0;
1275 l_label_index NUMBER;
1276 l_label_request_id NUMBER;
1277
1278 --I cleanup, use l_prev_format_id to record the previous label format
1279 l_prev_format_id NUMBER;
1280
1281 l_patch_level NUMBER;
1282
1283 -- Added for Bug 3581021 by joabraha
1284 -- Item id that is currently being processed in RCV flows
1285 l_cur_item_id number:= null;
1286 --
1287
1288 -- Variable for EPC Generation
1289 -- Added for 11.5.10+ RFID Compliance project
1290 --Modified in R12
1291
1292 l_epc VARCHAR2(300);
1293 l_epc_ret_status VARCHAR2(10);
1294 l_epc_ret_msg VARCHAR2(1000);
1295 l_label_status VARCHAR2(1);
1296 l_label_err_msg VARCHAR2(1000);
1297 l_is_epc_exist VARCHAR2(1) := 'N';
1298
1299 -- Bug 4137707
1300 v_lpn_content c_lpn_item_content%ROWTYPE;
1301
1302 l_count_custom_sql NUMBER := 0; -- Added for Bug#4179391
1303
1304 --Bug 4891916. Added the local variable to store the cycle count name
1305 l_cycle_count_name mtl_cycle_count_headers.cycle_count_header_name%TYPE;
1306 --lpn status project start
1307 l_material_status_code varchar2(30) := NULL;
1308 l_onhand_status_enabled NUMBER := 0;
1309 --lpn status project end
1310
1311 BEGIN
1312 l_debug := INV_LABEL.l_debug;
1313 IF (l_debug = 1) THEN
1314 trace('**In PVT5: LPN Summary label**');
1315 trace(' Business_flow='||p_label_type_info.business_flow_code ||
1316 ', Transaction ID='||p_transaction_id ||
1317 ', Transaction Identifier='||p_transaction_identifier );
1318 END IF;
1319 -- Initialize return status as success
1320 x_return_status := FND_API.G_RET_STS_SUCCESS;
1321
1322 IF (inv_rcv_common_apis.g_inv_patch_level >= inv_rcv_common_apis.g_patchset_j)
1323 AND (inv_rcv_common_apis.g_po_patch_level >=inv_rcv_common_apis.g_patchset_j_po) THEN
1324 l_patch_level := 1;
1325 ELSIF (inv_rcv_common_apis.g_inv_patch_level < inv_rcv_common_apis.g_patchset_j)
1326 AND (inv_rcv_common_apis.g_po_patch_level < inv_rcv_common_apis.g_patchset_j_po) THEN
1327 l_patch_level := 0;
1328 END IF;
1329 trace('patch level is ******* ' || l_patch_level);
1330 -- Get l_lpn_id
1331 IF p_lpn_id IS NOT NULL and p_label_type_info.business_flow_code <> 25 THEN
1332 l_lpn_id := p_lpn_id;
1333 /* Bug# 3263037 */
1334 l_lpn_table(1) := l_lpn_id;
1335 /* End of 3263037 */
1336 ELSE
1337 IF p_transaction_id IS NOT NULL THEN
1338 -- txn driven
1339 i := 1;
1340 IF p_label_type_info.business_flow_code in (1,2,3,4) THEN
1341 -- Receipt, Inspection, Delivery, Putaway
1342 IF ( p_transaction_identifier = INV_LABEL.TRX_ID_RT) OR l_patch_level = 1 THEN
1343 trace('is J patchset ');
1344 -- New Architecture : Get LPN from RT :J-DEV
1345 -- Applicable with DM.J and IProc.J
1346 FOR v_rt_lpn IN c_rt_lpn LOOP
1347 l_rlpn_ndx := l_rlpn_ndx+1;
1348
1349 l_rcv_lpn_table(l_rlpn_ndx).lpn_id := v_rt_lpn.lpn_id;
1350 trace('lpn_id = ' || l_rcv_lpn_table(l_rlpn_ndx).lpn_id || 'l_rlpn_ndx ' || l_rlpn_ndx);
1351 l_rcv_lpn_table(l_rlpn_ndx).purchase_order := v_rt_lpn.purchase_order;
1352 l_rcv_lpn_table(l_rlpn_ndx).subinventory := v_rt_lpn.subinventory;
1353 l_rcv_lpn_table(l_rlpn_ndx).locator_id := v_rt_lpn.locator_id;
1354 l_rcv_lpn_table(l_rlpn_ndx).receipt_num := v_rt_lpn.receipt_num;
1355 l_rcv_lpn_table(l_rlpn_ndx).po_line_num := v_rt_lpn.po_line_number;
1356 l_rcv_lpn_table(l_rlpn_ndx).quantity_ordered := v_rt_lpn.quantity_ordered;
1357 l_rcv_lpn_table(l_rlpn_ndx).supplier_part_number := v_rt_lpn.supplier_part_number;
1358 l_rcv_lpn_table(l_rlpn_ndx).vendor_id := v_rt_lpn.vendor_id;
1359 l_rcv_lpn_table(l_rlpn_ndx).vendor_site_id := v_rt_lpn.vendor_site_id;
1360 l_rcv_lpn_table(l_rlpn_ndx).supplier_site := v_rt_lpn.supplier_site;
1361 l_rcv_lpn_table(l_rlpn_ndx).supplier_name := v_rt_lpn.supplier_name;
1362 l_rcv_lpn_table(l_rlpn_ndx).requestor := v_rt_lpn.requestor;
1363 -- l_rcv_lpn_table(l_rlpn_ndx).deliver_to_location := v_rt_lpn.deliver_to_location;
1364 -- l_rcv_lpn_table(l_rlpn_ndx).location := v_rt_lpn.location;
1365 l_rcv_lpn_table(l_rlpn_ndx).note_to_receiver := v_rt_lpn.note_to_receiver;
1366 l_rcv_lpn_table(l_rlpn_ndx).item_id := v_rt_lpn.item_id;
1367
1368 l_deliver_to_location_id := v_rt_lpn.deliver_to_location_id;
1369 l_location_id := v_rt_lpn.location_id;
1370
1371 IF l_deliver_to_location_id IS NOT NULL OR l_location_id IS NOT NULL THEN
1372 trace('either l_location_id or l_deliver_to_location_id is not null');
1373 for v_hr in c_hr_locations loop
1374 l_rcv_lpn_table(l_rlpn_ndx).deliver_to_location := v_hr.deliver_to_location;
1375 l_rcv_lpn_table(l_rlpn_ndx).location := v_hr.location;
1376 END LOOP;
1377 END IF;
1378
1379 --l_rlpn_ndx := l_rlpn_ndx+1;
1380 END LOOP;
1381 ELSE
1382 -- Old Architecture
1383 IF p_label_type_info.business_flow_code = 2 THEN
1384 -- Inspection
1385 -- Getting lpn_id from RTI
1386 FOR v_rti_lpn_inspection IN c_rti_lpn_inspection LOOP
1387 l_lpn_table(i) := v_rti_lpn_inspection.transfer_lpn_id;
1388 l_purchase_order := v_rti_lpn_inspection.purchase_order;
1389 l_subinventory_code := v_rti_lpn_inspection.subinventory;
1390 l_locator_id := v_rti_lpn_inspection.locator_id;
1391 l_receipt_number := INV_RCV_COMMON_APIS.g_rcv_global_var.receipt_num;
1392 l_po_line_number := v_rti_lpn_inspection.po_line_number;
1393 l_quantity_ordered := v_rti_lpn_inspection.quantity_ordered;
1394 l_supplier_part_number := v_rti_lpn_inspection.supplier_part_number;
1395 l_supplier_name := v_rti_lpn_inspection.supplier_name;
1396 l_vendor_id := v_rti_lpn_inspection.vendor_id;
1397 l_vendor_site_id := v_rti_lpn_inspection.vendor_site_id;
1398 l_supplier_site := v_rti_lpn_inspection.supplier_site;
1399 l_requestor := v_rti_lpn_inspection.requestor;
1400 l_deliver_to_location := v_rti_lpn_inspection.deliver_to_location;
1401 l_location_code := v_rti_lpn_inspection.location;
1402 l_note_to_receiver := v_rti_lpn_inspection.note_to_receiver;
1403 i := i+1;
1404 END LOOP;
1405 ELSE
1406 -- Getting lpn_id from RTI for Rcpt, Putaway, Delivery flows
1407 FOR v_rti_lpn IN c_rti_lpn LOOP
1408 l_lpn_table(i) := v_rti_lpn.lpn_id;
1409 l_purchase_order := v_rti_lpn.purchase_order;
1410 l_subinventory_code := v_rti_lpn.subinventory;
1411 l_locator_id := v_rti_lpn.locator_id;
1412 l_receipt_number := INV_RCV_COMMON_APIS.g_rcv_global_var.receipt_num;
1413 l_po_line_number := v_rti_lpn.po_line_number;
1414 l_quantity_ordered := v_rti_lpn.quantity_ordered;
1415 l_supplier_part_number := v_rti_lpn.supplier_part_number;
1416 l_vendor_id := v_rti_lpn.vendor_id;
1417 l_vendor_site_id := v_rti_lpn.vendor_site_id;
1418 l_supplier_name := v_rti_lpn.supplier_name;
1419 l_supplier_site := v_rti_lpn.supplier_site;
1420 l_requestor := v_rti_lpn.requestor;
1421 l_deliver_to_location := v_rti_lpn.deliver_to_location;
1422 l_location_code := v_rti_lpn.location;
1423 l_note_to_receiver := v_rti_lpn.note_to_receiver;
1424 i := i+1;
1425 END LOOP;
1426 END IF; -- p_label_type_info.business_flow_code = 2
1427 END IF; -- p_transaction_identifier = INV_LABEL.TRX_ID_RT
1428 ELSIF p_label_type_info.business_flow_code in (6) THEN
1429 -- Cross-Dock, Pick Load and Pick Drop
1430 -- The delivery_detail_id of the line in WDD which has the LPN_ID
1431 -- is passed , get lpn_id from WDD lines
1432 OPEN c_wdd_lpn;
1433 FETCH c_wdd_lpn INTO l_lpn_id, p_organization_id, l_subinventory_code;
1434 IF c_wdd_lpn%NOTFOUND THEN
1435 IF (l_debug = 1) THEN
1436 trace(' No cross-dock found in MMTT for ID:'||p_transaction_id);
1437 END IF;
1438 CLOSE c_wdd_lpn;
1439 RETURN;
1440 ELSE
1441 IF l_lpn_id IS NOT NULL THEN
1442 l_lpn_table(1) := l_lpn_id;
1443 END IF;
1444 END IF;
1445 ELSIF p_label_type_info.business_flow_code in (21) THEN
1446 -- Ship confirm, delivery_id is passed
1447 -- Get all the LPNs for this delivery
1448 FOR v_wnd_lpn IN c_wnd_lpn LOOP
1449 l_lpn_table(i) := v_wnd_lpn.lpn_id;
1450 i := i+1;
1451 END LOOP;
1452 ELSIF p_label_type_info.business_flow_code in (22) THEN
1453 -- Cartonization: the lpn_id is in cartonization_id
1454 -- Set flag to so that packaging history will be checked for items.
1455 cartonization_flag := 1;
1456
1457 -- Find the header and packing mode to identify cartonization batch
1458 -- if no records found, should not try to access wph, so set flag to 0
1459 Begin
1460 SELECT DISTINCT header_id, packaging_mode , pack_level
1461 INTO l_header_id, l_packaging_mode,l_pack_level
1462 FROM WMS_PACKAGING_HIST
1463 WHERE parent_lpn_id = p_transaction_id;
1464 EXCEPTION
1465 WHEN no_data_found THEN
1466 IF (l_debug = 1) THEN
1467 trace('No record found in WPH with parent_lpn_id: '|| p_transaction_id);
1468 END IF;
1469 cartonization_flag := 0;
1470 END;
1471
1472 OPEN c_mmtt_cart_lpn;
1473 l_outermost_lpn_id := p_transaction_id;
1474 l_lpn_id := p_transaction_id;
1475 l_lpn_table(1) := l_lpn_id;
1476 /* Bug# 3423817*/
1477 l_pack_level := l_pack_level + 1;
1478
1479 ELSIF p_label_type_info.business_flow_code = INV_LABEL.WMS_BF_IMPORT_ASN THEN
1480 IF ( p_transaction_identifier = INV_LABEL.TRX_ID_RSH) THEN
1481 -- New Architecture for ASN : Get LPN details from RSH :J-DEV
1482 -- Applicable with DM.J and IProc.J
1483 -- First retrieve the header level info
1484 SELECT shipment_num asn_num, shipped_date shipment_date,
1485 expected_receipt_date,freight_terms,
1486 freight_carrier_code, num_of_containers,
1487 bill_of_lading, waybill_airbill_num,
1488 packing_slip,
1489 packaging_code, special_handling_code,
1490 receipt_num, comments
1491 INTO l_rcv_isp_header.asn_num, l_rcv_isp_header.shipment_date,
1492 l_rcv_isp_header.expected_receipt_date, l_rcv_isp_header.freight_terms,
1493 l_rcv_isp_header.freight_carrier, l_rcv_isp_header.num_of_containers,
1494 l_rcv_isp_header.bill_of_lading, l_rcv_isp_header.waybill_airbill_num,
1495 l_rcv_isp_header.packing_slip,
1496 l_rcv_isp_header.packaging_code, l_rcv_isp_header.special_handling_code,
1497 l_rcv_isp_header.receipt_num, l_rcv_isp_header.comments
1498 FROM rcv_shipment_headers
1499 WHERE shipment_header_id = p_transaction_id
1500 -- OR shipment_header_id in --Bug 5051210. Performance fix. Removing OR and adding UNION
1501 UNION
1502 SELECT shipment_num asn_num, shipped_date shipment_date,
1503 expected_receipt_date,freight_terms,
1504 freight_carrier_code, num_of_containers,
1505 bill_of_lading, waybill_airbill_num,
1506 packing_slip,
1507 packaging_code, special_handling_code,
1508 receipt_num, comments
1509 FROM rcv_shipment_headers
1510 WHERE shipment_header_id IN
1511 (select shipment_header_id from rcv_shipment_lines
1512 where asn_lpn_id = p_lpn_id);
1513
1514 -- Next retrieve details of all distinct LPNs associated with this shipment
1515
1516 FOR v_asn_lpn IN c_asn_lpn
1517 LOOP
1518 l_rlpn_ndx := l_rlpn_ndx + 1;
1519
1520 l_rcv_lpn_table(l_rlpn_ndx).lpn_id := v_asn_lpn.lpn_id;
1521 l_rcv_lpn_table(l_rlpn_ndx).purchase_order := v_asn_lpn.purchase_order;
1522 l_rcv_lpn_table(l_rlpn_ndx).subinventory := v_asn_lpn.subinventory_code;
1523 l_rcv_lpn_table(l_rlpn_ndx).locator_id := v_asn_lpn.locator_id;
1524 l_rcv_lpn_table(l_rlpn_ndx).due_date := v_asn_lpn.due_date;
1525 l_rcv_lpn_table(l_rlpn_ndx).truck_num := v_asn_lpn.truck_num;
1526 l_rcv_lpn_table(l_rlpn_ndx).country_of_origin := v_asn_lpn.country_of_origin_code;
1527 l_rcv_lpn_table(l_rlpn_ndx).comments := v_asn_lpn.comments;
1528 l_rcv_lpn_table(l_rlpn_ndx).po_line_num := v_asn_lpn.po_line_number;
1529 l_rcv_lpn_table(l_rlpn_ndx).quantity_ordered := v_asn_lpn.quantity_ordered;
1530 l_rcv_lpn_table(l_rlpn_ndx).supplier_part_number := v_asn_lpn.supplier_part_number;
1531 l_rcv_lpn_table(l_rlpn_ndx).vendor_id := v_asn_lpn.vendor_id;
1532 l_rcv_lpn_table(l_rlpn_ndx).vendor_site_id := v_asn_lpn.vendor_site_id;
1533 l_rcv_lpn_table(l_rlpn_ndx).supplier_site := v_asn_lpn.supplier_site;
1534 l_rcv_lpn_table(l_rlpn_ndx).supplier_name := v_asn_lpn.supplier_name;
1535 l_rcv_lpn_table(l_rlpn_ndx).requestor := v_asn_lpn.requestor;
1536 l_rcv_lpn_table(l_rlpn_ndx).deliver_to_location := v_asn_lpn.deliver_to_location;
1537 l_rcv_lpn_table(l_rlpn_ndx).location := v_asn_lpn.location;
1538 l_rcv_lpn_table(l_rlpn_ndx).note_to_receiver := v_asn_lpn.note_to_receiver;
1539 l_rcv_lpn_table(l_rlpn_ndx).packing_slip := v_asn_lpn.packing_slip;
1540
1541 -- Fields queried from RSH
1542 l_rcv_lpn_table(l_rlpn_ndx).receipt_num := l_rcv_isp_header.receipt_num;
1543 END LOOP;
1544 ELSE
1545 -- Old Architecture
1546 l_lpn_table(1) := p_input_param.lpn_id;
1547 END IF;
1548 -- Bug 4277718
1549 -- for WIP completion, lpn_id is used rather than transfer_lpn_id
1550 -- Changed to use c_mmtt_lpn
1551 /*ELSIF p_label_type_info.business_flow_code in (26) THEN
1552 -- WIP Completion
1553 FOR v_wip_lpn IN c_wip_lpn
1554 LOOP
1555 l_lpn_table(i) := v_wip_lpn.transfer_lpn_id;
1556 i := i+1;
1557 END LOOP;*/
1558 ELSIF p_label_type_info.business_flow_code in (29) THEN
1559 -- WIP Pick Drop, the lpn will not be packed, the lpn_id is transfer_lpn_id
1560 OPEN c_mmtt_wip_pick_drop_lpn;
1561 FETCH c_mmtt_wip_pick_drop_lpn
1562 INTO l_lpn_id, p_organization_id,
1563 p_inventory_item_id, p_lot_number,
1564 p_revision, p_qty, p_uom,
1565 l_subinventory_code, l_locator_id,
1566 l_secondary_quantity, l_secondary_uom; -- invconv changes
1567
1568 IF c_mmtt_wip_pick_drop_lpn%NOTFOUND THEN
1569 IF (l_debug = 1) THEN
1570 trace(' No WIP Pick Drop record found in MMTT for ID: '|| p_transaction_id);
1571 END IF;
1572 CLOSE c_mmtt_wip_pick_drop_lpn;
1573 RETURN;
1574 ELSE
1575 IF l_lpn_id IS NOT NULL THEN
1576 l_lpn_table(1) := l_lpn_id;
1577 END IF;
1578 END IF;
1579 ELSIF p_label_type_info.business_flow_code in (27) THEN
1580 -- Putaway pregeneration
1581 -- Get lpn_id from mmtt
1582 FOR v_pregen_lpn IN c_mmtt_pregen_lpn LOOP
1583 l_lpn_table(1) := v_pregen_lpn.lpn_id;
1584 l_subinventory_code := v_pregen_lpn.subinventory_code;
1585 l_locator_id := v_pregen_lpn.locator_id;
1586 END LOOP;
1587
1588 -- Fix bug 2167545-1 Cost Group Update(11) is calling label printing through TM
1589 -- not manually, add 11 in the following group.
1590 -- Bug 4277718
1591 -- for WIP completion, lpn_id is used rather than transfer_lpn_id
1592 -- Changed to use c_mmtt_lpn
1593
1594 --Bug 4891916. Modified the condition for business flow for cycle count
1595 --by checking for the business flow 8 and transaction_identifier as 5
1596
1597 ELSIF p_label_type_info.business_flow_code IN (7,/*8,*/9,11,12,13,14,15,19,20,23,30,26)
1598 OR(p_label_type_info.business_flow_code IN(33) AND p_transaction_identifier=1)
1599 OR(p_label_type_info.business_flow_code = 8 AND p_transaction_identifier = 5) THEN
1600 -- Obtain lpn_id, content_lpn_id, transfer_lpn_id from
1601 -- MMT record.
1602 OPEN c_mmtt_lpn;
1603 FETCH c_mmtt_lpn
1604 INTO l_from_lpn_id, l_content_lpn_id, l_transfer_lpn_id,l_subinventory_code, l_locator_id,
1605 l_transaction_type_id, l_transaction_action_id,l_uom;
1606 -- Bug 2515486: Added transaction_type_id, transaction_action_id, inventory_item_id ;
1607
1608 IF (l_debug = 1) THEN
1609 trace('From LPN ID : ' || l_from_lpn_id||
1610 ',Content LPN ID : ' || l_content_lpn_id||
1611 ',Transfer LPN ID : ' || l_transfer_lpn_id||
1612 ',Transaction Type ID : ' || l_transaction_type_id||
1613 ',Transaction Action ID : ' || l_transaction_action_id);
1614 END IF;
1615
1616 IF c_mmtt_lpn%NOTFOUND THEN
1617 IF (l_debug = 1) THEN
1618 trace(' No lpn_id found in MMTT for given ID: '|| p_transaction_id);
1619 END IF;
1620 CLOSE c_mmtt_lpn;
1621 RETURN;
1622 ELSE
1623 CLOSE c_mmtt_lpn;
1624
1625 --Bug 4891916. For cycle count, opened the cursor to fetch
1626 --values for cycle count header name and counter
1627 IF p_label_type_info.business_flow_code = 8 THEN
1628 OPEN cc_det_approval ;
1629
1630 FETCH cc_det_approval
1631 INTO l_cycle_count_name
1632 , l_requestor ;
1633
1634 IF cc_det_approval%NOTFOUND THEN
1635 IF (l_debug = 1) THEN
1636 TRACE(' No record found in MMTT for a cycle count id for given txn_temp_id: ' || p_transaction_id);
1637 END IF;
1638 CLOSE cc_det_approval;
1639 END IF;
1640
1641 END IF ; -- End of business flow=8 condition
1642
1643 --End of fix for Bug 4891916
1644
1645 -- Bug 2515486
1646 -- This check ensures that the content LPN ID is not added to the l_lpn_table for
1647 -- LPN Consolidation.
1648 --Bug 3277260
1649 -- Updated the condition to make sure that the LPN ID is not added for Pick-Drop
1650 -- Business Flow(19).
1651 IF (l_content_lpn_id IS NOT NULL) THEN
1652 IF ((l_transaction_type_id = 87 AND l_transaction_action_id = 50) AND
1653 (p_label_type_info.business_flow_code = 20 OR p_label_type_info.business_flow_code = 19)) THEN
1654 NULL;
1655 IF (l_debug = 1) THEN
1656 trace('The Content LPN ID is not added to the l_lpn_table');
1657 END IF;
1658 ELSE
1659 l_lpn_table(i) := l_content_lpn_id;
1660 i := i+1;
1661 IF (l_debug = 1) THEN
1662 trace('Content LPN ID has been added to the l_lpn_table');
1663 END IF;
1664
1665 END IF;
1666 END IF;
1667
1668 /* Start of fix for bug # 4751587 */
1669 /* The following condition has been added for fixing the bug # 4751587
1670 For Cost Group Update Bussiness Flow (11), only one label has to be generated with
1671 the updated cost group. Hence the following code (incrementing i, which controls the
1672 loop iteration) will be executed only if the business flow code is not 11
1673 i.e. Cost Group Update Business flow */
1674
1675 IF (p_label_type_info.business_flow_code <> 11) THEN
1676 IF (l_transfer_lpn_id IS NOT NULL)
1677 AND(NVL(l_transfer_lpn_id, -999) <> NVL(l_content_lpn_id, -999)) THEN
1678 l_lpn_table(i) := l_transfer_lpn_id;
1679 i := i + 1;
1680 END IF;
1681 END IF;
1682
1683 /* IF (l_transfer_lpn_id IS NOT NULL)
1684 AND (nvl(l_transfer_lpn_id,-999) <> nvl(l_content_lpn_id,-999)) THEN
1685 l_lpn_table(i) := l_transfer_lpn_id;
1686 i := i+1;
1687 END IF; */
1688
1689 /* End of fix for bug # 4751587 */
1690
1691 -- Bug 2367828 : In case of LPN Splits, the LPN labels were being printed for
1692 -- the new LPN being generated, but nothing for the existing LPN from which the
1693 -- the new LPN was being split. l_from_lpn_id is the mmtt.lpn_id(the from LPN)
1694 IF (l_from_lpn_id IS NOT NULL) THEN
1695 l_lpn_table(i) := l_from_lpn_id;
1696 END IF;
1697 END IF;
1698
1699 --Bug 4891916- Added the condition to open the cursor to fetch from
1700 --mcce by checking for business flow 8 and transaction identifier 4
1701 ELSIF p_label_type_info.business_flow_code = 8 and p_transaction_identifier = 4 THEN
1702 IF (l_debug = 1) THEN
1703 TRACE(' In the condition for bus flow 8 and pti 4 ');
1704 END IF;
1705
1706 OPEN mcce_lpn_cur ;
1707
1708 FETCH mcce_lpn_cur
1709 INTO l_inventory_item_id
1710 , l_organization_id
1711 , l_lot_number
1712 , l_cost_group_id
1713 , l_qty
1714 , l_uom
1715 , l_revision
1716 , l_subinventory_code
1717 , l_locator_id
1718 , l_lpn_id
1719 , l_cycle_count_name
1720 , l_requestor ;
1721
1722 IF (l_debug = 1) THEN
1723 TRACE('Values fetched from cursor:');
1724 TRACE('Values of l_inventory_item_id:'|| l_inventory_item_id);
1725 TRACE('Values of l_organization_id:' || l_organization_id);
1726 TRACE('Values of l_lot_number:' || l_lot_number);
1727 TRACE('Values of l_cost_group_id:' || l_cost_group_id);
1728 TRACE('Values of l_quantity:' || l_qty);
1729 TRACE('Values of l_uom:' || l_uom);
1730 TRACE('Values of l_revision:' || l_revision);
1731 TRACE('Values of l_subinventory:' || l_subinventory_code);
1732 TRACE('Values of l_locator_id:' || l_locator_id);
1733 TRACE('Values of l_lpn_id:' || l_lpn_id);
1734 TRACE('Values of l_cycle_count_name:' || l_cycle_count_name);
1735 TRACE('Values of Counter' || l_requestor);
1736 END IF;
1737
1738 IF mcce_lpn_cur%NOTFOUND THEN
1739 IF (l_debug = 1) THEN
1740 TRACE(' No record in mcce for this transaction_id:' || p_transaction_id);
1741 END IF;
1742
1743 CLOSE mcce_lpn_cur;
1744 RETURN;
1745 ELSE
1746 IF l_lpn_id IS NOT NULL THEN
1747 l_lpn_table(1) := l_lpn_id;
1748 END IF;
1749 CLOSE mcce_lpn_cur ;
1750 END IF;
1751 --End of fix for Bug 4891916
1752
1753 -- 18th February 2002 : Commented out below for fix to bug 2219171 for Qualcomm. Hence forth the
1754 -- WMSTASKB.pls will be calling label printing at Pick Load and WIP Pick Load with the
1755 -- transaction_temp_id as opposed to the transaction_header_id earlier. These business flows(18, 28,34)
1756 -- have been added to the above call.
1757 ELSIF p_label_type_info.business_flow_code in (18,28,34) THEN
1758 -- Pick Load
1759 OPEN c_mmtt_lpn_pick_load;
1760 FETCH c_mmtt_lpn_pick_load INTO l_lpn_id, p_organization_id,
1761 p_inventory_item_id, p_lot_number, p_revision, p_qty,
1762 p_uom, l_subinventory_code, l_locator_id, l_printer_sub,
1763 l_secondary_quantity, -- invconv changes
1764 l_secondary_uom; -- invconv changes
1765
1766 IF c_mmtt_lpn_pick_load%NOTFOUND THEN
1767 IF (l_debug = 1) THEN
1768 trace(' No record found in MMTT for temp ID: '|| p_transaction_id);
1769 END IF;
1770 CLOSE c_mmtt_lpn_pick_load;
1771 RETURN;
1772 ELSE
1773 IF l_lpn_id IS NOT NULL THEN
1774 l_lpn_table(1) := l_lpn_id;
1775 END IF;
1776 END IF;
1777
1778
1779 ELSIF p_label_type_info.business_flow_code in (33) AND p_transaction_identifier>1 THEN
1780 -- Flow Completion, not MMTT based
1781
1782 IF p_transaction_identifier=2 THEN
1783 IF (l_debug = 1) THEN
1784 trace('Flow Label - MTI based');
1785 END IF;
1786 FOR v_flow_mti_lpn IN c_flow_lpn_mti LOOP
1787 l_lpn_table(i) :=v_flow_mti_lpn.lpn_id;
1788 i := i+1;
1789 END LOOP;
1790 ELSIF p_transaction_identifier=3 THEN
1791 IF (l_debug = 1) THEN
1792 trace('Flow Label - MOL based');
1793 END IF;
1794 FOR v_flow_mol_lpn IN c_flow_lpn_mti LOOP
1795 l_lpn_table(i) :=v_flow_mol_lpn.lpn_id;
1796 i := i+1;
1797 END LOOP;
1798 END IF;
1799
1800 ELSE
1801 IF (l_debug = 1) THEN
1802 trace(' Invalid business flow code '|| p_label_type_info.business_flow_code);
1803 END IF;
1804 RETURN;
1805 END IF;
1806 ELSE
1807 -- On demand, get information from input_param
1808 -- for transactions which don't have a mmtt row in the table,
1809 -- they will also call in a manual mode, they are
1810 -- 5 LPN Correction/Update
1811 -- 10 Material Status update
1812 -- 16 LPN Generation
1813 -- 25 Import ASN
1814 trace('krishna');
1815 trace(' Business flow code is : '|| p_label_type_info.business_flow_code);
1816 trace(' l_cur_item_id : '|| l_cur_item_id);
1817 trace(' p_inventory_item_id : '|| p_inventory_item_id);
1818
1819 l_lpn_table(1) := nvl(p_lpn_id,p_input_param.lpn_id);
1820 END IF;
1821 END IF;
1822
1823 IF (l_debug = 1) THEN
1824 trace('Value of l_rlpn_ndx: '||l_rlpn_ndx);
1825 trace(' No. of LPN_IDs found: '|| l_lpn_table.count);
1826 END IF;
1827 IF (l_debug = 1) THEN
1828 FOR i IN 1..l_lpn_table.count LOOP
1829 trace(' LPN_ID('||i||')'|| l_lpn_table(i));
1830 END LOOP;
1831 END IF;
1832 trace('lpn table count ' || l_lpn_table.count || ' l_rlpn_ndx ' || l_rlpn_ndx);
1833 IF l_lpn_table.count = 0 AND l_rlpn_ndx = 0 THEN
1834 IF (l_debug = 1) THEN
1835 trace(' No LPN found, can not process ');
1836 END IF;
1837 RETURN;
1838 END IF;
1839
1840
1841
1842 /* Blocked in R12
1843
1844 IF (l_debug = 1) THEN
1845 trace(' Getting selected fields ');
1846 END IF;
1847 INV_LABEL.GET_VARIABLES_FOR_FORMAT(
1848 x_variables => l_selected_fields
1849 , x_variables_count => l_selected_fields_count
1850 , x_is_variable_exist => l_is_epc_exist
1851 , p_format_id => p_label_type_info.default_format_id
1852 , p_exist_variable_name => 'EPC');
1853
1854 IF (l_selected_fields_count=0) OR (l_selected_fields.count =0 ) THEN
1855 IF (l_debug = 1) THEN
1856 trace('no fields defined for this format: ' || p_label_type_info.default_format_id || ',' ||p_label_type_info.default_format_name);
1857 END IF;
1858 --return;
1859 END IF;
1860
1861 IF (l_debug = 1) THEN
1862 trace(' Found variable defined for this format, cont = ' || l_selected_fields_count);
1863 END IF;
1864 */
1865
1866 l_content_rec_index := 0;
1867 l_content_item_data := '';
1868 IF (l_debug = 1) THEN
1869 trace('** in PVT5.get_variable_dataa ** , start ');
1870 END IF;
1871 l_printer := p_label_type_info.default_printer;
1872
1873 -- Get number of rows per label
1874 BEGIN
1875 select min(table_a.c) into no_of_rows_per_label
1876 from (select wlfv.label_field_id,
1877 wlf.column_name, count(*) c
1878 from wms_label_field_variables wlfv, wms_label_fields_vl wlf
1879 where wlfv.label_field_id = wlf.label_field_id
1880 and wlfv.label_format_id = p_label_type_info.default_format_id
1881 group by wlfv.label_field_id, wlf.column_name
1882 having count(*)>1 ) table_a;
1883 EXCEPTION
1884 WHEN no_data_found THEN
1885 IF (l_debug = 1) THEN
1886 trace(' Did not find defined rows ');
1887 END IF;
1888 END;
1889
1890 IF (no_of_rows_per_label IS NULL) OR (no_of_rows_per_label=0) THEN
1891 no_of_rows_per_label :=1 ;
1892 END IF;
1893
1894 IF (l_debug = 1) THEN
1895 trace(' Got max rows per label='|| no_of_rows_per_label);
1896 END IF;
1897 new_label := true;
1898 row_index_per_label := 0;
1899
1900 IF (l_debug = 1) THEN
1901 trace('LPN ID = '||l_lpn_id||','||', Patch Level = '||l_patch_level||','||
1902 ', RLPN indx = '|| l_rlpn_ndx);
1903 END IF;
1904
1905 FOR i IN 1..l_rlpn_ndx
1906 LOOP
1907 IF (l_debug = 1) THEN
1908 trace(' For l_rcv_lpn_table (' || i ||')'||'.lpn_id = '|| l_rcv_lpn_table(i).lpn_id);
1909 trace(' For l_rcv_lpn_table (' || i ||')'||'.purchase_order =' ||l_rcv_lpn_table(i).purchase_order);
1910 trace(' For l_rcv_lpn_table (' || i ||')'||'.subinventory =' ||l_rcv_lpn_table(i).subinventory);
1911 trace(' For l_rcv_lpn_table (' || i ||')'||'.locator_id =' ||l_rcv_lpn_table(i).locator_id);
1912 trace(' For l_rcv_lpn_table (' || i ||')'||'.due_date =' ||l_rcv_lpn_table(i).due_date);
1913 trace(' For l_rcv_lpn_table (' || i ||')'||'.truck_num =' ||l_rcv_lpn_table(i).truck_num);
1914 trace(' For l_rcv_lpn_table (' || i ||')'||'.country_of_origin =' ||l_rcv_lpn_table(i).country_of_origin);
1915 trace(' For l_rcv_lpn_table (' || i ||')'||'.comments =' ||l_rcv_lpn_table(i).comments);
1916 trace(' For l_rcv_lpn_table (' || i ||')'||'.po_line_num =' ||l_rcv_lpn_table(i).po_line_num);
1917 trace(' For l_rcv_lpn_table (' || i ||')'||'.quantity_ordered =' ||l_rcv_lpn_table(i).quantity_ordered);
1918 trace(' For l_rcv_lpn_table (' || i ||')'||'.supplier_part_number =' ||l_rcv_lpn_table(i).supplier_part_number);
1919 trace(' For l_rcv_lpn_table (' || i ||')'||'.vendor_id =' ||l_rcv_lpn_table(i).vendor_id);
1920 trace(' For l_rcv_lpn_table (' || i ||')'||'.vendor_site_id =' ||l_rcv_lpn_table(i).vendor_site_id);
1921 trace(' For l_rcv_lpn_table (' || i ||')'||'.supplier_site =' ||l_rcv_lpn_table(i).supplier_site);
1922 trace(' For l_rcv_lpn_table (' || i ||')'||'.supplier_name =' ||l_rcv_lpn_table(i).supplier_name);
1923 trace(' For l_rcv_lpn_table (' || i ||')'||'.requestor =' ||l_rcv_lpn_table(i).requestor);
1924 trace(' For l_rcv_lpn_table (' || i ||')'||'.deliver_to_location =' ||l_rcv_lpn_table(i).deliver_to_location);
1925 trace(' For l_rcv_lpn_table (' || i ||')'||'.location =' ||l_rcv_lpn_table(i).location);
1926 trace(' For l_rcv_lpn_table (' || i ||')'||'.note_to_receiver =' ||l_rcv_lpn_table(i).note_to_receiver);
1927 trace(' For l_rcv_lpn_table (' || i ||')'||'.receipt_num =' ||l_rcv_lpn_table(l_rlpn_ndx).receipt_num);
1928 END IF;
1929 END LOOP;
1930
1931 IF l_lpn_id IS NULL AND l_rlpn_ndx = 0 THEN
1932 trace('l_lpn_id IS NULL AND l_rlpn_ndx = 0 ');
1933 l_lpn_id := l_lpn_table(1);
1934 IF (l_debug = 1) THEN
1935 trace('l_lpn_id = ' || l_lpn_id);
1936 END IF;
1937 -- Added for Bug 3581021 by joabraha
1938 ELSIF l_lpn_id IS NULL AND l_patch_level = 1 AND l_rlpn_ndx <> 0 THEN
1939 IF (l_debug = 1) THEN
1940 trace('Within Else l_lpn_id IS NULL AND l_patch_level = 1 AND l_rlpn_ndx <> 0');
1941 END IF;
1942 /* l_lpn_id := l_rcv_lpn_table(l_rlpn_ndx).lpn_id; */
1943 l_lpn_id := l_rcv_lpn_table(1).lpn_id;
1944 l_cur_item_id := l_rcv_lpn_table(1).item_id;
1945 IF (l_debug = 1) THEN
1946 trace('l_lpn_id = ' || l_lpn_id);
1947 END IF;
1948 --
1949 END IF;
1950 l_lpn_table_index :=0;
1951
1952 IF (l_debug = 1) THEN
1953 trace('Past the newly added else clause');
1954 END IF;
1955
1956 -- If labelAPI called for RCV flows with new architecture, then
1957 -- l_rlpn_ndx will be set. If so, then override earlier algorithms
1958 if ( l_rlpn_ndx <> 0 ) then
1959 trace('l_rlpn_ndx <> 0 ' || l_rlpn_ndx);
1960 l_lpn_id := l_rcv_lpn_table(1).lpn_id;
1961 end if;
1962
1963 l_content_item_data := '';
1964 l_label_index := 1;
1965
1966 IF (l_debug = 1) THEN
1967 trace('Manual Format='||p_label_type_info.manual_format_id||','
1968 ||p_label_type_info.manual_format_name
1969 ||',Manual Printer='||p_label_type_info.manual_printer);
1970 END IF;
1971 l_prev_format_id := p_label_type_info.default_format_id;
1972
1973 IF (l_debug = 1) THEN
1974 trace('Before entering the While loop');
1975 trace('lpn_id=' ||l_lpn_id ||' package id=' || l_package_id ||
1976 ' organization_id=' || p_organization_id||' inventory_item_id=' || p_inventory_item_id||
1977 ' revision=' || p_revision ||' lot=' || p_lot_number||
1978 ' quantity=' || p_qty||' uom=' || p_uom);
1979 trace('cartonization flag=' || cartonization_flag||' header id=' || l_header_id||' Packaging Mode=' || l_packaging_mode);
1980 END IF;
1981
1982 l_lpn_table_index := l_lpn_table_index + 1; -- Bug 3229533
1983
1984 WHILE l_lpn_id IS NOT NULL OR l_package_id IS NOT NULL LOOP
1985 IF (l_debug = 1) THEN
1986 trace(' calling Summary loop, lpn=' || l_lpn_id || ' package_id=' || l_package_id);
1987 trace(' for: lpn_id='||l_lpn_id||', l_cur_item='||l_cur_item_id||',ndx='||l_lpn_table_index);
1988 END IF;
1989
1990 -- Fix for bug: 4179593 <Begin>
1991 l_custom_sql_ret_status := FND_API.G_RET_STS_SUCCESS;
1992 -- Fix for bug: 4179593 <End>
1993
1994 -- Fix for bug: 4179593 Start
1995 l_CustSqlWarnFlagSet := FALSE;
1996 l_CustSqlErrFlagSet := FALSE;
1997 l_CustSqlWarnMsg := NULL;
1998 l_CustSqlErrMsg := NULL;
1999 -- Fix for bug: 4179593 End
2000
2001 -- Bug 4238729, 10+ CU2 bug
2002 -- Reset l_epc for each LPN
2003 l_epc := null;
2004
2005 -- Bug 4137707, performance of printing at cartonization
2006 -- Open seperate cursor for cartonization and non-cartonization flow
2007 -- FOR v_lpn_content IN c_lpn_item_content(l_lpn_id, l_package_id, l_cur_item_id) LOOP
2008 v_lpn_content := NULL;
2009 IF cartonization_flag = 0 THEN
2010 -- non cartonization flow
2011 OPEN c_lpn_item_content(l_lpn_id, l_cur_item_id);
2012 FETCH c_lpn_item_content INTO v_lpn_content;
2013 IF c_lpn_item_content%NOTFOUND THEN
2014 IF (l_debug = 1) THEN
2015 trace('No record found for c_lpn_item_content');
2016 --Moved the following statement outside the if block.
2017 -- as a part of a fix for Bug: -- Fix for 4351366
2018 --CLOSE c_lpn_item_content;
2019 END IF;
2020 -- Fix for 4351366 Start.
2021 CLOSE c_lpn_item_content;
2022 -- Fix for 4351366 end.
2023 END IF;
2024 ELSE
2025 -- cartonization flow
2026 OPEN c_lpn_item_content_cart(l_lpn_id, l_package_id);
2027 FETCH c_lpn_item_content_cart INTO v_lpn_content;
2028 IF c_lpn_item_content_cart%NOTFOUND THEN
2029 IF (l_debug = 1) THEN
2030 trace('No record found for c_lpn_item_content_cart');
2031 --Moved the following statement outside the if block.
2032 -- as a part of a fix for Bug: -- Fix for 4351366
2033 --CLOSE c_lpn_item_content_cart;
2034 END IF;
2035 -- Fix for 4351366 Start.
2036 CLOSE c_lpn_item_content_cart;
2037 -- Fix for 4351366 end.
2038 END IF;
2039 END IF;
2040
2041 WHILE v_lpn_content.organization_id IS NOT NULL LOOP
2042
2043 l_content_rec_index := l_content_rec_index + 1;
2044 row_index_per_label := row_index_per_label + 1;
2045 IF (l_debug = 1) THEN
2046 trace('Item=' || v_lpn_content.inventory_item_id || ' Qty=' || v_lpn_content.quantity);
2047 trace('organization= ' || v_lpn_content.organization_id);
2048 trace('revision= ' || v_lpn_content.revision);
2049 trace('lot number= '|| v_lpn_content.lot_number);
2050 trace('quantity= ' ||v_lpn_content.quantity);
2051 trace('uom= ' || v_lpn_content.uom);
2052 trace('cost group id= '|| v_lpn_content.cost_group_id);
2053 trace('cost group= ' || v_lpn_content.cost_group);
2054 trace('subinventory_code= '|| v_lpn_content.subinventory_code);
2055 trace('location id= ' || v_lpn_content.locator_id);
2056 trace('locator= ' || v_lpn_content.locator);
2057 trace('In Loop, record_index= ' || l_content_rec_index || ', row_index_per_label=' ||row_index_per_label);
2058 END IF;
2059
2060 /* Bug# 3739739 */
2061 IF (p_label_type_info.business_flow_code in (7,8,9,11,12,13,14,15,19,20,23,30)) THEN
2062
2063 -- Fix for BUG: 4654102. For the Buss. Flow 15, the UOM and QTY from WLC should
2064 -- be considered and therefore the conversion is not required.
2065 -- Added the AND condition(second part) to the following statement.
2066 /* Added the business flow code 14 in the second condition for the bug # 4860964 */
2067 IF(l_uom <> v_lpn_content.uom AND p_label_type_info.business_flow_code NOT IN (14, 15)) THEN
2068 --Transaction UOM is different from Primary UOM
2069 --Get the transaction quantity from the primary quantity
2070 l_qty :=
2071 inv_convert.inv_um_convert ( v_lpn_content.inventory_item_id,
2072 6,
2073 v_lpn_content.quantity,
2074 v_lpn_content.uom,
2075 l_uom,
2076 NULL,
2077 NULL
2078 );
2079 v_lpn_content.quantity := l_qty;
2080 v_lpn_content.uom := l_uom;
2081 END IF;
2082 END IF;
2083 /* End of Bug# 3739739 */
2084
2085
2086 -- Fetch LPN information
2087 OPEN c_lpn_attributes(v_lpn_content.organization_id , l_lpn_id);
2088 FETCH c_lpn_attributes INTO l_lpn_info;
2089 CLOSE c_lpn_attributes;
2090
2091 -- Fetch Item information
2092 OPEN c_item_attributes(v_lpn_content.organization_id,
2093 v_lpn_content.inventory_item_id,
2094 v_lpn_content.lot_number);
2095 FETCH c_item_attributes INTO l_item_info;
2096 CLOSE c_item_attributes;
2097
2098 /* The following code has been added for bug # 4998201 */
2099
2100 IF (p_label_type_info.business_flow_code IN (1,2,3,4)) THEN
2101 OPEN c_cost_group(l_lpn_id
2102 , v_lpn_content.inventory_item_id
2103 , v_lpn_content.lot_number);
2104 FETCH c_cost_group INTO l_cost_group_id
2105 , l_cost_group;
2106 IF c_cost_group%NOTFOUND THEN
2107 IF (l_debug = 1) THEN
2108 trace ('No records returned by c_cost_group cursor');
2109 END IF;
2110 END IF;
2111 CLOSE c_cost_group;
2112
2113 v_lpn_content.cost_group_id := nvl(v_lpn_content.cost_group_id, l_cost_group_id);
2114 v_lpn_content.cost_group := nvl(v_lpn_content.cost_group, l_cost_group);
2115
2116 IF (l_debug = 1) THEN
2117 trace('v_lpn_content.cost_group is ' || v_lpn_content.cost_group);
2118 END IF;
2119 END IF;
2120 -- End of fix for bug # 4998201
2121
2122 -- added by fabdi
2123 IF (l_item_info.origination_type IS NOT NULL)
2124 THEN
2125 OPEN c_origination_type (l_item_info.origination_type);
2126 FETCH c_origination_type INTO l_origination_type;
2127 CLOSE c_origination_type;
2128 END IF;
2129 --lpn status project start
2130 IF(inv_cache.set_org_rec(v_lpn_content.organization_id))THEN
2131 IF((inv_cache.org_rec.default_status_id) IS NOT NULL)THEN
2132 l_onhand_status_enabled := 1;
2133 IF (l_debug = 1) THEN
2134 trace('Org is onhand status enabled');
2135 END IF;
2136 Else
2137 l_onhand_status_enabled := 0;
2138 END IF;
2139 END IF;
2140 IF (l_onhand_status_enabled = 1) THEN
2141 l_item_info.lot_number_status := NULL;
2142 IF (l_debug = 1) THEN
2143 trace('going to get_txn_lpn_status');
2144 END IF;
2145 l_material_status_code := INV_LABEL.get_txn_lpn_status(p_lpn_id=>l_lpn_id,
2146 p_transaction_id => p_transaction_id,
2147 p_organization_id =>v_lpn_content.organization_id ,
2148 p_business_flow =>p_label_type_info.business_flow_code);
2149 END IF;
2150
2151 --lpn status project
2152
2153 -- Since it is a multi-record format,
2154 -- it will not apply different format for each record
2155 -- because they are in the same label
2156 IF l_content_rec_index = 1 OR (new_label) THEN -- Bug 3229533
2157
2158
2159 IF (l_debug = 1) THEN
2160 trace(' Going to apply rule engine to get label format with printer: ' || l_printer);
2161 END IF;
2162 /* Bug 3229533 */
2163 IF p_label_type_info.manual_format_id IS NOT NULL THEN
2164 l_label_format_id := p_label_type_info.manual_format_id;
2165 l_label_format := p_label_type_info.manual_format_name;
2166 ELSE
2167 l_label_format_id := null;
2168 l_label_format := null;
2169 END IF;
2170 /* Bug 3229533 */
2171 INV_LABEL.GET_FORMAT_WITH_RULE
2172 ( p_document_id =>p_label_type_info.label_type_id,
2173 p_label_format_id =>p_label_type_info.manual_format_id,
2174 p_organization_id =>v_lpn_content.organization_id,
2175 p_inventory_item_id =>v_lpn_content.inventory_item_id,
2176 p_subinventory_code =>v_lpn_content.subinventory_code,
2177 p_locator_id =>v_lpn_content.locator_id,
2178 p_lpn_id =>l_lpn_id,
2179 P_LOT_NUMBER =>v_lpn_content.lot_number,
2180 P_REVISION =>v_lpn_content.revision,
2181 P_BUSINESS_FLOW_CODE => p_label_type_info.business_flow_code,
2182 P_PACKAGE_ID => l_package_id,
2183 --P_PRINTER_NAME =>l_printer, Blocked in R12
2184 -- Added for Bug 2748297 Start
2185 P_SUPPLIER_ID => l_vendor_id,
2186 P_SUPPLIER_SITE_ID => l_vendor_site_id,
2187 -- End
2188 P_LAST_UPDATE_DATE =>sysdate,
2189 P_LAST_UPDATED_BY =>FND_GLOBAL.user_id,
2190 P_CREATION_DATE =>sysdate,
2191 P_CREATED_BY =>FND_GLOBAL.user_id,
2192
2193 x_return_status =>l_return_status,
2194 x_label_format_id =>l_label_format_id,
2195 x_label_format =>l_label_format,
2196 x_label_request_id =>l_label_request_id);
2197
2198 IF l_return_status <> 'S' THEN
2199 IF (l_debug = 1) THEN
2200 trace(' Error in applying rules engine, setting as default');
2201 END IF;
2202 /* Bug 3229533 */
2203 IF l_content_rec_index = 1 THEN
2204 l_label_format := p_label_type_info.default_format_name;
2205 l_label_format_id := p_label_type_info.default_format_id;
2206 ELSIF (new_label) THEN
2207 l_label_format_id := l_prev_format_id;
2208 END IF;
2209 END IF;
2210
2211 /* Bug 3229533 */
2212 /*IF p_label_type_info.manual_format_id IS NOT NULL THEN
2213 l_label_format_id := p_label_type_info.manual_format_id;
2214 l_label_format := p_label_type_info.manual_format_name;
2215 END IF; */
2216
2217 l_prev_format_id := l_label_format_id;
2218
2219 IF l_debug =1 THEN
2220 trace('Label format after calling rules engine, l_label_format_id='||l_label_format_id||',l_label_format='||l_label_format);
2221 END IF;
2222
2223
2224 --R12: RFID Compliance: Moved this call to after calling the Rules Engine
2225 IF p_label_type_info.manual_printer IS NULL THEN
2226 IF (nvl(l_printer_sub,v_lpn_content.subinventory_code) IS NOT NULL) THEN
2227 IF (l_debug = 1) THEN
2228 trace('getting printer with sub '||nvl(l_printer_sub,v_lpn_content.subinventory_code));
2229 -- null;
2230 END IF;
2231
2232 BEGIN
2233 WSH_REPORT_PRINTERS_PVT.get_printer
2234 (p_concurrent_program_id=>p_label_type_info.label_type_id,
2235 p_user_id =>fnd_global.user_id,
2236 p_responsibility_id =>fnd_global.resp_id,
2237 p_application_id =>fnd_global.resp_appl_id,
2238 p_organization_id =>v_lpn_content.organization_id,
2239 p_zone =>nvl(l_printer_sub,v_lpn_content.subinventory_code),
2240 p_format_id =>l_label_format_id, --added in R12
2241 x_printer =>l_printer,
2242 x_api_status =>l_api_status,
2243 x_error_message =>l_error_message);
2244
2245 IF l_api_status <> 'S' THEN
2246 IF (l_debug = 1) THEN
2247 trace('Error in GET_PRINTER '||l_error_message);
2248 END IF;
2249 l_printer := p_label_type_info.default_printer;
2250 END IF;
2251 EXCEPTION
2252 WHEN others THEN
2253 l_printer := p_label_type_info.default_printer;
2254 END;
2255 END IF;
2256 ELSE
2257 l_printer := p_label_type_info.manual_printer;
2258 END IF;
2259
2260
2261 IF (l_debug = 1) THEN
2262 trace(' Getting selected fields for label_format_id :'||l_label_format_id);
2263 END IF;
2264 INV_LABEL.get_variables_for_format
2265 (
2266 x_variables => l_selected_fields
2267 , x_variables_count => l_selected_fields_count
2268 , x_is_variable_exist => l_is_epc_exist
2269 , p_format_id => l_label_format_id
2270 , p_exist_variable_name => 'EPC');
2271
2272 IF (l_selected_fields_count=0) OR (l_selected_fields.count =0 ) THEN
2273 IF (l_debug = 1) THEN
2274 trace('no fields defined for this format: ' || l_label_format_id || ',' ||l_label_format);
2275 END IF;
2276
2277 GOTO nextlabel; --Added in R12
2278
2279 END IF;
2280
2281 IF (l_debug = 1) THEN
2282 trace(' Found variable defined for this format, cont = ' || l_selected_fields_count);
2283 END IF;
2284
2285 -- Get number of rows per label
2286 BEGIN
2287 select min(table_a.c) into no_of_rows_per_label
2288 from (select wlfv.label_field_id,
2289 wlf.column_name, count(*) c
2290 from wms_label_field_variables wlfv, wms_label_fields_vl wlf
2291 where wlfv.label_field_id = wlf.label_field_id
2292 and wlfv.label_format_id = l_label_format_id
2293 group by wlfv.label_field_id, wlf.column_name
2294 having count(*)>1 ) table_a;
2295 EXCEPTION
2296 WHEN no_data_found THEN
2297 IF (l_debug = 1) THEN
2298 trace(' Did not find defined rows ');
2299 END IF;
2300 END;
2301
2302 IF (no_of_rows_per_label IS NULL) OR (no_of_rows_per_label=0) THEN
2303 no_of_rows_per_label :=1 ;
2304 END IF;
2305
2306
2307 build_format_fields_structure (l_label_format_id);
2308
2309 -- Added for 11.5.10+ RFID compliance project
2310 -- Get RFID/EPC related information for a format
2311 -- Only do this if EPC is a field included in the format
2312
2313 -- Bug 4238729, 10+ CU2
2314 -- Move this section into l_content_rec_index = 1 cause only need to do it once when get new format
2315 -- Generate EPC once for each LPN
2316 IF l_is_epc_exist = 'Y' THEN
2317 IF (l_debug =1) THEN
2318 trace('EPC is a field included in the format, getting RFID/EPC related information from format');
2319 END IF;
2320 BEGIN
2321
2322 -- Modified in R12 -- changed spec WMS_EPC_PVT.generate_epc()
2323 -- Added for 11.5.10+ RFID Compliance project
2324 -- New field : EPC
2325 -- When generate_epc API returns E (expected error) or U(expected error),
2326 -- it sets the error message, but generate xml with EPC as null
2327
2328 -- Bug 4238729, 10+ CU2 bug
2329 -- Only need to call EPC generation once for each LPN
2330 -- Added new parameter p_business_flow_code
2331 IF l_epc IS NULL THEN
2332 IF (l_debug = 1) THEN
2333 trace('l_epc is null, calling generate_epc');
2334 END IF;
2335
2336 WMS_EPC_PVT.generate_epc
2337 (p_org_id => v_lpn_content.organization_id,
2338 p_label_type_id => p_label_type_info.label_type_id, -- 5
2339 p_group_id => inv_label.epc_group_id,
2340 p_label_format_id => l_label_format_id,
2341 p_label_request_id => l_label_request_id,
2342 p_business_flow_code => p_label_type_info.business_flow_code,
2343 x_epc => l_epc,
2344 x_return_status => l_epc_ret_status, -- S / E / U
2345 x_return_mesg => l_epc_ret_msg
2346 );
2347
2348 IF (l_debug = 1) THEN
2349 trace('Called generate_epc with ');
2350 trace('p_label_type_id='||p_label_type_info.label_type_id||',p_group_id='||inv_label.epc_group_id);
2351 trace('l_label_request_id='||l_label_request_id||',p_user_id='||fnd_global.user_id);
2352 trace('l_label_format_id='||l_label_format_id||',p_org_id='||v_lpn_content.organization_id);
2353 trace('x_epc='||l_epc);
2354 trace('x_return_status='||l_epc_ret_status);
2355 trace('x_return_mesg='||l_epc_ret_msg);
2356 END IF;
2357 IF l_epc_ret_status = 'S' THEN
2358 -- Success
2359 IF (l_debug = 1) THEN
2360 trace('Succesfully generated EPC '||l_epc);
2361 END IF;
2362 ELSIF l_epc_ret_status = 'U' THEN
2363 -- Unexpected error
2364 l_epc := null;
2365 IF(l_debug = 1) THEN
2366 trace('Got unexpected error from generate_epc, msg='||l_epc_ret_msg);
2367 trace('Set l_epc = null');
2368 END IF;
2369 ELSIF l_epc_ret_status = 'E' THEN
2370 -- Expected error
2371 l_epc := null;
2372 IF(l_debug = 1) THEN
2373 trace('Got expected error from generate_epc, msg='||l_epc_ret_msg);
2374 trace('Set l_epc = null');
2375 END IF;
2376 ELSE
2377 trace('generate_epc returned a status that is not recognized');
2378 END IF;
2379 ELSE -- l_epc is not null
2380 IF (l_debug = 1) THEN
2381 trace('generate_epc returned a status that is not recognized, set epc as null');
2382 l_epc := null;
2383 END IF;
2384 END IF; -- End if l_epc is null
2385
2386 EXCEPTION
2387 WHEN no_data_found THEN
2388 IF(l_debug =1 ) THEN
2389 trace('No format found when retrieving EPC information. Format_id='||l_label_format_id);
2390 END IF;
2391 WHEN others THEN
2392 IF(l_debug =1 ) THEN
2393 trace('Other error when retrieving EPC information. Format_id='||l_label_format_id);
2394 END IF;
2395 END;
2396 ELSE
2397 IF (l_debug =1) THEN
2398 trace('EPC is not a field included in the format');
2399 END IF;
2400 END IF; -- End if l_epc_exists = 'Y'
2401 -- End bug 4238729
2402
2403 END IF; -- IF l_content_rec_index = 1
2404
2405
2406 -- Added for UCC 128 J Bug #3067059
2407 INV_LABEL.is_item_gtin_enabled
2408 (
2409 x_return_status => l_return_status
2410 , x_gtin_enabled => l_gtin_enabled
2411 , x_gtin => l_gtin
2412 , x_gtin_desc => l_gtin_desc
2413 , p_organization_id => v_lpn_content.organization_id
2414 , p_inventory_item_id => v_lpn_content.inventory_item_id
2415 , p_unit_of_measure => v_lpn_content.uom
2416 , p_revision => v_lpn_content.revision);
2417
2418
2419
2420 --trace('Starting assign variables, ');
2421 /* variable header */
2422 IF(new_label) THEN
2423 IF (l_debug = 1) THEN
2424 trace('Inside New Label');
2425 END IF;
2426
2427 l_label_status := INV_LABEL.G_SUCCESS;
2428 l_label_err_msg := NULL;
2429
2430 row_index_per_label := 1;
2431 l_content_item_data := l_content_item_data || LABEL_B;
2432 IF (l_label_format_id IS NOT NULL) AND
2433 (l_label_format_id <> nvl(p_label_type_info.default_format_id,-999)) THEN
2434 l_content_item_data := l_content_item_data || ' _FORMAT="' || l_label_format || '"';
2435 END IF;
2436 IF (l_printer IS NOT NULL) AND
2437 (l_printer <> nvl(p_label_type_info.default_printer, '@@@')) THEN
2438 l_content_item_data := l_content_item_data || ' _PRINTERNAME="'||l_printer ||'"';
2439 END IF;
2440 l_content_item_data := l_content_item_data || TAG_E;
2441
2442 --For each new label, need to call get_format_with_rule to insert a WLR record
2443 -- but passing p_use_rule_engine a 'N'
2444 -- Only do this if it is not the first label
2445 /* Bug 3229533
2446 IF l_content_rec_index <> 1 THEN
2447
2448 INV_LABEL.GET_FORMAT_WITH_RULE
2449 ( p_document_id =>p_label_type_info.label_type_id,
2450 p_label_format_id =>p_label_type_info.manual_format_id,
2451 p_organization_id =>v_lpn_content.organization_id,
2452 p_inventory_item_id =>v_lpn_content.inventory_item_id,
2453 p_subinventory_code =>v_lpn_content.subinventory_code,
2454 p_locator_id =>v_lpn_content.locator_id,
2455 p_lpn_id =>l_lpn_id,
2456 P_LOT_NUMBER =>v_lpn_content.lot_number,
2457 P_REVISION =>v_lpn_content.revision,
2458 P_BUSINESS_FLOW_CODE => p_label_type_info.business_flow_code,
2459 P_PACKAGE_ID => l_package_id,
2460 P_PRINTER_NAME =>l_printer,
2461 P_LAST_UPDATE_DATE =>sysdate,
2462 P_LAST_UPDATED_BY =>FND_GLOBAL.user_id,
2463 P_CREATION_DATE =>sysdate,
2464 P_CREATED_BY =>FND_GLOBAL.user_id,
2465 p_use_rule_engine => 'N',
2466 x_return_status =>l_return_status,
2467 x_label_format_id =>l_label_format_id,
2468 x_label_format =>l_label_format,
2469 x_label_request_id =>l_label_request_id);
2470
2471 IF l_return_status <> 'S' THEN
2472 IF (l_debug = 1) THEN
2473 trace(' Error in applying rules engine, setting as default');
2474 END IF;
2475 END IF;
2476 l_label_format_id := l_prev_format_id;
2477 END IF;*/
2478 new_label := false;
2479 END IF; --new_label
2480
2481 /* Loop for each selected fields, find the columns and write into the XML_content*/
2482
2483 ---------------------------------------------------------------------------------------------
2484 -- Project: 'Custom Labels' (A 11i10+ Project) |
2485 -- Author: Dinesh ([email protected]) |
2486 -- Change Description: |
2487 -- For the column name 'sql_stmt', if the variable name is not null implies that the field |
2488 -- is a Custom SQL. For this variable name, get the corresponding SQL statement using the |
2489 -- function get_sql_for_variable(). Handle the sql appropriately. |
2490 ---------------------------------------------------------------------------------------------
2491 l_count_custom_sql := 0; -- Added for Bug#4179391
2492 Loop -- Added for Bug#4179391
2493 EXIT WHEN l_count_custom_sql >= g_count_custom_sql; -- Added for Bug#4179391
2494 --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
2495 l_variable_name := get_variable_name('sql_stmt', l_count_custom_sql, l_label_format_id); -- Added for Bug#4179391
2496 IF l_variable_name IS NOT NULL THEN
2497 --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
2498 l_sql_stmt := get_sql_for_variable('sql_stmt', l_count_custom_sql, l_label_format_id); -- Added for Bug#4179391
2499 IF (l_sql_stmt IS NOT NULL) THEN
2500 IF (l_debug = 1) THEN
2501 trace('Custom Labels Trace [INVLAP5B.pls]: ------------------------- REPORT BEGIN-------------------------------------');
2502 trace('Custom Labels Trace [INVLAP5B.pls]: FIELD_VARIABLE_NAME : ' || l_variable_name);
2503 trace('Custom Labels Trace [INVLAP5B.pls]: l_sql_stmt BEFORE REQUEST_ID Filter Concatenation: ' || l_sql_stmt);
2504 END IF;
2505 l_sql_stmt := l_sql_stmt || ' AND WLR.LABEL_REQUEST_ID = :REQUEST_ID';
2506 IF (l_debug = 1) THEN
2507 trace('Custom Labels Trace [INVLAP5B.pls]: l_sql_stmt AFTER REQUEST_ID Filter Concatenation: ' || l_sql_stmt);
2508 END IF;
2509 BEGIN
2510 IF (l_debug = 1) THEN
2511 trace('Custom Labels Trace [INVLAP5B.pls]: At Breadcrumb 1');
2512 trace('Custom Labels Trace [INVLAP5B.pls]: LABEL_REQUEST_ID : ' || l_label_request_id);
2513 END IF;
2514 OPEN c_sql_stmt FOR l_sql_stmt using l_label_request_id;
2515 LOOP
2516 FETCH c_sql_stmt INTO l_sql_stmt_result;
2517 EXIT WHEN c_sql_stmt%notfound OR c_sql_stmt%rowcount >=2;
2518 END LOOP;
2519
2520 IF (c_sql_stmt%rowcount=1 AND l_sql_stmt_result IS NULL) THEN
2521 x_return_status := FND_API.G_RET_STS_SUCCESS;
2522 l_custom_sql_ret_status := INV_LABEL.G_WARNING;
2523 fnd_message.set_name('WMS','WMS_CS_NULL_VALUE_RETURNED');
2524 fnd_msg_pub.ADD;
2525 -- Fix for bug: 4179593 Start
2526 --fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false, p_count => x_msg_count, p_data => l_custom_sql_ret_msg);
2527 l_custom_sql_ret_msg := fnd_msg_pub.get(p_msg_index => fnd_msg_pub.g_last, p_encoded => fnd_api.g_false);
2528 l_CustSqlWarnMsg := l_custom_sql_ret_msg;
2529 l_CustSqlWarnFlagSet := TRUE;
2530 -- Fix for bug: 4179593 End
2531 IF (l_debug = 1) THEN
2532 trace('Custom Labels Trace [INVLAP5B.pls]: At Breadcrumb 2');
2533 trace('Custom Labels Trace [INVLAP5B.pls]: l_sql_stmt_result is: ' || l_sql_stmt_result);
2534 trace('Custom Labels Trace [INVLAP5B.pls]: WARNING: NULL value returned by the custom SQL Query.');
2535 trace('Custom Labels Trace [INVLAP5B.pls]: l_custom_sql_ret_status is set to : ' || l_custom_sql_ret_status);
2536 END IF;
2537 ELSIF c_sql_stmt%rowcount=0 THEN
2538 IF (l_debug = 1) THEN
2539 trace('Custom Labels Trace [INVLAP5B.pls]: At Breadcrumb 3');
2540 trace('Custom Labels Trace [INVLAP5B.pls]: WARNING: No row returned by the Custom SQL query');
2541 END IF;
2542 x_return_status := FND_API.G_RET_STS_SUCCESS;
2543 l_custom_sql_ret_status := INV_LABEL.G_WARNING;
2544 fnd_message.set_name('WMS','WMS_CS_NO_DATA_FOUND');
2545 fnd_msg_pub.ADD;
2546 -- Fix for bug: 4179593 Start
2547 --fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false, p_count => x_msg_count, p_data => l_custom_sql_ret_msg);
2548 l_custom_sql_ret_msg := fnd_msg_pub.get(p_msg_index => fnd_msg_pub.g_last, p_encoded => fnd_api.g_false);
2549 l_CustSqlWarnMsg := l_custom_sql_ret_msg;
2550 l_CustSqlWarnFlagSet := TRUE;
2551 -- Fix for bug: 4179593 End
2552 ELSIF c_sql_stmt%rowcount>=2 THEN
2553 IF (l_debug = 1) THEN
2554 trace('Custom Labels Trace [INVLAP5B.pls]: At Breadcrumb 4');
2555 trace('Custom Labels Trace [INVLAP5B.pls]: ERROR: Multiple values returned by the Custom SQL query');
2556 END IF;
2557 x_return_status := FND_API.G_RET_STS_SUCCESS;
2558 l_custom_sql_ret_status := FND_API.G_RET_STS_ERROR;
2559 fnd_message.set_name('WMS','WMS_CS_MULTIPLE_VALUES_RETURN');
2560 fnd_msg_pub.ADD;
2561 -- Fix for bug: 4179593 Start
2562 --fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false, p_count => x_msg_count, p_data => l_custom_sql_ret_msg);
2563 l_custom_sql_ret_msg := fnd_msg_pub.get(p_msg_index => fnd_msg_pub.g_last, p_encoded => fnd_api.g_false);
2564 l_CustSqlErrMsg := l_custom_sql_ret_msg;
2565 l_CustSqlErrFlagSet := TRUE;
2566 -- Fix for bug: 4179593 End
2567 END IF;
2568 IF (c_sql_stmt%ISOPEN) THEN
2569 CLOSE c_sql_stmt;
2570 END IF;
2571 EXCEPTION
2572 WHEN OTHERS THEN
2573 IF (c_sql_stmt%ISOPEN) THEN
2574 CLOSE c_sql_stmt;
2575 END IF;
2576 IF (l_debug = 1) THEN
2577 trace('Custom Labels Trace [INVLAP5B.pls]: At Breadcrumb 5');
2578 trace('Custom Labels Trace [INVLAP5B.pls]: Unexpected Error has occured in GET_VARIABLES_DATA');
2579 END IF;
2580 x_return_status := FND_API.G_RET_STS_ERROR;
2581 fnd_message.set_name('WMS','WMS_CS_WRONG_SQL_CONSTRUCT');
2582 fnd_msg_pub.ADD;
2583 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false, p_count => x_msg_count, p_data => x_msg_data);
2584 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2585 END;
2586 IF (l_debug = 1) THEN
2587 trace('Custom Labels Trace [INVLAP5B.pls]: At Breadcrumb 6');
2588 trace('Custom Labels Trace [INVLAP5B.pls]: Before assigning it to l_content_item_data');
2589 END IF;
2590 l_content_item_data := l_content_item_data
2591 || variable_b
2592 || l_variable_name
2593 || '">'
2594 || l_sql_stmt_result
2595 || variable_e;
2596 l_sql_stmt_result := NULL;
2597 l_sql_stmt := NULL;
2598 IF (l_debug = 1) THEN
2599 trace('Custom Labels Trace [INVLAP5B.pls]: At Breadcrumb 7');
2600 trace('Custom Labels Trace [INVLAP5B.pls]: After assigning it to l_content_item_data');
2601 trace('Custom Labels Trace [INVLAP5B.pls]: --------------------------REPORT END-------------------------------------');
2602 END IF;
2603 END IF;
2604 END IF;
2605 l_count_custom_sql := l_count_custom_sql + 1; -- Added for Bug#4179391
2606 END LOOP; -- Added for Bug#4179391
2607 ------------------------End of this change for Custom Labels project code--------------------
2608
2609 l_variable_name := get_variable_name('current_date', row_index_per_label-1, l_label_format_id);
2610 IF l_variable_name IS NOT NULL THEN
2611 l_content_item_data := l_content_item_data || VARIABLE_B ||
2612 l_variable_name || '">' || INV_LABEL.G_DATE || VARIABLE_E;
2613 END IF;
2614 l_variable_name := get_variable_name('current_time', row_index_per_label-1, l_label_format_id);
2615 IF l_variable_name IS NOT NULL THEN
2616 l_content_item_data := l_content_item_data || VARIABLE_B ||
2617 l_variable_name || '">' || INV_LABEL.G_TIME || VARIABLE_E;
2618 END IF;
2619 l_variable_name := get_variable_name('request_user', row_index_per_label-1, l_label_format_id);
2620 IF l_variable_name IS NOT NULL THEN
2621 l_content_item_data := l_content_item_data || VARIABLE_B ||
2622 l_variable_name || '">' || INV_LABEL.G_USER || VARIABLE_E;
2623 END IF;
2624 l_variable_name := get_variable_name('lpn', row_index_per_label-1, l_label_format_id);
2625 IF l_variable_name IS NOT NULL THEN
2626 l_content_item_data := l_content_item_data || VARIABLE_B ||
2627 l_variable_name || '">' || l_lpn_info.lpn || VARIABLE_E;
2628 END IF;
2629 l_variable_name := get_variable_name('package_id', row_index_per_label-1, l_label_format_id);
2630 IF l_variable_name IS NOT NULL THEN
2631 l_content_item_data := l_content_item_data || VARIABLE_B ||
2632 l_variable_name || '">' || l_package_id || VARIABLE_E;
2633 END IF;
2634 l_variable_name := get_variable_name('organization', row_index_per_label-1, l_label_format_id);
2635 IF l_variable_name IS NOT NULL THEN
2636 l_content_item_data := l_content_item_data || VARIABLE_B ||
2637 l_variable_name || '">' || l_item_info.organization || VARIABLE_E;
2638 END IF;
2639
2640 l_variable_name := get_variable_name('subinventory_code', row_index_per_label-1, l_label_format_id);
2641 IF l_variable_name IS NOT NULL THEN
2642 l_content_item_data := l_content_item_data || VARIABLE_B ||
2643 l_variable_name || '">' || v_lpn_content.subinventory_code || VARIABLE_E;
2644 --null;
2645 END IF;
2646 l_variable_name := get_variable_name('locator', row_index_per_label-1, l_label_format_id);
2647 IF l_variable_name IS NOT NULL THEN
2648 l_content_item_data := l_content_item_data || VARIABLE_B ||
2649 l_variable_name || '">' || v_lpn_content.locator || VARIABLE_E;
2650 --null;
2651 END IF;
2652
2653 l_variable_name := get_variable_name('item', row_index_per_label-1, l_label_format_id);
2654 IF l_variable_name IS NOT NULL THEN
2655 l_content_item_data := l_content_item_data || VARIABLE_B ||
2656 l_variable_name || '">' || l_item_info.item || VARIABLE_E;
2657 END IF;
2658 l_variable_name := get_variable_name('item_description', row_index_per_label-1, l_label_format_id);
2659 IF l_variable_name IS NOT NULL THEN
2660 l_content_item_data := l_content_item_data || VARIABLE_B ||
2661 l_variable_name || '">' || l_item_info.item_description || VARIABLE_E;
2662 END IF;
2663 l_variable_name := get_variable_name('lot_number', row_index_per_label-1, l_label_format_id);
2664 IF l_variable_name IS NOT NULL THEN
2665 l_content_item_data := l_content_item_data || VARIABLE_B ||
2666 l_variable_name || '">' || v_lpn_content.lot_number || VARIABLE_E;
2667 END IF;
2668 l_variable_name := get_variable_name('quantity', row_index_per_label-1, l_label_format_id);
2669 IF l_variable_name IS NOT NULL THEN
2670 l_content_item_data := l_content_item_data || VARIABLE_B ||
2671 l_variable_name || '">' || v_lpn_content.quantity || VARIABLE_E;
2672 END IF;
2673 l_variable_name := get_variable_name('volume', row_index_per_label-1, l_label_format_id);
2674 IF l_variable_name IS NOT NULL THEN
2675 l_content_item_data := l_content_item_data || VARIABLE_B ||
2676 l_variable_name || '">' || l_lpn_info.volume || VARIABLE_E;
2677 END IF;
2678 l_variable_name := get_variable_name('volume_uom', row_index_per_label-1, l_label_format_id);
2679 IF l_variable_name IS NOT NULL THEN
2680 l_content_item_data := l_content_item_data || VARIABLE_B ||
2681 l_variable_name || '">' || l_lpn_info.volume_uom || VARIABLE_E;
2682 END IF;
2683 l_variable_name := get_variable_name('gross_weight', row_index_per_label-1, l_label_format_id);
2684 IF l_variable_name IS NOT NULL THEN
2685 l_content_item_data := l_content_item_data || VARIABLE_B ||
2686 l_variable_name || '">' || l_lpn_info.gross_weight || VARIABLE_E;
2687 END IF;
2688 l_variable_name := get_variable_name('gross_weight_uom', row_index_per_label-1, l_label_format_id);
2689 IF l_variable_name IS NOT NULL THEN
2690 l_content_item_data := l_content_item_data || VARIABLE_B ||
2691 l_variable_name || '">' || l_lpn_info.gross_weight_uom || VARIABLE_E;
2692 END IF;
2693 l_variable_name := get_variable_name('tare_weight', row_index_per_label-1, l_label_format_id);
2694 IF l_variable_name IS NOT NULL THEN
2695 l_content_item_data := l_content_item_data || VARIABLE_B ||
2696 l_variable_name || '">' || l_lpn_info.tare_weight || VARIABLE_E;
2697 END IF;
2698 l_variable_name := get_variable_name('tare_weight_uom', row_index_per_label-1, l_label_format_id);
2699 IF l_variable_name IS NOT NULL THEN
2700 l_content_item_data := l_content_item_data || VARIABLE_B ||
2701 l_variable_name || '">' || l_lpn_info.tare_weight_uom || VARIABLE_E;
2702 END IF;
2703 l_variable_name := get_variable_name('container_item', row_index_per_label-1, l_label_format_id);
2704 IF l_variable_name IS NOT NULL THEN
2705 l_content_item_data := l_content_item_data || VARIABLE_B ||
2706 l_variable_name || '">' || l_lpn_info.container_item || VARIABLE_E;
2707 END IF;
2708 l_variable_name := get_variable_name('revision', row_index_per_label-1, l_label_format_id);
2709 IF l_variable_name IS NOT NULL THEN
2710 l_content_item_data := l_content_item_data || VARIABLE_B ||
2711 l_variable_name || '">' || v_lpn_content.revision || VARIABLE_E;
2712 END IF;
2713 l_variable_name := get_variable_name('lot_number_status', row_index_per_label-1, l_label_format_id);
2714 IF l_variable_name IS NOT NULL THEN
2715 l_content_item_data := l_content_item_data || VARIABLE_B ||
2716 l_variable_name || '">' || l_item_info.lot_number_status || VARIABLE_E;
2717 END IF;
2718 l_variable_name := get_variable_name('lot_expiration_date', row_index_per_label-1, l_label_format_id);
2719 IF l_variable_name IS NOT NULL THEN
2720 l_content_item_data := l_content_item_data || VARIABLE_B ||
2721 l_variable_name || '">' || l_item_info.lot_expiration_date || VARIABLE_E;
2722 END IF;
2723 l_variable_name := get_variable_name('uom', row_index_per_label-1, l_label_format_id);
2724 IF l_variable_name IS NOT NULL THEN
2725 l_content_item_data := l_content_item_data || VARIABLE_B ||
2726 l_variable_name || '">' || v_lpn_content.uom || VARIABLE_E;
2727 --null;
2728 END IF;
2729 l_variable_name := get_variable_name('cost_group', row_index_per_label-1, l_label_format_id);
2730 IF l_variable_name IS NOT NULL THEN
2731 l_content_item_data := l_content_item_data || VARIABLE_B ||
2732 l_variable_name || '">' || v_lpn_content.cost_group || VARIABLE_E;
2733 --null;
2734 END IF;
2735 l_variable_name := get_variable_name('item_hazard_class', row_index_per_label-1, l_label_format_id);
2736 IF l_variable_name IS NOT NULL THEN
2737 l_content_item_data := l_content_item_data || VARIABLE_B ||
2738 l_variable_name || '">' || l_item_info.item_hazard_class || VARIABLE_E;
2739 END IF;
2740 l_variable_name := get_variable_name('po_num', row_index_per_label-1, l_label_format_id);
2741 IF l_variable_name IS NOT NULL THEN
2742 if ( l_rlpn_ndx <> 0 ) then -- :J-DEV
2743 l_content_item_data := l_content_item_data || VARIABLE_B || l_variable_name
2744 || '">' || l_rcv_lpn_table(l_lpn_table_index).purchase_order|| VARIABLE_E;
2745 else
2746 l_content_item_data := l_content_item_data || VARIABLE_B ||
2747 l_variable_name || '">' || l_purchase_order || VARIABLE_E;
2748 end if;
2749 END IF;
2750 l_variable_name := get_variable_name('item_attribute_category', row_index_per_label-1, l_label_format_id);
2751 IF l_variable_name IS NOT NULL THEN
2752 l_content_item_data := l_content_item_data || VARIABLE_B ||
2753 l_variable_name || '">' || l_item_info.item_attribute_category || VARIABLE_E;
2754 END IF;
2755 l_variable_name := get_variable_name('item_attribute1', row_index_per_label-1, l_label_format_id);
2756 IF l_variable_name IS NOT NULL THEN
2757 l_content_item_data := l_content_item_data || VARIABLE_B ||
2758 l_variable_name || '">' || l_item_info.item_attribute1 || VARIABLE_E;
2759 END IF;
2760 l_variable_name := get_variable_name('item_attribute2', row_index_per_label-1, l_label_format_id);
2761 IF l_variable_name IS NOT NULL THEN
2762 l_content_item_data := l_content_item_data || VARIABLE_B ||
2763 l_variable_name || '">' || l_item_info.item_attribute2 || VARIABLE_E;
2764 END IF;
2765 l_variable_name := get_variable_name('item_attribute3', row_index_per_label-1, l_label_format_id);
2766 IF l_variable_name IS NOT NULL THEN
2767 l_content_item_data := l_content_item_data || VARIABLE_B ||
2768 l_variable_name || '">' || l_item_info.item_attribute3 || VARIABLE_E;
2769 END IF;
2770 l_variable_name := get_variable_name('item_attribute4', row_index_per_label-1, l_label_format_id);
2771 IF l_variable_name IS NOT NULL THEN
2772 l_content_item_data := l_content_item_data || VARIABLE_B ||
2773 l_variable_name || '">' || l_item_info.item_attribute4 || VARIABLE_E;
2774 END IF;
2775 l_variable_name := get_variable_name('item_attribute5', row_index_per_label-1, l_label_format_id);
2776 IF l_variable_name IS NOT NULL THEN
2777 l_content_item_data := l_content_item_data || VARIABLE_B ||
2778 l_variable_name || '">' || l_item_info.item_attribute5 || VARIABLE_E;
2779 END IF;
2780 l_variable_name := get_variable_name('item_attribute6', row_index_per_label-1, l_label_format_id);
2781 IF l_variable_name IS NOT NULL THEN
2782 l_content_item_data := l_content_item_data || VARIABLE_B ||
2783 l_variable_name || '">' || l_item_info.item_attribute6 || VARIABLE_E;
2784 END IF;
2785 l_variable_name := get_variable_name('item_attribute7', row_index_per_label-1, l_label_format_id);
2786 IF l_variable_name IS NOT NULL THEN
2787 l_content_item_data := l_content_item_data || VARIABLE_B ||
2788 l_variable_name || '">' || l_item_info.item_attribute7 || VARIABLE_E;
2789 END IF;
2790 l_variable_name := get_variable_name('item_attribute8', row_index_per_label-1, l_label_format_id);
2791 IF l_variable_name IS NOT NULL THEN
2792 l_content_item_data := l_content_item_data || VARIABLE_B ||
2793 l_variable_name || '">' || l_item_info.item_attribute8 || VARIABLE_E;
2794 END IF;
2795 l_variable_name := get_variable_name('item_attribute9', row_index_per_label-1, l_label_format_id);
2796 IF l_variable_name IS NOT NULL THEN
2797 l_content_item_data := l_content_item_data || VARIABLE_B ||
2798 l_variable_name || '">' || l_item_info.item_attribute9 || VARIABLE_E;
2799 END IF;
2800 l_variable_name := get_variable_name('item_attribute10', row_index_per_label-1, l_label_format_id);
2801 IF l_variable_name IS NOT NULL THEN
2802 l_content_item_data := l_content_item_data || VARIABLE_B ||
2803 l_variable_name || '">' || l_item_info.item_attribute10 || VARIABLE_E;
2804 END IF;
2805 l_variable_name := get_variable_name('item_attribute11', row_index_per_label-1, l_label_format_id);
2806 IF l_variable_name IS NOT NULL THEN
2807 l_content_item_data := l_content_item_data || VARIABLE_B ||
2808 l_variable_name || '">' || l_item_info.item_attribute11 || VARIABLE_E;
2809 END IF;
2810 l_variable_name := get_variable_name('item_attribute12', row_index_per_label-1, l_label_format_id);
2811 IF l_variable_name IS NOT NULL THEN
2812 l_content_item_data := l_content_item_data || VARIABLE_B ||
2813 l_variable_name || '">' || l_item_info.item_attribute12 || VARIABLE_E;
2814 END IF;
2815 l_variable_name := get_variable_name('item_attribute13', row_index_per_label-1, l_label_format_id);
2816 IF l_variable_name IS NOT NULL THEN
2817 l_content_item_data := l_content_item_data || VARIABLE_B ||
2818 l_variable_name || '">' || l_item_info.item_attribute13 || VARIABLE_E;
2819 END IF;
2820 l_variable_name := get_variable_name('item_attribute14', row_index_per_label-1, l_label_format_id);
2821 IF l_variable_name IS NOT NULL THEN
2822 l_content_item_data := l_content_item_data || VARIABLE_B ||
2823 l_variable_name || '">' || l_item_info.item_attribute14 || VARIABLE_E;
2824 END IF;
2825 l_variable_name := get_variable_name('item_attribute15', row_index_per_label-1, l_label_format_id);
2826 IF l_variable_name IS NOT NULL THEN
2827 l_content_item_data := l_content_item_data || VARIABLE_B ||
2828 l_variable_name || '">' || l_item_info.item_attribute15 || VARIABLE_E;
2829 END IF;
2830 l_variable_name := get_variable_name('lpn_attribute_category', row_index_per_label-1, l_label_format_id);
2831 IF l_variable_name IS NOT NULL THEN
2832 l_content_item_data := l_content_item_data || VARIABLE_B ||
2833 l_variable_name || '">' || l_lpn_info.lpn_attribute_category || VARIABLE_E;
2834 END IF;
2835 l_variable_name := get_variable_name('lpn_attribute1', row_index_per_label-1, l_label_format_id);
2836 IF l_variable_name IS NOT NULL THEN
2837 l_content_item_data := l_content_item_data || VARIABLE_B ||
2838 l_variable_name || '">' || l_lpn_info.lpn_attribute1 || VARIABLE_E;
2839 END IF;
2840 l_variable_name := get_variable_name('lpn_attribute2', row_index_per_label-1, l_label_format_id);
2841 IF l_variable_name IS NOT NULL THEN
2842 l_content_item_data := l_content_item_data || VARIABLE_B ||
2843 l_variable_name || '">' || l_lpn_info.lpn_attribute2 || VARIABLE_E;
2844 END IF;
2845 l_variable_name := get_variable_name('lpn_attribute3', row_index_per_label-1, l_label_format_id);
2846 IF l_variable_name IS NOT NULL THEN
2847 l_content_item_data := l_content_item_data || VARIABLE_B ||
2848 l_variable_name || '">' || l_lpn_info.lpn_attribute3 || VARIABLE_E;
2849 END IF;
2850 l_variable_name := get_variable_name('lpn_attribute4', row_index_per_label-1, l_label_format_id);
2851 IF l_variable_name IS NOT NULL THEN
2852 l_content_item_data := l_content_item_data || VARIABLE_B ||
2853 l_variable_name || '">' || l_lpn_info.lpn_attribute4 || VARIABLE_E;
2854 END IF;
2855 l_variable_name := get_variable_name('lpn_attribute5', row_index_per_label-1, l_label_format_id);
2856 IF l_variable_name IS NOT NULL THEN
2857 l_content_item_data := l_content_item_data || VARIABLE_B ||
2858 l_variable_name || '">' || l_lpn_info.lpn_attribute5 || VARIABLE_E;
2859 END IF;
2860 l_variable_name := get_variable_name('lpn_attribute6', row_index_per_label-1, l_label_format_id);
2861 IF l_variable_name IS NOT NULL THEN
2862 l_content_item_data := l_content_item_data || VARIABLE_B ||
2863 l_variable_name || '">' || l_lpn_info.lpn_attribute6 || VARIABLE_E;
2864 END IF;
2865 l_variable_name := get_variable_name('lpn_attribute7', row_index_per_label-1, l_label_format_id);
2866 IF l_variable_name IS NOT NULL THEN
2867 l_content_item_data := l_content_item_data || VARIABLE_B ||
2868 l_variable_name || '">' || l_lpn_info.lpn_attribute7 || VARIABLE_E;
2869 END IF;
2870 l_variable_name := get_variable_name('lpn_attribute8', row_index_per_label-1, l_label_format_id);
2871 IF l_variable_name IS NOT NULL THEN
2872 l_content_item_data := l_content_item_data || VARIABLE_B ||
2873 l_variable_name || '">' || l_lpn_info.lpn_attribute8 || VARIABLE_E;
2874 END IF;
2875 l_variable_name := get_variable_name('lpn_attribute9', row_index_per_label-1, l_label_format_id);
2876 IF l_variable_name IS NOT NULL THEN
2877 l_content_item_data := l_content_item_data || VARIABLE_B ||
2878 l_variable_name || '">' || l_lpn_info.lpn_attribute9 || VARIABLE_E;
2879 END IF;
2880 l_variable_name := get_variable_name('lpn_attribute10', row_index_per_label-1, l_label_format_id);
2881 IF l_variable_name IS NOT NULL THEN
2882 l_content_item_data := l_content_item_data || VARIABLE_B ||
2883 l_variable_name || '">' || l_lpn_info.lpn_attribute10 || VARIABLE_E;
2884 END IF;
2885 l_variable_name := get_variable_name('lpn_attribute11', row_index_per_label-1, l_label_format_id);
2886 IF l_variable_name IS NOT NULL THEN
2887 l_content_item_data := l_content_item_data || VARIABLE_B ||
2888 l_variable_name || '">' || l_lpn_info.lpn_attribute11 || VARIABLE_E;
2889 END IF;
2890 l_variable_name := get_variable_name('lpn_attribute12', row_index_per_label-1, l_label_format_id);
2891 IF l_variable_name IS NOT NULL THEN
2892 l_content_item_data := l_content_item_data || VARIABLE_B ||
2893 l_variable_name || '">' || l_lpn_info.lpn_attribute12 || VARIABLE_E;
2894 END IF;
2895 l_variable_name := get_variable_name('lpn_attribute13', row_index_per_label-1, l_label_format_id);
2896 IF l_variable_name IS NOT NULL THEN
2897 l_content_item_data := l_content_item_data || VARIABLE_B ||
2898 l_variable_name || '">' || l_lpn_info.lpn_attribute13 || VARIABLE_E;
2899 END IF;
2900 l_variable_name := get_variable_name('lpn_attribute14', row_index_per_label-1, l_label_format_id);
2901 IF l_variable_name IS NOT NULL THEN
2902 l_content_item_data := l_content_item_data || VARIABLE_B ||
2903 l_variable_name || '">' || l_lpn_info.lpn_attribute14 || VARIABLE_E;
2904 END IF;
2905 l_variable_name := get_variable_name('lpn_attribute15', row_index_per_label-1, l_label_format_id);
2906 IF l_variable_name IS NOT NULL THEN
2907 l_content_item_data := l_content_item_data || VARIABLE_B ||
2908 l_variable_name || '">' || l_lpn_info.lpn_attribute15 || VARIABLE_E;
2909 END IF;
2910 l_variable_name := get_variable_name('lot_attribute_category', row_index_per_label-1, l_label_format_id);
2911 IF l_variable_name IS NOT NULL THEN
2912 l_content_item_data := l_content_item_data || VARIABLE_B ||
2913 l_variable_name || '">' || l_item_info.lot_attribute_category || VARIABLE_E;
2914 END IF;
2915 l_variable_name := get_variable_name('lot_c_attribute1', row_index_per_label-1, l_label_format_id);
2916 IF l_variable_name IS NOT NULL THEN
2917 l_content_item_data := l_content_item_data || VARIABLE_B ||
2918 l_variable_name || '">' || l_item_info.lot_c_attribute1 || VARIABLE_E;
2919 END IF;
2920 l_variable_name := get_variable_name('lot_c_attribute2', row_index_per_label-1, l_label_format_id);
2921 IF l_variable_name IS NOT NULL THEN
2922 l_content_item_data := l_content_item_data || VARIABLE_B ||
2923 l_variable_name || '">' || l_item_info.lot_c_attribute2 || VARIABLE_E;
2924 END IF;
2925 l_variable_name := get_variable_name('lot_c_attribute3', row_index_per_label-1, l_label_format_id);
2926 IF l_variable_name IS NOT NULL THEN
2927 l_content_item_data := l_content_item_data || VARIABLE_B ||
2928 l_variable_name || '">' || l_item_info.lot_c_attribute3 || VARIABLE_E;
2929 END IF;
2930 l_variable_name := get_variable_name('lot_c_attribute4', row_index_per_label-1, l_label_format_id);
2931 IF l_variable_name IS NOT NULL THEN
2932 l_content_item_data := l_content_item_data || VARIABLE_B ||
2933 l_variable_name || '">' || l_item_info.lot_c_attribute4 || VARIABLE_E;
2934 END IF;
2935 l_variable_name := get_variable_name('lot_c_attribute5', row_index_per_label-1, l_label_format_id);
2936 IF l_variable_name IS NOT NULL THEN
2937 l_content_item_data := l_content_item_data || VARIABLE_B ||
2938 l_variable_name || '">' || l_item_info.lot_c_attribute5 || VARIABLE_E;
2939 END IF;
2940 l_variable_name := get_variable_name('lot_c_attribute6', row_index_per_label-1, l_label_format_id);
2941 IF l_variable_name IS NOT NULL THEN
2942 l_content_item_data := l_content_item_data || VARIABLE_B ||
2943 l_variable_name || '">' || l_item_info.lot_c_attribute6 || VARIABLE_E;
2944 END IF;
2945 l_variable_name := get_variable_name('lot_c_attribute7', row_index_per_label-1, l_label_format_id);
2946 IF l_variable_name IS NOT NULL THEN
2947 l_content_item_data := l_content_item_data || VARIABLE_B ||
2948 l_variable_name || '">' || l_item_info.lot_c_attribute7 || VARIABLE_E;
2949 END IF;
2950 l_variable_name := get_variable_name('lot_c_attribute8', row_index_per_label-1, l_label_format_id);
2951 IF l_variable_name IS NOT NULL THEN
2952 l_content_item_data := l_content_item_data || VARIABLE_B ||
2953 l_variable_name || '">' || l_item_info.lot_c_attribute8 || VARIABLE_E;
2954 END IF;
2955 l_variable_name := get_variable_name('lot_c_attribute9', row_index_per_label-1, l_label_format_id);
2956 IF l_variable_name IS NOT NULL THEN
2957 l_content_item_data := l_content_item_data || VARIABLE_B ||
2958 l_variable_name || '">' || l_item_info.lot_c_attribute9 || VARIABLE_E;
2959 END IF;
2960 l_variable_name := get_variable_name('lot_c_attribute10', row_index_per_label-1, l_label_format_id);
2961 IF l_variable_name IS NOT NULL THEN
2962 l_content_item_data := l_content_item_data || VARIABLE_B ||
2963 l_variable_name || '">' || l_item_info.lot_c_attribute10 || VARIABLE_E;
2964 END IF;
2965 l_variable_name := get_variable_name('lot_c_attribute11', row_index_per_label-1, l_label_format_id);
2966 IF l_variable_name IS NOT NULL THEN
2967 l_content_item_data := l_content_item_data || VARIABLE_B ||
2968 l_variable_name || '">' || l_item_info.lot_c_attribute11 || VARIABLE_E;
2969 END IF;
2970 l_variable_name := get_variable_name('lot_c_attribute12', row_index_per_label-1, l_label_format_id);
2971 IF l_variable_name IS NOT NULL THEN
2972 l_content_item_data := l_content_item_data || VARIABLE_B ||
2973 l_variable_name || '">' || l_item_info.lot_c_attribute12 || VARIABLE_E;
2974 END IF;
2975 l_variable_name := get_variable_name('lot_c_attribute13', row_index_per_label-1, l_label_format_id);
2976 IF l_variable_name IS NOT NULL THEN
2977 l_content_item_data := l_content_item_data || VARIABLE_B ||
2978 l_variable_name || '">' || l_item_info.lot_c_attribute13 || VARIABLE_E;
2979 END IF;
2980 l_variable_name := get_variable_name('lot_c_attribute14', row_index_per_label-1, l_label_format_id);
2981 IF l_variable_name IS NOT NULL THEN
2982 l_content_item_data := l_content_item_data || VARIABLE_B ||
2983 l_variable_name || '">' || l_item_info.lot_c_attribute14 || VARIABLE_E;
2984 END IF;
2985 l_variable_name := get_variable_name('lot_c_attribute15', row_index_per_label-1, l_label_format_id);
2986 IF l_variable_name IS NOT NULL THEN
2987 l_content_item_data := l_content_item_data || VARIABLE_B ||
2988 l_variable_name || '">' || l_item_info.lot_c_attribute15 || VARIABLE_E;
2989 END IF;
2990 l_variable_name := get_variable_name('lot_c_attribute16', row_index_per_label-1, l_label_format_id);
2991 IF l_variable_name IS NOT NULL THEN
2992 l_content_item_data := l_content_item_data || VARIABLE_B ||
2993 l_variable_name || '">' || l_item_info.lot_c_attribute16 || VARIABLE_E;
2994 END IF;
2995 l_variable_name := get_variable_name('lot_c_attribute17', row_index_per_label-1, l_label_format_id);
2996 IF l_variable_name IS NOT NULL THEN
2997 l_content_item_data := l_content_item_data || VARIABLE_B ||
2998 l_variable_name || '">' || l_item_info.lot_c_attribute17 || VARIABLE_E;
2999 END IF;
3000 l_variable_name := get_variable_name('lot_c_attribute18', row_index_per_label-1, l_label_format_id);
3001 IF l_variable_name IS NOT NULL THEN
3002 l_content_item_data := l_content_item_data || VARIABLE_B ||
3003 l_variable_name || '">' || l_item_info.lot_c_attribute18 || VARIABLE_E;
3004 END IF;
3005 l_variable_name := get_variable_name('lot_c_attribute19', row_index_per_label-1, l_label_format_id);
3006 IF l_variable_name IS NOT NULL THEN
3007 l_content_item_data := l_content_item_data || VARIABLE_B ||
3008 l_variable_name || '">' || l_item_info.lot_c_attribute19 || VARIABLE_E;
3009 END IF;
3010 l_variable_name := get_variable_name('lot_c_attribute20', row_index_per_label-1, l_label_format_id);
3011 IF l_variable_name IS NOT NULL THEN
3012 l_content_item_data := l_content_item_data || VARIABLE_B ||
3013 l_variable_name || '">' || l_item_info.lot_c_attribute20 || VARIABLE_E;
3014 END IF;
3015 l_variable_name := get_variable_name('lot_d_attribute1', row_index_per_label-1, l_label_format_id);
3016 IF l_variable_name IS NOT NULL THEN
3017 l_content_item_data := l_content_item_data || VARIABLE_B ||
3018 l_variable_name || '">' || l_item_info.lot_d_attribute1 || VARIABLE_E;
3019 END IF;
3020 l_variable_name := get_variable_name('lot_d_attribute2', row_index_per_label-1, l_label_format_id);
3021 IF l_variable_name IS NOT NULL THEN
3022 l_content_item_data := l_content_item_data || VARIABLE_B ||
3023 l_variable_name || '">' || l_item_info.lot_d_attribute2 || VARIABLE_E;
3024 END IF;
3025 l_variable_name := get_variable_name('lot_d_attribute3', row_index_per_label-1, l_label_format_id);
3026 IF l_variable_name IS NOT NULL THEN
3027 l_content_item_data := l_content_item_data || VARIABLE_B ||
3028 l_variable_name || '">' || l_item_info.lot_d_attribute3 || VARIABLE_E;
3029 END IF;
3030 l_variable_name := get_variable_name('lot_d_attribute4', row_index_per_label-1, l_label_format_id);
3031 IF l_variable_name IS NOT NULL THEN
3032 l_content_item_data := l_content_item_data || VARIABLE_B ||
3033 l_variable_name || '">' || l_item_info.lot_d_attribute4 || VARIABLE_E;
3034 END IF;
3035 l_variable_name := get_variable_name('lot_d_attribute5', row_index_per_label-1, l_label_format_id);
3036 IF l_variable_name IS NOT NULL THEN
3037 l_content_item_data := l_content_item_data || VARIABLE_B ||
3038 l_variable_name || '">' || l_item_info.lot_d_attribute5 || VARIABLE_E;
3039 END IF;
3040 l_variable_name := get_variable_name('lot_d_attribute6', row_index_per_label-1, l_label_format_id);
3041 IF l_variable_name IS NOT NULL THEN
3042 l_content_item_data := l_content_item_data || VARIABLE_B ||
3043 l_variable_name || '">' || l_item_info.lot_d_attribute6 || VARIABLE_E;
3044 END IF;
3045 l_variable_name := get_variable_name('lot_d_attribute7', row_index_per_label-1, l_label_format_id);
3046 IF l_variable_name IS NOT NULL THEN
3047 l_content_item_data := l_content_item_data || VARIABLE_B ||
3048 l_variable_name || '">' || l_item_info.lot_d_attribute7 || VARIABLE_E;
3049 END IF;
3050 l_variable_name := get_variable_name('lot_d_attribute8', row_index_per_label-1, l_label_format_id);
3051 IF l_variable_name IS NOT NULL THEN
3052 l_content_item_data := l_content_item_data || VARIABLE_B ||
3053 l_variable_name || '">' || l_item_info.lot_d_attribute8 || VARIABLE_E;
3054 END IF;
3055 l_variable_name := get_variable_name('lot_d_attribute9', row_index_per_label-1, l_label_format_id);
3056 IF l_variable_name IS NOT NULL THEN
3057 l_content_item_data := l_content_item_data || VARIABLE_B ||
3058 l_variable_name || '">' || l_item_info.lot_d_attribute9 || VARIABLE_E;
3059 END IF;
3060 l_variable_name := get_variable_name('lot_d_attribute10', row_index_per_label-1, l_label_format_id);
3061 IF l_variable_name IS NOT NULL THEN
3062 l_content_item_data := l_content_item_data || VARIABLE_B ||
3063 l_variable_name || '">' || l_item_info.lot_d_attribute10 || VARIABLE_E;
3064 END IF;
3065 l_variable_name := get_variable_name('lot_n_attribute1', row_index_per_label-1, l_label_format_id);
3066 IF l_variable_name IS NOT NULL THEN
3067 l_content_item_data := l_content_item_data || VARIABLE_B ||
3068 l_variable_name || '">' || l_item_info.lot_n_attribute1 || VARIABLE_E;
3069 END IF;
3070 l_variable_name := get_variable_name('lot_n_attribute2', row_index_per_label-1, l_label_format_id);
3071 IF l_variable_name IS NOT NULL THEN
3072 l_content_item_data := l_content_item_data || VARIABLE_B ||
3073 l_variable_name || '">' || l_item_info.lot_n_attribute2 || VARIABLE_E;
3074 END IF;
3075 l_variable_name := get_variable_name('lot_n_attribute3', row_index_per_label-1, l_label_format_id);
3076 IF l_variable_name IS NOT NULL THEN
3077 l_content_item_data := l_content_item_data || VARIABLE_B ||
3078 l_variable_name || '">' || l_item_info.lot_n_attribute3 || VARIABLE_E;
3079 END IF;
3080 l_variable_name := get_variable_name('lot_n_attribute4', row_index_per_label-1, l_label_format_id);
3081 IF l_variable_name IS NOT NULL THEN
3082 l_content_item_data := l_content_item_data || VARIABLE_B ||
3083 l_variable_name || '">' || l_item_info.lot_n_attribute4 || VARIABLE_E;
3084 END IF;
3085 l_variable_name := get_variable_name('lot_n_attribute5', row_index_per_label-1, l_label_format_id);
3086 IF l_variable_name IS NOT NULL THEN
3087 l_content_item_data := l_content_item_data || VARIABLE_B ||
3088 l_variable_name || '">' || l_item_info.lot_n_attribute5 || VARIABLE_E;
3089 END IF;
3090 l_variable_name := get_variable_name('lot_n_attribute6', row_index_per_label-1, l_label_format_id);
3091 IF l_variable_name IS NOT NULL THEN
3092 l_content_item_data := l_content_item_data || VARIABLE_B ||
3093 l_variable_name || '">' || l_item_info.lot_n_attribute6 || VARIABLE_E;
3094 END IF;
3095 l_variable_name := get_variable_name('lot_n_attribute7', row_index_per_label-1, l_label_format_id);
3096 IF l_variable_name IS NOT NULL THEN
3097 l_content_item_data := l_content_item_data || VARIABLE_B ||
3098 l_variable_name || '">' || l_item_info.lot_n_attribute7 || VARIABLE_E;
3099 END IF;
3100 l_variable_name := get_variable_name('lot_n_attribute8', row_index_per_label-1, l_label_format_id);
3101 IF l_variable_name IS NOT NULL THEN
3102 l_content_item_data := l_content_item_data || VARIABLE_B ||
3103 l_variable_name || '">' || l_item_info.lot_n_attribute8 || VARIABLE_E;
3104 END IF;
3105 l_variable_name := get_variable_name('lot_n_attribute9', row_index_per_label-1, l_label_format_id);
3106 IF l_variable_name IS NOT NULL THEN
3107 l_content_item_data := l_content_item_data || VARIABLE_B ||
3108 l_variable_name || '">' || l_item_info.lot_n_attribute9 || VARIABLE_E;
3109 END IF;
3110 l_variable_name := get_variable_name('lot_n_attribute10', row_index_per_label-1, l_label_format_id);
3111 IF l_variable_name IS NOT NULL THEN
3112 l_content_item_data := l_content_item_data || VARIABLE_B ||
3113 l_variable_name || '">' || l_item_info.lot_n_attribute10 || VARIABLE_E;
3114 END IF;
3115 l_variable_name := get_variable_name('lot_country_of_origin', row_index_per_label-1, l_label_format_id);
3116 IF l_variable_name IS NOT NULL THEN
3117 l_content_item_data := l_content_item_data || VARIABLE_B ||
3118 l_variable_name || '">' || l_item_info.lot_country_of_origin || VARIABLE_E;
3119 END IF;
3120 l_variable_name := get_variable_name('lot_grade_code', row_index_per_label-1, l_label_format_id);
3121 IF l_variable_name IS NOT NULL THEN
3122 l_content_item_data := l_content_item_data || VARIABLE_B ||
3123 l_variable_name || '">' || l_item_info.lot_grade_code || VARIABLE_E;
3124 END IF;
3125 l_variable_name := get_variable_name('lot_origination_date', row_index_per_label-1, l_label_format_id);
3126 IF l_variable_name IS NOT NULL THEN
3127 l_content_item_data := l_content_item_data || VARIABLE_B ||
3128 l_variable_name || '">' || l_item_info.lot_origination_date || VARIABLE_E;
3129 END IF;
3130 l_variable_name := get_variable_name('lot_date_code', row_index_per_label-1, l_label_format_id);
3131 IF l_variable_name IS NOT NULL THEN
3132 l_content_item_data := l_content_item_data || VARIABLE_B ||
3133 l_variable_name || '">' || l_item_info.lot_date_code || VARIABLE_E;
3134 END IF;
3135 l_variable_name := get_variable_name('lot_change_date', row_index_per_label-1, l_label_format_id);
3136 IF l_variable_name IS NOT NULL THEN
3137 l_content_item_data := l_content_item_data || VARIABLE_B ||
3138 l_variable_name || '">' || l_item_info.lot_change_date || VARIABLE_E;
3139 END IF;
3140 l_variable_name := get_variable_name('lot_age', row_index_per_label-1, l_label_format_id);
3141 IF l_variable_name IS NOT NULL THEN
3142 l_content_item_data := l_content_item_data || VARIABLE_B ||
3143 l_variable_name || '">' || l_item_info.lot_age || VARIABLE_E;
3144 END IF;
3145 l_variable_name := get_variable_name('lot_retest_date', row_index_per_label-1, l_label_format_id);
3146 IF l_variable_name IS NOT NULL THEN
3147 l_content_item_data := l_content_item_data || VARIABLE_B ||
3148 l_variable_name || '">' || l_item_info.lot_retest_date || VARIABLE_E;
3149 END IF;
3150 l_variable_name := get_variable_name('lot_maturity_date', row_index_per_label-1, l_label_format_id);
3151 IF l_variable_name IS NOT NULL THEN
3152 l_content_item_data := l_content_item_data || VARIABLE_B ||
3153 l_variable_name || '">' || l_item_info.lot_maturity_date || VARIABLE_E;
3154 END IF;
3155 /******* start of invconv changes ***********/
3156
3157 IF (l_debug = 1) THEN
3158 trace(' invconv setting OPM attributes .. ');
3159 END IF;
3160
3161 l_variable_name := get_variable_name('parent_lot_number', row_index_per_label-1, l_label_format_id);
3162 IF l_variable_name IS NOT NULL THEN
3163 l_content_item_data := l_content_item_data || VARIABLE_B ||
3164 l_variable_name || '">' || l_item_info.parent_lot_number || VARIABLE_E;
3165 END IF;
3166
3167 l_variable_name := get_variable_name('hold_date', row_index_per_label-1, l_label_format_id);
3168 IF l_variable_name IS NOT NULL THEN
3169 l_content_item_data := l_content_item_data || VARIABLE_B ||
3170 l_variable_name || '">' || l_item_info.hold_date || VARIABLE_E;
3171 END IF;
3172
3173 l_variable_name := get_variable_name('expiration_action_date', row_index_per_label-1, l_label_format_id);
3174 IF l_variable_name IS NOT NULL THEN
3175 l_content_item_data := l_content_item_data || VARIABLE_B ||
3176 l_variable_name || '">' || l_item_info.expiration_action_date || VARIABLE_E;
3177 END IF;
3178
3179 l_variable_name := get_variable_name('expiration_action_code', row_index_per_label-1, l_label_format_id);
3180 IF l_variable_name IS NOT NULL THEN
3181 l_content_item_data := l_content_item_data || VARIABLE_B ||
3182 l_variable_name || '">' || l_item_info.expiration_action_code || VARIABLE_E;
3183 END IF;
3184
3185 l_variable_name := get_variable_name('origination_type', row_index_per_label-1, l_label_format_id);
3186 IF l_variable_name IS NOT NULL THEN
3187 l_content_item_data := l_content_item_data || VARIABLE_B ||
3188 l_variable_name || '">' || l_origination_type || VARIABLE_E;
3189 END IF;
3190
3191 l_variable_name := get_variable_name('supplier_lot_number', row_index_per_label-1, l_label_format_id);
3192 IF l_variable_name IS NOT NULL THEN
3193 l_content_item_data := l_content_item_data || VARIABLE_B ||
3194 l_variable_name || '">' || l_item_info.supplier_lot_number || VARIABLE_E;
3195 END IF;
3196
3197
3198 l_variable_name := get_variable_name('secondary_transaction_quantity', row_index_per_label-1, l_label_format_id);
3199 IF l_variable_name IS NOT NULL THEN
3200 l_content_item_data := l_content_item_data || VARIABLE_B ||
3201 l_variable_name || '">' || v_lpn_content.secondary_quantity || VARIABLE_E;
3202 END IF;
3203
3204 l_variable_name := get_variable_name('secondary_uom_code', row_index_per_label-1, l_label_format_id);
3205 IF l_variable_name IS NOT NULL THEN
3206 l_content_item_data := l_content_item_data || VARIABLE_B ||
3207 l_variable_name || '">' || v_lpn_content.secondary_uom || VARIABLE_E;
3208 END IF;
3209
3210
3211 /******* end invconv changes ***************/
3212
3213 l_variable_name := get_variable_name('lot_item_size', row_index_per_label-1, l_label_format_id);
3214 IF l_variable_name IS NOT NULL THEN
3215 l_content_item_data := l_content_item_data || VARIABLE_B ||
3216 l_variable_name || '">' || l_item_info.lot_item_size || VARIABLE_E;
3217 END IF;
3218 l_variable_name := get_variable_name('lot_color', row_index_per_label-1, l_label_format_id);
3219 IF l_variable_name IS NOT NULL THEN
3220 l_content_item_data := l_content_item_data || VARIABLE_B ||
3221 l_variable_name || '">' || l_item_info.lot_color || VARIABLE_E;
3222 END IF;
3223 l_variable_name := get_variable_name('lot_volume', row_index_per_label-1, l_label_format_id);
3224 IF l_variable_name IS NOT NULL THEN
3225 l_content_item_data := l_content_item_data || VARIABLE_B ||
3226 l_variable_name || '">' || l_item_info.lot_volume || VARIABLE_E;
3227 END IF;
3228 l_variable_name := get_variable_name('lot_place_of_origin', row_index_per_label-1, l_label_format_id);
3229 IF l_variable_name IS NOT NULL THEN
3230 l_content_item_data := l_content_item_data || VARIABLE_B ||
3231 l_variable_name || '">' || l_item_info.lot_place_of_origin || VARIABLE_E;
3232 END IF;
3233 l_variable_name := get_variable_name('lot_best_by_date', row_index_per_label-1, l_label_format_id);
3234 IF l_variable_name IS NOT NULL THEN
3235 l_content_item_data := l_content_item_data || VARIABLE_B ||
3236 l_variable_name || '">' || l_item_info.lot_best_by_date || VARIABLE_E;
3237 END IF;
3238 l_variable_name := get_variable_name('lot_length', row_index_per_label-1, l_label_format_id);
3239 IF l_variable_name IS NOT NULL THEN
3240 l_content_item_data := l_content_item_data || VARIABLE_B ||
3241 l_variable_name || '">' || l_item_info.lot_length || VARIABLE_E;
3242 END IF;
3243 l_variable_name := get_variable_name('lot_length_uom', row_index_per_label-1, l_label_format_id);
3244 IF l_variable_name IS NOT NULL THEN
3245 l_content_item_data := l_content_item_data || VARIABLE_B ||
3246 l_variable_name || '">' || l_item_info.lot_length_uom || VARIABLE_E;
3247 END IF;
3248 l_variable_name := get_variable_name('lot_recycled_cont', row_index_per_label-1, l_label_format_id);
3249 IF l_variable_name IS NOT NULL THEN
3250 l_content_item_data := l_content_item_data || VARIABLE_B ||
3251 l_variable_name || '">' || l_item_info.lot_recycled_cont || VARIABLE_E;
3252 END IF;
3253 l_variable_name := get_variable_name('lot_thickness', row_index_per_label-1, l_label_format_id);
3254 IF l_variable_name IS NOT NULL THEN
3255 l_content_item_data := l_content_item_data || VARIABLE_B ||
3256 l_variable_name || '">' || l_item_info.lot_thickness || VARIABLE_E;
3257 END IF;
3258 l_variable_name := get_variable_name('lot_thickness_uom', row_index_per_label-1, l_label_format_id);
3259 IF l_variable_name IS NOT NULL THEN
3260 l_content_item_data := l_content_item_data || VARIABLE_B ||
3261 l_variable_name || '">' || l_item_info.lot_thickness_uom || VARIABLE_E;
3262 END IF;
3263 l_variable_name := get_variable_name('lot_width', row_index_per_label-1, l_label_format_id);
3264 IF l_variable_name IS NOT NULL THEN
3265 l_content_item_data := l_content_item_data || VARIABLE_B ||
3266 l_variable_name || '">' || l_item_info.lot_width || VARIABLE_E;
3267 END IF;
3268 l_variable_name := get_variable_name('lot_width_uom', row_index_per_label-1, l_label_format_id);
3269 IF l_variable_name IS NOT NULL THEN
3270 l_content_item_data := l_content_item_data || VARIABLE_B ||
3271 l_variable_name || '">' || l_item_info.lot_width_uom || VARIABLE_E;
3272 END IF;
3273 l_variable_name := get_variable_name('lot_curl', row_index_per_label-1, l_label_format_id);
3274 IF l_variable_name IS NOT NULL THEN
3275 l_content_item_data := l_content_item_data || VARIABLE_B ||
3276 l_variable_name || '">' || l_item_info.lot_curl || VARIABLE_E;
3277 END IF;
3278 l_variable_name := get_variable_name('lot_vendor', row_index_per_label-1, l_label_format_id);
3279 IF l_variable_name IS NOT NULL THEN
3280 l_content_item_data := l_content_item_data || VARIABLE_B ||
3281 l_variable_name || '">' || l_item_info.lot_vendor || VARIABLE_E;
3282 END IF;
3283 l_variable_name := get_variable_name('parent_lpn', row_index_per_label-1, l_label_format_id);
3284 IF l_variable_name IS NOT NULL THEN
3285 l_content_item_data := l_content_item_data || VARIABLE_B ||
3286 l_variable_name || '">' || l_lpn_info.parent_lpn || VARIABLE_E;
3287 END IF;
3288 l_variable_name := get_variable_name('parent_package_id', row_index_per_label-1, l_label_format_id);
3289 IF l_variable_name IS NOT NULL THEN
3290 l_content_item_data := l_content_item_data || VARIABLE_B ||
3291 l_variable_name || '">' || l_parent_package_id || VARIABLE_E;
3292 END IF;
3293 l_variable_name := get_variable_name('pack_level', row_index_per_label-1, l_label_format_id);
3294 IF l_variable_name IS NOT NULL THEN
3295 l_content_item_data := l_content_item_data || VARIABLE_B ||
3296 l_variable_name || '">' || l_pack_level || VARIABLE_E;
3297 END IF;
3298 l_variable_name := get_variable_name('outermost_lpn', row_index_per_label-1, l_label_format_id);
3299 IF l_variable_name IS NOT NULL THEN
3300 l_content_item_data := l_content_item_data || VARIABLE_B ||
3301 l_variable_name || '">' || l_lpn_info.outermost_lpn || VARIABLE_E;
3302 END IF;
3303
3304 --
3305 l_variable_name := get_variable_name('receipt_num', row_index_per_label-1, l_label_format_id);
3306 IF l_variable_name IS NOT NULL THEN
3307 if ( l_rlpn_ndx <> 0 ) then -- :J-DEV
3308 l_content_item_data := l_content_item_data || VARIABLE_B ||l_variable_name
3309 || '">' || l_rcv_lpn_table(l_lpn_table_index).receipt_num || VARIABLE_E;
3310 else
3311 l_content_item_data := l_content_item_data || VARIABLE_B ||
3312 l_variable_name || '">' || l_receipt_number || VARIABLE_E;
3313 end if;
3314 END IF;
3315 l_variable_name := get_variable_name('po_line_num', row_index_per_label-1, l_label_format_id);
3316 IF l_variable_name IS NOT NULL THEN
3317 if ( l_rlpn_ndx <> 0 ) then -- :J-DEV
3318 l_content_item_data := l_content_item_data || VARIABLE_B || l_variable_name
3319 || '">' ||l_rcv_lpn_table(l_lpn_table_index).po_line_num || VARIABLE_E;
3320 else
3321 l_content_item_data := l_content_item_data || VARIABLE_B ||
3322 l_variable_name || '">' || l_po_line_number || VARIABLE_E;
3323 end if;
3324 END IF;
3325 l_variable_name := get_variable_name('quan_ordered', row_index_per_label-1, l_label_format_id);
3326 IF l_variable_name IS NOT NULL THEN
3327 if ( l_rlpn_ndx <> 0 ) then -- :J-DEV
3328 l_content_item_data := l_content_item_data || VARIABLE_B||l_variable_name
3329 || '">' ||l_rcv_lpn_table(l_lpn_table_index).quantity_ordered|| VARIABLE_E;
3330 else
3331 l_content_item_data := l_content_item_data || VARIABLE_B ||
3332 l_variable_name || '">' || l_quantity_ordered || VARIABLE_E;
3333 end if;
3334 END IF;
3335 l_variable_name := get_variable_name('supp_part_num', row_index_per_label-1, l_label_format_id);
3336 IF l_variable_name IS NOT NULL THEN
3337 if ( l_rlpn_ndx <> 0 ) then -- :J-DEV
3338 l_content_item_data := l_content_item_data || VARIABLE_B||l_variable_name
3339 || '">' ||l_rcv_lpn_table(l_lpn_table_index).supplier_part_number|| VARIABLE_E;
3340 else
3341 l_content_item_data := l_content_item_data || VARIABLE_B ||
3342 l_variable_name || '">' || l_supplier_part_number || VARIABLE_E;
3343 end if;
3344 END IF;
3345 l_variable_name := get_variable_name('supp_name', row_index_per_label-1, l_label_format_id);
3346 IF l_variable_name IS NOT NULL THEN
3347 if ( l_rlpn_ndx <> 0 ) then -- :J-DEV
3348 l_content_item_data := l_content_item_data || VARIABLE_B||l_variable_name
3349 || '">' ||l_rcv_lpn_table(l_lpn_table_index).supplier_name|| VARIABLE_E;
3350 else
3351 l_content_item_data := l_content_item_data || VARIABLE_B ||
3352 l_variable_name || '">' || l_supplier_name || VARIABLE_E;
3353 end if;
3354 END IF;
3355 l_variable_name := get_variable_name('supp_site', row_index_per_label-1, l_label_format_id);
3356 IF l_variable_name IS NOT NULL THEN
3357 if ( l_rlpn_ndx <> 0 ) then -- :J-DEV
3358 l_content_item_data := l_content_item_data || VARIABLE_B||l_variable_name
3359 || '">' ||l_rcv_lpn_table(l_lpn_table_index).supplier_site|| VARIABLE_E;
3360 else
3361 l_content_item_data := l_content_item_data || VARIABLE_B ||
3362 l_variable_name || '">' || l_supplier_site || VARIABLE_E;
3363 end if;
3364 END IF;
3365 l_variable_name := get_variable_name('requestor', row_index_per_label-1, l_label_format_id);
3366 IF l_variable_name IS NOT NULL THEN
3367 if ( l_rlpn_ndx <> 0 ) then -- :J-DEV
3368 l_content_item_data := l_content_item_data || VARIABLE_B||l_variable_name
3369 || '">' ||l_rcv_lpn_table(l_lpn_table_index).requestor|| VARIABLE_E;
3370 else
3371 l_content_item_data := l_content_item_data || VARIABLE_B ||
3372 l_variable_name || '">' || l_requestor || VARIABLE_E;
3373 end if;
3374 END IF;
3375 l_variable_name := get_variable_name('deliver_to_loc', row_index_per_label-1, l_label_format_id);
3376 IF l_variable_name IS NOT NULL THEN
3377 if ( l_rlpn_ndx <> 0 ) then -- :J-DEV
3378 l_content_item_data := l_content_item_data || VARIABLE_B||l_variable_name
3379 || '">' ||l_rcv_lpn_table(l_lpn_table_index).deliver_to_location|| VARIABLE_E;
3380 else
3381 l_content_item_data := l_content_item_data || VARIABLE_B ||
3382 l_variable_name || '">' || l_deliver_to_location || VARIABLE_E;
3383 end if;
3384 END IF;
3385 l_variable_name := get_variable_name('loc_id', row_index_per_label-1, l_label_format_id);
3386 IF l_variable_name IS NOT NULL THEN
3387 if ( l_rlpn_ndx <> 0 ) then -- :J-DEV
3388 l_content_item_data := l_content_item_data || VARIABLE_B||l_variable_name
3389 || '">' ||l_rcv_lpn_table(l_lpn_table_index).location|| VARIABLE_E;
3390 else
3391 l_content_item_data := l_content_item_data || VARIABLE_B ||
3392 l_variable_name || '">' || l_location_code || VARIABLE_E;
3393 end if;
3394 END IF;
3395 l_variable_name := get_variable_name('note_to_receiver', row_index_per_label-1, l_label_format_id);
3396 IF l_variable_name IS NOT NULL THEN
3397 if ( l_rlpn_ndx <> 0 ) then -- :J-DEV
3398 l_content_item_data := l_content_item_data || VARIABLE_B||l_variable_name
3399 || '">' ||l_rcv_lpn_table(l_lpn_table_index).note_to_receiver|| VARIABLE_E;
3400 else
3401 l_content_item_data := l_content_item_data || VARIABLE_B ||
3402 l_variable_name || '">' || l_note_to_receiver || VARIABLE_E;
3403 end if;
3404 END IF;
3405 l_variable_name := get_variable_name('gtin', row_index_per_label-1, l_label_format_id);
3406 IF l_variable_name IS NOT NULL THEN
3407 l_content_item_data := l_content_item_data || VARIABLE_B ||
3408 l_variable_name || '">' || l_gtin || VARIABLE_E;
3409 END IF;
3410 l_variable_name := get_variable_name('gtin_description', row_index_per_label-1, l_label_format_id);
3411 IF l_variable_name IS NOT NULL THEN
3412 l_content_item_data := l_content_item_data || VARIABLE_B ||
3413 l_variable_name || '">' || l_gtin_desc || VARIABLE_E;
3414 END IF;
3415
3416 -- New fields for iSP : Line-level
3417 l_variable_name := get_variable_name('comments_line', row_index_per_label-1, l_label_format_id);
3418 IF l_variable_name IS NOT NULL THEN -- :J-DEV
3419 if ( l_rlpn_ndx <> 0 ) then
3420 l_content_item_data := l_content_item_data || VARIABLE_B ||
3421 l_variable_name || '">' || l_rcv_lpn_table(l_lpn_table_index).comments || VARIABLE_E;
3422 else
3423 l_content_item_data := l_content_item_data || VARIABLE_B ||
3424 l_variable_name || '">' || VARIABLE_E;
3425 end if;
3426 END IF;
3427 -- New fields for iSP : Line-level
3428 l_variable_name := get_variable_name('packing_slip_line', row_index_per_label-1, l_label_format_id);
3429 IF l_variable_name IS NOT NULL THEN -- :J-DEV
3430 if ( l_rlpn_ndx <> 0 ) then
3431 l_content_item_data := l_content_item_data || VARIABLE_B ||
3432 l_variable_name || '">' || l_rcv_lpn_table(l_lpn_table_index).packing_slip || VARIABLE_E;
3433 else
3434 l_content_item_data := l_content_item_data || VARIABLE_B ||
3435 l_variable_name || '">' || VARIABLE_E;
3436 end if;
3437 END IF;
3438
3439 l_variable_name := get_variable_name('shipment_due_date', row_index_per_label-1, l_label_format_id);
3440 IF l_variable_name IS NOT NULL THEN -- :J-DEV
3441 if ( l_rlpn_ndx <> 0 ) then
3442 l_content_item_data := l_content_item_data || VARIABLE_B ||
3443 l_variable_name || '">' || l_rcv_lpn_table(l_lpn_table_index).due_date || VARIABLE_E;
3444 else
3445 l_content_item_data := l_content_item_data || VARIABLE_B ||
3446 l_variable_name || '">' || VARIABLE_E;
3447 end if;
3448 END IF;
3449
3450 -- New fields for iSP : Header-level
3451 l_variable_name := get_variable_name('asn_number', row_index_per_label-1, l_label_format_id);
3452 IF l_variable_name IS NOT NULL THEN -- :J-DEV
3453 l_content_item_data := l_content_item_data || VARIABLE_B ||
3454 l_variable_name || '">' || l_rcv_isp_header.asn_num || VARIABLE_E;
3455 END IF;
3456 l_variable_name := get_variable_name('shipment_date', row_index_per_label-1, l_label_format_id);
3457 IF l_variable_name IS NOT NULL THEN -- :J-DEV
3458 l_content_item_data := l_content_item_data || VARIABLE_B ||
3459 l_variable_name || '">' || l_rcv_isp_header.shipment_date || VARIABLE_E;
3460 END IF;
3461 l_variable_name := get_variable_name('expct_rcpt_date', row_index_per_label-1, l_label_format_id);
3462 IF l_variable_name IS NOT NULL THEN -- :J-DEV
3463 l_content_item_data := l_content_item_data || VARIABLE_B ||
3464 l_variable_name || '">' || l_rcv_isp_header.expected_receipt_date || VARIABLE_E;
3465 END IF;
3466 l_variable_name := get_variable_name('freight_terms', row_index_per_label-1, l_label_format_id);
3467 IF l_variable_name IS NOT NULL THEN -- :J-DEV
3468 l_content_item_data := l_content_item_data || VARIABLE_B ||
3469 l_variable_name || '">' || l_rcv_isp_header.freight_terms || VARIABLE_E;
3470 END IF;
3471 l_variable_name := get_variable_name('freight_carrier', row_index_per_label-1, l_label_format_id);
3472 IF l_variable_name IS NOT NULL THEN -- :J-DEV
3473 l_content_item_data := l_content_item_data || VARIABLE_B ||
3474 l_variable_name || '">' || l_rcv_isp_header.freight_carrier || VARIABLE_E;
3475 END IF;
3476 l_variable_name := get_variable_name('num_of_containers', row_index_per_label-1, l_label_format_id);
3477 IF l_variable_name IS NOT NULL THEN -- :J-DEV
3478 l_content_item_data := l_content_item_data || VARIABLE_B ||
3479 l_variable_name || '">' || l_rcv_isp_header.num_of_containers || VARIABLE_E;
3480 END IF;
3481 l_variable_name := get_variable_name('bill_of_lading', row_index_per_label-1, l_label_format_id);
3482 IF l_variable_name IS NOT NULL THEN -- :J-DEV
3483 l_content_item_data := l_content_item_data || VARIABLE_B ||
3484 l_variable_name || '">' || l_rcv_isp_header.bill_of_lading || VARIABLE_E;
3485 END IF;
3486 l_variable_name := get_variable_name('waybill_airbill_num', row_index_per_label-1, l_label_format_id);
3487 IF l_variable_name IS NOT NULL THEN -- :J-DEV
3488 l_content_item_data := l_content_item_data || VARIABLE_B ||
3489 l_variable_name || '">' || l_rcv_isp_header.waybill_airbill_num || VARIABLE_E;
3490 END IF;
3491 l_variable_name := get_variable_name('packing_slip_header', row_index_per_label-1, l_label_format_id);
3492 IF l_variable_name IS NOT NULL THEN -- :J-DEV
3493 l_content_item_data := l_content_item_data || VARIABLE_B ||
3494 l_variable_name || '">' || l_rcv_isp_header.packing_slip || VARIABLE_E;
3495 END IF;
3496 l_variable_name := get_variable_name('comments_header', row_index_per_label-1, l_label_format_id);
3497 IF l_variable_name IS NOT NULL THEN -- :J-DEV
3498 l_content_item_data := l_content_item_data || VARIABLE_B ||
3499 l_variable_name || '">' || l_rcv_isp_header.comments || VARIABLE_E;
3500 END IF;
3501 --lpn status project start
3502 l_variable_name := get_variable_name('material_status', row_index_per_label-1, l_label_format_id);
3503 IF l_variable_name IS NOT NULL THEN
3504 l_content_item_data := l_content_item_data || VARIABLE_B ||
3505 l_variable_name || '">' || l_material_status_code || VARIABLE_E;
3506
3507 END IF;
3508 --lpn status project end
3509 l_variable_name := get_variable_name('packaging_code', row_index_per_label-1, l_label_format_id);
3510 IF l_variable_name IS NOT NULL THEN -- :J-DEV
3511 l_content_item_data := l_content_item_data || VARIABLE_B ||
3512 l_variable_name || '">' || l_rcv_isp_header.packaging_code || VARIABLE_E;
3513 END IF;
3514 l_variable_name := get_variable_name('special_handling_code', row_index_per_label-1, l_label_format_id);
3515 IF l_variable_name IS NOT NULL THEN -- :J-DEV
3516 l_content_item_data := l_content_item_data || VARIABLE_B ||
3517 l_variable_name || '">' || l_rcv_isp_header.special_handling_code || VARIABLE_E;
3518 END IF;
3519
3520 -- Added for 11.5.10+ RFID Compliance project
3521 -- New field : EPC
3522 -- EPC is generated once for each LPN
3523 l_variable_name := get_variable_name('epc', row_index_per_label-1, l_label_format_id);
3524 IF l_variable_name IS NOT NULL THEN
3525 l_content_item_data := l_content_item_data || variable_b ||
3526 l_variable_name || '">' || l_epc || variable_e;
3527 l_label_err_msg := l_epc_ret_msg;
3528 IF l_epc_ret_status = 'U' THEN
3529 l_label_status := INV_LABEL.G_ERROR;
3530 ELSIF l_epc_ret_status = 'E' THEN
3531 l_label_status := INV_LABEL.G_WARNING;
3532 END IF;
3533
3534 END IF;
3535
3536 --Bug 4891916. Added for the field Cycle Count Name
3537 l_variable_name := get_variable_name('cycle_count_name', row_index_per_label - 1, l_label_format_id);
3538
3539 IF l_variable_name IS NOT NULL THEN
3540 l_content_item_data := l_content_item_data || variable_b || l_variable_name || '">' || l_cycle_count_name || variable_e;
3541 END IF;
3542
3543 --End of fix for Bug 4891916
3544
3545 IF row_index_per_label = no_of_rows_per_label THEN
3546 -- Finished
3547 l_content_item_data := l_content_item_data || LABEL_E;
3548 x_variable_content(l_label_index).label_content := l_content_item_data;
3549 x_variable_content(l_label_index).label_request_id := l_label_request_id;
3550 x_variable_content(l_label_index).label_status := l_label_status;
3551 x_variable_content(l_label_index).error_message := l_label_err_msg;
3552
3553 ------------------------Start of changes for Custom Labels project code------------------
3554
3555 -- Fix for bug: 4179593 Start
3556 IF (l_CustSqlWarnFlagSet) THEN
3557 l_custom_sql_ret_status := INV_LABEL.G_WARNING;
3558 l_custom_sql_ret_msg := l_CustSqlWarnMsg;
3559 END IF;
3560
3561 IF (l_CustSqlErrFlagSet) THEN
3562 l_custom_sql_ret_status := FND_API.G_RET_STS_ERROR;
3563 l_custom_sql_ret_msg := l_CustSqlErrMsg;
3564 END IF;
3565 -- Fix for bug: 4179593 End
3566
3567 -- We will concatenate the error message from Custom SQL and EPC code.
3568 x_variable_content(l_label_index).error_message := l_custom_sql_ret_msg || ' ' || l_label_err_msg;
3569 IF(l_CustSqlWarnFlagSet OR l_CustSqlErrFlagSet) THEN
3570 x_variable_content(l_label_index).label_status := l_custom_sql_ret_status;
3571 END IF;
3572
3573
3574 l_custom_sql_ret_status := NULL;
3575 l_custom_sql_ret_msg := NULL;
3576 ------------------------End of this changes for Custom Labels project code---------------
3577
3578 l_content_item_data := '';
3579 l_label_index := l_label_index +1;
3580 new_label := true;
3581 END IF;
3582
3583
3584 IF (l_debug = 1) THEN
3585 trace(' Finished writing item variables ');
3586 END IF;
3587
3588 <<nextlabel>> --Added in R12
3589
3590 -- Bug 4137707: performance of printing at cartonization
3591 -- Replaced the FOR LOOP
3592 -- Need to fetch record again for cartonization or non-cartonization flow
3593 IF cartonization_flag = 0 THEN
3594 -- non cartonization flow
3595 FETCH c_lpn_item_content INTO v_lpn_content;
3596 IF c_lpn_item_content%NOTFOUND THEN
3597 IF (l_debug = 1) THEN
3598 trace('No record found for c_lpn_item_content');
3599 --Moved the following 2 statements outside the if block.
3600 -- as a part of a fix for Bug: -- Fix for 4351366
3601 --CLOSE c_lpn_item_content;
3602 --v_lpn_content := null;
3603 END IF;
3604 -- Fix for 4351366 Start.
3605 CLOSE c_lpn_item_content;
3606 v_lpn_content := null;
3607 -- Fix for 4351366 end.
3608 END IF;
3609 ELSE
3610 -- cartonization flow
3611 FETCH c_lpn_item_content_cart INTO v_lpn_content;
3612 IF c_lpn_item_content_cart%NOTFOUND THEN
3613 IF (l_debug = 1) THEN
3614 trace('No record found for c_lpn_item_content_cart');
3615 --Moved the following 2 statements outside the if block.
3616 -- as a part of a fix for Bug: -- Fix for 4351366
3617 --CLOSE c_lpn_item_content_cart;
3618 --v_lpn_content := null;
3619 END IF;
3620 -- Fix for 4351366 Start.
3621 CLOSE c_lpn_item_content_cart;
3622 v_lpn_content := null;
3623 -- Fix for 4351366 end.
3624 END IF;
3625 END IF;
3626
3627
3628 END LOOP; -- v_lpn_content IN c_lpn_item_content
3629
3630
3631
3632 IF p_label_type_info.business_flow_code in (6) THEN
3633 -- Cross-Dock
3634 FETCH c_wdd_lpn INTO l_lpn_id, p_organization_id, l_subinventory_code;
3635 IF c_wdd_lpn%NOTFOUND THEN
3636 IF (l_debug = 1) THEN
3637 trace(' Finished getting more cross-dock');
3638 END IF;
3639 CLOSE c_wdd_lpn;
3640 l_lpn_id := null;
3641 END IF;
3642
3643 ELSIF p_label_type_info.business_flow_code = 22 THEN
3644 IF (l_debug = 1) THEN
3645 trace(' Getting another content for cartonization');
3646 END IF;
3647 FETCH c_mmtt_cart_lpn INTO l_lpn_id, l_package_id, l_content_volume_uom_code, l_content_volume,
3648 l_gross_weight_uom_code, l_gross_weight, l_inventory_item_id, l_parent_package_id, l_pack_level,
3649 l_parent_lpn_id, l_header_id, l_packaging_mode;
3650 IF c_mmtt_cart_lpn%NOTFOUND THEN
3651 IF (l_debug = 1) THEN
3652 trace(' Finished getting containers ' );
3653 END IF;
3654 CLOSE c_mmtt_cart_lpn;
3655 l_lpn_id := null;
3656 l_package_id := null;
3657 ELSE
3658 IF (l_debug = 1) THEN
3659 trace(' Found another container lpn_id=' || l_lpn_id || 'package_id=' || l_package_id);
3660 END IF;
3661 new_label := true;
3662 l_content_rec_index := 0;
3663 END IF;
3664 ELSIF p_label_type_info.business_flow_code = 29 THEN
3665
3666 FETCH c_mmtt_wip_pick_drop_lpn INTO l_lpn_id, p_organization_id,
3667 p_inventory_item_id, p_lot_number,
3668 p_revision, p_qty, p_uom,
3669 l_subinventory_code, l_locator_id,
3670 l_secondary_quantity, l_secondary_uom; -- invconv changes
3671 IF c_mmtt_wip_pick_drop_lpn%NOTFOUND THEN
3672 CLOSE c_mmtt_wip_pick_drop_lpn;
3673 l_lpn_id := null;
3674 ELSE
3675 IF (l_debug = 1) THEN
3676 trace(' Found another lot ' || p_lot_number);
3677 END IF;
3678 END IF;
3679
3680 ELSIF p_label_type_info.business_flow_code in (18,28,34) THEN
3681 FETCH c_mmtt_lpn_pick_load
3682 INTO l_lpn_id, p_organization_id,
3683 p_inventory_item_id, p_lot_number, p_revision,
3684 p_qty, p_uom,l_subinventory_code, l_locator_id, l_printer_sub,
3685 l_secondary_quantity, -- invconv changes
3686 l_secondary_uom; -- invconv changes
3687
3688 IF c_mmtt_lpn_pick_load%NOTFOUND THEN
3689 CLOSE c_mmtt_lpn_pick_load;
3690 l_lpn_id := null;
3691 ELSE
3692 IF (l_debug = 1) THEN
3693 trace(' Found another lot ' || p_lot_number);
3694 END IF;
3695 END IF;
3696
3697 ELSE
3698 -- For RCV flows, check if called based on new Architecture
3699 -- If new architecture, then index corresponding to new RCV_LPN
3700 -- table of records would be greater than 0
3701 IF (l_debug = 1) THEN
3702 trace(' for-end: lpn_id='||l_lpn_id||'item='||l_cur_item_id||'ndx='||l_lpn_table_index||'count='||l_rcv_lpn_table.count);
3703 END IF;
3704
3705 if l_rlpn_ndx > 0 then -- :J-DEV
3706 if (l_lpn_table_index < l_rcv_lpn_table.count) then
3707 l_lpn_table_index := l_lpn_table_index +1;
3708 l_lpn_id := l_rcv_lpn_table(l_lpn_table_index).lpn_id;
3709 l_cur_item_id := l_rcv_lpn_table(l_lpn_table_index).item_id;
3710 new_label := true; -- Bug 3841820, start a new label if found a new lpn
3711 else
3712 l_lpn_id := null;
3713 end if;
3714 else
3715 IF l_lpn_table_index < l_lpn_table.count THEN
3716 l_lpn_table_index := l_lpn_table_index +1;
3717 l_lpn_id := l_lpn_table(l_lpn_table_index);
3718 new_label := true; -- Bug 3841820, start a new label if found a new lpn
3719 ELSE
3720 l_lpn_id := null;
3721 END IF;
3722 end if;
3723 END IF;
3724
3725 IF ((row_index_per_label < no_of_rows_per_label) AND (new_label=TRUE)
3726 AND (l_label_format_id IS NOT NULL)) THEN
3727 -- Label is partial, write null to the rest of the variables.
3728 -- First, get max number of rows defined.
3729 -- It might be greater than the actual number of rows per label
3730 -- For example, the user setup as
3731 -- _ITEM1, _ITEM2 and _QTY1, _QTY2, _QTY3
3732 -- Then the number of rows per label is 2 and max_no_of_rows_defined is 3.
3733 max_no_of_rows_defined := 0;
3734
3735 BEGIN
3736 select max(table_a.c) into max_no_of_rows_defined
3737 from (select wlfv.label_field_id,
3738 wlf.column_name, count(*) c
3739 from wms_label_field_variables wlfv, wms_label_fields_vl wlf
3740 where wlfv.label_field_id = wlf.label_field_id
3741 and wlfv.label_format_id = l_label_format_id
3742 group by wlfv.label_field_id, wlf.column_name) table_a;
3743 EXCEPTION
3744 WHEN no_data_found THEN
3745 IF (l_debug = 1) THEN
3746 trace(' Error in finding max_no_of_rows_defined');
3747 END IF;
3748 END;
3749 IF (l_debug = 1) THEN
3750 trace(' Max number of rows defined = '|| max_no_of_rows_defined);
3751 END IF;
3752
3753 -- Loop for the rest of the rows that don't have value,
3754 -- we need to pass null.
3755 FOR i IN (row_index_per_label+1)..max_no_of_rows_defined LOOP
3756 FOR j IN 1..l_selected_fields.count LOOP
3757 IF j=1 OR l_selected_fields(j).column_name <>
3758 l_selected_fields(j-1).column_name THEN
3759 l_variable_name := get_variable_name(l_selected_fields(j).column_name,
3760 i-1, l_label_format_id);
3761 IF l_variable_name IS NOT NULL THEN
3762 IF (l_debug = 1) THEN
3763 trace(' Found extra row to pass null=> '|| l_variable_name);
3764 END IF;
3765 l_content_item_data := l_content_item_data || VARIABLE_B ||
3766 l_variable_name || '">' ||'' || VARIABLE_E;
3767 END IF;
3768 END IF;
3769 END LOOP;
3770 END LOOP; -- while l_lpn_id IS NOT NULL OR ..
3771 l_content_item_data := l_content_item_data || LABEL_E;
3772 x_variable_content(l_label_index).label_content := l_content_item_data;
3773 x_variable_content(l_label_index).label_request_id := l_label_request_id;
3774 x_variable_content(l_label_index).label_status := l_label_status;
3775 x_variable_content(l_label_index).error_message := l_label_err_msg;
3776
3777 ------------------------Start of changes for Custom Labels project code------------------
3778 -- Fix for bug: 4179593 Start
3779 IF (l_CustSqlWarnFlagSet) THEN
3780 l_custom_sql_ret_status := INV_LABEL.G_WARNING;
3781 l_custom_sql_ret_msg := l_CustSqlWarnMsg;
3782 END IF;
3783
3784 IF (l_CustSqlErrFlagSet) THEN
3785 l_custom_sql_ret_status := FND_API.G_RET_STS_ERROR;
3786 l_custom_sql_ret_msg := l_CustSqlErrMsg;
3787 END IF;
3788 -- Fix for bug: 4179593 End
3789
3790 -- We will concatenate the error message from Custom SQL and EPC code.
3791 x_variable_content(l_label_index).error_message := l_custom_sql_ret_msg || ' ' || l_label_err_msg;
3792 IF(l_CustSqlWarnFlagSet OR l_CustSqlErrFlagSet) THEN
3793 x_variable_content(l_label_index).label_status := l_custom_sql_ret_status;
3794 END IF;
3795 ------------------------End of this changes for Custom Labels project code---------------
3796
3797 l_content_item_data := '';
3798 l_label_index := l_label_index + 1;
3799 END IF;
3800 END LOOP;
3801 IF (l_debug = 1) THEN
3802 trace('End of loop with lpn_id=' || l_lpn_id || 'package_id=' || l_package_id);
3803 END IF;
3804
3805 IF ((row_index_per_label < no_of_rows_per_label) AND (new_label=FALSE)
3806 AND (l_label_format_id IS NOT NULL)) THEN
3807 -- Last label is partial, write null to the rest of the variables.
3808 -- First, get max number of rows defined.
3809 -- It might be greater than the actual number of rows per label
3810 -- For example, the user setup as
3811 -- _ITEM1, _ITEM2 and _QTY1, _QTY2, _QTY3
3812 -- Then the number of rows per label is 2 and max_no_of_rows_defined is 3.
3813 max_no_of_rows_defined := 0;
3814
3815 BEGIN
3816 select max(table_a.c) into max_no_of_rows_defined
3817 from (select wlfv.label_field_id,
3818 wlf.column_name, count(*) c
3819 from wms_label_field_variables wlfv, wms_label_fields_vl wlf
3820 where wlfv.label_field_id = wlf.label_field_id
3821 and wlfv.label_format_id = l_label_format_id
3822 group by wlfv.label_field_id, wlf.column_name) table_a;
3823 EXCEPTION
3824 WHEN no_data_found THEN
3825 IF (l_debug = 1) THEN
3826 trace(' Error in finding max_no_of_rows_defined');
3827 END IF;
3828 END;
3829 IF (l_debug = 1) THEN
3830 trace(' Max number of rows defined = '|| max_no_of_rows_defined);
3831 END IF;
3832
3833 -- Loop for the rest of the rows that don't have value,
3834 -- we need to pass null.
3835 FOR i IN (row_index_per_label+1)..max_no_of_rows_defined LOOP
3836 FOR j IN 1..l_selected_fields.count LOOP
3837 IF j=1 OR l_selected_fields(j).column_name <>
3838 l_selected_fields(j-1).column_name THEN
3839 l_variable_name := get_variable_name(l_selected_fields(j).column_name,
3840 i-1, l_label_format_id);
3841 IF l_variable_name IS NOT NULL THEN
3842 IF (l_debug = 1) THEN
3843 trace(' Found extra row to pass null=> '|| l_variable_name);
3844 END IF;
3845 l_content_item_data := l_content_item_data || VARIABLE_B ||
3846 l_variable_name || '">' ||'' || VARIABLE_E;
3847 END IF;
3848 END IF;
3849 END LOOP;
3850 END LOOP;
3851 l_content_item_data := l_content_item_data || LABEL_E;
3852 x_variable_content(l_label_index).label_content := l_content_item_data;
3853 x_variable_content(l_label_index).label_request_id := l_label_request_id;
3854 x_variable_content(l_label_index).label_status := l_label_status;
3855 x_variable_content(l_label_index).error_message := l_label_err_msg;
3856
3857 ------------------------Start of changes for Custom Labels project code------------------
3858
3859 -- Fix for bug: 4179593 Start
3860 IF (l_CustSqlWarnFlagSet) THEN
3861 l_custom_sql_ret_status := INV_LABEL.G_WARNING;
3862 l_custom_sql_ret_msg := l_CustSqlWarnMsg;
3863 END IF;
3864
3865 IF (l_CustSqlErrFlagSet) THEN
3866 l_custom_sql_ret_status := FND_API.G_RET_STS_ERROR;
3867 l_custom_sql_ret_msg := l_CustSqlErrMsg;
3868 END IF;
3869 -- Fix for bug: 4179593 End
3870
3871 -- We will concatenate the error message from Custom SQL and EPC code.
3872 x_variable_content(l_label_index).error_message := l_custom_sql_ret_msg || ' ' || l_label_err_msg;
3873 IF(l_CustSqlWarnFlagSet OR l_CustSqlErrFlagSet) THEN
3874 x_variable_content(l_label_index).label_status := l_custom_sql_ret_status;
3875 END IF;
3876 ------------------------End of this changes for Custom Labels project code---------------
3877 l_content_item_data := '';
3878 l_label_index := l_label_index + 1;
3879 END IF;
3880
3881 EXCEPTION
3882 WHEN OTHERS THEN
3883 trace(' Error Code, Error Message...' || sqlerrm(sqlcode));
3884 END get_variable_data;
3885
3886
3887
3888
3889
3890 FUNCTION get_variable_name(p_column_name IN VARCHAR2, p_row_index IN NUMBER, p_format_id IN NUMBER)
3891 RETURN VARCHAR2
3892 IS
3893
3894 lv_variable_name VARCHAR2(100);
3895
3896 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
3897 BEGIN
3898
3899 BEGIN
3900 lv_variable_name := g_field_elements_table(get_field_hash_value(p_column_name||(p_row_index+1), g_get_hash_for_retrieve)).variable_name;
3901 EXCEPTION
3902 WHEN OTHERS THEN
3903 lv_variable_name := NULL;
3904 END;
3905 --IF l_variable_name is not null THEN
3906 -- trace('get variable name '||l_variable_name||' for column '|| p_column_name);
3907 --END IF;
3908
3909 RETURN lv_variable_name;
3910
3911 END get_variable_name;
3912
3913 ---------------------------------------------------------------------------------------------
3914 -- Project: 'Custom Labels' (A 11i10+ Project) |
3915 -- Author: Dinesh ([email protected]) |
3916 -- Change Description: |
3917 -- This function get_sql_for_variable() is newly added for the Custom Labels project to |
3918 -- fetch the SQL statement from the PL/SQL table. |
3919 ---------------------------------------------------------------------------------------------
3920 FUNCTION get_sql_for_variable(p_column_name IN VARCHAR2, p_row_index IN NUMBER, p_format_id IN NUMBER)
3921 RETURN VARCHAR2
3922 IS
3923
3924 lv_sql_stmt VARCHAR2(4000);
3925
3926 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
3927 BEGIN
3928
3929 BEGIN
3930 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;
3931 IF (l_debug = 1) THEN
3932 trace(' Inside get_sql_for_variable() lv_sql_stmt is: '|| lv_sql_stmt);
3933 END IF;
3934 EXCEPTION
3935 WHEN OTHERS THEN
3936 lv_sql_stmt := NULL;
3937 IF (l_debug = 1) THEN
3938 trace(' Inside Exception Block of get_sql_for_variable() ');
3939 END IF;
3940 END;
3941 RETURN lv_sql_stmt;
3942
3943 END get_sql_for_variable;
3944
3945 ------------------------End of this change for Custom Labels project code--------------------
3946
3947 PROCEDURE get_variable_data(
3948 x_variable_content OUT NOCOPY LONG
3949 , x_msg_count OUT NOCOPY NUMBER
3950 , x_msg_data OUT NOCOPY VARCHAR2
3951 , x_return_status OUT NOCOPY VARCHAR2
3952 , p_label_type_info IN INV_LABEL.label_type_rec
3953 , p_transaction_id IN NUMBER
3954 , p_input_param IN MTL_MATERIAL_TRANSACTIONS_TEMP%ROWTYPE
3955 , p_lpn_id IN NUMBER
3956 , p_transaction_identifier IN NUMBER
3957 ) IS
3958 l_variable_data_tbl INV_LABEL.label_tbl_type;
3959 BEGIN
3960 get_variable_data(
3961 x_variable_content => l_variable_data_tbl
3962 , x_msg_count => x_msg_count
3963 , x_msg_data => x_msg_data
3964 , x_return_status => x_return_status
3965 , p_label_type_info => p_label_type_info
3966 , p_transaction_id => p_transaction_id
3967 , p_input_param => p_input_param
3968 , p_lpn_id => p_lpn_id
3969 , p_transaction_identifier=> p_transaction_identifier
3970 );
3971
3972 x_variable_content := '';
3973
3974 FOR i IN 1..l_variable_data_tbl.count() LOOP
3975 x_variable_content := x_variable_content || l_variable_data_tbl(i).label_content;
3976 END LOOP;
3977
3978 END get_variable_data;
3979
3980 END INV_LABEL_PVT5;