[Home] [Help]
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: -----------------------------------------------------------------
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:
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 ) );
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:
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 ) );
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:
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
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
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:
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:
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
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
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
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
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:
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
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
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:
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:
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.
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
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:
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 )
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:
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:
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:
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:
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';
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'
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
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:
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.
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:
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:
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;
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:
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
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
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;
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);
1695: RAISE;
1696: END get_entity_type;
1697:
1698: --
1699: END PO_PRICE_DIFFERENTIALS_PVT;