[Home] [Help]
PACKAGE BODY: APPS.PVX_CHANNEL_TYPE_PVT
Source
1 Package Body pvx_channel_type_pvt AS
2 /* $Header: pvxchnlb.pls 115.7 2002/11/20 02:05:42 pklin ship $ */
3
4
5 g_pkg_name CONSTANT VARCHAR2(30):='pvx_channel_type_pvt';
6
7 ---------------------------------------------------------------------
8 -- PROCEDURE
9 -- Create_channel_type
10 --
11 -- PURPOSE
12 -- Create a new channel type record
13 --
14 -- PARAMETERS
15 -- p_channel_type_rec: the new record to be inserted
16 -- x_channel_type_id: return the channel_type_id of the new record.
17 --
18 -- NOTES
19 -- 1. object_version_number will be set to 1.
20 -- 2. If channel_type_id is not passed in, generate a unique one from
21 -- the sequence.
22 -- 3. Please don't pass in any FND_API.g_mess_char/num/date.
23 ---------------------------------------------------------------------
24 PROCEDURE Create_channel_type(
25 p_api_version IN NUMBER := 1.0
26 ,p_init_msg_list IN VARCHAR2 := FND_API.g_false
27 ,p_commit IN VARCHAR2 := FND_API.g_false
28 ,p_validation_level IN NUMBER := FND_API.g_valid_level_full
29 ,p_channel_type_rec IN channel_type_rec_type
30 ,x_channel_type_id OUT NOCOPY NUMBER
31 ,x_return_status OUT NOCOPY VARCHAR2
32 ,x_msg_count OUT NOCOPY NUMBER
33 ,x_msg_data OUT NOCOPY VARCHAR2
34 )
35 IS
36 l_api_version CONSTANT NUMBER := 1.0;
37 l_api_name CONSTANT VARCHAR2(30) := 'Create_channel_type';
38 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
39
40 l_channel_type_rec channel_type_rec_type := p_channel_type_rec;
41 l_object_version_number NUMBER := 1;
42 l_uniqueness_check pls_integer;
43
44 CURSOR lc_get_next_seq IS
45 SELECT pv_channel_types_s.NEXTVAL FROM DUAL;
46
47 CURSOR lc_chk_exists(pc_lookup_type varchar2,
48 pc_lookup_code varchar2) is
49 SELECT 1 FROM PV_CHANNEL_TYPES
50 WHERE channel_lookup_type = pc_lookup_type
51 and channel_lookup_code = pc_lookup_code;
52
53 BEGIN
54
55 --------------------- initialize -----------------------
56 SAVEPOINT Create_channel_type;
57
58 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
59 PVX_Utility_PVT.debug_message(l_full_name||': start');
60 END IF;
61
62 IF FND_API.to_boolean(p_init_msg_list) THEN
63 FND_MSG_PUB.initialize;
64 END IF;
65
66 IF NOT FND_API.compatible_api_call( l_api_version,
67 p_api_version,
68 l_api_name,
69 g_pkg_name) THEN
70 RAISE FND_API.g_exc_unexpected_error;
71 END IF;
72
73 x_return_status := FND_API.g_ret_sts_success;
74
75 -------------------------- insert --------------------------
76 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
77 PVX_Utility_PVT.debug_message(l_full_name ||': insert');
78 END IF;
79
80
81 IF l_channel_type_rec.channel_type_id IS NULL THEN
82
83 -- Get the identifier
84 OPEN lc_get_next_seq;
85 FETCH lc_get_next_seq INTO l_channel_type_rec.channel_type_id;
86 CLOSE lc_get_next_seq;
87
88 -- Check the uniqueness of the identifier
89 OPEN lc_chk_exists(pc_lookup_type => l_channel_type_rec.channel_lookup_type,
90 pc_lookup_code => l_channel_type_rec.channel_lookup_code);
91 FETCH lc_chk_exists INTO l_uniqueness_check;
92 CLOSE lc_chk_exists;
93
94 if l_uniqueness_check is not null then
95 FND_MESSAGE.set_name('PV', 'PV_DUPLICATE_RECORD');
96 FND_MSG_PUB.add;
97 RAISE FND_API.g_exc_unexpected_error;
98 end if;
99
100 END IF;
101
102 INSERT INTO PV_CHANNEL_TYPES (
103 CHANNEL_TYPE_ID,
104 CHANNEL_LOOKUP_TYPE,
105 CHANNEL_LOOKUP_CODE,
106 INDIRECT_CHANNEL_FLAG,
107 LAST_UPDATE_DATE,
108 LAST_UPDATED_BY,
109 CREATION_DATE,
110 CREATED_BY,
111 LAST_UPDATE_LOGIN,
112 OBJECT_VERSION_NUMBER,
113 RANK
114 ) VALUES (
115 l_channel_type_rec.channel_type_id
116 ,l_channel_type_rec.channel_lookup_type
117 ,l_channel_type_rec.channel_lookup_code
118 ,l_channel_type_rec.indirect_channel_flag
119 ,SYSDATE -- LAST_UPDATE_DATE
120 ,NVL(FND_GLOBAL.user_id,-1) -- LAST_UPDATED_BY
121 ,SYSDATE -- CREATION_DATE
122 ,NVL(FND_GLOBAL.user_id,-1) -- CREATED_BY
123 ,NVL(FND_GLOBAL.conc_login_id,-1) -- LAST_UPDATE_LOGIN
124 ,l_object_version_number -- object_version_number
125 ,l_channel_type_rec.rank
126 );
127
128 x_channel_type_id := l_channel_type_rec.channel_type_id;
129
130 -- Check for commit
131 IF FND_API.to_boolean(p_commit) THEN
132 COMMIT;
133 END IF;
134
135 FND_MSG_PUB.count_and_get(p_encoded => FND_API.g_false,
136 p_count => x_msg_count,
137 p_data => x_msg_data);
138
139 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
140 PVX_Utility_PVT.debug_message(l_full_name ||': end');
141 END IF;
142
143 EXCEPTION
144
145 WHEN FND_API.g_exc_error THEN
146
147 ROLLBACK TO Create_channel_type;
148 x_return_status := FND_API.g_ret_sts_error;
149
150 FND_MSG_PUB.count_and_get ( p_encoded => FND_API.g_false
151 ,p_count => x_msg_count
152 ,p_data => x_msg_data);
153
154 WHEN FND_API.g_exc_unexpected_error THEN
155
156 ROLLBACK TO Create_channel_type;
157 x_return_status := FND_API.g_ret_sts_unexp_error ;
158
159 FND_MSG_PUB.count_and_get ( p_encoded => FND_API.g_false
160 ,p_count => x_msg_count
161 ,p_data => x_msg_data);
162
163 WHEN OTHERS THEN
164
165 ROLLBACK TO Create_channel_type;
166 x_return_status := FND_API.g_ret_sts_unexp_error ;
167
168 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
169 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
170 END IF;
171
172 FND_MSG_PUB.count_and_get( p_encoded => FND_API.g_false
173 ,p_count => x_msg_count
174 ,p_data => x_msg_data);
175
176 END Create_channel_type;
177
178
179 ---------------------------------------------------------------
180 -- PROCEDURE
181 -- Delete_channel_type
182 --
183 ---------------------------------------------------------------
184 PROCEDURE Delete_channel_type(
185 p_api_version IN NUMBER
186 ,p_init_msg_list IN VARCHAR2 := FND_API.g_false
187 ,p_commit IN VARCHAR2 := FND_API.g_false
188 ,p_channel_type_id IN NUMBER
189 ,p_object_version IN NUMBER
190 ,x_return_status OUT NOCOPY VARCHAR2
191 ,x_msg_count OUT NOCOPY NUMBER
192 ,x_msg_data OUT NOCOPY VARCHAR2
193 )
194 IS
195 l_api_version CONSTANT NUMBER := 1.0;
196 l_api_name CONSTANT VARCHAR2(30) := 'Delete_channel_type';
197 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
198
199 BEGIN
200
201 --------------------- initialize -----------------------
202 SAVEPOINT Delete_channel_type;
203
204 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
205 PVX_Utility_PVT.debug_message(l_full_name||': start');
206 END IF;
207
208
209 IF FND_API.to_boolean(p_init_msg_list) THEN
210 FND_MSG_PUB.initialize;
211 END IF;
212
213 IF NOT FND_API.compatible_api_call( l_api_version,
214 p_api_version,
215 l_api_name,
216 g_pkg_name) THEN
217 RAISE FND_API.g_exc_unexpected_error;
218 END IF;
219
220 x_return_status := FND_API.G_RET_STS_SUCCESS;
221
222 ------------------------ delete ------------------------
223 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
224 PVX_Utility_PVT.debug_message(l_full_name ||': delete');
225 END IF;
226
227 IF p_channel_type_id < 10000 THEN
228
229 FND_MESSAGE.set_name('PV', 'PV_DEBUG_MESSAGE');
230 FND_MESSAGE.set_token('TEXT', 'Cannot delete a seeded channel type');
231 FND_MSG_PUB.add;
232 RAISE FND_API.g_exc_error;
233
234
235 END IF;
236
237
238 DELETE FROM PV_channel_types
239 WHERE channel_type_id = p_channel_type_id
240 AND object_version_number = p_object_version;
241
242 IF (SQL%NOTFOUND) THEN
243 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
244 FND_MESSAGE.set_name('PV', 'PV_RECORD_NOT_FOUND');
245 FND_MSG_PUB.add;
246 END IF;
247 RAISE FND_API.g_exc_error;
248 END IF;
249
250 -------------------- finish --------------------------
251 IF FND_API.to_boolean(p_commit) THEN
252 COMMIT;
253 END IF;
254
255 FND_MSG_PUB.count_and_get( p_encoded => FND_API.g_false,
256 p_count => x_msg_count,
257 p_data => x_msg_data);
258
259 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
260 PVX_Utility_PVT.debug_message(l_full_name ||': end');
261 END IF;
262
263 EXCEPTION
264
265 WHEN FND_API.g_exc_error THEN
266
267 ROLLBACK TO Delete_channel_type;
268 x_return_status := FND_API.g_ret_sts_error;
269
270 FND_MSG_PUB.count_and_get ( p_encoded => FND_API.g_false
271 ,p_count => x_msg_count
272 ,p_data => x_msg_data);
273
274 WHEN FND_API.g_exc_unexpected_error THEN
275
276 ROLLBACK TO Delete_channel_type;
277 x_return_status := FND_API.g_ret_sts_unexp_error ;
278
279 FND_MSG_PUB.count_and_get ( p_encoded => FND_API.g_false
280 ,p_count => x_msg_count
281 ,p_data => x_msg_data);
282
283 WHEN OTHERS THEN
284
285 ROLLBACK TO Delete_channel_type;
286 x_return_status := FND_API.g_ret_sts_unexp_error ;
287
288 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
289 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
290 END IF;
291
292 FND_MSG_PUB.count_and_get( p_encoded => FND_API.g_false
293 ,p_count => x_msg_count
294 ,p_data => x_msg_data);
295
296 END Delete_channel_type;
297
298
299 ---------------------------------------------------------------------
300 -- PROCEDURE
301 -- Update_channel_type
302 ----------------------------------------------------------------------
303 PROCEDURE Update_channel_type(
304 p_api_version IN NUMBER := 1.0
305 ,p_init_msg_list IN VARCHAR2 := FND_API.g_false
306 ,p_commit IN VARCHAR2 := FND_API.g_false
307 ,p_validation_level IN NUMBER := FND_API.g_valid_level_full
308 ,p_channel_type_rec IN channel_type_rec_type
309 ,x_return_status OUT NOCOPY VARCHAR2
310 ,x_msg_count OUT NOCOPY NUMBER
311 ,x_msg_data OUT NOCOPY VARCHAR2)
312 IS
313
314 l_api_version CONSTANT NUMBER := 1.0;
315 l_api_name CONSTANT VARCHAR2(30) := 'Update_channel_type';
316 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
317
318 l_channel_type_rec channel_type_rec_type;
319 l_indirect_channel_flag VARCHAR2(20);
320
321 CURSOR lc_seed_chk(pc_channel_type_id NUMBER) IS
322 SELECT indirect_channel_flag
323 FROM pv_channel_types
324 WHERE channel_type_id = pc_channel_type_id;
325
326 BEGIN
327
328 -------------------- initialize -------------------------
329 SAVEPOINT Update_channel_type;
330
331 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
332 PVX_Utility_PVT.debug_message(l_full_name||': start');
333 END IF;
334
335 IF FND_API.to_boolean(p_init_msg_list) THEN
336 FND_MSG_PUB.initialize;
337 END IF;
338
339 IF NOT FND_API.compatible_api_call( l_api_version,
340 p_api_version,
341 l_api_name,
342 g_pkg_name) THEN
343 RAISE FND_API.g_exc_unexpected_error;
344 END IF;
345
346 x_return_status := FND_API.G_RET_STS_SUCCESS;
347
348 ----------------------- validate ----------------------
349 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
350 PVX_Utility_PVT.debug_message(l_full_name ||': validate');
351 END IF;
352
353 -- replace g_miss_char/num/date with current column values
354 Complete_channel_type_rec(p_channel_type_rec => p_channel_type_rec,
355 x_complete_rec => l_channel_type_rec);
356
357
358 -------------------------- update --------------------
359
360
361 IF l_channel_type_rec.channel_type_id < 10000 THEN
362
366
363 OPEN lc_seed_chk(l_channel_type_rec.channel_type_id);
364 FETCH lc_seed_chk INTO l_indirect_channel_flag;
365 CLOSE lc_seed_chk;
367
368 IF l_indirect_channel_flag <> l_channel_type_rec.indirect_channel_flag THEN
369
370 FND_MESSAGE.set_name('PV', 'PV_DEBUG_MESSAGE');
371 FND_MESSAGE.set_token('TEXT', 'Cannot update a seeded channel type');
372 FND_MSG_PUB.add;
373 RAISE FND_API.g_exc_error;
374
375 END IF;
376
377 END IF;
378
379 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
380 PVX_Utility_PVT.debug_message(l_full_name ||': update');
381 END IF;
382
383 UPDATE PV_CHANNEL_TYPES
384 SET
385 last_update_date = SYSDATE
386 ,last_updated_by = NVL(FND_GLOBAL.user_id,-1)
387 ,last_update_login = NVL(FND_GLOBAL.conc_login_id,-1)
388 ,channel_lookup_type = l_channel_type_rec.channel_lookup_type
389 ,channel_lookup_code = l_channel_type_rec.channel_lookup_code
390 ,indirect_channel_flag = l_channel_type_rec.indirect_channel_flag
391 ,object_version_number = l_channel_type_rec.object_version_number + 1
392 ,rank = l_channel_type_rec.rank
393 WHERE channel_type_id = l_channel_type_rec.channel_type_id
394 AND object_version_number = l_channel_type_rec.object_version_number;
395
396 IF (SQL%NOTFOUND) THEN
397 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
398 FND_MESSAGE.set_name('PV', 'PV_NO_RECORD_FOUND');
399 FND_MSG_PUB.add;
400 END IF;
401 RAISE FND_API.g_exc_error;
402 END IF;
403
404 -------------------- finish --------------------------
405
406 -- Check for commit
407 IF FND_API.to_boolean(p_commit) THEN
408 COMMIT;
409 END IF;
410
411 FND_MSG_PUB.count_and_get(
412 p_encoded => FND_API.g_false,
413 p_count => x_msg_count,
414 p_data => x_msg_data
415 );
416
417 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
418 PVX_Utility_PVT.debug_message(l_full_name ||': end');
419 END IF;
420
421
422 EXCEPTION
423
424 WHEN FND_API.g_exc_error THEN
425
426 ROLLBACK TO Update_channel_type;
427 x_return_status := FND_API.g_ret_sts_error;
428
429 FND_MSG_PUB.count_and_get ( p_encoded => FND_API.g_false
430 ,p_count => x_msg_count
431 ,p_data => x_msg_data);
432
433 WHEN FND_API.g_exc_unexpected_error THEN
434
435 ROLLBACK TO Update_channel_type;
436 x_return_status := FND_API.g_ret_sts_unexp_error ;
437
438 FND_MSG_PUB.count_and_get ( p_encoded => FND_API.g_false
439 ,p_count => x_msg_count
440 ,p_data => x_msg_data);
441
442 WHEN OTHERS THEN
443
444 ROLLBACK TO Update_channel_type;
445 x_return_status := FND_API.g_ret_sts_unexp_error ;
446
447 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
448 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
449 END IF;
450
451 FND_MSG_PUB.count_and_get( p_encoded => FND_API.g_false
452 ,p_count => x_msg_count
453 ,p_data => x_msg_data);
454
455 END Update_channel_type;
456
457
458 ---------------------------------------------------------------------
459 -- PROCEDURE
460 -- Complete_channel_type_rec
461 --
462 ---------------------------------------------------------------------
463 PROCEDURE Complete_channel_type_rec(
464 p_channel_type_rec IN channel_type_rec_type
465 ,x_complete_rec OUT NOCOPY channel_type_rec_type
466 )
467 IS
468
469 CURSOR lc_get_channel_type IS
470 SELECT *
471 FROM PV_CHANNEL_TYPES
472 WHERE channel_type_id = p_channel_type_rec.channel_type_id;
473
474 l_channel_type_rec lc_get_channel_type%ROWTYPE;
475
476 BEGIN
477
478 x_complete_rec := p_channel_type_rec;
479
480 OPEN lc_get_channel_type;
481 FETCH lc_get_channel_type INTO l_channel_type_rec;
482
483 IF lc_get_channel_type%NOTFOUND THEN
484 CLOSE lc_get_channel_type;
485 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
486 FND_MESSAGE.set_name('PV', 'PV_NO_RECORD_FOUND');
487 FND_MSG_PUB.add;
488 END IF;
489 RAISE FND_API.g_exc_error;
490 END IF;
491
492 CLOSE lc_get_channel_type;
493
494 IF p_channel_type_rec.channel_lookup_type = FND_API.G_MISS_CHAR THEN
495 x_complete_rec.channel_lookup_type := l_channel_type_rec.channel_lookup_type;
496 END IF;
497
498 IF p_channel_type_rec.channel_lookup_code = FND_API.G_MISS_CHAR THEN
499 x_complete_rec.channel_lookup_code := l_channel_type_rec.channel_lookup_code;
500 END IF;
501
502 IF p_channel_type_rec.indirect_channel_flag = FND_API.G_MISS_CHAR THEN
503 x_complete_rec.indirect_channel_flag := l_channel_type_rec.indirect_channel_flag;
504 END IF;
505
506 IF p_channel_type_rec.object_version_number = FND_API.G_MISS_NUM THEN
507 x_complete_rec.object_version_number := l_channel_type_rec.object_version_number;
508 END IF;
509
510 IF p_channel_type_rec.rank = FND_API.G_MISS_NUM THEN
511 x_complete_rec.rank := l_channel_type_rec.rank;
512 END IF;
513
514 END Complete_channel_type_rec;
515
516 END pvx_channel_type_pvt;