[Home] [Help]
PACKAGE BODY: APPS.OZF_REASON_PVT
Source
1 PACKAGE BODY OZF_Reason_PVT as
2 /* $Header: ozfvreab.pls 120.2 2005/07/29 06:26:26 appldev ship $ */
3 -- Start of Comments
4 -- Package name : OZF_Reason_PVT
5 -- Purpose :
6 -- History :
7 -- 28-OCT-2002 UPOLURI Add one more column: ORDER_TYPE_ID NUMBER
8 -- 28-SEP-2003 ANUJGUPT Add one more column: PARTNER_ACCESS_FLAG VARCHAR2(1)
9 -- History : 22-Jun-2005 KDHULIPA Add one more column: INVOICING_REASON_CODE VARCHAR2(30)
10 -- NOTE :
11 -- End of Comments
12
13
14 G_PKG_NAME CONSTANT VARCHAR2(30):= 'OZF_Reason_PVT';
15 G_FILE_NAME CONSTANT VARCHAR2(12) := 'ozfvreab.pls';
16
17 OZF_DEBUG_HIGH_ON BOOLEAN := FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_debug_high);
18
19 -- Hint: Primary key needs to be returned.
20 PROCEDURE Create_reason(
21 P_Api_Version_Number IN NUMBER,
22 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
23 P_Commit IN VARCHAR2 := FND_API.G_FALSE,
24 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
25
26 X_Return_Status OUT NOCOPY VARCHAR2,
27 X_Msg_Count OUT NOCOPY NUMBER,
28 X_Msg_Data OUT NOCOPY VARCHAR2,
29
30 P_reason_Rec IN reason_Rec_Type := G_MISS_reason_REC,
31 X_REASON_CODE_ID OUT NOCOPY NUMBER
32 )
33
34 IS
35 l_api_name CONSTANT VARCHAR2(30) := 'Create_reason';
36 l_api_version_number CONSTANT NUMBER := 1.0;
37 l_return_status_full VARCHAR2(1);
38 l_object_version_number NUMBER := 1;
39 l_org_id NUMBER := FND_API.G_MISS_NUM;
40 l_REASON_CODE_ID NUMBER;
41
42 BEGIN
43 -- Standard Start of API savepoint
44 SAVEPOINT CREATE_Reason_PVT;
45
46 -- Standard call to check for call compatibility.
47 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
48 p_api_version_number,
49 l_api_name,
50 G_PKG_NAME)
51 THEN
52 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
53 END IF;
54
55 -- Initialize message list if p_init_msg_list is set to TRUE.
56 IF FND_API.to_Boolean( p_init_msg_list )
57 THEN
58 FND_MSG_PUB.initialize;
59 END IF;
60
61 -- Debug Message
62 IF OZF_DEBUG_HIGH_ON THEN
63 OZF_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
64 END IF;
65
66 -- Initialize API return status to SUCCESS
67 x_return_status := FND_API.G_RET_STS_SUCCESS;
68
69 -- ******************************************************************
70 -- Validate Environment
71 -- ******************************************************************
72 IF FND_GLOBAL.User_Id IS NULL
73 THEN
74 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
75 THEN
76 FND_MESSAGE.Set_Name('OZF', 'OZF_USER_PROFILE_MISSING');
77 FND_MSG_PUB.ADD;
78 END IF;
79 RAISE FND_API.G_EXC_ERROR;
80 END IF;
81
82 IF ( P_validation_level >= FND_API.G_VALID_LEVEL_FULL)
83 THEN
84 -- Debug message
85 IF OZF_DEBUG_HIGH_ON THEN
86 OZF_UTILITY_PVT.debug_message('Private API: Validate_reason');
87 END IF;
88
89 -- Invoke validation procedures
90 Validate_reason(
91 p_api_version_number => 1.0,
92 p_init_msg_list => FND_API.G_FALSE,
93 p_validation_level => p_validation_level,
94 P_reason_Rec => P_reason_Rec,
95 x_return_status => x_return_status,
96 x_msg_count => x_msg_count,
97 x_msg_data => x_msg_data);
98 END IF;
99
100 IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
101 RAISE FND_API.G_EXC_ERROR;
102 END IF;
103
104
105 -- Debug Message
106 IF OZF_DEBUG_HIGH_ON THEN
107 OZF_UTILITY_PVT.debug_message( 'Private API: Calling create table handler');
108 END IF;
109
110 -- Pass org_id from UI
111 l_org_id := p_reason_rec.ORG_ID;
112
113
114 -- Invoke table handler(OZF_reason_codes_All_PKG.Insert_Row)
115 OZF_reason_codes_All_PKG.Insert_Row(
116 px_REASON_CODE_ID => l_REASON_CODE_ID,
117 px_OBJECT_VERSION_NUMBER => l_object_version_number,
118 p_LAST_UPDATE_DATE => SYSDATE,
119 p_LAST_UPDATED_BY => FND_GLOBAL.USER_ID,
120 p_CREATION_DATE => SYSDATE,
121 p_CREATED_BY => FND_GLOBAL.USER_ID,
122 p_LAST_UPDATE_LOGIN => FND_GLOBAL.CONC_LOGIN_ID,
123 p_REASON_CODE => p_reason_rec.REASON_CODE,
124 p_START_DATE_ACTIVE => p_reason_rec.START_DATE_ACTIVE,
125 p_END_DATE_ACTIVE => p_reason_rec.END_DATE_ACTIVE,
126 p_ATTRIBUTE_CATEGORY => p_reason_rec.ATTRIBUTE_CATEGORY,
127 p_ATTRIBUTE1 => p_reason_rec.ATTRIBUTE1,
128 p_ATTRIBUTE2 => p_reason_rec.ATTRIBUTE2,
129 p_ATTRIBUTE3 => p_reason_rec.ATTRIBUTE3,
130 p_ATTRIBUTE4 => p_reason_rec.ATTRIBUTE4,
131 p_ATTRIBUTE5 => p_reason_rec.ATTRIBUTE5,
132 p_ATTRIBUTE6 => p_reason_rec.ATTRIBUTE6,
133 p_ATTRIBUTE7 => p_reason_rec.ATTRIBUTE7,
134 p_ATTRIBUTE8 => p_reason_rec.ATTRIBUTE8,
135 p_ATTRIBUTE9 => p_reason_rec.ATTRIBUTE9,
136 p_ATTRIBUTE10 => p_reason_rec.ATTRIBUTE10,
137 p_ATTRIBUTE11 => p_reason_rec.ATTRIBUTE11,
138 p_ATTRIBUTE12 => p_reason_rec.ATTRIBUTE12,
139 p_ATTRIBUTE13 => p_reason_rec.ATTRIBUTE13,
140 p_ATTRIBUTE14 => p_reason_rec.ATTRIBUTE14,
141 p_ATTRIBUTE15 => p_reason_rec.ATTRIBUTE15,
142 p_NAME => p_reason_rec.NAME,
143 p_DESCRIPTION => p_reason_rec.DESCRIPTION,
144 px_ORG_ID => l_org_id,
145 p_REASON_TYPE => p_reason_rec.REASON_TYPE,
146 p_ADJUSTMENT_REASON_CODE => p_reason_rec.ADJUSTMENT_REASON_CODE,
147 p_INVOICING_REASON_CODE => p_reason_rec.INVOICING_REASON_CODE,
148 px_ORDER_TYPE_ID => p_reason_rec.ORDER_TYPE_ID,
149 p_PARTNER_ACCESS_FLAG => p_reason_rec.PARTNER_ACCESS_FLAG
150 );
151 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
152 RAISE FND_API.G_EXC_ERROR;
153 END IF;
154 --
155 -- End of API body
156 --
157 x_reason_code_id := l_reason_code_id;
158
159 -- Standard check for p_commit
160 IF FND_API.to_Boolean( p_commit )
161 THEN
162 COMMIT WORK;
163 END IF;
164
165
166 -- Debug Message
167 IF OZF_DEBUG_HIGH_ON THEN
168 OZF_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'end');
169 END IF;
170
171 -- Standard call to get message count and if count is 1, get message info.
172 FND_MSG_PUB.Count_And_Get
173 (p_count => x_msg_count,
174 p_data => x_msg_data
175 );
176 EXCEPTION
177 WHEN FND_API.G_EXC_ERROR THEN
178 ROLLBACK TO CREATE_Reason_PVT;
179 x_return_status := FND_API.G_RET_STS_ERROR;
180 -- Standard call to get message count and if count=1, get the message
181 FND_MSG_PUB.Count_And_Get (
182 p_encoded => FND_API.G_FALSE,
183 p_count => x_msg_count,
184 p_data => x_msg_data
185 );
186 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
187 ROLLBACK TO CREATE_Reason_PVT;
188 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
189 -- Standard call to get message count and if count=1, get the message
190 FND_MSG_PUB.Count_And_Get (
191 p_encoded => FND_API.G_FALSE,
192 p_count => x_msg_count,
193 p_data => x_msg_data
194 );
195 WHEN OTHERS THEN
196 ROLLBACK TO CREATE_Reason_PVT;
197 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
198 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
199 THEN
200 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
201 END IF;
202 -- Standard call to get message count and if count=1, get the message
203 FND_MSG_PUB.Count_And_Get (
204 p_encoded => FND_API.G_FALSE,
205 p_count => x_msg_count,
206 p_data => x_msg_data
207 );
208 End Create_reason;
209
210
211 PROCEDURE Update_reason(
212 P_Api_Version_Number IN NUMBER,
213 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
214 P_Commit IN VARCHAR2 := FND_API.G_FALSE,
215 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
216
217 X_Return_Status OUT NOCOPY VARCHAR2,
218 X_Msg_Count OUT NOCOPY NUMBER,
219 X_Msg_Data OUT NOCOPY VARCHAR2,
220
221 P_reason_Rec IN reason_Rec_Type,
222 X_Object_Version_Number OUT NOCOPY NUMBER
223 )
224
225 IS
226
227 Cursor C_Get_reason(l_REASON_CODE_ID Number) IS
228 SELECT REASON_CODE_ID,
229 OBJECT_VERSION_NUMBER,
230 LAST_UPDATE_DATE,
231 LAST_UPDATED_BY,
232 CREATION_DATE,
233 CREATED_BY,
234 LAST_UPDATE_LOGIN,
235 REASON_CODE,
236 START_DATE_ACTIVE,
237 END_DATE_ACTIVE,
238 ATTRIBUTE_CATEGORY,
239 ATTRIBUTE1,
240 ATTRIBUTE2,
241 ATTRIBUTE3,
242 ATTRIBUTE4,
243 ATTRIBUTE5,
244 ATTRIBUTE6,
245 ATTRIBUTE7,
246 ATTRIBUTE8,
247 ATTRIBUTE9,
248 ATTRIBUTE10,
249 ATTRIBUTE11,
250 ATTRIBUTE12,
251 ATTRIBUTE13,
252 ATTRIBUTE14,
253 ATTRIBUTE15,
254 NAME,
255 DESCRIPTION,
256 ORG_ID,
257 REASON_TYPE,
258 ADJUSTMENT_REASON_CODE,
259 INVOICING_REASON_CODE,
260 ORDER_TYPE_ID,
261 PARTNER_ACCESS_FLAG
262 FROM ozf_reason_codes_ALL_VL
263 WHERE REASON_CODE_ID = l_REASON_CODE_ID;
264
265 l_api_name CONSTANT VARCHAR2(30) := 'Update_reason';
266 l_api_version_number CONSTANT NUMBER := 1.0;
267 -- Local Variables
268 l_object_version_number NUMBER;
269 l_REASON_CODE_ID NUMBER;
270 l_ref_reason_rec OZF_reason_PVT.reason_Rec_Type;
271 l_reason_rec OZF_reason_PVT.reason_Rec_Type;
272 l_tar_reason_rec OZF_reason_PVT.reason_Rec_Type := P_reason_Rec;
273 l_rowid ROWID;
274
275 BEGIN
276 -- Standard Start of API savepoint
277 SAVEPOINT UPDATE_Reason_PVT;
278
279 -- Standard call to check for call compatibility.
280 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
281 p_api_version_number,
282 l_api_name,
283 G_PKG_NAME)
284 THEN
285 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
286 END IF;
287
288 -- Initialize message list if p_init_msg_list is set to TRUE.
289 IF FND_API.to_Boolean( p_init_msg_list )
290 THEN
291 FND_MSG_PUB.initialize;
292 END IF;
293
294 -- Debug Message
295 IF OZF_DEBUG_HIGH_ON THEN
296 OZF_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
297 END IF;
298
299
300 -- Initialize API return status to SUCCESS
301 x_return_status := FND_API.G_RET_STS_SUCCESS;
302
303 -- Debug Message
304 IF OZF_DEBUG_HIGH_ON THEN
305 OZF_UTILITY_PVT.debug_message('Private API: - Open Cursor to Select');
306 END IF;
307
308 Open C_Get_reason( l_tar_reason_rec.REASON_CODE_ID);
309 Fetch C_Get_reason into
310 l_ref_reason_rec.REASON_CODE_ID,
311 l_ref_reason_rec.OBJECT_VERSION_NUMBER,
312 l_ref_reason_rec.LAST_UPDATE_DATE,
313 l_ref_reason_rec.LAST_UPDATED_BY,
314 l_ref_reason_rec.CREATION_DATE,
315 l_ref_reason_rec.CREATED_BY,
316 l_ref_reason_rec.LAST_UPDATE_LOGIN,
317 l_ref_reason_rec.REASON_CODE,
318 l_ref_reason_rec.START_DATE_ACTIVE,
319 l_ref_reason_rec.END_DATE_ACTIVE,
320 l_ref_reason_rec.ATTRIBUTE_CATEGORY,
321 l_ref_reason_rec.ATTRIBUTE1,
322 l_ref_reason_rec.ATTRIBUTE2,
323 l_ref_reason_rec.ATTRIBUTE3,
324 l_ref_reason_rec.ATTRIBUTE4,
325 l_ref_reason_rec.ATTRIBUTE5,
326 l_ref_reason_rec.ATTRIBUTE6,
327 l_ref_reason_rec.ATTRIBUTE7,
328 l_ref_reason_rec.ATTRIBUTE8,
329 l_ref_reason_rec.ATTRIBUTE9,
330 l_ref_reason_rec.ATTRIBUTE10,
331 l_ref_reason_rec.ATTRIBUTE11,
332 l_ref_reason_rec.ATTRIBUTE12,
333 l_ref_reason_rec.ATTRIBUTE13,
334 l_ref_reason_rec.ATTRIBUTE14,
335 l_ref_reason_rec.ATTRIBUTE15,
336 l_ref_reason_rec.NAME,
337 l_ref_reason_rec.DESCRIPTION,
338 l_ref_reason_rec.ORG_ID,
339 l_ref_reason_rec.REASON_TYPE,
340 l_ref_reason_rec.ADJUSTMENT_REASON_CODE,
341 l_ref_reason_rec.INVOICING_REASON_CODE,
342 l_ref_reason_rec.ORDER_TYPE_ID,
343 l_ref_reason_rec.PARTNER_ACCESS_FLAG;
344
345 If ( C_Get_reason%NOTFOUND) Then
346 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
347 THEN
348 FND_MESSAGE.Set_Name('OZF', 'OZF_API_RECORD_NOT_FOUND');
349 FND_MSG_PUB.Add;
350 END IF;
351 raise FND_API.G_EXC_ERROR;
352 END IF;
353 -- Debug Message
354 IF OZF_DEBUG_HIGH_ON THEN
355 OZF_UTILITY_PVT.debug_message('Private API: - Close Cursor');
356 END IF;
357 Close C_Get_reason;
358
359
360 If (l_tar_reason_rec.object_version_number is NULL or
361 l_tar_reason_rec.object_version_number = FND_API.G_MISS_NUM ) Then
362 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
363 THEN
364 FND_MESSAGE.Set_Name('OZF', 'OZF_API_NO_OBJ_VER_NUM');
365 FND_MSG_PUB.ADD;
366 END IF;
367 raise FND_API.G_EXC_ERROR;
368 End if;
369
370 -- Check Whether record has been changed by someone else
371 If (l_tar_reason_rec.object_version_number <> l_ref_reason_rec.object_version_number) Then
372 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
373 THEN
374 FND_MESSAGE.Set_Name('OZF', 'OZF_API_RESOURCE_LOCKED');
375 FND_MSG_PUB.ADD;
376 END IF;
377 raise FND_API.G_EXC_ERROR;
378 End if;
379
380 Complete_reason_Rec(
381 P_reason_rec => P_reason_rec,
382 x_complete_rec => l_reason_rec,
383 x_return_status => x_return_status
384 );
385 IF x_return_status = FND_API.g_ret_sts_error THEN
386 RAISE FND_API.g_exc_error;
387 ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
388 RAISE FND_API.g_exc_unexpected_error;
389 END IF;
390
391 IF OZF_DEBUG_HIGH_ON THEN
392 OZF_UTILITY_PVT.debug_message('Private API: Before Validate');
393 END IF;
394 IF ( P_validation_level >= FND_API.G_VALID_LEVEL_FULL)
395 THEN
396 -- Debug message
397 IF OZF_DEBUG_HIGH_ON THEN
398 OZF_UTILITY_PVT.debug_message('Private API: Validate_reason');
399 END IF;
400
401 -- Invoke validation procedures
402 Validate_reason(
403 p_api_version_number => 1.0,
404 p_init_msg_list => FND_API.G_FALSE,
405 p_validation_level => p_validation_level,
406 P_reason_Rec => l_reason_rec,
407 x_return_status => x_return_status,
408 x_msg_count => x_msg_count,
409 x_msg_data => x_msg_data);
410 END IF;
411
412 IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
413 RAISE FND_API.G_EXC_ERROR;
414 END IF;
415 IF OZF_DEBUG_HIGH_ON THEN
416 OZF_UTILITY_PVT.debug_message('Private API: After Validate');
417 END IF;
418
419
420
421 -- Debug Message
422 IF OZF_DEBUG_HIGH_ON THEN
423 OZF_UTILITY_PVT.debug_message('Private API:Calling update table handler');
424 END IF;
425
426 l_object_version_number := l_reason_rec.OBJECT_VERSION_NUMBER + 1;
427
428 -- Invoke table handler(OZF_reason_codes_All_PKG.Update_Row)
429 OZF_reason_codes_All_PKG.Update_Row(
430 p_REASON_CODE_ID => l_reason_rec.REASON_CODE_ID,
431 p_OBJECT_VERSION_NUMBER => l_object_version_number,
432 p_LAST_UPDATE_DATE => SYSDATE,
433 p_LAST_UPDATED_BY => FND_GLOBAL.USER_ID,
434 p_LAST_UPDATE_LOGIN => FND_GLOBAL.CONC_LOGIN_ID,
435 p_REASON_CODE => l_reason_rec.REASON_CODE,
436 p_START_DATE_ACTIVE => l_reason_rec.START_DATE_ACTIVE,
437 p_END_DATE_ACTIVE => l_reason_rec.END_DATE_ACTIVE,
438 p_ATTRIBUTE_CATEGORY => l_reason_rec.ATTRIBUTE_CATEGORY,
439 p_ATTRIBUTE1 => l_reason_rec.ATTRIBUTE1,
440 p_ATTRIBUTE2 => l_reason_rec.ATTRIBUTE2,
441 p_ATTRIBUTE3 => l_reason_rec.ATTRIBUTE3,
442 p_ATTRIBUTE4 => l_reason_rec.ATTRIBUTE4,
443 p_ATTRIBUTE5 => l_reason_rec.ATTRIBUTE5,
444 p_ATTRIBUTE6 => l_reason_rec.ATTRIBUTE6,
445 p_ATTRIBUTE7 => l_reason_rec.ATTRIBUTE7,
446 p_ATTRIBUTE8 => l_reason_rec.ATTRIBUTE8,
447 p_ATTRIBUTE9 => l_reason_rec.ATTRIBUTE9,
448 p_ATTRIBUTE10 => l_reason_rec.ATTRIBUTE10,
449 p_ATTRIBUTE11 => l_reason_rec.ATTRIBUTE11,
450 p_ATTRIBUTE12 => l_reason_rec.ATTRIBUTE12,
451 p_ATTRIBUTE13 => l_reason_rec.ATTRIBUTE13,
452 p_ATTRIBUTE14 => l_reason_rec.ATTRIBUTE14,
453 p_ATTRIBUTE15 => l_reason_rec.ATTRIBUTE15,
454 p_NAME => l_reason_rec.NAME,
455 p_DESCRIPTION => l_reason_rec.DESCRIPTION,
456 p_ORG_ID => l_reason_rec.ORG_ID,
457 p_REASON_TYPE => l_reason_rec.REASON_TYPE,
458 p_ADJUSTMENT_REASON_CODE => l_reason_rec.ADJUSTMENT_REASON_CODE,
459 p_INVOICING_REASON_CODE => l_reason_rec.INVOICING_REASON_CODE,
460 p_ORDER_TYPE_ID => l_reason_rec.ORDER_TYPE_ID,
461 p_PARTNER_ACCESS_FLAG => l_reason_rec.PARTNER_ACCESS_FLAG
462 );
463 --
464 -- End of API body.
465 --
466 x_object_version_number := l_object_version_number;
467
468 -- Standard check for p_commit
469 IF FND_API.to_Boolean( p_commit )
470 THEN
471 COMMIT WORK;
472 END IF;
473
474
475 -- Debug Message
476 IF OZF_DEBUG_HIGH_ON THEN
477 OZF_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'end');
478 END IF;
479
480 -- Standard call to get message count and if count is 1, get message info.
481 FND_MSG_PUB.Count_And_Get
482 (p_count => x_msg_count,
483 p_data => x_msg_data
484 );
485 EXCEPTION
486 WHEN FND_API.G_EXC_ERROR THEN
487 ROLLBACK TO UPDATE_Reason_PVT;
488 x_return_status := FND_API.G_RET_STS_ERROR;
489 -- Standard call to get message count and if count=1, get the message
490 FND_MSG_PUB.Count_And_Get (
491 p_encoded => FND_API.G_FALSE,
492 p_count => x_msg_count,
493 p_data => x_msg_data
494 );
495 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
496 ROLLBACK TO UPDATE_Reason_PVT;
497 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
498 -- Standard call to get message count and if count=1, get the message
499 FND_MSG_PUB.Count_And_Get (
500 p_encoded => FND_API.G_FALSE,
501 p_count => x_msg_count,
502 p_data => x_msg_data
503 );
504 WHEN OTHERS THEN
505 ROLLBACK TO UPDATE_Reason_PVT;
506 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
507 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
508 THEN
509 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
510 END IF;
511 -- Standard call to get message count and if count=1, get the message
512 FND_MSG_PUB.Count_And_Get (
513 p_encoded => FND_API.G_FALSE,
514 p_count => x_msg_count,
515 p_data => x_msg_data
516 );
517 End Update_reason;
518
519
520 PROCEDURE Delete_reason(
521 P_Api_Version_Number IN NUMBER,
522 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
523 P_Commit IN VARCHAR2 := FND_API.G_FALSE,
524 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
525 X_Return_Status OUT NOCOPY VARCHAR2,
526 X_Msg_Count OUT NOCOPY NUMBER,
527 X_Msg_Data OUT NOCOPY VARCHAR2,
528 P_REASON_CODE_ID IN NUMBER,
529 P_Object_Version_Number IN NUMBER
530 )
531 IS
532 l_api_name CONSTANT VARCHAR2(30) := 'Delete_reason';
533 l_api_version_number CONSTANT NUMBER := 1.0;
534 l_object_version_number NUMBER;
535 l_reason_code_id NUMBER;
536
537 CURSOR exist_reason_csr(p_id in number) IS
538 select reason_code_id
539 from ozf_claims_all
540 where reason_code_id = p_id;
541
542 BEGIN
543 -- Standard Start of API savepoint
544 SAVEPOINT DELETE_Reason_PVT;
545
546 -- Standard call to check for call compatibility.
547 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
548 p_api_version_number,
549 l_api_name,
550 G_PKG_NAME)
551 THEN
552 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
553 END IF;
554
555 -- Initialize message list if p_init_msg_list is set to TRUE.
556 IF FND_API.to_Boolean( p_init_msg_list )
557 THEN
558 FND_MSG_PUB.initialize;
559 END IF;
560
561 -- Debug Message
562 IF OZF_DEBUG_HIGH_ON THEN
563 OZF_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
564 END IF;
565
566
567 -- Initialize API return status to SUCCESS
568 x_return_status := FND_API.G_RET_STS_SUCCESS;
569
570 --
571 -- Api body
572 --
573 -- Debug Message
574 IF OZF_DEBUG_HIGH_ON THEN
575 OZF_UTILITY_PVT.debug_message( 'Private API: Calling delete table handler');
576 END IF;
577
578 OPEN exist_reason_csr(p_reason_code_id);
579 FETCH exist_reason_csr INTO l_reason_code_id;
580 CLOSE exist_reason_csr;
581
582 IF l_reason_code_id IS NOT NULL THEN
583 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
584 FND_MESSAGE.set_name('OZF', 'OZF_CLAIM_REASON_ID_USED');
585 FND_MSG_PUB.add;
586 END IF;
587 RAISE FND_API.g_exc_error;
588 END IF;
589
590 -- Invoke table handler(OZF_reason_codes_All_PKG.Delete_Row)
591 OZF_reason_codes_All_PKG.Delete_Row(
592 p_REASON_CODE_ID => p_REASON_CODE_ID);
593 --
594 -- End of API body
595 --
596
597 -- Standard check for p_commit
598 IF FND_API.to_Boolean( p_commit )
599 THEN
600 COMMIT WORK;
601 END IF;
602
603
604 -- Debug Message
605 IF OZF_DEBUG_HIGH_ON THEN
606 OZF_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'end');
607 END IF;
608
609 -- Standard call to get message count and if count is 1, get message info.
610 FND_MSG_PUB.Count_And_Get
611 (p_count => x_msg_count,
612 p_data => x_msg_data
613 );
614 EXCEPTION
615 WHEN FND_API.G_EXC_ERROR THEN
616 ROLLBACK TO DELETE_Reason_PVT;
617 x_return_status := FND_API.G_RET_STS_ERROR;
618 -- Standard call to get message count and if count=1, get the message
619 FND_MSG_PUB.Count_And_Get (
620 p_encoded => FND_API.G_FALSE,
621 p_count => x_msg_count,
622 p_data => x_msg_data
623 );
624 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
625 ROLLBACK TO DELETE_Reason_PVT;
626 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
627 -- Standard call to get message count and if count=1, get the message
628 FND_MSG_PUB.Count_And_Get (
629 p_encoded => FND_API.G_FALSE,
630 p_count => x_msg_count,
631 p_data => x_msg_data
632 );
633 WHEN OTHERS THEN
634 ROLLBACK TO DELETE_Reason_PVT;
635 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
636 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
637 THEN
638 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
639 END IF;
640 -- Standard call to get message count and if count=1, get the message
641 FND_MSG_PUB.Count_And_Get (
642 p_encoded => FND_API.G_FALSE,
643 p_count => x_msg_count,
644 p_data => x_msg_data
645 );
646 End Delete_reason;
647
648
649 -- *******************************************************
650 -- Start of Comments
651 -- *******************************************************
652 -- API Name: Update_actions
653 -- Type : Private
654 -- Pre-Req :
655 -- Parameters:
656 -- IN
657 -- p_api_version_number IN NUMBER Required
658 -- p_init_msg_list IN VARCHAR2 Optional Default=FND_API_G_FALSE
659 -- p_commit IN VARCHAR2 Optional Default=FND_API.G_FALSE
660 -- p_validation_level IN NUMBER Optional Default=FND_API.G_VALID_LEVEL_FULL
661 -- p_action_Tbl IN action_Tbl_Type
662 --
663 -- OUT:
664 -- x_return_status OUT VARCHAR2
665 -- x_msg_count OUT NUMBER
666 -- x_msg_data OUT VARCHAR2
667 -- Version : Current version 1.0
668 -- Note: This automatic generated procedure definition, it
669 -- includes standard IN/OUT parameters and basic operation,
670 -- developer must manually add parameters and business
671 -- logic as necessary.
672 --
673 -- End of Comments
674 --
675 PROCEDURE Update_actions(
676 P_Api_Version_Number IN NUMBER,
677 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
678 P_Commit IN VARCHAR2 := FND_API.G_FALSE,
679 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
680 X_Return_Status OUT NOCOPY VARCHAR2,
681 X_Msg_Count OUT NOCOPY NUMBER,
682 X_Msg_Data OUT NOCOPY VARCHAR2,
683 P_action_Tbl IN action_Tbl_Type
684 )
685 IS
686 l_api_name CONSTANT VARCHAR2(30) := 'Update_actions';
687 l_api_version_number CONSTANT NUMBER := 1.0;
688 l_object_version_number NUMBER;
689 --
690 l_reason_type_id NUMBER;
691 l_task_template_group_id NUMBER;
692 l_active_flag VARCHAR2(1);
693 l_default_flag VARCHAR2(1);
694 P_action_Rec action_Rec_Type;
695 p_validation_mode VARCHAR2(1);
696 l_reason_type_count NUMBER;
697
698 CURSOR C2 IS SELECT OZF_REASONS_S.nextval FROM sys.dual;
699
700 CURSOR C_REASON_TYPE_COUNT(l_reason_type_id NUMBER)
701 IS SELECT COUNT(REASON_TYPE_ID)
702 FROM OZF_REASONS
703 WHERE REASON_TYPE_ID = l_reason_type_id;
704
705
706 CURSOR db_rec_csr (p_id number) IS
707 SELECT object_version_number
708 , task_template_group_id
709 , active_flag
710 , default_flag
711 FROM ozf_reasons
712 WHERE reason_type_id = p_id;
713
714 BEGIN
715
716 -- Standard Start of API savepoint
717 SAVEPOINT UPDATE_Actions_PVT;
718
719 -- Standard call to check for call compatibility.
720 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
721 p_api_version_number,
722 l_api_name,
723 G_PKG_NAME)
724 THEN
725 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
726 END IF;
727
728 -- Initialize message list if p_init_msg_list is set to TRUE.
729 IF FND_API.to_Boolean( p_init_msg_list )
730 THEN
731 FND_MSG_PUB.initialize;
732 END IF;
733
734 -- Debug Message
735 IF OZF_DEBUG_HIGH_ON THEN
736 OZF_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
737 END IF;
738
739
740 -- Initialize API return status to SUCCESS
741 x_return_status := FND_API.G_RET_STS_SUCCESS;
742
743 --
744 -- Api body
745 --
746 FOR i in p_action_tbl.first..p_action_tbl.last
747 LOOP
748 l_reason_type_id := p_action_tbl(i).reason_type_id;
749 l_default_flag := p_action_tbl(i).default_flag;
750 l_task_template_group_id := p_action_tbl(i).task_template_group_id;
751
752 IF (l_default_flag IS NULL) THEN
753 l_default_flag := FND_API.G_FALSE;
754 END IF;
755 -- CREATE mode
756 IF l_reason_type_id = -1
757 AND l_task_template_group_id <> -1
758 THEN
759 --Check for the uniqueness of action.
760 P_action_Rec := p_action_tbl(i);
761 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_item THEN
762 Check_unique_Action(
763 P_action_Rec => P_action_Rec,
764 p_validation_mode => JTF_PLSQL_API.g_create,
765 x_return_status => X_Return_Status
766 );
767 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
768 RAISE FND_API.G_EXC_ERROR;
769 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
770 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
771 END IF;
772 END IF;
773 --end of unique check.
774
775 l_object_version_number := 1;
776 l_active_flag := FND_API.G_TRUE;
777
778 --check for unique reason type id.
779 LOOP
780 -- get reason_type_id
781 OPEN C2;
782 FETCH C2 INTO l_reason_type_id;
783 CLOSE C2;
784
785 OPEN C_REASON_TYPE_COUNT(l_reason_type_id);
786 FETCH C_REASON_TYPE_COUNT INTO l_reason_type_count;
787 CLOSE C_REASON_TYPE_COUNT;
788 EXIT WHEN l_reason_type_count = 0;
789 END LOOP;
790
791 BEGIN
792 INSERT INTO ozf_reasons (
793 REASON_TYPE_ID,
794 OBJECT_VERSION_NUMBER,
795 LAST_UPDATE_DATE,
796 LAST_UPDATED_BY,
797 CREATION_DATE,
798 CREATED_BY,
799 LAST_UPDATE_LOGIN,
800 REASON_CODE_ID,
801 TASK_TEMPLATE_GROUP_ID,
802 ACTIVE_FLAG,
803 DEFAULT_FLAG
804 ) VALUES (
805 l_reason_type_id,
806 l_object_version_number,
807 SYSDATE,
808 FND_GLOBAL.USER_ID,
809 SYSDATE,
810 FND_GLOBAL.USER_ID,
811 FND_GLOBAL.CONC_LOGIN_ID,
812 p_action_tbl(i).reason_code_id,
813 l_task_template_group_id,
814 l_active_flag,
815 l_default_flag
816 );
817 EXCEPTION
818 WHEN OTHERS THEN
819 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
820 THEN
821 FND_MESSAGE.Set_Name('OZF', 'OZF_CLAIM_ACTION_INSERT_ERROR');
822 FND_MSG_PUB.Add;
823 END IF;
824 raise FND_API.G_EXC_ERROR;
825 END;
826 -- UPDATE mode
827 ELSE
828 -- fetch db record
829 OPEN db_rec_csr(l_reason_type_id);
830 FETCH db_rec_csr INTO l_object_version_number,
831 l_task_template_group_id,
832 l_active_flag,
833 l_default_flag;
834 CLOSE db_rec_csr;
835
836 -- Check Whether record has been changed by someone else
837 If (l_object_version_number <> p_action_tbl(i).object_version_number)
838 Then
839 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
840 THEN
841 FND_MESSAGE.Set_Name('OZF', 'OZF_API_RESOURCE_LOCKED');
842 FND_MSG_PUB.ADD;
843 END IF;
844 raise FND_API.G_EXC_ERROR;
845 END IF;
846
847 l_object_version_number := p_action_tbl(i).object_version_number + 1;
848
849 -- update if there are changes
850 IF l_task_template_group_id <> p_action_tbl(i).task_template_group_id
851 OR l_active_flag <> p_action_tbl(i).active_flag
852 OR l_default_flag <> p_action_tbl(i).default_flag
853 THEN
854 BEGIN
855 UPDATE ozf_reasons
856 SET OBJECT_VERSION_NUMBER = l_object_version_number
857 , LAST_UPDATE_DATE = SYSDATE
858 , LAST_UPDATED_BY = FND_GLOBAL.USER_ID
859 , LAST_UPDATE_LOGIN = FND_GLOBAL.CONC_LOGIN_ID
860 , REASON_CODE_ID = p_action_tbl(i).reason_code_id
861 , TASK_TEMPLATE_GROUP_ID= p_action_tbl(i).task_template_group_id
862 , ACTIVE_FLAG = p_action_tbl(i).active_flag
863 , default_flag = p_action_tbl(i).default_flag
864 WHERE reason_type_id = l_reason_type_id;
865 EXCEPTION
866 WHEN OTHERS THEN
867 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
868 THEN
869 FND_MESSAGE.Set_Name('OZF', 'OZF_CLAIM_ACTION_UPDATE_ERROR');
870 FND_MSG_PUB.Add;
871 END IF;
872 raise FND_API.G_EXC_ERROR;
873 END;
874 END IF; -- end update
875
876 END IF; -- end create and update modes
877 END LOOP;
878 --
879 -- End of API body
880 --
881
882 -- Standard check for p_commit
883 IF FND_API.to_Boolean( p_commit )
884 THEN
885 COMMIT WORK;
886 END IF;
887
888
889 -- Debug Message
890 IF OZF_DEBUG_HIGH_ON THEN
891 OZF_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'end');
892 END IF;
893
894 -- Standard call to get message count and if count is 1, get message info.
895 FND_MSG_PUB.Count_And_Get
896 (p_count => x_msg_count,
897 p_data => x_msg_data
898 );
899 EXCEPTION
900 WHEN FND_API.G_EXC_ERROR THEN
901 ROLLBACK TO UPDATE_Actions_PVT;
902 x_return_status := FND_API.G_RET_STS_ERROR;
903 -- Standard call to get message count and if count=1, get the message
904 FND_MSG_PUB.Count_And_Get (
905 p_encoded => FND_API.G_FALSE,
906 p_count => x_msg_count,
907 p_data => x_msg_data
908 );
909 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
910 ROLLBACK TO UPDATE_Actions_PVT;
911 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
912 -- Standard call to get message count and if count=1, get the message
913 FND_MSG_PUB.Count_And_Get (
914 p_encoded => FND_API.G_FALSE,
915 p_count => x_msg_count,
916 p_data => x_msg_data
917 );
918 WHEN OTHERS THEN
919 ROLLBACK TO UPDATE_Actions_PVT;
920 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
921 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
922 THEN
923 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
924 END IF;
925 -- Standard call to get message count and if count=1, get the message
926 FND_MSG_PUB.Count_And_Get (
927 p_encoded => FND_API.G_FALSE,
928 p_count => x_msg_count,
929 p_data => x_msg_data
930 );
931 End Update_Actions;
932
933
934 -- *******************************************************
935 -- Start of Comments
936 -- *******************************************************
937 -- API Name: Delete_Action
938 -- Type : Private
939 -- Pre-Req :
940 -- Parameters:
941 -- IN
942 -- p_api_version_number IN NUMBER Required
943 -- p_init_msg_list IN VARCHAR2 Optional Default=FND_API_G_FALSE
944 -- p_commit IN VARCHAR2 Optional Default=FND_API.G_FALSE
945 -- p_validation_level IN NUMBER Optional Default=FND_API.G_VALID_LEVEL_FULL
946 -- p_reason_type_id IN NUMBER
947 -- p_object_version_number IN NUMBER
948 --
949 -- OUT:
950 -- x_return_status OUT VARCHAR2
951 -- x_msg_count OUT NUMBER
952 -- x_msg_data OUT VARCHAR2
953 -- Version : Current version 1.0
954 -- Note: This automatic generated procedure definition, it
955 -- includes standard IN/OUT parameters and basic operation,
956 -- developer must manually add parameters and business
957 -- logic as necessary.
958 --
959 -- End of Comments
960 --
961 PROCEDURE Delete_action(
962 P_Api_Version_Number IN NUMBER,
963 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
964 P_Commit IN VARCHAR2 := FND_API.G_FALSE,
965 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
966 X_Return_Status OUT NOCOPY VARCHAR2,
967 X_Msg_Count OUT NOCOPY NUMBER,
968 X_Msg_Data OUT NOCOPY VARCHAR2,
969 P_reason_type_id IN NUMBER,
970 p_object_version_number IN NUMBER
971 )
972 IS
973 l_api_name CONSTANT VARCHAR2(30) := 'Delete_action';
974 l_api_version_number CONSTANT NUMBER := 1.0;
975 l_object_version_number NUMBER;
976
977 BEGIN
978 -- Standard Start of API savepoint
979 SAVEPOINT DELETE_Action_PVT;
980
981 -- Standard call to check for call compatibility.
982 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
983 p_api_version_number,
984 l_api_name,
985 G_PKG_NAME)
986 THEN
987 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
988 END IF;
989
990 -- Initialize message list if p_init_msg_list is set to TRUE.
991 IF FND_API.to_Boolean( p_init_msg_list )
992 THEN
993 FND_MSG_PUB.initialize;
994 END IF;
995
996 -- Debug Message
997 IF OZF_DEBUG_HIGH_ON THEN
998 OZF_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
999 END IF;
1000
1001
1002 -- Initialize API return status to SUCCESS
1003 x_return_status := FND_API.G_RET_STS_SUCCESS;
1004
1005 --
1006 -- Api body
1007 --
1008 -- Debug Message
1009 IF OZF_DEBUG_HIGH_ON THEN
1010 OZF_UTILITY_PVT.debug_message( 'Private API: Calling delete table handler');
1011 END IF;
1012
1013 -- Invoke table handler(OZF_reason_codes_All_PKG.Delete_Row)
1014 DELETE FROM ozf_reasons
1015 WHERE reason_type_id = p_reason_type_id
1016 AND object_version_number = p_object_version_number;
1017
1018 If (SQL%NOTFOUND) then
1019 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
1020 THEN
1021 FND_MESSAGE.Set_Name('OZF', 'OZF_API_RESOURCE_LOCKED');
1022 FND_MSG_PUB.ADD;
1023 END IF;
1024 raise FND_API.G_EXC_ERROR;
1025 End If;
1026 --
1027 -- End of API body
1028 --
1029
1030 -- Standard check for p_commit
1031 IF FND_API.to_Boolean( p_commit )
1032 THEN
1033 COMMIT WORK;
1034 END IF;
1035
1036
1037 -- Debug Message
1038 IF OZF_DEBUG_HIGH_ON THEN
1039 OZF_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'end');
1040 END IF;
1041
1042 -- Standard call to get message count and if count is 1, get message info.
1043 FND_MSG_PUB.Count_And_Get
1044 (p_count => x_msg_count,
1045 p_data => x_msg_data
1046 );
1047 EXCEPTION
1048 WHEN FND_API.G_EXC_ERROR THEN
1049 ROLLBACK TO DELETE_Action_PVT;
1050 x_return_status := FND_API.G_RET_STS_ERROR;
1051 -- Standard call to get message count and if count=1, get the message
1052 FND_MSG_PUB.Count_And_Get (
1053 p_encoded => FND_API.G_FALSE,
1054 p_count => x_msg_count,
1055 p_data => x_msg_data
1056 );
1057 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1058 ROLLBACK TO DELETE_Action_PVT;
1059 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1060 -- Standard call to get message count and if count=1, get the message
1061 FND_MSG_PUB.Count_And_Get (
1062 p_encoded => FND_API.G_FALSE,
1063 p_count => x_msg_count,
1064 p_data => x_msg_data
1065 );
1066 WHEN OTHERS THEN
1067 ROLLBACK TO DELETE_Action_PVT;
1068 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1069 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1070 THEN
1071 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1072 END IF;
1073 -- Standard call to get message count and if count=1, get the message
1074 FND_MSG_PUB.Count_And_Get (
1075 p_encoded => FND_API.G_FALSE,
1076 p_count => x_msg_count,
1077 p_data => x_msg_data
1078 );
1079 End Delete_action;
1080
1081 -- *******************************************************
1082 -- Start of Comments
1083 -- *******************************************************
1084 -- API Name: Check_unique_Action
1085 -- Type : Private
1086 -- Pre-Req :
1087 -- Parameters:
1088 -- IN
1089 -- P_action_Rec IN action_Rec_Type Required
1090 -- p_validation_mode IN VARCHAR2 Optional Default=JTF_PLSQL_API.g_create
1091 --
1092 -- OUT:
1093 -- x_return_status OUT VARCHAR2
1094 --
1095 -- Version : Current version 1.0
1096 -- Description : Checks the uniqueness of the action record for a reason.
1097 --
1098 -- End of Comments
1099 --
1100 PROCEDURE Check_unique_Action(
1101 P_action_Rec IN action_Rec_Type,
1102 p_validation_mode IN VARCHAR2 := JTF_PLSQL_API.g_create,
1103 x_return_status OUT NOCOPY VARCHAR2
1104 )
1105 IS
1106 l_valid_flag VARCHAR2(1);
1107 BEGIN
1108 x_return_status := FND_API.G_RET_STS_SUCCESS;
1109
1110 IF OZF_DEBUG_HIGH_ON THEN
1111 OZF_UTILITY_PVT.debug_message('Inside Check_unique_Action - p_validation_mode' || p_validation_mode);
1112
1113 OZF_UTILITY_PVT.debug_message('P_action_Rec.TASK_TEMPLATE_GROUP_ID :'|| P_action_Rec.TASK_TEMPLATE_GROUP_ID);
1114 END IF;
1115
1116 IF( p_validation_mode = JTF_PLSQL_API.g_create )
1117 THEN
1118 l_valid_flag := OZF_Utility_PVT.check_uniqueness(
1119 'OZF_REASONS',
1120 ' TASK_TEMPLATE_GROUP_ID ='||' '||P_action_Rec.TASK_TEMPLATE_GROUP_ID ||''||
1121 ' AND REASON_CODE_ID = '||' '|| P_action_Rec.REASON_CODE_ID
1122 );
1123 END IF;
1124
1125 IF l_valid_flag = FND_API.g_false THEN
1126 OZF_Utility_PVT.Error_Message(p_message_name => 'OZF_DUPLICATE_ACTION');
1127 x_return_status := FND_API.g_ret_sts_error;
1128 RETURN;
1129 END IF;
1130
1131 END Check_unique_Action;
1132 --End of Check_unique_Action
1133
1134 PROCEDURE Check_reason_Items (
1135 P_reason_Rec IN reason_Rec_Type,
1136 p_validation_mode IN VARCHAR2,
1137 x_return_status OUT NOCOPY VARCHAR2
1138 )
1139 IS
1140 BEGIN
1141
1142 --
1143 -- Check Items API calls
1144 NULL;
1145 --
1146
1147 END Check_reason_Items;
1148
1149 ---------------------------------------------------------------------
1150 -- PROCEDURE
1151 -- Complete_reason_Rec
1152 --
1153 -- PURPOSE
1154 -- For Update_reason, some attributes may be passed in as
1155 -- FND_API.g_miss_char/num/date if the user doesn't want to
1156 -- update those attributes. This procedure will replace the
1157 -- "g_miss" attributes with current database values.
1158 --
1159 -- PARAMETERS
1160 -- p_claim_rec : the record which may contain attributes as
1161 -- FND_API.g_miss_char/num/date
1162 -- x_complete_rec: the complete record after all "g_miss" items
1163 -- have been replaced by current database values
1164 ---------------------------------------------------------------------
1165
1166 PROCEDURE Complete_reason_Rec (
1167 P_reason_Rec IN reason_Rec_Type,
1168 x_complete_rec OUT NOCOPY reason_Rec_Type,
1169 x_return_status OUT NOCOPY varchar2
1170 )
1171 IS
1172
1173 CURSOR c_reason (cv_reason_id NUMBER) IS
1174 SELECT * FROM ozf_reason_codes_all_b
1175 WHERE reason_code_id = cv_reason_id;
1176
1177 l_reason_rec c_reason%ROWTYPE;
1178 BEGIN
1179
1180 -- Initialize API return status to sucess
1181 x_return_status := FND_API.G_RET_STS_SUCCESS;
1182
1183 x_complete_rec := P_reason_Rec;
1184
1185 OPEN c_reason(P_reason_Rec.reason_code_id);
1186 FETCH c_reason INTO l_reason_rec;
1187 IF c_reason%NOTFOUND THEN
1188 CLOSE c_reason;
1189 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1190 FND_MESSAGE.set_name('OZF','OZF_API_RECORD_NOT_FOUND');
1191 FND_MSG_PUB.add;
1192 END IF;
1193 RAISE FND_API.g_exc_error;
1194 END IF;
1195 CLOSE c_reason;
1196
1197 IF P_reason_Rec.reason_code_id = FND_API.G_MISS_NUM THEN
1198 x_complete_rec.reason_code_id := NULL;
1199 END IF;
1200 IF P_reason_Rec.reason_code_id IS NULL THEN
1201 x_complete_rec.reason_code_id := l_reason_rec.reason_code_id;
1202 END IF;
1203
1204 IF P_reason_Rec.last_update_login = FND_API.G_MISS_NUM THEN
1205 x_complete_rec.last_update_login := NULL;
1206 END IF;
1207 IF P_reason_Rec.last_update_login IS NULL THEN
1208 x_complete_rec.last_update_login := l_reason_rec.last_update_login;
1209 END IF;
1210
1211 IF P_reason_Rec.reason_type = FND_API.G_MISS_CHAR THEN
1212 x_complete_rec.reason_type := NULL;
1213 END IF;
1214 IF P_reason_Rec.reason_type IS NULL THEN
1215 x_complete_rec.reason_type := l_reason_rec.reason_type;
1216 END IF;
1217
1218
1219 IF P_reason_Rec.reason_code = FND_API.G_MISS_CHAR THEN
1220 x_complete_rec.reason_code := NULL;
1221 END IF;
1222 IF P_reason_Rec.reason_code IS NULL THEN
1223 x_complete_rec.reason_code := l_reason_rec.reason_code;
1224 END IF;
1225
1226 IF P_reason_Rec.start_date_active = FND_API.G_MISS_DATE THEN
1227 x_complete_rec.start_date_active := NULL;
1228 END IF;
1229 IF P_reason_Rec.start_date_active IS NULL THEN
1230 x_complete_rec.start_date_active := l_reason_rec.start_date_active;
1231 END IF;
1232
1233 IF P_reason_Rec.end_date_active = FND_API.G_MISS_DATE THEN
1234 x_complete_rec.end_date_active := NULL;
1235 END IF;
1236 IF P_reason_Rec.end_date_active IS NULL THEN
1237 x_complete_rec.end_date_active := l_reason_rec.end_date_active;
1238 END IF;
1239
1240 IF P_reason_Rec.attribute_category = FND_API.G_MISS_CHAR THEN
1241 x_complete_rec.attribute_category := NULL;
1242 END IF;
1243 IF P_reason_Rec.attribute_category IS NULL THEN
1244 x_complete_rec.attribute_category := l_reason_rec.attribute_category;
1245 END IF;
1246
1247 IF P_reason_Rec.attribute1 = FND_API.G_MISS_CHAR THEN
1248 x_complete_rec.attribute1 := NULL;
1249 END IF;
1250 IF P_reason_Rec.attribute1 IS NULL THEN
1251 x_complete_rec.attribute1 := l_reason_rec.attribute1;
1252 END IF;
1253
1254 IF P_reason_Rec.attribute2 = FND_API.G_MISS_CHAR THEN
1255 x_complete_rec.attribute2 := NULL;
1256 END IF;
1257 IF P_reason_Rec.attribute2 IS NULL THEN
1258 x_complete_rec.attribute2 := l_reason_rec.attribute2;
1259 END IF;
1260 IF P_reason_Rec.attribute3 = FND_API.G_MISS_CHAR THEN
1261 x_complete_rec.attribute3 := NULL;
1262 END IF;
1263 IF P_reason_Rec.attribute3 IS NULL THEN
1264 x_complete_rec.attribute3 := l_reason_rec.attribute3;
1265 END IF;
1266 IF P_reason_Rec.attribute4 = FND_API.G_MISS_CHAR THEN
1267 x_complete_rec.attribute4 := NULL;
1268 END IF;
1269 IF P_reason_Rec.attribute4 IS NULL THEN
1270 x_complete_rec.attribute4 := l_reason_rec.attribute4;
1271 END IF;
1272 IF P_reason_Rec.attribute5 = FND_API.G_MISS_CHAR THEN
1273 x_complete_rec.attribute5 := NULL;
1274 END IF;
1275 IF P_reason_Rec.attribute5 IS NULL THEN
1276 x_complete_rec.attribute5 := l_reason_rec.attribute5;
1277 END IF;
1278 IF P_reason_Rec.attribute6 = FND_API.G_MISS_CHAR THEN
1279 x_complete_rec.attribute6 := NULL;
1280 END IF;
1281 IF P_reason_Rec.attribute6 IS NULL THEN
1282 x_complete_rec.attribute6 := l_reason_rec.attribute6;
1283 END IF;
1284 IF P_reason_Rec.attribute7 = FND_API.G_MISS_CHAR THEN
1285 x_complete_rec.attribute7 := NULL;
1286 END IF;
1287 IF P_reason_Rec.attribute7 IS NULL THEN
1288 x_complete_rec.attribute7 := l_reason_rec.attribute7;
1289 END IF;
1290 IF P_reason_Rec.attribute8 = FND_API.G_MISS_CHAR THEN
1291 x_complete_rec.attribute8 := NULL;
1292 END IF;
1293 IF P_reason_Rec.attribute8 IS NULL THEN
1294 x_complete_rec.attribute8 := l_reason_rec.attribute8;
1295 END IF;
1296 IF P_reason_Rec.attribute9 = FND_API.G_MISS_CHAR THEN
1297 x_complete_rec.attribute9 := NULL;
1298 END IF;
1299 IF P_reason_Rec.attribute9 IS NULL THEN
1300 x_complete_rec.attribute9 := l_reason_rec.attribute9;
1301 END IF;
1302 IF P_reason_Rec.attribute10 = FND_API.G_MISS_CHAR THEN
1303 x_complete_rec.attribute10 := NULL;
1304 END IF;
1305 IF P_reason_Rec.attribute10 IS NULL THEN
1306 x_complete_rec.attribute10 := l_reason_rec.attribute10;
1307 END IF;
1308 IF P_reason_Rec.attribute11 = FND_API.G_MISS_CHAR THEN
1309 x_complete_rec.attribute11 := NULL;
1310 END IF;
1311 IF P_reason_Rec.attribute11 IS NULL THEN
1312 x_complete_rec.attribute11 := l_reason_rec.attribute11;
1313 END IF;
1314 IF P_reason_Rec.attribute12 = FND_API.G_MISS_CHAR THEN
1315 x_complete_rec.attribute12 := NULL;
1316 END IF;
1317 IF P_reason_Rec.attribute12 IS NULL THEN
1318 x_complete_rec.attribute12 := l_reason_rec.attribute12;
1319 END IF;
1320 IF P_reason_Rec.attribute13 = FND_API.G_MISS_CHAR THEN
1321 x_complete_rec.attribute13 := NULL;
1322 END IF;
1323 IF P_reason_Rec.attribute13 IS NULL THEN
1324 x_complete_rec.attribute13 := l_reason_rec.attribute13;
1325 END IF;
1326 IF P_reason_Rec.attribute14 = FND_API.G_MISS_CHAR THEN
1327 x_complete_rec.attribute14 := NULL;
1328 END IF;
1329 IF P_reason_Rec.attribute14 IS NULL THEN
1330 x_complete_rec.attribute14 := l_reason_rec.attribute14;
1331 END IF;
1332 IF P_reason_Rec.attribute15 = FND_API.G_MISS_CHAR THEN
1333 x_complete_rec.attribute15 := NULL;
1334 END IF;
1335 IF P_reason_Rec.attribute15 IS NULL THEN
1336 x_complete_rec.attribute15 := l_reason_rec.attribute15;
1337 END IF;
1338
1339 IF P_reason_Rec.org_id = FND_API.G_MISS_NUM THEN
1340 x_complete_rec.org_id := NULL;
1341 END IF;
1342 IF P_reason_Rec.org_id IS NULL THEN
1343 x_complete_rec.org_id := l_reason_rec.org_id;
1344 END IF;
1345
1346 IF P_reason_Rec.adjustment_reason_code = FND_API.G_MISS_CHAR THEN
1347 x_complete_rec.adjustment_reason_code := NULL;
1348 END IF;
1349 IF P_reason_Rec.adjustment_reason_code IS NULL THEN
1350 x_complete_rec.adjustment_reason_code := l_reason_rec.adjustment_reason_code;
1351 END IF;
1352
1353 IF P_reason_Rec.invoicing_reason_code = FND_API.G_MISS_CHAR THEN
1354 x_complete_rec.invoicing_reason_code := NULL;
1355 END IF;
1356 IF P_reason_Rec.invoicing_reason_code IS NULL THEN
1357 x_complete_rec.invoicing_reason_code := l_reason_rec.invoicing_reason_code;
1358 END IF;
1359
1360
1361 IF P_reason_Rec.order_type_id = FND_API.G_MISS_NUM THEN
1362 x_complete_rec.order_type_id := NULL;
1363 END IF;
1364 IF P_reason_Rec.order_type_id IS NULL THEN
1365 x_complete_rec.order_type_id := l_reason_rec.order_type_id;
1366 END IF;
1367
1368 IF P_reason_Rec.partner_access_flag = FND_API.G_MISS_CHAR THEN
1369 x_complete_rec.partner_access_flag := NULL;
1370 END IF;
1371 IF P_reason_Rec.partner_access_flag IS NULL THEN
1372 x_complete_rec.partner_access_flag := l_reason_rec.partner_access_flag;
1373 END IF;
1374
1375
1376 EXCEPTION
1377 WHEN FND_API.G_EXC_ERROR THEN
1378 x_return_status := FND_API.G_RET_STS_ERROR;
1379 WHEN OTHERS THEN
1380 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
1381 FND_MESSAGE.set_name('OZF', 'OZF_REASON_COMPLETE_ERROR');
1382 FND_MSG_PUB.add;
1383 END IF;
1384 x_return_status := FND_API.g_ret_sts_unexp_error;
1385
1386
1387 END Complete_reason_Rec;
1388
1389 PROCEDURE Validate_reason(
1390 P_Api_Version_Number IN NUMBER,
1391 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
1392 P_Validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1393 P_reason_Rec IN reason_Rec_Type,
1394 X_Return_Status OUT NOCOPY VARCHAR2,
1395 X_Msg_Count OUT NOCOPY NUMBER,
1396 X_Msg_Data OUT NOCOPY VARCHAR2
1397 )
1398 IS
1399 l_api_name CONSTANT VARCHAR2(30) := 'Validate_reason';
1400 l_api_version_number CONSTANT NUMBER := 1.0;
1401 l_object_version_number NUMBER;
1402 l_reason_rec OZF_reason_PVT.reason_Rec_Type;
1403
1404 BEGIN
1405 -- Standard Start of API savepoint
1406 SAVEPOINT VALIDATE_Reason_;
1407
1408 -- Standard call to check for call compatibility.
1409 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
1410 p_api_version_number,
1411 l_api_name,
1412 G_PKG_NAME)
1413 THEN
1414 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1415 END IF;
1416
1417 -- Initialize message list if p_init_msg_list is set to TRUE.
1418 IF FND_API.to_Boolean( p_init_msg_list )
1419 THEN
1420 FND_MSG_PUB.initialize;
1421 END IF;
1422 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_item THEN
1423 Check_reason_Items(
1424 p_reason_rec => p_reason_rec,
1425 p_validation_mode => JTF_PLSQL_API.g_update,
1426 x_return_status => x_return_status
1427 );
1428
1429 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1430 RAISE FND_API.G_EXC_ERROR;
1431 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1432 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1433 END IF;
1434 END IF;
1435 l_reason_Rec := P_reason_Rec;
1436
1437
1438 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_item THEN
1439 Validate_reason_Rec(
1440 p_api_version_number => 1.0,
1441 p_init_msg_list => FND_API.G_FALSE,
1442 x_return_status => x_return_status,
1443 x_msg_count => x_msg_count,
1444 x_msg_data => x_msg_data,
1445 P_reason_Rec => l_reason_Rec);
1446
1447 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1448 RAISE FND_API.G_EXC_ERROR;
1449 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1450 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1451 END IF;
1452 END IF;
1453
1454
1455 -- Debug Message
1456 IF OZF_DEBUG_HIGH_ON THEN
1457 OZF_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
1458 END IF;
1459
1460
1461 -- Initialize API return status to SUCCESS
1462 x_return_status := FND_API.G_RET_STS_SUCCESS;
1463
1464
1465 -- Debug Message
1466 IF OZF_DEBUG_HIGH_ON THEN
1467 OZF_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'end');
1468 END IF;
1469
1470 -- Standard call to get message count and if count is 1, get message info.
1471 FND_MSG_PUB.Count_And_Get
1472 (p_count => x_msg_count,
1473 p_data => x_msg_data
1474 );
1475 EXCEPTION
1476 WHEN FND_API.G_EXC_ERROR THEN
1477 ROLLBACK TO VALIDATE_Reason_;
1478 x_return_status := FND_API.G_RET_STS_ERROR;
1479 -- Standard call to get message count and if count=1, get the message
1480 FND_MSG_PUB.Count_And_Get (
1481 p_encoded => FND_API.G_FALSE,
1482 p_count => x_msg_count,
1483 p_data => x_msg_data
1484 );
1485 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1486 ROLLBACK TO VALIDATE_Reason_;
1487 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1488 -- Standard call to get message count and if count=1, get the message
1489 FND_MSG_PUB.Count_And_Get (
1490 p_encoded => FND_API.G_FALSE,
1491 p_count => x_msg_count,
1492 p_data => x_msg_data
1493 );
1494 WHEN OTHERS THEN
1495 ROLLBACK TO VALIDATE_Reason_;
1496 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1497 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1498 THEN
1499 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1500 END IF;
1501 -- Standard call to get message count and if count=1, get the message
1502 FND_MSG_PUB.Count_And_Get (
1503 p_encoded => FND_API.G_FALSE,
1504 p_count => x_msg_count,
1505 p_data => x_msg_data
1506 );
1507 End Validate_reason;
1508
1509
1510 PROCEDURE Validate_reason_rec(
1511 P_Api_Version_Number IN NUMBER,
1512 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
1513 X_Return_Status OUT NOCOPY VARCHAR2,
1514 X_Msg_Count OUT NOCOPY NUMBER,
1515 X_Msg_Data OUT NOCOPY VARCHAR2,
1516 P_reason_Rec IN reason_Rec_Type
1517 )
1518 IS
1519 l_reason_name VARCHAR2(80);
1520 l_start_date DATE;
1521 l_end_date DATE;
1522 l_dummy NUMBER;
1523
1524 CURSOR c_order_trx_type(cv_id NUMBER)
1525 IS
1526 SELECT 1
1527 FROM oe_transaction_types_vl
1528 WHERE transaction_type_id = cv_id;
1529
1530 BEGIN
1531 -- Initialize message list if p_init_msg_list is set to TRUE.
1532 IF FND_API.to_Boolean( p_init_msg_list )
1533 THEN
1534 FND_MSG_PUB.initialize;
1535 END IF;
1536
1537 -- Initialize API return status to SUCCESS
1538 x_return_status := FND_API.G_RET_STS_SUCCESS;
1539
1540 -- Hint: Validate data
1541 -- If data not valid
1542 -- THEN
1543 -- x_return_status := FND_API.G_RET_STS_ERROR;
1544
1545 l_reason_name := P_reason_Rec.NAME;
1546 l_start_date := P_reason_Rec.START_DATE_ACTIVE;
1547 l_end_date := P_reason_Rec.END_DATE_ACTIVE;
1548
1549 -- Check for null reason name.
1550 IF( (l_reason_name IS NULL)
1551 OR (l_reason_name = FND_API.G_MISS_CHAR) )
1552 THEN
1553 FND_MESSAGE.Set_Name('OZF', 'OZF_REASON_NULL_NAME');
1554 FND_MSG_PUB.ADD;
1555 x_return_status := FND_API.G_RET_STS_ERROR;
1556 END IF;
1557
1558 -- Check for null start date
1559 IF( (l_start_date IS NULL)
1560 OR (l_start_date = FND_API.G_MISS_DATE) )
1561 THEN
1562 FND_MESSAGE.Set_Name('OZF', 'OZF_REASON_NULL_STDATE');
1563 FND_MSG_PUB.ADD;
1564 x_return_status := FND_API.G_RET_STS_ERROR;
1565 END IF;
1566
1567 -- End date validation.
1568 IF( (l_end_date IS NOT NULL)
1569 AND (l_end_date <> FND_API.G_MISS_DATE) )
1570 THEN
1571 IF( l_start_date > l_end_date )
1572 THEN
1573 FND_MESSAGE.Set_Name('OZF', 'OZF_REASON_STDATE_GT_ENDDATE');
1574 FND_MSG_PUB.ADD;
1575 x_return_status := FND_API.G_RET_STS_ERROR;
1576 END IF;
1577 END IF;
1578
1579 --Check the validity of OM Transaction type
1580 IF P_reason_Rec.order_type_id IS NOT NULL THEN
1581 OPEN c_order_trx_type(P_reason_Rec.order_type_id);
1582 FETCH c_order_trx_type INTO l_dummy;
1583 CLOSE c_order_trx_type;
1584
1585 IF l_dummy <> 1 THEN
1586 FND_MESSAGE.set_name('OZF', 'OZF_CLAIM_INVALID_OM_TRX_TYPE');
1587 FND_MSG_PUB.add;
1588 x_return_status := FND_API.G_RET_STS_ERROR;
1589 RETURN;
1590 END IF;
1591 END IF;
1592
1593 -- Debug Message
1594 IF OZF_DEBUG_HIGH_ON THEN
1595 OZF_UTILITY_PVT.debug_message('API_INVALID_RECORD');
1596 END IF;
1597 -- Standard call to get message count and if count is 1, get message info.
1598 FND_MSG_PUB.Count_And_Get
1599 (p_count => x_msg_count,
1600 p_data => x_msg_data
1601 );
1602 END Validate_reason_Rec;
1603
1604 End OZF_Reason_PVT;