1 PACKAGE BODY QP_Delayed_Requests_UTIL AS
2 /* $Header: QPXUREQB.pls 120.18.12020000.3 2012/08/28 09:08:01 dnema ship $ */
3
4 -- Global constant holding the package name
5
6 G_PKG_NAME CONSTANT VARCHAR2(30) := 'QP_Delayed_Requests_UTIL';
7
8
9 Procedure Check_For_Duplicate_Qualifiers
10 ( x_return_status OUT NOCOPY Varchar2
11 , p_qualifier_rule_id IN NUMBER
12 ) IS
13 l_status VARCHAR2(20);
14 DUPLICATE_DISCOUNT EXCEPTION;
15 Cursor C_Qualifiers(p_qualifier_rule_id number) IS
16 Select 'DUPLICATE'
17 from qp_qualifiers a , qp_qualifiers b
18 where a.qualifier_rule_id = b.qualifier_rule_id
19 and a.qualifier_rule_id = p_qualifier_rule_id
20 and a.qualifier_grouping_no = b.qualifier_grouping_no
21 and a.qualifier_context = b.qualifier_context
22 and a. qualifier_attribute = b.qualifier_attribute
23 and a.qualifier_id <> b.qualifier_id;
24
25 BEGIN
26
27 oe_debug_pub.add('Entering QP_DELAYED_REQUESTS_UTIL.check dup');
28 oe_debug_pub.add('passed rule id is'||p_qualifier_rule_id);
29 --dbms_output.put_line('Entering QP_DELAYED_REQUESTS_UTIL.check dup');
30 --dbms_output.put_line('passed rule id is'||p_qualifier_rule_id);
31
32 x_return_status := FND_API.G_RET_STS_SUCCESS;
33 open C_Qualifiers(p_qualifier_rule_id);
34 fetch C_Qualifiers into l_status;
35 close C_Qualifiers;
36
37 --dbms_output.put_line('status is '||l_status);
38 If l_status = 'DUPLICATE' Then
39
40 oe_debug_pub.add('status is duplicate');
41 --dbms_output.put_line('status is duplicate');
42 x_return_status := FND_API.G_RET_STS_ERROR;
43
44 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR)
45 THEN
46
47 fnd_message.set_name('ONT', 'OE_DIS_DUPLICATE_LIN_DISC');
48 OE_MSG_PUB.Add;
49 END IF;
50 RAISE DUPLICATE_DISCOUNT;
51 END IF;
52
53 oe_debug_pub.add('Exiting QP_DELAYED_REQUESTS_UTIL.checkdup');
54 --dbms_output.put_line('Exiting QP_DELAYED_REQUESTS_UTIL.checkdup');
55
56 EXCEPTION
57 WHEN DUPLICATE_DISCOUNT
58 THEN
59 x_return_status := FND_API.G_RET_STS_ERROR;
60
61 WHEN FND_API.G_EXC_ERROR THEN
62
63 x_return_status := FND_API.G_RET_STS_ERROR;
64
65 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
66
67 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
68
69 WHEN OTHERS THEN
70
71 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
72
73 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
74 THEN
75 OE_MSG_PUB.Add_Exc_Msg
76 ( G_PKG_NAME
77 , 'QP_Delayed_Requests_Util'
78 );
79 END IF;
80 END Check_For_Duplicate_Qualifiers;
81
82 Procedure Maintain_List_Header_Phases
83 ( p_List_Header_ID IN NUMBER
84 , x_return_status OUT NOCOPY VARCHAR2
85 ) IS
86
87 BEGIN
88
89 delete from qp_list_header_phases
90 where list_header_id = p_List_Header_ID;
91
92 /*
93 Bug - 8224336
94 Changes for Pattern Engine - added column PRIC_PROD_ATTR_ONLY_FLAG
95 Column PRIC_PROD_ATTR_ONLY_FLAG in table qp_list_header_phases will be -
96 'Y' - If all the lines in the header for that phase have only product or pricing or both attributes (but not qualifiers).
97 'N' - If atleast one line within that header or header itself has qualifiers attached, for that phase
98 */
99
100 insert into qp_list_header_phases
101 (list_header_id, pricing_phase_id,PRIC_PROD_ATTR_ONLY_FLAG)
102 (
103 select distinct list_header_id , pricing_phase_id,'N'
104 from qp_list_lines
105 where pricing_phase_id > 1
106 and qualification_ind in (2,6,8,10,12,14,22,28,30)
107 and list_header_id = p_List_Header_ID);
108
109
110 EXCEPTION
111
115
112 WHEN FND_API.G_EXC_ERROR THEN
113
114 x_return_status := FND_API.G_RET_STS_ERROR;
116 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
117
118 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
119
120 WHEN OTHERS THEN
121
122 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
123
124 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
125 THEN
126 OE_MSG_PUB.Add_Exc_Msg
127 ( G_PKG_NAME
128 , 'Maintain_List_Header_Phases');
129 END IF;
130
131 END Maintain_List_Header_Phases;
132
133 Procedure Update_Qualifier_Status(p_list_header_id in NUMBER,
134 p_active_flag in VARCHAR2,
135 x_return_status OUT NOCOPY VARCHAR2)
136 IS
137 BEGIN
138 x_return_status := FND_API.G_RET_STS_SUCCESS;
139
140 UPDATE qp_qualifiers
141 SET active_flag = p_active_flag
142 WHERE list_header_id = p_list_header_id;
143
144 IF p_active_flag = 'Y'
145 THEN
146 update qp_pte_segments set used_in_setup='Y'
147 where nvl(used_in_setup,'N')='N'
148 and segment_id in
149 (select a.segment_id
150 from qp_segments_b a, qp_prc_contexts_b b, qp_qualifiers c
151 where c.list_header_id = p_list_header_id
152 and a.segment_mapping_column = c.qualifier_attribute
153 and a.prc_context_id = b.prc_context_id
154 and b.prc_context_type = 'QUALIFIER'
155 and b.prc_context_code = c.qualifier_context);
156
157 update qp_pte_segments set used_in_setup='Y'
158 where nvl(used_in_setup,'N')='N'
159 and segment_id in
160 (select a.segment_id
161 from qp_segments_b a, qp_prc_contexts_b b, qp_pricing_attributes c
162 where c.list_header_id = p_list_header_id
163 and a.segment_mapping_column = c.pricing_attribute
164 and a.prc_context_id = b.prc_context_id
165 and b.prc_context_type = 'PRICING_ATTRIBUTE'
166 and b.prc_context_code = c.pricing_attribute_context);
167
168 update qp_pte_segments set used_in_setup='Y'
169 where nvl(used_in_setup,'N')='N'
170 and segment_id in
171 (select a.segment_id
172 from qp_segments_b a, qp_prc_contexts_b b, qp_pricing_attributes c
173 where c.list_header_id = p_list_header_id
174 and a.segment_mapping_column = c.product_attribute
175 and a.prc_context_id = b.prc_context_id
176 and b.prc_context_type = 'PRODUCT'
177 and b.prc_context_code = c.product_attribute_context);
178
179 END IF;
180
181 EXCEPTION
182 WHEN NO_DATA_FOUND THEN
183 x_return_status := FND_API.G_RET_STS_SUCCESS;
184 WHEN FND_API.G_EXC_ERROR THEN
185 x_return_status := FND_API.G_RET_STS_ERROR;
186 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
187 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
188 WHEN OTHERS THEN
189 x_return_status := FND_API.G_RET_STS_ERROR;
190 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
191 OE_MSG_PUB.Add_Exc_Msg
192 ( G_PKG_NAME
193 , 'QP_Delayed_Requests_Util');
194 END IF;
195 END Update_Qualifier_Status;
196
197 Procedure Create_Security_Privilege(p_list_header_id in NUMBER,
198 p_list_type_code in VARCHAR2,
199 x_return_status OUT NOCOPY VARCHAR2)
200 IS
201 x_result VARCHAR2(1);
202 BEGIN
203 x_return_status := FND_API.G_RET_STS_SUCCESS;
204
205 IF p_list_type_code = 'AGR' THEN
206 QP_security.create_default_grants( p_instance_type => QP_security.G_AGREEMENT_OBJECT,
207 p_instance_pk1 => p_list_header_id,
208 x_return_status => x_result);
209 ELSIF p_list_type_code = 'PRL' THEN
210 QP_security.create_default_grants( p_instance_type => QP_security.G_PRICELIST_OBJECT,
211 p_instance_pk1 => p_list_header_id,
212 x_return_status => x_result);
213 ELSE
214 QP_security.create_default_grants( p_instance_type => QP_security.G_MODIFIER_OBJECT,
215 p_instance_pk1 => p_list_header_id,
216 x_return_status => x_result);
217 END IF;
218
219 EXCEPTION
220 WHEN NO_DATA_FOUND THEN
221 x_return_status := FND_API.G_RET_STS_SUCCESS;
222 WHEN FND_API.G_EXC_ERROR THEN
223 x_return_status := FND_API.G_RET_STS_ERROR;
224 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
225 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
226 WHEN OTHERS THEN
227 x_return_status := FND_API.G_RET_STS_ERROR;
228 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
229 OE_MSG_PUB.Add_Exc_Msg
230 ( G_PKG_NAME
231 , 'QP_Delayed_Requests_Util');
232 END IF;
233 END Create_Security_Privilege;
234
235 Procedure Update_Attribute_Status(p_list_header_id in NUMBER,
236 p_list_line_id in NUMBER,
237 p_context_type in VARCHAR2,
238 p_context_code in VARCHAR2,
239 p_segment_mapping_column VARCHAR2,
240 x_return_status OUT NOCOPY VARCHAR2)
241 IS
242 l_check_active_flag VARCHAR2(1);
243 l_active_flag VARCHAR2(1);
244 BEGIN
245 x_return_status := FND_API.G_RET_STS_SUCCESS;
246 BEGIN
250 FROM QP_LIST_HEADERS_B
247 IF p_list_header_id IS NOT NULL THEN
248 SELECT ACTIVE_FLAG
249 INTO l_active_flag
251 WHERE list_header_id=p_list_header_id;
252 END IF;
253 IF p_list_line_id IS NOT NULL THEN
254 SELECT ListHeaders.ACTIVE_FLAG
255 INTO l_active_flag
256 FROM QP_LIST_HEADERS_B ListHeaders, QP_LIST_LINES ListLines
257 WHERE ListHeaders.LIST_HEADER_ID = ListLines.LIST_HEADER_ID AND
258 ListLines.LIST_LINE_ID = p_list_line_id AND
259 rownum = 1;
260 END IF;
261 EXCEPTION
262 WHEN NO_DATA_FOUND THEN
263 NULL;
264 END;
265
266 l_check_active_flag:=nvl(fnd_profile.value('QP_BUILD_ATTRIBUTES_MAPPING_OPTIONS'),'N');
267 IF (l_check_active_flag='N') OR (l_check_active_flag='Y' AND l_active_flag='Y') THEN
268 IF(p_context_code IS NOT NULL) AND
269 (p_segment_mapping_column IS NOT NULL) THEN
270 UPDATE qp_pte_segments set used_in_setup='Y'
271 WHERE nvl(used_in_setup,'N')='N'
272 AND segment_id IN
273 (SELECT a.segment_id FROM qp_segments_b a, qp_prc_contexts_b b
274 WHERE a.segment_mapping_column=p_segment_mapping_column
275 AND a.prc_context_id=b.prc_context_id
276 AND b.prc_context_type = p_context_type
277 AND b.prc_context_code=p_context_code);
278 END IF;
279 END IF;
280
281 EXCEPTION
282 WHEN NO_DATA_FOUND THEN
283 x_return_status := FND_API.G_RET_STS_SUCCESS;
284 WHEN FND_API.G_EXC_ERROR THEN
285 x_return_status := FND_API.G_RET_STS_ERROR;
286 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
287 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
288 WHEN OTHERS THEN
289 x_return_status := FND_API.G_RET_STS_ERROR;
290 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
291 OE_MSG_PUB.Add_Exc_Msg
292 ( G_PKG_NAME
293 , 'QP_Delayed_Requests_Util');
294 END IF;
295 END Update_Attribute_Status;
296
297
298 -- start bug2091362
299 Procedure Check_Duplicate_Modifier_Lines
300 ( p_Start_Date_Active IN DATE
301 , p_End_Date_Active IN DATE
302 , p_List_Line_ID IN NUMBER
303 , p_List_Header_ID IN NUMBER
304 , p_pricing_attribute_context IN VARCHAR2
305 , p_pricing_attribute IN VARCHAR2
306 , p_Pricing_attr_value IN VARCHAR2
307 , x_return_status OUT NOCOPY VARCHAR2
308 ) IS
309 l_status BOOLEAN := TRUE;
310 l_rows number := 0;
311 l_effdates boolean := FALSE;
312 BEGIN
313
314 oe_debug_pub.add('Entering QP_DELAYED_REQUESTS_UTIL.check_duplicate_modifier_lines');
315
316 x_return_status := FND_API.G_RET_STS_SUCCESS;
317
318 l_status := QP_VALIDATE_PRICING_ATTR.Mod_Dup(p_Start_Date_Active,
319 p_End_Date_Active,
320 p_List_Line_ID,
321 p_List_Header_ID,
322 p_pricing_attribute_context,
323 p_pricing_attribute,
324 p_pricing_attr_value,
325 l_rows,
326 l_effdates);
327
328 IF l_status = FALSE then
329
330
331 oe_debug_pub.add('Ren: check_duplicate_modifiers status is false ');
332
333 FND_MESSAGE.SET_NAME('QP','QP_DUPLICATE_MODIFIER_LINES');
334 oe_msg_pub.Add;
335
336 RAISE FND_API.G_EXC_ERROR;
337
338 END IF;
339
340
341
342 oe_debug_pub.add('Exiting QP_DELAYED_REQUESTS_UTIL.mod_dup');
343
344
345 EXCEPTION
346
347 WHEN FND_API.G_EXC_ERROR THEN
348
349 x_return_status := FND_API.G_RET_STS_ERROR;
350
351 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
352
353 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
354
355 WHEN OTHERS THEN
356
357 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
358
359 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
360 THEN
361 OE_MSG_PUB.Add_Exc_Msg
362 ( G_PKG_NAME
363 , 'Check_duplicate_Modifier_lines');
364 END IF;
365
366 END Check_Duplicate_Modifier_Lines;
367
368 -- end bug2091362
369
370
371 Procedure Check_Duplicate_List_Lines
372 ( p_Start_Date_Active IN DATE
373 , p_End_Date_Active IN DATE
374 , p_Revision IN VARCHAR2
375 , p_List_Line_ID IN NUMBER
376 , p_List_Header_ID IN NUMBER
377 , x_return_status OUT NOCOPY VARCHAR2
378 , x_dup_sdate OUT NOCOPY DATE
379 , x_dup_edate OUT NOCOPY DATE
380 ) IS
381 l_status BOOLEAN := TRUE;
382 l_rows number := 0;
383 l_revision boolean := FALSE;
384 l_effdates boolean := FALSE;
385 l_blank_text VARCHAR2(2000);
386 BEGIN
387
388 oe_debug_pub.add('Entering QP_DELAYED_REQUESTS_UTIL.check_duplicate_list_lines');
389
390 x_return_status := FND_API.G_RET_STS_SUCCESS;
391
392 l_status := QP_VALIDATE_PLL_PRICING_ATTR.Check_Dup_Pra(p_Start_Date_Active,
393 p_End_Date_Active,
394 p_Revision,
395 p_List_Line_ID,
396 p_List_Header_ID,
397 l_rows,
401 x_dup_edate);
398 l_revision,
399 l_effdates,
400 x_dup_sdate,
402
403 IF l_status = FALSE then
404
405 IF l_revision = FALSE then
406
407
408 oe_debug_pub.add('Ren: check_duplicate_lines status is false ');
409
410 FND_MESSAGE.SET_NAME('QP','QP_DUPLICATE_LIST_LINES');
411 oe_msg_pub.Add;
412
413 RAISE FND_API.G_EXC_ERROR;
414
415 ELSIF l_effdates = FALSE then
416
417
418 oe_debug_pub.add('Ren: check_duplicate_lines status is false ');
419
420 FND_MESSAGE.SET_NAME('QP', 'QP_BLANK');
421 l_blank_text := FND_MESSAGE.get;
422
423 FND_MESSAGE.SET_NAME('QP','QP_DUPLICATE_LIST_LINES_DATES');
424
425 IF x_dup_sdate IS NULL THEN
426 FND_MESSAGE.SET_TOKEN('STARTDATE', l_blank_text);
427 ELSE
428 FND_MESSAGE.SET_TOKEN('STARTDATE', x_dup_sdate);
429 END IF;
430
431 IF x_dup_edate IS NULL THEN
432 FND_MESSAGE.SET_TOKEN('ENDDATE', l_blank_text);
433 ELSE
434 FND_MESSAGE.SET_TOKEN('ENDDATE', x_dup_edate);
435 END IF;
436
437 oe_msg_pub.Add;
438
439 RAISE FND_API.G_EXC_ERROR;
440
441 ELSE
442
443 oe_debug_pub.add('Ren: check_duplicate_lines status is false ');
444
445 FND_MESSAGE.SET_NAME('QP','QP_DUPLICATE_LIST_LINES');
446 oe_msg_pub.Add;
447
448 RAISE FND_API.G_EXC_ERROR;
449
450 END IF;
451
452 END IF;
453
454 oe_debug_pub.add('Exiting QP_DELAYED_REQUESTS_UTIL.checkdup');
455
456
457 EXCEPTION
458
459 WHEN FND_API.G_EXC_ERROR THEN
460
461 x_return_status := FND_API.G_RET_STS_ERROR;
462
463 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
464
465 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
466
467 WHEN OTHERS THEN
468
469 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
470
471 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
472 THEN
473 OE_MSG_PUB.Add_Exc_Msg
474 ( G_PKG_NAME
475 , 'check_duplicate_list_lines');
476 END IF;
477
478 END Check_Duplicate_List_Lines;
479
480
481
482 --Procedure checks whether a price break line has 1 child break line.
483
484 Procedure Validate_Lines_For_Child
485 (x_return_status OUT NOCOPY Varchar2
486 ,p_list_line_type_code Varchar2
487 ,p_list_line_id IN NUMBER
488 )IS
489
490 l_status NUMBER;
491 l_modifier_grp_type varchar2(30) := 'NOVAL';
492
493 NO_CHILD_FOR_PBH_EXCEPTION EXCEPTION;
494 NO_CHILD_FOR_OID_EXCEPTION EXCEPTION;
495 NO_CHILD_FOR_PRG_EXCEPTION EXCEPTION;
496 NO_CHILD_EXCEPTION EXCEPTION;
497
498
499
500
501 Cursor C_pbh_children(p_list_line_id number
502 ,p_list_line_type_code varchar2
503 ,l_modifier_grp_type varchar2) IS
504 SELECT count(1)
505 FROM QP_LIST_LINES qll,
506 QP_RLTD_MODIFIERS qrm
507 WHERE qll.list_line_id = p_list_line_id
508 AND qll.list_line_id= qrm.from_rltd_modifier_id
509 --AND qrm.rltd_modifier_grp_type = 'PRICE BREAK'
510 AND qrm.rltd_modifier_grp_type = l_modifier_grp_type
511 AND qll.list_line_type_code = p_list_line_type_code;
512
513 CURSOR C_CHECK_FOR_PARENT(p_list_line_id number) IS
514 select 'FOUND' from
515 QP_LIST_LINES
516 WHERE LIST_LINE_ID = P_LIST_LINE_ID;
517
518 l_parent varchar2(30);
519
520 BEGIN
521
522 oe_debug_pub.add('Entering QP_DELAYED_REQUESTS_UTIL.validate_pbh_line');
523 oe_debug_pub.add('passed rule id is'||p_list_line_id);
524
525 --dbms_output.put_line('Entering QP_DELAYED_REQUESTS_UTIL.validate_pbh_line');
526 --dbms_output.put_line('passed rule id is'||p_list_line_id);
527
528
529 x_return_status := FND_API.G_RET_STS_SUCCESS;
530
531 open c_check_for_parent(p_list_line_id);
532 fetch c_check_for_parent into l_parent;
533 close c_check_for_parent;
534
535 oe_debug_pub.add('check parent for ' ||p_list_line_id);
536 oe_debug_pub.add('parent is ' ||l_parent);
537
538 If l_parent = 'FOUND' then
539
540 IF p_list_line_type_code = 'PBH' THEN
541
542 l_modifier_grp_type := 'PRICE BREAK';
543
544 ELSIF p_list_line_type_code IN( 'OID','PRG') THEN
545
546 l_modifier_grp_type := 'BENEFIT';
547
548 END IF;
549
550
551 open C_pbh_children(p_list_line_id,
552 p_list_line_type_code,
553 l_modifier_grp_type);
554 fetch C_pbh_children into l_status;
555 close C_pbh_children;
556
557 --dbms_output.put_line('status is '||l_status);
558
559 IF l_status < 1 then
560
561 --Raise MULTIPLE_BREAK_CHILD_EXCEPTION;
562
563 oe_debug_pub.add('status is more than 1');
564 --dbms_output.put_line('status is more than 1');
565 x_return_status := FND_API.G_RET_STS_ERROR;
566
567 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR)
568 THEN
572 ELSIF p_list_line_type_code IN ('OID' ,'PRG') THEN
569 IF p_list_line_type_code = 'PBH' THEN
570 fnd_message.set_name('QP', 'QP_NO_CHILD_FOR_PBH');
571 --OE_MSG_PUB.Add;
573 fnd_message.set_name('QP', 'QP_NO_CHILD_FOR_OID_PRG');
574 --OE_MSG_PUB.Add;
575 END IF;
576 OE_MSG_PUB.Add;
577 END IF;
578 RAISE NO_CHILD_EXCEPTION;
579 END IF;
580
581 oe_debug_pub.add('Exiting QP_DELAYED_REQUESTS_UTIL.validate pbh');
582 --dbms_output.put_line('Exiting QP_DELAYED_REQUESTS_UTIL.validate pbh');
583 else
584
585 null;
586 oe_debug_pub.add('Exiting no parent found');
587
588 END IF;
589
590 EXCEPTION
591 WHEN NO_CHILD_EXCEPTION
592 THEN
593 x_return_status := FND_API.G_RET_STS_ERROR;
594
595 WHEN FND_API.G_EXC_ERROR THEN
596
597 x_return_status := FND_API.G_RET_STS_ERROR;
598
599 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
600
601 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
602
603 WHEN NO_DATA_FOUND THEN
604 NULL;
605
606 WHEN OTHERS THEN
607
608 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
609
610 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
611 THEN
612 OE_MSG_PUB.Add_Exc_Msg
613 ( G_PKG_NAME
614 , 'QP_Delayed_Requests_Util'
615 );
616 END IF;
617
618 End Validate_Lines_For_Child;
619
620 -- This procedure checkes if there are overlapping breaks within
621 -- the child lines of PBH.
622
623 --Changed on APR 07 -svdeshmu
624
625
626
627 Procedure Check_For_Overlapping_breaks
628 ( x_return_status OUT NOCOPY Varchar2
629 , p_list_line_id IN NUMBER
630 ) IS
631
632 --Begin Bug No: 7321885
633 l_to_number NUMBER;
634 l_count NUMBER;
635 --End Bug No: 7321885
636
637 OVERLAPPING_BREAKS_EXCEPTION EXCEPTION;
638 -- mkarya for performance bug 1840060
639 -- Changed the cursor definition from view qp_price_breaks_v to base tables
640
641 --[prarasto] changed the cursor to revert to the validations for
642 --Non-continuous price breaks
643 Cursor C_break_lines(p_list_line_id number) IS
644 --Begin Bug No: 7321885
645 SELECT PRICING_ATTR_VALUE_FROM_NUMBER,
646 PRICING_ATTR_VALUE_TO_NUMBER
647 FROM QP_RLTD_MODIFIERS QRMA, QP_PRICING_ATTRIBUTES QPBVA
648 WHERE QRMA.FROM_RLTD_MODIFIER_ID = p_list_line_id AND
649 QRMA.TO_RLTD_MODIFIER_ID = QPBVA.LIST_LINE_ID
650 AND QRMA.RLTD_MODIFIER_GRP_TYPE = 'PRICE BREAK'
651 AND QPBVA.PRICING_ATTRIBUTE_DATATYPE = 'N'
652 ORDER BY QPBVA.PRICING_ATTR_VALUE_FROM_NUMBER;
653 --End Bug No: 7321885
654
655
656 BEGIN
657 x_return_status := FND_API.G_RET_STS_SUCCESS;
658 --Begin Bug No: 7321885
659 l_count := 0;
660 l_to_number := 0;
661 --End Bug No: 7321885
662
663
664 oe_debug_pub.add('Before overlapping breaks select stmt');
665
666 --Begin Bug No: 7321885
667 for l_break_lines_rec in C_break_lines(p_list_line_id)
668 LOOP
669 l_count := l_count + 1;
670 if l_break_lines_rec.pricing_attr_value_to_number < l_break_lines_rec.pricing_attr_value_from_number --changes for value from = value to bug#9210448
671 then
672 x_return_status := FND_API.G_RET_STS_ERROR;
673 end if;
674 if (l_count > 1) and ( l_break_lines_rec.pricing_attr_value_from_number <= l_to_number)
675 then
676 x_return_status := FND_API.G_RET_STS_ERROR;
677 end if;
678 l_to_number := l_break_lines_rec.pricing_attr_value_to_number;
679 END LOOP;
680 --End Bug No: 7321885
681
682 oe_debug_pub.add('After overlapping breaks select stmt');
683 --dbms_output.put_line('status is '||l_status);
684
685 --Begin Bug No: 7321885
686 IF x_return_status = FND_API.G_RET_STS_ERROR then
687 --End Bug No: 7321885
688
689 --Raise OVERLAPPING_BREAKS_EXCEPTION;
690
691 /* x_return_status := FND_API.G_RET_STS_ERROR; */ --Bug No: 7321885
692
693 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR)
694 THEN
695
696 fnd_message.set_name('QP', 'QP_OVERLAP_PRICE_BREAK_RANGE');
697 OE_MSG_PUB.Add;
698 END IF;
699 Raise OVERLAPPING_BREAKS_EXCEPTION;
700 END IF;
701
702 oe_debug_pub.add('Exiting QP_DELAYED_REQUESTS_UTIL.overlapping breaks');
703 --dbms_output.put_line('Exiting QP_DELAYED_REQUESTS_UTIL.overlapping breaks');
704
705 EXCEPTION
706 WHEN NO_DATA_FOUND THEN
707 NULL;
708
709 WHEN OVERLAPPING_BREAKS_EXCEPTION
710 THEN
711 x_return_status := FND_API.G_RET_STS_ERROR;
712
713 WHEN FND_API.G_EXC_ERROR THEN
714
715 x_return_status := FND_API.G_RET_STS_ERROR;
716
717 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
718
719 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
720
721 WHEN OTHERS THEN
722
723 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
724
725 oe_debug_pub.add('overlapping breaks error '||substr(sqlerrm,1,100));
726 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
727 THEN
728 OE_MSG_PUB.Add_Exc_Msg
732 END IF;
729 ( G_PKG_NAME
730 , 'QP_Delayed_Requests_Util'
731 );
733 End Check_For_Overlapping_breaks;
734
735
736 -- This procedure validates continuous price breaks.
737 Procedure Check_Continuous_Price_Breaks
738 ( x_return_status OUT NOCOPY Varchar2
739 , p_list_line_id IN NUMBER
740 ) IS
741 l_status VARCHAR2(30) := NULL;
742 l_break_count NUMBER := 0;
743 l_old_value_from NUMBER := NULL;
744 l_old_value_to NUMBER := NULL;
745
746 CONTINUOUS_BREAKS_EXCEPTION EXCEPTION;
747
748 Cursor c_break_lines_attr_values(p_list_line_id number) IS
749 SELECT qpa.PRICING_ATTR_VALUE_FROM,
750 qpa.PRICING_ATTR_VALUE_TO
751 from qp_list_lines ql, qp_pricing_attributes qpa,qp_rltd_modifiers qrm
752 WHERE ql.list_line_id = qpa.list_line_id
753 and ql.list_line_type_code IN ('SUR', 'DIS', 'PLL')
754 and qrm.to_rltd_modifier_id = ql.list_line_id
755 and qrm.rltd_modifier_grp_type = 'PRICE BREAK'
756 and qrm.from_rltd_modifier_id = p_list_line_id
757 order by qp_number.canonical_to_number(qpa.PRICING_ATTR_VALUE_FROM);
758
759 BEGIN
760 x_return_status := FND_API.G_RET_STS_SUCCESS;
761
762 oe_debug_pub.add('Before continuous breaks loop');
763
764 FOR c_break_lines_attr_val_rec in c_break_lines_attr_values(p_list_line_id)
765 LOOP
766
767 IF qp_number.canonical_to_number(c_break_lines_attr_val_rec.PRICING_ATTR_VALUE_FROM) >=
768 qp_number.canonical_to_number(c_break_lines_attr_val_rec.PRICING_ATTR_VALUE_TO)
769 THEN
770 l_status := 'FROM_NOT_LESS_THAN_TO';
771 EXIT;
772 END IF;
773
774 IF l_break_count = 0 THEN
775 IF qp_number.canonical_to_number(c_break_lines_attr_val_rec.PRICING_ATTR_VALUE_FROM) <> 0
776 THEN
777 l_status := 'NON_ZERO_FIRST_VALUE';
778 EXIT;
779 END IF;
780 ELSE
781 IF qp_number.canonical_to_number(c_break_lines_attr_val_rec.PRICING_ATTR_VALUE_FROM) > l_old_value_to
782 THEN
783 l_status := 'GAP';
784 EXIT;
785 ELSIF qp_number.canonical_to_number(c_break_lines_attr_val_rec.PRICING_ATTR_VALUE_FROM) < l_old_value_to
786 THEN
787 l_status := 'OVERLAP';
788 EXIT;
789 END IF;
790 END IF;
791
792 l_old_value_from := qp_number.canonical_to_number(c_break_lines_attr_val_rec.PRICING_ATTR_VALUE_FROM);
793 l_old_value_to := qp_number.canonical_to_number(c_break_lines_attr_val_rec.PRICING_ATTR_VALUE_TO);
794
795
796 l_break_count := l_break_count+1;
797 END LOOP;
798 oe_debug_pub.add('After continuous breaks loop');
799
800 IF l_status IS NOT NULL THEN
801 x_return_status := FND_API.G_RET_STS_ERROR;
802
803 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR)
804 THEN
805 IF l_status = 'FROM_NOT_LESS_THAN_TO' THEN
806 fnd_message.set_name('QP', 'QP_INCORRECT_BREAK_VALUES');
807 ELSIF l_status = 'NON_ZERO_FIRST_VALUE' THEN
808 fnd_message.set_name('QP', 'QP_NON_ZERO_BREAK_VALUE');
809 ELSIF l_status = 'GAP' THEN
810 fnd_message.set_name('QP', 'QP_PRICE_BREAKS_GAP');
811 ELSIF l_status = 'OVERLAP' THEN
812 fnd_message.set_name('QP', 'QP_OVERLAP_PRICE_BREAK_RANGE');
813 END IF;
814
815 OE_MSG_PUB.Add;
816 Raise CONTINUOUS_BREAKS_EXCEPTION;
817 END IF;
818 END IF;
819
820 oe_debug_pub.add('Exiting QP_DELAYED_REQUESTS_UTIL.Check_Continuous_Price_Breaks');
821
822 EXCEPTION
823 WHEN NO_DATA_FOUND THEN
824 NULL;
825
826 WHEN CONTINUOUS_BREAKS_EXCEPTION
827 THEN
828 x_return_status := FND_API.G_RET_STS_ERROR;
829
830 WHEN FND_API.G_EXC_ERROR THEN
831
832 x_return_status := FND_API.G_RET_STS_ERROR;
833
834 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
835
836 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
837
838 WHEN OTHERS THEN
839
840 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
841
842 oe_debug_pub.add('continuous breaks error '||substr(sqlerrm,1,100));
843 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
844 THEN
845 OE_MSG_PUB.Add_Exc_Msg
846 ( G_PKG_NAME
847 , 'QP_Delayed_Requests_Util'
848 );
849 END IF;
850 End Check_Continuous_Price_Breaks;
851
852
853 -- Upgrades non-continuous Price Breaks to continuous Price Breaks
854 Procedure Upgrade_Price_Breaks
855 ( x_return_status OUT NOCOPY Varchar2
856 , p_pbh_id IN NUMBER
857 , p_list_line_no IN VARCHAR2
858 , p_product_attribute IN VARCHAR2
859 , p_product_attr_value IN VARCHAR2
860 , p_list_type IN VARCHAR2
861 , p_start_date_active IN VARCHAR2
862 , p_end_date_active IN VARCHAR2)
863 IS
864 Cursor c_break_lines_attr_values(p_list_line_id number) IS
865 SELECT qpa.PRICING_ATTR_VALUE_FROM_NUMBER,
866 qpa.PRICING_ATTR_VALUE_FROM,
867 qpa.PRICING_ATTR_VALUE_TO_NUMBER,
868 qpa.PRICING_ATTR_VALUE_TO
869 from qp_list_lines ql,qp_pricing_attributes qpa,qp_rltd_modifiers qrm
870 WHERE ql.list_line_id = qpa.list_line_id
871 and ql.list_line_type_code IN ('SUR', 'DIS', 'PLL')
872 and qrm.to_rltd_modifier_id = ql.list_line_id
873 and qrm.rltd_modifier_grp_type = 'PRICE BREAK'
877
874 and qrm.from_rltd_modifier_id = p_list_line_id
875 order by 1
876 FOR UPDATE OF PRICING_ATTR_VALUE_FROM,PRICING_ATTR_VALUE_FROM_NUMBER;
878 l_old_value_to VARCHAR2(240);
879 l_old_value_to_number NUMBER;
880 l_prc_attr_val_from VARCHAR2(240);
881 l_prc_attr_val_from_number NUMBER;
882 l_first_break BOOLEAN := true;
883 l_prod_attr_val_disp VARCHAR2(4000);
884 UPGRADE_PRICE_BREAKS_EXCEPTION EXCEPTION;
885
886 BEGIN
887 x_return_status := FND_API.G_RET_STS_SUCCESS;
888
889 l_prod_attr_val_disp := QP_PRICE_LIST_LINE_UTIL.Get_Product_Value('QP_ATTR_DEFNS_PRICING',
890 'ITEM',
891 p_product_attribute,
892 p_product_attr_value);
893
894 fnd_file.put_line(FND_FILE.LOG,'>>>Upgrading breaks for: ');
895
896 IF p_list_type = 'MODIFIER' THEN
897 fnd_file.put_line(FND_FILE.LOG,'Modifier Line No : '||p_list_line_no);
898 END IF;
899
900 fnd_file.put_line(FND_FILE.LOG,'Product Attribute Value : '||l_prod_attr_val_disp);
901 fnd_file.put_line(FND_FILE.LOG,'Start Date Active : '||p_start_date_active);
902 fnd_file.put_line(FND_FILE.LOG,'End Date Active : '||p_end_date_active);
903 fnd_file.put_line(FND_FILE.LOG,' Old Breaks | New Breaks');
904 fnd_file.put_line(FND_FILE.LOG,rpad(lpad('Value From',20,' '),25,' ')
905 ||'|'||rpad(lpad('Value To',20,' '),25,' ')
906 ||'|'||rpad(lpad('Value From',20,' '),25,' ')
907 ||'|'||rpad(lpad('Value To',20,' '),25,' ')
908 );
909
910 FOR c_break_lines_attr_val_rec in c_break_lines_attr_values(p_pbh_id)
911 LOOP
912 IF l_first_break THEN
913 l_prc_attr_val_from := '0';
914 l_prc_attr_val_from_number := 0;
915 l_first_break := false;
916 ELSE
917 l_prc_attr_val_from := l_old_value_to;
918 l_prc_attr_val_from_number := l_old_value_to_number;
919 END IF;
920 l_old_value_to := c_break_lines_attr_val_rec.PRICING_ATTR_VALUE_TO;
921 l_old_value_to_number := c_break_lines_attr_val_rec.PRICING_ATTR_VALUE_TO_NUMBER;
922
923 fnd_file.put_line(FND_FILE.LOG,rpad(lpad(c_break_lines_attr_val_rec.pricing_attr_value_from,20,' '),25,' ')
924 ||'|'||rpad(lpad(c_break_lines_attr_val_rec.pricing_attr_value_to,20,' '),25,' ')
925 ||'|'||rpad(lpad(l_prc_attr_val_from,20,' '),25,' ')
926 ||'|'||rpad(lpad(c_break_lines_attr_val_rec.pricing_attr_value_to,20,' '),25,' ')
927 );
928
929 BEGIN
930 UPDATE qp_pricing_attributes SET
931 PRICING_ATTR_VALUE_FROM = l_prc_attr_val_from,
932 PRICING_ATTR_VALUE_FROM_NUMBER = l_prc_attr_val_from_number
933 WHERE CURRENT OF c_break_lines_attr_values;
934 EXCEPTION
935 WHEN OTHERS THEN
936 raise UPGRADE_PRICE_BREAKS_EXCEPTION;
937 END;
938 END LOOP;
939
940 --oe_debug_pub.add('Exiting QP_DELAYED_REQUESTS_UTIL.Upgrade_Price_Breaks');
941
942 EXCEPTION
943
944 WHEN UPGRADE_PRICE_BREAKS_EXCEPTION
945 THEN
946 x_return_status := FND_API.G_RET_STS_ERROR;
947
948 WHEN FND_API.G_EXC_ERROR THEN
949
950 x_return_status := FND_API.G_RET_STS_ERROR;
951
952 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
953
954 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
955
956 WHEN OTHERS THEN
957
958 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
959
960 oe_debug_pub.add('upgrade breaks error '||substr(sqlerrm,1,100));
961 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
962 THEN
963 OE_MSG_PUB.Add_Exc_Msg
964 ( G_PKG_NAME
965 , 'QP_Delayed_Requests_Util'
966 );
967 END IF;
968
969 END Upgrade_Price_Breaks;
970
971
972
973 Procedure Check_Mult_Price_Break_Attrs
974 (x_return_status OUT NOCOPY VARCHAR2,
975 p_parent_list_line_id IN NUMBER)
976 IS
977
978 e_mult_price_break_attrs EXCEPTION;
979 l_count NUMBER := 0;
980
981 BEGIN
982
983 select count(DISTINCT pricing_attribute)
984 into l_count
985 from qp_price_breaks_v
986 where parent_list_line_id = p_parent_list_line_id;
987
988 oe_debug_pub.add('price break groups '|| to_char(l_count) );
989 IF l_count > 1 THEN
990 RAISE e_mult_price_break_attrs;
991 END IF;
992
993 EXCEPTION
994 WHEN e_mult_price_break_attrs THEN
995
996 oe_debug_pub.add('In relevant exception ');
997 FND_MESSAGE.SET_NAME('QP','QP_MULT_PRICE_BREAK_ATTRS');
998 oe_msg_pub.Add;
999
1000 x_return_status := FND_API.G_RET_STS_ERROR;
1001
1002 WHEN FND_API.G_EXC_ERROR THEN
1003
1004 x_return_status := FND_API.G_RET_STS_ERROR;
1005
1006 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1007
1008 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1009
1010 WHEN OTHERS THEN
1011
1012 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1013
1014 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1015 THEN
1016 OE_MSG_PUB.Add_Exc_Msg
1017 ( G_PKG_NAME
1018 , 'Check_Mult_Price_Break_Attrs'
1019 );
1020 END IF;
1021
1022
1026 Procedure Check_Mixed_Qual_Seg_Levels
1023 END Check_Mult_Price_Break_Attrs;
1024
1025
1027 (x_return_status OUT NOCOPY VARCHAR2,
1028 p_qualifier_rule_id IN NUMBER)
1029 IS
1030
1031 e_mixed_qual_seg_levels EXCEPTION;
1032 l_qualifier_grouping_no NUMBER;
1033 l_pte_code VARCHAR2(30);
1034
1035 CURSOR count_cur(a_qualifier_rule_id NUMBER, a_pte_code VARCHAR2)
1036 IS
1037 SELECT a.qualifier_grouping_no
1038 FROM qp_qualifiers a
1039 WHERE a.qualifier_rule_id = a_qualifier_rule_id
1040 AND EXISTS (SELECT 'x'
1041 FROM qp_qualifiers b, qp_prc_contexts_b d,
1042 qp_segments_b e, qp_pte_segments f
1043 WHERE b.qualifier_context = d.prc_context_code
1044 AND b.qualifier_attribute = e.segment_mapping_column
1045 AND d.prc_context_id = e.prc_context_id
1046 AND e.segment_id = f.segment_id
1047 AND f.pte_code = a_pte_code
1048 AND f.segment_level = 'LINE'
1049 AND b.qualifier_rule_id = a_qualifier_rule_id
1050 AND (b.qualifier_grouping_no = a.qualifier_grouping_no or
1051 b.qualifier_grouping_no = -1))
1052
1053 AND EXISTS (SELECT 'x'
1054 FROM qp_qualifiers c, qp_prc_contexts_b d1,
1055 qp_segments_b e1, qp_pte_segments f1
1056 WHERE c.qualifier_context = d1.prc_context_code
1057 AND c.qualifier_attribute = e1.segment_mapping_column
1058 AND d1.prc_context_id = e1.prc_context_id
1059 AND e1.segment_id = f1.segment_id
1060 AND f1.pte_code = a_pte_code
1061 AND f1.segment_level = 'ORDER'
1062 AND c.qualifier_rule_id = a_qualifier_rule_id
1063 AND (c.qualifier_grouping_no = a.qualifier_grouping_no or
1064 c.qualifier_grouping_no = -1))
1065
1066 GROUP BY a.qualifier_grouping_no;
1067
1068 BEGIN
1069 oe_debug_pub.add('Check_Mixed_Qual_Seg_Levels');
1070 FND_PROFILE.GET('QP_PRICING_TRANSACTION_ENTITY', l_pte_code);
1071
1072 if l_pte_code is null then
1073 l_pte_code := 'ORDFUL';
1074 end if;
1075
1076 OPEN count_cur(p_qualifier_rule_id, l_pte_code);
1077 FETCH count_cur INTO l_qualifier_grouping_no;
1078
1079 IF count_cur%FOUND THEN
1080 CLOSE count_cur;
1081 RAISE e_mixed_qual_seg_levels;
1082 END IF;
1083
1084 CLOSE count_cur;
1085
1086 EXCEPTION
1087 WHEN e_mixed_qual_seg_levels THEN
1088
1089 oe_debug_pub.add('Mixed Segment Levels for Qualifiers Attributes with ' ||
1090 'qualifier_rule_id = ' || p_qualifier_rule_id ||
1091 'and qualifier_grouping_no = ' || l_qualifier_grouping_no);
1092
1093 FND_MESSAGE.SET_NAME('QP','QP_MIXED_QUAL_SEG_LEVELS');
1094 oe_msg_pub.Add;
1095
1096 x_return_status := FND_API.G_RET_STS_ERROR;
1097
1098 WHEN FND_API.G_EXC_ERROR THEN
1099
1100 x_return_status := FND_API.G_RET_STS_ERROR;
1101
1102 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1103
1104 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1105
1106 WHEN OTHERS THEN
1107
1108 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1109
1110 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1111 THEN
1112
1113 OE_MSG_PUB.Add_Exc_Msg
1114 ( G_PKG_NAME
1115 , 'Check_Mixed_Qual_Seg_Levels'
1116 );
1117 END IF;
1118
1119 END Check_Mixed_Qual_Seg_Levels;
1120
1121
1122 Procedure Check_multiple_prl
1123 ( x_return_status OUT NOCOPY Varchar2
1124 , p_list_header_id IN NUMBER
1125 )IS
1126
1127 l_status NUMBER ;
1128
1129 MULTIPLE_PRICE_LIST_EXCEPTION EXCEPTION;
1130
1131 Cursor C_modifier(p_list_header_id number) IS
1132 SELECT count(1)
1133 FROM QP_LIST_HEADERS qplh ,
1134 QP_QUALIFIERS qpq
1135 WHERE qplh.list_header_id = p_list_header_id
1136 AND qplh.list_header_id = qpq.list_header_id
1137 AND qpq.qualifier_context = 'MODLIST'
1138 AND qpq.qualifier_attribute = 'QUALIFIER_ATTRIBUTE4';
1139 BEGIN
1140
1141 x_return_status := FND_API.G_RET_STS_SUCCESS;
1142
1143 open C_modifier(p_list_header_id);
1144 fetch C_modifier into l_status;
1145 close C_modifier;
1146
1147 If l_status > 1 Then
1148
1149 --oe_debug_pub.add('status is duplicate');
1150 --dbms_output.put_line('status is duplicate');
1151
1152 x_return_status := FND_API.G_RET_STS_ERROR;
1153
1154 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR)
1155 THEN
1156
1157 fnd_message.set_name('ONT', 'OE_DIS_DUPLICATE_LIN_DISC');
1158 OE_MSG_PUB.Add;
1159 END IF;
1160 RAISE MULTIPLE_PRICE_LIST_EXCEPTION;
1161 END IF;
1162
1163 oe_debug_pub.add('Exiting QP_DELAYED_REQUESTS_UTIL.checkdup');
1164 --dbms_output.put_line('Exiting QP_DELAYED_REQUESTS_UTIL.checkdup');
1165
1166 EXCEPTION
1167 WHEN MULTIPLE_PRICE_LIST_EXCEPTION
1168 THEN
1169 x_return_status := FND_API.G_RET_STS_ERROR;
1170
1171 WHEN FND_API.G_EXC_ERROR THEN
1172
1173 x_return_status := FND_API.G_RET_STS_ERROR;
1174
1175 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1176
1177 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1178
1179 WHEN OTHERS THEN
1180
1184 THEN
1181 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1182
1183 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1185 OE_MSG_PUB.Add_Exc_Msg
1186 ( G_PKG_NAME
1187 , 'QP_Delayed_Requests_Util'
1188 );
1189 END IF;
1190
1191 END Check_multiple_prl;
1192
1193 Procedure Maintain_Qualifier_Den_Cols
1194 ( x_return_status OUT NOCOPY Varchar2
1195 , p_list_header_id IN NUMBER
1196 ) IS
1197
1198 l_err_buf varchar2(30);
1199 l_ret_code number;
1200
1201 BEGIN
1202
1203 x_return_status := FND_API.G_RET_STS_SUCCESS;
1204
1205 QP_Maintain_Denormalized_Data.Update_Qualifiers(err_buff => l_err_buf,
1206 retcode => l_ret_code,
1207 p_list_header_id => p_list_header_id,
1208 p_update_type => 'DELAYED_REQ');
1209
1210 IF l_ret_code <> 0 THEN
1211
1212 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1213
1214 END IF;
1215
1216 EXCEPTION
1217
1218 WHEN FND_API.G_EXC_ERROR THEN
1219
1220 x_return_status := FND_API.G_RET_STS_ERROR;
1221
1222 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1223
1224 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1225 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1226 THEN
1227 OE_MSG_PUB.Add_Exc_Msg
1228 ( G_PKG_NAME
1229 , 'Unexpected error occured in the procedure : QP_Maintain_Denormalized_Data.Update_Qualifiers');
1230 END IF;
1231
1232 WHEN OTHERS THEN
1233
1234 x_return_status := FND_API.G_RET_STS_ERROR;
1235
1236 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1237 THEN
1238 OE_MSG_PUB.Add_Exc_Msg
1239 ( G_PKG_NAME
1240 , 'Update_Qualification_Ind');
1241 END IF;
1242
1243 END Maintain_Qualifier_Den_Cols;
1244
1245
1246 Procedure Maintain_Factor_List_Attrs
1247 ( x_return_status OUT NOCOPY Varchar2
1248 , p_list_line_id IN NUMBER
1249 )
1250 IS
1251 l_list_header_id NUMBER;
1252
1253 BEGIN
1254
1255 BEGIN
1256 SELECT list_header_id
1257 INTO l_list_header_id
1258 FROM qp_list_lines
1259 WHERE list_line_id = p_list_line_id;
1260 EXCEPTION
1261 WHEN OTHERS THEN
1262 l_list_header_id := 0;
1263 END;
1264
1265 x_return_status := FND_API.G_RET_STS_SUCCESS;
1266 QP_Denormalized_Pricing_Attrs.Update_Pricing_Attributes(
1267 p_list_header_id_low => l_list_header_id,
1268 p_list_header_id_high => l_list_header_id,
1269 p_update_type => 'FACTOR_DELAYED_REQ');
1270
1271 QP_Denormalized_Pricing_Attrs.Populate_Factor_List_Attrs(
1272 p_list_header_id_low => l_list_header_id,
1273 p_list_header_id_high => l_list_header_id);
1274
1275 EXCEPTION
1276 WHEN OTHERS THEN
1277 x_return_status := FND_API.G_RET_STS_ERROR;
1278
1279 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1280 THEN
1281 OE_MSG_PUB.Add_Exc_Msg
1282 ( G_PKG_NAME
1283 , 'Maintain_Factor_List_Attrs');
1284 END IF;
1285
1286 END Maintain_Factor_List_Attrs;
1287
1288
1289 Procedure Update_List_Qualification_Ind
1290 ( x_return_status OUT NOCOPY Varchar2
1291 , p_list_header_id IN NUMBER
1292 ) IS
1293
1294 CURSOR list_lines_cur (a_list_header_id NUMBER)
1295 IS
1296 SELECT list_line_id, qualification_ind
1297 FROM qp_list_lines
1298 WHERE list_header_id = a_list_header_id;
1299
1300 l_dummy number;
1301 l_list_type_code varchar2(30);
1302
1303 BEGIN
1304
1305 x_return_status := FND_API.G_RET_STS_SUCCESS;
1306
1307 oe_debug_pub.add('list_line_id is NULL');
1308
1309 BEGIN
1310 select list_type_code
1311 into l_list_type_code
1312 from qp_list_headers_vl
1313 where list_header_id = p_list_header_id;
1314 EXCEPTION
1315 WHEN OTHERS THEN
1316 NULL;
1317 END;
1318
1319 update qp_list_lines qpl
1320 set qpl.qualification_ind = 0
1321 where qpl.list_header_id=p_list_header_id;
1322
1323 update qp_list_lines qpl
1324 set qpl.qualification_ind=nvl(qualification_ind,0) + 1
1325 where qpl.list_header_id=p_list_header_id
1326 and exists (
1327 select 'X'
1328 from qp_rltd_modifiers qprltd
1329 where qprltd.to_rltd_modifier_id=qpl.list_line_id
1330 and rltd_modifier_grp_type<>'COUPON');
1331
1332
1333 IF l_list_type_code IN ('PRL', 'AGR') THEN
1334
1335 --Check if there exist qualifiers, not including qualifiers
1336 --corresponding to primary price list as qualifier for a secondary PL
1337 -- Replaced the count(*) with exists clause for performance fix of bug 2337578
1338
1339 Begin
1340 select 1 into l_dummy from dual where
1341 exists ( Select 'Y'
1342 from qp_qualifiers
1343 where list_header_id = p_list_header_id
1344 and NOT (qualifier_context = 'MODLIST' AND
1345 qualifier_attribute = 'QUALIFIER_ATTRIBUTE4'));
1346 Exception
1347 when no_data_found then
1351
1348 l_dummy :=0;
1349 End; --End of bug 2337578
1350
1352 IF l_dummy > 0 THEN --Qualifiers exist
1353 update qp_list_lines qpl
1354 set qpl.qualification_ind=nvl(qpl.qualification_ind,0) + 2
1355 where qpl.list_header_id=p_list_header_id;
1356 END IF;
1357
1358 ELSE -- for other list_type_codes
1359
1360 -- Header level qualifiers
1361 update qp_list_lines qpl
1362 set qpl.qualification_ind=nvl(qpl.qualification_ind,0) + 2
1363 where qpl.list_header_id=p_list_header_id
1364 and exists (
1365 select 'X'
1366 from qp_qualifiers q
1367 where q.list_header_id=qpl.list_header_id
1368 and q.list_line_id = -1);
1369
1370 -- Line level qualifiers
1371 update qp_list_lines qpl
1372 set qpl.qualification_ind=nvl(qpl.qualification_ind,0) + 8
1373 where qpl.list_header_id=p_list_header_id
1374 and exists (
1375 select 'X'
1376 from qp_qualifiers q
1377 where q.list_header_id=qpl.list_header_id
1378 and q.list_line_id=qpl.list_line_id);
1379
1380 END IF; --If list_type_code is 'PRL or 'AGR'
1381
1382 -- If Product Attributes exist
1383 update qp_list_lines qpl
1384 set qpl.qualification_ind= nvl(qpl.qualification_ind, 0) + 4
1385 where qpl.list_header_id=p_list_header_id
1386 and exists (
1387 select /*+ no_unnest */ 'X' --5612361
1388 from qp_pricing_attributes qpprod
1389 where qpprod.list_line_id = qpl.list_line_id
1390 and qpprod.excluder_flag = 'N');
1391
1392 -- If Pricing Attributes exist
1393 update qp_list_lines qpl
1394 set qpl.qualification_ind= nvl(qpl.qualification_ind, 0) + 16
1395 where qpl.list_header_id=p_list_header_id
1396 and exists (
1397 select 'X'
1398 from qp_pricing_attributes qpprod
1399 where qpprod.list_line_id = qpl.list_line_id
1400 and qpprod.list_header_id = p_list_header_id --bug#4261111
1401 and qpprod.pricing_attribute_context is not null
1402 and qpprod.pricing_attribute is not null
1403 -- changes made per rchellam's request --spgopal
1404 and qpprod.pricing_attr_value_from is not null);
1405 /*5612361
1406 for list_lines_rec IN list_lines_cur(p_list_header_id)
1407 loop
1408
1409 update qp_pricing_attributes
1410 set qualification_ind = list_lines_rec.qualification_ind
1411 where list_line_id = list_lines_rec.list_line_id;
1412
1413 end loop;
1414 */
1415
1416 --5612361
1417
1418 UPDATE QP_PRICING_ATTRIBUTES A
1419 SET QUALIFICATION_IND = (SELECT QUALIFICATION_IND
1420 FROM QP_LIST_LINES
1421 where A.LIST_LINE_ID = LIST_LINE_ID
1422 and LIST_HEADER_ID = p_list_header_id)
1423 WHERE LIST_LINE_ID in
1424 (SELECT /*+ cardinality(QP_LIST_LINES 1) */ LIST_LINE_ID
1425 FROM QP_LIST_LINES WHERE LIST_HEADER_ID = p_list_header_id);
1426
1427 EXCEPTION
1428
1429 WHEN FND_API.G_EXC_ERROR THEN
1430
1431 x_return_status := FND_API.G_RET_STS_ERROR;
1432
1433 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1434
1435 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1436
1437 WHEN OTHERS THEN
1438
1439 x_return_status := FND_API.G_RET_STS_ERROR;
1440
1441 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1442 THEN
1443 OE_MSG_PUB.Add_Exc_Msg
1444 ( G_PKG_NAME
1445 , 'Update_Qualification_Ind');
1446 END IF;
1447
1448 END Update_List_Qualification_Ind;
1449
1450 Procedure Update_Limits_Columns
1451 ( p_Limit_Id IN NUMBER
1452 ,x_return_status OUT NOCOPY Varchar2
1453 )
1454 IS
1455
1456 l_Organization_Count NUMBER := 0;
1457 l_Customer_Attr_Count NUMBER := 0;
1458 l_Product_Attr_Count NUMBER := 0;
1459 l_Limit_Attrs_Count NUMBER := 0;
1460 l_Total_Attr_Count NUMBER := 0;
1461 l_dummy NUMBER := 0;
1462
1463 Phase_Exception Exception;
1464 l_return_status_text VARCHAR2(300);
1465
1466 BEGIN
1467
1468 SELECT COUNT(*)
1469 INTO l_Organization_Count
1470 FROM QP_LIMITS
1471 WHERE (limit_id = p_Limit_Id) AND
1472 (UPPER(ORGANIZATION_FLAG) = 'Y');
1473
1474 SELECT COUNT(*)
1475 INTO l_Customer_Attr_Count
1476 FROM QP_LIMITS
1477 WHERE ((limit_id = p_Limit_Id) AND
1478 ((MULTIVAL_ATTR1_TYPE IS NOT NULL) OR
1479 (MULTIVAL_ATTR1_CONTEXT IS NOT NULL) OR
1480 (MULTIVAL_ATTRIBUTE1 IS NOT NULL) OR
1481 (MULTIVAL_ATTR1_DATATYPE IS NOT NULL)));
1482
1483 SELECT COUNT(*)
1484 INTO l_Product_Attr_Count
1485 FROM QP_LIMITS
1486 WHERE ((limit_id = p_Limit_Id) AND
1487 ((MULTIVAL_ATTR2_TYPE IS NOT NULL) OR
1488 (MULTIVAL_ATTR2_CONTEXT IS NOT NULL) OR
1489 (MULTIVAL_ATTRIBUTE2 IS NOT NULL) OR
1490 (MULTIVAL_ATTR2_DATATYPE IS NOT NULL)));
1491
1492 SELECT COUNT(*)
1493 INTO l_Limit_Attrs_Count
1494 FROM QP_LIMIT_ATTRIBUTES
1495 WHERE limit_id = p_Limit_Id;
1496
1497 l_Total_Attr_Count := l_Organization_Count + l_Customer_Attr_Count +
1498 l_Product_Attr_Count + l_Limit_Attrs_Count;
1499
1500 SELECT COUNT(*)
1504
1501 INTO l_dummy
1502 FROM QP_LIMITS
1503 WHERE limit_id = p_Limit_Id;
1505 IF l_dummy > 0 -- LIMIT EXISTS
1506 THEN
1507 IF (l_Organization_Count > 0) OR (l_Customer_Attr_Count > 0)
1508 OR (l_Product_Attr_Count > 0)
1509 THEN
1510 UPDATE QP_LIMITS
1511 SET EACH_ATTR_EXISTS = 'Y'
1512 WHERE limit_id = p_Limit_Id;
1513 ELSE
1514 UPDATE QP_LIMITS
1515 SET EACH_ATTR_EXISTS = 'N'
1516 WHERE limit_id = p_Limit_Id;
1517 END IF;
1518
1519 UPDATE QP_LIMITS
1520 SET NON_EACH_ATTR_COUNT = l_Limit_Attrs_Count
1521 WHERE limit_id = p_Limit_Id;
1522
1523 UPDATE QP_LIMITS
1524 SET TOTAL_ATTR_COUNT = l_Total_Attr_Count
1525 WHERE limit_id = p_Limit_Id;
1526 END IF;
1527
1528 --made the change to call this API to update the basic_modifiers_setup profile
1529 --when a limit gets created so the OM calls old code path
1530 IF QP_CODE_CONTROL.GET_CODE_RELEASE_LEVEL < '110509' THEN
1531 QP_Maintain_Denormalized_Data.Update_Pricing_Phases
1532 (p_update_type => 'DELAYED_REQ'
1533 --,p_pricing_phase_id => p_pricing_phase_id
1534 -- commenting out as suggested by SPGOPAL
1535 ,x_return_status => x_return_status
1536 ,x_return_status_text => l_return_status_text);
1537
1538 IF x_return_status = FND_API.G_RET_STS_ERROR
1539 THEN
1540 oe_debug_pub.add('error update_pricing_phase begin'||l_return_status_text);
1541 raise Phase_exception;
1542 END IF;
1543 END IF;
1544
1545 oe_debug_pub.add('end update_pricing_phase begin');
1546
1547 EXCEPTION
1548 WHEN Phase_Exception THEN
1549 x_return_status := FND_API.G_RET_STS_ERROR;
1550
1551 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1552 OE_MSG_PUB.Add_Exc_Msg
1553 ( G_PKG_NAME
1554 , 'Update_Pricing_Phase');
1555 END IF;
1556 WHEN FND_API.G_EXC_ERROR THEN
1557 x_return_status := FND_API.G_RET_STS_ERROR;
1558 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1559 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1560 WHEN OTHERS THEN
1561 x_return_status := FND_API.G_RET_STS_ERROR;
1562 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1563 OE_MSG_PUB.Add_Exc_Msg
1564 ( G_PKG_NAME
1565 , 'Update_Pricing_Phase');
1566 END IF;
1567 END Update_Limits_Columns;
1568
1569 Procedure Update_Line_Qualification_Ind
1570 ( x_return_status OUT NOCOPY Varchar2
1571 , p_list_line_id IN NUMBER
1572 ) IS
1573
1574 l_qualification_ind NUMBER;
1575 l_dummy number;
1576 l_list_type_code varchar2(30);
1577 l_list_header_id number;
1578
1579 BEGIN
1580
1581 x_return_status := FND_API.G_RET_STS_SUCCESS;
1582
1583 oe_debug_pub.add('list_line_id is '||p_list_line_id);
1584
1585 BEGIN
1586 select list_type_code, list_header_id
1587 into l_list_type_code, l_list_header_id
1588 from qp_list_headers_vl
1589 where list_header_id = (select list_header_id
1590 from qp_list_lines
1591 where list_line_id = p_list_line_id);
1592 EXCEPTION
1593 WHEN OTHERS THEN
1594 NULL;
1595 END;
1596
1597 update qp_list_lines qpl
1598 set qpl.qualification_ind = 0
1599 where qpl.list_line_id=p_list_line_id;
1600
1601 update qp_list_lines qpl
1602 set qpl.qualification_ind=nvl(qualification_ind,0) + 1
1603 where qpl.list_line_id=p_list_line_id
1604 and exists (
1605 select 'X'
1606 from qp_rltd_modifiers qprltd
1607 where qprltd.to_rltd_modifier_id=p_list_line_id
1608 and rltd_modifier_grp_type<>'COUPON')
1609 returning qpl.qualification_ind into l_qualification_ind;
1610
1611 IF l_list_type_code IN ('PRL', 'AGR') THEN
1612
1613 --Check if there exist qualifiers, not including qualifiers
1614 --corresponding to primary price list as qualifier for a secondary PL
1615
1616 -- Replaced the count(*) with exists clause for performance fix of bug 2337578
1617
1618 Begin
1619 select 1 into l_dummy from dual where
1620 exists ( Select 'Y'
1621 from qp_qualifiers
1622 where list_header_id = l_list_header_id
1623 and NOT (qualifier_context = 'MODLIST' AND
1624 qualifier_attribute = 'QUALIFIER_ATTRIBUTE4'));
1625 Exception
1626 when no_data_found then
1627 l_dummy :=0;
1628 End; --End of bug 2337578
1629
1630
1631 IF l_dummy > 0 THEN --Qualifiers exist
1632 update qp_list_lines qpl
1633 set qpl.qualification_ind=nvl(qpl.qualification_ind,0) + 2
1634 where qpl.list_line_id=p_list_line_id
1635 returning qpl.qualification_ind into l_qualification_ind;
1636 END IF;
1637
1638 ELSE -- for other list_type_codes
1639
1640 -- Header level qualifiers
1641 update qp_list_lines qpl
1642 set qpl.qualification_ind=nvl(qpl.qualification_ind,0) + 2
1643 where qpl.list_line_id=p_list_line_id
1644 and exists (
1645 select 'X'
1649 returning qpl.qualification_ind into l_qualification_ind;
1646 from qp_qualifiers q
1647 where q.list_header_id=qpl.list_header_id
1648 and q.list_line_id = -1)
1650
1651 -- Line level qualifiers
1652 update qp_list_lines qpl
1653 set qpl.qualification_ind=nvl(qpl.qualification_ind,0) + 8
1654 where qpl.list_line_id=p_list_line_id
1655 and exists (
1656 select 'X'
1657 from qp_qualifiers q
1658 where q.list_header_id=qpl.list_header_id
1659 and q.list_line_id=p_list_line_id)
1660 returning qpl.qualification_ind into l_qualification_ind;
1661
1662 END IF; --If list_type_code is 'PRL or 'AGR'
1663
1664 -- If Product Attributes exist
1665 update qp_list_lines qpl
1666 set qpl.qualification_ind= nvl(qpl.qualification_ind, 0) + 4
1667 where qpl.list_line_id=p_list_line_id
1668 and exists (
1669 select 'X'
1670 from qp_pricing_attributes qpprod
1671 where qpprod.list_line_id = p_list_line_id
1672 and qpprod.excluder_flag = 'N')
1673 returning qpl.qualification_ind into l_qualification_ind;
1674
1675 -- If Pricing Attributes exist
1676 update qp_list_lines qpl
1677 set qpl.qualification_ind= nvl(qpl.qualification_ind, 0) + 16
1678 where qpl.list_line_id=p_list_line_id
1679 and exists (
1680 select 'X'
1681 from qp_pricing_attributes qpprod
1682 where qpprod.list_line_id = p_list_line_id
1683 and qpprod.pricing_attribute_context is not null
1684 and qpprod.pricing_attribute is not null
1685 -- changes made per rchellam's request --spgopal
1686 and qpprod.pricing_attr_value_from is not null)
1687 returning qpl.qualification_ind into l_qualification_ind;
1688
1689 update qp_pricing_attributes pra
1690 set pra.qualification_ind = l_qualification_ind
1691 where pra.list_line_id = p_list_line_id;
1692
1693 EXCEPTION
1694
1695 WHEN FND_API.G_EXC_ERROR THEN
1696
1697 x_return_status := FND_API.G_RET_STS_ERROR;
1698
1699 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1700
1701 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1702
1703 WHEN OTHERS THEN
1704
1705 x_return_status := FND_API.G_RET_STS_ERROR;
1706
1707 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1708 THEN
1709 OE_MSG_PUB.Add_Exc_Msg
1710 ( G_PKG_NAME
1711 , 'Update_Qualification_Ind');
1712 END IF;
1713
1714
1715 END Update_Line_Qualification_Ind;
1716
1717
1718 Procedure Update_Child_Break_Lines
1719 ( x_return_status OUT NOCOPY Varchar2
1720 , p_list_line_id IN NUMBER
1721 ) IS
1722 l_price_break_type varchar2(30);
1723 l_pricing_phase_id number;
1724 l_arithmetic_operator varchar2(30); -- 4936019
1725
1726 CURSOR pbh_details_csr is
1727 SELECT
1728 a.modifier_level_code
1729 ,a.automatic_flag
1730 ,a.override_flag
1731 ,a.Print_on_invoice_flag
1732 ,a.price_break_type_code
1733 ,a.arithmetic_operator -- 4936019
1734 ,a.Proration_type_code
1735 ,a.Incompatibility_Grp_code
1736 ,a.Pricing_phase_id
1737 ,a.Pricing_group_sequence
1738 ,a.accrual_flag
1739 ,a.estim_accrual_rate
1740 ,a.expiration_date
1741 ,a.expiration_period_start_date
1742 ,a.expiration_period_uom
1743 ,a.number_expiration_periods
1744 ,a.rebate_transaction_type_code
1745 FROM qp_list_lines a
1746 WHERE a.list_line_id = p_list_line_id;
1747
1748 BEGIN
1749
1750 -- l_price_break_type := p_price_break_type;
1751
1752 x_return_status := FND_API.G_RET_STS_SUCCESS;
1753
1754 oe_debug_pub.add('list_line_id is '||p_list_line_id);
1755
1756 select price_break_type_code,pricing_phase_id, arithmetic_operator -- 4936019
1757 into l_price_break_type,l_pricing_phase_id, l_arithmetic_operator -- 4936019
1758 from qp_list_lines
1759 where list_line_id = p_list_line_id;
1760
1761 IF (l_pricing_phase_id = 1) THEN
1762 update qp_list_lines qpl
1763 set qpl.price_break_type_code = l_price_break_type
1764 where qpl.list_line_id in ( select to_rltd_modifier_id
1765 from qp_rltd_modifiers
1766 where from_rltd_modifier_id = p_list_line_id );
1767 IF (l_arithmetic_operator = 'UNIT_PRICE') THEN
1768 update qp_list_lines qpl
1769 set qpl.arithmetic_operator = 'UNIT_PRICE'
1770 where qpl.list_line_id in ( select to_rltd_modifier_id
1771 from qp_rltd_modifiers
1772 where from_rltd_modifier_id = p_list_line_id )
1773 and qpl.arithmetic_operator <> 'UNIT_PRICE';
1774 END IF;
1775
1776 IF (l_arithmetic_operator = 'BLOCK_PRICE' and l_price_break_type = 'POINT') THEN
1777 update qp_list_lines qpl
1778 set qpl.arithmetic_operator = 'BLOCK_PRICE'
1779 where qpl.list_line_id in ( select to_rltd_modifier_id
1780 from qp_rltd_modifiers
1781 where from_rltd_modifier_id = p_list_line_id )
1782 and qpl.arithmetic_operator <> 'BLOCK_PRICE';
1783 END IF;
1784
1785 IF (l_arithmetic_operator = 'BLOCK_PRICE' and l_price_break_type = 'RANGE') THEN
1786 update qp_list_lines qpl
1787 set qpl.arithmetic_operator = 'BLOCK_PRICE'
1791 and qpl.arithmetic_operator not in ('BLOCK_PRICE', 'BREAKUNIT_PRICE');
1788 where qpl.list_line_id in ( select to_rltd_modifier_id
1789 from qp_rltd_modifiers
1790 where from_rltd_modifier_id = p_list_line_id )
1792 END IF;
1793
1794 ELSE
1795 FOR i in pbh_details_csr
1796 LOOP
1797 UPDATE qp_list_lines
1798 SET modifier_level_code = i.modifier_level_code
1799 ,automatic_flag = i.automatic_flag
1800 ,override_flag = i.override_flag
1801 ,Print_on_invoice_flag = i.Print_on_invoice_flag
1802 ,price_break_type_code = i.price_break_type_code
1803 ,Proration_type_code = i.Proration_type_code
1804 ,Incompatibility_Grp_code= i.Incompatibility_Grp_code
1805 ,Pricing_phase_id = i.Pricing_phase_id
1806 ,Pricing_group_sequence = i.Pricing_group_sequence
1807 ,accrual_flag = i.accrual_flag
1808 ,rebate_transaction_type_code = i.rebate_transaction_type_code
1809 ,estim_accrual_rate = i.estim_accrual_rate
1810 ,expiration_date = i.expiration_date
1811 ,expiration_period_start_date = i.expiration_period_start_date
1812 ,expiration_period_uom = i.expiration_period_uom
1813 ,number_expiration_periods = i.number_expiration_periods
1814 WHERE list_line_id in (select to_rltd_modifier_id
1815 from qp_rltd_modifiers
1816 where from_rltd_modifier_id = p_list_line_id);
1817
1818 END LOOP;
1819 END IF;
1820
1821
1822 EXCEPTION
1823
1824 WHEN FND_API.G_EXC_ERROR THEN
1825
1826 x_return_status := FND_API.G_RET_STS_ERROR;
1827
1828 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1829
1830 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1831
1832 WHEN OTHERS THEN
1833
1834 x_return_status := FND_API.G_RET_STS_ERROR;
1835
1836 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1837 THEN
1838 OE_MSG_PUB.Add_Exc_Msg
1839 ( G_PKG_NAME
1840 , 'Update_Child_Break_Lines');
1841 END IF;
1842
1843
1844 END Update_Child_Break_Lines;
1845
1846
1847 PROCEDURE UPDATE_CHILD_PRICING_ATTR
1848 ( x_return_status OUT NOCOPY Varchar2
1849 , p_list_line_id IN NUMBER) IS
1850
1851 l_status NUMBER;
1852 l_list_line_id NUMBER;
1853 l_list_line_type_code VARCHAR2(30);
1854 l_Pricing_Attr_rec QP_PRICING_ATTRIBUTES%rowtype;
1855
1856 Cursor C_pbh_product_details IS
1857 SELECT product_attribute_context,
1858 product_attribute,
1859 product_attr_value,
1860 product_uom_code
1861 FROM QP_PRICING_ATTRIBUTES
1862 WHERE list_line_id = p_list_line_id;
1863
1864
1865
1866 BEGIN
1867
1868 /* select list_line_type_code
1869 into l_list_line_type_code
1870 from qp_list_lines where
1871 list_line_id = p_list_line_id; */
1872
1873 --IF l_list_line_type_code = 'PBH' THEN
1874
1875 --l_modifier_grp_type := 'PRICE BREAK';
1876
1877 --updating all child break pricing_attributes
1878
1879 FOR i in C_pbh_product_details
1880 LOOP
1881
1882 UPDATE qp_Pricing_Attributes SET
1883 Product_attribute_context = i.Product_attribute_context
1884 ,Product_attribute = i.Product_attribute
1885 ,Product_attr_value = i.Product_attr_value
1886 ,Product_uom_code = i.Product_Uom_Code
1887 WHERE list_line_id IN (select to_rltd_modifier_id
1888 from qp_rltd_modifiers qrm
1889 where from_rltd_modifier_id = p_list_line_id);
1890
1891 END LOOP;
1892
1893 --END IF;
1894
1895 EXCEPTION
1896
1897 WHEN OTHERS THEN
1898
1899 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1900 THEN
1901 OE_MSG_PUB.Add_Exc_Msg
1902 ( G_PKG_NAME
1903 , 'Update_Child_Pricing_Attr'
1904 );
1905 END IF;
1906
1907 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1908
1909 END UPDATE_CHILD_PRICING_ATTR;
1910
1911 /*added by spgopal for including list_header_id and pricing_phase_id in pricing_attributes table for modifiers*/
1912
1913 Procedure Update_Pricing_Attr_Phase
1914 ( x_return_status OUT NOCOPY Varchar2
1915 , p_List_Line_ID IN NUMBER
1916 ) IS
1917
1918 l_Pricing_Phase_id QP_PRICING_PHASES.PRICING_PHASE_ID%TYPE
1919 := FND_API.G_MISS_NUM;
1920 l_List_Header_ID QP_LIST_HEADERS_B.LIST_HEADER_ID%TYPE
1921 := FND_API.G_MISS_NUM;
1922
1923 /*
1924 Cursor C_pricing_attr(p_list_line_id number) IS
1925 SELECT *
1926 FROM QP_LIST_LINES WHERE list_line_id = p_list_line_id;
1927 */
1928
1929 BEGIN
1930
1931
1932 IF (p_List_Line_ID IS NOT NULL OR
1933 p_List_Line_ID <> FND_API.G_MISS_NUM) THEN
1934
1935 SELECT LIST_HEADER_ID, PRICING_PHASE_ID INTO
1936 l_List_Header_ID, l_Pricing_Phase_ID FROM QP_LIST_LINES
1937 WHERE LIST_LINE_ID = p_List_Line_ID;
1938
1939
1940 -- open c_Pricing_Attr(p_list_line_id); LOOP
1941
1942 for C_Pricing_Attr in (select pricing_attribute_id
1943 from qp_pricing_attributes
1944 where list_line_id = p_list_line_id) LOOP
1945
1946
1947 Update QP_PRICING_ATTRIBUTES Set
1948 LIST_HEADER_ID = l_List_Header_ID,
1952 END LOOP;
1949 PRICING_PHASE_ID = l_Pricing_Phase_ID
1950 where PRICING_ATTRIBUTE_ID = C_Pricing_Attr.Pricing_Attribute_ID;
1951
1953
1954 END IF;
1955
1956 EXCEPTION
1957
1958 WHEN FND_API.G_EXC_ERROR THEN
1959
1960 x_return_status := FND_API.G_RET_STS_ERROR;
1961
1962 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1963
1964 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1965
1966 WHEN OTHERS THEN
1967
1968 x_return_status := FND_API.G_RET_STS_ERROR;
1969
1970 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1971 THEN
1972 OE_MSG_PUB.Add_Exc_Msg
1973 ( G_PKG_NAME
1974 , 'Update_Pricing_Attr_Phase');
1975 END IF;
1976
1977
1978 END Update_Pricing_Attr_Phase;
1979
1980
1981
1982 /*added by spgopal for updating denormalised info on pricing_phases about line_g
1983 roup, oid and rltd lines for modifiers in that phase*/
1984
1985 Procedure Update_Pricing_Phase
1986 ( x_return_status OUT NOCOPY Varchar2
1987 , p_pricing_phase_id IN NUMBER
1988 , p_automatic_flag IN Varchar2 --fix for bug 3756625
1989 , p_count IN NUMBER
1990 , p_call_from IN NUMBER
1991 ) IS
1992 l_line_type VARCHAR2(30) := 'NONE';
1993 l_level_type VARCHAR2(30) := 'NONE';
1994
1995 l_line VARCHAR2(1) := 'N';
1996 l_rltd VARCHAR2(1) := 'N';
1997 l_level VARCHAR2(1) := 'N';
1998 Phase_Exception Exception;
1999 l_return_status_text VARCHAR2(300);
2000
2001 BEGIN
2002 oe_debug_pub.add('update_pricing_phase begin'||p_pricing_phase_id);--||' '||p_parent_line_id||' '||p_modifier_level_code);
2003
2004 /*
2005 IF p_list_line_type = 'OID' THEN
2006 l_line := 'Y';
2007 ELSIF p_list_line_type = 'RLTD' THEN
2008 IF p_parent_line_id IS NOT NULL OR
2009 p_parent_line_id <> FND_API.G_MISS_NUM THEN
2010
2011 select decode(LL.list_line_type_code, 'PRG','Y','N')
2012 into l_rltd from qp_list_lines LL
2013 where LL.list_line_id = p_parent_line_id;
2014
2015 END IF;
2016 ELSE NULL;
2017 END IF;
2018
2019 IF p_modifier_level_code = 'LINEGROUP' THEN
2020 l_level := 'Y';
2021 END IF;
2022
2023 -- oe_debug_pub.add('update_pricing_phase l_line '||l_line||' l_rltd '||l_rltd||' l_level '||l_level);
2024
2025 update qp_pricing_phases PH set
2026 PH.oid_exists = decode(l_line,'N',PH.oid_exists,'Y',l_line)
2027 , PH.rltd_exists = decode(l_rltd,'N',PH.rltd_exists,'Y',l_rltd)
2028 , PH.line_group_exists =
2029 decode(l_level,'N',PH.line_group_exists,'Y',l_level)
2030 where pricing_phase_id = p_pricing_phase_id;
2031 */
2032 QP_Maintain_Denormalized_Data.Update_Pricing_Phases
2033 (p_update_type => 'DELAYED_REQ'
2034 ,p_pricing_phase_id => p_pricing_phase_id
2035 ,p_automatic_flag => p_automatic_flag --fix for bug 3756625
2036 ,p_count => p_count
2037 ,p_call_from => p_call_from
2038 ,x_return_status => x_return_status
2039 ,x_return_status_text => l_return_status_text);
2040
2041 IF x_return_status = FND_API.G_RET_STS_ERROR
2042 THEN
2043 oe_debug_pub.add('error update_pricing_phase begin'||l_return_status_text);
2044 raise Phase_exception;
2045 END IF;
2046
2047 oe_debug_pub.add('end update_pricing_phase begin');
2048 EXCEPTION
2049 WHEN Phase_Exception THEN
2050
2051 x_return_status := FND_API.G_RET_STS_ERROR;
2052
2053 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2054 OE_MSG_PUB.Add_Exc_Msg
2055 ( G_PKG_NAME
2056 , 'Update_Pricing_Phase');
2057 END IF;
2058
2059 WHEN FND_API.G_EXC_ERROR THEN
2060
2061 x_return_status := FND_API.G_RET_STS_ERROR;
2062
2063 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2064
2065 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2066
2067 WHEN OTHERS THEN
2068
2069 x_return_status := FND_API.G_RET_STS_ERROR;
2070
2071 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2072 OE_MSG_PUB.Add_Exc_Msg
2073 ( G_PKG_NAME
2074 , 'Update_Pricing_Phase');
2075 END IF;
2076 END Update_Pricing_Phase;
2077
2078
2079 --Essilor Fix bug 2789138
2080 Procedure Update_manual_modifier_flag
2081 ( x_return_status OUT NOCOPY Varchar2
2082 , p_automatic_flag IN Varchar2
2083 , p_pricing_phase_id IN NUMBER
2084 ) IS
2085 l_manual_modifier_flag VARCHAR2(1);
2086 l_set_manual_flag VARCHAR2(1);
2087 BEGIN
2088
2089 oe_debug_pub.add('Update manual modifier flag Begin ');
2090 oe_debug_pub.add('Pricing Phase Id : '||p_pricing_phase_id);
2091 oe_debug_pub.add('Automatic Flag : '||p_automatic_flag);
2092
2093 l_set_manual_flag := NULL;
2094 select manual_modifier_flag into l_manual_modifier_flag
2095 from qp_pricing_phases
2096 where pricing_phase_id = p_pricing_phase_id;
2097
2098 IF nvl(l_manual_modifier_flag, '*') = 'A' then
2099 IF p_automatic_flag = 'N' then
2100 l_set_manual_flag := 'B';
2101 else
2102 l_set_manual_flag := 'A';
2103 END IF;
2104 ELSIF nvl(l_manual_modifier_flag, '*') = 'M' then
2105 IF p_automatic_flag = 'Y' then
2106 l_set_manual_flag := 'B';
2107 else
2108 l_set_manual_flag := 'M';
2109 END IF;
2110 ELSIF l_manual_modifier_flag is NULL then
2111 IF p_automatic_flag = 'Y' then
2112 l_set_manual_flag := 'A';
2116 END IF;
2113 ELSIF p_automatic_flag = 'N' then
2114 l_set_manual_flag := 'M';
2115 END IF;
2117 if l_set_manual_flag is not NULL
2118 AND ( l_manual_modifier_flag IS NULL --bug 14367359
2119 OR l_manual_modifier_flag <> l_set_manual_flag) --bug 14367359
2120 then
2121 update qp_pricing_phases
2122 set manual_modifier_flag = l_set_manual_flag
2123 where pricing_phase_id = p_pricing_phase_id;
2124 end if;
2125
2126 oe_debug_pub.add('Update manual modifier flag End');
2127 EXCEPTION
2128 WHEN FND_API.G_EXC_ERROR THEN
2129
2130 x_return_status := FND_API.G_RET_STS_ERROR;
2131
2132 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2133
2134 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2135
2136 WHEN OTHERS THEN
2137
2138 x_return_status := FND_API.G_RET_STS_ERROR;
2139
2140 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2141 OE_MSG_PUB.Add_Exc_Msg
2142 ( G_PKG_NAME
2143 , 'Update_Pricing_Phase');
2144 END IF;
2145 END Update_manual_modifier_flag;
2146
2147
2148 Procedure Validate_Selling_Rounding
2149 ( x_return_status OUT NOCOPY Varchar2
2150 , p_currency_header_id IN NUMBER
2151 , p_to_currency_code IN VARCHAR2
2152 ) IS
2153 -- If the selling_rounding_factor is NULL, then assuming a very high value 9999999 to compare
2154 -- with the not null selling_rounding_factor
2155 cursor c_selling_rounding is
2156 select distinct nvl(selling_rounding_factor, 9999999) selling_rounding_factor
2157 from qp_currency_details
2158 where currency_header_id = p_currency_header_id
2159 and to_currency_code = p_to_currency_code;
2160
2161 l_first_record varchar2(10);
2162 l_first_selling_rounding number;
2163 BEGIN
2164 oe_debug_pub.add('validate_selling_price begin '||p_currency_header_id||' '||p_to_currency_code);
2165
2166 x_return_status := FND_API.G_RET_STS_SUCCESS;
2167 l_first_record := 'TRUE';
2168
2169 for selling_rounding_rec in c_selling_rounding
2170 LOOP
2171 oe_debug_pub.add('IN LOOP selling_rounding_factor = '||selling_rounding_rec.selling_rounding_factor);
2172 if l_first_record = 'TRUE' then
2173 l_first_selling_rounding := selling_rounding_rec.selling_rounding_factor;
2174 l_first_record := 'FALSE';
2175 end if;
2176
2177 oe_debug_pub.add('l_first_selling_rounding = '||l_first_selling_rounding);
2178 if selling_rounding_rec.selling_rounding_factor <> l_first_selling_rounding then
2179 oe_debug_pub.add('selling_rounding_factor NOT EQUAL ');
2180
2181 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR) THEN
2182 fnd_message.set_name('QP', 'QP_SELLING_ROUNDING_NOT_SAME');
2183 fnd_message.set_token('CURRENCY_CODE', p_to_currency_code);
2184 OE_MSG_PUB.Add;
2185 END IF;
2186
2187 raise FND_API.G_EXC_ERROR;
2188 end if;
2189
2190 END LOOP;
2191
2192 oe_debug_pub.add('end validate_selling_rounding ');
2193
2194 EXCEPTION
2195 WHEN FND_API.G_EXC_ERROR THEN
2196
2197 x_return_status := FND_API.G_RET_STS_ERROR;
2198
2199 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2200
2201 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2202
2203 WHEN OTHERS THEN
2204
2205 x_return_status := FND_API.G_RET_STS_ERROR;
2206
2207 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2208 OE_MSG_PUB.Add_Exc_Msg
2209 ( G_PKG_NAME
2210 , 'Validate_Selling_Rounding');
2211 END IF;
2212 END Validate_Selling_Rounding;
2213
2214 Procedure Check_Segment_Level_in_Group
2215 ( x_return_status OUT NOCOPY Varchar2
2216 , p_list_line_id IN NUMBER
2217 , p_list_header_id IN NUMBER
2218 , p_qualifier_grouping_no IN NUMBER
2219 )
2220 is
2221 cursor c_qualifiers is
2222 select qualifier_context, qualifier_attribute
2223 from qp_qualifiers
2224 where list_header_id = p_list_header_id
2225 and ((qualifier_grouping_no = p_qualifier_grouping_no) OR (qualifier_grouping_no = -1))
2226 and list_line_id = p_list_line_id;
2227
2228 l_current_segment_level VARCHAR2(30) := NULL;
2229
2230 l_final_segment_level VARCHAR2(30) := NULL;
2231
2232
2233 BEGIN
2234 oe_debug_pub.add('Begin Check_Segment_Level_in_Group');
2235 oe_debug_pub.add('p_list_line_id = ' || p_list_line_id);
2236 oe_debug_pub.add('p_list_header_id = ' || p_list_header_id);
2237 oe_debug_pub.add('p_qualifier_grouping_no = ' || p_qualifier_grouping_no);
2238 FOR l_rec in c_qualifiers
2239 LOOP
2240 l_current_segment_level := qp_util.get_segment_level(p_list_header_id
2241 ,l_rec.qualifier_context
2242 ,l_rec.qualifier_attribute
2243 );
2244 if l_final_segment_level is NULL then
2245 l_final_segment_level := l_current_segment_level;
2246 else
2247 if l_final_segment_level = 'LINE' then
2248 if l_current_segment_level = 'LINE' then
2249 l_final_segment_level := 'LINE';
2250 elsif l_current_segment_level = 'BOTH' then
2251 l_final_segment_level := 'LINE_BOTH';
2252 elsif l_current_segment_level = 'ORDER' then
2253 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR) THEN
2254 -- There is a mix of 'LINE' and 'ORDER' segment level for list line id ? and
2258 fnd_message.set_token('LIST_LINE_ID', p_list_line_id);
2255 -- qualifier grouping no ?. Please make sure that all the segments should be either
2256 -- of level LINE/BOTH or ORDER/BOTH for a given list line id and qualifier grouping no
2257 fnd_message.set_name('QP', 'QP_MIXED_SEGMENT_LEVELS');
2259 fnd_message.set_token('QUALIFIER_GRP_NO', p_qualifier_grouping_no);
2260 OE_MSG_PUB.Add;
2261 END IF;
2262 raise FND_API.G_EXC_ERROR;
2263 end if;
2264
2265 elsif l_final_segment_level = 'ORDER' then
2266 if l_current_segment_level = 'ORDER' then
2267 l_final_segment_level := 'ORDER';
2268 elsif l_current_segment_level = 'BOTH' then
2269 l_final_segment_level := 'ORDER_BOTH';
2270 elsif l_current_segment_level = 'LINE' then
2271 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR) THEN
2272 -- There is a mix of 'LINE' and 'ORDER' segment level for list line id ? and
2273 -- qualifier grouping no ?. Please make sure that all the segments should be either
2274 -- of level LINE/BOTH or ORDER/BOTH for a given list line id and qualifier grouping no
2275 fnd_message.set_name('QP', 'QP_MIXED_SEGMENT_LEVELS');
2276 fnd_message.set_token('LIST_LINE_ID', p_list_line_id);
2277 fnd_message.set_token('QUALIFIER_GRP_NO', p_qualifier_grouping_no);
2278 OE_MSG_PUB.Add;
2279 END IF;
2280 raise FND_API.G_EXC_ERROR;
2281 end if;
2282
2283 elsif l_final_segment_level = 'BOTH' then
2284 if l_current_segment_level = 'LINE' then
2285 l_final_segment_level := 'LINE_BOTH';
2286 elsif l_current_segment_level = 'ORDER' then
2287 l_final_segment_level := 'ORDER_BOTH';
2288 elsif l_current_segment_level = 'BOTH' then
2289 l_final_segment_level := 'BOTH';
2290 end if;
2291
2292 elsif l_final_segment_level = 'LINE_BOTH' then
2293 if l_current_segment_level in ('LINE', 'BOTH') then
2294 l_final_segment_level := 'LINE_BOTH';
2295 elsif l_current_segment_level = 'ORDER' then
2296 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR) THEN
2297 -- There is a mix of 'LINE' and 'ORDER' segment level for list line id ? and
2298 -- qualifier grouping no ?. Please make sure that all the segments should be either
2299 -- of level LINE/BOTH or ORDER/BOTH for a given list line id and qualifier grouping no
2300 fnd_message.set_name('QP', 'QP_MIXED_SEGMENT_LEVELS');
2301 fnd_message.set_token('LIST_LINE_ID', p_list_line_id);
2302 fnd_message.set_token('QUALIFIER_GRP_NO', p_qualifier_grouping_no);
2303 OE_MSG_PUB.Add;
2304 END IF;
2305 raise FND_API.G_EXC_ERROR;
2306 end if;
2307
2308 elsif l_final_segment_level = 'ORDER_BOTH' then
2309 if l_current_segment_level in ('ORDER', 'BOTH') then
2310 l_final_segment_level := 'ORDER_BOTH';
2311 elsif l_current_segment_level = 'LINE' then
2312 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR) THEN
2313 -- There is a mix of 'LINE' and 'ORDER' segment level for list line id ? and
2314 -- qualifier grouping no ?. Please make sure that all the segments should be either
2315 -- of level LINE/BOTH or ORDER/BOTH for a given list line id and qualifier grouping no
2316 fnd_message.set_name('QP', 'QP_MIXED_SEGMENT_LEVELS');
2317 fnd_message.set_token('LIST_LINE_ID', p_list_line_id);
2318 fnd_message.set_token('QUALIFIER_GRP_NO', p_qualifier_grouping_no);
2319 OE_MSG_PUB.Add;
2320 END IF;
2321 raise FND_API.G_EXC_ERROR;
2322 end if;
2323
2324 end if; -- l_final_segment_level = 'LINE'
2325 end if; -- l_final_segment_level is NULL
2326
2327 END LOOP;
2328
2329 oe_debug_pub.add('End Check_Segment_Level_in_Group');
2330 EXCEPTION
2331 WHEN FND_API.G_EXC_ERROR THEN
2332
2333 x_return_status := FND_API.G_RET_STS_ERROR;
2334
2335 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2336
2337 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2338
2339 WHEN OTHERS THEN
2340
2341 x_return_status := FND_API.G_RET_STS_ERROR;
2342
2343 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2344 OE_MSG_PUB.Add_Exc_Msg
2345 ( G_PKG_NAME
2346 , 'Check_Segment_Level_in_Group');
2347 END IF;
2348 END Check_Segment_Level_in_Group;
2349
2350 Procedure Check_Line_for_Header_Qual
2351 ( x_return_status OUT NOCOPY Varchar2
2352 , p_list_line_id IN NUMBER
2353 , p_list_header_id IN NUMBER
2354 )
2355 is
2356 cursor c_mod_level is
2357 select distinct modifier_level_code modifier_level
2358 from qp_list_lines
2359 where list_header_id = p_list_header_id;
2360
2361 cursor c_qual_grp_no is
2362 select distinct qualifier_grouping_no qualifier_grouping_no
2363 from qp_qualifiers
2364 where list_header_id = p_list_header_id
2365 and list_line_id = p_list_line_id
2366 and qualifier_grouping_no <> -1;
2367
2368 l_order_modifier_exists VARCHAR2(1) := 'N';
2369 l_line_modifier_exists VARCHAR2(1) := 'N';
2370
2371 l_qual_exist_for_line_modifier VARCHAR2(1) := 'N';
2372 l_qual_exist_for_ord_modifier VARCHAR2(1) := 'N';
2373
2377 l_segment_level VARCHAR2(30) := NULL;
2374 -- to check whether header qualifier exists with qualifier_grouping_no <> -1
2375 l_header_qual_exists VARCHAR2(1) := 'N';
2376
2378
2379 BEGIN
2380 oe_debug_pub.add('Begin Check_Line_for_Header_Qual');
2381 oe_debug_pub.add('p_list_line_id = ' || p_list_line_id);
2382 oe_debug_pub.add('p_list_header_id = ' || p_list_header_id);
2383 for l_mod_rec in c_mod_level
2384 LOOP
2385 if l_mod_rec.modifier_level = 'ORDER' then
2386 l_order_modifier_exists := 'Y';
2387 elsif l_mod_rec.modifier_level in ('LINE', 'LINEGROUP') then
2388 l_line_modifier_exists := 'Y';
2389 end if;
2390 END LOOP;
2391
2392 if l_line_modifier_exists = 'Y' or l_order_modifier_exists = 'Y' then
2393 for l_rec in c_qual_grp_no
2394 LOOP
2395 l_header_qual_exists := 'Y';
2396 l_segment_level := QP_Modifier_List_Util.Get_Segment_Level_for_Group(p_list_header_id,
2397 p_list_line_id ,
2398 l_rec.qualifier_grouping_no);
2399 if ((l_segment_level in ('LINE', 'LINE_BOTH', 'BOTH')) AND l_line_modifier_exists = 'Y') then
2400 l_qual_exist_for_line_modifier := 'Y';
2401 end if;
2402
2403 if ((l_segment_level in ('ORDER', 'ORDER_BOTH', 'BOTH')) AND l_order_modifier_exists = 'Y') then
2404 l_qual_exist_for_ord_modifier := 'Y';
2405 end if;
2406 END LOOP;
2407
2408 -- if no header qualifiers exist with qualifier_grouping_no <> -1, then check for -1
2409 if l_header_qual_exists = 'N' then
2410 l_segment_level := NULL;
2411 l_segment_level := QP_Modifier_List_Util.Get_Segment_Level_for_Group(p_list_header_id,
2412 p_list_line_id ,
2413 -1);
2414 -- if l_segment_level is not null then it means header qualifiers with grouping no -1 exist
2415 if l_segment_level is not null then
2416 l_header_qual_exists := 'Y';
2417 if ((l_segment_level in ('LINE', 'LINE_BOTH', 'BOTH')) AND l_line_modifier_exists = 'Y') then
2418 l_qual_exist_for_line_modifier := 'Y';
2419 end if;
2420
2421 if ((l_segment_level in ('ORDER', 'ORDER_BOTH', 'BOTH')) AND l_order_modifier_exists = 'Y') then
2422 l_qual_exist_for_ord_modifier := 'Y';
2423 end if;
2424 end if; -- l_segment_level is not null
2425 end if; -- l_header_qual_exists = 'N'
2426
2427 if l_header_qual_exists = 'Y' then
2428 if l_line_modifier_exists = 'Y' and l_qual_exist_for_line_modifier = 'N' then
2429 oe_debug_pub.add('mkarya - Modifier Lines of level ''LINE'' or ''LINEGROUP''
2430 will not be applied to an order as no header qualifier exist');
2431 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR) THEN
2432 fnd_message.set_name('QP', 'QP_NO_HEADER_QUAL_FOR_LINE');
2433 OE_MSG_PUB.Add;
2434 END IF;
2435 --raise FND_API.G_EXC_ERROR;
2436 end if;
2437
2438 if l_order_modifier_exists = 'Y' and l_qual_exist_for_ord_modifier = 'N' then
2439 oe_debug_pub.add('mkarya - Modifier Lines of level ''ORDER'' will not be applied
2440 to an order as no header qualifier exist');
2441 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR) THEN
2442 fnd_message.set_name('QP', 'QP_NO_HEADER_QUAL_FOR_ORD');
2443 OE_MSG_PUB.Add;
2444 END IF;
2445 --raise FND_API.G_EXC_ERROR;
2446 end if;
2447
2448 end if; -- l_header_qual_exists = 'Y'
2449 end if; -- l_line_modifier_exists = 'Y' or l_order_modifier_exists = 'Y'
2450
2451 oe_debug_pub.add('End Check_Line_for_Header_Qual');
2452 EXCEPTION
2453 WHEN FND_API.G_EXC_ERROR THEN
2454
2455 x_return_status := FND_API.G_RET_STS_ERROR;
2456
2457 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2458
2459 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2460
2461 WHEN OTHERS THEN
2462
2463 x_return_status := FND_API.G_RET_STS_ERROR;
2464
2465 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2466 OE_MSG_PUB.Add_Exc_Msg
2467 ( G_PKG_NAME
2468 , 'Check_Line_for_Header_Qual');
2469 END IF;
2470 END Check_Line_for_Header_Qual;
2471
2472
2473 --hw
2474 -- update QP_ADV_MOD_PRODUCTS for changed lines
2475
2476 procedure update_changed_lines_add (
2477 p_list_line_id in number,
2478 p_list_header_id in number,
2479 p_pricing_phase_id in number,
2480 x_return_status out NOCOPY varchar2
2481 ) is
2482 begin
2483
2484 oe_debug_pub.add('process update_changed_lines_add');
2485
2486 --please note that this is being done from concurrent program
2487 --QP: Maintain denormalised data in qp_qualifiers also in which
2488 --case the procedure update_changed_lines_product in QPXDENOB.pls
2489 --is called. Any changes made to this routine needs to be
2490 --communicated to DENOB as well.
2491 --also this same operation is done in 3 other procedures in this
2492 --same API so the fixes need to be done there as well. They are:
2493 --update_changed_lines_act,update_changed_lines_del/_add/_ph
2494
2495 begin
2496
2497 insert into qp_adv_mod_products (
2498 product_attribute,
2499 product_attr_value,
2500 pricing_phase_id) (
2501 select distinct qpa.product_attribute,
2505 where qpa.list_line_id = p_list_line_id
2502 qpa.product_attr_value,
2503 p_pricing_phase_id
2504 from qp_pricing_attributes qpa
2506 and not exists (
2507 select 'Y' from qp_adv_mod_products
2508 where pricing_phase_id = p_pricing_phase_id
2509 and product_attribute = qpa.product_attribute
2510 and product_attr_value = qpa.product_attr_value));
2511
2512 exception
2513 when others then
2514 x_return_status := FND_API.G_RET_STS_ERROR;
2515 end;
2516
2517 x_return_status := FND_API.G_RET_STS_SUCCESS;
2518
2519 end update_changed_lines_add;
2520
2521
2522 procedure update_changed_lines_del (
2523 p_list_line_id in number,
2524 p_list_header_id in number,
2525 p_pricing_phase_id in number,
2526 p_product_attribute in varchar2,
2527 p_product_attr_value in varchar2,
2528 x_return_status out NOCOPY varchar2
2529 ) is
2530 begin
2531
2532 oe_debug_pub.add('process update_changed_lines_del');
2533
2534 --please note that this is being done from concurrent program
2535 --QP: Maintain denormalised data in qp_qualifiers also in which
2536 --case the procedure update_changed_lines_product in QPXDENOB.pls
2537 --is called. Any changes made to this routine needs to be
2538 --communicated to DENOB as well.
2539 --also this same operation is done in 3 other procedures in this
2540 --same API so the fixes need to be done there as well. They are:
2541 --update_changed_lines_act,update_changed_lines_del/_add/_ph
2542
2543 begin
2544
2545 delete from qp_adv_mod_products
2546 where pricing_phase_id = p_pricing_phase_id
2547 and product_attribute = p_product_attribute
2548 and product_attr_value = p_product_attr_value
2549 and not exists (
2550 select 'Y'
2551 from qp_list_lines qpl,
2552 qp_list_headers_b qph
2553 where qpl.list_line_id = p_list_line_id
2554 and qpl.modifier_level_code = 'LINEGROUP'
2555 and qph.list_header_id = p_list_header_id
2556 and qph.active_flag = 'Y'
2557 and rownum = 1
2558 union
2559 select 'Y'
2560 from qp_rltd_modifiers qpr,
2561 qp_list_lines qpl,
2562 qp_list_headers_b qph
2563 where qpl.list_line_id = p_list_line_id
2564 and qpr.to_rltd_modifier_id = p_list_line_id
2565 and qpr.rltd_modifier_grp_type = 'BENEFIT'
2566 and qpl.list_line_type_code = 'DIS'
2567 and qph.list_header_id = p_list_header_id
2568 and qph.active_flag = 'Y'
2569 and qpr.to_rltd_modifier_id = qpl.list_line_id
2570 and qph.list_header_id = qpl.list_header_id
2571 and rownum = 1
2572 union
2573 select 'Y'
2574 from qp_list_lines qpl,
2575 qp_list_headers_b qph
2576 where qpl.list_line_id = p_list_line_id
2577 and qpl.list_line_type_code in ('OID', 'PRG', 'RLTD')
2578 and qph.list_header_id = p_list_header_id
2579 and qph.active_flag = 'Y'
2580 and rownum = 1);
2581
2582 exception
2583 when others then
2584 x_return_status := FND_API.G_RET_STS_ERROR;
2585 end;
2586
2587 x_return_status := FND_API.G_RET_STS_SUCCESS;
2588
2589 end update_changed_lines_del;
2590
2591 procedure update_changed_lines_ph (
2592 p_list_line_id in number,
2593 p_list_header_id in number,
2594 p_pricing_phase_id in number,
2595 p_old_pricing_phase_id in number,
2596 x_return_status out NOCOPY varchar2
2597 ) is
2598 l_product_attribute varchar2(30);
2599 l_product_attr_value varchar2(240);
2600 l_pricing_phase_id number;
2601 begin
2602
2603 oe_debug_pub.add('process update_changed_lines_ph');
2604
2605 --please note that this is being done from concurrent program
2606 --QP: Maintain denormalised data in qp_qualifiers also in which
2607 --case the procedure update_changed_lines_product in QPXDENOB.pls
2608 --is called. Any changes made to this routine needs to be
2609 --communicated to DENOB as well.
2610 --also this same operation is done in 3 other procedures in this
2611 --same API so the fixes need to be done there as well. They are:
2612 --update_changed_lines_act,update_changed_lines_del/_add/_ph
2613 begin
2614
2615 -- process new phase id
2616 insert into qp_adv_mod_products (
2617 product_attribute,
2618 product_attr_value,
2619 pricing_phase_id) (
2620 select distinct qpa.product_attribute,
2621 qpa.product_attr_value,
2622 p_pricing_phase_id
2623 from qp_pricing_attributes qpa
2624 where qpa.list_line_id = p_list_line_id
2625 and not exists (
2626 select 'Y' from qp_adv_mod_products
2627 where pricing_phase_id = p_pricing_phase_id
2628 and product_attribute = qpa.product_attribute
2629 and product_attr_value = qpa.product_attr_value));
2630
2631 -- process old phase id
2632 select distinct product_attribute,
2633 product_attr_value,
2634 p_pricing_phase_id
2635 into l_product_attribute,
2636 l_product_attr_value,
2637 l_pricing_phase_id
2638 from qp_pricing_attributes
2639 where list_line_id = p_list_line_id;
2640
2641 --tuned SQl to avoid cartesian join
2642 delete from qp_adv_mod_products
2643 where pricing_phase_id = p_old_pricing_phase_id
2644 and product_attribute = l_product_attribute
2645 and product_attr_value = l_product_attr_value
2646 and not exists (
2647 select /*+ index(qpa QP_PRICING_ATTRIBUTES_N12) */ 'Y' --bug#11813168
2648 from qp_pricing_attributes qpa,
2649 qp_list_lines qpl,
2653 and qpa.product_attr_value = l_product_attr_value
2650 qp_list_headers_b qph
2651 where qpa.pricing_phase_id = p_old_pricing_phase_id
2652 and qpa.product_attribute = l_product_attribute
2654 and qpl.list_line_id = qpa.list_line_id
2655 and qpl.modifier_level_code = 'LINEGROUP'
2656 and qph.list_header_id = qpa.list_header_id
2657 and qph.active_flag = 'Y'
2658 and rownum = 1
2659 union
2660 select /*+ index(qpa QP_PRICING_ATTRIBUTES_N12) */ 'Y' --bug#11813168
2661 from qp_rltd_modifiers qpr,
2662 qp_list_lines qpl,
2663 qp_pricing_attributes qpa,
2664 qp_list_headers_b qph
2665 where qpa.pricing_phase_id = p_old_pricing_phase_id
2666 and qpa.product_attribute = l_product_attribute
2667 and qpa.product_attr_value = l_product_attr_value
2668 and qpl.list_line_id = qpa.list_line_id
2669 and qpr.rltd_modifier_grp_type = 'BENEFIT'
2670 and qpr.to_rltd_modifier_id = qpl.list_line_id
2671 and qpl.list_line_type_code = 'DIS'
2672 and qph.list_header_id = qpa.list_header_id
2673 and qph.active_flag = 'Y'
2674 and rownum = 1
2675 union
2676 select /*+ index(qpa QP_PRICING_ATTRIBUTES_N12) */ 'Y' --bug#11813168
2677 from qp_list_lines qpl,
2678 qp_pricing_attributes qpa,
2679 qp_list_headers_b qph
2680 where qpa.pricing_phase_id = p_old_pricing_phase_id
2681 and qpa.product_attribute = l_product_attribute
2682 and qpa.product_attr_value = l_product_attr_value
2683 and qpl.list_line_type_code in ('OID', 'PRG', 'RLTD')
2684 and qpl.list_line_id = qpa.list_line_id
2685 and qph.list_header_id = qpl.list_header_id--p_list_header_id
2686 and qph.active_flag = 'Y'
2687 and rownum = 1);
2688
2689 exception
2690 when others then
2691 x_return_status := FND_API.G_RET_STS_ERROR;
2692 end;
2693
2694 x_return_status := FND_API.G_RET_STS_SUCCESS;
2695
2696 end update_changed_lines_ph;
2697
2698 procedure update_changed_lines_act (
2699 p_list_header_id in number,
2700 p_active_flag varchar2,
2701 x_return_status out NOCOPY varchar2
2702 ) is
2703 l_product_attribute varchar2(30);
2704 l_product_attr_value varchar2(240);
2705
2706 cursor l_get_line_csr is
2707 select distinct qpl.list_line_id,
2708 qpl.pricing_phase_id,
2709 qpa.product_attribute,
2710 qpa.product_attr_value
2711 from qp_list_lines qpl,
2712 qp_pricing_attributes qpa
2713 where qpl.list_header_id = p_list_header_id
2714 and qpa.list_line_id = qpl.list_line_id;
2715
2716 begin
2717
2718 oe_debug_pub.add('process update_changed_lines_act');
2719 --please note that this is being done from concurrent program
2720 --QP: Maintain denormalised data in qp_qualifiers also in which
2721 --case the procedure update_changed_lines_product in QPXDENOB.pls
2722 --is called. Any changes made to this routine needs to be
2723 --communicated to DENOB as well.
2724 --also this same operation is done in 3 other procedures in this
2725 --same API so the fixes need to be done there as well. They are:
2726 --update_changed_lines_act,update_changed_lines_del/_add/_ph
2727
2728 begin
2729
2730 if p_active_flag = 'Y' then
2731
2732 for line_cursor in l_get_line_csr loop
2733
2734 insert into qp_adv_mod_products (
2735 pricing_phase_id,
2736 product_attribute,
2737 product_attr_value
2738 ) (
2739 select line_cursor.pricing_phase_id,
2740 line_cursor.product_attribute,
2741 line_cursor.product_attr_value
2742 from dual
2743 where not exists (
2744 select 'Y' from qp_adv_mod_products
2745 where pricing_phase_id = line_cursor.pricing_phase_id
2746 and product_attribute = line_cursor.product_attribute
2747 and product_attr_value = line_cursor.product_attr_value));
2748
2749 end loop;
2750
2751 else
2752
2753 for line_cursor in l_get_line_csr loop
2754
2755 --tuned SQl to avoid cartesian join
2756 delete from qp_adv_mod_products
2757 where pricing_phase_id = line_cursor.pricing_phase_id
2758 and product_attribute = line_cursor.product_attribute
2759 and product_attr_value = line_cursor.product_attr_value
2760 and not exists (
2761 select /*+ index(qpa QP_PRICING_ATTRIBUTES_N12) */ 'Y' --bug#11813168
2762 from qp_pricing_attributes qpa,
2763 qp_list_lines qpl,
2764 qp_list_headers_b qph
2765 where qpa.pricing_phase_id = line_cursor.pricing_phase_id
2766 and qpa.product_attribute = line_cursor.product_attribute
2767 and qpa.product_attr_value = line_cursor.product_attr_value
2768 and qpl.list_line_id = qpa.list_line_id
2769 and qpl.modifier_level_code = 'LINEGROUP'
2770 and qph.list_header_id = qpa.list_header_id
2771 and qph.active_flag = 'Y'
2772 and rownum = 1
2773 union
2777 qp_pricing_attributes qpa,
2774 select /*+ index(qpa QP_PRICING_ATTRIBUTES_N12) */ 'Y' --bug#11813168
2775 from qp_rltd_modifiers qpr,
2776 qp_list_lines qpl,
2778 qp_list_headers_b qph
2779 where qpa.pricing_phase_id = line_cursor.pricing_phase_id
2780 and qpa.product_attribute = line_cursor.product_attribute
2781 and qpa.product_attr_value = line_cursor.product_attr_value
2782 and qpl.list_line_id = qpa.list_line_id
2783 and qpr.rltd_modifier_grp_type = 'BENEFIT'
2784 and qpr.to_rltd_modifier_id = qpl.list_line_id
2785 and qpl.list_line_type_code = 'DIS'
2786 and qph.list_header_id = qpa.list_header_id
2787 and qph.active_flag = 'Y'
2788 and rownum = 1
2789 union
2790 select /*+ index(qpa QP_PRICING_ATTRIBUTES_N12) */ 'Y' --bug#11813168
2791 from qp_list_lines qpl,
2792 qp_pricing_attributes qpa,
2793 qp_list_headers_b qph
2794 where qpa.pricing_phase_id = line_cursor.pricing_phase_id
2795 and qpa.product_attribute = line_cursor.product_attribute
2796 and qpa.product_attr_value = line_cursor.product_attr_value
2797 and qpl.list_line_type_code in ('OID', 'PRG', 'RLTD')
2798 and qpl.list_line_id = qpa.list_line_id
2799 and qph.list_header_id = qpl.list_header_id
2800 and qph.active_flag = 'Y'
2801 and rownum = 1);
2802 end loop;
2803
2804 end if;
2805
2806 exception
2807 when others then
2808 x_return_status := FND_API.G_RET_STS_ERROR;
2809 end;
2810
2811 x_return_status := FND_API.G_RET_STS_SUCCESS;
2812
2813 end update_changed_lines_act;
2814
2815 Procedure HVOP_Pricing_Setup (x_return_status OUT NOCOPY VARCHAR2) IS
2816
2817 l_return_status_text VARCHAR2(200);
2818 procedure_error Exception;
2819 BEGIN
2820
2821 QP_Maintain_Denormalized_Data.Set_HVOP_Pricing (x_return_status, l_return_status_text);
2822 IF(x_return_status=FND_API.G_RET_STS_ERROR) THEN
2823 raise procedure_error;
2824 END IF;
2825
2826 EXCEPTION
2827 WHEN procedure_error THEN
2828 OE_MSG_PUB.Add_Exc_Msg
2829 ( G_PKG_NAME
2830 , 'Error while executing the process QP_Maintain_Denormalized_Data.Set_HVOP_Pricing : '|| l_return_status_text);
2831
2832 WHEN FND_API.G_EXC_ERROR THEN
2833 x_return_status := FND_API.G_RET_STS_ERROR;
2834 OE_MSG_PUB.Add_Exc_Msg
2835 ( G_PKG_NAME
2836 , l_return_status_text);
2837
2838 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2839 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2840 OE_MSG_PUB.Add_Exc_Msg
2841 ( G_PKG_NAME
2842 , l_return_status_text);
2843
2844 WHEN OTHERS THEN
2845 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2846 OE_MSG_PUB.Add_Exc_Msg
2847 ( G_PKG_NAME
2848 , l_return_status_text);
2849 END HVOP_Pricing_Setup;
2850
2851 -- pattern
2852 Procedure Maintain_header_pattern(p_list_header_id in number,
2853 p_qualifier_group in number,
2854 p_setup_action in varchar2,
2855 x_return_status out NOCOPY varchar2)
2856 IS
2857 BEGIN
2858 IF FND_PROFILE.VALUE('QP_PATTERN_SEARCH') = 'M' OR FND_PROFILE.VALUE('QP_PATTERN_SEARCH') = 'P' OR FND_PROFILE.VALUE('QP_PATTERN_SEARCH') = 'B' THEN
2859 QP_PS_ATTR_GRP_PVT.Header_Pattern_Main(
2860 p_list_header_id => p_list_header_id,
2861 p_qualifier_group => p_qualifier_group,
2862 p_setup_action => p_setup_action);
2863 oe_debug_pub.add('calling new jagan ' || p_list_header_id);
2864 ELSE
2865 QP_ATTR_GRP_PVT.Header_Pattern_Main(
2866 p_list_header_id => p_list_header_id,
2867 p_qualifier_group => p_qualifier_group,
2868 p_setup_action => p_setup_action);
2869 oe_debug_pub.add('calling old jagan ' || p_list_header_id);
2870 END IF;
2871 EXCEPTION
2872 WHEN FND_API.G_EXC_ERROR THEN
2873 x_return_status := FND_API.G_RET_STS_ERROR;
2874
2875 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2876 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2877
2878 WHEN OTHERS THEN
2879 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2880
2881 END Maintain_header_pattern;
2882
2883 Procedure Maintain_line_pattern(p_list_header_id in number,
2884 p_list_line_id in number,
2885 p_qualifier_group in number,
2886 p_setup_action in varchar2,
2887 x_return_status out NOCOPY varchar2)
2888 IS
2889 BEGIN
2890 IF FND_PROFILE.VALUE('QP_PATTERN_SEARCH') = 'M' OR FND_PROFILE.VALUE('QP_PATTERN_SEARCH') = 'P' OR FND_PROFILE.VALUE('QP_PATTERN_SEARCH') = 'B' THEN
2891 QP_PS_ATTR_GRP_PVT.Line_Pattern_Main(
2892 p_list_header_id => p_list_header_id,
2893 p_list_line_id => p_list_line_id,
2894 p_qualifier_group => p_qualifier_group,
2895 p_setup_action => p_setup_action);
2896 ELSE
2897 QP_ATTR_GRP_PVT.Line_Pattern_Main(
2898 p_list_header_id => p_list_header_id,
2899 p_list_line_id => p_list_line_id,
2900 p_qualifier_group => p_qualifier_group,
2901 p_setup_action => p_setup_action);
2902 END IF;
2903 EXCEPTION
2904 WHEN FND_API.G_EXC_ERROR THEN
2905 x_return_status := FND_API.G_RET_STS_ERROR;
2906
2907 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2908 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2909
2910 WHEN OTHERS THEN
2911 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2912
2913 END Maintain_line_pattern;
2914
2918 x_return_status out NOCOPY varchar2)
2915 Procedure Maintain_product_pattern(p_list_header_id in number,
2916 p_list_line_id in number,
2917 p_setup_action in varchar2,
2919 IS
2920 BEGIN
2921 IF FND_PROFILE.VALUE('QP_PATTERN_SEARCH') = 'M' OR FND_PROFILE.VALUE('QP_PATTERN_SEARCH') = 'P' OR FND_PROFILE.VALUE('QP_PATTERN_SEARCH') = 'B' THEN
2922 QP_PS_ATTR_GRP_PVT.Product_Pattern_Main(
2923 p_list_header_id => p_list_header_id,
2924 p_list_line_id => p_list_line_id,
2925 p_setup_action => p_setup_action);
2926 ELSE
2927 QP_ATTR_GRP_PVT.Product_Pattern_Main(
2928 p_list_header_id => p_list_header_id,
2929 p_list_line_id => p_list_line_id,
2930 p_setup_action => p_setup_action);
2931 END IF;
2932 EXCEPTION
2933 WHEN FND_API.G_EXC_ERROR THEN
2934 x_return_status := FND_API.G_RET_STS_ERROR;
2935
2936 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2937 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2938
2939 WHEN OTHERS THEN
2940 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2941
2942 END Maintain_product_pattern;
2943
2944 -- pattern
2945
2946 -- Hierarchical Categories (sfiresto)
2947 PROCEDURE Check_Enabled_Func_Areas(p_pte_source_system_id IN NUMBER,
2948 x_return_status OUT NOCOPY VARCHAR2)
2949 IS
2950 l_exists VARCHAR2(1);
2951 l_pte_code VARCHAR2(30);
2952 l_ss_code VARCHAR2(30);
2953 BEGIN
2954
2955 -- Check to see if any enabled functional area mappings exist for
2956 -- the given PTE/SS combination
2957
2958 SELECT 'x'
2959 INTO l_exists
2960 FROM qp_sourcesystem_fnarea_map
2961 WHERE pte_source_system_id = p_pte_source_system_id
2962 AND enabled_flag = 'Y'
2963 AND rownum = 1;
2964
2965 x_return_status := FND_API.G_RET_STS_SUCCESS;
2966
2967 EXCEPTION
2968
2969 WHEN NO_DATA_FOUND THEN
2970
2971 -- If no data was found, we add a warning message to the stack.
2972 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR) THEN
2973
2974 select pte_code, application_short_name
2975 into l_pte_code, l_ss_code
2976 from qp_pte_source_systems
2977 where pte_source_system_id = p_pte_source_system_id;
2978
2979 FND_MESSAGE.set_name('QP', 'QP_NO_FUNC_AREA_WITHIN_PTE');
2980 FND_MESSAGE.set_token('PTE', l_pte_code);
2981 FND_MESSAGE.set_token('SS', l_ss_code);
2982 OE_MSG_PUB.Add;
2983 END IF;
2984
2985
2986 -- As this is a WARNING message, we still return success
2987 x_return_status := FND_API.G_RET_STS_SUCCESS;
2988
2989 WHEN OTHERS THEN
2990 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2991 END Check_Enabled_Func_Areas;
2992
2993 END QP_Delayed_Requests_UTIL;
[Home] [Help]