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