[Home] [Help]
PACKAGE BODY: APPS.CN_SRP_PMT_PLANS_PVT
Source
1 PACKAGE BODY CN_SRP_PMT_PLANS_PVT AS
2 /* $Header: cnvsppab.pls 120.29.12020000.2 2012/07/25 09:18:44 swpoddar ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'CN_SRP_PMT_PLANS_PVT';
5 G_FILE_NAME CONSTANT VARCHAR2(12) := 'cnvsppab.pls';
6
7 procedure get_date_range_intersect(a_start_date in date, a_end_date in date,
8 b_start_date in date, b_end_date in date,
9 x_start_date out nocopy date, x_end_date out nocopy date)
10 IS
11 BEGIN
12 if ( a_start_date is null or b_start_date is null) then
13 x_start_date := null;
14 x_end_date := null;
15 elsif (a_end_date is not null and a_end_date < b_start_date)
16 or ( b_end_date is not null and a_start_date > b_end_date) then
17 x_start_date := null;
18 x_end_date := null;
19 else
20 x_start_date := greatest(a_start_date, b_start_date);
21 if a_end_date is null then
22 x_end_date := b_end_date;
23 elsif b_end_date is null then
24 x_end_date := a_end_date;
25 else
26 x_end_date := least(a_end_date, b_end_date);
27 end if;
28 end if;
29 END;
30
31 procedure get_masgn_date_intersect(
32 p_role_pmt_plan_id IN NUMBER,
33 p_srp_role_id IN NUMBER,
34 x_start_date OUT NOCOPY DATE,
35 x_end_date OUT NOCOPY DATE) IS
36
37 l_start_date cn_srp_pmt_plans.start_date%TYPE;
38 l_end_date cn_srp_pmt_plans.start_date%TYPE;
39
40 l_res_start_date cn_srp_pmt_plans.start_date%TYPE;
41 l_res_end_date cn_srp_pmt_plans.start_date%TYPE;
42
43 l_role_pp_start_date cn_srp_pmt_plans.start_date%TYPE;
44 l_role_pp_end_date cn_srp_pmt_plans.start_date%TYPE;
45
46 l_srp_role_start_date cn_srp_pmt_plans.start_date%TYPE;
47 l_srp_role_end_date cn_srp_pmt_plans.start_date%TYPE;
48
49 l_pp_start_date cn_srp_pmt_plans.start_date%TYPE;
50 l_pp_end_date cn_srp_pmt_plans.start_date%TYPE;
51
52 l_org_id NUMBER;
53 l_salesrep_id NUMBER;
54 l_pmt_plan_id NUMBER;
55
56 BEGIN
57 -- get start_date, end_date org_id and pmt_plan_id from role_pmt_plans
58 select org_id, pmt_plan_id, start_date, end_date
59 into l_org_id, l_pmt_plan_id, l_role_pp_start_date, l_role_pp_end_date
60 from cn_role_pmt_plans
61 where ROLE_PMT_PLAN_ID = p_role_pmt_plan_id;
62
63 -- get srp role assignment start and end dates
64 select start_date, end_date, salesrep_id
65 into l_srp_role_start_date, l_srp_role_end_date, l_salesrep_id
66 from cn_srp_roles
67 where srp_role_id = p_srp_role_id
68 and org_id = l_org_id;
69
70 -- get intersection between srp_role and role_payment_plan dates
71 get_date_range_intersect(
72 a_start_date => l_srp_role_start_date,
73 a_end_date => l_srp_role_end_date,
74 b_start_date => l_role_pp_start_date,
75 b_end_date => l_role_pp_end_date,
76 x_start_date => x_start_date,
77 x_end_date => x_end_date);
78
79 l_start_date := x_start_date;
80 l_end_date := x_end_date;
81
82 -- get resource start and end dates
83 select start_date_active, end_date_active
84 into l_res_start_date, l_res_end_date
85 from cn_salesreps
86 where salesrep_id = l_salesrep_id
87 and org_id = l_org_id;
88
89 -- get intersection with resource start and end dates
90 get_date_range_intersect(
91 a_start_date => l_start_date,
92 a_end_date => l_end_date,
93 b_start_date => l_res_start_date,
94 b_end_date => l_res_end_date,
95 x_start_date => x_start_date,
96 x_end_date => x_end_date);
97
98 l_start_date := x_start_date;
99 l_end_date := x_end_date;
100
101 -- get payment plan start and end dates
102 select start_date, end_date
103 into l_pp_start_date, l_pp_end_date
104 from cn_pmt_plans
105 where pmt_plan_id = l_pmt_plan_id;
106
107 -- get intersection with payment plan start and end dates
108 get_date_range_intersect(
109 a_start_date => l_start_date,
110 a_end_date => l_end_date,
111 b_start_date => l_pp_start_date,
112 b_end_date => l_pp_end_date,
113 x_start_date => x_start_date,
114 x_end_date => x_end_date);
115
116 END;
117
118
119
120 PROCEDURE validate_assignment
121 (x_return_status OUT NOCOPY VARCHAR2 ,
122 x_msg_count OUT NOCOPY NUMBER ,
123 x_msg_data OUT NOCOPY VARCHAR2,
124 p_salesrep_id IN NUMBER,
125 p_org_id IN NUMBER,
126 p_start_date IN DATE,
127 p_end_date IN DATE,
128 p_minimum_amount IN NUMBER,
129 p_maximum_amount IN NUMBER,
130 p_pmt_plan_id IN NUMBER,
131 p_srp_pmt_plan_id IN NUMBER,
132 p_loading_status IN VARCHAR2,
133 x_loading_status OUT NOCOPY VARCHAR2) IS
134
135 l_count NUMBER := 0;
136 l_api_name CONSTANT VARCHAR2(30) := 'Validate_assignment';
137 l_dummy NUMBER;
138 l_srp_start_date DATE;
139 l_srp_end_date DATE;
140 l_pp_start_date DATE;
141 l_pp_end_date DATE;
142 l_payment_group_code VARCHAR2(30);
143
144 BEGIN
145
146 --
147 -- Initialize API return status to success
148 --
149 x_return_status := FND_API.G_RET_STS_SUCCESS;
150 x_loading_status := p_loading_status ;
151
152 -- Check if already exist( duplicate assigned,unique key violation check)
153 SELECT COUNT(1) INTO l_dummy
154 FROM cn_srp_pmt_plans_all
155 WHERE salesrep_id = p_salesrep_id
156 AND pmt_plan_id = p_pmt_plan_id
157 AND start_date = p_start_date
158 AND ( (end_date = p_end_date) OR
159 (end_date IS NULL AND p_end_date IS NULL) )
160 AND ((p_srp_pmt_plan_id IS NOT NULL AND
161 srp_pmt_plan_id <> p_srp_pmt_plan_id)
162 OR
163 (p_srp_pmt_plan_id IS NULL));
164
165 IF l_dummy > 0 THEN
166 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
167 FND_MESSAGE.Set_Name('CN', 'CN_SRP_PMT_PLAN_EXIST');
168 FND_MSG_PUB.Add;
169 END IF;
170 x_loading_status := 'CN_SRP_PMT_PLAN_EXIST';
171 RAISE FND_API.G_EXC_ERROR ;
172 END IF;
173
174 -- Check if Salesrep active
175 -- Cannot assign a pmt plan to an inactive rep because we need the
176 -- cn_srp_periods in order to create cn_srp_period_quotas. It's also a
177 -- reasonable business requirement
178 SELECT start_date_active, end_date_active
179 INTO l_srp_start_date, l_srp_end_date
180 FROM cn_salesreps
181 WHERE salesrep_id = p_salesrep_id
182 AND org_id = p_org_id;
183
184 IF l_srp_start_date IS NULL THEN
185 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
186 FND_MESSAGE.SET_NAME('CN','SRP_MUST_ACTIVATE_REP');
187 FND_MSG_PUB.Add;
188 END IF;
189 x_loading_status := 'SRP_MUST_ACTIVATE_REP';
190 RAISE FND_API.G_EXC_ERROR ;
191 END IF;
192
193 -- Check if date range invalid
194 -- will check : if start_date is null
195 -- if start_date/end_date is missing
196 -- if start_date > end_date
197 IF ( (cn_api.invalid_date_range
198 (p_start_date => p_start_date,
199 p_end_date => p_end_date,
200 p_end_date_nullable => FND_API.G_TRUE,
201 p_loading_status => x_loading_status,
202 x_loading_status => x_loading_status)) = FND_API.G_TRUE ) THEN
203 RAISE FND_API.G_EXC_ERROR ;
204 END IF;
205
206 --
207 --
208 -- Validate Rule :Start or end date is outside of the processing
209 -- period range define in rep detail
210 --
211 -- Oct 26 1999 ACHUNG
212 -- Change: srp_pmt_plan.end_date not forced.
213 -- if srp_pmt_plan.end_date is null, no need to check between srp and pmt
214 -- start date/end date range
215 --
216 IF ( (
217 ( l_srp_start_date IS NOT NULL) AND ( l_srp_end_date IS NOT NULL)
218 AND(
219 ( (p_start_date NOT BETWEEN l_srp_start_date AND l_srp_end_date)AND ((p_end_date IS NULL) OR (p_end_date > l_srp_end_date)))
220 OR (p_start_date NOT BETWEEN l_srp_start_date AND l_srp_end_date)
221 OR ((p_end_date IS NULL) OR (p_end_date > l_srp_end_date))
222 )
223 )--End of first condition in IF
224
225 OR (
226 ( l_srp_start_date IS NOT NULL) AND ( l_srp_end_date IS NULL) AND
227 (p_start_date < l_srp_start_date )
228 ) --ENd of 2nd condition in IF
229
230 ) -- end of IF
231
232 THEN
233 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
234 FND_MESSAGE.SET_NAME ('CN' , 'CN_SPP_PRDS_NI_SRP_PRDS');
235 FND_MSG_PUB.Add;
236 END IF;
237 x_loading_status := 'CN_SPP_PRDS_NI_SRP_PRDS';
238 RAISE FND_API.G_EXC_ERROR ;
239 END IF;
240
241 --
242 -- Validate Rule :Start or end date is outside of the processing
243 -- period range define in payment plan definition
244 --
245 -- Oct 26 1999 ACHUNG
246 -- Change: srp_pmt_plan.end_date not forced.
247 -- if srp_pmt_plan.end_date is null, no need to check between srp and pmt
248 -- start date/end date range
249 --
250
251 SELECT start_date, end_date, payment_group_code
252 INTO l_pp_start_date, l_pp_end_date, l_payment_group_code
253 FROM cn_pmt_plans_all
254 WHERE pmt_plan_id = p_pmt_plan_id;
255 IF ( (
256 ( l_pp_start_date IS NOT NULL) AND ( l_pp_end_date IS NOT NULL)
257 AND(
258 ( (p_start_date NOT BETWEEN l_pp_start_date AND l_pp_end_date)AND ((p_end_date IS NULL)OR (p_end_date > l_pp_end_date)))
259 OR (p_start_date NOT BETWEEN l_pp_start_date AND l_pp_end_date)
260 OR ((p_end_date IS NULL) OR (p_end_date > l_pp_end_date))
261 )
262 )--End of first condition in IF
263
264 OR (
265 ( l_pp_start_date IS NOT NULL) AND ( l_pp_end_date IS NULL) AND
266 (p_start_date < l_pp_start_date)
267 ) --ENd of 2nd condition in IF
268
269 ) -- end of IF
270
271
272 THEN
273 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
274 FND_MESSAGE.SET_NAME ('CN' , 'CN_SPP_PRDS_NI_PMT_PRDS');
275 FND_MSG_PUB.Add;
276 END IF;
277 x_loading_status := 'CN_SPP_PRDS_NI_PMT_PRDS';
278 RAISE FND_API.G_EXC_ERROR ;
279 END IF;
280
281 -- Check Overlap
282 -- Ensure pmt plan assignments do not overlap each other in same role
283 -- only 1 payment plan is active at each date
284 -- Returns an error message and raises an exception if overlap occurs.
285 SELECT COUNT(1) INTO l_dummy
286 FROM cn_srp_pmt_plans_all cspp, cn_pmt_plans_all cpp
287 WHERE (((cspp.end_date IS NULL)
288 AND (p_end_date IS NULL))
289 OR
290 ((cspp.end_date IS NULL) AND
291 (p_end_date IS NOT NULL) AND
292 ((p_start_date >= cspp.start_date) OR
293 (cspp.start_date BETWEEN p_start_date AND p_end_date))
294 )
295 OR
296 ((cspp.end_date IS NOT NULL) AND
297 (p_end_date IS NULL) AND
298 ((p_start_date <= cspp.start_date) OR
299 (p_start_date BETWEEN cspp.start_date AND cspp.end_date))
300 )
301 OR
302 ((cspp.end_date IS NOT NULL) AND
303 (p_end_date IS NOT NULL) AND
304 ((cspp.start_date BETWEEN p_start_date AND p_end_date) OR
305 (cspp.end_date BETWEEN p_start_date AND p_end_date) OR
306 (p_start_date BETWEEN cspp.start_date AND cspp.end_date))
307 )
308 )
309 AND ((p_srp_pmt_plan_id IS NOT NULL AND
310 srp_pmt_plan_id <> p_srp_pmt_plan_id)
311 OR
312 (p_srp_pmt_plan_id IS NULL))
313 AND cspp.Salesrep_id = p_salesrep_id
314 AND cpp.payment_group_code = l_payment_group_code
315 AND cspp.pmt_plan_id = cpp.pmt_plan_id;
316
317 IF l_dummy > 0 then
318 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
319 FND_MESSAGE.SET_NAME ('CN' , 'CN_SRP_PMT_PLAN_OVERLAPS');
320 FND_MSG_PUB.Add;
321 END IF;
322 x_loading_status := 'CN_SRP_PMT_PLAN_OVERLAPS';
323 RAISE FND_API.G_EXC_ERROR ;
324 END IF;
325
326 /* credit type ID in cn_srp_pmt_plans is Obsolete!
327
328 -- Check the credit types of plan element assigned to this salesrep within this date range
329 -- Added by Zack Sep 12th, 2001
330 -- Modified on Oct. 8th, checking for credit type compatibility.
331 -- If there's no quotas assigned at this time, or at least one quota having the same credit
332 -- type id with the payment plan, we will allow it to be assigned.
333
334 l_dummy := 0;
335 l_dummy2 := 0;
336 FOR l_quota_rec in get_quota_ids_csr(x_srp_pmt_plans_row.salesrep_id,x_srp_pmt_plans_row.start_date,x_srp_pmt_plans_row.end_date) LOOP
337 l_dummy2 := 1;
338 IF l_quota_rec.credit_type_id = x_srp_pmt_plans_row.credit_type_id THEN
339 l_dummy := 1;
340 END IF;
341 END LOOP;
342
343 IF l_dummy2 = 1 THEN
344 IF l_dummy = 0 THEN
345 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
346 FND_MESSAGE.SET_NAME ('CN' , 'CN_SRPPP_CT_MISMATCH');
347 FND_MSG_PUB.Add;
348 END IF;
349 x_loading_status := 'CN_SRPPP_CT_MISMATCH';
350 RAISE FND_API.G_EXC_ERROR;
351 END IF;
352 END IF;
353 */
354
355 -- either min amount or max amount needs to be populated
356 IF (p_minimum_amount IS NULL AND p_maximum_amount IS NULL) THEN
357 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
358 FND_MESSAGE.SET_NAME ('CN' , 'CN_SPP_MIN_MAX_NULL');
359 FND_MSG_PUB.Add;
360 END IF;
361 x_loading_status := 'CN_SPP_MIN_MAX_NULL';
362 RAISE FND_API.G_EXC_ERROR ;
363 END IF;
364
365 -- Check Max amount must > Min amount
366 IF (p_maximum_amount < p_minimum_amount)
367 THEN
368 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
369 FND_MESSAGE.SET_NAME ('CN' , 'CN_SPP_MAX_LT_MIN');
370 FND_MSG_PUB.Add;
371 END IF;
372 x_loading_status := 'CN_SPP_MAX_LT_MIN';
373 RAISE FND_API.G_EXC_ERROR ;
374 END IF;
375
376
377 END validate_assignment;
378
379 PROCEDURE check_payruns
380 (p_operation IN VARCHAR2,
381 p_srp_pmt_plan_id IN NUMBER,
382 p_salesrep_id IN NUMBER,
383 p_start_date IN DATE,
384 p_end_date IN DATE,
385 x_payrun_tbl OUT NOCOPY payrun_tbl
386 ) IS
387
388 l_fixed_end_date DATE;
389 l_fixed_old_end_date DATE;
390 l_old_start_date DATE;
391 l_end_of_time CONSTANT DATE := To_date('31-12-9999', 'DD-MM-YYYY');
392 l_null_amount NUMBER := -9999;
393
394 CURSOR get_del_payruns IS
395 SELECT DISTINCT prun.name
396 FROM cn_payment_worksheets_all W, cn_period_statuses_all prd,
397 cn_payruns_all prun, cn_srp_pmt_plans_all spp
398 WHERE w.salesrep_id = spp.salesrep_id
399 AND w.quota_id is null
400 AND prun.pay_period_id = prd.period_id
401 AND prun.org_id = prd.org_id
402 AND prun.payrun_id = w.payrun_id
403 AND spp.srp_pmt_plan_id = p_srp_pmt_plan_id
404 AND ( ((spp.end_date IS NOT NULL) AND (prd.end_date IS NOT NULL)
405 AND (prd.start_date <= spp.end_date)
406 AND (prd.end_date >= spp.start_date))
407 OR ((spp.end_date IS NULL) AND (prd.end_date IS NOT NULL)
408 AND (prd.end_date >= spp.start_date))
409 OR ((spp.end_date IS NULL) AND (prd.end_date IS NULL))
410 );
411
412 CURSOR get_paid_del_payruns IS
413 SELECT 'ERROR' as estatus
414 FROM cn_payment_worksheets_all W, cn_period_statuses_all prd,
415 cn_payruns_all prun, cn_srp_pmt_plans_all spp,cn_pmt_plans_all pp
416 WHERE w.salesrep_id = spp.salesrep_id
417 AND w.quota_id is null
418 AND prun.pay_period_id = prd.period_id
419 AND prun.org_id = prd.org_id
420 AND prun.payrun_id = w.payrun_id
421 AND spp.srp_pmt_plan_id = p_srp_pmt_plan_id
422 AND spp.pmt_plan_id = pp.pmt_plan_id
423 AND prun.status = 'PAID'
424 AND ( ((spp.end_date IS NOT NULL) AND (prd.end_date IS NOT NULL)
425 AND (prd.start_date <= spp.end_date)
426 AND (prd.end_date >= spp.start_date))
427 OR ((spp.end_date IS NULL) AND (prd.end_date IS NOT NULL)
428 AND (prd.end_date >= spp.start_date))
429 OR ((spp.end_date IS NULL) AND (prd.end_date IS NULL))
430 );
431
432 CURSOR get_adj_del_payruns IS
433 SELECT 'ERROR' as estatus
434 FROM cn_payment_worksheets_all W, cn_period_statuses_all prd,
435 cn_payruns_all prun, cn_srp_pmt_plans_all spp,cn_pmt_plans_all pp
436 -- cn_payment_transactions pt
437 WHERE w.salesrep_id = spp.salesrep_id
438 AND w.quota_id is null
439 AND prun.pay_period_id = prd.period_id
440 AND prun.org_id = prd.org_id
441 AND prun.payrun_id = w.payrun_id
442 AND spp.srp_pmt_plan_id = p_srp_pmt_plan_id
443 AND spp.pmt_plan_id = pp.pmt_plan_id
444 -- AND pt.payrun_id = prun.payrun_id
445 -- AND pt.payee_salesrep_id = w.salesrep_id
446 -- AND pt.pay_period_id = prun.pay_period_id
447 -- AND pt.incentive_type_code = 'PMTPLN'
448 AND ( ((spp.end_date IS NOT NULL) AND (prd.end_date IS NOT NULL)
449 AND (prd.start_date <= spp.end_date)
450 AND (prd.end_date >= spp.start_date))
451 OR ((spp.end_date IS NULL) AND (prd.end_date IS NOT NULL)
452 AND (prd.end_date >= spp.start_date))
453 OR ((spp.end_date IS NULL) AND (prd.end_date IS NULL))
454 );
455
456
457 CURSOR get_upd_payruns IS
458 SELECT DISTINCT prun.name
459 FROM cn_payment_worksheets_all W, cn_period_statuses_all prd,cn_payruns_all prun
460 WHERE w.salesrep_id = p_salesrep_id
461 AND prun.pay_period_id = prd.period_id
462 AND w.quota_id is null
463 AND prun.org_id = prd.org_id
464 AND prun.payrun_id = w.payrun_id
465 AND l_old_start_date < p_start_date
466 AND prd.start_date < p_start_date
467 AND prd.end_date > l_old_start_date
468 UNION
469 SELECT DISTINCT prun.name
470 FROM cn_payment_worksheets_all W, cn_period_statuses_all prd,cn_payruns_all prun
471 WHERE w.salesrep_id = p_salesrep_id
472 AND prun.pay_period_id = prd.period_id
473 AND prun.org_id = prd.org_id
474 AND w.quota_id is null
475 AND prun.payrun_id = w.payrun_id
476 AND l_fixed_old_end_date > l_fixed_end_date
477 AND prd.start_date < l_fixed_old_end_date
478 AND prd.end_date > l_fixed_end_date;
479
480 CURSOR get_paid_upd_payruns IS
481 SELECT 'ERROR' as estatus
482 FROM cn_payment_worksheets_all W, cn_period_statuses_all prd,cn_payruns_all prun
483 WHERE w.salesrep_id = p_salesrep_id
484 AND prun.pay_period_id = prd.period_id
485 AND w.quota_id is null
486 AND prun.org_id = prd.org_id
487 AND prun.status = 'PAID'
488 AND prun.payrun_id = w.payrun_id
489 AND l_old_start_date < p_start_date
490 AND prd.start_date < p_start_date
491 AND prd.end_date > l_old_start_date
492 UNION
493 SELECT 'ERROR' as estatus
494 FROM cn_payment_worksheets_all W, cn_period_statuses_all prd,cn_payruns_all prun
495 WHERE w.salesrep_id = p_salesrep_id
496 AND prun.pay_period_id = prd.period_id
497 AND prun.org_id = prd.org_id
498 AND prun.status = 'PAID'
499 AND w.quota_id is null
500 AND prun.payrun_id = w.payrun_id
501 AND l_fixed_old_end_date > l_fixed_end_date
502 AND prd.start_date < l_fixed_old_end_date
503 AND prd.end_date > l_fixed_end_date;
504
505 CURSOR get_date_range(param_srp_pmt_plan_id NUMBER) IS
506 SELECT start_date, Nvl(end_date, l_end_of_time) as end_date
507 FROM cn_srp_pmt_plans_all
508 WHERE srp_pmt_plan_id = param_srp_pmt_plan_id;
509
510 --Added by Christina------------------------------------------------------------
511 -- This cursor returns an error status if there are any paid/unpaid worksheets
512 -- within the current pp assignment's date range but would fall outside
513 -- the date range that the user is trying to shrink the pp assignment to.
514
515 CURSOR get_adj_upd_payruns IS
516 SELECT 'ERROR' as estatus
517 FROM cn_payment_worksheets_all W, cn_period_statuses_all prd,
518 cn_payruns_all prun, cn_srp_pmt_plans_all spp
519 -- cn_payment_transactions pt
520 WHERE w.salesrep_id = spp.salesrep_id
521 AND w.quota_id is null
522 AND prun.pay_period_id = prd.period_id
523 AND prun.org_id = prd.org_id
524 AND prun.payrun_id = w.payrun_id
525 AND spp.srp_pmt_plan_id = p_srp_pmt_plan_id
526 AND prun.status<>'PAID'
527 -- AND pt.payrun_id = prun.payrun_id
528 -- AND pt.payee_salesrep_id = w.salesrep_id
529 -- AND pt.pay_period_id = prun.pay_period_id
530 -- AND pt.incentive_type_code = 'PMTPLN'
531 AND (((spp.end_date IS NOT NULL) AND (prd.end_date IS NOT NULL)
532 AND (prd.start_date <= spp.end_date)
533 AND (prd.end_date >= spp.start_date))
534 OR ((spp.end_date IS NULL) AND (prd.end_date IS NOT NULL)
535 AND (prd.end_date >= spp.start_date))
536 OR ((spp.end_date IS NULL) AND (prd.end_date IS NULL)))
537 AND (NVL(p_end_date, l_end_of_time) < NVL(prd.end_date, l_end_of_time)
538 OR p_start_date > prd.start_date);
539
540
541
542
543 l_date_range_rec get_date_range%ROWTYPE;
544 paid_upd_payruns_row get_paid_upd_payruns%rowtype;
545 paid_del_payruns_row get_paid_del_payruns%rowtype;
546 adj_del_payruns_row get_adj_del_payruns%rowtype;
547 adj_upd_payruns_row get_adj_upd_payruns%rowtype;
548
549 BEGIN
550
551 /*------------------------------------------------------------------------------
552 * CHANTHON - 19-Sep-2006
553 * As per the latest update received, the behaviour should be as follows,
554 * If a resource has been paid or has a working/unpaid worksheet then we should
555 * not allow users to delete the payment plan for that period even if no
556 * payment plan adjustments are there in the worksheet.
557 * Same applies to shrinking. Can happen only till the period end date of the
558 * latest paid/unpaid worksheet.
559 -----------------------------------------------------------------------------*/
560 -- Initialize message list
561 FND_MSG_PUB.initialize;
562
563 IF p_operation = 'DELETE' THEN
564 -- check payruns involved
565 OPEN get_paid_del_payruns;
566 FETCH get_paid_del_payruns INTO paid_del_payruns_row;
567 CLOSE get_paid_del_payruns;
568
569 IF paid_del_payruns_row.estatus = 'ERROR' then
570 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
571 FND_MESSAGE.Set_Name('CN', 'CN_SRP_PP_NO_DEL');
572 FND_MSG_PUB.Add;
573 END IF;
574 ELSE
575 OPEN get_adj_del_payruns;
576 FETCH get_adj_del_payruns INTO adj_del_payruns_row;
577 CLOSE get_adj_del_payruns;
578
579 IF adj_del_payruns_row.estatus = 'ERROR' then
580 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
581 FND_MESSAGE.Set_Name('CN', 'CN_SRP_PP_NO_DEL_ADJ');
582 FND_MSG_PUB.Add;
583 END IF;
584 /* ELSE
585 -- With latest update this should never be called as even if unpaid paysheets
586 -- with no payment plan adjustments are present, delete cannot happen.
587 OPEN get_del_payruns;
588 FETCH get_del_payruns bulk collect INTO x_payrun_tbl;
589 CLOSE get_del_payruns; */
590 END IF;
591 END IF;
592
593 ELSIF p_operation = 'UPDATE' THEN
594 open get_date_range(p_srp_pmt_plan_id);
595 fetch get_date_range into l_date_range_rec;
596 close get_date_range;
597 l_old_start_date := l_date_range_rec.start_date;
598 l_fixed_old_end_date := l_date_range_rec.end_date;
599 l_fixed_end_date := Nvl(p_end_date, l_end_of_time);
600
601 OPEN get_paid_upd_payruns;
602 FETCH get_paid_upd_payruns INTO paid_upd_payruns_row;
603 CLOSE get_paid_upd_payruns;
604
605 IF paid_upd_payruns_row.estatus = 'ERROR' then
606 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
607 FND_MESSAGE.Set_Name('CN', 'CN_SRP_PP_NO_UPD');
608 FND_MSG_PUB.Add;
609 END IF;
610 ELSE
611 OPEN get_adj_upd_payruns;
612 FETCH get_adj_upd_payruns INTO adj_upd_payruns_row;
613 CLOSE get_adj_upd_payruns;
614
615 IF adj_upd_payruns_row.estatus = 'ERROR' then
616 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
617 FND_MESSAGE.Set_Name('CN', 'CN_SRP_PP_NO_UPD');
618 FND_MSG_PUB.Add;
619 END IF;
620 /* ELSE
621 -- With latest update this should never be called as even if unpaid paysheets
622 -- with no payment plan adjustments are present, shrink cannot happen.
623 OPEN get_upd_payruns;
624 FETCH get_upd_payruns bulk collect INTO x_payrun_tbl;
625 CLOSE get_upd_payruns; */
626 END IF;
627 END IF;
628 END IF;
629 END check_payruns;
630
631
632 -- ------------------------------------------------------------------------+
633 -- Procedure : Check_Operation_Allowed ( Delete / Update )
634 -- Description : This procedure is used to check if the srp pmt plan can
635 -- be updated or deleted.
636 -- ------------------------------------------------------------------------+
637 PROCEDURE check_operation_allowed
638 (x_return_status OUT NOCOPY VARCHAR2,
639 x_msg_count OUT NOCOPY NUMBER ,
640 x_msg_data OUT NOCOPY VARCHAR2,
641 p_salesrep_id IN NUMBER,
642 p_old_start_date IN DATE := FND_API.G_MISS_DATE,
643 p_old_end_date IN DATE := FND_API.G_MISS_DATE,
644 p_start_date IN DATE,
645 p_end_date IN DATE,
646 p_loading_status IN VARCHAR2,
647 x_loading_status OUT NOCOPY VARCHAR2
648 ) IS
649
650 l_api_name CONSTANT VARCHAR2(30) := 'Check_operation_allowed';
651 l_dummy NUMBER;
652
653 BEGIN
654 --
655 -- Initialize API return status to success
656 --
657 x_return_status := FND_API.G_RET_STS_SUCCESS;
658 x_loading_status := p_loading_status ;
659
660 -- Need to check if payment plan already been used in payment worksheet
661 -- during the period, if so operation not allowed
662 IF p_old_start_date = FND_API.G_MISS_DATE AND
663 p_old_end_date = FND_API.G_MISS_DATE THEN
664 -- Called from Delete Srp Payment Plan Assign
665
666 --***********************************************************************
667 -- Added By Kumar Sivasankaran
668 -- Delete Srp Payment Plan is Not allowed when it is used in the WOrksheet
669 -- Date 10/09/01
670 --***********************************************************************
671
672 --***********************************************************************
673 -- Modified by Sundar Venkat on 22 Aug 2002
674 -- Bug fix 2518847
675 --***********************************************************************
676
677 SELECT COUNT(1) INTO l_dummy
678 FROM cn_payment_worksheets_all W, cn_period_statuses_all prd,
679 cn_payruns_all prun, cn_payment_transactions_all pmttrans
680 WHERE w.salesrep_id = p_salesrep_id
681 AND w.salesrep_id = pmttrans.credited_salesrep_id
682 AND pmttrans.incentive_type_code = 'PMTPLN'
683 AND prun.pay_period_id = prd.period_id
684 AND prun.org_id = prd.org_id
685 AND prun.payrun_id = w.payrun_id
686 AND ( ((p_end_date IS NOT NULL) AND (prd.end_date IS NOT NULL)
687 AND (prd.start_date <= p_end_date)
688 AND (prd.end_date >= p_start_date))
689 OR ((p_end_date IS NULL) AND (prd.end_date IS NOT NULL)
690 AND (prd.end_date >= p_start_date))
691 OR ((p_end_date IS NULL) AND (prd.end_date IS NULL))
692 );
693
694 IF l_dummy > 0 then
695 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
696 FND_MESSAGE.SET_NAME ('CN' , 'CN_SRP_PMT_PLAN_USED');
697 FND_MSG_PUB.Add;
698 END IF;
699 x_loading_status := 'CN_SRP_PMT_PLAN_USED';
700 RAISE FND_API.G_EXC_ERROR ;
701 END IF;
702
703 ELSE
704 -- Called from Update Srp Payment Plan Assign
705 -- Check if during the old date range assign, any pmt plan already
706 -- been used, if so, cannot change start_date. If not been used, start
707 -- date can be extend or shrink.
708
709 --***********************************************************************
710 -- Added By Kumar Sivasankaran
711 -- Date 09/10/01
712 --
713 -- Shorten the end_date assignment
714 -- Check for the shortened date range, if pmt plan already been paid,
715 -- if so, cannot shorten
716 --***********************************************************************
717 IF ( ((p_old_end_date IS NOT NULL) AND (p_end_date IS NOT NULL) AND
718 (p_old_end_date > p_end_date))
719 OR
720 ((p_old_end_date IS NULL) AND (p_end_date IS NOT NULL)) ) THEN
721 SELECT COUNT(1) INTO l_dummy
722 FROM cn_payment_worksheets_all W, cn_period_statuses_all prd,cn_payruns_all prun
723 WHERE w.salesrep_id = p_salesrep_id
724 AND prun.pay_period_id = prd.period_id
725 AND prun.org_id = prd.org_id
726 AND prun.payrun_id = w.payrun_id
727 AND ( ((p_old_end_date IS NOT NULL) AND (prd.end_date IS NOT NULL)
728 AND (prd.start_date < p_old_end_date)
729 AND (prd.end_date > p_end_date))
730 OR ((p_old_end_date IS NULL) AND
731 ((prd.start_date > p_end_date) OR (prd.end_date > p_end_date)))
732 );
733
734 IF l_dummy > 0 THEN
735 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
736 FND_MESSAGE.SET_NAME ('CN' , 'CN_SPP_CANNOT_SHORTEN_ED');
737 FND_MSG_PUB.Add;
738 END IF;
739 x_loading_status := 'CN_SPP_CANNOT_SHORTEN_ED';
740 RAISE FND_API.G_EXC_ERROR ;
741 END IF;
742
743 END IF ; -- end IF end date change
744
745 -- Check if during the old date range assign, any pmt plan already
746 -- been used, if so, cannot change start_date. If not been used, start
747 -- date can be extend or shrink.
748
749 IF p_old_start_date <> p_start_date
750 THEN
751 SELECT COUNT(1) INTO l_dummy
752 FROM cn_payment_worksheets_all W, cn_period_statuses_all prd,cn_payruns_all prun
753 WHERE w.salesrep_id = p_salesrep_id
754 AND prun.pay_period_id = prd.period_id
755 AND prun.org_id = prd.org_id
756 AND prun.payrun_id = w.payrun_id
757 AND ( ((p_old_end_date IS NOT NULL) AND (prd.end_date IS NOT NULL)
758 AND (prd.start_date <= p_old_end_date)
759 AND (prd.end_date >= p_old_start_date))
760 OR ((p_old_end_date IS NULL) AND (prd.end_date IS NOT NULL)
761 AND (prd.end_date >= p_old_start_date))
762 );
763 IF l_dummy > 0 THEN
764 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
765 THEN
766 FND_MESSAGE.SET_NAME ('CN' , 'CN_SPP_UPDATE_NOT_ALLOWED');
767 FND_MSG_PUB.Add;
768 END IF;
769 x_loading_status := 'CN_SPP_UPDATE_NOT_ALLOWED';
770 RAISE FND_API.G_EXC_ERROR ;
771 END IF;
772 END IF ; -- end IF start date change
773 END IF; -- end if delete/update operation
774
775 EXCEPTION
776 WHEN FND_API.G_EXC_ERROR THEN
777 x_return_status := FND_API.G_RET_STS_ERROR ;
778
779 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
780 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
781 x_loading_status := 'UNEXPECTED_ERR';
782
783 WHEN OTHERS THEN
784 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
785 x_loading_status := 'UNEXPECTED_ERR';
786
787 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
788 THEN
789 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME ,l_api_name );
790 END IF;
791
792 END check_operation_allowed;
793
794 PROCEDURE get_note
795 (p_field IN VARCHAR2,
796 p_old_value IN VARCHAR2,
797 p_new_value IN VARCHAR2,
798 x_msg IN OUT nocopy VARCHAR2) IS
799
800 l_note_msg VARCHAR2(240);
801 BEGIN
802 fnd_message.set_name('CN', 'CN_SPP_UPD_NOTE');
803 fnd_message.set_token('FIELD', cn_api.get_lkup_meaning(p_field, 'CN_NOTE_FIELDS'));
804 fnd_message.set_token('OLD', p_old_value);
805 fnd_message.set_token('NEW', p_new_value);
806 l_note_msg := fnd_message.get;
807
808 IF x_msg IS NOT NULL THEN
809 x_msg := x_msg || fnd_global.local_chr(10);
810 END IF;
811 x_msg := x_msg || l_note_msg;
812 END get_note;
813
814 PROCEDURE raise_note
815 (p_srp_pmt_plan_id IN NUMBER,
816 p_msg IN VARCHAR2) IS
817
818 x_note_id NUMBER;
819 x_msg_count NUMBER;
820 x_msg_data VARCHAR2(240);
821 x_return_status VARCHAR2(1);
822
823 BEGIN
824 jtf_notes_pub.create_note
825 ( p_api_version => 1.0,
826 x_return_status => x_return_status,
827 x_msg_count => x_msg_count,
828 x_msg_data => x_msg_data,
829 p_source_object_id => p_srp_pmt_plan_id,
830 p_source_object_code => 'CN_SRP_PMT_PLANS',
831 p_notes => p_msg,
832 p_notes_detail => p_msg,
833 p_note_type => 'CN_SYSGEN', -- for system generated
834 x_jtf_note_id => x_note_id -- returned
835 );
836 END raise_note;
837
838 PROCEDURE business_event
839 (p_operation IN VARCHAR2,
840 p_pmt_plan_assign_rec IN pmt_plan_assign_rec) IS
841
842 l_key VARCHAR2(80);
843 l_event_name VARCHAR2(80);
844 l_list wf_parameter_list_t;
845 BEGIN
846 -- p_operation = Add, Update, Remove
847 l_event_name := 'oracle.apps.cn.resource.PaymentPlanAssign.' || p_operation;
848
849 --Get the item key
850 -- for create - event_name || srp_pmt_plan_id
851 -- for update - event_name || srp_pmt_plan_id || ovn
852 -- for delete - event_name || srp_pmt_plan_id
853 l_key := l_event_name || '-' || p_pmt_plan_assign_rec.srp_pmt_plan_id;
854
855 -- build parameter list as appropriate
856 IF (p_operation = 'Add') THEN
857 wf_event.AddParameterToList('SALESREP_ID',p_pmt_plan_assign_rec.salesrep_id,l_list);
858 wf_event.AddParameterToList('PMT_PLAN_ID',p_pmt_plan_assign_rec.pmt_plan_id,l_list);
859 wf_event.AddParameterToList('START_DATE',p_pmt_plan_assign_rec.start_date,l_list);
860 wf_event.AddParameterToList('END_DATE',p_pmt_plan_assign_rec.end_date,l_list);
861 wf_event.AddParameterToList('MINIMUM_AMOUNT',p_pmt_plan_assign_rec.minimum_amount,l_list);
862 wf_event.AddParameterToList('MAXIMUM_AMOUNT',p_pmt_plan_assign_rec.maximum_amount,l_list);
863 wf_event.AddParameterToList('LOCK_FLAG',p_pmt_plan_assign_rec.lock_flag,l_list);
864 ELSIF (p_operation = 'Update') THEN
865 l_key := l_key || '-' || p_pmt_plan_assign_rec.object_version_number;
866 wf_event.AddParameterToList('SRP_PMT_PLAN_ID',p_pmt_plan_assign_rec.srp_pmt_plan_id,l_list);
867 wf_event.AddParameterToList('SALESREP_ID',p_pmt_plan_assign_rec.salesrep_id,l_list);
868 wf_event.AddParameterToList('PMT_PLAN_ID',p_pmt_plan_assign_rec.pmt_plan_id,l_list);
869 wf_event.AddParameterToList('START_DATE',p_pmt_plan_assign_rec.start_date,l_list);
870 wf_event.AddParameterToList('END_DATE',p_pmt_plan_assign_rec.end_date,l_list);
871 wf_event.AddParameterToList('MINIMUM_AMOUNT',p_pmt_plan_assign_rec.minimum_amount,l_list);
872 wf_event.AddParameterToList('MAXIMUM_AMOUNT',p_pmt_plan_assign_rec.maximum_amount,l_list);
873 wf_event.AddParameterToList('LOCK_FLAG',p_pmt_plan_assign_rec.lock_flag,l_list);
874 ELSIF (p_operation = 'Remove') THEN
875 wf_event.AddParameterToList('SRP_PMT_PLAN_ID',p_pmt_plan_assign_rec.srp_pmt_plan_id,l_list);
876 END IF;
877
878 -- Raise Event
879 wf_event.raise
880 (p_event_name => l_event_name,
881 p_event_key => l_key,
882 p_parameters => l_list);
883
884 l_list.DELETE;
885 END business_event;
886
887
888 -- --------------------------------------------------------------------------*
889 -- Procedure: Create_Srp_Pmt_Plan
890 -- --------------------------------------------------------------------------*
891 PROCEDURE Create_Srp_Pmt_Plan
892 ( p_api_version IN NUMBER ,
893 p_init_msg_list IN VARCHAR2,
894 p_commit IN VARCHAR2,
895 p_validation_level IN NUMBER,
896 x_return_status OUT NOCOPY VARCHAR2 ,
897 x_loading_status OUT NOCOPY VARCHAR2 ,
898 x_msg_count OUT NOCOPY NUMBER ,
899 x_msg_data OUT NOCOPY VARCHAR2 ,
900 p_pmt_plan_assign_rec IN OUT NOCOPY pmt_plan_assign_rec) IS
901
902 l_api_name CONSTANT VARCHAR2(30) := 'Create_Srp_Pmt_Plan';
903 l_api_version CONSTANT NUMBER := 1.0;
904 l_credit_type_id NUMBER;
905 l_name cn_pmt_plans.name%TYPE;
906 l_role_name cn_roles.name%TYPE;
907 l_loading_status VARCHAR2(2000);
908 x_note_id NUMBER;
909 l_note_msg VARCHAR2(240);
910
911 CURSOR get_role_name IS
912 select r.name
913 from cn_roles r, cn_role_pmt_plans_all rpp
914 where r.role_id = rpp.role_id
915 and rpp.role_pmt_plan_id = p_pmt_plan_assign_rec.role_pmt_plan_id;
916
917 BEGIN
918 -- Standard Start of API savepoint
919
920 SAVEPOINT Create_Srp_Pmt_Plan;
921
922 -- Standard call to check for call compatibility.
923
924 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
925 p_api_version ,
926 l_api_name ,
927 G_PKG_NAME )
928 THEN
929 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
930 END IF;
931
932 -- Initialize message list if p_init_msg_list is set to TRUE.
933 IF FND_API.to_Boolean( p_init_msg_list ) THEN
934 FND_MSG_PUB.initialize;
935 END IF;
936
937 -- Initialize API return status to success
938 x_return_status := FND_API.G_RET_STS_SUCCESS;
939 x_loading_status := 'CN_CREATED';
940
941 validate_assignment
942 (x_return_status => x_return_status,
943 x_msg_count => x_msg_count,
944 x_msg_data => x_msg_data,
945 p_salesrep_id => p_pmt_plan_assign_rec.salesrep_id,
946 p_org_id => p_pmt_plan_assign_rec.org_id,
947 p_start_date => p_pmt_plan_assign_rec.start_date,
948 p_end_date => p_pmt_plan_assign_rec.end_date,
949 p_minimum_amount => p_pmt_plan_assign_rec.minimum_amount,
950 p_maximum_amount => p_pmt_plan_assign_rec.maximum_amount,
951 p_pmt_plan_id => p_pmt_plan_assign_rec.pmt_plan_id,
952 p_srp_pmt_plan_id => NULL,
953 p_loading_status => x_loading_status,
954 x_loading_status => x_loading_status);
955
956 IF x_return_status <> fnd_api.g_ret_sts_success THEN
957 RAISE fnd_api.g_exc_error;
958 END IF;
959
960 -- inherit credit type of pmt plan
961 select credit_type_id, name into l_credit_type_id, l_name
962 from cn_pmt_plans_all
963 where pmt_plan_id = p_pmt_plan_assign_rec.pmt_plan_id;
964
965
966 cn_srp_pmt_plans_pkg.insert_row
967 ( x_srp_pmt_plan_id => p_pmt_plan_assign_rec.srp_pmt_plan_id
968 ,x_pmt_plan_id => p_pmt_plan_assign_rec.pmt_plan_id
969 ,x_salesrep_id => p_pmt_plan_assign_rec.salesrep_id
970 ,x_org_id => p_pmt_plan_assign_rec.org_id
971 ,x_role_id => NULL
972 ,x_credit_type_id => l_credit_type_id -- obsolete
973 ,x_start_date => p_pmt_plan_assign_rec.start_date
974 ,x_end_date => p_pmt_plan_assign_rec.end_date
975 ,x_minimum_amount => p_pmt_plan_assign_rec.minimum_amount
976 ,x_maximum_amount => p_pmt_plan_assign_rec.maximum_amount
977 ,x_max_recovery_amount => NULL -- obsolete
978 ,x_last_update_date => sysdate
979 ,x_last_updated_by => fnd_global.user_id
980 ,x_creation_date => sysdate
981 ,x_created_by => fnd_global.user_id
982 ,x_last_update_login => fnd_global.login_id
983 ,x_srp_role_id => p_pmt_plan_assign_rec.srp_role_id
984 ,x_role_pmt_plan_id => p_pmt_plan_assign_rec.role_pmt_plan_id
985 ,x_lock_flag => p_pmt_plan_assign_rec.lock_flag
986 );
987
988 -- raise business event
989 business_event
990 (p_operation => 'Add',
991 p_pmt_plan_assign_rec => p_pmt_plan_assign_rec);
992
993 -- create note
994 OPEN get_role_name;
995 FETCH get_role_name INTO l_role_name;
996 CLOSE get_role_name;
997
998 l_note_msg := fnd_message.get_string('CN', 'CN_SPP_CRE_NOTE');
999 l_note_msg := l_note_msg || l_name || ', ' || l_role_name || ', ' ||
1000 p_pmt_plan_assign_rec.start_date || ', ' ||
1001 p_pmt_plan_assign_rec.end_date || ', ' ||
1002 p_pmt_plan_assign_rec.minimum_amount || ', ' ||
1003 p_pmt_plan_assign_rec.maximum_amount || ', ' ||
1004 p_pmt_plan_assign_rec.lock_flag;
1005
1006 jtf_notes_pub.create_note
1007 ( p_api_version => 1.0,
1008 x_return_status => x_return_status,
1009 x_msg_count => x_msg_count,
1010 x_msg_data => x_msg_data,
1011 p_source_object_id => p_pmt_plan_assign_rec.salesrep_id,
1012 p_source_object_code => 'CN_SALESREPS',
1013 p_notes => l_note_msg,
1014 p_notes_detail => l_note_msg,
1015 p_note_type => 'CN_SYSGEN', -- for system generated
1016 x_jtf_note_id => x_note_id -- returned
1017 );
1018
1019 -- populate ovn
1020 SELECT object_version_number
1021 INTO p_pmt_plan_assign_rec.object_version_number
1022 FROM cn_srp_pmt_plans_all
1023 WHERE srp_pmt_plan_id = p_pmt_plan_assign_rec.srp_pmt_plan_id;
1024
1025 -- End of API body
1026
1027 -- Standard check of p_commit.
1028
1029 IF FND_API.To_Boolean( p_commit ) THEN
1030 COMMIT WORK;
1031 END IF;
1032
1033 -- Standard call to get message count and if count is 1, get message info
1034 FND_MSG_PUB.Count_And_Get
1035 (
1036 p_count => x_msg_count ,
1037 p_data => x_msg_data ,
1038 p_encoded => FND_API.G_FALSE
1039 );
1040
1041 EXCEPTION
1042 WHEN FND_API.G_EXC_ERROR THEN
1043 ROLLBACK TO Create_Srp_Pmt_Plan;
1044 x_return_status := FND_API.G_RET_STS_ERROR ;
1045 FND_MSG_PUB.Count_And_Get
1046 (
1047 p_count => x_msg_count ,
1048 p_data => x_msg_data ,
1049 p_encoded => FND_API.G_FALSE
1050 );
1051 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1052 ROLLBACK TO Create_Srp_Pmt_Plan;
1053 x_loading_status := 'UNEXPECTED_ERR';
1054 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1055 FND_MSG_PUB.Count_And_Get
1056 (
1057 p_count => x_msg_count ,
1058 p_data => x_msg_data ,
1059 p_encoded => FND_API.G_FALSE
1060 );
1061 WHEN OTHERS THEN
1062 ROLLBACK TO Create_Srp_Pmt_Plan;
1063 x_loading_status := 'UNEXPECTED_ERR';
1064 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1065 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1066 THEN
1067 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME ,l_api_name );
1068 END IF;
1069 FND_MSG_PUB.Count_And_Get
1070 (
1071 p_count => x_msg_count ,
1072 p_data => x_msg_data ,
1073 p_encoded => FND_API.G_FALSE
1074 );
1075 END create_srp_pmt_plan;
1076
1077
1078
1079 -- --------------------------------------------------------------------------*
1080 -- Procedure: Update_Srp_Pmt_Plan
1081 -- --------------------------------------------------------------------------*
1082 PROCEDURE Update_Srp_Pmt_Plan
1083 ( p_api_version IN NUMBER ,
1084 p_init_msg_list IN VARCHAR2,
1085 p_commit IN VARCHAR2,
1086 p_validation_level IN NUMBER,
1087 x_return_status OUT NOCOPY VARCHAR2 ,
1088 x_loading_status OUT NOCOPY VARCHAR2 ,
1089 x_msg_count OUT NOCOPY NUMBER ,
1090 x_msg_data OUT NOCOPY VARCHAR2 ,
1091 p_pmt_plan_assign_rec IN OUT NOCOPY pmt_plan_assign_rec ) IS
1092
1093 l_api_name CONSTANT VARCHAR2(30) := 'Update_Srp_Pmt_Plan';
1094 l_api_version CONSTANT NUMBER := 1.0;
1095 l_credit_type_id NUMBER;
1096
1097 CURSOR spp_csr( l_srp_pmt_plan_id NUMBER ) IS
1098 SELECT *
1099 FROM cn_srp_pmt_plans
1100 WHERE srp_pmt_plan_id = l_srp_pmt_plan_id;
1101
1102 l_oldrec spp_csr%ROWTYPE;
1103 l_oldname cn_pmt_plans.name%TYPE;
1104 l_newname cn_pmt_plans.name%TYPE;
1105 l_notemsg VARCHAR2(2000);
1106
1107
1108 BEGIN
1109 -- Standard Start of API savepoint
1110
1111 SAVEPOINT Update_Srp_Pmt_Plan;
1112
1113 -- Standard call to check for call compatibility.
1114
1115 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
1116 p_api_version ,
1117 l_api_name ,
1118 G_PKG_NAME )
1119 THEN
1120 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1121 END IF;
1122
1123 -- Initialize message list if p_init_msg_list is set to TRUE.
1124 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1125 FND_MSG_PUB.initialize;
1126 END IF;
1127
1128 -- Initialize API return status to success
1129 x_return_status := FND_API.G_RET_STS_SUCCESS;
1130 x_loading_status := 'CN_UPDATED';
1131
1132 -- check if the object version number is the same
1133 OPEN spp_csr(p_pmt_plan_assign_rec.srp_pmt_plan_id) ;
1134 FETCH spp_csr INTO l_oldrec;
1135 CLOSE spp_csr;
1136
1137 IF (l_oldrec.object_version_number <>
1138 p_pmt_plan_assign_rec.object_version_number) THEN
1139
1140 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
1141 THEN
1142 fnd_message.set_name('CN', 'CN_INVALID_OBJECT_VERSION');
1143 fnd_msg_pub.add;
1144 END IF;
1145
1146 x_loading_status := 'CN_INVALID_OBJECT_VERSION';
1147 RAISE FND_API.G_EXC_ERROR;
1148
1149 END IF;
1150
1151 -- can't change lock flag from Y to N
1152 IF l_oldrec.lock_flag = 'Y' AND p_pmt_plan_assign_rec.lock_flag = 'N' THEN
1153 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
1154 THEN
1155 fnd_message.set_name('CN', 'CN_CANNOT_UPDATE_LOCK');
1156 fnd_msg_pub.add;
1157 END IF;
1158 x_loading_status := 'CN_CANNOT_UPDATE_LOCK';
1159 RAISE FND_API.G_EXC_ERROR;
1160 END IF;
1161
1162 -- can't change lock from N to Y if it is manual assignment
1163 IF l_oldrec.lock_flag = 'N' AND p_pmt_plan_assign_rec.lock_flag = 'Y' AND
1164 p_pmt_plan_assign_rec.role_pmt_plan_id IS NULL THEN
1165 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
1166 THEN
1167 fnd_message.set_name('CN', 'CN_CANNOT_UPDATE_LOCK');
1168 fnd_msg_pub.add;
1169 END IF;
1170 x_loading_status := 'CN_CANNOT_UPDATE_LOCK';
1171 RAISE FND_API.G_EXC_ERROR;
1172 END IF;
1173
1174 -- validate the assignment
1175 validate_assignment
1176 (x_return_status => x_return_status,
1177 x_msg_count => x_msg_count,
1178 x_msg_data => x_msg_data,
1179 p_salesrep_id => p_pmt_plan_assign_rec.salesrep_id,
1180 p_org_id => p_pmt_plan_assign_rec.org_id,
1181 p_start_date => p_pmt_plan_assign_rec.start_date,
1182 p_end_date => p_pmt_plan_assign_rec.end_date,
1183 p_minimum_amount => p_pmt_plan_assign_rec.minimum_amount,
1184 p_maximum_amount => p_pmt_plan_assign_rec.maximum_amount,
1185 p_pmt_plan_id => p_pmt_plan_assign_rec.pmt_plan_id,
1186 p_srp_pmt_plan_id => p_pmt_plan_assign_rec.srp_pmt_plan_id,
1187 p_loading_status => x_loading_status,
1188 x_loading_status => x_loading_status);
1189
1190 -- inherit credit type of pmt plan
1191 select credit_type_id into l_credit_type_id
1192 from cn_pmt_plans_all
1193 where pmt_plan_id = p_pmt_plan_assign_rec.pmt_plan_id;
1194
1195 -- if the lock_flag is being set, then blow away role_pmt_plan_id
1196 IF p_pmt_plan_assign_rec.lock_flag = 'Y' THEN
1197 p_pmt_plan_assign_rec.role_pmt_plan_id := NULL;
1198 p_pmt_plan_assign_rec.srp_role_id := NULL;
1199 END IF;
1200
1201 -- Check if update is allowed
1202 IF l_oldrec.salesrep_id <> p_pmt_plan_assign_rec.salesrep_id OR
1203 l_oldrec.pmt_plan_id <> p_pmt_plan_assign_rec.pmt_plan_id THEN
1204 -- user try to change the assignment
1205 -- need to delete the old assginment then create the new assignment
1206 --
1207 -- Check if delete operation allowed
1208 --
1209 /* check_operation_allowed
1210 ( x_return_status => x_return_status,
1211 x_msg_count => x_msg_count,
1212 x_msg_data => x_msg_data,
1213 p_salesrep_id => l_oldrec.salesrep_id,
1214 p_start_date => l_oldrec.start_date,
1215 p_end_date => l_oldrec.end_date,
1216 p_loading_status => x_loading_status,
1217 x_loading_status => x_loading_status
1218 );
1219 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
1220 RAISE FND_API.G_EXC_ERROR ;
1221 END IF;
1222 */
1223 -- Delete record
1224 cn_srp_pmt_plans_pkg.delete_row
1225 (x_srp_pmt_plan_id => l_oldrec.srp_pmt_plan_id);
1226
1227 -- Insert new record w/ validation
1228 cn_srp_pmt_plans_pkg.insert_row
1229 ( x_srp_pmt_plan_id => p_pmt_plan_assign_rec.srp_pmt_plan_id
1230 ,x_pmt_plan_id => p_pmt_plan_assign_rec.pmt_plan_id
1231 ,x_salesrep_id => p_pmt_plan_assign_rec.salesrep_id
1232 ,x_org_id => p_pmt_plan_assign_rec.org_id
1233 ,x_role_id => NULL
1234 ,x_credit_type_id => l_credit_type_id -- obsolete
1235 ,x_start_date => p_pmt_plan_assign_rec.start_date
1236 ,x_end_date => p_pmt_plan_assign_rec.end_date
1237 ,x_minimum_amount => p_pmt_plan_assign_rec.minimum_amount
1238 ,x_maximum_amount => p_pmt_plan_assign_rec.maximum_amount
1239 ,x_max_recovery_amount => NULL -- obsolete
1240 ,x_last_update_date => sysdate
1241 ,x_last_updated_by => fnd_global.user_id
1242 ,x_creation_date => sysdate
1243 ,x_created_by => fnd_global.user_id
1244 ,x_last_update_login => fnd_global.login_id
1245 ,x_srp_role_id => p_pmt_plan_assign_rec.srp_role_id
1246 ,x_role_pmt_plan_id => p_pmt_plan_assign_rec.role_pmt_plan_id
1247 ,x_lock_flag => p_pmt_plan_assign_rec.lock_flag);
1248
1249 -- sync ID back
1250 update cn_srp_pmt_plans_all
1251 set srp_pmt_plan_id = l_oldrec.srp_pmt_plan_id
1252 where srp_pmt_plan_id = p_pmt_plan_assign_rec.srp_pmt_plan_id;
1253
1254 p_pmt_plan_assign_rec.srp_pmt_plan_id := l_oldrec.srp_pmt_plan_id;
1255
1256 -- Added the Min and Max or condition
1257 -- Kumar.
1258 ELSE
1259 /* -- this check has already been performed
1260 -- just do update instead of delete/create
1261 IF l_oldrec.start_date <> p_pmt_plan_assign_rec.start_date OR
1262 l_oldrec.end_date <> p_pmt_plan_assign_rec.end_date
1263 THEN
1264 -- Check if update operation allowed
1265 -- try to update start date, end date, need to check if the old_rec
1266 -- already been used in worksheet during those delete dates,if so,
1267 -- cannot change the date range
1268
1269 -- Added more parameters
1270 check_operation_allowed
1271 ( x_return_status => x_return_status,
1272 x_msg_count => x_msg_count,
1273 x_msg_data => x_msg_data,
1274 p_salesrep_id => l_oldrec.salesrep_id,
1275 p_old_start_date => l_oldrec.start_date,
1276 p_old_end_date => l_oldrec.end_date,
1277 p_start_date => p_pmt_plan_assign_rec.start_date,
1278 p_end_date => p_pmt_plan_assign_rec.end_date,
1279 p_loading_status => x_loading_status,
1280 x_loading_status => x_loading_status
1281 );
1282
1283 -- Check opeation fail
1284 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
1285 RAISE FND_API.G_EXC_ERROR ;
1286 END IF;
1287
1288 END IF;
1289 */
1290 -- Update pmt plan assignment into cn_srp_pmt_plans
1291 cn_srp_pmt_plans_pkg.update_row
1292 ( x_srp_pmt_plan_id => p_pmt_plan_assign_rec.srp_pmt_plan_id
1293 ,x_pmt_plan_id => p_pmt_plan_assign_rec.pmt_plan_id
1294 ,x_salesrep_id => p_pmt_plan_assign_rec.salesrep_id
1295 ,x_org_id => p_pmt_plan_assign_rec.org_id
1296 ,x_role_id => NULL
1297 ,x_credit_type_id => l_credit_type_id -- Obsolete
1298 ,x_start_date => p_pmt_plan_assign_rec.start_date
1299 ,x_end_date => p_pmt_plan_assign_rec.end_date
1300 ,x_minimum_amount => p_pmt_plan_assign_rec.minimum_amount
1301 ,x_maximum_amount => p_pmt_plan_assign_rec.maximum_amount
1302 ,x_max_recovery_amount => NULL -- Obsolete
1303 ,x_last_update_date => sysdate
1304 ,x_last_updated_by => fnd_global.user_id
1305 ,x_last_update_login => fnd_global.login_id
1306 ,x_object_version_number => p_pmt_plan_assign_rec.object_version_number
1307 ,x_lock_flag => p_pmt_plan_assign_rec.lock_flag
1308 );
1309
1310 -- if the lock_flag is being set, then blow away role_pmt_plan_id
1311 IF p_pmt_plan_assign_rec.lock_flag = 'Y' THEN
1312 UPDATE cn_srp_pmt_plans_all
1313 SET role_pmt_plan_id = NULL,
1314 srp_role_id = NULL
1315 WHERE srp_pmt_plan_id = p_pmt_plan_assign_rec.srp_pmt_plan_id;
1316 END IF;
1317
1318 END IF;
1319
1320 -- raise business event
1321 business_event
1322 (p_operation => 'Update',
1323 p_pmt_plan_assign_rec => p_pmt_plan_assign_rec);
1324
1325
1326 -- build notes
1327 l_notemsg := NULL;
1328
1329 -- raise notes
1330 IF l_oldrec.pmt_plan_id <> p_pmt_plan_assign_rec.pmt_plan_id THEN
1331 SELECT name INTO l_oldname FROM cn_pmt_plans_all
1332 WHERE pmt_plan_id = l_oldrec.pmt_plan_id;
1333 SELECT name INTO l_newname FROM cn_pmt_plans_all
1334 WHERE pmt_plan_id = p_pmt_plan_assign_rec.pmt_plan_id;
1335 get_note('PMT_PLAN', l_oldname, l_newname, l_notemsg);
1336 END IF;
1337
1338 IF l_oldrec.start_date <> p_pmt_plan_assign_rec.start_date THEN
1339 get_note('START_DATE', l_oldrec.start_date, p_pmt_plan_assign_rec.start_date, l_notemsg);
1340 END IF;
1341
1342 IF Nvl(l_oldrec.end_date, fnd_api.g_miss_date) <> Nvl(p_pmt_plan_assign_rec.end_date, fnd_api.g_miss_date) THEN
1343 get_note('END_DATE', l_oldrec.end_date, p_pmt_plan_assign_rec.end_date, l_notemsg);
1344 END IF;
1345
1346 IF Nvl(l_oldrec.minimum_amount, -1) <> Nvl(p_pmt_plan_assign_rec.minimum_amount, -1) THEN
1347 get_note('MIN_AMT', l_oldrec.minimum_amount, p_pmt_plan_assign_rec.minimum_amount, l_notemsg);
1348 END IF;
1349
1350 IF Nvl(l_oldrec.maximum_amount, -1) <> Nvl(p_pmt_plan_assign_rec.maximum_amount, -1) THEN
1351 get_note('MAX_AMT', l_oldrec.maximum_amount, p_pmt_plan_assign_rec.maximum_amount, l_notemsg);
1352 END IF;
1353
1354 IF l_oldrec.lock_flag <> p_pmt_plan_assign_rec.lock_flag THEN
1355 get_note('LOCK_FLAG', l_oldrec.lock_flag, p_pmt_plan_assign_rec.lock_flag, l_notemsg);
1356 END IF;
1357
1358 IF (l_notemsg IS NOT NULL) THEN
1359 raise_note(p_pmt_plan_assign_rec.srp_pmt_plan_id, l_notemsg);
1360 END IF;
1361
1362 -- pick up new object version number
1363 SELECT object_version_number
1364 INTO p_pmt_plan_assign_rec.object_version_number
1365 FROM cn_srp_pmt_plans_all
1366 WHERE srp_pmt_plan_id = p_pmt_plan_assign_rec.srp_pmt_plan_id;
1367 -- End of API body.
1368
1369 -- Standard check of p_commit.
1370
1371 IF FND_API.To_Boolean( p_commit ) THEN
1372 COMMIT WORK;
1373 END IF;
1374
1375 -- Standard call to get message count and if count is 1, get message info
1376 FND_MSG_PUB.Count_And_Get
1377 (
1378 p_count => x_msg_count ,
1379 p_data => x_msg_data ,
1380 p_encoded => FND_API.G_FALSE
1381 );
1382
1383 EXCEPTION
1384 WHEN FND_API.G_EXC_ERROR THEN
1385 ROLLBACK TO Update_Srp_Pmt_Plan;
1386 x_return_status := FND_API.G_RET_STS_ERROR ;
1387 FND_MSG_PUB.Count_And_Get
1388 (
1389 p_count => x_msg_count ,
1390 p_data => x_msg_data ,
1391 p_encoded => FND_API.G_FALSE
1392 );
1393 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1394 ROLLBACK TO Update_Srp_Pmt_Plan;
1395 x_loading_status := 'UNEXPECTED_ERR';
1396 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1397 FND_MSG_PUB.Count_And_Get
1398 (
1399 p_count => x_msg_count ,
1400 p_data => x_msg_data ,
1401 p_encoded => FND_API.G_FALSE
1402 );
1403 WHEN OTHERS THEN
1404 ROLLBACK TO Update_Srp_Pmt_Plan;
1405 x_loading_status := 'UNEXPECTED_ERR';
1406 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1407 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1408 THEN
1409 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME ,l_api_name );
1410 END IF;
1411 FND_MSG_PUB.Count_And_Get
1412 (
1413 p_count => x_msg_count ,
1414 p_data => x_msg_data ,
1415 p_encoded => FND_API.G_FALSE
1416 );
1417
1418 END update_srp_pmt_plan;
1419
1420 -- --------------------------------------------------------------------------*
1421 -- Procedure: Valid_Delete_Srp_Pmt_Plan
1422 -- --------------------------------------------------------------------------*
1423 PROCEDURE valid_delete_srp_pmt_plan
1424 ( p_srp_pmt_plan_id IN NUMBER,
1425 p_init_msg_list IN VARCHAR2,
1426 x_loading_status OUT NOCOPY VARCHAR2 ,
1427 x_return_status OUT NOCOPY VARCHAR2 ,
1428 x_msg_count OUT NOCOPY NUMBER ,
1429 x_msg_data OUT NOCOPY VARCHAR2
1430 ) IS
1431
1432 l_api_name CONSTANT VARCHAR2(30) := 'Valid_Delete_Srp_Pmt_Plan';
1433
1434 CURSOR spp_csr( l_srp_pmt_plan_id NUMBER ) IS
1435 SELECT *
1436 FROM cn_srp_pmt_plans
1437 WHERE srp_pmt_plan_id = l_srp_pmt_plan_id;
1438
1439 l_spp_rec spp_csr%ROWTYPE;
1440
1441 BEGIN
1442 -- Initialize message list if p_init_msg_list is set to TRUE.
1443 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1444 FND_MSG_PUB.initialize;
1445 END IF;
1446
1447 -- Initialize API return status to success
1448 x_return_status := FND_API.G_RET_STS_SUCCESS;
1449 x_loading_status := 'CN_DELETED';
1450
1451
1452 --
1453 -- Check if delete operation allowed
1454 --
1455 OPEN spp_csr(p_srp_pmt_plan_id);
1456 FETCH spp_csr INTO l_spp_rec;
1457 CLOSE spp_csr;
1458 /*
1459 IF (NVL(l_spp_rec.lock_flag, 'N') = 'Y') THEN
1460 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1461 FND_MESSAGE.Set_Name('CN', 'CN_SRP_PMT_PLAN_LOCKED');
1462 FND_MSG_PUB.Add;
1463 END IF;
1464 x_loading_status := 'CN_SRP_PMT_PLAN_LOCKED';
1465 RAISE FND_API.G_EXC_ERROR ;
1466 END IF;
1467
1468 check_operation_allowed
1469 ( x_return_status => x_return_status,
1470 x_msg_count => x_msg_count,
1471 x_msg_data => x_msg_data,
1472 p_salesrep_id => l_spp_rec.salesrep_id,
1473 p_start_date => l_spp_rec.start_date,
1474 p_end_date => l_spp_rec.end_date,
1475 p_loading_status => x_loading_status,
1476 x_loading_status => x_loading_status
1477 );
1478 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
1479 RAISE FND_API.G_EXC_ERROR ;
1480 END IF;
1481 */
1482 EXCEPTION
1483 WHEN FND_API.G_EXC_ERROR THEN
1484 x_return_status := FND_API.G_RET_STS_ERROR ;
1485 FND_MSG_PUB.Count_And_Get
1486
1487 (
1488 p_count => x_msg_count ,
1489 p_data => x_msg_data ,
1490 p_encoded => FND_API.G_FALSE
1491 );
1492 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1493 x_loading_status := 'UNEXPECTED_ERR';
1494 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1495 FND_MSG_PUB.Count_And_Get
1496 (
1497 p_count => x_msg_count ,
1498 p_data => x_msg_data ,
1499 p_encoded => FND_API.G_FALSE
1500 );
1501 WHEN OTHERS THEN
1502 x_loading_status := 'UNEXPECTED_ERR';
1503 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1504 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1505 THEN
1506 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME ,l_api_name );
1507 END IF;
1508 FND_MSG_PUB.Count_And_Get
1509 (
1510 p_count => x_msg_count ,
1511 p_data => x_msg_data ,
1512 p_encoded => FND_API.G_FALSE
1513 );
1514
1515
1516 END valid_delete_srp_pmt_plan;
1517
1518 -- --------------------------------------------------------------------------*
1519 -- Procedure: Delete_Srp_Pmt_Plan
1520 -- --------------------------------------------------------------------------*
1521 PROCEDURE Delete_Srp_Pmt_Plan
1522 ( p_api_version IN NUMBER ,
1523 p_init_msg_list IN VARCHAR2,
1524 p_commit IN VARCHAR2,
1525 p_validation_level IN NUMBER,
1526 x_return_status OUT NOCOPY VARCHAR2 ,
1527 x_loading_status OUT NOCOPY VARCHAR2 ,
1528 x_msg_count OUT NOCOPY NUMBER ,
1529 x_msg_data OUT NOCOPY VARCHAR2 ,
1530 p_srp_pmt_plan_id IN NUMBER) IS
1531
1532 l_api_name CONSTANT VARCHAR2(30) := 'Delete_Srp_Pmt_Plan';
1533 l_api_version CONSTANT NUMBER := 1.0;
1534 x_note_id NUMBER;
1535 l_note_msg VARCHAR2(240);
1536 l_pmt_plan_assign_rec pmt_plan_assign_rec;
1537
1538 CURSOR spp_info_cur IS
1539 select p.name, r.name role_name, spp.start_date, spp.end_date, spp.minimum_amount, spp.maximum_amount, spp.lock_flag, spp.salesrep_id
1540 from cn_srp_pmt_plans_all spp, cn_pmt_plans_all p, cn_role_pmt_plans_all rpp, cn_roles r
1541 where spp.srp_pmt_plan_id = p_srp_pmt_plan_id
1542 and spp.role_pmt_plan_id = rpp.role_pmt_plan_id(+)
1543 and spp.pmt_plan_id = p.pmt_plan_id
1544 and rpp.role_id = r.role_id(+);
1545
1546 spp_info spp_info_cur%ROWTYPE;
1547
1548 BEGIN
1549 -- Standard Start of API savepoint
1550 SAVEPOINT delete_srp_pmt_plan;
1551
1552 -- Standard call to check for call compatibility.
1553
1554 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
1555 p_api_version ,
1556 l_api_name ,
1557 G_PKG_NAME )
1558 THEN
1559 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1560 END IF;
1561
1562 -- Initialize message list if p_init_msg_list is set to TRUE.
1563 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1564 FND_MSG_PUB.initialize;
1565 END IF;
1566
1567 -- Initialize API return status to success
1568 x_return_status := FND_API.G_RET_STS_SUCCESS;
1569 x_loading_status := 'CN_DELETED';
1570
1571 -- validate delete
1572 valid_delete_srp_pmt_plan
1573 ( p_srp_pmt_plan_id => p_srp_pmt_plan_id,
1574 p_init_msg_list => p_init_msg_list,
1575 x_loading_status => x_loading_status,
1576 x_return_status => x_return_status,
1577 x_msg_count => x_msg_count,
1578 x_msg_data => x_msg_data);
1579
1580 IF x_return_status <> fnd_api.g_ret_sts_success THEN
1581 RAISE fnd_api.g_exc_error;
1582 END IF;
1583
1584 -- create note
1585 OPEN spp_info_cur;
1586 FETCH spp_info_cur INTO spp_info;
1587 CLOSE spp_info_cur;
1588
1589 l_note_msg := fnd_message.get_string('CN', 'CN_SPP_DEL_NOTE');
1590 l_note_msg := l_note_msg || spp_info.name || ', ' ||
1591 spp_info.role_name || ', ' ||
1592 spp_info.start_date || ', ' ||
1593 spp_info.end_date || ', ' ||
1594 spp_info.minimum_amount || ', ' ||
1595 spp_info.maximum_amount || ', ' ||
1596 spp_info.lock_flag;
1597
1598 jtf_notes_pub.create_note
1599 ( p_api_version => 1.0,
1600 x_return_status => x_return_status,
1601 x_msg_count => x_msg_count,
1602 x_msg_data => x_msg_data,
1603 p_source_object_id => spp_info.salesrep_id,
1604 p_source_object_code => 'CN_SALESREPS',
1605 p_notes => l_note_msg,
1606 p_notes_detail => l_note_msg,
1607 p_note_type => 'CN_SYSGEN', -- for system generated
1608 x_jtf_note_id => x_note_id -- returned
1609 );
1610
1611 -- Delete record
1612 cn_srp_pmt_plans_pkg.delete_row
1613 (x_srp_pmt_plan_id => p_srp_pmt_plan_id);
1614
1615 -- raise business event
1616 l_pmt_plan_assign_rec.srp_pmt_plan_id := p_srp_pmt_plan_id;
1617 business_event
1618 (p_operation => 'Remove',
1619 p_pmt_plan_assign_rec => l_pmt_plan_assign_rec);
1620
1621 -- End of API body
1622
1623 -- Standard check of p_commit.
1624
1625 IF FND_API.To_Boolean( p_commit ) THEN
1626 COMMIT WORK;
1627 END IF;
1628
1629
1630 -- Standard call to get message count and if count is 1, get message info.
1631
1632 FND_MSG_PUB.Count_And_Get
1633 (
1634 p_count => x_msg_count ,
1635 p_data => x_msg_data ,
1636 p_encoded => FND_API.G_FALSE
1637 );
1638
1639 EXCEPTION
1640 WHEN FND_API.G_EXC_ERROR THEN
1641 ROLLBACK TO Delete_Srp_Pmt_Plan;
1642 x_return_status := FND_API.G_RET_STS_ERROR ;
1643 FND_MSG_PUB.Count_And_Get
1644 (
1645 p_count => x_msg_count ,
1646 p_data => x_msg_data ,
1647 p_encoded => FND_API.G_FALSE
1648 );
1649 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1650 ROLLBACK TO Delete_Srp_Pmt_Plan;
1651 x_loading_status := 'UNEXPECTED_ERR';
1652 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1653 FND_MSG_PUB.Count_And_Get
1654 (
1655 p_count => x_msg_count ,
1656 p_data => x_msg_data ,
1657 p_encoded => FND_API.G_FALSE
1658 );
1659 WHEN OTHERS THEN
1660 ROLLBACK TO Delete_Srp_Pmt_Plan;
1661 x_loading_status := 'UNEXPECTED_ERR';
1662 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1663 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1664 THEN
1665 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME ,l_api_name );
1666 END IF;
1667 FND_MSG_PUB.Count_And_Get
1668 (
1669 p_count => x_msg_count ,
1670 p_data => x_msg_data ,
1671 p_encoded => FND_API.G_FALSE
1672 );
1673 END Delete_Srp_Pmt_Plan;
1674
1675 -- --------------------------------------------------------------------------*
1676 -- Procedure: Create_Mass_Asgn_Srp_Pmt_plan
1677 -- --------------------------------------------------------------------------*
1678
1679 PROCEDURE Create_Mass_Asgn_Srp_Pmt_Plan
1680 (
1681 p_api_version IN NUMBER,
1682 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1683 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1684 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1685 x_return_status OUT NOCOPY VARCHAR2,
1686 x_msg_count OUT NOCOPY NUMBER,
1687 x_msg_data OUT NOCOPY VARCHAR2,
1688 p_srp_role_id IN NUMBER,
1689 p_role_pmt_plan_id IN NUMBER,
1690 x_srp_pmt_plan_id OUT NOCOPY NUMBER,
1691 x_loading_status OUT NOCOPY VARCHAR2
1692 ) IS
1693
1694 l_api_name CONSTANT VARCHAR2(30) := 'Create_Mass_Asgn_Srp_Pmt_Plan';
1695 l_api_version CONSTANT NUMBER := 1.0;
1696 l_return_status VARCHAR2(2000);
1697 l_msg_count NUMBER;
1698 l_msg_data VARCHAR2(2000);
1699 l_srp_pmt_plan_id cn_srp_pmt_plans.srp_pmt_plan_id%TYPE;
1700 l_loading_status VARCHAR2(2000);
1701
1702 newrec pmt_plan_assign_rec;
1703 l_salesrep_id cn_salesreps.salesrep_id%TYPE;
1704 l_pmt_plan_id cn_pmt_plans.pmt_plan_id%TYPE;
1705 l_org_id cn_pmt_plans.org_id%TYPE;
1706 l_min_amt cn_pmt_plans.minimum_amount%TYPE;
1707 l_max_amt cn_pmt_plans.maximum_amount%TYPE;
1708 l_pp_start_date cn_pmt_plans.start_date%TYPE;
1709 l_pp_end_date cn_pmt_plans.end_date%TYPE;
1710 l_srp_start_date cn_srp_roles.start_date%TYPE;
1711 l_srp_end_date cn_pmt_plans.end_date%TYPE;
1712 l_start_date cn_srp_pmt_plans.start_date%TYPE;
1713 l_end_date cn_srp_pmt_plans.start_date%TYPE;
1714
1715 BEGIN
1716
1717 -- Standard Start of API savepoint
1718
1719 SAVEPOINT Create_Mass_Asgn_Srp_Pmt_Plan;
1720
1721 -- Standard call to check for call compatibility.
1722
1723 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
1724 p_api_version ,
1725 l_api_name ,
1726 G_PKG_NAME )
1727 THEN
1728 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1729 END IF;
1730
1731 -- Initialize message list if p_init_msg_list is set to TRUE.
1732 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1733 FND_MSG_PUB.initialize;
1734 END IF;
1735
1736 -- Initialize API return status to success
1737 x_return_status := FND_API.G_RET_STS_SUCCESS;
1738 x_loading_status := 'CN_PP_CREATED';
1739
1740 select pmt_plan_id, start_date, end_date
1741 into l_pmt_plan_id, l_pp_start_date, l_pp_end_date
1742 from cn_role_pmt_plans
1743 where role_pmt_plan_id = p_role_pmt_plan_id;
1744
1745 select minimum_amount, maximum_amount, org_id
1746 into l_min_amt, l_max_amt, l_org_id
1747 from cn_pmt_plans
1748 where pmt_plan_id = l_pmt_plan_id;
1749
1750 select salesrep_id, start_date, end_date
1751 into l_salesrep_id, l_srp_start_date, l_srp_end_date
1752 from cn_srp_roles
1753 where srp_role_id = p_srp_role_id
1754 and org_id = l_org_id;
1755
1756 l_start_date := NULL;
1757 l_end_date := NULL;
1758
1759 x_return_status := FND_API.G_RET_STS_SUCCESS;
1760 -- Start: Bug fix 5480386 5480540 CHANTHON
1761 get_masgn_date_intersect(
1762 p_srp_role_id => p_srp_role_id,
1763 p_role_pmt_plan_id => p_role_pmt_plan_id,
1764 x_start_date => l_start_date,
1765 x_end_date => l_end_date);
1766
1767
1768 /* if cn_api.date_range_overlap(
1769 a_start_date => l_srp_start_date,
1770 a_end_date => l_srp_end_date,
1771 b_start_date => l_pp_start_date,
1772 b_end_date => l_pp_end_date
1773 ) THEN
1774
1775 cn_api.get_date_range_intersect(
1776 a_start_date => l_srp_start_date,
1777 a_end_date => l_srp_end_date,
1778 b_start_date => l_pp_start_date,
1779 b_end_date => l_pp_end_date,
1780 x_start_date => l_start_date,
1781 x_end_date => l_end_date); */
1782 -- End: Bug fix 5480386 5480540 CHANTHON
1783
1784 newrec.salesrep_id := l_salesrep_id;
1785 newrec.pmt_plan_id := l_pmt_plan_id;
1786 newrec.minimum_amount := l_min_amt;
1787 newrec.maximum_amount := l_max_amt;
1788 newrec.start_date := l_start_date;
1789 newrec.end_date := l_end_date;
1790 newrec.srp_role_id := p_srp_role_id;
1791 newrec.role_pmt_plan_id := p_role_pmt_plan_id;
1792 newrec.org_id := l_org_id;
1793 newrec.lock_flag := 'N';
1794
1795
1796 create_srp_pmt_plan
1797 (p_api_version => p_api_version,
1798 p_init_msg_list => p_init_msg_list,
1799 p_commit => p_commit,
1800 p_validation_level => p_validation_level,
1801 x_return_status => l_return_status,
1802 x_msg_count => l_msg_count,
1803 x_msg_data => l_msg_data,
1804 p_pmt_plan_assign_rec=> newrec,
1805 x_loading_status => l_loading_status);
1806
1807 /*
1808 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1809 RAISE fnd_api.g_exc_error;
1810 END IF;
1811 */
1812 l_return_status:=FND_API.G_RET_STS_SUCCESS;
1813 x_return_status := l_return_status;
1814 x_loading_status := l_loading_status;
1815
1816 -- END IF;
1817
1818 -- Standard check of p_commit.
1819
1820 IF FND_API.To_Boolean( p_commit ) THEN
1821 COMMIT WORK;
1822 END IF;
1823
1824 -- Standard call to get message count and if count is 1, get message info
1825 FND_MSG_PUB.Count_And_Get
1826 (
1827 p_count => x_msg_count ,
1828 p_data => x_msg_data ,
1829 p_encoded => FND_API.G_FALSE
1830 );
1831
1832 EXCEPTION
1833 WHEN FND_API.G_EXC_ERROR THEN
1834 ROLLBACK TO Create_Mass_Asgn_Srp_Pmt_Plan;
1835 x_return_status := FND_API.G_RET_STS_ERROR ;
1836 FND_MSG_PUB.Count_And_Get
1837 (
1838 p_count => x_msg_count ,
1839 p_data => x_msg_data ,
1840 p_encoded => FND_API.G_FALSE
1841 );
1842 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1843 ROLLBACK TO Create_Mass_Asgn_Srp_Pmt_Plan;
1844 x_loading_status := 'UNEXPECTED_ERR';
1845 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1846 FND_MSG_PUB.Count_And_Get
1847 (
1848 p_count => x_msg_count ,
1849 p_data => x_msg_data ,
1850 p_encoded => FND_API.G_FALSE
1851 );
1852 WHEN OTHERS THEN
1853 ROLLBACK TO Create_Mass_Asgn_Srp_Pmt_Plan;
1854 x_loading_status := 'UNEXPECTED_ERR';
1855 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1856 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1857 THEN
1858 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME ,l_api_name );
1859 END IF;
1860 FND_MSG_PUB.Count_And_Get
1861 (
1862 p_count => x_msg_count ,
1863 p_data => x_msg_data ,
1864 p_encoded => FND_API.G_FALSE
1865 );
1866
1867 END Create_Mass_Asgn_Srp_Pmt_Plan;
1868
1869 -- --------------------------------------------------------------------------*
1870 -- Procedure: Update_Mass_Asgn_Srp_Pmt_plan
1871 -- --------------------------------------------------------------------------*
1872
1873 PROCEDURE Update_Mass_Asgn_Srp_Pmt_plan
1874 (
1875 p_api_version IN NUMBER,
1876 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1877 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1878 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1879 x_return_status OUT NOCOPY VARCHAR2,
1880 x_msg_count OUT NOCOPY NUMBER,
1881 x_msg_data OUT NOCOPY VARCHAR2,
1882 p_srp_role_id IN NUMBER,
1883 p_role_pmt_plan_id IN NUMBER,
1884 x_loading_status OUT NOCOPY VARCHAR2
1885 ) IS
1886
1887 l_api_name CONSTANT VARCHAR2(30) := 'Update_Mass_Asgn_Srp_Pmt_Plan';
1888 l_api_version CONSTANT NUMBER := 1.0;
1889 l_return_status VARCHAR2(2000);
1890 l_msg_count NUMBER;
1891 l_msg_data VARCHAR2(2000);
1892 l_srp_pmt_plan_id cn_srp_pmt_plans.srp_pmt_plan_id%TYPE;
1893 l_loading_status VARCHAR2(2000);
1894 l_count NUMBER;
1895 l_count_srp_pmt_plan NUMBER;
1896
1897 newrec pmt_plan_assign_rec;
1898 l_salesrep_id_old cn_salesreps.salesrep_id%TYPE;
1899 l_salesrep_id_new cn_salesreps.salesrep_id%TYPE;
1900 l_pmt_plan_id_new cn_pmt_plans.pmt_plan_id%TYPE;
1901 l_org_id cn_pmt_plans.org_id%TYPE;
1902 l_min_amt_new cn_pmt_plans.minimum_amount%TYPE;
1903 l_max_amt_new cn_pmt_plans.maximum_amount%TYPE;
1904 l_pp_start_date_new cn_pmt_plans.start_date%TYPE;
1905 l_pp_end_date_new cn_pmt_plans.end_date%TYPE;
1906 l_srp_start_date_new cn_srp_roles.start_date%TYPE;
1907 --l_srp_end_date_new cn_pmt_plans.end_date%TYPE;
1908 l_srp_end_date_new cn_srp_roles.end_date%TYPE;
1909 l_start_date_old cn_srp_pmt_plans.start_date%TYPE;
1910 l_start_date_new cn_srp_pmt_plans.start_date%TYPE;
1911 l_end_date_old cn_srp_pmt_plans.start_date%TYPE;
1912 l_end_date_new cn_srp_pmt_plans.start_date%TYPE;
1913 l_role_pp_start_date cn_role_pmt_plans.start_date%TYPE;
1914 l_role_pp_end_date cn_role_pmt_plans.end_date%TYPE;
1915
1916 --Added payment group code for bug 3560026 by Julia Huang on 4/7/2004.
1917 l_pgc cn_pmt_plans.payment_group_code%TYPE;
1918 l_worksheets NUMBER;
1919 l_end_of_time CONSTANT DATE := To_date('31-12-9999', 'DD-MM-YYYY');
1920
1921 BEGIN
1922
1923 -- Standard Start of API savepoint
1924
1925 SAVEPOINT Update_Mass_Asgn_Srp_Pmt_plan;
1926
1927 -- Standard call to check for call compatibility.
1928
1929 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
1930 p_api_version ,
1931 l_api_name ,
1932 G_PKG_NAME )
1933 THEN
1934 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1935 END IF;
1936
1937 -- Initialize message list if p_init_msg_list is set to TRUE.
1938 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1939 FND_MSG_PUB.initialize;
1940 END IF;
1941
1942 -- Initialize API return status to success
1943 x_return_status := FND_API.G_RET_STS_SUCCESS;
1944 x_loading_status := 'CN_PP_UPDATED';
1945
1946 select org_id into l_org_id
1947 from cn_role_pmt_plans
1948 where role_pmt_plan_id = p_role_pmt_plan_id;
1949
1950 select salesrep_id
1951 into l_salesrep_id_old
1952 from cn_srp_roles
1953 where srp_role_id = p_srp_role_id
1954 and org_id = l_org_id;
1955
1956 --Added to check if the role_pay_group_id is existing in cn_srp_pmt_plans
1957 select count(*) into l_count from cn_srp_pmt_plans
1958 where salesrep_id = l_salesrep_id_old
1959 AND srp_role_id = p_srp_role_id
1960 AND role_pmt_plan_id = p_role_pmt_plan_id;
1961
1962 IF (l_count <> 0)
1963 THEN
1964 --Bug 3670276 by Julia Huang on 6/4/04 to avoid the following
1965 --1.Cartesian join. Line changed: AND spp.role_pmt_plan_id = crpp.role_pmt_plan_id --p_role_pmt_plan_id
1966 --2.Full Table Scan. Added 1 index: create index cn_srp_pmt_plans_n1 ON cn_srp_pmt_plans_all(srp_role_id,org_id)
1967 select spp.start_date, spp.end_date, spp.salesrep_id,
1968 crpp.start_date, crpp.end_date, spp.srp_pmt_plan_id
1969 into l_start_date_old, l_end_date_old, l_salesrep_id_old,
1970 l_role_pp_start_date, l_role_pp_end_date, l_srp_pmt_plan_id
1971 from cn_srp_pmt_plans_all spp, cn_pmt_plans_all cpp, cn_role_pmt_plans_all crpp
1972 where spp.srp_role_id = p_srp_role_id
1973 AND spp.role_pmt_plan_id = crpp.role_pmt_plan_id --p_role_pmt_plan_id
1974 AND crpp.role_pmt_plan_id = p_role_pmt_plan_id
1975 AND cpp.pmt_plan_id = spp.pmt_plan_id;
1976
1977 END IF;
1978
1979 select pmt_plan_id, start_date, end_date
1980 into l_pmt_plan_id_new, l_pp_start_date_new, l_pp_end_date_new
1981 from cn_role_pmt_plans
1982 where role_pmt_plan_id = p_role_pmt_plan_id;
1983
1984 --Added payment group code for bug 3560026 by Julia Huang on 4/7/2004.
1985 select minimum_amount, maximum_amount, payment_group_code, org_id
1986 into l_min_amt_new, l_max_amt_new, l_pgc, l_org_id
1987 from cn_pmt_plans
1988 where pmt_plan_id = l_pmt_plan_id_new;
1989
1990 select salesrep_id, start_date, end_date
1991 into l_salesrep_id_new, l_srp_start_date_new, l_srp_end_date_new
1992 from cn_srp_roles
1993 where srp_role_id = p_srp_role_id
1994 and org_id = l_org_id;
1995
1996 --Added to check if the a record exists in cn_srp_pmt_plans for the dates passed for bug 3147026
1997 /* Commented out by Julia Huang for bug 3560026 by Julia Huang on 4/7/2004
1998 select count(*) into l_count_srp_pmt_plan from cn_srp_pmt_plans where salesrep_id=l_salesrep_id_old
1999 and ((l_pp_start_date_new between start_date and nvl(end_date,l_null_date))
2000 or (nvl(l_pp_end_date_new,l_null_date) between start_date and nvl(end_date,l_null_date)));
2001 */
2002 SELECT COUNT(*) INTO l_count_srp_pmt_plan
2003 FROM cn_srp_pmt_plans cspp, cn_pmt_plans cpp
2004 WHERE cspp.salesrep_id = l_salesrep_id_old
2005 AND ((l_pp_start_date_new BETWEEN cspp.start_date AND NVL(cspp.end_date,l_pp_start_date_new))
2006 OR (NVL(l_pp_end_date_new,l_pp_start_date_new) BETWEEN cspp.start_date AND NVL(cspp.end_date,l_pp_end_date_new)))
2007 AND cspp.pmt_plan_id = cpp.pmt_plan_id
2008 AND cpp.payment_group_code = l_pgc;
2009
2010 l_start_date_new := NULL;
2011 l_end_date_new := NULL;
2012
2013 x_return_status := FND_API.G_RET_STS_SUCCESS;
2014
2015 if cn_api.date_range_overlap(
2016 a_start_date => l_srp_start_date_new,
2017 a_end_date => l_srp_end_date_new,
2018 b_start_date => l_pp_start_date_new,
2019 b_end_date => l_pp_end_date_new
2020 ) THEN
2021
2022 /* Bug No 5525456 */
2023
2024 get_masgn_date_intersect(
2025 p_role_pmt_plan_id,
2026 p_srp_role_id,
2027 x_start_date => l_start_date_new,
2028 x_end_date => l_end_date_new);
2029
2030 newrec.salesrep_id := l_salesrep_id_new;
2031 newrec.srp_pmt_plan_id:= l_srp_pmt_plan_id;
2032 newrec.pmt_plan_id := l_pmt_plan_id_new;
2033 newrec.minimum_amount := l_min_amt_new;
2034 newrec.maximum_amount := l_max_amt_new;
2035 newrec.start_date := l_start_date_new;
2036 newrec.end_date := l_end_date_new;
2037 newrec.lock_flag := 'N';
2038 newrec.srp_role_id := p_srp_role_id;
2039 newrec.role_pmt_plan_id := p_role_pmt_plan_id;
2040 newrec.org_id := l_org_id;
2041
2042 if (l_count>0) then
2043 /* commented below code by Naren to fix bug 12821986
2044
2045 SELECT count(*) into l_worksheets
2046 FROM cn_payment_worksheets_all W, cn_period_statuses_all prd,
2047 cn_payruns_all prun, cn_srp_pmt_plans_all spp
2048 WHERE w.salesrep_id = spp.salesrep_id
2049 AND w.quota_id is null
2050 AND prun.pay_period_id = prd.period_id
2051 AND prun.org_id = prd.org_id
2052 AND prun.payrun_id = w.payrun_id
2053 AND spp.srp_pmt_plan_id = l_srp_pmt_plan_id
2054 AND (((spp.end_date IS NOT NULL) AND (prd.end_date IS NOT NULL)
2055 AND (prd.start_date <= spp.end_date)
2056 AND (prd.end_date >= spp.start_date))
2057 OR ((spp.end_date IS NULL) AND (prd.end_date IS NOT NULL)
2058 AND (prd.end_date >= spp.start_date))
2059 OR ((spp.end_date IS NULL) AND (prd.end_date IS NULL)))
2060 AND (NVL(l_end_date_new, l_end_of_time) < NVL(prd.end_date, l_end_of_time)
2061 OR l_start_date_new > prd.start_date);
2062
2063 */
2064
2065 /* Added by Naren to fix bug 12821986*/
2066
2067 SELECT count(*) into l_worksheets
2068 FROM cn_payment_worksheets_all W, cn_period_statuses_all prd,
2069 cn_payruns_all prun, cn_srp_pmt_plans_all spp
2070 WHERE w.salesrep_id = spp.salesrep_id
2071 AND w.quota_id is null
2072 AND prun.pay_period_id = prd.period_id
2073 AND prun.org_id = prd.org_id
2074 AND prun.payrun_id = w.payrun_id
2075 AND spp.srp_pmt_plan_id = l_srp_pmt_plan_id
2076 AND prun.status ='UNPAID'
2077 AND (((spp.end_date IS NOT NULL) AND (prd.end_date IS NOT NULL)
2078 AND (prd.start_date <= spp.end_date)
2079 AND (prd.end_date >= spp.start_date))
2080 OR ((spp.end_date IS NULL) AND (prd.end_date IS NOT NULL)
2081 AND (prd.end_date >= spp.start_date))
2082 OR ((spp.end_date IS NULL) AND (prd.end_date IS NULL)))
2083 AND (NVL(l_end_date_new, l_end_of_time) >= NVL(prd.end_date, l_end_of_time)
2084 OR l_start_date_new <= prd.start_date);
2085
2086
2087 IF (l_worksheets = 0) THEN
2088
2089 update_srp_pmt_plan
2090 (
2091 p_api_version => p_api_version,
2092 p_init_msg_list => p_init_msg_list,
2093 p_commit => p_commit,
2094 p_validation_level => p_validation_level,
2095 x_return_status => l_return_status,
2096 x_msg_count => l_msg_count,
2097 x_msg_data => l_msg_data,
2098 p_pmt_plan_assign_rec=> newrec,
2099 x_loading_status => l_loading_status);
2100
2101 /*
2102 IF l_return_status <> fnd_api.g_ret_sts_success THEN
2103 RAISE fnd_api.g_exc_error;
2104 END IF;
2105 */
2106 l_return_status:=FND_API.G_RET_STS_SUCCESS;
2107 x_return_status := l_return_status;
2108 x_loading_status := l_loading_status;
2109
2110 ELSE
2111 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
2112 FND_MESSAGE.Set_Name('CN', 'CN_SPP_UPDATE_NOT_ALLOWED');
2113 FND_MSG_PUB.Add;
2114 END IF;
2115 RAISE FND_API.G_EXC_ERROR;
2116 END IF;
2117
2118 -- Added to create a cn_srp_pmt_plan record if there are no records for the the date range for bug 3147026
2119 ELSIF (l_count_srp_pmt_plan = 0 )
2120 THEN
2121
2122 SELECT count(*)
2123 INTO l_count_srp_pmt_plan
2124 FROM cn_srp_pmt_plans
2125 WHERE salesrep_id = l_salesrep_id_new
2126 AND org_id = l_org_id
2127 AND ((l_start_date_new between start_date and nvl(end_date,l_end_of_time))
2128 OR (nvl(l_end_date_new,l_end_of_time) between
2129 start_date and nvl(end_date,l_end_of_time)));
2130
2131 IF (l_count_srp_pmt_plan = 0) THEN
2132 Create_Srp_Pmt_Plan
2133 (
2134 p_api_version => p_api_version,
2135 p_init_msg_list => p_init_msg_list,
2136 p_commit => p_commit,
2137 p_validation_level => p_validation_level,
2138 x_return_status => l_return_status,
2139 x_msg_count => l_msg_count,
2140 x_msg_data => l_msg_data,
2141 p_pmt_plan_assign_rec=> newrec,
2142 x_loading_status => l_loading_status
2143 );
2144
2145 /*
2146 IF l_return_status <> fnd_api.g_ret_sts_success THEN
2147 RAISE fnd_api.g_exc_error;
2148 END IF;
2149 */
2150 l_return_status:=FND_API.G_RET_STS_SUCCESS;
2151 x_return_status := l_return_status;
2152 x_loading_status := l_loading_status;
2153 END IF;
2154 END IF;
2155
2156 ELSE
2157
2158 -- only delete if exists
2159 IF l_srp_pmt_plan_id IS NOT NULL THEN
2160 delete_srp_pmt_plan
2161 (p_api_version => p_api_version,
2162 p_init_msg_list => p_init_msg_list,
2163 p_commit => p_commit,
2164 p_validation_level => p_validation_level,
2165 x_return_status => l_return_status,
2166 x_msg_count => l_msg_count,
2167 x_msg_data => l_msg_data,
2168 p_srp_pmt_plan_id => l_srp_pmt_plan_id,
2169 x_loading_status => l_loading_status);
2170
2171 /*
2172 IF l_return_status <> fnd_api.g_ret_sts_success THEN
2173 RAISE fnd_api.g_exc_error;
2174 END IF;
2175 */
2176 l_return_status:=FND_API.G_RET_STS_SUCCESS;
2177 x_return_status := l_return_status;
2178 x_loading_status := l_loading_status;
2179 END IF;
2180 END IF;
2181
2182 -- Standard check of p_commit.
2183
2184 IF FND_API.To_Boolean( p_commit ) THEN
2185 COMMIT WORK;
2186 END IF;
2187
2188 -- Standard call to get message count and if count is 1, get message info
2189 FND_MSG_PUB.Count_And_Get
2190 (
2191 p_count => x_msg_count ,
2192 p_data => x_msg_data ,
2193 p_encoded => FND_API.G_FALSE
2194 );
2195
2196 EXCEPTION
2197 WHEN FND_API.G_EXC_ERROR THEN
2198 ROLLBACK TO Update_Mass_Asgn_Srp_Pmt_plan;
2199 x_return_status := FND_API.G_RET_STS_ERROR ;
2200 FND_MSG_PUB.Count_And_Get
2201 (
2202 p_count => x_msg_count ,
2203 p_data => x_msg_data ,
2204 p_encoded => FND_API.G_FALSE
2205 );
2206 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2207 ROLLBACK TO Update_Mass_Asgn_Srp_Pmt_plan;
2208 x_loading_status := 'UNEXPECTED_ERR';
2209 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2210 FND_MSG_PUB.Count_And_Get
2211 (
2212 p_count => x_msg_count ,
2213 p_data => x_msg_data ,
2214 p_encoded => FND_API.G_FALSE
2215 );
2216 WHEN OTHERS THEN
2217 ROLLBACK TO Update_Mass_Asgn_Srp_Pmt_plan;
2218 x_loading_status := 'UNEXPECTED_ERR';
2219 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2220 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2221 THEN
2222 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME ,l_api_name );
2223 END IF;
2224 FND_MSG_PUB.Count_And_Get
2225 (
2226 p_count => x_msg_count ,
2227 p_data => x_msg_data ,
2228 p_encoded => FND_API.G_FALSE
2229 );
2230
2231 End Update_Mass_Asgn_Srp_Pmt_plan;
2232
2233
2234 -- --------------------------------------------------------------------------*
2235 -- Procedure: Delete_Mass_Asgn_Srp_Pmt_plan
2236 -- --------------------------------------------------------------------------*
2237
2238 PROCEDURE Delete_Mass_Asgn_Srp_Pmt_Plan
2239 (p_api_version IN NUMBER,
2240 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2241 p_commit IN VARCHAR2 := FND_API.G_FALSE,
2242 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
2243 x_return_status OUT NOCOPY VARCHAR2,
2244 x_msg_count OUT NOCOPY NUMBER,
2245 x_msg_data OUT NOCOPY VARCHAR2,
2246 p_srp_role_id IN NUMBER,
2247 p_role_pmt_plan_id IN NUMBER,
2248 x_loading_status OUT NOCOPY VARCHAR2
2249 ) IS
2250
2251 l_api_name CONSTANT VARCHAR2(30) := 'Delete_Mass_Asgn_Srp_Pmt_Plan';
2252 l_api_version CONSTANT NUMBER := 1.0;
2253 l_return_status VARCHAR2(2000);
2254 l_msg_count NUMBER;
2255 l_msg_data VARCHAR2(2000);
2256 l_srp_pmt_plan_id cn_srp_pmt_plans.srp_pmt_plan_id%TYPE;
2257 l_loading_status VARCHAR2(2000);
2258
2259 newrec CN_SRP_PMT_PLANS_PUB.srp_pmt_plans_rec_type;
2260 l_salesrep_id cn_salesreps.salesrep_id%TYPE;
2261 l_pmt_plan_id cn_pmt_plans.pmt_plan_id%TYPE;
2262 l_min_amt cn_pmt_plans.minimum_amount%TYPE;
2263 l_max_amt cn_pmt_plans.maximum_amount%TYPE;
2264 l_pp_start_date cn_pmt_plans.start_date%TYPE;
2265 l_pp_end_date cn_pmt_plans.end_date%TYPE;
2266 l_srp_start_date cn_srp_roles.start_date%TYPE;
2267 l_srp_end_date cn_pmt_plans.end_date%TYPE;
2268 l_start_date cn_srp_pmt_plans.start_date%TYPE;
2269 l_end_date cn_srp_pmt_plans.start_date%TYPE;
2270 l_role_pp_start_date cn_role_pmt_plans.start_date%TYPE;
2271 l_role_pp_end_date cn_role_pmt_plans.end_date%TYPE;
2272
2273
2274 CURSOR spp_csr( l_srp_pmt_plan_id NUMBER ) IS
2275 SELECT *
2276 FROM cn_srp_pmt_plans
2277 WHERE srp_pmt_plan_id = l_srp_pmt_plan_id;
2278
2279 l_spp_rec spp_csr%ROWTYPE;
2280 l_dummy NUMBER;
2281
2282
2283 BEGIN
2284
2285 -- Standard Start of API savepoint
2286
2287 SAVEPOINT Delete_Mass_Asgn_Srp_Pmt_Plan;
2288
2289 -- Standard call to check for call compatibility.
2290
2291 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
2292 p_api_version ,
2293 l_api_name ,
2294 G_PKG_NAME )
2295 THEN
2296 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2297 END IF;
2298
2299 -- Initialize message list if p_init_msg_list is set to TRUE.
2300 IF FND_API.to_Boolean( p_init_msg_list ) THEN
2301 FND_MSG_PUB.initialize;
2302 END IF;
2303
2304 -- Initialize API return status to success
2305 x_return_status := FND_API.G_RET_STS_SUCCESS;
2306 x_loading_status := 'CN_PP_DELETED';
2307
2308 BEGIN
2309
2310 --Bug 3670276 by Julia Huang on 6/4/04 to avoid the following
2311 --1.Cartesian join. Line changed: AND spp.role_pmt_plan_id = crpp.role_pmt_plan_id --p_role_pmt_plan_id
2312 --2.Full Table Scan. Added 1 index: create index cn_srp_pmt_plans_n1 ON cn_srp_pmt_plans_all(srp_role_id,org_id)
2313 select spp.start_date, spp.end_date, spp.salesrep_id,
2314 cpp.minimum_amount, cpp.maximum_amount,
2315 crpp.start_date, crpp.end_date, spp.srp_pmt_plan_id
2316 into l_start_date, l_end_date, l_salesrep_id,
2317 l_min_amt, l_max_amt,
2318 l_role_pp_start_date, l_role_pp_end_date, l_srp_pmt_plan_id
2319 from cn_srp_pmt_plans spp, cn_pmt_plans cpp, cn_role_pmt_plans crpp
2320 where spp.srp_role_id = p_srp_role_id
2321 AND spp.role_pmt_plan_id = crpp.role_pmt_plan_id --p_role_pmt_plan_id
2322 AND crpp.role_pmt_plan_id = p_role_pmt_plan_id
2323 AND cpp.pmt_plan_id = spp.pmt_plan_id;
2324 EXCEPTION
2325 WHEN no_data_found THEN
2326 null;
2327 END;
2328
2329 IF ((l_salesrep_id IS NOT NULL)
2330
2331 AND
2332
2333 (cn_api.date_range_within(
2334 a_start_date => l_start_date,
2335 a_end_date => l_end_date,
2336 b_start_date => l_role_pp_start_date,
2337 b_end_date => l_role_pp_end_date
2338 )))
2339
2340
2341 THEN
2342
2343 --***********************************************************************
2344 -- Added by CHANTHON on 21-Aug-2006
2345 -- Bug 5465072
2346 -- Moved the code for checking valid delete from check_operation_allowed
2347 -- procedure to this method. This is to validate the resource's payment
2348 -- plan when deleting the mass assignment. If the resource has worksheets
2349 -- then the resource assignment is severed from the role and acts as a
2350 -- direct assignment. If no worksheets then the payment plan is deleted.
2351 --***********************************************************************
2352
2353 OPEN spp_csr(l_srp_pmt_plan_id);
2354 FETCH spp_csr INTO l_spp_rec;
2355 CLOSE spp_csr;
2356
2357
2358 SELECT COUNT(1) INTO l_dummy
2359 FROM cn_payment_worksheets_all W, cn_period_statuses_all prd,
2360 cn_payruns_all prun, cn_payment_transactions_all pmttrans
2361 WHERE w.salesrep_id = l_spp_rec.salesrep_id
2362 AND w.salesrep_id = pmttrans.credited_salesrep_id
2363 AND pmttrans.incentive_type_code = 'PMTPLN'
2364 AND prun.pay_period_id = prd.period_id
2365 AND prun.org_id = prd.org_id
2366 AND prun.payrun_id = w.payrun_id
2367 AND ( ((l_spp_rec.end_date IS NOT NULL) AND (prd.end_date IS NOT NULL)
2368 AND (prd.start_date <= l_spp_rec.end_date)
2369 AND (prd.end_date >= l_spp_rec.start_date))
2370 OR ((l_spp_rec.end_date IS NULL) AND (prd.end_date IS NOT NULL)
2371 AND (prd.end_date >= l_spp_rec.start_date))
2372 OR ((l_spp_rec.end_date IS NULL) AND (prd.end_date IS NULL))
2373 );
2374
2375 IF l_dummy > 0 then
2376 -- IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
2377 -- FND_MESSAGE.SET_NAME ('CN' , 'CN_SRP_PMT_PLAN_USED');
2378 -- FND_MSG_PUB.Add;
2379 update cn_srp_pmt_plans set srp_role_id = null, role_pmt_plan_id = null
2380 where srp_pmt_plan_id = l_srp_pmt_plan_id;
2381
2382 ElSIF l_dummy = 0 THEN
2383
2384 delete_srp_pmt_plan
2385 (p_api_version => p_api_version,
2386 p_init_msg_list => p_init_msg_list,
2387 p_commit => p_commit,
2388 p_validation_level => p_validation_level,
2389 x_return_status => l_return_status,
2390 x_msg_count => l_msg_count,
2391 x_msg_data => l_msg_data,
2392 p_srp_pmt_plan_id => l_srp_pmt_plan_id,
2393 x_loading_status => l_loading_status);
2394 END IF;
2395
2396 /*
2397 IF l_return_status <> fnd_api.g_ret_sts_success THEN
2398 RAISE fnd_api.g_exc_error;
2399 END IF;
2400 */
2401 l_return_status:=FND_API.G_RET_STS_SUCCESS;
2402
2403 x_return_status := l_return_status;
2404 x_loading_status := l_loading_status;
2405
2406 END IF;
2407
2408 -- Standard check of p_commit.
2409
2410 IF FND_API.To_Boolean( p_commit ) THEN
2411 COMMIT WORK;
2412 END IF;
2413
2414 -- Standard call to get message count and if count is 1, get message info
2415 FND_MSG_PUB.Count_And_Get
2416 (
2417 p_count => x_msg_count ,
2418 p_data => x_msg_data ,
2419 p_encoded => FND_API.G_FALSE
2420 );
2421
2422 EXCEPTION
2423 WHEN FND_API.G_EXC_ERROR THEN
2424 ROLLBACK TO Delete_Mass_Asgn_Srp_Pmt_Plan;
2425 x_return_status := FND_API.G_RET_STS_ERROR ;
2426 FND_MSG_PUB.Count_And_Get
2427 (
2428 p_count => x_msg_count ,
2429 p_data => x_msg_data ,
2430 p_encoded => FND_API.G_FALSE
2431 );
2432 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2433 ROLLBACK TO Delete_Mass_Asgn_Srp_Pmt_Plan;
2434 x_loading_status := 'UNEXPECTED_ERR';
2435 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2436 FND_MSG_PUB.Count_And_Get
2437 (
2438 p_count => x_msg_count ,
2439 p_data => x_msg_data ,
2440 p_encoded => FND_API.G_FALSE
2441 );
2442 WHEN OTHERS THEN
2443 ROLLBACK TO Delete_Mass_Asgn_Srp_Pmt_Plan;
2444 x_loading_status := 'UNEXPECTED_ERR';
2445 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2446 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2447 THEN
2448 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME ,l_api_name );
2449 END IF;
2450 FND_MSG_PUB.Count_And_Get
2451 (
2452 p_count => x_msg_count ,
2453 p_data => x_msg_data ,
2454 p_encoded => FND_API.G_FALSE
2455 );
2456
2457 END Delete_Mass_Asgn_Srp_Pmt_Plan;
2458
2459 END cn_srp_pmt_plans_pvt;