[Home] [Help]
PACKAGE BODY: APPS.QP_PRICING_ENGINE_PVT
Source
1 PACKAGE BODY QP_Pricing_Engine_PVT AS
2 /* $Header: QPXVDLNB.pls 115.3 1999/11/04 11:28:34 pkm ship $ */
3
4 PROCEDURE Get_Discount_Lines
5 (p_price_list_id NUMBER,
6 p_list_price NUMBER,
7 p_quantity NUMBER,
8 p_unit_code VARCHAR2,
9 p_attribute_id NUMBER,
10 p_attribute_value VARCHAR2,
11 p_pricing_date DATE,
12 p_customer_class_code VARCHAR2,
13 p_sold_to_org_id VARCHAR2,
14 p_ship_to_id VARCHAR2,
15 p_invoice_to_id VARCHAR2,
16 p_best_adj_percent NUMBER,
17 p_gsa VARCHAR2,
18 p_asc_desc_flag VARCHAR2,
22
19 x_discount_line_rec OUT l_discount_line_rec) AS
20 --x_discount_lines_tbl OUT l_discount_lines_tbl) AS
21
23 v_discount_line_rec l_discount_line_rec;
24 v_discount_lines_tbl l_discount_lines_tbl;
25 x_discount_rec l_discount_line_rec;
26
27 cnt NUMBER := 0;
28 v_value NUMBER;
29 v_dummy VARCHAR2(1);
30 v_index NUMBER;
31 err_num NUMBER;
32 err_msg VARCHAR2(100);
33
34 v_product_context QP_PRICING_ATTRIBUTES.PRODUCT_ATTRIBUTE_CONTEXT%TYPE;
35 v_product_attribute QP_PRICING_ATTRIBUTES.PRODUCT_ATTRIBUTE%TYPE;
36 v_qualifier_context QP_QUALIFIERS.QUALIFIER_CONTEXT%TYPE;
37 v_qualifier_attribute QP_QUALIFIERS.QUALIFIER_ATTRIBUTE%TYPE;
38 v_customer_context QP_QUALIFIERS.QUALIFIER_CONTEXT%TYPE;
39 v_customer_class_attr QP_QUALIFIERS.QUALIFIER_ATTRIBUTE%TYPE;
40 v_sold_to_org_attr QP_QUALIFIERS.QUALIFIER_ATTRIBUTE%TYPE;
41 v_site_org_attr QP_QUALIFIERS.QUALIFIER_ATTRIBUTE%TYPE;
42 v_lines_flag VARCHAR2(1);
43
44
45 CURSOR get_discount_headers(p_context VARCHAR2,p_attribute VARCHAR2,p_attr_value VARCHAR2,
46 p_customer_context VARCHAR2 , p_customer_class_attr VARCHAR2,
47 p_sold_to_org_attr VARCHAR2, p_site_org_attr VARCHAR2) IS
48
49 SELECT qph.list_header_id,qph.name,qpl.list_line_id,qpl.list_line_type_code,
50 qpl.operand, qpl.arithmetic_operator,qph.discount_lines_flag
51 FROM QP_LIST_HEADERS qph , QP_LIST_LINES qpl,QP_QUALIFIERS qpq
52 WHERE qph.LIST_HEADER_ID = qpl.LIST_HEADER_ID
53 AND qpl.LIST_LINE_TYPE_CODE IN ('DIS','PBH')
54 AND qph.LIST_HEADER_ID = qpq.LIST_HEADER_ID
55 AND qph.AUTOMATIC_FLAG = 'Y'
56 AND ((p_gsa = 'Y') OR nvl(qph.GSA_INDICATOR , 'N') = 'N')
57 AND qpq.QUALIFIER_CONTEXT = p_context
58 AND qpq.QUALIFIER_ATTRIBUTE = p_attribute
59 AND qpq.QUALIFIER_ATTR_VALUE = p_attr_value
60 AND trunc(p_pricing_date) BETWEEN nvl(qpl.START_DATE_ACTIVE,trunc(p_pricing_date))
61 AND nvl(qpl.END_DATE_ACTIVE,trunc(p_pricing_date))
62 AND ( not exists (select null
63 from qp_qualifiers qpq
64 where qpq.list_header_id = qph.list_header_id
65 and qpq.qualifier_context = p_customer_context
66 and qpq.qualifier_attribute in (p_customer_class_attr , p_sold_to_org_attr, p_site_org_attr))
67 or
68 ( exists(select null
69 from qp_qualifiers qpq
70 where qpq.list_header_id = qph.list_header_id
71 and (qpq.qualifier_context = p_customer_context
72 and qpq.qualifier_attribute = p_sold_to_org_attr
73 and nvl(qpq.qualifier_attr_value,p_sold_to_org_id) = p_sold_to_org_id))
74 or exists(select null
75 from qp_qualifiers qpq
76 where qpq.list_header_id = qph.list_header_id
77 and (qpq.qualifier_context = p_customer_context
78 and qpq.qualifier_attribute = p_customer_class_attr
79 and nvl(qpq.qualifier_attr_value,p_customer_class_code) = p_customer_class_code))
80 and exists(select null
81 from qp_qualifiers qpq
82 where (qpq.qualifier_context = p_customer_context
83 and qpq.qualifier_attribute = p_site_org_attr
84 and (nvl(qpq.qualifier_attr_value,p_ship_to_id) = p_ship_to_id
85 or nvl(qpq.qualifier_attr_value,p_invoice_to_id) = p_invoice_to_id)))));
86
87 CURSOR get_discount_lines
88 (p_list_line_id NUMBER,p_product_attribute_context VARCHAR2,p_product_attribute VARCHAR2,
89 p_qualifier_context VARCHAR2 , p_qualifier_attribute VARCHAR2 ) IS
90
91 SELECT 'X'
92 FROM QP_LIST_LINES qpl, QP_PRICING_ATTRIBUTES qpbl
93 WHERE qpl.LIST_LINE_ID = p_list_line_id
94 AND qpl.LIST_LINE_ID = qpbl.LIST_LINE_ID
95 AND qpbl.PRODUCT_ATTRIBUTE_CONTEXT = p_product_attribute_context
96 AND qpbl.PRODUCT_ATTRIBUTE = p_product_attribute
97 AND qpbl.PRODUCT_ATTR_VALUE = p_attribute_value
98 AND nvl(qpbl.PRODUCT_UOM_CODE,nvl(p_unit_code,'NULL')) = nvl(p_unit_code,'NULL')
99 AND trunc(p_pricing_date) BETWEEN nvl(qpl.START_DATE_ACTIVE,trunc(p_pricing_date))
100 AND nvl(qpl.END_DATE_ACTIVE,trunc(p_pricing_date))
101 UNION
102 SELECT 'Y'
103 FROM QP_LIST_LINES qpl, QP_QUALIFIERS qpq
104 WHERE qpl.LIST_LINE_ID = p_list_line_id
105 AND qpl.LIST_LINE_ID = qpq.LIST_LINE_ID
106 AND qpq.QUALIFIER_CONTEXT = p_qualifier_context
107 AND qpq.QUALIFIER_ATTRIBUTE = p_qualifier_attribute
108 AND qpq.QUALIFIER_ATTR_VALUE = p_attribute_value
109 AND trunc(p_pricing_date) BETWEEN nvl(qpl.START_DATE_ACTIVE,trunc(p_pricing_date))
110 AND nvl(qpl.END_DATE_ACTIVE,trunc(p_pricing_date));
111
112 CURSOR get_price_break_lines
113 (p_list_line_id NUMBER,p_product_attribute_context VARCHAR2,p_product_attribute VARCHAR2,
114 p_qualifier_context VARCHAR2 , p_qualifier_attribute VARCHAR2 ) IS
115
116 SELECT qpbl.PRICING_ATTRIBUTE_ID,qpbl.LIST_LINE_ID,qpbl.PRICING_ATTRIBUTE_CONTEXT,qpbl.PRICING_ATTRIBUTE,
117 qpbl.PRICING_ATTR_VALUE_FROM, qpbl.PRICING_ATTR_VALUE_TO,qpl.OPERAND , qpl.ARITHMETIC_OPERATOR
118 FROM QP_PRICING_ATTRIBUTES qpbl , QP_RLTD_MODIFIERS qprl, QP_LIST_LINES qpl
119 WHERE qpl.LIST_LINE_ID = qpbl.LIST_LINE_ID
120 AND qpbl.LIST_LINE_ID = qprl.TO_RLTD_MODIFIER_ID
121 AND qpbl.PRODUCT_ATTRIBUTE_CONTEXT = p_product_attribute_context
122 AND qpbl.PRODUCT_ATTRIBUTE = p_product_attribute
123 AND qpbl.PRODUCT_ATTR_VALUE = p_attribute_value
124 AND nvl(qpbl.PRODUCT_UOM_CODE,nvl(p_unit_code,'NULL')) = nvl(p_unit_code,'NULL')
125 AND qprl.FROM_RLTD_MODIFIER_ID = p_list_line_id
126 AND trunc(p_pricing_date) BETWEEN nvl(qpl.START_DATE_ACTIVE,trunc(p_pricing_date))
127 AND nvl(qpl.END_DATE_ACTIVE,trunc(p_pricing_date))
128 UNION
129 SELECT qpbl.PRICING_ATTRIBUTE_ID,qpbl.LIST_LINE_ID,qpbl.PRICING_ATTRIBUTE_CONTEXT,qpbl.PRICING_ATTRIBUTE,
130 qpbl.PRICING_ATTR_VALUE_FROM, qpbl.PRICING_ATTR_VALUE_TO,qpl.OPERAND , qpl.ARITHMETIC_OPERATOR
131 FROM QP_PRICING_ATTRIBUTES qpbl , QP_LIST_LINES qpl, QP_QUALIFIERS qpq, QP_RLTD_MODIFIERS qprl
132 WHERE qpq.LIST_LINE_ID = p_list_line_id
133 AND qpq.QUALIFIER_CONTEXT = p_qualifier_context
134 AND qpq.QUALIFIER_ATTRIBUTE = p_qualifier_attribute
135 AND qpq.QUALIFIER_ATTR_VALUE = p_attribute_value
136 AND qpbl.LIST_LINE_ID = qprl.TO_RLTD_MODIFIER_ID
137 AND qpl.LIST_LINE_ID = qpbl.LIST_LINE_ID
138 AND qpl.LIST_LINE_ID IN ( SELECT TO_RLTD_MODIFIER_ID
139 FROM QP_RLTD_MODIFIERS qprl
140 WHERE qprl.FROM_RLTD_MODIFIER_ID = p_list_line_id)
141 AND trunc(p_pricing_date) BETWEEN nvl(qpl.START_DATE_ACTIVE,trunc(p_pricing_date))
142 AND nvl(qpl.END_DATE_ACTIVE,trunc(p_pricing_date));
143 BEGIN
144
145 -- Get the contexts and attributes
146 QP_UTIL.Get_Context_Attribute('PRICE_LIST_ID',v_qualifier_context,v_qualifier_attribute);
147 QP_UTIL.Get_Context_Attribute('CUSTOMER_CLASS_CODE',v_customer_context,v_customer_class_attr);
148 QP_UTIL.Get_Context_Attribute('SOLD_TO_ORG_ID',v_customer_context,v_sold_to_org_attr);
149 QP_UTIL.Get_Context_Attribute('SITE_ORG_ID',v_customer_context,v_site_org_attr);
150
151 FOR i in get_discount_headers(v_qualifier_context,v_qualifier_attribute,p_price_list_id,
152 v_customer_context,v_customer_class_attr,v_sold_to_org_attr,v_site_org_attr)
153 LOOP
154 --dbms_output.put_line('Inside the Loop');
155 --dbms_output.put_line('Discount Lines Flag: ' || nvl(i.discount_lines_flag,'NO LINES'));
156 --dbms_output.put_line('Discount Id: ' || i.list_header_id);
157 --dbms_output.put_line('Discount Line Id: ' || i.list_line_id);
158
159 IF (i.list_line_type_code = 'DIS') THEN
160
161 BEGIN
162 SELECT 'x'
163 INTO v_dummy
164 FROM QP_RLTD_MODIFIERS
165 WHERE TO_RLTD_MODIFIER_ID = i.list_line_id;
166 EXCEPTION
167 WHEN NO_DATA_FOUND THEN
168 v_dummy := NULL;
169 END;
170
171 --dbms_output.put_line('Dummy :' || v_dummy);
172
173 -- if v_dummy is not null then it is a line related to a price break line . So ignore it.
174 IF (v_dummy IS NULL) THEN
175
176 -- Get the context and attribute
177 IF QP_UTIL.Is_qualifier(p_attribute_id) = 'T' THEN
178 QP_UTIL.Get_Context_Attribute(p_attribute_id , v_qualifier_context , v_qualifier_attribute);
179 v_product_context := NULL;
180 v_product_attribute := NULL;
181 ELSIF QP_UTIL.Is_PricingAttr(p_attribute_id) = 'T' THEN
182 QP_UTIL.Get_Context_Attribute(p_attribute_id , v_product_context , v_product_attribute);
183 v_qualifier_context := NULL;
184 v_qualifier_attribute := NULL;
185 END IF;
186
187 --dbms_output.put_line('Product Context:' || v_product_context);
188 --dbms_output.put_line('Product Attribute:' || v_product_attribute);
189 --dbms_output.put_line('Qualifier Context:' || v_qualifier_context);
190 --dbms_output.put_line('Qualifier Attribute:' || v_qualifier_attribute);
191 --dbms_output.put_line('List Line Id:' || i.list_line_id);
192 --dbms_output.put_line('Attribute Value:' || p_attribute_value);
193
194 -- Reinit the lines flag
195 v_lines_flag := null;
196
197 -- Check to find a record with attribute id and value . If v_lines_flag is not null then match found
198 OPEN get_discount_lines
199 (i.list_line_id,v_product_context,v_product_attribute,v_qualifier_context,v_qualifier_attribute);
200 FETCH get_discount_lines INTO v_lines_flag;
201 CLOSE get_discount_lines;
202
203 IF (v_lines_flag IS NOT NULL) THEN
204
205 v_discount_line_rec.p_discount_id := i.list_header_id;
206 v_discount_line_rec.p_discount_name := i.name;
207 v_discount_line_rec.p_discount_line_id := i.list_line_id;
208
209 IF (i.arithmetic_operator = 'AMT') THEN
210 v_discount_line_rec.p_discount_percent := nvl(i.operand/p_list_price * 100,0);
211 ELSIF (i.arithmetic_operator = '%') THEN
212 v_discount_line_rec.p_discount_percent := nvl(i.operand,0);
213 ELSIF (i.arithmetic_operator = 'NEWPRICE') THEN
214 v_discount_line_rec.p_discount_percent := p_list_price - nvl(i.operand,0);
215 END IF;
216
217 IF (p_asc_desc_flag = 'A') THEN
218 IF (v_discount_line_rec.p_discount_percent < p_best_adj_percent) THEN
219 cnt := cnt + 1;
220 v_discount_lines_tbl(cnt) := v_discount_line_rec;
221 END IF;
222 ELSE
223 IF (v_discount_line_rec.p_discount_percent > p_best_adj_percent) THEN
224 cnt := cnt + 1;
225 v_discount_lines_tbl(cnt) := v_discount_line_rec;
226 END IF;
227 END IF;
228
229 END IF;
230 END IF;
231 ELSIF (i.list_line_type_code = 'PBH') THEN
232 --dbms_output.put_line('Discount Id: ' || i.list_header_id);
233 --dbms_output.put_line('Discount Line Id: ' || i.list_line_id);
234 --dbms_output.put_line('Discount Line Type Code : ' || 'PBH');
235
236 -- Get the context and attribute
237 IF QP_UTIL.Is_qualifier(p_attribute_id) = 'T' THEN
238 QP_UTIL.Get_Context_Attribute(p_attribute_id , v_qualifier_context , v_qualifier_attribute);
239 v_product_context := NULL;
240 v_product_attribute := NULL;
241 ELSIF QP_UTIL.Is_PricingAttr(p_attribute_id) = 'T' THEN
242 QP_UTIL.Get_Context_Attribute(p_attribute_id , v_product_context , v_product_attribute);
243 v_qualifier_context := NULL;
244 v_qualifier_attribute := NULL;
245 END IF;
246
247 --dbms_output.put_line('Product Context:' || v_product_context);
248 --dbms_output.put_line('Product Attribute:' || v_product_attribute);
249 --dbms_output.put_line('Qualifier Context:' || v_qualifier_context);
250 --dbms_output.put_line('Qualifier Attribute:' || v_qualifier_attribute);
251 --dbms_output.put_line('List Line Id:' || i.list_line_id);
252 --dbms_output.put_line('Attribute Value:' || p_attribute_value);
253
254
255 -- This cursor will take care of both product and qualifier contexts. Only 1 Select in the UNION
256 -- will be successful any time depending on whether it is a product or qualifier
257
258 FOR j in get_price_break_lines
259 (i.list_line_id,v_product_context,v_product_attribute,v_qualifier_context,v_qualifier_attribute)
260 LOOP
261 --dbms_output.put_line('Inside the Price break Loop');
262
263 IF (j.pricing_attribute_context = 'VOLUME' AND j.pricing_attribute = 'PRICING_ATTRIBUTE3') THEN
264 v_value := p_quantity;
265 ELSE
266 v_value := p_quantity * p_list_price ;
267 END IF;
268
269 --dbms_output.put_line('Value is : ' || v_value);
270 --dbms_output.put_line('Value From :' || j.pricing_attr_value_from);
271 --dbms_output.put_line('Value To :' || j.pricing_attr_value_to);
272
273 IF (v_value > nvl(j.pricing_attr_value_from , v_value) AND
274 v_value < nvl(j.pricing_attr_value_to, v_value)) THEN
275
276 --dbms_output.put_line('Value1 is : ' || v_value);
277
278 v_discount_line_rec.p_discount_id := i.list_header_id;
279 v_discount_line_rec.p_discount_name := i.name;
280 v_discount_line_rec.p_discount_line_id := j.list_line_id;
281
282 --dbms_output.put_line('Arithmetic Operator: ' || j.arithmetic_operator);
283 --dbms_output.put_line('Operand: ' || j.operand);
284
285 IF (j.arithmetic_operator = 'AMT') THEN
286 v_discount_line_rec.p_discount_percent := nvl(j.operand/p_list_price * 100,0);
287 ELSIF (j.arithmetic_operator = '%') THEN
288 v_discount_line_rec.p_discount_percent := nvl(j.operand,0);
289 ELSIF (j.arithmetic_operator = 'NEWPRICE') THEN
290 v_discount_line_rec.p_discount_percent := p_list_price - nvl(j.operand,0);
291 END IF;
292
293 IF (p_asc_desc_flag = 'A') THEN
294 IF (v_discount_line_rec.p_discount_percent < p_best_adj_percent) THEN
295 cnt := cnt + 1;
296 v_discount_lines_tbl(cnt) := v_discount_line_rec;
297 END IF;
298 ELSE
299 IF (v_discount_line_rec.p_discount_percent > p_best_adj_percent) THEN
300 cnt := cnt + 1;
301 v_discount_lines_tbl(cnt) := v_discount_line_rec;
302 END IF;
303 END IF;
304 END IF;
305 END LOOP;
306 END IF;
307 END LOOP;
308 --x_discount_lines_tbl := v_discount_lines_tbl;
309
310 --dbms_output.put_line('Hellooooo');
311 IF (v_discount_lines_tbl.COUNT > 0) THEN
312
313 --Delete all the records
314 DELETE FROM QP_DISCOUNTS_UPG_TEMP;
315
316 v_index := v_discount_lines_tbl.FIRST;
317 LOOP
318 --dbms_output.put_line('Discount Id : ' || v_discount_lines_tbl(v_index).p_discount_id);
319 --dbms_output.put_line('Discount Line Id : ' || v_discount_lines_tbl(v_index).p_discount_line_id);
320 --dbms_output.put_line('Discount Name : ' || v_discount_lines_tbl(v_index).p_discount_name);
321 --dbms_output.put_line('Discount Percent : ' || v_discount_lines_tbl(v_index).p_discount_percent);
322
323
324 INSERT INTO QP_DISCOUNTS_UPG_TEMP VALUES(v_discount_lines_tbl(v_index).p_discount_id,
325 v_discount_lines_tbl(v_index).p_discount_name,
326 v_discount_lines_tbl(v_index).p_discount_line_id,
327 v_discount_lines_tbl(v_index).p_discount_percent);
328 EXIT WHEN v_index = v_discount_lines_tbl.LAST;
329 v_index := v_discount_lines_tbl.NEXT(v_index);
330 END LOOP;
331
332 -- Get the first best discount based on the p_asc_desc_flag 'A'- Ascending , 'D' - Descending
333 IF (p_asc_desc_flag = 'A') THEN
334 SELECT discount_id , discount_name , discount_line_id, discount_percent
335 INTO x_discount_rec.p_discount_id,
336 x_discount_rec.p_discount_name,
337 x_discount_rec.p_discount_line_id,
338 x_discount_rec.p_discount_percent
339 FROM QP_DISCOUNTS_UPG_TEMP
340 WHERE DISCOUNT_PERCENT = (SELECT min(DISCOUNT_PERCENT) FROM QP_DISCOUNTS_UPG_TEMP)
341 AND ROWNUM = 1;
342 ELSE
343 SELECT discount_id , discount_name , discount_line_id, discount_percent
344 INTO x_discount_rec.p_discount_id,
345 x_discount_rec.p_discount_name,
346 x_discount_rec.p_discount_line_id,
347 x_discount_rec.p_discount_percent
348 FROM QP_DISCOUNTS_UPG_TEMP
349 WHERE DISCOUNT_PERCENT = (SELECT max(DISCOUNT_PERCENT) FROM QP_DISCOUNTS_UPG_TEMP)
350 AND ROWNUM = 1;
351 END IF;
352 ELSE
353 x_discount_rec.p_discount_id := NULL;
354 x_discount_rec.p_discount_name := NULL;
355 x_discount_rec.p_discount_line_id := NULL;
356 x_discount_rec.p_discount_percent := NULL;
357 END IF;
358
359 x_discount_line_rec := x_discount_rec;
360
361 --dbms_output.put_line(x_discount_rec.p_discount_id);
362 --dbms_output.put_line(x_discount_rec.p_discount_name);
363 --dbms_output.put_line(x_discount_rec.p_discount_line_id);
364 --dbms_output.put_line(x_discount_rec.p_discount_percent);
365
366 EXCEPTION
367 WHEN OTHERS THEN
368 err_num := SQLCODE;
369 err_msg := SUBSTR(SQLERRM, 1, 100);
370 --DBMS_OUTPUT.PUT_LINE('err_num ' || err_num);
371 --DBMS_OUTPUT.PUT_LINE('err_msg ' || err_msg);
372
373
374 END Get_Discount_Lines;
375
376 END QP_Pricing_Engine_PVT;