[Home] [Help]
PACKAGE BODY: APPS.AMS_R_CODE_DEFINITIONS_PKG
Source
1 PACKAGE BODY AMS_R_CODE_DEFINITIONS_PKG as
2 /* $Header: amstcdnb.pls 120.1 2005/06/27 05:39:43 appldev ship $ */
3 -- ===============================================================
4 -- Start of Comments
5 -- Package name
6 -- AMS_R_CODE_DEFINITIONS_PKG
7 -- Purpose
8 --
9 -- History
10 --
11 -- NOTE
12 --
13 -- End of Comments
14 -- ===============================================================
15
16
17 G_PKG_NAME CONSTANT VARCHAR2(30):= 'AMS_R_CODE_DEFINITIONS_PKG';
18 G_FILE_NAME CONSTANT VARCHAR2(12) := 'amstcdnb.pls';
19
20
21 ----------------------------------------------------------
22 ---- MEDIA ----
23 ----------------------------------------------------------
24
25 -- ========================================================
26 --
27 -- NAME
28 -- createInsertBody
29 --
30 -- PURPOSE
31 --
32 -- NOTES
33 --
34 -- HISTORY
35 --
36 -- ========================================================
37 PROCEDURE Insert_Row(
38 p_creation_date DATE,
39 p_last_update_date DATE,
40 p_created_by NUMBER,
41 p_last_updated_by NUMBER,
42 p_last_update_login NUMBER,
43 p_object_type VARCHAR2,
44 p_column_name VARCHAR2,
45 p_object_def VARCHAR2,
46 px_code_definition_id IN OUT NOCOPY NUMBER,
47 px_object_version_number IN OUT NOCOPY NUMBER)
48
49 IS
50 x_rowid VARCHAR2(30);
51 l_dbi_rank_count NUMBER:=0;
52
53 CURSOR c_get_dbi_ranks IS
54 SELECT count(*)
55 FROM BIM_R_CODE_DEFINITIONS
56 WHERE object_type = 'RANK_DBI'
57 and column_name = 'Z';
58
59
60 BEGIN
61
62
63 --AMS_UTILITY_PVT.debug_message( 'Inside insert Row ');
64
65 px_object_version_number := 1;
66
67 --AMS_UTILITY_PVT.debug_message( 'Before calling insert ');
68
69
70 INSERT INTO BIM_R_CODE_DEFINITIONS(
71 creation_date,
72 last_update_date,
73 created_by,
74 last_updated_by,
75 last_update_login,
76 object_type,
77 column_name,
78 object_def,
79 code_definition_id,
80 object_version_number
81 ) VALUES (
82 DECODE( p_creation_date, FND_API.g_miss_date, NULL, p_creation_date),
83 DECODE( p_last_update_date, FND_API.g_miss_date, NULL, p_last_update_date),
84 DECODE( p_created_by, FND_API.g_miss_num, NULL, p_created_by),
85 DECODE( p_last_updated_by, FND_API.g_miss_num, NULL, p_last_updated_by),
86 DECODE( p_last_update_login, FND_API.g_miss_num, NULL, p_last_update_login),
87 DECODE( p_object_type, FND_API.g_miss_char, NULL, p_object_type),
88 DECODE( p_column_name, FND_API.g_miss_char, NULL, p_column_name),
89 DECODE( p_object_def, FND_API.g_miss_char, NULL, p_object_def),
90 -- DECODE( px_code_definition_id, FND_API.g_miss_num, NULL, px_code_definition_id),
91 BIM_R_CODE_DEFINITIONS_s.NEXTVAL,
92 DECODE( px_object_version_number, FND_API.g_miss_num, NULL, px_object_version_number));
93
94 -- AMS_UTILITY_PVT.debug_message( 'After first Insert_Row '||p_object_type);
95
96 if( p_object_type = 'RANK') then
97
98 --AMS_UTILITY_PVT.debug_message( 'Inside Rank loop'||p_object_type);
99
100 --delete from BIM_R_CODE_DEFINITIONS where object_type = 'RANK_DBI';
101
102 INSERT INTO BIM_R_CODE_DEFINITIONS(
103 creation_date,
104 last_update_date,
105 created_by,
106 last_updated_by,
107 last_update_login,
108 object_type,
109 column_name,
110 object_def,
111 code_definition_id,
112 object_version_number
113 ) VALUES (
114 DECODE( p_creation_date, FND_API.g_miss_date, NULL, p_creation_date),
115 DECODE( p_last_update_date, FND_API.g_miss_date, NULL, p_last_update_date),
116 DECODE( p_created_by, FND_API.g_miss_num, NULL, p_created_by),
117 DECODE( p_last_updated_by, FND_API.g_miss_num, NULL, p_last_updated_by),
118 DECODE( p_last_update_login, FND_API.g_miss_num, NULL, p_last_update_login),
119 'RANK_DBI',
120 DECODE( p_column_name, FND_API.g_miss_char, NULL,'A',p_column_name,'B',p_column_name,'C',p_column_name,'D',p_column_name,'Z'),
121 DECODE( p_object_def, FND_API.g_miss_char, NULL, p_object_def),
122 BIM_R_CODE_DEFINITIONS_s.NEXTVAL,
123 DECODE( px_object_version_number, FND_API.g_miss_num, NULL, px_object_version_number));
124
125 --AMS_UTILITY_PVT.debug_message( 'After second Insert_Row ');
126
127 OPEN c_get_dbi_ranks;
128
129 FETCH c_get_dbi_ranks INTO l_dbi_rank_count ;
130
131 If ( l_dbi_rank_count>0) THEN
132
133 DELETE FROM BIM_R_CODE_DEFINITIONS WHERE column_name = 'Z';
134
135 END IF;
136
137 --AMS_UTILITY_PVT.debug_message( 'After third Insert_Row ');
138
139
140 INSERT INTO BIM_R_CODE_DEFINITIONS(
141 creation_date,
142 last_update_date,
143 created_by,
144 last_updated_by,
145 last_update_login,
146 object_type,
147 column_name,
148 object_def,
149 code_definition_id,
150 object_version_number)
151 ( SELECT
152 sysdate,
153 sysdate,
154 1,
155 1,
156 1,
157 'RANK_DBI',
158 'Z',
159 rank_id,
160 BIM_R_CODE_DEFINITIONS_s.NEXTVAL,
161 1
162 FROM AS_SALES_LEAD_RANKS_VL a
163 --WHERE enabled_flag = 'Y'
164 WHERE to_char(a.rank_id)
165 NOT IN
166 ( SELECT object_def from BIM_R_CODE_DEFINITIONS WHERE object_type = 'RANK' and column_name in ('A','B','C','D')
167 )
168 )
169 ;
170
171 --AMS_UTILITY_PVT.debug_message( 'After fourth Insert_Row ');
172
173 end if;
174 END Insert_Row;
175
176
177 ----------------------------------------------------------
178 ---- MEDIA ----
179 ----------------------------------------------------------
180
181 -- ========================================================
182 --
183 -- NAME
184 -- createUpdateBody
185 --
186 -- PURPOSE
187 --
188 -- NOTES
189 --
190 -- HISTORY
191 --
192 -- ========================================================
193 PROCEDURE Update_Row(
194 p_last_update_date DATE,
195 p_last_updated_by NUMBER,
196 p_last_update_login NUMBER,
197 p_object_type VARCHAR2,
198 p_column_name VARCHAR2,
199 p_object_def VARCHAR2,
200 p_code_definition_id NUMBER,
201 p_object_version_number NUMBER)
202
203 IS
204
205 CURSOR c_get_dbi_ranks(l_code_definition_id IN NUMBER) IS
206 SELECT count(*)
207 FROM BIM_R_CODE_DEFINITIONS
208 WHERE object_type = 'RANK_DBI'
209 and code_definition_id = l_code_definition_id;
210
211 CURSOR c_get_dbi_ranks2 IS
212 SELECT count(*)
213 FROM BIM_R_CODE_DEFINITIONS
214 WHERE object_type = 'RANK_DBI'
215 and column_name = 'Z';
216
217
218 l_dbi_rank_count NUMBER:=0;
219 l_dbi_rank_count2 NUMBER:=0;
220
221 BEGIN
222 Update BIM_R_CODE_DEFINITIONS
223 SET
224 last_update_date = DECODE( p_last_update_date, FND_API.g_miss_date, last_update_date, p_last_update_date),
225 last_updated_by = DECODE( p_last_updated_by, FND_API.g_miss_num, last_updated_by, p_last_updated_by),
226 last_update_login = DECODE( p_last_update_login, FND_API.g_miss_num, last_update_login, p_last_update_login),
227 object_type = DECODE( p_object_type, FND_API.g_miss_char, object_type, p_object_type),
228 column_name = DECODE( p_column_name, FND_API.g_miss_char, column_name, p_column_name),
229 object_def = DECODE( p_object_def, FND_API.g_miss_char, object_def, p_object_def),
230 code_definition_id = DECODE( p_code_definition_id, FND_API.g_miss_num, code_definition_id, p_code_definition_id),
231 object_version_number = DECODE( p_object_version_number, FND_API.g_miss_num, object_version_number, p_object_version_number + 1)
232 WHERE CODE_DEFINITION_ID = p_CODE_DEFINITION_ID;
233
234 OPEN c_get_dbi_ranks(p_CODE_DEFINITION_ID+1);
235 FETCH c_get_dbi_ranks INTO l_dbi_rank_count;
236 CLOSE c_get_dbi_ranks;
237
238 If ( l_dbi_rank_count>0) THEN
239
240
241
242 if( p_object_type = 'RANK') then
243
244 Update BIM_R_CODE_DEFINITIONS
245 SET
246 last_update_date = DECODE( p_last_update_date, FND_API.g_miss_date, last_update_date, p_last_update_date),
247 last_updated_by = DECODE( p_last_updated_by, FND_API.g_miss_num, last_updated_by, p_last_updated_by),
248 last_update_login = DECODE( p_last_update_login, FND_API.g_miss_num, last_update_login, p_last_update_login),
249 object_type = 'RANK_DBI',
250 column_name = DECODE( p_column_name, FND_API.g_miss_char, column_name, p_column_name),
251 object_def = DECODE( p_object_def, FND_API.g_miss_char, object_def, p_object_def),
252 code_definition_id = DECODE( p_code_definition_id, FND_API.g_miss_num, code_definition_id, p_code_definition_id),
253 object_version_number = DECODE( p_object_version_number, FND_API.g_miss_num, object_version_number, p_object_version_number + 1)
254 WHERE CODE_DEFINITION_ID = p_CODE_DEFINITION_ID+1
255 AND object_type = 'RANK_DBI';
256
257 OPEN c_get_dbi_ranks2;
258
259 FETCH c_get_dbi_ranks2 INTO l_dbi_rank_count2 ;
260
261 If ( l_dbi_rank_count2>0) THEN
262
263 DELETE FROM BIM_R_CODE_DEFINITIONS WHERE column_name = 'Z';
264
265
266 INSERT INTO BIM_R_CODE_DEFINITIONS(
267 creation_date,
268 last_update_date,
269 created_by,
270 last_updated_by,
271 last_update_login,
272 object_type,
273 column_name,
274 object_def,
275 code_definition_id,
276 object_version_number)
277 ( SELECT
278 sysdate,
279 sysdate,
280 1,
281 1,
282 1,
283 'RANK_DBI',
284 'Z',
285 rank_id,
286 BIM_R_CODE_DEFINITIONS_s.NEXTVAL,
287 1
288 FROM AS_SALES_LEAD_RANKS_VL a
289 --WHERE enabled_flag = 'Y'
290 WHERE to_char(a.rank_id)
291 NOT IN
292 ( SELECT object_def from BIM_R_CODE_DEFINITIONS WHERE object_type = 'RANK')
293 )
294 ;
295
296 END IF;
297 END IF;
298 END IF;
299
300 IF (SQL%NOTFOUND) THEN
301 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
302 END IF;
303 END Update_Row;
304
305
306 ----------------------------------------------------------
307 ---- MEDIA ----
308 ----------------------------------------------------------
309
310 -- ========================================================
311 --
312 -- NAME
313 -- createDeleteBody
314 --
315 -- PURPOSE
316 --
317 -- NOTES
318 --
319 -- HISTORY
320 --
321 -- ========================================================
322 PROCEDURE Delete_Row(
323 p_CODE_DEFINITION_ID NUMBER)
324 IS
325 BEGIN
326 DELETE FROM BIM_R_CODE_DEFINITIONS
327 WHERE CODE_DEFINITION_ID = p_CODE_DEFINITION_ID;
328
329 DELETE FROM BIM_R_CODE_DEFINITIONS
330 WHERE CODE_DEFINITION_ID = p_CODE_DEFINITION_ID + 1
331 AND object_type = 'RANK_DBI';
332
333 DELETE FROM BIM_R_CODE_DEFINITIONS WHERE column_name = 'Z';
334
335 INSERT INTO BIM_R_CODE_DEFINITIONS(
336 creation_date,
337 last_update_date,
338 created_by,
339 last_updated_by,
340 last_update_login,
341 object_type,
342 column_name,
343 object_def,
344 code_definition_id,
345 object_version_number)
346 ( SELECT
347 sysdate,
348 sysdate,
349 1,
350 1,
351 1,
352 'RANK_DBI',
353 'Z',
354 rank_id,
355 BIM_R_CODE_DEFINITIONS_s.NEXTVAL,
356 1
357 FROM AS_SALES_LEAD_RANKS_VL a
358 --WHERE enabled_flag = 'Y'
359 WHERE to_char(a.rank_id)
360 NOT IN
361 ( SELECT object_def from BIM_R_CODE_DEFINITIONS WHERE object_type = 'RANK')
362 )
363 ;
364
365 If (SQL%NOTFOUND) then
366 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
367 End If;
368
369
370 END Delete_Row ;
371
372
373
374 ----------------------------------------------------------
375 ---- MEDIA ----
376 ----------------------------------------------------------
377
378 -- ========================================================
379 --
380 -- NAME
381 -- createLockBody
382 --
383 -- PURPOSE
384 --
385 -- NOTES
386 --
387 -- HISTORY
388 --
389 -- ========================================================
390 PROCEDURE Lock_Row(
391 p_creation_date DATE,
392 p_last_update_date DATE,
393 p_created_by NUMBER,
394 p_last_updated_by NUMBER,
395 p_last_update_login NUMBER,
396 p_object_type VARCHAR2,
397 p_column_name VARCHAR2,
398 p_object_def VARCHAR2,
399 p_code_definition_id NUMBER,
400 p_object_version_number NUMBER)
401
402 IS
403 CURSOR C IS
404 SELECT *
405 FROM BIM_R_CODE_DEFINITIONS
406 WHERE CODE_DEFINITION_ID = p_CODE_DEFINITION_ID
407 FOR UPDATE of CODE_DEFINITION_ID NOWAIT;
408 Recinfo C%ROWTYPE;
409 BEGIN
410 OPEN c;
411 FETCH c INTO Recinfo;
412 If (c%NOTFOUND) then
413 CLOSE c;
414 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
415 APP_EXCEPTION.RAISE_EXCEPTION;
416 END IF;
417 CLOSE C;
418 IF (
419 ( Recinfo.creation_date = p_creation_date)
420 AND ( ( Recinfo.last_update_date = p_last_update_date)
421 OR ( ( Recinfo.last_update_date IS NULL )
422 AND ( p_last_update_date IS NULL )))
423 AND ( ( Recinfo.created_by = p_created_by)
424 OR ( ( Recinfo.created_by IS NULL )
425 AND ( p_created_by IS NULL )))
426 AND ( ( Recinfo.last_updated_by = p_last_updated_by)
427 OR ( ( Recinfo.last_updated_by IS NULL )
428 AND ( p_last_updated_by IS NULL )))
429 AND ( ( Recinfo.last_update_login = p_last_update_login)
430 OR ( ( Recinfo.last_update_login IS NULL )
431 AND ( p_last_update_login IS NULL )))
432 AND ( ( Recinfo.object_type = p_object_type)
433 OR ( ( Recinfo.object_type IS NULL )
434 AND ( p_object_type IS NULL )))
435 AND ( ( Recinfo.column_name = p_column_name)
436 OR ( ( Recinfo.column_name IS NULL )
437 AND ( p_column_name IS NULL )))
438 AND ( ( Recinfo.object_def = p_object_def)
439 OR ( ( Recinfo.object_def IS NULL )
440 AND ( p_object_def IS NULL )))
441 AND ( ( Recinfo.code_definition_id = p_code_definition_id)
442 OR ( ( Recinfo.code_definition_id IS NULL )
443 AND ( p_code_definition_id IS NULL )))
444 AND ( ( Recinfo.object_version_number = p_object_version_number)
445 OR ( ( Recinfo.object_version_number IS NULL )
446 AND ( p_object_version_number IS NULL )))
447 ) THEN
448 RETURN;
449 ELSE
450 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_CHANGED');
451 APP_EXCEPTION.RAISE_EXCEPTION;
452 END IF;
453 END Lock_Row;
454
455 END AMS_R_CODE_DEFINITIONS_PKG;