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