[Home] [Help]
PACKAGE BODY: APPS.ZX_EVENT_CLASSES_PKG
Source
1 package body ZX_EVENT_CLASSES_PKG as
2 /* $Header: zxieventb.pls 120.10 2006/10/27 17:15:55 appradha ship $ */
3
4 g_current_runtime_level CONSTANT NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
5 g_level_statement CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
6 g_level_procedure CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
7 g_level_event CONSTANT NUMBER := FND_LOG.LEVEL_EVENT;
8 g_level_unexpected CONSTANT NUMBER := FND_LOG.LEVEL_UNEXPECTED;
9
10 PROCEDURE insert_row (
11 x_tax_event_class_code IN VARCHAR2,
12 x_normal_sign_flag IN VARCHAR2,
13 x_intrcmp_tx_cls_code IN VARCHAR2,
14 x_creation_date IN DATE,
15 x_created_by IN NUMBER,
16 x_last_update_date IN DATE,
17 x_last_updated_by IN NUMBER,
18 x_last_update_login IN NUMBER,
19 x_tax_event_class_name IN VARCHAR2
20 ) is
21
22 CURSOR C IS SELECT rowid FROM zx_event_classes_b
23 WHERE tax_event_class_code = x_tax_event_class_code;
24
25 clsinfo c%rowtype;
26 BEGIN
27 IF (g_level_procedure >= g_current_runtime_level ) THEN
28 FND_LOG.STRING(g_level_procedure,
29 'ZX_EVENT_CLASSES_PKG.',
30 'Insert_Row (+)');
31 END IF;
32
33 IF (g_level_procedure >= g_current_runtime_level ) THEN
34 FND_LOG.STRING(g_level_procedure,
35 'ZX_EVENT_CLASSES_PKG.Insert_Row',
36 'Insert into ZX_EVENT_CLASSES_B (+)');
37 END IF;
38
39 INSERT INTO zx_event_classes_b (
40 tax_event_class_code ,
41 normal_sign_flag ,
42 asc_intrcmp_tx_evnt_cls_code,
43 creation_date ,
44 created_by,
45 last_update_date,
46 last_updated_by,
47 last_update_login )
48 VALUES (
49 x_tax_event_class_code ,
50 x_normal_sign_flag ,
51 x_intrcmp_tx_cls_code,
52 x_creation_date,
53 x_created_by,
54 x_last_update_date,
55 x_last_updated_by,
56 x_last_update_login
57 );
58
59 INSERT INTO zx_event_classes_tl (
60 tax_event_class_code,
61 tax_event_class_name,
62 creation_date,
63 created_by,
64 last_update_date,
65 last_updated_by,
66 last_update_login,
67 language,
68 source_lang
69 )
70 SELECT
71 x_tax_event_class_code,
72 x_tax_event_class_name,
73 x_creation_date,
74 x_created_by,
75 x_last_update_date,
76 x_last_updated_by,
77 x_last_update_login,
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 zx_event_classes_tl cls
85 WHERE cls.tax_event_class_code = x_tax_event_class_code
86 AND cls.language = l.language_code);
87
88 OPEN c;
89 FETCH c INTO clsinfo;
90 IF (c%NOTFOUND) THEN
91 CLOSE c;
92 RAISE NO_DATA_FOUND;
93 END IF;
94 CLOSE c;
95
96 IF (g_level_procedure >= g_current_runtime_level ) THEN
97 FND_LOG.STRING(g_level_procedure,
98 'ZX_EVENT_CLASSES_PKG.Insert_Row',
99 'Insert into ZX_EVENT_CLASSES_B (-)');
100 END IF;
101 END insert_row;
102
103
104 PROCEDURE translate_row
105 ( x_owner IN VARCHAR2,
106 x_tax_event_class_code IN VARCHAR2,
107 x_tax_event_class_name IN VARCHAR2,
108 x_last_update_date IN VARCHAR2,
109 x_custom_mode IN VARCHAR2) IS
110
111 f_luby number;
112 f_ludate date;
113 db_luby number;
114 db_ludate date;
115
116 BEGIN
117 IF (g_level_procedure >= g_current_runtime_level ) THEN
118 FND_LOG.STRING(g_level_procedure,
119 'ZX_EVENT_CLASSES_PKG',
120 'Translate_row (+)');
121 END IF;
122
123 --Translate owner to file_last_updated_by
124 f_luby:= fnd_load_util.owner_id(x_owner);
125 --Translate char last_update_date to date
126 f_ludate := nvl(to_date(x_last_update_date,'DD/MM/YYYY'),sysdate);
127
128 BEGIN
129 SELECT decode (last_updated_by,1,1,0), last_update_date
130 INTO db_luby, db_ludate
131 FROM zx_event_classes_tl
132 WHERE tax_event_class_code = x_tax_event_class_code
133 AND language = userenv('LANG');
134
135 IF (fnd_load_util.upload_test(f_luby, f_ludate, db_luby,
136 db_ludate, x_custom_mode)) THEN
137 UPDATE zx_event_classes_tl SET
138 tax_event_class_name = x_tax_event_class_name,
139 last_update_date = f_ludate,
140 last_updated_by = f_luby,
141 last_update_login = 0,
142 source_lang = userenv ('LANG')
143 WHERE userenv('LANG') IN (language,source_lang)
144 AND tax_event_class_code = x_tax_event_class_code;
145 END IF;
146
147 EXCEPTION
148 WHEN NO_DATA_FOUND THEN
149 NULL;
150 END;
151
152 IF (g_level_procedure >= g_current_runtime_level ) THEN
153 FND_LOG.STRING(g_level_procedure,
154 'ZX_EVENT_CLASSES_PKG.Translate_row',
155 'Insert into ZX_EVENT_CLASSES_TL (-)');
156 END IF;
157 END translate_row;
158
159
160 PROCEDURE load_row
161 (x_owner in VARCHAR2,
162 x_tax_event_class_code in VARCHAR2,
163 x_normal_sign_flag in VARCHAR2,
164 x_intrcmp_tx_cls_code in VARCHAR2,
165 x_last_update_date in VARCHAR2,
166 x_tax_event_class_name in VARCHAR2,
167 x_custom_mode in VARCHAR2) is
168
169 row_id varchar2(64);
170 f_luby number;
171 f_ludate date;
172 db_luby number;
173 db_ludate date;
174
175 BEGIN
176 IF (g_level_procedure >= g_current_runtime_level ) THEN
177 FND_LOG.STRING(g_level_procedure,
178 'ZX_EVENT_CLASSES_PKG',
179 'Load_Row (+)');
180 END IF;
181 --Translate owner to file_last_updated_by
182 f_luby:= fnd_load_util.owner_id(x_owner);
183 --Translate char last_update_date to date
184 f_ludate := nvl(to_date(x_last_update_date,'DD/MM/YYYY'),sysdate);
185
186 BEGIN
187 SELECT decode (LAST_UPDATED_BY,1,1,0), LAST_UPDATE_DATE
188 INTO db_luby, db_ludate
189 FROM zx_event_classes_b
190 WHERE tax_event_class_code = x_tax_event_class_code;
191
192 -- Record should be updated only if :
193 -- a. file owner is CUSTOM and db owner is SEED
194 -- b. owners are the same, and file_date > db_date
195 IF (fnd_load_util.upload_test(f_luby, f_ludate, db_luby,
196 db_ludate, x_custom_mode)) THEN
197 UPDATE zx_event_classes_b
198 SET normal_sign_flag = x_normal_sign_flag ,
199 asc_intrcmp_tx_evnt_cls_code = x_intrcmp_tx_cls_code,
200 last_update_date = f_ludate,
201 last_updated_by = f_luby,
202 last_update_login = 0
203 where TAX_EVENT_CLASS_CODE = x_tax_event_class_code;
204 END IF;
205 EXCEPTION
206 WHEN NO_DATA_FOUND THEN
207 insert_row (
208 x_tax_event_class_code => x_tax_event_class_code ,
209 x_normal_sign_flag => x_normal_sign_flag ,
210 x_intrcmp_tx_cls_code => x_intrcmp_tx_cls_code,
211 x_creation_date => f_ludate,
212 x_created_by => f_luby,
213 x_last_update_date => f_ludate,
214 x_last_updated_by => f_luby,
215 x_last_update_login => 0,
216 x_tax_event_class_name => x_tax_event_class_name);
217 END;
218 IF (g_level_procedure >= g_current_runtime_level ) THEN
219 FND_LOG.STRING(g_level_procedure,
220 'ZX_EVENT_CLASSES_PKG.Load_Row',
221 'Load_row (-)');
222 END IF;
223 END load_row;
224
225
226 PROCEDURE add_language
227 IS
228 BEGIN
229 DELETE FROM zx_event_classes_tl T
230 WHERE NOT EXISTS
231 (SELECT null
232 FROM ZX_EVENT_CLASSES_B B
233 WHERE B.tax_event_class_code = T.tax_event_class_code
234 );
235
236 UPDATE ZX_EVENT_CLASSES_TL T SET (
237 TAX_EVENT_CLASS_NAME
238 ) = (select
239 B.TAX_EVENT_CLASS_NAME
240 from ZX_EVENT_CLASSES_TL B
241 where B.TAX_EVENT_CLASS_CODE = T.TAX_EVENT_CLASS_CODE
242 and B.LANGUAGE = T.SOURCE_LANG)
243 where (
244 T.TAX_EVENT_CLASS_CODE,
245 T.LANGUAGE
246 ) in (select
247 SUBT.TAX_EVENT_CLASS_CODE,
248 SUBT.LANGUAGE
249 from ZX_EVENT_CLASSES_TL SUBB, ZX_EVENT_CLASSES_TL SUBT
250 where SUBB.TAX_EVENT_CLASS_CODE = SUBT.TAX_EVENT_CLASS_CODE
251 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
252 and (SUBB.TAX_EVENT_CLASS_NAME <> SUBT.TAX_EVENT_CLASS_NAME
253 ));
254
255 insert into ZX_EVENT_CLASSES_TL (
256 TAX_EVENT_CLASS_CODE,
257 TAX_EVENT_CLASS_NAME,
258 CREATION_DATE,
259 CREATED_BY,
260 LAST_UPDATE_DATE,
261 LAST_UPDATED_BY,
262 LAST_UPDATE_LOGIN,
263 LANGUAGE,
264 SOURCE_LANG
265 ) select /*+ ORDERED */
266 B.TAX_EVENT_CLASS_CODE,
267 B.TAX_EVENT_CLASS_NAME,
268 B.CREATION_DATE,
269 B.CREATED_BY,
270 B.LAST_UPDATE_DATE,
271 B.LAST_UPDATED_BY,
272 B.LAST_UPDATE_LOGIN,
273 L.LANGUAGE_CODE,
274 B.SOURCE_LANG
275 from ZX_EVENT_CLASSES_TL B, FND_LANGUAGES L
276 where L.INSTALLED_FLAG in ('I', 'B')
277 and B.LANGUAGE = userenv('LANG')
278 and not exists
279 (select NULL
280 from ZX_EVENT_CLASSES_TL T
281 where T.TAX_EVENT_CLASS_CODE = B.TAX_EVENT_CLASS_CODE
282 and T.LANGUAGE = L.LANGUAGE_CODE);
283 end add_language;
284
285 end ZX_EVENT_CLASSES_PKG;