[Home] [Help]
PACKAGE BODY: APPS.PV_ENTYROUT_PVT
Source
1 PACKAGE BODY PV_ENTYROUT_PVT as
2 /* $Header: pvrvertb.pls 115.4 2002/11/25 22:03:55 ryellapu ship $ */
3 -- Start of Comments
4 -- Package name : PV_ENTYROUT_PVT
5 -- Purpose :
6 -- History :
7 -- NOTE :
8 -- End of Comments
9
10
11 G_PKG_NAME CONSTANT VARCHAR2(30):= 'PV_ENTYROUT_PVT';
12 G_FILE_NAME CONSTANT VARCHAR2(12) := 'pvrvertb.pls';
13
14
15 -- Hint: Primary key needs to be returned.
16 AS_DEBUG_HIGH_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH);
17 AS_DEBUG_LOW_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
18 AS_DEBUG_MEDIUM_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_MEDIUM);
19
20 PROCEDURE Create_entyrout(
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 P_Identity_Resource_Id IN NUMBER,
26 P_ENTYROUT_Rec IN PV_RULE_RECTYPE_PUB.ENTYROUT_Rec_Type
27 := PV_RULE_RECTYPE_PUB.G_MISS_ENTYROUT_REC,
28 X_ENTITY_ROUTING_ID OUT NOCOPY NUMBER,
29 X_Return_Status OUT NOCOPY VARCHAR2,
30 X_Msg_Count OUT NOCOPY NUMBER,
31 X_Msg_Data OUT NOCOPY VARCHAR2
32 )
33 IS
34 l_api_name CONSTANT VARCHAR2(30) := 'Create_entyrout';
35 l_api_version_number CONSTANT NUMBER := 2.0;
36 l_return_status_full VARCHAR2(1);
37 l_access_flag VARCHAR2(1);
38 BEGIN
39 -- Standard Start of API savepoint
40 SAVEPOINT CREATE_ENTYROUT_PVT;
41
42 -- Standard call to check for call compatibility.
43 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
44 p_api_version_number,
45 l_api_name,
46 G_PKG_NAME)
47 THEN
48 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
49 END IF;
50
51
52 -- Initialize message list if p_init_msg_list is set to TRUE.
53 IF FND_API.to_Boolean( p_init_msg_list )
54 THEN
55 FND_MSG_PUB.initialize;
56 END IF;
57
58
59 -- Debug Message
60 IF (AS_DEBUG_HIGH_ON) THEN
61
62 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'Private API: ' || l_api_name || 'start');
63 END IF;
64
65
66 -- Initialize API return status to SUCCESS
67 x_return_status := FND_API.G_RET_STS_SUCCESS;
68
69 --
70 -- API body
71 --
72 -- ******************************************************************
73 -- Validate Environment
74 -- ******************************************************************
75 IF FND_GLOBAL.User_Id IS NULL
76 THEN
77 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
78 THEN
79 FND_MESSAGE.Set_Name('PV', 'UT_CANNOT_GET_PROFILE_VALUE');
80 FND_MESSAGE.Set_Token('PROFILE', 'USER_ID', FALSE);
81 FND_MSG_PUB.ADD;
82 END IF;
83 RAISE FND_API.G_EXC_ERROR;
84 END IF;
85
86 -- Debug message
87 IF (AS_DEBUG_HIGH_ON) THEN
88
89 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,'Private API: Validate_entyrout');
90 END IF;
91
92 -- Invoke validation procedures
93 Validate_entyrout(
94 p_init_msg_list => FND_API.G_FALSE,
95 p_validation_level => p_validation_level,
96 p_validation_mode => AS_UTILITY_PVT.G_CREATE,
97 P_ENTYROUT_Rec => P_ENTYROUT_Rec,
98 x_return_status => x_return_status,
99 x_msg_count => x_msg_count,
100 x_msg_data => x_msg_data);
101
102 IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
103 RAISE FND_API.G_EXC_ERROR;
104 END IF;
105
106 -- Hint: Add corresponding Master-Detail business logic here if necessary.
107
108 -- Debug Message
109 IF (AS_DEBUG_HIGH_ON) THEN
110
111 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'Private API: Calling create table handler');
112 END IF;
113
114 -- Invoke table handler(PV_ENTITY_ROUTINGS_PKG.Insert_Row)
115 PV_ENTITY_ROUTINGS_PKG.Insert_Row(
116 px_ENTITY_ROUTING_ID => x_ENTITY_ROUTING_ID
117 ,p_LAST_UPDATE_DATE => SYSDATE
118 ,p_LAST_UPDATED_BY => FND_GLOBAL.USER_ID
119 ,p_CREATION_DATE => SYSDATE
120 ,p_CREATED_BY => FND_GLOBAL.USER_ID
121 ,p_LAST_UPDATE_LOGIN => FND_GLOBAL.CONC_LOGIN_ID
122 ,p_OBJECT_VERSION_NUMBER => p_ENTYROUT_rec.OBJECT_VERSION_NUMBER
123 ,p_REQUEST_ID => p_ENTYROUT_rec.REQUEST_ID
124 ,p_PROGRAM_APPLICATION_ID => p_ENTYROUT_rec.PROGRAM_APPLICATION_ID
125 ,p_PROGRAM_ID => p_ENTYROUT_rec.PROGRAM_ID
126 ,p_PROGRAM_UPDATE_DATE => p_ENTYROUT_rec.PROGRAM_UPDATE_DATE
127 ,p_PROCESS_RULE_ID => p_ENTYROUT_rec.PROCESS_RULE_ID
128 ,p_DISTANCE_FROM_CUSTOMER => p_ENTYROUT_rec.DISTANCE_FROM_CUSTOMER
129 ,p_DISTANCE_UOM_CODE => p_ENTYROUT_rec.DISTANCE_UOM_CODE
130 ,p_MAX_NEAREST_PARTNER => p_ENTYROUT_rec.MAX_NEAREST_PARTNER
131 ,p_ROUTING_TYPE => p_ENTYROUT_rec.ROUTING_TYPE
132 ,p_BYPASS_CM_OK_FLAG => p_ENTYROUT_rec.BYPASS_CM_OK_FLAG
133 ,p_CM_TIMEOUT => p_ENTYROUT_rec.CM_TIMEOUT
134 ,p_CM_TIMEOUT_UOM_CODE => p_ENTYROUT_rec.CM_TIMEOUT_UOM_CODE
135 ,p_PARTNER_TIMEOUT => p_ENTYROUT_rec.PARTNER_TIMEOUT
136 ,p_PARTNER_TIMEOUT_UOM_CODE => p_ENTYROUT_rec.PARTNER_TIMEOUT_UOM_CODE
137 ,p_UNMATCHED_INT_RESOURCE_ID => p_ENTYROUT_rec.UNMATCHED_INT_RESOURCE_ID
138 ,p_UNMATCHED_CALL_TAP_FLAG => p_ENTYROUT_rec.UNMATCHED_CALL_TAP_FLAG
139 ,p_ATTRIBUTE_CATEGORY => p_ENTYROUT_rec.ATTRIBUTE_CATEGORY
140 ,p_ATTRIBUTE1 => p_ENTYROUT_rec.ATTRIBUTE1
141 ,p_ATTRIBUTE2 => p_ENTYROUT_rec.ATTRIBUTE2
142 ,p_ATTRIBUTE3 => p_ENTYROUT_rec.ATTRIBUTE3
143 ,p_ATTRIBUTE4 => p_ENTYROUT_rec.ATTRIBUTE4
144 ,p_ATTRIBUTE5 => p_ENTYROUT_rec.ATTRIBUTE5
145 ,p_ATTRIBUTE6 => p_ENTYROUT_rec.ATTRIBUTE6
146 ,p_ATTRIBUTE7 => p_ENTYROUT_rec.ATTRIBUTE7
147 ,p_ATTRIBUTE8 => p_ENTYROUT_rec.ATTRIBUTE8
148 ,p_ATTRIBUTE9 => p_ENTYROUT_rec.ATTRIBUTE9
149 ,p_ATTRIBUTE10 => p_ENTYROUT_rec.ATTRIBUTE10
150 ,p_ATTRIBUTE11 => p_ENTYROUT_rec.ATTRIBUTE11
151 ,p_ATTRIBUTE12 => p_ENTYROUT_rec.ATTRIBUTE12
152 ,p_ATTRIBUTE13 => p_ENTYROUT_rec.ATTRIBUTE13
153 ,p_ATTRIBUTE14 => p_ENTYROUT_rec.ATTRIBUTE14
154 ,p_ATTRIBUTE15 => p_ENTYROUT_rec.ATTRIBUTE15
155 ); -- Hint: Primary key should be returned.
156 -- x_ENTITY_ROUTING_ID := px_ENTITY_ROUTING_ID;
157
158 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
159 RAISE FND_API.G_EXC_ERROR;
160 END IF;
161
162 --
163 -- End of API body
164 --
165
166 -- Standard check for p_commit
167 IF FND_API.to_Boolean( p_commit )
168 THEN
169 COMMIT WORK;
170 END IF;
171
172
173 -- Debug Message
174 IF (AS_DEBUG_HIGH_ON) THEN
175
176 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'Private API: ' || l_api_name || 'end');
177 END IF;
178
179
180 -- Standard call to get message count and if count is 1, get message info.
181 FND_MSG_PUB.Count_And_Get
182 ( p_count => x_msg_count,
183 p_data => x_msg_data
184 );
185
186 EXCEPTION
187 WHEN FND_API.G_EXC_ERROR THEN
188 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
189 P_API_NAME => L_API_NAME
190 ,P_PKG_NAME => G_PKG_NAME
191 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
192 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
193 ,X_MSG_COUNT => X_MSG_COUNT
194 ,X_MSG_DATA => X_MSG_DATA
195 ,X_RETURN_STATUS => X_RETURN_STATUS);
196
197 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
198 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
199 P_API_NAME => L_API_NAME
200 ,P_PKG_NAME => G_PKG_NAME
201 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
202 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
203 ,X_MSG_COUNT => X_MSG_COUNT
204 ,X_MSG_DATA => X_MSG_DATA
205 ,X_RETURN_STATUS => X_RETURN_STATUS);
206
207 WHEN OTHERS THEN
208 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
209 P_API_NAME => L_API_NAME
210 ,P_PKG_NAME => G_PKG_NAME
211 ,P_EXCEPTION_LEVEL => AS_UTILITY_PVT.G_EXC_OTHERS
212 ,P_SQLCODE => SQLCODE
213 ,P_SQLERRM => SQLERRM
214 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
215 ,X_MSG_COUNT => X_MSG_COUNT
216 ,X_MSG_DATA => X_MSG_DATA
217 ,X_RETURN_STATUS => X_RETURN_STATUS);
218 End Create_entyrout;
219
220
221 -- Hint: Add corresponding update detail table procedures if it's master-detail relationship.
222 PROCEDURE Update_entyrout(
223 P_Api_Version_Number IN NUMBER,
224 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
225 P_Commit IN VARCHAR2 := FND_API.G_FALSE,
226 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
227 P_Identity_Resource_Id IN NUMBER,
228 P_ENTYROUT_Rec IN PV_RULE_RECTYPE_PUB.ENTYROUT_Rec_Type,
229 X_Return_Status OUT NOCOPY VARCHAR2,
230 X_Msg_Count OUT NOCOPY NUMBER,
231 X_Msg_Data OUT NOCOPY VARCHAR2
232 )
233
234 IS
235
236 Cursor C_Get_entyrout(pc_ENTITY_ROUTING_ID Number) IS
237 Select
238 object_version_number
239 From PV_ENTITY_ROUTINGS
240 where entity_routing_id = pc_entity_routing_id
241 For Update NOWAIT;
242
243 l_api_name CONSTANT VARCHAR2(30) := 'Update_entyrout';
244 l_api_version_number CONSTANT NUMBER := 2.0;
245 -- Local Variables
246 l_ref_ENTYROUT_rec PV_RULE_RECTYPE_PUB.ENTYROUT_Rec_Type;
247 l_tar_ENTYROUT_rec PV_RULE_RECTYPE_PUB.ENTYROUT_Rec_Type := P_ENTYROUT_Rec;
248 l_rowid ROWID;
249 BEGIN
250 -- Standard Start of API savepoint
251 SAVEPOINT UPDATE_ENTYROUT_PVT;
252
253 -- Standard call to check for call compatibility.
254 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
255 p_api_version_number,
256 l_api_name,
257 G_PKG_NAME)
258 THEN
259 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
260 END IF;
261
262
263 -- Initialize message list if p_init_msg_list is set to TRUE.
264 IF FND_API.to_Boolean( p_init_msg_list )
265 THEN
266 FND_MSG_PUB.initialize;
267 END IF;
268
269
270 -- Debug Message
271 IF (AS_DEBUG_HIGH_ON) THEN
272
273 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'Private API: ' || l_api_name || 'start');
274 END IF;
275
276
277 -- Initialize API return status to SUCCESS
278 x_return_status := FND_API.G_RET_STS_SUCCESS;
279
280 --
281 -- Api body
282 --
283
284 -- Debug Message
285 IF (AS_DEBUG_HIGH_ON) THEN
286
287 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,'Private API: - Open Cursor to Select');
288 END IF;
289
290 Open C_Get_entyrout( l_tar_ENTYROUT_rec.ENTITY_ROUTING_ID);
291
292 Fetch C_Get_entyrout into
293 l_ref_ENTYROUT_rec.object_version_number;
294
295 If ( C_Get_entyrout%NOTFOUND) Then
296 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
297 THEN
298 FND_MESSAGE.Set_Name('PV', 'API_MISSING_UPDATE_TARGET');
299 FND_MESSAGE.Set_Token ('INFO', 'entyrout', FALSE);
300 FND_MSG_PUB.Add;
301 END IF;
302 Close C_Get_entyrout;
303 raise FND_API.G_EXC_ERROR;
304 END IF;
305 -- Debug Message
306 IF (AS_DEBUG_HIGH_ON) THEN
307
308 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,'Private API: - Close Cursor');
309 END IF;
310 Close C_Get_entyrout;
311
312
313 If (l_tar_ENTYROUT_rec.object_version_number is NULL or
314 l_tar_ENTYROUT_rec.object_version_number = FND_API.G_MISS_NUM ) Then
315 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
316 THEN
317 FND_MESSAGE.Set_Name('PV', 'API_MISSING_ID');
318 FND_MESSAGE.Set_Token('COLUMN', 'object_version_number', FALSE);
319 FND_MSG_PUB.ADD;
320 END IF;
321 raise FND_API.G_EXC_ERROR;
322 End if;
323 -- Check Whether record has been changed by someone else
324 If (l_tar_ENTYROUT_rec.object_version_number <> l_ref_ENTYROUT_rec.object_version_number) Then
325 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
326 THEN
327 FND_MESSAGE.Set_Name('PV', 'API_RECORD_CHANGED');
328 FND_MESSAGE.Set_Token('INFO', 'entyrout', FALSE);
329 FND_MSG_PUB.ADD;
330 END IF;
331 raise FND_API.G_EXC_ERROR;
332 End if;
333
334 -- Debug message
335 IF (AS_DEBUG_HIGH_ON) THEN
336
337 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,'Private API: Validate_entyrout');
338 END IF;
339
340 -- Invoke validation procedures
341 Validate_entyrout(
342 p_init_msg_list => FND_API.G_FALSE,
343 p_validation_level => p_validation_level,
344 p_validation_mode => AS_UTILITY_PVT.G_UPDATE,
345 P_ENTYROUT_Rec => P_ENTYROUT_Rec,
346 x_return_status => x_return_status,
347 x_msg_count => x_msg_count,
348 x_msg_data => x_msg_data);
349
350 IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
351 RAISE FND_API.G_EXC_ERROR;
352 END IF;
353
354 -- Hint: Add corresponding Master-Detail business logic here if necessary.
355
356 -- Debug Message
357 IF (AS_DEBUG_HIGH_ON) THEN
358
359 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'Private API: Calling update table handler');
360 END IF;
361
362 -- Invoke table handler(PV_ENTITY_ROUTINGS_PKG.Update_Row)
363 PV_ENTITY_ROUTINGS_PKG.Update_Row(
364 p_ENTITY_ROUTING_ID => p_ENTYROUT_rec.ENTITY_ROUTING_ID
365 ,p_LAST_UPDATE_DATE => SYSDATE
366 ,p_LAST_UPDATED_BY => FND_GLOBAL.USER_ID
367 ,p_CREATION_DATE => FND_API.G_MISS_DATE
368 ,p_CREATED_BY => FND_API.G_MISS_NUM
369 ,p_LAST_UPDATE_LOGIN => FND_GLOBAL.CONC_LOGIN_ID
370 ,p_OBJECT_VERSION_NUMBER => p_ENTYROUT_rec.OBJECT_VERSION_NUMBER
371 ,p_REQUEST_ID => p_ENTYROUT_rec.REQUEST_ID
372 ,p_PROGRAM_APPLICATION_ID => p_ENTYROUT_rec.PROGRAM_APPLICATION_ID
373 ,p_PROGRAM_ID => p_ENTYROUT_rec.PROGRAM_ID
374 ,p_PROGRAM_UPDATE_DATE => p_ENTYROUT_rec.PROGRAM_UPDATE_DATE
375 ,p_PROCESS_RULE_ID => p_ENTYROUT_rec.PROCESS_RULE_ID
376 ,p_DISTANCE_FROM_CUSTOMER => p_ENTYROUT_rec.DISTANCE_FROM_CUSTOMER
377 ,p_DISTANCE_UOM_CODE => p_ENTYROUT_rec.DISTANCE_UOM_CODE
378 ,p_MAX_NEAREST_PARTNER => p_ENTYROUT_rec.MAX_NEAREST_PARTNER
379 ,p_ROUTING_TYPE => p_ENTYROUT_rec.ROUTING_TYPE
380 ,p_BYPASS_CM_OK_FLAG => p_ENTYROUT_rec.BYPASS_CM_OK_FLAG
381 ,p_CM_TIMEOUT => p_ENTYROUT_rec.CM_TIMEOUT
382 ,p_CM_TIMEOUT_UOM_CODE => p_ENTYROUT_rec.CM_TIMEOUT_UOM_CODE
383 ,p_PARTNER_TIMEOUT => p_ENTYROUT_rec.PARTNER_TIMEOUT
384 ,p_PARTNER_TIMEOUT_UOM_CODE => p_ENTYROUT_rec.PARTNER_TIMEOUT_UOM_CODE
385 ,p_UNMATCHED_INT_RESOURCE_ID => p_ENTYROUT_rec.UNMATCHED_INT_RESOURCE_ID
386 ,p_UNMATCHED_CALL_TAP_FLAG => p_ENTYROUT_rec.UNMATCHED_CALL_TAP_FLAG
387 ,p_ATTRIBUTE_CATEGORY => p_ENTYROUT_rec.ATTRIBUTE_CATEGORY
388 ,p_ATTRIBUTE1 => p_ENTYROUT_rec.ATTRIBUTE1
389 ,p_ATTRIBUTE2 => p_ENTYROUT_rec.ATTRIBUTE2
390 ,p_ATTRIBUTE3 => p_ENTYROUT_rec.ATTRIBUTE3
391 ,p_ATTRIBUTE4 => p_ENTYROUT_rec.ATTRIBUTE4
392 ,p_ATTRIBUTE5 => p_ENTYROUT_rec.ATTRIBUTE5
393 ,p_ATTRIBUTE6 => p_ENTYROUT_rec.ATTRIBUTE6
394 ,p_ATTRIBUTE7 => p_ENTYROUT_rec.ATTRIBUTE7
395 ,p_ATTRIBUTE8 => p_ENTYROUT_rec.ATTRIBUTE8
396 ,p_ATTRIBUTE9 => p_ENTYROUT_rec.ATTRIBUTE9
397 ,p_ATTRIBUTE10 => p_ENTYROUT_rec.ATTRIBUTE10
398 ,p_ATTRIBUTE11 => p_ENTYROUT_rec.ATTRIBUTE11
399 ,p_ATTRIBUTE12 => p_ENTYROUT_rec.ATTRIBUTE12
400 ,p_ATTRIBUTE13 => p_ENTYROUT_rec.ATTRIBUTE13
401 ,p_ATTRIBUTE14 => p_ENTYROUT_rec.ATTRIBUTE14
402 ,p_ATTRIBUTE15 => p_ENTYROUT_rec.ATTRIBUTE15
403 ); --
404 -- End of API body.
405 --
406
407 -- Standard check for p_commit
408 IF FND_API.to_Boolean( p_commit )
409 THEN
410 COMMIT WORK;
411 END IF;
412
413
414 -- Debug Message
415 IF (AS_DEBUG_HIGH_ON) THEN
416
417 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'Private API: ' || l_api_name || 'end');
418 END IF;
419
420
421 -- Standard call to get message count and if count is 1, get message info.
422 FND_MSG_PUB.Count_And_Get
423 ( p_count => x_msg_count,
424 p_data => x_msg_data
425 );
426
427 EXCEPTION
428 WHEN FND_API.G_EXC_ERROR THEN
429 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
430 P_API_NAME => L_API_NAME
431 ,P_PKG_NAME => G_PKG_NAME
432 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
433 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
434 ,X_MSG_COUNT => X_MSG_COUNT
435 ,X_MSG_DATA => X_MSG_DATA
436 ,X_RETURN_STATUS => X_RETURN_STATUS);
437
438 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
439 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
440 P_API_NAME => L_API_NAME
441 ,P_PKG_NAME => G_PKG_NAME
442 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
443 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
444 ,X_MSG_COUNT => X_MSG_COUNT
445 ,X_MSG_DATA => X_MSG_DATA
446 ,X_RETURN_STATUS => X_RETURN_STATUS);
447
448 WHEN OTHERS THEN
449 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
450 P_API_NAME => L_API_NAME
451 ,P_PKG_NAME => G_PKG_NAME
452 ,P_EXCEPTION_LEVEL => AS_UTILITY_PVT.G_EXC_OTHERS
453 ,P_SQLCODE => SQLCODE
454 ,P_SQLERRM => SQLERRM
455 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
456 ,X_MSG_COUNT => X_MSG_COUNT
457 ,X_MSG_DATA => X_MSG_DATA
458 ,X_RETURN_STATUS => X_RETURN_STATUS);
459 End Update_entyrout;
460
461
462 -- Hint: Add corresponding delete detail table procedures if it's master-detail relationship.
463 -- The Master delete procedure may not be needed depends on different business requirements.
464 PROCEDURE Delete_entyrout(
465 P_Api_Version_Number IN NUMBER,
466 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
467 P_Commit IN VARCHAR2 := FND_API.G_FALSE,
468 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
469 P_Identity_Resource_Id IN NUMBER,
470 P_ENTYROUT_Rec IN PV_RULE_RECTYPE_PUB.ENTYROUT_Rec_Type,
471 X_Return_Status OUT NOCOPY VARCHAR2,
472 X_Msg_Count OUT NOCOPY NUMBER,
473 X_Msg_Data OUT NOCOPY VARCHAR2
474 )
475
476 IS
477 l_api_name CONSTANT VARCHAR2(30) := 'Delete_entyrout';
478 l_api_version_number CONSTANT NUMBER := 2.0;
479 BEGIN
480 -- Standard Start of API savepoint
481 SAVEPOINT DELETE_ENTYROUT_PVT;
482
483 -- Standard call to check for call compatibility.
484 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
485 p_api_version_number,
486 l_api_name,
487 G_PKG_NAME)
488 THEN
489 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
490 END IF;
491
492
493 -- Initialize message list if p_init_msg_list is set to TRUE.
494 IF FND_API.to_Boolean( p_init_msg_list )
495 THEN
496 FND_MSG_PUB.initialize;
497 END IF;
498
499
500 -- Debug Message
501 IF (AS_DEBUG_HIGH_ON) THEN
502
503 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'Private API: ' || l_api_name || 'start');
504 END IF;
505
506
507 -- Initialize API return status to SUCCESS
508 x_return_status := FND_API.G_RET_STS_SUCCESS;
509
510 --
511 -- Api body
512 --
513 -- Debug Message
514 IF (AS_DEBUG_HIGH_ON) THEN
515
516 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'Private API: Calling delete table handler');
517 END IF;
518
519 -- Invoke table handler(PV_ENTITY_ROUTINGS_PKG.Delete_Row)
520 PV_ENTITY_ROUTINGS_PKG.Delete_Row(
521 p_ENTITY_ROUTING_ID => p_ENTYROUT_rec.ENTITY_ROUTING_ID);
522 --
523 -- End of API body
524 --
525
526 -- Standard check for p_commit
527 IF FND_API.to_Boolean( p_commit )
528 THEN
529 COMMIT WORK;
530 END IF;
531
532
533 -- Debug Message
534 IF (AS_DEBUG_HIGH_ON) THEN
535
536 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'Private API: ' || l_api_name || 'end');
537 END IF;
538
539
540 -- Standard call to get message count and if count is 1, get message info.
541 FND_MSG_PUB.Count_And_Get
542 ( p_count => x_msg_count,
543 p_data => x_msg_data
544 );
545
546 EXCEPTION
547 WHEN FND_API.G_EXC_ERROR THEN
548 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
549 P_API_NAME => L_API_NAME
550 ,P_PKG_NAME => G_PKG_NAME
551 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
552 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
553 ,X_MSG_COUNT => X_MSG_COUNT
554 ,X_MSG_DATA => X_MSG_DATA
555 ,X_RETURN_STATUS => X_RETURN_STATUS);
556
557 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
558 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
559 P_API_NAME => L_API_NAME
560 ,P_PKG_NAME => G_PKG_NAME
561 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
562 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
563 ,X_MSG_COUNT => X_MSG_COUNT
564 ,X_MSG_DATA => X_MSG_DATA
565 ,X_RETURN_STATUS => X_RETURN_STATUS);
566
567 WHEN OTHERS THEN
568 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
569 P_API_NAME => L_API_NAME
570 ,P_PKG_NAME => G_PKG_NAME
571 ,P_EXCEPTION_LEVEL => AS_UTILITY_PVT.G_EXC_OTHERS
572 ,P_SQLCODE => SQLCODE
573 ,P_SQLERRM => SQLERRM
574 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
575 ,X_MSG_COUNT => X_MSG_COUNT
576 ,X_MSG_DATA => X_MSG_DATA
577 ,X_RETURN_STATUS => X_RETURN_STATUS);
578 End Delete_entyrout;
579
580
581 -- Item-level validation procedures
582 PROCEDURE Validate_ENTITY_ROUTING_ID (
583 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
584 P_Validation_mode IN VARCHAR2,
585 P_ENTITY_ROUTING_ID IN NUMBER,
586 X_Return_Status OUT NOCOPY VARCHAR2,
587 X_Msg_Count OUT NOCOPY NUMBER,
588 X_Msg_Data OUT NOCOPY VARCHAR2
589 )
590 IS
591 CURSOR C_entity_routing_id_exists (pc_entity_routing_id NUMBER) IS
592 SELECT 'X'
593 FROM pv_entity_routings
594 WHERE entity_routing_id = pc_entity_routing_id;
595
596 l_val VARCHAR2(1);
597 BEGIN
598
599 -- Initialize message list if p_init_msg_list is set to TRUE.
600 IF FND_API.to_Boolean( p_init_msg_list )
601 THEN
602 FND_MSG_PUB.initialize;
603 END IF;
604
605
606 -- Initialize API return status to SUCCESS
607 x_return_status := FND_API.G_RET_STS_SUCCESS;
608
609 IF(p_validation_mode = AS_UTILITY_PVT.G_CREATE)
610 THEN
611 IF (p_entity_routing_id IS NOT NULL) AND
612 (p_entity_routing_id <> FND_API.G_MISS_NUM)
613 THEN
614 AS_UTILITY_PVT.Set_Message(
615 p_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR,
616 p_msg_name => 'API_INVALID_ID',
617 p_token1 => 'ENTITY_ROUTING_ID',
618 p_token1_value => p_entity_routing_id);
619
620 x_return_status := FND_API.G_RET_STS_ERROR;
621 END IF;
622
623 ELSIF(p_validation_mode = AS_UTILITY_PVT.G_UPDATE)
624 THEN
625 -- validate NOT NULL column
626 IF (p_entity_routing_id IS NULL) OR
627 (p_entity_routing_id = FND_API.G_MISS_NUM)
628 THEN
629 AS_UTILITY_PVT.Set_Message(
630 p_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR,
631 p_msg_name => 'API_MISSING_LEAD_ID');
632
633 x_return_status := FND_API.G_RET_STS_ERROR;
634 ELSE
635 OPEN C_ENTITY_ROUTING_ID_exists (p_entity_routing_id);
636 FETCH C_ENTITY_ROUTING_ID_exists into l_val;
637
638 IF C_ENTITY_ROUTING_ID_exists%NOTFOUND
639 THEN
640 AS_UTILITY_PVT.Set_Message(
641 p_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR,
642 p_msg_name => 'API_INVALID_ID',
643 p_token1 => 'ENTITY_ROUTING_ID',
644 p_token1_value => p_entity_routing_id );
645
646 x_return_status := FND_API.G_RET_STS_ERROR;
647 END IF;
648
649 CLOSE C_ENTITY_ROUTING_ID_exists;
650 END IF;
651 END IF;
652
653
654 -- Standard call to get message count and if count is 1, get message info.
655 FND_MSG_PUB.Count_And_Get
656 ( p_count => x_msg_count,
657 p_data => x_msg_data
658 );
659
660 END Validate_ENTITY_ROUTING_ID;
661
662
663 PROCEDURE Validate_UNMATCHED_INT_RS_ID (
664 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
665 P_Validation_mode IN VARCHAR2,
666 P_UNMATCHED_INT_RESOURCE_ID IN NUMBER,
667 X_Return_Status OUT NOCOPY VARCHAR2,
668 X_Msg_Count OUT NOCOPY NUMBER,
669 X_Msg_Data OUT NOCOPY VARCHAR2
670 )
671 IS
672 BEGIN
673
674 -- Initialize message list if p_init_msg_list is set to TRUE.
675 IF FND_API.to_Boolean( p_init_msg_list )
676 THEN
677 FND_MSG_PUB.initialize;
678 END IF;
679
680
681 -- Initialize API return status to SUCCESS
682 x_return_status := FND_API.G_RET_STS_SUCCESS;
683
684 IF(p_validation_mode = AS_UTILITY_PVT.G_CREATE)
685 THEN
686 -- Hint: Validate data
687 -- IF p_UNMATCHED_INT_RESOURCE_ID is not NULL and p_UNMATCHED_INT_RESOURCE_ID <> G_MISS_CHAR
688 -- verify if data is valid
689 -- if data is not valid : x_return_status := FND_API.G_RET_STS_ERROR;
690 NULL;
691 ELSIF(p_validation_mode = AS_UTILITY_PVT.G_UPDATE)
692 THEN
693 -- Hint: Validate data
694 -- IF p_UNMATCHED_INT_RESOURCE_ID <> G_MISS_CHAR
695 -- verify if data is valid
696 -- if data is not valid : x_return_status := FND_API.G_RET_STS_ERROR;
697 NULL;
698 END IF;
699
700 -- Standard call to get message count and if count is 1, get message info.
701 FND_MSG_PUB.Count_And_Get
702 ( p_count => x_msg_count,
703 p_data => x_msg_data
704 );
705
706 END Validate_UNMATCHED_INT_RS_ID;
707
708
709 PROCEDURE Validate_entyrout(
710 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
711 P_Validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
712 P_Validation_mode IN VARCHAR2,
713 P_ENTYROUT_Rec IN PV_RULE_RECTYPE_PUB.ENTYROUT_Rec_Type,
714 X_Return_Status OUT NOCOPY VARCHAR2,
715 X_Msg_Count OUT NOCOPY NUMBER,
716 X_Msg_Data OUT NOCOPY VARCHAR2
717 )
718 IS
719 l_api_name CONSTANT VARCHAR2(30) := 'Validate_entyrout';
720 BEGIN
721
722 -- Debug Message
723 IF (AS_DEBUG_HIGH_ON) THEN
724
725 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'Private API: ' || l_api_name || 'start');
726 END IF;
727
728
729 -- Initialize API return status to SUCCESS
730 x_return_status := FND_API.G_RET_STS_SUCCESS;
731
732 IF (p_validation_level >= AS_UTILITY_PUB.G_VALID_LEVEL_ITEM) THEN
733
734 Validate_ENTITY_ROUTING_ID(
735 p_init_msg_list => FND_API.G_FALSE,
736 p_validation_mode => p_validation_mode,
737 p_ENTITY_ROUTING_ID => P_ENTYROUT_Rec.ENTITY_ROUTING_ID,
738 x_return_status => x_return_status,
739 x_msg_count => x_msg_count,
740 x_msg_data => x_msg_data);
741
742 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
743 raise FND_API.G_EXC_ERROR;
744 END IF;
745
746 pv_common_checks_pvt.Validate_OBJECT_VERSION_NUMBER(
747 p_init_msg_list => FND_API.G_FALSE,
748 p_validation_mode => p_validation_mode,
749 p_OBJECT_VERSION_NUMBER => P_ENTYROUT_Rec.OBJECT_VERSION_NUMBER,
750 x_return_status => x_return_status,
751 x_msg_count => x_msg_count,
752 x_msg_data => x_msg_data);
753
754 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
755 raise FND_API.G_EXC_ERROR;
756 END IF;
757
758 pv_common_checks_pvt.Validate_PROCESS_RULE_ID(
759 p_init_msg_list => FND_API.G_FALSE,
760 p_validation_mode => p_validation_mode,
761 p_PROCESS_RULE_ID => P_ENTYROUT_Rec.PROCESS_RULE_ID,
762 x_return_status => x_return_status,
763 x_msg_count => x_msg_count,
764 x_msg_data => x_msg_data);
765
766 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
767 raise FND_API.G_EXC_ERROR;
768 END IF;
769
770 if p_ENTYROUT_Rec.DISTANCE_UOM_CODE is NOT NULL and
771 p_ENTYROUT_Rec.DISTANCE_UOM_CODE <> FND_API.G_MISS_CHAR then
772
773 pv_common_checks_pvt.Validate_Lookup(
774 p_init_msg_list => FND_API.G_FALSE,
775 p_validation_mode => p_validation_mode,
776 p_TABLE_NAME => 'PV_ENTITY_ROUTINGS',
777 p_COLUMN_NAME => 'DISTANCE_UOM_CODE',
778 p_lookup_type => 'PV_DISTANCE_UOM',
779 p_lookup_code => P_ENTYROUT_Rec.DISTANCE_UOM_CODE,
780 x_return_status => x_return_status,
781 x_msg_count => x_msg_count,
782 x_msg_data => x_msg_data);
783
784 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
785 raise FND_API.G_EXC_ERROR;
786 END IF;
787
788 END IF;
789
790 if p_ENTYROUT_Rec.ROUTING_TYPE is NOT NULL and
791 p_ENTYROUT_Rec.ROUTING_TYPE <> FND_API.G_MISS_CHAR then
792
793 pv_common_checks_pvt.Validate_Lookup(
794 p_init_msg_list => FND_API.G_FALSE,
795 p_validation_mode => p_validation_mode,
796 p_TABLE_NAME => 'PV_ENTITY_ROUTINGS',
797 p_COLUMN_NAME => 'ROUTING_TYPE',
798 p_lookup_type => 'PV_ASSIGNMENT_TYPE',
799 p_lookup_code => P_ENTYROUT_Rec.ROUTING_TYPE,
800 x_return_status => x_return_status,
801 x_msg_count => x_msg_count,
802 x_msg_data => x_msg_data);
803
804 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
805 raise FND_API.G_EXC_ERROR;
806 END IF;
807
808 END IF;
809
810 pv_common_checks_pvt.Validate_FLAG(
811 p_init_msg_list => FND_API.G_FALSE,
812 p_validation_mode => p_validation_mode,
813 p_FLAG => P_ENTYROUT_Rec.BYPASS_CM_OK_FLAG,
814 x_return_status => x_return_status,
815 x_msg_count => x_msg_count,
816 x_msg_data => x_msg_data);
817
818 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
819 raise FND_API.G_EXC_ERROR;
820 END IF;
821
822 if p_ENTYROUT_Rec.CM_TIMEOUT_UOM_CODE is NOT NULL and
823 p_ENTYROUT_Rec.CM_TIMEOUT_UOM_CODE <> FND_API.G_MISS_CHAR then
824
825 pv_common_checks_pvt.Validate_Lookup(
826 p_init_msg_list => FND_API.G_FALSE,
827 p_validation_mode => p_validation_mode,
828 p_TABLE_NAME => 'PV_ENTITY_ROUTINGS',
829 p_COLUMN_NAME => 'CM_TIMEOUT_UOM_CODE',
830 p_lookup_type => 'PV_TIMEOUT_UOM',
831 p_lookup_code => P_ENTYROUT_Rec.CM_TIMEOUT_UOM_CODE,
832 x_return_status => x_return_status,
833 x_msg_count => x_msg_count,
834 x_msg_data => x_msg_data);
835
836 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
837 raise FND_API.G_EXC_ERROR;
838 END IF;
839
840 END IF;
841
842 --ryellapu - validation for channel manager and partner timeout 08/23/02.
843
844 if p_ENTYROUT_Rec.PARTNER_TIMEOUT is NOT NULL and
845 p_ENTYROUT_Rec.PARTNER_TIMEOUT <> FND_API.G_MISS_NUM then
846 IF P_ENTYROUT_Rec.PARTNER_TIMEOUT > 9999 then
847 FND_MESSAGE.Set_Name('PV', 'PV_TIMEOUT_UPPER_RANGE_CHECK');
848 FND_MSG_PUB.Add;
849 RAISE FND_API.G_EXC_ERROR;
850 END IF;
851 end if;
852
853 if p_ENTYROUT_Rec.CM_TIMEOUT is NOT NULL and
854 p_ENTYROUT_Rec.CM_TIMEOUT <> FND_API.G_MISS_NUM then
855 IF P_ENTYROUT_Rec.CM_TIMEOUT > 9999 then
856 FND_MESSAGE.Set_Name('PV', 'PV_TIMEOUT_UPPER_RANGE_CHECK');
857 FND_MSG_PUB.Add;
858 RAISE FND_API.G_EXC_ERROR;
859 END IF;
860 end if;
861
862 if p_ENTYROUT_Rec.PARTNER_TIMEOUT_UOM_CODE is NOT NULL and
863 p_ENTYROUT_Rec.PARTNER_TIMEOUT_UOM_CODE <> FND_API.G_MISS_CHAR then
864
865 pv_common_checks_pvt.Validate_Lookup(
866 p_init_msg_list => FND_API.G_FALSE,
867 p_validation_mode => p_validation_mode,
868 p_TABLE_NAME => 'PV_ENTITY_ROUTINGS',
869 p_COLUMN_NAME => 'PARTNER_TIMEOUT_UOM_CODE',
870 p_lookup_type => 'PV_TIMEOUT_UOM',
871 p_lookup_code => P_ENTYROUT_Rec.PARTNER_TIMEOUT_UOM_CODE,
872 x_return_status => x_return_status,
873 x_msg_count => x_msg_count,
874 x_msg_data => x_msg_data);
875
876 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
877 raise FND_API.G_EXC_ERROR;
878 END IF;
879
880 END IF;
881
882 Validate_UNMATCHED_INT_RS_ID(
883 p_init_msg_list => FND_API.G_FALSE,
884 p_validation_mode => p_validation_mode,
885 p_UNMATCHED_INT_RESOURCE_ID => P_ENTYROUT_Rec.UNMATCHED_INT_RESOURCE_ID,
886 x_return_status => x_return_status,
887 x_msg_count => x_msg_count,
888 x_msg_data => x_msg_data);
889 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
890 raise FND_API.G_EXC_ERROR;
891 END IF;
892
893 pv_common_checks_pvt.Validate_FLAG(
894 p_init_msg_list => FND_API.G_FALSE,
895 p_validation_mode => p_validation_mode,
896 p_FLAG => P_ENTYROUT_Rec.UNMATCHED_CALL_TAP_FLAG,
897 x_return_status => x_return_status,
898 x_msg_count => x_msg_count,
899 x_msg_data => x_msg_data);
900
901 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
902 raise FND_API.G_EXC_ERROR;
903 END IF;
904
905 END IF;
906
907 IF (p_validation_level >= AS_UTILITY_PUB.G_VALID_LEVEL_RECORD) THEN
908 -- Hint: Inter-field level validation can be added here
909 -- invoke record level validation procedures
910 NULL;
911 END IF;
912
913 IF (p_validation_level >= AS_UTILITY_PUB.G_VALID_LEVEL_INTER_RECORD) THEN
914 -- invoke inter-record level validation procedures
915 NULL;
916 END IF;
917
918 IF (p_validation_level >= AS_UTILITY_PUB.G_VALID_LEVEL_INTER_ENTITY) THEN
919 -- invoke inter-entity level validation procedures
920 NULL;
921 END IF;
922
923
924 -- Debug Message
925 IF (AS_DEBUG_HIGH_ON) THEN
926
927 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'Private API: ' || l_api_name || 'end');
928 END IF;
929
930 END Validate_entyrout;
931
932 End PV_ENTYROUT_PVT;