[Home] [Help]
PACKAGE BODY: APPS.CSP_PACK_SERIAL_LOTS_PUB
Source
1 PACKAGE BODY CSP_Pack_Serial_Lots_PUB AS
2 /* $Header: cspptspb.pls 115.5 2003/05/02 17:23:12 phegde ship $ */
3 -- Start of Comments
4 -- Package name : CSP_Pack_Serial_Lots_PUB
5 -- Purpose :
6 -- History :
7 -- NOTE :
8 -- End of Comments
9
10
11 G_PKG_NAME CONSTANT VARCHAR2(30):= 'CSP_Pack_Serial_Lots_PUB';
12 G_FILE_NAME CONSTANT VARCHAR2(12) := 'cspptspb.pls';
13
14 -- Start of Comments
15 -- ***************** Private Conversion Routines Values -> Ids **************
16 -- Purpose
17 --
18 -- This procedure takes a public Pack_Serial_Lots record as input. It may contain
19 -- values or ids. All values are then converted into ids and a
20 -- private Pack_Serial_Lotsrecord is returned for the private
21 -- API call.
22 --
23 -- Conversions:
24 --
25 -- Notes
26 --
27 -- 1. IDs take precedence over values. If both are present for a field, ID is used,
28 -- the value based parameter is ignored and a warning message is created.
29 -- 2. This is automatically generated procedure, it converts public record type to
30 -- private record type for all attributes.
31 -- Developer must manually add conversion logic to the attributes.
32 --
33 -- End of Comments
34 PROCEDURE Convert_plsl_Values_To_Ids(
35 P_plsl_Rec IN CSP_pack_serial_lots_PUB.plsl_Rec_Type,
36 x_pvt_plsl_rec OUT NOCOPY CSP_pack_serial_lots_PVT.plsl_Rec_Type
37 )
38 IS
39 -- Hint: Declare cursor and local variables
40 -- Example: CURSOR C_Get_Lookup_Code(X_Lookup_Type VARCHAR2, X_Meaning VARCHAR2) IS
41 -- SELECT lookup_code
42 -- FROM as_lookups
43 -- WHERE lookup_type = X_Lookup_Type and nls_upper(meaning) = nls_upper(X_Meaning);
44 l_any_errors BOOLEAN := FALSE;
45 BEGIN
46 -- Hint: Add logic to process value-id verification for Pack_Serial_Lots record.
47 -- Value based parameters should be converted to their equivalent ids.
48 -- Each value should resolve into one and only one id.
49 -- If this condition is not satisfied, API must report an error and assign l_any_errors to TRUE.
50 -- Example: Process Lead Source/Lead Source Code
51 -- If(p_opp_rec.lead_source_code is NOT NULL and p_opp_rec.lead_source_code <> FND_API.G_MISS_CHAR)
52 -- THEN
53 -- p_pvt_opp_rec.lead_source_code := p_opp_rec.lead_source_code;
54 -- IF(p_opp_rec.lead_source is NOT NULL and p_opp_rec.lead_source <> FND_API.G_MISS_CHAR)
55 -- THEN
56 -- IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_SUCCESS)
57 -- THEN
58 -- FND_MESSAGE.Set_Name('AS','API_ATTRIBUTE_IGNORED');
59 -- FND_MESSAGE.Set_Token('COLUMN','LEAD_SOURCE',FALSE);
60 -- FND_MSG_PUB.Add;
61 -- END IF;
62 -- END IF;
63 -- ELSIF(p_opp_rec.lead_source is NOT NULL and p_opp_rec.lead_source <> FND_API.G_MISS_CHAR)
64 -- THEN
65 -- OPEN C_Get_Lookup_Code('LEAD_SOURCE', p_opp_rec.lead_source);
66 -- FETCH C_Get_Lookup_Code INTO l_val;
67 -- CLOSE C_Get_Lookup_Code;
68 -- p_pvt_opp_rec.lead_source_code := l_val;
69 -- IF(l_val IS NULL)
70 -- THEN
71 -- IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
72 -- THEN
73 -- FND_MESSAGE.Set_Name('AS','API_ATTRIBUTE_CONVERSION_ERROR'
74 -- FND_MESSAGE.Set_Token('COLUMN','LEAD_SOURCE', FALSE
75 -- FND_MESSAGE.Set_Token('VALUE', p_opp_rec.lead_source, FALSE
76 -- FND_MSG_PUB.Add;
77 -- END IF;
78 -- l_any_errors := TRUE;
79 -- END IF;
80 -- ELSE
81 -- p_pvt_opp_rec.lead_source_code := NULL;
82 -- END IF;
83
84
85 -- Now copy the rest of the columns to the private record
86 -- Hint: We provide copy all columns to the private record.
87 -- Developer should delete those fields which are used by Value-Id conversion above
88 -- Hint: Developer should remove some of the following statements because of inconsistent column name between table and view.
89 /*
90 x_pvt_plsl_rec.PACKLIST_SERIAL_LOT_ID := P_plsl_Rec.PACKLIST_SERIAL_LOT_ID;
91 x_pvt_plsl_rec.CREATED_BY := P_plsl_Rec.CREATED_BY;
92 x_pvt_plsl_rec.CREATION_DATE := P_plsl_Rec.CREATION_DATE;
93 x_pvt_plsl_rec.LAST_UPDATED_BY := P_plsl_Rec.LAST_UPDATED_BY;
94 x_pvt_plsl_rec.LAST_UPDATE_DATE := P_plsl_Rec.LAST_UPDATE_DATE;
95 x_pvt_plsl_rec.LAST_UPDATE_LOGIN := P_plsl_Rec.LAST_UPDATE_LOGIN;
96 x_pvt_plsl_rec.PACKLIST_LINE_ID := P_plsl_Rec.PACKLIST_LINE_ID;
97 x_pvt_plsl_rec.ORGANIZATION_ID := P_plsl_Rec.ORGANIZATION_ID;
98 x_pvt_plsl_rec.INVENTORY_ITEM_ID := P_plsl_Rec.INVENTORY_ITEM_ID;
99 x_pvt_plsl_rec.QUANTITY := P_plsl_Rec.QUANTITY;
100 x_pvt_plsl_rec.LOT_NUMBER := P_plsl_Rec.LOT_NUMBER;
101 x_pvt_plsl_rec.SERIAL_NUMBER := P_plsl_Rec.SERIAL_NUMBER;
102 */
103
104 -- If there is an error in conversion precessing, raise an error.
105 IF l_any_errors
106 THEN
107 raise FND_API.G_EXC_ERROR;
108 END IF;
109
110 END Convert_plsl_Values_To_Ids;
111 PROCEDURE Create_pack_serial_lots(
112 P_Api_Version_Number IN NUMBER,
113 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
114 P_Commit IN VARCHAR2 := FND_API.G_FALSE,
115 P_plsl_Rec IN plsl_Rec_Type := G_MISS_plsl_REC,
116 --Hint: Add detail tables as parameter lists if it's master-detail relationship.
117 X_PACKLIST_SERIAL_LOT_ID OUT NOCOPY NUMBER,
118 X_Return_Status OUT NOCOPY VARCHAR2,
119 X_Msg_Count OUT NOCOPY NUMBER,
120 X_Msg_Data OUT NOCOPY VARCHAR2
121 )
122
123 IS
124 l_api_name CONSTANT VARCHAR2(30) := 'Create_pack_serial_lots';
125 l_api_version_number CONSTANT NUMBER := 2.0;
126 l_pvt_plsl_rec CSP_Pack_Serial_Lots_PVT.plsl_Rec_Type;
127 BEGIN
128 -- Standard Start of API savepoint
129 SAVEPOINT CREATE_Pack_Serial_Lots_PUB;
130
131 -- Standard call to check for call compatibility.
132 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
133 p_api_version_number,
134 l_api_name,
135 G_PKG_NAME)
136 THEN
137 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
138 END IF;
139
140
141 -- Initialize message list if p_init_msg_list is set to TRUE.
142 IF FND_API.to_Boolean( p_init_msg_list )
143 THEN
144 FND_MSG_PUB.initialize;
145 END IF;
146
147
148 -- Debug Message
149 JTF_PLSQL_API.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'CSP', 'Public API: ' || l_api_name || 'start');
150
151
152 -- Initialize API return status to SUCCESS
153 x_return_status := FND_API.G_RET_STS_SUCCESS;
154
155 --
156 -- API body
157 --
158
159 -- Debug Message
160 JTF_PLSQL_API.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'CSP', 'AS: Public API: Convert_plsl_Values_To_Ids');
161
162 -- Convert the values to ids
163 --
164 Convert_plsl_Values_To_Ids (
165 p_plsl_rec => p_plsl_rec,
166 x_pvt_plsl_rec => l_pvt_plsl_rec
167 );
168
169 -- Calling Private package: Create_Pack_Serial_Lots
170 -- Hint: Primary key needs to be returned
171 CSP_pack_serial_lots_PVT.Create_pack_serial_lots(
172 P_Api_Version_Number => 2.0,
173 P_Init_Msg_List => FND_API.G_FALSE,
174 P_Commit => FND_API.G_FALSE,
175 P_Validation_Level => FND_API.G_VALID_LEVEL_FULL,
176 P_plsl_Rec => l_pvt_plsl_Rec ,
177 -- Hint: Add detail tables as parameter lists if it's master-detail relationship.
178 X_PACKLIST_SERIAL_LOT_ID => x_PACKLIST_SERIAL_LOT_ID,
179 X_Return_Status => x_return_status,
180 X_Msg_Count => x_msg_count,
181 X_Msg_Data => x_msg_data);
182
183
184
185 -- Check return status from the above procedure call
186 IF x_return_status = FND_API.G_RET_STS_ERROR then
187 raise FND_API.G_EXC_ERROR;
188 elsif x_return_status = FND_API.G_RET_STS_UNEXP_ERROR then
189 raise FND_API.G_EXC_UNEXPECTED_ERROR;
190 END IF;
191
192 --
193 -- End of API body.
194 --
195
196 -- Standard check for p_commit
197 IF FND_API.to_Boolean( p_commit )
198 THEN
199 COMMIT WORK;
200 END IF;
201
202
203 -- Debug Message
204 JTF_PLSQL_API.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'CSP', 'Public API: ' || l_api_name || 'end');
205
206
207 -- Standard call to get message count and if count is 1, get message info.
208 FND_MSG_PUB.Count_And_Get
209 ( p_count => x_msg_count,
210 p_data => x_msg_data
211 );
212
213 EXCEPTION
214 WHEN FND_API.G_EXC_ERROR THEN
215 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
216 P_API_NAME => L_API_NAME
217 ,P_PKG_NAME => G_PKG_NAME
218 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
219 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PUB
220 ,X_MSG_COUNT => X_MSG_COUNT
221 ,X_MSG_DATA => X_MSG_DATA
222 ,X_RETURN_STATUS => X_RETURN_STATUS);
223
224 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
225 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
226 P_API_NAME => L_API_NAME
227 ,P_PKG_NAME => G_PKG_NAME
228 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
229 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PUB
230 ,X_MSG_COUNT => X_MSG_COUNT
231 ,X_MSG_DATA => X_MSG_DATA
232 ,X_RETURN_STATUS => X_RETURN_STATUS);
233
234 WHEN OTHERS THEN
235 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
236 P_API_NAME => L_API_NAME
237 ,P_PKG_NAME => G_PKG_NAME
238 ,P_EXCEPTION_LEVEL => JTF_PLSQL_API.G_EXC_OTHERS
239 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PUB
240 ,X_MSG_COUNT => X_MSG_COUNT
241 ,X_MSG_DATA => X_MSG_DATA
242 ,X_RETURN_STATUS => X_RETURN_STATUS);
243 End Create_pack_serial_lots;
244
245
246 -- Hint: Add corresponding update detail table procedures if it's master-detail relationship.
247 PROCEDURE Update_pack_serial_lots(
248 P_Api_Version_Number IN NUMBER,
249 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
250 P_Commit IN VARCHAR2 := FND_API.G_FALSE,
251 P_Identity_Salesforce_Id IN NUMBER := NULL,
252 P_plsl_Rec IN plsl_Rec_Type,
253 X_Return_Status OUT NOCOPY VARCHAR2,
254 X_Msg_Count OUT NOCOPY NUMBER,
255 X_Msg_Data OUT NOCOPY VARCHAR2
256 )
257
258 IS
259 l_api_name CONSTANT VARCHAR2(30) := 'Update_pack_serial_lots';
260 l_api_version_number CONSTANT NUMBER := 2.0;
261 l_pvt_plsl_rec CSP_Pack_Serial_Lots_PVT.plsl_Rec_Type;
262 BEGIN
263 -- Standard Start of API savepoint
264 SAVEPOINT UPDATE_Pack_Serial_Lots_PUB;
265
266 -- Standard call to check for call compatibility.
267 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
268 p_api_version_number,
269 l_api_name,
270 G_PKG_NAME)
271 THEN
272 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
273 END IF;
274
275
276 -- Initialize message list if p_init_msg_list is set to TRUE.
277 IF FND_API.to_Boolean( p_init_msg_list )
278 THEN
279 FND_MSG_PUB.initialize;
280 END IF;
281
282
283 -- Debug Message
284 JTF_PLSQL_API.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'CSP', 'Public API: ' || l_api_name || 'start');
285
286
287 -- Initialize API return status to SUCCESS
288 x_return_status := FND_API.G_RET_STS_SUCCESS;
289
290 --
291 -- API body
292 --
293
294 -- Debug Message
295 JTF_PLSQL_API.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'CSP', 'AS: Public API: Convert_plsl_Values_To_Ids');
296
297 -- Convert the values to ids
298 --
299 Convert_plsl_Values_To_Ids (
300 p_plsl_rec => p_plsl_rec,
301 x_pvt_plsl_rec => l_pvt_plsl_rec
302 );
303
304 CSP_pack_serial_lots_PVT.Update_pack_serial_lots(
305 P_Api_Version_Number => 2.0,
306 P_Init_Msg_List => FND_API.G_FALSE,
307 P_Commit => p_commit,
308 P_Validation_Level => FND_API.G_VALID_LEVEL_FULL,
309 P_Identity_Salesforce_Id => p_identity_salesforce_id,
310 P_plsl_Rec => l_pvt_plsl_Rec ,
311 X_Return_Status => x_return_status,
312 X_Msg_Count => x_msg_count,
313 X_Msg_Data => x_msg_data);
314
315
316
317 -- Check return status from the above procedure call
318 IF x_return_status = FND_API.G_RET_STS_ERROR then
319 raise FND_API.G_EXC_ERROR;
320 elsif x_return_status = FND_API.G_RET_STS_UNEXP_ERROR then
321 raise FND_API.G_EXC_UNEXPECTED_ERROR;
322 END IF;
323
324 --
325 -- End of API body
326 --
327
328 -- Standard check for p_commit
329 IF FND_API.to_Boolean( p_commit )
330 THEN
331 COMMIT WORK;
332 END IF;
333
334
335 -- Debug Message
336 JTF_PLSQL_API.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'CSP', 'Public API: ' || l_api_name || 'end');
337
338
339 -- Standard call to get message count and if count is 1, get message info.
340 FND_MSG_PUB.Count_And_Get
341 ( p_count => x_msg_count,
342 p_data => x_msg_data
343 );
344
345 EXCEPTION
346 WHEN FND_API.G_EXC_ERROR THEN
347 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
348 P_API_NAME => L_API_NAME
349 ,P_PKG_NAME => G_PKG_NAME
350 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
351 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PUB
352 ,X_MSG_COUNT => X_MSG_COUNT
353 ,X_MSG_DATA => X_MSG_DATA
354 ,X_RETURN_STATUS => X_RETURN_STATUS);
355
356 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
357 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
358 P_API_NAME => L_API_NAME
359 ,P_PKG_NAME => G_PKG_NAME
360 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
361 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PUB
362 ,X_MSG_COUNT => X_MSG_COUNT
363 ,X_MSG_DATA => X_MSG_DATA
364 ,X_RETURN_STATUS => X_RETURN_STATUS);
365
366 WHEN OTHERS THEN
367 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
368 P_API_NAME => L_API_NAME
369 ,P_PKG_NAME => G_PKG_NAME
370 ,P_EXCEPTION_LEVEL => JTF_PLSQL_API.G_EXC_OTHERS
371 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PUB
372 ,X_MSG_COUNT => X_MSG_COUNT
373 ,X_MSG_DATA => X_MSG_DATA
374 ,X_RETURN_STATUS => X_RETURN_STATUS);
375 End Update_pack_serial_lots;
376
377
378 -- Hint: Add corresponding delete detail table procedures if it's master-detail relationship.
379 -- The Master delete procedure may not be needed depends on different business requirements.
380 PROCEDURE Delete_pack_serial_lots(
381 P_Api_Version_Number IN NUMBER,
385 P_plsl_Rec IN plsl_Rec_Type,
382 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
383 P_Commit IN VARCHAR2 := FND_API.G_FALSE,
384 P_identity_salesforce_id IN NUMBER := NULL,
386 X_Return_Status OUT NOCOPY VARCHAR2,
387 X_Msg_Count OUT NOCOPY NUMBER,
388 X_Msg_Data OUT NOCOPY VARCHAR2
389 )
390
391 IS
392 l_api_name CONSTANT VARCHAR2(30) := 'Delete_pack_serial_lots';
393 l_api_version_number CONSTANT NUMBER := 2.0;
394 l_pvt_plsl_rec CSP_Pack_Serial_Lots_PVT.plsl_Rec_Type;
395 BEGIN
396 -- Standard Start of API savepoint
397 SAVEPOINT DELETE_Pack_Serial_Lots_PUB;
398
399 -- Standard call to check for call compatibility.
400 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
401 p_api_version_number,
402 l_api_name,
403 G_PKG_NAME)
404 THEN
405 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
406 END IF;
407
408
409 -- Initialize message list if p_init_msg_list is set to TRUE.
410 IF FND_API.to_Boolean( p_init_msg_list )
411 THEN
412 FND_MSG_PUB.initialize;
413 END IF;
414
415
416 -- Debug Message
417 JTF_PLSQL_API.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'CSP', 'Public API: ' || l_api_name || 'start');
418
419
420 -- Initialize API return status to SUCCESS
421 x_return_status := FND_API.G_RET_STS_SUCCESS;
422
423 --
424 -- API body
425 --
426
427 -- Debug Message
428 JTF_PLSQL_API.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'CSP', 'AS: Public API: Convert_plsl_Values_To_Ids');
429
430 -- Convert the values to ids
431 --
432 Convert_plsl_Values_To_Ids (
433 p_plsl_rec => p_plsl_rec,
434 x_pvt_plsl_rec => l_pvt_plsl_rec
435 );
436
437 CSP_pack_serial_lots_PVT.Delete_pack_serial_lots(
438 P_Api_Version_Number => 2.0,
439 P_Init_Msg_List => FND_API.G_FALSE,
440 P_Commit => p_commit,
441 P_Validation_Level => FND_API.G_VALID_LEVEL_FULL,
442 P_Identity_Salesforce_Id => p_identity_salesforce_id,
443 P_plsl_Rec => l_pvt_plsl_Rec,
444 X_Return_Status => x_return_status,
445 X_Msg_Count => x_msg_count,
446 X_Msg_Data => x_msg_data);
447
448
449
450 -- Check return status from the above procedure call
451 IF x_return_status = FND_API.G_RET_STS_ERROR then
452 raise FND_API.G_EXC_ERROR;
453 elsif x_return_status = FND_API.G_RET_STS_UNEXP_ERROR then
454 raise FND_API.G_EXC_UNEXPECTED_ERROR;
455 END IF;
456
457 --
458 -- End of API body
459 --
460
461 -- Standard check for p_commit
462 IF FND_API.to_Boolean( p_commit )
463 THEN
464 COMMIT WORK;
465 END IF;
466
467
468 -- Debug Message
469 JTF_PLSQL_API.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'CSP', 'Public API: ' || l_api_name || 'end');
470
471
472 -- Standard call to get message count and if count is 1, get message info.
473 FND_MSG_PUB.Count_And_Get
474 ( p_count => x_msg_count,
475 p_data => x_msg_data
476 );
477
478 EXCEPTION
479 WHEN FND_API.G_EXC_ERROR THEN
480 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
481 P_API_NAME => L_API_NAME
482 ,P_PKG_NAME => G_PKG_NAME
483 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
484 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PUB
485 ,X_MSG_COUNT => X_MSG_COUNT
486 ,X_MSG_DATA => X_MSG_DATA
487 ,X_RETURN_STATUS => X_RETURN_STATUS);
488
489 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
490 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
491 P_API_NAME => L_API_NAME
492 ,P_PKG_NAME => G_PKG_NAME
493 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
494 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PUB
495 ,X_MSG_COUNT => X_MSG_COUNT
496 ,X_MSG_DATA => X_MSG_DATA
497 ,X_RETURN_STATUS => X_RETURN_STATUS);
498
499 WHEN OTHERS THEN
500 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
501 P_API_NAME => L_API_NAME
502 ,P_PKG_NAME => G_PKG_NAME
503 ,P_EXCEPTION_LEVEL => JTF_PLSQL_API.G_EXC_OTHERS
504 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PUB
505 ,X_MSG_COUNT => X_MSG_COUNT
506 ,X_MSG_DATA => X_MSG_DATA
507 ,X_RETURN_STATUS => X_RETURN_STATUS);
508 End Delete_pack_serial_lots;
509
510
511 PROCEDURE Get_pack_serial_lots(
512 P_Api_Version_Number IN NUMBER,
513 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
514 P_identity_salesforce_id IN NUMBER := NULL,
518 p_start_rec_prt IN NUMBER := 1,
515 P_plsl_Rec IN CSP_pack_serial_lots_PUB.plsl_Rec_Type,
516 -- Hint: Add list of bind variables here
517 p_rec_requested IN NUMBER := G_DEFAULT_NUM_REC_FETCH,
519 p_return_tot_count IN NUMBER := FND_API.G_FALSE,
520 -- Hint: user defined record type
521 p_order_by_rec IN CSP_pack_serial_lots_PUB.plsl_sort_rec_type,
522 x_return_status OUT NOCOPY VARCHAR2,
523 x_msg_count OUT NOCOPY NUMBER,
524 x_msg_data OUT NOCOPY VARCHAR2,
525 X_plsl_Tbl OUT NOCOPY CSP_pack_serial_lots_PUB.plsl_Tbl_Type,
526 x_returned_rec_count OUT NOCOPY NUMBER,
527 x_next_rec_ptr OUT NOCOPY NUMBER,
528 x_tot_rec_count OUT NOCOPY NUMBER
529 -- other optional parameters
530 -- x_tot_rec_amount OUT NOCOPY NUMBER
531 )
532
533 IS
534 l_api_name CONSTANT VARCHAR2(30) := 'Get_pack_serial_lots';
535 l_api_version_number CONSTANT NUMBER := 2.0;
536 BEGIN
537 -- Standard Start of API savepoint
538 SAVEPOINT GET_Pack_Serial_Lots_PUB;
539
540 -- Standard call to check for call compatibility.
541 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
542 p_api_version_number,
543 l_api_name,
544 G_PKG_NAME)
545 THEN
546 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
547 END IF;
548
549
550 -- Initialize message list if p_init_msg_list is set to TRUE.
551 IF FND_API.to_Boolean( p_init_msg_list )
552 THEN
553 FND_MSG_PUB.initialize;
554 END IF;
555
556
557 -- Debug Message
558 JTF_PLSQL_API.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'CSP', 'Public API: ' || l_api_name || 'start');
559
560
561 -- Initialize API return status to SUCCESS
562 x_return_status := FND_API.G_RET_STS_SUCCESS;
563
564 --
565 -- API body
566 --
567 -- Debug Message
568 JTF_PLSQL_API.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'CSP', 'Public API: - Calling PVT.Get_Pack_Serial_Lots');
569 /*
570 CSP_pack_serial_lots_PVT.Get_pack_serial_lots(
571 P_Api_Version_Number => 2.0,
572 P_Init_Msg_List => FND_API.G_FALSE,
573 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
574 P_Identity_Salesforce_id => p_identity_salesforce_id,
575 P_plsl_Rec => P_plsl_Rec,
576 p_rec_requested => p_rec_requested,
577 p_start_rec_prt => p_start_rec_prt,
578 p_return_tot_count => p_return_tot_count,
579 -- Hint: user defined record type
580 p_order_by_rec => p_order_by_rec,
581 X_Return_Status => x_return_status,
582 X_Msg_Count => x_msg_count,
583 X_Msg_Data => x_msg_data,
584 X_plsl_Tbl => X_plsl_Tbl,
585 x_returned_rec_count => x_returned_rec_count,
586 x_next_rec_ptr => x_next_rec_ptr,
587 x_tot_rec_count => x_tot_rec_count
588 -- other optional parameters
589 -- x_tot_rec_amount => x_tot_rec_amount
590 );
591
592
593
594 -- Check return status from the above procedure call
595 IF x_return_status = FND_API.G_RET_STS_ERROR then
596 raise FND_API.G_EXC_ERROR;
597 elsif x_return_status = FND_API.G_RET_STS_UNEXP_ERROR then
598 raise FND_API.G_EXC_UNEXPECTED_ERROR;
599 END IF;
600 */
601 --
602 -- End of API body
603 --
604
605 -- Debug Message
606 JTF_PLSQL_API.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'CSP', 'Public API: ' || l_api_name || 'end');
607
608
609 -- Standard call to get message count and if count is 1, get message info.
610 FND_MSG_PUB.Count_And_Get
611 ( p_count => x_msg_count,
612 p_data => x_msg_data
613 );
614
615 EXCEPTION
616 WHEN FND_API.G_EXC_ERROR THEN
617 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
618 P_API_NAME => L_API_NAME
619 ,P_PKG_NAME => G_PKG_NAME
620 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
621 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PUB
622 ,X_MSG_COUNT => X_MSG_COUNT
623 ,X_MSG_DATA => X_MSG_DATA
624 ,X_RETURN_STATUS => X_RETURN_STATUS);
625
626 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
627 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
628 P_API_NAME => L_API_NAME
629 ,P_PKG_NAME => G_PKG_NAME
630 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
631 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PUB
632 ,X_MSG_COUNT => X_MSG_COUNT
633 ,X_MSG_DATA => X_MSG_DATA
634 ,X_RETURN_STATUS => X_RETURN_STATUS);
635
636 WHEN OTHERS THEN
637 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
638 P_API_NAME => L_API_NAME
639 ,P_PKG_NAME => G_PKG_NAME
640 ,P_EXCEPTION_LEVEL => JTF_PLSQL_API.G_EXC_OTHERS
641 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PUB
642 ,X_MSG_COUNT => X_MSG_COUNT
643 ,X_MSG_DATA => X_MSG_DATA
644 ,X_RETURN_STATUS => X_RETURN_STATUS);
645 End Get_pack_serial_lots;
646
647
648 End CSP_Pack_Serial_Lots_PUB;