1 PACKAGE BODY QP_Validate_pll_pricing_attr AS
2 /* $Header: QPXLPLAB.pls 120.9.12010000.2 2008/10/15 14:08:29 jputta ship $ */
3
4 -- Global constant holding the package name
5
6 G_PKG_NAME CONSTANT VARCHAR2(30) := 'Qp_Validate_pll_pricing_attr';
7
8 -- Procedure Entity
9
10 PROCEDURE Entity
11 ( x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2
12 , p_PRICING_ATTR_rec IN QP_Price_List_PUB.Pricing_Attr_Rec_Type
13 , p_old_PRICING_ATTR_rec IN QP_Price_List_PUB.Pricing_Attr_Rec_Type :=
14 QP_Price_List_PUB.G_MISS_PRICING_ATTR_REC
15 )
16 IS
17 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
18 l_revision VARCHAR2(30);
19 l_start_date_active DATE;
20 l_end_date_active DATE;
21 l_list_header_id NUMBER;
22 l_comparison_operator_code VARCHAR2(30);
23 l_error_code NUMBER;
24 l_precedence NUMBER;
25 l_datatype FND_FLEX_VALUE_SETS.Format_type%TYPE;
26 l_value_error VARCHAR2(1);
27 l_context_error VARCHAR2(1);
28 l_attribute_error VARCHAR2(1);
29 l_x_rows NUMBER := 0;
30 l_count NUMBER := 0;
31 l_primary_uom_flag VARCHAR2(1);
32 l_from_rltd_modifier_id NUMBER;
33
34 l_context_type VARCHAR2(30);
35 l_sourcing_enabled VARCHAR2(1);
36 l_sourcing_status VARCHAR2(1);
37 l_sourcing_method VARCHAR2(30);
38
39 -- Modified by rassharm for Bug No 5457704
40 l_edate DATE;
41 l_sdate DATE;
42 l_min_date DATE;
43 l_max_date DATE;
44
45 l_pte_code VARCHAR2(30);
46 l_ss_code VARCHAR2(30);
47 l_fna_name VARCHAR2(4000);
48 l_fna_desc VARCHAR2(489);
49 l_fna_valid BOOLEAN;
50
51 l_dummy varchar2(30);
52 l_continuous_price_break_flag VARCHAR2(1); --Continuous Price Breaks
53 BEGIN
54
55 -- Check required attributes.
56
57 IF p_PRICING_ATTR_rec.pricing_attribute_id IS NULL
58 THEN
59
60 l_return_status := FND_API.G_RET_STS_ERROR;
61
62 IF oe_msg_pub.Check_Msg_Level(oe_msg_pub.G_MSG_LVL_ERROR)
63 THEN
64
65 FND_MESSAGE.SET_NAME('QP','QP_ATTRIBUTE_REQUIRED');
66 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','Pricing Attribute Id');
67 oe_msg_pub.Add;
68
69 END IF;
70
71 END IF;
72
73 --5286339
74
75 IF p_PRICING_ATTR_rec.operation = QP_GLOBALS.G_OPR_UPDATE
76 THEN
77
78 IF nvl(p_old_PRICING_ATTR_rec.PRODUCT_ATTRIBUTE_CONTEXT,'X') <>
79 nvl(p_PRICING_ATTR_rec.PRODUCT_ATTRIBUTE_CONTEXT,'X')
80 THEN
81 l_return_status := FND_API.G_RET_STS_ERROR;
82
83 FND_MESSAGE.SET_NAME('QP','QP_CANNOT_UPDATE_ATTRIBUTE');
84 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','product_attribute_context');
85 OE_MSG_PUB.Add;
86
87 END IF;
88
89 IF nvl(p_old_PRICING_ATTR_rec.PRODUCT_ATTRIBUTE,'X') <>
90 nvl(p_PRICING_ATTR_rec.PRODUCT_ATTRIBUTE,'X')
91 THEN
92 l_return_status := FND_API.G_RET_STS_ERROR;
93
94 FND_MESSAGE.SET_NAME('QP','QP_CANNOT_UPDATE_ATTRIBUTE');
95 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','product_attribute');
96 OE_MSG_PUB.Add;
97
98 END IF;
99
100 IF nvl(p_old_PRICING_ATTR_rec.PRODUCT_ATTR_VALUE,9999999) <>
101 nvl(p_PRICING_ATTR_rec.PRODUCT_ATTR_VALUE,9999999)
102 THEN
103 l_return_status := FND_API.G_RET_STS_ERROR;
104
105 FND_MESSAGE.SET_NAME('QP','QP_CANNOT_UPDATE_ATTRIBUTE');
106 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','product_attr_value');
107 OE_MSG_PUB.Add;
108
109 END IF;
110
111 END IF;
112
113 --5286339
114
115 IF p_PRICING_ATTR_rec.list_line_id IS NULL
116 THEN
117 l_return_status := FND_API.G_RET_STS_ERROR;
118
119 IF oe_msg_pub.Check_Msg_Level(oe_msg_pub.G_MSG_LVL_ERROR)
120 THEN
121 FND_MESSAGE.SET_NAME('QP','QP_ATTRIBUTE_REQUIRED');
122 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','List Line Id');
123 oe_msg_pub.Add;
124 END IF;
125 END IF;
126
127 /* IF p_PRICING_ATTR_rec.list_header_id IS NULL
128 THEN
129 l_return_status := FND_API.G_RET_STS_ERROR;
130
131 IF oe_msg_pub.Check_Msg_Level(oe_msg_pub.G_MSG_LVL_ERROR)
132 THEN
133 FND_MESSAGE.SET_NAME('QP','QP_ATTRIBUTE_REQUIRED');
134 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','List Header Id');
135 oe_msg_pub.Add;
136 END IF;
137 END IF;
138 */
139 IF p_PRICING_ATTR_rec.pricing_phase_id IS NULL
140 THEN
141 l_return_status := FND_API.G_RET_STS_ERROR;
142
143 IF oe_msg_pub.Check_Msg_Level(oe_msg_pub.G_MSG_LVL_ERROR)
144 THEN
145 FND_MESSAGE.SET_NAME('QP','QP_ATTRIBUTE_REQUIRED');
146 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','Pricing Phase Id');
147 oe_msg_pub.Add;
148 END IF;
149 END IF;
150
151 IF p_PRICING_ATTR_rec.excluder_flag IS NULL
152 THEN
153 l_return_status := FND_API.G_RET_STS_ERROR;
154
155 IF oe_msg_pub.Check_Msg_Level(oe_msg_pub.G_MSG_LVL_ERROR)
156 THEN
157 FND_MESSAGE.SET_NAME('QP','QP_ATTRIBUTE_REQUIRED');
158 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',QP_PRC_UTIL.Get_Attribute_Name('EXCLUDER_FLAG')); -- Fix For Bug-1974413
159 oe_msg_pub.Add;
160 END IF;
161 ELSE
162
163 IF (p_PRICING_ATTR_rec.excluder_flag not in ( 'Y', 'y', 'N', 'n' ) )
164 THEN
165 l_return_status := FND_API.G_RET_STS_ERROR;
166 IF oe_msg_pub.Check_Msg_Level(oe_msg_pub.G_MSG_LVL_ERROR)
167 THEN
168 FND_MESSAGE.SET_NAME('QP','QP_INVALID_ATTRIBUTE');
169 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','Excluder Flag');
170 oe_msg_pub.Add;
171 END IF;
172 END IF;
173 END IF;
174
175 IF p_PRICING_ATTR_rec.product_attribute_context IS NULL
176 THEN
177 l_return_status := FND_API.G_RET_STS_ERROR;
178
179 IF oe_msg_pub.Check_Msg_Level(oe_msg_pub.G_MSG_LVL_ERROR)
180 THEN
181 FND_MESSAGE.SET_NAME('QP','QP_ATTRIBUTE_REQUIRED');
182 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',QP_PRC_UTIL.Get_Attribute_Name('PRODUCT_ATTRIBUTE_CONTEXT')); --Fix For Bug-1974413
183 oe_msg_pub.Add;
184 END IF;
185 END IF;
186
187 IF p_PRICING_ATTR_rec.product_attribute IS NULL
188 THEN
189 l_return_status := FND_API.G_RET_STS_ERROR;
190
191 IF oe_msg_pub.Check_Msg_Level(oe_msg_pub.G_MSG_LVL_ERROR)
192 THEN
193 FND_MESSAGE.SET_NAME('QP','QP_ATTRIBUTE_REQUIRED');
194 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',QP_PRC_UTIL.Get_Attribute_Name('PRODUCT_ATTRIBUTE')); -- Fix For Bug-1974413
195 oe_msg_pub.Add;
196 END IF;
197 END IF;
198
199 IF p_PRICING_ATTR_rec.product_attr_value IS NULL
200 THEN
201 l_return_status := FND_API.G_RET_STS_ERROR;
202
203 IF oe_msg_pub.Check_Msg_Level(oe_msg_pub.G_MSG_LVL_ERROR)
204 THEN
205 FND_MESSAGE.SET_NAME('QP','QP_ATTRIBUTE_REQUIRED');
206 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',QP_PRC_UTIL.Get_Attribute_Name('PRODUCT_ATTR_VALUE')); -- Fix For Bug-1974413
207 oe_msg_pub.Add;
208 END IF;
209 END IF;
210
211 IF p_PRICING_ATTR_rec.product_attribute_datatype IS NULL
212 THEN
213 l_return_status := FND_API.G_RET_STS_ERROR;
214
215 IF oe_msg_pub.Check_Msg_Level(oe_msg_pub.G_MSG_LVL_ERROR)
216 THEN
217 FND_MESSAGE.SET_NAME('QP','QP_ATTRIBUTE_REQUIRED');
218 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','Product Attribute Datatype');
219 oe_msg_pub.Add;
220 END IF;
221 END IF;
222
223 IF p_PRICING_ATTR_rec.product_uom_code IS NULL
224 THEN
225 l_return_status := FND_API.G_RET_STS_ERROR;
226
227 IF oe_msg_pub.Check_Msg_Level(oe_msg_pub.G_MSG_LVL_ERROR)
228 THEN
229 FND_MESSAGE.SET_NAME('QP','QP_ATTRIBUTE_REQUIRED');
230 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',QP_PRC_UTIL.Get_Attribute_Name('PRODUCT_UOM_CODE')); -- Fix For Bug-1974413
231 oe_msg_pub.Add;
232 END IF;
233 END IF;
234
235 IF p_PRICING_ATTR_rec.attribute_grouping_no IS NULL
236 THEN
237 l_return_status := FND_API.G_RET_STS_ERROR;
238
239 IF oe_msg_pub.Check_Msg_Level(oe_msg_pub.G_MSG_LVL_ERROR)
240 THEN
241 FND_MESSAGE.SET_NAME('QP','QP_ATTRIBUTE_REQUIRED');
242 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',QP_PRC_UTIL.Get_Attribute_Name('ATTRIBUTE_GROUPING_NO')); -- Fix For Bug-1974413
243 oe_msg_pub.Add;
244 END IF;
245 END IF;
246
247 --
248 -- Check rest of required attributes here.
249 --
250
251
252 -- Return Error if a required attribute is missing.
253
254 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
255
256 RAISE FND_API.G_EXC_ERROR;
257
258 END IF;
259
260 --
261 -- Check conditionally required attributes here.
262 --
263 OE_Debug_Pub.add ( 'Geresh :: Price Line ID Check '|| p_PRICING_ATTR_rec.list_line_id ) ;
264
265 -- Functional Area Validation for Hierarchical Categories (sfiresto)
266 IF p_PRICING_ATTR_rec.product_attribute_context = 'ITEM' AND
267 p_PRICING_ATTR_rec.product_attribute = 'PRICING_ATTRIBUTE2' THEN
268 BEGIN
269
270 SELECT pte_code, source_system_code
271 INTO l_pte_code, l_ss_code
272 FROM qp_list_headers_b
273 WHERE list_header_id = p_PRICING_ATTR_rec.list_header_id;
274
275 QP_UTIL.Get_Item_Cat_Info(
276 p_PRICING_ATTR_rec.product_attr_value,
277 l_pte_code,
278 l_ss_code,
279 l_fna_name,
280 l_fna_desc,
281 l_fna_valid);
282
283 IF NOT l_fna_valid THEN
284
285 l_return_status := FND_API.G_RET_STS_ERROR;
286
287 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR)
288 THEN
289 FND_MESSAGE.set_name('QP', 'QP_INVALID_CAT_FUNC_PTE');
290 FND_MESSAGE.set_token('CATID', p_PRICING_ATTR_REC.product_attr_value);
291 FND_MESSAGE.set_token('PTE', l_pte_code);
292 FND_MESSAGE.set_token('SS', l_ss_code);
293 OE_MSG_PUB.Add;
294 END IF;
295
296 RAISE FND_API.G_EXC_ERROR;
297
298 END IF;
299
300 END;
301 END IF;
302
303 /* Duplicate Check */
304
305 BEGIN
306 SELECT primary_uom_flag
307 INTO l_primary_uom_flag
308 FROM qp_list_lines
309 WHERE list_line_id = p_PRICING_ATTR_rec.list_line_id;
310
311 EXCEPTION
312 WHEN OTHERS THEN
313 l_return_status := FND_API.G_RET_STS_ERROR;
314 END;
315
316 /* Modified by rassharm for Bug No 5457704 to include effective dates check for primary UOM */
317
318 select start_date_active, end_date_active , revision, list_header_id
319 into l_start_date_active, l_end_date_active, l_revision, l_list_header_id
320 from qp_list_lines
321 where list_line_id = p_PRICING_ATTR_rec.list_line_id;
322
323
324 IF l_primary_uom_flag = 'Y' AND p_PRICING_ATTR_rec.product_attribute <> 'PRICING_ATTRIBUTE3' THEN
325 l_count:=0;
326 for c1 in
327 (
328 select b.list_line_id col2
329 from qp_pricing_attributes b, qp_list_lines c
330 where b.list_line_id <> p_PRICING_ATTR_rec.list_line_id
331 and b.list_line_id=c.list_line_id
332 and c.primary_uom_flag='Y'
333 and b.product_attribute_context = p_PRICING_ATTR_rec.product_attribute_context
334 and b.product_attribute = p_PRICING_ATTR_rec.product_attribute
335 and b.product_attr_value = p_PRICING_ATTR_rec.product_attr_value
336 and b.list_header_id = p_PRICING_ATTR_rec.list_header_id
337 AND b.product_uom_code <> p_PRICING_ATTR_rec.product_uom_code -- for bug 7135111
338 )
339 Loop
340 /*----------------------------------------------*/
341 l_min_date := to_date('01/01/1900', 'MM/DD/YYYY');
342 l_max_date := to_date('12/31/9999', 'MM/DD/YYYY');
343 /*--------------------------------------------*/
344 begin
345 SELECT start_date_active, end_date_active
346 into l_sdate, l_edate
347 from qp_list_lines
348 where list_line_id = c1.col2;
349
350 exception
351 when no_data_found then
352 null;
353 end;
354
355
356 IF ( nvl(l_Start_Date_Active, l_min_date) <= nvl(l_sdate, l_min_date))
357 THEN
358 l_min_date := nvl(l_Start_Date_Active, l_min_date);
359 ELSE
360 l_min_date := nvl(l_sdate, l_min_date);
361 END IF;
362
363 IF ( nvl(l_End_Date_Active, l_max_date) >= nvl(l_edate, l_max_date))
364 THEN
365 l_max_date := nvl(l_End_Date_Active, l_max_date);
366 ELSE
367 l_max_date := nvl(l_edate, l_max_date);
368 END IF;
369
370
371 if ( trunc(nvl(l_sdate, l_min_date)) between
372 trunc(nvl(l_Start_Date_Active, l_min_date))
373 and trunc(nvl(l_End_Date_Active, l_max_date)) )
374 OR
375 ( trunc(nvl(l_edate, l_max_date)) between
376 trunc(nvl(l_Start_Date_Active, l_min_date))
377 and trunc(nvl(l_End_Date_Active, l_max_date)) )
378
379 OR
380 ( trunc(nvl(l_sdate, l_min_date)) <=
381 nvl(l_Start_Date_Active,l_min_date)
382 AND
383 trunc(nvl(l_edate, l_max_date)) >=
384 nvl(l_End_Date_Active,l_max_date) )
385
386 THEN
387 l_count:=1;
388 oe_debug_pub.add('Dates Overlapping' );
389 exit;
390 end if;
391
392 end loop;
393
394 IF l_count > 0 THEN
395 l_return_status := FND_API.G_RET_STS_ERROR;
396 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR) THEN
397 FND_MESSAGE.SET_NAME('QP','QP_UNIQUE_PRIMARY_UOM');
398 OE_MSG_PUB.Add;
399 RAISE FND_API.G_EXC_ERROR;
400 END IF;
401 END IF;
402 END IF;
403
404 /* End changes rassharm */
405
406
407 /* (sfiresto) Moving Item validation and UOM validtion code from Attributes procedure to Entity
408 * procedure for bug 4753707.
409 */
410
411 --fix for bug 5390181
412 l_return_status := QP_UTIL.Validate_Item(p_PRICING_ATTR_rec.product_attribute_context,
413 p_PRICING_ATTR_rec.product_attribute,
414 p_PRICING_ATTR_rec.product_attr_value);
415
416 /*
417 if (p_PRICING_ATTR_rec.product_attribute_context = 'ITEM') THEN
418 if (p_PRICING_ATTR_rec.product_attribute = 'PRICING_ATTRIBUTE1') THEN
419 begin
420
421 SELECT 'VALID' INTO l_dummy
422 FROM mtl_system_items_b
423 where inventory_item_id = p_PRICING_ATTR_rec.product_attr_value
424 AND customer_order_flag = 'Y'
425 and organization_id = fnd_profile.value('QP_ORGANIZATION_ID');
426 exception
427 WHEN NO_DATA_FOUND THEN
428
429 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR)
430 THEN
431 FND_MESSAGE.SET_NAME('QP','QP_ITEM_NOT_VALID');
432 FND_MESSAGE.SET_TOKEN('ITEM_ID', p_PRICING_ATTR_rec.product_attr_value);
433 OE_MSG_PUB.Add;
434 END IF;
435 RAISE FND_API.G_EXC_ERROR;
436
437 when others then
438 null;
439 end;
440 end if;
441 end if;
442 -- inserted for validation with the mtl_system_items_b
443 */
444
445
446 -- Begin fix for bug#4039819
447 if (p_PRICING_ATTR_rec.product_attribute_context = 'ITEM') THEN
448 if (p_PRICING_ATTR_rec.product_attribute = 'PRICING_ATTRIBUTE1') THEN
449 begin
450 select 'VALID' INTO l_dummy
451 from mtl_item_uoms_view
452 where uom_code = p_PRICING_ATTR_rec.product_uom_code
453 and organization_id = fnd_profile.value('QP_ORGANIZATION_ID')
454 and inventory_item_id = p_PRICING_ATTR_rec.product_attr_value
455 and rownum = 1;
456 exception
457 WHEN NO_DATA_FOUND THEN
458 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR)
459 THEN
460 FND_MESSAGE.SET_NAME('QP','QP_INVALID_PROD_UOM');
461 OE_MSG_PUB.Add;
462 END IF;
463 RAISE FND_API.G_EXC_ERROR;
464
465 WHEN OTHERS THEN
466 null;
467 end;
468 -- Bug 6891094 :UOM validity check to be done only for new list lines and lines in which the UOM is updated.
469 elsif (p_PRICING_ATTR_rec.product_attribute = 'PRICING_ATTRIBUTE2') THEN
470 IF p_PRICING_ATTR_rec.product_uom_code IS NOT NULL AND
471 ( p_PRICING_ATTR_rec.product_uom_code <> p_old_PRICING_ATTR_rec.product_uom_code
472 OR
473 p_old_PRICING_ATTR_rec.product_uom_code IS NULL ) THEN
474 IF NOT QP_VALIDATE.Product_Uom(p_pricing_attr_rec.product_uom_code,
475 to_number(p_PRICING_ATTR_rec.product_attr_value),
476 p_PRICING_ATTR_rec.list_header_id) THEN
477 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR) THEN
478 FND_MESSAGE.SET_NAME('QP','QP_INVALID_PROD_UOM');
479 OE_MSG_PUB.Add;
480 END IF;
481 RAISE FND_API.G_EXC_ERROR;
482 END IF;
483 END IF;
484 -- End changes for bug 6891094
485 /*
486 * Commented out for bug 4753707
487 *
488 begin
489 SELECT 'VALID' INTO l_dummy
490 FROM MTL_UNITS_OF_MEASURE_VL MTLUOM2
491 WHERE
492 EXISTS
493 (
494 SELECT /*+no_unnest*--/ 1
495 FROM MTL_SYSTEM_ITEMS_B MTLITM1,
496 MTL_UOM_CONVERSIONS MTLUCV
497 WHERE MTLUOM2.UOM_CODE = MTLUCV.UOM_CODE
498 AND MTLUOM2.UOM_CODE = p_PRICING_ATTR_rec.product_uom_code
499 AND MTLITM1.organization_id = fnd_profile.value('QP_ORGANIZATION_ID')
500 AND MTLITM1.inventory_item_id in
501 (
502 SELECT inventory_item_id FROM mtl_item_categories
503 WHERE category_id = to_number(p_PRICING_ATTR_rec.product_attr_value)
504 AND organization_id = fnd_profile.value('QP_ORGANIZATION_ID')
505 )
506 AND NVL( MTLUCV.DISABLE_DATE, TRUNC(SYSDATE)+1 ) > TRUNC(SYSDATE)
507 AND
508 (
509 (
510 MTLITM1.ALLOWED_UNITS_LOOKUP_CODE IN (1, 3)
511 AND MTLUCV.INVENTORY_ITEM_ID = MTLITM1.INVENTORY_ITEM_ID
512 OR
513 (
514 MTLUCV.INVENTORY_ITEM_ID = 0
515 AND MTLUOM2.BASE_UOM_FLAG = 'Y'
516 AND MTLUOM2.UOM_CLASS = MTLUCV.UOM_CLASS
517 AND MTLUCV.UOM_CLASS IN
518 (
519 SELECT MTLPRI1.UOM_CLASS
520 FROM MTL_UNITS_OF_MEASURE MTLPRI1
521 WHERE MTLPRI1.UOM_CODE = MTLITM1.PRIMARY_UOM_CODE
522 )
523 )
524 OR
525 (
526 MTLUCV.INVENTORY_ITEM_ID = 0
527 AND MTLUCV.UOM_CODE IN
528 (
529 SELECT MTLUCC1.TO_UOM_CODE
530 FROM MTL_UOM_CLASS_CONVERSIONS MTLUCC1
531 WHERE MTLUCC1.INVENTORY_ITEM_ID = MTLITM1.INVENTORY_ITEM_ID
532 AND NVL(MTLUCC1.DISABLE_DATE, TRUNC(SYSDATE)+1) > TRUNC(SYSDATE)
533 )
534 )
535 )
536 OR
537 (
538 MTLITM1.ALLOWED_UNITS_LOOKUP_CODE IN (2, 3)
539 AND MTLUCV.INVENTORY_ITEM_ID = 0
540 AND
541 (
542 MTLUCV.UOM_CLASS IN
543 (
544 SELECT MTLUCC.TO_UOM_CLASS
545 FROM MTL_UOM_CLASS_CONVERSIONS MTLUCC
546 WHERE MTLUCC.INVENTORY_ITEM_ID = MTLITM1.INVENTORY_ITEM_ID
547 AND NVL(MTLUCC.DISABLE_DATE, TRUNC(SYSDATE)+1) > TRUNC(SYSDATE)
548 )
549 OR MTLUCV.UOM_CLASS =
550 (
551 SELECT MTLPRI.UOM_CLASS
552 FROM MTL_UNITS_OF_MEASURE MTLPRI
553 WHERE MTLPRI.UOM_CODE = MTLITM1.PRIMARY_UOM_CODE
554 )
555 )
556 )
557 )
558 )
559 and rownum = 1;
560 exception
561 WHEN NO_DATA_FOUND THEN
562 x_return_status := FND_API.G_RET_STS_ERROR;
563 FND_MESSAGE.SET_NAME('QP','QP_INVALID_PROD_UOM');
564 OE_MSG_PUB.Add;
565 WHEN OTHERS THEN
566 null;
567 end;
568 *
569 */
570 else
571 begin
572 select 'VALID' INTO l_dummy
573 from MTL_UNITS_OF_MEASURE_VL
574 where uom_code = p_PRICING_ATTR_rec.product_uom_code
575 and rownum = 1;
576 exception
577 WHEN NO_DATA_FOUND THEN
578 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR)
579 THEN
580 FND_MESSAGE.SET_NAME('QP','QP_INVALID_PROD_UOM');
581 OE_MSG_PUB.Add;
582 END IF;
583 RAISE FND_API.G_EXC_ERROR;
584
585 WHEN OTHERS THEN
586 null;
587 end;
588 end if;
589 end if;
590 -- End fix for bug#4039819
591 /* (sfiresto) end movement of code from Attributes procedure to Entity procedure
592
593
594 /* modified IF condition by dhgupta for bug 1828734 */
595 /*
596 IF l_primary_uom_flag = 'Y' AND p_PRICING_ATTR_rec.product_attribute <> 'PRICING_ATTRIBUTE3'THEN
597
598 BEGIN
599 SELECT count(*)
600 INTO l_count
601 FROM qp_list_lines l, qp_pricing_attributes a
602 WHERE l.list_line_id = a.list_line_id
603 AND a.list_header_id = p_PRICING_ATTR_rec.list_header_id
604 AND a.product_attribute_context =
605 p_PRICING_ATTR_rec.product_attribute_context
606 AND a.product_attribute = p_PRICING_ATTR_rec.product_attribute
607 AND a.product_attr_value = p_PRICING_ATTR_rec.product_attr_value
608 AND a.product_uom_code <> p_PRICING_ATTR_rec.product_uom_code
609 AND l.primary_uom_flag = 'Y';
610
611 EXCEPTION
612 WHEN NO_DATA_FOUND THEN
613 l_count := 0;
614 END;
615
616 IF l_count > 0 THEN
617 l_return_status := FND_API.G_RET_STS_ERROR;
618 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR) THEN
619 FND_MESSAGE.SET_NAME('QP','QP_UNIQUE_PRIMARY_UOM');
620 OE_MSG_PUB.Add;
621 RAISE FND_API.G_EXC_ERROR;
622 END IF;
623 END IF;
624
625 END IF;
626
627 select start_date_active, end_date_active , revision, list_header_id
628 into l_start_date_active, l_end_date_active, l_revision, l_list_header_id
629 from qp_list_lines
630 where list_line_id = p_PRICING_ATTR_rec.list_line_id;
631 */
632 OE_Debug_Pub.add ( 'Geresh :: Value Set 1' || l_start_date_active || l_end_date_active );
633
634 OE_Debug_Pub.add ( 'Geresh :: Value Set 2' || l_revision );
635 /*
636 if NOT( QP_CHECK_DUP_PRA.Check_Dup_Pra (
637 l_start_date_active,
638 l_end_date_active,
639 l_revision,
640 p_PRICING_ATTR_rec.list_line_id ,
641 l_list_header_id ,
642 l_x_rows )
643 ) then
644
645 OE_Debug_Pub.add ( 'Geresh :: Result ' || l_x_rows );
646 if l_x_rows > 0 THEN
647
648 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR)
649 THEN
650 l_return_status := FND_API.G_RET_STS_ERROR;
651 FND_MESSAGE.SET_NAME('QP','QP_DUP_PRICING_ATTR');
652 OE_MSG_PUB.Add;
653 end if;
654 end if;
655
656
657 end if;
658 */
659 oe_debug_pub.add('G_CHECK_DUP_PRICELIST_LINES is '|| QP_GLOBALS.G_CHECK_DUP_PRICELIST_LINES); -- 5018856, 5024919
660 IF (QP_GLOBALS.G_CHECK_DUP_PRICELIST_LINES <> 'N' or QP_GLOBALS.G_CHECK_DUP_PRICELIST_LINES IS NULL)
661 THEN --5018856 , 5024919 only log request if not N or null
662 oe_debug_pub.add('about to log a request to check duplicate list lines ');
663
664 QP_DELAYED_REQUESTS_PVT.Log_Request
665 ( p_entity_code => QP_GLOBALS.G_ENTITY_ALL
666 , p_entity_id => p_PRICING_ATTR_rec.list_line_id
667 , p_requesting_entity_code => QP_GLOBALS.G_ENTITY_PRICING_ATTR
668 , p_requesting_entity_id => p_PRICING_ATTR_rec.pricing_attribute_id
669 , p_request_type => QP_GLOBALS.G_DUPLICATE_LIST_LINES
670 , p_param1 => l_list_header_id
671 , p_param2 => fnd_date.date_to_canonical(l_start_date_active) --2739511
672 , p_param3 => fnd_date.date_to_canonical(l_end_date_active) --2739511
673 , p_param4 => l_revision
674 , x_return_status => l_return_status
675 );
676
677 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
678
679 oe_debug_pub.add('failed in logging a delayed request ');
680
681 RAISE FND_API.G_EXC_ERROR;
682
683 END IF;
684
685 oe_debug_pub.add('after logging delayed request ');
686 END IF;-- end IF QP_GLOBALS.G_CHECK_DUP_PRICELIST_LINES <> 'N' or null-- 5108856
687
688 IF (p_Pricing_Attr_rec.pricing_attribute_context IS NOT NULL
689 OR p_Pricing_Attr_rec.pricing_attribute IS NOT NULL
690 OR p_Pricing_Attr_rec.pricing_attr_value_from IS NOT NULL
691 OR p_Pricing_Attr_rec.pricing_attr_value_to IS NOT NULL)
692 THEN
693 IF (p_Pricing_Attr_rec.pricing_attribute_context IS NULL
694 OR p_Pricing_Attr_rec.pricing_attribute IS NULL
695 OR p_Pricing_Attr_rec.comparison_operator_code IS NULL)
696 THEN
697
698 l_return_status := FND_API.G_RET_STS_ERROR;
699 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR)
700 THEN
701 FND_MESSAGE.SET_NAME('QP','QP_ATTRIBUTE_REQUIRED' );
702 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',QP_PRC_UTIL.Get_Attribute_Name('PRICING_ATTRIBUTE_CONTEXT')||'/'||
703 QP_PRC_UTIL.Get_Attribute_Name('PRICING_ATTRIBUTE')||'/'||
704 QP_PRC_UTIL.Get_Attribute_Name('COMPARISON_OPERATOR_CODE')); -- Fix For Bug-1974413
705 OE_MSG_PUB.Add;
706 END IF;
707 RAISE FND_API.G_EXC_ERROR;
708 ELSE
709
710 IF ( p_Pricing_Attr_rec.comparison_operator_code = 'BETWEEN'
711 AND p_Pricing_Attr_rec.pricing_attribute_datatype is NULL ) THEN
712
713 l_return_status := FND_API.G_RET_STS_ERROR;
714 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR)
715 THEN
716 FND_MESSAGE.SET_NAME('QP','QP_ATTRIBUTE_REQUIRED' );
717 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','Enter Required Values');
718 OE_MSG_PUB.Add;
719 END IF;
720 RAISE FND_API.G_EXC_ERROR;
721 END IF;
722
723 QP_UTIL.validate_qp_flexfield(flexfield_name =>'QP_ATTR_DEFNS_PRICING'
724 ,context =>p_Pricing_Attr_rec.pricing_attribute_context
725 ,attribute =>p_Pricing_Attr_rec.pricing_attribute
726 ,value =>p_Pricing_Attr_rec.pricing_attr_value_from
727 ,application_short_name => 'QP'
728 ,context_flag =>l_context_error
729 ,attribute_flag =>l_attribute_error
730 ,value_flag =>l_value_error
731 ,datatype =>l_datatype
732 ,precedence =>l_precedence
733 ,error_code =>l_error_code
734 );
735
736 If (l_context_error = 'N' AND l_error_code = 7) -- invalid context
737 Then
738 l_return_status := FND_API.G_RET_STS_ERROR;
739
740 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR)
741 THEN
742
743 FND_MESSAGE.SET_NAME('QP','QP_ATTRIBUTE_REQUIRED' );
744 --FND_MESSAGE.SET_NAME('QP','QP_INVALID_ATTRIBUTE');
745 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',QP_PRC_UTIL.Get_Attribute_Name('PRICING_ATTRIBUTE_CONTEXT')); --Fix For Bug-1974413
746
747 OE_MSG_PUB.Add;
748 END IF;
749
750 RAISE FND_API.G_EXC_ERROR;
751
752 End If;
753
754
755 --dbms_output.put_line('for context '||l_return_status);
756
757 --dbms_output.put_line('for context ,attribute,value,datatype,precedence');
758
759
760
761 If l_attribute_error = 'N' AND l_error_code = 8 -- invalid Attribute
762 Then
763 l_return_status := FND_API.G_RET_STS_ERROR;
764
765 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR)
766 THEN
767
768 FND_MESSAGE.SET_NAME('QP','QP_INVALID_ATTRIBUTE');
769 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',' Attribute');
770 OE_MSG_PUB.Add;
771 END IF;
772
773 RAISE FND_API.G_EXC_ERROR;
774
775 End If;
776
777
778 --dbms_output.put_line('for attributr '||l_return_status);
779 oe_debug_pub.add('for context '||l_return_status);
780
781
782 --- validate qualifier_attr_value only if comparison operator is
783 -- '='
784
785 IF p_Pricing_Attr_rec.comparison_operator_code = '=' Then
786
787 If l_value_error = 'N' AND l_error_code = 9 -- invalid value
788 Then
789 l_return_status := FND_API.G_RET_STS_ERROR;
790
791 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR)
792 THEN
793
794 FND_MESSAGE.SET_NAME('QP','QP_INVALID_ATTRIBUTE');
795 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',' Value From ');
796 --OE_MSG_PUB.Add;
797 OE_MSG_PUB.Add;
798 END IF;
799 RAISE FND_API.G_EXC_ERROR;
800 End If;
801 END IF;
802
803
804 --dbms_output.put_line('for value,'||l_return_status);
805 oe_debug_pub.add('for value,'||l_return_status);
806
807 --dbms_output.put_line('org precede '||p_QUALIFIERS_rec.qualifier_precedence);
808 --dbms_output.put_line('n precede '||l_precedence);
809
810 --dbms_output.put_line('for precedence'||l_return_status);
811
812 If p_Pricing_Attr_rec.pricing_attribute_datatype <> l_datatype --- invalid datatype
813 Then
814 l_return_status := FND_API.G_RET_STS_ERROR;
815
816 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR)
817 THEN
818 --FND_MESSAGE.SET_NAME('QP','QP_ATTRIBUTE_REQUIRED' );
819 FND_MESSAGE.SET_NAME('QP','QP_INVALID_ATTRIBUTE');
820 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','Pricing Attribute Datatype ');
821 OE_MSG_PUB.Add;
822 END IF;
823 RAISE FND_API.G_EXC_ERROR;
824
825 End If;
826
827 IF p_Pricing_Attr_rec.pricing_attribute_context = 'VOLUME' AND
828 p_Pricing_Attr_rec.pricing_attribute = 'PRICING_ATTRIBUTE12'
829 --When Pricing Context is 'Volume' and Attribute is 'Item Amount'
830 THEN
831 l_return_status := FND_API.G_RET_STS_ERROR;
832
833 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR)
834 THEN
835 FND_MESSAGE.SET_NAME('QP','QP_INVALID_ATTRIBUTE');
836 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','Pricing Attribute');
837 OE_MSG_PUB.Add;
838 END IF;
839
840 RAISE FND_API.G_EXC_ERROR;
841
842 END IF;
843
844
845 --dbms_output.put_line('for datatype,'||l_return_status);
846 oe_debug_pub.add('qualifier datatype,'||l_return_status);
847
848
849
850 --validation for canonical form
851
852 l_error_code:=QP_UTIL.validate_num_date(p_Pricing_Attr_rec.pricing_attribute_datatype, p_Pricing_Attr_rec.pricing_attr_value_from);
853 IF l_error_code <> 0 THEN
854
855 l_return_status := FND_API.G_RET_STS_ERROR;
856
857 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR)
858 THEN
859 FND_MESSAGE.SET_NAME('QP','QP_INVALID_ATTRIBUTE');
860 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','Value From ');
861 OE_MSG_PUB.Add;
862 END IF;
863 RAISE FND_API.G_EXC_ERROR;
864
865 END IF;
866 --dbms_output.put_line('for cano of value from ,'||l_return_status);
867
868 -- End of validation for canonical form on value from
869
870 -- Validation for Value_To
871
872 IF p_Pricing_Attr_rec.pricing_attribute_context IS NOT NULL AND
873 p_Pricing_Attr_rec.pricing_attribute IS NOT NULL AND
874 UPPER(p_Pricing_Attr_rec.comparison_operator_code) = 'BETWEEN' AND
875 (p_Pricing_Attr_rec.pricing_attr_value_to IS NULL OR
876 p_Pricing_Attr_rec.pricing_attr_value_from IS NULL)
877 THEN
878 l_return_status := FND_API.G_RET_STS_ERROR;
879
880 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR)
881 THEN
882 FND_MESSAGE.SET_NAME('QP','QP_ATTRIBUTE_REQUIRED' );
883 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',QP_PRC_UTIL.Get_Attribute_Name('PRICING_ATTR_VALUE_TO')||'/'||
884 QP_PRC_UTIL.Get_Attribute_Name('PRICING_ATTR_VALUE_FROM')); -- Fix For Bug-1974413
885 OE_MSG_PUB.Add;
886 END IF;
887 RAISE FND_API.G_EXC_ERROR;
888 END IF;
889
890 /* Added validation by dhgupta for bug # 1824227 */
891
892 IF p_Pricing_Attr_rec.pricing_attribute_context IS NOT NULL AND
893 p_Pricing_Attr_rec.pricing_attribute IS NOT NULL AND
894 UPPER(p_Pricing_Attr_rec.comparison_operator_code) <> 'BETWEEN' AND
895 p_Pricing_Attr_rec.pricing_attr_value_from IS NULL
896 THEN
897 l_return_status := FND_API.G_RET_STS_ERROR;
898
899 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR)
900 THEN
901 FND_MESSAGE.SET_NAME('QP','QP_ATTRIBUTE_REQUIRED' );
902 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',QP_PRC_UTIL.Get_Attribute_Name('PRICING_ATTR_VALUE_FROM')); --Fix For Bug-1974413
903 OE_MSG_PUB.Add;
904 END IF;
905 RAISE FND_API.G_EXC_ERROR;
906 END IF;
907
908 /* end changes for bug # 1824227 */
909
910
911
912 l_error_code:=QP_UTIL.validate_num_date(p_Pricing_Attr_rec.pricing_attribute_datatype, p_Pricing_Attr_rec.pricing_attr_value_to);
913
914 IF l_error_code <> 0 THEN
915 l_return_status := FND_API.G_RET_STS_ERROR;
916
917 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR)
918 THEN
919 FND_MESSAGE.SET_NAME('QP','QP_INVALID_ATTRIBUTE');
920 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','Value To ');
921 OE_MSG_PUB.Add;
922 END IF;
923 RAISE FND_API.G_EXC_ERROR;
924 END IF;
925 --here
926 END IF;
927 END IF;
928
929 IF ( p_Pricing_Attr_rec.pricing_attribute_context is not null
930 or p_Pricing_Attr_rec.pricing_attribute is not null ) then
931
932 --
933 -- Validate attribute dependencies here.
934 --
935
936 IF p_Pricing_Attr_rec.comparison_operator_code is null then
937
938 l_return_status := FND_API.G_RET_STS_ERROR;
939
940 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR)
941 THEN
942
943 FND_MESSAGE.SET_NAME('QP','QP_ATTRIBUTE_REQUIRED' );
944 --FND_MESSAGE.SET_NAME('QP','QP_INVALID_ATTRIBUTE');
945 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',QP_PRC_UTIL.Get_Attribute_Name('COMPARISON_OPERATOR_CODE')); --Fix For Bug-1974413
946 OE_MSG_PUB.Add;
947 END IF;
948
949 RAISE FND_API.G_EXC_ERROR;
950 ELSE
951
952
953 SELECT lookup_code
954 INTO l_comparison_operator_code
955 FROM QP_LOOKUPS
956 WHERE LOOKUP_TYPE = 'COMPARISON_OPERATOR'
957 AND LOOKUP_CODE = UPPER(p_Pricing_Attr_rec.comparison_operator_code);
958
959 If SQL%NOTFOUND
960 Then
961
962 l_return_status := FND_API.G_RET_STS_ERROR;
963
964 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR)
965 THEN
966
967 FND_MESSAGE.SET_NAME('QP','QP_ATTRIBUTE_REQUIRED' );
968 --FND_MESSAGE.SET_NAME('QP','QP_INVALID_ATTRIBUTE');
969 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',QP_PRC_UTIL.Get_Attribute_Name('COMPARISON_OPERATOR_CODE')); -- Fix For Bug-1974413
970 OE_MSG_PUB.Add;
971 END IF;
972
973 RAISE FND_API.G_EXC_ERROR;
974
975 End If;
976
977 END IF; /* comparison_operator_code is null */
978
979 END IF; /* context or atttribute is not null */
980
981
982 --dbms_output.put_line('entity validation for compa2 '||l_return_status);
983 oe_debug_pub.add('entity validation for compa2 '||l_return_status);
984
985
986
987 --Validate Qualifier_Context , Qualifier_attribute ,Qualifier_Attr Value
988 --qualifier_datatype,qualifier_precedence
989
990
991 --dbms_output.put_line('for context ,attribute,value,datatype,precedence');
992
993 /* IF ( p_Pricing_Attr_rec.pricing_attribute_context is not null
994 or p_Pricing_Attr_rec.pricing_attribute is not null
995 or p_Pricing_Attr_rec.pricing_attr_value_from is not null
996 or p_Pricing_Attr_rec.pricing_attr_value_to is not null) then
997
998 QP_UTIL.validate_qp_flexfield(flexfield_name =>'QP_ATTR_DEFNS_PRICING'
999 ,context =>p_Pricing_Attr_rec.pricing_attribute_context
1000 ,attribute =>p_Pricing_Attr_rec.pricing_attribute
1001 ,value =>p_Pricing_Attr_rec.pricing_attr_value_from
1002 ,application_short_name => 'QP'
1003 ,context_flag =>l_context_error
1004 ,attribute_flag =>l_attribute_error
1005 ,value_flag =>l_value_error
1006 ,datatype =>l_datatype
1007 ,precedence =>l_precedence
1008 ,error_code =>l_error_code
1009 );
1010
1011 If (l_context_error = 'N' AND l_error_code = 7) -- invalid context
1012 Then
1013 l_return_status := FND_API.G_RET_STS_ERROR;
1014
1015 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR)
1016 THEN
1017
1018 FND_MESSAGE.SET_NAME('QP','QP_ATTRIBUTE_REQUIRED' );
1019 --FND_MESSAGE.SET_NAME('QP','QP_INVALID_ATTRIBUTE');
1020 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',QP_PRC_UTIL.Get_Attribute_name('PRICING_ATTRIBUTE_CONTEXT')); --Fix For Bug-1974413
1021 OE_MSG_PUB.Add;
1022 END IF;
1023
1024 RAISE FND_API.G_EXC_ERROR;
1025
1026 End If;
1027
1028
1029 --dbms_output.put_line('for context '||l_return_status);
1030
1031 --dbms_output.put_line('for context ,attribute,value,datatype,precedence');
1032
1033
1034
1035 If l_attribute_error = 'N' AND l_error_code = 8 -- invalid Attribute
1036 Then
1037 l_return_status := FND_API.G_RET_STS_ERROR;
1038
1039 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR)
1040 THEN
1041
1042 FND_MESSAGE.SET_NAME('QP','QP_INVALID_ATTRIBUTE');
1043 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',' Attribute');
1044 OE_MSG_PUB.Add;
1045 END IF;
1046
1047 RAISE FND_API.G_EXC_ERROR;
1048
1049 End If;
1050
1051
1052 --dbms_output.put_line('for attributr '||l_return_status);
1053 oe_debug_pub.add('for context '||l_return_status);
1054
1055
1056 --- validate qualifier_attr_value only if comparison operator is
1057 -- '='
1058
1059 IF p_Pricing_Attr_rec.comparison_operator_code = '=' Then
1060
1061 If l_value_error = 'N' AND l_error_code = 9 -- invalid value
1062 Then
1063 l_return_status := FND_API.G_RET_STS_ERROR;
1064
1065 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR)
1066 THEN
1067
1068 FND_MESSAGE.SET_NAME('QP','QP_INVALID_ATTRIBUTE');
1069 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',' Value From ');
1070 --OE_MSG_PUB.Add;
1071 OE_MSG_PUB.Add;
1072 END IF;
1073 RAISE FND_API.G_EXC_ERROR;
1074 End If;
1075 END IF;
1076
1077
1078 --dbms_output.put_line('for value,'||l_return_status);
1079 oe_debug_pub.add('for value,'||l_return_status);
1080
1081 --dbms_output.put_line('org precede '||p_QUALIFIERS_rec.qualifier_precedence);
1082 --dbms_output.put_line('n precede '||l_precedence);
1083
1084 --dbms_output.put_line('for precedence'||l_return_status);
1085
1086 If p_Pricing_Attr_rec.pricing_attribute_datatype <> l_datatype --- invalid datatype
1087 Then
1088 l_return_status := FND_API.G_RET_STS_ERROR;
1089
1090 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR)
1091 THEN
1092 --FND_MESSAGE.SET_NAME('QP','QP_ATTRIBUTE_REQUIRED' );
1093 FND_MESSAGE.SET_NAME('QP','QP_INVALID_ATTRIBUTE');
1094 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','Pricing Attribute Datatype ');
1095 OE_MSG_PUB.Add;
1096 END IF;
1097 RAISE FND_API.G_EXC_ERROR;
1098
1099 End If;
1100
1101
1102
1103 --dbms_output.put_line('for datatype,'||l_return_status);
1104 oe_debug_pub.add('qualifier datatype,'||l_return_status);
1105
1106
1107
1108 --validation for canonical form
1109
1110 l_error_code:=QP_UTIL.validate_num_date(p_Pricing_Attr_rec.pricing_attribute_datatype, p_Pricing_Attr_rec.pricing_attr_value_from);
1111 IF l_error_code <> 0 THEN
1112
1113 l_return_status := FND_API.G_RET_STS_ERROR;
1114
1115 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR)
1116 THEN
1117 FND_MESSAGE.SET_NAME('QP','QP_INVALID_ATTRIBUTE');
1118 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','Value From ');
1119 OE_MSG_PUB.Add;
1120 END IF;
1121 RAISE FND_API.G_EXC_ERROR;
1122
1123 END IF;
1124 --dbms_output.put_line('for cano of value from ,'||l_return_status);
1125
1126 -- End of validation for canonical form on value from
1127
1128 -- Validation for Value_To
1129
1130 IF p_Pricing_Attr_rec.pricing_attribute_context IS NOT NULL AND
1131 p_Pricing_Attr_rec.pricing_attribute IS NOT NULL AND
1132 UPPER(p_Pricing_Attr_rec.comparison_operator_code) = 'BETWEEN' AND
1133 (p_Pricing_Attr_rec.pricing_attr_value_to IS NULL AND
1134 p_Pricing_Attr_rec.pricing_attr_value_from IS NULL)
1135 THEN
1136 l_return_status := FND_API.G_RET_STS_ERROR;
1137
1138 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR)
1139 THEN
1140 FND_MESSAGE.SET_NAME('QP','QP_ATTRIBUTE_REQUIRED' );
1141 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',QP_PRC_UTIL.Get_Attribute_Name('PRICING_ATTR_VALUE_TO')||'/'||
1142 QP_PRC_UTIL.Get_Attribute_Name('PRICING_ATTR_VALUE_FROM')); --Fix For Bug-1974413
1143 OE_MSG_PUB.Add;
1144 END IF;
1145 RAISE FND_API.G_EXC_ERROR;
1146 END IF;
1147
1148 l_error_code:=QP_UTIL.validate_num_date(p_Pricing_Attr_rec.pricing_attribute_datatype, p_Pricing_Attr_rec.pricing_attr_value_to);
1149
1150 IF l_error_code <> 0 THEN
1151 l_return_status := FND_API.G_RET_STS_ERROR;
1152
1153 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR)
1154 THEN
1155 FND_MESSAGE.SET_NAME('QP','QP_INVALID_ATTRIBUTE');
1156 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','Value To ');
1157 OE_MSG_PUB.Add;
1158 END IF;
1159 RAISE FND_API.G_EXC_ERROR;
1160 END IF;
1161
1162 end if;
1163 */
1164 /* if context, attribute, value_from or value_to is not null */
1165
1166 -- End of validation for canonical form on value to
1167
1168 OE_Debug_Pub.add ( 'before logging delayed request for Price Break Child Line ');
1169
1170 /* First Delayed Request to ensure that only one price break pricing attribute
1171 is allowed for a given Price Break Header.
1172 Second Delayed Request to prevent overlapping Price Break Ranges for a given
1173 Price Break Header */
1174
1175 BEGIN
1176 select from_rltd_modifier_id
1177 into l_from_rltd_modifier_id
1178 from qp_rltd_modifiers
1179 where to_rltd_modifier_id = p_Pricing_Attr_rec.list_line_id;
1180 EXCEPTION
1181 WHEN OTHERS THEN
1182 l_from_rltd_modifier_id := NULL;
1183 END;
1184
1185 --Added to check whether the PBH is for continuous or
1186 --non-continuous price breaks
1187 BEGIN
1188 select continuous_price_break_flag
1189 into l_continuous_price_break_flag
1190 from qp_list_lines
1191 where list_line_id = l_from_rltd_modifier_id;
1192 EXCEPTION
1193 WHEN OTHERS THEN
1194 l_continuous_price_break_flag := NULL;
1195 END;
1196
1197 IF p_Pricing_Attr_rec.pricing_attribute_context = 'VOLUME' AND
1198 l_from_rltd_modifier_id IS NOT NULL
1199 THEN
1200 OE_Debug_Pub.add ( 'Logging delayed request for Price Break Child Line ');
1201 QP_DELAYED_REQUESTS_PVT.Log_Request
1202 (p_entity_code => QP_GLOBALS.G_ENTITY_ALL,
1203 p_entity_id => p_PRICING_ATTR_rec.list_line_id,
1204 p_requesting_entity_code => QP_GLOBALS.G_ENTITY_PRICING_ATTR,
1205 p_requesting_entity_id => p_PRICING_ATTR_rec.list_line_id,
1206 p_request_type => QP_GLOBALS.G_MULTIPLE_PRICE_BREAK_ATTRS,
1207 p_param1 => l_from_rltd_modifier_id,
1208 x_return_status => l_return_status
1209 );
1210
1211 QP_DELAYED_REQUESTS_PVT.Log_Request
1212 (p_entity_code => QP_GLOBALS.G_ENTITY_ALL,
1213 p_entity_id => p_PRICING_ATTR_rec.list_line_id,
1214 p_requesting_entity_code => QP_GLOBALS.G_ENTITY_PRICING_ATTR,
1215 p_requesting_entity_id => p_PRICING_ATTR_rec.list_line_id,
1216 p_request_type => QP_GLOBALS.G_OVERLAPPING_PRICE_BREAKS,
1217 p_param1 => l_from_rltd_modifier_id,
1218 p_param2 => l_continuous_price_break_flag,
1219 --Added the param to call the validation
1220 --function depending upon the break type
1221 x_return_status => l_return_status
1222 );
1223
1224 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1225 oe_debug_pub.add('failed in logging a delayed request ');
1226 RAISE FND_API.G_EXC_ERROR;
1227 END IF;
1228
1229
1230 END IF; --IF Price Break Child Lines
1231
1232
1233 --Raise a warning if the Pricing/Product Attribute being used in setup
1234 --has a sourcing method of 'ATTRIBUTE MAPPING' but is not sourcing-enabled
1235 --or if its sourcing_status is not 'Y', i.e., the build sourcing conc.
1236 --program has to be run.
1237
1238 IF QP_UTIL.Attrmgr_Installed = 'Y' THEN
1239
1240 IF p_Pricing_Attr_rec.pricing_attribute_context IS NOT NULL AND
1241 p_Pricing_Attr_rec.pricing_attribute IS NOT NULL
1242 THEN
1243 QP_UTIL.Get_Context_Type('QP_ATTR_DEFNS_PRICING',
1244 p_Pricing_Attr_rec.pricing_attribute_context,
1245 l_context_type,
1246 l_error_code);
1247
1248 IF l_error_code = 0 THEN --successfully returned context_type
1249
1250 QP_UTIL.Get_Sourcing_Info(l_context_type,
1251 p_Pricing_Attr_rec.pricing_attribute_context,
1252 p_Pricing_Attr_rec.pricing_attribute,
1253 l_sourcing_enabled,
1254 l_sourcing_status,
1255 l_sourcing_method);
1256
1257 IF l_sourcing_method = 'ATTRIBUTE MAPPING' THEN
1258
1259 IF l_sourcing_enabled <> 'Y' THEN
1260
1261 FND_MESSAGE.SET_NAME('QP','QP_ENABLE_SOURCING');
1262 FND_MESSAGE.SET_TOKEN('CONTEXT',
1263 p_Pricing_Attr_rec.pricing_attribute_context);
1264 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',
1265 p_Pricing_Attr_rec.pricing_attribute);
1266 OE_MSG_PUB.Add;
1267
1268 END IF;
1269
1270 IF l_sourcing_status <> 'Y' THEN
1271
1272 FND_MESSAGE.SET_NAME('QP','QP_BUILD_SOURCING_RULES');
1273 FND_MESSAGE.SET_TOKEN('CONTEXT',
1274 p_Pricing_Attr_rec.pricing_attribute_context);
1275 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',
1276 p_Pricing_Attr_rec.pricing_attribute);
1277 OE_MSG_PUB.Add;
1278
1279 END IF;
1280
1281 END IF; --If sourcing_method = 'ATTRIBUTE MAPPING'
1282
1283 END IF; --l_error_code = 0
1284
1285 END IF;--If pricing_attribute_context and pricing_attribute are NOT NULL
1286
1287 END IF; --If QP_UTIL.Attrmgr_Installed = 'Y'
1288
1289 -- Done validating entity
1290
1291 x_return_status := l_return_status;
1292
1293 EXCEPTION
1294
1295 WHEN NO_DATA_FOUND THEN
1296
1297 x_return_status := FND_API.G_RET_STS_ERROR;
1298
1299 WHEN FND_API.G_EXC_ERROR THEN
1300
1301 x_return_status := FND_API.G_RET_STS_ERROR;
1302
1303 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1304
1305 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1306
1307 WHEN OTHERS THEN
1308
1309 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1310
1311 IF oe_msg_pub.Check_Msg_Level(oe_msg_pub.G_MSG_LVL_UNEXP_ERROR)
1312 THEN
1313 oe_msg_pub.Add_Exc_Msg
1314 ( G_PKG_NAME
1315 , 'Entity'
1316 );
1317 END IF;
1318
1319 END Entity;
1320
1321 -- Procedure Attributes
1322
1323 PROCEDURE Attributes
1324 ( x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2
1325 , p_PRICING_ATTR_rec IN QP_Price_List_PUB.Pricing_Attr_Rec_Type
1326 , p_old_PRICING_ATTR_rec IN QP_Price_List_PUB.Pricing_Attr_Rec_Type :=
1327 QP_Price_List_PUB.G_MISS_PRICING_ATTR_REC
1328 )
1329 IS
1330 l_pte_code varchar2(30);
1331 l_ss_code varchar2(30);
1332 BEGIN
1333
1334 x_return_status := FND_API.G_RET_STS_SUCCESS;
1335
1336 -- Validate PRICING_ATTR attributes
1337
1338 IF p_PRICING_ATTR_rec.accumulate_flag IS NOT NULL AND
1339 ( p_PRICING_ATTR_rec.accumulate_flag <>
1340 p_old_PRICING_ATTR_rec.accumulate_flag OR
1341 p_old_PRICING_ATTR_rec.accumulate_flag IS NULL )
1342 THEN
1343 IF NOT QP_Validate.Accumulate(p_PRICING_ATTR_rec.accumulate_flag) THEN
1344 x_return_status := FND_API.G_RET_STS_ERROR;
1345 END IF;
1346 END IF;
1347
1348 IF p_PRICING_ATTR_rec.attribute_grouping_no IS NOT NULL AND
1349 ( p_PRICING_ATTR_rec.attribute_grouping_no <>
1350 p_old_PRICING_ATTR_rec.attribute_grouping_no OR
1351 p_old_PRICING_ATTR_rec.attribute_grouping_no IS NULL )
1352 THEN
1353 IF NOT QP_Validate.Attribute_Grouping_No(p_PRICING_ATTR_rec.attribute_grouping_no) THEN
1354 x_return_status := FND_API.G_RET_STS_ERROR;
1355 END IF;
1356 END IF;
1357
1358 IF p_PRICING_ATTR_rec.created_by IS NOT NULL AND
1359 ( p_PRICING_ATTR_rec.created_by <>
1360 p_old_PRICING_ATTR_rec.created_by OR
1361 p_old_PRICING_ATTR_rec.created_by IS NULL )
1362 THEN
1363 IF NOT QP_Validate.Created_By(p_PRICING_ATTR_rec.created_by) THEN
1364 x_return_status := FND_API.G_RET_STS_ERROR;
1365 END IF;
1366 END IF;
1367
1368 IF p_PRICING_ATTR_rec.creation_date IS NOT NULL AND
1369 ( p_PRICING_ATTR_rec.creation_date <>
1370 p_old_PRICING_ATTR_rec.creation_date OR
1371 p_old_PRICING_ATTR_rec.creation_date IS NULL )
1372 THEN
1373 IF NOT QP_Validate.Creation_Date(p_PRICING_ATTR_rec.creation_date) THEN
1374 x_return_status := FND_API.G_RET_STS_ERROR;
1375 END IF;
1376 END IF;
1377
1378 IF p_PRICING_ATTR_rec.excluder_flag IS NOT NULL AND
1379 ( p_PRICING_ATTR_rec.excluder_flag <>
1380 p_old_PRICING_ATTR_rec.excluder_flag OR
1381 p_old_PRICING_ATTR_rec.excluder_flag IS NULL )
1382 THEN
1383 IF NOT QP_Validate.Excluder(p_PRICING_ATTR_rec.excluder_flag) THEN
1384 x_return_status := FND_API.G_RET_STS_ERROR;
1385 END IF;
1386 END IF;
1387
1388 IF p_PRICING_ATTR_rec.last_updated_by IS NOT NULL AND
1389 ( p_PRICING_ATTR_rec.last_updated_by <>
1390 p_old_PRICING_ATTR_rec.last_updated_by OR
1391 p_old_PRICING_ATTR_rec.last_updated_by IS NULL )
1392 THEN
1393 IF NOT QP_Validate.Last_Updated_By(p_PRICING_ATTR_rec.last_updated_by) THEN
1394 x_return_status := FND_API.G_RET_STS_ERROR;
1395 END IF;
1396 END IF;
1397
1398 IF p_PRICING_ATTR_rec.last_update_date IS NOT NULL AND
1399 ( p_PRICING_ATTR_rec.last_update_date <>
1400 p_old_PRICING_ATTR_rec.last_update_date OR
1401 p_old_PRICING_ATTR_rec.last_update_date IS NULL )
1402 THEN
1403 IF NOT QP_Validate.Last_Update_Date(p_PRICING_ATTR_rec.last_update_date) THEN
1404 x_return_status := FND_API.G_RET_STS_ERROR;
1405 END IF;
1406 END IF;
1407
1408 IF p_PRICING_ATTR_rec.last_update_login IS NOT NULL AND
1409 ( p_PRICING_ATTR_rec.last_update_login <>
1410 p_old_PRICING_ATTR_rec.last_update_login OR
1411 p_old_PRICING_ATTR_rec.last_update_login IS NULL )
1412 THEN
1413 IF NOT QP_Validate.Last_Update_Login(p_PRICING_ATTR_rec.last_update_login) THEN
1414 x_return_status := FND_API.G_RET_STS_ERROR;
1415 END IF;
1416 END IF;
1417
1418 IF p_PRICING_ATTR_rec.list_line_id IS NOT NULL AND
1419 ( p_PRICING_ATTR_rec.list_line_id <>
1420 p_old_PRICING_ATTR_rec.list_line_id OR
1421 p_old_PRICING_ATTR_rec.list_line_id IS NULL )
1422 THEN
1423 IF NOT QP_Validate.List_Line(p_PRICING_ATTR_rec.list_line_id) THEN
1424 x_return_status := FND_API.G_RET_STS_ERROR;
1425 END IF;
1426 END IF;
1427
1428 IF p_PRICING_ATTR_rec.pricing_attribute IS NOT NULL AND
1429 ( p_PRICING_ATTR_rec.pricing_attribute <>
1430 p_old_PRICING_ATTR_rec.pricing_attribute OR
1431 p_old_PRICING_ATTR_rec.pricing_attribute IS NULL )
1432 THEN
1433 IF NOT QP_Validate.Pricing_Attribute(p_PRICING_ATTR_rec.pricing_attribute) THEN
1434 x_return_status := FND_API.G_RET_STS_ERROR;
1435 END IF;
1436 END IF;
1437
1438 IF p_PRICING_ATTR_rec.pricing_attribute_context IS NOT NULL AND
1439 ( p_PRICING_ATTR_rec.pricing_attribute_context <>
1440 p_old_PRICING_ATTR_rec.pricing_attribute_context OR
1441 p_old_PRICING_ATTR_rec.pricing_attribute_context IS NULL )
1442 THEN
1443 IF NOT QP_Validate.Pricing_Attribute_Context(p_PRICING_ATTR_rec.pricing_attribute_context) THEN
1444 x_return_status := FND_API.G_RET_STS_ERROR;
1445 END IF;
1446 END IF;
1447
1448 IF p_PRICING_ATTR_rec.pricing_attribute_id IS NOT NULL AND
1449 ( p_PRICING_ATTR_rec.pricing_attribute_id <>
1450 p_old_PRICING_ATTR_rec.pricing_attribute_id OR
1451 p_old_PRICING_ATTR_rec.pricing_attribute_id IS NULL )
1452 THEN
1453 IF NOT QP_Validate.Pricing_Attribute(p_PRICING_ATTR_rec.pricing_attribute_id) THEN
1454 x_return_status := FND_API.G_RET_STS_ERROR;
1455 END IF;
1456 END IF;
1457
1458 IF p_PRICING_ATTR_rec.from_rltd_modifier_id IS NOT NULL AND
1459 ( p_PRICING_ATTR_rec.from_rltd_modifier_id <>
1460 p_old_PRICING_ATTR_rec.from_rltd_modifier_id OR
1461 p_old_PRICING_ATTR_rec.from_rltd_modifier_id IS NULL )
1462 THEN
1463 /*
1464 IF NOT QP_Validate.From_Rltd_Modifier_Id(p_PRICING_ATTR_rec.from_rltd_modifier_id) THEN
1465 x_return_status := FND_API.G_RET_STS_ERROR;
1466 END IF;
1467 */
1468 NULL;
1469
1470 END IF;
1471
1472 IF p_PRICING_ATTR_rec.pricing_attr_value_from IS NOT NULL AND
1473 ( p_PRICING_ATTR_rec.pricing_attr_value_from <>
1474 p_old_PRICING_ATTR_rec.pricing_attr_value_from OR
1475 p_old_PRICING_ATTR_rec.pricing_attr_value_from IS NULL )
1476 THEN
1477 IF NOT QP_Validate.Pricing_Attr_Value_From(p_PRICING_ATTR_rec.pricing_attr_value_from) THEN
1478 x_return_status := FND_API.G_RET_STS_ERROR;
1479 END IF;
1480 END IF;
1481
1482 IF p_PRICING_ATTR_rec.pricing_attr_value_to IS NOT NULL AND
1483 ( p_PRICING_ATTR_rec.pricing_attr_value_to <>
1484 p_old_PRICING_ATTR_rec.pricing_attr_value_to OR
1485 p_old_PRICING_ATTR_rec.pricing_attr_value_to IS NULL )
1486 THEN
1487 IF NOT QP_Validate.Pricing_Attr_Value_To(p_PRICING_ATTR_rec.pricing_attr_value_to) THEN
1488 x_return_status := FND_API.G_RET_STS_ERROR;
1489 END IF;
1490 END IF;
1491
1492 IF p_PRICING_ATTR_rec.product_attribute IS NOT NULL AND
1493 ( p_PRICING_ATTR_rec.product_attribute <>
1494 p_old_PRICING_ATTR_rec.product_attribute OR
1495 p_old_PRICING_ATTR_rec.product_attribute IS NULL )
1496 THEN
1497 IF NOT QP_Validate.Product_Attribute(p_PRICING_ATTR_rec.product_attribute) THEN
1498 x_return_status := FND_API.G_RET_STS_ERROR;
1499 END IF;
1500 END IF;
1501
1502 IF p_PRICING_ATTR_rec.product_attribute_context IS NOT NULL AND
1503 ( p_PRICING_ATTR_rec.product_attribute_context <>
1504 p_old_PRICING_ATTR_rec.product_attribute_context OR
1505 p_old_PRICING_ATTR_rec.product_attribute_context IS NULL )
1506 THEN
1507 IF NOT QP_Validate.Product_Attribute_Context(p_PRICING_ATTR_rec.product_attribute_context) THEN
1508 x_return_status := FND_API.G_RET_STS_ERROR;
1509 END IF;
1510 END IF;
1511
1512 IF p_PRICING_ATTR_rec.product_attr_value IS NOT NULL AND
1513 ( p_PRICING_ATTR_rec.product_attr_value <>
1514 p_old_PRICING_ATTR_rec.product_attr_value OR
1515 p_old_PRICING_ATTR_rec.product_attr_value IS NULL )
1516 THEN
1517 IF NOT QP_Validate.Product_Attr_Value(p_PRICING_ATTR_rec.product_attr_value) THEN
1518 x_return_status := FND_API.G_RET_STS_ERROR;
1519 END IF;
1520 END IF;
1521
1522 IF p_PRICING_ATTR_rec.product_uom_code IS NOT NULL AND
1523 ( p_PRICING_ATTR_rec.product_uom_code <>
1524 p_old_PRICING_ATTR_rec.product_uom_code OR
1525 p_old_PRICING_ATTR_rec.product_uom_code IS NULL )
1526 THEN
1527 IF NOT QP_Validate.Product_Uom(p_PRICING_ATTR_rec.product_uom_code) THEN
1528 x_return_status := FND_API.G_RET_STS_ERROR;
1529 END IF;
1530 END IF;
1531
1532 IF p_PRICING_ATTR_rec.program_application_id IS NOT NULL AND
1533 ( p_PRICING_ATTR_rec.program_application_id <>
1534 p_old_PRICING_ATTR_rec.program_application_id OR
1535 p_old_PRICING_ATTR_rec.program_application_id IS NULL )
1536 THEN
1537 IF NOT QP_Validate.Program_Application(p_PRICING_ATTR_rec.program_application_id) THEN
1538 x_return_status := FND_API.G_RET_STS_ERROR;
1539 END IF;
1540 END IF;
1541
1542 IF p_PRICING_ATTR_rec.program_id IS NOT NULL AND
1543 ( p_PRICING_ATTR_rec.program_id <>
1544 p_old_PRICING_ATTR_rec.program_id OR
1545 p_old_PRICING_ATTR_rec.program_id IS NULL )
1546 THEN
1547 IF NOT QP_Validate.Program(p_PRICING_ATTR_rec.program_id) THEN
1548 x_return_status := FND_API.G_RET_STS_ERROR;
1549 END IF;
1550 END IF;
1551
1552 IF p_PRICING_ATTR_rec.program_update_date IS NOT NULL AND
1553 ( p_PRICING_ATTR_rec.program_update_date <>
1554 p_old_PRICING_ATTR_rec.program_update_date OR
1555 p_old_PRICING_ATTR_rec.program_update_date IS NULL )
1556 THEN
1557 IF NOT QP_Validate.Program_Update_Date(p_PRICING_ATTR_rec.program_update_date) THEN
1558 x_return_status := FND_API.G_RET_STS_ERROR;
1559 END IF;
1560 END IF;
1561
1562 IF p_PRICING_ATTR_rec.request_id IS NOT NULL AND
1563 ( p_PRICING_ATTR_rec.request_id <>
1564 p_old_PRICING_ATTR_rec.request_id OR
1565 p_old_PRICING_ATTR_rec.request_id IS NULL )
1566 THEN
1567 IF NOT QP_Validate.Request(p_PRICING_ATTR_rec.request_id) THEN
1568 x_return_status := FND_API.G_RET_STS_ERROR;
1569 END IF;
1570 END IF;
1571
1572 IF p_PRICING_ATTR_rec.comparison_operator_code IS NOT NULL AND
1573 ( p_PRICING_ATTR_rec.comparison_operator_code <>
1574 p_old_PRICING_ATTR_rec.comparison_operator_code OR
1575 p_old_PRICING_ATTR_rec.comparison_operator_code IS NULL )
1576 THEN
1577 IF NOT QP_Validate.comparison_operator(p_PRICING_ATTR_rec.comparison_operator_code) THEN
1578 x_return_status := FND_API.G_RET_STS_ERROR;
1579 END IF;
1580 END IF;
1581
1582 IF p_PRICING_ATTR_rec.pricing_attribute_datatype IS NOT NULL AND
1583 ( p_PRICING_ATTR_rec.pricing_attribute_datatype <>
1584 p_old_PRICING_ATTR_rec.pricing_attribute_datatype OR
1585 p_old_PRICING_ATTR_rec.pricing_attribute_datatype IS NULL )
1586 THEN
1587 IF NOT QP_Validate.Pricing_Attribute_Datatype(p_PRICING_ATTR_rec.pricing_attribute_datatype) THEN
1588 x_return_status := FND_API.G_RET_STS_ERROR;
1589 END IF;
1590 END IF;
1591
1592 IF p_PRICING_ATTR_rec.product_attribute_datatype IS NOT NULL AND
1593 ( p_PRICING_ATTR_rec.product_attribute_datatype <>
1594 p_old_PRICING_ATTR_rec.product_attribute_datatype OR
1595 p_old_PRICING_ATTR_rec.product_attribute_datatype IS NULL )
1596 THEN
1597 IF NOT QP_Validate.product_attribute_datatype(p_PRICING_ATTR_rec.product_attribute_datatype) THEN
1598 x_return_status := FND_API.G_RET_STS_ERROR;
1599 END IF;
1600 END IF;
1601
1602
1603 IF (p_PRICING_ATTR_rec.attribute1 IS NOT NULL AND
1604 ( p_PRICING_ATTR_rec.attribute1 <>
1605 p_old_PRICING_ATTR_rec.attribute1 OR
1606 p_old_PRICING_ATTR_rec.attribute1 IS NULL ))
1607 OR (p_PRICING_ATTR_rec.attribute10 IS NOT NULL AND
1608 ( p_PRICING_ATTR_rec.attribute10 <>
1609 p_old_PRICING_ATTR_rec.attribute10 OR
1610 p_old_PRICING_ATTR_rec.attribute10 IS NULL ))
1611 OR (p_PRICING_ATTR_rec.attribute11 IS NOT NULL AND
1612 ( p_PRICING_ATTR_rec.attribute11 <>
1613 p_old_PRICING_ATTR_rec.attribute11 OR
1614 p_old_PRICING_ATTR_rec.attribute11 IS NULL ))
1615 OR (p_PRICING_ATTR_rec.attribute12 IS NOT NULL AND
1616 ( p_PRICING_ATTR_rec.attribute12 <>
1617 p_old_PRICING_ATTR_rec.attribute12 OR
1618 p_old_PRICING_ATTR_rec.attribute12 IS NULL ))
1619 OR (p_PRICING_ATTR_rec.attribute13 IS NOT NULL AND
1620 ( p_PRICING_ATTR_rec.attribute13 <>
1621 p_old_PRICING_ATTR_rec.attribute13 OR
1622 p_old_PRICING_ATTR_rec.attribute13 IS NULL ))
1623 OR (p_PRICING_ATTR_rec.attribute14 IS NOT NULL AND
1624 ( p_PRICING_ATTR_rec.attribute14 <>
1625 p_old_PRICING_ATTR_rec.attribute14 OR
1626 p_old_PRICING_ATTR_rec.attribute14 IS NULL ))
1627 OR (p_PRICING_ATTR_rec.attribute15 IS NOT NULL AND
1628 ( p_PRICING_ATTR_rec.attribute15 <>
1629 p_old_PRICING_ATTR_rec.attribute15 OR
1630 p_old_PRICING_ATTR_rec.attribute15 IS NULL ))
1631 OR (p_PRICING_ATTR_rec.attribute2 IS NOT NULL AND
1632 ( p_PRICING_ATTR_rec.attribute2 <>
1633 p_old_PRICING_ATTR_rec.attribute2 OR
1634 p_old_PRICING_ATTR_rec.attribute2 IS NULL ))
1635 OR (p_PRICING_ATTR_rec.attribute3 IS NOT NULL AND
1636 ( p_PRICING_ATTR_rec.attribute3 <>
1637 p_old_PRICING_ATTR_rec.attribute3 OR
1638 p_old_PRICING_ATTR_rec.attribute3 IS NULL ))
1639 OR (p_PRICING_ATTR_rec.attribute4 IS NOT NULL AND
1640 ( p_PRICING_ATTR_rec.attribute4 <>
1641 p_old_PRICING_ATTR_rec.attribute4 OR
1642 p_old_PRICING_ATTR_rec.attribute4 IS NULL ))
1643 OR (p_PRICING_ATTR_rec.attribute5 IS NOT NULL AND
1644 ( p_PRICING_ATTR_rec.attribute5 <>
1645 p_old_PRICING_ATTR_rec.attribute5 OR
1646 p_old_PRICING_ATTR_rec.attribute5 IS NULL ))
1647 OR (p_PRICING_ATTR_rec.attribute6 IS NOT NULL AND
1648 ( p_PRICING_ATTR_rec.attribute6 <>
1649 p_old_PRICING_ATTR_rec.attribute6 OR
1650 p_old_PRICING_ATTR_rec.attribute6 IS NULL ))
1651 OR (p_PRICING_ATTR_rec.attribute7 IS NOT NULL AND
1652 ( p_PRICING_ATTR_rec.attribute7 <>
1653 p_old_PRICING_ATTR_rec.attribute7 OR
1654 p_old_PRICING_ATTR_rec.attribute7 IS NULL ))
1655 OR (p_PRICING_ATTR_rec.attribute8 IS NOT NULL AND
1656 ( p_PRICING_ATTR_rec.attribute8 <>
1657 p_old_PRICING_ATTR_rec.attribute8 OR
1658 p_old_PRICING_ATTR_rec.attribute8 IS NULL ))
1659 OR (p_PRICING_ATTR_rec.attribute9 IS NOT NULL AND
1660 ( p_PRICING_ATTR_rec.attribute9 <>
1661 p_old_PRICING_ATTR_rec.attribute9 OR
1662 p_old_PRICING_ATTR_rec.attribute9 IS NULL ))
1663 OR (p_PRICING_ATTR_rec.context IS NOT NULL AND
1664 ( p_PRICING_ATTR_rec.context <>
1665 p_old_PRICING_ATTR_rec.context OR
1666 p_old_PRICING_ATTR_rec.context IS NULL ))
1667 THEN
1668
1669 -- These calls are temporarily commented out
1670
1671 /*
1672 FND_FLEX_DESC_VAL.Set_Column_Value
1673 ( column_name => 'ATTRIBUTE1'
1674 , column_value => p_PRICING_ATTR_rec.attribute1
1675 );
1676 FND_FLEX_DESC_VAL.Set_Column_Value
1677 ( column_name => 'ATTRIBUTE10'
1678 , column_value => p_PRICING_ATTR_rec.attribute10
1679 );
1680 FND_FLEX_DESC_VAL.Set_Column_Value
1681 ( column_name => 'ATTRIBUTE11'
1682 , column_value => p_PRICING_ATTR_rec.attribute11
1683 );
1684 FND_FLEX_DESC_VAL.Set_Column_Value
1685 ( column_name => 'ATTRIBUTE12'
1686 , column_value => p_PRICING_ATTR_rec.attribute12
1687 );
1688 FND_FLEX_DESC_VAL.Set_Column_Value
1689 ( column_name => 'ATTRIBUTE13'
1690 , column_value => p_PRICING_ATTR_rec.attribute13
1691 );
1692 FND_FLEX_DESC_VAL.Set_Column_Value
1693 ( column_name => 'ATTRIBUTE14'
1694 , column_value => p_PRICING_ATTR_rec.attribute14
1695 );
1696 FND_FLEX_DESC_VAL.Set_Column_Value
1697 ( column_name => 'ATTRIBUTE15'
1698 , column_value => p_PRICING_ATTR_rec.attribute15
1699 );
1700 FND_FLEX_DESC_VAL.Set_Column_Value
1701 ( column_name => 'ATTRIBUTE2'
1702 , column_value => p_PRICING_ATTR_rec.attribute2
1703 );
1704 FND_FLEX_DESC_VAL.Set_Column_Value
1705 ( column_name => 'ATTRIBUTE3'
1706 , column_value => p_PRICING_ATTR_rec.attribute3
1707 );
1708 FND_FLEX_DESC_VAL.Set_Column_Value
1709 ( column_name => 'ATTRIBUTE4'
1710 , column_value => p_PRICING_ATTR_rec.attribute4
1711 );
1712 FND_FLEX_DESC_VAL.Set_Column_Value
1713 ( column_name => 'ATTRIBUTE5'
1714 , column_value => p_PRICING_ATTR_rec.attribute5
1715 );
1716 FND_FLEX_DESC_VAL.Set_Column_Value
1717 ( column_name => 'ATTRIBUTE6'
1718 , column_value => p_PRICING_ATTR_rec.attribute6
1719 );
1720 FND_FLEX_DESC_VAL.Set_Column_Value
1721 ( column_name => 'ATTRIBUTE7'
1722 , column_value => p_PRICING_ATTR_rec.attribute7
1723 );
1724 FND_FLEX_DESC_VAL.Set_Column_Value
1725 ( column_name => 'ATTRIBUTE8'
1726 , column_value => p_PRICING_ATTR_rec.attribute8
1727 );
1728 FND_FLEX_DESC_VAL.Set_Column_Value
1729 ( column_name => 'ATTRIBUTE9'
1730 , column_value => p_PRICING_ATTR_rec.attribute9
1731 );
1732 FND_FLEX_DESC_VAL.Set_Column_Value
1733 ( column_name => 'CONTEXT'
1734 , column_value => p_PRICING_ATTR_rec.context
1735 );
1736 */
1737
1738 -- Validate descriptive flexfield.
1739
1740 IF NOT QP_Validate.Desc_Flex( 'PRICING_ATTR' ) THEN
1741 x_return_status := FND_API.G_RET_STS_ERROR;
1742 END IF;
1743
1744 END IF;
1745
1746 -- Done validating attributes
1747
1748 EXCEPTION
1749
1750
1751
1752 WHEN FND_API.G_EXC_ERROR THEN
1753
1754 x_return_status := FND_API.G_RET_STS_ERROR;
1755
1756 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1757
1758 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1759
1760 WHEN OTHERS THEN
1761
1762 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1763
1764 IF oe_msg_pub.Check_Msg_Level(oe_msg_pub.G_MSG_LVL_UNEXP_ERROR)
1765 THEN
1766 oe_msg_pub.Add_Exc_Msg
1767 ( G_PKG_NAME
1768 , 'Attributes'
1769 );
1770 END IF;
1771
1772 END Attributes;
1773
1774 -- Procedure Entity_Delete
1775
1776 PROCEDURE Entity_Delete
1777 ( x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2
1778 , p_PRICING_ATTR_rec IN QP_Price_List_PUB.Pricing_Attr_Rec_Type
1779 )
1780 IS
1781 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
1782 BEGIN
1783
1784 -- Validate entity delete.
1785
1786 NULL;
1787
1788 -- Done.
1789
1790 x_return_status := l_return_status;
1791
1792 EXCEPTION
1793
1794 WHEN FND_API.G_EXC_ERROR THEN
1795
1796 x_return_status := FND_API.G_RET_STS_ERROR;
1797
1798 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1799
1800 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1801
1802 WHEN OTHERS THEN
1803
1804 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1805
1806 IF oe_msg_pub.Check_Msg_Level(oe_msg_pub.G_MSG_LVL_UNEXP_ERROR)
1807 THEN
1808 oe_msg_pub.Add_Exc_Msg
1809 ( G_PKG_NAME
1810 , 'Entity_Delete'
1811 );
1812 END IF;
1813
1814 END Entity_Delete;
1815
1816
1817 /* Start Procedure */
1818
1819 FUNCTION Check_Dup_Pra ( p_Start_Date_Active IN DATE
1820 , p_End_Date_Active IN DATE
1821 , p_Revision IN VARCHAR2
1822 , p_List_Line_ID IN NUMBER
1823 , p_List_Header_ID IN NUMBER
1824 , p_x_rows OUT NOCOPY NUMBER
1825 , p_x_revision OUT NOCOPY BOOLEAN
1826 , p_x_effdates OUT NOCOPY BOOLEAN
1827 , p_x_dup_sdate OUT NOCOPY DATE
1828 , p_x_dup_edate OUT NOCOPY DATE
1829 )
1830 RETURN BOOLEAN
1831 is
1832 l_attr_lines_count NUMBER;
1833
1834 CURSOR get_rec(l_List_Line_ID NUMBER ) is
1835 select a.list_line_id col1, b.list_line_id col2
1836 from qp_pricing_attributes a, qp_pricing_attributes b
1837 where a.list_line_id = l_List_Line_ID
1838 and b.list_line_id <> l_List_Line_ID
1839 and not exists (select Null
1840 from qp_rltd_modifiers qrm
1841 Where qrm.to_rltd_modifier_id = b.list_line_id
1842 and qrm.rltd_modifier_grp_type = 'PRICE BREAK')
1843 and b.product_attribute_context = a.product_attribute_context
1844 and b.product_attribute = a.product_attribute
1845 and b.product_attr_value = a.product_attr_value
1846 and b.product_uom_code = a.product_uom_code --2943344
1847 and nvl( b.pricing_attribute_context,' ') = nvl(a.pricing_attribute_context,' ')
1848 and nvl(b.pricing_attribute,' ') = nvl(a.pricing_attribute,' ')
1849 and nvl(b.pricing_attr_value_from,0) = nvl(a.pricing_attr_value_from,0)
1850 and nvl(b.pricing_attr_value_to,0) = nvl(a.pricing_attr_value_to,0)
1851 and nvl(b.comparison_operator_code,' ') = nvl(a.comparison_operator_code,' ') --Added for 2128739; julin: added nvl
1852 and b.list_header_id = p_List_Header_Id
1853 and a.list_header_id = p_List_Header_Id
1854 group by a.list_line_id, b.list_line_id
1855 having count(b.list_line_id ) = l_attr_lines_count
1856 and count(b.list_line_id) = ( select count(*) from qp_pricing_attributes where list_line_id = b.list_line_id); --2326820
1857
1858
1859
1860 l_count varchar2(2);
1861 l_min_date date := to_date('01/01/1900', 'MM/DD/YYYY');
1862 l_max_date date := to_date('12/31/9999', 'MM/DD/YYYY');
1863 l_sdate DATE := NULL;
1864 l_edate DATE := NULL;
1865 BEGIN
1866
1867 select count(*)
1868 Into l_attr_lines_count
1869 from qp_pricing_attributes
1870 where list_line_id = p_List_Line_ID;
1871
1872 for rec in get_rec(p_List_Line_ID)
1873 loop
1874
1875 /*---------------- Bug 1951884-----------------*/
1876 l_min_date := to_date('01/01/1900', 'MM/DD/YYYY');
1877 l_max_date := to_date('12/31/9999', 'MM/DD/YYYY');
1878 /*--------------------------------------------*/
1879 begin
1880 SELECT revision, start_date_active, end_date_active
1881 into l_count, l_sdate, l_edate
1882 from qp_list_lines
1883 where list_line_id = rec.col2;
1884
1885 exception
1886 when no_data_found then null;
1887 end;
1888
1889 if l_count = p_Revision then
1890 p_x_revision := FALSE;
1891 RETURN FALSE;
1892
1893 END IF;
1894
1895
1896 IF ( nvl(p_Start_Date_Active, l_min_date) <= nvl(l_sdate, l_min_date))
1897 THEN
1898 l_min_date := nvl(p_Start_Date_Active, l_min_date);
1899 ELSE
1900 l_min_date := nvl(l_sdate, l_min_date);
1901 END IF;
1902
1903 IF ( nvl(p_End_Date_Active, l_max_date) >= nvl(l_edate, l_max_date))
1904 THEN
1905 l_max_date := nvl(p_End_Date_Active, l_max_date);
1906 ELSE
1907 l_max_date := nvl(l_edate, l_max_date);
1908 END IF;
1909
1910
1911
1912 if ( trunc(nvl(l_sdate, l_min_date)) between
1913 trunc(nvl(p_Start_Date_Active, l_min_date))
1914 and trunc(nvl(p_End_Date_Active, l_max_date)) )
1915 OR
1916 ( trunc(nvl(l_edate, l_max_date)) between
1917 trunc(nvl(p_Start_Date_Active, l_min_date))
1918 and trunc(nvl(p_End_Date_Active, l_max_date)) )
1919
1920 OR
1921 ( trunc(nvl(l_sdate, l_min_date)) <=
1922 nvl(p_Start_Date_Active,l_min_date)
1923 AND
1924 trunc(nvl(l_edate, l_max_date)) >=
1925 nvl(p_End_Date_Active,l_max_date) )
1926
1927 THEN
1928 p_x_dup_sdate := l_sdate;
1929 p_x_dup_edate := l_edate;
1930
1931 oe_debug_pub.add('Dates Overlapping' );
1932 p_x_effdates := FALSE;
1933 RETURN FALSE;
1934 end if;
1935
1936 end loop;
1937
1938
1939 p_x_rows := sql%rowcount;
1940 RETURN TRUE;
1941
1942 EXCEPTION
1943 WHEN NO_DATA_FOUND THEN
1944 p_x_rows := sql%rowcount;
1945 p_x_revision := TRUE;
1946 p_x_effdates := TRUE;
1947 RETURN TRUE;
1948
1949 WHEN OTHERS THEN
1950 p_x_rows := sql%rowcount;
1951 p_x_revision := FALSE;
1952 p_x_effdates := FALSE;
1953 RETURN FALSE;
1954
1955 END Check_Dup_Pra;
1956
1957
1958 /* End ------------- */
1959
1960
1961
1962
1963
1964
1965
1966 /* New Code Jan18 */
1967 FUNCTION Check_Line_Revision( p_Revision IN VARCHAR2
1968 , p_List_Line_ID IN NUMBER
1969 , p_List_Header_ID IN NUMBER
1970 , p_x_rows OUT NOCOPY /* file.sql.39 change */ NUMBER
1971 )
1972 RETURN BOOLEAN
1973 IS
1974 l_dummy VARCHAR2(20);
1975 l_dummy1 VARCHAR2(20);
1976 BEGIN
1977
1978 select a.list_line_id, b.list_line_id
1979 into l_dummy, l_dummy1
1980 from qp_pricing_attributes a, qp_pricing_attributes b, qp_list_lines c
1981 where a.list_line_id = p_List_Line_ID
1982 and b.list_line_id <> p_List_Line_ID
1983 and b.product_attribute_context = a.product_attribute_context
1984 and b.product_attribute = a.product_attribute
1985 and b.product_attr_value = a.product_attr_value
1986 -- and nvl(b.product_attribute_context,' ') = nvl(a.product_attribute_context,' ') ** bug 2813068 **
1987 -- and nvl(b.product_attribute,' ') = nvl(a.product_attribute,' ') ** bug 2813068 **
1988 -- and nvl(b.product_attr_value,' ') = nvl(a.product_attr_value,' ') ** bug 2813068 **
1989 and nvl(b.product_uom_code,' ') = nvl(a.product_uom_code,' ')
1990 and nvl(b.pricing_attribute_context,' ') = nvl(a.pricing_attribute_context,' ')
1991 and nvl(b.pricing_attribute,' ') = nvl(a.pricing_attribute,' ' )
1992 and nvl(b.pricing_attr_value_from,' ') = nvl(a.pricing_attr_value_from,' ')
1993 and nvl(b.pricing_attr_value_to,' ') = nvl(a.pricing_attr_value_to,' ')
1994 and b.comparison_operator_code = a.comparison_operator_code --Added for 2128739
1995 and a.list_line_id = c.list_line_id
1996 and nvl(c.revision,' ') = nvl(p_Revision,' ')
1997 group by a.list_line_id, b.list_line_id
1998 having count(b.list_line_id) = ( select count(*)
1999 from qp_pricing_attributes
2000 where list_line_id = b.list_line_id) ;
2001
2002
2003 p_x_rows := sql%rowcount;
2004
2005 if p_x_rows <> 0 then
2006 RETURN FALSE;
2007 else
2008 RETURN TRUE;
2009 end if;
2010
2011
2012 EXCEPTION
2013 WHEN NO_DATA_FOUND THEN
2014 p_x_rows := sql%rowcount;
2015 RETURN TRUE;
2016
2017
2018 WHEN OTHERS THEN
2019 p_x_rows := sql%rowcount;
2020 RETURN FALSE;
2021
2022
2023
2024 END Check_Line_Revision;
2025
2026
2027 FUNCTION Check_Line_EffDates( p_Start_Date_Active IN DATE
2028 , p_End_Date_Active IN DATE
2029 , p_Revision IN VARCHAR2
2030 , p_List_Line_ID IN NUMBER
2031 , p_List_Header_ID IN NUMBER
2032 , p_x_rows OUT NOCOPY /* file.sql.39 change */ NUMBER
2033 )
2034 RETURN BOOLEAN
2035 IS
2036 l_dummy VARCHAR2(20);
2037 l_dummy1 VARCHAR2(20);
2038 BEGIN
2039
2040 select a.list_line_id, b.list_line_id
2041 into l_dummy, l_dummy1
2042 from qp_pricing_attributes a, qp_pricing_attributes b, qp_list_lines c
2043 where a.list_line_id = p_List_Line_ID
2044 and b.list_line_id <> p_List_Line_ID
2045 and b.product_attribute_context = a.product_attribute_context
2046 and b.product_attribute = a.product_attribute
2047 and b.product_attr_value = a.product_attr_value
2048 -- and nvl(b.product_attribute_context,' ') = nvl(a.product_attribute_context,' ') ** bug 2813068 **
2049 -- and nvl(b.product_attribute,' ') = nvl(a.product_attribute,' ') ** bug 2813068 **
2050 -- and nvl(b.product_attr_value,' ') = nvl(a.product_attr_value,' ') ** bug 2813068 **
2051 and nvl(b.product_uom_code,' ') = nvl(a.product_uom_code,' ')
2052 and nvl(b.pricing_attribute_context,' ') = nvl(a.pricing_attribute_context,' ')
2053 and nvl(b.pricing_attribute,' ') = nvl(a.pricing_attribute,' ' )
2054 and nvl(b.pricing_attr_value_from,' ') = nvl(a.pricing_attr_value_from,' ')
2055 and nvl(b.pricing_attr_value_to,' ') = nvl(a.pricing_attr_value_to,' ')
2056 and b.comparison_operator_code = a.comparison_operator_code --Added for 2128739
2057 and a.list_line_id = c.list_line_id
2058 and ( nvl(trunc(start_date_active),sysdate) BETWEEN nvl(to_date(to_char(p_Start_Date_Active,'DD/MM/YYYY'),'DD/MM/YYYY'), sysdate)
2059 and nvl(to_date(to_char(p_End_Date_Active,'DD/MM/YYYY'),'DD/MM/YYYY'), sysdate) OR
2060 nvl(trunc(end_date_active),sysdate) BETWEEN nvl(to_date(to_char(p_Start_Date_Active,'DD/MM/YYYY'),'DD/MM/YYYY'),sysdate )
2061 and nvl(to_date(to_char(p_End_Date_Active,'DD/MM/YYYY'),'DD/MM/YYYY'), sysdate ) )
2062 group by a.list_line_id, b.list_line_id
2063 having count(b.list_line_id) = ( select count(*)
2064 from qp_pricing_attributes
2065 where list_line_id = b.list_line_id) ;
2066
2067
2068
2069 p_x_rows := sql%rowcount;
2070
2071 if p_x_rows <> 0 then
2072 RETURN FALSE;
2073 else
2074 RETURN TRUE;
2075 end if;
2076
2077
2078 EXCEPTION
2079 WHEN NO_DATA_FOUND THEN
2080 p_x_rows := sql%rowcount;
2081 RETURN TRUE;
2082
2083
2084 WHEN OTHERS THEN
2085 p_x_rows := sql%rowcount;
2086 RETURN FALSE;
2087
2088
2089
2090 END Check_Line_EffDates;
2091
2092
2093 END QP_Validate_pll_pricing_attr;
[Home] [Help]