[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,
295 ATTRIBUTE13,
296 ATTRIBUTE14,
297 ATTRIBUTE15
298 From CSP_PICKLIST_LINES
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
368 RAISE FND_API.G_EXC_ERROR;
365 ,x_sales_member_rec => l_identity_sales_member_rec);
366
367 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
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
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)
428 THEN
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
452 Validate_picklist_lines(
453 p_init_msg_list => FND_API.G_FALSE,
454 p_validation_level => p_validation_level,
455 p_validation_mode => JTF_PLSQL_API.G_UPDATE,
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,
482 p_UOM_CODE => p_picklist_line_rec.UOM_CODE,
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,
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
559 ,X_RETURN_STATUS => X_RETURN_STATUS);
560
561 WHEN OTHERS THEN
562 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
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,
584 X_Msg_Data OUT NOCOPY VARCHAR2
585 )
586
587 IS
588 l_api_name CONSTANT VARCHAR2(30) := 'Delete_picklist_lines';
589 l_api_version_number CONSTANT NUMBER := 1.0;
593 SAVEPOINT DELETE_picklist_lines_PVT;
590 --l_identity_sales_member_rec AS_SALES_MEMBER_PUB.Sales_member_rec_Type;
591 BEGIN
592 -- Standard Start of API savepoint
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
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
683 IF(FND_PROFILE.VALUE('AS_POST_CUSTOM_ENABLED')='Y')
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
716 ,X_MSG_DATA => X_MSG_DATA
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
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
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);
786 dbms_sql.column_value(p_cur_get_picklist_line, 4, X_picklist_line_Rec.LINE_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);
801 dbms_sql.column_value(p_cur_get_picklist_line, 19, X_picklist_line_Rec.ATTRIBUTE9);
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);
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,' ||
893 'CSP_PICKLIST_LINES_V.LAST_UPDATE_LOGIN,' ||
894 'CSP_PICKLIST_LINES_V.PICKLIST_LINE_NUMBER,' ||
895 'CSP_PICKLIST_LINES_V.picklist_header_id,' ||
896 'CSP_PICKLIST_LINES_V.LINE_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,' ||
911 'CSP_PICKLIST_LINES_V.ATTRIBUTE9,' ||
908 'CSP_PICKLIST_LINES_V.ATTRIBUTE6,' ||
909 'CSP_PICKLIST_LINES_V.ATTRIBUTE7,' ||
910 'CSP_PICKLIST_LINES_V.ATTRIBUTE8,' ||
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;
1026
1027 -- Add more IF statements for each column below
1028
1029 -- Debug Message
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 (
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.
1038 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
1039 P_Validation_mode IN VARCHAR2,
1040 P_picklist_line_id IN NUMBER,
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)
1169 THEN
1173
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;
1172 END IF;
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 (
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.
1308 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
1309 P_Validation_mode IN VARCHAR2,
1310 P_UOM_CODE IN VARCHAR2,
1312 X_Return_Status OUT NOCOPY VARCHAR2,
1313 X_Msg_Count OUT NOCOPY NUMBER,
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;
1447 -- IF p_QUANTITY_PICKED <> G_MISS_CHAR
1444 ELSIF(p_validation_mode = JTF_PLSQL_API.G_UPDATE)
1445 THEN
1446 -- Hint: Validate data
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
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,
1456 p_data => x_msg_data
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
1581 p_init_msg_list => FND_API.G_FALSE,
1578 -- Hint: We provide validation procedure for every column. Developer should delete
1579 -- unnecessary validation procedures.
1580 Validate_picklist_line_id(
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);
1588 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1589 raise FND_API.G_EXC_ERROR;
1590 END IF;
1591
1592 Validate_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,
1681 x_return_status => x_return_status,
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.
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;