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