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