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