[Home] [Help]
PACKAGE BODY: APPS.QP_CLEANUP_ADJUSTMENTS_PVT
Source
1 PACKAGE BODY QP_CLEANUP_ADJUSTMENTS_PVT AS
2 /* $Header: QPXVCLNB.pls 120.9.12010000.2 2008/08/12 06:28:41 smuhamme ship $ */
3
4 G_CALC_INSERT VARCHAR2(30) := 'INSERTED FOR CALCULATION';
5 --to store the line_detail_index with which the relationships were inserted
6 G_PBH_LINE_DTL_INDEX QP_PREQ_GRP.NUMBER_TYPE;
7 G_PBH_LINE_INDEX QP_PREQ_GRP.NUMBER_TYPE;
8 G_PBH_PRICE_ADJ_ID QP_PREQ_GRP.NUMBER_TYPE;
9 G_PBH_PLSQL_IND QP_PREQ_GRP.NUMBER_TYPE;
10 G_MAX_DTL_INDEX NUMBER := 0;
11 G_ORD_LVL_LDET_INDEX QP_PREQ_GRP.PLS_INTEGER_TYPE; -- 3031108
12
13 l_debug VARCHAR2(3);
14 --to populate the price_adjustment_id for modifiers applied by the
15 --pricing engine. The price_adjustment_id is queried from the
16 --sequence OE_PRICE_ADJUSTMENTS_S
17 PROCEDURE Populate_Price_Adj_ID(x_return_status OUT NOCOPY VARCHAR2,
18 x_return_status_text OUT NOCOPY VARCHAR2) IS
19
20 BEGIN
21 --ADDED BY YANGLI FOR JAVA ENGINE PUB 3086881
22 IF QP_JAVA_ENGINE_UTIL_PUB.Java_Engine_Running = 'N' THEN
23 IF l_debug = FND_API.G_TRUE THEN
24 QP_PREQ_GRP.ENGINE_DEBUG('Populate_Price_Adj_ID: Java Engine not Installed ----------');
25 END IF;
26 --ADDED BY YANGLI FOR JAVA ENGINE PUB 3086881
27 Update qp_npreq_ldets_tmp set price_adjustment_id =
28 OE_PRICE_ADJUSTMENTS_S.NEXTVAL
29 where pricing_status_code = QP_PREQ_PUB.G_STATUS_NEW
30 and process_code = QP_PREQ_PUB.G_STATUS_NEW
31 and (automatic_flag = QP_PREQ_PUB.G_YES
32 or created_from_list_line_type = QP_PREQ_PUB.G_FREIGHT_CHARGE)
33 and nvl(created_from_list_type_code,'NULL') not in
34 (QP_PREQ_PUB.G_PRICE_LIST_HEADER, QP_PREQ_PUB.G_AGR_LIST_HEADER);
35 --ADDED BY YANGLI FOR JAVA ENGINE PUB 3086881
36 ELSE
37 IF l_debug = FND_API.G_TRUE THEN
38 QP_PREQ_GRP.ENGINE_DEBUG('Populate_Price_Adj_ID: Java Engine is Installed ----------');
39 END IF;
40 IF (QP_UTIL_PUB.HVOP_Pricing_ON = 'Y') THEN
41 Update qp_int_ldets set price_adjustment_id =
42 OE_PRICE_ADJUSTMENTS_S.NEXTVAL
43 where pricing_status_code = QP_PREQ_PUB.G_STATUS_NEW
44 and process_code = QP_PREQ_PUB.G_STATUS_NEW
45 and (applied_flag = QP_PREQ_PUB.G_YES
46 or automatic_flag = QP_PREQ_PUB.G_YES)
47 -- or created_from_list_line_type = QP_PREQ_PUB.G_FREIGHT_CHARGE)
48 and nvl(created_from_list_type_code,'NULL') not in
49 (QP_PREQ_PUB.G_PRICE_LIST_HEADER, QP_PREQ_PUB.G_AGR_LIST_HEADER);
50
51 ELSE
52
53 Update qp_int_ldets set price_adjustment_id =
54 OE_PRICE_ADJUSTMENTS_S.NEXTVAL
55 where pricing_status_code = QP_PREQ_PUB.G_STATUS_NEW
56 and process_code = QP_PREQ_PUB.G_STATUS_NEW
57 and (automatic_flag = QP_PREQ_PUB.G_YES)
58 -- or created_from_list_line_type = QP_PREQ_PUB.G_FREIGHT_CHARGE)
59 and nvl(created_from_list_type_code,'NULL') not in
60 (QP_PREQ_PUB.G_PRICE_LIST_HEADER, QP_PREQ_PUB.G_AGR_LIST_HEADER);
61 END IF;
62 END IF;
63 --ADDED BY YANGLI FOR JAVA ENGINE PUB 3086881
64 x_return_status := FND_API.G_RET_STS_SUCCESS;
65 Exception
66 When OTHERS Then
67 x_return_status := FND_API.G_RET_STS_ERROR;
68 x_return_status_text := 'QP_CLEANUP_ADJUSTMENTS_PVT.Populate_Price_Adj_ID '||SQLERRM;
69 END Populate_Price_Adj_ID;
70
71 --This is used on PUB to get the sum of operand to see if the sum of
72 --the % on order level adjustments have changed during a
73 --changed lines request call to see if new order level adjustments
74 --are added or if existing ones have changed
75 FUNCTION get_sum_operand(p_header_id IN NUMBER) RETURN NUMBER IS
76 l_adj_sum_operand number;
77 BEGIN
78
79 l_debug := QP_PREQ_GRP.G_DEBUG_ENGINE;
80 select sum(operand) into l_adj_sum_operand
81 from oe_price_adjustments adj
82 where adj.header_id = p_header_id
83 and adj.line_id is null
84 and adj.modifier_level_code = QP_PREQ_PUB.G_ORDER_LEVEL
85 and adj.automatic_flag = QP_PREQ_PUB.G_YES
86 and applied_flag = QP_PREQ_PUB.G_YES;
87
88 IF l_adj_sum_operand is null
89 THEN
90 l_adj_sum_operand := 0;
91 END IF;
92 IF l_debug = FND_API.G_TRUE THEN
93 QP_PREQ_GRP.engine_debug(' Sum of adj operand '||l_adj_sum_operand);
94 END IF;
95 RETURN l_adj_sum_operand;
96
97 EXCEPTION
98 When NO_DATA_FOUND Then
99 l_adj_sum_operand := 0;
100 IF l_debug = FND_API.G_TRUE THEN
101 QP_PREQ_GRP.engine_debug(' Sum of adj operand '||l_adj_sum_operand);
102 END IF;
103 RETURN l_adj_sum_operand;
104 When OTHERS Then
105 l_adj_sum_operand := FND_API.G_MISS_NUM;
106 IF l_debug = FND_API.G_TRUE THEN
107 QP_PREQ_GRP.engine_debug(' Exception adj operand '||SQLERRM);
108 QP_PREQ_GRP.engine_debug(' Sum of adj operand '||l_adj_sum_operand);
109 END IF;
110 RETURN l_adj_sum_operand;
111 END get_sum_operand;
112
113 PROCEDURE cleanup_adjustments(p_view_code IN VARCHAR2,
114 p_request_type_code IN VARCHAR2,
115 p_cleanup_flag IN VARCHAR2,
116 x_return_status OUT NOCOPY VARCHAR2,
117 x_return_status_text OUT NOCOPY VARCHAR2) IS
118
119 --the same cursor for unchanged as well
120 --[julin/4865213] merged oe_price_adjustments unions, commented qp_npreq_ldets_tmp
121 CURSOR l_update_cur IS
122 SELECT /*+ ORDERED USE_NL(adj ldet ql) dynamic_sampling(1) index(LDET QP_PREQ_LDETS_TMP_N1)*/ -- Bug No: 6753550
123 ldet.line_index line_index
124 , ldet.line_detail_index line_detail_index
125 -- Begin Bug No: 6753550
126 --, ldet.list_line_id created_from_list_line_id
127 ,ldet.CREATED_FROM_LIST_LINE_ID
128 -- End Bug No: 6753550
129 , ldet.process_code process_code
130 , ldet.price_break_type_code price_break_type_code
131 , ldet.pricing_group_sequence pricing_group_sequence
132 , ldet.operand_calculation_code operand_calculation_code
133 , ldet.operand_value operand_value
134 , ldet.adjustment_amount adjustment_amount
135 -- Begin Bug No: 6753550
136 --, ldet.substitution_attribute substitution_type_code
137 ,ql_det.substitution_attribute substitution_type_code
138 -- End Bug No: 6753550
139 , ldet.substitution_value_to substitution_value_to
140 , ldet.pricing_phase_id pricing_phase_id
141 , ldet.applied_flag applied_flag
142 , ldet.automatic_flag automatic_flag
143 -- Begin Bug No: 6753550
144 --, ldet.override_flag override_flag
145 , ql_det.override_flag override_flag
146 -- End Bug No: 6753550
147 , ldet.benefit_qty benefit_qty
148 -- Begin Bug No: 6753550
149 --, ldet.benefit_uom_code benefit_uom_code
150 , ql_det.benefit_uom_code benefit_uom_code
151 --, ldet.accrual_flag accrual_flag
152 , ql_det.accrual_flag accrual_flag
153 --, ldet.accrual_conversion_rate accrual_conversion_rate
154 , ql_det.accrual_conversion_rate accrual_conversion_rate
155 -- End Bug No: 6753550
156 , ldet.charge_type_code charge_type_code
157 , ldet.charge_subtype_code charge_subtype_code
158 , ldet.line_quantity line_quantity
159 , adj.automatic_flag adj_automatic_flag
160 , adj.line_id adj_line_id
161 -- , adj.header_id adj_header_id
162 -- , adj.list_line_id adj_list_line_id
163 , adj.modified_from adj_modified_from
164 , adj.modified_to adj_modified_to
165 , adj.update_allowed adj_update_allowed
166 , adj.updated_flag adj_updated_flag
167 , adj.applied_flag adj_applied_flag
168 , adj.pricing_phase_id adj_pricing_phase_id
169 , adj.charge_type_code adj_charge_type_code
170 , adj.charge_subtype_code adj_charge_subtype_code
171 , adj.range_break_quantity adj_range_break_quantity
172 , adj.accrual_conversion_rate adj_accrual_conv_rate
173 , adj.pricing_group_sequence adj_pricing_group_seq
174 , adj.accrual_flag adj_accrual_flag
175 , adj.benefit_qty adj_benefit_qty
176 , adj.benefit_uom_code adj_benefit_uom_code
177 , adj.expiration_date adj_expiration_date
178 , adj.rebate_transaction_type_code adj_rebate_txn_type_code
179 , adj.price_break_type_code adj_price_break_type_code
180 , adj.substitution_attribute adj_substitution_attribute
181 , adj.proration_type_code adj_proration_type_code
182 , adj.include_on_returns_flag adj_include_on_returns
183 , nvl(adj.operand_per_pqty, adj.operand) adj_operand
184 , adj.adjusted_amount_per_pqty adj_adjusted_amount
185 , adj.arithmetic_operator adj_arithmetic_operator
186 , ql.expiration_date
187 , ql.proration_type_code
188 , ql.include_on_returns_flag
189 , ql.rebate_transaction_type_code
190 , ldet.pricing_status_text
191 , adj.price_adjustment_id
192 , ldet.order_qty_adj_amt ord_qty_adjamt
193 , adj.adjusted_amount adj_ord_qty_adjamt
194 , ldet.order_qty_operand ord_qty_operand
195 , adj.operand adj_ord_qty_operand
196 FROM qp_npreq_lines_tmp line
197 , oe_price_adjustments adj
198 -- Begin Bug No: 6753550
199 --,qp_ldets_v ldet
200 ,qp_npreq_ldets_tmp ldet
201 ,qp_list_lines ql_det
202 -- End Bug No: 6753550
203 , qp_list_lines ql
204 -- WHERE p_request_type_code = 'ONT'
205 --bug 3085453 handle pricing availability UI
206 -- they pass reqtype ONT and insert adj into ldets
207 WHERE QP_PREQ_PUB.G_CHECK_CUST_VIEW_FLAG = QP_PREQ_PUB.G_YES
208 and line.line_index = ldet.line_index
209 and (line.price_flag in (QP_PREQ_PUB.G_YES, QP_PREQ_PUB.G_PHASE)
210 OR nvl(line.processed_code,'0') = QP_PREQ_PUB.G_BY_ENGINE)
211 and line.pricing_status_code in (QP_PREQ_PUB.G_STATUS_UPDATED,
212 QP_PREQ_PUB.G_STATUS_UNCHANGED,
213 QP_PREQ_PUB.G_STATUS_GSA_VIOLATION)
214 and ldet.pricing_status_code IN (QP_PREQ_PUB.G_STATUS_NEW
215 ,QP_PREQ_PUB.G_STATUS_UPDATED)
216 and line.process_status in (QP_PREQ_PUB.G_STATUS_NEW,
217 QP_PREQ_PUB.G_STATUS_UPDATED,
218 QP_PREQ_PUB.G_STATUS_UNCHANGED,
219 'NEW'||QP_PREQ_PUB.G_STATUS_UNCHANGED,
220 --fix for bug 2823886 to do cleanup and calc for old fg line
221 'OLD'||QP_PREQ_PUB.G_STATUS_UNCHANGED)
222 and ((line.line_type_code = QP_PREQ_PUB.G_LINE_LEVEL
223 and line.line_id = adj.line_id)
224 OR
225 (line.line_type_code = QP_PREQ_PUB.G_ORDER_LEVEL
226 and line.line_id = adj.header_id))
227 -- Begin Bug No: 6753550
228 --and ldet.list_line_id = to_number(adj.list_line_id) -- bug 6023524
229 --and ldet.list_line_id = ql.list_line_id;
230 and ldet.CREATED_FROM_LIST_LINE_ID = to_number(adj.list_line_id)
231 and ldet.CREATED_FROM_LIST_LINE_ID = ql.list_line_id
232 and ldet.CREATED_FROM_LIST_LINE_ID = ql_det.LIST_LINE_ID
233 and ldet.PRICING_STATUS_CODE = 'N'
234 and ldet.REQUEST_ID = nvl ( SYS_CONTEXT ( 'QP_CONTEXT' , 'REQUEST_ID' ) , 1 );
235 -- End Bug No: 6753550
236 /*
237 UNION
238 SELECT /*+ ORDERED USE_NL(adj ldet ql)/
239 ldet.line_index line_index
240 , ldet.line_detail_index line_detail_index
241 , ldet.list_line_id created_from_list_line_id
242 , ldet.process_code process_code
243 , ldet.price_break_type_code price_break_type_code
244 , ldet.pricing_group_sequence pricing_group_sequence
245 , ldet.operand_calculation_code operand_calculation_code
246 , ldet.operand_value operand_value
247 , ldet.adjustment_amount adjustment_amount
248 , ldet.substitution_attribute substitution_type_code
249 , ldet.substitution_value_to substitution_value_to
250 , ldet.pricing_phase_id pricing_phase_id
251 , ldet.applied_flag applied_flag
252 , ldet.automatic_flag automatic_flag
253 , ldet.override_flag override_flag
254 , ldet.benefit_qty benefit_qty
255 , ldet.benefit_uom_code benefit_uom_code
256 , ldet.accrual_flag accrual_flag
257 , ldet.accrual_conversion_rate accrual_conversion_rate
258 , ldet.charge_type_code charge_type_code
259 , ldet.charge_subtype_code charge_subtype_code
260 , ldet.line_quantity line_quantity
261 , adj.automatic_flag adj_automatic_flag
262 , adj.line_id adj_line_id
263 -- , adj.header_id adj_header_id
264 -- , adj.list_line_id adj_list_line_id
265 , adj.modified_from adj_modified_from
266 , adj.modified_to adj_modified_to
267 , adj.update_allowed adj_update_allowed
268 , adj.updated_flag adj_updated_flag
269 , adj.applied_flag adj_applied_flag
270 , adj.pricing_phase_id adj_pricing_phase_id
271 , adj.charge_type_code adj_charge_type_code
272 , adj.charge_subtype_code adj_charge_subtype_code
273 , adj.range_break_quantity adj_range_break_quantity
274 , adj.accrual_conversion_rate adj_accrual_conv_rate
275 , adj.pricing_group_sequence adj_pricing_group_seq
276 , adj.accrual_flag adj_accrual_flag
277 , adj.benefit_qty adj_benefit_qty
278 , adj.benefit_uom_code adj_benefit_uom_code
279 , adj.expiration_date adj_expiration_date
280 , adj.rebate_transaction_type_code adj_rebate_txn_type_code
281 , adj.price_break_type_code adj_price_break_type_code
282 , adj.substitution_attribute adj_substitution_attribute
283 , adj.proration_type_code adj_proration_type_code
284 , adj.include_on_returns_flag adj_include_on_returns
285 , nvl(adj.operand_per_pqty, adj.operand) adj_operand
286 , adj.adjusted_amount_per_pqty adj_adjusted_amount
287 , adj.arithmetic_operator adj_arithmetic_operator
288 , ql.expiration_date
289 , ql.proration_type_code
290 , ql.include_on_returns_flag
291 , ql.rebate_transaction_type_code
292 , ldet.pricing_status_text
293 , adj.price_adjustment_id
294 , ldet.order_qty_adj_amt ord_qty_adjamt
295 , adj.adjusted_amount adj_ord_qty_adjamt
296 , ldet.order_qty_operand ord_qty_operand
297 , adj.operand adj_ord_qty_operand
298 FROM qp_npreq_lines_tmp line
299 , oe_price_adjustments adj
300 ,qp_ldets_v ldet
301 , qp_list_lines ql
302 -- WHERE p_request_type_code = 'ONT'
303 --bug 3085453 handle pricing availability UI
304 -- they pass reqtype ONT and insert adj into ldets
305 WHERE QP_PREQ_PUB.G_CHECK_CUST_VIEW_FLAG = QP_PREQ_PUB.G_YES
306 and line.line_index = ldet.line_index
307 and (line.price_flag in (QP_PREQ_PUB.G_YES, QP_PREQ_PUB.G_PHASE)
308 OR nvl(line.processed_code,'0') = QP_PREQ_PUB.G_BY_ENGINE)
309 and line.pricing_status_code in (QP_PREQ_PUB.G_STATUS_UPDATED,
310 QP_PREQ_PUB.G_STATUS_UNCHANGED,
311 QP_PREQ_PUB.G_STATUS_GSA_VIOLATION)
312 and ldet.pricing_status_code IN (QP_PREQ_PUB.G_STATUS_NEW
313 ,QP_PREQ_PUB.G_STATUS_UPDATED)
314 and line.process_status in (QP_PREQ_PUB.G_STATUS_NEW,
315 QP_PREQ_PUB.G_STATUS_UPDATED,
316 QP_PREQ_PUB.G_STATUS_UNCHANGED,
317 'NEW'||QP_PREQ_PUB.G_STATUS_UNCHANGED,
318 --fix for bug 2823886 to do cleanup and calc for old fg line
319 'OLD'||QP_PREQ_PUB.G_STATUS_UNCHANGED)
320 and (line.line_type_code = QP_PREQ_PUB.G_ORDER_LEVEL
321 and line.line_id = adj.header_id)
322 and ldet.list_line_id = adj.list_line_id
323 and ldet.list_line_id = ql.list_line_id
324 UNION
325 SELECT ldet.line_index line_index
326 , ldet.line_detail_index line_detail_index
327 , ldet.list_line_id created_from_list_line_id
328 , ldet.process_code process_code
329 , ldet.price_break_type_code price_break_type_code
330 , ldet.pricing_group_sequence pricing_group_sequence
331 , ldet.operand_calculation_code operand_calculation_code
332 , ldet.operand_value operand_value
333 , ldet.adjustment_amount adjustment_amount
334 , ldet.substitution_attribute substitution_type_code
335 , ldet.substitution_value_to substitution_value_to
336 , ldet.pricing_phase_id pricing_phase_id
337 , ldet.applied_flag applied_flag
338 , ldet.automatic_flag automatic_flag
339 , ldet.override_flag override_flag
340 , ldet.benefit_qty benefit_qty
341 , ldet.benefit_uom_code benefit_uom_code
342 , ldet.accrual_flag accrual_flag
343 , ldet.accrual_conversion_rate accrual_conversion_rate
344 , ldet.charge_type_code charge_type_code
345 , ldet.charge_subtype_code charge_subtype_code
346 , ldet.line_quantity line_quantity
347 , adj.automatic_flag adj_automatic_flag
348 , adj.created_from_list_line_id adj_list_line_id
352 , adj.updated_flag adj_updated_flag
349 , adj.substitution_value_from adj_modified_from
350 , adj.substitution_value_to adj_modified_to
351 , adj.override_flag adj_update_allowed
353 , adj.applied_flag adj_applied_flag
354 , adj.pricing_phase_id adj_pricing_phase_id
355 , adj.charge_type_code adj_charge_type_code
356 , adj.charge_subtype_code adj_charge_subtype_code
357 , adj.line_quantity adj_range_break_quantity
358 , adj.accrual_conversion_rate adj_accrual_conv_rate
359 , adj.pricing_group_sequence adj_pricing_group_seq
360 , adj.accrual_flag adj_accrual_flag
361 , adj.benefit_qty adj_benefit_qty
362 , adj.benefit_uom_code adj_benefit_uom_code
363 , ql.expiration_date adj_expiration_date
364 , ql.rebate_transaction_type_code adj_rebate_txn_type_code
365 , adj.price_break_type_code adj_price_break_type_code
366 , adj.substitution_type_code adj_substitution_attribute
367 , ql.proration_type_code adj_proration_type_code
368 , ql.include_on_returns_flag adj_include_on_returns
369 , adj.operand_value adj_operand
370 , adj.adjustment_amount adj_adjusted_amount
371 , adj.operand_calculation_code adj_arithmetic_operator
372 , ql.expiration_date
373 , ql.proration_type_code
374 , ql.include_on_returns_flag
375 , ql.rebate_transaction_type_code
376 , ldet.pricing_status_text
377 , adj.price_adjustment_id
378 , ldet.order_qty_adj_amt ord_qty_adjamt
379 , adj.order_qty_adj_amt adj_ord_qty_adjamt
380 , ldet.order_qty_operand ord_qty_operand
381 , adj.order_qty_operand adj_ord_qty_operand
382 FROM qp_npreq_lines_tmp line
383 , qp_npreq_ldets_tmp adj
384 ,qp_ldets_v ldet
385 , qp_list_lines ql
386 -- WHERE p_request_type_code <> 'ONT'
387 --bug 3085453 handle pricing availability UI
388 -- they pass reqtype ONT and insert adj into ldets
389 WHERE QP_PREQ_PUB.G_CHECK_CUST_VIEW_FLAG <> QP_PREQ_PUB.G_YES
390 and line.line_index = ldet.line_index
391 and (line.price_flag in (QP_PREQ_PUB.G_YES, QP_PREQ_PUB.G_PHASE)
392 OR nvl(line.processed_code,'0') = QP_PREQ_PUB.G_BY_ENGINE)
393 and line.pricing_status_code in (QP_PREQ_PUB.G_STATUS_UPDATED,
394 QP_PREQ_PUB.G_STATUS_UNCHANGED,
395 QP_PREQ_PUB.G_STATUS_GSA_VIOLATION)
396 and line.process_status in (QP_PREQ_PUB.G_STATUS_NEW,
397 QP_PREQ_PUB.G_STATUS_UPDATED,
398 QP_PREQ_PUB.G_STATUS_UNCHANGED,
399 'NEW'||QP_PREQ_PUB.G_STATUS_UNCHANGED,
400 --fix for bug 2823886 to do cleanup and calc for old fg line
401 'OLD'||QP_PREQ_PUB.G_STATUS_UNCHANGED)
402 and ldet.pricing_status_code IN (QP_PREQ_PUB.G_STATUS_NEW
403 ,QP_PREQ_PUB.G_STATUS_UPDATED)
404 and adj.line_index = line.line_index
405 and adj.pricing_status_code = QP_PREQ_PUB.G_STATUS_UNCHANGED
406 and ldet.list_line_id = adj.created_from_list_line_id
407 and ldet.list_line_id = ql.list_line_id;
408 */
409
410 --ADDED BY YANGLI FOR JAVA ENGINE PUB 3086881
411 --[julin/4865213] merged oe_price_adjustments unions, commented qp_npreq_ldets_tmp
412 CURSOR l_update_int_cur IS
413 SELECT /*+ ORDERED USE_NL(adj ldet ql)*/
414 ldet.line_index line_index
415 , ldet.line_detail_index line_detail_index
416 , ldet.list_line_id created_from_list_line_id
417 , ldet.process_code process_code
418 , ldet.price_break_type_code price_break_type_code
419 , ldet.pricing_group_sequence pricing_group_sequence
420 , ldet.operand_calculation_code operand_calculation_code
421 , ldet.operand_value operand_value
422 , ldet.adjustment_amount adjustment_amount
423 , ldet.substitution_attribute substitution_type_code
424 , ldet.substitution_value_to substitution_value_to
425 , ldet.pricing_phase_id pricing_phase_id
426 , ldet.applied_flag applied_flag
427 , ldet.automatic_flag automatic_flag
428 , ldet.override_flag override_flag
429 , ldet.benefit_qty benefit_qty
430 , ldet.benefit_uom_code benefit_uom_code
431 , ldet.accrual_flag accrual_flag
432 , ldet.accrual_conversion_rate accrual_conversion_rate
433 , ldet.charge_type_code charge_type_code
434 , ldet.charge_subtype_code charge_subtype_code
435 , ldet.line_quantity line_quantity
436 , adj.automatic_flag adj_automatic_flag
437 , adj.line_id adj_line_id
438 -- , adj.header_id adj_header_id
439 -- , adj.list_line_id adj_list_line_id
440 , adj.modified_from adj_modified_from
441 , adj.modified_to adj_modified_to
442 , adj.update_allowed adj_update_allowed
443 , adj.updated_flag adj_updated_flag
444 , adj.applied_flag adj_applied_flag
445 , adj.pricing_phase_id adj_pricing_phase_id
446 , adj.charge_type_code adj_charge_type_code
447 , adj.charge_subtype_code adj_charge_subtype_code
448 , adj.range_break_quantity adj_range_break_quantity
449 , adj.accrual_conversion_rate adj_accrual_conv_rate
450 , adj.pricing_group_sequence adj_pricing_group_seq
451 , adj.accrual_flag adj_accrual_flag
452 , adj.benefit_qty adj_benefit_qty
453 , adj.benefit_uom_code adj_benefit_uom_code
457 , adj.substitution_attribute adj_substitution_attribute
454 , adj.expiration_date adj_expiration_date
455 , adj.rebate_transaction_type_code adj_rebate_txn_type_code
456 , adj.price_break_type_code adj_price_break_type_code
458 , adj.proration_type_code adj_proration_type_code
459 , adj.include_on_returns_flag adj_include_on_returns
460 , nvl(adj.operand_per_pqty, adj.operand) adj_operand
461 , adj.adjusted_amount_per_pqty adj_adjusted_amount
462 , adj.arithmetic_operator adj_arithmetic_operator
463 , ql.expiration_date
464 , ql.proration_type_code
465 , ql.include_on_returns_flag
466 , ql.rebate_transaction_type_code
467 , ldet.pricing_status_text
468 , adj.price_adjustment_id
469 , ldet.order_qty_adj_amt ord_qty_adjamt
470 , adj.adjusted_amount adj_ord_qty_adjamt
471 , ldet.order_qty_operand ord_qty_operand
472 , adj.operand adj_ord_qty_operand
473 FROM qp_int_lines line
474 , oe_price_adjustments adj
475 ,qp_ldets_v ldet
476 , qp_list_lines ql
477 -- WHERE p_request_type_code = 'ONT'
478 --bug 3085453 handle pricing availability UI
479 -- they pass reqtype ONT and insert adj into ldets
480 WHERE QP_PREQ_PUB.G_CHECK_CUST_VIEW_FLAG = QP_PREQ_PUB.G_YES
481 and line.line_index = ldet.line_index
482 and (line.price_flag in (QP_PREQ_PUB.G_YES, QP_PREQ_PUB.G_PHASE)
483 OR nvl(line.processed_code,'0') = QP_PREQ_PUB.G_BY_ENGINE)
484 and line.pricing_status_code in (QP_PREQ_PUB.G_STATUS_UPDATED,
485 QP_PREQ_PUB.G_STATUS_UNCHANGED,
486 QP_PREQ_PUB.G_STATUS_GSA_VIOLATION)
487 and ldet.pricing_status_code IN (QP_PREQ_PUB.G_STATUS_NEW
488 ,QP_PREQ_PUB.G_STATUS_UPDATED)
489 and line.process_status in (QP_PREQ_PUB.G_STATUS_NEW,
490 QP_PREQ_PUB.G_STATUS_UPDATED,
491 QP_PREQ_PUB.G_STATUS_UNCHANGED,
492 'NEW'||QP_PREQ_PUB.G_STATUS_UNCHANGED,
493 --fix for bug 2823886 to do cleanup and calc for old fg line
494 'OLD'||QP_PREQ_PUB.G_STATUS_UNCHANGED)
495 and ((line.line_type_code = QP_PREQ_PUB.G_LINE_LEVEL
496 and line.line_id = adj.line_id)
497 OR
498 (line.line_type_code = QP_PREQ_PUB.G_ORDER_LEVEL
499 and line.line_id = adj.header_id))
500 and ldet.list_line_id = adj.list_line_id
501 and ldet.list_line_id = ql.list_line_id;
502 /*
503 UNION
504 SELECT /*+ ORDERED USE_NL(adj ldet ql)/
505 ldet.line_index line_index
506 , ldet.line_detail_index line_detail_index
507 , ldet.list_line_id created_from_list_line_id
508 , ldet.process_code process_code
509 , ldet.price_break_type_code price_break_type_code
510 , ldet.pricing_group_sequence pricing_group_sequence
511 , ldet.operand_calculation_code operand_calculation_code
512 , ldet.operand_value operand_value
513 , ldet.adjustment_amount adjustment_amount
514 , ldet.substitution_attribute substitution_type_code
515 , ldet.substitution_value_to substitution_value_to
516 , ldet.pricing_phase_id pricing_phase_id
517 , ldet.applied_flag applied_flag
518 , ldet.automatic_flag automatic_flag
519 , ldet.override_flag override_flag
520 , ldet.benefit_qty benefit_qty
521 , ldet.benefit_uom_code benefit_uom_code
522 , ldet.accrual_flag accrual_flag
523 , ldet.accrual_conversion_rate accrual_conversion_rate
524 , ldet.charge_type_code charge_type_code
525 , ldet.charge_subtype_code charge_subtype_code
526 , ldet.line_quantity line_quantity
527 , adj.automatic_flag adj_automatic_flag
528 , adj.line_id adj_line_id
529 -- , adj.header_id adj_header_id
530 -- , adj.list_line_id adj_list_line_id
531 , adj.modified_from adj_modified_from
532 , adj.modified_to adj_modified_to
533 , adj.update_allowed adj_update_allowed
534 , adj.updated_flag adj_updated_flag
535 , adj.applied_flag adj_applied_flag
536 , adj.pricing_phase_id adj_pricing_phase_id
537 , adj.charge_type_code adj_charge_type_code
538 , adj.charge_subtype_code adj_charge_subtype_code
539 , adj.range_break_quantity adj_range_break_quantity
540 , adj.accrual_conversion_rate adj_accrual_conv_rate
541 , adj.pricing_group_sequence adj_pricing_group_seq
542 , adj.accrual_flag adj_accrual_flag
543 , adj.benefit_qty adj_benefit_qty
544 , adj.benefit_uom_code adj_benefit_uom_code
545 , adj.expiration_date adj_expiration_date
546 , adj.rebate_transaction_type_code adj_rebate_txn_type_code
547 , adj.price_break_type_code adj_price_break_type_code
548 , adj.substitution_attribute adj_substitution_attribute
549 , adj.proration_type_code adj_proration_type_code
550 , adj.include_on_returns_flag adj_include_on_returns
551 , nvl(adj.operand_per_pqty, adj.operand) adj_operand
552 , adj.adjusted_amount_per_pqty adj_adjusted_amount
553 , adj.arithmetic_operator adj_arithmetic_operator
554 , ql.expiration_date
555 , ql.proration_type_code
556 , ql.include_on_returns_flag
557 , ql.rebate_transaction_type_code
558 , ldet.pricing_status_text
559 , adj.price_adjustment_id
560 , ldet.order_qty_adj_amt ord_qty_adjamt
561 , adj.adjusted_amount adj_ord_qty_adjamt
562 , ldet.order_qty_operand ord_qty_operand
563 , adj.operand adj_ord_qty_operand
564 FROM qp_int_lines line
565 , oe_price_adjustments adj
566 ,qp_ldets_v ldet
567 , qp_list_lines ql
568 -- WHERE p_request_type_code = 'ONT'
569 --bug 3085453 handle pricing availability UI
570 -- they pass reqtype ONT and insert adj into ldets
574 OR nvl(line.processed_code,'0') = QP_PREQ_PUB.G_BY_ENGINE)
571 WHERE QP_PREQ_PUB.G_CHECK_CUST_VIEW_FLAG = QP_PREQ_PUB.G_YES
572 and line.line_index = ldet.line_index
573 and (line.price_flag in (QP_PREQ_PUB.G_YES, QP_PREQ_PUB.G_PHASE)
575 and line.pricing_status_code in (QP_PREQ_PUB.G_STATUS_UPDATED,
576 QP_PREQ_PUB.G_STATUS_UNCHANGED,
577 QP_PREQ_PUB.G_STATUS_GSA_VIOLATION)
578 and ldet.pricing_status_code IN (QP_PREQ_PUB.G_STATUS_NEW
579 ,QP_PREQ_PUB.G_STATUS_UPDATED)
580 and line.process_status in (QP_PREQ_PUB.G_STATUS_NEW,
581 QP_PREQ_PUB.G_STATUS_UPDATED,
582 QP_PREQ_PUB.G_STATUS_UNCHANGED,
583 'NEW'||QP_PREQ_PUB.G_STATUS_UNCHANGED,
584 --fix for bug 2823886 to do cleanup and calc for old fg line
585 'OLD'||QP_PREQ_PUB.G_STATUS_UNCHANGED)
586 and (line.line_type_code = QP_PREQ_PUB.G_ORDER_LEVEL
587 and line.line_id = adj.header_id)
588 and ldet.list_line_id = adj.list_line_id
589 and ldet.list_line_id = ql.list_line_id
590 UNION
591 SELECT ldet.line_index line_index
592 , ldet.line_detail_index line_detail_index
593 , ldet.list_line_id created_from_list_line_id
594 , ldet.process_code process_code
595 , ldet.price_break_type_code price_break_type_code
596 , ldet.pricing_group_sequence pricing_group_sequence
597 , ldet.operand_calculation_code operand_calculation_code
598 , ldet.operand_value operand_value
599 , ldet.adjustment_amount adjustment_amount
600 , ldet.substitution_attribute substitution_type_code
601 , ldet.substitution_value_to substitution_value_to
602 , ldet.pricing_phase_id pricing_phase_id
603 , ldet.applied_flag applied_flag
604 , ldet.automatic_flag automatic_flag
605 , ldet.override_flag override_flag
606 , ldet.benefit_qty benefit_qty
607 , ldet.benefit_uom_code benefit_uom_code
608 , ldet.accrual_flag accrual_flag
609 , ldet.accrual_conversion_rate accrual_conversion_rate
610 , ldet.charge_type_code charge_type_code
611 , ldet.charge_subtype_code charge_subtype_code
612 , ldet.line_quantity line_quantity
613 , adj.automatic_flag adj_automatic_flag
614 , adj.created_from_list_line_id adj_list_line_id
615 , adj.substitution_value_from adj_modified_from
616 , adj.substitution_value_to adj_modified_to
617 , adj.override_flag adj_update_allowed
618 , adj.updated_flag adj_updated_flag
619 , adj.applied_flag adj_applied_flag
620 , adj.pricing_phase_id adj_pricing_phase_id
621 , adj.charge_type_code adj_charge_type_code
622 , adj.charge_subtype_code adj_charge_subtype_code
623 , adj.line_quantity adj_range_break_quantity
624 , adj.accrual_conversion_rate adj_accrual_conv_rate
625 , adj.pricing_group_sequence adj_pricing_group_seq
626 , adj.accrual_flag adj_accrual_flag
627 , adj.benefit_qty adj_benefit_qty
628 , adj.benefit_uom_code adj_benefit_uom_code
629 , ql.expiration_date adj_expiration_date
630 , ql.rebate_transaction_type_code adj_rebate_txn_type_code
631 , adj.price_break_type_code adj_price_break_type_code
632 , adj.substitution_type_code adj_substitution_attribute
633 , ql.proration_type_code adj_proration_type_code
634 , ql.include_on_returns_flag adj_include_on_returns
635 , adj.operand_value adj_operand
636 , adj.adjustment_amount adj_adjusted_amount
637 , adj.operand_calculation_code adj_arithmetic_operator
638 , ql.expiration_date
639 , ql.proration_type_code
640 , ql.include_on_returns_flag
641 , ql.rebate_transaction_type_code
642 , ldet.pricing_status_text
643 , adj.price_adjustment_id
644 , ldet.order_qty_adj_amt ord_qty_adjamt
645 , adj.order_qty_adj_amt adj_ord_qty_adjamt
646 , ldet.order_qty_operand ord_qty_operand
647 , adj.order_qty_operand adj_ord_qty_operand
648 FROM qp_int_lines line
649 , qp_int_ldets adj
650 ,qp_ldets_v ldet
651 , qp_list_lines ql
652 -- WHERE p_request_type_code <> 'ONT'
653 --bug 3085453 handle pricing availability UI
654 -- they pass reqtype ONT and insert adj into ldets
655 WHERE QP_PREQ_PUB.G_CHECK_CUST_VIEW_FLAG <> QP_PREQ_PUB.G_YES
656 and line.line_index = ldet.line_index
657 and (line.price_flag in (QP_PREQ_PUB.G_YES, QP_PREQ_PUB.G_PHASE)
658 OR nvl(line.processed_code,'0') = QP_PREQ_PUB.G_BY_ENGINE)
659 and line.pricing_status_code in (QP_PREQ_PUB.G_STATUS_UPDATED,
660 QP_PREQ_PUB.G_STATUS_UNCHANGED,
661 QP_PREQ_PUB.G_STATUS_GSA_VIOLATION)
662 and line.process_status in (QP_PREQ_PUB.G_STATUS_NEW,
663 QP_PREQ_PUB.G_STATUS_UPDATED,
664 QP_PREQ_PUB.G_STATUS_UNCHANGED,
665 'NEW'||QP_PREQ_PUB.G_STATUS_UNCHANGED,
666 --fix for bug 2823886 to do cleanup and calc for old fg line
670 and adj.line_index = line.line_index
667 'OLD'||QP_PREQ_PUB.G_STATUS_UNCHANGED)
668 and ldet.pricing_status_code IN (QP_PREQ_PUB.G_STATUS_NEW
669 ,QP_PREQ_PUB.G_STATUS_UPDATED)
671 and adj.pricing_status_code = QP_PREQ_PUB.G_STATUS_UNCHANGED
672 and ldet.list_line_id = adj.created_from_list_line_id
673 and ldet.list_line_id = ql.list_line_id;
674 */
675 --ADDED BY YANGLI FOR JAVA ENGINE PUB 3086881
676 /*
677 cursor l_attr_cur IS
678 SELECT line_index,
679 line_detail_index,
680 pricing_status_code,
681 context,
682 attribute,
683 value_from
684 FROM qp_npreq_line_attrs_tmp
685 WHERE line_detail_index is not null;
686
687 --ADDED BY YANGLI FOR JAVA ENGINE PUB 3086881
688 cursor l_attr_int_cur IS
689 SELECT line_index,
690 line_detail_index,
691 pricing_status_code,
692 context,
693 attribute,
694 value_from
695 FROM qp_int_line_attrs
696 WHERE line_detail_index is not null;
697 --ADDED BY YANGLI FOR JAVA ENGINE PUB 3086881
698 */
699 l_cur_line_index_tbl QP_PREQ_GRP.NUMBER_TYPE;
700 l_cur_dtl_index_tbl QP_PREQ_GRP.NUMBER_TYPE;
701 l_cur_list_line_id_tbl QP_PREQ_GRP.NUMBER_TYPE;
702 l_cur_process_code_tbl QP_PREQ_GRP.VARCHAR_TYPE;
703 l_cur_price_brk_type_tbl QP_PREQ_GRP.VARCHAR_TYPE;
704 l_cur_pricing_grp_seq_tbl QP_PREQ_GRP.NUMBER_TYPE;
705 l_cur_operator_tbl QP_PREQ_GRP.VARCHAR_TYPE;
706 l_cur_operand_tbl QP_PREQ_GRP.NUMBER_TYPE;
707 l_cur_adj_amt_tbl QP_PREQ_GRP.NUMBER_TYPE;
708 l_cur_substn_attr_tbl QP_PREQ_GRP.VARCHAR_TYPE;
709 l_cur_substn_val_to_tbl QP_PREQ_GRP.VARCHAR_TYPE;
710 l_cur_phase_id_tbl QP_PREQ_GRP.NUMBER_TYPE;
711 l_cur_applied_flag_tbl QP_PREQ_GRP.FLAG_TYPE;
712 l_cur_automatic_flag_tbl QP_PREQ_GRP.FLAG_TYPE;
713 l_cur_override_flag_tbl QP_PREQ_GRP.FLAG_TYPE;
714 l_cur_benefit_qty_tbl QP_PREQ_GRP.NUMBER_TYPE;
715 l_cur_benefit_uom_code_tbl QP_PREQ_GRP.VARCHAR_TYPE;
716 l_cur_accrual_flag_tbl QP_PREQ_GRP.FLAG_TYPE;
717 l_cur_accr_conv_rate_tbl QP_PREQ_GRP.NUMBER_TYPE;
718 l_cur_charge_type_tbl QP_PREQ_GRP.VARCHAR_TYPE;
719 l_cur_charge_subtype_tbl QP_PREQ_GRP.VARCHAR_TYPE;
720 l_cur_line_qty_tbl QP_PREQ_GRP.NUMBER_TYPE;
721 l_adj_automatic_flag_tbl QP_PREQ_GRP.FLAG_TYPE;
722 l_adj_line_id_tbl QP_PREQ_GRP.NUMBER_TYPE;
723 --l_adj_header_id_tbl QP_PREQ_GRP.NUMBER_TYPE;
724 --l_adj_list_line_id_tbl QP_PREQ_GRP.NUMBER_TYPE;
725 l_adj_modified_from_tbl QP_PREQ_GRP.VARCHAR_TYPE;
726 l_adj_modified_to_tbl QP_PREQ_GRP.VARCHAR_TYPE;
727 l_adj_update_allowed_tbl QP_PREQ_GRP.FLAG_TYPE;
728 l_adj_updated_flag_tbl QP_PREQ_GRP.FLAG_TYPE;
729 l_adj_applied_flag_tbl QP_PREQ_GRP.FLAG_TYPE;
730 l_adj_phase_id_tbl QP_PREQ_GRP.NUMBER_TYPE;
731 l_adj_charge_type_tbl QP_PREQ_GRP.VARCHAR_TYPE;
732 l_adj_charge_subtype_tbl QP_PREQ_GRP.VARCHAR_TYPE;
733 l_adj_range_break_qty_tbl QP_PREQ_GRP.NUMBER_TYPE;
734 l_adj_accrual_conv_rate_tbl QP_PREQ_GRP.NUMBER_TYPE;
735 l_adj_pricing_grp_seq_tbl QP_PREQ_GRP.NUMBER_TYPE;
736 l_adj_accrual_flag_tbl QP_PREQ_GRP.FLAG_TYPE;
737 l_adj_benefit_qty_tbl QP_PREQ_GRP.NUMBER_TYPE;
738 l_adj_benefit_uom_code_tbl QP_PREQ_GRP.VARCHAR_TYPE;
739 l_adj_exp_date_tbl QP_PREQ_GRP.DATE_TYPE;
740 l_adj_rebate_txn_type_tbl QP_PREQ_GRP.VARCHAR_TYPE;
741 l_adj_price_brk_type_tbl QP_PREQ_GRP.VARCHAR_TYPE;
742 l_adj_substn_attr_tbl QP_PREQ_GRP.VARCHAR_TYPE;
743 l_adj_prorat_type_tbl QP_PREQ_GRP.VARCHAR_TYPE;
744 l_adj_include_ret_flag_tbl QP_PREQ_GRP.FLAG_TYPE;
745 l_adj_operand_pqty_tbl QP_PREQ_GRP.NUMBER_TYPE;
746 l_adj_adj_amt_pqty_tbl QP_PREQ_GRP.NUMBER_TYPE;
747 l_adj_operator_tbl QP_PREQ_GRP.VARCHAR_TYPE;
748 l_cur_exp_date_tbl QP_PREQ_GRP.DATE_TYPE;
749 l_cur_prorat_type_tbl QP_PREQ_GRP.VARCHAR_TYPE;
750 l_cur_include_ret_flag_tbl QP_PREQ_GRP.FLAG_TYPE;
751 l_cur_reb_txn_type_tbl QP_PREQ_GRP.VARCHAR_TYPE;
752 l_cur_prc_sts_txt_tbl QP_PREQ_GRP.VARCHAR_TYPE;
753 l_cur_price_adj_id_tbl QP_PREQ_GRP.NUMBER_TYPE;
754 l_cur_ord_qty_adjamt QP_PREQ_GRP.NUMBER_TYPE;
755 l_adj_ord_qty_adjamt QP_PREQ_GRP.NUMBER_TYPE;
756 l_cur_ord_qty_operand QP_PREQ_GRP.NUMBER_TYPE;
757 l_adj_ord_qty_operand QP_PREQ_GRP.NUMBER_TYPE;
758
759
760 l_line_index_tbl QP_PREQ_GRP.NUMBER_TYPE;
761 l_line_dtl_index_tbl QP_PREQ_GRP.NUMBER_TYPE;
762 l_price_adj_id_tbl QP_PREQ_GRP.NUMBER_TYPE;
763 l_process_code_tbl QP_PREQ_GRP.VARCHAR_TYPE;
764 l_pricing_sts_text_tbl QP_PREQ_GRP.VARCHAR_TYPE;
765
766
767
768
769
770 X PLS_INTEGER;
771 nrows NUMBER := 500;
772 BEGIN
773
774 l_debug := QP_PREQ_GRP.G_DEBUG_ENGINE;
775 x:=0;
776 IF l_debug = FND_API.G_TRUE THEN
777 qp_preq_grp.engine_debug('begin update check clnup '||p_cleanup_flag);
778 qp_preq_grp.engine_debug('begin update check reqtype '||p_request_type_code);
779 qp_preq_grp.engine_debug('begin update check viewcode '||p_view_code);
780 END IF; --Bug No 4033618
781 --IF p_request_type_code in ('ONT','ASO')
782 IF p_cleanup_flag = 'Y'
783 --and p_request_type_code = 'ONT'
784 --bug 3085453 handle pricing availability UI
785 -- they pass reqtype ONT and insert adj into ldets
786 and QP_PREQ_PUB.G_CHECK_CUST_VIEW_FLAG = QP_PREQ_PUB.G_YES
787 THEN
788 IF l_debug = FND_API.G_TRUE THEN
789 qp_preq_grp.engine_debug('in update cleanup '||p_cleanup_flag);
790 END IF; -- Bug No 4033618
791 --ADDED BY YANGLI FOR JAVA ENGINE PUB 3086881
795 END IF;
792 IF QP_JAVA_ENGINE_UTIL_PUB.Java_Engine_Running = 'N' THEN
793 IF l_debug = FND_API.G_TRUE THEN
794 QP_PREQ_GRP.ENGINE_DEBUG('cleanup_adjustments Java Engine not Installed ----------');
796 --ADDED BY YANGLI FOR JAVA ENGINE PUB 3086881
797
798 OPEN l_update_cur;
799 LOOP
800
801 l_cur_line_index_tbl.delete;
802 l_cur_dtl_index_tbl.delete;
803 l_cur_list_line_id_tbl.delete;
804 l_cur_process_code_tbl.delete;
805 l_cur_price_brk_type_tbl.delete;
806 l_cur_pricing_grp_seq_tbl.delete;
807 l_cur_operator_tbl.delete;
808 l_cur_operand_tbl.delete;
809 l_cur_adj_amt_tbl.delete;
810 l_cur_substn_attr_tbl.delete;
811 l_cur_substn_val_to_tbl.delete;
812 l_cur_phase_id_tbl.delete;
813 l_cur_applied_flag_tbl.delete;
814 l_cur_automatic_flag_tbl.delete;
815 l_cur_override_flag_tbl.delete;
816 l_cur_benefit_qty_tbl.delete;
817 l_cur_benefit_uom_code_tbl.delete;
818 l_cur_accrual_flag_tbl.delete;
819 l_cur_accr_conv_rate_tbl.delete;
820 l_cur_charge_type_tbl.delete;
821 l_cur_charge_subtype_tbl.delete;
822 l_cur_line_qty_tbl.delete;
823 l_adj_automatic_flag_tbl.delete;
824 l_adj_line_id_tbl.delete;
825 --l_adj_header_id_tbl.delete;
826 --l_adj_list_line_id_tbl.delete;
827 l_adj_modified_from_tbl.delete;
828 l_adj_modified_to_tbl.delete;
829 l_adj_update_allowed_tbl.delete;
830 l_adj_updated_flag_tbl.delete;
831 l_adj_applied_flag_tbl.delete;
832 l_adj_phase_id_tbl.delete;
833 l_adj_charge_type_tbl.delete;
834 l_adj_charge_subtype_tbl.delete;
835 l_adj_range_break_qty_tbl.delete;
836 l_adj_accrual_conv_rate_tbl.delete;
837 l_adj_pricing_grp_seq_tbl.delete;
838 l_adj_accrual_flag_tbl.delete;
839 l_adj_benefit_qty_tbl.delete;
840 l_adj_benefit_uom_code_tbl.delete;
841 l_adj_exp_date_tbl.delete;
842 l_adj_rebate_txn_type_tbl.delete;
843 l_adj_price_brk_type_tbl.delete;
844 l_adj_substn_attr_tbl.delete;
845 l_adj_prorat_type_tbl.delete;
846 l_adj_include_ret_flag_tbl.delete;
847 l_adj_operand_pqty_tbl.delete;
848 l_adj_adj_amt_pqty_tbl.delete;
849 l_adj_operator_tbl.delete;
850 l_cur_exp_date_tbl.delete;
851 l_cur_prorat_type_tbl.delete;
852 l_cur_include_ret_flag_tbl.delete;
853 l_cur_reb_txn_type_tbl.delete;
854 l_cur_prc_sts_txt_tbl.delete;
855 l_cur_price_adj_id_tbl.delete;
856 l_cur_ord_qty_adjamt.delete;
857 l_adj_ord_qty_adjamt.delete;
858 l_cur_ord_qty_operand.delete;
859 l_adj_ord_qty_operand.delete;
860
861
862 FETCH l_update_cur
863 BULK COLLECT INTO
864 l_cur_line_index_tbl,
865 l_cur_dtl_index_tbl,
866 l_cur_list_line_id_tbl,
867 l_cur_process_code_tbl,
868 l_cur_price_brk_type_tbl,
869 l_cur_pricing_grp_seq_tbl,
870 l_cur_operator_tbl,
871 l_cur_operand_tbl,
872 l_cur_adj_amt_tbl,
873 l_cur_substn_attr_tbl,
874 l_cur_substn_val_to_tbl,
875 -- l_cur_prc_sts_txt_tbl,
876 l_cur_phase_id_tbl,
877 l_cur_applied_flag_tbl,
878 l_cur_automatic_flag_tbl,
879 l_cur_override_flag_tbl,
880 l_cur_benefit_qty_tbl,
881 l_cur_benefit_uom_code_tbl,
882 l_cur_accrual_flag_tbl,
883 l_cur_accr_conv_rate_tbl,
884 l_cur_charge_type_tbl,
885 l_cur_charge_subtype_tbl,
886 l_cur_line_qty_tbl,
887 l_adj_automatic_flag_tbl,
888 l_adj_line_id_tbl,
889 --l_adj_header_id_tbl,
890 --l_adj_list_line_id_tbl,
891 l_adj_modified_from_tbl,
892 l_adj_modified_to_tbl,
893 l_adj_update_allowed_tbl,
894 l_adj_updated_flag_tbl,
895 l_adj_applied_flag_tbl,
896 l_adj_phase_id_tbl,
897 l_adj_charge_type_tbl,
898 l_adj_charge_subtype_tbl,
899 l_adj_range_break_qty_tbl,
900 l_adj_accrual_conv_rate_tbl,
901 l_adj_pricing_grp_seq_tbl,
902 l_adj_accrual_flag_tbl,
903 l_adj_benefit_qty_tbl,
904 l_adj_benefit_uom_code_tbl,
905 l_adj_exp_date_tbl,
906 l_adj_rebate_txn_type_tbl,
907 l_adj_price_brk_type_tbl,
908 l_adj_substn_attr_tbl,
909 l_adj_prorat_type_tbl,
910 l_adj_include_ret_flag_tbl,
911 l_adj_operand_pqty_tbl,
912 l_adj_adj_amt_pqty_tbl,
913 l_adj_operator_tbl,
914 l_cur_exp_date_tbl,
915 l_cur_prorat_type_tbl,
916 l_cur_include_ret_flag_tbl,
917 l_cur_reb_txn_type_tbl,
918 l_cur_prc_sts_txt_tbl,
919 l_cur_price_adj_id_tbl,
920 l_cur_ord_qty_adjamt,
921 l_adj_ord_qty_adjamt,
922 l_cur_ord_qty_operand,
923 l_adj_ord_qty_operand
924 LIMIT nrows;
925 EXIT WHEN l_cur_dtl_index_tbl.COUNT = 0;
926
927 FOR i IN l_cur_dtl_index_tbl.FIRST..l_cur_dtl_index_tbl.LAST
928 LOOP
929
930 IF QP_PREQ_GRP.G_DEBUG_ENGINE = FND_API.G_TRUE
931 THEN
932 qp_preq_grp.engine_debug('loop update cur line ind '
933 ||l_cur_line_index_tbl(i)||' line_dtl_index '
934 ||l_cur_dtl_index_tbl(i)||' price_adj_id '
935 ||l_cur_price_adj_id_tbl(i));
936 qp_preq_grp.engine_debug('adj amt '||l_cur_adj_amt_tbl(i)
937 ||' adj '||l_adj_adj_amt_pqty_tbl(i));
938 qp_preq_grp.engine_debug('operand '||l_cur_operand_tbl(i)
939 ||' adj '||l_adj_operand_pqty_tbl(i));
940 qp_preq_grp.engine_debug('bucket '||l_cur_pricing_grp_seq_tbl(i)
941 ||' adj '||l_adj_pricing_grp_seq_tbl(i));
945 ||' adj charge subtype '||l_adj_charge_subtype_tbl(i));
942 qp_preq_grp.engine_debug('charg type '||l_cur_charge_type_tbl(i)
943 ||' adj '||l_adj_charge_type_tbl(i));
944 qp_preq_grp.engine_debug('chstype '||l_cur_charge_subtype_tbl(i)
946 qp_preq_grp.engine_debug('pbrk '||l_cur_price_brk_type_tbl(i)
947 ||' adj price brk type '||l_adj_price_brk_type_tbl(i));
948 qp_preq_grp.engine_debug('operator '||l_cur_operator_tbl(i)
949 ||' adj operator '||l_adj_operator_tbl(i));
950 qp_preq_grp.engine_debug('phase id '||l_cur_phase_id_tbl(i)
951 ||' adj phase id '||l_adj_phase_id_tbl(i));
952 qp_preq_grp.engine_debug('autoflg '||l_cur_automatic_flag_tbl(i)
953 ||' adj autom flag '||l_adj_automatic_flag_tbl(i));
954 qp_preq_grp.engine_debug('override '||l_cur_override_flag_tbl(i)
955 ||' adj override '||l_adj_update_allowed_tbl(i));
956 qp_preq_grp.engine_debug('sub attr '||l_cur_substn_attr_tbl(i)
957 ||' adj sub attr '||l_adj_substn_attr_tbl(i));
958 qp_preq_grp.engine_debug('subval '||l_cur_substn_val_to_tbl(i)
959 ||' adj substn val '||l_adj_modified_to_tbl(i));
960 qp_preq_grp.engine_debug('benf qty '||l_cur_benefit_qty_tbl(i)
961 ||' adj benefit qty '||l_adj_benefit_qty_tbl(i));
962 qp_preq_grp.engine_debug('bnuom '||l_cur_benefit_uom_code_tbl(i)
963 ||' adj benefit uom '||l_adj_benefit_uom_code_tbl(i));
964 qp_preq_grp.engine_debug('accrual '||l_cur_accrual_flag_tbl(i)
965 ||' adj accrual flag '||l_adj_accrual_flag_tbl(i));
966 qp_preq_grp.engine_debug('conv '||l_cur_accr_conv_rate_tbl(i)
967 ||' adj accr conv rate '||l_adj_accrual_conv_rate_tbl(i));
968 qp_preq_grp.engine_debug('rebtxn '||l_cur_reb_txn_type_tbl(i)
969 ||' adj rebate txn type '||l_adj_rebate_txn_type_tbl(i));
970 qp_preq_grp.engine_debug('prorat typ '||l_cur_prorat_type_tbl(i)
971 ||' adj proration type '||l_adj_prorat_type_tbl(i));
972 qp_preq_grp.engine_debug('incl '||l_cur_include_ret_flag_tbl(i)
973 ||' adj include on ret '||l_adj_include_ret_flag_tbl(i));
974 qp_preq_grp.engine_debug('line_qty '||l_cur_line_qty_tbl(i)
975 ||' adj range_brk_qty '||l_adj_range_break_qty_tbl(i));
976 qp_preq_grp.engine_debug('exp date '||l_cur_exp_date_tbl(i)
977 ||' adj exp date '||l_adj_exp_date_tbl(i)
978 ||' pricing sts text '||l_cur_prc_sts_txt_tbl(i));
979 qp_preq_grp.engine_debug('ord_qty_adjamt '
980 ||l_cur_ord_qty_adjamt(i)
981 ||' adj ord_qty_adjamt '||l_adj_ord_qty_adjamt(i)
982 ||' ord_qty_operand '||l_cur_ord_qty_operand(i)
983 ||' adj ord_qty_operand '||l_adj_ord_qty_operand(i));
984 END IF;--debug
985
986
987 IF
988 nvl(l_cur_adj_amt_tbl(i),FND_API.G_MISS_NUM) =
989 nvl(l_adj_adj_amt_pqty_tbl(i),FND_API.G_MISS_NUM)
990 AND nvl(l_cur_operand_tbl(i),FND_API.G_MISS_NUM) =
991 nvl(l_adj_operand_pqty_tbl(i),FND_API.G_MISS_NUM)
992 AND nvl(l_cur_pricing_grp_seq_tbl(i),-1) =
993 nvl(l_adj_pricing_grp_seq_tbl(i),-1)
994 AND nvl(l_cur_charge_type_tbl(i),'NULL') =
995 nvl(l_adj_charge_type_tbl(i),'NULL')
996 AND nvl(l_cur_charge_subtype_tbl(i),'NULL') =
997 nvl(l_adj_charge_subtype_tbl(i), 'NULL')
998 AND nvl(l_cur_price_brk_type_tbl(i), 'NULL') =
999 nvl(l_adj_price_brk_type_tbl(i), 'NULL')
1000 AND nvl(l_cur_operator_tbl(i),'NULL') =
1001 nvl(l_adj_operator_tbl(i), 'NULL')
1002 AND nvl(l_cur_phase_id_tbl(i),0) =
1003 nvl(l_adj_phase_id_tbl(i),0)
1004 AND nvl(l_cur_automatic_flag_tbl(i),' ') =
1005 nvl(l_adj_automatic_flag_tbl(i),' ')
1006 AND nvl(l_cur_override_flag_tbl(i),' ') =
1007 nvl(l_adj_update_allowed_tbl(i),' ')
1008 AND nvl(l_cur_substn_attr_tbl(i),'NULL') =
1009 nvl(l_adj_substn_attr_tbl(i),'NULL')
1010 AND nvl(l_cur_substn_val_to_tbl(i), 'NULL') =
1011 nvl(l_adj_modified_to_tbl(i), 'NULL')
1012 AND nvl(l_cur_benefit_qty_tbl(i),0) =
1013 nvl(l_adj_benefit_qty_tbl(i),0)
1014 AND nvl(l_cur_benefit_uom_code_tbl(i),'NULL') =
1015 nvl(l_adj_benefit_uom_code_tbl(i),'NULL')
1016 AND nvl(l_cur_accrual_flag_tbl(i),' ') =
1017 nvl(l_adj_accrual_flag_tbl(i),'NULL')
1018 AND nvl(l_cur_accr_conv_rate_tbl(i),0) =
1019 nvl(l_adj_accrual_conv_rate_tbl(i),0)
1020 AND nvl(l_cur_reb_txn_type_tbl(i),'NULL') =
1021 nvl(l_adj_rebate_txn_type_tbl(i),'NULL')
1022 AND nvl(l_cur_prorat_type_tbl(i),'NULL') =
1023 nvl(l_adj_prorat_type_tbl(i),'NULL')
1024 AND nvl(l_cur_include_ret_flag_tbl(i), ' ') =
1025 nvl(l_adj_include_ret_flag_tbl(i),' ')
1026 AND nvl(l_cur_exp_date_tbl(i),FND_API.G_MISS_DATE) =
1027 nvl(l_adj_exp_date_tbl(i), FND_API.G_MISS_DATE)
1028 AND nvl(l_cur_line_qty_tbl(i),FND_API.G_MISS_NUM) =
1029 nvl(l_adj_range_break_qty_tbl(i), FND_API.G_MISS_NUM)
1030 AND nvl(l_cur_ord_qty_adjamt(i),FND_API.G_MISS_NUM) =
1031 nvl(l_adj_ord_qty_adjamt(i), FND_API.G_MISS_NUM)
1032 AND nvl(l_cur_ord_qty_operand(i),FND_API.G_MISS_NUM) =
1033 nvl(l_adj_ord_qty_operand(i), FND_API.G_MISS_NUM)
1034 THEN
1035 x:=x+1;
1036 IF QP_PREQ_GRP.G_DEBUG_ENGINE = FND_API.G_TRUE THEN
1037 qp_preq_grp.engine_debug('if update check '||x);
1038 qp_preq_grp.engine_debug('update check dtls '
1039 ||l_cur_dtl_index_tbl(i)||' adj_id '
1040 ||l_cur_price_adj_id_tbl(i)||' id '
1041 ||l_cur_list_line_id_tbl(i)||' adj adj amt '
1042 ||l_adj_adj_amt_pqty_tbl(i)||' adj amt '
1043 ||l_cur_adj_amt_tbl(i));
1044 END IF; --Bug No 4033618
1045 l_line_index_tbl(x) := l_cur_line_index_tbl(i);
1046 l_line_dtl_index_tbl(x) := l_cur_dtl_index_tbl(i);
1047 l_price_adj_id_tbl(x) := l_cur_price_adj_id_tbl(i);
1048 l_process_code_tbl(x) := QP_PREQ_PUB.G_STATUS_UNCHANGED;
1052 IF QP_PREQ_GRP.G_DEBUG_ENGINE = FND_API.G_TRUE THEN
1049 l_pricing_sts_text_tbl(x) := 'ADJUSTMENT INFO UNCHANGED';
1050 ELSE
1051 x:=x+1;
1053 qp_preq_grp.engine_debug('else update check '||x);
1054 qp_preq_grp.engine_debug('update check dtls '
1055 ||l_cur_dtl_index_tbl(i)||' adj_id '
1056 ||l_cur_price_adj_id_tbl(i)||' id '
1057 ||l_cur_list_line_id_tbl(i)||' adj adj amt '
1058 ||l_adj_adj_amt_pqty_tbl(i)||' adj amt '
1059 ||l_cur_adj_amt_tbl(i));
1060 END IF; --Bug No 4033618
1061
1062 l_line_index_tbl(x) := l_cur_line_index_tbl(i);
1063 l_line_dtl_index_tbl(x) := l_cur_dtl_index_tbl(i);
1064 l_price_adj_id_tbl(x) := l_cur_price_adj_id_tbl(i);
1065 l_process_code_tbl(x) := QP_PREQ_PUB.G_STATUS_UPDATED;
1066 l_pricing_sts_text_tbl(x) := l_cur_prc_sts_txt_tbl(i);
1067 END IF;
1068
1069 END LOOP;--check each rec from fetch
1070 END LOOP;--bulk fetch
1071 CLOSE l_update_cur;
1072 ELSE
1073 --ADDED BY YANGLI FOR JAVA ENGINE PUB 3086881
1074 IF l_debug = FND_API.G_TRUE THEN
1075 QP_PREQ_GRP.ENGINE_DEBUG('cleanup_adjustments() Java Engine is Installed ----------');
1076 END IF;
1077 --ADDED BY YANGLI FOR JAVA ENGINE PUB 3086881
1078
1079 OPEN l_update_int_cur;
1080 LOOP
1081
1082 l_cur_line_index_tbl.delete;
1083 l_cur_dtl_index_tbl.delete;
1084 l_cur_list_line_id_tbl.delete;
1085 l_cur_process_code_tbl.delete;
1086 l_cur_price_brk_type_tbl.delete;
1087 l_cur_pricing_grp_seq_tbl.delete;
1088 l_cur_operator_tbl.delete;
1089 l_cur_operand_tbl.delete;
1090 l_cur_adj_amt_tbl.delete;
1091 l_cur_substn_attr_tbl.delete;
1092 l_cur_substn_val_to_tbl.delete;
1093 l_cur_phase_id_tbl.delete;
1094 l_cur_applied_flag_tbl.delete;
1095 l_cur_automatic_flag_tbl.delete;
1096 l_cur_override_flag_tbl.delete;
1097 l_cur_benefit_qty_tbl.delete;
1098 l_cur_benefit_uom_code_tbl.delete;
1099 l_cur_accrual_flag_tbl.delete;
1100 l_cur_accr_conv_rate_tbl.delete;
1101 l_cur_charge_type_tbl.delete;
1102 l_cur_charge_subtype_tbl.delete;
1103 l_cur_line_qty_tbl.delete;
1104 l_adj_automatic_flag_tbl.delete;
1105 l_adj_line_id_tbl.delete;
1106 --l_adj_header_id_tbl.delete;
1107 --l_adj_list_line_id_tbl.delete;
1108 l_adj_modified_from_tbl.delete;
1109 l_adj_modified_to_tbl.delete;
1110 l_adj_update_allowed_tbl.delete;
1111 l_adj_updated_flag_tbl.delete;
1112 l_adj_applied_flag_tbl.delete;
1113 l_adj_phase_id_tbl.delete;
1114 l_adj_charge_type_tbl.delete;
1115 l_adj_charge_subtype_tbl.delete;
1116 l_adj_range_break_qty_tbl.delete;
1117 l_adj_accrual_conv_rate_tbl.delete;
1118 l_adj_pricing_grp_seq_tbl.delete;
1119 l_adj_accrual_flag_tbl.delete;
1120 l_adj_benefit_qty_tbl.delete;
1121 l_adj_benefit_uom_code_tbl.delete;
1122 l_adj_exp_date_tbl.delete;
1123 l_adj_rebate_txn_type_tbl.delete;
1124 l_adj_price_brk_type_tbl.delete;
1125 l_adj_substn_attr_tbl.delete;
1126 l_adj_prorat_type_tbl.delete;
1127 l_adj_include_ret_flag_tbl.delete;
1128 l_adj_operand_pqty_tbl.delete;
1129 l_adj_adj_amt_pqty_tbl.delete;
1130 l_adj_operator_tbl.delete;
1131 l_cur_exp_date_tbl.delete;
1132 l_cur_prorat_type_tbl.delete;
1133 l_cur_include_ret_flag_tbl.delete;
1134 l_cur_reb_txn_type_tbl.delete;
1135 l_cur_prc_sts_txt_tbl.delete;
1136 l_cur_price_adj_id_tbl.delete;
1137 l_cur_ord_qty_adjamt.delete;
1138 l_adj_ord_qty_adjamt.delete;
1139 l_cur_ord_qty_operand.delete;
1140 l_adj_ord_qty_operand.delete;
1141
1142
1143 FETCH l_update_int_cur
1144 BULK COLLECT INTO
1145 l_cur_line_index_tbl,
1146 l_cur_dtl_index_tbl,
1147 l_cur_list_line_id_tbl,
1148 l_cur_process_code_tbl,
1149 l_cur_price_brk_type_tbl,
1150 l_cur_pricing_grp_seq_tbl,
1151 l_cur_operator_tbl,
1152 l_cur_operand_tbl,
1153 l_cur_adj_amt_tbl,
1154 l_cur_substn_attr_tbl,
1155 l_cur_substn_val_to_tbl,
1156 -- l_cur_prc_sts_txt_tbl,
1157 l_cur_phase_id_tbl,
1158 l_cur_applied_flag_tbl,
1159 l_cur_automatic_flag_tbl,
1160 l_cur_override_flag_tbl,
1161 l_cur_benefit_qty_tbl,
1162 l_cur_benefit_uom_code_tbl,
1163 l_cur_accrual_flag_tbl,
1164 l_cur_accr_conv_rate_tbl,
1165 l_cur_charge_type_tbl,
1166 l_cur_charge_subtype_tbl,
1167 l_cur_line_qty_tbl,
1168 l_adj_automatic_flag_tbl,
1169 l_adj_line_id_tbl,
1170 --l_adj_header_id_tbl,
1171 --l_adj_list_line_id_tbl,
1172 l_adj_modified_from_tbl,
1173 l_adj_modified_to_tbl,
1174 l_adj_update_allowed_tbl,
1175 l_adj_updated_flag_tbl,
1176 l_adj_applied_flag_tbl,
1177 l_adj_phase_id_tbl,
1178 l_adj_charge_type_tbl,
1179 l_adj_charge_subtype_tbl,
1180 l_adj_range_break_qty_tbl,
1181 l_adj_accrual_conv_rate_tbl,
1182 l_adj_pricing_grp_seq_tbl,
1183 l_adj_accrual_flag_tbl,
1184 l_adj_benefit_qty_tbl,
1185 l_adj_benefit_uom_code_tbl,
1186 l_adj_exp_date_tbl,
1187 l_adj_rebate_txn_type_tbl,
1188 l_adj_price_brk_type_tbl,
1189 l_adj_substn_attr_tbl,
1190 l_adj_prorat_type_tbl,
1191 l_adj_include_ret_flag_tbl,
1192 l_adj_operand_pqty_tbl,
1193 l_adj_adj_amt_pqty_tbl,
1194 l_adj_operator_tbl,
1195 l_cur_exp_date_tbl,
1196 l_cur_prorat_type_tbl,
1197 l_cur_include_ret_flag_tbl,
1198 l_cur_reb_txn_type_tbl,
1199 l_cur_prc_sts_txt_tbl,
1203 l_cur_ord_qty_operand,
1200 l_cur_price_adj_id_tbl,
1201 l_cur_ord_qty_adjamt,
1202 l_adj_ord_qty_adjamt,
1204 l_adj_ord_qty_operand
1205 LIMIT nrows;
1206 EXIT WHEN l_cur_dtl_index_tbl.COUNT = 0;
1207
1208 FOR i IN l_cur_dtl_index_tbl.FIRST..l_cur_dtl_index_tbl.LAST
1209 LOOP
1210
1211 IF QP_PREQ_GRP.G_DEBUG_ENGINE = FND_API.G_TRUE
1212 THEN
1213 qp_preq_grp.engine_debug('loop update cur line ind '
1214 ||l_cur_line_index_tbl(i)||' line_dtl_index '
1215 ||l_cur_dtl_index_tbl(i)||' price_adj_id '
1216 ||l_cur_price_adj_id_tbl(i));
1217 qp_preq_grp.engine_debug('adj amt '||l_cur_adj_amt_tbl(i)
1218 ||' adj '||l_adj_adj_amt_pqty_tbl(i));
1219 qp_preq_grp.engine_debug('operand '||l_cur_operand_tbl(i)
1220 ||' adj '||l_adj_operand_pqty_tbl(i));
1221 qp_preq_grp.engine_debug('bucket '||l_cur_pricing_grp_seq_tbl(i)
1222 ||' adj '||l_adj_pricing_grp_seq_tbl(i));
1223 qp_preq_grp.engine_debug('charg type '||l_cur_charge_type_tbl(i)
1224 ||' adj '||l_adj_charge_type_tbl(i));
1225 qp_preq_grp.engine_debug('chstype '||l_cur_charge_subtype_tbl(i)
1226 ||' adj charge subtype '||l_adj_charge_subtype_tbl(i));
1227 qp_preq_grp.engine_debug('pbrk '||l_cur_price_brk_type_tbl(i)
1228 ||' adj price brk type '||l_adj_price_brk_type_tbl(i));
1229 qp_preq_grp.engine_debug('operator '||l_cur_operator_tbl(i)
1230 ||' adj operator '||l_adj_operator_tbl(i));
1231 qp_preq_grp.engine_debug('phase id '||l_cur_phase_id_tbl(i)
1232 ||' adj phase id '||l_adj_phase_id_tbl(i));
1233 qp_preq_grp.engine_debug('autoflg '||l_cur_automatic_flag_tbl(i)
1234 ||' adj autom flag '||l_adj_automatic_flag_tbl(i));
1235 qp_preq_grp.engine_debug('override '||l_cur_override_flag_tbl(i)
1236 ||' adj override '||l_adj_update_allowed_tbl(i));
1237 qp_preq_grp.engine_debug('sub attr '||l_cur_substn_attr_tbl(i)
1238 ||' adj sub attr '||l_adj_substn_attr_tbl(i));
1239 qp_preq_grp.engine_debug('subval '||l_cur_substn_val_to_tbl(i)
1240 ||' adj substn val '||l_adj_modified_to_tbl(i));
1241 qp_preq_grp.engine_debug('benf qty '||l_cur_benefit_qty_tbl(i)
1242 ||' adj benefit qty '||l_adj_benefit_qty_tbl(i));
1243 qp_preq_grp.engine_debug('bnuom '||l_cur_benefit_uom_code_tbl(i)
1244 ||' adj benefit uom '||l_adj_benefit_uom_code_tbl(i));
1245 qp_preq_grp.engine_debug('accrual '||l_cur_accrual_flag_tbl(i)
1246 ||' adj accrual flag '||l_adj_accrual_flag_tbl(i));
1247 qp_preq_grp.engine_debug('conv '||l_cur_accr_conv_rate_tbl(i)
1248 ||' adj accr conv rate '||l_adj_accrual_conv_rate_tbl(i));
1249 qp_preq_grp.engine_debug('rebtxn '||l_cur_reb_txn_type_tbl(i)
1250 ||' adj rebate txn type '||l_adj_rebate_txn_type_tbl(i));
1251 qp_preq_grp.engine_debug('prorat typ '||l_cur_prorat_type_tbl(i)
1252 ||' adj proration type '||l_adj_prorat_type_tbl(i));
1253 qp_preq_grp.engine_debug('incl '||l_cur_include_ret_flag_tbl(i)
1254 ||' adj include on ret '||l_adj_include_ret_flag_tbl(i));
1255 qp_preq_grp.engine_debug('line_qty '||l_cur_line_qty_tbl(i)
1256 ||' adj range_brk_qty '||l_adj_range_break_qty_tbl(i));
1257 qp_preq_grp.engine_debug('exp date '||l_cur_exp_date_tbl(i)
1258 ||' adj exp date '||l_adj_exp_date_tbl(i)
1259 ||' pricing sts text '||l_cur_prc_sts_txt_tbl(i));
1260 qp_preq_grp.engine_debug('ord_qty_adjamt '
1261 ||l_cur_ord_qty_adjamt(i)
1262 ||' adj ord_qty_adjamt '||l_adj_ord_qty_adjamt(i)
1263 ||' ord_qty_operand '||l_cur_ord_qty_operand(i)
1264 ||' adj ord_qty_operand '||l_adj_ord_qty_operand(i));
1265 END IF;--debug
1266
1267
1268 IF
1269 nvl(l_cur_adj_amt_tbl(i),FND_API.G_MISS_NUM) =
1270 nvl(l_adj_adj_amt_pqty_tbl(i),FND_API.G_MISS_NUM)
1271 AND nvl(l_cur_operand_tbl(i),FND_API.G_MISS_NUM) =
1272 nvl(l_adj_operand_pqty_tbl(i),FND_API.G_MISS_NUM)
1273 AND nvl(l_cur_pricing_grp_seq_tbl(i),-1) =
1274 nvl(l_adj_pricing_grp_seq_tbl(i),-1)
1275 AND nvl(l_cur_charge_type_tbl(i),'NULL') =
1276 nvl(l_adj_charge_type_tbl(i),'NULL')
1277 AND nvl(l_cur_charge_subtype_tbl(i),'NULL') =
1278 nvl(l_adj_charge_subtype_tbl(i), 'NULL')
1279 AND nvl(l_cur_price_brk_type_tbl(i), 'NULL') =
1280 nvl(l_adj_price_brk_type_tbl(i), 'NULL')
1281 AND nvl(l_cur_operator_tbl(i),'NULL') =
1282 nvl(l_adj_operator_tbl(i), 'NULL')
1283 AND nvl(l_cur_phase_id_tbl(i),0) =
1284 nvl(l_adj_phase_id_tbl(i),0)
1285 AND nvl(l_cur_automatic_flag_tbl(i),' ') =
1286 nvl(l_adj_automatic_flag_tbl(i),' ')
1287 AND nvl(l_cur_override_flag_tbl(i),' ') =
1288 nvl(l_adj_update_allowed_tbl(i),' ')
1289 AND nvl(l_cur_substn_attr_tbl(i),'NULL') =
1290 nvl(l_adj_substn_attr_tbl(i),'NULL')
1291 AND nvl(l_cur_substn_val_to_tbl(i), 'NULL') =
1292 nvl(l_adj_modified_to_tbl(i), 'NULL')
1293 AND nvl(l_cur_benefit_qty_tbl(i),0) =
1294 nvl(l_adj_benefit_qty_tbl(i),0)
1295 AND nvl(l_cur_benefit_uom_code_tbl(i),'NULL') =
1296 nvl(l_adj_benefit_uom_code_tbl(i),'NULL')
1297 AND nvl(l_cur_accrual_flag_tbl(i),' ') =
1298 nvl(l_adj_accrual_flag_tbl(i),'NULL')
1299 AND nvl(l_cur_accr_conv_rate_tbl(i),0) =
1300 nvl(l_adj_accrual_conv_rate_tbl(i),0)
1301 AND nvl(l_cur_reb_txn_type_tbl(i),'NULL') =
1302 nvl(l_adj_rebate_txn_type_tbl(i),'NULL')
1303 AND nvl(l_cur_prorat_type_tbl(i),'NULL') =
1304 nvl(l_adj_prorat_type_tbl(i),'NULL')
1305 AND nvl(l_cur_include_ret_flag_tbl(i), ' ') =
1306 nvl(l_adj_include_ret_flag_tbl(i),' ')
1307 AND nvl(l_cur_exp_date_tbl(i),FND_API.G_MISS_DATE) =
1308 nvl(l_adj_exp_date_tbl(i), FND_API.G_MISS_DATE)
1309 AND nvl(l_cur_line_qty_tbl(i),FND_API.G_MISS_NUM) =
1310 nvl(l_adj_range_break_qty_tbl(i), FND_API.G_MISS_NUM)
1314 nvl(l_adj_ord_qty_operand(i), FND_API.G_MISS_NUM)
1311 AND nvl(l_cur_ord_qty_adjamt(i),FND_API.G_MISS_NUM) =
1312 nvl(l_adj_ord_qty_adjamt(i), FND_API.G_MISS_NUM)
1313 AND nvl(l_cur_ord_qty_operand(i),FND_API.G_MISS_NUM) =
1315 THEN
1316 x:=x+1;
1317 IF QP_PREQ_GRP.G_DEBUG_ENGINE = FND_API.G_TRUE THEN
1318 qp_preq_grp.engine_debug('if update check '||x);
1319 qp_preq_grp.engine_debug('update check dtls '
1320 ||l_cur_dtl_index_tbl(i)||' adj_id '
1321 ||l_cur_price_adj_id_tbl(i)||' id '
1322 ||l_cur_list_line_id_tbl(i)||' adj adj amt '
1323 ||l_adj_adj_amt_pqty_tbl(i)||' adj amt '
1324 ||l_cur_adj_amt_tbl(i));
1325 END IF; --Bug No 4033618
1326 l_line_index_tbl(x) := l_cur_line_index_tbl(i);
1327 l_line_dtl_index_tbl(x) := l_cur_dtl_index_tbl(i);
1328 l_price_adj_id_tbl(x) := l_cur_price_adj_id_tbl(i);
1329 l_process_code_tbl(x) := QP_PREQ_PUB.G_STATUS_UNCHANGED;
1330 l_pricing_sts_text_tbl(x) := 'ADJUSTMENT INFO UNCHANGED';
1331 ELSE
1332 x:=x+1;
1333 IF QP_PREQ_GRP.G_DEBUG_ENGINE = FND_API.G_TRUE THEN
1334 qp_preq_grp.engine_debug('else update check '||x);
1335 qp_preq_grp.engine_debug('update check dtls '
1336 ||l_cur_dtl_index_tbl(i)||' adj_id '
1337 ||l_cur_price_adj_id_tbl(i)||' id '
1338 ||l_cur_list_line_id_tbl(i)||' adj adj amt '
1339 ||l_adj_adj_amt_pqty_tbl(i)||' adj amt '
1340 ||l_cur_adj_amt_tbl(i));
1341 END IF; --Bug No 4033618
1342 l_line_index_tbl(x) := l_cur_line_index_tbl(i);
1343 l_line_dtl_index_tbl(x) := l_cur_dtl_index_tbl(i);
1344 l_price_adj_id_tbl(x) := l_cur_price_adj_id_tbl(i);
1345 l_process_code_tbl(x) := QP_PREQ_PUB.G_STATUS_UPDATED;
1346 l_pricing_sts_text_tbl(x) := l_cur_prc_sts_txt_tbl(i);
1347 END IF;
1348
1349 END LOOP;--check each rec from fetch
1350 END LOOP;--bulk fetch
1351 CLOSE l_update_int_cur;
1352 --ADDED BY YANGLI FOR JAVA ENGINE PUB 3086881
1353 END IF;
1354 --ADDED BY YANGLI FOR JAVA ENGINE PUB 3086881
1355
1356 IF l_line_dtl_index_tbl.COUNT > 0
1357 THEN
1358
1359 IF QP_PREQ_GRP.G_DEBUG_ENGINE = FND_API.G_TRUE
1360 THEN
1361 FOR x IN l_line_dtl_index_tbl.FIRST..l_line_dtl_index_tbl.LAST
1362 LOOP
1363 IF l_debug = FND_API.G_TRUE THEN
1364 QP_PREQ_GRP.engine_debug('Cleanup debug '
1365 ||' line index '||l_line_index_tbl(x)
1366 ||' line dtl index '||l_line_dtl_index_tbl(x)
1367 ||' price_adj_id '||l_price_adj_id_tbl(x)
1368 ||' process code '||l_process_code_tbl(x)
1369 ||' status text '||l_pricing_sts_text_tbl(x));
1370 END IF;
1371 END LOOP;
1372 END IF;
1373 --ADDED BY YANGLI FOR JAVA ENGINE PUB 3086881
1374 IF QP_JAVA_ENGINE_UTIL_PUB.Java_Engine_Running = 'N' THEN
1375 IF l_debug = FND_API.G_TRUE THEN
1376 QP_PREQ_GRP.ENGINE_DEBUG('Java Engine not Installed ----------');
1377 END IF;
1378 --ADDED BY YANGLI FOR JAVA ENGINE PUB 3086881
1379
1380 FORALL x IN l_line_dtl_index_tbl.FIRST..l_line_dtl_index_tbl.LAST
1381 UPDATE qp_npreq_ldets_tmp
1382 SET process_code = l_process_code_tbl(x)
1383 , price_adjustment_id = l_price_adj_id_tbl(x)
1384 , pricing_status_text = l_pricing_sts_text_tbl(x)
1385 WHERE line_index = l_line_index_tbl(x)
1386 and line_detail_index = l_line_dtl_index_tbl(x);
1387 --ADDED BY YANGLI FOR JAVA ENGINE PUB 3086881
1388 ELSE
1389 IF l_debug = FND_API.G_TRUE THEN
1390 QP_PREQ_GRP.ENGINE_DEBUG('Java Engine is Installed ----------');
1391 END IF;
1392 FORALL x IN l_line_dtl_index_tbl.FIRST..l_line_dtl_index_tbl.LAST
1393 UPDATE qp_int_ldets
1394 SET process_code = l_process_code_tbl(x)
1395 , price_adjustment_id = l_price_adj_id_tbl(x)
1396 , pricing_status_text = l_pricing_sts_text_tbl(x)
1397 WHERE line_index = l_line_index_tbl(x)
1398 and line_detail_index = l_line_dtl_index_tbl(x);
1399 END IF;
1400 --ADDED BY YANGLI FOR JAVA ENGINE PUB 3086881
1401 END IF;
1402 /*
1403 UPDATE qp_npreq_line_attrs_tmp lattr
1404 SET lattr.pricing_status_code = QP_PREQ_PUB.G_STATUS_UNCHANGED
1405 WHERE lattr.attribute_type = QP_PREQ_PUB.G_PRICING_TYPE
1406 and EXISTS ( SELECT 'X' FROM
1407 qp_npreq_lines_tmp line,
1408 qp_npreq_ldets_tmp ldet,
1409 oe_price_adjustments adj,
1410 oe_price_adj_attribs_v attr
1411 WHERE line.price_flag in
1412 (QP_PREQ_PUB.G_YES, QP_PREQ_PUB.G_PHASE)
1413 and line.line_type_code = QP_PREQ_PUB.G_LINE_LEVEL
1414 and line.pricing_status_code in
1415 (QP_PREQ_PUB.G_STATUS_UPDATED,
1416 QP_PREQ_PUB.G_STATUS_UNCHANGED,
1417 QP_PREQ_PUB.G_STATUS_GSA_VIOLATION)
1418 and ldet.line_index = line.line_index
1419 and ldet.pricing_status_code = QP_PREQ_PUB.G_STATUS_NEW
1420 and adj.line_id = line.line_id
1421 and attr.price_adjustment_id = adj.price_adjustment_id
1422 and attr.pricing_context = lattr.context
1423 and attr.pricing_attribute = lattr.attribute
1424 and attr.pricing_attr_value_from = lattr.value_from
1425 UNION
1426 SELECT 'X' FROM
1427 qp_npreq_lines_tmp line,
1428 qp_npreq_ldets_tmp ldet,
1432 (QP_PREQ_PUB.G_YES, QP_PREQ_PUB.G_PHASE)
1429 oe_price_adjustments adj,
1430 oe_price_adj_attribs_v attr
1431 WHERE line.price_flag in
1433 and line.line_type_code = QP_PREQ_PUB.G_ORDER_LEVEL
1434 and line.pricing_status_code in
1435 (QP_PREQ_PUB.G_STATUS_UPDATED,
1436 QP_PREQ_PUB.G_STATUS_UNCHANGED,
1437 QP_PREQ_PUB.G_STATUS_GSA_VIOLATION)
1438 and ldet.line_index = line.line_index
1439 and ldet.pricing_status_code = QP_PREQ_PUB.G_STATUS_NEW
1440 and adj.header_id = line.line_id
1441 and adj.line_id is null
1442 and attr.price_adjustment_id = adj.price_adjustment_id
1443 and attr.pricing_context = lattr.context
1444 and attr.pricing_attribute = lattr.attribute
1445 and attr.pricing_attr_value_from = lattr.value_from);
1446
1447
1448 IF QP_PREQ_GRP.G_DEBUG_ENGINE = FND_API.G_TRUE
1449 THEN
1450 for cl in l_attr_cur
1451 loop
1452 qp_preq_grp.engine_debug('attr details '
1453 ||cl.line_index||' dtl index '||cl.line_detail_index
1454 ||' sts code '||cl.pricing_status_code||' context '
1455 ||cl.context||' attr '||cl.attribute
1456 ||' val from '||cl.value_from);
1457 end loop;
1458 END IF;
1459 */
1460
1461
1462
1463 END IF;
1464 IF l_debug = FND_API.G_TRUE THEN
1465 QP_PREQ_GRP.engine_debug('Completed QP_CLEANUP.cleanup_adjustments');
1466 END IF;
1467 x_return_status := FND_API.G_RET_STS_SUCCESS;
1468
1469 EXCEPTION
1470 When OTHERS Then
1471 IF l_debug = FND_API.G_TRUE THEN
1472 QP_PREQ_GRP.engine_debug('error in QP_CLEANUP_ADJ.cleanup_adjustments '||SQLERRM);
1473 END IF;
1474 x_return_status := FND_API.G_RET_STS_ERROR;
1475 --x_return_status_text := 'error QP_CLEANUP_ADJ.cleanup_adjustments '||SQLERRM';
1476
1477 END cleanup_adjustments;
1478
1479 FUNCTION Get_line_detail_index(p_line_index IN NUMBER,
1480 p_price_adj_id IN NUMBER)
1481 RETURN NUMBER IS
1482 BEGIN
1483 l_debug := QP_PREQ_GRP.G_DEBUG_ENGINE;
1484 IF G_PBH_PLSQL_IND.EXISTS(p_price_adj_id) THEN
1485 IF l_debug = FND_API.G_TRUE THEN
1486 QP_PREQ_GRP.engine_debug('begin get loop ');
1487 END IF;--l_debug
1488 FOR i IN G_PBH_LINE_DTL_INDEX.FIRST..G_PBH_LINE_DTL_INDEX.LAST
1489 LOOP
1490 IF l_debug = FND_API.G_TRUE THEN
1491 QP_PREQ_GRP.engine_debug('in get loop '||G_PBH_LINE_INDEX(i)||' '
1492 ||G_PBH_PRICE_ADJ_ID(i));
1493 END IF;--l_debug
1494 IF G_PBH_LINE_INDEX(i) = p_line_index
1495 and G_PBH_PRICE_ADJ_ID(i) = p_price_adj_id THEN
1496 Return G_PBH_LINE_DTL_INDEX(i);
1497 END IF;
1498 END LOOP;
1499 END IF;--G_PBH_PLSQL_IND.EXISTS
1500
1501 G_MAX_DTL_INDEX := G_MAX_DTL_INDEX + 1;
1502 Return G_MAX_DTL_INDEX;
1503
1504 EXCEPTION
1505 When OTHERS THEN
1506 G_MAX_DTL_INDEX := G_MAX_DTL_INDEX + 1;
1507 Return G_MAX_DTL_INDEX;
1508 END Get_line_detail_index;
1509
1510 PROCEDURE Insert_Rltd_Lines(p_request_type_code IN VARCHAR2,
1511 p_calculate_flag IN VARCHAR2,
1512 p_event_code IN VARCHAR2,
1513 x_return_status OUT NOCOPY VARCHAR2,
1514 x_return_status_text OUT NOCOPY VARCHAR2) IS
1515
1516 CURSOR l_rltd_line_info_cur IS
1517 SELECT /*+ ORDERED USE_NL(adj_pbh ass adj attr) */
1518 /*index(ass OE_PRICE_ADJ_ASSOCS_N3)*/
1519 line.line_index line_index,
1520 adj_pbh.price_adjustment_id line_detail_index,
1521 line.line_index related_line_index,
1522 ass.rltd_price_adj_id related_line_detail_index,
1523 adj_pbh.list_line_id list_line_id,
1524 adj.list_line_id related_list_line_id,
1525 adj.list_line_type_code related_list_line_type,
1526 adj.arithmetic_operator,
1527 nvl(adj.operand_per_pqty, adj.operand) operand,
1528 adj_pbh.pricing_group_sequence,
1529 adj_pbh.price_break_type_code,
1530 adj_pbh.modifier_level_code,
1531 attr.pricing_attr_value_from setup_value_from,
1532 attr.pricing_attr_value_to setup_value_to,
1533 adj_pbh.range_break_quantity,
1534 --added these columns to insert child break lines for bug 3314259
1535 adj.pricing_phase_id,
1536 adj.automatic_flag,
1537 adj.applied_flag,
1538 adj.updated_flag,
1539 attr.list_header_id,
1540 adj.list_line_no
1541 FROM qp_npreq_lines_tmp line
1542 ,oe_price_adjustments adj_pbh
1543 ,oe_price_adj_assocs ass
1544 ,oe_price_adjustments adj
1545 , qp_pricing_attributes attr
1546 -- WHERE p_request_type_code = 'ONT'
1547 --bug 3085453 handle pricing availability UI
1548 -- they pass reqtype ONT and insert adj into ldets
1549 WHERE QP_PREQ_PUB.G_CHECK_CUST_VIEW_FLAG = QP_PREQ_PUB.G_YES
1550 and line.line_type_code = QP_PREQ_PUB.G_LINE_LEVEL
1551 and line.price_flag in (QP_PREQ_PUB.G_YES, QP_PREQ_PUB.G_PHASE)
1552 and line.line_id = adj_pbh.line_id
1553 and adj_pbh.list_line_type_code = QP_PREQ_PUB.G_PRICE_BREAK_TYPE
1554 and (p_calculate_flag = QP_PREQ_PUB.G_CALCULATE_ONLY
1555 or line.price_flag = QP_PREQ_PUB.G_CALCULATE_ONLY
1556 or adj_pbh.updated_flag = QP_PREQ_PUB.G_YES
1557 or p_event_code = ',' --we pad comma when it is null
1558 or adj_pbh.pricing_phase_id not in
1559 (select ph.pricing_phase_id
1560 from qp_event_phases evt , qp_pricing_phases ph
1561 where ph.pricing_phase_id = evt.pricing_phase_id
1562 --introduced for freight_rating functionality to return only modifiers in
1563 --phases where freight_exist = 'Y' if G_GET_FREIGHT_FLAG = 'Y'
1564 and ((QP_PREQ_PUB.G_GET_FREIGHT_FLAG = QP_PREQ_PUB.G_YES
1565 and ph.freight_exists = QP_PREQ_PUB.G_YES)
1569 or (nvl(QP_PREQ_PUB.Get_buy_line_price_flag(adj.list_line_id,line.line_index),line.price_flag) = QP_PREQ_PUB.G_PHASE
1566 or (QP_PREQ_PUB.G_GET_FREIGHT_FLAG = QP_PREQ_PUB.G_NO))
1567 and instr(p_event_code,evt.pricing_event_code||',') > 0
1568 and (nvl(QP_PREQ_PUB.Get_buy_line_price_flag(adj.list_line_id, line.line_index),line.price_flag) = QP_PREQ_PUB.G_YES
1570 and nvl(ph.user_freeze_override_flag,ph.freeze_override_flag) =
1571 QP_PREQ_PUB.G_YES))))
1572 and adj_pbh.price_adjustment_id = ass.price_adjustment_id
1573 and ass.rltd_price_adj_id = adj.price_adjustment_id
1574 and attr.list_line_id = adj.list_line_id
1575 and attr.pricing_attribute_context = QP_PREQ_PUB.G_PRIC_VOLUME_CONTEXT
1576 UNION
1577 SELECT /*+ ORDERED USE_NL(adj_pbh ass adj attr) */
1578 --/*+ index(ass OE_PRICE_ADJ_ASSOCS_N3)*/
1579 -- (adj_pbh.quote_header_id+nvl(adj_pbh.quote_line_id,0)) line_index,
1580 line.line_index,
1581 adj_pbh.line_detail_index line_detail_index,
1582 -- adj.quote_line_id related_line_index,
1583 line.line_index related_line_index,
1584 ass.related_line_detail_index related_line_detail_index,
1585 adj_pbh.created_from_list_line_id list_line_id,
1586 adj.created_from_list_line_id related_list_line_id,
1587 adj.created_from_list_line_type related_list_line_type,
1588 adj.operand_calculation_code arithmetic_operator,
1589 adj.operand_value operand,
1590 adj_pbh.pricing_group_sequence,
1591 adj_pbh.price_break_type_code,
1592 adj_pbh.modifier_level_code,
1593 attr.pricing_attr_value_from setup_value_from,
1594 attr.pricing_attr_value_to setup_value_to,
1595 adj_pbh.line_quantity range_break_quantity,
1596 --added these columns to insert child break lines for bug 3314259
1597 adj.pricing_phase_id,
1598 adj.automatic_flag,
1599 adj.applied_flag,
1600 adj.updated_flag,
1601 attr.list_header_id,
1602 adj.list_line_no
1603 FROM qp_npreq_lines_tmp line
1604 ,qp_npreq_ldets_tmp adj_pbh
1605 ,qp_npreq_rltd_lines_tmp ass
1606 ,qp_npreq_ldets_tmp adj
1607 , qp_pricing_attributes attr
1608 -- WHERE p_request_type_code <> 'ONT'
1609 --bug 3085453 handle pricing availability UI
1610 -- they pass reqtype ONT and insert adj into ldets
1611 WHERE QP_PREQ_PUB.G_CHECK_CUST_VIEW_FLAG <> QP_PREQ_PUB.G_YES
1612 and line.line_type_code = QP_PREQ_PUB.G_LINE_LEVEL
1613 and line.price_flag in (QP_PREQ_PUB.G_YES, QP_PREQ_PUB.G_PHASE)
1614 and line.line_index = adj_pbh.line_index
1615 and adj_pbh.pricing_status_code = QP_PREQ_PUB.G_STATUS_UNCHANGED
1616 and adj_pbh.created_from_list_line_type = QP_PREQ_PUB.G_PRICE_BREAK_TYPE
1617 and (p_calculate_flag = QP_PREQ_PUB.G_CALCULATE_ONLY
1618 or line.price_flag = QP_PREQ_PUB.G_CALCULATE_ONLY
1619 or adj_pbh.updated_flag = QP_PREQ_PUB.G_YES
1620 or p_event_code = ',' --we pad comma when it is null
1621 or adj_pbh.pricing_phase_id not in
1622 (select ph.pricing_phase_id
1623 from qp_event_phases evt, qp_pricing_phases ph
1624 where ph.pricing_phase_id = evt.pricing_phase_id
1625 --introduced for freight_rating functionality to return only modifiers in
1626 --phases where freight_exist = 'Y' if G_GET_FREIGHT_FLAG = 'Y'
1627 and ((QP_PREQ_PUB.G_GET_FREIGHT_FLAG = QP_PREQ_PUB.G_YES
1628 and ph.freight_exists = QP_PREQ_PUB.G_YES)
1629 or (QP_PREQ_PUB.G_GET_FREIGHT_FLAG = QP_PREQ_PUB.G_NO))
1630 and instr(p_event_code,evt.pricing_event_code||',') > 0
1631 and (nvl(QP_PREQ_PUB.Get_buy_line_price_flag(adj.created_from_list_line_id, line.line_index),line.price_flag) = QP_PREQ_PUB.G_YES
1632 or (nvl(QP_PREQ_PUB.Get_buy_line_price_flag(adj.created_from_list_line_id, line.line_index),line.price_flag) = QP_PREQ_PUB.G_PHASE
1633 and nvl(ph.user_freeze_override_flag,ph.freeze_override_flag) =
1634 QP_PREQ_PUB.G_YES))))
1635 and ass.line_detail_index = adj_pbh.line_detail_index
1636 and adj.line_detail_index = ass.related_line_detail_index
1637 and attr.list_line_id = adj.created_from_list_line_id
1638 and attr.pricing_attribute_context = QP_PREQ_PUB.G_PRIC_VOLUME_CONTEXT;
1639
1640
1641 CURSOR l_pbh_adj_exists_cur IS
1642 SELECT /*+ index(adj OE_PRICE_ADJUSTMENTS_N2) */ 'Y'
1643 FROM
1644 qp_npreq_lines_tmp line,
1645 oe_price_adjustments adj
1646 -- WHERE p_request_type_code = 'ONT'
1647 --bug 3085453 handle pricing availability UI
1648 -- they pass reqtype ONT and insert adj into ldets
1649 WHERE QP_PREQ_PUB.G_CHECK_CUST_VIEW_FLAG = QP_PREQ_PUB.G_YES
1650 and adj.line_id = line.line_id
1651 and line.line_type_code = QP_PREQ_PUB.G_LINE_LEVEL
1652 and adj.list_line_type_code = QP_PREQ_PUB.G_PRICE_BREAK_TYPE
1653 and (p_calculate_flag = QP_PREQ_PUB.G_CALCULATE_ONLY
1654 or line.price_flag = QP_PREQ_PUB.G_CALCULATE_ONLY
1655 or adj.updated_flag = QP_PREQ_PUB.G_YES
1656 or p_event_code = ',' -- we pad ',' when it is null
1657 or adj.pricing_phase_id not in
1658 (select ph.pricing_phase_id
1659 from qp_event_phases evt, qp_pricing_phases ph
1660 where ph.pricing_phase_id = evt.pricing_phase_id
1661 --introduced for freight_rating functionality to return only modifiers in
1662 --phases where freight_exist = 'Y' if G_GET_FREIGHT_FLAG = 'Y'
1663 and ((QP_PREQ_PUB.G_GET_FREIGHT_FLAG = QP_PREQ_PUB.G_YES
1664 and ph.freight_exists = QP_PREQ_PUB.G_YES)
1665 or (QP_PREQ_PUB.G_GET_FREIGHT_FLAG = QP_PREQ_PUB.G_NO))
1669 and nvl(ph.user_freeze_override_flag,ph.freeze_override_flag) =
1666 and instr(p_event_code,evt.pricing_event_code||',') > 0
1667 and (nvl(QP_PREQ_PUB.Get_buy_line_price_flag(adj.list_line_id, line.line_index),line.price_flag) = QP_PREQ_PUB.G_YES
1668 or (nvl(QP_PREQ_PUB.Get_buy_line_price_flag(adj.list_line_id, line.line_index),line.price_flag) = QP_PREQ_PUB.G_PHASE
1670 QP_PREQ_PUB.G_YES))))
1671 UNION
1672 SELECT /*+ index(adj OE_PRICE_ADJUSTMENTS_N1) */ 'Y'
1673 FROM
1674 qp_npreq_lines_tmp line,
1675 oe_price_adjustments adj
1676 -- WHERE p_request_type_code = 'ONT'
1677 --bug 3085453 handle pricing availability UI
1678 -- they pass reqtype ONT and insert adj into ldets
1679 WHERE QP_PREQ_PUB.G_CHECK_CUST_VIEW_FLAG = QP_PREQ_PUB.G_YES
1680 and adj.header_id = line.line_id
1681 and line.line_type_code = QP_PREQ_PUB.G_ORDER_LEVEL
1682 and adj.list_line_type_code = QP_PREQ_PUB.G_PRICE_BREAK_TYPE
1683 and (p_calculate_flag = QP_PREQ_PUB.G_CALCULATE_ONLY
1684 or line.price_flag = QP_PREQ_PUB.G_CALCULATE_ONLY
1685 or p_event_code = ',' -- we pad ',' when it is null
1686 or adj.pricing_phase_id not in
1687 (select ph.pricing_phase_id
1688 from qp_event_phases evt, qp_pricing_phases ph
1689 where ph.pricing_phase_id = evt.pricing_phase_id
1690 --introduced for freight_rating functionality to return only modifiers in
1691 --phases where freight_exist = 'Y' if G_GET_FREIGHT_FLAG = 'Y'
1692 and ((QP_PREQ_PUB.G_GET_FREIGHT_FLAG = QP_PREQ_PUB.G_YES
1693 and ph.freight_exists = QP_PREQ_PUB.G_YES)
1694 or (QP_PREQ_PUB.G_GET_FREIGHT_FLAG = QP_PREQ_PUB.G_NO))
1695 and instr(p_event_code,evt.pricing_event_code||',') > 0
1696 and (nvl(QP_PREQ_PUB.Get_buy_line_price_flag(adj.list_line_id, line.line_index),line.price_flag) = QP_PREQ_PUB.G_YES
1697 or (nvl(QP_PREQ_PUB.Get_buy_line_price_flag(adj.list_line_id, line.line_index),line.price_flag) = QP_PREQ_PUB.G_PHASE
1698 and nvl(ph.user_freeze_override_flag,ph.freeze_override_flag) =
1699 QP_PREQ_PUB.G_YES))))
1700 UNION
1701 SELECT 'Y'
1702 FROM
1703 qp_npreq_lines_tmp line,
1704 qp_npreq_ldets_tmp adj
1705 -- WHERE p_request_type_code <> 'ONT'
1706 --bug 3085453 handle pricing availability UI
1707 -- they pass reqtype ONT and insert adj into ldets
1708 WHERE QP_PREQ_PUB.G_CHECK_CUST_VIEW_FLAG <> QP_PREQ_PUB.G_YES
1709 and adj.line_index = line.line_index
1710 and adj.pricing_status_code = QP_PREQ_PUB.G_STATUS_UNCHANGED
1711 and adj.created_from_list_line_type = QP_PREQ_PUB.G_PRICE_BREAK_TYPE
1712 and (p_calculate_flag = QP_PREQ_PUB.G_CALCULATE_ONLY
1713 or line.price_flag = QP_PREQ_PUB.G_CALCULATE_ONLY
1714 or p_event_code = ',' -- we pad ',' when it is null
1715 or adj.pricing_phase_id not in
1716 (select ph.pricing_phase_id
1717 from qp_event_phases evt, qp_pricing_phases ph
1718 where ph.pricing_phase_id = evt.pricing_phase_id
1719 --introduced for freight_rating functionality to return only modifiers in
1720 --phases where freight_exist = 'Y' if G_GET_FREIGHT_FLAG = 'Y'
1721 and ((QP_PREQ_PUB.G_GET_FREIGHT_FLAG = QP_PREQ_PUB.G_YES
1722 and ph.freight_exists = QP_PREQ_PUB.G_YES)
1723 or (QP_PREQ_PUB.G_GET_FREIGHT_FLAG = QP_PREQ_PUB.G_NO))
1724 and instr(p_event_code,evt.pricing_event_code||',') > 0
1725 and (nvl(QP_PREQ_PUB.Get_buy_line_price_flag(adj.created_from_list_line_id, line.line_index),line.price_flag) = QP_PREQ_PUB.G_YES
1726 or (nvl(QP_PREQ_PUB.Get_buy_line_price_flag(adj.created_from_list_line_id, line.line_index),line.price_flag) = QP_PREQ_PUB.G_PHASE
1727 and nvl(ph.user_freeze_override_flag,ph.freeze_override_flag) = QP_PREQ_PUB.G_YES))));
1728
1729 l_line_index_tbl QP_PREQ_GRP.NUMBER_TYPE;
1730 l_line_dtl_index_tbl QP_PREQ_GRP.NUMBER_TYPE;
1731 l_rltd_line_index_tbl QP_PREQ_GRP.NUMBER_TYPE;
1732 l_rltd_line_dtl_index_tbl QP_PREQ_GRP.NUMBER_TYPE;
1733 l_list_line_id_tbl QP_PREQ_GRP.NUMBER_TYPE;
1734 l_rltd_list_line_id_tbl QP_PREQ_GRP.NUMBER_TYPE;
1735 l_list_line_type_code_tbl QP_PREQ_GRP.VARCHAR_TYPE;
1736 l_operand_calc_code_tbl QP_PREQ_GRP.VARCHAR_TYPE;
1737 l_operand_tbl QP_PREQ_GRP.NUMBER_TYPE;
1738 l_pricing_group_seq_tbl QP_PREQ_GRP.NUMBER_TYPE;
1739 l_price_brk_type_code_tbl QP_PREQ_GRP.VARCHAR_TYPE;
1740 l_setup_value_from_tbl QP_PREQ_GRP.NUMBER_TYPE;
1741 l_setup_value_to_tbl QP_PREQ_GRP.NUMBER_TYPE;
1742 l_qualifier_value_tbl QP_PREQ_GRP.NUMBER_TYPE;
1743 --added these columns to insert child break lines for bug 3314259
1744 l_mod_level_code_tbl QP_PREQ_GRP.VARCHAR_TYPE;
1745 l_pricing_phase_id_tbl QP_PREQ_GRP.NUMBER_TYPE;
1746 l_auto_flag_tbl QP_PREQ_GRP.FLAG_TYPE;
1747 l_applied_flag_tbl QP_PREQ_GRP.FLAG_TYPE;
1748 l_updated_flag_tbl QP_PREQ_GRP.FLAG_TYPE;
1749 l_list_hdr_id_tbl QP_PREQ_GRP.NUMBER_TYPE;
1750 l_list_line_no_tbl QP_PREQ_GRP.VARCHAR_TYPE;
1751
1752 l_pbh_adj_exists VARCHAR2(1) := QP_PREQ_PUB.G_NO;
1753 N pls_integer;
1754 BEGIN
1755 l_debug := QP_PREQ_GRP.G_DEBUG_ENGINE;
1756 IF l_debug = FND_API.G_TRUE THEN
1757 QP_PREQ_GRP.engine_debug('Begin Insert_rltd_lines rqtyp '||p_request_type_code);
1758 QP_PREQ_GRP.engine_debug('Begin Insert_rltd_lines calcflag '||p_calculate_flag);
1759 QP_PREQ_GRP.engine_debug('Begin Insert_rltd_lines event '||p_event_code);
1760
1761 END IF;
1762 OPEN l_pbh_adj_exists_cur;
1763 FETCH l_pbh_adj_exists_cur INTO l_pbh_adj_exists;
1767 QP_PREQ_GRP.engine_debug('Check l_pbh_adj_exists '||l_pbh_adj_exists);
1764 CLOSE l_pbh_adj_exists_cur;
1765
1766 IF l_debug = FND_API.G_TRUE THEN
1768
1769 END IF;
1770 N := 0;
1771
1772 IF l_pbh_adj_exists = QP_PREQ_PUB.G_YES
1773 THEN
1774 l_line_index_tbl.delete;
1775 l_line_dtl_index_tbl.delete;
1776 l_rltd_line_index_tbl.delete;
1777 l_rltd_line_dtl_index_tbl.delete;
1778 l_list_line_id_tbl.delete;
1779 l_rltd_list_line_id_tbl.delete;
1780 l_list_line_type_code_tbl.delete;
1781 l_operand_calc_code_tbl.delete;
1782 l_operand_tbl.delete;
1783 l_pricing_group_seq_tbl.delete;
1784 l_price_brk_type_code_tbl.delete;
1785 l_setup_value_from_tbl.delete;
1786 l_setup_value_to_tbl.delete;
1787 l_qualifier_value_tbl.delete;
1788 --added these columns to insert child break lines for bug 3314259
1789 l_mod_level_code_tbl.delete;
1790 l_pricing_phase_id_tbl.delete;
1791 l_auto_flag_tbl.delete;
1792 l_applied_flag_tbl.delete;
1793 l_updated_flag_tbl.delete;
1794 l_list_hdr_id_tbl.delete;
1795 l_list_line_no_tbl.delete;
1796
1797 FOR I IN l_rltd_line_info_cur
1798 LOOP
1799 IF l_debug = FND_API.G_TRUE THEN
1800 QP_PREQ_GRP.engine_debug('In l_rltd_line_info_cur');
1801 END IF;
1802 N := N+1;
1803 l_line_index_tbl(N):=I.line_index;
1804 l_line_dtl_index_tbl(N) :=
1805 Get_line_detail_index(I.line_index, I.line_detail_index);
1806 l_rltd_line_index_tbl(N):=I.related_line_index;
1807
1808 l_rltd_line_dtl_index_tbl(N):=
1809 Get_line_detail_index(I.related_line_index, I.related_line_detail_index);
1810 IF l_debug = FND_API.G_TRUE THEN
1811 QP_PREQ_GRP.engine_debug('Pbh dtl_index '
1812 ||l_line_dtl_index_tbl(N)
1813 ||' rltd_dtl_index '
1814 ||l_rltd_line_dtl_index_tbl(N));
1815 END IF;--l_debug
1816 l_list_line_id_tbl(N):=I.list_line_id;
1817 l_rltd_list_line_id_tbl(N):=I.related_list_line_id;
1818 l_list_line_type_code_tbl(N):=I.related_list_line_type;
1819 l_operand_calc_code_tbl(N):=I.arithmetic_operator;
1820 l_operand_tbl(N):=I.operand;
1821 l_pricing_group_seq_tbl(N):=I.pricing_group_sequence;
1822 l_price_brk_type_code_tbl(N):=I.price_break_type_code;
1823 l_setup_value_from_tbl(N):=
1824 qp_number.canonical_to_number(I.setup_value_from);
1825 l_setup_value_to_tbl(N):=
1826 qp_number.canonical_to_number(I.setup_value_to);
1827 l_qualifier_value_tbl(N):=I.range_break_quantity;
1828 --added these columns to insert child break lines for bug 3314259
1829 l_mod_level_code_tbl(N) := I.modifier_level_code;
1830 l_pricing_phase_id_tbl(N) := I.pricing_phase_id;
1831 l_auto_flag_tbl(N) := I.automatic_flag;
1832 l_applied_flag_tbl(N) := I.applied_flag;
1833 l_updated_flag_tbl(N) := I.updated_flag;
1834 l_list_hdr_id_tbl(N) := I.list_header_id;
1835 l_list_line_no_tbl(N) := I.list_line_no;
1836
1837 IF l_debug = FND_API.G_TRUE THEN
1838 QP_PREQ_GRP.engine_debug('price brk child details '
1839 ||l_line_dtl_index_tbl(N)||' rltd_dtl '
1840 ||l_rltd_line_dtl_index_tbl(N)||' bucket '
1841 ||l_pricing_group_seq_tbl(N)||' '
1842 ||' parent id '||l_list_line_id_tbl(N)
1843 ||' child id '||l_rltd_list_line_id_tbl(N)
1844 ||' operand '||l_operand_tbl(N)||' '
1845 ||l_operand_calc_code_tbl(N)||' brk type '
1846 ||l_price_brk_type_code_tbl(N)||' value from '
1847 ||l_setup_value_from_tbl(N)||' value to '
1848 ||l_setup_value_to_tbl(N)||' qualifier value '
1849 ||l_qualifier_value_tbl(N));
1850 END IF;
1851 END LOOP;
1852
1853 IF l_debug = FND_API.G_TRUE THEN
1854 QP_PREQ_GRP.engine_debug('price brk after loop ');
1855 END IF;
1856 IF l_line_dtl_index_tbl.COUNT > 0
1857 THEN
1858 IF l_debug = FND_API.G_TRUE THEN
1859 QP_PREQ_GRP.engine_debug('price brk before insert ');
1860 END IF;
1861 FORALL I IN l_line_dtl_index_tbl.FIRST..l_line_dtl_index_tbl.LAST
1862 INSERT INTO qp_npreq_rltd_lines_tmp
1863 (
1864 pricing_status_text,
1865 line_index,
1866 line_detail_index,
1867 relationship_type_code,
1868 related_line_index,
1869 related_line_detail_index,
1870 pricing_status_code,
1871 list_line_id,
1872 related_list_line_id,
1873 related_list_line_type,
1874 operand_calculation_code,
1875 operand,
1876 pricing_group_sequence,
1877 relationship_type_detail,
1878 setup_value_from,
1879 setup_value_to,
1880 qualifier_value
1881 )
1882 VALUES
1883 (
1884 G_CALC_INSERT,
1885 l_line_index_tbl(I),
1886 l_line_dtl_index_tbl(I),
1887 QP_PREQ_PUB.G_PBH_LINE,
1888 l_rltd_line_index_tbl(I),
1889 l_rltd_line_dtl_index_tbl(I),
1890 QP_PREQ_PUB.G_STATUS_NEW,
1891 l_list_line_id_tbl(I),
1892 l_rltd_list_line_id_tbl(I),
1893 l_list_line_type_code_tbl(I),
1894 l_operand_calc_code_tbl(I),
1895 l_operand_tbl(I),
1896 l_pricing_group_seq_tbl(I),
1897 l_price_brk_type_code_tbl(I),
1898 l_setup_value_from_tbl(I),
1899 l_setup_value_to_tbl(I),
1900 l_qualifier_value_tbl(I)
1901 );
1902
1903 --added this insert for bug 3314259 to insert child break lines to tmp table
1907 line_detail_index
1904 FORALL I IN l_line_dtl_index_tbl.FIRST..l_line_dtl_index_tbl.LAST
1905 INSERT INTO qp_npreq_ldets_tmp
1906 (
1908 ,line_index
1909 ,line_detail_type_code
1910 ,pricing_status_code
1911 ,pricing_status_text
1912 ,process_code
1913 ,created_from_list_header_id
1914 ,created_from_list_line_id
1915 ,created_from_list_line_type
1916 ,adjustment_amount
1917 ,operand_value
1918 ,modifier_level_code
1919 ,price_break_type_code
1920 -- ,line_quantity
1921 ,operand_calculation_code
1922 ,pricing_group_sequence
1923 -- ,created_from_list_type_code
1924 ,applied_flag
1925 -- ,limit_code
1926 -- ,limit_text
1927 ,list_line_no
1928 -- ,charge_type_code
1929 -- ,charge_subtype_code
1930 ,updated_flag
1931 ,automatic_flag
1932 ,pricing_phase_id)
1933 values
1934 (l_rltd_line_dtl_index_tbl(I)
1935 ,l_rltd_line_index_tbl(I)
1936 ,QP_PREQ_GRP.G_CHILD_DETAIL_TYPE -- bug 3487840, pass QP_PREQ_GRP.G_CHILD_DETAIL_TYPE instead of null
1937 ,QP_PREQ_PUB.G_STATUS_NEW
1938 ,''
1939 ,QP_PREQ_PUB.G_STATUS_NEW
1940 ,l_list_hdr_id_tbl(I) --put list_header_id
1941 ,l_rltd_list_line_id_tbl(I)
1942 ,l_list_line_type_code_tbl(I)
1943 ,0
1944 ,l_operand_tbl(I)
1945 ,l_mod_level_code_tbl(I) --modifier_level_code
1946 ,l_price_brk_type_code_tbl(I)
1947 ,l_operand_calc_code_tbl(I)
1948 ,l_pricing_group_seq_tbl(I)
1949 ,l_applied_flag_tbl(I) --QP_PREQ_PUB.G_YES
1950 ,l_list_line_no_tbl(I)
1951 ,l_updated_flag_tbl(I) --QP_PREQ_PUB.G_YES
1952 ,l_auto_flag_tbl(I) --QP_PREQ_PUB.G_NO
1953 ,l_pricing_phase_id_tbl(I));
1954
1955 IF l_debug = FND_API.G_TRUE THEN
1956 QP_PREQ_GRP.engine_debug('price brk after insert ');
1957
1958 END IF;
1959 END IF;
1960 END IF;
1961 IF l_debug = FND_API.G_TRUE THEN
1962 QP_PREQ_GRP.engine_debug('End Insert_rltd_lines');
1963 END IF;
1964 x_return_status := FND_API.G_RET_STS_SUCCESS;
1965 EXCEPTION
1966 When OTHERS Then
1967 IF l_debug = FND_API.G_TRUE THEN
1968 QP_PREQ_GRP.engine_debug('Error Insert_rltd_lines'||SQLERRM);
1969 END IF;
1970 x_return_status := FND_API.G_RET_STS_ERROR;
1971 x_return_status_text := 'Error in QP_CLEANUP_ADJUSTMENTS.Insert_Rltd_Lines '||SQLERRM;
1972 END Insert_Rltd_Lines;
1973
1974
1975
1976 PROCEDURE fetch_adjustments(p_view_code IN VARCHAR2,
1977 p_event_code IN VARCHAR2,
1978 p_calculate_flag IN VARCHAR2,
1979 p_rounding_flag IN VARCHAR2,
1980 p_request_type_code IN VARCHAR2,
1981 x_return_status OUT NOCOPY VARCHAR2,
1982 x_return_status_text OUT NOCOPY VARCHAR2) IS
1983
1984 --changed to make sure lumpsum on order level frt charge divide by 1 quantity
1985 CURSOR l_calculate_cur IS
1986 select /*+ ORDERED USE_NL(adj qplh) */
1987 adj.list_line_id created_from_list_line_id
1988 , line.line_index line_ind
1989 , line.line_index curr_line_index
1990 , line.line_id line_id
1991 , adj.price_adjustment_id line_detail_index
1992 , adj.list_line_type_code created_from_list_line_type
1993 , adj.list_header_id created_from_list_header_id
1994 , adj.applied_flag
1995 , (line.updated_adjusted_unit_price
1996 - line.adjusted_unit_price) amount_changed
1997 , line.adjusted_unit_price
1998 , adj.range_break_quantity priced_quantity
1999 , line.priced_quantity line_priced_quantity
2000 , line.updated_adjusted_unit_price
2001 , adj.automatic_flag
2002 , adj.update_allowed override_flag
2003 , adj.pricing_group_sequence
2004 , adj.arithmetic_operator operand_calculation_code
2005 , nvl(adj.operand_per_pqty,adj.operand) operand_value
2006 , nvl(adj.adjusted_amount_per_pqty,
2007 adj.adjusted_amount) adjustment_amount -- 4757680
2008 , line.unit_price
2009 , adj.accrual_flag
2010 , nvl(adj.updated_flag, QP_PREQ_PUB.G_NO)
2011 , 'N' process_code
2012 , 'N' pricing_status_code
2013 , ' ' pricing_status_text
2014 , adj.price_break_type_code
2015 , adj.charge_type_code
2016 , adj.charge_subtype_code
2017 , line.rounding_factor
2018 , adj.pricing_phase_id
2019 , qplh.list_type_code created_from_list_type_code -- [4222237/4500246]
2020 , '' limit_code
2021 , '' limit_text
2022 , adj.list_line_no
2023 , adj.modifier_level_code
2024 , adj.range_break_quantity group_quantity
2025 , adj.range_break_quantity group_amount
2026 , line.pricing_status_code line_pricing_status_code
2027 , QP_PREQ_PUB.G_ADJ_LINE_TYPE is_ldet_rec
2028 , line.line_type_code
2029 , NULL net_amount_flag --bucketed_flag
2030 , NULL calculation_code
2031 , line.catchweight_qty
2032 , line.actual_order_quantity
2033 , line.line_unit_price
2034 , line.line_quantity ordered_qty
2035 , NULL line_detail_type_code
2036 , line.line_category
2037 , line.price_flag
2041 --bug 3085453 handle pricing availability UI
2038 from qp_npreq_lines_tmp line, oe_price_adjustments adj,
2039 qp_list_headers_b qplh
2040 -- where p_request_type_code = 'ONT'
2042 -- they pass reqtype ONT and insert adj into ldets
2043 where QP_PREQ_PUB.G_CHECK_CUST_VIEW_FLAG = QP_PREQ_PUB.G_YES
2044 -- bug# 2739322
2045 -- not needed as this condition will always be false having this was causing the bug where
2046 -- the adjustments against the freegood line are not getting selected
2047 -- also we would like to get the adjustments for the freegood line
2048 -- and nvl(line.processed_flag,'N') <> QP_PREQ_PUB.G_FREEGOOD_LINE -- Ravi
2049 and line.process_status in (QP_PREQ_PUB.G_STATUS_UPDATED,
2050 QP_PREQ_PUB.G_STATUS_NEW,
2051 QP_PREQ_PUB.G_STATUS_UNCHANGED,
2052 'NEW'||QP_PREQ_PUB.G_STATUS_UNCHANGED,
2053 --fix for bug 2823886 to do cleanup and calc for old fg line
2054 'OLD'||QP_PREQ_PUB.G_STATUS_UNCHANGED)
2055 and adj.line_id = line.line_id
2056 and line.pricing_status_code IN (QP_PREQ_PUB.G_STATUS_UPDATED
2057 ,QP_PREQ_PUB.G_STATUS_GSA_VIOLATION
2058 ,QP_PREQ_PUB.G_STATUS_UNCHANGED)
2059 and (line.price_flag in (QP_PREQ_PUB.G_YES, QP_PREQ_PUB.G_PHASE,QP_PREQ_PUB.G_CALCULATE_ONLY)
2060 OR nvl(line.processed_code,'0') = QP_PREQ_PUB.G_BY_ENGINE)
2061 and line.line_type_code = QP_PREQ_PUB.G_LINE_LEVEL
2062 and line.unit_price is not null -- bug 3501866, calculation to be done for line having unit price
2063 and (adj.updated_flag = QP_PREQ_PUB.G_YES
2064 or line.price_flag = QP_PREQ_PUB.G_CALCULATE_ONLY
2065 or (p_event_code is null and adj.updated_flag is null)
2066 or adj.pricing_phase_id not in (select ph.pricing_phase_id
2067 from qp_event_phases ev, qp_pricing_phases ph
2068 --changes to enable multiple events passed as a string
2069 where ph.pricing_phase_id = ev.pricing_phase_id
2070 ---introduced the end date condition for bug 3376902
2071 and (ev.end_date_active is null or (ev.end_date_active is not null and ev.end_date_active > line.pricing_effective_date))
2072 --introduced for freight_rating functionality to return only modifiers in
2073 --phases where freight_exist = 'Y' if G_GET_FREIGHT_FLAG = 'Y'
2074 and ((QP_PREQ_PUB.G_GET_FREIGHT_FLAG = QP_PREQ_PUB.G_YES
2075 and ph.freight_exists = QP_PREQ_PUB.G_YES)
2076 or (QP_PREQ_PUB.G_GET_FREIGHT_FLAG = QP_PREQ_PUB.G_NO))
2077 and instr(p_event_code,ev.pricing_event_code||',') > 0
2078 -- 3721860, pass list_line_id and line_index both for function Get_buy_line_price_flag
2079 and (nvl(QP_PREQ_PUB.Get_buy_line_price_flag(adj.list_line_id, line.line_index),line.price_flag) = QP_PREQ_PUB.G_YES
2080 or (nvl(QP_PREQ_PUB.Get_buy_line_price_flag(adj.list_line_id, line.line_index),line.price_flag) = QP_PREQ_PUB.G_PHASE
2081 and nvl(ph.user_freeze_override_flag,ph.freeze_override_flag) =
2082 QP_PREQ_PUB.G_YES))))
2083 and adj.modifier_level_code IN (QP_PREQ_PUB.G_LINE_LEVEL,QP_PREQ_PUB.G_LINE_GROUP)
2084 -- and adj.pricing_status_code IN (QP_PREQ_PUB.G_STATUS_NEW,QP_PREQ_PUB.G_STATUS_UPDATED)
2085 --commented to fetch auto overridden unapplied adj(user deleted)
2086 -- and adj.applied_flag = QP_PREQ_PUB.G_YES
2087 -- and adj.list_line_type_code IN (QP_PREQ_PUB.G_DISCOUNT
2088 -- , QP_PREQ_PUB.G_SURCHARGE , QP_PREQ_PUB.G_PRICE_BREAK_TYPE, QP_PREQ_PUB.G_FREIGHT_CHARGE)
2089 and qplh.list_header_id = adj.list_header_id
2090 and qplh.list_type_code not in (QP_PREQ_PUB.G_PRICE_LIST_HEADER,
2091 QP_PREQ_PUB.G_AGR_LIST_HEADER)
2092 and not exists (select 'x'
2093 from oe_price_adj_assocs a, oe_price_adjustments b
2094 where a.RLTD_PRICE_ADJ_ID = adj.price_adjustment_id
2095 and b.price_adjustment_id = a.price_adjustment_id
2096 and b.list_line_type_code = QP_PREQ_GRP.G_PRICE_BREAK_TYPE)
2097 UNION
2098 select /*+ ORDERED USE_NL(adj line qplh) index(adj OE_PRICE_ADJUSTMENTS_N1) dynamic_sampling(1) */ -- Bug No: 6753550
2099 adj.list_line_id created_from_list_line_id
2100 , line.line_index line_ind
2101 , line.line_index curr_line_index
2102 , line.line_id line_id
2103 , adj.price_adjustment_id line_detail_index
2104 , adj.list_line_type_code created_from_list_line_type
2105 , adj.list_header_id created_from_list_header_id
2106 , adj.applied_flag
2107 , (line.updated_adjusted_unit_price
2108 - line.adjusted_unit_price) amount_changed
2109 , line.adjusted_unit_price
2110 , adj.range_break_quantity priced_quantity
2111 , line.priced_quantity line_priced_quantity
2112 , line.updated_adjusted_unit_price
2113 , adj.automatic_flag
2114 , adj.update_allowed override_flag
2115 , adj.pricing_group_sequence
2116 , adj.arithmetic_operator operand_calculation_code
2117 , nvl(adj.operand_per_pqty, adj.operand) operand_value
2118 , adj.adjusted_amount_per_pqty adjustment_amount
2119 , line.unit_price
2120 , adj.accrual_flag
2121 , nvl(adj.updated_flag, QP_PREQ_PUB.G_NO)
2122 , 'N' process_code
2123 , 'N' pricing_status_code
2124 , ' ' pricing_status_text
2125 , adj.price_break_type_code
2126 , adj.charge_type_code
2127 , adj.charge_subtype_code
2128 , line.rounding_factor
2132 , '' limit_text
2129 , adj.pricing_phase_id
2130 , qplh.list_type_code created_from_list_type_code -- [4222237/4500246]
2131 , '' limit_code
2133 , adj.list_line_no
2134 , adj.modifier_level_code
2135 , adj.range_break_quantity group_quantity
2136 , adj.range_break_quantity group_amount
2137 , line.pricing_status_code line_pricing_status_code
2138 , QP_PREQ_PUB.G_ADJ_ORDER_TYPE is_ldet_rec
2139 , line.line_type_code
2140 , NULL net_amount_flag --bucketed_flag
2141 , NULL calculation_code
2142 , line.catchweight_qty
2143 , line.actual_order_quantity
2144 , line.line_unit_price
2145 , line.line_quantity ordered_qty
2146 , NULL line_detail_type_code
2147 , line.line_category
2148 , line.price_flag
2149 from qp_npreq_lines_tmp line1, oe_price_adjustments adj
2150 ,qp_npreq_lines_tmp line, qp_list_headers_b qplh
2151 -- where p_request_type_code = 'ONT'
2152 --bug 3085453 handle pricing availability UI
2153 -- they pass reqtype ONT and insert adj into ldets
2154 where QP_PREQ_PUB.G_CHECK_CUST_VIEW_FLAG = QP_PREQ_PUB.G_YES
2155 --and nvl(line.processed_flag,'N') <> QP_PREQ_PUB.G_FREEGOOD_LINE -- Ravi
2156 and line1.process_status in (QP_PREQ_PUB.G_STATUS_UPDATED,
2157 QP_PREQ_PUB.G_STATUS_NEW,
2158 QP_PREQ_PUB.G_STATUS_UNCHANGED,
2159 'NEW'||QP_PREQ_PUB.G_STATUS_UNCHANGED,
2160 --fix for bug 2823886 to do cleanup and calc for old fg line
2161 'OLD'||QP_PREQ_PUB.G_STATUS_UNCHANGED)
2162 and line.process_status in (QP_PREQ_PUB.G_STATUS_UPDATED,
2163 QP_PREQ_PUB.G_STATUS_NEW,
2164 QP_PREQ_PUB.G_STATUS_UNCHANGED,
2165 'NEW'||QP_PREQ_PUB.G_STATUS_UNCHANGED,
2166 --fix for bug 2823886 to do cleanup and calc for old fg line
2167 'OLD'||QP_PREQ_PUB.G_STATUS_UNCHANGED)
2168 and line1.line_id = adj.header_id
2169 and line1.pricing_status_code IN (QP_PREQ_PUB.G_STATUS_UPDATED
2170 ,QP_PREQ_PUB.G_STATUS_GSA_VIOLATION
2171 ,QP_PREQ_PUB.G_STATUS_UNCHANGED)
2172 and line1.line_type_code = QP_PREQ_PUB.G_ORDER_LEVEL
2173 and (line1.price_flag in (QP_PREQ_PUB.G_YES, QP_PREQ_PUB.G_PHASE, QP_PREQ_PUB.G_CALCULATE_ONLY)
2174 OR nvl(line1.processed_code,'0') = QP_PREQ_PUB.G_BY_ENGINE)
2175 and (line.price_flag in (QP_PREQ_PUB.G_YES, QP_PREQ_PUB.G_PHASE, QP_PREQ_PUB.G_CALCULATE_ONLY)
2176 OR nvl(line.processed_code,'0') = QP_PREQ_PUB.G_BY_ENGINE)
2177 and line.pricing_status_code IN (QP_PREQ_PUB.G_STATUS_UPDATED
2178 ,QP_PREQ_PUB.G_STATUS_GSA_VIOLATION
2179 ,QP_PREQ_PUB.G_STATUS_UNCHANGED)
2180 and (line.unit_price is not null or line.line_type_code = QP_PREQ_PUB.G_ORDER_LEVEL) -- bug 3501866
2181 and (adj.updated_flag = QP_PREQ_PUB.G_YES
2182 or line.price_flag = QP_PREQ_PUB.G_CALCULATE_ONLY
2183 or (p_event_code is null and adj.updated_flag is null)
2184 or adj.pricing_phase_id not in (select ph.pricing_phase_id
2185 from qp_event_phases ev, qp_pricing_phases ph
2186 --changes to enable multiple events passed as a string
2187 where ph.pricing_phase_id = ev.pricing_phase_id
2188 ---introduced the end date condition for bug 3376902
2189 and (ev.end_date_active is null or (ev.end_date_active is not null and ev.end_date_active > line.pricing_effective_date))
2190 --introduced for freight_rating functionality to return only modifiers in
2191 --phases where freight_exist = 'Y' if G_GET_FREIGHT_FLAG = 'Y'
2192 and ((QP_PREQ_PUB.G_GET_FREIGHT_FLAG = QP_PREQ_PUB.G_YES
2193 and ph.freight_exists = QP_PREQ_PUB.G_YES)
2194 or (QP_PREQ_PUB.G_GET_FREIGHT_FLAG = QP_PREQ_PUB.G_NO))
2195 and instr(p_event_code,ev.pricing_event_code||',') > 0
2196 -- Ravi changed from line.price_flag to line1.price_flag , bug# 2739322 where the order level discounts are not
2197 -- getting selected with each line, since line has price_flag = 'Y' and is in phase
2198 -- by making it line1.price_flag , the summary line's price_flag = 'P'(when there is a freegood line created with
2199 -- partial price) it works as though it is out of phase as price_flag = 'P' and freeze_override_flag = 'N' and the
2200 -- modifier is selected across all the lines
2201 -- 3721860, pass list_line_id and line_index both for function Get_buy_line_price_flag
2202 and (nvl(QP_PREQ_PUB.Get_buy_line_price_flag(adj.list_line_id, line1.line_index),line1.price_flag) = QP_PREQ_PUB.G_YES
2203 or (nvl(QP_PREQ_PUB.Get_buy_line_price_flag(adj.list_line_id, line1.line_index),line1.price_flag) = QP_PREQ_PUB.G_PHASE
2204 and nvl(ph.user_freeze_override_flag, ph.freeze_override_flag) = QP_PREQ_PUB.G_YES))))
2205 and adj.modifier_level_code = QP_PREQ_PUB.G_ORDER_LEVEL
2206 and adj.line_id is null
2207 -- and ldet.pricing_status_code IN (QP_PREQ_PUB.G_STATUS_NEW,QP_PREQ_PUB.G_STATUS_UPDATED)
2208 -- and ldet.applied_flag = QP_PREQ_PUB.G_YES
2209 -- and adj.list_line_type_code IN (QP_PREQ_PUB.G_DISCOUNT
2210 -- , QP_PREQ_PUB.G_SURCHARGE , QP_PREQ_PUB.G_PRICE_BREAK_TYPE, QP_PREQ_PUB.G_FREIGHT_CHARGE)
2211 and qplh.list_header_id = adj.list_header_id
2212 and qplh.list_type_code not in (QP_PREQ_PUB.G_PRICE_LIST_HEADER,
2213 QP_PREQ_PUB.G_AGR_LIST_HEADER)
2214 UNION
2215 select /*+ ORDERED USE_NL(ldet) index(ldet QP_PREQ_LDETS_TMP_N1) */
2216 ldet.created_from_list_line_id
2217 , line.line_index line_ind
2218 , line.line_index curr_line_index
2219 , line.line_id line_id
2220 , ldet.line_detail_index
2221 , ldet.created_from_list_line_type
2225 - line.adjusted_unit_price) amount_changed
2222 , ldet.created_from_list_header_id
2223 , ldet.applied_flag
2224 , (line.updated_adjusted_unit_price
2226 , line.adjusted_unit_price
2227 , ldet.line_quantity priced_quantity
2228 , line.priced_quantity line_priced_quantity
2229 , line.updated_adjusted_unit_price
2230 , ldet.automatic_flag
2231 , ldet.override_flag
2232 , ldet.pricing_group_sequence
2233 , ldet.operand_calculation_code
2234 , ldet.operand_value
2235 , ldet.adjustment_amount
2236 , line.unit_price
2237 , ldet.accrual_flag
2238 , nvl(ldet.updated_flag, QP_PREQ_PUB.G_NO)
2239 , ldet.process_code
2240 , ldet.pricing_status_code
2241 , ldet.pricing_status_text
2242 , ldet.price_break_type_code
2243 , ldet.charge_type_code
2244 , ldet.charge_subtype_code
2245 , line.rounding_factor
2246 , ldet.pricing_phase_id
2247 , ldet.created_from_list_type_code
2248 , ldet.limit_code
2249 , substr(ldet.limit_text,1,240)
2250 , ldet.list_line_no
2251 , ldet.modifier_level_code
2252 , ldet.group_quantity group_quantity
2253 , ldet.group_amount group_amount
2254 , line.pricing_status_code line_pricing_status_code
2255 , QP_PREQ_PUB.G_LDET_LINE_TYPE is_ldet_rec
2256 , line.line_type_code
2257 , ldet.net_amount_flag net_amount_flag --bucketed_flag
2258 , ldet.calculation_code calculation_code
2259 , line.catchweight_qty
2260 , line.actual_order_quantity
2261 , line.line_unit_price
2262 , line.line_quantity ordered_qty
2263 , ldet.line_detail_type_code line_detail_type_code
2264 , line.line_category
2265 , line.price_flag
2266 from qp_npreq_lines_tmp line, qp_npreq_ldets_tmp ldet
2267 where line.line_index = ldet.line_index
2268 --and nvl(line.processed_flag,'N') <> QP_PREQ_PUB.G_FREEGOOD_LINE -- Ravi
2269 and line.process_status in (QP_PREQ_PUB.G_STATUS_UPDATED,
2270 QP_PREQ_PUB.G_STATUS_NEW,
2271 QP_PREQ_PUB.G_STATUS_UNCHANGED,
2272 'NEW'||QP_PREQ_PUB.G_STATUS_UNCHANGED,
2273 --fix for bug 2823886 to do cleanup and calc for old fg line
2274 'OLD'||QP_PREQ_PUB.G_STATUS_UNCHANGED)
2275 and line.pricing_status_code IN (QP_PREQ_PUB.G_STATUS_UPDATED
2276 ,QP_PREQ_PUB.G_STATUS_GSA_VIOLATION
2277 ,QP_PREQ_PUB.G_STATUS_UNCHANGED)
2278 and (line.price_flag in (QP_PREQ_PUB.G_YES, QP_PREQ_PUB.G_PHASE)
2279 OR nvl(line.processed_code,'0') = QP_PREQ_PUB.G_BY_ENGINE)
2280 and line.line_type_code = QP_PREQ_PUB.G_LINE_LEVEL
2281 and line.unit_price is not null -- bug 3501866, calculation to be done for line having unit price
2282 and ldet.pricing_status_code IN (QP_PREQ_PUB.G_STATUS_NEW,QP_PREQ_PUB.G_STATUS_UPDATED)--,QP_PREQ_PUB.G_STATUS_UNCHANGED)
2283 --fix for frt charge issue to calculate adj amt for manual frt charge
2284 and (ldet.applied_flag = QP_PREQ_PUB.G_YES
2285 or (ldet.applied_flag = QP_PREQ_PUB.G_NO
2286 and ldet.created_from_list_line_type =
2287 QP_PREQ_PUB.G_FREIGHT_CHARGE
2288 and ldet.automatic_flag = QP_PREQ_PUB.G_NO))
2289 -- and ldet.created_from_list_line_type IN (QP_PREQ_PUB.G_DISCOUNT
2290 -- , QP_PREQ_PUB.G_SURCHARGE , QP_PREQ_PUB.G_PRICE_BREAK_TYPE, QP_PREQ_PUB.G_FREIGHT_CHARGE)
2291 and ldet.created_from_list_type_code not in
2292 (QP_PREQ_PUB.G_PRICE_LIST_HEADER, QP_PREQ_PUB.G_AGR_LIST_HEADER)
2293 and ldet.modifier_level_code IN (QP_PREQ_PUB.G_LINE_LEVEL,QP_PREQ_PUB.G_LINE_GROUP)
2294 and nvl(ldet.line_detail_type_code,'NULL') <> QP_PREQ_PUB.G_CHILD_DETAIL_TYPE
2295 UNION
2296 select /*+ ORDERED USE_NL(ldet line) index(ldet QP_PREQ_LDETS_TMP_N1) */
2297 ldet.created_from_list_line_id
2298 , line.line_index line_ind
2299 , line.line_index curr_line_index
2300 , line.line_id line_id
2301 , ldet.line_detail_index
2302 , ldet.created_from_list_line_type
2303 , ldet.created_from_list_header_id
2304 , ldet.applied_flag
2305 , (line.updated_adjusted_unit_price
2306 - line.adjusted_unit_price) amount_changed
2307 , line.adjusted_unit_price
2308 , ldet.line_quantity priced_quantity
2309 , line.priced_quantity line_priced_quantity
2310 , line.updated_adjusted_unit_price
2311 , ldet.automatic_flag
2312 , ldet.override_flag
2313 , ldet.pricing_group_sequence
2314 , ldet.operand_calculation_code
2315 , ldet.operand_value
2316 , ldet.adjustment_amount
2317 , line.unit_price
2318 , ldet.accrual_flag
2319 , nvl(ldet.updated_flag, QP_PREQ_PUB.G_NO)
2320 , ldet.process_code
2321 , ldet.pricing_status_code
2322 , ldet.pricing_status_text
2323 , ldet.price_break_type_code
2324 , ldet.charge_type_code
2325 , ldet.charge_subtype_code
2326 , line.rounding_factor
2327 , ldet.pricing_phase_id
2328 , ldet.created_from_list_type_code
2329 , ldet.limit_code
2330 , substr(ldet.limit_text,1,240)
2331 , ldet.list_line_no
2332 , ldet.modifier_level_code
2333 , ldet.group_quantity group_quantity
2334 , ldet.group_amount group_amount
2335 , line.pricing_status_code line_pricing_status_code
2336 , QP_PREQ_PUB.G_LDET_ORDER_TYPE is_ldet_rec
2337 , line.line_type_code
2338 , ldet.net_amount_flag net_amount_flag --bucketed_flag
2339 , ldet.calculation_code calculation_code
2340 , line.catchweight_qty
2341 , line.actual_order_quantity
2342 , line.line_unit_price
2343 , line.line_quantity ordered_qty
2347 from qp_npreq_lines_tmp line1, qp_npreq_ldets_tmp ldet
2344 , ldet.line_detail_type_code line_detail_type_code
2345 , line.line_category
2346 , line.price_flag
2348 , qp_npreq_lines_tmp line
2349 where --line.line_index = p_line_index
2350 ldet.line_index = line1.line_index
2351 --and nvl(line.processed_flag,'N') <> QP_PREQ_PUB.G_FREEGOOD_LINE -- Ravi
2352 and line1.process_status in (QP_PREQ_PUB.G_STATUS_UPDATED,
2353 QP_PREQ_PUB.G_STATUS_NEW,
2354 QP_PREQ_PUB.G_STATUS_UNCHANGED,
2355 'NEW'||QP_PREQ_PUB.G_STATUS_UNCHANGED,
2356 --fix for bug 2823886 to do cleanup and calc for old fg line
2357 'OLD'||QP_PREQ_PUB.G_STATUS_UNCHANGED)
2358 and line.process_status in (QP_PREQ_PUB.G_STATUS_UPDATED,
2359 QP_PREQ_PUB.G_STATUS_NEW,
2360 QP_PREQ_PUB.G_STATUS_UNCHANGED,
2361 'NEW'||QP_PREQ_PUB.G_STATUS_UNCHANGED,
2362 --fix for bug 2823886 to do cleanup and calc for old fg line
2363 'OLD'||QP_PREQ_PUB.G_STATUS_UNCHANGED)
2364 and line1.pricing_status_code IN (QP_PREQ_PUB.G_STATUS_UPDATED
2365 ,QP_PREQ_PUB.G_STATUS_GSA_VIOLATION
2366 ,QP_PREQ_PUB.G_STATUS_UNCHANGED)
2367 and line1.price_flag in (QP_PREQ_PUB.G_YES, QP_PREQ_PUB.G_PHASE)
2368 and line1.line_type_code = QP_PREQ_PUB.G_ORDER_LEVEL
2369 -- and line.line_type_code = QP_PREQ_PUB.G_LINE_LEVEL
2370 and (line.unit_price is not null or line.line_type_code = QP_PREQ_PUB.G_ORDER_LEVEL) -- bug 3501866
2371 and line.pricing_status_code IN (QP_PREQ_PUB.G_STATUS_UPDATED
2372 ,QP_PREQ_PUB.G_STATUS_GSA_VIOLATION
2373 ,QP_PREQ_PUB.G_STATUS_UNCHANGED)
2374 AND ldet.pricing_status_code IN (QP_PREQ_PUB.G_STATUS_NEW,QP_PREQ_PUB.G_STATUS_UPDATED)
2375 --fix for frt charge issue to calculate adj amt for manual frt charge
2376 and (ldet.applied_flag = QP_PREQ_PUB.G_YES
2377 or (ldet.applied_flag = QP_PREQ_PUB.G_NO
2378 and ldet.created_from_list_line_type =
2379 QP_PREQ_PUB.G_FREIGHT_CHARGE
2380 and ldet.automatic_flag = QP_PREQ_PUB.G_NO))
2381 -- and ldet.created_from_list_line_type IN (QP_PREQ_PUB.G_DISCOUNT,
2382 -- QP_PREQ_PUB.G_SURCHARGE , QP_PREQ_PUB.G_PRICE_BREAK_TYPE, QP_PREQ_PUB.G_FREIGHT_CHARGE)
2383 and (line.price_flag in (QP_PREQ_PUB.G_YES, QP_PREQ_PUB.G_PHASE, QP_PREQ_PUB.G_CALCULATE_ONLY)
2384 OR nvl(line.processed_code,'0') = QP_PREQ_PUB.G_BY_ENGINE)
2385 and ldet.modifier_level_code = QP_PREQ_PUB.G_ORDER_LEVEL
2386 and ldet.created_from_list_type_code not in
2387 (QP_PREQ_PUB.G_PRICE_LIST_HEADER, QP_PREQ_PUB.G_AGR_LIST_HEADER)
2388 UNION
2389 select /*+ ORDERED USE_NL(ldet qplh) index(ldet QP_PREQ_LDETS_TMP_N1) */
2390 ldet.created_from_list_line_id
2391 , line.line_index line_ind
2392 , line.line_index curr_line_index
2393 , line.line_id line_id
2394 , ldet.line_detail_index
2395 , ldet.created_from_list_line_type
2396 , ldet.created_from_list_header_id
2397 , ldet.applied_flag
2398 , (line.updated_adjusted_unit_price
2399 - line.adjusted_unit_price) amount_changed
2400 , line.adjusted_unit_price
2401 , ldet.line_quantity priced_quantity
2402 , line.priced_quantity line_priced_quantity
2403 , line.updated_adjusted_unit_price
2404 , ldet.automatic_flag
2405 , ldet.override_flag
2406 , ldet.pricing_group_sequence
2407 , ldet.operand_calculation_code
2408 , ldet.operand_value
2409 , ldet.adjustment_amount
2410 , line.unit_price
2411 , ldet.accrual_flag
2412 , nvl(ldet.updated_flag, QP_PREQ_PUB.G_NO)
2413 , ldet.process_code
2414 , ldet.pricing_status_code
2415 , ldet.pricing_status_text
2416 , ldet.price_break_type_code
2417 , ldet.charge_type_code
2418 , ldet.charge_subtype_code
2419 , line.rounding_factor
2420 , ldet.pricing_phase_id
2421 , ldet.created_from_list_type_code
2422 , ldet.limit_code
2423 , substr(ldet.limit_text,1,240)
2424 , ldet.list_line_no
2425 , ldet.modifier_level_code
2426 , ldet.group_quantity group_quantity
2427 , ldet.group_amount group_amount
2428 , line.pricing_status_code line_pricing_status_code
2429 , QP_PREQ_PUB.G_ASO_LINE_TYPE is_ldet_rec
2430 , line.line_type_code
2431 , ldet.net_amount_flag net_amount_flag --bucketed_flag
2432 , ldet.calculation_code calculation_code
2433 , line.catchweight_qty
2434 , line.actual_order_quantity
2435 , line.line_unit_price
2436 , line.line_quantity ordered_qty
2437 , ldet.line_detail_type_code line_detail_type_code
2438 , line.line_category
2439 , line.price_flag
2440 from qp_npreq_lines_tmp line, qp_npreq_ldets_tmp ldet,
2441 qp_list_headers_b qplh
2442 -- where p_request_type_code <> 'ONT'
2443 --bug 3085453 handle pricing availability UI
2444 -- they pass reqtype ONT and insert adj into ldets
2445 where nvl(QP_PREQ_PUB.G_CHECK_CUST_VIEW_FLAG,QP_PREQ_PUB.G_NO) <> QP_PREQ_PUB.G_YES
2446 --and nvl(line.processed_flag,'N') <> QP_PREQ_PUB.G_FREEGOOD_LINE -- Ravi
2450 and line.process_status in (QP_PREQ_PUB.G_STATUS_UPDATED,
2447 and line.pricing_status_code IN (QP_PREQ_PUB.G_STATUS_UPDATED
2448 ,QP_PREQ_PUB.G_STATUS_GSA_VIOLATION
2449 ,QP_PREQ_PUB.G_STATUS_UNCHANGED)
2451 QP_PREQ_PUB.G_STATUS_NEW,
2452 QP_PREQ_PUB.G_STATUS_UNCHANGED,
2453 'NEW'||QP_PREQ_PUB.G_STATUS_UNCHANGED,
2454 --fix for bug 2823886 to do cleanup and calc for old fg line
2455 'OLD'||QP_PREQ_PUB.G_STATUS_UNCHANGED)
2456 and (line.price_flag in (QP_PREQ_PUB.G_YES, QP_PREQ_PUB.G_PHASE, QP_PREQ_PUB.G_CALCULATE_ONLY)
2457 OR nvl(line.processed_code,'0') = QP_PREQ_PUB.G_BY_ENGINE)
2458 and line.line_type_code = QP_PREQ_PUB.G_LINE_LEVEL
2459 and line.unit_price is not null -- bug 3501866, calculation to be done for line having unit price
2460 and ldet.line_index = line.line_index
2461 and ldet.pricing_status_code = QP_PREQ_PUB.G_STATUS_UNCHANGED
2462 and (ldet.updated_flag = QP_PREQ_PUB.G_YES
2463 or line.price_flag = QP_PREQ_PUB.G_CALCULATE_ONLY
2464 -- or (p_event_code is null and ldet.updated_flag is null)
2465 or ldet.pricing_phase_id not in (select ph.pricing_phase_id
2466 from qp_event_phases ev, qp_pricing_phases ph
2467 --changes to enable multiple events passed as a string
2468 where ph.pricing_phase_id = ev.pricing_phase_id
2469 ---introduced the end date condition for bug 3376902
2470 and (ev.end_date_active is null or (ev.end_date_active is not null and ev.end_date_active > line.pricing_effective_date))
2471 --introduced for freight_rating functionality to return only modifiers in
2472 --phases where freight_exist = 'Y' if G_GET_FREIGHT_FLAG = 'Y'
2473 and ((QP_PREQ_PUB.G_GET_FREIGHT_FLAG = QP_PREQ_PUB.G_YES
2474 and ph.freight_exists = QP_PREQ_PUB.G_YES)
2475 or (QP_PREQ_PUB.G_GET_FREIGHT_FLAG = QP_PREQ_PUB.G_NO))
2476 and instr(p_event_code,ev.pricing_event_code||',') > 0
2477 -- 3721860, pass list_line_id and line_index both for function Get_buy_line_price_flag
2478 and (nvl(QP_PREQ_PUB.Get_buy_line_price_flag(ldet.created_from_list_line_id, line.line_index),line.price_flag) = QP_PREQ_PUB.G_YES
2479 or (nvl(QP_PREQ_PUB.Get_buy_line_price_flag(ldet.created_from_list_line_id, line.line_index),line.price_flag) = QP_PREQ_PUB.G_PHASE
2480 and nvl(ph.user_freeze_override_flag,ph.freeze_override_flag) =
2481 QP_PREQ_PUB.G_YES))))
2482 and ldet.modifier_level_code IN (QP_PREQ_PUB.G_LINE_LEVEL,QP_PREQ_PUB.G_LINE_GROUP)
2483 --commented to fetch auto overridden unapplied adj(user deleted)
2484 -- and ldet.applied_flag = QP_PREQ_PUB.G_YES
2485 -- and ldet.created_from_list_line_type IN (QP_PREQ_PUB.G_DISCOUNT
2486 -- , QP_PREQ_PUB.G_SURCHARGE , QP_PREQ_PUB.G_PRICE_BREAK_TYPE, QP_PREQ_PUB.G_FREIGHT_CHARGE)
2487 and qplh.list_header_id = ldet.created_from_list_header_id
2488 and qplh.list_type_code not in (QP_PREQ_PUB.G_PRICE_LIST_HEADER,
2489 QP_PREQ_PUB.G_AGR_LIST_HEADER)
2490 and nvl(ldet.line_detail_type_code,'NULL') <> QP_PREQ_PUB.G_CHILD_DETAIL_TYPE -- updated in update_passed_in_pbh
2491 UNION
2492 select /*+ ORDERED USE_NL(ldet line qplh) index(ldet QP_PREQ_LDETS_TMP_N1) */
2493 ldet.created_from_list_line_id
2494 , line.line_index line_ind
2495 , line.line_index curr_line_index
2496 , line.line_id line_id
2497 , ldet.line_detail_index
2498 , ldet.created_from_list_line_type
2499 , ldet.created_from_list_header_id
2500 , ldet.applied_flag
2501 , (line.updated_adjusted_unit_price
2502 - line.adjusted_unit_price) amount_changed
2503 , line.adjusted_unit_price
2504 , ldet.line_quantity priced_quantity
2505 , line.priced_quantity line_priced_quantity
2506 , line.updated_adjusted_unit_price
2507 , ldet.automatic_flag
2508 , ldet.override_flag
2509 , ldet.pricing_group_sequence
2510 , ldet.operand_calculation_code
2511 , ldet.operand_value
2512 , ldet.adjustment_amount
2513 , line.unit_price
2514 , ldet.accrual_flag
2515 , nvl(ldet.updated_flag, QP_PREQ_PUB.G_NO)
2516 , ldet.process_code
2517 , ldet.pricing_status_code
2518 , ldet.pricing_status_text
2519 , ldet.price_break_type_code
2520 , ldet.charge_type_code
2521 , ldet.charge_subtype_code
2522 , line.rounding_factor
2523 , ldet.pricing_phase_id
2524 , ldet.created_from_list_type_code
2525 , ldet.limit_code
2526 , substr(ldet.limit_text,1,240)
2527 , ldet.list_line_no
2528 , ldet.modifier_level_code
2529 , ldet.group_quantity group_quantity
2530 , ldet.group_amount group_amount
2531 , line.pricing_status_code line_pricing_status_code
2532 , QP_PREQ_PUB.G_ASO_ORDER_TYPE is_ldet_rec
2533 , line.line_type_code
2534 , ldet.net_amount_flag net_amount_flag --bucketed_flag
2535 , ldet.calculation_code calculation_code
2539 , line.line_quantity ordered_qty
2536 , line.catchweight_qty
2537 , line.actual_order_quantity
2538 , line.line_unit_price
2540 , ldet.line_detail_type_code line_detail_type_code
2541 , line.line_category
2542 , line.price_flag
2543 from qp_npreq_lines_tmp line1, qp_npreq_ldets_tmp ldet
2544 ,qp_npreq_lines_tmp line, qp_list_headers_b qplh
2545 -- where p_request_type_code <> 'ONT'
2546 --bug 3085453 handle pricing availability UI
2547 -- they pass reqtype ONT and insert adj into ldets
2548 where nvl(QP_PREQ_PUB.G_CHECK_CUST_VIEW_FLAG,QP_PREQ_PUB.G_NO) <> QP_PREQ_PUB.G_YES
2549 --and nvl(line.processed_flag,'N') <> QP_PREQ_PUB.G_FREEGOOD_LINE -- Ravi
2550 and line1.pricing_status_code IN (QP_PREQ_PUB.G_STATUS_UPDATED
2551 ,QP_PREQ_PUB.G_STATUS_GSA_VIOLATION
2552 ,QP_PREQ_PUB.G_STATUS_UNCHANGED)
2553 and line1.process_status in (QP_PREQ_PUB.G_STATUS_UPDATED,
2554 QP_PREQ_PUB.G_STATUS_NEW,
2555 QP_PREQ_PUB.G_STATUS_UNCHANGED,
2556 'NEW'||QP_PREQ_PUB.G_STATUS_UNCHANGED,
2557 --fix for bug 2823886 to do cleanup and calc for old fg line
2558 'OLD'||QP_PREQ_PUB.G_STATUS_UNCHANGED)
2559 and line.process_status in (QP_PREQ_PUB.G_STATUS_UPDATED,
2560 QP_PREQ_PUB.G_STATUS_NEW,
2561 QP_PREQ_PUB.G_STATUS_UNCHANGED,
2562 'NEW'||QP_PREQ_PUB.G_STATUS_UNCHANGED,
2563 --fix for bug 2823886 to do cleanup and calc for old fg line
2564 'OLD'||QP_PREQ_PUB.G_STATUS_UNCHANGED)
2565 and line1.line_type_code = QP_PREQ_PUB.G_ORDER_LEVEL
2566 and (line1.price_flag in (QP_PREQ_PUB.G_YES, QP_PREQ_PUB.G_PHASE, QP_PREQ_PUB.G_CALCULATE_ONLY)
2567 OR nvl(line1.processed_code,'0') = QP_PREQ_PUB.G_BY_ENGINE)
2568 and (line.price_flag in (QP_PREQ_PUB.G_YES, QP_PREQ_PUB.G_PHASE, QP_PREQ_PUB.G_CALCULATE_ONLY)
2569 OR nvl(line.processed_code,'0') = QP_PREQ_PUB.G_BY_ENGINE)
2570 and (line.unit_price is not null or line.line_type_code = QP_PREQ_PUB.G_ORDER_LEVEL) -- bug 3501866
2571 and line.pricing_status_code IN (QP_PREQ_PUB.G_STATUS_UPDATED
2572 ,QP_PREQ_PUB.G_STATUS_GSA_VIOLATION
2573 ,QP_PREQ_PUB.G_STATUS_UNCHANGED)
2574 and line1.line_index = ldet.line_index
2575 and ldet.pricing_status_code = QP_PREQ_PUB.G_STATUS_UNCHANGED
2576 and (ldet.updated_flag = QP_PREQ_PUB.G_YES
2577 or line.price_flag = QP_PREQ_PUB.G_CALCULATE_ONLY
2578 -- or (p_event_code is null and ldet.updated_flag is null)
2579 or ldet.pricing_phase_id not in (select ph.pricing_phase_id
2580 from qp_event_phases ev, qp_pricing_phases ph
2581 --changes to enable multiple events passed as a string
2582 where ph.pricing_phase_id = ev.pricing_phase_id
2583 ---introduced the end date condition for bug 3376902
2584 and (ev.end_date_active is null or (ev.end_date_active is not null and ev.end_date_active > line.pricing_effective_date))
2585 --introduced for freight_rating functionality to return only modifiers in
2586 --phases where freight_exist = 'Y' if G_GET_FREIGHT_FLAG = 'Y'
2587 and ((QP_PREQ_PUB.G_GET_FREIGHT_FLAG = QP_PREQ_PUB.G_YES
2588 and ph.freight_exists = QP_PREQ_PUB.G_YES)
2589 or (QP_PREQ_PUB.G_GET_FREIGHT_FLAG = QP_PREQ_PUB.G_NO))
2590 and instr(p_event_code,ev.pricing_event_code||',') > 0
2591 -- Ravi changed from line.price_flag to line1.price_flag
2592 -- 3721860, pass list_line_id and line_index both for function Get_buy_line_price_flag
2593 and (nvl(QP_PREQ_PUB.Get_buy_line_price_flag(ldet.created_from_list_line_id, line1.line_index),line1.price_flag) = QP_PREQ_PUB.G_YES
2594 or (nvl(QP_PREQ_PUB.Get_buy_line_price_flag(ldet.created_from_list_line_id, line1.line_index),line1.price_flag) = QP_PREQ_PUB.G_PHASE
2595 and nvl(ph.user_freeze_override_flag, ph.freeze_override_flag) =
2596 QP_PREQ_PUB.G_YES))))
2597 and ldet.modifier_level_code = QP_PREQ_PUB.G_ORDER_LEVEL
2598 --commented to fetch auto overridden unapplied adj(user deleted)
2599 -- and ldet.applied_flag = QP_PREQ_PUB.G_YES
2600 -- and ldet.created_from_list_line_type IN (QP_PREQ_PUB.G_DISCOUNT
2601 -- , QP_PREQ_PUB.G_SURCHARGE , QP_PREQ_PUB.G_PRICE_BREAK_TYPE, QP_PREQ_PUB.G_FREIGHT_CHARGE)
2602 and qplh.list_header_id = ldet.created_from_list_header_id
2603 and qplh.list_type_code not in (QP_PREQ_PUB.G_PRICE_LIST_HEADER,
2604 QP_PREQ_PUB.G_AGR_LIST_HEADER)
2605 --order by line_ind,pricing_group_sequence,is_ldet_rec; -- 2892848 net_amt
2606 order by pricing_group_sequence,line_ind, is_ldet_rec; -- 2892848, net_amt
2607
2608
2609 CURSOR l_max_dtl_index_cur IS
2610 SELECT max(line_detail_index)
2611 FROM qp_npreq_ldets_tmp;
2612
2613 Pricing_Exception Exception;
2614
2615 i Pls_Integer;
2616 m Pls_Integer;
2617 x Pls_Integer;
2618 d Pls_Integer;
2619 g Pls_Integer;
2620 n Pls_Integer;
2621
2622 l_curr_line_index number := -1;--fnd_api.g_miss_num;
2623 l_auto_line_dtl_index_tbl qp_preq_grp.number_type;
2624 l_auto_override_dtl_id_tbl qp_preq_grp.number_type;
2625
2626 l_adj_tbl QP_PREQ_PUB.adj_tbl_type;
2627 l_adj_overflow_tbl QP_PREQ_PUB.adj_tbl_type;
2628 l_frt_tbl QP_PREQ_PUB.frt_charge_tbl;
2629
2630 l_list_line_id_tbl QP_PREQ_GRP.number_type;
2631 l_line_index_tbl QP_PREQ_GRP.number_type;
2632 l_curr_line_index_tbl QP_PREQ_GRP.number_type;
2633 l_line_id_tbl QP_PREQ_GRP.number_type;
2637 l_applied_flag_tbl QP_PREQ_GRP.flag_type;
2634 l_line_dtl_index_tbl QP_PREQ_GRP.number_type;
2635 l_list_line_type_code_tbl QP_PREQ_GRP.varchar_type;
2636 l_list_header_id_tbl QP_PREQ_GRP.number_type;
2638 l_amount_changed_tbl QP_PREQ_GRP.number_type;
2639 l_adjusted_unit_price_tbl QP_PREQ_GRP.number_type;
2640 l_priced_quantity_tbl QP_PREQ_GRP.number_type;
2641 l_upd_adj_unit_price_tbl QP_PREQ_GRP.number_type;
2642 l_automatic_flag_tbl QP_PREQ_GRP.flag_type;
2643 l_override_flag_tbl QP_PREQ_GRP.flag_type;
2644 l_pricing_group_sequence_tbl QP_PREQ_GRP.number_type;
2645 l_operand_calc_code_tbl QP_PREQ_GRP.varchar_type;
2646 l_operand_value_tbl QP_PREQ_GRP.number_type;
2647 l_adjustment_amount_tbl QP_PREQ_GRP.number_type;
2648 l_unit_price_tbl QP_PREQ_GRP.number_type;
2649 l_accrual_flag_tbl QP_PREQ_GRP.flag_type;
2650 l_updated_flag_tbl QP_PREQ_GRP.flag_type;
2651 l_process_code_tbl QP_PREQ_GRP.varchar_type;
2652 l_status_code_tbl QP_PREQ_GRP.varchar_type;
2653 l_status_text_tbl QP_PREQ_GRP.varchar_type;
2654 l_price_break_type_code_tbl QP_PREQ_GRP.varchar_type;
2655 l_charge_type_code_tbl QP_PREQ_GRP.varchar_type;
2656 l_charge_subtype_code_tbl QP_PREQ_GRP.varchar_type;
2657 l_rounding_factor_tbl QP_PREQ_GRP.number_type;
2658 l_pricing_phase_id_tbl QP_PREQ_GRP.number_type;
2659 l_list_type_code_tbl QP_PREQ_GRP.varchar_type;
2660 l_list_line_no_tbl QP_PREQ_GRP.varchar_type;
2661 l_limit_code_tbl QP_PREQ_GRP.varchar_type;
2662 l_limit_text_tbl QP_PREQ_GRP.varchar_type;
2663 l_modifier_level_tbl QP_PREQ_GRP.varchar_type;
2664 l_group_qty_tbl QP_PREQ_GRP.number_type;
2665 l_group_amt_tbl QP_PREQ_GRP.number_type;
2666 l_line_sts_code_tbl QP_PREQ_GRP.varchar_type;
2667 l_is_ldet_rec_tbl QP_PREQ_GRP.varchar_type;
2668 l_line_type_code_tbl QP_PREQ_GRP.varchar_type;
2669 l_line_priced_qty_tbl QP_PREQ_GRP.number_type;
2670 --2388011
2671 l_net_amount_flag_tbl QP_PREQ_GRP.flag_type;
2672 --2388011
2673 l_calculation_code_tbl QP_PREQ_GRP.varchar_type;
2674 l_catchweight_qty_tbl QP_PREQ_GRP.number_type;
2675 l_actual_order_qty_tbl QP_PREQ_GRP.number_type;
2676 l_line_unit_price_tbl QP_PREQ_GRP.number_type;
2677 l_ord_qty_tbl QP_PREQ_GRP.number_type;
2678 l_line_detail_type_code_tbl QP_PREQ_GRP.varchar_type;
2679 l_line_category_tbl QP_PREQ_GRP.varchar_type;
2680 l_price_flag_tbl QP_PREQ_GRP.flag_type;
2681
2682 l_prev_line_start_index number :=0;
2683 l_cleanup_flag VARCHAR2(1) := QP_PREQ_PUB.G_YES;
2684 nrows CONSTANT number := 2;
2685
2686 --added to check for duplicate list_line_ids
2687 l_dup_ind NUMBER;
2688 l_dup_updated_flag QP_PREQ_GRP.FLAG_TYPE;
2689 l_dup_is_ldet_rec QP_PREQ_GRP.VARCHAR_TYPE;
2690 l_dup_uniq_ind QP_PREQ_GRP.VARCHAR_TYPE;
2691 l_dup_plsql_ind QP_PREQ_GRP.NUMBER_TYPE;
2692 --added to check for duplicate list_line_ids
2693
2694 BEGIN
2695
2696 l_debug := QP_PREQ_GRP.G_DEBUG_ENGINE;
2697
2698 IF l_debug = FND_API.G_TRUE THEN
2699 QP_PREQ_GRP.engine_debug('begin fetch adj event'||p_event_code);
2700 QP_PREQ_GRP.engine_debug('begin fetch adj reqtype '||p_request_type_code);
2701 --bug 3085453 handle pricing availability UI
2702 -- they pass reqtype ONT and insert adj into ldets
2703 QP_PREQ_GRP.engine_debug('begin fetch adj chk_cust '||QP_PREQ_PUB.G_CHECK_CUST_VIEW_FLAG);
2704 QP_PREQ_GRP.engine_debug('begin fetch adj calc_flag '||p_calculate_flag);
2705 QP_PREQ_GRP.engine_debug('begin fetch adj round_flag '||p_rounding_flag);
2706 QP_PREQ_GRP.engine_debug('begin fetch adj view_code '||p_view_code);
2707 QP_PREQ_GRP.engine_debug('SL, with net_amt change, ordered by bucket, line_index.');
2708 END IF;
2709
2710 --cleanup needs to be done only for OM as OC/OKC delete and insert adjustments
2711 --setting up cleanup_flag accordingly to not peform cleanup for adj if
2712 --not called by OM
2713 --IF p_request_type_code = 'ONT' THEN
2714 --bug 3085453 handle pricing availability UI
2715 -- they pass reqtype ONT and insert adj into ldets
2716 IF QP_PREQ_PUB.G_CHECK_CUST_VIEW_FLAG = QP_PREQ_PUB.G_YES THEN
2717 l_cleanup_flag := QP_PREQ_PUB.G_YES;
2718 ELSE
2719 l_cleanup_flag := QP_PREQ_PUB.G_NO;
2720 END IF;--p_request_type_code
2721
2722 --fix for bug 2969419 this updated line_detail_type_code
2723 --to recognize the PBH child lines. This update needs to be
2724 --done before the l_calculate_cur fetch
2725 --IF p_request_type_code <> 'ONT' THEN
2726 --bug 3085453 handle pricing availability UI
2727 -- they pass reqtype ONT and insert adj into ldets
2728 IF QP_PREQ_PUB.G_CHECK_CUST_VIEW_FLAG <> QP_PREQ_PUB.G_YES THEN
2729 --This will be called for OC and others who will pass the PBH
2730 --and the relationship in which case the relationship needs to be updated
2731 QP_PREQ_PUB.Update_passed_in_pbh(x_return_status, x_return_status_text);
2732 END IF;--p_request_type_code
2733
2734 OPEN l_max_dtl_index_cur;
2735 FETCH l_max_dtl_index_cur INTO G_max_dtl_index;
2736 CLOSE l_max_dtl_index_cur;
2737
2738 G_max_dtl_index := nvl(G_max_dtl_index,0);
2739 G_PBH_LINE_DTL_INDEX.delete;
2740 G_PBH_LINE_INDEX.delete;
2741 G_PBH_PRICE_ADJ_ID.delete;
2742 G_PBH_PLSQL_IND.delete;
2743 G_ORD_LVL_LDET_INDEX.delete; -- 3031108
2744
2745 IF l_debug = FND_API.G_TRUE THEN
2746 QP_PREQ_GRP.engine_debug('G_MAX_DTL_INDEX '||G_MAX_DTL_INDEX);
2747
2748 END IF;
2749
2750 --to eliminate the duplicate manual adjustments in the temp table to prevent cleanup for them bug 2191169
2751 --fix for bug 2515297 not to check applied_flag in this update
2755 where pricing_status_code = QP_PREQ_PUB.G_STATUS_NEW
2752 UPDATE qp_npreq_ldets_tmp ldet2
2753 set ldet2.pricing_status_code = QP_PREQ_PUB.G_STATUS_DELETED,
2754 pricing_status_text = 'DUPLICATE MANUAL-OVERRIDEABLE'
2756 and nvl(ldet2.updated_flag,QP_PREQ_PUB.G_NO) = QP_PREQ_PUB.G_NO
2757 and exists ( select 'X'
2758 from qp_npreq_ldets_tmp ldet
2759 where nvl(ldet.updated_flag,QP_PREQ_PUB.G_NO) =QP_PREQ_PUB.G_YES
2760 and ldet.line_index = ldet2.line_index
2761 and ldet.created_from_list_line_id =
2762 ldet2.created_from_list_line_id);
2763
2764 IF (l_debug = FND_API.G_TRUE) THEN
2765 QP_PREQ_GRP.engine_debug('DUPLICATE MANUAL-OVERR '||SQL%ROWCOUNT);
2766 END IF;
2767
2768 -- bug 3359924 - AFTER APPLYING MANUAL PBH ENGINE RETURNS DUPLICATE RELATIONSHIP RECORDS
2769 UPDATE qp_npreq_rltd_lines_tmp rltd
2770 set rltd.pricing_status_code = QP_PREQ_PUB.G_STATUS_DELETED,
2771 rltd.pricing_status_text = 'DUPLICATE MANUAL-OVERRIDEABLE'
2772 where rltd.pricing_status_code = QP_PREQ_PUB.G_STATUS_NEW
2773 and rltd.line_detail_index in (select ldet.line_detail_index
2774 from qp_npreq_ldets_tmp ldet
2775 where ldet.pricing_status_code = QP_PREQ_PUB.G_STATUS_DELETED
2776 and ldet.CREATED_FROM_LIST_LINE_TYPE = QP_PREQ_PUB.G_BY_PBH);
2777
2778 IF (l_debug = FND_API.G_TRUE) THEN
2779 QP_PREQ_GRP.engine_debug('DUPLICATE MANUAL-OVERR '||SQL%ROWCOUNT);
2780 END IF;
2781
2782 -- IF p_event_code IS NULL
2783 -- and p_calculate_flag = QP_PREQ_GRP.G_CALCULATE_ONLY
2784 -- THEN
2785
2786 -- IF p_request_type_code = 'ONT'
2787 -- and p_view_code = 'ONTVIEW'
2788 -- THEN
2789 IF l_debug = FND_API.G_TRUE THEN
2790 -- QP_PREQ_GRP.engine_debug('open ONT cur'||p_request_type_code);
2791 QP_PREQ_GRP.engine_debug('open ONT cur'||QP_PREQ_PUB.G_CHECK_CUST_VIEW_FLAG);
2792 END IF;
2793
2794 --bug 4900095 get service qty for lumpsum
2795 IF QP_PREQ_GRP.G_Service_line_qty_tbl.COUNT = 0 THEN
2796 QP_PREQ_PUB.Determine_svc_item_quantity;
2797 END IF;
2798
2799 OPEN l_calculate_cur;
2800 -- END IF;
2801
2802
2803 LOOP
2804
2805 l_dup_updated_flag.delete;
2806 l_dup_is_ldet_rec.delete;
2807 l_dup_plsql_ind.delete;
2808 l_dup_uniq_ind.delete;
2809
2810 l_list_line_id_tbl.delete;
2811 l_line_index_tbl.delete;
2812 l_curr_line_index_tbl.delete;
2813 l_line_id_tbl.delete;
2814 l_line_dtl_index_tbl.delete;
2815 l_list_line_type_code_tbl.delete;
2816 l_list_header_id_tbl.delete;
2817 l_applied_flag_tbl.delete;
2818 l_amount_changed_tbl.delete;
2819 l_adjusted_unit_price_tbl.delete;
2820 l_priced_quantity_tbl.delete;
2821 l_line_priced_qty_tbl.delete;
2822 l_upd_adj_unit_price_tbl.delete;
2823 l_automatic_flag_tbl.delete;
2824 l_override_flag_tbl.delete;
2825 l_pricing_group_sequence_tbl.delete;
2826 l_operand_calc_code_tbl.delete;
2827 l_operand_value_tbl.delete;
2828 l_adjustment_amount_tbl.delete;
2829 l_unit_price_tbl.delete;
2830 l_accrual_flag_tbl.delete;
2831 l_updated_flag_tbl.delete;
2832 l_process_code_tbl.delete;
2833 l_status_code_tbl.delete;
2834 l_status_text_tbl.delete;
2835 l_price_break_type_code_tbl.delete;
2836 l_charge_type_code_tbl.delete;
2837 l_charge_subtype_code_tbl.delete;
2838 l_rounding_factor_tbl.delete;
2839 l_pricing_phase_id_tbl.delete;
2840 l_list_line_no_tbl.delete;
2841 l_limit_text_tbl.delete;
2842 l_limit_code_tbl.delete;
2843 l_list_type_code_tbl.delete;
2844 l_modifier_level_tbl.delete;
2845 l_group_qty_tbl.delete;
2846 l_group_amt_tbl.delete;
2847 l_line_sts_code_tbl.delete;
2848 l_is_ldet_rec_tbl.delete;
2849 l_line_type_code_tbl.delete;
2850 --2388011
2851 l_net_amount_flag_tbl.delete;
2852 --2388011
2853 l_calculation_code_tbl.delete;
2854 l_catchweight_qty_tbl.delete;
2855 l_actual_order_qty_tbl.delete;
2856 l_line_unit_price_tbl.delete;
2857 l_ord_qty_tbl.delete;
2858 l_line_detail_type_code_tbl.delete;
2859 l_line_category_tbl.delete;
2860 l_price_flag_tbl.delete;
2861
2862 d := l_adj_overflow_tbl.COUNT;
2863 --l_adj_tbl := l_adj_overflow_tbl;
2864 l_adj_overflow_tbl.delete;
2865 g := 0;
2866 l_prev_line_start_index := 0;
2867
2868
2869 IF l_debug = FND_API.G_TRUE THEN
2870 QP_PREQ_GRP.engine_debug('fetch cur');
2871 END IF;
2872
2873
2874 FETCH l_calculate_cur
2875 BULK COLLECT INTO
2876 l_list_line_id_tbl
2877 ,l_line_index_tbl
2878 ,l_curr_line_index_tbl
2879 ,l_line_id_tbl
2880 ,l_line_dtl_index_tbl
2881 ,l_list_line_type_code_tbl
2882 ,l_list_header_id_tbl
2883 ,l_applied_flag_tbl
2884 ,l_amount_changed_tbl
2885 ,l_adjusted_unit_price_tbl
2886 ,l_priced_quantity_tbl
2887 ,l_line_priced_qty_tbl
2888 ,l_upd_adj_unit_price_tbl
2889 ,l_automatic_flag_tbl
2890 ,l_override_flag_tbl
2891 ,l_pricing_group_sequence_tbl
2892 ,l_operand_calc_code_tbl
2893 ,l_operand_value_tbl
2894 ,l_adjustment_amount_tbl
2895 ,l_unit_price_tbl
2896 ,l_accrual_flag_tbl
2897 ,l_updated_flag_tbl
2898 ,l_process_code_tbl
2902 ,l_charge_type_code_tbl
2899 ,l_status_code_tbl
2900 ,l_status_text_tbl
2901 ,l_price_break_type_code_tbl
2903 ,l_charge_subtype_code_tbl
2904 ,l_rounding_factor_tbl
2905 ,l_pricing_phase_id_tbl
2906 ,l_list_type_code_tbl
2907 ,l_limit_code_tbl
2908 ,l_limit_text_tbl
2909 ,l_list_line_no_tbl
2910 ,l_modifier_level_tbl
2911 ,l_group_qty_tbl
2912 ,l_group_amt_tbl
2913 ,l_line_sts_code_tbl
2914 ,l_is_ldet_rec_tbl
2915 ,l_line_type_code_tbl
2916 --2388011
2917 ,l_net_amount_flag_tbl
2918 --2388011
2919 ,l_calculation_code_tbl
2920 ,l_catchweight_qty_tbl
2921 ,l_actual_order_qty_tbl
2922 ,l_line_unit_price_tbl
2923 ,l_ord_qty_tbl
2924 ,l_line_detail_type_code_tbl
2925 ,l_line_category_tbl
2926 ,l_price_flag_tbl;
2927 -- LIMIT nrows;
2928 EXIT WHEN l_list_line_id_tbl.COUNT = 0;
2929
2930 IF l_list_line_id_tbl.COUNT > 0
2931 and QP_PREQ_GRP.G_DEBUG_ENGINE = FND_API.G_TRUE
2932 and l_debug = FND_API.G_TRUE
2933 THEN
2934 for i in l_list_line_id_tbl.FIRST..l_list_line_id_tbl.LAST
2935 LOOP
2936 IF l_debug = FND_API.G_TRUE THEN
2937 QP_PREQ_GRP.engine_debug('list_line_id '||l_list_line_id_tbl(i)||
2938 'line index '||l_line_index_tbl(i)||' is ldet '||
2939 l_is_ldet_rec_tbl(i)||' linetype '||l_line_type_code_tbl(i) ||
2940 'line detail type code ' || l_line_detail_type_code_tbl(i)||
2941 ' line_category '||l_line_category_tbl(i)||' priceflag '||
2942 l_price_flag_tbl(i));
2943 END IF;
2944 END LOOP;
2945 END IF;--l_list_line_id_tbl.COUNT
2946
2947
2948 IF l_list_line_id_tbl.COUNT > 0
2949 THEN
2950 FOR i IN l_list_line_id_tbl.FIRST..l_list_line_id_tbl.LAST
2951 LOOP
2952 d := d + 1;
2953 IF l_debug = FND_API.G_TRUE THEN
2954 QP_PREQ_GRP.engine_debug('adj_tbl count '||d);
2955 QP_PREQ_GRP.engine_debug('loop cur '||i);
2956
2957 END IF;
2958 IF QP_PREQ_GRP.G_DEBUG_ENGINE = FND_API.G_TRUE THEN
2959 qp_preq_grp.engine_debug('display details '
2960 ||l_list_line_id_tbl(i)||' line index '||l_line_index_tbl(i)
2961 ||' line detail index '||l_line_dtl_index_tbl(i)
2962 ||' auto '||l_automatic_flag_tbl(i)
2963 ||' overr '||l_override_flag_tbl(i)
2964 ||' updated '||l_updated_flag_tbl(i)
2965 ||' applied '||l_applied_flag_tbl(i)
2966 ||' hdr id '||l_list_header_id_tbl(i)||' is ldet '
2967 ||l_is_ldet_rec_tbl(i)||' linetype '||l_line_type_code_tbl(i));
2968
2969 qp_preq_grp.engine_debug('list_line_type ' || l_list_line_type_code_tbl(i)
2970 || 'modifier_level ' || l_modifier_level_tbl(i)
2971 || 'net_amount_flag ' || l_net_amount_flag_tbl(i)
2972 || 'is_ldet_rec ' || l_is_ldet_rec_tbl(i));
2973 END IF;
2974
2975 -- bug 3618464 - PBH, GROUP OF LINES, OVERRIDE, NET AMOUNT modifier calculation fix
2976 if l_list_line_type_code_tbl(i) = QP_PREQ_PUB.G_PRICE_BREAK_TYPE and
2977 l_modifier_level_tbl(i) = QP_PREQ_PUB.G_LINE_GROUP and
2978 l_is_ldet_rec_tbl(i) in (QP_PREQ_PUB.G_ADJ_ORDER_TYPE, QP_PREQ_PUB.G_ADJ_LINE_TYPE)
2979 then
2980
2981 if l_net_amount_flag_tbl(i) is null then
2982 select net_amount_flag
2983 into l_net_amount_flag_tbl(i)
2984 from qp_list_lines
2985 where list_line_id = l_list_line_id_tbl(i);
2986 end if;
2987
2988 if l_net_amount_flag_tbl(i) = 'Y' then
2989 if QP_PREQ_PUB.G_LINE_INDEXES_FOR_LINE_ID.exists(l_list_line_id_tbl(i)) then
2990 QP_PREQ_PUB.G_LINE_INDEXES_FOR_LINE_ID(l_list_line_id_tbl(i)) := QP_PREQ_PUB.G_LINE_INDEXES_FOR_LINE_ID(l_list_line_id_tbl(i)) || l_line_index_tbl(i) || ',';
2991 else
2992 QP_PREQ_PUB.G_LINE_INDEXES_FOR_LINE_ID(l_list_line_id_tbl(i)) := l_line_index_tbl(i) || ',';
2993 end if;
2994 end if;
2995 end if;
2996
2997 --added to check for duplicate list_line_ids
2998 /*
2999 l_dup_ind := l_line_index_tbl(i) + l_list_line_id_tbl(i);
3000
3001 IF l_debug = FND_API.G_TRUE THEN
3002 QP_PREQ_GRP.engine_debug('Duplicate list line check '
3003 ||' line_index+list_line_id val: '||l_dup_ind);
3004 END IF;--l_debug
3005
3006 IF l_dup_updated_flag.exists(l_dup_ind) THEN
3007 IF l_debug = FND_API.G_TRUE THEN
3008 QP_PREQ_GRP.engine_debug('Duplicate list line exists ');
3009 END IF;--l_debug
3010
3011 IF l_dup_updated_flag(l_dup_ind) = QP_PREQ_PUB.G_YES
3012 and l_updated_flag_tbl(i) = QP_PREQ_PUB.G_NO THEN
3013 --in this case the oe_price_adj record must be applied
3014 --so engine selected needs to be marked as deleted
3015 IF l_debug = FND_API.G_TRUE THEN
3016 QP_PREQ_GRP.engine_debug('Duplicate adj overridden ');
3017 END IF;--l_debug
3018 IF l_is_ldet_rec_tbl(i) in (QP_PREQ_PUB.G_LDET_ORDER_TYPE,
3019 QP_PREQ_PUB.G_LDET_LINE_TYPE) THEN
3020 IF l_debug = FND_API.G_TRUE THEN
3021 QP_PREQ_GRP.engine_debug('Marking non-overrdn adj '
3022 ||'as deleted ');
3023 END IF;--l_debug
3024 l_status_code_tbl(i) := QP_PREQ_PUB.G_STATUS_DELETED;
3025 l_status_text_tbl(i) := 'DUPLICATE MODIFIER PICKED UP';
3026 END IF;--l_is_ldet_rec_tbl
3027 ELSIF l_updated_flag_tbl(i) = QP_PREQ_PUB.G_YES
3028 and l_dup_updated_flag(l_dup_ind) = QP_PREQ_PUB.G_YES THEN
3032 l_adj_tbl(l_dup_plsql_ind(l_dup_ind)).pricing_status_text
3029 --current adj is overridden
3030 l_adj_tbl(l_dup_plsql_ind(l_dup_ind)).pricing_status_code
3031 := QP_PREQ_PUB.G_STATUS_DELETED;
3033 := 'DUPLICATE MODIFIER PICKED UP';
3034 IF l_debug = FND_API.G_TRUE THEN
3035 QP_PREQ_GRP.engine_debug('Marking prev overdn deltd ');
3036 END IF;--l_debug
3037 --replace l_dup values w/current values
3038 l_dup_updated_flag(l_dup_ind) := l_updated_flag_tbl(i);
3039 l_dup_is_ldet_rec(l_dup_ind) := l_is_ldet_rec_tbl(i);
3040 l_dup_plsql_ind(l_dup_ind) := d;
3041 ELSE --updated_flag is 'N'
3042 --in this case the engine selected record must be applied
3043 --so adj from oe_price_adj needs to be marked as deleted
3044 IF l_debug = FND_API.G_TRUE THEN
3045 QP_PREQ_GRP.engine_debug('Duplicate adj not overridden ');
3046 QP_PREQ_GRP.engine_debug('Prev adj is_ldet: '
3047 ||l_dup_is_ldet_rec(l_dup_ind));
3048 QP_PREQ_GRP.engine_debug('Current adj is_ldet: '
3049 ||l_is_ldet_rec_tbl(i));
3050 END IF;--l_debug
3051 IF l_dup_is_ldet_rec(l_dup_ind) in
3052 (QP_PREQ_PUB.G_LDET_ORDER_TYPE,
3053 QP_PREQ_PUB.G_LDET_LINE_TYPE)
3054 and l_is_ldet_rec_tbl(i) not in
3055 (QP_PREQ_PUB.G_LDET_ORDER_TYPE,
3056 QP_PREQ_PUB.G_LDET_LINE_TYPE) THEN
3057 --mark the current record as deleted as
3058 --it is from oe_price_adj
3059 l_status_code_tbl(i) := QP_PREQ_PUB.G_STATUS_DELETED;
3060 l_status_text_tbl(i) := 'DUPLICATE MODIFIER PICKED UP';
3061 IF l_debug = FND_API.G_TRUE THEN
3062 QP_PREQ_GRP.engine_debug('Marking curr adj deleted ');
3063 END IF;--l_debug
3064 ELSIF l_is_ldet_rec_tbl(i) in
3065 (QP_PREQ_PUB.G_LDET_ORDER_TYPE,
3066 QP_PREQ_PUB.G_LDET_LINE_TYPE)
3067 and l_dup_is_ldet_rec(l_dup_ind) not in
3068 (QP_PREQ_PUB.G_LDET_ORDER_TYPE,
3069 QP_PREQ_PUB.G_LDET_LINE_TYPE) THEN
3070 l_adj_tbl(l_dup_plsql_ind(l_dup_ind)).pricing_status_code
3071 := QP_PREQ_PUB.G_STATUS_DELETED;
3072 l_adj_tbl(l_dup_plsql_ind(l_dup_ind)).pricing_status_text
3073 := 'DUPLICATE MODIFIER PICKED UP';
3074 --replace l_dup values w/current values
3075 l_dup_updated_flag(l_dup_ind) := l_updated_flag_tbl(i);
3076 l_dup_is_ldet_rec(l_dup_ind) := l_is_ldet_rec_tbl(i);
3077 l_dup_plsql_ind(l_dup_ind) := d;
3078 IF l_debug = FND_API.G_TRUE THEN
3079 QP_PREQ_GRP.engine_debug('Marking prev adj deleted ');
3080 END IF;--l_debug
3081 ELSE--both the records have same is_ldet so delete one
3082 l_status_code_tbl(i) := QP_PREQ_PUB.G_STATUS_DELETED;
3083 l_status_text_tbl(i) := 'DUPLICATE MODIFIER PICKED UP';
3084 IF l_debug = FND_API.G_TRUE THEN
3085 QP_PREQ_GRP.engine_debug('Marking else curr adj deltd');
3086 END IF;--l_debug
3087 END IF;--l_is_ldet_rec_tbl(i)
3088 END IF;--l_dup_updated_flag
3089 ELSE
3090 l_dup_updated_flag(l_dup_ind) := l_updated_flag_tbl(i);
3091 l_dup_is_ldet_rec(l_dup_ind) := l_is_ldet_rec_tbl(i);
3092 l_dup_plsql_ind(l_dup_ind) := d;
3093 END IF;--l_dup_updated_flag.exists
3094
3095 */
3096 --added to check for duplicate list_line_ids
3097
3098 l_adj_tbl(d).created_from_list_line_id := l_list_line_id_tbl(i);
3099 l_adj_tbl(d).line_ind := l_line_index_tbl(i);
3100 l_adj_tbl(d).curr_line_index := l_curr_line_index_tbl(i);
3101 l_adj_tbl(d).line_id := l_line_id_tbl(i);
3102 l_adj_tbl(d).created_from_list_header_id :=
3103 l_list_header_id_tbl(i);
3104 l_adj_tbl(d).created_from_list_line_type :=
3105 l_list_line_type_code_tbl(i);
3106 l_adj_tbl(d).applied_flag := l_applied_flag_tbl(i);
3107 l_adj_tbl(d).amount_changed := l_amount_changed_tbl(i);
3108 l_adj_tbl(d).adjusted_unit_price :=
3109 l_adjusted_unit_price_tbl(i);
3110 l_adj_tbl(d).priced_quantity := l_priced_quantity_tbl(i);
3111 --if limits is installed GRP makes line_quantity on ldets 0
3112 --for bug2897524 retain line_quantity for zerounitprice
3113 IF (l_adj_tbl(d).priced_quantity = 0
3114 and l_adj_tbl(d).unit_price <> 0)
3115 THEN
3116 IF l_debug = FND_API.G_TRUE THEN
3117 QP_PREQ_GRP.engine_debug('ldet.line_qty is zero');
3118 END IF;
3119 l_adj_tbl(d).priced_quantity := null;
3120 ELSE
3121 IF l_debug = FND_API.G_TRUE THEN
3122 QP_PREQ_GRP.engine_debug('ldet.line_qty not zero');
3123 END IF;
3124 END IF;
3125 l_adj_tbl(d).line_priced_quantity := l_line_priced_qty_tbl(i);
3126 l_adj_tbl(d).updated_adjusted_unit_price :=
3127 l_upd_adj_unit_price_tbl(i);
3128 l_adj_tbl(d).automatic_flag := l_automatic_flag_tbl(i);
3129 l_adj_tbl(d).override_flag := l_override_flag_tbl(i);
3130 l_adj_tbl(d).pricing_group_sequence :=
3131 l_pricing_group_sequence_tbl(i);
3132 l_adj_tbl(d).operand_calculation_code :=
3133 l_operand_calc_code_tbl(i);
3134 l_adj_tbl(d).operand_value := l_operand_value_tbl(i);
3135 l_adj_tbl(d).adjustment_amount := l_adjustment_amount_tbl(i);
3136 l_adj_tbl(d).unit_price := l_unit_price_tbl(i);
3137 l_adj_tbl(d).accrual_flag := l_accrual_flag_tbl(i);
3138 l_adj_tbl(d).updated_flag := l_updated_flag_tbl(i);
3139 l_adj_tbl(d).pricing_status_code := l_status_code_tbl(i);
3140 l_adj_tbl(d).pricing_status_text := l_status_text_tbl(i);
3141 l_adj_tbl(d).price_break_type_code :=
3142 l_price_break_type_code_tbl(i);
3146 l_adj_tbl(d).rounding_factor :=
3143 l_adj_tbl(d).charge_type_code := l_charge_type_code_tbl(i);
3144 l_adj_tbl(d).charge_subtype_code :=
3145 l_charge_subtype_code_tbl(i);
3147 l_rounding_factor_tbl(i);
3148 l_adj_tbl(d).pricing_phase_id := l_pricing_phase_id_tbl(i);
3149 l_adj_tbl(d).created_from_list_type_code :=
3150 l_list_type_code_tbl(i);
3151 l_adj_tbl(d).limit_code := l_limit_code_tbl(i);
3152 l_adj_tbl(d).limit_text := l_limit_text_tbl(i);
3153 l_adj_tbl(d).list_line_no := l_list_line_no_tbl(i);
3154 l_adj_tbl(d).modifier_level_code := l_modifier_level_tbl(i);
3155 l_adj_tbl(d).group_quantity := l_group_qty_tbl(i);
3156 l_adj_tbl(d).group_amount := l_group_amt_tbl(i);
3157 l_adj_tbl(d).line_pricing_status_code := l_line_sts_code_tbl(i);
3158 l_adj_tbl(d).is_ldet_rec := l_is_ldet_rec_tbl(i);
3159 l_adj_tbl(d).line_type_code := l_line_type_code_tbl(i);
3160
3161 --2388011
3162 l_adj_tbl(d).net_amount_flag := l_net_amount_flag_tbl(i);
3163 --2388011
3164 l_adj_tbl(d).calculation_code := l_calculation_code_tbl(i);
3165 l_adj_tbl(d).catchweight_qty := l_catchweight_qty_tbl(i);
3166 l_adj_tbl(d).actual_order_qty := l_actual_order_qty_tbl(i);
3167 l_adj_tbl(d).line_unit_price := l_line_unit_price_tbl(i);
3168 l_adj_tbl(d).ordered_qty := l_ord_qty_tbl(i);
3169 l_adj_tbl(d).line_category := l_line_category_tbl(i);
3170 l_adj_tbl(d).price_flag := l_price_flag_tbl(i);
3171
3172 IF l_curr_line_index <> l_line_index_tbl(i)
3173 THEN
3174 l_prev_line_start_index := i;
3175 IF l_debug = FND_API.G_TRUE THEN
3176 qp_preq_grp.engine_debug('auto_overr: new line');
3177 END IF; --Bug No 4033618
3178 l_curr_line_index := l_line_index_tbl(i);
3179 x := 0;
3180 l_auto_line_dtl_index_tbl.delete;
3181 l_auto_override_dtl_id_tbl.delete;
3182 END IF;
3183
3184 IF l_curr_line_index = l_line_index_tbl(i)
3185 and l_is_ldet_rec_tbl(i) IN (QP_PREQ_PUB.G_ADJ_ORDER_TYPE,
3186 QP_PREQ_PUB.G_ADJ_LINE_TYPE,
3187 QP_PREQ_PUB.G_ASO_LINE_TYPE,
3188 QP_PREQ_PUB.G_ASO_ORDER_TYPE)
3189 --there w/b duplicate applied adj in the cursor
3190 --l_calculate_cur only in case of automatic overrideable
3191 --adj and manual unapplied adj to filter those
3192 /*
3193 and ((nvl(l_automatic_flag_tbl(i),QP_PREQ_PUB.G_NO) =
3194 QP_PREQ_PUB.G_YES
3195 and nvl(l_updated_flag_tbl(i),QP_PREQ_PUB.G_NO) =
3196 QP_PREQ_PUB.G_YES
3197 and nvl(l_applied_flag_tbl(i),QP_PREQ_PUB.G_NO) =
3198 QP_PREQ_PUB.G_YES)
3199 or
3200 (nvl(l_automatic_flag_tbl(i),QP_PREQ_PUB.G_NO) =
3201 QP_PREQ_PUB.G_YES
3202 and nvl(l_applied_flag_tbl(i),QP_PREQ_PUB.G_NO) =
3203 QP_PREQ_PUB.G_NO))
3204 */
3205 --commented out because we need to look at all updated adj
3206 --and retain the updated(overridden) adj ,delete engine picked
3207 and nvl(l_updated_flag_tbl(i),QP_PREQ_PUB.G_NO) =
3208 QP_PREQ_PUB.G_YES
3209 THEN
3210 x := x+1;
3211 l_auto_line_dtl_index_tbl(x) := l_line_dtl_index_tbl(i);
3212 l_auto_override_dtl_id_tbl(x) := l_list_line_id_tbl(i);
3213 -- l_adj_tbl(d).pricing_status_code :=
3214 -- QP_PREQ_PUB.G_STATUS_UPDATED;
3215 IF l_debug = FND_API.G_TRUE THEN
3216 qp_preq_grp.engine_debug('auto_overr: from adj tbl '
3217 ||l_line_dtl_index_tbl(i)||' adj id '
3218 ||l_list_line_id_tbl(i));
3219 END IF; --Bug No 4033618
3220 ELSIF l_curr_line_index = l_line_index_tbl(i)
3221 and l_is_ldet_rec_tbl(i) in (QP_PREQ_PUB.G_LDET_ORDER_TYPE,
3222 QP_PREQ_PUB.G_LDET_LINE_TYPE)
3223 --commented out because we need to look at all updated adj
3224 --and retain the updated(overridden) adj ,delete engine picked
3225 /*
3226 and ((nvl(l_automatic_flag_tbl(i),QP_PREQ_PUB.G_NO) =
3227 QP_PREQ_PUB.G_YES
3228 and nvl(l_override_flag_tbl(i),QP_PREQ_PUB.G_NO) =
3229 QP_PREQ_PUB.G_YES
3230 and nvl(l_updated_flag_tbl(i),QP_PREQ_PUB.G_NO) =
3231 QP_PREQ_PUB.G_NO)
3232 or (nvl(l_automatic_flag_tbl(i),QP_PREQ_PUB.G_NO) =
3233 QP_PREQ_PUB.G_NO
3234 and nvl(l_applied_flag_tbl(i),QP_PREQ_PUB.G_NO) =
3235 QP_PREQ_PUB.G_NO))
3236 */
3237 THEN
3238 IF l_debug = FND_API.G_TRUE THEN
3239 qp_preq_grp.engine_debug('auto_overr: from ldet tbl '
3240 ||l_adj_tbl(d).created_from_list_line_id
3241 ||' adj index '||l_adj_tbl(d).line_detail_index);
3242 END IF; --Bug No 4033618
3243 m := l_auto_line_dtl_index_tbl.FIRST;
3244 WHILE (m IS NOT NULL
3245 and l_auto_line_dtl_index_tbl.COUNT > 0)
3246 LOOP
3247 IF l_debug = FND_API.G_TRUE THEN
3248 qp_preq_grp.engine_debug('auto_overr: from auto_ov tbl '
3249 ||m||' adj id '||l_auto_override_dtl_id_tbl(m)
3250 ||' dtl index '||l_auto_line_dtl_index_tbl(m));
3251 END IF; --Bug No 4033618
3252 IF l_list_line_id_tbl(i) =
3253 l_auto_override_dtl_id_tbl(m)
3254 THEN
3255 IF l_debug = FND_API.G_TRUE THEN
3256 QP_PREQ_GRP.engine_debug('duplicate hit'
3257 ||' '||l_list_line_id_tbl(i));
3258 END IF;
3259 l_adj_tbl(d).pricing_status_code :=
3260 QP_PREQ_GRP.G_STATUS_DELETED;
3261 l_adj_tbl(d).pricing_status_text :=
3262 'DUPLICATE AUTO-OVERRIDEABLE';
3263 l_auto_override_dtl_id_tbl.delete(m);
3264 l_auto_line_dtl_index_tbl.delete(m);
3268 END IF;
3265 END IF;
3266 m := l_auto_line_dtl_index_tbl.NEXT(m);
3267 END LOOP;
3269
3270 --need to populate pricing_attribute for pbh calculation
3271 --for bug 2388011
3272 IF l_adj_tbl(d).created_from_list_line_type = QP_PREQ_PUB.G_PRICE_BREAK_TYPE
3273 THEN
3274 BEGIN
3275 select pricing_attribute
3276 into QP_PREQ_PUB.G_pbhvolattr_attribute(l_adj_tbl(d).created_from_list_line_id)
3277 from qp_pricing_attributes
3278 where list_line_id =
3279 l_adj_tbl(d).created_from_list_line_id
3280 and excluder_flag='N'; --3607956
3281 EXCEPTION
3282 When OTHERS Then
3283 QP_PREQ_PUB.G_pbhvolattr_attribute(l_adj_tbl(d).created_from_list_line_id) := null;
3284 END;
3285
3286 IF l_debug = FND_API.G_TRUE THEN
3287 QP_PREQ_GRP.engine_debug(' PBH vol attr for list_line_id '
3288 ||l_adj_tbl(d).created_from_list_line_id||' attr '
3289 ||QP_PREQ_PUB.G_pbhvolattr_attribute(l_adj_tbl(d).created_from_list_line_id));
3290 END IF;--l_debug
3291 END IF;--l_adj_tbl(d).created_from_list_line_type
3292
3293 IF nvl(l_adj_tbl(d).is_ldet_rec,QP_PREQ_PUB.G_YES) IN
3294 (QP_PREQ_PUB.G_LDET_LINE_TYPE,QP_PREQ_PUB.G_LDET_ORDER_TYPE
3295 ,QP_PREQ_PUB.G_ASO_LINE_TYPE, QP_PREQ_PUB.G_ASO_ORDER_TYPE)
3296 THEN
3297 l_adj_tbl(d).process_code := QP_PREQ_PUB.G_STATUS_NEW;
3298 l_adj_tbl(d).line_detail_index := l_line_dtl_index_tbl(i);
3299 ELSE
3300 l_adj_tbl(d).process_code := QP_PREQ_PUB.G_STATUS_UPDATED;
3301 --per bug 2846527, when freegood gets repriced, same outofphase adj
3302 --may get selected on old and newfg with line w/same priceadjid
3303 --adding line_index to resolve this
3304
3305 -- Bug 3031108, OM only
3306 -- For order level adj, we apply them on each line, but for purpose
3307 -- of calculating total adj amt, each adj ldet should be inserted with
3308 -- same ldet index. So for each list line ID, we cache the ldet index
3309 -- and reuse when necessary. Only for order-level adjs.
3310 IF QP_PREQ_PUB.G_CHECK_CUST_VIEW_FLAG = 'Y'
3311 and nvl(l_adj_tbl(d).is_ldet_rec,QP_PREQ_PUB.G_YES) = QP_PREQ_PUB.G_ADJ_ORDER_TYPE
3312 THEN
3313 IF G_ORD_LVL_LDET_INDEX.EXISTS(l_adj_tbl(d).created_from_list_line_id) THEN
3314 l_adj_tbl(d).line_detail_index := G_ORD_LVL_LDET_INDEX(l_adj_tbl(d).created_from_list_line_id);
3315 ELSE
3316 G_MAX_DTL_INDEX := G_MAX_DTL_INDEX + 1;
3317 l_adj_tbl(d).line_detail_index := G_MAX_DTL_INDEX;
3318 G_ORD_LVL_LDET_INDEX(l_adj_tbl(d).created_from_list_line_id) := G_MAX_DTL_INDEX;
3319 END IF;
3320 ELSE
3321 -- this is the standard funcionality
3322 G_MAX_DTL_INDEX := G_MAX_DTL_INDEX + 1;
3323 l_adj_tbl(d).line_detail_index := G_MAX_DTL_INDEX;
3324 END IF; -- g_check_cust_view_flag
3325
3326 IF l_adj_tbl(d).created_from_list_line_type =
3327 QP_PREQ_PUB.G_PRICE_BREAK_TYPE THEN
3328 G_PBH_LINE_INDEX(G_PBH_LINE_INDEX.COUNT+1) :=
3329 l_adj_tbl(d).line_ind;
3330 G_PBH_LINE_DTL_INDEX(G_PBH_LINE_INDEX.COUNT) :=
3331 l_adj_tbl(d).line_detail_index;
3332 G_PBH_PRICE_ADJ_ID(G_PBH_LINE_INDEX.COUNT) :=
3333 l_line_dtl_index_tbl(i);
3334 G_PBH_PLSQL_IND(l_line_dtl_index_tbl(i)) :=
3335 G_PBH_LINE_INDEX.COUNT;
3336 END IF;--l_adj_tbl(d).created_from_list_line_type
3337 IF l_debug = FND_API.G_TRUE THEN
3338 QP_PREQ_GRP.engine_debug('figuring out line_dtl_index '
3339 ||l_adj_tbl(d).line_detail_index);
3340 END IF;--l_debug
3341 END IF;--nvl(l_adj_tbl(d).is_ldet_rec
3342
3343
3344
3345 IF l_debug = FND_API.G_TRUE THEN
3346 QP_PREQ_GRP.engine_debug('load tbl'||l_adj_tbl(d).line_detail_index
3347 ||' list_line_id '||l_adj_tbl(d).created_from_list_line_id
3348 ||' list_hdr_id '||l_adj_tbl(d).created_from_list_header_id
3349 ||' line dtl index '||l_adj_tbl(d).line_detail_index
3350 ||' operand '||l_adj_tbl(d).operand_value
3351 ||' pricing sts code '||l_adj_tbl(d).pricing_status_code
3352 ||' is ldet rec '||l_adj_tbl(d).is_ldet_rec);
3353 END IF;
3354 -- i:=l_list_line_id_tbl.NEXT(i);
3355 END LOOP;
3356 END IF;
3357
3358 /*
3359 i := l_adj_tbl.NEXT(l_prev_line_start_index-1);
3360 g := 0;
3361 IF l_debug = FND_API.G_TRUE THEN
3362 QP_PREQ_GRP.engine_debug('last line starts at '||l_prev_line_start_index);
3363 END IF;
3364 WHILE i IS NOT NULL
3365 LOOP
3366 g := g + 1;
3367 l_adj_overflow_tbl(g) := l_adj_tbl(i);
3368 i := l_adj_tbl.NEXT(i);
3369 END LOOP;--l_adj_tbl
3370
3371 l_adj_tbl.delete(l_prev_line_start_index,l_adj_tbl.COUNT);
3372
3373 IF l_adj_tbl.COUNT > 0
3374 and QP_PREQ_GRP.G_DEBUG_ENGINE = FND_API.G_TRUE
3375 THEN
3376 i := l_adj_tbl.first;
3377 WHILE i IS NOT NULL
3378 LOOP
3379 IF l_debug = FND_API.G_TRUE THEN
3380 QP_PREQ_GRP.engine_debug('list_line_id '||l_adj_tbl(i).line_detail_index);--||
3381 END IF;
3382 i := l_adj_tbl.next(i);
3383 END LOOP;
3384 END IF;
3385 */
3386 n:=G_PBH_LINE_DTL_INDEX.FIRST;
3387 WHILE n IS NOT NULL
3388 LOOP
3389 IF l_debug = FND_API.G_TRUE THEN
3390 QP_PREQ_GRP.engine_debug('G_PBH_LINE_DTL_INDEX loop '
3391 ||' lineindex '||G_PBH_LINE_INDEX(n)
3392 ||' price_adj_id '||G_PBH_PRICE_ADJ_ID(n)
3396 END LOOP;
3393 ||' linedtlind '||G_PBH_LINE_DTL_INDEX(n));
3394 END IF;
3395 n:=G_PBH_LINE_DTL_INDEX.NEXT(n);
3397
3398 --this needs to be done only for OM, OC and others will pass PBH relationship
3399 -- IF p_request_type_code = 'ONT' THEN
3400 --bug 3085453 handle pricing availability UI
3401 -- they pass reqtype ONT and insert adj into ldets
3402 IF QP_PREQ_PUB.G_CHECK_CUST_VIEW_FLAG = QP_PREQ_PUB.G_YES THEN
3403 Insert_rltd_lines(p_request_type_code,
3404 p_calculate_flag, p_event_code,
3405 x_return_status, x_return_status_text);
3406 -- END IF;
3407 /*
3408 ELSE
3409 --This will be called for OC and others who will pass the PBH
3410 --and the relationship in which case the relationship needs to be updated
3411 QP_PREQ_PUB.Update_passed_in_pbh(x_return_status, x_return_status_text);
3412 */
3413 END IF;--p_request_type_code
3414
3415 IF x_return_status = FND_API.G_RET_STS_ERROR
3416 THEN
3417 Raise Pricing_Exception;
3418 END IF;
3419
3420 IF l_adj_tbl.COUNT > 0
3421 THEN
3422
3423 QP_PREQ_PUB.calculate_price(p_request_type_code => 'ONT'
3424 ,p_rounding_flag => p_rounding_flag
3425 ,p_view_name => 'ONTVIEW'
3426 ,p_event_code => p_event_code
3427 ,p_adj_tbl => l_adj_tbl
3428 ,x_return_status => x_return_status
3429 ,x_return_status_text => x_return_status_text);
3430 IF x_return_status = FND_API.G_RET_STS_ERROR
3431 THEN
3432 Raise Pricing_Exception;
3433 END IF;
3434 END IF;
3435
3436
3437 END LOOP;
3438
3439 -- IF p_request_type_code = 'ONT'
3440 -- and p_view_name = 'ONTVIEW'
3441 -- THEN
3442 CLOSE l_calculate_cur;
3443 IF l_debug = FND_API.G_TRUE THEN
3444 QP_PREQ_GRP.engine_debug('close cur');
3445 END IF;
3446 -- END IF;
3447
3448 --commenting to do the delete at all times
3449 -- IF p_event_code = ','
3450 -- and p_calculate_flag = QP_PREQ_PUB.G_CALCULATE_ONLY
3451 -- THEN
3452 IF l_debug = FND_API.G_TRUE THEN
3453 QP_PREQ_GRP.engine_debug('deleting related lines');
3454 END IF;
3455 delete from qp_npreq_rltd_lines_tmp
3456 where pricing_status_code = QP_PREQ_PUB.G_STATUS_NEW
3457 and relationship_type_code = QP_PREQ_PUB.G_PBH_LINE
3458 and pricing_status_text = G_CALC_INSERT;
3459 IF l_debug = FND_API.G_TRUE THEN
3460 QP_PREQ_GRP.engine_debug('deleted related lines '||SQL%ROWCOUNT);
3461 END IF;
3462 -- END IF;
3463
3464 --This is to update the status code on the child lines on PBH for
3465 --pricing integ to insert/update the child line info as the parent changes
3466 QP_PREQ_PUB.Update_Child_Break_Lines(x_return_status,
3467 x_return_status_text);
3468
3469 --to eliminate the duplicate manual adjustments in the temp table to prevent cleanup for them bug 2191169
3470 --fix for bug 2515297 not to check applied_flag in this update
3471 UPDATE qp_npreq_ldets_tmp ldet2
3472 set ldet2.pricing_status_code = QP_PREQ_PUB.G_STATUS_DELETED,
3473 pricing_status_text = 'DUPLICATE MANUAL-OVERRIDEABLE'
3474 where pricing_status_code = QP_PREQ_PUB.G_STATUS_NEW
3475 and nvl(ldet2.updated_flag,QP_PREQ_PUB.G_NO) = QP_PREQ_PUB.G_NO
3476 and exists ( select 'X'
3477 from qp_npreq_ldets_tmp ldet
3478 where nvl(ldet.updated_flag,QP_PREQ_PUB.G_NO) =QP_PREQ_PUB.G_YES
3479 and ldet.line_index = ldet2.line_index
3480 and ldet.created_from_list_line_id =
3481 ldet2.created_from_list_line_id);
3482
3483 IF (l_debug = FND_API.G_TRUE) THEN
3484 QP_PREQ_GRP.engine_debug('DUPLICATE MANUAL-OVERR '||SQL%ROWCOUNT);
3485 END IF;
3486
3487 -- 4528043, mark any newly picked-up manual modifiers as 'deleted'
3488 -- to prevent OM from incorrectly displaying them in View Adjs
3489 -- 5413797, but don't delete any manual modifiers that appear new
3490 -- because they were repriced with a different quantity
3491 IF (QP_PREQ_PUB.G_CHECK_CUST_VIEW_FLAG = QP_PREQ_PUB.G_YES and
3492 QP_PREQ_GRP.G_MANUAL_ADJUSTMENTS_CALL_FLAG = QP_PREQ_PUB.G_NO)
3493 THEN
3494 UPDATE qp_npreq_ldets_tmp
3495 SET pricing_status_code = QP_PREQ_PUB.G_STATUS_DELETED
3496 WHERE pricing_status_code = QP_PREQ_PUB.G_STATUS_NEW
3497 AND automatic_flag = QP_PREQ_PUB.G_NO
3498 AND applied_flag = QP_PREQ_PUB.G_NO
3499 and updated_flag = QP_PREQ_PUB.G_NO; -- 5413797
3500
3501 IF (l_debug = FND_API.G_TRUE) THEN
3502 QP_PREQ_GRP.engine_debug(SQL%ROWCOUNT||' new manual modifier(s) marked as DELETED');
3503 END IF;
3504 END IF;
3505
3506 --Fix for bug 2247167 Status on lines is unchanged although
3507 --the adjustments have changed
3508 QP_PREQ_PUB.Update_Line_Status(x_return_status,x_return_status_text);
3509
3510
3511 IF x_return_status = FND_API.G_RET_STS_ERROR
3512 THEN
3513 Raise Pricing_Exception;
3514 END IF;
3515
3516 --fix for bug 2242736 where the selling price is not unit_price
3517 --when engine is called with calculate_only and there are no applied
3518 --adjustments to affect the selling price
3519 --removed the calculate_flag if condition due to bug 2567288
3520 --if the line does not qualify for the only discount anymore, this
3521 --update needs to be done
3522 -- IF p_calculate_flag = QP_PREQ_PUB.G_CALCULATE_ONLY
3523 -- THEN
3524 IF l_debug = FND_API.G_TRUE THEN
3525 QP_PREQ_GRP.engine_debug('calculate_only call check to see'
3526 ||' if there are adjustments');
3527 END IF;
3528 QP_PREQ_PUB.update_unit_price(x_return_status,
3529 x_return_status_text);
3530
3531 IF x_return_status = FND_API.G_RET_STS_ERROR
3532 THEN
3533 Raise Pricing_Exception;
3534 END IF;
3535 -- END IF;--p_calculate_flag
3536
3537
3538 IF l_cleanup_flag = QP_PREQ_PUB.G_YES THEN
3539 --call cleanup of adj only for OM
3540 cleanup_adjustments('ONTVIEW',
3541 p_request_type_code,
3542 l_cleanup_flag,
3543 x_return_status,
3544 x_return_status_text);
3545 IF x_return_status = FND_API.G_RET_STS_ERROR
3546 THEN
3547 Raise Pricing_Exception;
3548 END IF;
3549 END IF;--l_cleanup_flag
3550
3551 --populate the price adjustment id from sequence for rec with process_code = N
3552 -- IF p_request_type_code = 'ONT' THEN
3553 --bug 3085453 handle pricing availability UI
3554 -- they pass reqtype ONT and insert adj into ldets
3555 IF QP_PREQ_PUB.G_CHECK_CUST_VIEW_FLAG = QP_PREQ_PUB.G_YES THEN
3556 Populate_Price_Adj_ID(x_return_status,x_return_status_text);
3557
3558 IF x_return_status = FND_API.G_RET_STS_ERROR
3559 THEN
3560 Raise Pricing_Exception;
3561 END IF;
3562 END IF;--QP_PREQ_PUB.G_REQUEST_TYPE_CODE
3563
3564 --gsa violation check
3565 IF QP_PREQ_PUB.G_GSA_INDICATOR = QP_PREQ_PUB.G_NO
3566 THEN
3567 QP_PREQ_PUB.check_gsa_violation(
3568 x_return_status => x_return_status,
3569 x_return_status_text => x_return_status_text);
3570 IF x_return_status = FND_API.G_RET_STS_ERROR
3571 THEN
3572 Raise Pricing_Exception;
3573 END IF;
3574 END IF;--GSA_INDICATOR
3575
3576
3577
3578 IF l_debug = FND_API.G_TRUE THEN
3579 QP_PREQ_GRP.engine_debug('end fetch adjustments');
3580
3581
3582 END IF;
3583 EXCEPTION
3584 When PRICING_EXCEPTION Then
3585 x_return_status := FND_API.G_RET_STS_ERROR;
3586 IF l_debug = FND_API.G_TRUE THEN
3587 QP_PREQ_GRP.engine_debug('error in fetch_adjustments: '||SQLERRM);
3588 QP_PREQ_GRP.engine_debug('error in fetch_adjustments: '||x_return_status_text);
3589 END IF;
3590 When OTHERS Then
3591 x_return_status := FND_API.G_RET_STS_ERROR;
3592 x_return_status_text := 'Error in fetch_adjustments: '||SQLERRM;
3593 IF l_debug = FND_API.G_TRUE THEN
3594 QP_PREQ_GRP.engine_debug('error in fetch_adjustments: '||SQLERRM);
3595 END IF;
3596 END fetch_adjustments;
3597
3598
3599
3600
3601 END QP_CLEANUP_ADJUSTMENTS_PVT;