1 package body AMS_CUSTOM_SETUP_ATTR_PKG as
2 /* $Header: amslattb.pls 115.12 2004/04/08 22:51:09 asaha ship $ */
3 PROCEDURE INSERT_ROW (
4 X_ROWID in OUT NOCOPY VARCHAR2,
5 X_SETUP_ATTRIBUTE_ID in NUMBER,
6 X_OBJECT_VERSION_NUMBER in NUMBER,
7 X_CUSTOM_SETUP_ID in NUMBER,
8 X_DISPLAY_SEQUENCE_NO in NUMBER,
9 X_OBJECT_ATTRIBUTE in VARCHAR2,
10 X_ATTR_MANDATORY_FLAG in VARCHAR2,
11 X_ATTR_AVAILABLE_FLAG in VARCHAR2,
12 X_PARENT_FUNCTION_NAME in VARCHAR2,
13 X_FUNCTION_NAME in VARCHAR2,
14 X_PARENT_SETUP_ATTRIBUTE in VARCHAR2,
15 X_PARENT_DISPLAY_SEQUENCE in NUMBER,
16 X_SHOW_IN_REPORT in VARCHAR2,
17 X_SHOW_IN_CUE_CARD in VARCHAR2,
18 X_COPY_ALLOWED_FLAG in VARCHAR2,
19 X_RELATED_AK_ATTRIBUTE in VARCHAR2,
20 X_CREATION_DATE in DATE,
21 X_CREATED_BY in NUMBER,
22 X_LAST_UPDATE_DATE in DATE,
23 X_LAST_UPDATED_BY in NUMBER,
24 X_LAST_UPDATE_LOGIN in NUMBER,
25 X_ESSENTIAL_SEQ_NUM in NUMBER
26 )
27 IS
28 CURSOR c IS select ROWID FROM AMS_CUSTOM_SETUP_ATTR
29 WHERE SETUP_ATTRIBUTE_ID = X_SETUP_ATTRIBUTE_ID
30 ;
31 BEGIN
32 INSERT into AMS_CUSTOM_SETUP_ATTR (
33 SETUP_ATTRIBUTE_ID,
34 OBJECT_VERSION_NUMBER,
35 CUSTOM_SETUP_ID,
36 DISPLAY_SEQUENCE_NO,
37 OBJECT_ATTRIBUTE,
38 ATTR_MANDATORY_FLAG,
39 ATTR_AVAILABLE_FLAG,
40 PARENT_FUNCTION_NAME,
41 FUNCTION_NAME,
42 PARENT_SETUP_ATTRIBUTE,
43 PARENT_DISPLAY_SEQUENCE,
44 SHOW_IN_REPORT,
45 SHOW_IN_CUE_CARD,
46 COPY_ALLOWED_FLAG,
47 RELATED_AK_ATTRIBUTE,
48 CREATION_DATE,
49 CREATED_BY,
50 LAST_UPDATE_DATE,
51 LAST_UPDATED_BY,
52 LAST_UPDATE_LOGIN,
53 ESSENTIAL_SEQ_NUM
54 ) values (
55 X_SETUP_ATTRIBUTE_ID,
56 X_OBJECT_VERSION_NUMBER,
57 X_CUSTOM_SETUP_ID,
58 X_DISPLAY_SEQUENCE_NO,
59 X_OBJECT_ATTRIBUTE,
60 X_ATTR_MANDATORY_FLAG,
61 X_ATTR_AVAILABLE_FLAG,
62 X_PARENT_FUNCTION_NAME ,
63 X_FUNCTION_NAME,
64 X_PARENT_SETUP_ATTRIBUTE,
65 X_PARENT_DISPLAY_SEQUENCE,
66 X_SHOW_IN_REPORT ,
67 X_SHOW_IN_CUE_CARD ,
68 X_COPY_ALLOWED_FLAG,
69 X_RELATED_AK_ATTRIBUTE,
70 X_CREATION_DATE,
71 X_CREATED_BY,
72 X_LAST_UPDATE_DATE,
73 X_LAST_UPDATED_BY,
74 X_LAST_UPDATE_LOGIN,
75 X_ESSENTIAL_SEQ_NUM
76 );
77
78 OPEN c;
79 FETCH c into X_ROWID;
80 IF (c%NOTFOUND) THEN
81 CLOSE c;
82 RAISE no_data_found;
83 END IF;
84 CLOSE c;
85 END INSERT_ROW;
86
87 PROCEDURE UPDATE_ROW (
88 X_SETUP_ATTRIBUTE_ID in NUMBER,
89 X_OBJECT_VERSION_NUMBER in NUMBER,
90 X_CUSTOM_SETUP_ID in NUMBER,
91 X_DISPLAY_SEQUENCE_NO in NUMBER,
92 X_OBJECT_ATTRIBUTE in VARCHAR2,
93 X_ATTR_MANDATORY_FLAG in VARCHAR2,
94 X_ATTR_AVAILABLE_FLAG in VARCHAR2,
95 X_PARENT_FUNCTION_NAME in VARCHAR2,
96 X_FUNCTION_NAME in VARCHAR2,
97 X_PARENT_SETUP_ATTRIBUTE in VARCHAR2,
98 X_PARENT_DISPLAY_SEQUENCE in NUMBER,
99 X_SHOW_IN_REPORT in VARCHAR2,
100 X_SHOW_IN_CUE_CARD in VARCHAR2,
101 X_COPY_ALLOWED_FLAG in VARCHAR2,
102 X_RELATED_AK_ATTRIBUTE in VARCHAR2,
103 X_LAST_UPDATE_DATE in DATE,
104 X_LAST_UPDATED_BY in NUMBER,
105 X_LAST_UPDATE_LOGIN in NUMBER,
106 X_ESSENTIAL_SEQ_NUM in NUMBER
107 )
108 IS
109 BEGIN
110 UPDATE AMS_CUSTOM_SETUP_ATTR SET
111 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
112 DISPLAY_SEQUENCE_NO = X_DISPLAY_SEQUENCE_NO,
113 ATTR_MANDATORY_FLAG = X_ATTR_MANDATORY_FLAG,
114 ATTR_AVAILABLE_FLAG = X_ATTR_AVAILABLE_FLAG,
115 PARENT_FUNCTION_NAME = X_PARENT_FUNCTION_NAME,
116 FUNCTION_NAME = X_FUNCTION_NAME,
117 PARENT_SETUP_ATTRIBUTE = X_PARENT_SETUP_ATTRIBUTE,
118 PARENT_DISPLAY_SEQUENCE = X_PARENT_DISPLAY_SEQUENCE,
119 SHOW_IN_REPORT = X_SHOW_IN_REPORT,
120 SHOW_IN_CUE_CARD = X_SHOW_IN_CUE_CARD,
121 COPY_ALLOWED_FLAG = X_COPY_ALLOWED_FLAG,
122 RELATED_AK_ATTRIBUTE = X_RELATED_AK_ATTRIBUTE,
123 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
124 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
125 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
126 ESSENTIAL_SEQ_NUM = X_ESSENTIAL_SEQ_NUM
127 WHERE OBJECT_ATTRIBUTE = X_OBJECT_ATTRIBUTE
128 AND CUSTOM_SETUP_ID = X_CUSTOM_SETUP_ID;
129
130 IF (sql%NOTFOUND) THEN
131 RAISE no_data_found;
132 END IF;
133 END UPDATE_ROW;
134
135 PROCEDURE DELETE_ROW (
136 X_SETUP_ATTRIBUTE_ID in NUMBER
137 )
138 IS
139 BEGIN
140 DELETE FROM AMS_CUSTOM_SETUP_ATTR
141 WHERE SETUP_ATTRIBUTE_ID = X_SETUP_ATTRIBUTE_ID;
142
143 IF (sql%NOTFOUND) THEN
144 RAISE no_data_found;
145 END IF;
146 END DELETE_ROW;
147
148 /* This procedure is used to load the data from flat file to customer's database.
149 If there is no row existing for the data from flat file then create the data.
150 else
151 1) modify the whole data when data in db is not modified by customer which can be found
152 by comparing last updated by value to be
153 SEED/DATAMERGE(1), or
154 INITIAL SETUP/ORACLE (2), or
155 SYSTEM ADMINISTRATOR (0).or
156 2) modify the whole data when custom_mode is 'FORCE'
157 3) if the data in db is modified by customer, which can be found by
158 by comparing last updated by value to be not of 0,1,2, then
159 in that case modify only the user unexposed data with last updated by as 3 to
160 distinguish that data is updated by patch.
161 */
162
163 PROCEDURE LOAD_ROW(
164 X_SETUP_ATTRIBUTE_ID in NUMBER,
165 X_CUSTOM_SETUP_ID in NUMBER,
166 X_DISPLAY_SEQUENCE_NO in NUMBER,
167 X_OBJECT_ATTRIBUTE in VARCHAR2,
168 X_ATTR_MANDATORY_FLAG in VARCHAR2,
169 X_ATTR_AVAILABLE_FLAG in VARCHAR2,
170 X_PARENT_FUNCTION_NAME in VARCHAR2,
171 X_FUNCTION_NAME in VARCHAR2,
172 X_PARENT_SETUP_ATTRIBUTE in VARCHAR2,
173 X_PARENT_DISPLAY_SEQUENCE in NUMBER,
174 X_SHOW_IN_REPORT in VARCHAR2,
175 X_SHOW_IN_CUE_CARD in VARCHAR2,
176 X_COPY_ALLOWED_FLAG in VARCHAR2,
177 X_RELATED_AK_ATTRIBUTE in VARCHAR2,
178 X_ESSENTIAL_SEQ_NUM in NUMBER,
179 X_OWNER in VARCHAR2,
180 x_custom_mode IN VARCHAR2,
181 X_LAST_UPDATE_DATE in DATE
182 )
183 IS
184
185 l_user_id number := 1;
186 -- user id to be used in case of exceptions to update the customer modified unexposed data.
187 l_excp_user_id number := 3 ;
188
189 l_obj_verno NUMBER;
190 l_dummy_number number;
191 l_row_id VARCHAR2(100);
192 l_attr_id NUMBER;
193 l_count NUMBER;
194 l_db_luby_id NUMBER;
195
196 cursor c_db_data_details is
197 select last_updated_by, nvl(object_version_number,1)
198 FROM AMS_CUSTOM_SETUP_ATTR
199 WHERE OBJECT_ATTRIBUTE = X_OBJECT_ATTRIBUTE
200 AND CUSTOM_SETUP_ID = X_CUSTOM_SETUP_ID;
201
202 CURSOR c_chk_code_exists IS
203 select 1
204 FROM AMS_CUSTOM_SETUP_ATTR
205 WHERE OBJECT_ATTRIBUTE = X_OBJECT_ATTRIBUTE
206 AND CUSTOM_SETUP_ID = X_CUSTOM_SETUP_ID;
207
208 CURSOR c_get_attr_id IS
209 select AMS_CUSTOM_SETUP_ATTR_S.NEXTVAL
210 FROM DUAL;
211
212 CURSOR c_check_attr_id(attr_id IN NUMBER) IS
213 SELECT COUNT(*)
214 FROM AMS_CUSTOM_SETUP_ATTR
215 WHERE setup_attribute_id = attr_id;
216
217 BEGIN
218
219 -- set the last_updated_by to be used while updating the data in customer data.
220 if X_OWNER = 'SEED' then
221 l_user_id := 1;
222 elsif X_OWNER = 'ORACLE' THEN
223 l_user_id := 2;
224 elsif X_OWNER = 'SYSADMIN' THEN
225 l_user_id := 0;
226 end if ;
227
228 OPEN c_chk_code_exists;
229 FETCH c_chk_code_exists into l_dummy_number;
230 IF c_chk_code_exists%NOTFOUND
231 THEN
232 -- data does not exist in customer, and hence create the data.
233 CLOSE c_chk_code_exists;
234 -- get unused sequence number
235 LOOP
236 OPEN c_get_attr_id;
237 FETCH c_get_attr_id into l_attr_id;
238 CLOSE c_get_attr_id;
239
240 OPEN c_check_attr_id(l_attr_id);
241 FETCH c_check_attr_id INTO l_count;
242 CLOSE c_check_attr_id;
243
244 EXIT WHEN l_count = 0;
245 END LOOP;
246
247 l_obj_verno := 1;
248 AMS_CUSTOM_SETUP_ATTR_PKG.INSERT_ROW (
249 X_ROWID => l_row_id,
250 X_SETUP_ATTRIBUTE_ID => l_attr_id,
251 X_OBJECT_VERSION_NUMBER => l_obj_verno,
252 X_CUSTOM_SETUP_ID => X_CUSTOM_SETUP_ID,
253 X_DISPLAY_SEQUENCE_NO => X_DISPLAY_SEQUENCE_NO,
254 X_OBJECT_ATTRIBUTE => X_OBJECT_ATTRIBUTE,
255 X_ATTR_MANDATORY_FLAG => X_ATTR_MANDATORY_FLAG,
256 X_ATTR_AVAILABLE_FLAG => X_ATTR_AVAILABLE_FLAG,
257 X_PARENT_FUNCTION_NAME => X_PARENT_FUNCTION_NAME,
258 X_FUNCTION_NAME => X_FUNCTION_NAME,
259 X_PARENT_SETUP_ATTRIBUTE => X_PARENT_SETUP_ATTRIBUTE,
260 X_PARENT_DISPLAY_SEQUENCE => X_PARENT_DISPLAY_SEQUENCE,
261 X_SHOW_IN_REPORT => X_SHOW_IN_REPORT,
262 X_SHOW_IN_CUE_CARD => X_SHOW_IN_CUE_CARD,
263 X_COPY_ALLOWED_FLAG => X_COPY_ALLOWED_FLAG,
264 X_RELATED_AK_ATTRIBUTE => X_RELATED_AK_ATTRIBUTE,
265 X_CREATION_DATE => X_LAST_UPDATE_DATE,
266 X_CREATED_BY => l_user_id,
267 X_LAST_UPDATE_DATE => X_LAST_UPDATE_DATE,
268 X_LAST_UPDATED_BY => l_user_id,
269 X_LAST_UPDATE_LOGIN => 0,
270 X_ESSENTIAL_SEQ_NUM => X_ESSENTIAL_SEQ_NUM
271 );
272 ELSE
273 -- Update the data as per above rules.
274 CLOSE c_chk_code_exists;
275 open c_db_data_details;
276 fetch c_db_data_details into l_db_luby_id, l_obj_verno;
277 close c_db_data_details;
278 if ( l_db_luby_id IN (1, 2, 0)
279 OR NVL(x_custom_mode,'PRESERVE') = 'FORCE') THEN
280 AMS_CUSTOM_SETUP_ATTR_PKG.UPDATE_ROW (
281 X_SETUP_ATTRIBUTE_ID => X_SETUP_ATTRIBUTE_ID,
282 X_OBJECT_VERSION_NUMBER => l_obj_verno + 1,
283 X_CUSTOM_SETUP_ID => X_CUSTOM_SETUP_ID,
284 X_DISPLAY_SEQUENCE_NO => X_DISPLAY_SEQUENCE_NO,
285 X_OBJECT_ATTRIBUTE => X_OBJECT_ATTRIBUTE,
286 X_ATTR_MANDATORY_FLAG => X_ATTR_MANDATORY_FLAG,
287 X_ATTR_AVAILABLE_FLAG => X_ATTR_AVAILABLE_FLAG,
288 X_PARENT_FUNCTION_NAME => X_PARENT_FUNCTION_NAME,
289 X_FUNCTION_NAME => X_FUNCTION_NAME,
290 X_PARENT_SETUP_ATTRIBUTE => X_PARENT_SETUP_ATTRIBUTE,
291 X_PARENT_DISPLAY_SEQUENCE => X_PARENT_DISPLAY_SEQUENCE,
292 X_SHOW_IN_REPORT => X_SHOW_IN_REPORT,
293 X_SHOW_IN_CUE_CARD => X_SHOW_IN_CUE_CARD,
294 X_COPY_ALLOWED_FLAG => X_COPY_ALLOWED_FLAG,
295 X_RELATED_AK_ATTRIBUTE => X_RELATED_AK_ATTRIBUTE,
296 X_LAST_UPDATE_DATE => X_LAST_UPDATE_DATE,
297 X_LAST_UPDATED_BY => l_user_id,
298 X_LAST_UPDATE_LOGIN => 0,
299 X_ESSENTIAL_SEQ_NUM => X_ESSENTIAL_SEQ_NUM
300 );
301 ELSE
302 UPDATE AMS_CUSTOM_SETUP_ATTR SET
303 OBJECT_VERSION_NUMBER = l_obj_verno + 1,
304 OBJECT_ATTRIBUTE = X_OBJECT_ATTRIBUTE,
305 PARENT_FUNCTION_NAME = X_PARENT_FUNCTION_NAME,
306 FUNCTION_NAME = X_FUNCTION_NAME,
307 PARENT_SETUP_ATTRIBUTE = X_PARENT_SETUP_ATTRIBUTE,
308 PARENT_DISPLAY_SEQUENCE = X_PARENT_DISPLAY_SEQUENCE,
309 SHOW_IN_CUE_CARD = X_SHOW_IN_CUE_CARD,
310 COPY_ALLOWED_FLAG = X_COPY_ALLOWED_FLAG,
311 RELATED_AK_ATTRIBUTE = X_RELATED_AK_ATTRIBUTE,
312 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
313 LAST_UPDATED_BY = l_excp_user_id,
314 LAST_UPDATE_LOGIN = 0,
315 ESSENTIAL_SEQ_NUM = X_ESSENTIAL_SEQ_NUM
316 WHERE OBJECT_ATTRIBUTE = X_OBJECT_ATTRIBUTE
317 AND CUSTOM_SETUP_ID = X_CUSTOM_SETUP_ID;
318 end if;
319 end if;
320 END LOAD_ROW;
321
322 END AMS_CUSTOM_SETUP_ATTR_PKG;
[Home] [Help]