[Home] [Help]
PACKAGE BODY: APPS.PO_PRICE_DIFFERENTIALS_PVT
Source
1 PACKAGE BODY PO_PRICE_DIFFERENTIALS_PVT AS
2 /* $Header: POXVPDFB.pls 120.2 2005/09/02 01:33:45 arudas noship $*/
3
4
5 -----------------------------------------------------------------<SERVICES FPJ>
6 -------------------------------------------------------------------------------
7 --Start of Comments
8 --Name: allows_price_differentials
9 --Pre-reqs:
10 -- None.
11 --Modifies:
12 -- None.
13 --Locks:
14 -- None.
15 --Function:
16 -- Checks the "Allow Price Differentials" flag on the Requisition Line.
17 --Parameters:
18 --IN:
19 --p_req_line_id
20 -- Unique ID of the Requisition Line.
21 --Returns:
22 -- TRUE if the "Allow Price Differentials" flag is set to 'Y'.
23 -- FALSE otherwise.
24 --Testing:
25 -- None.
26 --End of Comments
27 -------------------------------------------------------------------------------
28 -------------------------------------------------------------------------------
29 FUNCTION allows_price_differentials
30 (
31 p_req_line_id IN NUMBER
32 )
33 RETURN BOOLEAN
34 IS
35 l_allow_price_diff_flag PO_REQUISITION_LINES.overtime_allowed_flag%TYPE;
36
37 BEGIN
38
39 SELECT overtime_allowed_flag
40 INTO l_allow_price_diff_flag
41 FROM po_requisition_lines_all
42 WHERE requisition_line_id = p_req_line_id;
43
44 IF ( nvl(l_allow_price_diff_flag,'N') = 'Y' )
45 THEN
46 return (TRUE);
47 ELSE
48 return (FALSE);
49 END IF;
50
51 EXCEPTION
52
53 WHEN OTHERS THEN
54 PO_MESSAGE_S.sql_error ( 'PO_PRICE_DIFFERENTIALS_PVT.allows_price_differentials', '000', SQLCODE );
55 RAISE;
56
57 END allows_price_differentials;
58
59
60 -----------------------------------------------------------------<SERVICES FPJ>
61 -------------------------------------------------------------------------------
62 --Start of Comments
63 --Name: check_unique_price_diff_num
64 --Pre-reqs:
65 -- None.
66 --Modifies:
67 -- None.
68 --Locks:
69 -- None.
70 --Function:
71 -- Checks if the Price Differential Num for the given record is unique
72 -- among its set of Price Differentials.
73 --Parameters:
74 --IN:
75 --p_row_id
76 -- ROWID of record whose Price Differential Num the uniqueness check
77 -- is performed on.
78 --p_entity_type
79 -- Entity Type of the set of Price Differentials.
80 --p_entity_id
81 -- Unique identifier for the Entity Type.
82 --p_price_differential_num
83 -- Value of Price Differential Num to check uniqueness on.
84 --Returns:
85 -- TRUE if the Price Differential Num is unique for the given set of
86 -- Price Differentials. FALSE otherwise.
87 --Testing:
88 -- None.
89 --End of Comments
90 -------------------------------------------------------------------------------
91 -------------------------------------------------------------------------------
92 FUNCTION check_unique_price_diff_num
93 (
94 p_row_id IN ROWID
95 , p_entity_type IN VARCHAR2
96 , p_entity_id IN NUMBER
97 , p_price_differential_num IN NUMBER
98 )
99 RETURN BOOLEAN
100 IS
101 l_num_duplicates NUMBER;
102
103 BEGIN
104
105 SELECT count('# of Price Differentials with same Number')
106 INTO l_num_duplicates
107 FROM po_price_differentials
108 WHERE entity_type = p_entity_type
109 AND entity_id = p_entity_id
110 AND price_differential_num = p_price_differential_num
111 AND ( ( p_row_id IS NULL ) OR ( ROWID <> p_row_id ) );
112
113 IF ( l_num_duplicates >= 1 )
114 THEN
115 return (FALSE);
116 ELSE
117 return (TRUE);
118 END IF;
119
120 EXCEPTION
121
122 WHEN OTHERS THEN
123 PO_MESSAGE_S.sql_error ( 'PO_PRICE_DIFFERENTIALS_PVT.check_unique_price_diff_num', '000', SQLCODE );
124 RAISE;
125
126 END check_unique_price_diff_num;
127
128
129 -----------------------------------------------------------------<SERVICES FPJ>
130 -------------------------------------------------------------------------------
131 --Start of Comments
132 --Name: check_unique_price_type
133 --Pre-reqs:
134 -- None.
135 --Modifies:
136 -- None.
137 --Locks:
138 -- None.
139 --Function:
140 -- Checks if the Price Type for the given record is unique
141 -- among its set of Price Differentials.
142 --Parameters:
143 --IN:
144 --p_row_id
145 -- ROWID of record whose Price Type the uniqueness check
146 -- is performed on.
147 --p_entity_type
148 -- Entity Type of the set of Price Differentials.
149 --p_entity_id
150 -- Unique identifier for the Entity Type.
151 --p_price_type
152 -- Value of Price Type to check uniqueness on.
153 --Returns:
154 -- TRUE if the Price Type is unique for the given set of
155 -- Price Differentials. FALSE otherwise.
156 --Testing:
157 -- None.
158 --End of Comments
159 -------------------------------------------------------------------------------
160 -------------------------------------------------------------------------------
161 FUNCTION check_unique_price_type
162 (
163 p_row_id IN ROWID
164 , p_entity_type IN VARCHAR2
165 , p_entity_id IN NUMBER
166 , p_price_type IN VARCHAR2
167 )
168 RETURN BOOLEAN
169 IS
170 l_num_duplicates NUMBER;
171
172 BEGIN
173
174 SELECT count('# of Price Differentials with same Price Type')
175 INTO l_num_duplicates
176 FROM po_price_differentials
177 WHERE entity_type = p_entity_type
178 AND entity_id = p_entity_id
179 AND price_type = p_price_type
180 AND ( ( p_row_id IS NULL ) OR ( ROWID <> p_row_id ) );
181
182 IF ( l_num_duplicates >= 1 )
183 THEN
184 return (FALSE);
185 ELSE
186 return (TRUE);
187 END IF;
188
189 EXCEPTION
190
191 WHEN OTHERS THEN
192 PO_MESSAGE_S.sql_error ( 'PO_PRICE_DIFFERENTIALS_PVT.check_unique_price_type', '000', SQLCODE );
193 RAISE;
194
195 END check_unique_price_type;
196
197
198 -------------------------------------------------------------------------------
199 --Start of Comments
200 --Name: copy_price_differentials
201 --
202 --Pre-reqs: None
203 --
204 --Modifies: None
205 --
206 --Locks: None
207 --
208 --Function: This procedure copied price differentials from one entity to
209 -- the other
210 --
211 --
212 --Parameters:
213 --IN:
214 -- p_to_entity_id
215 -- Id of the to document entity
216 -- p_to_entity_type
217 -- Type of the to document entity : PO LINE, BLANKET LINE , PRICE BREAK
218 -- p_from_entity_id
219 -- Id of the from document entity
220 -- p_from_entity_type
221 -- Type of the from document entity : PO LINE, BLANKET LINE , PRICE BREAK
222 --
223 --Testing: -
224 --End of Comments
225 -------------------------------------------------------------------------------
226 PROCEDURE copy_price_differentials
227 (
228 p_from_entity_type IN VARCHAR2
229 , p_from_entity_id IN NUMBER
230 , p_to_entity_type IN VARCHAR2
231 , p_to_entity_id IN NUMBER
232 )
233 IS
234 BEGIN
235
236 -- Inserts valid price differentials from the from entity to the to entity
237
238 INSERT INTO po_price_differentials
239 ( price_differential_id
240 , price_differential_num
241 , entity_type
242 , entity_id
243 , price_type
244 , multiplier
245 , max_multiplier
246 , min_multiplier
247 , enabled_flag
248 , last_update_date
249 , last_updated_by
250 , last_update_login
251 , creation_date
252 , created_by
253 )
254 SELECT PO_PRICE_DIFFERENTIALS_S.NEXTVAL
255 , PD.price_differential_num
256 , p_to_entity_type
257 , p_to_entity_id
258 , PD.price_type
259 , PD.multiplier
260 , PD.max_multiplier
261 , PD.min_multiplier
262 , PD.enabled_flag
263 , SYSDATE
264 , fnd_global.user_id
265 , fnd_global.login_id
266 , SYSDATE
267 , fnd_global.user_id
268 FROM po_price_differentials PD
269 WHERE PD.entity_type = p_from_entity_type
270 AND PD.entity_id = p_from_entity_id
271 AND nvl(PD.enabled_flag,'Y') = 'Y';
272
273 EXCEPTION
274
275 WHEN OTHERS THEN
276 PO_MESSAGE_S.sql_error ( 'PO_PRICE_DIFFERENTIALS_PVT.copy_price_differentials', '000', SQLCODE );
277 RAISE;
278
279 END;
280
281
282 -----------------------------------------------------------------<SERVICES FPJ>
283 -------------------------------------------------------------------------------
284 --Start of Comments
285 --Name: create_from_interface
286 --Pre-reqs:
287 -- The Parent Lines of the Price Differentials must already be
288 -- inserted into their respective base tables.
289 --Modifies:
290 -- PO_PRICE_DIFFERENTIALS
291 --Locks:
292 -- None.
293 --Function:
294 -- Copies from PO_PRICE_DIFF_INTERFACE -> PO_PRICE_DIFFERENTIALS
295 --Parameters:
296 --IN:
297 --p_entity_id
298 -- Entity ID to which the Price Differentials belong
299 --p_interface_line_id
300 -- interface_line_id to which current batch of Price Diff's will belong
301 --Testing:
302 -- None.
303 --End of Comments
304 -------------------------------------------------------------------------------
305 -------------------------------------------------------------------------------
306 PROCEDURE create_from_interface
307 (
308 p_entity_id IN NUMBER
309 , p_interface_line_id IN NUMBER
310 )
311 IS
312 BEGIN
313
314 INSERT INTO po_price_differentials
315 ( price_differential_id
316 , price_differential_num
317 , entity_type
318 , entity_id
319 , price_type
320 , enabled_flag
321 , min_multiplier
322 , max_multiplier
323 , multiplier
324 , last_update_date
325 , last_updated_by
326 , last_update_login
327 , creation_date
328 , created_by
329 )
330 SELECT PO_PRICE_DIFFERENTIALS_S.nextval
331 , PDI.price_differential_num
332 , PDI.entity_type
333 , p_entity_id
334 , PDI.price_type
335 , PDI.enabled_flag
336 , PDI.min_multiplier
337 , PDI.max_multiplier
338 , PDI.multiplier
339 , nvl(PDI.last_update_date, sysdate)
340 , nvl(PDI.last_updated_by, FND_GLOBAL.user_id)
341 , nvl(PDI.last_update_login, FND_GLOBAL.login_id)
342 , nvl(PDI.creation_date, sysdate)
343 , nvl(PDI.created_by, FND_GLOBAL.user_id)
344 FROM po_price_diff_interface PDI
345 WHERE PDI.interface_line_id = p_interface_line_id
346 AND nvl(process_status,'ACCEPTED') = 'ACCEPTED';
347
348 EXCEPTION
349
350 WHEN OTHERS THEN
351 PO_MESSAGE_S.sql_error ( 'PO_PRICE_DIFFERENTIALS_PVT.CREATE_FROM_INTERFACE', '000', SQLCODE );
352 RAISE;
353
354 END create_from_interface;
355
356
357 -----------------------------------------------------------------<SERVICES FPJ>
358 -------------------------------------------------------------------------------
359 --Start of Comments
360 --Name: default_price_differentials
361 --Pre-reqs:
362 -- None.
363 --Modifies:
364 -- None.
365 --Locks:
366 -- None.
367 --Function:
368 -- Defaults Price Differentials from a GA Line/Price Break to a PO Line.
369 -- This procedure differs from the COPY_PRICE_DIFFERENTIALS procedure in
370 -- that the GA's MIN_MULTIPLIER is carried over as the MULTIPLIER on the PO.
371 --Parameters:
372 --IN:
373 --p_from_entity_type
374 -- Entity Type of Price Differentials from which to copy.
375 -- (Must be either 'BLANKET LINE' or 'PRICE BREAK').
376 --p_from_entity_id
377 -- Entity ID of Price Differentials from which to copy.
378 --p_to_entity_type
379 -- Entity Type of the newly copied Price Differentials.
380 -- (Must be 'PO LINE').
381 --p_to_entity_id
382 -- Entity ID of the newly copied Price Differentials.
383 --Testing:
384 -- None.
385 --End of Comments
386 -------------------------------------------------------------------------------
387 -------------------------------------------------------------------------------
388 PROCEDURE default_price_differentials
389 (
390 p_from_entity_type IN VARCHAR2
391 , p_from_entity_id IN NUMBER
392 , p_to_entity_type IN VARCHAR2
393 , p_to_entity_id IN NUMBER
394 )
395 IS
396 BEGIN
397
398 INSERT INTO po_price_differentials
399 ( price_differential_id
400 , price_differential_num
401 , entity_type
402 , entity_id
403 , price_type
404 , multiplier
405 , max_multiplier
406 , min_multiplier
407 , enabled_flag
408 , last_update_date
409 , last_updated_by
410 , last_update_login
411 , creation_date
412 , created_by
413 )
414 SELECT PO_PRICE_DIFFERENTIALS_S.NEXTVAL
415 , PD.price_differential_num
416 , p_to_entity_type
417 , p_to_entity_id
418 , PD.price_type
419 , PD.min_multiplier --> multiplier
420 , NULL --> min_multiplier
421 , NULL --> max_multiplier
422 , PD.enabled_flag
423 , SYSDATE
424 , fnd_global.user_id
425 , fnd_global.login_id
426 , SYSDATE
427 , fnd_global.user_id
428 FROM po_price_differentials PD
429 WHERE PD.entity_type = p_from_entity_type
430 AND PD.entity_id = p_from_entity_id
431 AND nvl(PD.enabled_flag,'Y') = 'Y';
432
433 EXCEPTION
434
435 WHEN OTHERS THEN
436 PO_MESSAGE_S.sql_error ( 'PO_PRICE_DIFFERENTIALS_PVT.default_price_differentials', '000', SQLCODE );
437 RAISE;
438
439 END default_price_differentials;
440
441
442 -----------------------------------------------------------------<SERVICES FPJ>
443 -------------------------------------------------------------------------------
444 --Start of Comments
445 --Name: delete_price_differentials
446 --Pre-reqs:
447 -- None.
448 --Modifies:
449 -- PO_PRICE_DIFFERENTIALS
450 --Locks:
451 -- None.
452 --Function:
453 -- Deletes the set of Price Differentials specified by the Entity Type/ID.
454 --Parameters:
455 --IN:
456 --p_entity_type
457 -- Entity Type of Price Differential
458 -- ['REQ LINE','PO LINE','BLANKET LINE','PRICE BREAK']
459 --p_entity_id
460 -- Unique ID of the Line or Price Break
461 --Testing:
462 -- None.
463 --End of Comments
464 -------------------------------------------------------------------------------
465 -------------------------------------------------------------------------------
466 PROCEDURE delete_price_differentials
467 (
468 p_entity_type IN VARCHAR2
469 , p_entity_id IN VARCHAR2
470 )
471 IS
472 BEGIN
473
474 DELETE FROM po_price_differentials
475 WHERE entity_type = p_entity_type
476 AND entity_id = p_entity_id;
477
478 EXCEPTION
479
480 WHEN OTHERS THEN
481 PO_MESSAGE_S.sql_error('PO_PRICE_DIFFERENTIALS_PVT.delete_price_differentials','000',sqlcode);
482 RAISE;
483
484 END delete_price_differentials;
485
486
487 -----------------------------------------------------------------<SERVICES FPJ>
488 -------------------------------------------------------------------------------
489 --Start of Comments
490 --Name: get_max_price_diff_num
491 --Pre-reqs:
492 -- None.
493 --Modifies:
494 -- None.
495 --Locks:
496 -- None.
497 --Function:
498 -- Gets the maximum Price Differential Num for a set of Price Differentials
499 -- ( i.e. Price Differentials belonging to the same Entity Type/ID )
500 --Parameters:
501 --IN:
502 --p_entity_type
503 -- Entity Type of Price Differential
504 -- ['REQ LINE','PO LINE','BLANKET LINE','PRICE BREAK']
505 --p_entity_id
506 -- Unique ID of the Line or Price Break
507 --Returns:
508 -- NUMBER representing the current maximum Price Differential Num.
509 --Testing:
510 -- None.
511 --End of Comments
512 -------------------------------------------------------------------------------
513 -------------------------------------------------------------------------------
514 FUNCTION get_max_price_diff_num
515 (
516 p_entity_type IN VARCHAR2
517 , p_entity_id IN NUMBER
518 )
519 RETURN NUMBER
520 IS
521 x_max PO_PRICE_DIFFERENTIALS.price_differential_num%TYPE;
522
523 BEGIN
524
525 SELECT nvl( max(price_differential_num), 0 )
526 INTO x_max
527 FROM po_price_differentials
528 WHERE entity_type = p_entity_type
529 AND entity_id = p_entity_id;
530
531 return (x_max);
532
533 EXCEPTION
534
535 WHEN OTHERS THEN
536 PO_MESSAGE_S.sql_error('PO_PRICE_DIFFERENTIALS_PVT.get_max_price_diff_num','000',SQLCODE);
537 RAISE;
538
539 END get_max_price_diff_num;
540
541
542 -----------------------------------------------------------------<SERVICES FPJ>
543 -------------------------------------------------------------------------------
544 --Start of Comments
545 --Name: get_min_max_multiplier
546 --Pre-reqs:
547 -- None.
548 --Modifies:
549 -- None.
550 --Locks:
551 -- None.
552 --Function:
553 -- Gets the Minimum and Maximum Multiplier values for a given
554 -- Price Differential.
555 --Parameters:
556 --IN:
557 --p_entity_type
558 -- Entity Type of Price Differential
559 -- ['REQ LINE','PO LINE','BLANKET LINE','PRICE BREAK']
560 --p_entity_id
561 -- Unique ID of the Line or Price Break
562 --p_price_type
563 -- Price Differential Price Type
564 --OUT:
565 --x_min_multiplier
566 -- Minimum Multiplier
567 --x_max_multiplier
568 -- Maximum Multiplier
569 --Testing:
570 -- None.
571 --End of Comments
572 -------------------------------------------------------------------------------
573 -------------------------------------------------------------------------------
574 PROCEDURE get_min_max_multiplier
575 (
576 p_entity_type IN VARCHAR2
577 , p_entity_id IN NUMBER
578 , p_price_type IN VARCHAR2
579 , x_min_multiplier OUT NOCOPY NUMBER
580 , x_max_multiplier OUT NOCOPY NUMBER
581 )
582 IS
583 BEGIN
584
585 SELECT min_multiplier
586 , max_multiplier
587 INTO x_min_multiplier
588 , x_max_multiplier
589 FROM po_price_differentials
590 WHERE entity_type = p_entity_type
591 AND entity_id = p_entity_id
592 AND price_type = p_price_type;
593
594 EXCEPTION
595
596 WHEN OTHERS THEN
597 x_min_multiplier := NULL;
598 x_max_multiplier := NULL;
599
600 END get_min_max_multiplier;
601
602
603 -----------------------------------------------------------------<SERVICES FPJ>
604 -------------------------------------------------------------------------------
605 --Start of Comments
606 --Name: get_context
607 --Pre-reqs:
608 -- None.
609 --Modifies:
610 -- None.
611 --Locks:
612 -- None.
613 --Function:
614 -- Gets the Line Num, Price Break Num, Job Name and Description for a
615 -- particular Entity Type/ID.
616 --Parameters:
617 --IN:
618 --p_entity_type
619 -- Entity Type to which the Price Differentials will belong
620 -- ( Note: Does not currently support 'REQ LINE' Entity Types. )
621 --p_entity_id
622 -- Unique ID for the given Entity Type.
623 --OUT:
624 --x_line_num
625 -- Line Num of the Standard PO or Global Agreement Line
626 --x_price_break_num
627 -- Price Break Num (only applicable to Global Agreement Price Breaks)
628 --x_job_name
629 -- Job Name on the Standard PO or Global Agreement Line
630 --x_job_description
631 -- Job Description on the Standard PO or Global Agreement Line
632 --Testing:
633 -- None.
634 --End of Comments
635 -------------------------------------------------------------------------------
636 -------------------------------------------------------------------------------
637 PROCEDURE get_context
638 ( p_entity_type IN VARCHAR2
639 , p_entity_id IN NUMBER
640 , x_line_num OUT NOCOPY NUMBER
641 , x_price_break_num OUT NOCOPY NUMBER
642 , x_job_name OUT NOCOPY VARCHAR2
643 , x_job_description OUT NOCOPY VARCHAR2
644 )
645 IS
646 BEGIN
647
648 IF ( p_entity_type = 'PRICE BREAK' ) THEN
649
650 SELECT POL.line_num
651 , POLL.shipment_num
652 , PJ.name
653 , POJA.job_description
654 INTO x_line_num
655 , x_price_break_num
656 , x_job_name
657 , x_job_description
658 FROM po_lines_all POL
659 , po_line_locations_all POLL
660 , per_jobs_vl PJ
661 , po_job_associations POJA
662 WHERE p_entity_id = POLL.line_location_id
663 AND POLL.po_line_id = POL.po_line_id
664 AND POL.job_id = PJ.job_id
665 AND PJ.job_id = POJA.job_id;
666
667 ELSE -- ( p_entity_type IN ('PO LINE','BLANKET LINE') )
668
669 SELECT POL.line_num
670 , NULL
671 , PJ.name
672 , POJA.job_description
673 INTO x_line_num
674 , x_price_break_num
675 , x_job_name
676 , x_job_description
677 FROM po_lines_all POL
678 , per_jobs_vl PJ
679 , po_job_associations POJA
680 WHERE p_entity_id = POL.po_line_id
681 AND POL.job_id = PJ.job_id
682 AND PJ.job_id = POJA.job_id;
683
684 END IF;
685
686 EXCEPTION
687
688 WHEN OTHERS THEN
689 PO_MESSAGE_S.sql_error ( 'PO_PRICE_DIFFERENTIALS_PVT.GET_CONTEXT', '000', SQLCODE );
690 RAISE;
691
692 END get_context;
693
694
695 -----------------------------------------------------------------<SERVICES FPJ>
696 -------------------------------------------------------------------------------
697 --Start of Comments
698 --Name: has_price_differentials
699 --Pre-reqs:
700 -- None.
701 --Modifies:
702 -- None.
703 --Locks:
704 -- None.
705 --Function:
706 -- Determines if Price Differentials exist for a given Entity Type/ID.
707 --Parameters:
708 --IN:
709 --p_entity_type
710 -- Entity Type of Price Differential
711 -- ['REQ LINE','PO LINE','BLANKET LINE','PRICE BREAK']
712 --p_entity_id
713 -- Unique ID of the Line or Price Break
714 --Returns:
715 --BOOLEAN - TRUE if Price Differentials exist for the given Entity Type/ID.
716 -- FALSE otherwise.
717 --Testing:
718 -- None.
719 --End of Comments
720 -------------------------------------------------------------------------------
721 -------------------------------------------------------------------------------
722 FUNCTION has_price_differentials
723 (
724 p_entity_type IN VARCHAR2
725 , p_entity_id IN NUMBER
726 )
727 RETURN BOOLEAN
728 IS
729 CURSOR l_price_differentials_csr IS SELECT 'Price Differential'
730 FROM po_price_differentials
731 WHERE entity_type = p_entity_type
732 AND entity_id = p_entity_id
733 AND enabled_flag = 'Y';
734
735 l_dummy l_price_differentials_csr%ROWTYPE;
736 l_has_price_differentials BOOLEAN;
737
738 BEGIN
739
740 OPEN l_price_differentials_csr;
741 FETCH l_price_differentials_csr INTO l_dummy;
742 l_has_price_differentials := l_price_differentials_csr%FOUND;
743 CLOSE l_price_differentials_csr;
744
745 return (l_has_price_differentials);
746
747 END has_price_differentials;
748
749
750 -----------------------------------------------------------------<SERVICES FPJ>
751 -------------------------------------------------------------------------------
752 --Start of Comments
753 --Name: is_price_type_enabled
754 --Pre-reqs:
755 -- None.
756 --Modifies:
757 -- None.
758 --Locks:
759 -- None.
760 --Function:
761 -- Determines if a particular Price Type exists and is enabled for the
762 -- set of Price Differentials determined by the given Entity Type/ID.
763 --Parameters:
764 --IN:
765 --p_price_type
766 -- Price Type to check for.
767 --p_entity_type
768 -- Entity Type of Price Differential
769 --p_entity_id
770 -- Unique ID of the Line or Price Break
771 --Returns:
772 -- TRUE if the Price Type exists and is enabled for the given Entity Type/ID
773 -- Price Differentials. FALSE otherwise.
774 --Testing:
775 -- None.
776 --End of Comments
777 -------------------------------------------------------------------------------
778 -------------------------------------------------------------------------------
779 FUNCTION is_price_type_enabled
780 (
781 p_price_type IN VARCHAR2
782 , p_entity_type IN VARCHAR2
783 , p_entity_id IN NUMBER
784 )
785 RETURN BOOLEAN
786 IS
787 CURSOR l_price_type_csr IS SELECT 'Price Type exists'
788 FROM po_price_differentials
789 WHERE entity_type = p_entity_type
790 AND entity_id = p_entity_id
791 AND price_type = p_price_type
792 AND enabled_flag = 'Y';
793
794 l_dummy l_price_type_csr%ROWTYPE;
795 l_is_price_type_enabled BOOLEAN;
796
797 BEGIN
798
799 OPEN l_price_type_csr;
800 FETCH l_price_type_csr INTO l_dummy;
801 l_is_price_type_enabled := l_price_type_csr%FOUND;
802 CLOSE l_price_type_csr;
803
804 return (l_is_price_type_enabled);
805
806 END is_price_type_enabled;
807
808
809 -----------------------------------------------------------------<SERVICES FPJ>
810 -------------------------------------------------------------------------------
811 --Start of Comments
812 --Name: setup_interface_table
813 --Pre-reqs:
814 -- The Parent Lines of the Price Differentials must already be
815 -- inserted into their respective Interface tables.
816 --Modifies:
817 -- PO_PRICE_DIFF_INTERFACE
818 --Locks:
819 -- None.
820 --Function:
821 -- Copies from PO_PRICE_DIFFERENTIALS -> PO_PRICE_DIFF_INTERFACE
822 -- Fills up the Price Differentials Interface table based on the following
823 -- order of precedence:
824 -- 1) The Req Line's Price Differentials if the Req Line has its
825 -- Resource Status as 'COMPLETE'
826 -- 2) The Blanket Price Break's Price Differentials if the Pricing API
827 -- returned a Price Break for the new Standard PO Line and the
828 -- "Allow Price Differentials" flag is set on the Req Line.
829 -- 3) The Blanket Line Price Differentials if the Req Line references one and
830 -- the "Allow Price Differentials" flag is set on the Req Line.
831 --Parameters:
832 --IN:
833 --p_entity_type
834 -- Entity Type to which the Price Differentials will belong
835 -- (NOTE: the procedure currently only supports the Entity Type of 'PO LINE')
836 --p_interface_header_id
837 -- interface_header_id to which current batch of Price Diff's will belong
838 --p_interface_line_id
839 -- interface_line_id to which current batch of Price Diff's will belong
840 --p_req_line_id
841 -- Req Line ID from which is being Autocreated
842 --p_from_line_Id
843 -- Blanket Line ID which the Req Line references (may be NULL)
844 --p_price_break_id
845 -- Blanket Price Break which was returned by the Pricing API when
846 -- pricing the PO Line (may be NULL)
847 --Testing:
848 -- None.
849 --End of Comments
850 -------------------------------------------------------------------------------
851 -------------------------------------------------------------------------------
852 PROCEDURE setup_interface_table
853 (
854 p_entity_type IN VARCHAR2
855 , p_interface_header_id IN NUMBER
856 , p_interface_line_id IN NUMBER
857 , p_req_line_id IN NUMBER
858 , p_from_line_id IN NUMBER
859 , p_price_break_id IN NUMBER
860 )
861 IS
862 l_source_entity_type PO_PRICE_DIFFERENTIALS.entity_type%TYPE;
863 l_source_entity_id PO_PRICE_DIFFERENTIALS.entity_id%TYPE;
864 l_user_id PO_PRICE_DIFFERENTIALS.created_by%TYPE :=
865 to_number(FND_PROFILE.value('user_id'));
866 BEGIN
867
868 -- Determine where to get the Price Differentials from.
869
870 IF ( PO_SERVICES_PVT.get_contractor_status(p_req_line_id) = 'ASSIGNED' ) THEN
871
872 l_source_entity_type := 'REQ LINE';
873 l_source_entity_id := p_req_line_id;
874
875 ELSIF ( allows_price_differentials(p_req_line_id) ) THEN
876
877 IF ( p_price_break_id IS NOT NULL ) THEN
878
879 l_source_entity_type := 'PRICE BREAK';
880 l_source_entity_id := p_price_break_id;
881
882 ELSIF ( p_from_line_id IS NOT NULL ) THEN
883
884 l_source_entity_type := 'BLANKET LINE';
885 l_source_entity_id := p_from_line_id;
886
887 END IF;
888
889 END IF;
890
891 -- Insert into Interface Table
892
893 INSERT INTO po_price_diff_interface
894 (
895 price_diff_interface_id
896 , price_differential_num
897 , interface_header_id
898 , interface_line_id
899 , entity_type
900 , price_type
901 , min_multiplier
902 , max_multiplier
903 , multiplier
904 , enabled_flag
905 , process_status
906 , last_update_date
907 , last_updated_by
908 , last_update_login
909 , creation_date
910 , created_by
911 )
912 SELECT PO_PRICE_DIFF_INTERFACE_S.nextval
913 , PD.price_differential_num
914 , p_interface_header_id
915 , p_interface_line_id
916 , p_entity_type
917 , PD.price_type
918 , NULL
919 , NULL
920 , decode ( PD.entity_type
921 , 'REQ LINE' , PD.multiplier
922 , 'PO LINE' , PD.multiplier
923 , 'PRICE BREAK' , PD.min_multiplier
924 , 'BLANKET LINE' , PD.min_multiplier
925 )
926 , PD.enabled_flag
927 , 'ACCEPTED'
928 , sysdate
929 , l_user_id
930 , l_user_id
931 , sysdate
932 , l_user_id
933 FROM po_price_differentials PD
934 WHERE PD.entity_type = l_source_entity_type
935 AND PD.entity_id = l_source_entity_id
936 AND nvl(PD.enabled_flag,'N') = 'Y';
937
938 EXCEPTION
939
940 WHEN OTHERS THEN
941 PO_MESSAGE_S.sql_error ( 'PO_PRICE_DIFFERENTIALS_PVT.SETUP_INTERFACE_TABLE', '000', SQLCODE );
942 RAISE;
943
944 END setup_interface_table;
945
946 --<SERVICES FPJ START>
947 ------------------------------------------------------------------------
948 --Start of Comments
949 --Name: validate_price_differentials
950 --Pre-reqs:
951 -- None
952 --Modifies:
953 -- PO_PRICE_DIFF_INTERFACE
954 --Locks:
955 -- None
956 --Function:
957 -- 1) Performs validations on the price differentials data in the
958 -- PO_PRICE_DIFF_INTERFACE table.
959 -- a) Price Differential has a valid interface header ID.
960 -- b) Price Differential has a valid Price Type that is seeded
961 -- in the PO_PRICE_TYPE lookup table.
962 -- c) No multiple price differentials records of the same type
963 -- for a line or price break.
964 -- d) Price Differential is tied to a valid entity type.
965 -- e) For Blankets, minimum multiplier is mandatory and maximum
966 -- multiplier is optional. If the maximum multiplier is specified,
967 -- it has to be greater than the minimum multiplier.
968 -- f) Multiplier column should be null for blankets.
969 -- g) For Standard PO's, only a single value for the multiplier may
970 -- be specified.
971 -- h) Min/Max multiplier columns should be null for Standard PO's.
972 -- 2) Generates unique price_differential_num's
973 -- 3) Fill in the entity type if it is null.
974 -- 4) If an error occurs during the validation, sets
975 -- the process_status for those rows to 'REJECTED'.
976 --Parameters:
977 --IN:
978 --p_interface_header_id
979 -- interface_header_id in the interface table
980 --p_interface_line_id
981 -- interface_line_id in the interface table
982 --p_entity_type
983 -- The type of the entity this price differential is tied to.
984 -- ['PO LINE','BLANKET LINE','PRICE BREAK']
985 --p_entity_id
986 -- Unique ID of the Line or Price Break
987 --p_header_processable_flag
988 --
989 --Testing:
990 -- None
991 --End of Comments
992 ------------------------------------------------------------------------
993
994 PROCEDURE validate_price_differentials(
995 p_interface_header_id IN NUMBER,
996 p_interface_line_id IN NUMBER,
997 p_entity_type IN VARCHAR2,
998 p_entity_id IN NUMBER,
999 p_header_processable_flag IN VARCHAR2
1000
1001 )
1002 IS
1003
1004 CURSOR l_price_diff_csr IS
1005 SELECT *
1006 FROM PO_PRICE_DIFF_INTERFACE
1007 WHERE interface_line_id = p_interface_line_id;
1008
1009 l_price_diff_record l_price_diff_csr%ROWTYPE;
1010 l_price_diff_num PO_PRICE_DIFFERENTIALS.price_differential_num%TYPE;
1011 l_count NUMBER;
1012 l_error_flag VARCHAR2(1);
1013 l_progress VARCHAR2(3) := '000';
1014 l_header_processable_flag VARCHAR2(1);
1015
1016 Begin
1017
1018 l_header_processable_flag := p_header_processable_flag;
1019 l_price_diff_num := PO_PRICE_DIFFERENTIALS_PVT.get_max_price_diff_num (
1020 p_entity_type => p_entity_type,
1021 p_entity_id => p_entity_id);
1022
1023 OPEN l_price_diff_csr;
1024 LOOP
1025 FETCH l_price_diff_csr INTO l_price_diff_record;
1026 EXIT WHEN l_price_diff_csr%NOTFOUND;
1027
1028 l_error_flag := 'N';
1029
1030 l_progress := '010';
1031
1032 IF l_price_diff_record.interface_header_id IS NULL THEN
1033 l_error_flag := 'Y';
1034
1035 PO_INTERFACE_ERRORS_SV1.handle_interface_errors(
1036 X_interface_type => 'PO_DOCS_OPEN_INTERFACE',
1037 X_error_type => 'WARNING',
1038 X_batch_id => NULL,
1039 X_interface_header_id => p_interface_header_id,
1040 X_interface_line_id => p_interface_line_id,
1041 X_error_message_name => 'PO_PDOI_COLUMN_NOT_NULL',
1042 X_table_name => 'PO_PRICE_DIFF_INTERFACE',
1043 X_column_name => 'INTERFACE_HEADER_ID',
1044 X_TokenName1 => 'COLUMN_NAME',
1045 X_TokenName2 => NULL,
1046 X_TokenName3 => NULL,
1047 X_TokenName4 => NULL,
1048 X_TokenName5 => NULL,
1049 X_TokenName6 => NULL,
1050 X_TokenValue1 => 'INTERFACE_HEADER_ID',
1051 X_TokenValue2 => NULL,
1052 X_TokenValue3 => NULL,
1053 X_TokenValue4 => NULL,
1054 X_TokenValue5 => NULL,
1055 X_TokenValue6 => NULL,
1056 X_header_processable_flag => l_header_processable_flag,
1057 X_interface_dist_id => NULL);
1058
1059 ELSIF (l_price_diff_record.interface_header_id
1060 <> p_interface_header_id) THEN
1061 l_error_flag := 'Y';
1062 PO_INTERFACE_ERRORS_SV1.handle_interface_errors(
1063 X_interface_type => 'PO_DOCS_OPEN_INTERFACE',
1064 X_Error_type => 'WARNING',
1065 X_Batch_id => NULL,
1066 X_Interface_Header_Id => p_interface_header_id,
1067 X_Interface_Line_id => p_interface_line_id,
1068 X_Error_message_name => 'PO_PDOI_SVC_INVALID_INT_HDR_ID',
1069 X_Table_name => 'PO_PRICE_DIFF_INTERFACE',
1070 X_Column_name => 'INTERFACE_HEADER_ID',
1071 X_TokenName1 => NULL,
1072 X_TokenName2 => NULL,
1073 X_TokenName3 => NULL,
1074 X_TokenName4 => NULL,
1075 X_TokenName5 => NULL,
1076 X_TokenName6 => NULL,
1077 X_TokenValue1 => NULL,
1078 X_TokenValue2 => NULL,
1079 X_TokenValue3 => NULL,
1080 X_TokenValue4 => NULL,
1081 X_TokenValue5 => NULL,
1082 X_TokenValue6 => NULL,
1083 X_header_processable_flag => l_header_processable_flag,
1084 X_Interface_Dist_Id => NULL);
1085 END IF;
1086
1087 l_progress := '020';
1088
1089 IF l_price_diff_record.price_type IS NULL THEN
1090 l_error_flag := 'Y';
1091
1092 PO_INTERFACE_ERRORS_SV1.handle_interface_errors(
1093 X_interface_type => 'PO_DOCS_OPEN_INTERFACE',
1094 X_Error_type => 'WARNING',
1095 X_Batch_id => NULL,
1096 X_Interface_Header_Id => p_interface_header_id,
1097 X_Interface_Line_id => p_interface_line_id,
1098 X_Error_message_name => 'PO_PDOI_COLUMN_NOT_NULL',
1099 X_Table_name => 'PO_PRICE_DIFF_INTERFACE',
1100 X_Column_name => 'PRICE_TYPE',
1101 X_TokenName1 => 'COLUMN_NAME',
1102 X_TokenName2 => NULL,
1103 X_TokenName3 => NULL,
1104 X_TokenName4 => NULL,
1105 X_TokenName5 => NULL,
1106 X_TokenName6 => NULL,
1107 X_TokenValue1 => 'PRICE_TYPE',
1108 X_TokenValue2 => NULL,
1109 X_TokenValue3 => NULL,
1110 X_TokenValue4 => NULL,
1111 X_TokenValue5 => NULL,
1112 X_TokenValue6 => NULL,
1113 X_header_processable_flag => l_header_processable_flag,
1114 X_Interface_Dist_Id => NULL);
1115
1116 ELSE --l_price_diff_record.price_type is not null
1117
1118 l_progress := '030';
1119
1120 --Check that the Price Differentials record have a valid
1121 --Price Type that is seeded in the PO_PRICE_TYPE lookup table.
1122
1123 SELECT COUNT(*)
1124 INTO l_count
1125 FROM PO_PRICE_DIFF_LOOKUPS_V
1126 WHERE price_differential_type = l_price_diff_record.price_type;
1127
1128 IF (l_count <> 1) THEN
1129
1130 l_error_flag := 'Y';
1131
1132 PO_INTERFACE_ERRORS_SV1.handle_interface_errors(
1133 X_interface_type => 'PO_DOCS_OPEN_INTERFACE',
1134 X_Error_type => 'WARNING',
1135 X_Batch_id => NULL,
1136 X_Interface_Header_Id => p_interface_header_id,
1137 X_Interface_Line_id => p_interface_line_id,
1138 X_Error_message_name => 'PO_PDOI_SVC_INVALID_PRICE_TYPE',
1139 X_Table_name => 'PO_PRICE_DIFF_INTERFACE',
1140 X_Column_name => 'PRICE_TYPE',
1141 X_TokenName1 => NULL,
1142 X_TokenName2 => NULL,
1143 X_TokenName3 => NULL,
1144 X_TokenName4 => NULL,
1145 X_TokenName5 => NULL,
1146 X_TokenName6 => NULL,
1147 X_TokenValue1 => NULL,
1148 X_TokenValue2 => NULL,
1149 X_TokenValue3 => NULL,
1150 X_TokenValue4 => NULL,
1151 X_TokenValue5 => NULL,
1152 X_TokenValue6 => NULL,
1153 X_header_processable_flag => l_header_processable_flag,
1154 X_Interface_Dist_Id => NULL);
1155
1156 END IF; --IF (l_count <> 1)
1157
1158 l_progress := '040';
1159
1160 --Check that we are not creating multiple price differential
1161 --records of the same type for a line/price break record.
1162
1163 SELECT COUNT(*)
1164 INTO l_count
1165 FROM PO_PRICE_DIFFERENTIALS
1166 WHERE entity_type = p_entity_type
1167 AND entity_id = p_entity_id
1168 AND price_type = l_price_diff_record.price_type;
1169
1170 IF (l_count > 0) THEN
1171
1172 l_error_flag := 'Y';
1173
1174 PO_INTERFACE_ERRORS_SV1.handle_interface_errors(
1175 X_interface_type => 'PO_DOCS_OPEN_INTERFACE',
1176 X_Error_type => 'WARNING',
1177 X_Batch_id => NULL,
1178 X_Interface_Header_Id => p_interface_header_id,
1179 X_Interface_Line_id => p_interface_line_id,
1180 X_Error_message_name => 'PO_PDOI_SVC_NO_MULTI_DIFF',
1181 X_Table_name => 'PO_PRICE_DIFF_INTERFACE',
1182 X_Column_name => 'PRICE_TYPE',
1183 X_TokenName1 => NULL,
1184 X_TokenName2 => NULL,
1185 X_TokenName3 => NULL,
1186 X_TokenName4 => NULL,
1187 X_TokenName5 => NULL,
1188 X_TokenName6 => NULL,
1189 X_TokenValue1 => NULL,
1190 X_TokenValue2 => NULL,
1191 X_TokenValue3 => NULL,
1192 X_TokenValue4 => NULL,
1193 X_TokenValue5 => NULL,
1194 X_TokenValue6 => NULL,
1195 X_header_processable_flag => l_header_processable_flag,
1196 X_Interface_Dist_Id => NULL);
1197
1198 ELSE --l_count <= 0
1199
1200 l_progress := '050';
1201
1202 SELECT COUNT(*)
1203 INTO l_count
1204 FROM PO_PRICE_DIFF_INTERFACE
1205 WHERE interface_line_id = p_interface_line_id
1206 AND price_type = l_price_diff_record.price_type;
1207
1208 IF (l_count > 1) THEN
1209 l_error_flag := 'Y';
1210
1211 PO_INTERFACE_ERRORS_SV1.handle_interface_errors(
1212 X_interface_type => 'PO_DOCS_OPEN_INTERFACE',
1213 X_Error_type => 'WARNING',
1214 X_Batch_id => NULL,
1215 X_Interface_Header_Id => p_interface_header_id,
1216 X_Interface_Line_id => p_interface_line_id,
1217 X_Error_message_name => 'PO_PDOI_SVC_NO_MULTI_DIFF',
1218 X_Table_name => 'PO_PRICE_DIFF_INTERFACE',
1219 X_Column_name => 'NULL',
1220 X_TokenName1 => NULL,
1221 X_TokenName2 => NULL,
1222 X_TokenName3 => NULL,
1223 X_TokenName4 => NULL,
1224 X_TokenName5 => NULL,
1225 X_TokenName6 => NULL,
1226 X_TokenValue1 => NULL,
1227 X_TokenValue2 => NULL,
1228 X_TokenValue3 => NULL,
1229 X_TokenValue4 => NULL,
1230 X_TokenValue5 => NULL,
1231 X_TokenValue6 => NULL,
1232 X_header_processable_flag => l_header_processable_flag,
1233 X_Interface_Dist_Id => NULL);
1234
1235 END IF; --IF (l_count > 1)
1236 END IF; --IF (l_count > 0)
1237 END IF; --IF l_price_diff_record.price_type IS NULL
1238
1239 l_progress := '060';
1240
1241 IF (l_price_diff_record.entity_type <> p_entity_type) THEN
1242
1243 l_error_flag := 'Y';
1244
1245 PO_INTERFACE_ERRORS_SV1.handle_interface_errors(
1246 X_interface_type => 'PO_DOCS_OPEN_INTERFACE',
1247 X_Error_type => 'WARNING',
1248 X_Batch_id => NULL,
1249 X_Interface_Header_Id => p_interface_header_id,
1250 X_Interface_Line_id => p_interface_line_id,
1251 X_Error_message_name => 'PO_PDOI_SVC_INVALID_ENT_TYPE',
1252 X_Table_name => 'PO_PRICE_DIFF_INTERFACE',
1253 X_Column_name => 'ENTITY_TYPE',
1254 X_TokenName1 => NULL,
1255 X_TokenName2 => NULL,
1256 X_TokenName3 => NULL,
1257 X_TokenName4 => NULL,
1258 X_TokenName5 => NULL,
1259 X_TokenName6 => NULL,
1260 X_TokenValue1 => NULL,
1261 X_TokenValue2 => NULL,
1262 X_TokenValue3 => NULL,
1263 X_TokenValue4 => NULL,
1264 X_TokenValue5 => NULL,
1265 X_TokenValue6 => NULL,
1266 X_header_processable_flag => l_header_processable_flag,
1267 X_Interface_Dist_Id => NULL);
1268 END IF; --IF (l_price_diff_record.entity_type IS NOT NULL...
1269
1270 l_progress := '070';
1271
1272 IF (p_entity_type = 'PO LINE') THEN
1273
1274 --Price Differentials for a PO must have a non-null value
1275 --for the multiplier column
1276
1277 IF (l_price_diff_record.multiplier IS NULL) THEN
1278 l_error_flag := 'Y';
1279
1280 PO_INTERFACE_ERRORS_SV1.handle_interface_errors(
1281 X_interface_type => 'PO_DOCS_OPEN_INTERFACE',
1282 X_Error_type => 'WARNING',
1283 X_Batch_id => NULL,
1284 X_Interface_Header_Id => p_interface_header_id,
1285 X_Interface_Line_id => p_interface_line_id,
1286 X_Error_message_name => 'PO_PDOI_SVC_MUST_MULTIPLIER',
1287 X_Table_name => 'PO_PRICE_DIFF_INTERFACE',
1288 X_Column_name => 'MULTIPLIER',
1289 X_TokenName1 => NULL,
1290 X_TokenName2 => NULL,
1291 X_TokenName3 => NULL,
1292 X_TokenName4 => NULL,
1293 X_TokenName5 => NULL,
1294 X_TokenName6 => NULL,
1295 X_TokenValue1 => NULL,
1296 X_TokenValue2 => NULL,
1297 X_TokenValue3 => NULL,
1298 X_TokenValue4 => NULL,
1299 X_TokenValue5 => NULL,
1300 X_TokenValue6 => NULL,
1301 X_header_processable_flag => l_header_processable_flag,
1302 X_Interface_Dist_Id => NULL);
1303
1304 END IF; --IF (l_price_diff_record.multiplier IS NULL)
1305
1306 IF (l_price_diff_record.min_multiplier IS NOT NULL) THEN
1307 l_error_flag := 'Y';
1308
1309 PO_INTERFACE_ERRORS_SV1.handle_interface_errors(
1310 X_interface_type => 'PO_DOCS_OPEN_INTERFACE',
1311 X_Error_type => 'WARNING',
1312 X_Batch_id => NULL,
1313 X_Interface_Header_Id => p_interface_header_id,
1314 X_Interface_Line_id => p_interface_line_id,
1315 X_Error_message_name => 'PO_PDOI_SVC_NO_MIN_MULT',
1316 X_Table_name => 'PO_PRICE_DIFF_INTERFACE',
1317 X_Column_name => 'MIN_MULTIPLIER',
1318 X_TokenName1 => NULL,
1319 X_TokenName2 => NULL,
1320 X_TokenName3 => NULL,
1321 X_TokenName4 => NULL,
1322 X_TokenName5 => NULL,
1323 X_TokenName6 => NULL,
1324 X_TokenValue1 => NULL,
1325 X_TokenValue2 => NULL,
1326 X_TokenValue3 => NULL,
1327 X_TokenValue4 => NULL,
1328 X_TokenValue5 => NULL,
1329 X_TokenValue6 => NULL,
1330 X_header_processable_flag => l_header_processable_flag,
1331 X_Interface_Dist_Id => NULL);
1332
1333 END IF; --IF (l_price_diff_record.min_multiplier IS NOT NULL)
1334
1335 IF (l_price_diff_record.max_multiplier IS NOT NULL) THEN
1336 l_error_flag := 'Y';
1337
1338 PO_INTERFACE_ERRORS_SV1.handle_interface_errors(
1339 X_interface_type => 'PO_DOCS_OPEN_INTERFACE',
1340 X_Error_type => 'WARNING',
1341 X_Batch_id => NULL,
1342 X_Interface_Header_Id => p_interface_header_id,
1343 X_Interface_Line_id => p_interface_line_id,
1344 X_Error_message_name => 'PO_PDOI_SVC_NO_MAX_MULT',
1345 X_Table_name => 'PO_PRICE_DIFF_INTERFACE',
1346 X_Column_name => 'MAX_MULTIPLIER',
1347 X_TokenName1 => NULL,
1348 X_TokenName2 => NULL,
1349 X_TokenName3 => NULL,
1350 X_TokenName4 => NULL,
1351 X_TokenName5 => NULL,
1352 X_TokenName6 => NULL,
1353 X_TokenValue1 => NULL,
1354 X_TokenValue2 => NULL,
1355 X_TokenValue3 => NULL,
1356 X_TokenValue4 => NULL,
1357 X_TokenValue5 => NULL,
1358 X_TokenValue6 => NULL,
1359 X_header_processable_flag => l_header_processable_flag,
1360 X_Interface_Dist_Id => NULL);
1361
1362 END IF; --IF (l_price_diff_record.max_multiplier IS NOT NULL)
1363
1364 ELSE --p_entity_type IN ('BLANKET', 'PRICE BREAK')
1365
1366 l_progress := '080';
1367
1368 --The minimum multiplier column should not be null
1369 --if this price differential is for a blanket or a price
1370 --break
1371
1372 IF (l_price_diff_record.min_multiplier IS NULL) THEN
1373
1374 l_error_flag := 'Y';
1375
1376 PO_INTERFACE_ERRORS_SV1.handle_interface_errors(
1377 X_interface_type => 'PO_DOCS_OPEN_INTERFACE',
1378 X_Error_type => 'WARNING',
1379 X_Batch_id => NULL,
1380 X_Interface_Header_Id => p_interface_header_id,
1381 X_Interface_Line_id => p_interface_line_id,
1382 X_Error_message_name => 'PO_PDOI_SVC_MUST_MIN_MULT',
1383 X_Table_name => 'PO_PRICE_DIFF_INTERFACE',
1384 X_Column_name => 'MIN_MULTIPLIER',
1385 X_TokenName1 => NULL,
1386 X_TokenName2 => NULL,
1387 X_TokenName3 => NULL,
1388 X_TokenName4 => NULL,
1389 X_TokenName5 => NULL,
1390 X_TokenName6 => NULL,
1391 X_TokenValue1 => NULL,
1392 X_TokenValue2 => NULL,
1393 X_TokenValue3 => NULL,
1394 X_TokenValue4 => NULL,
1395 X_TokenValue5 => NULL,
1396 X_TokenValue6 => NULL,
1397 X_header_processable_flag => l_header_processable_flag,
1398 X_Interface_Dist_Id => NULL);
1399
1400 END IF;
1401
1402 IF (l_price_diff_record.max_multiplier IS NOT NULL
1403 AND l_price_diff_record.min_multiplier >
1404 l_price_diff_record.max_multiplier) THEN
1405
1406 l_error_flag := 'Y';
1407
1408 PO_INTERFACE_ERRORS_SV1.handle_interface_errors(
1409 X_interface_type => 'PO_DOCS_OPEN_INTERFACE',
1410 X_Error_type => 'WARNING',
1411 X_Batch_id => NULL,
1412 X_Interface_Header_Id => p_interface_header_id,
1413 X_Interface_Line_id => p_interface_line_id,
1414 X_Error_message_name => 'PO_PDOI_SVC_MAX_MULT_GE_MIN',
1415 X_Table_name => 'PO_PRICE_DIFF_INTERFACE',
1416 X_Column_name => 'MAX_MULTIPLIER',
1417 X_TokenName1 => NULL,
1418 X_TokenName2 => NULL,
1419 X_TokenName3 => NULL,
1420 X_TokenName4 => NULL,
1421 X_TokenName5 => NULL,
1422 X_TokenName6 => NULL,
1423 X_TokenValue1 => NULL,
1424 X_TokenValue2 => NULL,
1425 X_TokenValue3 => NULL,
1426 X_TokenValue4 => NULL,
1427 X_TokenValue5 => NULL,
1428 X_TokenValue6 => NULL,
1429 X_header_processable_flag => l_header_processable_flag,
1430 X_Interface_Dist_Id => NULL);
1431
1432 END IF;
1433
1434 IF (l_price_diff_record.multiplier IS NOT NULL) THEN
1435 l_error_flag := 'Y';
1436
1437 PO_INTERFACE_ERRORS_SV1.handle_interface_errors(
1438 X_interface_type => 'PO_DOCS_OPEN_INTERFACE',
1439 X_Error_type => 'WARNING',
1440 X_Batch_id => NULL,
1441 X_Interface_Header_Id => p_interface_header_id,
1442 X_Interface_Line_id => p_interface_line_id,
1443 X_Error_message_name => 'PO_PDOI_SVC_NO_MULTIPLIER',
1444 X_Table_name => 'PO_PRICE_DIFF_INTERFACE',
1445 X_Column_name => 'MULTIPLIER',
1446 X_TokenName1 => NULL,
1447 X_TokenName2 => NULL,
1448 X_TokenName3 => NULL,
1449 X_TokenName4 => NULL,
1450 X_TokenName5 => NULL,
1451 X_TokenName6 => NULL,
1452 X_TokenValue1 => NULL,
1453 X_TokenValue2 => NULL,
1454 X_TokenValue3 => NULL,
1455 X_TokenValue4 => NULL,
1456 X_TokenValue5 => NULL,
1457 X_TokenValue6 => NULL,
1458 X_header_processable_flag => l_header_processable_flag,
1459 X_Interface_Dist_Id => NULL);
1460
1461 END IF; --IF (l_price_diff_record.multiplier IS NOT NULL)
1462
1463 END IF; --IF (p_entity_type = 'PO LINE')
1464
1465 l_progress := '090';
1466
1467 IF (l_error_flag = 'Y') THEN
1468 UPDATE PO_PRICE_DIFF_INTERFACE
1469 SET process_status = 'REJECTED'
1470 WHERE price_diff_interface_id = l_price_diff_record.price_diff_interface_id;
1471 ELSE
1472
1473 l_price_diff_num := l_price_diff_num + 1;
1474
1475 UPDATE PO_PRICE_DIFF_INTERFACE
1476 SET process_status = 'ACCEPTED',
1477 price_differential_num = l_price_diff_num,
1478 entity_type = NVL(entity_type, p_entity_type)
1479 WHERE price_diff_interface_id = l_price_diff_record.price_diff_interface_id;
1480
1481 END IF;
1482
1483 END LOOP;
1484 CLOSE l_price_diff_csr;
1485
1486 EXCEPTION
1487 WHEN OTHERS THEN
1488 PO_MESSAGE_S.sql_error('validate_price_differentials', l_progress, sqlcode);
1489 RAISE;
1490 END validate_price_differentials;
1491 --<SERVICES FPJ END>
1492
1493 -----------------------------------------------------------------<SERVICES FPJ>
1494 -------------------------------------------------------------------------------
1495 --Start of Comments
1496 --Name: get_price_for_price_type
1497 --Pre-reqs:
1498 -- None.
1499 --Modifies:
1500 -- None.
1501 --Locks:
1502 -- None.
1503 --Function:
1504 -- Gets the price for a given Price Differential.
1505 --Parameters:
1506 --IN:
1507 --p_entity_type
1508 -- Entity Type of Price Differential
1509 -- ['REQ LINE','PO LINE','BLANKET LINE','PRICE BREAK']
1510 --p_entity_id
1511 -- Unique ID of the Line or Price Break
1512 --p_price_type
1513 -- Price Differential Price Type
1514 --OUT:
1515 --x_price : The line price multiplied by the multiplier for the price type
1516 --Testing:
1517 -- None.
1518 --End of Comments
1519 -------------------------------------------------------------------------------
1520 -------------------------------------------------------------------------------
1521 PROCEDURE get_price_for_price_type
1522 (
1523 p_entity_id IN NUMBER
1524 , p_entity_type IN VARCHAR2
1525 , p_price_type IN VARCHAR2
1526 , x_price OUT NOCOPY NUMBER
1527 )
1528 IS
1529
1530 l_line_price PO_LINES_ALL.unit_price%TYPE;
1531 l_multiplier PO_PRICE_DIFFERENTIALS.multiplier%TYPE;
1532
1533 BEGIN
1534
1535 -- Return is any of the input parameters are not provided
1536 IF p_entity_type is null OR
1537 p_entity_id is null OR
1538 p_price_type is null
1539 THEN
1540 Return;
1541 END IF;
1542
1543 SELECT unit_price
1544 INTO l_line_price
1545 FROM po_lines_all
1546 WHERE po_line_id = p_entity_id;
1547
1548 Begin
1549 SELECT multiplier
1550 INTO l_multiplier
1551 FROM po_price_differentials
1552 WHERE entity_type = p_entity_type
1553 AND entity_id = p_entity_id
1554 AND price_type = p_price_type;
1555 Exception
1556 When NO_DATA_FOUND Then
1557 l_multiplier := 1;
1558 End;
1559
1560 x_price := l_line_price * l_multiplier;
1561
1562
1563 EXCEPTION
1564
1565 WHEN OTHERS THEN
1566 x_price := null;
1567
1568 END get_price_for_price_type;
1569
1570 --<HTML Agreements R12 Start>
1571 -------------------------------------------------------------------------------
1572 --Start of Comments
1573 --Name: get_entity_type
1574 --Pre-reqs:
1575 -- None.
1576 --Modifies:
1577 -- None.
1578 --Locks:
1579 -- None.
1580 --Function:
1581 -- Given the document level, document type it returns the entity type with
1582 -- with which the price differentials are created for that level/doc type.
1583 --Parameters:
1584 -- IN
1585 -- p_doc_level
1586 -- Document Level {LINE/SHIPMENT}
1587 -- p_doc_level_id
1588 -- Unique Identifier for the Document Line/Shipment
1589 --Testing:
1590 -- None.
1591 --End of Comments
1592 -------------------------------------------------------------------------------
1593 FUNCTION get_entity_type( p_doc_level IN VARCHAR2
1594 ,p_doc_level_id IN NUMBER)
1595 RETURN VARCHAR2
1596 IS
1597 l_entity_type PO_PRICE_DIFFERENTIALS.entity_type%type := NULL;
1598 l_ga_flag PO_HEADERS_ALL.global_agreement_flag%TYPE;
1599 l_doc_subtype PO_HEADERS_ALL.type_lookup_code%TYPE;
1600 l_value_basis PO_LINE_TYPES_B.order_type_lookup_code%TYPE;
1601 l_purchase_basis PO_LINE_TYPES_B.purchase_basis%TYPE;
1602
1603 d_module_name CONSTANT VARCHAR2(30) := 'GET_ENTITY_TYPE';
1604 d_module_base CONSTANT VARCHAR2(70) := 'po.plsql.PO_PRICE_DIFFERENTIALS_PVT.get_entity_type';
1605 d_pos NUMBER := 0;
1606 BEGIN
1607 IF (PO_LOG.d_proc) THEN
1608 PO_LOG.proc_begin(d_module_base, 'p_doc_level', p_doc_level); PO_LOG.proc_begin(d_module_base, 'p_doc_level_id', p_doc_level_id);
1609 END IF;
1610
1611 IF p_doc_level = PO_CORE_S.g_doc_level_LINE THEN
1612 d_pos := 10;
1613
1614 SELECT poh.global_agreement_flag,
1615 poh.type_lookup_code,
1616 pol.order_type_lookup_code,
1617 pol.purchase_basis
1618 INTO l_ga_flag,
1619 l_doc_subtype,
1620 l_value_basis,
1621 l_purchase_basis
1622 FROM PO_LINES_ALL pol,
1623 PO_HEADERS_ALL poh
1624 WHERE poh.po_header_id = pol.po_header_id
1625 AND po_line_id = p_doc_level_id;
1626
1627 ELSIF p_doc_level = PO_CORE_S.g_doc_level_SHIPMENT THEN
1628 d_pos := 20;
1629
1630 SELECT poh.global_agreement_flag,
1631 poh.type_lookup_code,
1632 pol.order_type_lookup_code,
1633 pol.purchase_basis
1634 INTO l_ga_flag,
1635 l_doc_subtype,
1636 l_value_basis,
1637 l_purchase_basis
1638 FROM PO_HEADERS_ALL poh,
1639 PO_LINES_ALL pol,
1640 PO_LINE_LOCATIONS_ALL poll
1641 WHERE poh.po_header_id = pol.po_header_id
1642 AND pol.po_line_id = poll.po_line_id
1643 AND poll.line_location_id = p_doc_level_id;
1644
1645 END IF;
1646
1647 d_pos := 30;
1648 IF (PO_LOG.d_stmt) THEN
1649 PO_LOG.stmt(d_module_name,d_pos,'l_ga_flag',l_ga_flag); PO_LOG.stmt(d_module_name,d_pos,'l_value_basis',l_value_basis); PO_LOG.stmt(d_module_name,d_pos,'l_purchase_basis',l_purchase_basis);
1650 PO_LOG.stmt(d_module_name,d_pos,'l_doc_subtype',l_doc_subtype);
1651 END IF;
1652
1653 d_pos := 35;
1654 --Price Differentials are only allowed for Rate Based Temp Labor
1655 IF (l_value_basis <> 'RATE' OR l_purchase_basis <> 'TEMP LABOR') THEN
1656 RAISE PO_CORE_S.g_early_return_exc ;
1657 END IF;
1658
1659 IF l_doc_subtype = PO_CONSTANTS_SV.STANDARD THEN
1660 d_pos := 40;
1661
1662 IF p_doc_level = PO_CORE_S.g_doc_level_LINE THEN
1663 l_entity_type := 'PO LINE';
1664 END IF;
1665
1666 ELSIF l_doc_subtype = PO_CONSTANTS_SV.BLANKET THEN
1667 d_pos := 50;
1668 --Price Differentials are only allowed for Global Blankets
1669 IF p_doc_level = PO_CORE_S.g_doc_level_LINE
1670 AND l_ga_flag = 'Y' THEN
1671 l_entity_type := 'BLANKET LINE';
1672 ELSIF p_doc_level = PO_CORE_S.g_doc_level_SHIPMENT THEN
1673 l_entity_type := 'PRICE BREAK';
1674
1675 END IF;
1676
1677 END IF;
1678
1679 IF (PO_LOG.d_proc) THEN
1680 PO_LOG.proc_end(d_module_base,'l_entity_type',l_entity_type);
1681 END IF;
1682
1683 return l_entity_type;
1684 EXCEPTION
1685 WHEN PO_CORE_S.g_early_return_exc THEN
1686 IF PO_LOG.d_stmt THEN
1687 PO_LOG.stmt(d_module_name,d_pos,'Early exit from ' || d_module_name);
1688 END IF;
1689 return l_entity_type;
1690 WHEN OTHERS THEN
1691 FND_MSG_PUB.add_exc_msg(g_pkg_name, d_module_name || ':'|| d_pos);
1692 IF (PO_LOG.d_exc) THEN
1693 PO_LOG.exc(d_module_base, d_pos, SQLCODE || SQLERRM);
1694 END IF;
1695 RAISE;
1696 END get_entity_type;
1697
1698 --<HTML Agreements R12 End>
1699 END PO_PRICE_DIFFERENTIALS_PVT;