[Home] [Help]
PACKAGE BODY: APPS.IEX_DUNNING_PLANS_PKG
Source
1 package body IEX_DUNNING_PLANS_PKG as
2 /* $Header: iextdplb.pls 120.0 2005/07/09 21:55:04 ctlee noship $ */
3 procedure INSERT_ROW (
4 X_ROWID in out nocopy VARCHAR2,
5 x_dunning_plan_id in NUMBER,
6 x_name in VARCHAR2,
7 x_description in VARCHAR2,
8 x_start_date in date,
9 x_end_date in date,
10 x_ENABLED_FLAG in VARCHAR2,
11 x_aging_bucket_id in number,
12 x_score_id in number,
13 x_dunning_level in VARCHAR2,
14 x_object_version_number in number,
15 x_CREATION_DATE in DATE,
16 x_CREATED_BY in NUMBER,
17 x_LAST_UPDATE_DATE in DATE,
18 x_LAST_UPDATED_BY in NUMBER,
19 x_LAST_UPDATE_LOGIN in NUMBER,
20 x_PROGRAM_APPLICATION_ID in NUMBER,
21 x_PROGRAM_ID in NUMBER,
22 x_PROGRAM_UPDATE_DATE in DATE
23 ) is
24 cursor l_insert is
25 select ROWID from iex_dunning_plans_b
26 where dunning_plan_id = x_dunning_plan_id ;
27 l_rowid varchar2(2000);
28 begin
29
30 insert into iex_dunning_plans_b (
31 DUNNING_PLAN_ID,
32 START_DATE,
33 END_DATE,
34 ENABLED_FLAG,
35 AGING_BUCKET_ID,
36 SCORE_ID,
37 DUNNING_LEVEL,
38 OBJECT_VERSION_NUMBER,
39 CREATED_BY,
40 CREATION_DATE,
41 LAST_UPDATE_DATE,
42 LAST_UPDATED_BY,
43 LAST_UPDATE_LOGIN,
44 PROGRAM_APPLICATION_ID,
45 PROGRAM_ID,
46 PROGRAM_UPDATE_DATE
47 ) values (
48 x_dunning_plan_id,
49 x_START_DATE,
50 x_END_DATE,
51 x_ENABLED_FLAG,
52 x_AGING_BUCKET_ID,
53 x_SCORE_ID,
54 x_DUNNING_LEVEL,
55 1.0,
56 fnd_global.user_id,
57 sysdate,
58 sysdate,
59 fnd_global.user_id,
60 fnd_global.user_id,
61 to_number(null),
62 to_number(null),
63 to_date(null)
64 );
65
66
67 insert into iex_dunning_plans_tl (
68 DUNNING_PLAN_ID,
69 NAME,
70 DESCRIPTION,
71 LANGUAGE,
72 SOURCE_LANG,
73 CREATED_BY,
74 CREATION_DATE,
75 LAST_UPDATE_DATE,
76 LAST_UPDATED_BY,
77 LAST_UPDATE_LOGIN
78 ) select
79 x_DUNNING_PLAN_ID,
80 x_NAME,
81 x_DESCRIPTION,
82 L.LANGUAGE_CODE,
83 userenv('LANG'),
84 fnd_global.user_id,
85 sysdate,
86 sysdate,
87 fnd_global.user_id,
88 fnd_global.user_id
89 from FND_LANGUAGES L
90 where L.INSTALLED_FLAG in ('I', 'B')
91 and not exists
92 (select NULL
93 from iex_dunning_plans_tl T
94 where T.dunning_plan_id = x_dunning_plan_id
95 and T.LANGUAGE = L.LANGUAGE_CODE);
96
97 open l_insert;
98 fetch l_insert into l_rowid;
99 if (l_insert%notfound) then
100 close l_insert;
101 raise no_data_found;
102 end if;
103 close l_insert;
104
105 end INSERT_ROW;
106
107 procedure LOCK_ROW (
108 x_dunning_plan_id in NUMBER,
109 x_name in VARCHAR2,
110 x_description in VARCHAR2,
111 x_start_date in date,
112 x_end_date in date,
113 x_ENABLED_FLAG in VARCHAR2,
114 x_aging_bucket_id in number,
115 x_score_id in number,
116 x_dunning_level in VARCHAR2,
117 x_object_version_number in number,
118 -- x_CREATION_DATE in DATE,
119 -- x_CREATED_BY in NUMBER,
120 -- x_LAST_UPDATE_DATE in DATE,
121 -- x_LAST_UPDATED_BY in NUMBER,
122 -- x_LAST_UPDATE_LOGIN in NUMBER,
123 x_PROGRAM_APPLICATION_ID in NUMBER,
124 x_PROGRAM_ID in NUMBER,
125 x_PROGRAM_UPDATE_DATE in DATE
126 ) is
127 cursor c is select
128 DUNNING_PLAN_ID,
129 START_DATE,
130 END_DATE,
131 ENABLED_FLAG,
132 AGING_BUCKET_ID,
133 SCORE_ID,
134 DUNNING_LEVEL
135 from iex_dunning_plans_b
136 where dunning_plan_id = x_dunning_plan_id
137 for update of dunning_plan_id nowait;
138
139 recinfo c%rowtype;
140
141 cursor c1 is select
142 NAME,
143 DESCRIPTION,
144 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
145 from iex_dunning_plans_tl
146 where dunning_plan_id = x_dunning_plan_id
147 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
148 for update of dunning_plan_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
159 if ( ((recinfo.start_date = x_start_date)
160 OR ((recinfo.start_date is null) AND (x_start_date is null)))
161 AND ((recinfo.end_date = x_end_date)
162 OR ((recinfo.end_date is null) AND (x_end_date is null)))
163 AND ((recinfo.enabled_flag = x_enabled_flag)
164 OR ((recinfo.enabled_flag is null) AND (x_enabled_flag is null)))
165 AND ((recinfo.aging_bucket_id = x_aging_bucket_id)
166 OR ((recinfo.aging_bucket_id is null) AND (x_aging_bucket_id is null)))
167 AND ((recinfo.score_id = x_score_id)
168 OR ((recinfo.score_id is null) AND (x_score_id is null)))
169 AND ((recinfo.dunning_level = x_dunning_level)
170 OR ((recinfo.dunning_level is null) AND (x_dunning_level is null)))
171 ) then
172 null;
173 else
174 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
175 app_exception.raise_exception;
176 end if;
177
178 for tlinfo in c1 loop
179 if (tlinfo.BASELANG = 'Y') then
180 if ( ((tlinfo.name = x_name)
181 OR ((tlinfo.name is null) AND (x_name is null)))
182 AND ((tlinfo.description = x_description)
183 OR ((tlinfo.description is null) AND (x_description is null)))
184 ) then
185 null;
186 else
187 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
188 app_exception.raise_exception;
189 end if;
190 end if;
191 end loop;
192 return;
193 end LOCK_ROW;
194
195 procedure UPDATE_ROW (
196 x_dunning_plan_id in NUMBER,
197 x_name in VARCHAR2,
198 x_description in VARCHAR2,
199 x_start_date in date,
200 x_end_date in date,
201 x_ENABLED_FLAG in VARCHAR2,
202 x_aging_bucket_id in number,
203 x_score_id in number,
204 x_dunning_level in VARCHAR2,
205 x_object_version_number in number,
206 -- x_CREATION_DATE in DATE,
207 -- x_CREATED_BY in NUMBER,
208 x_LAST_UPDATE_DATE in DATE,
209 x_LAST_UPDATED_BY in NUMBER,
210 x_LAST_UPDATE_LOGIN in NUMBER,
211 x_PROGRAM_APPLICATION_ID in NUMBER,
212 x_PROGRAM_ID in NUMBER,
213 x_PROGRAM_UPDATE_DATE in DATE
214 ) is
215 begin
216 update iex_dunning_plans_b set
217 START_DATE = x_start_date,
218 END_DATE = x_end_date,
219 ENABLED_FLAG = x_enabled_flag,
220 AGING_BUCKET_ID = x_aging_bucket_id,
221 SCORE_ID = x_score_id,
222 DUNNING_LEVEL = x_dunning_level,
223 OBJECT_VERSION_NUMBER = object_version_number + 1.0,
224 LAST_UPDATE_DATE = sysdate,
225 LAST_UPDATED_BY = fnd_global.user_id,
226 LAST_UPDATE_LOGIN = fnd_global.user_id
227 where dunning_plan_id = x_dunning_plan_id;
228
229 if (sql%notfound) then
230 raise no_data_found;
231 end if;
232
233 update iex_dunning_plans_tl set
234 name = x_name,
235 description = x_description,
236 LAST_UPDATE_DATE = sysdate,
237 LAST_UPDATED_BY = fnd_global.user_id,
238 LAST_UPDATE_LOGIN = fnd_global.user_id,
239 SOURCE_LANG = userenv('LANG')
240 where dunning_plan_id = x_dunning_plan_id
241 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
242
243 if (sql%notfound) then
244 raise no_data_found;
245 end if;
246 end UPDATE_ROW;
247
248 procedure DELETE_ROW (
249 x_dunning_plan_id in NUMBER
250 ) is
251 begin
252 delete from iex_dunning_plans_tl
253 where dunning_plan_id = x_dunning_plan_id;
254
255 if (sql%notfound) then
256 raise no_data_found;
257 end if;
258
259 delete from iex_dunning_plans_b
260 where dunning_plan_id = x_dunning_plan_id;
261
262 if (sql%notfound) then
263 raise no_data_found;
264 end if;
265
266 delete from iex_ag_dn_xref
267 where dunning_plan_id = x_dunning_plan_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 iex_dunning_plans_tl T
278 where not exists
279 (select NULL
280 from iex_dunning_plans_b B
281 where B.dunning_plan_id = T.dunning_plan_id
282 );
283
284 update iex_dunning_plans_tl T set (
285 name,
286 description
287 ) = (select
288 B.name,
289 B.description
290 from iex_dunning_plans_tl B
291 where B.dunning_plan_id = T.dunning_plan_id
292 and B.LANGUAGE = T.SOURCE_LANG)
293 where (
294 T.dunning_plan_id,
295 T.LANGUAGE
296 ) in (select
297 SUBT.dunning_plan_id,
298 SUBT.LANGUAGE
299 from iex_dunning_plans_tl SUBB, iex_dunning_plans_tl SUBT
300 where SUBB.dunning_plan_id = SUBT.dunning_plan_id
301 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
302 and (SUBB.name <> SUBT.name
303 or (SUBB.name is null and SUBT.name is not null)
304 or (SUBB.name is not null and SUBT.name is null)
305 or SUBB.description <> SUBT.description
306 or (SUBB.description is null and SUBT.description is not null)
307 or (SUBB.description is not null and SUBT.description is null)
308 ));
309
310 insert into iex_dunning_plans_tl (
311 dunning_plan_id,
312 name,
313 description,
314 LAST_UPDATE_DATE,
315 LAST_UPDATED_BY,
316 CREATION_DATE,
317 CREATED_BY,
318 LAST_UPDATE_LOGIN,
319 LANGUAGE,
320 SOURCE_LANG
321 ) select
322 B.dunning_plan_id,
323 B.name,
324 B.description,
325 B.LAST_UPDATE_DATE,
326 B.LAST_UPDATED_BY,
327 B.CREATION_DATE,
328 B.CREATED_BY,
329 B.LAST_UPDATE_LOGIN,
330 L.LANGUAGE_CODE,
331 B.SOURCE_LANG
332 from iex_dunning_plans_tl B, FND_LANGUAGES L
333 where L.INSTALLED_FLAG in ('I', 'B')
334 and B.LANGUAGE = userenv('LANG')
335 and not exists
336 (select NULL
337 from iex_dunning_plans_tl T
338 where T.dunning_plan_id = B.dunning_plan_id
339 and T.LANGUAGE = L.LANGUAGE_CODE);
340 end ADD_LANGUAGE;
341
342 procedure TRANSLATE_ROW (
343 x_dunning_plan_id in NUMBER,
344 x_name in VARCHAR2,
345 x_description in VARCHAR2
346 ) is
347 begin
348 update iex_dunning_plans_tl
349 set name = x_name,
350 description = x_description,
351 source_lang = userenv('LANG'),
352 last_update_date = sysdate,
353 last_updated_by = fnd_global.user_id,
354 last_update_login = 0
355 where dunning_plan_id = x_dunning_plan_id
356 and userenv('LANG') in (language, source_lang);
357
358 end TRANSLATE_ROW;
359
360 procedure LOAD_ROW (
361 x_dunning_plan_id in NUMBER,
362 x_name in VARCHAR2,
363 x_description in VARCHAR2,
364 x_start_date in date,
365 x_end_date in date,
366 x_ENABLED_FLAG in VARCHAR2,
367 x_aging_bucket_id in number,
368 x_score_id in number,
369 x_dunning_level in VARCHAR2,
370 x_object_version_number in number,
371 x_PROGRAM_APPLICATION_ID in NUMBER,
372 x_PROGRAM_ID in NUMBER,
373 x_PROGRAM_UPDATE_DATE in DATE
374 ) IS
375 begin
376 declare
377 user_id number := 0;
378 row_id varchar2(64);
379 l_dunning_plan_id NUMBER;
380 begin
381
382 iex_dunning_plans_pkg.UPDATE_ROW (
383 x_dunning_plan_id => x_dunning_plan_id,
384 x_name => x_name,
385 x_description => x_description,
386 x_START_DATE => x_start_date,
387 x_END_DATE => x_end_date,
388 x_ENABLED_FLAG => x_enabled_flag,
389 x_AGING_BUCKET_ID => x_aging_bucket_id,
390 x_SCORE_ID => x_score_id,
391 x_DUNNING_LEVEL => x_dunning_level,
392 x_object_version_number => to_number(null),
393 -- x_CREATION_DATE => to_date(null),
394 -- x_CREATED_BY => to_number(null),
395 x_LAST_UPDATE_DATE => to_date(null),
396 x_LAST_UPDATED_BY => to_number(null),
397 x_LAST_UPDATE_LOGIN => to_number(null),
398 x_PROGRAM_APPLICATION_ID => to_number(null),
399 x_PROGRAM_ID => to_number(null),
400 x_PROGRAM_UPDATE_DATE => to_date(null)
401 );
402 exception
403 when NO_DATA_FOUND then
404 l_dunning_plan_id := x_dunning_plan_id;
405 iex_dunning_plans_pkg.INSERT_ROW (
406 x_rowid => row_id,
407 x_dunning_plan_id => l_dunning_plan_id,
408 x_name => x_name,
409 x_description => x_description,
410 x_START_DATE => x_start_date,
411 x_END_DATE => x_end_date,
412 x_ENABLED_FLAG => x_enabled_flag,
413 x_AGING_BUCKET_ID => x_aging_bucket_id,
414 x_SCORE_ID => x_score_id,
415 x_DUNNING_LEVEL => x_dunning_level,
416 x_object_version_number => to_number(null),
417 x_CREATION_DATE => to_date(null),
418 x_CREATED_BY => to_number(null),
419 x_LAST_UPDATE_DATE => to_date(null),
420 x_LAST_UPDATED_BY => to_number(null),
421 x_LAST_UPDATE_LOGIN => to_number(null),
422 x_PROGRAM_APPLICATION_ID => to_number(null),
423 x_PROGRAM_ID => to_number(null),
424 x_PROGRAM_UPDATE_DATE => to_date(null)
425 );
426
427 end;
428 end LOAD_ROW;
429
430 end iex_dunning_plans_pkg;