1 PACKAGE BODY QP_Validate_Seg AS
2 /* $Header: QPXLSEGB.pls 120.2 2005/08/03 07:36:49 srashmi noship $ */
3
4 -- Global constant holding the package name
5
6 G_PKG_NAME CONSTANT VARCHAR2(30) := 'QP_Validate_Seg';
7
8 -- Procedure Entity
9
10 PROCEDURE Entity
11 ( x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2
12 , p_SEG_rec IN QP_Attributes_PUB.Seg_Rec_Type
13 , p_old_SEG_rec IN QP_Attributes_PUB.Seg_Rec_Type :=
14 QP_Attributes_PUB.G_MISS_SEG_REC
15 )
16 IS
17 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
18 BEGIN
19
20 -- Check required attributes.
21
22 IF p_SEG_rec.segment_id IS NULL
23 THEN
24
25 l_return_status := FND_API.G_RET_STS_ERROR;
26
27 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR)
28 THEN
29
30 FND_MESSAGE.SET_NAME('QP','QP_ATTRIBUTE_REQUIRED');
31 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','attribute1');
32 OE_MSG_PUB.Add;
33
34 END IF;
35
36 END IF;
37
38 --
39 -- Check rest of required attributes here.
40 --
41
42
43 -- Return Error if a required attribute is missing.
44
45 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
46
47 RAISE FND_API.G_EXC_ERROR;
48
49 END IF;
50
51 --
52 -- Check conditionally required attributes here.
53 --
54
55
56 --
57 -- Validate attribute dependencies here.
58 --
59
60
61 -- Done validating entity
62
63 x_return_status := l_return_status;
64
65 EXCEPTION
66
67 WHEN FND_API.G_EXC_ERROR THEN
68
69 x_return_status := FND_API.G_RET_STS_ERROR;
70
71 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
72
73 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
74
75 WHEN OTHERS THEN
76
77 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
78
79 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
80 THEN
81 OE_MSG_PUB.Add_Exc_Msg
82 ( G_PKG_NAME
83 , 'Entity'
84 );
85 END IF;
86
87 END Entity;
88
89 -- Procedure Attributes
90
91 PROCEDURE Attributes
92 ( x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2
93 , p_SEG_rec IN QP_Attributes_PUB.Seg_Rec_Type
94 , p_old_SEG_rec IN QP_Attributes_PUB.Seg_Rec_Type :=
95 QP_Attributes_PUB.G_MISS_SEG_REC
96 )
97 IS
98 BEGIN
99
100 x_return_status := FND_API.G_RET_STS_SUCCESS;
101
102 -- Validate SEG attributes
103
104 IF p_SEG_rec.availability_in_basic IS NOT NULL AND
105 ( p_SEG_rec.availability_in_basic <>
106 p_old_SEG_rec.availability_in_basic OR
107 p_old_SEG_rec.availability_in_basic IS NULL )
108 THEN
109 IF NOT QP_Validate.Availability_In_Basic(p_SEG_rec.availability_in_basic) THEN
110 x_return_status := FND_API.G_RET_STS_ERROR;
111 END IF;
112 END IF;
113
114 IF p_SEG_rec.created_by IS NOT NULL AND
115 ( p_SEG_rec.created_by <>
116 p_old_SEG_rec.created_by OR
117 p_old_SEG_rec.created_by IS NULL )
118 THEN
119 IF NOT QP_Validate.Created_By(p_SEG_rec.created_by) THEN
120 x_return_status := FND_API.G_RET_STS_ERROR;
121 END IF;
122 END IF;
123
124 IF p_SEG_rec.creation_date IS NOT NULL AND
125 ( p_SEG_rec.creation_date <>
126 p_old_SEG_rec.creation_date OR
127 p_old_SEG_rec.creation_date IS NULL )
128 THEN
129 IF NOT QP_Validate.Creation_Date(p_SEG_rec.creation_date) THEN
130 x_return_status := FND_API.G_RET_STS_ERROR;
131 END IF;
132 END IF;
133
134 IF p_SEG_rec.last_updated_by IS NOT NULL AND
135 ( p_SEG_rec.last_updated_by <>
136 p_old_SEG_rec.last_updated_by OR
137 p_old_SEG_rec.last_updated_by IS NULL )
138 THEN
139 IF NOT QP_Validate.Last_Updated_By(p_SEG_rec.last_updated_by) THEN
140 x_return_status := FND_API.G_RET_STS_ERROR;
141 END IF;
142 END IF;
143
144 IF p_SEG_rec.last_update_date IS NOT NULL AND
145 ( p_SEG_rec.last_update_date <>
146 p_old_SEG_rec.last_update_date OR
147 p_old_SEG_rec.last_update_date IS NULL )
148 THEN
149 IF NOT QP_Validate.Last_Update_Date(p_SEG_rec.last_update_date) THEN
150 x_return_status := FND_API.G_RET_STS_ERROR;
151 END IF;
152 END IF;
153
154 IF p_SEG_rec.last_update_login IS NOT NULL AND
155 ( p_SEG_rec.last_update_login <>
156 p_old_SEG_rec.last_update_login OR
157 p_old_SEG_rec.last_update_login IS NULL )
158 THEN
159 IF NOT QP_Validate.Last_Update_Login(p_SEG_rec.last_update_login) THEN
160 x_return_status := FND_API.G_RET_STS_ERROR;
161 END IF;
162 END IF;
163
164 IF p_SEG_rec.prc_context_id IS NOT NULL AND
165 ( p_SEG_rec.prc_context_id <>
166 p_old_SEG_rec.prc_context_id OR
167 p_old_SEG_rec.prc_context_id IS NULL )
168 THEN
169 IF NOT QP_Validate.Prc_Context(p_SEG_rec.prc_context_id) THEN
170 x_return_status := FND_API.G_RET_STS_ERROR;
171 END IF;
172 END IF;
173
174 IF p_SEG_rec.program_application_id IS NOT NULL AND
175 ( p_SEG_rec.program_application_id <>
176 p_old_SEG_rec.program_application_id OR
177 p_old_SEG_rec.program_application_id IS NULL )
178 THEN
179 IF NOT QP_Validate.Program_Application(p_SEG_rec.program_application_id) THEN
180 x_return_status := FND_API.G_RET_STS_ERROR;
181 END IF;
182 END IF;
183
184 IF p_SEG_rec.program_id IS NOT NULL AND
185 ( p_SEG_rec.program_id <>
186 p_old_SEG_rec.program_id OR
187 p_old_SEG_rec.program_id IS NULL )
188 THEN
189 IF NOT QP_Validate.Program(p_SEG_rec.program_id) THEN
190 x_return_status := FND_API.G_RET_STS_ERROR;
191 END IF;
192 END IF;
193
194 IF p_SEG_rec.program_update_date IS NOT NULL AND
195 ( p_SEG_rec.program_update_date <>
196 p_old_SEG_rec.program_update_date OR
197 p_old_SEG_rec.program_update_date IS NULL )
198 THEN
199 IF NOT QP_Validate.Program_Update_Date(p_SEG_rec.program_update_date) THEN
200 x_return_status := FND_API.G_RET_STS_ERROR;
201 END IF;
202 END IF;
203
204 IF p_SEG_rec.seeded_flag IS NOT NULL AND
205 ( p_SEG_rec.seeded_flag <>
206 p_old_SEG_rec.seeded_flag OR
207 p_old_SEG_rec.seeded_flag IS NULL )
208 THEN
209 IF NOT QP_Validate.Seeded(p_SEG_rec.seeded_flag) THEN
210 x_return_status := FND_API.G_RET_STS_ERROR;
211 END IF;
212 END IF;
213
214 IF p_SEG_rec.seeded_format_type IS NOT NULL AND
215 ( p_SEG_rec.seeded_format_type <>
216 p_old_SEG_rec.seeded_format_type OR
217 p_old_SEG_rec.seeded_format_type IS NULL )
218 THEN
219 IF NOT QP_Validate.Seeded_Format_Type(p_SEG_rec.seeded_format_type) THEN
220 x_return_status := FND_API.G_RET_STS_ERROR;
221 END IF;
222 END IF;
223
224 IF p_SEG_rec.seeded_precedence IS NOT NULL AND
225 ( p_SEG_rec.seeded_precedence <>
226 p_old_SEG_rec.seeded_precedence OR
227 p_old_SEG_rec.seeded_precedence IS NULL )
228 THEN
229 IF NOT QP_Validate.Seeded_Precedence(p_SEG_rec.seeded_precedence) THEN
230 x_return_status := FND_API.G_RET_STS_ERROR;
231 END IF;
232 END IF;
233
234 IF p_SEG_rec.seeded_segment_name IS NOT NULL AND
235 ( p_SEG_rec.seeded_segment_name <>
236 p_old_SEG_rec.seeded_segment_name OR
237 p_old_SEG_rec.seeded_segment_name IS NULL )
238 THEN
239 IF NOT QP_Validate.Seeded_Segment_Name(p_SEG_rec.seeded_segment_name) THEN
240 x_return_status := FND_API.G_RET_STS_ERROR;
241 END IF;
242 END IF;
243
244 IF p_SEG_rec.seeded_valueset_id IS NOT NULL AND
245 ( p_SEG_rec.seeded_valueset_id <>
246 p_old_SEG_rec.seeded_valueset_id OR
247 p_old_SEG_rec.seeded_valueset_id IS NULL )
248 THEN
249 IF NOT QP_Validate.Seeded_Valueset(p_SEG_rec.seeded_valueset_id) THEN
250 x_return_status := FND_API.G_RET_STS_ERROR;
251 END IF;
252 END IF;
253
254 IF p_SEG_rec.segment_code IS NOT NULL AND
255 ( p_SEG_rec.segment_code <>
256 p_old_SEG_rec.segment_code OR
257 p_old_SEG_rec.segment_code IS NULL )
258 THEN
259 IF NOT QP_Validate.Segment_code(p_SEG_rec.segment_code) THEN
260 x_return_status := FND_API.G_RET_STS_ERROR;
261 END IF;
262 END IF;
263
264 IF p_SEG_rec.segment_id IS NOT NULL AND
265 ( p_SEG_rec.segment_id <>
266 p_old_SEG_rec.segment_id OR
267 p_old_SEG_rec.segment_id IS NULL )
268 THEN
269 IF NOT QP_Validate.Segment(p_SEG_rec.segment_id) THEN
270 x_return_status := FND_API.G_RET_STS_ERROR;
271 END IF;
272 END IF;
273 -- Added Application_Id : Abhijit
274 IF p_SEG_rec.application_id IS NOT NULL AND
275 ( p_SEG_rec.application_id <>
276 p_old_SEG_rec.application_id OR
277 p_old_SEG_rec.application_id IS NULL )
278 THEN
279 IF NOT QP_Validate.application_id(p_SEG_rec.application_id) THEN
280 x_return_status := FND_API.G_RET_STS_ERROR;
281 END IF;
282 END IF;
283
284 IF p_SEG_rec.segment_mapping_column IS NOT NULL AND
285 ( p_SEG_rec.segment_mapping_column <>
286 p_old_SEG_rec.segment_mapping_column OR
287 p_old_SEG_rec.segment_mapping_column IS NULL )
288 THEN
289 IF NOT QP_Validate.Segment_Mapping_Column(p_SEG_rec.segment_mapping_column) THEN
290 x_return_status := FND_API.G_RET_STS_ERROR;
291 END IF;
292 END IF;
293
294 IF p_SEG_rec.user_format_type IS NOT NULL AND
295 ( p_SEG_rec.user_format_type <>
296 p_old_SEG_rec.user_format_type OR
297 p_old_SEG_rec.user_format_type IS NULL )
298 THEN
299 IF NOT QP_Validate.User_Format_Type(p_SEG_rec.user_format_type) THEN
300 x_return_status := FND_API.G_RET_STS_ERROR;
301 END IF;
302 END IF;
303
304 IF p_SEG_rec.user_precedence IS NOT NULL AND
305 ( p_SEG_rec.user_precedence <>
306 p_old_SEG_rec.user_precedence OR
307 p_old_SEG_rec.user_precedence IS NULL )
308 THEN
309 IF NOT QP_Validate.User_Precedence(p_SEG_rec.user_precedence) THEN
310 x_return_status := FND_API.G_RET_STS_ERROR;
311 END IF;
312 END IF;
313
314 IF p_SEG_rec.user_segment_name IS NOT NULL AND
315 ( p_SEG_rec.user_segment_name <>
316 p_old_SEG_rec.user_segment_name OR
317 p_old_SEG_rec.user_segment_name IS NULL )
318 THEN
319 IF NOT QP_Validate.User_Segment_Name(p_SEG_rec.user_segment_name) THEN
320 x_return_status := FND_API.G_RET_STS_ERROR;
321 END IF;
322 END IF;
323
324 IF p_SEG_rec.user_valueset_id IS NOT NULL AND
325 ( p_SEG_rec.user_valueset_id <>
326 p_old_SEG_rec.user_valueset_id OR
327 p_old_SEG_rec.user_valueset_id IS NULL )
328 THEN
329 IF NOT QP_Validate.User_Valueset(p_SEG_rec.user_valueset_id) THEN
330 x_return_status := FND_API.G_RET_STS_ERROR;
331 END IF;
332 END IF;
333
334 IF p_SEG_rec.seeded_description IS NOT NULL AND
335 ( p_SEG_rec.seeded_description <>
336 p_old_SEG_rec.seeded_description OR
337 p_old_SEG_rec.seeded_description IS NULL )
338 THEN
339 IF NOT QP_Validate.Seeded_Description_Seg(p_SEG_rec.seeded_description) THEN
340 x_return_status := FND_API.G_RET_STS_ERROR;
341 END IF;
342 END IF;
343
344 IF p_SEG_rec.user_description IS NOT NULL AND
345 ( p_SEG_rec.user_description <>
346 p_old_SEG_rec.user_description OR
347 p_old_SEG_rec.user_description IS NULL )
348 THEN
349 IF NOT QP_Validate.User_Description_Seg(p_SEG_rec.user_description)
350 THEN
351 x_return_status := FND_API.G_RET_STS_ERROR;
352 END IF;
353 END IF;
354
355 IF p_SEG_rec.required_flag IS NOT NULL AND
356 ( p_SEG_rec.required_flag <>
357 p_old_SEG_rec.required_flag OR
358 p_old_SEG_rec.required_flag IS NULL )
359 THEN
360 IF NOT QP_Validate.required_flag(p_SEG_rec.required_flag) THEN
361 x_return_status := FND_API.G_RET_STS_ERROR;
362 END IF;
363 END IF;
364 -- Added for TCA
365 IF p_SEG_rec.party_hierarchy_enabled_flag IS NOT NULL AND
366 ( p_SEG_rec.party_hierarchy_enabled_flag <>
367 p_old_SEG_rec.party_hierarchy_enabled_flag OR
368 p_old_SEG_rec.party_hierarchy_enabled_flag IS NULL )
369 THEN
370 IF NOT QP_Validate.party_hierarchy_enabled_flag(p_SEG_rec.party_hierarchy_enabled_flag) THEN
371 x_return_status := FND_API.G_RET_STS_ERROR;
372 END IF;
373 END IF;
374
378 p_old_SEG_rec.attribute1 IS NULL ))
375 IF (p_SEG_rec.attribute1 IS NOT NULL AND
376 ( p_SEG_rec.attribute1 <>
377 p_old_SEG_rec.attribute1 OR
379 OR (p_SEG_rec.attribute10 IS NOT NULL AND
380 ( p_SEG_rec.attribute10 <>
381 p_old_SEG_rec.attribute10 OR
382 p_old_SEG_rec.attribute10 IS NULL ))
383 OR (p_SEG_rec.attribute11 IS NOT NULL AND
384 ( p_SEG_rec.attribute11 <>
385 p_old_SEG_rec.attribute11 OR
386 p_old_SEG_rec.attribute11 IS NULL ))
387 OR (p_SEG_rec.attribute12 IS NOT NULL AND
388 ( p_SEG_rec.attribute12 <>
389 p_old_SEG_rec.attribute12 OR
390 p_old_SEG_rec.attribute12 IS NULL ))
391 OR (p_SEG_rec.attribute13 IS NOT NULL AND
392 ( p_SEG_rec.attribute13 <>
393 p_old_SEG_rec.attribute13 OR
394 p_old_SEG_rec.attribute13 IS NULL ))
395 OR (p_SEG_rec.attribute14 IS NOT NULL AND
396 ( p_SEG_rec.attribute14 <>
397 p_old_SEG_rec.attribute14 OR
398 p_old_SEG_rec.attribute14 IS NULL ))
399 OR (p_SEG_rec.attribute15 IS NOT NULL AND
400 ( p_SEG_rec.attribute15 <>
401 p_old_SEG_rec.attribute15 OR
402 p_old_SEG_rec.attribute15 IS NULL ))
403 OR (p_SEG_rec.attribute2 IS NOT NULL AND
404 ( p_SEG_rec.attribute2 <>
405 p_old_SEG_rec.attribute2 OR
406 p_old_SEG_rec.attribute2 IS NULL ))
407 OR (p_SEG_rec.attribute3 IS NOT NULL AND
408 ( p_SEG_rec.attribute3 <>
409 p_old_SEG_rec.attribute3 OR
410 p_old_SEG_rec.attribute3 IS NULL ))
411 OR (p_SEG_rec.attribute4 IS NOT NULL AND
412 ( p_SEG_rec.attribute4 <>
413 p_old_SEG_rec.attribute4 OR
414 p_old_SEG_rec.attribute4 IS NULL ))
415 OR (p_SEG_rec.attribute5 IS NOT NULL AND
416 ( p_SEG_rec.attribute5 <>
417 p_old_SEG_rec.attribute5 OR
418 p_old_SEG_rec.attribute5 IS NULL ))
419 OR (p_SEG_rec.attribute6 IS NOT NULL AND
420 ( p_SEG_rec.attribute6 <>
421 p_old_SEG_rec.attribute6 OR
422 p_old_SEG_rec.attribute6 IS NULL ))
423 OR (p_SEG_rec.attribute7 IS NOT NULL AND
424 ( p_SEG_rec.attribute7 <>
425 p_old_SEG_rec.attribute7 OR
426 p_old_SEG_rec.attribute7 IS NULL ))
427 OR (p_SEG_rec.attribute8 IS NOT NULL AND
428 ( p_SEG_rec.attribute8 <>
429 p_old_SEG_rec.attribute8 OR
430 p_old_SEG_rec.attribute8 IS NULL ))
431 OR (p_SEG_rec.attribute9 IS NOT NULL AND
432 ( p_SEG_rec.attribute9 <>
433 p_old_SEG_rec.attribute9 OR
434 p_old_SEG_rec.attribute9 IS NULL ))
435 OR (p_SEG_rec.context IS NOT NULL AND
436 ( p_SEG_rec.context <>
437 p_old_SEG_rec.context OR
438 p_old_SEG_rec.context IS NULL ))
439 THEN
440
441 -- These calls are temporarily commented out
442
443 /*
444 FND_FLEX_DESC_VAL.Set_Column_Value
445 ( column_name => 'ATTRIBUTE1'
446 , column_value => p_SEG_rec.attribute1
447 );
448 FND_FLEX_DESC_VAL.Set_Column_Value
449 ( column_name => 'ATTRIBUTE10'
450 , column_value => p_SEG_rec.attribute10
451 );
452 FND_FLEX_DESC_VAL.Set_Column_Value
453 ( column_name => 'ATTRIBUTE11'
454 , column_value => p_SEG_rec.attribute11
455 );
456 FND_FLEX_DESC_VAL.Set_Column_Value
457 ( column_name => 'ATTRIBUTE12'
458 , column_value => p_SEG_rec.attribute12
459 );
460 FND_FLEX_DESC_VAL.Set_Column_Value
461 ( column_name => 'ATTRIBUTE13'
462 , column_value => p_SEG_rec.attribute13
463 );
464 FND_FLEX_DESC_VAL.Set_Column_Value
465 ( column_name => 'ATTRIBUTE14'
466 , column_value => p_SEG_rec.attribute14
467 );
468 FND_FLEX_DESC_VAL.Set_Column_Value
469 ( column_name => 'ATTRIBUTE15'
470 , column_value => p_SEG_rec.attribute15
471 );
472 FND_FLEX_DESC_VAL.Set_Column_Value
473 ( column_name => 'ATTRIBUTE2'
474 , column_value => p_SEG_rec.attribute2
475 );
476 FND_FLEX_DESC_VAL.Set_Column_Value
477 ( column_name => 'ATTRIBUTE3'
478 , column_value => p_SEG_rec.attribute3
479 );
480 FND_FLEX_DESC_VAL.Set_Column_Value
481 ( column_name => 'ATTRIBUTE4'
482 , column_value => p_SEG_rec.attribute4
483 );
484 FND_FLEX_DESC_VAL.Set_Column_Value
485 ( column_name => 'ATTRIBUTE5'
486 , column_value => p_SEG_rec.attribute5
487 );
488 FND_FLEX_DESC_VAL.Set_Column_Value
489 ( column_name => 'ATTRIBUTE6'
490 , column_value => p_SEG_rec.attribute6
491 );
495 );
492 FND_FLEX_DESC_VAL.Set_Column_Value
493 ( column_name => 'ATTRIBUTE7'
494 , column_value => p_SEG_rec.attribute7
496 FND_FLEX_DESC_VAL.Set_Column_Value
497 ( column_name => 'ATTRIBUTE8'
498 , column_value => p_SEG_rec.attribute8
499 );
500 FND_FLEX_DESC_VAL.Set_Column_Value
501 ( column_name => 'ATTRIBUTE9'
502 , column_value => p_SEG_rec.attribute9
503 );
504 FND_FLEX_DESC_VAL.Set_Column_Value
505 ( column_name => 'CONTEXT'
506 , column_value => p_SEG_rec.context
507 );
508 */
509
510 -- Validate descriptive flexfield.
511
512 IF NOT QP_Validate.Desc_Flex( 'SEG' ) THEN
513 x_return_status := FND_API.G_RET_STS_ERROR;
514 END IF;
515
516 END IF;
517
518 -- Done validating attributes
519
520 EXCEPTION
521
522 WHEN FND_API.G_EXC_ERROR THEN
523
524 x_return_status := FND_API.G_RET_STS_ERROR;
525
526 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
527
528 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
529
530 WHEN OTHERS THEN
531
532 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
533
534 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
535 THEN
536 OE_MSG_PUB.Add_Exc_Msg
537 ( G_PKG_NAME
538 , 'Attributes'
539 );
540 END IF;
541
542 END Attributes;
543
544 -- Procedure Entity_Delete
545
546 PROCEDURE Entity_Delete
547 ( x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2
548 , p_SEG_rec IN QP_Attributes_PUB.Seg_Rec_Type
549 )
550 IS
551 l_context_code varchar2(30);
552 is_attribute_used varchar2(1) := 'N';
553 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
554 BEGIN
555 -- Validate entity delete.
556 begin
557 select prc_context_code
558 into l_context_code
559 from qp_prc_contexts_b
560 where prc_context_id = p_SEG_rec.prc_context_id;
561 exception
562 when no_data_found then
563 x_return_status := l_return_status;
564 return;
565 end;
566 --
567 if is_attribute_used = 'N' then
568 if l_context_code is not null and p_SEG_rec.segment_mapping_column is not null then
569 begin
570 SELECT 'Y' into is_attribute_used
571 from qp_pricing_attributes
572 where product_attribute_context = l_context_code and
573 product_attribute = p_SEG_rec.segment_mapping_column and
574 product_attribute_context is not null and
575 product_attribute is not null and
576 rownum =1
577 UNION
578 SELECT 'Y'
579 from qp_pricing_attributes
580 where pricing_attribute_context = l_context_code and
581 pricing_attribute = p_SEG_rec.segment_mapping_column and
582 pricing_attribute_context is not null and
583 pricing_attribute is not null and
584 rownum = 1;
585 exception
586 when no_data_found then
587 null;
588 end;
589 end if;
590 end if;
591 --
592 if is_attribute_used = 'N' then
593 begin
594 select 'Y'
595 into is_attribute_used
596 from qp_qualifiers
597 where qualifier_context = l_context_code and
598 qualifier_attribute = p_SEG_rec.segment_mapping_column and
599 rownum = 1;
600 exception
601 when no_data_found then
602 null;
603 end;
604 end if;
605 --
606 if is_attribute_used = 'N' then
607 begin
608 select 'Y'
609 into is_attribute_used
610 from qp_limits
611 where (nvl(multival_attr1_context,'x') = nvl(l_context_code,'y') and
612 nvl(multival_attribute1,'x') = nvl(p_SEG_rec.segment_mapping_column,'y')) or
613 (nvl(multival_attr2_context,'x') = nvl(l_context_code,'y') and
614 nvl(multival_attribute2,'x') = nvl(p_SEG_rec.segment_mapping_column,'y')) and
615 rownum = 1;
616 exception
617 when no_data_found then
618 null;
619 end;
620 end if;
621 --
622 if is_attribute_used = 'N' then
623 begin
624 select 'Y'
625 into is_attribute_used
626 from qp_limit_attributes
627 where limit_attribute_context = l_context_code and
628 limit_attribute = p_SEG_rec.segment_mapping_column and
629 rownum = 1;
630 exception
631 when no_data_found then
632 null;
633 end;
634 end if;
635 --
636 if is_attribute_used = 'Y' then
637 l_return_status := FND_API.G_RET_STS_ERROR;
638 FND_MESSAGE.SET_NAME('QP','QP_CANNOT_DELETE_SEGMENT');
639 OE_MSG_PUB.Add;
640 raise fnd_api.g_exc_error;
641 end if;
642 --
643 x_return_status := l_return_status;
644
645 EXCEPTION
646
647 WHEN FND_API.G_EXC_ERROR THEN
648
649 x_return_status := FND_API.G_RET_STS_ERROR;
650
651 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
652
653 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
654
655 WHEN OTHERS THEN
656
657 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
658
659 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
660 THEN
661 OE_MSG_PUB.Add_Exc_Msg
662 ( G_PKG_NAME
663 , 'Entity_Delete'
664 );
665 END IF;
666
667 END Entity_Delete;
668
669 END QP_Validate_Seg;
[Home] [Help]