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