[Home] [Help]
PACKAGE BODY: APPS.CN_SRP_ROLLOVER_QUOTA_PVT
Source
1 PACKAGE BODY CN_SRP_ROLLOVER_QUOTA_PVT AS
2 /*$Header: cnvsrb.pls 115.1 2002/12/04 02:36:49 fting noship $*/
3
4 G_PKG_NAME CONSTANT VARCHAR2(30):='CN_SRP_ROLLOVER_QUOTA_PVT';
5
6 -- Start of comments
7 -- API name : Update_Srp_Rollover_Quota
8 -- Type : Private.
9 -- Function :
10 -- Pre-reqs : None.
11 -- Parameters :
12 -- End of comments
13 PROCEDURE Update_Srp_Rollover_Quota
14 (p_api_version IN NUMBER,
15 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
16 p_commit IN VARCHAR2 := FND_API.G_FALSE,
17 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
18 p_srp_rollover_quota IN srp_rollover_quota_rec_type,
19 x_return_status OUT NOCOPY VARCHAR2,
20 x_msg_count OUT NOCOPY NUMBER,
21 x_msg_data OUT NOCOPY VARCHAR2
22 ) IS
23
24 l_api_name CONSTANT VARCHAR2(30) := 'Update_Srp_Rollover_Quota';
25 l_api_version CONSTANT NUMBER := 1.0;
26
27 CURSOR l_old_srp_rollover_quota_cr IS
28 SELECT *
29 FROM cn_srp_rollover_quotas
30 WHERE srp_rollover_quota_id = p_srp_rollover_quota.srp_rollover_quota_id;
31
32 l_old_srp_rollover_quota l_old_srp_rollover_quota_cr%ROWTYPE;
33 l_srp_rollover_quota srp_rollover_quota_rec_type;
34 l_temp_count NUMBER;
35 l_start_date DATE;
36 l_end_date DATE;
37
38
39 l_loading_status varchar2(50);
40
41 l_srp_rollover_quota_id NUMBER := NULL;
42 l_rollover NUMBER := NULL;
43 l_customized_flag cn_srp_quota_assigns.customized_flag%type;
44
45
46 l_attribute_category cn_srp_rollover_quotas.attribute_category%TYPE;
47 l_attribute1 cn_srp_rollover_quotas.attribute1%TYPE;
48 l_attribute2 cn_srp_rollover_quotas.attribute2%TYPE;
49 l_attribute3 cn_srp_rollover_quotas.attribute3%TYPE;
50 l_attribute4 cn_srp_rollover_quotas.attribute4%TYPE;
51 l_attribute5 cn_srp_rollover_quotas.attribute5%TYPE;
52 l_attribute6 cn_srp_rollover_quotas.attribute6%TYPE;
53 l_attribute7 cn_srp_rollover_quotas.attribute7%TYPE;
54 l_attribute8 cn_srp_rollover_quotas.attribute8%TYPE;
55 l_attribute9 cn_srp_rollover_quotas.attribute9%TYPE;
56 l_attribute10 cn_srp_rollover_quotas.attribute10%TYPE;
57 l_attribute11 cn_srp_rollover_quotas.attribute11%TYPE;
58 l_attribute12 cn_srp_rollover_quotas.attribute12%TYPE;
59 l_attribute13 cn_srp_rollover_quotas.attribute13%TYPE;
60 l_attribute14 cn_srp_rollover_quotas.attribute14%TYPE;
61 l_attribute15 cn_srp_rollover_quotas.attribute15%TYPE;
62
63
64
65
66 BEGIN
67 -- Standard Start of API savepoint
68 SAVEPOINT Update_Srp_Rollover_Quota;
69 -- Standard call to check for call compatibility.
70 IF NOT FND_API.Compatible_API_Call
71 (l_api_version ,
72 p_api_version ,
73 l_api_name ,
74 G_PKG_NAME )
75 THEN
76 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
77 END IF;
78 -- Initialize message list if p_init_msg_list is set to TRUE.
79 IF FND_API.to_Boolean( p_init_msg_list ) THEN
80 FND_MSG_PUB.initialize;
81 END IF;
82 -- Initialize API return status to success
83 x_return_status := FND_API.G_RET_STS_SUCCESS;
84 -- API body
85
86
87 -- srp_quota_assign_id cannot be NULL
88
89 IF (p_srp_rollover_quota.srp_rollover_quota_id is NULL) OR
90 (p_srp_rollover_quota.srp_rollover_quota_id = fnd_api.g_miss_num)
91 THEN
92 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
93 FND_MESSAGE.SET_NAME ('CN' , 'CN_INPUT_CANT_NULL');
94 FND_MESSAGE.SET_TOKEN('INPUT_NAME', 'srp_rollover_quota_id');
95 FND_MSG_PUB.Add;
96 END IF;
97 RAISE FND_API.G_EXC_ERROR ;
98 END IF;
99
100 -- srp_quota_assign_id cannot be NULL
101
102 IF (p_srp_rollover_quota.srp_quota_assign_id is NULL) OR
103 (p_srp_rollover_quota.srp_quota_assign_id = fnd_api.g_miss_num)
104 THEN
105 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
106 FND_MESSAGE.SET_NAME ('CN' , 'CN_INPUT_CANT_NULL');
107 FND_MESSAGE.SET_TOKEN('INPUT_NAME', 'srp_quota_assign_id');
108 FND_MSG_PUB.Add;
109 END IF;
110 RAISE FND_API.G_EXC_ERROR ;
111 END IF;
112
113 -- Quota_Id cannot be NULL
114
115 IF (p_srp_rollover_quota.quota_id is NULL) OR
116 (p_srp_rollover_quota.quota_id = fnd_api.g_miss_num)
117 THEN
118 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
119 FND_MESSAGE.SET_NAME ('CN' , 'CN_INPUT_CANT_NULL');
120 FND_MESSAGE.SET_TOKEN('INPUT_NAME', cn_api.get_lkup_meaning('PE', 'INPUT_TOKEN'));
121 FND_MSG_PUB.Add;
122 END IF;
123 RAISE FND_API.G_EXC_ERROR ;
124 END IF;
125
126
127 -- Source_Quota_Id cannot be NULL
128
129 IF (p_srp_rollover_quota.source_quota_id is NULL) OR
130 (p_srp_rollover_quota.source_quota_id = fnd_api.g_miss_num)
131 THEN
132 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
133 FND_MESSAGE.SET_NAME ('CN' , 'CN_INPUT_CANT_NULL');
134 FND_MESSAGE.SET_TOKEN('INPUT_NAME', cn_api.get_lkup_meaning('SPE', 'INPUT_TOKEN'));
135 FND_MSG_PUB.Add;
136 END IF;
137 RAISE FND_API.G_EXC_ERROR ;
138 END IF;
139
140 -- Rollover cannot be NULL
141
142 IF (p_srp_rollover_quota.rollover is NULL) OR
143 (p_srp_rollover_quota.rollover = fnd_api.g_miss_num)
144 THEN
145 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
146 FND_MESSAGE.SET_NAME ('CN' , 'CN_INPUT_CANT_NULL');
147 FND_MESSAGE.SET_TOKEN('INPUT_NAME', cn_api.get_lkup_meaning('ROLLPERCENT', 'INPUT_TOKEN'));
148 FND_MSG_PUB.Add;
149 END IF;
150 RAISE FND_API.G_EXC_ERROR ;
151 END IF;
152
153
154
155 OPEN l_old_srp_rollover_quota_cr;
156 FETCH l_old_srp_rollover_quota_cr INTO l_old_srp_rollover_quota;
157 CLOSE l_old_srp_rollover_quota_cr;
158
159
160
161
162 -- 3. check object version number
163 IF l_old_srp_rollover_quota.object_version_number <>
164 p_srp_rollover_quota.object_version_number THEN
165 fnd_message.set_name('CN', 'CN_RECORD_CHANGED');
166 fnd_msg_pub.add;
167 raise fnd_api.g_exc_error;
168 END IF;
169
170
171
172
173
174 SELECT
175
176
177 Decode(p_srp_rollover_quota.rollover,
178 fnd_api.g_miss_num, l_old_srp_rollover_quota.rollover,
179 p_srp_rollover_quota.rollover),
180
181 Decode(p_srp_rollover_quota.ATTRIBUTE_CATEGORY,
182 fnd_api.g_miss_char, NULL,
183 p_srp_rollover_quota.ATTRIBUTE_CATEGORY),
184 Decode(p_srp_rollover_quota.ATTRIBUTE1,
185 fnd_api.g_miss_char, NULL,
186 p_srp_rollover_quota.ATTRIBUTE1),
187 Decode(p_srp_rollover_quota.ATTRIBUTE2,
188 fnd_api.g_miss_char, NULL,
189 p_srp_rollover_quota.ATTRIBUTE2),
190 Decode(p_srp_rollover_quota.ATTRIBUTE3,
191 fnd_api.g_miss_char, NULL,
192 p_srp_rollover_quota.ATTRIBUTE3),
193 Decode(p_srp_rollover_quota.ATTRIBUTE4,
194 fnd_api.g_miss_char, NULL,
195 p_srp_rollover_quota.ATTRIBUTE4),
196 Decode(p_srp_rollover_quota.ATTRIBUTE5,
197 fnd_api.g_miss_char, NULL,
198 p_srp_rollover_quota.ATTRIBUTE5),
199 Decode(p_srp_rollover_quota.ATTRIBUTE6,
200 fnd_api.g_miss_char, NULL,
201 p_srp_rollover_quota.ATTRIBUTE6),
202 Decode(p_srp_rollover_quota.ATTRIBUTE7,
203 fnd_api.g_miss_char, NULL,
204 p_srp_rollover_quota.ATTRIBUTE7),
205 Decode(p_srp_rollover_quota.ATTRIBUTE8,
206 fnd_api.g_miss_char, NULL,
207 p_srp_rollover_quota.ATTRIBUTE8),
208 Decode(p_srp_rollover_quota.ATTRIBUTE9,
209 fnd_api.g_miss_char, NULL,
210 p_srp_rollover_quota.ATTRIBUTE9),
211 Decode(p_srp_rollover_quota.ATTRIBUTE10,
212 fnd_api.g_miss_char, NULL,
213 p_srp_rollover_quota.ATTRIBUTE10),
214 Decode(p_srp_rollover_quota.ATTRIBUTE11,
215 fnd_api.g_miss_char, NULL,
216 p_srp_rollover_quota.ATTRIBUTE11),
217 Decode(p_srp_rollover_quota.ATTRIBUTE12,
218 fnd_api.g_miss_char, NULL,
219 p_srp_rollover_quota.ATTRIBUTE12),
220 Decode(p_srp_rollover_quota.ATTRIBUTE13,
221 fnd_api.g_miss_char, NULL,
222 p_srp_rollover_quota.ATTRIBUTE13),
223 Decode(p_srp_rollover_quota.ATTRIBUTE14,
224 fnd_api.g_miss_char, NULL,
225 p_srp_rollover_quota.ATTRIBUTE14),
226 Decode(p_srp_rollover_quota.ATTRIBUTE15,
227 fnd_api.g_miss_char, NULL,
228 p_srp_rollover_quota.ATTRIBUTE15)
229
230 INTO
231 l_rollover,
232 l_attribute_category,
233 l_attribute1,
234 l_attribute2,
235 l_attribute3,
236 l_attribute4,
237 l_attribute5,
238 l_attribute6,
239 l_attribute7,
240 l_attribute8,
241 l_attribute9,
242 l_attribute10,
243 l_attribute11,
244 l_attribute12,
245 l_attribute13,
246 l_attribute14,
247 l_attribute15
248 FROM dual;
249
250
251 IF l_rollover <> l_old_srp_rollover_quota.rollover THEN
252
253 select customized_flag
254 into l_customized_flag
255 from cn_srp_quota_assigns
256 where srp_quota_assign_id = p_srp_rollover_quota.srp_quota_assign_id;
257
258
259 IF l_customized_flag = 'Y' THEN
260
261
262
263 CN_SRP_ROLLOVER_QUOTAS_PKG.UPDATE_ROW (
264 X_SRP_ROLLOVER_QUOTA_ID => p_srp_rollover_quota.srp_rollover_quota_id,
265 X_SRP_QUOTA_ASSIGN_ID => p_srp_rollover_quota.srp_quota_assign_id,
266 X_ROLLOVER_QUOTA_ID => p_srp_rollover_quota.rollover_quota_id,
267 X_QUOTA_ID => p_srp_rollover_quota.quota_id,
268 X_SOURCE_QUOTA_ID => p_srp_rollover_quota.source_quota_id,
269 X_ROLLOVER => l_rollover,
270 X_ATTRIBUTE_CATEGORY => l_attribute_category,
271 X_ATTRIBUTE1 => l_attribute1,
272 X_ATTRIBUTE2 => l_attribute2,
273 X_ATTRIBUTE3 => l_attribute3,
274 X_ATTRIBUTE4 => l_attribute4,
275 X_ATTRIBUTE5 => l_attribute5,
276 X_ATTRIBUTE6 => l_attribute6,
277 X_ATTRIBUTE7 => l_attribute7,
278 X_ATTRIBUTE8 => l_attribute8,
279 X_ATTRIBUTE9 => l_attribute9,
280 X_ATTRIBUTE10 => l_attribute10,
281 X_ATTRIBUTE11 => l_attribute11,
282 X_ATTRIBUTE12 => l_attribute12,
283 X_ATTRIBUTE13 => l_attribute13,
284 X_ATTRIBUTE14 => l_attribute14,
285 X_ATTRIBUTE15 => l_attribute15,
286 X_CREATED_BY => fnd_global.user_id,
287 X_CREATION_DATE => sysdate,
288 X_LAST_UPDATE_DATE => sysdate,
289 X_LAST_UPDATED_BY => fnd_global.user_id,
290 X_LAST_UPDATE_LOGIN => fnd_global.login_id
291 );
292 END IF; -- if customize_flag = 'Y'
293
294 END IF; -- l_rollover is changed
295
296
297 -- End of API body.
298 -- Standard check of p_commit.
299 IF FND_API.To_Boolean( p_commit ) THEN
300 COMMIT WORK;
301 END IF;
302 -- Standard call to get message count and if count is 1, get message info.
303 FND_MSG_PUB.Count_And_Get
304 (p_count => x_msg_count ,
305 p_data => x_msg_data ,
306 p_encoded => FND_API.G_FALSE );
307 EXCEPTION
308 WHEN FND_API.G_EXC_ERROR THEN
309 ROLLBACK TO Update_Srp_Rollover_Quota;
310 x_return_status := FND_API.G_RET_STS_ERROR ;
311 FND_MSG_PUB.Count_And_Get
312 (p_count => x_msg_count ,
313 p_data => x_msg_data ,
314 p_encoded => FND_API.G_FALSE );
315 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
316 ROLLBACK TO Update_Srp_Rollover_Quota;
317 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
318 FND_MSG_PUB.Count_And_Get
319 (p_count => x_msg_count ,
320 p_data => x_msg_data ,
321 p_encoded => FND_API.G_FALSE );
322 WHEN OTHERS THEN
323 ROLLBACK TO Update_Srp_Rollover_Quota;
324 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
325 IF FND_MSG_PUB.Check_Msg_Level
326 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
327 THEN
328 FND_MSG_PUB.Add_Exc_Msg
329 (G_PKG_NAME ,
330 l_api_name );
331 END IF;
332 FND_MSG_PUB.Count_And_Get
333 (p_count => x_msg_count ,
334 p_data => x_msg_data ,
335 p_encoded => FND_API.G_FALSE );
336 END Update_Srp_Rollover_Quota;
337
338
339
340
341 END CN_SRP_ROLLOVER_QUOTA_PVT;