[Home] [Help]
PACKAGE BODY: APPS.AMS_IBA_PL_PG_PARAMS_PKG
Source
1 PACKAGE BODY AMS_IBA_PL_PG_PARAMS_PKG as
2 /* $Header: amstpgpb.pls 120.0 2005/06/01 01:36:26 appldev noship $ */
3 -- ===============================================================
4 -- Start of Comments
5 -- Package name
6 -- AMS_IBA_PL_PG_PARAMS_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_IBA_PL_PG_PARAMS_PKG';
18 G_FILE_NAME CONSTANT VARCHAR2(12) := 'amstpgpb.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 px_page_parameter_id IN OUT NOCOPY NUMBER,
39 p_page_id NUMBER,
40 p_site_ref_code VARCHAR2,
41 p_page_ref_code VARCHAR2,
42 p_parameter_id NUMBER,
43 p_parameter_ref_code VARCHAR2,
44 p_execution_order NUMBER,
45 p_created_by NUMBER,
46 p_creation_date DATE,
47 p_last_updated_by NUMBER,
48 p_last_update_date DATE,
49 p_last_update_login NUMBER,
50 px_object_version_number IN OUT NOCOPY NUMBER)
51
52 IS
53 x_rowid VARCHAR2(30);
54
55
56 BEGIN
57
58
59 px_object_version_number := 1;
60
61
62 INSERT INTO AMS_IBA_PL_PG_PARAMS(
63 page_parameter_id,
64 page_id,
65 site_ref_code,
66 page_ref_code,
67 parameter_id,
68 parameter_ref_code,
69 execution_order,
70 created_by,
71 creation_date,
72 last_updated_by,
73 last_update_date,
74 last_update_login,
75 object_version_number
76 ) VALUES (
77 DECODE( px_page_parameter_id, FND_API.g_miss_num, NULL, px_page_parameter_id),
78 DECODE( p_page_id, FND_API.g_miss_num, NULL, p_page_id),
79 DECODE( p_site_ref_code, FND_API.g_miss_char, NULL, p_site_ref_code),
80 DECODE( p_page_ref_code, FND_API.g_miss_char, NULL, p_page_ref_code),
81 DECODE( p_parameter_id, FND_API.g_miss_num, NULL, p_parameter_id),
82 DECODE( p_parameter_ref_code, FND_API.g_miss_char, NULL, p_parameter_ref_code),
83 DECODE( p_execution_order, FND_API.g_miss_num, NULL, p_execution_order),
84 DECODE( p_created_by, FND_API.g_miss_num, NULL, p_created_by),
85 DECODE( p_creation_date, FND_API.g_miss_date, NULL, p_creation_date),
86 DECODE( p_last_updated_by, FND_API.g_miss_num, NULL, p_last_updated_by),
87 DECODE( p_last_update_date, FND_API.g_miss_date, NULL, p_last_update_date),
88 DECODE( p_last_update_login, FND_API.g_miss_num, NULL, p_last_update_login),
89 DECODE( px_object_version_number, FND_API.g_miss_num, NULL, px_object_version_number));
90 END Insert_Row;
91
92
93 ----------------------------------------------------------
94 ---- MEDIA ----
95 ----------------------------------------------------------
96
97 -- ========================================================
98 --
99 -- NAME
100 -- createUpdateBody
101 --
102 -- PURPOSE
103 --
104 -- NOTES
105 --
106 -- HISTORY
107 --
108 -- ========================================================
109 PROCEDURE Update_Row(
110 p_page_parameter_id NUMBER,
111 p_page_id NUMBER,
112 p_site_ref_code VARCHAR2,
113 p_page_ref_code VARCHAR2,
114 p_parameter_id NUMBER,
115 p_parameter_ref_code VARCHAR2,
116 p_execution_order NUMBER,
117 p_created_by NUMBER,
118 p_creation_date DATE,
119 p_last_updated_by NUMBER,
120 p_last_update_date DATE,
121 p_last_update_login NUMBER,
122 p_object_version_number NUMBER)
123
124 IS
125 BEGIN
126 Update AMS_IBA_PL_PG_PARAMS
127 SET
128 page_id = DECODE( p_page_id, FND_API.g_miss_num, page_id, p_page_id),
129 site_ref_code = DECODE( p_site_ref_code, FND_API.g_miss_char, site_ref_code, p_site_ref_code),
130 page_ref_code = DECODE( p_page_ref_code, FND_API.g_miss_char, page_ref_code, p_page_ref_code),
131 parameter_id = DECODE( p_parameter_id, FND_API.g_miss_num, parameter_id, p_parameter_id),
132 parameter_ref_code = DECODE( p_parameter_ref_code, FND_API.g_miss_char, parameter_ref_code, p_parameter_ref_code),
133 execution_order = DECODE( p_execution_order, FND_API.g_miss_num, execution_order, p_execution_order),
134 last_updated_by = DECODE( p_last_updated_by, FND_API.g_miss_num, last_updated_by, p_last_updated_by),
135 last_update_date = DECODE( p_last_update_date, FND_API.g_miss_date, last_update_date, p_last_update_date),
136 last_update_login = DECODE( p_last_update_login, FND_API.g_miss_num, last_update_login, p_last_update_login),
137 object_version_number = DECODE( p_object_version_number, FND_API.g_miss_num, object_version_number, p_object_version_number)
138 WHERE PAGE_PARAMETER_ID = p_PAGE_PARAMETER_ID
139 AND object_version_number = p_object_version_number;
140
141 IF (SQL%NOTFOUND) THEN
142 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
143 END IF;
144 END Update_Row;
145
146
147 ----------------------------------------------------------
148 ---- MEDIA ----
149 ----------------------------------------------------------
150
151 -- ========================================================
152 --
153 -- NAME
154 -- createDeleteBody
155 --
156 -- PURPOSE
157 --
158 -- NOTES
159 --
160 -- HISTORY
161 --
162 -- ========================================================
163 PROCEDURE Delete_Row(
164 p_PAGE_PARAMETER_ID NUMBER)
165 IS
166 BEGIN
167 DELETE FROM AMS_IBA_PL_PG_PARAMS
168 WHERE PAGE_PARAMETER_ID = p_PAGE_PARAMETER_ID;
169 If (SQL%NOTFOUND) then
170 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
171 End If;
172 END Delete_Row ;
173
174
175
176 ----------------------------------------------------------
177 ---- MEDIA ----
178 ----------------------------------------------------------
179
180 -- ========================================================
181 --
182 -- NAME
183 -- createLockBody
184 --
185 -- PURPOSE
186 --
187 -- NOTES
188 --
189 -- HISTORY
190 --
191 -- ========================================================
192 PROCEDURE Lock_Row(
193 p_page_parameter_id NUMBER,
194 p_page_id NUMBER,
195 p_site_ref_code VARCHAR2,
196 p_page_ref_code VARCHAR2,
197 p_parameter_id NUMBER,
198 p_parameter_ref_code VARCHAR2,
199 p_execution_order NUMBER,
200 p_created_by NUMBER,
201 p_creation_date DATE,
202 p_last_updated_by NUMBER,
203 p_last_update_date DATE,
204 p_last_update_login NUMBER,
205 p_object_version_number NUMBER)
206
207 IS
208 CURSOR C IS
209 SELECT *
210 FROM AMS_IBA_PL_PG_PARAMS
211 WHERE PAGE_PARAMETER_ID = p_PAGE_PARAMETER_ID
212 FOR UPDATE of PAGE_PARAMETER_ID NOWAIT;
213 Recinfo C%ROWTYPE;
214 BEGIN
215 OPEN c;
216 FETCH c INTO Recinfo;
217 If (c%NOTFOUND) then
218 CLOSE c;
219 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
220 APP_EXCEPTION.RAISE_EXCEPTION;
221 END IF;
222 CLOSE C;
223 IF (
224 ( Recinfo.page_parameter_id = p_page_parameter_id)
225 AND ( ( Recinfo.page_id = p_page_id)
226 OR ( ( Recinfo.page_id IS NULL )
227 AND ( p_page_id IS NULL )))
228 AND ( ( Recinfo.site_ref_code = p_site_ref_code)
229 OR ( ( Recinfo.site_ref_code IS NULL )
230 AND ( p_site_ref_code IS NULL )))
231 AND ( ( Recinfo.page_ref_code = p_page_ref_code)
232 OR ( ( Recinfo.page_ref_code IS NULL )
233 AND ( p_page_ref_code IS NULL )))
234 AND ( ( Recinfo.parameter_id = p_parameter_id)
235 OR ( ( Recinfo.parameter_id IS NULL )
236 AND ( p_parameter_id IS NULL )))
237 AND ( ( Recinfo.parameter_ref_code = p_parameter_ref_code)
238 OR ( ( Recinfo.parameter_ref_code IS NULL )
239 AND ( p_parameter_ref_code IS NULL )))
240 AND ( ( Recinfo.execution_order = p_execution_order)
241 OR ( ( Recinfo.execution_order IS NULL )
242 AND ( p_execution_order IS NULL )))
243 AND ( ( Recinfo.created_by = p_created_by)
244 OR ( ( Recinfo.created_by IS NULL )
245 AND ( p_created_by IS NULL )))
246 AND ( ( Recinfo.creation_date = p_creation_date)
247 OR ( ( Recinfo.creation_date IS NULL )
248 AND ( p_creation_date IS NULL )))
249 AND ( ( Recinfo.last_updated_by = p_last_updated_by)
250 OR ( ( Recinfo.last_updated_by IS NULL )
251 AND ( p_last_updated_by IS NULL )))
252 AND ( ( Recinfo.last_update_date = p_last_update_date)
253 OR ( ( Recinfo.last_update_date IS NULL )
254 AND ( p_last_update_date IS NULL )))
255 AND ( ( Recinfo.last_update_login = p_last_update_login)
256 OR ( ( Recinfo.last_update_login IS NULL )
257 AND ( p_last_update_login IS NULL )))
258 AND ( ( Recinfo.object_version_number = p_object_version_number)
259 OR ( ( Recinfo.object_version_number IS NULL )
260 AND ( p_object_version_number IS NULL )))
261 ) THEN
262 RETURN;
263 ELSE
264 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_CHANGED');
265 APP_EXCEPTION.RAISE_EXCEPTION;
266 END IF;
267 END Lock_Row;
268
269 PROCEDURE load_row (
270 x_page_parameter_id IN NUMBER,
271 x_page_id IN NUMBER,
272 x_site_ref_code IN VARCHAR2,
273 x_page_ref_code IN VARCHAR2,
274 x_parameter_id IN NUMBER,
275 x_parameter_ref_code IN VARCHAR2,
276 x_execution_order IN NUMBER,
277 x_owner IN VARCHAR2,
278 x_custom_mode IN VARCHAR2
279 )
280 IS
281 l_user_id number := 1;
282 l_obj_verno number;
283 l_dummy_char varchar2(1);
284 l_row_id varchar2(100);
285 l_page_parameter_id number;
286 l_db_luby_id number;
287
288 /* cursor c_obj_verno is
289 select object_version_number
290 from ams_iba_pl_pg_params
291 where page_parameter_id = x_page_parameter_id;*/
292
293 cursor c_db_data_details is
294 select last_updated_by, nvl(object_version_number,1)
295 from ams_iba_pl_pg_params
296 where page_parameter_id = x_page_parameter_id;
297
298 cursor c_chk_page_parameter_exists is
299 select 'x'
300 from ams_iba_pl_pg_params
301 where page_parameter_id = x_page_parameter_id;
302
303 cursor c_get_page_parameter_id is
304 select ams_iba_pl_params_b_s.nextval
305 from dual;
306 BEGIN
307 if X_OWNER = 'SEED' then
308 l_user_id := 1;
309 elsif X_OWNER = 'ORACLE' then
310 l_user_id := 2;
311 elsif X_OWNER = 'SYSADMIN' then
312 l_user_id := 0;
313 end if;
314
315 open c_chk_page_parameter_exists;
316 fetch c_chk_page_parameter_exists into l_dummy_char;
317 if c_chk_page_parameter_exists%notfound THEN
318 if x_page_parameter_id is null then
319 open c_get_page_parameter_id;
320 fetch c_get_page_parameter_id into l_page_parameter_id;
321 close c_get_page_parameter_id;
322 else
323 l_page_parameter_id := x_page_parameter_id;
324 end if;
325 l_obj_verno := 1;
326
327 AMS_IBA_PL_PG_PARAMS_PKG.Insert_Row (
328 px_page_parameter_id => l_page_parameter_id,
329 p_page_id => x_page_id,
330 p_site_ref_code => x_site_ref_code,
331 p_page_ref_code => x_page_ref_code,
332 p_parameter_id => x_parameter_id,
333 p_parameter_ref_code => x_parameter_ref_code,
334 p_execution_order => x_execution_order,
335 p_created_by => l_user_id,
336 p_creation_date => SYSDATE,
337 p_last_updated_by => l_user_id,
338 p_last_update_date => SYSDATE,
339 p_last_update_login => 1,
340 px_object_version_number => l_obj_verno
341 );
342 else
343 open c_db_data_details;
344 fetch c_db_data_details into l_db_luby_id, l_obj_verno;
345 close c_db_data_details;
346
347 if (l_db_luby_id IN (0, 1, 2) or NVL(x_custom_mode, 'PRESERVE')='FORCE')
348 then
349
350 AMS_IBA_PL_PG_PARAMS_PKG.UPDATE_ROW (
351 p_page_parameter_id => x_page_parameter_id,
352 p_page_id => x_page_id,
353 p_site_ref_code => x_site_ref_code,
354 p_page_ref_code => x_page_ref_code,
355 p_parameter_id => x_parameter_id,
356 p_parameter_ref_code => x_parameter_ref_code,
357 p_execution_order => x_execution_order,
358 p_created_by => l_user_id,
359 p_creation_date => SYSDATE,
360 p_last_updated_by => l_user_id,
361 p_last_update_date => SYSDATE,
362 p_last_update_login => 1,
363 p_object_version_number => l_obj_verno
364 );
365 end if;
366 end if;
367 close c_chk_page_parameter_exists;
368 END load_row;
369
370 END AMS_IBA_PL_PG_PARAMS_PKG;