[Home] [Help]
PACKAGE BODY: APPS.INV_LABEL_PVT1
Source
1 PACKAGE BODY inv_label_pvt1 AS
2 /* $Header: INVLAP1B.pls 120.31.12010000.5 2008/11/22 10:57:31 hjogleka 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(
8 10
9 );
10 tag_e CONSTANT VARCHAR2(50) := '>' || fnd_global.local_chr(10);
11 l_debug NUMBER;
12 -- Bug 2795525 : This mask is used to mask all date fields.
13 g_date_format_mask VARCHAR2(100) := inv_label.g_date_format_mask;
14 g_header_printed BOOLEAN := FALSE;
15 g_user_name fnd_user.user_name%TYPE := fnd_global.user_name;
16
17 PROCEDURE trace(p_message IN VARCHAR2) IS
18 BEGIN
19 IF (g_header_printed = FALSE) THEN
20 inv_label.trace('$Header: INVLAP1B.pls 120.31.12010000.5 2008/11/22 10:57:31 hjogleka ship $', 'LABEL_MATRL');
21 g_header_printed := TRUE;
22 END IF;
23
24 inv_label.trace(g_user_name || ': ' || p_message, 'LABEL_MATRL');
25 END trace;
26
27 FUNCTION get_uom_code(
28 p_organization_id IN NUMBER
29 , p_inventory_item_id IN NUMBER
30 , p_unit_of_measure IN VARCHAR2
31 )
32 RETURN VARCHAR2 IS
33 l_uom_code VARCHAR2(3) := '';
34 BEGIN
35 SELECT uom_code
36 INTO l_uom_code
37 FROM mtl_item_uoms_view
38 WHERE organization_id = p_organization_id
39 AND inventory_item_id = p_inventory_item_id
40 AND unit_of_measure = p_unit_of_measure;
41
42 IF SQL%NOTFOUND THEN
43 l_uom_code := '';
44 END IF;
45
46 RETURN l_uom_code;
47 EXCEPTION
48 WHEN NO_DATA_FOUND THEN
49 RETURN '';
50 WHEN OTHERS THEN
51 RETURN '';
52 END get_uom_code;
53
54 -- Added for OPM changes, bug 4373856
55 FUNCTION get_uom2_code(
56 p_organization_id IN NUMBER
57 , p_inventory_item_id IN NUMBER
58 )
59 RETURN VARCHAR2 IS
60 l_uom_code VARCHAR2(3) := '';
61 BEGIN
62 SELECT SECONDARY_UOM_CODE
63 INTO l_uom_code
64 FROM mtl_system_items
65 WHERE organization_id = p_organization_id
66 AND inventory_item_id = p_inventory_item_id;
67
68 IF SQL%NOTFOUND THEN
69 l_uom_code := '';
70 END IF;
71
72 RETURN l_uom_code;
73 EXCEPTION
74 WHEN NO_DATA_FOUND THEN
75 RETURN '';
76 WHEN OTHERS THEN
77 RETURN '';
78 END get_uom2_code;
79
80
81 FUNCTION get_origination_type (
82 p_origination_type IN NUMBER
83 )
84 RETURN VARCHAR2 IS
85 l_origination_type mfg_lookups.meaning%TYPE := '';
86 BEGIN
87
88 SELECT meaning
89 into l_origination_type
90 FROM mfg_lookups
91 WHERE lookup_type = 'MTL_LOT_ORIGINATION_TYPE'
92 AND lookup_code = p_origination_type;
93
94 IF SQL%NOTFOUND THEN
95 l_origination_type := '';
96 END IF;
97
98 RETURN l_origination_type;
99 EXCEPTION
100 WHEN NO_DATA_FOUND THEN
101 RETURN '';
102 WHEN OTHERS THEN
103 RETURN '';
104 END get_origination_type;
105
106
107 PROCEDURE get_variable_data(
108 x_variable_content OUT NOCOPY inv_label.label_tbl_type
109 , x_msg_count OUT NOCOPY NUMBER
110 , x_msg_data OUT NOCOPY VARCHAR2
111 , x_return_status OUT NOCOPY VARCHAR2
112 , p_label_type_info IN inv_label.label_type_rec
113 , p_transaction_id IN NUMBER
114 , p_input_param IN mtl_material_transactions_temp%ROWTYPE
115 , p_transaction_identifier IN NUMBER
116 ) IS
117 -- Fix for 4888701: Included the Precsion and Scale for the variable quatity.
118 --l_quantity NUMBER := 0;
119 l_quantity NUMBER(38,5) := 0;
120 -- End of fix for 4888701
121
122 l_uom mtl_material_transactions.transaction_uom%TYPE;
123 l_revision mtl_material_transactions.revision%TYPE;
124 l_inventory_item_id NUMBER;
125 l_item_description VARCHAR2(240) := NULL;
126 l_organization_id NUMBER;
127 l_lot_number VARCHAR2(240);
128 l_from_subinventory VARCHAR2(30) := NULL;
129 l_to_subinventory VARCHAR2(30) := NULL;
130 l_subinventory_code VARCHAR2(30);
131 l_from_locator_id NUMBER;
132 l_to_locator_id NUMBER;
133 l_locator_id NUMBER;
134 l_cost_group_id NUMBER;
135 l_xfr_cost_group_id NUMBER; /* Added for the Bug # 4686024 */
136 l_cost_group VARCHAR2(240);
137 l_project_id NUMBER;
138 l_task_id NUMBER;
139 l_project_number VARCHAR (25); -- Fix For Bug: 4907062
140 l_task_number VARCHAR (25); -- Fix For Bug: 4907062
141 l_project_name VARCHAR2(240);
142 l_task_name VARCHAR2(240);
143 l_err_msg VARCHAR2(240);
144 item_fetch_cntr NUMBER := NULL;
145 -- Added for Bug 2308273
146 l_attribute_category VARCHAR2(150);
147 l_c_attribute1 VARCHAR2(150);
148 l_c_attribute2 VARCHAR2(150);
149 l_c_attribute3 VARCHAR2(150);
150 l_c_attribute4 VARCHAR2(150);
151 l_c_attribute5 VARCHAR2(150);
152 l_c_attribute6 VARCHAR2(150);
153 l_c_attribute7 VARCHAR2(150);
154 l_c_attribute8 VARCHAR2(150);
155 l_c_attribute9 VARCHAR2(150);
156 l_c_attribute10 VARCHAR2(150);
157 l_c_attribute11 VARCHAR2(150);
158 l_c_attribute12 VARCHAR2(150);
159 l_c_attribute13 VARCHAR2(150);
160 l_c_attribute14 VARCHAR2(150);
161 l_c_attribute15 VARCHAR2(150);
162 l_c_attribute16 VARCHAR2(150);
163 l_c_attribute17 VARCHAR2(150);
164 l_c_attribute18 VARCHAR2(150);
165 l_c_attribute19 VARCHAR2(150);
166 l_c_attribute20 VARCHAR2(150);
167 l_d_attribute1 DATE;
168 l_d_attribute2 DATE;
169 l_d_attribute3 DATE;
170 l_d_attribute4 DATE;
171 l_d_attribute5 DATE;
172 l_d_attribute6 DATE;
173 l_d_attribute7 DATE;
174 l_d_attribute8 DATE;
175 l_d_attribute9 DATE;
176 l_d_attribute10 DATE;
177 l_n_attribute1 NUMBER := NULL;
178 l_n_attribute2 NUMBER := NULL;
179 l_n_attribute3 NUMBER := NULL;
180 l_n_attribute4 NUMBER := NULL;
181 l_n_attribute5 NUMBER := NULL;
182 l_n_attribute6 NUMBER := NULL;
183 l_n_attribute7 NUMBER := NULL;
184 l_n_attribute8 NUMBER := NULL;
185 l_n_attribute9 NUMBER := NULL;
186 l_n_attribute10 NUMBER := NULL;
187 l_territory_code VARCHAR2(30);
188 l_grade_code VARCHAR2(150);
189 l_origination_date DATE;
190 l_date_code VARCHAR2(150);
191 l_change_date DATE;
192 l_age NUMBER := NULL;
193 l_retest_date DATE;
194 l_maturity_date DATE;
195 l_item_size NUMBER := NULL;
196 l_color VARCHAR2(150);
197 l_volume NUMBER := NULL;
198 l_volume_uom VARCHAR2(3);
199 l_place_of_origin VARCHAR2(150);
200 l_best_by_date DATE;
201 l_length NUMBER := NULL;
202 l_length_uom VARCHAR2(3);
203 l_recycled_content NUMBER := NULL;
204 l_thickness NUMBER := NULL;
205 l_thickness_uom VARCHAR2(3);
206 l_width NUMBER := NULL;
207 l_width_uom VARCHAR2(3);
208 l_curl_wrinkle_fold VARCHAR2(150);
209 l_vendor_name VARCHAR2(240);
210 -- Added l_transaction_identifier, for flow
211 -- Depending on when it is called, the driving table might be different
212 -- 1 means MMTT is the driving table
213 -- 2 means MTI is the driving table
214 -- 3 means Mtl_txn_request_lines is the driving table
215
216 l_transaction_identifier NUMBER := 0;
217 l_receipt_number VARCHAR2(30);
218 -- Added for Bug 2748297
219 l_vendor_id NUMBER;
220 l_vendor_site_id NUMBER;
221 -- Added for UCC 128 J Bug #3067059
222 l_gtin_enabled BOOLEAN := FALSE;
223 l_gtin VARCHAR2(100);
224 l_gtin_desc VARCHAR2(240);
225 l_quantity_floor NUMBER := 0;
226 -- changing l_shipment_num type from NUMBER to VARCHAR2 for bug 4306134
227 --l_shipment_num NUMBER;
228 l_shipment_num VARCHAR2(30);
229
230 /* Patchset J - Label Printing support for OSP
231 * Added the following local variables to support addition of new variables
232 * job / schedule, job description, OSP operation sequence, OSP department,
233 * and OSP resource in the Material Label. The cursors rt_material_cur,
234 * rti_material_lpn_cur, rti_material_inspec_cur, rti_material_mtlt_cur
235 * are changed.
236 */
237
238 l_wip_entity_id NUMBER;
239 l_wip_op_seq_num rcv_transactions.WIP_OPERATION_SEQ_NUM%type;
240 l_osp_dept_code VARCHAR2(10);
241 l_bom_resource_id NUMBER;
242 l_bom_resource_code VARCHAR2(20);
243 l_wip_entity_name wip_osp_jobs_val_v.wip_entity_name%TYPE;
244 l_wip_description wip_osp_jobs_val_v.description%TYPE;
245
246 ---------------------------------------------------------------------------------------------
247 -- Project: 'Custom Labels' (A 11i10+ Project) |
248 -- Author: Dinesh ([email protected]) |
249 -- Change Description: |
250 -- Following variables were added (as a part of 11i10+ 'Custom Labels' Project) |
251 -- to retrieve and hold the SQL Statement and it's result. |
252 ---------------------------------------------------------------------------------------------
253 l_sql_stmt VARCHAR2(4000);
254 l_sql_stmt_result VARCHAR2(4000);
255 TYPE sql_stmt IS REF CURSOR;
256 c_sql_stmt sql_stmt;
257 l_custom_sql_ret_status VARCHAR2(1);
258 l_custom_sql_ret_msg VARCHAR2(2000);
259
260 -- Fix for bug: 4179593 Start
261 l_CustSqlWarnFlagSet BOOLEAN;
262 l_CustSqlErrFlagSet BOOLEAN;
263 l_CustSqlWarnMsg VARCHAR2(2000);
264 l_CustSqlErrMsg VARCHAR2(2000);
265 -- Fix for bug: 4179593 End
266
267
268 --Start: Enabling EPC generation for R12 Project
269 l_epc VARCHAR2(300);
270 l_epc_ret_status VARCHAR2(10);
271 l_epc_ret_msg VARCHAR2(1000);
272 l_label_status VARCHAR2(1);
273 l_label_err_msg VARCHAR2(1000);
274 l_is_epc_exist VARCHAR2(1) := 'N';
275 l_label_formats_in_set NUMBER;
276 --End: Enabling EPC generation for R12 Project
277
278
279 ------------------------End of this change for Custom Labels project code--------------------
280
281 -- invconv fabdi start
282
283 l_parent_lot_number VARCHAR2(80);
284 l_expiration_action_date DATE;
285 l_expiration_action_code VARCHAR2(32);
286 l_origination_type NUMBER;
287 l_hold_date DATE;
288 l_secondary_uom_code VARCHAR2(3);
289 l_secondary_transaction_qty NUMBER;
290 l_supplier_lot_number VARCHAR2(150);
291 -- invconv fabdi start
292
293
294 -- For Receipt, Inspection, Putaway, Delivery,
295 -- the Item/Lot information is obtained like this
296
297 -- 1. For WMS Org, Item Lot Qty
298 -- -------- ------ -------
299 -- Receipt/Inspection rti rti+lpnCont rti or lpnContent
300 -- Putaway rti rti+mtlt rti or mtlt
301 -- Delivery no apply for WMS org
302 -- 2. For INV Org, Item Lot Qty
303 -- -------- ------ -------
304 -- Receipt/Inspection rti no lot rti.quantity
305 -- Putaway no apply for Inv org
306 -- Delivery rti rti+mtlt rti or mtlt
307 -- Therefore, two cursors are needed, rti+lpnContent or rti+mtlt
308
309 -- MOAC: Replaced the po_line_locations
310 -- view with a _ALL table the where clause of
311 -- the cursor select is sufficient to stripe
312 -- by a single OU.
313
314 -- RTI + LPN Content
315 -- Added vendor_id and vendor_site_id to the cursor for Bug 2748297
316 CURSOR rti_material_lpn_cur IS
317 SELECT rti.item_id inventory_item_id
318 , rti.to_organization_id organization_id
319 , wlc.lot_number lot_number
320 , rti.cost_group_id cost_group_id
321 , pol.project_id project_id
322 , pol.task_id task_id
323 -- Added by joabraha bug 3472150
324 , rsh.receipt_num
325 --
326 , NVL(wlc.quantity, rti.quantity) quantity
327 , -- Bug 2743097, For OSP or onetime expense item, they will not be packed into LPN
328 -- even in WMS org. So the UOM code need to be retrieved from RTI
329 NVL(
330 wlc.uom_code
331 , get_uom_code(
332 rti.to_organization_id
333 , rti.item_id
334 , rti.unit_of_measure
335 )
336 ) uom
337 , rti.item_revision revision
338 , rti.lpn_id
339 , pha.segment1
340 , pol.line_num po_line_number
341 , pll.quantity quantity_ordered
342 , rti.vendor_item_num supplier_part_number
343 , pov.vendor_id vendor_id
344 , pov.vendor_name supplier_name
345 , pvs.vendor_site_id vendor_site_id
346 , pvs.vendor_site_code supplier_site
347 , ppf.full_name requestor
348 , hrl1.location_code deliver_to_location
349 , hrl2.location_code location
350 , pll.note_to_receiver note_to_receiver
351 , rrh.routing_name routing_name
352 , rti.item_description item_description
353 , rti.subinventory
354 , rti.locator_id
355 , WOJV.WIP_ENTITY_NAME
356 , WOJV.DESCRIPTION
357 , RTI.WIP_OPERATION_SEQ_NUM
358 , rti.DEPARTMENT_CODE
359 , rti.BOM_RESOURCE_ID
360 FROM rcv_transactions_interface rti
361 , wms_lpn_contents wlc
362 , po_lines_all pol
363 , po_headers_all pha
364 , rcv_shipment_headers rsh
365 , po_line_locations_all pll
366 , po_vendors pov
367 , hr_locations_all hrl1
368 , hr_locations_all hrl2
369 , po_vendor_sites_all pvs
370 , per_people_f ppf
371 , rcv_routing_headers rrh
372 , wip_osp_jobs_val_v wojv
373 WHERE wlc.parent_lpn_id(+) = rti.lpn_id
374 AND wlc.inventory_item_id(+) = rti.item_id -- bug 2372669
375 AND pol.po_line_id(+) = rti.po_line_id
376 AND pha.po_header_id(+) = rti.po_header_id
377 AND rsh.shipment_header_id(+) = rti.shipment_header_id
378 AND pll.line_location_id(+) = rti.po_line_location_id
379 AND pov.vendor_id(+) = rti.vendor_id
380 -- AND pvs.vendor_id(+) = rti.vendor_id This line is uneccessary dherring 8/2/05
381 AND pvs.vendor_site_id(+) = rti.vendor_site_id
382 AND ppf.person_id(+) = rti.deliver_to_person_id
383 AND hrl1.location_id(+) = rti.deliver_to_location_id
384 AND hrl2.location_id(+) = rti.location_id
385 AND rrh.routing_header_id(+) = rti.routing_header_id
386 AND wlc.source_header_id(+) = rti.GROUP_ID --- Added for Bug 2699098.
387 AND rti.interface_transaction_id = p_transaction_id
388 AND wojv.wip_entity_id = rti.wip_entity_id
389 AND TRUNC(SYSDATE) BETWEEN ppf.effective_start_date(+) AND ppf.effective_end_date(+); -- for bug#5889715
390
391 -- MOAC: Replaced the po_line_locations
392 -- view with a _ALL table the where clause of
393 -- the cursor select is sufficient to stripe
394 -- by a single OU.
395
396 -- Inspection Cursor for Bug 2377796
397 -- Added vendor_id and vendor_site_id to the cursor for Bug 2748297
398 CURSOR rti_material_lpn_inspec_cur IS
399 SELECT rti.item_id inventory_item_id
400 , rti.to_organization_id organization_id
401 , wlc.lot_number lot_number
402 , rti.cost_group_id cost_group_id
403 , pol.project_id project_id
404 , pol.task_id task_id
405 -- Added by joabraha bug 3472150
406 , rsh.receipt_num
407 --
408 , NVL(wlc.quantity, rti.quantity) quantity
409 , -- Bug 2743097, For OSP or onetime expense item, they will not be packed into LPN
410 -- even in WMS org. So the UOM code need to be retrieved from RTI
411 NVL(
412 wlc.uom_code
413 , get_uom_code(
414 rti.to_organization_id
415 , rti.item_id
416 , rti.unit_of_measure
417 )
418 ) uom
419 , rti.item_revision revision
420 , rti.lpn_id
421 , pha.segment1
422 , pol.line_num po_line_number
423 , pll.quantity quantity_ordered
424 , rti.vendor_item_num supplier_part_number
425 , pov.vendor_id vendor_id
426 , pov.vendor_name supplier_name
427 , pvs.vendor_site_id vendor_site_id
428 , pvs.vendor_site_code supplier_site
429 , ppf.full_name requestor
430 , hrl1.location_code deliver_to_location
431 , hrl2.location_code location
432 , pll.note_to_receiver note_to_receiver
433 , rrh.routing_name routing_name
434 , rti.item_description item_description
435 , rti.subinventory
436 , rti.locator_id
437 , WOJV.WIP_ENTITY_NAME
438 , WOJV.DESCRIPTION
439 , RTI.WIP_OPERATION_SEQ_NUM
440 , rti.DEPARTMENT_CODE
441 , rti.BOM_RESOURCE_ID
442 FROM rcv_transactions_interface rti
443 , wms_lpn_contents wlc
444 , po_lines_all pol
445 , po_headers_all pha
446 , rcv_shipment_headers rsh
447 , po_line_locations_all pll
448 , po_vendors pov
449 , hr_locations_all hrl1
450 , hr_locations_all hrl2
451 , po_vendor_sites_all pvs
452 , per_people_f ppf
453 , rcv_routing_headers rrh
454 , wip_osp_jobs_val_v wojv
455 WHERE wlc.parent_lpn_id(+) = rti.transfer_lpn_id
456 AND wlc.inventory_item_id(+) = rti.item_id -- bug 2372669
457 AND pol.po_line_id(+) = rti.po_line_id
458 AND pha.po_header_id(+) = rti.po_header_id
459 AND rsh.shipment_header_id(+) = rti.shipment_header_id
460 AND pll.line_location_id(+) = rti.po_line_location_id
461 AND pov.vendor_id(+) = rti.vendor_id
462 -- AND pvs.vendor_id(+) = rti.vendor_id This line is uneccessary dherring 8/2/05
463 AND pvs.vendor_site_id(+) = rti.vendor_site_id
464 AND ppf.person_id(+) = rti.deliver_to_person_id
465 AND hrl1.location_id(+) = rti.deliver_to_location_id
466 AND hrl2.location_id(+) = rti.location_id
467 AND rrh.routing_header_id(+) = rti.routing_header_id
468 AND wlc.source_header_id(+) = rti.GROUP_ID --- Added for Bug 2699098.
469 AND rti.interface_transaction_id = p_transaction_id
470 AND TRUNC(SYSDATE) BETWEEN ppf.effective_start_date(+) AND ppf.effective_end_date(+); -- for bug#5889715
471
472 -- RTI + MTLT
473 -- This is the new cursor designed for the "I" cleanup project for printing at receipt
474 -- Added vendor_id and vendor_site_id to the cursor for Bug 2748297
475
476 -- MOAC: Replaced the po_line_locations
477 -- view with a _ALL table the where clause of
478 -- the cursor select is sufficient to stripe
479 -- by a single OU.
480
481 CURSOR rti_material_mtlt_cur IS
482 SELECT rti.item_id
483 , rti.item_revision
484 , mtlt.lot_number
485 , rti2.organization_id
486 , rti2.cost_group_id
487 , rti2.project_id
488 , rti2.task_id
489 , SUM(NVL(mtlt.transaction_quantity, rti.quantity)) quantity
490 , rti2.uom
491 , rti2.segment1
492 , rti2.po_line_number
493 , rti2.quantity_ordered
494 , rti2.supplier_part_number
495 , rti2.vendor_id
496 , rti2.supplier_name
497 , rti2.vendor_site_id
498 , rti2.supplier_site
499 , rti2.requestor
500 , rti2.deliver_to_location
501 , rti2.location
502 , rti2.note_to_receiver
503 , rti2.routing_name
504 , rti2.item_description
505 , rti2.subinventory
506 , rti2.locator_id
507 , WOJV.WIP_ENTITY_NAME
508 , WOJV.DESCRIPTION
509 , RTI.WIP_OPERATION_SEQ_NUM
510 , rti.DEPARTMENT_CODE
511 , rti.BOM_RESOURCE_ID
512 FROM rcv_transactions_interface rti
513 , wip_osp_jobs_val_v wojv
514 , mtl_transaction_lots_temp mtlt
515 , /***************************************/
516 (SELECT rti.GROUP_ID
517 , rti.interface_transaction_id
518 , rti.to_organization_id organization_id
519 , rti.cost_group_id cost_group_id
520 , pol.project_id project_id
521 , pol.task_id task_id
522 , inv_label_pvt1.get_uom_code(
523 rti.to_organization_id
524 , rti.item_id
525 , rti.unit_of_measure
526 ) uom
527 , pha.segment1 segment1
528 , pol.line_num po_line_number
529 , pll.quantity quantity_ordered
530 , rti.vendor_item_num supplier_part_number
531 , pov.vendor_id vendor_id
532 , pov.vendor_name supplier_name
533 , pvs.vendor_site_id vendor_site_id
534 , pvs.vendor_site_code supplier_site
535 , hre.full_name requestor
536 , hrl1.location_code deliver_to_location
537 , hrl2.location_code location
538 , pll.note_to_receiver note_to_receiver
539 , rrh.routing_name routing_name
540 , rti.item_description item_description
541 , rti.subinventory subinventory
542 , rti.locator_id locator_id
543 FROM rcv_transactions_interface rti
544 , po_lines_all pol
545 , po_headers_all pha
546 , rcv_shipment_headers rsh
547 , po_line_locations_all pll
548 , po_vendors pov
549 , hr_locations hrl1
550 , hr_locations hrl2
551 -- MOAC changed po_vendor_sites to po_vendor_sites_all
552 , po_vendor_sites_all pvs
553 , hr_employees hre
554 , rcv_routing_headers rrh
555 , wip_osp_jobs_val_v wojv
556 WHERE rti.GROUP_ID IN (SELECT GROUP_ID
557 FROM rcv_transactions_interface
558 WHERE interface_transaction_id =
559 p_transaction_id)
560 AND pol.po_line_id(+) = rti.po_line_id
561 AND pha.po_header_id(+) = rti.po_header_id
562 AND rsh.shipment_header_id(+) = rti.shipment_header_id
563 AND pll.line_location_id(+) = rti.po_line_location_id
564 AND pov.vendor_id(+) = rti.vendor_id
565 -- corrected following line to be pvs.vendor_site_id and not pvs.vendor_id dherring
566 AND pvs.vendor_site_id(+) = rti.vendor_site_id
567 -- AND pvs.vendor_id(+) = rti.vendor_id Uneccessary line dherring 8/2/05
568 AND hre.employee_id(+) = rti.deliver_to_person_id
569 AND hrl1.location_id(+) = rti.deliver_to_location_id
570 AND rrh.routing_header_id(+) = rti.routing_header_id
571 AND hrl2.location_id(+) = rti.location_id) rti2
572 /***************************************/
573 WHERE inv_label_pvt1.check_rti_id(
574 rti2.interface_transaction_id
575 , mtlt.lot_number
576 , rti.item_revision
577 ) = 'N'
578 AND mtlt.transaction_temp_id(+) = rti.interface_transaction_id
579 AND rti.interface_transaction_id = rti2.interface_transaction_id
580 AND rti.GROUP_ID = rti2.GROUP_ID
581 AND rti.wip_entity_id = wojv.wip_entity_id
582 GROUP BY rti.item_id
583 , rti.item_revision
584 , mtlt.lot_number
585 , rti2.organization_id
586 , rti2.cost_group_id
587 , rti2.project_id
588 , rti2.task_id
589 , rti2.uom
590 , rti2.segment1
591 , rti2.po_line_number
592 , rti2.quantity_ordered
593 , rti2.supplier_part_number
594 /* Bug# 3329195 - Added rti2.vendor_id and rti2.vendor_site_id to the group by clause */
595 , rti2.vendor_id
596 , rti2.supplier_name
597 , rti2.vendor_site_id
598 , rti2.supplier_site
599 , rti2.requestor
600 , rti2.deliver_to_location
601 , rti2.location
602 , rti2.note_to_receiver
603 , rti2.routing_name
604 , rti2.item_description
605 , rti2.subinventory
606 , rti2.locator_id
607 , WOJV.WIP_ENTITY_NAME
608 , WOJV.DESCRIPTION
609 , RTI.WIP_OPERATION_SEQ_NUM
610 , rti.DEPARTMENT_CODE
611 , rti.BOM_RESOURCE_ID;
612
613 /* 3069426 - Patchset J project - Label printing enhancements -
614 * Use one cursor that queries RCV_TRANSACTIONS_INTERFACE and RCV_LOTS_INTERFACE tables for
615 * Item, Lot, Quantity information
616 */
617
618 -- MOAC: Replaced the po_line_locations
619 -- view with a _ALL table the where clause of
620 -- the cursor select is sufficient to stripe
621 -- by a single OU.
622
623 /* Modified for Bug# 4516067
624 * Reverted the Modifications done for the Bug#4186856.
625 * The modifications done for the Bug#4186856 was causing performance issues.
626 */
627
628 CURSOR rt_material_cur IS
629 SELECT rsl.item_id inventory_item_id
630 , rt.organization_id organization_id
631 , rls.lot_num lot_number -- Reverted to original code as part of Bug#4516067
632 -- , rsl.cost_group_id cost_group_id /* Modified for the Bug # 4770558 */
633 , mmt.cost_group_id cost_group_id
634 --Bug# 3586116 - Get project and task id from rt
635 , rt.project_id
636 , rt.task_id
637 -- , pod.project_id project_id --Commented as part of Bug# 3586116
638 -- , pod.task_id task_id --Commented as part of Bug# 3586116
639 -- Added by joabraha bug 3472150
640 , rsh.receipt_num
641 , SUM(NVL(rls.quantity, rt.quantity)) quantity -- Reverted to original code as part of Bug#4516067
642 -- Commented as part of the Bug#4516067 and added the code to fetch secondary_quantity from rls instead of mtln
643 -- , SUM(NVL(mtln.SECONDARY_TRANSACTION_QUANTITY, rt.SECONDARY_QUANTITY)) secondary_quantity -- fabdi 4373856
644 , SUM(NVL(rls.SECONDARY_QUANTITY, rt.SECONDARY_QUANTITY)) secondary_quantity -- fabdi 4373856
645 , (inv_label_pvt1.get_uom_code(
646 rt.organization_id
647 , rsl.item_id
648 , rsl.unit_of_measure
649 )
650 ) uom
651 , (inv_label_pvt1.get_uom2_code(
652 rt.organization_id
653 , rsl.item_id
654 )
655 ) secondary_uom -- bug 4373856
656 , rsl.item_revision revision
657 , pha.segment1
658 , rsh.shipment_num
659 , pol.line_num po_line_number
660 , pll.quantity quantity_ordered
661 , rsl.vendor_item_num supplier_part_number
662 , pov.vendor_id vendor_id
663 , pov.vendor_name supplier_name
664 , pvs.vendor_site_id vendor_site_id
665 , pvs.vendor_site_code supplier_site
666 , ppf.full_name requestor
667 , hrl1.location_code deliver_to_location
668 , hrl2.location_code location
669 , pll.note_to_receiver note_to_receiver
670 , rrh.routing_name routing_name
671 --Bug 6504959-Reverted fix made which was fetching item desc from msiv.
672 , rsl.item_description item_description
673 , rt.subinventory
674 , rt.locator_id
675 -- Bug 4516067, to improve performance, query the base table directly
676 --, WOJV.WIP_ENTITY_NAME wip_entity_name
677 --, WOJV.DESCRIPTION wip_description
678 , we.wip_entity_name wip_entity_name -- Added for Bug#4516067
679 , wdj.description wip_description -- Added for Bug#4516067
680 , RT.WIP_OPERATION_SEQ_NUM wip_op_seq_num
681 , rt.DEPARTMENT_CODE wip_department_code
682 , rt.BOM_RESOURCE_ID wip_bom_resource_id
683 , wlpn.lpn_context
684 , wlpn.lpn_id
685 , rt.routing_header_id routing_header_id --bug 4916450
686 FROM rcv_transactions rt
687 , rcv_lots_supply rls -- Reverted to original code as part of Bug#4516067
688 -- , rcv_lot_transactions rls -- Replaced rcv_lot_transactions by mtl_transaction_lot_numbers to fetch the LOT details as part of Bug# 4186856
689 -- Added rt2 as part of Bug# 4186856
690 -- rt2, mtln commented for Bug#4516067 to revert the changes done for Bug#4186856
691 /* , (select transaction_id
692 * from rcv_transactions rt_deliver
693 * where rt_deliver.group_id = p_transaction_id
694 * and rt_deliver.transaction_type = 'DELIVER') rt2
695 * , mtl_transaction_lot_numbers mtln -- Added as part of Bug# 4186856
696 */
697 , rcv_shipment_lines rsl
698 , po_lines_all pol
699 -- , po_distributions_all pod --Commented as part of Bug# 3586116
700 , po_headers_all pha
701 , rcv_shipment_headers rsh
702 , po_line_locations_all pll
703 , po_vendors pov
704 , hr_locations_all hrl1
705 , hr_locations_all hrl2
706 , po_vendor_sites_all pvs
707 , per_people_f ppf
708 , rcv_routing_headers rrh
709 -- Bug 4516067, to improve performance, query the base table directly
710 --, wip_osp_jobs_val_v wojv
711 , wip_entities we -- Added for Bug#4516067
712 , wip_discrete_jobs wdj -- Added for Bug#4516067
713 , wms_license_plate_numbers wlpn -- Bug 3836623
714 , (SELECT cost_group_id, rcv_transaction_id
715 FROM mtl_material_transactions mmt1
716 WHERE mmt1.rcv_transaction_id = p_transaction_id
717 AND nvl(mmt1.logical_transaction, -999) <> 1) mmt -- Modified for bug# 5515979
718 --, mtl_material_transactions mmt -- Added for the Bug # 4770558
719 WHERE rls.transaction_id(+) = rt.transaction_id -- Reverted to original code as part of Bug#4516067
720 --mtln.product_transaction_id(+) = rt.transaction_id -- Commented as part of Bug#4186856
721 /* Reverted to original code as part of Bug#4516067
722 * mtln.product_code = 'RCV' -- Added as part of Bug#4186856
723 * AND mtln.product_transaction_id = rt2.transaction_id -- Added as part of Bug#4186856
724 * AND mtln.inventory_item_id = pol.item_id -- Added as part of Bug#4186856
725 */
726 AND pol.po_line_id(+) = rt.po_line_id
727 AND pha.po_header_id(+) = rt.po_header_id
728 AND rsh.shipment_header_id(+) = rt.shipment_header_id
729 AND pll.line_location_id(+) = rt.po_line_location_id
730 -- AND pod.po_distribution_id(+) = rt.po_distribution_id --Commented as part of Bug# 3586116
731 AND pov.vendor_id(+) = rt.vendor_id
732 -- AND pvs.vendor_id(+) = rt.vendor_id Uneccessary line dherring 8/2/05
733 AND pvs.vendor_site_id(+) = rt.vendor_site_id
734 AND ppf.person_id(+) = rt.deliver_to_person_id
735 AND hrl1.location_id(+) = rt.deliver_to_location_id
736 AND hrl2.location_id(+) = rt.location_id
737 AND rrh.routing_header_id(+) = rt.routing_header_id
738 AND rsl.shipment_line_id = rt.shipment_line_id
739 AND rt.GROUP_ID = p_transaction_id
740 -- Bug 4516067, to improve performance, query the base table directly
741 --AND rt.wip_entity_id = wojv.wip_entity_id (+)
742 AND rt.wip_entity_id = we.wip_entity_id (+)
743 AND rt.wip_entity_id = wdj.wip_entity_id (+)
744 AND ((rt.transaction_type IN ('ACCEPT', 'REJECT')
745 AND p_label_type_info.business_flow_code = 2) -- label flow code 'Inspect'
746 OR (rt.transaction_type = 'RECEIVE'
747 AND p_label_type_info.business_flow_code = 1 -- label flow code 'Receive'
748 -- Commented following condition for bug 4142656
749 -- Reverted back the changes done for Bug#4142656 as part of Bug#4516067
750 AND rt.routing_header_id <> 3
751 )
752 )
753 -- Bug 3836623
754 -- Add check for LPN context
755 -- When cross docking happens, label printing are called for both cross docking and putaway
756 -- To prevent duplicate labels
757 -- For putaway business flow, only print if LPN Context is not Picked (11)
758 AND wlpn.lpn_id(+) = rt.lpn_id
759 AND ((rt.lpn_id IS NULL) OR
760 (p_label_type_info.business_flow_code <> 4) OR
761 (p_label_type_info.business_flow_code = 4 AND
762 wlpn.lpn_context <> 11))
763 AND rt.transaction_id = mmt.rcv_transaction_id(+) /* Added for the Bug # 4770558 */
764 AND TRUNC(SYSDATE) BETWEEN ppf.effective_start_date(+) AND ppf.effective_end_date(+) -- for bug#5889715
765 -- The outer join has been added in the above condition for solving the bug # 4863161
766 GROUP BY rsl.item_id
767 , rt.organization_id
768 , rls.lot_num -- Modified as part of Bug# 4516067
769 --, rsl.cost_group_id /* Modified for the Bug # 4770558 */
770 , mmt.cost_group_id
771 , rt.project_id
772 , rt.task_id
773 -- , pod.project_id --Commented as part of Bug# 3586116
774 -- , pod.task_id --Commented as part of Bug# 3586116
775 -- Added by joabraha bug 3472150
776 , rsh.receipt_num
777 --
778 , inv_label_pvt1.get_uom_code(
779 rt.organization_id
780 , rsl.item_id
781 , rsl.unit_of_measure
782 )
783 , (inv_label_pvt1.get_uom2_code(
784 rt.organization_id
785 , rsl.item_id
786 ) -- bug 4373856
787 )
788 , (inv_label_pvt1.get_uom2_code(
789 rt.organization_id
790 , rsl.item_id
791 )
792 ) -- bug 4373856
793 , rsl.item_revision
794 , pha.segment1
795 , rsh.shipment_num
796 , pol.line_num
797 , pll.quantity
798 , rsl.vendor_item_num
799 , pov.vendor_id
800 , pov.vendor_name
801 , pvs.vendor_site_id
802 , pvs.vendor_site_code
803 , ppf.full_name
804 , hrl1.location_code
805 , hrl2.location_code
806 , pll.note_to_receiver
807 , rrh.routing_name
808 --Bug 6504959-Reverted fix made which was fetching item desc from msiv.
809 , rsl.item_description
810 , rt.subinventory
811 , rt.locator_id
812 -- Bug 4516067, to improve performance, query the base table directly
813 --, WOJV.WIP_ENTITY_NAME
814 --, WOJV.DESCRIPTION
815 , we.wip_entity_name
816 , wdj.description
817 , RT.WIP_OPERATION_SEQ_NUM
818 , rt.DEPARTMENT_CODE
819 , rt.BOM_RESOURCE_ID
820 , wlpn.lpn_context
821 , wlpn.lpn_id
822 , rt.routing_header_id --bug 4916450
823 UNION ALL -- Removed the cursor Added as part of 4186856 to segregate the Lot Controlled items and non Lot Controlled items.
824 -- Added a new cursor to pick the records for label during Direct Routing for Bug# 4516067
825 SELECT rsl.item_id inventory_item_id
826 , rt.organization_id organization_id
827 , mtln.lot_number lot_number
828 -- , rsl.cost_group_id cost_group_id /* Modified for the Bug # 4770558 */
829 , mmt.cost_group_id cost_group_id
830 --Bug# 3586116 - Get project and task id from rt
831 , rt.project_id
832 , rt.task_id
833 -- , pod.project_id project_id
834 --, pod.task_id task_id
835 -- Added by joabraha bug 3472150
836 , rsh.receipt_num
837 --
838 , SUM(NVL(mtln.transaction_quantity, rt.quantity)) quantity
839 , SUM(NVL(mtln.SECONDARY_TRANSACTION_QUANTITY, rt.SECONDARY_QUANTITY)) secondary_quantity -- fabdi 4373856
840 , (inv_label_pvt1.get_uom_code(
841 rt.organization_id
842 , rsl.item_id
843 , rsl.unit_of_measure
844 )
845 ) uom
846 , (inv_label_pvt1.get_uom2_code(
847 rt.organization_id
848 , rsl.item_id
849 )
850 ) secondary_uom -- bug 4373856
851 , rsl.item_revision revision
852 , pha.segment1
853 , rsh.shipment_num
854 , pol.line_num po_line_number
855 , pll.quantity quantity_ordered
856 , rsl.vendor_item_num supplier_part_number
857 , pov.vendor_id vendor_id
858 , pov.vendor_name supplier_name
859 , pvs.vendor_site_id vendor_site_id
860 , pvs.vendor_site_code supplier_site
861 , ppf.full_name requestor
862 , hrl1.location_code deliver_to_location
863 , hrl2.location_code location
864 , pll.note_to_receiver note_to_receiver
865 , rrh.routing_name routing_name
866 --Bug 6504959-Reverted fix made which was fetching item desc from msiv.
867 , rsl.item_description item_description
868 , rt.subinventory
869 , rt.locator_id
870 -- Bug 4516067, to improve performance, query the base table directly
871 --, WOJV.WIP_ENTITY_NAME wip_entity_name
872 --, WOJV.DESCRIPTION wip_description
873 , we.wip_entity_name wip_entity_name
874 , wdj.description wip_description
875 , RT.WIP_OPERATION_SEQ_NUM
876 , rt.DEPARTMENT_CODE
877 , rt.BOM_RESOURCE_ID
878 , wlpn.lpn_context
879 , wlpn.lpn_id
880 , rt.routing_header_id routing_header_id --bug 4916450
881 FROM rcv_transactions rt
882 , mtl_transaction_lot_numbers mtln
883 , rcv_shipment_lines rsl
884 , po_lines_all pol
885 -- , po_distributions_all pod --Commented as part of Bug# 3586116
886 , po_headers_all pha
887 , rcv_shipment_headers rsh
888 , po_line_locations pll
889 , po_vendors pov
890 , hr_locations_all hrl1
891 , hr_locations_all hrl2
892 , po_vendor_sites_all pvs
893 , per_people_f ppf
894 , rcv_routing_headers rrh
895 -- Bug 4516067, to improve performance, query the base table directly
896 --, wip_osp_jobs_val_v wojv
897 , wip_entities we
898 , wip_discrete_jobs wdj
899 , wms_license_plate_numbers wlpn -- Bug 3836623
900 , (SELECT cost_group_id, rcv_transaction_id
901 FROM mtl_material_transactions mmt1
902 WHERE mmt1.rcv_transaction_id = p_transaction_id
903 AND nvl(mmt1.logical_transaction, -999) <> 1) mmt -- Modified for bug# 5515979
904 --, mtl_material_transactions mmt -- Added for the Bug # 4770558
905 WHERE mtln.product_transaction_id(+) = rt.transaction_id
906 AND mtln.product_code(+) = 'RCV'
907 AND pol.po_line_id(+) = rt.po_line_id
908 AND pha.po_header_id(+) = rt.po_header_id
909 AND rsh.shipment_header_id(+) = rt.shipment_header_id
910 AND pll.line_location_id(+) = rt.po_line_location_id
911 -- AND pod.po_distribution_id(+) = rt.po_distribution_id --Commented as part of Bug# 3586116
912 AND pov.vendor_id(+) = rt.vendor_id
913 -- AND pvs.vendor_id(+) = rt.vendor_id
914 AND pvs.vendor_site_id(+) = rt.vendor_site_id
915 AND ppf.person_id(+) = rt.deliver_to_person_id
916 AND hrl1.location_id(+) = rt.deliver_to_location_id
917 AND hrl2.location_id(+) = rt.location_id
918 AND rrh.routing_header_id(+) = rt.routing_header_id
919 AND rsl.shipment_line_id = rt.shipment_line_id
920 AND rt.GROUP_ID = p_transaction_id
921 AND rt.transaction_type = 'DELIVER'
922 AND rt.routing_header_id = 3 -- Added as part of Bug# 4516067
923 AND p_label_type_info.business_flow_code in (1) -- Only pick for label flow code of 'deliver' or 'putaway'
924 -- Bug 4516067, to improve performance, query the base table directly
925 --AND rt.wip_entity_id = wojv.wip_entity_id (+)
926 AND rt.wip_entity_id = we.wip_entity_id (+)
927 AND rt.wip_entity_id = wdj.wip_entity_id (+)
928 -- Bug 3836623
929 -- Add check for LPN context
930 -- When cross docking happens, label printing are called for both cross docking and putaway
931 -- To prevent duplicate labels
932 -- For putaway business flow, only print if LPN Context is not Picked (11)
933 AND wlpn.lpn_id(+) = rt.lpn_id
934 AND ((rt.lpn_id IS NULL) OR
935 (p_label_type_info.business_flow_code <> 4) OR
936 (p_label_type_info.business_flow_code = 4 AND
937 wlpn.lpn_context <> 11))
938 AND rt.transaction_id = mmt.rcv_transaction_id(+) /* Added for the Bug # 4770558 */
939 AND TRUNC(SYSDATE) BETWEEN ppf.effective_start_date(+) AND ppf.effective_end_date(+) -- for bug#5889715
940 -- The outer join has been added in the above condition for solving the bug # 4863161
941 GROUP BY rsl.item_id
942 , rt.organization_id
943 , mtln.lot_number
944 --, rsl.cost_group_id /* Modified for the Bug # 4770558 */
945 , mmt.cost_group_id
946 , rt.project_id
947 , rt.task_id
948 -- , pod.project_id --Commented as part of Bug# 3586116
949 --, pod.task_id --Commented as part of Bug# 3586116
950 -- Added by joabraha bug 3472150
951 , rsh.receipt_num
952 --
953 , (inv_label_pvt1.get_uom_code(
954 rt.organization_id
955 , rsl.item_id
956 , rsl.unit_of_measure
957 )
958 )
959 , (inv_label_pvt1.get_uom2_code(
960 rt.organization_id
961 , rsl.item_id
962 )
963 ) -- bug 4373856
964 , rsl.item_revision
965 , pha.segment1
966 , rsh.shipment_num
967 , pol.line_num
968 , pll.quantity
969 , rsl.vendor_item_num
970 , pov.vendor_id
971 , pov.vendor_name
972 , pvs.vendor_site_id
973 , pvs.vendor_site_code
974 , ppf.full_name
975 , hrl1.location_code
976 , hrl2.location_code
977 , pll.note_to_receiver
978 , rrh.routing_name
979 --Bug 6504959-Reverted fix made which was fetching item desc from msiv.
980 , rsl.item_description
981 , rt.subinventory
982 , rt.locator_id
983 -- Bug 4516067, to improve performance, query the base table directly
984 --, WOJV.WIP_ENTITY_NAME
985 --, WOJV.DESCRIPTION
986 , we.wip_entity_name
987 , wdj.description
988 , RT.WIP_OPERATION_SEQ_NUM
989 , rt.DEPARTMENT_CODE
990 , rt.BOM_RESOURCE_ID
991 , wlpn.lpn_context
992 , wlpn.lpn_id
993 , rt.routing_header_id; --bug 4916450
994
995 -- Bug 4516067, break the following query into new cursor for putaway and deliver
996 -- it was part of rt_material_cur
997 CURSOR rt_putaway_deliver_cur IS
998 SELECT rsl.item_id inventory_item_id
999 , rt.organization_id organization_id
1000 , mtln.lot_number lot_number
1001 , rsl.cost_group_id cost_group_id
1002 --Bug# 3586116 - Get project and task id from rt
1003 , rt.project_id
1004 , rt.task_id
1005 -- , pod.project_id project_id
1006 --, pod.task_id task_id
1007 -- Added by joabraha bug 3472150
1008 , rsh.receipt_num
1009 --
1010 , SUM(NVL(mtln.transaction_quantity, rt.quantity)) quantity
1011 , SUM(NVL(mtln.SECONDARY_TRANSACTION_QUANTITY, rt.SECONDARY_QUANTITY)) secondary_quantity
1012 , (inv_label_pvt1.get_uom_code(
1013 rt.organization_id
1014 , rsl.item_id
1015 , rsl.unit_of_measure
1016 )
1017 ) uom
1018 , (inv_label_pvt1.get_uom2_code(
1019 rt.organization_id
1020 , rsl.item_id
1021 )
1022 ) secondary_uom -- bug 4373856
1023 , rsl.item_revision revision
1024 , pha.segment1
1025 , rsh.shipment_num
1026 , pol.line_num po_line_number
1027 , pll.quantity quantity_ordered
1028 , rsl.vendor_item_num supplier_part_number
1029 , pov.vendor_id vendor_id
1030 , pov.vendor_name supplier_name
1031 , pvs.vendor_site_id vendor_site_id
1032 , pvs.vendor_site_code supplier_site
1033 , ppf.full_name requestor
1034 , hrl1.location_code deliver_to_location
1035 , hrl2.location_code location
1036 , pll.note_to_receiver note_to_receiver
1037 , rrh.routing_name routing_name
1038 , rsl.item_description item_description
1039 , rt.subinventory
1040 , rt.locator_id
1041 -- Bug 4516067, to improve performance, query the base table directly
1042 --, WOJV.WIP_ENTITY_NAME wip_entity_name
1043 --, WOJV.DESCRIPTION wip_description
1044 , we.wip_entity_name wip_entity_name
1045 , wdj.description wip_description
1046 , RT.WIP_OPERATION_SEQ_NUM
1047 , rt.DEPARTMENT_CODE
1048 , rt.BOM_RESOURCE_ID
1049 , wlpn.lpn_context
1050 , wlpn.lpn_id
1051 , rt.routing_header_id --bug 4916450
1052 FROM rcv_transactions rt
1053 , mtl_transaction_lot_numbers mtln
1054 , rcv_shipment_lines rsl
1055 , po_lines_all pol
1056 -- , po_distributions_all pod --Commented as part of Bug# 3586116
1057 , po_headers_all pha
1058 , rcv_shipment_headers rsh
1059 , po_line_locations pll
1060 , po_vendors pov
1061 , hr_locations_all hrl1
1062 , hr_locations_all hrl2
1063 , po_vendor_sites_all pvs
1064 , per_people_f ppf
1065 , rcv_routing_headers rrh
1066 -- Bug 4516067, to improve performance, query the base table directly
1067 --, wip_osp_jobs_val_v wojv
1068 , wip_entities we
1069 , wip_discrete_jobs wdj
1070 , wms_license_plate_numbers wlpn -- Bug 3836623
1071 WHERE mtln.product_transaction_id(+) = rt.transaction_id
1072 AND mtln.product_code(+) = 'RCV'
1073 AND pol.po_line_id(+) = rt.po_line_id
1074 AND pha.po_header_id(+) = rt.po_header_id
1075 AND rsh.shipment_header_id(+) = rt.shipment_header_id
1076 AND pll.line_location_id(+) = rt.po_line_location_id
1077 -- AND pod.po_distribution_id(+) = rt.po_distribution_id --Commented as part of Bug# 3586116
1078 AND pov.vendor_id(+) = rt.vendor_id
1079 -- AND pvs.vendor_id(+) = rt.vendor_id uneccessary line dherring 8/2/05
1080 AND pvs.vendor_site_id(+) = rt.vendor_site_id
1081 AND ppf.person_id(+) = rt.deliver_to_person_id
1082 AND hrl1.location_id(+) = rt.deliver_to_location_id
1083 AND hrl2.location_id(+) = rt.location_id
1084 AND rrh.routing_header_id(+) = rt.routing_header_id
1085 AND rsl.shipment_line_id = rt.shipment_line_id
1086 AND rt.GROUP_ID = p_transaction_id
1087 AND rt.transaction_type = 'DELIVER'
1088 -- Bug 4516067, because created this new cursor for putaway and deliver
1089 -- no need to restrict business flow code here
1090 -- AND p_label_type_info.business_flow_code in (3,4) -- Only pick for label flow code of 'deliver' or 'putaway'
1091 -- Bug 4516067, to improve performance, query the base table directly
1092 -- AND rt.wip_entity_id = wojv.wip_entity_id (+)
1093 AND rt.wip_entity_id = we.wip_entity_id (+)
1094 AND rt.wip_entity_id = wdj.wip_entity_id (+)
1095 -- Bug 3836623
1096 -- Add check for LPN context
1097 -- When cross docking happens, label printing are called for both cross docking and putaway
1098 -- To prevent duplicate labels
1099 -- For putaway business flow, only print if LPN Context is not Picked (11)
1100 AND wlpn.lpn_id(+) = rt.lpn_id
1101 AND ((rt.lpn_id IS NULL) OR
1102 (p_label_type_info.business_flow_code <> 4) OR
1103 (p_label_type_info.business_flow_code = 4 AND
1104 wlpn.lpn_context <> 11))
1105 AND TRUNC(SYSDATE) BETWEEN ppf.effective_start_date(+) AND ppf.effective_end_date(+) -- for bug#5889715
1106 GROUP BY rsl.item_id
1107 , rt.organization_id
1108 , mtln.lot_number
1109 , rsl.cost_group_id
1110 , rt.project_id
1111 , rt.task_id
1112 -- , pod.project_id --Commented as part of Bug# 3586116
1113 --, pod.task_id --Commented as part of Bug# 3586116
1114 -- Added by joabraha bug 3472150
1115 , rsh.receipt_num
1116 --
1117 , (inv_label_pvt1.get_uom_code(
1118 rt.organization_id
1119 , rsl.item_id
1120 , rsl.unit_of_measure
1121 )
1122 )
1123 , (inv_label_pvt1.get_uom2_code(
1124 rt.organization_id
1125 , rsl.item_id
1126 )
1127 ) -- bug 4373856
1128 , rsl.item_revision
1129 , pha.segment1
1130 , rsh.shipment_num
1131 , pol.line_num
1132 , pll.quantity
1133 , rsl.vendor_item_num
1134 , pov.vendor_id
1135 , pov.vendor_name
1136 , pvs.vendor_site_id
1137 , pvs.vendor_site_code
1138 , ppf.full_name
1139 , hrl1.location_code
1140 , hrl2.location_code
1141 , pll.note_to_receiver
1142 , rrh.routing_name
1143 , rsl.item_description
1144 , rt.subinventory
1145 , rt.locator_id
1146 -- Bug 4516067, to improve performance, query the base table directly
1147 --, WOJV.WIP_ENTITY_NAME
1148 --, WOJV.DESCRIPTION
1149 , we.wip_entity_name
1150 , wdj.description
1151 , RT.WIP_OPERATION_SEQ_NUM
1152 , rt.DEPARTMENT_CODE
1153 , rt.BOM_RESOURCE_ID
1154 , wlpn.lpn_context
1155 , wlpn.lpn_id
1156 , rt.routing_header_id; --bug 4916450
1157
1158 /* Bug# 3238878
1159 Cursor to get the resource_code and departmetn_code */
1160
1161 CURSOR get_resource_dept_code_cur(p_resource_id NUMBER) IS
1162 SELECT br.resource_code
1163 ,bd.department_code
1164 FROM bom_resources br
1165 ,bom_department_resources bdr
1166 ,bom_departments bd
1167 WHERE br.resource_id = p_resource_id
1168 AND bdr.resource_id = p_resource_id
1169 AND bd.department_id = bdr.department_id
1170 GROUP BY br.resource_code
1171 ,bd.department_code;
1172
1173
1174 -- For transactions based on mmtt
1175 -- obtain item and lot information from mmtt and mtlt
1176
1177 -- For transactions based on mmtt
1178 -- obtain item and lot information from mmtt and mtlt
1179 -- Fix bug 2308273: Miscellaneous receipt(13) is calling label printing through TM
1180 -- but when label printing is called, the TM has not processed the LOT information into
1181 -- the mtl_lot_numbers table from the mtl_transactions_lot_temp. So for misc.receipts into
1182 -- a new lot, the lot number detailed information is taken from the mtl_transactions_lot_temp
1183 -- since the mtl_lot_numbers doesn't have the Lot number yet.
1184 CURSOR mmtt_material_receipt_cur IS
1185 SELECT mmtt.inventory_item_id
1186 , mmtt.organization_id
1187 , mtlt.lot_number
1188 , mmtt.cost_group_id
1189 , mmtt.project_id
1190 , mmtt.task_id
1191 , ABS(NVL(mtlt.transaction_quantity, mmtt.transaction_quantity)) quantity
1192 , mmtt.transaction_uom
1193 , ABS(NVL(mtlt.secondary_quantity, mmtt.secondary_transaction_quantity)) secondary_quantity -- invconv changes
1194 , mmtt.secondary_uom_code -- invconv changes
1195 , mmtt.revision
1196 , -- Added for Bug 2308273
1197 mtlt.lot_attribute_category
1198 , mtlt.c_attribute1
1199 , mtlt.c_attribute2
1200 , mtlt.c_attribute3
1201 , mtlt.c_attribute4
1202 , mtlt.c_attribute5
1203 , mtlt.c_attribute6
1204 , mtlt.c_attribute7
1205 , mtlt.c_attribute8
1206 , mtlt.c_attribute9
1207 , mtlt.c_attribute10
1208 , mtlt.c_attribute11
1209 , mtlt.c_attribute12
1210 , mtlt.c_attribute13
1211 , mtlt.c_attribute14
1212 , mtlt.c_attribute15
1213 , mtlt.c_attribute16
1214 , mtlt.c_attribute17
1215 , mtlt.c_attribute18
1216 , mtlt.c_attribute19
1217 , mtlt.c_attribute20
1218 , mtlt.d_attribute1
1219 , mtlt.d_attribute2
1220 , mtlt.d_attribute3
1221 , mtlt.d_attribute4
1222 , mtlt.d_attribute5
1223 , mtlt.d_attribute6
1224 , mtlt.d_attribute7
1225 , mtlt.d_attribute8
1226 , mtlt.d_attribute9
1227 , mtlt.d_attribute10
1228 , mtlt.n_attribute1
1229 , mtlt.n_attribute2
1230 , mtlt.n_attribute3
1231 , mtlt.n_attribute4
1232 , mtlt.n_attribute5
1233 , mtlt.n_attribute6
1234 , mtlt.n_attribute7
1235 , mtlt.n_attribute8
1236 , mtlt.n_attribute9
1237 , mtlt.n_attribute10
1238 , mtlt.territory_code
1239 , mtlt.grade_code
1240 , mtlt.origination_date
1241 , mtlt.date_code
1242 , mtlt.change_date
1243 , mtlt.age
1244 , mtlt.retest_date
1245 , mtlt.maturity_date
1246 , mtlt.item_size
1247 , mtlt.color
1248 , mtlt.volume
1249 , mtlt.volume_uom
1250 , mtlt.place_of_origin
1251 , mtlt.best_by_date
1252 , mtlt.LENGTH
1253 , mtlt.length_uom
1254 , mtlt.recycled_content
1255 , mtlt.thickness
1256 , mtlt.thickness_uom
1257 , mtlt.width
1258 , mtlt.width_uom
1259 , mtlt.curl_wrinkle_fold
1260 , mtlt.vendor_name
1261 -- End Bug 2308273
1262 , mmtt.subinventory_code
1263 , mmtt.locator_id
1264 , we.wip_entity_name -- Fix For Bug: 4907062
1265 , we.description -- Fix For Bug: 4907062
1266 , mtlt.parent_lot_number -- added for inconv fabdi start
1267 , mtlt.expiration_action_date
1268 , mtlt.origination_type
1269 , mtlt.hold_date
1270 , mtlt.expiration_action_code
1271 , mtlt.supplier_lot_number -- invconv end
1272 FROM mtl_material_transactions_temp mmtt
1273 ,mtl_transaction_lots_temp mtlt
1274 ,wip_entities we -- Fix For Bug: 4907062
1275 WHERE mtlt.transaction_temp_id(+) = mmtt.transaction_temp_id
1276 AND mmtt.transaction_temp_id = p_transaction_id
1277 AND we.wip_entity_id(+) = mmtt.transaction_source_id; -- Fix For Bug: 4907062
1278 /* Outer join has been added to fetch the data while performing the Misc. Alias/Receipt
1279 business flow */
1280
1281
1282 -- Bug Fix for bug 2251686
1283 -- If content lpn_id in MMTT is populated, we have to get the
1284 -- material info from WMS_LPN_CONTENTS
1285 -- New Union to this table has been added
1286 CURSOR mmtt_material_cur IS
1287 SELECT mmtt.inventory_item_id inventory_item_id
1288 , mmtt.organization_id organization_id
1289 , mtlt.lot_number lot_number
1290 , mmtt.cost_group_id cost_group_id
1291 , mmtt.transfer_cost_group_id xfr_cost_group_id /* Added for the bug # 4686024 */
1292 , mmtt.project_id project_id
1293 , mmtt.task_id task_id
1294 , ABS(NVL(mtlt.transaction_quantity, mmtt.transaction_quantity)) quantity
1295 , mmtt.transaction_uom uom
1296 , mmtt.revision revision
1297 , mmtt.subinventory_code
1298 , mmtt.transfer_subinventory
1299 , mmtt.locator_id
1300 , mmtt.transfer_to_location
1301 , mmtt.secondary_uom_code -- added for invconv
1302 , ABS(NVL(mtlt.secondary_quantity, mmtt.secondary_transaction_quantity)) -- added for invconv
1303 FROM mtl_material_transactions_temp mmtt, mtl_transaction_lots_temp mtlt
1304 WHERE mtlt.transaction_temp_id(+) = mmtt.transaction_temp_id
1305 AND mmtt.transaction_temp_id = p_transaction_id
1306 AND mmtt.content_lpn_id IS NULL
1307 UNION ALL
1308 SELECT wlc.inventory_item_id inventory_item_id
1309 , wlc.organization_id organization_id
1310 , wlc.lot_number lot_number
1311 , wlc.cost_group_id cost_group_id
1312 , mmtt.transfer_cost_group_id xfr_cost_group_id /* Added for the bug # 4686024 */
1313 , mmtt.project_id project_id
1314 , mmtt.task_id task_id
1315 , wlc.quantity quantity
1316 , wlc.uom_code uom
1317 , wlc.revision revision
1318 , mmtt.subinventory_code
1319 , mmtt.transfer_subinventory
1320 , mmtt.locator_id
1321 , mmtt.transfer_to_location
1322 , wlc.secondary_uom_code -- added for invconv
1323 , wlc.secondary_quantity -- added for invconv
1324 FROM wms_lpn_contents wlc, mtl_material_transactions_temp mmtt
1325 WHERE mmtt.transaction_temp_id = p_transaction_id
1326 AND mmtt.content_lpn_id IS NOT NULL
1327 AND mmtt.content_lpn_id = wlc.parent_lpn_id;
1328
1329 -- Bug fix for 2356935
1330 -- create new cursor for Inventory putaway (30)
1331 -- If putaway to a LPN-controlled location, the content information is in WLC
1332 -- If putaway to a non LPN-controlled location, TM will do a unpack and create
1333 -- multiple MMTT record for each content , with the same transaction_header_id
1334 -- of the original MMTT line
1335 -- Therefore, mmtt_material_cur will not work for this situation
1336 CURSOR inv_putaway_material_cur IS
1337 SELECT mmtt.inventory_item_id inventory_item_id
1338 , mmtt.organization_id organization_id
1339 , mtlt.lot_number lot_number
1340 , mmtt.cost_group_id cost_group_id
1341 , mmtt.project_id project_id
1342 , mmtt.task_id task_id
1343 , ABS(NVL(mtlt.transaction_quantity, mmtt.transaction_quantity)) quantity
1344 , ABS(NVL(mtlt.SECONDARY_QUANTITY, mmtt.SECONDARY_TRANSACTION_QUANTITY)) secondary_quantity -- fabdi bug 4387144
1345 , mmtt.transaction_uom uom
1346 , mmtt.SECONDARY_UOM_CODE secondary_uom -- fabdi bug 4387144
1347 , mmtt.revision revision
1348 , mmtt.transfer_subinventory
1349 , mmtt.transfer_to_location
1350 FROM mtl_material_transactions_temp mmtt
1351 , mtl_transaction_lots_temp mtlt
1352 , mtl_material_transactions_temp mmtt_orgin
1353 WHERE mtlt.transaction_temp_id(+) = mmtt.transaction_temp_id
1354 AND mmtt.transaction_header_id = mmtt_orgin.transaction_header_id
1355 AND mmtt.transaction_temp_id <> mmtt_orgin.transaction_temp_id
1356 AND mmtt_orgin.content_lpn_id IS NOT NULL
1357 AND mmtt_orgin.transaction_temp_id = p_transaction_id
1358 UNION ALL
1359 SELECT mmtt.inventory_item_id inventory_item_id
1360 , mmtt.organization_id organization_id
1361 , mtlt.lot_number lot_number
1362 , mmtt.cost_group_id cost_group_id
1363 , mmtt.project_id project_id
1364 , mmtt.task_id task_id
1365 , ABS(NVL(mtlt.transaction_quantity, mmtt.transaction_quantity)) quantity
1366 , ABS(NVL(mtlt.SECONDARY_QUANTITY , mmtt.SECONDARY_TRANSACTION_QUANTITY)) secondary_quantity -- fabdi bug 4387144
1367 , mmtt.transaction_uom uom
1368 , mmtt.SECONDARY_UOM_CODE secondary_uom -- fabdi bug 4387144
1369 , mmtt.revision revision
1370 , mmtt.transfer_subinventory
1371 , mmtt.transfer_to_location
1372 FROM mtl_material_transactions_temp mmtt, mtl_transaction_lots_temp mtlt
1373 WHERE mtlt.transaction_temp_id(+) = mmtt.transaction_temp_id
1374 AND mmtt.content_lpn_id IS NULL
1375 AND mmtt.transaction_temp_id = p_transaction_id;
1376
1377 -- Bug 2342737 : Print Material Label for Pack/Unpack/Split LPN.
1378 -- This call to label printing is from the TM and so the LPN is already packed when label printing is called.
1379 -- The absence of the outer join in the 2nd, 3rd and 4th sql ensures that they return records only for
1380 -- specific cases.
1381 CURSOR material_lpn_cur IS
1382 -- This part of the cursor returns all the items unpacked loose from an LPN.
1383 SELECT mmtt.inventory_item_id inventory_item_id
1384 , mmtt.organization_id organization_id
1385 , mtlt.lot_number lot_number
1386 , mmtt.cost_group_id cost_group_id
1387 , mmtt.project_id project_id
1388 , mmtt.task_id task_id
1389 , ABS(NVL(mtlt.transaction_quantity, mmtt.transaction_quantity)) quantity
1390 , mmtt.transaction_uom uom
1391 , mmtt.revision revision
1392 , mmtt.subinventory_code
1393 , mmtt.transfer_subinventory
1394 , mmtt.locator_id
1395 , mmtt.transfer_to_location
1396 , ABS(NVL(mtlt.secondary_quantity, mmtt.secondary_transaction_quantity)) secondary_quantity -- invconv changes
1397 , mmtt.secondary_uom_code -- invconv changes
1398 FROM mtl_material_transactions_temp mmtt, mtl_transaction_lots_temp mtlt
1399 WHERE mtlt.transaction_temp_id(+) = mmtt.transaction_temp_id
1400 AND mmtt.transfer_lpn_id IS NULL
1401 AND mmtt.content_lpn_id IS NULL
1402 AND mmtt.transaction_temp_id = p_transaction_id
1403 UNION ALL
1404 -- This part of the cursor returns the content_lpn_id unpacked from an LPN.
1405 SELECT wlc.inventory_item_id inventory_item_id
1406 , wlc.organization_id organization_id
1407 , wlc.lot_number lot_number
1408 , wlc.cost_group_id cost_group_id
1409 , mmtt.project_id project_id
1410 , mmtt.task_id task_id
1411 , wlc.quantity quantity
1412 , wlc.uom_code uom
1413 , wlc.revision revision
1414 , mmtt.subinventory_code
1415 , mmtt.transfer_subinventory
1416 , mmtt.locator_id
1417 , mmtt.transfer_to_location
1418 , wlc.secondary_quantity -- added for invconv
1419 , wlc.secondary_uom_code -- added for invconv
1420 FROM wms_lpn_contents wlc, mtl_material_transactions_temp mmtt
1421 WHERE mmtt.transaction_temp_id = p_transaction_id
1422 AND mmtt.content_lpn_id = wlc.parent_lpn_id
1423 UNION ALL
1424 -- This part of the cursor is for 2 cases. Items unpacked from an LPN and packed into another LPN AND
1425 -- for loose Items packed into an existing or loose LPN.
1426 SELECT wlc.inventory_item_id inventory_item_id
1427 , wlc.organization_id organization_id
1428 , wlc.lot_number lot_number
1429 , wlc.cost_group_id cost_group_id
1430 , mmtt.project_id project_id
1431 , mmtt.task_id task_id
1432 , wlc.quantity quantity
1433 , wlc.uom_code uom
1434 , wlc.revision revision
1435 , mmtt.subinventory_code
1436 , mmtt.transfer_subinventory
1437 , mmtt.locator_id
1438 , mmtt.transfer_to_location
1439 , wlc.secondary_quantity -- added for invconv
1440 , wlc.secondary_uom_code -- added for invconv
1441 FROM wms_lpn_contents wlc, mtl_material_transactions_temp mmtt
1442 WHERE mmtt.transaction_temp_id = p_transaction_id
1443 AND mmtt.transfer_lpn_id = wlc.parent_lpn_id
1444 UNION ALL
1445 -- This part of the cursor is for all cases except loose items being packed into an existing/new LPN.
1446 SELECT wlc.inventory_item_id inventory_item_id
1447 , wlc.organization_id organization_id
1448 , wlc.lot_number lot_number
1449 , wlc.cost_group_id cost_group_id
1450 , mmtt.project_id project_id
1451 , mmtt.task_id task_id
1452 , wlc.quantity quantity
1453 , wlc.uom_code uom
1454 , wlc.revision revision
1455 , mmtt.subinventory_code
1456 , mmtt.transfer_subinventory
1457 , mmtt.locator_id
1458 , mmtt.transfer_to_location
1459 , wlc.secondary_quantity -- added for invconv
1460 , wlc.secondary_uom_code -- added for invconv
1461 FROM wms_lpn_contents wlc, mtl_material_transactions_temp mmtt
1462 WHERE mmtt.transaction_temp_id = p_transaction_id
1463 AND mmtt.lpn_id = wlc.parent_lpn_id;
1464
1465 -- Packaging/Cartonization Cursors
1466 /*Bug 3639762 */
1467 CURSOR c_get_pkg_items_content IS
1468 SELECT wlc.organization_id
1469 , wlc.inventory_item_id
1470 , wlc.revision
1471 , wlc.lot_number
1472 , SUM(wlc.quantity)
1473 FROM wms_lpn_contents wlc, WMS_LICENSE_PLATE_NUMBERS wlpn
1474 WHERE wlpn.OUTERMOST_LPN_ID = p_transaction_id
1475 and wlc.parent_lpn_id = wlpn.lpn_id
1476 GROUP BY wlc.organization_id
1477 , wlc.inventory_item_id
1478 , wlc.revision
1479 , wlc.lot_number
1480 /* Union Clause added to fetch the details from mmtt for pick release transactions for cartonization flow
1481 as a part of Bug#4305501*/
1482 UNION
1483 SELECT mmtt.organization_id
1484 , mmtt.inventory_item_id
1485 , mmtt.revision
1486 , mtlt.lot_number
1487 , SUM(mmtt.primary_quantity) quantity
1488 FROM mtl_material_transactions_temp mmtt
1489 , mtl_transaction_lots_temp mtlt
1490 WHERE mmtt.cartonization_id = p_transaction_id
1491 AND mtlt.transaction_temp_id(+) = mmtt.transaction_temp_id
1492 GROUP BY mmtt.organization_id
1493 , mmtt.inventory_item_id
1494 , mmtt.revision
1495 , mtlt.lot_number;
1496 -- End Packaging/Cartonization Cursors
1497
1498
1499 -- For business_flow_code of Cross Dock, the delivery_detail_id is passed.
1500 CURSOR wdd_material_cur IS
1501 SELECT wdd1.inventory_item_id inventory_item_id
1502 , wdd1.organization_id organization_id
1503 , wdd1.lot_number lot_number
1504 , NVL(wlpn.cost_group_id, 0) cost_group_id
1505 , NVL(wdd1.project_id, 0) project_id
1506 , NVL(wdd1.task_id, 0) task_id
1507 , wdd1.requested_quantity quantity
1508 , wdd1.requested_quantity_uom uom
1509 , wdd1.revision revision
1510 , wdd1.subinventory
1511 , wdd1.locator_id
1512 FROM wsh_delivery_details wdd1
1513 , wsh_delivery_details wdd2
1514 , wsh_delivery_assignments_v wda
1515 , wms_license_plate_numbers wlpn
1516 WHERE wdd1.delivery_detail_id = wda.delivery_detail_id
1517 AND wdd2.delivery_detail_id = wda.parent_delivery_detail_id
1518 AND wlpn.lpn_id(+) = wdd2.lpn_id
1519 AND wdd2.delivery_detail_id = p_transaction_id;
1520
1521 -- Fix bug 2167545, problem 3, need to change the above cusror.
1522 -- the lpn_id is not on the WDD record that has inventory_item_id information,
1523 -- it is on the other parent WDD record. Changed to following
1524 CURSOR wda_material_cur IS
1525 SELECT wdd1.inventory_item_id inventory_item_id
1526 , wdd1.organization_id organization_id
1527 , wdd1.lot_number lot_number
1528 , NVL(wlpn.cost_group_id, 0) cost_group_id
1529 , NVL(wdd1.project_id, 0) project_id
1530 , NVL(wdd1.task_id, 0) task_id
1531 -- Bug - 4193950, requested_quantity is replaced with shipped_quantity.
1532 , wdd1.shipped_quantity quantity --, wdd1.requested_quantity quantity
1533 , wdd1.requested_quantity_uom uom
1534 , wdd1.revision revision
1535 , wdd1.subinventory
1536 , wdd1.locator_id
1537 FROM wsh_delivery_details wdd1
1538 , wsh_delivery_assignments_v wda
1539 , wsh_new_deliveries wnd
1540 , wms_license_plate_numbers wlpn
1541 , wsh_delivery_details wdd2
1542 WHERE wda.delivery_id = wnd.delivery_id
1543 AND wdd1.delivery_detail_id = wda.delivery_detail_id
1544 AND wdd2.delivery_detail_id = wda.parent_delivery_detail_id
1545 AND wdd1.inventory_item_id IS NOT NULL
1546 AND wlpn.lpn_id(+) = wdd2.lpn_id
1547 AND wnd.delivery_id = p_transaction_id;
1548
1549 -- For business_flow_code of WIP Completion(26), the transaction temp id is passed.
1550 -- Bug 2825748 : Material Label Is Not Printed On WIP Ccompletion.
1551 -- Bug 3823140, WIP Completion will use cursor mmtt_material_receipt_cur to get new lot information from MTLT
1552 /*
1553 CURSOR wip_material_cur IS
1554 SELECT mmtt.inventory_item_id
1555 , mmtt.organization_id
1556 , mtlt.lot_number
1557 , mmtt.cost_group_id
1558 , mmtt.project_id
1559 , mmtt.task_id
1560 , ABS(NVL(mtlt.transaction_quantity, mmtt.transaction_quantity))
1561 , mmtt.transaction_uom
1562 , mmtt.revision
1563 , mmtt.subinventory_code
1564 , mmtt.locator_id
1565 FROM mtl_material_transactions_temp mmtt, mtl_transaction_lots_temp mtlt
1566 WHERE mtlt.transaction_temp_id(+) = mmtt.transaction_temp_id
1567 AND mmtt.transaction_temp_id = p_transaction_id;
1568 */
1569 -- For business_flow_code of Manufacturing Cross-Dock
1570 CURSOR wip_material_cur IS
1571 SELECT mmtt.inventory_item_id
1572 , mmtt.organization_id
1573 , mtlt.lot_number
1574 , mmtt.cost_group_id
1575 , mmtt.project_id
1576 , mmtt.task_id
1577 , ABS(NVL(mtlt.transaction_quantity, mmtt.transaction_quantity))
1578 , mmtt.transaction_uom
1579 , mmtt.revision
1580 , mmtt.subinventory_code
1581 , mmtt.locator_id
1582 FROM mtl_material_transactions_temp mmtt, mtl_transaction_lots_temp mtlt
1583 WHERE mtlt.transaction_temp_id(+) = mmtt.transaction_temp_id
1584 AND mmtt.transaction_temp_id = p_transaction_id;
1585
1586 -- For business flow code of 33, the MMTT, MTI or MOL id is passed
1587 -- Depending on the txn identifier being passed,one of the
1588 -- following 3 flow csrs will be called
1589
1590 CURSOR flow_material_curs_mmtt IS
1591 SELECT mmtt.inventory_item_id inventory_item_id
1592 , mmtt.organization_id organization_id
1593 , mtlt.lot_number lot_number
1594 , mmtt.cost_group_id cost_group_id
1595 , mmtt.project_id project_id
1596 , mmtt.task_id task_id
1597 , NVL(mtlt.transaction_quantity, mmtt.transaction_quantity) quantity
1598 , mmtt.transaction_uom uom
1599 , mmtt.revision revision
1600 , mmtt.subinventory_code
1601 , mmtt.locator_id
1602 FROM mtl_material_transactions_temp mmtt, mtl_transaction_lots_temp mtlt
1603 WHERE mmtt.transaction_temp_id = mtlt.transaction_temp_id(+)
1604 AND mmtt.transaction_temp_id = p_transaction_id;
1605
1606 CURSOR flow_material_curs_mti IS
1607 SELECT mti.inventory_item_id inventory_item_id
1608 , mti.organization_id organization_id
1609 , mtil.lot_number lot_number
1610 , mti.cost_group_id cost_group_id
1611 , mti.project_id project_id
1612 , mti.task_id task_id
1613 , NVL(mtil.transaction_quantity, mti.transaction_quantity) quantity
1614 , mti.transaction_uom uom
1615 , mti.revision revision
1616 , mti.subinventory_code
1617 , mti.locator_id
1618 FROM mtl_transactions_interface mti, mtl_transaction_lots_interface mtil
1619 WHERE mti.transaction_interface_id = mtil.transaction_interface_id(+)
1620 AND mti.transaction_interface_id = p_transaction_id;
1621
1622 CURSOR flow_material_curs_mol IS
1623 SELECT mtrl.inventory_item_id inventory_id
1624 , mtrl.organization_id organization_id
1625 , mtrl.lot_number lot_number
1626 , mtrl.from_cost_group_id cost_group_id
1627 , mtrl.project_id project_id
1628 , mtrl.task_id task_id
1629 , mtrl.quantity quantity
1630 , mtrl.uom_code uom
1631 , mtrl.revision revision
1632 , mtrl.from_subinventory_code
1633 , mtrl.from_locator_id
1634 FROM mtl_txn_request_lines mtrl
1635 WHERE mtrl.line_id = p_transaction_id;
1636
1637 -- End of Flow csr
1638
1639
1640
1641 -- To get org type.
1642 CURSOR rti_get_org_cur IS
1643 SELECT to_organization_id
1644 FROM rcv_transactions_interface rti
1645 WHERE rti.interface_transaction_id = p_transaction_id;
1646
1647
1648 /*Bug# 3238878
1649 To get Org Type for Patchset J and above */
1650 CURSOR rt_get_org_cur IS
1651 SELECT organization_id
1652 FROM rcv_transactions rt
1653 WHERE rt.group_id = p_transaction_id
1654 and ( (p_label_type_info.business_flow_code = 1 AND rt.transaction_type = 'RECEIVE')
1655 OR (p_label_type_info.business_flow_code = 2 AND rt.transaction_type in ('ACCEPT', 'REJECT') )
1656 OR (p_label_type_info.business_flow_code = 3 AND rt.transaction_type = 'DELIVER')
1657 OR (p_label_type_info.business_flow_code = 4 AND rt.transaction_type = 'DELIVER')
1658 );
1659 /*End of Bug# 3238878 */
1660
1661 /* The following cursor is modified for the bug # 4686024 */
1662
1663 /* CURSOR c_cost_group IS
1664 SELECT cost_group
1665 FROM cst_cost_groups
1666 WHERE cost_group_id = l_cost_group_id; */
1667
1668 CURSOR c_cost_group(p_cost_group_id NUMBER) IS
1669 SELECT cost_group
1670 FROM cst_cost_groups
1671 WHERE cost_group_id = p_cost_group_id;
1672
1673
1674 -- Fix For Bug: 4907062
1675 -- a) Included Project Number in the cursor
1676 -- b) Taken the project details from pjm_projects_mtll_v instead of pa_projects
1677 CURSOR c_project IS
1678 SELECT project_name, project_number
1679 FROM pjm_projects_mtll_v --pa_projects
1680 WHERE project_id = l_project_id;
1681
1682
1683 -- Fix For Bug: 4907062
1684 -- Included Task Number in the cursor
1685 CURSOR c_task IS
1686 SELECT task_name, task_number
1687 FROM pa_tasks
1688 WHERE task_id = l_task_id;
1689
1690 CURSOR c_material_cur(
1691 p_organization_id NUMBER
1692 , p_inventory_item_id NUMBER
1693 , p_lot_number VARCHAR2
1694 ) IS
1695 SELECT msik.concatenated_segments item
1696 , NVL(msik.description, l_item_description) item_description
1697 , mp.organization_code ORGANIZATION
1698 , l_revision revision
1699 , l_quantity quantity
1700 , l_uom uom
1701 , mln.lot_number lot_number
1702 , NVL(l_parent_lot_number , mln.parent_lot_number) parent_lot_number -- invconv changes
1703 , TO_CHAR(NVL(l_expiration_action_date, mln.expiration_action_date), g_date_format_mask) expiration_action_date
1704 , NVL(l_expiration_action_code , mln.expiration_action_code) expiration_action_code
1705 , l_secondary_transaction_qty secondary_quantity
1706 , l_secondary_uom_code secondary_uom
1707 , TO_CHAR(NVL(l_hold_date, mln.hold_date), g_date_format_mask) hold_date
1708 , NVL(l_origination_type , mln.origination_type) origination_type
1709 , NVL(l_supplier_lot_number, mln.supplier_lot_number) supplier_lot_number -- invconv changes
1710 , mmsvl.status_code lot_status
1711 , TO_CHAR(mln.expiration_date, g_date_format_mask) lot_expiration_date
1712 , -- Added for Bug 2795525,
1713 NVL(l_attribute_category, mln.attribute_category) lot_attribute_category
1714 , NVL(l_c_attribute1, mln.c_attribute1) lot_c_attribute1
1715 , NVL(l_c_attribute2, mln.c_attribute2) lot_c_attribute2
1716 , NVL(l_c_attribute3, mln.c_attribute3) lot_c_attribute3
1717 , NVL(l_c_attribute4, mln.c_attribute4) lot_c_attribute4
1718 , NVL(l_c_attribute5, mln.c_attribute5) lot_c_attribute5
1719 , NVL(l_c_attribute6, mln.c_attribute6) lot_c_attribute6
1720 , NVL(l_c_attribute7, mln.c_attribute7) lot_c_attribute7
1721 , NVL(l_c_attribute8, mln.c_attribute8) lot_c_attribute8
1722 , NVL(l_c_attribute9, mln.c_attribute9) lot_c_attribute9
1723 , NVL(l_c_attribute10, mln.c_attribute10) lot_c_attribute10
1724 , NVL(l_c_attribute11, mln.c_attribute11) lot_c_attribute11
1725 , NVL(l_c_attribute12, mln.c_attribute12) lot_c_attribute12
1726 , NVL(l_c_attribute13, mln.c_attribute13) lot_c_attribute13
1727 , NVL(l_c_attribute14, mln.c_attribute14) lot_c_attribute14
1728 , NVL(l_c_attribute15, mln.c_attribute15) lot_c_attribute15
1729 , NVL(l_c_attribute16, mln.c_attribute16) lot_c_attribute16
1730 , NVL(l_c_attribute17, mln.c_attribute17) lot_c_attribute17
1731 , NVL(l_c_attribute18, mln.c_attribute18) lot_c_attribute18
1732 , NVL(l_c_attribute19, mln.c_attribute19) lot_c_attribute19
1733 , NVL(l_c_attribute20, mln.c_attribute20) lot_c_attribute20
1734 , TO_CHAR(NVL(l_d_attribute1, mln.d_attribute1), g_date_format_mask) lot_d_attribute1
1735 , -- Added for Bug 2795525,
1736 TO_CHAR(NVL(l_d_attribute2, mln.d_attribute2), g_date_format_mask) lot_d_attribute2
1737 , -- Added for Bug 2795525,
1738 TO_CHAR(NVL(l_d_attribute3, mln.d_attribute3), g_date_format_mask) lot_d_attribute3
1739 , -- Added for Bug 2795525,
1740 TO_CHAR(NVL(l_d_attribute4, mln.d_attribute4), g_date_format_mask) lot_d_attribute4
1741 , -- Added for Bug 2795525,
1742 TO_CHAR(NVL(l_d_attribute5, mln.d_attribute5), g_date_format_mask) lot_d_attribute5
1743 , -- Added for Bug 2795525,
1744 TO_CHAR(NVL(l_d_attribute6, mln.d_attribute6), g_date_format_mask) lot_d_attribute6
1745 , -- Added for Bug 2795525,
1746 TO_CHAR(NVL(l_d_attribute7, mln.d_attribute7), g_date_format_mask) lot_d_attribute7
1747 , -- Added for Bug 2795525,
1748 TO_CHAR(NVL(l_d_attribute8, mln.d_attribute8), g_date_format_mask) lot_d_attribute8
1749 , -- Added for Bug 2795525,
1750 TO_CHAR(NVL(l_d_attribute9, mln.d_attribute9), g_date_format_mask) lot_d_attribute9
1751 , -- Added for Bug 2795525,
1752 TO_CHAR(
1753 NVL(l_d_attribute10, mln.d_attribute10)
1754 , g_date_format_mask
1755 ) lot_d_attribute10
1756 , -- Added for Bug 2795525,
1757 NVL(l_n_attribute1, mln.n_attribute1) lot_n_attribute1
1758 , NVL(l_n_attribute2, mln.n_attribute2) lot_n_attribute2
1759 , NVL(l_n_attribute3, mln.n_attribute3) lot_n_attribute3
1760 , NVL(l_n_attribute4, mln.n_attribute4) lot_n_attribute4
1761 , NVL(l_n_attribute5, mln.n_attribute5) lot_n_attribute5
1762 , NVL(l_n_attribute6, mln.n_attribute6) lot_n_attribute6
1763 , NVL(l_n_attribute7, mln.n_attribute7) lot_n_attribute7
1764 , NVL(l_n_attribute8, mln.n_attribute8) lot_n_attribute8
1765 , NVL(l_n_attribute9, mln.n_attribute9) lot_n_attribute9
1766 , NVL(l_n_attribute10, mln.n_attribute10) lot_n_attribute10
1767 , NVL(l_territory_code, mln.territory_code) lot_country_of_origin
1768 , NVL(l_grade_code, mln.grade_code) lot_grade_code
1769 , TO_CHAR(
1770 NVL(l_origination_date, mln.origination_date)
1771 , g_date_format_mask
1772 ) lot_origination_date
1773 , -- Added for Bug 2795525,
1774 NVL(l_date_code, mln.date_code) lot_date_code
1775 , TO_CHAR(NVL(l_change_date, mln.change_date), g_date_format_mask) lot_change_date
1776 , -- Added for Bug 2795525,
1777 NVL(l_age, mln.age) lot_age
1778 , TO_CHAR(NVL(l_retest_date, mln.retest_date), g_date_format_mask) lot_retest_date
1779 , -- Added for Bug 2795525,
1780 TO_CHAR(
1781 NVL(l_maturity_date, mln.maturity_date)
1782 , g_date_format_mask
1783 ) lot_maturity_date
1784 , -- Added for Bug 2795525,
1785 NVL(l_item_size, mln.item_size) lot_item_size
1786 , NVL(l_color, mln.color) lot_color
1787 , NVL(l_volume, mln.volume) lot_volume
1788 , NVL(l_volume_uom, mln.volume_uom) lot_volume_uom
1789 , NVL(l_place_of_origin, mln.place_of_origin) lot_place_of_origin
1790 , TO_CHAR(NVL(l_best_by_date, mln.best_by_date), g_date_format_mask) lot_best_by_date
1791 , -- Added for Bug 2795525,
1792 NVL(l_length, mln.LENGTH) lot_length
1793 , NVL(l_length_uom, mln.length_uom) lot_length_uom
1794 , NVL(l_recycled_content, mln.recycled_content) lot_recycled_cont
1795 , NVL(l_thickness, mln.thickness) lot_thickness
1796 , NVL(l_thickness_uom, mln.thickness_uom) lot_thickness_uom
1797 , NVL(l_width, mln.width) lot_width
1798 , NVL(l_width_uom, mln.width_uom) lot_width_uom
1799 , NVL(l_curl_wrinkle_fold, mln.curl_wrinkle_fold) lot_curl
1800 , NVL(l_vendor_name, mln.vendor_name) lot_vendor
1801 , l_cost_group cost_group
1802 , poh.hazard_class item_hazard_class
1803 , msik.attribute_category item_attribute_category
1804 , msik.attribute1 item_attribute1
1805 , msik.attribute2 item_attribute2
1806 , msik.attribute3 item_attribute3
1807 , msik.attribute4 item_attribute4
1808 , msik.attribute5 item_attribute5
1809 , msik.attribute6 item_attribute6
1810 , msik.attribute7 item_attribute7
1811 , msik.attribute8 item_attribute8
1812 , msik.attribute9 item_attribute9
1813 , msik.attribute10 item_attribute10
1814 , msik.attribute11 item_attribute11
1815 , msik.attribute12 item_attribute12
1816 , msik.attribute13 item_attribute13
1817 , msik.attribute14 item_attribute14
1818 , msik.attribute15 item_attribute15
1819 , l_project_number project_number -- Fix For Bug: 4907062
1820 , l_project_name project
1821 , l_task_number task_number -- Fix For Bug: 4907062
1822 , l_task_name task
1823 , l_subinventory_code subinventory_code
1824 , wilk.concatenated_segments LOCATOR
1825 -- milk.concatenated_segments LOCATOR -- Modified for bug # 5015415
1826 FROM mtl_system_items_vl msik --Bug 5302715 changed from kfv to vl
1827 , mtl_lot_numbers mln
1828 , mtl_material_statuses_vl mmsvl
1829 , po_hazard_classes poh
1830 , mtl_parameters mp
1831 /*Commented for bug# 6334460 start
1832 , DUAL d
1833 Commented for bug# 6334460 end */
1834 , wms_item_locations_kfv wilk
1835 --, mtl_item_locations_kfv milk -- Modified for bug # 5015415
1836 /*Commented for bug# 6334460 start
1837 WHERE d.dummy = 'X'
1838 AND msik.concatenated_segments(+) <> NVL('@@@', d.dummy)
1839 Commented for bug# 6334460 End */
1840 WHERE msik.inventory_item_id(+) = p_inventory_item_id
1841 AND msik.organization_id(+) = p_organization_id
1842 AND mp.organization_id = p_organization_id
1843 AND mln.organization_id(+) = msik.organization_id
1844 AND mln.inventory_item_id(+) = msik.inventory_item_id
1845 AND mln.lot_number(+) = p_lot_number
1846 AND mmsvl.status_id(+) = mln.status_id
1847 AND poh.hazard_class_id(+) = msik.hazard_class_id
1848 AND wilk.organization_id(+) = msik.organization_id
1849 AND wilk.subinventory_code(+) = l_subinventory_code
1850 AND wilk.inventory_location_id(+) = l_locator_id;
1851
1852 /* The following conditions have been modified for bug # 5015415.
1853
1854 For PJM Org, Locator field in Material Label should not show the Project and task id's.
1855 This is because, the Project and Task Id's are not Bar code transactable.
1856 In mtl_item_locations_kfv, the cocatenated segments will have Project and
1857 Task Id's attached to it. Whereas in wms_item_locations_kfv, concatenated
1858 segments will have only the physical details (Row, Rack and Bin)
1859 and not the project and Task id's.
1860 AND milk.organization_id(+) = msik.organization_id
1861 AND milk.subinventory_code(+) = l_subinventory_code
1862 AND milk.inventory_location_id(+) = l_locator_id; */
1863
1864
1865 /* For Bug 4916450 defined the cursor pod_project_task */
1866 CURSOR pod_project_task IS
1867 SELECT DISTINCT pod.project_id, pod.task_id
1868 FROM po_distributions_all pod,
1869 rcv_transactions rt
1870 WHERE pod.po_header_id = rt.po_header_id
1871 AND pod.po_line_id = rt.po_line_id
1872 AND pod.line_location_id = rt.po_line_location_id
1873 AND pod.po_distribution_id = nvl(rt.po_distribution_id, pod.po_distribution_id)
1874 AND rt.group_id = p_transaction_id;
1875
1876 /* The following cursor has been added to fetch the PROJECT_REFERENCE_ENABLED value
1877 * from pjm_org_parameters table. The value 'Y' represents the PJM enabled org.
1878 * This field will be used to open the cursors that are required only for PJM org.
1879 */
1880
1881 CURSOR c_project_enabled(p_organization_id NUMBER) IS
1882 SELECT pop.project_reference_enabled
1883 FROM pjm_org_parameters pop
1884 WHERE pop.organization_id = p_organization_id;
1885
1886 l_is_pjm_org VARCHAR (1);
1887
1888
1889 --R12 PROJECT LABEL SET with RFID
1890
1891 CURSOR c_label_formats_in_set(p_format_set_id IN NUMBER) IS
1892 select wlfs.format_id label_format_id, wlf.label_entity_type --FOR SETS
1893 from wms_label_set_formats wlfs , wms_label_formats wlf
1894 where WLFS.SET_ID = p_format_set_id
1895 and wlfs.set_id = wlf.label_format_id
1896 and wlf.label_entity_type = 1
1897 AND WLF.DOCUMENT_ID = 1
1898 UNION --FOR FORMAT
1899 select label_format_id, nvl(wlf.label_entity_type,0) from wms_label_formats wlf
1900 where wlf.label_format_id = p_format_set_id
1901 and nvl(wlf.label_entity_type,0) = 0--for label formats only validation
1902 AND WLF.DOCUMENT_ID = 1 ;
1903
1904 --Start of fix for 4891916.
1905 --Added the cursor to fetch records from mcce at the
1906 --the time of cycle count entry for a particular entry
1907
1908 CURSOR mcce_material_cur IS
1909 SELECT mcce.inventory_item_id,
1910 mcce.organization_id,
1911 mcce.lot_number,
1912 mcce.cost_group_id,
1913 mcce.count_quantity_current,
1914 mcce.count_uom_current,
1915 mcce.revision,
1916 mcce.subinventory,
1917 mcce.locator_id,
1918 mcch.cycle_count_header_name,
1919 ppf.full_name requestor
1920 FROM mtl_cycle_count_headers mcch,
1921 mtl_cycle_count_entries mcce,
1922 per_people_f ppf
1923 WHERE mcce.cycle_count_entry_id = p_transaction_Id
1924 AND ppf.person_id(+) = mcce.counted_by_employee_id_current
1925 AND mcce.cycle_count_header_id=mcch.cycle_count_header_id;
1926
1927
1928 --Added this cursor to get details like cycle count header name
1929 --and counter for the entry for the label printed at the time of
1930 --cycle count approval
1931
1932 CURSOR cc_det_approval IS
1933 SELECT mcch.cycle_count_header_name,
1934 ppf.full_name requestor
1935 FROM mtl_cycle_count_headers mcch,
1936 mtl_cycle_count_entries mcce,
1937 per_people_f ppf,
1938 mtl_material_transactions_temp mmtt
1939 WHERE mmtt.transaction_temp_id= p_transaction_id
1940 AND mmtt.cycle_count_id = mcce.cycle_count_entry_id
1941 AND mcce.cycle_count_header_id = mcch.cycle_count_header_id
1942 AND ppf.person_id(+) = mcce.counted_by_employee_id_current;
1943
1944 --End of fix for Bug 4687964
1945
1946 l_selected_fields inv_label.label_field_variable_tbl_type;
1947 l_selected_fields_count NUMBER;
1948 l_return_status VARCHAR2(240);
1949 l_msg_count NUMBER;
1950 l_msg_data VARCHAR2(240);
1951 l_error_message VARCHAR2(240);
1952 l_api_status VARCHAR2(240);
1953 i NUMBER;
1954 l_transaction_id NUMBER := p_transaction_id;
1955 l_business_flow_code NUMBER := p_label_type_info.business_flow_code;
1956 l_count NUMBER;
1957 l_lpn_id NUMBER;
1958 l_label_info inv_label.label_type_rec;
1959 l_material_data LONG := '';
1960 l_label_format_id NUMBER := 0;
1961 l_label_format VARCHAR2(100) := NULL;
1962 l_printer VARCHAR2(30) := NULL;
1963 l_label_request_id NUMBER := 0;
1964 l_get_org_id NUMBER;
1965 l_is_wms_org BOOLEAN;
1966 l_material_input inv_label.material_label_input_tbl;
1967 l_material_input_index NUMBER;
1968 l_purchase_order po_headers_all.segment1%TYPE;
1969 rti_material_lpn_rec rti_material_lpn_cur%ROWTYPE;
1970 rti_material_mtlt_rec rti_material_mtlt_cur%ROWTYPE;
1971 l_po_line_number NUMBER;
1972 l_quantity_ordered NUMBER;
1973 l_supplier_part_number VARCHAR2(25);
1974 -- START of Bug fix for 3916663
1975 --l_supplier_name VARCHAR2(80);
1976 --l_supplier_site VARCHAR2(15);
1977 --l_requestor VARCHAR2(80);
1978 --l_deliver_to_location VARCHAR2(20);
1979 --l_location_code VARCHAR2(20);
1980 --l_note_to_receiver VARCHAR2(240);
1981
1982 -- Increased this variable size to the corresponding column size in the table.
1983 l_supplier_name po_vendors.VENDOR_NAME%TYPE;
1984 l_supplier_site po_vendor_sites.VENDOR_SITE_CODE%TYPE;
1985 l_requestor per_people_f.FULL_NAME%TYPE;
1986 l_deliver_to_location hr_locations_all.LOCATION_CODE%TYPE;
1987 l_location_code hr_locations_all.LOCATION_CODE%TYPE;
1988 l_note_to_receiver po_line_locations.NOTE_TO_RECEIVER%TYPE;
1989
1990 -- END of Bug fix for 3916663
1991 l_routing_name VARCHAR2(30);
1992 l_content_rec_index NUMBER := 0;
1993 l_printed_flag VARCHAR2(1);
1994 l_split_qty NUMBER := 0;
1995 l_label_counter NUMBER := 0;
1996 l_label_index NUMBER;
1997 --I cleanup, use l_prev_format_id to record the previous label format
1998 l_prev_format_id NUMBER;
1999 -- I cleanup, user l_prev_sub to record the previous subinventory
2000 --so that get_printer is not called if the subinventory is the same
2001 l_prev_sub VARCHAR2(30);
2002 -- a list of columns that are selected for format
2003 l_column_name_list LONG;
2004
2005 l_patch_level NUMBER;
2006 l_lpn_context Number;
2007 l_routing_header_id NUMBER; --bug 4916450
2008 l_next_project_id NUMBER; --bug 4916450
2009 l_next_task_id NUMBER; --bug 4916450
2010
2011
2012 l_gtin_epc_quantity NUMBER := 1;
2013 L_EPC_LOOP_COUNT NUMBER := 0;
2014 l_label_format_set_id NUMBER;
2015
2016 l_is_expense_item BOOLEAN := FALSE; /* Added for the bug # 4708752 */
2017 --Bug 4891916. Added the local variable to store the cycle count name
2018 l_cycle_count_name mtl_cycle_count_headers.cycle_count_header_name%TYPE;
2019
2020 v_material_cur c_material_cur%ROWTYPE; --Added for Bug 6504959
2021 l_moqd_quantity number;--added for bug 6646793
2022 l_mmtt_quantity number;--added for bug 6646793
2023 l_transaction_type NUMBER;--added for bug 6646793
2024
2025
2026 BEGIN
2027 l_debug := inv_label.l_debug;
2028 x_return_status := fnd_api.g_ret_sts_success;
2029 l_label_err_msg := NULL;
2030
2031 IF (l_debug = 1) THEN
2032 TRACE('**In PVT1: Material label**');
2033 TRACE(' Business_flow=' || p_label_type_info.business_flow_code
2034 || ', Transaction ID=' || p_transaction_id
2035 || ', Transaction Identifier=' || p_transaction_identifier
2036 );
2037 END IF;
2038
2039 l_transaction_identifier := p_transaction_identifier;
2040
2041 IF (inv_rcv_common_apis.g_inv_patch_level >= inv_rcv_common_apis.g_patchset_j)
2042 AND (inv_rcv_common_apis.g_po_patch_level >=inv_rcv_common_apis.g_patchset_j_po) THEN
2043 l_patch_level := 1;
2044 ELSE
2045 l_patch_level := 0;
2046 END IF;
2047 -- Get org for p_transaction_id
2048 IF p_label_type_info.business_flow_code IN (1, 2, 3, 4) THEN
2049
2050 /* Bug# 3238878 */
2051 IF((inv_rcv_common_apis.g_inv_patch_level >= inv_rcv_common_apis.g_patchset_j)
2052 AND (inv_rcv_common_apis.g_po_patch_level >=inv_rcv_common_apis.g_patchset_j_po)) THEN
2053 IF (l_debug = 1) THEN
2054 trace('Patchset J code ');
2055 END IF;
2056 OPEN rt_get_org_cur;
2057 FETCH rt_get_org_cur INTO l_get_org_id;
2058 IF rt_get_org_cur%NOTFOUND THEN
2059 IF (l_debug = 1) THEN
2060 TRACE(' No record found in RT to get the org for ID '|| p_transaction_id);
2061 END IF;
2062 CLOSE rt_get_org_cur;
2063 RETURN;
2064 ELSE
2065 CLOSE rt_get_org_cur;
2066 END IF;
2067 ELSE
2068 OPEN rti_get_org_cur;
2069 FETCH rti_get_org_cur INTO l_get_org_id;
2070
2071 IF rti_get_org_cur%NOTFOUND THEN
2072 IF (l_debug = 1) THEN
2073 TRACE( ' No record found in RTI to get the org for ID '|| p_transaction_id);
2074 END IF;
2075
2076 CLOSE rti_get_org_cur;
2077 RETURN;
2078 ELSE
2079 CLOSE rti_get_org_cur;
2080 END IF;
2081 END IF;
2082 /* End of Bug# 3238878 */
2083
2084 l_is_wms_org :=
2085 wms_install.check_install(
2086 x_return_status => l_return_status
2087 , x_msg_count => l_msg_count
2088 , x_msg_data => l_msg_data
2089 , p_organization_id => l_get_org_id
2090 );
2091
2092 IF l_return_status <> 'S' THEN
2093 fnd_message.set_name('WMS', 'WMS_INSTALL_CHECK_INSTALL_FAILED');
2094 fnd_msg_pub.ADD;
2095 RETURN;
2096 END IF;
2097
2098 IF (l_debug = 1) THEN
2099 IF (l_is_wms_org = TRUE) THEN
2100 TRACE(' Org is WMS enabled ');
2101 ELSE
2102 TRACE(' Org is INV enabled ');
2103 END IF;
2104 END IF;
2105 END IF;
2106
2107 -- Get l_inventory_item_id and l_lot_id
2108 IF (p_transaction_id IS NOT NULL) THEN -- Business flow + transaction_id passed.
2109 -- txn driven
2110 IF (p_label_type_info.business_flow_code IN (1,2,3,4)) THEN
2111 IF (l_debug = 1) THEN
2112 trace('business flow code is 1,2,3 or 4');
2113 END IF;
2114 IF l_patch_level = 1 THEN
2115
2116 IF (l_debug = 1) THEN
2117 TRACE('Patchset J code ');
2118 END IF;
2119 /* Patchset J - Use the new cursor rt_material_cur. This cursor replaces
2120 * RTI_MATERIAL_LPN_CUR and RTI_MATERIAL_MTLT_CUR in patchset J, due to receiving tables
2121 * changes. Also, earlier, receiving transaction records were created separately for
2122 * INV and WMS organizations, which is not the case now.
2123 * Open the cursor rt_material_cur. This cursor fetches data irrespective
2124 * of whether it is a WMS org or INV org.
2125 */
2126 -- Bug 4516067
2127 -- created new cursor for putaway and deliver
2128 -- Open rt_material_cur or rt_putaway_deliver_cur based on busienss flow code
2129 IF (p_label_type_info.business_flow_code IN (1,2)) THEN
2130 OPEN rt_material_cur;
2131 FETCH rt_material_cur INTO
2132 l_inventory_item_id
2133 , l_organization_id
2134 , l_lot_number
2135 , l_cost_group_id
2136 , l_project_id
2137 , l_task_id
2138 -- Added by joabraha for bug 3472150
2139 , l_receipt_number
2140 --
2141 , l_quantity
2142 , l_secondary_transaction_qty
2143 , l_uom
2144 , l_secondary_uom_code
2145 , l_revision
2146 , l_purchase_order
2147 , l_shipment_num
2148 , l_po_line_number
2149 , l_quantity_ordered
2150 , l_supplier_part_number
2151 , l_vendor_id
2152 , l_supplier_name
2153 , l_vendor_site_id
2154 , l_supplier_site
2155 , l_requestor
2156 , l_deliver_to_location
2157 , l_location_code
2158 , l_note_to_receiver
2159 , l_routing_name
2160 , l_item_description
2161 , l_subinventory_code
2162 , l_locator_id
2163 , l_wip_entity_name
2164 , l_wip_description
2165 , l_wip_op_seq_num
2166 , l_osp_dept_code
2167 , l_bom_resource_id
2168 , l_lpn_context
2169 , l_lpn_id
2170 , l_routing_header_id; --bug 4916450
2171
2172 IF rt_material_cur%NOTFOUND THEN
2173 IF (l_debug = 1) THEN
2174 TRACE(' No material found for this given Interface Transaction ID:' || p_transaction_id);
2175 END IF;
2176 CLOSE rt_material_cur;
2177 RETURN;
2178 END IF;
2179 ELSIF (p_label_type_info.business_flow_code IN (3,4)) THEN
2180 OPEN rt_putaway_deliver_cur;
2181 FETCH rt_putaway_deliver_cur INTO
2182 l_inventory_item_id
2183 , l_organization_id
2184 , l_lot_number
2185 , l_cost_group_id
2186 , l_project_id
2187 , l_task_id
2188 -- Added by joabraha for bug 3472150
2189 , l_receipt_number
2190 --
2191 , l_quantity
2192 , l_secondary_transaction_qty
2193 , l_uom
2194 , l_secondary_uom_code
2195 , l_revision
2196 , l_purchase_order
2197 , l_shipment_num
2198 , l_po_line_number
2199 , l_quantity_ordered
2200 , l_supplier_part_number
2201 , l_vendor_id
2202 , l_supplier_name
2203 , l_vendor_site_id
2204 , l_supplier_site
2205 , l_requestor
2206 , l_deliver_to_location
2207 , l_location_code
2208 , l_note_to_receiver
2209 , l_routing_name
2210 , l_item_description
2211 , l_subinventory_code
2212 , l_locator_id
2213 , l_wip_entity_name
2214 , l_wip_description
2215 , l_wip_op_seq_num
2216 , l_osp_dept_code
2217 , l_bom_resource_id
2218 , l_lpn_context
2219 , l_lpn_id
2220 , l_routing_header_id; --bug 4916450
2221
2222 IF rt_putaway_deliver_cur%NOTFOUND THEN
2223 IF (l_debug = 1) THEN
2224 TRACE(' No material found for this given Interface Transaction ID:' || p_transaction_id);
2225 END IF;
2226 CLOSE rt_putaway_deliver_cur;
2227 RETURN;
2228 END IF;
2229
2230 END IF;
2231
2232 OPEN get_resource_dept_code_cur(l_bom_resource_id);
2233 FETCH get_resource_dept_code_cur INTO l_bom_resource_code, l_osp_dept_code;
2234 IF get_resource_dept_code_cur%NOTFOUND THEN
2235 IF (l_debug = 1) THEN
2236 TRACE(' No Resource and Dept code found for Resource ID: ' || l_bom_resource_id);
2237 END IF;
2238 END IF;
2239 --CLOSE get_resource_dept_code_cur;
2240
2241 --l_receipt_number := inv_rcv_common_apis.g_rcv_global_var.receipt_num;
2242 IF (l_debug = 1) THEN
2243 TRACE(' Receipt Number: ' || l_receipt_number);
2244 END IF;
2245
2246 ELSE
2247 IF (l_debug = 1) THEN
2248 trace('NOT Patchset J code. Patch level < inv J or PO J');
2249 END IF;
2250 IF ((p_label_type_info.business_flow_code IN (1))
2251 AND (l_is_wms_org = TRUE)
2252 ) THEN
2253 --l_receipt_number := inv_rcv_common_apis.g_rcv_global_var.receipt_num;
2254 -- Receipt and Inspection, WMS org, obtaining the lot information
2255 -- from the wms_lpn_contents and the rest information from the
2256 -- rti record
2257 OPEN rti_material_lpn_cur;
2258 FETCH rti_material_lpn_cur INTO l_inventory_item_id
2259 , l_organization_id
2260 , l_lot_number
2261 , l_cost_group_id
2262 , l_project_id
2263 , l_task_id
2264 -- Added by joabraha for bug 3472150
2265 , l_receipt_number
2266 --
2267 , l_quantity
2268 , l_uom
2269 , l_revision
2270 , l_lpn_id
2271 , l_purchase_order
2272 , l_po_line_number
2273 , l_quantity_ordered
2274 , l_supplier_part_number
2275 , l_vendor_id
2276 , l_supplier_name
2277 , l_vendor_site_id
2278 , l_supplier_site
2279 , l_requestor
2280 , l_deliver_to_location
2281 , l_location_code
2282 , l_note_to_receiver
2283 , l_routing_name
2284 , l_item_description
2285 , l_subinventory_code
2286 , l_locator_id
2287 , l_wip_entity_name
2288 , l_wip_description
2289 , l_wip_op_seq_num
2290 , l_osp_dept_code
2291 , l_bom_resource_id;
2292
2293 IF rti_material_lpn_cur%NOTFOUND THEN
2294 IF (l_debug = 1) THEN
2295 TRACE(' No material found for this given Interface Transaction ID:' || p_transaction_id);
2296 END IF;
2297
2298 CLOSE rti_material_lpn_cur;
2299 RETURN;
2300 END IF;
2301
2302 --l_receipt_number := inv_rcv_common_apis.g_rcv_global_var.receipt_num;
2303 IF (l_debug = 1) THEN
2304 TRACE(' Receipt Number: ' || l_receipt_number);
2305 END IF;
2306
2307 ELSIF ((p_label_type_info.business_flow_code IN (2))
2308 AND (l_is_wms_org = TRUE)
2309 ) THEN
2310 -- Receipt and Inspection, WMS org, obtaining the lot information
2311 -- from the wms_lpn_contents and the rest information from the rti record
2312 --l_receipt_number := inv_rcv_common_apis.g_rcv_global_var.receipt_num;
2313 OPEN rti_material_lpn_inspec_cur;
2314 FETCH rti_material_lpn_inspec_cur INTO l_inventory_item_id
2315 , l_organization_id
2316 , l_lot_number
2317 , l_cost_group_id
2318 , l_project_id
2319 , l_task_id
2320 -- Added by joabraha for bug 3472150
2321 , l_receipt_number
2322 --
2323 , l_quantity
2324 , l_uom
2325 , l_revision
2326 , l_lpn_id
2327 , l_purchase_order
2328 , l_po_line_number
2329 , l_quantity_ordered
2330 , l_supplier_part_number
2331 , l_vendor_id
2332 , l_supplier_name
2333 , l_vendor_site_id
2334 , l_supplier_site
2335 , l_requestor
2336 , l_deliver_to_location
2337 , l_location_code
2338 , l_note_to_receiver
2339 , l_routing_name
2340 , l_item_description
2341 , l_subinventory_code
2342 , l_locator_id
2343 , l_wip_entity_name
2344 , l_wip_description
2345 , l_wip_op_seq_num
2346 , l_osp_dept_code
2347 , l_bom_resource_id;
2348
2349 IF rti_material_lpn_inspec_cur%NOTFOUND THEN
2350 IF (l_debug = 1) THEN
2351 TRACE(' No material found for this given Interface Transaction ID:' || p_transaction_id);
2352 END IF;
2353
2354 CLOSE rti_material_lpn_inspec_cur;
2355 RETURN;
2356 END IF;
2357
2358 --l_receipt_number := inv_rcv_common_apis.g_rcv_global_var.receipt_num;
2359 IF (l_debug = 1) THEN
2360 TRACE(' Receipt Number: ' || l_receipt_number);
2361 END IF;
2362
2363 ELSIF ((p_label_type_info.business_flow_code IN (4))
2364 AND (l_is_wms_org = TRUE)
2365 )
2366 OR ((p_label_type_info.business_flow_code IN (1, 2, 3))
2367 AND (l_is_wms_org = FALSE)
2368 ) THEN
2369 -- For putaway in WMS org and Receipt, Inspection, Delivery in INV org
2370 -- Obtain information from RTI and MTLT (if applicable)
2371 -- Receipt Inspection: No lot and seial information, print item information from RTI
2372 -- Delivery: RTI + MTLT
2373 --l_receipt_number := inv_rcv_common_apis.g_rcv_global_var.receipt_num;
2374 OPEN rti_material_mtlt_cur;
2375 FETCH rti_material_mtlt_cur --INTO rti_material_mtlt_rec;
2376 INTO l_inventory_item_id
2377 , l_revision
2378 , l_lot_number
2379 , l_organization_id
2380 , l_cost_group_id
2381 , l_project_id
2382 , l_task_id
2383 , l_quantity
2384 , l_uom
2385 , l_purchase_order
2386 , l_po_line_number
2387 , l_quantity_ordered
2388 , l_supplier_part_number
2389 , l_vendor_id
2390 , l_supplier_name
2391 , l_vendor_site_id
2392 , l_supplier_site
2393 , l_requestor
2394 , l_deliver_to_location
2395 , l_location_code
2396 , l_note_to_receiver
2397 , l_routing_name
2398 , l_item_description
2399 , l_subinventory_code
2400 , l_locator_id
2401 , l_wip_entity_name
2402 , l_wip_description
2403 , l_wip_op_seq_num
2404 , l_osp_dept_code
2405 , l_bom_resource_id;
2406
2407 IF rti_material_mtlt_cur%NOTFOUND THEN
2408 IF (l_debug = 1) THEN
2409 TRACE(' No material found for this given Interface Transaction ID:' || p_transaction_id);
2410 END IF;
2411
2412 CLOSE rti_material_mtlt_cur;
2413 RETURN;
2414 END IF;
2415 END IF;
2416 END IF; -- l-patch_level = 1
2417 ELSIF (p_label_type_info.business_flow_code IN (6)) THEN
2418 -- Cross Dock(6).
2419 -- Here in this case the delivery_detail_id is being passed.
2420 -- Delivery detail ID passed means that we just have to print serial label for the one
2421 -- delivery detail id and
2422 -- not all the delivery detail id's in the delivery.
2423 -- The cost group will be derived from the table wms_license_plate_numbers for the LPN
2424 -- stamped on the Delivery_detail_id.
2425 OPEN wdd_material_cur;
2426 FETCH wdd_material_cur INTO l_inventory_item_id
2427 , l_organization_id
2428 , l_lot_number
2429 , l_cost_group_id
2430 , l_project_id
2431 , l_task_id
2432 , l_quantity
2433 , l_uom
2434 , l_revision
2435 , l_subinventory_code
2436 , l_locator_id;
2437
2438 IF wdd_material_cur%NOTFOUND THEN
2439 IF (l_debug = 1) THEN
2440 TRACE(' No Material found for this given Delivery Detail ID:' || p_transaction_id);
2441 END IF;
2442 CLOSE wdd_material_cur;
2443 RETURN;
2444 -- Bug 3836623
2445 -- Can not close the cursor because there maybe more record available
2446 -- ELSE
2447 -- CLOSE wdd_material_cur;
2448 END IF;
2449 -- Fix bug 2308273: Miscellaneous receipt(13) is calling label printing through TM
2450 -- but when label printing is called, the TM has not processed the LOT information into
2451 -- the mtl_lot_numbers table from the mtl_transactions_lot_temp. So for misc.receipts into
2452 -- a new lot, the lot number detailed information is taken from the mtl_transactions_lot_temp.
2453
2454 -- Bug 3823140, For WIP Completion(26), it also needs to get the new lot information from MTLT. Therefore, it also uses cursor mmtt_material_receipt_cur.
2455 -- Commented out the use of wip_material_cur
2456
2457 ELSIF p_label_type_info.business_flow_code IN (13,26) THEN
2458 OPEN mmtt_material_receipt_cur;
2459 FETCH mmtt_material_receipt_cur INTO l_inventory_item_id
2460 , l_organization_id
2461 , l_lot_number
2462 , l_cost_group_id
2463 , l_project_id
2464 , l_task_id
2465 , l_quantity
2466 , l_uom
2467 , l_secondary_transaction_qty -- invconv
2468 , l_secondary_uom_code -- invconv
2469 , l_revision
2470 , l_attribute_category
2471 , l_c_attribute1
2472 , l_c_attribute2
2473 , l_c_attribute3
2474 , l_c_attribute4
2475 , l_c_attribute5
2476 , l_c_attribute6
2477 , l_c_attribute7
2478 , l_c_attribute8
2479 , l_c_attribute9
2480 , l_c_attribute10
2481 , l_c_attribute11
2482 , l_c_attribute12
2483 , l_c_attribute13
2484 , l_c_attribute14
2485 , l_c_attribute15
2486 , l_c_attribute16
2487 , l_c_attribute17
2488 , l_c_attribute18
2489 , l_c_attribute19
2490 , l_c_attribute20
2491 , l_d_attribute1
2492 , l_d_attribute2
2493 , l_d_attribute3
2494 , l_d_attribute4
2495 , l_d_attribute5
2496 , l_d_attribute6
2497 , l_d_attribute7
2498 , l_d_attribute8
2499 , l_d_attribute9
2500 , l_d_attribute10
2501 , l_n_attribute1
2502 , l_n_attribute2
2503 , l_n_attribute3
2504 , l_n_attribute4
2505 , l_n_attribute5
2506 , l_n_attribute6
2507 , l_n_attribute7
2508 , l_n_attribute8
2509 , l_n_attribute9
2510 , l_n_attribute10
2511 , l_territory_code
2512 , l_grade_code
2513 , l_origination_date
2514 , l_date_code
2515 , l_change_date
2516 , l_age
2517 , l_retest_date
2518 , l_maturity_date
2519 , l_item_size
2520 , l_color
2521 , l_volume
2522 , l_volume_uom
2523 , l_place_of_origin
2524 , l_best_by_date
2525 , l_length
2526 , l_length_uom
2527 , l_recycled_content
2528 , l_thickness
2529 , l_thickness_uom
2530 , l_width
2531 , l_width_uom
2532 , l_curl_wrinkle_fold
2533 , l_vendor_name
2534 , l_subinventory_code
2535 , l_locator_id
2536 , l_wip_entity_name -- Fix For Bug: 4907062
2537 , l_wip_description -- Fix For Bug: 4907062
2538 , l_parent_lot_number -- invconv fabdi
2539 , l_expiration_action_date
2540 , l_origination_type
2541 , l_hold_date
2542 , l_expiration_action_code
2543 , l_supplier_lot_number; -- invconv end
2544
2545 IF mmtt_material_receipt_cur%NOTFOUND THEN
2546 IF (l_debug = 1) THEN
2547 TRACE(' No record found in MMTT for given txn_temp_id: ' || p_transaction_id);
2548 END IF;
2549
2550 CLOSE mmtt_material_receipt_cur;
2551 RETURN;
2552 END IF;
2553 -- Pack/Unpack/Split LPN
2554 -- The mmtt.transaction_temp_id is being passed.
2555 ELSIF p_label_type_info.business_flow_code = 20 THEN
2556 OPEN material_lpn_cur;
2557 FETCH material_lpn_cur INTO l_inventory_item_id
2558 , l_organization_id
2559 , l_lot_number
2560 , l_cost_group_id
2561 , l_project_id
2562 , l_task_id
2563 , l_quantity
2564 , l_uom
2565 , l_revision
2566 , l_from_subinventory
2567 , l_to_subinventory
2568 , l_from_locator_id
2569 , l_to_locator_id
2570 , l_secondary_transaction_qty -- invconv
2571 , l_secondary_uom_code; -- invconv
2572
2573
2574 IF material_lpn_cur%NOTFOUND THEN
2575 IF (l_debug = 1) THEN
2576 TRACE(' No Material found for this given temp ID:'|| p_transaction_id);
2577 END IF;
2578
2579 CLOSE material_lpn_cur;
2580 RETURN;
2581 ELSE
2582 NULL;
2583 END IF;
2584 ELSIF p_label_type_info.business_flow_code IN (21) THEN
2585 -- Ship Confirm
2586 -- The delivery_id has being passed. Delivery ID passed means that all the delivery details ID have
2587 -- to be derived for the delivery ID. There will be one record per serial number in the wsh_delivery_details.
2588 -- The cost group will be derived from the table wms_license_plate_numbers for the LPN stamped on the Delivery_detail_id.
2589
2590 OPEN wda_material_cur;
2591 FETCH wda_material_cur INTO l_inventory_item_id
2592 , l_organization_id
2593 , l_lot_number
2594 , l_cost_group_id
2595 , l_project_id
2596 , l_task_id
2597 , l_quantity
2598 , l_uom
2599 , l_revision
2600 , l_subinventory_code
2601 , l_locator_id;
2602
2603 IF wda_material_cur%NOTFOUND THEN
2604 IF (l_debug = 1) THEN
2605 TRACE(' No Material found for this given delivery ID:' || p_transaction_id);
2606 END IF;
2607
2608 CLOSE wda_material_cur;
2609 RETURN;
2610 END IF;
2611 ELSIF p_label_type_info.business_flow_code IN (22) THEN
2612 -- Cartonization
2613 OPEN c_get_pkg_items_content;
2614 FETCH c_get_pkg_items_content INTO l_organization_id
2615 , l_inventory_item_id
2616 , l_revision
2617 , l_lot_number
2618 , l_quantity;
2619
2620 IF c_get_pkg_items_content%NOTFOUND THEN
2621 IF (l_debug = 1) THEN
2622 TRACE(' No records found for Header ID/package mode in the WPH:');
2623 END IF;
2624
2625 CLOSE c_get_pkg_items_content;
2626 RETURN;
2627 END IF;
2628 /*ELSIF p_label_type_info.business_flow_code IN (26) THEN
2629 -- WIP Completion.
2630 --
2631 -- LPN Completions:
2632 -- In this case a record is populated in the MMTT with the item populated in the
2633 -- MMTT.inventorry_item_id and the LPN populated in the MMTT.transfer_lpn_id.
2634 -- As per the WIP team, the LPN is packed before label printing is called
2635 -- For every item of the completion, one record
2636 -- is inserted into the MMTT (with the MMTT.TRANSFER_LPN_ID ) populated and
2637 -- label printing is called. Material Label is printed for the
2638 -- completed item .
2639
2640 -- Non-LPN Completion
2641 -- In this case a record is populated in the MMTT with the item populated in the
2642 -- MMTT.inventory_item_id with all the related inforamtion.
2643
2644 OPEN wip_material_cur;
2645 FETCH wip_material_cur INTO l_inventory_item_id
2646 , l_organization_id
2647 , l_lot_number
2648 , l_cost_group_id
2649 , l_project_id
2650 , l_task_id
2651 , l_quantity
2652 , l_uom
2653 , l_revision
2654 , l_subinventory_code
2655 , l_locator_id;
2656 TRACE(
2657 ' wip_material_cur '
2658 || ', Item ID=' || l_inventory_item_id
2659 || ', Organization ID=' || l_organization_id
2660 || ', Lot Number=' || l_lot_number
2661 || ', Project ID=' || l_project_id
2662 || ', Cost Group ID=' || l_cost_group_id
2663 || ', Task ID=' || l_task_id
2664 || ', Transaction Quantity=' || l_quantity
2665 || ', Transaction UOM=' || l_uom
2666 || ', Item Revision=' || l_revision
2667 || ', Subinventory Code=' || l_subinventory_code
2668 || ', Locator ID=' || l_locator_id
2669 );
2670
2671 IF wip_material_cur%NOTFOUND THEN
2672 TRACE(' No records found for transaction_temp_id in MMTT');
2673 CLOSE wip_material_cur;
2674 END IF;
2675 */
2676 -- Manufacturing Cross-Dock(37)
2677 ELSIF p_label_type_info.business_flow_code = 37 THEN
2678 OPEN wip_material_cur;
2679 FETCH wip_material_cur INTO l_inventory_item_id
2680 , l_organization_id
2681 , l_lot_number
2682 , l_cost_group_id
2683 , l_project_id
2684 , l_task_id
2685 , l_quantity
2686 , l_uom
2687 , l_revision
2688 , l_subinventory_code
2689 , l_locator_id;
2690 IF (l_debug = 1) THEN
2691 TRACE(' wip_material_cur '
2692 || ', Item ID=' || l_inventory_item_id
2693 || ', Organization ID=' || l_organization_id
2694 || ', Lot Number=' || l_lot_number
2695 || ', Project ID=' || l_project_id
2696 || ', Cost Group ID=' || l_cost_group_id
2697 || ', Task ID=' || l_task_id
2698 || ', Transaction Quantity=' || l_quantity
2699 || ', Transaction UOM=' || l_uom
2700 || ', Item Revision=' || l_revision
2701 || ', Subinventory Code=' || l_subinventory_code
2702 || ', Locator ID=' || l_locator_id
2703 );
2704 END IF;
2705
2706 IF wip_material_cur%NOTFOUND THEN
2707 IF (l_debug = 1) THEN
2708 TRACE(' No records found for transaction_temp_id in MMTT');
2709 END IF;
2710 CLOSE wip_material_cur;
2711 END IF;
2712 ELSIF p_label_type_info.business_flow_code IN (33) THEN
2713 -- Flow Completion
2714
2715 IF l_transaction_identifier = 1 THEN
2716 OPEN flow_material_curs_mmtt;
2717 FETCH flow_material_curs_mmtt INTO l_inventory_item_id
2718 , l_organization_id
2719 , l_lot_number
2720 , l_cost_group_id
2721 , l_project_id
2722 , l_task_id
2723 , l_quantity
2724 , l_uom
2725 , l_revision
2726 , l_subinventory_code
2727 , l_locator_id;
2728
2729 IF flow_material_curs_mmtt%NOTFOUND THEN
2730 IF (l_debug = 1) THEN
2731 TRACE(' No Flow Data found for this given ID:' || p_transaction_id || ' identifier=1');
2732 END IF;
2733
2734 CLOSE flow_material_curs_mmtt;
2735 RETURN;
2736 END IF;
2737 ELSIF l_transaction_identifier = 2 THEN
2738 OPEN flow_material_curs_mti;
2739 FETCH flow_material_curs_mti INTO l_inventory_item_id
2740 , l_organization_id
2741 , l_lot_number
2742 , l_cost_group_id
2743 , l_project_id
2744 , l_task_id
2745 , l_quantity
2746 , l_uom
2747 , l_revision
2748 , l_subinventory_code
2749 , l_locator_id;
2750
2751 IF flow_material_curs_mti%NOTFOUND THEN
2752 IF (l_debug = 1) THEN
2753 TRACE(' No Flow Data found for this given ID:' || p_transaction_id || ' identifier=2');
2754 END IF;
2755
2756 CLOSE flow_material_curs_mti;
2757 RETURN;
2758 END IF;
2759 ELSIF l_transaction_identifier = 3 THEN
2760 OPEN flow_material_curs_mol;
2761 FETCH flow_material_curs_mol INTO l_inventory_item_id
2762 , l_organization_id
2763 , l_lot_number
2764 , l_cost_group_id
2765 , l_project_id
2766 , l_task_id
2767 , l_quantity
2768 , l_uom
2769 , l_revision
2770 , l_subinventory_code
2771 , l_locator_id;
2772
2773 IF flow_material_curs_mol%NOTFOUND THEN
2774 IF (l_debug = 1) THEN
2775 TRACE(' No Flow Data found for this given ID:' || p_transaction_id || ' identifier=3');
2776 END IF;
2777
2778 CLOSE flow_material_curs_mol;
2779 RETURN;
2780 END IF;
2781 ELSE
2782 IF (l_debug = 1) THEN
2783 TRACE(' Invalid transaction_identifier passed' || p_transaction_identifier);
2784 END IF;
2785
2786 RETURN;
2787 END IF;
2788 -- Fix bug 2167545-1 Cost Group Update(11) is calling label printing through TM
2789 -- not manually, add 11 in the following group.
2790
2791 --Fix for Bug 4891916
2792 --Modified the condition for business flow for cycle count by checking
2793 --for the business flow 8 and transaction_identifier as 5
2794
2795 ELSIF p_label_type_info.business_flow_code IN
2796 (/*8,*/ 9, 11, 12, 14, 19, 18, 22, 23, 27, 28, 29, 34)--Bug 5928736 - Removed business flow 7
2797 OR (p_label_type_info.business_flow_code = 8 AND p_transaction_identifier = 5)
2798 THEN
2799 select transaction_type_id into l_transaction_type
2800 from mtl_material_transactions_temp
2801 where transaction_temp_id = p_transaction_id; --bug 6646793
2802
2803 OPEN mmtt_material_cur;
2804 FETCH mmtt_material_cur INTO l_inventory_item_id
2805 , l_organization_id
2806 , l_lot_number
2807 , l_cost_group_id
2808 , l_xfr_cost_group_id /* Added for the bug # 4686024 */
2809 , l_project_id
2810 , l_task_id
2811 , l_quantity
2812 , l_uom
2813 , l_revision
2814 , l_from_subinventory
2815 , l_to_subinventory
2816 , l_from_locator_id
2817 , l_to_locator_id
2818 , l_secondary_uom_code -- ADDED for invconv
2819 , l_secondary_transaction_qty; -- invocnv
2820
2821 IF mmtt_material_cur%NOTFOUND THEN
2822 IF (l_debug = 1) THEN
2823 TRACE(' No record found in MMTT for given txn_temp_id: ' || p_transaction_id);
2824 END IF;
2825
2826 CLOSE mmtt_material_cur;
2827 RETURN;
2828 ELSE
2829 --bug 6646793
2830 if (l_transaction_type = 82 and p_label_type_info.business_flow_code = 12)then
2831 select nvl (mmtt.transaction_quantity,mtlt.transaction_quantity) into l_mmtt_quantity
2832 from mtl_material_transactions_temp mmtt , mtl_transaction_lots_temp mtlt
2833 where mmtt.transaction_temp_id = p_transaction_id
2834 and mtlt.transaction_temp_id = mmtt.transaction_temp_id;
2835
2836 SELECT Nvl(Sum(primary_transaction_quantity),0) INTO l_moqd_quantity
2837 FROM mtl_onhand_quantities_detail moqd , mtl_material_transactions_temp mmtt , mtl_transaction_lots_temp mtlt
2838 WHERE mmtt.transaction_temp_id = p_transaction_id
2839 and mtlt.transaction_temp_id = mmtt.transaction_temp_id
2840 and moqd.lot_number = mtlt.lot_number
2841 and nvl(mmtt.lpn_id , -999) = nvl(moqd.lpn_id , -999)
2842 and moqd.inventory_item_id = mmtt.inventory_item_id
2843 and moqd.organization_id = mmtt.organization_id
2844 and moqd.subinventory_code = mmtt.subinventory_code
2845 and NVL(moqd.locator_id , -999 ) = NVL(mmtt.locator_id ,-999);
2846 l_quantity := l_moqd_quantity + l_mmtt_quantity ;
2847 end if;
2848 -- end of fix for bug 6646793
2849
2850 /* For transfer and drop transaction, should get printer with the to_subinventory,
2851 for other cases, use the to_subinevntory */
2852 IF p_label_type_info.business_flow_code IN (14, 19, 29) THEN --Bug 5928736 - Removed business flow 7
2853 l_subinventory_code := l_to_subinventory;
2854 l_locator_id := l_to_locator_id;
2855 ELSE
2856 l_subinventory_code := l_from_subinventory;
2857 l_locator_id := l_from_locator_id;
2858
2859 --Bug 4891916. For cycle count, opened the cursor to fetch values for
2860 --cycle count header name and counter.
2861
2862 IF p_label_type_info.business_flow_code = 8 THEN
2863 OPEN cc_det_approval;
2864
2865 FETCH cc_det_approval
2866 INTO l_cycle_count_name
2867 ,l_requestor;
2868
2869 IF cc_det_approval%NOTFOUND THEN
2870 IF (l_debug = 1) THEN
2871 TRACE(' No record found in MCCE for given txn_temp_id: ' || p_transaction_id);
2872 END IF;
2873
2874 CLOSE cc_det_approval;
2875 END IF; --End of cursor not found condition
2876 END IF; --End of business flow=8 condition
2877 --End of fix for Bug 4891916
2878 END IF;
2879 END IF;--End of mmtt_material_cursor not found
2880
2881 --Bug 4891916- Added the condition to open the cursor to fetch from mcce
2882 --by checking for business flow 8 and transaction identifier 4
2883 ELSIF (p_label_type_info.business_flow_code = 8 AND p_transaction_identifier= 4) THEN --from entry
2884
2885 IF (l_debug = 1) THEN
2886 TRACE(' IN the condition for bus flow 8 and pti 4 ');
2887 END IF;
2888
2889 OPEN mcce_material_cur ;
2890
2891 FETCH mcce_material_cur
2892 INTO l_inventory_item_id
2893 , l_organization_id
2894 , l_lot_number
2895 , l_cost_group_id
2896 , l_quantity
2897 , l_uom
2898 , l_revision
2899 , l_subinventory_code
2900 , l_locator_id
2901 , l_cycle_count_name
2902 , l_requestor ;
2903
2904 IF (l_debug = 1) THEN
2905 TRACE('Values fetched from cursor:');
2906 TRACE('Values of l_inventory_item_id:'|| l_inventory_item_id);
2907 TRACE('Values of l_organization_id:' || l_organization_id);
2908 TRACE('Values of l_lot_number:' || l_lot_number);
2909 TRACE('Values of l_cost_group_id:' || l_cost_group_id);
2910 TRACE('Values of l_quantity:' || l_quantity);
2911 TRACE('Values of l_uom:' || l_uom);
2912 TRACE('Values of l_revision:' || l_revision);
2913 TRACE('Values of l_subinventory:' || l_subinventory_code);
2914 TRACE('Values of l_locator_id:' || l_locator_id);
2915 TRACE('Values of l_cycle_count_name:' || l_cycle_count_name);
2916 TRACE('Values of l_counter:' || l_requestor);
2917 END IF;
2918
2919 IF mcce_material_cur%NOTFOUND THEN
2920
2921 IF (l_debug = 1) THEN
2922 TRACE(' No record found in mcce_material_cur for given cycle_count_id ' || p_transaction_id);
2923 END IF;
2924
2925 CLOSE mcce_material_cur;
2926
2927 RETURN;
2928 END IF;
2929
2930 /* End of fix for Bug 4891916 */
2931
2932 -- Fix for bug 2356935, add Sub and Loc information for Material label for Inventory Putaway
2933 -- Fix for bug 2390460, for subinventory transfer, use the following cursor instead of mmtt_material_cur
2934 ELSIF p_label_type_info.business_flow_code IN (15, 30, 7) THEN --Bug 5928736 - Added the business flow 7
2935 OPEN inv_putaway_material_cur;
2936 FETCH inv_putaway_material_cur INTO l_inventory_item_id
2937 , l_organization_id
2938 , l_lot_number
2939 , l_cost_group_id
2940 , l_project_id
2941 , l_task_id
2942 , l_quantity
2943 , l_secondary_transaction_qty
2944 , l_uom
2945 , l_secondary_uom_code
2946 , l_revision
2947 , l_subinventory_code
2948 , l_locator_id;
2949
2950 IF inv_putaway_material_cur%NOTFOUND THEN
2951 IF (l_debug = 1) THEN
2952 TRACE(' No record found for Inventory Putaway for given txn_temp_id: ' || p_transaction_id);
2953 END IF;
2954
2955 CLOSE inv_putaway_material_cur;
2956 RETURN;
2957 END IF;
2958 ELSE
2959 IF (l_debug = 1) THEN
2960 TRACE('No material label will be printed');
2961 END IF;
2962
2963 RETURN;
2964 END IF;
2965 ELSE
2966 -- On demand, get information from input_param
2967 -- for transactions which don't have a mmtt row in the table,
2968 -- they will also call in a manual mode, they are
2969 -- 5 LPN Correction/Update
2970 -- 10 Material Status update
2971
2972 l_organization_id := p_input_param.organization_id;
2973 l_inventory_item_id := p_input_param.inventory_item_id;
2974 l_lot_number := p_input_param.lot_number;
2975 l_cost_group_id := p_input_param.cost_group_id;
2976 l_xfr_cost_group_id := p_input_param.transfer_cost_group_id; /* Added for the bug # 4686024*/
2977 l_project_id := p_input_param.project_id;
2978 l_task_id := p_input_param.task_id;
2979 l_revision := p_input_param.revision;
2980 l_quantity := p_input_param.transaction_quantity;
2981 l_uom := p_input_param.transaction_uom;
2982 END IF; -- End transaction_is is not null
2983
2984 -- Get cost group, project and task
2985
2986 /* Added for the bug # 4686024 */
2987
2988 IF (l_debug = 1) THEN
2989 TRACE('l_xfr_cost_group_id is ' || l_xfr_cost_group_id || ',' ||
2990 'l_cost_group_id is ' || l_cost_group_id);
2991 END IF;
2992
2993 IF (l_xfr_cost_group_id IS NOT NULL) THEN
2994 OPEN c_cost_group(l_xfr_cost_group_id);
2995 FETCH c_cost_group INTO l_cost_group;
2996
2997 IF c_cost_group%NOTFOUND THEN
2998 l_cost_group := '';
2999 END IF;
3000
3001 CLOSE c_cost_group;
3002 ELSE
3003 OPEN c_cost_group(l_cost_group_id);
3004 FETCH c_cost_group INTO l_cost_group;
3005
3006 IF c_cost_group%NOTFOUND THEN
3007 l_cost_group := '';
3008 END IF;
3009
3010 CLOSE c_cost_group;
3011 END IF;
3012
3013 /* End of fix for bug # 4686024 */
3014
3015 /* Start of fix for 4916450 */
3016 IF (l_debug = 1) THEN
3017 TRACE('Routing Id: ' || l_routing_header_id || ' Transaction id: ' || p_transaction_id);
3018 END IF;
3019 IF ( l_is_wms_org = FALSE AND l_routing_header_id <> 3 ) THEN
3020 OPEN pod_project_task;
3021 FETCH pod_project_task INTO l_project_id, l_task_id;
3022 IF pod_project_task%NOTFOUND THEN
3023 l_project_id := NULL;
3024 l_task_id := NULL;
3025 ELSE
3026 IF (l_debug = 1) THEN
3027 TRACE('Project: ' || l_project_id || 'Task: ' || l_task_id);
3028 END IF;
3029
3030 LOOP
3031 FETCH pod_project_task INTO l_next_project_id, l_next_task_id;
3032 EXIT WHEN pod_project_task%NOTFOUND;
3033 IF (l_debug = 1) THEN
3034 TRACE('Next Project: ' || l_next_project_id || 'Next Task: ' || l_next_task_id);
3035 END IF;
3036 IF NVL(l_project_id,-9999) <> NVL(l_next_project_id,-9999) OR
3037 NVL(l_task_id,-9999) <> NVL(l_next_task_id,-9999) THEN
3038 IF (l_debug = 1) THEN
3039 TRACE('There are multiple distributions for the same po line and shipment');
3040 END IF;
3041 l_project_id := NULL;
3042 l_task_id := NULL;
3043 EXIT;
3044 END IF;
3045 END LOOP;
3046 END IF;
3047
3048 IF (l_debug = 1) THEN
3049 TRACE('Project: ' || l_project_id || 'Task: ' || l_task_id);
3050 END IF;
3051 l_next_project_id := NULL;
3052 l_next_task_id := NULL;
3053 CLOSE pod_project_task;
3054 END IF;
3055
3056 /* End of fix for 4916450 */
3057
3058 OPEN c_project_enabled(l_organization_id);
3059 FETCH c_project_enabled INTO l_is_pjm_org;
3060 IF c_project_enabled%NOTFOUND THEN
3061 IF (l_debug = 1) THEN
3062 trace( 'Organization id ' || l_organization_id || 'is not a PJM Org.');
3063 END IF;
3064 END IF;
3065 CLOSE c_project_enabled;
3066
3067 /*
3068 * The following code has been added so that the c_project and c_task cursors will be opened
3069 * only if the organization is project enabled.
3070 */
3071 IF l_is_pjm_org = 'Y' THEN
3072 -- Fix for 4907062. Fetching project number along with project name
3073 OPEN c_project;
3074 FETCH c_project INTO l_project_name, l_project_number;
3075
3076 IF c_project%NOTFOUND THEN
3077 l_project_name := '';
3078 END IF;
3079
3080 CLOSE c_project;
3081
3082 -- Fix for 4907062. Fetching task number along with project name
3083 OPEN c_task;
3084 FETCH c_task INTO l_task_name, l_task_number;
3085
3086 IF c_task%NOTFOUND THEN
3087 l_task_name := '';
3088 END IF;
3089
3090 CLOSE c_task;
3091 END IF;
3092
3093
3094 IF (l_debug = 1) THEN
3095 TRACE('** in PVT1.get_variable_dataa ** , start ');
3096 END IF;
3097
3098 l_material_input_index := 1;
3099
3100 -- Getting lot Number
3101 IF (l_material_input IS NOT NULL)
3102 AND (l_material_input.COUNT <> 0) THEN
3103 l_lot_number := l_material_input(l_material_input_index).lot_number;
3104 l_quantity := l_material_input(l_material_input_index).lot_quantity;
3105 END IF;
3106
3107 IF (l_debug = 1) THEN
3108 TRACE('Before the While Loop');
3109 END IF;
3110
3111 item_fetch_cntr := 1;
3112 l_label_index := 1;
3113 l_content_rec_index := 0;
3114 l_prev_format_id := -999;
3115 l_prev_sub := '####';
3116 l_printer := p_label_type_info.default_printer;
3117
3118 WHILE ((l_inventory_item_id IS NOT NULL)
3119 OR (l_item_description IS NOT NULL)
3120 ) LOOP
3121 l_material_data := '';
3122
3123 -- Bug 7423016, resetting the value of l_is_expense_item to False
3124 -- at the begining of every record fetched from txn cursors like rt_material_cur, etc.
3125 -- l_is_expense_item will be set inside loop over c_material_cur cursor.
3126 l_is_expense_item := FALSE;
3127
3128 /* Bug 6504959- The fix through bug 4708750 is incorrectly fetching from the
3129 cursor c_material_cur even if the item_id is null.
3130
3131 FOR v_material_cur IN c_material_cur(
3132 l_organization_id
3133 , l_inventory_item_id
3134 , l_lot_number
3135 ) LOOP
3136
3137 -- Start of Fix for the bug # 4708752
3138
3139 EXIT WHEN l_is_expense_item;
3140
3141 IF (l_inventory_item_id IS NULL) THEN
3142 l_is_expense_item := TRUE;
3143 END IF;
3144
3145 -- End of fix for the bug # 4708752 */
3146
3147 OPEN c_material_cur( l_organization_id
3148 , l_inventory_item_id
3149 , l_lot_number
3150 );
3151
3152 LOOP
3153 IF (l_debug = 1) THEN
3154 TRACE('Inside Inner Loop');
3155 END IF;
3156 EXIT WHEN l_is_expense_item;
3157
3158 IF l_inventory_item_id is not null THEN
3159 IF (l_debug = 1) THEN
3160 TRACE('Fetching c_material_cur');
3161 END IF;
3162 FETCH c_material_cur
3163 INTO v_material_cur;
3164
3165 EXIT WHEN c_material_cur%NOTFOUND;
3166 ELSE
3167 IF (l_debug = 1) THEN
3168 TRACE('inventory_item_id is null');
3169 END IF;
3170 l_is_expense_item := TRUE;
3171 END IF;
3172
3173 /* End of fix for Bug 6504959 */
3174
3175 l_content_rec_index := l_content_rec_index + 1;
3176
3177 IF (l_debug = 1) THEN
3178 TRACE(' In Loop '|| l_content_rec_index || '^New Label^');
3179 TRACE( 'orgId=' || l_organization_id
3180 || ',itemId=' || l_inventory_item_id
3181 || ',itemDesc=' || l_item_description
3182 || ',lot=' || l_lot_number
3183 || ',qty=' || l_quantity
3184 || ',uom=' || l_uom
3185 || ',rev=' || l_revision
3186 || ',Parent Lot=' -- invconv fabdi start
3187 || l_parent_lot_number
3188 || ',Expiration Action Date=' || l_expiration_action_date
3189 || ',Origination type=' || l_origination_type
3190 || ',Hold date=' || l_hold_date
3191 || ',Secondary Qty=' || l_secondary_transaction_qty
3192 || 'Secondary UOM=' || l_secondary_uom_code
3193 || 'Expiration action code=' || l_expiration_action_code-- invconv fabdi end
3194 );
3195 TRACE( ',fromSub=' || l_from_subinventory
3196 || ',fromLoc=' || l_from_locator_id
3197 || ',toSub=' || l_to_subinventory
3198 || ',toLoc=' || l_to_locator_id
3199 || ',sub=' || l_subinventory_code
3200 || ',loc=' || l_locator_id
3201 );
3202 TRACE( 'cg=' || l_cost_group
3203 || ',project=' || l_project_name
3204 || ',task=' || l_task_name
3205 );
3206 END IF;
3207
3208 l_label_status := INV_LABEL.G_SUCCESS;
3209
3210 IF (l_debug = 1) THEN
3211 TRACE('Apply Rules engine for format,'
3212 || ',manual_format_id=' || p_label_type_info.manual_format_id
3213 || ',manual_format_name=' || p_label_type_info.manual_format_name
3214 );
3215 END IF;
3216
3217 /* R12 insert a record into wms_label_requests entity to
3218 call the label rules engine to get appropriate label
3219 In this call if this happens to be for the label-set, the record
3220 from wms_label_request will be deleted inside following API*/
3221
3222 inv_label.get_format_with_rule(
3223 p_document_id => p_label_type_info.label_type_id
3224 , p_label_format_id => p_label_type_info.manual_format_id
3225 , p_organization_id => l_organization_id
3226 , p_inventory_item_id => l_inventory_item_id
3227 , p_lot_number => l_lot_number
3228 , p_revision => l_revision
3229 , p_subinventory_code => l_subinventory_code
3230 , p_locator_id => l_locator_id
3231 , p_business_flow_code => p_label_type_info.business_flow_code
3232 --, p_printer_name => l_printer --not used post R12
3233 , p_last_update_date => SYSDATE
3234 , p_last_updated_by => fnd_global.user_id
3235 , p_creation_date => SYSDATE
3236 , p_created_by => fnd_global.user_id
3237 , -- Added for Bug 2748297 Start
3238 p_supplier_id => l_vendor_id
3239 , p_supplier_site_id => l_vendor_site_id
3240 , -- End
3241 x_return_status => l_return_status
3242 , x_label_format_id => l_label_format_set_id
3243 , x_label_format => l_label_format
3244 , x_label_request_id => l_label_request_id
3245 );
3246
3247 IF l_return_status <> 'S' THEN
3248 fnd_message.set_name('WMS', 'WMS_LABL_RULE_ENGINE_FAILED');
3249 fnd_msg_pub.ADD;
3250 l_label_format_set_id := p_label_type_info.default_format_id;
3251 l_label_format := p_label_type_info.default_format_name;
3252 END IF;
3253
3254 IF (l_debug = 1) THEN
3255 TRACE('did apply label ' || l_label_format || ',' || l_label_format_set_id
3256 || ',req_id ' || l_label_request_id
3257 );
3258 END IF;
3259
3260 --for manual printer, l_label_format_set_id returned from above API
3261 --will be infact p_label_type_info.manual_format_id which can be a
3262 --label set or a label format
3263
3264
3265 --Added in R12 for Label sets with RFID
3266 --l_label_format_set_idreturned by the rules engine can be either a
3267 --label format OR a label set
3268 IF (l_debug = 1) THEN
3269 TRACE('^^^^^^^^^^^^^^^^Label-sets^^^^^^^^^^^^');
3270 TRACE(' looping though formats in set begins, format_id/set_id :'||l_label_format_set_id);
3271 END IF;
3272
3273
3274 FOR l_label_formats_in_set IN c_label_formats_in_set(l_label_format_set_id) LOOP
3275
3276 IF (l_debug = 1) THEN
3277 TRACE(' Format_id for Current set :'||l_label_formats_in_set.label_format_id);
3278 END IF;
3279
3280 --CODE logic
3281 -- If it is label-SET then
3282 ---- after getting all the formats inside a label SET calling the
3283 ----get_format_with_rule() is same. Just need to
3284 ----1 Insert record into WMS_LABEL_REQUESTS
3285 ----2 get value of l_label_format_id, l_label_format, l_label_request_id
3286 ----3 Do not call Rules Engine again, as we know format id
3287 --else
3288 ----Do not call get_format_with_rule(), just use the format-id
3289
3290 IF l_label_formats_in_set.label_entity_type = 1 THEN --IT IS LABEL SET
3291
3292 --In R12 call this API for the format AGAIN without calling Rules ENGINE
3293 /* insert a record into wms_label_requests entity */
3294
3295 inv_label.get_format_with_rule
3296 (
3297 p_document_id => p_label_type_info.label_type_id
3298 , p_label_format_id => l_label_formats_in_set.label_format_id --considers manual printer also
3299 , p_organization_id => l_organization_id
3300 , p_inventory_item_id => l_inventory_item_id
3301 , p_lot_number => l_lot_number
3302 , p_revision => l_revision
3303 , p_subinventory_code => l_subinventory_code
3304 , p_locator_id => l_locator_id
3305 , p_business_flow_code => p_label_type_info.business_flow_code
3306 --, p_printer_name => l_printer --not used post R12
3307 , p_last_update_date => SYSDATE
3308 , p_last_updated_by => fnd_global.user_id
3309 , p_creation_date => SYSDATE
3310 , p_created_by => fnd_global.user_id
3311 , -- Added for Bug 2748297 Start
3312 p_supplier_id => l_vendor_id
3313 , p_supplier_site_id => l_vendor_site_id -- End
3314 , p_use_rule_engine => 'N' --------------------------Rules ENgine will NOT get called
3315 , x_return_status => l_return_status
3316 , x_label_format_id => l_label_format_id
3317 , x_label_format => l_label_format
3318 , x_label_request_id => l_label_request_id
3319 );
3320
3321 IF l_return_status <> 'S' THEN
3322 fnd_message.set_name('WMS', 'WMS_LABL_RULE_ENGINE_FAILED');
3323 fnd_msg_pub.ADD;
3324 l_label_format_id := p_label_type_info.default_format_id;
3325 l_label_format := p_label_type_info.default_format_name;
3326 END IF;
3327
3328
3329 IF (l_debug = 1) THEN
3330 TRACE('did apply label ' || l_label_format|| ',' || l_label_format_id
3331 || ',req_id '|| l_label_request_id
3332 );
3333 END IF;
3334
3335
3336 ELSE --IT IS LABEL FORMAT
3337 --Just use the format-id returned
3338 l_label_format_id := l_label_formats_in_set.label_format_id ;
3339 END IF;
3340
3341
3342
3343 IF (l_debug = 1) THEN
3344 TRACE('Geting expected printer based on label_format_id :'||l_label_format_id);
3345 END IF;
3346
3347
3348 -- IF clause Added for Add format/printer for manual request
3349 IF p_label_type_info.manual_printer IS NULL THEN
3350 -- The p_label_type_info.manual_printer is the one passed from the manual page.
3351 -- As per the design, if a printer is passed from the manual page, then we use that printer irrespective.
3352 IF (l_subinventory_code IS NOT NULL)
3353 AND (l_subinventory_code <> l_prev_sub) THEN
3354 IF (l_debug = 1) THEN
3355 TRACE('getting printer with sub '|| l_subinventory_code);
3356 END IF;
3357
3358 BEGIN
3359 wsh_report_printers_pvt.get_printer
3360 (p_concurrent_program_id => p_label_type_info.label_type_id
3361 , p_user_id => fnd_global.user_id
3362 , p_responsibility_id => fnd_global.resp_id
3363 , p_application_id => fnd_global.resp_appl_id
3364 , p_organization_id => l_organization_id
3365 , p_zone => l_subinventory_code
3366 , p_format_id => l_label_format_id --added in R12
3367 , x_printer => l_printer
3368 , x_api_status => l_api_status
3369 , x_error_message => l_error_message
3370 );
3371
3372 IF l_api_status <> 'S' THEN
3373 IF (l_debug = 1) THEN
3374 TRACE('Error in calling get_printer, set printer '
3375 || 'as default printer, err_msg:' || l_error_message
3376 );
3377 END IF;
3378
3379 l_printer := p_label_type_info.default_printer;
3380 END IF;
3381 EXCEPTION
3382 WHEN OTHERS THEN
3383 l_printer := p_label_type_info.default_printer;
3384 END;
3385
3386 l_prev_sub := l_subinventory_code;
3387 END IF;
3388 ELSE
3389 IF (l_debug = 1) THEN
3390 TRACE('Set printer as Manual Printer passed in:'
3391 || p_label_type_info.manual_printer
3392 );
3393 END IF;
3394
3395 l_printer := p_label_type_info.manual_printer;
3396 END IF;
3397
3398
3399 IF (l_label_format_id IS NOT NULL) THEN
3400 -- Derive the fields for the format either passed in or derived via the rules engine.
3401 IF l_label_format_id <> NVL(l_prev_format_id, -999) THEN
3402 IF (l_debug = 1) THEN
3403 TRACE(' Getting variables for new format '|| l_label_format);
3404 END IF;
3405
3406 -- Changed for R12 RFID project
3407 inv_label.get_variables_for_format
3408 ( x_variables => l_selected_fields
3409 , x_variables_count => l_selected_fields_count
3410 , x_is_variable_exist => l_is_epc_exist
3411 , p_format_id => l_label_format_id
3412 , p_exist_variable_name => 'EPC'
3413 );
3414
3415 l_prev_format_id := l_label_format_id;
3416
3417 IF (l_selected_fields_count = 0)
3418 OR (l_selected_fields.COUNT = 0) THEN
3419 IF (l_debug = 1) THEN
3420 TRACE('no fields defined for this format: '
3421 || l_label_format || ',' || l_label_format_id
3422 );
3423 TRACE('######## GOING TO THE NEXT LABEL####');
3424 END IF;
3425 GOTO nextlabel;
3426 END IF;
3427
3428 IF (l_debug = 1) THEN
3429 TRACE(' Found selected_fields for format '
3430 || l_label_format || ', num=' || l_selected_fields_count
3431 );
3432 END IF;
3433 END IF;
3434 ELSE
3435 IF (l_debug = 1) THEN
3436 TRACE('No format exists for this label, goto nextlabel');
3437 END IF;
3438
3439 GOTO nextlabel;
3440 END IF;
3441
3442
3443 -- Added for UCC 128 J Bug #3067059
3444 inv_label.is_item_gtin_enabled(
3445 x_return_status => l_return_status
3446 , x_gtin_enabled => l_gtin_enabled
3447 , x_gtin => l_gtin
3448 , x_gtin_desc => l_gtin_desc
3449 , p_organization_id => l_organization_id
3450 , p_inventory_item_id => l_inventory_item_id
3451 , p_unit_of_measure => l_uom
3452 , p_revision => l_revision );
3453
3454
3455
3456 --R12 changes for RFID compliance project
3457 l_epc_loop_count := 1;
3458
3459 IF (l_gtin_enabled AND l_quantity > 1) THEN
3460 SELECT FLOOR(l_quantity)
3461 INTO l_quantity_floor
3462 FROM DUAL;
3463
3464
3465 IF (l_debug =1) THEN
3466 trace('l_quantity_floor :'||l_quantity_floor);
3467 trace('l_quantity :'||l_quantity);
3468 trace('l_is_epc_exist :'||l_is_epc_exist );
3469 END IF;
3470
3471
3472 -- IF quantity IS NON-INTEGER THEN
3473 -- derive _QUANTITY = transacted Qty.
3474 IF (l_quantity = l_quantity_floor) THEN --Integer quantity
3475
3476 --check for GTIN and EPC
3477 IF l_is_epc_exist = 'Y' THEN
3478 l_gtin_epc_quantity := 1; --to assign to "_QUANTITY"
3479 l_epc_loop_count := l_quantity; --each time different epc will be generated
3480 l_quantity := 1;
3481 ELSE
3482 l_gtin_epc_quantity := l_quantity; --to assign to "_QUANTITY"
3483 l_quantity := 1;
3484 END IF;
3485
3486 ELSE -- Fraction qty : Do not print GTIN
3487
3488 l_gtin_epc_quantity := 1;
3489 l_gtin_desc := NULL;
3490 l_gtin := NULL;
3491 END IF;
3492
3493 END IF;
3494
3495
3496 --Start epc LOOP
3497 --This loop added in R12 for RFID compliance project
3498 FOR i IN 1..l_epc_loop_count LOOP --loop to generate different EPC
3499
3500
3501 -- Added in R12 RFID compliance project
3502 -- Get RFID/EPC related information for a format
3503 -- Only do this if EPC is a field included in the format
3504 IF l_is_epc_exist = 'Y' THEN
3505 IF (l_debug =1) THEN
3506 trace('Generating EPC');
3507 END IF;
3508
3509 --we ned seperate label request corresponding to each EPC
3510
3511 IF i > 1 THEN --for first request, a record in wms_label_request has
3512 --already been posted IN last call TO .get_format_with_rule
3513 IF (l_debug =1) THEN
3514 trace('*****************passing l_label_format_id :' ||l_label_format_id);
3515 END IF;
3516
3517 inv_label.get_format_with_rule
3518 (
3519 p_document_id => p_label_type_info.label_type_id
3520 , p_label_format_id => l_label_formats_in_set.label_format_id --keep current format id
3521 , p_organization_id => l_organization_id
3522 , p_inventory_item_id => l_inventory_item_id
3523 , p_lot_number => l_lot_number
3524 , p_revision => l_revision
3525 , p_subinventory_code => l_subinventory_code
3526 , p_locator_id => l_locator_id
3527 , p_business_flow_code => p_label_type_info.business_flow_code
3528 --, p_printer_name => l_printer --not used post R12
3529 , p_last_update_date => SYSDATE
3530 , p_last_updated_by => fnd_global.user_id
3531 , p_creation_date => SYSDATE
3532 , p_created_by => fnd_global.user_id
3533 , -- Added for Bug 2748297 Start
3534 p_supplier_id => l_vendor_id
3535 , p_supplier_site_id => l_vendor_site_id -- End
3536 , p_use_rule_engine => 'N' ------Rules Engine will NOT get called
3537 , x_return_status => l_return_status
3538 , x_label_format_id => l_label_format_id
3539 , x_label_format => l_label_format
3540 , x_label_request_id => l_label_request_id --A NEW label request id
3541 );
3542
3543 IF l_return_status <> 'S' THEN
3544 fnd_message.set_name('WMS', 'WMS_LABL_RULE_ENGINE_FAILED');
3545 fnd_msg_pub.ADD;
3546 l_label_format_id := p_label_type_info.default_format_id;
3547 l_label_format := p_label_type_info.default_format_name;
3548 END IF;
3549
3550
3551 IF (l_debug = 1) THEN
3552 TRACE('did apply label ' || l_label_format || ',' || l_label_format_id
3553 || ',req_id ' || l_label_request_id
3554 );
3555 END IF;
3556 END IF;
3557
3558
3559 -- Now call generate EPC for each new label request
3560 BEGIN
3561
3562 -- Added in R12 RFID compliance
3563 -- New field : EPC
3564 -- When generate_epc API returns E (expected error) or U(expected error),
3565 -- it sets the error message, but generate xml with EPC as null
3566
3567 /* if l_quantity is fraction in primary_uom, it will not
3568 find correcponding GTIN for fraction qty , and NO EPC will
3569 be generated but for Non-primary UOM fraction qty it
3570 might finda match as teh number might be integer afer
3571 converting it to primary_qty*/
3572
3573 WMS_EPC_PVT.generate_epc
3574 (p_org_id => l_organization_id,
3575 p_label_type_id => p_label_type_info.label_type_id, -- 1
3576 p_group_id => inv_label.EPC_group_id,
3577 p_label_format_id => l_label_format_id,
3578 p_item_id => l_inventory_item_id, --For Material label
3579 p_txn_qty => l_quantity, --For Material Label
3580 p_txn_uom => l_uom, --For Material Label
3581 p_label_request_id => l_label_request_id,
3582 p_business_flow_code => p_label_type_info.business_flow_code,
3583 x_epc => l_epc,
3584 x_return_status => l_epc_ret_status, -- S / E / U
3585 x_return_mesg => l_epc_ret_msg
3586 );
3587
3588 IF (l_debug = 1) THEN
3589 trace('Called generate_epc with ');
3590 trace('l_inventory_item_id='||l_inventory_item_id||',p_group_id='||inv_label.epc_group_id);
3591 trace('l_quantity='||l_quantity||',l_uom='||l_uom);
3592 trace('l_label_format_id='||l_label_format_id||',p_user_id='||fnd_global.user_id);
3593 trace('p_org_id='||l_organization_id);
3594 trace('l_label_request_id= '||l_label_request_id);
3595 trace('x_epc='||l_epc);
3596 trace('x_return_status='||l_epc_ret_status);
3597 trace('x_return_mesg=' ||l_epc_ret_msg);
3598 END IF;
3599
3600 IF l_epc_ret_status = 'S' THEN
3601 -- Success
3602 IF (l_debug = 1) THEN
3603 trace('Succesfully generated EPC '||l_epc);
3604 END IF;
3605 ELSIF l_epc_ret_status = 'U' THEN
3606 -- Unexpected error
3607 l_epc := null;
3608 IF(l_debug = 1) THEN
3609 trace('Got unexpected error from generate_epc');
3610 trace('Set label status as Error and l_epc = null');
3611 END IF;
3612
3613 ELSIF l_epc_ret_status = 'E' THEN
3614 -- Expected error
3615 l_epc := null;
3616 IF(l_debug = 1) THEN
3617 trace('Got expected error from generate_epc, msg');
3618 trace('Set label status as Warning and l_epc = null');
3619 END IF;
3620 ELSE
3621 trace('generate_epc returned a status that is not recognized, set epc as null');
3622 l_epc := null;
3623 END IF;
3624 -- End Bug
3625
3626 EXCEPTION
3627 WHEN no_data_found THEN
3628 IF(l_debug =1 ) THEN
3629 trace('No format found when retrieving EPC information. Format_id='||l_label_format_id);
3630 END IF;
3631 WHEN others THEN
3632 IF(l_debug =1 ) THEN
3633 trace('Other error when retrieving EPC information. Format_id='||l_label_format_id);
3634 END IF;
3635 END;
3636
3637 END IF;
3638
3639
3640 /* variable header */
3641 l_material_data := l_material_data || label_b;
3642
3643 IF l_label_format <> NVL(p_label_type_info.default_format_name, '@@@') THEN
3644 l_material_data := l_material_data || ' _FORMAT="' || l_label_format || '"';
3645 END IF;
3646
3647 IF (l_printer IS NOT NULL)
3648 AND (l_printer <> NVL(p_label_type_info.default_printer, '###')) THEN
3649 l_material_data := l_material_data || ' _PRINTERNAME="' || l_printer || '"';
3650 END IF;
3651
3652 -- Bug 7497507, printing _QUANTITY only when item is gtin enabled.
3653 -- Earlier, _QUANTITY was always getting stamped to 1, in case of non-gtin enabled items.
3654 -- _QUANTITY in label tag overrides _QUANTITY in labels tag.
3655 -- Hence while printing no. of copies greater than 1, it always use to print only 1 label.
3656 IF (l_gtin_enabled = TRUE) THEN
3657 l_material_data := l_material_data || ' _QUANTITY="' || l_gtin_epc_quantity || '"';
3658 END IF;
3659 l_material_data := l_material_data || tag_e;
3660
3661 IF (l_debug = 1) THEN
3662 TRACE('Starting assign variables, ');
3663 END IF;
3664
3665 l_column_name_list := 'Set variables for ';
3666
3667 /* Modified for Bug 4072474 -start*/
3668 l_custom_sql_ret_status := FND_API.G_RET_STS_SUCCESS;
3669 /* Modified for Bug 4072474 -End*/
3670
3671 -- Fix for bug: 4179593 Start
3672 l_CustSqlWarnFlagSet := FALSE;
3673 l_CustSqlErrFlagSet := FALSE;
3674 l_CustSqlWarnMsg := NULL;
3675 l_CustSqlErrMsg := NULL;
3676 -- Fix for bug: 4179593 End
3677
3678 -- Loop for each selected fields, find the columns and write into the XML_content
3679 FOR i IN 1 .. l_selected_fields.COUNT LOOP
3680 IF (l_debug = 1) THEN
3681 l_column_name_list := l_column_name_list || ',' || l_selected_fields(i).column_name;
3682 END IF;
3683
3684 ---------------------------------------------------------------------------------------------
3685 -- Project: 'Custom Labels' (A 11i10+ Project) |
3686 -- Author: Dinesh ([email protected]) |
3687 -- Change Description: |
3688 -- The check (SQL_STMT <> NULL and COLUMN_NAME = NULL) implies that the field is a |
3689 -- Custom SQL based field. Handle it appropriately. |
3690 ---------------------------------------------------------------------------------------------
3691
3692 IF (l_selected_fields(i).SQL_STMT IS NOT NULL AND l_selected_fields(i).column_name = 'sql_stmt') THEN
3693 IF (l_debug = 1) THEN
3694 trace('Custom Labels Trace [INVLAP1B.pls]: ------------------------- REPORT BEGIN-------------------------------------');
3695 trace('Custom Labels Trace [INVLAP1B.pls]: LABEL_FIELD_ID : ' || l_selected_fields(i).label_field_id);
3696 trace('Custom Labels Trace [INVLAP1B.pls]: FIELD_VARIABLE_NAME : ' || l_selected_fields(i).variable_name);
3697 trace('Custom Labels Trace [INVLAP1B.pls]: COLUMN_NAME : ' || l_selected_fields(i).column_name);
3698 trace('Custom Labels Trace [INVLAP1B.pls]: SQL_STMT : ' || l_selected_fields(i).sql_stmt);
3699 END IF;
3700 l_sql_stmt := l_selected_fields(i).sql_stmt;
3701 IF (l_debug = 1) THEN
3702 trace('Custom Labels Trace [INVLAP1B.pls]: l_sql_stmt BEFORE REQUEST_ID Filter Concatenation: ' || l_sql_stmt);
3703 END IF;
3704 l_sql_stmt := l_sql_stmt || ' AND WLR.LABEL_REQUEST_ID = :REQUEST_ID';
3705 IF (l_debug = 1) THEN
3706 trace('Custom Labels Trace [INVLAP1B.pls]: l_sql_stmt AFTER REQUEST_ID Filter Concatenation: ' || l_sql_stmt);
3707
3708 END IF;
3709 BEGIN
3710 IF (l_debug = 1) THEN
3711 trace('Custom Labels Trace [INVLAP1B.pls]: At Breadcrumb 1');
3712 trace('Custom Labels Trace [INVLAP1B.pls]: LABEL_REQUEST_ID : ' || l_label_request_id);
3713 END IF;
3714 OPEN c_sql_stmt FOR l_sql_stmt using l_label_request_id;
3715 LOOP
3716 FETCH c_sql_stmt INTO l_sql_stmt_result;
3717 EXIT WHEN c_sql_stmt%notfound OR c_sql_stmt%rowcount >=2;
3718 END LOOP;
3719
3720 IF (c_sql_stmt%rowcount=1 AND l_sql_stmt_result IS NULL) THEN
3721 x_return_status := FND_API.G_RET_STS_SUCCESS;
3722 l_custom_sql_ret_status := INV_LABEL.G_WARNING;
3723 fnd_message.set_name('WMS','WMS_CS_NULL_VALUE_RETURNED');
3724 fnd_msg_pub.ADD;
3725 -- Fix for bug: 4179593 Start
3726 --fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false, p_count => x_msg_count, p_data => l_custom_sql_ret_msg);
3727 l_custom_sql_ret_msg := fnd_msg_pub.get(p_msg_index => fnd_msg_pub.g_last, p_encoded => fnd_api.g_false);
3728 l_CustSqlWarnMsg := l_custom_sql_ret_msg;
3729 l_CustSqlWarnFlagSet := TRUE;
3730 -- Fix for bug: 4179593 End
3731
3732 IF (l_debug = 1) THEN
3733 trace('Custom Labels Trace [INVLAP1B.pls]: At Breadcrumb 2');
3734 trace('Custom Labels Trace [INVLAP1B.pls]: l_sql_stmt_result is: ' || l_sql_stmt_result);
3735 trace('Custom Labels Trace [INVLAP1B.pls]: WARNING: NULL value returned by the custom SQL Query.');
3736 trace('Custom Labels Trace [INVLAP1B.pls]: l_custom_sql_ret_status is set to : ' || l_custom_sql_ret_status);
3737 END IF;
3738 ELSIF c_sql_stmt%rowcount=0 THEN
3739 IF (l_debug = 1) THEN
3740 trace('Custom Labels Trace [INVLAP1B.pls]: At Breadcrumb 3');
3741 trace('Custom Labels Trace [INVLAP1B.pls]: WARNING: No row returned by the Custom SQL query');
3742 END IF;
3743 x_return_status := FND_API.G_RET_STS_SUCCESS;
3744 l_custom_sql_ret_status := INV_LABEL.G_WARNING;
3745 fnd_message.set_name('WMS','WMS_CS_NO_DATA_FOUND');
3746 fnd_msg_pub.ADD;
3747 -- Fix for bug: 4179593 Start
3748 --fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false, p_count => x_msg_count, p_data => l_custom_sql_ret_msg);
3749 l_custom_sql_ret_msg := fnd_msg_pub.get(p_msg_index => fnd_msg_pub.g_last, p_encoded => fnd_api.g_false);
3750 l_CustSqlWarnMsg := l_custom_sql_ret_msg;
3751 l_CustSqlWarnFlagSet := TRUE;
3752 -- Fix for bug: 4179593 End
3753 ELSIF c_sql_stmt%rowcount>=2 THEN
3754 IF (l_debug = 1) THEN
3755 trace('Custom Labels Trace [INVLAP1B.pls]: At Breadcrumb 4');
3756 trace('Custom Labels Trace [INVLAP1B.pls]: ERROR: Multiple values returned by the Custom SQL query');
3757 END IF;
3758 l_sql_stmt_result := NULL;
3759 x_return_status := FND_API.G_RET_STS_SUCCESS;
3760 l_custom_sql_ret_status := FND_API.G_RET_STS_ERROR;
3761 fnd_message.set_name('WMS','WMS_CS_MULTIPLE_VALUES_RETURN');
3762 fnd_msg_pub.ADD;
3763 -- Fix for bug: 4179593 Start
3764 --fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false, p_count => x_msg_count, p_data => l_custom_sql_ret_msg);
3765 l_custom_sql_ret_msg := fnd_msg_pub.get(p_msg_index => fnd_msg_pub.g_last, p_encoded => fnd_api.g_false);
3766 l_CustSqlErrMsg := l_custom_sql_ret_msg;
3767 l_CustSqlErrFlagSet := TRUE;
3768 -- Fix for bug: 4179593 End
3769 END IF;
3770 IF (c_sql_stmt%ISOPEN) THEN
3771 CLOSE c_sql_stmt;
3772 END IF;
3773 EXCEPTION
3774 WHEN OTHERS THEN
3775 IF (c_sql_stmt%ISOPEN) THEN
3776 CLOSE c_sql_stmt;
3777 END IF;
3778 IF (l_debug = 1) THEN
3779 trace('Custom Labels Trace [INVLAP1B.pls]: At Breadcrumb 5');
3780 trace('Custom Labels Trace [INVLAP1B.pls]: Unexpected Error has occured in GET_VARIABLES_DATA');
3781 END IF;
3782 x_return_status := FND_API.G_RET_STS_ERROR;
3783 fnd_message.set_name('WMS','WMS_CS_WRONG_SQL_CONSTRUCT');
3784 fnd_msg_pub.ADD;
3785 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false, p_count => x_msg_count, p_data => x_msg_data);
3786 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3787 END;
3788 IF (l_debug = 1) THEN
3789 trace('Custom Labels Trace [INVLAP1B.pls]: At Breadcrumb 6');
3790 trace('Custom Labels Trace [INVLAP1B.pls]: Before assigning it to l_material_data');
3791 END IF;
3792 l_material_data := l_material_data
3793 || variable_b
3794 || l_selected_fields(i).variable_name
3795 || '">'
3796 || l_sql_stmt_result
3797 || variable_e;
3798 l_sql_stmt_result := NULL;
3799 l_sql_stmt := NULL;
3800 IF (l_debug = 1) THEN
3801 trace('Custom Labels Trace [INVLAP1B.pls]: At Breadcrumb 7');
3802 trace('Custom Labels Trace [INVLAP1B.pls]: After assigning it to l_material_data');
3803 trace('Custom Labels Trace [INVLAP1B.pls]: --------------------------REPORT END-------------------------------------');
3804 END IF;
3805 ------------------------End of this change for Custom Labels project code--------------------
3806 ELSIF LOWER(l_selected_fields(i).column_name) = 'current_date' THEN
3807 l_material_data := l_material_data
3808 || variable_b
3809 || l_selected_fields(i).variable_name
3810 || '">'
3811 || inv_label.g_date
3812 || variable_e;
3813 ELSIF LOWER(l_selected_fields(i).column_name) = 'current_time' THEN
3814 l_material_data := l_material_data
3815 || variable_b
3816 || l_selected_fields(i).variable_name
3817 || '">'
3818 || inv_label.g_time
3819 || variable_e;
3820 ELSIF LOWER(l_selected_fields(i).column_name) = 'request_user' THEN
3821 l_material_data := l_material_data
3822 || variable_b
3823 || l_selected_fields(i).variable_name
3824 || '">'
3825 || inv_label.g_user
3826 || variable_e;
3827 ELSIF LOWER(l_selected_fields(i).column_name) = 'item' THEN
3828 l_material_data := l_material_data
3829 || variable_b
3830 || l_selected_fields(i).variable_name
3831 || '">'
3832 || v_material_cur.item
3833 || variable_e;
3834 ELSIF LOWER(l_selected_fields(i).column_name) = 'item_description' THEN
3835 l_material_data := l_material_data
3836 || variable_b
3837 || l_selected_fields(i).variable_name
3838 || '">'
3839 || nvl(v_material_cur.item_description,l_item_description) /* Modified for the bug # 4708752*/
3840 || variable_e;
3841 ELSIF LOWER(l_selected_fields(i).column_name) = 'revision' THEN
3842 l_material_data := l_material_data
3843 || variable_b
3844 || l_selected_fields(i).variable_name
3845 || '">'
3846 || v_material_cur.revision
3847 || variable_e;
3848 ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_number' THEN
3849 l_material_data := l_material_data
3850 || variable_b
3851 || l_selected_fields(i).variable_name
3852 || '">'
3853 || l_lot_number
3854 || variable_e;
3855 ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_status' THEN
3856 l_material_data := l_material_data
3857 || variable_b
3858 || l_selected_fields(i).variable_name
3859 || '">'
3860 || v_material_cur.lot_status
3861 || variable_e;
3862 ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_expiration_date' THEN
3863 l_material_data := l_material_data
3864 || variable_b
3865 || l_selected_fields(i).variable_name
3866 || '">'
3867 || v_material_cur.lot_expiration_date
3868 || variable_e;
3869 ELSIF LOWER(l_selected_fields(i).column_name) = 'quantity' THEN
3870 l_material_data := l_material_data
3871 || variable_b
3872 || l_selected_fields(i).variable_name
3873 || '">'
3874 || l_quantity
3875 || variable_e;
3876 ELSIF LOWER(l_selected_fields(i).column_name) = 'uom' THEN
3877 l_material_data := l_material_data
3878 || variable_b
3879 || l_selected_fields(i).variable_name
3880 || '">'
3881 || v_material_cur.uom
3882 || variable_e;
3883 ELSIF LOWER(l_selected_fields(i).column_name) = 'cost_group' THEN
3884 l_material_data := l_material_data
3885 || variable_b
3886 || l_selected_fields(i).variable_name
3887 || '">'
3888 || v_material_cur.cost_group
3889 || variable_e;
3890 ELSIF LOWER(l_selected_fields(i).column_name) = 'customer_purchase_order' THEN
3891 l_material_data := l_material_data
3892 || variable_b
3893 || l_selected_fields(i).variable_name
3894 || '">'
3895 || l_purchase_order
3896 || variable_e;
3897 ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_attribute_category' THEN
3898 l_material_data := l_material_data
3899 || variable_b
3900 || l_selected_fields(i).variable_name
3901 || '">'
3902 || v_material_cur.lot_attribute_category
3903 || variable_e;
3904 ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_c_attribute1' THEN
3905 l_material_data := l_material_data
3906 || variable_b
3907 || l_selected_fields(i).variable_name
3908 || '">'
3909 || v_material_cur.lot_c_attribute1
3910 || variable_e;
3911 ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_c_attribute2' THEN
3912 l_material_data := l_material_data
3913 || variable_b
3914 || l_selected_fields(i).variable_name
3915 || '">'
3916 || v_material_cur.lot_c_attribute2
3917 || variable_e;
3918 ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_c_attribute3' THEN
3919 l_material_data := l_material_data
3920 || variable_b
3921 || l_selected_fields(i).variable_name
3922 || '">'
3923 || v_material_cur.lot_c_attribute3
3924 || variable_e;
3925 ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_c_attribute4' THEN
3926 l_material_data := l_material_data
3927 || variable_b
3928 || l_selected_fields(i).variable_name
3929 || '">'
3930 || v_material_cur.lot_c_attribute4
3931 || variable_e;
3932 ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_c_attribute5' THEN
3933 l_material_data := l_material_data
3934 || variable_b
3935 || l_selected_fields(i).variable_name
3936 || '">'
3937 || v_material_cur.lot_c_attribute5
3938 || variable_e;
3939 ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_c_attribute6' THEN
3940 l_material_data := l_material_data
3941 || variable_b
3942 || l_selected_fields(i).variable_name
3943 || '">'
3944 || v_material_cur.lot_c_attribute6
3945 || variable_e;
3946 ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_c_attribute7' THEN
3947 l_material_data := l_material_data
3948 || variable_b
3949 || l_selected_fields(i).variable_name
3950 || '">'
3951 || v_material_cur.lot_c_attribute7
3952 || variable_e;
3953 ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_c_attribute8' THEN
3954 l_material_data := l_material_data
3955 || variable_b
3956 || l_selected_fields(i).variable_name
3957 || '">'
3958 || v_material_cur.lot_c_attribute8
3959 || variable_e;
3960 ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_c_attribute9' THEN
3961 l_material_data := l_material_data
3962 || variable_b
3963 || l_selected_fields(i).variable_name
3964 || '">'
3965 || v_material_cur.lot_c_attribute9
3966 || variable_e;
3967 ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_c_attribute10' THEN
3968 l_material_data := l_material_data
3969 || variable_b
3970 || l_selected_fields(i).variable_name
3971 || '">'
3972 || v_material_cur.lot_c_attribute10
3973 || variable_e;
3974 ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_c_attribute11' THEN
3975 l_material_data := l_material_data
3976 || variable_b
3977 || l_selected_fields(i).variable_name
3978 || '">'
3979 || v_material_cur.lot_c_attribute11
3980 || variable_e;
3981 ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_c_attribute12' THEN
3982 l_material_data := l_material_data
3983 || variable_b
3984 || l_selected_fields(i).variable_name
3985 || '">'
3986 || v_material_cur.lot_c_attribute12
3987 || variable_e;
3988 ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_c_attribute13' THEN
3989 l_material_data := l_material_data
3990 || variable_b
3991 || l_selected_fields(i).variable_name
3992 || '">'
3993 || v_material_cur.lot_c_attribute13
3994 || variable_e;
3995 ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_c_attribute14' THEN
3996 l_material_data := l_material_data
3997 || variable_b
3998 || l_selected_fields(i).variable_name
3999 || '">'
4000 || v_material_cur.lot_c_attribute14
4001 || variable_e;
4002 ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_c_attribute15' THEN
4003 l_material_data := l_material_data
4004 || variable_b
4005 || l_selected_fields(i).variable_name
4006 || '">'
4007 || v_material_cur.lot_c_attribute15
4008 || variable_e;
4009 ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_c_attribute16' THEN
4010 l_material_data := l_material_data
4011 || variable_b
4012 || l_selected_fields(i).variable_name
4013 || '">'
4014 || v_material_cur.lot_c_attribute16
4015 || variable_e;
4016 ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_c_attribute17' THEN
4017 l_material_data := l_material_data
4018 || variable_b
4019 || l_selected_fields(i).variable_name
4020 || '">'
4021 || v_material_cur.lot_c_attribute17
4022 || variable_e;
4023 ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_c_attribute18' THEN
4024 l_material_data := l_material_data
4025 || variable_b
4026 || l_selected_fields(i).variable_name
4027 || '">'
4028 || v_material_cur.lot_c_attribute18
4029 || variable_e;
4030 ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_c_attribute19' THEN
4031 l_material_data := l_material_data
4032 || variable_b
4033 || l_selected_fields(i).variable_name
4034 || '">'
4035 || v_material_cur.lot_c_attribute19
4036 || variable_e;
4037 ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_c_attribute20' THEN
4038 l_material_data := l_material_data
4039 || variable_b
4040 || l_selected_fields(i).variable_name
4041 || '">'
4042 || v_material_cur.lot_c_attribute20
4043 || variable_e;
4044 ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_d_attribute1' THEN
4045 l_material_data := l_material_data
4046 || variable_b
4047 || l_selected_fields(i).variable_name
4048 || '">'
4049 || v_material_cur.lot_d_attribute1
4050 || variable_e;
4051 ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_d_attribute2' THEN
4052 l_material_data := l_material_data
4053 || variable_b
4054 || l_selected_fields(i).variable_name
4055 || '">'
4056 || v_material_cur.lot_d_attribute2
4057 || variable_e;
4058 ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_d_attribute3' THEN
4059 l_material_data := l_material_data
4060 || variable_b
4061 || l_selected_fields(i).variable_name
4062 || '">'
4063 || v_material_cur.lot_d_attribute3
4064 || variable_e;
4065 ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_d_attribute4' THEN
4066 l_material_data := l_material_data
4067 || variable_b
4068 || l_selected_fields(i).variable_name
4069 || '">'
4070 || v_material_cur.lot_d_attribute4
4071 || variable_e;
4072 ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_d_attribute5' THEN
4073 l_material_data := l_material_data
4074 || variable_b
4075 || l_selected_fields(i).variable_name
4076 || '">'
4077 || v_material_cur.lot_d_attribute5
4078 || variable_e;
4079 ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_d_attribute6' THEN
4080 l_material_data := l_material_data
4081 || variable_b
4082 || l_selected_fields(i).variable_name
4083 || '">'
4084 || v_material_cur.lot_d_attribute6
4085 || variable_e;
4086 ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_d_attribute7' THEN
4087 l_material_data := l_material_data
4088 || variable_b
4089 || l_selected_fields(i).variable_name
4090 || '">'
4091 || v_material_cur.lot_d_attribute7
4092 || variable_e;
4093 ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_d_attribute8' THEN
4094 l_material_data := l_material_data
4095 || variable_b
4096 || l_selected_fields(i).variable_name
4097 || '">'
4098 || v_material_cur.lot_d_attribute8
4099 || variable_e;
4100 ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_d_attribute9' THEN
4101 l_material_data := l_material_data
4102 || variable_b
4103 || l_selected_fields(i).variable_name
4104 || '">'
4105 || v_material_cur.lot_d_attribute9
4106 || variable_e;
4107 ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_d_attribute10' THEN
4108 l_material_data := l_material_data
4109 || variable_b
4110 || l_selected_fields(i).variable_name
4111 || '">'
4112 || v_material_cur.lot_d_attribute10
4113 || variable_e;
4114 ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_n_attribute1' THEN
4115 l_material_data := l_material_data
4116 || variable_b
4117 || l_selected_fields(i).variable_name
4118 || '">'
4119 || v_material_cur.lot_n_attribute1
4120 || variable_e;
4121 ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_n_attribute2' THEN
4122 l_material_data := l_material_data
4123 || variable_b
4124 || l_selected_fields(i).variable_name
4125 || '">'
4126 || v_material_cur.lot_n_attribute2
4127 || variable_e;
4128 ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_n_attribute3' THEN
4129 l_material_data := l_material_data
4130 || variable_b
4131 || l_selected_fields(i).variable_name
4132 || '">'
4133 || v_material_cur.lot_n_attribute3
4134 || variable_e;
4135 ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_n_attribute4' THEN
4136 l_material_data := l_material_data
4137 || variable_b
4138 || l_selected_fields(i).variable_name
4139 || '">'
4140 || v_material_cur.lot_n_attribute4
4141 || variable_e;
4142 ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_n_attribute5' THEN
4143 l_material_data := l_material_data
4144 || variable_b
4145 || l_selected_fields(i).variable_name
4146 || '">'
4147 || v_material_cur.lot_n_attribute5
4148 || variable_e;
4149 ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_n_attribute6' THEN
4150 l_material_data := l_material_data
4151 || variable_b
4152 || l_selected_fields(i).variable_name
4153 || '">'
4154 || v_material_cur.lot_n_attribute6
4155 || variable_e;
4156 ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_n_attribute7' THEN
4157 l_material_data := l_material_data
4158 || variable_b
4159 || l_selected_fields(i).variable_name
4160 || '">'
4161 || v_material_cur.lot_n_attribute7
4162 || variable_e;
4163 ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_n_attribute8' THEN
4164 l_material_data := l_material_data
4165 || variable_b
4166 || l_selected_fields(i).variable_name
4167 || '">'
4168 || v_material_cur.lot_n_attribute8
4169 || variable_e;
4170 ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_n_attribute9' THEN
4171 l_material_data := l_material_data
4172 || variable_b
4173 || l_selected_fields(i).variable_name
4174 || '">'
4175 || v_material_cur.lot_n_attribute9
4176 || variable_e;
4177 ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_n_attribute10' THEN
4178 l_material_data := l_material_data
4179 || variable_b
4180 || l_selected_fields(i).variable_name
4181 || '">'
4182 || v_material_cur.lot_n_attribute10
4183 || variable_e;
4184 ELSIF LOWER(l_selected_fields(i).column_name) =
4185 'lot_country_of_origin' THEN
4186 l_material_data := l_material_data
4187 || variable_b
4188 || l_selected_fields(i).variable_name
4189 || '">'
4190 || v_material_cur.lot_country_of_origin
4191 || variable_e;
4192 ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_grade_code' THEN
4193 l_material_data := l_material_data
4194 || variable_b
4195 || l_selected_fields(i).variable_name
4196 || '">'
4197 || v_material_cur.lot_grade_code
4198 || variable_e;
4199 ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_origination_date' THEN
4200 l_material_data := l_material_data
4201 || variable_b
4202 || l_selected_fields(i).variable_name
4203 || '">'
4204 || v_material_cur.lot_origination_date
4205 || variable_e;
4206 ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_date_code' THEN
4207 l_material_data := l_material_data
4208 || variable_b
4209 || l_selected_fields(i).variable_name
4210 || '">'
4211 || v_material_cur.lot_date_code
4212 || variable_e;
4213 ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_change_date' THEN
4214 l_material_data := l_material_data
4215 || variable_b
4216 || l_selected_fields(i).variable_name
4217 || '">'
4218 || v_material_cur.lot_change_date
4219 || variable_e;
4220 ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_age' THEN
4221 l_material_data := l_material_data
4222 || variable_b
4223 || l_selected_fields(i).variable_name
4224 || '">'
4225 || v_material_cur.lot_age
4226 || variable_e;
4227 ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_retest_date' THEN
4228 l_material_data := l_material_data
4229 || variable_b
4230 || l_selected_fields(i).variable_name
4231 || '">'
4232 || v_material_cur.lot_retest_date
4233 || variable_e;
4234 ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_maturity_date' THEN
4235 l_material_data := l_material_data
4236 || variable_b
4237 || l_selected_fields(i).variable_name
4238 || '">'
4239 || v_material_cur.lot_maturity_date
4240 || variable_e;
4241 ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_item_size' THEN
4242 l_material_data := l_material_data
4243 || variable_b
4244 || l_selected_fields(i).variable_name
4245 || '">'
4246 || v_material_cur.lot_item_size
4247 || variable_e;
4248 ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_color' THEN
4249 l_material_data := l_material_data
4250 || variable_b
4251 || l_selected_fields(i).variable_name
4252 || '">'
4253 || v_material_cur.lot_color
4254 || variable_e;
4255 ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_volume' THEN
4256 l_material_data := l_material_data
4257 || variable_b
4258 || l_selected_fields(i).variable_name
4259 || '">'
4260 || v_material_cur.lot_volume
4261 || variable_e;
4262 ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_volume_uom' THEN
4263 l_material_data := l_material_data
4264 || variable_b
4265 || l_selected_fields(i).variable_name
4266 || '">'
4267 || v_material_cur.lot_volume_uom
4268 || variable_e;
4269 ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_place_of_origin' THEN
4270 l_material_data := l_material_data
4271 || variable_b
4272 || l_selected_fields(i).variable_name
4273 || '">'
4274 || v_material_cur.lot_place_of_origin
4275 || variable_e;
4276 ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_best_by_date' THEN
4277 l_material_data := l_material_data
4278 || variable_b
4279 || l_selected_fields(i).variable_name
4280 || '">'
4281 || v_material_cur.lot_best_by_date
4282 || variable_e;
4283 ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_length' THEN
4284 l_material_data := l_material_data
4285 || variable_b
4286 || l_selected_fields(i).variable_name
4287 || '">'
4288 || v_material_cur.lot_length
4289 || variable_e;
4290 ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_length_uom' THEN
4291 l_material_data := l_material_data
4292 || variable_b
4293 || l_selected_fields(i).variable_name
4294 || '">'
4295 || v_material_cur.lot_length_uom
4296 || variable_e;
4297 ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_recycled_cont' THEN
4298 l_material_data := l_material_data
4299 || variable_b
4300 || l_selected_fields(i).variable_name
4301 || '">'
4302 || v_material_cur.lot_recycled_cont
4303 || variable_e;
4304 ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_thickness' THEN
4305 l_material_data := l_material_data
4306 || variable_b
4307 || l_selected_fields(i).variable_name
4308 || '">'
4309 || v_material_cur.lot_thickness
4310 || variable_e;
4311 ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_thickness_uom' THEN
4312 l_material_data := l_material_data
4313 || variable_b
4314 || l_selected_fields(i).variable_name
4315 || '">'
4316 || v_material_cur.lot_thickness_uom
4317 || variable_e;
4318 ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_width' THEN
4319 l_material_data := l_material_data
4320 || variable_b
4321 || l_selected_fields(i).variable_name
4322 || '">'
4323 || v_material_cur.lot_width
4324 || variable_e;
4325 ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_width_uom' THEN
4326 l_material_data := l_material_data
4327 || variable_b
4328 || l_selected_fields(i).variable_name
4329 || '">'
4330 || v_material_cur.lot_width_uom
4331 || variable_e;
4332 ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_curl' THEN
4333 l_material_data := l_material_data
4334 || variable_b
4335 || l_selected_fields(i).variable_name
4336 || '">'
4337 || v_material_cur.lot_curl
4338 || variable_e;
4339 ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_vendor' THEN
4340 l_material_data := l_material_data
4341 || variable_b
4342 || l_selected_fields(i).variable_name
4343 || '">'
4344 || v_material_cur.lot_vendor
4345 || variable_e;
4346 ELSIF LOWER(l_selected_fields(i).column_name) = 'item_hazard_class' THEN
4347 l_material_data := l_material_data
4348 || variable_b
4349 || l_selected_fields(i).variable_name
4350 || '">'
4351 || v_material_cur.item_hazard_class
4352 || variable_e;
4353 ELSIF LOWER(l_selected_fields(i).column_name) =
4354 'item_attribute_category' THEN
4355 l_material_data := l_material_data
4356 || variable_b
4357 || l_selected_fields(i).variable_name
4358 || '">'
4359 || v_material_cur.item_attribute_category
4360 || variable_e;
4361 ELSIF LOWER(l_selected_fields(i).column_name) = 'item_attribute1' THEN
4362 l_material_data := l_material_data
4363 || variable_b
4364 || l_selected_fields(i).variable_name
4365 || '">'
4366 || v_material_cur.item_attribute1
4367 || variable_e;
4368 ELSIF LOWER(l_selected_fields(i).column_name) = 'item_attribute2' THEN
4369 l_material_data := l_material_data
4370 || variable_b
4371 || l_selected_fields(i).variable_name
4372 || '">'
4373 || v_material_cur.item_attribute2
4374 || variable_e;
4375 ELSIF LOWER(l_selected_fields(i).column_name) = 'item_attribute3' THEN
4376 l_material_data := l_material_data
4377 || variable_b
4378 || l_selected_fields(i).variable_name
4379 || '">'
4380 || v_material_cur.item_attribute3
4381 || variable_e;
4382 ELSIF LOWER(l_selected_fields(i).column_name) = 'item_attribute4' THEN
4383 l_material_data := l_material_data
4384 || variable_b
4385 || l_selected_fields(i).variable_name
4386 || '">'
4387 || v_material_cur.item_attribute4
4388 || variable_e;
4389 ELSIF LOWER(l_selected_fields(i).column_name) = 'item_attribute5' THEN
4390 l_material_data := l_material_data
4391 || variable_b
4392 || l_selected_fields(i).variable_name
4393 || '">'
4394 || v_material_cur.item_attribute5
4395 || variable_e;
4396 ELSIF LOWER(l_selected_fields(i).column_name) = 'item_attribute6' THEN
4397 l_material_data := l_material_data
4398 || variable_b
4399 || l_selected_fields(i).variable_name
4400 || '">'
4401 || v_material_cur.item_attribute6
4402 || variable_e;
4403 ELSIF LOWER(l_selected_fields(i).column_name) = 'item_attribute7' THEN
4404 l_material_data := l_material_data
4405 || variable_b
4406 || l_selected_fields(i).variable_name
4407 || '">'
4408 || v_material_cur.item_attribute7
4409 || variable_e;
4410 ELSIF LOWER(l_selected_fields(i).column_name) = 'item_attribute8' THEN
4411 l_material_data := l_material_data
4412 || variable_b
4413 || l_selected_fields(i).variable_name
4414 || '">'
4415 || v_material_cur.item_attribute8
4416 || variable_e;
4417 ELSIF LOWER(l_selected_fields(i).column_name) = 'item_attribute9' THEN
4418 l_material_data := l_material_data
4419 || variable_b
4420 || l_selected_fields(i).variable_name
4421 || '">'
4422 || v_material_cur.item_attribute9
4423 || variable_e;
4424 ELSIF LOWER(l_selected_fields(i).column_name) = 'item_attribute10' THEN
4425 l_material_data := l_material_data
4426 || variable_b
4427 || l_selected_fields(i).variable_name
4428 || '">'
4429 || v_material_cur.item_attribute10
4430 || variable_e;
4431 ELSIF LOWER(l_selected_fields(i).column_name) = 'item_attribute11' THEN
4432 l_material_data := l_material_data
4433 || variable_b
4434 || l_selected_fields(i).variable_name
4435 || '">'
4436 || v_material_cur.item_attribute11
4437 || variable_e;
4438 ELSIF LOWER(l_selected_fields(i).column_name) = 'item_attribute12' THEN
4439 l_material_data := l_material_data
4440 || variable_b
4441 || l_selected_fields(i).variable_name
4442 || '">'
4443 || v_material_cur.item_attribute12
4444 || variable_e;
4445 ELSIF LOWER(l_selected_fields(i).column_name) = 'item_attribute13' THEN
4446 l_material_data := l_material_data
4447 || variable_b
4448 || l_selected_fields(i).variable_name
4449 || '">'
4450 || v_material_cur.item_attribute13
4451 || variable_e;
4452 ELSIF LOWER(l_selected_fields(i).column_name) = 'item_attribute14' THEN
4453 l_material_data := l_material_data
4454 || variable_b
4455 || l_selected_fields(i).variable_name
4456 || '">'
4457 || v_material_cur.item_attribute14
4458 || variable_e;
4459 ELSIF LOWER(l_selected_fields(i).column_name) = 'item_attribute15' THEN
4460 l_material_data := l_material_data
4461 || variable_b
4462 || l_selected_fields(i).variable_name
4463 || '">'
4464 || v_material_cur.item_attribute15
4465 || variable_e;
4466 --START of Fix For Bug: 4907062
4467 -- Project_Number and Task Number fields are added newly.
4468 ELSIF LOWER(l_selected_fields(i).column_name) = 'project_number' THEN
4469 l_material_data := l_material_data
4470 || variable_b
4471 || l_selected_fields(i).variable_name
4472 || '">'
4473 || v_material_cur.project_number
4474 || variable_e;
4475 ELSIF LOWER(l_selected_fields(i).column_name) = 'task_number' THEN
4476 l_material_data := l_material_data
4477 || variable_b
4478 || l_selected_fields(i).variable_name
4479 || '">'
4480 || v_material_cur.task_number
4481 || variable_e;
4482 --END of Fix For Bug: 4907062
4483 ELSIF LOWER(l_selected_fields(i).column_name) = 'project' THEN
4484 l_material_data := l_material_data
4485 || variable_b
4486 || l_selected_fields(i).variable_name
4487 || '">'
4488 || v_material_cur.project
4489 || variable_e;
4490 ELSIF LOWER(l_selected_fields(i).column_name) = 'task' THEN
4491 l_material_data := l_material_data
4492 || variable_b
4493 || l_selected_fields(i).variable_name
4494 || '">'
4495 || v_material_cur.task
4496 || variable_e;
4497 ELSIF LOWER(l_selected_fields(i).column_name) = 'receipt_num' THEN
4498 l_material_data := l_material_data
4499 || variable_b
4500 || l_selected_fields(i).variable_name
4501 || '">'
4502 || l_receipt_number
4503 || variable_e;
4504 ELSIF LOWER(l_selected_fields(i).column_name) = 'po_line_num' THEN
4505 l_material_data := l_material_data
4506 || variable_b
4507 || l_selected_fields(i).variable_name
4508 || '">'
4509 || l_po_line_number
4510 || variable_e;
4511 ELSIF LOWER(l_selected_fields(i).column_name) = 'quan_ordered' THEN
4512 l_material_data := l_material_data
4513 || variable_b
4514 || l_selected_fields(i).variable_name
4515 || '">'
4516 || l_quantity_ordered
4517 || variable_e;
4518 ELSIF LOWER(l_selected_fields(i).column_name) = 'supp_part_num' THEN
4519 l_material_data := l_material_data
4520 || variable_b
4521 || l_selected_fields(i).variable_name
4522 || '">'
4523 || l_supplier_part_number
4524 || variable_e;
4525 ELSIF LOWER(l_selected_fields(i).column_name) = 'supp_name' THEN
4526 l_material_data := l_material_data
4527 || variable_b
4528 || l_selected_fields(i).variable_name
4529 || '">'
4530 || l_supplier_name
4531 || variable_e;
4532 ELSIF LOWER(l_selected_fields(i).column_name) = 'supp_site' THEN
4533 l_material_data := l_material_data
4534 || variable_b
4535 || l_selected_fields(i).variable_name
4536 || '">'
4537 || l_supplier_site
4538 || variable_e;
4539 ELSIF LOWER(l_selected_fields(i).column_name) = 'requestor' THEN
4540 l_material_data := l_material_data
4541 || variable_b
4542 || l_selected_fields(i).variable_name
4543 || '">'
4544 || l_requestor
4545 || variable_e;
4546 ELSIF LOWER(l_selected_fields(i).column_name) = 'deliver_to_loc' THEN
4547 l_material_data := l_material_data
4548 || variable_b
4549 || l_selected_fields(i).variable_name
4550 || '">'
4551 || l_deliver_to_location
4552 || variable_e;
4553 ELSIF LOWER(l_selected_fields(i).column_name) = 'loc_id' THEN
4554 l_material_data := l_material_data
4555 || variable_b
4556 || l_selected_fields(i).variable_name
4557 || '">'
4558 || l_location_code
4559 || variable_e;
4560 ELSIF LOWER(l_selected_fields(i).column_name) = 'note_to_receiver' THEN
4561 l_material_data := l_material_data
4562 || variable_b
4563 || l_selected_fields(i).variable_name
4564 || '">'
4565 || l_note_to_receiver
4566 || variable_e;
4567 ELSIF LOWER(l_selected_fields(i).column_name) = 'rec_routing' THEN
4568 l_material_data := l_material_data
4569 || variable_b
4570 || l_selected_fields(i).variable_name
4571 || '">'
4572 || l_routing_name
4573 || variable_e;
4574 ELSIF LOWER(l_selected_fields(i).column_name) = 'po_num' THEN
4575 l_material_data := l_material_data
4576 || variable_b
4577 || l_selected_fields(i).variable_name
4578 || '">'
4579 || l_purchase_order
4580 || variable_e;
4581 ELSIF LOWER(l_selected_fields(i).column_name) = 'subinventory_code' THEN
4582 l_material_data := l_material_data
4583 || variable_b
4584 || l_selected_fields(i).variable_name
4585 || '">'
4586 || v_material_cur.subinventory_code
4587 || variable_e;
4588 ELSIF LOWER(l_selected_fields(i).column_name) = 'locator' THEN
4589 l_material_data := l_material_data
4590 || variable_b
4591 || l_selected_fields(i).variable_name
4592 || '">'
4593 || v_material_cur.LOCATOR
4594 || variable_e;
4595 ELSIF LOWER(l_selected_fields(i).column_name) = 'organization' THEN
4596 l_material_data := l_material_data
4597 || variable_b
4598 || l_selected_fields(i).variable_name
4599 || '">'
4600 || nvl(v_material_cur.ORGANIZATION,l_organization_id) /* Modified for the bug # 4708752*/
4601 || variable_e;
4602 --Bug 4891916- Added for the field Cycle Count Name */
4603 ELSIF LOWER(l_selected_fields(i).column_name) = 'cycle_count_name' THEN
4604 l_material_data := l_material_data
4605 || variable_b
4606 || l_selected_fields(i).variable_name
4607 || '">' || l_cycle_count_name
4608 || variable_e;
4609 --End of fix for Bug 4891916
4610
4611 -- Added for UCC 128 J Bug #3067059
4612 ELSIF LOWER(l_selected_fields(i).column_name) = 'gtin' THEN
4613 l_material_data := l_material_data
4614 || variable_b
4615 || l_selected_fields(i).variable_name
4616 || '">'
4617 || l_gtin
4618 || variable_e;
4619 ELSIF LOWER(l_selected_fields(i).column_name) = 'gtin_description' THEN
4620 l_material_data := l_material_data
4621 || variable_b
4622 || l_selected_fields(i).variable_name
4623 || '">'
4624 || l_gtin_desc
4625 || variable_e;
4626 ELSIF LOWER(l_selected_fields(i).column_name) = 'wip_entity_name' THEN
4627 l_material_data := l_material_data
4628 || variable_b
4629 || l_selected_fields(i).variable_name
4630 || '">'
4631 || l_wip_entity_name
4632 || variable_e;
4633 ELSIF LOWER(l_selected_fields(i).column_name) = 'wip_description' THEN
4634 l_material_data := l_material_data
4635 || variable_b
4636 || l_selected_fields(i).variable_name
4637 || '">'
4638 || l_wip_description
4639 || variable_e;
4640 ELSIF LOWER(l_selected_fields(i).column_name) = 'wip_operation_seq_num' THEN
4641 l_material_data := l_material_data
4642 || variable_b
4643 || l_selected_fields(i).variable_name
4644 || '">'
4645 || l_wip_op_seq_num
4646 || variable_e;
4647 ELSIF LOWER(l_selected_fields(i).column_name) = 'osp_department_code' THEN
4648 l_material_data := l_material_data
4649 || variable_b
4650 || l_selected_fields(i).variable_name
4651 || '">'
4652 || l_osp_dept_code
4653 || variable_e;
4654 ELSIF LOWER(l_selected_fields(i).column_name) = 'bom_resource' THEN
4655 l_material_data := l_material_data
4656 || variable_b
4657 || l_selected_fields(i).variable_name
4658 || '">'
4659 || l_bom_resource_code
4660 || variable_e;
4661
4662 -- invconv fabdi start
4663 ELSIF LOWER(l_selected_fields(i).column_name) = 'parent_lot_number' THEN
4664 l_material_data := l_material_data
4665 || variable_b
4666 || l_selected_fields(i).variable_name
4667 || '">'
4668 || v_material_cur.parent_lot_number
4669 || variable_e;
4670
4671 ELSIF LOWER(l_selected_fields(i).column_name) = 'expiration_action_date' THEN
4672 l_material_data := l_material_data
4673 || variable_b
4674 || l_selected_fields(i).variable_name
4675 || '">'
4676 || v_material_cur.expiration_action_date
4677 || variable_e;
4678
4679 ELSIF LOWER(l_selected_fields(i).column_name) = 'origination_type' THEN
4680 l_material_data := l_material_data
4681 || variable_b
4682 || l_selected_fields(i).variable_name
4683 || '">'
4684 || get_origination_type (v_material_cur.origination_type)
4685 || variable_e;
4686
4687 ELSIF LOWER(l_selected_fields(i).column_name) = 'hold_date' THEN
4688 l_material_data := l_material_data
4689 || variable_b
4690 || l_selected_fields(i).variable_name
4691 || '">'
4692 || v_material_cur.hold_date
4693 || variable_e;
4694
4695 ELSIF LOWER(l_selected_fields(i).column_name) = 'secondary_transaction_quantity' THEN
4696 l_material_data := l_material_data
4697 || variable_b
4698 || l_selected_fields(i).variable_name
4699 || '">'
4700 || v_material_cur.secondary_quantity
4701 || variable_e;
4702
4703 ELSIF LOWER(l_selected_fields(i).column_name) = 'secondary_uom_code' THEN
4704 l_material_data := l_material_data
4705 || variable_b
4706 || l_selected_fields(i).variable_name
4707 || '">'
4708 || v_material_cur.secondary_uom
4709 || variable_e;
4710
4711 ELSIF LOWER(l_selected_fields(i).column_name) = 'expiration_action_code' THEN
4712 l_material_data := l_material_data
4713 || variable_b
4714 || l_selected_fields(i).variable_name
4715 || '">'
4716 || v_material_cur.expiration_action_code
4717 || variable_e;
4718
4719 ELSIF LOWER(l_selected_fields(i).column_name) = 'supplier_lot_number' THEN
4720 l_material_data := l_material_data
4721 || variable_b
4722 || l_selected_fields(i).variable_name
4723 || '">'
4724 || v_material_cur.supplier_lot_number
4725 || variable_e;
4726 -- invconv fabdi end
4727
4728 -- Added for R12 RFID Compliance project
4729 -- New field : EPC
4730 -- EPC is generated once for each LPN
4731 ELSIF LOWER(l_selected_fields(i).column_name) = 'epc' THEN
4732 l_material_data := l_material_data
4733 || variable_b
4734 || l_selected_fields(i).variable_name
4735 || '">'
4736 || l_epc
4737 || variable_e;
4738
4739 l_label_err_msg := l_epc_ret_msg;
4740 IF l_epc_ret_status = 'U' THEN
4741 l_label_status := INV_LABEL.G_ERROR;
4742 ELSIF l_epc_ret_status = 'E' THEN
4743 l_label_status := INV_LABEL.G_WARNING;
4744 END IF;
4745 END IF;
4746 END LOOP;
4747
4748 l_material_data := l_material_data || label_e;
4749 x_variable_content(l_label_index).label_content := l_material_data;
4750 x_variable_content(l_label_index).label_request_id := l_label_request_id;
4751 x_variable_content(l_label_index).label_status := l_label_status;
4752 ------------------------Start of changes for Custom Labels project code------------------
4753
4754 -- Fix for bug: 4179593 Start
4755 IF (l_CustSqlWarnFlagSet) THEN
4756 l_custom_sql_ret_status := INV_LABEL.G_WARNING;
4757 l_custom_sql_ret_msg := l_CustSqlWarnMsg;
4758 END IF;
4759
4760 IF (l_CustSqlErrFlagSet) THEN
4761 l_custom_sql_ret_status := FND_API.G_RET_STS_ERROR;
4762 l_custom_sql_ret_msg := l_CustSqlErrMsg;
4763 END IF;
4764 -- Fix for bug: 4179593 End
4765 IF (l_CustSqlErrFlagSet) THEN
4766 x_variable_content(l_label_index).label_status := l_custom_sql_ret_status;
4767 END IF;
4768 x_variable_content(l_label_index).error_message := l_custom_sql_ret_msg|| ' ' || l_label_err_msg;
4769 ------------------------End of this changes for Custom Labels project code---------------
4770 l_label_index := l_label_index + 1;
4771
4772 IF (l_debug = 1) THEN
4773 TRACE('l_column_name_list"'||l_column_name_list);
4774 END IF;
4775
4776 --Clear all required variable values
4777 l_material_data := '';
4778 l_custom_sql_ret_status := NULL;
4779 l_custom_sql_ret_msg := NULL;
4780
4781 END LOOP; --END epc LOOP
4782
4783
4784
4785 --Clear all required variable values
4786 l_material_data := '';
4787 l_custom_sql_ret_status := NULL;
4788 l_custom_sql_ret_msg := NULL;
4789
4790
4791 IF (l_debug = 1) THEN
4792 TRACE(' Done with Label formats in the current label-set');
4793 END IF;
4794
4795 END LOOP; --for formats in label-set
4796
4797 <<nextlabel>>
4798
4799 l_material_data := '';
4800 l_label_request_id := NULL;
4801 ------------------------Start of changes for Custom Labels project code------------------
4802 l_custom_sql_ret_status := NULL;
4803 l_custom_sql_ret_msg := NULL;
4804 ------------------------End of this changes for Custom Labels project code---------------
4805 END LOOP;
4806
4807 /* Bug 6504959-Closing the cursor */
4808 IF c_material_cur%ISOPEN THEN
4809 CLOSE c_material_cur;
4810 END IF;
4811
4812 IF l_patch_level = 1 AND (p_label_type_info.business_flow_code IN (1,2,3,4)) THEN
4813 IF (l_debug = 1) THEN
4814 TRACE(' Within patchset level J');
4815 TRACE(' If business flow code in 1, 2, 3,4 within patchset J');
4816 END IF;
4817
4818 /* Patchset J - Use the new cursor rt_material_cur. This cursor replaces
4819 * RTI_MATERIAL_LPN_CUR and RTI_MATERIAL_MTLT_CUR in patchset J, due to receiving tables
4820 * changes. Also, earlier, receiving transaction records were created separately for
4821 * INV and WMS organizations, which is not the case now.
4822 * Open the cursor rt_material_cur. This cursor fetches data irrespective
4823 * of whether it is a WMS org or INV org.
4824 */
4825 -- Bug 4516067, created new cursor for putaway and deliver
4826 -- Fetch from rt_material_cur or rt_putaway_deliver_cur based on business flow code
4827 IF (p_label_type_info.business_flow_code IN (1,2)) THEN
4828 FETCH rt_material_cur INTO
4829 l_inventory_item_id
4830 , l_organization_id
4831 , l_lot_number
4832 , l_cost_group_id
4833 , l_project_id
4834 , l_task_id
4835 -- Added by joabraha for bug 3472150
4836 , l_receipt_number
4837 --
4838 , l_quantity
4839 , l_secondary_transaction_qty
4840 , l_uom
4841 , l_secondary_uom_code
4842 , l_revision
4843 , l_purchase_order
4844 , l_shipment_num
4845 , l_po_line_number
4846 , l_quantity_ordered
4847 , l_supplier_part_number
4848 , l_vendor_id
4849 , l_supplier_name
4850 , l_vendor_site_id
4851 , l_supplier_site
4852 , l_requestor
4853 , l_deliver_to_location
4854 , l_location_code
4855 , l_note_to_receiver
4856 , l_routing_name
4857 , l_item_description
4858 , l_subinventory_code
4859 , l_locator_id
4860 , l_wip_entity_name
4861 , l_wip_description
4862 , l_wip_op_seq_num
4863 , l_osp_dept_code
4864 , l_bom_resource_id
4865 , l_lpn_context
4866 , l_lpn_id
4867 , l_routing_header_id; --bug 4916450
4868
4869 IF rt_material_cur%NOTFOUND THEN
4870 IF (l_debug = 1) THEN
4871 TRACE(' No more material found for this given Interface Transaction ID:' || p_transaction_id);
4872 END IF;
4873 -- Fix for 4348641
4874 -- Following 2 assignment statements that were previously
4875 -- inside the above IF-ENDIF block is now moved outside.
4876 l_inventory_item_id := NULL;
4877 l_item_description := NULL;
4878 CLOSE rt_material_cur;
4879 --RETURN;
4880 ELSE
4881 IF (l_debug = 1) THEN
4882 TRACE(' Got next Item for Interface Transaction ID:' || p_transaction_id);
4883 END IF;
4884 END IF;
4885 ELSIF (p_label_type_info.business_flow_code IN (3,4)) THEN
4886 FETCH rt_putaway_deliver_cur INTO
4887 l_inventory_item_id
4888 , l_organization_id
4889 , l_lot_number
4890 , l_cost_group_id
4891 , l_project_id
4892 , l_task_id
4893 -- Added by joabraha for bug 3472150
4894 , l_receipt_number
4895 --
4896 , l_quantity
4897 , l_secondary_transaction_qty
4898 , l_uom
4899 , l_secondary_uom_code
4900 , l_revision
4901 , l_purchase_order
4902 , l_shipment_num
4903 , l_po_line_number
4904 , l_quantity_ordered
4905 , l_supplier_part_number
4906 , l_vendor_id
4907 , l_supplier_name
4908 , l_vendor_site_id
4909 , l_supplier_site
4910 , l_requestor
4911 , l_deliver_to_location
4912 , l_location_code
4913 , l_note_to_receiver
4914 , l_routing_name
4915 , l_item_description
4916 , l_subinventory_code
4917 , l_locator_id
4918 , l_wip_entity_name
4919 , l_wip_description
4920 , l_wip_op_seq_num
4921 , l_osp_dept_code
4922 , l_bom_resource_id
4923 , l_lpn_context
4924 , l_lpn_id
4925 , l_routing_header_id; --bug 4916450
4926
4927 IF rt_putaway_deliver_cur%NOTFOUND THEN
4928 IF (l_debug = 1) THEN
4929 TRACE(' No more material found for this given Interface Transaction ID:' || p_transaction_id);
4930 END IF;
4931 -- Fix for 4277218 Begin:
4932 -- Following 2 assignment statements that were previously
4933 -- inside the above IF-ENDIF block is now moved outside.
4934 l_inventory_item_id := NULL;
4935 l_item_description := NULL;
4936 -- Fix for 4277218 End
4937 CLOSE rt_putaway_deliver_cur;
4938 --RETURN;
4939 ELSE
4940 IF (l_debug = 1) THEN
4941 TRACE(' Got next Item for Interface Transaction ID:' || p_transaction_id);
4942 END IF;
4943 END IF;
4944 END IF;
4945
4946 FETCH get_resource_dept_code_cur INTO l_bom_resource_code, l_osp_dept_code;
4947 IF get_resource_dept_code_cur%NOTFOUND THEN
4948 IF (l_debug = 1) THEN
4949 TRACE(' No more Resource and Dept code found for Resource ID: ' || l_bom_resource_id);
4950 END IF;
4951 END IF;
4952
4953 IF (l_debug = 1) THEN
4954 TRACE(' End of Patchset J check');
4955 END IF;
4956
4957 ELSE
4958 -- This fetch for receiving is for multiple lots.
4959 IF ((p_label_type_info.business_flow_code IN (1))
4960 AND (l_is_wms_org = TRUE)
4961 AND l_patch_level = 0
4962 ) THEN
4963 -- Receipt and Inspection, WMS org, getting the next lot number from l_material_input
4964 FETCH rti_material_lpn_cur INTO l_inventory_item_id
4965 , l_organization_id
4966 , l_lot_number
4967 , l_cost_group_id
4968 , l_project_id
4969 , l_task_id
4970 -- Added by joabraha for bug 3472150
4971 , l_receipt_number
4972 --
4973 , l_quantity
4974 , l_uom
4975 , l_revision
4976 , l_lpn_id
4977 , l_purchase_order
4978 , l_po_line_number
4979 , l_quantity_ordered
4980 , l_supplier_part_number
4981 , l_vendor_id
4982 , l_supplier_name
4983 , l_vendor_site_id
4984 , l_supplier_site
4985 , l_requestor
4986 , l_deliver_to_location
4987 , l_location_code
4988 , l_note_to_receiver
4989 , l_routing_name
4990 , l_item_description
4991 , l_subinventory_code
4992 , l_locator_id
4993 , l_wip_entity_name
4994 , l_wip_description
4995 , l_wip_op_seq_num
4996 , l_osp_dept_code
4997 , l_bom_resource_id;
4998
4999 IF rti_material_lpn_cur%NOTFOUND THEN
5000 CLOSE rti_material_lpn_cur;
5001 l_inventory_item_id := NULL;
5002 l_item_description := NULL;
5003 END IF;
5004 ELSIF ((p_label_type_info.business_flow_code IN (2))
5005 AND (l_is_wms_org = TRUE)
5006 AND l_patch_level = 0
5007 ) THEN
5008 -- Receipt and Inspection, WMS org, getting the next lot number from l_material_input
5009 FETCH rti_material_lpn_inspec_cur INTO l_inventory_item_id
5010 , l_organization_id
5011 , l_lot_number
5012 , l_cost_group_id
5013 , l_project_id
5014 , l_task_id
5015 -- Added by joabraha for bug 3472150
5016 , l_receipt_number
5017 --
5018 , l_quantity
5019 , l_uom
5020 , l_revision
5021 , l_lpn_id
5022 , l_purchase_order
5023 , l_po_line_number
5024 , l_quantity_ordered
5025 , l_supplier_part_number
5026 , l_vendor_id
5027 , l_supplier_name
5028 , l_vendor_site_id
5029 , l_supplier_site
5030 , l_requestor
5031 , l_deliver_to_location
5032 , l_location_code
5033 , l_note_to_receiver
5034 , l_routing_name
5035 , l_item_description
5036 , l_subinventory_code
5037 , l_locator_id
5038 , l_wip_entity_name
5039 , l_wip_description
5040 , l_wip_op_seq_num
5041 , l_osp_dept_code
5042 , l_bom_resource_id;
5043
5044 IF rti_material_lpn_inspec_cur%NOTFOUND THEN
5045 CLOSE rti_material_lpn_inspec_cur;
5046 l_inventory_item_id := NULL;
5047 l_item_description := NULL;
5048 END IF;
5049 ELSIF ((p_label_type_info.business_flow_code IN (3))
5050 AND (l_is_wms_org = TRUE)
5051 )
5052 OR ((p_label_type_info.business_flow_code IN (1, 2, 4))
5053 AND (l_is_wms_org = FALSE)
5054 )
5055 AND l_patch_level = 0 THEN
5056 -- For putaway in WMS org and Receipt, Inspection, Delivery in INV org
5057 -- Obtain information from RTI and MTLT (if applicable)
5058 -- Receipt Inspection: No lot and seial information, print item information from RTI
5059 -- Delivery: RTI + MTLT
5060
5061 FETCH rti_material_mtlt_cur INTO l_inventory_item_id
5062 , l_revision
5063 , l_lot_number
5064 , l_organization_id
5065 , l_cost_group_id
5066 , l_project_id
5067 , l_task_id
5068 , l_quantity
5069 , l_uom
5070 , l_purchase_order
5071 , l_po_line_number
5072 , l_quantity_ordered
5073 , l_supplier_part_number
5074 , l_vendor_id
5075 , l_supplier_name
5076 , l_vendor_site_id
5077 , l_supplier_site
5078 , l_requestor
5079 , l_deliver_to_location
5080 , l_location_code
5081 , l_note_to_receiver
5082 , l_routing_name
5083 , l_item_description
5084 , l_subinventory_code
5085 , l_locator_id
5086 , l_wip_entity_name
5087 , l_wip_description
5088 , l_wip_op_seq_num
5089 , l_osp_dept_code
5090 , l_bom_resource_id;
5091
5092 IF rti_material_mtlt_cur%NOTFOUND THEN
5093 CLOSE rti_material_mtlt_cur;
5094 l_inventory_item_id := NULL;
5095 l_item_description := NULL;
5096 END IF;
5097 -- Fix bug 2167545-2, for Pick Drop(19), should use cursor mmtt_material_cur
5098 -- remove from this group, add to the mmtt_material_cur group
5099 ELSIF (p_label_type_info.business_flow_code IN (6)) THEN
5100 FETCH wdd_material_cur INTO l_inventory_item_id
5101 , l_organization_id
5102 , l_lot_number
5103 , l_cost_group_id
5104 , l_project_id
5105 , l_task_id
5106 , l_quantity
5107 , l_uom
5108 , l_revision
5109 , l_subinventory_code
5110 , l_locator_id;
5111
5112 IF wdd_material_cur%NOTFOUND THEN
5113 CLOSE wdd_material_cur;
5114 l_inventory_item_id := NULL;
5115 END IF;
5116 -- Bug 3823140, for WIP completion, also uses cursor mmtt_material_receipt_cur
5117 ELSIF p_label_type_info.business_flow_code IN (13,26) THEN
5118 FETCH mmtt_material_receipt_cur INTO l_inventory_item_id
5119 , l_organization_id
5120 , l_lot_number
5121 , l_cost_group_id
5122 , l_project_id
5123 , l_task_id
5124 , l_quantity
5125 , l_uom
5126 , l_secondary_transaction_qty -- invconv
5127 , l_secondary_uom_code -- invconv
5128 , l_revision
5129 , l_attribute_category
5130 , l_c_attribute1
5131 , l_c_attribute2
5132 , l_c_attribute3
5133 , l_c_attribute4
5134 , l_c_attribute5
5135 , l_c_attribute6
5136 , l_c_attribute7
5137 , l_c_attribute8
5138 , l_c_attribute9
5139 , l_c_attribute10
5140 , l_c_attribute11
5141 , l_c_attribute12
5142 , l_c_attribute13
5143 , l_c_attribute14
5144 , l_c_attribute15
5145 , l_c_attribute16
5146 , l_c_attribute17
5147 , l_c_attribute18
5148 , l_c_attribute19
5149 , l_c_attribute20
5150 , l_d_attribute1
5151 , l_d_attribute2
5152 , l_d_attribute3
5153 , l_d_attribute4
5154 , l_d_attribute5
5155 , l_d_attribute6
5156 , l_d_attribute7
5157 , l_d_attribute8
5158 , l_d_attribute9
5159 , l_d_attribute10
5160 , l_n_attribute1
5161 , l_n_attribute2
5162 , l_n_attribute3
5163 , l_n_attribute4
5164 , l_n_attribute5
5165 , l_n_attribute6
5166 , l_n_attribute7
5167 , l_n_attribute8
5168 , l_n_attribute9
5169 , l_n_attribute10
5170 , l_territory_code
5171 , l_grade_code
5172 , l_origination_date
5173 , l_date_code
5174 , l_change_date
5175 , l_age
5176 , l_retest_date
5177 , l_maturity_date
5178 , l_item_size
5179 , l_color
5180 , l_volume
5181 , l_volume_uom
5182 , l_place_of_origin
5183 , l_best_by_date
5184 , l_length
5185 , l_length_uom
5186 , l_recycled_content
5187 , l_thickness
5188 , l_thickness_uom
5189 , l_width
5190 , l_width_uom
5191 , l_curl_wrinkle_fold
5192 , l_vendor_name
5193 , l_subinventory_code
5194 , l_locator_id
5195 , l_wip_entity_name -- Fix For Bug: 4907062
5196 , l_wip_description -- Fix For Bug: 4907062
5197 , l_parent_lot_number -- invconv fabdi start
5198 , l_expiration_action_date
5199 , l_origination_type
5200 , l_hold_date
5201 , l_expiration_action_code
5202 , l_supplier_lot_number ; -- invconv fabdi end
5203
5204
5205 IF mmtt_material_receipt_cur%NOTFOUND THEN
5206 CLOSE mmtt_material_receipt_cur;
5207 l_inventory_item_id := NULL;
5208 END IF;
5209 ELSIF p_label_type_info.business_flow_code = 20 THEN
5210 -- Pack/Unpack/Split LPN
5211 FETCH material_lpn_cur INTO l_inventory_item_id
5212 , l_organization_id
5213 , l_lot_number
5214 , l_cost_group_id
5215 , l_project_id
5216 , l_task_id
5217 , l_quantity
5218 , l_uom
5219 , l_revision
5220 , l_from_subinventory
5221 , l_to_subinventory
5222 , l_from_locator_id
5223 , l_to_locator_id
5224 , l_secondary_transaction_qty -- invconv
5225 , l_secondary_uom_code; -- invconv
5226
5227
5228 IF material_lpn_cur%NOTFOUND THEN
5229 l_inventory_item_id := NULL;
5230 CLOSE material_lpn_cur;
5231 END IF;
5232 ELSIF p_label_type_info.business_flow_code IN (21) THEN
5233 -- Ship Confirm
5234 FETCH wda_material_cur INTO l_inventory_item_id
5235 , l_organization_id
5236 , l_lot_number
5237 , l_cost_group_id
5238 , l_project_id
5239 , l_task_id
5240 , l_quantity
5241 , l_uom
5242 , l_revision
5243 , l_subinventory_code
5244 , l_locator_id;
5245
5246 IF wda_material_cur%NOTFOUND THEN
5247 CLOSE wda_material_cur;
5248 l_inventory_item_id := NULL;
5249 END IF;
5250 ELSIF p_label_type_info.business_flow_code IN (22) THEN
5251 -- Cartonization
5252 FETCH c_get_pkg_items_content INTO l_organization_id
5253 , l_inventory_item_id
5254 , l_revision
5255 , l_lot_number
5256 , l_quantity;
5257
5258 IF c_get_pkg_items_content%NOTFOUND THEN
5259 IF (l_debug = 1) THEN
5260 TRACE(' No more records found for Header ID/package mode in the WPH:');
5261 END IF;
5262
5263 l_inventory_item_id := NULL;
5264 CLOSE c_get_pkg_items_content;
5265 ELSE
5266 item_fetch_cntr := item_fetch_cntr + 1;
5267
5268 IF (l_debug = 1) THEN
5269 TRACE('Item(s) fetched'|| item_fetch_cntr);
5270 END IF;
5271 END IF;
5272 -- Bug 3823140, use mmtt_material_receipt_cur instead to get new lot information
5273 /*ELSIF p_label_type_info.business_flow_code IN (26) THEN
5274 -- WIP Completion
5275 FETCH wip_material_cur INTO l_inventory_item_id
5276 , l_organization_id
5277 , l_lot_number
5278 , l_cost_group_id
5279 , l_project_id
5280 , l_task_id
5281 , l_quantity
5282 , l_uom
5283 , l_revision
5284 , l_subinventory_code
5285 , l_locator_id;
5286
5287 IF wip_material_cur%NOTFOUND THEN
5288 TRACE(' No more records found for transaction_temp_id in MMTT');
5289 l_inventory_item_id := NULL;
5290 CLOSE wip_material_cur;
5291 ELSE
5292 TRACE(' More Lot Items Retreived');
5293 TRACE(
5294 ' wip_material_cur '
5295 || ', Item ID=' || l_inventory_item_id
5296 || ', Organization ID=' || l_organization_id
5297 || ', Lot Number=' || l_lot_number
5298 || ', Project ID=' || l_project_id
5299 || ', Cost Group ID=' || l_cost_group_id
5300 || ', Task ID=' || l_task_id
5301 || ', Transaction Quantity=' || l_quantity
5302 || ', Transaction UOM=' || l_uom
5303 || ', Item Revision=' || l_revision
5304 || ', Subinventory Code=' || l_subinventory_code
5305 || ', Locator ID=' || l_locator_id
5306 );
5307 END IF; */
5308 ELSIF p_label_type_info.business_flow_code = 37 THEN
5309 -- Manufacturing Cross-Dock(37)
5310 FETCH wip_material_cur INTO l_inventory_item_id
5311 , l_organization_id
5312 , l_lot_number
5313 , l_cost_group_id
5314 , l_project_id
5315 , l_task_id
5316 , l_quantity
5317 , l_uom
5318 , l_revision
5319 , l_subinventory_code
5320 , l_locator_id;
5321
5322 IF wip_material_cur%NOTFOUND THEN
5323 TRACE(' No more records found for transaction_temp_id in MMTT');
5324 l_inventory_item_id := NULL;
5325 CLOSE wip_material_cur;
5326 ELSE
5327 TRACE(' More Items Retreived');
5328 TRACE( ' wip_material_cur '
5329 || ', Item ID=' || l_inventory_item_id
5330 || ', Organization ID=' || l_organization_id
5331 || ', Lot Number=' || l_lot_number
5332 || ', Project ID=' || l_project_id
5333 || ', Cost Group ID=' || l_cost_group_id
5334 || ', Task ID=' || l_task_id
5335 || ', Transaction Quantity=' || l_quantity
5336 || ', Transaction UOM=' || l_uom
5337 || ', Item Revision=' || l_revision
5338 || ', Subinventory Code=' || l_subinventory_code
5339 || ', Locator ID=' || l_locator_id
5340 );
5341 END IF;
5342 ELSIF p_label_type_info.business_flow_code IN (33) THEN
5343 -- Flow Completion
5344
5345
5346 IF l_transaction_identifier = 1 THEN
5347 FETCH flow_material_curs_mmtt INTO l_inventory_item_id
5348 , l_organization_id
5349 , l_lot_number
5350 , l_cost_group_id
5351 , l_project_id
5352 , l_task_id
5353 , l_quantity
5354 , l_uom
5355 , l_revision
5356 , l_subinventory_code
5357 , l_locator_id;
5358
5359 IF flow_material_curs_mmtt%NOTFOUND THEN
5360 CLOSE flow_material_curs_mmtt;
5361 l_inventory_item_id := NULL;
5362 END IF;
5363 ELSIF l_transaction_identifier = 2 THEN
5364 FETCH flow_material_curs_mti INTO l_inventory_item_id
5365 , l_organization_id
5366 , l_lot_number
5367 , l_cost_group_id
5368 , l_project_id
5369 , l_task_id
5370 , l_quantity
5371 , l_uom
5372 , l_revision
5373 , l_subinventory_code
5374 , l_locator_id;
5375
5376 IF flow_material_curs_mti%NOTFOUND THEN
5377 CLOSE flow_material_curs_mti;
5378 l_inventory_item_id := NULL;
5379 END IF;
5380 ELSIF l_transaction_identifier = 3 THEN
5381 FETCH flow_material_curs_mol INTO l_inventory_item_id
5382 , l_organization_id
5383 , l_lot_number
5384 , l_cost_group_id
5385 , l_project_id
5386 , l_task_id
5387 , l_quantity
5388 , l_uom
5389 , l_revision
5390 , l_subinventory_code
5391 , l_locator_id;
5392
5393 IF flow_material_curs_mol%NOTFOUND THEN
5394 CLOSE flow_material_curs_mol;
5395 l_inventory_item_id := NULL;
5396 END IF;
5397 ELSE
5398 IF (l_debug = 1) THEN
5399 TRACE( ' Invalid transaction_identifier passed' || p_transaction_identifier);
5400 END IF;
5401
5402 RETURN;
5403 END IF;
5404 -- Fix bug 2167545-1: Cost Group Update(11) is calling label printing through TM
5405 -- add 11 to this group.
5406 -- Fix bug 2167545-2: Pick Drop(19) is also using this cursor. add to this group.
5407
5408 --Bug 4891916. Modified the condition for business flow for cycle count by
5409 --checking for the business flow 8 and transaction_identifier as 5
5410 ELSIF p_label_type_info.business_flow_code IN
5411 ( /*8,*/ 9, 11, 12, 13, 14, 18, 19, 22, 23, 27, 28, 29, 34)--Bug 5928736- Removed business flow 7
5412 OR(p_label_type_info.business_flow_code = 8 AND p_transaction_identifier = 5) THEN
5413
5414 FETCH mmtt_material_cur INTO l_inventory_item_id
5415 , l_organization_id
5416 , l_lot_number
5417 , l_cost_group_id
5418 , l_xfr_cost_group_id /* Added for the bug # 4686024 */
5419 , l_project_id
5420 , l_task_id
5421 , l_quantity
5422 , l_uom
5423 , l_revision
5424 , l_from_subinventory
5425 , l_to_subinventory
5426 , l_from_locator_id
5427 , l_to_locator_id
5428 , l_secondary_uom_code -- added for invconv
5429 , l_secondary_transaction_qty; -- added for invconv
5430
5431 IF mmtt_material_cur%NOTFOUND THEN
5432 l_inventory_item_id := NULL;
5433 CLOSE mmtt_material_cur;
5434 ELSE
5435 IF p_label_type_info.business_flow_code IN (14, 19, 29) THEN --Bug 5928736- Removed business flow 7
5436 l_subinventory_code := l_to_subinventory;
5437 l_locator_id := l_from_locator_id;
5438 ELSE
5439 l_subinventory_code := l_from_subinventory;
5440 l_locator_id := l_to_locator_id;
5441
5442 --Bug 4891916. For cycle count, opened the cursor to fetch values for
5443 --cycle count header name and counter
5444 IF p_label_type_info.business_flow_code = 8 THEN
5445 OPEN cc_det_approval ;
5446
5447 FETCH cc_det_approval
5448 INTO l_cycle_count_name
5449 , l_requestor ;
5450
5451 IF cc_det_approval%NOTFOUND THEN
5452 IF (l_debug = 1) THEN
5453 TRACE(' No record found in MCCE for given txn_temp_id: ' || p_transaction_id);
5454 END IF;
5455
5456 CLOSE cc_det_approval;
5457 END IF;--End of cursor not found condition
5458
5459 END IF; --End of business flow=8 condition
5460 --End of fix for Bug 4891916
5461
5462 END IF;
5463 END IF;
5464
5465 --Bug 4891916- Added the condition to open the cursor to fetch from mcce
5466 --by checking for business flow 8 and transaction identifier 4
5467 ELSIF (p_label_type_info.business_flow_code = 8 AND p_transaction_identifier= 4) THEN --from entry
5468 IF (l_debug = 1) THEN
5469 TRACE(' IN the condition for bus flow 8 and pti 4 ');
5470 END IF;
5471 FETCH mcce_material_cur
5472 INTO l_inventory_item_id
5473 , l_organization_id
5474 , l_lot_number
5475 , l_cost_group_id
5476 , l_quantity
5477 , l_uom
5478 , l_revision
5479 , l_subinventory_code
5480 , l_locator_id
5481 , l_cycle_count_name
5482 , l_requestor ;
5483
5484 IF (l_debug = 1) THEN
5485 TRACE('Values fetched from cursor:');
5486 TRACE('Values of l_inventory_item_id:'|| l_inventory_item_id);
5487 TRACE('Values of l_organization_id:' || l_organization_id);
5488 TRACE('Values of l_lot_number:' || l_lot_number);
5489 TRACE('Values of l_cost_group_id:' || l_cost_group_id);
5490 TRACE('Values of l_quantity:' || l_quantity);
5491 TRACE('Values of l_uom:' || l_uom);
5492 TRACE('Values of l_revision:' || l_revision);
5493 TRACE('Values of l_subinventory:' || l_subinventory_code);
5494 TRACE('Values of l_locator_id:' || l_locator_id);
5495 TRACE('Values of l_cycle_count_name:' || l_cycle_count_name);
5496 TRACE('Values of l_counter:' || l_requestor);
5497 END IF;
5498
5499 IF mcce_material_cur%NOTFOUND THEN
5500 IF (l_debug = 1) THEN
5501 TRACE(' No record found in mcce_material_cur for given cycle_count_id ' || p_transaction_id);
5502 END IF;
5503 CLOSE mcce_material_cur;
5504 RETURN;
5505 END IF;
5506 /* End of fix for Bug 4891916 */
5507
5508 ELSIF p_label_type_info.business_flow_code IN (15, 30, 7) THEN --Bug 5928736 -Added the business flow 7
5509 FETCH inv_putaway_material_cur INTO l_inventory_item_id
5510 , l_organization_id
5511 , l_lot_number
5512 , l_cost_group_id
5513 , l_project_id
5514 , l_task_id
5515 , l_quantity
5516 , l_secondary_transaction_qty
5517 , l_uom
5518 , l_secondary_uom_code
5519 , l_revision
5520 , l_subinventory_code
5521 , l_locator_id;
5522
5523 IF inv_putaway_material_cur%NOTFOUND THEN
5524 CLOSE inv_putaway_material_cur;
5525 l_inventory_item_id := NULL;
5526 END IF;
5527 ELSE
5528 l_inventory_item_id := NULL;
5529 l_item_description := NULL;
5530 END IF;
5531 END IF;
5532 IF (l_debug = 1) THEN
5533 TRACE(' Outside of IF..THEN...ELSE... ENDIF; Check for patchset level J..');
5534 TRACE(' Just Before END LOOP ..');
5535 END IF;
5536
5537 END LOOP;
5538
5539 IF (wdd_material_cur%ISOPEN) THEN
5540 CLOSE wdd_material_cur;
5541 END IF;
5542
5543 IF (wda_material_cur%ISOPEN) THEN
5544 CLOSE wda_material_cur;
5545 END IF;
5546
5547 IF (rti_material_lpn_cur%ISOPEN) THEN
5548 CLOSE rti_material_lpn_cur;
5549 END IF;
5550
5551 IF (rti_material_mtlt_cur%ISOPEN) THEN
5552 CLOSE rti_material_mtlt_cur;
5553 END IF;
5554
5555 /*IF (wip_material_cur%ISOPEN) THEN
5556 CLOSE wip_material_cur;
5557 END IF;*/
5558
5559 IF (flow_material_curs_mmtt%ISOPEN) THEN
5560 CLOSE flow_material_curs_mmtt;
5561 END IF;
5562
5563 IF (flow_material_curs_mti%ISOPEN) THEN
5564 CLOSE flow_material_curs_mti;
5565 END IF;
5566
5567 IF (flow_material_curs_mol%ISOPEN) THEN
5568 CLOSE flow_material_curs_mol;
5569 END IF;
5570 END get_variable_data;
5571
5572 PROCEDURE get_variable_data(
5573 x_variable_content OUT NOCOPY LONG
5574 , x_msg_count OUT NOCOPY NUMBER
5575 , x_msg_data OUT NOCOPY VARCHAR2
5576 , x_return_status OUT NOCOPY VARCHAR2
5577 , p_label_type_info IN inv_label.label_type_rec
5578 , p_transaction_id IN NUMBER
5579 , p_input_param IN mtl_material_transactions_temp%ROWTYPE
5580 , p_transaction_identifier IN NUMBER
5581 ) IS
5582 l_variable_data_tbl inv_label.label_tbl_type;
5583 BEGIN
5584 get_variable_data(
5585 x_variable_content => l_variable_data_tbl
5586 , x_msg_count => x_msg_count
5587 , x_msg_data => x_msg_data
5588 , x_return_status => x_return_status
5589 , p_label_type_info => p_label_type_info
5590 , p_transaction_id => p_transaction_id
5591 , p_input_param => p_input_param
5592 , p_transaction_identifier => p_transaction_identifier
5593 );
5594 x_variable_content := '';
5595
5596 FOR i IN 1 .. l_variable_data_tbl.COUNT() LOOP
5597 x_variable_content :=
5598 x_variable_content || l_variable_data_tbl(i).label_content;
5599 END LOOP;
5600 END get_variable_data;
5601
5602 /*****************************************************************************
5603 * This function is used for printing labels at receiving *
5604 * This function adds all the interface transaction ID's to the PL/SQL table*
5605 * which means that any interface transaction ID existing in this table is *
5606 * already printed. *
5607 *****************************************************************************/
5608 FUNCTION check_rti_id(
5609 p_rti_id IN NUMBER
5610 , p_lot_number IN VARCHAR2
5611 , p_rev IN VARCHAR2
5612 )
5613 RETURN VARCHAR2 IS
5614 l_label_counter NUMBER := 0;
5615 l_return_flag VARCHAR2(1) := 'N';
5616 l_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
5617 BEGIN
5618 l_label_counter := g_rcv_label_print_rec_tb.COUNT;
5619
5620 IF (l_debug = 1) THEN
5621 TRACE('**** New Function Call****');
5622 TRACE('**** l_label_counter=' || l_label_counter
5623 || ',p_rti_id=' || p_rti_id
5624 || ',p_lot_number=' || p_lot_number
5625 || ',p_rev=' || p_rev
5626 );
5627 END IF;
5628
5629 IF (g_rcv_label_print_rec_tb.COUNT = 0)
5630 OR (l_label_counter = 0) THEN
5631 IF (l_debug = 1) THEN
5632 TRACE('no interface transaction IDs in the record structure table ');
5633 END IF;
5634
5635 -- This is the first record and so blindly add the interface transaction id to the
5636 -- table and give out a status of 'N'
5637 l_label_counter := l_label_counter + 1;
5638 g_rcv_label_print_rec_tb(l_label_counter).interface_transaction_id := p_rti_id;
5639 g_rcv_label_print_rec_tb(l_label_counter).lot_number := p_lot_number;
5640 g_rcv_label_print_rec_tb(l_label_counter).item_rev := p_rev;
5641
5642 -- This loop is to display the contents of the PL/SQL table in the log file.
5643 FOR i IN 1 .. l_label_counter LOOP
5644 IF (l_debug = 1) THEN
5645 TRACE( '****** first g_rcv_label_print_rec_tb(' || i || ')' || '.'
5646 || 'interface_transaction_id=' || g_rcv_label_print_rec_tb(i).interface_transaction_id
5647 || ',lot_number=' || g_rcv_label_print_rec_tb(i).lot_number
5648 || ',item_rev ' || g_rcv_label_print_rec_tb(i).item_rev
5649 );
5650 END IF;
5651 END LOOP;
5652
5653 IF (l_debug = 1) THEN
5654 TRACE('l_return_flag is '|| l_return_flag);
5655 END IF;
5656
5657 RETURN l_return_flag;
5658 ELSE
5659 IF (l_debug = 1) THEN
5660 TRACE('interface transaction IDs exist in the record structure');
5661 TRACE('No of Records in Structure '|| l_label_counter);
5662 END IF;
5663
5664 FOR i IN 1 .. l_label_counter LOOP
5665 IF (g_rcv_label_print_rec_tb(i).interface_transaction_id = p_rti_id
5666 AND NVL(g_rcv_label_print_rec_tb(i).lot_number, 'aaa') = NVL(p_lot_number, 'aaa')
5667 AND NVL(g_rcv_label_print_rec_tb(i).item_rev, 'aaa') = NVL(p_rev, 'aaa')
5668 ) THEN
5669 IF (l_debug = 1) THEN
5670 TRACE( 'interface transaction ID ' || p_rti_id
5671 || ', lot_number ' || g_rcv_label_print_rec_tb(i).lot_number
5672 || ', item_rev ' || g_rcv_label_print_rec_tb(i).item_rev
5673 || 'has been already considered for label printing '
5674 );
5675 END IF;
5676
5677 -- This loop is to display the contents of the PL/SQL table in the log file.
5678 FOR j IN 1 .. l_label_counter LOOP
5679 IF (l_debug = 1) THEN
5680 TRACE( '****** Second g_rcv_label_print_rec_tb(' || j || ')' || '.'
5681 || 'interface_transaction_id=' || g_rcv_label_print_rec_tb(j).interface_transaction_id
5682 || ',lot_number=' || g_rcv_label_print_rec_tb(j).lot_number
5683 || ',item_rev=' || g_rcv_label_print_rec_tb(j).item_rev
5684 );
5685 END IF;
5686 END LOOP;
5687
5688 l_return_flag := 'Y';
5689
5690 IF (l_debug = 1) THEN
5691 TRACE('l_return_flag is '|| l_return_flag);
5692 END IF;
5693
5694 RETURN l_return_flag;
5695 END IF;
5696 END LOOP;
5697
5698 IF (l_debug = 1) THEN
5699 TRACE('Label is not yet printed for interface transaction ID '|| p_rti_id);
5700 TRACE('Adding Record to the PL/SQL table ');
5701 END IF;
5702
5703 g_rcv_label_print_rec_tb(l_label_counter + 1).interface_transaction_id := p_rti_id;
5704 g_rcv_label_print_rec_tb(l_label_counter + 1).lot_number := p_lot_number;
5705 g_rcv_label_print_rec_tb(l_label_counter + 1).item_rev := p_rev;
5706 -- Updated Label Counter value.
5707 l_label_counter := g_rcv_label_print_rec_tb.COUNT;
5708
5709 -- This loop is to display the contents of the PL/SQL table in the log file.
5710 FOR i IN 1 .. l_label_counter LOOP
5711 IF (l_debug = 1) THEN
5712 TRACE( '****** Third g_rcv_label_print_rec_tb('|| i || ')' || '.'
5713 || 'interface_transaction_id=' || g_rcv_label_print_rec_tb(i).interface_transaction_id
5714 || ',lot_number=' || g_rcv_label_print_rec_tb(i).lot_number
5715 || ',item_rev=' || g_rcv_label_print_rec_tb(i).item_rev
5716 );
5717 END IF;
5718 END LOOP;
5719
5720 IF (l_debug = 1) THEN
5721 TRACE('l_return_flag is '|| l_return_flag);
5722 END IF;
5723
5724 RETURN l_return_flag;
5725 END IF;
5726 END;
5727 END inv_label_pvt1;