[Home] [Help]
PACKAGE BODY: APPS.CSP_PACK_SERIAL_LOTS_PVT
Source
1 PACKAGE BODY CSP_Pack_Serial_Lots_PVT AS
2 /* $Header: cspvtspb.pls 115.8 2003/05/02 00:08:05 phegde ship $ */
3 -- Start of Comments
4 -- Package name : CSP_Pack_Serial_Lots_PVT
5 -- Purpose :
6 -- History :
7 -- NOTE :
8 -- End of Comments
9
10
11 G_PKG_NAME CONSTANT VARCHAR2(30):= 'CSP_Pack_Serial_Lots_PVT';
12 G_FILE_NAME CONSTANT VARCHAR2(12) := 'cspvtspb.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_pack_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_plsl_Rec IN plsl_Rec_Type := G_MISS_plsl_REC,
24 --Hint: Add detail tables as parameter lists if it's master-detail relationship.
25 X_PACKLIST_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_pack_serial_lots';
33 l_api_version_number CONSTANT NUMBER := 2.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_plsl_Rec.created_by;
37 l_last_updated_by NUMBER := P_plsl_Rec.last_updated_by;
38 l_last_update_login NUMBER := P_plsl_Rec.last_update_login;
39 l_creation_date DATE := P_plsl_Rec.creation_date;
40 l_last_update_date DATE := P_plsl_Rec.last_update_date;
41
42 BEGIN
43 -- Standard Start of API savepoint
44 SAVEPOINT CREATE_Pack_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_pack_serial_lots_BC(
80 p_api_version_number => 2.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_plsl_Rec => P_plsl_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 => 2.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_pack_serial_lots');
123
124 -- Invoke validation procedures
125 Validate_pack_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_plsl_Rec => P_plsl_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 -- Invoke table handler(CSP_PACKLIST_SERIAL_LOTS_PKG.Insert_Row)
161 CSP_PACKLIST_SERIAL_LOTS_PKG.Insert_Row(
162 px_PACKLIST_SERIAL_LOT_ID => x_PACKLIST_SERIAL_LOT_ID,
163 p_CREATED_BY => l_created_by,
164 p_CREATION_DATE => l_creation_date,
165 p_LAST_UPDATED_BY => l_last_updated_by,
166 p_LAST_UPDATE_DATE => l_last_update_date,
167 p_LAST_UPDATE_LOGIN => l_last_update_login,
168 p_PACKLIST_LINE_ID => p_plsl_rec.PACKLIST_LINE_ID,
169 p_ORGANIZATION_ID => p_plsl_rec.ORGANIZATION_ID,
170 p_INVENTORY_ITEM_ID => p_plsl_rec.INVENTORY_ITEM_ID,
171 p_QUANTITY => p_plsl_rec.QUANTITY,
172 p_LOT_NUMBER => p_plsl_rec.LOT_NUMBER,
173 p_SERIAL_NUMBER => p_plsl_rec.SERIAL_NUMBER);
174 -- Hint: Primary key should be returned.
175 -- x_PACKLIST_SERIAL_LOT_ID := px_PACKLIST_SERIAL_LOT_ID;
176
177 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
178 RAISE FND_API.G_EXC_ERROR;
179 END IF;
180
181 --
182 -- End of API body
183 --
184
185 -- Standard check for p_commit
186 IF FND_API.to_Boolean( p_commit )
187 THEN
188 COMMIT WORK;
189 END IF;
190
191
192 -- Debug Message
193 JTF_PLSQL_API.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'CSP', 'Private API: ' || l_api_name || 'end');
194
195
196 -- Standard call to get message count and if count is 1, get message info.
197 FND_MSG_PUB.Count_And_Get
198 ( p_count => x_msg_count,
199 p_data => x_msg_data
200 );
201
202 -- Un-comment the following statements when AS_CALLOUT_PKG is ready.
203 /*
204 -- if profile AS_POST_CUSTOM_ENABLED is set to 'Y', callout procedure is invoked for customization purpose
205 IF(FND_PROFILE.VALUE('AS_POST_CUSTOM_ENABLED')='Y')
206 THEN
207 AS_CALLOUT_PKG.Create_pack_serial_lots_AC(
208 p_api_version_number => 2.0,
209 p_init_msg_list => FND_API.G_FALSE,
210 p_commit => FND_API.G_FALSE,
211 p_validation_level => p_validation_level,
212 P_plsl_Rec => P_plsl_Rec,
213 -- Hint: Add detail tables as parameter lists if it's master-detail relationship.
214 x_return_status => x_return_status,
215 x_msg_count => x_msg_count,
216 x_msg_data => x_msg_data);
217 END IF;
218 */
219 EXCEPTION
220 WHEN FND_API.G_EXC_ERROR THEN
221 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
222 P_API_NAME => L_API_NAME
223 ,P_PKG_NAME => G_PKG_NAME
224 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
225 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PVT
226 ,X_MSG_COUNT => X_MSG_COUNT
227 ,X_MSG_DATA => X_MSG_DATA
228 ,X_RETURN_STATUS => X_RETURN_STATUS);
229
230 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
231 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
232 P_API_NAME => L_API_NAME
233 ,P_PKG_NAME => G_PKG_NAME
234 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
235 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PVT
236 ,X_MSG_COUNT => X_MSG_COUNT
237 ,X_MSG_DATA => X_MSG_DATA
238 ,X_RETURN_STATUS => X_RETURN_STATUS);
239
240 WHEN OTHERS THEN
241 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
242 P_API_NAME => L_API_NAME
243 ,P_PKG_NAME => G_PKG_NAME
244 ,P_EXCEPTION_LEVEL => JTF_PLSQL_API.G_EXC_OTHERS
245 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PVT
246 ,X_MSG_COUNT => X_MSG_COUNT
247 ,X_MSG_DATA => X_MSG_DATA
248 ,X_RETURN_STATUS => X_RETURN_STATUS);
249 End Create_pack_serial_lots;
250
251
252 -- Hint: Add corresponding update detail table procedures if it's master-detail relationship.
253 PROCEDURE Update_pack_serial_lots(
254 P_Api_Version_Number IN NUMBER,
255 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
256 P_Commit IN VARCHAR2 := FND_API.G_FALSE,
257 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
258 P_Identity_Salesforce_Id IN NUMBER := NULL,
259 P_plsl_Rec IN plsl_Rec_Type,
260 X_Return_Status OUT NOCOPY VARCHAR2,
261 X_Msg_Count OUT NOCOPY NUMBER,
262 X_Msg_Data OUT NOCOPY VARCHAR2
263 )
264
265 IS
266 /*
267 Cursor C_Get_pack_serial_lots(PACKLIST_SERIAL_LOT_ID Number) IS
268 Select rowid,
269 PACKLIST_SERIAL_LOT_ID,
270 CREATED_BY,
271 CREATION_DATE,
272 LAST_UPDATED_BY,
273 LAST_UPDATE_DATE,
274 LAST_UPDATE_LOGIN,
275 PACKLIST_LINE_ID,
276 ORGANIZATION_ID,
277 INVENTORY_ITEM_ID,
278 QUANTITY,
279 LOT_NUMBER,
280 SERIAL_NUMBER
281 From CSP_PACKLIST_SERIAL_LOTS
282 -- Hint: Developer need to provide Where clause
283 For Update NOWAIT;
284 */
285 l_api_name CONSTANT VARCHAR2(30) := 'Update_pack_serial_lots';
286 l_api_version_number CONSTANT NUMBER := 2.0;
287 -- Local Variables
288 l_identity_sales_member_rec AS_SALES_MEMBER_PUB.Sales_member_rec_Type;
289 l_ref_plsl_rec CSP_pack_serial_lots_PVT.plsl_Rec_Type;
290 l_tar_plsl_rec CSP_pack_serial_lots_PVT.plsl_Rec_Type := P_plsl_Rec;
291 l_rowid ROWID;
292 BEGIN
293 -- Standard Start of API savepoint
294 SAVEPOINT UPDATE_Pack_Serial_Lots_PVT;
295
296 -- Standard call to check for call compatibility.
297 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
298 p_api_version_number,
299 l_api_name,
300 G_PKG_NAME)
301 THEN
302 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
303 END IF;
304
305
306 -- Initialize message list if p_init_msg_list is set to TRUE.
307 IF FND_API.to_Boolean( p_init_msg_list )
308 THEN
309 FND_MSG_PUB.initialize;
310 END IF;
311
312
313 -- Debug Message
314 JTF_PLSQL_API.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'CSP', 'Private API: ' || l_api_name || 'start');
315
316
317 -- Initialize API return status to SUCCESS
318 x_return_status := FND_API.G_RET_STS_SUCCESS;
319
320 --
321 -- Api body
322 --
323 -- Un-comment the following statements when AS_CALLOUT_PKG is ready.
324 /*
325 -- if profile AS_PRE_CUSTOM_ENABLED is set to 'Y', callout procedure is invoked for customization purpose
326 IF(FND_PROFILE.VALUE('AS_PRE_CUSTOM_ENABLED')='Y')
327 THEN
328 AS_CALLOUT_PKG.Update_pack_serial_lots_BU(
329 p_api_version_number => 2.0,
330 p_init_msg_list => FND_API.G_FALSE,
331 p_commit => FND_API.G_FALSE,
332 p_validation_level => p_validation_level,
333 p_identity_salesforce_id => p_identity_salesforce_id,
334 P_plsl_Rec => P_plsl_Rec,
335 -- Hint: Add detail tables as parameter lists if it's master-detail relationship.
336 x_return_status => x_return_status,
337 x_msg_count => x_msg_count,
338 x_msg_data => x_msg_data);
339 END IF;
340 */
341
342 /* Removed by CSP, 06/23/00
343
344 AS_SALES_ORG_MANAGER_PVT.Get_CurrentUser(
345 p_api_version_number => 2.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_pack_serial_lots( l_tar_plsl_rec.PACKLIST_SERIAL_LOT_ID);
361
362 Fetch C_Get_pack_serial_lots into
363 l_rowid,
364 l_ref_plsl_rec.PACKLIST_SERIAL_LOT_ID,
365 l_ref_plsl_rec.CREATED_BY,
366 l_ref_plsl_rec.CREATION_DATE,
367 l_ref_plsl_rec.LAST_UPDATED_BY,
368 l_ref_plsl_rec.LAST_UPDATE_DATE,
369 l_ref_plsl_rec.LAST_UPDATE_LOGIN,
370 l_ref_plsl_rec.PACKLIST_LINE_ID,
371 l_ref_plsl_rec.ORGANIZATION_ID,
372 l_ref_plsl_rec.INVENTORY_ITEM_ID,
373 l_ref_plsl_rec.QUANTITY,
374 l_ref_plsl_rec.LOT_NUMBER,
375 l_ref_plsl_rec.SERIAL_NUMBER;
376
377 If ( C_Get_pack_serial_lots%NOTFOUND) Then
378 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
379 THEN
383 END IF;
380 FND_MESSAGE.Set_Name('CSP', 'API_MISSING_UPDATE_TARGET');
381 FND_MESSAGE.Set_Token ('INFO', 'pack_serial_lots', FALSE);
382 FND_MSG_PUB.Add;
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_pack_serial_lots;
389 */
390
391
392 If (l_tar_plsl_rec.last_update_date is NULL or
393 l_tar_plsl_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_plsl_rec.last_update_date <> l_ref_plsl_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', 'pack_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_pack_serial_lots');
417
418 -- Invoke validation procedures
419 Validate_pack_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_plsl_Rec => P_plsl_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_PACKLIST_SERIAL_LOTS_PKG.Update_Row)
439 CSP_PACKLIST_SERIAL_LOTS_PKG.Update_Row(
440 p_PACKLIST_SERIAL_LOT_ID => p_plsl_rec.PACKLIST_SERIAL_LOT_ID,
441 p_CREATED_BY => p_plsl_rec.CREATED_BY,
442 p_CREATION_DATE => p_plsl_rec.CREATION_DATE,
443 p_LAST_UPDATED_BY => p_plsl_rec.LAST_UPDATED_BY,
444 p_LAST_UPDATE_DATE => p_plsl_rec.LAST_UPDATE_DATE,
445 p_LAST_UPDATE_LOGIN => p_plsl_rec.LAST_UPDATE_LOGIN,
446 p_PACKLIST_LINE_ID => p_plsl_rec.PACKLIST_LINE_ID,
447 p_ORGANIZATION_ID => p_plsl_rec.ORGANIZATION_ID,
448 p_INVENTORY_ITEM_ID => p_plsl_rec.INVENTORY_ITEM_ID,
449 p_QUANTITY => p_plsl_rec.QUANTITY,
450 p_LOT_NUMBER => p_plsl_rec.LOT_NUMBER,
451 p_SERIAL_NUMBER => p_plsl_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_pack_serial_lots_AU(
479 p_api_version_number => 2.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_plsl_Rec => P_plsl_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
500 ,X_RETURN_STATUS => X_RETURN_STATUS);
497 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PVT
498 ,X_MSG_COUNT => X_MSG_COUNT
499 ,X_MSG_DATA => X_MSG_DATA
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_pack_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_pack_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_plsl_Rec IN plsl_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_pack_serial_lots';
540 l_api_version_number CONSTANT NUMBER := 2.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_Pack_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_pack_serial_lots_BD(
579 p_api_version_number => 2.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_plsl_Rec => P_plsl_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
594 AS_SALES_ORG_MANAGER_PVT.Get_CurrentUser(
595 p_api_version_number => 2.0
596 ,p_salesforce_id => p_identity_salesforce_id
597 ,x_return_status => x_return_status
598 ,x_msg_count => x_msg_count
599 ,x_msg_data => x_msg_data
600 ,x_sales_member_rec => l_identity_sales_member_rec);
601
602 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
603 RAISE FND_API.G_EXC_ERROR;
604 END IF;
605 */
606 -- Debug Message
607 JTF_PLSQL_API.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'CSP', 'Private API: Calling delete table handler');
608
609 -- Invoke table handler(CSP_PACKLIST_SERIAL_LOTS_PKG.Delete_Row)
610 CSP_PACKLIST_SERIAL_LOTS_PKG.Delete_Row(
611 p_PACKLIST_SERIAL_LOT_ID => p_plsl_rec.PACKLIST_SERIAL_LOT_ID);
612 --
613 -- End of API body
614 --
615
616 -- Standard check for p_commit
617 IF FND_API.to_Boolean( p_commit )
618 THEN
619 COMMIT WORK;
620 END IF;
621
625
622
623 -- Debug Message
624 JTF_PLSQL_API.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'CSP', 'Private API: ' || l_api_name || 'end');
626
627 -- Standard call to get message count and if count is 1, get message info.
628 FND_MSG_PUB.Count_And_Get
629 ( p_count => x_msg_count,
630 p_data => x_msg_data
631 );
632
633 -- Un-comment the following statements when AS_CALLOUT_PKG is ready.
634 /*
635 -- if profile AS_POST_CUSTOM_ENABLED is set to 'Y', callout procedure is invoked for customization purpose
636 IF(FND_PROFILE.VALUE('AS_POST_CUSTOM_ENABLED')='Y')
637 THEN
638 AS_CALLOUT_PKG.Delete_pack_serial_lots_AD(
639 p_api_version_number => 2.0,
640 p_init_msg_list => FND_API.G_FALSE,
641 p_commit => FND_API.G_FALSE,
642 p_validation_level => p_validation_level,
643 p_identity_salesforce_id => p_identity_salesforce_id,
644 P_plsl_Rec => P_plsl_Rec,
645 -- Hint: Add detail tables as parameter lists if it's master-detail relationship.
646 x_return_status => x_return_status,
647 x_msg_count => x_msg_count,
648 x_msg_data => x_msg_data);
649 END IF;
650 */
651 EXCEPTION
652 WHEN FND_API.G_EXC_ERROR THEN
653 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
654 P_API_NAME => L_API_NAME
655 ,P_PKG_NAME => G_PKG_NAME
656 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
657 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PVT
658 ,X_MSG_COUNT => X_MSG_COUNT
659 ,X_MSG_DATA => X_MSG_DATA
660 ,X_RETURN_STATUS => X_RETURN_STATUS);
661
662 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
663 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
664 P_API_NAME => L_API_NAME
665 ,P_PKG_NAME => G_PKG_NAME
666 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
667 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PVT
668 ,X_MSG_COUNT => X_MSG_COUNT
669 ,X_MSG_DATA => X_MSG_DATA
670 ,X_RETURN_STATUS => X_RETURN_STATUS);
671
672 WHEN OTHERS THEN
673 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
674 P_API_NAME => L_API_NAME
675 ,P_PKG_NAME => G_PKG_NAME
676 ,P_EXCEPTION_LEVEL => JTF_PLSQL_API.G_EXC_OTHERS
677 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PVT
678 ,X_MSG_COUNT => X_MSG_COUNT
679 ,X_MSG_DATA => X_MSG_DATA
680 ,X_RETURN_STATUS => X_RETURN_STATUS);
681 End Delete_pack_serial_lots;
682
683
684 -- This procudure defines the columns for the Dynamic SQL.
685 PROCEDURE Define_Columns(
686 P_plsl_Rec IN CSP_Pack_Serial_Lots_PUB.plsl_Rec_Type,
687 p_cur_get_plsl IN NUMBER
688 )
689 IS
690 BEGIN
691 -- Debug Message
692 JTF_PLSQL_API.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'CSP', 'Private API: Define Columns Begins');
693
694 -- define all columns for CSP_PACKLIST_SERIAL_LOTS_V view
695 dbms_sql.define_column(p_cur_get_plsl, 1, P_plsl_Rec.PACKLIST_SERIAL_LOT_ID);
696 dbms_sql.define_column(p_cur_get_plsl, 2, P_plsl_Rec.PACKLIST_LINE_ID);
697 dbms_sql.define_column(p_cur_get_plsl, 3, P_plsl_Rec.ORGANIZATION_ID);
698 dbms_sql.define_column(p_cur_get_plsl, 4, P_plsl_Rec.INVENTORY_ITEM_ID);
699 dbms_sql.define_column(p_cur_get_plsl, 5, P_plsl_Rec.QUANTITY);
700 dbms_sql.define_column(p_cur_get_plsl, 6, P_plsl_Rec.LOT_NUMBER, 240);
701 dbms_sql.define_column(p_cur_get_plsl, 7, P_plsl_Rec.SERIAL_NUMBER, 240);
702
703 -- Debug Message
704 JTF_PLSQL_API.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'CSP', 'Private API: Define Columns Ends');
705 END Define_Columns;
706
707 -- This procudure gets column values by the Dynamic SQL.
708 PROCEDURE Get_Column_Values(
709 p_cur_get_plsl IN NUMBER,
710 X_plsl_Rec OUT NOCOPY CSP_Pack_Serial_Lots_PUB.plsl_Rec_Type
711 )
712 IS
713 BEGIN
714 -- Debug Message
715 JTF_PLSQL_API.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'CSP', 'Private API: Get Column Values Begins');
716
717 -- get all column values for CSP_PACKLIST_SERIAL_LOTS_V table
718 dbms_sql.column_value(p_cur_get_plsl, 1, X_plsl_Rec.ROW_ID);
719 dbms_sql.column_value(p_cur_get_plsl, 2, X_plsl_Rec.PACKLIST_SERIAL_LOT_ID);
720 dbms_sql.column_value(p_cur_get_plsl, 3, X_plsl_Rec.PACKLIST_LINE_ID);
721 dbms_sql.column_value(p_cur_get_plsl, 4, X_plsl_Rec.ORGANIZATION_ID);
722 dbms_sql.column_value(p_cur_get_plsl, 5, X_plsl_Rec.INVENTORY_ITEM_ID);
723 dbms_sql.column_value(p_cur_get_plsl, 6, X_plsl_Rec.QUANTITY);
724 dbms_sql.column_value(p_cur_get_plsl, 7, X_plsl_Rec.LOT_NUMBER);
725 dbms_sql.column_value(p_cur_get_plsl, 8, X_plsl_Rec.SERIAL_NUMBER);
726
727 -- Debug Message
728 JTF_PLSQL_API.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'CSP', 'Private API: Get Column Values Ends');
729 END Get_Column_Values;
730
731 PROCEDURE Gen_plsl_order_cl(
732 p_order_by_rec IN CSP_Pack_Serial_Lots_PUB.plsl_sort_rec_type,
733 x_order_by_cl OUT NOCOPY VARCHAR2,
737 )
734 x_return_status OUT NOCOPY VARCHAR2,
735 x_msg_count OUT NOCOPY NUMBER,
736 x_msg_data OUT NOCOPY VARCHAR2
738 IS
739 l_order_by_cl VARCHAR2(1000) := NULL;
740 l_util_order_by_tbl JTF_PLSQL_API.Util_order_by_tbl_type;
741 BEGIN
742 -- Debug Message
743 JTF_PLSQL_API.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'CSP', 'Private API: Generate Order by Begins');
744
745 -- Hint: Developer should add more statements according to CSP_sort_rec_type
746 -- Ex:
747 -- l_util_order_by_tbl(1).col_choice := p_order_by_rec.customer_name;
748 -- l_util_order_by_tbl(1).col_name := 'Customer_Name';
749
750 -- Debug Message
751 JTF_PLSQL_API.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'CSP', 'Invoke JTF_PLSQL_API.Translate_OrderBy');
752
753 JTF_PLSQL_API.Translate_OrderBy(
754 p_api_version_number => 1.0
755 ,p_init_msg_list => FND_API.G_FALSE
756 ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
757 ,p_order_by_tbl => l_util_order_by_tbl
758 ,x_order_by_clause => l_order_by_cl
759 ,x_return_status => x_return_status
760 ,x_msg_count => x_msg_count
761 ,x_msg_data => x_msg_data);
762
763 IF(l_order_by_cl IS NOT NULL) THEN
764 x_order_by_cl := 'order by' || l_order_by_cl;
765 ELSE
766 x_order_by_cl := NULL;
767 END IF;
768
769 -- Debug Message
770 JTF_PLSQL_API.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'CSP', 'Private API: Generate Order by Ends');
771 END Gen_plsl_order_cl;
772
773 -- This procedure bind the variables for the Dynamic SQL
774 PROCEDURE Bind(
775 P_plsl_Rec IN CSP_Pack_Serial_Lots_PUB.plsl_Rec_Type,
776 -- Hint: Add more binding variables here
777 p_cur_get_plsl IN NUMBER
778 )
779 IS
780 BEGIN
781 -- Bind variables
782 -- Only those that are not NULL
783 -- Debug Message
784 JTF_PLSQL_API.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'CSP', 'Private API: Bind Variables Begins');
785
786 -- The following example applies to all columns,
787 -- developers can copy and paste them.
788 IF( (P_plsl_Rec.PACKLIST_SERIAL_LOT_ID IS NOT NULL) AND (P_plsl_Rec.PACKLIST_SERIAL_LOT_ID <> FND_API.G_MISS_NUM) )
789 THEN
790 DBMS_SQL.BIND_VARIABLE(p_cur_get_plsl, ':p_PACKLIST_SERIAL_LOT_ID', P_plsl_Rec.PACKLIST_SERIAL_LOT_ID);
791 END IF;
792
793 -- Debug Message
794 JTF_PLSQL_API.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'CSP', 'Private API: Bind Variables Ends');
795 END Bind;
796
797 PROCEDURE Gen_Select(
798 x_select_cl OUT NOCOPY VARCHAR2
799 )
800 IS
801 BEGIN
802 -- Debug Message
803 JTF_PLSQL_API.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'CSP', 'Private API: Generate Select Begins');
804
805 x_select_cl := 'Select ' ||
806 'CSP_PACKLIST_SERIAL_LOTS_V.ROW_ID,' ||
807 'CSP_PACKLIST_SERIAL_LOTS_V.PACKLIST_SERIAL_LOT_ID,' ||
808 'CSP_PACKLIST_SERIAL_LOTS_V.CREATED_BY,' ||
809 'CSP_PACKLIST_SERIAL_LOTS_V.CREATION_DATE,' ||
810 'CSP_PACKLIST_SERIAL_LOTS_V.LAST_UPDATED_BY,' ||
811 'CSP_PACKLIST_SERIAL_LOTS_V.LAST_UPDATE_DATE,' ||
812 'CSP_PACKLIST_SERIAL_LOTS_V.LAST_UPDATE_LOGIN,' ||
813 'CSP_PACKLIST_SERIAL_LOTS_V.PACKLIST_LINE_ID,' ||
814 'CSP_PACKLIST_SERIAL_LOTS_V.ORGANIZATION_ID,' ||
815 'CSP_PACKLIST_SERIAL_LOTS_V.INVENTORY_ITEM_ID,' ||
816 'CSP_PACKLIST_SERIAL_LOTS_V.QUANTITY,' ||
817 'CSP_PACKLIST_SERIAL_LOTS_V.LOT_NUMBER,' ||
818 'CSP_PACKLIST_SERIAL_LOTS_V.SERIAL_NUMBER,' ||
819 'from CSP_PACKLIST_SERIAL_LOTS_V';
820 -- Debug Message
821 JTF_PLSQL_API.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'CSP', 'Private API: Generate Select Ends');
822
823 END Gen_Select;
824
825 PROCEDURE Gen_plsl_Where(
826 P_plsl_Rec IN CSP_Pack_Serial_Lots_PUB.plsl_Rec_Type,
827 x_plsl_where OUT NOCOPY VARCHAR2
828 )
829 IS
830 -- cursors to check if wildcard values '%' and '_' have been passed
831 -- as item values
832 CURSOR c_chk_str1(p_rec_item VARCHAR2) IS
833 SELECT INSTR(p_rec_item, '%', 1, 1)
834 FROM DUAL;
835 CURSOR c_chk_str2(p_rec_item VARCHAR2) IS
836 SELECT INSTR(p_rec_item, '_', 1, 1)
837 FROM DUAL;
838
839 -- return values from cursors
840 str_csr1 NUMBER;
841 str_csr2 NUMBER;
842 l_operator VARCHAR2(10);
843 BEGIN
844 -- Debug Message
845 JTF_PLSQL_API.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'CSP', 'Private API: Generate Where Begins');
846
847 -- There are three examples for each kind of datatype:
848 -- NUMBER, DATE, VARCHAR2.
849 -- Developer can copy and paste the following codes for your own record.
850
851 -- example for NUMBER datatype
852 IF( (P_plsl_Rec.PACKLIST_SERIAL_LOT_ID IS NOT NULL) AND (P_plsl_Rec.PACKLIST_SERIAL_LOT_ID <> FND_API.G_MISS_NUM) )
853 THEN
854 IF(x_plsl_where IS NULL) THEN
855 x_plsl_where := 'Where';
856 ELSE
857 x_plsl_where := x_plsl_where || ' AND ';
861
858 END IF;
859 x_plsl_where := x_plsl_where || 'P_plsl_Rec.PACKLIST_SERIAL_LOT_ID = :p_PACKLIST_SERIAL_LOT_ID';
860 END IF;
862 -- There's no example for DATE because no such
863 -- type in table CSP_PACKLIST_SERIAL_LOTS
864 -- example for VARCHAR2 datatype
865 IF( (P_plsl_Rec.LOT_NUMBER IS NOT NULL) AND (P_plsl_Rec.LOT_NUMBER <> FND_API.G_MISS_CHAR) )
866 THEN
867 -- check if item value contains '%' wildcard
868 OPEN c_chk_str1(P_plsl_Rec.LOT_NUMBER);
869 FETCH c_chk_str1 INTO str_csr1;
870 CLOSE c_chk_str1;
871
872 IF(str_csr1 <> 0) THEN
873 l_operator := ' LIKE ';
874 ELSE
875 l_operator := ' = ';
876 END IF;
877
878 -- check if item value contains '_' wildcard
879 OPEN c_chk_str2(P_plsl_Rec.LOT_NUMBER);
880 FETCH c_chk_str2 INTO str_csr2;
881 CLOSE c_chk_str2;
882
883 IF(str_csr2 <> 0) THEN
884 l_operator := ' LIKE ';
885 ELSE
886 l_operator := ' = ';
887 END IF;
888
889 IF(x_plsl_where IS NULL) THEN
890 x_plsl_where := 'Where ';
891 ELSE
892 x_plsl_where := x_plsl_where || ' AND ';
893 END IF;
894 x_plsl_where := x_plsl_where || 'P_plsl_Rec.LOT_NUMBER ' || l_operator || ' :p_LOT_NUMBER';
895 END IF;
896
897 -- Add more IF statements for each column below
898
899 -- Debug Message
900 JTF_PLSQL_API.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'CSP', 'Private API: Generate Where Ends');
901
902 END Gen_plsl_Where;
903
904 -- Item-level validation procedures
905 PROCEDURE Validate_PL_SERIAL_LOT_ID (
906 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
907 P_Validation_mode IN VARCHAR2,
908 P_PACKLIST_SERIAL_LOT_ID IN NUMBER,
909 -- 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.
910 X_Return_Status OUT NOCOPY VARCHAR2,
911 X_Msg_Count OUT NOCOPY NUMBER,
912 X_Msg_Data OUT NOCOPY VARCHAR2
913 )
914 IS
915 BEGIN
916
917 -- Initialize message list if p_init_msg_list is set to TRUE.
918 IF FND_API.to_Boolean( p_init_msg_list )
919 THEN
920 FND_MSG_PUB.initialize;
921 END IF;
922
923
924 -- Initialize API return status to SUCCESS
925 x_return_status := FND_API.G_RET_STS_SUCCESS;
926
927 -- validate NOT NULL column
928 IF(p_PACKLIST_SERIAL_LOT_ID is NULL)
929 THEN
930 JTF_PLSQL_API.Debug_Message(FND_MSG_PUB.G_MSG_LVL_ERROR, 'CSP', 'Private pack_serial_lots API: -Violate NOT NULL constraint(PACKLIST_SERIAL_LOT_ID)');
931 x_return_status := FND_API.G_RET_STS_ERROR;
932 END IF;
933
934 IF(p_validation_mode = JTF_PLSQL_API.G_CREATE)
935 THEN
936 -- Hint: Validate data
937 -- IF p_PACKLIST_SERIAL_LOT_ID is not NULL and p_PACKLIST_SERIAL_LOT_ID <> G_MISS_CHAR
938 -- verify if data is valid
939 -- if data is not valid : x_return_status := FND_API.G_RET_STS_ERROR;
940 NULL;
941 ELSIF(p_validation_mode = JTF_PLSQL_API.G_UPDATE)
942 THEN
943 -- Hint: Validate data
944 -- IF p_PACKLIST_SERIAL_LOT_ID <> G_MISS_CHAR
945 -- verify if data is valid
946 -- if data is not valid : x_return_status := FND_API.G_RET_STS_ERROR;
947 NULL;
948 END IF;
949
950 -- Standard call to get message count and if count is 1, get message info.
951 FND_MSG_PUB.Count_And_Get
952 ( p_count => x_msg_count,
953 p_data => x_msg_data
954 );
955
956 END Validate_PL_SERIAL_LOT_ID;
957
958
959 PROCEDURE Validate_PACKLIST_LINE_ID (
960 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
961 P_Validation_mode IN VARCHAR2,
962 P_PACKLIST_LINE_ID IN NUMBER,
963 -- 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.
964 X_Return_Status OUT NOCOPY VARCHAR2,
965 X_Msg_Count OUT NOCOPY NUMBER,
966 X_Msg_Data OUT NOCOPY VARCHAR2
967 )
968 IS
969 BEGIN
970
971 -- Initialize message list if p_init_msg_list is set to TRUE.
972 IF FND_API.to_Boolean( p_init_msg_list )
973 THEN
974 FND_MSG_PUB.initialize;
975 END IF;
976
977
978 -- Initialize API return status to SUCCESS
979 x_return_status := FND_API.G_RET_STS_SUCCESS;
980
981 -- validate NOT NULL column
982 IF(p_PACKLIST_LINE_ID is NULL)
983 THEN
984 JTF_PLSQL_API.Debug_Message(FND_MSG_PUB.G_MSG_LVL_ERROR, 'CSP', 'Private pack_serial_lots API: -Violate NOT NULL constraint(PACKLIST_LINE_ID)');
985 x_return_status := FND_API.G_RET_STS_ERROR;
986 END IF;
987
988 IF(p_validation_mode = JTF_PLSQL_API.G_CREATE)
989 THEN
990 -- Hint: Validate data
994 NULL;
991 -- IF p_PACKLIST_LINE_ID is not NULL and p_PACKLIST_LINE_ID <> G_MISS_CHAR
992 -- verify if data is valid
993 -- if data is not valid : x_return_status := FND_API.G_RET_STS_ERROR;
995 ELSIF(p_validation_mode = JTF_PLSQL_API.G_UPDATE)
996 THEN
997 -- Hint: Validate data
998 -- IF p_PACKLIST_LINE_ID <> G_MISS_CHAR
999 -- verify if data is valid
1000 -- if data is not valid : x_return_status := FND_API.G_RET_STS_ERROR;
1001 NULL;
1002 END IF;
1003
1004 -- Standard call to get message count and if count is 1, get message info.
1005 FND_MSG_PUB.Count_And_Get
1006 ( p_count => x_msg_count,
1007 p_data => x_msg_data
1008 );
1009
1010 END Validate_PACKLIST_LINE_ID;
1011
1012
1013 PROCEDURE Validate_ORGANIZATION_ID (
1014 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
1015 P_Validation_mode IN VARCHAR2,
1016 P_ORGANIZATION_ID IN NUMBER,
1017 -- 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.
1018 X_Return_Status OUT NOCOPY VARCHAR2,
1019 X_Msg_Count OUT NOCOPY NUMBER,
1020 X_Msg_Data OUT NOCOPY VARCHAR2
1021 )
1022 IS
1023 BEGIN
1024
1025 -- Initialize message list if p_init_msg_list is set to TRUE.
1026 IF FND_API.to_Boolean( p_init_msg_list )
1027 THEN
1028 FND_MSG_PUB.initialize;
1029 END IF;
1030
1031
1032 -- Initialize API return status to SUCCESS
1033 x_return_status := FND_API.G_RET_STS_SUCCESS;
1034
1035 -- validate NOT NULL column
1036 IF(p_ORGANIZATION_ID is NULL)
1037 THEN
1038 JTF_PLSQL_API.Debug_Message(FND_MSG_PUB.G_MSG_LVL_ERROR, 'CSP', 'Private pack_serial_lots API: -Violate NOT NULL constraint(ORGANIZATION_ID)');
1039 x_return_status := FND_API.G_RET_STS_ERROR;
1040 END IF;
1041
1042 IF(p_validation_mode = JTF_PLSQL_API.G_CREATE)
1043 THEN
1044 -- Hint: Validate data
1045 -- IF p_ORGANIZATION_ID is not NULL and p_ORGANIZATION_ID <> G_MISS_CHAR
1046 -- verify if data is valid
1047 -- if data is not valid : x_return_status := FND_API.G_RET_STS_ERROR;
1048 NULL;
1049 ELSIF(p_validation_mode = JTF_PLSQL_API.G_UPDATE)
1050 THEN
1051 -- Hint: Validate data
1052 -- IF p_ORGANIZATION_ID <> G_MISS_CHAR
1053 -- verify if data is valid
1054 -- if data is not valid : x_return_status := FND_API.G_RET_STS_ERROR;
1055 NULL;
1056 END IF;
1057
1058 -- Standard call to get message count and if count is 1, get message info.
1059 FND_MSG_PUB.Count_And_Get
1060 ( p_count => x_msg_count,
1061 p_data => x_msg_data
1062 );
1063
1064 END Validate_ORGANIZATION_ID;
1065
1066
1067 PROCEDURE Validate_INVENTORY_ITEM_ID (
1068 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
1069 P_Validation_mode IN VARCHAR2,
1070 P_INVENTORY_ITEM_ID IN NUMBER,
1071 -- 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.
1072 X_Return_Status OUT NOCOPY VARCHAR2,
1073 X_Msg_Count OUT NOCOPY NUMBER,
1074 X_Msg_Data OUT NOCOPY VARCHAR2
1075 )
1076 IS
1077 BEGIN
1078
1079 -- Initialize message list if p_init_msg_list is set to TRUE.
1080 IF FND_API.to_Boolean( p_init_msg_list )
1081 THEN
1082 FND_MSG_PUB.initialize;
1083 END IF;
1084
1085
1086 -- Initialize API return status to SUCCESS
1087 x_return_status := FND_API.G_RET_STS_SUCCESS;
1088
1089 -- validate NOT NULL column
1090 IF(p_INVENTORY_ITEM_ID is NULL)
1091 THEN
1092 JTF_PLSQL_API.Debug_Message(FND_MSG_PUB.G_MSG_LVL_ERROR, 'CSP', 'Private pack_serial_lots API: -Violate NOT NULL constraint(INVENTORY_ITEM_ID)');
1093 x_return_status := FND_API.G_RET_STS_ERROR;
1094 END IF;
1095
1096 IF(p_validation_mode = JTF_PLSQL_API.G_CREATE)
1097 THEN
1098 -- Hint: Validate data
1099 -- IF p_INVENTORY_ITEM_ID is not NULL and p_INVENTORY_ITEM_ID <> G_MISS_CHAR
1100 -- verify if data is valid
1101 -- if data is not valid : x_return_status := FND_API.G_RET_STS_ERROR;
1102 NULL;
1103 ELSIF(p_validation_mode = JTF_PLSQL_API.G_UPDATE)
1104 THEN
1105 -- Hint: Validate data
1106 -- IF p_INVENTORY_ITEM_ID <> G_MISS_CHAR
1107 -- verify if data is valid
1108 -- if data is not valid : x_return_status := FND_API.G_RET_STS_ERROR;
1109 NULL;
1110 END IF;
1111
1112 -- Standard call to get message count and if count is 1, get message info.
1113 FND_MSG_PUB.Count_And_Get
1114 ( p_count => x_msg_count,
1115 p_data => x_msg_data
1116 );
1117
1118 END Validate_INVENTORY_ITEM_ID;
1119
1120
1121 PROCEDURE Validate_QUANTITY (
1122 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
1126 X_Return_Status OUT NOCOPY VARCHAR2,
1123 P_Validation_mode IN VARCHAR2,
1124 P_QUANTITY IN NUMBER,
1125 -- 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.
1127 X_Msg_Count OUT NOCOPY NUMBER,
1128 X_Msg_Data OUT NOCOPY VARCHAR2
1129 )
1130 IS
1131 BEGIN
1132
1133 -- Initialize message list if p_init_msg_list is set to TRUE.
1134 IF FND_API.to_Boolean( p_init_msg_list )
1135 THEN
1136 FND_MSG_PUB.initialize;
1137 END IF;
1138
1139
1140 -- Initialize API return status to SUCCESS
1141 x_return_status := FND_API.G_RET_STS_SUCCESS;
1142
1143 -- validate NOT NULL column
1144 IF(p_QUANTITY is NULL)
1145 THEN
1146 JTF_PLSQL_API.Debug_Message(FND_MSG_PUB.G_MSG_LVL_ERROR, 'CSP', 'Private pack_serial_lots API: -Violate NOT NULL constraint(QUANTITY)');
1147 x_return_status := FND_API.G_RET_STS_ERROR;
1148 END IF;
1149
1150 IF(p_validation_mode = JTF_PLSQL_API.G_CREATE)
1151 THEN
1152 -- Hint: Validate data
1153 -- IF p_QUANTITY is not NULL and p_QUANTITY <> G_MISS_CHAR
1154 -- verify if data is valid
1155 -- if data is not valid : x_return_status := FND_API.G_RET_STS_ERROR;
1156 NULL;
1157 ELSIF(p_validation_mode = JTF_PLSQL_API.G_UPDATE)
1158 THEN
1159 -- Hint: Validate data
1160 -- IF p_QUANTITY <> G_MISS_CHAR
1161 -- verify if data is valid
1162 -- if data is not valid : x_return_status := FND_API.G_RET_STS_ERROR;
1163 NULL;
1164 END IF;
1165
1166 -- Standard call to get message count and if count is 1, get message info.
1167 FND_MSG_PUB.Count_And_Get
1168 ( p_count => x_msg_count,
1169 p_data => x_msg_data
1170 );
1171
1172 END Validate_QUANTITY;
1173
1174
1175 PROCEDURE Validate_LOT_NUMBER (
1176 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
1177 P_Validation_mode IN VARCHAR2,
1178 P_LOT_NUMBER IN VARCHAR2,
1179 -- 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.
1180 X_Return_Status OUT NOCOPY VARCHAR2,
1181 X_Msg_Count OUT NOCOPY NUMBER,
1182 X_Msg_Data OUT NOCOPY VARCHAR2
1183 )
1184 IS
1185 BEGIN
1186
1187 -- Initialize message list if p_init_msg_list is set to TRUE.
1188 IF FND_API.to_Boolean( p_init_msg_list )
1189 THEN
1190 FND_MSG_PUB.initialize;
1191 END IF;
1192
1193
1194 -- Initialize API return status to SUCCESS
1195 x_return_status := FND_API.G_RET_STS_SUCCESS;
1196
1197 IF(p_validation_mode = JTF_PLSQL_API.G_CREATE)
1198 THEN
1199 -- Hint: Validate data
1200 -- IF p_LOT_NUMBER is not NULL and p_LOT_NUMBER <> G_MISS_CHAR
1201 -- verify if data is valid
1202 -- if data is not valid : x_return_status := FND_API.G_RET_STS_ERROR;
1203 NULL;
1204 ELSIF(p_validation_mode = JTF_PLSQL_API.G_UPDATE)
1205 THEN
1206 -- Hint: Validate data
1207 -- IF p_LOT_NUMBER <> G_MISS_CHAR
1208 -- verify if data is valid
1209 -- if data is not valid : x_return_status := FND_API.G_RET_STS_ERROR;
1210 NULL;
1211 END IF;
1212
1213 -- Standard call to get message count and if count is 1, get message info.
1214 FND_MSG_PUB.Count_And_Get
1215 ( p_count => x_msg_count,
1216 p_data => x_msg_data
1217 );
1218
1219 END Validate_LOT_NUMBER;
1220
1221
1222 PROCEDURE Validate_SERIAL_NUMBER (
1223 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
1224 P_Validation_mode IN VARCHAR2,
1225 P_SERIAL_NUMBER IN VARCHAR2,
1226 -- 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.
1227 X_Return_Status OUT NOCOPY VARCHAR2,
1228 X_Msg_Count OUT NOCOPY NUMBER,
1229 X_Msg_Data OUT NOCOPY VARCHAR2
1230 )
1231 IS
1232 BEGIN
1233
1234 -- Initialize message list if p_init_msg_list is set to TRUE.
1235 IF FND_API.to_Boolean( p_init_msg_list )
1236 THEN
1237 FND_MSG_PUB.initialize;
1238 END IF;
1239
1240
1241 -- Initialize API return status to SUCCESS
1242 x_return_status := FND_API.G_RET_STS_SUCCESS;
1243
1244 IF(p_validation_mode = JTF_PLSQL_API.G_CREATE)
1245 THEN
1246 -- Hint: Validate data
1247 -- IF p_SERIAL_NUMBER is not NULL and p_SERIAL_NUMBER <> G_MISS_CHAR
1248 -- verify if data is valid
1249 -- if data is not valid : x_return_status := FND_API.G_RET_STS_ERROR;
1250 NULL;
1251 ELSIF(p_validation_mode = JTF_PLSQL_API.G_UPDATE)
1252 THEN
1253 -- Hint: Validate data
1254 -- IF p_SERIAL_NUMBER <> G_MISS_CHAR
1255 -- verify if data is valid
1259
1256 -- if data is not valid : x_return_status := FND_API.G_RET_STS_ERROR;
1257 NULL;
1258 END IF;
1260 -- Standard call to get message count and if count is 1, get message info.
1261 FND_MSG_PUB.Count_And_Get
1262 ( p_count => x_msg_count,
1263 p_data => x_msg_data
1264 );
1265
1266 END Validate_SERIAL_NUMBER;
1267
1268
1269 -- Hint: inter-field level validation can be added here.
1270 -- Hint: If p_validation_mode = JTF_PLSQL_API.G_VALIDATE_UPDATE, we should use cursor
1271 -- to get old values for all fields used in inter-field validation and set all G_MISS_XXX fields to original value
1272 -- stored in database table.
1273 PROCEDURE Validate_plsl_rec(
1274 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
1275 P_Validation_mode IN VARCHAR2,
1276 P_plsl_Rec IN plsl_Rec_Type,
1277 X_Return_Status OUT NOCOPY VARCHAR2,
1278 X_Msg_Count OUT NOCOPY NUMBER,
1279 X_Msg_Data OUT NOCOPY VARCHAR2
1280 )
1281 IS
1282 BEGIN
1283
1284 -- Initialize message list if p_init_msg_list is set to TRUE.
1285 IF FND_API.to_Boolean( p_init_msg_list )
1286 THEN
1287 FND_MSG_PUB.initialize;
1288 END IF;
1289
1290
1291 -- Initialize API return status to SUCCESS
1292 x_return_status := FND_API.G_RET_STS_SUCCESS;
1293
1294 -- Hint: Validate data
1295 -- If data not valid
1296 -- THEN
1297 -- x_return_status := FND_API.G_RET_STS_ERROR;
1298
1299 -- Debug Message
1300 JTF_PLSQL_API.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'CSP', 'API_INVALID_RECORD');
1301
1302 -- Standard call to get message count and if count is 1, get message info.
1303 FND_MSG_PUB.Count_And_Get
1304 ( p_count => x_msg_count,
1305 p_data => x_msg_data
1306 );
1307
1308 END Validate_plsl_Rec;
1309
1310 PROCEDURE Validate_pack_serial_lots(
1311 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
1312 P_Validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1313 P_Validation_mode IN VARCHAR2,
1314 P_plsl_Rec IN plsl_Rec_Type,
1315 X_Return_Status OUT NOCOPY VARCHAR2,
1316 X_Msg_Count OUT NOCOPY NUMBER,
1317 X_Msg_Data OUT NOCOPY VARCHAR2
1318 )
1319 IS
1320 l_api_name CONSTANT VARCHAR2(30) := 'Validate_pack_serial_lots';
1321 BEGIN
1322
1323 -- Debug Message
1324 JTF_PLSQL_API.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'CSP', 'Private API: ' || l_api_name || 'start');
1325
1326
1327 -- Initialize API return status to SUCCESS
1328 x_return_status := FND_API.G_RET_STS_SUCCESS;
1329
1330 IF (p_validation_level >= JTF_PLSQL_API.G_VALID_LEVEL_ITEM) THEN
1331 -- Hint: We provide validation procedure for every column. Developer should delete
1332 -- unnecessary validation procedures.
1333 Validate_PL_SERIAL_LOT_ID(
1334 p_init_msg_list => FND_API.G_FALSE,
1335 p_validation_mode => p_validation_mode,
1336 p_PACKLIST_SERIAL_LOT_ID => P_plsl_Rec.PACKLIST_SERIAL_LOT_ID,
1337 -- Hint: You may add x_item_property_rec as one of your OUT parameter if you'd like to pass back item property.
1338 x_return_status => x_return_status,
1339 x_msg_count => x_msg_count,
1340 x_msg_data => x_msg_data);
1341 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1342 raise FND_API.G_EXC_ERROR;
1343 END IF;
1344
1345 Validate_PACKLIST_LINE_ID(
1346 p_init_msg_list => FND_API.G_FALSE,
1347 p_validation_mode => p_validation_mode,
1348 p_PACKLIST_LINE_ID => P_plsl_Rec.PACKLIST_LINE_ID,
1349 -- Hint: You may add x_item_property_rec as one of your OUT parameter if you'd like to pass back item property.
1350 x_return_status => x_return_status,
1351 x_msg_count => x_msg_count,
1352 x_msg_data => x_msg_data);
1353 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1354 raise FND_API.G_EXC_ERROR;
1355 END IF;
1356
1357 Validate_ORGANIZATION_ID(
1358 p_init_msg_list => FND_API.G_FALSE,
1359 p_validation_mode => p_validation_mode,
1360 p_ORGANIZATION_ID => P_plsl_Rec.ORGANIZATION_ID,
1361 -- Hint: You may add x_item_property_rec as one of your OUT parameter if you'd like to pass back item property.
1362 x_return_status => x_return_status,
1363 x_msg_count => x_msg_count,
1364 x_msg_data => x_msg_data);
1365 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1366 raise FND_API.G_EXC_ERROR;
1367 END IF;
1368
1369 Validate_INVENTORY_ITEM_ID(
1370 p_init_msg_list => FND_API.G_FALSE,
1371 p_validation_mode => p_validation_mode,
1372 p_INVENTORY_ITEM_ID => P_plsl_Rec.INVENTORY_ITEM_ID,
1376 x_msg_data => x_msg_data);
1373 -- Hint: You may add x_item_property_rec as one of your OUT parameter if you'd like to pass back item property.
1374 x_return_status => x_return_status,
1375 x_msg_count => x_msg_count,
1377 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1378 raise FND_API.G_EXC_ERROR;
1379 END IF;
1380
1381 Validate_QUANTITY(
1382 p_init_msg_list => FND_API.G_FALSE,
1383 p_validation_mode => p_validation_mode,
1384 p_QUANTITY => P_plsl_Rec.QUANTITY,
1385 -- Hint: You may add x_item_property_rec as one of your OUT parameter if you'd like to pass back item property.
1386 x_return_status => x_return_status,
1387 x_msg_count => x_msg_count,
1388 x_msg_data => x_msg_data);
1389 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1390 raise FND_API.G_EXC_ERROR;
1391 END IF;
1392
1393 Validate_LOT_NUMBER(
1394 p_init_msg_list => FND_API.G_FALSE,
1395 p_validation_mode => p_validation_mode,
1396 p_LOT_NUMBER => P_plsl_Rec.LOT_NUMBER,
1397 -- Hint: You may add x_item_property_rec as one of your OUT parameter if you'd like to pass back item property.
1398 x_return_status => x_return_status,
1399 x_msg_count => x_msg_count,
1400 x_msg_data => x_msg_data);
1401 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1402 raise FND_API.G_EXC_ERROR;
1403 END IF;
1404
1405 Validate_SERIAL_NUMBER(
1406 p_init_msg_list => FND_API.G_FALSE,
1407 p_validation_mode => p_validation_mode,
1408 p_SERIAL_NUMBER => P_plsl_Rec.SERIAL_NUMBER,
1409 -- Hint: You may add x_item_property_rec as one of your OUT parameter if you'd like to pass back item property.
1410 x_return_status => x_return_status,
1411 x_msg_count => x_msg_count,
1412 x_msg_data => x_msg_data);
1413 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1414 raise FND_API.G_EXC_ERROR;
1415 END IF;
1416
1417 END IF;
1418
1419 IF (p_validation_level >= JTF_PLSQL_API.G_VALID_LEVEL_RECORD) THEN
1420 -- Hint: Inter-field level validation can be added here
1421 -- invoke record level validation procedures
1422 Validate_plsl_Rec(
1423 p_init_msg_list => FND_API.G_FALSE,
1424 p_validation_mode => p_validation_mode,
1425 P_plsl_Rec => P_plsl_Rec,
1426 x_return_status => x_return_status,
1427 x_msg_count => x_msg_count,
1428 x_msg_data => x_msg_data);
1429
1430 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1431 raise FND_API.G_EXC_ERROR;
1432 END IF;
1433 END IF;
1434
1435 IF (p_validation_level >= JTF_PLSQL_API.G_VALID_LEVEL_INTER_RECORD) THEN
1436 -- invoke inter-record level validation procedures
1437 NULL;
1438 END IF;
1439
1440 IF (p_validation_level >= JTF_PLSQL_API.G_VALID_LEVEL_INTER_ENTITY) THEN
1441 -- invoke inter-entity level validation procedures
1442 NULL;
1443 END IF;
1444
1445
1446 -- Debug Message
1447 JTF_PLSQL_API.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'CSP', 'Private API: ' || l_api_name || 'end');
1448
1449 END Validate_pack_serial_lots;
1450
1451 End CSP_Pack_Serial_Lots_PVT;