[Home] [Help]
PACKAGE BODY: APPS.IEX_STATUS_RULE_PVT
Source
1 PACKAGE BODY IEX_STATUS_RULE_PVT AS
2 /* $Header: iexvcstb.pls 120.3 2006/05/30 21:14:18 scherkas ship $ */
3
4
5 G_PKG_NAME CONSTANT VARCHAR2(30):= 'IEX_STATUS_RULE_PVT';
6 G_FILE_NAME CONSTANT VARCHAR2(12) := 'iexvcstb.pls';
7 G_MIN_STATUS_RULE CONSTANT NUMBER := 10;
8 G_MAX_STATUS_RULE CONSTANT NUMBER := 100;
9
10 PG_DEBUG NUMBER(2);
11
12 Procedure Validate_Status_Rule(P_Init_Msg_List IN VARCHAR2,
13 P_Status_Rule_rec IN IEX_STATUS_RULE_PUB.STATUS_RULE_REC_TYPE,
14 X_Dup_Status OUT NOCOPY VARCHAR2,
15 X_Return_Status OUT NOCOPY VARCHAR2,
16 X_Msg_Count OUT NOCOPY NUMBER,
17 X_Msg_Data OUT NOCOPY VARCHAR2)
18 IS
19 l_status_rule_rec IEX_STATUS_RULE_PUB.STATUS_RULE_REC_TYPE;
20
21 BEGIN
22 -- Initialize message list IF p_init_msg_list is set to TRUE.
23 IF FND_API.to_Boolean( p_init_msg_list )
24 THEN
25 FND_MSG_PUB.initialize;
26 END IF;
27 l_status_rule_rec := p_status_rule_rec;
28 IEX_UTILITIES.VALIDATE_ANY_ID(P_COL_ID => l_status_rule_rec.status_rule_id,
29 P_COL_NAME => 'STATUS_RULE_ID',
30 P_TABLE_NAME => 'IEX_STATUS_RULES',
31 X_Return_Status => x_return_status,
32 X_Msg_Count => x_msg_count,
33 X_Msg_Data => x_msg_data,
34 P_Init_Msg_List => FND_API.G_FALSE);
35
36 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
37 RAISE FND_API.G_EXC_ERROR;
38 END IF;
39
40 Validate_Status_Rule_ID_Name(P_Init_Msg_List => FND_API.G_FALSE,
41 P_STATUS_RULE_ID => l_status_rule_rec.status_rule_id,
42 P_STATUS_RULE_Name => l_status_rule_rec.status_rule_Name,
43 X_Dup_Status => x_dup_status,
44 X_Return_Status => x_return_status,
45 X_Msg_Count => x_msg_count,
46 X_Msg_Data => x_msg_data);
47
48 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
49 RAISE FND_API.G_EXC_ERROR;
50 END IF;
51 END Validate_Status_Rule;
52
53 Procedure Validate_STATUS_RULE_Name(P_Init_Msg_List IN VARCHAR2,
54 P_Status_Rule_Name IN VARCHAR2,
55 X_Dup_Status OUT NOCOPY VARCHAR2,
56 X_Return_Status OUT NOCOPY VARCHAR2,
57 X_Msg_Count OUT NOCOPY NUMBER,
58 X_Msg_Data OUT NOCOPY VARCHAR2)
59 IS
60 CURSOR C_GET_STATUS_RULE_name (IN_STATUS_RULE_Name VARCHAR2) IS
61 SELECT status_rule_Name
62 FROM iex_cust_status_rules
63 WHERE STATUS_RULE_Name = IN_STATUS_RULE_Name;
64 --
65 l_status_rule_Name VARCHAR2(50);
66
67 BEGIN
68 -- Initialize message list IF p_init_msg_list is set to TRUE.
69 IF FND_API.to_Boolean( p_init_msg_list )
70 THEN
71 FND_MSG_PUB.initialize;
72 END IF;
73
74 -- Initialize API return status to SUCCESS
75 x_return_status := FND_API.G_RET_STS_SUCCESS;
76
77 IF P_STATUS_RULE_Name is NULL
78 THEN
79 FND_MESSAGE.Set_Name('IEX', 'IEX_API_ALL_MISSING_PARAM');
80 FND_MESSAGE.Set_Token('API_NAME', 'Validate_Status_Rule_Name', FALSE);
81 FND_MESSAGE.Set_Token('MISSING_PARAM', 'STATUS_RULE_NAME', FALSE);
82 FND_MSG_PUB.Add;
83 x_return_status := FND_API.G_RET_STS_ERROR;
84
85 ELSE -- IF P_STATUS_RULE_Name is NULL
86
87 OPEN C_Get_Status_Rule_Name (p_status_rule_Name);
88 FETCH C_Get_Status_Rule_Name INTO l_status_rule_Name;
89
90 IF (C_Get_Status_Rule_Name%FOUND)
91 THEN
92 IF FND_MSG_PUB.CHECK_MSG_LEVEL (FND_MSG_PUB.G_MSG_LVL_ERROR)
93 THEN
94 FND_MESSAGE.Set_Name('IEX', 'IEX_API_DUPLICATE_NAME');
95 FND_MESSAGE.Set_Token('COLUMN', 'STATUS_RULE_NAME', FALSE);
96 FND_MESSAGE.Set_Token('VALUE', p_status_rule_Name, FALSE);
97 FND_MSG_PUB.Add;
98 END IF;
99 x_return_status := FND_API.G_RET_STS_ERROR;
100 x_dup_status := IEX_DUPLICATE_NAME;
101 END IF;
102 CLOSE C_GET_STATUS_RULE_Name;
103 END IF;
104
105 -- Standard call to get message count and IF count is 1, get message info.
106
107 FND_MSG_PUB.Count_And_Get
108 ( p_count => x_msg_count,
109 p_data => x_msg_data );
110
111 END Validate_Status_Rule_Name;
112
113
114 Procedure Validate_STATUS_RULE_ID_Name(P_Init_Msg_List IN VARCHAR2,
115 P_Status_Rule_ID IN NUMBER,
116 P_Status_Rule_Name IN VARCHAR2,
117 X_Dup_Status OUT NOCOPY VARCHAR2,
118 X_Return_Status OUT NOCOPY VARCHAR2,
119 X_Msg_Count OUT NOCOPY NUMBER,
120 X_Msg_Data OUT NOCOPY VARCHAR2)
121 IS
122 CURSOR C_GET_STATUS_RULE_ID_name (IN_STATUS_RULE_Name VARCHAR2, IN_STATUS_RULE_ID NUMBER) IS
123 SELECT status_rule_Name
124 FROM iex_cust_status_rules
125 WHERE STATUS_RULE_Name = IN_STATUS_RULE_Name and status_rule_id <> IN_Status_Rule_ID;
126 --
127 l_status_rule_Name VARCHAR2(50);
128
129 BEGIN
130 -- Initialize message list IF p_init_msg_list is set to TRUE.
131 IF FND_API.to_Boolean( p_init_msg_list )
132 THEN
133 FND_MSG_PUB.initialize;
134 END IF;
135
136 -- Initialize API return status to SUCCESS
137 x_return_status := FND_API.G_RET_STS_SUCCESS;
138
139 IF P_STATUS_RULE_Name is NULL
140 THEN
141 FND_MESSAGE.Set_Name('IEX', 'IEX_API_ALL_MISSING_PARAM');
142 FND_MESSAGE.Set_Token('API_NAME', 'Validate_STATUS_RULE_ID_Name', FALSE);
143 FND_MESSAGE.Set_Token('MISSING_PARAM', 'STATUS_RULE_NAME', FALSE);
144 FND_MSG_PUB.Add;
145 x_return_status := FND_API.G_RET_STS_ERROR;
146
147 ELSE -- IF P_STATUS_RULE_Name is NULL or P_STATUS_RULE_Name = FND_API.G_FALSE
148
149 OPEN C_Get_Status_Rule_ID_Name (p_status_rule_Name, P_STATUS_RULE_ID);
150 FETCH C_Get_Status_Rule_ID_Name INTO l_status_rule_Name;
151
152 IF (C_Get_Status_Rule_ID_Name%FOUND)
153 THEN
154 IF FND_MSG_PUB.CHECK_MSG_LEVEL (FND_MSG_PUB.G_MSG_LVL_ERROR)
155 THEN
156 FND_MESSAGE.Set_Name('IEX', 'IEX_API_DUPLICATE_NAME');
157 FND_MESSAGE.Set_Token('COLUMN', 'STATUS_RULE_NAME', FALSE);
158 FND_MESSAGE.Set_Token('VALUE', p_status_rule_Name, FALSE);
159 FND_MSG_PUB.Add;
160 END IF;
161 x_return_status := FND_API.G_RET_STS_ERROR;
162 x_dup_status := IEX_DUPLICATE_NAME;
163 END IF;
164 CLOSE C_GET_STATUS_RULE_ID_Name;
165 END IF;
166
167 -- Standard call to get message count and IF count is 1, get message info.
168
169 FND_MSG_PUB.Count_And_Get
170 ( p_count => x_msg_count,
171 p_data => x_msg_data );
172
173 END Validate_Status_Rule_ID_Name;
174
175
176 PROCEDURE Create_Status_Rule(p_api_version IN NUMBER,
177 p_init_msg_list IN VARCHAR2,
178 p_commit IN VARCHAR2,
179 P_STATUS_RULE_REC IN IEX_STATUS_RULE_PUB.STATUS_RULE_REC_TYPE,
180 x_dup_status OUT NOCOPY VARCHAR2,
181 x_return_status OUT NOCOPY VARCHAR2,
182 x_msg_count OUT NOCOPY NUMBER,
183 x_msg_data OUT NOCOPY VARCHAR2,
184 X_STATUS_RULE_ID OUT NOCOPY NUMBER)
185 IS
186 CURSOR get_seq_csr is
187 SELECT iex_cust_status_rules_s.nextval
188 FROM sys.dual;
189 --
190 l_api_name CONSTANT VARCHAR2(30) := 'Create_Status_Rule';
191 l_api_version_number CONSTANT NUMBER := 1.0;
192 l_return_status VARCHAR2(1);
193 l_msg_count NUMBER;
194 l_msg_data VARCHAR2(32767);
195 l_rowid Varchar2(50);
196 l_STATUS_RULE_REC IEX_STATUS_RULE_PUB.STATUS_RULE_REC_TYPE;
197 l_status_rule_id NUMBER ;
198
199 BEGIN
200
201 -- Standard Start of API savepoint
202 SAVEPOINT CREATE_STATUS_RULE_PVT;
203
204 l_STATUS_RULE_REC := p_status_rule_rec;
205 -- Standard call to check for call compatibility.
206 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
207 p_api_version,
208 l_api_name,
209 G_PKG_NAME)
210 THEN
211 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
212 END IF;
213
214 -- Initialize message list IF p_init_msg_list is set to TRUE.
215 IF FND_API.to_Boolean( p_init_msg_list )
216 THEN
217 FND_MSG_PUB.initialize;
218 END IF;
219
220
221 -- Initialize API return status to SUCCESS
222 x_return_status := FND_API.G_RET_STS_SUCCESS;
223
224 --
225 -- API body
226 --
227
228 -- Validate Data
229
230 Validate_Status_Rule_Name(P_Init_Msg_List => FND_API.G_FALSE,
231 P_STATUS_RULE_Name => l_status_rule_rec.status_rule_Name,
232 X_Dup_Status => x_Dup_status,
233 X_Return_Status => x_return_status,
234 X_Msg_Count => x_msg_count,
235 X_Msg_Data => x_msg_data);
236
237 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
238 RAISE FND_API.G_EXC_ERROR;
239 END IF;
240 -- ******************************************************************
241 -- Validate Environment
242 -- ******************************************************************
243 IF FND_GLOBAL.User_Id IS NULL
244 THEN
245 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
246 THEN
247 AS_UTILITY_PVT.Set_Message(
248 p_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR,
249 p_msg_name => 'UT_CANNOT_GET_PROFILE_VALUE',
250 p_token1 => 'PROFILE',
251 p_token1_value => 'USER_ID');
252
253 END IF;
254 RAISE FND_API.G_EXC_ERROR;
255 END IF;
256
257 If ( (l_status_rule_rec.status_rule_id IS NULL) OR
258 (l_status_rule_rec.status_rule_id = 0) ) then
259 OPEN get_seq_csr;
260 FETCH get_seq_csr INTO x_status_rule_id ;
261 CLOSE get_seq_csr;
262 End If;
263
264
265 -- Create Status_Rule
266 IEX_STATUS_RULE_PKG.insert_row(
267 x_rowid => l_rowid
268 , p_status_rule_id => x_status_rule_id
269 , p_status_rule_name => l_status_rule_rec.status_rule_name
270 , p_status_rule_description => l_status_rule_rec.status_rule_description
271 , p_start_date => l_status_rule_rec.start_date
272 , p_end_date => l_status_rule_rec.end_date
273 -- , p_jtf_object_code => l_status_rule_rec.jtf_object_code
274 , p_last_update_date => sysdate
275 , p_last_updated_by => FND_GLOBAL.USER_ID
276 , p_creation_date => sysdate
277 , p_created_by => FND_GLOBAL.USER_ID
278 , p_last_update_login => FND_GLOBAL.USER_ID
279 , p_object_version_number => 1.0
280 );
281
282
283 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
284 RAISE FND_API.G_EXC_ERROR;
285 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
286 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
287 END IF;
288
289
290 --
291 -- End of API body
292 --
293
294 -- Standard check for p_commit
295 IF FND_API.to_Boolean( p_commit )
296 THEN
297 COMMIT WORK;
298 END IF;
299
300
301 -- Standard call to get message count and IF count is 1, get message info.
302 FND_MSG_PUB.Count_And_Get
303 ( p_count => x_msg_count,
304 p_data => x_msg_data
305 );
306
307 EXCEPTION
308 WHEN FND_API.G_EXC_ERROR THEN
309 ROLLBACK TO CREATE_STATUS_RULE_PVT;
310 x_return_status := FND_API.G_RET_STS_ERROR;
311 FND_MSG_PUB.Count_And_Get
312 ( p_count => x_msg_count,
313 p_data => x_msg_data
314 );
315
316 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
317 ROLLBACK TO CREATE_STATUS_RULE_PVT;
318 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
319 FND_MSG_PUB.Count_And_Get
320 ( p_count => x_msg_count,
321 p_data => x_msg_data
322 );
323
324 WHEN OTHERS THEN
325 ROLLBACK TO CREATE_STATUS_RULE_PVT;
326 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
327 FND_MSG_PUB.Count_And_Get
328 ( p_count => x_msg_count,
329 p_data => x_msg_data
330 );
331
332 END CREATE_STATUS_RULE;
333
334
335
336
337 Procedure Update_Status_Rule(p_api_version IN NUMBER,
338 p_init_msg_list IN VARCHAR2,
339 p_commit IN VARCHAR2,
340 P_STATUS_RULE_REC IN IEX_STATUS_RULE_PUB.STATUS_RULE_REC_TYPE,
341 x_dup_status OUT NOCOPY VARCHAR2,
342 x_return_status OUT NOCOPY VARCHAR2,
343 x_msg_count OUT NOCOPY NUMBER,
344 x_msg_data OUT NOCOPY VARCHAR2)
345
346 IS
347 CURSOR C_get_Status_Rule_Rec (IN_STATUS_RULE_ID NUMBER) is
348 SELECT ROWID,
349 STATUS_RULE_ID,
350 STATUS_RULE_NAME,
351 STATUS_RULE_DESCRIPTION,
352 START_DATE,
353 END_DATE,
354 -- JTF_OBJECT_CODE,
355 LAST_UPDATE_DATE,
356 LAST_UPDATED_BY,
357 CREATION_DATE,
358 CREATED_BY ,
359 LAST_UPDATE_LOGIN,
360 OBJECT_VERSION_NUMBER
361 from iex_cust_status_rules
362 where status_rule_id = in_status_rule_id
363 FOR UPDATE NOWAIT;
364 --
365 l_api_name CONSTANT VARCHAR2(30) := 'Update_Status_Rule';
366 l_api_version_number CONSTANT NUMBER := 1.0;
367 l_return_status VARCHAR2(1);
368 l_msg_count NUMBER;
369 l_msg_data VARCHAR2(32767);
370 l_rowid Varchar2(50);
371 l_STATUS_RULE_REC IEX_STATUS_RULE_PUB.STATUS_RULE_REC_TYPE;
372 l_status_rule_id NUMBER ;
373 l_STATUS_RULE_REF_REC IEX_STATUS_RULE_PUB.STATUS_RULE_REC_TYPE ;
374
375
376 BEGIN
377
378 -- Standard Start of API savepoint
379 SAVEPOINT UPDATE_STATUS_RULE_PVT;
380
381 l_STATUS_RULE_REC := p_status_rule_rec;
382 -- Standard call to check for call compatibility.
383 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
384 p_api_version,
385 l_api_name,
386 G_PKG_NAME)
387 THEN
388 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
389 END IF;
390
391 -- Initialize message list IF p_init_msg_list is set to TRUE.
392 IF FND_API.to_Boolean( p_init_msg_list )
393 THEN
394 FND_MSG_PUB.initialize;
395 END IF;
396
397
398 -- Initialize API return status to SUCCESS
399 x_return_status := FND_API.G_RET_STS_SUCCESS;
400
401
402 --
403 -- Api body
404 --
405
406 Open C_Get_Status_Rule_Rec(l_status_rule_rec.STATUS_RULE_ID);
407 Fetch C_Get_Status_Rule_Rec into
408 l_rowid,
409 l_status_rule_ref_rec.STATUS_RULE_ID,
410 l_status_rule_ref_rec.STATUS_RULE_NAME,
411 l_status_rule_ref_rec.STATUS_RULE_DESCRIPTION,
412 l_status_rule_ref_rec.START_date,
413 l_status_rule_ref_rec.END_DATE,
414 -- l_status_rule_ref_rec.JTF_OBJECT_CODE,
415 l_status_rule_ref_rec.LAST_UPDATE_DATE,
416 l_status_rule_ref_rec.LAST_UPDATED_BY,
417 l_status_rule_ref_rec.CREATION_DATE,
418 l_status_rule_ref_rec.CREATED_BY,
419 l_status_rule_ref_rec.LAST_UPDATE_LOGIN,
420 l_status_rule_ref_rec.OBJECT_VERSION_NUMBER;
421
422 IF ( C_Get_STATUS_RULE_REC%NOTFOUND) THEN
423 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
424 THEN
425 FND_MESSAGE.Set_Name('IEX', 'API_MISSING_UPDATE_TARGET');
426 FND_MESSAGE.Set_Token ('INFO', 'iex_status_rules', FALSE);
427 FND_MSG_PUB.Add;
428 END IF;
429 RAISE FND_API.G_EXC_ERROR;
430 END IF;
431
432
433 Close C_Get_Status_Rule_Rec;
434
435
436 IF (l_status_rule_rec.last_update_date is NULL)
437 THEN
438 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
439 THEN
440 FND_MESSAGE.Set_Name('IEX', 'API_MISSING_ID');
441 FND_MESSAGE.Set_Token('COLUMN', 'Last_Update_Date', FALSE);
442 FND_MSG_PUB.ADD;
443 END IF;
444 RAISE FND_API.G_EXC_ERROR;
445 End IF;
446
447
448 -- Transfer Data into target record
449 l_status_rule_rec.CREATION_DATE := l_status_rule_ref_rec.CREATION_DATE;
450 l_status_rule_rec.CREATED_BY := l_status_rule_ref_rec.CREATED_BY;
451
452 l_status_rule_rec.STATUS_RULE_NAME := l_STATUS_RULE_REF_rec.STATUS_RULE_NAME;
453
454 IF l_status_rule_rec.STATUS_RULE_DESCRIPTION = NULL THEN
455 l_status_rule_rec.STATUS_RULE_DESCRIPTION := l_STATUS_RULE_REF_rec.STATUS_RULE_DESCRIPTION;
456 END IF;
457 IF l_status_rule_rec.START_DATE = NULL THEN
458 l_status_rule_rec.START_date := l_status_rule_ref_rec.START_DATE;
459 END IF;
460 IF l_status_rule_rec.END_DATE = NULL THEN
461 l_status_rule_rec.END_DATE := l_status_rule_ref_rec.END_DATE;
462 END IF;
463 -- IF l_status_rule_rec.JTF_OBJECT_CODE = NULL THEN
464 -- l_status_rule_rec.JTF_OBJECT_CODE := l_status_rule_ref_rec.JTF_OBJECT_CODE;
465 -- END IF;
466 IF l_status_rule_rec.OBJECT_VERSION_NUMBER = NULL THEN
467 l_status_rule_rec.OBJECT_VERSION_NUMBER := l_status_rule_ref_rec.OBJECT_VERSION_NUMBER;
468 END IF;
469
470 IEX_STATUS_RULE_PKG.update_row(
471 x_rowid => l_rowid
472 , p_status_rule_id => l_status_rule_rec.status_rule_id
473 , p_status_rule_name => l_status_rule_rec.status_rule_name
474 , p_status_rule_description => l_status_rule_rec.status_rule_description
475 , p_start_date => l_status_rule_rec.start_date
476 , p_end_date => l_status_rule_rec.end_date
477 -- , p_jtf_object_code => l_status_rule_rec.jtf_object_code
478 , p_last_update_date => sysdate
479 , p_last_updated_by => FND_GLOBAL.USER_ID
480 , p_creation_date => l_status_rule_rec.creation_date
481 , p_created_by => l_status_rule_rec.created_by
482 , p_last_update_login => FND_GLOBAL.USER_ID
483 , p_object_version_number => l_status_rule_rec.object_version_number);
484
485
486 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
487 RAISE FND_API.G_EXC_ERROR;
488 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
489 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
490 END IF;
491
492 --
493 -- End of API body.
494 --
495
496 -- Standard check for p_commit
497 IF FND_API.to_Boolean( p_commit )
498 THEN
499 COMMIT WORK;
500 END IF;
501
502 -- Standard call to get message count and IF count is 1, get message info.
503 FND_MSG_PUB.Count_And_Get
504 ( p_count => x_msg_count,
505 p_data => x_msg_data );
506
507 EXCEPTION
508 WHEN FND_API.G_EXC_ERROR THEN
509 ROLLBACK TO UPDATE_STATUS_RULE_PVT;
510 x_return_status := FND_API.G_RET_STS_ERROR;
511 FND_MSG_PUB.Count_And_Get
512 ( p_count => x_msg_count,
513 p_data => x_msg_data
514 );
515
516 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
517 ROLLBACK TO UPDATE_STATUS_RULE_PVT;
518 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
519 FND_MSG_PUB.Count_And_Get
520 ( p_count => x_msg_count,
521 p_data => x_msg_data
522 );
523
524 WHEN OTHERS THEN
525 ROLLBACK TO UPDATE_STATUS_RULE_PVT;
526 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
527 FND_MSG_PUB.Count_And_Get
528 ( p_count => x_msg_count,
529 p_data => x_msg_data
530 );
531
532 END Update_Status_Rule;
533
534
535
536 Procedure Delete_Status_Rule(p_api_version IN NUMBER,
537 p_init_msg_list IN VARCHAR2,
538 p_commit IN VARCHAR2,
539 P_STATUS_RULE_ID IN NUMBER,
540 x_return_status OUT NOCOPY VARCHAR2,
541 x_msg_count OUT NOCOPY NUMBER,
542 x_msg_data OUT NOCOPY VARCHAR2)
543
544 IS
545 CURSOR C_GET_STATUS_RULE (IN_STATUS_RULE_ID NUMBER) IS
546 SELECT rowid
547 FROM IEX_CUST_STATUS_RULES
548 WHERE STATUS_RULE_ID = IN_STATUS_RULE_ID;
549 --
550 CURSOR C_GET_Status_Rule_LineS (IN_STATUS_RULE_ID NUMBER) IS
551 SELECT Status_Rule_line_ID
552 FROM iex_cu_sts_rl_lines
553 WHERE STATUS_RULE_ID = IN_STATUS_RULE_ID;
554 --
555 l_status_rule_id NUMBER;
556 l_api_name CONSTANT VARCHAR2(30) := 'Delete_Status_Rule';
557 l_api_version_number CONSTANT NUMBER := 1.0;
558 l_return_status VARCHAR2(1);
559 l_msg_count NUMBER;
560 l_msg_data VARCHAR2(32767);
561 l_rowid Varchar2(50);
562
563
564 BEGIN
565 -- Standard Start of API savepoint
566 SAVEPOINT DELETE_STATUS_RULE_PVT;
567
568 -- Standard call to check for call compatibility.
569 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
570 p_api_version,
571 l_api_name,
572 G_PKG_NAME)
573 THEN
574 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
575 END IF;
576
577 -- Initialize message list IF p_init_msg_list is set to TRUE.
578 IF FND_API.to_Boolean( p_init_msg_list )
579 THEN
580 FND_MSG_PUB.initialize;
581 END IF;
582
583
584 -- Initialize API return status to SUCCESS
585 x_return_status := FND_API.G_RET_STS_SUCCESS;
586
587 --
588 -- Api body
589 --
590
591 Open C_Get_STATUS_RULE(p_status_rule_id);
592 Fetch C_Get_STATUS_RULE into
593 l_rowid;
594
595 IF ( C_Get_Status_Rule%NOTFOUND) THEN
596 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
597 THEN
598 FND_MESSAGE.Set_Name('IEX', 'API_MISSING_UPDATE_TARGET');
599 FND_MESSAGE.Set_Token ('INFO', 'iex_status_rules', FALSE);
600 FND_MSG_PUB.Add;
601 END IF;
602 RAISE FND_API.G_EXC_ERROR;
603 END IF;
604
605 Close C_Get_Status_Rule;
606
607 -- Invoke table handler
608 IEX_STATUS_RULE_PKG.Delete_Row(
609 x_rowid => l_rowid);
610
611 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
612 RAISE FND_API.G_EXC_ERROR;
613 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
614 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
615 END IF;
616
617 --
618 -- End of API body
619 --
620
621 -- Standard check for p_commit
622 IF FND_API.to_Boolean( p_commit )
623 THEN
624 COMMIT WORK;
625 END IF;
626
627
628 FND_MSG_PUB.Count_And_Get
629 ( p_count => x_msg_count,
630 p_data => x_msg_data );
631
632 EXCEPTION
633 WHEN FND_API.G_EXC_ERROR THEN
634 ROLLBACK TO DELETE_STATUS_RULE_PVT;
635 x_return_status := FND_API.G_RET_STS_ERROR;
636 FND_MSG_PUB.Count_And_Get
637 ( p_count => x_msg_count,
638 p_data => x_msg_data
639 );
640
641 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
642 ROLLBACK TO DELETE_STATUS_RULE_PVT;
643 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
644 FND_MSG_PUB.Count_And_Get
645 ( p_count => x_msg_count,
646 p_data => x_msg_data
647 );
648
649 WHEN OTHERS THEN
650 ROLLBACK TO DELETE_STATUS_RULE_PVT;
651 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
652 FND_MSG_PUB.Count_And_Get
653 ( p_count => x_msg_count,
654 p_data => x_msg_data
655 );
656
657 END Delete_Status_Rule;
658
659 Procedure Create_status_rule_line(p_api_version IN NUMBER,
660 p_init_msg_list IN VARCHAR2,
661 p_commit IN VARCHAR2,
662 p_status_rule_line_REC IN IEX_STATUS_RULE_PUB.status_rule_line_REC_Type,
663 x_return_status OUT NOCOPY VARCHAR2,
664 x_msg_count OUT NOCOPY NUMBER,
665 x_msg_data OUT NOCOPY VARCHAR2,
666 x_status_rule_line_id OUT NOCOPY NUMBER)
667
668
669 IS
670 CURSOR get_seq_csr is
671 SELECT iex_cu_sts_rl_lines_s.nextval
672 FROM sys.dual;
673 --
674 l_api_name CONSTANT VARCHAR2(30) := 'Create_status_rule_line';
675 l_api_version_number CONSTANT NUMBER := 1.0;
676 l_return_status VARCHAR2(1);
677 l_msg_count NUMBER;
678 l_msg_data VARCHAR2(32767);
679 l_rowid Varchar2(50);
680 l_status_rule_line_REC IEX_STATUS_RULE_PUB.status_rule_line_REC_TYPE := p_status_rule_line_REC;
681
682 BEGIN
683 -- Standard Start of API savepoint
684 SAVEPOINT CREATE_status_rule_line_PVT;
685
686 -- Standard call to check for call compatibility.
687 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
688 p_api_version,
689 l_api_name,
690 G_PKG_NAME)
691 THEN
692 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
693 END IF;
694
695
696 -- Initialize message list IF p_init_msg_list is set to TRUE.
697 IF FND_API.to_Boolean( p_init_msg_list )
698 THEN
699 FND_MSG_PUB.initialize;
700 END IF;
701
702
703 -- Initialize API return status to SUCCESS
704 x_return_status := FND_API.G_RET_STS_SUCCESS;
705
706 --
707 -- API body
708 --
709
710
711 -- Create Status_Rule Comp Det
712
713 If ( (l_status_rule_line_rec.status_rule_line_id IS NULL) OR
714 (l_status_rule_line_rec.status_rule_line_id = 0 )) then
715 OPEN get_seq_csr;
716 FETCH get_seq_csr INTO x_status_rule_line_id ;
717 CLOSE get_seq_csr;
718 End If;
719
720
721 IEX_status_rule_line_PKG.insert_row(
722 x_rowid => l_rowid
723 , p_status_rule_line_id => x_status_rule_line_id
724 , p_delinquency_status => l_status_rule_line_rec.delinquency_status
725 , p_priority => l_status_rule_line_rec.priority
726 , p_enabled_flag => l_status_rule_line_rec.enabled_flag
727 , p_status_rule_id => l_status_rule_line_rec.status_rule_id
728 , p_last_update_date => sysdate
729 , p_last_updated_by => FND_GLOBAL.USER_ID
730 , p_creation_date => sysdate
731 , p_created_by => FND_GLOBAL.USER_ID
732 , p_last_update_login => FND_GLOBAL.USER_ID
733 , p_object_version_number => 1.0);
734
735
736 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
737 RAISE FND_API.G_EXC_ERROR;
738 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
739 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
740 END IF;
741
742
743 --
744 -- End of API body
745 --
746
747 -- Standard check for p_commit
748 IF FND_API.to_Boolean( p_commit )
749 THEN
750 COMMIT WORK;
751 END IF;
752
753
754 -- Standard call to get message count and IF count is 1, get message info.
755 FND_MSG_PUB.Count_And_Get
756 ( p_count => x_msg_count,
757 p_data => x_msg_data
758 );
759
760 EXCEPTION
761 WHEN FND_API.G_EXC_ERROR THEN
762 ROLLBACK To CREATE_status_rule_line_PVT;
763 x_return_status := FND_API.G_RET_STS_ERROR;
764 FND_MSG_PUB.Count_And_Get
765 ( p_count => x_msg_count,
766 p_data => x_msg_data );
767
768 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
769 ROLLBACK To CREATE_status_rule_line_PVT;
770 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
771 FND_MSG_PUB.Count_And_Get
772 ( p_count => x_msg_count,
773 p_data => x_msg_data );
774
775 WHEN OTHERS THEN
776 ROLLBACK To CREATE_status_rule_line_PVT;
777 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
778 FND_MSG_PUB.Count_And_Get
779 ( p_count => x_msg_count,
780 p_data => x_msg_data );
781
782 END Create_status_rule_line;
783
784
785
786 Procedure Update_status_rule_line(p_api_version IN NUMBER,
787 p_init_msg_list IN VARCHAR2,
788 p_commit IN VARCHAR2,
789 p_status_rule_line_Rec IN IEX_STATUS_RULE_PUB.status_rule_line_REC_Type,
790 x_return_status OUT NOCOPY VARCHAR2,
791 x_msg_count OUT NOCOPY NUMBER,
792 x_msg_data OUT NOCOPY VARCHAR2)
793
794 IS
795 CURSOR C_get_status_rule_line_Rec (IN_status_rule_line_ID NUMBER) is
796 select ROWID,
797 status_rule_line_ID,
798 delinquency_status,
799 priority,
800 ENABLED_FLAG,
801 Status_Rule_line_ID,
802 LAST_UPDATE_DATE,
803 LAST_UPDATED_BY,
804 CREATION_DATE,
805 CREATED_BY ,
806 LAST_UPDATE_LOGIN,
807 OBJECT_VERSION_NUMBER
808 from iex_cu_sts_rl_lines
809 where status_rule_line_id = in_status_rule_line_id
810 FOR UPDATE NOWAIT;
811 --
812 l_api_name CONSTANT VARCHAR2(30) := 'Update_status_rule_line';
813 l_api_version_number CONSTANT NUMBER := 1.0;
814 l_return_status VARCHAR2(1);
815 l_msg_count NUMBER;
816 l_msg_data VARCHAR2(32767);
817 l_rowid Varchar2(50);
818 l_status_rule_line_REC IEX_STATUS_RULE_PUB.status_rule_line_REC_TYPE := p_status_rule_line_rec;
819 l_status_rule_line_REF_REC IEX_STATUS_RULE_PUB.status_rule_line_REC_TYPE ;
820
821
822 BEGIN
823
824 -- Standard Start of API savepoint
825 SAVEPOINT UPDATE_status_rule_line_PVT;
826
827 -- Standard call to check for call compatibility.
828 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
829 p_api_version,
830 l_api_name,
831 G_PKG_NAME)
832 THEN
833 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
834 END IF;
835
836 -- Initialize message list IF p_init_msg_list is set to TRUE.
837 IF FND_API.to_Boolean( p_init_msg_list )
838 THEN
839 FND_MSG_PUB.initialize;
840 END IF;
841
842
843 -- Initialize API return status to SUCCESS
844 x_return_status := FND_API.G_RET_STS_SUCCESS;
845
846 --
847 -- Api body
848 --
849
850
851 Open C_Get_status_rule_line_Rec(l_status_rule_line_rec.status_rule_line_ID);
852 Fetch C_Get_status_rule_line_Rec into
853 l_rowid,
854 l_status_rule_line_ref_rec.status_rule_line_ID,
855 l_status_rule_line_ref_rec.delinquency_status,
856 l_status_rule_line_ref_rec.priority,
857 l_status_rule_line_ref_rec.enabled_flag,
858 l_status_rule_line_ref_rec.Status_Rule_line_ID,
859 l_status_rule_line_ref_rec.LAST_UPDATE_DATE,
860 l_status_rule_line_ref_rec.LAST_UPDATED_BY,
861 l_status_rule_line_ref_rec.CREATION_DATE,
862 l_status_rule_line_ref_rec.CREATED_BY,
863 l_status_rule_line_ref_rec.LAST_UPDATE_LOGIN,
864 l_status_rule_line_ref_Rec.OBJECT_VERSION_NUMBER;
865
866 IF ( C_Get_status_rule_line_REC%NOTFOUND) THEN
867 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
868 THEN
869 FND_MESSAGE.Set_Name('IEX', 'API_MISSING_UPDATE_TARGET');
870 FND_MESSAGE.Set_Token ('INFO', 'iex_status_rule_lines', FALSE);
871 FND_MSG_PUB.Add;
872 END IF;
873 RAISE FND_API.G_EXC_ERROR;
874 END IF;
875
876 Close C_Get_status_rule_line_Rec;
877
878
879
880 IF (l_status_rule_line_rec.last_update_date is NULL)
881 THEN
882 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
883 THEN
884 FND_MESSAGE.Set_Name('IEX', 'API_MISSING_ID');
885 FND_MESSAGE.Set_Token('COLUMN', 'Last_Update_Date', FALSE);
886 FND_MSG_PUB.ADD;
887 END IF;
888 RAISE FND_API.G_EXC_ERROR;
889 End IF;
890
891
892 -- Transfer Data into target record
893 l_status_rule_line_rec.CREATION_DATE := l_status_rule_line_ref_rec.CREATION_DATE;
894 l_status_rule_line_rec.CREATED_BY := l_status_rule_line_ref_rec.CREATED_BY;
895
896 IF l_status_rule_line_rec.delinquency_status = NULL THEN
897 l_status_rule_line_rec.delinquency_status := l_status_rule_line_REF_rec.delinquency_status;
898 END IF;
899 IF l_status_rule_line_rec.priority = NULL THEN
900 l_status_rule_line_rec.priority := l_status_rule_line_REF_rec.priority;
901 END IF;
902 IF l_status_rule_line_rec.enabled_flag = NULL THEN
903 l_status_rule_line_rec.enabled_flag := l_status_rule_line_REF_rec.enabled_flag;
904 END IF;
905 IF l_status_rule_line_rec.object_version_number = NULL THEN
906 l_status_rule_line_rec.object_version_number := l_status_rule_line_REF_rec.object_version_number;
907 END IF;
908
909 iex_status_rule_line_PKG.update_row(
910 x_rowid => l_rowid
911 , p_status_rule_line_id => l_status_rule_line_rec.status_rule_line_id
912 , p_last_update_date => sysdate
913 , p_last_updated_by => FND_GLOBAL.USER_ID
914 , p_creation_date => l_status_rule_line_rec.CREATION_DATE
915 , p_created_by => l_status_rule_line_rec.CREATED_BY
916 , p_last_update_login => FND_GLOBAL.USER_ID
917 , p_delinquency_status => l_status_rule_line_rec.delinquency_status
918 , p_priority => l_status_rule_line_rec.priority
919 , p_enabled_flag => l_status_rule_line_rec.enabled_flag
920 , p_status_rule_id => l_status_rule_line_rec.status_rule_id
921 , p_object_version_number => l_status_rule_line_rec.object_version_number);
922
923 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
924 RAISE FND_API.G_EXC_ERROR;
925 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
926 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
927 END IF;
928
929 --
930 -- End of API body.
931 --
932
933 -- Standard check for p_commit
934 IF FND_API.to_Boolean( p_commit )
935 THEN
936 COMMIT WORK;
937 END IF;
938
939
940 -- Standard call to get message count and IF count is 1, get message info.
941 FND_MSG_PUB.Count_And_Get
942 ( p_count => x_msg_count,
943 p_data => x_msg_data );
944
945 EXCEPTION
946 WHEN FND_API.G_EXC_ERROR THEN
947 ROLLBACK To UPDATE_status_rule_line_PVT;
948 x_return_status := FND_API.G_RET_STS_ERROR;
949 FND_MSG_PUB.Count_And_Get
950 ( p_count => x_msg_count,
951 p_data => x_msg_data );
952
953 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
954 ROLLBACK To UPDATE_status_rule_line_PVT;
955 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
956 FND_MSG_PUB.Count_And_Get
957 ( p_count => x_msg_count,
958 p_data => x_msg_data );
959
960 WHEN OTHERS THEN
961 ROLLBACK To UPDATE_status_rule_line_PVT;
962 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
963 FND_MSG_PUB.Count_And_Get
964 ( p_count => x_msg_count,
965 p_data => x_msg_data );
966 END Update_status_rule_line;
967
968
969
970 Procedure Delete_status_rule_line(p_api_version IN NUMBER,
971 p_init_msg_list IN VARCHAR2,
972 p_commit IN VARCHAR2,
973 p_Status_Rule_id IN NUMBER,
974 p_status_rule_line_id IN NUMBER,
975 x_return_status OUT NOCOPY VARCHAR2,
976 x_msg_count OUT NOCOPY NUMBER,
977 x_msg_data OUT NOCOPY VARCHAR2)
978
979 IS
980 CURSOR C_GET_status_rule_line (IN_status_rule_line_ID NUMBER) IS
981 SELECT rowid
982 FROM iex_cu_sts_rl_lines
983 WHERE status_rule_line_ID = IN_status_rule_line_ID;
984 --
985 l_status_rule_line_id NUMBER;
986 l_api_name CONSTANT VARCHAR2(30) := 'Delete_status_rule_line';
987 l_api_version_number CONSTANT NUMBER := 1.0;
988 l_return_status VARCHAR2(1);
989 l_msg_count NUMBER;
990 l_msg_data VARCHAR2(32767);
991 l_rowid Varchar2(50);
992
993 BEGIN
994 -- Standard Start of API savepoint
995 SAVEPOINT DELETE_status_rule_line_PUB;
996
997 -- Standard call to check for call compatibility.
998 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
999 p_api_version,
1000 l_api_name,
1001 G_PKG_NAME)
1002 THEN
1003 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1004 END IF;
1005
1006 -- Initialize message list IF p_init_msg_list is set to TRUE.
1007 IF FND_API.to_Boolean( p_init_msg_list )
1008 THEN
1009 FND_MSG_PUB.initialize;
1010 END IF;
1011
1012
1013 -- Initialize API return status to SUCCESS
1014 x_return_status := FND_API.G_RET_STS_SUCCESS;
1015
1016 --
1017 -- Api body
1018 --
1019 Open C_Get_status_rule_line(p_status_rule_line_id);
1020 Fetch C_Get_status_rule_line into
1021 l_rowid;
1022
1023 IF ( C_Get_status_rule_line%NOTFOUND) THEN
1024 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
1025 THEN
1026 FND_MESSAGE.Set_Name('IEX', 'API_MISSING_UPDATE_TARGET');
1027 FND_MESSAGE.Set_Token ('INFO', 'iex_status_rule_lines', FALSE);
1028 FND_MSG_PUB.Add;
1029 END IF;
1030 RAISE FND_API.G_EXC_ERROR;
1031 END IF;
1032
1033 Close C_Get_status_rule_line;
1034
1035
1036 -- Invoke table handler
1037 iex_status_rule_line_PKG.Delete_Row(x_rowid => l_rowid);
1038
1039 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1040 RAISE FND_API.G_EXC_ERROR;
1041 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1042 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1043 END IF;
1044
1045 --
1046 -- End of API body
1047 --
1048
1049 -- Standard check for p_commit
1050 IF FND_API.to_Boolean( p_commit )
1051 THEN
1052 COMMIT WORK;
1053 END IF;
1054
1055
1056 FND_MSG_PUB.Count_And_Get
1057 ( p_count => x_msg_count,
1058 p_data => x_msg_data );
1059
1060 EXCEPTION
1061 WHEN FND_API.G_EXC_ERROR THEN
1062 ROLLBACK To DELETE_status_rule_line_PVT;
1063 x_return_status := FND_API.G_RET_STS_ERROR;
1064 FND_MSG_PUB.Count_And_Get
1065 ( p_count => x_msg_count,
1066 p_data => x_msg_data );
1067
1068 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1069 ROLLBACK To DELETE_status_rule_line_PVT;
1070 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1071 FND_MSG_PUB.Count_And_Get
1072 ( p_count => x_msg_count,
1073 p_data => x_msg_data );
1074
1075 WHEN OTHERS THEN
1076 ROLLBACK To DELETE_status_rule_line_PVT;
1077 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1078 FND_MSG_PUB.Count_And_Get
1079 ( p_count => x_msg_count,
1080 p_data => x_msg_data );
1081 END Delete_status_rule_line;
1082 BEGIN
1083 PG_DEBUG := TO_NUMBER(NVL(FND_PROFILE.value('IEX_DEBUG_LEVEL'), '20'));
1084 END IEX_STATUS_RULE_PVT;