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