[Home] [Help]
PACKAGE BODY: APPS.PV_PARTNER_PGM_TYPE_PKG
Source
1 PACKAGE BODY PV_PARTNER_PGM_TYPE_PKG as
2 /* $Header: pvxtpptb.pls 120.0 2005/05/27 16:28:16 appldev noship $*/
3 -- ===============================================================
4 -- Start of Comments
5 -- Package name
6 -- PV_PARTNER_PGM_TYPE_PKG
7 -- Purpose
8 --
9 -- History
10 -- 22-APR-2002 Peter.Nixon Created
11 -- 11-JUN-2002 Karen.Tsao Modified to reverse logic of G_MISS_XXX and NULL.
12 --
13 -- NOTE
14 --
15 -- Copyright (c) 2002 Oracle Corporation Redwood Shores, California, USA
16 -- All rights reserved.
17 --
18 -- End of Comments
19 -- ===============================================================
20
21
22 G_PKG_NAME CONSTANT VARCHAR2(30):= 'PV_PARTNER_PGM_TYPE_PKG';
23 G_FILE_NAME CONSTANT VARCHAR2(12) := 'pvxtpttb.pls';
24
25
26 -- ========================================================
27 --
28 -- NAME
29 -- Insert_Row
30 --
31 -- PURPOSE
32 --
33 -- NOTES
34 --
35 -- HISTORY
36 --
37 -- ========================================================
38 PV_DEBUG_HIGH_ON CONSTANT boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH);
39 PV_DEBUG_LOW_ON CONSTANT boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
40 PV_DEBUG_MEDIUM_ON CONSTANT boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_MEDIUM);
41
42 PROCEDURE Insert_Row(
43 px_PROGRAM_TYPE_ID IN OUT NOCOPY NUMBER
44 ,p_active_flag VARCHAR2
45 ,p_enabled_flag VARCHAR2
46 ,p_object_version_number NUMBER
47 ,p_creation_date DATE
48 ,p_created_by NUMBER
49 ,p_last_update_date DATE
50 ,p_last_updated_by NUMBER
51 ,p_last_update_login NUMBER
52 ,p_program_type_name VARCHAR2
53 ,p_program_type_description VARCHAR2
54 )
55
56 IS
57
58 BEGIN
59
60 INSERT INTO PV_PARTNER_PROGRAM_TYPE_B(
61 PROGRAM_TYPE_ID
62 ,active_flag
63 ,enabled_flag
64 ,object_version_number
65 ,creation_date
66 ,created_by
67 ,last_update_date
68 ,last_updated_by
69 ,last_update_login
70 ) VALUES (
71 DECODE( px_PROGRAM_TYPE_ID, NULL, px_PROGRAM_TYPE_ID, FND_API.g_miss_num, NULL, px_PROGRAM_TYPE_ID)
72 ,DECODE( p_active_flag, NULL, p_active_flag, FND_API.g_miss_char, NULL, p_active_flag)
73 ,DECODE( p_enabled_flag, NULL, p_enabled_flag, FND_API.g_miss_char, NULL, p_enabled_flag)
74 ,DECODE( p_object_version_number, NULL, p_object_version_number, FND_API.g_miss_num, NULL, p_object_version_number)
75 ,DECODE( p_creation_date, NULL, p_creation_date, FND_API.g_miss_date, NULL, p_creation_date)
76 ,DECODE( p_created_by, NULL, p_created_by, FND_API.g_miss_num, NULL, p_created_by)
77 ,DECODE( p_last_update_date, NULL, p_last_update_date, FND_API.g_miss_date, NULL, p_last_update_date)
78 ,DECODE( p_last_updated_by, NULL, p_last_updated_by, FND_API.g_miss_num, NULL, p_last_updated_by)
79 ,DECODE( p_last_update_login, NULL, p_last_update_login, FND_API.g_miss_num, NULL, p_last_update_login)
80 );
81
82
83 INSERT INTO PV_PARTNER_PROGRAM_TYPE_TL(
84 PROGRAM_TYPE_ID
85 ,creation_date
86 ,created_by
87 ,last_update_date
88 ,last_updated_by
89 ,last_update_login
90 ,language
91 ,source_lang
92 ,program_type_name
93 ,program_type_description
94 )
95 SELECT
96 DECODE( px_PROGRAM_TYPE_ID, NULL, px_PROGRAM_TYPE_ID, FND_API.g_miss_num, NULL, px_PROGRAM_TYPE_ID)
97 ,SYSDATE
98 ,FND_GLOBAL.user_id
99 ,SYSDATE
100 ,FND_GLOBAL.user_id
101 ,FND_GLOBAL.conc_login_id
102 ,l.language_code
103 ,USERENV('LANG')
104 ,DECODE( p_program_type_name, NULL, p_program_type_name, FND_API.g_miss_char, NULL, p_program_type_name)
105 ,DECODE( p_program_type_description, NULL, p_program_type_description, FND_API.g_miss_char, NULL, p_program_type_description)
106 FROM FND_LANGUAGES l
107 WHERE l.installed_flag IN ('I','B')
108 AND NOT EXISTS(
109 SELECT NULL
110 FROM PV_PARTNER_PROGRAM_TYPE_TL t
111 WHERE t.PROGRAM_TYPE_ID = DECODE( px_PROGRAM_TYPE_ID, NULL, px_PROGRAM_TYPE_ID, FND_API.g_miss_num, NULL, px_PROGRAM_TYPE_ID)
112 AND t.language = l.language_code
113 );
114
115 END Insert_Row;
116
117
118
119 -- ========================================================
120 --
121 -- NAME
122 -- Update_Row
123 --
124 -- PURPOSE
125 --
126 -- NOTES
127 --
128 -- HISTORY
129 --
130 -- ========================================================
131 PROCEDURE Update_Row(
132 p_PROGRAM_TYPE_ID NUMBER
133 ,p_active_flag VARCHAR2
134 ,p_enabled_flag VARCHAR2
135 ,p_object_version_number NUMBER
136 ,p_last_update_date DATE
137 ,p_last_updated_by NUMBER
138 ,p_last_update_login NUMBER
139 ,p_program_type_name VARCHAR2
140 ,p_program_type_description VARCHAR2
141 )
142
143 IS
144 BEGIN
145
146 IF (PV_DEBUG_HIGH_ON) THEN
147
148
149
150 PVX_Utility_PVT.debug_message('Within PV_PARTNER_PGM_TYPE_PKG.UPDATE_ROW API: ');
151
152 END IF;
153 IF (PV_DEBUG_HIGH_ON) THEN
154
155 PVX_Utility_PVT.debug_message('Within PV_PARTNER_PGM_TYPE_PKG.UPDATE_ROW API : object_version_number ' ||p_object_version_number );
156 END IF;
157
158
159 Update PV_PARTNER_PROGRAM_TYPE_B
160 SET
161 PROGRAM_TYPE_ID = DECODE( p_PROGRAM_TYPE_ID, NULL, PROGRAM_TYPE_ID, FND_API.g_miss_num, NULL, p_PROGRAM_TYPE_ID)
162 ,active_flag = DECODE( p_active_flag, NULL, active_flag, FND_API.g_miss_char, NULL, p_active_flag)
163 ,enabled_flag = DECODE( p_enabled_flag, NULL, enabled_flag, FND_API.g_miss_char, NULL, p_enabled_flag)
164 ,object_version_number = DECODE( p_object_version_number, NULL, object_version_number, FND_API.g_miss_num, NULL, p_object_version_number+1)
165 ,last_update_date = DECODE( p_last_update_date, NULL, last_update_date, FND_API.g_miss_date, NULL, p_last_update_date)
166 ,last_updated_by = DECODE( p_last_updated_by, NULL, last_updated_by, FND_API.g_miss_num, NULL, p_last_updated_by)
167 ,last_update_login = DECODE( p_last_update_login, NULL, last_update_login, FND_API.g_miss_num, NULL, p_last_update_login)
168
169 WHERE PROGRAM_TYPE_ID = p_PROGRAM_TYPE_ID
170 AND object_version_number = p_object_version_number;
171
172 IF (SQL%NOTFOUND) THEN
173 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
174 FND_MESSAGE.set_name('PV', 'PV_RECORD_NOT_FOUND');
175 FND_MSG_PUB.add;
176 END IF;
177 RAISE FND_API.g_exc_error;
178 END IF;
179
180 UPDATE PV_PARTNER_PROGRAM_TYPE_TL
181 SET
182 last_update_date = SYSDATE
183 ,last_updated_by = FND_GLOBAL.user_id
184 ,last_update_login = FND_GLOBAL.conc_login_id
185 ,source_lang = USERENV('LANG')
186 ,program_type_name = DECODE( p_program_type_name, NULL, program_type_name, FND_API.g_miss_char, NULL, p_program_type_name)
187 ,program_type_description = DECODE( p_program_type_description, NULL, program_type_description, FND_API.g_miss_char, NULL, p_program_type_description)
188 WHERE PROGRAM_TYPE_ID = p_PROGRAM_TYPE_ID
189 AND USERENV('LANG') IN (language, source_lang);
190
191 IF (SQL%NOTFOUND) THEN
192 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
193 FND_MESSAGE.set_name('PV', 'PV_RECORD_NOT_FOUND');
194 FND_MSG_PUB.add;
195 END IF;
196 RAISE FND_API.g_exc_error;
197 END IF;
198
199 END Update_Row;
200
201
202
203 -- ========================================================
204 --
205 -- NAME
206 -- Delete_Row
207 --
208 -- PURPOSE
209 --
210 -- NOTES
211 --
212 -- HISTORY
213 --
214 -- ========================================================
215 PROCEDURE Delete_Row(
216 p_PROGRAM_TYPE_ID NUMBER
217 ,p_object_version_number NUMBER
218 )
219 IS
220 BEGIN
221
222 DELETE FROM PV_PARTNER_PROGRAM_TYPE_TL
223 WHERE PROGRAM_TYPE_ID = p_PROGRAM_TYPE_ID;
224
225 IF (SQL%NOTFOUND) THEN
226 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
227 FND_MESSAGE.set_name('PV', 'PV_RECORD_NOT_FOUND');
228 FND_MSG_PUB.add;
229 END IF;
230 RAISE FND_API.g_exc_error;
231 END IF;
232
233 DELETE FROM PV_PARTNER_PROGRAM_TYPE_B
234 WHERE PROGRAM_TYPE_ID = p_PROGRAM_TYPE_ID
235 AND object_version_number = p_object_version_number;
236
237 IF (SQL%NOTFOUND) THEN
238 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
239 FND_MESSAGE.set_name('PV', 'PV_RECORD_NOT_FOUND');
240 FND_MSG_PUB.add;
241 END IF;
242 RAISE FND_API.g_exc_error;
243 END IF;
244
245 END Delete_Row ;
246
247
248
249
250 -- ========================================================
251 --
252 -- NAME
253 -- Lock_Row
254 --
255 -- PURPOSE
256 --
257 -- NOTES
258 --
259 -- HISTORY
260 --
261 -- ========================================================
262 PROCEDURE Lock_Row(
263 px_PROGRAM_TYPE_ID IN OUT NOCOPY NUMBER
264 ,p_active_flag VARCHAR2
265 ,p_enabled_flag VARCHAR2
266 ,px_object_version_number IN OUT NOCOPY NUMBER
267 ,p_creation_date DATE
268 ,p_created_by NUMBER
269 ,p_last_update_date DATE
270 ,p_last_updated_by NUMBER
271 ,p_last_update_login NUMBER
272 )
273
274 IS
275 CURSOR C IS
276 SELECT *
277 FROM PV_PARTNER_PROGRAM_TYPE_B
278 WHERE PROGRAM_TYPE_ID = px_PROGRAM_TYPE_ID
279 FOR UPDATE of PROGRAM_TYPE_ID NOWAIT;
280 Recinfo C%ROWTYPE;
281
282 BEGIN
283 OPEN c;
284 FETCH c INTO Recinfo;
285 If (c%NOTFOUND) then
286 CLOSE c;
287 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
288 APP_EXCEPTION.RAISE_EXCEPTION;
289 END IF;
290 CLOSE C;
291 IF (
292 ( Recinfo.PROGRAM_TYPE_ID = px_PROGRAM_TYPE_ID)
293 AND ( ( Recinfo.active_flag = p_active_flag)
294 OR ( ( Recinfo.active_flag IS NULL )
295 AND ( p_active_flag IS NULL )))
296 AND ( ( Recinfo.enabled_flag = p_enabled_flag)
297 OR ( ( Recinfo.enabled_flag IS NULL )
298 AND ( p_enabled_flag IS NULL )))
299 AND ( ( Recinfo.object_version_number = px_object_version_number)
300 OR ( ( Recinfo.object_version_number IS NULL )
301 AND ( px_object_version_number IS NULL )))
302 AND ( ( Recinfo.creation_date = p_creation_date)
303 OR ( ( Recinfo.creation_date IS NULL )
304 AND ( p_creation_date IS NULL )))
305 AND ( ( Recinfo.created_by = p_created_by)
306 OR ( ( Recinfo.created_by IS NULL )
307 AND ( p_created_by IS NULL )))
308 AND ( ( Recinfo.last_update_date = p_last_update_date)
309 OR ( ( Recinfo.last_update_date IS NULL )
310 AND ( p_last_update_date IS NULL )))
311 AND ( ( Recinfo.last_updated_by = p_last_updated_by)
312 OR ( ( Recinfo.last_updated_by IS NULL )
313 AND ( p_last_updated_by IS NULL )))
314 AND ( ( Recinfo.last_update_login = p_last_update_login)
315 OR ( ( Recinfo.last_update_login IS NULL )
316 AND ( p_last_update_login IS NULL )))
317 ) THEN
318 RETURN;
319 ELSE
320 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_CHANGED');
321 APP_EXCEPTION.RAISE_EXCEPTION;
322 END IF;
323
324 END Lock_Row;
325
326
327
328
329 -- ========================================================
330 --
331 -- NAME
332 -- Add_Language
333 --
334 -- PURPOSE
335 --
336 -- NOTES
337 --
338 -- HISTORY
339 --
340 -- ========================================================
341 PROCEDURE Add_Language
342 IS
343 BEGIN
344 -- changing by ktsao as per performance team guidelines to fix performance issue
345 -- as described in bug 3723612 (*** RTIKKU 03/24/05 12:46pm ***)
346 INSERT /*+ append parallel(tt) */ INTO PV_PARTNER_PROGRAM_TYPE_TL tt (
347 PROGRAM_TYPE_ID
348 ,LAST_UPDATE_DATE
349 ,LAST_UPDATED_BY
350 ,CREATION_DATE
351 ,CREATED_BY
352 ,LAST_UPDATE_LOGIN
353 ,LANGUAGE
354 ,SOURCE_LANG
355 ,PROGRAM_TYPE_NAME
356 ,PROGRAM_TYPE_DESCRIPTION
357 )
358 select /*+ parallel(v) parallel(t) use_nl(t) */ v.* from
359 ( SELECT /*+ no_merge ordered parallel(b) */
360 B.PROGRAM_TYPE_ID
361 ,B.LAST_UPDATE_DATE
362 ,B.LAST_UPDATED_BY
363 ,B.CREATION_DATE
364 ,B.CREATED_BY
365 ,B.LAST_UPDATE_LOGIN
366 ,L.LANGUAGE_CODE
367 ,B.SOURCE_LANG
368 ,B.PROGRAM_TYPE_NAME
369 ,B.PROGRAM_TYPE_DESCRIPTION
370
371 FROM PV_PARTNER_PROGRAM_TYPE_TL B ,
372 FND_LANGUAGES L
373 WHERE L.INSTALLED_FLAG IN ( 'I','B' )
374 AND B.LANGUAGE = USERENV ( 'LANG' )
375 ) v, PV_PARTNER_PROGRAM_TYPE_TL t
376 WHERE t.PROGRAM_TYPE_ID(+) = v.PROGRAM_TYPE_ID
377 AND t.language(+) = v.language_code
378 AND t.PROGRAM_TYPE_ID IS NULL;
379
380 END Add_Language;
381
382
383
384
385 -- ========================================================
386 --
387 -- NAME
388 -- Translate_Row
389 --
390 -- PURPOSE
391 --
392 -- NOTES
393 --
394 -- HISTORY
395 --
396 -- ========================================================
397 PROCEDURE Translate_Row(
398 px_PROGRAM_TYPE_ID IN NUMBER
399 ,p_program_type_name IN VARCHAR2
400 ,p_program_type_description IN VARCHAR2
401 ,p_owner IN VARCHAR2
402 )
403
404 IS
405
406 BEGIN
407 UPDATE PV_PARTNER_PROGRAM_TYPE_TL SET
408 PROGRAM_TYPE_NAME = NVL(p_program_type_name, program_type_name)
409 ,PROGRAM_TYPE_DESCRIPTION = NVL(p_program_type_description, program_type_description)
410 ,SOURCE_LANG = USERENV('LANG')
411 ,LAST_UPDATE_DATE = SYSDATE
412 ,LAST_UPDATED_BY = DECODE(p_owner, 'SEED', 1, 0)
413 ,LAST_UPDATE_LOGIN = 0
414 WHERE PROGRAM_TYPE_ID = px_PROGRAM_TYPE_ID
415 AND USERENV('LANG') IN (language, source_lang);
416
417 END TRANSLATE_ROW;
418
419
420 END PV_PARTNER_PGM_TYPE_PKG;