[Home] [Help]
PACKAGE BODY: APPS.AST_GRP_CAMP_PVT
Source
1 PACKAGE BODY ast_grp_camp_PVT as
2 /* $Header: astvgcab.pls 115.6 2002/02/05 17:28:56 pkm ship $ */
3 -- Start of Comments
4 -- Package name : ast_grp_camp_PVT
5 -- Purpose :
6 -- History :
7 -- NOTE :
8 -- End of Comments
9
10
11 G_PKG_NAME CONSTANT VARCHAR2(30):= 'ast_grp_camp_PVT';
12 G_FILE_NAME CONSTANT VARCHAR2(12) := 'astvgcab.pls';
13
14 G_USER_ID NUMBER := FND_GLOBAL.USER_ID;
15 G_LOGIN_ID NUMBER := FND_GLOBAL.CONC_LOGIN_ID;
16
17
18 -- Functions to return initialized variables
19
20 FUNCTION get_grp_Campaign_rec RETURN ast_grp_camp_pvt.grp_camp_rec_type IS
21 l_variable ast_grp_camp_pvt.grp_camp_rec_type := ast_grp_camp_pvt.g_miss_grp_camp_rec;
22 BEGIN
23 return (l_variable);
24 END;
25
26 -- Hint: Primary key needs to be returned.
27 PROCEDURE Create_grp_camp(
28 P_Api_Version_Number IN NUMBER,
29 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
30 P_Commit IN VARCHAR2 := FND_API.G_FALSE,
31 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
32 P_grp_camp_Rec IN grp_camp_Rec_Type := G_MISS_grp_camp_REC,
33 --Hint: Add detail tables as parameter lists if it's master-detail relationship.
34 X_GROUP_CAMPAIGN_ID OUT NUMBER,
35 X_Return_Status OUT VARCHAR2,
36 X_Msg_Count OUT NUMBER,
37 X_Msg_Data OUT VARCHAR2
38 )
39
40 IS
41 l_api_name CONSTANT VARCHAR2(30) := 'Create_grp_camp';
42 l_api_version_number CONSTANT NUMBER := 1.0;
43 l_return_status_full VARCHAR2(1);
44 l_identity_sales_member_rec AS_SALES_MEMBER_PUB.Sales_member_rec_Type;
45 BEGIN
46 -- Standard Start of API savepoint
47 SAVEPOINT CREATE_grp_camp_PVT;
48
49 -- Standard call to check for call compatibility.
50 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
51 p_api_version_number,
52 l_api_name,
53 G_PKG_NAME)
54 THEN
55 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
56 END IF;
57
58
59 -- Initialize message list if p_init_msg_list is set to TRUE.
60 IF FND_API.to_Boolean( p_init_msg_list )
61 THEN
62 FND_MSG_PUB.initialize;
63 END IF;
64
65
66 -- Debug Message
67 JTF_PLSQL_API.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'ast', 'Private API: ' || l_api_name || 'start');
68
69
70 -- Initialize API return status to SUCCESS
71 x_return_status := FND_API.G_RET_STS_SUCCESS;
72
73 --
74 -- API body
75 --
76
77 -- Un-comment the following statements when AS_CALLOUT_PKG is ready.
78 /*
79 -- if profile AS_PRE_CUSTOM_ENABLED is set to 'Y', callout procedure is invoked for customization purpose
80 IF(FND_PROFILE.VALUE('AS_PRE_CUSTOM_ENABLED')='Y')
81 THEN
82 AS_CALLOUT_PKG.Create_grp_camp_BC(
83 p_api_version_number => 1.0,
84 p_init_msg_list => FND_API.G_FALSE,
85 p_commit => FND_API.G_FALSE,
86 p_validation_level => p_validation_level,
87 P_grp_camp_Rec => P_grp_camp_Rec,
88 -- Hint: Add detail tables as parameter lists if it's master-detail relationship.
89 x_return_status => x_return_status,
90 x_msg_count => x_msg_count,
91 x_msg_data => x_msg_data);
92 END IF;
93 */
94 -- ******************************************************************
95 -- Validate Environment
96 -- ******************************************************************
97 IF FND_GLOBAL.User_Id IS NULL
98 THEN
99 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
100 THEN
101 FND_MESSAGE.Set_Name(' + appShortName +', 'UT_CANNOT_GET_PROFILE_VALUE');
102 FND_MESSAGE.Set_Token('PROFILE', 'USER_ID', FALSE);
103 FND_MSG_PUB.ADD;
104 END IF;
105 RAISE FND_API.G_EXC_ERROR;
106 END IF;
107
108
109 /* AS_SALES_ORG_MANAGER_PVT.Get_CurrentUser(
110 p_api_version_number => 1.0
111 ,p_salesforce_id => NULL
112 ,x_return_status => x_return_status
113 ,x_msg_count => x_msg_count
114 ,x_msg_data => x_msg_data
115 ,x_sales_member_rec => l_identity_sales_member_rec);
116
117 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
118 RAISE FND_API.G_EXC_ERROR;
119 END IF;
120 */
121
122 IF ( P_validation_level >= FND_API.G_VALID_LEVEL_FULL)
123 THEN
124 -- Debug message
125 JTF_PLSQL_API.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'ast', 'Private API: Validate_grp_camp');
126
127 -- Invoke validation procedures
128 Validate_grp_camp(
129 p_init_msg_list => FND_API.G_FALSE,
130 p_validation_level => p_validation_level,
131 p_validation_mode => JTF_PLSQL_API.G_CREATE,
132 P_grp_camp_Rec => P_grp_camp_Rec,
133 x_return_status => x_return_status,
134 x_msg_count => x_msg_count,
135 x_msg_data => x_msg_data);
136 END IF;
137
138 IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
139 RAISE FND_API.G_EXC_ERROR;
140 END IF;
141
142 -- Hint: Add corresponding Master-Detail business logic here if necessary.
143
144 -- Debug Message
145 JTF_PLSQL_API.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'ast', 'Private API: Calling create table handler');
146
147 -- Invoke table handler(AST_GRP_CAMPAIGNS_PKG.Insert_Row)
148 AST_GRP_CAMPAIGNS_PKG.Insert_Row(
149 px_GROUP_CAMPAIGN_ID => x_GROUP_CAMPAIGN_ID,
150 p_GROUP_ID => p_grp_camp_rec.GROUP_ID,
151 p_CAMPAIGN_ID => p_grp_camp_rec.CAMPAIGN_ID,
152 p_START_DATE => p_grp_camp_rec.START_DATE,
153 p_END_DATE => p_grp_camp_rec.END_DATE,
154 p_ENABLED_FLAG => p_grp_camp_rec.ENABLED_FLAG,
155 p_LAST_UPDATE_DATE => SYSDATE,
156 p_LAST_UPDATED_BY => G_USER_ID,
157 p_LAST_UPDATE_LOGIN => G_LOGIN_ID,
158 p_CREATED_BY => G_USER_ID,
159 p_CREATION_DATE => p_grp_camp_rec.CREATION_DATE);
160 -- Hint: Primary key should be returned.
161 -- x_GROUP_CAMPAIGN_ID := px_GROUP_CAMPAIGN_ID;
162
163 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
164 RAISE FND_API.G_EXC_ERROR;
165 END IF;
166
167 --
168 -- End of API body
169 --
170
171 -- Standard check for p_commit
172 IF FND_API.to_Boolean( p_commit )
173 THEN
174 COMMIT WORK;
175 END IF;
176
177
178 -- Debug Message
179 JTF_PLSQL_API.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'ast', 'Private API: ' || l_api_name || 'end');
180
181
182 -- Standard call to get message count and if count is 1, get message info.
183 FND_MSG_PUB.Count_And_Get
184 ( p_count => x_msg_count,
185 p_data => x_msg_data
186 );
187
188 -- Un-comment the following statements when AS_CALLOUT_PKG is ready.
189 /*
190 -- if profile AS_POST_CUSTOM_ENABLED is set to 'Y', callout procedure is invoked for customization purpose
191 IF(FND_PROFILE.VALUE('AS_POST_CUSTOM_ENABLED')='Y')
192 THEN
193 AS_CALLOUT_PKG.Create_grp_camp_AC(
194 p_api_version_number => 1.0,
195 p_init_msg_list => FND_API.G_FALSE,
196 p_commit => FND_API.G_FALSE,
197 p_validation_level => p_validation_level,
198 P_grp_camp_Rec => P_grp_camp_Rec,
199 -- Hint: Add detail tables as parameter lists if it's master-detail relationship.
200 x_return_status => x_return_status,
201 x_msg_count => x_msg_count,
202 x_msg_data => x_msg_data);
203 END IF;
204 */
205 EXCEPTION
206 WHEN FND_API.G_EXC_ERROR THEN
207 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
208 P_API_NAME => L_API_NAME
209 ,P_PKG_NAME => G_PKG_NAME
210 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
211 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PVT
212 ,X_MSG_COUNT => X_MSG_COUNT
213 ,X_MSG_DATA => X_MSG_DATA
214 ,X_RETURN_STATUS => X_RETURN_STATUS);
215
216 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
217 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
218 P_API_NAME => L_API_NAME
219 ,P_PKG_NAME => G_PKG_NAME
220 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
221 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PVT
222 ,X_MSG_COUNT => X_MSG_COUNT
223 ,X_MSG_DATA => X_MSG_DATA
224 ,X_RETURN_STATUS => X_RETURN_STATUS);
225
226 WHEN OTHERS THEN
227 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
228 P_API_NAME => L_API_NAME
229 ,P_PKG_NAME => G_PKG_NAME
230 ,P_EXCEPTION_LEVEL => JTF_PLSQL_API.G_EXC_OTHERS
231 ,P_PACKAGE_TYPE => JTF_PLSQL_API.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_grp_camp;
236
237
238 -- Hint: Add corresponding update detail table procedures if it's master-detail relationship.
239 PROCEDURE Update_grp_camp(
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_Identity_Salesforce_Id IN NUMBER := NULL,
245 P_grp_camp_Rec IN grp_camp_Rec_Type,
246 X_Return_Status OUT VARCHAR2,
247 X_Msg_Count OUT NUMBER,
248 X_Msg_Data OUT VARCHAR2
249 )
250
251 IS
252 Cursor C_Get_grp_camp(p_GROUP_CAMPAIGN_ID Number) IS
253 Select rowid,
254 GROUP_CAMPAIGN_ID,
255 GROUP_ID,
256 CAMPAIGN_ID,
257 START_DATE,
258 END_DATE,
259 ENABLED_FLAG,
260 LAST_UPDATE_DATE,
261 LAST_UPDATED_BY,
262 LAST_UPDATE_LOGIN,
263 CREATED_BY,
264 CREATION_DATE
265 From AST_GRP_CAMPAIGNS
266 where group_campaign_id = p_group_campaign_id
267 -- Hint: Developer need to provide Where clause
268 For Update NOWAIT;
269 l_api_name CONSTANT VARCHAR2(30) := 'Update_grp_camp';
270 l_api_version_number CONSTANT NUMBER := 1.0;
271 -- Local Variables
272 l_identity_sales_member_rec AS_SALES_MEMBER_PUB.Sales_member_rec_Type;
273 l_ref_grp_camp_rec ast_grp_camp_PVT.grp_camp_Rec_Type;
274 l_tar_grp_camp_rec ast_grp_camp_PVT.grp_camp_Rec_Type := P_grp_camp_Rec;
275 l_rowid ROWID;
276 BEGIN
277 -- Standard Start of API savepoint
278 SAVEPOINT UPDATE_grp_camp_PVT;
279
280 -- Standard call to check for call compatibility.
281 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
282 p_api_version_number,
283 l_api_name,
284 G_PKG_NAME)
285 THEN
286 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
287 END IF;
288
289
290 -- Initialize message list if p_init_msg_list is set to TRUE.
291 IF FND_API.to_Boolean( p_init_msg_list )
292 THEN
293 FND_MSG_PUB.initialize;
294 END IF;
295
296
297 -- Debug Message
298 JTF_PLSQL_API.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'ast', 'Private API: ' || l_api_name || 'start');
299
300
301 -- Initialize API return status to SUCCESS
302 x_return_status := FND_API.G_RET_STS_SUCCESS;
303
304 --
305 -- Api body
306 --
307 -- Un-comment the following statements when AS_CALLOUT_PKG is ready.
308 /*
309 -- if profile AS_PRE_CUSTOM_ENABLED is set to 'Y', callout procedure is invoked for customization purpose
310 IF(FND_PROFILE.VALUE('AS_PRE_CUSTOM_ENABLED')='Y')
311 THEN
312 AS_CALLOUT_PKG.Update_grp_camp_BU(
313 p_api_version_number => 1.0,
314 p_init_msg_list => FND_API.G_FALSE,
315 p_commit => FND_API.G_FALSE,
316 p_validation_level => p_validation_level,
317 p_identity_salesforce_id => p_identity_salesforce_id,
318 P_grp_camp_Rec => P_grp_camp_Rec,
319 -- Hint: Add detail tables as parameter lists if it's master-detail relationship.
320 x_return_status => x_return_status,
321 x_msg_count => x_msg_count,
322 x_msg_data => x_msg_data);
323 END IF;
324 */
325
326 /* AS_SALES_ORG_MANAGER_PVT.Get_CurrentUser(
327 p_api_version_number => 1.0
328 ,p_salesforce_id => p_identity_salesforce_id
329 ,x_return_status => x_return_status
330 ,x_msg_count => x_msg_count
331 ,x_msg_data => x_msg_data
332 ,x_sales_member_rec => l_identity_sales_member_rec);
333
334 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
335 RAISE FND_API.G_EXC_ERROR;
336 END IF;
337 */
338 -- Debug Message
339 JTF_PLSQL_API.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'ast', 'Private API: - Open Cursor to Select');
340
341
342 Open C_Get_grp_camp( l_tar_grp_camp_rec.GROUP_CAMPAIGN_ID);
343
344 Fetch C_Get_grp_camp into
345 l_rowid,
346 l_ref_grp_camp_rec.GROUP_CAMPAIGN_ID,
347 l_ref_grp_camp_rec.GROUP_ID,
348 l_ref_grp_camp_rec.CAMPAIGN_ID,
349 l_ref_grp_camp_rec.START_DATE,
350 l_ref_grp_camp_rec.END_DATE,
351 l_ref_grp_camp_rec.ENABLED_FLAG,
352 l_ref_grp_camp_rec.LAST_UPDATE_DATE,
353 l_ref_grp_camp_rec.LAST_UPDATED_BY,
354 l_ref_grp_camp_rec.LAST_UPDATE_LOGIN,
355 l_ref_grp_camp_rec.CREATED_BY,
356 l_ref_grp_camp_rec.CREATION_DATE;
357
358 If ( C_Get_grp_camp%NOTFOUND) Then
359 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
360 THEN
361 FND_MESSAGE.Set_Name('ast', 'API_MISSING_UPDATE_TARGET');
362 FND_MESSAGE.Set_Token ('INFO', 'grp_camp', FALSE);
363 FND_MSG_PUB.Add;
364 END IF;
365 raise FND_API.G_EXC_ERROR;
366 END IF;
367 -- Debug Message
368 JTF_PLSQL_API.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'ast', 'Private API: - Close Cursor');
369 Close C_Get_grp_camp;
370
371
372
373 If (l_tar_grp_camp_rec.last_update_date is NULL or
374 l_tar_grp_camp_rec.last_update_date = FND_API.G_MISS_Date ) Then
375 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
376 THEN
377 FND_MESSAGE.Set_Name('ast', 'API_MISSING_ID');
378 FND_MESSAGE.Set_Token('COLUMN', 'Last_Update_Date', FALSE);
379 FND_MSG_PUB.ADD;
380 END IF;
381 raise FND_API.G_EXC_ERROR;
382 End if;
383 /* by jypark
384 -- Check Whether record has been changed by someone else
385 If (l_tar_grp_camp_rec.last_update_date <> l_ref_grp_camp_rec.last_update_date) Then
386 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
387 THEN
388 FND_MESSAGE.Set_Name('ast', 'API_RECORD_CHANGED');
389 FND_MESSAGE.Set_Token('INFO', 'grp_camp', FALSE);
390 FND_MSG_PUB.ADD;
391 END IF;
392 raise FND_API.G_EXC_ERROR;
393 End if;
394 */
395 IF ( P_validation_level >= FND_API.G_VALID_LEVEL_FULL)
396 THEN
397 -- Debug message
398 JTF_PLSQL_API.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'ast', 'Private API: Validate_grp_camp');
399
400 -- Invoke validation procedures
401 Validate_grp_camp(
402 p_init_msg_list => FND_API.G_FALSE,
403 p_validation_level => p_validation_level,
404 p_validation_mode => JTF_PLSQL_API.G_UPDATE,
405 P_grp_camp_Rec => P_grp_camp_Rec,
406 x_return_status => x_return_status,
407 x_msg_count => x_msg_count,
408 x_msg_data => x_msg_data);
409 END IF;
410
411 IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
412 RAISE FND_API.G_EXC_ERROR;
413 END IF;
414
415 -- Hint: Add corresponding Master-Detail business logic here if necessary.
416
417 -- Debug Message
418 JTF_PLSQL_API.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'ast', 'Private API: Calling update table handler');
419
420 -- Invoke table handler(AST_GRP_CAMPAIGNS_PKG.Update_Row)
421 AST_GRP_CAMPAIGNS_PKG.Update_Row(
422 p_GROUP_CAMPAIGN_ID => p_grp_camp_rec.GROUP_CAMPAIGN_ID,
423 p_GROUP_ID => p_grp_camp_rec.GROUP_ID,
424 p_CAMPAIGN_ID => p_grp_camp_rec.CAMPAIGN_ID,
425 p_START_DATE => p_grp_camp_rec.START_DATE,
426 p_END_DATE => p_grp_camp_rec.END_DATE,
427 p_ENABLED_FLAG => p_grp_camp_rec.ENABLED_FLAG,
428 p_LAST_UPDATE_DATE => SYSDATE,
429 p_LAST_UPDATED_BY => G_USER_ID,
430 p_LAST_UPDATE_LOGIN => G_LOGIN_ID,
431 p_CREATED_BY => G_USER_ID,
432 p_CREATION_DATE => p_grp_camp_rec.CREATION_DATE);
433 --
434 -- End of API body.
435 --
436
437 -- Standard check for p_commit
438 IF FND_API.to_Boolean( p_commit )
439 THEN
440 COMMIT WORK;
441 END IF;
442
443
444 -- Debug Message
445 JTF_PLSQL_API.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'ast', 'Private API: ' || l_api_name || 'end');
446
447
448 -- Standard call to get message count and if count is 1, get message info.
449 FND_MSG_PUB.Count_And_Get
450 ( p_count => x_msg_count,
451 p_data => x_msg_data
452 );
453
454 -- Un-comment the following statements when AS_CALLOUT_PKG is ready.
455 /*
456 -- if profile AS_POST_CUSTOM_ENABLED is set to 'Y', callout procedure is invoked for customization purpose
457 IF(FND_PROFILE.VALUE('AS_POST_CUSTOM_ENABLED')='Y')
458 THEN
459 AS_CALLOUT_PKG.Update_grp_camp_AU(
460 p_api_version_number => 1.0,
461 p_init_msg_list => FND_API.G_FALSE,
462 p_commit => FND_API.G_FALSE,
463 p_validation_level => p_validation_level,
464 p_identity_salesforce_id => p_identity_salesforce_id,
465 P_grp_camp_Rec => P_grp_camp_Rec,
466 -- Hint: Add detail tables as parameter lists if it's master-detail relationship.
467 x_return_status => x_return_status,
468 x_msg_count => x_msg_count,
469 x_msg_data => x_msg_data);
470 END IF;
471 */
472 EXCEPTION
473 WHEN FND_API.G_EXC_ERROR THEN
474 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
475 P_API_NAME => L_API_NAME
476 ,P_PKG_NAME => G_PKG_NAME
477 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
478 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PVT
479 ,X_MSG_COUNT => X_MSG_COUNT
480 ,X_MSG_DATA => X_MSG_DATA
481 ,X_RETURN_STATUS => X_RETURN_STATUS);
482
483 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
484 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
485 P_API_NAME => L_API_NAME
486 ,P_PKG_NAME => G_PKG_NAME
487 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
488 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PVT
489 ,X_MSG_COUNT => X_MSG_COUNT
490 ,X_MSG_DATA => X_MSG_DATA
491 ,X_RETURN_STATUS => X_RETURN_STATUS);
492
493 WHEN OTHERS THEN
494 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
495 P_API_NAME => L_API_NAME
496 ,P_PKG_NAME => G_PKG_NAME
497 ,P_EXCEPTION_LEVEL => JTF_PLSQL_API.G_EXC_OTHERS
498 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PVT
499 ,X_MSG_COUNT => X_MSG_COUNT
500 ,X_MSG_DATA => X_MSG_DATA
501 ,X_RETURN_STATUS => X_RETURN_STATUS);
502 End Update_grp_camp;
503
504
505 -- Hint: Add corresponding delete detail table procedures if it's master-detail relationship.
506 -- The Master delete procedure may not be needed depends on different business requirements.
507 PROCEDURE Delete_grp_camp(
508 P_Api_Version_Number IN NUMBER,
509 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
510 P_Commit IN VARCHAR2 := FND_API.G_FALSE,
511 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
512 P_identity_salesforce_id IN NUMBER := NULL,
513 P_grp_camp_Rec IN grp_camp_Rec_Type,
514 X_Return_Status OUT VARCHAR2,
515 X_Msg_Count OUT NUMBER,
516 X_Msg_Data OUT VARCHAR2
517 )
518
519 IS
520 l_api_name CONSTANT VARCHAR2(30) := 'Delete_grp_camp';
521 l_api_version_number CONSTANT NUMBER := 1.0;
522 l_identity_sales_member_rec AS_SALES_MEMBER_PUB.Sales_member_rec_Type;
523 BEGIN
524 -- Standard Start of API savepoint
525 SAVEPOINT DELETE_grp_camp_PVT;
526
527 -- Standard call to check for call compatibility.
528 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
529 p_api_version_number,
530 l_api_name,
531 G_PKG_NAME)
532 THEN
533 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
534 END IF;
535
536
537 -- Initialize message list if p_init_msg_list is set to TRUE.
538 IF FND_API.to_Boolean( p_init_msg_list )
539 THEN
540 FND_MSG_PUB.initialize;
541 END IF;
542
543
544 -- Debug Message
545 JTF_PLSQL_API.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'ast', 'Private API: ' || l_api_name || 'start');
546
547
548 -- Initialize API return status to SUCCESS
549 x_return_status := FND_API.G_RET_STS_SUCCESS;
550
551 --
552 -- Api body
553 --
554 -- Un-comment the following statements when AS_CALLOUT_PKG is ready.
555 /*
556 -- if profile AS_PRE_CUSTOM_ENABLED is set to 'Y', callout procedure is invoked for customization purpose
557 IF(FND_PROFILE.VALUE('AS_PRE_CUSTOM_ENABLED')='Y')
558 THEN
559 AS_CALLOUT_PKG.Delete_grp_camp_BD(
560 p_api_version_number => 1.0,
561 p_init_msg_list => FND_API.G_FALSE,
562 p_commit => FND_API.G_FALSE,
563 p_validation_level => p_validation_level,
564 p_identity_salesforce_id => p_identity_salesforce_id,
565 P_grp_camp_Rec => P_grp_camp_Rec,
566 -- Hint: Add detail tables as parameter lists if it's master-detail relationship.
567 x_return_status => x_return_status,
568 x_msg_count => x_msg_count,
569 x_msg_data => x_msg_data);
570 END IF;
571 */
572
573 /* AS_SALES_ORG_MANAGER_PVT.Get_CurrentUser(
574 p_api_version_number => 1.0
575 ,p_salesforce_id => p_identity_salesforce_id
576 ,x_return_status => x_return_status
577 ,x_msg_count => x_msg_count
578 ,x_msg_data => x_msg_data
579 ,x_sales_member_rec => l_identity_sales_member_rec);
580
581 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
582 RAISE FND_API.G_EXC_ERROR;
583 END IF;
584 */
585 -- Debug Message
586 JTF_PLSQL_API.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'ast', 'Private API: Calling delete table handler');
587
588
589
590 -- Invoke table handler(AST_GRP_CAMPAIGNS_PKG.Delete_Row)
591 AST_GRP_CAMPAIGNS_PKG.Delete_Row(
592 p_GROUP_CAMPAIGN_ID => p_grp_camp_rec.GROUP_CAMPAIGN_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 JTF_PLSQL_API.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'ast', 'Private API: ' || l_api_name || 'end');
606
607
608 -- Standard call to get message count and if count is 1, get message info.
609 FND_MSG_PUB.Count_And_Get
610 ( p_count => x_msg_count,
611 p_data => x_msg_data
612 );
613
614 -- Un-comment the following statements when AS_CALLOUT_PKG is ready.
615 /*
616 -- if profile AS_POST_CUSTOM_ENABLED is set to 'Y', callout procedure is invoked for customization purpose
617 IF(FND_PROFILE.VALUE('AS_POST_CUSTOM_ENABLED')='Y')
618 THEN
619 AS_CALLOUT_PKG.Delete_grp_camp_AD(
620 p_api_version_number => 1.0,
621 p_init_msg_list => FND_API.G_FALSE,
622 p_commit => FND_API.G_FALSE,
623 p_validation_level => p_validation_level,
624 p_identity_salesforce_id => p_identity_salesforce_id,
625 P_grp_camp_Rec => P_grp_camp_Rec,
626 -- Hint: Add detail tables as parameter lists if it's master-detail relationship.
627 x_return_status => x_return_status,
628 x_msg_count => x_msg_count,
629 x_msg_data => x_msg_data);
630 END IF;
631 */
632 EXCEPTION
633 WHEN FND_API.G_EXC_ERROR THEN
634 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
635 P_API_NAME => L_API_NAME
636 ,P_PKG_NAME => G_PKG_NAME
637 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
638 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PVT
639 ,X_MSG_COUNT => X_MSG_COUNT
640 ,X_MSG_DATA => X_MSG_DATA
641 ,X_RETURN_STATUS => X_RETURN_STATUS);
642
643 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
644 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
645 P_API_NAME => L_API_NAME
646 ,P_PKG_NAME => G_PKG_NAME
647 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
648 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PVT
649 ,X_MSG_COUNT => X_MSG_COUNT
650 ,X_MSG_DATA => X_MSG_DATA
651 ,X_RETURN_STATUS => X_RETURN_STATUS);
652
653 WHEN OTHERS THEN
654 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
655 P_API_NAME => L_API_NAME
656 ,P_PKG_NAME => G_PKG_NAME
657 ,P_EXCEPTION_LEVEL => JTF_PLSQL_API.G_EXC_OTHERS
658 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PVT
659 ,X_MSG_COUNT => X_MSG_COUNT
660 ,X_MSG_DATA => X_MSG_DATA
661 ,X_RETURN_STATUS => X_RETURN_STATUS);
662 End Delete_grp_camp;
663
664
665 -- This procudure defines the columns for the Dynamic SQL.
666 PROCEDURE Define_Columns(
667 P_grp_camp_Rec IN ast_grp_camp_PUB.grp_camp_Rec_Type,
668 p_cur_get_grp_camp IN NUMBER
669 )
670 IS
671 BEGIN
672 -- Debug Message
673 JTF_PLSQL_API.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'ast', 'Private API: Define Columns Begins');
674
675 -- define all columns for AST_GRP_TEST view
676 dbms_sql.define_column(p_cur_get_grp_camp, 1, P_grp_camp_Rec.GROUP_CAMPAIGN_ID);
677 dbms_sql.define_column(p_cur_get_grp_camp, 2, P_grp_camp_Rec.GROUP_ID);
678 dbms_sql.define_column(p_cur_get_grp_camp, 3, P_grp_camp_Rec.CAMPAIGN_ID);
679 dbms_sql.define_column(p_cur_get_grp_camp, 4, P_grp_camp_Rec.START_DATE);
680 dbms_sql.define_column(p_cur_get_grp_camp, 5, P_grp_camp_Rec.END_DATE);
681 dbms_sql.define_column(p_cur_get_grp_camp, 6, P_grp_camp_Rec.ENABLED_FLAG, 1);
682
683 -- Debug Message
684 JTF_PLSQL_API.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'ast', 'Private API: Define Columns Ends');
685 END Define_Columns;
686
687 -- This procudure gets column values by the Dynamic SQL.
688 PROCEDURE Get_Column_Values(
689 p_cur_get_grp_camp IN NUMBER,
690 X_grp_camp_Rec OUT ast_grp_camp_PUB.grp_camp_Rec_Type
691 )
692 IS
693 BEGIN
694 -- Debug Message
695 JTF_PLSQL_API.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'ast', 'Private API: Get Column Values Begins');
696
697 -- get all column values for AST_GRP_TEST table
698 dbms_sql.column_value(p_cur_get_grp_camp, 1, X_grp_camp_Rec.GROUP_CAMPAIGN_ID);
699 dbms_sql.column_value(p_cur_get_grp_camp, 2, X_grp_camp_Rec.GROUP_ID);
700 dbms_sql.column_value(p_cur_get_grp_camp, 3, X_grp_camp_Rec.CAMPAIGN_ID);
701 dbms_sql.column_value(p_cur_get_grp_camp, 4, X_grp_camp_Rec.START_DATE);
702 dbms_sql.column_value(p_cur_get_grp_camp, 5, X_grp_camp_Rec.END_DATE);
703 dbms_sql.column_value(p_cur_get_grp_camp, 6, X_grp_camp_Rec.ENABLED_FLAG);
704
705 -- Debug Message
706 JTF_PLSQL_API.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'ast', 'Private API: Get Column Values Ends');
707 END Get_Column_Values;
708
709 PROCEDURE Gen_grp_camp_order_cl(
710 p_order_by_rec IN ast_grp_camp_PUB.grp_camp_sort_rec_type,
711 x_order_by_cl OUT VARCHAR2,
712 x_return_status OUT VARCHAR2,
713 x_msg_count OUT NUMBER,
714 x_msg_data OUT VARCHAR2
715 )
716 IS
717 l_order_by_cl VARCHAR2(1000) := NULL;
718 l_util_order_by_tbl JTF_PLSQL_API.Util_order_by_tbl_type;
719 BEGIN
720 -- Debug Message
721 JTF_PLSQL_API.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'ast', 'Private API: Generate Order by Begins');
722
723 -- Hint: Developer should add more statements according to ast_sort_rec_type
724 -- Ex:
725 -- l_util_order_by_tbl(1).col_choice := p_order_by_rec.customer_name;
726 -- l_util_order_by_tbl(1).col_name := 'Customer_Name';
727
728 -- Debug Message
729 JTF_PLSQL_API.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'ast', 'Invoke JTF_PLSQL_API.Translate_OrderBy');
730
731 JTF_PLSQL_API.Translate_OrderBy(
732 p_api_version_number => 1.0
733 ,p_init_msg_list => FND_API.G_FALSE
734 ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
735 ,p_order_by_tbl => l_util_order_by_tbl
736 ,x_order_by_clause => l_order_by_cl
737 ,x_return_status => x_return_status
738 ,x_msg_count => x_msg_count
739 ,x_msg_data => x_msg_data);
740
741 IF(l_order_by_cl IS NOT NULL) THEN
742 x_order_by_cl := 'order by' || l_order_by_cl;
743 ELSE
744 x_order_by_cl := NULL;
745 END IF;
746
747 -- Debug Message
748 JTF_PLSQL_API.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'ast', 'Private API: Generate Order by Ends');
749 END Gen_grp_camp_order_cl;
750
751 -- This procedure bind the variables for the Dynamic SQL
752 PROCEDURE Bind(
753 P_grp_camp_Rec IN ast_grp_camp_PUB.grp_camp_Rec_Type,
754 -- Hint: Add more binding variables here
755 p_cur_get_grp_camp IN NUMBER
756 )
757 IS
758 BEGIN
759 -- Bind variables
760 -- Only those that are not NULL
761 -- Debug Message
762 JTF_PLSQL_API.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'ast', 'Private API: Bind Variables Begins');
763
764 -- The following example applies to all columns,
765 -- developers can copy and paste them.
766 IF( (P_grp_camp_Rec.GROUP_CAMPAIGN_ID IS NOT NULL) AND (P_grp_camp_Rec.GROUP_CAMPAIGN_ID <> FND_API.G_MISS_NUM) )
767 THEN
768 DBMS_SQL.BIND_VARIABLE(p_cur_get_grp_camp, ':p_GROUP_CAMPAIGN_ID', P_grp_camp_Rec.GROUP_CAMPAIGN_ID);
769 END IF;
770
771 -- Debug Message
772 JTF_PLSQL_API.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'ast', 'Private API: Bind Variables Ends');
773 END Bind;
774
775 PROCEDURE Gen_Select(
776 x_select_cl OUT VARCHAR2
777 )
778 IS
779 BEGIN
780 -- Debug Message
781 JTF_PLSQL_API.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'ast', 'Private API: Generate Select Begins');
782
783 x_select_cl := 'Select ' ||
784 'AST_GRP_TEST.GROUP_CAMPAIGN_ID,' ||
785 'AST_GRP_TEST.GROUP_ID,' ||
786 'AST_GRP_TEST.CAMPAIGN_ID,' ||
787 'AST_GRP_TEST.START_DATE,' ||
788 'AST_GRP_TEST.END_DATE,' ||
789 'AST_GRP_TEST.ENABLED_FLAG,' ||
790 'AST_GRP_TEST.LAST_UPDATE_DATE,' ||
791 'AST_GRP_TEST.LAST_UPDATED_BY,' ||
792 'AST_GRP_TEST.LAST_UPDATE_LOGIN,' ||
793 'AST_GRP_TEST.CREATED_BY,' ||
794 'AST_GRP_TEST.CREATION_DATE,' ||
795 'from AST_GRP_TEST';
796 -- Debug Message
797 JTF_PLSQL_API.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'ast', 'Private API: Generate Select Ends');
798
799 END Gen_Select;
800
801 PROCEDURE Gen_grp_camp_Where(
802 P_grp_camp_Rec IN ast_grp_camp_PUB.grp_camp_Rec_Type,
803 x_grp_camp_where OUT VARCHAR2
804 )
805 IS
806 -- cursors to check if wildcard values '%' and '_' have been passed
807 -- as item values
808 CURSOR c_chk_str1(p_rec_item VARCHAR2) IS
809 SELECT INSTR(p_rec_item, '%', 1, 1)
810 FROM DUAL;
811 CURSOR c_chk_str2(p_rec_item VARCHAR2) IS
812 SELECT INSTR(p_rec_item, '_', 1, 1)
813 FROM DUAL;
814
815 -- return values from cursors
816 str_csr1 NUMBER;
817 str_csr2 NUMBER;
818 l_operator VARCHAR2(10);
819 BEGIN
820 -- Debug Message
821 JTF_PLSQL_API.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'ast', 'Private API: Generate Where Begins');
822
823 -- There are three examples for each kind of datatype:
824 -- NUMBER, DATE, VARCHAR2.
825 -- Developer can copy and paste the following codes for your own record.
826
827 -- example for NUMBER datatype
828 IF( (P_grp_camp_Rec.GROUP_CAMPAIGN_ID IS NOT NULL) AND (P_grp_camp_Rec.GROUP_CAMPAIGN_ID <> FND_API.G_MISS_NUM) )
829 THEN
830 IF(x_grp_camp_where IS NULL) THEN
831 x_grp_camp_where := 'Where';
832 ELSE
833 x_grp_camp_where := x_grp_camp_where || ' AND ';
834 END IF;
835 x_grp_camp_where := x_grp_camp_where || 'P_grp_camp_Rec.GROUP_CAMPAIGN_ID = :p_GROUP_CAMPAIGN_ID';
836 END IF;
837
838 -- example for DATE datatype
839 IF( (P_grp_camp_Rec.START_DATE IS NOT NULL) AND (P_grp_camp_Rec.START_DATE <> FND_API.G_MISS_DATE) )
840 THEN
841 -- check if item value contains '%' wildcard
842 OPEN c_chk_str1(P_grp_camp_Rec.START_DATE);
843 FETCH c_chk_str1 INTO str_csr1;
844 CLOSE c_chk_str1;
845
846 IF(str_csr1 <> 0) THEN
847 l_operator := ' LIKE ';
848 ELSE
849 l_operator := ' = ';
850 END IF;
851
852 -- check if item value contains '_' wildcard
853 OPEN c_chk_str2(P_grp_camp_Rec.START_DATE);
854 FETCH c_chk_str2 INTO str_csr2;
855 CLOSE c_chk_str2;
856
857 IF(str_csr2 <> 0) THEN
858 l_operator := ' LIKE ';
859 ELSE
860 l_operator := ' = ';
861 END IF;
862
863 IF(x_grp_camp_where IS NULL) THEN
864 x_grp_camp_where := 'Where ';
865 ELSE
866 x_grp_camp_where := x_grp_camp_where || ' AND ';
867 END IF;
868 x_grp_camp_where := x_grp_camp_where || 'P_grp_camp_Rec.START_DATE ' || l_operator || ' :p_START_DATE';
869 END IF;
870
871 -- example for VARCHAR2 datatype
872 IF( (P_grp_camp_Rec.ENABLED_FLAG IS NOT NULL) AND (P_grp_camp_Rec.ENABLED_FLAG <> FND_API.G_MISS_CHAR) )
873 THEN
874 -- check if item value contains '%' wildcard
875 OPEN c_chk_str1(P_grp_camp_Rec.ENABLED_FLAG);
876 FETCH c_chk_str1 INTO str_csr1;
877 CLOSE c_chk_str1;
878
879 IF(str_csr1 <> 0) THEN
880 l_operator := ' LIKE ';
881 ELSE
882 l_operator := ' = ';
883 END IF;
884
885 -- check if item value contains '_' wildcard
886 OPEN c_chk_str2(P_grp_camp_Rec.ENABLED_FLAG);
887 FETCH c_chk_str2 INTO str_csr2;
888 CLOSE c_chk_str2;
889
890 IF(str_csr2 <> 0) THEN
891 l_operator := ' LIKE ';
892 ELSE
893 l_operator := ' = ';
894 END IF;
895
896 IF(x_grp_camp_where IS NULL) THEN
897 x_grp_camp_where := 'Where ';
898 ELSE
899 x_grp_camp_where := x_grp_camp_where || ' AND ';
900 END IF;
901 x_grp_camp_where := x_grp_camp_where || 'P_grp_camp_Rec.ENABLED_FLAG ' || l_operator || ' :p_ENABLED_FLAG';
902 END IF;
903
904 -- Add more IF statements for each column below
905
906 -- Debug Message
907 JTF_PLSQL_API.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'ast', 'Private API: Generate Where Ends');
908
909 END Gen_grp_camp_Where;
910
911 PROCEDURE Get_grp_camp(
912 P_Api_Version_Number IN NUMBER,
913 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
914 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
915 P_identity_salesforce_id IN NUMBER := NULL,
916 P_grp_camp_Rec IN ast_grp_camp_PUB.grp_camp_Rec_Type,
917 -- Hint: Add list of bind variables here
918 p_rec_requested IN NUMBER := G_DEFAULT_NUM_REC_FETCH,
919 p_start_rec_prt IN NUMBER := 1,
920 p_return_tot_count IN NUMBER := FND_API.G_FALSE,
921 -- Hint: user defined record type
922 p_order_by_rec IN ast_grp_camp_PUB.grp_camp_sort_rec_type,
923 x_return_status OUT VARCHAR2,
924 x_msg_count OUT NUMBER,
925 x_msg_data OUT VARCHAR2,
926 X_grp_camp_Tbl OUT ast_grp_camp_PUB.grp_camp_Tbl_Type,
927 x_returned_rec_count OUT NUMBER,
928 x_next_rec_ptr OUT NUMBER,
929 x_tot_rec_count OUT NUMBER
930 -- other optional parameters
931 -- x_tot_rec_amount OUT NUMBER
932 )
933
934 IS
935 l_api_name CONSTANT VARCHAR2(30) := 'Get_grp_camp';
936 l_api_version_number CONSTANT NUMBER := 1.0;
937
938 -- Local identity variables
939 l_identity_sales_member_rec AS_SALES_MEMBER_PUB.Sales_member_rec_Type;
940
941 -- Local record counters
942 l_returned_rec_count NUMBER := 0; -- number of records returned in x_X_grp_camp_Rec
943 l_next_record_ptr NUMBER := 1;
944 l_ignore NUMBER;
945
946 -- total number of records accessable by caller
947 l_tot_rec_count NUMBER := 0;
948 l_tot_rec_amount NUMBER := 0;
949
950 -- Status local variables
951 l_return_status VARCHAR2(1); -- Return value from procedures
952 l_return_status_full VARCHAR2(1); -- Calculated return status from
953
954 -- Dynamic SQL statement elements
955 l_cur_get_grp_camp NUMBER;
956 l_select_cl VARCHAR2(2000) := '';
957 l_order_by_cl VARCHAR2(2000);
958 l_grp_camp_where VARCHAR2(2000) := '';
959
960 -- For flex field query
961 l_flex_where_tbl_type AS_FOUNDATION_PVT.flex_where_tbl_type;
962 l_flex_where VARCHAR2(2000) := NULL;
963 l_counter NUMBER;
964
965 -- Local scratch record
966 l_grp_camp_rec ast_grp_camp_PUB.grp_camp_Rec_Type;
967 l_crit_grp_camp_rec ast_grp_camp_PUB.grp_camp_Rec_Type;
968 BEGIN
969 -- Standard Start of API savepoint
970 SAVEPOINT GET_grp_camp_PVT;
971
972 -- Standard call to check for call compatibility.
973 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
974 p_api_version_number,
975 l_api_name,
976 G_PKG_NAME)
977 THEN
978 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
979 END IF;
980
981
982 -- Initialize message list if p_init_msg_list is set to TRUE.
983 IF FND_API.to_Boolean( p_init_msg_list )
984 THEN
985 FND_MSG_PUB.initialize;
986 END IF;
987
988
989 -- Debug Message
990 JTF_PLSQL_API.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'ast', 'Private API: ' || l_api_name || 'start');
991
992
993 -- Initialize API return status to SUCCESS
994 x_return_status := FND_API.G_RET_STS_SUCCESS;
995
996 --
997 -- Api body
998 --
999
1000 /* AS_SALES_ORG_MANAGER_PVT.Get_CurrentUser(
1001 p_api_version_number => 1.0
1002 ,p_salesforce_id => p_identity_salesforce_id
1003 ,x_return_status => x_return_status
1004 ,x_msg_count => x_msg_count
1005 ,x_msg_data => x_msg_data
1006 ,x_sales_member_rec => l_identity_sales_member_rec);
1007
1008 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1009 RAISE FND_API.G_EXC_ERROR;
1010 END IF;
1011 */
1012 -- *************************************************
1013 -- Generate Dynamic SQL based on criteria passed in.
1014 -- Doing this for performance. Indexes are disabled when using NVL within static SQL statement.
1015 -- Ignore condition when criteria is NULL
1016 -- Debug Message
1017 JTF_PLSQL_API.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'ast', 'Private API: Generating Dsql');
1018 -- Generate Select clause and From clause
1019 -- Hint: Developer should modify Gen_Select procedure.
1020 Gen_Select(l_select_cl);
1021
1022 -- Hint: Developer should modify and implement Gen_Where precedure.
1023 Gen_grp_camp_Where(l_crit_grp_camp_rec, l_grp_camp_where);
1024
1025 -- Generate Where clause for flex fields
1026 -- Hint: Developer can use table/view alias in the From clause generated in Gen_Select procedure
1027 /*
1028 FOR l_counter IN 1..15 LOOP
1029 l_flex_where_tbl_type(l_counter).name := 'AST_GRP_TEST.attribute' || l_counter;
1030 END LOOP;
1031
1032 l_flex_where_tbl_type(16).name := 'AST_GRP_TEST.attribute_category';
1033 l_flex_where_tbl_type(1).value := P_grp_camp_Rec.attribute1;
1034 l_flex_where_tbl_type(2).value := P_grp_camp_Rec.attribute2;
1035 l_flex_where_tbl_type(3).value := P_grp_camp_Rec.attribute3;
1036 l_flex_where_tbl_type(4).value := P_grp_camp_Rec.attribute4;
1037 l_flex_where_tbl_type(5).value := P_grp_camp_Rec.attribute5;
1038 l_flex_where_tbl_type(6).value := P_grp_camp_Rec.attribute6;
1039 l_flex_where_tbl_type(7).value := P_grp_camp_Rec.attribute7;
1040 l_flex_where_tbl_type(8).value := P_grp_camp_Rec.attribute8;
1041 l_flex_where_tbl_type(9).value := P_grp_camp_Rec.attribute9;
1042 l_flex_where_tbl_type(10).value := P_grp_camp_Rec.attribute10;
1043 l_flex_where_tbl_type(11).value := P_grp_camp_Rec.attribute11;
1044 l_flex_where_tbl_type(12).value := P_grp_camp_Rec.attribute12;
1045 l_flex_where_tbl_type(13).value := P_grp_camp_Rec.attribute13;
1046 l_flex_where_tbl_type(14).value := P_grp_camp_Rec.attribute14;
1047 l_flex_where_tbl_type(15).value := P_grp_camp_Rec.attribute15;
1048 l_flex_where_tbl_type(16).value := P_grp_camp_Rec.attribute_category;
1049
1050 AS_FOUNDATION_PVT.Gen_Flexfield_Where(
1051 p_flex_where_tbl_type => l_flex_where_tbl_type,
1052 x_flex_where_clause => l_flex_where);
1053 */
1054 -- Hint: if master/detail relationship, generate Where clause for lines level criteria
1055 -- Generate order by clause
1056 Gen_grp_camp_order_cl(p_order_by_rec, l_order_by_cl, l_return_status, x_msg_count, x_msg_data);
1057
1058 -- Debug Message
1059 JTF_PLSQL_API.Debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'ast', 'Private API: Open and Parse Cursor');
1060
1061 l_cur_get_grp_camp := dbms_sql.open_cursor;
1062
1063 -- Hint: concatenate all where clause (include flex field/line level if any applies)
1064 -- dbms_sql.parse(l_cur_get_grp_camp, l_select_cl || l_head_where || l_flex_where || l_lines_where
1065 -- || l_steam_where || l_order_by_cl, dbms_sql.native);
1066
1067 -- Hint: Developer should implement Bind Variables procedure according to bind variables in the parameter list
1068 -- Bind(l_crit_grp_camp_rec, l_crit_exp_purchase_rec, p_start_date, p_end_date,
1069 -- p_crit_exp_salesforce_id, p_crit_ptr_salesforce_id,
1070 -- p_crit_salesgroup_id, p_crit_ptr_manager_person_id,
1071 -- p_win_prob_ceiling, p_win_prob_floor,
1072 -- p_total_amt_ceiling, p_total_amt_floor,
1073 -- l_cur_get_grp_camp);
1074
1075 -- Bind flexfield variables
1076 AS_FOUNDATION_PVT.Bind_Flexfield_Where(
1077 p_cursor_id => l_cur_get_grp_camp,
1078 p_flex_where_tbl_type => l_flex_where_tbl_type);
1079
1080 -- Define all Select Columns
1081 Define_Columns(l_crit_grp_camp_rec, l_cur_get_grp_camp);
1082
1083 -- Execute
1084 -- Debug Message
1085 JTF_PLSQL_API.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'ast', 'Private API: Execute Dsql');
1086
1087 l_ignore := dbms_sql.execute(l_cur_get_grp_camp);
1088
1089 -- Debug Message
1090 JTF_PLSQL_API.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'ast', 'Private API: Fetch Results');
1091
1092 -- This loop is here to avoid calling a function in the main
1093 -- cursor. Basically, calling this function seems to disable
1094 -- index, but verification is needed. This is a good
1095 -- place to optimize the code if required.
1096
1097 LOOP
1098 -- 1. There are more rows in the cursor.
1099 -- 2. User does not care about total records, and we need to return more.
1100 -- 3. Or user cares about total number of records.
1101 IF((dbms_sql.fetch_rows(l_cur_get_grp_camp)>0) AND ((p_return_tot_count = FND_API.G_TRUE)
1102 OR (l_returned_rec_count<p_rec_requested) OR (p_rec_requested=FND_API.G_MISS_NUM)))
1103 THEN
1104 -- Debug Message
1105 JTF_PLSQL_API.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'ast', 'Private API: found');
1106
1107 -- Hint: Developer need to implement this part
1108 -- dbms_sql.column_value(l_cur_get_opp, 1, l_opp_rec.lead_id);
1109 -- dbms_sql.column_value(l_cur_get_opp, 7, l_opp_rec.customer_id);
1110 -- dbms_sql.column_value(l_cur_get_opp, 8, l_opp_rec.address_id);
1111
1112 -- Hint: Check access for this record (e.x. AS_ACCESS_PVT.Has_OpportunityAccess)
1113 -- Return this particular record if
1114 -- 1. The caller has access to record.
1115 -- 2. The number of records returned < number of records caller requested in this run.
1116 -- 3. The record comes AFTER or Equal to the start index the caller requested.
1117
1118 -- Developer should check whether there is access privilege here
1119 -- IF(l_grp_camp_rec.member_access <> 'N' OR l_grp_camp_rec.member_role <> 'N') THEN
1120 Get_Column_Values(l_cur_get_grp_camp, l_grp_camp_rec);
1121 l_tot_rec_count := l_tot_rec_count + 1;
1122 IF(l_returned_rec_count < p_rec_requested) AND (l_tot_rec_count >= p_start_rec_prt) THEN
1123 l_returned_rec_count := l_returned_rec_count + 1;
1124 -- insert into resultant tables
1125 X_grp_camp_Tbl(l_returned_rec_count) := l_grp_camp_rec;
1126 END IF;
1127 -- END IF;
1128 ELSE
1129 EXIT;
1130 END IF;
1131 END LOOP;
1132 --
1133 -- End of API body
1134 --
1135
1136 -- Debug Message
1137 JTF_PLSQL_API.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'ast', 'Private API: ' || l_api_name || 'end');
1138
1139
1140 -- Standard call to get message count and if count is 1, get message info.
1141 FND_MSG_PUB.Count_And_Get
1142 ( p_count => x_msg_count,
1143 p_data => x_msg_data
1144 );
1145
1146 EXCEPTION
1147 WHEN FND_API.G_EXC_ERROR THEN
1148 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
1149 P_API_NAME => L_API_NAME
1150 ,P_PKG_NAME => G_PKG_NAME
1151 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
1152 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PVT
1153 ,X_MSG_COUNT => X_MSG_COUNT
1154 ,X_MSG_DATA => X_MSG_DATA
1155 ,X_RETURN_STATUS => X_RETURN_STATUS);
1156
1157 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1158 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
1159 P_API_NAME => L_API_NAME
1160 ,P_PKG_NAME => G_PKG_NAME
1161 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
1162 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PVT
1163 ,X_MSG_COUNT => X_MSG_COUNT
1164 ,X_MSG_DATA => X_MSG_DATA
1165 ,X_RETURN_STATUS => X_RETURN_STATUS);
1166
1167 WHEN OTHERS THEN
1168 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
1169 P_API_NAME => L_API_NAME
1170 ,P_PKG_NAME => G_PKG_NAME
1171 ,P_EXCEPTION_LEVEL => JTF_PLSQL_API.G_EXC_OTHERS
1172 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PVT
1173 ,X_MSG_COUNT => X_MSG_COUNT
1174 ,X_MSG_DATA => X_MSG_DATA
1175 ,X_RETURN_STATUS => X_RETURN_STATUS);
1176 End Get_grp_camp;
1177
1178
1179 -- Item-level validation procedures
1180 PROCEDURE Validate_GROUP_CAMPAIGN_ID (
1181 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
1182 P_Validation_mode IN VARCHAR2,
1183 P_GROUP_CAMPAIGN_ID IN NUMBER,
1184 -- Hint: You may add 'X_Item_Property_Rec OUT JTF_PLSQL_API.ITEM_PROPERTY_REC_TYPE' here if you'd like to pass back item property.
1185
1186 X_Return_Status OUT VARCHAR2,
1187 X_Msg_Count OUT NUMBER,
1188 X_Msg_Data OUT VARCHAR2
1189 )
1190 IS
1191 BEGIN
1192
1193 -- Initialize message list if p_init_msg_list is set to TRUE.
1194 IF FND_API.to_Boolean( p_init_msg_list )
1195 THEN
1196 FND_MSG_PUB.initialize;
1197 END IF;
1198
1199
1200 -- Initialize API return status to SUCCESS
1201 x_return_status := FND_API.G_RET_STS_SUCCESS;
1202
1203 -- validate NOT NULL column
1204 IF(p_GROUP_CAMPAIGN_ID is NULL)
1205 THEN
1206 JTF_PLSQL_API.Debug_Message(FND_MSG_PUB.G_MSG_LVL_ERROR, 'ast', 'Private grp_camp API: -Violate NOT NULL constraint(GROUP_CAMPAIGN_ID)');
1207
1208 x_return_status := FND_API.G_RET_STS_ERROR;
1209 END IF;
1210
1211 IF(p_validation_mode = JTF_PLSQL_API.G_CREATE)
1212 THEN
1213 -- Hint: Validate data
1214 -- IF p_GROUP_CAMPAIGN_ID is not NULL and p_GROUP_CAMPAIGN_ID <> G_MISS_CHAR
1215 -- verify if data is valid
1216 -- if data is not valid : x_return_status := FND_API.G_RET_STS_ERROR;
1217 NULL;
1218 ELSIF(p_validation_mode = JTF_PLSQL_API.G_UPDATE)
1219 THEN
1220 -- Hint: Validate data
1221 -- IF p_GROUP_CAMPAIGN_ID <> G_MISS_CHAR
1222 -- verify if data is valid
1223 -- if data is not valid : x_return_status := FND_API.G_RET_STS_ERROR;
1224 NULL;
1225 END IF;
1226
1227 -- Standard call to get message count and if count is 1, get message info.
1228 FND_MSG_PUB.Count_And_Get
1229 ( p_count => x_msg_count,
1230 p_data => x_msg_data
1231 );
1232
1233 END Validate_GROUP_CAMPAIGN_ID;
1234
1235
1236 PROCEDURE Validate_GROUP_ID (
1237 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
1238 P_Validation_mode IN VARCHAR2,
1239 P_GROUP_ID IN NUMBER,
1240 -- Hint: You may add 'X_Item_Property_Rec OUT JTF_PLSQL_API.ITEM_PROPERTY_REC_TYPE' here if you'd like to pass back item property.
1241
1242 X_Return_Status OUT VARCHAR2,
1243 X_Msg_Count OUT NUMBER,
1244 X_Msg_Data OUT VARCHAR2
1245 )
1246 IS
1247 BEGIN
1248
1249 -- Initialize message list if p_init_msg_list is set to TRUE.
1250 IF FND_API.to_Boolean( p_init_msg_list )
1251 THEN
1252 FND_MSG_PUB.initialize;
1253 END IF;
1254
1255
1256 -- Initialize API return status to SUCCESS
1257 x_return_status := FND_API.G_RET_STS_SUCCESS;
1258
1259 -- validate NOT NULL column
1260 IF(p_GROUP_ID is NULL)
1261 THEN
1262 JTF_PLSQL_API.Debug_Message(FND_MSG_PUB.G_MSG_LVL_ERROR, 'ast', 'Private grp_camp API: -Violate NOT NULL constraint(GROUP_ID)');
1263
1264 x_return_status := FND_API.G_RET_STS_ERROR;
1265 END IF;
1266
1267 IF(p_validation_mode = JTF_PLSQL_API.G_CREATE)
1268 THEN
1269 -- Hint: Validate data
1270 -- IF p_GROUP_ID is not NULL and p_GROUP_ID <> G_MISS_CHAR
1271 -- verify if data is valid
1272 -- if data is not valid : x_return_status := FND_API.G_RET_STS_ERROR;
1273 NULL;
1274 ELSIF(p_validation_mode = JTF_PLSQL_API.G_UPDATE)
1275 THEN
1276 -- Hint: Validate data
1277 -- IF p_GROUP_ID <> G_MISS_CHAR
1278 -- verify if data is valid
1279 -- if data is not valid : x_return_status := FND_API.G_RET_STS_ERROR;
1280 NULL;
1281 END IF;
1282
1283 -- Standard call to get message count and if count is 1, get message info.
1284 FND_MSG_PUB.Count_And_Get
1285 ( p_count => x_msg_count,
1286 p_data => x_msg_data
1287 );
1288
1289 END Validate_GROUP_ID;
1290
1291
1292 PROCEDURE Validate_CAMPAIGN_ID (
1293 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
1294 P_Validation_mode IN VARCHAR2,
1295 P_CAMPAIGN_ID IN NUMBER,
1296 -- Hint: You may add 'X_Item_Property_Rec OUT JTF_PLSQL_API.ITEM_PROPERTY_REC_TYPE' here if you'd like to pass back item property.
1297
1298 X_Return_Status OUT VARCHAR2,
1299 X_Msg_Count OUT NUMBER,
1300 X_Msg_Data OUT VARCHAR2
1301 )
1302 IS
1303 BEGIN
1304
1305 -- Initialize message list if p_init_msg_list is set to TRUE.
1306 IF FND_API.to_Boolean( p_init_msg_list )
1307 THEN
1308 FND_MSG_PUB.initialize;
1309 END IF;
1310
1311
1312 -- Initialize API return status to SUCCESS
1313 x_return_status := FND_API.G_RET_STS_SUCCESS;
1314
1315 -- validate NOT NULL column
1316 IF(p_CAMPAIGN_ID is NULL)
1317 THEN
1318 JTF_PLSQL_API.Debug_Message(FND_MSG_PUB.G_MSG_LVL_ERROR, 'ast', 'Private grp_camp API: -Violate NOT NULL constraint(CAMPAIGN_ID)');
1319
1320 x_return_status := FND_API.G_RET_STS_ERROR;
1321 END IF;
1322
1323 IF(p_validation_mode = JTF_PLSQL_API.G_CREATE)
1324 THEN
1325 -- Hint: Validate data
1326 -- IF p_CAMPAIGN_ID is not NULL and p_CAMPAIGN_ID <> G_MISS_CHAR
1327 -- verify if data is valid
1328 -- if data is not valid : x_return_status := FND_API.G_RET_STS_ERROR;
1329 NULL;
1330 ELSIF(p_validation_mode = JTF_PLSQL_API.G_UPDATE)
1331 THEN
1332 -- Hint: Validate data
1333 -- IF p_CAMPAIGN_ID <> G_MISS_CHAR
1334 -- verify if data is valid
1335 -- if data is not valid : x_return_status := FND_API.G_RET_STS_ERROR;
1336 NULL;
1337 END IF;
1338
1339 -- Standard call to get message count and if count is 1, get message info.
1340 FND_MSG_PUB.Count_And_Get
1341 ( p_count => x_msg_count,
1342 p_data => x_msg_data
1343 );
1344
1345 END Validate_CAMPAIGN_ID;
1346
1347
1348 PROCEDURE Validate_START_DATE (
1349 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
1350 P_Validation_mode IN VARCHAR2,
1351 P_START_DATE IN DATE,
1352 -- Hint: You may add 'X_Item_Property_Rec OUT JTF_PLSQL_API.ITEM_PROPERTY_REC_TYPE' here if you'd like to pass back item property.
1353
1354 X_Return_Status OUT VARCHAR2,
1355 X_Msg_Count OUT NUMBER,
1356 X_Msg_Data OUT VARCHAR2
1357 )
1358 IS
1359 BEGIN
1360
1361 -- Initialize message list if p_init_msg_list is set to TRUE.
1362 IF FND_API.to_Boolean( p_init_msg_list )
1363 THEN
1364 FND_MSG_PUB.initialize;
1365 END IF;
1366
1367
1368 -- Initialize API return status to SUCCESS
1369 x_return_status := FND_API.G_RET_STS_SUCCESS;
1370
1371 IF(p_validation_mode = JTF_PLSQL_API.G_CREATE)
1372 THEN
1373 -- Hint: Validate data
1374 -- IF p_START_DATE is not NULL and p_START_DATE <> G_MISS_CHAR
1375 -- verify if data is valid
1376 -- if data is not valid : x_return_status := FND_API.G_RET_STS_ERROR;
1377 NULL;
1378 ELSIF(p_validation_mode = JTF_PLSQL_API.G_UPDATE)
1379 THEN
1380 -- Hint: Validate data
1381 -- IF p_START_DATE <> G_MISS_CHAR
1382 -- verify if data is valid
1383 -- if data is not valid : x_return_status := FND_API.G_RET_STS_ERROR;
1384 NULL;
1385 END IF;
1386
1387 -- Standard call to get message count and if count is 1, get message info.
1388 FND_MSG_PUB.Count_And_Get
1389 ( p_count => x_msg_count,
1390 p_data => x_msg_data
1391 );
1392
1393 END Validate_START_DATE;
1394
1395
1396 PROCEDURE Validate_END_DATE (
1397 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
1398 P_Validation_mode IN VARCHAR2,
1399 P_END_DATE IN DATE,
1400 -- Hint: You may add 'X_Item_Property_Rec OUT JTF_PLSQL_API.ITEM_PROPERTY_REC_TYPE' here if you'd like to pass back item property.
1401
1402 X_Return_Status OUT VARCHAR2,
1403 X_Msg_Count OUT NUMBER,
1404 X_Msg_Data OUT VARCHAR2
1405 )
1406 IS
1407 BEGIN
1408
1409 -- Initialize message list if p_init_msg_list is set to TRUE.
1410 IF FND_API.to_Boolean( p_init_msg_list )
1411 THEN
1412 FND_MSG_PUB.initialize;
1413 END IF;
1414
1415
1416 -- Initialize API return status to SUCCESS
1417 x_return_status := FND_API.G_RET_STS_SUCCESS;
1418
1419 IF(p_validation_mode = JTF_PLSQL_API.G_CREATE)
1420 THEN
1421 -- Hint: Validate data
1422 -- IF p_END_DATE is not NULL and p_END_DATE <> G_MISS_CHAR
1423 -- verify if data is valid
1424 -- if data is not valid : x_return_status := FND_API.G_RET_STS_ERROR;
1425 NULL;
1426 ELSIF(p_validation_mode = JTF_PLSQL_API.G_UPDATE)
1427 THEN
1428 -- Hint: Validate data
1429 -- IF p_END_DATE <> G_MISS_CHAR
1430 -- verify if data is valid
1431 -- if data is not valid : x_return_status := FND_API.G_RET_STS_ERROR;
1432 NULL;
1433 END IF;
1434
1435 -- Standard call to get message count and if count is 1, get message info.
1436 FND_MSG_PUB.Count_And_Get
1437 ( p_count => x_msg_count,
1438 p_data => x_msg_data
1439 );
1440
1441 END Validate_END_DATE;
1442
1443
1444 PROCEDURE Validate_ENABLED_FLAG (
1445 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
1446 P_Validation_mode IN VARCHAR2,
1447 P_ENABLED_FLAG IN VARCHAR2,
1448 -- Hint: You may add 'X_Item_Property_Rec OUT JTF_PLSQL_API.ITEM_PROPERTY_REC_TYPE' here if you'd like to pass back item property.
1449
1450 X_Return_Status OUT VARCHAR2,
1451 X_Msg_Count OUT NUMBER,
1452 X_Msg_Data OUT VARCHAR2
1453 )
1454 IS
1455 BEGIN
1456
1457 -- Initialize message list if p_init_msg_list is set to TRUE.
1458 IF FND_API.to_Boolean( p_init_msg_list )
1459 THEN
1460 FND_MSG_PUB.initialize;
1461 END IF;
1462
1463
1464 -- Initialize API return status to SUCCESS
1465 x_return_status := FND_API.G_RET_STS_SUCCESS;
1466
1467 -- validate NOT NULL column
1468 IF(p_ENABLED_FLAG is NULL)
1469 THEN
1470 JTF_PLSQL_API.Debug_Message(FND_MSG_PUB.G_MSG_LVL_ERROR, 'ast', 'Private grp_camp API: -Violate NOT NULL constraint(ENABLED_FLAG)');
1471
1472 x_return_status := FND_API.G_RET_STS_ERROR;
1473 END IF;
1474
1475 IF(p_validation_mode = JTF_PLSQL_API.G_CREATE)
1476 THEN
1477 -- Hint: Validate data
1478 -- IF p_ENABLED_FLAG is not NULL and p_ENABLED_FLAG <> G_MISS_CHAR
1479 -- verify if data is valid
1480 -- if data is not valid : x_return_status := FND_API.G_RET_STS_ERROR;
1481 NULL;
1482 ELSIF(p_validation_mode = JTF_PLSQL_API.G_UPDATE)
1483 THEN
1484 -- Hint: Validate data
1485 -- IF p_ENABLED_FLAG <> G_MISS_CHAR
1486 -- verify if data is valid
1487 -- if data is not valid : x_return_status := FND_API.G_RET_STS_ERROR;
1488 NULL;
1489 END IF;
1490
1491 -- Standard call to get message count and if count is 1, get message info.
1492 FND_MSG_PUB.Count_And_Get
1493 ( p_count => x_msg_count,
1494 p_data => x_msg_data
1495 );
1496
1497 END Validate_ENABLED_FLAG;
1498
1499
1500 -- Hint: inter-field level validation can be added here.
1501 -- Hint: If p_validation_mode = JTF_PLSQL_API.G_VALIDATE_UPDATE, we should use cursor
1502 -- to get old values for all fields used in inter-field validation and set all G_MISS_XXX fields to original value
1503
1504
1505 -- stored in database table.
1506 PROCEDURE Validate_grp_camp_rec(
1507 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
1508 P_Validation_mode IN VARCHAR2,
1509 P_grp_camp_Rec IN grp_camp_Rec_Type,
1510 X_Return_Status OUT VARCHAR2,
1511 X_Msg_Count OUT NUMBER,
1512 X_Msg_Data OUT VARCHAR2
1513 )
1514 IS
1515 BEGIN
1516
1517 -- Initialize message list if p_init_msg_list is set to TRUE.
1518 IF FND_API.to_Boolean( p_init_msg_list )
1519 THEN
1520 FND_MSG_PUB.initialize;
1521 END IF;
1522
1523
1524 -- Initialize API return status to SUCCESS
1525 x_return_status := FND_API.G_RET_STS_SUCCESS;
1526
1527 -- Hint: Validate data
1528 -- If data not valid
1529 -- THEN
1530 -- x_return_status := FND_API.G_RET_STS_ERROR;
1531
1532 -- Debug Message
1533 JTF_PLSQL_API.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'ast', 'API_INVALID_RECORD');
1534
1535 -- Standard call to get message count and if count is 1, get message info.
1536 FND_MSG_PUB.Count_And_Get
1537 ( p_count => x_msg_count,
1538 p_data => x_msg_data
1539 );
1540
1541 END Validate_grp_camp_Rec;
1542
1543 PROCEDURE Validate_grp_camp(
1544 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
1545 P_Validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1546 P_Validation_mode IN VARCHAR2,
1547 P_grp_camp_Rec IN grp_camp_Rec_Type,
1548 X_Return_Status OUT VARCHAR2,
1549 X_Msg_Count OUT NUMBER,
1550 X_Msg_Data OUT VARCHAR2
1551 )
1552 IS
1553 l_api_name CONSTANT VARCHAR2(30) := 'Validate_grp_camp';
1554 BEGIN
1555
1556 -- Debug Message
1557 JTF_PLSQL_API.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'ast', 'Private API: ' || l_api_name || 'start');
1558
1559
1560 -- Initialize API return status to SUCCESS
1561 x_return_status := FND_API.G_RET_STS_SUCCESS;
1562
1563 IF (p_validation_level >= JTF_PLSQL_API.G_VALID_LEVEL_ITEM) THEN
1564 -- Hint: We provide validation procedure for every column. Developer should delete
1565 -- unnecessary validation procedures.
1566 Validate_GROUP_CAMPAIGN_ID(
1567 p_init_msg_list => FND_API.G_FALSE,
1568 p_validation_mode => p_validation_mode,
1569 p_GROUP_CAMPAIGN_ID => P_grp_camp_Rec.GROUP_CAMPAIGN_ID,
1570 -- Hint: You may add x_item_property_rec as one of your OUT parameter if you'd like to pass back item property.
1571
1572 x_return_status => x_return_status,
1573 x_msg_count => x_msg_count,
1574 x_msg_data => x_msg_data);
1575 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1576 raise FND_API.G_EXC_ERROR;
1577 END IF;
1578
1579 Validate_GROUP_ID(
1580 p_init_msg_list => FND_API.G_FALSE,
1581 p_validation_mode => p_validation_mode,
1582 p_GROUP_ID => P_grp_camp_Rec.GROUP_ID,
1583 -- Hint: You may add x_item_property_rec as one of your OUT parameter if you'd like to pass back item property.
1584
1585 x_return_status => x_return_status,
1586 x_msg_count => x_msg_count,
1587 x_msg_data => x_msg_data);
1588 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1589 raise FND_API.G_EXC_ERROR;
1590 END IF;
1591
1592 Validate_CAMPAIGN_ID(
1593 p_init_msg_list => FND_API.G_FALSE,
1594 p_validation_mode => p_validation_mode,
1595 p_CAMPAIGN_ID => P_grp_camp_Rec.CAMPAIGN_ID,
1596 -- Hint: You may add x_item_property_rec as one of your OUT parameter if you'd like to pass back item property.
1597
1598 x_return_status => x_return_status,
1599 x_msg_count => x_msg_count,
1600 x_msg_data => x_msg_data);
1601 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1602 raise FND_API.G_EXC_ERROR;
1603 END IF;
1604
1605 Validate_START_DATE(
1606 p_init_msg_list => FND_API.G_FALSE,
1607 p_validation_mode => p_validation_mode,
1608 p_START_DATE => P_grp_camp_Rec.START_DATE,
1609 -- Hint: You may add x_item_property_rec as one of your OUT parameter if you'd like to pass back item property.
1610
1611 x_return_status => x_return_status,
1612 x_msg_count => x_msg_count,
1613 x_msg_data => x_msg_data);
1614 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1615 raise FND_API.G_EXC_ERROR;
1616 END IF;
1617
1618 Validate_END_DATE(
1619 p_init_msg_list => FND_API.G_FALSE,
1620 p_validation_mode => p_validation_mode,
1621 p_END_DATE => P_grp_camp_Rec.END_DATE,
1622 -- Hint: You may add x_item_property_rec as one of your OUT parameter if you'd like to pass back item property.
1623
1624 x_return_status => x_return_status,
1625 x_msg_count => x_msg_count,
1626 x_msg_data => x_msg_data);
1627 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1628 raise FND_API.G_EXC_ERROR;
1629 END IF;
1630
1631 Validate_ENABLED_FLAG(
1632 p_init_msg_list => FND_API.G_FALSE,
1633 p_validation_mode => p_validation_mode,
1634 p_ENABLED_FLAG => P_grp_camp_Rec.ENABLED_FLAG,
1635 -- Hint: You may add x_item_property_rec as one of your OUT parameter if you'd like to pass back item property.
1636
1637 x_return_status => x_return_status,
1638 x_msg_count => x_msg_count,
1639 x_msg_data => x_msg_data);
1640 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1641 raise FND_API.G_EXC_ERROR;
1642 END IF;
1643
1644 END IF;
1645
1646 IF (p_validation_level >= JTF_PLSQL_API.G_VALID_LEVEL_RECORD) THEN
1647 -- Hint: Inter-field level validation can be added here
1648 -- invoke record level validation procedures
1649 Validate_grp_camp_Rec(
1650 p_init_msg_list => FND_API.G_FALSE,
1651 p_validation_mode => p_validation_mode,
1652 P_grp_camp_Rec => P_grp_camp_Rec,
1653 x_return_status => x_return_status,
1654 x_msg_count => x_msg_count,
1655 x_msg_data => x_msg_data);
1656
1657 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1658 raise FND_API.G_EXC_ERROR;
1659 END IF;
1660 END IF;
1661
1662 IF (p_validation_level >= JTF_PLSQL_API.G_VALID_LEVEL_INTER_RECORD) THEN
1663 -- invoke inter-record level validation procedures
1664 NULL;
1665 END IF;
1666
1667 IF (p_validation_level >= JTF_PLSQL_API.G_VALID_LEVEL_INTER_ENTITY) THEN
1668 -- invoke inter-entity level validation procedures
1669 NULL;
1670 END IF;
1671
1672
1673 -- Debug Message
1674 JTF_PLSQL_API.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'ast', 'Private API: ' || l_api_name || 'end');
1675
1676 END Validate_grp_camp;
1677 End ast_grp_camp_PVT;