1 package body iby_pmtmthd_conditions_pkg as
2 /*$Header: ibycondb.pls 115.12 2002/10/04 20:47:05 jleybovi ship $*/
3
4 /*--------------------------------------------------------------+
5 | Function: createCondition. |
6 | Purpose: To create a rule condition in the database. |
7 +--------------------------------------------------------------*/
8
9 procedure createCondition(
10 i_paymentmethodid in iby_pmtmthd_conditions.paymentmethodid%type,
11 i_parameter_code in iby_pmtmthd_conditions.parameter_code%type,
12 i_operation_code in iby_pmtmthd_conditions.operation_code%type,
13 i_value in iby_pmtmthd_conditions.value%type,
14 i_is_value_string in iby_pmtmthd_conditions.is_value_string%type,
15 i_entry_sequence in iby_pmtmthd_conditions.entry_sequence%type,
16 i_condition_name in iby_pmtmthd_conditions.condition_name%type)
17 is
18 l_count int:=0;
19 CURSOR c_ruleCondt IS
20 SELECT COUNT(*)
21 FROM iby_pmtmthd_conditions a
22 WHERE
23 i_entry_sequence=a.entry_sequence AND
24 i_paymentmethodId = a.paymentmethodId ;
25 BEGIN
26
27 IF c_ruleCondt%ISOPEN
28 THEN
29 CLOSE c_ruleCondt;
30 OPEN c_ruleCondt;
31 ELSE
32 OPEN c_ruleCondt;
33 END IF;
34
35 FETCH c_ruleCondt INTO l_count;
36 CLOSE c_ruleCondt;
37
38 IF (checkDuplicateCondName(i_condition_name, i_paymentmethodid) = false)
39 THEN
40 raise_application_error(-20000, 'IBY_204589#', FALSE);
41 END IF;
42
43 -- Check whether this condition is already being used
44 -- if not create a new row.
45
46 IF (l_count = 0)
47 THEN
48 INSERT INTO iby_pmtmthd_conditions
49 (paymentmethodid, parameter_code, operation_code, value, is_value_string,
50 entry_sequence, last_update_date, last_updated_by, creation_date,
51 created_by, last_update_login, condition_name, object_version_number)
52 VALUES ( i_paymentmethodid, i_parameter_code, i_operation_code, i_value,
53 i_is_value_string, i_entry_sequence, sysdate, fnd_global.user_id, sysdate, fnd_global.user_id,
54 fnd_global.login_id, i_condition_name, 1);
55 ELSE
56 raise_application_error(-20000, 'IBY_204554#', FALSE);
57 END IF;
58
59 COMMIT;
60
61 END;
62
63
64 /*
65 ** Function: modifyCondition.
66 ** Purpose: modifies rule condition information in the database.
67 */
68 procedure modifyCondition (
69 i_paymentmethodid in iby_pmtmthd_conditions.paymentmethodid%type,
70 i_parameter_code in iby_pmtmthd_conditions.parameter_code%type,
71 i_operation_code in iby_pmtmthd_conditions.operation_code%type,
72 i_value in iby_pmtmthd_conditions.value%type,
73 i_is_value_string in iby_pmtmthd_conditions.is_value_string%type,
74 i_entry_sequence in iby_pmtmthd_conditions.entry_sequence%type,
75 i_version in iby_pmtmthd_conditions.object_version_number%type,
76 i_condition_name in iby_pmtmthd_conditions.condition_name%type)
77 is
78
79 CURSOR c_ruleCondt IS
80 SELECT *
81 FROM iby_pmtmthd_conditions a
82 WHERE i_version = a.object_version_number AND
83 i_entry_sequence=a.entry_sequence AND
84 i_paymentmethodId = a.paymentmethodId
85 FOR UPDATE;
86 BEGIN
87
88 IF c_ruleCondt%ISOPEN
89 THEN
90 CLOSE c_ruleCondt;
91 OPEN c_ruleCondt;
92 ELSE
93 OPEN c_ruleCondt;
94 END IF;
95
96 IF c_ruleCondt%NOTFOUND
97 THEN
98 CLOSE c_ruleCondt;
99 raise_application_error(-20000, 'IBY_204555#', FALSE);
100 END IF;
101
102 CLOSE c_ruleCondt;
103
104 IF (checkDuplicateCondName(i_condition_name, i_paymentmethodid) = false)
105 THEN
106 raise_application_error(-20000, 'IBY_204589#', FALSE);
107 END IF;
108
109 FOR v_ruleCondt IN c_ruleCondt LOOP
110 UPDATE iby_pmtmthd_conditions
111 SET paymentmethodid = i_paymentmethodid, parameter_code = i_parameter_code,
112 operation_code = i_operation_code, value=i_value,
113 is_value_string = i_is_value_string, entry_sequence=i_entry_sequence,
114 last_update_date = sysdate,
115 last_updated_by = fnd_global.user_id,
116 last_update_login = fnd_global.login_id,
117 condition_name = i_condition_name,
118 object_version_number = object_version_number+1
119 WHERE CURRENT OF c_ruleCondt;
120
121 END LOOP;
122
123 IF c_ruleCondt%ISOPEN
124 THEN
125 CLOSE c_ruleCondt;
126 END IF;
127
128 COMMIT;
129
130 END;
131
132
133
134 /*
135 ** Function: deleteCondition.
136 ** Purpose: deletes rule condition in the database.
137 */
138 procedure deleteCondition (
139 i_paymentmethodid in iby_pmtmthd_conditions.paymentmethodid%type,
140 i_parameter_code in iby_pmtmthd_conditions.parameter_code%type,
141 i_operation_code in iby_pmtmthd_conditions.operation_code%type,
142 i_value in iby_pmtmthd_conditions.value%type,
143 i_is_value_string in iby_pmtmthd_conditions.is_value_string%type,
144 i_entry_sequence in iby_pmtmthd_conditions.entry_sequence%type,
145 i_version in iby_pmtmthd_conditions.object_version_number%type)
146 is
147 -- Check whether this method name is already being used
148 CURSOR c_ruleCondt IS
149 SELECT *
150 FROM iby_pmtmthd_conditions a
151 WHERE i_version = a.object_version_number AND
152 i_entry_sequence=a.entry_sequence AND
153 i_paymentmethodId = a.paymentmethodId
154 FOR UPDATE;
155 BEGIN
156 IF c_ruleCondt%ISOPEN
157 THEN
158 CLOSE c_ruleCondt;
159 OPEN c_ruleCondt;
160 ELSE
161 OPEN c_ruleCondt;
162 END IF;
163
164 IF c_ruleCondt%NOTFOUND
165 THEN
166 CLOSE c_ruleCondt;
167 raise_application_error(-20000, 'IBY_204556#', FALSE);
168 END IF;
169
170 CLOSE c_ruleCondt;
171
172 FOR v_ruleCondt IN c_ruleCondt LOOP
173 DELETE FROM iby_pmtmthd_conditions
174 WHERE CURRENT OF c_ruleCondt;
175 END LOOP;
176
177 IF c_ruleCondt%ISOPEN
178 THEN
179 CLOSE c_ruleCondt;
180 END IF;
181
182 COMMIT;
183
184 END;
185
186 /*
187 ** Function: checkDuplicateCondName.
188 ** Purpose: Checks whether the condition name is unique for this rule. Returns
189 ** true if the name is unique, false otherwise.
190 */
191 function checkDuplicateCondName(
192 i_condition_name in iby_pmtmthd_conditions.condition_name%type,
193 i_paymentmethodid in iby_pmtmthd_conditions.paymentmethodid%type
194 )
195 return boolean is
196
197 l_count int:=0;
198 BEGIN
199
200 SELECT COUNT(*) into l_count
201 FROM iby_pmtmthd_conditions a
202 WHERE UPPER(i_condition_name) = UPPER(a.condition_name)
203 AND i_paymentmethodId = a.paymentmethodId ;
204
205 IF (l_count = 0) THEN
206 RETURN TRUE;
207 ELSE
208 RETURN FALSE;
209 END IF;
210
211 END checkDuplicateCondName;
212
213 end iby_pmtmthd_conditions_pkg;
[Home] [Help]