[Home] [Help]
PACKAGE BODY: APPS.IBY_UPGRADE_PARAM_PVT
Source
1 PACKAGE BODY IBY_UPGRADE_PARAM_PVT AS
2 /* $Header: ibyupgpb.pls 120.0 2005/05/03 22:42:07 jleybovi noship $ */
3
4
5 --
6 -- Name: update_account
7 -- Args:
8 -- p_bep_account_id => the BEP account ID (type)
9 -- p_bepid_ => The bep ID
10 -- p_merchant_account_names => names of merchant account optionss
11 -- p_merchant_account_values => values of merchant account options
12 -- p_online_param_names => names of online transmission parameters
13 -- p_online_param_values => values of online transmission parameters
14 -- p_online_param_types => types of online transmission parameters
15 -- p_settle_param_names => names of settle transmission parameters
16 -- p_settle_param_values => values of settle transmission parameters
17 -- p_settle_param_types => types of settle transmission parameters
18 -- p_query_param_names => names of query transmission parameters
19 -- p_query_param_values => values of query transmission parameters
20 -- p_query_param_types => types of online transmission parameters
21 -- p_commit => flag to indicate whether to commit
22 --
23 PROCEDURE update_account
24 (
25 p_bep_account_id IN NUMBER,
26 p_bepid IN NUMBER,
27 p_merchant_account_names IN JTF_VARCHAR2_TABLE_100,
28 p_merchant_account_values IN JTF_VARCHAR2_TABLE_100,
29 p_online_param_names IN JTF_VARCHAR2_TABLE_100,
30 p_online_param_values IN JTF_VARCHAR2_TABLE_100,
31 p_online_param_types IN JTF_VARCHAR2_TABLE_100,
32 p_settle_param_names IN JTF_VARCHAR2_TABLE_100,
33 p_settle_param_values IN JTF_VARCHAR2_TABLE_100,
34 p_settle_param_types IN JTF_VARCHAR2_TABLE_100,
35 p_query_param_names IN JTF_VARCHAR2_TABLE_100,
36 p_query_param_values IN JTF_VARCHAR2_TABLE_100,
37 p_query_param_types IN JTF_VARCHAR2_TABLE_100,
38 p_commit IN VARCHAR2 DEFAULT 'N'
39 )
40 IS
41
42 l_user_id NUMBER;
43 l_online_config_id NUMBER;
44 l_settle_config_id NUMBER;
45 l_query_config_id NUMBER;
46 l_dummy VARCHAR2(100);
47
48 CURSOR c_user_pf (ci_bep_account_id IBY_FNDCPT_USER_CC_PF_B.BEP_ACCOUNT_ID%TYPE) IS
49 SELECT ONLINE_AUTH_TRANS_CONFIG_ID,
50 SETTLEMENT_TRANS_CONFIG_ID,
51 QUERY_TRANS_CONFIG_ID
52 FROM IBY_FNDCPT_USER_CC_PF_B
53 WHERE BEP_ACCOUNT_ID=ci_bep_account_id;
54
55 CURSOR c_acct_opt_exists(ci_bepid IBY_BEP_ACCT_OPT_VALS.BEPID%TYPE,
56 ci_bep_account_id IBY_BEP_ACCT_OPT_VALS.BEP_ACCOUNT_ID%TYPE)
57 IS SELECT 1
58 FROM IBY_BEP_ACCT_OPT_VALS
59 WHERE BEPID=ci_bepid
60 AND BEP_ACCOUNT_ID=ci_bep_account_id;
61
62 CURSOR c_trans_config_exists(
63 ci_online_trans_config_id IBY_TRANSMIT_VALUES.TRANSMIT_CONFIGURATION_ID%TYPE,
64 ci_settle_trans_config_id IBY_TRANSMIT_VALUES.TRANSMIT_CONFIGURATION_ID%TYPE,
65 ci_query_trans_config_id IBY_TRANSMIT_VALUES.TRANSMIT_CONFIGURATION_ID%TYPE)
66 IS SELECT 1
67 FROM IBY_TRANSMIT_VALUES
68 WHERE TRANSMIT_CONFIGURATION_ID IN (ci_online_trans_config_id,
69 ci_settle_trans_config_id,ci_query_trans_config_id);
70
71
72 BEGIN
73
74 l_user_id :=fnd_global.user_id;
75
76 if l_user_id is null then
77 l_user_id:=1;
78
79 end if;
80
81 -- Check the merchant account exists
82
83 IF (c_acct_opt_exists%ISOPEN) THEN
84 CLOSE c_acct_opt_exists;
85 END IF;
86
87
88 OPEN c_acct_opt_exists(p_bepid, p_bep_account_id);
89
90 FETCH c_acct_opt_exists into l_dummy;
91
92 IF (c_acct_opt_exists%NOTFOUND) THEN
93 -- Not found any merchant account options
94 -- do upgrade
95
96
97 IF (p_merchant_account_names.count<>0) THEN
98
99 -- loop the merchant account
100 FOR i IN p_merchant_account_names.FIRST .. p_merchant_account_names.LAST LOOP
101 -- insert into the merchant account table
102
103
104 IF ((NOT (TRIM(p_merchant_account_names(i)) is null))) THEN
105
106 INSERT INTO IBY_BEP_ACCT_OPT_VALS(
107 BEP_ACCOUNT_ID,
108 BEPID,
109 ACCOUNT_OPTION_CODE,
110 ACCOUNT_OPTION_VALUE,
111 CREATED_BY,
112 CREATION_DATE,
113 LAST_UPDATE_DATE,
114 LAST_UPDATE_LOGIN,
115 LAST_UPDATED_BY,
116 OBJECT_VERSION_NUMBER)
117 VALUES(
118 p_bep_account_id,
119 p_bepid,
120 p_merchant_account_names(i),
121 p_merchant_account_values(i),
122 l_user_id,
123 sysdate,
124 sysdate,
125 l_user_id,
126 l_user_id,
127 1);
128
129 END IF;
130 END LOOP;
131 END IF;
132 END IF;
133
134 CLOSE c_acct_opt_exists;
135
136
137 -- Check the tranmission parameters
138 -- obtain the transmission config ID
139
140 IF (c_user_pf%ISOPEN) THEN
141 CLOSE c_user_pf;
142 END IF;
143
144 OPEN c_user_pf(p_bep_account_id);
145
146 FETCH c_user_pf INTO l_online_config_id,
147 l_settle_config_id,
148 l_query_config_id;
149
150
151 IF (NOT c_user_pf%NOTFOUND) THEN
152 IF (c_trans_config_exists%ISOPEN) THEN
153 CLOSE c_trans_config_exists;
154 END IF;
155
156 OPEN c_trans_config_exists(l_online_config_id,
157 l_settle_config_id,
158 l_query_config_id);
159
160 FETCH c_trans_config_exists into l_dummy;
161
162 IF (c_trans_config_exists%NOTFOUND) THEN
163
164 IF (p_online_param_names.count<>0) THEN
165
166 -- loop the online param
167 FOR i IN p_online_param_names.FIRST .. p_online_param_names.LAST LOOP
168 -- insert into online config table
169 --varchar2 case
170
171 IF(UPPER(TRIM(p_online_param_types(i)))='VARCHAR2') THEN
172 IF ((NOT (TRIM(p_online_param_names(i)) is null))) THEN
173
174
175 INSERT INTO IBY_TRANSMIT_VALUES(
176 TRANSMIT_VALUE_ID,
177 TRANSMIT_CONFIGURATION_ID,
178 TRANSMIT_PARAMETER_CODE,
179 TRANSMIT_VARCHAR2_VALUE,
180 CREATED_BY,
181 CREATION_DATE,
182 LAST_UPDATE_DATE,
183 LAST_UPDATE_LOGIN,
184 LAST_UPDATED_BY,
185 OBJECT_VERSION_NUMBER)
186 VALUES(
187 iby_transmit_values_s.nextval,
188 l_online_config_id,
189 p_online_param_names(i),
190 p_online_param_values(i),
191 l_user_id,
192 sysdate,
193 sysdate,
194 l_user_id,
195 l_user_id,
196 1);
197
198 END IF;
199 END IF;
200 --number case
201
202 IF(UPPER(TRIM(p_online_param_types(i)))='NUMBER') THEN
203 IF ((NOT (TRIM(p_online_param_names(i)) is null))) THEN
204
205
206 INSERT INTO IBY_TRANSMIT_VALUES(
207 TRANSMIT_VALUE_ID,
208 TRANSMIT_CONFIGURATION_ID,
209 TRANSMIT_PARAMETER_CODE,
210 TRANSMIT_NUMBER_VALUE,
211 CREATED_BY,
212 CREATION_DATE,
213 LAST_UPDATE_DATE,
214 LAST_UPDATE_LOGIN,
215 LAST_UPDATED_BY,
216 OBJECT_VERSION_NUMBER)
217 VALUES(
218 iby_transmit_values_s.nextval,
219 l_online_config_id,
220 p_online_param_names(i),
221 TO_NUMBER(p_online_param_values(i)),
222 l_user_id,
223 sysdate,
224 sysdate,
225 l_user_id,
226 l_user_id,
227 1);
228
229 END IF;
230 END IF;
231
232 --Date case
233
234 IF(UPPER(TRIM(p_online_param_types(i)))='DATE') THEN
235 IF ((NOT (TRIM(p_online_param_names(i)) is null))) THEN
236
237 INSERT INTO IBY_TRANSMIT_VALUES(
238 TRANSMIT_VALUE_ID,
239 TRANSMIT_CONFIGURATION_ID,
240 TRANSMIT_PARAMETER_CODE,
241 TRANSMIT_DATE_VALUE,
242 CREATED_BY,
243 CREATION_DATE,
244 LAST_UPDATE_DATE,
245 LAST_UPDATE_LOGIN,
246 LAST_UPDATED_BY,
247 OBJECT_VERSION_NUMBER)
248 VALUES(
249 iby_transmit_values_s.nextval,
250 l_online_config_id,
251 p_online_param_names(i),
252 TO_DATE(p_online_param_values(i), 'YYYY/MM/DD'),
253 l_user_id,
254 sysdate,
255 sysdate,
256 l_user_id,
257 l_user_id,
258 1);
259
260 END IF;
261 END IF;
262
263
264 END LOOP;
265 END IF;
266
267 -- settlement case
268 IF (p_settle_param_names.count<>0) THEN
269
270 -- loop the settle param
271 FOR i IN p_settle_param_names.FIRST .. p_settle_param_names.LAST LOOP
272 -- insert into settle config table
273 --varchar2 case
274
275 IF(UPPER(TRIM(p_settle_param_types(i)))='VARCHAR2') THEN
276 IF ((NOT (TRIM(p_settle_param_names(i)) is null))) THEN
277
278 INSERT INTO IBY_TRANSMIT_VALUES(
279 TRANSMIT_VALUE_ID,
280 TRANSMIT_CONFIGURATION_ID,
281 TRANSMIT_PARAMETER_CODE,
282 TRANSMIT_VARCHAR2_VALUE,
283 CREATED_BY,
284 CREATION_DATE,
285 LAST_UPDATE_DATE,
286 LAST_UPDATE_LOGIN,
287 LAST_UPDATED_BY,
288 OBJECT_VERSION_NUMBER)
289 VALUES(
290 iby_transmit_values_s.nextval,
291 l_settle_config_id,
292 p_settle_param_names(i),
293 p_settle_param_values(i),
294 l_user_id,
295 sysdate,
296 sysdate,
297 l_user_id,
298 l_user_id,
299 1);
300
301 END IF;
302 END IF;
303 --number case
304
305 IF(UPPER(TRIM(p_settle_param_types(i)))='NUMBER') THEN
306 IF ((NOT (TRIM(p_settle_param_names(i)) is null))) THEN
307
308
309 INSERT INTO IBY_TRANSMIT_VALUES(
310 TRANSMIT_VALUE_ID,
311 TRANSMIT_CONFIGURATION_ID,
312 TRANSMIT_PARAMETER_CODE,
313 TRANSMIT_NUMBER_VALUE,
314 CREATED_BY,
315 CREATION_DATE,
316 LAST_UPDATE_DATE,
317 LAST_UPDATE_LOGIN,
318 LAST_UPDATED_BY,
319 OBJECT_VERSION_NUMBER)
320 VALUES(
321 iby_transmit_values_s.nextval,
322 l_settle_config_id,
323 p_settle_param_names(i),
324 p_settle_param_values(i),
325 l_user_id,
326 sysdate,
327 sysdate,
328 l_user_id,
329 l_user_id,
330 1);
331
332 END IF;
333 END IF;
334
335 --Date case
336
337 IF(UPPER(TRIM(p_settle_param_types(i)))='DATE') THEN
338 IF ((NOT (TRIM(p_settle_param_names(i)) is null))) THEN
339
340 INSERT INTO IBY_TRANSMIT_VALUES(
341 TRANSMIT_VALUE_ID,
342 TRANSMIT_CONFIGURATION_ID,
343 TRANSMIT_PARAMETER_CODE,
344 TRANSMIT_DATE_VALUE,
348 LAST_UPDATE_LOGIN,
345 CREATED_BY,
346 CREATION_DATE,
347 LAST_UPDATE_DATE,
349 LAST_UPDATED_BY,
350 OBJECT_VERSION_NUMBER)
351 VALUES(
352 iby_transmit_values_s.nextval,
353 l_settle_config_id,
354 p_settle_param_names(i),
355 TO_DATE(p_settle_param_values(i), 'YYYY/MM/DD'),
356 l_user_id,
357 sysdate,
358 sysdate,
359 l_user_id,
360 l_user_id,
361 1);
362
363 END IF;
364 END IF;
365
366
367 END LOOP;
368 END IF;
369
370 -- end of settlement case
371
372 -- query case
373
374 IF (p_query_param_names.count<>0) THEN
375
376 -- loop the query param
377 FOR i IN p_query_param_names.FIRST .. p_query_param_names.LAST LOOP
378 -- insert into query config table
379 --varchar2 case
380
381 IF(UPPER(TRIM(p_query_param_types(i)))='VARCHAR2') THEN
382 IF ((NOT (TRIM(p_query_param_names(i)) is null))) THEN
383
384 INSERT INTO IBY_TRANSMIT_VALUES(
385 TRANSMIT_VALUE_ID,
386 TRANSMIT_CONFIGURATION_ID,
387 TRANSMIT_PARAMETER_CODE,
388 TRANSMIT_VARCHAR2_VALUE,
389 CREATED_BY,
390 CREATION_DATE,
391 LAST_UPDATE_DATE,
392 LAST_UPDATE_LOGIN,
393 LAST_UPDATED_BY,
394 OBJECT_VERSION_NUMBER)
395 VALUES(
396 iby_transmit_values_s.nextval,
397 l_query_config_id,
398 p_query_param_names(i),
399 p_query_param_values(i),
400 l_user_id,
401 sysdate,
402 sysdate,
403 l_user_id,
404 l_user_id,
405 1);
406
407 END IF;
408 END IF;
409 --number case
410
411 IF(UPPER(TRIM(p_query_param_types(i)))='NUMBER') THEN
412 IF ((NOT (TRIM(p_query_param_names(i)) is null))) THEN
413
414 INSERT INTO IBY_TRANSMIT_VALUES(
415 TRANSMIT_VALUE_ID,
416 TRANSMIT_CONFIGURATION_ID,
417 TRANSMIT_PARAMETER_CODE,
418 TRANSMIT_NUMBER_VALUE,
419 CREATED_BY,
420 CREATION_DATE,
421 LAST_UPDATE_DATE,
422 LAST_UPDATE_LOGIN,
423 LAST_UPDATED_BY,
424 OBJECT_VERSION_NUMBER)
425 VALUES(
426 iby_transmit_values_s.nextval,
427 l_query_config_id,
428 p_query_param_names(i),
429 p_query_param_values(i),
430 l_user_id,
431 sysdate,
432 sysdate,
433 l_user_id,
434 l_user_id,
435 1);
436
437 END IF;
438 END IF;
439
440 --Date case
441
442 IF(UPPER(TRIM(p_query_param_types(i)))='DATE') THEN
443 IF ((NOT (TRIM(p_query_param_names(i)) is null))) THEN
444
445 INSERT INTO IBY_TRANSMIT_VALUES(
446 TRANSMIT_VALUE_ID,
447 TRANSMIT_CONFIGURATION_ID,
448 TRANSMIT_PARAMETER_CODE,
449 TRANSMIT_DATE_VALUE,
450 CREATED_BY,
451 CREATION_DATE,
452 LAST_UPDATE_DATE,
453 LAST_UPDATE_LOGIN,
454 LAST_UPDATED_BY,
455 OBJECT_VERSION_NUMBER)
456 VALUES(
457 iby_transmit_values_s.nextval,
458 l_query_config_id,
459 p_query_param_names(i),
460 TO_DATE(p_query_param_values(i), 'YYYY/MM/DD'),
461 l_user_id,
462 sysdate,
463 sysdate,
464 l_user_id,
465 l_user_id,
466 1);
467
468 END IF;
469 END IF;
470
471
472 END LOOP;
473 END IF;
474
475 -- end of query case
476
477 END IF;
478 CLOSE c_trans_config_exists;
479
480 END IF;
481
482 CLOSE c_user_pf;
483 commit;
484
485
486 END;
487
488 END IBY_UPGRADE_PARAM_PVT;