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