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