[Home] [Help]
PACKAGE BODY: APPS.PO_LINES_SV4
Source
1 PACKAGE BODY PO_LINES_SV4 as
2 /* $Header: POXPOL4B.pls 115.24 2004/07/01 17:20:52 zxzhang ship $ */
3
4 /*============================= PO_LINES_SV4 ==============================*/
5
6 /*=============================================================================
7
8 FUNCTION: get_line_num <GA FPI>
9
10 DESCRIPTION: Returns the line number (line_num) based on the po_line_id.
11
12 =============================================================================*/
13 FUNCTION get_line_num
14 (
15 p_po_line_id IN PO_LINES_ALL.po_line_id%TYPE
16 )
17 RETURN PO_LINES_ALL.line_num%TYPE
18 IS
19 x_line_num PO_LINES_ALL.line_num%TYPE;
20 BEGIN
21
22 SELECT line_num
23 INTO x_line_num
24 FROM po_lines_all
25 WHERE po_line_id = p_po_line_id;
26
27 return (x_line_num);
28
29 EXCEPTION
30 WHEN OTHERS THEN
31 po_message_s.sql_error('get_line_num', '000', sqlcode);
32 raise;
33
34 END get_line_num;
35
36
37 /*=============================================================================
38
39 FUNCTION: is_cumulative_pricing <GA FPI>
40
41 DESCRIPTION: Returns TRUE if the price_break_lookup_code for the input
42 po_line_id is of type 'CUMULATIVE'. FALSE, otherwise.
43
44 =============================================================================*/
45 FUNCTION is_cumulative_pricing
46 (
47 p_po_line_id IN PO_LINES_ALL.po_line_id%TYPE
48 )
49 RETURN BOOLEAN
50 IS
51 l_price_break_lookup_code PO_LINES_ALL.price_break_lookup_code%TYPE;
52 BEGIN
53
54 SELECT price_break_lookup_code
55 INTO l_price_break_lookup_code
56 FROM po_lines_all
57 WHERE po_line_id = p_po_line_id;
58
59 IF ( l_price_break_lookup_code = 'CUMULATIVE' ) THEN
60 return(TRUE);
61 ELSE
62 return(FALSE);
63 END IF;
64
65 EXCEPTION
66 WHEN OTHERS THEN
67 po_message_s.sql_error('is_cumulative_pricing', '000', sqlcode);
68 raise;
69
70 END is_cumulative_pricing;
71
72
73 --=============================================================================
74 -- Function : effective_dates_exist -- <GA FPI>
75 -- Type : Private
76 --
77 -- Pre-reqs : -
78 -- Modifies : -
79 -- Description : Determines if any Price Breaks with Effectivity Dates exist
80 -- for the given po_line_id. Automatically returns FALSE if the
81 -- po_line_id is NULL or does not refer to a Blanket line.
82 --
83 -- Parameters : p_po_line_id - Line ID for the Blanket line.
84 --
85 -- Returns : TRUE if Price Breaks with Effectivity Dates exist.
86 -- FALSE otherwise.
87 -- Exceptions : -
88 --=============================================================================
89 FUNCTION effective_dates_exist
90 (
91 p_po_line_id IN PO_LINES_ALL.po_line_id%TYPE
92 )
93 RETURN BOOLEAN
94 IS
95 l_count NUMBER;
96 BEGIN
97
98 SELECT count('Price Breaks with Effectivity Dates')
99 INTO l_count
100 FROM po_line_locations_all
101 WHERE po_line_id = p_po_line_id
102 AND ( ( start_date IS NOT NULL )
103 OR ( end_date IS NOT NULL ) );
104
105 IF ( l_count > 0 ) THEN
106 return (TRUE);
107 ELSE
108 return (FALSE);
109 END IF;
110
111 EXCEPTION
112 WHEN OTHERS THEN
113 return (FALSE);
114
115 END effective_dates_exist;
116
117
118 --=============================================================================
119 -- Function : allow_price_override <2716528>
120 -- Type : Private
121 --
122 -- Pre-reqs : p_po_line_id must refer to an existing line.
123 -- Modifies : -
124 -- Description : Determines if the 'Allow Price Override Flag' is set.
125 --
126 -- Parameters : p_po_line_id - Line ID
127 --
128 -- Returns : TRUE if ALLOW_PRICE_OVERRIDE_FLAG is set to 'Y'.
129 -- FALSE otherwise.
130 -- Exceptions : -
131 --=============================================================================
132 FUNCTION allow_price_override
133 (
134 p_po_line_id PO_LINES_ALL.po_line_id%TYPE
135 )
136 RETURN BOOLEAN
137 IS
138 l_allow_price_override_flag PO_LINES_ALL.allow_price_override_flag%TYPE;
139 BEGIN
140
141 SELECT allow_price_override_flag
142 INTO l_allow_price_override_flag
143 FROM po_lines_all
144 WHERE po_line_id = p_po_line_id;
145
146 IF ( l_allow_price_override_flag = 'Y' )
147 THEN
148 return (TRUE);
149 ELSE
150 return (FALSE);
151 END IF;
152
153 EXCEPTION
154
155 WHEN OTHERS THEN
156 return (TRUE);
157
158 END allow_price_override;
159
160
161
162 /*===========================================================================
163 ** PROCEDURE : get_ship_dist_num()
164 **===========================================================================*/
165
166
167 procedure get_ship_dist_num(X_po_line_id IN NUMBER,
168 X_num_of_ship IN OUT NOCOPY NUMBER,
169 --< NBD TZ/Timestamp FPJ Start >
170 --X_promised_date IN OUT NOCOPY VARCHAR2,
171 --X_need_by IN OUT NOCOPY VARCHAR2,
172 X_promised_date IN OUT NOCOPY DATE,
173 X_need_by IN OUT NOCOPY DATE,
174 --< NBD TZ/Timestamp FPJ End >
175 X_num_of_dist IN OUT NOCOPY NUMBER,
176 X_code_combination_id IN OUT NOCOPY NUMBER) is
177
178
179 X_Progress varchar2(3);
180 X_line_location_id number;
181
182
183 begin
184
185 X_Progress := '010';
186
187 begin
188
189 select line_location_id ,
190 --< NBD TZ/Timestamp FPJ Start >
191 --fnd_date.date_to_chardate(promised_date),
192 --fnd_date.date_to_chardate(need_by_date),
193 promised_date,
194 need_by_date
195 --< NBD TZ/Timestamp FPJ End >
196 into X_line_location_id,
197 X_promised_date,
198 X_need_by
199 from po_line_locations
200 where po_line_id = X_po_line_id;
201
202
203 exception
204 when no_data_found then
205 X_num_of_ship := 0 ; /* Set the value to 0 */
206 when too_many_rows then
207 X_num_of_ship := 2; /* Set it to a value > 1 */
208 when others then
209 po_message_s.sql_error('get_ship_dist_num', x_progress, sqlcode);
210 raise;
211 end;
212
213 /* If the control ever went into the above anonymous block's exception,
214 ** if there were no shipments OR there were more than ONE shipment,
215 ** return back without getting the code combination id. We would
216 ** assume that if there is > 1 shipment, there will be multiple distributions */
217
218 if X_num_of_ship = 0 then
219
220 X_num_of_dist := 0;
221
222 return;
223
224 elsif X_num_of_ship = 2 then
225
226 X_num_of_dist := 2;
227
228 return;
229
230 else
231
232 X_num_of_ship := 1;
233
234 X_Progress := '020';
235
236 if X_line_location_id is not null then
237
238 select code_combination_id
239 into X_code_combination_id
240 from po_distributions
241 where line_location_id = X_line_location_id;
242
243 X_num_of_dist := 1;
244
245 else
246
247 X_num_of_dist := NULL;
248
249 end if;
250
251 end if;
252
253
254 exception
255
256 when no_data_found then
257 X_num_of_dist := 0;
258 when too_many_rows then
259 X_num_of_dist := 2;
260 when others then
261 po_message_s.sql_error('get_ship_dist_num', x_progress, sqlcode);
262 raise;
263 end get_ship_dist_num;
264
265
266
267 /*===========================================================================
268
269 FUNCTION NAME: get_encumbered_quantity
270
271 ===========================================================================*/
272 FUNCTION get_encumbered_quantity
273 (X_po_line_id IN NUMBER) RETURN NUMBER IS
274
275
276 X_progress VARCHAR2(3) := '';
277 X_encumbered_quantity NUMBER := 0;
278
279 BEGIN
280
281 X_progress := '010';
282
283 --<Encumbrance FPJ>
284 SELECT sum(POD.quantity_ordered)
285 INTO X_encumbered_quantity
286 FROM PO_DISTRIBUTIONS_ALL POD
287 WHERE POD.po_line_id = X_po_line_id
288 AND POD.encumbered_flag = 'Y'
289 AND POD.distribution_type in ('STANDARD', 'PLANNED')
290 ;
291
292 RETURN(X_encumbered_quantity);
293
294 EXCEPTION
295 WHEN NO_DATA_FOUND THEN
296 RETURN (0);
297 WHEN OTHERS THEN
298 -- dbms_output.put_line('In exception');
299 po_message_s.sql_error('get_encumbered_quantity', X_progress, sqlcode);
300 RAISE;
301
302 END get_encumbered_quantity;
303
304
305
306 /*===========================================================================
307
308 FUNCTION NAME: get_receipt_required_flag
309
310 ===========================================================================*/
311 FUNCTION get_receipt_required_flag (X_line_type_id IN NUMBER,
312 X_item_id IN NUMBER,
313 X_inventory_org_id IN NUMBER)
314 RETURN VARCHAR2 IS
315
316 X_progress VARCHAR2(3) := '';
317 X_receipt_required_flag VARCHAR2(1) := '';
318
319 BEGIN
320
321
322
323 IF X_item_id is not null then
324 X_progress := '010';
325 SELECT nvl(msi.receipt_required_flag, X_receipt_required_flag)
326 INTO X_receipt_required_flag
327 FROM mtl_system_items msi
328 WHERE msi.inventory_item_id = X_item_id
329 AND msi.organization_id = X_inventory_org_id;
330 ELSE
331 X_progress := '020';
332 SELECT receiving_flag
333 INTO X_receipt_required_flag
334 FROM po_line_types
335 WHERE line_type_id = X_line_type_id;
336 END IF;
337
338
339 RETURN(X_receipt_required_flag);
340
341 EXCEPTION
342 WHEN NO_DATA_FOUND THEN
343 RETURN (NULL);
344 WHEN OTHERS THEN
345 -- dbms_output.put_line('In exception');
346 po_message_s.sql_error('get_receipt_required_flag', X_progress, sqlcode);
347 RAISE;
348
349 END get_receipt_required_flag;
350
351
352 /*===========================================================================
353
354 PROCEDURE NAME: get_ship_quantity_info ()
355
356 ============================================================================*/
357
358
359 PROCEDURE get_ship_quantity_info (X_po_line_id IN NUMBER,
360 X_expense_accrual_code IN VARCHAR2,
361 X_po_header_id IN NUMBER,
362 X_type_lookup_code IN VARCHAR2,
363 X_quantity_received IN OUT NOCOPY NUMBER,
364 X_quantity_billed IN OUT NOCOPY NUMBER,
365 X_encumbered_flag IN OUT NOCOPY VARCHAR2,
366 X_prevent_price_update_flag IN OUT NOCOPY VARCHAR2,
367 X_online_req_flag IN OUT NOCOPY VARCHAR2,
368 X_quantity_released IN OUT NOCOPY NUMBER,
369 X_amount_released IN OUT NOCOPY NUMBER) IS
370
371 X_progress varchar2(3);
372 X_row_exists number := 0;
373
374 l_is_global_agreement BOOLEAN; -- <GA FPI>
375 l_value_basis PO_LINES_ALL.order_type_lookup_code%TYPE;
376 X_amount_received NUMBER;
377 X_amount_billed NUMBER;
378
379 -- <FPJ Retroactive Price START>
380 l_retroactive_update VARCHAR2(30) := 'NEVER';
381 l_archive_mode_std_po PO_DOCUMENT_TYPES.archive_external_revision_code%TYPE;
382 l_encumbrance_on VARCHAR2(1) ;
383 l_current_org_id NUMBER;
384 -- Bug 3231062
385 l_retro_prj_allowed VARCHAR2(1);
386 -- <FPJ Retroactive Price END>
387
388 BEGIN
389 X_progress := '010';
390 --<Encumbrance FPJ>
391 PO_CORE_S.should_display_reserved(
392 p_doc_type => PO_CORE_S.g_doc_type_PO
393 , p_doc_level => PO_CORE_S.g_doc_level_LINE
394 , p_doc_level_id => x_po_line_id
395 , x_display_reserved_flag => x_encumbered_flag
396 );
397
398
399 /* Find out if the shipments are encumbered and also the qty billed /recd */
400
401 BEGIN
402 X_progress := '020';
403 SELECT sum(nvl(quantity_received,0)),
404 sum(nvl(quantity_billed,0)),
405 sum(nvl(amount_received,0)),
406 sum(nvl(amount_billed,0))
407 INTO X_quantity_received,
408 X_quantity_billed,
409 X_amount_received,
410 X_amount_billed
411 FROM po_line_locations
412 WHERE po_line_id = X_po_line_id
413 AND shipment_type in ('STANDARD', 'PLANNED');
414
415 X_Progress := '030';
416 l_is_global_agreement := PO_GA_PVT.is_global_agreement(X_po_header_id);
417
418 X_Progress := '040';
419 if X_type_lookup_code in ('BLANKET', 'PLANNED') then
420 /* Get the corresponding release information .
421 The price_override column WILL always be a
422 NOT NULL value ( although it is not defined
423 as such in the table, The Enter Releases
424 form should ensure that the value is not null.*/
425
426 if ( X_type_lookup_code = 'BLANKET' ) AND
427 ( NOT l_is_global_agreement ) then -- <GA FPI>
428
429 X_Progress := '050';
430 -- <SERVICES FPJ START>
431 -- Added a decode in the SELECT statement to use Amount
432 -- instead of quantity for Fixed Price Services lines
433 -- <SERVICES FPJ END>
434 SELECT sum(nvl(quantity,0) - nvl(quantity_cancelled,0)),
435 sum(decode(quantity,
436 null,
437 (nvl(amount, 0)
438 - nvl(amount_cancelled, 0)),
439 (price_override* (nvl(quantity,0) -
440 nvl(quantity_cancelled,0)))
441 )
442 )
443 INTO X_quantity_released,
444 X_amount_released
445 FROM po_line_locations
446 WHERE po_header_id = X_po_header_id and
447 shipment_type = 'BLANKET'and
448 po_line_id = X_po_line_id;
449
450 -- <GA FPI START>
451 --
452 ELSIF ( l_is_global_agreement ) THEN
453
454 X_Progress := '060';
455 PO_CORE_S.get_ga_line_amount_released( X_po_line_id, -- IN
456 X_po_header_id, -- OUT
457 X_quantity_released, -- OUT
458 X_amount_released ); -- OUT
459 -- <GA FPI END>
460
461 elsif X_type_lookup_code = 'PLANNED' then
462
463
464 X_Progress := '070';
465 SELECT sum(nvl(quantity,0) - nvl(quantity_cancelled,0)),
466 sum(price_override* (nvl(quantity,0) -
467 nvl(quantity_cancelled,0)))
468 INTO X_quantity_released,
469 X_amount_released
470 FROM po_line_locations
471 WHERE po_header_id = X_po_header_id and
472 shipment_type = 'SCHEDULED' and
473 po_line_id = X_po_line_id;
474 end if;
475
476 end if;
477
478 EXCEPTION
479 /* If no shipments found or any database error,
480 ** set fields to default values
481 */
482 WHEN NO_DATA_FOUND THEN
483 X_Progress := '080';
484 X_quantity_received := 0;
485 X_quantity_billed := 0;
486 X_amount_received := 0;
487 X_amount_billed := 0;
488 X_encumbered_flag := 'N';
489 X_quantity_released := 0;
490 X_amount_released := 0;
491 END;
492
493 X_Progress := '100';
494 Select order_type_lookup_code
495 into l_value_basis
496 from po_lines_all
497 where po_line_id = X_po_line_id;
498
499 -- <FPJ Retroactive Price START>
500 X_Progress := '110';
501 l_current_org_id := PO_GA_PVT.get_current_org;
502 X_Progress := '120';
503 l_retroactive_update := PO_RETROACTIVE_PRICING_PVT.Get_Retro_Mode;
504 -- Bug 3565522 : get the archive mode
505 X_Progress := '130';
506 l_archive_mode_std_po := PO_DOCUMENT_ARCHIVE_PVT.get_archive_mode(
507 p_doc_type => 'PO',
508 p_doc_subtype => 'STANDARD');
509 X_Progress := '140';
510 IF PO_CORE_S.is_encumbrance_on( p_doc_type => 'PO',
511 p_org_id => l_current_org_id)
512 THEN
513 l_encumbrance_on := 'Y';
514 ELSE
515 l_encumbrance_on := 'N';
516 END IF;
517 -- Bug 3231062
518 X_Progress := '150';
519 l_retro_prj_allowed := PO_RETROACTIVE_PRICING_PVT.Is_Retro_Project_Allowed(
520 p_std_po_price_change => 'Y',
521 p_po_line_id => X_po_line_id,
522 p_po_line_loc_id => NULL);
523
524 -- <FPJ Retroactive Price END>
525
526 BEGIN
527
528 X_Progress := '160';
529 IF l_value_basis in ('QUANTITY','AMOUNT') THEN
530 X_progress := '170';
531
532 SELECT COUNT(1)
533 INTO X_row_exists
534 FROM po_distributions
535 WHERE po_line_id = X_po_line_id
536 AND (destination_type_code in ('INVENTORY','SHOP FLOOR')
537 OR (destination_type_code = 'EXPENSE'
538 AND (X_expense_accrual_code = 'RECEIPT'
539 OR X_quantity_billed > 0)))
540 AND (X_quantity_received > 0 OR
541 X_quantity_billed > 0)
542 -- <FPJ Retroactive Price START>
543 AND (l_retroactive_update <> 'ALL_RELEASES' OR
544 (l_retroactive_update = 'ALL_RELEASES' AND
545 l_encumbrance_on = 'Y') OR -- Bug 3573266
546 (l_retroactive_update = 'ALL_RELEASES' AND
547 l_archive_mode_std_po <> 'APPROVE' ) OR -- Bug 3565522
548 (l_retroactive_update = 'ALL_RELEASES' AND
549 l_retro_prj_allowed = 'N' )); -- Bug 3231062
550 -- <FPJ Retroactive Price END>
551 ELSE
552 X_progress := '180';
553 -- Bug 3524527 : check for amounts on service lines
554 SELECT COUNT(1)
555 INTO X_row_exists
556 FROM po_distributions
557 WHERE po_line_id = X_po_line_id
558 AND (destination_type_code = 'EXPENSE'
559 AND (X_expense_accrual_code = 'RECEIPT'
560 OR X_amount_billed > 0))
561 AND (X_amount_received > 0 OR
562 X_amount_billed > 0);
563
564 END IF; -- end of qty and amount based lines
565
566 IF X_row_exists > 0 THEN
567 X_prevent_price_update_flag := 'Y';
568 ELSE
569 X_prevent_price_update_flag := 'N';
570 END IF;
571
572 EXCEPTION
573 WHEN NO_DATA_FOUND THEN
574 X_prevent_price_update_flag := 'N';
575
576 END;
577
578
579 /* Find out if this is based on an ONLINE REQ */
580
581 BEGIN
582 X_progress := '200';
583 X_row_exists := 0;
584
585 SELECT COUNT(1)
586 INTO X_row_exists
587 FROM po_distributions pod
588 WHERE pod.po_Line_id = X_po_line_id
589 AND req_distribution_id is not null;
590
591 IF X_row_exists > 0 THEN
592 X_online_req_flag := 'Y';
593 ELSE
594 X_online_req_flag := 'N';
595 END IF;
596
597 EXCEPTION
598 /* If no distributions found, set it to 'N'
599 */
600 WHEN NO_DATA_FOUND THEN
601 X_online_req_flag := 'N';
602 END;
603
604
605
606 EXCEPTION
607
608 WHEN OTHERS THEN
609 -- dbms_output.put_line('In exception');
610 po_message_s.sql_error('PO_LINES_SV4','get_ship_quantity_info',
611 x_progress, sqlcode, sqlerrm);
612 RAISE;
613
614
615 END get_ship_quantity_info;
616
617
618 /*===========================================================================
619
620 PROCEDURE NAME: get_quotation_info ()
621
622 ============================================================================*/
623
624
625 PROCEDURE get_quotation_info (X_from_header_id IN NUMBER,
626 X_from_line_id IN NUMBER,
627 X_quotation_number IN OUT NOCOPY VARCHAR2,
628 X_quotation_line IN OUT NOCOPY NUMBER,
629 X_quotation_type IN OUT NOCOPY VARCHAR2,
630 X_vendor_quotation_number IN OUT NOCOPY VARCHAR2,
631 x_quote_terms_id IN OUT NOCOPY NUMBER,
632 x_quote_ship_via_lookup_code IN OUT NOCOPY VARCHAR2,
633 x_quote_fob_lookup_code IN OUT NOCOPY VARCHAR2,
634 x_quote_freight_terms IN OUT NOCOPY VARCHAR2) IS
635
636 X_progress varchar2(3);
637 X_from_type_lookup_code varchar2(25);
638 X_quote_type_lookup_code varchar2(25);
639
640 BEGIN
641
642 BEGIN
643
644 X_progress := '010';
645 SELECT segment1,
646 type_lookup_code,
647 quote_type_lookup_code,
648 quote_vendor_quote_number,
649 terms_id,
650 ship_via_lookup_code,
651 fob_lookup_code,
652 freight_terms_lookup_code
653 INTO X_quotation_number,
654 X_from_type_lookup_code,
655 X_quote_type_lookup_code,
656 X_vendor_quotation_number,
657 x_quote_terms_id,
658 x_quote_ship_via_lookup_code,
659 x_quote_fob_lookup_code,
660 x_quote_freight_terms
661 FROM po_headers
662 WHERE po_header_id = X_from_header_id
663 AND type_lookup_code = 'QUOTATION';
664
665 X_progress := '020';
666
667 SELECT podt.type_name
668 INTO X_quotation_type
669 FROM po_document_types podt
670 WHERE podt.document_type_code = X_from_type_lookup_code
671 AND podt.document_subtype = X_quote_type_lookup_code;
672
673
674
675 EXCEPTION
676 /* If no record found,set return fields to null */
677
678 WHEN NO_DATA_FOUND THEN
679 X_quotation_number := '';
680 X_quotation_type := '';
681 X_vendor_quotation_number :='';
682 X_quotation_line := '';
683 x_quote_terms_id := '';
684 x_quote_ship_via_lookup_code := '';
685 x_quote_fob_lookup_code := '';
686 x_quote_freight_terms := '';
687
688 END;
689
690
691 IF X_from_type_lookup_code = 'QUOTATION' THEN
692
693 BEGIN
694
695 X_progress := '020';
696
697 SELECT line_num
698 INTO X_quotation_line
699 FROM po_lines
700 WHERE po_line_id = X_from_line_id;
701
702 EXCEPTION
703
704 WHEN NO_DATA_FOUND THEN
705 X_quotation_line := '';
706
707 END;
708 END IF;
709
710
711 EXCEPTION
712
713 WHEN OTHERS THEN
714 -- dbms_output.put_line('In exception');
715 po_message_s.sql_error('get_quotation_info', X_progress, sqlcode);
716 RAISE;
717
718
719 END get_quotation_info;
720
721 /*===========================================================================
722
723 PROCEDURE NAME: get_lookup_code_dsp()
724
725 ===========================================================================*/
726
727
728 procedure get_lookup_code_dsp (X_lookup_type IN VARCHAR2,
729 X_lookup_code IN VARCHAR2,
730 X_displayed_field IN OUT NOCOPY VARCHAR2) is
731
732 X_progress varchar2(3) := '';
733
734 begin
735 X_progress := '010';
736
737 select polc.displayed_field
738 into X_displayed_field
739 from po_lookup_codes polc
740 where polc.lookup_type = X_lookup_type
741 and polc.lookup_code = X_lookup_code ;
742
743 exception
744 when others then
745 po_message_s.sql_error('get_lookup_code_dsp', X_progress, sqlcode);
746 raise;
747
748 end get_lookup_code_dsp;
749
750
751 /*===========================================================================
752
753 PROCEDURE NAME: online_req
754
755 ===========================================================================*/
756
757 FUNCTION online_req(x_po_line_id IN NUMBER) return BOOLEAN
758 IS
759 x_progress VARCHAR2(3) := NULL;
760 x_row_exists NUMBER := 0;
761 x_online_req_flag BOOLEAN := FALSE;
762 BEGIN
763
764 x_progress := '010';
765
766 SELECT COUNT(1)
767 INTO x_row_exists
768 FROM po_distributions pod
769 WHERE pod.po_line_id = X_po_line_id
770 AND req_distribution_id is not null;
771
772 IF x_row_exists > 0 THEN
773 x_online_req_flag := TRUE;
774 END IF;
775
776 return x_online_req_flag;
777
778 EXCEPTION
779 /* If no distributions found, return FALSE */
780 WHEN NO_DATA_FOUND THEN
781 X_online_req_flag := FALSE;
782 return x_online_req_flag;
783 WHEN OTHERS THEN
784 raise;
785 END online_req;
786
787 /*===========================================================================
788
789 PROCEDURE NAME: get_item_id()
790
791 ===========================================================================*/
792
793 /* PROCEDURE get_item_id
794 (X_item_id_record IN OUT rcv_shipment_line_sv.item_id_record_type) is
795
796 BEGIN
797
798 if (X_item_id_record.item_num is not null) then
799
800 select min(inventory_item_id),
801 min(primary_unit_of_measure)
802 into x_item_id_record.item_id,
803 x_item_id_record.primary_unit_of_measure
804 from mtl_item_flexfields
805 where item_number = X_item_id_record.item_num and
806 organization_id = X_item_id_record.to_organization_id;
807
808 if (x_item_id_record.item_id is null) then
809
810 select min(inventory_item_id),
811 min(primary_unit_of_measure)
812 into x_item_id_record.item_id,
813 x_item_id_record.primary_unit_of_measure
814 from mtl_item_flexfields
815 where item_number = X_item_id_record.vendor_item_num and
816 organization_id = X_item_id_record.to_organization_id;
817
818 end if;
819
820 end if;
821
822 if (x_item_id_record.item_id is null) then
823 x_item_id_record.error_record.error_status := 'W';
824 x_item_id_record.error_record.error_message := 'RCV_ITEM_ID';
825 end if;
826
827 exception
828 when others then
829 x_item_id_record.error_record.error_status := 'U';
830
831 END get_item_id; */
832
833
834 /*===========================================================================
835
836 PROCEDURE NAME: get_sub_item_id()
837
838 ===========================================================================*/
839
840 /* PROCEDURE get_sub_item_id
841 (X_sub_item_id_record IN OUT rcv_shipment_line_sv.sub_item_id_record_type) is
842
843 BEGIN
844
845 if (X_sub_item_id_record.substitute_item_num is not null) then
846 select max(inventory_item_id)
847 into x_sub_item_id_record.substitute_item_id
848 from mtl_system_items_kfv
849 where concatenated_segments = X_sub_item_id_record.substitute_item_num;
850 else
851 select max(inventory_item_id)
852 into x_sub_item_id_record.substitute_item_id
853 from mtl_system_items_kfv
854 where concatenated_segments = X_sub_item_id_record.vendor_item_num;
855 end if;
856
857 if (x_sub_item_id_record.substitute_item_id is null) then
858 x_sub_item_id_record.error_record.error_status := 'F';
859 x_sub_item_id_record.error_record.error_message := 'RCV_ITEM_SUB_ID';
860 end if;
861
862 exception
863 when others then
864 x_sub_item_id_record.error_record.error_status := 'U';
865
866 END get_sub_item_id; */
867
868 /*===========================================================================
869
870 PROCEDURE NAME: get_po_line_id()
871
872 ===========================================================================*/
873
874 /* PROCEDURE get_po_line_id
875 (X_po_line_id_record IN OUT rcv_shipment_line_sv.po_line_id_record_type) is
876
877 BEGIN
878
879 select po_line_id, item_id
880 into x_po_line_id_record.po_line_id, X_po_line_id_record.item_id
881 from po_lines
882 where po_header_id = X_po_line_id_record.po_header_id and
883 line_num = X_po_line_id_record.document_line_num;
884
885 if (x_po_line_id_record.po_line_id is null) then
886 x_po_line_id_record.error_record.error_status := 'F';
887 x_po_line_id_record.error_record.error_message := 'RCV_ITEM_PO_LINE_ID';
888 end if;
889
890 exception
891 when others then
892 x_po_line_id_record.error_record.error_status := 'U';
893
894 END get_po_line_id; */
895
896 --togeorge 10/03/2000
897 procedure get_oke_contract_header_info(
898 X_oke_contract_header_id IN NUMBER,
899 X_oke_contract_num IN OUT NOCOPY VARCHAR2
900 ) is
901 X_Progress varchar2(3);
902
903 begin
904
905 X_Progress := '010';
906
907 begin
908
909 select contract_number
910 into X_oke_contract_num
911 from okc_k_headers_b
912 where id = X_oke_contract_header_id;
913
914
915 exception
916 when no_data_found then
917 X_oke_contract_num := to_char(null) ; /* Set the value to null */
918 when others then
919 po_message_s.sql_error('get_oke_contract_header_info', x_progress, sqlcode);
920 raise;
921 end;
922
923 exception
924 when others then
925 po_message_s.sql_error('get_oke_contract_header_info', x_progress, sqlcode);
926 raise;
927 end get_oke_contract_header_info;
928
929 --Bug# 1625462
930 --togeorge 01/30/2001
931 --Now displaying translated values for uom.
932 procedure get_unit_meas_lookup_code_tl(
933 X_unit_meas_lookup_code IN VARCHAR2,
934 X_unit_meas_lookup_code_tl IN OUT NOCOPY VARCHAR2
935 ) is
936 X_Progress varchar2(3);
937
938 begin
939 X_Progress := '010';
940 select mum.unit_of_measure_tl
941 into x_unit_meas_lookup_code_tl
942 from mtl_units_of_measure mum
943 where mum.unit_of_measure = x_unit_meas_lookup_code;
944
945 exception
946 when others then
947 /*
948 ** Bug 1689746
949 ** In the Receiving transactions form you can't query up
950 ** RMA receipts. Commenting out the error and raise so that
951 ** transaction may proceed.
952 po_message_s.sql_error('get_unit_meas_lookup_code_tl', x_progress, sqlcode);
953 raise;
954 */
955 null;
956
957 end get_unit_meas_lookup_code_tl;
958
959 --Bug# 1751180
960 --togeorge 04/27/2001
961 --This procedure would select the translated uom using uom_code since om stores uom_code unlike units_of_measure as po.
962 procedure get_om_uom_tl(
963 X_uom_code IN VARCHAR2,
964 X_unit_meas_lookup_code_tl IN OUT NOCOPY VARCHAR2
965 ) is
966 X_Progress varchar2(3);
967
968 begin
969 X_Progress := '010';
970 select mum.unit_of_measure_tl
971 into x_unit_meas_lookup_code_tl
972 from mtl_units_of_measure mum
973 where mum.uom_code = rtrim(x_uom_code);
974
975 exception
976 when others then
977 null;
978
979 end get_om_uom_tl;
980
981 END PO_LINES_SV4;