[Home] [Help]
PACKAGE BODY: APPS.ZX_DET_FACTOR_TEMPL_PKG
Source
1 package body ZX_DET_FACTOR_TEMPL_PKG as
2 /* $Header: zxddetfactorb.pls 120.4 2005/03/14 10:26:04 scsharma ship $ */
3
4 procedure INSERT_ROW (
5 X_ROWID in out nocopy VARCHAR2,
6 X_DET_FACTOR_TEMPL_ID in NUMBER,
7 X_DET_FACTOR_TEMPL_CODE in VARCHAR2,
8 X_TAX_REGIME_CODE in VARCHAR2,
9 X_LEDGER_ID in NUMBER,
10 X_CHART_OF_ACCOUNTS_ID in NUMBER,
11 X_Template_Usage_Code in VARCHAR2,
12 X_Record_Type_Code in VARCHAR2,
13 X_REQUEST_ID in NUMBER,
14 X_DET_FACTOR_TEMPL_NAME in VARCHAR2,
15 X_DET_FACTOR_TEMPL_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 X_PROGRAM_APPLICATION_ID in NUMBER,
22 X_PROGRAM_ID in NUMBER,
23 X_PROGRAM_LOGIN_ID in NUMBER,
24 X_OBJECT_VERSION_NUMBER in NUMBER) is
25
26 cursor C is select ROWID from ZX_DET_FACTOR_TEMPL_B
27 where DET_FACTOR_TEMPL_ID = X_DET_FACTOR_TEMPL_ID;
28 begin
29 insert into ZX_DET_FACTOR_TEMPL_B (
30 DET_FACTOR_TEMPL_ID,
31 DET_FACTOR_TEMPL_CODE,
32 TAX_REGIME_CODE,
33 LEDGER_ID,
34 CHART_OF_ACCOUNTS_ID,
35 Template_Usage_Code,
36 Record_Type_Code,
37 REQUEST_ID,
38 CREATION_DATE,
39 CREATED_BY,
40 LAST_UPDATE_DATE,
41 LAST_UPDATED_BY,
42 LAST_UPDATE_LOGIN,
43 PROGRAM_APPLICATION_ID,
44 PROGRAM_ID,
45 PROGRAM_LOGIN_ID,
46 OBJECT_VERSION_NUMBER)
47 values (
48 X_DET_FACTOR_TEMPL_ID,
49 X_DET_FACTOR_TEMPL_CODE,
50 X_TAX_REGIME_CODE,
51 X_LEDGER_ID,
52 X_CHART_OF_ACCOUNTS_ID,
53 X_Template_Usage_Code,
54 X_Record_Type_Code,
55 X_REQUEST_ID,
56 X_CREATION_DATE,
57 X_CREATED_BY,
58 X_LAST_UPDATE_DATE,
59 X_LAST_UPDATED_BY,
60 X_LAST_UPDATE_LOGIN,
61 X_PROGRAM_APPLICATION_ID,
62 X_PROGRAM_ID,
63 X_PROGRAM_LOGIN_ID,
64 X_OBJECT_VERSION_NUMBER);
65
66 insert into ZX_DET_FACTOR_TEMPL_TL (
67 DET_FACTOR_TEMPL_ID,
68 DET_FACTOR_TEMPL_NAME,
69 DET_FACTOR_TEMPL_DESC,
70 CREATION_DATE,
71 CREATED_BY,
72 LAST_UPDATE_DATE,
73 LAST_UPDATED_BY,
74 LAST_UPDATE_LOGIN,
75 LANGUAGE,
76 SOURCE_LANG)
77 select
78 X_DET_FACTOR_TEMPL_ID,
79 X_DET_FACTOR_TEMPL_NAME,
80 X_DET_FACTOR_TEMPL_DESC,
81 X_CREATION_DATE,
82 X_CREATED_BY,
83 X_LAST_UPDATE_DATE,
84 X_LAST_UPDATED_BY,
85 X_LAST_UPDATE_LOGIN,
86 L.LANGUAGE_CODE,
87 userenv('LANG')
88 from FND_LANGUAGES L
89 where L.INSTALLED_FLAG in ('I', 'B')
90 and not exists
91 (select NULL
92 from ZX_DET_FACTOR_TEMPL_TL T
93 where T.DET_FACTOR_TEMPL_ID = X_DET_FACTOR_TEMPL_ID
94 and T.LANGUAGE = L.LANGUAGE_CODE);
95 open c;
96 fetch c into X_ROWID;
97 if (c%notfound) then
98 close c;
99 raise no_data_found;
100 end if;
101 close c;
102
103 EXCEPTION
104 WHEN OTHERS THEN
105 APP_EXCEPTION.RAISE_EXCEPTION;
106
107 end INSERT_ROW;
108
109 procedure LOCK_ROW (
110 X_DET_FACTOR_TEMPL_ID in NUMBER,
111 X_DET_FACTOR_TEMPL_CODE in VARCHAR2,
112 X_TAX_REGIME_CODE in VARCHAR2,
113 X_LEDGER_ID in NUMBER,
114 X_CHART_OF_ACCOUNTS_ID in NUMBER,
115 X_Template_Usage_Code in VARCHAR2,
116 X_Record_Type_Code in VARCHAR2,
117 X_REQUEST_ID in NUMBER,
118 X_PROGRAM_APPLICATION_ID in NUMBER,
119 X_PROGRAM_ID in NUMBER,
120 X_PROGRAM_LOGIN_ID in NUMBER,
121 X_DET_FACTOR_TEMPL_NAME in VARCHAR2,
122 X_DET_FACTOR_TEMPL_DESC in VARCHAR2,
123 X_OBJECT_VERSION_NUMBER in NUMBER) is
124
125 cursor c is select DET_FACTOR_TEMPL_CODE,
126 TAX_REGIME_CODE,
127 LEDGER_ID,
128 CHART_OF_ACCOUNTS_ID,
129 Template_Usage_Code,
130 Record_Type_Code,
131 REQUEST_ID,
132 PROGRAM_APPLICATION_ID,
133 PROGRAM_ID,
134 PROGRAM_LOGIN_ID
135 from ZX_DET_FACTOR_TEMPL_B
136 where DET_FACTOR_TEMPL_ID = X_DET_FACTOR_TEMPL_ID
137 for update of DET_FACTOR_TEMPL_ID nowait;
138
139 recinfo c%rowtype;
140
141 cursor c1 is select DET_FACTOR_TEMPL_NAME,
142 DET_FACTOR_TEMPL_DESC,
143 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
144 from ZX_DET_FACTOR_TEMPL_TL
145 where DET_FACTOR_TEMPL_ID = X_DET_FACTOR_TEMPL_ID
146 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
147 for update of DET_FACTOR_TEMPL_ID nowait;
148 begin
149
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
159 if ( (recinfo.DET_FACTOR_TEMPL_CODE = X_DET_FACTOR_TEMPL_CODE)
160 AND ((recinfo.TAX_REGIME_CODE = X_TAX_REGIME_CODE)
161 OR ((recinfo.TAX_REGIME_CODE is null) AND (X_TAX_REGIME_CODE is null)))
162 AND ((recinfo.LEDGER_ID = X_LEDGER_ID)
163 OR ((recinfo.LEDGER_ID is null) AND (X_LEDGER_ID is null)))
164 AND ((recinfo.CHART_OF_ACCOUNTS_ID = X_CHART_OF_ACCOUNTS_ID)
165 OR ((recinfo.CHART_OF_ACCOUNTS_ID is null) AND (X_CHART_OF_ACCOUNTS_ID is null)))
166 AND (recinfo.Template_Usage_Code = X_Template_Usage_Code)
167 AND (recinfo.Record_Type_Code = X_Record_Type_Code)
168 AND ((recinfo.REQUEST_ID = X_REQUEST_ID)
169 OR ((recinfo.REQUEST_ID is null) AND (X_REQUEST_ID is null)))
170 AND ((recinfo.PROGRAM_APPLICATION_ID = X_PROGRAM_APPLICATION_ID)
171 OR ((recinfo.PROGRAM_APPLICATION_ID is null) AND (X_PROGRAM_APPLICATION_ID is null)))
172 AND ((recinfo. PROGRAM_ID = X_PROGRAM_ID)
173 OR ((recinfo.PROGRAM_ID is null) AND (X_PROGRAM_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 ) then
177 null;
178 else
179 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
180 app_exception.raise_exception;
181 end if;
182 for tlinfo in c1 loop
183 if (tlinfo.BASELANG = 'Y') then
184 if ( (tlinfo.DET_FACTOR_TEMPL_NAME = X_DET_FACTOR_TEMPL_NAME)
185 AND ((tlinfo.DET_FACTOR_TEMPL_DESC = X_DET_FACTOR_TEMPL_DESC)
186 OR ((tlinfo.DET_FACTOR_TEMPL_DESC is null) AND (X_DET_FACTOR_TEMPL_DESC is null)))
187 ) then
188 null;
189 else
190 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
191 app_exception.raise_exception;
192 end if;
193 end if;
194 end loop;
195 return;
196
197 EXCEPTION
198 WHEN OTHERS THEN
199 APP_EXCEPTION.RAISE_EXCEPTION;
200
201 end LOCK_ROW;
202
203 procedure UPDATE_ROW (
204 X_DET_FACTOR_TEMPL_ID in NUMBER,
205 X_DET_FACTOR_TEMPL_CODE in VARCHAR2,
206 X_TAX_REGIME_CODE in VARCHAR2,
207 X_LEDGER_ID in NUMBER,
208 X_CHART_OF_ACCOUNTS_ID in NUMBER,
209 X_Template_Usage_Code in VARCHAR2,
210 X_Record_Type_Code in VARCHAR2,
211 X_REQUEST_ID in NUMBER,
212 X_DET_FACTOR_TEMPL_NAME in VARCHAR2,
213 X_DET_FACTOR_TEMPL_DESC in VARCHAR2,
214 X_LAST_UPDATE_DATE in DATE,
215 X_LAST_UPDATED_BY in NUMBER,
216 X_LAST_UPDATE_LOGIN in NUMBER,
217 X_PROGRAM_APPLICATION_ID in NUMBER,
218 X_PROGRAM_ID in NUMBER,
219 X_PROGRAM_LOGIN_ID in NUMBER,
220 X_OBJECT_VERSION_NUMBER in NUMBER) is
221 begin
222
223 update ZX_DET_FACTOR_TEMPL_B set
224 DET_FACTOR_TEMPL_CODE = X_DET_FACTOR_TEMPL_CODE,
225 TAX_REGIME_CODE = X_TAX_REGIME_CODE,
226 LEDGER_ID = X_LEDGER_ID,
227 CHART_OF_ACCOUNTS_ID = X_CHART_OF_ACCOUNTS_ID,
228 Template_Usage_Code = X_Template_Usage_Code,
229 Record_Type_Code = X_Record_Type_Code,
230 REQUEST_ID = X_REQUEST_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 PROGRAM_APPLICATION_ID = X_PROGRAM_APPLICATION_ID,
235 PROGRAM_ID = X_PROGRAM_ID,
236 PROGRAM_LOGIN_ID = X_PROGRAM_LOGIN_ID,
237 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER
238 where DET_FACTOR_TEMPL_ID = X_DET_FACTOR_TEMPL_ID;
239
240 if (sql%notfound) then
241 raise no_data_found;
242 end if;
243
244 update ZX_DET_FACTOR_TEMPL_TL set
245 DET_FACTOR_TEMPL_NAME = X_DET_FACTOR_TEMPL_NAME,
246 DET_FACTOR_TEMPL_DESC = X_DET_FACTOR_TEMPL_DESC,
247 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
248 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
249 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
250 SOURCE_LANG = userenv('LANG')
251 where DET_FACTOR_TEMPL_ID = X_DET_FACTOR_TEMPL_ID
252 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
253
254 if (sql%notfound) then
255 raise no_data_found;
256 end if;
257
258 EXCEPTION
259 WHEN OTHERS THEN
260 APP_EXCEPTION.RAISE_EXCEPTION;
261
262 end UPDATE_ROW;
263
264 procedure DELETE_ROW (
265 X_DET_FACTOR_TEMPL_ID in NUMBER) is
266 begin
267
268 delete from ZX_DET_FACTOR_TEMPL_TL
269 where DET_FACTOR_TEMPL_ID = X_DET_FACTOR_TEMPL_ID;
270
271 if (sql%notfound) then
272 raise no_data_found;
273 end if;
274
275 delete from ZX_DET_FACTOR_TEMPL_B
276 where DET_FACTOR_TEMPL_ID = X_DET_FACTOR_TEMPL_ID;
277
278 if (sql%notfound) then
279 raise no_data_found;
280 end if;
281
282 EXCEPTION
283 WHEN OTHERS THEN
284 APP_EXCEPTION.RAISE_EXCEPTION;
285
286 end DELETE_ROW;
287
288 procedure ADD_LANGUAGE
289 is
290 begin
291
292 delete from ZX_DET_FACTOR_TEMPL_TL T
293 where not exists (select NULL
294 from ZX_DET_FACTOR_TEMPL_B B
295 where B.DET_FACTOR_TEMPL_ID = T.DET_FACTOR_TEMPL_ID);
296
297 update ZX_DET_FACTOR_TEMPL_TL T
298 set (DET_FACTOR_TEMPL_NAME, DET_FACTOR_TEMPL_DESC) =
299 (select B.DET_FACTOR_TEMPL_NAME,
300 B.DET_FACTOR_TEMPL_DESC
301 from ZX_DET_FACTOR_TEMPL_TL B
302 where B.DET_FACTOR_TEMPL_ID = T.DET_FACTOR_TEMPL_ID
303 and B.LANGUAGE = T.SOURCE_LANG)
304 where (T.DET_FACTOR_TEMPL_ID, T.LANGUAGE) in
305 (select SUBT.DET_FACTOR_TEMPL_ID,
306 SUBT.LANGUAGE
307 from ZX_DET_FACTOR_TEMPL_TL SUBB, ZX_DET_FACTOR_TEMPL_TL SUBT
308 where SUBB.DET_FACTOR_TEMPL_ID = SUBT.DET_FACTOR_TEMPL_ID
309 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
310 and (SUBB.DET_FACTOR_TEMPL_NAME <> SUBT.DET_FACTOR_TEMPL_NAME
311 or SUBB.DET_FACTOR_TEMPL_DESC <> SUBT.DET_FACTOR_TEMPL_DESC
312 or (SUBB.DET_FACTOR_TEMPL_DESC is null
313 and SUBT.DET_FACTOR_TEMPL_DESC is not null)
314 or (SUBB.DET_FACTOR_TEMPL_DESC is not null
315 and SUBT.DET_FACTOR_TEMPL_DESC is null)));
316
317 insert into ZX_DET_FACTOR_TEMPL_TL (DET_FACTOR_TEMPL_ID,
318 DET_FACTOR_TEMPL_NAME,
319 DET_FACTOR_TEMPL_DESC,
320 CREATION_DATE,
321 CREATED_BY,
322 LAST_UPDATE_DATE,
323 LAST_UPDATED_BY,
324 LAST_UPDATE_LOGIN,
325 LANGUAGE,
326 SOURCE_LANG)
327 select B.DET_FACTOR_TEMPL_ID,
328 B.DET_FACTOR_TEMPL_NAME,
329 B.DET_FACTOR_TEMPL_DESC,
330 B.CREATION_DATE,
331 B.CREATED_BY,
332 B.LAST_UPDATE_DATE,
333 B.LAST_UPDATED_BY,
334 B.LAST_UPDATE_LOGIN,
335 L.LANGUAGE_CODE,
336 B.SOURCE_LANG
337 from ZX_DET_FACTOR_TEMPL_TL B,
338 FND_LANGUAGES L
339 where L.INSTALLED_FLAG in ('I', 'B')
340 and B.LANGUAGE = userenv('LANG')
341 and not exists
342 (select NULL
343 from ZX_DET_FACTOR_TEMPL_TL T
344 where T.DET_FACTOR_TEMPL_ID =
345 B.DET_FACTOR_TEMPL_ID
346 and T.LANGUAGE = L.LANGUAGE_CODE);
347
348 EXCEPTION
349 WHEN OTHERS THEN
350 APP_EXCEPTION.RAISE_EXCEPTION;
351
352 end ADD_LANGUAGE;
353
354 procedure bulk_insert_det_factor_templ (
355 X_DET_FACTOR_TEMPL_ID IN t_det_factor_templ_id,
356 X_DET_FACTOR_TEMPL_CODE IN t_det_factor_templ_code,
357 X_TAX_REGIME_CODE IN t_tax_regime_code,
358 X_LEDGER_ID IN t_ledger_id,
359 X_CHART_OF_ACCOUNTS_ID IN t_chart_of_accounts_id,
360 X_Template_Usage_Code IN t_template_usage,
361 X_Record_Type_Code IN t_record_type,
362 X_DET_FACTOR_TEMPL_NAME IN t_det_factor_templ_name,
363 X_DET_FACTOR_TEMPL_DESC IN t_det_factor_templ_desc) is
364
365 begin
366
367 if x_det_factor_templ_id.count <> 0 then
368 forall i in x_det_factor_templ_id.first..x_det_factor_templ_id.last
369 insert into ZX_DET_FACTOR_TEMPL_B (DET_FACTOR_TEMPL_ID,
370 DET_FACTOR_TEMPL_CODE,
371 TAX_REGIME_CODE,
372 LEDGER_ID,
373 CHART_OF_ACCOUNTS_ID,
374 Template_Usage_Code,
375 Record_Type_Code,
376 CREATED_BY ,
377 CREATION_DATE ,
378 LAST_UPDATED_BY ,
379 LAST_UPDATE_DATE ,
380 LAST_UPDATE_LOGIN ,
381 REQUEST_ID ,
382 PROGRAM_APPLICATION_ID ,
383 PROGRAM_ID ,
384 PROGRAM_LOGIN_ID)
385 values (X_DET_FACTOR_TEMPL_ID(i),
386 X_DET_FACTOR_TEMPL_CODE(i),
387 X_TAX_REGIME_CODE(i),
388 X_LEDGER_ID(i),
389 X_CHART_OF_ACCOUNTS_ID(i),
390 X_Template_Usage_Code(i),
391 X_Record_Type_Code(i),
392 fnd_global.user_id ,
393 sysdate ,
394 fnd_global.user_id ,
395 sysdate ,
396 fnd_global.conc_login_id ,
397 fnd_global.conc_request_id ,
398 fnd_global.prog_appl_id ,
399 fnd_global.conc_program_id ,
400 fnd_global.conc_login_id
401 );
402
403 forall i in x_det_factor_templ_id.first..x_det_factor_templ_id.last
404 insert into ZX_DET_FACTOR_TEMPL_TL (DET_FACTOR_TEMPL_ID,
405 DET_FACTOR_TEMPL_NAME,
406 DET_FACTOR_TEMPL_DESC,
407 LANGUAGE,
408 SOURCE_LANG,
409 CREATED_BY ,
410 CREATION_DATE ,
411 LAST_UPDATED_BY ,
412 LAST_UPDATE_DATE ,
413 LAST_UPDATE_LOGIN)
414 select X_DET_FACTOR_TEMPL_ID(i),
415 X_DET_FACTOR_TEMPL_NAME(i),
416 X_DET_FACTOR_TEMPL_DESC(i),
417 L.LANGUAGE_CODE,
418 userenv('LANG'),
419 fnd_global.user_id ,
420 sysdate ,
421 fnd_global.user_id ,
422 sysdate ,
423 fnd_global.conc_login_id
424 from FND_LANGUAGES L
425 where L.INSTALLED_FLAG in ('I', 'B')
426 and not exists
427 (select NULL
428 from ZX_DET_FACTOR_TEMPL_TL T
429 where T.DET_FACTOR_TEMPL_ID =
430 X_DET_FACTOR_TEMPL_ID(i)
431 and T.LANGUAGE = L.LANGUAGE_CODE);
432 end if;
433
434 EXCEPTION
435 WHEN OTHERS THEN
436 APP_EXCEPTION.RAISE_EXCEPTION;
437
438 end bulk_insert_det_factor_templ;
439
440 end ZX_DET_FACTOR_TEMPL_PKG;