1 PACKAGE BODY CN_RATE_TIERS_PKG as
2 /* $Header: cnplirtb.pls 120.2 2005/07/12 16:28:26 appldev ship $ */
3 /* Name : CN_RATE_TIERS_PKG
4 Purpose : Holds all server side packages used to proces a commission rate
5 table tier.
6
7 Notes : If we add, update or delete a tier we must 'INCOMPLETE' any comp
8 plans that this schedule is assigned to (via a quota).
9 This is done in the calls to cn_comp_plans_pkg.set_status.
10 Since there is no custom validation in this package the com plan
11 status update could've gone at the start of the begin procedure.
12 But for consistency with the other packages we call it in each
13 individual insert, update and delete procedure.*/
14
15 PROCEDURE INSERT_ROW
16 (X_RATE_TIER_ID IN OUT NOCOPY CN_RATE_TIERS.RATE_TIER_ID%TYPE,
17 X_RATE_SCHEDULE_ID IN CN_RATE_TIERS.RATE_SCHEDULE_ID%TYPE,
18 X_COMMISSION_AMOUNT IN CN_RATE_TIERS.COMMISSION_AMOUNT%TYPE,
19 X_RATE_SEQUENCE IN CN_RATE_TIERS.RATE_SEQUENCE%TYPE,
20 --R12 MOAC Changes--Start
21 X_ORG_ID IN CN_RATE_TIERS.ORG_ID%TYPE,
22 --R12 MOAC Changes--End
23 X_CREATION_DATE IN CN_RATE_TIERS.CREATION_DATE%TYPE := SYSDATE,
24 X_CREATED_BY IN CN_RATE_TIERS.CREATED_BY%TYPE := FND_GLOBAL.USER_ID,
25 X_LAST_UPDATE_DATE IN CN_RATE_TIERS.LAST_UPDATE_DATE%TYPE := SYSDATE,
26 X_LAST_UPDATED_BY IN CN_RATE_TIERS.LAST_UPDATED_BY%TYPE := FND_GLOBAL.USER_ID,
27 X_LAST_UPDATE_LOGIN IN CN_RATE_TIERS.LAST_UPDATE_LOGIN%TYPE:= FND_GLOBAL.LOGIN_ID) IS
28
29 CURSOR C IS SELECT RATE_TIER_ID FROM CN_RATE_TIERS
30 WHERE RATE_TIER_ID = X_RATE_TIER_ID;
31
32 CURSOR id IS SELECT CN_RATE_TIERS_S.NEXTVAL FROM DUAL;
33 BEGIN
34 IF (x_rate_tier_id IS NULL) THEN
35 OPEN id;
36 FETCH id INTO x_rate_tier_id;
37 IF (id%notfound) THEN
38 CLOSE id;
39 RAISE no_data_found;
40 END IF;
41 CLOSE id;
42 END IF;
43
44 -- invalidate all comp plans using a rate table with this tier
45 cn_comp_plans_pkg.set_status
46 (x_comp_plan_id => NULL,
47 x_quota_id => NULL,
48 x_rate_schedule_id => X_rate_schedule_id,
49 x_status_code => 'INCOMPLETE',
50 x_event => 'CHANGE_TIERS');
51
52 insert into CN_RATE_TIERS
53 (RATE_TIER_ID,
54 RATE_SCHEDULE_ID,
55 COMMISSION_AMOUNT,
56 RATE_SEQUENCE,
57 --R12 MOAC Changes--Start
58 ORG_ID,
59 --R12 MOAC Changes--End
60 CREATED_BY,
61 CREATION_DATE,
62 LAST_UPDATE_LOGIN,
63 LAST_UPDATE_DATE,
64 LAST_UPDATED_BY,
65 OBJECT_VERSION_NUMBER)
66 VALUES
67 (X_RATE_TIER_ID,
68 X_RATE_SCHEDULE_ID,
69 X_COMMISSION_AMOUNT,
70 X_RATE_SEQUENCE,
71 --R12 MOAC Changes--Start
72 X_ORG_ID,
73 --R12 MOAC Changes--End
74 X_CREATED_BY,
75 X_CREATION_DATE,
76 X_LAST_UPDATE_LOGIN,
77 X_LAST_UPDATE_DATE,
78 X_LAST_UPDATED_BY,
79 0);
80
81 open c;
82 fetch c into x_rate_tier_id;
83 if (c%notfound) then
84 close c;
85 raise no_data_found;
86 end if;
87 close c;
88
89 -- assign this rate tier to all salesreps with this rate schedule
90 cn_srp_rate_assigns_pkg.insert_record
91 (x_rate_schedule_id => x_rate_schedule_id,
92 x_rate_tier_id => x_rate_tier_id,
93 x_rate_sequence => x_rate_sequence,
94
95 -- these are not used anymore
96 x_srp_plan_assign_id => null,
97 x_srp_quota_assign_id => null,
98 x_srp_rate_assign_id => null,
99 x_quota_id => null,
100 x_commission_Rate => null,
101 x_commission_amount => null,
102 x_disc_rate_table_flag => null);
103
104 END INSERT_ROW;
105
106 PROCEDURE LOCK_ROW
107 (X_RATE_TIER_ID IN CN_RATE_TIERS.RATE_TIER_ID%TYPE,
108 X_OBJECT_VERSION_NUMBER IN CN_RATE_TIERS.OBJECT_VERSION_NUMBER%TYPE) IS
109
110 cursor c is
111 select object_version_number
112 from CN_RATE_TIERS
113 where RATE_TIER_ID = X_RATE_TIER_ID
114 for update of RATE_TIER_ID nowait;
115
116 tlinfo c%rowtype ;
117 BEGIN
118 open c;
119 fetch c into tlinfo;
120 if (c%notfound) then
121 close c;
122 fnd_message.set_name('CN', 'CN_RECORD_DELETED');
123 fnd_msg_pub.add;
124 raise fnd_api.g_exc_unexpected_error;
125 end if;
126 close c;
127
128 if (tlinfo.object_version_number <> x_object_version_number) then
129 fnd_message.set_name('CN', 'CN_RECORD_CHANGED');
130 fnd_msg_pub.add;
131 raise fnd_api.g_exc_unexpected_error;
132 end if;
133
134 END LOCK_ROW;
135
136 PROCEDURE UPDATE_ROW
137 (X_RATE_TIER_ID IN CN_RATE_TIERS.RATE_TIER_ID%TYPE,
138 X_RATE_SCHEDULE_ID IN CN_RATE_TIERS.RATE_SCHEDULE_ID%TYPE,
139 X_COMMISSION_AMOUNT IN CN_RATE_TIERS.COMMISSION_AMOUNT%TYPE,
140 X_RATE_SEQUENCE IN CN_RATE_TIERS.RATE_SEQUENCE%TYPE,
141 X_OBJECT_VERSION_NUMBER IN OUT NOCOPY CN_RATE_TIERS.OBJECT_VERSION_NUMBER%TYPE,
142 X_LAST_UPDATE_DATE IN CN_RATE_TIERS.LAST_UPDATE_DATE%TYPE := SYSDATE,
143 X_LAST_UPDATED_BY IN CN_RATE_TIERS.LAST_UPDATED_BY%TYPE := FND_GLOBAL.USER_ID,
144 X_LAST_UPDATE_LOGIN IN CN_RATE_TIERS.LAST_UPDATE_LOGIN%TYPE:= FND_GLOBAL.LOGIN_ID) IS
145
146 l_commission_amount_old number := 0;
147 BEGIN
148 X_OBJECT_VERSION_NUMBER := X_OBJECT_VERSION_NUMBER + 1;
149 -- invalidate all comp plans using a rate schedule with this tier
150 cn_comp_plans_pkg.set_status
151 (x_comp_plan_id => NULL,
152 x_quota_id => NULL,
153 x_rate_schedule_id => x_rate_schedule_id,
154 x_status_code => 'INCOMPLETE',
155 x_event => 'CHANGE_TIERS');
156
157 UPDATE cn_rate_tiers SET
158 rate_schedule_id = x_rate_schedule_id,
159 commission_amount = x_commission_amount,
160 RATE_SEQUENCE = X_RATE_SEQUENCE,
161 last_update_date = X_Last_Update_Date,
162 last_updated_by = X_Last_Updated_By,
163 last_update_login = X_Last_Update_Login,
164 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER
165 WHERE rate_tier_id = X_rate_tier_id;
166
167 IF (SQL%NOTFOUND) then
168 raise no_data_found;
169 END IF;
170
171 -- see if the commission amount changed
172 select commission_amount into l_commission_amount_old
173 from cn_rate_tiers
174 where rate_tier_id = x_rate_tier_id;
175
176 IF x_commission_amount <> l_commission_amount_old THEN
177
178 -- Only the commission amount is denormalized no srp_tiers to maintain
179 cn_srp_rate_assigns_pkg.synch_rate(
180 x_srp_plan_assign_id => null
181 ,x_srp_quota_assign_id => null
182 ,x_rate_schedule_id => x_rate_schedule_id
183 ,x_rate_tier_id => x_rate_tier_id
184 ,x_commission_rate => null
185 ,x_salesrep_id => null
186 ,x_start_period_id => null
187 ,x_commission_amount => x_commission_amount);
188 END IF;
189
190 END UPDATE_ROW;
191
192
193 -- Procedure Name
194 --
195 -- Purpose
196 --
197 -- Notes
198 -- If the rate tier id is null this routine has been called on delete
199 -- or a rate schedule. You cannot delete a schedule that is assigned to a
200 -- quota so there's no need to try and update the status of the plans.
201 -- If the tier_id is not null then we delete an individual tier.
202
203 PROCEDURE Delete_Row
204 (X_RATE_SCHEDULE_ID IN CN_RATE_TIERS.RATE_SCHEDULE_ID%TYPE,
205 X_RATE_TIER_ID IN CN_RATE_TIERS.RATE_TIER_ID%TYPE) IS
206
207 BEGIN
208
209 IF X_Rate_Schedule_Id IS NOT NULL THEN
210
211 IF X_Rate_Tier_Id IS NULL THEN
212
213 DELETE FROM cn_rate_tiers
214 WHERE rate_schedule_id = X_Rate_Schedule_Id;
215
216 ELSE
217 cn_comp_plans_pkg.set_status(
218 x_comp_plan_id => NULL
219 ,x_quota_id => NULL
220 ,x_rate_schedule_id => X_rate_schedule_id
221 ,x_status_code => 'INCOMPLETE'
222 ,x_event => 'CHANGE_TIERS');
223
224 DELETE FROM cn_rate_tiers
225 WHERE rate_tier_id = x_rate_tier_id;
226
227 IF (SQL%NOTFOUND) then
228 Raise NO_DATA_FOUND;
229 END if;
230
231 cn_srp_rate_assigns_pkg.delete_record(
232 x_srp_plan_assign_id => null
233 ,x_srp_rate_assign_id => null
234 ,x_quota_id => null
235 ,x_rate_schedule_id => x_rate_schedule_id
236 ,x_rate_tier_id => x_rate_tier_id);
237
238 END IF;
239
240 END IF;
241
242
243 END Delete_Row;
244
245 END CN_RATE_TIERS_PKG;
[Home] [Help]