[Home] [Help]
PACKAGE BODY: APPS.JG_ZZ_VAT_REGISTERS_PKG
Source
1 package body jg_zz_vat_registers_pkg as
2 /*$Header: jgzzvrgb.pls 120.1 2006/06/23 12:26:17 brathod ship $*/
3 /* CHANGE HISTORY ------------------------------------------------------------------------------------------
4 DATE AUTHOR VERSION BUG NO. DESCRIPTION
5 (DD/MM/YYYY) (UID)
6 ------------------------------------------------------------------------------------------------------------
7 23/6/2006 BRATHOD 120.1 5166688 Modified the signature of INSERT_ROW procedure in
8 to return rowid to caller of API by adding out
9 parameter in the call. Refer bug# 5166688 for details
10 -----------------------------------------------------------------------------------------------------------*/
11
12 procedure insert_row
13 ( x_record jg_zz_vat_registers_vl%rowtype
14 , x_vat_register_id in out nocopy jg_zz_vat_registers_b.vat_register_id%type
15 , x_row_id out nocopy rowid
16 )
17 is
18
19
20 cursor c_gen_vat_register_id
21 is
22 select jg_zz_vat_registers_b_s.nextval
23 from dual;
24
25 begin
26
27 if x_record.vat_register_id is null then
28 open c_gen_vat_register_id;
29 fetch c_gen_vat_register_id into x_vat_register_id;
30 close c_gen_vat_register_id;
31 else
32 x_vat_register_id := x_record.vat_register_id;
33 end if;
34
35 insert into jg_zz_vat_registers_b
36 ( vat_register_id
37 , vat_reporting_entity_id
38 , register_type
39 , effective_from_date
40 , effective_to_date
41 , created_by
42 , creation_date
43 , last_updated_by
44 , last_update_date
45 , last_update_login
46 )
47 values ( x_vat_register_id
48 , x_record.vat_reporting_entity_id
49 , x_record.register_type
50 , x_record.effective_from_date
51 , x_record.effective_to_date
52 , x_record.created_by
53 , x_record.creation_date
54 , x_record.last_updated_by
55 , x_record.last_update_date
56 , x_record.last_update_login
57 ) returning rowid into x_row_id;
58
59 insert into jg_zz_vat_registers_tl
60 ( vat_register_id
61 , register_name
62 , language
63 , source_lang
64 , created_by
65 , creation_date
66 , last_updated_by
67 , last_update_date
68 , last_update_login
69 )
70 (
71 select
72 x_vat_register_id
73 , x_record.register_name
74 , fndlang.language_code
75 , userenv('LANG')
76 , x_record.created_by
77 , x_record.creation_date
78 , x_record.last_updated_by
79 , x_record.last_update_date
80 , x_record.last_update_login
81 from fnd_languages fndlang
82 where fndlang.installed_flag in ('I','B')
83 and not exists (select 1
84 from jg_zz_vat_registers_tl jzvrgtl
85 where jzvrgtl.vat_register_id = x_vat_register_id
86 and jzvrgtl.language = fndlang.language_code
87 )
88 );
89 exception
90 when others then
91 x_vat_register_id := null;
92 x_row_id := null;
93 raise;
94 end insert_row ;
95
96 /*------------------------------------------------------------------------------------------------------------*/
97
98 procedure lock_row ( x_record jg_zz_vat_registers_vl%rowtype )
99 is
100
101 lr_locked_b_row jg_zz_vat_registers_b%rowtype;
102 lr_locked_tl_row jg_zz_vat_registers_tl%rowtype;
103 lv_usr_env_lang fnd_languages.language_code%type;
104
105 cursor c_locked_b_row
106 is
107 select *
108 from jg_zz_vat_registers_b
109 where vat_register_id = x_record.vat_register_id
110 for update nowait;
111
112 cursor c_locked_tl_row
113 is
114 select *
115 from jg_zz_vat_registers_tl
116 where vat_register_id = x_record.vat_register_id
117 and ( language = lv_usr_env_lang
118 or source_lang = lv_usr_env_lang
119 )
120 for update nowait;
121
122 begin
123
124 lv_usr_env_lang := userenv('LANG');
125
126 open c_locked_b_row;
127 fetch c_locked_b_row into lr_locked_b_row;
128
129 if (c_locked_b_row%notfound) then
130 close c_locked_b_row;
131 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
132 app_exception.raise_exception;
133 end if;
134
135 close c_locked_b_row;
136
137 if ( nvl(lr_locked_b_row.register_type,'X$') = nvl( x_record.register_type,'X$')
138 and nvl(lr_locked_b_row.effective_from_date, sysdate) = nvl(x_record.effective_from_date, sysdate)
139 and nvl(lr_locked_b_row.effective_to_date , sysdate) = nvl(x_record.effective_to_date, sysdate)
140 )
141 then
142 null;
143 else
144 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
145 app_exception.raise_exception;
146 end if;
147
148 open c_locked_tl_row;
149 fetch c_locked_tl_row into lr_locked_tl_row;
150 close c_locked_tl_row;
151
152 if lr_locked_tl_row.language = lr_locked_tl_row.source_lang then
153 if nvl(lr_locked_tl_row.register_name, 'X$') = nvl(x_record.register_name, 'X$') then
154 null;
155 else
156 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
157 app_exception.raise_exception;
158 end if;
159 end if;
160
161 return;
162 end lock_row;
163
164 /*------------------------------------------------------------------------------------------------------------*/
165
166 procedure update_row (x_record jg_zz_vat_registers_vl%rowtype)
167 is
168
169 lv_usr_env_lang fnd_languages.language_code%type;
170
171 begin
172
173 lv_usr_env_lang := userenv('LANG');
174
175 update jg_zz_vat_registers_b
176 set vat_reporting_entity_id= x_record.vat_reporting_entity_id
177 , register_type = x_record.register_type
178 , effective_from_date = x_record.effective_from_date
179 , effective_to_date = x_record.effective_to_date
180 , last_updated_by = x_record.last_updated_by
181 , last_update_date = x_record.last_update_date
182 , last_update_login = x_record.last_update_login
183 where vat_register_id = x_record.vat_register_id;
184
185 update jg_zz_vat_registers_tl
186 set register_name = x_record.register_name
187 , source_lang = lv_usr_env_lang
188 , last_updated_by = x_record.last_updated_by
189 , last_update_date = x_record.last_update_date
190 , last_update_login = x_record.last_update_login
191 where vat_register_id = x_record.vat_register_id
192 and ( language = lv_usr_env_lang
193 or source_lang = lv_usr_env_lang
194 ) ;
195
196 end update_row;
197
198 /*------------------------------------------------------------------------------------------------------------*/
199
200 procedure delete_row (x_vat_register_id jg_zz_vat_registers_b.vat_register_id%type)
201 is
202 begin
203 delete from jg_zz_vat_registers_b
204 where vat_register_id = x_vat_register_id;
205
206 delete from jg_zz_vat_registers_tl
207 where vat_register_id = x_vat_register_id;
208
209 end delete_row;
210
211 /*------------------------------------------------------------------------------------------------------------*/
212
213
214 procedure add_language is
215
216 begin
217
218 delete from jg_zz_vat_registers_tl T
219 where not exists (
220 select NULL
221 from jg_zz_vat_registers_b B
222 where B.VAT_REGISTER_ID = T.VAT_REGISTER_ID
223 );
224
225 update jg_zz_vat_registers_tl T
226 set REGISTER_NAME = (select
227 B.REGISTER_NAME
228 from jg_zz_vat_registers_tl B
229 where B.vAT_REGISTER_ID = T.VAT_REGISTER_ID
230 and B.LANGUAGE = T.SOURCE_LANG)
231 where (T.VAT_REGISTER_ID, T.LANGUAGE ) in
232 (select subt.vat_register_id, subt.language
233 from jg_zz_vat_registers_tl SUBB, jg_zz_vat_registers_tl SUBT
234 where SUBB.VAT_REGISTER_ID = SUBT.VAT_REGISTER_ID
235 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
236 and (SUBB.REGISTER_NAME <> SUBT.REGISTER_NAME
237 or (SUBB.REGISTER_NAME is null and SUBT.REGISTER_NAME is not null)
238 or (SUBB.REGISTER_NAME is not null and SUBT.REGISTER_NAME is null)
239 )
240 );
241
242 insert into jg_zz_vat_registers_tl (
243 VAT_REGISTER_ID,
244 REGISTER_NAME,
245 LAST_UPDATE_DATE,
246 LAST_UPDATED_BY,
247 CREATION_DATE,
248 CREATED_BY,
249 LAST_UPDATE_LOGIN,
250 LANGUAGE,
251 SOURCE_LANG
252 ) select
253 B.VAT_REGISTER_ID,
254 B.REGISTER_NAME,
255 B.LAST_UPDATE_DATE,
256 B.LAST_UPDATED_BY,
257 B.CREATION_DATE,
258 B.CREATED_BY,
259 B.LAST_UPDATE_LOGIN,
260 L.LANGUAGE_CODE,
261 B.SOURCE_LANG
262 from jg_zz_vat_registers_tl b, fnd_languages l
263 where L.INSTALLED_FLAG in ('I', 'B')
264 and B.LANGUAGE = userenv('LANG')
265 and not exists
266 (select NULL
267 from jg_zz_vat_registers_tl T
268 where T.VAT_REGISTER_ID = B.VAT_REGISTER_ID
269 and T.LANGUAGE = L.LANGUAGE_CODE);
270
271 end add_language;
272
273 procedure LOAD_ROW (
274 x_VAT_REGISTER_ID in NUMBER,
275 x_VAT_REPORTING_ENTITY_ID in NUMBER,
276 x_REGISTER_TYPE in VARCHAR2,
277 x_REGISTER_NAME in VARCHAR2,
278 x_EFFECTIVE_FROM_DATE in DATE,
279 x_EFFECTIVE_TO_DATE in DATE,
280 x_OWNER in VARCHAR2
281 ) is
282
283 l_user_id number;
284 l_row_id rowid;
285 l_table_rec jg_zz_vat_registers_vl%rowtype;
286 ln_vat_register_id number;
287
288 begin
289 l_user_id := 0;
290
291 if (x_OWNER = 'SEED') then
292 l_user_id := 1;
293 end if;
294
295 l_table_rec.vat_register_id := x_vat_register_id;
296 l_table_rec.vat_reporting_entity_id := x_vat_reporting_entity_id ;
297 l_table_rec.register_type := x_register_type ;
298 l_table_rec.register_name := x_register_name ;
299 l_table_rec.effective_from_date := x_effective_from_date;
300 l_table_rec.effective_to_date := x_effective_to_date ;
301 l_table_rec.last_update_date := sysdate ;
302 l_table_rec.last_updated_by := l_user_id ;
303 l_table_rec.creation_date := sysdate ;
304 l_table_rec.created_by := l_user_id;
305 l_table_rec.last_update_login := 0 ;
306
307
308 jg_zz_vat_registers_pkg.UPDATE_ROW (
309 x_record => l_table_rec
310 );
311
312 exception
313 when NO_DATA_FOUND then
314 jg_zz_vat_registers_pkg.INSERT_ROW (
315 x_record => l_table_rec
316 ,x_vat_register_id => ln_vat_register_id
317 ,x_row_id => l_row_id
318 );
319
320 end LOAD_ROW;
321
322
323 procedure TRANSLATE_ROW (
324 X_VAT_REGISTER_ID in NUMBER,
325 X_REGISTER_NAME in VARCHAR2,
326 X_OWNER in VARCHAR2
327 ) is
328 begin
329 update jg_zz_vat_registers_tl set
330 REGISTER_NAME = X_REGISTER_NAME,
331 LAST_UPDATE_DATE = sysdate,
332 LAST_UPDATED_BY = decode(X_OWNER, 'SEED', 1, 0),
333 LAST_UPDATE_LOGIN = 0,
334 SOURCE_LANG = userenv('LANG')
335 where VAT_REGISTER_ID = X_VAT_REGISTER_ID
336 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
337 end TRANSLATE_ROW;
338
339 end jg_zz_vat_registers_pkg;