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