1 PACKAGE BODY OE_RMA_QUERY_PRICE as
2 /* $Header: oexrlqpb.pls 115.0 99/07/16 08:29:23 porting ship $ */
3
4 /*
5 ** Get the price adjustments total associated with
6 ** the given header_id and line_id.
7 */
8 FUNCTION GET_PRICE_ADJUSTMENTS_TOTAL(
9 P_HEADER_ID IN NUMBER,
10 P_LINE_ID IN NUMBER,
11 P_REF_ORD_HEADER_ID IN NUMBER DEFAULT NULL
12 )
13 RETURN NUMBER IS
14
15 L_PRICE_ADJUSTMENTS_TOTAL NUMBER;
16
17 BEGIN
18
19 SELECT NVL( SUM( PERCENT ), 0 )
20 INTO L_PRICE_ADJUSTMENTS_TOTAL
21 FROM SO_PRICE_ADJUSTMENTS
22 WHERE (HEADER_ID = P_HEADER_ID
23 AND LINE_ID IS NULL)
24 OR LINE_ID = P_LINE_ID
25 OR (HEADER_ID = P_REF_ORD_HEADER_ID
26 AND LINE_ID IS NULL);
27
28 RETURN L_PRICE_ADJUSTMENTS_TOTAL;
29
30 EXCEPTION
31 WHEN NO_DATA_FOUND THEN
32 RETURN 0;
33
34 END GET_PRICE_ADJUSTMENTS_TOTAL;
35
36
37 /*
38 ** Get the price list related information given price list id
39 ** and the reference line id
40 */
41 PROCEDURE GET_PRICE_INFO (
42 P_LINE_ID IN NUMBER,
43 P_PRICE_LIST_ID OUT NUMBER,
44 P_TERMS_ID OUT NUMBER,
45 P_ROUNDING_FACTOR OUT NUMBER
46 ) IS
47
48
49 BEGIN
50
51 P_PRICE_LIST_ID := NULL;
52 P_TERMS_ID := NULL;
53 P_ROUNDING_FACTOR := NULL;
54
55 SELECT SO_LINES.PRICE_LIST_ID
56 , SO_LINES.TERMS_ID
57 , SOPLS.ROUNDING_FACTOR
58 INTO P_PRICE_LIST_ID
59 , P_TERMS_ID
60 , P_ROUNDING_FACTOR
61 FROM SO_PRICE_LISTS SOPLS
62 , SO_LINES
63 WHERE SO_LINES.PRICE_LIST_ID = SOPLS.PRICE_LIST_ID
64 AND SO_LINES.LINE_ID = P_LINE_ID;
65
66 EXCEPTION
67 WHEN NO_DATA_FOUND THEN
68 RETURN;
69
70 END GET_PRICE_INFO;
71
72
73 /*
74 ** Get pricing attribute values given reference line id
75 */
76 PROCEDURE GET_PRICING(
77 L_PRICING_CONTEXT OUT VARCHAR2,
78 L_PRICING_ATTRIBUTE1 OUT VARCHAR2,
79 L_PRICING_ATTRIBUTE2 OUT VARCHAR2,
80 L_PRICING_ATTRIBUTE3 OUT VARCHAR2,
81 L_PRICING_ATTRIBUTE4 OUT VARCHAR2,
82 L_PRICING_ATTRIBUTE5 OUT VARCHAR2,
83 L_PRICING_ATTRIBUTE6 OUT VARCHAR2,
84 L_PRICING_ATTRIBUTE7 OUT VARCHAR2,
85 L_PRICING_ATTRIBUTE8 OUT VARCHAR2,
86 L_PRICING_ATTRIBUTE9 OUT VARCHAR2,
87 L_PRICING_ATTRIBUTE10 OUT VARCHAR2,
88 L_PRICING_ATTRIBUTE11 OUT VARCHAR2,
89 L_PRICING_ATTRIBUTE12 OUT VARCHAR2,
90 L_PRICING_ATTRIBUTE13 OUT VARCHAR2,
91 L_PRICING_ATTRIBUTE14 OUT VARCHAR2,
92 L_PRICING_ATTRIBUTE15 OUT VARCHAR2,
93 L_LINK_TO_LINE_ID IN NUMBER
94 ) IS
95
96 BEGIN
97
98 SELECT PRICING_CONTEXT,
99 PRICING_ATTRIBUTE1,
100 PRICING_ATTRIBUTE2,
101 PRICING_ATTRIBUTE3,
102 PRICING_ATTRIBUTE4,
103 PRICING_ATTRIBUTE5,
104 PRICING_ATTRIBUTE6,
105 PRICING_ATTRIBUTE7,
106 PRICING_ATTRIBUTE8,
107 PRICING_ATTRIBUTE9,
108 PRICING_ATTRIBUTE10,
109 PRICING_ATTRIBUTE11,
110 PRICING_ATTRIBUTE12,
111 PRICING_ATTRIBUTE13,
112 PRICING_ATTRIBUTE14,
113 PRICING_ATTRIBUTE15
114 INTO L_PRICING_CONTEXT,
115 L_PRICING_ATTRIBUTE1,
116 L_PRICING_ATTRIBUTE2,
117 L_PRICING_ATTRIBUTE3,
118 L_PRICING_ATTRIBUTE4,
119 L_PRICING_ATTRIBUTE5,
120 L_PRICING_ATTRIBUTE6,
121 L_PRICING_ATTRIBUTE7,
122 L_PRICING_ATTRIBUTE8,
123 L_PRICING_ATTRIBUTE9,
124 L_PRICING_ATTRIBUTE10,
125 L_PRICING_ATTRIBUTE11,
126 L_PRICING_ATTRIBUTE12,
127 L_PRICING_ATTRIBUTE13,
128 L_PRICING_ATTRIBUTE14,
129 L_PRICING_ATTRIBUTE15
130 FROM SO_LINES
131 WHERE LINE_ID = L_LINK_TO_LINE_ID;
132
133 RETURN;
134
135 EXCEPTION
136 WHEN NO_DATA_FOUND THEN
137 RETURN;
138
139 END GET_PRICING;
140
141
142 END OE_RMA_QUERY_PRICE;
[Home] [Help]