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