[Home] [Help]
PACKAGE BODY: APPS.CN_SRP_PMT_PLANS_PUB
Source
1 PACKAGE BODY CN_SRP_PMT_PLANS_PUB AS
2 /* $Header: cnpsppab.pls 120.4 2005/10/27 16:03:49 mblum noship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'CN_SRP_PMT_PLANS_PUB';
5 G_FILE_NAME CONSTANT VARCHAR2(12) := 'cnpsppab.pls';
6
7 G_EMP_NUM CONSTANT VARCHAR2(80)
8 := cn_api.get_lkup_meaning('EMP_NUM','SRP_OBJECT_TYPE');
9 G_PMT_PLAN CONSTANT VARCHAR2(80)
10 := cn_api.get_lkup_meaning('PMT_PLAN','SRP_OBJECT_TYPE');
11 G_SALESREP CONSTANT VARCHAR2(80)
12 := cn_api.get_lkup_meaning('SALESREP','SRP_OBJECT_TYPE');
13
14 --| ----------------------------------------------------------------------+-+
15 --| Procedure : chk_existence_get_id
16 --| Desc : Procedure to get ids and check existence
17 --| ----------------------------------------------------------------------+
18 PROCEDURE chk_existence_get_id
19 (
20 x_return_status OUT NOCOPY VARCHAR2 ,
21 x_msg_count OUT NOCOPY NUMBER ,
22 x_msg_data OUT NOCOPY VARCHAR2 ,
23 p_srp_pmt_plans_rec IN srp_pmt_plans_rec_type,
24 x_srp_pmt_plans_row IN OUT NOCOPY cn_srp_pmt_plans%ROWTYPE ,
25 x_srp_start_date OUT NOCOPY cn_salesreps.start_date_active%TYPE,
26 x_srp_end_date OUT NOCOPY cn_salesreps.end_date_active%TYPE,
27 x_pp_start_date OUT NOCOPY cn_pmt_plans.start_date%TYPE,
28 x_pp_end_date OUT NOCOPY cn_pmt_plans.end_date%TYPE,
29 p_action IN VARCHAR2 := 'VALIDATE',
30 p_loading_status IN VARCHAR2,
31 x_loading_status OUT NOCOPY VARCHAR2
32 )
33 IS
34 l_api_name CONSTANT VARCHAR2(30) := 'chk_existence_get_id';
35 l_dummy NUMBER;
36 l_loading_status varchar2(50);
37
38 BEGIN
39 -- Initialize API return status to success
40 x_return_status := FND_API.G_RET_STS_SUCCESS;
41 x_loading_status := p_loading_status;
42 -- API body
43 -- Check payment plan cannot null or missing
44 IF (cn_api.chk_miss_null_char_para
45 (p_char_para => p_srp_pmt_plans_rec.pmt_plan_name,
46 p_obj_name => G_PMT_PLAN,
47 p_loading_status => x_loading_status,
48 x_loading_status => x_loading_status) = FND_API.G_TRUE) THEN
49 RAISE FND_API.G_EXC_ERROR ;
50 END IF;
51
52 -- migrate org ID
53 x_srp_pmt_plans_row.org_id := p_srp_pmt_plans_rec.org_id;
54
55 -- Check if Payment Plan exist
56 BEGIN
57 SELECT pmt_plan_id, credit_type_id,
58 start_date, end_date
59 INTO x_srp_pmt_plans_row.pmt_plan_id,
60 x_srp_pmt_plans_row.credit_type_id,
61 x_pp_start_date,x_pp_end_date
62 FROM cn_pmt_plans_all
63 WHERE name = p_srp_pmt_plans_rec.pmt_plan_name
64 AND org_id = p_srp_pmt_plans_rec.org_id;
65 EXCEPTION
66 WHEN NO_DATA_FOUND THEN
67 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
68 FND_MESSAGE.SET_NAME ('CN' , 'CN_PP_NOT_EXIST');
69 FND_MESSAGE.SET_TOKEN('PP_NAME',p_srp_pmt_plans_rec.pmt_plan_name);
70 FND_MSG_PUB.Add;
71 END IF;
72 x_loading_status := 'CN_PP_NOT_EXIST';
73 RAISE FND_API.G_EXC_ERROR ;
74 END;
75 -- Check if Salesrep exist
76 cn_api.chk_and_get_salesrep_id
77 (p_emp_num => p_srp_pmt_plans_rec.emp_num
78 ,p_type => p_srp_pmt_plans_rec.salesrep_type
79 ,p_org_id => p_srp_pmt_plans_rec.org_id
80 ,x_salesrep_id => x_srp_pmt_plans_row.salesrep_id
81 ,x_return_status => x_return_status
82 ,x_loading_status => l_loading_status);
83 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
84 x_loading_status := l_loading_status;
85 RAISE FND_API.G_EXC_ERROR ;
86 END IF;
87
88 -- get salesrep start date, end date
89 BEGIN
90 SELECT start_date_active,end_date_active
91 INTO x_srp_start_date,x_srp_end_date
92 FROM cn_salesreps
93 WHERE salesrep_id = x_srp_pmt_plans_row.salesrep_id
94 AND org_id = x_srp_pmt_plans_row.org_id;
95 EXCEPTION
96 WHEN NO_DATA_FOUND THEN
97 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
98 FND_MESSAGE.SET_NAME ('CN' , 'CN_SRP_NOT_EXIST');
99 FND_MSG_PUB.Add;
100 END IF;
101 x_loading_status := 'CN_SRP_NOT_EXIST';
102 RAISE FND_API.G_EXC_ERROR ;
103 END;
104
105 -- If p_action = 'GETOLDREC'
106 -- called by update(), need to get old_rec data
107 BEGIN
108 IF p_action = 'GETOLDREC' THEN
109 SELECT *
110 INTO x_srp_pmt_plans_row
111 FROM cn_srp_pmt_plans_all
112 WHERE pmt_plan_id = x_srp_pmt_plans_row.pmt_plan_id
113 AND salesrep_id = x_srp_pmt_plans_row.salesrep_id
114 AND trunc(start_date) = trunc(p_srp_pmt_plans_rec.start_date)
115 ;
116 END IF;
117 EXCEPTION
118 WHEN NO_DATA_FOUND THEN
119 x_srp_pmt_plans_row.srp_pmt_plan_id := NULL;
120 END;
121 -- End of API body.
122 EXCEPTION
123 WHEN FND_API.G_EXC_ERROR THEN
124 x_return_status := FND_API.G_RET_STS_ERROR ;
125 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
126 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
127 x_loading_status := 'UNEXPECTED_ERR';
128 WHEN OTHERS THEN
129 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
130 x_loading_status := 'UNEXPECTED_ERR';
131 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
132 THEN
133 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME ,l_api_name );
134 END IF;
135 END chk_existence_get_id;
136
137 --| ----------------------------------------------------------------------+-+
138 --| Procedure : valid_pp_assign
139 --| Desc : Procedure to validate pmt plan assignment to a salesrep
140 --| ----------------------------------------------------------------------+
141 PROCEDURE valid_pp_assign
142 (
143 x_return_status OUT NOCOPY VARCHAR2 ,
144 x_msg_count OUT NOCOPY NUMBER ,
145 x_msg_data OUT NOCOPY VARCHAR2 ,
146 p_srp_pmt_plans_rec IN srp_pmt_plans_rec_type,
147 x_srp_pmt_plans_row IN OUT NOCOPY cn_srp_pmt_plans%ROWTYPE ,
148 p_action IN VARCHAR2,
149 p_loading_status IN VARCHAR2 ,
150 x_loading_status OUT NOCOPY VARCHAR2
151 )
152 IS
153 l_api_name CONSTANT VARCHAR2(30) := 'valid_pp_assign';
154 --Bug 3432689 by Julia Huang on 4/29/04.
155
156 l_dummy NUMBER;
157 l_dummy2 NUMBER;
158 l_srp_start_date cn_salesreps.start_date_active%TYPE;
159 l_srp_end_date cn_salesreps.end_date_active%TYPE;
160 l_pp_start_date cn_pmt_plans.start_date%TYPE;
161 l_pp_end_date cn_pmt_plans.end_date%TYPE;
162 l_pp_min cn_pmt_plans.minimum_amount%TYPE;
163 l_pp_max cn_pmt_plans.maximum_amount%TYPE;
164 l_payment_group_code cn_pmt_plans.payment_group_code%TYPE;
165
166 -- Cursor to get the quota id and credit type id list from
167 -- cn_srp_plan_assigns which falls into
168 -- the date range for credit_type_id check.
169 CURSOR get_quota_ids_csr (l_salesrep_id NUMBER,
170 l_start_date DATE, l_end_date DATE) IS
171 SELECT cq.quota_id,cq.credit_type_id
172 FROM cn_srp_plan_assigns cspa, cn_srp_quota_assigns csqa , cn_quotas cq
173 WHERE cspa.salesrep_id = l_salesrep_id
174 AND cspa.start_date <= l_start_date
175 --Bug 3432689 by Julia Huang on 4/29/04.
176 --AND nvl(l_end_date,l_null_date) <= nvl(cspa.end_date,l_null_date)
177 AND nvl(l_end_date, nvl(cspa.end_date, l_start_date)) <= nvl(cspa.end_date, nvl(l_end_date,l_start_date))
178 AND csqa.srp_plan_assign_id = cspa.srp_plan_assign_id
179 AND csqa.quota_id = cq.quota_id;
180
181 BEGIN
182 -- Initialize API return status to success
183 x_return_status := FND_API.G_RET_STS_SUCCESS;
184 x_loading_status := p_loading_status;
185
186 -- API body
187 -- Get IDs by using Names. Also check the existence
188 chk_existence_get_id
189 ( x_return_status => x_return_status,
190 x_msg_count => x_msg_count,
191 x_msg_data => x_msg_data,
192 p_srp_pmt_plans_rec => p_srp_pmt_plans_rec,
193 x_srp_pmt_plans_row => x_srp_pmt_plans_row,
194 x_srp_start_date => l_srp_start_date,
195 x_srp_end_date => l_srp_end_date,
196 x_pp_start_date => l_pp_start_date,
197 x_pp_end_date => l_pp_end_date,
198 p_action => p_action,
199 p_loading_status => x_loading_status,
200 x_loading_status => x_loading_status
201 );
202 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
203 RAISE FND_API.G_EXC_ERROR ;
204 ELSIF x_srp_pmt_plans_row.srp_pmt_plan_id IS NOT NULL AND
205 p_action <> 'UPDATE' THEN
206 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
207 FND_MESSAGE.Set_Name('CN', 'CN_SRP_PMT_PLAN_EXIST');
208 FND_MSG_PUB.Add;
209 END IF;
210 x_loading_status := 'CN_SRP_PMT_PLAN_EXIST';
211 RAISE FND_API.G_EXC_ERROR ;
212 END IF;
213
214 -- Get correct start date if it's null
215 IF p_srp_pmt_plans_rec.start_date IS NULL THEN
216 IF l_srp_start_date < l_pp_start_date THEN
217 x_srp_pmt_plans_row.start_date := l_pp_start_date;
218 ELSE
219 x_srp_pmt_plans_row.start_date := l_srp_start_date;
220 END IF;
221 ELSE
222 x_srp_pmt_plans_row.start_date := p_srp_pmt_plans_rec.start_date;
223 END IF ;
224
225 x_srp_pmt_plans_row.end_date := p_srp_pmt_plans_rec.end_date;
226
227
228 -- Get correct min/max amount if it's null
229 SELECT minimum_amount, maximum_amount
230 INTO l_pp_min, l_pp_max
231 FROM cn_pmt_plans
232 WHERE pmt_plan_id = x_srp_pmt_plans_row.pmt_plan_id;
233
234 SELECT
235 Decode(p_srp_pmt_plans_rec.minimum_amount,FND_API.G_MISS_NUM,l_pp_min,
236 p_srp_pmt_plans_rec.minimum_amount),
237 Decode(p_srp_pmt_plans_rec.maximum_amount,FND_API.G_MISS_NUM,l_pp_max,
238 p_srp_pmt_plans_rec.maximum_amount)
239 INTO
240 x_srp_pmt_plans_row.minimum_amount,
241 x_srp_pmt_plans_row.maximum_amount
242 FROM dual;
243
244 -- End of API body.
245 EXCEPTION
246 WHEN FND_API.G_EXC_ERROR THEN
247 x_return_status := FND_API.G_RET_STS_ERROR ;
248 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
249 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
250 x_loading_status := 'UNEXPECTED_ERR';
251 WHEN OTHERS THEN
252 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
253 x_loading_status := 'UNEXPECTED_ERR';
254 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
255 THEN
256 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME ,l_api_name );
257 END IF;
258 END valid_pp_assign;
259
260 --| ----------------------------------------------------------------------+-+
261 --| Procedure : Create_Srp_Pmt_Plan
262 --| Desc : Procedure to create a new payment plan assignment to salesrep
263 --| ----------------------------------------------------------------------+
264
265 PROCEDURE Create_Srp_Pmt_Plan
266 (
267 p_api_version IN NUMBER,
268 p_init_msg_list IN VARCHAR2,
269 p_commit IN VARCHAR2,
270 p_validation_level IN NUMBER,
271 x_return_status OUT NOCOPY VARCHAR2,
272 x_msg_count OUT NOCOPY NUMBER,
273 x_msg_data OUT NOCOPY VARCHAR2,
274 p_srp_pmt_plans_rec IN srp_pmt_plans_rec_type,
275 x_srp_pmt_plan_id OUT NOCOPY NUMBER,
276 x_loading_status OUT NOCOPY VARCHAR2
277 ) IS
278
279 l_api_name CONSTANT VARCHAR2(30) := 'Create_Srp_Pmt_Plan';
280 l_api_version CONSTANT NUMBER := 1.0;
281 l_spp_rec srp_pmt_plans_rec_type;
282 l_spp_row cn_srp_pmt_plans%ROWTYPE ;
283
284 l_pmt_plan_id cn_srp_pmt_plans.pmt_plan_id%TYPE;
285 l_salesrep_id cn_srp_pmt_plans.salesrep_id%TYPE;
286 l_start_date cn_srp_pmt_plans.start_date%TYPE;
287 l_end_date cn_srp_pmt_plans.end_date%TYPE;
288 l_action VARCHAR2(30) := 'CREATE';
289 l_create_rec cn_srp_pmt_plans_pvt.pmt_plan_assign_rec;
290 l_org_id NUMBER;
291 l_status VARCHAR2(1);
292
293 -- Declaration for user hooks
294 l_OAI_array JTF_USR_HKS.oai_data_array_type;
295 l_bind_data_id NUMBER ;
296
297 BEGIN
298 -- Standard Start of API savepoint
299 SAVEPOINT Create_Srp_Pmt_Plan;
300 -- Standard call to check for call compatibility.
301 IF NOT FND_API.compatible_api_call
302 ( l_api_version ,p_api_version ,l_api_name ,G_PKG_NAME )
303 THEN
304 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
305 END IF;
306 -- Initialize message list if p_init_msg_list is set to TRUE.
307 IF FND_API.to_Boolean( p_init_msg_list ) THEN
308 FND_MSG_PUB.initialize;
309 END IF;
310 -- Initialize API return status to success
311 x_return_status := FND_API.G_RET_STS_SUCCESS;
312 x_loading_status := 'CN_INSERTED';
313 -- Assign the parameter to a local variable
314 l_spp_rec := p_srp_pmt_plans_rec;
315
316 --
317 -- API body
318 --
319
320 --
321 --Validate org id
322 --
323 l_org_id := l_spp_rec.org_id;
324 mo_global.validate_orgid_pub_api
325 (org_id => l_org_id,
326 status => l_status);
327
328 if (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
329 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
330 'cn.plsql.cn_srp_pmt_plans_pub.create_srp_pmt_plan.org_validate',
331 'Validated org_id = ' || l_org_id || ' status = ' || l_status);
332 end if;
333 l_spp_rec.org_id := l_org_id;
334
335 -- Trim spaces before/after user input string, get Value-Id para assigned
336 SELECT
337 Decode(p_srp_pmt_plans_rec.pmt_plan_name,
338 FND_API.G_MISS_CHAR, NULL ,
339 Ltrim(Rtrim(p_srp_pmt_plans_rec.pmt_plan_name))),
340 Decode(p_srp_pmt_plans_rec.salesrep_type,
341 FND_API.G_MISS_CHAR, NULL ,
342 Ltrim(Rtrim(p_srp_pmt_plans_rec.salesrep_type))),
343 Decode(p_srp_pmt_plans_rec.emp_num,
344 FND_API.G_MISS_CHAR, NULL ,
345 Ltrim(Rtrim(p_srp_pmt_plans_rec.emp_num))),
346 Decode(p_srp_pmt_plans_rec.start_date,
347 FND_API.G_MISS_DATE,To_date(NULL) ,
348 trunc(p_srp_pmt_plans_rec.start_date)),
349 Decode(p_srp_pmt_plans_rec.end_date,
350 FND_API.G_MISS_DATE,To_date(NULL) ,
351 trunc(p_srp_pmt_plans_rec.end_date))
355 l_spp_rec.emp_num,
352 INTO
353 l_spp_rec.pmt_plan_name,
354 l_spp_rec.salesrep_type,
356 l_spp_rec.start_date,
357 l_spp_rec.end_date
358 FROM dual;
359 --
360 -- Valid payment plan assignment
361 --
362 valid_pp_assign
363 ( x_return_status => x_return_status,
364 x_msg_count => x_msg_count,
365 x_msg_data => x_msg_data,
366 p_srp_pmt_plans_rec => l_spp_rec,
367 x_srp_pmt_plans_row => l_spp_row,
368 p_action => l_action,
369 p_loading_status => x_loading_status,
370 x_loading_status => x_loading_status
371 );
372 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
373 RAISE FND_API.G_EXC_ERROR ;
374 END IF;
375
376 --
377 -- User hooks
378 --
379 IF JTF_USR_HKS.Ok_to_Execute('CN_SRP_PMT_PLANS_PUB',
380 'CREATE_SRP_PMT_PLAN',
381 'B',
382 'C')
383 THEN
384 cn_srp_pmt_plans_pub_cuhk.create_srp_pmt_plan_pre
385 (p_api_version => p_api_version,
386 p_init_msg_list => fnd_api.g_false,
387 p_commit => fnd_api.g_false,
388 p_validation_level => p_validation_level,
389 x_return_status => x_return_status,
390 x_msg_count => x_msg_count,
391 x_msg_data => x_msg_data,
392 p_srp_pmt_plans_rec => l_spp_rec,
393 x_srp_pmt_plan_id => x_srp_pmt_plan_id,
394 x_loading_status => x_loading_status
395 );
396
397 IF ( x_return_status = FND_API.G_RET_STS_ERROR )
398 THEN
399 RAISE FND_API.G_EXC_ERROR;
400 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR )
401 THEN
402 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
403 END IF;
404 END IF;
405
406 IF JTF_USR_HKS.Ok_to_Execute('CN_SRP_PMT_PLANS_PUB',
407 'CREATE_SRP_PMT_PLAN',
408 'B',
409 'V')
410 THEN
411 cn_srp_pmt_plans_pub_vuhk.create_srp_pmt_plan_pre
412 (p_api_version => p_api_version,
413 p_init_msg_list => fnd_api.g_false,
414 p_commit => fnd_api.g_false,
415 p_validation_level => p_validation_level,
416 x_return_status => x_return_status,
417 x_msg_count => x_msg_count,
418 x_msg_data => x_msg_data,
419 p_srp_pmt_plans_rec => l_spp_rec,
420 x_srp_pmt_plan_id => x_srp_pmt_plan_id,
421 x_loading_status => x_loading_status
422 );
423
424 IF ( x_return_status = FND_API.G_RET_STS_ERROR )
425 THEN
426 RAISE FND_API.G_EXC_ERROR;
427 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR )
428 THEN
429 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
430 END IF;
431 END IF;
432
433
434 -- Call private API
435 l_create_rec.pmt_plan_id := l_spp_row.pmt_plan_id;
436 l_create_rec.salesrep_id := l_spp_row.salesrep_id;
437 l_create_rec.org_id := l_spp_row.org_id;
438 l_create_rec.start_date := l_spp_row.start_date;
439 l_create_rec.end_date := l_spp_row.end_date;
440 l_create_rec.minimum_amount := l_spp_row.minimum_amount;
441 l_create_rec.maximum_amount := l_spp_row.maximum_amount;
442 l_create_rec.srp_role_id := p_srp_pmt_plans_rec.srp_role_id;
443 l_create_rec.role_pmt_plan_id := p_srp_pmt_plans_rec.role_pmt_plan_id;
444 l_create_rec.lock_flag := p_srp_pmt_plans_rec.lock_flag;
445
446 cn_srp_pmt_plans_pvt.create_srp_pmt_plan
447 ( p_api_version => p_api_version,
448 p_init_msg_list => fnd_api.g_false,
449 p_commit => fnd_api.g_false,
450 p_validation_level => p_validation_level,
451 x_return_status => x_return_status,
452 x_loading_status => x_loading_status,
453 x_msg_count => x_msg_count,
454 x_msg_data => x_msg_data,
455 p_pmt_plan_assign_rec => l_create_rec);
456
457 IF x_return_status <> fnd_api.g_ret_sts_success THEN
461 x_srp_pmt_plan_id := l_create_rec.srp_pmt_plan_id;
458 RAISE fnd_api.g_exc_error;
459 END IF;
460
462
463 --
464 -- End of API body.
465 --
466
467 --
468 -- Post processing hooks
469 --
470 IF JTF_USR_HKS.Ok_to_Execute('CN_SRP_PMT_PLANS_PUB',
471 'CREATE_SRP_PMT_PLAN',
472 'A',
473 'V')
474 THEN
475 cn_srp_pmt_plans_pub_vuhk.create_srp_pmt_plan_post
476 (p_api_version => p_api_version,
477 p_init_msg_list => fnd_api.g_false,
478 p_commit => fnd_api.g_false,
479 p_validation_level => p_validation_level,
480 x_return_status => x_return_status,
481 x_msg_count => x_msg_count,
482 x_msg_data => x_msg_data,
483 p_srp_pmt_plans_rec => l_spp_rec,
484 x_srp_pmt_plan_id => x_srp_pmt_plan_id,
485 x_loading_status => x_loading_status
486 );
487
488 IF ( x_return_status = FND_API.G_RET_STS_ERROR )
489 THEN
490 RAISE FND_API.G_EXC_ERROR;
491 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR )
492 THEN
493 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
494 END IF;
495 END IF;
496
497 IF JTF_USR_HKS.Ok_to_Execute('CN_SRP_PMT_PLANS_PUB',
498 'CREATE_SRP_PMT_PLAN',
499 'A',
500 'C')
501 THEN
502 cn_srp_pmt_plans_pub_cuhk.create_srp_pmt_plan_post
503 (p_api_version => p_api_version,
504 p_init_msg_list => fnd_api.g_false,
505 p_commit => fnd_api.g_false,
506 p_validation_level => p_validation_level,
507 x_return_status => x_return_status,
508 x_msg_count => x_msg_count,
509 x_msg_data => x_msg_data,
510 p_srp_pmt_plans_rec => l_spp_rec,
511 x_srp_pmt_plan_id => x_srp_pmt_plan_id,
512 x_loading_status => x_loading_status
513 );
514
515 IF ( x_return_status = FND_API.G_RET_STS_ERROR )
516 THEN
517 RAISE FND_API.G_EXC_ERROR;
518 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR )
519 THEN
520 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
521 END IF;
522 END IF;
523
524 --
525 -- Message enable hook
526 --
527 IF JTF_USR_HKS.Ok_to_execute('CN_SRP_PMT_PLANS_PUB',
528 'CREATE_SRP_PMT_PLAN',
529 'M',
530 'M')
531 THEN
532 IF cn_srp_pmt_plans_pub_cuhk.ok_to_generate_msg
533 (p_srp_pmt_plans_rec => l_spp_rec)
534 THEN
535 -- Clear bind variables
536 -- XMLGEN.clearBindValues;
537
538 -- Set values for bind variables,
539 -- call this for all bind variables in the business object
540 -- XMLGEN.setBindValue('SRP_PMT_PLAN_ID', x_srp_pmt_plan_id);
541
542 -- Get a ID for workflow/ business object instance
543 l_bind_data_id := JTF_USR_HKS.get_bind_data_id;
544
545 -- Do this for all the bind variables in the Business Object
546 JTF_USR_HKS.load_bind_data
547 ( l_bind_data_id, 'SRP_PMT_PLAN_ID', x_srp_pmt_plan_id, 'S', 'S');
548
549 -- Message generation API
550 JTF_USR_HKS.generate_message
551 (p_prod_code => 'CN',
552 p_bus_obj_code => 'SRP_PMTPLN',
553 p_bus_obj_name => 'SRP_PMT_PLAN',
554 p_action_code => 'I',
555 p_bind_data_id => l_bind_data_id,
556 p_oai_param => null,
557 p_oai_array => l_oai_array,
558 x_return_code => x_return_status) ;
559
560 IF (x_return_status = FND_API.G_RET_STS_ERROR)
561 THEN
562 RAISE FND_API.G_EXC_ERROR;
563 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR )
564 THEN
565 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
566 END IF;
567 END IF;
568 END IF;
569
570 -- Standard check of p_commit.
571 IF FND_API.To_Boolean( p_commit ) THEN
572 COMMIT WORK;
573 END IF;
574 -- Standard call to get message count and if count is 1, get message info.
575 FND_MSG_PUB.Count_And_Get
576 (
577 p_count => x_msg_count ,
578 p_data => x_msg_data ,
579 p_encoded => FND_API.G_FALSE
580 );
581
582 EXCEPTION
583 WHEN FND_API.G_EXC_ERROR THEN
584 ROLLBACK TO Create_Srp_Pmt_Plan ;
585 x_return_status := FND_API.G_RET_STS_ERROR ;
586 FND_MSG_PUB.Count_And_Get
587 (
588 p_count => x_msg_count ,
589 p_data => x_msg_data ,
590 p_encoded => FND_API.G_FALSE
591 );
592
593 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
594 ROLLBACK TO Create_Srp_Pmt_Plan;
595 x_loading_status := 'UNEXPECTED_ERR';
596 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
597 FND_MSG_PUB.Count_And_Get
598 (
599 p_count => x_msg_count ,
600 p_data => x_msg_data ,
601 p_encoded => FND_API.G_FALSE
602 );
603 WHEN OTHERS THEN
604 ROLLBACK TO Create_Srp_Pmt_Plan;
605 x_loading_status := 'UNEXPECTED_ERR';
606 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
607 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
608 THEN
609 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME ,l_api_name );
610 END IF;
611 FND_MSG_PUB.Count_And_Get
612 (
613 p_count => x_msg_count ,
614 p_data => x_msg_data ,
618 END Create_Srp_Pmt_Plan;
615 p_encoded => FND_API.G_FALSE
616 );
617
619
620
621 --| ----------------------------------------------------------------------+
622 --| Procedure : Update_Srp_Pmt_Plan
623 --| Desc : Procedure to update payment plan assignment of an salesrep
624 --| ----------------------------------------------------------------------+
625
626 PROCEDURE Update_Srp_Pmt_Plan
627 (
628 p_api_version IN NUMBER,
629 p_init_msg_list IN VARCHAR2,
630 p_commit IN VARCHAR2,
631 p_validation_level IN NUMBER,
632 x_return_status OUT NOCOPY VARCHAR2,
633 x_msg_count OUT NOCOPY NUMBER,
634 x_msg_data OUT NOCOPY VARCHAR2,
635 p_old_srp_pmt_plans_rec IN srp_pmt_plans_rec_type,
636 p_srp_pmt_plans_rec IN srp_pmt_plans_rec_type,
637 x_loading_status OUT NOCOPY VARCHAR2,
638 p_check_lock IN VARCHAR2 := NULL
639 ) IS
640
641 l_api_name CONSTANT VARCHAR2(30) := 'Update_Srp_Pmt_Plan';
642 l_api_version CONSTANT NUMBER := 1.0;
643 l_spp_rec srp_pmt_plans_rec_type;
644 l_old_spp_rec srp_pmt_plans_rec_type;
645 l_spp_row cn_srp_pmt_plans%ROWTYPE;
646 l_old_spp_row cn_srp_pmt_plans%ROWTYPE;
647 l_org_id NUMBER;
648 l_status VARCHAR2(1);
649
650 l_start_date cn_srp_pmt_plans.start_date%TYPE;
651 l_end_date cn_srp_pmt_plans.end_date%TYPE;
652 l_srp_start_date cn_salesreps.start_date_active%TYPE;
653 l_srp_end_date cn_salesreps.end_date_active%TYPE;
654 l_pp_start_date cn_pmt_plans.start_date%TYPE;
655 l_pp_end_date cn_pmt_plans.end_date%TYPE;
656 l_action VARCHAR2(30) := 'UPDATE';
657 l_srp_pmt_plan_id cn_srp_pmt_plans.srp_pmt_plan_id%TYPE;
658 l_update_rec cn_srp_pmt_plans_pvt.pmt_plan_assign_rec;
659
660 -- Declaration for user hooks
661 l_OAI_array JTF_USR_HKS.oai_data_array_type;
662 l_bind_data_id NUMBER ;
663 l_check_lock VARCHAR2(1);
664
665
666 BEGIN
667
668 -- Standard Start of API savepoint
669 SAVEPOINT Update_Srp_Pmt_Plan;
670 -- Standard call to check for call compatibility.
671 IF NOT FND_API.compatible_api_call
672 ( l_api_version ,p_api_version ,l_api_name ,G_PKG_NAME )
673 THEN
674 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
675 END IF;
676 -- Initialize message list if p_init_msg_list is set to TRUE.
677 IF FND_API.to_Boolean( p_init_msg_list ) THEN
678 FND_MSG_PUB.initialize;
679 END IF;
680 -- Initialize API return status to success
681 x_return_status := FND_API.G_RET_STS_SUCCESS;
682 x_loading_status := 'CN_UPDATED';
683
684 -- Assign the parameter to a local variable
685 l_old_spp_rec := p_old_srp_pmt_plans_rec;
686 l_spp_rec := p_srp_pmt_plans_rec;
687
688 --
689 -- API body
690 --
691
692 if nvl(l_old_spp_rec.org_id, -99) <>
693 Nvl(l_spp_rec.org_id, -99) then
694 FND_MESSAGE.SET_NAME ('FND' , 'FND_MO_OU_CANNOT_UPDATE');
695 if (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
696 FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR,
697 'cn.plsql.cn_srp_pmt_plans_pub.update_srp_pmt_plan.error',
698 true);
699 end if;
700
701 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
702 FND_MESSAGE.SET_NAME ('FND' , 'FND_MO_OU_CANNOT_UPDATE');
703 FND_MSG_PUB.Add;
704 END IF;
705
706 RAISE FND_API.G_EXC_ERROR ;
707 end if;
708
709 l_org_id := l_old_spp_rec.org_id;
710 mo_global.validate_orgid_pub_api
711 (org_id => l_org_id,
712 status => l_status);
713
714 if (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
715 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
716 'cn.plsql.cn_srp_pmt_plans_pub.update_srp_pmt_plan.org_validate',
717 'Validated org_id = ' || l_org_id || ' status = ' || l_status);
718 end if;
719 l_old_spp_rec.org_id := l_org_id;
720 l_spp_rec.org_id := l_org_id;
721
722 l_check_lock := NVL(p_check_lock, 'N');
723
724 -- Trim spaces before/after user input string (Old record)
725 SELECT Ltrim(Rtrim(p_old_srp_pmt_plans_rec.pmt_plan_name)),
726 Ltrim(Rtrim(p_old_srp_pmt_plans_rec.salesrep_type)),
727 Ltrim(Rtrim(p_old_srp_pmt_plans_rec.emp_num)),
728 trunc(p_old_srp_pmt_plans_rec.start_date),
729 trunc(p_old_srp_pmt_plans_rec.end_date)
730 INTO
731 l_old_spp_rec.pmt_plan_name,
732 l_old_spp_rec.salesrep_type,
733 l_old_spp_rec.emp_num,
734 l_old_spp_rec.start_date,
735 l_old_spp_rec.end_date
736 FROM dual;
737 -- Get IDs
738 chk_existence_get_id
739 ( x_return_status => x_return_status,
740 x_msg_count => x_msg_count,
741 x_msg_data => x_msg_data,
742 p_srp_pmt_plans_rec => l_old_spp_rec,
743 x_srp_pmt_plans_row => l_old_spp_row,
744 x_srp_start_date => l_srp_start_date,
745 x_srp_end_date => l_srp_end_date,
746 x_pp_start_date => l_pp_start_date,
747 x_pp_end_date => l_pp_end_date,
748 p_action => 'GETOLDREC',
752 IF ( x_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
749 p_loading_status => x_loading_status,
750 x_loading_status => x_loading_status
751 );
753 RAISE FND_API.G_EXC_ERROR ;
754 ELSIF l_old_spp_row.srp_pmt_plan_id IS NULL THEN
755 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
756 FND_MESSAGE.Set_Name('CN', 'CN_SRP_PMT_PLAN_NOT_EXIST');
757 FND_MSG_PUB.Add;
758 END IF;
759 x_loading_status := 'CN_SRP_PMT_PLAN_NOT_EXIST';
760 RAISE FND_API.G_EXC_ERROR ;
761 END IF;
762
763 -- Trim spaces before/after user input string (New record) if missing,
764 -- assign the old value into it
765
766 IF ((NVL(l_old_spp_row.lock_flag, 'N') <> 'Y') OR (l_check_lock <> 'Y')) THEN
767 SELECT
768 Decode(p_srp_pmt_plans_rec.pmt_plan_name,
769 FND_API.G_MISS_CHAR, l_old_spp_rec.pmt_plan_name,
770 Ltrim(Rtrim(p_srp_pmt_plans_rec.pmt_plan_name))),
771 Decode(p_srp_pmt_plans_rec.salesrep_type,
772 FND_API.G_MISS_CHAR, l_old_spp_rec.salesrep_type,
773 Ltrim(Rtrim(p_srp_pmt_plans_rec.salesrep_type))),
774 Decode(p_srp_pmt_plans_rec.emp_num,
775 FND_API.G_MISS_CHAR, l_old_spp_rec.emp_num,
776 Ltrim(Rtrim(p_srp_pmt_plans_rec.emp_num))),
777 Decode(p_srp_pmt_plans_rec.start_date,
778 FND_API.G_MISS_DATE, l_old_spp_row.start_date,
779 trunc(p_srp_pmt_plans_rec.start_date)),
780 Decode(p_srp_pmt_plans_rec.end_date,
781 FND_API.G_MISS_DATE, l_old_spp_row.end_date,
782 trunc(p_srp_pmt_plans_rec.end_date))
783 INTO
784 l_spp_rec.pmt_plan_name,
785 l_spp_rec.salesrep_type,
786 l_spp_rec.emp_num,
787 l_spp_rec.start_date,
788 l_spp_rec.end_date
789 FROM dual;
790
791 --
792 -- Valid payment plan assignment
793 --
794 valid_pp_assign
795 ( x_return_status => x_return_status,
796 x_msg_count => x_msg_count,
797 x_msg_data => x_msg_data,
798 p_srp_pmt_plans_rec => l_spp_rec,
799 x_srp_pmt_plans_row => l_spp_row,
800 p_action => l_action,
801 p_loading_status => x_loading_status,
802 x_loading_status => x_loading_status
803 );
804 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
805 RAISE FND_API.G_EXC_ERROR ;
806 END IF;
807
808 --
809 -- User hooks
810 --
811 IF JTF_USR_HKS.Ok_to_Execute('CN_SRP_PMT_PLANS_PUB',
812 'UPDATE_SRP_PMT_PLAN',
813 'B',
814 'C')
815 THEN
816 cn_srp_pmt_plans_pub_cuhk.update_srp_pmt_plan_pre
817 (p_api_version => p_api_version,
818 p_init_msg_list => fnd_api.g_false,
819 p_commit => fnd_api.g_false,
820 p_validation_level => p_validation_level,
821 x_return_status => x_return_status,
822 x_msg_count => x_msg_count,
823 x_msg_data => x_msg_data,
824 p_old_srp_pmt_plans_rec=> l_old_spp_rec,
825 p_srp_pmt_plans_rec => l_spp_rec,
826 x_loading_status => x_loading_status
827 );
828
829 IF ( x_return_status = FND_API.G_RET_STS_ERROR )
830 THEN
831 RAISE FND_API.G_EXC_ERROR;
832 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR )
833 THEN
834 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
835 END IF;
836 END IF;
837
838 IF JTF_USR_HKS.Ok_to_Execute('CN_SRP_PMT_PLANS_PUB',
839 'UPDATE_SRP_PMT_PLAN',
840 'B',
841 'V')
842 THEN
843 cn_srp_pmt_plans_pub_vuhk.update_srp_pmt_plan_pre
844 (p_api_version => p_api_version,
845 p_init_msg_list => fnd_api.g_false,
846 p_commit => fnd_api.g_false,
847 p_validation_level => p_validation_level,
848 x_return_status => x_return_status,
849 x_msg_count => x_msg_count,
850 x_msg_data => x_msg_data,
851 p_old_srp_pmt_plans_rec=> l_old_spp_rec,
852 p_srp_pmt_plans_rec => l_spp_rec,
853 x_loading_status => x_loading_status
854 );
855
856 IF ( x_return_status = FND_API.G_RET_STS_ERROR )
857 THEN
858 RAISE FND_API.G_EXC_ERROR;
859 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR )
860 THEN
861 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
862 END IF;
863 END IF;
864
865 -- Call private API
866 l_update_rec.srp_pmt_plan_id := l_old_spp_row.srp_pmt_plan_id;
867 l_update_rec.pmt_plan_id := l_spp_row.pmt_plan_id;
868 l_update_rec.salesrep_id := l_spp_row.salesrep_id;
869 l_update_rec.org_id := l_spp_row.org_id;
870 l_update_rec.start_date := l_spp_row.start_date;
871 l_update_rec.end_date := l_spp_row.end_date;
872 l_update_rec.minimum_amount := l_spp_row.minimum_amount;
873 l_update_rec.maximum_amount := l_spp_row.maximum_amount;
874 l_update_rec.object_version_number := p_old_srp_pmt_plans_rec.object_version_number;
875 l_update_rec.srp_role_id := p_srp_pmt_plans_rec.srp_role_id;
876 l_update_rec.role_pmt_plan_id := p_srp_pmt_plans_rec.role_pmt_plan_id;
877 l_update_rec.lock_flag := p_srp_pmt_plans_rec.lock_flag;
878
879 cn_srp_pmt_plans_pvt.update_srp_pmt_plan
880 ( p_api_version => p_api_version,
884 x_return_status => x_return_status,
881 p_init_msg_list => fnd_api.g_false,
882 p_commit => fnd_api.g_false,
883 p_validation_level => p_validation_level,
885 x_loading_status => x_loading_status,
886 x_msg_count => x_msg_count,
887 x_msg_data => x_msg_data,
888 p_pmt_plan_assign_rec => l_update_rec);
889
890 IF x_return_status <> fnd_api.g_ret_sts_success THEN
891 RAISE fnd_api.g_exc_error;
892 END IF;
893
894 END IF;
895 --
896 -- End of API body.
897 --
898
899 --
900 -- Post processing hooks
901 --
902 IF JTF_USR_HKS.Ok_to_Execute('CN_SRP_PMT_PLANS_PUB',
903 'UPDATE_SRP_PMT_PLAN',
904 'A',
905 'V')
906 THEN
907 cn_srp_pmt_plans_pub_vuhk.update_srp_pmt_plan_post
908 (p_api_version => p_api_version,
909 p_init_msg_list => fnd_api.g_false,
910 p_commit => fnd_api.g_false,
911 p_validation_level => p_validation_level,
912 x_return_status => x_return_status,
913 x_msg_count => x_msg_count,
914 x_msg_data => x_msg_data,
915 p_old_srp_pmt_plans_rec=> l_old_spp_rec,
916 p_srp_pmt_plans_rec => l_spp_rec,
917 x_loading_status => x_loading_status
918 );
919
920 IF ( x_return_status = FND_API.G_RET_STS_ERROR )
921 THEN
922 RAISE FND_API.G_EXC_ERROR;
923 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR )
924 THEN
925 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
926 END IF;
927 END IF;
928
929 IF JTF_USR_HKS.Ok_to_Execute('CN_SRP_PMT_PLANS_PUB',
930 'UPDATE_SRP_PMT_PLAN',
931 'A',
932 'C')
933 THEN
934 cn_srp_pmt_plans_pub_cuhk.update_srp_pmt_plan_post
935 (p_api_version => p_api_version,
936 p_init_msg_list => fnd_api.g_false,
937 p_commit => fnd_api.g_false,
938 p_validation_level => p_validation_level,
939 x_return_status => x_return_status,
940 x_msg_count => x_msg_count,
941 x_msg_data => x_msg_data,
942 p_old_srp_pmt_plans_rec=> l_old_spp_rec,
943 p_srp_pmt_plans_rec => l_spp_rec,
944 x_loading_status => x_loading_status
945 );
946
947 IF ( x_return_status = FND_API.G_RET_STS_ERROR )
948 THEN
949 RAISE FND_API.G_EXC_ERROR;
950 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR )
951 THEN
952 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
953 END IF;
954 END IF;
955
956 --
957 -- Message enable hook
958 --
959 IF JTF_USR_HKS.Ok_to_execute('CN_SRP_PMT_PLANS_PUB',
960 'CREATE_SRP_PMT_PLAN',
961 'M',
962 'M')
963 THEN
964 IF cn_srp_pmt_plans_pub_cuhk.ok_to_generate_msg
965 (p_srp_pmt_plans_rec => l_spp_rec)
966 THEN
967 -- Clear bind variables
968 -- XMLGEN.clearBindValues;
969
970 -- Set values for bind variables,
971 -- call this for all bind variables in the business object
972 -- XMLGEN.setBindValue('SRP_PMT_PLAN_ID', l_spp_row.srp_pmt_plan_id);
973
974 -- Get a ID for workflow/ business object instance
975 l_bind_data_id := JTF_USR_HKS.get_bind_data_id;
976
977 -- Do this for all the bind variables in the Business Object
978 JTF_USR_HKS.load_bind_data
979 ( l_bind_data_id, 'SRP_PMT_PLAN_ID',
980 l_spp_row.srp_pmt_plan_id, 'S', 'S');
981
982 -- Message generation API
983 JTF_USR_HKS.generate_message
984 (p_prod_code => 'CN',
985 p_bus_obj_code => 'SRP_PMTPLN',
986 p_bus_obj_name => 'SRP_PMT_PLAN',
987 p_action_code => 'I',
988 p_bind_data_id => l_bind_data_id,
989 p_oai_param => null,
990 p_oai_array => l_oai_array,
991 x_return_code => x_return_status) ;
992
993 IF (x_return_status = FND_API.G_RET_STS_ERROR)
994 THEN
995 RAISE FND_API.G_EXC_ERROR;
996 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR )
997 THEN
998 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
999 END IF;
1000 END IF;
1001 END IF;
1002
1003 -- Standard check of p_commit.
1004 IF FND_API.To_Boolean( p_commit ) THEN
1005 COMMIT WORK;
1006 END IF;
1007 -- Standard call to get message count and if count is 1, get message info.
1008 FND_MSG_PUB.Count_And_Get
1009 (
1010 p_count => x_msg_count ,
1011 p_data => x_msg_data ,
1012 p_encoded => FND_API.G_FALSE
1013 );
1014
1015 EXCEPTION
1016 WHEN FND_API.G_EXC_ERROR THEN
1017 ROLLBACK TO Update_Srp_Pmt_Plan ;
1018 x_return_status := FND_API.G_RET_STS_ERROR ;
1019 FND_MSG_PUB.Count_And_Get
1020 (
1021 p_count => x_msg_count ,
1022 p_data => x_msg_data ,
1023 p_encoded => FND_API.G_FALSE
1024 );
1025 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1026 ROLLBACK TO Update_Srp_Pmt_Plan;
1027 x_loading_status := 'UNEXPECTED_ERR';
1028 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1029 FND_MSG_PUB.Count_And_Get
1030 (
1031 p_count => x_msg_count ,
1032 p_data => x_msg_data ,
1036 ROLLBACK TO Update_Srp_Pmt_Plan;
1033 p_encoded => FND_API.G_FALSE
1034 );
1035 WHEN OTHERS THEN
1037 x_loading_status := 'UNEXPECTED_ERR';
1038 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1039 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1040 THEN
1041 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME ,l_api_name );
1042 END IF;
1043 FND_MSG_PUB.Count_And_Get
1044 (
1045 p_count => x_msg_count ,
1046 p_data => x_msg_data ,
1047 p_encoded => FND_API.G_FALSE
1048 );
1049
1050 END Update_Srp_Pmt_Plan;
1051
1052 --| ----------------------------------------------------------------------+-+
1053 --| Procedure : Delete_Srp_Pmt_Plan
1054 --| Desc : Procedure to delete a payment plan assignment to salesrep
1055 --| ----------------------------------------------------------------------+
1056
1057 PROCEDURE Delete_Srp_Pmt_Plan
1058 (
1059 p_api_version IN NUMBER,
1060 p_init_msg_list IN VARCHAR2,
1061 p_commit IN VARCHAR2,
1062 p_validation_level IN NUMBER,
1063 x_return_status OUT NOCOPY VARCHAR2,
1064 x_msg_count OUT NOCOPY NUMBER,
1065 x_msg_data OUT NOCOPY VARCHAR2,
1066 p_srp_pmt_plans_rec IN srp_pmt_plans_rec_type,
1067 x_loading_status OUT NOCOPY VARCHAR2,
1068 p_check_lock IN VARCHAR2 := NULL
1069 ) IS
1070
1071 l_api_name CONSTANT VARCHAR2(30) := 'Delete_Srp_Pmt_Plan';
1072 l_api_version CONSTANT NUMBER := 1.0;
1073 l_spp_rec srp_pmt_plans_rec_type := G_MISS_SRP_PMT_PLANS_REC;
1074 l_spp_row cn_srp_pmt_plans%ROWTYPE;
1075
1076 l_srp_start_date cn_salesreps.start_date_active%TYPE;
1077 l_srp_end_date cn_salesreps.end_date_active%TYPE;
1078 l_pp_start_date cn_pmt_plans.start_date%TYPE;
1079 l_pp_end_date cn_pmt_plans.end_date%TYPE;
1080
1081 l_org_id NUMBER;
1082 l_status VARCHAR2(1);
1083
1084 -- Declaration for user hooks
1085 l_OAI_array JTF_USR_HKS.oai_data_array_type;
1086 l_bind_data_id NUMBER ;
1087 l_check_lock VARCHAR2(1);
1088
1089 BEGIN
1090
1091 -- Standard Start of API savepoint
1092 SAVEPOINT Delete_Srp_Pmt_Plan;
1093 -- Standard call to check for call compatibility.
1094 IF NOT FND_API.compatible_api_call
1095 ( l_api_version ,p_api_version ,l_api_name ,G_PKG_NAME )
1096 THEN
1097 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1098 END IF;
1099 -- Initialize message list if p_init_msg_list is set to TRUE.
1100 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1101 FND_MSG_PUB.initialize;
1102 END IF;
1103 -- Initialize API return status to success
1104 x_return_status := FND_API.G_RET_STS_SUCCESS;
1105 x_loading_status := 'CN_DELETED';
1106 -- Assign the parameter to a local variable
1107 l_spp_rec := p_srp_pmt_plans_rec;
1108
1109 --
1110 -- API body
1111 --
1112
1113 --
1114 --Validate org id
1115 --
1116 l_org_id := l_spp_rec.org_id;
1117 mo_global.validate_orgid_pub_api
1118 (org_id => l_org_id,
1119 status => l_status);
1120
1121 if (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
1122 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1123 'cn.plsql.cn_srp_pmt_plans_pub.delete_srp_pmt_plan.org_validate',
1124 'Validated org_id = ' || l_org_id || ' status = ' || l_status);
1125 end if;
1126 l_spp_rec.org_id := l_org_id;
1127
1128 l_check_lock := NVL(p_check_lock, 'N');
1129
1130 -- Trim spaces before/after user input string (Old record)
1131 SELECT Ltrim(Rtrim(p_srp_pmt_plans_rec.pmt_plan_name)),
1132 Ltrim(Rtrim(p_srp_pmt_plans_rec.salesrep_type)),
1133 Ltrim(Rtrim(p_srp_pmt_plans_rec.emp_num)),
1134 trunc(p_srp_pmt_plans_rec.start_date),
1135 trunc(p_srp_pmt_plans_rec.end_date)
1136 INTO
1137 l_spp_rec.pmt_plan_name,
1138 l_spp_rec.salesrep_type,
1139 l_spp_rec.emp_num,
1140 l_spp_rec.start_date,
1141 l_spp_rec.end_date
1142 FROM dual;
1143 -- Get IDs
1144 chk_existence_get_id
1145 ( x_return_status => x_return_status,
1146 x_msg_count => x_msg_count,
1147 x_msg_data => x_msg_data,
1148 p_srp_pmt_plans_rec => l_spp_rec,
1149 x_srp_pmt_plans_row => l_spp_row,
1150 x_srp_start_date => l_srp_start_date,
1151 x_srp_end_date => l_srp_end_date,
1152 x_pp_start_date => l_pp_start_date,
1153 x_pp_end_date => l_pp_end_date,
1154 p_action => 'GETOLDREC',
1155 p_loading_status => x_loading_status,
1156 x_loading_status => x_loading_status
1157 );
1158 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
1159 RAISE FND_API.G_EXC_ERROR ;
1160 ELSIF l_spp_row.srp_pmt_plan_id IS NULL THEN
1161 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1162 FND_MESSAGE.Set_Name('CN', 'CN_SRP_PMT_PLAN_NOT_EXIST');
1163 FND_MSG_PUB.Add;
1164 END IF;
1165 x_loading_status := 'CN_SRP_PMT_PLAN_NOT_EXIST';
1166 RAISE FND_API.G_EXC_ERROR ;
1167 END IF;
1168
1169 --
1170 -- User hooks
1171 --
1175 'C')
1172 IF JTF_USR_HKS.Ok_to_Execute('CN_SRP_PMT_PLANS_PUB',
1173 'DELETE_SRP_PMT_PLAN',
1174 'B',
1176 THEN
1177 cn_srp_pmt_plans_pub_cuhk.delete_srp_pmt_plan_pre
1178 (p_api_version => p_api_version,
1179 p_init_msg_list => fnd_api.g_false,
1180 p_commit => fnd_api.g_false,
1181 p_validation_level => p_validation_level,
1182 x_return_status => x_return_status,
1183 x_msg_count => x_msg_count,
1184 x_msg_data => x_msg_data,
1185 p_srp_pmt_plans_rec => l_spp_rec,
1186 x_loading_status => x_loading_status
1187 );
1188 IF ( x_return_status = FND_API.G_RET_STS_ERROR )
1189 THEN
1190 RAISE FND_API.G_EXC_ERROR;
1191 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR )
1192 THEN
1193 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1194 END IF;
1195 END IF;
1196
1197 IF JTF_USR_HKS.Ok_to_Execute('CN_SRP_PMT_PLANS_PUB',
1198 'DELETE_SRP_PMT_PLAN',
1199 'B',
1200 'V')
1201 THEN
1202 cn_srp_pmt_plans_pub_vuhk.delete_srp_pmt_plan_pre
1203 (p_api_version => p_api_version,
1204 p_init_msg_list => fnd_api.g_false,
1205 p_commit => fnd_api.g_false,
1206 p_validation_level => p_validation_level,
1207 x_return_status => x_return_status,
1208 x_msg_count => x_msg_count,
1209 x_msg_data => x_msg_data,
1210 p_srp_pmt_plans_rec => l_spp_rec,
1211 x_loading_status => x_loading_status
1212 );
1213 IF ( x_return_status = FND_API.G_RET_STS_ERROR )
1214 THEN
1215 RAISE FND_API.G_EXC_ERROR;
1216 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR )
1217 THEN
1218 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1219 END IF;
1220 END IF;
1221
1222 -- API body
1223
1224 cn_srp_pmt_plans_pvt.delete_srp_pmt_plan
1225 (
1226 p_api_version => p_api_version,
1227 p_init_msg_list => fnd_api.g_false,
1228 p_commit => fnd_api.g_false,
1229 p_validation_level => p_validation_level,
1230 x_return_status => x_return_status,
1231 x_loading_status => x_loading_status,
1232 x_msg_count => x_msg_count,
1233 x_msg_data => x_msg_data,
1234 p_srp_pmt_plan_id => l_spp_row.srp_pmt_plan_id);
1235
1236 IF x_return_status <> fnd_api.g_ret_sts_success THEN
1237 RAISE fnd_api.g_exc_error;
1238 END IF;
1239
1240 --
1241 -- End of API body.
1242 --
1243
1244 --
1245 -- Post processing hooks
1246 --
1247 IF JTF_USR_HKS.Ok_to_Execute('CN_SRP_PMT_PLANS_PUB',
1248 'DELETE_SRP_PMT_PLAN',
1249 'A',
1250 'V')
1251 THEN
1252 cn_srp_pmt_plans_pub_vuhk.delete_srp_pmt_plan_post
1253 (p_api_version => p_api_version,
1254 p_init_msg_list => fnd_api.g_false,
1255 p_commit => fnd_api.g_false,
1256 p_validation_level => p_validation_level,
1257 x_return_status => x_return_status,
1258 x_msg_count => x_msg_count,
1259 x_msg_data => x_msg_data,
1260 p_srp_pmt_plans_rec => l_spp_rec,
1261 x_loading_status => x_loading_status
1262 );
1263
1264 IF ( x_return_status = FND_API.G_RET_STS_ERROR )
1265 THEN
1266 RAISE FND_API.G_EXC_ERROR;
1267 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR )
1268 THEN
1269 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1270 END IF;
1271 END IF;
1272
1273 IF JTF_USR_HKS.Ok_to_Execute('CN_SRP_PMT_PLANS_PUB',
1274 'DELETE_SRP_PMT_PLAN',
1275 'A',
1276 'C')
1277 THEN
1278 cn_srp_pmt_plans_pub_cuhk.delete_srp_pmt_plan_post
1279 (p_api_version => p_api_version,
1280 p_init_msg_list => fnd_api.g_false,
1281 p_commit => fnd_api.g_false,
1282 p_validation_level => p_validation_level,
1283 x_return_status => x_return_status,
1284 x_msg_count => x_msg_count,
1285 x_msg_data => x_msg_data,
1286 p_srp_pmt_plans_rec => l_spp_rec,
1287 x_loading_status => x_loading_status
1288 );
1289
1290 IF ( x_return_status = FND_API.G_RET_STS_ERROR )
1291 THEN
1292 RAISE FND_API.G_EXC_ERROR;
1293 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR )
1294 THEN
1295 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1296 END IF;
1297 END IF;
1298 --
1299 -- Message enable hook
1300 --
1301 IF JTF_USR_HKS.Ok_to_execute('CN_SRP_PMT_PLANS_PUB',
1302 'DELETE_SRP_PMT_PLAN',
1303 'M',
1304 'M')
1305 THEN
1306 IF cn_srp_pmt_plans_pub_cuhk.ok_to_generate_msg
1307 (p_srp_pmt_plans_rec => l_spp_rec)
1308 THEN
1309 -- Clear bind variables
1310 -- XMLGEN.clearBindValues;
1311
1312 -- Set values for bind variables,
1313 -- call this for all bind variables in the business object
1314 -- XMLGEN.setBindValue('SRP_PMT_PLAN_ID', l_spp_row.srp_pmt_plan_id);
1315
1316 -- Get a ID for workflow/ business object instance
1317 l_bind_data_id := JTF_USR_HKS.get_bind_data_id;
1318
1319 -- Do this for all the bind variables in the Business Object
1320 JTF_USR_HKS.load_bind_data
1324 -- Message generation API
1321 ( l_bind_data_id, 'SRP_PMT_PLAN_ID',
1322 l_spp_row.srp_pmt_plan_id, 'S', 'S');
1323
1325 JTF_USR_HKS.generate_message
1326 (p_prod_code => 'CN',
1327 p_bus_obj_code => 'SRP_PMTPLN',
1328 p_bus_obj_name => 'SRP_PMT_PLAN',
1329 p_action_code => 'I',
1330 p_bind_data_id => l_bind_data_id,
1331 p_oai_param => null,
1332 p_oai_array => l_oai_array,
1333 x_return_code => x_return_status) ;
1334
1335 IF (x_return_status = FND_API.G_RET_STS_ERROR)
1336 THEN
1337 RAISE FND_API.G_EXC_ERROR;
1338 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR )
1339 THEN
1340 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1341 END IF;
1342 END IF;
1343 END IF;
1344
1345 -- Standard check of p_commit.
1346 IF FND_API.To_Boolean( p_commit ) THEN
1347 COMMIT WORK;
1348 END IF;
1349 -- Standard call to get message count and if count is 1, get message info.
1350 FND_MSG_PUB.Count_And_Get
1351 (
1352 p_count => x_msg_count ,
1353 p_data => x_msg_data ,
1354 p_encoded => FND_API.G_FALSE
1355 );
1356
1357 EXCEPTION
1358 WHEN FND_API.G_EXC_ERROR THEN
1359 ROLLBACK TO Delete_Srp_Pmt_Plan ;
1360 x_return_status := FND_API.G_RET_STS_ERROR ;
1361 FND_MSG_PUB.Count_And_Get
1362 (
1363 p_count => x_msg_count ,
1364 p_data => x_msg_data ,
1365 p_encoded => FND_API.G_FALSE
1366 );
1367 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1368 ROLLBACK TO Delete_Srp_Pmt_Plan;
1369 x_loading_status := 'UNEXPECTED_ERR';
1370 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1371 FND_MSG_PUB.Count_And_Get
1372 (
1373 p_count => x_msg_count ,
1374 p_data => x_msg_data ,
1375 p_encoded => FND_API.G_FALSE
1376 );
1377 WHEN OTHERS THEN
1378 ROLLBACK TO Delete_Srp_Pmt_Plan;
1379 x_loading_status := 'UNEXPECTED_ERR';
1380 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1381 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1382 THEN
1383 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME ,l_api_name );
1384 END IF;
1385 FND_MSG_PUB.Count_And_Get
1386 (
1387 p_count => x_msg_count ,
1388 p_data => x_msg_data ,
1389 p_encoded => FND_API.G_FALSE
1390 );
1391
1392 END Delete_Srp_Pmt_Plan;
1393
1394
1395 -- --------------------------------------------------------------------------*
1396 -- Procedure: Create_Mass_Asgn_Srp_Pmt_plan
1397 -- --------------------------------------------------------------------------*
1398
1399 PROCEDURE Create_Mass_Asgn_Srp_Pmt_Plan
1400 (
1401 p_api_version IN NUMBER,
1402 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1403 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1404 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1405 x_return_status OUT NOCOPY VARCHAR2,
1406 x_msg_count OUT NOCOPY NUMBER,
1407 x_msg_data OUT NOCOPY VARCHAR2,
1408 p_srp_role_id IN NUMBER,
1409 p_role_pmt_plan_id IN NUMBER,
1410 x_srp_pmt_plan_id OUT NOCOPY NUMBER,
1411 x_loading_status OUT NOCOPY VARCHAR2
1412 ) IS
1413 BEGIN
1414 cn_srp_pmt_plans_pvt.create_mass_asgn_srp_pmt_plan
1415 (p_api_version => p_api_version,
1416 p_init_msg_list => p_init_msg_list,
1417 p_commit => p_commit,
1418 p_validation_level => p_validation_level,
1419 x_return_status => x_return_status,
1420 x_msg_count => x_msg_count,
1421 x_msg_data => x_msg_data,
1422 p_srp_role_id => p_srp_role_id,
1423 p_role_pmt_plan_id => p_role_pmt_plan_id,
1424 x_srp_pmt_plan_id => x_srp_pmt_plan_id,
1425 x_loading_status => x_loading_status);
1426 END Create_Mass_Asgn_Srp_Pmt_Plan;
1427
1428 -- --------------------------------------------------------------------------*
1429 -- Procedure: Update_Mass_Asgn_Srp_Pmt_plan
1430 -- --------------------------------------------------------------------------*
1431
1432 PROCEDURE Update_Mass_Asgn_Srp_Pmt_plan
1433 (
1434 p_api_version IN NUMBER,
1435 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1436 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1437 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1438 x_return_status OUT NOCOPY VARCHAR2,
1439 x_msg_count OUT NOCOPY NUMBER,
1440 x_msg_data OUT NOCOPY VARCHAR2,
1441 p_srp_role_id IN NUMBER,
1442 p_role_pmt_plan_id IN NUMBER,
1443 x_loading_status OUT NOCOPY VARCHAR2
1444 ) IS
1445 BEGIN
1446 cn_srp_pmt_plans_pvt.update_mass_asgn_srp_pmt_plan
1447 (p_api_version => p_api_version,
1448 p_init_msg_list => p_init_msg_list,
1449 p_commit => p_commit,
1450 p_validation_level => p_validation_level,
1451 x_return_status => x_return_status,
1452 x_msg_count => x_msg_count,
1453 x_msg_data => x_msg_data,
1454 p_srp_role_id => p_srp_role_id,
1455 p_role_pmt_plan_id => p_role_pmt_plan_id,
1456 x_loading_status => x_loading_status);
1457 END update_mass_asgn_srp_pmt_plan;
1458
1459
1460 -- --------------------------------------------------------------------------*
1461 -- Procedure: Delete_Mass_Asgn_Srp_Pmt_plan
1462 -- --------------------------------------------------------------------------*
1463
1464 PROCEDURE Delete_Mass_Asgn_Srp_Pmt_Plan
1465 (p_api_version IN NUMBER,
1466 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1467 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1468 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1469 x_return_status OUT NOCOPY VARCHAR2,
1470 x_msg_count OUT NOCOPY NUMBER,
1471 x_msg_data OUT NOCOPY VARCHAR2,
1472 p_srp_role_id IN NUMBER,
1473 p_role_pmt_plan_id IN NUMBER,
1474 x_loading_status OUT NOCOPY VARCHAR2
1475 ) IS
1476 BEGIN
1477 cn_srp_pmt_plans_pvt.delete_mass_asgn_srp_pmt_plan
1478 (p_api_version => p_api_version,
1479 p_init_msg_list => p_init_msg_list,
1480 p_commit => p_commit,
1481 p_validation_level => p_validation_level,
1482 x_return_status => x_return_status,
1483 x_msg_count => x_msg_count,
1484 x_msg_data => x_msg_data,
1485 p_srp_role_id => p_srp_role_id,
1486 p_role_pmt_plan_id => p_role_pmt_plan_id,
1487 x_loading_status => x_loading_status);
1488
1489 END Delete_Mass_Asgn_Srp_Pmt_Plan;
1490
1491 END CN_SRP_PMT_PLANS_PUB;