[Home] [Help]
PACKAGE BODY: APPS.CN_SCA_CRRULEATTR_PVT
Source
4
1 PACKAGE BODY CN_SCA_CRRULEATTR_PVT as
2 -- $Header: cnvscrrb.pls 120.2 2005/08/12 00:00:42 vensrini noship $ --+
3
5 G_PKG_NAME CONSTANT VARCHAR2(30):='CN_SCA_CRRULEATTR_PVT';
6 -- -------------------------------------------------------------------------+
7 --+
8 -- Procedure : Get_PayGroup_ID
9 -- Description : This procedure is used to get the ID for the pay group
10 -- Calls :
11 --+
12 -- -------------------------------------------------------------------------+
13 -- PROCEDURE Get_Credit_Rule_Attr
14 -- (p_api_version IN NUMBER,
15 -- p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
16 -- p_commit IN VARCHAR2 := FND_API.G_FALSE,
17 -- p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
18 -- p_start_record IN NUMBER := -1,
19 -- p_fetch_size IN NUMBER := -1,
20 -- p_search_uname IN cn_sca_rule_attributes.user_column_name%TYPE := '%',
21 -- p_search_trx_source IN cn_sca_rule_attributes.transaction_source%TYPE,
22 -- x_credit_rule_attr OUT NOCOPY credit_rule_attr_tbl_type ,
23 -- x_total_record OUT NOCOPY NUMBER,
24 -- x_return_status OUT NOCOPY VARCHAR2,
25 -- x_msg_count OUT NOCOPY NUMBER,
26 -- x_msg_data OUT NOCOPY VARCHAR2
27 -- ) IS
28 -- l_api_name CONSTANT VARCHAR2(30) := 'Get_Credit_Rule_Attr';
29 -- l_api_version CONSTANT NUMBER := 1.0;
30 --
31 -- l_counter NUMBER;
32 -- l_value_set_name fnd_flex_value_sets.flex_value_set_name%TYPE;
33 -- l_user_src_name VARCHAR2(50);
34 --
35 -- CURSOR get_value_set_name(x number) IS
36 -- SELECT flex_value_set_name
37 -- FROM fnd_flex_value_Sets
38 -- WHERE flex_value_set_id = x;
39
40 -- CURSOR get_user_src_name(x_src_name VARCHAR2) IS
41 -- SELECT /*+ ORDERED*/ a.user_name from cn_objects b, cn_objects a
42 -- where A.name = x_src_name AND B.NAME= 'CN_COMM_LINES_API'
43 -- AND a.table_id = b.object_id AND A.object_type = 'COL';
44 --
45 -- CURSOR l_credit_rule_attr_search_cr IS
46 -- SELECT transaction_source,
47 -- src_column_name,
48 -- user_column_name,
49 -- datatype,
50 -- VALUE_SET_ID,
51 -- TRX_SRC_COLUMN_NAME,
52 -- ENABLED_FLAG,
53 -- attribute_category,
54 -- attribute1,
55 -- attribute2,
56 -- attribute3,
57 -- attribute4,
58 -- attribute5,
59 -- attribute6,
60 -- attribute7,
61 -- attribute8,
62 -- attribute9,
63 -- attribute10,
64 -- attribute11,
65 -- attribute12,
66 -- attribute13,
67 -- attribute14,
68 -- attribute15,
69 -- object_version_number
70 -- FROM cn_sca_rule_attributes WHERE ((upper(user_column_name) like upper(p_search_uname)
71 -- and (transaction_source like p_search_trx_source)) )
72 -- ORDER BY TO_NUMBER(substr(src_column_name,10));
73 -- CURSOR l_credit_rule_attr_cr IS
74 -- SELECT transaction_source,
75 -- src_column_name,
76 -- user_column_name,
77 -- datatype,
78 -- VALUE_SET_ID,
79 -- TRX_SRC_COLUMN_NAME,
80 -- ENABLED_FLAG,
81 -- attribute_category,
82 -- attribute1,
83 -- attribute2,
84 -- attribute3,
85 -- attribute4,
86 -- attribute5,
87 -- attribute6,
88 -- attribute7,
89 -- attribute8,
90 -- attribute9,
91 -- attribute10,
92 -- attribute11,
93 -- attribute12,
94 -- attribute13,
95 -- attribute14,
96 -- attribute15,
97 -- object_version_number
98 -- FROM cn_sca_rule_attributes WHERE ((upper(user_column_name) like upper(p_search_uname)
99 -- and (transaction_source like p_search_trx_source))
100 -- OR ( transaction_source = p_search_trx_source ))
101 -- ORDER BY TO_NUMBER(substr(src_column_name,10));
102 --
103 --
104 --
105 -- BEGIN
106 -- -- Standard Start of API savepoint
107 -- SAVEPOINT Get_Credit_Rule_Attr;
108 -- -- Standard call to check for call compatibility.
109 -- IF NOT FND_API.Compatible_API_Call
110 -- (l_api_version ,
111 -- p_api_version ,
112 -- l_api_name ,
113 -- G_PKG_NAME )
114 -- THEN
115 -- RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
116 -- END IF;
117 -- -- Initialize message list if p_init_msg_list is set to TRUE.
118 -- IF FND_API.to_Boolean( p_init_msg_list ) THEN
119 -- FND_MSG_PUB.initialize;
120 -- END IF;
121 -- -- Initialize API return status to success
122 -- x_return_status := FND_API.G_RET_STS_SUCCESS;
126 --
123 -- -- API body
124 --
125 -- x_credit_rule_attr := G_MISS_SCACRRR_REC_TB ;
127 -- l_counter := 0;
128 -- x_total_record := 0;
129 --
130 -- if p_search_uname <> '%'
131 -- then
132 --
133 --
134 -- FOR l_credit_rule_attr_search IN l_credit_rule_attr_search_cr LOOP
135 --
136 -- x_total_record := x_total_record +1;
137 -- IF (p_fetch_size = -1) OR (x_total_record >= p_start_record
138 -- AND x_total_record <= (p_start_record + p_fetch_size - 1)) THEN
139 --
140 -- x_credit_rule_attr(l_counter).Transaction_source := l_credit_rule_attr_search.transaction_source;
141 -- x_credit_rule_attr(l_counter).Destination_column := l_credit_rule_attr_search.src_column_name;
142 -- x_credit_rule_attr(l_counter).User_Name := l_credit_rule_attr_search.user_column_name;
143 -- x_credit_rule_attr(l_counter).Data_Type := l_credit_rule_attr_search.datatype;
144 -- --x_credit_rule_attr(l_counter).Value_Set_Id := l_credit_rule_attr_search.VALUE_SET_ID;
145 -- OPEN get_user_src_name(l_credit_rule_attr_search.TRX_SRC_COLUMN_NAME);
146 -- FETCH get_user_src_name INTO l_user_src_name;
147 -- close get_user_src_name;
148 --
149 -- x_credit_rule_attr(l_counter).Source_Column := l_user_src_name;
150 --
151 -- x_credit_rule_attr(l_counter).enable_flag := nvl(l_credit_rule_attr_search.enabled_flag,'N');
152 -- x_credit_rule_attr(l_counter).object_version_number := l_credit_rule_attr_search.object_version_number;
153 --
154 -- IF l_credit_rule_attr_search.VALUE_SET_ID IS NULL THEN
155 -- x_credit_rule_attr(l_counter).value_set_name := FND_API.G_MISS_CHAR;
156 -- ELSE
157 -- OPEN get_value_set_name(l_credit_rule_attr_search.VALUE_SET_ID);
158 -- FETCH get_value_set_name INTO l_value_set_name;
159 --
160 -- close get_value_set_name;
161 -- --select flex_value_set_name into x_credit_rule_attr(l_counter).value_set_name from fnd_flex_value_Sets where flex_value_Set_id = l_credit_rule_attr.VALUE_SET_ID;
162 -- x_credit_rule_attr(l_counter).value_set_name := l_value_set_name;
163 -- END IF;
164 --
165 --
166 -- l_counter := l_counter + 1;
167 --
168 -- END IF;
169 -- END LOOP;
170 --
171 -- else
172 -- FOR l_credit_rule_attr IN l_credit_rule_attr_cr LOOP
173 --
174 -- x_total_record := x_total_record +1;
175 -- IF (p_fetch_size = -1) OR (x_total_record >= p_start_record
176 -- AND x_total_record <= (p_start_record + p_fetch_size - 1)) THEN
177 --
178 -- x_credit_rule_attr(l_counter).Transaction_source := l_credit_rule_attr.transaction_source;
179 -- x_credit_rule_attr(l_counter).Destination_column := l_credit_rule_attr.src_column_name;
180 -- x_credit_rule_attr(l_counter).User_Name := l_credit_rule_attr.user_column_name;
181 -- x_credit_rule_attr(l_counter).Data_Type := l_credit_rule_attr.datatype;
182 -- --x_credit_rule_attr(l_counter).Value_Set_Id := l_credit_rule_attr.VALUE_SET_ID;
183 -- OPEN get_user_src_name(l_credit_rule_attr.TRX_SRC_COLUMN_NAME);
184 -- FETCH get_user_src_name INTO l_user_src_name;
185 -- close get_user_src_name;
186 --
187 -- x_credit_rule_attr(l_counter).Source_Column := l_user_src_name;
188 -- x_credit_rule_attr(l_counter).enable_flag := nvl(l_credit_rule_attr.enabled_flag,'N');
189 -- x_credit_rule_attr(l_counter).object_version_number := l_credit_rule_attr.object_version_number;
190 --
191 -- IF l_credit_rule_attr.VALUE_SET_ID IS NULL THEN
192 -- x_credit_rule_attr(l_counter).value_set_name := FND_API.G_MISS_CHAR;
193 -- ELSE
194 -- OPEN get_value_set_name(l_credit_rule_attr.VALUE_SET_ID);
195 -- FETCH get_value_set_name INTO l_value_set_name;
196 --
197 -- close get_value_set_name;
198 -- --select flex_value_set_name into x_credit_rule_attr(l_counter).value_set_name from fnd_flex_value_Sets where flex_value_Set_id = l_credit_rule_attr.VALUE_SET_ID;
199 -- x_credit_rule_attr(l_counter).value_set_name := l_value_set_name;
200 -- END IF;
201 --
202 -- l_counter := l_counter + 1;
203 --
204 -- END IF;
205 -- END LOOP;
206 -- end if;
207 --
208 -- -- End of API body.
209 -- -- Standard check of p_commit.
210 -- IF FND_API.To_Boolean( p_commit ) THEN
211 -- COMMIT WORK;
212 -- END IF;
213 -- -- Standard call to get message count and if count is 1, get message info.
214 -- FND_MSG_PUB.Count_And_Get
215 -- (p_count => x_msg_count ,
216 -- p_data => x_msg_data ,
217 -- p_encoded => FND_API.G_FALSE );
218 -- EXCEPTION
219 -- WHEN FND_API.G_EXC_ERROR THEN
220 -- ROLLBACK TO Get_Credit_Rule_Attr;
221 -- x_return_status := FND_API.G_RET_STS_ERROR ;
222 -- FND_MSG_PUB.Count_And_Get
223 -- (p_count => x_msg_count ,
224 -- p_data => x_msg_data ,
225 -- p_encoded => FND_API.G_FALSE );
226 -- WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
227 -- ROLLBACK TO Get_Credit_Rule_Attr;
228 -- x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
229 -- FND_MSG_PUB.Count_And_Get
230 -- (p_count => x_msg_count ,
231 -- p_data => x_msg_data ,
232 -- p_encoded => FND_API.G_FALSE );
233 -- WHEN OTHERS THEN
234 -- ROLLBACK TO Get_Credit_Rule_Attr;
235 -- x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
236 -- IF FND_MSG_PUB.Check_Msg_Level
240 -- (G_PKG_NAME ,
237 -- (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
238 -- THEN
239 -- FND_MSG_PUB.Add_Exc_Msg
241 -- l_api_name );
242 -- END IF;
243 -- FND_MSG_PUB.Count_And_Get
244 -- (p_count => x_msg_count ,
245 -- p_data => x_msg_data ,
246 -- p_encoded => FND_API.G_FALSE );
247 -- END Get_Credit_Rule_Attr;
248
249
250 --------------------------------------------------------------------------+
251 -- Procedure : Create_PayGroup
252 -- Description: Public API to create a Credit Rule Attribute
253 -- Calls : validate_pay_group
254 -- CN_Pay_Groups_Pkg.Begin_Record
255 --------------------------------------------------------------------------+
256 PROCEDURE Create_Credit_RuleAttr( p_api_version IN NUMBER,
257 p_init_msg_list IN VARCHAR2 ,
258 p_commit IN VARCHAR2,
259 p_valdiation_level IN VARCHAR2,
260 p_org_id IN cn_sca_rule_attributes.org_id%TYPE, -- MOAC Change
261 p_credit_rule_attr_rec IN credit_rule_attr_rec,
262 x_return_status OUT NOCOPY VARCHAR2,
263 x_msg_count OUT NOCOPY NUMBER,
264 x_msg_data OUT NOCOPY VARCHAR2
265 ) IS
266
267
268
269 l_api_name CONSTANT VARCHAR2(30) := 'Create_Credit_RuleAttr';
270 l_api_version CONSTANT NUMBER := 1.0;
271 l_count NUMBER;
272
273 L_PKG_NAME CONSTANT VARCHAR2(30) := 'CN_SCA_CRRULEATTR_PVT';
274 L_LAST_UPDATE_DATE DATE := sysdate;
275 L_LAST_UPDATED_BY NUMBER := fnd_global.user_id;
276 L_CREATION_DATE DATE := sysdate;
277 L_CREATED_BY NUMBER := fnd_global.user_id;
278 L_LAST_UPDATE_LOGIN NUMBER := fnd_global.login_id;
279 L_ROWID VARCHAR2(30);
280 L_PROGRAM_TYPE VARCHAR2(30);
281 L_SECURITY_GROUP_ID NUMBER ;
282 l_credit_rule_attr_rec credit_rule_attr_rec;
283 l_value_set_id NUMBER;
284 l_sca_rule_attribute_id cn_sca_rule_attributes.sca_rule_attribute_id%TYPE :=0;
285
286
287 /*-- CURSOR get_value_set_id IS
288 -- SELECT nvl(flex_value_set_id,0)
289 -- FROM fnd_flex_value_Sets
290 -- WHERE flex_value_set_name = p_credit_rule_attr_rec.value_set_name;*/
291
292
293 BEGIN
294
295
296 -- Standard Start of API savepoint
297
298 SAVEPOINT Create_Credit_RuleAttr;
299
300
301 -- Standard call to check for call compatibility.
302
303 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
304 p_api_version ,
305 l_api_name ,
306 L_PKG_NAME )
307 THEN
308 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
309 END IF;
310
311
312 -- Initialize message list if p_init_msg_list is set to TRUE.
313
314 IF FND_API.to_Boolean( p_init_msg_list ) THEN
315 FND_MSG_PUB.initialize;
316 END IF;
317
318 l_credit_rule_attr_rec := p_credit_rule_attr_rec;
319 -- Initialize API return status to success
320
321 x_return_status := FND_API.G_RET_STS_SUCCESS;
322
323
324 --OPEN get_value_set_id;
325 --FETCH get_value_set_id INTO l_value_set_id;
326 --IF get_value_set_id%ROWCOUNT = 0 THEN
327 -- null;
328 --END IF;
329
330 l_value_set_id := l_credit_rule_attr_rec.value_set_id;
331
332 IF (l_credit_rule_attr_rec.source_column IS NULL AND l_credit_rule_attr_rec.transaction_source = 'CN') THEN
333 fnd_message.set_name('CN', 'CN_REQ_PAR_MISSING');
334 fnd_msg_pub.add;
335 RAISE FND_API.G_EXC_ERROR;
336 END IF;
337
338 IF l_credit_rule_attr_rec.user_name IS NULL
339 THEN
340 l_credit_rule_attr_rec.user_name := l_credit_rule_attr_rec.destination_column;
341 END IF;
342
343 IF l_credit_rule_attr_rec.enable_flag IS NULL
344 THEN
345 l_credit_rule_attr_rec.enable_flag := 'N';
346 END IF;
347
348 SELECT count(*) into l_count from cn_sca_rule_attributes where
349 TRANSACTION_SOURCE = l_credit_rule_attr_rec.transaction_source
350 and ORG_ID = p_org_id -- MOAC Change
351 and ( SRC_COLUMN_NAME = l_credit_rule_attr_rec.destination_column OR upper(USER_COLUMN_NAME) = upper(trim(l_credit_rule_attr_rec.user_name)));
352
353 IF (l_count <> 0) THEN
354 --Error condition
355 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
356 THEN
357 fnd_message.set_name('CN', 'CN_SCA_RULE_ATTRIBUTE_EXISTS');
358 fnd_msg_pub.add;
359 END IF;
360
361
362 RAISE FND_API.G_EXC_ERROR;
363 END IF ;
364
365 SELECT cn_sca_rule_attributes_s.nextval INTO l_sca_rule_attribute_id FROM sys.dual;
366 -- API body
367
368
369 CN_SCA_CRRULEATTR_PKG.INSERT_ROW(
370
371 x_Rowid => L_ROWID,
372 x_org_id => p_org_id, -- MOAC Change
373 x_sca_rule_attribute_id => l_sca_rule_attribute_id,
374 X_TRANSACTION_SOURCE => l_credit_rule_attr_rec.transaction_source,
375 X_SRC_COLUMN_NAME => l_credit_rule_attr_rec.destination_column,
376 X_DATATYPE => l_credit_rule_attr_rec.data_type,
377 X_VALUE_SET_ID => l_value_set_id,
378 X_TRX_SRC_COLUMN_NAME => l_credit_rule_attr_rec.source_column,
379 X_ENABLED_FLAG => l_credit_rule_attr_rec.enable_flag,
380 x_attribute_category => l_credit_rule_attr_rec.attribute_category,
381 x_attribute1 => l_credit_rule_attr_rec.attribute1,
382 x_attribute2 => l_credit_rule_attr_rec.attribute2,
383 x_attribute3 => l_credit_rule_attr_rec.attribute3,
384 x_attribute4 => l_credit_rule_attr_rec.attribute4,
385 x_attribute5 => l_credit_rule_attr_rec.attribute5,
386 x_attribute6 => l_credit_rule_attr_rec.attribute6,
387 x_attribute7 => l_credit_rule_attr_rec.attribute7,
388 x_attribute8 => l_credit_rule_attr_rec.attribute8,
389 x_attribute9 => l_credit_rule_attr_rec.attribute9,
390 x_attribute10 => l_credit_rule_attr_rec.attribute10,
391 x_attribute11 => l_credit_rule_attr_rec.attribute11,
392 x_attribute12 => l_credit_rule_attr_rec.attribute12,
393 x_attribute13 => l_credit_rule_attr_rec.attribute13,
394 x_attribute14 => l_credit_rule_attr_rec.attribute14,
395 x_attribute15 => l_credit_rule_attr_rec.attribute15,
396 X_OBJECT_VERSION_NUMBER => l_credit_rule_attr_rec.Object_Version_Number,
397 X_SECURITY_GROUP_ID => L_SECURITY_GROUP_ID,
398 X_USER_COLUMN_NAME => l_credit_rule_attr_rec.user_name,
399 x_Creation_Date => l_creation_date,
400 x_Created_By => l_created_by,
401 x_Last_Update_Date => l_last_update_date,
402 x_Last_Updated_By => l_last_updated_by,
403 x_Last_Update_Login => l_last_update_login
404 );
405
406 IF l_credit_rule_attr_rec.transaction_source = 'CN' THEN
407 UPDATE cn_repositories
408 SET sca_mapping_status = 'UNSYNC'
409 WHERE org_id = p_org_id; -- MOAC Change
410 END IF;
411
412 IF FND_API.To_Boolean( p_commit ) THEN
413 COMMIT WORK;
414 END IF;
415
416
417 -- Standard call to get message count and if count is 1, get message info.
418
419 FND_MSG_PUB.Count_And_Get
420 (
421 p_count => x_msg_count ,
422 p_data => x_msg_data ,
423 p_encoded => FND_API.G_FALSE
424 );
425
426 EXCEPTION
427 WHEN FND_API.G_EXC_ERROR THEN
428 ROLLBACK TO Create_Credit_RuleAttr;
429 x_return_status := FND_API.G_RET_STS_ERROR ;
430 FND_MSG_PUB.Count_And_Get
431 (
432 p_count => x_msg_count ,
433 p_data => x_msg_data ,
434 p_encoded => FND_API.G_FALSE
435 );
436 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
437 ROLLBACK TO Create_Credit_RuleAttr;
438 IF SQLCODE = '-1'
439 THEN
440 fnd_message.set_name('CN', 'CN_SCA_RULE_ATTRIBUTES_UNIQUE');
441 fnd_msg_pub.ADD;
442 RAISE fnd_api.g_exc_error;
443 END IF;
444 --x_loading_status := 'UNEXPECTED_ERR';
445 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
446 FND_MSG_PUB.Count_And_Get
447 (
448 p_count => x_msg_count ,
449 p_data => x_msg_data ,
450 p_encoded => FND_API.G_FALSE
451 );
452 WHEN OTHERS THEN
453 ROLLBACK TO Create_Credit_RuleAttr;
454 --x_loading_status := 'UNEXPECTED_ERR';
455 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
456 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
457 THEN
458 FND_MSG_PUB.Add_Exc_Msg( L_PKG_NAME ,l_api_name );
459 END IF;
460 FND_MSG_PUB.Count_And_Get
461 (
462 p_count => x_msg_count ,
463 p_data => x_msg_data ,
464 p_encoded => FND_API.G_FALSE
465 );
466 END Create_Credit_RuleAttr;
467
468 ---------------------------------------------------------------------------+
469 -- Procedure : Update PayGroup
470 -- Description : This is a public procedure to update pay groups
471 -- Calls : validate_pay_group
472 -- CN_Pay_Groups_Pkg.Begin_Record
473 ---------------------------------------------------------------------------+
477 p_init_msg_list IN VARCHAR2 ,
474
475 PROCEDURE Update_Credit_RuleAttr (
476 p_api_version IN NUMBER,
478 p_commit IN VARCHAR2,
479 p_valdiation_level IN VARCHAR2,
480 p_org_id IN cn_sca_rule_attributes.org_id%TYPE, -- MOAC Change
481 p_credit_rule_attr_rec IN credit_rule_attr_rec,
482 -- p_old_credit_rule_attr_rec IN credit_rule_attr_rec,
483 x_return_status OUT NOCOPY VARCHAR2,
484 x_msg_count OUT NOCOPY NUMBER,
485 x_msg_data OUT NOCOPY VARCHAR2
486 ) IS
487 L_PKG_NAME CONSTANT VARCHAR2(30) := 'CN_SCA_CRRULEATTR_PVT';
488 L_LAST_UPDATE_DATE DATE := sysdate;
489 L_LAST_UPDATED_BY NUMBER := fnd_global.user_id;
490 L_CREATION_DATE DATE := sysdate;
491 L_CREATED_BY NUMBER := fnd_global.user_id;
492 L_LAST_UPDATE_LOGIN NUMBER := fnd_global.login_id;
493 L_ROWID VARCHAR2(30);
494 L_PROGRAM_TYPE VARCHAR2(30);
495 L_SECURITY_GROUP_ID NUMBER ;
496
497 l_api_name CONSTANT VARCHAR2(30) := 'Update_Credit_RuleAttr';
498 l_api_version CONSTANT NUMBER := 1.0;
499 l_credit_rule_attr_rec credit_rule_attr_rec;
500 l_credit_rule_attribute_id NUMBER;
501
502 l_count NUMBER;
503 l_count2 NUMBER;
504 l_period_set_id NUMBER;
505 l_period_type_id NUMBER;
506 l_start_date DATE;
507 l_end_date DATE;
508 l_null_date CONSTANT DATE := to_date('31-12-3000','DD-MM-YYYY');
509 l_dummy NUMBER;
510 l_old_ovn NUMBER;
511 -- l_sca_rule_attribute_id cn_sca_rule_attributes.sca_rule_attribute_id%TYPE;
512 l_value_set_id fnd_flex_value_sets.flex_value_set_id%TYPE;
513 l_trx_source_column cn_sca_rule_attributes.trx_src_column_name%TYPE;
514
515 -- CURSOR get_value_set_id IS
516 -- SELECT flex_value_set_id
517 -- FROM fnd_flex_value_Sets
518 -- WHERE flex_value_set_name = p_credit_rule_attr_rec.value_set_name;
519
520 BEGIN
521
522 -- Standard Start of API savepoint
523
524 SAVEPOINT Update_Credit_RuleAttr;
525
526 -- Standard call to check for call compatibility.
527
528 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
529 p_api_version ,
530 l_api_name ,
531 L_PKG_NAME )
532 THEN
533 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
534 END IF;
535
536 -- Initialize message list if p_init_msg_list is set to TRUE.
537
538 IF FND_API.to_Boolean( p_init_msg_list ) THEN
539 FND_MSG_PUB.initialize;
540 END IF;
541
542 -- Initialize API return status to success
543 l_credit_rule_attr_rec := p_credit_rule_attr_rec;
544
545 x_return_status := FND_API.G_RET_STS_SUCCESS;
546
547
548 SELECT object_version_number -- ,sca_rule_attribute_id
549 INTO l_old_ovn -- ,l_sca_rule_attribute_id
550 FROM cn_sca_rule_attributes
551 WHERE SCA_RULE_ATTRIBUTE_ID = l_credit_rule_attr_rec.sca_rule_attribute_id
552 AND ORG_ID = p_org_id; -- MOAC Change
553 -- TRANSACTION_SOURCE = p_old_credit_rule_attr_rec.transaction_source
554 -- and SRC_COLUMN_NAME = p_old_credit_rule_attr_rec.destination_column ;
555 -- and TRX_SRC_COLUMN_NAME = p_old_credit_rule_attr_rec.source_column;
556
557 IF l_old_ovn <> p_credit_rule_attr_rec.object_version_number THEN
558 fnd_message.set_name('CN', 'CN_RECORD_CHANGED');
559 fnd_msg_pub.add;
560 raise fnd_api.g_exc_error;
561 END IF;
562
563 --OPEN get_value_set_id;
564 -- FETCH get_value_set_id INTO l_value_set_id;
565 -- IF get_value_set_id%ROWCOUNT = 0 THEN
566 -- NULL;
567 -- END IF;
568 --CLOSE get_value_set_id;
569
570 l_value_set_id := l_credit_rule_attr_rec.value_set_id;
571
572
573
574 IF l_credit_rule_attr_rec.enable_flag IS NULL THEN
575 l_credit_rule_attr_rec.enable_flag := 'N';
576 END IF;
577
578 IF (l_credit_rule_attr_rec.source_column IS NULL AND l_credit_rule_attr_rec.transaction_source = 'CN'AND (l_credit_rule_attr_rec.enable_flag <> 'N' OR l_value_set_id IS NOT NULL OR l_credit_rule_attr_rec.user_name IS NOT NULL )) THEN
579 fnd_message.set_name('CN', 'CN_REQ_PAR_MISSING');
580 fnd_msg_pub.add;
581 RAISE FND_API.G_EXC_ERROR;
582 END IF;
583
584
585 IF (l_value_set_id IS NULL AND l_credit_rule_attr_rec.user_name IS NULL
586 AND l_credit_rule_attr_rec.source_column IS NULL
587 AND l_credit_rule_attr_rec.enable_flag ='N') THEN
588 SELECT count(1) into l_count2
589 FROM CN_SCA_CONDITIONS
590 WHERE SCA_RULE_ATTRIBUTE_ID= l_credit_rule_attr_rec.sca_rule_attribute_id
591 AND ORG_ID = p_org_id; -- MOAC Change
592
593 IF l_count2 = 0 THEN
594 CN_SCA_CRRULEATTR_PKG.DELETE_ROW(
595 X_ORG_ID => p_org_id, -- MOAC Change
596 X_SCA_RULE_ATTRIBUTE_ID => l_credit_rule_attr_rec.sca_rule_attribute_id) ;
597
598 IF l_credit_rule_attr_rec.transaction_source = 'CN' THEN
599 update cn_repositories
600 set sca_mapping_status = 'UNSYNC'
601 where org_id = p_org_id; -- MOAC Change
602 END IF;
603 ELSE
604 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
605 THEN
606 fnd_message.set_name('CN', 'CN_SCA_RULE_COND_EXISTS');
610 RAISE FND_API.G_EXC_ERROR;
607 fnd_msg_pub.add;
608 END IF;
609
611 END IF;
612 ELSE
613 IF l_credit_rule_attr_rec.user_name IS NULL
614 THEN
615 l_credit_rule_attr_rec.user_name := l_credit_rule_attr_rec.destination_column;
616 END IF;
617
618 --IF l_credit_rule_attr_rec.user_name <> p_old_credit_rule_attr_rec.user_name THEN
619 SELECT count(sca_rule_attribute_id) into l_count from cn_sca_rule_attributes where
620 TRANSACTION_SOURCE = l_credit_rule_attr_rec.transaction_source
621 and org_id = p_org_id -- MOAC Change
622 and upper(USER_COLUMN_NAME) = upper(trim(l_credit_rule_attr_rec.user_name));
623
624 IF (l_count <> 0) THEN
625 --Error condition
626 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
627 THEN
628 fnd_message.set_name('CN', 'CN_SCA_RULE_ATTRIBUTE_EXISTS');
629 fnd_msg_pub.add;
630 END IF;
631
632
633 RAISE FND_API.G_EXC_ERROR;
634 END IF ;
635 --END IF;
636
637 l_count2 :=0;
638 SELECT count(1) into l_count2 FROM CN_SCA_CONDITIONS WHERE
639 SCA_RULE_ATTRIBUTE_ID= l_credit_rule_attr_rec.sca_rule_attribute_id
640 and org_id = p_org_id; -- MOAC Change
641 IF l_count2 = 0 THEN
642
643 -- Changing the UNCYNC update checking condition to retrieve the existing source column
644 select trx_src_column_name
645 into l_trx_source_column
646 from cn_sca_rule_attributes
647 where sca_rule_attribute_id = l_credit_rule_attr_rec.sca_rule_attribute_id
648 and org_id = p_org_id; -- MOAC Change
649
650 CN_SCA_CRRULEATTR_PKG.UPDATE_ROW(
651 x_org_id => p_org_id, -- MOAC Change
652 x_sca_rule_attribute_id => l_credit_rule_attr_rec.sca_rule_attribute_id,
653 X_TRANSACTION_SOURCE => l_credit_rule_attr_rec.transaction_source,
654 X_SRC_COLUMN_NAME => l_credit_rule_attr_rec.destination_column,
655 X_DATATYPE => l_credit_rule_attr_rec.data_type,
656 X_VALUE_SET_ID => l_value_set_id,
657 X_TRX_SRC_COLUMN_NAME => l_credit_rule_attr_rec.source_column,
658 X_ENABLED_FLAG => l_credit_rule_attr_rec.enable_flag,
659 x_attribute_category => l_credit_rule_attr_rec.attribute_category,
660 x_attribute1 => l_credit_rule_attr_rec.attribute1,
661 x_attribute2 => l_credit_rule_attr_rec.attribute2,
662 x_attribute3 => l_credit_rule_attr_rec.attribute3,
663 x_attribute4 => l_credit_rule_attr_rec.attribute4,
664 x_attribute5 => l_credit_rule_attr_rec.attribute5,
665 x_attribute6 => l_credit_rule_attr_rec.attribute6,
666 x_attribute7 => l_credit_rule_attr_rec.attribute7,
667 x_attribute8 => l_credit_rule_attr_rec.attribute8,
668 x_attribute9 => l_credit_rule_attr_rec.attribute9,
669 x_attribute10 => l_credit_rule_attr_rec.attribute10,
670 x_attribute11 => l_credit_rule_attr_rec.attribute11,
671 x_attribute12 => l_credit_rule_attr_rec.attribute12,
672 x_attribute13 => l_credit_rule_attr_rec.attribute13,
673 x_attribute14 => l_credit_rule_attr_rec.attribute14,
674 x_attribute15 => l_credit_rule_attr_rec.attribute15,
675 X_OBJECT_VERSION_NUMBER => l_credit_rule_attr_rec.Object_Version_Number,
676 X_SECURITY_GROUP_ID => L_SECURITY_GROUP_ID ,
677 X_USER_COLUMN_NAME => l_credit_rule_attr_rec.user_name,
678 x_Last_Update_Date => l_last_update_date,
679 x_Last_Updated_By => l_last_updated_by,
680 x_Last_Update_Login => l_last_update_login
681
682 );
683
684
685 IF l_credit_rule_attr_rec.source_column <> l_trx_source_column AND l_credit_rule_attr_rec.transaction_source = 'CN'
686 THEN
687 update cn_repositories
688 set sca_mapping_status = 'UNSYNC'
689 where org_id = p_org_id;
690 END IF;
691 ELSE
692 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
693 THEN
694 fnd_message.set_name('CN', 'CN_SCA_RULE_COND_EXISTS');
695 fnd_msg_pub.add;
696 END IF;
697
698 RAISE FND_API.G_EXC_ERROR;
699 END IF;
700 END IF;
701
702 IF (SQL%NOTFOUND) THEN
703 Raise NO_DATA_FOUND;
704 END IF;
705 -- End of API body.
706 -- Standard check of p_commit.
707
708 IF FND_API.To_Boolean( p_commit ) THEN
709 COMMIT WORK;
710 END IF;
711
712
713 -- Standard call to get message count and if count is 1, get message info.
714
715 FND_MSG_PUB.Count_And_Get
716 (
717 p_count => x_msg_count ,
718 p_data => x_msg_data ,
719 p_encoded => FND_API.G_FALSE
720 );
721 EXCEPTION
722 WHEN FND_API.G_EXC_ERROR THEN
723 ROLLBACK TO Update_Credit_RuleAttr;
724 x_return_status := FND_API.G_RET_STS_ERROR ;
725 FND_MSG_PUB.Count_And_Get
726 (
727 p_count => x_msg_count ,
728 p_data => x_msg_data ,
729 p_encoded => FND_API.G_FALSE
730 );
731 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
732 ROLLBACK TO Update_Credit_RuleAttr;
733 --x_loading_status := 'UNEXPECTED_ERR';
734 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
735 FND_MSG_PUB.Count_And_Get
736 (
737 p_count => x_msg_count ,
738 p_data => x_msg_data ,
739 p_encoded => FND_API.G_FALSE
740 );
741 WHEN OTHERS THEN
742 ROLLBACK TO Update_Credit_RuleAttr;
743 -- x_loading_status := 'UNEXPECTED_ERR';
744 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
745 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
746 THEN
747 FND_MSG_PUB.Add_Exc_Msg( L_PKG_NAME ,l_api_name );
748 END IF;
749 FND_MSG_PUB.Count_And_Get
750 (
751 p_count => x_msg_count ,
752 p_data => x_msg_data ,
753 p_encoded => FND_API.G_FALSE
754 );
755 END Update_Credit_RuleAttr;
756
757 -- TODO: Make MOAC Changes
758 PROCEDURE Generate_Package
759 ( p_api_version IN NUMBER,
760 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
761 p_commit IN VARCHAR2 := FND_API.G_FALSE,
762 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
763 p_org_id IN NUMBER, -- MOAC Change
764 x_return_status OUT NOCOPY VARCHAR2,
765 x_msg_count OUT NOCOPY NUMBER,
766 x_msg_data OUT NOCOPY VARCHAR2
767 ) IS
768 L_PKG_NAME CONSTANT VARCHAR2(30) := 'CN_SCA_CRRULEATTR_PVT';
769 L_LAST_UPDATE_DATE DATE := sysdate;
770 L_LAST_UPDATED_BY NUMBER := fnd_global.user_id;
771 L_CREATION_DATE DATE := sysdate;
772 L_CREATED_BY NUMBER := fnd_global.user_id;
773 L_LAST_UPDATE_LOGIN NUMBER := fnd_global.login_id;
774 L_ROWID VARCHAR2(30);
775 L_PROGRAM_TYPE VARCHAR2(30);
776 l_count NUMBER;
777
778 l_api_name CONSTANT VARCHAR2(30) := 'Generate_Package';
779 l_api_version CONSTANT NUMBER := 1.0;
780 l_return_status VARCHAR2(2000) ;
781 l_msg_count NUMBER;
782 l_msg_data VARCHAR2(2000);
783
784
785
786
787 BEGIN
788
789 -- Standard Start of API savepoint
790
791 SAVEPOINT Generate_Package;
792
793 -- Standard call to check for call compatibility.
794
795 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
796 p_api_version ,
797 l_api_name ,
798 L_PKG_NAME )
799 THEN
800 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
801 END IF;
802
803 -- Initialize message list if p_init_msg_list is set to TRUE.
804
805 IF FND_API.to_Boolean( p_init_msg_list ) THEN
806 FND_MSG_PUB.initialize;
807 END IF;
808
809
810 x_return_status := FND_API.G_RET_STS_SUCCESS;
811 -- x_loading_status := 'CN_UPDATED';
812
813 -- API body
814 SELECT count(*) into l_count
815 FROM CN_SCA_RULE_ATTRIBUTES
816 WHERE TRANSACTION_SOURCE='CN'
817 AND ORG_ID = p_org_id; -- MOAC Change
818
819 IF l_count = 0 THEN
820 fnd_message.set_name('CN', 'CN_NORECORD_GENERATE');
821 fnd_msg_pub.add;
822 raise fnd_api.g_exc_error;
823 ELSE
824
825
826 CN_SCA_INTERFACE_MAP_PVT.GENERATE (
827 p_api_version => l_api_version,
828 p_init_msg_list => fnd_api.g_true,
829 p_commit => fnd_api.g_false,
830 p_validation_level => fnd_api.g_valid_level_full,
831 p_org_id => p_org_id, -- MOAC Change
832 x_return_status => l_return_status,
833 x_msg_count => l_msg_count,
834 x_msg_data => l_msg_data
835 );
836
837
838
839
840 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
841 x_return_status := l_return_status;
842 fnd_message.set_name('CN', 'CN_GENERATION_FAILED');
843
844 fnd_msg_pub.add;
845 RAISE FND_API.G_EXC_ERROR;
846 -- x_loading_status := l_loading_status;
847
848 ELSE
849 update cn_repositories
850 set sca_mapping_status='GENERATED'
851 WHERE org_id = p_org_id; -- MOAC Change
852
853 END IF;
854 END IF;
855
856 -- End of API body.
857 -- Standard check of p_commit.
858
859 IF FND_API.To_Boolean( p_commit ) THEN
860 COMMIT WORK;
861 END IF;
862
863
864 -- Standard call to get message count and if count is 1, get message info.
865
866 FND_MSG_PUB.Count_And_Get
867 (
868 p_count => x_msg_count ,
869 p_data => x_msg_data ,
870 p_encoded => FND_API.G_FALSE
871 );
872 EXCEPTION
873 WHEN FND_API.G_EXC_ERROR THEN
874 --ROLLBACK TO Generate_Package;
875 x_return_status := FND_API.G_RET_STS_ERROR ;
876 FND_MSG_PUB.Count_And_Get
877 (
878 p_count => x_msg_count ,
879 p_data => x_msg_data ,
880 p_encoded => FND_API.G_FALSE
881 );
882 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
883 --ROLLBACK TO Generate_Package;
884 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
885 FND_MSG_PUB.Count_And_Get
886 (
887 p_count => x_msg_count ,
888 p_data => x_msg_data ,
889 p_encoded => FND_API.G_FALSE
890 );
891 WHEN OTHERS THEN
892 --ROLLBACK TO Generate_Package;
893 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
894 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
895 THEN
896 FND_MSG_PUB.Add_Exc_Msg( L_PKG_NAME ,l_api_name );
897 END IF;
898 FND_MSG_PUB.Count_And_Get
899 (
900 p_count => x_msg_count ,
901 p_data => x_msg_data ,
902 p_encoded => FND_API.G_FALSE
903 );
904
905 END Generate_Package;
906
907 END CN_SCA_CRRULEATTR_PVT ;