[Home] [Help]
PACKAGE BODY: APPS.AMS_MET_TPL_ASSOCS_PKG
Source
1 PACKAGE BODY AMS_MET_TPL_ASSOCS_PKG AS
2 /* $Header: amslmtab.pls 115.10 2003/03/07 22:45:44 dmvincen ship $ */
3 -- ===============================================================
4 -- Start of Comments
5 -- Package name
6 -- AMS_MET_TPL_ASSOCS_PKG
7 -- Purpose
8 --
9 -- History
10 -- 03/05/2002 dmvincen Created.
11 -- 03/07/2002 dmvincen Added LOAD_ROW.
12 -- 03/06/2003 dmvincen BUG2819067: Do not update if customized.
13 --
14 -- NOTE
15 --
16 -- End of Comments
17 -- ===============================================================
18
19
20 G_PKG_NAME CONSTANT VARCHAR2(30):= 'AMS_MET_TPL_ASSOCS_PKG';
21 G_FILE_NAME CONSTANT VARCHAR2(12) := 'amslmtab.pls';
22
23
24 ----------------------------------------------------------
25 ---- MEDIA ----
26 ----------------------------------------------------------
27
28 -- ========================================================
29 --
30 -- NAME
31 -- createInsertBody
32 --
33 -- PURPOSE
34 --
35 -- NOTES
36 --
37 -- HISTORY
38 --
39 -- ========================================================
40 AMS_DEBUG_HIGH_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH);
41 AMS_DEBUG_LOW_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
42 AMS_DEBUG_MEDIUM_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_MEDIUM);
43
44 PROCEDURE Insert_Row(
45 p_metric_tpl_assoc_id NUMBER,
46 p_last_update_date DATE,
47 p_last_updated_by NUMBER,
48 p_creation_date DATE,
49 p_created_by NUMBER,
50 p_last_update_login NUMBER,
51 px_object_version_number NUMBER,
52 p_metric_tpl_header_id NUMBER,
53 p_association_type VARCHAR2,
54 p_used_by_id NUMBER,
55 p_used_by_code VARCHAR2,
56 p_enabled_flag VARCHAR2)
57
58 IS
59 x_rowid VARCHAR2(30);
60
61 BEGIN
62
63 -- px_object_version_number := 1;
64
65 INSERT INTO AMS_MET_TPL_ASSOCS(
66 metric_tpl_assoc_id,
67 last_update_date,
68 last_updated_by,
69 creation_date,
70 created_by,
71 last_update_login,
72 object_version_number,
73 metric_tpl_header_id,
74 association_type,
75 used_by_id,
76 used_by_code,
77 enabled_flag
78 ) VALUES (
79 DECODE( p_metric_tpl_assoc_id, FND_API.g_miss_num, NULL, p_metric_tpl_assoc_id),
80 DECODE( p_last_update_date, FND_API.g_miss_date, NULL, p_last_update_date),
81 DECODE( p_last_updated_by, FND_API.g_miss_num, NULL, p_last_updated_by),
82 DECODE( p_creation_date, FND_API.g_miss_date, NULL, p_creation_date),
83 DECODE( p_created_by, FND_API.g_miss_num, NULL, p_created_by),
84 DECODE( p_last_update_login, FND_API.g_miss_num, NULL, p_last_update_login),
85 1, -- DECODE( px_object_version_number, FND_API.g_miss_num, NULL, px_object_version_number),
86 DECODE( p_metric_tpl_header_id, FND_API.g_miss_num, NULL, p_metric_tpl_header_id),
87 DECODE( p_association_type, FND_API.g_miss_char, NULL, p_association_type),
88 DECODE( p_used_by_id, FND_API.g_miss_num, NULL, p_used_by_id),
89 DECODE( p_used_by_code, FND_API.g_miss_char, NULL, p_used_by_code),
90 DECODE( p_enabled_flag, FND_API.g_miss_char, NULL, p_enabled_flag));
91 END Insert_Row;
92
93
94 ----------------------------------------------------------
95 ---- MEDIA ----
96 ----------------------------------------------------------
97
98 -- ========================================================
99 --
100 -- NAME
101 -- createUpdateBody
102 --
103 -- PURPOSE
104 --
105 -- NOTES
106 --
107 -- HISTORY
108 --
109 -- ========================================================
110 PROCEDURE Update_Row(
111 p_metric_tpl_assoc_id NUMBER,
112 p_last_update_date DATE,
113 p_last_updated_by NUMBER,
114 p_last_update_login NUMBER,
115 p_object_version_number NUMBER,
116 p_metric_tpl_header_id NUMBER,
117 p_association_type VARCHAR2,
118 p_used_by_id NUMBER,
119 p_used_by_code VARCHAR2,
120 p_enabled_flag VARCHAR2)
121
122 IS
123 BEGIN
124 UPDATE AMS_MET_TPL_ASSOCS
125 SET
126 metric_tpl_assoc_id = DECODE( p_metric_tpl_assoc_id, FND_API.g_miss_num, metric_tpl_assoc_id, p_metric_tpl_assoc_id),
127 last_update_date = DECODE( p_last_update_date, FND_API.g_miss_date, last_update_date, p_last_update_date),
128 last_updated_by = DECODE( p_last_updated_by, FND_API.g_miss_num, last_updated_by, p_last_updated_by),
129 last_update_login = DECODE( p_last_update_login, FND_API.g_miss_num, last_update_login, p_last_update_login),
130 object_version_number = DECODE( p_object_version_number, FND_API.g_miss_num, object_version_number, p_object_version_number),
131 metric_tpl_header_id = DECODE( p_metric_tpl_header_id, FND_API.g_miss_num, metric_tpl_header_id, p_metric_tpl_header_id),
132 association_type = DECODE( p_association_type, FND_API.g_miss_char, association_type, p_association_type),
133 used_by_id = DECODE( p_used_by_id, FND_API.g_miss_num, used_by_id, p_used_by_id),
134 used_by_code = DECODE( p_used_by_code, FND_API.g_miss_char, used_by_code, p_used_by_code),
135 enabled_flag = DECODE( p_enabled_flag, FND_API.g_miss_char, enabled_flag, p_enabled_flag)
136 WHERE METRIC_TPL_ASSOC_ID = p_METRIC_TPL_ASSOC_ID;
137 --AND object_version_number = p_object_version_number;
138
139 IF (SQL%NOTFOUND) THEN
140 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
141 END IF;
142 END Update_Row;
143
144
145 ----------------------------------------------------------
146 ---- MEDIA ----
147 ----------------------------------------------------------
148
149 -- ========================================================
150 --
151 -- NAME
152 -- createDeleteBody
153 --
154 -- PURPOSE
155 --
156 -- NOTES
157 --
158 -- HISTORY
159 --
160 -- ========================================================
161 PROCEDURE Delete_Row(
162 p_METRIC_TPL_ASSOC_ID NUMBER)
163 IS
164 BEGIN
165 DELETE FROM AMS_MET_TPL_ASSOCS
166 WHERE METRIC_TPL_ASSOC_ID = p_METRIC_TPL_ASSOC_ID;
167 IF (SQL%NOTFOUND) THEN
168 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
169 END IF;
170 END Delete_Row ;
171
172
173
174 ----------------------------------------------------------
175 ---- MEDIA ----
176 ----------------------------------------------------------
177
178 -- ========================================================
179 --
180 -- NAME
181 -- createLockBody
182 --
183 -- PURPOSE
184 --
185 -- NOTES
186 --
187 -- HISTORY
188 --
189 -- ========================================================
190 PROCEDURE Lock_Row(
191 p_metric_tpl_assoc_id NUMBER,
192 p_last_update_date DATE,
193 p_last_updated_by NUMBER,
194 p_creation_date DATE,
195 p_created_by NUMBER,
196 p_last_update_login NUMBER,
197 p_object_version_number NUMBER,
198 p_metric_tpl_header_id NUMBER,
199 p_association_type VARCHAR2,
200 p_used_by_id NUMBER,
201 p_used_by_code VARCHAR2,
202 p_enabled_flag VARCHAR2)
203
204 IS
205 CURSOR C IS
206 SELECT *
207 FROM AMS_MET_TPL_ASSOCS
208 WHERE METRIC_TPL_ASSOC_ID = p_METRIC_TPL_ASSOC_ID
209 FOR UPDATE OF METRIC_TPL_ASSOC_ID NOWAIT;
210 Recinfo C%ROWTYPE;
211 BEGIN
212 OPEN c;
213 FETCH c INTO Recinfo;
214 IF (c%NOTFOUND) THEN
215 CLOSE c;
216 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
217 APP_EXCEPTION.RAISE_EXCEPTION;
218 END IF;
219 CLOSE C;
220 IF (
221 ( Recinfo.metric_tpl_assoc_id = p_metric_tpl_assoc_id)
222 AND ( ( Recinfo.last_update_date = p_last_update_date)
223 OR ( ( Recinfo.last_update_date IS NULL )
224 AND ( p_last_update_date IS NULL )))
225 AND ( ( Recinfo.last_updated_by = p_last_updated_by)
226 OR ( ( Recinfo.last_updated_by IS NULL )
227 AND ( p_last_updated_by IS NULL )))
228 AND ( ( Recinfo.creation_date = p_creation_date)
229 OR ( ( Recinfo.creation_date IS NULL )
230 AND ( p_creation_date IS NULL )))
231 AND ( ( Recinfo.created_by = p_created_by)
232 OR ( ( Recinfo.created_by IS NULL )
233 AND ( p_created_by IS NULL )))
234 AND ( ( Recinfo.last_update_login = p_last_update_login)
235 OR ( ( Recinfo.last_update_login IS NULL )
236 AND ( p_last_update_login IS NULL )))
237 AND ( ( Recinfo.object_version_number = p_object_version_number)
238 OR ( ( Recinfo.object_version_number IS NULL )
239 AND ( p_object_version_number IS NULL )))
240 AND ( ( Recinfo.metric_tpl_header_id = p_metric_tpl_header_id)
241 OR ( ( Recinfo.metric_tpl_header_id IS NULL )
242 AND ( p_metric_tpl_header_id IS NULL )))
243 AND ( ( Recinfo.association_type = p_association_type)
244 OR ( ( Recinfo.association_type IS NULL )
245 AND ( p_association_type IS NULL )))
246 AND ( ( Recinfo.used_by_id = p_used_by_id)
247 OR ( ( Recinfo.used_by_id IS NULL )
248 AND ( p_used_by_id IS NULL )))
249 AND ( ( Recinfo.used_by_code = p_used_by_code)
250 OR ( ( Recinfo.used_by_code IS NULL )
251 AND ( p_used_by_code IS NULL )))
252 AND ( ( Recinfo.enabled_flag = p_enabled_flag)
253 OR ( ( Recinfo.enabled_flag IS NULL )
254 AND ( p_enabled_flag IS NULL )))
255 ) THEN
256 RETURN;
257 ELSE
258 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_CHANGED');
259 APP_EXCEPTION.RAISE_EXCEPTION;
260 END IF;
261 END Lock_Row;
262
263 PROCEDURE LOAD_ROW (
264 X_METRIC_TPL_ASSOC_ID NUMBER,
265 X_OBJECT_VERSION_NUMBER NUMBER,
266 X_METRIC_TPL_HEADER_ID NUMBER,
267 X_ASSOCIATION_TYPE VARCHAR2,
268 X_USED_BY_ID NUMBER,
269 X_USED_BY_CODE VARCHAR2,
270 X_ENABLED_FLAG VARCHAR2,
271 X_Owner VARCHAR2,
272 X_CUSTOM_MODE VARCHAR2
273 )
274 IS
275 l_user_id NUMBER := 0;
276 l_obj_verno NUMBER;
277 l_row_id VARCHAR2(100);
278 l_metric_tpl_assoc_id NUMBER;
279 l_db_luby_id NUMBER;
280
281 CURSOR c_db_data_details IS
282 SELECT last_updated_by, object_version_number
283 FROM AMS_MET_TPL_ASSOCS
284 WHERE METRIC_TPL_ASSOC_ID = X_METRIC_TPL_ASSOC_ID;
285
286 CURSOR c_get_mtaid IS
287 SELECT AMS_MET_TPL_ASSOCS_S.NEXTVAL
288 FROM dual;
289
290 BEGIN
291 if X_OWNER = 'SEED' then
292 l_user_id := 1;
293 elsif X_OWNER = 'ORACLE' THEN
294 l_user_id := 2;
295 elsif X_OWNER = 'SYSADMIN' THEN
296 l_user_id := 0;
297 end if ;
298
299 OPEN c_db_data_details;
300 FETCH c_db_data_details INTO l_db_luby_id, l_obj_verno;
301 IF c_db_data_details%NOTFOUND
302 THEN
303 CLOSE c_db_data_details;
304
305 IF X_METRIC_TPL_ASSOC_ID IS NULL THEN
306 OPEN c_get_mtaid;
307 FETCH c_get_mtaid INTO L_METRIC_TPL_ASSOC_ID;
308 CLOSE c_get_mtaid;
309 ELSE
310 L_METRIC_TPL_ASSOC_ID := X_METRIC_TPL_ASSOC_ID;
311 END IF ;
312
313 l_obj_verno := 1;
314
315 Insert_Row(
316 p_metric_tpl_assoc_id => l_metric_tpl_assoc_id,
317 p_last_update_date => sysdate,
318 p_last_updated_by => l_user_id,
319 p_creation_date => sysdate,
320 p_created_by => l_user_id,
321 p_last_update_login => 0,
322 px_object_version_number => l_obj_verno,
323 p_metric_tpl_header_id => x_metric_tpl_header_id,
324 p_association_type => x_association_type,
325 p_used_by_id => x_used_by_id,
326 p_used_by_code => x_used_by_code,
327 p_enabled_flag => x_enabled_flag);
328
329 ELSE
330 CLOSE c_db_data_details;
331 if ( l_db_luby_id IN (1, 2, 0)
332 OR NVL(x_custom_mode,'PRESERVE') = 'FORCE') THEN
333 Update_Row(
334 p_metric_tpl_assoc_id => x_metric_tpl_assoc_id,
335 p_last_update_date => sysdate,
336 p_last_updated_by => l_user_id,
337 p_last_update_login => 0,
338 p_object_version_number => l_obj_verno + 1,
339 p_metric_tpl_header_id => x_metric_tpl_header_id,
340 p_association_type => x_association_type,
341 p_used_by_id => x_used_by_id,
342 p_used_by_code => x_used_by_code,
343 p_enabled_flag => x_enabled_flag);
344 END IF;
345 END IF;
346 END LOAD_ROW;
347
348 END AMS_MET_TPL_ASSOCS_PKG;