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