[Home] [Help]
PACKAGE BODY: APPS.IBY_TUNNEL_CONFIG_UPG_PKG
Source
1 PACKAGE BODY IBY_TUNNEL_CONFIG_UPG_PKG AS
2 /* $Header: ibytunlb.pls 120.3 2006/09/08 22:23:47 jleybovi noship $ */
3
4 PROCEDURE CREATE_TUNNELING_CONFIGS
5 IS
6 lx_rowid ROWID;
7 l_config_id NUMBER;
8 l_config_name VARCHAR(100);
9 l_tunnel_name VARCHAR(100);
10 l_url VARCHAR(1024);
11 l_update_count NUMBER;
12
13 CURSOR c_bep IS
14 SELECT bepid, suffix, name, baseurl
15 FROM iby_bepinfo
16 WHERE (NOT baseurl IS NULL)
17 ORDER BY last_update_date DESC;
18
19 BEGIN
20 IF (c_bep%ISOPEN) THEN CLOSE c_bep; END IF;
21
22 SELECT transmit_protocol_name
23 INTO l_tunnel_name
24 FROM iby_transmit_protocols_vl
25 WHERE transmit_protocol_code='IBY_DELIVERY_ENVELOPE';
26
27 FOR bep_rec IN c_bep LOOP
28
29 SELECT iby_transmit_configs_s.nextval INTO l_config_id FROM DUAL;
30
31 -- cc profiles
32 UPDATE iby_transmit_configs_b
33 SET tunneling_trans_config_id=l_config_id
34 WHERE transmit_configuration_id IN
35 (SELECT online_auth_trans_config_id
36 FROM iby_fndcpt_user_cc_pf_b p, iby_bepkeys k
37 WHERE (p.bep_account_id = k.bep_account_id)
38 AND (k.bepid = bep_rec.bepid)
39 )
40 AND (tunneling_trans_config_id IS NULL);
41 l_update_count := SQL%ROWCOUNT;
42 UPDATE iby_transmit_configs_b
43 SET tunneling_trans_config_id=l_config_id
44 WHERE transmit_configuration_id IN
45 (SELECT settlement_trans_config_id
46 FROM iby_fndcpt_user_cc_pf_b p, iby_bepkeys k
47 WHERE (p.bep_account_id = k.bep_account_id)
48 AND (k.bepid = bep_rec.bepid)
49 )
50 AND (tunneling_trans_config_id IS NULL);
51 l_update_count := l_update_count + SQL%ROWCOUNT;
52 UPDATE iby_transmit_configs_b
53 SET tunneling_trans_config_id=l_config_id
54 WHERE transmit_configuration_id IN
55 (SELECT query_trans_config_id
56 FROM iby_fndcpt_user_cc_pf_b p, iby_bepkeys k
57 WHERE (p.bep_account_id = k.bep_account_id)
58 AND (k.bepid = bep_rec.bepid)
59 )
60 AND (tunneling_trans_config_id IS NULL);
61 l_update_count := l_update_count + SQL%ROWCOUNT;
62
63 -- debit card profiles
64 UPDATE iby_transmit_configs_b
65 SET tunneling_trans_config_id=l_config_id
66 WHERE transmit_configuration_id IN
67 (SELECT online_deb_trans_config_id
68 FROM iby_fndcpt_user_dc_pf_b p, iby_bepkeys k
69 WHERE (p.bep_account_id = k.bep_account_id)
70 AND (k.bepid = bep_rec.bepid)
71 )
72 AND (tunneling_trans_config_id IS NULL);
73 l_update_count := l_update_count + SQL%ROWCOUNT;
74 UPDATE iby_transmit_configs_b
75 SET tunneling_trans_config_id=l_config_id
76 WHERE transmit_configuration_id IN
77 (SELECT settlement_trans_config_id
78 FROM iby_fndcpt_user_dc_pf_b p, iby_bepkeys k
79 WHERE (p.bep_account_id = k.bep_account_id)
80 AND (k.bepid = bep_rec.bepid)
81 )
82 AND (tunneling_trans_config_id IS NULL);
83 l_update_count := l_update_count + SQL%ROWCOUNT;
84 UPDATE iby_transmit_configs_b
85 SET tunneling_trans_config_id=l_config_id
86 WHERE transmit_configuration_id IN
87 (SELECT query_trans_config_id
88 FROM iby_fndcpt_user_dc_pf_b p, iby_bepkeys k
89 WHERE (p.bep_account_id = k.bep_account_id)
90 AND (k.bepid = bep_rec.bepid)
91 )
92 AND (tunneling_trans_config_id IS NULL);
93 l_update_count := l_update_count + SQL%ROWCOUNT;
94
95 -- eft profiles
96 UPDATE iby_transmit_configs_b
97 SET tunneling_trans_config_id=l_config_id
98 WHERE transmit_configuration_id IN
99 (SELECT verify_trans_config_id
100 FROM iby_fndcpt_user_eft_pf_b p, iby_bepkeys k
101 WHERE (p.bep_account_id = k.bep_account_id)
102 AND (k.bepid = bep_rec.bepid)
103 )
104 AND (tunneling_trans_config_id IS NULL);
105 l_update_count := l_update_count + SQL%ROWCOUNT;
106 UPDATE iby_transmit_configs_b
107 SET tunneling_trans_config_id=l_config_id
108 WHERE transmit_configuration_id IN
109 (SELECT funds_xfer_trans_config_id
110 FROM iby_fndcpt_user_eft_pf_b p, iby_bepkeys k
111 WHERE (p.bep_account_id = k.bep_account_id)
112 AND (k.bepid = bep_rec.bepid)
113 )
114 AND (tunneling_trans_config_id IS NULL);
115 l_update_count := l_update_count + SQL%ROWCOUNT;
116 UPDATE iby_transmit_configs_b
117 SET tunneling_trans_config_id=l_config_id
118 WHERE transmit_configuration_id IN
119 (SELECT query_trans_config_id
120 FROM iby_fndcpt_user_eft_pf_b p, iby_bepkeys k
121 WHERE (p.bep_account_id = k.bep_account_id)
122 AND (k.bepid = bep_rec.bepid)
123 )
124 AND (tunneling_trans_config_id IS NULL);
125 l_update_count := l_update_count + SQL%ROWCOUNT;
126
127 IF (l_update_count>0) THEN
128 l_config_name := bep_rec.name || ' ' || l_tunnel_name;
129 IF (SUBSTR(bep_rec.baseurl,-1) = '/') THEN
130 l_url := bep_rec.baseurl || 'oramipp_' || bep_rec.suffix;
131 ELSIF (SUBSTR(bep_rec.baseurl,-11,7) = 'oramipp') THEN
132 l_url := bep_rec.baseurl;
133 ELSE
134 l_url := bep_rec.baseurl || '/oramipp_' || bep_rec.suffix;
135 END IF;
136
137 -- new servlet mount-point in R12 techstack
138 l_url := REPLACE(l_url,'oa_servlets','OA_HTML');
139
140 IBY_PP_MLSUTL_PVT.TRANS_CONFIG_INSERT_ROW
141 (lx_rowid,l_config_id,1,null,'IBY_DELIVERY_ENVELOPE',
142 null,l_config_name,sysdate,fnd_global.user_id,sysdate,
143 fnd_global.user_id,fnd_global.login_id);
144
145 INSERT INTO iby_transmit_values
146 (transmit_value_id, transmit_configuration_id, transmit_parameter_code,
147 transmit_varchar2_value, transmit_number_value, transmit_date_value,
148 created_by, creation_date, last_updated_by, last_update_date,
149 last_update_login, object_version_number)
150 VALUES
151 (iby_transmit_values_s.nextval, l_config_id, 'WEB_URL',
152 l_url, null, null,
153 fnd_global.user_id, sysdate, fnd_global.user_id, sysdate,
154 fnd_global.login_id, 1);
155 END IF;
156
157 END LOOP;
158
159 COMMIT;
160 END CREATE_TUNNELING_CONFIGS;
161
162 END IBY_TUNNEL_CONFIG_UPG_PKG;
163