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