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