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