[Home] [Help]
PACKAGE BODY: APPS.AMS_MET_TPL_DETAILS_PKG
Source
1 PACKAGE BODY Ams_Met_Tpl_Details_Pkg AS
2 /* $Header: amslmtdb.pls 115.12 2003/03/07 22:45:50 dmvincen ship $ */
3 -- ===============================================================
4 -- Start of Comments
5 -- Package name
6 -- AMS_MET_TPL_DETAILS_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_DETAILS_PKG';
21 G_FILE_NAME CONSTANT VARCHAR2(12) := 'amslmtdb.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 px_metric_template_detail_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_metric_id NUMBER,
54 p_enabled_flag VARCHAR2)
55
56 IS
57 x_rowid VARCHAR2(30);
58
59
60 BEGIN
61
62
63 -- px_object_version_number := 1;
64
65
66 INSERT INTO AMS_MET_TPL_DETAILS(
67 metric_template_detail_id,
68 last_update_date,
69 last_updated_by,
70 creation_date,
71 created_by,
72 last_update_login,
73 object_version_number,
74 metric_tpl_header_id,
75 metric_id,
76 enabled_flag
77 ) VALUES (
78 DECODE( px_metric_template_detail_id, FND_API.g_miss_num, NULL, px_metric_template_detail_id),
79 DECODE( p_last_update_date, FND_API.g_miss_date, NULL, p_last_update_date),
80 DECODE( p_last_updated_by, FND_API.g_miss_num, NULL, p_last_updated_by),
81 DECODE( p_creation_date, FND_API.g_miss_date, NULL, p_creation_date),
82 DECODE( p_created_by, FND_API.g_miss_num, NULL, p_created_by),
83 DECODE( p_last_update_login, FND_API.g_miss_num, NULL, p_last_update_login),
84 1, --DECODE( px_object_version_number, FND_API.g_miss_num, NULL, px_object_version_number),
85 DECODE( p_metric_tpl_header_id, FND_API.g_miss_num, NULL, p_metric_tpl_header_id),
86 DECODE( p_metric_id, FND_API.g_miss_num, NULL, p_metric_id),
87 DECODE( p_enabled_flag, FND_API.g_miss_char, NULL, p_enabled_flag));
88 END Insert_Row;
89
90
91 ----------------------------------------------------------
92 ---- MEDIA ----
93 ----------------------------------------------------------
94
95 -- ========================================================
96 --
97 -- NAME
98 -- createUpdateBody
99 --
100 -- PURPOSE
101 --
102 -- NOTES
103 --
104 -- HISTORY
105 --
106 -- ========================================================
107 PROCEDURE Update_Row(
108 p_metric_template_detail_id NUMBER,
109 p_last_update_date DATE,
110 p_last_updated_by NUMBER,
111 p_last_update_login NUMBER,
112 p_object_version_number NUMBER,
113 p_metric_tpl_header_id NUMBER,
114 p_metric_id NUMBER,
115 p_enabled_flag VARCHAR2)
116
117 IS
118 BEGIN
119 UPDATE AMS_MET_TPL_DETAILS
120 SET
121 metric_template_detail_id = DECODE( p_metric_template_detail_id, FND_API.g_miss_num, metric_template_detail_id, p_metric_template_detail_id),
122 last_update_date = DECODE( p_last_update_date, FND_API.g_miss_date, last_update_date, p_last_update_date),
123 last_updated_by = DECODE( p_last_updated_by, FND_API.g_miss_num, last_updated_by, p_last_updated_by),
124 last_update_login = DECODE( p_last_update_login, FND_API.g_miss_num, last_update_login, p_last_update_login),
125 object_version_number = DECODE( p_object_version_number, FND_API.g_miss_num, object_version_number, p_object_version_number),
126 metric_tpl_header_id = DECODE( p_metric_tpl_header_id, FND_API.g_miss_num, metric_tpl_header_id, p_metric_tpl_header_id),
127 metric_id = DECODE( p_metric_id, FND_API.g_miss_num, metric_id, p_metric_id),
128 enabled_flag = DECODE( p_enabled_flag, FND_API.g_miss_char, enabled_flag, p_enabled_flag)
129 WHERE METRIC_TEMPLATE_DETAIL_ID = p_METRIC_TEMPLATE_DETAIL_ID;
130 -- AND object_version_number = p_object_version_number;
131
132 IF (SQL%NOTFOUND) THEN
133 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
134 END IF;
135 END Update_Row;
136
137
138 ----------------------------------------------------------
139 ---- MEDIA ----
140 ----------------------------------------------------------
141
142 -- ========================================================
143 --
144 -- NAME
145 -- createDeleteBody
146 --
147 -- PURPOSE
148 --
149 -- NOTES
150 --
151 -- HISTORY
152 --
153 -- ========================================================
154 PROCEDURE Delete_Row(
155 p_METRIC_TEMPLATE_DETAIL_ID NUMBER)
156 IS
157 BEGIN
158 DELETE FROM AMS_MET_TPL_DETAILS
159 WHERE METRIC_TEMPLATE_DETAIL_ID = p_METRIC_TEMPLATE_DETAIL_ID;
160 IF (SQL%NOTFOUND) THEN
161 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
162 END IF;
163 END Delete_Row ;
164
165
166
167 ----------------------------------------------------------
168 ---- MEDIA ----
169 ----------------------------------------------------------
170
171 -- ========================================================
172 --
173 -- NAME
174 -- createLockBody
175 --
176 -- PURPOSE
177 --
178 -- NOTES
179 --
180 -- HISTORY
181 --
182 -- ========================================================
183 PROCEDURE Lock_Row(
184 p_metric_template_detail_id NUMBER,
185 p_last_update_date DATE,
186 p_last_updated_by NUMBER,
187 p_creation_date DATE,
188 p_created_by NUMBER,
189 p_last_update_login NUMBER,
190 p_object_version_number NUMBER,
191 p_metric_tpl_header_id NUMBER,
192 p_metric_id NUMBER,
193 p_enabled_flag VARCHAR2)
194
195 IS
196 CURSOR C IS
197 SELECT *
198 FROM AMS_MET_TPL_DETAILS
199 WHERE METRIC_TEMPLATE_DETAIL_ID = p_METRIC_TEMPLATE_DETAIL_ID
200 FOR UPDATE OF METRIC_TEMPLATE_DETAIL_ID NOWAIT;
201 Recinfo C%ROWTYPE;
202 BEGIN
203 OPEN c;
204 FETCH c INTO Recinfo;
205 IF (c%NOTFOUND) THEN
206 CLOSE c;
207 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
208 APP_EXCEPTION.RAISE_EXCEPTION;
209 END IF;
210 CLOSE C;
211 IF (
212 ( Recinfo.metric_template_detail_id = p_metric_template_detail_id)
213 AND ( ( Recinfo.last_update_date = p_last_update_date)
214 OR ( ( Recinfo.last_update_date IS NULL )
215 AND ( p_last_update_date IS NULL )))
216 AND ( ( Recinfo.last_updated_by = p_last_updated_by)
217 OR ( ( Recinfo.last_updated_by IS NULL )
218 AND ( p_last_updated_by IS NULL )))
219 AND ( ( Recinfo.creation_date = p_creation_date)
220 OR ( ( Recinfo.creation_date IS NULL )
221 AND ( p_creation_date IS NULL )))
222 AND ( ( Recinfo.created_by = p_created_by)
223 OR ( ( Recinfo.created_by IS NULL )
224 AND ( p_created_by IS NULL )))
225 AND ( ( Recinfo.last_update_login = p_last_update_login)
226 OR ( ( Recinfo.last_update_login IS NULL )
227 AND ( p_last_update_login IS NULL )))
228 AND ( ( Recinfo.object_version_number = p_object_version_number)
229 OR ( ( Recinfo.object_version_number IS NULL )
230 AND ( p_object_version_number IS NULL )))
231 AND ( ( Recinfo.metric_tpl_header_id = p_metric_tpl_header_id)
232 OR ( ( Recinfo.metric_tpl_header_id IS NULL )
233 AND ( p_metric_tpl_header_id IS NULL )))
234 AND ( ( Recinfo.metric_id = p_metric_id)
235 OR ( ( Recinfo.metric_id IS NULL )
236 AND ( p_metric_id IS NULL )))
237 AND ( ( Recinfo.enabled_flag = p_enabled_flag)
238 OR ( ( Recinfo.enabled_flag IS NULL )
239 AND ( p_enabled_flag IS NULL )))
240 ) THEN
241 RETURN;
242 ELSE
243 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_CHANGED');
244 APP_EXCEPTION.RAISE_EXCEPTION;
245 END IF;
246 END Lock_Row;
247
248 PROCEDURE LOAD_ROW (
249 X_METRIC_TEMPLATE_DETAIL_ID IN NUMBER,
250 X_OBJECT_VERSION_NUMBER IN NUMBER,
251 X_METRIC_TPL_HEADER_ID IN NUMBER,
252 X_METRIC_ID IN NUMBER,
253 X_ENABLED_FLAG IN VARCHAR2,
254 X_Owner IN VARCHAR2,
255 X_CUSTOM_MODE IN VARCHAR2
256 )
257 IS
258 l_user_id NUMBER := 0;
259 l_obj_verno NUMBER;
260 l_row_id VARCHAR2(100);
261 l_metric_template_detail_id NUMBER;
262 l_db_luby_id NUMBER;
263
264 CURSOR c_db_data_details IS
265 SELECT last_updated_by, object_version_number
266 FROM AMS_MET_TPL_DETAILS
267 WHERE METRIC_TEMPLATE_DETAIL_ID = X_METRIC_TEMPLATE_DETAIL_ID;
268
269 CURSOR c_get_mtdid IS
270 SELECT AMS_MET_TPL_DETAILS_S.NEXTVAL
271 FROM dual;
272
273 BEGIN
274
275 if X_OWNER = 'SEED' then
276 l_user_id := 1;
277 elsif X_OWNER = 'ORACLE' THEN
278 l_user_id := 2;
279 elsif X_OWNER = 'SYSADMIN' THEN
280 l_user_id := 0;
281 end if ;
282
283 OPEN c_db_data_details;
284 FETCH c_db_data_details INTO l_db_luby_id, l_obj_verno;
285 IF c_db_data_details%NOTFOUND
286 THEN
287 CLOSE c_db_data_details;
288
289 IF x_metric_template_detail_id IS NULL THEN
290 OPEN c_get_mtdid;
291 FETCH c_get_mtdid INTO l_metric_template_detail_id;
292 CLOSE c_get_mtdid;
293 ELSE
294 l_metric_template_detail_id := x_metric_template_detail_id;
295 END IF ;
296
297 l_obj_verno := 1;
298
299 Insert_Row(
300 px_metric_template_detail_id => l_metric_template_detail_id,
301 p_last_update_date => sysdate,
302 p_last_updated_by => l_user_id,
303 p_creation_date => sysdate,
304 p_created_by => l_user_id,
305 p_last_update_login => 0,
306 px_object_version_number => l_obj_verno,
307 p_metric_tpl_header_id => x_METRIC_TPL_HEADER_ID,
308 p_metric_id => X_METRIC_ID,
309 p_enabled_flag => X_ENABLED_FLAG);
310
311 ELSE
312 CLOSE c_db_data_details;
313 if ( l_db_luby_id IN (1, 2, 0)
314 OR NVL(x_custom_mode,'PRESERVE') = 'FORCE') THEN
315 Update_Row(
316 p_metric_template_detail_id => x_metric_template_detail_id,
317 p_last_update_date => sysdate,
318 p_last_updated_by => l_user_id,
319 p_last_update_login => 0,
320 p_object_version_number => l_obj_verno + 1,
321 p_metric_tpl_header_id => x_metric_tpl_header_id,
322 p_metric_id => x_metric_id,
323 p_enabled_flag => x_enabled_flag);
324 END IF;
325 END IF;
326 END LOAD_ROW;
327
328 END Ams_Met_Tpl_Details_Pkg;