1 package body AMS_LIST_RULES_ALL_PKG as
2 /* $Header: amstlsrb.pls 120.1 2005/06/27 05:40:31 appldev ship $ */
3 procedure INSERT_ROW (
4 X_ROWID in OUT NOCOPY VARCHAR2,
5 X_LIST_RULE_ID in NUMBER,
6 X_LIST_SOURCE_TYPE in VARCHAR2,
7 X_ENABLED_FLAG in VARCHAR2,
8 X_SEEDED_FLAG in VARCHAR2,
9 X_OBJECT_VERSION_NUMBER in NUMBER,
10 X_WEIGHTAGE_FOR_DEDUPE in NUMBER,
11 X_ACTIVE_FROM_DATE in DATE,
12 X_ACTIVE_TO_DATE in DATE,
13 X_LIST_RULE_TYPE in VARCHAR2,
14 X_LIST_RULE_NAME in VARCHAR2,
15 X_DESCRIPTION in VARCHAR2,
16 X_CREATION_DATE in DATE,
17 X_CREATED_BY in NUMBER,
18 X_LAST_UPDATE_DATE in DATE,
19 X_LAST_UPDATED_BY in NUMBER,
20 X_LAST_UPDATE_LOGIN in NUMBER
21 ) is
22 cursor C is select ROWID from AMS_LIST_RULES_ALL
23 where LIST_RULE_ID = X_LIST_RULE_ID
24 ;
25 begin
26 insert into AMS_LIST_RULES_ALL (
27 LIST_SOURCE_TYPE,
28 ENABLED_FLAG,
29 SEEDED_FLAG,
30 LIST_RULE_ID,
31 OBJECT_VERSION_NUMBER,
32 WEIGHTAGE_FOR_DEDUPE,
33 ACTIVE_FROM_DATE,
34 ACTIVE_TO_DATE,
35 LIST_RULE_TYPE,
36 CREATION_DATE,
37 CREATED_BY,
38 LAST_UPDATE_DATE,
39 LAST_UPDATED_BY,
40 LAST_UPDATE_LOGIN,
41 LIST_RULE_NAME
42 ) values (
43 X_LIST_SOURCE_TYPE,
44 X_ENABLED_FLAG,
45 X_SEEDED_FLAG,
46 X_LIST_RULE_ID,
47 X_OBJECT_VERSION_NUMBER,
48 X_WEIGHTAGE_FOR_DEDUPE,
49 X_ACTIVE_FROM_DATE,
50 X_ACTIVE_TO_DATE,
51 X_LIST_RULE_TYPE,
52 X_CREATION_DATE,
53 X_CREATED_BY,
54 X_LAST_UPDATE_DATE,
55 X_LAST_UPDATED_BY,
56 X_LAST_UPDATE_LOGIN,
57 X_LIST_RULE_NAME
58 );
59
60 insert into AMS_LIST_RULES_ALL_TL (
61 LIST_RULE_ID,
62 LAST_UPDATE_DATE,
63 LAST_UPDATED_BY,
64 CREATION_DATE,
65 CREATED_BY,
66 LAST_UPDATE_LOGIN,
67 LIST_RULE_NAME,
68 DESCRIPTION,
69 LANGUAGE,
70 SOURCE_LANG
71 ) select
72 X_LIST_RULE_ID,
73 X_LAST_UPDATE_DATE,
74 X_LAST_UPDATED_BY, -- igoswami :: manually changed here
75 X_CREATION_DATE,
76 X_CREATED_BY,
77 X_LAST_UPDATE_LOGIN,
78 X_LIST_RULE_NAME,
79 X_DESCRIPTION,
80 L.LANGUAGE_CODE,
81 userenv('LANG')
82 from FND_LANGUAGES L
83 where L.INSTALLED_FLAG in ('I', 'B')
84 and not exists
85 (select NULL
86 from AMS_LIST_RULES_ALL_TL T
87 where T.LIST_RULE_ID = X_LIST_RULE_ID
88 and T.LANGUAGE = L.LANGUAGE_CODE);
89
90 open c;
91 fetch c into X_ROWID;
92 if (c%notfound) then
93 close c;
94 raise no_data_found;
95 end if;
96 close c;
97
98 end INSERT_ROW;
99
100 procedure LOCK_ROW (
101 X_LIST_RULE_ID in NUMBER,
102 X_LIST_SOURCE_TYPE in VARCHAR2,
103 X_ENABLED_FLAG in VARCHAR2,
104 X_SEEDED_FLAG in VARCHAR2,
105 X_OBJECT_VERSION_NUMBER in NUMBER,
106 X_WEIGHTAGE_FOR_DEDUPE in NUMBER,
107 X_ACTIVE_FROM_DATE in DATE,
108 X_ACTIVE_TO_DATE in DATE,
109 X_LIST_RULE_TYPE in VARCHAR2,
110 X_LIST_RULE_NAME in VARCHAR2,
111 X_DESCRIPTION in VARCHAR2
112 ) is
113 cursor c is select
114 LIST_SOURCE_TYPE,
115 ENABLED_FLAG,
116 SEEDED_FLAG,
117 OBJECT_VERSION_NUMBER,
118 WEIGHTAGE_FOR_DEDUPE,
119 ACTIVE_FROM_DATE,
120 ACTIVE_TO_DATE,
121 LIST_RULE_TYPE
122 from AMS_LIST_RULES_ALL
123 where LIST_RULE_ID = X_LIST_RULE_ID
124 for update of LIST_RULE_ID nowait;
125 recinfo c%rowtype;
126
127 cursor c1 is select
128 LIST_RULE_NAME,
129 DESCRIPTION,
130 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
131 from AMS_LIST_RULES_ALL_TL
132 where LIST_RULE_ID = X_LIST_RULE_ID
133 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
134 for update of LIST_RULE_ID nowait;
135 begin
136 open c;
137 fetch c into recinfo;
138 if (c%notfound) then
139 close c;
140 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
141 app_exception.raise_exception;
142 end if;
143 close c;
144 if ( ((recinfo.LIST_SOURCE_TYPE = X_LIST_SOURCE_TYPE)
145 OR ((recinfo.LIST_SOURCE_TYPE is null) AND (X_LIST_SOURCE_TYPE is null)))
146 AND ((recinfo.ENABLED_FLAG = X_ENABLED_FLAG)
147 OR ((recinfo.ENABLED_FLAG is null) AND (X_ENABLED_FLAG is null)))
148 AND ((recinfo.SEEDED_FLAG = X_SEEDED_FLAG)
149 OR ((recinfo.SEEDED_FLAG is null) AND (X_SEEDED_FLAG is null)))
150 AND ((recinfo.OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER)
151 OR ((recinfo.OBJECT_VERSION_NUMBER is null) AND (X_OBJECT_VERSION_NUMBER is null)))
152 AND ((recinfo.WEIGHTAGE_FOR_DEDUPE = X_WEIGHTAGE_FOR_DEDUPE)
153 OR ((recinfo.WEIGHTAGE_FOR_DEDUPE is null) AND (X_WEIGHTAGE_FOR_DEDUPE is null)))
154 AND (recinfo.ACTIVE_FROM_DATE = X_ACTIVE_FROM_DATE)
155 AND ((recinfo.ACTIVE_TO_DATE = X_ACTIVE_TO_DATE)
156 OR ((recinfo.ACTIVE_TO_DATE is null) AND (X_ACTIVE_TO_DATE is null)))
157 AND ((recinfo.LIST_RULE_TYPE = X_LIST_RULE_TYPE)
158 OR ((recinfo.LIST_RULE_TYPE is null) AND (X_LIST_RULE_TYPE is null)))
159 ) then
160 null;
161 else
162 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
163 app_exception.raise_exception;
164 end if;
165
166 for tlinfo in c1 loop
167 if (tlinfo.BASELANG = 'Y') then
168 if ( (tlinfo.LIST_RULE_NAME = X_LIST_RULE_NAME)
169 AND ((tlinfo.DESCRIPTION = X_DESCRIPTION)
170 OR ((tlinfo.DESCRIPTION is null) AND (X_DESCRIPTION is null)))
171 ) then
172 null;
173 else
174 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
175 app_exception.raise_exception;
176 end if;
177 end if;
178 end loop;
179 return;
180 end LOCK_ROW;
181
182 procedure UPDATE_ROW (
183 X_LIST_RULE_ID in NUMBER,
184 X_LIST_SOURCE_TYPE in VARCHAR2,
185 X_ENABLED_FLAG in VARCHAR2,
186 X_SEEDED_FLAG in VARCHAR2,
187 X_OBJECT_VERSION_NUMBER in NUMBER,
188 X_WEIGHTAGE_FOR_DEDUPE in NUMBER,
189 X_ACTIVE_FROM_DATE in DATE,
190 X_ACTIVE_TO_DATE in DATE,
191 X_LIST_RULE_TYPE in VARCHAR2,
192 X_LIST_RULE_NAME in VARCHAR2,
193 X_DESCRIPTION in VARCHAR2,
194 X_LAST_UPDATE_DATE in DATE,
195 X_LAST_UPDATED_BY in NUMBER,
196 X_LAST_UPDATE_LOGIN in NUMBER
197 ) is
198 begin
199 update AMS_LIST_RULES_ALL set
200 LIST_SOURCE_TYPE = X_LIST_SOURCE_TYPE,
201 ENABLED_FLAG = X_ENABLED_FLAG,
202 SEEDED_FLAG = X_SEEDED_FLAG,
203 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
204 WEIGHTAGE_FOR_DEDUPE = X_WEIGHTAGE_FOR_DEDUPE,
205 ACTIVE_FROM_DATE = X_ACTIVE_FROM_DATE,
206 ACTIVE_TO_DATE = X_ACTIVE_TO_DATE,
207 LIST_RULE_TYPE = X_LIST_RULE_TYPE,
208 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
209 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
210 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
211 where LIST_RULE_ID = X_LIST_RULE_ID;
212
213 if (sql%notfound) then
214 raise no_data_found;
215 end if;
216
217 update AMS_LIST_RULES_ALL_TL set
218 LIST_RULE_NAME = X_LIST_RULE_NAME,
219 DESCRIPTION = X_DESCRIPTION,
220 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
221 LAST_UPDATED_BY = X_LAST_UPDATED_BY, -- igoswami :: manually changed here
222 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
223 SOURCE_LANG = userenv('LANG')
224 where LIST_RULE_ID = X_LIST_RULE_ID
225 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
226
227 if (sql%notfound) then
228 raise no_data_found;
229 end if;
230 end UPDATE_ROW;
231
232 procedure DELETE_ROW (
233 X_LIST_RULE_ID in NUMBER
234 ) is
235 begin
236 delete from AMS_LIST_RULES_ALL_TL
237 where LIST_RULE_ID = X_LIST_RULE_ID;
238
239 if (sql%notfound) then
240 raise no_data_found;
241 end if;
242
243 delete from AMS_LIST_RULES_ALL
244 where LIST_RULE_ID = X_LIST_RULE_ID;
245
246 if (sql%notfound) then
247 raise no_data_found;
248 end if;
249 end DELETE_ROW;
250
251 procedure ADD_LANGUAGE
252 is
253 begin
254 delete from AMS_LIST_RULES_ALL_TL T
255 where not exists
256 (select NULL
257 from AMS_LIST_RULES_ALL B
258 where B.LIST_RULE_ID = T.LIST_RULE_ID
259 );
260
261 update AMS_LIST_RULES_ALL_TL T set (
262 LIST_RULE_NAME,
263 DESCRIPTION
264 ) = (select
265 B.LIST_RULE_NAME,
266 B.DESCRIPTION
267 from AMS_LIST_RULES_ALL_TL B
268 where B.LIST_RULE_ID = T.LIST_RULE_ID
269 and B.LANGUAGE = T.SOURCE_LANG)
270 where (
271 T.LIST_RULE_ID,
272 T.LANGUAGE
273 ) in (select
274 SUBT.LIST_RULE_ID,
275 SUBT.LANGUAGE
276 from AMS_LIST_RULES_ALL_TL SUBB, AMS_LIST_RULES_ALL_TL SUBT
277 where SUBB.LIST_RULE_ID = SUBT.LIST_RULE_ID
278 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
279 and (SUBB.LIST_RULE_NAME <> SUBT.LIST_RULE_NAME
280 or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
281 or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
282 or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
283 ));
284
285 insert into AMS_LIST_RULES_ALL_TL (
286 LIST_RULE_ID,
287 LAST_UPDATE_DATE,
288 LAST_UPDATED_BY,
289 CREATION_DATE,
290 CREATED_BY,
291 LAST_UPDATE_LOGIN,
292 LIST_RULE_NAME,
293 DESCRIPTION,
294 LANGUAGE,
295 SOURCE_LANG
296 ) select /*+ ORDERED */
297 B.LIST_RULE_ID,
298 B.LAST_UPDATE_DATE,
299 B.LAST_UPDATED_BY,
300 B.CREATION_DATE,
301 B.CREATED_BY,
302 B.LAST_UPDATE_LOGIN,
303 B.LIST_RULE_NAME,
304 B.DESCRIPTION,
305 L.LANGUAGE_CODE,
306 B.SOURCE_LANG
307 from AMS_LIST_RULES_ALL_TL B, FND_LANGUAGES L
308 where L.INSTALLED_FLAG in ('I', 'B')
309 and B.LANGUAGE = userenv('LANG')
310 and not exists
311 (select NULL
312 from AMS_LIST_RULES_ALL_TL T
313 where T.LIST_RULE_ID = B.LIST_RULE_ID
314 and T.LANGUAGE = L.LANGUAGE_CODE);
315 end ADD_LANGUAGE;
316
317 end AMS_LIST_RULES_ALL_PKG;
[Home] [Help]