[Home] [Help]
PACKAGE BODY: APPS.IBY_FNDCPT_PROFILE_PVT
Source
4 PROCEDURE Insert_Trans_Config
1 PACKAGE BODY IBY_FNDCPT_PROFILE_PVT AS
2 /* $Header: ibyfcpfb.pls 120.0 2005/05/03 22:41:51 jleybovi noship $ */
3
5 (
6 p_profile_name IN VARCHAR2,
7 p_protocol_code IN iby_transmit_configs_vl.transmit_protocol_code%TYPE,
8 x_config_id OUT NOCOPY iby_transmit_configs_b.transmit_configuration_id%TYPE
9 )
10 IS
11 l_rowid ROWID;
12 BEGIN
13
14 SELECT iby_transmit_configs_s.nextval
15 INTO x_config_id
16 FROM dual;
17
18 iby_pp_mlsutl_pvt.trans_config_insert_row
19 (
20 X_ROWID => l_rowid,
21 X_TRANSMIT_CONFIGURATION_ID => x_config_id,
22 X_OBJECT_VERSION_NUMBER => 1,
23 X_TUNNELING_TRANS_CONFIG_ID => null,
24 X_TRANSMIT_PROTOCOL_CODE => p_protocol_code,
25 X_INACTIVE_DATE => null,
26 X_TRANSMIT_CONFIGURATION_NAME => p_profile_name || ' ' || p_protocol_code,
27 X_CREATION_DATE => sysdate,
28 X_CREATED_BY => fnd_global.user_id,
29 X_LAST_UPDATE_DATE => sysdate,
30 X_LAST_UPDATED_BY => fnd_global.user_id,
31 X_LAST_UPDATE_LOGIN => fnd_global.login_id
32 );
33 END Insert_Trans_Config;
34
35 PROCEDURE Delete_Trans_Config
36 (p_config_id IN iby_transmit_configs_b.transmit_configuration_id%TYPE)
37 IS
38 BEGIN
39
40 IF (p_config_id IS NULL) THEN
41 RETURN;
42 END IF;
43
44 DELETE
45 FROM iby_transmit_values
46 WHERE (transmit_configuration_id=p_config_id);
47
48 iby_pp_mlsutl_pvt.trans_config_delete_row(p_config_id);
49
50 END Delete_Trans_Config;
51
52 PROCEDURE Get_Profile_Code
53 (
54 p_bepid IN iby_bepinfo.bepid%TYPE,
55 p_payeeid IN iby_bepkeys.ownerid%TYPE,
56 p_bepkey IN iby_bepkeys.key%TYPE,
57 p_instr_type IN VARCHAR2,
58 x_profile_code OUT NOCOPY VARCHAR2
59 )
60 IS
61
62 BEGIN
63
64 x_profile_code := NULL;
65
66 IF (p_instr_type in ( 'CREDITCARD', 'PURCHASECARD')) THEN
67
68 SELECT user_cc_profile_code
69 INTO x_profile_code
70 FROM iby_fndcpt_user_cc_pf_b prof, iby_bepkeys key
71 WHERE (prof.bep_account_id = key.bep_account_id)
72 AND (key.bepid = p_bepid)
73 AND (key.ownertype = 'PAYEE')
74 AND (key.ownerid = p_payeeid)
75 AND (key.key = p_bepkey);
76
77 ELSIF (p_instr_type = 'BANKACCOUNT') THEN
78
79 SELECT user_eft_profile_code
80 INTO x_profile_code
81 FROM iby_fndcpt_user_eft_pf_b prof, iby_bepkeys key
82 WHERE (prof.bep_account_id = key.bep_account_id)
83 AND (key.bepid = p_bepid)
84 AND (key.ownertype = 'PAYEE')
85 AND (key.ownerid = p_payeeid)
86 AND (key.key = p_bepkey);
87
88 ELSIF (p_instr_type = 'PINLESSDEBITCARD') THEN
89
90 SELECT user_dc_profile_code
91 INTO x_profile_code
92 FROM iby_fndcpt_user_dc_pf_b prof, iby_bepkeys key
93 WHERE (prof.bep_account_id = key.bep_account_id)
94 AND (key.bepid = p_bepid)
95 AND (key.ownertype = 'PAYEE')
96 AND (key.ownerid = p_payeeid)
97 AND (key.key = p_bepkey);
98
99 END IF;
100
101 EXCEPTION
102 WHEN NO_DATA_FOUND THEN
103 x_profile_code := NULL;
104 END Get_Profile_Code;
105
106 PROCEDURE Create_User_Profiles
107 (
108 p_bepid IN iby_bepinfo.bepid%TYPE,
109 p_bep_acct_id IN iby_bepkeys.bep_account_id%TYPE,
110 p_cc_profile_code IN iby_fndcpt_user_cc_pf_vl.user_cc_profile_code%TYPE,
111 p_cc_profile_name IN iby_fndcpt_user_cc_pf_vl.user_cc_profile_name%TYPE,
112 p_eft_profile_code IN iby_fndcpt_user_eft_pf_vl.user_eft_profile_code%TYPE,
113 p_eft_profile_name IN iby_fndcpt_user_eft_pf_vl.user_eft_profile_name%TYPE,
114 p_dc_profile_code IN iby_fndcpt_user_dc_pf_vl.user_dc_profile_code%TYPE,
115 p_dc_profile_name IN iby_fndcpt_user_dc_pf_vl.user_dc_profile_name%TYPE,
116 x_cc_online_cfg_id OUT NOCOPY
117 iby_fndcpt_user_cc_pf_vl.online_auth_trans_config_id%TYPE,
118 x_cc_settle_cfg_id OUT NOCOPY
119 iby_fndcpt_user_cc_pf_vl.settlement_trans_config_id%TYPE,
120 x_cc_query_cfg_id OUT NOCOPY
121 iby_fndcpt_user_cc_pf_vl.query_trans_config_id%TYPE,
122 x_eft_verify_cfg_id OUT NOCOPY
123 iby_fndcpt_user_eft_pf_vl.verify_trans_config_id%TYPE,
124 x_eft_xfer_cfg_id OUT NOCOPY
125 iby_fndcpt_user_eft_pf_vl.funds_xfer_trans_config_id%TYPE,
126 x_eft_query_cfg_id OUT NOCOPY
127 iby_fndcpt_user_eft_pf_vl.query_trans_config_id%TYPE,
128 x_dc_online_cfg_id OUT NOCOPY
129 iby_fndcpt_user_dc_pf_vl.online_deb_trans_config_id%TYPE,
130 x_dc_settle_cfg_id OUT NOCOPY
131 iby_fndcpt_user_dc_pf_vl.settlement_trans_config_id%TYPE,
132 x_dc_query_cfg_id OUT NOCOPY
133 iby_fndcpt_user_dc_pf_vl.query_trans_config_id%TYPE
134 )
135 IS
136 l_rowid ROWID;
137
138 l_profile_code VARCHAR2(30);
139 l_online_trans_code VARCHAR2(30);
140 l_settle_trans_code VARCHAR2(30);
141 l_query_trans_code VARCHAR2(30);
142
143 l_online_trans_cfg_id iby_transmit_configs_b.transmit_configuration_id%TYPE;
144 l_settle_trans_cfg_id iby_transmit_configs_b.transmit_configuration_id%TYPE;
145 l_query_trans_cfg_id iby_transmit_configs_b.transmit_configuration_id%TYPE;
146
147 CURSOR c_sys_cc_profile
148 (ci_bepid iby_bepinfo.bepid%TYPE)
149 IS
150 SELECT sys_cc_profile_code,online_auth_trans_prtcl_code,
151 settlement_trans_prtcl_code,query_trans_prtcl_code
152 FROM iby_fndcpt_sys_cc_pf_b profs
153 WHERE profs.payment_system_id = ci_bepid
154 AND NVL(inactive_date,SYSDATE+30)>SYSDATE;
155
156 CURSOR c_sys_eft_profile
157 (ci_bepid iby_bepinfo.bepid%TYPE)
158 IS
159 SELECT sys_eft_profile_code,verify_trans_prtcl_code,
160 funds_xfer_trans_prtcl_code,query_trans_prtcl_code
161 FROM iby_fndcpt_sys_eft_pf_b profs
162 WHERE profs.payment_system_id = ci_bepid
163 AND NVL(inactive_date,SYSDATE+30)>SYSDATE;
164
165 CURSOR c_sys_dc_profile
166 (ci_bepid iby_bepinfo.bepid%TYPE)
167 IS
168 SELECT sys_dc_profile_code,online_deb_trans_prtcl_code,
169 settlement_trans_prtcl_code,query_trans_prtcl_code
170 FROM iby_fndcpt_sys_dc_pf_b profs
171 WHERE profs.payment_system_id = ci_bepid
172 AND NVL(inactive_date,SYSDATE+30)>SYSDATE;
173
174 BEGIN
175
176 IF (c_sys_cc_profile%ISOPEN) THEN
177 CLOSE c_sys_cc_profile;
178 END IF;
179 IF (c_sys_eft_profile%ISOPEN) THEN
180 CLOSE c_sys_eft_profile;
181 END IF;
182 IF (c_sys_dc_profile%ISOPEN) THEN
183 CLOSE c_sys_dc_profile;
184 END IF;
185
186 -- cursors for system profiles assume there is only a single
187 -- profile per payment system
188
189 -- create credit card user profiles for the payment system
190 OPEN c_sys_cc_profile(p_bepid);
191 FETCH c_sys_cc_profile INTO l_profile_code,l_online_trans_code,
192 l_settle_trans_code,l_query_trans_code;
193 IF (NOT c_sys_cc_profile%NOTFOUND) THEN
194 IF (NOT l_online_trans_code IS NULL) THEN
195 Insert_Trans_Config(p_cc_profile_name,l_online_trans_code,
196 l_online_trans_cfg_id);
197 END IF;
198 IF (NOT l_settle_trans_code IS NULL) THEN
199 Insert_Trans_Config(p_cc_profile_name,l_settle_trans_code,
200 l_settle_trans_cfg_id);
201 END IF;
202 IF (NOT l_query_trans_code IS NULL) THEN
203 Insert_Trans_Config(p_cc_profile_name,l_query_trans_code,
204 l_query_trans_cfg_id);
205 END IF;
206
207 iby_fndcpt_mlsutl_pvt.user_cc_prof_insert_row
208 (
209 X_ROWID => l_rowid,
210 X_USER_CC_PROFILE_CODE => p_cc_profile_code,
211 X_OBJECT_VERSION_NUMBER => 1,
212 X_BEP_ACCOUNT_ID => p_bep_acct_id,
213 X_SYS_CC_PROFILE_CODE => l_profile_code,
214 X_INACTIVE_DATE => null,
215 X_QUERY_TRANS_CONFIG_ID => l_query_trans_cfg_id,
216 X_ONLINE_AUTH_TRANS_CONFIG_ID => l_online_trans_cfg_id,
217 X_SETTLEMENT_TRANS_CONFIG_ID => l_settle_trans_cfg_id,
218 X_USER_CC_PROFILE_NAME => p_cc_profile_name,
219 X_CREATION_DATE => sysdate,
220 X_CREATED_BY => fnd_global.user_id,
221 X_LAST_UPDATE_DATE => sysdate,
222 X_LAST_UPDATED_BY => fnd_global.user_id,
223 X_LAST_UPDATE_LOGIN => fnd_global.login_id
224 );
225
226 END IF;
227 CLOSE c_sys_cc_profile;
228
229 -- create eft user profiles for the payment system
230 OPEN c_sys_eft_profile(p_bepid);
231 FETCH c_sys_eft_profile INTO l_profile_code,l_online_trans_code,
232 l_settle_trans_code,l_query_trans_code;
233 IF (NOT c_sys_eft_profile%NOTFOUND) THEN
234 IF (NOT l_online_trans_code IS NULL) THEN
235 Insert_Trans_Config(p_eft_profile_name,l_online_trans_code,
236 l_online_trans_cfg_id);
237 END IF;
238 IF (NOT l_settle_trans_code IS NULL) THEN
239 Insert_Trans_Config(p_eft_profile_name,l_settle_trans_code,
240 l_settle_trans_cfg_id);
241 END IF;
242 IF (NOT l_query_trans_code IS NULL) THEN
243 Insert_Trans_Config(p_eft_profile_name,l_query_trans_code,
244 l_query_trans_cfg_id);
245 END IF;
246
247 iby_fndcpt_mlsutl_pvt.user_eft_prof_insert_row
248 (
249 X_ROWID => l_rowid,
250 X_USER_EFT_PROFILE_CODE => p_eft_profile_code,
251 X_OBJECT_VERSION_NUMBER => 1,
252 X_BEP_ACCOUNT_ID => p_bep_acct_id,
253 X_SYS_EFT_PROFILE_CODE => l_profile_code,
254 X_INACTIVE_DATE => null,
255 X_QUERY_TRANS_CONFIG_ID => l_query_trans_cfg_id,
256 X_VERIFY_TRANS_CONFIG_ID => l_online_trans_cfg_id,
257 X_FUNDS_XFER_TRANS_CONFIG_ID => l_settle_trans_cfg_id,
258 X_USER_EFT_PROFILE_NAME => p_eft_profile_name,
259 X_CREATION_DATE => sysdate,
260 X_CREATED_BY => fnd_global.user_id,
261 X_LAST_UPDATE_DATE => sysdate,
262 X_LAST_UPDATED_BY => fnd_global.user_id,
263 X_LAST_UPDATE_LOGIN => fnd_global.login_id
264 );
265
266 END IF;
267 CLOSE c_sys_eft_profile;
268
269 -- create debit card user profiles for the payment system
270 OPEN c_sys_dc_profile(p_bepid);
271 FETCH c_sys_dc_profile INTO l_profile_code,l_online_trans_code,
272 l_settle_trans_code,l_query_trans_code;
273 IF (NOT c_sys_dc_profile%NOTFOUND) THEN
274 IF (NOT l_online_trans_code IS NULL) THEN
275 Insert_Trans_Config(p_dc_profile_name,l_online_trans_code,
276 l_online_trans_cfg_id);
277 END IF;
278 IF (NOT l_settle_trans_code IS NULL) THEN
279 Insert_Trans_Config(p_dc_profile_name,l_settle_trans_code,
280 l_settle_trans_cfg_id);
281 END IF;
282 IF (NOT l_query_trans_code IS NULL) THEN
283 Insert_Trans_Config(p_dc_profile_name,l_query_trans_code,
284 l_query_trans_cfg_id);
285 END IF;
286
287 iby_fndcpt_mlsutl_pvt.user_dc_prof_insert_row
288 (
289 X_ROWID => l_rowid,
290 X_USER_DC_PROFILE_CODE => p_dc_profile_code,
291 X_OBJECT_VERSION_NUMBER => 1,
292 X_BEP_ACCOUNT_ID => p_bep_acct_id,
293 X_SYS_DC_PROFILE_CODE => l_profile_code,
294 X_INACTIVE_DATE => null,
295 X_QUERY_TRANS_CONFIG_ID => l_query_trans_cfg_id,
296 X_ONLINE_DEB_TRANS_CONFIG_ID => l_online_trans_cfg_id,
297 X_SETTLEMENT_TRANS_CONFIG_ID => l_settle_trans_cfg_id,
298 X_USER_DC_PROFILE_NAME => p_dc_profile_name,
299 X_CREATION_DATE => sysdate,
300 X_CREATED_BY => fnd_global.user_id,
301 X_LAST_UPDATE_DATE => sysdate,
302 X_LAST_UPDATED_BY => fnd_global.user_id,
303 X_LAST_UPDATE_LOGIN => fnd_global.login_id
304 );
305
306 END IF;
307 CLOSE c_sys_dc_profile;
308
309 COMMIT;
310 END Create_User_Profiles;
311
312 PROCEDURE Delete_User_Profiles
313 (
314 p_commit IN VARCHAR2,
315 p_cc_profile_code IN iby_fndcpt_user_cc_pf_vl.user_cc_profile_code%TYPE,
316 p_eft_profile_code IN iby_fndcpt_user_eft_pf_vl.user_eft_profile_code%TYPE,
317 p_dc_profile_code IN iby_fndcpt_user_dc_pf_vl.user_dc_profile_code%TYPE
318 )
319 IS
320 l_online_cfg_id iby_transmit_configs_b.transmit_configuration_id%TYPE;
321 l_settle_cfg_id iby_transmit_configs_b.transmit_configuration_id%TYPE;
322 l_query_cfg_id iby_transmit_configs_b.transmit_configuration_id%TYPE;
323
324 CURSOR c_cc_user_prof
325 (ci_profile_code iby_fndcpt_user_cc_pf_b.user_cc_profile_code%TYPE)
326 IS
327 SELECT online_auth_trans_config_id, settlement_trans_config_id,
328 query_trans_config_id
329 FROM iby_fndcpt_user_cc_pf_b
330 WHERE (user_cc_profile_code=ci_profile_code);
331
332 CURSOR c_eft_user_prof
333 (ci_profile_code iby_fndcpt_user_eft_pf_b.user_eft_profile_code%TYPE)
334 IS
335 SELECT verify_trans_config_id, funds_xfer_trans_config_id,
336 query_trans_config_id
337 FROM iby_fndcpt_user_eft_pf_b
338 WHERE (user_eft_profile_code=ci_profile_code);
339
340 CURSOR c_dc_user_prof
341 (ci_profile_code iby_fndcpt_user_dc_pf_b.user_dc_profile_code%TYPE)
342 IS
343 SELECT online_deb_trans_config_id, settlement_trans_config_id,
344 query_trans_config_id
345 FROM iby_fndcpt_user_dc_pf_b
346 WHERE (user_dc_profile_code=ci_profile_code);
347
348 BEGIN
349
350 IF (c_cc_user_prof%ISOPEN) THEN
351 CLOSE c_cc_user_prof;
352 END IF;
353 IF (c_eft_user_prof%ISOPEN) THEN
354 CLOSE c_eft_user_prof;
355 END IF;
356 IF (c_dc_user_prof%ISOPEN) THEN
357 CLOSE c_dc_user_prof;
358 END IF;
359
360 OPEN c_cc_user_prof(p_cc_profile_code);
361 FETCH c_cc_user_prof INTO
362 l_online_cfg_id, l_settle_cfg_id, l_query_cfg_id;
363 IF (NOT c_cc_user_prof%NOTFOUND) THEN
364 Delete_Trans_Config(l_online_cfg_id);
365 Delete_Trans_Config(l_settle_cfg_id);
366 Delete_Trans_Config(l_query_cfg_id);
367
368 DELETE
369 FROM iby_fndcpt_user_cc_pf_b
370 WHERE (user_cc_profile_code=p_cc_profile_code);
371 END IF;
372 CLOSE c_cc_user_prof;
373
374 OPEN c_eft_user_prof(p_eft_profile_code);
375 FETCH c_eft_user_prof INTO
376 l_online_cfg_id, l_settle_cfg_id, l_query_cfg_id;
377 IF (NOT c_eft_user_prof%NOTFOUND) THEN
378 Delete_Trans_Config(l_online_cfg_id);
379 Delete_Trans_Config(l_settle_cfg_id);
380 Delete_Trans_Config(l_query_cfg_id);
381
382 DELETE
383 FROM iby_fndcpt_user_eft_pf_b
384 WHERE (user_eft_profile_code=p_eft_profile_code);
385 END IF;
386 CLOSE c_eft_user_prof;
387
388 OPEN c_dc_user_prof(p_dc_profile_code);
389 FETCH c_dc_user_prof INTO
390 l_online_cfg_id, l_settle_cfg_id, l_query_cfg_id;
391 IF (NOT c_dc_user_prof%NOTFOUND) THEN
392 Delete_Trans_Config(l_online_cfg_id);
393 Delete_Trans_Config(l_settle_cfg_id);
394 Delete_Trans_Config(l_query_cfg_id);
395
396 DELETE
397 FROM iby_fndcpt_user_dc_pf_b
398 WHERE (user_dc_profile_code=p_dc_profile_code);
399 END IF;
400 CLOSE c_dc_user_prof;
401
402 COMMIT;
403
404 END Delete_User_Profiles;
405
406 END IBY_FNDCPT_PROFILE_PVT;