[Home] [Help]
PACKAGE BODY: APPS.CN_RULEATTRIBUTE_PUB
Source
1 PACKAGE BODY CN_RuleAttribute_PUB AS
2 --$Header: cnpratrb.pls 120.1 2005/08/25 23:37:55 rramakri noship $
3
4 --Global Variables
5 G_PKG_NAME CONSTANT VARCHAR2(30) := 'CN_RuleAttribute_PUB';
6 G_API_NAME VARCHAR2(30);
7 G_LAST_UPDATE_DATE DATE := Sysdate;
8 G_LAST_UPDATED_BY NUMBER := fnd_global.user_id;
9 G_CREATION_DATE DATE := Sysdate;
10 G_CREATED_BY NUMBER := fnd_global.user_id;
11 G_LAST_UPDATE_LOGIN NUMBER := fnd_global.login_id;
12
13
14 -- Start of comments
15 -- API name : Create_RuleAttribute
16 -- Type : Public
17 -- Function : This Public API can be used to create a rule,
18 -- a ruleset or rule attributes.
19 -- Pre-reqs : None.
20 -- Parameters :
21 -- IN : p_api_version IN NUMBER Required
22 -- p_init_msg_list IN VARCHAR2 Optional
23 -- Default = FND_API.G_FALSE
24 -- p_commit IN VARCHAR2 Optional
25 -- Default = FND_API.G_FALSE
26 -- p_validation_level IN NUMBER Optional
27 -- Default = FND_API.G_VALID_LEVEL_FULL
28 -- p_RuleAttribute_rec IN
29 -- CN_RuleAttribute_PUB.RuleAttribute_rec_type
30 --
31 -- OUT : x_return_status OUT VARCHAR2(1)
32 -- x_msg_count OUT NUMBER
33 -- x_msg_data OUT VARCHAR2(2000)
34 --
35 -- Version : Current version 1.0
36 -- 25-Mar-99 Renu Chintalapati
37 -- previous version y.y
38 -- Changed....
39 -- Initial version 1.0
40 -- 25-Mar-99 Renu Chintalapati
41 --
42 -- Notes : Note text
43 --
44 -- End of comments
45
46 PROCEDURE Create_RuleAttribute
47 ( p_api_version IN NUMBER,
48 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
49 p_commit IN VARCHAR2 := FND_API.G_FALSE,
50 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
51 x_return_status OUT NOCOPY VARCHAR2,
52 x_msg_count OUT NOCOPY NUMBER,
53 x_msg_data OUT NOCOPY VARCHAR2,
54 x_loading_status OUT NOCOPY VARCHAR2,
55 p_RuleAttribute_rec IN CN_RuleAttribute_PUB.RuleAttribute_rec_type
56 )
57 IS
58
59 l_api_name CONSTANT VARCHAR2(30) := 'Create_RuleAttribute';
60 l_api_version CONSTANT NUMBER := 1.0;
61 l_count NUMBER;
62 l_ruleattribute_rec_pvt cn_ruleattribute_pvt.ruleattribute_rec_type;
63 l_object_id cn_objects.object_id%TYPE;
64 l_bind_data_id NUMBER;
65 l_return_code VARCHAR2(1);
66
67
68 CURSOR get_rules(p_ruleset_id IN cn_rulesets.ruleset_id%TYPE,p_org_id IN cn_rulesets.org_id%TYPE) IS
69 SELECT rule_id
70 FROM cn_rules
71 WHERE ruleset_id = p_ruleset_id
72 AND name = p_ruleattribute_rec.rule_name
73 AND org_id=p_org_id;
74
75 --
76 -- Declaration for user hooks
77 --
78 l_OAI_array JTF_USR_HKS.oai_data_array_type;
79 l_ruleattribute_rec CN_RuleAttribute_PUB.RuleAttribute_rec_type;
80
81 BEGIN
82
83 -- Standard Start of API savepoint
84 SAVEPOINT Create_RuleAttribute;
85 -- Standard call to check for call compatibility.
86 IF NOT FND_API.Compatible_API_Call ( l_api_version,
87 p_api_version,
88 l_api_name,
89 G_PKG_NAME )
90 THEN
91 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
92 END IF;
93
94 -- Initialize message list if p_init_msg_list is set to TRUE.
95 IF FND_API.to_Boolean( p_init_msg_list )
96 THEN
97 FND_MSG_PUB.initialize;
98 END IF;
99
100 -- Initialize API return status to success
101 x_return_status := FND_API.G_RET_STS_SUCCESS;
102 x_loading_status := 'CN_INSERTED';
103
104
105 --
106 -- Assign the parameter to a local variable
107 --
108 l_ruleattribute_rec := p_ruleattribute_rec;
109
110
111 --
112 -- User hooks
113 --
114
115 -- customer pre-processing section
116 IF JTF_USR_HKS.Ok_to_Execute('CN_RULEATTRIBUTE_PUB',
117 'CREATE_RULEATTRIBUTE',
118 'B',
119 'C')
120 THEN
121 cn_ruleattribute_pub_cuhk.create_ruleattribute_pre
122 (p_api_version => p_api_version,
123 p_init_msg_list => p_init_msg_list,
124 p_commit => p_commit,
125 p_validation_level => p_validation_level,
126 x_return_status => x_return_status,
127 x_msg_count => x_msg_count,
128 x_msg_data => x_msg_data,
129 x_loading_status => x_loading_status,
130 p_ruleattribute_rec => l_ruleattribute_rec);
131
132 IF x_return_status = fnd_api.g_ret_sts_error
133 THEN
134 RAISE fnd_api.g_exc_error;
135 ELSIF x_return_status = fnd_api.g_ret_sts_unexp_error
136 THEN
137 RAISE fnd_api.g_exc_unexpected_error;
138 END IF;
139 END IF;
140
141 -- vertical industry pre-processing section
142 IF JTF_USR_HKS.Ok_to_Execute('CN_RULEATTRIBUTE_PUB',
143 'CREATE_RULEATTRIBUTE',
144 'B',
145 'V')
146 THEN
147 cn_ruleattribute_pub_vuhk.create_ruleattribute_pre
148 (p_api_version => p_api_version,
149 p_init_msg_list => p_init_msg_list,
150 p_commit => p_commit,
151 p_validation_level => p_validation_level,
152 x_return_status => x_return_status,
153 x_msg_count => x_msg_count,
154 x_msg_data => x_msg_data,
155 x_loading_status => x_loading_status,
156 p_ruleattribute_rec => l_ruleattribute_rec);
157
158 IF x_return_status = fnd_api.g_ret_sts_error
159 THEN
160 RAISE fnd_api.g_exc_error;
161 ELSIF x_return_status = fnd_api.g_ret_sts_unexp_error
162 THEN
163 RAISE fnd_api.g_exc_unexpected_error;
164 END IF;
165 END IF;
166
167
168 --
169 -- API body
170 --
171
172
173 --Check for missing parameters
174 IF (cn_api.chk_miss_null_char_para
175 ( l_RuleAttribute_rec.ruleset_name,
176 cn_api.get_lkup_meaning('RULESET_NAME', 'RULESET_TYPE'),
177 x_loading_status,
178 x_loading_status) = FND_API.G_TRUE )
179 THEN
180 RAISE fnd_api.g_exc_error;
181 END IF;
182 IF (cn_api.chk_miss_null_date_para
183 ( l_RuleAttribute_rec.start_date,
184 cn_api.get_lkup_meaning('START_DATE', 'RULESET_TYPE'),
185 x_loading_status,
186 x_loading_status) = FND_API.G_TRUE )
187 THEN
188 RAISE fnd_api.g_exc_error;
189 END IF;
190 IF (cn_api.chk_miss_null_date_para
191 ( l_RuleAttribute_rec.end_date,
192 cn_api.get_lkup_meaning('END_DATE', 'RULESET_TYPE'),
193 x_loading_status,
194 x_loading_status) = FND_API.G_TRUE )
195 THEN
196 RAISE fnd_api.g_exc_error;
197 END IF;
198
199 IF (cn_api.chk_miss_null_char_para
200 ( l_RuleAttribute_rec.rule_name,
201 cn_api.get_lkup_meaning('RULE_NAME', 'RULESET_TYPE'),
202 x_loading_status,
203 x_loading_status) = FND_API.G_TRUE )
204 THEN
205 RAISE fnd_api.g_exc_error;
206 END IF;
207
208 IF (cn_api.chk_null_char_para
209 ( l_RuleAttribute_rec.object_name,
210 cn_api.get_lkup_meaning('OBJECT_NAME', 'RULESET_TYPE'),
211 x_loading_status,
212 x_loading_status) = FND_API.G_TRUE )
213 THEN
214 RAISE fnd_api.g_exc_error;
215 END IF;
216
217 IF (cn_api.chk_null_char_para
218 ( l_RuleAttribute_rec.data_flag,
219 cn_api.get_lkup_meaning('DATA_FLAG', 'RULESET_TYPE'),
220 x_loading_status,
221 x_loading_status) = FND_API.G_TRUE )
222 THEN
223 RAISE fnd_api.g_exc_error;
224 END IF;
225
226 SELECT object_id
227 INTO l_object_id
228 FROM cn_objects
229 WHERE name = l_RuleAttribute_rec.object_name
230 AND table_id = -11803 and
231 org_id=l_RuleAttribute_rec.org_id;
232
233 SELECT cn_attribute_rules_s.NEXTVAL
234 INTO l_ruleattribute_rec_pvt.attribute_rule_id
235 FROM dual;
236
237 SELECT count(1)
238 INTO l_count
239 FROM cn_rulesets
240 WHERE name = l_ruleattribute_rec.ruleset_name
241 AND start_date = l_ruleattribute_rec.start_date
242 AND end_date = l_ruleattribute_rec.end_date
243 and org_id=l_RuleAttribute_rec.org_id;
244
245 IF l_count = 0
246 THEN
247 --Error condition
248 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
249 THEN
250 fnd_message.set_name('CN', 'CN_INVALID_RULESET');
251 fnd_msg_pub.add;
252 END IF;
253 x_loading_status := 'CN_INVALID_RULESET';
254 RAISE FND_API.G_EXC_ERROR;
255 ELSE
256 SELECT ruleset_id
257 INTO l_ruleattribute_rec_pvt.ruleset_id
258 FROM cn_rulesets
259 WHERE name = l_ruleattribute_rec.ruleset_name
260 AND start_date = l_ruleattribute_rec.start_date
261 AND end_date = l_ruleattribute_rec.end_date and
262 org_id=l_RuleAttribute_rec.org_id;
263 END IF;
264
265 l_ruleattribute_rec_pvt.object_name := l_ruleattribute_rec.object_name;
266 l_ruleattribute_rec_pvt.not_flag := l_ruleattribute_rec.not_flag;
267 l_ruleattribute_rec_pvt.value_1 := l_ruleattribute_rec.value_1;
268 l_ruleattribute_rec_pvt.value_2 := l_ruleattribute_rec.value_2;
269 l_ruleattribute_rec_pvt.data_flag := l_ruleattribute_rec.data_flag;
270 l_ruleattribute_rec_pvt.org_id := l_RuleAttribute_rec.org_id;
271
272 FOR i IN get_rules(l_ruleattribute_rec_pvt.ruleset_id,l_ruleattribute_rec_pvt.org_id)
273 LOOP
274 l_ruleattribute_rec_pvt.rule_id := i.rule_id;
275
276 cn_ruleattribute_pvt.create_ruleattribute
277 (p_api_version => p_api_version,
278 p_init_msg_list => p_init_msg_list,
279 p_commit => p_commit,
280 p_validation_level => p_validation_level,
281 x_return_status => x_return_status,
282 x_msg_count => x_msg_count,
283 x_msg_data => x_msg_data,
284 x_loading_status => x_loading_status,
285 p_ruleattribute_rec => l_ruleattribute_rec_pvt);
286
287 IF x_return_status = fnd_api.g_ret_sts_error
288 THEN
289 RAISE fnd_api.g_exc_error;
290 ELSIF x_return_status = fnd_api.g_ret_sts_unexp_error
291 THEN
292 RAISE fnd_api.g_exc_unexpected_error;
293 END IF;
294 END LOOP;
295
296 --
297 -- End of API body.
298 --
299
300 --
301 -- Post processing hooks
302 --
303
304 -- SK Start of post processing hooks
305
306 -- vertical post processing section
307 IF JTF_USR_HKS.Ok_to_Execute('CN_RULEATTRIBUTE_PUB',
308 'CREATE_RULEATTRIBUTE',
309 'A',
310 'V')
311 THEN
312 cn_ruleattribute_pub_vuhk.create_ruleattribute_post
313 (p_api_version => p_api_version,
314 p_init_msg_list => p_init_msg_list,
315 p_commit => p_commit,
316 p_validation_level => p_validation_level,
317 x_return_status => x_return_status,
318 x_msg_count => x_msg_count,
319 x_msg_data => x_msg_data,
320 x_loading_status => x_loading_status,
321 p_ruleattribute_rec => l_ruleattribute_rec);
322
323 IF x_return_status = fnd_api.g_ret_sts_error
324 THEN
325 RAISE fnd_api.g_exc_error;
326 ELSIF x_return_status = fnd_api.g_ret_sts_unexp_error
327 THEN
328 RAISE fnd_api.g_exc_unexpected_error;
329 END IF;
330 END IF;
331
332 -- customer post processing section
333 IF JTF_USR_HKS.Ok_to_Execute('CN_RULEATTRIBUTE_PUB',
334 'CREATE_RULEATTRIBUTE',
335 'A',
336 'C')
337 THEN
338 cn_ruleattribute_pub_cuhk.create_ruleattribute_post
339 (p_api_version => p_api_version,
340 p_init_msg_list => p_init_msg_list,
341 p_commit => p_commit,
342 p_validation_level => p_validation_level,
343 x_return_status => x_return_status,
344 x_msg_count => x_msg_count,
345 x_msg_data => x_msg_data,
346 x_loading_status => x_loading_status,
347 p_ruleattribute_rec => l_ruleattribute_rec);
348
349 IF x_return_status = fnd_api.g_ret_sts_error
350 THEN
351 RAISE fnd_api.g_exc_error;
352 ELSIF x_return_status = fnd_api.g_ret_sts_unexp_error
353 THEN
354 RAISE fnd_api.g_exc_unexpected_error;
355 END IF;
356 END IF;
357
358 -- Message generation section.
359 IF JTF_USR_HKS.Ok_to_Execute('CN_RULEATTRIBUTE_PUB',
360 'CREATE_RULEATTRIBUTE',
361 'M',
362 'M')
363 THEN
364 IF cn_ruleattribute_pub_cuhk.ok_to_generate_msg
365 (p_ruleattribute_rec => l_ruleattribute_rec)
366 THEN
367 -- Clear bind variables
368 -- XMLGEN.clearBindValues;
369
370 -- Set values for bind variables,
371 -- call this for all bind variables in the business object
372 -- XMLGEN.setBindValue('RULESET_NAME', l_ruleattribute_rec.ruleset_name);
373
374 -- get ID for all the bind_variables in a Business Object.
375 l_bind_data_id := JTF_USR_HKS.get_bind_data_id;
376
377 JTF_USR_HKS.load_bind_data(l_bind_data_id, 'RULESET_NAME', l_ruleattribute_rec.ruleset_name, 'S', 'T');
378
379 -- Message generation API
380 JTF_USR_HKS.generate_message
381 (p_prod_code => 'CN',
382 p_bus_obj_code => 'CRT_RUAT',
383 p_action_code => 'I',
384 p_bind_data_id => l_bind_data_id,
385 x_return_code => l_return_code) ;
386
387 IF (l_return_code = FND_API.G_RET_STS_ERROR)
388 THEN
389 RAISE FND_API.G_EXC_ERROR;
390 ELSIF (l_return_code = FND_API.G_RET_STS_UNEXP_ERROR )
391 THEN
392 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
393 END IF;
394
395 /*
396 -- Message generation API
397 JTF_USR_HKS.generate_message
398 (p_prod_code => 'CN',
399 p_bus_obj_code => 'CRT_RUAT',
400 p_bus_obj_name => 'RATTR',
401 p_action_code => 'I',
402 p_oai_param => null,
403 p_oai_array => l_oai_array,
404 x_return_code => x_return_status) ;
405
406 IF (x_return_status = FND_API.G_RET_STS_ERROR)
407 THEN
408 RAISE FND_API.G_EXC_ERROR;
409 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR )
410 THEN
411 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
412 END IF;
413 */
414
415 END IF;
416 END IF;
417
418
419 -- Standard check of p_commit.
420 IF FND_API.To_Boolean( p_commit )
421 THEN
422 COMMIT WORK;
423 END IF;
424
425 -- Standard call to get message count and if count is 1, get message info.
426 FND_MSG_PUB.Count_And_Get
427 (p_count => x_msg_count,
428 p_data => x_msg_data
429 );
430 EXCEPTION
431 WHEN FND_API.G_EXC_ERROR THEN
432 ROLLBACK TO Create_RuleAttribute;
433 x_return_status := FND_API.G_RET_STS_ERROR ;
434 FND_MSG_PUB.Count_And_Get
435 (p_count => x_msg_count,
436 p_data => x_msg_data,
437 p_encoded => fnd_api.g_false
438 );
439 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
440 ROLLBACK TO Create_RuleAttribute;
441 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
442 x_loading_status := 'UNEXPECTED_ERR';
443 FND_MSG_PUB.Count_And_Get
444 (p_count => x_msg_count,
445 p_data => x_msg_data,
446 p_encoded => fnd_api.g_false
447 );
448 WHEN OTHERS THEN
449 ROLLBACK TO Create_RuleAttribute;
450 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
451 x_loading_status := 'UNEXPECTED_ERR';
452 IF FND_MSG_PUB.Check_Msg_Level
453 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
454 THEN
455 FND_MSG_PUB.Add_Exc_Msg
456 (G_PKG_NAME,l_api_name);
457 END IF;
458 FND_MSG_PUB.Count_And_Get
459 (p_count => x_msg_count,
460 p_data => x_msg_data,
461 p_encoded => fnd_api.g_false
462 );
463 END Create_RuleAttribute;
464
465
466
467 -- Start of comments
468 -- API name : Update_RuleAttribute
469 -- Type : Public
470 -- Function : This Public API can be used to update a rule,
471 -- a ruleset or rule attributes in Oracle Sales
472 -- Compensation.
473 -- Pre-reqs : None.
474 -- Parameters :
475 -- IN : p_api_version IN NUMBER Required
476 -- p_init_msg_list IN VARCHAR2 Optional
477 -- Default = FND_API.G_FALSE
478 -- p_commit IN VARCHAR2 Optional
479 -- Default = FND_API.G_FALSE
480 -- p_validation_level IN NUMBER Optional
481 -- Default = FND_API.G_VALID_LEVEL_FULL
482 -- p_rule_rec_type IN
483 -- CN_RuleAttribute_PUB.rule_rec_type
484 -- p_RuleAttribute_rec_type IN
485 -- CN_RuleAttribute_PUB.RuleAttribute_rec_type
486 --
487 -- OUT : x_return_status OUT VARCHAR2(1)
488 -- x_msg_count OUT NUMBER
489 -- x_msg_data OUT VARCHAR2(2000)
490 --
491 -- Version : Current version 1.0
492 -- 25-Mar-99 Renu Chintalapati
493 -- previous version y.y
494 -- Changed....
495 -- Initial version 1.0
496 -- 25-Mar-99 Renu Chintalapati
497 --
498 -- Notes : Note text
499 --
500 -- End of comments
501
502
503 PROCEDURE Update_RuleAttribute
504 ( p_api_version IN NUMBER,
505 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
506 p_commit IN VARCHAR2 := FND_API.G_FALSE,
507 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
508 x_return_status OUT NOCOPY VARCHAR2,
509 x_msg_count OUT NOCOPY NUMBER,
510 x_msg_data OUT NOCOPY VARCHAR2,
511 x_loading_status OUT NOCOPY VARCHAR2,
512 p_old_RuleAttribute_rec IN CN_RuleAttribute_PUB.RuleAttribute_rec_type,
513 p_RuleAttribute_rec IN CN_RuleAttribute_PUB.RuleAttribute_rec_type
514 ) IS
515
516 l_api_name CONSTANT VARCHAR2(30) := 'Update_RuleAttribute';
517 l_api_version CONSTANT NUMBER := 1.0;
518 l_ruleattribute_rec_pvt cn_ruleattribute_pvt.ruleattribute_rec_type;
519 l_old_ruleattribute_rec_pvt cn_ruleattribute_pvt.ruleattribute_rec_type;
520 l_object_id cn_objects.object_id%TYPE;
521 l_old_object_id cn_objects.object_id%TYPE;
522 l_count NUMBER;
523
524 --
525 --Declaration for user hooks
526 --
527 l_OAI_array JTF_USR_HKS.oai_data_array_type;
528 l_old_RuleAttribute_rec CN_RuleAttribute_PUB.RuleAttribute_rec_type;
529 l_RuleAttribute_rec CN_RuleAttribute_PUB.RuleAttribute_rec_type;
530 l_bind_data_id NUMBER;
531 l_return_code VARCHAR2(1);
532
533
534 CURSOR get_rules(p_ruleset_id IN cn_rulesets.ruleset_id%TYPE,
535 p_rule_name IN cn_rules.name%TYPE,
536 p_org_id IN cn_rules.org_id%TYPE) IS
537 SELECT rule_id
538 FROM cn_rules
539 WHERE ruleset_id = p_ruleset_id
540 AND name = p_rule_name and
541 org_id=p_org_id;
542
543 CURSOR get_attribute_rules(p_rule_id IN cn_rules.rule_id%TYPE,
544 p_column_id IN
545 cn_attribute_rules.column_id%TYPE,
546 p_org_id IN cn_rules.org_id%TYPE) IS
547 SELECT attribute_rule_id
548 FROM cn_attribute_rules
549 WHERE rule_id = p_rule_id
550 AND column_id = p_column_id and
551 org_id=p_org_id;
552
553 BEGIN
554
555 -- Standard Start of API savepoint
556 SAVEPOINT Update_RuleAttribute;
557 -- Standard call to check for call compatibility.
558 IF NOT FND_API.Compatible_API_Call ( l_api_version,
559 p_api_version,
560 l_api_name,
561 G_PKG_NAME )
562 THEN
563 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
564 END IF;
565
566 -- Initialize message list if p_init_msg_list is set to TRUE.
567 IF FND_API.to_Boolean( p_init_msg_list )
568 THEN
569 FND_MSG_PUB.initialize;
570 END IF;
571
572 -- Initialize API return status to success
573 x_return_status := FND_API.G_RET_STS_SUCCESS;
574 x_loading_status := 'CN_UPDATED';
575
576
577 --
578 -- Assign the parameter to a local variable
579 --
580 l_old_RuleAttribute_rec := p_old_RuleAttribute_rec;
581 l_RuleAttribute_rec := p_RuleAttribute_rec;
582
583
584 --
585 -- User hooks
586 --
587
588 -- customer pre-processing section
589 IF JTF_USR_HKS.Ok_to_Execute('CN_RULEATTRIBUTE_PUB',
590 'UPDATE_RULEATTRIBUTE',
591 'B',
592 'C')
593 THEN
594 cn_ruleattribute_pub_cuhk.update_ruleattribute_pre
595 (p_api_version => p_api_version,
596 p_init_msg_list => p_init_msg_list,
597 p_commit => p_commit,
598 p_validation_level => p_validation_level,
599 x_return_status => x_return_status,
600 x_msg_count => x_msg_count,
601 x_msg_data => x_msg_data,
602 x_loading_status => x_loading_status,
603 p_ruleattribute_rec => l_ruleattribute_rec,
604 p_old_RuleAttribute_rec => l_old_RuleAttribute_rec);
605
606 IF x_return_status = fnd_api.g_ret_sts_error
607 THEN
608 RAISE fnd_api.g_exc_error;
609 ELSIF x_return_status = fnd_api.g_ret_sts_unexp_error
610 THEN
611 RAISE fnd_api.g_exc_unexpected_error;
612 END IF;
613 END IF;
614
615 -- vertical industry pre-processing section
616 IF JTF_USR_HKS.Ok_to_Execute('CN_RULEATTRIBUTE_PUB',
617 'UPDATE_RULEATTRIBUTE',
618 'B',
619 'V')
620 THEN
621 cn_ruleattribute_pub_vuhk.update_ruleattribute_pre
622 (p_api_version => p_api_version,
623 p_init_msg_list => p_init_msg_list,
624 p_commit => p_commit,
625 p_validation_level => p_validation_level,
626 x_return_status => x_return_status,
627 x_msg_count => x_msg_count,
628 x_msg_data => x_msg_data,
629 x_loading_status => x_loading_status,
630 p_ruleattribute_rec => l_ruleattribute_rec,
631 p_old_RuleAttribute_rec => l_old_RuleAttribute_rec);
632
633 IF x_return_status = fnd_api.g_ret_sts_error
634 THEN
635 RAISE fnd_api.g_exc_error;
636 ELSIF x_return_status = fnd_api.g_ret_sts_unexp_error
637 THEN
638 RAISE fnd_api.g_exc_unexpected_error;
639 END IF;
640 END IF;
641
642
643
644 --
645 -- API body
646 --
647
648 --Check for missing parameters in the p_rule_rec parameter
649 IF (cn_api.chk_miss_null_char_para
650 ( l_old_RuleAttribute_rec.ruleset_name,
651 cn_api.get_lkup_meaning('RULESET_NAME', 'RULESET_TYPE'),
652 x_loading_status,
653 x_loading_status) = FND_API.G_TRUE )
654 THEN
655 RAISE fnd_api.g_exc_error;
656 END IF;
657
658 IF (cn_api.chk_miss_null_date_para
659 ( l_old_RuleAttribute_rec.start_date,
660 cn_api.get_lkup_meaning('START_DATE', 'RULESET_TYPE'),
661 x_loading_status,
662 x_loading_status) = FND_API.G_TRUE )
663 THEN
664 RAISE fnd_api.g_exc_error;
665 END IF;
666
667 IF (cn_api.chk_miss_null_date_para
668 ( l_old_RuleAttribute_rec.end_date,
669 cn_api.get_lkup_meaning('END_DATE', 'RULESET_TYPE'),
670 x_loading_status,
671 x_loading_status) = FND_API.G_TRUE )
672 THEN
673 RAISE fnd_api.g_exc_error;
674 END IF;
675
676 IF (cn_api.chk_miss_null_char_para
677 ( l_old_RuleAttribute_rec.rule_name,
678 cn_api.get_lkup_meaning('RULE_NAME', 'RULESET_TYPE'),
679 x_loading_status,
680 x_loading_status) = FND_API.G_TRUE )
681 THEN
682 RAISE fnd_api.g_exc_error;
683 END IF;
684
685 IF (cn_api.chk_null_char_para
686 ( l_old_RuleAttribute_rec.object_name,
687 cn_api.get_lkup_meaning('OBJECT_NAME', 'RULESET_TYPE'),
688 x_loading_status,
689 x_loading_status) = FND_API.G_TRUE )
690 THEN
691 RAISE fnd_api.g_exc_error;
692 END IF;
693
694 IF (cn_api.chk_null_char_para
695 ( l_old_RuleAttribute_rec.data_flag,
696 cn_api.get_lkup_meaning('DATA_FLAG', 'RULESET_TYPE'),
697 x_loading_status,
698 x_loading_status) = FND_API.G_TRUE )
699 THEN
700 RAISE fnd_api.g_exc_error;
701 END IF;
702
703 --New parameters
704 IF (cn_api.chk_miss_null_char_para
705 ( l_RuleAttribute_rec.ruleset_name,
706 cn_api.get_lkup_meaning('RULESET_NAME', 'RULESET_TYPE'),
707 x_loading_status,
708 x_loading_status) = FND_API.G_TRUE )
709 THEN
710 RAISE fnd_api.g_exc_error;
711 END IF;
712
713 IF (cn_api.chk_miss_null_date_para
714 ( l_RuleAttribute_rec.start_date,
715 cn_api.get_lkup_meaning('START_DATE', 'RULESET_TYPE'),
716 x_loading_status,
717 x_loading_status) = FND_API.G_TRUE )
718 THEN
719 RAISE fnd_api.g_exc_error;
720 END IF;
721
722 IF (cn_api.chk_miss_null_date_para
723 ( l_RuleAttribute_rec.end_date,
724 cn_api.get_lkup_meaning('END_DATE', 'RULESET_TYPE'),
725 x_loading_status,
726 x_loading_status) = FND_API.G_TRUE )
727 THEN
728 RAISE fnd_api.g_exc_error;
729 END IF;
730
731 IF (cn_api.chk_miss_null_char_para
732 ( l_RuleAttribute_rec.rule_name,
733 cn_api.get_lkup_meaning('RULE_NAME', 'RULESET_TYPE'),
734 x_loading_status,
735 x_loading_status) = FND_API.G_TRUE )
736 THEN
737 RAISE fnd_api.g_exc_error;
738 END IF;
739
740 IF (cn_api.chk_null_char_para
741 ( l_RuleAttribute_rec.object_name,
742 cn_api.get_lkup_meaning('OBJECT_NAME', 'RULESET_TYPE'),
743 x_loading_status,
744 x_loading_status) = FND_API.G_TRUE )
745 THEN
746 RAISE fnd_api.g_exc_error;
747 END IF;
748
749 IF (cn_api.chk_null_char_para
750 ( l_RuleAttribute_rec.data_flag,
751 cn_api.get_lkup_meaning('DATA_FLAG', 'RULESET_TYPE'),
752 x_loading_status,
753 x_loading_status) = FND_API.G_TRUE )
754 THEN
755 RAISE fnd_api.g_exc_error;
756 END IF;
757
758 -- l_old_ruleattribute_rec.object_name := p_old_ruleattribute_rec.object_name;
759 SELECT object_id
760 INTO l_old_object_id
761 FROM cn_objects
762 WHERE name = l_old_RuleAttribute_rec.object_name
763 AND table_id = -11803;
764
765 -- l_ruleattribute_rec.object_name := l_ruleattribute_rec.object_name;
766 SELECT object_id
767 INTO l_object_id
768 FROM cn_objects
769 WHERE name = l_RuleAttribute_rec.object_name
770 AND table_id = -11803;
771
772 l_count := 0;
773
774 SELECT count(1)
775 INTO l_count
776 FROM cn_rulesets
777 WHERE name = l_ruleattribute_rec.ruleset_name
778 AND start_date = l_ruleattribute_rec.start_date
779 AND end_date = l_ruleattribute_rec.end_date;
780
781 IF l_count <> 1
782 THEN
783 --Error condition
784 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
785 THEN
786 fnd_message.set_name('CN', 'CN_INVALID_RULESET');
787 fnd_msg_pub.add;
788 END IF;
789 x_loading_status := 'CN_INVALID_RULESET';
790 RAISE FND_API.G_EXC_ERROR;
791 ELSE
792 SELECT ruleset_id
793 INTO l_ruleattribute_rec_pvt.ruleset_id
794 FROM cn_rulesets
795 WHERE name = l_ruleattribute_rec.ruleset_name
796 AND start_date = l_ruleattribute_rec.start_date
797 AND end_date = l_ruleattribute_rec.end_date;
798 END IF;
799
800 l_count := 0;
801
802 SELECT count(1)
803 INTO l_count
804 FROM cn_rulesets
805 WHERE name = l_old_ruleattribute_rec.ruleset_name
806 AND start_date = l_old_ruleattribute_rec.start_date
807 AND end_date = l_old_ruleattribute_rec.end_date;
808
809 IF l_count <> 1
810 THEN
811 --Error condition
812 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
813 THEN
814 fnd_message.set_name('CN', 'CN_INVALID_RULESET');
815 fnd_msg_pub.add;
816 END IF;
817 x_loading_status := 'CN_INVALID_RULESET';
818 RAISE FND_API.G_EXC_ERROR;
819 ELSE
820 SELECT ruleset_id
821 INTO l_old_ruleattribute_rec_pvt.ruleset_id
822 FROM cn_rulesets
823 WHERE name = l_old_ruleattribute_rec.ruleset_name
824 AND start_date = l_old_ruleattribute_rec.start_date
825 AND end_date = l_old_ruleattribute_rec.end_date and
826 org_id=l_old_ruleattribute_rec.org_id;
827 END IF;
828
829 l_ruleattribute_rec_pvt.object_name := l_ruleattribute_rec.object_name;
830 l_ruleattribute_rec_pvt.org_id := l_ruleattribute_rec.org_id;
831 l_old_ruleattribute_rec_pvt.object_name := l_old_ruleattribute_rec.object_name;
832 l_old_ruleattribute_rec_pvt.not_flag := l_old_ruleattribute_rec.not_flag;
833 l_old_ruleattribute_rec_pvt.value_1 := l_old_ruleattribute_rec.value_1;
834 l_old_ruleattribute_rec_pvt.value_2 := l_old_ruleattribute_rec.value_2;
835 l_old_ruleattribute_rec_pvt.data_flag := l_old_ruleattribute_rec.data_flag;
836 l_old_ruleattribute_rec_pvt.org_id := l_old_ruleattribute_rec.org_id;
837
838 l_ruleattribute_rec_pvt.not_flag := l_ruleattribute_rec.not_flag;
839 l_ruleattribute_rec_pvt.value_1 := l_ruleattribute_rec.value_1;
840 l_ruleattribute_rec_pvt.value_2 := l_ruleattribute_rec.value_2;
841 l_ruleattribute_rec_pvt.data_flag := l_ruleattribute_rec.data_flag;
842
843
844 FOR i IN get_rules(l_ruleattribute_rec_pvt.ruleset_id,
845 l_old_ruleattribute_rec.rule_name,
846 l_old_ruleattribute_rec.org_id)
847 LOOP
848
849 l_ruleattribute_rec_pvt.rule_id := i.rule_id;
850 l_old_ruleattribute_rec_pvt.rule_id := i.rule_id;
851
852 -- Fixed code based on bug# 1320242 as the update record was
853 -- giving error as multiple records are found with a single select below
854
855 /*
856 SELECT attribute_rule_id
857 INTO l_ruleattribute_rec_pvt.attribute_rule_id
858 FROM cn_attribute_rules
859 WHERE rule_id = i.rule_id
860 AND column_id = l_object_id;
861
862 SELECT attribute_rule_id
863 INTO l_old_ruleattribute_rec_pvt.attribute_rule_id
864 FROM cn_attribute_rules
865 WHERE rule_id = i.rule_id
866 AND column_id = l_old_object_id;
867 */
868
869 FOR j IN get_attribute_rules(i.rule_id, l_old_object_id,l_old_ruleattribute_rec.org_id)
870 LOOP
871 l_ruleattribute_rec_pvt.attribute_rule_id := j.attribute_rule_id;
872 l_old_ruleattribute_rec_pvt.attribute_rule_id := j.attribute_rule_id;
873
874 cn_ruleattribute_pvt.update_ruleattribute
875 (p_api_version => p_api_version,
876 p_init_msg_list => p_init_msg_list,
877 p_commit => p_commit,
878 p_validation_level => p_validation_level,
879 x_return_status => x_return_status,
880 x_msg_count => x_msg_count,
881 x_msg_data => x_msg_data,
882 x_loading_status => x_loading_status,
883 p_ruleattribute_rec => l_ruleattribute_rec_pvt,
884 p_old_ruleattribute_rec => l_old_ruleattribute_rec_pvt);
885
886 IF x_return_status = fnd_api.g_ret_sts_error
887 THEN
888 RAISE fnd_api.g_exc_error;
889 ELSIF x_return_status = fnd_api.g_ret_sts_unexp_error
890 THEN
891 RAISE fnd_api.g_exc_unexpected_error;
892 END IF;
893 END LOOP;
894 END LOOP;
895
896 --
897 -- End of API body.
898 --
899
900 --
901 -- Post processing hooks
902 --
903
904 -- SK Start of post processing hooks
905
906 -- vertical industry pre-processing section
907 IF JTF_USR_HKS.Ok_to_Execute('CN_RULEATTRIBUTE_PUB',
908 'UPDATE_RULEATTRIBUTE',
909 'A',
910 'V')
911 THEN
912 cn_ruleattribute_pub_vuhk.update_ruleattribute_post
913 (p_api_version => p_api_version,
914 p_init_msg_list => p_init_msg_list,
915 p_commit => p_commit,
916 p_validation_level => p_validation_level,
917 x_return_status => x_return_status,
918 x_msg_count => x_msg_count,
919 x_msg_data => x_msg_data,
920 x_loading_status => x_loading_status,
921 p_ruleattribute_rec => l_ruleattribute_rec,
922 p_old_RuleAttribute_rec => l_old_RuleAttribute_rec);
923
924 IF x_return_status = fnd_api.g_ret_sts_error
925 THEN
926 RAISE fnd_api.g_exc_error;
927 ELSIF x_return_status = fnd_api.g_ret_sts_unexp_error
928 THEN
929 RAISE fnd_api.g_exc_unexpected_error;
930 END IF;
931 END IF;
932
933
934 -- customer pre-processing section
935 IF JTF_USR_HKS.Ok_to_Execute('CN_RULEATTRIBUTE_PUB',
936 'UPDATE_RULEATTRIBUTE',
937 'A',
938 'C')
939 THEN
940 cn_ruleattribute_pub_cuhk.update_ruleattribute_post
941 (p_api_version => p_api_version,
942 p_init_msg_list => p_init_msg_list,
943 p_commit => p_commit,
944 p_validation_level => p_validation_level,
945 x_return_status => x_return_status,
946 x_msg_count => x_msg_count,
947 x_msg_data => x_msg_data,
948 x_loading_status => x_loading_status,
949 p_ruleattribute_rec => l_ruleattribute_rec,
950 p_old_RuleAttribute_rec => l_old_RuleAttribute_rec);
951
952 IF x_return_status = fnd_api.g_ret_sts_error
953 THEN
954 RAISE fnd_api.g_exc_error;
955 ELSIF x_return_status = fnd_api.g_ret_sts_unexp_error
956 THEN
957 RAISE fnd_api.g_exc_unexpected_error;
958 END IF;
959 END IF;
960 -- SK End of post processing hooks
961
962
963 -- Message generation section.
964 IF JTF_USR_HKS.Ok_to_Execute('CN_RULEATTRIBUTE_PUB',
965 'UPDATE_RULEATTRIBUTE',
966 'M',
967 'M')
968 THEN
969 IF cn_ruleattribute_pub_cuhk.ok_to_generate_msg
970 (p_ruleattribute_rec => l_ruleattribute_rec)
971 THEN
972 -- Clear bind variables
973 -- XMLGEN.clearBindValues;
974
975 -- Set values for bind variables,
976 -- call this for all bind variables in the business object
977 -- XMLGEN.setBindValue('RULESET_NAME', l_ruleattribute_rec.ruleset_name);
978
979 -- get ID for all the bind_variables in a Business Object.
980 l_bind_data_id := JTF_USR_HKS.get_bind_data_id;
981
982 JTF_USR_HKS.load_bind_data(l_bind_data_id, 'RULESET_NAME', l_ruleattribute_rec.ruleset_name, 'S', 'T');
983
984 -- Message generation API
985 JTF_USR_HKS.generate_message
986 (p_prod_code => 'CN',
987 p_bus_obj_code => 'UPD_RUAT',
988 p_action_code => 'U',
989 p_bind_data_id => l_bind_data_id,
990 x_return_code => l_return_code) ;
991
992 IF (l_return_code = FND_API.G_RET_STS_ERROR)
993 THEN
994 RAISE FND_API.G_EXC_ERROR;
995 ELSIF (l_return_code = FND_API.G_RET_STS_UNEXP_ERROR )
996 THEN
997 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
998 END IF;
999
1000 /*
1001 -- Message generation API
1002 JTF_USR_HKS.generate_message
1003 (p_prod_code => 'CN',
1004 p_bus_obj_code => 'UPD_RUAT',
1005 p_bus_obj_name => 'RATTR',
1006 p_action_code => 'I',
1007 p_oai_param => null,
1008 p_oai_array => l_oai_array,
1009 x_return_code => x_return_status) ;
1010
1011 IF (x_return_status = FND_API.G_RET_STS_ERROR)
1012 THEN
1013 RAISE FND_API.G_EXC_ERROR;
1014 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR )
1015 THEN
1016 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1017 END IF;
1018 */
1019 END IF;
1020 END IF;
1021
1022
1023 -- Standard check of p_commit.
1024 IF FND_API.To_Boolean( p_commit )
1025 THEN
1026 COMMIT WORK;
1027 END IF;
1028
1029 -- Standard call to get message count and if count is 1, get message info.
1030 FND_MSG_PUB.Count_And_Get
1031 (p_count => x_msg_count,
1032 p_data => x_msg_data
1033 );
1034 EXCEPTION
1035 WHEN FND_API.G_EXC_ERROR THEN
1036 ROLLBACK TO Update_RuleAttribute;
1037 x_return_status := FND_API.G_RET_STS_ERROR ;
1038 FND_MSG_PUB.Count_And_Get
1039 (p_count => x_msg_count,
1040 p_data => x_msg_data,
1041 p_encoded => fnd_api.g_false
1042 );
1043 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1044 ROLLBACK TO Update_RuleAttribute;
1045 x_loading_status := 'UNEXPECTED_ERR';
1046 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1047 FND_MSG_PUB.Count_And_Get
1048 (p_count => x_msg_count,
1049 p_data => x_msg_data,
1050 p_encoded => fnd_api.g_false
1051 );
1052 WHEN OTHERS THEN
1053 ROLLBACK TO Update_RuleAttribute;
1054 x_loading_status := 'UNEXPECTED_ERR';
1055 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1056 IF FND_MSG_PUB.Check_Msg_Level
1057 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1058 THEN
1059 FND_MSG_PUB.Add_Exc_Msg
1060 (G_PKG_NAME,
1061 l_api_name
1062 );
1063 END IF;
1064 FND_MSG_PUB.Count_And_Get
1065 (p_count => x_msg_count,
1066 p_data => x_msg_data,
1067 p_encoded => fnd_api.g_false
1068 );
1069
1070 END;
1071
1072 -- Start of comments
1073 -- API name : Delete_RuleAttribute
1074 -- Type : Public
1075 -- Function : This Public API can be used to delete a rule or
1076 -- it's attributes from Oracle Sales Compensation.
1077 -- Pre-reqs : None.
1078 -- Parameters :
1079 -- IN : p_api_version IN NUMBER Required
1080 -- p_init_msg_list IN VARCHAR2 Optional
1081 -- Default = FND_API.G_FALSE
1082 -- p_commit IN VARCHAR2 Optional
1083 -- Default = FND_API.G_FALSE
1084 -- p_validation_level IN NUMBER Optional
1085 -- Default = FND_API.G_VALID_LEVEL_FULL
1086 -- p_rule_rec_type IN
1087 -- CN_RuleAttribute_PUB.rule_rec_type
1088 -- p_rule_attr_rec_type IN
1089 -- CN_RuleAttribute_PUB.rule_attr_rec_type
1090 --
1091 -- OUT : x_return_status OUT VARCHAR2(1)
1092 -- x_msg_count OUT NUMBER
1093 -- x_msg_data OUT VARCHAR2(2000)
1094 --
1095 -- Version : Current version 1.0
1096 -- 25-Mar-99 Renu Chintalapati
1097 -- previous version y.y
1098 -- Changed....
1099 -- Initial version 1.0
1100 -- 25-Mar-99 Renu Chintalapati
1101 --
1102 -- Notes : This can be used to delete rules (and thus
1103 -- their rule attributes).
1104 -- Mandatory parameters are ruleset id, rule id
1105 -- and attribute_rule_id
1106 --
1107 -- End of comments
1108
1109
1110 PROCEDURE Delete_RuleAttribute
1111 ( p_api_version IN NUMBER,
1112 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1113 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1114 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1115 x_return_status OUT NOCOPY VARCHAR2,
1116 x_msg_count OUT NOCOPY NUMBER,
1117 x_msg_data OUT NOCOPY VARCHAR2,
1118 x_loading_status OUT NOCOPY VARCHAR2,
1119 p_ruleattribute_rec IN CN_RuleAttribute_PUB.ruleattribute_rec_type
1120 ) IS
1121
1122
1123 l_api_name CONSTANT VARCHAR2(30) := 'Delete_RuleAttribute';
1124 l_api_version CONSTANT NUMBER := 1.0;
1125 l_ruleattribute_rec_pvt cn_ruleattribute_pvt.ruleattribute_rec_type;
1126 l_count NUMBER;
1127 l_object_id cn_objects.object_id%TYPE;
1128
1129 CURSOR get_rules
1130 (p_ruleset_id IN cn_rulesets.ruleset_id%TYPE,
1131 p_rule_name IN cn_rules.name%TYPE,
1132 p_org_id IN cn_rules.org_id%TYPE) IS
1133 SELECT rule_id
1134 FROM cn_rules
1135 WHERE ruleset_id = p_ruleset_id
1136 AND name = p_rule_name
1137 AND org_id = p_org_id;
1138
1139 CURSOR get_attribute_rules(p_rule_id IN cn_rules.rule_id%TYPE,
1140 p_column_id IN cn_attribute_rules.column_id%TYPE,
1141 p_org_id IN cn_rules.org_id%TYPE) IS
1142 SELECT attribute_rule_id,object_version_number
1143 FROM cn_attribute_rules
1144 WHERE rule_id = p_rule_id
1145 AND column_id = p_column_id
1146 AND org_id = p_org_id;
1147 --
1148 -- Declaration for user hooks
1149 --
1150 l_OAI_array JTF_USR_HKS.oai_data_array_type;
1151 l_ruleattribute_rec CN_RuleAttribute_PUB.RuleAttribute_rec_type;
1152 l_bind_data_id NUMBER;
1153 l_return_code VARCHAR2(1);
1154
1155
1156 BEGIN
1157
1158 -- Standard Start of API savepoint
1159 SAVEPOINT Delete_RuleAttribute;
1160 -- Standard call to check for call compatibility.
1161 IF NOT FND_API.Compatible_API_Call ( l_api_version,
1162 p_api_version,
1163 l_api_name,
1164 G_PKG_NAME )
1165 THEN
1166 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1167 END IF;
1168
1169 -- Initialize message list if p_init_msg_list is set to TRUE.
1170 IF FND_API.to_Boolean( p_init_msg_list )
1171 THEN
1172 FND_MSG_PUB.initialize;
1173 END IF;
1174
1175 -- Initialize API return status to success
1176 x_return_status := FND_API.G_RET_STS_SUCCESS;
1177
1178
1179 --
1180 -- Assign the parameter to a local variable
1181 --
1182 l_ruleattribute_rec := p_ruleattribute_rec;
1183
1184
1185 --
1186 -- User hooks
1187 --
1188
1189 -- customer pre-processing section
1190 IF JTF_USR_HKS.Ok_to_Execute('CN_RULEATTRIBUTE_PUB',
1191 'DELETE_RULEATTRIBUTE',
1192 'B',
1193 'C')
1194 THEN
1195 cn_ruleattribute_pub_cuhk.delete_ruleattribute_pre
1196 (p_api_version => p_api_version,
1197 p_init_msg_list => p_init_msg_list,
1198 p_commit => p_commit,
1199 p_validation_level => p_validation_level,
1200 x_return_status => x_return_status,
1201 x_msg_count => x_msg_count,
1202 x_msg_data => x_msg_data,
1203 x_loading_status => x_loading_status,
1204 p_ruleattribute_rec => l_ruleattribute_rec);
1205
1206 IF x_return_status = fnd_api.g_ret_sts_error
1207 THEN
1208 RAISE fnd_api.g_exc_error;
1209 ELSIF x_return_status = fnd_api.g_ret_sts_unexp_error
1210 THEN
1211 RAISE fnd_api.g_exc_unexpected_error;
1212 END IF;
1213 END IF;
1214
1215 -- vertical industry pre-processing section
1216 IF JTF_USR_HKS.Ok_to_Execute('CN_RULEATTRIBUTE_PUB',
1217 'DELETE_RULEATTRIBUTE',
1218 'B',
1219 'V')
1220 THEN
1221 cn_ruleattribute_pub_vuhk.delete_ruleattribute_pre
1222 (p_api_version => p_api_version,
1223 p_init_msg_list => p_init_msg_list,
1224 p_commit => p_commit,
1225 p_validation_level => p_validation_level,
1226 x_return_status => x_return_status,
1227 x_msg_count => x_msg_count,
1228 x_msg_data => x_msg_data,
1229 x_loading_status => x_loading_status,
1230 p_ruleattribute_rec => l_ruleattribute_rec);
1231
1232 IF x_return_status = fnd_api.g_ret_sts_error
1233 THEN
1234 RAISE fnd_api.g_exc_error;
1235 ELSIF x_return_status = fnd_api.g_ret_sts_unexp_error
1236 THEN
1237 RAISE fnd_api.g_exc_unexpected_error;
1238 END IF;
1239 END IF;
1240
1241
1242 --
1243 -- API body
1244 --
1245
1246 IF (cn_api.chk_miss_null_char_para
1247 ( l_RuleAttribute_rec.ruleset_name,
1248 cn_api.get_lkup_meaning('RULESET_NAME', 'RULESET_TYPE'),
1249 x_loading_status,
1250 x_loading_status) = FND_API.G_TRUE )
1251 THEN
1252 RAISE fnd_api.g_exc_error;
1253 END IF;
1254
1255 IF (cn_api.chk_miss_null_date_para
1256 ( l_RuleAttribute_rec.start_date,
1257 cn_api.get_lkup_meaning('START_DATE', 'RULESET_TYPE'),
1258 x_loading_status,
1259 x_loading_status) = FND_API.G_TRUE )
1260 THEN
1261 RAISE fnd_api.g_exc_error;
1262 END IF;
1263
1264 IF (cn_api.chk_miss_null_date_para
1265 ( l_RuleAttribute_rec.end_date,
1266 cn_api.get_lkup_meaning('END_DATE', 'RULESET_TYPE'),
1267 x_loading_status,
1268 x_loading_status) = FND_API.G_TRUE )
1269 THEN
1270 RAISE fnd_api.g_exc_error;
1271 END IF;
1272
1273 IF (cn_api.chk_miss_null_char_para
1274 ( l_RuleAttribute_rec.rule_name,
1275 cn_api.get_lkup_meaning('RULE_NAME', 'RULESET_TYPE'),
1276 x_loading_status,
1277 x_loading_status) = FND_API.G_TRUE )
1278 THEN
1279 RAISE fnd_api.g_exc_error;
1280 END IF;
1281
1282 IF (cn_api.chk_null_char_para
1283 ( l_RuleAttribute_rec.object_name,
1284 cn_api.get_lkup_meaning('OBJECT_NAME', 'RULESET_TYPE'),
1285 x_loading_status,
1286 x_loading_status) = FND_API.G_TRUE )
1287 THEN
1288 RAISE fnd_api.g_exc_error;
1289 END IF;
1290
1291 IF (cn_api.chk_null_char_para
1292 ( l_RuleAttribute_rec.data_flag,
1293 cn_api.get_lkup_meaning('DATA_FLAG', 'RULESET_TYPE'),
1294 x_loading_status,
1295 x_loading_status) = FND_API.G_TRUE )
1296 THEN
1297 RAISE fnd_api.g_exc_error;
1298 END IF;
1299
1300 -- l_ruleattribute_rec.object_name := p_ruleattribute_rec.object_name;
1301 SELECT object_id
1302 INTO l_object_id
1303 FROM cn_objects
1304 WHERE name = l_RuleAttribute_rec.object_name
1305 AND table_id = -11803
1306 and org_id = l_RuleAttribute_rec.org_id;
1307
1308 l_count := 0;
1309
1310 SELECT count(1)
1311 INTO l_count
1312 FROM cn_rulesets
1313 WHERE name = l_ruleattribute_rec.ruleset_name
1314 AND start_date = l_ruleattribute_rec.start_date
1315 AND end_date = l_ruleattribute_rec.end_date;
1316
1317 IF l_count <> 1
1318 THEN
1319 --Error condition
1320 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
1321 THEN
1322 fnd_message.set_name('CN', 'CN_INVALID_RULESET');
1323 fnd_msg_pub.add;
1324 END IF;
1325 x_loading_status := 'CN_INVALID_RULESET';
1326 RAISE FND_API.G_EXC_ERROR;
1327 ELSE
1328 SELECT ruleset_id
1329 INTO l_ruleattribute_rec_pvt.ruleset_id
1330 FROM cn_rulesets
1331 WHERE name = l_ruleattribute_rec.ruleset_name
1332 AND start_date = l_ruleattribute_rec.start_date
1333 AND end_date = l_ruleattribute_rec.end_date;
1334 END IF;
1335
1336 l_ruleattribute_rec_pvt.not_flag := l_ruleattribute_rec.not_flag;
1337 l_ruleattribute_rec_pvt.value_1 := l_ruleattribute_rec.value_1;
1338 l_ruleattribute_rec_pvt.value_2 := l_ruleattribute_rec.value_2;
1339 l_ruleattribute_rec_pvt.data_flag := l_ruleattribute_rec.data_flag;
1340 l_ruleattribute_rec_pvt.org_id := l_ruleattribute_rec.org_id;
1341
1342
1343 FOR i IN get_rules(l_ruleattribute_rec_pvt.ruleset_id,
1344 l_ruleattribute_rec.rule_name,
1345 l_ruleattribute_rec.org_id)
1346 LOOP
1347 l_ruleattribute_rec_pvt.rule_id := i.rule_id;
1348
1349 -- Fixed code based on bug# 1320242 as the update record was
1350 -- giving error as multiple records are found with a single select below
1351
1352 /*
1353 SELECT attribute_rule_id
1354 INTO l_ruleattribute_rec_pvt.attribute_rule_id
1355 FROM cn_attribute_rules
1356 WHERE rule_id = i.rule_id
1357 AND column_id = l_object_id;
1358 */
1359
1360 FOR j IN get_attribute_rules(i.rule_id, l_object_id,l_ruleattribute_rec_pvt.org_id)
1361 LOOP
1362 l_ruleattribute_rec_pvt.attribute_rule_id := j.attribute_rule_id;
1363 l_ruleattribute_rec_pvt.object_version_number := j.object_version_number;
1364 cn_ruleattribute_pvt.delete_ruleattribute
1365 (p_api_version => p_api_version,
1366 p_init_msg_list => p_init_msg_list,
1367 p_commit => p_commit,
1368 p_validation_level => p_validation_level,
1369 x_return_status => x_return_status,
1370 x_msg_count => x_msg_count,
1371 x_msg_data => x_msg_data,
1372 x_loading_status => x_loading_status,
1373 p_ruleset_id => l_ruleattribute_rec_pvt.ruleset_id,
1374 p_rule_id => l_ruleattribute_rec_pvt.rule_id,
1375 p_object_version_number => l_ruleattribute_rec_pvt.object_version_number,
1376 p_attribute_rule_id => l_ruleattribute_rec_pvt.attribute_rule_id);
1377
1378 IF x_return_status = fnd_api.g_ret_sts_error
1379 THEN
1380 RAISE fnd_api.g_exc_error;
1381 ELSIF x_return_status = fnd_api.g_ret_sts_unexp_error
1382 THEN
1383 RAISE fnd_api.g_exc_unexpected_error;
1384 END IF;
1385 END LOOP;
1386 END LOOP;
1387
1388 --
1389 -- End of API body.
1390 --
1391
1392
1393 --
1394 -- Post processing hooks
1395 --
1396
1397 -- SK Start of post processing hooks
1398
1399 -- vertical post processing section
1400 IF JTF_USR_HKS.Ok_to_Execute('CN_RULEATTRIBUTE_PUB',
1401 'DELETE_RULEATTRIBUTE',
1402 'A',
1403 'V')
1404 THEN
1405 cn_ruleattribute_pub_vuhk.delete_ruleattribute_pre
1406 (p_api_version => p_api_version,
1407 p_init_msg_list => p_init_msg_list,
1408 p_commit => p_commit,
1409 p_validation_level => p_validation_level,
1410 x_return_status => x_return_status,
1411 x_msg_count => x_msg_count,
1412 x_msg_data => x_msg_data,
1413 x_loading_status => x_loading_status,
1414 p_ruleattribute_rec => l_ruleattribute_rec);
1415
1416 IF x_return_status = fnd_api.g_ret_sts_error
1417 THEN
1418 RAISE fnd_api.g_exc_error;
1419 ELSIF x_return_status = fnd_api.g_ret_sts_unexp_error
1420 THEN
1421 RAISE fnd_api.g_exc_unexpected_error;
1422 END IF;
1423 END IF;
1424
1425 -- customer post processing section
1426 IF JTF_USR_HKS.Ok_to_Execute('CN_RULEATTRIBUTE_PUB',
1427 'DELETE_RULEATTRIBUTE',
1428 'A',
1429 'C')
1430 THEN
1431 cn_ruleattribute_pub_cuhk.delete_ruleattribute_pre
1432 (p_api_version => p_api_version,
1433 p_init_msg_list => p_init_msg_list,
1434 p_commit => p_commit,
1435 p_validation_level => p_validation_level,
1436 x_return_status => x_return_status,
1437 x_msg_count => x_msg_count,
1438 x_msg_data => x_msg_data,
1439 x_loading_status => x_loading_status,
1440 p_ruleattribute_rec => l_ruleattribute_rec);
1441
1442 IF x_return_status = fnd_api.g_ret_sts_error
1443 THEN
1444 RAISE fnd_api.g_exc_error;
1445 ELSIF x_return_status = fnd_api.g_ret_sts_unexp_error
1446 THEN
1447 RAISE fnd_api.g_exc_unexpected_error;
1448 END IF;
1449 END IF;
1450
1451 -- Message generation section.
1452 IF JTF_USR_HKS.Ok_to_Execute('CN_RULEATTRIBUTE_PUB',
1453 'DELETE_RULEATTRIBUTE',
1454 'M',
1455 'M')
1456 THEN
1457 IF cn_ruleattribute_pub_cuhk.ok_to_generate_msg
1458 (p_ruleattribute_rec => l_ruleattribute_rec)
1459 THEN
1460 -- Clear bind variables
1461 -- XMLGEN.clearBindValues;
1462
1463 -- Set values for bind variables,
1464 -- call this for all bind variables in the business object
1465 -- XMLGEN.setBindValue('RULESET_NAME', l_ruleattribute_rec.ruleset_name);
1466
1467 -- get ID for all the bind_variables in a Business Object.
1468 l_bind_data_id := JTF_USR_HKS.get_bind_data_id;
1469
1470 JTF_USR_HKS.load_bind_data(l_bind_data_id, 'RULESET_NAME', l_ruleattribute_rec.ruleset_name, 'S', 'T');
1471
1472 -- Message generation API
1473 JTF_USR_HKS.generate_message
1474 (p_prod_code => 'CN',
1475 p_bus_obj_code => 'DEL_RUAT',
1476 p_action_code => 'D',
1477 p_bind_data_id => l_bind_data_id,
1478 x_return_code => l_return_code) ;
1479
1480 IF (l_return_code = FND_API.G_RET_STS_ERROR)
1481 THEN
1482 RAISE FND_API.G_EXC_ERROR;
1483 ELSIF (l_return_code = FND_API.G_RET_STS_UNEXP_ERROR )
1484 THEN
1485 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1486 END IF;
1487
1488 /*
1489 -- Message generation API
1490 JTF_USR_HKS.generate_message
1491 (p_prod_code => 'CN',
1492 p_bus_obj_code => 'DEL_RUAT',
1493 p_bus_obj_name => 'RATTR',
1494 p_action_code => 'I',
1495 p_oai_param => null,
1496 p_oai_array => l_oai_array,
1497 x_return_code => x_return_status) ;
1498
1499 IF (x_return_status = FND_API.G_RET_STS_ERROR)
1500 THEN
1501 RAISE FND_API.G_EXC_ERROR;
1502 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR )
1503 THEN
1504 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1505 END IF;
1506 */
1507 END IF;
1508 END IF;
1509
1510
1511 -- Standard check of p_commit.
1512 IF FND_API.To_Boolean( p_commit )
1513 THEN
1514 COMMIT WORK;
1515 END IF;
1516
1517 -- Standard call to get message count and if count is 1, get message info.
1518 FND_MSG_PUB.Count_And_Get
1519 (p_count => x_msg_count,
1520 p_data => x_msg_data
1521 );
1522 EXCEPTION
1523 WHEN FND_API.G_EXC_ERROR THEN
1524 ROLLBACK TO Delete_RuleAttribute;
1525 x_return_status := FND_API.G_RET_STS_ERROR ;
1526 FND_MSG_PUB.Count_And_Get
1527 (p_count => x_msg_count,
1528 p_data => x_msg_data
1529 );
1530 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1531 ROLLBACK TO Delete_RuleAttribute;
1532 x_loading_status := 'UNEXPECTED_ERR';
1533 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1534 FND_MSG_PUB.Count_And_Get
1535 (p_count => x_msg_count,
1536 p_data => x_msg_data,
1537 p_encoded => fnd_api.g_false
1538 );
1539 WHEN OTHERS THEN
1540 ROLLBACK TO Delete_RuleAttribute;
1541 x_loading_status := 'UNEXPECTED_ERR';
1542 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1543 IF FND_MSG_PUB.Check_Msg_Level
1544 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1545 THEN
1546 FND_MSG_PUB.Add_Exc_Msg
1547 (G_PKG_NAME,
1548 l_api_name
1549 );
1550 END IF;
1551 FND_MSG_PUB.Count_And_Get
1552 (p_count => x_msg_count,
1553 p_data => x_msg_data,
1554 p_encoded => fnd_api.g_false
1555 );
1556 END;
1557 END CN_RuleAttribute_PUB;