1 PACKAGE BODY PO_SERVICES_PVT AS
2 /* $Header: POXVSVCB.pls 115.15 2004/07/06 22:17:30 anhuang noship $ */
3
4 -- Debugging booleans used to bypass logging when turned off
5 g_debug_stmt CONSTANT BOOLEAN := PO_DEBUG.is_debug_stmt_on;
6 g_debug_unexp CONSTANT BOOLEAN := PO_DEBUG.is_debug_unexp_on;
7
8 g_pkg_name CONSTANT VARCHAR2(20) := 'PO_SERVICES_PVT';
9 g_module_prefix CONSTANT VARCHAR2(30) := 'po.plsql.' || g_pkg_name || '.';
10
11 -----------------------------------------------------------------<SERVICES FPJ>
12 -------------------------------------------------------------------------------
13 --Start of Comments
14 --Name: allow_price_override
15 --Pre-reqs:
16 -- None.
17 --Modifies:
18 -- None.
19 --Locks:
20 -- None.
21 --Function:
22 -- Retrieves the Allow Price Override Flag for the given Blanket Line.
23 --Parameters:
24 --IN:
25 --p_po_line_id
26 -- Unique ID of the Blanket Line
27 --Returns:
28 -- BOOLEAN - Allow Price Override Flag of the line
29 --Testing:
30 -- None.
31 --End of Comments
32 -------------------------------------------------------------------------------
33 -------------------------------------------------------------------------------
34 FUNCTION allow_price_override(p_po_line_id NUMBER) RETURN BOOLEAN IS
35
36 l_allow_price_override_flag po_lines_all.allow_price_override_flag%TYPE :=
37 null;
38
39 BEGIN
40
41 SELECT allow_price_override_flag
42 INTO l_allow_price_override_flag
43 FROM po_lines_all
44 WHERE po_line_id = p_po_line_id;
45
46 IF (l_allow_price_override_flag = 'Y') THEN
47 RETURN (TRUE);
48 ELSE
49 RETURN (FALSE);
50 END IF;
51
52 EXCEPTION
53 WHEN NO_DATA_FOUND THEN
54 NULL;
55 WHEN OTHERS THEN
56 PO_MESSAGE_S.sql_error('PO_SERVICES_PVT.allow_price_override',
57 '',
58 sqlcode);
59 RAISE;
60 END allow_price_override;
61
62
63 ------------------------------------------------------------------<BUG 3248161>
64 -------------------------------------------------------------------------------
65 --Start of Comments
66 --Name: get_ap_compatibility_flag
67 --Pre-reqs:
68 -- None.
69 --Modifies:
70 -- None.
71 --Locks:
72 -- None.
73 --Function:
74 -- Determines if AP is on a sufficient Family Pack to support
75 -- Services Procurement. No "Amount"-based matching line types will be
76 -- allowed unless...
77 -- (a) (Oracle) AP is on Family Pack M or higher (11i.AP.M) (FIN.F), or
78 -- (b) (Oracle) AP is not installed (we will allow new Services line types
79 -- with 3rd party "AP" products, but will not support them), or
80 -- (c) (Oracle) AP is custom installed (we will allow new Services line types
81 -- with custom installed AP, but will not support it)
82 --Parameters:
83 -- None.
84 --Returns:
85 -- 'Y' if the current install status of AP is sufficient to support the
86 -- new Services Procurement "Amount"-based matching line types.
87 -- 'N' otherwise.
88 --Testing:
89 -- None.
90 --End of Comments
91 -------------------------------------------------------------------------------
92 -------------------------------------------------------------------------------
93 FUNCTION get_ap_compatibility_flag
94 RETURN VARCHAR2
95 IS
96 l_api_name VARCHAR2(30) := 'get_ap_compatibility_flag';
97 l_log_head VARCHAR2(100) := g_module_prefix || l_api_name;
98 l_progress VARCHAR2(3);
99
100 l_install_status VARCHAR2(1); -- <BUG 3449557>
101 l_ap_family_pack FND_PRODUCT_INSTALLATIONS.patch_level%TYPE;
102 l_result VARCHAR2(1) := 'N';
103
104 BEGIN
105
106 l_progress := '000'; PO_DEBUG.debug_begin(l_log_head);
107
108 l_install_status := PO_CORE_S.get_product_install_status('SQLAP');
109
110 l_progress := '010'; PO_DEBUG.debug_var ( p_log_head => l_log_head
111 , p_progress => l_progress
112 , p_name => 'AP Install Status'
113 , p_value => l_install_status
114 );
115
116 -- <BUG 3449557 START> Only check for the family pack version if AP
117 -- is installed.
118 --
119 IF ( l_install_status = 'I' ) THEN
120
121 AD_VERSION_UTIL.get_product_patch_level
122 ( p_appl_id => 200 -- AP
123 , p_patch_level => l_ap_family_pack
124 );
125
126 l_progress := '020'; PO_DEBUG.debug_var ( p_log_head => l_log_head
127 , p_progress => l_progress
128 , p_name => 'AP Family Pack'
129 , p_value => l_ap_family_pack
130 );
131
132 IF ( l_ap_family_pack >= '11i.AP.M' )
133 THEN
134 l_result := 'Y';
135 ELSE
136 l_result := 'N';
137 END IF;
138
139 l_progress := '030'; PO_DEBUG.debug_var ( p_log_head => l_log_head
140 , p_progress => l_progress
141 , p_name => 'l_result'
142 , p_value => l_result
143 );
144 ELSE
145
146 l_result := 'Y'; -- Else, Oracle AP is not installed,
147 -- return 'Y' for compatibility flag
148 END IF; -- ( l_install_status )
149 --
150 -- <BUG 3449557 END>
151
152 l_progress := '040'; PO_DEBUG.debug_end(l_log_head);
153
154 return (l_result); -- <BUG 3449557>
155
156 EXCEPTION
157
158 WHEN OTHERS THEN
159 PO_DEBUG.debug_exc ( p_log_head => l_log_head
160 , p_progress => l_progress);
161 RAISE;
162
163 END get_ap_compatibility_flag;
164
165
166 -----------------------------------------------------------------<SERVICES FPJ>
167 -------------------------------------------------------------------------------
168 --Start of Comments
169 --Name: get_contractor_status
170 --Pre-reqs:
171 -- None.
172 --Modifies:
173 -- None.
174 --Locks:
175 -- None.
176 --Function:
177 -- Retrieves the Contractor Status for the given Requisition Line.
178 --Parameters:
179 --IN:
180 --p_req_line_id
181 -- Unique ID of the Requisition Line
182 --Returns:
183 -- VARCHAR2 - Contractor Status of the given Req Line
184 --Testing:
185 -- None.
186 --End of Comments
187 -------------------------------------------------------------------------------
188 -------------------------------------------------------------------------------
189 FUNCTION get_contractor_status ( p_req_line_id IN NUMBER )
190 RETURN VARCHAR2
191 IS
192 l_contractor_status PO_REQUISITION_LINES_ALL.contractor_status%TYPE;
193 BEGIN
194
195 SELECT contractor_status
196 INTO l_contractor_status
197 FROM po_requisition_lines_all
198 WHERE requisition_line_id = p_req_line_id;
199
200 return (l_contractor_status);
201
202 EXCEPTION
203
204 WHEN OTHERS THEN
205 return (NULL);
206
207 END get_contractor_status;
208
209
210 -----------------------------------------------------------------<SERVICES FPJ>
211 -------------------------------------------------------------------------------
212 --Start of Comments
213 --Name: get_expense_line
214 --Pre-reqs:
215 -- None.
216 --Modifies:
217 -- None.
218 --Locks:
219 -- None.
220 --Function:
221 -- Retrieves information on the Expense Line corresponding to the input
222 -- Parent Temp Labor Requisition Line. If no Expense Line exists for the
223 -- particular input Line, then return NULL in the OUT parameters.
224 --Parameters:
225 --IN:
226 --p_parent_line_id - Unique Requisition Line ID of the Temp Labor Parent Line
227 --OUT:
228 --x_expense_line_id - Unique Requisition Line ID of the associated Expense Line
229 --x_expense_line_num - Requisition Line Num of the associated Expense Line
230 --Testing:
231 -- None.
232 --End of Comments
233 -------------------------------------------------------------------------------
234 -------------------------------------------------------------------------------
235 PROCEDURE get_expense_line
236 ( p_parent_line_id IN NUMBER
237 , x_expense_line_id OUT NOCOPY NUMBER
238 , x_expense_line_num OUT NOCOPY NUMBER
239 )
240 IS BEGIN
241
242 SELECT requisition_line_id
243 , line_num
244 INTO x_expense_line_id
245 , x_expense_line_num
246 FROM po_requisition_lines_all
247 WHERE labor_req_line_id = p_parent_line_id;
248
249 EXCEPTION
250
251 WHEN NO_DATA_FOUND THEN
252 x_expense_line_id := NULL;
253 x_expense_line_num := NULL;
254
255 WHEN OTHERS THEN
256 PO_MESSAGE_S.sql_error ( 'PO_SERVICES_PVT.GET_EXPENSE_LINE', '000', SQLCODE );
257 RAISE;
258
259 END get_expense_line;
260
261
262 -----------------------------------------------------------------<SERVICES FPJ>
263 -------------------------------------------------------------------------------
264 --Start of Comments
265 --Name: get_job_long_description
266 --Pre-reqs:
267 -- None.
268 --Modifies:
269 -- None.
270 --Locks:
271 -- None.
272 --Function:
273 -- Retrieves the Job Long Description for the given Req Line ID
274 -- Returns NULL if the Requisition Line does not exist.
275 --Parameters:
276 --IN:
277 --p_req_line_id
278 -- Unique ID of the Requisition Line
279 --Returns:
280 -- VARCHAR2 - Job Long Description
281 --Testing:
282 -- None.
283 --End of Comments
284 -------------------------------------------------------------------------------
285 -------------------------------------------------------------------------------
286 FUNCTION get_job_long_description
287 (
288 p_req_line_id IN NUMBER
289 )
290 RETURN VARCHAR2
291 IS
292 l_job_long_description PO_REQUISITION_LINES_ALL.job_long_description%TYPE;
293
294 BEGIN
295
296 SELECT job_long_description
297 INTO l_job_long_description
298 FROM po_requisition_lines_all
299 WHERE requisition_line_id = p_req_line_id;
300
301 return (l_job_long_description);
302
303 EXCEPTION
304
305 WHEN OTHERS THEN
306 return (NULL);
307
308 END get_job_long_description;
309
310
311 -----------------------------------------------------------------<SERVICES FPJ>
312 -------------------------------------------------------------------------------
313 --Start of Comments
314 --Name: get_job_name
315 --Pre-reqs:
316 -- None.
317 --Modifies:
318 -- None.
319 --Locks:
320 -- None.
321 --Function:
322 -- Retrieves the Job Name for the given Job ID.
323 -- Returns NULL if the input Job ID is NULL.
324 -- Throws error if input Job ID is not NULL but does not exist in PER_JOBS.
325 --Parameters:
326 --IN:
327 --p_job_id
328 -- Unique ID of the Job
329 --Returns:
330 -- VARCHAR2 - Job Name
331 --Testing:
332 -- None.
333 --End of Comments
334 -------------------------------------------------------------------------------
335 -------------------------------------------------------------------------------
336 FUNCTION get_job_name ( p_job_id IN NUMBER )
337 RETURN VARCHAR2
338 IS
339 l_job_name PER_JOBS_VL.name%TYPE;
340 BEGIN
341
342 IF ( p_job_id IS NULL )
343 THEN
344 return (NULL);
345 END IF;
346
347 SELECT name
348 INTO l_job_name
349 FROM per_jobs_vl
350 WHERE job_id = p_job_id;
351
352 return (l_job_name);
353
354 EXCEPTION
355
356 WHEN OTHERS THEN
357 PO_MESSAGE_S.sql_error( 'PO_SERVICES_PVT.GET_JOB_NAME','000',SQLCODE );
358 RAISE;
359
360 END get_job_name;
361
362
363 -----------------------------------------------------------------<SERVICES FPJ>
364 -------------------------------------------------------------------------------
365 --Start of Comments
366 --Name: get_labor_req_line
367 --Pre-reqs:
368 -- None.
369 --Modifies:
370 -- None.
371 --Locks:
372 -- None.
373 --Function:
374 -- Retrieves information on the Parent Temp Labor Line corresponding to the
375 -- input Expense Requisition Line. If the input line is not an Expense line,
376 -- returns NULL for the OUT parameters.
377 --Parameters:
378 --IN:
379 --p_expense_line_id - Unique Requisition Line ID of the Expense Line
380 --OUT:
381 --x_parent_line_id - Unique Requisition Line ID of the Parent Temp Labor Line
382 --x_parent_line_num - Requisition Line Num of the Parent Temp Labor Line
383 --Testing:
384 -- None.
385 --End of Comments
386 -------------------------------------------------------------------------------
387 -------------------------------------------------------------------------------
388 PROCEDURE get_labor_req_line
389 ( p_expense_line_id IN NUMBER
390 , x_parent_line_id OUT NOCOPY NUMBER
391 , x_parent_line_num OUT NOCOPY NUMBER
392 )
393 IS BEGIN
394
395 SELECT PRL2.requisition_line_id
396 , PRL2.line_num
397 INTO x_parent_line_id
398 , x_parent_line_num
399 FROM po_requisition_lines_all PRL1 -- Expense Line
400 , po_requisition_lines_all PRL2 -- Parent Line
401 WHERE p_expense_line_id = PRL1.requisition_line_id
402 AND PRL1.labor_req_line_id = PRL2.requisition_line_id;
403
404 EXCEPTION
405
406 WHEN NO_DATA_FOUND THEN
407 x_parent_line_id := NULL;
408 x_parent_line_num := NULL;
409
410 WHEN OTHERS THEN
411 PO_MESSAGE_S.sql_error ( 'PO_SERVICES_PVT.GET_LABOR_REQ_LINE', '000', SQLCODE );
412 RAISE;
413
414 END get_labor_req_line;
415
416
417 -----------------------------------------------------------------<SERVICES FPJ>
418 -------------------------------------------------------------------------------
419 --Start of Comments
420 --Name: is_expense_line
421 --Pre-reqs:
422 -- None.
423 --Modifies:
424 -- None.
425 --Locks:
426 -- None.
427 --Function:
428 -- Determines if a Requisition line is an Expense line
429 -- (i.e. is associated with a parent Temp Labor line).
430 --Parameters:
431 --IN:
432 --p_req_line_id
433 -- Unique ID of the Requisition Line
434 --Returns:
435 -- TRUE if the Requisition line is an Expense line. FALSE otherwise.
436 --Testing:
437 -- None.
438 --End of Comments
439 -------------------------------------------------------------------------------
440 -------------------------------------------------------------------------------
441 FUNCTION is_expense_line
442 (
443 p_req_line_id IN NUMBER
444 )
445 RETURN BOOLEAN
446 IS
447 l_labor_req_line_id PO_REQUISITION_LINES_ALL.labor_req_line_id%TYPE;
448
449 BEGIN
450
451 SELECT labor_req_line_id
452 INTO l_labor_req_line_id
453 FROM po_requisition_lines_all
454 WHERE requisition_line_id = p_req_line_id;
455
456 IF ( l_labor_req_line_id IS NOT NULL )
457 THEN
458 return (TRUE);
459 ELSE
460 return (FALSE);
461 END IF;
462
463 EXCEPTION
464
465 WHEN NO_DATA_FOUND THEN
466 return (FALSE);
467
468 WHEN OTHERS THEN
469 PO_MESSAGE_S.sql_error ( 'PO_SERVICES_PVT.is_expense_line', '000', SQLCODE );
470 RAISE;
471
472 END is_expense_line;
473
474
475 -----------------------------------------------------------------<SERVICES FPJ>
476 -------------------------------------------------------------------------------
477 --Start of Comments
478 --Name: is_rate_based_line
479 --Pre-reqs:
480 -- None.
481 --Modifies:
482 -- None.
483 --Locks:
484 -- None.
485 --Function:
486 -- Determines if a po line is rate based line
487 --Parameters:
488 --IN:
489 --p_po_line_id
490 -- Unique ID of the PO Line
491 --Returns:
492 -- TRUE if the PO line is an rate based line. FALSE otherwise.
493 --Testing:
494 -- None.
495 --End of Comments
496 -------------------------------------------------------------------------------
497 -------------------------------------------------------------------------------
498 FUNCTION is_rate_based_line
499 (
500 p_po_line_id IN NUMBER
501 )
502 RETURN BOOLEAN
503 IS
504 l_value_basis PO_LINES_ALL.order_type_lookup_code%TYPE;
505
506 BEGIN
507
508 IF p_po_line_id is null THEN
509 return (FALSE);
510 END IF;
511
512 SELECT order_type_lookup_code
513 INTO l_value_basis
514 FROM po_lines_all
515 WHERE po_line_id = p_po_line_id;
516
517 IF ( l_value_basis = 'RATE' )
518 THEN
519 return (TRUE);
520 ELSE
521 return (FALSE);
522 END IF;
523
524 EXCEPTION
525
526 WHEN NO_DATA_FOUND THEN
527 return (FALSE);
528
529 WHEN OTHERS THEN
530 PO_MESSAGE_S.sql_error ( 'PO_SERVICES_PVT.is_rate_based_line', '000', SQLCODE );
531 RAISE;
532
533 END is_rate_based_line;
534
535
536 -----------------------------------------------------------------<SERVICES FPJ>
537 -------------------------------------------------------------------------------
538 --Start of Comments
539 --Name: validate_ship_to_org
540 --Pre-reqs:
541 -- None.
542 --Modifies:
543 -- None.
544 --Locks:
545 -- None.
546 --Function:
547 -- Validates the ship-to organization with respect to the specified job. The
548 -- ship-to organization must roll up to the same business group as the job on
549 -- the PO line for temp labor line types.
550 --Parameters:
551 --IN:
552 --p_job_id
553 -- The job ID of the PO line
554 --p_ship_to_org_id
555 -- The ship-to organization ID of the PO shipment
556 --OUT:
557 --x_return_status
558 -- FND_API.g_ret_sts_success - if the procedure completed successfully
559 -- FND_API.g_ret_sts_unexp_error - unexpected error occurred
560 --x_is_valid
561 -- TRUE if p_ship_to_org_id rolls up to the same business group as p_job_id, or
562 -- if p_job_id is NULL.
563 -- FALSE otherwise.
564 --End of Comments
565 -------------------------------------------------------------------------------
566 -------------------------------------------------------------------------------
567 PROCEDURE validate_ship_to_org
568 (
569 x_return_status OUT NOCOPY VARCHAR2,
570 p_job_id IN NUMBER,
571 p_ship_to_org_id IN NUMBER,
572 x_is_valid OUT NOCOPY BOOLEAN
573 )
574 IS
575
576 l_progress VARCHAR2(3);
577 l_valid_flag VARCHAR2(1);
578
579 BEGIN
580 l_progress := '000';
581 x_return_status := FND_API.g_ret_sts_success;
582
583 IF g_debug_stmt THEN
584 PO_DEBUG.debug_stmt
585 (p_log_head => g_module_prefix||'validate_ship_to_org',
586 p_token => 'invoked',
587 p_message => 'job ID: '||p_job_id||' ship-to org ID: '||
588 p_ship_to_org_id);
589 END IF;
590
591 IF (p_job_id IS NOT NULL) THEN
592 l_progress := '010';
593
594 --SQL What: Check that ship-to org and job are in same business group
595 --SQL Why: Ensure ship-to orgs of temp labor lines are valid
596 SELECT 'Y'
597 INTO l_valid_flag
598 FROM per_jobs pj,
599 hr_all_organization_units haou,
600 mtl_parameters mp
601 WHERE pj.job_id = p_job_id
602 AND mp.organization_id = p_ship_to_org_id
603 AND mp.organization_id = haou.organization_id
604 AND haou.business_group_id = pj.business_group_id;
605
606 END IF;
607
608 x_is_valid := TRUE;
609
610 IF g_debug_stmt THEN
611 PO_DEBUG.debug_var
612 (p_log_head => g_module_prefix||'validate_ship_to_org',
613 p_progress => l_progress,
614 p_name => 'x_is_valid',
615 p_value => x_is_valid);
616 END IF;
617
618 EXCEPTION
619 WHEN NO_DATA_FOUND THEN
620 -- x_return_status is still success here because this is not an error
621 x_is_valid := FALSE;
622 IF g_debug_stmt THEN
623 PO_DEBUG.debug_var
624 (p_log_head => g_module_prefix||'validate_ship_to_org',
625 p_progress => l_progress,
626 p_name => 'x_is_valid',
627 p_value => x_is_valid);
628 END IF;
629 WHEN OTHERS THEN
630 x_return_status := FND_API.g_ret_sts_unexp_error;
631 FND_MSG_PUB.add_exc_msg(p_pkg_name => g_pkg_name,
632 p_procedure_name => 'validate_ship_to_org',
633 p_error_text => 'Progress: '||l_progress||
634 ' Error: '||SUBSTRB(SQLERRM,1,215));
635 IF g_debug_unexp THEN
636 PO_DEBUG.debug_exc
637 (p_log_head => g_module_prefix||'validate_ship_to_org',
638 p_progress => l_progress);
639 END IF;
640 END validate_ship_to_org;
641
642 -----------------------------------------------------------------<SERVICES FPJ>
643 -------------------------------------------------------------------------------
644 --Start of Comments
645 --Name: get_po_amounts
646 --Pre-reqs:
647 -- None.
648 --Modifies:
649 -- None.
650 --Locks:
651 -- None.
652 --Function:
653 -- Retrieves total amount billed and received from all shipments for a
654 -- given line
655 --Parameters:
656 --IN:
657 --p_po_line_id - Unique PO Line ID
658 --OUT:
659 --x_amount_received - total amount received
660 --x_amount_billed - total amount billed
661 --Testing:
662 -- None.
663 --End of Comments
664 -------------------------------------------------------------------------------
665 -------------------------------------------------------------------------------
666 PROCEDURE get_po_amounts
667 ( p_po_line_id IN NUMBER
668 , x_amount_received OUT NOCOPY NUMBER
669 , x_amount_billed OUT NOCOPY NUMBER
670 )
671 IS
672 BEGIN
673
674 SELECT sum(nvl(amount_received,0)),
675 sum(nvl(amount_billed,0))
676 INTO x_amount_received,
677 x_amount_billed
678 FROM po_line_locations_all
679 WHERE po_line_id = p_po_line_id
680 AND shipment_type = 'STANDARD';
681
682 EXCEPTION
683
684 WHEN NO_DATA_FOUND THEN
685 x_amount_received := 0;
686 x_amount_billed := 0;
687
688 WHEN OTHERS THEN
689 PO_MESSAGE_S.sql_error ( 'PO_SERVICES_PVT.GET_PO_AMOUNTS', '000', SQLCODE );
690 RAISE;
691
692 END get_po_amounts;
693
694 -- Bug# 3465756: Added the following two new functions
695 -------------------------------------------------------------------------------
696 --Start of Comments
697 --Name: check_po_has_svc_line_with_req
698 --Pre-reqs:
699 -- None.
700 --Modifies:
701 -- None.
702 --Locks:
703 -- None.
704 --Function:
705 -- Check if a given PO contains at least one Services line, i.e. if the value
706 -- basis of any line is any of the following:
707 -- 1. FIXED PRICE
708 -- 2. RATE
709 -- Also checks if the services line has a backing requisition or not.
710 --Parameters:
711 --IN:
712 --p_po_header_id - Unique PO Header ID
713 --OUT:
714 -- None
715 --RETURN:
716 -- BOOLEAN - TRUE, if the PO has at least one Services Line with a backing req,
717 -- FALSE, otherwise.
718 --Testing:
719 -- None.
720 --End of Comments
721 -------------------------------------------------------------------------------
722 FUNCTION check_po_has_svc_line_with_req
723 (
724 p_po_header_id IN NUMBER
725 ) RETURN BOOLEAN
726 IS
727 l_count_services_lines NUMBER;
728 BEGIN
729 IF p_po_header_id is null THEN
730 RETURN FALSE;
731 END IF;
732
733 l_count_services_lines := 0;
734
735 SELECT count(*)
736 INTO l_count_services_lines
737 FROM po_lines_all POL,
738 po_distributions_all POD
739 WHERE POL.po_header_id = p_po_header_id
740 AND POL.order_type_lookup_code IN ('FIXED PRICE', 'RATE')
741 AND POD.po_line_id = POL.po_line_id -- Join between PO Lines and Distributions
742 AND POD.req_distribution_id IS NOT NULL; -- There exists a backing Req
743
744 IF (l_count_services_lines > 0) THEN
745 RETURN TRUE;
746 ELSE
747 RETURN FALSE;
748 END IF;
749 EXCEPTION
750 WHEN NO_DATA_FOUND THEN
751 RETURN FALSE;
752 WHEN OTHERS THEN
753 PO_MESSAGE_S.sql_error('PO_SERVICES_PVT.check_po_has_svc_line_with_req', '000', SQLCODE);
754 RAISE;
755 END check_po_has_svc_line_with_req;
756
757 -------------------------------------------------------------------------------
758 --Start of Comments
759 --Name: is_FPS_po_line_with_req
760 --Pre-reqs:
761 -- None.
762 --Modifies:
763 -- None.
764 --Locks:
765 -- None.
766 --Function:
767 -- Checks if the given PO line is a Fixed Price Services line with a backing
768 -- requisition.
769 --Parameters:
770 --IN:
771 --p_po_line_id - Unique PO Line ID
772 --OUT:
773 -- None
774 --RETURN:
775 -- BOOLEAN - TRUE, if the PO line type is Fixed Price Services and has a backing req
776 -- FALSE, otherwise.
777 --Testing:
778 -- None.
779 --End of Comments
780 -------------------------------------------------------------------------------
781 FUNCTION is_FPS_po_line_with_req
782 (
783 p_po_line_id IN NUMBER
784 ) RETURN BOOLEAN
785 IS
786 l_count NUMBER;
787 BEGIN
788 IF p_po_line_id is null THEN
789 RETURN FALSE;
790 END IF;
791
792 l_count := 0;
793
794 SELECT count(*)
795 INTO l_count
796 FROM po_lines_all POL,
797 po_distributions_all POD
798 WHERE POL.po_line_id = p_po_line_id
799 AND POL.order_type_lookup_code = 'FIXED PRICE'
800 AND POL.purchase_basis = 'SERVICES'
801 AND POD.po_line_id = POL.po_line_id
802 AND POD.req_distribution_id IS NOT NULL; -- There exists a backing Req.
803
804 IF (l_count > 0) THEN
805 RETURN TRUE;
806 ELSE
807 RETURN FALSE;
808 END IF;
809 EXCEPTION
810 WHEN NO_DATA_FOUND THEN
811 RETURN FALSE;
812 WHEN OTHERS THEN
813 PO_MESSAGE_S.sql_error('PO_SERVICES_PVT.is_FPS_po_line_with_req', '000', SQLCODE);
814 RAISE;
815 END is_FPS_po_line_with_req;
816
817 -------------------------------------------------------------------------------
818 --Start of Comments
819 --Name: is_FPS_po_shipment_with_req
820 --Pre-reqs:
821 -- None.
822 --Modifies:
823 -- None.
824 --Locks:
825 -- None.
826 --Function:
827 -- Checks if the line type of a given PO Shipment is Fixed Price Services and
828 -- a backing requisition exists for the given PO Shipment.
829 --Parameters:
830 --IN:
831 --p_po_line_location_id - Unique PO Line Location ID
832 --OUT:
833 -- None
834 --RETURN:
835 -- BOOLEAN - TRUE, if the PO line type for the given Shipment is Fixed Price
836 -- Services and has a backing requisition,
837 -- FALSE, otherwise.
838 --Testing:
839 -- None.
840 --End of Comments
841 -------------------------------------------------------------------------------
842 FUNCTION is_FPS_po_shipment_with_req
843 (
844 p_po_line_location_id IN NUMBER
845 ) RETURN BOOLEAN
846 IS
847 l_count NUMBER;
848 BEGIN
849 IF p_po_line_location_id is null THEN
850 RETURN FALSE;
851 END IF;
852
853 l_count := 0;
854
855 SELECT count(*)
856 INTO l_count
857 FROM po_line_locations_all POLL,
858 po_lines_all POL,
859 po_distributions_all POD
860 WHERE POLL.line_location_id = p_po_line_location_id
861 AND POL.po_line_id = POLL.po_line_id
862 AND POL.order_type_lookup_code = 'FIXED PRICE'
863 AND POL.purchase_basis = 'SERVICES'
864 AND POD.line_location_id = POLL.line_location_id
865 AND POD.req_distribution_id IS NOT NULL; -- There exists a backing Req.
866
867 IF (l_count > 0) THEN
868 RETURN TRUE;
869 ELSE
870 RETURN FALSE;
871 END IF;
872 EXCEPTION
873 WHEN NO_DATA_FOUND THEN
874 RETURN FALSE;
875 WHEN OTHERS THEN
876 PO_MESSAGE_S.sql_error('PO_SERVICES_PVT.is_FPS_po_shipment_with_req', '000', SQLCODE);
877 RAISE;
878 END is_FPS_po_shipment_with_req;
879 -- Bug# 3465756: End
880
881 END PO_SERVICES_PVT;
[Home] [Help]