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