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