1 PACKAGE BODY PO_ITEMS_SV as
2 /* $Header: POXCOI1B.pls 120.4 2011/06/27 12:44:04 swvyamas ship $ */
3 /*============================= PO_ITEMS_SV ===============================*/
4 g_chktype_TRACKING_QTY_IND_S CONSTANT
5 MTL_SYSTEM_ITEMS_B.TRACKING_QUANTITY_IND%TYPE
6 := 'PS'; --<INVCONV R12>
7 /*===========================================================================
8
9 FUNCTION NAME: val_category()
10
11 ===========================================================================*/
12 FUNCTION val_category(X_category_id IN NUMBER,
13 X_structure_id IN NUMBER) return BOOLEAN IS
14
15 X_progress varchar2(3) := NULL;
16 X_category_id_v number := NULL;
17
18 BEGIN
19
20 X_progress := '010';
21
22 /* Check if the given Category is active */
23
24 SELECT category_id
25 INTO X_category_id_v
26 FROM mtl_categories
27 WHERE sysdate < nvl(disable_date, sysdate + 1)
28 AND enabled_flag = 'Y'
29 AND sysdate between nvl(start_date_active, sysdate -1)
30 AND nvl(end_date_active, sysdate + 1)
31 AND category_id = X_category_id
32 AND structure_id = X_structure_id;
33
34 return (TRUE);
35
36 EXCEPTION
37
38 when no_data_found then
39 return (FALSE);
40 when others then
41 po_message_s.sql_error('val_category',X_progress,sqlcode);
42 raise;
43
44 END val_category;
45
46 /*===========================================================================
47
48 PROCEDURE NAME: val_item_org()
49
50 ===========================================================================*/
51
52 PROCEDURE val_item_org
53 (X_item_revision IN VARCHAR2,
54 X_item_id IN NUMBER,
55 X_master_ship_org_id IN NUMBER,
56 X_outside_operation_flag IN VARCHAR2,
57 X_item_valid IN OUT NOCOPY VARCHAR2) IS
58
59 x_progress VARCHAR2(3) := '';
60
61 BEGIN
62
63 x_progress := '010';
64
65 /*
66 ** If item does not have a revision, the following SQL statement
67 ** will be executed to verify the line item is valid for the
68 ** selected organization.
69 */
70 IF (X_item_revision IS NULL) THEN
71
72 SELECT MAX('Y') /*'item valid in the defaulted ship org'*/
73 INTO X_item_valid
74 FROM mtl_system_items msi
75 WHERE msi.inventory_item_id = X_item_id
76 AND msi.organization_id = X_master_ship_org_id
77 AND msi.purchasing_enabled_flag = 'Y'
78 AND ( ( X_outside_operation_flag = 'Y'
79 AND nvl(msi.outside_operation_flag,'N') = 'Y')
80 OR X_outside_operation_flag = 'N'
81 );
82
83 x_progress := '020';
84
85 IF (X_item_valid is NULL) THEN
86 X_item_valid := 'N';
87 END IF;
88
89 ELSE
90
91 /*
92 ** If item does have a revision, the following SQL statement
93 ** will be executed to verify the line item and it's revision are
94 ** valid for the selected organization.
95 */
96 SELECT MAX('Y')
97 INTO X_item_valid
98 FROM mtl_system_items msi,
99 mtl_item_revisions mir
100 WHERE mir.organization_id = X_master_ship_org_id
101 AND mir.revision = X_item_revision
102 AND mir.inventory_item_id = X_item_id
103 AND msi.inventory_item_id = X_item_id
104 AND msi.organization_id = X_master_ship_org_id
105 AND msi.purchasing_enabled_flag = 'Y'
106 AND ( ( X_outside_operation_flag = 'Y'
107 AND nvl(msi.outside_operation_flag,'N') = 'Y')
108 OR X_outside_operation_flag = 'N'
109 );
110
111 x_progress := '030';
112
113 IF (X_item_valid is NULL) THEN
114 X_item_valid := 'N';
115 END IF;
116
117 END IF;
118
119 EXCEPTION
120 WHEN OTHERS THEN
121 po_message_s.sql_error('val_item_org', x_progress, sqlcode);
122
123 END val_item_org;
124
125 /*===========================================================================
126
127 PROCEDURE NAME: get_item_info()
128
129 ===========================================================================*/
130
131 PROCEDURE get_item_info(X_type_lookup_code IN VARCHAR2,
132 X_category_set_id IN NUMBER,
133 X_item_id IN NUMBER,
134 X_inventory_organization_id IN NUMBER,
135 X_source_type_lookup_code IN VARCHAR2,
136 X_item_description IN OUT NOCOPY VARCHAR2,
137 X_unit_meas_lookup_code IN OUT NOCOPY VARCHAR2,
138 X_unit_price IN OUT NOCOPY NUMBER,
139 X_category_id IN OUT NOCOPY NUMBER,
140 X_purchasing_enabled_flag IN OUT NOCOPY VARCHAR2,
141 X_internal_order_enabled_flag IN OUT NOCOPY VARCHAR2,
142 X_outside_op_uom_type IN OUT NOCOPY VARCHAR2,
143 X_inventory_asset_flag IN OUT NOCOPY VARCHAR2,
144 X_allow_item_desc_update_flag IN OUT NOCOPY VARCHAR2,
145 X_allowed_units_lookup_code IN OUT NOCOPY NUMBER,
146 X_primary_unit_class IN OUT NOCOPY VARCHAR2,
147 X_rfq_required_flag IN OUT NOCOPY VARCHAR2,
148 X_un_number_id IN OUT NOCOPY NUMBER,
149 X_hazard_class_id IN OUT NOCOPY NUMBER,
150 X_inv_planned_item_flag IN OUT NOCOPY VARCHAR2,
151 X_mrp_planned_item_flag IN OUT NOCOPY VARCHAR2,
152 X_planned_item_flag IN OUT NOCOPY VARCHAR2,
153 X_taxable_flag IN OUT NOCOPY VARCHAR2,
154 X_market_price IN OUT NOCOPY NUMBER,
155 X_invoice_close_tolerance IN OUT NOCOPY NUMBER,
156 X_receive_close_tolerance IN OUT NOCOPY NUMBER,
157 X_receipt_required_flag IN OUT NOCOPY VARCHAR2,
158 X_restrict_subinventories_code IN OUT NOCOPY NUMBER,
159 X_hazard_class IN OUT NOCOPY VARCHAR2,
160 X_un_number IN OUT NOCOPY VARCHAR2,
161 X_stock_enabled_flag IN OUT NOCOPY VARCHAR2,
162 X_outside_operation_flag IN OUT NOCOPY VARCHAR2,
163 --<INVCONV R12 START>
164 X_secondary_default_ind IN OUT NOCOPY VARCHAR2,
165 X_grade_control_flag IN OUT NOCOPY VARCHAR2,
166 X_secondary_unit_of_measure IN OUT NOCOPY VARCHAR2
167 --<INVCONV R12 END>
168 ) IS
169
170 x_progress VARCHAR2(3) := '';
171
172 BEGIN
173 x_progress := '010';
174 IF (X_item_id is NOT NULL) THEN
175
176 po_items_sv.get_item_defaults
177 (X_type_lookup_code,
178 X_category_set_id,
179 X_item_id,
180 X_inventory_organization_id,
181 X_source_type_lookup_code,
182 X_item_description,
183 X_unit_meas_lookup_code,
184 X_unit_price,
185 X_category_id,
186 X_purchasing_enabled_flag,
187 X_internal_order_enabled_flag,
188 X_outside_op_uom_type,
189 X_inventory_asset_flag,
190 X_allow_item_desc_update_flag,
191 X_allowed_units_lookup_code,
192 X_primary_unit_class,
193 X_rfq_required_flag,
194 X_un_number_id,
195 X_hazard_class_id,
196 X_inv_planned_item_flag,
197 X_mrp_planned_item_flag,
198 X_planned_item_flag,
199 X_taxable_flag,
200 X_market_price,
201 X_invoice_close_tolerance,
202 X_receive_close_tolerance,
203 X_receipt_required_flag,
204 X_restrict_subinventories_code,
205 X_stock_enabled_flag,
206 X_outside_operation_flag,
207 --<INVCONV R12 START>
208 X_secondary_default_ind,
209 X_grade_control_flag,
210 X_secondary_unit_of_measure
211 --<INVCONV R12 END>
212 );
213 x_progress := '020';
214
215 /*
216 ** Derive the un number from the returned un_number_id.
217 */
218 IF (X_un_number_id IS NOT NULL) THEN
219 po_items_sv.get_un_number(X_un_number_id,
220 X_un_number);
221 x_progress := '030';
222
223 END IF;
224
225 /*
226 ** Derive the hazard class name from the returned hazard_class_id.
227 */
228 IF (X_hazard_class_id IS NOT NULL) THEN
229 po_items_sv.get_hazard_class(X_hazard_class_id,
230 X_hazard_class);
231 x_progress := '040';
232
233 END IF;
234 END if;
235 EXCEPTION
236 WHEN OTHERS THEN
237 po_message_s.sql_error('get_item_info', x_progress, sqlcode);
238
239 END get_item_info;
240
241 /*===========================================================================
242
243 PROCEDURE NAME: get_item_defaults()
244
245 ===========================================================================*/
246
247 PROCEDURE get_item_defaults
248 (X_type_lookup_code IN VARCHAR2,
249 X_category_set_id IN NUMBER,
250 X_item_id IN NUMBER,
251 X_inventory_organization_id IN NUMBER,
252 X_source_type_lookup_code IN VARCHAR2,
253 X_item_description IN OUT NOCOPY VARCHAR2,
254 X_unit_meas_lookup_code IN OUT NOCOPY VARCHAR2,
255 X_unit_price IN OUT NOCOPY NUMBER,
256 X_category_id IN OUT NOCOPY NUMBER,
257 X_purchasing_enabled_flag IN OUT NOCOPY VARCHAR2,
258 X_internal_order_enabled_flag IN OUT NOCOPY VARCHAR2,
259 X_outside_op_uom_type IN OUT NOCOPY VARCHAR2,
260 X_inventory_asset_flag IN OUT NOCOPY VARCHAR2,
261 X_allow_item_desc_update_flag IN OUT NOCOPY VARCHAR2,
262 X_allowed_units_lookup_code IN OUT NOCOPY NUMBER,
263 X_primary_unit_class IN OUT NOCOPY VARCHAR2,
264 X_rfq_required_flag IN OUT NOCOPY VARCHAR2,
265 X_un_number_id IN OUT NOCOPY NUMBER,
266 X_hazard_class_id IN OUT NOCOPY NUMBER,
267 X_inv_planned_item_flag IN OUT NOCOPY VARCHAR2,
268 X_mrp_planned_item_flag IN OUT NOCOPY VARCHAR2,
269 X_planned_item_flag IN OUT NOCOPY VARCHAR2,
270 X_taxable_flag IN OUT NOCOPY VARCHAR2,
271 X_market_price IN OUT NOCOPY NUMBER,
272 X_invoice_close_tolerance IN OUT NOCOPY NUMBER,
273 X_receive_close_tolerance IN OUT NOCOPY NUMBER,
274 X_receipt_required_flag IN OUT NOCOPY VARCHAR2,
275 X_restrict_subinventories_code IN OUT NOCOPY NUMBER,
276 X_stock_enabled_flag IN OUT NOCOPY VARCHAR2,
277 X_outside_operation_flag IN OUT NOCOPY VARCHAR2,
278 --<INVCONV R12 START>
279 X_secondary_default_ind IN OUT NOCOPY VARCHAR2,
280 X_grade_control_flag IN OUT NOCOPY VARCHAR2,
281 X_secondary_unit_of_measure IN OUT NOCOPY VARCHAR2
282 --<INVCONV R12 END>
283 ) IS
284
285 x_progress VARCHAR2(3) := NULL;
286
287 BEGIN
288
289 x_progress := '010';
290
291 /*
292 ** If document is a requisition (type_lookup_code = INTERNAL or PURCHASE),
293 ** a different SELECT statement is executed.
294 */
295 /* BUG: 656428 - Added to_char conversion to the mrp_planning_code
296 ** and the inventory_planning_code to avoid value errors.
297 */
298
299 IF (X_type_lookup_code IN ('INTERNAL', 'PURCHASE')) THEN
300
301 x_progress := '020';
302
303 SELECT msi.description,
304 decode(X_source_type_lookup_code, 'INVENTORY',
305 nvl(msi.unit_of_issue, msi.primary_unit_of_measure),
306 msi.primary_unit_of_measure),
307 msi.list_price_per_unit,
308 mic.category_id,
309 msi.purchasing_enabled_flag,
310 msi.internal_order_enabled_flag,
311 msi.outside_operation_uom_type,
312 msi.inventory_asset_flag,
313 msi.allow_item_desc_update_flag,
314 msi.allowed_units_lookup_code,
315 mum.uom_class,
316 nvl(msi.rfq_required_flag, X_rfq_required_flag),
317 nvl(msi.un_number_id, X_un_number_id),
318 nvl(msi.hazard_class_id, X_hazard_class_id),
319 decode(to_char(msi.inventory_planning_code),
320 NULL,'N',
321 '6', 'N',
322 'Y'),
323 decode(to_char(msi.mrp_planning_code),
324 NULL,'N',
325 '6', 'N',
326 'Y'),
327 nvl(msi.stock_enabled_flag,'N'),
328 nvl(msi.outside_operation_flag,'N'),
329 --<INVCONV R12 START>
330 decode(msi.tracking_quantity_ind,
331 g_chktype_TRACKING_QTY_IND_S,msi.secondary_default_ind,NULL),
332 msi.grade_control_flag,
333 decode(msi.tracking_quantity_ind,
334 g_chktype_TRACKING_QTY_IND_S,mum2.unit_of_measure,NULL)
335 --<INVCONV R12 END>
336 INTO X_item_description,
337 X_unit_meas_lookup_code,
338 X_unit_price,
339 X_category_id,
340 X_purchasing_enabled_flag,
341 X_internal_order_enabled_flag,
342 X_outside_op_uom_type,
343 X_inventory_asset_flag,
344 X_allow_item_desc_update_flag,
345 X_allowed_units_lookup_code,
346 X_primary_unit_class,
347 X_rfq_required_flag,
348 X_un_number_id,
349 X_hazard_class_id,
350 X_inv_planned_item_flag,
351 X_mrp_planned_item_flag,
352 X_stock_enabled_flag,
353 X_outside_operation_flag,
354 --<INVCONV R12 START>
355 X_secondary_default_ind,
356 X_grade_control_flag ,
357 X_secondary_unit_of_measure
358 --<INVCONV R12 END>
359 FROM mtl_units_of_measure mum,
360 mtl_item_categories mic,
361 mtl_system_items msi,
362 mtl_parameters mpa,
363 mtl_units_of_measure mum2 --<INVCONV R12>
364 WHERE mic.inventory_item_id = X_item_id
365 AND mic.category_set_id = X_category_set_id
366 AND mic.organization_id = X_inventory_organization_id
367 AND msi.organization_id = X_inventory_organization_id
368 AND msi.inventory_item_id = X_item_id
369 AND mum.unit_of_measure =
370 decode(X_source_type_lookup_code,'INVENTORY',
371 nvl(msi.unit_of_issue, msi.primary_unit_of_measure),
372 msi.primary_unit_of_measure)
373 AND mpa.organization_id = X_inventory_organization_id
374 AND msi.secondary_uom_code = mum2.uom_code(+) ; --<INVCONV R12>
375
376 x_progress := '030';
377
378 /*
379 ** If document is NOT a requisition, perform a different SELECT
380 ** (this SELECT doesn't take into consideration the source type)
381 */
382 ELSE
383
384 x_progress := '040';
385
386 /* Bug # 2076346.
387 Added conditions for mrp_planning_code 7,8,9. This will be used in the
388 need_by_date validation in Shipments of Enter PO later. */
389
390 SELECT mic.category_id,
391 decode(msi.mrp_planning_code, 3, 'Y', 4, 'Y', 7, 'Y', 8, 'Y', 9, 'Y',
392 decode(msi.inventory_planning_code,1,'Y',2,'Y', 'N')),
393 msi.description,
394 msi.list_price_per_unit,
395 msi.market_price,
396 msi.taxable_flag,
397 msi.allow_item_desc_update_flag,
398 msi.allowed_units_lookup_code,
399 msi.primary_unit_of_measure,
400 mum.uom_class,
401 msi.un_number_id,
402 msi.hazard_class_id,
403 msi.outside_operation_uom_type,
404 nvl(msi.invoice_close_tolerance, X_invoice_close_tolerance),
405 nvl(msi.receive_close_tolerance, X_receive_close_tolerance),
406 nvl(msi.receipt_required_flag, X_receipt_required_flag),
407 msi.restrict_subinventories_code,
408 --<INVCONV R12 START>
409 decode(msi.tracking_quantity_ind,
410 g_chktype_TRACKING_QTY_IND_S,msi.secondary_default_ind,NULL),
411 msi.grade_control_flag,
412 decode(msi.tracking_quantity_ind,
413 g_chktype_TRACKING_QTY_IND_S,mum2.unit_of_measure,NULL)
414 --<INVCONV R12 END>
415 INTO X_category_id,
416 X_planned_item_flag,
417 X_item_description,
418 X_unit_price,
419 X_market_price,
420 X_taxable_flag,
421 X_allow_item_desc_update_flag,
422 X_allowed_units_lookup_code,
423 X_unit_meas_lookup_code,
424 X_primary_unit_class,
425 X_un_number_id,
426 X_hazard_class_id,
427 X_outside_op_uom_type,
428 X_invoice_close_tolerance,
429 X_receive_close_tolerance,
430 X_receipt_required_flag,
431 X_restrict_subinventories_code,
432 --<INVCONV R12 START>
433 X_secondary_default_ind,
434 X_grade_control_flag ,
435 X_secondary_unit_of_measure
436 --<INVCONV R12 END>
437 FROM mtl_units_of_measure mum,
438 mtl_item_categories mic,
439 mtl_system_items msi,
440 mtl_units_of_measure mum2 --<INVCONV R12>
441 WHERE msi.inventory_item_id = X_item_id
442 AND mic.inventory_item_id = X_item_id
443 AND mic.category_set_id = X_category_set_id
444 AND mic.organization_id = X_inventory_organization_id
445 AND msi.organization_id = X_inventory_organization_id
446 AND msi.primary_unit_of_measure = mum.unit_of_measure
447 AND msi.secondary_uom_code = mum2.uom_code(+) ; --<INVCONV R12>
448
449 x_progress := '050';
450
451 END IF;
452
453
454 /*
455 ** Bug 2198247. The item description should be
456 ** fetched from mtl_system_item_tl not from
457 ** mtl_system_items. So the following SELECT statement
458 ** is added to get the description from mtl_system_items_tl.
459 */
460
461
462 SELECT description
463 INTO X_item_description
464 FROM mtl_system_items_tl
465 WHERE inventory_item_id = X_item_id
466 AND language = USERENV('LANG')
467 AND organization_id = X_inventory_organization_id;
468
469 -- End of Bug 2198247.
470
471 EXCEPTION
472 WHEN OTHERS THEN
473 po_message_s.sql_error('get_item_defaults', x_progress, sqlcode);
474
475 END get_item_defaults;
476
477 /*===========================================================================
478
479 PROCEDURE NAME: get_hazard_class()
480
481 ===========================================================================*/
482
483 PROCEDURE get_hazard_class(X_hazard_class_id IN NUMBER,
484 X_hazard_class IN OUT NOCOPY VARCHAR2) IS
485
486 x_progress VARCHAR2(3) := NULL;
487
488 BEGIN
489
490 x_progress := '010';
491
492 SELECT hazard_class
493 INTO X_hazard_class
494 FROM po_hazard_classes
495 WHERE hazard_class_id = X_hazard_class_id;
496
497 x_progress := '020';
498
499 EXCEPTION
500 WHEN OTHERS THEN
501 po_message_s.sql_error('get_hazard_class', x_progress, sqlcode);
502
503 END get_hazard_class;
504
505 /*===========================================================================
506
507 PROCEDURE NAME: get_un_number()
508
509 ===========================================================================*/
510
511 PROCEDURE get_un_number(X_un_number_id IN NUMBER,
512 X_un_number IN OUT NOCOPY VARCHAR2) IS
513
514 x_progress VARCHAR2(3) := NULL;
515
516 BEGIN
517
518 x_progress := '010';
519
520 SELECT poun.un_number
521 INTO X_un_number
522 FROM po_un_numbers poun
523 WHERE poun.un_number_id = X_un_number_id;
524
525 x_progress := '020';
526
527 EXCEPTION
528 WHEN OTHERS THEN
529 po_message_s.sql_error('get_un_number', x_progress, sqlcode);
530
531 END get_un_number;
532
533 /*===========================================================================
534
535 PROCEDURE NAME: val_item_revision()
536
537 ===========================================================================*/
538
539 PROCEDURE val_item_revision(X_item_revision IN VARCHAR2,
540 X_item_id IN NUMBER,
541 X_destination_org_id IN OUT NOCOPY NUMBER,
542 X_deliver_to_location_id IN OUT NOCOPY NUMBER,
543 X_destination_subinventory IN OUT NOCOPY VARCHAR2,
544 X_destination_org_name IN OUT NOCOPY VARCHAR2,
545 X_revision_is_valid IN OUT NOCOPY VARCHAR2) IS
546
547 x_progress VARCHAR2(3) := '';
548
549 BEGIN
550
551 x_progress := '010';
552 X_revision_is_valid := '';
553
554 /*
555 ** If an item revision is passed into this procedure, verify the revision
556 ** is valid for the item in the current organization.
557 */
558 IF (X_item_revision is NOT NULL) THEN
559
560 SELECT MAX('Y')
561 INTO X_revision_is_valid
562 FROM mtl_item_revisions mir
563 WHERE mir.organization_id = X_destination_org_id
564 AND mir.revision = X_item_revision
565 AND mir.inventory_item_id = X_item_id;
566
567 x_progress := '020';
568
569 /*
570 ** If the revision is invalid, then set the organization,
571 ** deliver to location, and destination subinventory to NULL, and
572 ** display an error message.
573 */
574 IF (X_revision_is_valid is null) THEN
575
576 SELECT organization_name
577 INTO X_destination_org_name
578 FROM org_organization_definitions
579 WHERE organization_id = X_destination_org_id;
580
581 x_progress := '030';
582
583 X_revision_is_valid := 'N';
584
585 X_destination_org_id := '';
586 X_deliver_to_location_id := '';
587 X_destination_subinventory := '';
588
589 po_message_s.app_error('PO_REQ_REV_NOT_VALID', 'REVISION', X_item_revision,
590 'ORG', X_destination_org_name);
591
592 END IF;
593
594 END IF;
595
596 EXCEPTION
597 WHEN OTHERS THEN
598 po_message_s.sql_error('val_item_revision', x_progress, sqlcode);
599
600 END val_item_revision;
601
602 /*===========================================================================
603
604 PROCEDURE NAME: get_secondary_attributes()
605
606 ===========================================================================*/
607
608
609 PROCEDURE get_secondary_attributes (
610 X_item_id IN NUMBER,
611 X_inventory_organization_id IN NUMBER,
612 X_fetch_secondary_uom IN VARCHAR2 DEFAULT 'Y',
613 X_secondary_unit_of_measure IN OUT NOCOPY VARCHAR2,
614 X_secondary_default_ind OUT NOCOPY VARCHAR2,
615 X_grade_control_flag OUT NOCOPY VARCHAR2,
616 X_secondary_unit_of_measure_tl OUT NOCOPY VARCHAR2) IS
617
618 /** FETCH_SECONDARY_UOM = 'N' - don't fetch secondary unit of measure
619 FETCH_SECONDARY_UOM = 'Y' and secondary_unit_of_measure is not given
620 Fetch secondary unit of measure and secondary unit of measure_tl
621 FETCH_SECONDARY_UOM = 'Y' and secondary_unit_of_measure is given
622 Fetch secondary unit of measure_tl **/
623
624 BEGIN
625 IF x_item_id IS NULL OR X_inventory_organization_id IS NULL THEN
626 RETURN;
627 END IF;
628
629 IF X_fetch_secondary_uom = 'Y' and x_secondary_unit_of_measure IS NULL THEN
630
631 SELECT decode(msi.tracking_quantity_ind,
632 g_chktype_TRACKING_QTY_IND_S,msi.secondary_default_ind,NULL),
633 msi.grade_control_flag,
634 decode(msi.tracking_quantity_ind,
635 g_chktype_TRACKING_QTY_IND_S,mum.unit_of_measure,NULL)
636 INTO X_secondary_default_ind, X_grade_control_flag, X_secondary_unit_of_measure
637 FROM mtl_units_of_measure mum, mtl_system_items msi
638 WHERE msi.organization_id = X_inventory_organization_id
639 AND msi.inventory_item_id = X_item_id
640 AND mum.uom_code(+) = msi.secondary_uom_code ;
641
642 ELSE
643 SELECT decode(msi.tracking_quantity_ind,
644 g_chktype_TRACKING_QTY_IND_S,msi.secondary_default_ind,NULL),
645 msi.grade_control_flag
646 INTO X_secondary_default_ind, X_grade_control_flag
647 FROM mtl_system_items msi
648 WHERE msi.organization_id = X_inventory_organization_id
649 AND msi.inventory_item_id = X_item_id ;
650 END IF;
651
652 IF X_fetch_secondary_uom = 'Y' AND X_secondary_unit_of_measure is not null then
653 po_lines_sv4.get_unit_meas_lookup_code_tl(X_secondary_unit_of_measure,
654 X_secondary_unit_of_measure_tl);
655 END IF;
656
657 EXCEPTION WHEN OTHERS THEN
658 po_message_s.sql_error('get_secondary_attributes', '010', sqlcode);
659 RAISE;
660 END GET_SECONDARY_ATTRIBUTES ;
661
662
663 -- bug5467964 START
664
665 /*===========================================================================
666
667 PROCEDURE NAME: has_valid_item_rev_for_line
668
669 ===========================================================================*/
670 PROCEDURE has_valid_item_rev_for_line
671 ( p_item_id IN NUMBER,
672 p_has_line_been_saved_flag IN VARCHAR2,
673 p_po_line_id IN NUMBER,
674 p_draft_id IN NUMBER ,
675 p_sob_id IN NUMBER,
676 x_result OUT NOCOPY VARCHAR2
677 ) IS
678
679 l_key PO_SESSION_GT.key%TYPE;
680 l_dummy NUMBER;
681 l_shipment_count NUMBER;
682
683 --Bug11658279
684 l_doc_type PO_HEADERS_ALL.type_lookup_code%TYPE;
685
686 BEGIN
687
688 /* Bug 11658279 START- For a BPA, there may not be shipments when line is saved.
689 Bcoz of this, it is not necessary to check if all ship-to
690 orgs have particular item revision.
691 Making a fix to find all possible item revisions within org
692 for current sob in case of BPA line.
693 */
694
695 IF (p_has_line_been_saved_flag = 'Y') THEN
696
697
698 SELECT type_lookup_code INTO l_doc_type
699 FROM po_headers_all
700 WHERE po_header_id = (SELECT po_header_id
701 FROM po_lines_merge_v
702 WHERE po_line_id = p_po_line_id
703 AND draft_id = p_draft_id);
704 END IF;
705 -- Bug 11658279 END
706
707
708 IF (l_doc_type = 'BLANKET' OR p_has_line_been_saved_flag = 'N') THEN -- Bug 11658279 added condition to check if doc type is BPA.
709
710 BEGIN
711
712 -- if the line has not been saved, then find all possible
713 -- revisions from within the org in current sob.
714 SELECT 1
715 INTO l_dummy
716 FROM DUAL
717 WHERE EXISTS
718 (SELECT MIR.revision
719 FROM mtl_item_revisions MIR,
720 org_organization_definitions OOD
721 WHERE OOD.set_of_books_id = p_sob_id
722 AND SYSDATE < NVL(OOD.disable_date, SYSDATE + 1)
723 AND MIR.organization_id = OOD.organization_id
724 AND MIR.inventory_item_id = p_item_id);
725
726 x_result := FND_API.G_TRUE;
727 EXCEPTION
728 WHEN NO_DATA_FOUND THEN
729 x_result := FND_API.G_FALSE;
730 END;
731 ELSE
732
733 l_key := PO_CORE_S.get_session_gt_nextval;
734
735 -- if the line has been saved, then we return FND_API.TRUE only if
736 -- all the non-cancelled shipments share at least one revision. This
737 -- is essentially done by:
738
739 -- (1) Count all occurences of the revision numbers for the orgs in
740 -- shipment
741 -- (2) Count the number of shipments
742 -- (3) If a particular revision is shared among all orgs, the count
743 -- for the revision should be the same as the shipment count
744
745 INSERT INTO po_session_gt
746 ( key,
747 char1,
748 num1
749 )
750 SELECT l_key,
751 MIR.revision,
752 count(*)
753 FROM po_line_locations_all PLL,
754 mtl_item_revisions MIR,
755 org_organization_definitions OOD
756 WHERE PLL.po_line_id = p_po_line_id
757 AND NVL(PLL.cancel_flag, 'N') = 'N'
758 AND MIR.inventory_item_id = p_item_id
759 AND OOD.set_of_books_id = p_sob_id
760 AND SYSDATE < NVL (OOD.disable_date, SYSDATE + 1)
761 AND PLL.ship_to_organization_id = OOD.organization_id
762 AND PLL.ship_to_organization_id = MIR.organization_id
763 GROUP BY MIR.revision;
764
765 SELECT count(*)
766 INTO l_shipment_count
767 FROM po_line_locations_merge_v
768 WHERE po_line_id = p_po_line_id
769 AND draft_id = p_draft_id
770 AND NVL(cancel_flag, 'N') = 'N';
771
772 BEGIN
773 -- If one of the revision counts is the same as the number of shipments,
774 -- it means that all ship to orgs have this revision
775 SELECT 1
776 INTO l_dummy
777 FROM DUAL
778 WHERE EXISTS
779 ( SELECT 1
780 FROM po_session_gt
781 WHERE key = l_key
782 AND num1 = l_shipment_count );
783
784 x_result := FND_API.G_TRUE;
785
786 EXCEPTION
787 WHEN NO_DATA_FOUND THEN
788 x_result := FND_API.G_FALSE;
789 END;
790
791 DELETE FROM po_session_gt WHERE key = l_key;
792
793
794 END IF;
795
796
797 EXCEPTION
798 WHEN OTHERS THEN
799 PO_MESSAGE_S.sql_error('has_valid_item_rev_for_line', '010', sqlcode);
800 RAISE;
801
802 END has_valid_item_rev_for_line;
803 -- bug5467964 END
804
805
806 END PO_ITEMS_SV;
[Home] [Help]