[Home] [Help]
PACKAGE BODY: APPS.INV_LABEL_PVT3
Source
1 PACKAGE BODY inv_label_pvt3 AS
2 /* $Header: INVLAP3B.pls 120.11 2006/02/23 01:48:21 dchithir ship $ */
3
4 label_b CONSTANT VARCHAR2(50) := '<label';
5 label_e CONSTANT VARCHAR2(50) := '</label>' || fnd_global.local_chr(10);
6 variable_b CONSTANT VARCHAR2(50) := '<variable name= "';
7 variable_e CONSTANT VARCHAR2(50) := '</variable>' || fnd_global.local_chr(10);
8 tag_e CONSTANT VARCHAR2(50) := '>' || fnd_global.local_chr(10);
9 l_debug NUMBER;
10
11 PROCEDURE TRACE(p_message VARCHAR2) IS
12 BEGIN
13 inv_label.TRACE(p_message, 'LABEL_LPN');
14 END TRACE;
15
16 PROCEDURE get_variable_data(
17 x_variable_content OUT NOCOPY inv_label.label_tbl_type
18 , x_msg_count OUT NOCOPY NUMBER
19 , x_msg_data OUT NOCOPY VARCHAR2
20 , x_return_status OUT NOCOPY VARCHAR2
21 , p_label_type_info IN inv_label.label_type_rec
22 , p_transaction_id IN NUMBER
23 , p_input_param IN mtl_material_transactions_temp%ROWTYPE
24 , p_transaction_identifier IN NUMBER
25 ) IS
26
27 ---------------------------------------------------------------------------------------------
28 -- Project: 'Custom Labels' (A 11i10+ Project) |
29 -- Author: Dinesh ([email protected]) |
30 -- Change Description: |
31 -- Following variables were added (as a part of 11i10+ 'Custom Labels' Project) |
32 -- to retrieve and hold the SQL Statement and it's result. |
33 ---------------------------------------------------------------------------------------------
34 l_sql_stmt VARCHAR2(4000);
35 l_sql_stmt_result VARCHAR2(4000) := NULL;
36 TYPE sql_stmt IS REF CURSOR;
37 c_sql_stmt sql_stmt;
38 l_custom_sql_ret_status VARCHAR2(1);
39 l_custom_sql_ret_msg VARCHAR2(2000);
40
41 -- Fix for bug: 4179593 Start
42 l_CustSqlWarnFlagSet BOOLEAN;
43 l_CustSqlErrFlagSet BOOLEAN;
44 l_CustSqlWarnMsg VARCHAR2(2000);
45 l_CustSqlErrMsg VARCHAR2(2000);
46 -- Fix for bug: 4179593 End
47
48 ------------------------End of this change for Custom Labels project code--------------------
49
50 CURSOR c_rti_lpn IS
51 SELECT rti.lpn_id lpn_id
52 , pha.segment1 purchase_order
53 , rti.subinventory
54 , rti.locator_id
55 FROM rcv_transactions_interface rti, po_headers_all pha
56 WHERE rti.interface_transaction_id = p_transaction_id
57 AND rti.po_header_id = pha.po_header_id(+);
58
59 CURSOR c_rti_lpn_inspection IS
60 SELECT rti.transfer_lpn_id transfer_lpn_id
61 , pha.segment1 purchase_order
62 , rti.subinventory
63 , rti.locator_id
64 FROM rcv_transactions_interface rti, po_headers_all pha
65 WHERE rti.interface_transaction_id = p_transaction_id
66 AND rti.po_header_id = pha.po_header_id(+);
67
68 CURSOR c_mmtt_lpn IS
69 SELECT lpn_id
70 , content_lpn_id
71 , transfer_lpn_id
72 , transfer_subinventory
73 , subinventory_code
74 , transaction_type_id
75 , transaction_action_id
76 -- Bug 2515486: Added transaction_type_id, transaction_action_id, inventory_item_id
77 FROM mtl_material_transactions_temp
78 WHERE transaction_temp_id = p_transaction_id
79 AND ROWNUM < 2;
80
81 CURSOR c_mmtt_cart_lpn IS
82 SELECT cartonization_id
83 FROM mtl_material_transactions_temp
84 WHERE transaction_temp_id = p_transaction_id;
85
86 CURSOR c_mmtt_pregen_lpn IS
87 SELECT lpn_id
88 , subinventory_code
89 , locator_id
90 FROM mtl_material_transactions_temp
91 WHERE transaction_temp_id = p_transaction_id;
92
93 CURSOR c_wdd_lpn IS
94 SELECT lpn_id
95 FROM wsh_delivery_details
96 WHERE delivery_detail_id = p_transaction_id;
97
98 -- Bug 2825748 : WIP is passing a transaction_temp_id instead of
99 -- wip_lpn_completions,header_id for both LPN and non-LPN Completions.
100 CURSOR c_wip_lpn IS
101 SELECT transfer_lpn_id
102 FROM mtl_material_transactions_temp mmtt
103 WHERE mmtt.transaction_temp_id = p_transaction_id;
104
105 -- For business flow code of 33, the MMTT, MTI or MOL id is passed
106 -- Depending on the txn identifier being passed,one of the
107 -- following 2 flow csrs or the generic mmtt crsr will be called
108
109 CURSOR c_flow_lpn_mol IS
110 SELECT lpn_id
111 FROM mtl_txn_request_lines
112 WHERE line_id = p_transaction_id;
113
114 CURSOR c_flow_lpn_mti IS
115 SELECT lpn_id
116 FROM mtl_transactions_interface
117 WHERE transaction_interface_id = p_transaction_id;
118
119 CURSOR c_wnd_lpn IS
120 SELECT wdd2.lpn_id
121 FROM wsh_new_deliveries wnd
122 , wsh_delivery_assignments_v wda
123 , wsh_delivery_details wdd1
124 , wsh_delivery_details wdd2
125 WHERE wnd.delivery_id = p_transaction_id
126 AND wnd.delivery_id = wda.delivery_id
127 AND wdd1.delivery_detail_id = wda.delivery_detail_id
128 AND wdd2.delivery_detail_id = wda.parent_delivery_detail_id;
129
130 l_subinventory_code VARCHAR2(10) := NULL;
131 l_locator_id NUMBER := NULL;
132 l_locator VARCHAR2(204) := NULL;
133
134 /* Patchset J- Label printing support for Inbound.
135 * This cursor will get the distinct LPN, Transfer LPN, and their parent LPN and outermost LPN
136 * to be printed. Note that in a group of RTI records, there maybe many duplicate LPNs
137 * so it is important to get a distinct list of LPNs so that they don't get printed many times.
138 * After fetching from this cursor, the results will be saved into the new table of record type
139 * rcv_lpn_table_type.
140 * Note: records in RT are filtered by transaction_type and business_flow_code
141 * becuase it is possible for label-API to be called multiple times by RCV-TM
142 * in the case of ROI, when multiple trx.types are present in a group
143 */
144
145 CURSOR c_rt_lpn IS
146 SELECT DISTINCT all_lpn.lpn_id
147 , pha.segment1 purchase_order
148 , all_lpn.subinventory
149 , all_lpn.locator_id
150 FROM (-- LPN_ID
151 SELECT lpn_id
152 , po_header_id
153 , subinventory
154 , locator_id
155 FROM rcv_transactions rt
156 WHERE lpn_id IS NOT NULL
157 AND rt.group_id = p_transaction_id
158 AND ((rt.transaction_type IN ('ACCEPT', 'REJECT')
159 AND p_label_type_info.business_flow_code = 2)
160 OR (rt.transaction_type = 'DELIVER'
161 AND p_label_type_info.business_flow_code in (3,4))
162 OR (rt.transaction_type = 'RECEIVE'
163 /* modified for bug 4293052 :aujain*/
164 -- AND rt.routing_header_id <> 3
165 AND p_label_type_info.business_flow_code = 1
166 )
167 )
168 UNION ALL
169 -- PARENT LPN of LPN_ID
170 SELECT lpn.parent_lpn_id
171 , rt.po_header_id
172 , rt.subinventory
173 , rt.locator_id
174 FROM wms_license_plate_numbers lpn, rcv_transactions rt
175 WHERE lpn.lpn_id = rt.lpn_id
176 -- AND lpn.parent_lpn_id <> rt.lpn_id
177 AND rt.group_id = p_transaction_id
178 AND lpn.parent_lpn_id is not null
179 AND ((rt.transaction_type IN ('ACCEPT', 'REJECT')
180 AND p_label_type_info.business_flow_code = 2)
181 OR (rt.transaction_type = 'DELIVER'
182 AND p_label_type_info.business_flow_code in (3,4))
183 OR (rt.transaction_type = 'RECEIVE'
184 /* modified for bug 4293052 :aujain*/
185 -- AND rt.routing_header_id <> 3
186 AND p_label_type_info.business_flow_code = 1
187 )
188 )
189 UNION ALL
190 -- OUTERMOSE LPN of LPN_ID, and different than the LPN and parent LPN
191 SELECT lpn.outermost_lpn_id
192 , rt.po_header_id
193 , rt.subinventory
194 , rt.locator_id
195 FROM wms_license_plate_numbers lpn, rcv_transactions rt
196 WHERE lpn.lpn_id = rt.lpn_id
197 -- AND lpn.outermost_lpn_id <> lpn.lpn_id
198 -- AND lpn.outermost_lpn_id <> lpn.parent_lpn_id
199 AND rt.group_id = p_transaction_id
200 AND ((rt.transaction_type IN ('ACCEPT', 'REJECT')
201 AND p_label_type_info.business_flow_code = 2)
202 OR (rt.transaction_type = 'DELIVER'
203 AND p_label_type_info.business_flow_code in (3,4))
204 OR (rt.transaction_type = 'RECEIVE'
205 /* modified for bug 4293052 :aujain*/
206 -- AND rt.routing_header_id <> 3
207 AND p_label_type_info.business_flow_code = 1
208 )
209 )
210 UNION ALL
211 -- Transfer LPN (different than LPN)
212 SELECT transfer_lpn_id lpn_id
213 , po_header_id
214 , subinventory
215 , locator_id
216 FROM rcv_transactions rt
217 WHERE
218 --rt.transfer_lpn_id <> rt.lpn_id AND
219 rt.group_id = p_transaction_id
220 AND ((rt.transaction_type IN ('ACCEPT', 'REJECT')
221 AND p_label_type_info.business_flow_code = 2)
222 OR (rt.transaction_type = 'DELIVER'
223 AND p_label_type_info.business_flow_code in (3,4))
224 OR (rt.transaction_type = 'RECEIVE'
225 /* modified for bug 4293052 :aujain*/
226 --AND rt.routing_header_id <> 3
227 AND p_label_type_info.business_flow_code = 1
228 )
229 )
230 UNION ALL
231 -- Parent LPN of Transfer LPN
232 SELECT lpn.parent_lpn_id
233 , rt.po_header_id
234 , rt.subinventory
235 , rt.locator_id
236 FROM wms_license_plate_numbers lpn, rcv_transactions rt
237 WHERE lpn.lpn_id = rt.transfer_lpn_id
238 -- AND rt.transfer_lpn_id <> rt.lpn_id
239 -- AND lpn.parent_lpn_id <> lpn.lpn_id
240 AND lpn.parent_lpn_id is not null
241 AND rt.group_id = p_transaction_id
242 AND ((rt.transaction_type IN ('ACCEPT', 'REJECT')
243 AND p_label_type_info.business_flow_code = 2)
244 OR (rt.transaction_type = 'DELIVER'
245 AND p_label_type_info.business_flow_code in (3,4))
246 OR (rt.transaction_type = 'RECEIVE'
247 /* modified for bug 4293052 :aujain*/
248 --AND rt.routing_header_id <> 3
249 AND p_label_type_info.business_flow_code = 1
250 )
251 )
252 UNION ALL
253 -- Outermost LPN of Transfer LPN
254 SELECT lpn.outermost_lpn_id
255 , rt.po_header_id
256 , rt.subinventory
257 , rt.locator_id
258 FROM wms_license_plate_numbers lpn, rcv_transactions rt
259 WHERE lpn.lpn_id = rt.transfer_lpn_id
260 -- AND rt.transfer_lpn_id <> rt.lpn_id
261 -- AND lpn.outermost_lpn_id <> lpn.lpn_id
262 -- AND lpn.outermost_lpn_id <> lpn.parent_lpn_id
263 AND rt.group_id = p_transaction_id
264 AND ((rt.transaction_type IN ('ACCEPT', 'REJECT')
265 AND p_label_type_info.business_flow_code = 2)
266 OR (rt.transaction_type = 'DELIVER'
267 AND p_label_type_info.business_flow_code in (3,4))
268 OR (rt.transaction_type = 'RECEIVE'
269 /* modified for bug 4293052 :aujain*/
270 --AND rt.routing_header_id <> 3
271 AND p_label_type_info.business_flow_code = 1
272 )
273 )) all_lpn
274 , po_headers_all pha
275 , wms_license_plate_numbers wlpn -- Bug 3836623
276 WHERE pha.po_header_id(+) = all_lpn.po_header_id
277 -- Bug 3836623
278 -- Add check for LPN context
279 -- When cross docking happens, label printing are called for both cross docking and putaway
280 -- To prevent duplicate labels
281 -- For putaway business flow, only print if LPN Context is not Picked (11)
282 AND wlpn.lpn_id = all_lpn.lpn_id
283 AND (p_label_type_info.business_flow_code <> 4 OR
284 (p_label_type_info.business_flow_code = 4 AND
285 wlpn.lpn_context <> 11));
286
287
288 CURSOR c_lpn(p_lpn_id NUMBER) IS
289 SELECT lpn.lpn_id lpn_id
290 , lpn.license_plate_number lpn
291 , msik.concatenated_segments lpn_container_item
292 , lpn.inventory_item_id inventory_item_id
293 , NVL(l_subinventory_code, lpn.subinventory_code) subinventory_code
294 , lpn.revision revision
295 , lpn.locator_id locator_id
296 , inv_project.get_locsegs(
297 milkfv.inventory_location_id
298 , milkfv.organization_id
299 ) LOCATOR
300 , lpn.lot_number lot_number
301 , lpn.serial_number serial_number
302 , plpn.license_plate_number parent_lpn
303 , olpn.license_plate_number outermost_lpn
304 , mp.organization_code ORGANIZATION
305 , lpn.organization_id organization_id
306 , lpn.content_volume volume
307 , lpn.content_volume_uom_code volume_uom
308 , lpn.gross_weight gross_weight
309 , lpn.gross_weight_uom_code gross_weight_uom
310 , lpn.tare_weight tare_weight
311 , lpn.tare_weight_uom_code tare_weight_uom
312 , lpn.attribute_category CATEGORY
313 , lpn.attribute1 attribute1
314 , lpn.attribute2 attribute2
315 , lpn.attribute3 attribute3
316 , lpn.attribute4 attribute4
317 , lpn.attribute5 attribute5
318 , lpn.attribute6 attribute6
319 , lpn.attribute7 attribute7
320 , lpn.attribute8 attribute8
321 , lpn.attribute9 attribute9
322 , lpn.attribute10 attribute10
323 , lpn.attribute11 attribute11
324 , lpn.attribute12 attribute12
325 , lpn.attribute13 attribute13
326 , lpn.attribute14 attribute14
327 , lpn.attribute15 attribute15
328 FROM wms_license_plate_numbers lpn
329 , wms_license_plate_numbers plpn
330 , wms_license_plate_numbers olpn
331 , mtl_system_items_kfv msik
332 , mtl_parameters mp
333 , mtl_item_locations milkfv
334 WHERE lpn.lpn_id = p_lpn_id
335 AND mp.organization_id(+) = lpn.organization_id
336 AND msik.inventory_item_id(+) = lpn.inventory_item_id
337 AND msik.organization_id(+) = lpn.organization_id
338 AND milkfv.organization_id(+) = lpn.organization_id
339 AND milkfv.subinventory_code(+) =
340 NVL(l_subinventory_code, lpn.subinventory_code)
341 AND milkfv.inventory_location_id(+) =
342 NVL(l_locator_id, lpn.locator_id)
343 AND plpn.lpn_id(+) = lpn.parent_lpn_id
344 AND olpn.lpn_id(+) = lpn.outermost_lpn_id;
345
346
347 /* Bug 4891916 -Added the cursor to fetch records from mcce
348 at the time of cycle count entry for a particular entry*/
349 CURSOR c_mcce_lpn_cur IS
350 SELECT parent_lpn_id
351 , subinventory
352 , locator_id
353 , mcch.cycle_count_header_name
354 , ppf.full_name requestor
355 FROM mtl_cycle_count_headers mcch,
356 mtl_cycle_count_entries mcce,
357 per_people_f ppf
358 WHERE cycle_count_entry_id= p_transaction_id
359 AND ppf.person_id(+) = mcce.counted_by_employee_id_current
360 AND mcce.cycle_count_header_id=mcch.cycle_count_header_id;
361 /* End of fix for Bug 4891916 */
362
363 /* Bug 4891916- Added this cursor to get details like cycle count header name
364 and counter for the entry for the label printed at the time of
365 cycle count approval*/
366
367 CURSOR cc_det_approval IS
368 SELECT mcch.cycle_count_header_name,
369 ppf.full_name requestor
370 FROM mtl_cycle_count_headers mcch,
371 mtl_cycle_count_entries mcce,
372 per_people_f ppf,
373 mtl_material_transactions_temp mmtt
374 WHERE mmtt.transaction_temp_id= p_transaction_id
375 AND mmtt.cycle_count_id = mcce.cycle_count_entry_id
376 AND mcce.cycle_count_header_id = mcch.cycle_count_header_id
377 AND ppf.person_id(+) = mcce.counted_by_employee_id_current ;
378
379 /* End of fix for Bug 4891916 */
380
381
382 -- MOAC: Replaced the po_line_locations
383 -- view with a _ALL table. The where
384 -- clause is sufficient to stripe by
385 -- OU
386
387 /* bug 3841820 */
388 CURSOR c_asn_lpn IS
389 SELECT distinct
390 all_lpn.lpn_id
391 , pha.segment1 purchase_order
392 , all_lpn.subinventory_code
393 , all_lpn.locator_id
394 FROM(
395 select lpn.lpn_id
396 , rsl.po_header_id, rsl.po_line_id
397 , lpn.subinventory_code, lpn.locator_id
398 , rsh.shipment_header_id
399 , rsl.po_line_location_id
400 from wms_license_plate_numbers lpn,
401 rcv_shipment_headers rsh,
402 rcv_shipment_lines rsl
403 where lpn.source_name = rsh.shipment_num
404 AND lpn.lpn_context = 7
405 AND rsl.shipment_header_id = rsh.shipment_header_id
406 and rsh.shipment_header_id = p_transaction_id
407 and rsl.asn_lpn_id = lpn.lpn_id
408 AND rsh.asn_type = 'ASN'
409 UNION
410 select lpn.parent_lpn_id
411 , rsl.po_header_id, rsl.po_line_id
412 , lpn.subinventory_code, lpn.locator_id
413 , rsh.shipment_header_id
414 , rsl.po_line_location_id
415 from wms_license_plate_numbers lpn,
416 rcv_shipment_headers rsh,
417 rcv_shipment_lines rsl
418 where lpn.source_name = rsh.shipment_num
419 AND lpn.lpn_context = 7
420 AND rsl.shipment_header_id = rsh.shipment_header_id
421 and rsl.asn_lpn_id = lpn.lpn_id
422 and rsh.shipment_header_id = p_transaction_id
423 AND rsh.asn_type = 'ASN'
424 UNION
425 select lpn.outermost_lpn_id
426 , rsl.po_header_id, rsl.po_line_id
427 , lpn.subinventory_code, lpn.locator_id
428 , rsh.shipment_header_id
429 , rsl.po_line_location_id
430 from wms_license_plate_numbers lpn,
431 rcv_shipment_headers rsh,
432 rcv_shipment_lines rsl
433 where lpn.source_name = rsh.shipment_num
434 AND lpn.lpn_context = 7
435 AND rsl.shipment_header_id = rsh.shipment_header_id
436 and rsh.shipment_header_id = p_transaction_id
437 and rsl.asn_lpn_id = lpn.lpn_id
438 AND rsh.asn_type = 'ASN'
439 ) all_lpn
440 , po_headers_all pha
441 , po_lines_all pol
442 , rcv_shipment_headers rsh
443 , po_line_locations_all pll
444 WHERE pha.po_header_id(+) = all_lpn.po_header_id
445 AND rsh.shipment_header_id(+) = all_lpn.shipment_header_id
446 AND pol.po_line_id (+) = all_lpn.po_line_id
447 AND pol.po_header_id (+) = all_lpn.po_header_id
448 AND pll.line_location_id(+) = all_lpn.po_line_location_id;
449
450 --R12 PROJECT LABEL SET with RFID
451
452 CURSOR c_label_formats_in_set(p_format_set_id IN NUMBER) IS
453 select wlfs.format_id label_format_id, wlf.label_entity_type --FOR SETS
454 from wms_label_set_formats wlfs , wms_label_formats wlf
455 where WLFS.SET_ID = p_format_set_id
456 and wlfs.set_id = wlf.label_format_id
457 and wlf.label_entity_type = 1
458 AND WLF.DOCUMENT_ID = 3
459 UNION --FOR FORMATS
460 select label_format_id,nvl(wlf.label_entity_type,0)
461 from wms_label_formats wlf
462 where wlf.label_format_id = p_format_set_id
463 and nvl(wlf.label_entity_type,0) = 0--for label formats only validation
464 AND WLF.DOCUMENT_ID = 3 ;
465
466
467
468 l_lpn_id NUMBER;
469 l_content_lpn_id NUMBER;
470 l_transfer_lpn_id NUMBER;
471 l_from_lpn_id NUMBER;
472 l_purchase_order po_headers_all.segment1%TYPE;
473 l_content_item_data LONG;
474 l_selected_fields inv_label.label_field_variable_tbl_type;
475 l_selected_fields_count NUMBER;
476 l_content_rec_index NUMBER := 0;
477 l_label_format_set_id NUMBER := 0;
478 l_label_format_id NUMBER;
479 l_label_format VARCHAR2(100);
480 l_printer VARCHAR2(30);
481 l_printer_sub VARCHAR2(30) := NULL;
482 l_api_name VARCHAR2(20) := 'get_variable_data';
483 l_return_status VARCHAR2(240);
484 l_error_message VARCHAR2(240);
485 l_msg_count NUMBER;
486 l_api_status VARCHAR2(240);
487 l_msg_data VARCHAR2(240);
488 l_lpn_table inv_label.lpn_table_type;
489 l_rcv_lpn_table inv_label_pvt3.rcv_lpn_table_type;
490 -- Bug 2515486
491 l_transaction_type_id NUMBER := 0;
492 l_transaction_action_id NUMBER := 0;
493 i NUMBER;
494 -- Added l_transaction_identifier, for flow
495 -- Depending on when it is called, the driving table might be different
496 -- 1 means MMTT is the driving table
497 -- 2 means MTI is the driving table
498 -- 3 means Mtl_txn_request_lines is the driving table
499
500 l_transaction_identifier NUMBER := 0;
501 l_label_index NUMBER;
502 l_label_request_id NUMBER;
503 --I cleanup, use l_prev_format_id to record the previous label format
504 l_prev_format_id NUMBER;
505 -- I cleanup, user l_prev_sub to record the previous subinventory
506 --so that get_printer is not called if the subinventory is the same
507 l_prev_sub VARCHAR2(30);
508 -- a list of columns that are selected for format
509 l_column_name_list LONG;
510
511 --Bug 4891916. Added the following local variables to store the counter and cycle count name.
512 l_requestor per_people_f.full_name%TYPE;
513 l_cycle_count_name mtl_cycle_count_headers.cycle_count_header_name%TYPE;
514
515 l_patch_level NUMBER;
516 l_count NUMBER;
517
518 l_rcv_isp_header rcv_isp_header_rec; -- Header-level info for ASN iSP
519
520 -- Variable for EPC Generation
521 -- Added for 11.5.10+ RFID Compliance project,
522 -- Modified in R12
523 l_epc VARCHAR2(300);
524 l_epc_ret_status VARCHAR2(10);
525 l_epc_ret_msg VARCHAR2(1000);
526 l_label_status VARCHAR2(1);
527 l_label_err_msg VARCHAR2(1000);
528 l_is_epc_exist VARCHAR2(1) := 'N';
529 l_label_formats_in_set NUMBER;
530
531 BEGIN
532 -- Initialize return status as success
533 x_return_status := fnd_api.g_ret_sts_success;
534 l_debug := inv_label.l_debug;
535
536 IF (l_debug = 1) THEN
537 TRACE('**In PVT3: LPN label**');
538 TRACE(
539 ' Business_flow='
540 || p_label_type_info.business_flow_code
541 || ', Transaction ID='
542 || p_transaction_id
543 || ', Transaction Identifier='
544 || p_transaction_identifier
545 );
546 END IF;
547
548 IF (inv_rcv_common_apis.g_inv_patch_level >= inv_rcv_common_apis.g_patchset_j)
549 AND (inv_rcv_common_apis.g_po_patch_level >= inv_rcv_common_apis.g_patchset_j_po)
550 THEN
551 l_patch_level := 1;
552 ELSIF (inv_rcv_common_apis.g_inv_patch_level < inv_rcv_common_apis.g_patchset_j)
553 AND
554 (inv_rcv_common_apis.g_po_patch_level < inv_rcv_common_apis.g_patchset_j_po)
555 THEN
556 l_patch_level := 0;
557 END IF;
558
559
560 -- Get l_lpn_id
561 IF p_transaction_id IS NOT NULL THEN
562 -- txn driven
563 i := 1;
564
565 IF p_label_type_info.business_flow_code IN (1, 2, 3, 4) THEN
566 -- Receipt, Inspection, Delivery
567 -- Getting lpn_id from RTI
568 /* Use the following loop for code below Inventory Patchset J level */
569 IF (l_patch_level = 0)
570 THEN
571 trace('Patch level less than J');
572 if p_label_type_info.business_flow_code = 2 THEN -- Inspection
573 FOR v_rti_lpn_inspection IN c_rti_lpn_inspection LOOP
574 l_lpn_table(i) := v_rti_lpn_inspection.transfer_lpn_id;
575 l_purchase_order := v_rti_lpn_inspection.purchase_order;
576 l_subinventory_code := v_rti_lpn_inspection.subinventory;
577 l_locator_id := v_rti_lpn_inspection.locator_id;
578 i := i + 1;
579 END LOOP;
580 else -- else Receipt, delivery, putaway
581 FOR v_rti_lpn IN c_rti_lpn LOOP
582 l_lpn_table(i) := v_rti_lpn.lpn_id;
583 l_purchase_order := v_rti_lpn.purchase_order;
584 l_subinventory_code := v_rti_lpn.subinventory;
585 l_locator_id := v_rti_lpn.locator_id;
586 i := i + 1;
587 END LOOP;
588 end if;
589 END IF;
590
591 /* We will open up the new cursor c_rt_lpn from Patchset J onwards */
592 IF (l_patch_level = 1) THEN
593 trace('Patchset J code ');
594 FOR v_rti_lpn IN c_rt_lpn LOOP
595 l_rcv_lpn_table(i).lpn_id := v_rti_lpn.lpn_id;
596 l_rcv_lpn_table(i).purchase_order := v_rti_lpn.purchase_order;
597 l_rcv_lpn_table(i).subinventory_code := v_rti_lpn.subinventory;
598 l_rcv_lpn_table(i).locator_id := v_rti_lpn.locator_id;
599 i := i + 1;
600 END LOOP;
601 END IF;
602 ELSIF p_label_type_info.business_flow_code IN (6) THEN
603 -- Cross-Dock and Pick Drop
604 -- The delivery_detail_id of the line in WDD which has the LPN_ID
605 -- is passed , get lpn_id from WDD lines
606 FOR v_wdd_lpn IN c_wdd_lpn LOOP
607 l_lpn_table(i) := v_wdd_lpn.lpn_id;
608 i := i + 1;
609 END LOOP;
610 /* bug 3841820 */
611 ELSIF p_label_type_info.business_flow_code = INV_LABEL.WMS_BF_IMPORT_ASN AND
612 p_transaction_identifier = INV_LABEL.TRX_ID_RSH THEN
613 trace('business flow code is asn import and txnid is 6');
614 -- New Architecture for ASN : Get LPN details from RSH :J-DEV
615 -- Applicable with DM.J and IProc.J
616 -- First retrieve the header level info
617 SELECT shipment_num asn_num, shipped_date shipment_date,
618 expected_receipt_date,freight_terms,
619 freight_carrier_code, num_of_containers,
620 bill_of_lading, waybill_airbill_num,
621 packing_slip,
622 packaging_code, special_handling_code,
623 receipt_num, comments
624 INTO l_rcv_isp_header.asn_num, l_rcv_isp_header.shipment_date,
625 l_rcv_isp_header.expected_receipt_date, l_rcv_isp_header.freight_terms,
626 l_rcv_isp_header.freight_carrier, l_rcv_isp_header.num_of_containers,
627 l_rcv_isp_header.bill_of_lading, l_rcv_isp_header.waybill_airbill_num,
628 l_rcv_isp_header.packing_slip,
629 l_rcv_isp_header.packaging_code, l_rcv_isp_header.special_handling_code,
630 l_rcv_isp_header.receipt_num, l_rcv_isp_header.comments
631 FROM rcv_shipment_headers
632 WHERE shipment_header_id = p_transaction_id;
633
634 FOR v_asn_lpn IN c_asn_lpn LOOP
635 l_rcv_lpn_table(i).lpn_id := v_asn_lpn.lpn_id;
636 l_rcv_lpn_table(i).purchase_order := v_asn_lpn.purchase_order;
637 l_rcv_lpn_table(i).subinventory_code := v_asn_lpn.subinventory_code;
638 l_rcv_lpn_table(i).locator_id := v_asn_lpn.locator_id;
639 i := i + 1;
640 END LOOP;
641 -- Bug 4277718
642 -- for WIP completion, lpn_id is used rather than transfer_lpn_id
643 -- Changed to use c_mmtt_lpn
644 /*ELSIF p_label_type_info.business_flow_code IN (26) THEN
645 -- WIP Completion
646 FOR v_wip_lpn IN c_wip_lpn LOOP
647 l_lpn_table(i) := v_wip_lpn.transfer_lpn_id;
648 i := i + 1;
649 END LOOP;*/
650 ELSIF p_label_type_info.business_flow_code IN (21) THEN
651 -- Ship confirm, delivery_id is passed
652 -- Get all the LPNs for this delivery
653 FOR v_wnd_lpn IN c_wnd_lpn LOOP
654 l_lpn_table(i) := v_wnd_lpn.lpn_id;
655 i := i + 1;
656 END LOOP;
657 ELSIF p_label_type_info.business_flow_code IN (22) THEN
658 -- Cartonization:
659 -- According to the new design, the LPN ID is passed in.
660 -- Print the LPN inforamtion for the LPN passed.
661 l_lpn_table(1) := p_transaction_id;
662 ELSIF p_label_type_info.business_flow_code IN (27) THEN
663 -- Putaway pregeneration
664 -- Get lpn_id from mmtt
665 FOR v_pregen_lpn IN c_mmtt_pregen_lpn LOOP
666 l_lpn_table(1) := v_pregen_lpn.lpn_id;
667 l_subinventory_code := v_pregen_lpn.subinventory_code;
668 l_locator_id := v_pregen_lpn.locator_id;
669 END LOOP;
670 ELSIF p_label_type_info.business_flow_code IN (33)
671 AND p_transaction_identifier > 1 THEN
672 -- Flow Completion, not MMTT based
673
674 IF p_transaction_identifier = 2 THEN
675 IF (l_debug = 1) THEN
676 TRACE('Flow Label - MTI based');
677 END IF;
678
679 FOR v_flow_mti_lpn IN c_flow_lpn_mti LOOP
680 l_lpn_table(i) := v_flow_mti_lpn.lpn_id;
681 i := i + 1;
682 END LOOP;
683 ELSIF p_transaction_identifier = 3 THEN
684 IF (l_debug = 1) THEN
685 TRACE('Flow Label - MOL based');
686 END IF;
687
688 FOR v_flow_mol_lpn IN c_flow_lpn_mti LOOP
689 l_lpn_table(i) := v_flow_mol_lpn.lpn_id;
690 i := i + 1;
691 END LOOP;
692 END IF;
693 -- Fix bug 2167545-1: Cost Group Update(11) is calling label printing through TM
694 -- not manually, add 11 in the following group.
695
696
697 -- Added Flow business code., if it is mmtt based,we can
698 -- USE the same crsr
699 -- Bug 4277718
700 -- for WIP completion, lpn_id is used rather than transfer_lpn_id
701 -- Changed to use c_mmtt_lpn
702
703 --Bug 4891916. Modified the condition for business flow for cycle count
704 --by checking for the business flow 8 and transaction_identifier as 5
705
706 ELSIF p_label_type_info.business_flow_code IN
707 (7,/* 8,*/ 9, 11, 12, 13, 14, 15, 18, 19, 20, 23, 28, 29, 30, 34, 26)
708 OR (p_label_type_info.business_flow_code IN (33) AND p_transaction_identifier = 1)
709 OR(p_label_type_info.business_flow_code = 8 AND p_transaction_identifier = 5)
710 THEN
711 -- Obtain lpn_id, content_lpn_id, transfer_lpn_id from
712 -- MMTT record.
713 OPEN c_mmtt_lpn;
714 FETCH c_mmtt_lpn INTO l_from_lpn_id
715 , l_content_lpn_id
716 , l_transfer_lpn_id
717 , l_subinventory_code
718 , l_printer_sub
719 , l_transaction_type_id
720 , l_transaction_action_id;
721
722 -- Bug 2515486: Added transaction_type_id, transaction_action_id, inventory_item_id ;
723
724 IF (l_debug = 1) THEN
725 TRACE(
726 'From LPN ID : '
727 || l_from_lpn_id
728 || ',Content LPN ID : '
729 || l_content_lpn_id
730 || ',Transfer LPN ID : '
731 || l_transfer_lpn_id
732 || ',Transaction Type ID : '
733 || l_transaction_type_id
734 || ',Transaction Action ID : '
735 || l_transaction_action_id
736 );
737 END IF;
738
739 IF c_mmtt_lpn%NOTFOUND THEN
740 IF (l_debug = 1) THEN
741 TRACE(' No lpn_id found in MMTT for given ID: '|| p_transaction_id);
742 END IF;
743
744 CLOSE c_mmtt_lpn;
745 RETURN;
746 ELSE
747 CLOSE c_mmtt_lpn;
748
749 --Bug 4891916. For cycle count, opened the cursor to fetch values for
750 --cycle count header name and counter
751 IF p_label_type_info.business_flow_code = 8 THEN
752
753 OPEN cc_det_approval ;
754 FETCH cc_det_approval
755 INTO l_cycle_count_name
756 , l_requestor ;
757
758 IF cc_det_approval%NOTFOUND THEN
759 IF (l_debug = 1) THEN
760 TRACE(' No record found in MMTT for a cycle count id for given txn_temp_id: ' || p_transaction_id);
761 END IF;
762 CLOSE cc_det_approval;
763 ELSE
764 CLOSE cc_det_approval ;
765 END IF;
766
767 END IF;--End of business flow=8 condition
768
769 --End of fix for Bug 4891916
770
771 -- Bug 2515486
772 -- This check ensures that the content LPN ID is not added to the l_lpn_table for
773 -- LPN Consolidation.
774 --Bug 3277260
775 -- Updated the condition to make sure that the LPN ID is not added for Pick-Drop
776 -- Business Flow(19).
777 IF (l_content_lpn_id IS NOT NULL) THEN
778 IF ((l_transaction_type_id = 87 AND l_transaction_action_id = 50) AND
779 (p_label_type_info.business_flow_code = 20 OR p_label_type_info.business_flow_code = 19)) THEN
780 IF (l_debug = 1) THEN
781 TRACE('The Content LPN ID is not added to the l_lpn_table');
782 END IF;
783 ELSE
784 l_lpn_table(i) := l_content_lpn_id;
785
786 IF (l_debug = 1) THEN
787 TRACE('Content LPN ID has been added to the l_lpn_table');
788 END IF;
789
790 i := i + 1;
791 END IF;
792 END IF;
793
794 /* Start of fix for bug # 4751587 */
795 /* The following condition has been added for fixing the bug # 4751587
796 For Cost Group Update Bussiness Flow (11), only one label has to be generated with
797 the updated cost group. Hence the following code (incrementing i, which controls the
798 loop iteration) will be executed only if the business flow code is not 11
799 i.e. Cost Group Update Business flow */
800
801 IF (p_label_type_info.business_flow_code NOT IN (11, 28, 29)) THEN -- Modified for bug # 4911236
802 IF (l_transfer_lpn_id IS NOT NULL)
803 AND(NVL(l_transfer_lpn_id, -999) <> NVL(l_content_lpn_id, -999)) THEN
804 l_lpn_table(i) := l_transfer_lpn_id;
805 i := i + 1;
806 END IF;
807 END IF;
808
809 /* IF (l_transfer_lpn_id IS NOT NULL)
810 AND (NVL(l_transfer_lpn_id, -999) <> NVL(l_content_lpn_id, -999)) THEN
811 l_lpn_table(i) := l_transfer_lpn_id;
812 i := i + 1;
813 END IF; */
814
815 /* End of fix for bug # 4751587 */
816
817
818 -- Bug 2367828 : In case of LPN Splits, the LPN labels were being printed for
819 -- the new LPN being generated, but nothing for the existing LPN from which the
820 -- the new LPN was being split. l_from_lpn_id is the mmtt.lpn_id(the from LPN)
821 IF (l_from_lpn_id IS NOT NULL
822 AND p_label_type_info.business_flow_code NOT IN(28, 29)) THEN -- Added for bug # 4911236
823 l_lpn_table(i) := l_from_lpn_id;
824 ELSIF (p_label_type_info.business_flow_code IN (28, 29)) THEN -- Added for bug # 4911236
825 l_lpn_table(i) := l_transfer_lpn_id;
826 END IF;
827
828 /* for pick load and replenishment load, need the from sub to get_printer
829 , otherwise, use to_sub to get printer */
830 IF p_label_type_info.business_flow_code NOT IN (18, 34) THEN
831 l_printer_sub := NULL;
832 END IF;
833 END IF;
834 -- 18th February 2002 : Commented out below for fix to bug 2219171 for Qualcomm. Hence forth the
835 -- WMSTASKB.pls will be calling label printing at Pick Load and WIP Pick Load with the
836 -- transaction_temp_id as opposed to the transaction_header_id earlier. These business flows(18, 28)
837 -- have been added to the above call.
838 -- ELSIF p_label_type_info.business_flow_code in (18,28,34) THEN
839 -- Pick Load
840 -- Get transfer_lpn_id from mmtt with the given header_id
841 -- FOR v_mmtt_header_lpn IN c_mmtt_header_lpn LOOP
842 -- l_lpn_table(i) := v_mmtt_header_lpn.transfer_lpn_id;
843 -- i := i +1;
844 -- END LOOP;
845
846 --Bug 4891916. Added the condition to open the cursor to fetch from mcce
847 --by checking for business flow 8 and transaction identifier 4
848 ELSIF p_label_type_info.business_flow_code = 8 AND p_transaction_identifier = 4 THEN
849 IF (l_debug = 1) THEN
850 TRACE(' IN the condition for bus flow 8 and pti 4 ');
851 END IF;
852
853 OPEN c_mcce_lpn_cur ;
854
855 FETCH c_mcce_lpn_cur
856 INTO l_lpn_id,
857 l_subinventory_code,
858 l_locator_id,
859 l_cycle_count_name,
860 l_requestor ;
861 IF c_mcce_lpn_cur%NOTFOUND THEN
862 IF (l_debug = 1) THEN
863 TRACE(' No record in mcce for this transaction_id:' || p_transaction_id);
864 END IF;
865
866 CLOSE c_mcce_lpn_cur;
867 RETURN;
868 ELSE
869 IF l_lpn_id IS NOT NULL THEN
870 l_lpn_table(1) := l_lpn_id;
871 END IF;
872 CLOSE c_mcce_lpn_cur ;
873 END IF;
874 --End of fix for Bug 4891916
875 ELSE
876 IF (l_debug = 1) THEN
877 TRACE(
878 ' Invalid business flow code '
879 || p_label_type_info.business_flow_code
880 );
881 END IF;
882
883 RETURN;
884 END IF;
885 ELSE
886 -- On demand, get information from input_param
887 -- for transactions which don't have a mmtt row in the table,
888 -- they will also call in a manual mode, they are
889 -- 5 LPN Correction/Update
890 -- 10 Material Status update
891 -- 16 LPN Generation
892 -- 25 Import ASN
893 l_lpn_table(1) := p_input_param.lpn_id;
894 END IF;
895
896 IF (l_debug = 1) THEN
897 TRACE(' No. of LPN_IDs found in l_lpn_table: '|| l_lpn_table.COUNT);
898 END IF;
899
900 IF (l_debug = 1) THEN
901 FOR i IN 1 .. l_lpn_table.COUNT LOOP
902 TRACE(' LPN_ID('|| i || ')' || l_lpn_table(i));
903 END LOOP;
904 END IF;
905
906 IF p_label_type_info.business_flow_code = 22 THEN
907 -- Cartonization, only print the distinct LPN
908 IF (l_debug = 1) THEN
909 TRACE(' G_LPN_ID = '|| g_lpn_id);
910 END IF;
911
912 IF g_lpn_id = -1 THEN
913 g_lpn_id := l_lpn_table(1);
914 ELSIF l_lpn_table(1) = g_lpn_id THEN
915 RETURN;
916 ELSE
917 g_lpn_id := l_lpn_table(1);
918 END IF;
919 END IF;
920
921 IF l_lpn_table.COUNT = 0 AND l_rcv_lpn_table.count = 0 THEN
922 IF (l_debug = 1) THEN
923 TRACE(' No LPN found, can not process ');
924 END IF;
925
926 RETURN;
927 END IF;
928
929
930 l_content_rec_index := 0;
931 l_content_item_data := '';
932
933 IF (l_debug = 1) THEN
934 TRACE('** in PVT3.get_variable_dataa ** , start ');
935 END IF;
936
937 l_printer := p_label_type_info.default_printer;
938 --x_variable_content := '';
939 l_label_index := 1;
940 l_prev_format_id := -999;
941 l_prev_sub := '####';
942 trace('patch level is ' || l_patch_level || ' and businessflow code is ' || p_label_type_info.business_flow_code);
943 IF (l_patch_level = 1 AND (p_label_type_info.business_flow_code IN (1,2, 3, 4, 25))
944 ) THEN
945 l_count := l_rcv_lpn_table.COUNT;
946 ELSE
947 l_count := l_lpn_table.COUNT;
948 END IF;
949 trace('count is ' || l_count);
950
951 --
952 FOR i IN 1 .. l_count LOOP
953 trace('inside for loop ' || i || ' patch level is ' || l_patch_level);
954 IF ((l_patch_level = 1) AND (p_label_type_info.business_flow_code IN (1,2, 3, 4, 25))) THEN
955 IF l_rcv_lpn_table(i).lpn_id IS NOT NULL THEN
956 trace(' patch level is 1.. lpn id is ' || l_lpn_id);
957 l_lpn_id := l_rcv_lpn_table(i).lpn_id;
958 END IF;
959 ELSE
960 l_lpn_id := l_lpn_table(i);
961 trace(' patch level is 0.. lpn id is ' || l_lpn_id);
962 END IF;
963
964 IF (l_debug = 1) THEN
965 TRACE(' ^^^^ Getting New label for LPN_ID: '|| l_lpn_id);
966 END IF;
967
968 l_content_item_data := '';
969
970 /* Post Patchset J, subinventory code, locator_id, purchase_order are also present in
971 * the l_rcv_lpn_table
972 */
973 IF (l_patch_level = 1) AND (p_label_type_info.business_flow_code IN (1, 2,3, 4, 25)) THEN
974 IF l_rcv_lpn_table(i).lpn_id IS NOT NULL THEN
975 trace(' patch level is 1..');
976 l_subinventory_code := l_rcv_lpn_table(i).subinventory_code;
977 l_locator_id := l_rcv_lpn_table(i).locator_id;
978 l_purchase_order := l_rcv_lpn_table(i).purchase_order;
979 END IF;
980 END IF;
981
982 FOR v_lpn_content IN c_lpn(l_lpn_id) LOOP
983 l_content_rec_index := l_content_rec_index + 1;
984 -- Bug 4238729, 10+ CU2 bug
985 -- Moved set label status and message here.
986 l_label_status := INV_LABEL.G_SUCCESS;
987 l_label_err_msg := NULL;
988
989 IF (l_debug = 1) THEN
990 TRACE(' In Loop '|| l_content_rec_index);
991 END IF;
992
993
994 IF (l_debug = 1) THEN
995 TRACE(' Calling Rules Engine ');
996
997 TRACE( 'manual_format_id='
998 || p_label_type_info.manual_format_id
999 || ',manual_format_name='
1000 || p_label_type_info.manual_format_name
1001 );
1002 END IF;
1003
1004
1005 --In R12 moved this Rules engine call before the call to get printer
1006 /* insert a record into wms_label_requests entity to
1007 call the label rules engine to get appropriate label
1008 In this call if this happens to be for the label-set, the record
1009 from wms_label_request will be deleted inside following API*/
1010
1011 inv_label.get_format_with_rule
1012 (
1013 p_document_id => p_label_type_info.label_type_id
1014 , p_label_format_id => p_label_type_info.manual_format_id
1015 , p_organization_id => v_lpn_content.organization_id
1016 , p_inventory_item_id => v_lpn_content.inventory_item_id
1017 , p_subinventory_code => v_lpn_content.subinventory_code
1018 , p_locator_id => v_lpn_content.locator_id
1019 , p_lpn_id => v_lpn_content.lpn_id
1020 , p_lot_number => v_lpn_content.lot_number
1021 , p_revision => v_lpn_content.revision
1022 , p_serial_number => v_lpn_content.serial_number
1023 , p_business_flow_code => p_label_type_info.business_flow_code
1024 , p_last_update_date => SYSDATE
1025 , p_last_updated_by => fnd_global.user_id
1026 , p_creation_date => SYSDATE
1027 , p_created_by => fnd_global.user_id
1028 --, p_printer_name => l_printer --not used post R12
1029 , x_return_status => l_return_status
1030 , x_label_format_id => l_label_format_set_id
1031 , x_label_format => l_label_format
1032 , x_label_request_id => l_label_request_id
1033 );
1034
1035 IF l_return_status <> 'S' THEN
1036 fnd_message.set_name('WMS', 'WMS_LABL_RULE_ENGINE_FAILED');
1037 fnd_msg_pub.ADD;
1038 l_label_format_set_id := p_label_type_info.default_format_id;
1039 l_label_format := p_label_type_info.default_format_name;
1040 END IF;
1041
1042
1043 --for manual printer, l_label_format_set_id returned from above API
1044 --will be infact p_label_type_info.manual_format_id which can be a
1045 --label set or a label format
1046
1047
1048 --Added in R12 for Label sets with RFID
1049 --l_label_format_set_idreturned by the rules engine can be either a
1050 --label format OR a label set
1051 IF (l_debug = 1) THEN
1052 TRACE('^^^^^^^^^^^^^^^^Label-sets^^^^^^^^^^^^');
1053 TRACE(' looping though formats in set begins, format_id/set_id :'||l_label_format_set_id);
1054 END IF;
1055
1056
1057 -- this CURSOR c_label_formats_in_set() will give all formats in the
1058 -- SET or just the current format
1059
1060 FOR l_label_formats_in_set IN c_label_formats_in_set(l_label_format_set_id) LOOP
1061
1062 IF (l_debug = 1) THEN
1063 TRACE(' Format_id for Current set :'||l_label_formats_in_set.label_format_id);
1064 END IF;
1065
1066 --CODE logic
1067 -- If it is label-SET then
1068 ---- after getting all the formats inside a label SET calling the
1069 ----get_format_with_rule() is same. Just need to
1070 ----1 Insert record into WMS_LABEL_REQUESTS
1071 ----2 get value of l_label_format_id, l_label_format, l_label_request_id
1072 ----3 Do not call Rules Engine again, as we know format id
1073 --else
1074 ----Do not call get_format_with_rule(), just use the format-id
1075
1076 IF l_label_formats_in_set.label_entity_type = 1 THEN --IT IS LABEL SET
1077
1078 --In R12 call this API for the format AGAIN without calling Rules ENGINE
1079 /* insert a record into wms_label_requests entity */
1080
1081
1082 inv_label.get_format_with_rule
1083 (
1084 p_document_id => p_label_type_info.label_type_id
1085 , p_label_format_id => l_label_formats_in_set.label_format_id --considers manual printer also
1086 , p_organization_id => v_lpn_content.organization_id
1087 , p_inventory_item_id => v_lpn_content.inventory_item_id
1088 , p_subinventory_code => v_lpn_content.subinventory_code
1089 , p_locator_id => v_lpn_content.locator_id
1090 , p_lpn_id => v_lpn_content.lpn_id
1091 , p_lot_number => v_lpn_content.lot_number
1092 , p_revision => v_lpn_content.revision
1093 , p_serial_number => v_lpn_content.serial_number
1094 , p_business_flow_code => p_label_type_info.business_flow_code
1095 , p_last_update_date => SYSDATE
1096 , p_last_updated_by => fnd_global.user_id
1097 , p_creation_date => SYSDATE
1098 , p_created_by => fnd_global.user_id
1099 --, p_printer_name => l_printer --not used post R12
1100 , p_use_rule_engine => 'N' --------------------------Rules ENgine will NOT get called
1101 , x_return_status => l_return_status
1102 , x_label_format_id => l_label_format_id
1103 , x_label_format => l_label_format
1104 , x_label_request_id => l_label_request_id
1105 );
1106
1107 IF l_return_status <> 'S' THEN
1108 fnd_message.set_name('WMS', 'WMS_LABL_RULE_ENGINE_FAILED');
1109 fnd_msg_pub.ADD;
1110 l_label_format_id := p_label_type_info.default_format_id;
1111 l_label_format := p_label_type_info.default_format_name;
1112 END IF;
1113
1114 IF (l_debug = 1) THEN
1115 TRACE(
1116 'did apply label '
1117 || l_label_format
1118 || ','
1119 || l_label_format_id
1120 || ',req_id '
1121 || l_label_request_id
1122 );
1123 END IF;
1124
1125 ELSE --IT IS LABEL FORMAT
1126 --Just use the format-id returned
1127
1128 l_label_format_id := l_label_formats_in_set.label_format_id ;
1129
1130 END IF;
1131
1132
1133 IF (l_debug = 1) THEN
1134 TRACE(
1135 ' Getting printer label_format_id :'||l_label_format_id
1136 || ', manual_printer='
1137 || p_label_type_info.manual_printer
1138 || ',sub='
1139 || NVL(l_printer_sub, v_lpn_content.subinventory_code)
1140 || ',default printer='
1141 || p_label_type_info.default_printer
1142 );
1143 END IF;
1144
1145
1146 -- IF clause Added for Add format/printer for manual request
1147 IF p_label_type_info.manual_printer IS NULL THEN
1148 -- The p_label_type_info.manual_printer is the one passed from the manual page.
1149 -- As per the design, if a printer is passed from the manual page, then we use that printer irrespective.
1150
1151 IF (NVL(l_printer_sub, v_lpn_content.subinventory_code) IS NOT NULL)
1152 AND (NVL(l_printer_sub, v_lpn_content.subinventory_code) <>
1153 l_prev_sub
1154 ) THEN
1155 BEGIN
1156 wsh_report_printers_pvt.get_printer
1157 ( p_concurrent_program_id => p_label_type_info.label_type_id
1158 , p_user_id => fnd_global.user_id
1159 , p_responsibility_id => fnd_global.resp_id
1160 , p_application_id => fnd_global.resp_appl_id
1161 , p_organization_id => v_lpn_content.organization_id
1162 , p_zone => NVL(l_printer_sub,v_lpn_content.subinventory_code)
1163 , p_format_id => l_label_format_id --added in R12
1164 , x_printer => l_printer
1165 , x_api_status => l_api_status
1166 , x_error_message => l_error_message
1167 );
1168
1169 IF l_api_status <> 'S' THEN
1170 IF (l_debug = 1) THEN
1171 TRACE(
1172 'Error in calling get_printer, set printer as default printer, err_msg:'
1173 || l_error_message
1174 );
1175 END IF;
1176
1177 l_printer := p_label_type_info.default_printer;
1178 END IF;
1179 EXCEPTION
1180 WHEN OTHERS THEN
1181 l_printer := p_label_type_info.default_printer;
1182 END;
1183
1184 l_prev_sub := NVL(l_printer_sub, v_lpn_content.subinventory_code);
1185 END IF;
1186 ELSE
1187 IF (l_debug = 1) THEN
1188 TRACE(
1189 'Set printer as Manual Printer passed in:'
1190 || p_label_type_info.manual_printer
1191 );
1192 END IF;
1193
1194 l_printer := p_label_type_info.manual_printer;
1195 END IF;
1196
1197
1198 IF (l_label_format_id IS NOT NULL) THEN
1199 -- Derive the fields for the format either passed in or derived via the rules engine.
1200 IF l_label_format_id <> NVL(l_prev_format_id, -999) THEN
1201 IF (l_debug = 1) THEN
1202 TRACE(' Getting variables for new format '|| l_label_format);
1203 END IF;
1204
1205 /* Changed for R12 RFID project
1206 * while getting variables for format
1207 * Check whether EPC field is included in the format
1208 * If it is included, it will later query WMS_LABEL_FORMATS
1209 * table to get RFID related information
1210 * Otherwise, it does not need to do that
1211 */
1212
1213 inv_label.get_variables_for_format
1214 (
1215 x_variables => l_selected_fields
1216 , x_variables_count => l_selected_fields_count
1217 , x_is_variable_exist => l_is_epc_exist
1218 , p_format_id => l_label_format_id
1219 , p_exist_variable_name => 'EPC'
1220 );
1221 l_prev_format_id := l_label_format_id;
1222
1223 IF (l_selected_fields_count = 0)
1224 OR (l_selected_fields.COUNT = 0) THEN
1225 IF (l_debug = 1) THEN
1226 TRACE(
1227 'no fields defined for this format: '
1228 || l_label_format
1229 || ','
1230 || l_label_format_id
1231 );
1232 TRACE('######## GOING TO NEXT LABEL #######');
1233 END IF;
1234
1235 GOTO nextlabel;
1236 END IF;
1237
1238 IF (l_debug = 1) THEN
1239 TRACE(
1240 ' Found selected_fields for format '
1241 || l_label_format
1242 || ', num='
1243 || l_selected_fields_count
1244 );
1245 END IF;
1246 END IF;
1247 ELSE
1248 IF (l_debug = 1) THEN
1249 TRACE('No format exists for this label, goto nextlabel');
1250 END IF;
1251
1252 GOTO nextlabel;
1253 END IF;
1254
1255 -- Added for 11.5.10+ RFID compliance project
1256 -- Get RFID/EPC related information for a format
1257 -- Only do this if EPC is a field included in the format
1258 IF l_is_epc_exist = 'Y' THEN
1259 IF (l_debug =1) THEN
1260 trace('EPC is a field included in the format, getting RFID/EPC related information from format');
1261 END IF;
1262 l_epc := null;
1263 BEGIN
1264
1265 -- Added for 11.5.10+ RFID Compliance project
1266 -- New field : EPC
1267 -- When generate_epc API returns E (expected error) or U(expected error),
1268 -- it sets the error message, but generate xml with EPC as null
1269
1270 -- R12 changed the call-- changed spec WMS_EPC_PVT.generate_epc()
1271
1272 WMS_EPC_PVT.generate_epc
1273 (p_org_id => v_lpn_content.organization_id,
1274 p_label_type_id => p_label_type_info.label_type_id, -- 3
1275 p_group_id => inv_label.epc_group_id,
1276 p_label_format_id => l_label_format_id,
1277 p_label_request_id => l_label_request_id,
1278 p_business_flow_code => p_label_type_info.business_flow_code,
1279 x_epc => l_epc,
1280 x_return_status => l_epc_ret_status, -- S / E / U
1281 x_return_mesg => l_epc_ret_msg
1282 );
1283
1284 IF (l_debug = 1) THEN
1285 trace('Called generate_epc with ');
1286 trace('p_lpn_id='||v_lpn_content.lpn_id||',p_group_id='||inv_label.epc_group_id);
1287 trace('l_label_format_id='||l_label_format_id||',p_user_id='||fnd_global.user_id);
1288 trace('p_org_id='||v_lpn_content.organization_id);
1289 trace('l_label_request_id= '||l_label_request_id);
1290 trace('x_epc='||l_epc);
1291 trace('x_return_status='||l_epc_ret_status);
1292 trace('x_return_mesg=' ||l_epc_ret_msg);
1293 END IF;
1294
1295 IF l_epc_ret_status = 'S' THEN
1296 -- Success
1297 IF (l_debug = 1) THEN
1298 trace('Succesfully generated EPC '||l_epc);
1299 END IF;
1300 ELSIF l_epc_ret_status = 'U' THEN
1301 -- Unexpected error
1302 l_epc := null;
1303 IF(l_debug = 1) THEN
1304 trace('Got unexpected error from generate_epc, msg='||l_epc_ret_msg);
1305 trace('Set label status as Error and l_epc = null');
1306 END IF;
1307
1308 ELSIF l_epc_ret_status = 'E' THEN
1309 -- Expected error
1310 l_epc := null;
1311 IF(l_debug = 1) THEN
1312 trace('Got expected error from generate_epc, msg='||l_epc_ret_msg);
1313 trace('Set label status as Warning and l_epc = null');
1314 END IF;
1315 ELSE
1316 trace('generate_epc returned a status that is not recognized, set epc as null');
1317 l_epc := null;
1318 END IF;
1319 -- End Bug
1320
1321 EXCEPTION
1322 WHEN no_data_found THEN
1323 IF(l_debug =1 ) THEN
1324 trace('No format found when retrieving EPC information. Format_id='||l_label_format_id);
1325 END IF;
1326 WHEN others THEN
1327 IF(l_debug =1 ) THEN
1328 trace('Other error when retrieving EPC information. Format_id='||l_label_format_id);
1329 END IF;
1330 END;
1331 ELSE
1332 IF (l_debug =1) THEN
1333 trace('EPC is not a field included in the format');
1334 END IF;
1335 END IF;
1336
1337
1338
1339
1340 /* variable header */
1341
1342 l_content_item_data := l_content_item_data || label_b;
1343
1344 IF l_label_format <> NVL(p_label_type_info.default_format_name, '@@@') THEN
1345 l_content_item_data := l_content_item_data
1346 || ' _FORMAT="'
1347 || l_label_format
1348 || '"';
1349 END IF;
1350
1351 IF (l_printer IS NOT NULL)
1352 AND (l_printer <> NVL(p_label_type_info.default_printer, '###')) THEN
1353 l_content_item_data :=
1354 l_content_item_data || ' _PRINTERNAME="' || l_printer || '"';
1355 END IF;
1356
1357 l_content_item_data := l_content_item_data || tag_e;
1358
1359 IF (l_debug = 1) THEN
1360 TRACE('Starting assign variables, ');
1361 END IF;
1362
1363 l_column_name_list := 'Set variables for ';
1364
1365 -- Fix for bug: 4179593 Start
1366 l_custom_sql_ret_status := FND_API.G_RET_STS_SUCCESS;
1367 l_CustSqlWarnFlagSet := FALSE;
1368 l_CustSqlErrFlagSet := FALSE;
1369 l_CustSqlWarnMsg := NULL;
1370 l_CustSqlErrMsg := NULL;
1371 -- Fix for bug: 4179593 End
1372
1373 /* Loop for each selected fields, find the columns and write into the XML_content*/
1374 FOR i IN 1 .. l_selected_fields.COUNT LOOP
1375 IF (l_debug = 1) THEN
1376 l_column_name_list :=
1377 l_column_name_list || ',' || l_selected_fields(i).column_name;
1378 END IF;
1379
1380 ---------------------------------------------------------------------------------------------
1381 -- Project: 'Custom Labels' (A 11i10+ Project) |
1382 -- Author: Dinesh ([email protected]) |
1383 -- Change Description: |
1384 -- The check (SQL_STMT <> NULL and COLUMN_NAME = NULL) implies that the field is a |
1385 -- Custom SQL based field. Handle it appropriately. |
1386 ---------------------------------------------------------------------------------------------
1387 IF (l_selected_fields(i).SQL_STMT IS NOT NULL AND l_selected_fields(i).column_name = 'sql_stmt') THEN
1388 IF (l_debug = 1) THEN
1389 trace('Custom Labels Trace [INVLAP3B.pls]: ------------------------- REPORT BEGIN-------------------------------------');
1390 trace('Custom Labels Trace [INVLAP3B.pls]: LABEL_FIELD_ID : ' || l_selected_fields(i).label_field_id);
1391 trace('Custom Labels Trace [INVLAP3B.pls]: FIELD_VARIABLE_NAME : ' || l_selected_fields(i).variable_name);
1392 trace('Custom Labels Trace [INVLAP3B.pls]: COLUMN_NAME : ' || l_selected_fields(i).column_name);
1393 trace('Custom Labels Trace [INVLAP3B.pls]: SQL_STMT : ' || l_selected_fields(i).sql_stmt);
1394 END IF;
1395 l_sql_stmt := l_selected_fields(i).sql_stmt;
1396 IF (l_debug = 1) THEN
1397 trace('Custom Labels Trace [INVLAP3B.pls]: l_sql_stmt BEFORE REQUEST_ID Filter Concatenation: ' || l_sql_stmt);
1398 END IF;
1399 l_sql_stmt := l_sql_stmt || ' AND WLR.LABEL_REQUEST_ID = :REQUEST_ID';
1400 IF (l_debug = 1) THEN
1401 trace('Custom Labels Trace [INVLAP3B.pls]: l_sql_stmt AFTER REQUEST_ID Filter Concatenation: ' || l_sql_stmt);
1402
1403 END IF;
1404 BEGIN
1405 IF (l_debug = 1) THEN
1406 trace('Custom Labels Trace [INVLAP3B.pls]: At Breadcrumb 1');
1407 trace('Custom Labels Trace [INVLAP3B.pls]: LABEL_REQUEST_ID : ' || l_label_request_id);
1408 END IF;
1409 OPEN c_sql_stmt FOR l_sql_stmt using l_label_request_id;
1410 LOOP
1411 FETCH c_sql_stmt INTO l_sql_stmt_result;
1412 EXIT WHEN c_sql_stmt%notfound OR c_sql_stmt%rowcount >=2;
1413 END LOOP;
1414
1415 IF (c_sql_stmt%rowcount=1 AND l_sql_stmt_result IS NULL) THEN
1416 x_return_status := FND_API.G_RET_STS_SUCCESS;
1417 l_custom_sql_ret_status := INV_LABEL.G_WARNING;
1418 fnd_message.set_name('WMS','WMS_CS_NULL_VALUE_RETURNED');
1419 fnd_msg_pub.ADD;
1420 -- Fix for bug: 4179593 Start
1421 --fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false, p_count => x_msg_count, p_data => l_custom_sql_ret_msg);
1422 l_custom_sql_ret_msg := fnd_msg_pub.get(p_msg_index => fnd_msg_pub.g_last, p_encoded => fnd_api.g_false);
1423 l_CustSqlWarnMsg := l_custom_sql_ret_msg;
1424 l_CustSqlWarnFlagSet := TRUE;
1425 -- Fix for bug: 4179593 End
1426 IF (l_debug = 1) THEN
1427 trace('Custom Labels Trace [INVLAP3B.pls]: At Breadcrumb 2');
1428 trace('Custom Labels Trace [INVLAP3B.pls]: l_sql_stmt_result is: ' || l_sql_stmt_result);
1429 trace('Custom Labels Trace [INVLAP3B.pls]: WARNING: NULL value returned.');
1430 trace('Custom Labels Trace [INVLAP3B.pls]: l_custom_sql_ret_status is set to : ' || l_custom_sql_ret_status);
1431 END IF;
1432 ELSIF c_sql_stmt%rowcount=0 THEN
1433 IF (l_debug = 1) THEN
1434 trace('Custom Labels Trace [INVLAP3B.pls]: At Breadcrumb 3');
1435 trace('Custom Labels Trace [INVLAP3B.pls]: WARNING: No row returned by the Custom SQL query');
1436 END IF;
1437 x_return_status := FND_API.G_RET_STS_SUCCESS;
1438 l_custom_sql_ret_status := INV_LABEL.G_WARNING;
1439 fnd_message.set_name('WMS','WMS_CS_NO_DATA_FOUND');
1440 fnd_msg_pub.ADD;
1441 -- Fix for bug: 4179593 Start
1442 --fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false, p_count => x_msg_count, p_data => l_custom_sql_ret_msg);
1443 l_custom_sql_ret_msg := fnd_msg_pub.get(p_msg_index => fnd_msg_pub.g_last, p_encoded => fnd_api.g_false);
1444 l_CustSqlWarnMsg := l_custom_sql_ret_msg;
1445 l_CustSqlWarnFlagSet := TRUE;
1446 -- Fix for bug: 4179593 End
1447 ELSIF c_sql_stmt%rowcount>=2 THEN
1448 IF (l_debug = 1) THEN
1449 trace('Custom Labels Trace [INVLAP3B.pls]: At Breadcrumb 4');
1450 trace('Custom Labels Trace [INVLAP3B.pls]: ERROR: Multiple values returned by the Custom SQL query');
1451 END IF;
1452 l_sql_stmt_result := NULL;
1453 x_return_status := FND_API.G_RET_STS_SUCCESS;
1454 l_custom_sql_ret_status := FND_API.G_RET_STS_ERROR;
1455 fnd_message.set_name('WMS','WMS_CS_MULTIPLE_VALUES_RETURN');
1456 fnd_msg_pub.ADD;
1457 -- Fix for bug: 4179593 Start
1458 --fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false, p_count => x_msg_count, p_data => l_custom_sql_ret_msg);
1459 l_custom_sql_ret_msg := fnd_msg_pub.get(p_msg_index => fnd_msg_pub.g_last, p_encoded => fnd_api.g_false);
1460 l_CustSqlErrMsg := l_custom_sql_ret_msg;
1461 l_CustSqlErrFlagSet := TRUE;
1462 -- Fix for bug: 4179593 End
1463 END IF;
1464 IF (c_sql_stmt%ISOPEN) THEN
1465 CLOSE c_sql_stmt;
1466 END IF;
1467 EXCEPTION
1468 WHEN OTHERS THEN
1469 IF (c_sql_stmt%ISOPEN) THEN
1470 CLOSE c_sql_stmt;
1471 END IF;
1472 IF (l_debug = 1) THEN
1473 trace('Custom Labels Trace [INVLAP3B.pls]: At Breadcrumb 5');
1474 trace('Custom Labels Trace [INVLAP3B.pls]: Unexpected Error has occured in GET_VARIABLES_DATA');
1475 END IF;
1476 x_return_status := FND_API.G_RET_STS_ERROR;
1477 fnd_message.set_name('WMS','WMS_CS_WRONG_SQL_CONSTRUCT');
1478 fnd_msg_pub.ADD;
1479 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false, p_count => x_msg_count, p_data => x_msg_data);
1480 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1481 END;
1482 IF (l_debug = 1) THEN
1483 trace('Custom Labels Trace [INVLAP3B.pls]: At Breadcrumb 6');
1484 trace('Custom Labels Trace [INVLAP3B.pls]: Before assigning it to l_content_item_data');
1485 END IF;
1486 l_content_item_data := l_content_item_data
1487 || variable_b
1488 || l_selected_fields(i).variable_name
1489 || '">'
1490 || l_sql_stmt_result
1491 || variable_e;
1492 l_sql_stmt_result := NULL;
1493 l_sql_stmt := NULL;
1494 IF (l_debug = 1) THEN
1495 trace('Custom Labels Trace [INVLAP3B.pls]: At Breadcrumb 7');
1496 trace('Custom Labels Trace [INVLAP3B.pls]: After assigning it to l_content_item_data');
1497 trace('Custom Labels Trace [INVLAP3B.pls]: --------------------------REPORT END-------------------------------------');
1498 END IF;
1499 ------------------------End of this changes for Custom Labels project code--------------------
1500 ELSIF LOWER(l_selected_fields(i).column_name) = 'current_date' THEN
1501 l_content_item_data := l_content_item_data
1502 || variable_b
1503 || l_selected_fields(i).variable_name
1504 || '">'
1505 || inv_label.g_date
1506 || variable_e;
1507 ELSIF LOWER(l_selected_fields(i).column_name) = 'current_time' THEN
1508 l_content_item_data := l_content_item_data
1509 || variable_b
1510 || l_selected_fields(i).variable_name
1511 || '">'
1512 || inv_label.g_time
1513 || variable_e;
1514 ELSIF LOWER(l_selected_fields(i).column_name) = 'request_user' THEN
1515 l_content_item_data := l_content_item_data
1516 || variable_b
1517 || l_selected_fields(i).variable_name
1518 || '">'
1519 || inv_label.g_user
1520 || variable_e;
1521 ELSIF LOWER(l_selected_fields(i).column_name) = 'lpn' THEN
1522 l_content_item_data := l_content_item_data
1523 || variable_b
1524 || l_selected_fields(i).variable_name
1525 || '">'
1526 || v_lpn_content.lpn
1527 || variable_e;
1528 ELSIF LOWER(l_selected_fields(i).column_name) = 'organization' THEN
1529 l_content_item_data := l_content_item_data
1530 || variable_b
1531 || l_selected_fields(i).variable_name
1532 || '">'
1533 || v_lpn_content.ORGANIZATION
1534 || variable_e;
1535 ELSIF LOWER(l_selected_fields(i).column_name) = 'subinventory_code' THEN
1536 l_content_item_data := l_content_item_data
1537 || variable_b
1538 || l_selected_fields(i).variable_name
1539 || '">'
1540 || v_lpn_content.subinventory_code
1541 || variable_e;
1542 ELSIF LOWER(l_selected_fields(i).column_name) = 'locator' THEN
1543 l_content_item_data := l_content_item_data
1544 || variable_b
1545 || l_selected_fields(i).variable_name
1546 || '">'
1547 || v_lpn_content.LOCATOR
1548 || variable_e;
1549 ELSIF LOWER(l_selected_fields(i).column_name) = 'volume' THEN
1550 l_content_item_data := l_content_item_data
1551 || variable_b
1552 || l_selected_fields(i).variable_name
1553 || '">'
1554 || v_lpn_content.volume
1555 || variable_e;
1556 ELSIF LOWER(l_selected_fields(i).column_name) = 'volume_uom' THEN
1557 l_content_item_data := l_content_item_data
1558 || variable_b
1559 || l_selected_fields(i).variable_name
1560 || '">'
1561 || v_lpn_content.volume_uom
1562 || variable_e;
1563 ELSIF LOWER(l_selected_fields(i).column_name) = 'gross_weight' THEN
1564 l_content_item_data := l_content_item_data
1565 || variable_b
1566 || l_selected_fields(i).variable_name
1567 || '">'
1568 || v_lpn_content.gross_weight
1569 || variable_e;
1570 ELSIF LOWER(l_selected_fields(i).column_name) = 'gross_weight_uom' THEN
1571 l_content_item_data := l_content_item_data
1572 || variable_b
1573 || l_selected_fields(i).variable_name
1574 || '">'
1575 || v_lpn_content.gross_weight_uom
1576 || variable_e;
1577 ELSIF LOWER(l_selected_fields(i).column_name) = 'tare_weight' THEN
1578 l_content_item_data := l_content_item_data
1579 || variable_b
1580 || l_selected_fields(i).variable_name
1581 || '">'
1582 || v_lpn_content.tare_weight
1583 || variable_e;
1584 ELSIF LOWER(l_selected_fields(i).column_name) = 'tare_weight_uom' THEN
1585 l_content_item_data := l_content_item_data
1586 || variable_b
1587 || l_selected_fields(i).variable_name
1588 || '">'
1589 || v_lpn_content.tare_weight_uom
1590 || variable_e;
1591 ELSIF LOWER(l_selected_fields(i).column_name) = 'lpn_container_item' THEN
1592 l_content_item_data := l_content_item_data
1593 || variable_b
1594 || l_selected_fields(i).variable_name
1595 || '">'
1596 || v_lpn_content.lpn_container_item
1597 || variable_e;
1598 ELSIF LOWER(l_selected_fields(i).column_name) = 'parent_lpn' THEN
1599 l_content_item_data := l_content_item_data
1600 || variable_b
1601 || l_selected_fields(i).variable_name
1602 || '">'
1603 || v_lpn_content.parent_lpn
1604 || variable_e;
1605 ELSIF LOWER(l_selected_fields(i).column_name) = 'outermost_lpn' THEN
1606 l_content_item_data := l_content_item_data
1607 || variable_b
1608 || l_selected_fields(i).variable_name
1609 || '">'
1610 || v_lpn_content.outermost_lpn
1611 || variable_e;
1612 ELSIF LOWER(l_selected_fields(i).column_name) =
1613 'customer_purchase_order' THEN
1614 l_content_item_data := l_content_item_data
1615 || variable_b
1616 || l_selected_fields(i).variable_name
1617 || '">'
1618 || l_purchase_order
1619 || variable_e;
1620 ELSIF LOWER(l_selected_fields(i).column_name) = 'category' THEN
1621 l_content_item_data := l_content_item_data
1622 || variable_b
1623 || l_selected_fields(i).variable_name
1624 || '">'
1625 || v_lpn_content.CATEGORY
1626 || variable_e;
1627 ELSIF LOWER(l_selected_fields(i).column_name) = 'attribute1' THEN
1628 l_content_item_data := l_content_item_data
1629 || variable_b
1630 || l_selected_fields(i).variable_name
1631 || '">'
1632 || v_lpn_content.attribute1
1633 || variable_e;
1634 ELSIF LOWER(l_selected_fields(i).column_name) = 'attribute2' THEN
1635 l_content_item_data := l_content_item_data
1636 || variable_b
1637 || l_selected_fields(i).variable_name
1638 || '">'
1639 || v_lpn_content.attribute2
1640 || variable_e;
1641 ELSIF LOWER(l_selected_fields(i).column_name) = 'attribute3' THEN
1642 l_content_item_data := l_content_item_data
1643 || variable_b
1644 || l_selected_fields(i).variable_name
1645 || '">'
1646 || v_lpn_content.attribute3
1647 || variable_e;
1648 ELSIF LOWER(l_selected_fields(i).column_name) = 'attribute4' THEN
1649 l_content_item_data := l_content_item_data
1650 || variable_b
1651 || l_selected_fields(i).variable_name
1652 || '">'
1653 || v_lpn_content.attribute4
1654 || variable_e;
1655 ELSIF LOWER(l_selected_fields(i).column_name) = 'attribute5' THEN
1656 l_content_item_data := l_content_item_data
1657 || variable_b
1658 || l_selected_fields(i).variable_name
1659 || '">'
1660 || v_lpn_content.attribute5
1661 || variable_e;
1662 ELSIF LOWER(l_selected_fields(i).column_name) = 'attribute6' THEN
1663 l_content_item_data := l_content_item_data
1664 || variable_b
1665 || l_selected_fields(i).variable_name
1666 || '">'
1667 || v_lpn_content.attribute6
1668 || variable_e;
1669 ELSIF LOWER(l_selected_fields(i).column_name) = 'attribute7' THEN
1670 l_content_item_data := l_content_item_data
1671 || variable_b
1672 || l_selected_fields(i).variable_name
1673 || '">'
1674 || v_lpn_content.attribute7
1675 || variable_e;
1676 ELSIF LOWER(l_selected_fields(i).column_name) = 'attribute8' THEN
1677 l_content_item_data := l_content_item_data
1678 || variable_b
1679 || l_selected_fields(i).variable_name
1680 || '">'
1681 || v_lpn_content.attribute8
1682 || variable_e;
1683 ELSIF LOWER(l_selected_fields(i).column_name) = 'attribute9' THEN
1684 l_content_item_data := l_content_item_data
1685 || variable_b
1686 || l_selected_fields(i).variable_name
1687 || '">'
1688 || v_lpn_content.attribute9
1689 || variable_e;
1690 ELSIF LOWER(l_selected_fields(i).column_name) = 'attribute10' THEN
1691 l_content_item_data := l_content_item_data
1692 || variable_b
1693 || l_selected_fields(i).variable_name
1694 || '">'
1695 || v_lpn_content.attribute10
1696 || variable_e;
1697 ELSIF LOWER(l_selected_fields(i).column_name) = 'attribute11' THEN
1698 l_content_item_data := l_content_item_data
1699 || variable_b
1700 || l_selected_fields(i).variable_name
1701 || '">'
1702 || v_lpn_content.attribute11
1703 || variable_e;
1704 ELSIF LOWER(l_selected_fields(i).column_name) = 'attribute12' THEN
1705 l_content_item_data := l_content_item_data
1706 || variable_b
1707 || l_selected_fields(i).variable_name
1708 || '">'
1709 || v_lpn_content.attribute12
1710 || variable_e;
1711 ELSIF LOWER(l_selected_fields(i).column_name) = 'attribute13' THEN
1712 l_content_item_data := l_content_item_data
1713 || variable_b
1714 || l_selected_fields(i).variable_name
1715 || '">'
1716 || v_lpn_content.attribute13
1717 || variable_e;
1718 ELSIF LOWER(l_selected_fields(i).column_name) = 'attribute14' THEN
1719 l_content_item_data := l_content_item_data
1720 || variable_b
1721 || l_selected_fields(i).variable_name
1722 || '">'
1723 || v_lpn_content.attribute14
1724 || variable_e;
1725 ELSIF LOWER(l_selected_fields(i).column_name) = 'attribute15' THEN
1726 l_content_item_data := l_content_item_data
1727 || variable_b
1728 || l_selected_fields(i).variable_name
1729 || '">'
1730 || v_lpn_content.attribute15
1731 || variable_e;
1732 --Bug 4891916. Added for the field Requestor
1733 ELSIF LOWER(l_selected_fields(i).column_name) = 'requestor' THEN
1734 l_content_item_data := l_content_item_data
1735 || variable_b
1736 || l_selected_fields(i).variable_name
1737 || '">' || l_requestor
1738 || variable_e;
1739 --Bug 4891916. Added for the field Cycle Count Name
1740 ELSIF LOWER(l_selected_fields(i).column_name) = 'cycle_count_name' THEN
1741 l_content_item_data := l_content_item_data
1742 || variable_b
1743 || l_selected_fields(i).variable_name
1744 || '">' || l_cycle_count_name
1745 || variable_e;
1746 --End of fix for Bug 4891916
1747
1748 -- Patchset J
1749 -- iSP printing
1750 ELSIF LOWER(l_selected_fields(i).column_name) = 'asn_number' THEN
1751 l_content_item_data := l_content_item_data
1752 || variable_b
1753 || l_selected_fields(i).variable_name
1754 || '">'
1755 || l_rcv_isp_header.asn_num
1756 || variable_e;
1757 ELSIF LOWER(l_selected_fields(i).column_name) = 'expct_rcpt_date' THEN
1758 l_content_item_data := l_content_item_data
1759 || variable_b
1760 || l_selected_fields(i).variable_name
1761 || '">'
1762 || l_rcv_isp_header.expected_receipt_date
1763 || variable_e;
1764 ELSIF LOWER(l_selected_fields(i).column_name) = 'freight_terms' THEN
1765 l_content_item_data := l_content_item_data
1766 || variable_b
1767 || l_selected_fields(i).variable_name
1768 || '">'
1769 || l_rcv_isp_header.freight_terms
1770 || variable_e;
1771 ELSIF LOWER(l_selected_fields(i).column_name) = 'freight_carrier' THEN
1772 l_content_item_data := l_content_item_data
1773 || variable_b
1774 || l_selected_fields(i).variable_name
1775 || '">'
1776 || l_rcv_isp_header.freight_carrier
1777 || variable_e;
1778 ELSIF LOWER(l_selected_fields(i).column_name) = 'num_of_containers' THEN
1779 l_content_item_data := l_content_item_data
1780 || variable_b
1781 || l_selected_fields(i).variable_name
1782 || '">'
1783 || l_rcv_isp_header.num_of_containers
1784 || variable_e;
1785 ELSIF LOWER(l_selected_fields(i).column_name) = 'bill_of_lading' THEN
1786 l_content_item_data := l_content_item_data
1787 || variable_b
1788 || l_selected_fields(i).variable_name
1789 || '">'
1790 || l_rcv_isp_header.bill_of_lading
1791 || variable_e;
1792 ELSIF LOWER(l_selected_fields(i).column_name) = 'waybill_airbill_num' THEN
1793 l_content_item_data := l_content_item_data
1794 || variable_b
1795 || l_selected_fields(i).variable_name
1796 || '">'
1797 || l_rcv_isp_header.waybill_airbill_num
1798 || variable_e;
1799 ELSIF LOWER(l_selected_fields(i).column_name) = 'comments_header' THEN
1800 l_content_item_data := l_content_item_data
1801 || variable_b
1802 || l_selected_fields(i).variable_name
1803 || '">'
1804 || l_rcv_isp_header.comments
1805 || variable_e;
1806 ELSIF LOWER(l_selected_fields(i).column_name) = 'packaging_code' THEN
1807 l_content_item_data := l_content_item_data
1808 || variable_b
1809 || l_selected_fields(i).variable_name
1810 || '">'
1811 || l_rcv_isp_header.packaging_code
1812 || variable_e;
1813 ELSIF LOWER(l_selected_fields(i).column_name) = 'special_handling_code' THEN
1814 l_content_item_data := l_content_item_data
1815 || variable_b
1816 || l_selected_fields(i).variable_name
1817 || '">'
1818 || l_rcv_isp_header.special_handling_code
1819 || variable_e;
1820 ELSIF LOWER(l_selected_fields(i).column_name) = 'shipment_date' THEN
1821 l_content_item_data := l_content_item_data
1822 || variable_b
1823 || l_selected_fields(i).variable_name
1824 || '">'
1825 || l_rcv_isp_header.shipment_date
1826 || variable_e;
1827 ELSIF LOWER(l_selected_fields(i).column_name) = 'packing_slip_header' THEN
1828 l_content_item_data := l_content_item_data
1829 || variable_b
1830 || l_selected_fields(i).variable_name
1831 || '">'
1832 || l_rcv_isp_header.packing_slip
1833 || variable_e;
1834 -- Added for 11.5.10+ RFID Compliance project
1835 -- New field : EPC
1836 -- EPC is generated once for each LPN
1837 ELSIF LOWER(l_selected_fields(i).column_name) = 'epc' THEN
1838 l_content_item_data := l_content_item_data
1839 || variable_b
1840 || l_selected_fields(i).variable_name
1841 || '">'
1842 || l_epc
1843 || variable_e;
1844 l_label_err_msg := l_epc_ret_msg;
1845 IF l_epc_ret_status = 'U' THEN
1846 l_label_status := INV_LABEL.G_ERROR;
1847 ELSIF l_epc_ret_status = 'E' THEN
1848 l_label_status := INV_LABEL.G_WARNING;
1849 END IF;
1850
1851 END IF;
1852 END LOOP;
1853
1854 l_content_item_data := l_content_item_data || label_e;
1855 x_variable_content(l_label_index).label_content := l_content_item_data;
1856 x_variable_content(l_label_index).label_request_id := l_label_request_id;
1857 x_variable_content(l_label_index).label_status := l_label_status;
1858 x_variable_content(l_label_index).error_message := l_label_err_msg;
1859
1860 ------------------------Start of changes for Custom Labels project code------------------
1861
1862 -- Fix for bug: 4179593 Start
1863 IF (l_CustSqlWarnFlagSet) THEN
1864 l_custom_sql_ret_status := INV_LABEL.G_WARNING;
1865 l_custom_sql_ret_msg := l_CustSqlWarnMsg;
1866 END IF;
1867
1868 IF (l_CustSqlErrFlagSet) THEN
1869 l_custom_sql_ret_status := FND_API.G_RET_STS_ERROR;
1870 l_custom_sql_ret_msg := l_CustSqlErrMsg;
1871 END IF;
1872 -- Fix for bug: 4179593 End
1873
1874 -- We will concatenate the error message from Custom SQL and EPC code.
1875 x_variable_content(l_label_index).error_message := l_custom_sql_ret_msg || ' ' || l_label_err_msg;
1876 IF(l_CustSqlWarnFlagSet OR l_CustSqlErrFlagSet) THEN
1877 x_variable_content(l_label_index).label_status := l_custom_sql_ret_status;
1878 END IF;
1879
1880 ------------------------End of this changes for Custom Labels project code---------------
1881 l_label_index := l_label_index + 1;
1882
1883
1884 ------------------------Starts R12 label-set project------------------
1885 l_content_item_data := '';
1886 l_label_request_id := NULL;
1887 l_custom_sql_ret_status := NULL;
1888 l_custom_sql_ret_msg := NULL;
1889 ------------------------Ends R12 label-set project---------------
1890
1891 IF (l_debug = 1) THEN
1892 TRACE(' Done with Label formats in the current label-set');
1893 END IF;
1894
1895
1896 END LOOP; --for formats in label-set
1897
1898 <<nextlabel>>
1899
1900 l_content_item_data := '';
1901 l_label_request_id := NULL;
1902 ------------------------Start of changes for Custom Labels project code------------------
1903 l_custom_sql_ret_status := NULL;
1904 l_custom_sql_ret_msg := NULL;
1905 ------------------------End of this changes for Custom Labels project code---------------
1906
1907 IF (l_debug = 1) THEN
1908 TRACE(l_column_name_list);
1909 TRACE(' Finished writing item variables ');
1910 END IF;
1911 END LOOP;
1912 --x_variable_content := x_variable_content || l_content_item_data ;
1913 END LOOP;
1914 END get_variable_data;
1915
1916 PROCEDURE get_variable_data(
1917 x_variable_content OUT NOCOPY LONG
1918 , x_msg_count OUT NOCOPY NUMBER
1919 , x_msg_data OUT NOCOPY VARCHAR2
1920 , x_return_status OUT NOCOPY VARCHAR2
1921 , p_label_type_info IN inv_label.label_type_rec
1922 , p_transaction_id IN NUMBER
1923 , p_input_param IN mtl_material_transactions_temp%ROWTYPE
1924 , p_transaction_identifier IN NUMBER
1925 ) IS
1926 l_variable_data_tbl inv_label.label_tbl_type;
1927 BEGIN
1928 get_variable_data(
1929 x_variable_content => l_variable_data_tbl
1930 , x_msg_count => x_msg_count
1931 , x_msg_data => x_msg_data
1932 , x_return_status => x_return_status
1933 , p_label_type_info => p_label_type_info
1934 , p_transaction_id => p_transaction_id
1935 , p_input_param => p_input_param
1936 , p_transaction_identifier => p_transaction_identifier
1937 );
1938 x_variable_content := '';
1939
1940 FOR i IN 1 .. l_variable_data_tbl.COUNT() LOOP
1941 x_variable_content :=
1942 x_variable_content || l_variable_data_tbl(i).label_content;
1943 END LOOP;
1944 END get_variable_data;
1945 END inv_label_pvt3;