[Home] [Help]
PACKAGE BODY: APPS.CN_SF_PARAMS_PVT
Source
1 PACKAGE BODY CN_SF_PARAMS_PVT AS
2 -- $Header: cnvprmsb.pls 115.3 2002/11/21 21:15:43 hlchen ship $
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'cn_sf_params_pvt';
5 G_FILE_NAME CONSTANT VARCHAR2(12) := 'cnvprmsb.pls';
6
7
8 -- Start of comments
9 -- API name : Get_SF_Parameters
10 -- Type : Private.
11 -- Function :
12 -- Pre-reqs : None.
13 -- Parameters :
14 -- IN : p_api_version IN NUMBER Required
15 -- p_init_msg_list IN VARCHAR2 Optional
16 -- Default = FND_API.G_FALSE
17 -- p_commit IN VARCHAR2 Optional
18 -- Default = FND_API.G_FALSE
19 -- p_validation_level IN NUMBER Optional
20 -- Default = FND_API.G_VALID_LEVEL_FULL
21 -- OUT : p_sf_repositories_rec OUT cn_sf_repositories_rec_type
22 -- x_return_status OUT VARCHAR2(1)
23 -- x_msg_count OUT NUMBER
24 -- x_msg_data OUT VARCHAR2(2000)
25 --
26 -- Version : Current version 1.0
27 --
28 --
29 --
30 -- Notes : This procedure uses the table handler CN_SF_PARAMS_PKG
31 -- to get parameters from CN_SF_REPOSITORIES.
32 --
33 -- End of comments
34
35 PROCEDURE Get_SF_Parameters
36 (
37 p_api_version IN NUMBER,
38 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
39 p_commit IN VARCHAR2 := FND_API.G_FALSE,
40 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
41 p_sf_param_rec OUT NOCOPY cn_sf_repositories_rec_type,
42 x_return_status OUT NOCOPY VARCHAR2,
43 x_msg_count OUT NOCOPY NUMBER,
44 x_msg_data OUT NOCOPY VARCHAR2
45 ) IS
46
47 -- local variables
48 l_api_name CONSTANT VARCHAR2(30) := 'Insert_SF_Parameters' ;
49 l_api_version CONSTANT NUMBER := 1.0 ;
50 l_validation_status VARCHAR2(30) ;
51 l_return_status VARCHAR2(1);
52 l_error_code NUMBER ;
53
54 BEGIN
55 -- show that the update is starting
56 --DBMS_OUTPUT.PUT_LINE('Inserting.....');
57
58 -- start of the API savepoint
59 SAVEPOINT Get_SF_Parameters_svp ;
60
61 -- Standard call to check for call compatibility.
62 IF NOT FND_API.compatible_api_call
63 ( l_api_version ,p_api_version ,l_api_name ,G_PKG_NAME )
64 THEN
65 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
66 END IF;
67
68 -- Initialize message list if p_init_msg_list is set to TRUE.
69 IF FND_API.to_Boolean( p_init_msg_list ) THEN
70 FND_MSG_PUB.initialize;
71 END IF;
72
73 -- Initialize API return status to success
74 x_return_status := FND_API.G_RET_STS_SUCCESS;
75 l_validation_status := 'INVALID';
76
77 -- BEGINING OF API BODY ----
78
79
80 -- Beginning of validation code -----
81
82 -- End of validation code -----
83
84 SELECT REPOSITORY_ID,CONTRACT_TITLE ,TERMS_AND_CONDITIONS ,
85 CLUB_QUAL_TEXT ,APPROVER_NAME ,APPROVER_TITLE ,
86 APPROVER_ORG_NAME ,FILE_ID,FORMU_ACTIVATED_FLAG,
87 TRANSACTION_CALENDAR_ID,OBJECT_VERSION_NUMBER
88 INTO p_sf_param_rec.REPOSITORY_ID, p_sf_param_rec.CONTRACT_TITLE ,
89 p_sf_param_rec.TERMS_AND_CONDITIONS ,p_sf_param_rec.CLUB_QUAL_TEXT ,
90 p_sf_param_rec.APPROVER_NAME , p_sf_param_rec.APPROVER_TITLE ,
91 p_sf_param_rec.APPROVER_ORG_NAME , p_sf_param_rec.FILE_ID,
92 p_sf_param_rec.FORMU_ACTIVATED_FLAG, p_sf_param_rec.TRANSACTION_CALENDAR_ID,
93 p_sf_param_rec.OBJECT_VERSION_NUMBER
94 FROM CN_SF_REPOSITORIES;
95
96 --DBMS_OUTPUT.PUT_LINE('Getting complete.');
97 -- END OF API BODY ---
98 << end_Get_SF_Parameters >>
99 NULL;
100
101 -- Standard check of p_commit.
102 IF FND_API.To_Boolean( p_commit ) THEN
103 COMMIT WORK;
104 END IF;
105
106 -- Standard call to get message count and if count is 1, get message info.
107 FND_MSG_PUB.Count_And_Get
108 (
109 p_count => x_msg_count ,
110 p_data => x_msg_data ,
111 p_encoded => FND_API.G_FALSE
112 );
113
114 EXCEPTION
115 WHEN FND_API.G_EXC_ERROR THEN
116 ROLLBACK TO Get_SF_Parameters_svp ;
117 x_return_status := FND_API.G_RET_STS_ERROR ;
118 FND_MSG_PUB.Count_And_Get
119 (
120 p_count => x_msg_count ,
121 p_data => x_msg_data ,
122 p_encoded => FND_API.G_FALSE
123 );
124
125 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
126 ROLLBACK TO Get_SF_Parameters_svp ;
127 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
128 FND_MSG_PUB.Count_And_Get
129 (
130 p_count => x_msg_count ,
131 p_data => x_msg_data ,
132 p_encoded => FND_API.G_FALSE
133 );
134
135 WHEN OTHERS THEN
136 ROLLBACK TO Get_SF_Parameters_svp ;
137 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
138 l_error_code := SQLCODE;
139 IF l_error_code = -54 THEN
140 x_return_status := FND_API.G_RET_STS_ERROR ;
141 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
142 THEN
143 FND_MESSAGE.SET_NAME ('CN' , 'CN_LOCK_FAIL');
144 FND_MSG_PUB.Add;
145 END IF;
146 ELSE
147 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
148 THEN
149 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME ,l_api_name );
150 END IF;
151 END IF;
152 FND_MSG_PUB.Count_And_Get
153 (
154 p_count => x_msg_count ,
155 p_data => x_msg_data ,
156 p_encoded => FND_API.G_FALSE
157 );
158
159 END Get_SF_Parameters ;
160
161
162
163 -- Start of comments
164 -- API name : Update_SF_Parameters
165 -- Type : Private.
166 -- Function :
167 -- Pre-reqs : None.
168 -- Parameters :
169 -- IN : p_api_version IN NUMBER Required
170 -- p_init_msg_list IN VARCHAR2 Optional
171 -- Default = FND_API.G_FALSE
172 -- p_commit IN VARCHAR2 Optional
173 -- Default = FND_API.G_FALSE
174 -- p_validation_level IN NUMBER Optional
175 -- Default = FND_API.G_VALID_LEVEL_FULL
176 -- p_seasonalities_rec_type IN seasonalities_rec_type
177 -- OUT : x_return_status OUT VARCHAR2(1)
178 -- x_msg_count OUT NUMBER
179 -- x_msg_data OUT VARCHAR2(2000)
180 --
181 -- Version : Current version 1.0
182 --
183 --
184 --
185 -- Notes : This procedure uses the table handler CN_SEAS_SCHEDULES_PKG
186 -- to update rows into CN_SEAS_SCHEDULES after some validations.
187 --
188 -- End of comments
189
190
191 PROCEDURE Update_SF_Parameters
192 (
193 p_api_version IN NUMBER,
194 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
195 p_commit IN VARCHAR2 := FND_API.G_FALSE,
196 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
197 p_sf_repositories_rec IN cn_sf_repositories_rec_type,
198 x_return_status OUT NOCOPY VARCHAR2,
199 x_msg_count OUT NOCOPY NUMBER,
200 x_msg_data OUT NOCOPY VARCHAR2
201 ) IS
202
203 -- local variables
204 l_api_name CONSTANT VARCHAR2(30) := 'Update_Repository' ;
205 l_api_version CONSTANT NUMBER := 1.0 ;
206 l_error_code NUMBER ;
207 l_rec cn_sf_repositories_rec_type ;
208 l_validation_status VARCHAR2(30) ;
209 l_return_status VARCHAR2(1);
210 l_count NUMBER ;
211
212 BEGIN
213 -- show that the update is starting
214 --DBMS_OUTPUT.PUT_LINE('Update in progress');
215
216 -- start of the API savepoint
217 SAVEPOINT Update_SF_Parameters_svp ;
218
219 -- Standard call to check for call compatibility.
220 IF NOT FND_API.compatible_api_call
221 ( l_api_version ,p_api_version ,l_api_name ,G_PKG_NAME )
222 THEN
223 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
224 END IF;
225
226 -- Initialize message list if p_init_msg_list is set to TRUE.
227 IF FND_API.to_Boolean( p_init_msg_list ) THEN
228 FND_MSG_PUB.initialize;
229 END IF;
230
231 -- Initialize API return status to success
232 x_return_status := FND_API.G_RET_STS_SUCCESS;
233 l_validation_status := 'INVALID';
234 /*
235 SELECT Count(*)
236 INTO l_count
237 FROM CN_SF_REPOSITORIES ;
238
239 IF l_count > 0 THEN
240 FND_MESSAGE.SET_NAME ('CN' , 'CN_RECORD_CHANGED');
241 FND_MSG_PUB.Add;
242 RAISE fnd_api.g_exc_error ;
243 END IF;
244 */
245 -- BEGINING OF API BODY ----
246
247
248 -- Beginning of validation code -----
249
250 -- End of validation code -----
251
252 CN_SF_PARAMS_pkg.update_row
253 (
254 P_REPOSITORY_ID => p_sf_repositories_rec.REPOSITORY_ID,
255 P_CONTRACT_TITLE => p_sf_repositories_rec.CONTRACT_TITLE,
256 P_TERMS_AND_CONDITIONS => p_sf_repositories_rec.TERMS_AND_CONDITIONS,
257 P_CLUB_QUAL_TEXT => p_sf_repositories_rec.CLUB_QUAL_TEXT,
258 P_APPROVER_NAME => p_sf_repositories_rec.APPROVER_NAME,
259 P_APPROVER_TITLE => p_sf_repositories_rec.APPROVER_TITLE,
260 P_APPROVER_ORG_NAME => p_sf_repositories_rec.APPROVER_ORG_NAME,
261 P_FILE_ID => p_sf_repositories_rec.FILE_ID,
262 P_FORMU_ACTIVATED_FLAG => p_sf_repositories_rec.FORMU_ACTIVATED_FLAG,
263 P_TRANSACTION_CALENDAR_ID => p_sf_repositories_rec.TRANSACTION_CALENDAR_ID,
264 P_OBJECT_VERSION_NUMBER => p_sf_repositories_rec.OBJECT_VERSION_NUMBER
265 );
266
267
268 --DBMS_OUTPUT.PUT_LINE('Update complete.');
269 -- END OF API BODY ---
270 << end_Update_SF_Parameters >>
271 NULL;
272
273 -- Standard check of p_commit.
274 IF FND_API.To_Boolean( p_commit ) THEN
275 COMMIT WORK;
276 END IF;
277
278 -- Standard call to get message count and if count is 1, get message info.
279 FND_MSG_PUB.Count_And_Get
280 (
281 p_count => x_msg_count ,
282 p_data => x_msg_data ,
283 p_encoded => FND_API.G_FALSE
284 );
285
286 EXCEPTION
287 WHEN FND_API.G_EXC_ERROR THEN
288 ROLLBACK TO Update_SF_Parameters_svp ;
289 x_return_status := FND_API.G_RET_STS_ERROR ;
290 FND_MSG_PUB.Count_And_Get
291 (
292 p_count => x_msg_count ,
293 p_data => x_msg_data ,
294 p_encoded => FND_API.G_FALSE
295 );
296
297 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
298 ROLLBACK TO Update_SF_Parameters_svp ;
299 --DBMS_OUTPUT.PUT_LINE('Update Error : Unexpected ');
300 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
301 FND_MSG_PUB.Count_And_Get
302 (
303 p_count => x_msg_count ,
304 p_data => x_msg_data ,
305 p_encoded => FND_API.G_FALSE
306 );
307
308 WHEN OTHERS THEN
309 ROLLBACK TO Update_SF_Parameters_svp ;
310 --DBMS_OUTPUT.PUT_LINE('Update Error : Unexpected Others.');
311 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
312
313 l_error_code := SQLCODE;
314 IF l_error_code = -54 THEN
315 x_return_status := FND_API.G_RET_STS_ERROR ;
316 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
317 THEN
318 FND_MESSAGE.SET_NAME ('CN' , 'CN_LOCK_FAIL');
319 FND_MSG_PUB.Add;
320 END IF;
321 ELSE
322 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
323 THEN
324 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME ,l_api_name );
325 END IF;
326 END IF;
327 FND_MSG_PUB.Count_And_Get
328 (
329 p_count => x_msg_count ,
330 p_data => x_msg_data ,
331 p_encoded => FND_API.G_FALSE
332 );
333
334 END Update_SF_Parameters ;
335
336
337
338
339
340
341
342 -- Start of comments
343 -- API name : Insert_SF_Parameters
344 -- Type : Private.
345 -- Function :
346 -- Pre-reqs : None.
347 -- Parameters :
348 -- IN : p_api_version IN NUMBER Required
349 -- p_init_msg_list IN VARCHAR2 Optional
350 -- Default = FND_API.G_FALSE
351 -- p_commit IN VARCHAR2 Optional
352 -- Default = FND_API.G_FALSE
353 -- p_validation_level IN NUMBER Optional
354 -- Default = FND_API.G_VALID_LEVEL_FULL
355 -- p_sf_repositories_rec IN cn_sf_repositories_rec_type
356 -- OUT : x_return_status OUT VARCHAR2(1)
357 -- x_msg_count OUT NUMBER
358 -- x_msg_data OUT VARCHAR2(2000)
359 --
360 -- Version : Current version 1.0
361 --
362 --
363 --
364 -- Notes : This procedure uses the table handler CN_SF_PARAMS_PKG
365 -- to insert a row into CN_SF_REPOSITORIES after some validations.
366 --
367 -- End of comments
368
369 PROCEDURE Insert_SF_Parameters
370 (
371 p_api_version IN NUMBER,
372 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
373 p_commit IN VARCHAR2 := FND_API.G_FALSE,
374 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
375 p_sf_repositories_rec IN cn_sf_repositories_rec_type,
376 x_return_status OUT NOCOPY VARCHAR2,
377 x_msg_count OUT NOCOPY NUMBER,
378 x_msg_data OUT NOCOPY VARCHAR2
379 ) IS
380
381 -- local variables
382 l_api_name CONSTANT VARCHAR2(30) := 'Insert_SF_Parameters' ;
383 l_api_version CONSTANT NUMBER := 1.0 ;
384 l_validation_status VARCHAR2(30) ;
385 l_return_status VARCHAR2(1);
386 l_error_code NUMBER ;
387 l_count NUMBER ;
388 BEGIN
389 -- show that the update is starting
390 --DBMS_OUTPUT.PUT_LINE('Inserting.....');
391
392 -- start of the API savepoint
393 SAVEPOINT Insert_SF_Parameters_svp ;
394
395 -- Standard call to check for call compatibility.
396 IF NOT FND_API.compatible_api_call
397 ( l_api_version ,p_api_version ,l_api_name ,G_PKG_NAME )
398 THEN
399 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
400 END IF;
401
402 -- Initialize message list if p_init_msg_list is set to TRUE.
403 IF FND_API.to_Boolean( p_init_msg_list ) THEN
404 FND_MSG_PUB.initialize;
405 END IF;
406
407 -- Initialize API return status to success
408 x_return_status := FND_API.G_RET_STS_SUCCESS;
409 l_validation_status := 'INVALID';
410
411 -- BEGINING OF API BODY ----
412
413
414 -- Beginning of validation code -----
415 SELECT Count(*)
416 INTO l_count
417 FROM CN_SF_REPOSITORIES ;
418
419 IF l_count > 0 THEN
420 FND_MESSAGE.SET_NAME ('CN' , 'CN_SF_RECORD_EXISTS');
421 FND_MSG_PUB.Add;
422 RAISE fnd_api.g_exc_error ;
423 END IF;
424
425
426 -- End of validation code -----
427
428 CN_SF_PARAMS_pkg.insert_row
432 P_TERMS_AND_CONDITIONS => p_sf_repositories_rec.TERMS_AND_CONDITIONS,
429 (
430 P_REPOSITORY_ID => p_sf_repositories_rec.REPOSITORY_ID,
431 P_CONTRACT_TITLE => p_sf_repositories_rec.CONTRACT_TITLE,
433 P_CLUB_QUAL_TEXT => p_sf_repositories_rec.CLUB_QUAL_TEXT,
434 P_APPROVER_NAME => p_sf_repositories_rec.APPROVER_NAME,
435 P_APPROVER_TITLE => p_sf_repositories_rec.APPROVER_TITLE,
436 P_APPROVER_ORG_NAME => p_sf_repositories_rec.APPROVER_ORG_NAME,
437 P_FILE_ID => p_sf_repositories_rec.FILE_ID,
438 P_FORMU_ACTIVATED_FLAG => p_sf_repositories_rec.FORMU_ACTIVATED_FLAG,
439 P_TRANSACTION_CALENDAR_ID => p_sf_repositories_rec.TRANSACTION_CALENDAR_ID
440 );
441
442
443 --DBMS_OUTPUT.PUT_LINE('Insert complete.');
444 -- END OF API BODY ---
445 << end_Insert_SF_Parameters >>
446 NULL;
447
448 -- Standard check of p_commit.
449 IF FND_API.To_Boolean( p_commit ) THEN
450 COMMIT WORK;
451 END IF;
452
453 -- Standard call to get message count and if count is 1, get message info.
454 FND_MSG_PUB.Count_And_Get
455 (
456 p_count => x_msg_count ,
457 p_data => x_msg_data ,
458 p_encoded => FND_API.G_FALSE
459 );
460
461 EXCEPTION
462 WHEN FND_API.G_EXC_ERROR THEN
463 ROLLBACK TO Insert_SF_Parameters_svp ;
464 x_return_status := FND_API.G_RET_STS_ERROR ;
465 FND_MSG_PUB.Count_And_Get
466 (
467 p_count => x_msg_count ,
468 p_data => x_msg_data ,
469 p_encoded => FND_API.G_FALSE
470 );
471
472 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
473 ROLLBACK TO Insert_SF_Parameters_svp ;
474 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
475 FND_MSG_PUB.Count_And_Get
476 (
477 p_count => x_msg_count ,
478 p_data => x_msg_data ,
479 p_encoded => FND_API.G_FALSE
480 );
481
482 WHEN OTHERS THEN
483 ROLLBACK TO Insert_SF_Parameters_svp ;
484 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
485 l_error_code := SQLCODE;
486 IF l_error_code = -54 THEN
487 x_return_status := FND_API.G_RET_STS_ERROR ;
488 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
489 THEN
490 FND_MESSAGE.SET_NAME ('CN' , 'CN_LOCK_FAIL');
491 FND_MSG_PUB.Add;
492 END IF;
493 ELSE
494 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
495 THEN
496 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME ,l_api_name );
497 END IF;
498 END IF;
499 FND_MSG_PUB.Count_And_Get
500 (
501 p_count => x_msg_count ,
502 p_data => x_msg_data ,
503 p_encoded => FND_API.G_FALSE
504 );
505
506 END Insert_SF_Parameters ;
507
508
509 END CN_SF_PARAMS_PVT;