[Home] [Help]
PACKAGE BODY: APPS.GML_MOBILE_RECEIPT
Source
1 PACKAGE BODY GML_MOBILE_RECEIPT AS
2 /* $Header: GMLMRCVB.pls 120.0 2005/05/25 16:19:23 appldev noship $ */
3
4 g_pkg_name CONSTANT VARCHAR2(30) := 'GML_MOBILE_RECEIPT';
5
6 --yannamal 4189249 Added NOCOPY for x_return_status and x_error_msg
7 PROCEDURE Check_Lot_Status(p_lot_id IN NUMBER,
8 p_lot_num IN VARCHAR2,
9 p_sublot_num IN VARCHAR2,
10 p_item_id IN NUMBER,
11 p_org_id IN NUMBER,
12 p_locator_id IN NUMBER,
13 p_reason_code IN VARCHAR2,
14 x_return_status OUT NOCOPY VARCHAR2,
15 x_error_msg OUT NOCOPY VARCHAR2) IS
16
17 v_location VARCHAR2(20);
18 v_opm_item_id NUMBER;
19 v_inv_lot_status VARCHAR2(5) := NULL;
20 v_item_rec IC_ITEM_MST%ROWTYPE;
21 v_inv_loct_onhand NUMBER := 0;
22
23 v_processing_mode VARCHAR2(15);
24
25 ---l_trans_rec GMIGAPI.qty_rec_typ := GML_MLT_CNTR_RCPT.gmigapi_qty_format;
26
27
28 l_ic_jrnl_mst_row ic_jrnl_mst%ROWTYPE;
29 l_ic_adjs_jnl_row1 ic_adjs_jnl%ROWTYPE;
30 l_ic_adjs_jnl_row2 ic_adjs_jnl%ROWTYPE;
31 l_status VARCHAR2(1);
32 l_count NUMBER;
33 l_data VARCHAR2(2000);
34 l_count_msg NUMBER;
35 l_dummy_cnt NUMBER :=0;
36 l_reason_code_security VARCHAR2(10) := 'N';
37
38 RECV_DIFF_STATUS_ERROR EXCEPTION;
39 ERRORS EXCEPTION;
40
41 l_message_data VARCHAR2(2000);
42
43 l_move_diff_status INTEGER := FND_PROFILE.VALUE('IC$MOVEDIFFSTAT');
44
45
46 BEGIN
47 x_return_status := 'S';
48 v_opm_item_id := p_item_id;
49
50 SELECT *
51 INTO v_item_rec
52 FROM ic_item_mst
53 WHERE item_id = v_opm_item_id;
54
55 IF p_locator_id > 0 THEN
56 SELECT location
57 INTO v_location
58 FROM ic_loct_mst
59 WHERE inventory_location_id = p_locator_id;
60 ELSE
61 v_location := FND_PROFILE.VALUE('IC$DEFAULT_LOCT');
62 END IF;
63
64 IF v_item_rec.status_ctl = 0 THEN
65 RETURN;
66 END IF;
67
68
69 BEGIN
70 SELECT lot_status, loct_onhand
71 INTO v_inv_lot_status, v_inv_loct_onhand
72 FROM ic_loct_inv ilv, ic_whse_mst w
73 WHERE ilv.item_id = v_opm_item_id
74 AND ilv.lot_id = p_lot_id
75 AND w.mtl_organization_id = p_org_id
76 AND ilv.whse_code = w.whse_code
77 AND ilv.location = v_location;
78
79 EXCEPTION
80 WHEN OTHERS THEN
81 v_inv_lot_status := NULL;
82 END;
83
84
85 IF l_move_diff_status = 0 THEN
86 IF (v_inv_lot_status IS NOT NULL) AND (v_inv_lot_status <> v_item_rec.lot_status) THEN
87 FND_MESSAGE.SET_NAME('GML', 'GML_CANT_RECV_DIFF_STATUS');
88 RAISE RECV_DIFF_STATUS_ERROR;
89 END IF;
90 ELSIF l_move_diff_status = 2 THEN
91 IF v_inv_lot_status IS NOT NULL
92 AND v_inv_lot_status <> v_item_rec.lot_status
93 AND v_inv_loct_onhand <> 0 THEN
94 FND_MESSAGE.SET_NAME('GML', 'GML_CANT_RECV_DIFF_STATUS');
95 RAISE RECV_DIFF_STATUS_ERROR;
96 END IF;
97 END IF;
98
99 EXCEPTION
100 WHEN RECV_DIFF_STATUS_ERROR THEN
101 x_error_msg := FND_MESSAGE.GET;
102 x_return_status := 'E';
103 WHEN ERRORS THEN
104 x_return_status := 'U';
105 WHEN OTHERS THEN
106 x_return_status := 'U';
107
108
109 END check_lot_status;
110
111
112 PROCEDURE GET_PO_LINE_ITEM_NUM_LOV(x_po_line_num_lov OUT NOCOPY t_genref,
113 p_organization_id IN NUMBER,
114 p_po_header_id IN NUMBER,
115 p_mobile_form IN VARCHAR2,
116 p_po_line_num IN VARCHAR2,
117 p_inventory_item_id IN VARCHAR2)
118 IS
119 BEGIN
120 IF p_mobile_form = 'RECEIPT' THEN
121 OPEN x_po_line_num_lov FOR
122 select distinct pl.line_num
123 , pl.po_line_id
124 , pl.item_description
125 , pl.item_id
126 , pl.item_revision
127 , msi.concatenated_segments
128 , msi.outside_operation_flag
129 , mum.uom_code
130 from po_lines_all pl
131 , mtl_units_of_measure mum
132 , mtl_system_items_kfv msi
133 where pl.item_id = msi.inventory_item_id (+)
134 and mum.UNIT_OF_MEASURE(+) = pl.UNIT_MEAS_LOOKUP_CODE
135 and Nvl(msi.organization_id, p_organization_id) = p_organization_id
136 and pl.po_header_id = p_po_header_id
137 and exists (SELECT 'Valid PO Shipments'
138 FROM po_line_locations_all poll
139 WHERE poll.po_header_id = pl.po_header_id
140 AND poll.po_line_id = pl.po_line_id
141 AND Nvl(poll.approved_flag,'N') = 'Y'
142 AND Nvl(poll.cancel_flag,'N') = 'N'
143 AND receiving_routing_id = 3 --- Direct only supported by OPM
144 -- AND poll.closed_code = 'OPEN' -- Bug 2859355
145 AND Nvl(poll.closed_code,'OPEN') NOT IN ('CLOSED', 'FINALLY CLOSED','CLOSED FOR RECEIVING')
146 AND poll.shipment_type IN ('STANDARD','BLANKET','SCHEDULED')
147 AND poll.ship_to_organization_id = p_organization_id)
148 AND pl.line_num LIKE (p_po_line_num)
149 AND nvl(pl.item_id,-999) LIKE nvl(p_inventory_item_id,'%')
150 UNION ALL
151 select distinct pl.line_num
152 , pl.po_line_id
153 , pl.item_description
154 , pl.item_id
155 , pl.item_revision
156 , msi.concatenated_segments
157 , msi.outside_operation_flag
158 , mum.uom_code
159 from po_lines_all pl
160 , mtl_units_of_measure mum
161 , mtl_system_items_kfv msi
162 , mtl_related_items mri
163 where Nvl(msi.organization_id, p_organization_id) = p_organization_id
164 and mum.UNIT_OF_MEASURE(+) = pl.UNIT_MEAS_LOOKUP_CODE
165 and pl.po_header_id = p_po_header_id
166 and exists (SELECT 'Valid PO Shipments'
167 FROM po_line_locations_all poll
168 WHERE poll.po_header_id = pl.po_header_id
169 AND poll.po_line_id = pl.po_line_id
170 AND Nvl(poll.approved_flag,'N') = 'Y'
171 AND Nvl(poll.cancel_flag,'N') = 'N'
172 AND receiving_routing_id = 3 --- Direct only supported by OPM
173 -- AND poll.closed_code = 'OPEN' --Bug 2859355
174 AND Nvl(poll.closed_code,'OPEN') NOT IN ('CLOSED','FINALLY CLOSED','CLOSED FOR RECEIVING')
175 AND poll.shipment_type IN ('STANDARD','BLANKET','SCHEDULED')
176 AND poll.ship_to_organization_id = p_organization_id)
177 AND pl.line_num LIKE (p_po_line_num)
178 AND ( (mri.related_item_id = msi.inventory_item_id
179 and pl.item_id = mri.inventory_item_id
180 and msi.inventory_item_id like p_inventory_item_id )
181 or
182 (mri.inventory_item_id = msi.inventory_item_id
183 and pl.item_id = mri.related_item_id and mri.reciprocal_flag = 'Y'
184 and msi.inventory_item_id like p_inventory_item_id )
185 )
186 order by 1;
187 ELSE
188 OPEN x_po_line_num_lov FOR
189 select distinct pl.line_num
190 , pl.po_line_id
191 , pl.item_description
192 , pl.item_id
193 , pl.item_revision
194 , msi.concatenated_segments
195 , msi.outside_operation_flag
196 , mum.uom_code
197 FROM rcv_supply rsup
198 , mtl_units_of_measure mum
199 , po_lines_all pl
200 , mtl_system_items_kfv msi
201 WHERE rsup.po_line_id = pl.po_line_id
202 and mum.UNIT_OF_MEASURE(+) = pl.UNIT_MEAS_LOOKUP_CODE
203 AND pl.item_id = msi.inventory_item_id (+)
204 and Nvl(msi.organization_id, p_organization_id) = p_organization_id
205 and rsup.po_header_id = p_po_header_id
206 AND pl.line_num LIKE (p_po_line_num)
207 order by pl.line_num;
208 END IF;
209 END GET_PO_LINE_ITEM_NUM_LOV;
210
211 PROCEDURE Get_UoM_LoV_RcV(x_uoms OUT NOCOPY t_genref,
212 p_organization_id IN NUMBER,
213 p_item_id IN NUMBER,
214 p_uom_type IN NUMBER,
215 p_uom_code IN VARCHAR2) IS
216
217 BEGIN
218
219 IF (p_item_id IS NOT NULL AND p_item_id > 0) THEN
220 OPEN x_uoms FOR
221 SELECT
222 uom_code
223 , unit_of_measure
224 , description
225 , uom_class
226 , PO_GML_DB_COMMON.GET_OPM_UOM_CODE(uom_code)
227 FROM mtl_item_uoms_view
228 WHERE organization_id = p_organization_id
229 AND inventory_item_id(+) = p_item_id
230 AND NVL(uom_type, 3) = NVL(p_uom_type, 3)
231 AND uom_code LIKE (p_uom_code)
232 ORDER BY Upper(uom_code);
233
234 END IF;
235
236 END get_uom_lov_rcv;
237
238 PROCEDURE Get_Lot_LoV( x_lot_lov OUT NOCOPY t_genref,
239 p_item_id IN NUMBER,
240 p_lot_no IN VARCHAR2) IS
241 BEGIN
242
243
244 OPEN x_lot_lov FOR
245 select a.lot_no,a.sublot_no,a.expire_date,a.lot_id
246 from ic_lots_mst a, ic_item_mst b
247 where a.item_id= p_item_id
248 and a.lot_id <> 0
249 and a.lot_no like (p_lot_no)
250 and a.delete_mark=0
251 and b.item_id = a.item_id
252 and b.delete_mark=0
253 order by 1,2;
254
255 END Get_Lot_LoV;
256
257 PROCEDURE Get_SubLot_LoV( x_sublot_lov OUT NOCOPY t_genref,
258 p_item_id IN NUMBER,
259 p_lot_no IN VARCHAR2,
260 p_sublot_no IN VARCHAR2) IS
261 BEGIN
262
263 OPEN x_sublot_lov FOR
264 select sublot_no ,expire_date,lot_id
265 from ic_lots_mst
266 where item_id= p_item_id
267 and lot_no = p_lot_no
268 and sublot_no like (p_sublot_no)
269 and lot_id <>0
270 and delete_mark=0
271 order by sublot_no;
272
273 END Get_SubLot_LoV;
274
275 PROCEDURE Get_Reason_Code_LoV( x_reason_code_lov OUT NOCOPY t_genref,
276 p_reason_code IN VARCHAR2) IS
277 BEGIN
278
279 OPEN x_reason_code_lov FOR
280 select reason_code,reason_desc1
281 from sy_reas_cds
282 where reason_code like (p_reason_code) AND
283 delete_mark = 0
284 order by 1;
285
286 END Get_Reason_Code_LoV;
287
288 PROCEDURE Get_Location_Lov( x_location_lov OUT NOCOPY t_genref,
289 p_location IN VARCHAR2,
290 p_item_id IN NUMBER,
291 p_whse_code IN VARCHAR2,
292 p_lot_id IN NUMBER) IS
293
294 l_default_loc VARCHAR2(30) := FND_PROFILE.VALUE('IC$DEFAULT_LOCT');
295
296 BEGIN
297
298 OPEN x_location_lov FOR
299 select distinct location, NVL(loct_desc, location), INVENTORY_LOCATION_ID
300 from ic_loct_mst
301 where whse_code = p_whse_code and delete_mark = 0 and
302 location like (p_location) and
303 location <> l_default_loc;
304
305 END Get_Location_Lov;
306
307
308 -- This api creates a record in the MTL_TRANSACTION_LOTS_INTERFACE
309 -- It checks if the p_transaction_temp_id is null, if it is, then it
310 -- generates a new id and returns that.
311 PROCEDURE insert_lot(
312 p_transaction_interface_id IN OUT NOCOPY NUMBER
313 , p_product_transaction_id IN OUT NOCOPY NUMBER
314 , p_created_by IN NUMBER
315 , p_transaction_qty IN NUMBER
316 , p_secondary_qty IN NUMBER
317 , p_primary_qty IN NUMBER
318 , p_lot_number IN VARCHAR2
319 , p_sublot_number IN VARCHAR2
320 , p_expiration_date IN DATE
321 , p_secondary_unit_of_measure IN VARCHAR2
322 , p_reason_code IN VARCHAR2
323 , x_return_status OUT NOCOPY VARCHAR2
324 , x_msg_data OUT NOCOPY VARCHAR2
325 ) IS
326 l_return NUMBER;
327 l_lot_count NUMBER := 0;
328 l_msg_count NUMBER;
329 BEGIN
330
331 x_return_status := fnd_api.g_ret_sts_success;
332
333 --If the lot number and transaction_interface_id combination already exists
334 --then add the specified transaction_quantity and primary_quantity to the
335 --current lot interface record.
336 IF p_transaction_interface_id IS NOT NULL THEN
337 BEGIN
338 SELECT 1
339 INTO l_lot_count
340 FROM mtl_transaction_lots_interface
341 WHERE transaction_interface_id = p_transaction_interface_id
342 AND Ltrim(Rtrim(lot_number)) = Ltrim(Rtrim(p_lot_number))
343 AND Ltrim(Rtrim(sublot_num)) = Ltrim(Rtrim(p_sublot_number))
344 AND ROWNUM = 1;
345 EXCEPTION
346 WHEN NO_DATA_FOUND THEN
347 l_lot_count := 0;
348 WHEN OTHERS THEN
349 l_lot_count := 0;
350 END;
351
352
353 IF l_lot_count = 1 THEN
354 UPDATE mtl_transaction_lots_interface
355 SET transaction_quantity = transaction_quantity + p_transaction_qty
356 , primary_quantity = primary_quantity + p_primary_qty
357 , reason_code = p_reason_code
358 WHERE transaction_interface_id = p_transaction_interface_id
359 AND Ltrim(Rtrim(lot_number)) = Ltrim(Rtrim(p_lot_number))
360 AND Ltrim(Rtrim(sublot_num)) = Ltrim(Rtrim(p_sublot_number));
361
362
363 RETURN;
364 END IF;
365 END IF;
366
367
368 --Generate transaction_interface_id if the parameter is NULL
369 IF (p_transaction_interface_id IS NULL) THEN
370 SELECT mtl_material_transactions_s.NEXTVAL
371 INTO p_transaction_interface_id
372 FROM sys.dual;
373 END IF;
374
375 --Generate production_transaction_id if the parameter is NULL
376 IF (p_product_transaction_id IS NULL) THEN
377 SELECT rcv_transactions_interface_s.NEXTVAL
378 INTO p_product_transaction_id
379 FROM sys.dual;
380 END IF;
381
382 INSERT INTO MTL_TRANSACTION_LOTS_INTERFACE (
383 transaction_interface_id
384 , last_update_date
385 , last_updated_by
386 , creation_date
387 , created_by
388 , last_update_login
389 , lot_number
390 , sublot_num
391 , lot_expiration_date
392 , transaction_quantity
393 , primary_quantity
394 , secondary_transaction_quantity
395 , reason_code
396 , product_transaction_id
397 , product_code
398 )
399 VALUES (
400 p_transaction_interface_id
401 , SYSDATE
402 , FND_GLOBAL.USER_ID
403 , SYSDATE
404 , FND_GLOBAL.USER_ID
405 , FND_GLOBAL.LOGIN_ID
406 , Ltrim(Rtrim(p_lot_number))
407 , Ltrim(Rtrim(p_sublot_number))
408 , p_expiration_date
409 , p_transaction_qty
410 , p_primary_qty
411 , p_secondary_qty
412 , p_reason_code
413 , p_product_transaction_id
414 , 'RCV'
415 );
416
417
418 EXCEPTION
419 WHEN OTHERS THEN
420 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
421 fnd_msg_pub.count_and_get (
422 p_encoded => FND_API.G_FALSE
423 , p_count => l_msg_count
424 , p_data => x_msg_data );
425 END insert_lot;
426
427
428
429 PROCEDURE rcv_clear_global IS
430 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
431 l_msg_count NUMBER;
432 l_msg_data VARCHAR2(400);
433 BEGIN
434 gml_rcv_std_rcpt_apis.g_shipment_header_id := NULL;
435 gml_rcv_std_rcpt_apis.g_rcpt_match_table_gross.DELETE;
436 gml_rcv_std_rcpt_apis.g_receipt_detail_index := 1;
437 gml_rcv_std_rcpt_apis.g_dummy_lpn_id := NULL;
438 inv_rcv_common_apis.g_rcv_global_var := NULL;
439
440 clear_lot_rec;
441
442 -- clear the message stack.
443 fnd_msg_pub.delete_msg;
444
445 COMMIT;
446 END rcv_clear_global;
447
448 PROCEDURE clear_lot_rec IS
449 BEGIN
450 gml_rcv_std_rcpt_apis.g_rcpt_lot_qty_rec_tb.DELETE;
451 END clear_lot_rec;
452
453 PROCEDURE get_uom_code(
454 x_return_status OUT NOCOPY VARCHAR2
455 , x_uom_code OUT NOCOPY VARCHAR2
456 , p_po_header_id IN NUMBER
457 , p_item_id IN NUMBER
458 , p_organization_id IN NUMBER
459 ) IS
460 l_count NUMBER;
461
462 BEGIN
463 x_return_status := fnd_api.g_ret_sts_success;
464
465 x_uom_code := '@@@';
466 l_count := 0;
467
468 IF p_po_header_id IS NOT NULL AND p_item_id IS NOT NULL THEN
469
470 BEGIN
471 SELECT COUNT(DISTINCT pol.unit_meas_lookup_code)
472 INTO l_count
473 FROM po_lines pol
474 WHERE pol.po_header_id = p_po_header_id
475 AND pol.unit_meas_lookup_code IS NOT NULL
476 AND pol.item_id = p_item_id
477 AND pol.po_line_id IN (SELECT poll.po_line_id
478 FROM po_line_locations_all poll, po_lines_all po
479 WHERE poll.po_header_id = po.po_header_id
480 AND Nvl(poll.approved_flag,'N') = 'Y'
481 AND Nvl(poll.cancel_flag,'N') = 'N'
482 AND Nvl(poll.closed_code,'OPEN') NOT IN ('CLOSED','FINALLY CLOSED','CLOSED FOR RECEIVING')
483 AND poll.shipment_type IN ('STANDARD','BLANKET','SCHEDULED')
484 AND poll.ship_to_organization_id = p_organization_id
485 AND poll.po_line_id = po.po_line_id
486 AND po.item_id = p_item_id
487 AND po.po_header_id = p_po_header_id);
488 EXCEPTION
489 WHEN NO_DATA_FOUND THEN
490 l_count := 0;
491 END;
492
493 IF l_count = 1 THEN
494
495 BEGIN
496 SELECT mum.uom_code
497 INTO x_uom_code
498 FROM po_lines pol
499 , mtl_units_of_measure mum
500 WHERE pol.po_header_id = p_po_header_id
501 AND pol.unit_meas_lookup_code IS NOT NULL
502 AND pol.item_id = p_item_id
503 AND mum.UNIT_OF_MEASURE(+) = pol.UNIT_MEAS_LOOKUP_CODE
504 AND pol.po_line_id IN (SELECT poll.po_line_id
505 FROM po_line_locations_all poll, po_lines_all po
506 WHERE poll.po_header_id = po.po_header_id
507 AND Nvl(poll.approved_flag,'N') = 'Y'
508 AND Nvl(poll.cancel_flag,'N') = 'N'
509 AND Nvl(poll.closed_code,'OPEN') NOT IN ('CLOSED', 'FINALLY CLOSED','CLOSED FOR RECEIVING')
510 AND poll.shipment_type IN ('STANDARD','BLANKET','SCHEDULED')
511 AND poll.ship_to_organization_id = p_organization_id
512 AND poll.po_line_id = po.po_line_id
513 AND po.item_id = p_item_id
514 AND po.po_header_id = p_po_header_id)
515 AND ROWNUM < 2;
516 EXCEPTION
517 WHEN OTHERS THEN
518 x_uom_code := '@@@';
519 END;
520
521 END IF;
522 END IF;
523 EXCEPTION
524 WHEN OTHERS THEN
525 x_return_status := fnd_api.g_ret_sts_unexp_error;
526
527 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
528 fnd_msg_pub.add_exc_msg(g_pkg_name, 'get_uom_code');
529 END IF;
530
531 END get_uom_code;
532
533 PROCEDURE Create_Lot(p_item_id IN NUMBER,
534 p_item_no IN VARCHAR2,
535 p_lot_no IN VARCHAR2,
536 p_sublot_no IN VARCHAR2,
537 p_vendor_id IN NUMBER,
538 x_lot_id OUT NOCOPY NUMBER,
539 x_return_status OUT NOCOPY VARCHAR2,
540 x_error_msg OUT NOCOPY VARCHAR2) IS
541
542
543 l_lot_rec GMIGAPI.lot_rec_typ;
544 l_msg_count NUMBER;
545 l_msg_data VARCHAR2(2000);
546 l_ic_lots_mst_row ic_lots_mst%ROWTYPE;
547 l_ic_lots_cpg_row ic_lots_cpg%ROWTYPE;
548
549 BEGIN
550
551 x_return_status := 'S';
552 x_error_msg := '';
553
554
555 l_lot_rec.item_no := p_item_no;
556 l_lot_rec.lot_no := p_lot_no;
557 l_lot_rec.sublot_no := p_sublot_no;
558 l_lot_rec.lot_desc := NULL;
559 l_lot_rec.qc_grade := NULL;
560 l_lot_rec.lot_created := SYSDATE;
561 l_lot_rec.expire_date := NULL;
562 l_lot_rec.origination_type := 3;
563 l_lot_rec.vendor_lot_no := NULL;
564 l_lot_rec.user_name := FND_GLOBAL.user_name;
565
566 IF (GMIGUTL.SETUP(l_lot_rec.user_name)) THEN
567 GMIPAPI.create_lot(
568 p_api_version => 3.0
569 , p_init_msg_list => 'T'
570 , p_commit => 'F'
571 , p_validation_level => 100
572 , p_lot_rec => l_lot_rec
573 , x_ic_lots_mst_row => l_ic_lots_mst_row
574 , x_ic_lots_cpg_row => l_ic_lots_cpg_row
575 , x_return_status => x_return_status
576 , x_msg_count => l_msg_count
577 , x_msg_data => l_msg_data
578 );
579
580 /*
581 dbms_output.put_line('error code in API = '||x_return_status);
582 dbms_output.put_line('error in API = '||l_msg_data);
583 dbms_output.put_line('error in API count= '||l_msg_count);
584 */
585 IF (x_return_status = 'S') THEN
586 x_lot_id := l_ic_lots_mst_row.lot_id;
587 ELSE
588 For I IN 1..l_msg_count LOOP
589 l_msg_data := fnd_msg_pub.get(I,'F');
590 -- FND_MESSAGE.SET_STRING (x_msg_data);
591
592 ---dbms_output.put_line('error = '||l_msg_data);
593 END LOOP;
594 x_error_msg := l_msg_data;
595 END IF;
596 ELSE
597 FND_MESSAGE.SET_NAME('GMI','GMI_XML_CONFIRM_DESCRTN_LOT_F');
598 x_error_msg := FND_MESSAGE.GET;
599 x_return_status := 'E';
600 END IF;
601
602 END Create_Lot;
603
604 --yannamal 4189249 Added NOCOPY for x_message
605 PROCEDURE get_stacked_messages(x_message OUT NOCOPY VARCHAR2)
606 IS
607 l_message VARCHAR2(2000);
608 l_msg_count NUMBER;
609 BEGIN
610 fnd_msg_pub.Count_And_Get
611 (p_encoded => FND_API.g_false,
612 p_count => l_msg_count,
613 p_data => l_message
614 );
615
616 IF l_msg_count > 1 THEN
617 FOR i IN 1..l_msg_count LOOP
618 l_message := substr((l_message || '|' || FND_MSG_PUB.GET(p_msg_index => l_msg_count - i + 1,
619 p_encoded => FND_API.g_false)),1,2000);
620 END LOOP;
621 END IF;
622
623 fnd_msg_pub.delete_msg;
624
625 x_message := l_message;
626
627 EXCEPTION
628 WHEN OTHERS THEN
629 NULL;
630
631 END get_stacked_messages;
632
633
634 PROCEDURE GET_DOC_LOV(x_doc_num_lov OUT NOCOPY t_genref,
635 p_organization_id IN NUMBER,
636 p_doc_number IN VARCHAR2,
637 p_mobile_form IN VARCHAR2,
638 p_manual_po_num_type IN VARCHAR2,
639 p_shipment_header_id IN VARCHAR2,
640 p_inventory_item_id IN VARCHAR2,
641 p_item_description IN VARCHAR2,
642 p_doc_type IN VARCHAR2,
643 p_vendor_prod_num IN VARCHAR2)
644
645 IS
646 BEGIN
647
648 IF p_mobile_form = 'RECEIPT' THEN
649 OPEN x_doc_num_lov FOR
650 -- This select takes care of Vendor Item and any non-expense item
651 -- and cross ref item case.
652 SELECT DISTINCT
653 -- DOCTYPE PO
654 meaning FIELD0
655 , poh.segment1 FIELD1
656 , to_char(poh.po_header_id) FIELD2
657 , poh.type_lookup_code FIELD3
658 , PO_VENDORS_SV2.GET_VENDOR_NAME_FUNC(POH.VENDOR_ID) FIELD4
659 , to_char(poh.vendor_id) FIELD5
660 , to_char(poh.vendor_site_id) FIELD6
661 , 'Vendor' FIELD7
662 , poh.note_to_receiver FIELD8
663 , Decode(p_manual_po_num_type,'NUMERIC', null, poh.segment1 ) FIELD9
664 , to_char(Decode(p_manual_po_num_type,'NUMERIC', to_number(poh.segment1),null)) FIELD10
665 , null FIELD11
666 , lookup_code FIELD12
667 FROM po_headers poh,
668 fnd_lookup_values_vl flv
669 WHERE flv.lookup_code = 'PO'
670 AND flv.lookup_type = 'DOC_TYPE'
671 AND nvl(flv.start_date_active, sysdate)<=sysdate
672 AND nvl(flv.end_date_active,sysdate)>=sysdate
673 AND flv.enabled_flag = 'Y'
674 AND exists (SELECT 'Valid PO Shipments'
675 FROM po_line_locations_all poll
676 WHERE poh.po_header_id = poll.po_header_id
677 AND Nvl(poll.approved_flag,'N') = 'Y'
678 AND Nvl(poll.cancel_flag,'N') = 'N'
679 -- AND poll.closed_code = 'OPEN' -- Bug 2859335
680 AND receiving_routing_id = 3 --- Direct only supported by OPM
681 AND Nvl(poll.closed_code,'OPEN') NOT IN ('CLOSED', 'FINALLY CLOSED','CLOSED FOR RECEIVING', 'CLOSED') -- 3687249
682 AND poll.shipment_type IN ('STANDARD','BLANKET','SCHEDULED')
683 AND poll.ship_to_organization_id = p_organization_id)
684 -- Bug 2859355 Added the Extra conditions for poh.
685 AND POH.TYPE_LOOKUP_CODE IN ('STANDARD','PLANNED', 'BLANKET','CONTRACT')
686 AND NVL(POH.CANCEL_FLAG, 'N') IN ('N', 'I')
687 AND NVL(POH.CLOSED_CODE, 'OPEN') NOT IN ('CLOSED', 'FINALLY CLOSED','CLOSED FOR RECEIVING', 'CLOSED') -- 3152693
688 AND poh.segment1 LIKE (p_doc_number)
689 AND exists ( select 'x'
690 from po_lines_all pl
691 , mtl_system_items_kfv msi
692 where pl.item_id = msi.inventory_item_id (+)
693 and Nvl(msi.organization_id, p_organization_id) = p_organization_id
694 and pl.po_header_id = poh.po_header_id
695 AND Nvl(pl.vendor_product_num,' ') =
696 Nvl(p_vendor_prod_num, Nvl(pl.vendor_product_num,' '))
697 and Nvl(pl.item_id,-999) like Nvl(p_inventory_item_id,'%')
698 )
699 AND p_item_description is null
700 UNION
701 -- This Select Handles Substitute Items
702 SELECT DISTINCT
703 -- DOCTYPE PO
704 meaning FIELD0
705 , poh.segment1 FIELD1
706 , to_char(poh.po_header_id) FIELD2
707 , poh.type_lookup_code FIELD3
708 , PO_VENDORS_SV2.GET_VENDOR_NAME_FUNC(POH.VENDOR_ID) FIELD4
709 , to_char(poh.vendor_id) FIELD5
710 , to_char(poh.vendor_site_id) FIELD6
711 , 'Vendor' FIELD7
712 , poh.note_to_receiver FIELD8
713 , Decode(p_manual_po_num_type,'NUMERIC', null, poh.segment1 ) FIELD9
714 , to_char(Decode(p_manual_po_num_type,'NUMERIC', to_number(poh.segment1),null)) FIELD10
715 , null FIELD11
716 , lookup_code FIELD12
717 FROM po_headers poh,
718 fnd_lookup_values_vl flv
719 WHERE flv.lookup_code = 'PO'
720 AND flv.lookup_type = 'DOC_TYPE'
721 AND nvl(flv.start_date_active, sysdate)<=sysdate
722 AND nvl(flv.end_date_active,sysdate)>=sysdate
723 AND flv.enabled_flag = 'Y'
724 AND exists (SELECT 'Valid PO Shipments'
725 FROM po_line_locations_all poll
726 WHERE poh.po_header_id = poll.po_header_id
727 AND Nvl(poll.approved_flag,'N') = 'Y'
728 AND Nvl(poll.cancel_flag,'N') = 'N'
729 -- AND poll.closed_code = 'OPEN' -- Bug 2859355
730 AND receiving_routing_id = 3 --- Direct only supported by OPM
731 AND Nvl(poll.closed_code,'OPEN') NOT IN ('CLOSED', 'FINALLY CLOSED','CLOSED FOR RECEIVING', 'CLOSED') -- 3687249
732 AND poll.shipment_type IN ('STANDARD','BLANKET','SCHEDULED')
733 AND poll.ship_to_organization_id = p_organization_id)
734 -- Bug 2859355 Added the Extra conditions for poh.
735 AND POH.TYPE_LOOKUP_CODE IN ('STANDARD','PLANNED', 'BLANKET','CONTRACT')
736 AND NVL(POH.CANCEL_FLAG, 'N') IN ('N', 'I')
737 AND NVL(POH.CLOSED_CODE, 'OPEN') NOT IN ('CLOSED', 'FINALLY CLOSED','CLOSED FOR RECEIVING', 'CLOSED') -- 3152693
738 AND poh.segment1 LIKE (p_doc_number)
739 AND exists ( select 'x'
740 from po_lines_all pl
741 , mtl_related_items mri
742 , mtl_system_items_kfv msi
743 Where msi.organization_id = p_organization_id
744 and (( mri.related_item_id = msi.inventory_item_id
745 and pl.item_id = mri.inventory_item_id
746 and msi.inventory_item_id like p_inventory_item_id ) or
747 ( mri.inventory_item_id = msi.inventory_item_id
748 and pl.item_id = mri.related_item_id and mri.reciprocal_flag = 'Y'
749 and msi.inventory_item_id like p_inventory_item_id ))
750 and pl.po_header_id = poh.po_header_id
751 AND Nvl(pl.vendor_product_num,' ') =
752 Nvl(p_vendor_prod_num,Nvl(pl.vendor_product_num,' '))
753 )
754 AND p_item_description is null
755 UNION
756 -- This Select Handles Expense Items
757 SELECT DISTINCT
758 -- DOCTYPE PO
759 meaning FIELD0
760 , poh.segment1 FIELD1
761 , to_char(poh.po_header_id) FIELD2
762 , poh.type_lookup_code FIELD3
763 , PO_VENDORS_SV2.GET_VENDOR_NAME_FUNC(POH.VENDOR_ID) FIELD4
764 , to_char(poh.vendor_id) FIELD5
765 , to_char(poh.vendor_site_id) FIELD6
766 , 'Vendor' FIELD7
767 , poh.note_to_receiver FIELD8
768 , Decode(p_manual_po_num_type,'NUMERIC', null, poh.segment1 ) FIELD9
769 , to_char(Decode(p_manual_po_num_type,'NUMERIC', to_number(poh.segment1),null)) FIELD10
770 , null FIELD11
771 , lookup_code FIELD12
772 FROM po_headers poh,
773 fnd_lookup_values_vl flv
774 WHERE flv.lookup_code = 'PO'
775 AND flv.lookup_type = 'DOC_TYPE'
776 AND nvl(flv.start_date_active, sysdate)<=sysdate
777 AND nvl(flv.end_date_active,sysdate)>=sysdate
778 AND flv.enabled_flag = 'Y'
779 AND exists (SELECT 'Valid PO Shipments'
780 FROM po_line_locations_all poll
781 WHERE poh.po_header_id = poll.po_header_id
782 AND Nvl(poll.approved_flag,'N') = 'Y'
783 AND Nvl(poll.cancel_flag,'N') = 'N'
784 AND receiving_routing_id = 3 --- Direct only supported by OPM
785 -- AND poll.closed_code = 'OPEN' --Bug 2859355
786 AND Nvl(poll.closed_code,'OPEN') NOT IN ('CLOSED', 'FINALLY CLOSED','CLOSED FOR RECEIVING', 'CLOSED') -- 3687249
787 AND poll.shipment_type IN ('STANDARD','BLANKET','SCHEDULED')
788 AND poll.ship_to_organization_id = p_organization_id)
789 -- Bug 2859355 Added the Extra conditions for poh.
790 AND POH.TYPE_LOOKUP_CODE IN ('STANDARD','PLANNED', 'BLANKET','CONTRACT')
791 AND NVL(POH.CANCEL_FLAG, 'N') IN ('N', 'I')
792 AND NVL(POH.CLOSED_CODE, 'OPEN') NOT IN ('CLOSED', 'FINALLY CLOSED','CLOSED FOR RECEIVING', 'CLOSED') -- 3152693
793 AND poh.segment1 LIKE (p_doc_number)
794 AND exists ( select 'x'
795 from po_lines_all pol
796 ,mtl_units_of_measure mum
797 where mum.UNIT_OF_MEASURE(+) = pol.UNIT_MEAS_LOOKUP_CODE
798 and mum.base_uom_flag(+) = 'Y'
799 and pol.ITEM_ID is null
800 and pol.item_description like p_item_description||'%'
801 AND pol.po_header_id = poh.po_header_id
802 AND Nvl(pol.vendor_product_num,' ') =
803 Nvl(p_vendor_prod_num,Nvl(pol.vendor_product_num,' '))
804 )
805 AND p_item_description is not null
806 ORDER BY 1,2
807 ;
808 END IF;
809
810 END get_doc_lov;
811
812
813
814 PROCEDURE GET_ITEM_LOV_RECEIVING (
815 x_Items OUT NOCOPY t_genref,
816 p_Organization_Id IN NUMBER,
817 p_Concatenated_Segments IN VARCHAR2,
818 p_poHeaderID IN VARCHAR2,
819 p_poReleaseID IN VARCHAR2,
820 p_poLineID IN VARCHAR2,
821 p_shipmentHeaderID IN VARCHAR2,
822 p_oeOrderHeaderID IN VARCHAR2,
823 p_reqHeaderID IN VARCHAR2,
824 p_projectId IN VARCHAR2,
825 p_taskId IN VARCHAR2,
826 p_pjmorg IN VARCHAR2,
827 p_crossreftype IN VARCHAR2
828 )
829
830 IS
831 -- Changes for GTIN CrossRef Type
832 --
833 g_gtin_cross_ref_type VARCHAR2(25) := fnd_profile.value('INV:GTIN_CROSS_REFERENCE_TYPE');
834 g_gtin_code_length NUMBER := 14;
835 g_crossref VARCHAR2(40) := lpad(Rtrim(p_concatenated_segments, '%'), g_gtin_code_length, '00000000000000');
836
837 BEGIN
838
839
840 -- if ( ( p_doctype = 'PO') or (p_doctype = 'RMA') or (p_doctype = 'REQ') or (p_doctype = 'SHIP') )
841 -- then
842
843 if (p_poHeaderID is not null or
844 p_poReleaseID is not null or
845 p_oeOrderHeaderID is not null or
846 p_shipmentHeaderID is not null or
847 p_reqHeaderID is not null or
848 p_projectId is not null or
849 p_taskId is not null )
850 then
851
852 -- *****************************
853 ---- Case for Document Info already entered in the session , txn starts with document ID
854 -- *****************************
855
856 if (p_poHeaderID is not null ) then
857 -- *****************************
858 --- START OF PO HEADER ID SECTION
859 -- *****************************
860
861 if ( p_pjmorg = 1) then --and ( p_projectId is not null ) ) then
862
863 -- *****************************
864 ---- Start of PJM BASED Tran.
865 -- *****************************
866
867 if (p_poReleaseID is not null) then
868 -- *****************************
869 --- releaseBased PJM Transaction
870 -- *****************************
871 open x_items for
872 select concatenated_segments,
873 inventory_item_id,
874 description,
875 Nvl(revision_qty_control_code,1),
876 Nvl(lot_control_code, 1),
877 Nvl(serial_number_control_code, 1),
878 Nvl(restrict_subinventories_code, 2),
879 Nvl(restrict_locators_code, 2),
880 Nvl(location_control_code, 1),
881 primary_uom_code,
882 Nvl(inspection_required_flag, 'N'),
883 Nvl(shelf_life_code, 1),
884 Nvl(shelf_life_days,0),
885 Nvl(allowed_units_lookup_code, 2),
886 Nvl(effectivity_control,1),
887 0,
888 0,
889 Nvl(default_serial_status_id,1),
890 Nvl(serial_status_enabled,'N'),
891 Nvl(default_lot_status_id,0),
892 Nvl(lot_status_enabled,'N'),
893 '',
894 'N',
895 inventory_item_flag,
896 0,
897 inventory_asset_flag,
898 outside_operation_flag
899 from mtl_system_items_kfv
900 WHERE organization_id = p_Organization_Id
901 and concatenated_segments like p_concatenated_segments
902 and (purchasing_enabled_flag = 'Y' OR stock_enabled_flag = 'Y')
903 and inventory_item_id IN (SELECT pol.item_id FROM po_lines_all pol
904 where pol.po_header_id = p_poHeaderID
905 and exists (select 1 from po_line_locations_all pll WHERE NVL(pll.closed_code,'OPEN')
906 not in ('CLOSED', 'FINALLY CLOSED', 'CLOSED FOR RECEIVING') and
907 Nvl(pll.ship_to_organization_id, p_organization_id) = p_organization_id and
908 pll.po_header_id = p_poHeaderID
909 and pll.po_release_id = p_poReleaseID
910 and pll.po_line_id = pol.po_line_id
911 AND Nvl(pll.cancel_flag,'N') = 'N'
912 and pll.receiving_routing_id = 3)
913 and exists (select 1 from po_distributions_all pd where pd.po_header_id = p_poHeaderID
914 and pd.po_line_id = pol.po_line_id
915 and pd.po_release_id = p_poReleaseID
916 and ((p_projectId is null or pd.project_id = p_projectId)
917 and (p_taskId is null or pd.task_id = p_taskId)
918 )
919 )
920 )
921 UNION ALL
922 -- Substitute Item SQL
923 select distinct msi.concatenated_segments,
924 msi.inventory_item_id,
925 msi.description,
926 Nvl(msi.revision_qty_control_code,1),
927 Nvl(msi.lot_control_code, 1),
928 Nvl(msi.serial_number_control_code, 1),
929 Nvl(msi.restrict_subinventories_code, 2),
930 Nvl(msi.restrict_locators_code,2),
931 Nvl(msi.location_control_code,1),
932 msi.primary_uom_code,
933 Nvl(msi.inspection_required_flag,'N'),
934 Nvl(msi.shelf_life_code, 1),
935 Nvl(msi.shelf_life_days,0),
936 Nvl(msi.allowed_units_lookup_code, 2),
937 Nvl(msi.effectivity_control,1),
938 0,
939 0,
940 Nvl(msi.default_serial_status_id,1),
941 Nvl(msi.serial_status_enabled,'N'),
942 Nvl(msi.default_lot_status_id,0),
943 Nvl(msi.lot_status_enabled,'N'),
944 msia.concatenated_segments,
945 'S',
946 msi.inventory_item_flag,
947 0,
948 msi.inventory_asset_flag,
949 msi.outside_operation_flag
950 from po_lines_all pol
951 ,mtl_related_items mri
952 ,mtl_system_items_kfv msi
953 ,mtl_system_items_kfv msia
954 where msi.organization_id = p_organization_id
955 and msi.concatenated_segments like p_concatenated_segments
956 and pol.po_header_id = p_poHeaderID
957 and pol.item_id = msia.inventory_item_id
958 and msia.organization_id = p_organization_id
959 and (( mri.related_item_id = msi.inventory_item_id
960 and pol.item_id = mri.inventory_item_id) or
961 ( mri.inventory_item_id = msi.inventory_item_id
962 and pol.item_id = mri.related_item_id
963 and mri.reciprocal_flag = 'Y'))
964 and exists (select 1 from po_line_locations_all pll
965 where NVL(pll.closed_code,'OPEN')
966 not in ('CLOSED', 'FINALLY CLOSED', 'CLOSED FOR RECEIVING')
967 and Nvl(pll.ship_to_organization_id, p_organization_id) = p_organization_id
968 and Nvl(pll.allow_substitute_receipts_flag, 'N') = 'Y'
969 and pll.po_header_id = pol.po_header_id
970 and pll.po_line_id = pol.po_line_id
971 and pll.po_release_id = p_poReleaseID
972 AND Nvl(pll.cancel_flag,'N') = 'N'
973 and pll.receiving_routing_id = 3)
974 and exists (select 1 from po_distributions_all pd where pd.po_header_id = p_poHeaderID
975 and pd.po_line_id = pol.po_line_id
976 and pd.po_release_id = p_poReleaseID
977 and ((p_projectId is null or pd.project_id = p_projectId)
978 and (p_taskId is null or pd.task_id = p_taskId)
979 )
980 )
981 UNION ALL
982 -- Vendor Item SQL
983 select distinct pol.vendor_product_num,
984 msi.inventory_item_id,
985 msi.description,
986 Nvl(msi.revision_qty_control_code,1),
987 Nvl(msi.lot_control_code, 1),
988 Nvl(msi.serial_number_control_code, 1),
989 Nvl(msi.restrict_subinventories_code, 2),
990 Nvl(msi.restrict_locators_code,2),
991 Nvl(msi.location_control_code,1),
992 msi.primary_uom_code,
993 Nvl(msi.inspection_required_flag,'N'),
994 Nvl(msi.shelf_life_code, 1),
995 Nvl(msi.shelf_life_days,0),
996 Nvl(msi.allowed_units_lookup_code, 2),
997 Nvl(msi.effectivity_control,1),
998 0,
999 0,
1000 Nvl(msi.default_serial_status_id,1),
1001 Nvl(msi.serial_status_enabled,'N'),
1002 Nvl(msi.default_lot_status_id,0),
1003 Nvl(msi.lot_status_enabled,'N'),
1004 msi.concatenated_segments,
1005 'Y',
1006 msi.inventory_item_flag,
1007 0,
1008 msi.inventory_asset_flag,
1009 msi.outside_operation_flag
1010 from po_lines_all pol
1011 , mtl_system_items_kfv msi
1012 where organization_id = p_organization_id
1013 and pol.vendor_product_num like p_concatenated_segments
1014 and pol.item_id = msi.inventory_item_id
1015 and pol.vendor_product_num IS NOT NULL
1016 and pol.po_header_id = p_poHeaderID
1017 and (purchasing_enabled_flag = 'Y' OR stock_enabled_flag = 'Y')
1018 and inventory_item_id IN (SELECT pol.item_id FROM po_lines_all pol
1019 where pol.po_header_id = p_poHeaderID
1020 and exists (select 1 from po_line_locations_all pll WHERE NVL(pll.closed_code,'OPEN')
1021 not in ('CLOSED', 'FINALLY CLOSED', 'CLOSED FOR RECEIVING') and
1022 Nvl(pll.ship_to_organization_id, p_organization_id) = p_organization_id and
1023 pll.po_header_id = p_poHeaderID
1024 and pll.po_release_id = p_poReleaseID
1025 and pll.po_line_id = pol.po_line_id
1026 AND Nvl(pll.cancel_flag,'N') = 'N'
1027 and pll.receiving_routing_id = 3)
1028 and exists (select 1 from po_distributions_all pd where pd.po_header_id = p_poHeaderID
1029 and pd.po_line_id = pol.po_line_id
1030 and pd.po_release_id = p_poReleaseID
1031 and ((p_projectId is null or pd.project_id = p_projectId)
1032 and (p_taskId is null or pd.task_id = p_taskId)
1033 )
1034 )
1035 )
1036 UNION ALL
1037 -- non item Master
1038 select distinct pol.item_description,
1039 to_number(''),
1040 pol.item_description,
1041 1,
1042 1,
1043 1,
1044 2,
1045 2,
1046 1,
1047 mum.uom_code,
1048 'N',
1049 1,
1050 0,
1051 2,
1052 1,
1053 0,
1054 0,
1055 1,
1056 'N',
1057 0,
1058 'N',
1059 '',
1060 'N',
1061 'N',
1062 0,
1063 to_char(NULL),
1064 'N'
1065 from po_lines_all pol
1066 , mtl_units_of_measure mum
1067 -- Bug 2619063, 2614016
1068 -- Modified to select the base uom for the uom class defined on po.
1069 where mum.uom_class = (SELECT mum2.uom_class
1070 FROM mtl_units_of_measure mum2
1071 WHERE mum2.unit_of_measure(+) = pol.unit_meas_lookup_code)
1072 and mum.base_uom_flag = 'Y'
1073 and pol.ITEM_ID is null
1074 and pol.item_description is not null
1075 and pol.po_header_id = p_poHeaderID
1076 and pol.item_description like p_concatenated_segments
1077 and exists (select 1 from po_distributions_all pd where pd.po_header_id = p_poHeaderID
1078 and pd.po_line_id = pol.po_line_id
1079 and pd.po_release_id = p_poReleaseID
1080 and ((p_projectId is null or pd.project_id = p_projectId)
1081 and (p_taskId is null or pd.task_id = p_taskId)
1082 )
1083 )
1084 UNION ALL
1085 -- Cross Ref SQL
1086 select distinct msi.concatenated_segments,
1087 ---select distinct mcr.cross_reference,
1088 msi.inventory_item_id,
1089 msi.description,
1090 Nvl(msi.revision_qty_control_code,1),
1091 Nvl(msi.lot_control_code, 1),
1092 Nvl(msi.serial_number_control_code, 1),
1093 Nvl(msi.restrict_subinventories_code, 2),
1094 Nvl(msi.restrict_locators_code,2),
1095 Nvl(msi.location_control_code,1),
1096 msi.primary_uom_code,
1097 Nvl(msi.inspection_required_flag,'N'),
1098 Nvl(msi.shelf_life_code, 1),
1099 Nvl(msi.shelf_life_days,0),
1100 Nvl(msi.allowed_units_lookup_code, 2),
1101 Nvl(msi.effectivity_control,1),
1102 0,
1103 0,
1104 Nvl(msi.default_serial_status_id,1),
1105 Nvl(msi.serial_status_enabled,'N'),
1106 Nvl(msi.default_lot_status_id,0),
1107 Nvl(msi.lot_status_enabled,'N'),
1108 ---msi.concatenated_segments,
1109 mcr.cross_reference,
1110 'C',
1111 msi.inventory_item_flag,
1112 0,
1113 msi.inventory_asset_flag,
1114 msi.outside_operation_flag
1115 from po_lines_all pol
1116 ,mtl_system_items_kfv msi
1117 ,mtl_cross_references mcr
1118 where msi.organization_id = p_organization_id
1119 and ( (mcr.cross_reference_type = p_crossreftype
1120 and mcr.cross_reference like p_concatenated_segments
1121 ) or
1122 ( mcr.cross_reference_type = g_gtin_cross_ref_type
1123 AND mcr.cross_reference LIKE g_crossref )
1124 )
1125 and ( (mcr.org_independent_flag = 'Y') or (mcr.org_independent_flag = 'N'
1126 and mcr.organization_id = p_organization_id
1127 ) )
1128 and mcr.inventory_item_id = msi.inventory_item_id
1129 and pol.item_id = msi.inventory_item_id
1130 and pol.po_header_id = p_poHeaderID
1131 and (purchasing_enabled_flag = 'Y' OR stock_enabled_flag = 'Y')
1132 and msi.inventory_item_id IN (SELECT pol.item_id FROM po_lines_all pol
1133 where pol.po_header_id = p_poHeaderID
1134 and exists (select 1 from po_line_locations_all pll WHERE NVL(pll.closed_code,'OPEN')
1135 not in ('CLOSED', 'FINALLY CLOSED', 'CLOSED FOR RECEIVING') and
1136 Nvl(pll.ship_to_organization_id, p_organization_id) = p_organization_id and
1137 pll.po_header_id = p_poHeaderID
1138 and pll.po_release_id = p_poReleaseID
1139 and pll.po_line_id = pol.po_line_id
1140 AND Nvl(pll.cancel_flag,'N') = 'N'
1141 and pll.receiving_routing_id = 3)
1142 and exists (select 1 from po_distributions_all pd where pd.po_header_id =
1143 p_poHeaderID
1144 and pd.po_line_id = pol.po_line_id
1145 and pd.po_release_id = p_poReleaseID
1146 and ((p_projectId is null or pd.project_id = p_projectId)
1147 and (p_taskId is null or pd.task_id = p_taskId)
1148 )
1149 )
1150 )
1151 ;
1152 elsif (p_poLineID IS NOT NULL) then
1153 -- *****************************
1154 ----- lineBased PJM Transaction
1155 -- *****************************
1156 open x_items for
1157 select concatenated_segments,
1158 inventory_item_id,
1159 description,
1160 Nvl(revision_qty_control_code,1),
1161 Nvl(lot_control_code, 1),
1162 Nvl(serial_number_control_code, 1),
1163 Nvl(restrict_subinventories_code, 2),
1164 Nvl(restrict_locators_code, 2),
1165 Nvl(location_control_code, 1),
1166 primary_uom_code,
1167 Nvl(inspection_required_flag, 'N'),
1168 Nvl(shelf_life_code, 1),
1169 Nvl(shelf_life_days,0),
1170 Nvl(allowed_units_lookup_code, 2),
1171 Nvl(effectivity_control,1),
1172 0,
1173 0,
1174 Nvl(default_serial_status_id,1),
1175 Nvl(serial_status_enabled,'N'),
1176 Nvl(default_lot_status_id,0),
1177 Nvl(lot_status_enabled,'N'),
1178 '',
1179 'N',
1180 inventory_item_flag,
1181 0,
1182 inventory_asset_flag,
1183 outside_operation_flag
1184 from mtl_system_items_kfv
1185 WHERE organization_id = p_Organization_Id
1186 and concatenated_segments like p_concatenated_segments
1187 and (purchasing_enabled_flag = 'Y' OR stock_enabled_flag = 'Y')
1188 and inventory_item_id IN (SELECT pol.item_id FROM po_lines_all pol WHERE
1189 pol.po_header_id = p_poHeaderID
1190 and pol.po_line_id = p_poLineID
1191 and exists (select 1 from po_line_locations_all pll where NVL(pll.closed_code, 'OPEN')
1192 not in ('CLOSED', 'FINALLY CLOSED' , 'CLOSED FOR RECEIVING' )
1193 and Nvl(pll.ship_to_organization_id, p_organization_id) = p_organization_id
1194 and pll.po_header_id = p_poHeaderID
1195 and pll.po_line_id = p_poLineID
1196 AND Nvl(pll.cancel_flag,'N') = 'N'
1197 and pll.receiving_routing_id = 3
1198 ) and exists (select 1 from po_distributions_all pd
1199 where pd.po_header_id = p_poHeaderID
1200 and pd.po_line_id = p_poLineID
1201 and pd.po_line_id = pol.po_line_id
1202 and ((p_projectId is null or pd.project_id = p_projectId)
1203 and (p_taskId is null or pd.task_id = p_taskId)
1204 )
1205 )
1206 )
1207 UNION ALL
1208 -- Substitute Item SQL
1209 select distinct msi.concatenated_segments,
1210 msi.inventory_item_id,
1211 msi.description,
1212 Nvl(msi.revision_qty_control_code,1),
1213 Nvl(msi.lot_control_code, 1),
1214 Nvl(msi.serial_number_control_code, 1),
1215 Nvl(msi.restrict_subinventories_code, 2),
1216 Nvl(msi.restrict_locators_code,2),
1217 Nvl(msi.location_control_code,1),
1218 msi.primary_uom_code,
1219 Nvl(msi.inspection_required_flag,'N'),
1220 Nvl(msi.shelf_life_code, 1),
1221 Nvl(msi.shelf_life_days,0),
1222 Nvl(msi.allowed_units_lookup_code, 2),
1223 Nvl(msi.effectivity_control,1),
1224 0,
1225 0,
1226 Nvl(msi.default_serial_status_id,1),
1227 Nvl(msi.serial_status_enabled,'N'),
1228 Nvl(msi.default_lot_status_id,0),
1229 Nvl(msi.lot_status_enabled,'N'),
1230 msia.concatenated_segments,
1231 'S',
1232 msi.inventory_item_flag,
1233 0,
1234 msi.inventory_asset_flag,
1235 msi.outside_operation_flag
1236 from po_lines_all pol
1237 ,mtl_related_items mri
1238 ,mtl_system_items_kfv msi
1239 ,mtl_system_items_kfv msia
1240 where msi.organization_id = p_organization_id
1241 and msi.concatenated_segments like p_concatenated_segments
1242 and pol.po_header_id = p_poHeaderID
1243 and pol.item_id = msia.inventory_item_id
1244 and msia.organization_id = p_organization_id
1245 and (( mri.related_item_id = msi.inventory_item_id
1246 and pol.item_id = mri.inventory_item_id) or
1247 ( mri.inventory_item_id = msi.inventory_item_id
1248 and pol.item_id = mri.related_item_id
1249 and mri.reciprocal_flag = 'Y'))
1250 and pol.po_line_id = p_poLineID
1251 and exists (select 1 from po_line_locations_all pll
1252 where NVL(pll.closed_code,'OPEN') not in
1253 ('CLOSED', 'FINALLY CLOSED', 'CLOSED FOR RECEIVING')
1254 and Nvl(pll.ship_to_organization_id, p_organization_id) = p_organization_id
1255 and Nvl(pll.allow_substitute_receipts_flag, 'N') = 'Y'
1256 and pll.po_header_id = pol.po_header_id
1257 and pll.po_line_id = pol.po_line_id
1258 AND Nvl(pll.cancel_flag,'N') = 'N'
1259 and pll.receiving_routing_id = 3)
1260 and exists ( select 1 from po_distributions_all pd
1261 where pd.po_header_id = p_poHeaderID
1262 and pd.po_line_id = p_poLineID
1263 and pd.po_line_id = pol.po_line_id
1264 and ((p_projectId is null or pd.project_id = p_projectId)
1265 and (p_taskId is null or pd.task_id = p_taskId)
1266 )
1267 )
1268 UNION ALL
1269 -- Vendor Item SQL
1270 select distinct pol.vendor_product_num,
1271 msi.inventory_item_id,
1272 msi.description,
1273 Nvl(msi.revision_qty_control_code,1),
1274 Nvl(msi.lot_control_code, 1),
1275 Nvl(msi.serial_number_control_code, 1),
1276 Nvl(msi.restrict_subinventories_code, 2),
1277 Nvl(msi.restrict_locators_code,2),
1278 Nvl(msi.location_control_code,1),
1279 msi.primary_uom_code,
1280 Nvl(msi.inspection_required_flag,'N'),
1281 Nvl(msi.shelf_life_code, 1),
1282 Nvl(msi.shelf_life_days,0),
1283 Nvl(msi.allowed_units_lookup_code, 2),
1284 Nvl(msi.effectivity_control,1),
1285 0,
1286 0,
1287 Nvl(msi.default_serial_status_id,1),
1288 Nvl(msi.serial_status_enabled,'N'),
1289 Nvl(msi.default_lot_status_id,0),
1290 Nvl(msi.lot_status_enabled,'N'),
1291 msi.concatenated_segments,
1292 'Y',
1293 msi.inventory_item_flag,
1294 0,
1295 msi.inventory_asset_flag,
1296 msi.outside_operation_flag
1297 from po_lines_all pol
1298 , mtl_system_items_kfv msi
1299 where organization_id = p_organization_id
1300 and pol.vendor_product_num like p_concatenated_segments
1301 and pol.item_id = msi.inventory_item_id
1302 and pol.vendor_product_num IS NOT NULL
1303 and pol.po_header_id = p_poHeaderID
1304 and (purchasing_enabled_flag = 'Y' OR stock_enabled_flag = 'Y')
1305 and inventory_item_id IN (SELECT pl.item_id FROM po_lines_all pl WHERE
1306 pl.po_header_id = p_poHeaderID
1307 and pl.po_line_id = p_poLineID
1308 and exists (select 1 from po_line_locations_all pll where NVL(pll.closed_code, 'OPEN')
1309 not in ('CLOSED', 'FINALLY CLOSED' , 'CLOSED FOR RECEIVING' )
1310 and Nvl(pll.ship_to_organization_id, p_organization_id) = p_organization_id
1311 and pll.po_header_id = p_poHeaderID
1312 and pll.po_line_id = p_poLineID
1313 AND Nvl(pll.cancel_flag,'N') = 'N'
1314 and pll.receiving_routing_id = 3)
1315 and exists (select 1 from po_distributions_all pd
1316 where pd.po_header_id = p_poHeaderID
1317 and pd.po_line_id = pol.po_line_id
1318 and pd.po_line_id = p_poLineID
1319 and ((p_projectId is null or pd.project_id = p_projectId)
1320 and (p_taskId is null or pd.task_id = p_taskId)
1321 )
1322 )
1323 )
1324 UNION ALL
1325 -- non item Master
1326 select distinct pol.item_description,
1327 to_number(''),
1328 pol.item_description,
1329 1,
1330 1,
1331 1,
1332 2,
1333 2,
1334 1,
1335 mum.uom_code,
1336 'N',
1337 1,
1338 0,
1339 2,
1340 1,
1341 0,
1342 0,
1343 1,
1344 'N',
1345 0,
1346 'N',
1347 '',
1348 'N',
1349 'N',
1350 0,
1351 to_char(NULL),
1352 'N'
1353 from po_lines_all pol
1354 , mtl_units_of_measure mum
1355 -- Bug 2619063, 2614016
1356 -- Modified to select the base uom for the uom class defined on po.
1357 where mum.uom_class = (SELECT mum2.uom_class
1358 FROM mtl_units_of_measure mum2
1359 WHERE mum2.unit_of_measure(+) = pol.unit_meas_lookup_code)
1360 and mum.base_uom_flag = 'Y'
1361 and pol.ITEM_ID is null
1362 and pol.item_description is not null
1363 and pol.po_header_id = p_poHeaderID
1364 and pol.item_description like p_concatenated_segments
1365 and exists ( select 1 from po_distributions_all pd
1366 where pd.po_header_id = p_poHeaderID
1367 and pd.po_line_id = p_poLineID
1368 and pd.po_line_id = pol.po_line_id
1369 and ((p_projectId is null or pd.project_id = p_projectId)
1370 and (p_taskId is null or pd.task_id = p_taskId)
1371 )
1372 )
1373 UNION ALL
1374 -- Cross Ref SQL
1375 ---select distinct mcr.cross_reference,
1376 select distinct msi.concatenated_segments,
1377 msi.inventory_item_id,
1378 msi.description,
1379 Nvl(msi.revision_qty_control_code,1),
1380 Nvl(msi.lot_control_code, 1),
1381 Nvl(msi.serial_number_control_code, 1),
1382 Nvl(msi.restrict_subinventories_code, 2),
1383 Nvl(msi.restrict_locators_code,2),
1384 Nvl(msi.location_control_code,1),
1385 msi.primary_uom_code,
1386 Nvl(msi.inspection_required_flag,'N'),
1387 Nvl(msi.shelf_life_code, 1),
1388 Nvl(msi.shelf_life_days,0),
1389 Nvl(msi.allowed_units_lookup_code, 2),
1390 Nvl(msi.effectivity_control,1),
1391 0,
1392 0,
1393 Nvl(msi.default_serial_status_id,1),
1394 Nvl(msi.serial_status_enabled,'N'),
1395 Nvl(msi.default_lot_status_id,0),
1396 Nvl(msi.lot_status_enabled,'N'),
1397 ---msi.concatenated_segments,
1398 mcr.cross_reference,
1399 'C',
1400 msi.inventory_item_flag,
1401 0,
1402 msi.inventory_asset_flag,
1403 msi.outside_operation_flag
1404 from po_lines_all pol
1405 ,mtl_system_items_kfv msi
1406 ,mtl_cross_references mcr
1407 where msi.organization_id = p_organization_id
1408 and ( (mcr.cross_reference_type = p_crossreftype
1409 and mcr.cross_reference like p_concatenated_segments
1410 ) or
1411 ( mcr.cross_reference_type = g_gtin_cross_ref_type
1412 AND mcr.cross_reference LIKE g_crossref )
1413 )
1414 and ( (mcr.org_independent_flag = 'Y') or (mcr.org_independent_flag = 'N'
1415 and mcr.organization_id = p_organization_id
1416 ) )
1417 and mcr.inventory_item_id = msi.inventory_item_id
1418 and pol.item_id = msi.inventory_item_id
1419 and pol.po_header_id = p_poHeaderID
1420 and (purchasing_enabled_flag = 'Y' OR stock_enabled_flag = 'Y')
1421 and msi.inventory_item_id IN (SELECT pl.item_id FROM po_lines_all pl WHERE
1422 pl.po_header_id = p_poHeaderID
1423 and pl.po_line_id = p_poLineID
1424 and exists (select 1 from po_line_locations_all pll where NVL(pll.closed_code, 'OPEN')
1425 not in ('CLOSED', 'FINALLY CLOSED' , 'CLOSED FOR RECEIVING' )
1426 and Nvl(pll.ship_to_organization_id,p_organization_id) = p_organization_id
1427 and pll.po_header_id = p_poHeaderID
1428 and pll.po_line_id = p_poLineID
1429 AND Nvl(pll.cancel_flag,'N') = 'N'
1430 and pll.receiving_routing_id = 3
1431 ) and exists (select 1 from po_distributions_all pd
1432 where pd.po_header_id = p_poHeaderID
1433 and pd.po_line_id = pol.po_line_id
1434 and pd.po_line_id = p_poLineID
1435 and ((p_projectId is null or pd.project_id = p_projectId)
1436 and (p_taskId is null or pd.task_id = p_taskId)
1437 )
1438 )
1439 )
1440 ;
1441 else
1442 -- *****************************
1443 --- headerBased PJM Transaction
1444 -- *****************************
1445 open x_items for
1446 select concatenated_segments,
1447 inventory_item_id,
1448 description,
1449 Nvl(revision_qty_control_code,1),
1450 Nvl(lot_control_code, 1),
1451 Nvl(serial_number_control_code, 1),
1452 Nvl(restrict_subinventories_code, 2),
1453 Nvl(restrict_locators_code, 2),
1454 Nvl(location_control_code, 1),
1455 primary_uom_code,
1456 Nvl(inspection_required_flag, 'N'),
1457 Nvl(shelf_life_code, 1),
1458 Nvl(shelf_life_days,0),
1459 Nvl(allowed_units_lookup_code, 2),
1460 Nvl(effectivity_control,1),
1461 0,
1462 0,
1463 Nvl(default_serial_status_id,1),
1464 Nvl(serial_status_enabled,'N'),
1465 Nvl(default_lot_status_id,0),
1466 Nvl(lot_status_enabled,'N'),
1467 '',
1468 'N',
1469 inventory_item_flag,
1470 0,
1471 inventory_asset_flag,
1472 outside_operation_flag
1473 from mtl_system_items_kfv
1474 WHERE organization_id = p_Organization_Id
1475 and concatenated_segments like p_concatenated_segments
1476 and (purchasing_enabled_flag = 'Y' OR stock_enabled_flag = 'Y')
1477 and inventory_item_id IN
1478 ( SELECT pol.item_id FROM po_lines_all pol WHERE pol.po_header_id =
1479 p_poHeaderID
1480 and exists (select 1 from po_line_locations_all pll where NVL(pll.closed_code,'OPEN')
1481 not in ('CLOSED', 'FINALLY CLOSED', 'CLOSED FOR RECEIVING') and pll.po_header_id =
1482 p_poHeaderID and pll.po_line_id = pol.po_line_id
1483 and Nvl(pll.ship_to_organization_id, p_organization_id) = p_organization_id
1484 AND Nvl(pll.cancel_flag,'N') = 'N'
1485 and pll.receiving_routing_id = 3)
1486 and exists
1487 (select 1 from po_distributions_all pd
1488 where pd.po_header_id = p_poHeaderID
1489 and pd.po_line_id = pol.po_line_id
1490 and ((p_projectId is null or pd.project_id = p_projectId)
1491 and (p_taskId is null or pd.task_id = p_taskId)
1492 )
1493 )
1494 )
1495 UNION ALL
1496 -- Substitute Item SQL
1497 select distinct msi.concatenated_segments,
1498 msi.inventory_item_id,
1499 msi.description,
1500 Nvl(msi.revision_qty_control_code,1),
1501 Nvl(msi.lot_control_code, 1),
1502 Nvl(msi.serial_number_control_code, 1),
1503 Nvl(msi.restrict_subinventories_code, 2),
1504 Nvl(msi.restrict_locators_code,2),
1505 Nvl(msi.location_control_code,1),
1506 msi.primary_uom_code,
1507 Nvl(msi.inspection_required_flag,'N'),
1508 Nvl(msi.shelf_life_code, 1),
1509 Nvl(msi.shelf_life_days,0),
1510 Nvl(msi.allowed_units_lookup_code, 2),
1511 Nvl(msi.effectivity_control,1),
1512 0,
1513 0,
1514 Nvl(msi.default_serial_status_id,1),
1515 Nvl(msi.serial_status_enabled,'N'),
1516 Nvl(msi.default_lot_status_id,0),
1517 Nvl(msi.lot_status_enabled,'N'),
1518 msia.concatenated_segments,
1519 'S',
1520 msi.inventory_item_flag,
1521 0,
1522 msi.inventory_asset_flag,
1523 msi.outside_operation_flag
1524 from po_lines_all pol
1525 ,mtl_related_items mri
1526 ,mtl_system_items_kfv msi
1527 ,mtl_system_items_kfv msia
1528 where msi.organization_id = p_organization_id
1529 and msi.concatenated_segments like p_concatenated_segments
1530 and pol.po_header_id = p_poHeaderID
1531 and pol.item_id = msia.inventory_item_id
1532 and msia.organization_id = p_organization_id
1533 and (( mri.related_item_id = msi.inventory_item_id
1534 and pol.item_id = mri.inventory_item_id) or
1535 ( mri.inventory_item_id = msi.inventory_item_id
1536 and pol.item_id = mri.related_item_id
1537 and mri.reciprocal_flag = 'Y'))
1538 and exists (select 1 from po_line_locations_all pll
1539 where NVL(pll.closed_code,'OPEN') not in ('CLOSED', 'FINALLY CLOSED',
1540 'CLOSED FOR RECEIVING')
1541 and Nvl(pll.ship_to_organization_id, p_organization_id) = p_organization_id
1542 and Nvl(pll.allow_substitute_receipts_flag, 'N') = 'Y'
1543 and pll.po_header_id = pol.po_header_id
1544 and pll.po_line_id = pol.po_line_id
1545 AND Nvl(pll.cancel_flag,'N') = 'N'
1546 and pll.receiving_routing_id = 3)
1547 and exists
1548 (select 1 from po_distributions_all pd
1549 where pd.po_header_id = p_poHeaderID
1550 and pd.po_line_id = pol.po_line_id
1551 and ((p_projectId is null or pd.project_id = p_projectId)
1552 and (p_taskId is null or pd.task_id = p_taskId)
1553 )
1554 )
1555 UNION ALL
1556 -- Vendor Item SQL
1557 select distinct pol.vendor_product_num,
1558 msi.inventory_item_id,
1559 msi.description,
1560 Nvl(msi.revision_qty_control_code,1),
1561 Nvl(msi.lot_control_code, 1),
1562 Nvl(msi.serial_number_control_code, 1),
1563 Nvl(msi.restrict_subinventories_code, 2),
1564 Nvl(msi.restrict_locators_code,2),
1565 Nvl(msi.location_control_code,1),
1566 msi.primary_uom_code,
1567 Nvl(msi.inspection_required_flag,'N'),
1568 Nvl(msi.shelf_life_code, 1),
1569 Nvl(msi.shelf_life_days,0),
1570 Nvl(msi.allowed_units_lookup_code, 2),
1571 Nvl(msi.effectivity_control,1),
1572 0,
1573 0,
1574 Nvl(msi.default_serial_status_id,1),
1575 Nvl(msi.serial_status_enabled,'N'),
1576 Nvl(msi.default_lot_status_id,0),
1577 Nvl(msi.lot_status_enabled,'N'),
1578 msi.concatenated_segments,
1579 'Y',
1580 msi.inventory_item_flag,
1581 0,
1582 msi.inventory_asset_flag,
1583 msi.outside_operation_flag
1584 from po_lines_all pol
1585 , mtl_system_items_kfv msi
1586 where organization_id = p_organization_id
1587 and pol.vendor_product_num like p_concatenated_segments
1588 and pol.item_id = msi.inventory_item_id
1589 and pol.vendor_product_num IS NOT NULL
1590 and pol.po_header_id = p_poHeaderID
1591 and (purchasing_enabled_flag = 'Y' OR stock_enabled_flag = 'Y')
1592 and inventory_item_id IN
1593 ( SELECT pl.item_id FROM po_lines_all pl WHERE pl.po_header_id =
1594 p_poHeaderID
1595 and exists (select 1 from po_line_locations_all pll where NVL(pll.closed_code,'OPEN')
1596 not in ('CLOSED', 'FINALLY CLOSED', 'CLOSED FOR RECEIVING') and pll.po_header_id =
1597 p_poHeaderID and pll.po_line_id = pl.po_line_id
1598 and Nvl(pll.ship_to_organization_id, p_organization_id) = p_organization_id
1599 AND Nvl(pll.cancel_flag,'N') = 'N'
1600 and pll.receiving_routing_id = 3)
1601 and exists
1602 (select 1 from po_distributions_all pd
1603 where pd.po_header_id = p_poHeaderID
1604 and pd.po_line_id = pol.po_line_id
1605 and ((p_projectId is null or pd.project_id = p_projectId)
1606 and (p_taskId is null or pd.task_id = p_taskId)
1607 )
1608 )
1609 )
1610 UNION ALL
1611 -- non item Master
1612 select distinct pol.item_description,
1613 to_number(''),
1614 pol.item_description,
1615 1,
1616 1,
1617 1,
1618 2,
1619 2,
1620 1,
1621 mum.uom_code,
1622 'N',
1623 1,
1624 0,
1625 2,
1626 1,
1627 0,
1628 0,
1629 1,
1630 'N',
1631 0,
1632 'N',
1633 '',
1634 'N',
1635 'N',
1636 0,
1637 to_char(NULL),
1638 'N'
1639 from po_lines_all pol
1640 , mtl_units_of_measure mum
1641 -- Bug 2619063, 2614016
1642 -- Modified to select the base uom for the uom class defined on po.
1643 where mum.uom_class = (SELECT mum2.uom_class
1644 FROM mtl_units_of_measure mum2
1645 WHERE mum2.unit_of_measure(+) = pol.unit_meas_lookup_code)
1646 and mum.base_uom_flag = 'Y'
1647 and pol.ITEM_ID is null
1648 and pol.item_description is not null
1649 and pol.po_header_id = p_poHeaderID
1650 and pol.item_description like p_concatenated_segments
1651 and exists
1652 (select 1 from po_distributions_all pd
1653 where pd.po_header_id = p_poHeaderID
1654 and pd.po_line_id = pol.po_line_id
1655 and ((p_projectId is null or pd.project_id = p_projectId)
1656 and (p_taskId is null or pd.task_id = p_taskId)
1657 )
1658 )
1659 UNION ALL
1660 -- Cross Ref SQL
1661 ---select distinct mcr.cross_reference,
1662 select distinct msi.concatenated_segments,
1663 msi.inventory_item_id,
1664 msi.description,
1665 Nvl(msi.revision_qty_control_code,1),
1666 Nvl(msi.lot_control_code, 1),
1667 Nvl(msi.serial_number_control_code, 1),
1668 Nvl(msi.restrict_subinventories_code, 2),
1669 Nvl(msi.restrict_locators_code,2),
1670 Nvl(msi.location_control_code,1),
1671 msi.primary_uom_code,
1672 Nvl(msi.inspection_required_flag,'N'),
1673 Nvl(msi.shelf_life_code, 1),
1674 Nvl(msi.shelf_life_days,0),
1675 Nvl(msi.allowed_units_lookup_code, 2),
1676 Nvl(msi.effectivity_control,1),
1677 0,
1678 0,
1679 Nvl(msi.default_serial_status_id,1),
1680 Nvl(msi.serial_status_enabled,'N'),
1681 Nvl(msi.default_lot_status_id,0),
1682 Nvl(msi.lot_status_enabled,'N'),
1683 --- msi.concatenated_segments,
1684 mcr.cross_reference,
1685 'C',
1686 msi.inventory_item_flag,
1687 0,
1688 msi.inventory_asset_flag,
1689 msi.outside_operation_flag
1690 from po_lines_all pol
1691 ,mtl_system_items_kfv msi
1692 ,mtl_cross_references mcr
1693 where msi.organization_id = p_organization_id
1694 and ( (mcr.cross_reference_type = p_crossreftype
1695 and mcr.cross_reference like p_concatenated_segments
1696 ) or
1697 ( mcr.cross_reference_type = g_gtin_cross_ref_type
1698 AND mcr.cross_reference LIKE g_crossref )
1699 )
1700 and ( (mcr.org_independent_flag = 'Y') or (mcr.org_independent_flag = 'N'
1701 and mcr.organization_id = p_organization_id
1702 ) )
1703 and mcr.inventory_item_id = msi.inventory_item_id
1704 and pol.item_id = msi.inventory_item_id
1705 and pol.po_header_id = p_poHeaderID
1706 and (purchasing_enabled_flag = 'Y' OR stock_enabled_flag = 'Y')
1707 and msi.inventory_item_id IN
1708 ( SELECT pl.item_id FROM po_lines_all pl WHERE pl.po_header_id =
1709 p_poHeaderID
1710 and exists (select 1 from po_line_locations_all pll where NVL(pll.closed_code,'OPEN')
1711 not in ('CLOSED', 'FINALLY CLOSED', 'CLOSED FOR RECEIVING')
1712 and Nvl(pll.ship_to_organization_id, p_organization_id) = p_organization_id
1713 and pll.po_header_id = p_poHeaderID and pll.po_line_id = pl.po_line_id
1714 AND Nvl(pll.cancel_flag,'N') = 'N'
1715 and pll.receiving_routing_id = 3)
1716 and exists
1717 (select 1 from po_distributions_all pd
1718 where pd.po_header_id = p_poHeaderID
1719 and pd.po_line_id = pol.po_line_id
1720 and ((p_projectId is null or pd.project_id = p_projectId)
1721 and (p_taskId is null or pd.task_id = p_taskId)
1722 )
1723 )
1724 )
1725 ;
1726 end if;
1727 -- End of PJM Based Tran
1728 else
1729
1730 -- *****************************
1731 --- Start of not PJM BASED Tran.
1732 -- *****************************
1733
1734 if (p_poReleaseID is not null) then
1735 -- *****************************
1736 -- Release Based Transaction
1737 -- *****************************
1738 open x_items for
1739 select concatenated_segments,
1740 inventory_item_id,
1741 description,
1742 Nvl(revision_qty_control_code,1),
1743 Nvl(lot_control_code, 1),
1744 Nvl(serial_number_control_code, 1),
1745 Nvl(restrict_subinventories_code, 2),
1746 Nvl(restrict_locators_code, 2),
1747 Nvl(location_control_code, 1),
1748 primary_uom_code,
1749 Nvl(inspection_required_flag, 'N'),
1750 Nvl(shelf_life_code, 1),
1751 Nvl(shelf_life_days,0),
1752 Nvl(allowed_units_lookup_code, 2),
1753 Nvl(effectivity_control,1),
1754 0,
1755 0,
1756 Nvl(default_serial_status_id,1),
1757 Nvl(serial_status_enabled,'N'),
1758 Nvl(default_lot_status_id,0),
1759 Nvl(lot_status_enabled,'N'),
1760 '',
1761 'N',
1762 inventory_item_flag,
1763 0,
1764 inventory_asset_flag,
1765 outside_operation_flag
1766 from mtl_system_items_kfv
1767 WHERE organization_id = p_Organization_Id
1768 and concatenated_segments like p_concatenated_segments
1769 and (purchasing_enabled_flag = 'Y' OR stock_enabled_flag = 'Y')
1770 and inventory_item_id IN (SELECT pol.item_id FROM po_lines_all pol
1771 where pol.po_header_id = p_poHeaderID
1772 and exists (select 1 from po_line_locations_all pll WHERE NVL(pll.closed_code,'OPEN')
1773 not in ('CLOSED','FINALLY CLOSED', 'CLOSED FOR RECEIVING') and
1774 Nvl(pll.ship_to_organization_id, p_organization_id) = p_organization_id and
1775 pll.po_header_id = p_poHeaderID
1776 and pll.po_release_id = p_poReleaseID
1777 and pll.po_line_id = pol.po_line_id
1778 AND Nvl(pll.cancel_flag,'N') = 'N'
1779 and pll.receiving_routing_id = 3)
1780 )
1781 UNION ALL
1782 -- Substitute ITEM SQL
1783 select distinct msi.concatenated_segments,
1784 msi.inventory_item_id,
1785 msi.description,
1786 Nvl(msi.revision_qty_control_code,1),
1787 Nvl(msi.lot_control_code, 1),
1788 Nvl(msi.serial_number_control_code, 1),
1789 Nvl(msi.restrict_subinventories_code, 2),
1790 Nvl(msi.restrict_locators_code,2),
1791 Nvl(msi.location_control_code,1),
1792 msi.primary_uom_code,
1793 Nvl(msi.inspection_required_flag,'N'),
1794 Nvl(msi.shelf_life_code, 1),
1795 Nvl(msi.shelf_life_days,0),
1796 Nvl(msi.allowed_units_lookup_code, 2),
1797 Nvl(msi.effectivity_control,1),
1798 0,
1799 0,
1800 Nvl(msi.default_serial_status_id,1),
1801 Nvl(msi.serial_status_enabled,'N'),
1802 Nvl(msi.default_lot_status_id,0),
1803 Nvl(msi.lot_status_enabled,'N'),
1804 msia.concatenated_segments,
1805 'S',
1806 msi.inventory_item_flag,
1807 0,
1808 msi.inventory_asset_flag,
1809 msi.outside_operation_flag
1810 from po_lines_all pol
1811 ,mtl_related_items mri
1812 ,mtl_system_items_kfv msi
1813 ,mtl_system_items_kfv msia
1814 where msi.organization_id = p_organization_id
1815 and msi.concatenated_segments like p_concatenated_segments
1816 and pol.po_header_id = p_poHeaderID
1817 and pol.item_id = msia.inventory_item_id
1818 and msia.organization_id = p_organization_id
1819 and (( mri.related_item_id = msi.inventory_item_id
1820 and pol.item_id = mri.inventory_item_id) or
1821 ( mri.inventory_item_id = msi.inventory_item_id
1822 and pol.item_id = mri.related_item_id
1823 and mri.reciprocal_flag = 'Y'))
1824 and exists (select 1 from po_line_locations_all pll
1825 where NVL(pll.closed_code,'OPEN')
1826 not in ('CLOSED', 'FINALLY CLOSED', 'CLOSED FOR RECEIVING')
1827 and Nvl(pll.ship_to_organization_id, p_organization_id) = p_organization_id
1828 and Nvl(pll.allow_substitute_receipts_flag, 'N') = 'Y'
1829 and pll.po_header_id = pol.po_header_id
1830 and pll.po_line_id = pol.po_line_id
1831 and pll.po_release_id = p_poReleaseID
1832 AND Nvl(pll.cancel_flag,'N') = 'N'
1833 and pll.receiving_routing_id = 3)
1834 UNION ALL
1835 -- Vendor Item SQL
1836 select distinct pol.vendor_product_num,
1837 msi.inventory_item_id,
1838 msi.description,
1839 Nvl(msi.revision_qty_control_code,1),
1840 Nvl(msi.lot_control_code, 1),
1841 Nvl(msi.serial_number_control_code, 1),
1842 Nvl(msi.restrict_subinventories_code, 2),
1843 Nvl(msi.restrict_locators_code,2),
1844 Nvl(msi.location_control_code,1),
1845 msi.primary_uom_code,
1846 Nvl(msi.inspection_required_flag,'N'),
1847 Nvl(msi.shelf_life_code, 1),
1848 Nvl(msi.shelf_life_days,0),
1849 Nvl(msi.allowed_units_lookup_code, 2),
1850 Nvl(msi.effectivity_control,1),
1851 0,
1852 0,
1853 Nvl(msi.default_serial_status_id,1),
1854 Nvl(msi.serial_status_enabled,'N'),
1855 Nvl(msi.default_lot_status_id,0),
1856 Nvl(msi.lot_status_enabled,'N'),
1857 msi.concatenated_segments,
1858 'Y',
1859 msi.inventory_item_flag,
1860 0,
1861 msi.inventory_asset_flag,
1862 msi.outside_operation_flag
1863 from po_lines_all pol
1864 , mtl_system_items_kfv msi
1865 where organization_id = p_organization_id
1866 and pol.vendor_product_num like p_concatenated_segments
1867 and pol.item_id = msi.inventory_item_id
1868 and pol.vendor_product_num IS NOT NULL
1869 and pol.po_header_id = p_poHeaderID
1870 and (purchasing_enabled_flag = 'Y' OR stock_enabled_flag = 'Y')
1871 and inventory_item_id IN (SELECT pol.item_id FROM po_lines_all pol
1872 where pol.po_header_id = p_poHeaderID
1873 and exists (select 1 from po_line_locations_all pll WHERE NVL(pll.closed_code,'OPEN')
1874 not in ('CLOSED', 'FINALLY CLOSED', 'CLOSED FOR RECEIVING') and
1875 pll.po_header_id = p_poHeaderID
1876 and Nvl(pll.ship_to_organization_id, p_organization_id) = p_organization_id
1877 and pll.po_release_id = p_poReleaseID
1878 and pll.po_line_id = pol.po_line_id
1879 AND Nvl(pll.cancel_flag,'N') = 'N'
1880 and pll.receiving_routing_id = 3)
1881 )
1882 UNION ALL
1883 -- non item Master
1884 select distinct pol.item_description,
1885 to_number(''),
1886 pol.item_description,
1887 1,
1888 1,
1889 1,
1890 2,
1891 2,
1892 1,
1893 mum.uom_code,
1894 'N',
1895 1,
1896 0,
1897 2,
1898 1,
1899 0,
1900 0,
1901 1,
1902 'N',
1903 0,
1904 'N',
1905 '',
1906 'N',
1907 'N',
1908 0,
1909 to_char(NULL),
1910 'N'
1911 from po_lines_all pol
1912 , mtl_units_of_measure mum
1913 -- Bug 2619063, 2614016
1914 -- Modified to select the base uom for the uom class defined on po.
1915 where mum.uom_class = (SELECT mum2.uom_class
1916 FROM mtl_units_of_measure mum2
1917 WHERE mum2.unit_of_measure(+) = pol.unit_meas_lookup_code)
1918 and mum.base_uom_flag = 'Y'
1919 and pol.ITEM_ID is null
1920 and pol.item_description is not null
1921 and pol.po_header_id = p_poHeaderID
1922 and pol.item_description like p_concatenated_segments
1923 UNION ALL
1924 -- Cross Ref SQL
1925 select distinct msi.concatenated_segments,
1926 ---select distinct mcr.cross_reference,
1927 msi.inventory_item_id,
1928 msi.description,
1929 Nvl(msi.revision_qty_control_code,1),
1930 Nvl(msi.lot_control_code, 1),
1931 Nvl(msi.serial_number_control_code, 1),
1932 Nvl(msi.restrict_subinventories_code, 2),
1933 Nvl(msi.restrict_locators_code,2),
1934 Nvl(msi.location_control_code,1),
1935 msi.primary_uom_code,
1936 Nvl(msi.inspection_required_flag,'N'),
1937 Nvl(msi.shelf_life_code, 1),
1938 Nvl(msi.shelf_life_days,0),
1939 Nvl(msi.allowed_units_lookup_code, 2),
1940 Nvl(msi.effectivity_control,1),
1941 0,
1942 0,
1943 Nvl(msi.default_serial_status_id,1),
1944 Nvl(msi.serial_status_enabled,'N'),
1945 Nvl(msi.default_lot_status_id,0),
1946 Nvl(msi.lot_status_enabled,'N'),
1947 --- msi.concatenated_segments,
1948 mcr.cross_reference,
1949 'C',
1950 msi.inventory_item_flag,
1951 0,
1952 msi.inventory_asset_flag,
1953 msi.outside_operation_flag
1954 from po_lines_all pol
1955 ,mtl_system_items_kfv msi
1956 ,mtl_cross_references mcr
1957 where msi.organization_id = p_organization_id
1958 and ( (mcr.cross_reference_type = p_crossreftype
1959 and mcr.cross_reference like p_concatenated_segments
1960 ) or
1961 ( mcr.cross_reference_type = g_gtin_cross_ref_type
1962 AND mcr.cross_reference LIKE g_crossref )
1963 )
1964 and ( (mcr.org_independent_flag = 'Y') or (mcr.org_independent_flag = 'N'
1965 and mcr.organization_id = p_organization_id
1966 ) )
1967 and mcr.inventory_item_id = msi.inventory_item_id
1968 and pol.item_id = msi.inventory_item_id
1969 and pol.po_header_id = p_poHeaderID
1970 and (purchasing_enabled_flag = 'Y' OR stock_enabled_flag = 'Y')
1971 and msi.inventory_item_id IN (SELECT pol.item_id FROM po_lines_all pol
1972 where pol.po_header_id = p_poHeaderID
1973 and exists (select 1 from po_line_locations_all pll WHERE NVL(pll.closed_code,'OPEN')
1974 not in ('CLOSED', 'FINALLY CLOSED', 'CLOSED FOR RECEIVING') and
1975 pll.po_header_id = p_poHeaderID
1976 and Nvl(pll.ship_to_organization_id, p_organization_id) = p_organization_id
1977 and pll.po_release_id = p_poReleaseID
1978 and pll.po_line_id = pol.po_line_id
1979 AND Nvl(pll.cancel_flag,'N') = 'N'
1980 and pll.receiving_routing_id = 3)
1981 )
1982 ;
1983 elsif (p_poLineID IS NOT NULL) then
1984 -- *****************************
1985 -- Deafult Line Based Tran
1986 --- ***************************
1987 open x_items for
1988 select concatenated_segments,
1989 inventory_item_id,
1990 description,
1991 Nvl(revision_qty_control_code,1),
1992 Nvl(lot_control_code, 1),
1993 Nvl(serial_number_control_code, 1),
1994 Nvl(restrict_subinventories_code, 2),
1995 Nvl(restrict_locators_code, 2),
1996 Nvl(location_control_code, 1),
1997 primary_uom_code,
1998 Nvl(inspection_required_flag, 'N'),
1999 Nvl(shelf_life_code, 1),
2000 Nvl(shelf_life_days,0),
2001 Nvl(allowed_units_lookup_code, 2),
2002 Nvl(effectivity_control,1),
2003 0,
2004 0,
2005 Nvl(default_serial_status_id,1),
2006 Nvl(serial_status_enabled,'N'),
2007 Nvl(default_lot_status_id,0),
2008 Nvl(lot_status_enabled,'N'),
2009 '',
2010 'N',
2011 inventory_item_flag,
2012 0,
2013 inventory_asset_flag,
2014 outside_operation_flag
2015 from mtl_system_items_kfv
2016 WHERE organization_id = p_Organization_Id
2017 and concatenated_segments like p_concatenated_segments
2018 and (purchasing_enabled_flag = 'Y' OR stock_enabled_flag = 'Y')
2019 and inventory_item_id IN (SELECT pl.item_id FROM po_lines_all pl WHERE
2020 pl.po_header_id = p_poHeaderID
2021 and pl.po_line_id = p_poLineID
2022 and exists (select 1 from po_line_locations_all pll where NVL(pll.closed_code,
2023 'OPEN')
2024 not in ('CLOSED','FINALLY CLOSED' , 'CLOSED FOR RECEIVING' )
2025 and pll.po_header_id = p_poHeaderID
2026 and Nvl(pll.ship_to_organization_id, p_organization_id) = p_organization_id
2027 and pll.po_line_id = p_poLineID
2028 AND Nvl(pll.cancel_flag,'N') = 'N'
2029 and pll.receiving_routing_id = 3
2030 ))
2031 UNION ALL
2032 -- Substitute Item SQL
2033 select distinct msi.concatenated_segments,
2034 msi.inventory_item_id,
2035 msi.description,
2036 Nvl(msi.revision_qty_control_code,1),
2037 Nvl(msi.lot_control_code, 1),
2038 Nvl(msi.serial_number_control_code, 1),
2039 Nvl(msi.restrict_subinventories_code, 2),
2040 Nvl(msi.restrict_locators_code,2),
2041 Nvl(msi.location_control_code,1),
2042 msi.primary_uom_code,
2043 Nvl(msi.inspection_required_flag,'N'),
2044 Nvl(msi.shelf_life_code, 1),
2045 Nvl(msi.shelf_life_days,0),
2046 Nvl(msi.allowed_units_lookup_code, 2),
2047 Nvl(msi.effectivity_control,1),
2048 0,
2049 0,
2050 Nvl(msi.default_serial_status_id,1),
2051 Nvl(msi.serial_status_enabled,'N'),
2052 Nvl(msi.default_lot_status_id,0),
2053 Nvl(msi.lot_status_enabled,'N'),
2054 msia.concatenated_segments,
2055 'S',
2056 msi.inventory_item_flag,
2057 0,
2058 msi.inventory_asset_flag,
2059 msi.outside_operation_flag
2060 from po_lines_all pol
2061 ,mtl_related_items mri
2062 ,mtl_system_items_kfv msi
2063 ,mtl_system_items_kfv msia
2064 where msi.organization_id = p_organization_id
2065 and msi.concatenated_segments like p_concatenated_segments
2066 and pol.po_header_id = p_poHeaderID
2067 and pol.item_id = msia.inventory_item_id
2068 and msia.organization_id = p_organization_id
2069 and (( mri.related_item_id = msi.inventory_item_id
2070 and pol.item_id = mri.inventory_item_id) or
2071 ( mri.inventory_item_id = msi.inventory_item_id
2072 and pol.item_id = mri.related_item_id
2073 and mri.reciprocal_flag = 'Y'))
2074 and pol.po_line_id = p_poLineID
2075 and exists (select 1 from po_line_locations_all pll
2076 where NVL(pll.closed_code,'OPEN') not in
2077 ('CLOSED', 'FINALLY CLOSED', 'CLOSED FOR RECEIVING')
2078 and Nvl(pll.allow_substitute_receipts_flag, 'N') = 'Y'
2079 and pll.po_header_id = pol.po_header_id
2080 and Nvl(pll.ship_to_organization_id, p_organization_id) = p_organization_id
2081 and pll.po_line_id = pol.po_line_id
2082 and pll.receiving_routing_id = 3)
2083 UNION ALL
2084 -- Vendor Item SQL
2085 select distinct pol.vendor_product_num,
2086 msi.inventory_item_id,
2087 msi.description,
2088 Nvl(msi.revision_qty_control_code,1),
2089 Nvl(msi.lot_control_code, 1),
2090 Nvl(msi.serial_number_control_code, 1),
2091 Nvl(msi.restrict_subinventories_code, 2),
2092 Nvl(msi.restrict_locators_code,2),
2093 Nvl(msi.location_control_code,1),
2094 msi.primary_uom_code,
2095 Nvl(msi.inspection_required_flag,'N'),
2096 Nvl(msi.shelf_life_code, 1),
2097 Nvl(msi.shelf_life_days,0),
2098 Nvl(msi.allowed_units_lookup_code, 2),
2099 Nvl(msi.effectivity_control,1),
2100 0,
2101 0,
2102 Nvl(msi.default_serial_status_id,1),
2103 Nvl(msi.serial_status_enabled,'N'),
2104 Nvl(msi.default_lot_status_id,0),
2105 Nvl(msi.lot_status_enabled,'N'),
2106 msi.concatenated_segments,
2107 'Y',
2108 msi.inventory_item_flag,
2109 0,
2110 msi.inventory_asset_flag,
2111 msi.outside_operation_flag
2112 from po_lines_all pol
2113 ,mtl_system_items_kfv msi
2114 where organization_id = p_organization_id
2115 and pol.vendor_product_num like p_concatenated_segments
2116 and pol.item_id = msi.inventory_item_id
2117 and pol.vendor_product_num IS NOT NULL
2118 and pol.po_header_id = p_poHeaderID
2119 and (purchasing_enabled_flag = 'Y' OR stock_enabled_flag = 'Y')
2120 and inventory_item_id IN (SELECT pl.item_id FROM po_lines_all pl WHERE
2121 pl.po_header_id = p_poHeaderID
2122 and pl.po_line_id = p_poLineID
2123 and exists (select 1 from po_line_locations_all pll where NVL(pll.closed_code,
2124 'OPEN')
2125 not in ('CLOSED', 'FINALLY CLOSED' , 'CLOSED FOR RECEIVING' )
2126 and pll.po_header_id = p_poHeaderID
2127 and Nvl(pll.ship_to_organization_id, p_organization_id) = p_organization_id
2128 and pll.po_line_id = p_poLineID
2129 AND Nvl(pll.cancel_flag,'N') = 'N'
2130 and pll.receiving_routing_id = 3
2131 ))
2132 UNION ALL
2133 -- non item Master
2134 select distinct pol.item_description,
2135 to_number(''),
2136 pol.item_description,
2137 1,
2138 1,
2139 1,
2140 2,
2141 2,
2142 1,
2143 mum.uom_code,
2144 'N',
2145 1,
2146 0,
2147 2,
2148 1,
2149 0,
2150 0,
2151 1,
2152 'N',
2153 0,
2154 'N',
2155 '',
2156 'N',
2157 'N',
2158 0,
2159 to_char(NULL),
2160 'N'
2161 from po_lines_all pol
2162 , mtl_units_of_measure mum
2163 -- Bug 2619063, 2614016
2164 -- Modified to select the base uom for the uom class defined on po.
2165 where mum.uom_class = (SELECT mum2.uom_class
2166 FROM mtl_units_of_measure mum2
2167 WHERE mum2.unit_of_measure(+) = pol.unit_meas_lookup_code)
2168 and mum.base_uom_flag = 'Y'
2169 and pol.ITEM_ID is null
2170 and pol.item_description is not null
2171 and pol.po_header_id = p_poHeaderID
2172 and pol.item_description like p_concatenated_segments
2173 UNION ALL
2174 -- Cross Ref SQL
2175 ---select distinct mcr.cross_reference,
2176 select distinct msi.concatenated_segments,
2177 msi.inventory_item_id,
2178 msi.description,
2179 Nvl(msi.revision_qty_control_code,1),
2180 Nvl(msi.lot_control_code, 1),
2181 Nvl(msi.serial_number_control_code, 1),
2182 Nvl(msi.restrict_subinventories_code, 2),
2183 Nvl(msi.restrict_locators_code,2),
2184 Nvl(msi.location_control_code,1),
2185 msi.primary_uom_code,
2186 Nvl(msi.inspection_required_flag,'N'),
2187 Nvl(msi.shelf_life_code, 1),
2188 Nvl(msi.shelf_life_days,0),
2189 Nvl(msi.allowed_units_lookup_code, 2),
2190 Nvl(msi.effectivity_control,1),
2191 0,
2192 0,
2193 Nvl(msi.default_serial_status_id,1),
2194 Nvl(msi.serial_status_enabled,'N'),
2195 Nvl(msi.default_lot_status_id,0),
2196 Nvl(msi.lot_status_enabled,'N'),
2197 ---msi.concatenated_segments,
2198 mcr.cross_reference,
2199 'C',
2200 msi.inventory_item_flag,
2201 0,
2202 msi.inventory_asset_flag,
2203 msi.outside_operation_flag
2204 from po_lines_all pol
2205 ,mtl_system_items_kfv msi
2206 ,mtl_cross_references mcr
2207 where msi.organization_id = p_organization_id
2208 and ( (mcr.cross_reference_type = p_crossreftype
2209 and mcr.cross_reference like p_concatenated_segments
2210 ) or
2211 ( mcr.cross_reference_type = g_gtin_cross_ref_type
2212 AND mcr.cross_reference LIKE g_crossref )
2213 )
2214 and ( (mcr.org_independent_flag = 'Y') or (mcr.org_independent_flag = 'N'
2215 and mcr.organization_id = p_organization_id
2216 ) )
2217 and mcr.inventory_item_id = msi.inventory_item_id
2218 and pol.item_id = msi.inventory_item_id
2219 and pol.po_header_id = p_poHeaderID
2220 and (purchasing_enabled_flag = 'Y' OR stock_enabled_flag = 'Y')
2221 and msi.inventory_item_id IN (SELECT pl.item_id FROM po_lines_all pl WHERE
2222 pl.po_header_id = p_poHeaderID
2223 and pl.po_line_id = p_poLineID
2224 and exists (select 1 from po_line_locations_all pll where NVL(pll.closed_code,
2225 'OPEN')
2226 not in ('CLOSED', 'FINALLY CLOSED' , 'CLOSED FOR RECEIVING' )
2227 and pll.po_header_id = p_poHeaderID
2228 and Nvl(pll.ship_to_organization_id, p_organization_id) = p_organization_id
2229 and pll.po_line_id = p_poLineID
2230 AND Nvl(pll.cancel_flag,'N') = 'N'
2231 and pll.receiving_routing_id = 3
2232 ))
2233 ;
2234 else
2235 -- *****************************
2236 -- Deafult headerBased Tran
2237 -- ***************************
2238 open x_Items for
2239 select concatenated_segments,
2240 inventory_item_id,
2241 description,
2242 Nvl(revision_qty_control_code,1),
2243 Nvl(lot_control_code, 1),
2244 Nvl(serial_number_control_code, 1),
2245 Nvl(restrict_subinventories_code, 2),
2246 Nvl(restrict_locators_code, 2),
2247 Nvl(location_control_code, 1),
2248 primary_uom_code,
2249 Nvl(inspection_required_flag, 'N'),
2250 Nvl(shelf_life_code, 1),
2251 Nvl(shelf_life_days,0),
2252 Nvl(allowed_units_lookup_code, 2),
2253 Nvl(effectivity_control,1),
2254 0,
2255 0,
2256 Nvl(default_serial_status_id,1),
2257 Nvl(serial_status_enabled,'N'),
2258 Nvl(default_lot_status_id,0),
2259 Nvl(lot_status_enabled,'N'),
2260 '',
2261 'N',
2262 inventory_item_flag,
2263 0,
2264 inventory_asset_flag,
2265 outside_operation_flag
2266 from mtl_system_items_kfv
2267 WHERE organization_id = p_Organization_Id
2268 and concatenated_segments like p_concatenated_segments
2269 and (purchasing_enabled_flag = 'Y' OR stock_enabled_flag = 'Y')
2270 and inventory_item_id IN
2271 ( SELECT pl.item_id FROM po_lines_all pl WHERE pl.po_header_id = p_poHeaderID
2272 and exists (select 1 from po_line_locations_all pll where NVL(pll.closed_code,'OPEN')
2273 not in ('CLOSED', 'FINALLY CLOSED', 'CLOSED FOR RECEIVING')
2274 and pll.po_header_id = p_poHeaderID
2275 and Nvl(pll.ship_to_organization_id, p_organization_id) = p_organization_id
2276 and pll.po_line_id = pl.po_line_id
2277 AND Nvl(pll.cancel_flag,'N') = 'N'
2278 and pll.receiving_routing_id = 3)
2279 )
2280 UNION ALL
2281 -- Substitute Item SQL
2282 select distinct msi.concatenated_segments,
2283 msi.inventory_item_id,
2284 msi.description,
2285 Nvl(msi.revision_qty_control_code,1),
2286 Nvl(msi.lot_control_code, 1),
2287 Nvl(msi.serial_number_control_code, 1),
2288 Nvl(msi.restrict_subinventories_code, 2),
2289 Nvl(msi.restrict_locators_code,2),
2290 Nvl(msi.location_control_code,1),
2291 msi.primary_uom_code,
2292 Nvl(msi.inspection_required_flag,'N'),
2293 Nvl(msi.shelf_life_code, 1),
2294 Nvl(msi.shelf_life_days,0),
2295 Nvl(msi.allowed_units_lookup_code, 2),
2296 Nvl(msi.effectivity_control,1),
2297 0,
2298 0,
2299 Nvl(msi.default_serial_status_id,1),
2300 Nvl(msi.serial_status_enabled,'N'),
2301 Nvl(msi.default_lot_status_id,0),
2302 Nvl(msi.lot_status_enabled,'N'),
2303 msia.concatenated_segments,
2304 'S',
2305 msi.inventory_item_flag,
2306 0,
2307 msi.inventory_asset_flag,
2308 msi.outside_operation_flag
2309 from po_lines_all pol
2310 ,mtl_related_items mri
2311 ,mtl_system_items_kfv msi
2312 ,mtl_system_items_kfv msia
2313 where msi.organization_id = p_organization_id
2314 and msi.concatenated_segments like p_concatenated_segments
2315 and pol.po_header_id = p_poHeaderID
2316 and pol.item_id = msia.inventory_item_id
2317 and msia.organization_id = p_organization_id
2318 and (( mri.related_item_id = msi.inventory_item_id
2319 and pol.item_id = mri.inventory_item_id) or
2320 ( mri.inventory_item_id = msi.inventory_item_id
2321 and pol.item_id = mri.related_item_id
2322 and mri.reciprocal_flag = 'Y'))
2323 and exists (select 1 from po_line_locations_all pll
2324 where NVL(pll.closed_code,'OPEN') not in ('CLOSED','FINALLY CLOSED',
2325 'CLOSED FOR RECEIVING')
2326 and Nvl(pll.allow_substitute_receipts_flag, 'N') = 'Y'
2327 and pll.po_header_id = pol.po_header_id
2328 and Nvl(pll.ship_to_organization_id, p_organization_id) = p_organization_id
2329 and pll.po_line_id = pol.po_line_id
2330 AND Nvl(pll.cancel_flag,'N') = 'N'
2331 and pll.receiving_routing_id = 3)
2332 UNION ALL
2333 -- Vendor Item SQL
2334 select distinct pol.vendor_product_num,
2335 msi.inventory_item_id,
2336 msi.description,
2337 Nvl(msi.revision_qty_control_code,1),
2338 Nvl(msi.lot_control_code, 1),
2339 Nvl(msi.serial_number_control_code, 1),
2340 Nvl(msi.restrict_subinventories_code, 2),
2341 Nvl(msi.restrict_locators_code,2),
2342 Nvl(msi.location_control_code,1),
2343 msi.primary_uom_code,
2344 Nvl(msi.inspection_required_flag,'N'),
2345 Nvl(msi.shelf_life_code, 1),
2346 Nvl(msi.shelf_life_days,0),
2347 Nvl(msi.allowed_units_lookup_code, 2),
2348 Nvl(msi.effectivity_control,1),
2349 0,
2350 0,
2351 Nvl(msi.default_serial_status_id,1),
2352 Nvl(msi.serial_status_enabled,'N'),
2353 Nvl(msi.default_lot_status_id,0),
2354 Nvl(msi.lot_status_enabled,'N'),
2355 msi.concatenated_segments,
2356 'Y',
2357 msi.inventory_item_flag,
2358 0,
2359 msi.inventory_asset_flag,
2360 msi.outside_operation_flag
2361 from po_lines_all pol
2362 , mtl_system_items_kfv msi
2363 where organization_id = p_organization_id
2364 and pol.vendor_product_num like p_concatenated_segments
2365 and pol.item_id = msi.inventory_item_id
2366 and pol.vendor_product_num IS NOT NULL
2367 and pol.po_header_id = p_poHeaderID
2368 and (purchasing_enabled_flag = 'Y' OR stock_enabled_flag = 'Y')
2369 and inventory_item_id IN
2370 ( SELECT pl.item_id FROM po_lines_all pl WHERE pl.po_header_id =
2371 p_poHeaderID
2372 and exists (select 1 from po_line_locations_all pll where
2373 NVL(pll.closed_code,'OPEN')
2374 not in ('CLOSED', 'FINALLY CLOSED', 'CLOSED FOR RECEIVING')
2375 and pll.po_header_id = p_poHeaderID
2376 and Nvl(pll.ship_to_organization_id, p_organization_id) = p_organization_id
2377 and pll.po_line_id = pl.po_line_id
2378 AND Nvl(pll.cancel_flag,'N') = 'N'
2379 and pll.receiving_routing_id = 3)
2380 )
2381 UNION ALL
2382 -- non item Master
2383 select distinct pol.item_description,
2384 to_number(''),
2385 pol.item_description,
2386 1,
2387 1,
2388 1,
2389 2,
2390 2,
2391 1,
2392 mum.uom_code,
2393 'N',
2394 1,
2395 0,
2396 2,
2397 1,
2398 0,
2399 0,
2400 1,
2401 'N',
2402 0,
2403 'N',
2404 '',
2405 'N',
2406 'N',
2407 0,
2408 to_char(NULL),
2409 'N'
2410 from po_lines_all pol
2411 , mtl_units_of_measure mum
2412 -- Bug 2619063, 2614016
2413 -- Modified to select the base uom for the uom class defined on po.
2414 where mum.uom_class = (SELECT mum2.uom_class
2415 FROM mtl_units_of_measure mum2
2416 WHERE mum2.unit_of_measure(+) = pol.unit_meas_lookup_code)
2417 and mum.base_uom_flag = 'Y'
2418 and pol.ITEM_ID is null
2419 and pol.item_description is not null
2420 and pol.po_header_id = p_poHeaderID
2421 and pol.item_description like p_concatenated_segments
2422 UNION ALL
2423 -- Cross Ref SQL
2424 ---select distinct mcr.cross_reference,
2425 select distinct msi.concatenated_segments,
2426 msi.inventory_item_id,
2427 msi.description,
2428 Nvl(msi.revision_qty_control_code,1),
2429 Nvl(msi.lot_control_code, 1),
2430 Nvl(msi.serial_number_control_code, 1),
2431 Nvl(msi.restrict_subinventories_code, 2),
2432 Nvl(msi.restrict_locators_code,2),
2433 Nvl(msi.location_control_code,1),
2434 msi.primary_uom_code,
2435 Nvl(msi.inspection_required_flag,'N'),
2436 Nvl(msi.shelf_life_code, 1),
2437 Nvl(msi.shelf_life_days,0),
2438 Nvl(msi.allowed_units_lookup_code, 2),
2439 Nvl(msi.effectivity_control,1),
2440 0,
2441 0,
2442 Nvl(msi.default_serial_status_id,1),
2443 Nvl(msi.serial_status_enabled,'N'),
2444 Nvl(msi.default_lot_status_id,0),
2445 Nvl(msi.lot_status_enabled,'N'),
2446 --- msi.concatenated_segments,
2447 mcr.cross_reference,
2448 'C',
2449 msi.inventory_item_flag,
2450 0,
2451 msi.inventory_asset_flag,
2452 msi.outside_operation_flag
2453 from po_lines_all pol
2454 ,mtl_system_items_kfv msi
2455 ,mtl_cross_references mcr
2456 where msi.organization_id = p_organization_id
2457 and ( (mcr.cross_reference_type = p_crossreftype
2458 and mcr.cross_reference like p_concatenated_segments
2459 ) or
2460 ( mcr.cross_reference_type = g_gtin_cross_ref_type
2461 AND mcr.cross_reference LIKE g_crossref )
2462 )
2463 and ( (mcr.org_independent_flag = 'Y') or (mcr.org_independent_flag = 'N'
2464 and mcr.organization_id = p_organization_id
2465 ) )
2466 and mcr.inventory_item_id = msi.inventory_item_id
2467 and pol.item_id = msi.inventory_item_id
2468 and pol.po_header_id = p_poHeaderID
2469 and (purchasing_enabled_flag = 'Y' OR stock_enabled_flag = 'Y')
2470 and msi.inventory_item_id IN
2471 ( SELECT pl.item_id FROM po_lines_all pl WHERE pl.po_header_id = p_poHeaderID
2472 and exists (select 1 from po_line_locations_all pll where NVL(pll.closed_code,'OPEN')
2473 not in ('CLOSED', 'FINALLY CLOSED', 'CLOSED FOR RECEIVING')
2474 and pll.po_header_id = p_poHeaderID
2475 and Nvl(pll.ship_to_organization_id, p_organization_id) = p_organization_id
2476 and pll.po_line_id = pl.po_line_id
2477 AND Nvl(pll.cancel_flag,'N') = 'N'
2478 and pll.receiving_routing_id = 3)
2479 )
2480 ;
2481
2482 end if;
2483
2484 -- *****************************
2485 -- End of not PJM Based Tran
2486 -- *****************************
2487
2488 end if;
2489
2490 -- *****************************
2491 --- END OF PO HEADER ID SECTION
2492 -- *****************************
2493
2494 elsif (p_shipmentHeaderID is not null ) then
2495 -- *****************************
2496 --- START OF SHIPMENT HEADER ID SECTION
2497 -- *****************************
2498 open x_Items for
2499 select concatenated_segments,
2500 inventory_item_id,
2501 description,
2502 Nvl(revision_qty_control_code,1),
2503 Nvl(lot_control_code, 1),
2504 Nvl(serial_number_control_code, 1),
2505 Nvl(restrict_subinventories_code, 2),
2506 Nvl(restrict_locators_code, 2),
2507 Nvl(location_control_code, 1),
2508 primary_uom_code,
2509 Nvl(inspection_required_flag, 'N'),
2510 Nvl(shelf_life_code, 1),
2511 Nvl(shelf_life_days,0),
2512 Nvl(allowed_units_lookup_code, 2),
2513 Nvl(effectivity_control,1),
2514 0,
2515 0,
2516 Nvl(default_serial_status_id,1),
2517 Nvl(serial_status_enabled,'N'),
2518 Nvl(default_lot_status_id,0),
2519 Nvl(lot_status_enabled,'N'),
2520 '',
2521 'N',
2522 inventory_item_flag,
2523 0,
2524 inventory_asset_flag,
2525 outside_operation_flag
2526 from mtl_system_items_kfv msn,
2527 rcv_shipment_lines rsl
2528 WHERE msn.organization_id = p_Organization_Id
2529 and msn.concatenated_segments like p_concatenated_segments
2530 and (msn.purchasing_enabled_flag = 'Y' OR msn.stock_enabled_flag = 'Y')
2531 and rsl.SHIPMENT_HEADER_ID = p_shipmentHeaderID
2532 -- This was fix for bug 2740648/2752094
2533 AND rsl.shipment_line_status_code in ('EXPECTED','PARTIALLY RECEIVED')
2534 and rsl.item_id = msn.inventory_item_id
2535 UNION
2536 -- bug 2775596
2537 -- added unions for the substitute item and vendor item
2538 -- if receiving an ASN.
2539 -- Vendor Item SQL
2540 select distinct pol.vendor_product_num,
2541 msi.inventory_item_id,
2542 msi.description,
2543 Nvl(msi.revision_qty_control_code,1),
2544 Nvl(msi.lot_control_code, 1),
2545 Nvl(msi.serial_number_control_code, 1),
2546 Nvl(msi.restrict_subinventories_code, 2),
2547 Nvl(msi.restrict_locators_code,2),
2548 Nvl(msi.location_control_code,1),
2549 msi.primary_uom_code,
2550 Nvl(msi.inspection_required_flag,'N'),
2551 Nvl(msi.shelf_life_code, 1),
2552 Nvl(msi.shelf_life_days,0),
2553 Nvl(msi.allowed_units_lookup_code, 2),
2554 Nvl(msi.effectivity_control,1),
2555 0,
2556 0,
2557 Nvl(msi.default_serial_status_id,1),
2558 Nvl(msi.serial_status_enabled,'N'),
2559 Nvl(msi.default_lot_status_id,0),
2560 Nvl(msi.lot_status_enabled,'N'),
2561 msi.concatenated_segments,
2562 'Y',
2563 msi.inventory_item_flag,
2564 0,
2565 msi.inventory_asset_flag,
2566 msi.outside_operation_flag
2567 from po_lines_all pol
2568 ,mtl_system_items_kfv msi
2569 , rcv_shipment_lines rsl
2570 where organization_id = p_Organization_Id
2571 and pol.vendor_product_num like p_concatenated_segments
2572 and pol.item_id = msi.inventory_item_id
2573 and pol.vendor_product_num IS NOT NULL
2574 and pol.po_header_id = Nvl(p_poheaderid,pol.po_header_id)
2575 and (purchasing_enabled_flag = 'Y' OR stock_enabled_flag = 'Y')
2576 and inventory_item_id IN (SELECT pl.item_id
2577 FROM po_lines_all pl
2578 WHERE pl.po_header_id = rsl.po_header_id
2579 and pl.po_line_id = rsl.po_line_id
2580 and exists (select 1 from
2581 po_line_locations_all pll
2582 where NVL(pll.closed_code,'OPEN')
2583 not in ('CLOSED', 'FINALLY CLOSED' , 'CLOSED FOR RECEIVING' )
2584 and pll.po_header_id = rsl.po_header_id
2585 and Nvl(pll.ship_to_organization_id, p_organization_id) = p_organization_id
2586 and pll.po_line_id = rsl.po_line_id
2587 AND Nvl(pll.cancel_flag,'N') = 'N'
2588 and pll.receiving_routing_id = 3
2589 ))
2590 AND pol.po_line_id = rsl.po_line_id
2591 and rsl.SHIPMENT_HEADER_ID = p_shipmentHeaderID
2592 AND rsl.shipment_line_status_code in ('EXPECTED','PARTIALLY RECEIVED')
2593 AND rsl.source_document_code = 'PO'
2594 UNION
2595 -- Bug 2775532
2596 -- This section is non item master stuff for ASNs
2597 select distinct pol.item_description,
2598 to_number(''),
2599 pol.item_description,
2600 1,
2601 1,
2602 1,
2603 2,
2604 2,
2605 1,
2606 mum.uom_code,
2607 'N',
2608 1,
2609 0,
2610 2,
2611 1,
2612 0,
2613 0,
2614 1,
2615 'N',
2616 0,
2617 'N',
2618 '',
2619 'N',
2620 'N',
2621 0,
2622 to_char(NULL),
2623 'N'
2624 from po_lines_all pol
2625 , mtl_units_of_measure mum
2626 , rcv_shipment_lines rsl
2627 -- Bug 2619063, 2614016
2628 -- Modified to select the base uom for the uom class defined on po.
2629 where mum.uom_class = (SELECT mum2.uom_class
2630 FROM mtl_units_of_measure mum2
2631 WHERE mum2.unit_of_measure(+) = pol.unit_meas_lookup_code)
2632 and mum.base_uom_flag = 'Y'
2633 and pol.ITEM_ID is null
2634 and pol.item_description is not null
2635 and pol.po_header_id = Nvl(p_poheaderid,pol.po_header_id)
2636 and pol.item_description like p_concatenated_segments
2637 AND pol.po_line_id = rsl.po_line_id
2638 and rsl.SHIPMENT_HEADER_ID = p_shipmentHeaderID
2639 AND rsl.shipment_line_status_code in ('EXPECTED','PARTIALLY RECEIVED')
2640 AND rsl.source_document_code = 'PO'
2641 UNION
2642 -- This Section for GTIN Cross Ref
2643 ---select mcr.cross_reference,
2644 select distinct msn.concatenated_segments,
2645 msn.inventory_item_id,
2646 msn.description,
2647 Nvl(msn.revision_qty_control_code,1),
2648 Nvl(msn.lot_control_code, 1),
2649 Nvl(msn.serial_number_control_code, 1),
2650 Nvl(msn.restrict_subinventories_code, 2),
2651 Nvl(msn.restrict_locators_code, 2),
2652 Nvl(msn.location_control_code, 1),
2653 msn.primary_uom_code,
2654 Nvl(msn.inspection_required_flag, 'N'),
2655 Nvl(msn.shelf_life_code, 1),
2656 Nvl(msn.shelf_life_days,0),
2657 Nvl(msn.allowed_units_lookup_code, 2),
2658 Nvl(msn.effectivity_control,1),
2659 0,
2660 0,
2661 Nvl(msn.default_serial_status_id,1),
2662 Nvl(msn.serial_status_enabled,'N'),
2663 Nvl(msn.default_lot_status_id,0),
2664 Nvl(msn.lot_status_enabled,'N'),
2665 '',
2666 'N',
2667 msn.inventory_item_flag,
2668 0,
2669 msn.inventory_asset_flag,
2670 msn.outside_operation_flag
2671 from mtl_system_items_kfv msn,
2672 rcv_shipment_lines rsl,
2673 mtl_cross_references mcr
2674 WHERE msn.organization_id = p_Organization_Id
2675 and ( mcr.cross_reference_type = g_gtin_cross_ref_type
2676 AND mcr.cross_reference LIKE g_crossref
2677 )
2678 and ( (mcr.org_independent_flag = 'Y') or (mcr.org_independent_flag = 'N'
2679 and mcr.organization_id = p_organization_id
2680 ) )
2681 and mcr.inventory_item_id = msn.inventory_item_id
2682 and (msn.purchasing_enabled_flag = 'Y' OR msn.stock_enabled_flag = 'Y')
2683 and rsl.SHIPMENT_HEADER_ID = p_shipmentHeaderID
2684 and rsl.item_id = msn.inventory_item_id
2685 ;
2686
2687
2688 -- *****************************
2689 --- END OF SHIPMENT HEADER ID SECTION
2690 -- *****************************
2691
2692 elsif (p_oeOrderHeaderID is not null) then
2693
2694 -- *****************************
2695 --- START OF OE ORDER HEADER ID SECTION
2696 -- *****************************
2697
2698 open x_items for
2699 select concatenated_segments,
2700 inventory_item_id,
2701 description,
2702 Nvl(revision_qty_control_code,1),
2703 Nvl(lot_control_code, 1),
2704 Nvl(serial_number_control_code, 1),
2705 Nvl(restrict_subinventories_code, 2),
2706 Nvl(restrict_locators_code, 2),
2707 Nvl(location_control_code, 1),
2708 primary_uom_code,
2709 Nvl(inspection_required_flag, 'N'),
2710 Nvl(shelf_life_code, 1),
2711 Nvl(shelf_life_days,0),
2712 Nvl(allowed_units_lookup_code, 2),
2713 Nvl(effectivity_control,1),
2714 0,
2715 0,
2716 Nvl(default_serial_status_id,1),
2717 Nvl(serial_status_enabled,'N'),
2718 Nvl(default_lot_status_id,0),
2719 Nvl(lot_status_enabled,'N'),
2720 '',
2721 'N',
2722 inventory_item_flag,
2723 0,
2724 inventory_asset_flag,
2725 outside_operation_flag
2726 from mtl_system_items_kfv
2727 WHERE organization_id = p_Organization_Id
2728 and concatenated_segments like p_concatenated_segments
2729 and (purchasing_enabled_flag = 'Y' OR stock_enabled_flag = 'Y')
2730 and inventory_item_id IN (SELECT oel.inventory_item_id FROM
2731 oe_order_lines_all oel WHERE oel.HEADER_ID = p_oeOrderHeaderID
2732 and oel.ORDERED_QUANTITY > NVL(oel.SHIPPED_QUANTITY,0)
2733 and ((p_projectId is null or oel.project_id = p_projectId)
2734 and (p_taskID is null or oel.task_id = p_taskId )) )
2735 UNION
2736 -- This Section Added for GTIN Cross Ref
2737 ---select mcr.cross_reference,
2738 select distinct msi.concatenated_segments,
2739 msi.inventory_item_id,
2740 msi.description,
2741 Nvl(msi.revision_qty_control_code,1),
2742 Nvl(msi.lot_control_code, 1),
2743 Nvl(msi.serial_number_control_code, 1),
2744 Nvl(msi.restrict_subinventories_code, 2),
2745 Nvl(msi.restrict_locators_code, 2),
2746 Nvl(msi.location_control_code, 1),
2747 msi.primary_uom_code,
2748 Nvl(msi.inspection_required_flag, 'N'),
2749 Nvl(msi.shelf_life_code, 1),
2750 Nvl(msi.shelf_life_days,0),
2751 Nvl(msi.allowed_units_lookup_code, 2),
2752 Nvl(msi.effectivity_control,1),
2753 0,
2754 0,
2755 Nvl(msi.default_serial_status_id,1),
2756 Nvl(msi.serial_status_enabled,'N'),
2757 Nvl(msi.default_lot_status_id,0),
2758 Nvl(msi.lot_status_enabled,'N'),
2759 '',
2760 'N',
2761 msi.inventory_item_flag,
2762 0,
2763 msi.inventory_asset_flag,
2764 msi.outside_operation_flag
2765 from mtl_system_items_kfv msi
2766 ,mtl_cross_references mcr
2767 WHERE msi.organization_id = p_Organization_Id
2768 and ( mcr.cross_reference_type = g_gtin_cross_ref_type
2769 AND mcr.cross_reference LIKE g_crossref
2770 )
2771 and ( (mcr.org_independent_flag = 'Y') or (mcr.org_independent_flag = 'N'
2772 and mcr.organization_id = p_organization_id
2773 ) )
2774 and mcr.inventory_item_id = msi.inventory_item_id
2775 and (purchasing_enabled_flag = 'Y' OR stock_enabled_flag = 'Y')
2776 and msi.inventory_item_id IN (SELECT oel.inventory_item_id FROM
2777 oe_order_lines_all oel WHERE oel.HEADER_ID = p_oeOrderHeaderID
2778 and oel.ORDERED_QUANTITY > NVL(oel.SHIPPED_QUANTITY,0)
2779 and ((p_projectId is null or oel.project_id = p_projectId)
2780 and (p_taskID is null or oel.task_id = p_taskId )) ) ;
2781
2782 -- *****************************
2783 --- END OF OE ORDER HEADER ID SECTION
2784 -- *****************************
2785
2786 elsif (p_reqHeaderID is not null) then
2787
2788 -- *****************************
2789 --- START OF REQ HEADER ID SECTION
2790 -- *****************************
2791
2792 open x_items for
2793 select concatenated_segments,
2794 inventory_item_id,
2795 description,
2796 Nvl(revision_qty_control_code,1),
2797 Nvl(lot_control_code, 1),
2798 Nvl(serial_number_control_code, 1),
2799 Nvl(restrict_subinventories_code, 2),
2800 Nvl(restrict_locators_code, 2),
2801 Nvl(location_control_code, 1),
2802 primary_uom_code,
2803 Nvl(inspection_required_flag, 'N'),
2804 Nvl(shelf_life_code, 1),
2805 Nvl(shelf_life_days,0),
2806 Nvl(allowed_units_lookup_code, 2),
2807 Nvl(effectivity_control,1),
2808 0,
2809 0,
2810 Nvl(default_serial_status_id,1),
2811 Nvl(serial_status_enabled,'N'),
2812 Nvl(default_lot_status_id,0),
2813 Nvl(lot_status_enabled,'N'),
2814 '',
2815 'N',
2816 inventory_item_flag,
2817 0,
2818 inventory_asset_flag,
2819 outside_operation_flag
2820 from mtl_system_items_kfv
2821 WHERE organization_id = p_Organization_Id
2822 and concatenated_segments like p_concatenated_segments
2823 and (purchasing_enabled_flag = 'Y' OR stock_enabled_flag = 'Y')
2824 and exists (SELECT 1 FROM po_requisition_lines_all prl,
2825 rcv_shipment_lines rsl , po_req_distributions_all prd
2826 WHERE prl.requisition_header_id = p_reqHeaderID
2827 and rsl.item_id = inventory_item_id
2828 and prl.requisition_line_id = rsl.requisition_line_id
2829 and prl.requisition_line_id = prd.requisition_line_id
2830 and (p_projectId is null or prd.project_id = p_projectId)
2831 and (p_taskId is null or prd.task_id = p_taskId)
2832 )
2833 UNION
2834 -- Section for GTIN Cross Ref.
2835 ---select mcr.cross_reference,
2836 select distinct msi.concatenated_segments,
2837 msi.inventory_item_id,
2838 msi.description,
2839 Nvl(msi.revision_qty_control_code,1),
2840 Nvl(msi.lot_control_code, 1),
2841 Nvl(msi.serial_number_control_code, 1),
2842 Nvl(msi.restrict_subinventories_code, 2),
2843 Nvl(msi.restrict_locators_code, 2),
2844 Nvl(msi.location_control_code, 1),
2845 msi.primary_uom_code,
2846 Nvl(msi.inspection_required_flag, 'N'),
2847 Nvl(msi.shelf_life_code, 1),
2848 Nvl(msi.shelf_life_days,0),
2849 Nvl(msi.allowed_units_lookup_code, 2),
2850 Nvl(msi.effectivity_control,1),
2851 0,
2852 0,
2853 Nvl(msi.default_serial_status_id,1),
2854 Nvl(msi.serial_status_enabled,'N'),
2855 Nvl(msi.default_lot_status_id,0),
2856 Nvl(msi.lot_status_enabled,'N'),
2857 '',
2858 'N',
2859 msi.inventory_item_flag,
2860 0,
2861 msi.inventory_asset_flag,
2862 msi.outside_operation_flag
2863 from mtl_system_items_kfv msi
2864 ,mtl_cross_references mcr
2865 WHERE msi.organization_id = p_Organization_Id
2866 and ( mcr.cross_reference_type = g_gtin_cross_ref_type
2867 AND mcr.cross_reference LIKE g_crossref
2868 )
2869 and ( (mcr.org_independent_flag = 'Y') or (mcr.org_independent_flag = 'N'
2870 and mcr.organization_id = p_organization_id
2871 ) )
2872 and mcr.inventory_item_id = msi.inventory_item_id
2873 and (purchasing_enabled_flag = 'Y' OR stock_enabled_flag = 'Y')
2874 and exists (SELECT 1 FROM po_requisition_lines_all prl,
2875 rcv_shipment_lines rsl , po_req_distributions_all prd
2876 WHERE prl.requisition_header_id = p_reqHeaderID
2877 and rsl.item_id = msi.inventory_item_id
2878 and prl.requisition_line_id = rsl.requisition_line_id
2879 and prl.requisition_line_id = prd.requisition_line_id
2880 and (p_projectId is null or prd.project_id = p_projectId)
2881 and (p_taskId is null or prd.task_id = p_taskId)
2882 ) ;
2883
2884 -- *****************************
2885 --- END OF REQ HEADER ID SECTION
2886 -- *****************************
2887
2888 end if; --- End of doc Entered transaction
2889
2890 else
2891
2892 -- *****************************
2893 ---- Case for Document Info is not entered in the session , i.e transaction starts with Item
2894 -- *****************************
2895 open x_items for
2896 select concatenated_segments,
2897 inventory_item_id,
2898 description,
2899 Nvl(revision_qty_control_code,1),
2900 Nvl(lot_control_code, 1),
2901 Nvl(serial_number_control_code, 1),
2902 Nvl(restrict_subinventories_code, 2),
2903 Nvl(restrict_locators_code, 2),
2904 Nvl(location_control_code, 1),
2905 primary_uom_code,
2906 Nvl(inspection_required_flag, 'N'),
2907 Nvl(shelf_life_code, 1),
2908 Nvl(shelf_life_days,0),
2909 Nvl(allowed_units_lookup_code, 2),
2910 Nvl(effectivity_control,1),
2911 0,
2912 0,
2913 Nvl(default_serial_status_id,1),
2914 Nvl(serial_status_enabled,'N'),
2915 Nvl(default_lot_status_id,0),
2916 Nvl(lot_status_enabled,'N'),
2917 '',
2918 'N',
2919 inventory_item_flag,
2920 0,
2921 inventory_asset_flag,
2922 outside_operation_flag
2923 from mtl_system_items_kfv
2924 WHERE organization_id = p_Organization_Id
2925 and concatenated_segments like p_concatenated_segments
2926 and (purchasing_enabled_flag = 'Y' OR stock_enabled_flag = 'Y')
2927 UNION
2928 --- Substitute Item SQL
2929 select distinct msi.concatenated_segments,
2930 msi.inventory_item_id,
2931 msi.description,
2932 Nvl(msi.revision_qty_control_code,1),
2933 Nvl(msi.lot_control_code, 1),
2934 Nvl(msi.serial_number_control_code, 1),
2935 Nvl(msi.restrict_subinventories_code, 2),
2936 Nvl(msi.restrict_locators_code,2),
2937 Nvl(msi.location_control_code,1),
2938 msi.primary_uom_code,
2939 Nvl(msi.inspection_required_flag,'N'),
2940 Nvl(msi.shelf_life_code, 1),
2941 Nvl(msi.shelf_life_days,0),
2942 Nvl(msi.allowed_units_lookup_code, 2),
2943 Nvl(msi.effectivity_control,1),
2944 0,
2945 0,
2946 Nvl(msi.default_serial_status_id,1),
2947 Nvl(msi.serial_status_enabled,'N'),
2948 Nvl(msi.default_lot_status_id,0),
2949 Nvl(msi.lot_status_enabled,'N'),
2950 '',
2951 'N',
2952 msi.inventory_item_flag,
2953 0,
2954 msi.inventory_asset_flag,
2955 msi.outside_operation_flag
2956 from po_lines_all pol
2957 ,mtl_related_items mri
2958 ,mtl_system_items_kfv msi
2959 ,mtl_system_items_kfv msia
2960 where msi.organization_id = p_organization_id
2961 and msi.concatenated_segments like p_concatenated_segments
2962 and pol.item_id = msia.inventory_item_id
2963 and msia.organization_id = p_organization_id
2964 and ((mri.related_item_id = msi.inventory_item_id
2965 and pol.item_id = mri.inventory_item_id) or
2966 (mri.inventory_item_id = msi.inventory_item_id
2967 and pol.item_id = mri.related_item_id
2968 and mri.reciprocal_flag = 'Y'))
2969 and exists ( select 1 from po_line_locations_all pll
2970 where
2971 -- pll.closed_code = 'OPEN' -- Bug 2859355
2972 Nvl(pll.closed_code,'OPEN') NOT IN ('CLOSED','FINALLY CLOSED','CLOSED FOR RECEIVING')
2973 and Nvl(pll.allow_substitute_receipts_flag, 'N') = 'Y'
2974 and pll.po_header_id = pol.po_header_id
2975 and Nvl(pll.ship_to_organization_id, p_organization_id) = p_organization_id
2976 and pll.po_line_id = pol.po_line_id
2977 AND Nvl(pll.cancel_flag,'N') = 'N'
2978 and pll.receiving_routing_id = 3)
2979 UNION ALL
2980 ---- Vendor Item SQL
2981 select distinct pol.vendor_product_num,
2982 msi.inventory_item_id,
2983 msi.description,
2984 Nvl(msi.revision_qty_control_code,1),
2985 Nvl(msi.lot_control_code, 1),
2986 Nvl(msi.serial_number_control_code, 1),
2987 Nvl(msi.restrict_subinventories_code, 2),
2988 Nvl(msi.restrict_locators_code,2),
2989 Nvl(msi.location_control_code,1),
2990 msi.primary_uom_code,
2991 Nvl(msi.inspection_required_flag,'N'),
2992 Nvl(msi.shelf_life_code, 1),
2993 Nvl(msi.shelf_life_days,0),
2994 Nvl(msi.allowed_units_lookup_code, 2),
2995 Nvl(msi.effectivity_control,1),
2996 0,
2997 0,
2998 Nvl(msi.default_serial_status_id,1),
2999 Nvl(msi.serial_status_enabled,'N'),
3000 Nvl(msi.default_lot_status_id,0),
3001 Nvl(msi.lot_status_enabled,'N'),
3002 msi.concatenated_segments,
3003 'Y',
3004 msi.inventory_item_flag,
3005 0,
3006 msi.inventory_asset_flag,
3007 msi.outside_operation_flag
3008 from po_lines_all pol
3009 ,mtl_system_items_kfv msi
3010 where organization_id = p_organization_id
3011 and (purchasing_enabled_flag = 'Y' OR stock_enabled_flag = 'Y')
3012 and pol.vendor_product_num like p_concatenated_segments
3013 and pol.item_id = msi.inventory_item_id
3014 AND pol.vendor_product_num IS NOT NULL
3015 UNION ALL
3016 --- Cross Ref SQL
3017 ---select distinct mcr.cross_reference,
3018 select distinct msi.concatenated_segments,
3019 msi.inventory_item_id,
3020 msi.description,
3021 Nvl(msi.revision_qty_control_code,1),
3022 Nvl(msi.lot_control_code, 1),
3023 Nvl(msi.serial_number_control_code, 1),
3024 Nvl(msi.restrict_subinventories_code, 2),
3025 Nvl(msi.restrict_locators_code,2),
3026 Nvl(msi.location_control_code,1),
3027 msi.primary_uom_code,
3028 Nvl(msi.inspection_required_flag,'N'),
3029 Nvl(msi.shelf_life_code, 1),
3030 Nvl(msi.shelf_life_days,0),
3031 Nvl(msi.allowed_units_lookup_code, 2),
3032 Nvl(msi.effectivity_control,1),
3033 0,
3034 0,
3035 Nvl(msi.default_serial_status_id,1),
3036 Nvl(msi.serial_status_enabled,'N'),
3037 Nvl(msi.default_lot_status_id,0),
3038 Nvl(msi.lot_status_enabled,'N'),
3039 --- msi.concatenated_segments,
3040 mcr.cross_reference,
3041 'C',
3042 msi.inventory_item_flag,
3043 0,
3044 msi.inventory_asset_flag,
3045 msi.outside_operation_flag
3046 from
3047 mtl_system_items_kfv msi
3048 ,mtl_cross_references mcr
3049 where msi.organization_id = p_organization_id
3050 and ( (mcr.cross_reference_type = p_crossreftype
3051 and mcr.cross_reference like p_concatenated_segments
3052 ) or
3053 ( mcr.cross_reference_type = g_gtin_cross_ref_type
3054 AND mcr.cross_reference LIKE g_crossref )
3055 )
3056 and ( (mcr.org_independent_flag = 'Y') or (mcr.org_independent_flag = 'N'
3057 and mcr.organization_id = p_organization_id
3058 ) )
3059 and mcr.inventory_item_id = msi.inventory_item_id
3060 and (purchasing_enabled_flag = 'Y' OR stock_enabled_flag = 'Y')
3061 UNION ALL
3062 -- Non Item Master
3063 select distinct pol.item_description,
3064 to_number(''),
3065 pol.item_description,
3066 1,
3067 1,
3068 1,
3069 2,
3070 2,
3071 1,
3072 mum.uom_code,
3073 'N',
3074 1,
3075 0,
3076 2,
3077 1,
3078 0,
3079 0,
3080 1,
3081 'N',
3082 0,
3083 'N',
3084 '',
3085 'N',
3086 'N',
3087 0,
3088 to_char(NULL),
3089 'N'
3090 from po_lines_all pol
3091 ,mtl_units_of_measure mum
3092 -- Bug 2619063, 2614016
3093 -- Modified to select the base uom for the uom class defined on po.
3094 where mum.uom_class = (SELECT mum2.uom_class
3095 FROM mtl_units_of_measure mum2
3096 WHERE mum2.unit_of_measure(+) = pol.unit_meas_lookup_code)
3097 and mum.base_uom_flag = 'Y'
3098 and pol.ITEM_ID is null
3099 and pol.item_description is not null
3100 and pol.item_description like p_concatenated_segments
3101 ;
3102 end if;
3103
3104 END GET_ITEM_LOV_RECEIVING ;
3105
3106 PROCEDURE GET_COUNTRY_LOV
3107 (x_country_lov OUT NOCOPY t_genref,
3108 p_country IN VARCHAR2 )
3109 IS
3110 BEGIN
3111 OPEN x_country_lov FOR
3112 SELECT territory_code, territory_short_name
3113 FROM fnd_territories_vl
3114 WHERE territory_code LIKE p_country || '%'
3115 ORDER BY territory_code;
3116 END GET_COUNTRY_LOV;
3117
3118
3119 PROCEDURE Get_Sub_Lov_RcV(x_sub OUT NOCOPY t_genref,
3120 p_organization_id IN NUMBER,
3121 p_item_id IN NUMBER,
3122 p_sub IN VARCHAR2,
3123 p_restrict_subinventories_code IN NUMBER,
3124 p_transaction_type_id IN NUMBER,
3125 p_wms_installed IN VARCHAR2) IS
3126
3127 BEGIN
3128 IF (p_item_id IS NULL
3129 OR p_restrict_subinventories_code <> 1
3130 ) THEN
3131 OPEN x_sub FOR
3132 SELECT msub.secondary_inventory_name
3133 , NVL(msub.locator_type, 1)
3134 , msub.description
3135 , msub.asset_inventory
3136 , lpn_controlled_flag
3137 FROM mtl_secondary_inventories msub
3138 WHERE msub.organization_id = p_organization_id
3139 AND NVL(msub.disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
3140 AND msub.secondary_inventory_name LIKE (p_sub)
3141 AND inv_material_status_grp.is_status_applicable(p_wms_installed, NULL,
3142 p_transaction_type_id, NULL, NULL, p_organization_id, p_item_id,
3143 msub.secondary_inventory_name, NULL, NULL, NULL, 'Z') = 'Y'
3144 ORDER BY UPPER(msub.secondary_inventory_name);
3145 ELSE
3146 -- It is a restricted item,
3147 OPEN x_sub FOR
3148 SELECT msub.secondary_inventory_name
3149 , NVL(msub.locator_type, 1)
3150 , msub.description
3151 , msub.asset_inventory
3152 , lpn_controlled_flag
3153 FROM mtl_secondary_inventories msub
3154 WHERE msub.organization_id = p_organization_id
3155 AND NVL(msub.disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
3156 AND msub.secondary_inventory_name LIKE (p_sub)
3157 AND EXISTS( SELECT NULL
3158 FROM mtl_item_sub_inventories mis
3159 WHERE mis.organization_id = NVL(p_organization_id,
3160 mis.organization_id)
3161
3162 AND mis.inventory_item_id = p_item_id
3163 AND mis.secondary_inventory = msub.secondary_inventory_name)
3164 AND inv_material_status_grp.is_status_applicable(p_wms_installed, NULL,
3165 p_transaction_type_id, NULL, NULL, p_organization_id, p_item_id,
3166 msub.secondary_inventory_name, NULL, NULL, NULL, 'Z') = 'Y'
3167 ORDER BY UPPER(msub.secondary_inventory_name);
3168 END IF;
3169 END get_sub_lov_rcv;
3170
3171
3172 PROCEDURE Calculate_Secondary_Qty(
3173 p_item_no IN VARCHAR2
3174 , p_unit_of_measure IN VARCHAR2
3175 , p_quantity IN NUMBER
3176 , p_lot_no IN VARCHAR2
3177 , p_sublot_no IN VARCHAR2
3178 , p_secondary_unit_of_measure IN VARCHAR2
3179 , x_secondary_quantity OUT NOCOPY NUMBER
3180 )
3181
3182 IS
3183
3184 l_opm_um_code VARCHAR2(25);
3185 l_passed_opm_sec_um_code VARCHAR2(25);
3186 l_opm_item_id NUMBER;
3187 l_opm_dualum_ind NUMBER;
3188 l_opm_secondary_um VARCHAR2(25);
3189 l_lot_id NUMBER;
3190
3191 v_ret_val NUMBER;
3192
3193 Cursor Cr_get_opm_attr IS
3194 Select ilm.item_id,
3195 ilm.dualum_ind,
3196 ilm.item_um2
3197 From ic_item_mst ilm
3198 Where ilm.item_no = p_item_no;
3199
3200 CURSOR Get_Lot_Id (p_item_id NUMBER) IS
3201 SELECT lot_id
3202 FROM ic_lots_mst
3203 WHERE item_id = p_item_id AND
3204 lot_no = p_lot_no;
3205
3206 CURSOR Get_LotSubLot_Id (p_item_id NUMBER) IS
3207 SELECT lot_id
3208 FROM ic_lots_mst
3209 WHERE item_id = p_item_id AND
3210 lot_no = p_lot_no AND
3211 sublot_no = p_sublot_no;
3212 BEGIN
3213
3214 IF p_secondary_unit_of_measure IS NULL OR p_item_no IS NULL
3215 THEN
3216 RETURN;
3217 ELSE
3218 --Get opm attributes for the item.
3219 Open Cr_get_opm_attr;
3220 Fetch Cr_get_opm_attr Into l_opm_item_id, l_opm_dualum_ind, l_opm_secondary_um;
3221
3222 IF (Cr_get_opm_attr%NOTFOUND) THEN
3223 --item not an opm item do nothing just return
3224 CLOSE Cr_get_opm_attr;
3225 RETURN;
3226 END IF;
3227 CLOSE Cr_get_opm_attr;
3228
3229 --if item is not dualum control then return doing nothing.
3230 IF l_opm_dualum_ind = 0 THEN
3231 RETURN;
3232 END IF;
3233
3234 --Get opm uom code for the passed apps unit of measure.
3235 IF p_unit_of_measure IS NOT NULL THEN
3236 BEGIN
3237
3238 l_opm_um_code := po_gml_db_common.get_opm_uom_code(p_unit_of_measure);
3239
3240 EXCEPTION WHEN OTHERS THEN
3241 RETURN;
3242 END;
3243 ELSE
3244 RETURN;
3245 END IF;
3246
3247 IF p_lot_no IS NULL OR p_lot_no = '' THEN
3248 l_lot_id := 0;
3249 ELSIF p_sublot_no IS NULL OR p_sublot_no = '' THEN
3250
3251 Open Get_Lot_Id (l_opm_item_id);
3252 Fetch Get_Lot_Id Into l_lot_id;
3253
3254 IF (Get_Lot_Id%NOTFOUND) THEN
3255 l_lot_id := 0;
3256 END IF;
3257
3258 CLOSE Get_Lot_Id;
3259
3260 ELSE
3261 Open Get_LotSubLot_Id (l_opm_item_id);
3262 Fetch Get_LotSubLot_Id Into l_lot_id;
3263
3264 IF (Get_LotSubLot_Id%NOTFOUND) THEN
3265 l_lot_id := 0;
3266 END IF;
3267
3268 CLOSE Get_LotSubLot_Id;
3269 END IF;
3270
3271 GMICUOM.icuomcv ( l_opm_item_id,
3272 l_lot_id,
3273 p_quantity,
3274 l_opm_um_code,
3275 l_opm_secondary_um,
3276 x_secondary_quantity );
3277
3278 END IF;
3279
3280
3281 EXCEPTION
3282 WHEN OTHERS THEN
3283 NULL;
3284
3285 END Calculate_Secondary_Qty;
3286
3287
3288
3289 -- This returns the locator id for an existing locator and if
3290 -- it does not exist then it creates a new one.
3291 PROCEDURE get_dynamic_locator(x_location_id OUT NOCOPY NUMBER,
3292 x_description OUT NOCOPY VARCHAR2,
3293 x_result OUT NOCOPY VARCHAR2,
3294 x_exist_or_create OUT NOCOPY VARCHAR2,
3295 p_org_id IN NUMBER,
3296 p_sub_code IN VARCHAR2,
3297 p_concat_segs IN VARCHAR2)
3298 IS
3299
3300 l_keystat_val BOOLEAN;
3301 l_sub_default_status NUMBER;
3302 l_validity_check VARCHAR2(10);
3303 l_wms_org BOOLEAN;
3304 l_loc_type NUMBER;
3305 l_return_status VARCHAR2(10);
3306 l_msg_count NUMBER;
3307 l_msg_data VARCHAR2(20);
3308 l_label_status VARCHAR2(20);
3309 l_status_rec inv_material_status_pub.mtl_status_update_rec_type;
3310
3311 BEGIN
3312 x_result := 'S';
3313 l_validity_check := 'passed';
3314
3315 BEGIN
3316 SELECT inventory_location_id
3317 , description
3318 INTO x_location_id
3319 , x_description
3320 FROM mtl_item_locations_kfv
3321 WHERE organization_id = p_org_id
3322 AND subinventory_code = p_sub_code
3323 AND concatenated_segments = p_concat_segs
3324 AND ROWNUM < 2;
3325
3326 x_exist_or_create := 'EXISTS';
3327 RETURN;
3328 EXCEPTION
3329 WHEN NO_DATA_FOUND THEN
3330 l_keystat_val :=
3331 fnd_flex_keyval.validate_segs(operation => 'CREATE_COMB_NO_AT',
3332 appl_short_name => 'INV', key_flex_code => 'MTLL', structure_number => 101,
3333 concat_segments => p_concat_segs, values_or_ids => 'V', data_set => p_org_id);
3334
3335
3336 IF (l_keystat_val = FALSE) THEN
3337 --dbms_output.put_line('ERROR 1');
3338 x_result := 'E';
3339 x_exist_or_create := '';
3340 RETURN;
3341 ELSE
3342 x_location_id := fnd_flex_keyval.combination_id;
3343
3344 x_exist_or_create := 'EXISTS';
3345
3346 IF fnd_flex_keyval.new_combination THEN
3347 x_exist_or_create := 'CREATE';
3348
3349 IF p_sub_code IS NOT NULL THEN
3350 BEGIN
3351 --- check validity
3352 SELECT 'failed'
3353 INTO l_validity_check
3354 FROM DUAL
3355
3356 WHERE EXISTS( SELECT subinventory_code
3357 FROM mtl_item_locations_kfv
3358 WHERE concatenated_segments = p_concat_segs
3359 AND p_sub_code <> subinventory_code
3360 AND organization_id = p_org_id);
3361 EXCEPTION
3362 WHEN NO_DATA_FOUND THEN
3363 NULL;
3364 END;
3365
3366 IF l_validity_check = 'failed' THEN
3367 x_result := 'E';
3368 x_exist_or_create := '';
3369 RETURN;
3370 END IF;
3371
3372 SELECT NVL(default_loc_status_id, 1)
3373 INTO l_sub_default_status
3374 FROM mtl_secondary_inventories
3375 WHERE organization_id = p_org_id
3376 AND secondary_inventory_name = p_sub_code;
3377
3378
3379 l_loc_type := NULL;
3380
3381 UPDATE mtl_item_locations
3382 SET subinventory_code = p_sub_code
3383 , status_id = l_sub_default_status
3384 , inventory_location_type = l_loc_type
3385 WHERE organization_id = p_org_id
3386 AND inventory_location_id = x_location_id;
3387 END IF;
3388 ELSE
3389 BEGIN
3390 --- check validity
3391 SELECT 'failed'
3392 INTO l_validity_check
3393 FROM DUAL
3394 WHERE EXISTS( SELECT subinventory_code
3395 FROM mtl_item_locations_kfv
3396 WHERE concatenated_segments = p_concat_segs
3397 AND p_sub_code <> subinventory_code
3398 AND organization_id = p_org_id);
3399 EXCEPTION
3400 WHEN NO_DATA_FOUND THEN
3401 NULL;
3402 END;
3403
3404 IF l_validity_check = 'failed' THEN
3405 x_result := 'E';
3406 x_exist_or_create := '';
3407 RETURN;
3408 END IF;
3409 END IF;
3410
3411 IF x_exist_or_create = 'CREATE' THEN
3412 -- If a new locator is created then create a status history for it,
3413 -- bug# 1695432
3414
3415 l_status_rec.organization_id := p_org_id;
3416 l_status_rec.inventory_item_id := NULL;
3417 l_status_rec.lot_number := NULL;
3418 l_status_rec.serial_number := NULL;
3419 l_status_rec.update_method := inv_material_status_pub.g_update_method_manual;
3420
3421 l_status_rec.status_id := l_sub_default_status;
3422 l_status_rec.zone_code := p_sub_code;
3423 l_status_rec.locator_id := x_location_id;
3424 l_status_rec.creation_date := SYSDATE;
3425 l_status_rec.created_by := fnd_global.user_id;
3426 l_status_rec.last_update_date := SYSDATE;
3427 l_status_rec.last_update_login := fnd_global.user_id;
3428 l_status_rec.initial_status_flag := 'Y';
3429 l_status_rec.from_mobile_apps_flag := 'Y';
3430 inv_material_status_pkg.insert_status_history(l_status_rec);
3431 -- Do we need this for OPM ??
3432 -- If a new locator is created, call label printing API
3433
3434 inv_label.print_label_manual_wrap(
3435 x_return_status => l_return_status
3436 , x_msg_count => l_msg_count
3437 , x_msg_data => l_msg_data
3438 , x_label_status => l_label_status
3439 , p_business_flow_code => 24
3440 , p_organization_id => p_org_id
3441 , p_subinventory_code => p_sub_code
3442 , p_locator_id => x_location_id
3443 );
3444 END IF;
3445 END IF;
3446 END;
3447 END Get_Dynamic_Locator;
3448
3449
3450
3451 PROCEDURE get_prj_loc_lov(
3452 x_locators OUT NOCOPY t_genref
3453 , p_organization_id IN NUMBER
3454 , p_subinventory_code IN VARCHAR2
3455 , p_restrict_locators_code IN NUMBER
3456 , p_inventory_item_id IN NUMBER
3457 , p_concatenated_segments IN VARCHAR2
3458 , p_transaction_type_id IN NUMBER
3459 , p_wms_installed IN VARCHAR2
3460 , p_project_id IN NUMBER
3461 , p_task_id IN NUMBER
3462 ) IS
3463 x_return_status VARCHAR2(100);
3464 x_display VARCHAR2(100);
3465 x_project_col NUMBER;
3466 x_task_col NUMBER;
3467 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
3468
3469 BEGIN
3470
3471
3472 IF p_concatenated_segments IS NOT NULL THEN
3473 IF p_Restrict_Locators_Code = 1 THEN --Locators restricted to predefined list
3474
3475 OPEN x_Locators FOR
3476 select a.inventory_location_id,
3477 INV_PROJECT.GET_LOCSEGS(a.concatenated_segments),
3478 nvl( a.description, -1)
3479 FROM mtl_item_locations_kfv a,mtl_secondary_locators b, ic_loct_mst l
3480 WHERE b.organization_id = p_Organization_Id
3481 AND b.inventory_item_id = p_Inventory_Item_Id
3482 AND a.inventory_location_id = l.inventory_location_id
3483 AND nvl(a.disable_date, trunc(sysdate+1)) > trunc(sysdate)
3484 AND b.subinventory_code = p_Subinventory_Code
3485 AND a.inventory_location_id = b.secondary_locator
3486 AND a.concatenated_segments LIKE (p_concatenated_segments||'%')
3487 /* BUG#2810405: To show only common locators in the LOV */
3488 /*
3489 AND inv_material_status_grp.is_status_applicable
3490 ( p_wms_installed,
3491 NULL,
3492 p_transaction_type_id,
3493 NULL,
3494 NULL,
3495 p_Organization_Id,
3496 p_Inventory_Item_Id,
3497 p_Subinventory_Code,
3498 a.inventory_location_id,
3499 NULL,
3500 NULL,
3501 'L') = 'Y'
3502 */
3503 ORDER BY 2;
3504
3505 ELSE --Locators not restricted
3506
3507 OPEN x_Locators FOR
3508 select a.inventory_location_id,
3509 INV_PROJECT.GET_LOCSEGS(concatenated_segments),
3510 description
3511 FROM mtl_item_locations_kfv a, ic_loct_mst l
3512 WHERE organization_id = p_Organization_Id
3513 AND subinventory_code = p_Subinventory_Code
3514 AND a.inventory_location_id = l.inventory_location_id
3515 AND nvl(disable_date, trunc(sysdate+1)) > trunc(sysdate)
3516 AND concatenated_segments LIKE (p_concatenated_segments||'%' )
3517 /* BUG#2810405: To show only common locators in the LOV */
3518 /*
3519 AND inv_material_status_grp.is_status_applicable
3520 ( p_wms_installed,
3521 NULL,
3522 p_transaction_type_id,
3523 NULL,
3524 NULL,
3525 p_Organization_Id,
3526 p_Inventory_Item_Id,
3527 p_Subinventory_Code,
3528 inventory_location_id,
3529 NULL,
3530 NULL,
3531 'L') = 'Y'
3532 */
3533 ORDER BY 2;
3534 END IF;
3535 ELSE /*Non PJM Org concatenated segments null*/
3536 IF p_Restrict_Locators_Code = 1 THEN --Locators restricted to predefined list
3537
3538 OPEN x_Locators FOR
3539 select a.inventory_location_id,
3540 INV_PROJECT.GET_LOCSEGS(a.concatenated_segments),
3541 nvl( a.description, -1)
3542 FROM mtl_item_locations_kfv a,mtl_secondary_locators b, ic_loct_mst l
3543 WHERE b.organization_id = p_Organization_Id
3544 AND b.inventory_item_id = p_Inventory_Item_Id
3545 AND a.inventory_location_id = l.inventory_location_id
3546 AND nvl(a.disable_date, trunc(sysdate+1)) > trunc(sysdate)
3547 AND b.subinventory_code = p_Subinventory_Code
3548 AND a.inventory_location_id = b.secondary_locator
3549 /* BUG#2810405: To show only common locators in the LOV */
3550 /*
3551 AND inv_material_status_grp.is_status_applicable
3552 ( p_wms_installed,
3553 NULL,
3554 p_transaction_type_id,
3555 NULL,
3556 NULL,
3557 p_Organization_Id,
3558 p_Inventory_Item_Id,
3559 p_Subinventory_Code,
3560 a.inventory_location_id,
3561 NULL,
3562 NULL,
3563 'L') = 'Y'
3564 */
3565 ORDER BY 2;
3566
3567 ELSE --Locators not restricted
3568 OPEN x_Locators FOR
3569 select a.inventory_location_id,
3570 INV_PROJECT.GET_LOCSEGS(concatenated_segments),
3571 description
3572 FROM mtl_item_locations_kfv a, ic_loct_mst l
3573 WHERE organization_id = p_Organization_Id
3574 AND subinventory_code = p_Subinventory_Code
3575 AND a.inventory_location_id = l.inventory_location_id
3576 AND nvl(disable_date, trunc(sysdate+1)) > trunc(sysdate)
3577 /* BUG#2810405: To show only common locators in the LOV */
3578 /*
3579 AND inv_material_status_grp.is_status_applicable
3580 ( p_wms_installed,
3581 NULL,
3582 p_transaction_type_id,
3583 NULL,
3584 NULL,
3585 p_Organization_Id,
3586 p_Inventory_Item_Id,
3587 p_Subinventory_Code,
3588 inventory_location_id,
3589 NULL,
3590 NULL,
3591 'L') = 'Y'
3592 */
3593 ORDER BY 2;
3594 END IF;
3595 END IF;
3596
3597 END get_prj_loc_lov;
3598
3599 END GML_MOBILE_RECEIPT;