1 package body iby_routinginfo_pkg as
2 /*$Header: ibyroutb.pls 115.24 2002/05/17 12:53:32 pkm ship $*/
3
4 /*-------------------------------------------------------------------+
5 | Function: createRoutingInfo. |
6 | Purpose: To create a Routing information in the database. |
7 +-------------------------------------------------------------------*/
8 procedure createRoutingInfo(i_rules in t_rulesRec,
9 i_conditions in t_condtRecVec)
10 is
11 l_count int :=0;
12 l_cntBep int :=0;
13 l_ruleId iby_routinginfo.PAYMENTMETHODID%type;
14 CURSOR c_routingInfo IS
15 SELECT count(1)
16 FROM iby_routinginfo a
17 WHERE
18 i_rules.ruleName=a.paymentmethodName ;
19 BEGIN
20
21 IF c_routingInfo%ISOPEN
22 THEN
23 CLOSE c_routingInfo;
24 OPEN c_routingInfo;
25 ELSE
26 OPEN c_routingInfo;
27 END IF;
28
29 -- Check whether this method name is already being used
30 -- if not create a new row.
31 FETCH c_routingInfo INTO l_count;
32 CLOSE c_routingInfo;
33
34 SELECT COUNT(*) into l_cntBep
35 FROM iby_bepinfo
36 WHERE BEPID = i_rules.bepId;
37 IF ( l_cntBep = 0 ) THEN
38 raise_application_error(-20000, 'IBY_204558#', FALSE);
39 END IF;
40
41 IF (isDuplicateCondNames(i_conditions) = true) THEN
42 raise_application_error(-20000, 'IBY_204589#', FALSE);
43 END IF;
44
45 IF ( l_count = 0 )
46 THEN
47 select IBY_PMTMETHOD_S.NextVal INTO l_ruleId from dual;
48 INSERT INTO iby_routinginfo
49 (payeeid, bepkey, bepid, paymentmethodid, paymentmethodName, instr_type,
50 configured, priority, last_update_date, last_updated_by, creation_date,
51 created_by, last_update_login, hitcounter, object_version_number)
52 VALUES ( i_rules.payeeId, i_rules.merchantAccount, i_rules.bepId, l_ruleId,
53 i_rules.ruleName, i_rules.bepInstrType, i_rules.activeStatus,
54 i_rules.priority, sysdate, fnd_global.user_id, sysdate,
55 fnd_global.user_id, fnd_global.login_id, i_rules.hitcounter, 1);
56
57 FOR v_count in 1..i_conditions.COUNT LOOP
58 INSERT INTO iby_pmtmthd_conditions
59 (paymentmethodid, parameter_code, operation_code, value,
60 is_value_string, entry_sequence, condition_name, last_update_date,
61 last_updated_by, creation_date, created_by, last_update_login,
62 object_version_number)
63 VALUES ( l_ruleId, i_conditions(v_count).parameter,
64 i_conditions(v_count).operation, i_conditions(v_count).value,
65 i_conditions(v_count).is_value_string,
66 i_conditions(v_count).entry_seq,
67 i_conditions(v_count).condition_name, sysdate, fnd_global.user_id,
68 sysdate, fnd_global.user_id, fnd_global.login_id, 1);
69 END LOOP;
70 ELSE
71 raise_application_error(-20000, 'IBY_204559#', FALSE);
72 --raise_application_error(-20520,
73 --'Payment Method Name already in use. Use unique payment method name.',
74 --FALSE);
75 END IF;
76
77 COMMIT;
78
79 END;
80
81
82 /*
83 ** Function: modifyRoutingInfo.
84 ** Purpose: modifies the Routing information in the database.
85 */
86 procedure modifyRoutingInfo(i_rules in t_rulesRec,
87 i_conditions in t_condtRecVec)
88 is
89 l_ruleId iby_routinginfo.PAYMENTMETHODID%type;
90
91 CURSOR c_routingInfo IS
92 SELECT *
93 FROM iby_routinginfo a
94 WHERE i_rules.object_version = a.object_version_number AND
95 i_rules.ruleName=a.paymentmethodName AND
96 i_rules.ruleId = a.paymentmethodId
97 FOR UPDATE ;
98 BEGIN
99
100 IF c_routingInfo%ISOPEN
101 THEN
102 CLOSE c_routingInfo;
103 OPEN c_routingInfo;
104 ELSE
105 OPEN c_routingInfo;
106 END IF;
107
108 IF c_routingInfo%NOTFOUND
109 THEN
110 CLOSE c_routingInfo;
111 raise_application_error(-20000, 'IBY_204560#', FALSE);
112 --raise_application_error(-20520,
113 --'Rule condition has been changed. Modify Failed.', FALSE);
114 END IF;
115
116 CLOSE c_routingInfo;
117
118 IF (isDuplicateCondNames(i_conditions) = true) THEN
119 raise_application_error(-20000, 'IBY_204589#', FALSE);
120 END IF;
121
122 l_ruleId := i_rules.ruleId;
123 FOR v_routingInfo IN c_routingInfo LOOP
124 UPDATE iby_routinginfo
125 SET payeeid = i_rules.payeeId, bepkey = i_rules.merchantAccount,
126 bepid = i_rules.bepid, configured = i_rules.activeStatus,
127 instr_type = i_rules.bepInstrType, priority = i_rules.priority,
128 last_update_date = sysdate, last_updated_by = fnd_global.user_id,
129 last_update_login = fnd_global.login_id,
130 hitcounter = i_rules.hitcounter,
131 object_version_number = object_version_number+1
132 WHERE CURRENT OF c_routingInfo;
133 END LOOP;
134
135 COMMIT;
136 IF ( i_conditions.COUNT > 0 ) THEN
137 DELETE FROM iby_pmtmthd_conditions
138 WHERE l_ruleId = iby_pmtmthd_conditions.PAYMENTMETHODID ;
139
140 FOR v_count in 1..i_conditions.COUNT LOOP
141 iby_pmtmthd_conditions_pkg.createCondition(l_ruleId,
142 i_conditions(v_count).parameter,
143 i_conditions(v_count).operation,
144 i_conditions(v_count).value,
145 i_conditions(v_count).is_value_string,
146 i_conditions(v_count).entry_seq,
147 i_conditions(v_count).condition_name );
148 END LOOP;
149
150 END IF;
151
152 END;
153
154 /*
155 ** Function: deleteRoutingInfo.
156 ** Purpose: deletes the Routing information in the database.
157 */
158 procedure deleteRoutingInfo ( i_paymentmethodId in
159 iby_routinginfo.paymentmethodId%type,
160 i_paymentmethodName in
161 iby_routinginfo.paymentmethodName%type,
162 i_version in
163 iby_routinginfo.object_version_number%type)
164 is
165 -- Check whether this method name is already being used
166 CURSOR c_routingInfo IS
167 SELECT *
168 FROM iby_routinginfo a
169 WHERE i_version = a.object_version_number AND
170 i_paymentmethodName=a.paymentmethodName AND
171 i_paymentmethodId = a.paymentmethodId
172 FOR UPDATE ;
173 BEGIN
174 IF c_routingInfo%ISOPEN
175 THEN
176 CLOSE c_routingInfo;
177 OPEN c_routingInfo;
178 ELSE
179 OPEN c_routingInfo;
180 END IF;
181
182 IF c_routingInfo%NOTFOUND
183 THEN
184 CLOSE c_routingInfo;
185 raise_application_error(-20000, 'IBY_204561#', FALSE);
186 --raise_application_error(-20520,
187 --'Routing Rule has been changed. Delete Failed.',
188 --FALSE);
189 END IF;
190
191 CLOSE c_routingInfo;
192 FOR v_routingInfo IN c_routingInfo LOOP
193 DELETE FROM iby_routinginfo
194 WHERE CURRENT OF c_routingInfo;
195 END LOOP;
196 IF c_routingInfo%ISOPEN THEN
197 CLOSE c_routingInfo;
198 END IF;
199
200 DELETE FROM iby_pmtmthd_conditions a
201 WHERE a.paymentmethodid = i_paymentmethodId;
202
203 COMMIT;
204
205 END;
206
207 /*
208 ** Function: isDuplicateCondNames.
209 ** Purpose: Checks whether the input rule condition names contain
210 ** duplicates. Returns 'true' if there are duplicates, and
211 ** 'false' if not.
212 */
213 function isDuplicateCondNames ( i_conditions in t_condtRecVec )
214 return boolean is
215 BEGIN
216
217 FOR v_count1 in 1..i_conditions.COUNT LOOP
218 FOR v_count2 in (v_count1 + 1)..i_conditions.COUNT LOOP
219 IF (UPPER(i_conditions(v_count1).condition_name) =
220 UPPER(i_conditions(v_count2).condition_name))
221 THEN
222 return true;
223 END IF;
224 END LOOP;
225 END LOOP;
226
227 return false;
228
229 END isDuplicateCondNames;
230
231 end iby_routinginfo_pkg;
[Home] [Help]