[Home] [Help]
PACKAGE BODY: APPS.INV_LABEL_PVT2
Source
1 PACKAGE BODY inv_label_pvt2 AS
2 /* $Header: INVLAP2B.pls 120.20.12010000.2 2008/07/29 13:40:38 ptkumar ship $ */
3 label_b CONSTANT VARCHAR2(50) := '<label';
4 label_e CONSTANT VARCHAR2(50) := '</label>' || fnd_global.local_chr(10);
5 variable_b CONSTANT VARCHAR2(50) := '<variable name= "';
6 variable_e CONSTANT VARCHAR2(50) := '</variable>' || fnd_global.local_chr(10);
7 tag_e CONSTANT VARCHAR2(50) := '>' || fnd_global.local_chr(10);
8 l_debug NUMBER;
9 -- Bug 2795525 : This mask is used to mask all date fields.
10 g_date_format_mask VARCHAR2(100) := inv_label.g_date_format_mask;
11 g_header_printed BOOLEAN := FALSE;
12 g_user_name fnd_user.user_name%TYPE := fnd_global.user_name;
13
14 PROCEDURE TRACE(p_message IN VARCHAR2) IS
15 BEGIN
16 IF (g_header_printed = FALSE) THEN
17 inv_label.TRACE('$Header: INVLAP2B.pls 120.20.12010000.2 2008/07/29 13:40:38 ptkumar ship $', 'LABEL_SERIAL');
18 g_header_printed := TRUE;
19 END IF;
20
21 inv_label.TRACE(g_user_name || ': ' || p_message, 'LABEL_SERIAL');
22 END TRACE;
23
24
25 PROCEDURE get_variable_data(
26 x_variable_content OUT NOCOPY inv_label.label_tbl_type
27 , x_msg_count OUT NOCOPY NUMBER
28 , x_msg_data OUT NOCOPY VARCHAR2
29 , x_return_status OUT NOCOPY VARCHAR2
30 , p_label_type_info IN inv_label.label_type_rec
31 , p_transaction_id IN NUMBER
32 , p_input_param IN mtl_material_transactions_temp%ROWTYPE
33 , p_transaction_identifier IN NUMBER
34 ) IS
35 l_receipt_number VARCHAR2(30); -- Added for Bug 2847799
36 l_delivery_detail_id NUMBER;
37 l_project_id NUMBER;
38 l_task_id NUMBER;
39 l_cost_group_id NUMBER;
40 l_inventory_item_id NUMBER;
41 l_organization_id NUMBER;
42 l_lot_number VARCHAR2(240);
43 l_serial_number VARCHAR2(240);
44 l_revision mtl_material_transactions_temp.revision%TYPE;
45 l_subinventory VARCHAR2(30) := NULL;
46 l_project_number VARCHAR (25); -- Fix For Bug: 4907062
47 l_project_name VARCHAR2(240);
48 l_task_number VARCHAR (25); -- Fix For Bug: 4907062
49 l_task_name VARCHAR2(240);
50 l_wip_entity_id NUMBER;
51 -- Added for Bug 2748297
52 l_vendor_id NUMBER;
53 l_vendor_site_id NUMBER;
54 -- Bug 2825748 : Material Label Is Not Printed On WIP Completion.
55 l_uom mtl_material_transactions.transaction_uom%TYPE;
56 l_locator_id NUMBER;
57 -- Added for Bug 4582954
58 l_oe_order_header_id NUMBER;
59 l_oe_order_line_id NUMBER;
60 -- Added for UCC 128 J Bug #3067059
61 l_gtin_enabled BOOLEAN := FALSE;
62 l_gtin VARCHAR2(100);
63 l_gtin_desc VARCHAR2(240);
64 l_quantity_floor NUMBER := 0;
65 l_fm_serial_number VARCHAR2(240);
66 l_to_serial_number VARCHAR2(240);
67
68
69 ---------------------------------------------------------------------------------------------
70 -- Project: 'Custom Labels' (A 11i10+ Project) |
71 -- Author: Dinesh ([email protected]) |
72 -- Change Description: |
73 -- Following variables were added (as a part of 11i10+ 'Custom Labels' Project) |
74 -- to retrieve and hold the SQL Statement and it's result. |
75 ---------------------------------------------------------------------------------------------
76
77 l_sql_stmt VARCHAR2(4000);
78 l_sql_stmt_result VARCHAR2(4000) := NULL;
79 TYPE sql_stmt IS REF CURSOR;
80 c_sql_stmt sql_stmt;
81 l_custom_sql_ret_status VARCHAR2(1);
82 l_custom_sql_ret_msg VARCHAR2(2000);
83
84
85 -- Fix for bug: 4179593 Start
86 l_CustSqlWarnFlagSet BOOLEAN;
87 l_CustSqlErrFlagSet BOOLEAN;
88 l_CustSqlWarnMsg VARCHAR2(2000);
89 l_CustSqlErrMsg VARCHAR2(2000);
90 -- Fix for bug: 4179593 End
91
92 ------------------------End of this change for Custom Labels project code--------------------
93
94 -- added for conergence projact (invconv)
95 l_grade_code mtl_lot_numbers.grade_code%TYPE;
96 l_parent_lot_number mtl_lot_numbers.parent_lot_number%TYPE;
97 l_expiration_action_date mtl_lot_numbers.expiration_action_date%TYPE;
98 l_expiration_action_code mtl_lot_numbers.expiration_action_code%TYPE;
99 l_origination_type mtl_lot_numbers.origination_type%TYPE;
100 l_hold_date mtl_lot_numbers.hold_date%TYPE;
101 l_supplier_lot_number mtl_lot_numbers.supplier_lot_number%TYPE;
102 l_expiration_date mtl_lot_numbers.expiration_date%TYPE;
103
104 l_maturity_date mtl_lot_numbers.maturity_date%TYPE;
105 l_retest_date mtl_lot_numbers.retest_date%TYPE;
106 l_origination_date mtl_lot_numbers.origination_date%TYPE;
107 l_lot_status mtl_material_statuses_vl.status_code%TYPE; -- Bug 4355080
108 -- invconv enf
109
110
111 -- For Receipt, Inspection, Putaway, Delivery,
112 -- the Item/Lot information is obtained like this
113
114 -- 1. For WMS Org, Serial
115 -- --------
116 -- Receipt/Inspection rti+msn(with lpn_id)
117 -- Putaway rti+mtlt+msnt
118 -- Delivery no apply for WMS org
119 -- 2. For INV Org, Serial
120 -- --------
121 -- Receipt/Inspection No serial number
122 -- Putaway no apply for Inv org
123 -- Delivery rti+mtlt+msnt
124 -- Therefore, two cursors are needed, rti+msn or rti+mtlt+msnt
125
126 -- For WMS org, receipt and inspection
127 CURSOR rti_serial_lpn_cur IS
128 SELECT rti.item_id inventory_item_id
129 , rti.to_organization_id organization_id
130 , msn.lot_number lot_number
131 , pol.project_id project_id
132 , pol.task_id task_id
133 , rti.item_revision revision
134 , msn.serial_number serial_number
135 , pha.segment1 purchase_order
136 , rti.subinventory
137 , rti.vendor_id
138 , rti.vendor_site_id
139 , rti.oe_order_header_id --Bug 4582954
140 , rti.oe_order_line_id --Bug 4582954
141 FROM rcv_transactions_interface rti, mtl_serial_numbers msn, po_lines_all pol, wms_lpn_contents wlc, po_headers_all pha
142 WHERE wlc.parent_lpn_id = rti.lpn_id
143 AND pol.po_line_id(+) = rti.po_line_id
144 AND pha.po_header_id(+) = rti.po_header_id
145 AND msn.lpn_id = rti.lpn_id
146 AND NVL(msn.lot_number, '&&&') = NVL(wlc.lot_number, NVL(msn.lot_number, '&&&'))
147 AND msn.inventory_item_id = rti.item_id
148 AND msn.current_organization_id = rti.to_organization_id
149 AND rti.interface_transaction_id = p_transaction_id
150 ORDER BY msn.serial_number;
151
152 /* Patchset J - Earlier serial numbers were only recorded for WMS organizations
153 * using RTI_SERIAL_LPN_CUR by joining RTI to WLC and MTL_SERIAL_NUMBERS(MSN).
154 * Now th Serial numbers are recorded for both WMS and INV organizations in
155 * RCV_SERIALS_INTERFACE table, with the link to RTI, and/or RLI.
156 * The old cursor RTI_SERIAL_LPN_CUR should be replaced by new cursor RTI_SERIAL_CUR
157 */
158 CURSOR rt_serial_cur IS
159 /* Note: records in RT are filtered by transaction_type and business_flow_code
160 * becuase it is possible for label-API to be called multiple times by RCV-TM
161 * in the case of ROI, when multiple trx.types are present in a group
162 */
163 -- Commented as part of fix for bug Bug 3472432. The l_inventory_item_id is required for the serial_cur as an
164 -- input parameter. The item id is being derived from the rcv_shipment_lines because irrespective of the
165 -- transaction type(Receipt or Internal Req), the item id will be populated. For Internal Requisitions, the
166 -- pol.item_id may not be populated. This cursor is common for everything except a deliver transaction . This
167 -- is taken care of in teh second part of the SQL(after the UNION ALL) since for Deliver transactions, the
168 -- rcv_serials_supply may not be populated. This information has been derived from talking to the Inbound
169 -- team.
170 -- SELECT to_number(null) inventory_item_id
171 SELECT rsl.item_id inventory_item_id -- @@@ Bug 3472432
172 , rt.organization_id organization_id
173 , rss.lot_num lot_number
174 --Bug# 3586116 - Get project and task id from rt
175 , rt.project_id
176 , rt.task_id
177 -- , pod.project_id project_id --Commented as part of Bug# 3586116
178 -- , pod.task_id task_id --Commented as part of Bug# 3586116
179 , pol.item_revision revision
180 , rss.serial_num
181 , pha.segment1 purchase_order
182 , rt.subinventory
183 , rt.locator_id
184 , rt.vendor_id
185 , rt.vendor_site_id
186 , rt.uom_code
187 , rt.oe_order_header_id --Bug 4582954
188 , rt.oe_order_line_id --Bug 4582954
189 FROM rcv_transactions rt, rcv_serials_supply rss, po_lines_all pol
190 -- , po_distributions_all pod --Commented as part of Bug# 3586116
191 , po_headers_all pha, rcv_shipment_lines rsl
192 , wms_license_plate_numbers wlpn -- Bug 3836623
193 WHERE rss.transaction_id = rt.transaction_id
194 AND pol.po_line_id(+) = rt.po_line_id
195 AND pha.po_header_id(+) = rt.po_header_id
196 -- AND pod.po_distribution_id(+) = rt.po_distribution_id --Commented as part of Bug# 3586116
197 AND(
198 (rt.transaction_type IN('ACCEPT', 'REJECT')
199 AND p_label_type_info.business_flow_code = 2)
200 OR(rt.transaction_type = 'RECEIVE'
201 --AND rt.routing_header_id <> 3 Modified for Bug: 4312020
202 AND p_label_type_info.business_flow_code = 1)
203 )
204 --AND rsl.shipment_header_id = rt.shipment_header_id -- @@@ Bug 3472432. Takes care of the cartesian product.
205 AND rsl.shipment_line_id = rt.shipment_line_id --Bug# 3516361. Takes care of cartesian product.
206 AND rt.GROUP_ID = p_transaction_id
207 -- Bug 3836623
208 -- Add check for LPN context
209 -- When cross docking happens, label printing are called for both cross docking and putaway
210 -- To prevent duplicate labels
211 -- For putaway business flow, only print if LPN Context is not Picked (11)
212 AND wlpn.lpn_id(+) = rt.lpn_id
213 AND ((rt.lpn_id IS NULL) OR
214 (p_label_type_info.business_flow_code <> 4) OR
215 (p_label_type_info.business_flow_code = 4 AND
216 wlpn.lpn_context <> 11))
217 UNION ALL
218 SELECT rsl.item_id inventory_item_id
219 , rt.organization_id organization_id
220 , mtln.lot_number
221 --Bug# 3586116 - Get project and task id from rt
222 , rt.project_id
223 , rt.task_id
224 -- , pod.project_id project_id --Commented as part of Bug# 3586116
225 -- , pod.task_id task_id --Commented as part of Bug# 3586116
226 , rsl.item_revision revision
227 , mut.serial_number
228 , pha.segment1 purchase_order
229 , rt.subinventory
230 , rt.locator_id
231 , rt.vendor_id
232 , rt.vendor_site_id
233 , rt.uom_code
234 , rt.oe_order_header_id --Bug 4582954
235 , rt.oe_order_line_id --Bug 4582954
236 FROM rcv_transactions rt
237 , mtl_transaction_lot_numbers mtln
238 , mtl_unit_transactions mut
239 -- , po_distributions_all pod --Commented as part of Bug# 3586116
240 , po_lines_all pol
241 , po_headers_all pha
242 , rcv_shipment_lines rsl
243 , wms_license_plate_numbers wlpn -- Bug 3836623
244 -- Bug 4179732, can not print serial number from putaway
245 -- Changed to link to MUT through MMT
246 , mtl_material_transactions mmt
247 WHERE mmt.rcv_transaction_id = rt.transaction_id
248 AND mmt.transaction_id = mtln.transaction_id (+)
249 AND mut.transaction_id = nvl(mtln.serial_transaction_id, mmt.transaction_id)
250 --WHERE mtln.product_transaction_id(+) = rt.transaction_id
251 -- AND mut.product_transaction_id(+) = rt.transaction_id
252 -- AND NVL(mut.transaction_id, -9998) = NVL(mtln.serial_transaction_id, NVL(mut.transaction_id, -9998))
253 -- End of bug 4179732
254 AND pol.po_line_id(+) = rt.po_line_id
255 AND pha.po_header_id(+) = rt.po_header_id
256 --AND pod.po_distribution_id(+) = rt.po_distribution_id --Commented as part of Bug# 3586116
257 AND rt.transaction_type = 'DELIVER'
258 AND (p_label_type_info.business_flow_code IN(3, 4) OR
259 (rt.routing_header_id = 3
260 AND p_label_type_info.business_flow_code = 1)) /* Added for bug # 5219262*/
261 AND rt.GROUP_ID = p_transaction_id
262 AND rsl.shipment_line_id = rt.shipment_line_id
263 -- Bug 3836623
264 -- Add check for LPN context
265 -- When cross docking happens, label printing are called for both cross docking and putaway
266 -- To prevent duplicate labels
267 -- For putaway business flow, only print if LPN Context is not Picked (11)
268 AND wlpn.lpn_id(+) = rt.lpn_id
269 AND ((rt.lpn_id IS NULL) OR
270 (p_label_type_info.business_flow_code <> 4) OR
271 (p_label_type_info.business_flow_code = 4 AND
272 wlpn.lpn_context <> 11))
273
274 ;
275
276 -- For Putaway in WMS org and Delivery in INV org
277 -- If the item is serial/lot control then the link is mtlt.serial_transaction_temp_id = msnt.transaction_temp_id
278 -- If the item is serial only control, then the above link is missing and so the link is from rti.transaction_temp_id to
279 -- msnt.transaction_temp_id
280 -- (delivery)
281 CURSOR rti_serial_msnt_cur IS
282 SELECT rti.item_id inventory_item_id
283 , rti.to_organization_id organization_id
284 , mtlt.lot_number lot_number
285 , pol.project_id project_id
286 , pol.task_id task_id
287 , rti.item_revision revision
288 , msnt.fm_serial_number fm_serial_number
289 , msnt.to_serial_number to_serial_number
290 , pha.segment1 purchase_order
291 , rti.subinventory
292 , rti.vendor_id
293 , rti.vendor_site_id
294 , rti.oe_order_header_id --Bug 4582954
295 , rti.oe_order_line_id --Bug 4582954
296 FROM rcv_transactions_interface rti
297 , mtl_serial_numbers_temp msnt
298 , mtl_transaction_lots_temp mtlt
299 , po_lines_all pol
300 , po_headers_all pha
301 WHERE mtlt.transaction_temp_id(+) = rti.interface_transaction_id
302 AND msnt.transaction_temp_id = NVL(mtlt.serial_transaction_temp_id, rti.interface_transaction_id)
303 AND pol.po_line_id(+) = rti.po_line_id
304 AND pha.po_header_id(+) = rti.po_header_id
305 AND rti.interface_transaction_id = p_transaction_id;
306
307 -- For INV org
308 -- If the item is serial/lot controlled then the link is mtlt.serial_transaction_temp_id = msnt.transaction_temp_id
309 -- If the item is serial controlled, then the above link is missing and so the link is from mmtt.transaction_temp_id to
310 -- msnt.transaction_temp_id
311 -- (Misc/Alias issue/receipt)
312 CURSOR mmtt_serial_cur IS
313 SELECT mmtt.inventory_item_id inventory_item_id
314 , mmtt.organization_id organization_id
315 , mtlt.lot_number lot_number
316 , mmtt.project_id project_id
317 , mmtt.task_id task_id
318 , mmtt.revision revision
319 , msnt.fm_serial_number fm_serial_number
320 , msnt.to_serial_number to_serial_number
321 , mmtt.subinventory_code
322 , mmtt.transaction_uom
323 , mmtt.locator_id /* Added for Bug # 4672471 */
324 FROM mtl_material_transactions_temp mmtt, mtl_transaction_lots_temp mtlt, mtl_serial_numbers_temp msnt
325 WHERE mtlt.transaction_temp_id(+) = mmtt.transaction_temp_id
326 AND msnt.transaction_temp_id = NVL(mtlt.serial_transaction_temp_id, mmtt.transaction_temp_id)
327 AND mmtt.transaction_temp_id = p_transaction_id;
328
329 -- The following cursor has been added for bug # 5245012
330 -- For details about the changes, please refer to bug or read the rlog message
331 CURSOR wip_lpn_serial_cur IS
332 SELECT mmtt.inventory_item_id inventory_item_id
333 , mmtt.organization_id organization_id
334 , msn.lot_number lot_number
335 , mmtt.project_id project_id
336 , mmtt.task_id task_id
337 , mmtt.revision revision
338 , msn.serial_number serial_number
339 , mmtt.subinventory_code
340 , mmtt.transaction_uom
341 , mmtt.locator_id
342 FROM mtl_material_transactions_temp mmtt, mtl_serial_numbers msn
343 WHERE mmtt.transaction_temp_id = p_transaction_id
344 AND mmtt.lpn_id = msn.lpn_id;
345
346 -- For business_flow_code of Cross Dock, the delivery_detail_id is passed.
347 CURSOR wdd_serial_cur IS
348 SELECT wdd1.inventory_item_id inventory_item_id
349 , wdd1.organization_id organization_id
350 , wdd1.lot_number lot_number
351 , NVL(wdd1.project_id, 0) project_id
352 , NVL(wdd1.task_id, 0) task_id
353 , wdd1.revision revision
354 , wdd1.serial_number serial_number
355 , wdd1.subinventory
356 , wdd1.requested_quantity_uom
357 FROM wsh_delivery_details wdd1, wsh_delivery_assignments_v wda, wsh_delivery_details wdd2
358 WHERE wdd1.delivery_detail_id(+) = wda.delivery_detail_id
359 AND wda.parent_delivery_detail_id(+) = wdd2.delivery_detail_id
360 AND wdd2.delivery_detail_id = p_transaction_id; --168158
361
362 -- For business_flow_code of Ship Confirm, the delivery_id is passed. So this means derive all the delivery_detail_id's for the
363 -- delivery_id.
364 CURSOR wda_serial_cur IS
365 SELECT wdd.inventory_item_id inventory_item_id
366 , wdd.organization_id organization_id
367 , wdd.lot_number lot_number
368 , NVL(wdd.project_id, 0) project_id
369 , NVL(wdd.task_id, 0) task_id
370 , wdd.revision revision
371 , wdd.serial_number serial_number /* If there is only one item then this sl. no will get populated
372 and there would not be any mtl_serial_numbers_temp record for it. */
373 , msnt.fm_serial_number fm_serial_number --Added to fix Bug# 4290536
374 , NVL(msnt.to_serial_number, msnt.fm_serial_number) to_serial_number --Added to fix Bug# 4290536
375 , wdd.subinventory
376 , wdd.requested_quantity_uom
377 FROM wsh_delivery_details wdd, wsh_delivery_assignments wda,
378 wsh_new_deliveries wnd, mtl_serial_numbers_temp msnt
379 WHERE wda.delivery_id = wnd.delivery_id
380 AND NVL(wdd.transaction_temp_id, -1) = msnt.transaction_temp_id(+)
381 AND wdd.delivery_detail_id = wda.delivery_detail_id
382 AND wdd.inventory_item_id IS NOT NULL
383 AND wnd.delivery_id = p_transaction_id;
384
385 -- For business_flow_code of WIP Completion(26), Manufacturing Cross-Dock (37) the transaction temp id is passed.
386 -- Bug 2825748 : Material Label Is Not Printed On WIP Completion.
387 -- Bug 3896738
388 CURSOR wip_serial_cur IS
389 SELECT mmtt.inventory_item_id
390 , mmtt.organization_id
391 , mtlt.lot_number
392 , mmtt.cost_group_id
393 , mmtt.project_id
394 , mmtt.task_id
395 , mmtt.transaction_uom
396 , mmtt.revision
397 , msnt.fm_serial_number
398 , msnt.to_serial_number
399 , mmtt.subinventory_code
400 , mmtt.locator_id
401 , wnt.wip_entity_name --Added for Bug: 4642062
402 , wnt.wip_entity_id
403 FROM mtl_material_transactions_temp mmtt
404 , mtl_transaction_lots_temp mtlt
405 , mtl_serial_numbers_temp msnt
406 , wip_entities wnt --Added for Bug 4642062
407 WHERE mtlt.transaction_temp_id(+) = mmtt.transaction_temp_id
408 AND msnt.transaction_temp_id = NVL(mtlt.serial_transaction_temp_id, mmtt.transaction_temp_id)
409 AND mmtt.transaction_temp_id = p_transaction_id
410 AND wnt.wip_entity_id(+) = mmtt.transaction_source_id;--Added for Bug 4642062
411 -- Added the outer Join in the above condition for bug#5438565
412
413 -- For business flow code of 33, the MMTT, MTI or MOL id is passed
414 -- Depending on the txn identifier being passed,one of the
415 -- following 2 flow csrs or the generic mmtt crsr will be called
416 CURSOR flow_serial_curs_mti IS
417 SELECT mti.inventory_item_id inventory_item_id
418 , mti.organization_id organization_id
419 , mtil.lot_number lot_number
420 , mti.project_id project_id
421 , mti.task_id task_id
422 , mti.revision revision
423 , msni.fm_serial_number fm_serial_number
424 , msni.to_serial_number to_serial_number
425 , mti.subinventory_code
426 , mti.locator_id -- Added for Bug #5533362
427 , mti.transaction_uom
428 FROM mtl_transactions_interface mti, mtl_transaction_lots_interface mtil, mtl_serial_numbers_interface msni
429 WHERE mtil.transaction_interface_id(+) = mti.transaction_interface_id
430 AND msni.transaction_interface_id = NVL(mtil.serial_transaction_temp_id, mti.transaction_interface_id)
431 AND mti.transaction_interface_id = p_transaction_id;
432
433 CURSOR flow_serial_curs_mol IS
434 SELECT mol.inventory_item_id inventory_item_id
435 , mol.organization_id organization_id
436 , mol.lot_number lot_number
437 , mol.project_id project_id
438 , mol.task_id task_id
439 , mol.revision revision
440 , mol.serial_number_start fm_serial_number
441 , mol.serial_number_end to_serial_number
442 , mol.from_subinventory_code
443 , mol.uom_code
444 FROM mtl_txn_request_lines mol
445 WHERE mol.line_id = p_transaction_id;
446
447 -- End of Flow csr
448
449 -- Commented as part of bug fix for Bug 3472432.
450 -- Repaced the query with rcv_transactions.
451 -- To get org type.
452 -- CURSOR rti_get_org_cur IS
453 -- SELECT to_organization_id
454 -- FROM rcv_transactions_interface rti
455 -- WHERE rti.interface_transaction_id = p_transaction_id;
456
457 -- To get org type.
458 CURSOR rt_get_org_cur IS
459 SELECT organization_id
460 FROM rcv_transactions rt
461 WHERE rt.GROUP_ID = p_transaction_id;
462
463 -- Fix For Bug: 4907062
464 -- a) Included Project Number in the cursor
465 -- b) Taken the project details from pjm_projects_mtll_v instead of pa_projects
466 CURSOR c_project IS
467 SELECT project_name, project_number
468 FROM pjm_projects_mtll_v --pa_projects
469 WHERE project_id = l_project_id;
470
471 -- Fix For Bug: 4907062
472 -- Included Task Number in the cursor
473 CURSOR c_task IS
474 SELECT task_name, task_number
475 FROM pa_tasks
476 WHERE task_id = l_task_id;
477
478 /* The following cursor has been added to fetch the PROJECT_REFERENCE_ENABLED value
479 * from pjm_org_parameters table. The value 'Y' represents the PJM enabled org.
480 * This field will be used to open the cursors that are required only for PJM org.
481 */
482
483 CURSOR c_project_enabled(p_organization_id NUMBER) IS
484 SELECT pop.project_reference_enabled
485 FROM pjm_org_parameters pop
486 WHERE pop.organization_id = p_organization_id;
487
488 l_is_pjm_org VARCHAR (1);
489
490 --Bug #6417575,Label Printing Support for WIP Move Transactions (12.1)
491 -- Created new cursor to fetch WIP Job Attributes based on wip_entity_id.
492 CURSOR wip_attributes_cur IS
493 SELECT wipent.wip_entity_name job_name
494 , mfglkp.meaning job_type
495 , wipdj.net_quantity job_net_quantity
496 , TO_CHAR(wipdj.scheduled_start_date, g_date_format_mask) job_scheduled_start_date
497 , TO_CHAR(wipdj.scheduled_completion_date, g_date_format_mask) job_scheduled_completion_date
498 , wipdj.bom_revision job_bom_revision
499 , wipdj.routing_revision job_routing_revision
500 FROM wip_entities wipent
501 , wip_discrete_jobs wipdj
502 , mfg_lookups mfglkp
503 WHERE wipdj.wip_entity_id = wipent.wip_entity_id
504 AND wipdj.organization_id = wipent.organization_id
505 AND mfglkp.lookup_code(+) = wipent.entity_type
506 AND mfglkp.lookup_type(+) = 'WIP_ENTITY'
507 AND wipent.wip_entity_id = l_wip_entity_id
508 AND wipent.organization_id = l_organization_id;
509
510 l_entity_type VARCHAR2(80) := NULL;
511 l_net_quantity NUMBER := NULL;
512 l_scheduled_start_date DATE := NULL;
513 l_scheduled_completion_date DATE := NULL;
514 l_bom_revision VARCHAR2(3) := NULL;
515 l_routing_revision VARCHAR2(3) := NULL;
516
517
518 /* Start of fix for bug # 4947399 */
519 l_lot_c_attribute1 VARCHAR2(150);
520 l_lot_c_attribute2 VARCHAR2(150);
521 l_lot_c_attribute3 VARCHAR2(150);
522 l_lot_c_attribute4 VARCHAR2(150);
523 l_lot_c_attribute5 VARCHAR2(150);
524 l_lot_c_attribute6 VARCHAR2(150);
525 l_lot_c_attribute7 VARCHAR2(150);
526 l_lot_c_attribute8 VARCHAR2(150);
527 l_lot_c_attribute9 VARCHAR2(150);
528 l_lot_c_attribute10 VARCHAR2(150);
529 l_lot_c_attribute11 VARCHAR2(150);
530 l_lot_c_attribute12 VARCHAR2(150);
531 l_lot_c_attribute13 VARCHAR2(150);
532 l_lot_c_attribute14 VARCHAR2(150);
533 l_lot_c_attribute15 VARCHAR2(150);
534 l_lot_c_attribute16 VARCHAR2(150);
535 l_lot_c_attribute17 VARCHAR2(150);
536 l_lot_c_attribute18 VARCHAR2(150);
537 l_lot_c_attribute19 VARCHAR2(150);
538 l_lot_c_attribute20 VARCHAR2(150);
539 l_lot_d_attribute1 DATE;
540 l_lot_d_attribute2 DATE;
541 l_lot_d_attribute3 DATE;
542 l_lot_d_attribute4 DATE;
543 l_lot_d_attribute5 DATE;
544 l_lot_d_attribute6 DATE;
545 l_lot_d_attribute7 DATE;
546 l_lot_d_attribute8 DATE;
547 l_lot_d_attribute9 DATE;
548 l_lot_d_attribute10 DATE;
549 l_lot_n_attribute1 NUMBER := NULL;
550 l_lot_n_attribute2 NUMBER := NULL;
551 l_lot_n_attribute3 NUMBER := NULL;
552 l_lot_n_attribute4 NUMBER := NULL;
553 l_lot_n_attribute5 NUMBER := NULL;
554 l_lot_n_attribute6 NUMBER := NULL;
555 l_lot_n_attribute7 NUMBER := NULL;
556 l_lot_n_attribute8 NUMBER := NULL;
557 l_lot_n_attribute9 NUMBER := NULL;
558 l_lot_n_attribute10 NUMBER := NULL;
559 l_serial_c_attribute1 VARCHAR2(150);
560 l_serial_c_attribute2 VARCHAR2(150);
561 l_serial_c_attribute3 VARCHAR2(150);
562 l_serial_c_attribute4 VARCHAR2(150);
563 l_serial_c_attribute5 VARCHAR2(150);
564 l_serial_c_attribute6 VARCHAR2(150);
565 l_serial_c_attribute7 VARCHAR2(150);
566 l_serial_c_attribute8 VARCHAR2(150);
567 l_serial_c_attribute9 VARCHAR2(150);
568 l_serial_c_attribute10 VARCHAR2(150);
569 l_serial_c_attribute11 VARCHAR2(150);
570 l_serial_c_attribute12 VARCHAR2(150);
571 l_serial_c_attribute13 VARCHAR2(150);
572 l_serial_c_attribute14 VARCHAR2(150);
573 l_serial_c_attribute15 VARCHAR2(150);
574 l_serial_c_attribute16 VARCHAR2(150);
575 l_serial_c_attribute17 VARCHAR2(150);
576 l_serial_c_attribute18 VARCHAR2(150);
577 l_serial_c_attribute19 VARCHAR2(150);
578 l_serial_c_attribute20 VARCHAR2(150);
579 l_serial_d_attribute1 DATE;
580 l_serial_d_attribute2 DATE;
581 l_serial_d_attribute3 DATE;
582 l_serial_d_attribute4 DATE;
583 l_serial_d_attribute5 DATE;
584 l_serial_d_attribute6 DATE;
585 l_serial_d_attribute7 DATE;
586 l_serial_d_attribute8 DATE;
587 l_serial_d_attribute9 DATE;
588 l_serial_d_attribute10 DATE;
589 l_serial_n_attribute1 NUMBER := NULL;
590 l_serial_n_attribute2 NUMBER := NULL;
591 l_serial_n_attribute3 NUMBER := NULL;
592 l_serial_n_attribute4 NUMBER := NULL;
593 l_serial_n_attribute5 NUMBER := NULL;
594 l_serial_n_attribute6 NUMBER := NULL;
595 l_serial_n_attribute7 NUMBER := NULL;
596 l_serial_n_attribute8 NUMBER := NULL;
597 l_serial_n_attribute9 NUMBER := NULL;
598 l_serial_n_attribute10 NUMBER := NULL;
599
600
601 /*
602 * The following cursor has been added to fetch the lot and serial attributes from
603 * mtl_transaction_lots_temp and mtl_serial_numbers_temp based on transaction_id,
604 * lot_number, from_serial_number and to_serial_number. Since a lot can have
605 * multiple serials associated with it and each serial can have different attributes,
606 * fm_serial_number and to_serial_number has been added in the condition.
607 *
608 */
609
610 CURSOR c_lot_serial_attributes IS
611 SELECT mtlt.c_attribute1
612 , mtlt.c_attribute2
613 , mtlt.c_attribute3
614 , mtlt.c_attribute4
615 , mtlt.c_attribute5
616 , mtlt.c_attribute6
617 , mtlt.c_attribute7
618 , mtlt.c_attribute8
619 , mtlt.c_attribute9
620 , mtlt.c_attribute10
621 , mtlt.c_attribute11
622 , mtlt.c_attribute12
623 , mtlt.c_attribute13
624 , mtlt.c_attribute14
625 , mtlt.c_attribute15
626 , mtlt.c_attribute16
627 , mtlt.c_attribute17
628 , mtlt.c_attribute18
629 , mtlt.c_attribute19
630 , mtlt.c_attribute20
631 , mtlt.d_attribute1
632 , mtlt.d_attribute2
633 , mtlt.d_attribute3
634 , mtlt.d_attribute4
635 , mtlt.d_attribute5
636 , mtlt.d_attribute6
637 , mtlt.d_attribute7
638 , mtlt.d_attribute8
639 , mtlt.d_attribute9
640 , mtlt.d_attribute10
641 , mtlt.n_attribute1
642 , mtlt.n_attribute2
643 , mtlt.n_attribute3
644 , mtlt.n_attribute4
645 , mtlt.n_attribute5
646 , mtlt.n_attribute6
647 , mtlt.n_attribute7
648 , mtlt.n_attribute8
649 , mtlt.n_attribute9
650 , mtlt.n_attribute10
651 , msnt.c_attribute1
652 , msnt.c_attribute2
653 , msnt.c_attribute3
654 , msnt.c_attribute4
655 , msnt.c_attribute5
656 , msnt.c_attribute6
657 , msnt.c_attribute7
658 , msnt.c_attribute8
659 , msnt.c_attribute9
660 , msnt.c_attribute10
661 , msnt.c_attribute11
662 , msnt.c_attribute12
663 , msnt.c_attribute13
664 , msnt.c_attribute14
665 , msnt.c_attribute15
666 , msnt.c_attribute16
667 , msnt.c_attribute17
668 , msnt.c_attribute18
669 , msnt.c_attribute19
670 , msnt.c_attribute20
671 , msnt.d_attribute1
672 , msnt.d_attribute2
673 , msnt.d_attribute3
674 , msnt.d_attribute4
675 , msnt.d_attribute5
676 , msnt.d_attribute6
677 , msnt.d_attribute7
678 , msnt.d_attribute8
679 , msnt.d_attribute9
680 , msnt.d_attribute10
681 , msnt.n_attribute1
682 , msnt.n_attribute2
683 , msnt.n_attribute3
684 , msnt.n_attribute4
685 , msnt.n_attribute5
686 , msnt.n_attribute6
687 , msnt.n_attribute7
688 , msnt.n_attribute8
689 , msnt.n_attribute9
690 , msnt.n_attribute10
691 FROM mtl_transaction_lots_temp mtlt
692 , mtl_serial_numbers_temp msnt
693 , mtl_material_transactions_temp mmtt
694 WHERE mtlt.transaction_temp_id(+) = mmtt.transaction_temp_id
695 AND msnt.transaction_temp_id = NVL(mtlt.serial_transaction_temp_id, mmtt.transaction_temp_id)
696 AND mmtt.transaction_temp_id = p_transaction_id
697 AND mtlt.lot_number(+) = l_lot_number
698 AND msnt.fm_serial_number = l_fm_serial_number
699 AND msnt.to_serial_number = l_to_serial_number;
700
701 /* End of fix for bug # 4947399 */
702
703
704 CURSOR serial_cur IS
705 SELECT msn2.item item
706 , msn2.inventory_item_id inventory_item_id
707 , mp.organization_code ORGANIZATION
708 , msn2.organization_id organization_id
709 , msn2.item_description item_description
710 , msn2.revision revision
711 , msn2.item_hazard_class item_hazard_class
712 , msn2.item_attribute_category item_attribute_category
713 , msn2.item_attribute1 item_attribute1
714 , msn2.item_attribute2 item_attribute2
715 , msn2.item_attribute3 item_attribute3
716 , msn2.item_attribute4 item_attribute4
717 , msn2.item_attribute5 item_attribute5
718 , msn2.item_attribute6 item_attribute6
719 , msn2.item_attribute7 item_attribute7
720 , msn2.item_attribute8 item_attribute8
721 , msn2.item_attribute9 item_attribute9
722 , msn2.item_attribute10 item_attribute10
723 , msn2.item_attribute11 item_attribute11
724 , msn2.item_attribute12 item_attribute12
725 , msn2.item_attribute13 item_attribute13
726 , msn2.item_attribute14 item_attribute14
727 , msn2.item_attribute15 item_attribute15
728 , msn2.serial_number serial_number
729 , mmsvl1.status_code lot_status
730 , msn2.serial_attribute_category serial_attribute_category
731 , -- Start for bug # 4947399
732 NVL(l_serial_c_attribute1, msn2.serial_c_attribute1) serial_c_attribute1
733 , NVL(l_serial_c_attribute2, msn2.serial_c_attribute2) serial_c_attribute2
734 , NVL(l_serial_c_attribute3, msn2.serial_c_attribute3) serial_c_attribute3
735 , NVL(l_serial_c_attribute4, msn2.serial_c_attribute4) serial_c_attribute4
736 , NVL(l_serial_c_attribute5, msn2.serial_c_attribute5) serial_c_attribute5
737 , NVL(l_serial_c_attribute6, msn2.serial_c_attribute6) serial_c_attribute6
738 , NVL(l_serial_c_attribute7, msn2.serial_c_attribute7) serial_c_attribute7
739 , NVL(l_serial_c_attribute8, msn2.serial_c_attribute8) serial_c_attribute8
740 , NVL(l_serial_c_attribute9, msn2.serial_c_attribute9) serial_c_attribute9
741 , NVL(l_serial_c_attribute10, msn2.serial_c_attribute10) serial_c_attribute10
742 , NVL(l_serial_c_attribute11, msn2.serial_c_attribute11) serial_c_attribute11
743 , NVL(l_serial_c_attribute12, msn2.serial_c_attribute12) serial_c_attribute12
744 , NVL(l_serial_c_attribute13, msn2.serial_c_attribute13) serial_c_attribute13
745 , NVL(l_serial_c_attribute14, msn2.serial_c_attribute14) serial_c_attribute14
746 , NVL(l_serial_c_attribute15, msn2.serial_c_attribute15) serial_c_attribute15
747 , NVL(l_serial_c_attribute16, msn2.serial_c_attribute16) serial_c_attribute16
748 , NVL(l_serial_c_attribute17, msn2.serial_c_attribute17) serial_c_attribute17
749 , NVL(l_serial_c_attribute18, msn2.serial_c_attribute18) serial_c_attribute18
750 , NVL(l_serial_c_attribute19, msn2.serial_c_attribute19) serial_c_attribute19
751 , NVL(l_serial_c_attribute20, msn2.serial_c_attribute20) serial_c_attribute20
752 , NVL(l_serial_d_attribute1, msn2.serial_d_attribute1) serial_d_attribute1
753 , NVL(l_serial_d_attribute2, msn2.serial_d_attribute2) serial_d_attribute2
754 , NVL(l_serial_d_attribute3, msn2.serial_d_attribute3) serial_d_attribute3
755 , NVL(l_serial_d_attribute4, msn2.serial_d_attribute4) serial_d_attribute4
756 , NVL(l_serial_d_attribute5, msn2.serial_d_attribute5) serial_d_attribute5
757 , NVL(l_serial_d_attribute6, msn2.serial_d_attribute6) serial_d_attribute6
758 , NVL(l_serial_d_attribute7, msn2.serial_d_attribute7) serial_d_attribute7
759 , NVL(l_serial_d_attribute8, msn2.serial_d_attribute8) serial_d_attribute8
760 , NVL(l_serial_d_attribute9, msn2.serial_d_attribute9) serial_d_attribute9
761 , NVL(l_serial_d_attribute10, msn2.serial_d_attribute10) serial_d_attribute10
762 , NVL(l_serial_n_attribute1, msn2.serial_n_attribute1) serial_n_attribute1
763 , NVL(l_serial_n_attribute2, msn2.serial_n_attribute2) serial_n_attribute2
764 , NVL(l_serial_n_attribute3, msn2.serial_n_attribute3) serial_n_attribute3
765 , NVL(l_serial_n_attribute4, msn2.serial_n_attribute4) serial_n_attribute4
766 , NVL(l_serial_n_attribute5, msn2.serial_n_attribute5) serial_n_attribute5
767 , NVL(l_serial_n_attribute6, msn2.serial_n_attribute6) serial_n_attribute6
768 , NVL(l_serial_n_attribute7, msn2.serial_n_attribute7) serial_n_attribute7
769 , NVL(l_serial_n_attribute8, msn2.serial_n_attribute8) serial_n_attribute8
770 , NVL(l_serial_n_attribute9, msn2.serial_n_attribute9) serial_n_attribute9
771 , NVL(l_serial_n_attribute10, msn2.serial_n_attribute10) serial_n_attribute10
772 , -- End for bug # 4947399
773 msn2.serial_country_of_origin serial_country_of_origin
774 , msn2.serial_time_since_new serial_time_since_new
775 , msn2.serial_cycles_since_new serial_cycles_since_new
776 , msn2.serial_time_since_overhaul serial_time_since_overhaul
777 , msn2.serial_cycles_since_overhaul serial_cycles_since_overhaul
778 , msn2.serial_time_since_repair serial_time_since_repair
779 , msn2.serial_cycles_since_repair serial_cycles_since_repair
780 , msn2.serial_time_since_visit serial_time_since_visit
781 , msn2.serial_cycles_since_visit serial_cycles_since_visit
782 , msn2.serial_time_since_mark serial_time_since_mark
783 , msn2.serial_cycles_since_mark serial_cycles_since_mark
784 , msn2.serial_num_of_repairs serial_num_of_repairs
785 , msn2.serial_initialization_date serial_initialization_date
786 , msn2.serial_completion_date serial_completion_date
787 , msn2.serial_fixed_asset_tag serial_fixed_asset_tag
788 , msn2.serial_vendor_serial serial_vendor_serial
789 , msn2.project_number project_number -- Fix For Bug: 4907062
790 , msn2.project project
791 , msn2.task_number task_number -- Fix For Bug: 4907062
792 , msn2.task task
793 , msn2.cost_group cost_group
794 , NVL(l_lot_number, mln.lot_number) lot_number
795 , msn2.serial_number_status serial_number_status
796 , msn2.job_name job_name
797 , -- Added as part of change for patchset "I".
798 msn2.LOCATOR LOCATOR
799 , TO_CHAR(mln.expiration_date, g_date_format_mask) lot_expiration_date
800 , -- Added for Bug 2795525,
801 mln.attribute_category lot_attribute_category
802 , -- Start for bug # 4947399
803 NVL(l_lot_c_attribute1, mln.c_attribute1) lot_c_attribute1
804 , NVL(l_lot_c_attribute2, mln.c_attribute2) lot_c_attribute2
805 , NVL(l_lot_c_attribute3, mln.c_attribute3) lot_c_attribute3
806 , NVL(l_lot_c_attribute4, mln.c_attribute4) lot_c_attribute4
807 , NVL(l_lot_c_attribute5, mln.c_attribute5) lot_c_attribute5
808 , NVL(l_lot_c_attribute6, mln.c_attribute6) lot_c_attribute6
809 , NVL(l_lot_c_attribute7, mln.c_attribute7) lot_c_attribute7
810 , NVL(l_lot_c_attribute8, mln.c_attribute8) lot_c_attribute8
811 , NVL(l_lot_c_attribute9, mln.c_attribute9) lot_c_attribute9
812 , NVL(l_lot_c_attribute10, mln.c_attribute10) lot_c_attribute10
813 , NVL(l_lot_c_attribute11, mln.c_attribute11) lot_c_attribute11
814 , NVL(l_lot_c_attribute12, mln.c_attribute12) lot_c_attribute12
815 , NVL(l_lot_c_attribute13, mln.c_attribute13) lot_c_attribute13
816 , NVL(l_lot_c_attribute14, mln.c_attribute14) lot_c_attribute14
817 , NVL(l_lot_c_attribute15, mln.c_attribute15) lot_c_attribute15
818 , NVL(l_lot_c_attribute16, mln.c_attribute16) lot_c_attribute16
819 , NVL(l_lot_c_attribute17, mln.c_attribute17) lot_c_attribute17
820 , NVL(l_lot_c_attribute18, mln.c_attribute18) lot_c_attribute18
821 , NVL(l_lot_c_attribute19, mln.c_attribute19) lot_c_attribute19
822 , NVL(l_lot_c_attribute20, mln.c_attribute20) lot_c_attribute20
823 , TO_CHAR(NVL(l_lot_d_attribute1, mln.d_attribute1), g_date_format_mask) lot_d_attribute1
824 , -- Added for Bug 2795525,
825 TO_CHAR(NVL(l_lot_d_attribute2, mln.d_attribute2), g_date_format_mask) lot_d_attribute2
826 , -- Added for Bug 2795525,
827 TO_CHAR(NVL(l_lot_d_attribute3, mln.d_attribute3), g_date_format_mask) lot_d_attribute3
828 , -- Added for Bug 2795525,
829 TO_CHAR(NVL(l_lot_d_attribute4, mln.d_attribute4), g_date_format_mask) lot_d_attribute4
830 , -- Added for Bug 2795525,
831 TO_CHAR(NVL(l_lot_d_attribute5, mln.d_attribute5), g_date_format_mask) lot_d_attribute5
832 , -- Added for Bug 2795525,
833 TO_CHAR(NVL(l_lot_d_attribute6, mln.d_attribute6), g_date_format_mask) lot_d_attribute6
834 , -- Added for Bug 2795525,
835 TO_CHAR(NVL(l_lot_d_attribute7, mln.d_attribute7), g_date_format_mask) lot_d_attribute7
836 , -- Added for Bug 2795525,
837 TO_CHAR(NVL(l_lot_d_attribute8, mln.d_attribute8), g_date_format_mask) lot_d_attribute8
838 , -- Added for Bug 2795525,
839 TO_CHAR(NVL(l_lot_d_attribute9, mln.d_attribute9), g_date_format_mask) lot_d_attribute9
840 , -- Added for Bug 2795525,
841 TO_CHAR(NVL(l_lot_d_attribute10, mln.d_attribute10), g_date_format_mask) lot_d_attribute10
842 , -- Added for Bug 2795525,
843 NVL(l_lot_n_attribute1, mln.n_attribute1) lot_n_attribute1
844 , NVL(l_lot_n_attribute2, mln.n_attribute2) lot_n_attribute2
845 , NVL(l_lot_n_attribute3, mln.n_attribute3) lot_n_attribute3
846 , NVL(l_lot_n_attribute4, mln.n_attribute4) lot_n_attribute4
847 , NVL(l_lot_n_attribute5, mln.n_attribute5) lot_n_attribute5
848 , NVL(l_lot_n_attribute6, mln.n_attribute6) lot_n_attribute6
849 , NVL(l_lot_n_attribute7, mln.n_attribute7) lot_n_attribute7
850 , NVL(l_lot_n_attribute8, mln.n_attribute8) lot_n_attribute8
851 , NVL(l_lot_n_attribute9, mln.n_attribute9) lot_n_attribute9
852 , NVL(l_lot_n_attribute10, mln.n_attribute10) lot_n_attribute10
853 , -- End for bug # 4947399
854 mln.territory_code lot_country_of_origin
855 , mln.grade_code lot_grade_code
856 , TO_CHAR(mln.origination_date, g_date_format_mask) lot_origination_date
857 , -- Added for Bug 2795525,
858 mln.date_code lot_date_code
859 , TO_CHAR(mln.change_date, g_date_format_mask) lot_change_date
860 , -- Added for Bug 2795525,
861 mln.age lot_age
862 , TO_CHAR(mln.retest_date, g_date_format_mask) lot_retest_date
863 , -- Added for Bug 2795525,
864 TO_CHAR(mln.maturity_date, g_date_format_mask) lot_maturity_date
865 , -- Added for Bug 2795525,
866 mln.item_size lot_item_size
867 , mln.color lot_color
868 , mln.volume lot_volume
869 , mln.volume_uom lot_volume_uom
870 , mln.place_of_origin lot_place_of_origin
871 , TO_CHAR(mln.best_by_date, g_date_format_mask) lot_best_by_date
872 , -- Added for Bug 2795525,
873 mln.LENGTH lot_length
874 , mln.length_uom lot_length_uom
875 , mln.recycled_content lot_recycled_cont
876 , mln.thickness lot_thickness
877 , mln.thickness_uom lot_thickness_uom
878 , mln.width lot_width
879 , mln.width_uom lot_width_uom
880 , mln.curl_wrinkle_fold lot_curl
881 , mln.vendor_name lot_vendor
882 FROM mtl_lot_numbers mln
883 , mtl_material_statuses_vl mmsvl1
884 , mtl_parameters mp
885 , (SELECT msik.concatenated_segments item
886 , msik.inventory_item_id inventory_item_id
887 , msik.organization_id organization_id
888 , msik.description item_description
889 , l_revision revision
890 , poh.hazard_class item_hazard_class
891 , msik.attribute_category item_attribute_category
892 , msik.attribute1 item_attribute1
893 , msik.attribute2 item_attribute2
894 , msik.attribute3 item_attribute3
895 , msik.attribute4 item_attribute4
896 , msik.attribute5 item_attribute5
897 , msik.attribute6 item_attribute6
898 , msik.attribute7 item_attribute7
899 , msik.attribute8 item_attribute8
900 , msik.attribute9 item_attribute9
901 , msik.attribute10 item_attribute10
902 , msik.attribute11 item_attribute11
903 , msik.attribute12 item_attribute12
904 , msik.attribute13 item_attribute13
905 , msik.attribute14 item_attribute14
906 , msik.attribute15 item_attribute15
907 , mmsvl2.status_code serial_number_status
908 , msn.attribute_category serial_attribute_category
909 , msn.c_attribute1 serial_c_attribute1
910 , msn.c_attribute2 serial_c_attribute2
911 , msn.c_attribute3 serial_c_attribute3
912 , msn.c_attribute4 serial_c_attribute4
913 , msn.c_attribute5 serial_c_attribute5
914 , msn.c_attribute6 serial_c_attribute6
915 , msn.c_attribute7 serial_c_attribute7
916 , msn.c_attribute8 serial_c_attribute8
917 , msn.c_attribute9 serial_c_attribute9
918 , msn.c_attribute10 serial_c_attribute10
919 , msn.c_attribute11 serial_c_attribute11
920 , msn.c_attribute12 serial_c_attribute12
921 , msn.c_attribute13 serial_c_attribute13
922 , msn.c_attribute14 serial_c_attribute14
923 , msn.c_attribute15 serial_c_attribute15
924 , msn.c_attribute16 serial_c_attribute16
925 , msn.c_attribute17 serial_c_attribute17
926 , msn.c_attribute18 serial_c_attribute18
927 , msn.c_attribute19 serial_c_attribute19
928 , msn.c_attribute20 serial_c_attribute20
929 , TO_CHAR(msn.d_attribute1, g_date_format_mask) serial_d_attribute1
930 , -- Added for Bug 2795525,
931 TO_CHAR(msn.d_attribute2, g_date_format_mask) serial_d_attribute2
932 , -- Added for Bug 2795525,
933 TO_CHAR(msn.d_attribute3, g_date_format_mask) serial_d_attribute3
934 , -- Added for Bug 2795525,
935 TO_CHAR(msn.d_attribute4, g_date_format_mask) serial_d_attribute4
936 , -- Added for Bug 2795525,
937 TO_CHAR(msn.d_attribute5, g_date_format_mask) serial_d_attribute5
938 , -- Added for Bug 2795525,
939 TO_CHAR(msn.d_attribute6, g_date_format_mask) serial_d_attribute6
940 , -- Added for Bug 2795525,
941 TO_CHAR(msn.d_attribute7, g_date_format_mask) serial_d_attribute7
942 , -- Added for Bug 2795525,
943 TO_CHAR(msn.d_attribute8, g_date_format_mask) serial_d_attribute8
944 , -- Added for Bug 2795525,
945 TO_CHAR(msn.d_attribute9, g_date_format_mask) serial_d_attribute9
946 , -- Added for Bug 2795525,
947 TO_CHAR(msn.d_attribute10, g_date_format_mask) serial_d_attribute10
948 , -- Added for Bug 2795525,
949 msn.n_attribute1 serial_n_attribute1
950 , msn.n_attribute2 serial_n_attribute2
951 , msn.n_attribute3 serial_n_attribute3
952 , msn.n_attribute4 serial_n_attribute4
953 , msn.n_attribute5 serial_n_attribute5
954 , msn.n_attribute6 serial_n_attribute6
955 , msn.n_attribute7 serial_n_attribute7
956 , msn.n_attribute8 serial_n_attribute8
957 , msn.n_attribute9 serial_n_attribute9
958 , msn.n_attribute10 serial_n_attribute10
959 , msn.territory_code serial_country_of_origin
960 , msn.time_since_new serial_time_since_new
961 , msn.cycles_since_new serial_cycles_since_new
962 , msn.time_since_overhaul serial_time_since_overhaul
963 , msn.cycles_since_overhaul serial_cycles_since_overhaul
964 , msn.time_since_repair serial_time_since_repair
965 , msn.cycles_since_repair serial_cycles_since_repair
966 , msn.time_since_visit serial_time_since_visit
967 , msn.cycles_since_visit serial_cycles_since_visit
968 , msn.time_since_mark serial_time_since_mark
969 , msn.cycles_since_mark serial_cycles_since_mark
970 , msn.number_of_repairs serial_num_of_repairs
971 , TO_CHAR(msn.initialization_date, g_date_format_mask) serial_initialization_date
972 -- Added for Bug 2795525,
973 , TO_CHAR(msn.completion_date, g_date_format_mask) serial_completion_date
974 -- Added for Bug 2795525, ,
975 , msn.fixed_asset_tag serial_fixed_asset_tag
976 , msn.vendor_serial_number serial_vendor_serial
977 , l_project_number project_number -- Fix For Bug: 4907062
978 , l_project_name project
979 , l_task_number task_number -- Fix For Bug: 4907062
980 , l_task_name task
981 , ccg.cost_group cost_group
982 , msn.lot_number lot_number
983 , msn.serial_number serial_number
984 , wipent.wip_entity_name job_name
985 , -- Added as part of change for patchset "I".
986 wilk.concatenated_segments LOCATOR
987 --milk.concatenated_segments LOCATOR -- Modified for bug # 5015415
988 FROM mtl_system_items_vl msik
989 , mtl_material_statuses_vl mmsvl2
990 , po_hazard_classes poh
991 , mtl_serial_numbers msn
992 , cst_cost_groups ccg
993 , wip_entities wipent
994 , wms_item_locations_kfv wilk -- Modified for bug # 5015415
995 -- , mtl_item_locations_kfv milk -- Added as part of change for patchset "I".
996 WHERE msik.inventory_item_id = l_inventory_item_id
997 AND msik.organization_id = l_organization_id
998 AND poh.hazard_class_id(+) = msik.hazard_class_id
999 AND msn.current_organization_id(+) = msik.organization_id
1000 AND msn.inventory_item_id(+) = msik.inventory_item_id
1001 AND msn.serial_number(+) = l_serial_number
1002 AND mmsvl2.status_id(+) = msn.status_id
1003 AND ccg.cost_group_id(+) = msn.cost_group_id
1004 AND wipent.wip_entity_id(+) = msn.wip_entity_id
1005 /* The following conditions have been modified for bug # 5015415.
1006
1007 For PJM Org, Locator field in Material Label should not show the Project and task id's.
1008 This is because, the Project and Task Id's are not Bar code transactable.
1009 In mtl_item_locations_kfv, the cocatenated segments will have Project and
1010 Task Id's attached to it. Whereas in wms_item_locations_kfv, concatenated
1011 segments will have only the physical details (Row, Rack and Bin)
1012 and not the project and Task id's.
1013
1014 AND milk.inventory_location_id(+) = l_locator_id
1015 AND milk.organization_id(+) = msik.organization_id
1016 AND milk.subinventory_code(+) = l_subinventory*/
1017
1018 AND wilk.inventory_location_id(+) = l_locator_id
1019 AND wilk.organization_id(+) = msik.organization_id
1020 AND wilk.subinventory_code(+) = l_subinventory) msn2 -- Added as part of change for patchset "I".
1021 WHERE mln.organization_id(+) = l_organization_id
1022 AND mln.inventory_item_id(+) = l_inventory_item_id
1023 AND mmsvl1.status_id(+) = mln.status_id
1024 AND mln.lot_number(+) = msn2.lot_number
1025 AND mp.organization_id = msn2.organization_id
1026 AND mln.lot_number(+) = l_lot_number;
1027
1028 /* added for invconv, to get OPM lot attributes */
1029 CURSOR get_lot_info IS
1030 select
1031 parent_lot_number,
1032 expiration_action_date ,
1033 expiration_action_code,
1034 hold_date ,
1035 supplier_lot_number,
1036 origination_type ,
1037 grade_code,
1038 maturity_date,
1039 retest_date,
1040 expiration_date,
1041 origination_date,
1042 sts.status_code
1043 FROM
1044 MTL_LOT_NUMBERS l,
1045 mtl_material_statuses_vl sts
1046 WHERE LOT_NUMBER = l_lot_number AND
1047 INVENTORY_ITEM_ID = l_inventory_item_id AND
1048 ORGANIZATION_ID = l_organization_id
1049 AND sts.status_id(+) = l.status_id; -- Bug 4355080
1050
1051 /* added for invconv,to get OPM lot attributes , if the lot is new then the data
1052 must be fetched from mtlt */
1053
1054 CURSOR mtlt_lot_info_cur IS
1055 SELECT parent_lot_number,
1056 expiration_action_date ,
1057 expiration_action_code,
1058 hold_date ,
1059 supplier_lot_number,
1060 origination_type ,
1061 grade_code,
1062 maturity_date,
1063 retest_date,
1064 lot_expiration_date,
1065 origination_date,
1066 sts.status_code
1067 FROM mtl_transaction_lots_temp t,
1068 mtl_material_statuses_vl sts
1069 WHERE transaction_temp_id = p_input_param.transaction_temp_id AND
1070 lot_number = l_lot_number
1071 AND sts.status_id(+) = t.status_id; -- Bug 4355080
1072
1073
1074 --R12 PROJECT LABEL SET with RFID
1075 CURSOR c_label_formats_in_set(p_format_set_id IN NUMBER) IS
1076 select wlfs.format_id label_format_id, wlf.label_entity_type --FOR SETS
1077 from wms_label_set_formats wlfs , wms_label_formats wlf
1078 where WLFS.SET_ID = p_format_set_id
1079 and wlfs.set_id = wlf.label_format_id
1080 and wlf.label_entity_type = 1
1081 AND WLF.DOCUMENT_ID = 2
1082 UNION --FOR FORMATS
1083 select label_format_id,nvl(wlf.label_entity_type,0)
1084 from wms_label_formats wlf
1085 where wlf.label_format_id = p_format_set_id
1086 and nvl(wlf.label_entity_type,0) = 0 --for label formats only validation
1087 AND WLF.DOCUMENT_ID = 2 ;
1088
1089
1090 -- Interface transaction_id 71629
1091 -- lpn_id 2170
1092 serial_rec serial_cur%ROWTYPE;
1093 l_org_type BOOLEAN := FALSE;
1094 l_serial_label LONG := '';
1095 l_get_org_id NUMBER := 0;
1096 l_is_wms_org BOOLEAN;
1097 l_selected_fields inv_label.label_field_variable_tbl_type;
1098 l_selected_fields_count NUMBER;
1099 l_api_name VARCHAR2(20) := 'get_variable_data';
1100 l_return_status VARCHAR2(240);
1101 l_error_message VARCHAR2(240);
1102 l_api_status VARCHAR2(240);
1103 i NUMBER;
1104 l_business_flow_code NUMBER := p_label_type_info.business_flow_code;
1105 l_count NUMBER;
1106 l_msg_count NUMBER;
1107 l_msg_data VARCHAR2(240);
1108 serial_count NUMBER;
1109 l_serial_numbers_table inv_label.serial_tab_type;
1110 i BINARY_INTEGER;
1111 l_wms_installed BOOLEAN;
1112 l_serial_data LONG := '';
1113 l_label_format_id NUMBER := 0;
1114 l_label_format VARCHAR2(100) := '';
1115 l_printer VARCHAR2(30) := '';
1116 selected_fields_count NUMBER;
1117 i NUMBER;
1118 j NUMBER;
1119 l_serial_table_index NUMBER;
1120 l_serial_loop_count NUMBER;
1121 l_purchase_order po_headers_all.segment1%TYPE;
1122 l_label_index NUMBER;
1123 l_label_request_id NUMBER;
1124 --I cleanup, use l_prev_format_id to record the previous label format
1125 l_prev_format_id NUMBER;
1126 -- I cleanup, user l_prev_sub to record the previous subinventory
1127 --so that get_printer is not called if the subinventory is the same
1128 l_prev_sub VARCHAR2(30);
1129 -- a list of columns that are selected for format
1130 l_column_name_list LONG;
1131 l_patch_level NUMBER;
1132 -- Added the variable for Bug 4642062 to store the job name
1133 l_wip_entity_name wip_entities.wip_entity_name%TYPE;
1134
1135 --Start: Enabling EPC generation for R12 Project
1136 l_epc VARCHAR2(300);
1137 l_epc_ret_status VARCHAR2(10);
1138 l_epc_ret_msg VARCHAR2(1000);
1139 l_label_status VARCHAR2(1);
1140 l_label_err_msg VARCHAR2(1000);
1141 l_is_epc_exist VARCHAR2(1) := 'N';
1142 l_label_format_set_id NUMBER;
1143 --End: Enabling EPC generation for R12 Project
1144
1145 BEGIN
1146 x_return_status := fnd_api.g_ret_sts_success;
1147 l_label_err_msg := NULL;
1148 l_debug := inv_label.l_debug;
1149
1150 IF (
1151 (inv_rcv_common_apis.g_inv_patch_level >= inv_rcv_common_apis.g_patchset_j)
1152 AND(inv_rcv_common_apis.g_po_patch_level >= inv_rcv_common_apis.g_patchset_j_po)
1153 ) THEN
1154 l_patch_level := 1; --Patchset J and above
1155 ELSE
1156 l_patch_level := 0; --Below Patchset J
1157 END IF;
1158
1159 IF (l_debug = 1) THEN
1160 TRACE('**In PVT2: Serial label**');
1161 TRACE(
1162 ' Business_flow='
1163 || p_label_type_info.business_flow_code
1164 || ', Transaction ID='
1165 || p_transaction_id
1166 || ', Transaction Identifier='
1167 || p_transaction_identifier
1168 );
1169 END IF;
1170
1171 -- Get org for p_transaction_id
1172 -- As part of fix for bug 3472432, the rti_get_org_cur is beign replaced with rt_get_org_cur.
1173 IF p_label_type_info.business_flow_code IN(1, 2, 3, 4) THEN
1174 OPEN rt_get_org_cur;
1175
1176 FETCH rt_get_org_cur
1177 INTO l_get_org_id;
1178
1179 IF rt_get_org_cur%NOTFOUND THEN
1180 IF (l_debug = 1) THEN
1181 TRACE(' No record found in RTI for ID: ' || p_transaction_id);
1182 END IF;
1183
1184 CLOSE rt_get_org_cur;
1185
1186 RETURN;
1187 ELSE
1188 CLOSE rt_get_org_cur;
1189 END IF;
1190
1191 l_is_wms_org :=
1192 wms_install.check_install(x_return_status => l_return_status, x_msg_count => l_msg_count, x_msg_data => l_msg_data
1193 , p_organization_id => l_get_org_id);
1194
1195 IF l_return_status <> 'S' THEN
1196 fnd_message.set_name('WMS', 'WMS_INSTALL_CHECK_INSTALL_FAILED');
1197 fnd_msg_pub.ADD;
1198 RETURN;
1199 END IF;
1200
1201 IF (l_debug = 1) THEN
1202 IF (l_is_wms_org = TRUE) THEN
1203 TRACE(' Org is WMS enabled ');
1204 ELSE
1205 TRACE(' Org is INV enabled ');
1206 END IF;
1207 END IF;
1208 END IF;
1209
1210 --Main Start
1211 IF p_transaction_id IS NOT NULL THEN -- Business flow + transaction_id passed.
1212 -- txn driven
1213
1214 /* Patchset J- Open the new cursor for patchset J and above. Otherwise, the existing
1215 * code remains as it is.
1216 */
1217 IF (p_label_type_info.business_flow_code IN(1, 2, 3, 4)
1218 AND(l_patch_level = 1)) THEN
1219 TRACE('Patchset J code');
1220 l_receipt_number := inv_rcv_common_apis.g_rcv_global_var.receipt_num;
1221
1222 OPEN rt_serial_cur;
1223
1224 FETCH rt_serial_cur
1225 INTO l_inventory_item_id
1226 , l_organization_id
1227 , l_lot_number
1228 , l_project_id
1229 , l_task_id
1230 , l_revision
1231 , l_serial_number
1232 , l_purchase_order
1233 , l_subinventory
1234 , l_locator_id
1235 , l_vendor_id
1236 , l_vendor_site_id
1237 , l_uom
1238 , l_oe_order_header_id --Bug 4582954
1239 , l_oe_order_line_id; --Bug 4582954
1240
1241 IF rt_serial_cur%NOTFOUND THEN
1242 IF (l_debug = 1) THEN
1243 TRACE(' (1)No Serial number found for this given Interface Transaction ID:' || p_transaction_id);
1244 END IF;
1245
1246 CLOSE rt_serial_cur;
1247
1248 RETURN;
1249 END IF;
1250 ELSIF((l_patch_level = 0)
1251 AND(p_label_type_info.business_flow_code IN(1, 2))
1252 AND(l_is_wms_org = TRUE)) THEN
1253 -- Receipt(1), Inspection(2) or Putaway Drop(4) and org is WMS enabled
1254 -- In an INV org there are no serial numbers at these points in the transaction.
1255 l_receipt_number := inv_rcv_common_apis.g_rcv_global_var.receipt_num;
1256
1257 OPEN rti_serial_lpn_cur;
1258
1259 FETCH rti_serial_lpn_cur
1260 INTO l_inventory_item_id
1261 , l_organization_id
1262 , l_lot_number
1263 , l_project_id
1264 , l_task_id
1265 , l_revision
1266 , l_serial_number
1267 , l_purchase_order
1268 , l_subinventory
1269 , l_vendor_id
1270 , l_vendor_site_id
1271 , l_oe_order_header_id --Bug 4582954
1272 , l_oe_order_line_id; --Bug 4582954
1273
1274 IF rti_serial_lpn_cur%NOTFOUND THEN
1275 IF (l_debug = 1) THEN
1276 TRACE(' (1)No Serial number found for this given Interface Transaction ID:' || p_transaction_id);
1277 END IF;
1278
1279 CLOSE rti_serial_lpn_cur;
1280
1281 RETURN;
1282 END IF;
1283 ELSIF(
1284 (l_patch_level = 0)
1285 AND(
1286 (p_label_type_info.business_flow_code IN(3)
1287 AND(l_is_wms_org = TRUE))
1288 OR(p_label_type_info.business_flow_code IN(4)
1289 AND(l_is_wms_org = FALSE))
1290 )
1291 ) THEN
1292 -- Delivery(3) and org is an INV org.
1293 l_receipt_number := inv_rcv_common_apis.g_rcv_global_var.receipt_num;
1294
1295 OPEN rti_serial_msnt_cur;
1296
1297 FETCH rti_serial_msnt_cur
1298 INTO l_inventory_item_id
1299 , l_organization_id
1300 , l_lot_number
1301 , l_project_id
1302 , l_task_id
1303 , l_revision
1304 , l_fm_serial_number
1305 , l_to_serial_number
1306 , l_purchase_order
1307 , l_subinventory
1308 , l_vendor_id
1309 , l_vendor_site_id
1310 , l_oe_order_header_id --Bug 4582954
1311 , l_oe_order_line_id; --Bug 4582954
1312
1313 IF rti_serial_msnt_cur%NOTFOUND THEN
1314 IF (l_debug = 1) THEN
1315 TRACE(' (2)No Serial number found for this given Interface Transaction ID:' || p_transaction_id);
1316 END IF;
1317
1318 CLOSE rti_serial_msnt_cur;
1319
1320 RETURN;
1321 ELSE
1322 -- getting range serial numbers
1323 inv_label.get_number_between_range(
1324 fm_x_number => l_fm_serial_number
1325 , to_x_number => l_to_serial_number
1326 , x_return_status => l_return_status
1327 , x_number_table => l_serial_numbers_table
1328 );
1329
1330 IF l_return_status <> 'S' THEN
1331 fnd_message.set_name('WMS', 'WMS_GET_SER_CUR_FAILED');
1332 fnd_msg_pub.ADD;
1333 RETURN;
1334 END IF;
1335
1336 IF (l_debug = 1) THEN
1337 TRACE(' Number of SN in l_serial_numbers_table ' || l_serial_numbers_table.COUNT);
1338 END IF;
1339
1340 l_serial_number := l_serial_numbers_table(1);
1341 END IF;
1342 ELSIF(p_label_type_info.business_flow_code IN(6)) THEN
1343 -- Cross Dock(6).
1344 -- Here in this case the delivery_detail_id is being passed.
1345 -- Delivery detail ID passed means that we just have to print serial label for the one delivery detail id and
1346 -- not all the delivery detail id's in the delivery.
1347 -- The cost group will be derived from the table wms_license_plate_numbers for the LPN stamped on the Delivery_detail_id.
1348 OPEN wdd_serial_cur;
1349
1350 FETCH wdd_serial_cur
1351 INTO l_inventory_item_id
1352 , l_organization_id
1353 , l_lot_number
1354 , l_project_id
1355 , l_task_id
1356 , l_revision
1357 , l_serial_number
1358 , l_subinventory
1359 , l_uom;
1360
1361 IF wdd_serial_cur%NOTFOUND THEN
1362 IF (l_debug = 1) THEN
1363 TRACE(' No Serial number found for this given Delivery Detail ID:' || p_transaction_id);
1364 END IF;
1365
1366 CLOSE wdd_serial_cur;
1367
1368 RETURN;
1369 -- Bug 3836623
1370 -- Can not close the cursor because there maybe more record available
1371 -- ELSE
1372 -- CLOSE wdd_serial_cur;
1373 END IF;
1374 ELSIF p_label_type_info.business_flow_code IN(13, 23, 27) THEN
1375 -- Miscellaneous/Alias Receipt(13), Miscellaneous/Alias Issue(23)
1376 -- Put Away pregeneration(27)
1377 -- Flow, MMTT based (33), transaction_identifier=1
1378 OPEN mmtt_serial_cur;
1379
1380 FETCH mmtt_serial_cur
1381 INTO l_inventory_item_id
1382 , l_organization_id
1383 , l_lot_number
1384 , l_project_id
1385 , l_task_id
1386 , l_revision
1387 , l_fm_serial_number
1388 , l_to_serial_number
1389 , l_subinventory
1390 , l_uom
1391 , l_locator_id; /* Added for Bug # 4672471 */
1392
1393 IF mmtt_serial_cur%NOTFOUND THEN
1394 IF (l_debug = 1) THEN
1395 TRACE(' No Serial number found for this given Transaction Temp ID:' || p_transaction_id);
1396 END IF;
1397
1398 CLOSE mmtt_serial_cur;
1399
1400 RETURN;
1401 ELSE
1402 inv_label.get_number_between_range(
1403 fm_x_number => l_fm_serial_number
1404 , to_x_number => l_to_serial_number
1405 , x_return_status => l_return_status
1406 , x_number_table => l_serial_numbers_table
1407 );
1408
1409 IF l_return_status <> 'S' THEN
1410 fnd_message.set_name('WMS', 'WMS_GET_SER_CUR_FAILED');
1411 fnd_msg_pub.ADD;
1412 RETURN;
1413 END IF;
1414
1415 IF (l_debug = 1) THEN
1416 TRACE(' Number of SN in l_serial_numbers_table ' || l_serial_numbers_table.COUNT);
1417 END IF;
1418
1419 l_serial_number := l_serial_numbers_table(1);
1420 END IF;
1421 ELSIF (p_label_type_info.business_flow_code IN(33) AND p_transaction_identifier = 1) THEN
1422
1423 IF (l_debug = 1) THEN
1424 trace(' WIP - LPN work orderless completion business flow.');
1425 END IF;
1426
1427 OPEN wip_lpn_serial_cur;
1428 FETCH wip_lpn_serial_cur
1429 INTO l_inventory_item_id
1430 , l_organization_id
1431 , l_lot_number
1432 , l_project_id
1433 , l_task_id
1434 , l_revision
1435 , l_serial_number
1436 , l_subinventory
1437 , l_uom
1438 , l_locator_id;
1439
1440 IF (l_debug = 1) THEN
1441 trace('l_serial_number : ' || l_serial_number);
1442 END IF;
1443
1444 IF wip_lpn_serial_cur%NOTFOUND THEN
1445 IF (l_debug = 1) THEN
1446 trace('No record returned from wip_lpn_serial_cur cursor');
1447 END IF;
1448 CLOSE wip_lpn_serial_cur;
1449 RETURN;
1450 END IF;
1451
1452 ELSIF p_label_type_info.business_flow_code IN(21) THEN
1453 -- Ship Confirm
1454 -- The delivery_id has being passed. Delivery ID passed means that all the delivery details ID have
1455 -- to be derived for the delivery ID. There will be one record per serial number in the wsh_delivery_details.
1456 -- The cost group will be derived from the table wms_license_plate_numbers for the LPN stamped on the Delivery_detail_id.
1457 IF (l_debug = 1) THEN
1458 TRACE(' Ship Confirm Flow with Delivery ID: ' || p_transaction_id);
1459 END IF;
1460 OPEN wda_serial_cur;
1461 FETCH wda_serial_cur
1462 INTO l_inventory_item_id
1463 , l_organization_id
1464 , l_lot_number
1465 , l_project_id
1466 , l_task_id
1467 , l_revision
1468 , l_serial_number
1469 , l_fm_serial_number
1470 , l_to_serial_number
1471 , l_subinventory
1472 , l_uom;
1473
1474 IF wda_serial_cur%NOTFOUND THEN
1475 IF (l_debug = 1) THEN
1476 TRACE(' No Serial number found for this given ID:' || p_transaction_id);
1477 END IF;
1478
1479 CLOSE wda_serial_cur;
1480
1481 RETURN;
1482 --Start of New code to fix Bug# 4290536
1483 ELSE
1484 IF (l_debug = 1) THEN
1485 TRACE(' Found Serial Number for the given ID: ' || p_transaction_id||'; l_serial_number: '||l_serial_number||'; l_fm_serial_number: '||l_fm_serial_number||'; l_to_serial_number: '||l_to_serial_number);
1486 END IF;
1487 IF l_fm_serial_number IS NOT NULL THEN
1488 -- Gett range of Serial Numbers
1489 inv_label.get_number_between_range(
1490 fm_x_number => l_fm_serial_number
1491 , to_x_number => l_to_serial_number
1492 , x_return_status => l_return_status
1493 , x_number_table => l_serial_numbers_table);
1494
1495 IF l_return_status <> 'S' THEN
1496 FND_MESSAGE.SET_NAME('WMS', 'WMS_GET_SER_CUR_FAILED');
1497 FND_MSG_PUB.ADD;
1498 RETURN;
1499 END IF;
1500
1501 IF (l_debug = 1) THEN
1502 TRACE(' Number of SN in l_serial_numbers_table ' || l_serial_numbers_table.COUNT);
1503 END IF;
1504 l_serial_number := l_serial_numbers_table(1);
1505 END IF;
1506 --End of Newly added code to fix Bug# 4290536
1507 END IF;
1508 -- Bug Number: 3896738
1509 -- Added the business flow Manufacturing Cross-Dock(37)
1510 ELSIF p_label_type_info.business_flow_code IN(26, 37) THEN
1511 -- WIP Completion.
1512 -- Bug 2825748 : Material Label Is Not Printed On WIP Completion.
1513 -- LPN Completions:
1514 -- In this case a record is populated in the MMTT with the item populated in the
1515 -- MMTT.inventorry_item_id and the LLPN populated in the MMTT.transfer_lpn_id.
1516 -- As per the WIP team, the LPN is packed before label printing is called
1517 -- For every item of the completion, one record
1518 -- is inserted into the MMTT (with the MMTT.TRANSFER_LPN_ID ) populated and
1519 -- label printing is called. Serial Labels are printed for the completed items
1520 --
1521 -- Non-LPN Completion
1522 -- In this case a record is populated in the MMTT with the item populated in the
1523 -- MMTT.inventory_item_id with all the related inforamtion.
1524 OPEN wip_serial_cur;
1525
1526 FETCH wip_serial_cur
1527 INTO l_inventory_item_id
1528 , l_organization_id
1529 , l_lot_number
1530 , l_cost_group_id
1531 , l_project_id
1532 , l_task_id
1533 , l_uom
1534 , l_revision
1535 , l_fm_serial_number
1536 , l_to_serial_number
1537 , l_subinventory
1538 , l_locator_id
1539 , l_wip_entity_name --Added for Bug 4642062
1540 , l_wip_entity_id;
1541
1542 TRACE(
1543 ' wip_serial_cur '
1544 || ', Item ID='
1545 || l_inventory_item_id
1546 || ', Organization ID='
1547 || l_organization_id
1548 || ', Lot Number='
1549 || l_lot_number
1550 || ', Project ID='
1551 || l_project_id
1552 || ', Cost Group ID='
1553 || l_cost_group_id
1554 || ', Task ID='
1555 || l_task_id
1556 || ', Transaction UOM='
1557 || l_uom
1558 || ', Item Revision='
1559 || l_revision
1560 || ', Subinventory Code='
1561 || l_subinventory
1562 || ', Locator ID='
1563 || l_locator_id
1564 || ', Job Name='
1565 || l_wip_entity_name
1566 || ', Job Id='
1567 || l_wip_entity_id
1568 );
1569
1570 IF wip_serial_cur%NOTFOUND THEN
1571 TRACE(' No records found for transaction_temp_id in MMTT');
1572
1573 CLOSE wip_serial_cur;
1574 ELSE
1575 inv_label.get_number_between_range(
1576 fm_x_number => l_fm_serial_number
1577 , to_x_number => l_to_serial_number
1578 , x_return_status => l_return_status
1579 , x_number_table => l_serial_numbers_table
1580 );
1581
1582 IF l_return_status <> 'S' THEN
1583 fnd_message.set_name('WMS', 'WMS_GET_SER_CUR_FAILED');
1584 fnd_msg_pub.ADD;
1585 RETURN;
1586 END IF;
1587
1588 TRACE(' Count of rows in l_serial_numbers_table ' || l_serial_numbers_table.COUNT);
1589 l_serial_number := l_serial_numbers_table(1);
1590 TRACE('l_serial_number after call to GET_SERIALS_BETWEEN_RANGE ' || l_serial_number);
1591 END IF;
1592 -- Flow Labels
1593 ELSIF p_label_type_info.business_flow_code IN(33)
1594 AND p_transaction_identifier > 1 THEN
1595 -- Flow Completion, not MMTT based
1596 IF p_transaction_identifier = 2 THEN
1597 OPEN flow_serial_curs_mti;
1598
1599 FETCH flow_serial_curs_mti
1600 INTO l_inventory_item_id
1601 , l_organization_id
1602 , l_lot_number
1603 , l_project_id
1604 , l_task_id
1605 , l_revision
1606 , l_fm_serial_number
1607 , l_to_serial_number
1608 , l_subinventory
1609 , l_locator_id -- Added for Bug #5533362
1610 , l_uom;
1611
1612 IF flow_serial_curs_mti%NOTFOUND THEN
1613 IF (l_debug = 1) THEN
1614 TRACE(' No Flow Data found for this given ID:' || p_transaction_id);
1615 END IF;
1616
1617 CLOSE flow_serial_curs_mti;
1618
1619 RETURN;
1620 ELSE
1621 inv_label.get_number_between_range(
1622 fm_x_number => l_fm_serial_number
1623 , to_x_number => l_to_serial_number
1624 , x_return_status => l_return_status
1625 , x_number_table => l_serial_numbers_table
1626 );
1627
1628 IF l_return_status <> 'S' THEN
1629 fnd_message.set_name('WMS', 'WMS_GET_SER_CUR_FAILED');
1630 fnd_msg_pub.ADD;
1631 RETURN;
1632 END IF;
1633
1634 IF (l_debug = 1) THEN
1635 TRACE(' Count of rows in l_serial_numbers_table ' || l_serial_numbers_table.COUNT);
1636 END IF;
1637
1638 l_serial_number := l_serial_numbers_table(1);
1639 END IF;
1640 ELSIF p_transaction_identifier = 3 THEN
1641 OPEN flow_serial_curs_mol;
1642
1643 FETCH flow_serial_curs_mol
1644 INTO l_inventory_item_id
1645 , l_organization_id
1646 , l_lot_number
1647 , l_project_id
1648 , l_task_id
1649 , l_revision
1650 , l_fm_serial_number
1651 , l_to_serial_number
1652 , l_subinventory
1653 , l_uom;
1654
1655 IF flow_serial_curs_mol%NOTFOUND THEN
1656 IF (l_debug = 1) THEN
1657 TRACE(' No Flow Data found for this given ID:' || p_transaction_id);
1658 END IF;
1659
1660 CLOSE flow_serial_curs_mol;
1661
1662 RETURN;
1663 ELSE
1664 inv_label.get_number_between_range(
1665 fm_x_number => l_fm_serial_number
1666 , to_x_number => l_to_serial_number
1667 , x_return_status => l_return_status
1668 , x_number_table => l_serial_numbers_table
1669 );
1670
1671 IF l_return_status <> 'S' THEN
1672 fnd_message.set_name('WMS', 'WMS_GET_SER_CUR_FAILED'); -- Code message for this.
1673 fnd_msg_pub.ADD;
1674 RETURN;
1675 END IF;
1676
1677 IF (l_debug = 1) THEN
1678 TRACE(' Count of rows in l_serial_numbers_table ' || l_serial_numbers_table.COUNT);
1679 END IF;
1680
1681 l_serial_number := l_serial_numbers_table(1);
1682 END IF;
1683 ELSE
1684 IF (l_debug = 1) THEN
1685 TRACE(' Invalid transaction_identifier passed' || p_transaction_identifier);
1686 END IF;
1687
1688 RETURN;
1689 END IF;
1690 ELSE
1691 IF (l_debug = 1) THEN
1692 TRACE('No serial number will be printed');
1693 END IF;
1694
1695 RETURN;
1696 END IF;
1697 ELSE
1698 -- On demand, get information from p_input_param
1699 l_organization_id := p_input_param.organization_id;
1700 l_inventory_item_id := p_input_param.inventory_item_id;
1701 l_lot_number := p_input_param.lot_number;
1702 l_serial_number := p_input_param.serial_number;
1703 l_project_id := p_input_param.project_id;
1704 l_task_id := p_input_param.task_id;
1705 l_revision := p_input_param.revision;
1706 l_wip_entity_id := p_input_param.transaction_source_id;
1707 END IF;
1708
1709 OPEN c_project_enabled(l_organization_id);
1710 FETCH c_project_enabled INTO l_is_pjm_org;
1711 IF c_project_enabled%NOTFOUND THEN
1712 IF (l_debug = 1) THEN
1713 trace( 'Organization id ' || l_organization_id || 'is not a PJM Org.');
1714 END IF;
1715 END IF;
1716 CLOSE c_project_enabled;
1717
1718 --Bug #6417575,Label Printing Support for WIP Move Transactions (12.1)
1719 -- Fetching WIP Job attributes, based on wip_entity_id passed through
1720 -- transaction_source_id in manual mode.
1721 -- Currently, printing WIP job information for serial is restricted
1722 -- only for WIP Completion and Serial Label Manual Printing.
1723 IF (l_debug = 1) THEN
1724 trace( 'l_wip_entity_id = ' || l_wip_entity_id);
1725 END IF;
1726 IF (l_wip_entity_id IS NOT NULL) THEN
1727 OPEN wip_attributes_cur;
1728 FETCH wip_attributes_cur INTO l_wip_entity_name
1729 , l_entity_type
1730 , l_net_quantity
1731 , l_scheduled_start_date
1732 , l_scheduled_completion_date
1733 , l_bom_revision
1734 , l_routing_revision;
1735
1736 IF wip_attributes_cur%NOTFOUND THEN
1737 IF (l_debug = 1) THEN
1738 trace( ' No records returned by wip_attributes_cur cursor');
1739 END IF;
1740 END IF;
1741 CLOSE wip_attributes_cur;
1742 END IF;
1743
1744 /*
1745 * The following code has been added so that the c_project and c_task cursors will be opened
1746 * only if the organization is project enabled.
1747 */
1748
1749 IF l_is_pjm_org = 'Y' THEN
1750 OPEN c_project;
1751
1752 -- Fix for 4907062. Fetching project number along with project name
1753 FETCH c_project INTO l_project_name, l_project_number;
1754
1755 IF c_project%NOTFOUND THEN
1756 l_project_name := '';
1757 END IF;
1758
1759 CLOSE c_project;
1760
1761 OPEN c_task;
1762
1763 -- Fix for 4907062. Fetching task number along with project name
1764 FETCH c_task INTO l_task_name, l_task_number;
1765
1766 IF c_task%NOTFOUND THEN
1767 l_task_name := '';
1768 END IF;
1769
1770 CLOSE c_task;
1771 END IF;
1772
1773
1774 /* Oherwise, it does not need to do that
1775
1776 -- Get variables defined for the format id passed in.
1777 inv_label.get_variables_for_format
1778 (
1779 x_variables => l_selected_fields
1780 , x_variables_count => l_selected_fields_count
1781 , x_is_variable_exist => l_is_epc_exist
1782 , p_format_id => p_label_type_info.default_format_id
1783 , p_exist_variable_name => 'EPC'
1784 );
1785
1786 IF (l_selected_fields_count = 0)
1787 OR(l_selected_fields.COUNT = 0) THEN
1788 IF (l_debug = 1) THEN
1789 TRACE('no fields defined for this format: ' || p_label_type_info.default_format_id || ',' || p_label_type_info.default_format_name);
1790 END IF;
1791 END IF;
1792
1793
1794 */
1795
1796
1797 IF (l_debug = 1) THEN
1798 TRACE('** in PVT2.get_variable_dataa ** , start ');
1799 END IF;
1800
1801 l_serial_data := '';
1802 l_serial_table_index := 1;
1803
1804 IF (l_debug = 1) THEN
1805 TRACE('l_serial_number before WHILE LOOP ' || l_serial_number);
1806 END IF;
1807
1808 l_serial_loop_count := 1;
1809 l_label_index := 1;
1810 l_prev_format_id := -999;
1811 l_printer := p_label_type_info.default_printer;
1812 l_prev_sub := '####';
1813
1814 WHILE l_serial_number IS NOT NULL LOOP
1815 IF (l_debug = 1) THEN
1816 TRACE(
1817 'org_id='
1818 || l_organization_id
1819 || ',item_id='
1820 || l_inventory_item_id
1821 || ',lot='
1822 || l_lot_number
1823 || ',serial='
1824 || l_serial_number
1825 || ',project_id='
1826 || l_project_id
1827 || ',task_id='
1828 || l_task_id
1829 || ',revision='
1830 || l_revision
1831 );
1832 END IF;
1833
1834 /* Start of fix for bug # 4947399 */
1835 IF (p_label_type_info.business_flow_code IN (13, 23, 26)) THEN
1836 OPEN c_lot_serial_attributes;
1837 FETCH c_lot_serial_attributes INTO l_lot_c_attribute1
1838 , l_lot_c_attribute2
1839 , l_lot_c_attribute3
1840 , l_lot_c_attribute4
1841 , l_lot_c_attribute5
1842 , l_lot_c_attribute6
1843 , l_lot_c_attribute7
1844 , l_lot_c_attribute8
1845 , l_lot_c_attribute9
1846 , l_lot_c_attribute10
1847 , l_lot_c_attribute11
1848 , l_lot_c_attribute12
1849 , l_lot_c_attribute13
1850 , l_lot_c_attribute14
1851 , l_lot_c_attribute15
1852 , l_lot_c_attribute16
1853 , l_lot_c_attribute17
1854 , l_lot_c_attribute18
1855 , l_lot_c_attribute19
1856 , l_lot_c_attribute20
1857 , l_lot_d_attribute1
1858 , l_lot_d_attribute2
1859 , l_lot_d_attribute3
1860 , l_lot_d_attribute4
1861 , l_lot_d_attribute5
1862 , l_lot_d_attribute6
1863 , l_lot_d_attribute7
1864 , l_lot_d_attribute8
1865 , l_lot_d_attribute9
1866 , l_lot_d_attribute10
1867 , l_lot_n_attribute1
1868 , l_lot_n_attribute2
1869 , l_lot_n_attribute3
1870 , l_lot_n_attribute4
1871 , l_lot_n_attribute5
1872 , l_lot_n_attribute6
1873 , l_lot_n_attribute7
1874 , l_lot_n_attribute8
1875 , l_lot_n_attribute9
1876 , l_lot_n_attribute10
1877 , l_serial_c_attribute1
1878 , l_serial_c_attribute2
1879 , l_serial_c_attribute3
1880 , l_serial_c_attribute4
1881 , l_serial_c_attribute5
1882 , l_serial_c_attribute6
1883 , l_serial_c_attribute7
1884 , l_serial_c_attribute8
1885 , l_serial_c_attribute9
1886 , l_serial_c_attribute10
1887 , l_serial_c_attribute11
1888 , l_serial_c_attribute12
1889 , l_serial_c_attribute13
1890 , l_serial_c_attribute14
1891 , l_serial_c_attribute15
1892 , l_serial_c_attribute16
1893 , l_serial_c_attribute17
1894 , l_serial_c_attribute18
1895 , l_serial_c_attribute19
1896 , l_serial_c_attribute20
1897 , l_serial_d_attribute1
1898 , l_serial_d_attribute2
1899 , l_serial_d_attribute3
1900 , l_serial_d_attribute4
1901 , l_serial_d_attribute5
1902 , l_serial_d_attribute6
1903 , l_serial_d_attribute7
1904 , l_serial_d_attribute8
1905 , l_serial_d_attribute9
1906 , l_serial_d_attribute10
1907 , l_serial_n_attribute1
1908 , l_serial_n_attribute2
1909 , l_serial_n_attribute3
1910 , l_serial_n_attribute4
1911 , l_serial_n_attribute5
1912 , l_serial_n_attribute6
1913 , l_serial_n_attribute7
1914 , l_serial_n_attribute8
1915 , l_serial_n_attribute9
1916 , l_serial_n_attribute10;
1917 IF c_lot_serial_attributes%NOTFOUND THEN
1918 IF (l_debug = 1) THEN
1919 TRACE(' No records returned by c_lot_serial_attributes cursor');
1920 END IF;
1921 END IF;
1922 CLOSE c_lot_serial_attributes;
1923 END IF;
1924 /* End of fix for bug # 4947399 */
1925
1926 FOR serial_rec IN serial_cur LOOP
1927 l_serial_data := '';
1928
1929 IF (l_debug = 1) THEN
1930 TRACE(' ^^^New label ^^^');
1931 TRACE(' Serial Number: ' || l_serial_number);
1932 END IF;
1933 l_label_status := INV_LABEL.G_SUCCESS;
1934
1935 --In R12 moved this Rules engine call before the call to get printer
1936 /* insert a record into wms_label_requests entity to
1937 call the label rules engine to get appropriate label
1938 In this call if this happens to be for the label-set, the record
1939 from wms_label_request will be deleted inside following API*/
1940
1941 IF (l_debug = 1) THEN
1942 TRACE(' 1. Apply Rules engine get label set or format');
1943 END IF;
1944
1945 inv_label.get_format_with_rule(
1946 p_document_id => p_label_type_info.label_type_id
1947 , p_label_format_id => p_label_type_info.manual_format_id
1948 , p_organization_id => serial_rec.organization_id
1949 , p_inventory_item_id => serial_rec.inventory_item_id
1950 , p_lot_number => serial_rec.lot_number
1951 , p_serial_number => serial_rec.serial_number
1952 , p_revision => serial_rec.revision
1953 , p_business_flow_code => p_label_type_info.business_flow_code
1954 --, p_printer_name => l_printer --Blocked in R12 RFID project
1955 , p_last_update_date => SYSDATE
1956 , p_last_updated_by => fnd_global.user_id
1957 , p_creation_date => SYSDATE
1958 , p_created_by => fnd_global.user_id
1959 -- Added for Bug 2748297 Start
1960 , p_supplier_id => l_vendor_id
1961 , p_supplier_site_id => l_vendor_site_id
1962 -- End
1963 -- Added for bug 4582954 Start
1964 , p_sales_order_header_id => l_oe_order_header_id
1965 , p_sales_order_line_id => l_oe_order_line_id
1966 -- End bug 4582954
1967 , x_return_status => l_return_status
1968 , x_label_format_id => l_label_format_set_id
1969 , x_label_format => l_label_format
1970 , x_label_request_id => l_label_request_id
1971 );
1972
1973 IF l_return_status <> 'S' THEN
1974 fnd_message.set_name('WMS', 'WMS_LABL_RULE_ENGINE_FAILED');
1975 fnd_msg_pub.ADD;
1976 l_label_format_set_id := p_label_type_info.default_format_id;
1977 l_label_format := p_label_type_info.default_format_name;
1978 END IF;
1979
1980 IF (l_debug = 1) THEN
1981 TRACE('did apply label ' || l_label_format || ',' || l_label_format_set_id || ',req_id ' || l_label_request_id);
1982 END IF;
1983
1984
1985 --for manual printer, l_label_format_set_id returned from above API
1986 --will be infact p_label_type_info.manual_format_id which can be a
1987 --label set or a label format
1988
1989 --Added in R12 for Label sets with RFID
1990 --l_label_format_set_idreturned by the rules engine can be either a
1991 --label format OR a label set
1992
1993 IF (l_debug = 1) THEN
1994 TRACE('^^^^^^^^^^^^^^^^Label-sets^^^^^^^^^^^^');
1995 TRACE(' looping though formats in set begins, format_id/set_id :'||l_label_format_set_id);
1996 END IF;
1997
1998
1999 -- SET or just the current format
2000
2001 FOR l_label_formats_in_set IN c_label_formats_in_set(l_label_format_set_id) LOOP
2002
2003 IF (l_debug = 1) THEN
2004 TRACE(' Format_id for Current set :'||l_label_formats_in_set.label_format_id);
2005 END IF;
2006
2007 --CODE logic
2008 -- If it is label-SET then
2009 ---- after getting all the formats inside a label SET calling the
2010 ----get_format_with_rule() is same. Just need to
2011 ----1 Insert record into WMS_LABEL_REQUESTS
2012 ----2 get value of l_label_format_id, l_label_format, l_label_request_id
2013 ----3 Do not call Rules Engine again, as we know format id
2014 --else
2015 ----Do not call get_format_with_rule(), just use the format-id
2016
2017 IF l_label_formats_in_set.label_entity_type = 1 THEN --IT IS LABEL SET
2018
2019 --In R12 call this API for the format AGAIN without calling Rules ENGINE
2020 /* insert a record into wms_label_requests entity */
2021
2022
2023 IF (l_debug = 1) THEN
2024 TRACE('Insert record into WMS_LABEL_REQUESTS and get label_request_id');
2025 END IF;
2026
2027 inv_label.get_format_with_rule
2028 ( p_document_id => p_label_type_info.label_type_id
2029 , p_label_format_id => l_label_formats_in_set.label_format_id --considers manual printer also
2030 , p_organization_id => serial_rec.organization_id
2031 , p_inventory_item_id => serial_rec.inventory_item_id
2032 , p_lot_number => serial_rec.lot_number
2033 , p_serial_number => serial_rec.serial_number
2034 , p_revision => serial_rec.revision
2035 , p_business_flow_code => p_label_type_info.business_flow_code
2036 --, p_printer_name => l_printer --Blocked in R12 RFID project
2037 , p_last_update_date => SYSDATE
2038 , p_last_updated_by => fnd_global.user_id
2039 , p_creation_date => SYSDATE
2040 , p_created_by => fnd_global.user_id
2041 , p_use_rule_engine => 'N' --------------------------Rules ENgine will NOT get called
2042 ,
2043 -- Added for Bug 2748297 Start
2044 p_supplier_id => l_vendor_id
2045 , p_supplier_site_id => l_vendor_site_id
2046 ,
2047 -- End
2048 x_return_status => l_return_status
2049 , x_label_format_id => l_label_format_id
2050 , x_label_format => l_label_format
2051 , x_label_request_id => l_label_request_id
2052 );
2053
2054 IF l_return_status <> 'S' THEN
2055 fnd_message.set_name('WMS', 'WMS_LABL_RULE_ENGINE_FAILED');
2056 fnd_msg_pub.ADD;
2057 l_label_format_id := p_label_type_info.default_format_id;
2058 l_label_format := p_label_type_info.default_format_name;
2059 END IF;
2060
2061 IF (l_debug = 1) THEN
2062 TRACE('did apply label ' || l_label_format || ',' || l_label_format_id || ',req_id ' || l_label_request_id);
2063 END IF;
2064
2065
2066 ELSE --IT IS LABEL FORMAT
2067 --Just use the format-id returned
2068
2069 l_label_format_id := l_label_formats_in_set.label_format_id ;
2070
2071 END IF;
2072
2073
2074 IF (l_debug = 1) THEN
2075 TRACE(
2076 ' Getting printer, manual_printer='
2077 || p_label_type_info.manual_printer
2078 || ',sub='
2079 || l_subinventory
2080 || ',default printer='
2081 || p_label_type_info.default_printer
2082 );
2083 END IF;
2084
2085
2086 -- IF clause Added for Add format/printer for manual request
2087 IF p_label_type_info.manual_printer IS NULL THEN
2088 -- The p_label_type_info.manual_printer is the one passed from the manual page.
2089 -- As per the design, if a printer is passed from the manual page, then we use that printer irrespective.
2090 IF (l_subinventory IS NOT NULL)
2091 AND(l_subinventory <> l_prev_sub) THEN
2092 IF (l_debug = 1) THEN
2093 TRACE('getting printer with sub ' || l_subinventory);
2094 END IF;
2095
2096 BEGIN
2097 wsh_report_printers_pvt.get_printer
2098 (
2099 p_concurrent_program_id => p_label_type_info.label_type_id
2100 , p_user_id => fnd_global.user_id
2101 , p_responsibility_id => fnd_global.resp_id
2102 , p_application_id => fnd_global.resp_appl_id
2103 , p_organization_id => l_organization_id
2104 , p_zone => l_subinventory
2105 , p_format_id => l_label_format_id --added in R12
2106 , x_printer => l_printer
2107 , x_api_status => l_api_status
2108 , x_error_message => l_error_message
2109 );
2110
2111 IF l_api_status <> 'S' THEN
2112 IF (l_debug = 1) THEN
2113 TRACE('Error in calling get_printer, set printer as default printer, err_msg:' || l_error_message);
2114 END IF;
2115
2116 l_printer := p_label_type_info.default_printer;
2117 END IF;
2118 EXCEPTION
2119 WHEN OTHERS THEN
2120 l_printer := p_label_type_info.default_printer;
2121 END;
2122
2123 l_prev_sub := l_subinventory;
2124 END IF;
2125 ELSE
2126 IF (l_debug = 1) THEN
2127 TRACE('Set printer as Manual Printer passed in:' || p_label_type_info.manual_printer);
2128 END IF;
2129
2130 l_printer := p_label_type_info.manual_printer;
2131 END IF;
2132
2133 IF (l_debug = 1) THEN
2134 TRACE(
2135 'Apply Rules engine for format, printer='
2136 || l_printer
2137 || ',manual_format_id='
2138 || p_label_type_info.manual_format_id
2139 || ',manual_format_name='
2140 || p_label_type_info.manual_format_name
2141 );
2142 END IF;
2143
2144
2145
2146 IF (l_label_format_id IS NOT NULL) THEN
2147 -- Derive the fields for the format either passed in or derived via the rules engine.
2148 IF l_label_format_id <> NVL(l_prev_format_id, -999) THEN
2149 IF (l_debug = 1) THEN
2150 TRACE(' Getting variables for new format ' || l_label_format);
2151 END IF;
2152 --changed in R12
2153 inv_label.get_variables_for_format
2154 (x_variables => l_selected_fields
2155 , x_variables_count => l_selected_fields_count
2156 , x_is_variable_exist => l_is_epc_exist
2157 , p_format_id => l_label_format_id
2158 , p_exist_variable_name => 'EPC'
2159 );
2160
2161 l_prev_format_id := l_label_format_id;
2162
2163 IF (l_selected_fields_count = 0)
2164 OR(l_selected_fields.COUNT = 0) THEN
2165 IF (l_debug = 1) THEN
2166 TRACE('no fields defined for this format: ' || l_label_format || ',' || l_label_format_id);
2167 TRACE('######## GOING TO THE NEXT LABEL####');
2168 END IF;
2169
2170 GOTO nextlabel;
2171 END IF;
2172
2173 IF (l_debug = 1) THEN
2174 TRACE(' Found selected_fields for format ' || l_label_format || ', num=' || l_selected_fields_count);
2175 END IF;
2176 END IF;
2177 ELSE
2178 IF (l_debug = 1) THEN
2179 TRACE('No format exists for this label, goto nextlabel');
2180 END IF;
2181
2182 GOTO nextlabel;
2183 END IF;
2184
2185 -- Added for UCC 128 J Bug #3067059
2186 inv_label.is_item_gtin_enabled(
2187 x_return_status => l_return_status
2188 , x_gtin_enabled => l_gtin_enabled
2189 , x_gtin => l_gtin
2190 , x_gtin_desc => l_gtin_desc
2191 , p_organization_id => l_organization_id
2192 , p_inventory_item_id => l_inventory_item_id
2193 , p_unit_of_measure => l_uom
2194 , p_revision => l_revision
2195 );
2196
2197
2198 -- Added in R12 RFID compliance project
2199 -- Get RFID/EPC related information for a format
2200 -- Only do this if EPC is a field included in the format
2201 IF l_is_epc_exist = 'Y' THEN
2202 IF (l_debug =1) THEN
2203 trace('Generating EPC');
2204 END IF;
2205
2206 BEGIN
2207
2208 -- Added in R12 RFID compliance
2209 -- New field : EPC
2210 -- When generate_epc API returns E (expected error) or U(expected error),
2211 -- it sets the error message, but generate xml with EPC as null
2212
2213 WMS_EPC_PVT.generate_epc
2214 (p_org_id => l_organization_id,
2215 p_label_type_id => p_label_type_info.label_type_id, -- 2
2216 p_group_id => inv_label.EPC_group_id,
2217 p_label_format_id => l_label_format_id,
2218 p_label_request_id => l_label_request_id,
2219 p_business_flow_code => p_label_type_info.business_flow_code,
2220 x_epc => l_epc,
2221 x_return_status => l_epc_ret_status, -- S / E / U
2222 x_return_mesg => l_epc_ret_msg
2223 );
2224
2225 IF (l_debug = 1) THEN
2226 trace('Called generate_epc with ');
2227 trace('p_group_id='||inv_label.epc_group_id);
2228 trace('l_label_format_id='||l_label_format_id||',p_user_id='||fnd_global.user_id);
2229 trace('p_org_id='||l_organization_id);
2230 trace('l_label_request_id= '||l_label_request_id);
2231 trace('x_epc='||l_epc);
2232 trace('x_return_status='||l_epc_ret_status);
2233 trace('x_return_mesg=' ||l_epc_ret_msg);
2234 END IF;
2235
2236 IF l_epc_ret_status = 'S' THEN
2237 -- Success
2238 IF (l_debug = 1) THEN
2239 trace('Succesfully generated EPC '||l_epc);
2240 END IF;
2241 ELSIF l_epc_ret_status = 'U' THEN
2242 -- Unexpected error
2243 l_epc := null;
2244 IF(l_debug = 1) THEN
2245 trace('Got unexpected error from generate_epc, msg='||l_epc_ret_msg);
2246 trace('Set label status as Error and l_epc = null');
2247 END IF;
2248
2249 ELSIF l_epc_ret_status = 'E' THEN
2250 -- Expected error
2251 l_epc := null;
2252 IF(l_debug = 1) THEN
2253 trace('Got expected error from generate_epc, msg='||l_epc_ret_msg);
2254 trace('Set label status as Warning and l_epc = null');
2255 END IF;
2256 ELSE
2257 trace('generate_epc returned a status that is not recognized, set epc as null');
2258 l_epc := null;
2259 END IF;
2260 -- End Bug
2261
2262 EXCEPTION
2263 WHEN no_data_found THEN
2264 IF(l_debug =1 ) THEN
2265 trace('No format found when retrieving EPC information. Format_id='||l_label_format_id);
2266 END IF;
2267 WHEN others THEN
2268 IF(l_debug =1 ) THEN
2269 trace('Other error when retrieving EPC information. Format_id='||l_label_format_id);
2270 END IF;
2271 END;
2272
2273 END IF;
2274
2275
2276
2277 /* variable header */
2278 l_serial_data := l_serial_data || label_b;
2279
2280 IF l_label_format <> NVL(p_label_type_info.default_format_name, '@@@') THEN
2281 l_serial_data := l_serial_data || ' _FORMAT="' || l_label_format || '"';
2282 END IF;
2283
2284 IF (l_printer IS NOT NULL)
2285 AND(l_printer <> NVL(p_label_type_info.default_printer, '###')) THEN
2286 l_serial_data := l_serial_data || ' _PRINTERNAME="' || l_printer || '"';
2287 END IF;
2288
2289 l_serial_data := l_serial_data || tag_e;
2290
2291 /* added by incvonv project - start */
2292 open get_lot_info;
2293 FETCH get_lot_info into l_parent_lot_number,
2294 l_expiration_action_date ,
2295 l_expiration_action_code,
2296 l_hold_date ,
2297 l_supplier_lot_number,
2298 l_origination_type ,
2299 l_grade_code,
2300 l_maturity_date,
2301 l_retest_date,
2302 l_expiration_date,
2303 l_origination_date,
2304 l_lot_status; --- Bug 4355080
2305
2306 IF get_lot_info%NOTFOUND
2307 THEN
2308 IF (l_debug = 1) THEN
2309 trace('No lot record was found in MLN for lot, '|| l_lot_number );
2310 trace('Lot must be new.' );
2311 END IF;
2312
2313 -- since lot is new, lot attributes must exists on MTLT
2314 OPEN mtlt_lot_info_cur;
2315 FETCH mtlt_lot_info_cur INTO l_parent_lot_number,
2316 l_expiration_action_date ,
2317 l_expiration_action_code,
2318 l_hold_date ,
2319 l_supplier_lot_number,
2320 l_origination_type ,
2321 l_grade_code,
2322 l_maturity_date,
2323 l_retest_date,
2324 l_expiration_date,
2325 l_origination_date,
2326 l_lot_status; --- Bug 4355080
2327
2328 IF mtlt_lot_info_cur%NOTFOUND
2329 THEN
2330 IF (l_debug = 1) THEN
2331 trace('No lot record was found also in MTLT for lot , '|| l_lot_number ||
2332 ', transaction_temp_id = ' || p_input_param.transaction_temp_id);
2333 END IF;
2334 END IF;
2335 --
2336 CLOSE mtlt_lot_info_cur; --added along with Bugfix 4290536
2337 --
2338 END IF; -- cursor not found
2339 --
2340 CLOSE get_lot_info; --added along with Bugfix 4290536
2341 --
2342 IF (l_debug = 1) THEN
2343 trace(' fabdi ');
2344 trace(' Item , ' || serial_rec.item );
2345 trace(' lot_number, ' || serial_rec.lot_number);
2346 trace(' parent_lot_number, ' || l_parent_lot_number);
2347 trace(' grade code, ' || l_grade_code);
2348 trace(' expiration action date , ' || l_expiration_action_date);
2349 trace(' expiration action code , ' || l_expiration_action_code);
2350 trace(' origination date , ' || l_origination_date);
2351 END IF;
2352
2353 /* added by incvonv project - END */
2354
2355 IF (l_debug = 1) THEN
2356 TRACE('Starting assign variables, ');
2357 END IF;
2358
2359 l_column_name_list := 'Set variables for ';
2360
2361 /* Modified for Bug 4072474 -start*/
2362 l_custom_sql_ret_status := FND_API.G_RET_STS_SUCCESS;
2363 /* Modified for Bug 4072474 -End*/
2364
2365 -- Fix for bug: 4179593 Start
2366 l_CustSqlWarnFlagSet := FALSE;
2367 l_CustSqlErrFlagSet := FALSE;
2368 l_CustSqlWarnMsg := NULL;
2369 l_CustSqlErrMsg := NULL;
2370 -- Fix for bug: 4179593 End
2371
2372 -- Loop for each selected fields, find the columns and write into the XML_content
2373 FOR i IN 1 .. l_selected_fields.COUNT LOOP
2374 IF (l_debug = 1) THEN
2375 l_column_name_list := l_column_name_list || ',' || l_selected_fields(i).column_name;
2376 END IF;
2377
2378 ---------------------------------------------------------------------------------------------
2379 -- Project: 'Custom Labels' (A 11i10+ Project) |
2380 -- Author: Dinesh ([email protected]) |
2381 -- Change Description: |
2382 -- The check (SQL_STMT <> NULL and COLUMN_NAME = NULL) implies that the field is a |
2383 -- Custom SQL based field. Handle it appropriately. |
2384 ---------------------------------------------------------------------------------------------
2385
2386 IF (l_selected_fields(i).SQL_STMT IS NOT NULL AND l_selected_fields(i).column_name = 'sql_stmt') THEN
2387 IF (l_debug = 1) THEN
2388 trace('Custom Labels Trace [INVLAP2B.pls]: ------------------------- REPORT BEGIN-------------------------------------');
2389 trace('Custom Labels Trace [INVLAP2B.pls]: LABEL_FIELD_ID : ' || l_selected_fields(i).label_field_id);
2390 trace('Custom Labels Trace [INVLAP2B.pls]: FIELD_VARIABLE_NAME : ' || l_selected_fields(i).variable_name);
2391 trace('Custom Labels Trace [INVLAP2B.pls]: COLUMN_NAME : ' || l_selected_fields(i).column_name);
2392 trace('Custom Labels Trace [INVLAP2B.pls]: SQL_STMT : ' || l_selected_fields(i).sql_stmt);
2393 END IF;
2394 l_sql_stmt := l_selected_fields(i).sql_stmt;
2395 IF (l_debug = 1) THEN
2396 trace('Custom Labels Trace [INVLAP2B.pls]: l_sql_stmt BEFORE REQUEST_ID Filter Concatenation: ' || l_sql_stmt);
2397 END IF;
2398 l_sql_stmt := l_sql_stmt || ' AND WLR.LABEL_REQUEST_ID = :REQUEST_ID';
2399 IF (l_debug = 1) THEN
2400 trace('Custom Labels Trace [INVLAP2B.pls]: l_sql_stmt AFTER REQUEST_ID Filter Concatenation: ' || l_sql_stmt);
2401 END IF;
2402 BEGIN
2403 IF (l_debug = 1) THEN
2404 trace('Custom Labels Trace [INVLAP2B.pls]: At Breadcrumb 1');
2405 trace('Custom Labels Trace [INVLAP2B.pls]: LABEL_REQUEST_ID : ' || l_label_request_id);
2406 END IF;
2407 OPEN c_sql_stmt FOR l_sql_stmt using l_label_request_id;
2408 LOOP
2409 FETCH c_sql_stmt INTO l_sql_stmt_result;
2410 EXIT WHEN c_sql_stmt%notfound OR c_sql_stmt%rowcount >=2;
2411 END LOOP;
2412
2413 IF (c_sql_stmt%rowcount=1 AND l_sql_stmt_result IS NULL) THEN
2414 x_return_status := FND_API.G_RET_STS_SUCCESS;
2415 l_custom_sql_ret_status := INV_LABEL.G_WARNING;
2416 fnd_message.set_name('WMS','WMS_CS_NULL_VALUE_RETURNED');
2417 fnd_msg_pub.ADD;
2418 -- Fix for bug: 4179593 Start
2419 --fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false, p_count => x_msg_count, p_data => l_custom_sql_ret_msg);
2420 l_custom_sql_ret_msg := fnd_msg_pub.get(p_msg_index => fnd_msg_pub.g_last, p_encoded => fnd_api.g_false);
2421 l_CustSqlWarnMsg := l_custom_sql_ret_msg;
2422 l_CustSqlWarnFlagSet := TRUE;
2423 -- Fix for bug: 4179593 End
2424
2425 IF (l_debug = 1) THEN
2426 trace('Custom Labels Trace [INVLAP2B.pls]: At Breadcrumb 2');
2427 trace('Custom Labels Trace [INVLAP2B.pls]: l_sql_stmt_result is: ' || l_sql_stmt_result);
2428 trace('Custom Labels Trace [INVLAP2B.pls]: WARNING: NULL value returned by the custom SQL Query.');
2429 trace('Custom Labels Trace [INVLAP2B.pls]: l_custom_sql_ret_status is set to : ' || l_custom_sql_ret_status);
2430 END IF;
2431 ELSIF c_sql_stmt%rowcount=0 THEN
2432 IF (l_debug = 1) THEN
2433 trace('Custom Labels Trace [INVLAP2B.pls]: At Breadcrumb 3');
2434 trace('Custom Labels Trace [INVLAP2B.pls]: WARNING: No row returned by the Custom SQL query');
2435 END IF;
2436 x_return_status := FND_API.G_RET_STS_SUCCESS;
2437 l_custom_sql_ret_status := INV_LABEL.G_WARNING;
2438 fnd_message.set_name('WMS','WMS_CS_NO_DATA_FOUND');
2439 fnd_msg_pub.ADD;
2440 -- Fix for bug: 4179593 Start
2441 --fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false, p_count => x_msg_count, p_data => l_custom_sql_ret_msg);
2442 l_custom_sql_ret_msg := fnd_msg_pub.get(p_msg_index => fnd_msg_pub.g_last, p_encoded => fnd_api.g_false);
2443 l_CustSqlWarnMsg := l_custom_sql_ret_msg;
2444 l_CustSqlWarnFlagSet := TRUE;
2445 -- Fix for bug: 4179593 End
2446 ELSIF c_sql_stmt%rowcount>=2 THEN
2447 IF (l_debug = 1) THEN
2448 trace('Custom Labels Trace [INVLAP2B.pls]: At Breadcrumb 4');
2449 trace('Custom Labels Trace [INVLAP2B.pls]: ERROR: Multiple values returned by the Custom SQL query');
2450 END IF;
2451 l_sql_stmt_result := NULL;
2452 x_return_status := FND_API.G_RET_STS_SUCCESS;
2453 l_custom_sql_ret_status := FND_API.G_RET_STS_ERROR;
2454 fnd_message.set_name('WMS','WMS_CS_MULTIPLE_VALUES_RETURN');
2455 fnd_msg_pub.ADD;
2456 -- Fix for bug: 4179593 Start
2457 --fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false, p_count => x_msg_count, p_data => l_custom_sql_ret_msg);
2458 l_custom_sql_ret_msg := fnd_msg_pub.get(p_msg_index => fnd_msg_pub.g_last, p_encoded => fnd_api.g_false);
2459 l_CustSqlErrMsg := l_custom_sql_ret_msg;
2460 l_CustSqlErrFlagSet := TRUE;
2461 -- Fix for bug: 4179593 End
2462 END IF;
2463 IF (c_sql_stmt%ISOPEN) THEN
2464 CLOSE c_sql_stmt;
2465 END IF;
2466 EXCEPTION
2467 WHEN OTHERS THEN
2468 IF (c_sql_stmt%ISOPEN) THEN
2469 CLOSE c_sql_stmt;
2470 END IF;
2471 IF (l_debug = 1) THEN
2472 trace('Custom Labels Trace [INVLAP2B.pls]: At Breadcrumb 5');
2473 trace('Custom Labels Trace [INVLAP2B.pls]: Unexpected Error has occured in GET_VARIABLES_DATA');
2474 END IF;
2475 x_return_status := FND_API.G_RET_STS_ERROR;
2476 fnd_message.set_name('WMS','WMS_CS_WRONG_SQL_CONSTRUCT');
2477 fnd_msg_pub.ADD;
2478 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false, p_count => x_msg_count, p_data => x_msg_data);
2479 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2480 END;
2481 IF (l_debug = 1) THEN
2482 trace('Custom Labels Trace [INVLAP2B.pls]: At Breadcrumb 6');
2483 trace('Custom Labels Trace [INVLAP2B.pls]: Before assigning it to l_serial_data');
2484 END IF;
2485 l_serial_data := l_serial_data
2486 || variable_b
2487 || l_selected_fields(i).variable_name
2488 || '">'
2489 || l_sql_stmt_result
2490 || variable_e;
2491 l_sql_stmt_result := NULL;
2492 l_sql_stmt := NULL;
2493 IF (l_debug = 1) THEN
2494 trace('Custom Labels Trace [INVLAP2B.pls]: At Breadcrumb 7');
2495 trace('Custom Labels Trace [INVLAP2B.pls]: After assigning it to l_serial_data');
2496 trace('Custom Labels Trace [INVLAP2B.pls]: --------------------------REPORT END-------------------------------------');
2497 END IF;
2498 ------------------------End of this changes for Custom Labels project code--------------------
2499 ELSIF LOWER(l_selected_fields(i).column_name) = 'current_date' THEN
2500 l_serial_data := l_serial_data || variable_b || l_selected_fields(i).variable_name || '">' || inv_label.g_date || variable_e;
2501 ELSIF LOWER(l_selected_fields(i).column_name) = 'current_time' THEN
2502 l_serial_data := l_serial_data || variable_b || l_selected_fields(i).variable_name || '">' || inv_label.g_time || variable_e;
2503 ELSIF LOWER(l_selected_fields(i).column_name) = 'request_user' THEN
2504 l_serial_data := l_serial_data || variable_b || l_selected_fields(i).variable_name || '">' || inv_label.g_user || variable_e;
2505 ELSIF LOWER(l_selected_fields(i).column_name) = 'item' THEN
2506 l_serial_data := l_serial_data || variable_b || l_selected_fields(i).variable_name || '">' || serial_rec.item || variable_e;
2507 ELSIF LOWER(l_selected_fields(i).column_name) = 'item_description' THEN
2508 l_serial_data :=
2509 l_serial_data || variable_b || l_selected_fields(i).variable_name || '">' || serial_rec.item_description || variable_e;
2510 ELSIF LOWER(l_selected_fields(i).column_name) = 'revision' THEN
2511 l_serial_data :=
2512 l_serial_data || variable_b || l_selected_fields(i).variable_name || '">' || serial_rec.revision || variable_e;
2513 ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_number' THEN
2514 l_serial_data :=
2515 l_serial_data || variable_b || l_selected_fields(i).variable_name || '">' || serial_rec.lot_number || variable_e;
2516 ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_expiration_date' THEN
2517 l_serial_data :=
2518 l_serial_data || variable_b || l_selected_fields(i).variable_name || '">' || l_expiration_date || variable_e;
2519 ELSIF LOWER(l_selected_fields(i).column_name) = 'cost_group' THEN
2520 l_serial_data :=
2521 l_serial_data || variable_b || l_selected_fields(i).variable_name || '">' || serial_rec.cost_group || variable_e;
2522 ELSIF LOWER(l_selected_fields(i).column_name) = 'customer_purchase_order' THEN
2523 l_serial_data := l_serial_data || variable_b || l_selected_fields(i).variable_name || '">' || l_purchase_order || variable_e;
2524 ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_attribute_category' THEN
2525 l_serial_data :=
2526 l_serial_data || variable_b || l_selected_fields(i).variable_name || '">' || serial_rec.lot_attribute_category || variable_e;
2527 ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_c_attribute1' THEN
2528 l_serial_data :=
2529 l_serial_data || variable_b || l_selected_fields(i).variable_name || '">' || serial_rec.lot_c_attribute1 || variable_e;
2530 ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_c_attribute2' THEN
2531 l_serial_data :=
2532 l_serial_data || variable_b || l_selected_fields(i).variable_name || '">' || serial_rec.lot_c_attribute2 || variable_e;
2533 ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_c_attribute3' THEN
2534 l_serial_data :=
2535 l_serial_data || variable_b || l_selected_fields(i).variable_name || '">' || serial_rec.lot_c_attribute3 || variable_e;
2536 ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_c_attribute4' THEN
2537 l_serial_data :=
2538 l_serial_data || variable_b || l_selected_fields(i).variable_name || '">' || serial_rec.lot_c_attribute4 || variable_e;
2539 ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_c_attribute5' THEN
2540 l_serial_data :=
2541 l_serial_data || variable_b || l_selected_fields(i).variable_name || '">' || serial_rec.lot_c_attribute5 || variable_e;
2542 ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_c_attribute6' THEN
2543 l_serial_data :=
2544 l_serial_data || variable_b || l_selected_fields(i).variable_name || '">' || serial_rec.lot_c_attribute6 || variable_e;
2545 ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_c_attribute7' THEN
2546 l_serial_data :=
2547 l_serial_data || variable_b || l_selected_fields(i).variable_name || '">' || serial_rec.lot_c_attribute7 || variable_e;
2548 ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_c_attribute8' THEN
2549 l_serial_data :=
2550 l_serial_data || variable_b || l_selected_fields(i).variable_name || '">' || serial_rec.lot_c_attribute8 || variable_e;
2551 ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_c_attribute9' THEN
2552 l_serial_data :=
2553 l_serial_data || variable_b || l_selected_fields(i).variable_name || '">' || serial_rec.lot_c_attribute9 || variable_e;
2554 ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_c_attribute10' THEN
2555 l_serial_data :=
2556 l_serial_data || variable_b || l_selected_fields(i).variable_name || '">' || serial_rec.lot_c_attribute10 || variable_e;
2557 ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_c_attribute11' THEN
2558 l_serial_data :=
2559 l_serial_data || variable_b || l_selected_fields(i).variable_name || '">' || serial_rec.lot_c_attribute11 || variable_e;
2560 ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_c_attribute12' THEN
2561 l_serial_data :=
2562 l_serial_data || variable_b || l_selected_fields(i).variable_name || '">' || serial_rec.lot_c_attribute12 || variable_e;
2563 ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_c_attribute13' THEN
2564 l_serial_data :=
2565 l_serial_data || variable_b || l_selected_fields(i).variable_name || '">' || serial_rec.lot_c_attribute13 || variable_e;
2566 ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_c_attribute14' THEN
2567 l_serial_data :=
2568 l_serial_data || variable_b || l_selected_fields(i).variable_name || '">' || serial_rec.lot_c_attribute14 || variable_e;
2569 ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_c_attribute15' THEN
2570 l_serial_data :=
2571 l_serial_data || variable_b || l_selected_fields(i).variable_name || '">' || serial_rec.lot_c_attribute15 || variable_e;
2572 ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_c_attribute16' THEN
2573 l_serial_data :=
2574 l_serial_data || variable_b || l_selected_fields(i).variable_name || '">' || serial_rec.lot_c_attribute16 || variable_e;
2575 ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_c_attribute17' THEN
2576 l_serial_data :=
2577 l_serial_data || variable_b || l_selected_fields(i).variable_name || '">' || serial_rec.lot_c_attribute17 || variable_e;
2578 ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_c_attribute18' THEN
2579 l_serial_data :=
2580 l_serial_data || variable_b || l_selected_fields(i).variable_name || '">' || serial_rec.lot_c_attribute18 || variable_e;
2581 ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_c_attribute19' THEN
2582 l_serial_data :=
2583 l_serial_data || variable_b || l_selected_fields(i).variable_name || '">' || serial_rec.lot_c_attribute19 || variable_e;
2584 ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_c_attribute20' THEN
2585 l_serial_data :=
2586 l_serial_data || variable_b || l_selected_fields(i).variable_name || '">' || serial_rec.lot_c_attribute20 || variable_e;
2587 ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_d_attribute1' THEN
2588 l_serial_data :=
2589 l_serial_data || variable_b || l_selected_fields(i).variable_name || '">' || serial_rec.lot_d_attribute1 || variable_e;
2590 ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_d_attribute2' THEN
2591 l_serial_data :=
2592 l_serial_data || variable_b || l_selected_fields(i).variable_name || '">' || serial_rec.lot_d_attribute2 || variable_e;
2593 ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_d_attribute3' THEN
2594 l_serial_data :=
2595 l_serial_data || variable_b || l_selected_fields(i).variable_name || '">' || serial_rec.lot_d_attribute3 || variable_e;
2596 ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_d_attribute4' THEN
2597 l_serial_data :=
2598 l_serial_data || variable_b || l_selected_fields(i).variable_name || '">' || serial_rec.lot_d_attribute4 || variable_e;
2599 ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_d_attribute5' THEN
2600 l_serial_data :=
2601 l_serial_data || variable_b || l_selected_fields(i).variable_name || '">' || serial_rec.lot_d_attribute5 || variable_e;
2602 ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_d_attribute6' THEN
2603 l_serial_data :=
2604 l_serial_data || variable_b || l_selected_fields(i).variable_name || '">' || serial_rec.lot_d_attribute6 || variable_e;
2605 ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_d_attribute7' THEN
2606 l_serial_data :=
2607 l_serial_data || variable_b || l_selected_fields(i).variable_name || '">' || serial_rec.lot_d_attribute7 || variable_e;
2608 ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_d_attribute8' THEN
2609 l_serial_data :=
2610 l_serial_data || variable_b || l_selected_fields(i).variable_name || '">' || serial_rec.lot_d_attribute8 || variable_e;
2611 ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_d_attribute9' THEN
2612 l_serial_data :=
2613 l_serial_data || variable_b || l_selected_fields(i).variable_name || '">' || serial_rec.lot_d_attribute9 || variable_e;
2614 ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_d_attribute10' THEN
2615 l_serial_data :=
2616 l_serial_data || variable_b || l_selected_fields(i).variable_name || '">' || serial_rec.lot_d_attribute10 || variable_e;
2617 ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_n_attribute1' THEN
2618 l_serial_data :=
2619 l_serial_data || variable_b || l_selected_fields(i).variable_name || '">' || serial_rec.lot_n_attribute1 || variable_e;
2620 ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_n_attribute2' THEN
2621 l_serial_data :=
2622 l_serial_data || variable_b || l_selected_fields(i).variable_name || '">' || serial_rec.lot_n_attribute2 || variable_e;
2623 ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_n_attribute3' THEN
2624 l_serial_data :=
2625 l_serial_data || variable_b || l_selected_fields(i).variable_name || '">' || serial_rec.lot_n_attribute3 || variable_e;
2626 ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_n_attribute4' THEN
2627 l_serial_data :=
2628 l_serial_data || variable_b || l_selected_fields(i).variable_name || '">' || serial_rec.lot_n_attribute4 || variable_e;
2629 ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_n_attribute5' THEN
2630 l_serial_data :=
2631 l_serial_data || variable_b || l_selected_fields(i).variable_name || '">' || serial_rec.lot_n_attribute5 || variable_e;
2632 ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_n_attribute6' THEN
2633 l_serial_data :=
2634 l_serial_data || variable_b || l_selected_fields(i).variable_name || '">' || serial_rec.lot_n_attribute6 || variable_e;
2635 ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_n_attribute7' THEN
2636 l_serial_data :=
2637 l_serial_data || variable_b || l_selected_fields(i).variable_name || '">' || serial_rec.lot_n_attribute7 || variable_e;
2638 ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_n_attribute8' THEN
2639 l_serial_data :=
2640 l_serial_data || variable_b || l_selected_fields(i).variable_name || '">' || serial_rec.lot_n_attribute8 || variable_e;
2641 ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_n_attribute9' THEN
2642 l_serial_data :=
2643 l_serial_data || variable_b || l_selected_fields(i).variable_name || '">' || serial_rec.lot_n_attribute9 || variable_e;
2644 ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_n_attribute10' THEN
2645 l_serial_data :=
2646 l_serial_data || variable_b || l_selected_fields(i).variable_name || '">' || serial_rec.lot_n_attribute10 || variable_e;
2647 ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_country_of_origin' THEN
2648 l_serial_data :=
2649 l_serial_data || variable_b || l_selected_fields(i).variable_name || '">' || serial_rec.lot_country_of_origin || variable_e;
2650
2651 --- Start Bug 4355080
2652 ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_status' THEN
2653 l_serial_data :=
2654 l_serial_data || variable_b || l_selected_fields(i).variable_name || '">' || l_lot_status || variable_e;
2655 ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_grade_code' THEN
2656 l_serial_data :=
2657 l_serial_data || variable_b || l_selected_fields(i).variable_name || '">' || l_grade_code || variable_e;
2658 ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_origination_date' THEN
2659 l_serial_data :=
2660 l_serial_data || variable_b || l_selected_fields(i).variable_name || '">' || l_origination_date || variable_e;
2661 ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_retest_date' THEN
2662 l_serial_data :=
2663 l_serial_data || variable_b || l_selected_fields(i).variable_name || '">' || l_retest_date || variable_e;
2664 ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_maturity_date' THEN
2665 l_serial_data :=
2666 l_serial_data || variable_b || l_selected_fields(i).variable_name || '">' || l_maturity_date || variable_e;
2667 --- End Bug 4355080
2668
2669 ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_date_code' THEN
2670 l_serial_data :=
2671 l_serial_data || variable_b || l_selected_fields(i).variable_name || '">' || serial_rec.lot_date_code || variable_e;
2672 ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_change_date' THEN
2673 l_serial_data :=
2674 l_serial_data || variable_b || l_selected_fields(i).variable_name || '">' || serial_rec.lot_change_date || variable_e;
2675 ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_age' THEN
2676 l_serial_data := l_serial_data || variable_b || l_selected_fields(i).variable_name || '">' || serial_rec.lot_age || variable_e;
2677 ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_item_size' THEN
2678 l_serial_data :=
2679 l_serial_data || variable_b || l_selected_fields(i).variable_name || '">' || serial_rec.lot_item_size || variable_e;
2680 ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_color' THEN
2681 l_serial_data :=
2682 l_serial_data || variable_b || l_selected_fields(i).variable_name || '">' || serial_rec.lot_color || variable_e;
2683 ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_volume' THEN
2684 l_serial_data :=
2685 l_serial_data || variable_b || l_selected_fields(i).variable_name || '">' || serial_rec.lot_volume || variable_e;
2686 ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_volume_uom' THEN
2687 l_serial_data :=
2688 l_serial_data || variable_b || l_selected_fields(i).variable_name || '">' || serial_rec.lot_volume_uom || variable_e;
2689 ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_place_of_origin' THEN
2690 l_serial_data :=
2691 l_serial_data || variable_b || l_selected_fields(i).variable_name || '">' || serial_rec.lot_place_of_origin || variable_e;
2692 ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_best_by_date' THEN
2693 l_serial_data :=
2694 l_serial_data || variable_b || l_selected_fields(i).variable_name || '">' || serial_rec.lot_best_by_date || variable_e;
2695 ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_length' THEN
2696 l_serial_data :=
2697 l_serial_data || variable_b || l_selected_fields(i).variable_name || '">' || serial_rec.lot_length || variable_e;
2698 ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_length_uom' THEN
2699 l_serial_data :=
2700 l_serial_data || variable_b || l_selected_fields(i).variable_name || '">' || serial_rec.lot_length_uom || variable_e;
2701 ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_recycled_cont' THEN
2702 l_serial_data :=
2703 l_serial_data || variable_b || l_selected_fields(i).variable_name || '">' || serial_rec.lot_recycled_cont || variable_e;
2704 ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_thickness' THEN
2705 l_serial_data :=
2706 l_serial_data || variable_b || l_selected_fields(i).variable_name || '">' || serial_rec.lot_thickness || variable_e;
2707 ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_thickness_uom' THEN
2708 l_serial_data :=
2709 l_serial_data || variable_b || l_selected_fields(i).variable_name || '">' || serial_rec.lot_thickness_uom || variable_e;
2710 ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_width' THEN
2711 l_serial_data :=
2712 l_serial_data || variable_b || l_selected_fields(i).variable_name || '">' || serial_rec.lot_width || variable_e;
2713 ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_width_uom' THEN
2714 l_serial_data :=
2715 l_serial_data || variable_b || l_selected_fields(i).variable_name || '">' || serial_rec.lot_width_uom || variable_e;
2716 ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_curl' THEN
2717 l_serial_data :=
2718 l_serial_data || variable_b || l_selected_fields(i).variable_name || '">' || serial_rec.lot_curl || variable_e;
2719 ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_vendor' THEN
2720 l_serial_data :=
2721 l_serial_data || variable_b || l_selected_fields(i).variable_name || '">' || serial_rec.lot_vendor || variable_e;
2722 ELSIF LOWER(l_selected_fields(i).column_name) = 'item_hazard_class' THEN
2723 l_serial_data :=
2724 l_serial_data || variable_b || l_selected_fields(i).variable_name || '">' || serial_rec.item_hazard_class || variable_e;
2725 ELSIF LOWER(l_selected_fields(i).column_name) = 'item_attribute_category' THEN
2726 l_serial_data :=
2727 l_serial_data || variable_b || l_selected_fields(i).variable_name || '">' || serial_rec.item_attribute_category || variable_e;
2728 ELSIF LOWER(l_selected_fields(i).column_name) = 'item_attribute1' THEN
2729 l_serial_data :=
2730 l_serial_data || variable_b || l_selected_fields(i).variable_name || '">' || serial_rec.item_attribute1 || variable_e;
2731 ELSIF LOWER(l_selected_fields(i).column_name) = 'item_attribute2' THEN
2732 l_serial_data :=
2733 l_serial_data || variable_b || l_selected_fields(i).variable_name || '">' || serial_rec.item_attribute2 || variable_e;
2734 ELSIF LOWER(l_selected_fields(i).column_name) = 'item_attribute3' THEN
2735 l_serial_data :=
2736 l_serial_data || variable_b || l_selected_fields(i).variable_name || '">' || serial_rec.item_attribute3 || variable_e;
2737 ELSIF LOWER(l_selected_fields(i).column_name) = 'item_attribute4' THEN
2738 l_serial_data :=
2739 l_serial_data || variable_b || l_selected_fields(i).variable_name || '">' || serial_rec.item_attribute4 || variable_e;
2740 ELSIF LOWER(l_selected_fields(i).column_name) = 'item_attribute5' THEN
2741 l_serial_data :=
2742 l_serial_data || variable_b || l_selected_fields(i).variable_name || '">' || serial_rec.item_attribute5 || variable_e;
2743 ELSIF LOWER(l_selected_fields(i).column_name) = 'item_attribute6' THEN
2744 l_serial_data :=
2745 l_serial_data || variable_b || l_selected_fields(i).variable_name || '">' || serial_rec.item_attribute6 || variable_e;
2746 ELSIF LOWER(l_selected_fields(i).column_name) = 'item_attribute7' THEN
2747 l_serial_data :=
2748 l_serial_data || variable_b || l_selected_fields(i).variable_name || '">' || serial_rec.item_attribute7 || variable_e;
2749 ELSIF LOWER(l_selected_fields(i).column_name) = 'item_attribute8' THEN
2750 l_serial_data :=
2751 l_serial_data || variable_b || l_selected_fields(i).variable_name || '">' || serial_rec.item_attribute8 || variable_e;
2752 ELSIF LOWER(l_selected_fields(i).column_name) = 'item_attribute9' THEN
2753 l_serial_data :=
2754 l_serial_data || variable_b || l_selected_fields(i).variable_name || '">' || serial_rec.item_attribute9 || variable_e;
2755 ELSIF LOWER(l_selected_fields(i).column_name) = 'item_attribute10' THEN
2756 l_serial_data :=
2757 l_serial_data || variable_b || l_selected_fields(i).variable_name || '">' || serial_rec.item_attribute10 || variable_e;
2758 ELSIF LOWER(l_selected_fields(i).column_name) = 'item_attribute11' THEN
2759 l_serial_data :=
2760 l_serial_data || variable_b || l_selected_fields(i).variable_name || '">' || serial_rec.item_attribute11 || variable_e;
2761 ELSIF LOWER(l_selected_fields(i).column_name) = 'item_attribute12' THEN
2762 l_serial_data :=
2763 l_serial_data || variable_b || l_selected_fields(i).variable_name || '">' || serial_rec.item_attribute12 || variable_e;
2764 ELSIF LOWER(l_selected_fields(i).column_name) = 'item_attribute13' THEN
2765 l_serial_data :=
2766 l_serial_data || variable_b || l_selected_fields(i).variable_name || '">' || serial_rec.item_attribute13 || variable_e;
2767 ELSIF LOWER(l_selected_fields(i).column_name) = 'item_attribute14' THEN
2768 l_serial_data :=
2769 l_serial_data || variable_b || l_selected_fields(i).variable_name || '">' || serial_rec.item_attribute14 || variable_e;
2770 ELSIF LOWER(l_selected_fields(i).column_name) = 'item_attribute15' THEN
2771 l_serial_data :=
2772 l_serial_data || variable_b || l_selected_fields(i).variable_name || '">' || serial_rec.item_attribute15 || variable_e;
2773 ELSIF LOWER(l_selected_fields(i).column_name) = 'serial_number' THEN
2774 l_serial_data :=
2775 l_serial_data || variable_b || l_selected_fields(i).variable_name || '">' || serial_rec.serial_number || variable_e;
2776 ELSIF LOWER(l_selected_fields(i).column_name) = 'serial_number_status' THEN
2777 l_serial_data :=
2778 l_serial_data || variable_b || l_selected_fields(i).variable_name || '">' || serial_rec.serial_number_status || variable_e;
2779 ELSIF LOWER(l_selected_fields(i).column_name) = 'serial_attribute_category' THEN
2780 l_serial_data :=
2781 l_serial_data || variable_b || l_selected_fields(i).variable_name || '">' || serial_rec.serial_attribute_category
2782 || variable_e;
2783 ELSIF LOWER(l_selected_fields(i).column_name) = 'serial_c_attribute1' THEN
2784 l_serial_data :=
2785 l_serial_data || variable_b || l_selected_fields(i).variable_name || '">' || serial_rec.serial_c_attribute1 || variable_e;
2786 ELSIF LOWER(l_selected_fields(i).column_name) = 'serial_c_attribute2' THEN
2787 l_serial_data :=
2788 l_serial_data || variable_b || l_selected_fields(i).variable_name || '">' || serial_rec.serial_c_attribute2 || variable_e;
2789 ELSIF LOWER(l_selected_fields(i).column_name) = 'serial_c_attribute3' THEN
2790 l_serial_data :=
2791 l_serial_data || variable_b || l_selected_fields(i).variable_name || '">' || serial_rec.serial_c_attribute3 || variable_e;
2792 ELSIF LOWER(l_selected_fields(i).column_name) = 'serial_c_attribute4' THEN
2793 l_serial_data :=
2794 l_serial_data || variable_b || l_selected_fields(i).variable_name || '">' || serial_rec.serial_c_attribute4 || variable_e;
2795 ELSIF LOWER(l_selected_fields(i).column_name) = 'serial_c_attribute5' THEN
2796 l_serial_data :=
2797 l_serial_data || variable_b || l_selected_fields(i).variable_name || '">' || serial_rec.serial_c_attribute5 || variable_e;
2798 ELSIF LOWER(l_selected_fields(i).column_name) = 'serial_c_attribute6' THEN
2799 l_serial_data :=
2800 l_serial_data || variable_b || l_selected_fields(i).variable_name || '">' || serial_rec.serial_c_attribute6 || variable_e;
2801 ELSIF LOWER(l_selected_fields(i).column_name) = 'serial_c_attribute7' THEN
2802 l_serial_data :=
2803 l_serial_data || variable_b || l_selected_fields(i).variable_name || '">' || serial_rec.serial_c_attribute7 || variable_e;
2804 ELSIF LOWER(l_selected_fields(i).column_name) = 'serial_c_attribute8' THEN
2805 l_serial_data :=
2806 l_serial_data || variable_b || l_selected_fields(i).variable_name || '">' || serial_rec.serial_c_attribute8 || variable_e;
2807 ELSIF LOWER(l_selected_fields(i).column_name) = 'serial_c_attribute9' THEN
2808 l_serial_data :=
2809 l_serial_data || variable_b || l_selected_fields(i).variable_name || '">' || serial_rec.serial_c_attribute9 || variable_e;
2810 ELSIF LOWER(l_selected_fields(i).column_name) = 'serial_c_attribute10' THEN
2811 l_serial_data :=
2812 l_serial_data || variable_b || l_selected_fields(i).variable_name || '">' || serial_rec.serial_c_attribute10 || variable_e;
2813 ELSIF LOWER(l_selected_fields(i).column_name) = 'serial_c_attribute11' THEN
2814 l_serial_data :=
2815 l_serial_data || variable_b || l_selected_fields(i).variable_name || '">' || serial_rec.serial_c_attribute11 || variable_e;
2816 ELSIF LOWER(l_selected_fields(i).column_name) = 'serial_c_attribute12' THEN
2817 l_serial_data :=
2818 l_serial_data || variable_b || l_selected_fields(i).variable_name || '">' || serial_rec.serial_c_attribute12 || variable_e;
2819 ELSIF LOWER(l_selected_fields(i).column_name) = 'serial_c_attribute13' THEN
2820 l_serial_data :=
2821 l_serial_data || variable_b || l_selected_fields(i).variable_name || '">' || serial_rec.serial_c_attribute13 || variable_e;
2822 ELSIF LOWER(l_selected_fields(i).column_name) = 'serial_c_attribute14' THEN
2823 l_serial_data :=
2824 l_serial_data || variable_b || l_selected_fields(i).variable_name || '">' || serial_rec.serial_c_attribute14 || variable_e;
2825 ELSIF LOWER(l_selected_fields(i).column_name) = 'serial_c_attribute15' THEN
2826 l_serial_data :=
2827 l_serial_data || variable_b || l_selected_fields(i).variable_name || '">' || serial_rec.serial_c_attribute15 || variable_e;
2828 ELSIF LOWER(l_selected_fields(i).column_name) = 'serial_c_attribute16' THEN
2829 l_serial_data :=
2830 l_serial_data || variable_b || l_selected_fields(i).variable_name || '">' || serial_rec.serial_c_attribute16 || variable_e;
2831 ELSIF LOWER(l_selected_fields(i).column_name) = 'serial_c_attribute17' THEN
2832 l_serial_data :=
2833 l_serial_data || variable_b || l_selected_fields(i).variable_name || '">' || serial_rec.serial_c_attribute17 || variable_e;
2834 ELSIF LOWER(l_selected_fields(i).column_name) = 'serial_c_attribute18' THEN
2835 l_serial_data :=
2836 l_serial_data || variable_b || l_selected_fields(i).variable_name || '">' || serial_rec.serial_c_attribute18 || variable_e;
2837 ELSIF LOWER(l_selected_fields(i).column_name) = 'serial_c_attribute19' THEN
2838 l_serial_data :=
2839 l_serial_data || variable_b || l_selected_fields(i).variable_name || '">' || serial_rec.serial_c_attribute19 || variable_e;
2840 ELSIF LOWER(l_selected_fields(i).column_name) = 'serial_c_attribute20' THEN
2841 l_serial_data :=
2842 l_serial_data || variable_b || l_selected_fields(i).variable_name || '">' || serial_rec.serial_c_attribute20 || variable_e;
2843 ELSIF LOWER(l_selected_fields(i).column_name) = 'serial_d_attribute1' THEN
2844 l_serial_data :=
2845 l_serial_data || variable_b || l_selected_fields(i).variable_name || '">' || serial_rec.serial_d_attribute1 || variable_e;
2846 ELSIF LOWER(l_selected_fields(i).column_name) = 'serial_d_attribute2' THEN
2847 l_serial_data :=
2848 l_serial_data || variable_b || l_selected_fields(i).variable_name || '">' || serial_rec.serial_d_attribute2 || variable_e;
2849 ELSIF LOWER(l_selected_fields(i).column_name) = 'serial_d_attribute3' THEN
2850 l_serial_data :=
2851 l_serial_data || variable_b || l_selected_fields(i).variable_name || '">' || serial_rec.serial_d_attribute3 || variable_e;
2852 ELSIF LOWER(l_selected_fields(i).column_name) = 'serial_d_attribute4' THEN
2853 l_serial_data :=
2854 l_serial_data || variable_b || l_selected_fields(i).variable_name || '">' || serial_rec.serial_d_attribute4 || variable_e;
2855 ELSIF LOWER(l_selected_fields(i).column_name) = 'serial_d_attribute5' THEN
2856 l_serial_data :=
2857 l_serial_data || variable_b || l_selected_fields(i).variable_name || '">' || serial_rec.serial_d_attribute5 || variable_e;
2858 ELSIF LOWER(l_selected_fields(i).column_name) = 'serial_d_attribute6' THEN
2859 l_serial_data :=
2860 l_serial_data || variable_b || l_selected_fields(i).variable_name || '">' || serial_rec.serial_d_attribute6 || variable_e;
2861 ELSIF LOWER(l_selected_fields(i).column_name) = 'serial_d_attribute7' THEN
2862 l_serial_data :=
2863 l_serial_data || variable_b || l_selected_fields(i).variable_name || '">' || serial_rec.serial_d_attribute7 || variable_e;
2864 ELSIF LOWER(l_selected_fields(i).column_name) = 'serial_d_attribute8' THEN
2865 l_serial_data :=
2866 l_serial_data || variable_b || l_selected_fields(i).variable_name || '">' || serial_rec.serial_d_attribute8 || variable_e;
2867 ELSIF LOWER(l_selected_fields(i).column_name) = 'serial_d_attribute9' THEN
2868 l_serial_data :=
2869 l_serial_data || variable_b || l_selected_fields(i).variable_name || '">' || serial_rec.serial_d_attribute9 || variable_e;
2870 ELSIF LOWER(l_selected_fields(i).column_name) = 'serial_d_attribute10' THEN
2871 l_serial_data :=
2872 l_serial_data || variable_b || l_selected_fields(i).variable_name || '">' || serial_rec.serial_d_attribute10 || variable_e;
2873 ELSIF LOWER(l_selected_fields(i).column_name) = 'serial_n_attribute1' THEN
2874 l_serial_data :=
2875 l_serial_data || variable_b || l_selected_fields(i).variable_name || '">' || serial_rec.serial_n_attribute1 || variable_e;
2876 ELSIF LOWER(l_selected_fields(i).column_name) = 'serial_n_attribute2' THEN
2877 l_serial_data :=
2878 l_serial_data || variable_b || l_selected_fields(i).variable_name || '">' || serial_rec.serial_n_attribute2 || variable_e;
2879 ELSIF LOWER(l_selected_fields(i).column_name) = 'serial_n_attribute3' THEN
2880 l_serial_data :=
2881 l_serial_data || variable_b || l_selected_fields(i).variable_name || '">' || serial_rec.serial_n_attribute3 || variable_e;
2882 ELSIF LOWER(l_selected_fields(i).column_name) = 'serial_n_attribute4' THEN
2883 l_serial_data :=
2884 l_serial_data || variable_b || l_selected_fields(i).variable_name || '">' || serial_rec.serial_n_attribute4 || variable_e;
2885 ELSIF LOWER(l_selected_fields(i).column_name) = 'serial_n_attribute5' THEN
2886 l_serial_data :=
2887 l_serial_data || variable_b || l_selected_fields(i).variable_name || '">' || serial_rec.serial_n_attribute5 || variable_e;
2888 ELSIF LOWER(l_selected_fields(i).column_name) = 'serial_n_attribute6' THEN
2889 l_serial_data :=
2890 l_serial_data || variable_b || l_selected_fields(i).variable_name || '">' || serial_rec.serial_n_attribute6 || variable_e;
2891 ELSIF LOWER(l_selected_fields(i).column_name) = 'serial_n_attribute7' THEN
2892 l_serial_data :=
2893 l_serial_data || variable_b || l_selected_fields(i).variable_name || '">' || serial_rec.serial_n_attribute7 || variable_e;
2894 ELSIF LOWER(l_selected_fields(i).column_name) = 'serial_n_attribute8' THEN
2895 l_serial_data :=
2896 l_serial_data || variable_b || l_selected_fields(i).variable_name || '">' || serial_rec.serial_n_attribute8 || variable_e;
2897 ELSIF LOWER(l_selected_fields(i).column_name) = 'serial_n_attribute9' THEN
2898 l_serial_data :=
2899 l_serial_data || variable_b || l_selected_fields(i).variable_name || '">' || serial_rec.serial_n_attribute9 || variable_e;
2900 ELSIF LOWER(l_selected_fields(i).column_name) = 'serial_n_attribute10' THEN
2901 l_serial_data :=
2902 l_serial_data || variable_b || l_selected_fields(i).variable_name || '">' || serial_rec.serial_n_attribute10 || variable_e;
2903 ELSIF LOWER(l_selected_fields(i).column_name) = 'serial_country_of_origin' THEN
2904 l_serial_data :=
2905 l_serial_data || variable_b || l_selected_fields(i).variable_name || '">' || serial_rec.serial_country_of_origin
2906 || variable_e;
2907 ELSIF LOWER(l_selected_fields(i).column_name) = 'serial_time_since_new' THEN
2908 l_serial_data :=
2909 l_serial_data || variable_b || l_selected_fields(i).variable_name || '">' || serial_rec.serial_time_since_new || variable_e;
2910 ELSIF LOWER(l_selected_fields(i).column_name) = 'serial_cycles_since_new' THEN
2911 l_serial_data :=
2912 l_serial_data || variable_b || l_selected_fields(i).variable_name || '">' || serial_rec.serial_cycles_since_new || variable_e;
2913 ELSIF LOWER(l_selected_fields(i).column_name) = 'serial_time_since_over' THEN
2914 l_serial_data :=
2915 l_serial_data || variable_b || l_selected_fields(i).variable_name || '">' || serial_rec.serial_time_since_overhaul
2916 || variable_e;
2917 ELSIF LOWER(l_selected_fields(i).column_name) = 'serial_cycles_since_over' THEN
2918 l_serial_data :=
2919 l_serial_data || variable_b || l_selected_fields(i).variable_name || '">' || serial_rec.serial_cycles_since_overhaul
2920 || variable_e;
2921 ELSIF LOWER(l_selected_fields(i).column_name) = 'serial_time_since_repair' THEN
2922 l_serial_data :=
2923 l_serial_data || variable_b || l_selected_fields(i).variable_name || '">' || serial_rec.serial_time_since_repair
2924 || variable_e;
2925 ELSIF LOWER(l_selected_fields(i).column_name) = 'serial_cycles_since_repair' THEN
2926 l_serial_data :=
2927 l_serial_data || variable_b || l_selected_fields(i).variable_name || '">' || serial_rec.serial_cycles_since_repair
2928 || variable_e;
2929 ELSIF LOWER(l_selected_fields(i).column_name) = 'serial_time_since_visit' THEN
2930 l_serial_data :=
2931 l_serial_data || variable_b || l_selected_fields(i).variable_name || '">' || serial_rec.serial_time_since_visit || variable_e;
2932 ELSIF LOWER(l_selected_fields(i).column_name) = 'serial_cycles_since_visit' THEN
2933 l_serial_data :=
2934 l_serial_data || variable_b || l_selected_fields(i).variable_name || '">' || serial_rec.serial_cycles_since_visit
2935 || variable_e;
2936 ELSIF LOWER(l_selected_fields(i).column_name) = 'serial_time_since_mark' THEN
2937 l_serial_data :=
2938 l_serial_data || variable_b || l_selected_fields(i).variable_name || '">' || serial_rec.serial_time_since_mark || variable_e;
2939 ELSIF LOWER(l_selected_fields(i).column_name) = 'serial_cycles_since_mark' THEN
2940 l_serial_data :=
2941 l_serial_data || variable_b || l_selected_fields(i).variable_name || '">' || serial_rec.serial_cycles_since_mark
2942 || variable_e;
2943 ELSIF LOWER(l_selected_fields(i).column_name) = 'serial_num_of_repairs' THEN
2944 l_serial_data :=
2945 l_serial_data || variable_b || l_selected_fields(i).variable_name || '">' || serial_rec.serial_num_of_repairs || variable_e;
2946 ELSIF LOWER(l_selected_fields(i).column_name) = 'serial_initialization_date' THEN
2947 l_serial_data :=
2948 l_serial_data || variable_b || l_selected_fields(i).variable_name || '">' || serial_rec.serial_initialization_date
2949 || variable_e;
2950 ELSIF LOWER(l_selected_fields(i).column_name) = 'serial_completion_date' THEN
2951 l_serial_data :=
2952 l_serial_data || variable_b || l_selected_fields(i).variable_name || '">' || serial_rec.serial_completion_date || variable_e;
2953 ELSIF LOWER(l_selected_fields(i).column_name) = 'serial_fixed_asset_tag' THEN
2954 l_serial_data :=
2955 l_serial_data || variable_b || l_selected_fields(i).variable_name || '">' || serial_rec.serial_fixed_asset_tag || variable_e;
2956 ELSIF LOWER(l_selected_fields(i).column_name) = 'serial_vendor_serial' THEN
2957 l_serial_data :=
2958 l_serial_data || variable_b || l_selected_fields(i).variable_name || '">' || serial_rec.serial_vendor_serial || variable_e;
2959 --START of Fix For Bug: 4907062
2960 -- Project_Number and Task Number fields are added newly.
2961 ELSIF LOWER(l_selected_fields(i).column_name) = 'project_number' THEN
2962 l_serial_data :=
2963 l_serial_data || variable_b
2964 || l_selected_fields(i).variable_name
2965 || '">'
2966 || serial_rec.project_number
2967 || variable_e;
2968 ELSIF LOWER(l_selected_fields(i).column_name) = 'task_number' THEN
2969 l_serial_data := l_serial_data
2970 || variable_b
2971 || l_selected_fields(i).variable_name
2972 || '">'
2973 || serial_rec.task_number
2974 || variable_e;
2975 --END of Fix For Bug: 4907062
2976 ELSIF LOWER(l_selected_fields(i).column_name) = 'project' THEN
2977 l_serial_data := l_serial_data || variable_b || l_selected_fields(i).variable_name || '">' || serial_rec.project || variable_e;
2978 ELSIF LOWER(l_selected_fields(i).column_name) = 'task' THEN
2979 l_serial_data := l_serial_data || variable_b || l_selected_fields(i).variable_name || '">' || serial_rec.task || variable_e;
2980 ELSIF LOWER(l_selected_fields(i).column_name) = 'organization' THEN
2981 l_serial_data :=
2982 l_serial_data || variable_b || l_selected_fields(i).variable_name || '">' || serial_rec.ORGANIZATION || variable_e;
2983
2984 ELSIF LOWER(l_selected_fields(i).column_name) = 'job_name' THEN
2985 -- Added for Bug 4642062.
2986 -- Using the value of l_wip_entity_name if job_name from serial_rec is null
2987 l_serial_data :=
2988 l_serial_data || variable_b || l_selected_fields(i).variable_name || '">' || nvl(serial_rec.job_name, l_wip_entity_name) || variable_e;
2989
2990 ELSIF LOWER(l_selected_fields(i).column_name) = 'job_type' THEN
2991 l_serial_data := l_serial_data || variable_b || l_selected_fields(i).variable_name || '">' || l_entity_type || variable_e;
2992 ELSIF LOWER(l_selected_fields(i).column_name) = 'job_qty' THEN
2993 l_serial_data := l_serial_data || variable_b || l_selected_fields(i).variable_name || '">' || l_net_quantity || variable_e;
2994 ELSIF LOWER(l_selected_fields(i).column_name) = 'job_scheduled_start_date' THEN
2995 l_serial_data := l_serial_data || variable_b || l_selected_fields(i).variable_name || '">' || l_scheduled_start_date || variable_e;
2996 ELSIF LOWER(l_selected_fields(i).column_name) = 'job_scheduled_completion_date' THEN
2997 l_serial_data := l_serial_data || variable_b || l_selected_fields(i).variable_name || '">' || l_scheduled_completion_date || variable_e;
2998 ELSIF LOWER(l_selected_fields(i).column_name) = 'bom_revision' THEN
2999 l_serial_data := l_serial_data || variable_b || l_selected_fields(i).variable_name || '">' || l_bom_revision || variable_e;
3000 ELSIF LOWER(l_selected_fields(i).column_name) = 'routing_revision' THEN
3001 l_serial_data := l_serial_data || variable_b || l_selected_fields(i).variable_name || '">' || l_routing_revision || variable_e;
3002
3003 -- Added as part of change for patchset "I".
3004 ELSIF LOWER(l_selected_fields(i).column_name) = 'receipt_num' THEN
3005 l_serial_data := l_serial_data || variable_b || l_selected_fields(i).variable_name || '">' || l_receipt_number || variable_e;
3006 -- Added for UCC 128 J Bug #3067059
3007 ELSIF LOWER(l_selected_fields(i).column_name) = 'gtin' THEN
3008 l_serial_data := l_serial_data || variable_b || l_selected_fields(i).variable_name || '">' || l_gtin || variable_e;
3009 ELSIF LOWER(l_selected_fields(i).column_name) = 'gtin_description' THEN
3010 l_serial_data := l_serial_data || variable_b || l_selected_fields(i).variable_name || '">' || l_gtin_desc || variable_e;
3011 ELSIF LOWER(l_selected_fields(i).column_name) = 'subinventory_code' THEN
3012 l_serial_data := l_serial_data || variable_b || l_selected_fields(i).variable_name || '">' || l_subinventory || variable_e;
3013 ELSIF LOWER(l_selected_fields(i).column_name) = 'locator' THEN
3014 l_serial_data := l_serial_data || variable_b || l_selected_fields(i).variable_name || '">' || serial_rec.LOCATOR || variable_e;
3015 -- Fix for bug# 3739661: UOM not displayed in Serial Labels
3016 ELSIF LOWER(l_selected_fields(i).column_name) = 'uom' THEN
3017 l_serial_data := l_serial_data || variable_b || l_selected_fields(i).variable_name || '">' || l_uom || variable_e;
3018 -- End of fix for 3739661
3019
3020 -- invconv changes start
3021 ELSIF LOWER(l_selected_fields(i).column_name) = 'parent_lot_number' THEN
3022 l_serial_data := l_serial_data || variable_b || l_selected_fields(i).variable_name || '">' || l_parent_lot_number || variable_e;
3023
3024 ELSIF LOWER(l_selected_fields(i).column_name) = 'hold_date' THEN
3025 l_serial_data := l_serial_data || variable_b || l_selected_fields(i).variable_name || '">' || l_hold_date || variable_e;
3026
3027 ELSIF LOWER(l_selected_fields(i).column_name) = 'expiration_action_date' THEN
3028 l_serial_data := l_serial_data || variable_b || l_selected_fields(i).variable_name || '">' || l_expiration_action_date || variable_e;
3029
3030 ELSIF LOWER(l_selected_fields(i).column_name) = 'expiration_action_code' THEN
3031 l_serial_data := l_serial_data || variable_b || l_selected_fields(i).variable_name || '">' || l_expiration_action_code || variable_e;
3032
3033 ELSIF LOWER(l_selected_fields(i).column_name) = 'supplier_lot_number' THEN
3034 l_serial_data := l_serial_data || variable_b || l_selected_fields(i).variable_name || '">' || l_supplier_lot_number || variable_e;
3035
3036 ELSIF LOWER(l_selected_fields(i).column_name) = 'origination_type' THEN
3037 l_serial_data := l_serial_data || variable_b || l_selected_fields(i).variable_name || '">' || l_origination_type || variable_e;
3038 -- invconv changes END
3039
3040 -- Added for R12 RFID Compliance project
3041 -- New field : EPC
3042 -- EPC is generated once for each LPN
3043 ELSIF LOWER(l_selected_fields(i).column_name) = 'epc' THEN
3044 l_serial_data := l_serial_data
3045 || variable_b
3046 || l_selected_fields(i).variable_name
3047 || '">'
3048 || l_epc
3049 || variable_e;
3050
3051 l_label_err_msg := l_epc_ret_msg;
3052 IF l_epc_ret_status = 'U' THEN
3053 l_label_status := INV_LABEL.G_ERROR;
3054 ELSIF l_epc_ret_status = 'E' THEN
3055 l_label_status := INV_LABEL.G_WARNING;
3056
3057 END IF;
3058
3059 END IF;
3060 END LOOP;
3061
3062 l_serial_data := l_serial_data || label_e;
3063 x_variable_content(l_label_index).label_content := l_serial_data;
3064 x_variable_content(l_label_index).label_request_id := l_label_request_id;
3065 x_variable_content(l_label_index).label_status := l_label_status;
3066 ------------------------Start of changes for Custom Labels project code------------------
3067
3068 -- Fix for bug: 4179593 Start
3069 IF (l_CustSqlWarnFlagSet) THEN
3070 l_custom_sql_ret_status := INV_LABEL.G_WARNING;
3071 l_custom_sql_ret_msg := l_CustSqlWarnMsg;
3072 END IF;
3073
3074 IF (l_CustSqlErrFlagSet) THEN
3075 l_custom_sql_ret_status := FND_API.G_RET_STS_ERROR;
3076 l_custom_sql_ret_msg := l_CustSqlErrMsg;
3077 x_variable_content(l_label_index).label_status := l_custom_sql_ret_status;
3078 END IF;
3079 -- Fix for bug: 4179593 End
3080
3081 x_variable_content(l_label_index).error_message := l_custom_sql_ret_msg|| ' ' || l_label_err_msg;
3082 ------------------------End of this changes for Custom Labels project code---------------
3083 l_label_index := l_label_index + 1;
3084
3085 IF (l_debug = 1) THEN
3086 TRACE(l_column_name_list);
3087 TRACE('Finished writing one label');
3088 END IF;
3089
3090 l_serial_data := '';
3091 l_label_request_id := NULL;
3092 ------------------------Start of changes for Custom Labels project code------------------
3093 l_custom_sql_ret_status := NULL;
3094 l_custom_sql_ret_msg := NULL;
3095 ------------------------End of this changes for Custom Labels project code---------------
3096 l_serial_loop_count := l_serial_loop_count + 1;
3097 END LOOP;
3098
3099
3100 l_serial_data := '';
3101 l_label_request_id := NULL;
3102 ------------------------Start of changes for Custom Labels project code------------------
3103 l_custom_sql_ret_status := NULL;
3104 l_custom_sql_ret_msg := NULL;
3105 ------------------------End of this changes for Custom Labels project code---------------
3106
3107 <<nextlabel>>
3108
3109 IF (l_debug = 1) THEN
3110 TRACE(' Done with Label format in the current label-set');
3111 END IF;
3112
3113
3114 END LOOP; --for formats in label-set
3115
3116
3117
3118
3119
3120
3121 IF (p_label_type_info.business_flow_code IN(1, 2, 3, 4)
3122 AND(l_patch_level = 1)) THEN
3123 FETCH rt_serial_cur
3124 INTO l_inventory_item_id
3125 , l_organization_id
3126 , l_lot_number
3127 , l_project_id
3128 , l_task_id
3129 , l_revision
3130 , l_serial_number
3131 , l_purchase_order
3132 , l_subinventory
3133 , l_locator_id
3134 , l_vendor_id
3135 , l_vendor_site_id
3136 , l_uom
3137 , l_oe_order_header_id --Bug 4582954
3138 , l_oe_order_line_id; --Bug 4582954
3139
3140 IF rt_serial_cur%NOTFOUND THEN
3141 l_serial_number := NULL;
3142
3143 CLOSE rt_serial_cur;
3144 ELSE
3145 IF (l_debug = 1) THEN
3146 TRACE(' Found another serial number fetching once again RT_SERIAL_CUR => ' || l_serial_number);
3147 END IF;
3148 END IF;
3149 ELSIF((l_patch_level = 0)
3150 AND(p_label_type_info.business_flow_code IN(1, 2)
3151 AND(l_is_wms_org = TRUE))) THEN
3152 -- Receipt(1), Inspection(2) or Putaway Drop(4) and org is WMS enabled
3153 -- In an INV org there are no serial numbers at these points in the transaction.
3154 l_receipt_number := inv_rcv_common_apis.g_rcv_global_var.receipt_num;
3155
3156 FETCH rti_serial_lpn_cur
3157 INTO l_inventory_item_id
3158 , l_organization_id
3159 , l_lot_number
3160 , l_project_id
3161 , l_task_id
3162 , l_revision
3163 , l_serial_number
3164 , l_purchase_order
3165 , l_subinventory
3166 , l_vendor_id
3167 , l_vendor_site_id
3168 , l_oe_order_header_id --Bug 4582954
3169 , l_oe_order_line_id; --Bug 4582954
3170
3171 IF rti_serial_lpn_cur%NOTFOUND THEN
3172 IF (l_debug = 1) THEN
3173 TRACE(' (1)No Serial number found for this given Interface Transaction ID:' || p_transaction_id);
3174 END IF;
3175
3176 CLOSE rti_serial_lpn_cur;
3177
3178 RETURN;
3179 END IF;
3180 ELSIF(
3181 (l_patch_level = 0)
3182 AND(
3183 (p_label_type_info.business_flow_code IN(3)
3184 AND(l_is_wms_org = TRUE))
3185 OR(p_label_type_info.business_flow_code IN(4)
3186 AND(l_is_wms_org = FALSE))
3187 )
3188 ) THEN
3189 IF (l_debug = 1) THEN
3190 TRACE(
3191 ' getting next serial number for business flow 3, l_serial_table_index= '
3192 || l_serial_table_index
3193 || ', table count= '
3194 || l_serial_numbers_table.COUNT
3195 );
3196 END IF;
3197
3198 IF (l_serial_table_index < l_serial_numbers_table.COUNT) THEN
3199 l_serial_table_index := l_serial_table_index + 1;
3200 l_serial_number := l_serial_numbers_table(l_serial_table_index);
3201 ELSE
3202 -- finished this serial number table, get a new record
3203 -- from rti_serial_msnt_cur and get a new serial number table
3204 FETCH rti_serial_msnt_cur
3205 INTO l_inventory_item_id
3206 , l_organization_id
3207 , l_lot_number
3208 , l_project_id
3209 , l_task_id
3210 , l_revision
3211 , l_fm_serial_number
3212 , l_to_serial_number
3213 , l_purchase_order
3214 , l_subinventory
3215 , l_vendor_id
3216 , l_vendor_site_id
3217 , l_oe_order_header_id --Bug 4582954
3218 , l_oe_order_line_id; --Bug 4582954
3219
3220 IF rti_serial_msnt_cur%NOTFOUND THEN
3221 CLOSE rti_serial_msnt_cur;
3222
3223 l_serial_number := NULL;
3224 ELSE
3225 -- getting range serial numbers
3226 inv_label.get_number_between_range(
3227 fm_x_number => l_fm_serial_number
3228 , to_x_number => l_to_serial_number
3229 , x_return_status => l_return_status
3230 , x_number_table => l_serial_numbers_table
3231 );
3232
3233 IF l_return_status <> 'S' THEN
3234 fnd_message.set_name('WMS', 'WMS_GET_SER_CUR_FAILED'); -- Code message for this.
3235 fnd_msg_pub.ADD;
3236 RETURN;
3237 END IF;
3238
3239 IF (l_debug = 1) THEN
3240 TRACE(' Count of rows in l_serial_numbers_table ' || l_serial_numbers_table.COUNT);
3241 END IF;
3242
3243 l_serial_number := l_serial_numbers_table(1);
3244 l_serial_table_index := 1;
3245 END IF;
3246
3247 IF (l_debug = 1) THEN
3248 TRACE(' Got the next serial number = ' || l_serial_number);
3249 END IF;
3250 END IF;
3251 ELSIF p_label_type_info.business_flow_code IN(21) THEN
3252 -- Start of New Code Added as part of Bug# 4290536
3253 IF (l_serial_table_index < l_serial_numbers_table.COUNT) THEN
3254 l_serial_table_index := l_serial_table_index + 1;
3255 l_serial_number := l_serial_numbers_table(l_serial_table_index);
3256 ELSE
3257 -- End of New Code Added as part of Bug# 4290536
3258 -- finished this serial number table, get a new record
3259 FETCH wda_serial_cur
3260 INTO l_inventory_item_id
3261 , l_organization_id
3262 , l_lot_number
3263 , l_project_id
3264 , l_task_id
3265 , l_revision
3266 , l_serial_number
3267 , l_fm_serial_number
3268 , l_to_serial_number
3269 , l_subinventory
3270 , l_uom;
3271
3272 IF wda_serial_cur%NOTFOUND THEN
3273 l_serial_number := NULL;
3274 l_fm_serial_number := NULL;
3275 l_to_serial_number := NULL;
3276 CLOSE wda_serial_cur;
3277 --
3278 -- Start of New Code Added as part of Bug# 4290536
3279 --
3280 ELSIF l_fm_serial_number IS NOT NULL THEN
3281 IF (l_debug = 1) THEN
3282 TRACE(' Found Serial Number for the given ID: ' || p_transaction_id||'; l_serial_number: '||l_serial_number||'; l_fm_serial_number: '||l_fm_serial_number||'; l_to_serial_number: '||l_to_serial_number);
3283 END IF;
3284 -- getting range serial numbers
3285 inv_label.get_number_between_range(
3286 fm_x_number => l_fm_serial_number
3287 , to_x_number => l_to_serial_number
3288 , x_return_status => l_return_status
3289 , x_number_table => l_serial_numbers_table);
3290
3291 IF l_return_status <> 'S' THEN
3292 FND_MESSAGE.SET_NAME('WMS', 'WMS_GET_SER_CUR_FAILED');
3293 FND_MSG_PUB.ADD;
3294 RETURN;
3295 END IF;
3296 IF (l_debug = 1) THEN
3297 TRACE(' Number of SN in l_serial_numbers_table ' || l_serial_numbers_table.COUNT);
3298 END IF;
3299 l_serial_number := l_serial_numbers_table(1);
3300 l_serial_table_index := 1;
3301 END IF;
3302 END IF;
3303 --
3304 -- End of New Code Added as part of Bug# 4290536
3305 --
3306 ELSIF p_label_type_info.business_flow_code IN(13, 23, 27) THEN
3307 --Misc receipt and Issue, Put away pregeneration
3308 -- Flow labels, MMTT based
3309 IF (l_serial_table_index < l_serial_numbers_table.COUNT) THEN
3310 l_serial_table_index := l_serial_table_index + 1;
3311 l_serial_number := l_serial_numbers_table(l_serial_table_index);
3312 ELSE
3313 -- finished this serial number table, get a new record
3314 -- from rti_serial_msnt_cur and get a new serial number table
3315 FETCH mmtt_serial_cur
3316 INTO l_inventory_item_id
3317 , l_organization_id
3318 , l_lot_number
3319 , l_project_id
3320 , l_task_id
3321 , l_revision
3322 , l_fm_serial_number
3323 , l_to_serial_number
3324 , l_subinventory
3325 , l_uom
3326 , l_locator_id; /* Added for Bug # 4672471 */
3327
3328
3329 IF mmtt_serial_cur%NOTFOUND THEN
3330 IF (l_debug = 1) THEN
3331 TRACE(' No Serial number found for this given Transaction Temp ID:' || p_transaction_id);
3332 END IF;
3333
3334 CLOSE mmtt_serial_cur;
3335
3336 l_serial_number := NULL;
3337 ELSE
3338 -- getting range serial numbers
3339 IF (l_debug = 1) THEN
3340 TRACE(' Before call to GET_NUMBER_BETWEEN_RANGE ');
3341 END IF;
3342
3343 inv_label.get_number_between_range(
3344 fm_x_number => l_fm_serial_number
3345 , to_x_number => l_to_serial_number
3346 , x_return_status => l_return_status
3347 , x_number_table => l_serial_numbers_table
3348 );
3349
3350 IF l_return_status <> 'S' THEN
3351 fnd_message.set_name('WMS', 'WMS_GET_SER_CUR_FAILED'); -- Code message for this.
3352 fnd_msg_pub.ADD;
3353 RETURN;
3354 END IF;
3355
3356 IF (l_debug = 1) THEN
3357 TRACE(' Count of rows in l_serial_numbers_table ' || l_serial_numbers_table.COUNT);
3358 END IF;
3359
3360 l_serial_number := l_serial_numbers_table(1);
3361 l_serial_table_index := 1;
3362 END IF;
3363
3364 IF (l_debug = 1) THEN
3365 TRACE(' Got the next serial number = ' || l_serial_number);
3366 END IF;
3367 END IF;
3368 ELSIF (p_label_type_info.business_flow_code IN(33) AND p_transaction_identifier = 1) THEN
3369
3370 IF (l_debug = 1) THEN
3371 trace(' WIP - LPN work orderless completion business flow.');
3372 END IF;
3373
3374 FETCH wip_lpn_serial_cur
3375 INTO l_inventory_item_id
3376 , l_organization_id
3377 , l_lot_number
3378 , l_project_id
3379 , l_task_id
3380 , l_revision
3381 , l_serial_number
3382 , l_subinventory
3383 , l_uom
3384 , l_locator_id;
3385
3386 IF wip_lpn_serial_cur%NOTFOUND THEN
3387 IF (l_debug = 1) THEN
3388 trace('No more serial number returned from wip_lpn_serial_cur cursor');
3389 END IF;
3390 CLOSE wip_lpn_serial_cur;
3391 RETURN;
3392 ELSE
3393 IF (l_debug = 1) THEN
3394 trace('Got the next serial number : ' || l_serial_number);
3395 END IF;
3396 END IF;
3397
3398 ELSIF p_label_type_info.business_flow_code IN(26, 37) THEN --WIP Completion,Manufacturing Cross-Dock(37)
3399 -- Bug 2825748 : Material Label Is Not Printed On WIP Completion.
3400 -- Bug 3896738: Added Manufacturing Cross-Dock flow
3401 IF (l_serial_table_index < l_serial_numbers_table.COUNT) THEN
3402 l_serial_table_index := l_serial_table_index + 1;
3403 l_serial_number := l_serial_numbers_table(l_serial_table_index);
3404 ELSE
3405 FETCH wip_serial_cur
3406 INTO l_inventory_item_id
3407 , l_organization_id
3408 , l_lot_number
3409 , l_cost_group_id
3410 , l_project_id
3411 , l_task_id
3412 , l_uom
3413 , l_revision
3414 , l_fm_serial_number
3415 , l_to_serial_number
3416 , l_subinventory
3417 , l_locator_id
3418 , l_wip_entity_name --Added for Bug 4642062
3419 , l_wip_entity_id;
3420
3421 TRACE(
3422 ' wip_serial_cur '
3423 || ', Item ID='
3424 || l_inventory_item_id
3425 || ', Organization ID='
3426 || l_organization_id
3427 || ', Lot Number='
3428 || l_lot_number
3429 || ', Project ID='
3430 || l_project_id
3431 || ', Cost Group ID='
3432 || l_cost_group_id
3433 || ', Task ID='
3434 || l_task_id
3435 || ', Transaction UOM='
3436 || l_uom
3437 || ', Item Revision='
3438 || l_revision
3439 || ', Subinventory Code='
3440 || l_subinventory
3441 || ', Locator ID='
3442 || l_locator_id
3443 || ', Job Name='
3444 || l_wip_entity_name
3445 || ', Job Id='
3446 || l_wip_entity_id
3447 );
3448
3449 IF wip_serial_cur%NOTFOUND THEN
3450 TRACE(' No more records found for transaction_temp_id in MMTT/MTLT');
3451 l_serial_number := NULL;
3452
3453 CLOSE wip_serial_cur;
3454 ELSE
3455 --Bug #6417575,
3456 -- Fetching WIP Job attributes, based on wip_entity_id passed through
3457 -- transaction_source_id.
3458 -- Currently, printing WIP job information for serial is restricted
3459 -- only for WIP Completion and Serial Label Manual Printing.
3460 IF (l_debug = 1) THEN
3461 trace( 'l_wip_entity_id = ' || l_wip_entity_id);
3462 END IF;
3463 IF (l_wip_entity_id IS NOT NULL) THEN
3464 OPEN wip_attributes_cur;
3465 FETCH wip_attributes_cur INTO l_wip_entity_name
3466 , l_entity_type
3467 , l_net_quantity
3468 , l_scheduled_start_date
3469 , l_scheduled_completion_date
3470 , l_bom_revision
3471 , l_routing_revision;
3472
3473 IF wip_attributes_cur%NOTFOUND THEN
3474 IF (l_debug = 1) THEN
3475 trace( ' No records returned by wip_attributes_cur cursor');
3476 END IF;
3477 END IF;
3478 CLOSE wip_attributes_cur;
3479 END IF;
3480
3481 inv_label.get_number_between_range(
3482 fm_x_number => l_fm_serial_number
3483 , to_x_number => l_to_serial_number
3484 , x_return_status => l_return_status
3485 , x_number_table => l_serial_numbers_table
3486 );
3487
3488 IF l_return_status <> 'S' THEN
3489 fnd_message.set_name('WMS', 'WMS_GET_SER_CUR_FAILED');
3490 fnd_msg_pub.ADD;
3491 RETURN;
3492 END IF;
3493
3494 TRACE(' Count of rows in l_serial_numbers_table ' || l_serial_numbers_table.COUNT);
3495 l_serial_number := l_serial_numbers_table(1);
3496 TRACE('l_serial_number after call to GET_SERIALS_BETWEEN_RANGE ' || l_serial_number);
3497 END IF;
3498 END IF;
3499 -- Flow Labels
3500 ELSIF p_label_type_info.business_flow_code IN(33)
3501 AND p_transaction_identifier > 1 THEN
3502 -- Flow Completion, not MMTT based
3503 IF p_transaction_identifier = 2 THEN
3504 IF (l_serial_table_index < l_serial_numbers_table.COUNT) THEN
3505 l_serial_table_index := l_serial_table_index + 1;
3506 l_serial_number := l_serial_numbers_table(l_serial_table_index);
3507 ELSE
3508 FETCH flow_serial_curs_mti
3509 INTO l_inventory_item_id
3510 , l_organization_id
3511 , l_lot_number
3512 , l_project_id
3513 , l_task_id
3514 , l_revision
3515 , l_fm_serial_number
3516 , l_to_serial_number
3517 , l_subinventory
3518 , l_locator_id -- Added for Bug #5533362
3519 , l_uom;
3520
3521 IF flow_serial_curs_mti%NOTFOUND THEN
3522 IF (l_debug = 1) THEN
3523 TRACE(' No Flow Data found for this given ID:' || p_transaction_id);
3524 END IF;
3525
3526 l_serial_number := NULL;
3527
3528 CLOSE flow_serial_curs_mti;
3529 -- RETURN;
3530 ELSE
3531 inv_label.get_number_between_range(
3532 fm_x_number => l_fm_serial_number
3533 , to_x_number => l_to_serial_number
3534 , x_return_status => l_return_status
3535 , x_number_table => l_serial_numbers_table
3536 );
3537
3538 IF l_return_status <> 'S' THEN
3539 fnd_message.set_name('WMS', 'WMS_GET_SER_CUR_FAILED'); -- Code message for this.
3540 fnd_msg_pub.ADD;
3541 RETURN;
3542 END IF;
3543
3544 IF (l_debug = 1) THEN
3545 TRACE(' Count of rows in l_serial_numbers_table ' || l_serial_numbers_table.COUNT);
3546 END IF;
3547
3548 l_serial_number := l_serial_numbers_table(1);
3549 END IF;
3550 END IF;
3551 ELSIF p_transaction_identifier = 3 THEN
3552 IF (l_serial_table_index < l_serial_numbers_table.COUNT) THEN
3553 l_serial_table_index := l_serial_table_index + 1;
3554 l_serial_number := l_serial_numbers_table(l_serial_table_index);
3555 ELSE
3556 FETCH flow_serial_curs_mol
3557 INTO l_inventory_item_id
3558 , l_organization_id
3559 , l_lot_number
3560 , l_project_id
3561 , l_task_id
3562 , l_revision
3563 , l_fm_serial_number
3564 , l_to_serial_number
3565 , l_subinventory
3566 , l_uom;
3567
3568 IF flow_serial_curs_mol%NOTFOUND THEN
3569 IF (l_debug = 1) THEN
3570 TRACE(' No Flow Data found for this given ID:' || p_transaction_id);
3571 END IF;
3572
3573 l_serial_number := NULL;
3574
3575 CLOSE flow_serial_curs_mol;
3576 --RETURN;
3577 ELSE
3578 inv_label.get_number_between_range(
3579 fm_x_number => l_fm_serial_number
3580 , to_x_number => l_to_serial_number
3581 , x_return_status => l_return_status
3582 , x_number_table => l_serial_numbers_table
3583 );
3584
3585 IF l_return_status <> 'S' THEN
3586 fnd_message.set_name('WMS', 'WMS_GET_SER_CUR_FAILED');
3587 fnd_msg_pub.ADD;
3588 RETURN;
3589 END IF;
3590
3591 IF (l_debug = 1) THEN
3592 TRACE(' Count of rows in l_serial_numbers_table ' || l_serial_numbers_table.COUNT);
3593 END IF;
3594
3595 l_serial_number := l_serial_numbers_table(1);
3596 END IF;
3597 END IF;
3598 ELSE
3599 IF (l_debug = 1) THEN
3600 TRACE(' Invalid transaction_identifier passed' || p_transaction_identifier);
3601 END IF;
3602
3603 RETURN;
3604 END IF;
3605 -- Bug 3836623
3606 -- Fetch more serial numbers
3607 ELSIF p_label_type_info.business_flow_code IN(6) THEN
3608 FETCH wdd_serial_cur
3609 INTO l_inventory_item_id
3610 , l_organization_id
3611 , l_lot_number
3612 , l_project_id
3613 , l_task_id
3614 , l_revision
3615 , l_serial_number
3616 , l_subinventory
3617 , l_uom;
3618
3619 IF wdd_serial_cur%NOTFOUND THEN
3620 IF (l_debug = 1) THEN
3621 TRACE(' No more Serial number found for cross dock');
3622 END IF;
3623 l_serial_number := null;
3624 CLOSE wdd_serial_cur;
3625 RETURN;
3626 ELSE
3627 IF (l_debug = 1) THEN
3628 TRACE(' Found a new Serial number found for cross dock: '||l_serial_number);
3629 END IF;
3630 END IF;
3631 ELSE
3632 l_serial_number := NULL;
3633 END IF;
3634 END LOOP;
3635
3636 IF (rti_serial_lpn_cur%ISOPEN) THEN
3637 CLOSE rti_serial_lpn_cur;
3638 END IF;
3639
3640 IF (rti_serial_msnt_cur%ISOPEN) THEN
3641 CLOSE rti_serial_msnt_cur;
3642 END IF;
3643
3644 IF (mmtt_serial_cur%ISOPEN) THEN
3645 CLOSE mmtt_serial_cur;
3646 END IF;
3647
3648 IF (wdd_serial_cur%ISOPEN) THEN
3649 CLOSE wdd_serial_cur;
3650 END IF;
3651
3652 IF (wip_lpn_serial_cur%ISOPEN) THEN
3653 CLOSE wip_lpn_serial_cur;
3654 END IF;
3655
3656 IF (wda_serial_cur%ISOPEN) THEN
3657 CLOSE wda_serial_cur;
3658 END IF;
3659
3660 IF (wip_serial_cur%ISOPEN) THEN
3661 CLOSE wip_serial_cur;
3662 END IF;
3663
3664 IF (flow_serial_curs_mti%ISOPEN) THEN
3665 CLOSE flow_serial_curs_mti;
3666 END IF;
3667
3668 IF (flow_serial_curs_mol%ISOPEN) THEN
3669 CLOSE flow_serial_curs_mol;
3670 END IF;
3671
3672 --added along with Bugfix 4290536
3673 IF (mtlt_lot_info_cur%ISOPEN) THEN
3674 CLOSE mtlt_lot_info_cur;
3675 END IF;
3676
3677 --added along with Bugfix 4290536
3678 IF (get_lot_info%ISOPEN) THEN
3679 CLOSE get_lot_info;
3680 END IF;
3681
3682 END get_variable_data;
3683
3684 PROCEDURE get_variable_data(
3685 x_variable_content OUT NOCOPY LONG
3686 , x_msg_count OUT NOCOPY NUMBER
3687 , x_msg_data OUT NOCOPY VARCHAR2
3688 , x_return_status OUT NOCOPY VARCHAR2
3689 , p_label_type_info IN inv_label.label_type_rec
3690 , p_transaction_id IN NUMBER
3691 , p_input_param IN mtl_material_transactions_temp%ROWTYPE
3692 , p_transaction_identifier IN NUMBER
3693 ) IS
3694 l_variable_data_tbl inv_label.label_tbl_type;
3695 BEGIN
3696 get_variable_data(
3697 x_variable_content => l_variable_data_tbl
3698 , x_msg_count => x_msg_count
3699 , x_msg_data => x_msg_data
3700 , x_return_status => x_return_status
3701 , p_label_type_info => p_label_type_info
3702 , p_transaction_id => p_transaction_id
3703 , p_input_param => p_input_param
3704 , p_transaction_identifier => p_transaction_identifier
3705 );
3706 x_variable_content := '';
3707
3708 FOR i IN 1 .. l_variable_data_tbl.COUNT() LOOP
3709 x_variable_content := x_variable_content || l_variable_data_tbl(i).label_content;
3710 END LOOP;
3711 END get_variable_data;
3712 END inv_label_pvt2;