[Home] [Help]
PACKAGE BODY: APPS.AML_TIMEFRAME_PVT
Source
1 PACKAGE BODY AML_TIMEFRAME_PVT as
2 /* $Header: amlvtfrb.pls 115.8 2004/01/20 20:39:34 chchandr noship $ */
3 -- Start of Comments
4 -- Package name : AML_TIMEFRAME_PVT
5 -- Purpose :
6 -- History :
7 -- NOTE :
8 -- End of Comments
9
10
11 G_PKG_NAME CONSTANT VARCHAR2(30):= 'AML_TIMEFRAME_PVT';
12 G_FILE_NAME CONSTANT VARCHAR2(12) := 'amlvtfrb.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 AS_DEBUG_ERROR_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_ERROR);
20
21 PROCEDURE Create_timeframe(
22 P_Api_Version_Number IN NUMBER,
23 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
24 P_Commit IN VARCHAR2 := FND_API.G_FALSE,
25 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
26 P_Check_Access_Flag IN VARCHAR2 := FND_API.G_FALSE,
27 P_Admin_Flag IN VARCHAR2 := FND_API.G_FALSE,
28 P_Admin_Group_Id IN NUMBER,
29 P_Identity_Salesforce_Id IN NUMBER,
30 P_profile_tbl IN AS_UTILITY_PUB.PROFILE_TBL_TYPE,
31 P_timeframe_Rec IN AML_TIMEFRAME_PUB.timeframe_Rec_Type ,
32 X_TIMEFRAME_ID OUT NOCOPY NUMBER,
33 X_Return_Status OUT NOCOPY VARCHAR2,
34 X_Msg_Count OUT NOCOPY NUMBER,
35 X_Msg_Data OUT NOCOPY VARCHAR2
36 )
37
38 IS
39 l_api_name CONSTANT VARCHAR2(30) := 'Create_timeframe';
40 l_api_version_number CONSTANT NUMBER := 2.0;
41 l_return_status_full VARCHAR2(1);
42 l_identity_sales_member_rec AS_SALES_MEMBER_PUB.Sales_member_rec_Type;
43 l_access_flag VARCHAR2(1);
44 l_timeframe_rec AML_TIMEFRAME_PUB.timeframe_REC_TYPE;
45 l_dummy CHAR(1);
46
47
48 CURSOR c1 IS
49 SELECT 'X' FROM AML_SALES_LEAD_TIMEFRAMES
50 WHERE decision_timeframe_code = p_timeframe_rec.decision_timeframe_code
51 AND enabled_flag = 'Y';
52
53
54 BEGIN
55 -- Standard Start of API savepoint
56 SAVEPOINT CREATE_TIMEFRAME_PVT;
57
58 -- Standard call to check for call compatibility.
59 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
60 p_api_version_number,
61 l_api_name,
62 G_PKG_NAME)
63 THEN
64 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
65 END IF;
66
67
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
75 -- Debug Message
76 IF (AS_DEBUG_LOW_ON) THEN
77
78 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'Private API: ' || l_api_name || 'start');
79 END IF;
80
81
82 -- Initialize API return status to SUCCESS
83 x_return_status := FND_API.G_RET_STS_SUCCESS;
84
85 l_timeframe_rec := p_timeframe_rec;
86
87 --
88 -- API body
89 --
90
91
92 -- if p_timeframe_rec.enabled_flag is 'Y' then check for duplicates.
93
94 if (p_timeframe_rec.ENABLED_FLAG = 'Y') then
95
96 OPEN c1;
97 FETCH c1 INTO l_dummy;
98 IF c1%FOUND THEN
99 CLOSE c1;
100 --dbms_output.put_line('duplicate found ');
101 FND_MESSAGE.SET_NAME('AS', 'AS_DUPE_TIMEFRAME');
102 -- Add message to API message list
103 FND_MSG_PUB.Add;
104 RAISE FND_API.G_EXC_ERROR;
105 END IF;
106 CLOSE c1;
107
108 END IF;
109
110
111
112
113 -- Debug message
114 IF (AS_DEBUG_LOW_ON) THEN
115
116 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
117 'Pvt API: ' || l_timeframe_rec.DECISION_TIMEFRAME_CODE);
118 END IF;
119
120
121
122 Validate_DECN_TIMEFRAME_CODE(
123 p_init_msg_list => FND_API.G_FALSE,
124 p_validation_mode => AS_UTILITY_PVT.G_CREATE,
125 p_decision_timeframe_code => l_timeframe_rec.DECISION_TIMEFRAME_CODE,
126 x_return_status => x_return_status,
127 x_msg_count => x_msg_count,
128 x_msg_data => x_msg_data);
129 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
130 raise FND_API.G_EXC_ERROR;
131 END IF;
132
133
134
135 -- Check the timeframe days
136 Validate_TIMEFRAME_DAYS(
137 P_Init_Msg_List => FND_API.G_FALSE,
138 P_Validation_mode => AS_UTILITY_PVT.G_CREATE,
139 p_timeframe_days => l_timeframe_rec.timeframe_days,
140 X_Return_Status => x_return_status,
141 X_Msg_Count => x_msg_count,
142 X_Msg_Data => x_msg_data
143 );
144
145 IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
146 RAISE FND_API.G_EXC_ERROR;
147 END IF;
148
149
150 -- Debug Message
151 IF (AS_DEBUG_LOW_ON) THEN
152
153 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
154 'Private API: Calling create table handler');
155 END IF;
156
157 -- Invoke table handler(AML_SALES_LEAD_TIMEFRAMES_PKG.Insert_Row)
158 AML_SALES_LEAD_TIMEFRAMES_PKG.Insert_Row(
159 px_TIMEFRAME_ID => x_TIMEFRAME_ID
160 ,p_DECISION_TIMEFRAME_CODE => l_timeframe_rec.DECISION_TIMEFRAME_CODE
161 ,p_TIMEFRAME_DAYS => l_timeframe_rec.TIMEFRAME_DAYS
162 ,p_CREATION_DATE => sysdate --l_timeframe_rec.CREATION_DATE
163 ,p_CREATED_BY => FND_GLOBAL.USER_ID --l_timeframe_rec.CREATED_BY
164 ,p_LAST_UPDATE_DATE => sysdate --l_timeframe_rec.LAST_UPDATE_DATE
165 ,p_LAST_UPDATED_BY => FND_GLOBAL.USER_ID -- l_timeframe_rec.LAST_UPDATED_BY
166 ,p_LAST_UPDATE_LOGIN => FND_GLOBAL.CONC_LOGIN_ID --l_timeframe_rec.LAST_UPDATE_LOGIN
167 , p_ENABLED_FLAG => NVL(l_timeframe_rec.ENABLED_FLAG, 'N')
168
169
170
171 );
172 -- Hint: Primary key should be returned.
173 -- x_TIMEFRAME_ID := px_TIMEFRAME_ID;
174
175 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
176 RAISE FND_API.G_EXC_ERROR;
177 END IF;
178
179 --
180 -- End of API body
181 --
182
183 -- Standard check for p_commit
184 IF FND_API.to_Boolean( p_commit )
185 THEN
186 COMMIT WORK;
187 END IF;
188
189
190 -- Debug Message
191 IF (AS_DEBUG_LOW_ON) THEN
192
193 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'Private API: ' || l_api_name || 'end');
194 END IF;
195
196
197 -- Standard call to get message count and if count is 1, get message info.
198 FND_MSG_PUB.Count_And_Get
199 ( p_count => x_msg_count,
200 p_data => x_msg_data
201 );
202
203 EXCEPTION
204 WHEN FND_API.G_EXC_ERROR THEN
205 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
206 P_API_NAME => L_API_NAME
207 ,P_PKG_NAME => G_PKG_NAME
208 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
209 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
210 ,X_MSG_COUNT => X_MSG_COUNT
211 ,X_MSG_DATA => X_MSG_DATA
212 ,X_RETURN_STATUS => X_RETURN_STATUS);
213
214 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
215 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
216 P_API_NAME => L_API_NAME
217 ,P_PKG_NAME => G_PKG_NAME
218 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
219 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
220 ,X_MSG_COUNT => X_MSG_COUNT
221 ,X_MSG_DATA => X_MSG_DATA
222 ,X_RETURN_STATUS => X_RETURN_STATUS);
223
224 WHEN OTHERS THEN
225 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
226 P_API_NAME => L_API_NAME
227 ,P_PKG_NAME => G_PKG_NAME
228 ,P_EXCEPTION_LEVEL => AS_UTILITY_PVT.G_EXC_OTHERS
229 ,P_SQLCODE => SQLCODE
230 ,P_SQLERRM => SQLERRM
231 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
232 ,X_MSG_COUNT => X_MSG_COUNT
233 ,X_MSG_DATA => X_MSG_DATA
234 ,X_RETURN_STATUS => X_RETURN_STATUS);
235 End Create_timeframe;
236
237
238 -- Hint: Add corresponding update detail table procedures if it's master-detail relationship.
239 PROCEDURE Update_timeframe(
240 P_Api_Version_Number IN NUMBER,
241 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
242 P_Commit IN VARCHAR2 := FND_API.G_FALSE,
243 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
244 P_Check_Access_Flag IN VARCHAR2 := FND_API.G_FALSE,
245 P_Admin_Flag IN VARCHAR2 := FND_API.G_FALSE,
246 P_Admin_Group_Id IN NUMBER,
247 P_Identity_Salesforce_Id IN NUMBER,
248 P_profile_tbl IN AS_UTILITY_PUB.PROFILE_TBL_TYPE,
249 P_timeframe_Rec IN AML_TIMEFRAME_PUB.timeframe_Rec_Type,
250 X_Return_Status OUT NOCOPY VARCHAR2,
251 X_Msg_Count OUT NOCOPY NUMBER,
252 X_Msg_Data OUT NOCOPY VARCHAR2
253 )
254
255 IS
256 /*
257 Cursor C_Get_timeframe(TIMEFRAME_ID Number) IS
258 Select rowid,
259 TIMEFRAME_ID,
260 DECISION_TIMEFRAME_CODE,
261 TIMEFRAME_DAYS
262 From AML_SALES_LEAD_TIMEFRAMES
263 -- Hint: Developer need to provide Where clause
264 For Update NOWAIT;
265 */
266 l_api_name CONSTANT VARCHAR2(30) := 'Update_timeframe';
267 l_api_version_number CONSTANT NUMBER := 2.0;
268 -- Local Variables
269 l_identity_sales_member_rec AS_SALES_MEMBER_PUB.Sales_member_rec_Type;
270 l_ref_timeframe_rec AML_timeframe_PUB.timeframe_Rec_Type;
271 l_tar_timeframe_rec AML_timeframe_PUB.timeframe_Rec_Type := P_timeframe_Rec;
272 l_rowid ROWID;
273 l_dummy CHAR(1);
274
275
276 CURSOR c1 IS
277 SELECT 'X' FROM AML_SALES_LEAD_TIMEFRAMES
278 WHERE decision_timeframe_code = p_timeframe_rec.decision_timeframe_code
279 and timeframe_id <> p_timeframe_rec.timeframe_id
280 AND enabled_flag = 'Y';
281
282 BEGIN
283 -- Standard Start of API savepoint
284 SAVEPOINT UPDATE_TIMEFRAME_PVT;
285
286 -- Standard call to check for call compatibility.
287 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
288 p_api_version_number,
289 l_api_name,
290 G_PKG_NAME)
291 THEN
292 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
293 END IF;
294
295
296 -- Initialize message list if p_init_msg_list is set to TRUE.
297 IF FND_API.to_Boolean( p_init_msg_list )
298 THEN
299 FND_MSG_PUB.initialize;
300 END IF;
301
302
303 -- Debug Message
304 IF (AS_DEBUG_LOW_ON) THEN
305
306 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'Private API: ' || l_api_name || 'start');
307 END IF;
308
309
310
311 -- Initialize API return status to SUCCESS
312 x_return_status := FND_API.G_RET_STS_SUCCESS;
313
314 --
315 -- Api body
316 --
317
318
319 -- Debug Message
320 IF (AS_DEBUG_LOW_ON) THEN
321
322 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
323 'Private API: - Open Cursor to Select');
324 END IF;
325
326
327 If (l_tar_timeframe_rec.last_update_date is NULL or
328 l_tar_timeframe_rec.last_update_date = FND_API.G_MISS_Date ) Then
329 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
330 THEN
331 FND_MESSAGE.Set_Name('AS', 'API_MISSING_ID');
332 FND_MESSAGE.Set_Token('COLUMN', 'Last_Update_Date', FALSE);
333 FND_MSG_PUB.ADD;
334 END IF;
335 raise FND_API.G_EXC_ERROR;
336 End if;
337 -- Check Whether record has been changed by someone else
338 If (l_tar_timeframe_rec.last_update_date <> l_ref_timeframe_rec.last_update_date) Then
339 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
340 THEN
341 FND_MESSAGE.Set_Name('AS', 'API_RECORD_CHANGED');
342 FND_MESSAGE.Set_Token('INFO', 'timeframe', FALSE);
343 FND_MSG_PUB.ADD;
344 END IF;
345 raise FND_API.G_EXC_ERROR;
346 End if;
347
348 -- Debug message
349 IF (AS_DEBUG_LOW_ON) THEN
350
351 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
352 'Private API: Validate_timeframe');
353 END IF;
354
355 -- Invoke validation procedures
356
357
358
359 -- Validate decision timeframe code.
360
361
362 -- Validate timeframe days
363
364 IF (AS_DEBUG_LOW_ON) THEN
365
366
367
368 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
369 'Private API: Validate_timeframe');
370
371 END IF;
372
373
374
375 Validate_DECN_TIMEFRAME_CODE(
376 p_init_msg_list => FND_API.G_FALSE,
377 p_validation_mode => AS_UTILITY_PVT.G_UPDATE,
378 p_decision_timeframe_code => l_tar_timeframe_rec.DECISION_TIMEFRAME_CODE,
379 x_return_status => x_return_status,
380 x_msg_count => x_msg_count,
381 x_msg_data => x_msg_data);
382 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
383 raise FND_API.G_EXC_ERROR;
384 END IF;
385
386
387
388 -- Check the timeframe days
389 Validate_TIMEFRAME_DAYS(
390 P_Init_Msg_List => FND_API.G_FALSE,
391 P_Validation_mode => AS_UTILITY_PVT.G_UPDATE,
392 p_timeframe_days => l_tar_timeframe_rec.timeframe_days,
393 X_Return_Status => x_return_status,
394 X_Msg_Count => x_msg_count,
395 X_Msg_Data => x_msg_data
396 );
397
398 IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
399 RAISE FND_API.G_EXC_ERROR;
400 END IF;
401
402
403
404 if (p_timeframe_rec.ENABLED_FLAG = 'Y') then
405
406 OPEN c1;
407 FETCH c1 INTO l_dummy;
408 IF c1%FOUND THEN
409 CLOSE c1;
410 --dbms_output.put_line('duplicate found ');
411 FND_MESSAGE.SET_NAME('AS', 'AS_DUPE_TIMEFRAME');
412 -- Add message to API message list
413 FND_MSG_PUB.Add;
414 RAISE FND_API.G_EXC_ERROR;
415 END IF;
416 CLOSE c1;
417
418 END IF;
419
420
421
422
423 -- Debug Message
424 IF (AS_DEBUG_LOW_ON) THEN
425
426 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
427 'Private API: Calling update table handler');
428 END IF;
429
430 -- Invoke table handler(AML_SALES_LEAD_TIMEFRAMES_PKG.Update_Row)
431 AML_SALES_LEAD_TIMEFRAMES_PKG.Update_Row(
432 p_TIMEFRAME_ID => p_timeframe_rec.TIMEFRAME_ID
433 ,p_DECISION_TIMEFRAME_CODE => p_timeframe_rec.DECISION_TIMEFRAME_CODE
434 ,p_TIMEFRAME_DAYS => p_timeframe_rec.TIMEFRAME_DAYS
435 ,p_CREATION_DATE => p_timeframe_rec.CREATION_DATE
436 ,p_CREATED_BY => p_timeframe_rec.CREATED_BY
437 ,p_LAST_UPDATE_DATE => sysdate
438 ,p_LAST_UPDATED_BY => FND_GLOBAL.user_id
439 ,p_LAST_UPDATE_LOGIN => FND_GLOBAL.CONC_LOGIN_ID --FND_GLOBAL.user_id
440 , p_ENABLED_FLAG => NVL(p_timeframe_rec.ENABLED_FLAG, 'N')
441
442
443 );
444 --
445 -- End of API body.
446 --
447
448 -- Standard check for p_commit
449 IF FND_API.to_Boolean( p_commit )
450 THEN
451 COMMIT WORK;
452 END IF;
453
454
455 -- Debug Message
456 IF (AS_DEBUG_LOW_ON) THEN
457
458 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'Private API: ' || l_api_name || 'end');
459 END IF;
460
461
462 -- Standard call to get message count and if count is 1, get message info.
463 FND_MSG_PUB.Count_And_Get
464 ( p_count => x_msg_count,
465 p_data => x_msg_data
466 );
467
468 EXCEPTION
469 WHEN FND_API.G_EXC_ERROR THEN
470 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
471 P_API_NAME => L_API_NAME
472 ,P_PKG_NAME => G_PKG_NAME
473 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
474 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
475 ,X_MSG_COUNT => X_MSG_COUNT
476 ,X_MSG_DATA => X_MSG_DATA
477 ,X_RETURN_STATUS => X_RETURN_STATUS);
478
479 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
480 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
481 P_API_NAME => L_API_NAME
482 ,P_PKG_NAME => G_PKG_NAME
483 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
484 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
485 ,X_MSG_COUNT => X_MSG_COUNT
486 ,X_MSG_DATA => X_MSG_DATA
487 ,X_RETURN_STATUS => X_RETURN_STATUS);
488
489 WHEN OTHERS THEN
490 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
491 P_API_NAME => L_API_NAME
492 ,P_PKG_NAME => G_PKG_NAME
493 ,P_EXCEPTION_LEVEL => AS_UTILITY_PVT.G_EXC_OTHERS
494 ,P_SQLCODE => SQLCODE
495 ,P_SQLERRM => SQLERRM
496 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
497 ,X_MSG_COUNT => X_MSG_COUNT
498 ,X_MSG_DATA => X_MSG_DATA
499 ,X_RETURN_STATUS => X_RETURN_STATUS);
500 End Update_timeframe;
501
502
503 -- Hint: Add corresponding delete detail table procedures if it's master-detail relationship.
504 -- The Master delete procedure may not be needed depends on different business requirements.
505 PROCEDURE Delete_timeframe(
506 P_Api_Version_Number IN NUMBER,
507 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
508 P_Commit IN VARCHAR2 := FND_API.G_FALSE,
509 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
510 P_Check_Access_Flag IN VARCHAR2 := FND_API.G_FALSE,
511 P_Admin_Flag IN VARCHAR2 := FND_API.G_FALSE,
512 P_Admin_Group_Id IN NUMBER,
513 P_Identity_Salesforce_Id IN NUMBER,
514 P_Profile_Tbl IN AS_UTILITY_PUB.PROFILE_TBL_TYPE,
515 P_timeframe_Rec IN AML_TIMEFRAME_PUB.timeframe_Rec_Type,
516 X_Return_Status OUT NOCOPY VARCHAR2,
517 X_Msg_Count OUT NOCOPY NUMBER,
518 X_Msg_Data OUT NOCOPY VARCHAR2
519 )
520
521 IS
522 l_api_name CONSTANT VARCHAR2(30) := 'Delete_timeframe';
523 l_api_version_number CONSTANT NUMBER := 2.0;
524 l_identity_sales_member_rec AS_SALES_MEMBER_PUB.Sales_member_rec_Type;
525 BEGIN
526 -- Standard Start of API savepoint
527 SAVEPOINT DELETE_TIMEFRAME_PVT;
528
529 -- Standard call to check for call compatibility.
530 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
531 p_api_version_number,
532 l_api_name,
533 G_PKG_NAME)
534 THEN
535 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
536 END IF;
537
538
539 -- Initialize message list if p_init_msg_list is set to TRUE.
540 IF FND_API.to_Boolean( p_init_msg_list )
541 THEN
542 FND_MSG_PUB.initialize;
543 END IF;
544
545
546 -- Debug Message
547 IF (AS_DEBUG_LOW_ON) THEN
548
549 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'Private API: ' || l_api_name || 'start');
550 END IF;
551
552
553 -- Initialize API return status to SUCCESS
554 x_return_status := FND_API.G_RET_STS_SUCCESS;
555
556 --
557 -- Api body
558 --
559
560 -- Do not allow deletion of seeded timeframes. Disabling them is fine
561
562 if (p_timeframe_rec.TIMEFRAME_ID < 10000) then
563 FND_MESSAGE.SET_NAME('AS', 'AS_SEEDED_TIMEFRAME_NO_DELETE');
564 -- Add message to API message list
565 FND_MSG_PUB.Add;
566 RAISE FND_API.G_EXC_ERROR;
567
568 end if;
569
570 -- Do not allow deletion of enabled timeframes. Disabling them is fine
571
572 if (p_timeframe_rec.ENABLED_FLAG = 'Y') then
573 FND_MESSAGE.SET_NAME('AS', 'AS_ENABLED_TIMEFRAME_NO_DELETE');
574 -- Add message to API message list
575 FND_MSG_PUB.Add;
576 RAISE FND_API.G_EXC_ERROR;
577
578 end if;
579
580
581 -- Debug Message
582 IF (AS_DEBUG_LOW_ON) THEN
583
584 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
585 'Private API: Calling delete table handler');
586 END IF;
587
588 -- Invoke table handler(AML_SALES_LEAD_TIMEFRAMES_PKG.Delete_Row)
589 AML_SALES_LEAD_TIMEFRAMES_PKG.Delete_Row(
590 p_TIMEFRAME_ID => p_timeframe_rec.TIMEFRAME_ID);
591 --
592 -- End of API body
593 --
594
595 -- Standard check for p_commit
596 IF FND_API.to_Boolean( p_commit )
597 THEN
598 COMMIT WORK;
599 END IF;
600
601
602 -- Debug Message
603 IF (AS_DEBUG_LOW_ON) THEN
604
605 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'Private API: ' || l_api_name || 'end');
606 END IF;
607
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
615 EXCEPTION
616 WHEN FND_API.G_EXC_ERROR THEN
617 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
618 P_API_NAME => L_API_NAME
619 ,P_PKG_NAME => G_PKG_NAME
620 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
621 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
622 ,X_MSG_COUNT => X_MSG_COUNT
623 ,X_MSG_DATA => X_MSG_DATA
624 ,X_RETURN_STATUS => X_RETURN_STATUS);
625
626 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
627 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
628 P_API_NAME => L_API_NAME
629 ,P_PKG_NAME => G_PKG_NAME
630 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
631 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
632 ,X_MSG_COUNT => X_MSG_COUNT
633 ,X_MSG_DATA => X_MSG_DATA
634 ,X_RETURN_STATUS => X_RETURN_STATUS);
635
636 WHEN OTHERS THEN
637 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
638 P_API_NAME => L_API_NAME
639 ,P_PKG_NAME => G_PKG_NAME
640 ,P_EXCEPTION_LEVEL => AS_UTILITY_PVT.G_EXC_OTHERS
641 ,P_SQLCODE => SQLCODE
642 ,P_SQLERRM => SQLERRM
643 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
644 ,X_MSG_COUNT => X_MSG_COUNT
645 ,X_MSG_DATA => X_MSG_DATA
646 ,X_RETURN_STATUS => X_RETURN_STATUS);
647 End Delete_timeframe;
648
649
650 -- Item-level validation procedures
651 PROCEDURE Validate_TIMEFRAME_ID (
652 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
653 P_Validation_mode IN VARCHAR2,
654 P_TIMEFRAME_ID IN NUMBER,
655 -- Hint: You may add 'X_Item_Property_Rec OUT AS_UTILITY_PVT.ITEM_PROPERTY_REC_TYPE' here if you'd like to pass back item property.
656 X_Return_Status OUT NOCOPY VARCHAR2,
657 X_Msg_Count OUT NOCOPY NUMBER,
658 X_Msg_Data OUT NOCOPY VARCHAR2
659 )
660 IS
661 BEGIN
662
663 -- Initialize message list if p_init_msg_list is set to TRUE.
664 IF FND_API.to_Boolean( p_init_msg_list )
665 THEN
666 FND_MSG_PUB.initialize;
667 END IF;
668
669
670 -- Initialize API return status to SUCCESS
671 x_return_status := FND_API.G_RET_STS_SUCCESS;
672
673 IF(p_validation_mode = AS_UTILITY_PVT.G_CREATE)
674 THEN
675 -- Hint: Validate data
676 -- IF p_TIMEFRAME_ID is not NULL and p_TIMEFRAME_ID <> G_MISS_CHAR
677 -- verify if data is valid
678 -- if data is not valid : x_return_status := FND_API.G_RET_STS_ERROR;
679 NULL;
680 ELSIF(p_validation_mode = AS_UTILITY_PVT.G_UPDATE)
681 THEN
682 -- Hint: Validate data
683 -- IF p_TIMEFRAME_ID <> G_MISS_CHAR
684 -- verify if data is valid
685 -- if data is not valid : x_return_status := FND_API.G_RET_STS_ERROR;
686 NULL;
687 END IF;
688
689 -- Standard call to get message count and if count is 1, get message info.
690 FND_MSG_PUB.Count_And_Get
691 ( p_count => x_msg_count,
692 p_data => x_msg_data
693 );
694
698
695 END Validate_TIMEFRAME_ID;
696
697
699 PROCEDURE Validate_DECN_TIMEFRAME_CODE (
700 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
701 P_Validation_mode IN VARCHAR2,
702 P_DECISION_TIMEFRAME_CODE IN VARCHAR2,
703 -- Hint: You may add 'X_Item_Property_Rec OUT AS_UTILITY_PVT.ITEM_PROPERTY_REC_TYPE' here if you'd like to pass back item property.
704 X_Return_Status OUT NOCOPY VARCHAR2,
705 X_Msg_Count OUT NOCOPY NUMBER,
706 X_Msg_Data OUT NOCOPY VARCHAR2
707 )
708 IS
709 CURSOR C_Lookup_Exists (X_Lookup_Code VARCHAR2, X_Lookup_Type VARCHAR2) IS
710 SELECT 'X'
711 FROM as_lookups
712 WHERE lookup_type = X_Lookup_Type
713 and lookup_code = X_Lookup_Code
714 -- ffang 012501
715 and enabled_flag = 'Y';
716
717 l_val VARCHAR2(1);
718 BEGIN
719
720 -- Initialize message list if p_init_msg_list is set to TRUE.
721 IF FND_API.to_Boolean( p_init_msg_list )
722 THEN
723 FND_MSG_PUB.initialize;
724 END IF;
725
726
727 -- Initialize API return status to SUCCESS
728 x_return_status := FND_API.G_RET_STS_SUCCESS;
729
730
731 IF (p_decision_timeframe_code is NOT NULL
732 AND p_decision_timeframe_code <> FND_API.G_MISS_CHAR) THEN
733 OPEN C_Lookup_Exists ( p_decision_timeframe_code, 'DECISION_TIMEFRAME');
734 FETCH C_Lookup_Exists into l_val;
735
736 IF C_Lookup_Exists%NOTFOUND
737 THEN
738 AS_UTILITY_PVT.Set_Message(
739 p_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR,
740 p_msg_name => 'API_INVALID_ID',
741 p_token1 => 'COLUMN',
742 p_token1_value => 'DECISION TIMEFRAME_CODE',
743 p_token2 => 'VALUE',
744 p_token2_value => p_DECISION_TIMEFRAME_CODE );
745
746 x_return_status := FND_API.G_RET_STS_ERROR;
747 END IF;
748 CLOSE C_Lookup_Exists;
749 END IF;
750
751
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 END Validate_DECN_TIMEFRAME_CODE;
761
762
763 PROCEDURE Validate_TIMEFRAME_DAYS (
764 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
765 P_Validation_mode IN VARCHAR2,
766 P_TIMEFRAME_DAYS IN NUMBER,
767 -- Hint: You may add 'X_Item_Property_Rec OUT AS_UTILITY_PVT.ITEM_PROPERTY_REC_TYPE' here if you'd like to pass back item property.
768 X_Return_Status OUT NOCOPY VARCHAR2,
769 X_Msg_Count OUT NOCOPY NUMBER,
770 X_Msg_Data OUT NOCOPY VARCHAR2
771 )
772 IS
773 BEGIN
774
775 -- Initialize message list if p_init_msg_list is set to TRUE.
776 IF FND_API.to_Boolean( p_init_msg_list )
777 THEN
778 FND_MSG_PUB.initialize;
779 END IF;
780
781
782 -- Initialize API return status to SUCCESS
783 x_return_status := FND_API.G_RET_STS_SUCCESS;
784
785 IF (p_timeframe_days is NOT NULL
786 AND p_timeframe_days < 0 ) THEN
787
788 AS_UTILITY_PVT.Set_Message(
789 p_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR,
790 p_msg_name => 'API_INVALID_ID',
791 p_token1 => 'COLUMN',
792 p_token1_value => 'TIMEFRAME_DAYS',
793 p_token2 => 'VALUE',
794 p_token2_value => p_TIMEFRAME_DAYS );
795
796 x_return_status := FND_API.G_RET_STS_ERROR;
797 END IF;
798
799 -- Standard call to get message count and if count is 1, get message info.
800 FND_MSG_PUB.Count_And_Get
801 ( p_count => x_msg_count,
802 p_data => x_msg_data
803 );
804
805 END Validate_TIMEFRAME_DAYS;
806
807
808
809
810 End AML_TIMEFRAME_PVT;