1 PACKAGE BODY QP_Resolve_Incompatability_PVT AS
2 /* $Header: QPXVINCB.pls 120.14.12010000.4 2008/11/07 06:23:13 hmohamme 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 IF l_debug = FND_API.G_TRUE THEN
624 QP_PREQ_GRP.engine_debug('Constract start/end dates '||
625 l_date_passed||
626 'passed in ==> qp_time_uom_conversion = '||
627 l_qp_time_uom_conversion);
628 END IF;
629
630 IF l_debug = FND_API.G_TRUE THEN
631 QP_PREQ_GRP.engine_debug( 'Determine_Pricing_UOM1');
632 QP_PREQ_GRP.engine_debug( I.INCOMPATABILITY_GRP_CODE);
633 QP_PREQ_GRP.engine_debug( 'Line Index:'|| i.line_index);
634
635 END IF;
636 Precedence_For_Price_List_Line(i.line_index,i.line_uom_code,NULL,l_precedence_tbl,l_status,l_status_text);
637
638 IF l_debug = FND_API.G_TRUE THEN
639 QP_PREQ_GRP.engine_debug( ' Debug Point #0');
640
641 QP_PREQ_GRP.engine_debug('Count : ' || l_precedence_tbl.count);
642
643 END IF;
644 -- Sort the table(not needed as it would have only the price list lines with least precedence)
645 /* IF (l_precedence_tbl.COUNT > 0 ) THEN
646 --sort_on_precedence(l_precedence_tbl, l_precedence_tbl.FIRST, l_precedence_tbl.LAST);
647 sort_on_precedence(l_precedence_tbl);
648 END IF; */
649
650 IF (l_precedence_tbl.COUNT > 0 ) THEN
651 v_total_count := l_precedence_tbl.COUNT;
652 v_count := 0;
653 v_list_line_count1 := NULL;
654 IF l_debug = FND_API.G_TRUE THEN
655 QP_PREQ_GRP.engine_debug( 'Precedence Table Count Line : '|| i.line_index ||' Cnt:'|| l_precedence_tbl.count);
656 END IF;
657 FOR j IN l_precedence_tbl.FIRST .. l_precedence_tbl.LAST
658 LOOP
659 IF l_debug = FND_API.G_TRUE THEN
660 QP_PREQ_GRP.engine_debug( 'Determine_Pricing_UOM2');
661 END IF;
662 IF (v_count = 0) THEN
663 v_list_line_id := l_precedence_tbl(j).created_from_list_line_id;
664 v_list_precedence := l_precedence_tbl(j).product_precedence;
665 END IF;
666
667 IF (v_count > 0 ) THEN
668
669 IF l_debug = FND_API.G_TRUE THEN
670 QP_PREQ_GRP.engine_debug( 'Precedence 1 : '|| v_list_precedence);
671 QP_PREQ_GRP.engine_debug( 'Precedence 2 : '|| l_precedence_tbl(j).product_precedence);
672
673 END IF;
674 -- If precedence matches match pricing attributes count
675 IF (v_list_precedence = l_precedence_tbl(j).product_precedence) THEN
676
677 --Reset
678 v_list_line_count2 := NULL;
679
680 IF (v_list_line_count1 IS NULL) THEN
681 OPEN get_attribute_count_cur(i.line_index,v_list_line_id); -- For First Line
682 FETCH get_attribute_count_cur INTO v_list_line_count1;
683 CLOSE get_attribute_count_cur;
684 END IF;
685
686 IF l_debug = FND_API.G_TRUE THEN
687 QP_PREQ_GRP.engine_debug( 'Precedence Matched . Attribute Count1 : ' || v_list_line_id || ' ' || v_list_line_count1);
688
689 END IF;
690 OPEN get_attribute_count_cur(i.line_index,l_precedence_tbl(j).created_from_list_line_id); -- For Second Line
691 FETCH get_attribute_count_cur INTO v_list_line_count2;
692 CLOSE get_attribute_count_cur;
693
694 IF l_debug = FND_API.G_TRUE THEN
695 QP_PREQ_GRP.engine_debug( 'Precedence Matched . Attribute Count2 : ' || l_precedence_tbl(j).created_from_list_line_id || ' ' || v_list_line_count2);
696
697 END IF;
698 -- Update the status to duplicate list lines , only after comparing the attribute counts for all list lines
699 -- That is why the extra condition v_total_cout = v_count+1
700 IF (nvl(v_list_line_count1,0) = nvl(v_list_line_count2,0) and v_total_count = v_count + 1) THEN
701 v_dup_list_line_id := l_precedence_tbl(j).created_from_list_line_id;
702
703 --begin fix bug 2746019
704 --l_status_text := v_list_line_id || ',' || v_dup_list_line_id; --fix bug 2746019
705 select name into v_price_list from qp_list_headers_vl where
706 list_header_id = (select list_header_id from qp_list_lines
707 where list_line_id = v_list_line_id);
708
709 select name into v_dup_price_list from qp_list_headers_vl where
710 list_header_id = (select list_header_id from qp_list_lines
711 where list_line_id = v_dup_list_line_id);
712
713 FND_MESSAGE.SET_NAME('QP','QP_DUPLICATE_PRICE_LIST');
714 FND_MESSAGE.SET_TOKEN('LIST_LINE_ID', v_price_list);
715 FND_MESSAGE.SET_TOKEN('DUP_LIST_LINE_ID', v_dup_price_list);
716 l_status_text:= FND_MESSAGE.GET;
717 -- end fix bug 2746019
718
719 Update_Invalid_List_Lines(i.INCOMPATABILITY_GRP_CODE,i.line_index,p_pricing_phase_id,
720 QP_PREQ_GRP.G_STATUS_DUP_PRICE_LIST,l_status_text, v_return_status,
721 v_return_status_text);
722 GOTO NEXT_LINE;
723
724 --RAISE DUPLICATE_PRICE_LIST;
725 ELSE
726 IF (nvl(v_list_line_count1,0) < nvl(v_list_line_count2,0)) THEN
727 v_list_line_id := l_precedence_tbl(j).created_from_list_line_id; -- Second Line should be given
728 v_list_line_count1 := v_list_line_count2;
729 END IF;
730 END IF;
731 END IF;
732 END IF;
733 v_count := v_count + 1;
734 END LOOP;
735 END IF;
736
737 IF l_debug = FND_API.G_TRUE THEN
738 QP_PREQ_GRP.engine_debug('List Line Id:' || v_list_line_id);
739
740 END IF;
741 IF (v_list_line_id IS NOT NULL) THEN -- Successful in finding pll in order uom
742 -- Update all the other list lines to status 'U'
743 /*
744 INDX,QP_Resolve_Incompatability_PVTRUN.determine_pricing_uom_and_qty.upd1,qp_npreq_ldets_tmp_N2,PRICING_PHASE_ID,1
745 INDX,QP_Resolve_Incompatability_PVTRUN.determine_pricing_uom_and_qty.upd1,qp_npreq_ldets_tmp_N2,PRICING_STATUS_CODE,2
746 INDX,QP_Resolve_Incompatability_PVTRUN.determine_pricing_uom_and_qty.upd1,qp_npreq_lines_tmp_N2,LINE_INDEX,3
747 */
748 UPDATE qp_npreq_ldets_tmp -- upd1
749 SET PRICING_STATUS_CODE = QP_PREQ_GRP.G_STATUS_UOM_FAILURE
750 WHERE INCOMPATABILITY_GRP_CODE = i.INCOMPATABILITY_GRP_CODE
751 AND LINE_INDEX = i.line_index
752 AND PRICING_PHASE_ID = p_pricing_phase_id
753 AND CREATED_FROM_LIST_LINE_ID <> v_list_line_id
754 AND PRICING_STATUS_CODE = QP_PREQ_GRP.G_STATUS_NEW;
755
756 /* UPDATE qp_npreq_line_attrs_tmp a
757 SET a.PRICING_STATUS_CODE = QP_PREQ_GRP.G_STATUS_UOM_FAILURE
758 WHERE a.LIST_LINE_ID
759 IN (SELECT b.CREATED_FROM_LIST_LINE_ID
760 FROM qp_npreq_ldets_tmp b
761 WHERE b.PRICING_STATUS_CODE = QP_PREQ_GRP.G_STATUS_UOM_FAILURE
762 AND b.PRICING_PHASE_ID = p_pricing_phase_id
763 AND b.INCOMPATABILITY_GRP_CODE = i.INCOMPATABILITY_GRP_CODE
764 AND b.CREATED_FROM_LIST_LINE_ID <> v_list_line_id
765 AND b.LINE_INDEX = i.line_index)
766 AND a.LINE_INDEX = i.line_index; */
767
768 -- begin shu, fix bug 2453250, no price_list_header_id after big search
769 /* Commented for bug#2882115
770 UPDATE qp_npreq_lines_tmp a
771 SET a.price_list_header_id = (SELECT list_header_id from qp_list_lines where list_line_id = v_list_line_id )
772 WHERE ( a.price_list_header_id < 0 OR a.price_list_header_id IS NULL)
773 AND a.line_index = i.line_index;
774 */
775 -- end shu fix bug 2453250
776
777 -- begin shulin bug 1829731
778 -- use OKS API to calculate pricing_qty based on order_uom if profile set to 'ORACLE_CONTRACTS'
779 IF l_debug = FND_API.G_TRUE THEN
780 QP_PREQ_GRP.engine_debug('no uom conversion ...');
781 END IF;
782 IF l_qp_time_uom_conversion = 'ORACLE_CONTRACTS' THEN
783
784 IF l_debug = FND_API.G_TRUE THEN
785 QP_PREQ_GRP.engine_debug( 'contract_start_date :' || i.contract_start_date);
786 QP_PREQ_GRP.engine_debug( 'contract_end_date :' || i.contract_end_date);
787 QP_PREQ_GRP.engine_debug( 'line_uom_code/order uom :' || i.line_uom_code); -- order_uom
788 END IF;
789
790 --IF i.contract_start_date IS NOT NULL AND i.contract_end_date IS NOT NULL THEN
791 -- OKS_TIME_MEASURES_PUB replaced with OKS_OMINT_PUB in R12
792 /* [julin/4285975/4662388] removing dynamic SQL, unnecessary and causes error
793 * when env language has ',' delimiter
794 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';
795 IF l_debug = FND_API.G_TRUE THEN
796 QP_PREQ_GRP.engine_debug('l_sql_stmt:' || l_sql_stmt);
797 END IF; -- end debug
798 execute immediate l_sql_stmt INTO l_oks_qty;
799 */
800 --END IF;
801 --bug 4900095
802 IF l_duration_passed = 'N' THEN --call OKS API for OKS calls where uom_quantity is passed as null
803 -- 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);
804 --bug 4900095
805 l_oks_qty := nvl(round(OKS_OMINT_PUB.get_target_duration( p_start_date => i.contract_start_date,
806 p_end_date => i.contract_end_date,
807 p_source_uom => i.line_uom_code,
808 p_source_duration => i.uom_quantity,
809 p_target_uom => i.line_uom_code,/*Default Month*/
810 p_org_id => QP_PREQ_GRP.G_CURRENT_USER_OP_UNIT), l_max_decimal_digits), -999999);
811 ELSE
812 l_oks_qty := null;
813 END IF;--l_duration_passed = 'N'
814
815 IF l_debug = FND_API.G_TRUE THEN
816 QP_PREQ_GRP.engine_debug('l_oks_qty:' || l_oks_qty);
817 END IF; -- end debug
818 -- when contract_start_date or contract_end_date is null, or uom is 'MO' instead of 'MTH', l_oks_qty = 0
819 -- when uom is not time_related or invalid to oks API, i.e. 'EA', 'MI', l_oks_qty = NULL
820
821 IF (l_oks_qty IS NOT NULL AND l_oks_qty <> -999999)
822 --bug 4900095
823 and l_duration_passed = 'N' THEN -- oks succeed
824 v_pricing_qty := round(l_oks_qty , l_max_decimal_digits);
825 l_order_qty := v_pricing_qty; -- shu_latest, this is to correct if user enter order_qty not matching the start_date end_date
826 l_uom_quantity :=1;
827 IF l_debug = FND_API.G_TRUE THEN
828 QP_PREQ_GRP.engine_debug('Pric_Qty OKS_API Conv based on Order_UOM :' || v_pricing_qty);
829 END IF; --end debug
830
831 ELSE -- oks fail, make v_pricing_qty as line_quantity just like STANDARD conversion
832 v_pricing_qty := i.line_quantity; -- STANDARD
833 --l_uom_quantity :=1;
834 l_uom_quantity := nvl(i.uom_quantity, 1); -- to back support OM, SL BUG FOUND
835 -- no need to update l_order_qty, since it has been initialized to i.line_quantity
836 IF l_debug = FND_API.G_TRUE THEN
837 QP_PREQ_GRP.engine_debug('pric_qty same as order_qty :' || v_pricing_qty); --shu 12/26/2001
838 QP_PREQ_GRP.engine_debug('uom_qty passed :' || l_uom_quantity); --shu 12/26/2001
839 END IF; -- end debug
840
841 END IF; -- END IF (l_oks_qty IS NOT NULL AND l_oks_qty <> 0)
842 ELSE -- l_qp_time_uom_conversion = 'STANDARD' THEN
843 v_pricing_qty := i.line_quantity; -- STANDARD
844 -- no need to update l_order_qty, since it has been initialized to i.line_quantity
845 l_uom_quantity := nvl(i.uom_quantity, 1); -- to back support OM, for case user order 2 of 6 MTH service
846 IF l_debug = FND_API.G_TRUE THEN
847 QP_PREQ_GRP.engine_debug('pric_qty same as order_qty :' || v_pricing_qty);
848 END IF;
849 END IF;
850 -- end shulin bug 1829731
851
852 -- The followings are used to bulk update at the end of the procedure for no uom conversion cases
853 l_line_quantity_tbl(m) := l_order_qty; -- shu_latest
854 l_line_index_tbl(m) := i.line_index;
855 l_line_uom_code_tbl(m) := i.line_uom_code;
856 l_priced_quantity_tbl(m) := v_pricing_qty; --shu_latest
857 l_uom_quantity_tbl(m) := l_uom_quantity; --shu_latest
858 m := m+1;
859
860 /* no need if bulk update later
861 UPDATE qp_npreq_lines_tmp
862 SET PRICED_UOM_CODE = i.line_uom_code,
863 PRICED_QUANTITY = v_pricing_qty, -- shulin bug 1829731
864 LINE_UOM_CODE = i.line_uom_code, -- order_uom
865 LINE_QUANTITY = l_order_qty, -- shu_latest
866 UOM_QUANTITY =1
867 WHERE LINE_INDEX = i.line_index;
868 */
869
870 x_list_line_id := v_list_line_id;
871 ELSE -- uom conversion cases
872
873 Precedence_For_Price_List_Line(i.line_index,NULL,'Y',l_precedence_tbl,l_status,l_status_text);
874
875 -- Sort the table(not needed as it would have only the price list lines with least precedence)
876 /* IF (l_precedence_tbl.COUNT > 0) THEN
877 --sort_on_precedence(l_precedence_tbl, l_precedence_tbl.FIRST, l_precedence_tbl.LAST);
878 sort_on_precedence(l_precedence_tbl);
879 END IF; */
880
881 /* IF (l_precedence_tbl.COUNT > 0) THEN
882 FOR j IN l_precedence_tbl.FIRST .. l_precedence_tbl.LAST
883 LOOP
884 IF l_debug = FND_API.G_TRUE THEN
885 QP_PREQ_GRP.engine_debug('List Line Id : ' || l_precedence_tbl(j).created_from_list_line_id);
886 QP_PREQ_GRP.engine_debug('Precedence : ' || l_precedence_tbl(j).product_precedence);
887 QP_PREQ_GRP.engine_debug('Inventory Item Id: ' || l_precedence_tbl(j).inventory_item_id);
888 QP_PREQ_GRP.engine_debug('Product Uom : ' || l_precedence_tbl(j).product_uom_code);
889 END IF;
890 END LOOP;
891 END IF; */
892
893 IF (l_precedence_tbl.COUNT > 0) THEN
894 v_total_count := l_precedence_tbl.COUNT;
895 v_count := 0;
896 v_list_line_count1 := NULL;
897 FOR j IN l_precedence_tbl.FIRST .. l_precedence_tbl.LAST
898 LOOP
899 IF l_debug = FND_API.G_TRUE THEN
900 QP_PREQ_GRP.engine_debug('pri flag');
901 END IF;
902 IF (v_count = 0) THEN
903 v_list_line_id := l_precedence_tbl(j).created_from_list_line_id;
904 v_list_precedence := l_precedence_tbl(j).product_precedence;
905 v_primary_uom_code := l_precedence_tbl(j).product_uom_code;
906 v_inventory_item_id := to_number(l_precedence_tbl(j).inventory_item_id);
907 END IF;
908
909 IF (v_count > 0 ) THEN
910 IF (v_list_precedence = l_precedence_tbl(j).product_precedence) THEN
911
912 --Reset
913 v_list_line_count2 := NULL;
914
915 IF (v_list_line_count1 IS NULL) THEN
916 OPEN get_attribute_count_cur(i.line_index,v_list_line_id); -- For First Line
917 FETCH get_attribute_count_cur INTO v_list_line_count1;
918 CLOSE get_attribute_count_cur;
919 END IF;
920
921 IF l_debug = FND_API.G_TRUE THEN
922 QP_PREQ_GRP.engine_debug( 'Precedence Matched . Attribute Count Pri1 : ' || v_list_line_id || ' ' || v_list_line_count1);
923
924 END IF;
925 OPEN get_attribute_count_cur(i.line_index,l_precedence_tbl(j).created_from_list_line_id); -- For Second Line
926 FETCH get_attribute_count_cur INTO v_list_line_count2;
927 CLOSE get_attribute_count_cur;
928
929 IF l_debug = FND_API.G_TRUE THEN
930 QP_PREQ_GRP.engine_debug( 'Precedence Matched . Attribute Count Pri2 : ' || l_precedence_tbl(j).created_from_list_line_id || ' ' || v_list_line_count2);
931
932 END IF;
933 IF (nvl(v_list_line_count1,0) = nvl(v_list_line_count2,0) and v_total_count = v_count + 1) THEN
934
935 v_dup_list_line_id := l_precedence_tbl(j).created_from_list_line_id;
936
937 --begin fix bug 2746019
938 --l_status_text := v_list_line_id || ',' || v_dup_list_line_id; --fix bug 2746019
939 select name into v_price_list from qp_list_headers_vl where
940 list_header_id = (select list_header_id from qp_list_lines
941 where list_line_id = v_list_line_id);
942
943 select name into v_dup_price_list from qp_list_headers_vl where
944 list_header_id = (select list_header_id from qp_list_lines
945 where list_line_id = v_dup_list_line_id);
946
947 FND_MESSAGE.SET_NAME('QP','QP_DUPLICATE_PRICE_LIST');
948 FND_MESSAGE.SET_TOKEN('LIST_LINE_ID', v_price_list);
949 FND_MESSAGE.SET_TOKEN('DUP_LIST_LINE_ID', v_dup_price_list);
950 l_status_text:= FND_MESSAGE.GET;
951 -- end fix bug 2746019
952
953 Update_Invalid_List_Lines(i.INCOMPATABILITY_GRP_CODE,i.line_index,p_pricing_phase_id,
954 QP_PREQ_GRP.G_STATUS_DUP_PRICE_LIST,l_status_text, v_return_status,
955 v_return_status_text);
956 GOTO NEXT_LINE;
957 --RAISE DUPLICATE_PRICE_LIST;
958 ELSE
959 IF (nvl(v_list_line_count1,0) < nvl(v_list_line_count2,0)) THEN
960 v_list_line_id := l_precedence_tbl(j).created_from_list_line_id; -- Second Line should be given
961 v_list_line_count1 := v_list_line_count2;
962 END IF;
963 END IF;
964 END IF;
965 END IF;
966 v_count := v_count + 1;
967 END LOOP;
968 END IF; -- v_list_line_id IS NOT NULL
969
970 IF l_debug = FND_API.G_TRUE THEN
971 QP_PREQ_GRP.engine_debug('Pri Flag List Line Id:' || v_list_line_id);
972
973 END IF;
974 IF (v_list_line_id IS NOT NULL) THEN
975 /*
976 INDX,QP_Resolve_Incompatability_PVTRUN.determine_pricing_uom_and_qty.upd2,qp_npreq_ldets_tmp_N2,PRICING_PHASE_ID,1
977 INDX,QP_Resolve_Incompatability_PVTRUN.determine_pricing_uom_and_qty.upd2,qp_npreq_ldets_tmp_N2,PRICING_STATUS_CODE,2
978 INDX,QP_Resolve_Incompatability_PVTRUN.determine_pricing_uom_and_qty.upd2,qp_npreq_lines_tmp_N2,LINE_INDEX,3
979 */
980 -- Update all the other list lines to status 'P'
981 UPDATE qp_npreq_ldets_tmp -- upd2
982 SET PRICING_STATUS_CODE = QP_PREQ_GRP.G_STATUS_PRIMARY_UOM_FLAG
983 WHERE INCOMPATABILITY_GRP_CODE = i.INCOMPATABILITY_GRP_CODE
984 AND LINE_INDEX = i.line_index
985 AND PRICING_PHASE_ID = p_pricing_phase_id
986 AND CREATED_FROM_LIST_LINE_ID <> v_list_line_id
987 AND PRICING_STATUS_CODE = QP_PREQ_GRP.G_STATUS_NEW;
988
989
990 /* UPDATE qp_npreq_line_attrs_tmp a
991 SET a.PRICING_STATUS_CODE = QP_PREQ_GRP.G_STATUS_PRIMARY_UOM_FLAG
992 WHERE a.LIST_LINE_ID IN (SELECT b.CREATED_FROM_LIST_LINE_ID
993 FROM qp_npreq_ldets_tmp b
994 WHERE b.PRICING_STATUS_CODE = QP_PREQ_GRP.G_STATUS_PRIMARY_UOM_FLAG
995 AND b.CREATED_FROM_LIST_LINE_ID <> v_list_line_id
996 AND b.LINE_INDEX = i.line_index)
997 AND a.LINE_INDEX = i.line_index; */
998
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
1002 IN (SELECT b.CREATED_FROM_LIST_LINE_ID
1003 FROM qp_npreq_ldets_tmp b
1004 WHERE b.PRICING_STATUS_CODE = QP_PREQ_GRP.G_STATUS_PRIMARY_UOM_FLAG
1005 AND b.PRICING_PHASE_ID = p_pricing_phase_id
1006 AND b.INCOMPATABILITY_GRP_CODE = i.INCOMPATABILITY_GRP_CODE
1007 AND b.CREATED_FROM_LIST_LINE_ID <> v_list_line_id
1008 AND b.LINE_INDEX = i.line_index)
1009 AND a.LINE_INDEX = i.line_index; */
1010
1011 -- begin shu, fix bug 2453250, no price_list_header_id after big search
1012 /* Commented for bug#2882115
1013 UPDATE qp_npreq_lines_tmp a
1014 SET a.price_list_header_id = (SELECT list_header_id from qp_list_lines where list_line_id = v_list_line_id )
1015 WHERE ( a.price_list_header_id < 0 OR a.price_list_header_id IS NULL)
1016 AND a.line_index = i.line_index;
1017 */
1018 -- end shu fix bug 2453250
1019
1020 IF l_debug = FND_API.G_TRUE THEN
1021 QP_PREQ_GRP.engine_debug('Data Updated');
1022 QP_PREQ_GRP.engine_debug('Order Uom:' || i.line_uom_code);
1023 QP_PREQ_GRP.engine_debug('Prim UOM:' || v_primary_uom_code);
1024 QP_PREQ_GRP.engine_debug('Inventory Id:' || v_inventory_item_id);
1025 QP_PREQ_GRP.engine_debug('v_pricing_qty:' || v_pricing_qty);
1026 END IF;
1027
1028 -- begin Bug 1829731 fix, shulin
1029 IF l_debug = FND_API.G_TRUE THEN
1030 QP_PREQ_GRP.engine_debug('uom conversion...');
1031 END IF;
1032 l_uom_quantity := i.uom_quantity; --shu_latest, to back support OM, either from usr input or null
1033 -- shulin, do OKS conversion if profile set to 'ORCALE_CONTRACTS'
1034 IF (l_qp_time_uom_conversion = 'ORACLE_CONTRACTS') THEN -- do oks conversion
1035
1036 IF l_debug = FND_API.G_TRUE THEN
1037 QP_PREQ_GRP.engine_debug( 'contract_start_date :' || i.contract_start_date);
1038 QP_PREQ_GRP.engine_debug( 'contract_end_date :' || i.contract_end_date);
1039 QP_PREQ_GRP.engine_debug( 'primary_uom_code :' || v_primary_uom_code);
1040 QP_PREQ_GRP.engine_debug('v_pricing_qty:' || v_pricing_qty);
1041 END IF;
1042 -- when contract_start_date or contract_end_date is null, or uom is 'MO' instead of 'MTH', l_oks_qty = 0
1043 -- when uom is not time_related or invalid to oks API, i.e. 'EA', 'MI', l_oks_qty = NULL
1044
1045 --IF i.contract_start_date IS NOT NULL AND i.contract_end_date IS NOT NULL THEN
1046 -- OKS_TIME_MEASURES_PUB replaced with OKS_OMINT_PUB in R12
1047 /* [julin/4285975/4662388] removing dynamic SQL, unnecessary and causes error
1048 * when env language has ',' delimiter
1049 l_sql_stmt := 'select ' || ROUND (OKS_OMINT_PUB.get_quantity (i.contract_start_date,
1050 i.contract_end_date, v_primary_uom_code, QP_PREQ_GRP.G_CURRENT_USER_OP_UNIT), l_max_decimal_digits) ||' from dual';
1051 execute immediate l_sql_stmt INTO l_oks_qty; -- shulin
1052 */
1053 --END IF;
1054 -- 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);
1055 --bug 4900095
1056 l_oks_qty := nvl(round(OKS_OMINT_PUB.get_target_duration( p_start_date => i.contract_start_date,
1057 p_end_date => i.contract_end_date,
1058 p_source_uom => i.line_uom_code,
1059 p_source_duration => i.uom_quantity,
1060 p_target_uom => v_primary_uom_code,/*Default Month*/
1061 p_org_id => QP_PREQ_GRP.G_CURRENT_USER_OP_UNIT), l_max_decimal_digits), -999999);
1062
1063 IF l_debug = FND_API.G_TRUE THEN
1064 QP_PREQ_GRP.engine_debug('l_oks_qty1:' || l_oks_qty);
1065 END IF; -- end debug
1066
1067 IF (l_oks_qty IS NOT NULL OR l_oks_qty <> 0)
1068 --bug 4900095
1069 and l_duration_passed = 'N' THEN -- oks succeed
1070
1071 -- order_qty is based on odr_uom
1072 /* [julin/4285975/4662388] removing dynamic SQL, unnecessary and causes error
1073 * when env language has ',' delimiter
1074 l_sql_stmt := 'select ' || ROUND (OKS_OMINT_PUB.get_quantity (i.contract_start_date,
1075 i.contract_end_date, i.line_uom_code, QP_PREQ_GRP.G_CURRENT_USER_OP_UNIT), l_max_decimal_digits) ||' from dual';
1076 execute immediate l_sql_stmt INTO l_order_qty; -- shu_latest
1077 */
1078 -- 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);
1079 --bug 4900095
1080 l_order_qty := nvl(round(OKS_OMINT_PUB.get_target_duration( p_start_date => i.contract_start_date,
1081 p_end_date => i.contract_end_date,
1082 p_source_uom => i.line_uom_code,
1083 p_source_duration => i.uom_quantity,
1084 p_target_uom => i.line_uom_code,/*Default Month*/
1085 p_org_id => QP_PREQ_GRP.G_CURRENT_USER_OP_UNIT), l_max_decimal_digits), -999999);
1086 IF l_debug = FND_API.G_TRUE THEN
1087 QP_PREQ_GRP.engine_debug('l_oks_qty2:' || l_oks_qty);
1088 END IF; -- end debug
1089
1090 v_pricing_qty := round (l_oks_qty , l_max_decimal_digits);
1091
1092 l_uom_quantity := 1; --shulin
1093 IF l_debug = FND_API.G_TRUE THEN
1094 QP_PREQ_GRP.engine_debug('OKS_API conversion...');
1095 QP_PREQ_GRP.engine_debug('pricing_qty:' || v_pricing_qty);
1096 QP_PREQ_GRP.engine_debug('pricing_uom:' || v_primary_uom_code);
1097 QP_PREQ_GRP.engine_debug('order_qty:' || l_order_qty);
1098 QP_PREQ_GRP.engine_debug('order_uom:' || l_order_uom_code);
1099 QP_PREQ_GRP.engine_debug('uom_quantity:' || l_uom_quantity);
1100 END IF;
1101
1102 ELSIF (l_oks_qty <> -999999 AND l_oks_qty <> 0) -- OM/ASO/IBE call for service line duration/uom_quantity is not null
1103 --bug 4900095
1104 and l_duration_passed = 'Y' THEN
1105 l_uom_quantity := l_oks_qty;
1106 v_pricing_qty := ROUND( i.line_quantity * l_oks_qty, l_max_decimal_digits) ; -- nvl is case we only have line_quantity
1107 --changed for service pricing
1108 --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
1109 --l_uom_quantity := ROUND( i.uom_quantity * v_uom_rate, l_max_decimal_digits); -- do not nvl uom_quantity
1110 l_uom_quantity :=1; -- reset to 1, so the unit price by pricing uom does not change
1111 l_order_qty := i.line_quantity;
1112 IF l_debug = FND_API.G_TRUE THEN
1113 QP_PREQ_GRP.engine_debug('OKS_API conversion for OM...');
1114 QP_PREQ_GRP.engine_debug('pricing_qty:' || v_pricing_qty);
1115 QP_PREQ_GRP.engine_debug('pricing_uom:' || v_primary_uom_code);
1116 QP_PREQ_GRP.engine_debug('order_qty:' || l_order_qty);
1117 QP_PREQ_GRP.engine_debug('order_uom:' || l_order_uom_code);
1118 QP_PREQ_GRP.engine_debug('uom_quantity:' || l_uom_quantity);
1119 END IF;
1120 END IF; --oks succeed
1121 END IF; -- end profile is ORACLE_CONTRACTS
1122
1123 -- not 'ORACLE_CONTACTS' or OKS conversion failed, use standard inventory uom conversion
1124 IF (l_qp_time_uom_conversion = 'STANDARD') OR (l_oks_qty = -999999 OR l_oks_qty = 0) THEN
1125
1126 IF (l_qp_time_uom_conversion = 'ORACLE_CONTRACTS') then
1127 IF l_debug = FND_API.G_TRUE THEN
1128 QP_PREQ_GRP.engine_debug('oks conversion had failed...');
1129 END IF;
1130 END IF; -- for debug, to distinglish if it is oks failed case or if profile set to standard case
1131
1132 -- Get the conversion rate based on prclist's primary_uom
1133 Inv_convert.inv_um_conversion(i.line_uom_code,
1134 v_primary_uom_code,
1135 v_inventory_item_id,
1136 v_uom_rate);
1137 IF (v_uom_rate = -99999) THEN
1138 l_status_text := 'invalid conversion rate from ' || l_order_uom_code || ' to ' || v_primary_uom_code;
1139 Update_Invalid_List_Lines(i.INCOMPATABILITY_GRP_CODE,i.line_index,p_pricing_phase_id,
1140 QP_PREQ_GRP.G_STATUS_INVALID_UOM_CONV,l_status_text, v_return_status,
1141 v_return_status_text);
1142 -- [julin/4330147/4335995]
1143 l_uom_conv_success := 'N';
1144 --RAISE INVALID_UOM_CONVERSION;
1145 ELSE
1146 IF (i.PROCESSING_ORDER <> 2 or i.PROCESSING_ORDER IS NULL ) THEN -- It is not a service line
1147
1148 v_pricing_qty := ROUND( i.line_quantity * v_uom_rate, l_max_decimal_digits);
1149 l_uom_quantity := 1; --shu_latest
1150 l_order_qty := i.line_quantity;
1151 IF l_debug = FND_API.G_TRUE THEN
1152 QP_PREQ_GRP.engine_debug('Standard uom conversion, non-service line...');
1153 QP_PREQ_GRP.engine_debug('pricing_qty:' || v_pricing_qty);
1154 QP_PREQ_GRP.engine_debug('pricing_uom:' || v_primary_uom_code);
1155 QP_PREQ_GRP.engine_debug('order_qty:' || l_order_qty);
1156 QP_PREQ_GRP.engine_debug('order_uom:' || l_order_uom_code);
1157 QP_PREQ_GRP.engine_debug('uom_quantity:' || l_uom_quantity);
1158 END IF;
1159
1160 ELSE -- service line, l_uom_quantity matters
1161
1162 IF (l_qp_time_uom_conversion = 'ORACLE_CONTRACTS') THEN -- from oks failed case
1163 v_pricing_qty := ROUND( i.line_quantity * v_uom_rate, l_max_decimal_digits);
1164 l_uom_quantity := 1;
1165 l_order_qty := i.line_quantity;
1166 IF l_debug = FND_API.G_TRUE THEN
1167 QP_PREQ_GRP.engine_debug('Standard uom conversion (oks failed), service line...');
1168 QP_PREQ_GRP.engine_debug('pricing_qty:' || v_pricing_qty);
1169 QP_PREQ_GRP.engine_debug('pricing_uom:' || v_primary_uom_code);
1170 QP_PREQ_GRP.engine_debug('order_qty:' || l_order_qty);
1171 QP_PREQ_GRP.engine_debug('order_uom:' || l_order_uom_code);
1172 QP_PREQ_GRP.engine_debug('uom_quantity:' || l_uom_quantity);
1173 END IF;
1174
1175 ELSE -- from profile being standard case, l_uom_quantity matters for OM
1176 -- assuming OM pass l_uom_quantity is converted to order uom like the following
1177 -- 1 of 2 YR services, order_uom=YR, l_uom_quantity = 2 (YR)
1178 IF l_debug = FND_API.G_TRUE THEN
1179 QP_PREQ_GRP.engine_debug('uom_quantity from calling application:' || i.uom_quantity);
1180 END IF;
1181 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
1182 --l_uom_quantity := ROUND( i.uom_quantity * v_uom_rate, l_max_decimal_digits); -- do not nvl uom_quantity
1183 l_uom_quantity :=1; -- reset to 1, so the unit price by pricing uom does not change
1184 l_order_qty := i.line_quantity;
1185
1186 IF l_debug = FND_API.G_TRUE THEN
1187 QP_PREQ_GRP.engine_debug('Standard uom conversion, service line...');
1188 QP_PREQ_GRP.engine_debug('pricing_qty:' || v_pricing_qty);
1189 QP_PREQ_GRP.engine_debug('pricing_uom:' || v_primary_uom_code);
1190 QP_PREQ_GRP.engine_debug('order_qty:' || l_order_qty);
1191 QP_PREQ_GRP.engine_debug('order_uom:' || l_order_uom_code);
1192 END IF;
1193
1194 END IF;
1195
1196 END IF; -- end if service or non-service line
1197
1198 END IF; -- end if v_uom_rate
1199
1200 END IF; -- end profile is 'STANDARD'
1201
1202
1203 /*
1204 INDX,QP_Resolve_Incompatability_PVTRUN.determine_pricing_uom_and_qty.upd1,qp_npreq_lines_tmp_N1,LINE_INDEX,1
1205 */
1206 -- do not nvl UOM_QUANTITY when updating UOM_QUANTITY , for a case if uom_quantity is null for a regular line , we could
1207 -- potentially update it to value by nvl'ing it , when uom_quantity is supposed to be null.
1208 -- Ex: 1Ton = 1000Kg , UOM_QUANTITY passed is null . in this case if we nvl(UOM_QUANTITY,1) then the new uom_quantity after the
1209 -- update will be nvl(uom_quantity,1) * 1000(v_uom_rate) = 1000 which is wrong .. bug# 2028618
1210
1211 UPDATE qp_npreq_lines_tmp -- upd1
1212 SET PRICED_UOM_CODE = v_primary_uom_code,
1213 PRICED_QUANTITY = v_pricing_qty, -- shulin bug 1829731
1214 LINE_UOM_CODE = l_order_uom_code, -- order_uom, i.line_uom_code
1215 LINE_QUANTITY = l_order_qty, -- shu_latest
1216 UOM_QUANTITY = decode(l_duration_passed,
1217 'Y', l_uom_quantity,
1218 'N', uom_quantity,
1219 uom_quantity)
1220 WHERE LINE_INDEX = i.line_index;
1221 x_list_line_id := v_list_line_id;
1222
1223 -- [julin/4330147/4335995] only update line_attrs when uom conversion successful
1224 IF (l_uom_conv_success = 'Y') THEN
1225 -- 3773652
1226 -- used for bulk update for UOM conversion cases
1227 l_upd_line_index_tbl(n) := i.line_index;
1228 l_upd_priced_qty_tbl(n) := v_pricing_qty;
1229 n := n+1;
1230 END IF;
1231 ELSE -- No record found in primary uom and also in order uom, else IF (v_list_line_id IS NOT NULL)
1232 l_status_text := 'Could not find a price list in Ordered UOM or Primary UOM';
1233 IF l_debug = FND_API.G_TRUE THEN
1234 QP_PREQ_GRP.engine_debug(l_status_text);
1235 END IF;
1236 Update_Invalid_List_Lines(i.INCOMPATABILITY_GRP_CODE,i.line_index,p_pricing_phase_id,
1237 QP_PREQ_GRP.G_STATUS_INVALID_UOM,l_status_text, v_return_status,
1238 v_return_status_text);
1239 GOTO NEXT_LINE;
1240 -- RAISE INVALID_UOM;
1241 END IF;
1242 END IF;
1243
1244 <<NEXT_LINE>>
1245 null;
1246 END LOOP;
1247
1248
1249 /*
1250 INDX,QP_Resolve_Incompatability_PVTRUN.determine_pricing_uom_and_qty.upd2,qp_npreq_lines_tmp_N1,LINE_INDEX,1
1251 */
1252
1253 -- Bulk Update, from no uom conversion cases
1254 FORALL i in 1 .. l_line_index_tbl.COUNT --upd2
1255 UPDATE qp_npreq_lines_tmp
1256 SET PRICED_UOM_CODE = l_line_uom_code_tbl(i), --priced uom is the same as order uom
1257 PRICED_QUANTITY = l_priced_quantity_tbl(i), -- shu_latest
1258 LINE_UOM_CODE = l_line_uom_code_tbl(i), -- order uom
1259 LINE_QUANTITY = l_line_quantity_tbl(i), -- shu_latest
1260 UOM_QUANTITY = l_uom_quantity_tbl(i) -- shu_latest
1261 WHERE LINE_INDEX = l_line_index_tbl(i);
1262
1263 l_line_uom_code_tbl := l_line_uom_code_tbl_m;
1264 l_line_quantity_tbl := l_line_quantity_tbl_m;
1265 l_line_index_tbl := l_line_index_tbl_m;
1266 l_priced_quantity_tbl := l_priced_quantity_tbl_m; --shu_latest
1267 l_uom_quantity_tbl := l_uom_quantity_tbl_m; --shu_latest
1268
1269 -- 3773652
1270 -- bulk update, from uom conversion cases
1271 FORALL i in 1..l_upd_line_index_tbl.COUNT
1272 UPDATE qp_npreq_line_attrs_tmp
1273 SET VALUE_FROM = qp_number.number_to_canonical(l_upd_priced_qty_tbl(i))
1274 WHERE LINE_INDEX = l_upd_line_index_tbl(i)
1275 AND CONTEXT = QP_PREQ_GRP.G_PRIC_VOLUME_CONTEXT
1276 AND ATTRIBUTE = QP_PREQ_GRP.G_QUANTITY_ATTRIBUTE
1277 AND ATTRIBUTE_TYPE = QP_PREQ_GRP.G_PRICING_TYPE
1278 AND PRICING_STATUS_CODE = QP_PREQ_GRP.G_STATUS_UNCHANGED;
1279 l_upd_line_index_tbl := l_line_index_tbl_m;
1280 l_upd_priced_qty_tbl := l_priced_quantity_tbl_m;
1281
1282 IF l_debug = FND_API.G_TRUE THEN
1283 QP_PREQ_GRP.engine_debug('Incomp Return Status : ' || v_return_status);
1284 END IF;
1285 v_return_status := x_return_status;
1286 EXCEPTION
1287 WHEN INVALID_UOM_CONVERSION THEN
1288 IF l_debug = FND_API.G_TRUE THEN
1289 QP_PREQ_GRP.engine_debug(v_routine_name ||' Invalid Unit of Measure Conversion'|| l_order_uom_code || ','
1290 || v_primary_uom_code);
1291 END IF;
1292 x_return_status_txt := l_order_uom_code || ',' || v_primary_uom_code;
1293 v_return_status := QP_PREQ_GRP.G_STATUS_INVALID_UOM_CONV;
1294 x_return_status := v_return_status;
1295 x_list_line_id := NULL;
1296 WHEN DUPLICATE_PRICE_LIST THEN
1297 IF l_debug = FND_API.G_TRUE THEN
1298 QP_PREQ_GRP.engine_debug(v_routine_name || ' Duplicate Price List '|| v_list_line_id || ',' || v_dup_list_line_id);
1299 END IF;
1300 v_return_msg := v_list_line_id || ',' || v_dup_list_line_id ;
1301 IF l_debug = FND_API.G_TRUE THEN
1302 QP_PREQ_GRP.engine_debug(v_return_msg);
1303 END IF;
1304 x_return_status_txt := v_return_msg;
1305 v_return_status := QP_PREQ_GRP.G_STATUS_DUP_PRICE_LIST;
1306 x_return_status := v_return_status;
1307 x_list_line_id := NULL;
1308 WHEN INVALID_UOM THEN
1309 IF l_debug = FND_API.G_TRUE THEN
1310 QP_PREQ_GRP.engine_debug(v_routine_name || ' Could not find a price list in Ordered UOM or Primary UOM');
1311 END IF;
1312 x_return_status_txt := 'Could not find a price list in Ordered UOM or Primary UOM';
1313 v_return_status := QP_PREQ_GRP.G_STATUS_INVALID_UOM;
1314 x_return_status := v_return_status;
1315 x_list_line_id := NULL;
1316 WHEN OTHERS THEN
1317 IF l_debug = FND_API.G_TRUE THEN
1318 QP_PREQ_GRP.engine_debug(v_routine_name || 'Unexpected Error');
1319 QP_PREQ_GRP.engine_debug(v_routine_name || ' ' || SQLERRM);
1320 END IF;
1321 v_return_status := FND_API.G_RET_STS_ERROR;
1322 x_return_status := v_return_status;
1323 x_return_status_txt := v_routine_name || ' ' || SQLERRM;
1324 x_list_line_id := NULL;
1325 END Determine_Pricing_UOM_And_Qty;
1326
1327 PROCEDURE Best_Price_Evaluation(p_list_price NUMBER,
1328 p_line_index NUMBER,
1329 p_pricing_phase_id NUMBER,
1330 p_incomp_grp_id VARCHAR2,
1331 p_precedence NUMBER, -- Added for bug#2661540
1332 p_manual_dis_flag VARCHAR2,
1333 x_list_line_id OUT NOCOPY NUMBER,
1334 x_return_status OUT NOCOPY VARCHAR2) AS
1335
1336
1337 v_index NUMBER := 1;
1338 v_list_price NUMBER;
1339 v_benefit_price NUMBER;
1340 v_benefit_percent NUMBER;
1341 v_list_line_id NUMBER;
1342 v_request_qty NUMBER;
1343 v_return_status VARCHAR2(30) := FND_API.G_RET_STS_SUCCESS;
1344 x_benefit_amount NUMBER;
1345 l_return_status VARCHAR2(30);
1346 l_return_status_text VARCHAR2(240);
1347 l_sign NUMBER;
1348 l_request_qty NUMBER;
1349 l_line_id NUMBER; --3244060
1350
1351 v_routine_name CONSTANT VARCHAR2(240) := 'Routine:QP_Resolve_Incompatability_PVTRUN.Best_Price_Evaluation';
1352
1353 /*
1354 INDX,QP_Resolve_Incompatability_PVTRUN.best_price_evaluation.get_list_lines_cur,qp_npreq_ldets_tmp_N1,LINE_INDEX,1
1355 INDX,QP_Resolve_Incompatability_PVTRUN.best_price_evaluation.get_list_lines_cur,qp_npreq_ldets_tmp_N1,PRICING_STATUS_CODE,2
1356 INDX,QP_Resolve_Incompatability_PVTRUN.best_price_evaluation.get_list_lines_cur,qp_npreq_ldets_tmp_N1,PRICING_PHASE_ID,3
1357 INDX,QP_Resolve_Incompatability_PVTRUN.best_price_evaluation.get_list_lines_cur,qp_npreq_ldets_tmp_N1,CREATED_FROM_LIST_HEADER_ID,4
1358
1359 INDX,QP_Resolve_Incompatability_PVTRUN.best_price_evaluation.get_list_lines_cur,qp_npreq_ldets_tmp_N1,CREATED_FROM_LIST_LINE_ID,5
1360 INDX,QP_Resolve_Incompatability_PVTRUN.best_price_evaluation.get_list_lines_cur,QP_LIST_LINES_PK,LIST_LINE_ID,1
1361 */
1362 -- For bug#2661540
1363 CURSOR get_list_lines_cur IS
1364 SELECT b.CREATED_FROM_LIST_HEADER_ID, b.CREATED_FROM_LIST_LINE_ID,b.line_detail_index, --3244060
1365 b.CREATED_FROM_LIST_LINE_TYPE, b.OPERAND_CALCULATION_CODE, b.OPERAND_VALUE,a.ESTIM_GL_VALUE ,
1366 a.BENEFIT_PRICE_LIST_LINE_ID,a.PRICE_BREAK_TYPE_CODE,b.LINE_QUANTITY,b.GROUP_QUANTITY,b.GROUP_AMOUNT, --[julin/4240067/4307242]
1367 b.modifier_level_code
1368 FROM QP_LIST_LINES a, qp_npreq_ldets_tmp b, qp_npreq_line_attrs_tmp c
1369 WHERE a.LIST_HEADER_ID = b.CREATED_FROM_LIST_HEADER_ID
1370 AND a.LIST_LINE_ID = b.CREATED_FROM_LIST_LINE_ID
1371 AND b.CREATED_FROM_LIST_TYPE_CODE not in (QP_PREQ_GRP.G_PRICE_LIST_HEADER,QP_PREQ_GRP.G_AGR_LIST_HEADER)
1372 AND b.PRICING_PHASE_ID = p_pricing_phase_id
1373 AND b.INCOMPATABILITY_GRP_CODE = p_incomp_grp_id
1374 AND b.LINE_INDEX = p_line_index
1375 AND b.PRICING_STATUS_CODE = QP_PREQ_GRP.G_STATUS_NEW
1376 AND ((b.AUTOMATIC_FLAG = QP_PREQ_GRP.G_YES AND p_manual_dis_flag = QP_PREQ_GRP.G_YES)
1377 OR p_manual_dis_flag = QP_PREQ_GRP.G_NO)
1378 AND c.LIST_LINE_ID(+) = a.LIST_LINE_ID
1379 AND c.LIST_HEADER_ID(+) = a.LIST_HEADER_ID
1380 AND c.ATTRIBUTE_TYPE(+) = 'QUALIFIER'
1381 AND c.INCOMPATABILITY_GRP_CODE(+) = p_incomp_grp_id
1382 AND c.PRICING_PHASE_ID(+) = p_pricing_phase_id
1383 AND c.PRICING_STATUS_CODE(+) = 'N'
1384 AND c.LINE_INDEX(+) = p_line_index
1385 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)));
1386
1387 /* Commented for bug 2661540
1388 CURSOR get_list_lines_cur IS
1389 SELECT b.CREATED_FROM_LIST_HEADER_ID, b.CREATED_FROM_LIST_LINE_ID,
1390 b.CREATED_FROM_LIST_LINE_TYPE, b.OPERAND_CALCULATION_CODE, b.OPERAND_VALUE,a.ESTIM_GL_VALUE ,
1391 a.BENEFIT_PRICE_LIST_LINE_ID,a.PRICE_BREAK_TYPE_CODE,b.GROUP_QUANTITY,b.GROUP_AMOUNT
1392 FROM QP_LIST_LINES a, qp_npreq_ldets_tmp b
1393 WHERE a.LIST_HEADER_ID = b.CREATED_FROM_LIST_HEADER_ID
1394 AND a.LIST_LINE_ID = b.CREATED_FROM_LIST_LINE_ID
1395 AND b.CREATED_FROM_LIST_TYPE_CODE not in (QP_PREQ_GRP.G_PRICE_LIST_HEADER,QP_PREQ_GRP.G_AGR_LIST_HEADER)
1396 AND b.PRICING_PHASE_ID = p_pricing_phase_id
1397 AND b.INCOMPATABILITY_GRP_CODE = p_incomp_grp_id
1398 AND b.LINE_INDEX = p_line_index
1399 AND b.PRICING_STATUS_CODE = QP_PREQ_GRP.G_STATUS_NEW
1400 AND ((b.AUTOMATIC_FLAG = QP_PREQ_GRP.G_YES AND p_manual_dis_flag = QP_PREQ_GRP.G_YES) OR
1401 p_manual_dis_flag = QP_PREQ_GRP.G_NO);
1402 */
1403 /*
1404 INDX,QP_Resolve_Incompatability_PVTRUN.best_price_evaluation.get_request_qty_cur,qp_npreq_lines_tmp_N1,LINE_INDEX,1*/
1405
1406 CURSOR get_request_qty_cur IS
1407 SELECT nvl(PRICED_QUANTITY,LINE_QUANTITY)
1408 FROM qp_npreq_lines_tmp
1409 WHERE LINE_INDEX = p_line_index;
1410
1411 /*
1412 INDX,QP_Resolve_Incompatability_PVTRUN.best_price_evaluation.get_list_price_cur,QP_LIST_LINES_PK,LIST_LINE_ID,1
1413 */
1414
1415 CURSOR get_list_price_cur(p_list_line_id NUMBER) IS
1416 SELECT OPERAND
1417 FROM QP_LIST_LINES
1418 WHERE LIST_LINE_ID = p_list_line_id
1419 AND ARITHMETIC_OPERATOR = QP_PREQ_GRP.G_UNIT_PRICE;
1420
1421 /*
1422 INDX,QP_Resolve_Incompatability_PVTRUN.best_price_evaluation.get_list_lines_in_order,qp_npreq_ldets_tmp_N1,LINE_INDEX,1
1423 INDX,QP_Resolve_Incompatability_PVTRUN.best_price_evaluation.get_list_lines_in_order,qp_npreq_ldets_tmp_N1,PRICING_STATUS_CODE,2
1424 INDX,QP_Resolve_Incompatability_PVTRUN.best_price_evaluation.get_list_lines_in_order,qp_npreq_ldets_tmp_N1,PRICING_PHASE_ID,3
1425 */
1426
1427 -- For bug 2661540
1428 CURSOR get_list_lines_in_order IS
1429 SELECT a.CREATED_FROM_LIST_LINE_ID
1430 FROM qp_npreq_ldets_tmp a, qp_npreq_line_attrs_tmp b
1431 WHERE a.CREATED_FROM_LIST_TYPE_CODE not in (QP_PREQ_GRP.G_PRICE_LIST_HEADER,QP_PREQ_GRP.G_AGR_LIST_HEADER)
1432 AND a.PRICING_PHASE_ID = p_pricing_phase_id
1433 AND a.INCOMPATABILITY_GRP_CODE = p_incomp_grp_id
1434 AND a.LINE_INDEX = p_line_index
1435 AND a.PRICING_STATUS_CODE = QP_PREQ_GRP.G_STATUS_NEW
1436 AND ((a.AUTOMATIC_FLAG = QP_PREQ_GRP.G_YES AND p_manual_dis_flag = QP_PREQ_GRP.G_YES) OR
1437 p_manual_dis_flag = QP_PREQ_GRP.G_NO)
1438 AND b.LIST_LINE_ID(+) = a.CREATED_FROM_LIST_LINE_ID
1439 AND b.LIST_HEADER_ID(+) = a.CREATED_FROM_LIST_HEADER_ID
1440 AND b.ATTRIBUTE_TYPE(+) = 'QUALIFIER'
1441 AND b.INCOMPATABILITY_GRP_CODE(+) = p_incomp_grp_id
1442 AND b.PRICING_PHASE_ID(+) = p_pricing_phase_id
1443 AND b.PRICING_STATUS_CODE(+) = 'N'
1444 AND b.LINE_INDEX(+) = p_line_index
1445 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)))
1446 ORDER BY BEST_PERCENT DESC;
1447
1448 /* Commented for Bug 2661540
1449 CURSOR get_list_lines_in_order IS
1450 SELECT CREATED_FROM_LIST_LINE_ID
1451 FROM qp_npreq_ldets_tmp
1452 WHERE CREATED_FROM_LIST_TYPE_CODE not in (QP_PREQ_GRP.G_PRICE_LIST_HEADER,QP_PREQ_GRP.G_AGR_LIST_HEADER)
1453 AND PRICING_PHASE_ID = p_pricing_phase_id
1454 AND INCOMPATABILITY_GRP_CODE = p_incomp_grp_id
1455 AND LINE_INDEX = p_line_index
1456 AND PRICING_STATUS_CODE = QP_PREQ_GRP.G_STATUS_NEW
1457 AND ((AUTOMATIC_FLAG = QP_PREQ_GRP.G_YES AND p_manual_dis_flag = QP_PREQ_GRP.G_YES) OR
1458 p_manual_dis_flag = QP_PREQ_GRP.G_NO)
1459 ORDER BY BEST_PERCENT DESC;
1460 */
1461
1462 --[julin/5456188]
1463 l_req_value_per_unit NUMBER;
1464 l_total_value NUMBER;
1465 l_volume_attribute VARCHAR2(240);
1466 l_calc_quantity NUMBER;
1467
1468 BEGIN
1469 l_debug := QP_PREQ_GRP.G_DEBUG_ENGINE;
1470 FOR i IN get_list_lines_cur
1471 LOOP
1472 /*IF (i.CREATED_FROM_LIST_LINE_TYPE = QP_PREQ_GRP.G_OTHER_ITEM_DISCOUNT) THEN
1473 IF l_debug = FND_API.G_TRUE THEN
1474 QP_PREQ_GRP.engine_debug('Benefit Price List Line Id is Very important: ' || i.BENEFIT_PRICE_LIST_LINE_ID);
1475 END IF;
1476 OPEN get_list_price_cur(i.BENEFIT_PRICE_LIST_LINE_ID);
1477 FETCH get_list_price_cur INTO v_list_price;
1478 CLOSE get_list_price_cur;
1479 IF (i.OPERAND_CALCULATION_CODE = QP_PREQ_GRP.G_PERCENT_DISCOUNT) THEN
1480 v_benefit_price := (v_list_price) * (i.OPERAND_VALUE / 100);
1481 ELSIF (i.OPERAND_CALCULATION_CODE = QP_PREQ_GRP.G_AMOUNT_DISCOUNT) THEN
1482 v_benefit_price := (v_list_price - i.OPERAND_VALUE);
1483 ELSIF (i.OPERAND_CALCULATION_CODE IS NULL) THEN
1484 v_benefit_price := v_list_price;
1485 END IF; */
1486 IF (i.CREATED_FROM_LIST_LINE_TYPE IN (QP_PREQ_GRP.G_ITEM_UPGRADE,QP_PREQ_GRP.G_TERMS_SUBSTITUTION,
1487 QP_PREQ_GRP.G_COUPON_ISSUE,QP_PREQ_GRP.G_OTHER_ITEM_DISCOUNT,
1488 QP_PREQ_GRP.G_PROMO_GOODS_DISCOUNT)) THEN
1489 v_benefit_price := nvl(i.ESTIM_GL_VALUE,0);
1490 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
1491
1492 IF (i.CREATED_FROM_LIST_LINE_TYPE = QP_PREQ_GRP.G_DISCOUNT) THEN
1493 l_sign := 1;
1494 ELSE
1495 l_sign := -1;
1496 END IF;
1497
1498 IF l_debug = FND_API.G_TRUE THEN
1499 QP_PREQ_GRP.engine_debug('List Price For Best Price Eval: ' || p_list_price);
1500
1501 END IF;
1502
1503 IF(i.modifier_level_code <> 'ORDER') THEN -- bug 4234043
1504 IF (i.OPERAND_CALCULATION_CODE = QP_PREQ_GRP.G_PERCENT_DISCOUNT) THEN
1505 v_benefit_price := l_sign * p_list_price * (i.OPERAND_VALUE / 100);
1506 ELSIF (i.OPERAND_CALCULATION_CODE = QP_PREQ_GRP.G_AMOUNT_DISCOUNT) THEN
1507 v_benefit_price := l_sign * i.OPERAND_VALUE;
1508 ELSIF (i.OPERAND_CALCULATION_CODE = QP_PREQ_GRP.G_NEWPRICE_DISCOUNT) THEN
1509 l_sign := 1; -- For NEWPRICE l_sign will always be 1 irrespective of whether it is a discount or surcharge
1510 v_benefit_price := l_sign * (p_list_price - i.OPERAND_VALUE);
1511 ELSIF (i.OPERAND_CALCULATION_CODE = QP_PREQ_GRP.G_LUMPSUM_DISCOUNT) THEN
1512 OPEN get_request_qty_cur;
1513 FETCH get_request_qty_cur INTO v_request_qty;
1514 CLOSE get_request_qty_cur;
1515 IF (i.modifier_level_code = QP_PREQ_GRP.G_LINE_GROUP) THEN
1516 l_calc_quantity := nvl(nvl(i.group_quantity, i.group_amount), v_request_qty);
1517 ELSE
1518 l_calc_quantity := v_request_qty;
1519 END IF;
1520 v_benefit_price := l_sign * i.OPERAND_VALUE/l_calc_quantity;
1521 END IF;
1522 ELSE ---- modifier_level_code='ORDER'
1523 IF (i.OPERAND_CALCULATION_CODE IN (QP_PREQ_GRP.G_PERCENT_DISCOUNT)) THEN
1524 v_benefit_price := l_sign * i.OPERAND_VALUE;
1525 END IF;
1526 END IF;
1527
1528 ELSIF (i.CREATED_FROM_LIST_LINE_TYPE = QP_PREQ_GRP.G_PRICE_BREAK_TYPE) THEN
1529
1530 IF l_debug = FND_API.G_TRUE THEN
1531 QP_PREQ_GRP.engine_debug('Best Price Eval For Price Break Line Quantity : ' || i.line_quantity); --[julin/4240067/4307242]
1532 QP_PREQ_GRP.engine_debug('Best Price Eval For Price Break Group Quantity : ' || i.group_quantity);
1533 QP_PREQ_GRP.engine_debug('Best Price Eval For Price Break Group Amount : ' || i.group_amount);
1534
1535 END IF;
1536 --[julin/4240067/4307242] using i.line_quantity
1537 l_request_qty := nvl(nvl(i.group_quantity,i.group_amount),i.line_quantity);
1538
1539 IF l_debug = FND_API.G_TRUE THEN
1540 QP_PREQ_GRP.engine_debug('Best Price Eval For Price Break Qualifier Value : ' || l_request_qty);
1541
1542 END IF;
1543
1544 /* Added for 3244060 */
1545 IF QP_PREQ_GRP.G_PUBLIC_API_CALL_FLAG = 'Y' THEN
1546 l_line_id :=i.line_detail_index;
1547 ELSE
1548 l_line_id :=i.created_from_list_line_id;
1549 END IF;
1550
1551 --[julin/5456188] using same price_break_calculation as PPREB, but with no bucket/netamt support
1552 OPEN get_request_qty_cur;
1553 FETCH get_request_qty_cur INTO v_request_qty;
1554 CLOSE get_request_qty_cur;
1555
1556 BEGIN
1557 select pricing_attribute
1558 into l_volume_attribute
1559 from qp_pricing_attributes
1560 where list_line_id = i.created_from_list_line_id
1561 and pricing_attribute_context = QP_PREQ_GRP.G_PRIC_VOLUME_CONTEXT
1562 and excluder_flag='N'; --3607956
1563 EXCEPTION
1564 When OTHERS Then
1565 l_volume_attribute := null;
1566 END;
1567
1568 IF (i.modifier_level_code IN (QP_PREQ_PUB.G_LINE_LEVEL, QP_PREQ_PUB.G_ORDER_LEVEL)) THEN
1569 IF l_volume_attribute = QP_PREQ_PUB.G_QUANTITY_ATTRIBUTE THEN
1570 l_total_value := 0;
1571 l_req_value_per_unit := v_request_qty;
1572 ELSE
1573 l_total_value := i.line_quantity;
1574 l_req_value_per_unit := v_request_qty;
1575 END IF;
1576 ELSE -- linegroup
1577 IF l_volume_attribute = QP_PREQ_PUB.G_QUANTITY_ATTRIBUTE THEN
1578 l_total_value := 0;
1579 l_req_value_per_unit := i.group_quantity;
1580 ELSE
1581 l_total_value := i.line_quantity;
1582 l_req_value_per_unit := i.group_amount;
1583 END IF;
1584 END IF;
1585
1586 QP_Calculate_Price_PUB.Price_Break_Calculation(l_line_id, --3244060
1587 i.price_break_type_code,
1588 p_line_index,
1589 l_req_value_per_unit,--p_req_value_per_unit
1590 0,--p_applied_req_value_per_unit, no net amt support here
1591 l_total_value,--p_total_value
1592 p_list_price,
1593 0,--p_line_quantity, not used
1594 0,--p_bucketed_adjustment, no bucket support here
1595 'N',--p_bucketed_flag, no bucket support here
1596 'N',--p_automatic_flag, don't want deletion
1597 x_benefit_amount,
1598 l_return_status,
1599 l_return_status_text);
1600
1601 IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
1602 NULL;
1603 END IF;
1604
1605 v_benefit_price := x_benefit_amount;
1606 END IF;
1607
1608 IF l_debug = FND_API.G_TRUE THEN
1609 QP_PREQ_GRP.engine_debug('Benefit Price For Best Price Eval: ' || v_benefit_price);
1610
1611 END IF;
1612 IF (i.modifier_level_code <> 'ORDER') THEN --bug 4234043
1613 IF (p_list_price > 0) THEN
1614 v_benefit_percent := (nvl(v_benefit_price,0) / p_list_price) * 100;
1615 ELSE
1616 v_benefit_percent := 0;
1617 END IF;
1618 ELSE -- bug 4234043
1619 v_benefit_percent :=v_benefit_price;
1620 END IF;
1621
1622 IF l_debug = FND_API.G_TRUE THEN
1623 QP_PREQ_GRP.engine_debug('Benefit Percent For Best Price Eval: ' || v_benefit_percent);
1624
1625 END IF;
1626 /*
1627 INDX,QP_Resolve_Incompatability_PVTRUN.best_price_evaluation.upd1,qp_npreq_ldets_tmp_N1,LINE_INDEX,1
1628 INDX,QP_Resolve_Incompatability_PVTRUN.best_price_evaluation.upd1,qp_npreq_ldets_tmp_N1,PRICING_STATUS_CODE,2
1629 INDX,QP_Resolve_Incompatability_PVTRUN.best_price_evaluation.upd1,qp_npreq_ldets_tmp_N1,PRICING_PHASE_ID,3
1630 INDX,QP_Resolve_Incompatability_PVTRUN.best_price_evaluation.upd1,qp_npreq_ldets_tmp_N1,CREATED_FROM_LIST_HEADER_ID,4
1631 INDX,QP_Resolve_Incompatability_PVTRUN.best_price_evaluation.upd1,qp_npreq_ldets_tmp_N1,CREATED_FROM_LIST_LINE_ID,5
1632 */
1633 UPDATE qp_npreq_ldets_tmp -- upd1
1634 SET BEST_PERCENT = v_benefit_percent
1635 WHERE CREATED_FROM_LIST_HEADER_ID = i.CREATED_FROM_LIST_HEADER_ID
1636 AND CREATED_FROM_LIST_LINE_ID = i.CREATED_FROM_LIST_LINE_ID
1637 AND PRICING_PHASE_ID = p_pricing_phase_id
1638 AND INCOMPATABILITY_GRP_CODE = p_incomp_grp_id
1639 AND PRICING_STATUS_CODE = QP_PREQ_GRP.G_STATUS_NEW
1640 AND LINE_INDEX = p_line_index;
1641
1642 END LOOP;
1643
1644 OPEN get_list_lines_in_order;
1645 FETCH get_list_lines_in_order into v_list_line_id;
1646 CLOSE get_list_lines_in_order;
1647
1648 /*
1649 INDX,QP_Resolve_Incompatability_PVTRUN.best_price_evaluation.upd2,qp_npreq_ldets_tmp_N1,LINE_INDEX,1
1650 INDX,QP_Resolve_Incompatability_PVTRUN.best_price_evaluation.upd2,qp_npreq_ldets_tmp_N1,PRICING_STATUS_CODE,2
1651 INDX,QP_Resolve_Incompatability_PVTRUN.best_price_evaluation.upd2,qp_npreq_ldets_tmp_N1,PRICING_PHASE_ID,3
1652 */
1653
1654 UPDATE qp_npreq_ldets_tmp -- upd2
1655 SET PRICING_STATUS_CODE = QP_PREQ_GRP.G_STATUS_BEST_PRICE_EVAL
1656 WHERE CREATED_FROM_LIST_LINE_ID <> v_list_line_id
1657 AND CREATED_FROM_LIST_TYPE_CODE not in (QP_PREQ_GRP.G_PRICE_LIST_HEADER,QP_PREQ_GRP.G_AGR_LIST_HEADER)
1658 AND PRICING_PHASE_ID = p_pricing_phase_id
1659 AND INCOMPATABILITY_GRP_CODE = p_incomp_grp_id
1660 AND LINE_INDEX = p_line_index
1661 AND PRICING_STATUS_CODE = QP_PREQ_GRP.G_STATUS_NEW;
1662
1663 /* UPDATE qp_npreq_line_attrs_tmp a
1664 SET a.PRICING_STATUS_CODE = QP_PREQ_GRP.G_STATUS_BEST_PRICE_EVAL
1665 WHERE a.LIST_LINE_ID IN (SELECT b.CREATED_FROM_LIST_LINE_ID
1666 FROM qp_npreq_ldets_tmp b
1667 WHERE b.PRICING_STATUS_CODE = QP_PREQ_GRP.G_STATUS_BEST_PRICE_EVAL
1668 AND b.CREATED_FROM_LIST_LINE_ID <> v_list_line_id
1669 AND b.LINE_INDEX = p_line_index)
1670 AND a.LINE_INDEX = p_line_index; */
1671
1672 /* UPDATE qp_npreq_line_attrs_tmp a
1673 SET a.PRICING_STATUS_CODE = QP_PREQ_GRP.G_STATUS_BEST_PRICE_EVAL
1674 WHERE a.LIST_LINE_ID
1675 IN (SELECT b.CREATED_FROM_LIST_LINE_ID
1676 FROM qp_npreq_ldets_tmp b
1677 WHERE b.PRICING_STATUS_CODE = QP_PREQ_GRP.G_STATUS_BEST_PRICE_EVAL
1678 AND b.PRICING_PHASE_ID = p_pricing_phase_id
1679 AND b.INCOMPATABILITY_GRP_CODE = p_incomp_grp_id
1680 AND b.CREATED_FROM_LIST_LINE_ID <> v_list_line_id
1681 AND b.LINE_INDEX = p_line_index)
1682 AND a.LINE_INDEX = p_line_index; */
1683
1684 x_list_line_id := v_list_line_id;
1685 IF l_debug = FND_API.G_TRUE THEN
1686 QP_PREQ_GRP.engine_debug('Best List line Id: ' || v_list_line_id);
1687
1688 END IF;
1689 x_return_status := v_return_status; -- SUCCESS
1690
1691 EXCEPTION
1692 WHEN OTHERS THEN
1693 IF l_debug = FND_API.G_TRUE THEN
1694 QP_PREQ_GRP.engine_debug(v_routine_name || 'Unexpected Error');
1695 QP_PREQ_GRP.engine_debug(v_routine_name || ' ' || SQLERRM);
1696 END IF;
1697 v_return_status := FND_API.G_RET_STS_ERROR;
1698 x_return_status := v_return_status;
1699 END Best_Price_Evaluation;
1700
1701 PROCEDURE Delete_Lines_Complete (p_line_index_tbl IN QP_PREQ_GRP.NUMBER_TYPE,
1702 p_pricing_status_text IN VARCHAR2,
1703 x_return_status OUT NOCOPY VARCHAR2,
1704 x_return_status_txt OUT NOCOPY VARCHAR2) AS
1705 BEGIN
1706 l_debug := QP_PREQ_GRP.G_DEBUG_ENGINE;
1707 IF l_debug = FND_API.G_TRUE THEN
1708 QP_PREQ_GRP.engine_debug('deleting lines/ldets/attrs/rltd:'||p_pricing_status_text);
1709 END IF;
1710
1711 FORALL i IN p_line_index_tbl.FIRST..p_line_index_tbl.LAST
1712 UPDATE qp_npreq_lines_tmp
1713 SET pricing_status_code = QP_PREQ_PUB.G_NOT_VALID, process_status = QP_PREQ_PUB.G_NOT_VALID
1714 WHERE line_index = p_line_index_tbl(i)
1715 and line_id is null; --bug 7539796
1716 FORALL i IN p_line_index_tbl.FIRST..p_line_index_tbl.LAST
1717 UPDATE qp_npreq_ldets_tmp
1718 SET PRICING_STATUS_CODE = QP_PREQ_PUB.G_STATUS_DELETED,
1719 PRICING_STATUS_TEXT = substr(p_pricing_status_text,1,2000)
1720 WHERE LINE_INDEX = p_line_index_tbl(i);
1721 FORALL i IN p_line_index_tbl.FIRST..p_line_index_tbl.LAST
1722 UPDATE qp_npreq_line_attrs_tmp
1723 SET PRICING_STATUS_CODE = QP_PREQ_PUB.G_STATUS_DELETED,
1724 PRICING_STATUS_TEXT = substr(p_pricing_status_text,1,240)
1725 WHERE LINE_INDEX = p_line_index_tbl(i);
1726 FORALL i IN p_line_index_tbl.FIRST..p_line_index_tbl.LAST
1727 UPDATE qp_npreq_rltd_lines_tmp
1728 SET PRICING_STATUS_CODE = QP_PREQ_PUB.G_STATUS_DELETED,
1729 PRICING_STATUS_TEXT = substr(p_pricing_status_text,1,240)
1730 WHERE RELATED_LINE_INDEX = p_line_index_tbl(i);
1731 EXCEPTION
1732 WHEN OTHERS THEN
1733 x_return_status := FND_API.G_RET_STS_ERROR;
1734 x_return_status_txt := 'Unexpected error in QP_Resolve_Incompatability_PVT.Delete_Lines_Complete: ' || SQLERRM;
1735 END Delete_Lines_Complete;
1736
1737 PROCEDURE Delete_Ldets_Complete (p_line_detail_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 ldets/rltd:'||p_pricing_status_text);
1745 END IF;
1746
1747 FORALL i IN p_line_detail_index_tbl.FIRST..p_line_detail_index_tbl.LAST
1748 UPDATE qp_npreq_ldets_tmp a
1749 SET PRICING_STATUS_CODE = QP_PREQ_PUB.G_STATUS_DELETED,
1750 PRICING_STATUS_TEXT = substr(p_pricing_status_text,1,2000)
1751 WHERE LINE_DETAIL_INDEX = p_line_detail_index_tbl(i) OR
1752 EXISTS (SELECT 1 --[julin/4671446] also deleting children ldets
1753 FROM qp_npreq_rltd_lines_tmp
1754 WHERE LINE_DETAIL_INDEX = p_line_detail_index_tbl(i)
1755 AND RELATED_LINE_DETAIL_INDEX = a.LINE_DETAIL_INDEX);
1756 FORALL i IN p_line_detail_index_tbl.FIRST..p_line_detail_index_tbl.LAST
1757 UPDATE qp_npreq_rltd_lines_tmp
1758 SET PRICING_STATUS_CODE = QP_PREQ_PUB.G_STATUS_DELETED,
1759 PRICING_STATUS_TEXT = substr(p_pricing_status_text,1,240)
1760 WHERE (LINE_DETAIL_INDEX = p_line_detail_index_tbl(i) OR
1761 RELATED_LINE_DETAIL_INDEX = p_line_detail_index_tbl(i)); --[julin/4671446] also deleting children ldets
1762 EXCEPTION
1763 WHEN OTHERS THEN
1764 x_return_status := FND_API.G_RET_STS_ERROR;
1765 x_return_status_txt := 'Unexpected error in QP_Resolve_Incompatability_PVT.Delete_Ldets_Complete: ' || SQLERRM;
1766 END Delete_Ldets_Complete;
1767
1768
1769 PROCEDURE Delete_Incompatible_Lines(p_pricing_phase_id NUMBER,
1770 p_line_index NUMBER,
1771 p_incomp_grp_id VARCHAR2 := NULL,
1772 p_list_line_id NUMBER,
1773 p_excl_discount BOOLEAN,
1774 p_manual_dis_flag VARCHAR2,
1775 x_return_status OUT NOCOPY VARCHAR2) AS
1776
1777
1778 -- [prarasto/4141235] find loser prg line indexes to be deleted, incompatibility group
1779 --frontported fix done in 4134088
1780 CURSOR l_del_prg_lines_grp_cur IS
1781 SELECT rltd.RELATED_LINE_INDEX
1782 FROM qp_npreq_ldets_tmp ldets, qp_npreq_rltd_lines_tmp rltd
1783 WHERE ldets.INCOMPATABILITY_GRP_CODE = p_incomp_grp_id
1784 AND ldets.LINE_INDEX = p_line_index
1785 AND ldets.PRICING_PHASE_ID = p_pricing_phase_id
1786 AND ldets.CREATED_FROM_LIST_LINE_ID <> p_list_line_id
1787 AND ldets.LINE_DETAIL_TYPE_CODE <> QP_PREQ_GRP.G_CHILD_DETAIL_TYPE
1788 AND ldets.CREATED_FROM_LIST_LINE_TYPE IN (QP_PREQ_GRP.G_PROMO_GOODS_DISCOUNT)
1789 AND ldets.CREATED_FROM_LIST_LINE_ID = rltd.LIST_LINE_ID
1790 AND rltd.LINE_INDEX = p_line_index;
1791
1792 -- [prarasto/4141235] find loser prg line indexes to be deleted, incompatibility group
1793 --frontported fix done in 4134088
1794 CURSOR l_del_oid_ldets_grp_cur IS
1795 SELECT rltd.RELATED_LINE_DETAIL_INDEX
1796 FROM qp_npreq_ldets_tmp ldets, qp_npreq_rltd_lines_tmp rltd
1797 WHERE ldets.INCOMPATABILITY_GRP_CODE = p_incomp_grp_id
1798 AND ldets.LINE_INDEX = p_line_index
1799 AND ldets.PRICING_PHASE_ID = p_pricing_phase_id
1800 AND ldets.CREATED_FROM_LIST_LINE_ID <> p_list_line_id
1801 AND ldets.LINE_DETAIL_TYPE_CODE <> QP_PREQ_GRP.G_CHILD_DETAIL_TYPE
1802 AND ldets.CREATED_FROM_LIST_LINE_TYPE IN (QP_PREQ_GRP.G_OTHER_ITEM_DISCOUNT)
1803 AND ldets.CREATED_FROM_LIST_LINE_ID = rltd.LIST_LINE_ID
1804 AND rltd.LINE_INDEX = p_line_index;
1805
1806 -- [prarasto/4141235] find loser prg line indexes to be deleted, incompatibility group
1807 --frontported fix done in 4134088
1808 CURSOR l_del_prg_lines_excl_cur IS
1809 SELECT rltd.RELATED_LINE_INDEX
1810 FROM qp_npreq_ldets_tmp ldets, qp_npreq_rltd_lines_tmp rltd
1811 WHERE ldets.LINE_INDEX = p_line_index
1812 AND ldets.PRICING_PHASE_ID = p_pricing_phase_id
1813 AND ldets.CREATED_FROM_LIST_LINE_ID <> p_list_line_id
1814 AND ldets.LINE_DETAIL_TYPE_CODE <> QP_PREQ_GRP.G_CHILD_DETAIL_TYPE
1815 AND ldets.CREATED_FROM_LIST_LINE_TYPE IN (QP_PREQ_GRP.G_PROMO_GOODS_DISCOUNT)
1816 AND ldets.CREATED_FROM_LIST_LINE_ID = rltd.LIST_LINE_ID
1817 AND rltd.LINE_INDEX = p_line_index;
1818
1819 -- [prarasto/4141235] find loser prg line indexes to be deleted, incompatibility group
1820 --frontported fix done in 4134088
1821 CURSOR l_del_oid_ldets_excl_cur IS
1822 SELECT rltd.RELATED_LINE_DETAIL_INDEX
1823 FROM qp_npreq_ldets_tmp ldets, qp_npreq_rltd_lines_tmp rltd
1824 WHERE ldets.LINE_INDEX = p_line_index
1825 AND ldets.PRICING_PHASE_ID = p_pricing_phase_id
1826 AND ldets.CREATED_FROM_LIST_LINE_ID <> p_list_line_id
1827 AND ldets.LINE_DETAIL_TYPE_CODE <> QP_PREQ_GRP.G_CHILD_DETAIL_TYPE
1828 AND ldets.CREATED_FROM_LIST_LINE_TYPE IN (QP_PREQ_GRP.G_OTHER_ITEM_DISCOUNT)
1829 AND ldets.CREATED_FROM_LIST_LINE_ID = rltd.LIST_LINE_ID
1830 AND rltd.LINE_INDEX = p_line_index;
1831
1832 l_del_index_tbl QP_PREQ_GRP.NUMBER_TYPE;
1833
1834 l_status_code VARCHAR2(30);
1835 l_status_text VARCHAR2(240);
1836
1837 v_return_status VARCHAR2(30) := FND_API.G_RET_STS_SUCCESS;
1838 v_routine_name CONSTANT VARCHAR2(240):='Routine:QP_Resolve_Incompatability_PVTRUN.Delete_Incompatible_Lines';
1839
1840 BEGIN
1841 l_debug := QP_PREQ_GRP.G_DEBUG_ENGINE;
1842
1843 IF l_debug = FND_API.G_TRUE THEN
1844 QP_PREQ_GRP.engine_debug('Enter Delete_Incompatible_Lines:'||p_line_index||':'||p_incomp_grp_id||':'||p_list_line_id||':'||p_manual_dis_flag);
1845 END IF;
1846
1847 IF (p_manual_dis_flag = QP_PREQ_GRP.G_YES) THEN
1848 IF (p_excl_discount = FALSE) THEN
1849
1850 /*
1851 INDX,QP_Resolve_Incompatability_PVTRUN.delete_incompatible_lines.upd1,qp_npreq_ldets_tmp_N1,LINE_INDEX,1
1852 INDX,QP_Resolve_Incompatability_PVTRUN.delete_incompatible_lines.upd1,qp_npreq_ldets_tmp_N1,PRICING_STATUS_CODE,2
1853 INDX,QP_Resolve_Incompatability_PVTRUN.delete_incompatible_lines.upd1,qp_npreq_ldets_tmp_N1,PRICING_PHASE_ID,3
1854 */
1855
1856 UPDATE qp_npreq_ldets_tmp --upd1
1857 SET PRICING_STATUS_CODE = QP_PREQ_GRP.G_STATUS_INCOMP_LOGIC
1858 WHERE INCOMPATABILITY_GRP_CODE = p_incomp_grp_id
1859 AND LINE_INDEX = p_line_index
1860 AND PRICING_PHASE_ID = p_pricing_phase_id
1861 AND CREATED_FROM_LIST_LINE_ID <> p_list_line_id
1862 AND CREATED_FROM_LIST_LINE_TYPE NOT IN (QP_PREQ_GRP.G_DISCOUNT,QP_PREQ_GRP.G_SURCHARGE)
1863 AND LINE_DETAIL_TYPE_CODE <> QP_PREQ_GRP.G_CHILD_DETAIL_TYPE -- Don't delete PBH Children any time
1864 AND PRICING_STATUS_CODE = QP_PREQ_GRP.G_STATUS_NEW;
1865
1866 /*
1867 INDX,QP_Resolve_Incompatability_PVTRUN.delete_incompatible_lines.upd2,qp_npreq_ldets_tmp_N3,LINE_INDEX,1
1868 INDX,QP_Resolve_Incompatability_PVTRUN.delete_incompatible_lines.upd2,qp_npreq_ldets_tmp_N3,PRICING_PHASE_ID,2
1869 INDX,QP_Resolve_Incompatability_PVTRUN.delete_incompatible_lines.upd2,qp_npreq_ldets_tmp_N3,ASK_FOR_FLAG,3
1870 INDX,QP_Resolve_Incompatability_PVTRUN.delete_incompatible_lines.upd2,qp_npreq_ldets_tmp_N3,CREATED_FROM_LIST_LINE_TYPE,4
1871 INDX,QP_Resolve_Incompatability_PVTRUN.delete_incompatible_lines.upd2,qp_npreq_ldets_tmp_N3,PRICING_STATUS_CODE,5
1872 */
1873
1874 UPDATE qp_npreq_ldets_tmp --upd2
1875 SET PRICING_STATUS_CODE = QP_PREQ_GRP.G_STATUS_NEW,
1876 AUTOMATIC_FLAG = QP_PREQ_GRP.G_NO,
1877 APPLIED_FLAG = QP_PREQ_GRP.G_NO
1878 WHERE INCOMPATABILITY_GRP_CODE = p_incomp_grp_id
1879 AND LINE_INDEX = p_line_index
1880 AND PRICING_PHASE_ID = p_pricing_phase_id
1881 AND CREATED_FROM_LIST_LINE_ID <> p_list_line_id
1882 AND ASK_FOR_FLAG = QP_PREQ_GRP.G_NO
1883 AND CREATED_FROM_LIST_LINE_TYPE IN (QP_PREQ_GRP.G_DISCOUNT,QP_PREQ_GRP.G_SURCHARGE)
1884 AND LINE_DETAIL_TYPE_CODE <> QP_PREQ_GRP.G_CHILD_DETAIL_TYPE -- Don't delete PBH Children any time
1885 AND PRICING_STATUS_CODE IN (QP_PREQ_GRP.G_STATUS_NEW,QP_PREQ_GRP.G_STATUS_BEST_PRICE_EVAL);
1886
1887 /* UPDATE qp_npreq_line_attrs_tmp a
1888 SET a.PRICING_STATUS_CODE = QP_PREQ_GRP.G_STATUS_NEW
1889 WHERE a.LIST_LINE_ID
1890 IN (SELECT b.CREATED_FROM_LIST_LINE_ID
1891 FROM qp_npreq_ldets_tmp b
1892 WHERE b.PRICING_STATUS_CODE = QP_PREQ_GRP.G_STATUS_NEW
1893 AND b.PRICING_PHASE_ID = p_pricing_phase_id
1894 AND b.INCOMPATABILITY_GRP_CODE = p_incomp_grp_id
1895 AND b.CREATED_FROM_LIST_LINE_ID <> p_list_line_id
1896 AND ASK_FOR_FLAG = QP_PREQ_GRP.G_NO
1897 AND b.CREATED_FROM_LIST_LINE_TYPE IN (QP_PREQ_GRP.G_DISCOUNT,QP_PREQ_GRP.G_SURCHARGE)
1898 AND b.LINE_INDEX = p_line_index)
1899 AND a.LINE_INDEX = p_line_index; */
1900
1901 ELSE
1902 /*
1903 INDX,QP_Resolve_Incompatability_PVTRUN.delete_incompatible_lines.upd3,qp_npreq_ldets_tmp_N1,LINE_INDEX,1
1904 INDX,QP_Resolve_Incompatability_PVTRUN.delete_incompatible_lines.upd3,qp_npreq_ldets_tmp_N1,PRICING_STATUS_CODE,2
1905 INDX,QP_Resolve_Incompatability_PVTRUN.delete_incompatible_lines.upd3,qp_npreq_ldets_tmp_N1,PRICING_PHASE_ID,3
1906 */
1907
1908 UPDATE qp_npreq_ldets_tmp --upd3
1909 SET PRICING_STATUS_CODE = QP_PREQ_GRP.G_STATUS_INCOMP_LOGIC
1910 WHERE LINE_INDEX = p_line_index
1911 AND PRICING_PHASE_ID = p_pricing_phase_id
1912 AND CREATED_FROM_LIST_LINE_ID <> p_list_line_id
1913 AND CREATED_FROM_LIST_LINE_TYPE NOT IN (QP_PREQ_GRP.G_DISCOUNT,QP_PREQ_GRP.G_SURCHARGE)
1914 AND LINE_DETAIL_TYPE_CODE <> QP_PREQ_GRP.G_CHILD_DETAIL_TYPE -- Don't delete PBH Children any time
1915 AND PRICING_STATUS_CODE = QP_PREQ_GRP.G_STATUS_NEW;
1916
1917 /*
1918 INDX,QP_Resolve_Incompatability_PVTRUN.delete_incompatible_lines.upd4,qp_npreq_ldets_tmp_N3,LINE_INDEX,1
1919 INDX,QP_Resolve_Incompatability_PVTRUN.delete_incompatible_lines.upd4,qp_npreq_ldets_tmp_N3,PRICING_PHASE_ID,2
1920 INDX,QP_Resolve_Incompatability_PVTRUN.delete_incompatible_lines.upd4,qp_npreq_ldets_tmp_N3,ASK_FOR_FLAG,3
1921 INDX,QP_Resolve_Incompatability_PVTRUN.delete_incompatible_lines.upd4,qp_npreq_ldets_tmp_N3,CREATED_FROM_LIST_LINE_TYPE,4
1922 INDX,QP_Resolve_Incompatability_PVTRUN.delete_incompatible_lines.upd4,qp_npreq_ldets_tmp_N3,PRICING_STATUS_CODE,5
1923 */
1924
1925
1926 UPDATE qp_npreq_ldets_tmp --upd4
1927 SET PRICING_STATUS_CODE = QP_PREQ_GRP.G_STATUS_NEW,
1928 AUTOMATIC_FLAG = QP_PREQ_GRP.G_NO,
1929 APPLIED_FLAG = QP_PREQ_GRP.G_NO
1930 WHERE LINE_INDEX = p_line_index
1931 AND PRICING_PHASE_ID = p_pricing_phase_id
1932 AND CREATED_FROM_LIST_LINE_ID <> p_list_line_id
1933 AND ASK_FOR_FLAG = QP_PREQ_GRP.G_NO
1934 AND CREATED_FROM_LIST_LINE_TYPE IN (QP_PREQ_GRP.G_DISCOUNT,QP_PREQ_GRP.G_SURCHARGE)
1935 AND LINE_DETAIL_TYPE_CODE <> QP_PREQ_GRP.G_CHILD_DETAIL_TYPE -- Don't delete PBH Children any time
1936 AND PRICING_STATUS_CODE IN (QP_PREQ_GRP.G_STATUS_NEW,QP_PREQ_GRP.G_STATUS_BEST_PRICE_EVAL);
1937
1938 /* UPDATE qp_npreq_line_attrs_tmp a
1939 SET a.PRICING_STATUS_CODE = QP_PREQ_GRP.G_STATUS_NEW
1940 WHERE a.LIST_LINE_ID
1941 IN (SELECT b.CREATED_FROM_LIST_LINE_ID
1942 FROM qp_npreq_ldets_tmp b
1943 WHERE b.PRICING_STATUS_CODE = QP_PREQ_GRP.G_STATUS_NEW
1944 AND b.PRICING_PHASE_ID = p_pricing_phase_id
1945 AND b.INCOMPATABILITY_GRP_CODE = p_incomp_grp_id
1946 AND b.CREATED_FROM_LIST_LINE_ID <> p_list_line_id
1947 AND ASK_FOR_FLAG = QP_PREQ_GRP.G_NO
1948 AND b.CREATED_FROM_LIST_LINE_TYPE IN (QP_PREQ_GRP.G_DISCOUNT,QP_PREQ_GRP.G_SURCHARGE)
1949 AND b.LINE_INDEX = p_line_index)
1950 AND a.LINE_INDEX = p_line_index; */
1951
1952 END IF; --p_exclusive_discount = TRUE
1953 ELSE -- Automatic discounts
1954 IF (p_excl_discount = FALSE) THEN
1955
1956 /*
1957 INDX,QP_Resolve_Incompatability_PVTRUN.delete_incompatible_lines.upd5,qp_npreq_ldets_tmp_N1,LINE_INDEX,1
1958 INDX,QP_Resolve_Incompatability_PVTRUN.delete_incompatible_lines.upd5,qp_npreq_ldets_tmp_N1,PRICING_STATUS_CODE,2
1959 INDX,QP_Resolve_Incompatability_PVTRUN.delete_incompatible_lines.upd5,qp_npreq_ldets_tmp_N1,PRICING_PHASE_ID,3
1960 */
1961
1962 UPDATE qp_npreq_ldets_tmp --upd5
1963 SET PRICING_STATUS_CODE = QP_PREQ_GRP.G_STATUS_INCOMP_LOGIC
1964 WHERE INCOMPATABILITY_GRP_CODE = p_incomp_grp_id
1965 AND LINE_INDEX = p_line_index
1966 AND PRICING_PHASE_ID = p_pricing_phase_id
1967 AND CREATED_FROM_LIST_LINE_ID <> p_list_line_id
1968 AND LINE_DETAIL_TYPE_CODE <> QP_PREQ_GRP.G_CHILD_DETAIL_TYPE -- Don't delete PBH Children any time
1969 AND PRICING_STATUS_CODE = QP_PREQ_GRP.G_STATUS_NEW;
1970 ELSE
1971 UPDATE qp_npreq_ldets_tmp
1972 SET PRICING_STATUS_CODE = QP_PREQ_GRP.G_STATUS_INCOMP_LOGIC
1973 WHERE LINE_INDEX = p_line_index
1974 AND PRICING_PHASE_ID = p_pricing_phase_id
1975 AND CREATED_FROM_LIST_LINE_ID <> p_list_line_id
1976 AND LINE_DETAIL_TYPE_CODE <> QP_PREQ_GRP.G_CHILD_DETAIL_TYPE -- Don't delete PBH Children any time
1977 AND PRICING_STATUS_CODE = QP_PREQ_GRP.G_STATUS_NEW;
1978 END IF; -- p_exclusive_discount = FALSE
1979 END IF;
1980
1981 IF (p_excl_discount = FALSE) THEN
1982
1983 IF l_debug = FND_API.G_TRUE THEN
1984 QP_PREQ_GRP.engine_debug('Checking for PRG/OID losers in incompatibility group '||p_incomp_grp_id);
1985 END IF;
1986
1987 -- [prarasto/4141235] delete loser prg/oid lines/ldets/attrs/rltd
1988 --frontported fix done in 41340888
1989 OPEN l_del_prg_lines_grp_cur;
1990 FETCH l_del_prg_lines_grp_cur
1991 BULK COLLECT INTO l_del_index_tbl;
1992 CLOSE l_del_prg_lines_grp_cur;
1993
1994 IF (l_del_index_tbl.count > 0) THEN
1995 Delete_Lines_Complete(l_del_index_tbl, 'PRG DELETED BY INCOMPATIBILITY LOGIC', l_status_code, l_status_text);
1996 END IF;
1997
1998 -- [prarasto/4141235] delete loser oid ldets/rltd
1999 --frontported fix done in 41340888
2000 OPEN l_del_oid_ldets_grp_cur;
2001 FETCH l_del_oid_ldets_grp_cur
2002 BULK COLLECT INTO l_del_index_tbl;
2003 CLOSE l_del_oid_ldets_grp_cur;
2004
2005 IF (l_del_index_tbl.count > 0) THEN
2006 Delete_Ldets_Complete(l_del_index_tbl, 'OID DELETED BY INCOMPATIBILITY LOGIC', l_status_code, l_status_text);
2007 END IF;
2008
2009 ELSE
2010
2011 IF l_debug = FND_API.G_TRUE THEN
2012 QP_PREQ_GRP.engine_debug('Checking for PRG/OID losers to exclusive group line' );
2013 END IF;
2014
2015 -- [prarasto/4141235] delete loser prg/oid lines/ldets/attrs/rltd, exclusive winner
2016 --frontported fix done in 41340888
2017 OPEN l_del_prg_lines_excl_cur;
2018 FETCH l_del_prg_lines_excl_cur
2019 BULK COLLECT INTO l_del_index_tbl;
2020 CLOSE l_del_prg_lines_excl_cur;
2021
2022 IF (l_del_index_tbl.count > 0) THEN
2023 Delete_Lines_Complete(l_del_index_tbl, 'PRG DELETED BY INCOMPATIBILITY LOGIC', l_status_code, l_status_text);
2024 END IF;
2025
2026 -- [prarasto/4141235] delete loser oid ldets/rltd, exclusive winner
2027 --frontported fix done in 41340888
2028 OPEN l_del_oid_ldets_excl_cur;
2029 FETCH l_del_oid_ldets_excl_cur
2030 BULK COLLECT INTO l_del_index_tbl;
2031 CLOSE l_del_oid_ldets_excl_cur;
2032
2033 IF (l_del_index_tbl.count > 0) THEN
2034 Delete_Ldets_Complete(l_del_index_tbl, 'OID DELETED BY INCOMPATIBILITY LOGIC', l_status_code, l_status_text);
2035 END IF;
2036
2037 END IF;
2038
2039 x_return_status := v_return_status;
2040 EXCEPTION
2041 WHEN OTHERS THEN
2042 IF l_debug = FND_API.G_TRUE THEN
2043 QP_PREQ_GRP.engine_debug(v_routine_name || ' ' || SQLERRM);
2044 END IF;
2045 v_return_status := FND_API.G_RET_STS_ERROR;
2046 x_return_status := v_return_status;
2047 END Delete_Incompatible_Lines;
2048
2049 PROCEDURE Best_Price_For_Phase(p_list_price NUMBER,
2050 p_line_index NUMBER,
2051 p_pricing_phase_id NUMBER,
2052 x_return_status OUT NOCOPY VARCHAR2,
2053 x_return_status_txt OUT NOCOPY VARCHAR2) AS
2054 -- Index Certificate
2055
2056 /*
2057 INDX,QP_Resolve_Incompatability_PVTRUN.best_price_for_phase.incomp_cur,qp_npreq_ldets_tmp_N1,LINE_INDEX,1
2058 INDX,QP_Resolve_Incompatability_PVTRUN.best_price_for_phase.incomp_cur,qp_npreq_ldets_tmp_N1,PRICING_STATUS_CODE,2
2059 INDX,QP_Resolve_Incompatability_PVTRUN.best_price_for_phase.incomp_cur,qp_npreq_ldets_tmp_N1,PRICING_PHASE_ID,3
2060 */
2061
2062 CURSOR incomp_cur(p_manual_dis_flag VARCHAR2) IS
2063 SELECT DISTINCT INCOMPATABILITY_GRP_CODE
2064 FROM qp_npreq_ldets_tmp
2065 WHERE PRICING_PHASE_ID = p_pricing_phase_id
2066 AND INCOMPATABILITY_GRP_CODE IS NOT NULL
2067 AND LINE_INDEX = p_line_index
2068 AND ((AUTOMATIC_FLAG = QP_PREQ_GRP.G_YES AND p_manual_dis_flag = QP_PREQ_GRP.G_YES) OR
2069 p_manual_dis_flag = QP_PREQ_GRP.G_NO)
2070 AND PRICING_STATUS_CODE = QP_PREQ_GRP.G_STATUS_NEW;
2071
2072
2073 x_list_line_id NUMBER;
2074 v_return_status VARCHAR2(30) := FND_API.G_RET_STS_SUCCESS;
2075 x_ret_status VARCHAR2(30);
2076 v_excl_flag BOOLEAN := FALSE;
2077 v_manual_dis_flag VARCHAR2(1) := nvl(QP_PREQ_GRP.G_MANUAL_DISCOUNT_FLAG,'Y');
2078
2079 INVALID_BEST_PRICE EXCEPTION;
2080
2081 v_routine_name CONSTANT VARCHAR2(240) := 'Routine:QP_Resolve_Incompatability_PVTRUN.Best_Price_For_Phase';
2082
2083 BEGIN
2084 l_debug := QP_PREQ_GRP.G_DEBUG_ENGINE;
2085
2086 --[julin/4116856] moved deletion of un-asked-for promotions to QP_PREQ_GRP
2087
2088 /* UPDATE qp_npreq_line_attrs_tmp a
2089 SET a.PRICING_STATUS_CODE = QP_PREQ_GRP.G_STATUS_BEST_PRICE_EVAL
2090 WHERE a.LIST_LINE_ID IN (SELECT b.CREATED_FROM_LIST_LINE_ID
2091 FROM qp_npreq_ldets_tmp b
2092 WHERE b.PRICING_STATUS_CODE = QP_PREQ_GRP.G_STATUS_BEST_PRICE_EVAL
2093 AND b.PRICING_PHASE_ID = p_pricing_phase_id
2094 AND b.ASK_FOR_FLAG = QP_PREQ_GRP.G_YES
2095 AND b.LINE_INDEX = p_line_index)
2096 AND a.LINE_INDEX = p_line_index; */
2097
2098 FOR i IN incomp_cur(v_manual_dis_flag)
2099 LOOP
2100 IF (v_excl_flag = FALSE) THEN
2101 -- Best Price Evaluation
2102 IF l_debug = FND_API.G_TRUE THEN
2103 QP_PREQ_GRP.engine_debug('Best Price For Phase .....');
2104 END IF;
2105 Best_Price_Evaluation(p_list_price,
2106 p_line_index,
2107 p_pricing_phase_id,
2108 i.INCOMPATABILITY_GRP_CODE,
2109 NULL, -- Added for bug#2661540
2110 v_manual_dis_flag,
2111 x_list_line_id,
2112 x_ret_status);
2113 IF (x_ret_status = FND_API.G_RET_STS_ERROR) THEN
2114 RAISE INVALID_BEST_PRICE;
2115 END IF;
2116 END IF;
2117 -- Incomp_grp_id ='EXCL' has the highest priority(exclusivity)
2118 IF (i.INCOMPATABILITY_GRP_CODE = QP_PREQ_GRP.G_INCOMP_EXCLUSIVE) THEN
2119 v_excl_flag := TRUE;
2120 END IF;
2121 IF (v_excl_flag = FALSE) THEN
2122 Delete_Incompatible_Lines(p_pricing_phase_id,
2123 p_line_index,
2124 i.INCOMPATABILITY_GRP_CODE,
2125 x_list_line_id,
2126 v_excl_flag,
2127 v_manual_dis_flag,
2128 x_ret_status);
2129
2130 END IF;
2131 END LOOP;
2132 IF(v_excl_flag = TRUE) THEN
2133 Delete_Incompatible_Lines(p_pricing_phase_id,
2134 p_line_index,
2135 NULL, -- incomp_grp_id
2136 x_list_line_id,
2137 v_excl_flag,
2138 v_manual_dis_flag,
2139 x_ret_status);
2140
2141 END IF;
2142 EXCEPTION
2143 WHEN INVALID_BEST_PRICE THEN
2144 IF l_debug = FND_API.G_TRUE THEN
2145 QP_PREQ_GRP.engine_debug(v_routine_name || ' ' || SQLERRM);
2146 END IF;
2147 x_return_status_txt := v_routine_name || ' ' || SQLERRM;
2148 v_return_status := QP_PREQ_GRP.G_STATUS_BEST_PRICE_EVAL_ERROR;
2149 x_return_status := v_return_status;
2150 WHEN OTHERS THEN
2151 IF l_debug = FND_API.G_TRUE THEN
2152 QP_PREQ_GRP.engine_debug(v_routine_name || 'Unexpected Error');
2153 QP_PREQ_GRP.engine_debug(v_routine_name || ' ' || SQLERRM);
2154 END IF;
2155 v_return_status := FND_API.G_RET_STS_ERROR;
2156 x_return_status := v_return_status;
2157 END Best_Price_For_Phase;
2158
2159 PROCEDURE Resolve_Incompatability(p_pricing_phase_id NUMBER,
2160 p_processing_flag VARCHAR2,
2161 p_list_price NUMBER,
2162 p_line_index NUMBER,
2163 x_return_status OUT NOCOPY VARCHAR2,
2164 x_return_status_txt OUT NOCOPY VARCHAR2) AS
2165
2166 -- Index Certificate
2167
2168 /*
2169 INDX,QP_Resolve_Incompatability_PVTRUN.resolve_incompatability.incomp_cur,qp_npreq_ldets_tmp_N1,LINE_INDEX,1
2170 INDX,QP_Resolve_Incompatability_PVTRUN.resolve_incompatability.incomp_cur,qp_npreq_ldets_tmp_N1,PRICING_STATUS_CODE,2
2171 INDX,QP_Resolve_Incompatability_PVTRUN.resolve_incompatability.incomp_cur,qp_npreq_ldets_tmp_N1,PRICING_PHASE_ID,3
2172 */
2173
2174 CURSOR incomp_cur(l_manual_dis_flag VARCHAR2) IS
2175 SELECT DISTINCT INCOMPATABILITY_GRP_CODE , PRICING_STATUS_CODE
2176 FROM qp_npreq_ldets_tmp
2177 WHERE PRICING_PHASE_ID = p_pricing_phase_id
2178 AND INCOMPATABILITY_GRP_CODE IS NOT NULL
2179 AND PRICING_STATUS_CODE = QP_PREQ_GRP.G_STATUS_NEW
2180 AND ((AUTOMATIC_FLAG = QP_PREQ_GRP.G_YES AND l_manual_dis_flag = QP_PREQ_GRP.G_YES) OR
2181 l_manual_dis_flag = QP_PREQ_GRP.G_NO)
2182 AND LINE_INDEX = p_line_index;
2183
2184 /*
2185 INDX,QP_Resolve_Incompatability_PVTRUN.resolve_incompatability.each_incomp_cur,qp_npreq_ldets_tmp_N3,LINE_INDEX,1
2186 INDX,QP_Resolve_Incompatability_PVTRUN.resolve_incompatability.each_incomp_cur,qp_npreq_ldets_tmp_N3,PRICING_PHASE_ID,2
2187 INDX,QP_Resolve_Incompatability_PVTRUN.resolve_incompatability.each_incomp_cur,qp_npreq_ldets_tmp_N3,ASK_FOR_FLAG,3
2188
2189 INDX,QP_Resolve_Incompatability_PVTRUN.resolve_incompatability.each_incomp_cur,qp_npreq_line_attrs_tmp_N2,PRICING_STATUS_CODE,1
2190 INDX,QP_Resolve_Incompatability_PVTRUN.resolve_incompatability.each_incomp_cur,qp_npreq_line_attrs_tmp_N2,ATTRIBUTE_TYPE,2
2191 INDX,QP_Resolve_Incompatability_PVTRUN.resolve_incompatability.each_incomp_cur,qp_npreq_line_attrs_tmp_N2,CONTEXT,3
2192 INDX,QP_Resolve_Incompatability_PVTRUN.resolve_incompatability.each_incomp_cur,qp_npreq_line_attrs_tmp_N2,ATTRIBUTE,4
2193 INDX,QP_Resolve_Incompatability_PVTRUN.resolve_incompatability.each_incomp_cur,qp_npreq_line_attrs_tmp_N2,LINE_INDEX,5
2194 INDX,QP_Resolve_Incompatability_PVTRUN.resolve_incompatability.each_incomp_cur,qp_npreq_line_attrs_tmp_N2,VALUE_FROM,6
2195 */
2196
2197 --UNION
2198
2199 /*
2200 INDX,QP_Resolve_Incompatability_PVTRUN.resolve_incompatability.each_incomp_cur,qp_npreq_ldets_tmp_N3,LINE_INDEX,1
2201 INDX,QP_Resolve_Incompatability_PVTRUN.resolve_incompatability.each_incomp_cur,qp_npreq_ldets_tmp_N3,PRICING_PHASE_ID,2
2202 INDX,QP_Resolve_Incompatability_PVTRUN.resolve_incompatability.each_incomp_cur,qp_npreq_ldets_tmp_N3,ASK_FOR_FLAG,3
2203 */
2204
2205 CURSOR each_incomp_cur(p_incomp_grp_id VARCHAR2,l_manual_dis_flag VARCHAR2) IS
2206 SELECT a.CREATED_FROM_LIST_HEADER_ID,a.CREATED_FROM_LIST_LINE_ID,a.INCOMPATABILITY_GRP_CODE,a.ASK_FOR_FLAG
2207 FROM qp_npreq_ldets_tmp a
2208 WHERE EXISTS (SELECT 'X'
2209 FROM qp_npreq_line_attrs_tmp b
2210 WHERE a.LINE_INDEX = b.LINE_INDEX
2211 AND b.ATTRIBUTE_TYPE = QP_PREQ_GRP.G_QUALIFIER_TYPE
2212 AND b.PRICING_STATUS_CODE = QP_PREQ_GRP.G_STATUS_UNCHANGED
2213 AND b.ATTRIBUTE IN (QP_PREQ_GRP.G_QUAL_ATTRIBUTE1,
2214 QP_PREQ_GRP.G_QUAL_ATTRIBUTE2,
2215 QP_PREQ_GRP.G_QUAL_ATTRIBUTE6)
2216 AND b.CONTEXT = QP_PREQ_GRP.G_LIST_HEADER_CONTEXT
2217 AND b.VALUE_FROM = decode(b.ATTRIBUTE,
2218 QP_PREQ_GRP.G_QUAL_ATTRIBUTE1,to_char(a.CREATED_FROM_LIST_HEADER_ID),
2219 QP_PREQ_GRP.G_QUAL_ATTRIBUTE2,to_char(a.CREATED_FROM_LIST_LINE_ID),
2220 QP_PREQ_GRP.G_QUAL_ATTRIBUTE6,to_char(a.CREATED_FROM_LIST_HEADER_ID)))
2221 AND a.ASK_FOR_FLAG = QP_PREQ_GRP.G_YES
2222 AND a.INCOMPATABILITY_GRP_CODE = p_incomp_grp_id
2223 AND a.PRICING_PHASE_ID = p_pricing_phase_id
2224 AND a.LINE_INDEX = p_line_index
2225 AND a.LINE_DETAIL_TYPE_CODE <> QP_PREQ_GRP.G_CHILD_DETAIL_TYPE
2226 AND a.PRICING_STATUS_CODE = QP_PREQ_GRP.G_STATUS_NEW
2227 AND ((a.AUTOMATIC_FLAG = QP_PREQ_GRP.G_YES AND l_manual_dis_flag = QP_PREQ_GRP.G_YES) OR
2228 l_manual_dis_flag = QP_PREQ_GRP.G_NO)
2229 UNION
2230 SELECT a.CREATED_FROM_LIST_HEADER_ID,a.CREATED_FROM_LIST_LINE_ID,a.INCOMPATABILITY_GRP_CODE,'N' ASK_FOR_FLAG
2231 FROM qp_npreq_ldets_tmp a
2232 WHERE a.ASK_FOR_FLAG = QP_PREQ_GRP.G_NO -- Removed NVL , expect some issues
2233 AND a.INCOMPATABILITY_GRP_CODE = p_incomp_grp_id
2234 AND a.PRICING_PHASE_ID = p_pricing_phase_id
2235 AND a.LINE_INDEX = p_line_index
2236 AND a.LINE_DETAIL_TYPE_CODE <> QP_PREQ_GRP.G_CHILD_DETAIL_TYPE
2237 AND a.PRICING_STATUS_CODE = QP_PREQ_GRP.G_STATUS_NEW
2238 AND ((a.AUTOMATIC_FLAG = QP_PREQ_GRP.G_YES AND l_manual_dis_flag = QP_PREQ_GRP.G_YES) OR
2239 l_manual_dis_flag = QP_PREQ_GRP.G_NO)
2240 ORDER BY 4 desc;
2241
2242 v_this_is_the_list_line_id NUMBER;
2243 v_first_list_line_id NUMBER;
2244 v_count NUMBER:= 0;
2245 v_others_flag BOOLEAN:= TRUE;
2246 v_high_precedence NUMBER;
2247 v_ask_for_flag VARCHAR2(1);
2248 v_excl_flag BOOLEAN := FALSE;
2249 v_excl_list_line_id NUMBER;
2250 x_best_list_line_id NUMBER;
2251 x_ret_status VARCHAR2(30);
2252 v_return_status VARCHAR2(30) := FND_API.G_RET_STS_SUCCESS;
2253 v_routine_name CONSTANT VARCHAR2(240):='Routine:QP_Resolve_Incompatability_PVTRUN.Resolve_Incompatability';
2254 p_manual_dis_flag VARCHAR2(1) := nvl(QP_PREQ_GRP.G_MANUAL_DISCOUNT_FLAG,'Y');
2255 v_ask_for_constant CONSTANT NUMBER := -100000;
2256
2257
2258 l_precedence_tbl precedence_tbl_type;
2259
2260 v_precedence NUMBER;
2261 v_counter NUMBER := 0;
2262
2263 INVALID_INCOMPATIBILITY EXCEPTION;
2264 INVALID_BEST_PRICE EXCEPTION;
2265
2266 BEGIN
2267 l_debug := QP_PREQ_GRP.G_DEBUG_ENGINE;
2268 IF l_debug = FND_API.G_TRUE THEN
2269 QP_PREQ_GRP.engine_debug ('S1');
2270
2271 END IF;
2272
2273 -- [julin/4116856] moved deletion of un-asked-for promotions to QP_PREQ_GRP
2274
2275
2276 /* UPDATE qp_npreq_line_attrs_tmp a
2277 SET a.PRICING_STATUS_CODE = QP_PREQ_GRP.G_STATUS_INCOMP_LOGIC
2278 WHERE a.LIST_LINE_ID IN (SELECT b.CREATED_FROM_LIST_LINE_ID
2279 FROM qp_npreq_ldets_tmp b
2280 WHERE b.PRICING_STATUS_CODE = QP_PREQ_GRP.G_STATUS_INCOMP_LOGIC
2281 AND b.PRICING_PHASE_ID = p_pricing_phase_id
2282 AND b.ASK_FOR_FLAG = QP_PREQ_GRP.G_YES
2283 AND b.LINE_INDEX = p_line_index)
2284 AND a.LINE_INDEX = p_line_index; */
2285
2286
2287 FOR i IN incomp_cur(p_manual_dis_flag)
2288 LOOP
2289 IF l_debug = FND_API.G_TRUE THEN
2290 QP_PREQ_GRP.engine_debug ('#2');
2291
2292 END IF;
2293 l_precedence_tbl.delete;
2294 v_counter := 0;
2295
2296 FOR j IN each_incomp_cur(i.INCOMPATABILITY_GRP_CODE,p_manual_dis_flag)
2297 LOOP
2298 v_precedence := Precedence_For_List_Line(j.CREATED_FROM_LIST_HEADER_ID,j.CREATED_FROM_LIST_LINE_ID,
2299 i.incompatability_grp_code, p_line_index,p_pricing_phase_id);
2300 v_counter := v_counter + 1;
2301 l_precedence_tbl(v_counter).created_from_list_line_id := j.created_from_list_line_id;
2302 l_precedence_tbl(v_counter).incompatability_grp_code := j.incompatability_grp_code;
2303 l_precedence_tbl(v_counter).ask_for_flag := j.ask_for_flag;
2304 l_precedence_tbl(v_counter).original_precedence := v_precedence;
2305 IF (j.ask_for_flag = QP_PREQ_GRP.G_YES) THEN
2306 v_precedence := v_precedence + v_ask_for_constant;
2307 END IF;
2308 l_precedence_tbl(v_counter).product_precedence := v_precedence;
2309 END LOOP;
2310
2311 -- Sort the table
2312 IF (l_precedence_tbl.COUNT > 0 ) THEN
2313 --sort_on_precedence(l_precedence_tbl, l_precedence_tbl.FIRST, l_precedence_tbl.LAST);
2314 sort_on_precedence(l_precedence_tbl);
2315 END IF;
2316
2317 IF (l_precedence_tbl.COUNT > 0) THEN
2318 FOR j IN l_precedence_tbl.FIRST .. l_precedence_tbl.LAST
2319 LOOP
2320 IF l_debug = FND_API.G_TRUE THEN
2321 QP_PREQ_GRP.engine_debug ('#3');
2322
2323 END IF;
2324 -- Store the first list_line_id
2325 IF (v_count = 0) THEN
2326 v_first_list_line_id := l_precedence_tbl(j).CREATED_FROM_LIST_LINE_ID;
2327 IF l_debug = FND_API.G_TRUE THEN
2328 QP_PREQ_GRP.engine_debug('The First List Line Id : ' || v_first_list_line_id);
2329 QP_PREQ_GRP.engine_debug('Pricing Status Code: ' || i.pricing_status_code);
2330 END IF;
2331 v_high_precedence := l_precedence_tbl(j).ORIGINAL_PRECEDENCE;
2332 v_ask_for_flag := l_precedence_tbl(j).ASK_FOR_FLAG;
2333 END IF;
2334 IF l_debug = FND_API.G_TRUE THEN
2335 QP_PREQ_GRP.engine_debug ('#4');
2336
2337 END IF;
2338 -- Incomp_grp_id ='EXCL' has the highest priority(exclusivity)
2339 IF (i.INCOMPATABILITY_GRP_CODE = QP_PREQ_GRP.G_INCOMP_EXCLUSIVE) THEN
2340 IF(v_count = 0) THEN
2341 v_this_is_the_list_line_id := l_precedence_tbl(j).CREATED_FROM_LIST_LINE_ID;
2342 v_excl_list_line_id := l_precedence_tbl(j).CREATED_FROM_LIST_LINE_ID;
2343 --v_others_flag := FALSE;
2344 v_excl_flag := TRUE;
2345 ELSE -- If there are multiple list lines in EXCL incomp
2346 IF (v_others_flag = TRUE) THEN
2347 IF (v_high_precedence = l_precedence_tbl(j).ORIGINAL_PRECEDENCE) THEN
2348 IF ((v_ask_for_flag = QP_PREQ_GRP.G_YES and l_precedence_tbl(j).ASK_FOR_FLAG = QP_PREQ_GRP.G_YES) OR
2349 (v_ask_for_flag = QP_PREQ_GRP.G_NO and l_precedence_tbl(j).ASK_FOR_FLAG = QP_PREQ_GRP.G_NO)) THEN
2350 -- Best Price Evaluation
2351 IF (p_processing_flag = QP_PREQ_GRP.G_DISCOUNT_PROCESSING) THEN
2352 IF l_debug = FND_API.G_TRUE THEN
2353 QP_PREQ_GRP.engine_debug ('Best Price Evaluation');
2354 END IF;
2355 IF (p_list_price IS NOT NULL) THEN
2356 Best_Price_Evaluation(p_list_price,
2357 p_line_index,
2358 p_pricing_phase_id,
2359 i.INCOMPATABILITY_GRP_CODE,
2360 v_high_precedence, -- Added for bug#2661540
2361 p_manual_dis_flag,
2362 x_best_list_line_id,
2363 x_ret_status);
2364 IF l_debug = FND_API.G_TRUE THEN
2365 QP_PREQ_GRP.engine_debug('Successful Best Price Eval');
2366 END IF;
2367 IF x_ret_status in (QP_PREQ_GRP.G_STATUS_BEST_PRICE_EVAL_ERROR,FND_API.G_RET_STS_ERROR) THEN
2368 RAISE INVALID_BEST_PRICE;
2369 END IF;
2370 v_this_is_the_list_line_id := x_best_list_line_id;
2371 v_excl_list_line_id := x_best_list_line_id;
2372 v_others_flag := FALSE;
2373 END IF;
2374 END IF;
2375 ELSIF (v_ask_for_flag=QP_PREQ_GRP.G_NO and l_precedence_tbl(j).ASK_FOR_FLAG = QP_PREQ_GRP.G_YES) THEN
2376 v_this_is_the_list_line_id := l_precedence_tbl(j).CREATED_FROM_LIST_LINE_ID;
2377 v_excl_list_line_id := l_precedence_tbl(j).CREATED_FROM_LIST_LINE_ID;
2378 v_others_flag := FALSE;
2379 END IF;
2380 ELSE
2381 IF (v_ask_for_flag = QP_PREQ_GRP.G_NO and l_precedence_tbl(j).ASK_FOR_FLAG = QP_PREQ_GRP.G_YES) THEN
2382 v_this_is_the_list_line_id := l_precedence_tbl(j).CREATED_FROM_LIST_LINE_ID;
2383 v_excl_list_line_id := l_precedence_tbl(j).CREATED_FROM_LIST_LINE_ID;
2384 v_others_flag := FALSE;
2385 END IF;
2386 END IF;
2387 END IF;
2388 END IF;
2389 END IF;
2390 IF l_debug = FND_API.G_TRUE THEN
2391 QP_PREQ_GRP.engine_debug ('#5');
2392
2393 END IF;
2394 IF (v_others_flag = TRUE and v_excl_flag = FALSE) THEN
2395 IF l_debug = FND_API.G_TRUE THEN
2396 QP_PREQ_GRP.engine_debug('Others_Flag: TRUE');
2397 END IF;
2398 null;
2399 ELSE
2400 IF l_debug = FND_API.G_TRUE THEN
2401 QP_PREQ_GRP.engine_debug('Others_Flag: FALSE');
2402 END IF;
2403 null;
2404 END IF;
2405 IF l_debug = FND_API.G_TRUE THEN
2406 QP_PREQ_GRP.engine_debug ('#6');
2407 QP_PREQ_GRP.engine_debug('Count: ' || v_count);
2408
2409 END IF;
2410 IF (v_others_flag = TRUE) THEN
2411 -- If it is an asked for promo
2412 IF (v_count > 0) THEN -- Do not compare the first time for the first record
2413 IF l_debug = FND_API.G_TRUE THEN
2414 QP_PREQ_GRP.engine_debug ('Precedence1:' || v_high_precedence);
2415 QP_PREQ_GRP.engine_debug ('Precedence2:' || l_precedence_tbl(j).ORIGINAL_PRECEDENCE);
2416 QP_PREQ_GRP.engine_debug ('Ask_For_Flag1:' || v_ask_for_flag);
2417 QP_PREQ_GRP.engine_debug ('Ask_For_FLag2:' || l_precedence_tbl(j).ASK_FOR_FLAG);
2418 QP_PREQ_GRP.engine_debug ('Second List Line Id:' || l_precedence_tbl(j).CREATED_FROM_LIST_LINE_ID);
2419 QP_PREQ_GRP.engine_debug ('#7');
2420 END IF;
2421 IF (v_high_precedence = l_precedence_tbl(j).ORIGINAL_PRECEDENCE) THEN
2422 IF ((v_ask_for_flag = QP_PREQ_GRP.G_YES and l_precedence_tbl(j).ASK_FOR_FLAG = QP_PREQ_GRP.G_YES) OR
2423 (v_ask_for_flag = QP_PREQ_GRP.G_NO and l_precedence_tbl(j).ASK_FOR_FLAG = QP_PREQ_GRP.G_NO)) THEN
2424 -- Best Price Evaluation
2425 IF (p_processing_flag = QP_PREQ_GRP.G_DISCOUNT_PROCESSING) THEN
2426 IF l_debug = FND_API.G_TRUE THEN
2427 QP_PREQ_GRP.engine_debug ('Best Price Evaluation');
2428 END IF;
2429 IF (p_list_price IS NOT NULL) THEN
2430 Best_Price_Evaluation(p_list_price,
2431 p_line_index,
2432 p_pricing_phase_id,
2433 i.INCOMPATABILITY_GRP_CODE,
2434 v_high_precedence, -- Added for bug#2661540
2435 p_manual_dis_flag,
2436 x_best_list_line_id,
2437 x_ret_status);
2438 IF l_debug = FND_API.G_TRUE THEN
2439 QP_PREQ_GRP.engine_debug('Successful Best Price Eval1');
2440 END IF;
2441 IF x_ret_status in (QP_PREQ_GRP.G_STATUS_BEST_PRICE_EVAL_ERROR,FND_API.G_RET_STS_ERROR) THEN
2442 RAISE INVALID_BEST_PRICE;
2443 END IF;
2444 v_this_is_the_list_line_id := x_best_list_line_id;
2445 v_others_flag := FALSE;
2446 END IF;
2447 ELSE -- PRICE_LIST
2448 RAISE INVALID_INCOMPATIBILITY;
2449 END IF;
2450 ELSIF (v_ask_for_flag=QP_PREQ_GRP.G_NO and l_precedence_tbl(j).ASK_FOR_FLAG = QP_PREQ_GRP.G_YES) THEN
2451 v_this_is_the_list_line_id := l_precedence_tbl(j).CREATED_FROM_LIST_LINE_ID;
2452 v_others_flag := FALSE;
2453 END IF;
2454 ELSE
2455 IF (v_ask_for_flag = QP_PREQ_GRP.G_NO and l_precedence_tbl(j).ASK_FOR_FLAG = QP_PREQ_GRP.G_YES) THEN
2456 v_this_is_the_list_line_id := l_precedence_tbl(j).CREATED_FROM_LIST_LINE_ID;
2457 v_others_flag := FALSE;
2458 END IF;
2459 END IF;
2460 END IF;
2461 END IF;
2462 IF l_debug = FND_API.G_TRUE THEN
2463 QP_PREQ_GRP.engine_debug ('#8');
2464 END IF;
2465 v_count := v_count + 1;
2466 END LOOP;
2467 END IF; -- l_precedence_tbl.COUNT > 0
2468
2469 v_count := 0; -- Reinit to 0 for an incomp grp id
2470 v_others_flag := TRUE;
2471 IF (v_this_is_the_list_line_id IS NOT NULL) THEN
2472 v_first_list_line_id := v_this_is_the_list_line_id;
2473 IF l_debug = FND_API.G_TRUE THEN
2474 QP_PREQ_GRP.engine_debug('The List Line Id : ' || v_first_list_line_id);
2475 END IF;
2476 END IF;
2477 v_this_is_the_list_line_id := null; -- Reset
2478
2479 IF l_debug = FND_API.G_TRUE THEN
2480 QP_PREQ_GRP.engine_debug('Before Update ......');
2481 QP_PREQ_GRP.engine_debug('Incomp Grp Code:' || i.INCOMPATABILITY_GRP_CODE);
2482 QP_PREQ_GRP.engine_debug('List Line Id:' || v_first_list_line_id);
2483 QP_PREQ_GRP.engine_debug('Pricing Phase Id:' || p_pricing_phase_id);
2484 QP_PREQ_GRP.engine_debug ('#9');
2485
2486 END IF;
2487 -- Update all the other list lines to status 'I' for each incomp grp
2488 IF (v_excl_flag = FALSE) THEN
2489 Delete_Incompatible_Lines(p_pricing_phase_id,
2490 p_line_index,
2491 i.INCOMPATABILITY_GRP_CODE,
2492 v_first_list_line_id,
2493 v_excl_flag,
2494 p_manual_dis_flag,
2495 x_ret_status);
2496 END IF;
2497 IF l_debug = FND_API.G_TRUE THEN
2498 QP_PREQ_GRP.engine_debug ('#10');
2499 END IF;
2500 END LOOP;
2501 IF (v_excl_flag = TRUE) THEN
2502 Delete_Incompatible_Lines(p_pricing_phase_id,
2503 p_line_index,
2504 NULL, -- incomp_grp_id
2505 v_excl_list_line_id,
2506 v_excl_flag,
2507 p_manual_dis_flag,
2508 x_ret_status);
2509 END IF;
2510 x_return_status := v_return_status; -- SUCCESS
2511 EXCEPTION
2512 WHEN INVALID_INCOMPATIBILITY THEN
2513 IF l_debug = FND_API.G_TRUE THEN
2514 QP_PREQ_GRP.engine_debug(v_routine_name || ' Multiple Price Lists cannot have same precedence
2515 and cannot be asked for');
2516 END IF;
2517 x_return_status_txt := v_routine_name || ' Multiple Price Lists cannot have same precedence
2518 and cannot be asked for';
2519 v_return_status := QP_PREQ_GRP.G_STATUS_INVALID_INCOMP;
2520 x_return_status := v_return_status;
2521 WHEN INVALID_BEST_PRICE THEN
2522 IF l_debug = FND_API.G_TRUE THEN
2523 QP_PREQ_GRP.engine_debug(v_routine_name || 'Best Price Evaluation Has Error');
2524 END IF;
2525 x_return_status_txt := v_routine_name || 'Best Price Evaluation Has Error';
2526 v_return_status := QP_PREQ_GRP.G_STATUS_BEST_PRICE_EVAL_ERROR;
2527 x_return_status := v_return_status;
2528 WHEN OTHERS THEN
2529 IF l_debug = FND_API.G_TRUE THEN
2530 QP_PREQ_GRP.engine_debug(v_routine_name || ' ' || SQLERRM);
2531 END IF;
2532 x_return_status_txt := v_routine_name || ' ' || SQLERRM;
2533 v_return_status := FND_API.G_RET_STS_ERROR;
2534 x_return_status := v_return_status;
2535 END Resolve_Incompatability;
2536
2537 END QP_Resolve_Incompatability_PVT ;
[Home] [Help]