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