1 PACKAGE BODY QP_Validate_Pricing_Attr AS
2 /* $Header: QPXLPRAB.pls 120.8 2006/09/07 10:08:10 rbagri noship $ */
3
4 -- Global constant holding the package name
5
6 G_PKG_NAME CONSTANT VARCHAR2(30) := 'QP_Validate_Pricing_Attr';
7
8 -- Procedure Entity
9
10 PROCEDURE Entity
11 ( x_return_status OUT NOCOPY VARCHAR2
12 , p_PRICING_ATTR_rec IN QP_Modifiers_PUB.Pricing_Attr_Rec_Type
13 , p_old_PRICING_ATTR_rec IN QP_Modifiers_PUB.Pricing_Attr_Rec_Type :=
14 QP_Modifiers_PUB.G_MISS_PRICING_ATTR_REC
15 )
16 IS
17 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
18 l_context_flag VARCHAR2(1);
19 l_attribute_flag VARCHAR2(1);
20 l_value_flag VARCHAR2(1);
21 l_datatype VARCHAR2(1);
22 l_precedence NUMBER;
23 l_error_code NUMBER := 0;
24 l_primary_list_line_type_code VARCHAR2(30);
25 l_organization_id VARCHAR2(30);
26 l_dummy_2 VARCHAR2(3);
27 l_dummy_3 NUMBER;
28 l_count NUMBER;
29 l_qp_status VARCHAR2(1);
30 l_no_pricing_attr NUMBER;
31
32 l_start_date_active DATE;
33 l_end_date_active DATE;
34 l_list_header_id NUMBER;
35
36 l_uom_list_header_id NUMBER;
37
38 CURSOR list_line_type_code_cur(a_list_line_id NUMBER)
39 IS
40 SELECT *
41 FROM qp_list_lines
42 WHERE list_line_id = a_list_line_id;
43
44 CURSOR gsa_cur(a_list_header_id NUMBER)
45 IS
46 SELECT gsa_indicator
47 FROM qp_list_headers_b
48 WHERE list_header_id = a_list_header_id;
49
50 CURSOR to_rltd_modifier_id_cur(a_list_line_id NUMBER,
51 a_rltd_modifier_grp_type VARCHAR2)
52 IS
53 select to_rltd_modifier_id
54 from qp_rltd_modifiers
55 where to_rltd_modifier_id = a_list_line_id
56 and rltd_modifier_grp_type = a_rltd_modifier_grp_type;
57
58 -- SELECT list_line_type_code
59 l_context_type VARCHAR2(30);
60 l_sourcing_enabled VARCHAR2(1);
61 l_sourcing_status VARCHAR2(1);
62 l_sourcing_method VARCHAR2(30);
63
64 l_pte_code VARCHAR2(30);
65 l_ss_code VARCHAR2(30);
66 l_fna_name VARCHAR2(4000);
67 l_fna_desc VARCHAR2(489);
68 l_fna_valid BOOLEAN;
69
70 BEGIN
71
72 oe_debug_pub.add('BEGIN Entity in QPXLPRAB');
73
74 -- Check whether Source System Code matches
75 -- mkarya for bug 1728764, Prevent update of Trade Management Data in QP
76 --dbms_output.put_line('Manoj - QPXLPRAB - list_header_id = ' || p_PRICING_ATTR_rec.list_header_id);
77 --dbms_output.put_line('Manoj - QPXLPRAB - list_line_id = ' || p_PRICING_ATTR_rec.list_line_id);
78 QP_UTIL.Check_Source_System_Code
79 (p_list_header_id => p_PRICING_ATTR_rec.list_header_id,
80 p_list_line_id => p_PRICING_ATTR_rec.list_line_id,
81 x_return_status => l_return_status
82 );
83
84 -- Check required attributes.
85
86 IF p_PRICING_ATTR_rec.pricing_attribute_id IS NULL
87 THEN
88
89 l_return_status := FND_API.G_RET_STS_ERROR;
90
91 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR)
92 THEN
93
94 FND_MESSAGE.SET_NAME('QP','QP_ATTRIBUTE_REQUIRED');
95 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','Pricing Attribute Id');
96 OE_MSG_PUB.Add;
97
98 END IF;
99
100 END IF;
101
102 /*
103 IF p_PRICING_ATTR_rec.list_header_id IS NULL
104 THEN
105
106 l_return_status := FND_API.G_RET_STS_ERROR;
107
108 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR)
109 THEN
110
111 FND_MESSAGE.SET_NAME('QP','QP_ATTRIBUTE_REQUIRED');
112 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','List Header Id');
113 OE_MSG_PUB.Add;
114
115 END IF;
116
117 END IF;
118
119 IF p_PRICING_ATTR_rec.pricing_phase_id IS NULL
120 THEN
121
122 l_return_status := FND_API.G_RET_STS_ERROR;
123
124 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR)
125 THEN
126
127 FND_MESSAGE.SET_NAME('QP','QP_ATTRIBUTE_REQUIRED');
128 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','Pricing Phase Id');
129 OE_MSG_PUB.Add;
130
131 END IF;
132
133 END IF;
134 */
135
136 --
137 -- Check rest of required attributes .
138 --
139
140
141 IF p_PRICING_ATTR_rec.operation = QP_GLOBALS.G_OPR_UPDATE
142 THEN
143
144 IF p_old_PRICING_ATTR_rec.product_attribute IS NOT NULL
145 AND p_old_PRICING_ATTR_rec.product_attribute <> FND_API.G_MISS_CHAR
146 AND p_old_PRICING_ATTR_rec.product_attribute <> p_PRICING_ATTR_rec.product_attribute THEN
147 DECLARE
148 count_attr NUMBER;
149 BEGIN
150 Select count(*) into count_attr
151 from qp_pricing_attributes
152 where list_line_id = p_PRICING_ATTR_rec.list_line_id;
153 if count_attr > 1 THEN
154 l_return_status := FND_API.G_RET_STS_ERROR;
155
156 FND_MESSAGE.SET_NAME('QP','QP_CANNOT_UPDATE_PROD_ATTR');
157 OE_MSG_PUB.Add;
158 ELSE
159 NULL;
160 END IF;
161 END;
162
163 END IF;
164
165 IF p_old_PRICING_ATTR_rec.product_attr_value IS NOT NULL
166 AND p_old_PRICING_ATTR_rec.product_attr_value <> FND_API.G_MISS_CHAR
167 AND p_old_PRICING_ATTR_rec.product_attr_value <> p_PRICING_ATTR_rec.product_attr_value
168 THEN
169
170 DECLARE
171 count_attr NUMBER;
172 BEGIN
173 Select count(*) into count_attr
174 from qp_pricing_attributes
175 where list_line_id = p_PRICING_ATTR_rec.list_line_id;
176 if count_attr > 1 AND p_PRICING_ATTR_rec.excluder_flag = 'N' THEN
177 l_return_status := FND_API.G_RET_STS_ERROR;
178
179 FND_MESSAGE.SET_NAME('QP','QP_CANNOT_UPDATE_PROD_VALUE');
180 OE_MSG_PUB.Add;
181 ELSE
182 NULL;
183 END IF;
184 END;
185 END IF;
186
187 END IF;
188
189
190
191
192 FOR list_line in list_line_type_code_cur(p_PRICING_ATTR_rec.list_line_id)
193 LOOP
194
195 /* Bug2069685 Start */
196
197 IF (list_line.price_break_type_code = 'RECURRING' AND
198 p_PRICING_ATTR_rec.pricing_attribute_context = 'VOLUME' AND --bug#4261068
199 p_PRICING_ATTR_rec.pricing_attr_value_from <= 0) then
200
201 l_return_status := FND_API.G_RET_STS_ERROR;
202
203 FND_MESSAGE.SET_NAME('QP','QP_RECUR_VAL_FROM_CHECK');
204
205 OE_MSG_PUB.Add;
206
207 END IF;
208
209 /* Bug2069685 End */
210
211 oe_debug_pub.add('excluder flag = '||p_PRICING_ATTR_rec.excluder_flag);
212
213 IF list_line.list_line_type_code <> 'PMR' THEN
214 IF p_PRICING_ATTR_rec.excluder_flag IS NULL
215 THEN
216
217 oe_debug_pub.add('excluder flag null');
218 l_return_status := FND_API.G_RET_STS_ERROR;
219
220 FND_MESSAGE.SET_NAME('QP','QP_ATTRIBUTE_REQUIRED');
221 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',QP_PRC_UTIL.Get_Attribute_Name('EXCLUDER_FLAG')); -- Fix For Bug-1974413
222 OE_MSG_PUB.Add;
223
224 ELSIF ( p_PRICING_ATTR_rec.excluder_flag <> 'Y' AND
225 p_PRICING_ATTR_rec.excluder_flag <> 'N' )
226 THEN
227
228 oe_debug_pub.add('excluder flag invalid');
229 l_return_status := FND_API.G_RET_STS_ERROR;
230
231 FND_MESSAGE.SET_NAME('QP','QP_EXCLD_FLAG_Y_OR_N');
232 OE_MSG_PUB.Add;
233
234 END IF;
235 END IF; --list_line_type_code not 'PMR'
236
237 oe_debug_pub.add(p_PRICING_ATTR_rec.Comparison_operator_code);
238
239 IF list_line.list_line_type_code <> 'PBH'
240 THEN
241
242 IF p_PRICING_ATTR_rec.pricing_attribute_context IS NOT NULL
243 AND p_PRICING_ATTR_rec.pricing_attribute IS NOT NULL
244 THEN
245
246 IF p_PRICING_ATTR_rec.comparison_operator_code IS NULL
247 THEN
248
249 l_return_status := FND_API.G_RET_STS_ERROR;
250
251 FND_MESSAGE.SET_NAME('QP','QP_ATTRIBUTE_REQUIRED');
252 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',QP_PRC_UTIL.Get_Attribute_Name('COMPARISON_OPERATOR_CODE')); -- Fix For Bug-1974413
253 OE_MSG_PUB.Add;
254
255 END IF;
256
257 IF ( p_PRICING_ATTR_rec.comparison_operator_code <> '=' AND
258 p_PRICING_ATTR_rec.comparison_operator_code <> 'BETWEEN' )
259 THEN
260
261 l_return_status := FND_API.G_RET_STS_ERROR;
262
263 FND_MESSAGE.SET_NAME('QP','QP_INVALID_COMP_OPERATOR');
264 OE_MSG_PUB.Add;
265
266 END IF;
267
268 -- Do not check for value to null. This change is to avoid an error being thrown
269 -- when a null value to is entered for the last price break (infinite value)
270 IF p_PRICING_ATTR_rec.pricing_attr_value_from IS NULL
271 --AND p_PRICING_ATTR_rec.pricing_attr_value_to IS NULL
272 THEN
273
274 l_return_status := FND_API.G_RET_STS_ERROR;
275
276 FND_MESSAGE.SET_NAME('QP','QP_ATTRIBUTE_REQUIRED');
277 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',QP_PRC_UTIL.Get_Attribute_Name('PRICING_ATTR_VALUE_FROM')||'/'||
278 QP_PRC_UTIL.Get_Attribute_Name('PRICING_ATTR_VALUE_TO')); --Fix For Bug-1974413
279 OE_MSG_PUB.Add;
280
281 -- Do not check for value to null. This change is to avoid an error being thrown
282 -- when a null value to is entered for the last price break (infinite value)
283 ELSIF p_PRICING_ATTR_rec.comparison_operator_code = 'BETWEEN'
284 AND p_PRICING_ATTR_rec.pricing_attr_value_from IS NULL
285 --AND (p_PRICING_ATTR_rec.pricing_attr_value_from IS NULL
286 --OR p_PRICING_ATTR_rec.pricing_attr_value_to IS NULL)
287 AND list_line.price_break_type_code <> 'RECURRING' THEN
288
289 l_return_status := FND_API.G_RET_STS_ERROR;
290
291 FND_MESSAGE.SET_NAME('QP','QP_ATTRIBUTE_REQUIRED');
292 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',QP_PRC_UTIL.Get_Attribute_Name('PRICING_ATTR_VALUE_FROM')||'/'||
293 QP_PRC_UTIL.Get_Attribute_Name('PRICING_ATTR_VALUE_TO')); --Fix For Bug-1974413
294 OE_MSG_PUB.Add;
295
296 ELSIF p_PRICING_ATTR_rec.comparison_operator_code = 'BETWEEN'
297 AND p_PRICING_ATTR_rec.pricing_attr_value_from IS NULL
298 AND list_line.price_break_type_code = 'RECURRING' THEN
299
300 l_return_status := FND_API.G_RET_STS_ERROR;
301
302 FND_MESSAGE.SET_NAME('QP','QP_ATTRIBUTE_REQUIRED');
303 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',QP_PRC_UTIL.Get_Attribute_Name('PRICING_ATTR_VALUE_FROM')); -- Fix For Bug-1974413
304 OE_MSG_PUB.Add;
305 END IF;
306
307
308
309 END IF;
310
311 /*commented out this validation and included the else condition that follows -spgopal
312 ELSIF p_PRICING_ATTR_rec.pricing_attribute IS NULL
313 THEN
314
315 l_return_status := FND_API.G_RET_STS_ERROR;
316
317 FND_MESSAGE.SET_NAME('QP','QP_ATTRIBUTE_REQUIRED');
318 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','Volume Type');
319 OE_MSG_PUB.Add;
320 */
321
322 ELSE
323
324
325 IF p_PRICING_ATTR_rec.pricing_attribute_context IS NOT NULL THEN
326
327 IF p_PRICING_ATTR_rec.pricing_attribute IS NULL THEN
328
329 l_return_status := FND_API.G_RET_STS_ERROR;
330
331 FND_MESSAGE.SET_NAME('QP','QP_ATTRIBUTE_REQUIRED');
332 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','Volume Type');
333 OE_MSG_PUB.Add;
334
335 ELSIF p_PRICING_ATTR_rec.pricing_attribute IS NOT NULL
336 AND p_PRICING_ATTR_rec.pricing_attribute_context
337 = 'VOLUME'
338 AND p_PRICING_ATTR_rec.comparison_operator_code
339 = 'BETWEEN'
340 AND (p_PRICING_ATTR_rec.pricing_attr_value_from
341 IS NOT NULL
342 OR p_PRICING_ATTR_rec.pricing_attr_value_to
343 IS NOT NULL) THEN
344 --when value from/to entered for PBH record volume context
345
346 l_return_status := FND_API.G_RET_STS_ERROR;
347
348 FND_MESSAGE.SET_NAME('QP','QP_PBH_NO_VALUE_FROM_TO');
349 OE_MSG_PUB.Add;
350
351 ELSIF p_PRICING_ATTR_rec.pricing_attribute_context
352 <> 'VOLUME'
353 AND p_PRICING_ATTR_rec.pricing_attribute IS NOT NULL
354 AND p_PRICING_ATTR_rec.comparison_operator_code = 'BETWEEN'
355 AND (p_PRICING_ATTR_rec.pricing_attr_value_from IS NULL
356 OR p_PRICING_ATTR_rec.pricing_attr_value_to IS NULL) THEN
357
358 --when value from/to NOT entered for PBH rec context<>VOLUME
359
360 l_return_status := FND_API.G_RET_STS_ERROR;
361
362 FND_MESSAGE.SET_NAME('QP','QP_ATTRIBUTE_REQUIRED');
363 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',QP_PRC_UTIL.Get_Attribute_Name('PRICING_ATTR_VALUE_FROM')||'/'||
364 QP_PRC_UTIL.Get_Attribute_Name('PRICING_ATTR_VALUE_TO')); -- Fix For Bug-1974413
365 OE_MSG_PUB.Add;
366
367 ELSIF p_PRICING_ATTR_rec.pricing_attribute_context
368 <> 'VOLUME'
369 AND p_PRICING_ATTR_rec.pricing_attribute IS NOT NULL
370 AND p_PRICING_ATTR_rec.comparison_operator_code = '='
371 AND (p_PRICING_ATTR_rec.pricing_attr_value_from IS NULL
372 AND p_PRICING_ATTR_rec.pricing_attr_value_to IS NULL) THEN
373
374 --when value from/to NOT entered for PBH rec context<>VOLUME
375
376 l_return_status := FND_API.G_RET_STS_ERROR;
377
378 FND_MESSAGE.SET_NAME('QP','QP_ATTRIBUTE_REQUIRED');
379 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',QP_PRC_UTIL.Get_Attribute_Name('PRICING_ATTR_VALUE_FROM')||'/'|| QP_PRC_UTIL.Get_Attribute_Name('PRICING_ATTR_VALUE_TO')); --Fix For Bug-1974413
380 OE_MSG_PUB.Add;
381
382
383 END IF;
384
385 END IF;
386
387
388 END IF;
389
390 /* The Pricing Context for a Price Break Header record must be VOLUME
391
392 IF list_line.list_line_type_code = 'PBH'
393 AND p_PRICING_ATTR_rec.pricing_attribute_context <> 'VOLUME'
394 THEN
395
396 l_return_status := FND_API.G_RET_STS_ERROR;
397
398 FND_MESSAGE.SET_NAME('QP','QP_PBH_PRICING_CONTEXT_VOLUME');
399 OE_MSG_PUB.Add;
400
401
402 END IF;
403
404 */
405
406 /* If Value From or Value To or Comparison Operator is entered, it is mandatory to enter pricing attribute */
407
408
409 IF list_line.list_line_type_code <> 'PBH'
410 THEN
411
412 IF p_PRICING_ATTR_rec.pricing_attribute_context IS NULL
413 AND p_PRICING_ATTR_rec.pricing_attribute IS NULL
414 THEN
415
416 IF (p_PRICING_ATTR_rec.pricing_attr_value_from IS NOT NULL
417 OR p_PRICING_ATTR_rec.pricing_attr_value_to IS NOT NULL
418 OR (p_PRICING_ATTR_rec.comparison_operator_code IS NOT NULL
419 AND list_line.list_line_type_code <> 'RLTD'))
420 THEN
421
422 l_return_status := FND_API.G_RET_STS_ERROR;
423
424 FND_MESSAGE.SET_NAME('QP','QP_ATTRIBUTE_REQUIRED');
425 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','Volume Type');
426 OE_MSG_PUB.Add;
427
428 END IF;
429
430 END IF;
431
432 END IF;
433
434
435 /* Added validation by dhgupta to fix bug # 1859923 */
436
437 IF p_PRICING_ATTR_rec.pricing_attr_value_from IS NOT NULL
438 THEN
439
440 l_error_code := QP_UTIL.validate_num_date(p_PRICING_ATTR_rec.pricing_attribute_datatype,
441 p_PRICING_ATTR_rec.pricing_attr_value_from);
442
443 If (l_error_code <> 0) -- invalid value
444 Then
445 l_return_status := FND_API.G_RET_STS_ERROR;
446
447 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR)
448 THEN
449
450 FND_MESSAGE.SET_NAME('QP','QP_INVALID_ATTRIBUTE');
451 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','Value From');
452 OE_MSG_PUB.Add;
453 END IF;
454 RAISE FND_API.G_EXC_ERROR;
455 End If;
456
457 END IF;
458
459
460
461
462 IF p_PRICING_ATTR_rec.pricing_attr_value_to IS NOT NULL
463 THEN
464
465 l_error_code := QP_UTIL.validate_num_date(p_PRICING_ATTR_rec.pricing_attribute_datatype,
466 p_PRICING_ATTR_rec.pricing_attr_value_to);
467
468 If (l_error_code <> 0) -- invalid value
469 Then
470 l_return_status := FND_API.G_RET_STS_ERROR;
471
472 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR)
473 THEN
474
475 FND_MESSAGE.SET_NAME('QP','QP_INVALID_ATTRIBUTE');
476 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','Value To');
477 OE_MSG_PUB.Add;
478 END IF;
479 RAISE FND_API.G_EXC_ERROR;
480 End If;
481
482 END IF;
483
484 /* End validation added by dhgupta to fix bug 1859923 */
485
486
487
488 IF p_PRICING_ATTR_rec.pricing_attr_value_from IS NOT NULL
489 AND p_PRICING_ATTR_rec.pricing_attr_value_to IS NOT NULL
490 AND p_PRICING_ATTR_rec.pricing_attribute_datatype = 'N'
491 THEN
492
493 IF QP_NUMBER.CANONICAL_TO_NUMBER(p_PRICING_ATTR_rec.pricing_attr_value_from) >
494 QP_NUMBER.CANONICAL_TO_NUMBER(p_PRICING_ATTR_rec.pricing_attr_value_to)
495 THEN
496
497 l_return_status := FND_API.G_RET_STS_ERROR;
498
499 FND_MESSAGE.SET_NAME('QP','QP_FROM_MUST_LESS_THAN_TO');
500 OE_MSG_PUB.Add;
501
502 END IF;
503
504 END IF;
505
506 IF list_line.list_line_type_code <> 'PMR' THEN
507
508 FOR gsa_ind_cur in gsa_cur(list_line.list_header_id)
509 LOOP
510
511 /* The only Product Attribute allowed for GSA Discounts is Item Number */
512
513 IF gsa_ind_cur.gsa_indicator = 'Y'
514 AND p_PRICING_ATTR_rec.product_attribute_context <> 'ITEM'
515 THEN
516
517 l_return_status := FND_API.G_RET_STS_ERROR;
518
519 FND_MESSAGE.SET_NAME('QP','QP_GSA_PROD_ATTR_ITEM');
520 OE_MSG_PUB.Add;
521
522 END IF;
523
524 /* Exclude flag cannot be 'Y' for GSA Discounts */
525
526 IF gsa_ind_cur.gsa_indicator = 'Y'
527 AND p_PRICING_ATTR_rec.excluder_flag = 'Y'
528 THEN
529
530 l_return_status := FND_API.G_RET_STS_ERROR;
531
532 FND_MESSAGE.SET_NAME('QP','QP_NO_EXCLUDE_FOR_GSA');
533 OE_MSG_PUB.Add;
534
535 END IF;
536
537 /* Pricing Attributes are not allowed for GSA Discounts */
538
539 IF gsa_ind_cur.gsa_indicator = 'Y'
540 AND (p_PRICING_ATTR_rec.pricing_attribute_context IS NOT NULL
541 OR p_PRICING_ATTR_rec.pricing_attribute IS NOT NULL
542 OR p_PRICING_ATTR_rec.pricing_attr_value_from IS NOT NULL
543 OR p_PRICING_ATTR_rec.pricing_attr_value_to IS NOT NULL)
544 THEN
545
546 l_return_status := FND_API.G_RET_STS_ERROR;
547
548 FND_MESSAGE.SET_NAME('QP','QP_NO_PRICING_ATTR_FOR_GSA');
549 OE_MSG_PUB.Add;
550
551 END IF;
552
553 --- start bug2091362, bug2119287
554
555 l_qp_status := QP_UTIL.GET_QP_STATUS;
556
557 IF (fnd_profile.value('QP_ALLOW_DUPLICATE_MODIFIERS') <> 'Y' AND
558 (l_qp_status = 'S' or gsa_ind_cur.gsa_indicator = 'Y')) THEN
559 select start_date_active, end_date_active , list_header_id
560 into l_start_date_active, l_end_date_active, l_list_header_id
561 from qp_list_lines
562 where list_line_id = p_PRICING_ATTR_rec.list_line_id;
563
564 OE_Debug_Pub.add ( ' Value Set 1' || l_start_date_active || l_end_date_active );
565
566 oe_debug_pub.add('about to delete a request to check duplicate modifier list lines without product attribute');
567 QP_delayed_requests_pvt.Delete_Request
568 ( p_entity_code => QP_GLOBALS.G_ENTITY_ALL
569 , p_entity_id => p_PRICING_ATTR_rec.list_line_id
570 , p_request_Type => QP_GLOBALS.G_DUPLICATE_MODIFIER_LINES
571 , x_return_status => l_return_status
572 );
573
574 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
575
576 oe_debug_pub.add('failed in deleting a delayed request for duplicate Modifier');
577
578 RAISE FND_API.G_EXC_ERROR;
579
580 END IF;
581 /* After deleting the first one for modifier lines without pricing attributes, log a new request
582 to check duplicate modifier list lines with product attribute */
583 oe_debug_pub.add('about to log a request to check duplicate modifier list lines with product attribute');
584 QP_DELAYED_REQUESTS_PVT.Log_Request
585 ( p_entity_code => QP_GLOBALS.G_ENTITY_ALL
586 , p_entity_id => p_PRICING_ATTR_rec.list_line_id
587 , p_requesting_entity_code => QP_GLOBALS.G_ENTITY_ALL
588 , p_requesting_entity_id => p_PRICING_ATTR_rec.list_line_id
589 , p_request_type => QP_GLOBALS.G_DUPLICATE_MODIFIER_LINES
590 , p_param1 => l_list_header_id
591 , p_param2 => fnd_date.date_to_canonical(l_start_date_active) --2752265
592 , p_param3 => fnd_date.date_to_canonical(l_end_date_active) --2752265
593 , p_param4 => p_PRICING_ATTR_rec.product_attribute_context
594 , p_param5 => p_PRICING_ATTR_rec.product_attribute
595 , p_param6 =>p_PRICING_ATTR_rec.product_attr_value
596 , x_return_status => l_return_status
597 );
598
599 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
600
601 oe_debug_pub.add('failed in logging a delayed request ');
602
603 RAISE FND_API.G_EXC_ERROR;
604
605 END IF;
606
607 oe_debug_pub.add('after logging delayed request for duplicate modifiers');
608
609 END IF;
610
611 --- end 2091362
612
613
614 END LOOP;
615
616 /* IF p_PRICING_ATTR_rec.product_attribute_context = 'ITEM'
617 AND p_PRICING_ATTR_rec.product_attribute = 'PRICING_ATTRIBUTE1' */
618
619 IF list_line.list_line_type_code <> 'RLTD'
620 AND list_line.price_break_type_code IS NOT NULL
621 AND p_PRICING_ATTR_rec.excluder_flag = 'N'
622 AND p_PRICING_ATTR_rec.product_uom_code IS NULL
623 THEN
624 IF ((list_line.LIST_LINE_TYPE_CODE in ('IUE','PRG','OID') AND
625 p_PRICING_ATTR_rec.PRICING_ATTRIBUTE NOT IN
626 ('PRICING_ATTRIBUTE12','PRICING_ATTRIBUTE13',
627 'PRICING_ATTRIBUTE14','PRICING_ATTRIBUTE15')) OR -- Bug#2828308
628 p_PRICING_ATTR_rec.PRICING_ATTRIBUTE = 'PRICING_ATTRIBUTE10') THEN
629 l_return_status := FND_API.G_RET_STS_ERROR;
630
631 FND_MESSAGE.SET_NAME('QP','QP_ATTRIBUTE_REQUIRED');
632 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',QP_PRC_UTIL.Get_Attribute_Name('PRODUCT_UOM_CODE')); -- Fix For Bug-1974413
633 OE_MSG_PUB.Add;
634 END IF;
635 END IF;
636
637
638 IF p_PRICING_ATTR_rec.product_attribute_context IS NULL
639 OR p_PRICING_ATTR_rec.product_attribute IS NULL
640 OR p_PRICING_ATTR_rec.product_attr_value IS NULL
641 -- OR p_PRICING_ATTR_rec.product_uom_code IS NULL
642 THEN
643
644 l_return_status := FND_API.G_RET_STS_ERROR;
645
646 FND_MESSAGE.SET_NAME('QP','QP_ATTRIBUTE_REQUIRED');
647 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',QP_PRC_UTIL.Get_Attribute_Name('PRODUCT_ATTRIBUTE')||'/'||
648 QP_PRC_UTIL.Get_Attribute_Name('PRODUCT_ATTR_VALUE')); -- Fix For Bug-1974413
649 OE_MSG_PUB.Add;
650
651 ELSE
652
653 /* Order level discounts cannot have Products */
654
655 IF list_line.modifier_level_code = 'ORDER'
656 THEN
657
658 l_return_status := FND_API.G_RET_STS_ERROR;
659
660 FND_MESSAGE.SET_NAME('QP','QP_ORDER_LEVEL_NO_PRODUCT');
661 OE_MSG_PUB.Add;
662
663 END IF;
664
665 QP_UTIL.validate_qp_flexfield(flexfield_name =>'QP_ATTR_DEFNS_PRICING'
666 ,context =>p_PRICING_ATTR_rec.product_attribute_context
667 ,attribute =>p_PRICING_ATTR_rec.product_attribute
668 ,value =>p_PRICING_ATTR_rec.product_attr_value
669 ,application_short_name => 'QP'
670 ,context_flag =>l_context_flag
671 ,attribute_flag =>l_attribute_flag
672 ,value_flag =>l_value_flag
673 ,datatype =>l_datatype
674 ,precedence =>l_precedence
675 ,error_code =>l_error_code
676 );
677
678 oe_debug_pub.add('error code = '|| to_char(l_error_code));
679 If (l_context_flag = 'N' AND l_error_code = 7) -- invalid context
680 Then
681 l_return_status := FND_API.G_RET_STS_ERROR;
682
683 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR)
684 THEN
685
686 FND_MESSAGE.SET_NAME('QP','QP_INVALID_PROD_CONTEXT' );
687 OE_MSG_PUB.Add;
688 END IF;
689
690 End If;
691
692 If (l_attribute_flag = 'N' AND l_error_code = 8) -- invalid attribute
693 Then
694 l_return_status := FND_API.G_RET_STS_ERROR;
695
696 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR)
697 THEN
698
699 FND_MESSAGE.SET_NAME('QP','QP_INVALID_PROD_ATTR' );
700 OE_MSG_PUB.Add;
701 END IF;
702
703 End If;
704
705 If (l_value_flag = 'N' AND l_error_code = 9) -- invalid value
706 Then
707 l_return_status := FND_API.G_RET_STS_ERROR;
708
709 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR)
710 THEN
711
712 FND_MESSAGE.SET_NAME('QP','QP_INVALID_PROD_VALUE' );
713 OE_MSG_PUB.Add;
714 END IF;
715
716 End If;
717
718
719 END IF;
720
721 -- p_PRICING_ATTR_rec.product_attribute_datatype := l_datatype;
722
723 -- Functional Area Validation for Hierarchical Categories (sfiresto)
724 IF p_PRICING_ATTR_rec.product_attribute_context = 'ITEM' AND
725 p_PRICING_ATTR_rec.product_attribute = 'PRICING_ATTRIBUTE2' THEN
726 BEGIN
727
728 -- We have to use list_line_id here because list_header_id has not been populated yet.
729 SELECT h.pte_code, h.source_system_code
730 INTO l_pte_code, l_ss_code
731 FROM qp_list_headers_b h, qp_list_lines l
732 WHERE l.list_line_id = p_PRICING_ATTR_rec.list_line_id
733 AND l.list_header_id = h.list_header_id;
734
735 QP_UTIL.Get_Item_Cat_Info(
736 p_PRICING_ATTR_rec.product_attr_value,
737 l_pte_code,
738 l_ss_code,
739 l_fna_name,
740 l_fna_desc,
741 l_fna_valid);
742
743 IF NOT l_fna_valid THEN
744
745 l_return_status := FND_API.G_RET_STS_ERROR;
746
747 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR)
748 THEN
749 FND_MESSAGE.set_name('QP', 'QP_INVALID_CAT_FUNC_PTE');
750 FND_MESSAGE.set_token('CATID', p_pricing_attr_rec.product_attr_value);
751 FND_MESSAGE.set_token('PTE', l_pte_code);
752 FND_MESSAGE.set_token('SS', l_ss_code);
753 OE_MSG_PUB.Add;
754 END IF;
755
756 END IF;
757
758 END;
759 END IF;
760
761 oe_debug_pub.add('end attribute ');
762
763 --fix for bug 5507953
764 l_return_status := QP_UTIL.Validate_Item(p_PRICING_ATTR_rec.product_attribute_context,
765 p_PRICING_ATTR_rec.product_attribute,
766 p_PRICING_ATTR_rec.product_attr_value);
767
768 IF p_PRICING_ATTR_rec.product_uom_code IS NOT NULL
769 THEN
770
771 IF p_PRICING_ATTR_rec.product_attribute_context = 'ITEM' -- Item Number
772 AND p_PRICING_ATTR_rec.product_attribute = 'PRICING_ATTRIBUTE1'
773 THEN
774 oe_debug_pub.add('IIIITTTTEEEEMMMM');
775 l_organization_id := QP_UTIL.Get_Item_Validation_Org;
776
777 BEGIN
778
779 select distinct uom_code
780 into l_dummy_2
781 from mtl_item_uoms_view
782 where ( organization_id = l_organization_id
783 or l_organization_id is NULL )
784 and uom_code = p_PRICING_ATTR_rec.product_uom_code
785 and inventory_item_id = to_number(p_PRICING_ATTR_rec.product_attr_value);
786
787 EXCEPTION
788 WHEN NO_DATA_FOUND THEN
789 l_return_status := FND_API.G_RET_STS_ERROR;
790
791 FND_MESSAGE.SET_NAME('QP','QP_INVALID_PROD_UOM');
792 OE_MSG_PUB.Add;
793
794 END;
795
796 END IF;
797
798 IF p_PRICING_ATTR_rec.product_attribute_context = 'ITEM' -- Item Category
799 AND p_PRICING_ATTR_rec.product_attribute = 'PRICING_ATTRIBUTE2'
800 THEN
801
802 oe_debug_pub.add('CCCCAAAATTTT');
803
804 -- the validation code that was here has been abstracted out to
805 -- package QP_CATEGORY_MAPPING_RULE for 11i10 Product Catalog
806
807 -- Get the list_header_id from the list_line_id, since the list_header_id is not set in
808 -- the p_PRICING_ATTR_rec.list_header_id yet.
809 select list_header_id into l_uom_list_header_id
810 from qp_list_lines
811 where list_line_id = p_PRICING_ATTR_rec.list_line_id;
812
813 IF NOT QP_VALIDATE.Product_Uom(p_PRICING_ATTR_rec.product_uom_code, -- sfiresto 4753707
814 to_number(p_PRICING_ATTR_rec.product_attr_value),
815 l_uom_list_header_id)
816 THEN
817 l_return_status := FND_API.G_RET_STS_ERROR;
818 FND_MESSAGE.SET_NAME('QP','QP_INVALID_PROD_UOM');
819 OE_MSG_PUB.Add;
820 END IF;
821
822 END IF;
823
824 IF p_PRICING_ATTR_rec.product_attribute_context = 'ITEM' -- All Items
825 AND p_PRICING_ATTR_rec.product_attribute = 'PRICING_ATTRIBUTE3'
826 THEN
827
828 oe_debug_pub.add('AAAAAAALLLLLLL');
829 BEGIN
830
831 select distinct uom_code
832 into l_dummy_2
833 from mtl_units_of_measure_vl
834 where uom_code = p_PRICING_ATTR_rec.product_uom_code;
835
836 EXCEPTION
837 WHEN NO_DATA_FOUND THEN
838 l_return_status := FND_API.G_RET_STS_ERROR;
839
840 FND_MESSAGE.SET_NAME('QP','QP_INVALID_PROD_UOM');
841 OE_MSG_PUB.Add;
842
843 END;
844
845 END IF;
846
847 END IF;
848
849 /* Product is mandatory for Item Upgrade, Other Item Discount, Promotional Goods, Related Products and Price Break Header */
850
851 IF list_line.list_line_type_code = 'IUE'
852 OR list_line.list_line_type_code = 'OID'
853 OR list_line.list_line_type_code = 'PRG'
854 OR list_line.list_line_type_code = 'RLTD'
855 OR list_line.list_line_type_code = 'PBH'
856 OR list_line.list_line_type_code = 'TSN'
857 THEN
858
859 IF p_PRICING_ATTR_rec.product_attribute_context IS NULL
860 OR p_PRICING_ATTR_rec.product_attribute IS NULL
861 OR p_PRICING_ATTR_rec.product_attr_value IS NULL
862 THEN
863
864 l_return_status := FND_API.G_RET_STS_ERROR;
865
866 FND_MESSAGE.SET_NAME('QP','QP_ATTRIBUTE_REQUIRED');
867 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',QP_PRC_UTIL.Get_Attribute_Name('PRODUCT_ATTRIBUTE_CONTEXT')||'/'||
868 QP_PRC_UTIL.Get_Attribute_Name('PRODUCT_ATTRIBUTE')||'/'||
869 QP_PRC_UTIL.Get_Attribute_Name('PRODUCT_ATTR_VALUE')); -- Fix For Bug-1974413
870 OE_MSG_PUB.Add;
871
872 END IF;
873
874 END IF;
875
876 /* Item Number is mandatory for Item Upgrade - cannot be category or ALL */
877
878 IF list_line.list_line_type_code = 'IUE'
879 AND p_PRICING_ATTR_rec.product_attribute_context = 'ITEM'
880 AND p_PRICING_ATTR_rec.product_attribute <> 'PRICING_ATTRIBUTE1'
881 THEN
882
883 l_return_status := FND_API.G_RET_STS_ERROR;
884
885 FND_MESSAGE.SET_NAME('QP','QP_ITEM_MAND_FOR_IUE');
886 OE_MSG_PUB.Add;
887
888 END IF;
889
890 /* Item Number is mandatory for Get Products - cannot be category or ALL */
891
892 -- julin [3754832]: using cursor to reuse sql
893 l_dummy_3 := null;
894 OPEN to_rltd_modifier_id_cur(p_PRICING_ATTR_rec.list_line_id, 'BENEFIT');
895 FETCH to_rltd_modifier_id_cur INTO l_dummy_3;
896 CLOSE to_rltd_modifier_id_cur;
897
898 IF l_dummy_3 is not null THEN
899 IF p_PRICING_ATTR_rec.product_attribute_context = 'ITEM'
900 AND p_PRICING_ATTR_rec.product_attribute <> 'PRICING_ATTRIBUTE1'
901 THEN
902
903 l_return_status := FND_API.G_RET_STS_ERROR;
904
905 FND_MESSAGE.SET_NAME('QP','QP_ITEM_MAND_FOR_GET');
906 OE_MSG_PUB.Add;
907
908 END IF;
909
910 /* Get record must be of the type Discount (DIS) for Other Item Discount and Promotional Goods */
911
912 IF list_line.list_line_type_code <> 'DIS'
913 THEN
914
915 l_return_status := FND_API.G_RET_STS_ERROR;
916
917 FND_MESSAGE.SET_NAME('QP','QP_GET_MUST_BE_DISCOUNT');
918 OE_MSG_PUB.Add;
919
920 END IF;
921
922 END IF;
923
924 /* Additional products must be of the type Related (RLTD) for Other Item Discount and Promotional Goods */
925
926 -- julin [3754832]: using cursor to reuse sql
927 l_dummy_3 := null;
928 OPEN to_rltd_modifier_id_cur(p_PRICING_ATTR_rec.list_line_id, 'QUALIFIER');
929 FETCH to_rltd_modifier_id_cur INTO l_dummy_3;
930 CLOSE to_rltd_modifier_id_cur;
931
932 IF l_dummy_3 is not null THEN
933 IF list_line.list_line_type_code <> 'RLTD'
934 THEN
935
936 l_return_status := FND_API.G_RET_STS_ERROR;
937
938 FND_MESSAGE.SET_NAME('QP','QP_ADD_MUST_BE_RELATED');
939 OE_MSG_PUB.Add;
940
941 END IF;
942
943 END IF;
944
945 END IF; --list_line_type_code <> 'PMR'
946
947 /* Product precedence is mandatory on a Modifier line if a product exists in the pricing attributes */
948
949
950 IF list_line.list_line_type_code = 'DIS'
951 OR list_line.list_line_type_code = 'SUR'
952 OR list_line.list_line_type_code = 'FREIGHT_CHARGE'
953 OR list_line.list_line_type_code = 'PRG'
954 THEN
955
956 BEGIN
957
958 select count(*)
959 into l_count
960 from qp_rltd_modifiers
961 where to_rltd_modifier_id = p_PRICING_ATTR_rec.list_line_id;
962
963 EXCEPTION
964 when no_data_found then
965 l_count := 0;
966
967 END;
968
969 END IF;
970
971 oe_debug_pub.add('prod line id '|| to_char(p_PRICING_ATTR_rec.list_line_id));
972 oe_debug_pub.add('line id '|| to_char(list_line.list_line_id));
973 oe_debug_pub.add('list line type '|| list_line.list_line_type_code);
974 oe_debug_pub.add('prece '|| to_char(list_line.product_precedence));
975
976
977 IF (((list_line.list_line_type_code = 'DIS'
978 OR list_line.list_line_type_code = 'SUR'
979 OR list_line.list_line_type_code = 'FREIGHT_CHARGE'
980 OR list_line.list_line_type_code = 'PRG')
981 AND l_count = 0 )
982 OR (list_line.list_line_type_code = 'TSN'))
983 AND list_line.product_precedence IS NULL
984 THEN
985
986 oe_debug_pub.add('prece 22 ');
987 --changes by spgopal for bug 1466254
988 --precedence is mandatory for pricing attribute records to exclude product
989 if p_PRICING_ATTR_rec.excluder_flag = 'N' then
990 l_return_status := FND_API.G_RET_STS_ERROR;
991
992 FND_MESSAGE.SET_NAME('QP','QP_ATTRIBUTE_REQUIRED');
993 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',QP_PRC_UTIL.Get_Attribute_Name('PRODUCT_PRECEDENCE')); -- Fix For Bug-1974413
994 OE_MSG_PUB.Add;
995 end if;
996
997 END IF;
998
999 /* Recurring must have comparison operator as =
1000
1001 IF list_line.list_line_type_code <> 'PMR' THEN
1002 IF list_line.price_break_type_code = 'RECURRING'
1003 AND p_PRICING_ATTR_rec.comparison_operator_code <> '='
1004 THEN
1005
1006 l_return_status := FND_API.G_RET_STS_ERROR;
1007
1008 FND_MESSAGE.SET_NAME('QP','QP_RECUR_OPER_MUST_BE_EQUL');
1009 OE_MSG_PUB.Add;
1010
1011 END IF;
1012
1013 Point or Range must have comparison operator as Between
1014
1015 IF (list_line.price_break_type_code = 'POINT'
1016 OR list_line.price_break_type_code = 'RANGE')
1017 AND p_PRICING_ATTR_rec.comparison_operator_code <> 'BETWEEN'
1018 THEN
1019
1020 l_return_status := FND_API.G_RET_STS_ERROR;
1021
1022 FND_MESSAGE.SET_NAME('QP','QP_POINT_RANGE_OPER_BETWEEN');
1023 OE_MSG_PUB.Add;
1024
1025 END IF; */
1026
1027 oe_debug_pub.add('pric context = '||p_PRICING_ATTR_rec.pricing_attribute_context);
1028 oe_debug_pub.add('comp oper = '|| p_PRICING_ATTR_rec.comparison_operator_code);
1029 oe_debug_pub.add('list type = '|| list_line.list_line_type_code);
1030
1031 IF list_line.list_line_type_code <> 'PMR' THEN
1032 IF p_PRICING_ATTR_rec.pricing_attribute_context = 'VOLUME'
1033 AND (p_PRICING_ATTR_rec.comparison_operator_code <> 'BETWEEN'
1034 OR p_PRICING_ATTR_rec.comparison_operator_code IS NULL )
1035 THEN
1036
1037 oe_debug_pub.add('I am herrrrr');
1038 l_return_status := FND_API.G_RET_STS_ERROR;
1039
1040 FND_MESSAGE.SET_NAME('QP','QP_BETW_MAND_FOR_VOLUME');
1041 OE_MSG_PUB.Add;
1042
1043 END IF;
1044
1045 /* Select the Primary line type of the current record */
1046
1047 BEGIN
1048
1049 SELECT LIST_LINE_TYPE_CODE
1050 INTO l_primary_list_line_type_code
1051 FROM QP_LIST_LINES
1052 WHERE LIST_LINE_ID = ( select from_rltd_modifier_id
1053 from qp_rltd_modifiers
1054 where to_rltd_modifier_id
1055 = p_PRICING_ATTR_rec.list_line_id);
1056
1057 EXCEPTION
1058 WHEN NO_DATA_FOUND THEN
1059 l_primary_list_line_type_code := NULL;
1060
1061 END;
1062
1063 /* Not to be done - discussed with Alison - If the Primary line is a Coupon Issue and the Child lines are DIS or PRG, and if the product is entered, product precedence is mandatory
1064
1065 IF l_primary_list_line_type_code = 'CIE'
1066 AND ( list_line.list_line_type_code = 'DIS'
1067 OR list_line.list_line_type_code = 'PRG')
1068 AND list_line.product_precedence IS NULL
1069 THEN
1070
1071 oe_debug_pub.add('prece 33 ');
1072 l_return_status := FND_API.G_RET_STS_ERROR;
1073
1074 FND_MESSAGE.SET_NAME('QP','QP_ATTRIBUTE_REQUIRED');
1075 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','Precedence');
1076 OE_MSG_PUB.Add;
1077
1078 END IF; */
1079
1080 /* If the Primary line is a OID or PRG, and if the product is entered, proration type code is mandatory */
1081
1082 IF (l_primary_list_line_type_code = 'OID'
1083 OR l_primary_list_line_type_code = 'PRG')
1084 AND list_line.proration_type_code IS NULL
1085 THEN
1086
1087 l_return_status := FND_API.G_RET_STS_ERROR;
1088
1089 FND_MESSAGE.SET_NAME('QP','QP_ATTRIBUTE_REQUIRED');
1090 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',QP_PRC_UTIL.Get_Attribute_Name('PRORATION_TYPE_CODE')); -- Fix For Bug-1974413
1091 OE_MSG_PUB.Add;
1092
1093 END IF;
1094
1095 /* If the Primary line is a Price Break Header and the Child lines are DIS or SUR, and if the product is entered, proration type code is mandatory */
1096
1097 IF l_primary_list_line_type_code = 'PBH'
1098 AND ( list_line.list_line_type_code = 'DIS'
1099 OR list_line.list_line_type_code = 'SUR')
1100 AND list_line.proration_type_code IS NULL
1101 THEN
1102
1103 l_return_status := FND_API.G_RET_STS_ERROR;
1104
1105 FND_MESSAGE.SET_NAME('QP','QP_ATTRIBUTE_REQUIRED');
1106 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',QP_PRC_UTIL.Get_Attribute_Name('PRORATION_TYPE_CODE')); -- Fix For Bug-1974413
1107 OE_MSG_PUB.Add;
1108
1109 END IF;
1110
1111 oe_debug_pub.add('end uom ');
1112 END IF; --If list_line_type_code <> 'PMR'
1113
1114 IF p_PRICING_ATTR_rec.pricing_attribute_context IS NOT NULL
1115 THEN
1116
1117 QP_UTIL.validate_context_code(p_flexfield_name => 'QP_ATTR_DEFNS_PRICING'
1118 ,p_application_short_name => 'QP'
1119 ,p_context_name => p_PRICING_ATTR_rec.pricing_attribute_context
1120 ,p_error_code => l_error_code);
1121
1122 IF (l_error_code <> 0 ) -- invalid context
1123 THEN
1124 l_return_status := FND_API.G_RET_STS_ERROR;
1125
1126 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR)
1127 THEN
1128
1129 FND_MESSAGE.SET_NAME('QP','QP_INVALID_PRICING_CONTEXT' );
1130 OE_MSG_PUB.Add;
1131 END IF;
1132
1133 END IF;
1134 END IF;
1135
1136 /* If pricing context is VOLUME, price break is mandatory */
1137
1138 IF list_line.list_line_type_code <> 'PMR' THEN
1139 IF p_PRICING_ATTR_rec.pricing_attribute_context = 'VOLUME'
1140 AND list_line.price_break_type_code IS NULL
1141 THEN
1142
1143 l_return_status := FND_API.G_RET_STS_ERROR;
1144
1145 FND_MESSAGE.SET_NAME('QP','QP_BREAK_MAND_FOR_VOLUME');
1146 OE_MSG_PUB.Add;
1147
1148 END IF;
1149
1150 IF p_PRICING_ATTR_rec.pricing_attribute_context IS NOT NULL
1151 AND p_PRICING_ATTR_rec.pricing_attribute IS NOT NULL
1152 THEN
1153
1154 QP_UTIL.validate_attribute_name(p_application_short_name => 'QP'
1155 ,p_flexfield_name => 'QP_ATTR_DEFNS_PRICING'
1156 ,p_context_name => p_PRICING_ATTR_rec.pricing_attribute_context
1157 ,p_attribute_name => p_PRICING_ATTR_rec.pricing_attribute
1158 ,p_error_code => l_error_code);
1159
1160 IF (l_error_code <> 0 ) -- invalid context
1161 THEN
1162 l_return_status := FND_API.G_RET_STS_ERROR;
1163
1164 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR)
1165 THEN
1166
1167 FND_MESSAGE.SET_NAME('QP','QP_INVALID_PRICING_ATTR' );
1168 OE_MSG_PUB.Add;
1169 END IF;
1170
1171 END IF;
1172
1173 /* If pricing attribute is VOLUME, for RLTD record, comparison operator must be BETWEEN */
1174
1175 IF p_PRICING_ATTR_rec.pricing_attribute_context = 'VOLUME'
1176 AND list_line.list_line_type_code = 'RLTD'
1177 AND p_PRICING_ATTR_rec.comparison_operator_code <> 'BETWEEN'
1178 THEN
1179
1180 l_return_status := FND_API.G_RET_STS_ERROR;
1181
1182 FND_MESSAGE.SET_NAME('QP','QP_BREAK_MAND_FOR_VOLUME');
1183 OE_MSG_PUB.Add;
1184
1185 END IF;
1186
1187 END IF; --If Pricing Attribute and Context are not null
1188
1189 /* Call this procedure to validate pricing_attr_value_from since it may have valid values from the LOV like color as 'BLUE'. This procedure also checks for invalid canonical format */
1190
1191 IF p_PRICING_ATTR_rec.pricing_attr_value_from IS NOT NULL
1192 AND p_PRICING_ATTR_rec.comparison_operator_code = '='
1193 THEN
1194
1195 QP_UTIL.validate_qp_flexfield(flexfield_name =>'QP_ATTR_DEFNS_PRICING'
1196 ,context =>p_PRICING_ATTR_rec.pricing_attribute_context
1197 ,attribute =>p_PRICING_ATTR_rec.pricing_attribute
1198 ,value =>p_PRICING_ATTR_rec.pricing_attr_value_from
1199 ,application_short_name => 'QP'
1200 ,context_flag =>l_context_flag
1201 ,attribute_flag =>l_attribute_flag
1202 ,value_flag =>l_value_flag
1203 ,datatype =>l_datatype
1204 ,precedence =>l_precedence
1205 ,error_code =>l_error_code
1206 );
1207
1208 If (l_context_flag = 'N' AND l_error_code = 9) -- invalid value
1209 Then
1210 l_return_status := FND_API.G_RET_STS_ERROR;
1211
1212 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR)
1213 THEN
1214
1215 FND_MESSAGE.SET_NAME('QP','QP_INVALID_ATTRIBUTE');
1216 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','Value From');
1217 OE_MSG_PUB.Add;
1218 END IF;
1219
1220 End If;
1221 oe_debug_pub.add('pricing attr value from = '|| p_PRICING_ATTR_rec.pricing_attr_value_from);
1222 oe_debug_pub.add('from datatype = '||l_datatype);
1223 oe_debug_pub.add('from error = '||to_char(l_error_code));
1224
1225 END IF;
1226
1227 /* Value To cannot be given with Recurring break type */
1228 /*commenting this validation, value_to is mandatory, so we will default a huge no for value_to with recurring - spgopal for bug 1566429*/
1229 /*
1230 IF p_PRICING_ATTR_rec.pricing_attr_value_to IS NOT NULL
1231 AND list_line.price_break_type_code = 'RECURRING'
1232 THEN
1233
1234 l_return_status := FND_API.G_RET_STS_ERROR;
1235
1236 FND_MESSAGE.SET_NAME('QP','QP_VALUE_TO_NOT_WITH_RECUR');
1237 OE_MSG_PUB.Add;
1238
1239 END IF;
1240 */
1241
1242 /* Call this procedure to validate pricing_attr_value_to for invalid canonical format */
1243
1244 IF p_PRICING_ATTR_rec.pricing_attr_value_to IS NOT NULL
1245 AND p_PRICING_ATTR_rec.comparison_operator_code = 'BETWEEN'
1246 THEN
1247
1248 oe_debug_pub.add('pricing attr value to = '|| p_PRICING_ATTR_rec.pricing_attr_value_to);
1249 oe_debug_pub.add('pricing attr datatype = '|| p_PRICING_ATTR_rec.pricing_attribute_datatype);
1250
1251 l_error_code := QP_UTIL.validate_num_date(p_PRICING_ATTR_rec.pricing_attribute_datatype,
1252 p_PRICING_ATTR_rec.pricing_attr_value_to);
1253
1254 oe_debug_pub.add('to error = '||to_char(l_error_code));
1255 If (l_error_code = 9) -- invalid value
1256 Then
1257 l_return_status := FND_API.G_RET_STS_ERROR;
1258
1259 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR)
1260 THEN
1261
1262 FND_MESSAGE.SET_NAME('QP','QP_INVALID_ATTRIBUTE');
1263 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','Value To');
1264 OE_MSG_PUB.Add;
1265 END IF;
1266
1267 End If;
1268
1269 END IF;
1270
1271 l_qp_status := QP_UTIL.GET_QP_STATUS;
1272
1273 -- For bug 2363065, raise the error in basic pricing if not called from FTE
1274 IF (p_PRICING_ATTR_rec.pricing_attribute_context IS NOT NULL
1275 AND p_PRICING_ATTR_rec.pricing_attribute IS NOT NULL
1276 AND (p_PRICING_ATTR_rec.pricing_attr_value_from IS NOT NULL
1277 OR p_PRICING_ATTR_rec.pricing_attr_value_to IS NOT NULL )
1278 AND l_qp_status = 'S'
1279 AND QP_MOD_LOADER_PUB.G_PROCESS_LST_REQ_TYPE <> 'FTE')
1280 THEN
1281
1282 BEGIN
1283
1284 SELECT COUNT(*)
1285 INTO l_no_pricing_attr
1286 FROM QP_PRICING_ATTRIBUTES
1287 WHERE LIST_LINE_ID = p_PRICING_ATTR_rec.list_line_id
1288 AND PRICING_ATTRIBUTE_CONTEXT <> 'VOLUME';
1289
1290 IF nvl(l_no_pricing_attr,0) = 1
1291 THEN
1292 l_return_status := FND_API.G_RET_STS_ERROR;
1293
1294 FND_MESSAGE.SET_NAME('QP','QP_1_PRICING_ATTR_FOR_BASIC');
1295 OE_MSG_PUB.Add;
1296
1297 END IF;
1298
1299 EXCEPTION
1300 WHEN NO_DATA_FOUND THEN
1301 null;
1302
1303 END;
1304
1305 END IF;
1306
1307 END IF; -- list_line_type_code <> 'PMR'
1308
1309 -- Return Error if a required attribute is missing.
1310
1311 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1312
1313 RAISE FND_API.G_EXC_ERROR;
1314
1315 END IF;
1316
1317 END LOOP;
1318
1319 --
1320 -- Check conditionally required attributes here.
1321 --
1322
1323
1324 --
1325 -- Validate attribute dependencies here.
1326 --
1327 --Raise a warning if the Pricing/Product Attribute being used in setup
1328 --has a sourcing method of 'ATTRIBUTE MAPPING' but is not sourcing-enabled
1329 --or if its sourcing_status is not 'Y', i.e., the build sourcing conc.
1330 --program has to be run.
1331
1332 oe_debug_pub.add('Here 0000');
1333 IF qp_util.attrmgr_installed = 'Y' THEN
1334 oe_debug_pub.add('Here 1111');
1335 IF p_Pricing_Attr_rec.product_attribute_context IS NOT NULL AND
1336 p_Pricing_Attr_rec.product_attribute IS NOT NULL
1337 THEN
1338 oe_debug_pub.add('Here 2222');
1339 QP_UTIL.Get_Context_Type('QP_ATTR_DEFNS_PRICING',
1340 p_Pricing_Attr_rec.product_attribute_context,
1341 l_context_type,
1342 l_error_code);
1343
1344 IF l_error_code = 0 THEN --successfully returned context_type
1345
1346 oe_debug_pub.add('Here 3333');
1347 QP_UTIL.Get_Sourcing_Info(l_context_type,
1348 p_Pricing_Attr_rec.product_attribute_context,
1349 p_Pricing_Attr_rec.product_attribute,
1350 l_sourcing_enabled,
1351 l_sourcing_status,
1352 l_sourcing_method);
1353
1354 IF l_sourcing_method = 'ATTRIBUTE MAPPING' THEN
1355
1356 oe_debug_pub.add('Here 4444');
1357 IF l_sourcing_enabled <> 'Y' THEN
1358
1359 oe_debug_pub.add('Here 5555');
1360 FND_MESSAGE.SET_NAME('QP','QP_ENABLE_SOURCING');
1361 FND_MESSAGE.SET_TOKEN('CONTEXT',
1362 p_Pricing_Attr_rec.product_attribute_context);
1363 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',
1364 p_Pricing_Attr_rec.product_attribute);
1365 OE_MSG_PUB.Add;
1366
1367 END IF;
1368
1369 IF l_sourcing_status <> 'Y' THEN
1370
1371 oe_debug_pub.add('Here 6666');
1372 FND_MESSAGE.SET_NAME('QP','QP_BUILD_SOURCING_RULES');
1373 FND_MESSAGE.SET_TOKEN('CONTEXT',
1374 p_Pricing_Attr_rec.product_attribute_context);
1375 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',
1376 p_Pricing_Attr_rec.product_attribute);
1377 OE_MSG_PUB.Add;
1378
1379 END IF;
1380
1381 END IF; --If sourcing_method = 'ATTRIBUTE MAPPING'
1382
1383 END IF; --l_error_code = 0
1384
1385 END IF;--If product_attribute_context and product_attribute are NOT NULL
1386
1387 IF p_Pricing_Attr_rec.pricing_attribute_context IS NOT NULL AND
1388 p_Pricing_Attr_rec.pricing_attribute IS NOT NULL
1389 THEN
1390 QP_UTIL.Get_Context_Type('QP_ATTR_DEFNS_PRICING',
1391 p_Pricing_Attr_rec.pricing_attribute_context,
1392 l_context_type,
1393 l_error_code);
1394
1395 IF l_error_code = 0 THEN --successfully returned context_type
1396
1397 QP_UTIL.Get_Sourcing_Info(l_context_type,
1398 p_Pricing_Attr_rec.pricing_attribute_context,
1399 p_Pricing_Attr_rec.pricing_attribute,
1400 l_sourcing_enabled,
1401 l_sourcing_status,
1402 l_sourcing_method);
1403
1404 IF l_sourcing_method = 'ATTRIBUTE MAPPING' THEN
1405
1406 IF l_sourcing_enabled <> 'Y' THEN
1407
1408 FND_MESSAGE.SET_NAME('QP','QP_ENABLE_SOURCING');
1409 FND_MESSAGE.SET_TOKEN('CONTEXT',
1410 p_Pricing_Attr_rec.pricing_attribute_context);
1411 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',
1412 p_Pricing_Attr_rec.pricing_attribute);
1413 OE_MSG_PUB.Add;
1414
1415 END IF;
1416
1417 IF l_sourcing_status <> 'Y' THEN
1418
1419 FND_MESSAGE.SET_NAME('QP','QP_BUILD_SOURCING_RULES');
1420 FND_MESSAGE.SET_TOKEN('CONTEXT',
1421 p_Pricing_Attr_rec.pricing_attribute_context);
1422 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',
1423 p_Pricing_Attr_rec.pricing_attribute);
1424 OE_MSG_PUB.Add;
1425
1426 END IF;
1427
1428 END IF; --If sourcing_method = 'ATTRIBUTE MAPPING'
1429
1430 END IF; --l_error_code = 0
1431
1432 END IF;--If pricing_attribute_context and pricing_attribute are NOT NULL
1433
1434 END IF; --qp_util.attrmgr_installed = 'Y'
1435
1436 -- Done validating entity
1437
1438 x_return_status := l_return_status;
1439
1440 oe_debug_pub.add('END Entity in QPXLPRAB');
1441
1442 EXCEPTION
1443
1444 WHEN FND_API.G_EXC_ERROR THEN
1445
1446 x_return_status := FND_API.G_RET_STS_ERROR;
1447 oe_debug_pub.add('EXP error');
1448
1449 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1450
1451 oe_debug_pub.add('EXP unexpect error');
1452 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1453
1454 WHEN OTHERS THEN
1455
1456 oe_debug_pub.add('EXP others');
1457 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1458
1459 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1460 THEN
1461 OE_MSG_PUB.Add_Exc_Msg
1462 ( G_PKG_NAME
1463 , 'Entity'
1464 );
1465 END IF;
1466
1467 END Entity;
1468
1469 -- Procedure Attributes
1470
1471 PROCEDURE Attributes
1472 ( x_return_status OUT NOCOPY VARCHAR2
1473 , p_PRICING_ATTR_rec IN QP_Modifiers_PUB.Pricing_Attr_Rec_Type
1474 , p_old_PRICING_ATTR_rec IN QP_Modifiers_PUB.Pricing_Attr_Rec_Type :=
1475 QP_Modifiers_PUB.G_MISS_PRICING_ATTR_REC
1476 )
1477 IS
1478 BEGIN
1479
1480 oe_debug_pub.add('BEGIN Attributes in QPXLPRAB');
1481
1482 x_return_status := FND_API.G_RET_STS_SUCCESS;
1483
1484 -- Validate PRICING_ATTR attributes
1485
1486 IF p_PRICING_ATTR_rec.accumulate_flag IS NOT NULL AND
1487 ( p_PRICING_ATTR_rec.accumulate_flag <>
1488 p_old_PRICING_ATTR_rec.accumulate_flag OR
1489 p_old_PRICING_ATTR_rec.accumulate_flag IS NULL )
1490 THEN
1491 IF NOT QP_Validate.Accumulate(p_PRICING_ATTR_rec.accumulate_flag) THEN
1492 x_return_status := FND_API.G_RET_STS_ERROR;
1493 END IF;
1494 END IF;
1495
1496 IF p_PRICING_ATTR_rec.attribute_grouping_no IS NOT NULL AND
1497 ( p_PRICING_ATTR_rec.attribute_grouping_no <>
1498 p_old_PRICING_ATTR_rec.attribute_grouping_no OR
1499 p_old_PRICING_ATTR_rec.attribute_grouping_no IS NULL )
1500 THEN
1501 IF NOT QP_Validate.Attribute_Grouping_No(p_PRICING_ATTR_rec.attribute_grouping_no) THEN
1502 x_return_status := FND_API.G_RET_STS_ERROR;
1503 END IF;
1504 END IF;
1505
1506 IF p_PRICING_ATTR_rec.created_by IS NOT NULL AND
1507 ( p_PRICING_ATTR_rec.created_by <>
1508 p_old_PRICING_ATTR_rec.created_by OR
1509 p_old_PRICING_ATTR_rec.created_by IS NULL )
1510 THEN
1511 IF NOT QP_Validate.Created_By(p_PRICING_ATTR_rec.created_by) THEN
1512 x_return_status := FND_API.G_RET_STS_ERROR;
1513 END IF;
1514 END IF;
1515
1516 IF p_PRICING_ATTR_rec.creation_date IS NOT NULL AND
1517 ( p_PRICING_ATTR_rec.creation_date <>
1518 p_old_PRICING_ATTR_rec.creation_date OR
1519 p_old_PRICING_ATTR_rec.creation_date IS NULL )
1520 THEN
1521 IF NOT QP_Validate.Creation_Date(p_PRICING_ATTR_rec.creation_date) THEN
1522 x_return_status := FND_API.G_RET_STS_ERROR;
1523 END IF;
1524 END IF;
1525
1526 IF p_PRICING_ATTR_rec.excluder_flag IS NOT NULL AND
1527 ( p_PRICING_ATTR_rec.excluder_flag <>
1528 p_old_PRICING_ATTR_rec.excluder_flag OR
1529 p_old_PRICING_ATTR_rec.excluder_flag IS NULL )
1530 THEN
1531 IF NOT QP_Validate.Excluder(p_PRICING_ATTR_rec.excluder_flag) THEN
1532 x_return_status := FND_API.G_RET_STS_ERROR;
1533 END IF;
1534 END IF;
1535
1536 IF p_PRICING_ATTR_rec.last_updated_by IS NOT NULL AND
1537 ( p_PRICING_ATTR_rec.last_updated_by <>
1538 p_old_PRICING_ATTR_rec.last_updated_by OR
1539 p_old_PRICING_ATTR_rec.last_updated_by IS NULL )
1540 THEN
1541 IF NOT QP_Validate.Last_Updated_By(p_PRICING_ATTR_rec.last_updated_by) THEN
1542 x_return_status := FND_API.G_RET_STS_ERROR;
1543 END IF;
1544 END IF;
1545
1546 IF p_PRICING_ATTR_rec.last_update_date IS NOT NULL AND
1547 ( p_PRICING_ATTR_rec.last_update_date <>
1548 p_old_PRICING_ATTR_rec.last_update_date OR
1549 p_old_PRICING_ATTR_rec.last_update_date IS NULL )
1550 THEN
1551 IF NOT QP_Validate.Last_Update_Date(p_PRICING_ATTR_rec.last_update_date) THEN
1552 x_return_status := FND_API.G_RET_STS_ERROR;
1553 END IF;
1554 END IF;
1555
1556 IF p_PRICING_ATTR_rec.last_update_login IS NOT NULL AND
1557 ( p_PRICING_ATTR_rec.last_update_login <>
1558 p_old_PRICING_ATTR_rec.last_update_login OR
1559 p_old_PRICING_ATTR_rec.last_update_login IS NULL )
1560 THEN
1561 IF NOT QP_Validate.Last_Update_Login(p_PRICING_ATTR_rec.last_update_login) THEN
1562 x_return_status := FND_API.G_RET_STS_ERROR;
1563 END IF;
1564 END IF;
1565
1566 IF p_PRICING_ATTR_rec.list_line_id IS NOT NULL AND
1567 ( p_PRICING_ATTR_rec.list_line_id <>
1568 p_old_PRICING_ATTR_rec.list_line_id OR
1569 p_old_PRICING_ATTR_rec.list_line_id IS NULL )
1570 THEN
1571 IF NOT QP_Validate.List_Line(p_PRICING_ATTR_rec.list_line_id) THEN
1572 x_return_status := FND_API.G_RET_STS_ERROR;
1573 END IF;
1574 END IF;
1575
1576 IF p_PRICING_ATTR_rec.pricing_attribute IS NOT NULL AND
1577 ( p_PRICING_ATTR_rec.pricing_attribute <>
1578 p_old_PRICING_ATTR_rec.pricing_attribute OR
1579 p_old_PRICING_ATTR_rec.pricing_attribute IS NULL )
1580 THEN
1581 IF NOT QP_Validate.Pricing_Attribute(p_PRICING_ATTR_rec.pricing_attribute) THEN
1582 x_return_status := FND_API.G_RET_STS_ERROR;
1583 END IF;
1584 END IF;
1585
1586 IF p_PRICING_ATTR_rec.pricing_attribute_context IS NOT NULL AND
1587 ( p_PRICING_ATTR_rec.pricing_attribute_context <>
1588 p_old_PRICING_ATTR_rec.pricing_attribute_context OR
1589 p_old_PRICING_ATTR_rec.pricing_attribute_context IS NULL )
1590 THEN
1591 IF NOT QP_Validate.Pricing_Attribute_Context(p_PRICING_ATTR_rec.pricing_attribute_context) THEN
1592 x_return_status := FND_API.G_RET_STS_ERROR;
1593 END IF;
1594 END IF;
1595
1596 IF p_PRICING_ATTR_rec.pricing_attribute_id IS NOT NULL AND
1597 ( p_PRICING_ATTR_rec.pricing_attribute_id <>
1598 p_old_PRICING_ATTR_rec.pricing_attribute_id OR
1599 p_old_PRICING_ATTR_rec.pricing_attribute_id IS NULL )
1600 THEN
1601 IF NOT QP_Validate.Pricing_Attribute(p_PRICING_ATTR_rec.pricing_attribute_id) THEN
1602 x_return_status := FND_API.G_RET_STS_ERROR;
1603 END IF;
1604 END IF;
1605
1606 IF p_PRICING_ATTR_rec.pricing_attr_value_from IS NOT NULL AND
1607 ( p_PRICING_ATTR_rec.pricing_attr_value_from <>
1608 p_old_PRICING_ATTR_rec.pricing_attr_value_from OR
1609 p_old_PRICING_ATTR_rec.pricing_attr_value_from IS NULL )
1610 THEN
1611 IF NOT QP_Validate.Pricing_Attr_Value_From(p_PRICING_ATTR_rec.pricing_attr_value_from) THEN
1612 x_return_status := FND_API.G_RET_STS_ERROR;
1613 END IF;
1614 END IF;
1615
1616 IF p_PRICING_ATTR_rec.pricing_attr_value_to IS NOT NULL AND
1617 ( p_PRICING_ATTR_rec.pricing_attr_value_to <>
1618 p_old_PRICING_ATTR_rec.pricing_attr_value_to OR
1619 p_old_PRICING_ATTR_rec.pricing_attr_value_to IS NULL )
1620 THEN
1621 IF NOT QP_Validate.Pricing_Attr_Value_To(p_PRICING_ATTR_rec.pricing_attr_value_to) THEN
1622 x_return_status := FND_API.G_RET_STS_ERROR;
1623 END IF;
1624 END IF;
1625
1626 IF p_PRICING_ATTR_rec.product_attribute IS NOT NULL AND
1627 ( p_PRICING_ATTR_rec.product_attribute <>
1628 p_old_PRICING_ATTR_rec.product_attribute OR
1629 p_old_PRICING_ATTR_rec.product_attribute IS NULL )
1630 THEN
1631 IF NOT QP_Validate.Product_Attribute(p_PRICING_ATTR_rec.product_attribute) THEN
1632 x_return_status := FND_API.G_RET_STS_ERROR;
1633 END IF;
1634 END IF;
1635
1636 IF p_PRICING_ATTR_rec.product_attribute_context IS NOT NULL AND
1637 ( p_PRICING_ATTR_rec.product_attribute_context <>
1638 p_old_PRICING_ATTR_rec.product_attribute_context OR
1639 p_old_PRICING_ATTR_rec.product_attribute_context IS NULL )
1640 THEN
1641 IF NOT QP_Validate.Product_Attribute_Context(p_PRICING_ATTR_rec.product_attribute_context) THEN
1642 x_return_status := FND_API.G_RET_STS_ERROR;
1643 END IF;
1644 END IF;
1645
1646 IF p_PRICING_ATTR_rec.product_attr_value IS NOT NULL AND
1647 ( p_PRICING_ATTR_rec.product_attr_value <>
1648 p_old_PRICING_ATTR_rec.product_attr_value OR
1649 p_old_PRICING_ATTR_rec.product_attr_value IS NULL )
1650 THEN
1651 IF NOT QP_Validate.Product_Attr_Value(p_PRICING_ATTR_rec.product_attr_value) THEN
1652 x_return_status := FND_API.G_RET_STS_ERROR;
1653 END IF;
1654 END IF;
1655
1656 IF p_PRICING_ATTR_rec.product_uom_code IS NOT NULL AND
1657 ( p_PRICING_ATTR_rec.product_uom_code <>
1658 p_old_PRICING_ATTR_rec.product_uom_code OR
1659 p_old_PRICING_ATTR_rec.product_uom_code IS NULL )
1660 THEN
1661 IF NOT QP_Validate.Product_Uom(p_PRICING_ATTR_rec.product_uom_code) THEN
1662 x_return_status := FND_API.G_RET_STS_ERROR;
1663 END IF;
1664 END IF;
1665
1666 IF p_PRICING_ATTR_rec.program_application_id IS NOT NULL AND
1667 ( p_PRICING_ATTR_rec.program_application_id <>
1668 p_old_PRICING_ATTR_rec.program_application_id OR
1669 p_old_PRICING_ATTR_rec.program_application_id IS NULL )
1670 THEN
1671 IF NOT QP_Validate.Program_Application(p_PRICING_ATTR_rec.program_application_id) THEN
1672 x_return_status := FND_API.G_RET_STS_ERROR;
1673 END IF;
1674 END IF;
1675
1676 IF p_PRICING_ATTR_rec.program_id IS NOT NULL AND
1677 ( p_PRICING_ATTR_rec.program_id <>
1678 p_old_PRICING_ATTR_rec.program_id OR
1679 p_old_PRICING_ATTR_rec.program_id IS NULL )
1680 THEN
1681 IF NOT QP_Validate.Program(p_PRICING_ATTR_rec.program_id) THEN
1682 x_return_status := FND_API.G_RET_STS_ERROR;
1683 END IF;
1684 END IF;
1685
1686 IF p_PRICING_ATTR_rec.program_update_date IS NOT NULL AND
1687 ( p_PRICING_ATTR_rec.program_update_date <>
1688 p_old_PRICING_ATTR_rec.program_update_date OR
1689 p_old_PRICING_ATTR_rec.program_update_date IS NULL )
1690 THEN
1691 IF NOT QP_Validate.Program_Update_Date(p_PRICING_ATTR_rec.program_update_date) THEN
1692 x_return_status := FND_API.G_RET_STS_ERROR;
1693 END IF;
1694 END IF;
1695
1696 IF p_PRICING_ATTR_rec.request_id IS NOT NULL AND
1697 ( p_PRICING_ATTR_rec.request_id <>
1698 p_old_PRICING_ATTR_rec.request_id OR
1699 p_old_PRICING_ATTR_rec.request_id IS NULL )
1700 THEN
1701 IF NOT QP_Validate.Request(p_PRICING_ATTR_rec.request_id) THEN
1702 x_return_status := FND_API.G_RET_STS_ERROR;
1703 END IF;
1704 END IF;
1705
1706 IF p_PRICING_ATTR_rec.product_attribute_datatype IS NOT NULL AND
1707 ( p_PRICING_ATTR_rec.product_attribute_datatype <>
1708 p_old_PRICING_ATTR_rec.product_attribute_datatype OR
1709 p_old_PRICING_ATTR_rec.product_attribute_datatype IS NULL )
1710 THEN
1711 IF NOT QP_Validate.Product_Attribute_Datatype(p_PRICING_ATTR_rec.product_attribute_datatype) THEN
1712 x_return_status := FND_API.G_RET_STS_ERROR;
1713 END IF;
1714 END IF;
1715
1716 IF p_PRICING_ATTR_rec.pricing_attribute_datatype IS NOT NULL AND
1717 ( p_PRICING_ATTR_rec.pricing_attribute_datatype <>
1718 p_old_PRICING_ATTR_rec.pricing_attribute_datatype OR
1719 p_old_PRICING_ATTR_rec.pricing_attribute_datatype IS NULL )
1720 THEN
1721 IF NOT QP_Validate.Pricing_Attribute_Datatype(p_PRICING_ATTR_rec.pricing_attribute_datatype) THEN
1722 x_return_status := FND_API.G_RET_STS_ERROR;
1723 END IF;
1724 END IF;
1725
1726 IF p_PRICING_ATTR_rec.comparison_operator_code IS NOT NULL AND
1727 ( p_PRICING_ATTR_rec.comparison_operator_code <>
1728 p_old_PRICING_ATTR_rec.comparison_operator_code OR
1729 p_old_PRICING_ATTR_rec.comparison_operator_code IS NULL )
1730 THEN
1731 IF NOT QP_Validate.Comparison_Operator(p_PRICING_ATTR_rec.comparison_operator_code) THEN
1732 x_return_status := FND_API.G_RET_STS_ERROR;
1733 END IF;
1734 END IF;
1735
1736 IF (p_PRICING_ATTR_rec.attribute1 IS NOT NULL AND
1737 ( p_PRICING_ATTR_rec.attribute1 <>
1738 p_old_PRICING_ATTR_rec.attribute1 OR
1739 p_old_PRICING_ATTR_rec.attribute1 IS NULL ))
1740 OR (p_PRICING_ATTR_rec.attribute10 IS NOT NULL AND
1741 ( p_PRICING_ATTR_rec.attribute10 <>
1742 p_old_PRICING_ATTR_rec.attribute10 OR
1743 p_old_PRICING_ATTR_rec.attribute10 IS NULL ))
1744 OR (p_PRICING_ATTR_rec.attribute11 IS NOT NULL AND
1745 ( p_PRICING_ATTR_rec.attribute11 <>
1746 p_old_PRICING_ATTR_rec.attribute11 OR
1747 p_old_PRICING_ATTR_rec.attribute11 IS NULL ))
1748 OR (p_PRICING_ATTR_rec.attribute12 IS NOT NULL AND
1749 ( p_PRICING_ATTR_rec.attribute12 <>
1750 p_old_PRICING_ATTR_rec.attribute12 OR
1751 p_old_PRICING_ATTR_rec.attribute12 IS NULL ))
1752 OR (p_PRICING_ATTR_rec.attribute13 IS NOT NULL AND
1753 ( p_PRICING_ATTR_rec.attribute13 <>
1754 p_old_PRICING_ATTR_rec.attribute13 OR
1755 p_old_PRICING_ATTR_rec.attribute13 IS NULL ))
1756 OR (p_PRICING_ATTR_rec.attribute14 IS NOT NULL AND
1757 ( p_PRICING_ATTR_rec.attribute14 <>
1758 p_old_PRICING_ATTR_rec.attribute14 OR
1759 p_old_PRICING_ATTR_rec.attribute14 IS NULL ))
1760 OR (p_PRICING_ATTR_rec.attribute15 IS NOT NULL AND
1761 ( p_PRICING_ATTR_rec.attribute15 <>
1762 p_old_PRICING_ATTR_rec.attribute15 OR
1763 p_old_PRICING_ATTR_rec.attribute15 IS NULL ))
1764 OR (p_PRICING_ATTR_rec.attribute2 IS NOT NULL AND
1765 ( p_PRICING_ATTR_rec.attribute2 <>
1766 p_old_PRICING_ATTR_rec.attribute2 OR
1767 p_old_PRICING_ATTR_rec.attribute2 IS NULL ))
1768 OR (p_PRICING_ATTR_rec.attribute3 IS NOT NULL AND
1769 ( p_PRICING_ATTR_rec.attribute3 <>
1770 p_old_PRICING_ATTR_rec.attribute3 OR
1771 p_old_PRICING_ATTR_rec.attribute3 IS NULL ))
1772 OR (p_PRICING_ATTR_rec.attribute4 IS NOT NULL AND
1773 ( p_PRICING_ATTR_rec.attribute4 <>
1774 p_old_PRICING_ATTR_rec.attribute4 OR
1775 p_old_PRICING_ATTR_rec.attribute4 IS NULL ))
1776 OR (p_PRICING_ATTR_rec.attribute5 IS NOT NULL AND
1777 ( p_PRICING_ATTR_rec.attribute5 <>
1778 p_old_PRICING_ATTR_rec.attribute5 OR
1779 p_old_PRICING_ATTR_rec.attribute5 IS NULL ))
1780 OR (p_PRICING_ATTR_rec.attribute6 IS NOT NULL AND
1781 ( p_PRICING_ATTR_rec.attribute6 <>
1782 p_old_PRICING_ATTR_rec.attribute6 OR
1783 p_old_PRICING_ATTR_rec.attribute6 IS NULL ))
1784 OR (p_PRICING_ATTR_rec.attribute7 IS NOT NULL AND
1785 ( p_PRICING_ATTR_rec.attribute7 <>
1786 p_old_PRICING_ATTR_rec.attribute7 OR
1787 p_old_PRICING_ATTR_rec.attribute7 IS NULL ))
1788 OR (p_PRICING_ATTR_rec.attribute8 IS NOT NULL AND
1789 ( p_PRICING_ATTR_rec.attribute8 <>
1790 p_old_PRICING_ATTR_rec.attribute8 OR
1791 p_old_PRICING_ATTR_rec.attribute8 IS NULL ))
1792 OR (p_PRICING_ATTR_rec.attribute9 IS NOT NULL AND
1793 ( p_PRICING_ATTR_rec.attribute9 <>
1794 p_old_PRICING_ATTR_rec.attribute9 OR
1795 p_old_PRICING_ATTR_rec.attribute9 IS NULL ))
1796 OR (p_PRICING_ATTR_rec.context IS NOT NULL AND
1797 ( p_PRICING_ATTR_rec.context <>
1798 p_old_PRICING_ATTR_rec.context OR
1799 p_old_PRICING_ATTR_rec.context IS NULL ))
1800 THEN
1801
1802 -- These calls are temporarily commented out
1803
1804 /*
1805 FND_FLEX_DESC_VAL.Set_Column_Value
1806 ( column_name => 'ATTRIBUTE1'
1807 , column_value => p_PRICING_ATTR_rec.attribute1
1808 );
1809 FND_FLEX_DESC_VAL.Set_Column_Value
1810 ( column_name => 'ATTRIBUTE10'
1811 , column_value => p_PRICING_ATTR_rec.attribute10
1812 );
1813 FND_FLEX_DESC_VAL.Set_Column_Value
1814 ( column_name => 'ATTRIBUTE11'
1815 , column_value => p_PRICING_ATTR_rec.attribute11
1816 );
1817 FND_FLEX_DESC_VAL.Set_Column_Value
1818 ( column_name => 'ATTRIBUTE12'
1819 , column_value => p_PRICING_ATTR_rec.attribute12
1820 );
1821 FND_FLEX_DESC_VAL.Set_Column_Value
1822 ( column_name => 'ATTRIBUTE13'
1823 , column_value => p_PRICING_ATTR_rec.attribute13
1824 );
1825 FND_FLEX_DESC_VAL.Set_Column_Value
1826 ( column_name => 'ATTRIBUTE14'
1827 , column_value => p_PRICING_ATTR_rec.attribute14
1828 );
1829 FND_FLEX_DESC_VAL.Set_Column_Value
1830 ( column_name => 'ATTRIBUTE15'
1831 , column_value => p_PRICING_ATTR_rec.attribute15
1832 );
1833 FND_FLEX_DESC_VAL.Set_Column_Value
1834 ( column_name => 'ATTRIBUTE2'
1835 , column_value => p_PRICING_ATTR_rec.attribute2
1836 );
1837 FND_FLEX_DESC_VAL.Set_Column_Value
1838 ( column_name => 'ATTRIBUTE3'
1839 , column_value => p_PRICING_ATTR_rec.attribute3
1840 );
1841 FND_FLEX_DESC_VAL.Set_Column_Value
1842 ( column_name => 'ATTRIBUTE4'
1843 , column_value => p_PRICING_ATTR_rec.attribute4
1844 );
1845 FND_FLEX_DESC_VAL.Set_Column_Value
1846 ( column_name => 'ATTRIBUTE5'
1847 , column_value => p_PRICING_ATTR_rec.attribute5
1848 );
1849 FND_FLEX_DESC_VAL.Set_Column_Value
1850 ( column_name => 'ATTRIBUTE6'
1851 , column_value => p_PRICING_ATTR_rec.attribute6
1852 );
1853 FND_FLEX_DESC_VAL.Set_Column_Value
1854 ( column_name => 'ATTRIBUTE7'
1855 , column_value => p_PRICING_ATTR_rec.attribute7
1856 );
1857 FND_FLEX_DESC_VAL.Set_Column_Value
1858 ( column_name => 'ATTRIBUTE8'
1859 , column_value => p_PRICING_ATTR_rec.attribute8
1860 );
1861 FND_FLEX_DESC_VAL.Set_Column_Value
1862 ( column_name => 'ATTRIBUTE9'
1863 , column_value => p_PRICING_ATTR_rec.attribute9
1864 );
1865 FND_FLEX_DESC_VAL.Set_Column_Value
1866 ( column_name => 'CONTEXT'
1867 , column_value => p_PRICING_ATTR_rec.context
1868 );
1869 */
1870
1871 -- Validate descriptive flexfield.
1872
1873 IF NOT QP_Validate.Desc_Flex( 'PRICING_ATTR' ) THEN
1874 x_return_status := FND_API.G_RET_STS_ERROR;
1875 END IF;
1876
1877 END IF;
1878
1879 -- Done validating attributes
1880
1881 oe_debug_pub.add('END Attributes in QPXLPRAB');
1882
1883 EXCEPTION
1884
1885 WHEN FND_API.G_EXC_ERROR THEN
1886
1887 x_return_status := FND_API.G_RET_STS_ERROR;
1888
1889 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1890
1891 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1892
1893 WHEN OTHERS THEN
1894
1895 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1896
1897 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1898 THEN
1899 OE_MSG_PUB.Add_Exc_Msg
1900 ( G_PKG_NAME
1901 , 'Attributes'
1902 );
1903 END IF;
1904
1905 END Attributes;
1906
1907 -- Procedure Entity_Delete
1908
1909 PROCEDURE Entity_Delete
1910 ( x_return_status OUT NOCOPY VARCHAR2
1911 , p_PRICING_ATTR_rec IN QP_Modifiers_PUB.Pricing_Attr_Rec_Type
1912 )
1913 IS
1914 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
1915 BEGIN
1916
1917 oe_debug_pub.add('BEGIN Entity_Delete in QPXLPRAB');
1918
1919 -- Validate entity delete.
1920
1921 NULL;
1922 -- Check whether Source System Code matches
1923 -- mkarya for bug 1728764, Prevent update of Trade Management Data in QP
1924 QP_UTIL.Check_Source_System_Code
1925 (p_list_header_id => p_PRICING_ATTR_rec.list_header_id,
1926 p_list_line_id => p_PRICING_ATTR_rec.list_line_id,
1927 x_return_status => l_return_status
1928 );
1929
1930 -- Done.
1931
1932 x_return_status := l_return_status;
1933
1934 oe_debug_pub.add('END Entity_Delete in QPXLPRAB');
1935
1936 EXCEPTION
1937
1938 WHEN FND_API.G_EXC_ERROR THEN
1939
1940 x_return_status := FND_API.G_RET_STS_ERROR;
1941
1942 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1943
1944 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1945
1946 WHEN OTHERS THEN
1947
1948 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1949
1950 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1951 THEN
1952 OE_MSG_PUB.Add_Exc_Msg
1953 ( G_PKG_NAME
1954 , 'Entity_Delete'
1955 );
1956 END IF;
1957
1958 END Entity_Delete;
1959
1960 -- start bug2091362
1961 FUNCTION MOD_DUP(p_Start_Date_Active IN DATE
1962 , p_End_Date_Active IN DATE
1963 , p_List_Line_ID IN NUMBER
1964 , p_List_Header_ID IN NUMBER
1965 , p_product_attribute_context IN VARCHAR2
1966 , p_product_attribute IN VARCHAR2
1967 , p_product_attr_value IN VARCHAR2
1968 , p_x_rows OUT NOCOPY NUMBER
1969 , p_x_effdates OUT NOCOPY BOOLEAN
1970 )
1971 RETURN BOOLEAN
1972 is
1973 l_x_list_line_id NUMBER ;
1974 l_y_list_line_id NUMBER;
1975
1976 CURSOR get_rec(l_List_Line_ID NUMBER ) is
1977 select a.list_line_id col1, b.list_line_id col2
1978 from qp_pricing_attributes a, qp_pricing_attributes b, qp_list_lines c,
1979 qp_list_lines c1
1980 where a.list_line_id = l_List_Line_ID
1981 and b.list_line_id <> l_List_Line_ID
1982 and b.product_attribute_context = a.product_attribute_context
1983 and b.product_attribute = a.product_attribute
1984 and b.product_attr_value = a.product_attr_value
1985 and b.pricing_attr_value_from Is Null
1986 and a.pricing_attr_value_from Is Null
1987 and nvl(Decode(b.pricing_attribute_context,'VOLUME',null),' ') =
1988 nvl(Decode(a.pricing_attribute_context,'VOLUME',null),' ')
1989 and (nvl( b.product_uom_code,' ') = nvl(a.product_uom_code,' ')
1990 Or (a.pricing_attribute_context = 'VOLUME' Or b.pricing_attribute_context = 'VOLUME' ))
1991 and (nvl(b.pricing_attribute,' ') = nvl(a.pricing_attribute,' ')
1992 Or (a.pricing_attribute_context = 'VOLUME' Or b.pricing_attribute_context = 'VOLUME' ))
1993 and (nvl(b.pricing_attr_value_from,0) = nvl(a.pricing_attr_value_from,0)
1994 Or (a.pricing_attribute_context = 'VOLUME' Or b.pricing_attribute_context = 'VOLUME' ))
1995 and (nvl(b.pricing_attr_value_to,0) = nvl(a.pricing_attr_value_to,0)
1996 Or (a.pricing_attribute_context = 'VOLUME' Or b.pricing_attribute_context = 'VOLUME' ))
1997 and (nvl(b.comparison_operator_code,' ') = nvl(a.comparison_operator_code,' ')
1998 Or (a.pricing_attribute_context = 'VOLUME' Or b.pricing_attribute_context = 'VOLUME' ))
1999 and a.list_line_id = c.list_line_id
2000 and b.list_line_id = c1.list_line_id
2001 and c.modifier_level_code = c1.modifier_level_code
2002 and c.automatic_flag = c1.automatic_flag
2003 and c1.list_header_id = p_List_Header_ID
2004 and c.list_header_id = p_List_Header_ID
2005 group by a.list_line_id, b.list_line_id
2006 having count(b.list_line_id ) = ( select count(*)
2007 from qp_pricing_attributes
2008 where list_line_id = l_List_Line_ID)
2009 and count(b.list_line_id) = ( select count(*)
2010 from qp_pricing_attributes
2011 where list_line_id = b.list_line_id) ;
2012
2013
2014 CURSOR get_rec_no_attr(l_List_Line_ID NUMBER ) is
2015 select c.list_line_id col1, c1.list_line_id col2
2016 from qp_list_lines c, qp_list_lines c1
2017 where c.list_line_id = l_List_Line_ID
2018 and c1.list_line_id <> l_List_Line_ID
2019 and c.list_line_type_code = c1.list_line_type_code
2020 and c.modifier_level_code = c1.modifier_level_code
2021 and c.automatic_flag = c1.automatic_flag
2022 and c1.list_header_id = p_List_Header_ID
2023 and c.list_header_id = p_List_Header_ID
2024 and not exists (select list_line_id
2025 from qp_pricing_attributes
2026 where list_line_id = c1.list_line_id)
2027 group by c.list_line_id, c1.list_line_id;
2028
2029 l_min_date date := to_date('01/01/1900', 'MM/DD/YYYY');
2030 l_max_date date := to_date('12/31/9999', 'MM/DD/YYYY');
2031 l_sdate DATE;
2032 l_edate DATE;
2033 DUPLI_QUAL BOOLEAN := FALSE;
2034 l_org_line NUMBER;
2035 l_other_line NUMBER;
2036 CT_DUPLI_QUAL NUMBER;
2037 l_Attr_present NUMBER;
2038 BEGIN
2039
2040
2041 if p_product_attribute_context is null then
2042 oe_debug_pub.add('No attribute');
2043 for rec in get_rec_no_attr(p_List_Line_ID)
2044 loop
2045 oe_debug_pub.add('No attribute in for loop');
2046 oe_debug_pub.add('col2 list_line_id' || rec.col2 );
2047 l_min_date := to_date('01/01/1900', 'MM/DD/YYYY');
2048 l_max_date := to_date('12/31/9999', 'MM/DD/YYYY');
2049
2050 CT_DUPLI_QUAL := 1;
2051 DUPLI_QUAL := FALSE;
2052
2053 Select count(*) into l_org_line from qp_qualifiers where list_line_id = rec.col1;
2054
2055 Select count(*) into l_other_line from qp_qualifiers where list_line_id = rec.col2;
2056
2057 if (l_org_line = 0 and l_other_line = 0) then
2058
2059 DUPLI_QUAL := TRUE;
2060
2061 elsif(l_org_line = l_other_line) then
2062
2063 Declare
2064 Cursor get_same_quals(l_list_id in Number) is
2065 select qualifier_id qual_id, list_header_id hdr_id from qp_qualifiers
2066 where list_line_id = l_list_id;
2067 CT_DUPLI_QUAL NUMBER;
2068
2069 begin
2070 for rec1 in get_same_quals(rec.col1)
2071 loop
2072
2073 Select count(*) into CT_DUPLI_QUAL
2074 from qp_qualifiers q1, qp_qualifiers q2
2075 where q1.qualifier_id = rec1.qual_id
2076 and q2.list_line_id = rec.col2
2077 and nvl(q1.qualifier_grouping_no, 0) = nvl(q2.qualifier_grouping_no, 0)
2078 and q1.qualifier_context = q2.qualifier_context
2079 and q1.qualifier_attribute = q2.qualifier_attribute
2080 and nvl(q1.qualifier_attr_value,' ') = nvl(q2.qualifier_attr_value,' ')
2081 and nvl(q1.qualifier_attr_value_to,' ') = nvl(q2.qualifier_attr_value_to,' ')
2082 and nvl(q1.comparison_operator_code,' ') = nvl(q2.comparison_operator_code,' ');
2083
2084 oe_debug_pub.add('The count of duplicate qualifiers is '||CT_DUPLI_QUAL);
2085
2086 if (CT_DUPLI_QUAL = 0) then -- the qualifiers are not same.
2087
2088 oe_debug_pub.add('Count equal to zero');
2089 DUPLI_QUAL := FALSE;
2090 exit;
2091 end if;
2092 DUPLI_QUAL := TRUE;
2093 end loop;
2094 end;
2095 end if;
2096 if (DUPLI_QUAL = TRUE) then
2097
2098 l_min_date := to_date('01/01/1900', 'MM/DD/YYYY');
2099 l_max_date := to_date('12/31/9999', 'MM/DD/YYYY');
2100
2101 begin
2102 SELECT start_date_active, end_date_active
2103 into l_sdate, l_edate
2104 from qp_list_lines
2105 where list_line_id = rec.col2;
2106 exception
2107 when no_data_found then null;
2108 end;
2109
2110
2111 IF ( nvl(p_Start_Date_Active, l_min_date) <= nvl(l_sdate, l_min_date))
2112 THEN
2113 l_min_date := nvl(p_Start_Date_Active, l_min_date);
2114 ELSE
2115 l_min_date := nvl(l_sdate, l_min_date);
2116 END IF;
2117
2118 IF ( nvl(p_End_Date_Active, l_max_date) >= nvl(l_edate, l_max_date))
2119 THEN
2120 l_max_date := nvl(p_End_Date_Active, l_max_date);
2121 ELSE
2122 l_max_date := nvl(l_edate, l_max_date);
2123 END IF;
2124
2125 IF ( trunc(nvl(l_sdate, l_min_date)) between
2126 trunc(nvl(p_Start_Date_Active, l_min_date))
2127 and trunc(nvl(p_End_Date_Active, l_max_date)) )
2128 OR
2129 ( trunc(nvl(l_edate, l_max_date)) between
2130 trunc(nvl(p_Start_Date_Active, l_min_date))
2131 and trunc(nvl(p_End_Date_Active, l_max_date)) )
2132
2133 OR
2134 ( trunc(nvl(l_sdate, l_min_date)) <=
2135 nvl(p_Start_Date_Active,l_min_date)
2136 AND
2137 trunc(nvl(l_edate, l_max_date)) >=
2138 nvl(p_End_Date_Active,l_max_date) )
2139
2140 THEN
2141
2142 oe_debug_pub.add('Found a Modifier line duplicate...' );
2143 p_x_effdates := FALSE;
2144 RETURN FALSE;
2145
2146
2147 END IF; --- Overlapping Dates.
2148
2149 end if; --- Qual dupli check
2150 end loop;
2151
2152 Else
2153 oe_debug_pub.add('Attribute present');
2154 for rec in get_rec(p_List_Line_ID)
2155 loop
2156
2157 l_min_date := to_date('01/01/1900', 'MM/DD/YYYY');
2158 l_max_date := to_date('12/31/9999', 'MM/DD/YYYY');
2159 CT_DUPLI_QUAL := 1;
2160 DUPLI_QUAL := FALSE;
2161
2162 Select count(*) into l_org_line from qp_qualifiers where list_line_id = rec.col1;
2163
2164 Select count(*) into l_other_line from qp_qualifiers where list_line_id = rec.col2;
2165
2166 if (l_org_line = 0 and l_other_line = 0) then
2167
2168 DUPLI_QUAL := TRUE;
2169
2170 elsif(l_org_line = l_other_line) then
2171
2172 Declare
2173 Cursor get_same_quals(l_list_id in Number) is
2174 select qualifier_id qual_id, list_header_id hdr_id from qp_qualifiers
2175 where list_line_id = l_list_id;
2176 CT_DUPLI_QUAL NUMBER := 0;
2177
2178 begin
2179 for rec1 in get_same_quals(rec.col1)
2180 loop
2181
2182 Select count(*) into CT_DUPLI_QUAL
2183 from qp_qualifiers q1, qp_qualifiers q2
2184 where q1.qualifier_id = rec1.qual_id
2185 and q2.list_line_id = rec.col2
2186 and nvl(q1.qualifier_grouping_no, 0) = nvl(q2.qualifier_grouping_no, 0)
2187 and q1.qualifier_context = q2.qualifier_context
2188 and q1.qualifier_attribute = q2.qualifier_attribute
2189 and nvl(q1.qualifier_attr_value,' ') = nvl(q2.qualifier_attr_value,' ')
2190 and nvl(q1.qualifier_attr_value_to,' ') = nvl(q2.qualifier_attr_value_to,' ')
2191 and nvl(q1.comparison_operator_code,' ') = nvl(q2.comparison_operator_code,' ');
2192
2193 oe_debug_pub.add('The count of duplicate qualifiers is '||CT_DUPLI_QUAL);
2194
2195 if (CT_DUPLI_QUAL = 0) then -- the qualifiers are not same.
2196
2197 oe_debug_pub.add('Count equal to zero');
2198 DUPLI_QUAL := FALSE;
2199 exit;
2200 end if;
2201 DUPLI_QUAL := TRUE;
2202 end loop;
2203 end;
2204
2205 end if;
2206 if (DUPLI_QUAL = TRUE) then
2207
2208 l_min_date := to_date('01/01/1900', 'MM/DD/YYYY');
2209 l_max_date := to_date('12/31/9999', 'MM/DD/YYYY');
2210
2211 begin
2212 SELECT start_date_active, end_date_active
2213 into l_sdate, l_edate
2214 from qp_list_lines
2215 where list_line_id = rec.col2;
2216 exception
2217 when no_data_found then null;
2218 end;
2219
2220
2221 IF ( nvl(p_Start_Date_Active, l_min_date) <= nvl(l_sdate, l_min_date))
2222 THEN
2223 l_min_date := nvl(p_Start_Date_Active, l_min_date);
2224 ELSE
2225 l_min_date := nvl(l_sdate, l_min_date);
2226 END IF;
2227
2228 IF ( nvl(p_End_Date_Active, l_max_date) >= nvl(l_edate, l_max_date))
2229 THEN
2230 l_max_date := nvl(p_End_Date_Active, l_max_date);
2231 ELSE
2232 l_max_date := nvl(l_edate, l_max_date);
2233 END IF;
2234
2235 IF ( trunc(nvl(l_sdate, l_min_date)) between
2236 trunc(nvl(p_Start_Date_Active, l_min_date))
2237 and trunc(nvl(p_End_Date_Active, l_max_date)) )
2238 OR
2239 ( trunc(nvl(l_edate, l_max_date)) between
2240 trunc(nvl(p_Start_Date_Active, l_min_date))
2241 and trunc(nvl(p_End_Date_Active, l_max_date)) )
2242
2243 OR
2244 ( trunc(nvl(l_sdate, l_min_date)) <=
2245 nvl(p_Start_Date_Active,l_min_date)
2246 AND
2247 trunc(nvl(l_edate, l_max_date)) >=
2248 nvl(p_End_Date_Active,l_max_date) )
2249
2250 THEN
2251
2252 oe_debug_pub.add('Found a Modifier line p attr duplicate...' );
2253 p_x_effdates := FALSE;
2254 RETURN FALSE;
2255
2256
2257 END IF; --- Overlapping Dates.
2258
2259 end if; --- Qual dupli check
2260 end loop;
2261
2262 end if;
2263
2264
2265 p_x_rows := sql%rowcount;
2266 RETURN TRUE;
2267
2268 EXCEPTION
2269 WHEN NO_DATA_FOUND THEN
2270 p_x_rows := sql%rowcount;
2271 p_x_effdates := TRUE;
2272 RETURN TRUE;
2273
2274 WHEN OTHERS THEN
2275 p_x_rows := sql%rowcount;
2276 p_x_effdates := FALSE;
2277 RETURN FALSE;
2278
2279 END Mod_Dup;
2280
2281 -- end bug2091362
2282
2283 END QP_Validate_Pricing_Attr;
[Home] [Help]