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