[Home] [Help]
PACKAGE BODY: APPS.CSP_PICK_SERIAL_LOTS_PUB
Source
1 PACKAGE BODY csp_pick_serial_lots_PUB AS
2 /* $Header: cspptslb.pls 115.4 2003/05/02 17:21:54 phegde ship $ */
3 -- Start of Comments
4 -- Package name : csp_pick_serial_lots_PUB
5 -- Purpose :
6 -- History :
7 -- NOTE :
8 -- End of Comments
9
10
11 G_PKG_NAME CONSTANT VARCHAR2(30):= 'csp_pick_serial_lots_PUB';
12 G_FILE_NAME CONSTANT VARCHAR2(12) := 'cspptslb.pls';
13
14 -- Start of Comments
15 -- ***************** Private Conversion Routines Values -> Ids **************
16 -- Purpose
17 --
18 -- This procedure takes a public pick_serial_lots record as input. It may contain
19 -- values or ids. All values are then converted into ids and a
20 -- private pick_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_psl_Values_To_Ids(
35 P_psl_Rec IN csp_pick_serial_lots_PUB.psl_Rec_Type,
36 x_pvt_psl_rec OUT NOCOPY csp_pick_serial_lots_PVT.psl_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 pick_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_psl_rec.PICKLIST_SERIAL_LOT_ID := P_psl_Rec.PICKLIST_SERIAL_LOT_ID;
91 x_pvt_psl_rec.CREATED_BY := P_psl_Rec.CREATED_BY;
92 x_pvt_psl_rec.CREATION_DATE := P_psl_Rec.CREATION_DATE;
93 x_pvt_psl_rec.LAST_UPDATED_BY := P_psl_Rec.LAST_UPDATED_BY;
94 x_pvt_psl_rec.LAST_UPDATE_DATE := P_psl_Rec.LAST_UPDATE_DATE;
95 x_pvt_psl_rec.LAST_UPDATE_LOGIN := P_psl_Rec.LAST_UPDATE_LOGIN;
96 x_pvt_psl_rec.PICKLIST_LINE_ID := P_psl_Rec.PICKLIST_LINE_ID;
97 x_pvt_psl_rec.ORGANIZATION_ID := P_psl_Rec.ORGANIZATION_ID;
98 x_pvt_psl_rec.INVENTORY_ITEM_ID := P_psl_Rec.INVENTORY_ITEM_ID;
99 x_pvt_psl_rec.QUANTITY := P_psl_Rec.QUANTITY;
100 x_pvt_psl_rec.LOT_NUMBER := P_psl_Rec.LOT_NUMBER;
101 x_pvt_psl_rec.SERIAL_NUMBER := P_psl_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_psl_Values_To_Ids;
111 PROCEDURE Create_pick_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_psl_Rec IN psl_Rec_Type := G_MISS_psl_REC,
116 --Hint: Add detail tables as parameter lists if it's master-detail relationship.
117 X_PICKLIST_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_pick_serial_lots';
125 l_api_version_number CONSTANT NUMBER := 2.0;
126 l_pvt_psl_rec csp_pick_serial_lots_PVT.psl_Rec_Type;
127 BEGIN
128 -- Standard Start of API savepoint
129 SAVEPOINT CREATE_pick_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_psl_Values_To_Ids');
161
162 -- Convert the values to ids
163 --
164 Convert_psl_Values_To_Ids (
165 p_psl_rec => p_psl_rec,
166 x_pvt_psl_rec => l_pvt_psl_rec
167 );
168
169 -- Calling Private package: Create_pick_serial_lots
170 -- Hint: Primary key needs to be returned
171 csp_pick_serial_lots_PVT.Create_pick_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_psl_Rec => l_pvt_psl_Rec ,
177 -- Hint: Add detail tables as parameter lists if it's master-detail relationship.
178 X_PICKLIST_SERIAL_LOT_ID => x_PICKLIST_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_pick_serial_lots;
244
245
246 -- Hint: Add corresponding update detail table procedures if it's master-detail relationship.
247 PROCEDURE Update_pick_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_psl_Rec IN psl_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_pick_serial_lots';
260 l_api_version_number CONSTANT NUMBER := 2.0;
261 l_pvt_psl_rec csp_pick_serial_lots_PVT.psl_Rec_Type;
262 BEGIN
263 -- Standard Start of API savepoint
264 SAVEPOINT UPDATE_pick_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_psl_Values_To_Ids');
296
297 -- Convert the values to ids
298 --
299 Convert_psl_Values_To_Ids (
300 p_psl_rec => p_psl_rec,
301 x_pvt_psl_rec => l_pvt_psl_rec
302 );
303
304 csp_pick_serial_lots_PVT.Update_pick_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_psl_Rec => l_pvt_psl_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
377
374 ,X_RETURN_STATUS => X_RETURN_STATUS);
375 End Update_pick_serial_lots;
376
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_pick_serial_lots(
381 P_Api_Version_Number IN NUMBER,
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,
385 P_psl_Rec IN psl_Rec_Type,
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_pick_serial_lots';
393 l_api_version_number CONSTANT NUMBER := 2.0;
394 l_pvt_psl_rec csp_pick_serial_lots_PVT.psl_Rec_Type;
395 BEGIN
396 -- Standard Start of API savepoint
397 SAVEPOINT DELETE_pick_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_psl_Values_To_Ids');
429
430 -- Convert the values to ids
431 --
432 Convert_psl_Values_To_Ids (
433 p_psl_rec => p_psl_rec,
434 x_pvt_psl_rec => l_pvt_psl_rec
435 );
436
437 csp_pick_serial_lots_PVT.Delete_pick_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_psl_Rec => l_pvt_psl_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
509
506 ,X_MSG_DATA => X_MSG_DATA
507 ,X_RETURN_STATUS => X_RETURN_STATUS);
508 End Delete_pick_serial_lots;
510
511 PROCEDURE Get_pick_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,
515 P_psl_Rec IN csp_pick_serial_lots_PUB.psl_Rec_Type,
516 -- Hint: Add list of bind variables here
517 p_rec_requested IN NUMBER := G_DEFAULT_NUM_REC_FETCH,
518 p_start_rec_prt IN NUMBER := 1,
519 p_return_tot_count IN NUMBER := FND_API.G_FALSE,
520 -- Hint: user defined record type
521 p_order_by_rec IN csp_pick_serial_lots_PUB.psl_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_psl_Tbl OUT NOCOPY csp_pick_serial_lots_PUB.psl_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_pick_serial_lots';
535 l_api_version_number CONSTANT NUMBER := 2.0;
536 BEGIN
537 -- Standard Start of API savepoint
538 SAVEPOINT GET_pick_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_pick_serial_lots');
569 /*
570 csp_pick_serial_lots_PVT.Get_pick_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_psl_Rec => P_psl_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_psl_Tbl => X_psl_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 -- Check return status from the above procedure call
594 IF x_return_status = FND_API.G_RET_STS_ERROR then
595 raise FND_API.G_EXC_ERROR;
596 elsif x_return_status = FND_API.G_RET_STS_UNEXP_ERROR then
597 raise FND_API.G_EXC_UNEXPECTED_ERROR;
598 END IF;
599 */
600 --
601 -- End of API body
602 --
603
604 -- Debug Message
605 JTF_PLSQL_API.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'csp', 'Public API: ' || l_api_name || 'end');
606
607
608 -- Standard call to get message count and if count is 1, get message info.
609 FND_MSG_PUB.Count_And_Get
610 ( p_count => x_msg_count,
611 p_data => x_msg_data
612 );
613
614 EXCEPTION
615 WHEN FND_API.G_EXC_ERROR THEN
616 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
617 P_API_NAME => L_API_NAME
618 ,P_PKG_NAME => G_PKG_NAME
619 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
620 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PUB
621 ,X_MSG_COUNT => X_MSG_COUNT
622 ,X_MSG_DATA => X_MSG_DATA
623 ,X_RETURN_STATUS => X_RETURN_STATUS);
624
625 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
626 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
627 P_API_NAME => L_API_NAME
628 ,P_PKG_NAME => G_PKG_NAME
629 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
630 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PUB
631 ,X_MSG_COUNT => X_MSG_COUNT
632 ,X_MSG_DATA => X_MSG_DATA
633 ,X_RETURN_STATUS => X_RETURN_STATUS);
634
635 WHEN OTHERS THEN
636 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
637 P_API_NAME => L_API_NAME
638 ,P_PKG_NAME => G_PKG_NAME
639 ,P_EXCEPTION_LEVEL => JTF_PLSQL_API.G_EXC_OTHERS
640 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PUB
641 ,X_MSG_COUNT => X_MSG_COUNT
642 ,X_MSG_DATA => X_MSG_DATA
643 ,X_RETURN_STATUS => X_RETURN_STATUS);
644 End Get_pick_serial_lots;
645
646
647 End csp_pick_serial_lots_PUB;