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