[Home] [Help]
PACKAGE BODY: APPS.JTF_QUALIFIER_PUB
Source
1 PACKAGE BODY JTF_QUALIFIER_PUB AS
2 /* $Header: jtfptrqb.pls 120.0 2005/06/02 18:20:52 appldev ship $ */
3
4 -- ---------------------------------------------------
5 -- Start of Comments
6 -- ---------------------------------------------------
7 -- PACKAGE NAME: JTF_QUALIFIER_PUB
8 -- ---------------------------------------------------
9 -- PURPOSE
10 -- Joint task force core territory manager public api's.
11 -- This package is a public API for inserting, updating and deleting
12 -- qualifier related information into JTF tables.
13 -- It contains specification for pl/sql records and tables
14 -- and the Public territory related API's.
15 --
16 -- Procedures:
17 --
18 --
19 -- NOTES
20 -- This package is publicly available for use
21 --
22 -- HISTORY
23 -- 07/15/99 JDOCHERT Created
24 --
25 -- End of Comments
26
27 G_PKG_NAME CONSTANT VARCHAR2(30):='JTF_QUALIFIER_PUB';
28 G_FILE_NAME CONSTANT VARCHAR2(12):='jtfptrqb.pls';
29
30
31
32 -- ***************************************************
33 -- start of comments
34 -- ***************************************************
35 --
36 -- API name : Create_Qualifier
37 -- Type : PUBLIC
38 -- Function : To create qualifiers
39 --
40 -- Pre-reqs : None
41 -- Parameters:
42 -- IN :
43 -- Required
44 -- Parameter Name Data Type Default
45 -- p_api_version NUMBER
46 -- P_Seed_Qual_Rec Seed_Qual_Rec_Type G_MISS_SEED_QUAL_REC
47 -- p_Qual_Usgs_Rec Qual_Usgs_All_Rec_Type G_MISS_QUAL_USGS_ALL_REC
48 --
49 -- Optional
50 -- Parameter Name Data Type Default
51 -- P_Init_Msg_List VARCHAR2 FND_API.G_FALSE
52 -- P_Commit VARCHAR2 FND_API.G_FALSE
53 --
54 -- OUT :
55 -- Parameter Name Data Type Default
56 -- x_Return_Status VARCHAR2(1)
57 -- x_Msg_Count NUMBER
58 -- x_Msg_Data VARCHAR2(2000)
59 -- x_Seeded_Qual_Id NUMBER
60 -- x_Qual_Usgs_Id NUMBER
61 --
62 -- Version:
63 -- Initial version 1.0
64 --
65 --
66 -- Notes:
67 --
68 --
69 -- End of Comments
70 --
71 PROCEDURE Create_Qualifier
72 (p_Api_Version IN NUMBER,
73 -- commented out eihsu 11/04
74 p_Init_Msg_List IN VARCHAR2 ,-- := FND_API.G_FALSE,
75 p_Commit IN VARCHAR2 ,-- := FND_API.G_FALSE,
76 x_return_status OUT NOCOPY VARCHAR2,
77 x_Msg_Count OUT NOCOPY NUMBER,
78 x_Msg_Data OUT NOCOPY VARCHAR2,
79 -- commented out eihsu 11/04
80 p_Seed_Qual_Rec IN Seed_Qual_Rec_Type ,-- := G_MISS_SEED_QUAL_REC,
81 p_Qual_Usgs_Rec IN Qual_Usgs_All_Rec_Type ,-- := G_MISS_QUAL_USGS_ALL_REC,
82 x_Seed_Qual_Rec OUT NOCOPY Seed_Qual_Out_Rec_Type,
83 x_Qual_Usgs_Rec OUT NOCOPY Qual_Usgs_All_Out_Rec_Type
84 )
85 IS
86
87 l_api_name CONSTANT VARCHAR2(30) := 'Create_Qualifier';
88 l_api_version CONSTANT NUMBER := 1.0;
89 l_return_status VARCHAR2(1);
90
91 BEGIN
92 ----plsdbg.put('QUALPUB.CREATE_QUALIFIER: BEGIN _______________________________');
93
94 -- Standard Start of API savepoint;
95 SAVEPOINT CREATE_QUALIFIER_PUB;
96
97 -- Standard call to check for call compatibility.
98 IF NOT FND_API.Compatible_API_Call (
99 l_api_version,
100 p_api_version ,
101 l_api_name,
102 G_PKG_NAME)
103 THEN
104 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
105 END IF;
106
107 -- Initialize message list if p_init_msg_list is set to TRUE.
108 IF FND_API.to_Boolean( p_init_msg_list )
109 THEN
110 FND_MSG_PUB.initialize;
111 END IF;
112
113 -- Debug Message
114 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
115 THEN
116 FND_MESSAGE.Set_Name('JTF', 'Pub Qual API: Start');
117 FND_MSG_PUB.Add;
118 END IF;
119
120 -------------------------------------------------------------------------------------------------------
121 -- Start of API body.
122 -------------------------------------------------------------------------------------------------------
123
124 -- Initialize API return status to success
125 l_return_status := FND_API.G_RET_STS_SUCCESS;
126
127 ----plsdbg.put('QUALPUB.CREATE_QUALIFIER: calling PRIVATE.Create_Qualifier ');
128
129 -- All of the values have been converted, so call the Create_Qualifier Private API
130 JTF_QUALIFIER_PVT.Create_Qualifier ( p_api_version => 1.0,
131 p_Init_Msg_List => FND_API.G_FALSE,
132 p_Commit => FND_API.G_FALSE,
133 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
134 x_Return_Status => l_return_status,
135 x_Msg_Count => x_msg_count,
136 x_Msg_Data => x_msg_data,
137 p_Seed_Qual_Rec => p_seed_qual_rec,
138 p_Qual_Usgs_Rec => p_qual_usgs_rec,
139 x_Seed_Qual_Rec => x_seed_qual_rec,
140 x_Qual_Usgs_Rec => x_qual_usgs_rec);
141
142 --dbms_output.put_line('After calling private JTF_QUALIFIER_PVT.Create_Qualifier ');
143
144 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
145 RAISE FND_API.G_EXC_ERROR;
146 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
147 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
148 END IF;
149
150 /* save return status */
151 x_return_status := l_return_status;
152
153 -------------------------------------------------------------------------------------------------------
154 -- End of API body.
155 -------------------------------------------------------------------------------------------------------
156
157 -- Standard check of p_commit.
158 IF FND_API.To_Boolean ( p_commit )
159 THEN
160 COMMIT WORK;
161 END IF;
162
163 -- Debug Message
164 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
165 THEN
166 FND_MESSAGE.Set_Name('JTF', 'Pub Qualifier API: End');
167 FND_MSG_PUB.Add;
168 END IF;
169
170 -- Standard call to get message count and if count is 1, get message info.
171 FND_MSG_PUB.Count_And_Get
172 ( p_count => x_msg_count,
173 p_data => x_msg_data
174 );
175 ----plsdbg.put('QUALPUB.CREATE_QUALIFIER: END');
176
177
178 EXCEPTION
179
180 WHEN FND_API.G_EXC_ERROR THEN
181 ROLLBACK TO CREATE_QUALIFIER_PUB;
182 x_return_status := FND_API.G_RET_STS_ERROR ;
183
184 FND_MSG_PUB.Count_And_Get
185 ( p_count => x_msg_count,
186 p_data => x_msg_data
187 );
188
189 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
190 ROLLBACK TO CREATE_QUALIFIER_PUB;
191 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
192
193 FND_MSG_PUB.Count_And_Get
194 ( p_count => x_msg_count,
195 p_data => x_msg_data
196 );
197
198
199 WHEN OTHERS THEN
200 ROLLBACK TO CREATE_QUALIFIER_PUB;
201 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
202
203 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
204 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
205 END IF;
206
207 FND_MSG_PUB.Count_And_Get
208 ( p_count => x_msg_count,
209 p_data => x_msg_data
210 );
211
212 END Create_Qualifier;
213
214
215
216 -- ***************************************************
217 -- start of comments
218 -- ***************************************************
219 --
220 -- API name : Update_Qualifier
221 -- Type : PUBLIC
222 -- Function : To update existing qualifiers
223 --
224 -- Pre-reqs :
225 -- Parameters:
226 -- IN :
227 -- Required
228 -- Parameter Name Data Type Default
229 -- p_api_version NUMBER
230 -- P_Seed_Qual_Rec Seed_Qual_Rec_Type
231 -- p_Qual_Usgs_Rec Qual_Usgs_All_Rec_Type
232 --
233 -- Optional
234 -- Parameter Name Data Type Default
235 -- P_Init_Msg_List VARCHAR2 FND_API.G_FALSE
236 -- P_Commit VARCHAR2 FND_API.G_FALSE
237 --
238 -- OUT :
239 -- Parameter Name Data Type
240 -- x_Return_Status VARCHAR2(1)
241 -- x_Msg_Count NUMBER
242 -- x_Msg_Data VARCHAR2(2000)
243 -- x_Seed_Qual_Rec Seed_Qual_Out_Rec_Type,
244 -- x_Qual_Usgs_Rec Qual_Usgs_All_Out_Rec_Type);
245 --
246 --
247 -- Notes:
248 --
249 --
250 -- End of Comments
251 --
252 PROCEDURE Update_Qualifier
253 (p_api_version IN NUMBER,
254 p_Init_Msg_List IN VARCHAR2, -- 2372550: JDOCHERT : 05/1402 -- := FND_API.G_FALSE,
255 p_Commit IN VARCHAR2, -- 2372550: JDOCHERT : 05/1402 -- := FND_API.G_FALSE,
256 x_Return_Status OUT NOCOPY VARCHAR2,
257 x_Msg_Count OUT NOCOPY NUMBER,
258 x_Msg_Data OUT NOCOPY VARCHAR2,
259 p_Seed_Qual_Rec IN Seed_Qual_Rec_Type,
260 p_Qual_Usgs_Rec IN Qual_Usgs_All_Rec_Type,
261 x_Seed_Qual_Rec OUT NOCOPY Seed_Qual_Out_Rec_Type,
262 x_Qual_Usgs_Rec OUT NOCOPY Qual_Usgs_All_Out_Rec_Type
263 )
264 IS
265
266 l_api_name CONSTANT VARCHAR2(30) := 'Update_Qualifier';
267 l_api_version CONSTANT NUMBER := 1.0;
268 l_return_status VARCHAR2(1);
269
270
271 BEGIN
272 ----plsdbg.put('QUALPUB.UPDATE_QUALIFIER: BEGIN _______________________________');
273 ----plsdbg.put('Received input include the following');
274 ----plsdbg.put('p_Seed_Qual_Rec.Name: '|| p_Seed_Qual_Rec.Name);
275 ----plsdbg.put('p_Seed_Qual_Rec.Description: '|| p_Seed_Qual_Rec.Description);
276 ----plsdbg.put('p_Seed_Qual_Rec.Seeded_Qual_Id: '|| p_Seed_Qual_Rec.Seeded_Qual_Id);
277 ----plsdbg.put('p_Qual_Usgs_Rec.Qual_Usg_Id: '|| p_Qual_Usgs_Rec.Qual_Usg_Id);
278
279
280 -- Standard Start of API savepoint;
281 SAVEPOINT UPDATE_QUALIFIER_PUB;
282
283 -- Standard call to check for call compatibility.
284 IF NOT FND_API.Compatible_API_Call (
285 l_api_version,
286 p_api_version ,
287 l_api_name,
288 G_PKG_NAME)
289 THEN
290 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
291 END IF;
292
293 -- Initialize message list if p_init_msg_list is set to TRUE.
294 IF FND_API.to_Boolean( p_init_msg_list )
295 THEN
296 FND_MSG_PUB.initialize;
297 END IF;
298
299 -- Debug Message
300 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
301 THEN
302 FND_MESSAGE.Set_Name('JTF', 'Pub Qual API: Start');
303 FND_MSG_PUB.Add;
304 END IF;
305
306 -------------------------------------------------------------------------------------------------------
307 -- Start of API body.
308 -------------------------------------------------------------------------------------------------------
309
310 -- Initialize API return status to success
311 l_return_status := FND_API.G_RET_STS_SUCCESS;
312
313 ----plsdbg.put('QUALPUB.UPDATE_QUAL: Calling Update Qual in PVT package');
314
315 -- All of the values have been converted, so call the Create_Qualifier Private API
316 JTF_QUALIFIER_PVT.Update_Qualifier ( p_api_version => 1.0,
317 p_Init_Msg_List => FND_API.G_FALSE,
318 p_Commit => FND_API.G_FALSE,
319 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
320 x_Return_Status => l_return_status,
321 x_Msg_Count => x_msg_count,
322 x_Msg_Data => x_msg_data,
323 p_Seed_Qual_Rec => p_seed_qual_rec,
324 p_Qual_Usgs_Rec => p_qual_usgs_rec,
325 x_Seed_Qual_Rec => x_seed_qual_rec,
326 x_Qual_Usgs_Rec => x_qual_usgs_rec);
327
328 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
329 RAISE FND_API.G_EXC_ERROR;
330 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
331 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
332 END IF;
333
334 /* save return status */
335 x_return_status := l_return_status;
336
337 ----plsdbg.put('QUALPUB.UPDATE_QUAL: END - l_return_status: ' || l_return_status);
338
339 -------------------------------------------------------------------------------------------------------
340 -- End of API body.
341 -------------------------------------------------------------------------------------------------------
342
343 -- Standard check of p_commit.
344 IF FND_API.To_Boolean ( p_commit )
345 THEN
346 COMMIT WORK;
347 END IF;
348
349 -- Debug Message
350 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
351 THEN
352 FND_MESSAGE.Set_Name('JTF', 'Pub Qualifier API: End');
353 FND_MSG_PUB.Add;
354 END IF;
355
356 -- Standard call to get message count and if count is 1, get message info.
357 FND_MSG_PUB.Count_And_Get
358 ( p_count => x_msg_count,
359 p_data => x_msg_data
360 );
361
362
363 EXCEPTION
364
365 WHEN FND_API.G_EXC_ERROR THEN
366 ROLLBACK TO UPDATE_QUALIFIER_PUB;
367 x_return_status := FND_API.G_RET_STS_ERROR ;
368
369 FND_MSG_PUB.Count_And_Get
370 ( p_count => x_msg_count,
371 p_data => x_msg_data
372 );
373
374 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
375 ROLLBACK TO UPDATE_QUALIFIER_PUB;
376 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
377
378 FND_MSG_PUB.Count_And_Get
379 ( p_count => x_msg_count,
380 p_data => x_msg_data
381 );
382
383
384 WHEN OTHERS THEN
385 ROLLBACK TO UPDATE_QUALIFIER_PUB;
386 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
387
388 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
389 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
390 END IF;
391
392 FND_MSG_PUB.Count_And_Get
393 ( p_count => x_msg_count,
394 p_data => x_msg_data
395 );
396
397 END Update_Qualifier;
398
399
400
401 -- ***************************************************
402 -- start of comments
403 -- ***************************************************
404 --
405 -- API name : Delete_Qualifier
406 -- Type : PUBLIC
407 -- Function : To delete an existing qualifiers
408 --
409 -- Pre-reqs :
410 -- Parameters:
411 -- IN :
412 -- Required
413 -- Parameter Name Data Type Default
414 -- p_api_version NUMBER
415 -- P_Seeded_Qual_Id NUMBER
416 -- p_Qual_Usgs_Id NUMBER
417 --
418 -- Optional
419 -- Parameter Name Data Type Default
420 -- P_Init_Msg_List VARCHAR2 FND_API.G_FALSE
421 -- P_Commit VARCHAR2 FND_API.G_FALSE
422 --
423 -- OUT :
424 -- Parameter Name Data Type
425 -- x_Return_Status VARCHAR2(1)
426 -- x_Msg_Count NUMBER
427 -- x_Msg_Data VARCHAR2(2000)
428 --
429 --
430 -- Notes:
431 --
432 --
433 -- End of Comments
434 --
435 PROCEDURE Delete_Qualifier
436 (p_api_version IN NUMBER,
437 p_Init_Msg_List IN VARCHAR2, -- 2372550: JDOCHERT : 05/1402 -- := FND_API.G_FALSE,
438 p_Commit IN VARCHAR2, -- 2372550: JDOCHERT : 05/1402 -- := FND_API.G_FALSE,
439 x_Return_Status OUT NOCOPY VARCHAR2,
440 x_Msg_Count OUT NOCOPY NUMBER,
441 x_Msg_Data OUT NOCOPY VARCHAR2,
442 p_Seeded_Qual_Id IN NUMBER,
443 p_Qual_Usg_Id IN NUMBER
444 )
445 IS
446
447 l_api_name CONSTANT VARCHAR2(30) := 'Delete_Qualifier';
448 l_api_version CONSTANT NUMBER := 1.0;
449 l_return_status VARCHAR2(1);
450
451
452 BEGIN
453 ----plsdbg.put('QUALPUB.DELETE_QUALIFIER: BEGIN');
454 -- Standard Start of API savepoint;
455 SAVEPOINT DELETE_QUALIFIER_PUB;
456
457 -- Standard call to check for call compatibility.
458 IF NOT FND_API.Compatible_API_Call (
459 l_api_version,
460 p_api_version ,
461 l_api_name,
462 G_PKG_NAME)
463 THEN
464 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
465 END IF;
466
467 -- Initialize message list if p_init_msg_list is set to TRUE.
468 IF FND_API.to_Boolean( p_init_msg_list )
469 THEN
470 FND_MSG_PUB.initialize;
471 END IF;
472
473 -- Debug Message
474 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
475 THEN
476 FND_MESSAGE.Set_Name('JTF', 'Pub Qual API: Start');
477 FND_MSG_PUB.Add;
478 END IF;
479
480 -------------------------------------------------------------------------------------------------------
481 -- Start of API body.
482 -------------------------------------------------------------------------------------------------------
483
484 -- Initialize API return status to success
485 l_return_status := FND_API.G_RET_STS_SUCCESS;
486
487
488 -- All of the values have been converted, so call the Create_Qualifier Private API
489 JTF_QUALIFIER_PVT.Delete_Qualifier ( p_api_version => 1.0,
490 p_Init_Msg_List => FND_API.G_FALSE,
491 p_Commit => FND_API.G_FALSE,
492 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
493 x_Return_Status => l_return_status,
494 x_Msg_Count => x_msg_count,
495 x_Msg_Data => x_msg_data,
496 p_Seeded_Qual_Id => p_seeded_qual_id,
497 p_Qual_Usg_Id => p_qual_usg_id);
498
499 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
500 RAISE FND_API.G_EXC_ERROR;
501 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
502 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
503 END IF;
504
505 /* save return status */
506 x_return_status := l_return_status;
507
508 ----plsdbg.put('QUALPUB.DELETE_QUALIFIER: END - l_return_status: ' || l_return_status);
509
510 -------------------------------------------------------------------------------------------------------
511 -- End of API body.
512 -------------------------------------------------------------------------------------------------------
513
514 -- Standard check of p_commit.
515 IF FND_API.To_Boolean ( p_commit )
516 THEN
517 COMMIT WORK;
518 END IF;
519
520 -- Debug Message
521 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
522 THEN
523 FND_MESSAGE.Set_Name('JTF', 'Pub Qualifier API: End');
524 FND_MSG_PUB.Add;
525 END IF;
526
527 -- Standard call to get message count and if count is 1, get message info.
528 FND_MSG_PUB.Count_And_Get
529 ( p_count => x_msg_count,
530 p_data => x_msg_data
531 );
532
533
534 EXCEPTION
535
536 WHEN FND_API.G_EXC_ERROR THEN
537 ROLLBACK TO DELETE_QUALIFIER_PUB;
538 x_return_status := FND_API.G_RET_STS_ERROR ;
539
540 FND_MSG_PUB.Count_And_Get
541 ( p_count => x_msg_count,
542 p_data => x_msg_data
543 );
544
545 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
546 ROLLBACK TO DELETE_QUALIFIER_PUB;
547 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
548
549 FND_MSG_PUB.Count_And_Get
550 ( p_count => x_msg_count,
551 p_data => x_msg_data
552 );
553
554
555 WHEN OTHERS THEN
556 ROLLBACK TO DELETE_QUALIFIER_PUB;
557 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
558
559 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
560 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
561 END IF;
562
563 FND_MSG_PUB.Count_And_Get
564 ( p_count => x_msg_count,
565 p_data => x_msg_data
566 );
567
568 END Delete_Qualifier;
569
570
571 END JTF_QUALIFIER_PUB; -- Package Body JTF_QUALIFIER_PUB