[Home] [Help]
PACKAGE BODY: APPS.QP_QUALIFIER_RULES_PVT
Source
1 PACKAGE BODY QP_Qualifier_Rules_PVT AS
2 /* $Header: QPXVQRQB.pls 120.2 2005/07/06 02:59:19 appldev ship $ */
3
4 -- Global constant holding the package name
5
6 G_PKG_NAME CONSTANT VARCHAR2(30) := 'QP_Qualifier_Rules_PVT';
7
8 -- Qualifier_Rules
9
10 PROCEDURE Qualifier_Rules
11 ( p_validation_level IN NUMBER
12 , p_control_rec IN QP_GLOBALS.Control_Rec_Type
13 , p_QUALIFIER_RULES_rec IN QP_Qualifier_Rules_PUB.Qualifier_Rules_Rec_Type
14 , p_old_QUALIFIER_RULES_rec IN QP_Qualifier_Rules_PUB.Qualifier_Rules_Rec_Type
15 , x_QUALIFIER_RULES_rec OUT NOCOPY /* file.sql.39 change */ QP_Qualifier_Rules_PUB.Qualifier_Rules_Rec_Type
16 , x_old_QUALIFIER_RULES_rec OUT NOCOPY /* file.sql.39 change */ QP_Qualifier_Rules_PUB.Qualifier_Rules_Rec_Type
17 )
18 IS
19 l_return_status VARCHAR2(1);
20 l_control_rec QP_GLOBALS.Control_Rec_Type;
21 l_QUALIFIER_RULES_rec QP_Qualifier_Rules_PUB.Qualifier_Rules_Rec_Type := p_QUALIFIER_RULES_rec;
22 l_p_QUALIFIER_RULES_rec QP_Qualifier_Rules_PUB.Qualifier_Rules_Rec_Type := p_QUALIFIER_RULES_rec;
23 l_old_QUALIFIER_RULES_rec QP_Qualifier_Rules_PUB.Qualifier_Rules_Rec_Type := p_old_QUALIFIER_RULES_rec;
24 BEGIN
25
26 oe_debug_pub.add('entering qualifier rules' );
27 oe_debug_pub.add('operation is '||nvl(l_QUALIFIER_RULES_rec.operation,'null') );
28
29 -- Load API control record
30
31 l_control_rec := QP_GLOBALS.Init_Control_Rec
32 ( p_operation => l_QUALIFIER_RULES_rec.operation
33 , p_control_rec => p_control_rec
34 );
35
36
37
38 -- Set record return status.
39
40 l_QUALIFIER_RULES_rec.return_status := FND_API.G_RET_STS_SUCCESS;
41
42 -- Prepare record.
43
44 IF l_QUALIFIER_RULES_rec.operation = QP_GLOBALS.G_OPR_CREATE THEN
45
46 l_QUALIFIER_RULES_rec.db_flag := FND_API.G_FALSE;
47
48 -- Set missing old record elements to NULL.
49
50 --oe_debug_pub.add('setting missing old record to null');
51
52 l_old_QUALIFIER_RULES_rec :=
53 QP_Qualifier_Rules_Util.Convert_Miss_To_Null (l_old_QUALIFIER_RULES_rec);
54
55 ELSIF l_QUALIFIER_RULES_rec.operation = QP_GLOBALS.G_OPR_UPDATE
56 OR l_QUALIFIER_RULES_rec.operation = QP_GLOBALS.G_OPR_DELETE
57 THEN
58
59 l_QUALIFIER_RULES_rec.db_flag := FND_API.G_TRUE;
60
61 -- Query Old if missing
62
63 IF l_old_QUALIFIER_RULES_rec.qualifier_rule_id = FND_API.G_MISS_NUM
64 THEN
65
66 l_old_QUALIFIER_RULES_rec := QP_Qualifier_Rules_Util.Query_Row
67 ( p_qualifier_rule_id => l_QUALIFIER_RULES_rec.qualifier_rule_id
68 );
69
70 ELSE
71
72 -- Set missing old record elements to NULL.
73
74 l_old_QUALIFIER_RULES_rec :=
75 QP_Qualifier_Rules_Util.Convert_Miss_To_Null (l_old_QUALIFIER_RULES_rec);
76
77 END IF;
78
79 -- Complete new record from old
80
81 l_QUALIFIER_RULES_rec := QP_Qualifier_Rules_Util.Complete_Record
82 ( p_QUALIFIER_RULES_rec => l_QUALIFIER_RULES_rec
83 , p_old_QUALIFIER_RULES_rec => l_old_QUALIFIER_RULES_rec
84 );
85
86 END IF;
87
88 -- Attribute level validation.
89
90 -- added on 30-nov-99 by svdeshmu to avoid generation of sequence
91 -- number for qualifier rule id when lines are added for existing
92 --qualifier rule
93
94 IF l_QUALIFIER_RULES_rec.operation = QP_GLOBALS.G_OPR_CREATE OR
95 l_QUALIFIER_RULES_rec.operation = QP_GLOBALS.G_OPR_UPDATE OR
96 l_QUALIFIER_RULES_rec.operation = QP_GLOBALS.G_OPR_DELETE THEN
97
98 IF l_control_rec.default_attributes
99 OR l_control_rec.change_attributes
100 THEN
101
102 IF p_validation_level > FND_API.G_VALID_LEVEL_NONE THEN
103
104 --oe_debug_pub.add('executing QP_validate.attributes');
105 --oe_debug_pub.add('name '||l_QUALIFIER_RULES_rec.name);
106 --oe_debug_pub.add('desc '||l_QUALIFIER_RULES_rec.description);
107
108
109
110 --oe_debug_pub.add('from old record');
111 --oe_debug_pub.add('name '||l_old_QUALIFIER_RULES_rec.name);
112 --oe_debug_pub.add('desc '||l_old_QUALIFIER_RULES_rec.description);
113
114 --dbms_output.put_line('calling validate attributes');
115
116 QP_Validate_Qualifier_Rules.Attributes
117 ( x_return_status => l_return_status
118 , p_QUALIFIER_RULES_rec => l_QUALIFIER_RULES_rec
119 , p_old_QUALIFIER_RULES_rec => l_old_QUALIFIER_RULES_rec
120 );
121
122
123
124 --oe_debug_pub.add('after executing QP_validate.attributes ,status is '|| l_return_status);
125 --dbms_output.put_line('after executing QP_validate.attributes ,status is '|| l_return_status);
126
127
128 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
129 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
130 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
131 RAISE FND_API.G_EXC_ERROR;
132 END IF;
133
134 END IF;
135
136 END IF;
137
138 -- Clear dependent attributes.
139
140 IF l_control_rec.change_attributes THEN
141 l_p_QUALIFIER_RULES_rec := l_QUALIFIER_RULES_rec; --added for nocopy hint
142 QP_Qualifier_Rules_Util.Clear_Dependent_Attr
143 ( p_QUALIFIER_RULES_rec => l_p_QUALIFIER_RULES_rec
144 , p_old_QUALIFIER_RULES_rec => l_old_QUALIFIER_RULES_rec
145 , x_QUALIFIER_RULES_rec => l_QUALIFIER_RULES_rec
146 );
147
148 END IF;
149
150 -- Default missing attributes
151
152
153
154 IF l_control_rec.default_attributes
155 OR l_control_rec.change_attributes
156 THEN
157
158
159 oe_debug_pub.add(' executing QP_default_qualifier_rules.attributes ');
160 --dbms_output.put_line('calling default qualifier_rule attribute');
161 l_p_QUALIFIER_RULES_rec := l_QUALIFIER_RULES_rec; --added for nocopy hint
162 QP_Default_Qualifier_Rules.Attributes
163 ( p_QUALIFIER_RULES_rec => l_p_QUALIFIER_RULES_rec
164 , x_QUALIFIER_RULES_rec => l_QUALIFIER_RULES_rec
165 );
166
167
168
169
170 END IF;
171
172 -- Apply attribute changes
173
174 IF l_control_rec.default_attributes
175 OR l_control_rec.change_attributes
176 THEN
177
178 --dbms_output.put_line('calling _rule apply changes attribute');
179 l_p_QUALIFIER_RULES_rec := l_QUALIFIER_RULES_rec; --added for nocopy hint
180 QP_Qualifier_Rules_Util.Apply_Attribute_Changes
181 ( p_QUALIFIER_RULES_rec => l_p_QUALIFIER_RULES_rec
182 , p_old_QUALIFIER_RULES_rec => l_old_QUALIFIER_RULES_rec
183 , x_QUALIFIER_RULES_rec => l_QUALIFIER_RULES_rec
184 );
185
186 END IF;
187
188 -- Entity level validation.
189
190 IF l_control_rec.validate_entity THEN
191
192 IF l_QUALIFIER_RULES_rec.operation = QP_GLOBALS.G_OPR_DELETE THEN
193
194 QP_Validate_Qualifier_Rules.Entity_Delete
195 ( x_return_status => l_return_status
196 , p_QUALIFIER_RULES_rec => l_QUALIFIER_RULES_rec
197 );
198
199 ELSE
200
201 --dbms_output.put_line('entity validation');
202
203 QP_Validate_Qualifier_Rules.Entity
204 ( x_return_status => l_return_status
205 , p_QUALIFIER_RULES_rec => l_QUALIFIER_RULES_rec
206 , p_old_QUALIFIER_RULES_rec => l_old_QUALIFIER_RULES_rec
207 );
208
209 END IF;
210
211 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
212 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
213 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
214 RAISE FND_API.G_EXC_ERROR;
215 END IF;
216
217 END IF;
218
219 -- Step 4. Write to DB
220
221 IF l_control_rec.write_to_db THEN
222
223 IF l_QUALIFIER_RULES_rec.operation = QP_GLOBALS.G_OPR_DELETE THEN
224
225 QP_Qualifier_Rules_Util.Delete_Row
226 ( p_qualifier_rule_id => l_QUALIFIER_RULES_rec.qualifier_rule_id
227 );
228
229 ELSE
230
231 -- Get Who Information
232
233 l_QUALIFIER_RULES_rec.last_update_date := SYSDATE;
234 l_QUALIFIER_RULES_rec.last_updated_by := FND_GLOBAL.USER_ID;
235 l_QUALIFIER_RULES_rec.last_update_login := FND_GLOBAL.LOGIN_ID;
236
237 IF l_QUALIFIER_RULES_rec.operation = QP_GLOBALS.G_OPR_UPDATE THEN
238
239 QP_Qualifier_Rules_Util.Update_Row (l_QUALIFIER_RULES_rec);
240
241 ELSIF l_QUALIFIER_RULES_rec.operation = QP_GLOBALS.G_OPR_CREATE THEN
242
243 l_QUALIFIER_RULES_rec.creation_date := SYSDATE;
244 l_QUALIFIER_RULES_rec.created_by := FND_GLOBAL.USER_ID;
245
246 --dbms_output.put_line('calling _rule insert row');
247 oe_debug_pub.add('calling insert row');
248 QP_Qualifier_Rules_Util.Insert_Row (l_QUALIFIER_RULES_rec);
249
250 END IF;
251
252 END IF;
253
254 END IF;
255
256 END IF; --added for the if added above on 30-nov-99
257
258 -- Load OUT parameters
259
260 x_QUALIFIER_RULES_rec := l_QUALIFIER_RULES_rec;
261 x_old_QUALIFIER_RULES_rec := l_old_QUALIFIER_RULES_rec;
262
263 oe_debug_pub.add('leavingqualfiier rule pricate');
264
265
266 EXCEPTION
267
268 WHEN FND_API.G_EXC_ERROR THEN
269
270 l_QUALIFIER_RULES_rec.return_status := FND_API.G_RET_STS_ERROR;
271 x_QUALIFIER_RULES_rec := l_QUALIFIER_RULES_rec;
272 x_old_QUALIFIER_RULES_rec := l_old_QUALIFIER_RULES_rec;
273
274 -- mkarya If process_qualifier_rules has been called from public package, then ONLY
275 -- call qp_delayed_requests_pvt.delete_reqs_for_deleted_entity
276 if l_control_rec.called_from_ui = 'N' then
277 qp_delayed_requests_pvt.delete_reqs_for_deleted_entity
278 (p_entity_code => QP_GLOBALS.G_ENTITY_QUALIFIER_RULES,
279 p_entity_id => l_QUALIFIER_RULES_rec.qualifier_rule_id,
280 x_return_status => l_return_status);
281 end if;
282
283 RAISE;
284
285
286 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
287
288 l_QUALIFIER_RULES_rec.return_status := FND_API.G_RET_STS_UNEXP_ERROR;
289 x_QUALIFIER_RULES_rec := l_QUALIFIER_RULES_rec;
290 x_old_QUALIFIER_RULES_rec := l_old_QUALIFIER_RULES_rec;
291
292 -- mkarya If process_qualifier_rules has been called from public package, then ONLY
293 -- call qp_delayed_requests_pvt.delete_reqs_for_deleted_entity
294 if l_control_rec.called_from_ui = 'N' then
295 qp_delayed_requests_pvt.delete_reqs_for_deleted_entity
296 (p_entity_code => QP_GLOBALS.G_ENTITY_QUALIFIER_RULES,
297 p_entity_id => l_QUALIFIER_RULES_rec.qualifier_rule_id,
298 x_return_status => l_return_status);
299 end if;
300
301 RAISE;
302
303 WHEN OTHERS THEN
304
305 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
306 THEN
307 OE_MSG_PUB.Add_Exc_Msg
308 ( G_PKG_NAME
309 , 'Qualifier_Rules'
310 );
311 END IF;
312
313 l_QUALIFIER_RULES_rec.return_status := FND_API.G_RET_STS_UNEXP_ERROR;
314 x_QUALIFIER_RULES_rec := l_QUALIFIER_RULES_rec;
315 x_old_QUALIFIER_RULES_rec := l_old_QUALIFIER_RULES_rec;
316
317 -- mkarya If process_qualifier_rules has been called from public package, then ONLY
318 -- call qp_delayed_requests_pvt.delete_reqs_for_deleted_entity
319 if l_control_rec.called_from_ui = 'N' then
320 qp_delayed_requests_pvt.delete_reqs_for_deleted_entity
321 (p_entity_code => QP_GLOBALS.G_ENTITY_QUALIFIER_RULES,
322 p_entity_id => l_QUALIFIER_RULES_rec.qualifier_rule_id,
323 x_return_status => l_return_status);
324 end if;
325
326 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
327
328 END Qualifier_Rules;
329
330 -- Qualifierss
331
332 PROCEDURE Qualifierss
333 ( p_validation_level IN NUMBER
334 , p_control_rec IN QP_GLOBALS.Control_Rec_Type
335 , p_QUALIFIERS_tbl IN QP_Qualifier_Rules_PUB.Qualifiers_Tbl_Type
336 , p_old_QUALIFIERS_tbl IN QP_Qualifier_Rules_PUB.Qualifiers_Tbl_Type
337 , x_QUALIFIERS_tbl OUT NOCOPY /* file.sql.39 change */ QP_Qualifier_Rules_PUB.Qualifiers_Tbl_Type
338 , x_old_QUALIFIERS_tbl OUT NOCOPY /* file.sql.39 change */ QP_Qualifier_Rules_PUB.Qualifiers_Tbl_Type
339 )
340 IS
341 l_return_status VARCHAR2(1);
342 l_control_rec QP_GLOBALS.Control_Rec_Type;
343 l_QUALIFIERS_rec QP_Qualifier_Rules_PUB.Qualifiers_Rec_Type;
344 l_p_QUALIFIERS_rec QP_Qualifier_Rules_PUB.Qualifiers_Rec_Type;
345 l_QUALIFIERS_tbl QP_Qualifier_Rules_PUB.Qualifiers_Tbl_Type;
346 l_old_QUALIFIERS_rec QP_Qualifier_Rules_PUB.Qualifiers_Rec_Type;
347 l_old_QUALIFIERS_tbl QP_Qualifier_Rules_PUB.Qualifiers_Tbl_Type;
348 BEGIN
349
350 -- Init local table variables.
351
352 l_QUALIFIERS_tbl := p_QUALIFIERS_tbl;
353 l_old_QUALIFIERS_tbl := p_old_QUALIFIERS_tbl;
354
355
356 --dbms_output.put_line('entering in loop');
357 oe_debug_pub.add('entering in qualifierss');
358 oe_debug_pub.add('entering in loop with '||l_QUALIFIERS_tbl.COUNT);
359
360
361 FOR I IN 1..l_QUALIFIERS_tbl.COUNT LOOP
362 BEGIN
363
364 -- Load local records.
365
366 --dbms_output.put_line('executing loop for '||I);
367
368
369
370 l_QUALIFIERS_rec := l_QUALIFIERS_tbl(I);
371
372 IF l_old_QUALIFIERS_tbl.EXISTS(I) THEN
373 l_old_QUALIFIERS_rec := l_old_QUALIFIERS_tbl(I);
374 ELSE
375 l_old_QUALIFIERS_rec := QP_Qualifier_Rules_PUB.G_MISS_QUALIFIERS_REC;
376 END IF;
377
378 -- Load API control record
379
380 oe_debug_pub.add('qualifier rule is = '||l_QUALIFIERS_rec.qualifier_rule_id);
381 oe_debug_pub.add('qualifier value is = '||l_QUALIFIERS_rec.qualifier_attr_value);
382 oe_debug_pub.add('qualifier value is = '||l_QUALIFIERS_rec.qualifier_attr_value_to);
383
384 --dbms_output.put_line('operation is '|| l_QUALIFIERS_rec.operation);
385
386
387
388 l_control_rec := QP_GLOBALS.Init_Control_Rec
389 ( p_operation => l_QUALIFIERS_rec.operation
390 , p_control_rec => p_control_rec
391 );
392
393 -- Set record return status.
394
395 l_QUALIFIERS_rec.return_status := FND_API.G_RET_STS_SUCCESS;
396
397 -- Prepare record.
398
399 IF l_QUALIFIERS_rec.operation = QP_GLOBALS.G_OPR_CREATE THEN
400
401 l_QUALIFIERS_rec.db_flag := FND_API.G_FALSE;
402
403 -- Set missing old record elements to NULL.
404
405 --dbms_output.put_line('caliing miss to null');
406 oe_debug_pub.add('caliing miss to null');
407
408
409
410
411
412 l_old_QUALIFIERS_rec :=
413 QP_Qualifiers_Util.Convert_Miss_To_Null (l_old_QUALIFIERS_rec);
414
415 ELSIF l_QUALIFIERS_rec.operation = QP_GLOBALS.G_OPR_UPDATE
416 OR l_QUALIFIERS_rec.operation = QP_GLOBALS.G_OPR_DELETE
417 THEN
418
419 l_QUALIFIERS_rec.db_flag := FND_API.G_TRUE;
420
421 -- Query Old if missing
422
423 IF l_old_QUALIFIERS_rec.qualifier_id = FND_API.G_MISS_NUM
424 THEN
425
426 l_old_QUALIFIERS_rec := QP_Qualifiers_Util.Query_Row
427 ( p_qualifier_id => l_QUALIFIERS_rec.qualifier_id
428 );
429
430 ELSE
431
432 -- Set missing old record elements to NULL.
433
434 l_old_QUALIFIERS_rec :=
435 QP_Qualifiers_Util.Convert_Miss_To_Null (l_old_QUALIFIERS_rec);
436
437 END IF;
438
439 -- Complete new record from old
440 --dbms_output.put_line('completing record');
441 oe_debug_pub.add('completing new reocrd');
442
443 l_QUALIFIERS_rec := QP_Qualifiers_Util.Complete_Record
444 ( p_QUALIFIERS_rec => l_QUALIFIERS_rec
445 , p_old_QUALIFIERS_rec => l_old_QUALIFIERS_rec
446 );
447
448 END IF;
449
450 -- Attribute level validation.
451
452 IF l_control_rec.default_attributes
453 OR l_control_rec.change_attributes
454 THEN
455
456 IF p_validation_level > FND_API.G_VALID_LEVEL_NONE THEN
457
458 --dbms_output.put_line('validating record');
459 oe_debug_pub.add('validating record by attributes');
460
461 QP_Validate_Qualifiers.Attributes
462 ( x_return_status => l_return_status
463 , p_QUALIFIERS_rec => l_QUALIFIERS_rec
464 , p_old_QUALIFIERS_rec => l_old_QUALIFIERS_rec
465 );
466
467 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
468 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
469 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
470 RAISE FND_API.G_EXC_ERROR;
471 END IF;
472
473 END IF;
474
475 END IF;
476
477 -- Clear dependent attributes.
478
479 IF l_control_rec.change_attributes THEN
480
481 --dbms_output.put_line('clearing dependent record');
482
483 oe_debug_pub.add('clearing dependent records');
484
485 l_p_QUALIFIERS_rec := l_QUALIFIERS_rec; -- added for nocopy hint
486 QP_Qualifiers_Util.Clear_Dependent_Attr
487 ( p_QUALIFIERS_rec => l_p_QUALIFIERS_rec
488 , p_old_QUALIFIERS_rec => l_old_QUALIFIERS_rec
489 , x_QUALIFIERS_rec => l_QUALIFIERS_rec
490 );
491
492 END IF;
493
494 -- Default missing attributes
495
496 IF l_control_rec.default_attributes
497 OR l_control_rec.change_attributes
498 THEN
499
500 --dbms_output.put_line('defaulting record');
501 oe_debug_pub.add('defaulting record');
502
503
504 l_p_QUALIFIERS_rec := l_QUALIFIERS_rec; -- added for nocopy hint
505 QP_Default_Qualifiers.Attributes
506 ( p_QUALIFIERS_rec => l_p_QUALIFIERS_rec
507 , x_QUALIFIERS_rec => l_QUALIFIERS_rec
508 );
509
510 END IF;
511
512 -- Apply attribute changes
513
514 IF l_control_rec.default_attributes
515 OR l_control_rec.change_attributes
516 THEN
517
518 --dbms_output.put_line('applying change attribute ');
519 oe_debug_pub.add('applying change attribute ');
520
521
522 l_p_QUALIFIERS_rec := l_QUALIFIERS_rec; -- added for nocopy hint
523 QP_Qualifiers_Util.Apply_Attribute_Changes
524 ( p_QUALIFIERS_rec => l_p_QUALIFIERS_rec
525 , p_old_QUALIFIERS_rec => l_old_QUALIFIERS_rec
526 , x_QUALIFIERS_rec => l_QUALIFIERS_rec
527 );
528
529 END IF;
530
531 -- Entity level validation.
532
533 IF l_control_rec.validate_entity THEN
534
535 oe_debug_pub.add('checking entity validation condition ');
536 oe_debug_pub.add('operation is '|| l_QUALIFIERS_rec.operation);
537 IF l_QUALIFIERS_rec.operation = QP_GLOBALS.G_OPR_DELETE THEN
538
539 QP_Validate_Qualifiers.Entity_Delete
540 ( x_return_status => l_return_status
541 , p_QUALIFIERS_rec => l_QUALIFIERS_rec
542 );
543
544 ELSE
545
546 --dbms_output.put_line('qualifiers entity validation ');
547 oe_debug_pub.add('qualifiers entity validation ');
548 QP_Validate_Qualifiers.Entity
549 ( x_return_status => l_return_status
550 , p_QUALIFIERS_rec => l_QUALIFIERS_rec
551 , p_old_QUALIFIERS_rec => l_old_QUALIFIERS_rec
552 );
553
554 --dbms_output.put_line('qualifiers entity validation status '||l_return_status);
555 END IF;
556
557 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
558 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
559 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
560 RAISE FND_API.G_EXC_ERROR;
561 END IF;
562
563 END IF;
564
565 -- Step 3.5. Perform action which need to be performed before
566 -- writing to the DB (like Scheduling).
567 --added by spgopal for list_qual_ind delayed req
568
569 IF l_control_rec.write_to_db THEN
570 l_p_QUALIFIERS_rec := l_QUALIFIERS_rec; -- added for nocopy hint
571 QP_QUALIFIERS_UTIL.PRE_WRITE_PROCESS
572 ( p_QUALIFIERS_rec => l_p_QUALIFIERS_rec
573 , p_old_QUALIFIERS_rec => l_old_QUALIFIERS_rec
574 , x_QUALIFIERS_rec => l_QUALIFIERS_rec);
575
576 END IF;
577
578 -- Step 4. Write to DB
579
580 IF l_control_rec.write_to_db THEN
581
582 IF l_QUALIFIERS_rec.operation = QP_GLOBALS.G_OPR_DELETE THEN
583
584 QP_Qualifiers_Util.Delete_Row
585 ( p_qualifier_id => l_QUALIFIERS_rec.qualifier_id
586 );
587
588 ELSE
589
590 -- Get Who Information
591
592 l_QUALIFIERS_rec.last_update_date := SYSDATE;
593 l_QUALIFIERS_rec.last_updated_by := FND_GLOBAL.USER_ID;
594 l_QUALIFIERS_rec.last_update_login := FND_GLOBAL.LOGIN_ID;
595
596 IF l_QUALIFIERS_rec.operation = QP_GLOBALS.G_OPR_UPDATE THEN
597
598 QP_Qualifiers_Util.Update_Row (l_QUALIFIERS_rec);
599
600 ELSIF l_QUALIFIERS_rec.operation = QP_GLOBALS.G_OPR_CREATE THEN
601
602 l_QUALIFIERS_rec.creation_date := SYSDATE;
603 l_QUALIFIERS_rec.created_by := FND_GLOBAL.USER_ID;
604
605
606 --dbms_output.put_line('claiing insert');
607 oe_debug_pub.add('calling insert for qualifiers');
608
609 QP_Qualifiers_Util.Insert_Row (l_QUALIFIERS_rec);
610
611 END IF;
612
613 END IF;
614
615 END IF;
616
617 -- Load tables.
618
619 l_QUALIFIERS_tbl(I) := l_QUALIFIERS_rec;
620 l_old_QUALIFIERS_tbl(I) := l_old_QUALIFIERS_rec;
621
622 -- For loop exception handler.
623
624
625 EXCEPTION
626
627 WHEN FND_API.G_EXC_ERROR THEN
628
629 l_QUALIFIERS_rec.return_status := FND_API.G_RET_STS_ERROR;
630 l_QUALIFIERS_tbl(I) := l_QUALIFIERS_rec;
631 l_old_QUALIFIERS_tbl(I) := l_old_QUALIFIERS_rec;
632
633 -- mkarya If process_qualifier_rules has been called from public package, then ONLY
634 -- call qp_delayed_requests_pvt.delete_reqs_for_deleted_entity
635 if l_control_rec.called_from_ui = 'N' then
636 qp_delayed_requests_pvt.delete_reqs_for_deleted_entity
637 (p_entity_code => QP_GLOBALS.G_ENTITY_ALL,
638 p_entity_id => l_QUALIFIERS_rec.list_header_id,
639 x_return_status => l_return_status);
640
641 qp_delayed_requests_pvt.delete_reqs_for_deleted_entity
642 (p_entity_code => QP_GLOBALS.G_ENTITY_ALL,
643 p_entity_id => l_QUALIFIERS_rec.list_line_id,
644 x_return_status => l_return_status);
645
646 qp_delayed_requests_pvt.delete_reqs_for_deleted_entity
647 (p_entity_code => QP_GLOBALS.G_ENTITY_ALL,
648 p_entity_id => l_QUALIFIERS_rec.qualifier_id,
649 x_return_status => l_return_status);
650 end if;
651
652 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
653
654 l_QUALIFIERS_rec.return_status := FND_API.G_RET_STS_UNEXP_ERROR;
655 l_QUALIFIERS_tbl(I) := l_QUALIFIERS_rec;
656 l_old_QUALIFIERS_tbl(I) := l_old_QUALIFIERS_rec;
657
658 -- mkarya If process_qualifier_rules has been called from public package, then ONLY
659 -- call qp_delayed_requests_pvt.delete_reqs_for_deleted_entity
660 if l_control_rec.called_from_ui = 'N' then
661 qp_delayed_requests_pvt.delete_reqs_for_deleted_entity
662 (p_entity_code => QP_GLOBALS.G_ENTITY_ALL,
663 p_entity_id => l_QUALIFIERS_rec.list_header_id,
664 x_return_status => l_return_status);
665
666 qp_delayed_requests_pvt.delete_reqs_for_deleted_entity
667 (p_entity_code => QP_GLOBALS.G_ENTITY_ALL,
668 p_entity_id => l_QUALIFIERS_rec.list_line_id,
669 x_return_status => l_return_status);
670
671 qp_delayed_requests_pvt.delete_reqs_for_deleted_entity
672 (p_entity_code => QP_GLOBALS.G_ENTITY_ALL,
673 p_entity_id => l_QUALIFIERS_rec.qualifier_id,
674 x_return_status => l_return_status);
675 end if;
676
677 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
678
679 WHEN OTHERS THEN
680
681 l_QUALIFIERS_rec.return_status := FND_API.G_RET_STS_UNEXP_ERROR;
682 l_QUALIFIERS_tbl(I) := l_QUALIFIERS_rec;
683 l_old_QUALIFIERS_tbl(I) := l_old_QUALIFIERS_rec;
684
685 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
686 THEN
687 OE_MSG_PUB.Add_Exc_Msg
688 ( G_PKG_NAME
689 , 'Qualifierss'
690 );
691 END IF;
692
693 -- mkarya If process_qualifier_rules has been called from public package, then ONLY
694 -- call qp_delayed_requests_pvt.delete_reqs_for_deleted_entity
695 if l_control_rec.called_from_ui = 'N' then
696 qp_delayed_requests_pvt.delete_reqs_for_deleted_entity
697 (p_entity_code => QP_GLOBALS.G_ENTITY_ALL,
698 p_entity_id => l_QUALIFIERS_rec.list_header_id,
699 x_return_status => l_return_status);
700
701 qp_delayed_requests_pvt.delete_reqs_for_deleted_entity
702 (p_entity_code => QP_GLOBALS.G_ENTITY_ALL,
703 p_entity_id => l_QUALIFIERS_rec.list_line_id,
704 x_return_status => l_return_status);
705
706 qp_delayed_requests_pvt.delete_reqs_for_deleted_entity
707 (p_entity_code => QP_GLOBALS.G_ENTITY_ALL,
708 p_entity_id => l_QUALIFIERS_rec.qualifier_id,
709 x_return_status => l_return_status);
710 end if;
711
712 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
713
714 END;
715 END LOOP;
716
717 -- Load OUT parameters
718
719 x_QUALIFIERS_tbl := l_QUALIFIERS_tbl;
720 x_old_QUALIFIERS_tbl := l_old_QUALIFIERS_tbl;
721
722 EXCEPTION
723
724 WHEN FND_API.G_EXC_ERROR THEN
725
726 RAISE;
727
728 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
729
730
731 RAISE;
732
733 WHEN OTHERS THEN
734
735 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
736 THEN
737 OE_MSG_PUB.Add_Exc_Msg
738 ( G_PKG_NAME
739 , 'Qualifierss'
740 );
741 END IF;
742
743
744 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
745
746 END Qualifierss;
747
748 -- Start of Comments
749 -- API name Process_Qualifier_Rules
750 -- Type Private
751 -- Function
752 --
753 -- Pre-reqs
754 --
755 -- Parameters
756 --
757 -- Version Current version = 1.0
758 -- Initial version = 1.0
759 --
760 -- Notes
761 --
762 -- End of Comments
763
764 PROCEDURE Process_Qualifier_Rules
765 ( p_api_version_number IN NUMBER
766 , p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
767 , p_commit IN VARCHAR2 := FND_API.G_FALSE
768 , p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
769 , p_control_rec IN QP_GLOBALS.Control_Rec_Type :=
770 QP_GLOBALS.G_MISS_CONTROL_REC
771 , x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2
772 , x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER
773 , x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
774 , p_QUALIFIER_RULES_rec IN QP_Qualifier_Rules_PUB.Qualifier_Rules_Rec_Type :=
775 QP_Qualifier_Rules_PUB.G_MISS_QUALIFIER_RULES_REC
776 , p_old_QUALIFIER_RULES_rec IN QP_Qualifier_Rules_PUB.Qualifier_Rules_Rec_Type :=
777 QP_Qualifier_Rules_PUB.G_MISS_QUALIFIER_RULES_REC
778 , p_QUALIFIERS_tbl IN QP_Qualifier_Rules_PUB.Qualifiers_Tbl_Type :=
779 QP_Qualifier_Rules_PUB.G_MISS_QUALIFIERS_TBL
780 , p_old_QUALIFIERS_tbl IN QP_Qualifier_Rules_PUB.Qualifiers_Tbl_Type :=
781 QP_Qualifier_Rules_PUB.G_MISS_QUALIFIERS_TBL
782 , x_QUALIFIER_RULES_rec OUT NOCOPY /* file.sql.39 change */ QP_Qualifier_Rules_PUB.Qualifier_Rules_Rec_Type
783 , x_QUALIFIERS_tbl OUT NOCOPY /* file.sql.39 change */ QP_Qualifier_Rules_PUB.Qualifiers_Tbl_Type
784 )
785 IS
786 l_api_version_number CONSTANT NUMBER := 1.0;
787 l_api_name CONSTANT VARCHAR2(30):= 'Process_Qualifier_Rules';
788 l_return_status VARCHAR2(1);
789 l_control_rec QP_GLOBALS.Control_Rec_Type;
790 l_QUALIFIER_RULES_rec QP_Qualifier_Rules_PUB.Qualifier_Rules_Rec_Type := p_QUALIFIER_RULES_rec;
791 l_p_QUALIFIER_RULES_rec QP_Qualifier_Rules_PUB.Qualifier_Rules_Rec_Type := p_QUALIFIER_RULES_rec;
792 l_old_QUALIFIER_RULES_rec QP_Qualifier_Rules_PUB.Qualifier_Rules_Rec_Type := p_old_QUALIFIER_RULES_rec;
793 l_p_old_QUALIFIER_RULES_rec QP_Qualifier_Rules_PUB.Qualifier_Rules_Rec_Type := p_old_QUALIFIER_RULES_rec;
794 l_QUALIFIERS_rec QP_Qualifier_Rules_PUB.Qualifiers_Rec_Type;
795 l_QUALIFIERS_tbl QP_Qualifier_Rules_PUB.Qualifiers_Tbl_Type;
796 l_p_QUALIFIERS_tbl QP_Qualifier_Rules_PUB.Qualifiers_Tbl_Type;
797 l_old_QUALIFIERS_rec QP_Qualifier_Rules_PUB.Qualifiers_Rec_Type;
798 l_old_QUALIFIERS_tbl QP_Qualifier_Rules_PUB.Qualifiers_Tbl_Type;
799 l_p_old_QUALIFIERS_tbl QP_Qualifier_Rules_PUB.Qualifiers_Tbl_Type;
800
801 -- Blanket Agreement
802 l_list_header_id NUMBER;
803 qual_count NUMBER;
804 l_list_name VARCHAR2(240);
805 BEGIN
806
807 oe_debug_pub.add('entering process qualifier rules');
808
809
810 -- Standard call to check for call compatibility
811
812 IF NOT FND_API.Compatible_API_Call
813 ( l_api_version_number
814 , p_api_version_number
815 , l_api_name
816 , G_PKG_NAME
817 )
818 THEN
819 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
820 END IF;
821
822 -- Initialize message list.
823
824 IF FND_API.to_Boolean(p_init_msg_list) THEN
825 OE_MSG_PUB.initialize;
826 END IF;
827
828 -- Init local table variables.
829
830 l_QUALIFIERS_tbl := p_QUALIFIERS_tbl;
831 l_old_QUALIFIERS_tbl := p_old_QUALIFIERS_tbl;
832
833 -- Qualifier_Rules
834
835 oe_debug_pub.add('calling qualifier rules');
836
837 l_p_QUALIFIER_RULES_rec := l_QUALIFIER_RULES_rec; -- added for nocopy hint
838 l_p_old_QUALIFIER_RULES_rec := l_old_QUALIFIER_RULES_rec;
839 Qualifier_Rules
840 ( p_validation_level => p_validation_level
841 , p_control_rec => p_control_rec
842 , p_QUALIFIER_RULES_rec => l_p_QUALIFIER_RULES_rec
843 , p_old_QUALIFIER_RULES_rec => l_p_old_QUALIFIER_RULES_rec
844 , x_QUALIFIER_RULES_rec => l_QUALIFIER_RULES_rec
845 , x_old_QUALIFIER_RULES_rec => l_old_QUALIFIER_RULES_rec
846 );
847
848
849 oe_debug_pub.add('leaving qualifier rules ' ||x_QUALIFIER_RULES_rec.qualifier_rule_id);
850
851
852
853 -- Perform QUALIFIER_RULES group requests.
854
855 IF p_control_rec.process AND
856 (p_control_rec.process_entity = QP_GLOBALS.G_ENTITY_ALL OR
857 p_control_rec.process_entity = QP_GLOBALS.G_ENTITY_QUALIFIER_RULES)
858 THEN
859
860 NULL;
861
862 END IF;
863
864 -- Load parent key if missing and operation is create.
865
866 FOR I IN 1..l_QUALIFIERS_tbl.COUNT LOOP
867
868 l_QUALIFIERS_rec := l_QUALIFIERS_tbl(I);
869
870 oe_debug_pub.add('qualifier rec is ');
871 oe_debug_pub.add('id is ' || l_QUALIFIERS_rec.qualifier_id);
872 oe_debug_pub.add('context is ' || l_QUALIFIERS_rec.qualifier_context);
873 oe_debug_pub.add(' attr is ' || l_QUALIFIERS_rec.qualifier_attribute);
874 oe_debug_pub.add(' val is ' || l_QUALIFIERS_rec.qualifier_attr_value);
875
876
877
878 IF l_QUALIFIERS_rec.operation = QP_GLOBALS.G_OPR_CREATE
879 AND (l_QUALIFIERS_rec.qualifier_rule_id IS NULL OR
880 l_QUALIFIERS_rec.qualifier_rule_id = FND_API.G_MISS_NUM)
881 THEN
882
883 -- Copy parent_id.
884
885 l_QUALIFIERS_tbl(I).qualifier_rule_id := l_QUALIFIER_RULES_rec.qualifier_rule_id;
886 END IF;
887
888 -- Blanket Agreement
889
890 IF l_QUALIFIERS_rec.operation = QP_GLOBALS.G_OPR_DELETE AND
891 l_QUALIFIERS_rec.qualifier_context = 'ORDER' AND
892 l_QUALIFIERS_rec.qualifier_attribute = 'QUALIFIER_ATTRIBUTE5'
893 THEN
894 qual_count := 1;
895 BEGIN
896 select list_header_id into l_list_header_id
897 from qp_qualifiers
898 where qualifier_id = l_QUALIFIERS_rec.qualifier_id;
899
900 BEGIN
901 select count(*) into qual_count
902 from qp_qualifiers
903 where list_header_id = l_list_header_id
904 and qualifier_context = 'ORDER'
905 and qualifier_attribute = 'QUALIFIER_ATTRIBUTE5'
906 and qualifier_id <> l_QUALIFIERS_rec.qualifier_id;
907 EXCEPTION
908 WHEN OTHERS THEN NULL;
909 END;
910
911 IF qual_count = 0 THEN
912 update qp_list_headers_b
913 set active_flag = 'N'
914 where list_header_id = l_list_header_id
915 and list_source_code = 'BSO';
916
917 select name into l_list_name
918 from qp_list_headers_tl
919 where list_header_id = l_list_header_id;
920
921 --Show message saying the BSO PL/Modifier incativated.
922 FND_MESSAGE.SET_NAME('QP', 'QP_BSO_PL_MODIFIER_INACTIVATED');
923 FND_MESSAGE.SET_TOKEN('BSO_PL_MOD_NAME',l_list_name);
924 -- FND_MESSAGE.SHOW;
925 oe_msg_pub.Add;
926
927 END IF;
928
929 EXCEPTION
930 WHEN OTHERS THEN NULL;
931 END;
932
933 END IF;
934
935 END LOOP;
936
937 -- Qualifierss
938
939 --dbms_output.put_line('calling qualifierssi');
940 oe_debug_pub.add('calling qualifierss from process rules pvtt');
941 l_p_QUALIFIERS_tbl := l_QUALIFIERS_tbl; -- added for nocopy hint
942 l_p_old_QUALIFIERS_tbl := l_old_QUALIFIERS_tbl;
943 Qualifierss
944 ( p_validation_level => p_validation_level
945 , p_control_rec => p_control_rec
946 , p_QUALIFIERS_tbl => l_p_QUALIFIERS_tbl
947 , p_old_QUALIFIERS_tbl => l_p_old_QUALIFIERS_tbl
948 , x_QUALIFIERS_tbl => l_QUALIFIERS_tbl
949 , x_old_QUALIFIERS_tbl => l_old_QUALIFIERS_tbl
950 );
951
952 -- Perform QUALIFIERS group requests.
953 oe_debug_pub.add('before if delayed req for warn_same_qual_group');
954
955 IF p_control_rec.process AND
956 (p_control_rec.process_entity = QP_GLOBALS.G_ENTITY_ALL OR
957 p_control_rec.process_entity = QP_GLOBALS.G_ENTITY_QUALIFIERS)
958 THEN
959
960 oe_debug_pub.add('before delayed req for warn_same_qual_group');
961
962 --code modified by spgopal for bug 1501138
963 --warning message to be shown
964 --when a < 1 qualifier is created in same group with same
965 --context and attribute from a pricelist or modifier
966 QP_DELAYED_REQUESTS_PVT.Process_Request_for_entity
967 (p_entity_code => QP_GLOBALS.G_ENTITY_QUALIFIERS
968 ,p_delete => FND_API.G_TRUE
969 ,x_return_status => l_return_status
970 );
971 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
972 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
973 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
974 RAISE FND_API.G_EXC_ERROR;
975 END IF;
976 NULL;
977
978 END IF;
979
980 oe_debug_pub.add('after if delayed req for warn_same_qual_group');
981 oe_debug_pub.add('before if delayed req for list_qual_ind');
982 oe_debug_pub.add('before if delayed req for list_qual_ind'||p_control_rec.process_entity);
983 -- Step 6. Perform Object group logic
984
985 IF p_control_rec.process AND
986 p_control_rec.process_entity = QP_GLOBALS.G_ENTITY_ALL
987 THEN
988
989 oe_debug_pub.add('before delayed req for list_qual_ind');
990 --code modified by spgopal for bug 1543456
991 --qualification_indicator to be updated for qualifiers
992 --created when a qualifierrule is copied in pricelist or modifier
993 -- FOR QUALIFICATION_IND
994 QP_DELAYED_REQUESTS_PVT.Process_Delayed_Requests
995 (
996 x_return_status => l_return_status
997 );
998 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
999 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1000 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
1001 RAISE FND_API.G_EXC_ERROR;
1002 END IF;
1003
1004 END IF;
1005 oe_debug_pub.add('after if delayed req for list_qual_ind');
1006
1007 -- Done processing, load OUT parameters.
1008
1009 x_QUALIFIER_RULES_rec := l_QUALIFIER_RULES_rec;
1010 x_QUALIFIERS_tbl := l_QUALIFIERS_tbl;
1011
1012 -- Clear API cache.
1013
1014 IF p_control_rec.clear_api_cache THEN
1015
1016 NULL;
1017
1018 END IF;
1019
1020 -- Clear API request tbl.
1021
1022 IF p_control_rec.clear_api_requests THEN
1023
1024 NULL;
1025
1026 END IF;
1027
1028 -- Derive return status.
1029
1030 x_return_status := FND_API.G_RET_STS_SUCCESS;
1031
1032 IF l_QUALIFIER_RULES_rec.return_status = FND_API.G_RET_STS_ERROR THEN
1033 x_return_status := FND_API.G_RET_STS_ERROR;
1034 END IF;
1035
1036 FOR I IN 1..l_QUALIFIERS_tbl.COUNT LOOP
1037
1038 IF l_QUALIFIERS_tbl(I).return_status = FND_API.G_RET_STS_ERROR THEN
1039 x_return_status := FND_API.G_RET_STS_ERROR;
1040 END IF;
1041
1042 END LOOP;
1043
1044 -- Get message count and data
1045
1046 OE_MSG_PUB.Count_And_Get
1047 ( p_count => x_msg_count
1048 , p_data => x_msg_data
1049 );
1050
1051 oe_debug_pub.add('leaving process qualifier rules');
1052
1053
1054 EXCEPTION
1055
1056
1057 WHEN FND_API.G_EXC_ERROR THEN
1058
1059 x_return_status := FND_API.G_RET_STS_ERROR;
1060
1061 -- mkarya If process_qualifier_rules has been called from public package, then ONLY
1062 -- call clear_request
1063 if p_control_rec.called_from_ui = 'N' then
1064 qp_delayed_requests_pvt.Clear_Request
1065 (x_return_status => l_return_status);
1066 end if;
1067
1068 -- Get message count and data
1069
1070 OE_MSG_PUB.Count_And_Get
1071 ( p_count => x_msg_count
1072 , p_data => x_msg_data
1073 );
1074
1075 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1076
1077 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1078
1079 -- mkarya If process_qualifier_rules has been called from public package, then ONLY
1080 -- call clear_request
1081 if p_control_rec.called_from_ui = 'N' then
1082 qp_delayed_requests_pvt.Clear_Request
1083 (x_return_status => l_return_status);
1084 end if;
1085
1086 -- Get message count and data
1087
1088 OE_MSG_PUB.Count_And_Get
1089 ( p_count => x_msg_count
1090 , p_data => x_msg_data
1091 );
1092
1093 WHEN OTHERS THEN
1094
1095 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1096
1097 -- mkarya If process_qualifier_rules has been called from public package, then ONLY
1098 -- call clear_request
1099 if p_control_rec.called_from_ui = 'N' then
1100 qp_delayed_requests_pvt.Clear_Request
1101 (x_return_status => l_return_status);
1102 end if;
1103
1104 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1105 THEN
1106 OE_MSG_PUB.Add_Exc_Msg
1107 ( G_PKG_NAME
1108 , 'Process_Qualifier_Rules'
1109 );
1110 END IF;
1111
1112 -- Get message count and data
1113
1114 OE_MSG_PUB.Count_And_Get
1115 ( p_count => x_msg_count
1116 , p_data => x_msg_data
1117 );
1118
1119 oe_debug_pub.add('in exception qualifier_rules_pvt');
1120 END Process_Qualifier_Rules;
1121
1122 -- Start of Comments
1123 -- API name Lock_Qualifier_Rules
1124 -- Type Private
1125 -- Function
1126 --
1127 -- Pre-reqs
1128 --
1129 -- Parameters
1130 --
1131 -- Version Current version = 1.0
1132 -- Initial version = 1.0
1133 --
1134 -- Notes
1135 --
1136 -- End of Comments
1137
1138 PROCEDURE Lock_Qualifier_Rules
1139 ( p_api_version_number IN NUMBER
1140 , p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
1141 , x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2
1142 , x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER
1143 , x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
1144 , p_QUALIFIER_RULES_rec IN QP_Qualifier_Rules_PUB.Qualifier_Rules_Rec_Type :=
1145 QP_Qualifier_Rules_PUB.G_MISS_QUALIFIER_RULES_REC
1146 , p_QUALIFIERS_tbl IN QP_Qualifier_Rules_PUB.Qualifiers_Tbl_Type :=
1147 QP_Qualifier_Rules_PUB.G_MISS_QUALIFIERS_TBL
1148 , x_QUALIFIER_RULES_rec OUT NOCOPY /* file.sql.39 change */ QP_Qualifier_Rules_PUB.Qualifier_Rules_Rec_Type
1149 , x_QUALIFIERS_tbl OUT NOCOPY /* file.sql.39 change */ QP_Qualifier_Rules_PUB.Qualifiers_Tbl_Type
1150 )
1151 IS
1152 l_api_version_number CONSTANT NUMBER := 1.0;
1153 l_api_name CONSTANT VARCHAR2(30):= 'Lock_Qualifier_Rules';
1154 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
1155 l_QUALIFIERS_rec QP_Qualifier_Rules_PUB.Qualifiers_Rec_Type;
1156 BEGIN
1157
1158 -- Standard call to check for call compatibility
1159
1160 IF NOT FND_API.Compatible_API_Call
1161 ( l_api_version_number
1162 , p_api_version_number
1163 , l_api_name
1164 , G_PKG_NAME
1165 )
1166 THEN
1167 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1168 END IF;
1169
1170 -- Initialize message list.
1171
1172 IF FND_API.to_Boolean(p_init_msg_list) THEN
1173 OE_MSG_PUB.initialize;
1174 END IF;
1175
1176 -- Set Savepoint
1177
1178 SAVEPOINT Lock_Qualifier_Rules_PVT;
1179
1180 -- Lock QUALIFIER_RULES
1181
1182 IF p_QUALIFIER_RULES_rec.operation = QP_GLOBALS.G_OPR_LOCK THEN
1183
1184 QP_Qualifier_Rules_Util.Lock_Row
1185 ( p_QUALIFIER_RULES_rec => p_QUALIFIER_RULES_rec
1186 , x_QUALIFIER_RULES_rec => x_QUALIFIER_RULES_rec
1187 , x_return_status => l_return_status
1188 );
1189
1190 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1191 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1192 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
1193 RAISE FND_API.G_EXC_ERROR;
1194 END IF;
1195
1196
1197 END IF;
1198
1199 -- Lock QUALIFIERS
1200
1201 FOR I IN 1..p_QUALIFIERS_tbl.COUNT LOOP
1202
1203 IF p_QUALIFIERS_tbl(I).operation = QP_GLOBALS.G_OPR_LOCK THEN
1204
1205 QP_Qualifiers_Util.Lock_Row
1206 ( p_QUALIFIERS_rec => p_QUALIFIERS_tbl(I)
1207 , x_QUALIFIERS_rec => l_QUALIFIERS_rec
1208 , x_return_status => l_return_status
1209 );
1210
1211 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1212 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1213 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
1214 RAISE FND_API.G_EXC_ERROR;
1215 END IF;
1216
1217 x_QUALIFIERS_tbl(I) := l_QUALIFIERS_rec;
1218
1219 END IF;
1220
1221 END LOOP;
1222
1223 -- Set return status
1224
1225 x_return_status := FND_API.G_RET_STS_SUCCESS;
1226
1227 -- Get message count and data
1228
1229 OE_MSG_PUB.Count_And_Get
1230 ( p_count => x_msg_count
1231 , p_data => x_msg_data
1232 );
1233
1234
1235 EXCEPTION
1236
1237 WHEN FND_API.G_EXC_ERROR THEN
1238
1239 x_return_status := FND_API.G_RET_STS_ERROR;
1240
1241 -- Get message count and data
1242
1243 OE_MSG_PUB.Count_And_Get
1244 ( p_count => x_msg_count
1245 , p_data => x_msg_data
1246 );
1247
1248 -- Rollback
1249
1250 ROLLBACK TO Lock_Qualifier_Rules_PVT;
1251
1252 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1253
1254 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1255
1256 -- Get message count and data
1257
1258 OE_MSG_PUB.Count_And_Get
1259 ( p_count => x_msg_count
1260 , p_data => x_msg_data
1261 );
1262
1263 -- Rollback
1264
1265 ROLLBACK TO Lock_Qualifier_Rules_PVT;
1266
1267 WHEN OTHERS THEN
1268
1269 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1270
1271 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1272 THEN
1273 OE_MSG_PUB.Add_Exc_Msg
1274 ( G_PKG_NAME
1275 , 'Lock_Qualifier_Rules'
1276 );
1277 END IF;
1278
1279 -- Get message count and data
1280
1281 OE_MSG_PUB.Count_And_Get
1282 ( p_count => x_msg_count
1283 , p_data => x_msg_data
1284 );
1285
1286 -- Rollback
1287
1288 ROLLBACK TO Lock_Qualifier_Rules_PVT;
1289
1290 END Lock_Qualifier_Rules;
1291
1292 -- Start of Comments
1293 -- API name Get_Qualifier_Rules
1294 -- Type Private
1295 -- Function
1296 --
1297 -- Pre-reqs
1298 --
1299 -- Parameters
1300 --
1301 -- Version Current version = 1.0
1302 -- Initial version = 1.0
1303 --
1304 -- Notes
1305 --
1306 -- End of Comments
1307
1308 PROCEDURE Get_Qualifier_Rules
1309 ( p_api_version_number IN NUMBER
1310 , p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
1311 , x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2
1312 , x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER
1313 , x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
1314 , p_qualifier_rule_id IN NUMBER
1315 , x_QUALIFIER_RULES_rec OUT NOCOPY /* file.sql.39 change */ QP_Qualifier_Rules_PUB.Qualifier_Rules_Rec_Type
1316 , x_QUALIFIERS_tbl OUT NOCOPY /* file.sql.39 change */ QP_Qualifier_Rules_PUB.Qualifiers_Tbl_Type
1317 )
1318 IS
1319 l_api_version_number CONSTANT NUMBER := 1.0;
1320 l_api_name CONSTANT VARCHAR2(30):= 'Get_Qualifier_Rules';
1321 l_QUALIFIER_RULES_rec QP_Qualifier_Rules_PUB.Qualifier_Rules_Rec_Type;
1322 l_QUALIFIERS_tbl QP_Qualifier_Rules_PUB.Qualifiers_Tbl_Type;
1323 BEGIN
1324
1325 -- Standard call to check for call compatibility
1326
1327 IF NOT FND_API.Compatible_API_Call
1328 ( l_api_version_number
1329 , p_api_version_number
1330 , l_api_name
1331 , G_PKG_NAME
1332 )
1333 THEN
1334 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1335 END IF;
1336
1337 -- Initialize message list.
1338
1339 IF FND_API.to_Boolean(p_init_msg_list) THEN
1340 OE_MSG_PUB.initialize;
1341 END IF;
1342
1343 -- Get QUALIFIER_RULES ( parent = QUALIFIER_RULES )
1344
1345 l_QUALIFIER_RULES_rec := QP_Qualifier_Rules_Util.Query_Row
1346 ( p_qualifier_rule_id => p_qualifier_rule_id
1347 );
1348
1349 -- Get QUALIFIERS ( parent = QUALIFIER_RULES )
1350
1351 l_QUALIFIERS_tbl := QP_Qualifiers_Util.Query_Rows
1352 ( p_qualifier_rule_id => l_QUALIFIER_RULES_rec.qualifier_rule_id
1353 );
1354
1355
1356 -- Load out parameters
1357
1358 x_QUALIFIER_RULES_rec := l_QUALIFIER_RULES_rec;
1359 x_QUALIFIERS_tbl := l_QUALIFIERS_tbl;
1360
1361 -- Set return status
1362
1363 x_return_status := FND_API.G_RET_STS_SUCCESS;
1364
1365 -- Get message count and data
1366
1367 OE_MSG_PUB.Count_And_Get
1368 ( p_count => x_msg_count
1369 , p_data => x_msg_data
1370 );
1371
1372
1373 EXCEPTION
1374
1375 WHEN FND_API.G_EXC_ERROR THEN
1376
1377 x_return_status := FND_API.G_RET_STS_ERROR;
1378
1379 -- Get message count and data
1380
1381 OE_MSG_PUB.Count_And_Get
1382 ( p_count => x_msg_count
1383 , p_data => x_msg_data
1384 );
1385
1386 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1387
1388 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1389
1390 -- Get message count and data
1391
1392 OE_MSG_PUB.Count_And_Get
1393 ( p_count => x_msg_count
1394 , p_data => x_msg_data
1395 );
1396
1397 WHEN OTHERS THEN
1398
1399 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1400
1401 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1402 THEN
1403 OE_MSG_PUB.Add_Exc_Msg
1404 ( G_PKG_NAME
1405 , 'Get_Qualifier_Rules'
1406 );
1407 END IF;
1408
1409 -- Get message count and data
1410
1411 OE_MSG_PUB.Count_And_Get
1412 ( p_count => x_msg_count
1413 , p_data => x_msg_data
1414 );
1415
1416 END Get_Qualifier_Rules;
1417
1418
1419
1420 -- Start of Comments
1421 -- API name Copy_Qualifier_Rule
1422 -- Type Private
1423 -- Function
1424 --
1425 -- Pre-reqs
1426 --
1427 -- Parameters
1428 --
1429 -- Version Current version = 1.0
1430 -- Initial version = 1.0
1431 --
1432 -- Notes
1433 --
1434 -- End of Comments
1435
1436
1437 PROCEDURE Copy_Qualifier_Rule
1438 ( p_api_version_number IN NUMBER
1439 , p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
1440 , p_commit IN VARCHAR2 := FND_API.G_FALSE
1441 , x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2
1442 , x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER
1443 , x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
1444 , p_qualifier_rule_id IN NUMBER := FND_API.G_MISS_NUM
1445 , p_to_qualifier_rule IN VARCHAR2 :=FND_API.G_MISS_CHAR
1446 , p_to_description IN VARCHAR2 := FND_API.G_MISS_CHAR
1447 , x_qualifier_rule_id OUT NOCOPY /* file.sql.39 change */ NUMBER
1448 ) IS
1449
1450
1451 l_x_QUALIFIER_RULES_rec QP_Qualifier_Rules_PUB.Qualifier_Rules_Rec_Type;
1452 l_x_QUALIFIERS_tbl QP_Qualifier_Rules_PUB.Qualifiers_Tbl_Type;
1453 l_x_Qualifier_rule_id NUMBER;
1454
1455 l_QUALIFIER_RULES_rec QP_Qualifier_Rules_PUB.Qualifier_Rules_Rec_Type;
1456 l_p_QUALIFIER_RULES_rec QP_Qualifier_Rules_PUB.Qualifier_Rules_Rec_Type;
1457 l_QUALIFIERS_rec QP_Qualifier_Rules_PUB.Qualifiers_Rec_Type;
1458 l_QUALIFIERS_tbl QP_Qualifier_Rules_PUB.Qualifiers_Tbl_Type;
1459 l_p_QUALIFIERS_tbl QP_Qualifier_Rules_PUB.Qualifiers_Tbl_Type;
1460 l_control_rec QP_GLOBALS.Control_Rec_Type;
1461 l_api_version_number CONSTANT NUMBER := 1.0;
1462 l_api_name CONSTANT VARCHAR2(30):= 'Copy_Qualifier_Rule';
1463 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
1464
1465 BEGIN
1466
1467
1468 -- Standard call to check for call compatibility
1469
1470 IF NOT FND_API.Compatible_API_Call
1471 ( l_api_version_number
1472 , p_api_version_number
1473 , l_api_name
1474 , G_PKG_NAME
1475 )
1476 THEN
1477 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1478 END IF;
1479
1480 -- Initialize message list.
1481
1482 IF FND_API.to_Boolean(p_init_msg_list) THEN
1483 OE_MSG_PUB.initialize;
1484 END IF;
1485
1486 --Get qualifer rule ,qualifier details for the given qualifier rule id
1487
1488
1489 QP_Qualifier_Rules_PVT.Get_Qualifier_Rules
1490 ( p_api_version_number =>1.0
1491 , p_init_msg_list => FND_API.G_FALSE
1492 , x_return_status =>l_return_status
1493 , x_msg_count =>x_msg_count
1494 , x_msg_data =>x_msg_data
1495 , p_qualifier_rule_id =>p_qualifier_rule_id
1496 , x_QUALIFIER_RULES_rec=>l_x_QUALIFIER_RULES_rec
1497 , x_QUALIFIERS_tbl=>l_x_QUALIFIERS_tbl
1498 );
1499
1500 --Prepare new qualifier rule record for inserting ,with passed qualifier rule name and
1501 --description
1502
1503
1504
1505 l_QUALIFIER_RULES_rec.operation:=QP_GLOBALS.G_OPR_CREATE;
1506 l_QUALIFIER_RULES_rec.name:= p_to_qualifier_rule;
1507 l_QUALIFIER_RULES_rec.description:= p_to_description;
1508
1509
1510 --Prepare retrieved qualifier records for inserting with new qualifier rule id as parent
1511
1512
1513 l_QUALIFIERS_tbl := l_x_QUALIFIERS_tbl;
1514
1515 FOR i in 1..l_QUALIFIERS_tbl.count loop
1516
1517 l_QUALIFIERS_rec := l_QUALIFIERS_tbl(i);
1518
1519 --dbms_output.put_line('id :' ||l_QUALIFIERS_rec.qualifier_id);
1520 --dbms_output.put_line('rule id :' ||l_QUALIFIERS_rec.qualifier_rule_id);
1521 l_QUALIFIERS_tbl(i).operation := QP_GLOBALS.G_OPR_CREATE;
1522 l_QUALIFIERS_tbl(i).qualifier_id :=FND_API.G_MISS_NUM;
1523 l_QUALIFIERS_tbl(i).qualifier_rule_id :=FND_API.G_MISS_NUM;
1524
1525 END LOOP;
1526
1527 -- call Process qualifiers for inserting new qualifier rule and qualifiers in QP_QUALIFIERS
1528 l_p_QUALIFIER_RULES_rec := l_QUALIFIER_RULES_rec; -- added for nocopy hint
1529 l_p_QUALIFIERS_tbl := l_QUALIFIERS_tbl;
1530 QP_Qualifier_Rules_PVT.Process_Qualifier_Rules
1531 ( p_api_version_number => 1.0
1532 , p_init_msg_list => p_init_msg_list
1533 , p_validation_level => FND_API.G_VALID_LEVEL_FULL
1534 , p_commit => p_commit
1535 , x_return_status => x_return_status
1536 , x_msg_count => x_msg_count
1537 , x_msg_data => x_msg_data
1538 , p_control_rec => l_control_rec
1539 , p_QUALIFIER_RULES_rec => l_p_QUALIFIER_RULES_rec
1540 , p_QUALIFIERS_tbl => l_p_QUALIFIERS_tbl
1541 , x_QUALIFIER_RULES_rec => l_QUALIFIER_RULES_rec
1542 , x_QUALIFIERS_tbl => l_QUALIFIERS_tbl
1543 );
1544
1545 -- Load out parameters
1546
1547 x_qualifier_rule_id := l_QUALIFIER_RULES_rec.qualifier_rule_id;
1548
1549
1550
1551 -- Set return status
1552
1553 x_return_status := FND_API.G_RET_STS_SUCCESS;
1554
1555 -- Get message count and data
1556
1557 OE_MSG_PUB.Count_And_Get
1558 ( p_count => x_msg_count
1559 , p_data => x_msg_data
1560 );
1561
1562
1563 EXCEPTION
1564
1565 WHEN FND_API.G_EXC_ERROR THEN
1566
1567 x_return_status := FND_API.G_RET_STS_ERROR;
1568
1569 -- Get message count and data
1570
1571 OE_MSG_PUB.Count_And_Get
1572 ( p_count => x_msg_count
1573 , p_data => x_msg_data
1574 );
1575
1576 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1577
1578 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1579
1580 -- Get message count and data
1581
1582 OE_MSG_PUB.Count_And_Get
1583 ( p_count => x_msg_count
1584 , p_data => x_msg_data
1585 );
1586
1587 WHEN OTHERS THEN
1588
1589 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1590
1591 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1592 THEN
1593 OE_MSG_PUB.Add_Exc_Msg
1594 ( G_PKG_NAME
1595 , 'Copy_Qualifier_Rule'
1596 );
1597 END IF;
1598
1599 -- Get message count and data
1600
1601 OE_MSG_PUB.Count_And_Get
1602 ( p_count => x_msg_count
1603 , p_data => x_msg_data
1604 );
1605
1606
1607 END Copy_Qualifier_Rule;
1608
1609 -- Blanket Agreement
1610
1611 PROCEDURE Create_Blanket_Qualifier
1612 ( p_list_header_id IN NUMBER
1613 , p_old_list_header_id IN NUMBER
1614 , p_blanket_id IN NUMBER
1615 , p_operation IN VARCHAR2
1616 , x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2
1617 )
1618 IS
1619 /* For creating qualifiers */
1620 l_price_list_type_code QP_LIST_HEADERS_B.LIST_TYPE_CODE%TYPE;
1621 l_QUALIFIERS_tbl QP_Qualifier_Rules_PUB.Qualifiers_Tbl_Type ;
1622 l_QUALIFIERS_val_tbl QP_Qualifier_Rules_PUB.Qualifiers_Val_Tbl_Type;
1623 l_QUALIFIERS_rec QP_Qualifier_Rules_PUB.Qualifiers_Rec_Type ;
1624 l_QUALIFIER_RULES_rec QP_Qualifier_Rules_PUB.Qualifier_Rules_Rec_Type ;
1625 l_QUALIFIER_RULES_val_rec QP_Qualifier_Rules_PUB.Qualifier_Rules_Val_Rec_Type;
1626
1627 l_control_rec QP_GLOBALS.Control_Rec_Type;
1628
1629 l_x_QUALIFIERS_tbl QP_Qualifier_Rules_PUB.Qualifiers_Tbl_Type ;
1630 l_x_QUALIFIERS_val_tbl QP_Qualifier_Rules_PUB.Qualifiers_Val_Tbl_Type;
1631 l_x_QUALIFIERS_rec QP_Qualifier_Rules_PUB.Qualifiers_Rec_Type ;
1632 l_x_QUALIFIER_RULES_rec QP_Qualifier_Rules_PUB.Qualifier_Rules_Rec_Type ;
1633 l_x_QUALIFIER_RULES_val_rec QP_Qualifier_Rules_PUB.Qualifier_Rules_Val_Rec_Type;
1634 l_return_status VARCHAR2(1);
1635 l_return_values VARCHAR2(1) := FND_API.G_FALSE;
1636 l_commit VARCHAR2(1) := FND_API.G_FALSE;
1637 l_msg_count NUMBER;
1638 l_msg_data VARCHAR2(250);
1639 l_qual_count NUMBER;
1640 l_old_qual_count NUMBER;
1641
1642 BEGIN
1643
1644 oe_debug_pub.add('begin create_blanket_qual'||to_char(p_old_list_header_id)||'old'||to_char(p_list_header_id)||'new');
1645
1646 IF p_list_header_id IS NOT NULL OR
1647 p_list_header_id <> FND_API.G_MISS_NUM THEN
1648
1649 if p_operation = QP_GLOBALS.G_OPR_CREATE then
1650
1651 oe_debug_pub.add('in if create : '||p_operation);
1652
1653 l_qualifiers_rec.list_header_id := p_list_header_id;
1654 l_qualifiers_rec.qualifier_attr_value := p_blanket_id;
1655 l_qualifiers_rec.qualifier_context := 'ORDER';
1656 l_qualifiers_rec.qualifier_attribute := 'QUALIFIER_ATTRIBUTE5';
1657 l_qualifiers_rec.qualifier_grouping_no := p_blanket_id;
1658
1659 l_qualifiers_rec.operation := QP_GLOBALS.G_OPR_CREATE;
1660 l_qualifiers_tbl(1) := l_qualifiers_rec;
1661
1662 l_control_rec.called_from_ui := 'Y';
1663
1664 QP_Qualifier_Rules_PVT.Process_Qualifier_Rules
1665 ( p_api_version_number => 1.0
1666 , p_init_msg_list => FND_API.G_TRUE
1667 , p_validation_level => FND_API.G_VALID_LEVEL_FULL
1668 , p_commit => FND_API.G_FALSE
1669 , x_return_status => l_return_status
1670 , x_msg_count => l_msg_count
1671 , x_msg_data => l_msg_data
1672 , p_control_rec => l_control_rec
1673 , p_QUALIFIER_RULES_rec => l_QUALIFIER_RULES_rec
1674 , p_QUALIFIERS_tbl => l_QUALIFIERS_tbl
1675 , x_QUALIFIER_RULES_rec => l_x_QUALIFIER_RULES_rec
1676 , x_QUALIFIERS_tbl => l_x_QUALIFIERS_tbl
1677 );
1678
1679 elsif p_operation = OE_GLOBALS.G_OPR_UPDATE then
1680 oe_debug_pub.add('in if update : '||p_operation);
1681
1682 if p_old_list_header_id <> p_list_header_id then
1683 oe_debug_pub.add('in else id!=id'||p_operation);
1684 delete from qp_qualifiers where
1685 list_header_id = p_old_list_header_id
1686 and qualifier_context = 'ORDER'
1687 and qualifier_attribute = 'QUALIFIER_ATTRIBUTE5'
1688 and qualifier_attr_value = p_blanket_id;
1689
1690 else
1691 null;
1692 end if;
1693
1694 oe_debug_pub.add('in if id=id'||p_operation);
1695
1696 BEGIN
1697 select count(list_header_id) into l_qual_count
1698 from qp_qualifiers where
1699 list_header_id = p_list_header_id
1700 and qualifier_context = 'ORDER'
1701 and qualifier_attribute = 'QUALIFIER_ATTRIBUTE5'
1702 and qualifier_attr_value = p_blanket_id;
1703
1704 EXCEPTION
1705 When NO_DATA_FOUND Then
1706 l_qual_count := 0;
1707
1708 END;
1709
1710 if l_qual_count < 1 then
1711 oe_debug_pub.add('in if count'||to_char(l_qual_count));
1712 l_qualifiers_rec.list_header_id := p_list_header_id;
1713 l_qualifiers_rec.qualifier_attr_value := p_blanket_id;
1714 l_qualifiers_rec.qualifier_context := 'ORDER';
1715 l_qualifiers_rec.qualifier_attribute := 'QUALIFIER_ATTRIBUTE5';
1716 l_qualifiers_rec.qualifier_grouping_no := p_blanket_id;
1717
1718 l_qualifiers_rec.operation := QP_GLOBALS.G_OPR_CREATE;
1719 l_qualifiers_tbl(1) := l_qualifiers_rec;
1720
1721 else
1722 oe_debug_pub.add('in else count'||to_char(l_qual_count));
1723 null;
1724
1725 end if; --l_qual_count<1
1726
1727 l_control_rec.called_from_ui := 'Y';
1728
1729 QP_Qualifier_Rules_PVT.Process_Qualifier_Rules
1730 ( p_api_version_number => 1.0
1731 , p_init_msg_list => FND_API.G_TRUE
1732 , p_validation_level => FND_API.G_VALID_LEVEL_FULL
1733 , p_commit => FND_API.G_FALSE
1734 , x_return_status => x_return_status
1735 , x_msg_count => l_msg_count
1736 , x_msg_data => l_msg_data
1737 , p_control_rec => l_control_rec
1738 , p_QUALIFIER_RULES_rec => l_QUALIFIER_RULES_rec
1739 , p_QUALIFIERS_tbl => l_QUALIFIERS_tbl
1740 , x_QUALIFIER_RULES_rec => l_x_QUALIFIER_RULES_rec
1741 , x_QUALIFIERS_tbl => l_x_QUALIFIERS_tbl
1742 );
1743
1744 elsif p_operation = OE_GLOBALS.G_OPR_DELETE then
1745 oe_debug_pub.add('in if delete : '||p_operation);
1746
1747 if p_old_list_header_id <> p_list_header_id then
1748 oe_debug_pub.add('in else id != id '||p_operation);
1749 delete from qp_qualifiers where
1750 list_header_id = p_old_list_header_id
1751 and qualifier_context = 'ORDER'
1752 and qualifier_attribute = 'QUALIFIER_ATTRIBUTE5'
1753 and qualifier_attr_value = p_blanket_id;
1754
1755 else
1756 null;
1757 end if;
1758
1759 BEGIN
1760 oe_debug_pub.add('in if id=id'||p_operation);
1761 delete from qp_qualifiers where
1762 list_header_id = p_old_list_header_id
1763 and qualifier_context = 'ORDER'
1764 and qualifier_attribute = 'QUALIFIER_ATTRIBUTE5'
1765 and qualifier_attr_value = p_blanket_id;
1766 EXCEPTION
1767 When NO_DATA_FOUND Then
1768 l_qual_count := 0;
1769
1770 END;
1771 end if; --operation
1772 END IF;
1773
1774 oe_debug_pub.add('end create_blanket_qual'||to_char(p_old_list_header_id)||'old'||to_char(p_list_header_id)||'new');
1775
1776 EXCEPTION
1777
1778 WHEN OTHERS THEN
1779 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1780 THEN
1781 OE_MSG_PUB.Add_Exc_Msg
1782 ( G_PKG_NAME
1783 , 'Create_Blanket_Qualifier'
1784 );
1785 END IF;
1786
1787 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1788 -- Get message count and data
1789 OE_MSG_PUB.Count_And_Get
1790 ( p_count => l_msg_count
1791 , p_data => l_msg_data
1792 );
1793
1794 oe_debug_pub.add('exp create_blanket_qual'||to_char(p_old_list_header_id)||'old'||to_char(p_list_header_id)||'new');
1795
1796 END Create_Blanket_Qualifier;
1797
1798 END QP_Qualifier_Rules_PVT;