1 PACKAGE BODY QP_Calculate_Price_PUB AS
2 /* $Header: QPXPCLPB.pls 120.22.12010000.2 2008/10/15 14:32:22 dnema ship $ */
3
4 l_debug VARCHAR2(3);
5 l_line_index NUMBER; -- for accum range break
6 l_prev_list_line_id NUMBER; -- accum range break
7 l_accum_global NUMBER; -- accum range break
8 l_prev_order_id NUMBER; -- accum range break
9 l_prev_line_index NUMBER; -- accum range break
10 l_accum_context_cache QP_LIST_LINES.ACCUM_CONTEXT%TYPE; -- accum range break
11 l_accum_attrib_cache QP_LIST_LINES.ACCUM_ATTRIBUTE%TYPE; -- accum range break
12 l_accum_flag_cache QP_LIST_LINES.ACCUM_ATTR_RUN_SRC_FLAG%TYPE; -- accum range break
13 l_accum_list_line_no_cache QP_LIST_LINES.LIST_LINE_NO%TYPE; -- accum range break
14
15
16 --4900095
17 G_Lumpsum_Qty NUMBER;
18 --private function to return service quantity
19 FUNCTION Get_lumpsum_qty(p_line_index NUMBER, p_line_detail_index NUMBER
20 , p_modifier_level_code VARCHAR2)
21 RETURN NUMBER IS
22 x_lumpsum_qty NUMBER;
23 BEGIN
24 l_debug := QP_PREQ_GRP.G_DEBUG_ENGINE;
25 IF l_debug = FND_API.G_TRUE THEN
26 QP_PREQ_GRP.engine_debug('In Get_Lumpsum_Qty ..., QPXPCLPB.pls');
27 QP_PREQ_GRP.engine_debug('line_index '|| p_line_index
28 ||' line_dtl_index '|| p_line_detail_index
29 ||' level '|| p_modifier_level_code);
30 IF QP_PREQ_GRP.G_service_line_qty_tbl.EXISTS(p_line_index) THEN
31 QP_PREQ_GRP.engine_debug('line_index qty '
32 || QP_PREQ_GRP.G_service_line_qty_tbl(p_line_index));
33 END IF; --QP_PREQ_GRP.G_service_line_qty_tbl
34 IF QP_PREQ_GRP.G_service_line_qty_tbl.EXISTS(p_line_detail_index) THEN
35 QP_PREQ_GRP.engine_debug('line_detail_index qty '
36 || QP_PREQ_GRP.G_service_line_qty_tbl(p_line_detail_index));
37 END IF; --QP_PREQ_GRP.G_service_line_qty_tbl
38 END IF;
39 --4900095
40 IF (QP_PREQ_GRP.G_service_line_qty_tbl.EXISTS(p_line_index)
41 OR QP_PREQ_GRP.G_service_ldet_qty_tbl.EXISTS(p_line_detail_index)) THEN
42 IF p_modifier_level_code = QP_PREQ_GRP.G_LINE_GROUP THEN
43 x_lumpsum_qty :=
44 nvl(QP_PREQ_PUB.G_Service_pbh_lg_amt_qty(p_line_detail_index),
45 QP_PREQ_GRP.G_service_ldet_qty_tbl(p_line_detail_index));
46 ELSIF p_modifier_level_code = QP_PREQ_GRP.G_LINE_LEVEL THEN
47 x_lumpsum_qty :=
48 QP_PREQ_GRP.G_service_line_qty_tbl(p_line_index);
49 END IF; --p_modifier_level_code
50 END IF; --QP_PREQ_GRP.G_service_line_qty_tbl
51
52 IF l_debug = FND_API.G_TRUE THEN
53 QP_PREQ_GRP.engine_debug('Get_Lumpsum_Qty '|| x_lumpsum_qty);
54 END IF;
55
56 RETURN x_lumpsum_qty;
57 EXCEPTION
58 WHEN OTHERS THEN
59 RETURN NULL;
60 END Get_lumpsum_qty;
61
62 -- Public Calculate_List_Price
63 PROCEDURE Calculate_List_Price
64 (p_operand_calc_code VARCHAR2,
65 p_operand_value NUMBER,
66 p_request_qty NUMBER,
67 p_rltd_item_price NUMBER,
68 p_service_duration NUMBER,
69 p_rounding_flag VARCHAR2,
70 p_rounding_factor NUMBER,
71 x_list_price OUT NOCOPY NUMBER,
72 x_percent_price OUT NOCOPY NUMBER,
73 x_return_status OUT NOCOPY VARCHAR2,
74 x_return_status_txt OUT NOCOPY VARCHAR2) AS
75
76 v_routine_name CONSTANT VARCHAR2(240) := 'Routine:QP_Calculate_Price_PUB.Calculate_List_Price';
77 v_price_round_options VARCHAR2(30) := NULL; --shu, new rounding
78
79 BEGIN
80 l_debug := QP_PREQ_GRP.G_DEBUG_ENGINE;
81 x_return_status := FND_API.G_RET_STS_SUCCESS;
82 IF l_debug = FND_API.G_TRUE THEN
83 QP_PREQ_GRP.engine_debug('In Calculate_List_Price ..., QPXPCLPB.pls');
84 END IF;
85 IF (p_operand_calc_code = QP_PREQ_GRP.G_UNIT_PRICE) THEN
86 x_list_price := p_operand_value * p_service_duration;
87 ELSIF(p_operand_calc_code = QP_PREQ_GRP.G_BLOCK_PRICE) THEN -- block pricing
88 IF (p_request_qty = 0) THEN
89 x_list_price := 0;
90 ELSE
91 IF l_debug = FND_API.G_TRUE THEN
92 qp_preq_grp.engine_debug('In Calculate_List_Price #1');
93 END IF;
94 x_list_price := (p_operand_value / p_request_qty);
95 IF l_debug = FND_API.G_TRUE THEN
96 qp_preq_grp.engine_debug('Request Qty: ' || p_request_qty);
97 qp_preq_grp.engine_debug('List Price: ' || x_list_price);
98 END IF;
99 END IF;
100 ELSIF(p_operand_calc_code = QP_PREQ_GRP.G_PERCENT_PRICE) THEN
101 x_list_price := (p_operand_value / 100) * p_rltd_item_price * p_service_duration;
102 x_percent_price := p_operand_value; -- service duration is not applicable here.Discussion with Alison
103 END IF;
104
105 -- ravi passed (-1)*request_line.rounding_factor when calling this procedure, therefore needs to *(-1)
106 -- to print the original rounding_factor
107 IF l_debug = FND_API.G_TRUE THEN
108 QP_PREQ_GRP.engine_debug('In procedure Calculate_List_Price ...');
109 QP_PREQ_GRP.engine_debug('G_ROUNDING_FLAG: '|| QP_PREQ_GRP.G_ROUNDING_FLAG); -- aso rounding, shu, 2457629
110 QP_PREQ_GRP.engine_debug('rounding_factor: '|| ( - 1) * p_rounding_factor); -- ravi passed (-1)*request_line.rounding_factor
111
112 END IF;
113 IF (nvl(QP_PREQ_GRP.G_ROUNDING_FLAG, QP_PREQ_GRP.G_YES) = QP_PREQ_GRP.G_YES AND p_rounding_factor IS NOT NULL) THEN
114
115 x_list_price := ROUND(x_list_price, p_rounding_factor);
116 x_percent_price := ROUND(x_percent_price, p_rounding_factor);
117
118 ELSIF (QP_PREQ_GRP.G_ROUNDING_FLAG = G_CHAR_Q) THEN -- check v_selling_price_rounding_options profile
119 IF l_debug = FND_API.G_TRUE THEN
120 QP_PREQ_GRP.engine_debug('are we here 2?');
121 END IF;
122 v_price_round_options := nvl(FND_PROFILE.VALUE('QP_SELLING_PRICE_ROUNDING_OPTIONS'), QP_Calculate_Price_PUB.G_NO_ROUND); --shu, new rounding
123 IF l_debug = FND_API.G_TRUE THEN
124 QP_PREQ_GRP.engine_debug('selling_price_rounding_options: '|| v_price_round_options );
125 END IF;
126 IF (v_price_round_options = G_ROUND_ADJ AND p_rounding_factor IS NOT NULL) THEN
127 x_list_price := ROUND(x_list_price, p_rounding_factor);
128 x_percent_price := ROUND(x_percent_price, p_rounding_factor);
129 END IF;
130 END IF;
131
132 IF l_debug = FND_API.G_TRUE THEN
133 QP_PREQ_GRP.engine_debug('x_list_price' || x_list_price);
134 END IF;
135 -- end shu new rounding
136 EXCEPTION
137 WHEN OTHERS THEN
138 IF l_debug = FND_API.G_TRUE THEN
139 QP_PREQ_GRP.engine_debug(v_routine_name || ' ' || SQLERRM);
140 END IF;
141 x_return_status := FND_API.G_RET_STS_ERROR;
142 x_return_status_txt := v_routine_name || ' ' || SQLERRM;
143 END Calculate_List_Price;
144
145 -- Local to the calculation Engine
146 PROCEDURE Calculate_List_Price_PVT
147 (p_operand_calc_code VARCHAR2,
148 p_operand_value NUMBER,
149 p_recurring_value NUMBER, -- block pricing
150 p_request_qty NUMBER,
151 p_rltd_item_price NUMBER,
152 p_service_duration NUMBER,
153 p_rounding_flag VARCHAR2,
154 p_rounding_factor NUMBER,
155 x_list_price OUT NOCOPY NUMBER,
156 x_percent_price OUT NOCOPY NUMBER,
157 x_extended_price OUT NOCOPY NUMBER, -- block pricing
158 x_return_status OUT NOCOPY VARCHAR2,
159 x_return_status_txt OUT NOCOPY VARCHAR2) AS
160
161 v_routine_name CONSTANT VARCHAR2(240) := 'Routine:QP_Calculate_Price_PUB.Calculate_List_Price_PVT';
162 v_price_round_options VARCHAR2(30) := NULL; --shu, new rounding
163
164 BEGIN
165 l_debug := QP_PREQ_GRP.G_DEBUG_ENGINE;
166 x_return_status := FND_API.G_RET_STS_SUCCESS;
167 IF l_debug = FND_API.G_TRUE THEN
168 QP_PREQ_GRP.engine_debug('In Calculate_List_Price_PVT..., QPXPCLPB.pls');
169 END IF;
170 IF (p_operand_calc_code IN (QP_PREQ_GRP.G_UNIT_PRICE, QP_PREQ_GRP.G_BREAKUNIT_PRICE)) THEN
171 x_list_price := p_operand_value * p_service_duration;
172 ELSIF(p_operand_calc_code = QP_PREQ_GRP.G_BLOCK_PRICE) THEN -- block pricing
173 IF (p_request_qty = 0) THEN
174 x_list_price := 0;
175 x_extended_price := p_operand_value;
176 ELSE
177 IF (p_recurring_value IS NULL) THEN
178 IF l_debug = FND_API.G_TRUE THEN
179 qp_preq_grp.engine_debug('In Calculate_List_Price_PVT #1');
180 END IF;
181 x_list_price := (p_operand_value / p_request_qty);
182 x_extended_price := p_operand_value;
183 IF l_debug = FND_API.G_TRUE THEN
184 qp_preq_grp.engine_debug('Extended Price #1 : ' || x_extended_price);
185 qp_preq_grp.engine_debug('Request Qty: ' || p_request_qty);
186 qp_preq_grp.engine_debug('List Price: ' || x_list_price);
187 END IF;
188 ELSE
189 IF l_debug = FND_API.G_TRUE THEN
190 qp_preq_grp.engine_debug('In Calculate_List_Price_PVT #2');
191 END IF;
192 x_list_price := p_operand_value / p_recurring_value;
193 x_extended_price := x_list_price * p_request_qty;
194 IF l_debug = FND_API.G_TRUE THEN
195 qp_preq_grp.engine_debug('Extended Price #2: ' || x_extended_price);
196 END IF;
197 END IF;
198 END IF;
199 ELSIF(p_operand_calc_code = QP_PREQ_GRP.G_PERCENT_PRICE) THEN
200 x_list_price := (p_operand_value / 100) * p_rltd_item_price * p_service_duration;
201 x_percent_price := p_operand_value; -- service duration is not applicable here.Discussion with Alison
202 END IF;
203
204 -- ravi passed (-1)*request_line.rounding_factor when calling this procedure, therefore needs to *(-1)
205 -- to print the original rounding_factor
206 IF l_debug = FND_API.G_TRUE THEN
207 QP_PREQ_GRP.engine_debug('In procedure Calculate_List_Price_PVT...');
208 QP_PREQ_GRP.engine_debug('G_ROUNDING_FLAG: '|| QP_PREQ_GRP.G_ROUNDING_FLAG); -- aso rounding, shu, 2457629
209 QP_PREQ_GRP.engine_debug('rounding_factor: '|| ( - 1) * p_rounding_factor); -- ravi passed (-1)*request_line.rounding_factor
210
211 END IF;
212 IF (nvl(QP_PREQ_GRP.G_ROUNDING_FLAG, QP_PREQ_GRP.G_YES) = QP_PREQ_GRP.G_YES AND p_rounding_factor IS NOT NULL) THEN
213
214 x_list_price := ROUND(x_list_price, p_rounding_factor);
215 x_percent_price := ROUND(x_percent_price, p_rounding_factor);
216
217 ELSIF (QP_PREQ_GRP.G_ROUNDING_FLAG = G_CHAR_Q) THEN -- check v_selling_price_rounding_options profile
218
219 v_price_round_options := nvl(FND_PROFILE.VALUE('QP_SELLING_PRICE_ROUNDING_OPTIONS'), QP_Calculate_Price_PUB.G_NO_ROUND); --shu, new rounding
220 IF l_debug = FND_API.G_TRUE THEN
221 QP_PREQ_GRP.engine_debug('selling_price_rounding_options: '|| v_price_round_options );
222 END IF;
223
224 IF (v_price_round_options = G_ROUND_ADJ AND p_rounding_factor IS NOT NULL) THEN
225 x_list_price := ROUND(x_list_price, p_rounding_factor);
226 x_percent_price := ROUND(x_percent_price, p_rounding_factor);
227 END IF;
228 END IF; -- end rounding stuff
229
230 IF l_debug = FND_API.G_TRUE THEN
231 QP_PREQ_GRP.engine_debug('x_list_price' || x_list_price);
232 END IF;
233 -- end shu new rounding
234 EXCEPTION
235 WHEN OTHERS THEN
236 IF l_debug = FND_API.G_TRUE THEN
237 QP_PREQ_GRP.engine_debug(v_routine_name || ' ' || SQLERRM);
238 END IF;
239 x_return_status := FND_API.G_RET_STS_ERROR;
240 x_return_status_txt := v_routine_name || ' ' || SQLERRM;
241 END Calculate_List_Price_PVT;
242
243 PROCEDURE Calculate_Adjusted_Price
244 (p_list_price NUMBER,
245 p_discounted_price NUMBER,
246 p_old_pricing_sequence NUMBER,
247 p_new_pricing_sequence NUMBER,
248 p_operand_calc_code VARCHAR2,
249 p_operand_value NUMBER,
250 p_list_line_type VARCHAR2,
251 p_request_qty NUMBER,
252 p_accrual_flag VARCHAR2,
253 p_rounding_flag VARCHAR2,
254 p_rounding_factor NUMBER,
255 p_orig_unit_price NUMBER,
256 x_discounted_price OUT NOCOPY NUMBER,
257 x_adjusted_amount OUT NOCOPY NUMBER,
258 x_list_price OUT NOCOPY NUMBER,
259 x_return_status OUT NOCOPY VARCHAR2,
260 x_return_status_txt OUT NOCOPY VARCHAR2) AS
261
262 v_list_price NUMBER;
263 v_routine_name CONSTANT VARCHAR2(240) := 'Routine:QP_Calculate_Price_PUB.Calculate_Adjusted_Price';
264 v_price_round_options VARCHAR2(30) := NULL; --shu, new rounding
265
266 BEGIN
267 l_debug := QP_PREQ_GRP.G_DEBUG_ENGINE;
268 IF l_debug = FND_API.G_TRUE THEN
269 QP_PREQ_GRP.engine_debug('In Calculate_Adjusted_Price...');
270 END IF;
271 v_price_round_options := FND_PROFILE.VALUE('QP_SELLING_PRICE_ROUNDING_OPTIONS'); --shu, new rounding
272 IF l_debug = FND_API.G_TRUE THEN
273 QP_PREQ_GRP.engine_debug('selling_price_rounding_options: '|| v_price_round_options );
274 QP_PREQ_GRP.engine_debug('G_ROUNDING_FLAG: '|| QP_PREQ_GRP.G_ROUNDING_FLAG); -- aso rounding, shu, 2457629
275 END IF;
276 -- ravi passed (-1)*request_line.rounding_factor when calling this procedure, therefore needs to *(-1)
277 -- to print the original rounding_factor
278 IF l_debug = FND_API.G_TRUE THEN
279 QP_PREQ_GRP.engine_debug('rounding_factor: '|| ( - 1) * p_rounding_factor); -- ravi passed (-1)*request_line.rounding_factor
280 END IF;
281
282 x_return_status := FND_API.G_RET_STS_SUCCESS;
283 IF (p_old_pricing_sequence = p_new_pricing_sequence AND p_new_pricing_sequence IS NOT NULL) THEN
284 IF l_debug = FND_API.G_TRUE THEN
285 QP_PREQ_GRP.engine_debug('I am in Calculate Adjusted Price');
286 END IF;
287
288 -- G_BREAKUNIT_PRICE added for FTE breakunit
289 IF (p_operand_calc_code IN (QP_PREQ_GRP.G_AMOUNT_DISCOUNT, QP_PREQ_GRP.G_BREAKUNIT_PRICE)) THEN
290
291 IF (p_list_line_type = QP_PREQ_GRP.G_DISCOUNT) THEN
292 x_adjusted_amount := - (p_operand_value);
293 IF (p_accrual_flag = QP_PREQ_GRP.G_YES) THEN
294 x_discounted_price := p_discounted_price;
295 ELSE
296 x_discounted_price := p_discounted_price - p_operand_value;
297 END IF; -- end if p_accrual_flag
298
299 ELSIF (p_list_line_type = QP_PREQ_GRP.G_SURCHARGE) THEN
300 x_adjusted_amount := p_operand_value;
301 x_discounted_price := p_discounted_price + p_operand_value;
302
303 ELSIF (p_list_line_type = QP_PREQ_GRP.G_FREIGHT_CHARGE) THEN
304 x_discounted_price := p_discounted_price;
305 x_adjusted_amount := p_operand_value;
306 END IF; -- end if p_list_line_type
307
308 ELSIF (p_operand_calc_code = QP_PREQ_GRP.G_PERCENT_DISCOUNT) THEN
309
310 IF (p_list_line_type = QP_PREQ_GRP.G_DISCOUNT) THEN
311 IF (p_accrual_flag = QP_PREQ_GRP.G_YES) THEN
312 x_discounted_price := p_discounted_price;
313 x_adjusted_amount := - (abs(p_list_price) * (p_operand_value / 100));
314 ELSE
315 --IF (v_round_individual_adj_flag = QP_PREQ_GRP.G_YES AND p_rounding_factor IS NOT NULL) THEN -- shu
316 IF (nvl(QP_PREQ_GRP.G_ROUNDING_FLAG, QP_PREQ_GRP.G_YES) = QP_PREQ_GRP.G_YES AND p_rounding_factor IS NOT NULL) THEN -- shu, new rounding
317 x_discounted_price := p_discounted_price - ROUND((abs(p_list_price) * (p_operand_value / 100)), p_rounding_factor);
318 ELSIF (QP_PREQ_GRP.G_ROUNDING_FLAG = G_CHAR_Q) THEN -- check qp_selling_price_rounding_options profile
322 ELSE -- QP_PREQ_GRP.G_ROUNDING_FLAG ='N' or 'U', un-round adjs
319 IF (v_price_round_options = G_ROUND_ADJ AND p_rounding_factor IS NOT NULL) THEN -- do not round if profile is null
320 x_discounted_price := p_discounted_price - ROUND((abs(p_list_price) * (p_operand_value / 100)), p_rounding_factor);
321 END IF;
323 x_discounted_price := p_discounted_price - (abs(p_list_price) * (p_operand_value / 100));
324 END IF; -- end if QP_PREQ_GRP.G_ROUNDING_FLAG
325
326 x_adjusted_amount := - (abs(p_list_price) * (p_operand_value / 100));
327 END IF; -- end if p_accrual_flag
328
329 ELSIF (p_list_line_type = QP_PREQ_GRP.G_SURCHARGE) THEN
330
331 --IF (v_round_individual_adj_flag = QP_PREQ_GRP.G_YES AND p_rounding_factor IS NOT NULL) THEN -- shu
332 IF (nvl(QP_PREQ_GRP.G_ROUNDING_FLAG, QP_PREQ_GRP.G_YES) = QP_PREQ_GRP.G_YES AND p_rounding_factor IS NOT NULL) THEN -- shu, new rounding
333 x_discounted_price := p_discounted_price + ROUND((abs(p_list_price) * (p_operand_value / 100)), p_rounding_factor);
334 ELSIF (QP_PREQ_GRP.G_ROUNDING_FLAG = G_CHAR_Q) THEN -- check qp_selling_price_rounding_options profile
335 IF (v_price_round_options = G_ROUND_ADJ AND p_rounding_factor IS NOT NULL) THEN -- do not round if profile is null
336 x_discounted_price := p_discounted_price + ROUND((abs(p_list_price) * (p_operand_value / 100)), p_rounding_factor);
337 END IF;
338 ELSE -- QP_PREQ_GRP.G_ROUNDING_FLAG ='N' or 'U', un-round adjs
339 x_discounted_price := p_discounted_price + (abs(p_list_price) * (p_operand_value / 100));
340 END IF; -- end if QP_PREQ_GRP.G_ROUNDING_FLAG
341
342 x_adjusted_amount := abs(p_list_price) * (p_operand_value / 100);
343
344 ELSIF (p_list_line_type = QP_PREQ_GRP.G_FREIGHT_CHARGE) THEN
345 x_discounted_price := p_discounted_price;
346 x_adjusted_amount := abs(p_list_price) * (p_operand_value / 100);
347 END IF; -- end if p_list_line_type
348
349 ELSIF(p_operand_calc_code = QP_PREQ_GRP.G_NEWPRICE_DISCOUNT) THEN
350
351 -- For NewPrice Calculation is same for Discount and Surcharge
352 IF (p_list_line_type IN (QP_PREQ_GRP.G_DISCOUNT, QP_PREQ_GRP.G_SURCHARGE)) THEN
353 x_discounted_price := p_operand_value;
354 x_adjusted_amount := - (p_list_price - p_operand_value);
355 --fix bug 2353905
356 --Negative list price can get correct adjusted_amount
357 END IF;
358
359 ELSIF (p_operand_calc_code = QP_PREQ_GRP.G_LUMPSUM_DISCOUNT) THEN
360
361 IF (p_list_line_type = QP_PREQ_GRP.G_DISCOUNT) THEN
362
363 IF (p_accrual_flag = QP_PREQ_GRP.G_YES) THEN
364 x_discounted_price := p_discounted_price;
365 IF (p_request_qty <> 0) THEN -- ask ravi, how about null?
366 x_adjusted_amount := - (p_operand_value / p_request_qty);
367 ELSE
368 x_adjusted_amount := 0; -- bug2385874, should be 0 instead of -p_operand_value
369 END IF; -- end if p_request_qty
370 ELSE -- else if p_accural_flag
371
372 IF (p_request_qty = 0) THEN -- ask ravi how about null??
373 x_discounted_price := p_discounted_price;
374 x_adjusted_amount := 0; --bug2385874, should be 0 instead of p_operand_value
375 ELSE
376
377 --IF (v_round_individual_adj_flag = QP_PREQ_GRP.G_YES AND p_rounding_factor IS NOT NULL) THEN -- shu
378 IF (nvl(QP_PREQ_GRP.G_ROUNDING_FLAG, QP_PREQ_GRP.G_YES) = QP_PREQ_GRP.G_YES AND p_rounding_factor IS NOT NULL) THEN -- shu, new rounding
379 --4900095 added nvl to G_Lumpsum_qty
380 x_discounted_price := p_discounted_price - ROUND((p_operand_value / nvl(G_Lumpsum_qty, p_request_qty)), p_rounding_factor);
381 ELSIF (QP_PREQ_GRP.G_ROUNDING_FLAG = G_CHAR_Q) THEN -- check qp_selling_price_rounding_options profile
382 IF (v_price_round_options = G_ROUND_ADJ AND p_rounding_factor IS NOT NULL) THEN -- do not round if profile is null
383 --4900095 added nvl to G_Lumpsum_qty
384 x_discounted_price := p_discounted_price - ROUND((p_operand_value / nvl(G_Lumpsum_qty, p_request_qty)), p_rounding_factor);
385 END IF;
386 ELSE -- QP_PREQ_GRP.G_ROUNDING_FLAG ='N' or 'U', un-round adjs
387 --4900095 added nvl to G_Lumpsum_qty
391 x_adjusted_amount := - (p_operand_value / nvl(G_Lumpsum_qty, p_request_qty));
388 x_discounted_price := p_discounted_price - (p_operand_value / nvl(G_Lumpsum_Qty, p_request_qty));
389 END IF; -- end if QP_PREQ_GRP.G_ROUNDING_FLAG
390 --4900095 added nvl to G_Lumpsum_qty
392
393 END IF; -- end if p_request_qty
394 END IF; -- end if p_accural_flag
395
396 ELSIF (p_list_line_type = QP_PREQ_GRP.G_SURCHARGE) THEN
397
398 IF (p_accrual_flag = QP_PREQ_GRP.G_YES) THEN
399
400 x_discounted_price := p_discounted_price;
401 IF (p_request_qty <> 0) THEN -- ask ravi, how about null?
402 --4900095 added nvl to G_Lumpsum_qty
403 x_adjusted_amount := (p_operand_value / nvl(G_Lumpsum_qty, p_request_qty));
404 ELSE
405 --x_adjusted_amount := p_operand_value;
406 x_adjusted_amount := 0; --bug2385874, should be 0 instead of p_operand_value
407 END IF;
408
409 ELSE -- else if p_accrual_flag
410
411 IF (p_request_qty = 0) THEN
412 x_discounted_price := p_discounted_price;
413 --x_adjusted_amount := p_operand_value;
414 x_adjusted_amount := 0; --bug2385874, should be 0 instead of p_operand_value
415 ELSE
416 --IF (v_round_individual_adj_flag = QP_PREQ_GRP.G_YES AND p_rounding_factor IS NOT NULL) THEN -- shu
417 IF (nvl(QP_PREQ_GRP.G_ROUNDING_FLAG, QP_PREQ_GRP.G_YES) = QP_PREQ_GRP.G_YES AND p_rounding_factor IS NOT NULL) THEN -- shu, new rounding
418 --4900095 added nvl to G_Lumpsum_qty
419 x_discounted_price := p_discounted_price + ROUND((p_operand_value / nvl(G_Lumpsum_qty, p_request_qty)), p_rounding_factor);
420 ELSIF (QP_PREQ_GRP.G_ROUNDING_FLAG = G_CHAR_Q) THEN -- check qp_selling_price_rounding_options profile
421 IF (v_price_round_options = G_ROUND_ADJ AND p_rounding_factor IS NOT NULL) THEN -- do not round if profile is null
422 --4900095 added nvl to G_Lumpsum_qty
423 x_discounted_price := p_discounted_price + ROUND((p_operand_value / nvl(G_Lumpsum_Qty, p_request_qty)), p_rounding_factor);
424 END IF;
425 ELSE -- QP_PREQ_GRP.G_ROUNDING_FLAG ='N' or 'U', un-round adjs
426 --4900095 added nvl to G_Lumpsum_qty
427 x_discounted_price := p_discounted_price + (p_operand_value / nvl(G_Lumpsum_Qty, p_request_qty));
428 END IF; -- end if QP_PREQ_GRP.G_ROUNDING_FLAG
429
430 --4900095 added nvl to G_Lumpsum_qty
431 x_adjusted_amount := (p_operand_value / nvl(G_Lumpsum_Qty, p_request_qty));
432 END IF; -- end if p_request_qty
433 END IF; -- end if p_accrual_flag
434
435 ELSIF (p_list_line_type = QP_PREQ_GRP.G_FREIGHT_CHARGE) THEN
436
437 IF l_debug = FND_API.G_TRUE THEN
438 QP_PREQ_GRP.engine_debug('Freight Charge');
439 QP_PREQ_GRP.engine_debug('Discounted Price : ' || p_discounted_price);
440 END IF;
441 x_discounted_price := p_discounted_price;
442
443 IF (p_request_qty = 0) THEN
444 x_adjusted_amount := p_operand_value;
445 ELSE
446 --4900095 added nvl to G_Lumpsum_qty
447 x_adjusted_amount := p_operand_value / nvl(G_Lumpsum_Qty, p_request_qty);
448 IF l_debug = FND_API.G_TRUE THEN
449 QP_PREQ_GRP.engine_debug('Adjusted Amount: ' || x_adjusted_amount);
450 END IF;
451 END IF; -- end IF p_request_qty
452
453 END IF; -- end if p_list_line_type = G_FREIGHT_CHARGE
454
455 END IF; -- end if p_operand_calc_code
456
457 x_list_price := p_list_price;
458
459 ELSE -- else IF (p_old_pricing_sequence = p_new_pricing_sequence AND p_new_pricing_sequence IS NOT NULL)
460 -- On Sequence Change , the base price changes to the current discount price
461
462 IF (p_new_pricing_sequence IS NULL) THEN
463 v_list_price := p_orig_unit_price;
464 ELSE
465 v_list_price := p_discounted_price;
466 END IF;
467
468 -- G_BREAKUNIT_PRICE added for FTE breakunit
469 IF (p_operand_calc_code IN (QP_PREQ_GRP.G_AMOUNT_DISCOUNT, QP_PREQ_GRP.G_BREAKUNIT_PRICE)) THEN
470
471 IF (p_list_line_type = QP_PREQ_GRP.G_DISCOUNT) THEN
472
473 IF(p_accrual_flag = QP_PREQ_GRP.G_YES) THEN
474 x_discounted_price := p_discounted_price;
475 x_adjusted_amount := - (p_operand_value) ;
476 ELSE
477 x_discounted_price := p_discounted_price - p_operand_value;
478 x_adjusted_amount := - (p_operand_value) ;
479 END IF; -- end if p_accrual_flag
480
481 ELSIF (p_list_line_type = QP_PREQ_GRP.G_SURCHARGE) THEN
482
483 x_discounted_price := p_discounted_price + p_operand_value;
484 x_adjusted_amount := p_operand_value;
485
486 ELSIF (p_list_line_type = QP_PREQ_GRP.G_FREIGHT_CHARGE) THEN
487 x_discounted_price := p_discounted_price;
488 x_adjusted_amount := p_operand_value;
489 END IF; -- end if p_list_line_type
490
491 ELSIF (p_operand_calc_code = QP_PREQ_GRP.G_PERCENT_DISCOUNT) THEN
492
493 IF (p_list_line_type = QP_PREQ_GRP.G_DISCOUNT) THEN
494
495 IF(p_accrual_flag = QP_PREQ_GRP.G_YES) THEN
499 --IF (v_round_individual_adj_flag = QP_PREQ_GRP.G_YES AND p_rounding_factor IS NOT NULL) THEN -- shu
496 x_discounted_price := p_discounted_price;
497 x_adjusted_amount := - (abs(v_list_price) * (p_operand_value / 100));
498 ELSE
500 IF (nvl(QP_PREQ_GRP.G_ROUNDING_FLAG, QP_PREQ_GRP.G_YES) = QP_PREQ_GRP.G_YES AND p_rounding_factor IS NOT NULL) THEN -- shu, new rounding
501 x_discounted_price := p_discounted_price - ROUND((abs(v_list_price) * (p_operand_value / 100)), p_rounding_factor);
502 ELSIF (QP_PREQ_GRP.G_ROUNDING_FLAG = G_CHAR_Q) THEN -- check qp_selling_price_rounding_options profile
503 IF (v_price_round_options = G_ROUND_ADJ AND p_rounding_factor IS NOT NULL) THEN -- do not round if profile is null
504 x_discounted_price := p_discounted_price - ROUND((abs(v_list_price) * (p_operand_value / 100)), p_rounding_factor);
505 END IF;
506 ELSE -- QP_PREQ_GRP.G_ROUNDING_FLAG ='N' or 'U', un-round adjs
507 x_discounted_price := p_discounted_price - (abs(v_list_price) * (p_operand_value / 100));
508 END IF; -- end if QP_PREQ_GRP.G_ROUNDING_FLAG
509
510 --x_discounted_price := p_discounted_price - (abs(v_list_price) *(p_operand_value/100));
511 x_adjusted_amount := - (abs(v_list_price) * (p_operand_value / 100));
512
513 END IF; -- end if p_accrual_flag
514
515 ELSIF (p_list_line_type = QP_PREQ_GRP.G_SURCHARGE) THEN -- the sign along with value
516
517 --IF (v_round_individual_adj_flag = QP_PREQ_GRP.G_YES AND p_rounding_factor IS NOT NULL) THEN -- shu
518 IF (nvl(QP_PREQ_GRP.G_ROUNDING_FLAG, QP_PREQ_GRP.G_YES) = QP_PREQ_GRP.G_YES AND p_rounding_factor IS NOT NULL) THEN -- shu, new rounding
519 x_discounted_price := p_discounted_price + ROUND((abs(v_list_price) * (p_operand_value / 100)), p_rounding_factor);
520 ELSIF (QP_PREQ_GRP.G_ROUNDING_FLAG = G_CHAR_Q) THEN -- check qp_selling_price_rounding_options profile
521 IF (v_price_round_options = G_ROUND_ADJ AND p_rounding_factor IS NOT NULL) THEN -- do not round if profile is null
522 x_discounted_price := p_discounted_price + ROUND((abs(v_list_price) * (p_operand_value / 100)), p_rounding_factor);
523 END IF;
524 ELSE -- QP_PREQ_GRP.G_ROUNDING_FLAG ='N' or 'U', un-round adjs
525 x_discounted_price := p_discounted_price + (abs(v_list_price) * (p_operand_value / 100));
526 END IF;
527
528 --x_discounted_price := p_discounted_price + (abs(v_list_price) *(p_operand_value/100));
529 x_adjusted_amount := abs(v_list_price) * (p_operand_value / 100);
530
531 ELSIF (p_list_line_type = QP_PREQ_GRP.G_FREIGHT_CHARGE) THEN
532 x_discounted_price := p_discounted_price;
533 x_adjusted_amount := abs(v_list_price) * (p_operand_value / 100);
534 END IF; -- end if p_list_line_type
535
536 ELSIF (p_operand_calc_code = QP_PREQ_GRP.G_NEWPRICE_DISCOUNT) THEN
537
538 IF (p_list_line_type IN (QP_PREQ_GRP.G_DISCOUNT, QP_PREQ_GRP.G_SURCHARGE)) THEN
539 x_discounted_price := p_operand_value;
540 x_adjusted_amount := - (v_list_price - p_operand_value);
541 --fix bug 2353905
542 --Negative list price can get correct adjusted_amount
543 END IF;
544
545 ELSIF (p_operand_calc_code = QP_PREQ_GRP.G_LUMPSUM_DISCOUNT) THEN
546
547 IF (p_list_line_type = QP_PREQ_GRP.G_DISCOUNT) THEN
548
549 IF(p_accrual_flag = QP_PREQ_GRP.G_YES) THEN
550 x_discounted_price := p_discounted_price;
551 IF (p_request_qty = 0) THEN
552 --x_adjusted_amount := p_operand_value;
553 x_adjusted_amount := 0; --bug2385874, should be 0 instead of p_operand_value
554 ELSE
555 --4900095 added nvl to G_Lumpsum_qty
556 x_adjusted_amount := - (p_operand_value / nvl(G_Lumpsum_qty, p_request_qty));
557 END IF; -- end if p_request_qty
558 ELSE
559 IF (p_request_qty = 0) THEN
560 x_discounted_price := p_discounted_price;
561 --x_adjusted_amount := p_operand_value;
562 x_adjusted_amount := 0; --bug2385874, should be 0 instead of p_operand_value
563 ELSE
564 --IF (v_round_individual_adj_flag = QP_PREQ_GRP.G_YES AND p_rounding_factor IS NOT NULL) THEN -- shu
565 IF (nvl(QP_PREQ_GRP.G_ROUNDING_FLAG, QP_PREQ_GRP.G_YES) = QP_PREQ_GRP.G_YES AND p_rounding_factor IS NOT NULL) THEN -- shu, new rounding
566 --4900095 added nvl to G_Lumpsum_qty
567 x_discounted_price := p_discounted_price - ROUND((p_operand_value / nvl(G_Lumpsum_Qty, p_request_qty)), p_rounding_factor);
568 ELSIF (QP_PREQ_GRP.G_ROUNDING_FLAG = G_CHAR_Q) THEN -- check qp_selling_price_rounding_options profile
569 IF (v_price_round_options = G_ROUND_ADJ AND p_rounding_factor IS NOT NULL) THEN -- do not round if profile is null
570 --4900095 added nvl to G_Lumpsum_qty
571 x_discounted_price := p_discounted_price - ROUND((p_operand_value / nvl(G_Lumpsum_Qty, p_request_qty)), p_rounding_factor);
572 END IF;
573 ELSE -- QP_PREQ_GRP.G_ROUNDING_FLAG ='N' or 'U', un-round adjs
574 --4900095 added nvl to G_Lumpsum_qty
575 x_discounted_price := p_discounted_price - (p_operand_value / nvl(G_Lumpsum_Qty, p_request_qty));
576 END IF;
577
578 --4900095 added nvl to G_Lumpsum_qty
582 END IF; -- end if p_request_qty
579 -- x_discounted_price := p_discounted_price - (p_operand_value/nvl(G_Lumpsum_Qty,p_request_qty));
580 --4900095 added nvl to G_Lumpsum_qty
581 x_adjusted_amount := - (p_operand_value / nvl(G_Lumpsum_qty, p_request_qty));
583
584 END IF; -- end if p_accrual_flag
585
586 ELSIF (p_list_line_type = QP_PREQ_GRP.G_SURCHARGE) THEN
587
588 IF (p_request_qty = 0) THEN
589 x_discounted_price := p_discounted_price;
590 --x_adjusted_amount := p_operand_value;
591 x_adjusted_amount := 0; --bug2385874, should be 0 instead of p_operand_value
592 ELSE
593 --IF (v_round_individual_adj_flag = QP_PREQ_GRP.G_YES AND p_rounding_factor IS NOT NULL) THEN -- shu
594 IF (nvl(QP_PREQ_GRP.G_ROUNDING_FLAG, QP_PREQ_GRP.G_YES) = QP_PREQ_GRP.G_YES AND p_rounding_factor IS NOT NULL) THEN -- shu, new rounding
595 --4900095 added nvl to G_Lumpsum_qty
596 x_discounted_price := p_discounted_price + ROUND((p_operand_value / nvl(G_Lumpsum_Qty, p_request_qty)), p_rounding_factor);
597 ELSIF (QP_PREQ_GRP.G_ROUNDING_FLAG = G_CHAR_Q) THEN -- check qp_selling_price_rounding_options profile
598 IF (v_price_round_options = G_ROUND_ADJ AND p_rounding_factor IS NOT NULL) THEN -- do not round if profile is null
599 --4900095 added nvl to G_Lumpsum_qty
600 x_discounted_price := p_discounted_price + ROUND((p_operand_value / nvl(G_Lumpsum_Qty, p_request_qty)), p_rounding_factor);
601 END IF;
602 ELSE -- QP_PREQ_GRP.G_ROUNDING_FLAG ='N' or 'U', un-round adjs
603 --4900095 added nvl to G_Lumpsum_qty
604 x_discounted_price := p_discounted_price + (p_operand_value / nvl(G_Lumpsum_Qty, p_request_qty));
605 END IF; -- end if p_request_qty
606
607 --4900095 added nvl to G_Lumpsum_qty
608 -- x_discounted_price := p_discounted_price + (p_operand_value/nvl(G_Lumpsum_Qty,p_request_qty));
609 x_adjusted_amount := p_operand_value / nvl(G_Lumpsum_Qty, p_request_qty);
610
611 END IF; -- end if p_request_qty
612
613 ELSIF (p_list_line_type = QP_PREQ_GRP.G_FREIGHT_CHARGE) THEN
614 x_discounted_price := p_discounted_price;
615
616 IF (p_request_qty = 0) THEN
617 x_adjusted_amount := p_operand_value;
618 ELSE
619 --4900095 added nvl to G_Lumpsum_qty
620 x_adjusted_amount := p_operand_value / nvl(G_Lumpsum_Qty, p_request_qty);
621 END IF;
622 END IF; -- end if p_list_line_type
623 END IF; -- end if p_operand_calc_code
624
625 x_list_price := v_list_price;
626
627 END IF; -- end IF (p_old_pricing_sequence = p_new_pricing_sequence AND p_new_pricing_sequence IS NOT NULL)
628
629 -- round it all
630 -- IF (v_round_individual_adj_flag = QP_PREQ_GRP.G_YES AND p_rounding_factor IS NOT NULL) THEN -- shu
631 IF (nvl(QP_PREQ_GRP.G_ROUNDING_FLAG, QP_PREQ_GRP.G_YES) = QP_PREQ_GRP.G_YES AND p_rounding_factor IS NOT NULL) THEN -- shu, new rounding
632 x_adjusted_amount := ROUND(x_adjusted_amount, p_rounding_factor);
633 ELSIF (QP_PREQ_GRP.G_ROUNDING_FLAG = G_CHAR_Q) THEN -- check qp_selling_price_rounding_options profile
634 IF (v_price_round_options = G_ROUND_ADJ AND p_rounding_factor IS NOT NULL) THEN -- do not round if profile is null
635 x_adjusted_amount := ROUND(x_adjusted_amount, p_rounding_factor);
636 END IF;
637 END IF;
638
639 EXCEPTION
640
641 WHEN OTHERS THEN
642 IF l_debug = FND_API.G_TRUE THEN
643 QP_PREQ_GRP.engine_debug(v_routine_name || ' ' || SQLERRM);
644 END IF;
645 x_return_status := FND_API.G_RET_STS_ERROR;
646 x_return_status_txt := v_routine_name || ' ' || SQLERRM;
647 END Calculate_Adjusted_Price;
648
649 PROCEDURE Process_Price_Break(
650 p_list_line_id NUMBER, -- list line id, used for accum range break
651 p_list_line_index NUMBER, -- PBH Line Detail Index
652 p_operand_calc_code VARCHAR, -- PBH arithmetic operator
653 p_list_line_qty NUMBER, -- Ordered Qty
654 p_actual_line_qty NUMBER, -- Line Qty, 2388011 pbh_grp_amt
655 p_pbh_type VARCHAR2,
656 p_list_price NUMBER, -- Applicable when it is discount break
657 p_discounted_price NUMBER,
658 p_old_pricing_grp_seq NUMBER,
659 p_related_item_price NUMBER, -- Applicable in case of service item
660 p_service_duration NUMBER,
661 p_related_request_lines l_related_request_lines_tbl,
662 p_rounding_flag VARCHAR2,
663 p_rounding_factor NUMBER,
664 p_group_value NUMBER := 0, -- LINEGROUP, 2388011 pbh_grp_amt
665 x_pbh_list_price OUT NOCOPY NUMBER,
666 x_pbh_extended_price OUT NOCOPY NUMBER, -- block pricing
667 x_adjustment_amount OUT NOCOPY NUMBER,
668 x_related_request_lines_tbl OUT NOCOPY l_related_request_lines_tbl,
669 x_return_status OUT NOCOPY VARCHAR2,
670 x_return_status_txt OUT NOCOPY VARCHAR2) AS
671
672 v_temp NUMBER := 0;
676 v_total_amount NUMBER := 0;
673 v_ord_qty NUMBER;
674 v_count NUMBER := 0;
675 v_index NUMBER := 0;
677 v_discounted_price NUMBER;
678 v_operand_calc_code VARCHAR2(30);
679 v_operand_value NUMBER;
680 v_old_pricing_sequence NUMBER;
681 v_zero_count NUMBER;
682 x_discounted_price NUMBER;
683 x_list_price NUMBER;
684 x_percent_price NUMBER;
685 x_extended_price NUMBER; -- block pricing
686 x_adjusted_amount NUMBER;
687 v_related_request_lines l_related_request_lines_tbl;
688 x_ret_status VARCHAR2(30);
689 x_ret_status_txt VARCHAR2(240);
690 k NUMBER;
691 l_precision NUMBER;
692 l_value_from_precision NUMBER;
693 l_value_to_precision NUMBER;
694 l_difference NUMBER;
695 l_satisfied_value NUMBER;
696 l_qualifier_value NUMBER; -- 2388011, 2388011 pbh_grp_amt
697 v_attribute_value_per_unit NUMBER; --2388011_latest
698 v_calculate_per_unit_flag VARCHAR2(1);
699 l_price_round_options VARCHAR2(30) := NULL;
700
701 -- 4061138, 5183755 continuous price breaks
702 CURSOR get_continuous_flag_cur IS
703 SELECT nvl(continuous_price_break_flag, 'N')
704 FROM qp_list_lines
705 WHERE list_line_id = p_list_line_id;
706
707 l_continuous_flag VARCHAR2(1) := 'N';
708 l_prorated_flag VARCHAR2(1) := 'N';
709
710 v_routine_name CONSTANT VARCHAR2(240) := 'Routine:QP_Calculate_Price_PUB.Process_Price_Break';
711
712 BEGIN
713
714 l_debug := QP_PREQ_GRP.G_DEBUG_ENGINE;
715 x_return_status := FND_API.G_RET_STS_SUCCESS;
716 v_related_request_lines := p_related_request_lines;
717 v_old_pricing_sequence := p_old_pricing_grp_seq;
718
719 -- 4061138, 5183755
720 OPEN get_continuous_flag_cur;
721 FETCH get_continuous_flag_cur INTO l_continuous_flag;
722 CLOSE get_continuous_flag_cur;
723
724 k := p_related_request_lines.FIRST;
725 WHILE (k IS NOT NULL)
726 LOOP
727 IF (p_related_request_lines(k).LINE_DETAIL_INDEX = p_list_line_index) THEN
728 IF l_debug = FND_API.G_TRUE THEN
729 IF l_continuous_flag = 'Y' THEN
730 QP_PREQ_GRP.engine_debug ('<-------- CONTINUOUS PRICE BREAK -------->');
731 ELSE
732 QP_PREQ_GRP.engine_debug ('<---------------------------------------->');
733 END IF;
734 QP_PREQ_GRP.engine_debug ('p_list_line_id: '|| p_list_line_id);
735 QP_PREQ_GRP.engine_debug ('p_list_line_index: '|| p_list_line_index);
736 END IF; -- END IF l_debug = FND_API.G_TRUE
737
738 -- 4061138
739 -- check if PBH is prorated, important for continuous processing later
740 IF l_continuous_flag = 'Y' AND QP_PREQ_GRP.G_BREAK_UOM_PRORATION = 'Y'
741 THEN
742 BEGIN
743 SELECT 'Y' INTO l_prorated_flag
744 FROM qp_npreq_ldets_tmp
745 WHERE line_detail_index = p_list_line_index
746 AND break_uom_code IS NOT NULL;
747 EXCEPTION
748 WHEN NO_DATA_FOUND THEN
749 l_prorated_flag := 'N';
750 END;
751 END IF;
752
753 IF (p_related_request_lines(k).PRICE_BREAK_TYPE_CODE = QP_PREQ_GRP.G_POINT_BREAK) THEN
754 IF l_debug = FND_API.G_TRUE THEN
755 QP_PREQ_GRP.engine_debug ('i am in Point break'); -- shu dbg 2388011
756 QP_PREQ_GRP.engine_debug ('p_group_value: '|| p_group_value); -- shu dbg 2388011
757 QP_PREQ_GRP.engine_debug ('p_list_line_qty: '|| p_list_line_qty); -- shu dbg 2388011
758 END IF; -- END IF l_debug = FND_API.G_TRUE
759
760 -- 2388011, Applicable for group of lines based PBH
761 v_ord_qty := p_list_line_qty; -- Store the ordered qty
762 --IF (p_group_value > - 1) THEN --[julin/5158413]
763 IF (p_group_value > 0) THEN --6896139 undoing changes done as part of bug 5158413
764 v_temp := p_group_value;
765 ELSE
766 v_temp := p_list_line_qty;
767 END IF;
768
769 IF l_debug = FND_API.G_TRUE THEN
770 QP_PREQ_GRP.engine_debug ('v_temp, for evaluation (either p_group_value or p_list_line_qty): '|| v_temp); -- shu dbg 2388011
771 END IF; -- END IF l_debug = FND_API.G_TRUE
772
773 -- If it falls in the range
774 /* rewritten for 4061138
775 IF (v_temp >= p_related_request_lines(k).VALUE_FROM AND --2388011
776 v_temp <= nvl(p_related_request_lines(k).VALUE_TO,v_temp)) THEN --2388011, 2 changes
777 */
778 IF ((l_continuous_flag <> 'Y' AND
779 v_temp BETWEEN p_related_request_lines(k).VALUE_FROM AND nvl(p_related_request_lines(k).VALUE_TO, v_temp))
780 OR
781 (l_continuous_flag = 'Y' AND
782 p_related_request_lines(k).VALUE_FROM < v_temp AND
783 v_temp <= nvl(p_related_request_lines(k).VALUE_TO, v_temp)) )
784 THEN
785 v_related_request_lines(k).LINE_QTY := v_temp; -- PBH line qty/v_ord_qty, 2388011
786
787 IF l_debug = FND_API.G_TRUE THEN
788 QP_PREQ_GRP.engine_debug ('PBH operand Calc Code : ' || p_operand_calc_code);
789 END IF;
790
791 -- Do not calculate per unit satisfied value if it is BLOCK_PRICING
792 IF (p_pbh_type IN (QP_PREQ_GRP.G_PRICE_LIST_HEADER, QP_PREQ_GRP.G_AGR_LIST_HEADER)) THEN
793 IF (p_operand_calc_code = 'BLOCK_PRICE') THEN
797 END IF;
794 v_calculate_per_unit_flag := 'N';
795 ELSE
796 v_calculate_per_unit_flag := 'Y';
798 ELSE
799 v_calculate_per_unit_flag := 'Y';
800 END IF;
801 -- for FTE breakunit
802 -- also do not calculate per unit value if line is BREAKUNIT_PRICE
803 IF (p_related_request_lines(k).OPERAND_CALCULATION_CODE = QP_PREQ_GRP.G_BREAKUNIT_PRICE) THEN
804 v_calculate_per_unit_flag := 'N';
805 END IF;
806
807 IF l_debug = FND_API.G_TRUE THEN
808 QP_PREQ_GRP.engine_debug ('Calculate Per Unit Flag : ' || v_calculate_per_unit_flag);
809 END IF;
810
811 -- This happens only if it is line group based Applicable for both Linegroup Amount and Item Amount
812 -- 2388011_new
813 IF (p_group_value > 0 AND v_calculate_per_unit_flag = 'Y') THEN
814 v_attribute_value_per_unit := p_group_value / p_list_line_qty; -- 2388011_latest
815 v_related_request_lines(k).LINE_QTY := v_temp / v_attribute_value_per_unit; -- 2388011_latest
816 ELSE
817 v_related_request_lines(k).LINE_QTY := v_temp;
818 END IF;
819
820
821 IF l_debug = FND_API.G_TRUE THEN
822 QP_PREQ_GRP.engine_debug('List Line Quantity: ' || v_related_request_lines(k).LINE_QTY );
823 QP_PREQ_GRP.engine_debug('PBH Type: ' || p_pbh_type);
824 END IF; -- END IF l_debug = FND_API.G_TRUE
825
826 -- Call the Calculate List Price Function
827 IF (p_pbh_type IN (QP_PREQ_GRP.G_PRICE_LIST_HEADER, QP_PREQ_GRP.G_AGR_LIST_HEADER)) THEN
828 Calculate_List_Price_PVT(
829 p_related_request_lines(k).OPERAND_CALCULATION_CODE,
830 p_related_request_lines(k).OPERAND_VALUE,
831 p_related_request_lines(k).RECURRING_VALUE, -- block pricing
832 v_ord_qty, -- 2388011_new, for calculation
833 p_related_item_price,
834 p_service_duration,
835 QP_PREQ_GRP.G_ROUNDING_FLAG,
836 p_rounding_factor,
837 x_list_price,
838 x_percent_price,
839 x_extended_price,
840 x_ret_status,
841 x_ret_status_txt);
842
843 IF(x_ret_status = FND_API.G_RET_STS_ERROR) THEN
844 RAISE FND_API.G_EXC_ERROR;
845 END IF;
846
847 -- This is the final list price after break evaluation
848 -- changed for block pricing
849 IF (p_related_request_lines(k).OPERAND_CALCULATION_CODE = qp_preq_grp.G_BLOCK_PRICE) THEN
850 v_related_request_lines(k).ADJUSTMENT_AMOUNT := x_extended_price;
851 ELSE
852 v_related_request_lines(k).ADJUSTMENT_AMOUNT := x_list_price;
853 END IF;
854
855 x_pbh_list_price := x_list_price;
856 x_pbh_extended_price := x_extended_price; -- block pricing
857
858 IF l_debug = FND_API.G_TRUE THEN
859 QP_PREQ_GRP.engine_debug('final list price after break evaluation..');
860 QP_PREQ_GRP.engine_debug('List Line Price: ' || v_related_request_lines(k).ADJUSTMENT_AMOUNT );
861 QP_PREQ_GRP.engine_debug('List Line Price: ' || x_pbh_list_price );
862 END IF; -- END IF l_debug = FND_API.G_TRUE
863
864 ELSE -- DIS/SUR/FREIGHT_CHARGE
865
866 IF l_debug = FND_API.G_TRUE THEN
867 QP_PREQ_GRP.engine_debug('I am in Discount Break');
868 QP_PREQ_GRP.engine_debug('List Price: ' || p_list_price);
869 QP_PREQ_GRP.engine_debug('Discounted Price: ' || p_discounted_price);
870 QP_PREQ_GRP.engine_debug('Old Pricing Seq: ' || v_old_pricing_sequence);
871 QP_PREQ_GRP.engine_debug('New Pricing Seq: ' || v_related_request_lines(k).PRICING_GROUP_SEQUENCE);
872 END IF; -- END IF l_debug = FND_API.G_TRUE
873
874 Calculate_Adjusted_Price(p_list_price => p_list_price, -- List Price
875 p_discounted_price => p_discounted_price,
876 p_old_pricing_sequence => nvl(v_old_pricing_sequence,
877 v_related_request_lines(k).PRICING_GROUP_SEQUENCE),
878 p_new_pricing_sequence => v_related_request_lines(k).PRICING_GROUP_SEQUENCE,
879 p_operand_calc_code => v_related_request_lines(k).OPERAND_CALCULATION_CODE,
880 p_operand_value => v_related_request_lines(k).OPERAND_VALUE,
881 p_list_line_type => v_related_request_lines(k).RELATED_LIST_LINE_TYPE,
882 --p_request_qty => v_related_request_lines(k).LINE_QTY, -- 2388011,
883 p_request_qty => p_list_line_qty, -- 2388011_new
884 p_accrual_flag => v_related_request_lines(k).ACCRUAL_FLAG,
888 x_discounted_price => x_discounted_price, -- Output Discounted Price
885 p_rounding_flag => QP_PREQ_GRP.G_ROUNDING_FLAG,
886 p_rounding_factor => p_rounding_factor,
887 p_orig_unit_price => p_list_price,
889 x_adjusted_amount => x_adjusted_amount, -- Discount amount for this line
890 x_list_price => x_list_price, -- Output List Price
891 x_return_status => x_ret_status,
892 x_return_status_txt => x_ret_status_txt);
893
894 v_old_pricing_sequence := v_related_request_lines(k).PRICING_GROUP_SEQUENCE;
895
896 IF (x_ret_status = FND_API.G_RET_STS_ERROR) THEN
897 RAISE FND_API.G_EXC_ERROR;
898 END IF;
899
900 -- This is the final selling price after break evaluation for this discount line
901 v_related_request_lines(k).ADJUSTMENT_AMOUNT := x_adjusted_amount;
902
903 IF l_debug = FND_API.G_TRUE THEN
904 QP_PREQ_GRP.engine_debug('Adjusted Line Amount: ' || v_related_request_lines(k).ADJUSTMENT_AMOUNT);
905 END IF; -- END IF l_debug = FND_API.G_TRUE
906 END IF; -- END IF (p_pbh_type in (QP_PREQ_GRP.G_PRICE_LIST_HEADER,QP_PREQ_GRP.G_AGR_LIST_HEADER))
907
908 -- ELSE IF (v_temp >= p_related_request_lines(k).VALUE_FROM AND...
909 ELSE -- Store the break line which did not satisfy with qty 0,for other related lines
910 v_related_request_lines(k).LINE_QTY := 0;
911 -- This is the final list price or selling price after break evaluation
912 IF (p_pbh_type IN (QP_PREQ_GRP.G_PRICE_LIST_HEADER, QP_PREQ_GRP.G_AGR_LIST_HEADER)) THEN
913
914 Calculate_List_Price_PVT(
915 p_related_request_lines(k).OPERAND_CALCULATION_CODE,
916 p_related_request_lines(k).OPERAND_VALUE,
917 p_related_request_lines(k).RECURRING_VALUE, -- block pricing
918 0, -- p_request_qty = 0
919 p_related_item_price,
920 p_service_duration,
921 QP_PREQ_GRP.G_ROUNDING_FLAG,
922 p_rounding_factor,
923 x_list_price,
924 x_percent_price,
925 x_extended_price, -- block pricing
926 x_ret_status,
927 x_ret_status_txt);
928
929 IF(x_ret_status = FND_API.G_RET_STS_ERROR) THEN
930 IF l_debug = FND_API.G_TRUE THEN
931 QP_PREQ_GRP.engine_debug(v_routine_name || ' ' || x_ret_status_txt);
932 END IF;
933 RAISE FND_API.G_EXC_ERROR;
934 END IF; -- END IF(x_ret_status = FND_API.G_RET_STS_ERROR)
935
936 -- This is the final list price after break evaluation
937 -- modified for block pricing
938 IF (p_related_request_lines(k).OPERAND_CALCULATION_CODE = qp_preq_grp.G_BLOCK_PRICE) THEN
939 v_related_request_lines(k).ADJUSTMENT_AMOUNT := x_extended_price;
940 ELSE
941 v_related_request_lines(k).ADJUSTMENT_AMOUNT := x_list_price;
942 END IF;
943
944 IF l_debug = FND_API.G_TRUE THEN
945 QP_PREQ_GRP.engine_debug('List Line Price: ' || v_related_request_lines(k).ADJUSTMENT_AMOUNT );
946 END IF;
947
948 ELSE --DIS/SUR/FREIGHT_CHARGE
949
950 Calculate_Adjusted_Price(p_list_price => p_list_price, -- List Price
951 p_discounted_price => p_discounted_price,
952 p_old_pricing_sequence => nvl(v_old_pricing_sequence,
953 v_related_request_lines(k).PRICING_GROUP_SEQUENCE),
954 p_new_pricing_sequence => v_related_request_lines(k).PRICING_GROUP_SEQUENCE,
955 p_operand_calc_code => v_related_request_lines(k).OPERAND_CALCULATION_CODE,
956 p_operand_value => v_related_request_lines(k).OPERAND_VALUE,
957 p_list_line_type => v_related_request_lines(k).RELATED_LIST_LINE_TYPE,
958 p_request_qty => 0, -- p_request_qty
959 p_accrual_flag => v_related_request_lines(k).ACCRUAL_FLAG,
960 p_rounding_flag => QP_PREQ_GRP.G_ROUNDING_FLAG,
961 p_rounding_factor => p_rounding_factor,
962 p_orig_unit_price => p_list_price,
963 x_discounted_price => x_discounted_price, -- Output Discounted Price
964 x_adjusted_amount => x_adjusted_amount, -- Discount amount for this line
965 x_list_price => x_list_price, -- Output List Price
966 x_return_status => x_ret_status,
967 x_return_status_txt => x_ret_status_txt);
968
969 v_old_pricing_sequence := v_related_request_lines(k).PRICING_GROUP_SEQUENCE;
970
974
971 IF(x_ret_status = FND_API.G_RET_STS_ERROR) THEN
972 RAISE FND_API.G_EXC_ERROR;
973 END IF;
975 -- This is the final selling price after break evaluation for this discount line
976 v_related_request_lines(k).ADJUSTMENT_AMOUNT := x_adjusted_amount;
977
978 IF l_debug = FND_API.G_TRUE THEN
979 QP_PREQ_GRP.engine_debug('Adjusted Line Amount: ' || x_adjustment_amount);
980 END IF; -- END IF l_debug = FND_API.G_TRUE
981 END IF; -- END IF (p_pbh_type in (QP_PREQ_GRP.G_PRICE_LIST_HEADER
982 END IF; -- END IF (v_temp >= p_related_request_lines(k).VALUE_FROM AND
983
984 -- RANGE BREAK
985 ELSIF (p_related_request_lines(k).PRICE_BREAK_TYPE_CODE = QP_PREQ_GRP.G_RANGE_BREAK) THEN
986
987 IF l_debug = FND_API.G_TRUE THEN
988 QP_PREQ_GRP.engine_debug('I am in Range Break');
989 QP_PREQ_GRP.engine_debug('The value of v_count is:' || v_count );
990 END IF;
991
992 IF (v_count = 0) THEN
993 -- begin 2388011, grp_lines_pbh
994 IF (p_group_value > 0 ) THEN -- Ravi Change
995 l_qualifier_value := p_group_value;
996 v_temp := p_group_value;
997 ELSE
998 l_qualifier_value := p_list_line_qty;
999 v_temp := p_list_line_qty;
1000 END IF;
1001 -- end 2388011
1002 v_ord_qty := p_list_line_qty;
1003 END IF; -- end IF (v_count = 0)
1004
1005 IF l_debug = FND_API.G_TRUE THEN
1006 QP_PREQ_GRP.engine_debug('p_group_value: ' || p_group_value); -- shu debug, 2388011
1007 QP_PREQ_GRP.engine_debug('p_list_line_qty (v_ord_qty): ' || p_list_line_qty); -- shu debug, 2388011
1008 QP_PREQ_GRP.engine_debug('l_qualifier_value (either p_group_value or p_list_line_qty): ' || l_qualifier_value); -- shu debug, 2388011
1009 QP_PREQ_GRP.engine_debug('v_temp (either p_group_value or p_list_line_qty): ' || v_temp); -- shu debug, 2388011
1010 END IF; -- END IF l_debug = FND_API.G_TRUE
1011
1012 -- The check to see if the ordered quantity falls in the range needs to be done only once
1013 -- Ex: If Ord Qty = 50 and 100-null then u do not get it
1014
1015 IF (p_related_request_lines(k).VALUE_FROM > v_temp AND v_count = 0) THEN --2388011
1016 v_temp := 0;
1017 END IF;
1018
1019 IF (v_temp > 0) THEN
1020
1021 -- Ex: 100-NULL
1022 IF (p_related_request_lines(k).VALUE_TO IS NULL) THEN
1023 v_related_request_lines(k).LINE_QTY := v_temp;
1024 ELSE
1025 IF l_debug = FND_API.G_TRUE THEN
1026 QP_PREQ_GRP.engine_debug('C100# value from: ' || p_related_request_lines(k).value_from);
1027 QP_PREQ_GRP.engine_debug('C200# value to: ' || p_related_request_lines(k).value_to);
1028 QP_PREQ_GRP.engine_debug('C300# Qualifier Value: ' || l_qualifier_value); --2388011
1029 QP_PREQ_GRP.engine_debug('C400# Group Amount: ' || p_group_value); --2388011
1030 END IF; -- END IF l_debug = FND_API.G_TRUE
1031
1032 Get_Satisfied_Range(
1033 p_related_request_lines(k).value_from,
1034 p_related_request_lines(k).value_to,
1035 l_qualifier_value, --2388011
1036 p_list_line_id, -- for accum range break
1037 l_continuous_flag, -- 4061138
1038 l_prorated_flag, -- 4061138
1039 l_satisfied_value);
1040
1041 IF l_debug = FND_API.G_TRUE THEN
1042 QP_PREQ_GRP.engine_debug('C500#: ' || l_satisfied_value);
1043 QP_PREQ_GRP.engine_debug('C600# list price : ' || p_list_price);
1044 END IF; -- END IF l_debug = FND_API.G_TRUE
1045
1046 IF l_debug = FND_API.G_TRUE THEN
1047 QP_PREQ_GRP.engine_debug ('PBH operand Calc Code : ' || p_operand_calc_code);
1048 END IF;
1049
1050 -- Do not calculate the per unit satisfied value if it is BLOCK_PRICING
1051 IF (p_pbh_type IN (QP_PREQ_GRP.G_PRICE_LIST_HEADER, QP_PREQ_GRP.G_AGR_LIST_HEADER)) THEN
1052 IF (p_operand_calc_code = 'BLOCK_PRICE') THEN
1053 v_calculate_per_unit_flag := 'N';
1054 ELSE
1055 v_calculate_per_unit_flag := 'Y';
1056 END IF;
1057 ELSE
1058 v_calculate_per_unit_flag := 'Y';
1059 END IF;
1060 -- for FTE breakunit
1061 -- also do not calculate per unit value if line is BREAKUNIT_PRICE
1062 IF (p_related_request_lines(k).OPERAND_CALCULATION_CODE = QP_PREQ_GRP.G_BREAKUNIT_PRICE) THEN
1063 v_calculate_per_unit_flag := 'N';
1064 END IF;
1065
1066 IF l_debug = FND_API.G_TRUE THEN
1067 QP_PREQ_GRP.engine_debug ('Calculate Per Unit Flag : ' || v_calculate_per_unit_flag);
1068 END IF;
1069
1070 -- 2388011_new
1071 -- Ravi Change Applicable for both linegroup Group Amount and Line Item Amt
1072 IF (p_group_value > 0 AND v_calculate_per_unit_flag = 'Y') THEN
1073 v_attribute_value_per_unit := p_group_value / p_list_line_qty; -- 2388011_latest
1074 v_related_request_lines(k).LINE_QTY := l_satisfied_value / v_attribute_value_per_unit; -- 2388011_latest
1078
1075 ELSE
1076 v_related_request_lines(k).LINE_QTY := l_satisfied_value;
1077 END IF;
1079 v_temp := v_temp - l_satisfied_value;
1080
1081 -- shu, fix bug 2372064
1082 -- l_satisfied_value is 0 have 2 cases: 1) no more to satisfied 2) from=to such as 0-0 break
1083 -- only for case 1) should reset v_temp
1084 -- for accum range break, case 1 should take into account accum_value
1085 -- to determine if no more need to be satisfied:
1086 -- if (stored accum value + l_qualifier) < value_to
1087 -- then no more satisfied
1088 IF (l_satisfied_value = 0
1089 AND p_related_request_lines(k).value_from <> p_related_request_lines(k).value_to
1090 AND l_accum_global + l_qualifier_value < p_related_request_lines(k).value_to)
1091 THEN
1092 v_temp := 0;
1093 END IF; -- END IF (l_satisfied_value = 0
1094
1095 END IF; -- END IF (p_related_request_lines(k).VALUE_TO IS NULL)
1096
1097 IF l_debug = FND_API.G_TRUE THEN
1098 QP_PREQ_GRP.engine_debug('List Line Quantity: ' || v_related_request_lines(k).LINE_QTY );
1099 QP_PREQ_GRP.engine_debug('Temp: ' || v_temp );
1100 END IF;
1101
1102 IF (p_pbh_type IN (QP_PREQ_GRP.G_PRICE_LIST_HEADER, QP_PREQ_GRP.G_AGR_LIST_HEADER)) THEN
1103 -- Call the Calculate List Price Function
1104 Calculate_List_Price_PVT(
1105 p_related_request_lines(k).OPERAND_CALCULATION_CODE,
1106 p_related_request_lines(k).OPERAND_VALUE,
1107 p_related_request_lines(k).RECURRING_VALUE, -- block pricing
1108 --v_ord_qty, -- 2388011_new
1109 v_related_request_lines(k).LINE_QTY, -- new fix, error found in block pricing
1110 p_related_item_price,
1111 p_service_duration,
1112 QP_PREQ_GRP.G_ROUNDING_FLAG,
1113 NULL, -- fix bug 2724697, null rounding_factor so operand does not get rounded, it will be rounded later at Average Out PBH Lines
1114 x_list_price,
1115 x_percent_price,
1116 x_extended_price, -- block pricing
1117 x_ret_status,
1118 x_ret_status_txt);
1119
1120 IF(x_ret_status = FND_API.G_RET_STS_ERROR) THEN
1121 RAISE FND_API.G_EXC_ERROR;
1122 END IF;
1123
1124 -- This is the final list price after break evaluation
1125 -- modified for block pricing
1126 IF (p_related_request_lines(k).OPERAND_CALCULATION_CODE = qp_preq_grp.G_BLOCK_PRICE) THEN
1127 v_related_request_lines(k).ADJUSTMENT_AMOUNT := x_extended_price;
1128 ELSE
1129 v_related_request_lines(k).ADJUSTMENT_AMOUNT := x_list_price;
1130 END IF;
1131
1132 ELSE -- DIS/SUR/FREIGHT_CHARGE
1133 IF l_debug = FND_API.G_TRUE THEN
1134 QP_PREQ_GRP.engine_debug('I am in Discount Break (RANGE BLOCK)');
1135 QP_PREQ_GRP.engine_debug('OPERAND_VALUE: '|| v_related_request_lines(k).OPERAND_VALUE); --shu debug 2388011
1136 QP_PREQ_GRP.engine_debug('LINE_QTY: '|| v_related_request_lines(k).LINE_QTY); --shu debug 2388011
1137 QP_PREQ_GRP.engine_debug('l_qualifier_value (p_request_qty)'|| l_qualifier_value); --shu debug 2388011
1138 END IF;
1139
1140 --p_request_qty => l_qualifier_value,
1141 -- 2388011, range break should be total amount not l_satisfied_value
1142 --p_request_qty => p_list_line_qty,
1143 -- group quantity,group value or item quantity 2388011_new
1144
1145 Calculate_Adjusted_Price(
1146 p_list_price => p_list_price, -- List Price
1147 p_discounted_price => p_discounted_price,
1148 p_old_pricing_sequence => nvl(v_old_pricing_sequence,
1149 v_related_request_lines(k).PRICING_GROUP_SEQUENCE),
1150 p_new_pricing_sequence => v_related_request_lines(k).PRICING_GROUP_SEQUENCE,
1151 p_operand_calc_code => v_related_request_lines(k).OPERAND_CALCULATION_CODE,
1152 p_operand_value => v_related_request_lines(k).OPERAND_VALUE,
1153 p_list_line_type => v_related_request_lines(k).RELATED_LIST_LINE_TYPE,
1154 p_request_qty => v_related_request_lines(k).LINE_QTY, --always satisfied qty
1155 p_accrual_flag => v_related_request_lines(k).ACCRUAL_FLAG,
1156 p_rounding_flag => QP_PREQ_GRP.G_ROUNDING_FLAG,
1157 p_rounding_factor => p_rounding_factor,
1158 p_orig_unit_price => p_list_price,
1159 x_discounted_price => x_discounted_price, -- Output Discounted Price
1160 x_adjusted_amount => x_adjusted_amount, -- Discount amount for this line
1161 x_list_price => x_list_price, -- Output List Price
1165 v_old_pricing_sequence := v_related_request_lines(k).PRICING_GROUP_SEQUENCE;
1162 x_return_status => x_ret_status,
1163 x_return_status_txt => x_ret_status_txt);
1164
1166
1167 IF(x_ret_status = FND_API.G_RET_STS_ERROR) THEN
1168 RAISE FND_API.G_EXC_ERROR;
1169 END IF;
1170
1171 -- This is the final list price after break evaluation
1172 v_related_request_lines(k).ADJUSTMENT_AMOUNT := x_adjusted_amount;
1173 IF l_debug = FND_API.G_TRUE THEN
1174 QP_PREQ_GRP.engine_debug('Adjusted Line Amount: ' || x_adjusted_amount);
1175 END IF;
1176 END IF;
1177
1178 v_count := v_count + 1;
1179 ELSE -- v_temp > 0
1180
1181 -- Store the break line which did not satisfy with qty 0
1182 v_related_request_lines(k).LINE_QTY := 0;
1183 -- This is the final list price or selling price after break evaluation
1184 IF (p_pbh_type IN (QP_PREQ_GRP.G_PRICE_LIST_HEADER, QP_PREQ_GRP.G_AGR_LIST_HEADER)) THEN
1185
1186 Calculate_List_Price_PVT(p_related_request_lines(k).OPERAND_CALCULATION_CODE,
1187 p_related_request_lines(k).OPERAND_VALUE,
1188 p_related_request_lines(k).RECURRING_VALUE, -- block pricing
1189 0,
1190 p_related_item_price,
1191 p_service_duration,
1192 QP_PREQ_GRP.G_ROUNDING_FLAG,
1193 NULL, -- fix bug 2724697, null rounding_factor so operand does not get rounded, it will be rounded later at Average Out PBH Lines
1194 x_list_price,
1195 x_percent_price,
1196 x_extended_price,
1197 x_ret_status,
1198 x_ret_status_txt);
1199
1200 IF(x_ret_status = FND_API.G_RET_STS_ERROR) THEN
1201 RAISE FND_API.G_EXC_ERROR;
1202 END IF;
1203
1204 -- This is the final list price after break evaluation
1205 -- modified for block pricing
1206 IF (p_related_request_lines(k).OPERAND_CALCULATION_CODE = qp_preq_grp.G_BLOCK_PRICE) THEN
1207 v_related_request_lines(k).ADJUSTMENT_AMOUNT := x_extended_price;
1208 ELSE
1209 v_related_request_lines(k).ADJUSTMENT_AMOUNT := x_list_price;
1210 END IF;
1211
1212 IF l_debug = FND_API.G_TRUE THEN
1213 QP_PREQ_GRP.engine_debug('List Line Price: ' || x_list_price);
1214 END IF;
1215
1216 ELSE -- DIS/SUR/FREIGHT_CHARGE
1217
1218 Calculate_Adjusted_Price(
1219 p_list_price => p_list_price, -- List Price
1220 p_discounted_price => p_discounted_price,
1221 p_old_pricing_sequence => nvl(v_old_pricing_sequence
1222 , v_related_request_lines(k).PRICING_GROUP_SEQUENCE),
1223 p_new_pricing_sequence => v_related_request_lines(k).PRICING_GROUP_SEQUENCE,
1224 p_operand_calc_code =>
1225 v_related_request_lines(k).OPERAND_CALCULATION_CODE,
1226 p_operand_value => v_related_request_lines(k).OPERAND_VALUE,
1227 p_list_line_type => v_related_request_lines(k).RELATED_LIST_LINE_TYPE,
1228 p_request_qty => 0,
1229 p_accrual_flag => v_related_request_lines(k).ACCRUAL_FLAG,
1230 p_rounding_flag => QP_PREQ_GRP.G_ROUNDING_FLAG,
1231 p_rounding_factor => p_rounding_factor,
1232 p_orig_unit_price => p_list_price,
1233 x_discounted_price => x_discounted_price, -- Output Discounted Price
1234 x_adjusted_amount => x_adjusted_amount, -- Discount amount for this line
1235 x_list_price => x_list_price, -- Output List Price
1236 x_return_status => x_ret_status,
1237 x_return_status_txt => x_ret_status_txt);
1238
1239 v_old_pricing_sequence := v_related_request_lines(k).PRICING_GROUP_SEQUENCE;
1240
1241 IF(x_ret_status = FND_API.G_RET_STS_ERROR) THEN
1242 RAISE FND_API.G_EXC_ERROR;
1243 END IF;
1244
1245 -- This is the final list price after break evaluation
1246 v_related_request_lines(k).ADJUSTMENT_AMOUNT := x_adjusted_amount;
1247 IF l_debug = FND_API.G_TRUE THEN
1248 QP_PREQ_GRP.engine_debug('Adjusted Line Amount: ' || x_adjusted_amount);
1249 END IF;
1250 END IF;
1251
1252 v_count := v_count + 1;
1253 END IF; -- v_temp >0
1254 END IF; -- POINT or RANGE Break
1255 END IF; -- p_line_detail_index match
1256
1257 EXIT WHEN k = v_related_request_lines.LAST;
1261 -- This needs to be done only to get the averaged out value for the PBH Line
1258 k := v_related_request_lines.NEXT(k);
1259 END LOOP; -- END WHILE (k IS NOT NULL)
1260
1262 IF (v_related_request_lines.COUNT > 0) THEN
1263 IF l_debug = FND_API.G_TRUE THEN
1264 QP_PREQ_GRP.engine_debug('.........Average out value for the PBH line........ ' );
1265 END IF;
1266 v_index := v_related_request_lines.FIRST;
1267 WHILE (v_index IS NOT NULL)
1268 LOOP
1269 IF (v_related_request_lines(v_index).LINE_DETAIL_INDEX = p_list_line_index) THEN
1270 IF l_debug = FND_API.G_TRUE THEN
1271 QP_PREQ_GRP.engine_debug('p_list_line_index '|| p_list_line_index ); -- shu debug 2388011
1272 QP_PREQ_GRP.engine_debug('p_list_line_qty: '|| p_list_line_qty ); -- shu debug 2388011
1273 QP_PREQ_GRP.engine_debug('p_actual_line_qty: '|| p_actual_line_qty ); -- shu debug 2388011
1274 END IF;
1275
1276 IF (p_pbh_type IN (QP_PREQ_GRP.G_PRICE_LIST_HEADER, QP_PREQ_GRP.G_AGR_LIST_HEADER)) THEN
1277 -- modified for block pricing
1278 IF (v_related_request_lines(v_index).OPERAND_CALCULATION_CODE = QP_PREQ_GRP.G_BLOCK_PRICE) THEN
1279 IF (v_related_request_lines(v_index).LINE_QTY <> 0) THEN
1280 v_total_amount := v_total_amount + v_related_request_lines(v_index).ADJUSTMENT_AMOUNT;
1281
1282 -- fix for OM Pricing/Availability
1283 IF (v_related_request_lines(v_index).RECURRING_VALUE > 0) THEN
1284 v_related_request_lines(v_index).ADJUSTMENT_AMOUNT :=
1285 v_related_request_lines(v_index).ADJUSTMENT_AMOUNT / v_related_request_lines(v_index).LINE_QTY;
1286 END IF;
1287 ELSE
1288 -- fix for OM Pricing/Availability
1289 IF (v_related_request_lines(v_index).RECURRING_VALUE > 0) THEN
1290 v_related_request_lines(v_index).ADJUSTMENT_AMOUNT :=
1291 v_related_request_lines(v_index).ADJUSTMENT_AMOUNT / v_related_request_lines(v_index).RECURRING_VALUE;
1292 END IF;
1293 END IF;
1294 ELSE
1295 v_total_amount := v_total_amount + (v_related_request_lines(v_index).LINE_QTY *
1296 v_related_request_lines(v_index).ADJUSTMENT_AMOUNT);
1297 END IF;
1298
1299 -- begin 2388011_new, grp_lines_pbh,
1300 -- fix 2724697, round here after average out PBH lines
1301
1302 IF (p_list_line_qty <> 0 ) THEN
1303
1304 x_pbh_list_price := v_total_amount / p_list_line_qty; -- Divide by grp amount/unit or group quantity
1305 x_pbh_extended_price := v_total_amount; -- block pricing
1306
1307 ELSE -- bug 3075286 discussed the solution with rtata
1308
1309 x_pbh_list_price := v_total_amount;
1310 x_pbh_extended_price := 0; -- block pricing
1311
1312 END IF;
1313
1314 IF (nvl(QP_PREQ_GRP.G_ROUNDING_FLAG, QP_PREQ_GRP.G_YES) = QP_PREQ_GRP.G_YES AND p_rounding_factor IS NOT NULL) THEN
1315 x_pbh_list_price := ROUND (x_pbh_list_price, p_rounding_factor); -- Divide by grp amount/unit or group quantity
1316 ELSIF (QP_PREQ_GRP.G_ROUNDING_FLAG = G_CHAR_Q) THEN -- check v_selling_price_rounding_options profile
1317 l_price_round_options := nvl(FND_PROFILE.VALUE('QP_SELLING_PRICE_ROUNDING_OPTIONS'), QP_Calculate_Price_PUB.G_NO_ROUND);
1318 IF (l_price_round_options = G_ROUND_ADJ AND p_rounding_factor IS NOT NULL) THEN
1319 x_pbh_list_price := ROUND (x_pbh_list_price, p_rounding_factor); -- Divide by grp amount/unit or group quantity
1320 END IF; -- end if G_ROUND_ADJ
1321 END IF; --end if rounding stuff
1322
1323 IF l_debug = FND_API.G_TRUE THEN
1324 QP_PREQ_GRP.engine_debug('x_pbh_list_price: '|| x_pbh_list_price);
1325 END IF; -- END IF l_debug
1326
1327
1328 ELSE
1329 v_total_amount := v_total_amount + (v_related_request_lines(v_index).LINE_QTY *
1330 v_related_request_lines(v_index).ADJUSTMENT_AMOUNT);
1331 IF l_debug = FND_API.G_TRUE THEN
1332 QP_PREQ_GRP.engine_debug ('v_index: '|| v_index); --2388011
1333 QP_PREQ_GRP.engine_debug ('LINE_QTY : '|| v_related_request_lines(v_index).LINE_QTY ); --2388011
1334 QP_PREQ_GRP.engine_debug ('ADJUSTMENT_AMOUNT for the line: '|| v_related_request_lines(v_index).ADJUSTMENT_AMOUNT); --2388011
1335 QP_PREQ_GRP.engine_debug ('v_total_amount for now: '|| v_total_amount); --2388011
1336 END IF; -- END IF l_debug = FND_API.G_TRUE
1337
1338 -- begin 2388011_new, grp_lines_pbh,
1339
1340
1341 IF (p_list_line_qty <> 0 ) THEN
1342
1343 x_adjustment_amount := v_total_amount / p_list_line_qty; -- Divide by grp amount/unit or group quantity
1344
1345 ELSE -- bug 3075286
1346
1347 x_adjustment_amount := v_total_amount;
1348
1349 END IF;
1350
1351 IF l_debug = FND_API.G_TRUE THEN
1352 QP_PREQ_GRP.engine_debug ('p_list_line_qty: '|| p_list_line_qty); --2388011
1353 QP_PREQ_GRP.engine_debug ('x_adjustment_amount: '|| x_adjustment_amount); --2388011
1354 END IF; -- END IF l_debug = FND_API.G_TRUE
1355 -- end 2388011, grp_lines_pbh
1356
1357 END IF; -- END IF (p_pbh_type in (QP_PREQ_GRP.G_PRICE_LIST_HEADER...
1358
1362 QP_PREQ_GRP.engine_debug ('Total Adjustment : ' || x_adjustment_amount);
1359 IF l_debug = FND_API.G_TRUE THEN
1360 QP_PREQ_GRP.engine_debug ('Total Amount : ' || v_total_amount);
1361 QP_PREQ_GRP.engine_debug ('Total Qty : ' || v_ord_qty);
1363 END IF; -- END IF l_debug = FND_API.G_TRUE THEN
1364 END IF;
1365
1366 EXIT WHEN v_index = v_related_request_lines.LAST;
1367 v_index := v_related_request_lines.NEXT(v_index);
1368 END LOOP;
1369 END IF;
1370 x_related_request_lines_tbl := v_related_request_lines;
1371 EXCEPTION
1372 WHEN FND_API.G_EXC_ERROR THEN
1373 IF l_debug = FND_API.G_TRUE THEN
1374 QP_PREQ_GRP.engine_debug(v_routine_name || ' ' || x_ret_status_txt);
1375 END IF;
1376 x_return_status := FND_API.G_RET_STS_ERROR;
1377 x_return_status_txt := x_ret_status_txt;
1378 WHEN OTHERS THEN
1379 IF l_debug = FND_API.G_TRUE THEN
1380 QP_PREQ_GRP.engine_debug(v_routine_name || ' ' || SQLERRM);
1381 END IF;
1382 x_return_status := FND_API.G_RET_STS_ERROR;
1383 x_return_status_txt := v_routine_name || ' ' || SQLERRM;
1384 END Process_Price_Break;
1385
1386 PROCEDURE Calculate_Adjustment(p_price NUMBER
1387 , p_operand_calculation_code VARCHAR2
1388 , p_operand_value NUMBER
1389 , p_priced_quantity NUMBER
1390 , x_calc_adjustment OUT NOCOPY NUMBER
1391 , x_return_status OUT NOCOPY VARCHAR2
1392 , x_return_status_text OUT NOCOPY VARCHAR2) IS
1393
1394 l_routine VARCHAR2(50) := 'QP_Calculate_Price_PUB.Calculate_Adjustment';
1395
1396 BEGIN
1397 l_debug := QP_PREQ_GRP.G_DEBUG_ENGINE;
1398 IF l_debug = FND_API.G_TRUE THEN
1399 QP_PREQ_GRP.ENGINE_DEBUG('In Routine: ' || l_routine);
1400
1401 -- begin 2892848
1402 QP_PREQ_GRP.ENGINE_DEBUG('p_price: ' || p_price);
1403 QP_PREQ_GRP.ENGINE_DEBUG('p_operand_calculation_code: ' || p_operand_calculation_code);
1404 QP_PREQ_GRP.ENGINE_DEBUG('p_operand_value: ' || p_operand_value);
1405 QP_PREQ_GRP.ENGINE_DEBUG('p_priced_quantity: ' || p_priced_quantity);
1406 -- end 2892848
1407
1408 END IF;
1409 IF p_operand_calculation_code = QP_PREQ_GRP.G_PERCENT_DISCOUNT THEN
1410 -- x_calc_adjustment := abs(p_price) * p_operand_value / 100; -- bug 6122488
1411 x_calc_adjustment := p_price * p_operand_value / 100;
1412 ELSIF p_operand_calculation_code = QP_PREQ_GRP.G_AMOUNT_DISCOUNT THEN
1413 x_calc_adjustment := p_operand_value;
1414 ELSIF p_operand_calculation_code = QP_PREQ_GRP.G_NEWPRICE_DISCOUNT THEN
1415 x_calc_adjustment := (abs(p_price) - p_operand_value);
1416 ELSIF p_operand_calculation_code = QP_PREQ_GRP.G_LUMPSUM_DISCOUNT THEN
1417 -- [julin/4571612/4671446] explicitly set FND_API.G_NULL_NUM qty means infinity; this qty and zero qty yield no adj
1418 IF (p_priced_quantity = FND_API.G_NULL_NUM OR p_priced_quantity = 0) THEN
1419 x_calc_adjustment := 0;
1420 ELSE
1421 x_calc_adjustment := p_operand_value / p_priced_quantity;
1422 END IF;
1423 ELSIF p_operand_calculation_code = QP_PREQ_GRP.G_BREAKUNIT_PRICE THEN
1424 x_calc_adjustment := p_operand_value; -- FTE breakunit
1425 END IF;
1426
1427 x_return_status := FND_API.G_RET_STS_SUCCESS;
1428 x_return_status_text := l_routine ||' SUCCESS';
1429
1430 IF l_debug = FND_API.G_TRUE THEN
1431 QP_PREQ_GRP.ENGINE_DEBUG('Out of Routine: ' || l_routine);
1432
1433 END IF;
1434 EXCEPTION
1435 WHEN OTHERS THEN
1436 IF l_debug = FND_API.G_TRUE THEN
1437 QP_PREQ_GRP.ENGINE_DEBUG('Error in calculating bucket price '|| SQLERRM);
1438 END IF;
1439 x_return_status := FND_API.G_RET_STS_ERROR;
1440 x_return_status_text := l_routine ||' '|| SQLERRM;
1441 END Calculate_Adjustment;
1442
1443 PROCEDURE Get_Satisfied_Range(p_value_from NUMBER,
1444 p_value_to NUMBER,
1445 p_qualifier_value NUMBER,
1446 p_list_line_id NUMBER, -- for accum range break
1447 p_continuous_flag VARCHAR, -- 4061138, continuous price break
1448 p_prorated_flag VARCHAR, -- 4061138
1449 x_satisfied_value OUT NOCOPY NUMBER) AS
1450 l_value_from NUMBER;
1451 l_precision NUMBER;
1452 l_difference NUMBER;
1453 l_value_from_precision NUMBER;
1454 l_value_to_precision NUMBER;
1455
1456 -- variables for accumulated range break
1457 l_new_qualifier NUMBER;
1458 l_accum NUMBER; -- accumulated value
1459 l_accum_context VARCHAR2(30);
1460 l_accum_attrib VARCHAR2(240);
1461 l_accum_flag VARCHAR2(1);
1462 v_list_line_no QP_LIST_LINES.LIST_LINE_NO%TYPE;
1463 v_price_eff_date DATE;
1464 v_line_id NUMBER;
1465 l_req_attrs_tbl QP_RUNTIME_SOURCE.accum_req_line_attrs_tbl;
1466 l_accum_rec QP_RUNTIME_SOURCE.accum_record_type;
1467 counter PLS_INTEGER := 1;
1468
1469 -- for accum range break
1470 -- gets request line attributes to pass to the custom API call
1471 -- (only the attributes that qualified for the price break in question)
1472 CURSOR l_req_line_attrs_cur(p_list_line_id NUMBER) IS
1473 SELECT line_index, attribute_type, context, attribute, value_from, grouping_number
1474 FROM qp_npreq_line_attrs_tmp
1475 WHERE list_line_id = p_list_line_id
1479
1476 AND pricing_status_code = QP_PREQ_GRP.G_STATUS_NEW;
1477
1478 BEGIN
1480 l_debug := QP_PREQ_GRP.G_DEBUG_ENGINE;
1481 IF l_debug = FND_API.G_TRUE THEN
1482 QP_PREQ_GRP.engine_debug('In Procedure : Get_Satisfied_Range');
1483 QP_PREQ_GRP.engine_debug(' continuous_flag = '|| p_continuous_flag);
1484 QP_PREQ_GRP.engine_debug(' prorated_flag = '|| p_prorated_flag);
1485 END IF;
1486 l_value_from_precision := power(10, - (length(to_char(abs(p_value_from) - trunc(abs(p_value_from)))) - 1));
1487
1488 l_value_to_precision := power(10, - (length(to_char(abs(p_value_to) - trunc(abs(p_value_to)))) - 1));
1489
1490 l_difference := least(l_value_from_precision, l_value_to_precision);
1491
1492 -- 4061138, precision not needed if continuous break
1493 IF p_continuous_flag <> 'Y' THEN
1494 -- if break is prorated, only consider the from precision
1495 IF p_prorated_flag = 'Y' THEN
1496 l_precision := l_value_from_precision;
1497 ELSE
1498 l_precision := power(10, - (length(to_char(abs(l_difference) - trunc(abs(l_difference)))) - 1));
1499 END IF;
1500 ELSE
1501 l_precision := 0;
1502 END IF;
1503
1504 -- 4061138, added p_continuous_flag condition to prevent remapping of a zero-value value_from
1505 IF (p_value_from = 0 AND p_continuous_flag <> 'Y') THEN
1506 l_value_from := l_difference;
1507 ELSE
1508 l_value_from := p_value_from;
1509 END IF;
1510
1511 IF (nvl(FND_PROFILE.VALUE('QP_ACCUM_ATTR_ENABLED'), QP_PREQ_GRP.G_NO) = QP_PREQ_GRP.G_YES AND
1512 p_list_line_id IS NOT NULL AND
1513 nvl(QP_PREQ_PUB.G_CALL_FROM_PRICE_BOOK, QP_PREQ_GRP.G_NO) <> QP_PREQ_GRP.G_YES) -- price book
1514 THEN
1515 -- fetch accumulation attribute
1516 -- these are "cached" on the basis of list_line_id
1517 -- if list_line_id changes, then need to retrieve values from temp table
1518 -- else use previously cached values
1519 IF (p_list_line_id = l_prev_list_line_id) THEN
1520 IF l_debug = FND_API.G_TRUE THEN
1521 QP_PREQ_GRP.engine_debug(' list_line_id = '|| p_list_line_id ||', prev = '|| l_prev_list_line_id);
1522 END IF;
1523 l_accum_context := l_accum_context_cache;
1524 l_accum_attrib := l_accum_attrib_cache;
1525 l_accum_flag := l_accum_flag_cache;
1526 v_list_line_no := l_accum_list_line_no_cache;
1527 IF l_debug = FND_API.G_TRUE THEN
1528 QP_PREQ_GRP.engine_debug(' using cached values for accum context, attribute, etc.');
1529 END IF;
1530 ELSE
1531 BEGIN
1532 SELECT accum_context, accum_attribute, accum_attr_run_src_flag, list_line_no
1533 INTO l_accum_context, l_accum_attrib, l_accum_flag, v_list_line_no
1534 FROM qp_list_lines
1535 WHERE list_line_id = p_list_line_id;
1536 -- from qp_npreq_ldets_tmp
1537 -- where created_from_list_line_id = p_list_line_id
1538 -- and rownum = 1;
1539 EXCEPTION
1540 WHEN NO_DATA_FOUND THEN
1541 IF l_debug = FND_API.G_TRUE THEN
1542 QP_PREQ_GRP.engine_debug(' No rows returned for list line ID '|| p_list_line_id);
1543 END IF;
1544 END;
1545
1546 IF l_debug = FND_API.G_TRUE THEN
1547 QP_PREQ_GRP.engine_debug(' accum context, attribute, etc. not cached');
1548 END IF;
1549 -- cache the values
1550 l_accum_context_cache := l_accum_context;
1551 l_accum_attrib_cache := l_accum_attrib;
1552 l_accum_flag_cache := l_accum_flag;
1553 l_accum_list_line_no_cache := v_list_line_no;
1554 l_prev_list_line_id := p_list_line_id;
1555 END IF;
1556 IF (l_accum_attrib IS NULL) THEN
1557 -- conceptually, this means no accumulation processing
1558 -- in this code, we set accum=0 and let accumulation processing fall through
1559 l_accum := 0;
1560 IF l_debug = FND_API.G_TRUE THEN
1561 QP_PREQ_GRP.engine_debug(' no accum attribute specified.');
1562 END IF;
1563 ELSE
1564 IF l_debug = FND_API.G_TRUE THEN
1565 QP_PREQ_GRP.engine_debug(' accum attribute = '|| l_accum_attrib);
1566 QP_PREQ_GRP.engine_debug(' line index = '|| l_line_index);
1567 END IF;
1568
1569 -- try to get accum value from request line
1570 BEGIN
1571 SELECT DISTINCT value_from
1572 INTO l_accum
1573 FROM qp_npreq_line_attrs_tmp
1574 WHERE line_index = l_line_index -- (l_line_index is a global package variable)
1575 AND context = l_accum_context
1576 AND attribute = l_accum_attrib
1577 AND pricing_status_code = QP_PREQ_GRP.G_STATUS_UNCHANGED;
1578
1579 IF l_debug = FND_API.G_TRUE THEN
1580 QP_PREQ_GRP.engine_debug(' accum value '|| l_accum ||' passed on request line.');
1581 END IF;
1582 EXCEPTION
1583 WHEN NO_DATA_FOUND THEN
1584 IF l_debug = FND_API.G_TRUE THEN
1585 QP_PREQ_GRP.engine_debug(' accum value not passed on request line.');
1586 END IF;
1587 -- accum value was not passed on request line
1588 IF l_accum_flag = 'Y'
1589 THEN
1590 ------------------------------------------------------
1591 -- call the custom API
1592 -- 1. construct the table of request line attributes
1596 -- For performance reasons, the code does not follow steps 1,2,3 in order.
1593 -- 2. construct the accumulation record
1594 -- 3. call function Get_numeric_attribute_value
1595 --
1597 -- One select statement (a cursor) needed for step 1.
1598 -- Another select needed for steps 2 and 3.
1599 -- Last select needed to get order id.
1600 ------------------------------------------------------
1601 IF l_debug = FND_API.G_TRUE THEN
1602 QP_PREQ_GRP.engine_debug(' * Calling Custom API for accumulation *');
1603 QP_PREQ_GRP.engine_debug(' request line attr(s):');
1604 END IF;
1605 FOR attrs IN l_req_line_attrs_cur(p_list_line_id) LOOP
1606 l_req_attrs_tbl(counter).line_index := attrs.line_index;
1607 l_req_attrs_tbl(counter).attribute_type := attrs.attribute_type;
1608 l_req_attrs_tbl(counter).context := attrs.context;
1609 l_req_attrs_tbl(counter).attribute := attrs.attribute;
1610 l_req_attrs_tbl(counter).VALUE := attrs.value_from;
1611 l_req_attrs_tbl(counter).grouping_no := attrs.grouping_number;
1612 IF l_debug = FND_API.G_TRUE THEN
1613 QP_PREQ_GRP.engine_debug(' '|| counter ||': line index '|| attrs.line_index ||', '|| attrs.attribute_type
1614 ||', '|| attrs.context ||', '|| attrs.attribute ||', '|| attrs.value_from
1615 ||', '|| attrs.grouping_number);
1616 END IF;
1617 counter := counter + 1;
1618 END LOOP;
1619
1620 -- get the order (header) ID if it's null
1621 -- this should never happen, though
1622 IF QP_PREQ_GRP.G_ORDER_ID IS NULL THEN
1623 BEGIN
1624 SELECT line_id
1625 INTO QP_PREQ_GRP.G_ORDER_ID
1626 FROM qp_npreq_lines_tmp
1627 WHERE line_type_code = QP_PREQ_GRP.G_ORDER_LEVEL;
1628 EXCEPTION
1629 WHEN NO_DATA_FOUND THEN
1630 IF l_debug = FND_API.G_TRUE THEN
1631 QP_PREQ_GRP.engine_debug(' - summary line not present; cannot get order ID');
1632 END IF;
1633 END;
1634 IF l_debug = FND_API.G_TRUE THEN
1635 QP_PREQ_GRP.engine_debug(' ^ G_ORDER_ID was null, select statement got value '|| QP_PREQ_GRP.G_ORDER_ID);
1636 END IF;
1637 END IF;
1638
1639 -- get order line ID and pricing effective date
1640 -- these values are "cached" on the basis of order_id and line_index
1641 -- if the composite line_index+order_id changes, then need to retrieve new line_id/eff date
1642 -- else use the previously cached values
1643 IF (QP_PREQ_GRP.G_LINE_ID IS NULL OR
1644 QP_PREQ_GRP.G_PRICE_EFF_DATE IS NULL OR
1645 -- 4613884 bugfix
1646 TO_CHAR(l_line_index || QP_PREQ_GRP.G_ORDER_ID) <> TO_CHAR(l_prev_line_index || l_prev_order_id))
1647 THEN
1648 IF l_debug = FND_API.G_TRUE THEN
1649 QP_PREQ_GRP.engine_debug(' need to cache line_id, price_eff_date!');
1650 QP_PREQ_GRP.engine_debug(' - order_id ('|| qp_preq_grp.G_ORDER_ID ||'), prev_order_id ('|| l_prev_order_id || ')');
1651 QP_PREQ_GRP.engine_debug(' - line_index ('|| l_line_index ||'), prev_line_index ('|| l_prev_line_index || ')');
1652 END IF;
1653 SELECT lines.line_id, lines.pricing_effective_date
1654 INTO v_line_id, v_price_eff_date
1655 FROM qp_npreq_lines_tmp lines
1656 WHERE lines.line_index = l_line_index;
1657
1658 QP_PREQ_GRP.G_LINE_ID := v_line_id;
1659 QP_PREQ_GRP.G_PRICE_EFF_DATE := v_price_eff_date;
1660 l_prev_order_id := QP_PREQ_GRP.G_ORDER_ID;
1661 l_prev_line_index := l_line_index;
1662 ELSE
1663 IF l_debug = FND_API.G_TRUE THEN
1664 QP_PREQ_GRP.engine_debug(' using cached line_id, price_eff_date');
1665 END IF;
1666 v_line_id := QP_PREQ_GRP.G_LINE_ID;
1667 v_price_eff_date := QP_PREQ_GRP.G_PRICE_EFF_DATE;
1668 END IF;
1669
1670 IF l_debug = FND_API.G_TRUE THEN
1671 QP_PREQ_GRP.engine_debug(' order ID '|| QP_PREQ_GRP.G_ORDER_ID ||
1672 ', line ID '|| QP_PREQ_GRP.G_LINE_ID);
1673 END IF;
1674
1675 l_accum_rec.p_request_type_code := QP_PREQ_GRP.G_REQUEST_TYPE_CODE;
1676 l_accum_rec.context := l_accum_context;
1677 l_accum_rec.attribute := l_accum_attrib;
1678 IF (l_accum_context = 'VOLUME' AND l_accum_attrib = 'PRICING_ATTRIBUTE19') THEN
1679 l_accum := QP_TM_RUNTIME_SOURCE.Get_numeric_attribute_value(p_list_line_id,
1680 v_list_line_no,
1681 QP_PREQ_GRP.G_ORDER_ID,
1682 v_line_id,
1683 v_price_eff_date,
1684 l_req_attrs_tbl,
1688 l_accum := QP_RUNTIME_SOURCE.Get_numeric_attribute_value(p_list_line_id,
1685 l_accum_rec
1686 );
1687 ELSE
1689 v_list_line_no,
1690 QP_PREQ_GRP.G_ORDER_ID,
1691 v_line_id,
1692 v_price_eff_date,
1693 l_req_attrs_tbl,
1694 l_accum_rec
1695 );
1696 END IF;
1697 IF l_debug = FND_API.G_TRUE THEN
1698 QP_PREQ_GRP.engine_debug(' accum value returned from custom API = '|| l_accum);
1699 END IF;
1700 ELSE
1701 -- this case should almost never happen, but if it does, set accum=0
1702 IF l_debug = FND_API.G_TRUE THEN
1703 QP_PREQ_GRP.engine_debug('***oops. this should not be here!');
1704 QP_PREQ_GRP.engine_debug(' Profile Value = '|| FND_PROFILE.VALUE('QP_ACCUM_ATTR_ENABLED'));
1705 END IF;
1706 l_accum := 0;
1707 END IF;
1708 END;
1709
1710 END IF; -- if (l_accum_attrib is null)
1711 ELSE
1712 IF l_debug = FND_API.G_TRUE THEN
1713 QP_PREQ_GRP.engine_debug(' - Accumulation Profile is not enabled');
1714 END IF;
1715 l_accum := 0;
1716 END IF; -- if ACCUM_ATTR_ENABLED profile...
1717
1718 -- save accumulation value into global variable for Process_Price_Break to use
1719 l_accum_global := l_accum;
1720
1721 -- accumulation processing:
1722 -- if accumulation value, then remap the value_from and offset qualifier value as necessary
1723 IF l_debug = FND_API.G_TRUE THEN
1724 QP_PREQ_GRP.engine_debug(' * begin accumulation processing (if necessary) *');
1725 END IF;
1726 IF p_value_to <= l_accum THEN
1727 -- skip this break because no quantity is satisfied
1728 x_satisfied_value := 0;
1729 l_accum_global := nvl(l_accum_global, 0);
1730 IF l_debug = FND_API.G_TRUE THEN
1731 QP_PREQ_GRP.engine_debug(' - skipping this break');
1732 END IF;
1733 RETURN;
1734 ELSE
1735 IF l_accum <> 0 THEN
1736 -- remap value_from
1737 l_value_from := greatest(l_value_from, l_accum + l_precision);
1738 END IF;
1739 -- when l_accum=0, it means that either
1740 -- 1. no accumulation processing needed, or
1741 -- 2. accum has just started
1742 -- so no need to remap value_from
1743 END IF;
1744 -- offset qualifier value if necessary
1745 l_new_qualifier := l_accum + p_qualifier_value;
1746 IF l_debug = FND_API.G_TRUE THEN
1747 QP_PREQ_GRP.engine_debug(' l_accum = '|| l_accum);
1748 QP_PREQ_GRP.engine_debug(' l_value_from = '|| l_value_from);
1749 QP_PREQ_GRP.engine_debug(' l_new_qualifier = '|| l_new_qualifier);
1750 END IF;
1751
1752 -- shulin, fix bug 2372064
1753 -- 4999377, restrict shulin's fix to only 0-0 case
1754 IF (p_value_from = p_value_to AND p_value_from = 0) THEN -- for cases like 0-0, 2-2, 0.5-0.5
1755 IF l_debug = FND_API.G_TRUE THEN
1756 QP_PREQ_GRP.engine_debug('value_from is the same as value_to, x_satisfied_value is 0');
1757 END IF;
1758 x_satisfied_value := 0;
1759 ELSIF (l_new_qualifier - l_value_from + l_precision > 0) THEN
1760 x_satisfied_value := least(l_new_qualifier - l_value_from + l_precision, p_value_to - l_value_from + l_precision);
1761 ELSE
1762 x_satisfied_value := 0;
1763 END IF;
1764
1765 IF l_debug = FND_API.G_TRUE THEN
1766 QP_PREQ_GRP.engine_debug('Out of Procedure : Get_Satisfied_Range');
1767
1768 END IF;
1769 END Get_Satisfied_Range;
1770
1771
1772 -- this procedure is used by QPXPPREB.pls to perform price_break_Calculation
1773
1774 PROCEDURE Price_Break_Calculation(p_list_line_id NUMBER,
1775 p_break_type VARCHAR2,
1776 p_line_index NUMBER,
1777 p_req_value_per_unit NUMBER, -- Item qty,group qty,group value
1778 p_applied_req_value_per_unit NUMBER, -- [julin/4112395/4220399]
1779 p_total_value NUMBER, -- Total value (Group amount or item amount)
1780 p_list_price NUMBER,
1781 p_line_quantity NUMBER, -- remove default 0, Acutal quantity on order line
1782 p_bucketed_adjustment NUMBER,
1783 p_bucketed_flag VARCHAR2,
1784 p_automatic_flag VARCHAR2, -- 5413797
1785 x_adjustment_amount OUT NOCOPY NUMBER, --2388011_latest
1786 x_return_status OUT NOCOPY VARCHAR2, --2388011_latest
1787 x_return_status_text OUT NOCOPY VARCHAR2) AS --2388011_latest
1788
1789 -- 2388011_latest1
1790 CURSOR get_price_break_details IS
1791 SELECT rltd.related_line_detail_index
1795 , rltd.operand_calculation_code
1792 , rltd.list_line_id -- for accum range break
1793 , rltd.related_list_line_id
1794 , rltd.related_list_line_type
1796 , rltd.operand
1797 , rltd.pricing_group_sequence
1798 , rltd.relationship_type_detail price_break_type
1799 , rltd.setup_value_from value_from
1800 , rltd.setup_value_to value_to
1801 , rltd.qualifier_value
1802 , line.line_quantity ordered_qty
1803 , line.priced_quantity priced_qty
1804 , line.catchweight_qty catchweight_qty
1805 , line.actual_order_quantity actual_order_qty
1806 FROM qp_npreq_rltd_lines_tmp rltd, qp_npreq_lines_tmp line
1807 WHERE rltd.list_line_id = p_list_line_id
1808 AND rltd.line_index = p_line_index
1809 AND line.line_index = p_line_index
1810 AND rltd.pricing_status_code = QP_PREQ_GRP.G_STATUS_NEW
1811 AND rltd.line_index = line.line_index -- sql repos
1812 UNION
1813 SELECT rltd.related_line_detail_index
1814 , rltd.list_line_id -- for accum range break
1815 , rltd.related_list_line_id
1816 , rltd.related_list_line_type
1817 , rltd.operand_calculation_code
1818 , rltd.operand
1819 , rltd.pricing_group_sequence
1820 , rltd.relationship_type_detail price_break_type
1821 , rltd.setup_value_from value_from
1822 , rltd.setup_value_to value_to
1823 , rltd.qualifier_value
1824 , line.line_quantity ordered_qty
1825 , line.priced_quantity priced_qty
1826 , line.catchweight_qty catchweight_qty
1827 , line.actual_order_quantity actual_order_qty
1828 FROM qp_npreq_rltd_lines_tmp rltd, qp_npreq_lines_tmp line
1829 WHERE QP_PREQ_GRP.G_PUBLIC_API_CALL_FLAG = QP_PREQ_GRP.G_YES
1830 AND rltd.line_index = p_line_index
1831 AND line_detail_index = p_list_line_id
1832 AND line.line_index = p_line_index
1833 AND rltd.pricing_status_code = QP_PREQ_GRP.G_STATUS_NEW
1834 AND rltd.line_index = line.line_index -- sql repos
1835 ORDER BY 7, 9;
1836 -- 5169222 added order by 9 to sort breaks by ascending value from
1837
1838 pbh_child_rec get_price_break_details%ROWTYPE;
1839
1840 l_sign NUMBER;
1841 l_adjustment_amount NUMBER;
1842 l_return_status VARCHAR2(30);
1843 l_return_status_text VARCHAR2(240);
1844
1845 TYPE pbh_adjustment_rec IS RECORD (
1846 l_related_list_line_id NUMBER,
1847 l_related_line_detail_index NUMBER,
1848 l_adjustment_amount NUMBER,
1849 l_satisfied_value NUMBER,
1850 l_raw_satisfied_value NUMBER); -- 5335689
1851
1852 TYPE pbh_adjustment_tab IS TABLE OF pbh_adjustment_rec INDEX BY BINARY_INTEGER;
1853 TYPE pbh_child_tbl IS TABLE OF get_price_break_details%ROWTYPE INDEX BY BINARY_INTEGER;
1854
1855 pbh_adjustments pbh_adjustment_tab;
1856 pbh_child_dtl_tbl pbh_child_tbl;
1857
1858 i NUMBER := 0;
1859 l_qualifier_value NUMBER := 0;
1860 l_total_value NUMBER := 0;
1861 l_satisfied_value NUMBER;
1862 l_range_adjustment NUMBER := 0;
1863 l_ord_qty_operand NUMBER := 0;
1864 l_ord_qty_adj_amt NUMBER := 0;
1865 v_attribute_value_per_unit NUMBER := 0; --2388011_latest
1866 l_priced_quantity NUMBER := 0; -- fte breakunit
1867 l_routine VARCHAR2(50) := 'QP_Calculate_Price_PUB.Price_Break_Calculation';
1868
1869 -- 4061138, 5183755 continuous price breaks
1870 CURSOR get_continuous_flag_cur IS
1871 SELECT nvl(qpll.continuous_price_break_flag, 'N')
1872 FROM qp_list_lines qpll, qp_npreq_rltd_lines_tmp rltd
1873 WHERE rltd.line_index = p_line_index
1874 AND rltd.line_detail_index = p_list_line_id
1875 AND rltd.pricing_status_code = QP_PREQ_GRP.G_STATUS_NEW
1876 AND rltd.list_line_id = qpll.list_line_id
1877 AND ROWNUM = 1;
1878
1879 l_continuous_flag VARCHAR2(1) := 'N';
1880 l_break_qualified VARCHAR2(1) := 'N';
1881 l_del_index_tbl QP_PREQ_GRP.NUMBER_TYPE;
1882
1883 BEGIN
1884
1885 x_adjustment_amount := 0;
1886 l_line_index := p_line_index; -- accum range break
1887
1888 -- 4061138, 5183755
1889 OPEN get_continuous_flag_cur;
1890 FETCH get_continuous_flag_cur INTO l_continuous_flag;
1891 CLOSE get_continuous_flag_cur;
1892
1893 IF l_debug = FND_API.G_TRUE THEN
1894 QP_PREQ_GRP.engine_debug('In Procedure '|| l_routine || '...');
1895 IF l_continuous_flag = 'Y' THEN
1896 QP_PREQ_GRP.engine_debug('---------- CONTINUOUS PRICE BREAK ----------');
1897 END IF;
1898 QP_PREQ_GRP.engine_debug('SL with net_amt changes.'); -- 2892848
1899 QP_PREQ_GRP.engine_debug('p_list_line_id: '|| p_list_line_id);
1900 QP_PREQ_GRP.engine_debug('p_line_index: '|| p_line_index);
1901 QP_PREQ_GRP.engine_debug('p_req_value_per_unit: '|| p_req_value_per_unit);
1902 QP_PREQ_GRP.engine_debug('p_total_value: '|| p_total_value);
1903 QP_PREQ_GRP.engine_debug('p_list_price: '|| p_list_price); -- 2892848
1904 QP_PREQ_GRP.engine_debug('p_line_quantity: '|| p_line_quantity);
1905 QP_PREQ_GRP.engine_debug('p_bucketed_adjustment: '|| p_bucketed_adjustment);
1906 QP_PREQ_GRP.engine_debug('p_bucketed_flag: '|| p_bucketed_flag);
1907 END IF;
1908
1909 OPEN get_price_break_details;
1910
1911 LOOP
1912 i := i + 1;
1913
1917 IF (p_bucketed_flag = 'Y') THEN
1914 FETCH get_price_break_details INTO pbh_child_rec;
1915 EXIT WHEN get_price_break_details%NOTFOUND;
1916
1918 --pbh_child_rec.qualifier_value := p_total_value + nvl(p_bucketed_adjustment*p_line_quantity,0);
1919 pbh_child_rec.qualifier_value := p_bucketed_adjustment; -- 2892848
1920 ELSE
1921 IF (p_total_value > 0) THEN -- ravi_new
1922 pbh_child_rec.qualifier_value := p_total_value;
1923 ELSE
1924 pbh_child_rec.qualifier_value := p_req_value_per_unit;
1925 END IF;
1926 END IF;
1927
1928 IF l_debug = FND_API.G_TRUE THEN
1929 QP_PREQ_GRP.engine_debug('PBH Qualifier Value : ' || pbh_child_rec.qualifier_value);
1930 QP_PREQ_GRP.engine_debug('PBH Value From : ' || pbh_child_rec.value_from);
1931 QP_PREQ_GRP.engine_debug('PBH Value To : ' || pbh_child_rec.value_to);
1932 QP_PREQ_GRP.engine_debug('PBH Break Type : ' || p_break_type);
1933 END IF;
1934
1935 IF (p_break_type = QP_PREQ_GRP.G_POINT_BREAK) THEN
1936 IF l_debug = FND_API.G_TRUE THEN
1937 QP_PREQ_GRP.engine_debug('i am in POINT block...');
1938 QP_PREQ_GRP.engine_debug('Before PBH List Line Type : ' || pbh_child_rec.related_list_line_type);
1939 END IF;
1940
1941 -- rewritten for 4061138
1942 IF ((l_continuous_flag <> 'Y' AND
1943 pbh_child_rec.qualifier_value BETWEEN pbh_child_rec.value_from AND pbh_child_rec.value_to)
1944 OR
1945 (l_continuous_flag = 'Y' AND
1946 pbh_child_rec.value_from < pbh_child_rec.qualifier_value AND
1947 pbh_child_rec.qualifier_value <= pbh_child_rec.value_to) )
1948 THEN
1949
1950 l_break_qualified := 'Y'; --[julin/4671446]
1951
1952 IF (pbh_child_rec.related_list_line_type = QP_PREQ_GRP.G_DISCOUNT) THEN
1953 l_sign := 1;
1954 ELSIF (pbh_child_rec.related_list_line_type = QP_PREQ_GRP.G_SURCHARGE) THEN
1955 l_sign := - 1;
1956 END IF;
1957
1958 IF (pbh_child_rec.operand_calculation_code = QP_PREQ_GRP.G_NEWPRICE_DISCOUNT) THEN
1959 l_sign := 1; -- new price discount/surcharge are the same
1960 END IF;
1961
1962 IF l_debug = FND_API.G_TRUE THEN
1963 QP_PREQ_GRP.engine_debug('l sign: '|| l_sign);
1964 END IF;
1965 -- fte breakunit
1966 IF (pbh_child_rec.operand_calculation_code = QP_PREQ_GRP.G_BREAKUNIT_PRICE AND
1967 p_total_value > 0)
1968 THEN
1969 IF l_debug = FND_API.G_TRUE THEN
1970 QP_PREQ_GRP.engine_debug('fte breakunit price, using p_total_value = '|| p_total_value);
1971 END IF;
1972 l_priced_quantity := p_total_value;
1973 ELSIF (p_applied_req_value_per_unit > 0) THEN -- [julin/4112395/4220399]
1974 l_priced_quantity := p_applied_req_value_per_unit;
1975 ELSE
1976 l_priced_quantity := p_req_value_per_unit;
1977 END IF;
1978
1979 IF l_debug = FND_API.G_TRUE THEN
1980 QP_PREQ_GRP.engine_debug('l_priced_quantity '|| l_priced_quantity);
1981 END IF; --l_debug
1982
1983 --4900095 lumpsum
1984 IF pbh_child_rec.operand_calculation_code = QP_PREQ_GRP.G_LUMPSUM_DISCOUNT
1985 AND QP_PREQ_GRP.G_service_line_ind_tbl.EXISTS(p_line_index) THEN
1986 IF QP_PREQ_PUB.G_PBH_MOD_LEVEL_CODE.EXISTS(p_list_line_id)
1987 AND QP_PREQ_PUB.G_PBH_MOD_LEVEL_CODE(p_list_line_id) = 'LINE' THEN
1988 l_priced_quantity := nvl(p_applied_req_value_per_unit,
1989 QP_PREQ_GRP.G_service_line_qty_tbl(p_line_index));
1990 IF l_debug = FND_API.G_TRUE THEN
1991 QP_PREQ_GRP.engine_debug('service line pbh with line level');
1992 END IF; --l_debug
1993 --p_request_qty * l_parent_qty;
1994 ELSIF QP_PREQ_PUB.G_PBH_MOD_LEVEL_CODE.EXISTS(p_list_line_id)
1995 AND QP_PREQ_PUB.G_PBH_MOD_LEVEL_CODE(p_list_line_id) = 'LINEGROUP' THEN
1996 l_priced_quantity := nvl(p_applied_req_value_per_unit,
1997 QP_PREQ_GRP.G_service_ldet_qty_tbl(p_list_line_id));
1998 IF l_debug = FND_API.G_TRUE THEN
1999 QP_PREQ_GRP.engine_debug('service line pbh with linegrp level');
2000 END IF; --l_debug
2001 ELSE
2002 l_priced_quantity := nvl(p_applied_req_value_per_unit, p_req_value_per_unit);
2003 IF l_debug = FND_API.G_TRUE THEN
2004 QP_PREQ_GRP.engine_debug('non-service line pbh with linegrp level');
2005 END IF; --l_debug
2006 END IF; --QP_PREQ_GRP.G_service_line_ind_tbl.exists
2007
2008 IF l_debug = FND_API.G_TRUE THEN
2009 QP_PREQ_GRP.engine_debug('lumpsum qty '|| l_priced_quantity);
2010 END IF; --l_debug
2011 END IF; --pbh_child_rec.operand_calculation_code
2012
2013 Calculate_Adjustment(p_list_price,
2014 pbh_child_rec.operand_calculation_code,
2015 pbh_child_rec.operand * l_sign,
2016 --pbh_child_rec.qualifier_value ,
2017 --p_req_value_per_unit, -- ravi_new
2018 l_priced_quantity, -- fte breakunit
2019 l_adjustment_amount,
2020 l_return_status,
2021 l_return_status_text);
2022
2023 IF l_debug = FND_API.G_TRUE THEN
2027 pbh_adjustments(i).l_adjustment_amount := l_adjustment_amount;
2024 QP_PREQ_GRP.engine_debug('PBH Adjustment : ' || l_adjustment_amount);
2025 END IF;
2026
2028
2029 -- begin 2892848
2030 IF (p_bucketed_flag = 'Y') THEN
2031 pbh_adjustments(i).l_satisfied_value := pbh_child_rec.qualifier_value;
2032 IF l_debug = FND_API.G_TRUE THEN
2033 QP_PREQ_GRP.engine_debug('POINT net amount break l_satisfied_value: '|| pbh_adjustments(i).l_satisfied_value);
2034 END IF; -- END IF l_debug
2035 ELSE -- end 2892848
2036
2037 -- ravi_new
2038 IF (p_total_value > 0 AND p_req_value_per_unit <> 0) THEN --6896139 added to avoid divide by zero error
2039 v_attribute_value_per_unit := p_total_value / p_req_value_per_unit; --2388011_latest
2040 -- bug 3603096 - commented if..else
2041 --IF (pbh_child_rec.operand_calculation_code = QP_PREQ_GRP.G_BREAKUNIT_PRICE) THEN
2042 pbh_adjustments(i).l_satisfied_value := pbh_child_rec.qualifier_value; -- fte breakunit
2043 --ELSE
2044 -- pbh_adjustments(i).l_satisfied_value := pbh_child_rec.qualifier_value/v_attribute_value_per_unit; --2388011_latest
2045 --END IF;
2046 IF l_debug = FND_API.G_TRUE THEN
2047 QP_PREQ_GRP.engine_debug('p_total_value > 0');
2048 QP_PREQ_GRP.engine_debug('v_attribute_value_per_unit (p_total_value/p_req_value_per_unit): ' || v_attribute_value_per_unit);
2049 QP_PREQ_GRP.engine_debug('l_satisfied_value (qualifier_value/v_attribute_value_per_unit): '|| pbh_adjustments(i).l_satisfied_value);
2050 END IF; -- END IF l_debug
2051 ELSE
2052 pbh_adjustments(i).l_satisfied_value := pbh_child_rec.qualifier_value;
2053 IF l_debug = FND_API.G_TRUE THEN
2054 QP_PREQ_GRP.engine_debug('POINT break l_satisfied_value: '|| pbh_adjustments(i).l_satisfied_value);
2055 END IF; -- END IF l_debug
2056 END IF; -- END IF (p_total_value > 0)
2057 END IF; -- END IF (p_bucketed_flag = 'Y') 2892848
2058
2059 x_adjustment_amount := l_adjustment_amount;
2060 IF l_debug = FND_API.G_TRUE THEN
2061 QP_PREQ_GRP.engine_debug('POINT break x_adjustment_amount: '|| x_adjustment_amount);
2062 END IF; -- END IF l_debug
2063 -- fte breakunit
2064 -- Normally, point breaks assume the adj amt on the header is the same as
2065 -- the single qualifying child line. This is true for regular breaks, but
2066 -- for block pricing (breakunit), the header and line quantity may not be
2067 -- the same UOM, so need to calculate a different adj amt for the PBH.
2068 IF (pbh_child_rec.operand_calculation_code = QP_PREQ_GRP.G_BREAKUNIT_PRICE AND
2069 p_total_value > 0)
2070 THEN
2071 x_adjustment_amount := l_adjustment_amount * l_priced_quantity / p_req_value_per_unit;
2072 IF l_debug = FND_API.G_TRUE THEN
2073 QP_PREQ_GRP.engine_debug('fte breakunit, PBH x_adjustment_amount: '|| x_adjustment_amount);
2074 END IF;
2075 END IF;
2076 ELSE
2077 pbh_adjustments(i).l_adjustment_amount := NULL;
2078 pbh_adjustments(i).l_satisfied_value := 0;
2079 END IF;
2080
2081 pbh_adjustments(i).l_related_list_line_id := pbh_child_rec.related_list_line_id;
2082 pbh_adjustments(i).l_related_line_detail_index := pbh_child_rec.related_line_detail_index;
2083 pbh_child_dtl_tbl(i) := pbh_child_rec;
2084
2085 ELSIF (p_break_type = QP_PREQ_GRP.G_RANGE_BREAK) THEN
2086
2087 IF l_debug = FND_API.G_TRUE THEN
2088 QP_PREQ_GRP.engine_debug('i am in RANGE block...');
2089 END IF; -- END IF l_debug
2090
2091 IF (pbh_child_rec.related_list_line_type = QP_PREQ_GRP.G_DISCOUNT) THEN
2092 l_sign := 1;
2093 ELSIF (pbh_child_rec.related_list_line_type = QP_PREQ_GRP.G_SURCHARGE) THEN
2094 l_sign := - 1;
2095 END IF;
2096
2097 IF (pbh_child_rec.operand_calculation_code = QP_PREQ_GRP.G_NEWPRICE_DISCOUNT) THEN
2098 l_sign := 1; -- new price discount/surcharge are the same
2099 END IF;
2100
2101 IF l_debug = FND_API.G_TRUE THEN
2102 QP_PREQ_GRP.engine_debug('l sign: '|| l_sign);
2103 END IF; -- END IF l_debug
2104
2105 IF l_debug = FND_API.G_TRUE THEN
2106 QP_PREQ_GRP.engine_debug('l_total_value now: '|| l_total_value || ' is used to get_satisfied_range.');
2107 END IF; -- END IF l_debug
2108
2109 Get_Satisfied_Range(pbh_child_rec.value_from, pbh_child_rec.value_to, pbh_child_rec.qualifier_value,
2110 pbh_child_rec.list_line_id, l_continuous_flag, 'N', l_satisfied_value);
2111
2112 IF l_debug = FND_API.G_TRUE THEN
2113 QP_PREQ_GRP.engine_debug('l_satisfied_range from Get_Satisfied_Range: '|| l_satisfied_value);
2114 END IF; -- END IF l_debug
2115 pbh_adjustments(i).l_raw_satisfied_value := l_satisfied_value; -- 5335689
2116 IF l_satisfied_value > 0 THEN
2117 l_break_qualified := 'Y'; --[julin/4671446]
2118 END IF;
2119
2120 IF (p_bucketed_flag = 'Y') THEN
2121 pbh_adjustments(i).l_satisfied_value := l_satisfied_value / p_list_price; -- this is the p_price_qty for Calculate_Adjustment
2122 ELSE
2123 -- ravi_new
2124 IF (p_total_value > 0) THEN
2128 IF l_debug = FND_API.G_TRUE THEN
2125 v_attribute_value_per_unit := p_total_value / p_req_value_per_unit; --2388011_latest
2126 --pbh_adjustments(i).l_satisfied_value := pbh_child_rec.qualifier_value/v_attribute_value_per_unit; --2388011_latest
2127 pbh_adjustments(i).l_satisfied_value := l_satisfied_value / v_attribute_value_per_unit; -- SHU diff RAVI
2129 QP_PREQ_GRP.engine_debug('p_total_value >= 0');
2130 QP_PREQ_GRP.engine_debug('v_attribute_value_per_unit (p_total_value/p_req_value_per_unit): ' || v_attribute_value_per_unit);
2131 QP_PREQ_GRP.engine_debug('l_raw_satisfied_value: ' || pbh_adjustments(i).l_raw_satisfied_value);
2132 QP_PREQ_GRP.engine_debug('l_satisfied_value assigned back to this child break: '|| pbh_adjustments(i).l_satisfied_value);
2133 END IF; -- END IF l_debug
2134 ELSE
2135 pbh_adjustments(i).l_satisfied_value := l_satisfied_value;
2136 IF l_debug = FND_API.G_TRUE THEN
2137 QP_PREQ_GRP.engine_debug('l_satisfied_value assigned back to this child break: '|| pbh_adjustments(i).l_satisfied_value);
2138 END IF; -- END IF l_debug
2139 END IF; -- END IF (p_total_value > 0)
2140 END IF; -- END IF (p_bucketed_flag = 'Y') THEN
2141
2142 --If operator is lumpsum and l_satisfied_value is zero do not calculate
2143 IF l_satisfied_value = 0 THEN
2144 l_adjustment_amount := NULL;
2145 ELSE
2146 Calculate_Adjustment(p_list_price,
2147 pbh_child_rec.operand_calculation_code,
2148 pbh_child_rec.operand * l_sign,
2149 pbh_adjustments(i).l_satisfied_value, -- SL, p_priced_qty
2150 l_adjustment_amount,
2151 l_return_status,
2152 l_return_status_text);
2153 END IF;
2154
2155 pbh_adjustments(i).l_related_list_line_id := pbh_child_rec.related_list_line_id;
2156 pbh_adjustments(i).l_related_line_detail_index := pbh_child_rec.related_line_detail_index;
2157 pbh_adjustments(i).l_adjustment_amount := l_adjustment_amount;
2158 pbh_child_dtl_tbl(i) := pbh_child_rec;
2159
2160 IF l_debug = FND_API.G_TRUE THEN
2161 QP_PREQ_GRP.engine_debug('Adjustment : (' || i || ') ' || l_adjustment_amount);
2162 QP_PREQ_GRP.engine_debug('Satisfied Value : (' || i || ') ' || l_satisfied_value);
2163 QP_PREQ_GRP.engine_debug('Total Value for next child line: (' || i || ') ' || l_total_value);
2164 END IF; -- END IF l_debug
2165
2166 END IF; -- END IF (p_break_type = POINT or RANGE
2167
2168 END LOOP;
2169
2170 CLOSE get_price_break_details;
2171
2172 IF (p_break_type = QP_PREQ_GRP.G_RANGE_BREAK) THEN
2173 FOR i IN 1 .. pbh_adjustments.COUNT
2174 LOOP
2175
2176 l_range_adjustment := l_range_adjustment + nvl(pbh_adjustments(i).l_adjustment_amount *
2177 pbh_adjustments(i).l_satisfied_value, 0);
2178
2179 IF l_debug = FND_API.G_TRUE THEN
2180 QP_PREQ_GRP.engine_debug('--- '||'break child '|| i ||' ---');
2181 QP_PREQ_GRP.engine_debug('l_adjustment_amount: '|| pbh_adjustments(i).l_adjustment_amount);
2182 QP_PREQ_GRP.engine_debug('l_satisfied_value: '|| nvl(pbh_adjustments(i).l_raw_satisfied_value,
2183 pbh_adjustments(i).l_satisfied_value));
2184 QP_PREQ_GRP.engine_debug('l_range_adjustment total: '|| l_range_adjustment);
2185 END IF; -- END IF l_debug
2186 END LOOP;
2187 x_adjustment_amount := l_range_adjustment / p_req_value_per_unit; -- ravi_new
2188 IF l_debug = FND_API.G_TRUE THEN
2189 QP_PREQ_GRP.engine_debug('Range Break Averaged Adjustment : ' || x_adjustment_amount);
2190 END IF; -- END IF l_debug
2191
2192 END IF;
2193
2194 -- Update the adjustments/satisfied ranges info on line details
2195 IF (QP_PREQ_GRP.G_PUBLIC_API_CALL_FLAG = QP_PREQ_GRP.G_YES) THEN
2196 IF (pbh_adjustments.COUNT > 0) THEN
2197 IF l_debug = FND_API.G_TRUE THEN
2198 QP_PREQ_GRP.engine_debug('updating child pbh lines '|| pbh_adjustments.COUNT);
2199 QP_PREQ_GRP.engine_debug('l_break_qualified=' || l_break_qualified);
2200 END IF; -- END IF l_debug
2201 IF l_break_qualified = 'Y' THEN
2202 FOR i IN pbh_adjustments.FIRST .. pbh_adjustments.LAST LOOP
2203 l_ord_qty_operand := 0;
2204 l_ord_qty_adj_amt := 0;
2205
2206 BEGIN
2207 QP_PREQ_PUB.GET_ORDERQTY_VALUES(p_ordered_qty =>
2208 pbh_child_dtl_tbl(i).ordered_qty,
2209 p_priced_qty =>
2210 pbh_child_dtl_tbl(i).priced_qty,
2211 p_catchweight_qty =>
2212 pbh_child_dtl_tbl(i).catchweight_qty,
2213 p_actual_order_qty =>
2214 pbh_child_dtl_tbl(i).actual_order_qty,
2215 p_operand => pbh_child_dtl_tbl(i).operand,
2216 p_adjustment_amt =>
2217 pbh_adjustments(i).l_adjustment_amount,
2218 p_operand_calculation_code =>
2219 pbh_child_dtl_tbl(i).operand_calculation_code,
2223 x_return_status => x_return_status,
2220 p_input_type => 'OPERAND',
2221 x_ordqty_output1 => l_ord_qty_operand,
2222 x_ordqty_output2 => l_ord_qty_adj_amt,
2224 x_return_status_text => x_return_status_text);
2225 EXCEPTION
2226 WHEN OTHERS THEN
2227 NULL;
2228 END;
2229 IF l_debug = FND_API.G_TRUE THEN
2230 QP_PREQ_GRP.engine_debug('child dtl adj amt '
2231 || pbh_adjustments(i).l_adjustment_amount
2232 ||' l_satisfied_value '|| pbh_adjustments(i).l_satisfied_value
2233 ||' ordqtyadjamt '|| l_ord_qty_adj_amt
2234 ||' order_qty_operand '|| l_ord_qty_operand
2235 ||' dtl index '|| pbh_adjustments(i).l_related_line_detail_index);
2236 END IF; -- END IF l_debug
2237
2238 UPDATE qp_npreq_ldets_tmp
2239 --changes for bug 2264566 update adjamt on child lines of manualpbh
2240 SET adjustment_amount = - (pbh_adjustments(i).l_adjustment_amount),
2241 --SET adjustment_amount = l_sign * pbh_adjustments(i).l_adjustment_amount,
2242 line_quantity = nvl(pbh_adjustments(i).l_raw_satisfied_value, -- 5335689
2243 pbh_adjustments(i).l_satisfied_value)
2244 , order_qty_adj_amt = - (l_ord_qty_adj_amt) -- bug 3285662
2245 , order_qty_operand = l_ord_qty_operand
2246 WHERE line_detail_index = pbh_adjustments(i).l_related_line_detail_index
2247 AND pricing_status_code IN (QP_PREQ_GRP.G_STATUS_NEW,
2248 --changes for bug 2264566 update adjamt on child lines of manualpbh
2249 QP_PREQ_GRP.G_STATUS_UNCHANGED);
2250 END LOOP;
2251 ELSE
2252 IF p_automatic_flag <> QP_PREQ_PUB.G_NO THEN -- 5413797
2253 l_del_index_tbl(1) := p_list_line_id;
2254 QP_Resolve_Incompatability_PVT.Delete_Ldets_Complete(
2255 l_del_index_tbl, 'No breaks qualified', x_return_status, x_return_status_text);
2256 END IF;
2257 END IF;
2258 END IF;
2259 END IF;
2260
2261
2262 EXCEPTION
2263 WHEN OTHERS THEN
2264 IF l_debug = FND_API.G_TRUE THEN
2265 QP_PREQ_GRP.ENGINE_DEBUG('Error in price break calculation: '|| SQLERRM);
2266 END IF; -- END IF l_debug
2267 x_return_status := FND_API.G_RET_STS_ERROR;
2268 x_return_status_text := l_routine ||' '|| SQLERRM;
2269 END Price_Break_Calculation;
2270
2271 PROCEDURE Price_Break_Calculation(p_list_line_id NUMBER,
2272 p_break_type VARCHAR2,
2273 p_line_index NUMBER,
2274 p_request_qty NUMBER,
2275 p_list_price NUMBER,
2276 x_adjustment_amount OUT NOCOPY NUMBER,
2277 x_return_status OUT NOCOPY VARCHAR2,
2278 x_return_status_text OUT NOCOPY VARCHAR2) AS
2279
2280 CURSOR get_price_break_details IS
2281 SELECT related_line_detail_index
2282 , related_list_line_id
2283 , related_list_line_type
2284 , list_line_id -- for accum range break
2285 , operand_calculation_code
2286 , operand
2287 , pricing_group_sequence
2288 , relationship_type_detail price_break_type
2289 , setup_value_from value_from
2290 , setup_value_to value_to
2291 , qualifier_value
2292 FROM qp_npreq_rltd_lines_tmp
2293 WHERE QP_PREQ_GRP.G_PUBLIC_API_CALL_FLAG = QP_PREQ_GRP.G_NO
2294 AND list_line_id = p_list_line_id
2295 AND line_index = p_line_index
2296 AND pricing_status_code = QP_PREQ_GRP.G_STATUS_NEW
2297 --fix for bug 2515762 added the union clause to distinguish between
2298 --the passed-in rltd lines of PBH modifier versus the engine-inserted
2299 --when called from public API
2300 --made corresponding changes in the public API to pass the dtl_index
2301 UNION
2302 SELECT related_line_detail_index
2303 , related_list_line_id
2304 , related_list_line_type
2305 , list_line_id -- for accum range break
2306 , operand_calculation_code
2307 , operand
2308 , pricing_group_sequence
2309 , relationship_type_detail price_break_type
2310 , setup_value_from value_from
2311 , setup_value_to value_to
2312 , qualifier_value
2313 FROM qp_npreq_rltd_lines_tmp
2314 WHERE QP_PREQ_GRP.G_PUBLIC_API_CALL_FLAG = QP_PREQ_GRP.G_YES
2315 AND line_index = p_line_index
2316 AND line_detail_index = p_list_line_id
2317 AND pricing_status_code = QP_PREQ_GRP.G_STATUS_NEW
2318 ORDER BY 7 ;
2319
2320 pbh_child_rec get_price_break_details%ROWTYPE;
2321
2322 l_sign NUMBER;
2323 l_adjustment_amount NUMBER;
2324 l_return_status VARCHAR2(30);
2325 l_return_status_text VARCHAR2(240);
2326
2327 TYPE pbh_adjustment_rec IS RECORD (
2328 l_related_list_line_id NUMBER,
2329 l_related_line_detail_index NUMBER,
2333 TYPE pbh_adjustment_tab IS TABLE OF pbh_adjustment_rec INDEX BY BINARY_INTEGER;
2330 l_adjustment_amount NUMBER,
2331 l_satisfied_value NUMBER);
2332
2334
2335 pbh_adjustments pbh_adjustment_tab;
2336
2337 i NUMBER := 0;
2338 l_qualifier_value NUMBER := 0;
2339 l_total_value NUMBER;
2340 l_satisfied_value NUMBER;
2341 l_range_adjustment NUMBER := 0;
2342 l_routine VARCHAR2(50) := 'QP_Calculate_Price_PUB.Price_Break_Calculation';
2343
2344 -- 4061138, 5183755 continuous price breaks
2345 CURSOR get_continuous_flag_cur IS
2346 SELECT nvl(continuous_price_break_flag, 'N')
2347 FROM qp_list_lines
2348 WHERE QP_PREQ_GRP.G_PUBLIC_API_CALL_FLAG = QP_PREQ_GRP.G_NO
2349 AND list_line_id = p_list_line_id
2350 AND ROWNUM = 1
2351 UNION
2352 SELECT nvl(qpll.continuous_price_break_flag, 'N')
2353 FROM qp_list_lines qpll, qp_npreq_rltd_lines_tmp rltd
2354 WHERE QP_PREQ_GRP.G_PUBLIC_API_CALL_FLAG = QP_PREQ_GRP.G_YES
2355 AND rltd.line_index = p_line_index
2356 AND rltd.line_detail_index = p_list_line_id
2357 AND rltd.list_line_id = qpll.list_line_id
2358 AND ROWNUM = 1;
2359
2360 l_continuous_flag VARCHAR2(1) := 'N';
2361
2362 BEGIN
2363
2364 l_debug := QP_PREQ_GRP.G_DEBUG_ENGINE;
2365 IF l_debug = FND_API.G_TRUE THEN
2366 QP_PREQ_GRP.engine_debug('#90 p_id '|| p_list_line_id ||' pub_api_call_flg '
2367 || QP_PREQ_GRP.G_PUBLIC_API_CALL_FLAG);
2368 END IF;
2369 x_adjustment_amount := 0;
2370 l_line_index := p_line_index; -- accum range break
2371 IF l_debug = FND_API.G_TRUE THEN
2372 QP_PREQ_GRP.engine_debug('#100');
2373
2374 END IF;
2375
2376 -- 4061138, 5183755
2377 OPEN get_continuous_flag_cur;
2378 FETCH get_continuous_flag_cur INTO l_continuous_flag;
2379 CLOSE get_continuous_flag_cur;
2380
2381 OPEN get_price_break_details;
2382
2383 IF l_debug = FND_API.G_TRUE THEN
2384 QP_PREQ_GRP.engine_debug('#101');
2385
2386 END IF;
2387 LOOP
2388
2389 IF l_debug = FND_API.G_TRUE THEN
2390 QP_PREQ_GRP.engine_debug('#102');
2391 END IF;
2392
2393 i := i + 1;
2394
2395 FETCH get_price_break_details INTO pbh_child_rec;
2396 EXIT WHEN get_price_break_details%NOTFOUND;
2397
2398 IF (QP_PREQ_GRP.G_PUBLIC_API_CALL_FLAG = QP_PREQ_GRP.G_YES) THEN
2399 pbh_child_rec.qualifier_value := p_request_qty;
2400 END IF;
2401
2402 IF l_debug = FND_API.G_TRUE THEN
2403 QP_PREQ_GRP.engine_debug('PBH Qualifier Value : ' || pbh_child_rec.qualifier_value);
2404 QP_PREQ_GRP.engine_debug('PBH Value From : ' || pbh_child_rec.value_from);
2405 QP_PREQ_GRP.engine_debug('PBH Value To : ' || pbh_child_rec.value_to);
2406 QP_PREQ_GRP.engine_debug('PBH Break Type : ' || p_break_type);
2407
2408 END IF;
2409
2410 IF (p_break_type = QP_PREQ_GRP.G_POINT_BREAK) THEN
2411
2412 IF l_debug = FND_API.G_TRUE THEN
2413 QP_PREQ_GRP.engine_debug('Before PBH List Line Type : ' || pbh_child_rec.related_list_line_type);
2414 END IF;
2415 -- rewritten for 4061138
2416 IF ((l_continuous_flag <> 'Y' AND
2417 pbh_child_rec.qualifier_value BETWEEN pbh_child_rec.value_from AND pbh_child_rec.value_to)
2418 OR
2419 (l_continuous_flag = 'Y' AND
2420 pbh_child_rec.value_from < pbh_child_rec.qualifier_value AND
2421 pbh_child_rec.qualifier_value <= pbh_child_rec.value_to) )
2422 THEN
2423 IF l_debug = FND_API.G_TRUE THEN
2424 QP_PREQ_GRP.engine_debug('In PBH List Line Type : ' || pbh_child_rec.related_list_line_type);
2425
2426 END IF;
2427 IF (pbh_child_rec.related_list_line_type = QP_PREQ_GRP.G_DISCOUNT) THEN
2428 l_sign := 1;
2429 ELSIF (pbh_child_rec.related_list_line_type = QP_PREQ_GRP.G_SURCHARGE) THEN
2430 l_sign := - 1;
2431 END IF;
2432
2433 IF (pbh_child_rec.operand_calculation_code = QP_PREQ_GRP.G_NEWPRICE_DISCOUNT) THEN
2434 l_sign := 1; -- new price discount/surcharge are the same
2435 END IF;
2436 IF l_debug = FND_API.G_TRUE THEN
2437 QP_PREQ_GRP.engine_debug('l sign: '|| l_sign);
2438
2439 END IF;
2440 Calculate_Adjustment(p_list_price,
2441 pbh_child_rec.operand_calculation_code,
2442 pbh_child_rec.operand * l_sign,
2443 p_request_qty,
2444 l_adjustment_amount,
2445 l_return_status,
2446 l_return_status_text);
2447
2448 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
2449 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2450 END IF;
2451 IF l_debug = FND_API.G_TRUE THEN
2452 QP_PREQ_GRP.engine_debug('PBH Adjustment : ' || l_adjustment_amount);
2453
2454 END IF;
2455 pbh_adjustments(i).l_adjustment_amount := l_adjustment_amount;
2456 pbh_adjustments(i).l_satisfied_value := pbh_child_rec.qualifier_value;
2457
2458 x_adjustment_amount := l_adjustment_amount;
2459 ELSE
2460 pbh_adjustments(i).l_adjustment_amount := NULL;
2464 pbh_adjustments(i).l_related_list_line_id := pbh_child_rec.related_list_line_id;
2461 pbh_adjustments(i).l_satisfied_value := 0;
2462 END IF;
2463
2465 pbh_adjustments(i).l_related_line_detail_index := pbh_child_rec.related_line_detail_index;
2466
2467 ELSIF (p_break_type = QP_PREQ_GRP.G_RANGE_BREAK) THEN
2468
2469 IF (l_qualifier_value = 0) THEN
2470 IF l_debug = FND_API.G_TRUE THEN
2471 QP_PREQ_GRP.engine_debug('Range Break');
2472 END IF;
2473 l_qualifier_value := pbh_child_rec.qualifier_value;
2474 l_total_value := pbh_child_rec.qualifier_value;
2475 END IF;
2476
2477 IF (pbh_child_rec.related_list_line_type = QP_PREQ_GRP.G_DISCOUNT) THEN
2478 l_sign := 1;
2479 ELSIF (pbh_child_rec.related_list_line_type = QP_PREQ_GRP.G_SURCHARGE) THEN
2480 l_sign := - 1;
2481 END IF;
2482
2483 IF (pbh_child_rec.operand_calculation_code = QP_PREQ_GRP.G_NEWPRICE_DISCOUNT) THEN
2484 l_sign := 1; -- new price discount/surcharge are the same
2485 END IF;
2486 IF l_debug = FND_API.G_TRUE THEN
2487 QP_PREQ_GRP.engine_debug('l sign: '|| l_sign);
2488
2489 END IF;
2490 -- 3943868: was passing p_list_line_id instead of pbh_child_rec.list_line_id
2491 Get_Satisfied_Range(pbh_child_rec.value_from, pbh_child_rec.value_to,
2492 l_total_value, pbh_child_rec.list_line_id, l_continuous_flag, 'N', l_satisfied_value);
2493 pbh_adjustments(i).l_satisfied_value := l_satisfied_value;
2494
2495 --If operator is lumpsum and l_satisfied_value is zero do not calculate
2496 IF l_satisfied_value = 0
2497 THEN
2498 l_adjustment_amount := NULL;
2499 ELSE
2500 Calculate_Adjustment(p_list_price,
2501 pbh_child_rec.operand_calculation_code,
2502 pbh_child_rec.operand * l_sign,
2503 pbh_adjustments(i).l_satisfied_value,
2504 --l_total_value,
2505 l_adjustment_amount,
2506 l_return_status,
2507 l_return_status_text);
2508 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
2509 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2510 END IF;
2511 END IF;
2512
2513 pbh_adjustments(i).l_related_list_line_id := pbh_child_rec.related_list_line_id;
2514 pbh_adjustments(i).l_related_line_detail_index := pbh_child_rec.related_line_detail_index;
2515 pbh_adjustments(i).l_adjustment_amount := l_adjustment_amount;
2516
2517 --IF (l_total_value > 0) THEN
2518 --l_total_value := l_total_value - l_satisfied_value; -- this is correct to be commented out
2519
2520 IF l_debug = FND_API.G_TRUE THEN
2521 QP_PREQ_GRP.engine_debug('Adjustment : (' || i || ') ' || l_adjustment_amount);
2522 QP_PREQ_GRP.engine_debug('Satisfied Value : (' || i || ') ' || l_satisfied_value);
2523 QP_PREQ_GRP.engine_debug('Total Value : (' || i || ') ' || l_total_value);
2524 END IF;
2525 --ELSE
2526 -- pbh_adjustments(i).l_satisfied_value := 0;
2527 --END IF;
2528
2529 END IF;
2530
2531 END LOOP;
2532
2533 CLOSE get_price_break_details;
2534
2535 IF (p_break_type = QP_PREQ_GRP.G_RANGE_BREAK) THEN
2536 FOR i IN 1 .. pbh_adjustments.COUNT
2537 LOOP
2538 l_range_adjustment := l_range_adjustment + nvl(pbh_adjustments(i).l_adjustment_amount * pbh_adjustments(i).l_satisfied_value, 0);
2539 END LOOP;
2540 x_adjustment_amount := l_range_adjustment / l_qualifier_value;
2541
2542 IF l_debug = FND_API.G_TRUE THEN
2543 QP_PREQ_GRP.engine_debug('Averaged Adjustment : ' || x_adjustment_amount);
2544
2545 END IF;
2546 END IF;
2547
2548 -- Update the adjustments/satisfied ranges info on line details
2549 IF (QP_PREQ_GRP.G_PUBLIC_API_CALL_FLAG = QP_PREQ_GRP.G_YES) THEN
2550 IF (pbh_adjustments.COUNT > 0) THEN
2551 IF l_debug = FND_API.G_TRUE THEN
2552 QP_PREQ_GRP.engine_debug('updating child pbh lines '
2553 || pbh_adjustments.COUNT);
2554 END IF;
2555 FOR i IN pbh_adjustments.FIRST .. pbh_adjustments.LAST LOOP
2556 IF l_debug = FND_API.G_TRUE THEN
2557 QP_PREQ_GRP.engine_debug('child dtl adj amt '
2558 || pbh_adjustments(i).l_adjustment_amount
2559 ||' qty '|| pbh_adjustments(i).l_satisfied_value
2560 ||' dtl index '|| pbh_adjustments(i).l_related_line_detail_index);
2561 END IF;
2562 UPDATE qp_npreq_ldets_tmp
2563 --changes for bug 2264566 update adjamt on child lines of manualpbh
2564 SET adjustment_amount = - (pbh_adjustments(i).l_adjustment_amount),
2565 --SET adjustment_amount = l_sign * pbh_adjustments(i).l_adjustment_amount,
2566 line_quantity = pbh_adjustments(i).l_satisfied_value
2567 WHERE line_detail_index = pbh_adjustments(i).l_related_line_detail_index
2568 AND pricing_status_code IN (QP_PREQ_GRP.G_STATUS_NEW,
2569 --changes for bug 2264566 update adjamt on child lines of manualpbh
2570 QP_PREQ_GRP.G_STATUS_UNCHANGED);
2571 END LOOP;
2575
2572 END IF;
2573 END IF;
2574
2576 EXCEPTION
2577 WHEN OTHERS THEN
2578 IF l_debug = FND_API.G_TRUE THEN
2579 QP_PREQ_GRP.ENGINE_DEBUG('Error in price break calculation: '|| SQLERRM);
2580 END IF;
2581 x_return_status := FND_API.G_RET_STS_ERROR;
2582 x_return_status_text := l_routine ||' '|| SQLERRM;
2583 END Price_Break_Calculation;
2584
2585 PROCEDURE GSA_Max_Discount_Check(p_adjusted_unit_price NUMBER,
2586 p_line_index NUMBER,
2587 p_pricing_date DATE,
2588 x_return_status OUT NOCOPY VARCHAR2,
2589 x_return_status_txt OUT NOCOPY VARCHAR2) AS
2590
2591 v_list_line_id NUMBER;
2592 v_operand NUMBER;
2593 v_routine_name CONSTANT VARCHAR2(240) := 'Routine:QP_Calculate_Price_PUB.GSA_Max_Discount_Check';
2594 v_source_system_code VARCHAR2(30);
2595
2596 GSA_VIOLATION EXCEPTION;
2597
2598 CURSOR get_source_system_code IS
2599 SELECT b.SOURCE_SYSTEM_CODE
2600 FROM qp_npreq_ldets_tmp a, QP_LIST_HEADERS_B b
2601 WHERE a.CREATED_FROM_LIST_HEADER_ID = b.LIST_HEADER_ID
2602 AND a.LINE_INDEX = p_line_index
2603 AND a.CREATED_FROM_LIST_LINE_TYPE = 'PLL'
2604 AND a.PRICING_STATUS_CODE = QP_PREQ_GRP.G_STATUS_NEW;
2605
2606 CURSOR get_list_details_cur(p_source_system_code VARCHAR2) IS
2607 SELECT /*+ ORDERED USE_NL(a b c d e) */ c.LIST_LINE_ID, c.OPERAND
2608 FROM
2609 qp_npreq_line_attrs_tmp a, QP_PRICING_ATTRIBUTES b, QP_LIST_LINES c, QP_LIST_HEADERS_B d, QP_QUALIFIERS e
2610 WHERE a.LINE_INDEX = p_line_index
2611 AND a.CONTEXT = b.PRODUCT_ATTRIBUTE_CONTEXT
2612 AND a.ATTRIBUTE = b.PRODUCT_ATTRIBUTE
2613 AND a.VALUE_FROM = b.PRODUCT_ATTR_VALUE
2614 AND b.PRICING_ATTRIBUTE_CONTEXT IS NULL
2615 AND b.LIST_LINE_ID = c.LIST_LINE_ID
2616 AND c.ARITHMETIC_OPERATOR = QP_PREQ_GRP.G_NEWPRICE_DISCOUNT
2617 AND c.LIST_HEADER_ID = d.LIST_HEADER_ID
2618 AND trunc(p_pricing_date) BETWEEN nvl(TRUNC(c.START_DATE_ACTIVE), trunc(p_pricing_date))
2619 AND nvl(TRUNC(c.END_DATE_ACTIVE), trunc(p_pricing_date))
2620 AND d.SOURCE_SYSTEM_CODE = p_source_system_code
2621 AND d.ACTIVE_FLAG = QP_PREQ_GRP.G_YES
2622 AND d.LIST_HEADER_ID = e.LIST_HEADER_ID
2623 AND e.QUALIFIER_CONTEXT = QP_PREQ_GRP.G_CUSTOMER_CONTEXT
2624 AND e.QUALIFIER_ATTRIBUTE = QP_PREQ_GRP.G_GSA_ATTRIBUTE
2625 AND e.QUALIFIER_ATTR_VALUE = QP_PREQ_GRP.G_YES
2626 ORDER BY 2;
2627
2628 BEGIN
2629 l_debug := QP_PREQ_GRP.G_DEBUG_ENGINE;
2630 x_return_status := FND_API.G_RET_STS_SUCCESS;
2631
2632 OPEN get_source_system_code;
2633 FETCH get_source_system_code INTO v_source_system_code;
2634 CLOSE get_source_system_code;
2635
2636 OPEN get_list_details_cur(v_source_system_code);
2637 FETCH get_list_details_cur INTO v_list_line_id, v_operand;
2638 CLOSE get_list_details_cur;
2639
2640 IF l_debug = FND_API.G_TRUE THEN
2641 QP_PREQ_GRP.engine_debug ('GSA List Line : ' || v_list_line_id);
2642 QP_PREQ_GRP.engine_debug ('GSA Price : ' || v_operand);
2643 QP_PREQ_GRP.engine_debug('GSA List Line : ' || v_list_line_id);
2644 QP_PREQ_GRP.engine_debug('GSA Price : ' || v_operand);
2645
2646 END IF;
2647 IF (v_operand IS NOT NULL)
2648 --changes made by spgopal for ASO bug 1898927
2649 AND (p_adjusted_unit_price <= v_operand)
2650 THEN
2651 RAISE GSA_VIOLATION;
2652 END IF;
2653
2654 EXCEPTION
2655 WHEN GSA_VIOLATION THEN
2656 IF l_debug = FND_API.G_TRUE THEN
2657 QP_PREQ_GRP.engine_debug(v_routine_name || ' GSA Violation. GSA Price: ' || v_operand || 'List Line: ' || v_list_line_id);
2658 END IF;
2659 x_return_status := QP_PREQ_GRP.G_STATUS_GSA_VIOLATION;
2660 x_return_status_txt := v_operand ;
2661 WHEN OTHERS THEN
2662 IF l_debug = FND_API.G_TRUE THEN
2663 QP_PREQ_GRP.engine_debug(v_routine_name || ' ' || SQLERRM);
2664 END IF;
2665 x_return_status := FND_API.G_RET_STS_ERROR;
2666 x_return_status_txt := v_routine_name || ' ' || SQLERRM;
2667 END GSA_Max_Discount_Check;
2668
2669 PROCEDURE Calculate_Price(p_request_line IN OUT NOCOPY l_request_line_rec,
2670 p_request_line_details IN OUT NOCOPY l_request_line_details_tbl,
2671 p_related_request_lines IN OUT NOCOPY l_related_request_lines_tbl,
2672 x_return_status OUT NOCOPY VARCHAR2,
2673 x_return_status_txt OUT NOCOPY VARCHAR2) AS
2674
2675 x_related_request_lines_tbl l_related_request_lines_tbl;
2676
2677 x_pbh_list_price NUMBER;
2678 x_list_price NUMBER;
2679 x_adjusted_amount NUMBER;
2680 x_percent_price NUMBER;
2681 x_extended_price NUMBER; -- block pricing
2682 x_discounted_price NUMBER;
2683 x_benefit_line_qty NUMBER;
2684
2685 --v_request_line_rec l_request_line_rec;
2686 --v_request_line_details_tbl l_request_line_details_tbl;
2687 --v_related_request_lines l_related_request_lines_tbl;
2688 v_list_line_id NUMBER;
2689 v_list_line_index NUMBER;
2690 v_ord_qty NUMBER;
2691 v_discounted_price NUMBER;
2692 v_old_pricing_sequence NUMBER;
2693 v_list_price NUMBER;
2694 v_total_adj_amt NUMBER;
2695 v_operand_calc_code VARCHAR2(30);
2696 v_operand_value NUMBER;
2697 v_related_item_price NUMBER := 0;
2698 v_index NUMBER := 0;
2702 v_bucketed_value NUMBER := NULL; -- IT bucket
2699 v_orig_list_price NUMBER;
2700 v_service_duration NUMBER;
2701 v_actual_line_qty NUMBER; -- begin 2388011, grp_lines_pbh,
2703 v_bucketed_qty NUMBER := NULL; -- IT bucket
2704 v_total_value NUMBER; -- end 2388011, LINEGROUP PBH
2705 x_ret_status VARCHAR2(30);
2706 x_ret_status_txt VARCHAR2(240);
2707
2708 v_routine_name CONSTANT VARCHAR2(240) := 'Routine:QP_Calculate_Price_PUB.Calculate_Price';
2709 --v_round_individual_adj_flag varchar2(3); --shu
2710 v_price_round_options VARCHAR2(30) := FND_PROFILE.VALUE('QP_SELLING_PRICE_ROUNDING_OPTIONS'); --shu, new rounding
2711
2712 BEGIN
2713 -- Get the records from the l_request_line_details_tbl in the order
2714 --of pricing sequence(this cannot be done
2715 -- using PL/SQL table. I am assuming that I would get the records in ordered sequence
2716
2717 l_debug := QP_PREQ_GRP.G_DEBUG_ENGINE;
2718 IF l_debug = FND_API.G_TRUE THEN
2719 QP_PREQ_GRP.engine_debug ('In Procedure Calculate_Price...'); -- shu, aso rounding, 2457629
2720 QP_PREQ_GRP.engine_debug ('selling price rounding options : ' || v_price_round_options); -- shu
2721 QP_PREQ_GRP.engine_debug ('Rounding Flag : ' || QP_PREQ_GRP.G_ROUNDING_FLAG); -- aso rounding, 2457629
2722 QP_PREQ_GRP.engine_debug ('Rounding Factor : ' || p_request_line.rounding_factor);
2723 END IF;
2724
2725 IF (p_request_line.rounding_flag = 'Y' AND p_request_line.rounding_factor IS NULL) THEN -- shu
2726 IF l_debug = FND_API.G_TRUE THEN
2727 QP_PREQ_GRP.engine_debug ('WARNING: rounding_flag is Y, but rounding_factor is NULL!!!');
2728 END IF;
2729 END IF;
2730
2731 -- For accum range break
2732 l_line_index := p_request_line.line_index;
2733
2734 -- Loop through the p_request_line_details
2735
2736 IF l_debug = FND_API.G_TRUE THEN
2737 QP_PREQ_GRP.engine_debug('The request line tables count is: ' || p_request_line_details.COUNT);
2738 END IF;
2739
2740 v_index := p_request_line_details.FIRST;
2741 WHILE (v_index IS NOT NULL )
2742 LOOP
2743
2744 IF l_debug = FND_API.G_TRUE THEN
2745 QP_PREQ_GRP.engine_debug('LOOPing through p_request_line_details........');
2746 QP_PREQ_GRP.engine_debug('The request line index is: ' || v_index);
2747 QP_PREQ_GRP.engine_debug('PBH Type123:' || p_request_line_details(v_index).CREATED_FROM_LIST_TYPE);
2748 END IF;
2749
2750 -- Store the ordered qty
2751 v_ord_qty := nvl(p_request_line_details(v_index).line_quantity, 0); -- 2388011_latest
2752
2753 -- begin 2388011
2754 -- Store the total group amount , applicable for LINEGROUP PBH Ravi Change
2755 --[julin/5158413] defaulting to -1 (instead of 0) to distinguish null qual value from 0 qual value
2756 --v_total_value := nvl(p_request_line_details(v_index).qualifier_value, - 1); -- 2388011_latest
2757 v_total_value := nvl(p_request_line_details(v_index).qualifier_value, 0); --6896139 undoing changes done as part of bug 5158413
2758
2759 IF l_debug = FND_API.G_TRUE THEN
2760 QP_PREQ_GRP.engine_debug('ldets.qualifier_value, (v_ord_qty) : ' || v_ord_qty);
2761 QP_PREQ_GRP.engine_debug('ldets.line_quqntity, (v_total_value) : ' || v_total_value);
2762 -- If bucketed net amount flag is set to 'Y'
2763 QP_PREQ_GRP.engine_debug('Bucketed Old PGS : ' || v_old_pricing_sequence);
2764 QP_PREQ_GRP.engine_debug('Bucketed New PGS : ' || p_request_line_details(v_index).PRICING_GROUP_SEQUENCE);
2765 END IF; -- END IF l_debug = FND_API.G_TRUE
2766
2767 IF (nvl(v_old_pricing_sequence, - 9999) <> p_request_line_details(v_index).PRICING_GROUP_SEQUENCE AND
2768 p_request_line_details(v_index).bucketed_flag = 'Y') THEN
2769 IF l_debug = FND_API.G_TRUE THEN
2770 QP_PREQ_GRP.engine_debug('Before Bucketed Group Amount : ' || v_bucketed_value);
2771 QP_PREQ_GRP.engine_debug('Before Bucketed Line Amount : ' || v_bucketed_qty);
2772 QP_PREQ_GRP.engine_debug('Bucket Adjustment Amount: ' || nvl(v_total_adj_amt, 0));
2773 END IF; -- END IF l_debug = FND_API.G_TRUE
2774
2775 IF (v_total_value > 0) THEN
2776 v_bucketed_value := v_total_value + nvl(v_total_adj_amt * v_actual_line_qty, 0); -- Net Group Amount
2777 v_bucketed_qty := NULL;
2778 ELSE -- Only needed when group amount is not passed
2779 v_bucketed_qty := v_ord_qty + nvl(v_total_adj_amt * v_actual_line_qty, 0); -- Net Item Amount
2780 v_bucketed_value := NULL;
2781 END IF; -- END IF (v_total_value > 0)
2782
2783 IF l_debug = FND_API.G_TRUE THEN
2784 QP_PREQ_GRP.engine_debug('Net Bucketed Group Amount : ' || v_bucketed_value);
2785 QP_PREQ_GRP.engine_debug('Net Bucketed Line Amount : ' || v_bucketed_qty);
2786 END IF; -- END IF l_debug = FND_API.G_TRUE
2787 END IF; -- END IF (nvl(v_old_pricing_sequence,-9999)
2788
2789 -- end fix 2388011, grp_lines_pbh, IT bucket
2790
2791 IF (v_ord_qty = 0) THEN
2792 v_ord_qty := nvl(p_request_line.qualifier_value, 0);
2793 END IF;
2794
2795 -- fix 2388011, grp_lines_pbh
2796 -- Actual quantity on the order line
2797 v_actual_line_qty := nvl(p_request_line.qualifier_value, 0);
2798
2799 -- Store the related item price
2800 v_related_item_price := p_request_line.RELATED_ITEM_PRICE;
2801 v_service_duration := nvl(p_request_line.SERVICE_DURATION, 1);
2802
2803 IF l_debug = FND_API.G_TRUE THEN
2804 QP_PREQ_GRP.engine_debug('Service Duration : ' || v_service_duration);
2805
2809 v_list_line_index := p_request_line_details(v_index).LINE_DETAIL_INDEX;
2806 END IF;
2807
2808 -- Store the list line id
2810
2811 -- Store the operand calc code for the PBH line
2812 v_operand_calc_code := p_request_line_details(v_index).operand_calculation_code;
2813
2814 -- Calculate List Price for Pricing Sequence 0 for a PBH line
2815 -- Process PBH for Getting List Price(if exists)
2816
2817 -- Null Bucket
2818 IF (p_request_line_details(v_index).PRICING_GROUP_SEQUENCE IS NULL) THEN
2819 v_orig_list_price := p_request_line.UNIT_PRICE;
2820 ELSE
2821 IF (v_list_price IS NULL) THEN
2822 IF l_debug = FND_API.G_TRUE THEN
2823 QP_PREQ_GRP.engine_debug ('V_List_Price is null');
2824 END IF;
2825 v_list_price := p_request_line.UNIT_PRICE;
2826
2827 IF nvl(QP_PREQ_GRP.G_ROUNDING_FLAG, 'Y') = QP_PREQ_GRP.G_YES AND -- shu, aso debug 2457629
2828 p_request_line.ROUNDING_FACTOR IS NOT NULL THEN
2829 v_list_price := ROUND(v_list_price, p_request_line.ROUNDING_FACTOR * ( - 1));
2830 p_request_line.UNIT_PRICE := v_list_price;
2831 ELSIF (QP_PREQ_GRP.G_ROUNDING_FLAG = G_CHAR_Q) THEN -- check v_selling_price_rounding_options profile
2832
2833 IF (v_price_round_options = G_ROUND_ADJ AND p_request_line.ROUNDING_FACTOR IS NOT NULL) THEN
2834 v_list_price := ROUND(v_list_price, p_request_line.ROUNDING_FACTOR * ( - 1));
2835 p_request_line.UNIT_PRICE := v_list_price;
2836 END IF;
2837 END IF; -- end IF nvl(QP_PREQ_GRP.G_ROUNDING_FLAG, 'Y')
2838 v_discounted_price := nvl(p_request_line.ADJUSTED_UNIT_PRICE, v_list_price);
2839
2840 END IF; -- END IF (v_list_price IS NULL)
2841 v_orig_list_price := v_list_price;
2842 END IF; -- END IF (p_request_line_details(v_index).PRICING_GROUP_SEQUENCE IS NULL)
2843
2844 IF (p_request_line_details(v_index).CREATED_FROM_LIST_LINE_TYPE =
2845 QP_PREQ_GRP.G_PRICE_BREAK_TYPE) THEN
2846 IF l_debug = FND_API.G_TRUE THEN
2847 QP_PREQ_GRP.engine_debug('PBH Type:' || p_request_line_details(v_index).CREATED_FROM_LIST_TYPE);
2848 QP_PREQ_GRP.engine_debug('PBH Unit Price:' || p_request_line.UNIT_PRICE);
2849 QP_PREQ_GRP.engine_debug('Old PGS:' || v_old_pricing_sequence);
2850 END IF;
2851
2852 -- 2388011, pbh_grp_amt
2853 Process_Price_Break(
2854 p_request_line_details(v_index).created_from_list_line_id, -- PBH list line ID, for accum range break
2855 v_list_line_index, -- PBH Line Id
2856 v_operand_calc_code, -- PBH Line arithmetic operator
2857 v_ord_qty, -- 2388011_new
2858 v_actual_line_qty, -- 2388011,
2859 p_request_line_details(v_index).CREATED_FROM_LIST_TYPE, -- Prc/Dis Break
2860 nvl(v_orig_list_price, p_request_line.UNIT_PRICE), -- Unit Price(for discount break)
2861 nvl(v_discounted_price, p_request_line.UNIT_PRICE),
2862 v_old_pricing_sequence,
2863 v_related_item_price, -- Related Item Price
2864 v_service_duration, -- Service Duration for service line pricing
2865 p_related_request_lines, -- Children of PBH Lines
2866 nvl(QP_PREQ_GRP.G_ROUNDING_FLAG, 'Y'), -- shu, aso debug 2457629
2867 ( - 1) * p_request_line.ROUNDING_FACTOR,
2868 nvl(v_bucketed_value, v_total_value), -- 2388011, PBH LINEGROUP Ravi Change
2869 x_pbh_list_price, -- List Price for the Request Line
2870 x_extended_price, -- block pricing
2871 x_adjusted_amount, -- Adjustment Amount for the PBH Line
2872 x_related_request_lines_tbl, -- Related Request Lines Detail Table
2873 x_ret_status,
2874 x_ret_status_txt);
2875 v_old_pricing_sequence := p_request_line_details(v_index).PRICING_GROUP_SEQUENCE;
2876
2877 IF(x_ret_status = FND_API.G_RET_STS_ERROR) THEN
2878 RAISE FND_API.G_EXC_ERROR;
2879 END IF;
2880
2881 IF (p_request_line_details(v_index).CREATED_FROM_LIST_TYPE IN
2882 (QP_PREQ_GRP.G_PRICE_LIST_HEADER, QP_PREQ_GRP.G_AGR_LIST_HEADER)) THEN
2883
2884 -- Update the list price on the p_request_line_details structure for the PBH line
2885 p_request_line.UNIT_PRICE := x_pbh_list_price;
2886 p_request_line.EXTENDED_PRICE := x_extended_price; -- block pricing
2887 p_request_line_details(v_index).ADJUSTMENT_AMOUNT := nvl(x_pbh_list_price, 0);
2888 p_request_line_details(v_index).LINE_QUANTITY := v_ord_qty;
2889 -- Init the price list values for input to the Calculate_Adjusted_Price function
2890 v_list_price := x_pbh_list_price;
2891 v_discounted_price := x_pbh_list_price;
2892 IF l_debug = FND_API.G_TRUE THEN
2893 QP_PREQ_GRP.engine_debug('Unit Price:' || x_pbh_list_price);
2894 QP_PREQ_GRP.engine_debug('Extended Price on the Line: ' || p_request_line.EXTENDED_PRICE);
2895 QP_PREQ_GRP.engine_debug('Related Request Lines Table Count:'|| x_related_request_lines_tbl.COUNT);
2896 END IF;
2897 ELSE
2898 -- Update the adjustment amount on the p_request_line_details
2899 p_request_line_details(v_index).ADJUSTMENT_AMOUNT := nvl(x_adjusted_amount, 0);
2903 -- automatic_flag = 'N'
2900 p_request_line_details(v_index).LINE_QUANTITY := v_ord_qty;
2901 -- Update the adjusted unit price
2902 -- Total Adjusted amount is not calculated on Freight Charges and manual price breaks which will have
2904
2905 IF (p_request_line_details(v_index).CREATED_FROM_LIST_LINE_TYPE <> QP_PREQ_GRP.G_FREIGHT_CHARGE AND
2906 p_request_line_details(v_index).AUTOMATIC_FLAG <> QP_PREQ_GRP.G_NO) THEN
2907 IF (nvl(p_request_line_details(v_index).ACCRUAL_FLAG, QP_PREQ_GRP.G_NO) <> QP_PREQ_GRP.G_YES) THEN
2908 v_total_adj_amt := nvl(v_total_adj_amt, 0) + nvl(x_adjusted_amount, 0);
2909 END IF;
2910 p_request_line.ADJUSTED_UNIT_PRICE := p_request_line.UNIT_PRICE + nvl(v_total_adj_amt, 0);
2911 END IF;
2912 v_discounted_price := p_request_line.ADJUSTED_UNIT_PRICE;
2913
2914 IF l_debug = FND_API.G_TRUE THEN
2915 QP_PREQ_GRP.engine_debug('Adjusted Amount:' || x_adjusted_amount);
2916 QP_PREQ_GRP.engine_debug('Unit Price:' || p_request_line.UNIT_PRICE );
2917 QP_PREQ_GRP.engine_debug('Adjusted Unit Price:' || v_discounted_price);
2918 END IF;
2919
2920 END IF; -- END IF (p_request_line_details(v_index).CREATED_FROM_LIST_TYPE...
2921
2922 -- Update the passed in p_related_request_lines with new structure, which has the
2923 -- LIST_QTY and LIST_PRICE
2924 -- and SELLING PRICE for each PBH Line
2925 p_related_request_lines := x_related_request_lines_tbl;
2926 END IF;
2927
2928 -- Calculate List Price for Pricing Sequence 0 for a PLL Line
2929 --IF (p_request_line_details(v_index).CREATED_FROM_LIST_TYPE in
2930 -- (QP_PREQ_GRP.G_PRICE_LIST_HEADER,QP_PREQ_GRP.G_AGR_LIST_HEADER)) THEN
2931 IF (p_request_line_details(v_index).CREATED_FROM_LIST_LINE_TYPE = QP_PREQ_GRP.G_PRICE_LIST_TYPE) THEN
2932 IF l_debug = FND_API.G_TRUE THEN
2933 QP_PREQ_GRP.engine_debug('I am in PLL');
2934 END IF;
2935 Calculate_List_Price_PVT(p_request_line_details(v_index).OPERAND_CALCULATION_CODE,
2936 p_request_line_details(v_index).OPERAND_VALUE,
2937 0, -- block pricing
2938 v_ord_qty,
2939 v_related_item_price,
2940 v_service_duration,
2941 --nvl(p_request_line.ROUNDING_FLAG,'Y'),
2942 nvl(QP_PREQ_GRP.G_ROUNDING_FLAG, 'Y'), -- shu, aso debug 2457629
2943 ( - 1) * p_request_line.ROUNDING_FACTOR,
2944 x_list_price,
2945 x_percent_price,
2946 x_extended_price, -- block pricing
2947 x_ret_status,
2948 x_ret_status_txt);
2949 IF(x_ret_status = FND_API.G_RET_STS_ERROR) THEN
2950 RAISE FND_API.G_EXC_ERROR;
2951 END IF;
2952
2953 p_request_line.UNIT_PRICE := x_list_price;
2954 p_request_line.PERCENT_PRICE := x_percent_price;
2955 p_request_line_details(v_index).ADJUSTMENT_AMOUNT := nvl(x_list_price, 0);
2956 p_request_line_details(v_index).LINE_QUANTITY := v_ord_qty;
2957
2958 -- Init the price list values for input to the Calculate_Adjusted_Price function
2959 v_list_price := x_list_price;
2960 v_discounted_price := x_list_price;
2961 IF l_debug = FND_API.G_TRUE THEN
2962 QP_PREQ_GRP.engine_debug('The List Price23 is: ' || v_list_price);
2963
2964 END IF;
2965 END IF;
2966
2967 -- Set the list price and discounted price if they are null
2968 -- This can be the case if pll lines are not passed
2969 -- nvl , if the caller wants the adjustments to be applied to an already
2970 -- discounted line
2971 IF (v_list_price IS NULL) THEN
2972 IF l_debug = FND_API.G_TRUE THEN
2973 QP_PREQ_GRP.engine_debug ('V_List_Price is null');
2974 END IF;
2975 v_list_price := p_request_line.UNIT_PRICE;
2976
2977 IF nvl(QP_PREQ_GRP.G_ROUNDING_FLAG, 'Y') = QP_PREQ_GRP.G_YES AND -- shu, aso debug 2457629
2978 p_request_line.ROUNDING_FACTOR IS NOT NULL THEN
2979 v_list_price := ROUND(p_request_line.UNIT_PRICE, p_request_line.ROUNDING_FACTOR * ( - 1));
2980 p_request_line.UNIT_PRICE := v_list_price;
2981 -- shu, aso debug 2457629
2982 ELSIF (QP_PREQ_GRP.G_ROUNDING_FLAG = G_CHAR_Q) THEN -- shu, check v_selling_price_rounding_options profile
2983 IF (v_price_round_options = G_ROUND_ADJ AND p_request_line.ROUNDING_FACTOR IS NOT NULL) THEN
2984 v_list_price := ROUND(p_request_line.UNIT_PRICE, p_request_line.ROUNDING_FACTOR * ( - 1));
2985 p_request_line.UNIT_PRICE := v_list_price;
2986 END IF;
2987
2988 END IF;
2989 v_discounted_price := nvl(p_request_line.ADJUSTED_UNIT_PRICE, v_list_price);
2990 END IF;
2991
2992 -- Calculate Adjusted Price
2993 IF (p_request_line_details(v_index).CREATED_FROM_LIST_LINE_TYPE
2994 IN (QP_PREQ_GRP.G_DISCOUNT, QP_PREQ_GRP.G_SURCHARGE, QP_PREQ_GRP.G_FREIGHT_CHARGE)) THEN
2995 IF l_debug = FND_API.G_TRUE THEN
2996 QP_PREQ_GRP.engine_debug('I am in DIS');
2997 QP_PREQ_GRP.engine_debug('The List Price is: ' || v_list_price);
2998 QP_PREQ_GRP.engine_debug('The Discounted Price is: ' || v_discounted_price);
2999 QP_PREQ_GRP.engine_debug('Old Pricing Seq: ' || v_old_pricing_sequence);
3003 QP_PREQ_GRP.engine_debug('List Line Type: ' || p_request_line_details(v_index).CREATED_FROM_LIST_LINE_TYPE);
3000 QP_PREQ_GRP.engine_debug('New Pricing Seq: ' || p_request_line_details(v_index).PRICING_GROUP_SEQUENCE);
3001 QP_PREQ_GRP.engine_debug('Operand Calc Code: ' || p_request_line_details(v_index).OPERAND_CALCULATION_CODE);
3002 QP_PREQ_GRP.engine_debug('Operand Calc Value: ' || p_request_line_details(v_index).OPERAND_VALUE);
3004
3005 END IF;
3006
3007 /* [julin/5529345] recurring logic moved to QP_PREQ_GRP.PRICE_REQUEST
3008 -- Recurring flag check
3009 IF (p_request_line_details(v_index).PRICE_BREAK_TYPE_CODE = QP_PREQ_GRP.G_RECURRING_BREAK AND
3010 QP_PREQ_GRP.G_LIMITS_CODE_EXECUTED = QP_PREQ_GRP.G_NO) THEN
3011 IF l_debug = FND_API.G_TRUE THEN
3012 QP_PREQ_GRP.engine_debug('In Recurring Routine --- #1');
3013 QP_PREQ_GRP.engine_debug('List Line Id: ' || p_request_line_details(v_index).CREATED_FROM_LIST_LINE_ID );
3014 QP_PREQ_GRP.engine_debug('List Line Index: ' || p_request_line.LINE_INDEX );
3015 END IF;
3016 -- Call Recurring Routine
3017 QP_Process_Other_Benefits_PVT.Calculate_Recurring_Quantity(
3018 p_request_line_details(v_index).CREATED_FROM_LIST_LINE_ID,
3019 p_request_line_details(v_index).CREATED_FROM_LIST_HEADER_ID,
3020 p_request_line.LINE_INDEX,
3021 NULL,
3022 x_benefit_line_qty,
3023 x_ret_status,
3024 x_ret_status_txt);
3025 IF l_debug = FND_API.G_TRUE THEN
3026 QP_PREQ_GRP.engine_debug('Return Status Text : ' || x_ret_status_txt);
3027
3028 END IF;
3029 IF(x_ret_status = FND_API.G_RET_STS_ERROR) THEN
3030 RAISE FND_API.G_EXC_ERROR;
3031 END IF;
3032 p_request_line_details(v_index).OPERAND_VALUE := x_benefit_line_qty;
3033
3034 IF l_debug = FND_API.G_TRUE THEN
3035 QP_PREQ_GRP.engine_debug('Limits Code Not Executed.Did call Recurring Qty Routine');
3036 QP_PREQ_GRP.engine_debug('After Recurring Routine Value --- #2 : ' || x_benefit_line_qty);
3037
3038 END IF;
3039 END IF;
3040
3041 IF (p_request_line_details(v_index).PRICE_BREAK_TYPE_CODE = QP_PREQ_GRP.G_RECURRING_BREAK AND
3042 QP_PREQ_GRP.G_LIMITS_CODE_EXECUTED = QP_PREQ_GRP.G_YES) THEN
3043 IF l_debug = FND_API.G_TRUE THEN
3044 QP_PREQ_GRP.engine_debug('Limits Code Executed.Do not calculate OPERAND value 2nd time.');
3045 QP_PREQ_GRP.engine_debug('List Line Id: ' || p_request_line_details(v_index).CREATED_FROM_LIST_LINE_ID );
3046 QP_PREQ_GRP.engine_debug('List Line Index: ' || p_request_line.LINE_INDEX );
3047 QP_PREQ_GRP.engine_debug('Recurring Value: ' || p_request_line_details(v_index).OPERAND_VALUE );
3048 END IF;
3049 END IF;
3050 */
3051
3052 IF (nvl(p_request_line_details(v_index).ACCRUAL_FLAG, QP_PREQ_GRP.G_NO) = QP_PREQ_GRP.G_YES) THEN
3053 IF l_debug = FND_API.G_TRUE THEN
3054 QP_PREQ_GRP.engine_debug('OPERAND_CALCULATION_CODE is ' || p_request_line_details(v_index).OPERAND_CALCULATION_CODE);
3055 QP_PREQ_GRP.engine_debug('v_ord_qty is ' || v_ord_qty);
3056 END IF;
3057 -- bug 4002891 - benefit_qty should be multiplied by ordered_qty for application_method other than LUMPSUM
3058 IF p_request_line_details(v_index).OPERAND_CALCULATION_CODE = QP_PREQ_GRP.G_LUMPSUM_DISCOUNT THEN
3059 p_request_line_details(v_index).BENEFIT_QTY :=
3060 p_request_line_details(v_index).OPERAND_VALUE *
3061 1 / nvl(p_request_line_details(v_index).ACCRUAL_CONVERSION_RATE, 1) *
3062 nvl(p_request_line_details(v_index).ESTIM_ACCRUAL_RATE, 100) / 100;
3063 ELSE
3064 p_request_line_details(v_index).BENEFIT_QTY :=
3065 (p_request_line_details(v_index).OPERAND_VALUE *
3066 1 / nvl(p_request_line_details(v_index).ACCRUAL_CONVERSION_RATE, 1) *
3067 nvl(p_request_line_details(v_index).ESTIM_ACCRUAL_RATE, 100) / 100) * v_ord_qty;
3068 END IF;
3069 END IF;
3070
3071 --4900095
3072 --calculate lumpsum qty for svc lines
3073 IF p_request_line_details(v_index).modifier_level_code
3074 IN (QP_PREQ_GRP.G_LINE_LEVEL
3075 , QP_PREQ_GRP.G_LINE_GROUP)
3076 AND p_request_line_details(v_index).operand_calculation_code
3077 = QP_PREQ_GRP.G_LUMPSUM_DISCOUNT THEN
3078 --G_Lumpsum_qty will be not null only for service items
3079 --when uom_quantity is passed as null,
3080 -- and contract_start/end_dates are passed
3081 --and for a line/linegroup lumpsum DIS/PBH/SUR/FREIGHT
3082 G_Lumpsum_qty := Get_lumpsum_qty(l_line_index,
3083 p_request_line_details(v_index).line_detail_index,
3084 p_request_line_details(v_index).modifier_level_code);
3085 IF l_debug = FND_API.G_TRUE THEN
3086 QP_PREQ_GRP.engine_debug('Lumpsum_qty '
3087 || G_Lumpsum_qty);
3088 END IF;
3089 END IF; --p_request_line_details(v_index).modifier_level_code
3090
3094 p_request_line_details(v_index).PRICING_GROUP_SEQUENCE),
3091 Calculate_Adjusted_Price(p_list_price => v_orig_list_price,
3092 p_discounted_price => v_discounted_price, -- Discounted Price
3093 p_old_pricing_sequence => nvl(v_old_pricing_sequence,
3095 p_new_pricing_sequence => p_request_line_details(v_index).PRICING_GROUP_SEQUENCE,
3096 p_operand_calc_code => p_request_line_details(v_index).OPERAND_CALCULATION_CODE,
3097 p_operand_value => p_request_line_details(v_index).OPERAND_VALUE,
3098 p_list_line_type => p_request_line_details(v_index).CREATED_FROM_LIST_LINE_TYPE,
3099 p_request_qty => v_ord_qty,
3100 p_accrual_flag => p_request_line_details(v_index).ACCRUAL_FLAG,
3101 --p_rounding_flag => nvl(p_request_line.ROUNDING_FLAG,'Y'),
3102 p_rounding_flag => nvl(QP_PREQ_GRP.G_ROUNDING_FLAG, 'Y'), -- shu, aso debug 2457629
3103 p_rounding_factor => ( - 1) * p_request_line.ROUNDING_FACTOR,
3104 p_orig_unit_price => p_request_line.UNIT_PRICE,
3105 x_discounted_price => x_discounted_price, -- Output Discounted Price
3106 x_adjusted_amount => x_adjusted_amount,
3107 x_list_price => x_list_price, -- Output List Price
3108 x_return_status => x_ret_status,
3109 x_return_status_txt => x_ret_status_txt);
3110
3111 IF(x_ret_status = FND_API.G_RET_STS_ERROR) THEN
3112 RAISE FND_API.G_EXC_ERROR;
3113 END IF;
3114
3115 v_old_pricing_sequence := p_request_line_details(v_index).PRICING_GROUP_SEQUENCE;
3116 v_discounted_price := x_discounted_price;
3117 v_list_price := x_List_price;
3118
3119 IF l_debug = FND_API.G_TRUE THEN
3120 QP_PREQ_GRP.engine_debug('Adjusted Amount: ' || x_adjusted_amount);
3121
3122 END IF;
3123 -- Update the adjusted amount on the request line
3124 p_request_line_details(v_index).ADJUSTMENT_AMOUNT := nvl(x_adjusted_amount, 0);
3125 p_request_line_details(v_index).LINE_QUANTITY := v_ord_qty;
3126
3127 -- Update the adjusted unit price(the adjusted_unit_price gets updated for
3128 -- every dis/sur line,which
3129 -- is same as updating once, because the adjusted_unit_price value gets overwritten
3130 -- Total Adjustment amount is not calculated for freight charges
3131
3132 IF (p_request_line_details(v_index).CREATED_FROM_LIST_TYPE <>
3133 QP_PREQ_GRP.G_CHARGES_HEADER) THEN
3134 IF (nvl(p_request_line_details(v_index).ACCRUAL_FLAG, QP_PREQ_GRP.G_NO) <> QP_PREQ_GRP.G_YES AND
3135 p_request_line_details(v_index).AUTOMATIC_FLAG <> QP_PREQ_GRP.G_NO) THEN
3136 v_total_adj_amt := nvl(v_total_adj_amt, 0) + nvl(x_adjusted_amount, 0);
3137 END IF;
3138 --[prarasto:Post Round]removed rounding of unit selling price, the unrounded value will be
3139 --sent from calculation engine and it will be rounded in QP_PREQ_GRP.Call_Calculation_Engine
3140 /*
3141
3142 -- DO UNIT SELLING PRICE ROUNDING HERE, shu
3143
3144 --IF (( nvl(p_request_line.rounding_flag, QP_PREQ_GRP.G_YES) = QP_PREQ_GRP.G_YES OR p_request_line.rounding_flag = G_CHAR_U) and p_request_line.rounding_factor IS NOT NULL) THEN -- shu
3145 IF (nvl(QP_PREQ_GRP.G_ROUNDING_FLAG,'Y')='Y' OR QP_PREQ_GRP.G_ROUNDING_FLAG = G_CHAR_U) and p_request_line.rounding_factor IS NOT NULL THEN-- aso rounding, 2457629, shu
3146 p_request_line.ADJUSTED_UNIT_PRICE := ROUND ((p_request_line.UNIT_PRICE + nvl(v_total_adj_amt,0)), (-1) * p_request_line.rounding_factor);
3147 ELSIF (QP_PREQ_GRP.G_ROUNDING_FLAG = G_CHAR_Q) THEN -- check qp_selling_price_rounding_options profile -- shu, new rounding
3148 IF ((v_price_round_options = G_ROUND_ADJ OR v_price_round_options = G_NO_ROUND_ADJ) AND p_request_line.rounding_factor IS NOT NULL) THEN -- do not round if profile is null
3149 p_request_line.ADJUSTED_UNIT_PRICE := ROUND ((p_request_line.UNIT_PRICE + nvl(v_total_adj_amt,0)), (-1) * p_request_line.rounding_factor);
3150 ELSE -- NO_ROUND
3151 p_request_line.ADJUSTED_UNIT_PRICE := p_request_line.UNIT_PRICE + nvl(v_total_adj_amt,0);
3152 END IF;
3153
3154 ELSE -- rounding_flag is 'N'
3155 p_request_line.ADJUSTED_UNIT_PRICE := p_request_line.UNIT_PRICE + nvl(v_total_adj_amt,0);
3156 END IF; -- end rounding
3157 -- end shu fix
3158 */
3159 p_request_line.ADJUSTED_UNIT_PRICE := p_request_line.UNIT_PRICE + nvl(v_total_adj_amt, 0);
3160 --[prarasto:Post Round] End post round fix
3161
3162 END IF;
3163
3164 IF l_debug = FND_API.G_TRUE THEN
3165 QP_PREQ_GRP.engine_debug('The Adjusted Amount is: ' || v_total_adj_amt);
3166 QP_PREQ_GRP.engine_debug('The Unit Price: ' || p_request_line.UNIT_PRICE );
3167 QP_PREQ_GRP.engine_debug('The Adjusted Price: ' || p_request_line.ADJUSTED_UNIT_PRICE );
3168
3169 END IF;
3170 END IF;
3171 EXIT WHEN v_index = p_request_line_details.LAST;
3172 v_index := p_request_line_details.NEXT(v_index);
3173 END LOOP;
3174
3175 -- If price list is not passed , round the unit price and adjusted unit price
3176 IF (v_list_price IS NULL) THEN
3177 IF l_debug = FND_API.G_TRUE THEN
3178 QP_PREQ_GRP.engine_debug ('V_List_Price is null');
3179 END IF;
3180 v_list_price := p_request_line.UNIT_PRICE ;
3181 IF l_debug = FND_API.G_TRUE THEN
3182 QP_PREQ_GRP.engine_debug ('List Price after rounding: ' || v_list_price);
3183 END IF;
3184
3185 -- shu new rounding
3186 IF (nvl(QP_PREQ_GRP.G_ROUNDING_FLAG, QP_PREQ_GRP.G_YES) = QP_PREQ_GRP.G_YES -- aso rounding, shu, 2457629
3187 AND p_request_line.rounding_factor IS NOT NULL) THEN -- shu new rounding
3188
3189 p_request_line.UNIT_PRICE := ROUND(v_list_price, p_request_line.ROUNDING_FACTOR * ( - 1));
3190 v_list_price := ROUND(v_list_price, p_request_line.ROUNDING_FACTOR * ( - 1));
3191
3192 ELSIF (QP_PREQ_GRP.G_ROUNDING_FLAG = G_CHAR_Q) THEN -- aso rounding, shu, 2457629
3193 -- no round if rounding_flag is Q bug QP profile is null
3194 IF v_price_round_options = G_ROUND_ADJ AND p_request_line.rounding_factor IS NOT NULL THEN -- bug 2415571
3195 p_request_line.UNIT_PRICE := ROUND(v_list_price, p_request_line.ROUNDING_FACTOR * ( - 1));
3196 v_list_price := ROUND(v_list_price, p_request_line.ROUNDING_FACTOR * ( - 1));
3197 END IF;
3198
3199 END IF;
3200
3201 IF l_debug = FND_API.G_TRUE THEN
3202 QP_PREQ_GRP.engine_debug ('List Price after rounding1: ' || p_request_line.UNIT_PRICE);
3203 QP_PREQ_GRP.engine_debug ('List Price after rounding2: ' || v_list_price);
3204 END IF;
3205 v_discounted_price := nvl(p_request_line.ADJUSTED_UNIT_PRICE, v_list_price);
3206 END IF;
3207
3211 QP_PREQ_GRP.G_GSA_DUP_CHECK_FLAG = QP_PREQ_GRP.G_YES ) THEN
3208 -- Call GSA Check only for non-gsa customers(where GSA_QUALIFIER_FLAG IS NULL)
3209 IF (p_request_line.GSA_QUALIFIER_FLAG IS NULL AND nvl(QP_PREQ_GRP.G_GSA_CHECK_FLAG, 'Y') = QP_PREQ_GRP.G_YES AND
3210 p_request_line.GSA_ENABLED_FLAG = QP_PREQ_GRP.G_YES AND
3212 GSA_Max_Discount_Check(p_request_line.ADJUSTED_UNIT_PRICE,
3213 p_request_line.LINE_INDEX,
3214 p_request_line.PRICING_EFFECTIVE_DATE,
3215 x_ret_status,
3216 x_ret_status_txt);
3217 IF(x_ret_status = QP_PREQ_GRP.G_STATUS_GSA_VIOLATION) THEN
3218 x_return_status := QP_PREQ_GRP.G_STATUS_GSA_VIOLATION;
3219 x_return_status_txt := x_ret_status_txt;
3220 END IF;
3221 END IF;
3222 IF l_debug = FND_API.G_TRUE THEN
3223 QP_PREQ_GRP.engine_debug ('Before Update List Price after rounding1: ' || p_request_line.UNIT_PRICE);
3224
3225 END IF;
3226 -- Populate the Output records
3227 --p_request_line := p_request_line;
3228 --p_request_line_details := p_request_line_details;
3229 --p_related_request_lines := p_related_request_lines;
3230 EXCEPTION
3231 WHEN FND_API.G_EXC_ERROR THEN
3232 IF l_debug = FND_API.G_TRUE THEN
3233 QP_PREQ_GRP.engine_debug(v_routine_name || ' ' || x_ret_status_txt);
3234 END IF;
3235 x_return_status := FND_API.G_RET_STS_ERROR;
3236 x_return_status_txt := v_routine_name || ' ' || x_ret_status_txt;
3237 WHEN OTHERS THEN
3238 IF l_debug = FND_API.G_TRUE THEN
3239 QP_PREQ_GRP.engine_debug(v_routine_name || ' ' || SQLERRM);
3240 END IF;
3241 x_return_status := FND_API.G_RET_STS_ERROR;
3242 x_return_status_txt := v_routine_name || ' ' || SQLERRM;
3243
3244 END Calculate_Price;
3245
3246 END QP_Calculate_Price_PUB;