[Home] [Help]
PACKAGE BODY: APPS.CN_PMTPLAN_PUB
Source
1 PACKAGE BODY CN_PMTPLAN_PUB as
2 -- $Header: cnppplnb.pls 120.4 2005/11/02 05:37:38 sjustina noship $
3
4 -- -------------------------------------------------------------------------*
5 -- Procedure : Get_PmtPlan_ID
6 -- Description : This procedure is used to get the ID for the pmt plan
7 -- -------------------------------------------------------------------------*
8 PROCEDURE Get_PmtPlan_ID
9 (
10 x_return_status OUT NOCOPY VARCHAR2 ,
11 x_msg_count OUT NOCOPY NUMBER ,
12 x_msg_data OUT NOCOPY VARCHAR2 ,
13 p_PmtPlan_rec IN PmtPlan_Rec_Type,
14 p_loading_status IN VARCHAR2,
15 x_pmt_plan_id OUT NOCOPY NUMBER,
16 x_loading_status OUT NOCOPY VARCHAR2,
17 x_status OUT NOCOPY VARCHAR2
18 ) IS
19 L_PKG_NAME CONSTANT VARCHAR2(30) := 'CN_PmtPlan_PUB';
20 L_LAST_UPDATE_DATE DATE := sysdate;
21 L_LAST_UPDATED_BY NUMBER := fnd_global.user_id;
22 L_CREATION_DATE DATE := sysdate;
23 L_CREATED_BY NUMBER := fnd_global.user_id;
24 L_LAST_UPDATE_LOGIN NUMBER := fnd_global.login_id;
25 L_ROWID VARCHAR2(30);
26 L_PROGRAM_TYPE VARCHAR2(30);
27 l_api_name CONSTANT VARCHAR2(30) := 'Get_PmtPlan_ID';
28
29 CURSOR get_PmtPlan_id is
30 SELECT pmt_plan_id
31 FROM cn_pmt_plans
32 WHERE name = p_PmtPlan_rec.name
33 AND start_date = p_PmtPlan_rec.start_date
34 AND end_date = p_PmtPlan_rec.end_date
35 AND org_id = p_PmtPlan_rec.org_id;
36
37 --If end date is null, then use the following cursor
38 CURSOR get_PmtPlan_id2 is
39 SELECT pmt_plan_id
40 FROM cn_pmt_plans
41 WHERE name = p_PmtPlan_rec.name
42 AND start_date = p_PmtPlan_rec.start_date
43 AND org_id = p_PmtPlan_rec.org_id;
44
45 l_get_PmtPlan_id_rec get_PmtPlan_id%ROWTYPE;
46
47 BEGIN
48
49 -- Initialize API return status to success
50 x_return_status := FND_API.G_RET_STS_SUCCESS;
51 x_loading_status := p_loading_status;
52 x_status := p_loading_status ;
53
54
55 --Open appropriate cursor and fetch the payment plan ID
56 IF p_PmtPlan_rec.end_date IS NOT NULL
57 THEN
58 OPEN get_PmtPlan_id;
59 FETCH get_PmtPlan_id INTO l_get_PmtPlan_id_rec;
60 IF get_PmtPlan_id%ROWCOUNT = 0
61 THEN
62 x_status := 'NEW PMT PLAN';
63 SELECT cn_pmt_plans_s.nextval
64 INTO x_pmt_plan_id
65 FROM dual;
66 ELSIF get_PmtPlan_id%ROWCOUNT = 1
67 THEN
68 x_status := 'PMT PLAN EXISTS';
69 x_pmt_plan_id := l_get_PmtPlan_id_rec.pmt_plan_id;
70 END IF;
71 CLOSE get_PmtPlan_id;
72 ELSE
73 OPEN get_PmtPlan_id2;
74 FETCH get_PmtPlan_id2 INTO l_get_PmtPlan_id_rec;
75 IF get_PmtPlan_id2%ROWCOUNT = 0
76 THEN
77 x_status := 'NEW PMT PLAN';
78 SELECT cn_pmt_plans_s.nextval
79 INTO x_pmt_plan_id
80 FROM dual;
81 ELSIF get_PmtPlan_id2%ROWCOUNT = 1
82 THEN
83 x_status := 'PMT PLAN EXISTS';
84 x_pmt_plan_id := l_get_PmtPlan_id_rec.pmt_plan_id;
85 END IF;
86 CLOSE get_PmtPlan_id2;
87 END IF;
88
89 EXCEPTION
90 WHEN OTHERS THEN
91 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
92 x_loading_status := 'UNEXPECTED_ERR';
93 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
94 THEN
95 FND_MSG_PUB.Add_Exc_Msg( L_PKG_NAME ,l_api_name );
96 END IF;
97
98 END Get_PmtPlan_ID;
99
100
101 --------------------------------------------------------------------------*
102 -- Procedure : Create_PmtPlan
103 -- Description: Public API to create a pmt plan
104 -- Calls : CN_PMTPLAN_PVT.Create_PmtPlan
105 --------------------------------------------------------------------------*
106 PROCEDURE Create_PmtPlan(
107 p_api_version IN NUMBER,
108 p_init_msg_list IN VARCHAR2 ,
109 p_commit IN VARCHAR2,
110 p_validation_level IN NUMBER,
111 x_return_status OUT NOCOPY VARCHAR2,
112 x_msg_count OUT NOCOPY NUMBER,
113 x_msg_data OUT NOCOPY VARCHAR2,
114 p_PmtPlan_rec IN OUT NOCOPY PmtPlan_Rec_Type,
115 x_loading_status OUT NOCOPY VARCHAR2,
116 x_status OUT NOCOPY VARCHAR2
117 ) IS
118
119 L_PKG_NAME CONSTANT VARCHAR2(30) := 'CN_PmtPlan_PUB';
120 l_api_name CONSTANT VARCHAR2(30) := 'Create_PmtPlan';
121 l_api_version CONSTANT NUMBER := 1.0;
122
123
124 l_create_rec cn_pmtplan_pvt.PmtPlan_rec_type;
125 l_payment_grp_code varchar2(40);
126 l_status VARCHAR2(1);
127
128 BEGIN
129
130 -- Copy the Record values into l_create_rec and call private
131
132 l_create_rec.org_id := p_PmtPlan_rec.org_id;
133 mo_global.validate_orgid_pub_api(org_id => l_create_rec.org_id,status =>l_status);
134 if (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
135 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'cn.plsql.cn_pmtplan_pub.Create_PmtPlan.org_validate',
136 'Validated org_id = ' || l_create_rec.org_id || ' status ='||l_status);
137 end if;
138
139 l_create_rec.name := p_PmtPlan_rec.name;
140 l_create_rec.minimum_amount := p_PmtPlan_rec.minimum_amount;
141 l_create_rec.maximum_amount := p_PmtPlan_rec.maximum_amount;
142 l_create_rec.min_rec_flag := p_PmtPlan_rec.min_rec_flag;
143 l_create_rec.max_rec_flag := p_PmtPlan_rec.max_rec_flag;
144 l_create_rec.max_recovery_amount := p_PmtPlan_rec.max_recovery_amount;
145 l_create_rec.credit_type_name := p_PmtPlan_rec.credit_type_name;
146 l_create_rec.pay_interval_type_name := p_PmtPlan_rec.pay_interval_type_name;
147 l_create_rec.start_date := p_PmtPlan_rec.start_date;
148 l_create_rec.end_date := p_PmtPlan_rec.end_date;
149 l_create_rec.object_version_number := 1;
150 l_create_rec.recoverable_interval_type := p_PmtPlan_rec.recoverable_interval_type;
151 l_create_rec.pay_against_commission := p_PmtPlan_rec.pay_against_commission;
152 l_create_rec.payment_group_code := p_PmtPlan_rec.payment_group_code;
153
154 if ((p_PmtPlan_rec.pay_against_commission <> 'Y' and
155 p_PmtPlan_rec.pay_against_commission <> 'N')
156 or p_PmtPlan_rec.pay_against_commission is null)
157 then
158 l_create_rec.pay_against_commission := 'Y';
159 end if;
160
161
162 CN_PMTPLAN_PVT.Create_PmtPlan
163 (p_api_version => p_api_version,
164 p_init_msg_list => p_init_msg_list,
165 p_commit => p_commit,
166 p_validation_level => p_validation_level,
167 x_return_status => x_return_status,
168 x_msg_count => x_msg_count,
169 x_msg_data => x_msg_data,
170 p_PmtPlan_rec => l_create_rec,
171 x_loading_status => x_loading_status,
172 x_status => x_status);
173
174 -- End of Create Pmt Plans.
175 -- Standard call to get message count and if count is 1,
176 -- get message info.
177
178 FND_MSG_PUB.Count_And_Get
179 (
180 p_count => x_msg_count,
181 p_data => x_msg_data,
182 p_encoded => FND_API.G_FALSE
183 );
184
185 EXCEPTION
186 WHEN FND_API.G_EXC_ERROR THEN
187 x_return_status := FND_API.G_RET_STS_ERROR ;
188
189 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
190 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
191 x_loading_status := 'UNEXPECTED_ERR';
192
193 WHEN OTHERS THEN
194 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
195 x_loading_status := 'UNEXPECTED_ERR';
196 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
197 THEN
198 FND_MSG_PUB.Add_Exc_Msg( L_PKG_NAME ,l_api_name );
199 END IF;
200
201 END Create_PmtPlan;
202
203 ---------------------------------------------------------------------------*
204 -- Procedure : Update PmtPlan
205 -- Description : This is a public procedure to update pmt plans
206 -- Calls : CN_PMTPLAN_PVT.Update_PmtPlan
207 ---------------------------------------------------------------------------*
208
209 PROCEDURE Update_PmtPlan (
210 p_api_version IN NUMBER,
211 p_init_msg_list IN VARCHAR2,
212 p_commit IN VARCHAR2,
213 p_validation_level IN NUMBER,
214 x_return_status OUT NOCOPY VARCHAR2,
215 x_msg_count OUT NOCOPY NUMBER,
216 x_msg_data OUT NOCOPY VARCHAR2,
217 p_old_PmtPlan_rec IN PmtPlan_rec_type,
218 p_PmtPlan_rec IN OUT NOCOPY PmtPlan_rec_type,
219 x_status OUT NOCOPY VARCHAR2,
220 x_loading_status OUT NOCOPY VARCHAR2
221 ) IS
222
223 l_api_name CONSTANT VARCHAR2(30) := 'Update_PmtPlan';
224 l_api_version CONSTANT NUMBER := 1.0;
225 l_PmtPlans_rec PmtPlan_rec_type;
226 l_pmt_plan_id NUMBER;
227 l_credit_type_id NUMBER;
228 l_pay_interval_type_id NUMBER;
229 l_count NUMBER := 0;
230 l_start_date DATE;
231 l_end_date DATE;
232
233 L_PKG_NAME CONSTANT VARCHAR2(30) := 'CN_PmtPlan_PUB';
234 L_LAST_UPDATE_DATE DATE := sysdate;
235 L_LAST_UPDATED_BY NUMBER := fnd_global.user_id;
236 L_CREATION_DATE DATE := sysdate;
237 L_CREATED_BY NUMBER := fnd_global.user_id;
238 L_LAST_UPDATE_LOGIN NUMBER := fnd_global.login_id;
239 L_ROWID VARCHAR2(30);
240 L_PROGRAM_TYPE VARCHAR2(30);
241
242 l_update_rec cn_pmtplan_pvt.PmtPlan_rec_type;
243 l_update_old_rec cn_pmtplan_pvt.PmtPlan_rec_type;
244 l_status VARCHAR2(1);
245 cursor get_start_date is
246 select start_date from cn_pmt_plans
247 where name = p_PmtPlan_rec.name
248 and org_id = p_PmtPlan_rec.org_id;
249
250 BEGIN
251 -- Standard Start of API savepoint
252 SAVEPOINT Update_PmtPlan;
253 -- Standard call to check for call compatibility.
254 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
255 p_api_version ,
256 l_api_name ,
257 L_PKG_NAME )
258 THEN
259 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
260 END IF;
261 -- Initialize message list if p_init_msg_list is set to TRUE.
262 IF FND_API.to_Boolean( p_init_msg_list ) THEN
263 FND_MSG_PUB.initialize;
264 END IF;
265 -- Initialize API return status to success
266 x_return_status := FND_API.G_RET_STS_SUCCESS;
267 x_loading_status := 'CN_UPDATED';
268 -- API body
269
270 --Initialize g_mode
271 g_mode := 'UPDATE';
272
273 -- copy the old record values
274 l_update_old_rec.org_id := p_old_PmtPlan_rec.org_id;
275 --mo_global.validate_orgid_pub_api(org_id => l_update_old_rec.org_id,status =>l_status);
276 l_update_old_rec.name := p_old_PmtPlan_rec.name;
277 l_update_old_rec.minimum_amount := p_old_PmtPlan_rec.minimum_amount;
278 l_update_old_rec.maximum_amount := p_old_PmtPlan_rec.maximum_amount;
279 l_update_old_rec.min_rec_flag := p_old_PmtPlan_rec.min_rec_flag;
280 l_update_old_rec.max_rec_flag := p_old_PmtPlan_rec.max_rec_flag;
281 l_update_old_rec.max_recovery_amount := p_old_PmtPlan_rec.max_recovery_amount;
282 l_update_old_rec.credit_type_name := p_old_PmtPlan_rec.credit_type_name;
283 l_update_old_rec.pay_interval_type_name := p_old_PmtPlan_rec.pay_interval_type_name;
284 l_update_old_rec.start_date := p_old_PmtPlan_rec.start_date;
285 l_update_old_rec.end_date := p_old_PmtPlan_rec.end_date;
286 l_update_old_rec.object_version_number := p_old_PmtPlan_rec.object_version_number;
287 l_update_old_rec.recoverable_interval_type := p_old_PmtPlan_rec.recoverable_interval_type;
288 l_update_old_rec.pay_against_commission := p_old_PmtPlan_rec.pay_against_commission;
289 l_update_old_rec.payment_group_code := p_old_PmtPlan_rec.payment_group_code;
290
291 IF (p_old_PmtPlan_rec.start_date is null)
292 THEN
293 OPEN get_start_date;
294 FETCH get_start_date INTO l_update_old_rec.start_date;
295 END IF;
296
297 get_PmtPlan_id(
298 x_return_status => x_return_status,
299 x_msg_count => x_msg_count,
300 x_msg_data => x_msg_data,
301 p_PmtPlan_rec => p_old_PmtPlan_rec,
302 x_pmt_plan_id => l_pmt_plan_id,
303 p_loading_status => x_loading_status,
304 x_loading_status => x_loading_status,
305 x_status => x_status
306 );
307
308 l_update_old_rec.pmt_plan_id := l_pmt_plan_id;
309
310 -- Copy the new Record values into l_update_rec and call private
311
312 l_update_rec.org_id := p_PmtPlan_rec.org_id;
313 mo_global.validate_orgid_pub_api(org_id => l_update_rec.org_id,status =>l_status);
314 if (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
315 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'cn.plsql.cn_pmtplan_pub.Update_PmtPlan.org_validate',
316 'Validated org_id = ' || l_update_rec.org_id || ' status ='||l_status);
317 end if;
318
319 if (l_update_rec.org_id <> l_update_old_rec.org_id ) then
320 FND_MESSAGE.SET_NAME ('FND' , 'FND_MO_OU_CANNOT_UPDATE');
321 if (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
322 FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR,
323 'cn.plsql.cn_paygroup_pub.update_PmtPlan.error',
324 true);
325 end if;
326
327 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
328 FND_MESSAGE.SET_NAME ('FND' , 'FND_MO_OU_CANNOT_UPDATE');
329 FND_MSG_PUB.Add;
330 END IF;
331
332 RAISE FND_API.G_EXC_ERROR ;
333 end if;
334 l_update_rec.name := p_PmtPlan_rec.name;
335 l_update_rec.minimum_amount := p_PmtPlan_rec.minimum_amount;
336 l_update_rec.maximum_amount := p_PmtPlan_rec.maximum_amount;
337 l_update_rec.min_rec_flag := p_PmtPlan_rec.min_rec_flag;
338 l_update_rec.max_rec_flag := p_PmtPlan_rec.max_rec_flag;
339 l_update_rec.max_recovery_amount := p_PmtPlan_rec.max_recovery_amount;
340 l_update_rec.credit_type_name := p_PmtPlan_rec.credit_type_name;
341 l_update_rec.pay_interval_type_name := p_PmtPlan_rec.pay_interval_type_name;
342 l_update_rec.start_date := p_PmtPlan_rec.start_date;
343 l_update_rec.end_date := p_PmtPlan_rec.end_date;
344 l_update_rec.object_version_number := p_PmtPlan_rec.object_version_number;
345 l_update_rec.recoverable_interval_type := p_PmtPlan_rec.recoverable_interval_type;
346 l_update_rec.pay_against_commission := p_PmtPlan_rec.pay_against_commission;
347 l_update_rec.payment_group_code := p_PmtPlan_rec.payment_group_code;
348
349 get_PmtPlan_id(
350 x_return_status => x_return_status,
351 x_msg_count => x_msg_count,
352 x_msg_data => x_msg_data,
353 p_PmtPlan_rec => p_PmtPlan_rec,
354 x_pmt_plan_id => l_pmt_plan_id,
355 p_loading_status => x_loading_status,
356 x_loading_status => x_loading_status,
357 x_status => x_status
358 );
359
360 l_update_rec.pmt_plan_id := l_pmt_plan_id;
361 CN_PMTPLAN_PVT.Update_PmtPlan
362 (p_api_version => p_api_version,
363 p_init_msg_list => p_init_msg_list,
364 p_commit => p_commit,
365 p_validation_level => p_validation_level,
366 x_return_status => x_return_status,
367 x_msg_count => x_msg_count,
368 x_msg_data => x_msg_data,
372 x_loading_status => x_loading_status
369 p_old_PmtPlan_rec => l_update_old_rec,
370 p_PmtPlan_rec => l_update_rec,
371 x_status => x_status,
373 );
374
375 -- End of API body.
376 -- Standard check of p_commit.
377 IF FND_API.To_Boolean( p_commit ) THEN
378 COMMIT WORK;
379 END IF;
380 -- Standard call to get message count and if count is 1, get message info.
381 FND_MSG_PUB.Count_And_Get
382 (
383 p_count => x_msg_count ,
384 p_data => x_msg_data ,
385 p_encoded => FND_API.G_FALSE
386 );
387 EXCEPTION
388 WHEN FND_API.G_EXC_ERROR THEN
389 ROLLBACK TO Update_PmtPlan;
390 x_return_status := FND_API.G_RET_STS_ERROR ;
391 FND_MSG_PUB.Count_And_Get
392 (
393 p_count => x_msg_count ,
394 p_data => x_msg_data ,
395 p_encoded => FND_API.G_FALSE
396 );
397 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
398 ROLLBACK TO Update_PmtPlan;
399 x_loading_status := 'UNEXPECTED_ERR';
400 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
401 FND_MSG_PUB.Count_And_Get
402 (
403 p_count => x_msg_count ,
404 p_data => x_msg_data ,
405 p_encoded => FND_API.G_FALSE
406 );
407 WHEN OTHERS THEN
408 ROLLBACK TO Update_PmtPlan;
409 x_loading_status := 'UNEXPECTED_ERR';
410 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
411 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
412 THEN
413 FND_MSG_PUB.Add_Exc_Msg( L_PKG_NAME ,l_api_name );
414 END IF;
415 FND_MSG_PUB.Count_And_Get
416 (
417 p_count => x_msg_count ,
418 p_data => x_msg_data ,
419 p_encoded => FND_API.G_FALSE
420 );
421 END Update_PmtPlan;
422
423 ---------------------------------------------------------------------------*
424 -- Procedure Name : Delete Pmt Plans
425 ---------------------------------------------------------------------------*
426 PROCEDURE Delete_PmtPlan
427 ( p_api_version IN NUMBER,
428 p_init_msg_list IN VARCHAR2,
429 p_commit IN VARCHAR2,
430 p_validation_level IN NUMBER,
431 x_return_status OUT NOCOPY VARCHAR2,
432 x_msg_count OUT NOCOPY NUMBER,
433 x_msg_data OUT NOCOPY VARCHAR2,
434 p_PmtPlan_rec IN PmtPlan_rec_type ,
435 x_status OUT NOCOPY VARCHAR2,
436 x_loading_status OUT NOCOPY VARCHAR2
437 ) IS
438
439 l_api_name CONSTANT VARCHAR2(30) := 'Delete_PmtPlan';
440 l_api_version CONSTANT NUMBER := 1.0;
441 L_PKG_NAME CONSTANT VARCHAR2(30) := 'CN_PmtPlan_PUB';
442
443 l_create_rec cn_pmtplan_pvt.PmtPlan_rec_type;
444 l_start_date cn_pmt_plans.start_date%TYPE;
445 l_status VARCHAR2(1);
446 cursor get_start_date is
447 select start_date from cn_pmt_plans
448 where name = p_PmtPlan_rec.name
449 and org_id = p_PmtPlan_rec.org_id;
450
451 BEGIN
452
453 -- Get the start date for payment plan using the payment plan name and org id
454 l_create_rec.org_id := p_PmtPlan_rec.org_id;
455 mo_global.validate_orgid_pub_api(org_id => l_create_rec.org_id,status =>l_status);
456 if (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
457 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'cn.plsql.cn_pmtplan_pub.delete_PmtPlan.org_validate',
458 'Validated org_id = ' || l_create_rec.org_id || ' status ='||l_status);
459 end if;
460 l_create_rec.name := p_PmtPlan_rec.name;
461 l_create_rec.start_date := p_PmtPlan_rec.start_date;
462
463 IF (p_PmtPlan_rec.start_date is null)
464 THEN
465 OPEN get_start_date;
466 FETCH get_start_date INTO l_create_rec.start_date;
467 END IF;
468
469 -- call the private package for deleting the payment plan
470 CN_PMTPLAN_PVT.Delete_PmtPlan
471 (p_api_version => p_api_version,
472 p_init_msg_list => p_init_msg_list,
473 p_commit => p_commit,
474 p_validation_level => p_validation_level,
475 x_return_status => x_return_status,
476 x_msg_count => x_msg_count,
477 x_msg_data => x_msg_data,
478 p_PmtPlan_rec => l_create_rec,
479 x_status => x_status,
480 x_loading_status => x_loading_status);
481
482 END Delete_PmtPlan;
483
484 END CN_PmtPlan_PUB ;