[Home] [Help]
PACKAGE BODY: APPS.CN_TRX_FACTORS_PVT
Source
1 PACKAGE BODY CN_TRX_FACTORS_PVT as
2 /* $Header: cnxvtrxb.pls 120.1 2005/09/09 00:06:55 rarajara noship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'CN_TRX_FACTORS_PVT';
5 G_FILE_NAME CONSTANT VARCHAR2(12) := 'cnxvtrxb.pls';
6 G_LAST_UPDATE_DATE DATE := sysdate;
7 G_LAST_UPDATED_BY NUMBER := fnd_global.user_id;
8 G_CREATION_DATE DATE := sysdate;
9 G_CREATED_BY NUMBER := fnd_global.user_id;
10 G_LAST_UPDATE_LOGIN NUMBER := fnd_global.login_id;
11 G_ROWID VARCHAR2(30);
12 G_PROGRAM_TYPE VARCHAR2(30);
13 --|/*-----------------------------------------------------------------------+
14 --|
15 --| Procedure Name : CHECK_VALID_QUOTAS
16 --|
17 --|----------------------------------------------------------------------- */
18 PROCEDURE Check_valid_quotas
19 (
20 x_return_status OUT NOCOPY VARCHAR2 ,
21 x_msg_count OUT NOCOPY NUMBER ,
22 x_msg_data OUT NOCOPY VARCHAR2 ,
23 p_quota_name IN VARCHAR2 ,
24 p_rev_class_name IN VARCHAR2 ,
25 p_org_id IN NUMBER,
26 x_quota_id OUT NOCOPY NUMBER ,
27 x_quota_rule_id OUT NOCOPY NUMBER ,
28 x_rev_class_id OUT NOCOPY NUMBER ,
29 p_loading_status IN VARCHAR2 ,
30 x_loading_status OUT NOCOPY VARCHAR2
31 ) IS
32
33 l_api_name CONSTANT VARCHAR2(30)
34 := 'Validate_trx_factors';
35 l_lkup_meaning cn_lookups.meaning%TYPE;
36
37 l_loading_status VARCHAR2(80);
38
39 BEGIN
40 -- Initialize API return status to success
41 x_return_status := FND_API.G_RET_STS_SUCCESS;
42 x_loading_status := p_loading_status ;
43 --+
44 -- Check Miss And Null Parameters.
45 --+
46 l_lkup_meaning := cn_api.get_lkup_meaning('QUOTA_NAME','PM_OBJECT_TYPE');
47 IF ( (cn_api.chk_null_char_para
48 (p_char_para => p_quota_name,
49 p_obj_name => l_lkup_meaning,
50 p_loading_status => x_loading_status,
51 x_loading_status => l_loading_status)) = FND_API.G_TRUE ) THEN
52 RAISE FND_API.G_EXC_ERROR ;
53 END IF;
54 l_lkup_meaning := cn_api.get_lkup_meaning('','PM_OBJECT_TYPE');
55 IF ( (cn_api.chk_null_char_para
56 (p_char_para => p_rev_class_name,
57 p_obj_name => l_lkup_meaning,
58 p_loading_status => x_loading_status,
59 x_loading_status => l_loading_status)) = FND_API.G_TRUE ) THEN
60 RAISE FND_API.G_EXC_ERROR ;
61 END IF;
62 -- Quota ID
63 x_quota_id := cn_chk_plan_element_pkg.get_quota_id(Ltrim(Rtrim(p_quota_name)),p_org_id);
64 -- Get the Revenue Class ID
65 x_rev_class_id := cn_api.get_rev_class_id(Ltrim(Rtrim(p_rev_class_name)),p_org_id);
66 -- get the Quota Rule ID
67 x_quota_rule_id := cn_chk_plan_element_pkg.get_quota_rule_id(x_quota_id, x_rev_class_id);
68 IF p_quota_name IS NOT NULL THEN
69 IF x_quota_id IS NULL THEN
70 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
71 THEN
72 FND_MESSAGE.SET_NAME('CN' , 'CN_PLN_NOT_EXIST');
73 FND_MESSAGE.SET_TOKEN('PE_NAME',p_quota_name);
74 FND_MSG_PUB.Add;
75 RAISE FND_API.G_EXC_ERROR ;
76 END IF;
77 END IF;
78 END IF;
79
80 IF p_rev_class_name IS NOT NULL THEN
81 IF x_rev_class_id IS NULL THEN
82 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
83 THEN
84 FND_MESSAGE.SET_NAME ('CN' , 'CN_REV_CLASS_NOT_EXIST');
85 FND_MSG_PUB.Add;
86 END IF;
87 x_loading_status := 'CN_REV_CLASS_NOT_EXIST';
88 RAISE FND_API.G_EXC_ERROR ;
89 END IF ;
90 END IF;
91
92
93 IF x_quota_rule_id IS NULL THEN
94 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
95 THEN
96 FND_MESSAGE.SET_NAME ('CN' , 'CN_QUOTA_RULE_NOT_EXIST');
97 FND_MSG_PUB.Add;
98 END IF;
99 x_loading_status := 'CN_QUOTA_RULE_NOT_EXIST';
100 RAISE FND_API.G_EXC_ERROR ;
101 END IF;
102 -- End Check Valid Quotas.
103
104 END check_valid_quotas;
105
106 --|/*-----------------------------------------------------------------------+
107 --|
108 --| Procedure Name : Validate_Trx_Factors
109 --|
110 --|----------------------------------------------------------------------- */
111 PROCEDURE Validate_trx_Factors
112 (
113 x_return_status OUT NOCOPY VARCHAR2 ,
114 x_msg_count OUT NOCOPY NUMBER ,
115 x_msg_data OUT NOCOPY VARCHAR2 ,
116 p_trx_factor_rec IN cn_plan_element_pub.trx_factor_rec_type,
117 p_quota_name IN VARCHAR2,
118 p_quota_id IN NUMBER,
119 p_rev_class_name IN VARCHAR2,
120 p_rev_class_id IN NUMBER,
121 p_quota_rule_id IN NUMBER,
122 p_loading_status IN VARCHAR2,
123 x_loading_status OUT NOCOPY VARCHAR2
124 ) IS
125
126 l_api_name CONSTANT VARCHAR2(30)
127 := 'Validate_trx_factors';
128 l_lkup_meaning cn_lookups.meaning%TYPE;
129 l_tmp NUMBER;
130
131 l_loading_status VARCHAR2(80);
132
133 BEGIN
134 -- Initialize API return status to success
135 x_return_status := FND_API.G_RET_STS_SUCCESS;
136 x_loading_status := p_loading_status ;
137 --+
138 -- Check Miss And Null Parameters.
139 --+
140 l_lkup_meaning := cn_api.get_lkup_meaning('TRX_TYPE','PM_OBJECT_TYPE');
141
142 IF((cn_api.chk_null_char_para
143 (p_char_para => p_trx_factor_rec.trx_type,
144 p_obj_name => l_lkup_meaning,
145 p_loading_status => x_loading_status,
146 x_loading_status => l_loading_status)) = FND_API.G_TRUE)
147 THEN
148 RAISE FND_API.G_EXC_ERROR ;
149 END IF;
150
151
152 l_lkup_meaning := cn_api.get_lkup_meaning('REV_CLASS_NAME','PM_OBJECT_TYPE');
153 IF((cn_api.chk_null_char_para
154 (p_char_para => p_trx_factor_rec.rev_class_name,
155 p_obj_name => l_lkup_meaning,
156 p_loading_status => x_loading_status,
157 x_loading_status => l_loading_status)) = FND_API.G_TRUE)
158 THEN
159 RAISE FND_API.G_EXC_ERROR ;
160 END IF;
161
162 l_lkup_meaning := cn_api.get_lkup_meaning('EVENT_FACTOR','PM_OBJECT_TYPE');
163
164 IF ( (cn_api.chk_null_num_para
165 (p_num_para => p_trx_factor_rec.event_factor,
166 p_obj_name => l_lkup_meaning,
167 p_loading_status => x_loading_status,
168 x_loading_status => l_loading_status)) = FND_API.G_TRUE )
169 THEN
170 RAISE FND_API.G_EXC_ERROR ;
171 END IF;
172
173 IF ltrim(Rtrim(p_rev_class_name)) <> Ltrim(Rtrim(p_trx_factor_rec.rev_class_name))
174 THEN
175 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
176 THEN
177 FND_MESSAGE.SET_NAME ('CN' , 'CN_INCONSISTENT_REV_CLASS');
178 FND_MSG_PUB.Add;
179 END IF;
180 x_loading_status := 'CN_INCONSISTENT_REV_CLASS';
181 RAISE FND_API.G_EXC_ERROR ;
182 END IF;
183 -- +
184 -- Check Record Already exists.
185 --+
186 SELECT COUNT(*)
187 INTO l_tmp
188 FROM cn_trx_factors
189 WHERE revenue_class_id = p_rev_class_id
190 AND quota_id = p_quota_id
191 AND quota_rule_id = p_quota_rule_id
192 AND trx_type = p_trx_factor_rec.trx_type ;
193
194 IF (l_tmp <> 0) THEN
195 x_loading_status := 'TRX_FACTORS_EXISTS';
196 END IF ;
197
198 -- End of Validate Trx Factors.
199 -- Standard call to get message count and if count is 1, get message info.
200 --+
201 FND_MSG_PUB.Count_And_Get
202 (
203 p_count => x_msg_count,
204 p_data => x_msg_data,
205 p_encoded => FND_API.G_FALSE
206 );
207 EXCEPTION
208 WHEN FND_API.G_EXC_ERROR THEN
209 x_return_status := FND_API.G_RET_STS_ERROR ;
210 FND_MSG_PUB.Count_And_Get
211 (
212 p_count => x_msg_count ,
213 p_data => x_msg_data ,
214 p_encoded => FND_API.G_FALSE
215 );
216 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
217 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
218 x_loading_status := 'UNEXPECTED_ERR';
219 FND_MSG_PUB.Count_And_Get
220 (
221 p_count => x_msg_count ,
222 p_data => x_msg_data ,
223 p_encoded => FND_API.G_FALSE
224 );
225 WHEN OTHERS THEN
226 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
227 x_loading_status := 'UNEXPECTED_ERR';
228 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
229 THEN
230 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME ,l_api_name );
231 END IF;
232 FND_MSG_PUB.Count_And_Get
233 (
234 p_count => x_msg_count,
235 p_data => x_msg_data,
236 p_encoded=> FND_API.G_FALSE
237 );
238 END Validate_trx_factors;
239 --|/*---------------n--------------------------------------------------------+
240 --| Procedure Name : Update Trx Factors
241 --|
242 --|----------------------------------------------------------------------- */
243 PROCEDURE update_trx_factors
244 (
245 p_api_version IN NUMBER,
246 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
247 p_commit IN VARCHAR2 := FND_API.G_FALSE,
248 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
249 x_return_status OUT NOCOPY VARCHAR2,
250 x_msg_count OUT NOCOPY NUMBER,
251 x_msg_data OUT NOCOPY VARCHAR2,
252 p_quota_name IN VARCHAR2,
253 p_rev_class_name IN VARCHAR2,
254 p_trx_factor_rec_tbl IN CN_PLAN_ELEMENT_PUB.trx_factor_rec_tbl_type
255 := CN_PLAN_ELEMENT_PUB.G_MISS_TRX_FACTOR_REC_TBL,
256 x_loading_status OUT NOCOPY VARCHAR2,
257 p_org_id IN NUMBER
258 ) IS
259 l_api_name CONSTANT VARCHAR2(30)
260 := 'Update_Trx_Factors';
261 l_api_version CONSTANT NUMBER := 1.0;
262
263 l_quota_id NUMBER;
264 l_quota_rule_id NUMBER;
265 l_rev_class_id NUMBER;
266 l_trx_factor_rec cn_plan_element_pub.trx_factor_rec_type;
267
268 l_loading_status VARCHAR2(80);
269
270 BEGIN
271 --
272 -- Standard Start of API savepoint
273 -- +
274 SAVEPOINT Update_trx_Factors ;
275 --+
276 -- Standard call to check for call compatibility.
277 --+
278 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
279 p_api_version ,
280 l_api_name ,
281 G_PKG_NAME )
282 THEN
283 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
284 END IF;
285 --+
286 -- Initialize message list if p_init_msg_list is set to TRUE.
287 -- +
288 IF FND_API.to_Boolean( p_init_msg_list ) THEN
289 FND_MSG_PUB.initialize;
290 END IF;
291 -- +
292 -- Initialize API return status to success
293 --+
294 x_return_status := FND_API.G_RET_STS_SUCCESS;
295 x_loading_status := 'CN_UPDATED';
296
297 IF p_trx_factor_rec_tbl.COUNT > 0 THEN
298
299 check_valid_quotas
300 (x_return_status => x_return_status,
301 x_msg_count => x_msg_count,
302 x_msg_data => x_msg_data,
303 p_quota_name => p_quota_name,
304 p_rev_class_name => p_rev_class_name,
305 p_org_id => p_org_id,
306 x_quota_id => l_quota_id,
307 x_quota_rule_id => l_quota_rule_id,
308 x_rev_class_id => l_rev_class_id,
309 p_loading_status => x_loading_status,
310 x_loading_status => l_loading_status
311 );
312 x_loading_status := l_loading_status;
313 END IF;
314
315 IF ( x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
316 RAISE FND_API.G_EXC_ERROR ;
317 ELSIF ( x_return_status = FND_API.G_RET_STS_SUCCESS )
318 AND ( x_loading_status = 'CN_UPDATED' )
319 THEN
320 IF p_trx_factor_rec_tbl.COUNT > 0 THEN
321 FOR i IN p_trx_factor_rec_tbl.first .. p_trx_factor_rec_tbl.last LOOP
322 IF (p_trx_factor_rec_tbl.exists(i)) AND
323 (p_trx_factor_rec_tbl(i).rev_class_name =
324 p_rev_class_name )
325 THEN
326 l_trx_factor_rec.rev_class_name := p_trx_factor_rec_tbl(i).rev_class_name;
327 l_trx_factor_rec.trx_type := p_trx_factor_rec_tbl(i).trx_type;
328 l_trx_factor_rec.event_factor := p_trx_factor_rec_tbl(i).event_factor;
329 Validate_trx_Factors
330 (
331 x_return_status => x_return_status,
332 x_msg_count => x_msg_count,
333 x_msg_data => x_msg_data,
334 p_trx_factor_rec => l_trx_factor_rec,
335 p_quota_name => p_quota_name,
336 p_quota_id => l_quota_id,
337 p_rev_class_name => p_rev_class_name,
338 p_rev_class_id => l_rev_class_id,
339 p_quota_rule_id => l_quota_rule_id,
340 p_loading_status => x_loading_status,
341 x_loading_status => l_loading_status );
342 x_loading_status := l_loading_status;
343
344 IF ( x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
345 RAISE FND_API.G_EXC_ERROR ;
346 ELSIF ( x_return_status = FND_API.G_RET_STS_SUCCESS )
347 AND ( x_loading_status = 'TRX_FACTORS_EXISTS' ) THEN
348 x_loading_status := 'CN_UPDATED' ;
349 UPDATE cn_trx_factors
350 SET event_factor = p_trx_factor_rec_tbl(i).event_factor
351 WHERE quota_id = l_quota_id
352 AND quota_rule_id = l_quota_rule_id
353 AND trx_type = Ltrim(Rtrim(p_trx_factor_rec_tbl(i).trx_type)) ;
354 END IF;
355 END IF; -- trx Factor Exists
356 END LOOP; --+
357 CN_CHK_PLAN_ELEMENT_PKG.chk_trx_factor
358 (
359 x_return_status => x_return_status,
360 p_quota_rule_id => l_quota_rule_id,
361 p_rev_class_name => p_rev_class_name,
362 p_loading_status => x_loading_status,
363 x_loading_status => l_loading_status
364 );
365 x_loading_status := l_loading_status;
366 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
367 RAISE FND_API.G_EXC_ERROR ;
368 END IF;
369 END IF;
370 END IF;
371 -- End of API body.
372 -- Standard check of p_commit.
373 --+
374 IF FND_API.To_Boolean( p_commit ) THEN
375 COMMIT WORK;
376 END IF;
377 --+
378 -- Standard call to get message count and if count is 1, get message info.
379 --+
380 FND_MSG_PUB.Count_And_Get
381 (
382 p_count => x_msg_count ,
383 p_data => x_msg_data ,
384 p_encoded => FND_API.G_FALSE
385 );
386 EXCEPTION
387 WHEN FND_API.G_EXC_ERROR THEN
388 ROLLBACK TO Update_Trx_Factors;
389 x_return_status := FND_API.G_RET_STS_ERROR ;
390 FND_MSG_PUB.Count_And_Get
391 (
392 p_count => x_msg_count ,
393 p_data => x_msg_data ,
394 p_encoded => FND_API.G_FALSE
395 );
396 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
397 ROLLBACK TO Update_Trx_Factors;
398 x_loading_status := 'UNEXPECTED_ERR';
399 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
400 FND_MSG_PUB.Count_And_Get
401 (
402 p_count => x_msg_count ,
403 p_data => x_msg_data ,
404 p_encoded => FND_API.G_FALSE
405 );
406 WHEN OTHERS THEN
407 ROLLBACK TO Update_Trx_Factors;
408 x_loading_status := 'UNEXPECTED_ERR';
409 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
410 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
411 THEN
412 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME ,l_api_name );
413 END IF;
414 FND_MSG_PUB.Count_And_Get
415 (
416 p_count => x_msg_count ,
417 p_data => x_msg_data ,
418 p_encoded => FND_API.G_FALSE
419 );
420 END Update_Trx_Factors;
421
422
423 END CN_TRX_FACTORS_PVT ;