1 PACKAGE BODY QP_Resolve_Incompatability_PVT AS
2 /* $Header: QPXVINCB.pls 120.21.12020000.2 2012/07/04 12:16:42 smbalara ship $ */
3
4 L_PRICE_LIST_PHASE_ID CONSTANT NUMBER := 1;
5 l_debug VARCHAR2(3) ;
6
7 FUNCTION Precedence_For_List_Line(p_list_header_id NUMBER,
8 p_list_line_id NUMBER,
9 p_incomp_grp_id VARCHAR2,
10 p_line_index NUMBER,
11 p_pricing_phase_id NUMBER)
12 RETURN NUMBER IS
13
14 v_number number;
15
16 V_QUALIFIER_TYPE CONSTANT VARCHAR2(240) := 'QUALIFIER';
17 /*
18
19 INDX,QP_Resolve_Incompatability_PVTRUN.precedence_for_list_line.precedence_for_line_cur,qp_npreq_line_attrs_tmp_N1,LINE_INDEX,1
20 INDX,QP_Resolve_Incompatability_PVTRUN.precedence_for_list_line.precedence_for_line_cur,qp_npreq_line_attrs_tmp_N1,ATTRIBUTE_TYPE,2
21 INDX,QP_Resolve_Incompatability_PVTRUN.precedence_for_list_line.precedence_for_line_cur,qp_npreq_line_attrs_tmp_N1,PRICING_STATUS_CODE,3
22 INDX,QP_Resolve_Incompatability_PVTRUN.precedence_for_list_line.precedence_for_line_cur,qp_npreq_line_attrs_tmp_N1,LIST_HEADER_ID,4
23 INDX,QP_Resolve_Incompatability_PVTRUN.precedence_for_list_line.precedence_for_line_cur,qp_npreq_line_attrs_tmp_N1,LIST_LINE_ID,5
24
25 UNION
26 INDX,QP_Resolve_Incompatability_PVTRUN.precedence_for_list_line.precedence_for_line_cur,qp_npreq_ldets_tmp_N1,LINE_INDEX,1
27 INDX,QP_Resolve_Incompatability_PVTRUN.precedence_for_list_line.precedence_for_line_cur,qp_npreq_ldets_tmp_N1,PRICING_STATUS_CODE,2
28 INDX,QP_Resolve_Incompatability_PVTRUN.precedence_for_list_line.precedence_for_line_cur,qp_npreq_ldets_tmp_N1,PRICING_PHASE_ID,3
29 INDX,QP_Resolve_Incompatability_PVTRUN.precedence_for_list_line.precedence_for_line_cur,qp_npreq_ldets_tmp_N1,CREATED_FROM_LIST_HEADER_ID,4
30 INDX,QP_Resolve_Incompatability_PVTRUN.precedence_for_list_line.precedence_for_line_cur,qp_npreq_ldets_tmp_N1,CREATED_FROM_LIST_LINE_ID,5
31
32 */
33 --Precedence for line cur
34 -- Pricing Phase Change
35 CURSOR precedence_for_line_cur IS
36 SELECT nvl(a.QUALIFIER_PRECEDENCE,5000) PRECED
37 FROM qp_npreq_line_attrs_tmp a
38 WHERE a.LIST_LINE_ID = p_list_line_id
39 AND a.LIST_HEADER_ID = p_list_header_id
40 AND a.ATTRIBUTE_TYPE = 'QUALIFIER'
41 AND a.INCOMPATABILITY_GRP_CODE = p_incomp_grp_id
42 AND a.PRICING_PHASE_ID = p_pricing_phase_id
43 AND a.PRICING_STATUS_CODE = 'N'
44 AND a.LINE_INDEX = p_line_index
45 UNION
46 SELECT nvl(a.PRODUCT_PRECEDENCE,5000) PRECED
47 FROM qp_npreq_ldets_tmp a
48 WHERE a.CREATED_FROM_LIST_LINE_ID = p_list_line_id
49 AND a.CREATED_FROM_LIST_HEADER_ID = p_list_header_id
50 AND a.INCOMPATABILITY_GRP_CODE = p_incomp_grp_id
51 AND a.PRICING_PHASE_ID = p_pricing_phase_id
52 AND a.PRICING_STATUS_CODE = 'N'
53 AND a.LINE_INDEX = p_line_index
54 ORDER BY 1;
55
56 BEGIN
57
58 OPEN precedence_for_line_cur;
59 FETCH precedence_for_line_cur INTO v_number;
60 CLOSE precedence_for_line_cur;
61
62 RETURN v_number;
63
64 END Precedence_For_List_Line;
65
66 -- Used only for precedence processing of price list lines
67 PROCEDURE Precedence_For_Price_List_Line(p_line_index NUMBER,
68 p_order_uom_code VARCHAR2,
69 p_primary_uom_flag VARCHAR2,
70 x_precedence_tbl OUT NOCOPY precedence_tbl_type,
71 x_return_status OUT NOCOPY VARCHAR2,
72 x_return_status_text OUT NOCOPY VARCHAR2) IS
73
74
75 CURSOR get_product_details_cur(p_list_line_id number) IS
76 SELECT product_uom_code,attribute,value_from inventory_item_id
77 FROM qp_npreq_line_attrs_tmp a
78 WHERE a.LIST_LINE_ID = p_list_line_id
79 AND a.LINE_INDEX = p_line_index
80 AND a.ATTRIBUTE_TYPE = 'PRODUCT'
81 AND a.PRICING_PHASE_ID = 1
82 AND a.PRICING_STATUS_CODE = 'N';
83
84 CURSOR get_inventory_item_id_cur(p_line_index NUMBER) IS
85 SELECT value_from
86 FROM qp_npreq_line_attrs_tmp
87 WHERE line_index = p_line_index
88 AND attribute_type = QP_PREQ_GRP.G_PRODUCT_TYPE
89 AND context = 'ITEM'
90 AND attribute = 'PRICING_ATTRIBUTE1'
91 AND pricing_status_code = QP_PREQ_GRP.G_STATUS_UNCHANGED;
92
93 l_product_uom_code VARCHAR2(30);
94 l_attribute VARCHAR2(30);
95 l_inventory_item_id VARCHAR2(30);
96 l_list_line_id NUMBER;
97
98 g_precedence_tbl QP_PREQ_GRP.NUMBER_TYPE;
99 g_qual_precedence_tbl QP_PREQ_GRP.NUMBER_TYPE;
100 g_list_line_id_tbl QP_PREQ_GRP.NUMBER_TYPE;
101 v_number NUMBER;
102 v_routine_name CONSTANT VARCHAR2(40) := 'Precedence_For_Price_List_Line';
103
104 type refcur is ref cursor;
105
106 l_precedence_for_line_cur refcur;
107
108 l_prev_precedence NUMBER := -9999;
109 l_prev_qual_precedence NUMBER := -9999;
110 v_counter NUMBER := 0;
111 l_exit_status VARCHAR2(1) := 'F';
112 nROWS number := 1000;
113
114 BEGIN
115 l_debug := QP_PREQ_GRP.G_DEBUG_ENGINE;
116
117 IF (p_order_uom_code IS NOT NULL) THEN
118
119 IF l_debug = FND_API.G_TRUE THEN
120 QP_PREQ_GRP.engine_debug('Opening Order Uom Cur : ' || p_order_uom_code);
121
122 END IF;
123 -- Method to derive QUAL_PRECED
124 --In first select of 2 way UNION , there is a qualifier on primary price list , it will be -99999 as prim pl as high preced
125 --In second select of 2 way UNION , if there is no secondary price list , so QUAL_PRECED is defaulted to -99999,since it is
126 --coming from primary price list and not secondary price list and it should have more precedence
127
128 -- shulin
129 -- uom match comdition is added, when order_uom does not match product uom (uom conversion should happen)
130 -- this cursor should not return list_line_id, which will cause UOM conversion not happening
131 -- Bug 2490074 Join with qp_npreq_ldets_tmp is added in 1st part of union to pick only undeleted lines.
132 -- Bug 2687089 Commented exists and addded join with qp_npreq_line_attrs_tmp
133 OPEN l_precedence_for_line_cur FOR
134 SELECT distinct nvl(a.QUALIFIER_PRECEDENCE,5000) PRECED , a.LIST_LINE_ID , -99999 QUAL_PRECED
135 FROM qp_npreq_line_attrs_tmp a , qp_npreq_ldets_tmp c , qp_npreq_line_attrs_tmp b
136 WHERE a.ATTRIBUTE_TYPE = 'QUALIFIER'
137 AND a.INCOMPATABILITY_GRP_CODE = 'EXCL'
138 AND a.PRICING_PHASE_ID = 1
139 AND a.PRICING_STATUS_CODE = 'N'
140 AND a.LINE_INDEX = p_line_index
141 AND b.PRICING_STATUS_CODE = 'N'
142 AND b.LINE_INDEX = p_line_index
143 AND b.INCOMPATABILITY_GRP_CODE = 'EXCL'
144 AND b.PRICING_PHASE_ID = 1
145 AND b.ATTRIBUTE_TYPE = QP_PREQ_GRP.G_PRODUCT_TYPE
146 AND b.PRODUCT_UOM_CODE = p_order_uom_code
147 AND b.LINE_DETAIL_TYPE_CODE <> QP_PREQ_GRP.G_CHILD_DETAIL_TYPE
148 AND b.list_line_id = a.LIST_LINE_ID
149 /* AND EXISTS (SELECT 'X' -- uom match, shulin
150 FROM qp_npreq_line_attrs_tmp b
151 WHERE LINE_INDEX = p_line_index
152 AND PRICING_STATUS_CODE = 'N'
153 AND INCOMPATABILITY_GRP_CODE = 'EXCL'
154 AND PRICING_PHASE_ID = 1
155 AND ATTRIBUTE_TYPE = QP_PREQ_GRP.G_PRODUCT_TYPE
156 AND PRODUCT_UOM_CODE = p_order_uom_code
157 AND LINE_DETAIL_TYPE_CODE <> QP_PREQ_GRP.G_CHILD_DETAIL_TYPE
158 AND a.LIST_LINE_ID = b.LIST_LINE_ID) */
159 AND a.LINE_DETAIL_INDEX = c.LINE_DETAIL_INDEX
160 AND c.PRICING_STATUS_CODE = QP_PREQ_GRP.G_STATUS_NEW
161
162 UNION
163 SELECT z.PRECED , z.LIST_LINE_ID , z.QUALIFIER_PRECEDENCE QUAL_PRECED
164 FROM (
165 SELECT /*+ ORDERED USE_NL(b) index(c qp_preq_line_attrs_tmp_N5) rec_exist_with_order_uom_cur */
166 nvl(b.PRODUCT_PRECEDENCE,5000) PRECED , c.LIST_LINE_ID , nvl(c.QUALIFIER_PRECEDENCE,-99999) QUALIFIER_PRECEDENCE
167 FROM qp_npreq_line_attrs_tmp c,qp_npreq_ldets_tmp b
168 WHERE c.PRICING_PHASE_ID = L_PRICE_LIST_PHASE_ID
169 AND c.PRODUCT_UOM_CODE = p_order_uom_code
170 AND c.ATTRIBUTE_TYPE = QP_PREQ_GRP.G_PRODUCT_TYPE
171 AND c.LINE_INDEX = p_line_index
172 AND c.INCOMPATABILITY_GRP_CODE = 'EXCL'
173 AND c.LINE_DETAIL_TYPE_CODE <> QP_PREQ_GRP.G_CHILD_DETAIL_TYPE
174 AND c.LINE_DETAIL_INDEX = b.LINE_DETAIL_INDEX
175 AND b.PRICING_STATUS_CODE = QP_PREQ_GRP.G_STATUS_NEW
176 AND c.PRICING_STATUS_CODE = QP_PREQ_GRP.G_STATUS_NEW
177 ORDER BY 3,1) z
178 ORDER BY 3,1;
179 ELSE
180
181 IF l_debug = FND_API.G_TRUE THEN
182 QP_PREQ_GRP.engine_debug('Opening Primary Uom Cur');
183
184 END IF;
185 /* Modified cursor l_precedence_for_line_cur for 2780293 */
186 OPEN l_precedence_for_line_cur FOR
187 SELECT nvl(a.QUALIFIER_PRECEDENCE,5000) PRECED , a.LIST_LINE_ID , -99999 QUAL_PRECED
188 FROM qp_npreq_line_attrs_tmp a, qp_npreq_ldets_tmp c, qp_npreq_line_attrs_tmp b
189 WHERE a.ATTRIBUTE_TYPE = 'QUALIFIER'
190 AND a.INCOMPATABILITY_GRP_CODE = 'EXCL'
191 AND a.PRICING_PHASE_ID = 1
192 AND a.PRICING_STATUS_CODE = 'N'
193 AND a.LINE_INDEX = p_line_index
194 AND b.PRICING_STATUS_CODE = 'N'
195 AND b.LINE_INDEX = p_line_index
196 AND b.INCOMPATABILITY_GRP_CODE = 'EXCL'
197 AND b.PRICING_PHASE_ID = 1
198 AND b.ATTRIBUTE_TYPE = QP_PREQ_GRP.G_PRODUCT_TYPE
199 AND b.PRIMARY_UOM_FLAG = QP_PREQ_GRP.G_YES
200 AND b.LINE_DETAIL_TYPE_CODE <> QP_PREQ_GRP.G_CHILD_DETAIL_TYPE
201 AND b.list_line_id = a.LIST_LINE_ID
202 AND a.LINE_DETAIL_INDEX = c.LINE_DETAIL_INDEX
203 AND c.PRICING_STATUS_CODE = QP_PREQ_GRP.G_STATUS_NEW
204
205
206 UNION
207 SELECT z.PRECED , z.LIST_LINE_ID , z.QUALIFIER_PRECEDENCE QUAL_PRECED
208 FROM(
209 SELECT /*+ ORDERED USE_NL(c d) index(b qp_preq_line_attrs_tmp_N5) rec_exist_with_pri_flag_cur */
210 nvl(c.PRODUCT_PRECEDENCE,5000) PRECED , b.LIST_LINE_ID , nvl(b.QUALIFIER_PRECEDENCE ,-99999) QUALIFIER_PRECEDENCE
211 FROM qp_npreq_line_attrs_tmp b,qp_npreq_ldets_tmp c
212 WHERE b.PRIMARY_UOM_FLAG = QP_PREQ_GRP.G_YES
213 AND b.PRICING_STATUS_CODE = QP_PREQ_GRP.G_STATUS_NEW
214 AND b.CONTEXT = 'ITEM'
215 AND b.ATTRIBUTE_TYPE = QP_PREQ_GRP.G_PRODUCT_TYPE
216 AND b.LINE_DETAIL_TYPE_CODE <> QP_PREQ_GRP.G_CHILD_DETAIL_TYPE
217 AND b.INCOMPATABILITY_GRP_CODE = 'EXCL'
218 AND b.LINE_INDEX = p_line_index
219 AND b.PRICING_PHASE_ID = L_PRICE_LIST_PHASE_ID
220 AND c.LINE_DETAIL_INDEX = b.LINE_DETAIL_INDEX
221 AND c.PRICING_STATUS_CODE = QP_PREQ_GRP.G_STATUS_NEW
222 ORDER BY 3,1) z
223 ORDER BY 3,1;
224 END IF;
225
226 LOOP -- Outer Main Loop
227
228 g_precedence_tbl.delete;
229 g_qual_precedence_tbl.delete;
230 g_list_line_id_tbl.delete;
231
232 FETCH l_precedence_for_line_cur BULK COLLECT INTO g_precedence_tbl,g_list_line_id_tbl,g_qual_precedence_tbl LIMIT nROWS;
233 EXIT WHEN g_list_line_id_tbl.count = 0;
234
235 IF (g_list_line_id_tbl.count > 0) THEN
236
237 IF l_debug = FND_API.G_TRUE THEN
238 QP_PREQ_GRP.engine_debug('List Line Table Count: ' || g_list_line_id_tbl.count);
239
240 END IF;
241 FOR j in g_list_line_id_tbl.first .. g_list_line_id_tbl.last -- Inner Loop
242 LOOP
243
244 IF (v_counter=0) THEN
245 l_prev_precedence := g_precedence_tbl(j);
246 l_prev_qual_precedence := g_qual_precedence_tbl(j);
247 END IF;
248
249 IF l_debug = FND_API.G_TRUE THEN
250 QP_PREQ_GRP.engine_debug('High Qual Preced Is : ' || g_qual_precedence_tbl(j));
251 QP_PREQ_GRP.engine_debug('High Prod Preced Is : ' || g_precedence_tbl(j) || ' List Line Id: ' || g_list_line_id_tbl(j));
252
253 END IF;
254 -- Load all list lines with same precedence and if different exit out
255 -- On change of either qualifier and product precedence stop
256 --Ex: Qual Preced :0 Prod Preced:220
257 -- Qual Preced :0 Prod Preced:315 we should not process this record
258 IF (l_prev_qual_precedence = g_qual_precedence_tbl(j) and l_prev_precedence = g_precedence_tbl(j)) THEN
259
260 x_precedence_tbl(j).created_from_list_line_id := g_list_line_id_tbl(j);
261 x_precedence_tbl(j).product_precedence := g_precedence_tbl(j);
262 v_counter := v_counter + 1;
263
264 IF (p_primary_uom_flag IS NOT NULL) THEN -- pri uom cur
265 l_product_uom_code := NULL;
266 OPEN get_product_details_cur(g_list_line_id_tbl(j));
267 FETCH get_product_details_cur INTO l_product_uom_code,l_attribute,l_inventory_item_id;
268 CLOSE get_product_details_cur;
269
270 x_precedence_tbl(j).product_uom_code := l_product_uom_code;
271
272 IF (l_attribute <> 'PRICING_ATTRIBUTE1') THEN
273 l_inventory_item_id := NULL; -- Reset
274 OPEN get_inventory_item_id_cur(p_line_index);
275 FETCH get_inventory_item_id_cur INTO l_inventory_item_id;
276 CLOSE get_inventory_item_id_cur;
277
278 IF l_debug = FND_API.G_TRUE THEN
279 QP_PREQ_GRP.engine_debug('Attribute Is : ' || l_attribute);
280 QP_PREQ_GRP.engine_debug('Inventory Item Id For Item Specific : ' || l_inventory_item_id);
281
282 END IF;
283 IF (l_inventory_item_id IS NULL) THEN
284 IF l_debug = FND_API.G_TRUE THEN
285 QP_PREQ_GRP.engine_debug('Could not find Item Specific. Doing generic conversion');
286 END IF;
287 x_precedence_tbl(j).inventory_item_id := 0;
288 ELSE
289 x_precedence_tbl(j).inventory_item_id := l_inventory_item_id;
290 END IF;
291
292 ELSE -- IF (l_attribute <> 'PRICING_ATTRIBUTE1')
293 IF l_debug = FND_API.G_TRUE THEN
294 QP_PREQ_GRP.engine_debug('Inventory Item Id For Item: ' || l_inventory_item_id);
295 END IF;
296 x_precedence_tbl(j).inventory_item_id := l_inventory_item_id;
297 END IF; -- IF (l_attribute <> 'PRICING_ATTRIBUTE1')
298
299 END IF; -- (p_primary_uom_flag IS NOT NULL)
300 ELSE -- l_prev_precedence = g_precedence_tbl(j)
301 IF l_debug = FND_API.G_TRUE THEN
302 QP_PREQ_GRP.engine_debug('Exiting the inner loop after loading the higher precedence list lines');
303 END IF;
304 l_exit_status := 'T';
305 EXIT; -- Inner Loop
306 END IF; -- l_prev_precedence = g_precedence_tbl(j)
307
308 END LOOP;
309
310 IF (l_exit_status = 'T') THEN
311 IF l_debug = FND_API.G_TRUE THEN
312 QP_PREQ_GRP.engine_debug('Exiting the outer loop after loading the higher precedence list lines');
313 END IF;
314 EXIT; -- Outer Main Loop
315 END IF;
316
317 END IF; -- G_LIST_LINE_ID_TBL.COUNT > 0
318
319 END LOOP; -- Main Loop
320 CLOSE l_precedence_for_line_cur;
321
322 IF (x_precedence_tbl.COUNT > 0) THEN
323 FOR k in x_precedence_tbl.FIRST .. x_precedence_tbl.LAST
324 LOOP
325 IF l_debug = FND_API.G_TRUE THEN
326 QP_PREQ_GRP.engine_debug('PPL List Line Id : ' || x_precedence_tbl(k).created_from_list_line_id);
327 QP_PREQ_GRP.engine_debug('PPL Precedence : ' || x_precedence_tbl(k).product_precedence);
328 QP_PREQ_GRP.engine_debug('PPL Inventory Item Id: ' || x_precedence_tbl(k).inventory_item_id);
329 QP_PREQ_GRP.engine_debug('PPL Product Uom : ' || x_precedence_tbl(k).product_uom_code);
330 END IF;
331 END LOOP;
332 END IF;
333
334 EXCEPTION
335 WHEN OTHERS THEN
336 IF l_debug = FND_API.G_TRUE THEN
337 QP_PREQ_GRP.engine_debug('In Routine Precedence_For_Price_List_Line : ' || SQLERRM);
338 END IF;
339 x_return_status := FND_API.G_RET_STS_ERROR;
340 x_return_status_text := v_routine_name || ' ' || SQLERRM;
341 END Precedence_For_Price_List_Line;
342
343 Procedure sort_on_precedence(p_sorted_tbl in out Nocopy precedence_tbl_type) Is
344
345 h PLS_INTEGER:=1;
346 i PLS_INTEGER;
347 j PLS_INTEGER;
348 N PLS_INTEGER;
349
350 l_Sorted_Precedence_Rec precedence_rec_type;
351
352 Begin
353 N := p_sorted_tbl.count;
354 --DBMS_OUTPUT.PUT_LINE('Determining h step size...');
355
356 For k in 1..N Loop
357 h:= h*3 + 1;
358 exit when h*3 + 1 > N;
359 End Loop;
360
361 --DBMS_OUTPUT.PUT_LINE('h: '||h);
362
363 For k in 1..h Loop
364 --DBMS_OUTPUT.PUT_LINE('h2:'||h);
365 i:= h + 1;
366 For i in h+1..N Loop
367 l_Sorted_Precedence_Rec := p_sorted_tbl(i);
368 j:=i;
369 While ((j > h) and (nvl(p_sorted_tbl(j-h).product_precedence, -1) > nvl(l_Sorted_Precedence_Rec.product_precedence,-1)))
370 Loop
371 p_sorted_tbl(j) := p_sorted_tbl(j-h);
372 j:=j-h;
373 End Loop;
374 p_sorted_tbl(j):=l_Sorted_Precedence_Rec;
375 End Loop;
376 h:= h/3;
377 Exit When h < 1;
378 End Loop;
379
380 End sort_on_precedence;
381
382
383 PROCEDURE Update_Invalid_List_Lines(p_incomp_grp_code VARCHAR2,
384 p_line_index NUMBER,
385 p_pricing_phase_id NUMBER,
386 p_status_code VARCHAR2,
387 p_status_text VARCHAR2,
388 x_return_status OUT NOCOPY VARCHAR2,
389 x_return_status_text OUT NOCOPY VARCHAR2) AS
390
391 v_routine_name CONSTANT VARCHAR2(240):='Routine:QP_Resolve_Incompatability_PVTRUN.Update_Invalid_List_Lines';
392 BEGIN
393
394 /*
395 INDX,QP_Resolve_Incompatability_PVTRUN.update_invalid_list_lines.upd1,qp_npreq_lines_tmp_N1,LINE_INDEX,1
396 INDX,QP_Resolve_Incompatability_PVTRUN.update_invalid_list_lines.upd2,qp_npreq_lines_tmp_N1,LINE_INDEX,1
397 */
398
399 IF p_status_code IN (QP_PREQ_GRP.G_STATUS_DUP_PRICE_LIST,QP_PREQ_GRP.G_STATUS_INVALID_UOM_CONV) THEN
400 UPDATE qp_npreq_lines_tmp -- upd1
401 SET PRICING_STATUS_CODE = p_status_code,
402 PRICING_STATUS_TEXT = p_status_text,
403 PROCESSED_CODE = NULL -- To prevent big search from hapenning , if failed in mini search due to above reasons
404 WHERE LINE_INDEX = p_line_index;
405 ELSIF (p_status_code = QP_PREQ_GRP.G_STATUS_INVALID_UOM) THEN
406 UPDATE qp_npreq_lines_tmp -- upd2
407 SET PROCESSED_CODE = QP_PREQ_GRP.G_STS_LHS_NOT_FOUND,
408 PRICING_STATUS_CODE = p_status_code,
409 PRICING_STATUS_TEXT = p_status_text
410 WHERE LINE_INDEX = p_line_index;
411 END IF;
412
413 /*
414 INDX,QP_Resolve_Incompatability_PVTRUN.update_invalid_list_lines.upd3,qp_npreq_ldets_tmp_N1,LINE_INDEX,1
415 INDX,QP_Resolve_Incompatability_PVTRUN.update_invalid_list_lines.upd3,qp_npreq_ldets_tmp_N1,PRICING_STATUS_CODE,2
416 INDX,QP_Resolve_Incompatability_PVTRUN.update_invalid_list_lines.upd3,qp_npreq_ldets_tmp_N1,PRICING_PHASE_ID,3
417 */
418
419 UPDATE qp_npreq_ldets_tmp -- upd3
420 SET PRICING_STATUS_CODE = p_status_code
421 WHERE INCOMPATABILITY_GRP_CODE = p_incomp_grp_code
422 AND LINE_INDEX = p_line_index
423 AND PRICING_PHASE_ID = p_pricing_phase_id
424 AND PRICING_STATUS_CODE = QP_PREQ_GRP.G_STATUS_NEW;
425
426 /* UPDATE qp_npreq_line_attrs_tmp a
427 SET a.PRICING_STATUS_CODE = p_status_code
428 WHERE a.LIST_LINE_ID
429 IN (SELECT b.CREATED_FROM_LIST_LINE_ID
430 FROM qp_npreq_ldets_tmp b
431 WHERE b.PRICING_STATUS_CODE = p_status_code
432 AND b.PRICING_PHASE_ID = p_pricing_phase_id
433 AND b.INCOMPATABILITY_GRP_CODE = p_incomp_grp_code
434 AND b.LINE_INDEX = p_line_index)
435 AND a.LINE_INDEX = p_line_index; */
436
437 EXCEPTION
438 WHEN OTHERS THEN
439 x_return_status := FND_API.G_RET_STS_ERROR;
440 x_return_status_text := v_routine_name || ' ' || SQLERRM;
441 END Update_Invalid_List_lines;
442
443
444 PROCEDURE Determine_Pricing_UOM_And_Qty(p_line_index NUMBER,
445 p_order_uom_code VARCHAR2,
446 p_order_qty NUMBER,
447 p_pricing_phase_id NUMBER,
448 p_call_big_search BOOLEAN,
449 x_list_line_id OUT NOCOPY NUMBER,
450 x_return_status OUT NOCOPY VARCHAR2,
451 x_return_status_txt OUT NOCOPY VARCHAR2) IS
452
453 /*
454 INDX,QP_Resolve_Incompatability_PVTRUN.determine_pricing_uom_and_qty.incomp_cur,qp_npreq_ldets_tmp_N2,PRICING_PHASE_ID,1
455 INDX,QP_Resolve_Incompatability_PVTRUN.determine_pricing_uom_and_qty.incomp_cur,qp_npreq_ldets_tmp_N2,PRICING_STATUS_CODE,2
456 INDX,QP_Resolve_Incompatability_PVTRUN.determine_pricing_uom_and_qty.incomp_cur,qp_npreq_lines_tmp_N2,LINE_INDEX,3
457 */
458 --shulin bug 1829731, add contract_start_date, contract_end_date
459 --[julin/pricebook] added hint for qp_npreq_ldets_tmp
460 CURSOR incomp_cur IS
461 SELECT /*+ ORDERED USE_NL(a) INDEX(a QP_PREQ_LDETS_TMP_N2) */
462 DISTINCT a.LINE_INDEX , a.INCOMPATABILITY_GRP_CODE ,a.PRICING_STATUS_CODE,b.LINE_UOM_CODE,b.LINE_QUANTITY,
463 b.PROCESSING_ORDER,b.UOM_QUANTITY, b.CONTRACT_START_DATE, b.CONTRACT_END_DATE
464 FROM qp_npreq_lines_tmp b , qp_npreq_ldets_tmp a
465 WHERE a.INCOMPATABILITY_GRP_CODE IS NOT NULL
466 AND a.PRICING_PHASE_ID = p_pricing_phase_id
467 AND a.PRICING_STATUS_CODE = QP_PREQ_GRP.G_STATUS_NEW
468 AND a.LINE_INDEX = b.LINE_INDEX
469 --AND b.PRICING_STATUS_CODE = QP_PREQ_GRP.G_STATUS_UNCHANGED
470 ORDER BY a.LINE_INDEX;
471
472 /*
473 INDX,QP_Resolve_Incompatability_PVTRUN.determine_pricing_uom_and_qty.get_attribute_count_cur,qp_npreq_line_attrs_tmp_N1,LINE_INDEX,1
474 INDX,QP_Resolve_Incompatability_PVTRUN.determine_pricing_uom_and_qty.get_attribute_count_cur,qp_npreq_line_attrs_tmp_N1,ATTRIBUTE_TYPE,2
475 INDX,QP_Resolve_Incompatability_PVTRUN.determine_pricing_uom_and_qty.get_attribute_count_cur,qp_npreq_line_attrs_tmp_N1,PRICING_STATUS_CODE,3
476 */
477
478 CURSOR get_attribute_count_cur(p_line_index NUMBER , p_list_line_id NUMBER) IS
479 SELECT COUNT(*) ATTRIBUTE_COUNT
480 FROM qp_npreq_line_attrs_tmp
481 WHERE LIST_LINE_ID = p_list_line_id
482 AND LINE_INDEX = p_line_index
483 AND ATTRIBUTE_TYPE = QP_PREQ_GRP.G_PRICING_TYPE
484 AND PRICING_STATUS_CODE = QP_PREQ_GRP.G_STATUS_NEW;
485
486
487
488 v_list_line_id NUMBER;
489 v_price_list VARCHAR2(240);
490 v_list_precedence NUMBER;
491 v_primary_uom_code VARCHAR2(30);
492 v_pricing_qty NUMBER;
493 v_inventory_item_id NUMBER;
494 v_uom_rate NUMBER;
495 v_dup_list_line_id NUMBER;
496 v_dup_price_list VARCHAR2(240);
497 v_old_precedence NUMBER;
498 v_count NUMBER := 0;
499 v_return_status VARCHAR2(30) := FND_API.G_RET_STS_SUCCESS;
500 v_return_msg VARCHAR2(240);
501 v_precedence NUMBER;
502 v_counter NUMBER := 0;
503 l_id VARCHAR2(30);
504 l_status VARCHAR2(30);
505 v_return_status_text VARCHAR2(200);
506 v_list_line_count1 NUMBER;
507 v_list_line_count2 NUMBER;
508 l_order_uom_code VARCHAR2(30);
509 v_total_count NUMBER;
510
511 l_validate_qty_rtn_status varchar2(1):= NULL;
512 l_output_qty number;
513 l_primary_qty number;
514
515 -- shulin bug 1781829 fix
516 l_max_decimal_digits PLS_INTEGER := nvl(FND_PROFILE.Value ('QP_INV_DECIMAL_PRECISION'),10);
517
518 --begin shulin bug 1829731, add profile qp_time_uom_conversion
519 --l_qp_time_uom_conversion := nvl(FND_PROFILE.Value('QP_TIME_UOM_CONVERSION'), 'STANDARD');
520 l_qp_time_uom_conversion VARCHAR2(20) := 'STANDARD'; --jhkuo
521 l_oks_qty NUMBER := NULL;
522 l_uom_quantity NUMBER := NULL;
523 l_sql_stmt varchar2(240) := NULL;
524 l_order_qty NUMBER := NULL; -- shu_latest
525 -- end shulin bug 1829731
526 l_date_passed VARCHAR2(5) := null;
527 --introduced for calculating unit_price for service items
528 --detailed description of changes in bug 4900095
529 l_duration_passed VARCHAR2(1) := null;
530
531 l_precedence_tbl precedence_tbl_type;
532
533 l_old_line_index NUMBER := -9999;
534
535 v_routine_name CONSTANT VARCHAR2(240):='Routine:QP_Resolve_Incompatability_PVTRUN.Determine_Pricing_UOM_And_Qty';
536
537 l_line_quantity_tbl QP_PREQ_GRP.NUMBER_TYPE;
538 l_line_index_tbl QP_PREQ_GRP.NUMBER_TYPE;
539 l_line_uom_code_tbl QP_PREQ_GRP.VARCHAR_TYPE;
540 l_priced_quantity_tbl QP_PREQ_GRP.NUMBER_TYPE; --shu_latest
541 l_uom_quantity_tbl QP_PREQ_GRP.NUMBER_TYPE; --shu_latest
542 l_order_quantity_tbl QP_PREQ_GRP.NUMBER_TYPE; --shu_latest
543 l_upd_line_index_tbl QP_PREQ_GRP.NUMBER_TYPE; -- 3773652
544 l_upd_priced_qty_tbl QP_PREQ_GRP.NUMBER_TYPE; -- 3773652
545
546 l_line_quantity_tbl_m QP_PREQ_GRP.NUMBER_TYPE;
547 l_line_index_tbl_m QP_PREQ_GRP.NUMBER_TYPE;
548 l_line_uom_code_tbl_m QP_PREQ_GRP.VARCHAR_TYPE;
549 l_priced_quantity_tbl_m QP_PREQ_GRP.NUMBER_TYPE; --shu_latest
550 l_uom_quantity_tbl_m QP_PREQ_GRP.NUMBER_TYPE; --shu_latest
551 l_order_quantity_tbl_m QP_PREQ_GRP.NUMBER_TYPE; --shu_latest
552
553 m PLS_INTEGER := 1;
554 n PLS_INTEGER := 1; -- 3773652
555 l_list_header_id NUMBER := -9999;
556 lx_list_header_id NUMBER := -9999;
557 l_status_text VARCHAR2(500); --Increased length from 240 to 500 for 3103800
558 l_inventory_item_id NUMBER;
559 x_precedence_tbl precedence_tbl_type;
560
561 -- [julin/4330147/4335995]
562 l_uom_conv_success VARCHAR2(1);
563
564 INVALID_UOM EXCEPTION;
565 DUPLICATE_PRICE_LIST EXCEPTION;
566 INVALID_UOM_CONVERSION EXCEPTION;
567
568 BEGIN
569 l_debug := QP_PREQ_GRP.G_DEBUG_ENGINE;
570 IF l_debug = FND_API.G_TRUE THEN
571 QP_PREQ_GRP.engine_debug( 'Determine_Pricing_UOM');
572
573 QP_PREQ_GRP.engine_debug( 'After Determine_Pricing_UOM');
574 QP_PREQ_GRP.engine_debug( 'Pricing Phase Id:'|| p_pricing_phase_id);
575
576 END IF;
577 -- shulin
578 --IF l_debug = FND_API.G_TRUE THEN
579 --QP_PREQ_GRP.engine_debug('QP_TIME_UOM_CONVERSION Profile Setting: ' ||l_qp_time_uom_conversion);
580 --END IF;
581
582 FOR i IN incomp_cur
583 LOOP
584
585 -- [julin/4330147/4335995]
586 l_uom_conv_success := 'Y';
587
588 IF (l_old_line_index <> i.line_index) THEN
589 l_precedence_tbl.delete;
590 v_count := 0;
591 v_list_line_id := NULL;
592 l_list_header_id := -9999;
593 lx_list_header_id := -9999;
594 l_order_uom_code := i.line_uom_code;
595 l_old_line_index := i.line_index;
596 v_pricing_qty := NULL; -- shulin
597 l_oks_qty := NULL; --shulin
598 l_uom_quantity := NULL; -- shulin
599 l_sql_stmt := NULL; -- shulin
600 l_order_qty := i.line_quantity; --shu_latest
601 END IF;
602
603 v_counter := 0;
604 --m := 1;
605
606 --jhkuo, discontinue profile use for partial period pricing of service items
607 IF (i.contract_start_date IS NOT NULL AND i.contract_end_date IS NOT NULL)
608 OR i.uom_quantity IS NOT NULL THEN
609 l_qp_time_uom_conversion := 'ORACLE_CONTRACTS';
610 IF i.uom_quantity IS NOT NULL THEN
611 --bug 4900095
612 l_duration_passed := 'Y';
613 ELSE
614 --bug 4900095
615 l_duration_passed := 'N';
616 END IF;
617 ELSE
618 l_date_passed := 'NOT ';
619 l_qp_time_uom_conversion := 'STANDARD';
620 --bug 4900095
621 l_duration_passed := 'N';
622 END IF;
623
624 IF (FND_PROFILE.Value('QP_TIME_UOM_CONVERSION') = 'STANDARD') THEN --13404970
625 l_date_passed := 'NOT ';
626 l_qp_time_uom_conversion := 'STANDARD';
627 l_duration_passed := 'N';
628 END IF;
629
630 IF l_debug = FND_API.G_TRUE THEN
631 QP_PREQ_GRP.engine_debug('Constract start/end dates '||
632 l_date_passed||
633 'passed in ==> qp_time_uom_conversion = '||
634 l_qp_time_uom_conversion);
635 END IF;
636
637 IF l_debug = FND_API.G_TRUE THEN
638 QP_PREQ_GRP.engine_debug( 'Determine_Pricing_UOM1');
639 QP_PREQ_GRP.engine_debug( I.INCOMPATABILITY_GRP_CODE);
640 QP_PREQ_GRP.engine_debug( 'Line Index:'|| i.line_index);
641
642 END IF;
643 Precedence_For_Price_List_Line(i.line_index,i.line_uom_code,NULL,l_precedence_tbl,l_status,l_status_text);
644
645 IF l_debug = FND_API.G_TRUE THEN
646 QP_PREQ_GRP.engine_debug( ' Debug Point #0');
647
648 QP_PREQ_GRP.engine_debug('Count : ' || l_precedence_tbl.count);
649
650 END IF;
651 -- Sort the table(not needed as it would have only the price list lines with least precedence)
652 /* IF (l_precedence_tbl.COUNT > 0 ) THEN
653 --sort_on_precedence(l_precedence_tbl, l_precedence_tbl.FIRST, l_precedence_tbl.LAST);
654 sort_on_precedence(l_precedence_tbl);
655 END IF; */
656
657 IF (l_precedence_tbl.COUNT > 0 ) THEN
658 v_total_count := l_precedence_tbl.COUNT;
659 v_count := 0;
660 v_list_line_count1 := NULL;
661 IF l_debug = FND_API.G_TRUE THEN
662 QP_PREQ_GRP.engine_debug( 'Precedence Table Count Line : '|| i.line_index ||' Cnt:'|| l_precedence_tbl.count);
663 END IF;
664 FOR j IN l_precedence_tbl.FIRST .. l_precedence_tbl.LAST
665 LOOP
666 IF l_debug = FND_API.G_TRUE THEN
667 QP_PREQ_GRP.engine_debug( 'Determine_Pricing_UOM2');
668 END IF;
669 IF (v_count = 0) THEN
670 v_list_line_id := l_precedence_tbl(j).created_from_list_line_id;
671 v_list_precedence := l_precedence_tbl(j).product_precedence;
672 END IF;
673
674 IF (v_count > 0 ) THEN
675
676 IF l_debug = FND_API.G_TRUE THEN
677 QP_PREQ_GRP.engine_debug( 'Precedence 1 : '|| v_list_precedence);
678 QP_PREQ_GRP.engine_debug( 'Precedence 2 : '|| l_precedence_tbl(j).product_precedence);
679
680 END IF;
681 -- If precedence matches match pricing attributes count
682 IF (v_list_precedence = l_precedence_tbl(j).product_precedence) THEN
683
684 --Reset
685 v_list_line_count2 := NULL;
686
687 IF (v_list_line_count1 IS NULL) THEN
688 OPEN get_attribute_count_cur(i.line_index,v_list_line_id); -- For First Line
689 FETCH get_attribute_count_cur INTO v_list_line_count1;
690 CLOSE get_attribute_count_cur;
691 END IF;
692
693 IF l_debug = FND_API.G_TRUE THEN
694 QP_PREQ_GRP.engine_debug( 'Precedence Matched . Attribute Count1 : ' || v_list_line_id || ' ' || v_list_line_count1);
695
696 END IF;
697 OPEN get_attribute_count_cur(i.line_index,l_precedence_tbl(j).created_from_list_line_id); -- For Second Line
698 FETCH get_attribute_count_cur INTO v_list_line_count2;
699 CLOSE get_attribute_count_cur;
700
701 IF l_debug = FND_API.G_TRUE THEN
702 QP_PREQ_GRP.engine_debug( 'Precedence Matched . Attribute Count2 : ' || l_precedence_tbl(j).created_from_list_line_id || ' ' || v_list_line_count2);
703
704 END IF;
705 -- Update the status to duplicate list lines , only after comparing the attribute counts for all list lines
706 -- That is why the extra condition v_total_cout = v_count+1
707 IF (nvl(v_list_line_count1,0) = nvl(v_list_line_count2,0) and v_total_count = v_count + 1) THEN
708 v_dup_list_line_id := l_precedence_tbl(j).created_from_list_line_id;
709
710 --begin fix bug 2746019
711 --l_status_text := v_list_line_id || ',' || v_dup_list_line_id; --fix bug 2746019
712 select name into v_price_list from qp_list_headers_vl where
713 list_header_id = (select list_header_id from qp_list_lines
714 where list_line_id = v_list_line_id);
715
716 select name into v_dup_price_list from qp_list_headers_vl where
717 list_header_id = (select list_header_id from qp_list_lines
718 where list_line_id = v_dup_list_line_id);
719
720 FND_MESSAGE.SET_NAME('QP','QP_DUPLICATE_PRICE_LIST');
721 FND_MESSAGE.SET_TOKEN('LIST_LINE_ID', v_price_list);
722 FND_MESSAGE.SET_TOKEN('DUP_LIST_LINE_ID', v_dup_price_list);
723 l_status_text:= FND_MESSAGE.GET;
724 -- end fix bug 2746019
725
726 Update_Invalid_List_Lines(i.INCOMPATABILITY_GRP_CODE,i.line_index,p_pricing_phase_id,
727 QP_PREQ_GRP.G_STATUS_DUP_PRICE_LIST,l_status_text, v_return_status,
728 v_return_status_text);
729 GOTO NEXT_LINE;
730
731 --RAISE DUPLICATE_PRICE_LIST;
732 ELSE
733 IF (nvl(v_list_line_count1,0) < nvl(v_list_line_count2,0)) THEN
734 v_list_line_id := l_precedence_tbl(j).created_from_list_line_id; -- Second Line should be given
735 v_list_line_count1 := v_list_line_count2;
736 END IF;
737 END IF;
738 END IF;
739 END IF;
740 v_count := v_count + 1;
741 END LOOP;
742 END IF;
743
744 IF l_debug = FND_API.G_TRUE THEN
745 QP_PREQ_GRP.engine_debug('List Line Id:' || v_list_line_id);
746
747 END IF;
748 IF (v_list_line_id IS NOT NULL) THEN -- Successful in finding pll in order uom
749 -- Update all the other list lines to status 'U'
750 /*
751 INDX,QP_Resolve_Incompatability_PVTRUN.determine_pricing_uom_and_qty.upd1,qp_npreq_ldets_tmp_N2,PRICING_PHASE_ID,1
752 INDX,QP_Resolve_Incompatability_PVTRUN.determine_pricing_uom_and_qty.upd1,qp_npreq_ldets_tmp_N2,PRICING_STATUS_CODE,2
753 INDX,QP_Resolve_Incompatability_PVTRUN.determine_pricing_uom_and_qty.upd1,qp_npreq_lines_tmp_N2,LINE_INDEX,3
754 */
755 UPDATE qp_npreq_ldets_tmp -- upd1
756 SET PRICING_STATUS_CODE = QP_PREQ_GRP.G_STATUS_UOM_FAILURE
757 WHERE INCOMPATABILITY_GRP_CODE = i.INCOMPATABILITY_GRP_CODE
758 AND LINE_INDEX = i.line_index
759 AND PRICING_PHASE_ID = p_pricing_phase_id
760 AND CREATED_FROM_LIST_LINE_ID <> v_list_line_id
761 AND PRICING_STATUS_CODE = QP_PREQ_GRP.G_STATUS_NEW;
762
763 /* UPDATE qp_npreq_line_attrs_tmp a
764 SET a.PRICING_STATUS_CODE = QP_PREQ_GRP.G_STATUS_UOM_FAILURE
765 WHERE a.LIST_LINE_ID
766 IN (SELECT b.CREATED_FROM_LIST_LINE_ID
767 FROM qp_npreq_ldets_tmp b
768 WHERE b.PRICING_STATUS_CODE = QP_PREQ_GRP.G_STATUS_UOM_FAILURE
769 AND b.PRICING_PHASE_ID = p_pricing_phase_id
770 AND b.INCOMPATABILITY_GRP_CODE = i.INCOMPATABILITY_GRP_CODE
771 AND b.CREATED_FROM_LIST_LINE_ID <> v_list_line_id
772 AND b.LINE_INDEX = i.line_index)
773 AND a.LINE_INDEX = i.line_index; */
774
775 -- begin shu, fix bug 2453250, no price_list_header_id after big search
776 /* Commented for bug#2882115
777 UPDATE qp_npreq_lines_tmp a
778 SET a.price_list_header_id = (SELECT list_header_id from qp_list_lines where list_line_id = v_list_line_id )
779 WHERE ( a.price_list_header_id < 0 OR a.price_list_header_id IS NULL)
780 AND a.line_index = i.line_index;
781 */
782 -- end shu fix bug 2453250
783
784 -- begin shulin bug 1829731
785 -- use OKS API to calculate pricing_qty based on order_uom if profile set to 'ORACLE_CONTRACTS'
786 IF l_debug = FND_API.G_TRUE THEN
787 QP_PREQ_GRP.engine_debug('no uom conversion ...');
788 END IF;
789 IF l_qp_time_uom_conversion = 'ORACLE_CONTRACTS' THEN
790
794 QP_PREQ_GRP.engine_debug( 'line_uom_code/order uom :' || i.line_uom_code); -- order_uom
791 IF l_debug = FND_API.G_TRUE THEN
792 QP_PREQ_GRP.engine_debug( 'contract_start_date :' || i.contract_start_date);
793 QP_PREQ_GRP.engine_debug( 'contract_end_date :' || i.contract_end_date);
795 END IF;
796
797 --IF i.contract_start_date IS NOT NULL AND i.contract_end_date IS NOT NULL THEN
798 -- OKS_TIME_MEASURES_PUB replaced with OKS_OMINT_PUB in R12
799 /* [julin/4285975/4662388] removing dynamic SQL, unnecessary and causes error
800 * when env language has ',' delimiter
801 l_sql_stmt := 'select ' || nvl(ROUND (OKS_OMINT_PUB.get_quantity (i.contract_start_date, i.contract_end_date, i.line_uom_code, QP_PREQ_GRP.G_CURRENT_USER_OP_UNIT), l_max_decimal_digits), -999999) ||' from dual';
802 IF l_debug = FND_API.G_TRUE THEN
803 QP_PREQ_GRP.engine_debug('l_sql_stmt:' || l_sql_stmt);
804 END IF; -- end debug
805 execute immediate l_sql_stmt INTO l_oks_qty;
806 */
807 --END IF;
808 --bug 4900095
809 IF l_duration_passed = 'N' THEN --call OKS API for OKS calls where uom_quantity is passed as null
810 -- l_oks_qty := nvl(ROUND (OKS_OMINT_PUB.get_target_quantity (i.contract_start_date, i.contract_end_date, i.line_uom_code, QP_PREQ_GRP.G_CURRENT_USER_OP_UNIT), l_max_decimal_digits), -999999);
811 --bug 4900095
812 l_oks_qty := nvl(round(OKS_OMINT_PUB.get_target_duration( p_start_date => i.contract_start_date,
813 p_end_date => i.contract_end_date,
814 p_source_uom => i.line_uom_code,
815 p_source_duration => i.uom_quantity,
816 p_target_uom => i.line_uom_code,/*Default Month*/
817 p_org_id => QP_PREQ_GRP.G_CURRENT_USER_OP_UNIT), l_max_decimal_digits), -999999);
818 ELSE
819 l_oks_qty := null;
820 END IF;--l_duration_passed = 'N'
821
822 IF l_debug = FND_API.G_TRUE THEN
823 QP_PREQ_GRP.engine_debug('l_oks_qty:' || l_oks_qty);
824 END IF; -- end debug
825 -- when contract_start_date or contract_end_date is null, or uom is 'MO' instead of 'MTH', l_oks_qty = 0
826 -- when uom is not time_related or invalid to oks API, i.e. 'EA', 'MI', l_oks_qty = NULL
827
828 IF (l_oks_qty IS NOT NULL AND l_oks_qty <> -999999)
829 --bug 4900095
830 and l_duration_passed = 'N' THEN -- oks succeed
831 v_pricing_qty := round(l_oks_qty , l_max_decimal_digits);
832 l_order_qty := v_pricing_qty; -- shu_latest, this is to correct if user enter order_qty not matching the start_date end_date
833 l_uom_quantity :=1;
834 IF l_debug = FND_API.G_TRUE THEN
835 QP_PREQ_GRP.engine_debug('Pric_Qty OKS_API Conv based on Order_UOM :' || v_pricing_qty);
836 END IF; --end debug
837
838 ELSE -- oks fail, make v_pricing_qty as line_quantity just like STANDARD conversion
839 v_pricing_qty := i.line_quantity; -- STANDARD
840 --l_uom_quantity :=1;
841 l_uom_quantity := nvl(i.uom_quantity, 1); -- to back support OM, SL BUG FOUND
842 -- no need to update l_order_qty, since it has been initialized to i.line_quantity
843 IF l_debug = FND_API.G_TRUE THEN
844 QP_PREQ_GRP.engine_debug('pric_qty same as order_qty :' || v_pricing_qty); --shu 12/26/2001
845 QP_PREQ_GRP.engine_debug('uom_qty passed :' || l_uom_quantity); --shu 12/26/2001
846 END IF; -- end debug
847
848 END IF; -- END IF (l_oks_qty IS NOT NULL AND l_oks_qty <> 0)
849 ELSE -- l_qp_time_uom_conversion = 'STANDARD' THEN
850 --commeneted for 9977712 smbalara
851 --v_pricing_qty := i.line_quantity; -- STANDARD
852 v_pricing_qty := ROUND(i.line_quantity,l_max_decimal_digits); --added ROUND for 9977712 smbalara
853 -- no need to update l_order_qty, since it has been initialized to i.line_quantity
854 l_uom_quantity := nvl(i.uom_quantity, 1); -- to back support OM, for case user order 2 of 6 MTH service
855 IF l_debug = FND_API.G_TRUE THEN
856 QP_PREQ_GRP.engine_debug('pric_qty same as order_qty :' || v_pricing_qty);
857 END IF;
858 END IF;
859 -- end shulin bug 1829731
860
861 -- The followings are used to bulk update at the end of the procedure for no uom conversion cases
862 l_line_quantity_tbl(m) := l_order_qty; -- shu_latest
863 l_line_index_tbl(m) := i.line_index;
864 l_line_uom_code_tbl(m) := i.line_uom_code;
865 l_priced_quantity_tbl(m) := v_pricing_qty; --shu_latest
866 l_uom_quantity_tbl(m) := l_uom_quantity; --shu_latest
867 m := m+1;
868
869 /* no need if bulk update later
870 UPDATE qp_npreq_lines_tmp
871 SET PRICED_UOM_CODE = i.line_uom_code,
872 PRICED_QUANTITY = v_pricing_qty, -- shulin bug 1829731
873 LINE_UOM_CODE = i.line_uom_code, -- order_uom
874 LINE_QUANTITY = l_order_qty, -- shu_latest
875 UOM_QUANTITY =1
876 WHERE LINE_INDEX = i.line_index;
877 */
878
879 x_list_line_id := v_list_line_id;
880 ELSE -- uom conversion cases
881
882 Precedence_For_Price_List_Line(i.line_index,NULL,'Y',l_precedence_tbl,l_status,l_status_text);
883
884 -- Sort the table(not needed as it would have only the price list lines with least precedence)
885 /* IF (l_precedence_tbl.COUNT > 0) THEN
886 --sort_on_precedence(l_precedence_tbl, l_precedence_tbl.FIRST, l_precedence_tbl.LAST);
887 sort_on_precedence(l_precedence_tbl);
888 END IF; */
889
890 /* IF (l_precedence_tbl.COUNT > 0) THEN
891 FOR j IN l_precedence_tbl.FIRST .. l_precedence_tbl.LAST
892 LOOP
893 IF l_debug = FND_API.G_TRUE THEN
894 QP_PREQ_GRP.engine_debug('List Line Id : ' || l_precedence_tbl(j).created_from_list_line_id);
895 QP_PREQ_GRP.engine_debug('Precedence : ' || l_precedence_tbl(j).product_precedence);
896 QP_PREQ_GRP.engine_debug('Inventory Item Id: ' || l_precedence_tbl(j).inventory_item_id);
897 QP_PREQ_GRP.engine_debug('Product Uom : ' || l_precedence_tbl(j).product_uom_code);
898 END IF;
899 END LOOP;
900 END IF; */
901
902 IF (l_precedence_tbl.COUNT > 0) THEN
903 v_total_count := l_precedence_tbl.COUNT;
904 v_count := 0;
905 v_list_line_count1 := NULL;
906 FOR j IN l_precedence_tbl.FIRST .. l_precedence_tbl.LAST
907 LOOP
908 IF l_debug = FND_API.G_TRUE THEN
909 QP_PREQ_GRP.engine_debug('pri flag');
910 END IF;
911 IF (v_count = 0) THEN
912 v_list_line_id := l_precedence_tbl(j).created_from_list_line_id;
913 v_list_precedence := l_precedence_tbl(j).product_precedence;
914 v_primary_uom_code := l_precedence_tbl(j).product_uom_code;
915 v_inventory_item_id := to_number(l_precedence_tbl(j).inventory_item_id);
916 END IF;
917
918 IF (v_count > 0 ) THEN
919 IF (v_list_precedence = l_precedence_tbl(j).product_precedence) THEN
920
921 --Reset
922 v_list_line_count2 := NULL;
923
924 IF (v_list_line_count1 IS NULL) THEN
925 OPEN get_attribute_count_cur(i.line_index,v_list_line_id); -- For First Line
926 FETCH get_attribute_count_cur INTO v_list_line_count1;
927 CLOSE get_attribute_count_cur;
928 END IF;
929
930 IF l_debug = FND_API.G_TRUE THEN
931 QP_PREQ_GRP.engine_debug( 'Precedence Matched . Attribute Count Pri1 : ' || v_list_line_id || ' ' || v_list_line_count1);
932
933 END IF;
934 OPEN get_attribute_count_cur(i.line_index,l_precedence_tbl(j).created_from_list_line_id); -- For Second Line
935 FETCH get_attribute_count_cur INTO v_list_line_count2;
936 CLOSE get_attribute_count_cur;
937
938 IF l_debug = FND_API.G_TRUE THEN
939 QP_PREQ_GRP.engine_debug( 'Precedence Matched . Attribute Count Pri2 : ' || l_precedence_tbl(j).created_from_list_line_id || ' ' || v_list_line_count2);
940
941 END IF;
942 IF (nvl(v_list_line_count1,0) = nvl(v_list_line_count2,0) and v_total_count = v_count + 1) THEN
943
944 v_dup_list_line_id := l_precedence_tbl(j).created_from_list_line_id;
945
946 --begin fix bug 2746019
947 --l_status_text := v_list_line_id || ',' || v_dup_list_line_id; --fix bug 2746019
948 select name into v_price_list from qp_list_headers_vl where
949 list_header_id = (select list_header_id from qp_list_lines
950 where list_line_id = v_list_line_id);
951
952 select name into v_dup_price_list from qp_list_headers_vl where
953 list_header_id = (select list_header_id from qp_list_lines
954 where list_line_id = v_dup_list_line_id);
955
956 FND_MESSAGE.SET_NAME('QP','QP_DUPLICATE_PRICE_LIST');
957 FND_MESSAGE.SET_TOKEN('LIST_LINE_ID', v_price_list);
958 FND_MESSAGE.SET_TOKEN('DUP_LIST_LINE_ID', v_dup_price_list);
959 l_status_text:= FND_MESSAGE.GET;
960 -- end fix bug 2746019
961
962 Update_Invalid_List_Lines(i.INCOMPATABILITY_GRP_CODE,i.line_index,p_pricing_phase_id,
963 QP_PREQ_GRP.G_STATUS_DUP_PRICE_LIST,l_status_text, v_return_status,
964 v_return_status_text);
965 GOTO NEXT_LINE;
966 --RAISE DUPLICATE_PRICE_LIST;
967 ELSE
968 IF (nvl(v_list_line_count1,0) < nvl(v_list_line_count2,0)) THEN
969 v_list_line_id := l_precedence_tbl(j).created_from_list_line_id; -- Second Line should be given
970 v_list_line_count1 := v_list_line_count2;
971 END IF;
972 END IF;
973 END IF;
974 END IF;
975 v_count := v_count + 1;
976 END LOOP;
977 END IF; -- v_list_line_id IS NOT NULL
978
979 IF l_debug = FND_API.G_TRUE THEN
980 QP_PREQ_GRP.engine_debug('Pri Flag List Line Id:' || v_list_line_id);
981
982 END IF;
983 IF (v_list_line_id IS NOT NULL) THEN
984 /*
985 INDX,QP_Resolve_Incompatability_PVTRUN.determine_pricing_uom_and_qty.upd2,qp_npreq_ldets_tmp_N2,PRICING_PHASE_ID,1
986 INDX,QP_Resolve_Incompatability_PVTRUN.determine_pricing_uom_and_qty.upd2,qp_npreq_ldets_tmp_N2,PRICING_STATUS_CODE,2
987 INDX,QP_Resolve_Incompatability_PVTRUN.determine_pricing_uom_and_qty.upd2,qp_npreq_lines_tmp_N2,LINE_INDEX,3
988 */
989 -- Update all the other list lines to status 'P'
990 UPDATE qp_npreq_ldets_tmp -- upd2
991 SET PRICING_STATUS_CODE = QP_PREQ_GRP.G_STATUS_PRIMARY_UOM_FLAG
992 WHERE INCOMPATABILITY_GRP_CODE = i.INCOMPATABILITY_GRP_CODE
993 AND LINE_INDEX = i.line_index
994 AND PRICING_PHASE_ID = p_pricing_phase_id
995 AND CREATED_FROM_LIST_LINE_ID <> v_list_line_id
996 AND PRICING_STATUS_CODE = QP_PREQ_GRP.G_STATUS_NEW;
997
998
1002 FROM qp_npreq_ldets_tmp b
999 /* UPDATE qp_npreq_line_attrs_tmp a
1000 SET a.PRICING_STATUS_CODE = QP_PREQ_GRP.G_STATUS_PRIMARY_UOM_FLAG
1001 WHERE a.LIST_LINE_ID IN (SELECT b.CREATED_FROM_LIST_LINE_ID
1003 WHERE b.PRICING_STATUS_CODE = QP_PREQ_GRP.G_STATUS_PRIMARY_UOM_FLAG
1004 AND b.CREATED_FROM_LIST_LINE_ID <> v_list_line_id
1005 AND b.LINE_INDEX = i.line_index)
1006 AND a.LINE_INDEX = i.line_index; */
1007
1008 /* UPDATE qp_npreq_line_attrs_tmp a
1009 SET a.PRICING_STATUS_CODE = QP_PREQ_GRP.G_STATUS_PRIMARY_UOM_FLAG
1010 WHERE a.LIST_LINE_ID
1011 IN (SELECT b.CREATED_FROM_LIST_LINE_ID
1012 FROM qp_npreq_ldets_tmp b
1013 WHERE b.PRICING_STATUS_CODE = QP_PREQ_GRP.G_STATUS_PRIMARY_UOM_FLAG
1014 AND b.PRICING_PHASE_ID = p_pricing_phase_id
1015 AND b.INCOMPATABILITY_GRP_CODE = i.INCOMPATABILITY_GRP_CODE
1016 AND b.CREATED_FROM_LIST_LINE_ID <> v_list_line_id
1017 AND b.LINE_INDEX = i.line_index)
1018 AND a.LINE_INDEX = i.line_index; */
1019
1020 -- begin shu, fix bug 2453250, no price_list_header_id after big search
1021 /* Commented for bug#2882115
1022 UPDATE qp_npreq_lines_tmp a
1023 SET a.price_list_header_id = (SELECT list_header_id from qp_list_lines where list_line_id = v_list_line_id )
1024 WHERE ( a.price_list_header_id < 0 OR a.price_list_header_id IS NULL)
1025 AND a.line_index = i.line_index;
1026 */
1027 -- end shu fix bug 2453250
1028
1029 IF l_debug = FND_API.G_TRUE THEN
1030 QP_PREQ_GRP.engine_debug('Data Updated');
1031 QP_PREQ_GRP.engine_debug('Order Uom:' || i.line_uom_code);
1032 QP_PREQ_GRP.engine_debug('Prim UOM:' || v_primary_uom_code);
1033 QP_PREQ_GRP.engine_debug('Inventory Id:' || v_inventory_item_id);
1034 QP_PREQ_GRP.engine_debug('v_pricing_qty:' || v_pricing_qty);
1035 END IF;
1036
1037 -- begin Bug 1829731 fix, shulin
1038 IF l_debug = FND_API.G_TRUE THEN
1039 QP_PREQ_GRP.engine_debug('uom conversion...');
1040 END IF;
1041 l_uom_quantity := i.uom_quantity; --shu_latest, to back support OM, either from usr input or null
1042 -- shulin, do OKS conversion if profile set to 'ORCALE_CONTRACTS'
1043 IF (l_qp_time_uom_conversion = 'ORACLE_CONTRACTS') THEN -- do oks conversion
1044
1045 IF l_debug = FND_API.G_TRUE THEN
1046 QP_PREQ_GRP.engine_debug( 'contract_start_date :' || i.contract_start_date);
1047 QP_PREQ_GRP.engine_debug( 'contract_end_date :' || i.contract_end_date);
1048 QP_PREQ_GRP.engine_debug( 'primary_uom_code :' || v_primary_uom_code);
1049 QP_PREQ_GRP.engine_debug('v_pricing_qty:' || v_pricing_qty);
1050 END IF;
1051 -- when contract_start_date or contract_end_date is null, or uom is 'MO' instead of 'MTH', l_oks_qty = 0
1052 -- when uom is not time_related or invalid to oks API, i.e. 'EA', 'MI', l_oks_qty = NULL
1053
1054 --IF i.contract_start_date IS NOT NULL AND i.contract_end_date IS NOT NULL THEN
1055 -- OKS_TIME_MEASURES_PUB replaced with OKS_OMINT_PUB in R12
1056 /* [julin/4285975/4662388] removing dynamic SQL, unnecessary and causes error
1057 * when env language has ',' delimiter
1058 l_sql_stmt := 'select ' || ROUND (OKS_OMINT_PUB.get_quantity (i.contract_start_date,
1059 i.contract_end_date, v_primary_uom_code, QP_PREQ_GRP.G_CURRENT_USER_OP_UNIT), l_max_decimal_digits) ||' from dual';
1060 execute immediate l_sql_stmt INTO l_oks_qty; -- shulin
1061 */
1062 --END IF;
1063 -- l_oks_qty := ROUND (OKS_OMINT_PUB.get_quantity (i.contract_start_date, i.contract_end_date, v_primary_uom_code, QP_PREQ_GRP.G_CURRENT_USER_OP_UNIT), l_max_decimal_digits);
1064 --bug 4900095
1065 l_oks_qty := nvl(round(OKS_OMINT_PUB.get_target_duration( p_start_date => i.contract_start_date,
1066 p_end_date => i.contract_end_date,
1067 p_source_uom => i.line_uom_code,
1068 p_source_duration => i.uom_quantity,
1069 p_target_uom => v_primary_uom_code,/*Default Month*/
1070 p_org_id => QP_PREQ_GRP.G_CURRENT_USER_OP_UNIT), l_max_decimal_digits), -999999);
1071
1072 IF l_debug = FND_API.G_TRUE THEN
1073 QP_PREQ_GRP.engine_debug('l_oks_qty1:' || l_oks_qty);
1074 END IF; -- end debug
1075
1076 IF (l_oks_qty IS NOT NULL OR l_oks_qty <> 0)
1077 --bug 4900095
1078 and l_duration_passed = 'N' THEN -- oks succeed
1079
1080 -- order_qty is based on odr_uom
1081 /* [julin/4285975/4662388] removing dynamic SQL, unnecessary and causes error
1082 * when env language has ',' delimiter
1083 l_sql_stmt := 'select ' || ROUND (OKS_OMINT_PUB.get_quantity (i.contract_start_date,
1084 i.contract_end_date, i.line_uom_code, QP_PREQ_GRP.G_CURRENT_USER_OP_UNIT), l_max_decimal_digits) ||' from dual';
1085 execute immediate l_sql_stmt INTO l_order_qty; -- shu_latest
1086 */
1087 -- l_order_qty := ROUND (OKS_OMINT_PUB.get_quantity (i.contract_start_date, i.contract_end_date, i.line_uom_code, QP_PREQ_GRP.G_CURRENT_USER_OP_UNIT), l_max_decimal_digits);
1088 --bug 4900095
1089 l_order_qty := nvl(round(OKS_OMINT_PUB.get_target_duration( p_start_date => i.contract_start_date,
1090 p_end_date => i.contract_end_date,
1091 p_source_uom => i.line_uom_code,
1092 p_source_duration => i.uom_quantity,
1093 p_target_uom => i.line_uom_code,/*Default Month*/
1094 p_org_id => QP_PREQ_GRP.G_CURRENT_USER_OP_UNIT), l_max_decimal_digits), -999999);
1095 IF l_debug = FND_API.G_TRUE THEN
1096 QP_PREQ_GRP.engine_debug('l_oks_qty2:' || l_oks_qty);
1097 END IF; -- end debug
1098
1099 v_pricing_qty := round (l_oks_qty , l_max_decimal_digits);
1100
1101 l_uom_quantity := 1; --shulin
1102 IF l_debug = FND_API.G_TRUE THEN
1103 QP_PREQ_GRP.engine_debug('OKS_API conversion...');
1104 QP_PREQ_GRP.engine_debug('pricing_qty:' || v_pricing_qty);
1105 QP_PREQ_GRP.engine_debug('pricing_uom:' || v_primary_uom_code);
1106 QP_PREQ_GRP.engine_debug('order_qty:' || l_order_qty);
1107 QP_PREQ_GRP.engine_debug('order_uom:' || l_order_uom_code);
1108 QP_PREQ_GRP.engine_debug('uom_quantity:' || l_uom_quantity);
1109 END IF;
1110
1111 ELSIF (l_oks_qty <> -999999 AND l_oks_qty <> 0) -- OM/ASO/IBE call for service line duration/uom_quantity is not null
1112 --bug 4900095
1113 and l_duration_passed = 'Y' THEN
1114 l_uom_quantity := l_oks_qty;
1115 v_pricing_qty := ROUND( i.line_quantity * l_oks_qty, l_max_decimal_digits) ; -- nvl is case we only have line_quantity
1116 --changed for service pricing
1117 --v_pricing_qty := ROUND( i.line_quantity * l_oks_qty * nvl(i.uom_quantity,1), l_max_decimal_digits) ; -- nvl is case we only have line_quantity
1118 --l_uom_quantity := ROUND( i.uom_quantity * v_uom_rate, l_max_decimal_digits); -- do not nvl uom_quantity
1119 l_uom_quantity :=1; -- reset to 1, so the unit price by pricing uom does not change
1120 l_order_qty := i.line_quantity;
1121 IF l_debug = FND_API.G_TRUE THEN
1122 QP_PREQ_GRP.engine_debug('OKS_API conversion for OM...');
1123 QP_PREQ_GRP.engine_debug('pricing_qty:' || v_pricing_qty);
1124 QP_PREQ_GRP.engine_debug('pricing_uom:' || v_primary_uom_code);
1125 QP_PREQ_GRP.engine_debug('order_qty:' || l_order_qty);
1126 QP_PREQ_GRP.engine_debug('order_uom:' || l_order_uom_code);
1127 QP_PREQ_GRP.engine_debug('uom_quantity:' || l_uom_quantity);
1128 END IF;
1129 END IF; --oks succeed
1130 END IF; -- end profile is ORACLE_CONTRACTS
1131
1132 -- not 'ORACLE_CONTACTS' or OKS conversion failed, use standard inventory uom conversion
1133 IF (l_qp_time_uom_conversion = 'STANDARD') OR (l_oks_qty = -999999 OR l_oks_qty = 0) THEN
1134
1135 IF (l_qp_time_uom_conversion = 'ORACLE_CONTRACTS') then
1136 IF l_debug = FND_API.G_TRUE THEN
1137 QP_PREQ_GRP.engine_debug('oks conversion had failed...');
1138 END IF;
1139 END IF; -- for debug, to distinglish if it is oks failed case or if profile set to standard case
1140
1141 -- Get the conversion rate based on prclist's primary_uom
1142 Inv_convert.inv_um_conversion(i.line_uom_code,
1143 v_primary_uom_code,
1144 v_inventory_item_id,
1145 v_uom_rate);
1146 IF (v_uom_rate = -99999) THEN
1147 l_status_text := 'invalid conversion rate from ' || l_order_uom_code || ' to ' || v_primary_uom_code;
1148 Update_Invalid_List_Lines(i.INCOMPATABILITY_GRP_CODE,i.line_index,p_pricing_phase_id,
1149 QP_PREQ_GRP.G_STATUS_INVALID_UOM_CONV,l_status_text, v_return_status,
1150 v_return_status_text);
1151 -- [julin/4330147/4335995]
1152 l_uom_conv_success := 'N';
1153 --RAISE INVALID_UOM_CONVERSION;
1154 ELSE
1155 IF (i.PROCESSING_ORDER <> 2 or i.PROCESSING_ORDER IS NULL ) THEN -- It is not a service line
1156
1157 v_pricing_qty := ROUND( i.line_quantity * v_uom_rate, l_max_decimal_digits);
1158 l_uom_quantity := 1; --shu_latest
1159 l_order_qty := i.line_quantity;
1160 IF l_debug = FND_API.G_TRUE THEN
1161 QP_PREQ_GRP.engine_debug('Standard uom conversion, non-service line...');
1162 QP_PREQ_GRP.engine_debug('pricing_qty:' || v_pricing_qty);
1163 QP_PREQ_GRP.engine_debug('pricing_uom:' || v_primary_uom_code);
1164 QP_PREQ_GRP.engine_debug('order_qty:' || l_order_qty);
1165 QP_PREQ_GRP.engine_debug('order_uom:' || l_order_uom_code);
1166 QP_PREQ_GRP.engine_debug('uom_quantity:' || l_uom_quantity);
1167 END IF;
1168
1169 ELSE -- service line, l_uom_quantity matters
1170
1171 IF (l_qp_time_uom_conversion = 'ORACLE_CONTRACTS') THEN -- from oks failed case
1172 v_pricing_qty := ROUND( i.line_quantity * v_uom_rate, l_max_decimal_digits);
1173 l_uom_quantity := 1;
1174 l_order_qty := i.line_quantity;
1175 IF l_debug = FND_API.G_TRUE THEN
1176 QP_PREQ_GRP.engine_debug('Standard uom conversion (oks failed), service line...');
1177 QP_PREQ_GRP.engine_debug('pricing_qty:' || v_pricing_qty);
1178 QP_PREQ_GRP.engine_debug('pricing_uom:' || v_primary_uom_code);
1179 QP_PREQ_GRP.engine_debug('order_qty:' || l_order_qty);
1180 QP_PREQ_GRP.engine_debug('order_uom:' || l_order_uom_code);
1181 QP_PREQ_GRP.engine_debug('uom_quantity:' || l_uom_quantity);
1182 END IF;
1183
1184 ELSE -- from profile being standard case, l_uom_quantity matters for OM
1185 -- assuming OM pass l_uom_quantity is converted to order uom like the following
1186 -- 1 of 2 YR services, order_uom=YR, l_uom_quantity = 2 (YR)
1187 IF l_debug = FND_API.G_TRUE THEN
1188 QP_PREQ_GRP.engine_debug('uom_quantity from calling application:' || i.uom_quantity);
1189 END IF;
1190 v_pricing_qty := ROUND( i.line_quantity * v_uom_rate * nvl(i.uom_quantity,1), l_max_decimal_digits) ; -- nvl is case we only have line_quantity
1191 --l_uom_quantity := ROUND( i.uom_quantity * v_uom_rate, l_max_decimal_digits); -- do not nvl uom_quantity
1192 l_uom_quantity :=1; -- reset to 1, so the unit price by pricing uom does not change
1193 l_order_qty := i.line_quantity;
1194
1195 IF l_debug = FND_API.G_TRUE THEN
1196 QP_PREQ_GRP.engine_debug('Standard uom conversion, service line...');
1197 QP_PREQ_GRP.engine_debug('pricing_qty:' || v_pricing_qty);
1198 QP_PREQ_GRP.engine_debug('pricing_uom:' || v_primary_uom_code);
1199 QP_PREQ_GRP.engine_debug('order_qty:' || l_order_qty);
1200 QP_PREQ_GRP.engine_debug('order_uom:' || l_order_uom_code);
1201 END IF;
1202
1203 END IF;
1204
1205 END IF; -- end if service or non-service line
1206
1207 END IF; -- end if v_uom_rate
1208
1209 END IF; -- end profile is 'STANDARD'
1210
1211
1212 /*
1213 INDX,QP_Resolve_Incompatability_PVTRUN.determine_pricing_uom_and_qty.upd1,qp_npreq_lines_tmp_N1,LINE_INDEX,1
1214 */
1215 -- do not nvl UOM_QUANTITY when updating UOM_QUANTITY , for a case if uom_quantity is null for a regular line , we could
1216 -- potentially update it to value by nvl'ing it , when uom_quantity is supposed to be null.
1217 -- Ex: 1Ton = 1000Kg , UOM_QUANTITY passed is null . in this case if we nvl(UOM_QUANTITY,1) then the new uom_quantity after the
1218 -- update will be nvl(uom_quantity,1) * 1000(v_uom_rate) = 1000 which is wrong .. bug# 2028618
1219
1220 UPDATE qp_npreq_lines_tmp -- upd1
1221 SET PRICED_UOM_CODE = v_primary_uom_code,
1222 PRICED_QUANTITY = v_pricing_qty, -- shulin bug 1829731
1223 LINE_UOM_CODE = l_order_uom_code, -- order_uom, i.line_uom_code
1224 LINE_QUANTITY = l_order_qty, -- shu_latest
1225 UOM_QUANTITY = decode(l_duration_passed,
1226 'Y', l_uom_quantity,
1227 'N', uom_quantity,
1228 uom_quantity)
1229 WHERE LINE_INDEX = i.line_index;
1230 x_list_line_id := v_list_line_id;
1231
1232 -- [julin/4330147/4335995] only update line_attrs when uom conversion successful
1233 IF (l_uom_conv_success = 'Y') THEN
1234 -- 3773652
1235 -- used for bulk update for UOM conversion cases
1236 l_upd_line_index_tbl(n) := i.line_index;
1237 l_upd_priced_qty_tbl(n) := v_pricing_qty;
1238 n := n+1;
1239 END IF;
1240 ELSE -- No record found in primary uom and also in order uom, else IF (v_list_line_id IS NOT NULL)
1241 l_status_text := 'Could not find a price list in Ordered UOM or Primary UOM';
1242 IF l_debug = FND_API.G_TRUE THEN
1243 QP_PREQ_GRP.engine_debug(l_status_text);
1244 END IF;
1245 Update_Invalid_List_Lines(i.INCOMPATABILITY_GRP_CODE,i.line_index,p_pricing_phase_id,
1246 QP_PREQ_GRP.G_STATUS_INVALID_UOM,l_status_text, v_return_status,
1247 v_return_status_text);
1248 GOTO NEXT_LINE;
1249 -- RAISE INVALID_UOM;
1250 END IF;
1251 END IF;
1252
1253 <<NEXT_LINE>>
1254 null;
1255 END LOOP;
1256
1257
1258 /*
1259 INDX,QP_Resolve_Incompatability_PVTRUN.determine_pricing_uom_and_qty.upd2,qp_npreq_lines_tmp_N1,LINE_INDEX,1
1260 */
1261
1262 -- Bulk Update, from no uom conversion cases
1263 FORALL i in 1 .. l_line_index_tbl.COUNT --upd2
1264 UPDATE qp_npreq_lines_tmp
1265 SET PRICED_UOM_CODE = l_line_uom_code_tbl(i), --priced uom is the same as order uom
1266 PRICED_QUANTITY = l_priced_quantity_tbl(i), -- shu_latest
1267 LINE_UOM_CODE = l_line_uom_code_tbl(i), -- order uom
1268 LINE_QUANTITY = l_line_quantity_tbl(i), -- shu_latest
1269 UOM_QUANTITY = l_uom_quantity_tbl(i) -- shu_latest
1270 WHERE LINE_INDEX = l_line_index_tbl(i);
1271
1272 l_line_uom_code_tbl := l_line_uom_code_tbl_m;
1273 l_line_quantity_tbl := l_line_quantity_tbl_m;
1274 l_line_index_tbl := l_line_index_tbl_m;
1275 l_priced_quantity_tbl := l_priced_quantity_tbl_m; --shu_latest
1276 l_uom_quantity_tbl := l_uom_quantity_tbl_m; --shu_latest
1277
1278 -- 3773652
1279 -- bulk update, from uom conversion cases
1280 FORALL i in 1..l_upd_line_index_tbl.COUNT
1281 UPDATE qp_npreq_line_attrs_tmp
1282 SET VALUE_FROM = qp_number.number_to_canonical(l_upd_priced_qty_tbl(i))
1283 WHERE LINE_INDEX = l_upd_line_index_tbl(i)
1284 AND CONTEXT = QP_PREQ_GRP.G_PRIC_VOLUME_CONTEXT
1285 AND ATTRIBUTE = QP_PREQ_GRP.G_QUANTITY_ATTRIBUTE
1286 AND ATTRIBUTE_TYPE = QP_PREQ_GRP.G_PRICING_TYPE
1287 AND PRICING_STATUS_CODE = QP_PREQ_GRP.G_STATUS_UNCHANGED;
1288 l_upd_line_index_tbl := l_line_index_tbl_m;
1289 l_upd_priced_qty_tbl := l_priced_quantity_tbl_m;
1290
1291 IF l_debug = FND_API.G_TRUE THEN
1292 QP_PREQ_GRP.engine_debug('Incomp Return Status : ' || v_return_status);
1293 END IF;
1294 v_return_status := x_return_status;
1295 EXCEPTION
1296 WHEN INVALID_UOM_CONVERSION THEN
1297 IF l_debug = FND_API.G_TRUE THEN
1298 QP_PREQ_GRP.engine_debug(v_routine_name ||' Invalid Unit of Measure Conversion'|| l_order_uom_code || ','
1299 || v_primary_uom_code);
1300 END IF;
1301 x_return_status_txt := l_order_uom_code || ',' || v_primary_uom_code;
1302 v_return_status := QP_PREQ_GRP.G_STATUS_INVALID_UOM_CONV;
1303 x_return_status := v_return_status;
1304 x_list_line_id := NULL;
1305 WHEN DUPLICATE_PRICE_LIST THEN
1306 IF l_debug = FND_API.G_TRUE THEN
1307 QP_PREQ_GRP.engine_debug(v_routine_name || ' Duplicate Price List '|| v_list_line_id || ',' || v_dup_list_line_id);
1308 END IF;
1309 v_return_msg := v_list_line_id || ',' || v_dup_list_line_id ;
1310 IF l_debug = FND_API.G_TRUE THEN
1311 QP_PREQ_GRP.engine_debug(v_return_msg);
1312 END IF;
1313 x_return_status_txt := v_return_msg;
1314 v_return_status := QP_PREQ_GRP.G_STATUS_DUP_PRICE_LIST;
1315 x_return_status := v_return_status;
1316 x_list_line_id := NULL;
1317 WHEN INVALID_UOM THEN
1318 IF l_debug = FND_API.G_TRUE THEN
1319 QP_PREQ_GRP.engine_debug(v_routine_name || ' Could not find a price list in Ordered UOM or Primary UOM');
1320 END IF;
1321 x_return_status_txt := 'Could not find a price list in Ordered UOM or Primary UOM';
1322 v_return_status := QP_PREQ_GRP.G_STATUS_INVALID_UOM;
1323 x_return_status := v_return_status;
1324 x_list_line_id := NULL;
1325 WHEN OTHERS THEN
1326 IF l_debug = FND_API.G_TRUE THEN
1327 QP_PREQ_GRP.engine_debug(v_routine_name || 'Unexpected Error');
1328 QP_PREQ_GRP.engine_debug(v_routine_name || ' ' || SQLERRM);
1329 END IF;
1330 v_return_status := FND_API.G_RET_STS_ERROR;
1331 x_return_status := v_return_status;
1332 x_return_status_txt := v_routine_name || ' ' || SQLERRM;
1333 x_list_line_id := NULL;
1334 END Determine_Pricing_UOM_And_Qty;
1335
1336 PROCEDURE Best_Price_Evaluation(p_list_price NUMBER,
1337 p_line_index NUMBER,
1338 p_pricing_phase_id NUMBER,
1339 p_incomp_grp_id VARCHAR2,
1340 p_precedence NUMBER, -- Added for bug#2661540
1341 p_manual_dis_flag VARCHAR2,
1342 x_list_line_id OUT NOCOPY NUMBER,
1343 x_return_status OUT NOCOPY VARCHAR2) AS
1344
1345
1346 v_index NUMBER := 1;
1347 v_list_price NUMBER;
1348 v_benefit_price NUMBER;
1349 v_benefit_percent NUMBER;
1350 v_list_line_id NUMBER;
1351 v_request_qty NUMBER;
1352 v_return_status VARCHAR2(30) := FND_API.G_RET_STS_SUCCESS;
1353 x_benefit_amount NUMBER;
1354 l_return_status VARCHAR2(30);
1355 l_return_status_text VARCHAR2(240);
1356 l_sign NUMBER;
1357 l_request_qty NUMBER;
1358 l_line_id NUMBER; --3244060
1359
1360 v_routine_name CONSTANT VARCHAR2(240) := 'Routine:QP_Resolve_Incompatability_PVTRUN.Best_Price_Evaluation';
1361
1362 /*
1363 INDX,QP_Resolve_Incompatability_PVTRUN.best_price_evaluation.get_list_lines_cur,qp_npreq_ldets_tmp_N1,LINE_INDEX,1
1364 INDX,QP_Resolve_Incompatability_PVTRUN.best_price_evaluation.get_list_lines_cur,qp_npreq_ldets_tmp_N1,PRICING_STATUS_CODE,2
1365 INDX,QP_Resolve_Incompatability_PVTRUN.best_price_evaluation.get_list_lines_cur,qp_npreq_ldets_tmp_N1,PRICING_PHASE_ID,3
1366 INDX,QP_Resolve_Incompatability_PVTRUN.best_price_evaluation.get_list_lines_cur,qp_npreq_ldets_tmp_N1,CREATED_FROM_LIST_HEADER_ID,4
1367
1368 INDX,QP_Resolve_Incompatability_PVTRUN.best_price_evaluation.get_list_lines_cur,qp_npreq_ldets_tmp_N1,CREATED_FROM_LIST_LINE_ID,5
1372 CURSOR get_list_lines_cur IS
1369 INDX,QP_Resolve_Incompatability_PVTRUN.best_price_evaluation.get_list_lines_cur,QP_LIST_LINES_PK,LIST_LINE_ID,1
1370 */
1371 -- For bug#2661540
1373 SELECT b.CREATED_FROM_LIST_HEADER_ID, b.CREATED_FROM_LIST_LINE_ID,b.line_detail_index, --3244060
1374 b.CREATED_FROM_LIST_LINE_TYPE, b.OPERAND_CALCULATION_CODE, b.OPERAND_VALUE,a.ESTIM_GL_VALUE ,
1375 a.BENEFIT_PRICE_LIST_LINE_ID,a.PRICE_BREAK_TYPE_CODE,b.LINE_QUANTITY,b.GROUP_QUANTITY,b.GROUP_AMOUNT, --[julin/4240067/4307242]
1376 b.modifier_level_code
1377 FROM QP_LIST_LINES a, qp_npreq_ldets_tmp b, qp_npreq_line_attrs_tmp c
1378 WHERE a.LIST_HEADER_ID = b.CREATED_FROM_LIST_HEADER_ID
1379 AND a.LIST_LINE_ID = b.CREATED_FROM_LIST_LINE_ID
1380 AND b.CREATED_FROM_LIST_TYPE_CODE not in (QP_PREQ_GRP.G_PRICE_LIST_HEADER,QP_PREQ_GRP.G_AGR_LIST_HEADER)
1381 AND b.PRICING_PHASE_ID = p_pricing_phase_id
1382 AND b.INCOMPATABILITY_GRP_CODE = p_incomp_grp_id
1383 AND b.LINE_INDEX = p_line_index
1384 AND b.PRICING_STATUS_CODE = QP_PREQ_GRP.G_STATUS_NEW
1385 AND ((b.AUTOMATIC_FLAG = QP_PREQ_GRP.G_YES AND p_manual_dis_flag = QP_PREQ_GRP.G_YES)
1386 OR p_manual_dis_flag = QP_PREQ_GRP.G_NO)
1387 AND c.LIST_LINE_ID(+) = a.LIST_LINE_ID
1388 AND c.LIST_HEADER_ID(+) = a.LIST_HEADER_ID
1389 AND c.ATTRIBUTE_TYPE(+) = 'QUALIFIER'
1390 AND c.INCOMPATABILITY_GRP_CODE(+) = p_incomp_grp_id
1391 AND c.PRICING_PHASE_ID(+) = p_pricing_phase_id
1392 AND c.PRICING_STATUS_CODE(+) = 'N'
1393 AND c.LINE_INDEX(+) = p_line_index
1394 AND least(nvl(b.PRODUCT_PRECEDENCE,5000),nvl(c.QUALIFIER_PRECEDENCE,5000)) = nvl(p_precedence, least(nvl(b.PRODUCT_PRECEDENCE,5000),nvl(c.QUALIFIER_PRECEDENCE,5000)));
1395
1396 /* Commented for bug 2661540
1397 CURSOR get_list_lines_cur IS
1398 SELECT b.CREATED_FROM_LIST_HEADER_ID, b.CREATED_FROM_LIST_LINE_ID,
1399 b.CREATED_FROM_LIST_LINE_TYPE, b.OPERAND_CALCULATION_CODE, b.OPERAND_VALUE,a.ESTIM_GL_VALUE ,
1400 a.BENEFIT_PRICE_LIST_LINE_ID,a.PRICE_BREAK_TYPE_CODE,b.GROUP_QUANTITY,b.GROUP_AMOUNT
1401 FROM QP_LIST_LINES a, qp_npreq_ldets_tmp b
1402 WHERE a.LIST_HEADER_ID = b.CREATED_FROM_LIST_HEADER_ID
1403 AND a.LIST_LINE_ID = b.CREATED_FROM_LIST_LINE_ID
1404 AND b.CREATED_FROM_LIST_TYPE_CODE not in (QP_PREQ_GRP.G_PRICE_LIST_HEADER,QP_PREQ_GRP.G_AGR_LIST_HEADER)
1405 AND b.PRICING_PHASE_ID = p_pricing_phase_id
1406 AND b.INCOMPATABILITY_GRP_CODE = p_incomp_grp_id
1407 AND b.LINE_INDEX = p_line_index
1408 AND b.PRICING_STATUS_CODE = QP_PREQ_GRP.G_STATUS_NEW
1409 AND ((b.AUTOMATIC_FLAG = QP_PREQ_GRP.G_YES AND p_manual_dis_flag = QP_PREQ_GRP.G_YES) OR
1410 p_manual_dis_flag = QP_PREQ_GRP.G_NO);
1411 */
1412 /*
1413 INDX,QP_Resolve_Incompatability_PVTRUN.best_price_evaluation.get_request_qty_cur,qp_npreq_lines_tmp_N1,LINE_INDEX,1*/
1414
1415 CURSOR get_request_qty_cur IS
1416 SELECT nvl(PRICED_QUANTITY,LINE_QUANTITY)
1417 FROM qp_npreq_lines_tmp
1418 WHERE LINE_INDEX = p_line_index;
1419
1420 /*
1421 INDX,QP_Resolve_Incompatability_PVTRUN.best_price_evaluation.get_list_price_cur,QP_LIST_LINES_PK,LIST_LINE_ID,1
1422 */
1423
1424 CURSOR get_list_price_cur(p_list_line_id NUMBER) IS
1425 SELECT OPERAND
1426 FROM QP_LIST_LINES
1427 WHERE LIST_LINE_ID = p_list_line_id
1428 AND ARITHMETIC_OPERATOR = QP_PREQ_GRP.G_UNIT_PRICE;
1429
1430 /*
1431 INDX,QP_Resolve_Incompatability_PVTRUN.best_price_evaluation.get_list_lines_in_order,qp_npreq_ldets_tmp_N1,LINE_INDEX,1
1432 INDX,QP_Resolve_Incompatability_PVTRUN.best_price_evaluation.get_list_lines_in_order,qp_npreq_ldets_tmp_N1,PRICING_STATUS_CODE,2
1433 INDX,QP_Resolve_Incompatability_PVTRUN.best_price_evaluation.get_list_lines_in_order,qp_npreq_ldets_tmp_N1,PRICING_PHASE_ID,3
1434 */
1435
1436 -- For bug 2661540
1437 CURSOR get_list_lines_in_order IS
1438 SELECT a.CREATED_FROM_LIST_LINE_ID
1439 FROM qp_npreq_ldets_tmp a, qp_npreq_line_attrs_tmp b
1440 WHERE a.CREATED_FROM_LIST_TYPE_CODE not in (QP_PREQ_GRP.G_PRICE_LIST_HEADER,QP_PREQ_GRP.G_AGR_LIST_HEADER)
1441 AND a.PRICING_PHASE_ID = p_pricing_phase_id
1442 AND a.INCOMPATABILITY_GRP_CODE = p_incomp_grp_id
1443 AND a.LINE_INDEX = p_line_index
1444 AND a.PRICING_STATUS_CODE = QP_PREQ_GRP.G_STATUS_NEW
1445 AND ((a.AUTOMATIC_FLAG = QP_PREQ_GRP.G_YES AND p_manual_dis_flag = QP_PREQ_GRP.G_YES) OR
1446 p_manual_dis_flag = QP_PREQ_GRP.G_NO)
1447 AND b.LIST_LINE_ID(+) = a.CREATED_FROM_LIST_LINE_ID
1448 AND b.LIST_HEADER_ID(+) = a.CREATED_FROM_LIST_HEADER_ID
1449 AND b.ATTRIBUTE_TYPE(+) = 'QUALIFIER'
1450 AND b.INCOMPATABILITY_GRP_CODE(+) = p_incomp_grp_id
1451 AND b.PRICING_PHASE_ID(+) = p_pricing_phase_id
1452 AND b.PRICING_STATUS_CODE(+) = 'N'
1453 AND b.LINE_INDEX(+) = p_line_index
1454 AND least(nvl(a.PRODUCT_PRECEDENCE,5000),nvl(b.QUALIFIER_PRECEDENCE,5000)) = nvl(p_precedence, least(nvl(a.PRODUCT_PRECEDENCE,5000),nvl(b.QUALIFIER_PRECEDENCE,5000)))
1455 ORDER BY BEST_PERCENT DESC;
1456
1457 /* Commented for Bug 2661540
1458 CURSOR get_list_lines_in_order IS
1459 SELECT CREATED_FROM_LIST_LINE_ID
1460 FROM qp_npreq_ldets_tmp
1461 WHERE CREATED_FROM_LIST_TYPE_CODE not in (QP_PREQ_GRP.G_PRICE_LIST_HEADER,QP_PREQ_GRP.G_AGR_LIST_HEADER)
1462 AND PRICING_PHASE_ID = p_pricing_phase_id
1463 AND INCOMPATABILITY_GRP_CODE = p_incomp_grp_id
1464 AND LINE_INDEX = p_line_index
1465 AND PRICING_STATUS_CODE = QP_PREQ_GRP.G_STATUS_NEW
1466 AND ((AUTOMATIC_FLAG = QP_PREQ_GRP.G_YES AND p_manual_dis_flag = QP_PREQ_GRP.G_YES) OR
1467 p_manual_dis_flag = QP_PREQ_GRP.G_NO)
1468 ORDER BY BEST_PERCENT DESC;
1469 */
1470
1471 --[julin/5456188]
1472 l_req_value_per_unit NUMBER;
1473 l_total_value NUMBER;
1474 l_volume_attribute VARCHAR2(240);
1475 l_calc_quantity NUMBER;
1476
1477 BEGIN
1478 l_debug := QP_PREQ_GRP.G_DEBUG_ENGINE;
1479 FOR i IN get_list_lines_cur
1480 LOOP
1481 /*IF (i.CREATED_FROM_LIST_LINE_TYPE = QP_PREQ_GRP.G_OTHER_ITEM_DISCOUNT) THEN
1482 IF l_debug = FND_API.G_TRUE THEN
1483 QP_PREQ_GRP.engine_debug('Benefit Price List Line Id is Very important: ' || i.BENEFIT_PRICE_LIST_LINE_ID);
1484 END IF;
1485 OPEN get_list_price_cur(i.BENEFIT_PRICE_LIST_LINE_ID);
1486 FETCH get_list_price_cur INTO v_list_price;
1487 CLOSE get_list_price_cur;
1488 IF (i.OPERAND_CALCULATION_CODE = QP_PREQ_GRP.G_PERCENT_DISCOUNT) THEN
1489 v_benefit_price := (v_list_price) * (i.OPERAND_VALUE / 100);
1490 ELSIF (i.OPERAND_CALCULATION_CODE = QP_PREQ_GRP.G_AMOUNT_DISCOUNT) THEN
1491 v_benefit_price := (v_list_price - i.OPERAND_VALUE);
1492 ELSIF (i.OPERAND_CALCULATION_CODE IS NULL) THEN
1493 v_benefit_price := v_list_price;
1494 END IF; */
1495 IF (i.CREATED_FROM_LIST_LINE_TYPE IN (QP_PREQ_GRP.G_ITEM_UPGRADE,QP_PREQ_GRP.G_TERMS_SUBSTITUTION,
1496 QP_PREQ_GRP.G_COUPON_ISSUE,QP_PREQ_GRP.G_OTHER_ITEM_DISCOUNT,
1497 QP_PREQ_GRP.G_PROMO_GOODS_DISCOUNT)) THEN
1498 v_benefit_price := nvl(i.ESTIM_GL_VALUE,0);
1499 ELSIF (i.CREATED_FROM_LIST_LINE_TYPE IN (QP_PREQ_GRP.G_DISCOUNT,QP_PREQ_GRP.G_SURCHARGE,QP_PREQ_GRP.G_FREIGHT_CHARGE)) THEN
1500
1501 IF (i.CREATED_FROM_LIST_LINE_TYPE = QP_PREQ_GRP.G_DISCOUNT) THEN
1502 l_sign := 1;
1503 ELSE
1504 l_sign := -1;
1505 END IF;
1506
1507 IF l_debug = FND_API.G_TRUE THEN
1508 QP_PREQ_GRP.engine_debug('List Price For Best Price Eval: ' || p_list_price);
1509
1510 END IF;
1511
1512 IF(i.modifier_level_code <> 'ORDER') THEN -- bug 4234043
1513 IF (i.OPERAND_CALCULATION_CODE = QP_PREQ_GRP.G_PERCENT_DISCOUNT) THEN
1514 v_benefit_price := l_sign * p_list_price * (i.OPERAND_VALUE / 100);
1515 ELSIF (i.OPERAND_CALCULATION_CODE = QP_PREQ_GRP.G_AMOUNT_DISCOUNT) THEN
1516 v_benefit_price := l_sign * i.OPERAND_VALUE;
1517 ELSIF (i.OPERAND_CALCULATION_CODE = QP_PREQ_GRP.G_NEWPRICE_DISCOUNT) THEN
1518 l_sign := 1; -- For NEWPRICE l_sign will always be 1 irrespective of whether it is a discount or surcharge
1519 v_benefit_price := l_sign * (p_list_price - i.OPERAND_VALUE);
1520 ELSIF (i.OPERAND_CALCULATION_CODE = QP_PREQ_GRP.G_LUMPSUM_DISCOUNT) THEN
1521 OPEN get_request_qty_cur;
1522 FETCH get_request_qty_cur INTO v_request_qty;
1523 CLOSE get_request_qty_cur;
1524 IF (i.modifier_level_code = QP_PREQ_GRP.G_LINE_GROUP) THEN
1525 l_calc_quantity := nvl(nvl(i.group_quantity, i.group_amount), v_request_qty);
1526 ELSE
1527 l_calc_quantity := v_request_qty;
1528 END IF;
1529 v_benefit_price := l_sign * i.OPERAND_VALUE/l_calc_quantity;
1530 END IF;
1531 ELSE ---- modifier_level_code='ORDER'
1532 IF (i.OPERAND_CALCULATION_CODE IN (QP_PREQ_GRP.G_PERCENT_DISCOUNT)) THEN
1533 v_benefit_price := l_sign * i.OPERAND_VALUE;
1534 END IF;
1535 END IF;
1536
1537 ELSIF (i.CREATED_FROM_LIST_LINE_TYPE = QP_PREQ_GRP.G_PRICE_BREAK_TYPE) THEN
1538
1539 IF l_debug = FND_API.G_TRUE THEN
1540 QP_PREQ_GRP.engine_debug('Best Price Eval For Price Break Line Quantity : ' || i.line_quantity); --[julin/4240067/4307242]
1541 QP_PREQ_GRP.engine_debug('Best Price Eval For Price Break Group Quantity : ' || i.group_quantity);
1542 QP_PREQ_GRP.engine_debug('Best Price Eval For Price Break Group Amount : ' || i.group_amount);
1543
1544 END IF;
1545 --[julin/4240067/4307242] using i.line_quantity
1546 l_request_qty := nvl(nvl(i.group_quantity,i.group_amount),i.line_quantity);
1547
1548 IF l_debug = FND_API.G_TRUE THEN
1549 QP_PREQ_GRP.engine_debug('Best Price Eval For Price Break Qualifier Value : ' || l_request_qty);
1550
1551 END IF;
1552
1553 /* Added for 3244060 */
1554 IF QP_PREQ_GRP.G_PUBLIC_API_CALL_FLAG = 'Y' THEN
1555 l_line_id :=i.line_detail_index;
1556 ELSE
1557 l_line_id :=i.created_from_list_line_id;
1558 END IF;
1559
1560 --[julin/5456188] using same price_break_calculation as PPREB, but with no bucket/netamt support
1561 OPEN get_request_qty_cur;
1562 FETCH get_request_qty_cur INTO v_request_qty;
1563 CLOSE get_request_qty_cur;
1564
1565 BEGIN
1566 select pricing_attribute
1567 into l_volume_attribute
1568 from qp_pricing_attributes
1569 where list_line_id = i.created_from_list_line_id
1570 and pricing_attribute_context = QP_PREQ_GRP.G_PRIC_VOLUME_CONTEXT
1571 and excluder_flag='N'; --3607956
1572 EXCEPTION
1573 When OTHERS Then
1574 l_volume_attribute := null;
1575 END;
1576
1577 IF (i.modifier_level_code IN (QP_PREQ_PUB.G_LINE_LEVEL, QP_PREQ_PUB.G_ORDER_LEVEL)) THEN
1578 IF l_volume_attribute = QP_PREQ_PUB.G_QUANTITY_ATTRIBUTE THEN
1579 l_total_value := 0;
1580 l_req_value_per_unit := v_request_qty;
1581 ELSE
1582 l_total_value := i.line_quantity;
1583 l_req_value_per_unit := v_request_qty;
1584 END IF;
1585 ELSE -- linegroup
1586 IF l_volume_attribute = QP_PREQ_PUB.G_QUANTITY_ATTRIBUTE THEN
1587 l_total_value := 0;
1588 l_req_value_per_unit := i.group_quantity;
1589 ELSE
1590 l_total_value := i.line_quantity;
1591 l_req_value_per_unit := i.group_amount;
1592 END IF;
1593 END IF;
1594
1595 QP_Calculate_Price_PUB.Price_Break_Calculation(l_line_id, --3244060
1596 i.price_break_type_code,
1597 p_line_index,
1598 l_req_value_per_unit,--p_req_value_per_unit
1599 0,--p_applied_req_value_per_unit, no net amt support here
1600 l_total_value,--p_total_value
1601 p_list_price,
1602 0,--p_line_quantity, not used
1603 0,--p_bucketed_adjustment, no bucket support here
1604 'N',--p_bucketed_flag, no bucket support here
1605 'N',--p_automatic_flag, don't want deletion
1606 x_benefit_amount,
1607 l_return_status,
1608 l_return_status_text);
1609
1610 IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
1611 NULL;
1612 END IF;
1613
1614 v_benefit_price := x_benefit_amount;
1615 END IF;
1616
1617 IF l_debug = FND_API.G_TRUE THEN
1618 QP_PREQ_GRP.engine_debug('Benefit Price For Best Price Eval: ' || v_benefit_price);
1619
1620 END IF;
1621 IF (i.modifier_level_code <> 'ORDER') THEN --bug 4234043
1622 IF (p_list_price > 0) THEN
1623 v_benefit_percent := (nvl(v_benefit_price,0) / p_list_price) * 100;
1624 ELSIF (p_list_price < 0) THEN
1625 v_benefit_percent := (nvl(ABS(v_benefit_price),0) / ABS(p_list_price)) * 100;
1626 ELSE
1627 --v_benefit_percent := 0;
1628 v_benefit_percent := v_benefit_price;--14054072
1629 END IF;
1630 ELSE -- bug 4234043
1631 v_benefit_percent :=v_benefit_price;
1632 END IF;
1633
1634 IF l_debug = FND_API.G_TRUE THEN
1635 QP_PREQ_GRP.engine_debug('Benefit Percent For Best Price Eval: ' || v_benefit_percent);
1636
1637 END IF;
1638 /*
1639 INDX,QP_Resolve_Incompatability_PVTRUN.best_price_evaluation.upd1,qp_npreq_ldets_tmp_N1,LINE_INDEX,1
1640 INDX,QP_Resolve_Incompatability_PVTRUN.best_price_evaluation.upd1,qp_npreq_ldets_tmp_N1,PRICING_STATUS_CODE,2
1641 INDX,QP_Resolve_Incompatability_PVTRUN.best_price_evaluation.upd1,qp_npreq_ldets_tmp_N1,PRICING_PHASE_ID,3
1642 INDX,QP_Resolve_Incompatability_PVTRUN.best_price_evaluation.upd1,qp_npreq_ldets_tmp_N1,CREATED_FROM_LIST_HEADER_ID,4
1646 SET BEST_PERCENT = v_benefit_percent
1643 INDX,QP_Resolve_Incompatability_PVTRUN.best_price_evaluation.upd1,qp_npreq_ldets_tmp_N1,CREATED_FROM_LIST_LINE_ID,5
1644 */
1645 UPDATE qp_npreq_ldets_tmp -- upd1
1647 WHERE CREATED_FROM_LIST_HEADER_ID = i.CREATED_FROM_LIST_HEADER_ID
1648 AND CREATED_FROM_LIST_LINE_ID = i.CREATED_FROM_LIST_LINE_ID
1649 AND PRICING_PHASE_ID = p_pricing_phase_id
1650 AND INCOMPATABILITY_GRP_CODE = p_incomp_grp_id
1651 AND PRICING_STATUS_CODE = QP_PREQ_GRP.G_STATUS_NEW
1652 AND LINE_INDEX = p_line_index;
1653
1654 END LOOP;
1655
1656 OPEN get_list_lines_in_order;
1657 FETCH get_list_lines_in_order into v_list_line_id;
1658 CLOSE get_list_lines_in_order;
1659
1660 /*
1661 INDX,QP_Resolve_Incompatability_PVTRUN.best_price_evaluation.upd2,qp_npreq_ldets_tmp_N1,LINE_INDEX,1
1662 INDX,QP_Resolve_Incompatability_PVTRUN.best_price_evaluation.upd2,qp_npreq_ldets_tmp_N1,PRICING_STATUS_CODE,2
1663 INDX,QP_Resolve_Incompatability_PVTRUN.best_price_evaluation.upd2,qp_npreq_ldets_tmp_N1,PRICING_PHASE_ID,3
1664 */
1665
1666 UPDATE qp_npreq_ldets_tmp -- upd2
1667 SET PRICING_STATUS_CODE = QP_PREQ_GRP.G_STATUS_BEST_PRICE_EVAL
1668 WHERE CREATED_FROM_LIST_LINE_ID <> v_list_line_id
1669 AND CREATED_FROM_LIST_TYPE_CODE not in (QP_PREQ_GRP.G_PRICE_LIST_HEADER,QP_PREQ_GRP.G_AGR_LIST_HEADER)
1670 AND PRICING_PHASE_ID = p_pricing_phase_id
1671 AND INCOMPATABILITY_GRP_CODE = p_incomp_grp_id
1672 AND LINE_INDEX = p_line_index
1673 AND PRICING_STATUS_CODE = QP_PREQ_GRP.G_STATUS_NEW;
1674
1675
1676 --smbalara start 11924159 START :issue PBH child lines not getting updated to status B, hence best price not working correctly when PBH
1677 --line is involved
1678 IF l_debug = FND_API.G_TRUE THEN
1679 QP_PREQ_GRP.engine_debug('Number of parent lines updated with status B :' || SQL%ROWCOUNT);
1680 END IF;
1681 -- print_tmp_tabs();
1682
1683 UPDATE qp_npreq_ldets_tmp x-- upd2
1684 SET PRICING_STATUS_CODE = QP_PREQ_GRP.G_STATUS_BEST_PRICE_EVAL
1685 WHERE CREATED_FROM_LIST_LINE_ID in
1686 (select RELATED_LIST_LINE_ID
1687 FROM QP_NPREQ_RLTD_LINES_TMP y,qp_npreq_ldets_tmp z
1688 WHERE y.LIST_LINE_ID =z.CREATED_FROM_LIST_LINE_ID
1689 AND y.LINE_DETAIL_INDEX = z.LINE_DETAIL_INDEX
1690 AND z.PRICING_STATUS_CODE=QP_PREQ_GRP.G_STATUS_BEST_PRICE_EVAL
1691 );
1692
1693 IF l_debug = FND_API.G_TRUE THEN
1694 QP_PREQ_GRP.engine_debug('Number of lines child updated with status B :' || SQL%ROWCOUNT);
1695 END IF;
1696 --print_tmp_tabs();
1697 --smbalara start 11924159 END
1698
1699 /* UPDATE qp_npreq_line_attrs_tmp a
1700 SET a.PRICING_STATUS_CODE = QP_PREQ_GRP.G_STATUS_BEST_PRICE_EVAL
1701 WHERE a.LIST_LINE_ID IN (SELECT b.CREATED_FROM_LIST_LINE_ID
1702 FROM qp_npreq_ldets_tmp b
1703 WHERE b.PRICING_STATUS_CODE = QP_PREQ_GRP.G_STATUS_BEST_PRICE_EVAL
1704 AND b.CREATED_FROM_LIST_LINE_ID <> v_list_line_id
1705 AND b.LINE_INDEX = p_line_index)
1706 AND a.LINE_INDEX = p_line_index; */
1707
1708 /* UPDATE qp_npreq_line_attrs_tmp a
1709 SET a.PRICING_STATUS_CODE = QP_PREQ_GRP.G_STATUS_BEST_PRICE_EVAL
1710 WHERE a.LIST_LINE_ID
1711 IN (SELECT b.CREATED_FROM_LIST_LINE_ID
1712 FROM qp_npreq_ldets_tmp b
1713 WHERE b.PRICING_STATUS_CODE = QP_PREQ_GRP.G_STATUS_BEST_PRICE_EVAL
1714 AND b.PRICING_PHASE_ID = p_pricing_phase_id
1715 AND b.INCOMPATABILITY_GRP_CODE = p_incomp_grp_id
1716 AND b.CREATED_FROM_LIST_LINE_ID <> v_list_line_id
1717 AND b.LINE_INDEX = p_line_index)
1718 AND a.LINE_INDEX = p_line_index; */
1719
1720 x_list_line_id := v_list_line_id;
1721 IF l_debug = FND_API.G_TRUE THEN
1722 QP_PREQ_GRP.engine_debug('Best List line Id: ' || v_list_line_id);
1723
1724 END IF;
1725 x_return_status := v_return_status; -- SUCCESS
1726
1727 EXCEPTION
1728 WHEN OTHERS THEN
1729 IF l_debug = FND_API.G_TRUE THEN
1730 QP_PREQ_GRP.engine_debug(v_routine_name || 'Unexpected Error');
1731 QP_PREQ_GRP.engine_debug(v_routine_name || ' ' || SQLERRM);
1732 END IF;
1733 v_return_status := FND_API.G_RET_STS_ERROR;
1734 x_return_status := v_return_status;
1735 END Best_Price_Evaluation;
1736
1737 PROCEDURE Delete_Lines_Complete (p_line_index_tbl IN QP_PREQ_GRP.NUMBER_TYPE,
1738 p_pricing_status_text IN VARCHAR2,
1739 x_return_status OUT NOCOPY VARCHAR2,
1740 x_return_status_txt OUT NOCOPY VARCHAR2) AS
1741 BEGIN
1742 l_debug := QP_PREQ_GRP.G_DEBUG_ENGINE;
1743 IF l_debug = FND_API.G_TRUE THEN
1744 QP_PREQ_GRP.engine_debug('deleting lines/ldets/attrs/rltd:'||p_pricing_status_text);
1745 END IF;
1746
1747 FORALL i IN p_line_index_tbl.FIRST..p_line_index_tbl.LAST
1748 UPDATE qp_npreq_lines_tmp
1749 SET pricing_status_code = QP_PREQ_PUB.G_NOT_VALID, process_status = QP_PREQ_PUB.G_NOT_VALID
1750 WHERE line_index = p_line_index_tbl(i)
1751 and line_id is null; --bug 7539796
1752 FORALL i IN p_line_index_tbl.FIRST..p_line_index_tbl.LAST
1753 UPDATE qp_npreq_ldets_tmp
1754 SET PRICING_STATUS_CODE = QP_PREQ_PUB.G_STATUS_DELETED,
1755 PRICING_STATUS_TEXT = substr(p_pricing_status_text,1,2000)
1756 WHERE LINE_INDEX = p_line_index_tbl(i);
1757 FORALL i IN p_line_index_tbl.FIRST..p_line_index_tbl.LAST
1758 UPDATE qp_npreq_line_attrs_tmp
1759 SET PRICING_STATUS_CODE = QP_PREQ_PUB.G_STATUS_DELETED,
1760 PRICING_STATUS_TEXT = substr(p_pricing_status_text,1,240)
1761 WHERE LINE_INDEX = p_line_index_tbl(i);
1762 FORALL i IN p_line_index_tbl.FIRST..p_line_index_tbl.LAST
1763 UPDATE qp_npreq_rltd_lines_tmp
1764 SET PRICING_STATUS_CODE = QP_PREQ_PUB.G_STATUS_DELETED,
1765 PRICING_STATUS_TEXT = substr(p_pricing_status_text,1,240)
1766 WHERE RELATED_LINE_INDEX = p_line_index_tbl(i);
1767 EXCEPTION
1768 WHEN OTHERS THEN
1769 x_return_status := FND_API.G_RET_STS_ERROR;
1770 x_return_status_txt := 'Unexpected error in QP_Resolve_Incompatability_PVT.Delete_Lines_Complete: ' || SQLERRM;
1771 END Delete_Lines_Complete;
1772
1773 PROCEDURE Delete_Ldets_Complete (p_line_detail_index_tbl IN QP_PREQ_GRP.NUMBER_TYPE,
1774 p_pricing_status_text IN VARCHAR2,
1775 x_return_status OUT NOCOPY VARCHAR2,
1776 x_return_status_txt OUT NOCOPY VARCHAR2) AS
1777 BEGIN
1778 l_debug := QP_PREQ_GRP.G_DEBUG_ENGINE;
1779 IF l_debug = FND_API.G_TRUE THEN
1780 QP_PREQ_GRP.engine_debug('deleting ldets/rltd:'||p_pricing_status_text);
1781 END IF;
1782
1783 FORALL i IN p_line_detail_index_tbl.FIRST..p_line_detail_index_tbl.LAST
1784 UPDATE qp_npreq_ldets_tmp a
1785 SET PRICING_STATUS_CODE = QP_PREQ_PUB.G_STATUS_DELETED,
1786 PRICING_STATUS_TEXT = substr(p_pricing_status_text,1,2000)
1787 WHERE LINE_DETAIL_INDEX = p_line_detail_index_tbl(i) OR
1788 EXISTS (SELECT 1 --[julin/4671446] also deleting children ldets
1789 FROM qp_npreq_rltd_lines_tmp
1790 WHERE LINE_DETAIL_INDEX = p_line_detail_index_tbl(i)
1791 AND RELATED_LINE_DETAIL_INDEX = a.LINE_DETAIL_INDEX);
1792 FORALL i IN p_line_detail_index_tbl.FIRST..p_line_detail_index_tbl.LAST
1793 UPDATE qp_npreq_rltd_lines_tmp
1794 SET PRICING_STATUS_CODE = QP_PREQ_PUB.G_STATUS_DELETED,
1795 PRICING_STATUS_TEXT = substr(p_pricing_status_text,1,240)
1796 WHERE (LINE_DETAIL_INDEX = p_line_detail_index_tbl(i) OR
1797 RELATED_LINE_DETAIL_INDEX = p_line_detail_index_tbl(i)); --[julin/4671446] also deleting children ldets
1798 EXCEPTION
1799 WHEN OTHERS THEN
1800 x_return_status := FND_API.G_RET_STS_ERROR;
1801 x_return_status_txt := 'Unexpected error in QP_Resolve_Incompatability_PVT.Delete_Ldets_Complete: ' || SQLERRM;
1802 END Delete_Ldets_Complete;
1803
1804
1805 PROCEDURE Delete_Incompatible_Lines(p_pricing_phase_id NUMBER,
1806 p_line_index NUMBER,
1807 p_incomp_grp_id VARCHAR2 := NULL,
1808 p_list_line_id NUMBER,
1809 p_excl_discount BOOLEAN,
1810 p_manual_dis_flag VARCHAR2,
1811 x_return_status OUT NOCOPY VARCHAR2) AS
1812
1813
1814 -- [prarasto/4141235] find loser prg line indexes to be deleted, incompatibility group
1815 --frontported fix done in 4134088
1816 CURSOR l_del_prg_lines_grp_cur IS
1817 SELECT rltd.RELATED_LINE_INDEX
1818 FROM qp_npreq_ldets_tmp ldets, qp_npreq_rltd_lines_tmp rltd
1819 WHERE ldets.INCOMPATABILITY_GRP_CODE = p_incomp_grp_id
1820 AND ldets.LINE_INDEX = p_line_index
1821 AND ldets.PRICING_PHASE_ID = p_pricing_phase_id
1822 AND ldets.CREATED_FROM_LIST_LINE_ID <> p_list_line_id
1823 AND ldets.LINE_DETAIL_TYPE_CODE <> QP_PREQ_GRP.G_CHILD_DETAIL_TYPE
1824 AND ldets.CREATED_FROM_LIST_LINE_TYPE IN (QP_PREQ_GRP.G_PROMO_GOODS_DISCOUNT)
1825 AND ldets.CREATED_FROM_LIST_LINE_ID = rltd.LIST_LINE_ID
1826 AND rltd.LINE_INDEX = p_line_index;
1827
1828 -- [prarasto/4141235] find loser prg line indexes to be deleted, incompatibility group
1829 --frontported fix done in 4134088
1830 CURSOR l_del_oid_ldets_grp_cur IS
1831 SELECT rltd.RELATED_LINE_DETAIL_INDEX
1832 FROM qp_npreq_ldets_tmp ldets, qp_npreq_rltd_lines_tmp rltd
1833 WHERE ldets.INCOMPATABILITY_GRP_CODE = p_incomp_grp_id
1834 AND ldets.LINE_INDEX = p_line_index
1835 AND ldets.PRICING_PHASE_ID = p_pricing_phase_id
1836 AND ldets.CREATED_FROM_LIST_LINE_ID <> p_list_line_id
1837 AND ldets.LINE_DETAIL_TYPE_CODE <> QP_PREQ_GRP.G_CHILD_DETAIL_TYPE
1838 AND ldets.CREATED_FROM_LIST_LINE_TYPE IN (QP_PREQ_GRP.G_OTHER_ITEM_DISCOUNT)
1839 AND ldets.CREATED_FROM_LIST_LINE_ID = rltd.LIST_LINE_ID
1840 AND rltd.LINE_INDEX = p_line_index;
1841
1842 -- [prarasto/4141235] find loser prg line indexes to be deleted, incompatibility group
1843 --frontported fix done in 4134088
1844 CURSOR l_del_prg_lines_excl_cur IS
1845 SELECT rltd.RELATED_LINE_INDEX
1846 FROM qp_npreq_ldets_tmp ldets, qp_npreq_rltd_lines_tmp rltd
1847 WHERE ldets.LINE_INDEX = p_line_index
1848 AND ldets.PRICING_PHASE_ID = p_pricing_phase_id
1849 AND ldets.CREATED_FROM_LIST_LINE_ID <> p_list_line_id
1850 AND ldets.LINE_DETAIL_TYPE_CODE <> QP_PREQ_GRP.G_CHILD_DETAIL_TYPE
1851 AND ldets.CREATED_FROM_LIST_LINE_TYPE IN (QP_PREQ_GRP.G_PROMO_GOODS_DISCOUNT)
1852 AND ldets.CREATED_FROM_LIST_LINE_ID = rltd.LIST_LINE_ID
1853 AND rltd.LINE_INDEX = p_line_index;
1857 CURSOR l_del_oid_ldets_excl_cur IS
1854
1855 -- [prarasto/4141235] find loser prg line indexes to be deleted, incompatibility group
1856 --frontported fix done in 4134088
1858 SELECT rltd.RELATED_LINE_DETAIL_INDEX
1859 FROM qp_npreq_ldets_tmp ldets, qp_npreq_rltd_lines_tmp rltd
1860 WHERE ldets.LINE_INDEX = p_line_index
1861 AND ldets.PRICING_PHASE_ID = p_pricing_phase_id
1862 AND ldets.CREATED_FROM_LIST_LINE_ID <> p_list_line_id
1863 AND ldets.LINE_DETAIL_TYPE_CODE <> QP_PREQ_GRP.G_CHILD_DETAIL_TYPE
1864 AND ldets.CREATED_FROM_LIST_LINE_TYPE IN (QP_PREQ_GRP.G_OTHER_ITEM_DISCOUNT)
1865 AND ldets.CREATED_FROM_LIST_LINE_ID = rltd.LIST_LINE_ID
1866 AND rltd.LINE_INDEX = p_line_index;
1867
1868 l_del_index_tbl QP_PREQ_GRP.NUMBER_TYPE;
1869
1870 l_status_code VARCHAR2(30);
1871 l_status_text VARCHAR2(240);
1872
1873 v_return_status VARCHAR2(30) := FND_API.G_RET_STS_SUCCESS;
1874 v_routine_name CONSTANT VARCHAR2(240):='Routine:QP_Resolve_Incompatability_PVTRUN.Delete_Incompatible_Lines';
1875
1876 BEGIN
1877 l_debug := QP_PREQ_GRP.G_DEBUG_ENGINE;
1878
1879 IF l_debug = FND_API.G_TRUE THEN
1880 QP_PREQ_GRP.engine_debug('Enter Delete_Incompatible_Lines:'||p_line_index||':'||p_incomp_grp_id||':'||p_list_line_id||':'||p_manual_dis_flag);
1881 END IF;
1882
1883 IF (p_manual_dis_flag = QP_PREQ_GRP.G_YES) THEN
1884 IF (p_excl_discount = FALSE) THEN
1885
1886 /*
1887 INDX,QP_Resolve_Incompatability_PVTRUN.delete_incompatible_lines.upd1,qp_npreq_ldets_tmp_N1,LINE_INDEX,1
1888 INDX,QP_Resolve_Incompatability_PVTRUN.delete_incompatible_lines.upd1,qp_npreq_ldets_tmp_N1,PRICING_STATUS_CODE,2
1889 INDX,QP_Resolve_Incompatability_PVTRUN.delete_incompatible_lines.upd1,qp_npreq_ldets_tmp_N1,PRICING_PHASE_ID,3
1890 */
1891
1892 UPDATE qp_npreq_ldets_tmp --upd1
1893 SET PRICING_STATUS_CODE = QP_PREQ_GRP.G_STATUS_INCOMP_LOGIC
1894 WHERE INCOMPATABILITY_GRP_CODE = p_incomp_grp_id
1895 AND LINE_INDEX = p_line_index
1896 AND PRICING_PHASE_ID = p_pricing_phase_id
1897 AND CREATED_FROM_LIST_LINE_ID <> p_list_line_id
1898 AND CREATED_FROM_LIST_LINE_TYPE NOT IN (QP_PREQ_GRP.G_DISCOUNT,QP_PREQ_GRP.G_SURCHARGE)
1899 AND LINE_DETAIL_TYPE_CODE <> QP_PREQ_GRP.G_CHILD_DETAIL_TYPE -- Don't delete PBH Children any time
1900 AND PRICING_STATUS_CODE = QP_PREQ_GRP.G_STATUS_NEW;
1901
1902 --Begin Bug No: 7691661
1903 UPDATE qp_npreq_ldets_tmp
1904 SET PRICING_STATUS_CODE = QP_PREQ_GRP.G_STATUS_INCOMP_LOGIC
1905 WHERE INCOMPATABILITY_GRP_CODE = p_incomp_grp_id
1906 AND LINE_INDEX = p_line_index
1907 AND PRICING_PHASE_ID = p_pricing_phase_id
1908 AND CREATED_FROM_LIST_LINE_ID <> p_list_line_id
1909 AND ASK_FOR_FLAG = QP_PREQ_GRP.G_YES
1910 AND CREATED_FROM_LIST_LINE_TYPE IN (QP_PREQ_GRP.G_DISCOUNT,QP_PREQ_GRP.G_SURCHARGE)
1911 AND LINE_DETAIL_TYPE_CODE <> QP_PREQ_GRP.G_CHILD_DETAIL_TYPE -- Don't delete PBH Children any time
1912 AND PRICING_STATUS_CODE IN (QP_PREQ_GRP.G_STATUS_NEW,QP_PREQ_GRP.G_STATUS_BEST_PRICE_EVAL);
1913 --End Bug No: 7691661
1914
1915
1916 /*
1917 INDX,QP_Resolve_Incompatability_PVTRUN.delete_incompatible_lines.upd2,qp_npreq_ldets_tmp_N3,LINE_INDEX,1
1918 INDX,QP_Resolve_Incompatability_PVTRUN.delete_incompatible_lines.upd2,qp_npreq_ldets_tmp_N3,PRICING_PHASE_ID,2
1919 INDX,QP_Resolve_Incompatability_PVTRUN.delete_incompatible_lines.upd2,qp_npreq_ldets_tmp_N3,ASK_FOR_FLAG,3
1920 INDX,QP_Resolve_Incompatability_PVTRUN.delete_incompatible_lines.upd2,qp_npreq_ldets_tmp_N3,CREATED_FROM_LIST_LINE_TYPE,4
1921 INDX,QP_Resolve_Incompatability_PVTRUN.delete_incompatible_lines.upd2,qp_npreq_ldets_tmp_N3,PRICING_STATUS_CODE,5
1922 */
1923
1924 UPDATE qp_npreq_ldets_tmp --upd2
1925 SET PRICING_STATUS_CODE = QP_PREQ_GRP.G_STATUS_NEW,
1926 AUTOMATIC_FLAG = QP_PREQ_GRP.G_NO,
1927 APPLIED_FLAG = QP_PREQ_GRP.G_NO
1928 WHERE INCOMPATABILITY_GRP_CODE = p_incomp_grp_id
1929 AND LINE_INDEX = p_line_index
1930 AND PRICING_PHASE_ID = p_pricing_phase_id
1931 AND CREATED_FROM_LIST_LINE_ID <> p_list_line_id
1932 AND ASK_FOR_FLAG = QP_PREQ_GRP.G_NO
1933 AND CREATED_FROM_LIST_LINE_TYPE IN (QP_PREQ_GRP.G_DISCOUNT,QP_PREQ_GRP.G_SURCHARGE)
1934 AND LINE_DETAIL_TYPE_CODE <> QP_PREQ_GRP.G_CHILD_DETAIL_TYPE -- Don't delete PBH Children any time
1935 AND PRICING_STATUS_CODE IN (QP_PREQ_GRP.G_STATUS_NEW,QP_PREQ_GRP.G_STATUS_BEST_PRICE_EVAL);
1936
1937 /* UPDATE qp_npreq_line_attrs_tmp a
1938 SET a.PRICING_STATUS_CODE = QP_PREQ_GRP.G_STATUS_NEW
1939 WHERE a.LIST_LINE_ID
1940 IN (SELECT b.CREATED_FROM_LIST_LINE_ID
1941 FROM qp_npreq_ldets_tmp b
1942 WHERE b.PRICING_STATUS_CODE = QP_PREQ_GRP.G_STATUS_NEW
1943 AND b.PRICING_PHASE_ID = p_pricing_phase_id
1944 AND b.INCOMPATABILITY_GRP_CODE = p_incomp_grp_id
1945 AND b.CREATED_FROM_LIST_LINE_ID <> p_list_line_id
1946 AND ASK_FOR_FLAG = QP_PREQ_GRP.G_NO
1947 AND b.CREATED_FROM_LIST_LINE_TYPE IN (QP_PREQ_GRP.G_DISCOUNT,QP_PREQ_GRP.G_SURCHARGE)
1948 AND b.LINE_INDEX = p_line_index)
1949 AND a.LINE_INDEX = p_line_index; */
1950
1951 ELSE
1952 /*
1953 INDX,QP_Resolve_Incompatability_PVTRUN.delete_incompatible_lines.upd3,qp_npreq_ldets_tmp_N1,LINE_INDEX,1
1954 INDX,QP_Resolve_Incompatability_PVTRUN.delete_incompatible_lines.upd3,qp_npreq_ldets_tmp_N1,PRICING_STATUS_CODE,2
1955 INDX,QP_Resolve_Incompatability_PVTRUN.delete_incompatible_lines.upd3,qp_npreq_ldets_tmp_N1,PRICING_PHASE_ID,3
1956 */
1957
1958 UPDATE qp_npreq_ldets_tmp --upd3
1959 SET PRICING_STATUS_CODE = QP_PREQ_GRP.G_STATUS_INCOMP_LOGIC
1960 WHERE LINE_INDEX = p_line_index
1961 AND PRICING_PHASE_ID = p_pricing_phase_id
1962 AND CREATED_FROM_LIST_LINE_ID <> p_list_line_id
1966
1963 AND CREATED_FROM_LIST_LINE_TYPE NOT IN (QP_PREQ_GRP.G_DISCOUNT,QP_PREQ_GRP.G_SURCHARGE)
1964 AND LINE_DETAIL_TYPE_CODE <> QP_PREQ_GRP.G_CHILD_DETAIL_TYPE -- Don't delete PBH Children any time
1965 AND PRICING_STATUS_CODE = QP_PREQ_GRP.G_STATUS_NEW;
1967 /*
1968 INDX,QP_Resolve_Incompatability_PVTRUN.delete_incompatible_lines.upd4,qp_npreq_ldets_tmp_N3,LINE_INDEX,1
1969 INDX,QP_Resolve_Incompatability_PVTRUN.delete_incompatible_lines.upd4,qp_npreq_ldets_tmp_N3,PRICING_PHASE_ID,2
1970 INDX,QP_Resolve_Incompatability_PVTRUN.delete_incompatible_lines.upd4,qp_npreq_ldets_tmp_N3,ASK_FOR_FLAG,3
1971 INDX,QP_Resolve_Incompatability_PVTRUN.delete_incompatible_lines.upd4,qp_npreq_ldets_tmp_N3,CREATED_FROM_LIST_LINE_TYPE,4
1972 INDX,QP_Resolve_Incompatability_PVTRUN.delete_incompatible_lines.upd4,qp_npreq_ldets_tmp_N3,PRICING_STATUS_CODE,5
1973 */
1974 --Begin Bug No: 7691661
1975 UPDATE qp_npreq_ldets_tmp
1976 SET PRICING_STATUS_CODE = QP_PREQ_GRP.G_STATUS_INCOMP_LOGIC
1977 WHERE LINE_INDEX = p_line_index
1978 AND PRICING_PHASE_ID = p_pricing_phase_id
1979 AND CREATED_FROM_LIST_LINE_ID <> p_list_line_id
1980 AND ASK_FOR_FLAG = QP_PREQ_GRP.G_YES
1981 AND CREATED_FROM_LIST_LINE_TYPE IN (QP_PREQ_GRP.G_DISCOUNT,QP_PREQ_GRP.G_SURCHARGE)
1982 AND LINE_DETAIL_TYPE_CODE <> QP_PREQ_GRP.G_CHILD_DETAIL_TYPE -- Don't delete PBH Children any time
1983 AND PRICING_STATUS_CODE IN (QP_PREQ_GRP.G_STATUS_NEW,QP_PREQ_GRP.G_STATUS_BEST_PRICE_EVAL);
1984 --End Bug No: 7691661
1985
1986
1987 UPDATE qp_npreq_ldets_tmp --upd4
1988 SET PRICING_STATUS_CODE = QP_PREQ_GRP.G_STATUS_NEW,
1989 AUTOMATIC_FLAG = QP_PREQ_GRP.G_NO,
1990 APPLIED_FLAG = QP_PREQ_GRP.G_NO
1991 WHERE LINE_INDEX = p_line_index
1992 AND PRICING_PHASE_ID = p_pricing_phase_id
1993 AND CREATED_FROM_LIST_LINE_ID <> p_list_line_id
1994 AND ASK_FOR_FLAG = QP_PREQ_GRP.G_NO
1995 AND CREATED_FROM_LIST_LINE_TYPE IN (QP_PREQ_GRP.G_DISCOUNT,QP_PREQ_GRP.G_SURCHARGE)
1996 AND LINE_DETAIL_TYPE_CODE <> QP_PREQ_GRP.G_CHILD_DETAIL_TYPE -- Don't delete PBH Children any time
1997 AND PRICING_STATUS_CODE IN (QP_PREQ_GRP.G_STATUS_NEW,QP_PREQ_GRP.G_STATUS_BEST_PRICE_EVAL);
1998
1999 /* UPDATE qp_npreq_line_attrs_tmp a
2000 SET a.PRICING_STATUS_CODE = QP_PREQ_GRP.G_STATUS_NEW
2001 WHERE a.LIST_LINE_ID
2002 IN (SELECT b.CREATED_FROM_LIST_LINE_ID
2003 FROM qp_npreq_ldets_tmp b
2004 WHERE b.PRICING_STATUS_CODE = QP_PREQ_GRP.G_STATUS_NEW
2005 AND b.PRICING_PHASE_ID = p_pricing_phase_id
2006 AND b.INCOMPATABILITY_GRP_CODE = p_incomp_grp_id
2007 AND b.CREATED_FROM_LIST_LINE_ID <> p_list_line_id
2008 AND ASK_FOR_FLAG = QP_PREQ_GRP.G_NO
2009 AND b.CREATED_FROM_LIST_LINE_TYPE IN (QP_PREQ_GRP.G_DISCOUNT,QP_PREQ_GRP.G_SURCHARGE)
2010 AND b.LINE_INDEX = p_line_index)
2011 AND a.LINE_INDEX = p_line_index; */
2012
2013 END IF; --p_exclusive_discount = TRUE
2014 ELSE -- Automatic discounts
2015 IF (p_excl_discount = FALSE) THEN
2016
2017 /*
2018 INDX,QP_Resolve_Incompatability_PVTRUN.delete_incompatible_lines.upd5,qp_npreq_ldets_tmp_N1,LINE_INDEX,1
2019 INDX,QP_Resolve_Incompatability_PVTRUN.delete_incompatible_lines.upd5,qp_npreq_ldets_tmp_N1,PRICING_STATUS_CODE,2
2020 INDX,QP_Resolve_Incompatability_PVTRUN.delete_incompatible_lines.upd5,qp_npreq_ldets_tmp_N1,PRICING_PHASE_ID,3
2021 */
2022
2023 UPDATE qp_npreq_ldets_tmp --upd5
2024 SET PRICING_STATUS_CODE = QP_PREQ_GRP.G_STATUS_INCOMP_LOGIC
2025 WHERE INCOMPATABILITY_GRP_CODE = p_incomp_grp_id
2026 AND LINE_INDEX = p_line_index
2027 AND PRICING_PHASE_ID = p_pricing_phase_id
2028 AND CREATED_FROM_LIST_LINE_ID <> p_list_line_id
2029 AND LINE_DETAIL_TYPE_CODE <> QP_PREQ_GRP.G_CHILD_DETAIL_TYPE -- Don't delete PBH Children any time
2030 AND PRICING_STATUS_CODE = QP_PREQ_GRP.G_STATUS_NEW;
2031 ELSE
2032 UPDATE qp_npreq_ldets_tmp
2033 SET PRICING_STATUS_CODE = QP_PREQ_GRP.G_STATUS_INCOMP_LOGIC
2034 WHERE LINE_INDEX = p_line_index
2035 AND PRICING_PHASE_ID = p_pricing_phase_id
2036 AND CREATED_FROM_LIST_LINE_ID <> p_list_line_id
2037 AND LINE_DETAIL_TYPE_CODE <> QP_PREQ_GRP.G_CHILD_DETAIL_TYPE -- Don't delete PBH Children any time
2038 AND PRICING_STATUS_CODE = QP_PREQ_GRP.G_STATUS_NEW;
2039 END IF; -- p_exclusive_discount = FALSE
2040 END IF;
2041
2042 IF (p_excl_discount = FALSE) THEN
2043
2044 IF l_debug = FND_API.G_TRUE THEN
2045 QP_PREQ_GRP.engine_debug('Checking for PRG/OID losers in incompatibility group '||p_incomp_grp_id);
2046 END IF;
2047
2048 -- [prarasto/4141235] delete loser prg/oid lines/ldets/attrs/rltd
2049 --frontported fix done in 41340888
2050 OPEN l_del_prg_lines_grp_cur;
2051 FETCH l_del_prg_lines_grp_cur
2052 BULK COLLECT INTO l_del_index_tbl;
2053 CLOSE l_del_prg_lines_grp_cur;
2054
2055 IF (l_del_index_tbl.count > 0) THEN
2056 Delete_Lines_Complete(l_del_index_tbl, 'PRG DELETED BY INCOMPATIBILITY LOGIC', l_status_code, l_status_text);
2057 END IF;
2058
2059 -- [prarasto/4141235] delete loser oid ldets/rltd
2060 --frontported fix done in 41340888
2061 OPEN l_del_oid_ldets_grp_cur;
2062 FETCH l_del_oid_ldets_grp_cur
2063 BULK COLLECT INTO l_del_index_tbl;
2064 CLOSE l_del_oid_ldets_grp_cur;
2065
2066 IF (l_del_index_tbl.count > 0) THEN
2067 Delete_Ldets_Complete(l_del_index_tbl, 'OID DELETED BY INCOMPATIBILITY LOGIC', l_status_code, l_status_text);
2068 END IF;
2069
2070 ELSE
2071
2072 IF l_debug = FND_API.G_TRUE THEN
2073 QP_PREQ_GRP.engine_debug('Checking for PRG/OID losers to exclusive group line' );
2074 END IF;
2075
2076 -- [prarasto/4141235] delete loser prg/oid lines/ldets/attrs/rltd, exclusive winner
2077 --frontported fix done in 41340888
2078 OPEN l_del_prg_lines_excl_cur;
2079 FETCH l_del_prg_lines_excl_cur
2080 BULK COLLECT INTO l_del_index_tbl;
2081 CLOSE l_del_prg_lines_excl_cur;
2082
2083 IF (l_del_index_tbl.count > 0) THEN
2084 Delete_Lines_Complete(l_del_index_tbl, 'PRG DELETED BY INCOMPATIBILITY LOGIC', l_status_code, l_status_text);
2085 END IF;
2086
2087 -- [prarasto/4141235] delete loser oid ldets/rltd, exclusive winner
2088 --frontported fix done in 41340888
2089 OPEN l_del_oid_ldets_excl_cur;
2090 FETCH l_del_oid_ldets_excl_cur
2091 BULK COLLECT INTO l_del_index_tbl;
2092 CLOSE l_del_oid_ldets_excl_cur;
2093
2094 IF (l_del_index_tbl.count > 0) THEN
2095 Delete_Ldets_Complete(l_del_index_tbl, 'OID DELETED BY INCOMPATIBILITY LOGIC', l_status_code, l_status_text);
2096 END IF;
2097
2098 END IF;
2099
2100 x_return_status := v_return_status;
2101 EXCEPTION
2102 WHEN OTHERS THEN
2103 IF l_debug = FND_API.G_TRUE THEN
2104 QP_PREQ_GRP.engine_debug(v_routine_name || ' ' || SQLERRM);
2105 END IF;
2106 v_return_status := FND_API.G_RET_STS_ERROR;
2107 x_return_status := v_return_status;
2108 END Delete_Incompatible_Lines;
2109
2110 PROCEDURE Best_Price_For_Phase(p_list_price NUMBER,
2111 p_line_index NUMBER,
2112 p_pricing_phase_id NUMBER,
2113 x_return_status OUT NOCOPY VARCHAR2,
2114 x_return_status_txt OUT NOCOPY VARCHAR2) AS
2115 -- Index Certificate
2116
2117 /*
2118 INDX,QP_Resolve_Incompatability_PVTRUN.best_price_for_phase.incomp_cur,qp_npreq_ldets_tmp_N1,LINE_INDEX,1
2119 INDX,QP_Resolve_Incompatability_PVTRUN.best_price_for_phase.incomp_cur,qp_npreq_ldets_tmp_N1,PRICING_STATUS_CODE,2
2120 INDX,QP_Resolve_Incompatability_PVTRUN.best_price_for_phase.incomp_cur,qp_npreq_ldets_tmp_N1,PRICING_PHASE_ID,3
2121 */
2122
2123 CURSOR incomp_cur(p_manual_dis_flag VARCHAR2) IS
2124 SELECT DISTINCT INCOMPATABILITY_GRP_CODE
2125 FROM qp_npreq_ldets_tmp
2126 WHERE PRICING_PHASE_ID = p_pricing_phase_id
2127 AND INCOMPATABILITY_GRP_CODE IS NOT NULL
2128 AND LINE_INDEX = p_line_index
2129 AND ((AUTOMATIC_FLAG = QP_PREQ_GRP.G_YES AND p_manual_dis_flag = QP_PREQ_GRP.G_YES) OR
2130 p_manual_dis_flag = QP_PREQ_GRP.G_NO)
2131 AND PRICING_STATUS_CODE = QP_PREQ_GRP.G_STATUS_NEW;
2132
2133
2134 x_list_line_id NUMBER;
2135 v_return_status VARCHAR2(30) := FND_API.G_RET_STS_SUCCESS;
2136 x_ret_status VARCHAR2(30);
2137 v_excl_flag BOOLEAN := FALSE;
2138 v_manual_dis_flag VARCHAR2(1) := nvl(QP_PREQ_GRP.G_MANUAL_DISCOUNT_FLAG,'Y');
2139
2140 INVALID_BEST_PRICE EXCEPTION;
2141
2142 v_routine_name CONSTANT VARCHAR2(240) := 'Routine:QP_Resolve_Incompatability_PVTRUN.Best_Price_For_Phase';
2143
2144 BEGIN
2145 l_debug := QP_PREQ_GRP.G_DEBUG_ENGINE;
2146
2147 --[julin/4116856] moved deletion of un-asked-for promotions to QP_PREQ_GRP
2148
2149 /* UPDATE qp_npreq_line_attrs_tmp a
2150 SET a.PRICING_STATUS_CODE = QP_PREQ_GRP.G_STATUS_BEST_PRICE_EVAL
2151 WHERE a.LIST_LINE_ID IN (SELECT b.CREATED_FROM_LIST_LINE_ID
2152 FROM qp_npreq_ldets_tmp b
2153 WHERE b.PRICING_STATUS_CODE = QP_PREQ_GRP.G_STATUS_BEST_PRICE_EVAL
2154 AND b.PRICING_PHASE_ID = p_pricing_phase_id
2155 AND b.ASK_FOR_FLAG = QP_PREQ_GRP.G_YES
2156 AND b.LINE_INDEX = p_line_index)
2157 AND a.LINE_INDEX = p_line_index; */
2158
2159 FOR i IN incomp_cur(v_manual_dis_flag)
2160 LOOP
2161 IF (v_excl_flag = FALSE) THEN
2162 -- Best Price Evaluation
2163 IF l_debug = FND_API.G_TRUE THEN
2164 QP_PREQ_GRP.engine_debug('Best Price For Phase .....');
2165 END IF;
2166 Best_Price_Evaluation(p_list_price,
2167 p_line_index,
2168 p_pricing_phase_id,
2169 i.INCOMPATABILITY_GRP_CODE,
2170 NULL, -- Added for bug#2661540
2171 v_manual_dis_flag,
2172 x_list_line_id,
2173 x_ret_status);
2174 IF (x_ret_status = FND_API.G_RET_STS_ERROR) THEN
2175 RAISE INVALID_BEST_PRICE;
2176 END IF;
2177 END IF;
2178 -- Incomp_grp_id ='EXCL' has the highest priority(exclusivity)
2182 IF (v_excl_flag = FALSE) THEN
2179 IF (i.INCOMPATABILITY_GRP_CODE = QP_PREQ_GRP.G_INCOMP_EXCLUSIVE) THEN
2180 v_excl_flag := TRUE;
2181 END IF;
2183 Delete_Incompatible_Lines(p_pricing_phase_id,
2184 p_line_index,
2185 i.INCOMPATABILITY_GRP_CODE,
2186 x_list_line_id,
2187 v_excl_flag,
2188 v_manual_dis_flag,
2189 x_ret_status);
2190
2191 END IF;
2192 END LOOP;
2193 IF(v_excl_flag = TRUE) THEN
2194 Delete_Incompatible_Lines(p_pricing_phase_id,
2195 p_line_index,
2196 NULL, -- incomp_grp_id
2197 x_list_line_id,
2198 v_excl_flag,
2199 v_manual_dis_flag,
2200 x_ret_status);
2201
2202 END IF;
2203 EXCEPTION
2204 WHEN INVALID_BEST_PRICE THEN
2205 IF l_debug = FND_API.G_TRUE THEN
2206 QP_PREQ_GRP.engine_debug(v_routine_name || ' ' || SQLERRM);
2207 END IF;
2208 x_return_status_txt := v_routine_name || ' ' || SQLERRM;
2209 v_return_status := QP_PREQ_GRP.G_STATUS_BEST_PRICE_EVAL_ERROR;
2210 x_return_status := v_return_status;
2211 WHEN OTHERS THEN
2212 IF l_debug = FND_API.G_TRUE THEN
2213 QP_PREQ_GRP.engine_debug(v_routine_name || 'Unexpected Error');
2214 QP_PREQ_GRP.engine_debug(v_routine_name || ' ' || SQLERRM);
2215 END IF;
2216 v_return_status := FND_API.G_RET_STS_ERROR;
2217 x_return_status := v_return_status;
2218 END Best_Price_For_Phase;
2219
2220 PROCEDURE Resolve_Incompatability(p_pricing_phase_id NUMBER,
2221 p_processing_flag VARCHAR2,
2222 p_list_price NUMBER,
2223 p_line_index NUMBER,
2224 x_return_status OUT NOCOPY VARCHAR2,
2225 x_return_status_txt OUT NOCOPY VARCHAR2) AS
2226
2227 -- Index Certificate
2228
2229 /*
2230 INDX,QP_Resolve_Incompatability_PVTRUN.resolve_incompatability.incomp_cur,qp_npreq_ldets_tmp_N1,LINE_INDEX,1
2231 INDX,QP_Resolve_Incompatability_PVTRUN.resolve_incompatability.incomp_cur,qp_npreq_ldets_tmp_N1,PRICING_STATUS_CODE,2
2232 INDX,QP_Resolve_Incompatability_PVTRUN.resolve_incompatability.incomp_cur,qp_npreq_ldets_tmp_N1,PRICING_PHASE_ID,3
2233 */
2234
2235 CURSOR incomp_cur(l_manual_dis_flag VARCHAR2) IS
2236 SELECT DISTINCT INCOMPATABILITY_GRP_CODE , PRICING_STATUS_CODE
2237 FROM qp_npreq_ldets_tmp
2238 WHERE PRICING_PHASE_ID = p_pricing_phase_id
2239 AND INCOMPATABILITY_GRP_CODE IS NOT NULL
2240 AND PRICING_STATUS_CODE = QP_PREQ_GRP.G_STATUS_NEW
2241 AND ((AUTOMATIC_FLAG = QP_PREQ_GRP.G_YES AND l_manual_dis_flag = QP_PREQ_GRP.G_YES) OR
2242 l_manual_dis_flag = QP_PREQ_GRP.G_NO)
2243 AND LINE_INDEX = p_line_index;
2244
2245 /*
2246 INDX,QP_Resolve_Incompatability_PVTRUN.resolve_incompatability.each_incomp_cur,qp_npreq_ldets_tmp_N3,LINE_INDEX,1
2247 INDX,QP_Resolve_Incompatability_PVTRUN.resolve_incompatability.each_incomp_cur,qp_npreq_ldets_tmp_N3,PRICING_PHASE_ID,2
2248 INDX,QP_Resolve_Incompatability_PVTRUN.resolve_incompatability.each_incomp_cur,qp_npreq_ldets_tmp_N3,ASK_FOR_FLAG,3
2249
2250 INDX,QP_Resolve_Incompatability_PVTRUN.resolve_incompatability.each_incomp_cur,qp_npreq_line_attrs_tmp_N2,PRICING_STATUS_CODE,1
2251 INDX,QP_Resolve_Incompatability_PVTRUN.resolve_incompatability.each_incomp_cur,qp_npreq_line_attrs_tmp_N2,ATTRIBUTE_TYPE,2
2252 INDX,QP_Resolve_Incompatability_PVTRUN.resolve_incompatability.each_incomp_cur,qp_npreq_line_attrs_tmp_N2,CONTEXT,3
2253 INDX,QP_Resolve_Incompatability_PVTRUN.resolve_incompatability.each_incomp_cur,qp_npreq_line_attrs_tmp_N2,ATTRIBUTE,4
2254 INDX,QP_Resolve_Incompatability_PVTRUN.resolve_incompatability.each_incomp_cur,qp_npreq_line_attrs_tmp_N2,LINE_INDEX,5
2255 INDX,QP_Resolve_Incompatability_PVTRUN.resolve_incompatability.each_incomp_cur,qp_npreq_line_attrs_tmp_N2,VALUE_FROM,6
2256 */
2257
2258 --UNION
2259
2260 /*
2261 INDX,QP_Resolve_Incompatability_PVTRUN.resolve_incompatability.each_incomp_cur,qp_npreq_ldets_tmp_N3,LINE_INDEX,1
2262 INDX,QP_Resolve_Incompatability_PVTRUN.resolve_incompatability.each_incomp_cur,qp_npreq_ldets_tmp_N3,PRICING_PHASE_ID,2
2263 INDX,QP_Resolve_Incompatability_PVTRUN.resolve_incompatability.each_incomp_cur,qp_npreq_ldets_tmp_N3,ASK_FOR_FLAG,3
2264 */
2265
2266 CURSOR each_incomp_cur(p_incomp_grp_id VARCHAR2,l_manual_dis_flag VARCHAR2) IS
2267 SELECT a.CREATED_FROM_LIST_HEADER_ID,a.CREATED_FROM_LIST_LINE_ID,a.INCOMPATABILITY_GRP_CODE,a.ASK_FOR_FLAG
2268 FROM qp_npreq_ldets_tmp a
2269 WHERE EXISTS (SELECT 'X'
2270 FROM qp_npreq_line_attrs_tmp b
2271 WHERE a.LINE_INDEX = b.LINE_INDEX
2272 AND b.ATTRIBUTE_TYPE = QP_PREQ_GRP.G_QUALIFIER_TYPE
2273 AND b.PRICING_STATUS_CODE = QP_PREQ_GRP.G_STATUS_UNCHANGED
2274 AND b.ATTRIBUTE IN (QP_PREQ_GRP.G_QUAL_ATTRIBUTE1,
2275 QP_PREQ_GRP.G_QUAL_ATTRIBUTE2,
2276 QP_PREQ_GRP.G_QUAL_ATTRIBUTE6)
2277 AND b.CONTEXT = QP_PREQ_GRP.G_LIST_HEADER_CONTEXT
2278 AND b.VALUE_FROM = decode(b.ATTRIBUTE,
2279 QP_PREQ_GRP.G_QUAL_ATTRIBUTE1,to_char(a.CREATED_FROM_LIST_HEADER_ID),
2280 QP_PREQ_GRP.G_QUAL_ATTRIBUTE2,to_char(a.CREATED_FROM_LIST_LINE_ID),
2281 QP_PREQ_GRP.G_QUAL_ATTRIBUTE6,to_char(a.CREATED_FROM_LIST_HEADER_ID)))
2282 AND a.ASK_FOR_FLAG = QP_PREQ_GRP.G_YES
2283 AND a.INCOMPATABILITY_GRP_CODE = p_incomp_grp_id
2284 AND a.PRICING_PHASE_ID = p_pricing_phase_id
2285 AND a.LINE_INDEX = p_line_index
2286 AND a.LINE_DETAIL_TYPE_CODE <> QP_PREQ_GRP.G_CHILD_DETAIL_TYPE
2287 AND a.PRICING_STATUS_CODE = QP_PREQ_GRP.G_STATUS_NEW
2291 SELECT a.CREATED_FROM_LIST_HEADER_ID,a.CREATED_FROM_LIST_LINE_ID,a.INCOMPATABILITY_GRP_CODE,'N' ASK_FOR_FLAG
2288 AND ((a.AUTOMATIC_FLAG = QP_PREQ_GRP.G_YES AND l_manual_dis_flag = QP_PREQ_GRP.G_YES) OR
2289 l_manual_dis_flag = QP_PREQ_GRP.G_NO)
2290 UNION
2292 FROM qp_npreq_ldets_tmp a
2293 WHERE a.ASK_FOR_FLAG = QP_PREQ_GRP.G_NO -- Removed NVL , expect some issues
2294 AND a.INCOMPATABILITY_GRP_CODE = p_incomp_grp_id
2295 AND a.PRICING_PHASE_ID = p_pricing_phase_id
2296 AND a.LINE_INDEX = p_line_index
2297 AND a.LINE_DETAIL_TYPE_CODE <> QP_PREQ_GRP.G_CHILD_DETAIL_TYPE
2298 AND a.PRICING_STATUS_CODE = QP_PREQ_GRP.G_STATUS_NEW
2299 AND ((a.AUTOMATIC_FLAG = QP_PREQ_GRP.G_YES AND l_manual_dis_flag = QP_PREQ_GRP.G_YES) OR
2300 l_manual_dis_flag = QP_PREQ_GRP.G_NO)
2301 ORDER BY 4 desc;
2302
2303 v_this_is_the_list_line_id NUMBER;
2304 v_first_list_line_id NUMBER;
2305 v_count NUMBER:= 0;
2306 v_others_flag BOOLEAN:= TRUE;
2307 v_high_precedence NUMBER;
2308 v_ask_for_flag VARCHAR2(1);
2309 v_excl_flag BOOLEAN := FALSE;
2310 v_excl_list_line_id NUMBER;
2311 x_best_list_line_id NUMBER;
2312 x_ret_status VARCHAR2(30);
2313 v_return_status VARCHAR2(30) := FND_API.G_RET_STS_SUCCESS;
2314 v_routine_name CONSTANT VARCHAR2(240):='Routine:QP_Resolve_Incompatability_PVTRUN.Resolve_Incompatability';
2315 p_manual_dis_flag VARCHAR2(1) := nvl(QP_PREQ_GRP.G_MANUAL_DISCOUNT_FLAG,'Y');
2316 v_ask_for_constant CONSTANT NUMBER := -100000;
2317
2318
2319 l_precedence_tbl precedence_tbl_type;
2320
2321 v_precedence NUMBER;
2322 v_counter NUMBER := 0;
2323
2324 INVALID_INCOMPATIBILITY EXCEPTION;
2325 INVALID_BEST_PRICE EXCEPTION;
2326
2327 BEGIN
2328 l_debug := QP_PREQ_GRP.G_DEBUG_ENGINE;
2329 IF l_debug = FND_API.G_TRUE THEN
2330 QP_PREQ_GRP.engine_debug ('S1');
2331
2332 END IF;
2333
2334 -- [julin/4116856] moved deletion of un-asked-for promotions to QP_PREQ_GRP
2335
2336
2337 /* UPDATE qp_npreq_line_attrs_tmp a
2338 SET a.PRICING_STATUS_CODE = QP_PREQ_GRP.G_STATUS_INCOMP_LOGIC
2339 WHERE a.LIST_LINE_ID IN (SELECT b.CREATED_FROM_LIST_LINE_ID
2340 FROM qp_npreq_ldets_tmp b
2341 WHERE b.PRICING_STATUS_CODE = QP_PREQ_GRP.G_STATUS_INCOMP_LOGIC
2342 AND b.PRICING_PHASE_ID = p_pricing_phase_id
2343 AND b.ASK_FOR_FLAG = QP_PREQ_GRP.G_YES
2344 AND b.LINE_INDEX = p_line_index)
2345 AND a.LINE_INDEX = p_line_index; */
2346
2347
2348 FOR i IN incomp_cur(p_manual_dis_flag)
2349 LOOP
2350 IF l_debug = FND_API.G_TRUE THEN
2351 QP_PREQ_GRP.engine_debug ('#2');
2352
2353 END IF;
2354 l_precedence_tbl.delete;
2355 v_counter := 0;
2356
2357 FOR j IN each_incomp_cur(i.INCOMPATABILITY_GRP_CODE,p_manual_dis_flag)
2358 LOOP
2359 v_precedence := Precedence_For_List_Line(j.CREATED_FROM_LIST_HEADER_ID,j.CREATED_FROM_LIST_LINE_ID,
2360 i.incompatability_grp_code, p_line_index,p_pricing_phase_id);
2361 v_counter := v_counter + 1;
2362 l_precedence_tbl(v_counter).created_from_list_line_id := j.created_from_list_line_id;
2363 l_precedence_tbl(v_counter).incompatability_grp_code := j.incompatability_grp_code;
2364 l_precedence_tbl(v_counter).ask_for_flag := j.ask_for_flag;
2365 l_precedence_tbl(v_counter).original_precedence := v_precedence;
2366 IF (j.ask_for_flag = QP_PREQ_GRP.G_YES) THEN
2367 v_precedence := v_precedence + v_ask_for_constant;
2368 END IF;
2369 l_precedence_tbl(v_counter).product_precedence := v_precedence;
2370 END LOOP;
2371
2372 -- Sort the table
2373 IF (l_precedence_tbl.COUNT > 0 ) THEN
2374 --sort_on_precedence(l_precedence_tbl, l_precedence_tbl.FIRST, l_precedence_tbl.LAST);
2375 sort_on_precedence(l_precedence_tbl);
2376 END IF;
2377
2378 IF (l_precedence_tbl.COUNT > 0) THEN
2379 FOR j IN l_precedence_tbl.FIRST .. l_precedence_tbl.LAST
2380 LOOP
2381 IF l_debug = FND_API.G_TRUE THEN
2382 QP_PREQ_GRP.engine_debug ('#3');
2383
2384 END IF;
2385 -- Store the first list_line_id
2386 IF (v_count = 0) THEN
2387 v_first_list_line_id := l_precedence_tbl(j).CREATED_FROM_LIST_LINE_ID;
2388 IF l_debug = FND_API.G_TRUE THEN
2389 QP_PREQ_GRP.engine_debug('The First List Line Id : ' || v_first_list_line_id);
2390 QP_PREQ_GRP.engine_debug('Pricing Status Code: ' || i.pricing_status_code);
2391 END IF;
2392 v_high_precedence := l_precedence_tbl(j).ORIGINAL_PRECEDENCE;
2393 v_ask_for_flag := l_precedence_tbl(j).ASK_FOR_FLAG;
2394 END IF;
2395 IF l_debug = FND_API.G_TRUE THEN
2396 QP_PREQ_GRP.engine_debug ('#4');
2397
2398 END IF;
2399 -- Incomp_grp_id ='EXCL' has the highest priority(exclusivity)
2400 IF (i.INCOMPATABILITY_GRP_CODE = QP_PREQ_GRP.G_INCOMP_EXCLUSIVE) THEN
2401 IF(v_count = 0) THEN
2402 v_this_is_the_list_line_id := l_precedence_tbl(j).CREATED_FROM_LIST_LINE_ID;
2403 v_excl_list_line_id := l_precedence_tbl(j).CREATED_FROM_LIST_LINE_ID;
2404 --v_others_flag := FALSE;
2405 v_excl_flag := TRUE;
2406 ELSE -- If there are multiple list lines in EXCL incomp
2407 IF (v_others_flag = TRUE) THEN
2408 IF (v_high_precedence = l_precedence_tbl(j).ORIGINAL_PRECEDENCE) THEN
2409 IF ((v_ask_for_flag = QP_PREQ_GRP.G_YES and l_precedence_tbl(j).ASK_FOR_FLAG = QP_PREQ_GRP.G_YES) OR
2410 (v_ask_for_flag = QP_PREQ_GRP.G_NO and l_precedence_tbl(j).ASK_FOR_FLAG = QP_PREQ_GRP.G_NO)) THEN
2411 -- Best Price Evaluation
2412 IF (p_processing_flag = QP_PREQ_GRP.G_DISCOUNT_PROCESSING) THEN
2413 IF l_debug = FND_API.G_TRUE THEN
2414 QP_PREQ_GRP.engine_debug ('Best Price Evaluation');
2415 END IF;
2416 IF (p_list_price IS NOT NULL) THEN
2417 Best_Price_Evaluation(p_list_price,
2418 p_line_index,
2419 p_pricing_phase_id,
2420 i.INCOMPATABILITY_GRP_CODE,
2421 v_high_precedence, -- Added for bug#2661540
2422 p_manual_dis_flag,
2423 x_best_list_line_id,
2424 x_ret_status);
2425 IF l_debug = FND_API.G_TRUE THEN
2426 QP_PREQ_GRP.engine_debug('Successful Best Price Eval');
2427 END IF;
2428 IF x_ret_status in (QP_PREQ_GRP.G_STATUS_BEST_PRICE_EVAL_ERROR,FND_API.G_RET_STS_ERROR) THEN
2429 RAISE INVALID_BEST_PRICE;
2430 END IF;
2431 v_this_is_the_list_line_id := x_best_list_line_id;
2432 v_excl_list_line_id := x_best_list_line_id;
2433 v_others_flag := FALSE;
2434 END IF;
2435 END IF;
2436 ELSIF (v_ask_for_flag=QP_PREQ_GRP.G_NO and l_precedence_tbl(j).ASK_FOR_FLAG = QP_PREQ_GRP.G_YES) THEN
2437 v_this_is_the_list_line_id := l_precedence_tbl(j).CREATED_FROM_LIST_LINE_ID;
2438 v_excl_list_line_id := l_precedence_tbl(j).CREATED_FROM_LIST_LINE_ID;
2439 v_others_flag := FALSE;
2440 END IF;
2441 ELSE
2442 IF (v_ask_for_flag = QP_PREQ_GRP.G_NO and l_precedence_tbl(j).ASK_FOR_FLAG = QP_PREQ_GRP.G_YES) THEN
2443 v_this_is_the_list_line_id := l_precedence_tbl(j).CREATED_FROM_LIST_LINE_ID;
2444 v_excl_list_line_id := l_precedence_tbl(j).CREATED_FROM_LIST_LINE_ID;
2445 v_others_flag := FALSE;
2446 END IF;
2447 END IF;
2448 END IF;
2449 END IF;
2450 END IF;
2451 IF l_debug = FND_API.G_TRUE THEN
2452 QP_PREQ_GRP.engine_debug ('#5');
2453
2454 END IF;
2455 IF (v_others_flag = TRUE and v_excl_flag = FALSE) THEN
2456 IF l_debug = FND_API.G_TRUE THEN
2457 QP_PREQ_GRP.engine_debug('Others_Flag: TRUE');
2458 END IF;
2459 null;
2460 ELSE
2461 IF l_debug = FND_API.G_TRUE THEN
2462 QP_PREQ_GRP.engine_debug('Others_Flag: FALSE');
2463 END IF;
2464 null;
2465 END IF;
2466 IF l_debug = FND_API.G_TRUE THEN
2467 QP_PREQ_GRP.engine_debug ('#6');
2468 QP_PREQ_GRP.engine_debug('Count: ' || v_count);
2469
2470 END IF;
2471 IF (v_others_flag = TRUE) THEN
2472 -- If it is an asked for promo
2473 IF (v_count > 0) THEN -- Do not compare the first time for the first record
2474 IF l_debug = FND_API.G_TRUE THEN
2475 QP_PREQ_GRP.engine_debug ('Precedence1:' || v_high_precedence);
2476 QP_PREQ_GRP.engine_debug ('Precedence2:' || l_precedence_tbl(j).ORIGINAL_PRECEDENCE);
2477 QP_PREQ_GRP.engine_debug ('Ask_For_Flag1:' || v_ask_for_flag);
2478 QP_PREQ_GRP.engine_debug ('Ask_For_FLag2:' || l_precedence_tbl(j).ASK_FOR_FLAG);
2479 QP_PREQ_GRP.engine_debug ('Second List Line Id:' || l_precedence_tbl(j).CREATED_FROM_LIST_LINE_ID);
2480 QP_PREQ_GRP.engine_debug ('#7');
2481 END IF;
2482 IF (v_high_precedence = l_precedence_tbl(j).ORIGINAL_PRECEDENCE) THEN
2483 IF ((v_ask_for_flag = QP_PREQ_GRP.G_YES and l_precedence_tbl(j).ASK_FOR_FLAG = QP_PREQ_GRP.G_YES) OR
2484 (v_ask_for_flag = QP_PREQ_GRP.G_NO and l_precedence_tbl(j).ASK_FOR_FLAG = QP_PREQ_GRP.G_NO)) THEN
2485 -- Best Price Evaluation
2486 IF (p_processing_flag = QP_PREQ_GRP.G_DISCOUNT_PROCESSING) THEN
2487 IF l_debug = FND_API.G_TRUE THEN
2488 QP_PREQ_GRP.engine_debug ('Best Price Evaluation');
2489 END IF;
2490 IF (p_list_price IS NOT NULL) THEN
2491 Best_Price_Evaluation(p_list_price,
2492 p_line_index,
2493 p_pricing_phase_id,
2494 i.INCOMPATABILITY_GRP_CODE,
2495 v_high_precedence, -- Added for bug#2661540
2496 p_manual_dis_flag,
2497 x_best_list_line_id,
2498 x_ret_status);
2499 IF l_debug = FND_API.G_TRUE THEN
2500 QP_PREQ_GRP.engine_debug('Successful Best Price Eval1');
2501 END IF;
2502 IF x_ret_status in (QP_PREQ_GRP.G_STATUS_BEST_PRICE_EVAL_ERROR,FND_API.G_RET_STS_ERROR) THEN
2503 RAISE INVALID_BEST_PRICE;
2504 END IF;
2505 v_this_is_the_list_line_id := x_best_list_line_id;
2506 v_others_flag := FALSE;
2507 END IF;
2508 ELSE -- PRICE_LIST
2509 RAISE INVALID_INCOMPATIBILITY;
2510 END IF;
2511 ELSIF (v_ask_for_flag=QP_PREQ_GRP.G_NO and l_precedence_tbl(j).ASK_FOR_FLAG = QP_PREQ_GRP.G_YES) THEN
2512 v_this_is_the_list_line_id := l_precedence_tbl(j).CREATED_FROM_LIST_LINE_ID;
2513 v_others_flag := FALSE;
2514 END IF;
2515 ELSE
2516 IF (v_ask_for_flag = QP_PREQ_GRP.G_NO and l_precedence_tbl(j).ASK_FOR_FLAG = QP_PREQ_GRP.G_YES) THEN
2517 v_this_is_the_list_line_id := l_precedence_tbl(j).CREATED_FROM_LIST_LINE_ID;
2518 v_others_flag := FALSE;
2519 END IF;
2520 END IF;
2521 END IF;
2522 END IF;
2523 IF l_debug = FND_API.G_TRUE THEN
2524 QP_PREQ_GRP.engine_debug ('#8');
2525 END IF;
2526 v_count := v_count + 1;
2527 END LOOP;
2528 END IF; -- l_precedence_tbl.COUNT > 0
2529
2530 v_count := 0; -- Reinit to 0 for an incomp grp id
2531 v_others_flag := TRUE;
2532 IF (v_this_is_the_list_line_id IS NOT NULL) THEN
2533 v_first_list_line_id := v_this_is_the_list_line_id;
2534 IF l_debug = FND_API.G_TRUE THEN
2535 QP_PREQ_GRP.engine_debug('The List Line Id : ' || v_first_list_line_id);
2536 END IF;
2537 END IF;
2538 v_this_is_the_list_line_id := null; -- Reset
2539
2540 IF l_debug = FND_API.G_TRUE THEN
2541 QP_PREQ_GRP.engine_debug('Before Update ......');
2542 QP_PREQ_GRP.engine_debug('Incomp Grp Code:' || i.INCOMPATABILITY_GRP_CODE);
2543 QP_PREQ_GRP.engine_debug('List Line Id:' || v_first_list_line_id);
2544 QP_PREQ_GRP.engine_debug('Pricing Phase Id:' || p_pricing_phase_id);
2545 QP_PREQ_GRP.engine_debug ('#9');
2546
2547 END IF;
2548 -- Update all the other list lines to status 'I' for each incomp grp
2549 IF (v_excl_flag = FALSE) THEN
2550 Delete_Incompatible_Lines(p_pricing_phase_id,
2551 p_line_index,
2552 i.INCOMPATABILITY_GRP_CODE,
2553 v_first_list_line_id,
2554 v_excl_flag,
2555 p_manual_dis_flag,
2556 x_ret_status);
2557 END IF;
2558 IF l_debug = FND_API.G_TRUE THEN
2559 QP_PREQ_GRP.engine_debug ('#10');
2560 END IF;
2561 END LOOP;
2562 IF (v_excl_flag = TRUE) THEN
2563 Delete_Incompatible_Lines(p_pricing_phase_id,
2564 p_line_index,
2565 NULL, -- incomp_grp_id
2566 v_excl_list_line_id,
2567 v_excl_flag,
2568 p_manual_dis_flag,
2569 x_ret_status);
2570 END IF;
2571 x_return_status := v_return_status; -- SUCCESS
2572 EXCEPTION
2573 WHEN INVALID_INCOMPATIBILITY THEN
2574 IF l_debug = FND_API.G_TRUE THEN
2575 QP_PREQ_GRP.engine_debug(v_routine_name || ' Multiple Price Lists cannot have same precedence
2576 and cannot be asked for');
2577 END IF;
2578 x_return_status_txt := v_routine_name || ' Multiple Price Lists cannot have same precedence
2579 and cannot be asked for';
2580 v_return_status := QP_PREQ_GRP.G_STATUS_INVALID_INCOMP;
2581 x_return_status := v_return_status;
2582 WHEN INVALID_BEST_PRICE THEN
2583 IF l_debug = FND_API.G_TRUE THEN
2584 QP_PREQ_GRP.engine_debug(v_routine_name || 'Best Price Evaluation Has Error');
2585 END IF;
2586 x_return_status_txt := v_routine_name || 'Best Price Evaluation Has Error';
2587 v_return_status := QP_PREQ_GRP.G_STATUS_BEST_PRICE_EVAL_ERROR;
2588 x_return_status := v_return_status;
2589 WHEN OTHERS THEN
2590 IF l_debug = FND_API.G_TRUE THEN
2591 QP_PREQ_GRP.engine_debug(v_routine_name || ' ' || SQLERRM);
2592 END IF;
2593 x_return_status_txt := v_routine_name || ' ' || SQLERRM;
2594 v_return_status := FND_API.G_RET_STS_ERROR;
2595 x_return_status := v_return_status;
2596 END Resolve_Incompatability;
2597
2598 END QP_Resolve_Incompatability_PVT ;
[Home] [Help]