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