1 PACKAGE BODY QP_Validate_Qualifiers AS
2 /* $Header: QPXLQPQB.pls 120.2.12010000.2 2009/04/28 17:07:10 dnema ship $ */
3
4 -- Global constant holding the package name
5
6 G_PKG_NAME CONSTANT VARCHAR2(30) := 'QP_Validate_Qualifiers';
7
8 /*
9 Function added for bug8359572.
10 It will return true if a qualifier already exist at the same level, having same context,
11 attribute, comparison operator, value from and value to as the input qualifier.
12 */
13
14 Function Check_Duplicate_Qualifier (
15 p_QUALIFIERS_rec IN QP_Qualifier_Rules_PUB.Qualifiers_Rec_Type
16 )
17 RETURN BOOLEAN
18 IS
19 l_count NUMBER := 0;
20 BEGIN
21
22 IF p_QUALIFIERS_rec.qualifier_rule_id <> FND_API.G_MISS_NUM
23 OR p_QUALIFIERS_rec.qualifier_rule_id IS NOT NULL
24 THEN
25 SELECT 1 INTO l_count
26 FROM DUAL
27 WHERE EXISTS( SELECT qualifier_id
28 FROM qp_qualifiers
29 WHERE qualifier_rule_id = p_QUALIFIERS_rec.qualifier_rule_id
30 AND qualifier_grouping_no = p_QUALIFIERS_rec.qualifier_grouping_no
31 AND qualifier_context = p_QUALIFIERS_rec.qualifier_context
32 AND qualifier_attribute = p_QUALIFIERS_rec.qualifier_attribute
33 AND qualifier_attr_value = p_QUALIFIERS_rec.qualifier_attr_value
34 AND COMPARISON_OPERATOR_CODE = p_QUALIFIERS_rec.comparison_operator_code
35 AND nvl(QUALIFIER_ATTR_VALUE_TO,FND_API.G_MISS_NUM) =
36 nvl(p_QUALIFIERS_rec.qualifier_attr_value_to,FND_API.G_MISS_NUM)
37 AND qualifier_id <> p_QUALIFIERS_rec.qualifier_id );
38 ELSE
39 SELECT 1 INTO l_count
40 FROM DUAL
41 WHERE EXISTS ( SELECT qualifier_id
42 FROM qp_qualifiers
43 WHERE list_header_id = p_QUALIFIERS_rec.list_header_id
44 AND qualifier_grouping_no = p_QUALIFIERS_rec.qualifier_grouping_no
45 AND qualifier_context = p_QUALIFIERS_rec.qualifier_context
46 AND qualifier_attribute = p_QUALIFIERS_rec.qualifier_attribute
47 AND qualifier_attr_value = p_QUALIFIERS_rec.qualifier_attr_value
48 AND COMPARISON_OPERATOR_CODE = p_QUALIFIERS_rec.comparison_operator_code
49 AND nvl(QUALIFIER_ATTR_VALUE_TO,FND_API.G_MISS_NUM) =
50 nvl(p_QUALIFIERS_rec.qualifier_attr_value_to,FND_API.G_MISS_NUM)
51 AND qualifier_rule_id is null
52 AND list_line_id = p_QUALIFIERS_rec.list_line_id
53 AND qualifier_id <> p_QUALIFIERS_rec.qualifier_id );
54 END IF;
55
56 RETURN TRUE;
57
58 EXCEPTION
59 WHEN NO_DATA_FOUND THEN
60
61 RETURN FALSE;
62
63 WHEN OTHERS THEN
64
65 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
66 THEN
67 OE_MSG_PUB.Add_Exc_Msg
68 ( G_PKG_NAME
69 , 'Check_Duplicate_Qualifier'
70 );
71 END IF;
72
73 RAISE FND_API.G_EXC_ERROR;
74
75 END Check_Duplicate_Qualifier;
76
77
78 FUNCTION Check_Duplicate_Start_date(p_qualifier_grouping_no IN number,
79 p_start_date IN date,
80 p_qualifier_rule_id IN number,
81 p_qualifier_id IN number)RETURN BOOLEAN IS
82 CURSOR c_QualifierStartDate(p_qualifier_grouping_no number,
83 p_start_date date,
84 p_qualifier_rule_id number,
85 p_qualifier_id number) is
86 SELECT 'CHANGED'
87 FROM QP_QUALIFIERS
88 WHERE qualifier_rule_id = p_qualifier_rule_id
89 AND qualifier_grouping_no = p_qualifier_grouping_no
90 AND start_date_active <> p_start_date
91 AND qualifier_id <> p_qualifier_id;
92
93
94 l_status VARCHAR2(30);
95
96 BEGIN
97
98 OPEN c_QualifierStartDate(p_qualifier_grouping_no,
99 p_start_date,
100 p_qualifier_rule_id,
101 p_qualifier_id);
102 FETCH c_QualifierStartDate INTO l_status;
103 CLOSE c_QualifierStartDate;
104
105 IF l_status = 'CHANGED' then
106
107 RETURN TRUE;
108
109 ELSE
110
111 RETURN FALSE;
112
113 End If;
114
115 END check_duplicate_start_date;
116
117
118 FUNCTION Check_Duplicate_End_Date(p_qualifier_grouping_no IN number,
119 p_end_date IN date,
120 p_qualifier_rule_id IN number,
121 p_qualifier_id IN number)RETURN BOOLEAN IS
122 CURSOR c_QualifierEndDate(p_qualifier_grouping_no number,
123 p_end_date date,
124 p_qualifier_rule_id number,
125 p_qualifier_id number ) is
126 SELECT 'CHANGED'
127 FROM QP_QUALIFIERS
128 WHERE qualifier_rule_id = p_qualifier_rule_id
129 AND qualifier_grouping_no = p_qualifier_grouping_no
130 AND end_date_active <> p_end_date
131 AND qualifier_id <> p_qualifier_id;
132
133
134 l_status VARCHAR2(30);
135
136 BEGIN
137
138 OPEN c_QualifierEndDate(p_qualifier_grouping_no,
139 p_end_date,
140 p_qualifier_rule_id,
141 p_qualifier_id);
142 FETCH c_QualifierEndDate INTO l_status;
143 CLOSE c_QualifierEndDate;
144
145 IF l_status = 'CHANGED' then
146
147 RETURN TRUE;
148
149 ELSE
150
151 RETURN FALSE;
152
153 End If;
154
155 END check_duplicate_end_date;
156
157
158 -- Procedure Entity
159
160 PROCEDURE Entity
161 ( x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2
162 , p_QUALIFIERS_rec IN QP_Qualifier_Rules_PUB.Qualifiers_Rec_Type
163 , p_old_QUALIFIERS_rec IN QP_Qualifier_Rules_PUB.Qualifiers_Rec_Type :=
164 QP_Qualifier_Rules_PUB.G_MISS_QUALIFIERS_REC
165 )
166 IS
167 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
168 l_qualifier_id NUMBER;
169 l_qualifier_rule_id NUMBER;
170 l_comparison_operator_code VARCHAR2(30);
171 l_error_code NUMBER;
172 l_precedence NUMBER;
173 l_datatype FND_FLEX_VALUE_SETS.Format_type%TYPE;
174 l_value_error VARCHAR2(1);
175 l_context_error VARCHAR2(1);
176 l_attribute_error VARCHAR2(1);
177 l_list_header_id NUMBER;
178 l_list_line_id NUMBER;
179 l_gsa_indicator VARCHAR2(1);
180 l_customer_gsa_indicator VARCHAR2(1);
181 l_list_type_code VARCHAR2(30) := '';
182
183 l_context_type VARCHAR2(30);
184 l_sourcing_enabled VARCHAR2(1);
185 l_sourcing_status VARCHAR2(1);
186 l_sourcing_method VARCHAR2(30);
187
188 l_modifier_level_code VARCHAR2(30);
189 l_segment_level VARCHAR2(30);
190 x_attribute_code VARCHAR2(80);
191 x_segment_name VARCHAR2(30);
192
193 -- start 2091362
194 p_attr_count number;
195 p_product_attribute_context VARCHAR2(30);
196 p_product_attribute VARCHAR2(240);
197 p_product_attr_value VARCHAR2(240);
198 l_qp_status VARCHAR2(1);
199 l_start_date_active DATE;
200 l_end_date_active DATE;
201 l_list_header_id_1 NUMBER;
202 -- end bug2091362
203
204 BEGIN
205
206 -- Check whether Source System Code matches
207 -- mkarya for bug 1728764, Prevent update of Trade Management Data in QP
208 QP_UTIL.Check_Source_System_Code
209 (p_list_header_id => p_QUALIFIERS_rec.list_header_id,
210 p_list_line_id => p_QUALIFIERS_rec.list_line_id,
211 x_return_status => l_return_status
212 );
213
214 -- Check required attributes.
215
216
217 --dbms_output.put_line('entity validation for qualifier id');
218 oe_debug_pub.add('entity validation for qualifier id');
219 oe_debug_pub.add('entity validation for qualifier id'||p_QUALIFIERS_rec.qualifier_id);
220
221
222
223
224 IF p_QUALIFIERS_rec.qualifier_id IS NULL
225 THEN
226
227 l_return_status := FND_API.G_RET_STS_ERROR;
228
229 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR)
230 THEN
231
232 FND_MESSAGE.SET_NAME('QP','QP_ATTRIBUTE_REQUIRED');
233 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','Qualifier Id');
234 OE_MSG_PUB.Add;
235
236 END IF;
237
238 END IF;
239
240 --dbms_output.put_line('entity validation for qualifier id status '|| l_return_status);
241 oe_debug_pub.add('entity null validation for qualifier id status '|| l_return_status);
242 --dbms_output.put_line('entity validation for qualifier grouping no');
243 oe_debug_pub.add('entity validation for qualifier grouping no');
244
245
246
247
248 --
249 -- Check rest of required attributes here.
250 --
251
252
253
254 IF p_QUALIFIERS_rec.qualifier_grouping_no IS NULL
255 THEN
256
257 l_return_status := FND_API.G_RET_STS_ERROR;
258
259 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR)
260 THEN
261
262 FND_MESSAGE.SET_NAME('QP','QP_ATTRIBUTE_REQUIRED');
263 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',QP_PRC_UTIL.Get_Attribute_Name('QUALIFIER_GROUPING_NO')); -- Fix For Bug-1974413
264 OE_MSG_PUB.Add;
265
266 END IF;
267
268 END IF;
269
270
271 --oe_debug_pub.add('entity null validation for qualifier group id status '|| l_return_status);
272 --dbms_output.put_line('entity validation for qualifier group id status '|| l_return_status);
273
274 --dbms_output.put_line('entity validation for qualifier attr_value');
275
276 --oe_debug_pub.add('entity validation for attr_value with value as '
277 -- || p_QUALIFIERS_rec.qualifier_attr_value);
278
279
280 -- qualifier_attr_value can be null when the operator is
281 --'Between'.This is to proivde 'less than ' functionality
282 -- using 'between ' operator.
283 --fix for the bug 1253121.
284
285 IF p_QUALIFIERS_rec.comparison_operator_code <> 'BETWEEN' AND
286 p_QUALIFIERS_rec.qualifier_attr_value IS NULL
287 THEN
288
289 l_return_status := FND_API.G_RET_STS_ERROR;
290
291 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR)
292 THEN
293
294 FND_MESSAGE.SET_NAME('QP','QP_ATTRIBUTE_REQUIRED');
295 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',QP_PRC_UTIL.Get_Attribute_Name('QUALIFIER_ATTR_VALUE')); -- Fix FOr Bug-1974413
296 OE_MSG_PUB.Add;
297
298 END IF;
299
300 END IF;
301
302
303
304
305 --dbms_output.put_line('entity validation for qualifier attr value '|| l_return_status);
306 oe_debug_pub.add('entity validation for qualifier attr value '|| l_return_status);
307
308
309 --dbms_output.put_line('entity validation for qualifier attribute');
310
311
312
313
314 IF p_QUALIFIERS_rec.qualifier_attribute IS NULL
315 THEN
316
317 l_return_status := FND_API.G_RET_STS_ERROR;
318
319 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR)
320 THEN
321
322 FND_MESSAGE.SET_NAME('QP','QP_ATTRIBUTE_REQUIRED');
323 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','Attribute');
324 OE_MSG_PUB.Add;
325
326 END IF;
327
328 END IF;
329
330
331
332 --dbms_output.put_line('entity validation for qualifier attr '|| l_return_status);
333 oe_debug_pub.add('entity null validation for qualifier attr '|| l_return_status);
334
335
336
337 --dbms_output.put_line('entity validation for qualifier context');
338
339
340
341 IF p_QUALIFIERS_rec.qualifier_context IS NULL
342 THEN
343
344 l_return_status := FND_API.G_RET_STS_ERROR;
345
346 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR)
347 THEN
348
349 FND_MESSAGE.SET_NAME('QP','QP_ATTRIBUTE_REQUIRED');
350 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',QP_PRC_UTIL.Get_Attribute_Name('QUALIFIER_CONTEXT')); -- Fix For Bug-1974413
351 OE_MSG_PUB.Add;
352
353 END IF;
354
355 END IF;
356
357
358 --dbms_output.put_line('entity null validation for qualifier context '|| l_return_status);
359 oe_debug_pub.add('entity validation for qualifier context '|| l_return_status);
360 --dbms_output.put_line('entity validation for comparison operator ');
361
362
363
364
365 IF p_QUALIFIERS_rec.comparison_operator_code IS NULL
366
367 THEN
368
369 l_return_status := FND_API.G_RET_STS_ERROR;
370
371 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR)
372 THEN
373
374 FND_MESSAGE.SET_NAME('QP','QP_ATTRIBUTE_REQUIRED');
375 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',QP_PRC_UTIL.Get_Attribute_Name('COMPARISON_OPERATOR_CODE')); -- Fix For Bug-1974413
376 OE_MSG_PUB.Add;
377
378 END IF;
379
380 END IF;
381
382
383
384
385 --dbms_output.put_line('entity validation for compari '|| l_return_status);
386 oe_debug_pub.add('entity validation for compari '|| l_return_status);
387 --dbms_output.put_line('entity validation for excluder flag operator ');
388
389 /* Added for bug2368511 */
390
391 IF p_QUALIFIERS_rec.list_line_id IS NULL
392
393 THEN
394
395 l_return_status := FND_API.G_RET_STS_ERROR;
396
397 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR)
398 THEN
399
400 FND_MESSAGE.SET_NAME('QP','QP_ATTRIBUTE_REQUIRED');
401 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','List Line Id');
402 OE_MSG_PUB.Add;
403
404 END IF;
405
406 END IF;
407
408 oe_debug_pub.add('entity validation for List Line Id '|| l_return_status);
409
410
411 IF p_QUALIFIERS_rec.excluder_flag IS NULL
412 THEN
413
414 l_return_status := FND_API.G_RET_STS_ERROR;
415
416 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR)
417 THEN
418
419 FND_MESSAGE.SET_NAME('QP','QP_ATTRIBUTE_REQUIRED');
420 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',QP_PRC_UTIL.Get_Attribute_Name('EXCLUDER_FLAG')); -- FIx For Bug-1974413
421 OE_MSG_PUB.Add;
422
423 END IF;
424
425 END IF;
426
427
428
429
430 --dbms_output.put_line('entity validation for excluder '|| l_return_status);
431 oe_debug_pub.add('entity validation for excluder '|| l_return_status);
432 -- Return Error if a required attribute is missing.
433
434
435
436 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
437
438 RAISE FND_API.G_EXC_ERROR;
439
440 END IF;
441
442
443 -- Check for duplicate qualifier bug8359572
444
445 IF Check_Duplicate_Qualifier(p_QUALIFIERS_rec)
446 THEN
447
448 l_return_status := FND_API.G_RET_STS_ERROR;
449
450 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR)
451 THEN
452
453 FND_MESSAGE.SET_NAME('QP','QP_DUPL_QUAL_EXISTS');
454
455 FND_MESSAGE.SET_TOKEN('CONTEXT',
456 QP_UTIL.Get_Context('QP_ATTR_DEFNS_QUALIFIER',p_QUALIFIERS_rec.qualifier_context));
457
458 QP_UTIL.Get_Attribute_Code('QP_ATTR_DEFNS_QUALIFIER',
459 p_QUALIFIERS_rec.qualifier_context,
460 p_QUALIFIERS_rec.qualifier_attribute,
461 x_attribute_code,
462 x_segment_name);
463 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',x_attribute_code);
464 FND_MESSAGE.SET_TOKEN('QUAL_GRPNG_NO',p_QUALIFIERS_rec.qualifier_grouping_no);
465
466 OE_MSG_PUB.Add;
467
468 END IF;
469 END IF;
470
471 -- Changes for bug 8359572 ends.
472
473 --
474 -- Check conditionally required attributes here.
475 --
476
477
478 --
479 -- Validate attribute dependencies here.
480 --
481 IF p_QUALIFIERS_rec.list_header_id IS NOT NULL
482 THEN
483 BEGIN
484 select list_type_code
485 into l_list_type_code
486 from qp_list_headers_b
487 where list_header_id = p_QUALIFIERS_rec.list_header_id;
488 EXCEPTION
489 WHEN OTHERS THEN
490 l_list_type_code := '';
491 END;
492 END IF;
493
494 IF p_QUALIFIERS_rec.qualifier_context = 'VOLUME' AND
495 p_QUALIFIERS_rec.qualifier_attribute = 'QUALIFIER_ATTRIBUTE10' AND
496 l_list_type_code in ('PRL', 'AGR')
497 -- Qualifier Attr 'Order Amount' under the 'Volume' Qualfier context
498 THEN
499 l_return_status := FND_API.G_RET_STS_ERROR;
500
501 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR)
502 THEN
503 FND_MESSAGE.SET_NAME('QP','QP_INVALID_ATTRIBUTE');
504 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','Qualifier Attribute');
505 OE_MSG_PUB.Add;
506 END IF;
507 END IF;
508
509 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
510 RAISE FND_API.G_EXC_ERROR;
511 END IF;
512
513 -- Validate start_date active and end_date active
514 --end date active must be greater than start date active
515
516 --dbms_output.put_line('entity validation for start-date ');
517
518
519
520 /*IF nvl(p_QUALIFIERS_rec.end_date_active,
521 TO_DATE('01-01-1999','MM-DD-YYYY')) <
522 nvl(p_QUALIFIERS_rec.start_date_active,
523 TO_DATE('01-01-1955','MM-DD-YYYY'))*/
524 IF nvl(p_QUALIFIERS_rec.end_date_active,
525 p_QUALIFIERS_rec.start_date_active ) <
526 nvl(p_QUALIFIERS_rec.start_date_active,
527 p_QUALIFIERS_rec.end_date_active)
528 THEN
529 l_return_status := FND_API.G_RET_STS_ERROR;
530
531 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR)
532 THEN
533
534 FND_MESSAGE.SET_NAME('QP','QP_STRT_DATE_BFR_END_DATE');
535 --FND_MESSAGE.SET_TOKEN('ATTRIBUTE','Start Date active');
536 OE_MSG_PUB.Add;
537
538 END IF;
539
540 END IF;
541
542
543 --dbms_output.put_line('entity validation for start_date '||l_return_status);
544 oe_debug_pub.add('entity validation for start_date '||l_return_status);
545 --dbms_output.put_line('entity validation for value to ');
546
547
548
549 --Validate Qualifier_attr_value_to and Qualifier_attr_value
550 -- if comparison operator code is BETWEEN then both
551 --qualifier attr_value _to or qualifier_attr_value are required
552 -- for all other operators ,qualifier_attr_value_to must be null.
553
554 IF UPPER(p_QUALIFIERS_rec.comparison_operator_code) = 'BETWEEN' AND
555 (p_QUALIFIERS_rec.qualifier_attr_value_to IS NULL OR
556 p_QUALIFIERS_rec.qualifier_attr_value IS NULL)
557 THEN
558 l_return_status := FND_API.G_RET_STS_ERROR;
559
560 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR)
561 THEN
562
563 FND_MESSAGE.SET_NAME('QP','QP_ATTRIBUTE_REQUIRED' );
564 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',QP_PRC_UTIL.Get_Attribute_Name('QUALIFIER_ATTR_VALUE_TO')||'/'||
565 QP_PRC_UTIL.Get_Attribute_Name('QUALIFIER_ATTR_VALUE')); -- Fix For Bug-1974413
566 OE_MSG_PUB.Add;
567
568 END IF;
569
570 END IF;
571
572
573 --validation for canonical form for value to
574
575 l_error_code:=QP_UTIL.validate_num_date(p_QUALIFIERS_rec.qualifier_datatype,
576 p_QUALIFIERS_rec.qualifier_attr_value_to);
577 IF l_error_code <> 0 THEN
578
579 l_return_status := FND_API.G_RET_STS_ERROR;
580
581 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR)
582 THEN
583 FND_MESSAGE.SET_NAME('QP','QP_INVALID_ATTRIBUTE');
584 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','Value To ');
585 OE_MSG_PUB.Add;
586 END IF;
587
588 END IF;
589
590 -- End of validation for canonical form on value to
591
592
593
594 --dbms_output.put_line('entity validation for value to '||l_return_status);
595 oe_debug_pub.add('entity validation for value to '||l_return_status);
596
597
598
599 --Other Validations
600
601
602 --validate for start_date active to the same within a group for a qualifier rule id
603
604 If Check_Duplicate_start_date(p_QUALIFIERS_rec.qualifier_grouping_no,
605 p_QUALIFIERS_rec.start_date_active,
606 p_QUALIFIERS_rec.qualifier_rule_id,
607 p_QUALIFIERS_rec.qualifier_id) THEN
608 l_return_status := FND_API.G_RET_STS_ERROR;
609
610 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR)
611 Then
612 FND_MESSAGE.SET_NAME('QP','QP_DATE_WITHIN_GRPNO_REQ_SAME');
613 FND_MESSAGE.SET_TOKEN('ATTRIBUTE', 'Start Date Active');
614 OE_MSG_PUB.Add;
615 END IF;
616 END IF;
617
618
619 If Check_Duplicate_end_date(p_QUALIFIERS_rec.qualifier_grouping_no,
620 p_QUALIFIERS_rec.end_date_active,
621 p_QUALIFIERS_rec.qualifier_rule_id,
622 p_QUALIFIERS_rec.qualifier_id) THEN
623 l_return_status := FND_API.G_RET_STS_ERROR;
624
625 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR)
626 Then
627 FND_MESSAGE.SET_NAME('QP','QP_DATE_WITHIN_GRPNO_REQ_SAME');
628 FND_MESSAGE.SET_TOKEN('ATTRIBUTE', 'End Date Active');
629 OE_MSG_PUB.Add;
630 END IF;
631 END IF;
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649 --Validate Qualifier Id for duplicate values.
650
651
652 --dbms_output.put_line('entity validation for duplicate id' ||p_QUALIFIERS_rec.qualifier_id);
653
654
655
656 /* SELECT qualifier_id
657 INTO l_qualifier_id
658 FROM QP_QUALIFIERS
659 WHERE qualifier_id = p_QUALIFIERS_rec.qualifier_id;
660
661 If SQL%NOTFOUND
662 Then
663 null;
664 Else
665 l_return_status := FND_API.G_RET_STS_ERROR;
666 --dbms_output.put_line('entity validation for duplicaet id '||l_return_status);
667
668 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR)
669 THEN
670
671 FND_MESSAGE.SET_NAME('QP','QP_ATTRIBUTE_REQUIRED' );
672 -- FND_MESSAGE.SET_NAME('QP','QP_DUPLICATE_ATTRIBUTE');
673 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','Qualifier Id');
674 OE_MSG_PUB.Add;
675 END IF;
676
677 End If;*/
678
679 --dbms_output.put_line('entity validation for duplicaet id '||l_return_status);
680 oe_debug_pub.add('entity validation for duplicaet id '||l_return_status);
681
682
683
684
685 --Validate Comparison Operator Code for Valid Values.
686
687 --dbms_output.put_line('entity validation for compar 2 ');
688
689
690
691 SELECT lookup_code
692 INTO l_comparison_operator_code
693 FROM QP_LOOKUPS
694 WHERE LOOKUP_TYPE = 'COMPARISON_OPERATOR'
695 AND LOOKUP_CODE = UPPER(p_QUALIFIERS_rec.comparison_operator_code);
696
697 If SQL%NOTFOUND
698 Then
699
700 l_return_status := FND_API.G_RET_STS_ERROR;
701
702 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR)
703 THEN
704
705 FND_MESSAGE.SET_NAME('QP','QP_ATTRIBUTE_REQUIRED' );
706 --FND_MESSAGE.SET_NAME('QP','QP_INVALID_ATTRIBUTE');
707 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',QP_PRC_UTIL.Get_Attribute_Name('COMPARISON_OPERATOR_CODE')); -- Fix For Bug-1974413
708 OE_MSG_PUB.Add;
709 END IF;
710
711 End If;
712
713
714 --dbms_output.put_line('entity validation for compa2 '||l_return_status);
715 oe_debug_pub.add('entity validation for compa2 '||l_return_status);
716
717
718
719 --Validate Qualifier_Context , Qualifier_attribute ,Qualifier_Attr Value
720 --qualifier_datatype,qualifier_precedence
721
722
723 --dbms_output.put_line('for context ,attribute,value,datatype,precedence');
724
725 if (p_QUALIFIERS_rec.qualifier_datatype = 'X' and p_QUALIFIERS_rec.comparison_operator_code = 'BETWEEN' ) THEN
726
727 IF fnd_date.canonical_to_date(p_QUALIFIERS_rec.qualifier_attr_value_to) <
728 fnd_date.canonical_to_date(p_QUALIFIERS_rec.qualifier_attr_value)
729 THEN
730
731 l_return_status := FND_API.G_RET_STS_ERROR;
732
733 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR)
734 THEN
735 FND_MESSAGE.SET_NAME('QP','QP_STRT_DATE_BFR_END_DATE');
736 OE_MSG_PUB.Add;
737
738 END IF;
739
740 END IF;
741
742 END IF;
743
744 QP_UTIL.validate_qp_flexfield(flexfield_name =>'QP_ATTR_DEFNS_QUALIFIER'
745 ,context =>p_QUALIFIERS_rec.qualifier_context
746 ,attribute =>p_QUALIFIERS_rec.qualifier_attribute
747 ,value =>p_QUALIFIERS_rec.qualifier_attr_value
748 ,application_short_name => 'QP'
749 ,context_flag =>l_context_error
750 ,attribute_flag =>l_attribute_error
751 ,value_flag =>l_value_error
752 ,datatype =>l_datatype
753 ,precedence =>l_precedence
754 ,error_code =>l_error_code
755 );
756
757 If (l_context_error = 'N' AND l_error_code = 7) -- invalid context
758 Then
759 l_return_status := FND_API.G_RET_STS_ERROR;
760
761 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR)
762 THEN
763
764 FND_MESSAGE.SET_NAME('QP','QP_ATTRIBUTE_REQUIRED' );
765 --FND_MESSAGE.SET_NAME('QP','QP_INVALID_ATTRIBUTE');
766 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',QP_PRC_UTIL.Get_Attribute_Name('QUALIFIER_CONTEXT')); -- Fix For Bug-1974413
767 OE_MSG_PUB.Add;
768 END IF;
769
770 End If;
771
772
773 --dbms_output.put_line('for context '||l_return_status);
774
775 --dbms_output.put_line('for context ,attribute,value,datatype,precedence');
776
777
778
779 If l_attribute_error = 'N' AND l_error_code = 8 -- invalid Attribute
780 Then
781 l_return_status := FND_API.G_RET_STS_ERROR;
782
783 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR)
784 THEN
785
786 FND_MESSAGE.SET_NAME('QP','QP_INVALID_ATTRIBUTE');
787 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',' Attribute');
788 OE_MSG_PUB.Add;
789 END IF;
790
791 End If;
792
793
794 --dbms_output.put_line('for attributr '||l_return_status);
795 oe_debug_pub.add('for context '||l_return_status);
796
797
798 --- validate qualifier_attr_value only if comparison operator is
799 -- '='
800
801 IF p_QUALIFIERS_rec.comparison_operator_code = '=' Then
802
803 If l_value_error = 'N' AND l_error_code = 9 -- invalid value
804 Then
805 l_return_status := FND_API.G_RET_STS_ERROR;
806
807 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR)
808 THEN
809
810 FND_MESSAGE.SET_NAME('QP','QP_INVALID_ATTRIBUTE');
811 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',' Value From ');
812 --OE_MSG_PUB.Add;
813 OE_MSG_PUB.Add;
814 END IF;
815 End If;
816 END IF;
817
818 --- Validation for GSA Discounts - Customer must be GSA
819
820 IF p_QUALIFIERS_rec.qualifier_context = 'CUSTOMER'
821 AND p_QUALIFIERS_rec.qualifier_attribute = 'QUALIFIER_ATTRIBUTE2'
822 AND p_QUALIFIERS_rec.list_header_id IS NOT NULL
823 THEN
824
825 oe_debug_pub.add('attcching');
826 BEGIN
827
828 select gsa_indicator
829 into l_gsa_indicator
830 from qp_list_headers_b
831 where list_header_id = p_QUALIFIERS_rec.list_header_id;
832
833 IF nvl(l_gsa_indicator,'N') = 'Y'
834 THEN
835
836 oe_debug_pub.add('GSA = Y');
837 select decode(party.party_type,'ORGANIZATION',party.gsa_indicator_flag,'N')
838 into l_customer_gsa_indicator
839 from hz_cust_accounts cust_acct,hz_parties party
840 where cust_acct.party_id = party.party_id and
841 cust_acct.cust_account_id = to_number(p_QUALIFIERS_rec.qualifier_attr_value);
842
843 IF SQL%NOTFOUND
844 THEN
845
846 oe_debug_pub.add('customer GSA not found');
847 l_return_status := FND_API.G_RET_STS_ERROR;
848
849 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR)
850 THEN
851
852 FND_MESSAGE.SET_NAME('QP','QP_INVALID_GSA_CUSTOMER');
853 OE_MSG_PUB.Add;
854
855 END IF;
856
857 ELSIF nvl(l_customer_gsa_indicator,'N') <> 'Y'
858 THEN
859
860 oe_debug_pub.add('customer GSA = '||l_customer_gsa_indicator);
861 l_return_status := FND_API.G_RET_STS_ERROR;
862
863 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR)
864 THEN
865
866 FND_MESSAGE.SET_NAME('QP','QP_INVALID_GSA_CUSTOMER');
867 OE_MSG_PUB.Add;
868
869 END IF;
870
871 END IF;
872
873 END IF;
874
875 EXCEPTION
876 when no_data_found then
877 oe_debug_pub.add('exception GSA indicator in header is null');
878 null;
879
880 END;
881
882 END IF;
883
884 --dbms_output.put_line('for value,'||l_return_status);
885 oe_debug_pub.add('for value,'||l_return_status);
886
887 --dbms_output.put_line('org precede '||p_QUALIFIERS_rec.qualifier_precedence);
888 --dbms_output.put_line('n precede '||l_precedence);
889 oe_debug_pub.add('org precede '||p_QUALIFIERS_rec.qualifier_precedence);
890 oe_debug_pub.add('n precede '||l_precedence);
891
892 /*If p_QUALIFIERS_rec.qualifier_precedence <> l_precedence --- invalid precedence
893 Then
894 l_return_status := FND_API.G_RET_STS_ERROR;
895
896 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR)
897 THEN
898
899 --FND_MESSAGE.SET_NAME('QP','QP_ATTRIBUTE_REQUIRED' );
900 FND_MESSAGE.SET_NAME('QP','QP_INVALID_ATTRIBUTE');
901 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',QP_PRC_UTIL.Get_Attribute_Name('QUALIFIER_PRECEDENCE')); -- Fix For Bug-1974413
902 OE_MSG_PUB.Add;
903 END IF;
904
905
906 End If;*/
907
908
909
910 --dbms_output.put_line('for precedence'||l_return_status);
911
912
913
914 If p_QUALIFIERS_rec.qualifier_datatype <> l_datatype --- invalid qualifier datatype
915 Then
916 l_return_status := FND_API.G_RET_STS_ERROR;
917
918 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR)
919 THEN
920 --FND_MESSAGE.SET_NAME('QP','QP_ATTRIBUTE_REQUIRED' );
921 FND_MESSAGE.SET_NAME('QP','QP_INVALID_ATTRIBUTE');
922 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','Qualifier Datatype ');
923 OE_MSG_PUB.Add;
924 END IF;
925
926 End If;
927
928
929
930 --dbms_output.put_line('for datatype,'||l_return_status);
931 oe_debug_pub.add('qualifier datatype,'||l_return_status);
932
933
934
935 --validation for canonical form
936
937 l_error_code:=QP_UTIL.validate_num_date(p_QUALIFIERS_rec.qualifier_datatype,
938 p_QUALIFIERS_rec.qualifier_attr_value);
939 IF l_error_code <> 0 THEN
940
941 l_return_status := FND_API.G_RET_STS_ERROR;
942
943 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR)
944 THEN
945 FND_MESSAGE.SET_NAME('QP','QP_INVALID_ATTRIBUTE');
946 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','Value From ');
947 OE_MSG_PUB.Add;
948 END IF;
949
950 END IF;
951 --dbms_output.put_line('for cano of value from ,'||l_return_status);
952
953 -- End of validation for canonical form on value from
954
955
956 -- Validate qualifier rule id when not null
957
958
959
960 /* IF p_QUALIFIERS_rec.qualifier_rule_id IS NOT NULL
961 THEN
962 SELECT qualifier_rule_id
963 INTO l_qualifier_rule_id
964 FROM QP_QUALIFIER_RULES
965 WHERE qualifier_rule_id = p_QUALIFIERS_rec.qualifier_rule_id;
966
967 If SQL%NOTFOUND
968 Then
969 l_return_status := FND_API.G_RET_STS_ERROR;
970
971 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR)
972 THEN
973 FND_MESSAGE.SET_NAME('QP','QP_ATTRIBUTE_REQUIRED' );
974 -- FND_MESSAGE.SET_NAME('QP','QP_INVALID_ATTRIBUTE');
975 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','Qualifier qualifier rule id ');
976 OE_MSG_PUB.Add;
977 END IF;
978 End If;
979 END IF;*/
980
981
982
983
984 -- Validate created from rule id when not null
985
986 /* IF p_QUALIFIERS_rec.created_from_rule_id IS NOT NULL
987 THEN
988 SELECT qualifier_rule_id
989 INTO l_qualifier_rule_id
990 FROM QP_QUALIFIER_RULES
991 WHERE qualifier_rule_id = p_QUALIFIERS_rec.created_from_rule_id;
992
993 If SQL%NOTFOUND
994 Then
995 l_return_status := FND_API.G_RET_STS_ERROR;
996
997 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR)
998 THEN
999 FND_MESSAGE.SET_NAME('QP','QP_ATTRIBUTE_REQUIRED' );
1000 --FND_MESSAGE.SET_NAME('QP','QP_INVALID_ATTRIBUTE');
1001 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','created from rule id ');
1002 OE_MSG_PUB.Add;
1003 END IF;
1004 End If;
1005 END IF;*/
1006
1007
1008
1009 -- Validate list header id when not null
1010
1011 /* IF p_QUALIFIERS_rec.list_header_id IS NOT NULL
1012 THEN
1013 SELECT list_header_id
1014 INTO l_list_header_id
1015 FROM QP_LIST_HEADERS_B
1016 WHERE list_header_id = p_QUALIFIERS_rec.list_header_id;
1017
1018 If SQL%NOTFOUND
1019 Then
1020 l_return_status := FND_API.G_RET_STS_ERROR;
1021
1022 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR)
1023 THEN
1024 FND_MESSAGE.SET_NAME('QP','QP_ATTRIBUTE_REQUIRED' );
1025 --FND_MESSAGE.SET_NAME('QP','QP_INVALID_ATTRIBUTE');
1026 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','List Header id ');
1027 OE_MSG_PUB.Add;
1028 END IF;
1029 End If;
1030 END IF; */
1031
1032
1033
1034 -- Validate list line id when not null
1035
1036 /* IF p_QUALIFIERS_rec.list_line_id IS NOT NULL
1037 THEN
1038 SELECT list_line_id
1039 INTO l_list_line_id
1040 FROM QP_LIST_LINES
1041 WHERE list_line_id = p_QUALIFIERS_rec.list_line_id;
1042
1043 If SQL%NOTFOUND
1044 Then
1045 l_return_status := FND_API.G_RET_STS_ERROR;
1046
1047 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR)
1048 THEN
1049 FND_MESSAGE.SET_NAME('QP','QP_ATTRIBUTE_REQUIRED' );
1050 --FND_MESSAGE.SET_NAME('QP','QP_INVALID_ATTRIBUTE');
1051 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','List Line id ');
1052 OE_MSG_PUB.Add;
1053 END IF;
1054 End If;
1055 END IF; */
1056
1057
1058 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1059
1060 RAISE FND_API.G_EXC_ERROR;
1061
1062 END IF;
1063
1064 --- start bug2091362
1065 l_qp_status := QP_UTIL.GET_QP_STATUS;
1066
1067 IF (fnd_profile.value('QP_ALLOW_DUPLICATE_MODIFIERS') <> 'Y'
1068 and p_qualifiers_rec.list_line_id <> -1
1069 and l_qp_status = 'S') THEN
1070
1071 select start_date_active, end_date_active , list_header_id
1072 into l_start_date_active, l_end_date_active, l_list_header_id_1
1073 from qp_list_lines
1074 where list_line_id = p_qualifiers_rec.list_line_id;
1075
1076 OE_Debug_Pub.add ( 'Value Set 1' || l_start_date_active || l_end_date_active );
1077
1078 -- oe_debug_pub.add('about to delete a request to check duplicate modifier list lines without product attribute');
1079
1080 QP_delayed_requests_pvt.Delete_Request
1081 ( p_entity_code => QP_GLOBALS.G_ENTITY_ALL
1082 , p_entity_id => p_qualifiers_rec.list_line_id
1083 , p_request_Type => QP_GLOBALS.G_DUPLICATE_MODIFIER_LINES
1084 , x_return_status => l_return_status
1085 );
1086
1087 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1088
1089 oe_debug_pub.add('failed in logging a delayed request ');
1090
1091 RAISE FND_API.G_EXC_ERROR;
1092
1093 END IF;
1094
1095 /* After deleting request if any log a new request to check duplicate modifier list lines with product attribute */
1096
1097 oe_debug_pub.add('about to log a request to check duplicate modifier list lines with product attribute');
1098
1099 select count(*) into p_attr_count
1100 from qp_pricing_attributes
1101 where list_line_id = p_qualifiers_rec.list_line_id;
1102
1103 IF p_attr_count = 0 THEN
1104
1105 QP_DELAYED_REQUESTS_PVT.Log_Request
1106 ( p_entity_code => QP_GLOBALS.G_ENTITY_ALL
1107 , p_entity_id => p_qualifiers_rec.list_line_id
1108 , p_requesting_entity_code => QP_GLOBALS.G_ENTITY_ALL
1109 , p_requesting_entity_id => p_qualifiers_rec.list_line_id
1110 , p_request_type => QP_GLOBALS.G_DUPLICATE_MODIFIER_LINES
1111 , p_param1 => l_list_header_id_1
1112 , p_param2 => fnd_date.date_to_canonical(l_start_date_active) --2752265
1113 , p_param3 => fnd_date.date_to_canonical(l_end_date_active) --2752265
1114 , p_param4 => NULL
1115 , p_param5 => NULL
1116 , p_param6 => NULL
1117 , x_return_status => l_return_status
1118 );
1119
1120 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1121
1122 oe_debug_pub.add('failed in logging a delayed request ');
1123
1124 RAISE FND_API.G_EXC_ERROR;
1125
1126 END IF;
1127
1128 ELSE
1129
1130 Select product_attribute_context, product_attribute, product_attr_value
1131 into p_product_attribute_context, p_product_attribute, p_product_attr_value
1132 from qp_pricing_attributes
1133 where list_line_id = p_qualifiers_rec.list_line_id
1134 and rownum < 2;
1135
1136 QP_DELAYED_REQUESTS_PVT.Log_Request
1137 ( p_entity_code => QP_GLOBALS.G_ENTITY_ALL
1138 , p_entity_id => p_qualifiers_rec.list_line_id
1139 , p_requesting_entity_code => QP_GLOBALS.G_ENTITY_ALL
1140 , p_requesting_entity_id => p_qualifiers_rec.list_line_id
1141 , p_request_type => QP_GLOBALS.G_DUPLICATE_MODIFIER_LINES
1142 , p_param1 => l_list_header_id_1
1143 , p_param2 => fnd_date.date_to_canonical(l_start_date_active) --2752265
1144 , p_param3 => fnd_date.date_to_canonical(l_end_date_active) --2752265
1145 , p_param4 => p_product_attribute_context
1146 , p_param5 => p_product_attribute
1147 , p_param6 => p_product_attr_value
1148 , x_return_status => l_return_status
1149 );
1150
1151 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1152
1153 oe_debug_pub.add('failed in logging a delayed request for duplicate modifiers');
1154
1155 RAISE FND_API.G_EXC_ERROR;
1156
1157 END IF;
1158 END IF;
1159 oe_debug_pub.add('after logging delayed request from qualfiers ');
1160 END IF;
1161
1162
1163
1164 --- end bug2091362
1165
1166
1167 --Raise a warning if the Qualifier Attribute being used in setup
1168 --has a sourcing method of 'ATTRIBUTE MAPPING' but is not sourcing-enabled
1169 --or if its sourcing_status is not 'Y', i.e., the build sourcing conc.
1170 --program has to be run.
1171
1172 IF qp_util.attrmgr_installed = 'Y' THEN
1173
1174 IF p_Qualifiers_rec.qualifier_context IS NOT NULL AND
1175 p_Qualifiers_rec.qualifier_attribute IS NOT NULL
1176 THEN
1177 QP_UTIL.Get_Context_Type('QP_ATTR_DEFNS_QUALIFIER',
1178 p_Qualifiers_rec.qualifier_context,
1179 l_context_type,
1180 l_error_code);
1181
1182 IF l_error_code = 0 THEN --successfully returned context_type
1183
1184 QP_UTIL.Get_Sourcing_Info(l_context_type,
1185 p_Qualifiers_rec.qualifier_context,
1186 p_Qualifiers_rec.qualifier_attribute,
1187 l_sourcing_enabled,
1188 l_sourcing_status,
1189 l_sourcing_method);
1190
1191 IF l_sourcing_method = 'ATTRIBUTE MAPPING' THEN
1192
1193 IF l_sourcing_enabled <> 'Y' THEN
1194
1195 FND_MESSAGE.SET_NAME('QP','QP_ENABLE_SOURCING');
1196 FND_MESSAGE.SET_TOKEN('CONTEXT',
1197 p_Qualifiers_rec.qualifier_context);
1198 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',
1199 p_Qualifiers_rec.qualifier_attribute);
1200 OE_MSG_PUB.Add;
1201
1202 END IF;
1203
1204 IF l_sourcing_status <> 'Y' THEN
1205
1206 IF NOT (
1207 p_qualifiers_rec.qualifier_context= 'MODLIST' AND
1208 p_qualifiers_rec.qualifier_attribute = 'QUALIFIER_ATTRIBUTE10')
1209 THEN
1210 FND_MESSAGE.SET_NAME('QP','QP_BUILD_SOURCING_RULES');
1211 FND_MESSAGE.SET_TOKEN('CONTEXT',
1212 p_Qualifiers_rec.qualifier_context);
1213 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',
1214 p_Qualifiers_rec.qualifier_attribute);
1215 OE_MSG_PUB.Add;
1216 END IF;
1217
1218 END IF;
1219
1220 END IF; --If sourcing_method = 'ATTRIBUTE MAPPING'
1221
1222 END IF; --l_error_code = 0
1223
1224 END IF;--If qualifier_context and qualifier_attribute are NOT NULL
1225
1226 -- Giri, validate that qualifiers attached to Price Lists are or type LINE/BOTH
1227 IF p_qualifiers_rec.list_header_id is not null and
1228 l_list_type_code in ('PRL', 'AGR') THEN
1229 IF p_Qualifiers_rec.qualifier_context IS NOT NULL AND
1230 p_Qualifiers_rec.qualifier_attribute IS NOT NULL THEN
1231 l_segment_level := qp_util.get_segment_level(p_qualifiers_rec.list_header_id
1232 ,p_Qualifiers_rec.qualifier_context
1233 ,p_Qualifiers_rec.qualifier_attribute
1234 );
1235 IF l_segment_level = 'ORDER' THEN
1236 l_return_status := FND_API.G_RET_STS_ERROR;
1237 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR) THEN
1238 qp_util. Get_Attribute_Code(p_FlexField_Name => 'QP_ATTR_DEFNS_QUALIFIER',
1239 p_Context_Name => p_Qualifiers_rec.qualifier_context,
1240 p_attribute => p_Qualifiers_rec.qualifier_attribute,
1241 x_attribute_code => x_attribute_code,
1242 x_segment_name => x_segment_name);
1243 -- The level of attribute (?) ? is not compatible for Price Lists
1244 FND_MESSAGE.SET_NAME('QP','QP_SEGMENT_NOT_ALLOWED_FOR_PL');
1245 FND_MESSAGE.SET_TOKEN('ATTRIBUTE', x_attribute_code);
1246 FND_MESSAGE.SET_TOKEN('SEGMENT_LEVEL', l_segment_level);
1247 OE_MSG_PUB.Add;
1248 END IF;
1249 END IF;
1250 END IF;
1251 END IF;
1252
1253 -- mkarya, validate that line level qualifiers attached to modifier match the modifier level
1254 -- Changes for attribute manager
1255
1256 if p_qualifiers_rec.list_header_id is not null and
1257 p_qualifiers_rec.list_line_id <> -1 and
1258 l_list_type_code not in ('PRL', 'AGR') then
1259
1260 IF p_Qualifiers_rec.qualifier_context IS NOT NULL AND
1261 p_Qualifiers_rec.qualifier_attribute IS NOT NULL
1262 THEN
1263 select modifier_level_code
1264 into l_modifier_level_code
1265 from qp_list_lines
1266 where list_header_id = p_qualifiers_rec.list_header_id
1267 and list_line_id = p_qualifiers_rec.list_line_id;
1268
1269 l_segment_level := qp_util.get_segment_level(p_qualifiers_rec.list_header_id
1270 ,p_Qualifiers_rec.qualifier_context
1271 ,p_Qualifiers_rec.qualifier_attribute
1272 );
1273 if ((l_modifier_level_code in ('LINE', 'LINEGROUP') and l_segment_level = 'ORDER')
1274 OR
1275 (l_modifier_level_code = 'ORDER' and l_segment_level = 'LINE')) then
1276
1277 l_return_status := FND_API.G_RET_STS_ERROR;
1278 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR) THEN
1279 qp_util. Get_Attribute_Code(p_FlexField_Name => 'QP_ATTR_DEFNS_QUALIFIER',
1280 p_Context_Name => p_Qualifiers_rec.qualifier_context,
1281 p_attribute => p_Qualifiers_rec.qualifier_attribute,
1282 x_attribute_code => x_attribute_code,
1283 x_segment_name => x_segment_name);
1284 -- The level of attribute (?) ? is not compatible with modifier level ?.
1285 FND_MESSAGE.SET_NAME('QP','QP_SEGMENT_NOT_ALLOWED');
1286 FND_MESSAGE.SET_TOKEN('ATTRIBUTE', x_attribute_code);
1287 FND_MESSAGE.SET_TOKEN('SEGMENT_LEVEL', l_segment_level);
1288 FND_MESSAGE.SET_TOKEN('MODIFIER_LEVEL', l_modifier_level_code);
1289 OE_MSG_PUB.Add;
1290 END IF; -- check_msg_level
1291
1292 end if; -- compare modifier_level and segment_level
1293
1294 END IF;--If qualifier_context and qualifier_attribute are NOT NULL
1295
1296 end if; -- if line level qualifier attached to modifier
1297
1298 END IF; -- attribute manager installed
1299
1300 -- Done validating entity
1301
1302 x_return_status := l_return_status;
1303
1304 EXCEPTION
1305
1306 WHEN FND_API.G_EXC_ERROR THEN
1307
1308 x_return_status := FND_API.G_RET_STS_ERROR;
1309
1310 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1311
1312 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1313
1314 WHEN OTHERS THEN
1315
1316 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1317
1318 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1319 THEN
1320 OE_MSG_PUB.Add_Exc_Msg
1321 ( G_PKG_NAME
1322 , 'Entity'
1323 );
1324 END IF;
1325
1326 END Entity;
1327
1328 -- Procedure Attributes
1329
1330 PROCEDURE Attributes
1331 ( x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2
1332 , p_QUALIFIERS_rec IN QP_Qualifier_Rules_PUB.Qualifiers_Rec_Type
1333 , p_old_QUALIFIERS_rec IN QP_Qualifier_Rules_PUB.Qualifiers_Rec_Type :=
1334 QP_Qualifier_Rules_PUB.G_MISS_QUALIFIERS_REC
1335 )
1336 IS
1337 BEGIN
1338
1339 x_return_status := FND_API.G_RET_STS_SUCCESS;
1340
1341 -- Validate QUALIFIERS attributes
1342
1343
1344 --dbms_output.put_line('comparison operator');
1345 --dbms_output.put_line('comparison operators is '||nvl(p_QUALIFIERS_rec.comparison_operator_code,'c'));
1346
1347
1348 IF p_QUALIFIERS_rec.comparison_operator_code IS NOT NULL AND
1349 ( p_QUALIFIERS_rec.comparison_operator_code <>
1350 p_old_QUALIFIERS_rec.comparison_operator_code OR
1351 p_old_QUALIFIERS_rec.comparison_operator_code IS NULL )
1352 THEN
1353
1354 --dbms_output.put_line(' inside if not null comparison operator');
1355 IF NOT QP_Validate.Comparison_Operator(p_QUALIFIERS_rec.comparison_operator_code) THEN
1356 x_return_status := FND_API.G_RET_STS_ERROR;
1357 END IF;
1358 END IF;
1359
1360
1361 --dbms_output.put_line('comparison status is '||x_return_status);
1362
1363
1364 --dbms_output.put_line('created_by ');
1365
1366 IF p_QUALIFIERS_rec.created_by IS NOT NULL AND
1367 ( p_QUALIFIERS_rec.created_by <>
1368 p_old_QUALIFIERS_rec.created_by OR
1369 p_old_QUALIFIERS_rec.created_by IS NULL )
1370 THEN
1371 IF NOT QP_Validate.Created_By(p_QUALIFIERS_rec.created_by) THEN
1372 x_return_status := FND_API.G_RET_STS_ERROR;
1373 END IF;
1374 END IF;
1375 --dbms_output.put_line('status is '||x_return_status);
1376
1377
1378
1379 --dbms_output.put_line('create from rule ');
1380
1381 IF p_QUALIFIERS_rec.created_from_rule_id IS NOT NULL AND
1382 ( p_QUALIFIERS_rec.created_from_rule_id <>
1383 p_old_QUALIFIERS_rec.created_from_rule_id OR
1384 p_old_QUALIFIERS_rec.created_from_rule_id IS NULL )
1385 THEN
1386 IF NOT QP_Validate.Created_From_Rule(p_QUALIFIERS_rec.created_from_rule_id) THEN
1387 x_return_status := FND_API.G_RET_STS_ERROR;
1388 END IF;
1389 END IF;
1390
1391 --dbms_output.put_line(' created from status is '||x_return_status);
1392
1393
1394
1395 --dbms_output.put_line('validating creation date');
1396
1397 IF p_QUALIFIERS_rec.creation_date IS NOT NULL AND
1398 ( p_QUALIFIERS_rec.creation_date <>
1399 p_old_QUALIFIERS_rec.creation_date OR
1400 p_old_QUALIFIERS_rec.creation_date IS NULL )
1401 THEN
1402 IF NOT QP_Validate.Creation_Date(p_QUALIFIERS_rec.creation_date) THEN
1403 x_return_status := FND_API.G_RET_STS_ERROR;
1404 END IF;
1405 END IF;
1406 --dbms_output.put_line('status is '||x_return_status);
1407
1408
1409
1410 --dbms_output.put_line('validating end date');
1411
1412 IF p_QUALIFIERS_rec.end_date_active IS NOT NULL AND
1413 ( p_QUALIFIERS_rec.end_date_active <>
1414 p_old_QUALIFIERS_rec.end_date_active OR
1415 p_old_QUALIFIERS_rec.end_date_active IS NULL )
1416 THEN
1417 IF NOT QP_Validate.End_Date_Active(p_QUALIFIERS_rec.end_date_active) THEN
1418 x_return_status := FND_API.G_RET_STS_ERROR;
1419 END IF;
1420 END IF;
1421 --dbms_output.put_line('end adate status is '||x_return_status);
1422
1423
1424
1425 --dbms_output.put_line('validating excluder flag ');
1426
1427 IF p_QUALIFIERS_rec.excluder_flag IS NOT NULL AND
1428 ( p_QUALIFIERS_rec.excluder_flag <>
1429 p_old_QUALIFIERS_rec.excluder_flag OR
1430 p_old_QUALIFIERS_rec.excluder_flag IS NULL )
1431 THEN
1432 IF NOT QP_Validate.Excluder(p_QUALIFIERS_rec.excluder_flag) THEN
1433 x_return_status := FND_API.G_RET_STS_ERROR;
1434 END IF;
1435 END IF;
1436 --dbms_output.put_line(' excluder flag status is '||x_return_status);
1437
1438
1439
1440 --dbms_output.put_line('validating lat_updated by ');
1441
1442 IF p_QUALIFIERS_rec.last_updated_by IS NOT NULL AND
1443 ( p_QUALIFIERS_rec.last_updated_by <>
1444 p_old_QUALIFIERS_rec.last_updated_by OR
1445 p_old_QUALIFIERS_rec.last_updated_by IS NULL )
1446 THEN
1447 IF NOT QP_Validate.Last_Updated_By(p_QUALIFIERS_rec.last_updated_by) THEN
1448 x_return_status := FND_API.G_RET_STS_ERROR;
1449 END IF;
1450 END IF;
1451 --dbms_output.put_line('status is '||x_return_status);
1452
1453
1454
1455 --dbms_output.put_line('validating lat_update date ');
1456
1457 IF p_QUALIFIERS_rec.last_update_date IS NOT NULL AND
1458 ( p_QUALIFIERS_rec.last_update_date <>
1459 p_old_QUALIFIERS_rec.last_update_date OR
1460 p_old_QUALIFIERS_rec.last_update_date IS NULL )
1461 THEN
1462 IF NOT QP_Validate.Last_Update_Date(p_QUALIFIERS_rec.last_update_date) THEN
1463 x_return_status := FND_API.G_RET_STS_ERROR;
1464 END IF;
1465 END IF;
1466 --dbms_output.put_line('status is '||x_return_status);
1467
1468
1469
1470 --dbms_output.put_line('validating lat_update login ');
1471
1472 IF p_QUALIFIERS_rec.last_update_login IS NOT NULL AND
1473 ( p_QUALIFIERS_rec.last_update_login <>
1474 p_old_QUALIFIERS_rec.last_update_login OR
1475 p_old_QUALIFIERS_rec.last_update_login IS NULL )
1476 THEN
1477 IF NOT QP_Validate.Last_Update_Login(p_QUALIFIERS_rec.last_update_login) THEN
1478 x_return_status := FND_API.G_RET_STS_ERROR;
1479 END IF;
1480 END IF;
1481 --dbms_output.put_line('status is '||x_return_status);
1482
1483
1484
1485 --dbms_output.put_line('validating list_header_id ');
1486
1487 IF p_QUALIFIERS_rec.list_header_id IS NOT NULL AND
1488 ( p_QUALIFIERS_rec.list_header_id <>
1489 p_old_QUALIFIERS_rec.list_header_id OR
1490 p_old_QUALIFIERS_rec.list_header_id IS NULL )
1491 THEN
1492 IF NOT QP_Validate.List_Header(p_QUALIFIERS_rec.list_header_id) THEN
1493 x_return_status := FND_API.G_RET_STS_ERROR;
1494 END IF;
1495 END IF;
1496 --dbms_output.put_line('status is '||x_return_status);
1497
1498
1499
1500 --dbms_output.put_line('validating list_line id ');
1501
1502 IF p_QUALIFIERS_rec.list_line_id IS NOT NULL AND
1503 ( p_QUALIFIERS_rec.list_line_id <>
1504 p_old_QUALIFIERS_rec.list_line_id OR
1505 p_old_QUALIFIERS_rec.list_line_id IS NULL )
1506 THEN
1507 IF NOT QP_Validate.List_Line(p_QUALIFIERS_rec.list_line_id) THEN
1508 x_return_status := FND_API.G_RET_STS_ERROR;
1509 END IF;
1510 END IF;
1511 --dbms_output.put_line('status is '||x_return_status);
1512
1513
1514
1515
1516 --dbms_output.put_line('validating program application id ');
1517
1518 IF p_QUALIFIERS_rec.program_application_id IS NOT NULL AND
1519 ( p_QUALIFIERS_rec.program_application_id <>
1520 p_old_QUALIFIERS_rec.program_application_id OR
1521 p_old_QUALIFIERS_rec.program_application_id IS NULL )
1522 THEN
1523 IF NOT QP_Validate.Program_Application(p_QUALIFIERS_rec.program_application_id) THEN
1524 x_return_status := FND_API.G_RET_STS_ERROR;
1525 END IF;
1526 END IF;
1527 --dbms_output.put_line('status is '||x_return_status);
1528
1529
1530
1531
1532 --dbms_output.put_line('validating program id ');
1533
1534 IF p_QUALIFIERS_rec.program_id IS NOT NULL AND
1535 ( p_QUALIFIERS_rec.program_id <>
1536 p_old_QUALIFIERS_rec.program_id OR
1537 p_old_QUALIFIERS_rec.program_id IS NULL )
1538 THEN
1539 IF NOT QP_Validate.Program(p_QUALIFIERS_rec.program_id) THEN
1540 x_return_status := FND_API.G_RET_STS_ERROR;
1541 END IF;
1542 END IF;
1543 --dbms_output.put_line('status is '||x_return_status);
1544
1545
1546
1547 --dbms_output.put_line('validating update date ');
1548
1549 IF p_QUALIFIERS_rec.program_update_date IS NOT NULL AND
1550 ( p_QUALIFIERS_rec.program_update_date <>
1551 p_old_QUALIFIERS_rec.program_update_date OR
1552 p_old_QUALIFIERS_rec.program_update_date IS NULL )
1553 THEN
1554 IF NOT QP_Validate.Program_Update_Date(p_QUALIFIERS_rec.program_update_date) THEN
1555 x_return_status := FND_API.G_RET_STS_ERROR;
1556 END IF;
1557 END IF;
1558 --dbms_output.put_line('status is '||x_return_status);
1559
1560
1561
1562 --dbms_output.put_line('validating qualifier_attribute ');
1563
1564 IF p_QUALIFIERS_rec.qualifier_attribute IS NOT NULL AND
1565 ( p_QUALIFIERS_rec.qualifier_attribute <>
1566 p_old_QUALIFIERS_rec.qualifier_attribute OR
1567 p_old_QUALIFIERS_rec.qualifier_attribute IS NULL )
1568 THEN
1569 IF NOT QP_Validate.Qualifier_Attribute(p_QUALIFIERS_rec.qualifier_attribute) THEN
1570 x_return_status := FND_API.G_RET_STS_ERROR;
1571 END IF;
1572 END IF;
1573 --dbms_output.put_line(' attribute status is '||x_return_status);
1574
1575
1576
1577 --dbms_output.put_line('validating qualifier_attribute val ');
1578
1579 IF p_QUALIFIERS_rec.qualifier_attr_value IS NOT NULL AND
1580 ( p_QUALIFIERS_rec.qualifier_attr_value <>
1581 p_old_QUALIFIERS_rec.qualifier_attr_value OR
1582 p_old_QUALIFIERS_rec.qualifier_attr_value IS NULL )
1583 THEN
1584 IF NOT QP_Validate.Qualifier_Attr_Value(p_QUALIFIERS_rec.qualifier_attr_value) THEN
1585 x_return_status := FND_API.G_RET_STS_ERROR;
1586 END IF;
1587 END IF;
1588 --dbms_output.put_line(' attr value status is '||x_return_status);
1589
1590
1591
1592 --dbms_output.put_line('validating qualifier_attribute valto ');
1593
1594 IF p_QUALIFIERS_rec.qualifier_attr_value_to IS NOT NULL AND
1595 ( p_QUALIFIERS_rec.qualifier_attr_value_to <>
1596 p_old_QUALIFIERS_rec.qualifier_attr_value_to OR
1597 p_old_QUALIFIERS_rec.qualifier_attr_value_to IS NULL )
1598 THEN
1599 IF NOT QP_Validate.Qualifier_Attr_Value_to(p_QUALIFIERS_rec.qualifier_attr_value_to) THEN
1600 x_return_status := FND_API.G_RET_STS_ERROR;
1601 END IF;
1602 END IF;
1603 --dbms_output.put_line(' attr_value to status is '||x_return_status);
1604
1605
1606
1607
1608 --dbms_output.put_line('validating qualifier_context ');
1609
1610 IF p_QUALIFIERS_rec.qualifier_context IS NOT NULL AND
1611 ( p_QUALIFIERS_rec.qualifier_context <>
1612 p_old_QUALIFIERS_rec.qualifier_context OR
1613 p_old_QUALIFIERS_rec.qualifier_context IS NULL )
1614 THEN
1615 IF NOT QP_Validate.Qualifier_Context(p_QUALIFIERS_rec.qualifier_context) THEN
1616 x_return_status := FND_API.G_RET_STS_ERROR;
1617 END IF;
1618 END IF;
1619 --dbms_output.put_line(' context status is '||x_return_status);
1620
1621
1622
1623 --dbms_output.put_line('validating qualifier_datatype ');
1624
1625 IF p_QUALIFIERS_rec.qualifier_datatype IS NOT NULL AND
1626 ( p_QUALIFIERS_rec.qualifier_datatype <>
1627 p_old_QUALIFIERS_rec.qualifier_datatype OR
1628 p_old_QUALIFIERS_rec.qualifier_datatype IS NULL )
1629 THEN
1630 IF NOT QP_Validate.Qualifier_Datatype(p_QUALIFIERS_rec.qualifier_datatype) THEN
1631 x_return_status := FND_API.G_RET_STS_ERROR;
1632 END IF;
1633 END IF;
1634 --dbms_output.put_line(' data type status is '||x_return_status);
1635
1636 /*IF p_QUALIFIERS_rec.qualifier_date_format IS NOT NULL AND
1637 ( p_QUALIFIERS_rec.qualifier_date_format <>
1638 p_old_QUALIFIERS_rec.qualifier_date_format OR
1639 p_old_QUALIFIERS_rec.qualifier_date_format IS NULL )
1640 THEN
1641 IF NOT QP_Validate.Qualifier_Date_Format(p_QUALIFIERS_rec.qualifier_date_format) THEN
1642 x_return_status := FND_API.G_RET_STS_ERROR;
1643 END IF;
1644 END IF;*/
1645
1646
1647
1648
1649 --dbms_output.put_line('validating qualifier_gruouping ');
1650
1651
1652
1653 IF p_QUALIFIERS_rec.qualifier_grouping_no <> -1 AND
1654 ( p_QUALIFIERS_rec.qualifier_grouping_no <>
1655 p_old_QUALIFIERS_rec.qualifier_grouping_no OR
1656 p_old_QUALIFIERS_rec.qualifier_grouping_no = -1 )
1657 THEN
1658 IF NOT QP_Validate.Qualifier_Grouping_No(p_QUALIFIERS_rec.qualifier_grouping_no) THEN
1659 x_return_status := FND_API.G_RET_STS_ERROR;
1660 END IF;
1661 END IF;
1662 --dbms_output.put_line(' gropuing status is '||x_return_status);
1663
1664
1665
1666 --dbms_output.put_line('validating qualifier_id ');
1667
1668 IF p_QUALIFIERS_rec.qualifier_id IS NOT NULL AND
1669 ( p_QUALIFIERS_rec.qualifier_id <>
1670 p_old_QUALIFIERS_rec.qualifier_id OR
1671 p_old_QUALIFIERS_rec.qualifier_id IS NULL )
1672 THEN
1673 IF NOT QP_Validate.Qualifier(p_QUALIFIERS_rec.qualifier_id) THEN
1674 x_return_status := FND_API.G_RET_STS_ERROR;
1675 END IF;
1676 END IF;
1677 --dbms_output.put_line(' qualiifer id status is '||x_return_status);
1678
1679
1680
1681
1682 /*IF p_QUALIFIERS_rec.qualifier_number_format IS NOT NULL AND
1683 ( p_QUALIFIERS_rec.qualifier_number_format <>
1684 p_old_QUALIFIERS_rec.qualifier_number_format OR
1685 p_old_QUALIFIERS_rec.qualifier_number_format IS NULL )
1686 THEN
1687 IF NOT QP_Validate.Qualifier_Number_Format(p_QUALIFIERS_rec.qualifier_number_format) THEN
1688 x_return_status := FND_API.G_RET_STS_ERROR;
1689 END IF;
1690 END IF;*/
1691
1692
1693
1694 --dbms_output.put_line('validating qualifier_precedence ');
1695
1696 IF p_QUALIFIERS_rec.qualifier_precedence IS NOT NULL AND
1697 ( p_QUALIFIERS_rec.qualifier_precedence <>
1698 p_old_QUALIFIERS_rec.qualifier_precedence OR
1699 p_old_QUALIFIERS_rec.qualifier_precedence IS NULL )
1700 THEN
1701 IF NOT QP_Validate.Qualifier_Precedence(p_QUALIFIERS_rec.qualifier_precedence) THEN
1702 x_return_status := FND_API.G_RET_STS_ERROR;
1703 END IF;
1704 END IF;
1705 --dbms_output.put_line(' precedence status is '||x_return_status);
1706
1707
1708
1709
1710 --dbms_output.put_line('validating qualifier rule id ');
1711
1712
1713
1714 IF p_QUALIFIERS_rec.qualifier_rule_id IS NOT NULL AND
1715 ( p_QUALIFIERS_rec.qualifier_rule_id <>
1716 p_old_QUALIFIERS_rec.qualifier_rule_id OR
1717 p_old_QUALIFIERS_rec.qualifier_rule_id IS NULL )
1718 THEN
1719 IF NOT QP_Validate.Qualifier_Rule(p_QUALIFIERS_rec.qualifier_rule_id) THEN
1720 x_return_status := FND_API.G_RET_STS_ERROR;
1721 END IF;
1722 END IF;
1723 --dbms_output.put_line(' qualifier rule id status is '||x_return_status);
1724
1725
1726 --dbms_output.put_line('validating request id ');
1727
1728 IF p_QUALIFIERS_rec.request_id IS NOT NULL AND
1729 ( p_QUALIFIERS_rec.request_id <>
1730 p_old_QUALIFIERS_rec.request_id OR
1731 p_old_QUALIFIERS_rec.request_id IS NULL )
1732 THEN
1733 IF NOT QP_Validate.Request(p_QUALIFIERS_rec.request_id) THEN
1734 x_return_status := FND_API.G_RET_STS_ERROR;
1735 END IF;
1736 END IF;
1737 --dbms_output.put_line('status is '||x_return_status);
1738
1739
1740 --dbms_output.put_line('validating date active ');
1741
1742 IF p_QUALIFIERS_rec.start_date_active IS NOT NULL AND
1743 ( p_QUALIFIERS_rec.start_date_active <>
1744 p_old_QUALIFIERS_rec.start_date_active OR
1745 p_old_QUALIFIERS_rec.start_date_active IS NULL )
1746 THEN
1747 IF NOT QP_Validate.Start_Date_Active(p_QUALIFIERS_rec.start_date_active) THEN
1748 x_return_status := FND_API.G_RET_STS_ERROR;
1749 END IF;
1750 END IF;
1751 --dbms_output.put_line('status is '||x_return_status);
1752 -- Added for TCA
1753 IF p_QUALIFIERS_rec.qualify_hier_descendent_flag IS NOT NULL AND
1754 ( p_QUALIFIERS_rec.qualify_hier_descendent_flag <>
1755 p_old_QUALIFIERS_rec.qualify_hier_descendent_flag OR
1756 p_old_QUALIFIERS_rec.qualify_hier_descendent_flag IS NULL )
1757 THEN
1758 IF NOT QP_Validate.Qualify_Hier_Descendent_Flag(p_QUALIFIERS_rec.qualify_hier_descendent_flag) THEN
1759 x_return_status := FND_API.G_RET_STS_ERROR;
1760 END IF;
1761 END IF;
1762
1763
1764 IF (p_QUALIFIERS_rec.attribute1 IS NOT NULL AND
1765 ( p_QUALIFIERS_rec.attribute1 <>
1766 p_old_QUALIFIERS_rec.attribute1 OR
1767 p_old_QUALIFIERS_rec.attribute1 IS NULL ))
1768 OR (p_QUALIFIERS_rec.attribute10 IS NOT NULL AND
1769 ( p_QUALIFIERS_rec.attribute10 <>
1770 p_old_QUALIFIERS_rec.attribute10 OR
1771 p_old_QUALIFIERS_rec.attribute10 IS NULL ))
1772 OR (p_QUALIFIERS_rec.attribute11 IS NOT NULL AND
1773 ( p_QUALIFIERS_rec.attribute11 <>
1774 p_old_QUALIFIERS_rec.attribute11 OR
1775 p_old_QUALIFIERS_rec.attribute11 IS NULL ))
1776 OR (p_QUALIFIERS_rec.attribute12 IS NOT NULL AND
1777 ( p_QUALIFIERS_rec.attribute12 <>
1778 p_old_QUALIFIERS_rec.attribute12 OR
1779 p_old_QUALIFIERS_rec.attribute12 IS NULL ))
1780 OR (p_QUALIFIERS_rec.attribute13 IS NOT NULL AND
1781 ( p_QUALIFIERS_rec.attribute13 <>
1782 p_old_QUALIFIERS_rec.attribute13 OR
1783 p_old_QUALIFIERS_rec.attribute13 IS NULL ))
1784 OR (p_QUALIFIERS_rec.attribute14 IS NOT NULL AND
1785 ( p_QUALIFIERS_rec.attribute14 <>
1786 p_old_QUALIFIERS_rec.attribute14 OR
1787 p_old_QUALIFIERS_rec.attribute14 IS NULL ))
1788 OR (p_QUALIFIERS_rec.attribute15 IS NOT NULL AND
1789 ( p_QUALIFIERS_rec.attribute15 <>
1790 p_old_QUALIFIERS_rec.attribute15 OR
1791 p_old_QUALIFIERS_rec.attribute15 IS NULL ))
1792 OR (p_QUALIFIERS_rec.attribute2 IS NOT NULL AND
1793 ( p_QUALIFIERS_rec.attribute2 <>
1794 p_old_QUALIFIERS_rec.attribute2 OR
1795 p_old_QUALIFIERS_rec.attribute2 IS NULL ))
1796 OR (p_QUALIFIERS_rec.attribute3 IS NOT NULL AND
1797 ( p_QUALIFIERS_rec.attribute3 <>
1798 p_old_QUALIFIERS_rec.attribute3 OR
1799 p_old_QUALIFIERS_rec.attribute3 IS NULL ))
1800 OR (p_QUALIFIERS_rec.attribute4 IS NOT NULL AND
1801 ( p_QUALIFIERS_rec.attribute4 <>
1802 p_old_QUALIFIERS_rec.attribute4 OR
1803 p_old_QUALIFIERS_rec.attribute4 IS NULL ))
1804 OR (p_QUALIFIERS_rec.attribute5 IS NOT NULL AND
1805 ( p_QUALIFIERS_rec.attribute5 <>
1806 p_old_QUALIFIERS_rec.attribute5 OR
1807 p_old_QUALIFIERS_rec.attribute5 IS NULL ))
1808 OR (p_QUALIFIERS_rec.attribute6 IS NOT NULL AND
1809 ( p_QUALIFIERS_rec.attribute6 <>
1810 p_old_QUALIFIERS_rec.attribute6 OR
1811 p_old_QUALIFIERS_rec.attribute6 IS NULL ))
1812 OR (p_QUALIFIERS_rec.attribute7 IS NOT NULL AND
1813 ( p_QUALIFIERS_rec.attribute7 <>
1814 p_old_QUALIFIERS_rec.attribute7 OR
1815 p_old_QUALIFIERS_rec.attribute7 IS NULL ))
1816 OR (p_QUALIFIERS_rec.attribute8 IS NOT NULL AND
1817 ( p_QUALIFIERS_rec.attribute8 <>
1818 p_old_QUALIFIERS_rec.attribute8 OR
1819 p_old_QUALIFIERS_rec.attribute8 IS NULL ))
1820 OR (p_QUALIFIERS_rec.attribute9 IS NOT NULL AND
1821 ( p_QUALIFIERS_rec.attribute9 <>
1822 p_old_QUALIFIERS_rec.attribute9 OR
1823 p_old_QUALIFIERS_rec.attribute9 IS NULL ))
1824 OR (p_QUALIFIERS_rec.context IS NOT NULL AND
1825 ( p_QUALIFIERS_rec.context <>
1826 p_old_QUALIFIERS_rec.context OR
1827 p_old_QUALIFIERS_rec.context IS NULL ))
1828 THEN
1829
1830 -- These calls are temporarily commented out
1831
1832 /*
1833 FND_FLEX_DESC_VAL.Set_Column_Value
1834 ( column_name => 'ATTRIBUTE1'
1835 , column_value => p_QUALIFIERS_rec.attribute1
1836 );
1837 FND_FLEX_DESC_VAL.Set_Column_Value
1838 ( column_name => 'ATTRIBUTE10'
1839 , column_value => p_QUALIFIERS_rec.attribute10
1840 );
1841 FND_FLEX_DESC_VAL.Set_Column_Value
1842 ( column_name => 'ATTRIBUTE11'
1843 , column_value => p_QUALIFIERS_rec.attribute11
1844 );
1845 FND_FLEX_DESC_VAL.Set_Column_Value
1846 ( column_name => 'ATTRIBUTE12'
1847 , column_value => p_QUALIFIERS_rec.attribute12
1848 );
1849 FND_FLEX_DESC_VAL.Set_Column_Value
1850 ( column_name => 'ATTRIBUTE13'
1851 , column_value => p_QUALIFIERS_rec.attribute13
1852 );
1853 FND_FLEX_DESC_VAL.Set_Column_Value
1854 ( column_name => 'ATTRIBUTE14'
1855 , column_value => p_QUALIFIERS_rec.attribute14
1856 );
1857 FND_FLEX_DESC_VAL.Set_Column_Value
1858 ( column_name => 'ATTRIBUTE15'
1859 , column_value => p_QUALIFIERS_rec.attribute15
1860 );
1861 FND_FLEX_DESC_VAL.Set_Column_Value
1862 ( column_name => 'ATTRIBUTE2'
1863 , column_value => p_QUALIFIERS_rec.attribute2
1864 );
1865 FND_FLEX_DESC_VAL.Set_Column_Value
1866 ( column_name => 'ATTRIBUTE3'
1867 , column_value => p_QUALIFIERS_rec.attribute3
1868 );
1869 FND_FLEX_DESC_VAL.Set_Column_Value
1870 ( column_name => 'ATTRIBUTE4'
1871 , column_value => p_QUALIFIERS_rec.attribute4
1872 );
1873 FND_FLEX_DESC_VAL.Set_Column_Value
1874 ( column_name => 'ATTRIBUTE5'
1875 , column_value => p_QUALIFIERS_rec.attribute5
1876 );
1877 FND_FLEX_DESC_VAL.Set_Column_Value
1878 ( column_name => 'ATTRIBUTE6'
1879 , column_value => p_QUALIFIERS_rec.attribute6
1880 );
1881 FND_FLEX_DESC_VAL.Set_Column_Value
1882 ( column_name => 'ATTRIBUTE7'
1883 , column_value => p_QUALIFIERS_rec.attribute7
1884 );
1885 FND_FLEX_DESC_VAL.Set_Column_Value
1886 ( column_name => 'ATTRIBUTE8'
1887 , column_value => p_QUALIFIERS_rec.attribute8
1888 );
1889 FND_FLEX_DESC_VAL.Set_Column_Value
1890 ( column_name => 'ATTRIBUTE9'
1891 , column_value => p_QUALIFIERS_rec.attribute9
1892 );
1893 FND_FLEX_DESC_VAL.Set_Column_Value
1894 ( column_name => 'CONTEXT'
1895 , column_value => p_QUALIFIERS_rec.context
1896 );
1897 */
1898
1899 -- Validate descriptive flexfield.
1900
1901 --dbms_output.put_line('validating flex ');
1902 IF NOT QP_Validate.Desc_Flex( 'QP_QUALIFIERS' ) THEN
1903 x_return_status := FND_API.G_RET_STS_ERROR;
1904 END IF;
1905 --dbms_output.put_line('status is '||x_return_status);
1906
1907 END IF;
1908
1909 -- Done validating attributes
1910
1911 EXCEPTION
1912
1913 WHEN FND_API.G_EXC_ERROR THEN
1914
1915 x_return_status := FND_API.G_RET_STS_ERROR;
1916
1917 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1918
1919 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1920
1921 WHEN OTHERS THEN
1922
1923 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1924
1925 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1926 THEN
1927 OE_MSG_PUB.Add_Exc_Msg
1928 ( G_PKG_NAME
1929 , 'Attributes'
1930 );
1931 END IF;
1932
1933 END Attributes;
1934
1935 -- Procedure Entity_Delete
1936
1937 PROCEDURE Entity_Delete
1938 ( x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2
1939 , p_QUALIFIERS_rec IN QP_Qualifier_Rules_PUB.Qualifiers_Rec_Type
1940 )
1941 IS
1942 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
1943 BEGIN
1944
1945 -- Validate entity delete.
1946
1947 NULL;
1948 -- Check whether Source System Code matches
1949 -- mkarya for bug 1728764, Prevent update of Trade Management Data in QP
1950 QP_UTIL.Check_Source_System_Code
1951 (p_list_header_id => p_QUALIFIERS_rec.list_header_id,
1952 p_list_line_id => p_QUALIFIERS_rec.list_line_id,
1953 x_return_status => l_return_status
1954 );
1955
1956 -- Done.
1957
1958 x_return_status := l_return_status;
1959
1960 EXCEPTION
1961
1962 WHEN FND_API.G_EXC_ERROR THEN
1963
1964 x_return_status := FND_API.G_RET_STS_ERROR;
1965
1966 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1967
1968 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1969
1970 WHEN OTHERS THEN
1971
1972 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1973
1974 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1975 THEN
1976 OE_MSG_PUB.Add_Exc_Msg
1977 ( G_PKG_NAME
1978 , 'Entity_Delete'
1979 );
1980 END IF;
1981
1982 END Entity_Delete;
1983
1984 END QP_Validate_Qualifiers;
[Home] [Help]