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