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