[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.26 2006/10/05 13:49:41 chanthon noship $ */
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 pt.payrun_id = prun.payrun_id
527 -- AND pt.payee_salesrep_id = w.salesrep_id
528 -- AND pt.pay_period_id = prun.pay_period_id
529 -- AND pt.incentive_type_code = 'PMTPLN'
530 AND (((spp.end_date IS NOT NULL) AND (prd.end_date IS NOT NULL)
531 AND (prd.start_date <= spp.end_date)
532 AND (prd.end_date >= spp.start_date))
533 OR ((spp.end_date IS NULL) AND (prd.end_date IS NOT NULL)
534 AND (prd.end_date >= spp.start_date))
535 OR ((spp.end_date IS NULL) AND (prd.end_date IS NULL)))
536 AND (NVL(p_end_date, l_end_of_time) < NVL(prd.end_date, l_end_of_time)
537 OR p_start_date > prd.start_date);
538
539
540
541
542 l_date_range_rec get_date_range%ROWTYPE;
543 paid_upd_payruns_row get_paid_upd_payruns%rowtype;
544 paid_del_payruns_row get_paid_del_payruns%rowtype;
545 adj_del_payruns_row get_adj_del_payruns%rowtype;
546 adj_upd_payruns_row get_adj_upd_payruns%rowtype;
547
548 BEGIN
549
550 /*------------------------------------------------------------------------------
551 * CHANTHON - 19-Sep-2006
552 * As per the latest update received, the behaviour should be as follows,
553 * If a resource has been paid or has a working/unpaid worksheet then we should
554 * not allow users to delete the payment plan for that period even if no
555 * payment plan adjustments are there in the worksheet.
556 * Same applies to shrinking. Can happen only till the period end date of the
557 * latest paid/unpaid worksheet.
558 -----------------------------------------------------------------------------*/
559 -- Initialize message list
560 FND_MSG_PUB.initialize;
561
562 IF p_operation = 'DELETE' THEN
563 -- check payruns involved
564 OPEN get_paid_del_payruns;
565 FETCH get_paid_del_payruns INTO paid_del_payruns_row;
566 CLOSE get_paid_del_payruns;
567
568 IF paid_del_payruns_row.estatus = 'ERROR' then
569 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
570 FND_MESSAGE.Set_Name('CN', 'CN_SRP_PP_NO_DEL');
571 FND_MSG_PUB.Add;
572 END IF;
573 ELSE
574 OPEN get_adj_del_payruns;
575 FETCH get_adj_del_payruns INTO adj_del_payruns_row;
576 CLOSE get_adj_del_payruns;
577
578 IF adj_del_payruns_row.estatus = 'ERROR' then
579 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
580 FND_MESSAGE.Set_Name('CN', 'CN_SRP_PP_NO_DEL_ADJ');
581 FND_MSG_PUB.Add;
582 END IF;
583 /* ELSE
584 -- With latest update this should never be called as even if unpaid paysheets
585 -- with no payment plan adjustments are present, delete cannot happen.
586 OPEN get_del_payruns;
587 FETCH get_del_payruns bulk collect INTO x_payrun_tbl;
588 CLOSE get_del_payruns; */
589 END IF;
590 END IF;
591
592 ELSIF p_operation = 'UPDATE' THEN
593 open get_date_range(p_srp_pmt_plan_id);
594 fetch get_date_range into l_date_range_rec;
595 close get_date_range;
596 l_old_start_date := l_date_range_rec.start_date;
597 l_fixed_old_end_date := l_date_range_rec.end_date;
598 l_fixed_end_date := Nvl(p_end_date, l_end_of_time);
599
600 OPEN get_paid_upd_payruns;
601 FETCH get_paid_upd_payruns INTO paid_upd_payruns_row;
602 CLOSE get_paid_upd_payruns;
603
604 IF paid_upd_payruns_row.estatus = 'ERROR' then
605 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
606 FND_MESSAGE.Set_Name('CN', 'CN_SRP_PP_NO_UPD');
607 FND_MSG_PUB.Add;
608 END IF;
609 ELSE
610 OPEN get_adj_upd_payruns;
611 FETCH get_adj_upd_payruns INTO adj_upd_payruns_row;
612 CLOSE get_adj_upd_payruns;
613
614 IF adj_upd_payruns_row.estatus = 'ERROR' then
615 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
616 FND_MESSAGE.Set_Name('CN', 'CN_SRP_PP_NO_UPD');
617 FND_MSG_PUB.Add;
618 END IF;
619 /* ELSE
620 -- With latest update this should never be called as even if unpaid paysheets
621 -- with no payment plan adjustments are present, shrink cannot happen.
622 OPEN get_upd_payruns;
623 FETCH get_upd_payruns bulk collect INTO x_payrun_tbl;
624 CLOSE get_upd_payruns; */
625 END IF;
626 END IF;
627 END IF;
628 END check_payruns;
629
630
631 -- ------------------------------------------------------------------------+
632 -- Procedure : Check_Operation_Allowed ( Delete / Update )
633 -- Description : This procedure is used to check if the srp pmt plan can
634 -- be updated or deleted.
635 -- ------------------------------------------------------------------------+
636 PROCEDURE check_operation_allowed
637 (x_return_status OUT NOCOPY VARCHAR2,
638 x_msg_count OUT NOCOPY NUMBER ,
639 x_msg_data OUT NOCOPY VARCHAR2,
640 p_salesrep_id IN NUMBER,
641 p_old_start_date IN DATE := FND_API.G_MISS_DATE,
642 p_old_end_date IN DATE := FND_API.G_MISS_DATE,
643 p_start_date IN DATE,
644 p_end_date IN DATE,
645 p_loading_status IN VARCHAR2,
646 x_loading_status OUT NOCOPY VARCHAR2
647 ) IS
648
649 l_api_name CONSTANT VARCHAR2(30) := 'Check_operation_allowed';
650 l_dummy NUMBER;
651
652 BEGIN
653 --
654 -- Initialize API return status to success
655 --
656 x_return_status := FND_API.G_RET_STS_SUCCESS;
657 x_loading_status := p_loading_status ;
658
659 -- Need to check if payment plan already been used in payment worksheet
660 -- during the period, if so operation not allowed
661 IF p_old_start_date = FND_API.G_MISS_DATE AND
662 p_old_end_date = FND_API.G_MISS_DATE THEN
663 -- Called from Delete Srp Payment Plan Assign
664
665 --***********************************************************************
666 -- Added By Kumar Sivasankaran
667 -- Delete Srp Payment Plan is Not allowed when it is used in the WOrksheet
668 -- Date 10/09/01
669 --***********************************************************************
670
671 --***********************************************************************
672 -- Modified by Sundar Venkat on 22 Aug 2002
673 -- Bug fix 2518847
674 --***********************************************************************
675
676 SELECT COUNT(1) INTO l_dummy
677 FROM cn_payment_worksheets_all W, cn_period_statuses_all prd,
678 cn_payruns_all prun, cn_payment_transactions_all pmttrans
679 WHERE w.salesrep_id = p_salesrep_id
680 AND w.salesrep_id = pmttrans.credited_salesrep_id
681 AND pmttrans.incentive_type_code = 'PMTPLN'
682 AND prun.pay_period_id = prd.period_id
683 AND prun.org_id = prd.org_id
684 AND prun.payrun_id = w.payrun_id
685 AND ( ((p_end_date IS NOT NULL) AND (prd.end_date IS NOT NULL)
686 AND (prd.start_date <= p_end_date)
687 AND (prd.end_date >= p_start_date))
688 OR ((p_end_date IS NULL) AND (prd.end_date IS NOT NULL)
689 AND (prd.end_date >= p_start_date))
690 OR ((p_end_date IS NULL) AND (prd.end_date IS NULL))
691 );
692
693 IF l_dummy > 0 then
694 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
695 FND_MESSAGE.SET_NAME ('CN' , 'CN_SRP_PMT_PLAN_USED');
696 FND_MSG_PUB.Add;
697 END IF;
698 x_loading_status := 'CN_SRP_PMT_PLAN_USED';
699 RAISE FND_API.G_EXC_ERROR ;
700 END IF;
701
702 ELSE
703 -- Called from Update Srp Payment Plan Assign
704 -- Check if during the old date range assign, any pmt plan already
705 -- been used, if so, cannot change start_date. If not been used, start
706 -- date can be extend or shrink.
707
708 --***********************************************************************
709 -- Added By Kumar Sivasankaran
710 -- Date 09/10/01
711 --
712 -- Shorten the end_date assignment
713 -- Check for the shortened date range, if pmt plan already been paid,
714 -- if so, cannot shorten
715 --***********************************************************************
716 IF ( ((p_old_end_date IS NOT NULL) AND (p_end_date IS NOT NULL) AND
717 (p_old_end_date > p_end_date))
718 OR
719 ((p_old_end_date IS NULL) AND (p_end_date IS NOT NULL)) ) THEN
720 SELECT COUNT(1) INTO l_dummy
721 FROM cn_payment_worksheets_all W, cn_period_statuses_all prd,cn_payruns_all prun
722 WHERE w.salesrep_id = p_salesrep_id
723 AND prun.pay_period_id = prd.period_id
724 AND prun.org_id = prd.org_id
725 AND prun.payrun_id = w.payrun_id
726 AND ( ((p_old_end_date IS NOT NULL) AND (prd.end_date IS NOT NULL)
727 AND (prd.start_date < p_old_end_date)
728 AND (prd.end_date > p_end_date))
729 OR ((p_old_end_date IS NULL) AND
730 ((prd.start_date > p_end_date) OR (prd.end_date > p_end_date)))
731 );
732
733 IF l_dummy > 0 THEN
734 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
735 FND_MESSAGE.SET_NAME ('CN' , 'CN_SPP_CANNOT_SHORTEN_ED');
736 FND_MSG_PUB.Add;
737 END IF;
738 x_loading_status := 'CN_SPP_CANNOT_SHORTEN_ED';
739 RAISE FND_API.G_EXC_ERROR ;
740 END IF;
741
742 END IF ; -- end IF end date change
743
744 -- Check if during the old date range assign, any pmt plan already
745 -- been used, if so, cannot change start_date. If not been used, start
746 -- date can be extend or shrink.
747
748 IF p_old_start_date <> p_start_date
749 THEN
750 SELECT COUNT(1) INTO l_dummy
751 FROM cn_payment_worksheets_all W, cn_period_statuses_all prd,cn_payruns_all prun
752 WHERE w.salesrep_id = p_salesrep_id
753 AND prun.pay_period_id = prd.period_id
754 AND prun.org_id = prd.org_id
755 AND prun.payrun_id = w.payrun_id
756 AND ( ((p_old_end_date IS NOT NULL) AND (prd.end_date IS NOT NULL)
757 AND (prd.start_date <= p_old_end_date)
758 AND (prd.end_date >= p_old_start_date))
759 OR ((p_old_end_date IS NULL) AND (prd.end_date IS NOT NULL)
760 AND (prd.end_date >= p_old_start_date))
761 );
762 IF l_dummy > 0 THEN
763 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
764 THEN
765 FND_MESSAGE.SET_NAME ('CN' , 'CN_SPP_UPDATE_NOT_ALLOWED');
766 FND_MSG_PUB.Add;
767 END IF;
768 x_loading_status := 'CN_SPP_UPDATE_NOT_ALLOWED';
769 RAISE FND_API.G_EXC_ERROR ;
770 END IF;
771 END IF ; -- end IF start date change
772 END IF; -- end if delete/update operation
773
774 EXCEPTION
775 WHEN FND_API.G_EXC_ERROR THEN
776 x_return_status := FND_API.G_RET_STS_ERROR ;
777
778 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
779 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
780 x_loading_status := 'UNEXPECTED_ERR';
781
782 WHEN OTHERS THEN
783 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
784 x_loading_status := 'UNEXPECTED_ERR';
785
786 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
787 THEN
788 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME ,l_api_name );
789 END IF;
790
791 END check_operation_allowed;
792
793 PROCEDURE get_note
794 (p_field IN VARCHAR2,
795 p_old_value IN VARCHAR2,
796 p_new_value IN VARCHAR2,
797 x_msg IN OUT nocopy VARCHAR2) IS
798
799 l_note_msg VARCHAR2(240);
800 BEGIN
801 fnd_message.set_name('CN', 'CN_SPP_UPD_NOTE');
802 fnd_message.set_token('FIELD', cn_api.get_lkup_meaning(p_field, 'CN_NOTE_FIELDS'));
803 fnd_message.set_token('OLD', p_old_value);
804 fnd_message.set_token('NEW', p_new_value);
805 l_note_msg := fnd_message.get;
806
807 IF x_msg IS NOT NULL THEN
808 x_msg := x_msg || fnd_global.local_chr(10);
809 END IF;
810 x_msg := x_msg || l_note_msg;
811 END get_note;
812
813 PROCEDURE raise_note
814 (p_srp_pmt_plan_id IN NUMBER,
815 p_msg IN VARCHAR2) IS
816
817 x_note_id NUMBER;
818 x_msg_count NUMBER;
819 x_msg_data VARCHAR2(240);
820 x_return_status VARCHAR2(1);
821
822 BEGIN
823 jtf_notes_pub.create_note
824 ( p_api_version => 1.0,
825 x_return_status => x_return_status,
826 x_msg_count => x_msg_count,
827 x_msg_data => x_msg_data,
828 p_source_object_id => p_srp_pmt_plan_id,
829 p_source_object_code => 'CN_SRP_PMT_PLANS',
830 p_notes => p_msg,
831 p_notes_detail => p_msg,
832 p_note_type => 'CN_SYSGEN', -- for system generated
833 x_jtf_note_id => x_note_id -- returned
834 );
835 END raise_note;
836
837 PROCEDURE business_event
838 (p_operation IN VARCHAR2,
839 p_pmt_plan_assign_rec IN pmt_plan_assign_rec) IS
840
841 l_key VARCHAR2(80);
842 l_event_name VARCHAR2(80);
843 l_list wf_parameter_list_t;
844 BEGIN
845 -- p_operation = Add, Update, Remove
846 l_event_name := 'oracle.apps.cn.resource.PaymentPlanAssign.' || p_operation;
847
848 --Get the item key
849 -- for create - event_name || srp_pmt_plan_id
850 -- for update - event_name || srp_pmt_plan_id || ovn
851 -- for delete - event_name || srp_pmt_plan_id
852 l_key := l_event_name || '-' || p_pmt_plan_assign_rec.srp_pmt_plan_id;
853
854 -- build parameter list as appropriate
855 IF (p_operation = 'Add') THEN
856 wf_event.AddParameterToList('SALESREP_ID',p_pmt_plan_assign_rec.salesrep_id,l_list);
857 wf_event.AddParameterToList('PMT_PLAN_ID',p_pmt_plan_assign_rec.pmt_plan_id,l_list);
858 wf_event.AddParameterToList('START_DATE',p_pmt_plan_assign_rec.start_date,l_list);
859 wf_event.AddParameterToList('END_DATE',p_pmt_plan_assign_rec.end_date,l_list);
860 wf_event.AddParameterToList('MINIMUM_AMOUNT',p_pmt_plan_assign_rec.minimum_amount,l_list);
861 wf_event.AddParameterToList('MAXIMUM_AMOUNT',p_pmt_plan_assign_rec.maximum_amount,l_list);
862 wf_event.AddParameterToList('LOCK_FLAG',p_pmt_plan_assign_rec.lock_flag,l_list);
863 ELSIF (p_operation = 'Update') THEN
864 l_key := l_key || '-' || p_pmt_plan_assign_rec.object_version_number;
865 wf_event.AddParameterToList('SRP_PMT_PLAN_ID',p_pmt_plan_assign_rec.srp_pmt_plan_id,l_list);
866 wf_event.AddParameterToList('SALESREP_ID',p_pmt_plan_assign_rec.salesrep_id,l_list);
867 wf_event.AddParameterToList('PMT_PLAN_ID',p_pmt_plan_assign_rec.pmt_plan_id,l_list);
868 wf_event.AddParameterToList('START_DATE',p_pmt_plan_assign_rec.start_date,l_list);
869 wf_event.AddParameterToList('END_DATE',p_pmt_plan_assign_rec.end_date,l_list);
870 wf_event.AddParameterToList('MINIMUM_AMOUNT',p_pmt_plan_assign_rec.minimum_amount,l_list);
871 wf_event.AddParameterToList('MAXIMUM_AMOUNT',p_pmt_plan_assign_rec.maximum_amount,l_list);
872 wf_event.AddParameterToList('LOCK_FLAG',p_pmt_plan_assign_rec.lock_flag,l_list);
873 ELSIF (p_operation = 'Remove') THEN
874 wf_event.AddParameterToList('SRP_PMT_PLAN_ID',p_pmt_plan_assign_rec.srp_pmt_plan_id,l_list);
875 END IF;
876
877 -- Raise Event
878 wf_event.raise
879 (p_event_name => l_event_name,
880 p_event_key => l_key,
881 p_parameters => l_list);
882
883 l_list.DELETE;
884 END business_event;
885
886
887 -- --------------------------------------------------------------------------*
888 -- Procedure: Create_Srp_Pmt_Plan
889 -- --------------------------------------------------------------------------*
890 PROCEDURE Create_Srp_Pmt_Plan
891 ( p_api_version IN NUMBER ,
892 p_init_msg_list IN VARCHAR2,
893 p_commit IN VARCHAR2,
894 p_validation_level IN NUMBER,
895 x_return_status OUT NOCOPY VARCHAR2 ,
896 x_loading_status OUT NOCOPY VARCHAR2 ,
897 x_msg_count OUT NOCOPY NUMBER ,
898 x_msg_data OUT NOCOPY VARCHAR2 ,
899 p_pmt_plan_assign_rec IN OUT NOCOPY pmt_plan_assign_rec) IS
900
901 l_api_name CONSTANT VARCHAR2(30) := 'Create_Srp_Pmt_Plan';
902 l_api_version CONSTANT NUMBER := 1.0;
903 l_credit_type_id NUMBER;
904 l_name cn_pmt_plans.name%TYPE;
905 l_role_name cn_roles.name%TYPE;
906 l_loading_status VARCHAR2(2000);
907 x_note_id NUMBER;
908 l_note_msg VARCHAR2(240);
909
910 CURSOR get_role_name IS
911 select r.name
912 from cn_roles r, cn_role_pmt_plans_all rpp
913 where r.role_id = rpp.role_id
914 and rpp.role_pmt_plan_id = p_pmt_plan_assign_rec.role_pmt_plan_id;
915
916 BEGIN
917 -- Standard Start of API savepoint
918
919 SAVEPOINT Create_Srp_Pmt_Plan;
920
921 -- Standard call to check for call compatibility.
922
923 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
924 p_api_version ,
925 l_api_name ,
926 G_PKG_NAME )
927 THEN
928 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
929 END IF;
930
931 -- Initialize message list if p_init_msg_list is set to TRUE.
932 IF FND_API.to_Boolean( p_init_msg_list ) THEN
933 FND_MSG_PUB.initialize;
934 END IF;
935
936 -- Initialize API return status to success
937 x_return_status := FND_API.G_RET_STS_SUCCESS;
938 x_loading_status := 'CN_CREATED';
939
940 validate_assignment
941 (x_return_status => x_return_status,
942 x_msg_count => x_msg_count,
943 x_msg_data => x_msg_data,
944 p_salesrep_id => p_pmt_plan_assign_rec.salesrep_id,
945 p_org_id => p_pmt_plan_assign_rec.org_id,
946 p_start_date => p_pmt_plan_assign_rec.start_date,
947 p_end_date => p_pmt_plan_assign_rec.end_date,
948 p_minimum_amount => p_pmt_plan_assign_rec.minimum_amount,
949 p_maximum_amount => p_pmt_plan_assign_rec.maximum_amount,
950 p_pmt_plan_id => p_pmt_plan_assign_rec.pmt_plan_id,
951 p_srp_pmt_plan_id => NULL,
952 p_loading_status => x_loading_status,
953 x_loading_status => x_loading_status);
954
955 IF x_return_status <> fnd_api.g_ret_sts_success THEN
956 RAISE fnd_api.g_exc_error;
957 END IF;
958
959 -- inherit credit type of pmt plan
960 select credit_type_id, name into l_credit_type_id, l_name
961 from cn_pmt_plans_all
962 where pmt_plan_id = p_pmt_plan_assign_rec.pmt_plan_id;
963
964
965 cn_srp_pmt_plans_pkg.insert_row
966 ( x_srp_pmt_plan_id => p_pmt_plan_assign_rec.srp_pmt_plan_id
967 ,x_pmt_plan_id => p_pmt_plan_assign_rec.pmt_plan_id
968 ,x_salesrep_id => p_pmt_plan_assign_rec.salesrep_id
969 ,x_org_id => p_pmt_plan_assign_rec.org_id
970 ,x_role_id => NULL
971 ,x_credit_type_id => l_credit_type_id -- obsolete
972 ,x_start_date => p_pmt_plan_assign_rec.start_date
973 ,x_end_date => p_pmt_plan_assign_rec.end_date
974 ,x_minimum_amount => p_pmt_plan_assign_rec.minimum_amount
975 ,x_maximum_amount => p_pmt_plan_assign_rec.maximum_amount
976 ,x_max_recovery_amount => NULL -- obsolete
977 ,x_last_update_date => sysdate
978 ,x_last_updated_by => fnd_global.user_id
979 ,x_creation_date => sysdate
980 ,x_created_by => fnd_global.user_id
981 ,x_last_update_login => fnd_global.login_id
982 ,x_srp_role_id => p_pmt_plan_assign_rec.srp_role_id
983 ,x_role_pmt_plan_id => p_pmt_plan_assign_rec.role_pmt_plan_id
984 ,x_lock_flag => p_pmt_plan_assign_rec.lock_flag
985 );
986
987 -- raise business event
988 business_event
989 (p_operation => 'Add',
990 p_pmt_plan_assign_rec => p_pmt_plan_assign_rec);
991
992 -- create note
993 OPEN get_role_name;
994 FETCH get_role_name INTO l_role_name;
995 CLOSE get_role_name;
996
997 l_note_msg := fnd_message.get_string('CN', 'CN_SPP_CRE_NOTE');
998 l_note_msg := l_note_msg || l_name || ', ' || l_role_name || ', ' ||
999 p_pmt_plan_assign_rec.start_date || ', ' ||
1000 p_pmt_plan_assign_rec.end_date || ', ' ||
1001 p_pmt_plan_assign_rec.minimum_amount || ', ' ||
1002 p_pmt_plan_assign_rec.maximum_amount || ', ' ||
1003 p_pmt_plan_assign_rec.lock_flag;
1004
1005 jtf_notes_pub.create_note
1006 ( p_api_version => 1.0,
1007 x_return_status => x_return_status,
1008 x_msg_count => x_msg_count,
1009 x_msg_data => x_msg_data,
1010 p_source_object_id => p_pmt_plan_assign_rec.salesrep_id,
1011 p_source_object_code => 'CN_SALESREPS',
1012 p_notes => l_note_msg,
1013 p_notes_detail => l_note_msg,
1014 p_note_type => 'CN_SYSGEN', -- for system generated
1015 x_jtf_note_id => x_note_id -- returned
1016 );
1017
1018 -- populate ovn
1019 SELECT object_version_number
1020 INTO p_pmt_plan_assign_rec.object_version_number
1021 FROM cn_srp_pmt_plans_all
1022 WHERE srp_pmt_plan_id = p_pmt_plan_assign_rec.srp_pmt_plan_id;
1023
1024 -- End of API body
1025
1026 -- Standard check of p_commit.
1027
1028 IF FND_API.To_Boolean( p_commit ) THEN
1029 COMMIT WORK;
1030 END IF;
1031
1032 -- Standard call to get message count and if count is 1, get message info
1033 FND_MSG_PUB.Count_And_Get
1034 (
1035 p_count => x_msg_count ,
1036 p_data => x_msg_data ,
1037 p_encoded => FND_API.G_FALSE
1038 );
1039
1040 EXCEPTION
1041 WHEN FND_API.G_EXC_ERROR THEN
1042 ROLLBACK TO Create_Srp_Pmt_Plan;
1043 x_return_status := FND_API.G_RET_STS_ERROR ;
1044 FND_MSG_PUB.Count_And_Get
1045 (
1046 p_count => x_msg_count ,
1047 p_data => x_msg_data ,
1048 p_encoded => FND_API.G_FALSE
1049 );
1050 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1051 ROLLBACK TO Create_Srp_Pmt_Plan;
1052 x_loading_status := 'UNEXPECTED_ERR';
1053 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1054 FND_MSG_PUB.Count_And_Get
1055 (
1056 p_count => x_msg_count ,
1057 p_data => x_msg_data ,
1058 p_encoded => FND_API.G_FALSE
1059 );
1060 WHEN OTHERS THEN
1061 ROLLBACK TO Create_Srp_Pmt_Plan;
1062 x_loading_status := 'UNEXPECTED_ERR';
1063 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1064 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1065 THEN
1066 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME ,l_api_name );
1067 END IF;
1068 FND_MSG_PUB.Count_And_Get
1069 (
1070 p_count => x_msg_count ,
1071 p_data => x_msg_data ,
1072 p_encoded => FND_API.G_FALSE
1073 );
1074 END create_srp_pmt_plan;
1075
1076
1077
1078 -- --------------------------------------------------------------------------*
1079 -- Procedure: Update_Srp_Pmt_Plan
1080 -- --------------------------------------------------------------------------*
1081 PROCEDURE Update_Srp_Pmt_Plan
1082 ( p_api_version IN NUMBER ,
1083 p_init_msg_list IN VARCHAR2,
1084 p_commit IN VARCHAR2,
1085 p_validation_level IN NUMBER,
1086 x_return_status OUT NOCOPY VARCHAR2 ,
1087 x_loading_status OUT NOCOPY VARCHAR2 ,
1088 x_msg_count OUT NOCOPY NUMBER ,
1089 x_msg_data OUT NOCOPY VARCHAR2 ,
1090 p_pmt_plan_assign_rec IN OUT NOCOPY pmt_plan_assign_rec ) IS
1091
1092 l_api_name CONSTANT VARCHAR2(30) := 'Update_Srp_Pmt_Plan';
1093 l_api_version CONSTANT NUMBER := 1.0;
1094 l_credit_type_id NUMBER;
1095
1096 CURSOR spp_csr( l_srp_pmt_plan_id NUMBER ) IS
1097 SELECT *
1098 FROM cn_srp_pmt_plans
1099 WHERE srp_pmt_plan_id = l_srp_pmt_plan_id;
1100
1101 l_oldrec spp_csr%ROWTYPE;
1102 l_oldname cn_pmt_plans.name%TYPE;
1103 l_newname cn_pmt_plans.name%TYPE;
1104 l_notemsg VARCHAR2(2000);
1105
1106
1107 BEGIN
1108 -- Standard Start of API savepoint
1109
1110 SAVEPOINT Update_Srp_Pmt_Plan;
1111
1112 -- Standard call to check for call compatibility.
1113
1114 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
1115 p_api_version ,
1116 l_api_name ,
1117 G_PKG_NAME )
1118 THEN
1119 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1120 END IF;
1121
1122 -- Initialize message list if p_init_msg_list is set to TRUE.
1123 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1124 FND_MSG_PUB.initialize;
1125 END IF;
1126
1127 -- Initialize API return status to success
1128 x_return_status := FND_API.G_RET_STS_SUCCESS;
1129 x_loading_status := 'CN_UPDATED';
1130
1131 -- check if the object version number is the same
1132 OPEN spp_csr(p_pmt_plan_assign_rec.srp_pmt_plan_id) ;
1133 FETCH spp_csr INTO l_oldrec;
1134 CLOSE spp_csr;
1135
1136 IF (l_oldrec.object_version_number <>
1137 p_pmt_plan_assign_rec.object_version_number) THEN
1138
1139 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
1140 THEN
1141 fnd_message.set_name('CN', 'CN_INVALID_OBJECT_VERSION');
1142 fnd_msg_pub.add;
1143 END IF;
1144
1145 x_loading_status := 'CN_INVALID_OBJECT_VERSION';
1146 RAISE FND_API.G_EXC_ERROR;
1147
1148 END IF;
1149
1150 -- can't change lock flag from Y to N
1151 IF l_oldrec.lock_flag = 'Y' AND p_pmt_plan_assign_rec.lock_flag = 'N' THEN
1152 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
1153 THEN
1154 fnd_message.set_name('CN', 'CN_CANNOT_UPDATE_LOCK');
1155 fnd_msg_pub.add;
1156 END IF;
1157 x_loading_status := 'CN_CANNOT_UPDATE_LOCK';
1158 RAISE FND_API.G_EXC_ERROR;
1159 END IF;
1160
1161 -- can't change lock from N to Y if it is manual assignment
1162 IF l_oldrec.lock_flag = 'N' AND p_pmt_plan_assign_rec.lock_flag = 'Y' AND
1163 p_pmt_plan_assign_rec.role_pmt_plan_id IS NULL THEN
1164 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
1165 THEN
1166 fnd_message.set_name('CN', 'CN_CANNOT_UPDATE_LOCK');
1167 fnd_msg_pub.add;
1168 END IF;
1169 x_loading_status := 'CN_CANNOT_UPDATE_LOCK';
1170 RAISE FND_API.G_EXC_ERROR;
1171 END IF;
1172
1173 -- validate the assignment
1174 validate_assignment
1175 (x_return_status => x_return_status,
1176 x_msg_count => x_msg_count,
1177 x_msg_data => x_msg_data,
1178 p_salesrep_id => p_pmt_plan_assign_rec.salesrep_id,
1179 p_org_id => p_pmt_plan_assign_rec.org_id,
1180 p_start_date => p_pmt_plan_assign_rec.start_date,
1181 p_end_date => p_pmt_plan_assign_rec.end_date,
1182 p_minimum_amount => p_pmt_plan_assign_rec.minimum_amount,
1183 p_maximum_amount => p_pmt_plan_assign_rec.maximum_amount,
1184 p_pmt_plan_id => p_pmt_plan_assign_rec.pmt_plan_id,
1185 p_srp_pmt_plan_id => p_pmt_plan_assign_rec.srp_pmt_plan_id,
1186 p_loading_status => x_loading_status,
1187 x_loading_status => x_loading_status);
1188
1189 -- inherit credit type of pmt plan
1190 select credit_type_id into l_credit_type_id
1191 from cn_pmt_plans_all
1192 where pmt_plan_id = p_pmt_plan_assign_rec.pmt_plan_id;
1193
1194 -- if the lock_flag is being set, then blow away role_pmt_plan_id
1195 IF p_pmt_plan_assign_rec.lock_flag = 'Y' THEN
1196 p_pmt_plan_assign_rec.role_pmt_plan_id := NULL;
1197 p_pmt_plan_assign_rec.srp_role_id := NULL;
1198 END IF;
1199
1200 -- Check if update is allowed
1201 IF l_oldrec.salesrep_id <> p_pmt_plan_assign_rec.salesrep_id OR
1202 l_oldrec.pmt_plan_id <> p_pmt_plan_assign_rec.pmt_plan_id THEN
1203 -- user try to change the assignment
1204 -- need to delete the old assginment then create the new assignment
1205 --
1206 -- Check if delete operation allowed
1207 --
1208 /* check_operation_allowed
1209 ( x_return_status => x_return_status,
1210 x_msg_count => x_msg_count,
1211 x_msg_data => x_msg_data,
1212 p_salesrep_id => l_oldrec.salesrep_id,
1213 p_start_date => l_oldrec.start_date,
1214 p_end_date => l_oldrec.end_date,
1215 p_loading_status => x_loading_status,
1216 x_loading_status => x_loading_status
1217 );
1218 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
1219 RAISE FND_API.G_EXC_ERROR ;
1220 END IF;
1221 */
1222 -- Delete record
1223 cn_srp_pmt_plans_pkg.delete_row
1224 (x_srp_pmt_plan_id => l_oldrec.srp_pmt_plan_id);
1225
1226 -- Insert new record w/ validation
1227 cn_srp_pmt_plans_pkg.insert_row
1228 ( x_srp_pmt_plan_id => p_pmt_plan_assign_rec.srp_pmt_plan_id
1229 ,x_pmt_plan_id => p_pmt_plan_assign_rec.pmt_plan_id
1230 ,x_salesrep_id => p_pmt_plan_assign_rec.salesrep_id
1231 ,x_org_id => p_pmt_plan_assign_rec.org_id
1232 ,x_role_id => NULL
1233 ,x_credit_type_id => l_credit_type_id -- obsolete
1234 ,x_start_date => p_pmt_plan_assign_rec.start_date
1235 ,x_end_date => p_pmt_plan_assign_rec.end_date
1236 ,x_minimum_amount => p_pmt_plan_assign_rec.minimum_amount
1237 ,x_maximum_amount => p_pmt_plan_assign_rec.maximum_amount
1238 ,x_max_recovery_amount => NULL -- obsolete
1239 ,x_last_update_date => sysdate
1240 ,x_last_updated_by => fnd_global.user_id
1241 ,x_creation_date => sysdate
1242 ,x_created_by => fnd_global.user_id
1243 ,x_last_update_login => fnd_global.login_id
1244 ,x_srp_role_id => p_pmt_plan_assign_rec.srp_role_id
1245 ,x_role_pmt_plan_id => p_pmt_plan_assign_rec.role_pmt_plan_id
1246 ,x_lock_flag => p_pmt_plan_assign_rec.lock_flag);
1247
1248 -- sync ID back
1249 update cn_srp_pmt_plans_all
1250 set srp_pmt_plan_id = l_oldrec.srp_pmt_plan_id
1251 where srp_pmt_plan_id = p_pmt_plan_assign_rec.srp_pmt_plan_id;
1252
1253 p_pmt_plan_assign_rec.srp_pmt_plan_id := l_oldrec.srp_pmt_plan_id;
1254
1255 -- Added the Min and Max or condition
1256 -- Kumar.
1257 ELSE
1258 /* -- this check has already been performed
1259 -- just do update instead of delete/create
1260 IF l_oldrec.start_date <> p_pmt_plan_assign_rec.start_date OR
1261 l_oldrec.end_date <> p_pmt_plan_assign_rec.end_date
1262 THEN
1263 -- Check if update operation allowed
1264 -- try to update start date, end date, need to check if the old_rec
1265 -- already been used in worksheet during those delete dates,if so,
1266 -- cannot change the date range
1267
1268 -- Added more parameters
1269 check_operation_allowed
1270 ( x_return_status => x_return_status,
1271 x_msg_count => x_msg_count,
1272 x_msg_data => x_msg_data,
1273 p_salesrep_id => l_oldrec.salesrep_id,
1274 p_old_start_date => l_oldrec.start_date,
1275 p_old_end_date => l_oldrec.end_date,
1276 p_start_date => p_pmt_plan_assign_rec.start_date,
1277 p_end_date => p_pmt_plan_assign_rec.end_date,
1278 p_loading_status => x_loading_status,
1279 x_loading_status => x_loading_status
1280 );
1281
1282 -- Check opeation fail
1283 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
1284 RAISE FND_API.G_EXC_ERROR ;
1285 END IF;
1286
1287 END IF;
1288 */
1289 -- Update pmt plan assignment into cn_srp_pmt_plans
1290 cn_srp_pmt_plans_pkg.update_row
1291 ( x_srp_pmt_plan_id => p_pmt_plan_assign_rec.srp_pmt_plan_id
1292 ,x_pmt_plan_id => p_pmt_plan_assign_rec.pmt_plan_id
1293 ,x_salesrep_id => p_pmt_plan_assign_rec.salesrep_id
1294 ,x_org_id => p_pmt_plan_assign_rec.org_id
1295 ,x_role_id => NULL
1296 ,x_credit_type_id => l_credit_type_id -- Obsolete
1297 ,x_start_date => p_pmt_plan_assign_rec.start_date
1298 ,x_end_date => p_pmt_plan_assign_rec.end_date
1299 ,x_minimum_amount => p_pmt_plan_assign_rec.minimum_amount
1300 ,x_maximum_amount => p_pmt_plan_assign_rec.maximum_amount
1301 ,x_max_recovery_amount => NULL -- Obsolete
1302 ,x_last_update_date => sysdate
1303 ,x_last_updated_by => fnd_global.user_id
1304 ,x_last_update_login => fnd_global.login_id
1305 ,x_object_version_number => p_pmt_plan_assign_rec.object_version_number
1306 ,x_lock_flag => p_pmt_plan_assign_rec.lock_flag
1307 );
1308
1309 -- if the lock_flag is being set, then blow away role_pmt_plan_id
1310 IF p_pmt_plan_assign_rec.lock_flag = 'Y' THEN
1311 UPDATE cn_srp_pmt_plans_all
1312 SET role_pmt_plan_id = NULL,
1313 srp_role_id = NULL
1314 WHERE srp_pmt_plan_id = p_pmt_plan_assign_rec.srp_pmt_plan_id;
1315 END IF;
1316
1317 END IF;
1318
1319 -- raise business event
1320 business_event
1321 (p_operation => 'Update',
1322 p_pmt_plan_assign_rec => p_pmt_plan_assign_rec);
1323
1324
1325 -- build notes
1326 l_notemsg := NULL;
1327
1328 -- raise notes
1329 IF l_oldrec.pmt_plan_id <> p_pmt_plan_assign_rec.pmt_plan_id THEN
1330 SELECT name INTO l_oldname FROM cn_pmt_plans_all
1331 WHERE pmt_plan_id = l_oldrec.pmt_plan_id;
1332 SELECT name INTO l_newname FROM cn_pmt_plans_all
1333 WHERE pmt_plan_id = p_pmt_plan_assign_rec.pmt_plan_id;
1334 get_note('PMT_PLAN', l_oldname, l_newname, l_notemsg);
1335 END IF;
1336
1337 IF l_oldrec.start_date <> p_pmt_plan_assign_rec.start_date THEN
1338 get_note('START_DATE', l_oldrec.start_date, p_pmt_plan_assign_rec.start_date, l_notemsg);
1339 END IF;
1340
1341 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
1342 get_note('END_DATE', l_oldrec.end_date, p_pmt_plan_assign_rec.end_date, l_notemsg);
1343 END IF;
1344
1345 IF Nvl(l_oldrec.minimum_amount, -1) <> Nvl(p_pmt_plan_assign_rec.minimum_amount, -1) THEN
1346 get_note('MIN_AMT', l_oldrec.minimum_amount, p_pmt_plan_assign_rec.minimum_amount, l_notemsg);
1347 END IF;
1348
1349 IF Nvl(l_oldrec.maximum_amount, -1) <> Nvl(p_pmt_plan_assign_rec.maximum_amount, -1) THEN
1350 get_note('MAX_AMT', l_oldrec.maximum_amount, p_pmt_plan_assign_rec.maximum_amount, l_notemsg);
1351 END IF;
1352
1353 IF l_oldrec.lock_flag <> p_pmt_plan_assign_rec.lock_flag THEN
1354 get_note('LOCK_FLAG', l_oldrec.lock_flag, p_pmt_plan_assign_rec.lock_flag, l_notemsg);
1355 END IF;
1356
1357 IF (l_notemsg IS NOT NULL) THEN
1358 raise_note(p_pmt_plan_assign_rec.srp_pmt_plan_id, l_notemsg);
1359 END IF;
1360
1361 -- pick up new object version number
1362 SELECT object_version_number
1363 INTO p_pmt_plan_assign_rec.object_version_number
1364 FROM cn_srp_pmt_plans_all
1365 WHERE srp_pmt_plan_id = p_pmt_plan_assign_rec.srp_pmt_plan_id;
1366 -- End of API body.
1367
1368 -- Standard check of p_commit.
1369
1370 IF FND_API.To_Boolean( p_commit ) THEN
1371 COMMIT WORK;
1372 END IF;
1373
1374 -- Standard call to get message count and if count is 1, get message info
1375 FND_MSG_PUB.Count_And_Get
1376 (
1377 p_count => x_msg_count ,
1378 p_data => x_msg_data ,
1379 p_encoded => FND_API.G_FALSE
1380 );
1381
1382 EXCEPTION
1383 WHEN FND_API.G_EXC_ERROR THEN
1384 ROLLBACK TO Update_Srp_Pmt_Plan;
1385 x_return_status := FND_API.G_RET_STS_ERROR ;
1386 FND_MSG_PUB.Count_And_Get
1387 (
1388 p_count => x_msg_count ,
1389 p_data => x_msg_data ,
1390 p_encoded => FND_API.G_FALSE
1391 );
1392 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1393 ROLLBACK TO Update_Srp_Pmt_Plan;
1394 x_loading_status := 'UNEXPECTED_ERR';
1395 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1396 FND_MSG_PUB.Count_And_Get
1397 (
1398 p_count => x_msg_count ,
1399 p_data => x_msg_data ,
1400 p_encoded => FND_API.G_FALSE
1401 );
1402 WHEN OTHERS THEN
1403 ROLLBACK TO Update_Srp_Pmt_Plan;
1404 x_loading_status := 'UNEXPECTED_ERR';
1405 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1406 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1407 THEN
1408 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME ,l_api_name );
1409 END IF;
1410 FND_MSG_PUB.Count_And_Get
1411 (
1412 p_count => x_msg_count ,
1413 p_data => x_msg_data ,
1414 p_encoded => FND_API.G_FALSE
1415 );
1416
1417 END update_srp_pmt_plan;
1418
1419 -- --------------------------------------------------------------------------*
1420 -- Procedure: Valid_Delete_Srp_Pmt_Plan
1421 -- --------------------------------------------------------------------------*
1422 PROCEDURE valid_delete_srp_pmt_plan
1423 ( p_srp_pmt_plan_id IN NUMBER,
1424 p_init_msg_list IN VARCHAR2,
1425 x_loading_status OUT NOCOPY VARCHAR2 ,
1426 x_return_status OUT NOCOPY VARCHAR2 ,
1427 x_msg_count OUT NOCOPY NUMBER ,
1428 x_msg_data OUT NOCOPY VARCHAR2
1429 ) IS
1430
1431 l_api_name CONSTANT VARCHAR2(30) := 'Valid_Delete_Srp_Pmt_Plan';
1432
1433 CURSOR spp_csr( l_srp_pmt_plan_id NUMBER ) IS
1434 SELECT *
1435 FROM cn_srp_pmt_plans
1436 WHERE srp_pmt_plan_id = l_srp_pmt_plan_id;
1437
1438 l_spp_rec spp_csr%ROWTYPE;
1439
1440 BEGIN
1441 -- Initialize message list if p_init_msg_list is set to TRUE.
1442 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1443 FND_MSG_PUB.initialize;
1444 END IF;
1445
1446 -- Initialize API return status to success
1447 x_return_status := FND_API.G_RET_STS_SUCCESS;
1448 x_loading_status := 'CN_DELETED';
1449
1450
1451 --
1452 -- Check if delete operation allowed
1453 --
1454 OPEN spp_csr(p_srp_pmt_plan_id);
1455 FETCH spp_csr INTO l_spp_rec;
1456 CLOSE spp_csr;
1457 /*
1458 IF (NVL(l_spp_rec.lock_flag, 'N') = 'Y') THEN
1459 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1460 FND_MESSAGE.Set_Name('CN', 'CN_SRP_PMT_PLAN_LOCKED');
1461 FND_MSG_PUB.Add;
1462 END IF;
1463 x_loading_status := 'CN_SRP_PMT_PLAN_LOCKED';
1464 RAISE FND_API.G_EXC_ERROR ;
1465 END IF;
1466
1467 check_operation_allowed
1468 ( x_return_status => x_return_status,
1469 x_msg_count => x_msg_count,
1470 x_msg_data => x_msg_data,
1471 p_salesrep_id => l_spp_rec.salesrep_id,
1472 p_start_date => l_spp_rec.start_date,
1473 p_end_date => l_spp_rec.end_date,
1474 p_loading_status => x_loading_status,
1475 x_loading_status => x_loading_status
1476 );
1477 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
1478 RAISE FND_API.G_EXC_ERROR ;
1479 END IF;
1480 */
1481 EXCEPTION
1482 WHEN FND_API.G_EXC_ERROR THEN
1483 x_return_status := FND_API.G_RET_STS_ERROR ;
1484 FND_MSG_PUB.Count_And_Get
1485
1486 (
1487 p_count => x_msg_count ,
1488 p_data => x_msg_data ,
1489 p_encoded => FND_API.G_FALSE
1490 );
1491 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1492 x_loading_status := 'UNEXPECTED_ERR';
1493 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1494 FND_MSG_PUB.Count_And_Get
1495 (
1496 p_count => x_msg_count ,
1497 p_data => x_msg_data ,
1498 p_encoded => FND_API.G_FALSE
1499 );
1500 WHEN OTHERS THEN
1501 x_loading_status := 'UNEXPECTED_ERR';
1502 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1503 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1504 THEN
1505 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME ,l_api_name );
1506 END IF;
1507 FND_MSG_PUB.Count_And_Get
1508 (
1509 p_count => x_msg_count ,
1510 p_data => x_msg_data ,
1511 p_encoded => FND_API.G_FALSE
1512 );
1513
1514
1515 END valid_delete_srp_pmt_plan;
1516
1517 -- --------------------------------------------------------------------------*
1518 -- Procedure: Delete_Srp_Pmt_Plan
1519 -- --------------------------------------------------------------------------*
1520 PROCEDURE Delete_Srp_Pmt_Plan
1521 ( p_api_version IN NUMBER ,
1522 p_init_msg_list IN VARCHAR2,
1523 p_commit IN VARCHAR2,
1524 p_validation_level IN NUMBER,
1525 x_return_status OUT NOCOPY VARCHAR2 ,
1526 x_loading_status OUT NOCOPY VARCHAR2 ,
1527 x_msg_count OUT NOCOPY NUMBER ,
1528 x_msg_data OUT NOCOPY VARCHAR2 ,
1529 p_srp_pmt_plan_id IN NUMBER) IS
1530
1531 l_api_name CONSTANT VARCHAR2(30) := 'Delete_Srp_Pmt_Plan';
1532 l_api_version CONSTANT NUMBER := 1.0;
1533 x_note_id NUMBER;
1534 l_note_msg VARCHAR2(240);
1535 l_pmt_plan_assign_rec pmt_plan_assign_rec;
1536
1537 CURSOR spp_info_cur IS
1538 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
1539 from cn_srp_pmt_plans_all spp, cn_pmt_plans_all p, cn_role_pmt_plans_all rpp, cn_roles r
1540 where spp.srp_pmt_plan_id = p_srp_pmt_plan_id
1541 and spp.role_pmt_plan_id = rpp.role_pmt_plan_id(+)
1542 and spp.pmt_plan_id = p.pmt_plan_id
1543 and rpp.role_id = r.role_id(+);
1544
1545 spp_info spp_info_cur%ROWTYPE;
1546
1547 BEGIN
1548 -- Standard Start of API savepoint
1549 SAVEPOINT delete_srp_pmt_plan;
1550
1551 -- Standard call to check for call compatibility.
1552
1553 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
1554 p_api_version ,
1555 l_api_name ,
1556 G_PKG_NAME )
1557 THEN
1558 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1559 END IF;
1560
1561 -- Initialize message list if p_init_msg_list is set to TRUE.
1562 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1563 FND_MSG_PUB.initialize;
1564 END IF;
1565
1566 -- Initialize API return status to success
1567 x_return_status := FND_API.G_RET_STS_SUCCESS;
1568 x_loading_status := 'CN_DELETED';
1569
1570 -- validate delete
1571 valid_delete_srp_pmt_plan
1572 ( p_srp_pmt_plan_id => p_srp_pmt_plan_id,
1573 p_init_msg_list => p_init_msg_list,
1574 x_loading_status => x_loading_status,
1575 x_return_status => x_return_status,
1576 x_msg_count => x_msg_count,
1577 x_msg_data => x_msg_data);
1578
1579 IF x_return_status <> fnd_api.g_ret_sts_success THEN
1580 RAISE fnd_api.g_exc_error;
1581 END IF;
1582
1583 -- create note
1584 OPEN spp_info_cur;
1585 FETCH spp_info_cur INTO spp_info;
1586 CLOSE spp_info_cur;
1587
1588 l_note_msg := fnd_message.get_string('CN', 'CN_SPP_DEL_NOTE');
1589 l_note_msg := l_note_msg || spp_info.name || ', ' ||
1590 spp_info.role_name || ', ' ||
1591 spp_info.start_date || ', ' ||
1592 spp_info.end_date || ', ' ||
1593 spp_info.minimum_amount || ', ' ||
1594 spp_info.maximum_amount || ', ' ||
1595 spp_info.lock_flag;
1596
1597 jtf_notes_pub.create_note
1598 ( p_api_version => 1.0,
1599 x_return_status => x_return_status,
1600 x_msg_count => x_msg_count,
1601 x_msg_data => x_msg_data,
1602 p_source_object_id => spp_info.salesrep_id,
1603 p_source_object_code => 'CN_SALESREPS',
1604 p_notes => l_note_msg,
1605 p_notes_detail => l_note_msg,
1606 p_note_type => 'CN_SYSGEN', -- for system generated
1607 x_jtf_note_id => x_note_id -- returned
1608 );
1609
1610 -- Delete record
1611 cn_srp_pmt_plans_pkg.delete_row
1612 (x_srp_pmt_plan_id => p_srp_pmt_plan_id);
1613
1614 -- raise business event
1615 l_pmt_plan_assign_rec.srp_pmt_plan_id := p_srp_pmt_plan_id;
1616 business_event
1617 (p_operation => 'Remove',
1618 p_pmt_plan_assign_rec => l_pmt_plan_assign_rec);
1619
1620 -- End of API body
1621
1622 -- Standard check of p_commit.
1623
1624 IF FND_API.To_Boolean( p_commit ) THEN
1625 COMMIT WORK;
1626 END IF;
1627
1628
1629 -- Standard call to get message count and if count is 1, get message info.
1630
1631 FND_MSG_PUB.Count_And_Get
1632 (
1633 p_count => x_msg_count ,
1634 p_data => x_msg_data ,
1635 p_encoded => FND_API.G_FALSE
1636 );
1637
1638 EXCEPTION
1639 WHEN FND_API.G_EXC_ERROR THEN
1640 ROLLBACK TO Delete_Srp_Pmt_Plan;
1641 x_return_status := FND_API.G_RET_STS_ERROR ;
1642 FND_MSG_PUB.Count_And_Get
1643 (
1644 p_count => x_msg_count ,
1645 p_data => x_msg_data ,
1646 p_encoded => FND_API.G_FALSE
1647 );
1648 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1649 ROLLBACK TO Delete_Srp_Pmt_Plan;
1650 x_loading_status := 'UNEXPECTED_ERR';
1651 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1652 FND_MSG_PUB.Count_And_Get
1653 (
1654 p_count => x_msg_count ,
1655 p_data => x_msg_data ,
1656 p_encoded => FND_API.G_FALSE
1657 );
1658 WHEN OTHERS THEN
1659 ROLLBACK TO Delete_Srp_Pmt_Plan;
1660 x_loading_status := 'UNEXPECTED_ERR';
1661 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1662 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1663 THEN
1664 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME ,l_api_name );
1665 END IF;
1666 FND_MSG_PUB.Count_And_Get
1667 (
1668 p_count => x_msg_count ,
1669 p_data => x_msg_data ,
1670 p_encoded => FND_API.G_FALSE
1671 );
1672 END Delete_Srp_Pmt_Plan;
1673
1674 -- --------------------------------------------------------------------------*
1675 -- Procedure: Create_Mass_Asgn_Srp_Pmt_plan
1676 -- --------------------------------------------------------------------------*
1677
1678 PROCEDURE Create_Mass_Asgn_Srp_Pmt_Plan
1679 (
1680 p_api_version IN NUMBER,
1681 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1682 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1683 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1684 x_return_status OUT NOCOPY VARCHAR2,
1685 x_msg_count OUT NOCOPY NUMBER,
1686 x_msg_data OUT NOCOPY VARCHAR2,
1687 p_srp_role_id IN NUMBER,
1688 p_role_pmt_plan_id IN NUMBER,
1689 x_srp_pmt_plan_id OUT NOCOPY NUMBER,
1690 x_loading_status OUT NOCOPY VARCHAR2
1691 ) IS
1692
1693 l_api_name CONSTANT VARCHAR2(30) := 'Create_Mass_Asgn_Srp_Pmt_Plan';
1694 l_api_version CONSTANT NUMBER := 1.0;
1695 l_return_status VARCHAR2(2000);
1696 l_msg_count NUMBER;
1697 l_msg_data VARCHAR2(2000);
1698 l_srp_pmt_plan_id cn_srp_pmt_plans.srp_pmt_plan_id%TYPE;
1699 l_loading_status VARCHAR2(2000);
1700
1701 newrec pmt_plan_assign_rec;
1702 l_salesrep_id cn_salesreps.salesrep_id%TYPE;
1703 l_pmt_plan_id cn_pmt_plans.pmt_plan_id%TYPE;
1704 l_org_id cn_pmt_plans.org_id%TYPE;
1705 l_min_amt cn_pmt_plans.minimum_amount%TYPE;
1706 l_max_amt cn_pmt_plans.maximum_amount%TYPE;
1707 l_pp_start_date cn_pmt_plans.start_date%TYPE;
1708 l_pp_end_date cn_pmt_plans.end_date%TYPE;
1709 l_srp_start_date cn_srp_roles.start_date%TYPE;
1710 l_srp_end_date cn_pmt_plans.end_date%TYPE;
1711 l_start_date cn_srp_pmt_plans.start_date%TYPE;
1712 l_end_date cn_srp_pmt_plans.start_date%TYPE;
1713
1714 BEGIN
1715
1716 -- Standard Start of API savepoint
1717
1718 SAVEPOINT Create_Mass_Asgn_Srp_Pmt_Plan;
1719
1720 -- Standard call to check for call compatibility.
1721
1722 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
1723 p_api_version ,
1724 l_api_name ,
1725 G_PKG_NAME )
1726 THEN
1727 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1728 END IF;
1729
1730 -- Initialize message list if p_init_msg_list is set to TRUE.
1731 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1732 FND_MSG_PUB.initialize;
1733 END IF;
1734
1735 -- Initialize API return status to success
1736 x_return_status := FND_API.G_RET_STS_SUCCESS;
1737 x_loading_status := 'CN_PP_CREATED';
1738
1739 select pmt_plan_id, start_date, end_date
1740 into l_pmt_plan_id, l_pp_start_date, l_pp_end_date
1741 from cn_role_pmt_plans
1742 where role_pmt_plan_id = p_role_pmt_plan_id;
1743
1744 select minimum_amount, maximum_amount, org_id
1745 into l_min_amt, l_max_amt, l_org_id
1746 from cn_pmt_plans
1747 where pmt_plan_id = l_pmt_plan_id;
1748
1749 select salesrep_id, start_date, end_date
1750 into l_salesrep_id, l_srp_start_date, l_srp_end_date
1751 from cn_srp_roles
1752 where srp_role_id = p_srp_role_id
1753 and org_id = l_org_id;
1754
1755 l_start_date := NULL;
1756 l_end_date := NULL;
1757
1758 x_return_status := FND_API.G_RET_STS_SUCCESS;
1759 -- Start: Bug fix 5480386 5480540 CHANTHON
1760 get_masgn_date_intersect(
1761 p_srp_role_id => p_srp_role_id,
1762 p_role_pmt_plan_id => p_role_pmt_plan_id,
1763 x_start_date => l_start_date,
1764 x_end_date => l_end_date);
1765
1766
1767 /* if cn_api.date_range_overlap(
1768 a_start_date => l_srp_start_date,
1769 a_end_date => l_srp_end_date,
1770 b_start_date => l_pp_start_date,
1771 b_end_date => l_pp_end_date
1772 ) THEN
1773
1774 cn_api.get_date_range_intersect(
1775 a_start_date => l_srp_start_date,
1776 a_end_date => l_srp_end_date,
1777 b_start_date => l_pp_start_date,
1778 b_end_date => l_pp_end_date,
1779 x_start_date => l_start_date,
1780 x_end_date => l_end_date); */
1781 -- End: Bug fix 5480386 5480540 CHANTHON
1782
1783 newrec.salesrep_id := l_salesrep_id;
1784 newrec.pmt_plan_id := l_pmt_plan_id;
1785 newrec.minimum_amount := l_min_amt;
1786 newrec.maximum_amount := l_max_amt;
1787 newrec.start_date := l_start_date;
1788 newrec.end_date := l_end_date;
1789 newrec.srp_role_id := p_srp_role_id;
1790 newrec.role_pmt_plan_id := p_role_pmt_plan_id;
1791 newrec.org_id := l_org_id;
1792 newrec.lock_flag := 'N';
1793
1794
1795 create_srp_pmt_plan
1796 (p_api_version => p_api_version,
1797 p_init_msg_list => p_init_msg_list,
1798 p_commit => p_commit,
1799 p_validation_level => p_validation_level,
1800 x_return_status => l_return_status,
1801 x_msg_count => l_msg_count,
1802 x_msg_data => l_msg_data,
1803 p_pmt_plan_assign_rec=> newrec,
1804 x_loading_status => l_loading_status);
1805
1806 /*
1807 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1808 RAISE fnd_api.g_exc_error;
1809 END IF;
1810 */
1811 l_return_status:=FND_API.G_RET_STS_SUCCESS;
1812 x_return_status := l_return_status;
1813 x_loading_status := l_loading_status;
1814
1815 -- END IF;
1816
1817 -- Standard check of p_commit.
1818
1819 IF FND_API.To_Boolean( p_commit ) THEN
1820 COMMIT WORK;
1821 END IF;
1822
1823 -- Standard call to get message count and if count is 1, get message info
1824 FND_MSG_PUB.Count_And_Get
1825 (
1826 p_count => x_msg_count ,
1827 p_data => x_msg_data ,
1828 p_encoded => FND_API.G_FALSE
1829 );
1830
1831 EXCEPTION
1832 WHEN FND_API.G_EXC_ERROR THEN
1833 ROLLBACK TO Create_Mass_Asgn_Srp_Pmt_Plan;
1834 x_return_status := FND_API.G_RET_STS_ERROR ;
1835 FND_MSG_PUB.Count_And_Get
1836 (
1837 p_count => x_msg_count ,
1838 p_data => x_msg_data ,
1839 p_encoded => FND_API.G_FALSE
1840 );
1841 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1842 ROLLBACK TO Create_Mass_Asgn_Srp_Pmt_Plan;
1843 x_loading_status := 'UNEXPECTED_ERR';
1844 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1845 FND_MSG_PUB.Count_And_Get
1846 (
1847 p_count => x_msg_count ,
1848 p_data => x_msg_data ,
1849 p_encoded => FND_API.G_FALSE
1850 );
1851 WHEN OTHERS THEN
1852 ROLLBACK TO Create_Mass_Asgn_Srp_Pmt_Plan;
1853 x_loading_status := 'UNEXPECTED_ERR';
1854 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1855 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1856 THEN
1857 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME ,l_api_name );
1858 END IF;
1859 FND_MSG_PUB.Count_And_Get
1860 (
1861 p_count => x_msg_count ,
1862 p_data => x_msg_data ,
1863 p_encoded => FND_API.G_FALSE
1864 );
1865
1866 END Create_Mass_Asgn_Srp_Pmt_Plan;
1867
1868 -- --------------------------------------------------------------------------*
1869 -- Procedure: Update_Mass_Asgn_Srp_Pmt_plan
1870 -- --------------------------------------------------------------------------*
1871
1872 PROCEDURE Update_Mass_Asgn_Srp_Pmt_plan
1873 (
1874 p_api_version IN NUMBER,
1875 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1876 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1877 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1878 x_return_status OUT NOCOPY VARCHAR2,
1879 x_msg_count OUT NOCOPY NUMBER,
1880 x_msg_data OUT NOCOPY VARCHAR2,
1881 p_srp_role_id IN NUMBER,
1882 p_role_pmt_plan_id IN NUMBER,
1883 x_loading_status OUT NOCOPY VARCHAR2
1884 ) IS
1885
1886 l_api_name CONSTANT VARCHAR2(30) := 'Update_Mass_Asgn_Srp_Pmt_Plan';
1887 l_api_version CONSTANT NUMBER := 1.0;
1888 l_return_status VARCHAR2(2000);
1889 l_msg_count NUMBER;
1890 l_msg_data VARCHAR2(2000);
1891 l_srp_pmt_plan_id cn_srp_pmt_plans.srp_pmt_plan_id%TYPE;
1892 l_loading_status VARCHAR2(2000);
1893 l_count NUMBER;
1894 l_count_srp_pmt_plan NUMBER;
1895
1896 newrec pmt_plan_assign_rec;
1897 l_salesrep_id_old cn_salesreps.salesrep_id%TYPE;
1898 l_salesrep_id_new cn_salesreps.salesrep_id%TYPE;
1899 l_pmt_plan_id_new cn_pmt_plans.pmt_plan_id%TYPE;
1900 l_org_id cn_pmt_plans.org_id%TYPE;
1901 l_min_amt_new cn_pmt_plans.minimum_amount%TYPE;
1902 l_max_amt_new cn_pmt_plans.maximum_amount%TYPE;
1903 l_pp_start_date_new cn_pmt_plans.start_date%TYPE;
1904 l_pp_end_date_new cn_pmt_plans.end_date%TYPE;
1905 l_srp_start_date_new cn_srp_roles.start_date%TYPE;
1906 --l_srp_end_date_new cn_pmt_plans.end_date%TYPE;
1907 l_srp_end_date_new cn_srp_roles.end_date%TYPE;
1908 l_start_date_old cn_srp_pmt_plans.start_date%TYPE;
1909 l_start_date_new cn_srp_pmt_plans.start_date%TYPE;
1910 l_end_date_old cn_srp_pmt_plans.start_date%TYPE;
1911 l_end_date_new cn_srp_pmt_plans.start_date%TYPE;
1912 l_role_pp_start_date cn_role_pmt_plans.start_date%TYPE;
1913 l_role_pp_end_date cn_role_pmt_plans.end_date%TYPE;
1914
1915 --Added payment group code for bug 3560026 by Julia Huang on 4/7/2004.
1916 l_pgc cn_pmt_plans.payment_group_code%TYPE;
1917 l_worksheets NUMBER;
1918 l_end_of_time CONSTANT DATE := To_date('31-12-9999', 'DD-MM-YYYY');
1919
1920 BEGIN
1921
1922 -- Standard Start of API savepoint
1923
1924 SAVEPOINT Update_Mass_Asgn_Srp_Pmt_plan;
1925
1926 -- Standard call to check for call compatibility.
1927
1928 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
1929 p_api_version ,
1930 l_api_name ,
1931 G_PKG_NAME )
1932 THEN
1933 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1934 END IF;
1935
1936 -- Initialize message list if p_init_msg_list is set to TRUE.
1937 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1938 FND_MSG_PUB.initialize;
1939 END IF;
1940
1941 -- Initialize API return status to success
1942 x_return_status := FND_API.G_RET_STS_SUCCESS;
1943 x_loading_status := 'CN_PP_UPDATED';
1944
1945 select org_id into l_org_id
1946 from cn_role_pmt_plans
1947 where role_pmt_plan_id = p_role_pmt_plan_id;
1948
1949 select salesrep_id
1950 into l_salesrep_id_old
1951 from cn_srp_roles
1952 where srp_role_id = p_srp_role_id
1953 and org_id = l_org_id;
1954
1955 --Added to check if the role_pay_group_id is existing in cn_srp_pmt_plans
1956 select count(*) into l_count from cn_srp_pmt_plans
1957 where salesrep_id = l_salesrep_id_old
1958 AND srp_role_id = p_srp_role_id
1959 AND role_pmt_plan_id = p_role_pmt_plan_id;
1960
1961 IF (l_count <> 0)
1962 THEN
1963 --Bug 3670276 by Julia Huang on 6/4/04 to avoid the following
1964 --1.Cartesian join. Line changed: AND spp.role_pmt_plan_id = crpp.role_pmt_plan_id --p_role_pmt_plan_id
1965 --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)
1966 select spp.start_date, spp.end_date, spp.salesrep_id,
1967 crpp.start_date, crpp.end_date, spp.srp_pmt_plan_id
1968 into l_start_date_old, l_end_date_old, l_salesrep_id_old,
1969 l_role_pp_start_date, l_role_pp_end_date, l_srp_pmt_plan_id
1970 from cn_srp_pmt_plans_all spp, cn_pmt_plans_all cpp, cn_role_pmt_plans_all crpp
1971 where spp.srp_role_id = p_srp_role_id
1972 AND spp.role_pmt_plan_id = crpp.role_pmt_plan_id --p_role_pmt_plan_id
1973 AND crpp.role_pmt_plan_id = p_role_pmt_plan_id
1974 AND cpp.pmt_plan_id = spp.pmt_plan_id;
1975
1976 END IF;
1977
1978 select pmt_plan_id, start_date, end_date
1979 into l_pmt_plan_id_new, l_pp_start_date_new, l_pp_end_date_new
1980 from cn_role_pmt_plans
1981 where role_pmt_plan_id = p_role_pmt_plan_id;
1982
1983 --Added payment group code for bug 3560026 by Julia Huang on 4/7/2004.
1984 select minimum_amount, maximum_amount, payment_group_code, org_id
1985 into l_min_amt_new, l_max_amt_new, l_pgc, l_org_id
1986 from cn_pmt_plans
1987 where pmt_plan_id = l_pmt_plan_id_new;
1988
1989 select salesrep_id, start_date, end_date
1990 into l_salesrep_id_new, l_srp_start_date_new, l_srp_end_date_new
1991 from cn_srp_roles
1992 where srp_role_id = p_srp_role_id
1993 and org_id = l_org_id;
1994
1995 --Added to check if the a record exists in cn_srp_pmt_plans for the dates passed for bug 3147026
1996 /* Commented out by Julia Huang for bug 3560026 by Julia Huang on 4/7/2004
1997 select count(*) into l_count_srp_pmt_plan from cn_srp_pmt_plans where salesrep_id=l_salesrep_id_old
1998 and ((l_pp_start_date_new between start_date and nvl(end_date,l_null_date))
1999 or (nvl(l_pp_end_date_new,l_null_date) between start_date and nvl(end_date,l_null_date)));
2000 */
2001 SELECT COUNT(*) INTO l_count_srp_pmt_plan
2002 FROM cn_srp_pmt_plans cspp, cn_pmt_plans cpp
2003 WHERE cspp.salesrep_id = l_salesrep_id_old
2004 AND ((l_pp_start_date_new BETWEEN cspp.start_date AND NVL(cspp.end_date,l_pp_start_date_new))
2005 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)))
2006 AND cspp.pmt_plan_id = cpp.pmt_plan_id
2007 AND cpp.payment_group_code = l_pgc;
2008
2009 l_start_date_new := NULL;
2010 l_end_date_new := NULL;
2011
2012 x_return_status := FND_API.G_RET_STS_SUCCESS;
2013
2014 if cn_api.date_range_overlap(
2015 a_start_date => l_srp_start_date_new,
2016 a_end_date => l_srp_end_date_new,
2017 b_start_date => l_pp_start_date_new,
2018 b_end_date => l_pp_end_date_new
2019 ) THEN
2020
2021 /* Bug No 5525456 */
2022
2023 get_masgn_date_intersect(
2024 p_role_pmt_plan_id,
2025 p_srp_role_id,
2026 x_start_date => l_start_date_new,
2027 x_end_date => l_end_date_new);
2028
2029 newrec.salesrep_id := l_salesrep_id_new;
2030 newrec.srp_pmt_plan_id:= l_srp_pmt_plan_id;
2031 newrec.pmt_plan_id := l_pmt_plan_id_new;
2032 newrec.minimum_amount := l_min_amt_new;
2033 newrec.maximum_amount := l_max_amt_new;
2034 newrec.start_date := l_start_date_new;
2035 newrec.end_date := l_end_date_new;
2036 newrec.lock_flag := 'N';
2037 newrec.srp_role_id := p_srp_role_id;
2038 newrec.role_pmt_plan_id := p_role_pmt_plan_id;
2039 newrec.org_id := l_org_id;
2040
2041 if (l_count>0) then
2042
2043 SELECT count(*) into l_worksheets
2044 FROM cn_payment_worksheets_all W, cn_period_statuses_all prd,
2045 cn_payruns_all prun, cn_srp_pmt_plans_all spp
2046 WHERE w.salesrep_id = spp.salesrep_id
2047 AND w.quota_id is null
2048 AND prun.pay_period_id = prd.period_id
2049 AND prun.org_id = prd.org_id
2050 AND prun.payrun_id = w.payrun_id
2051 AND spp.srp_pmt_plan_id = l_srp_pmt_plan_id
2052 AND (((spp.end_date IS NOT NULL) AND (prd.end_date IS NOT NULL)
2053 AND (prd.start_date <= spp.end_date)
2054 AND (prd.end_date >= spp.start_date))
2055 OR ((spp.end_date IS NULL) AND (prd.end_date IS NOT NULL)
2056 AND (prd.end_date >= spp.start_date))
2057 OR ((spp.end_date IS NULL) AND (prd.end_date IS NULL)))
2058 AND (NVL(l_end_date_new, l_end_of_time) < NVL(prd.end_date, l_end_of_time)
2059 OR l_start_date_new > prd.start_date);
2060
2061 IF (l_worksheets = 0) THEN
2062
2063 update_srp_pmt_plan
2064 (
2065 p_api_version => p_api_version,
2066 p_init_msg_list => p_init_msg_list,
2067 p_commit => p_commit,
2068 p_validation_level => p_validation_level,
2069 x_return_status => l_return_status,
2070 x_msg_count => l_msg_count,
2071 x_msg_data => l_msg_data,
2072 p_pmt_plan_assign_rec=> newrec,
2073 x_loading_status => l_loading_status);
2074
2075 /*
2076 IF l_return_status <> fnd_api.g_ret_sts_success THEN
2077 RAISE fnd_api.g_exc_error;
2078 END IF;
2079 */
2080 l_return_status:=FND_API.G_RET_STS_SUCCESS;
2081 x_return_status := l_return_status;
2082 x_loading_status := l_loading_status;
2083
2084 ELSE
2085 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
2086 FND_MESSAGE.Set_Name('CN', 'CN_SPP_UPDATE_NOT_ALLOWED');
2087 FND_MSG_PUB.Add;
2088 END IF;
2089 RAISE FND_API.G_EXC_ERROR;
2090 END IF;
2091
2092 -- Added to create a cn_srp_pmt_plan record if there are no records for the the date range for bug 3147026
2093 ELSIF (l_count_srp_pmt_plan = 0 )
2094 THEN
2095
2096 SELECT count(*)
2097 INTO l_count_srp_pmt_plan
2098 FROM cn_srp_pmt_plans
2099 WHERE salesrep_id = l_salesrep_id_new
2100 AND org_id = l_org_id
2101 AND ((l_start_date_new between start_date and nvl(end_date,l_end_of_time))
2102 OR (nvl(l_end_date_new,l_end_of_time) between
2103 start_date and nvl(end_date,l_end_of_time)));
2104
2105 IF (l_count_srp_pmt_plan = 0) THEN
2106 Create_Srp_Pmt_Plan
2107 (
2108 p_api_version => p_api_version,
2109 p_init_msg_list => p_init_msg_list,
2110 p_commit => p_commit,
2111 p_validation_level => p_validation_level,
2112 x_return_status => l_return_status,
2113 x_msg_count => l_msg_count,
2114 x_msg_data => l_msg_data,
2115 p_pmt_plan_assign_rec=> newrec,
2116 x_loading_status => l_loading_status
2117 );
2118
2119 /*
2120 IF l_return_status <> fnd_api.g_ret_sts_success THEN
2121 RAISE fnd_api.g_exc_error;
2122 END IF;
2123 */
2124 l_return_status:=FND_API.G_RET_STS_SUCCESS;
2125 x_return_status := l_return_status;
2126 x_loading_status := l_loading_status;
2127 END IF;
2128 END IF;
2129
2130 ELSE
2131
2132 -- only delete if exists
2133 IF l_srp_pmt_plan_id IS NOT NULL THEN
2134 delete_srp_pmt_plan
2135 (p_api_version => p_api_version,
2136 p_init_msg_list => p_init_msg_list,
2137 p_commit => p_commit,
2138 p_validation_level => p_validation_level,
2139 x_return_status => l_return_status,
2140 x_msg_count => l_msg_count,
2141 x_msg_data => l_msg_data,
2142 p_srp_pmt_plan_id => l_srp_pmt_plan_id,
2143 x_loading_status => l_loading_status);
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 -- Standard check of p_commit.
2157
2158 IF FND_API.To_Boolean( p_commit ) THEN
2159 COMMIT WORK;
2160 END IF;
2161
2162 -- Standard call to get message count and if count is 1, get message info
2163 FND_MSG_PUB.Count_And_Get
2164 (
2165 p_count => x_msg_count ,
2166 p_data => x_msg_data ,
2167 p_encoded => FND_API.G_FALSE
2168 );
2169
2170 EXCEPTION
2171 WHEN FND_API.G_EXC_ERROR THEN
2172 ROLLBACK TO Update_Mass_Asgn_Srp_Pmt_plan;
2173 x_return_status := FND_API.G_RET_STS_ERROR ;
2174 FND_MSG_PUB.Count_And_Get
2175 (
2176 p_count => x_msg_count ,
2177 p_data => x_msg_data ,
2178 p_encoded => FND_API.G_FALSE
2179 );
2180 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2181 ROLLBACK TO Update_Mass_Asgn_Srp_Pmt_plan;
2182 x_loading_status := 'UNEXPECTED_ERR';
2183 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2184 FND_MSG_PUB.Count_And_Get
2185 (
2186 p_count => x_msg_count ,
2187 p_data => x_msg_data ,
2188 p_encoded => FND_API.G_FALSE
2189 );
2190 WHEN OTHERS THEN
2191 ROLLBACK TO Update_Mass_Asgn_Srp_Pmt_plan;
2192 x_loading_status := 'UNEXPECTED_ERR';
2193 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2194 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2195 THEN
2196 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME ,l_api_name );
2197 END IF;
2198 FND_MSG_PUB.Count_And_Get
2199 (
2200 p_count => x_msg_count ,
2201 p_data => x_msg_data ,
2202 p_encoded => FND_API.G_FALSE
2203 );
2204
2205 End Update_Mass_Asgn_Srp_Pmt_plan;
2206
2207
2208 -- --------------------------------------------------------------------------*
2209 -- Procedure: Delete_Mass_Asgn_Srp_Pmt_plan
2210 -- --------------------------------------------------------------------------*
2211
2212 PROCEDURE Delete_Mass_Asgn_Srp_Pmt_Plan
2213 (p_api_version IN NUMBER,
2214 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2215 p_commit IN VARCHAR2 := FND_API.G_FALSE,
2216 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
2217 x_return_status OUT NOCOPY VARCHAR2,
2218 x_msg_count OUT NOCOPY NUMBER,
2219 x_msg_data OUT NOCOPY VARCHAR2,
2220 p_srp_role_id IN NUMBER,
2221 p_role_pmt_plan_id IN NUMBER,
2222 x_loading_status OUT NOCOPY VARCHAR2
2223 ) IS
2224
2225 l_api_name CONSTANT VARCHAR2(30) := 'Delete_Mass_Asgn_Srp_Pmt_Plan';
2226 l_api_version CONSTANT NUMBER := 1.0;
2227 l_return_status VARCHAR2(2000);
2228 l_msg_count NUMBER;
2229 l_msg_data VARCHAR2(2000);
2230 l_srp_pmt_plan_id cn_srp_pmt_plans.srp_pmt_plan_id%TYPE;
2231 l_loading_status VARCHAR2(2000);
2232
2233 newrec CN_SRP_PMT_PLANS_PUB.srp_pmt_plans_rec_type;
2234 l_salesrep_id cn_salesreps.salesrep_id%TYPE;
2235 l_pmt_plan_id cn_pmt_plans.pmt_plan_id%TYPE;
2236 l_min_amt cn_pmt_plans.minimum_amount%TYPE;
2237 l_max_amt cn_pmt_plans.maximum_amount%TYPE;
2238 l_pp_start_date cn_pmt_plans.start_date%TYPE;
2239 l_pp_end_date cn_pmt_plans.end_date%TYPE;
2240 l_srp_start_date cn_srp_roles.start_date%TYPE;
2241 l_srp_end_date cn_pmt_plans.end_date%TYPE;
2242 l_start_date cn_srp_pmt_plans.start_date%TYPE;
2243 l_end_date cn_srp_pmt_plans.start_date%TYPE;
2244 l_role_pp_start_date cn_role_pmt_plans.start_date%TYPE;
2245 l_role_pp_end_date cn_role_pmt_plans.end_date%TYPE;
2246
2247
2248 CURSOR spp_csr( l_srp_pmt_plan_id NUMBER ) IS
2249 SELECT *
2250 FROM cn_srp_pmt_plans
2251 WHERE srp_pmt_plan_id = l_srp_pmt_plan_id;
2252
2253 l_spp_rec spp_csr%ROWTYPE;
2254 l_dummy NUMBER;
2255
2256
2257 BEGIN
2258
2259 -- Standard Start of API savepoint
2260
2261 SAVEPOINT Delete_Mass_Asgn_Srp_Pmt_Plan;
2262
2263 -- Standard call to check for call compatibility.
2264
2265 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
2266 p_api_version ,
2267 l_api_name ,
2268 G_PKG_NAME )
2269 THEN
2270 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2271 END IF;
2272
2273 -- Initialize message list if p_init_msg_list is set to TRUE.
2274 IF FND_API.to_Boolean( p_init_msg_list ) THEN
2275 FND_MSG_PUB.initialize;
2276 END IF;
2277
2278 -- Initialize API return status to success
2279 x_return_status := FND_API.G_RET_STS_SUCCESS;
2280 x_loading_status := 'CN_PP_DELETED';
2281
2282 BEGIN
2283
2284 --Bug 3670276 by Julia Huang on 6/4/04 to avoid the following
2285 --1.Cartesian join. Line changed: AND spp.role_pmt_plan_id = crpp.role_pmt_plan_id --p_role_pmt_plan_id
2286 --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)
2287 select spp.start_date, spp.end_date, spp.salesrep_id,
2288 cpp.minimum_amount, cpp.maximum_amount,
2289 crpp.start_date, crpp.end_date, spp.srp_pmt_plan_id
2290 into l_start_date, l_end_date, l_salesrep_id,
2291 l_min_amt, l_max_amt,
2292 l_role_pp_start_date, l_role_pp_end_date, l_srp_pmt_plan_id
2293 from cn_srp_pmt_plans spp, cn_pmt_plans cpp, cn_role_pmt_plans crpp
2294 where spp.srp_role_id = p_srp_role_id
2295 AND spp.role_pmt_plan_id = crpp.role_pmt_plan_id --p_role_pmt_plan_id
2296 AND crpp.role_pmt_plan_id = p_role_pmt_plan_id
2297 AND cpp.pmt_plan_id = spp.pmt_plan_id;
2298 EXCEPTION
2299 WHEN no_data_found THEN
2300 null;
2301 END;
2302
2303 IF ((l_salesrep_id IS NOT NULL)
2304
2305 AND
2306
2307 (cn_api.date_range_within(
2308 a_start_date => l_start_date,
2309 a_end_date => l_end_date,
2310 b_start_date => l_role_pp_start_date,
2311 b_end_date => l_role_pp_end_date
2312 )))
2313
2314
2315 THEN
2316
2317 --***********************************************************************
2318 -- Added by CHANTHON on 21-Aug-2006
2319 -- Bug 5465072
2320 -- Moved the code for checking valid delete from check_operation_allowed
2321 -- procedure to this method. This is to validate the resource's payment
2322 -- plan when deleting the mass assignment. If the resource has worksheets
2323 -- then the resource assignment is severed from the role and acts as a
2324 -- direct assignment. If no worksheets then the payment plan is deleted.
2325 --***********************************************************************
2326
2327 OPEN spp_csr(l_srp_pmt_plan_id);
2328 FETCH spp_csr INTO l_spp_rec;
2329 CLOSE spp_csr;
2330
2331
2332 SELECT COUNT(1) INTO l_dummy
2333 FROM cn_payment_worksheets_all W, cn_period_statuses_all prd,
2334 cn_payruns_all prun, cn_payment_transactions_all pmttrans
2335 WHERE w.salesrep_id = l_spp_rec.salesrep_id
2336 AND w.salesrep_id = pmttrans.credited_salesrep_id
2337 AND pmttrans.incentive_type_code = 'PMTPLN'
2338 AND prun.pay_period_id = prd.period_id
2339 AND prun.org_id = prd.org_id
2340 AND prun.payrun_id = w.payrun_id
2341 AND ( ((l_spp_rec.end_date IS NOT NULL) AND (prd.end_date IS NOT NULL)
2342 AND (prd.start_date <= l_spp_rec.end_date)
2343 AND (prd.end_date >= l_spp_rec.start_date))
2344 OR ((l_spp_rec.end_date IS NULL) AND (prd.end_date IS NOT NULL)
2345 AND (prd.end_date >= l_spp_rec.start_date))
2346 OR ((l_spp_rec.end_date IS NULL) AND (prd.end_date IS NULL))
2347 );
2348
2349 IF l_dummy > 0 then
2350 -- IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
2351 -- FND_MESSAGE.SET_NAME ('CN' , 'CN_SRP_PMT_PLAN_USED');
2352 -- FND_MSG_PUB.Add;
2353 update cn_srp_pmt_plans set srp_role_id = null, role_pmt_plan_id = null
2354 where srp_pmt_plan_id = l_srp_pmt_plan_id;
2355
2356 ElSIF l_dummy = 0 THEN
2357
2358 delete_srp_pmt_plan
2359 (p_api_version => p_api_version,
2360 p_init_msg_list => p_init_msg_list,
2361 p_commit => p_commit,
2362 p_validation_level => p_validation_level,
2363 x_return_status => l_return_status,
2364 x_msg_count => l_msg_count,
2365 x_msg_data => l_msg_data,
2366 p_srp_pmt_plan_id => l_srp_pmt_plan_id,
2367 x_loading_status => l_loading_status);
2368 END IF;
2369
2370 /*
2371 IF l_return_status <> fnd_api.g_ret_sts_success THEN
2372 RAISE fnd_api.g_exc_error;
2373 END IF;
2374 */
2375 l_return_status:=FND_API.G_RET_STS_SUCCESS;
2376
2377 x_return_status := l_return_status;
2378 x_loading_status := l_loading_status;
2379
2380 END IF;
2381
2382 -- Standard check of p_commit.
2383
2384 IF FND_API.To_Boolean( p_commit ) THEN
2385 COMMIT WORK;
2386 END IF;
2387
2388 -- Standard call to get message count and if count is 1, get message info
2389 FND_MSG_PUB.Count_And_Get
2390 (
2391 p_count => x_msg_count ,
2392 p_data => x_msg_data ,
2393 p_encoded => FND_API.G_FALSE
2394 );
2395
2396 EXCEPTION
2397 WHEN FND_API.G_EXC_ERROR THEN
2398 ROLLBACK TO Delete_Mass_Asgn_Srp_Pmt_Plan;
2399 x_return_status := FND_API.G_RET_STS_ERROR ;
2400 FND_MSG_PUB.Count_And_Get
2401 (
2402 p_count => x_msg_count ,
2403 p_data => x_msg_data ,
2404 p_encoded => FND_API.G_FALSE
2405 );
2406 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2407 ROLLBACK TO Delete_Mass_Asgn_Srp_Pmt_Plan;
2408 x_loading_status := 'UNEXPECTED_ERR';
2409 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2410 FND_MSG_PUB.Count_And_Get
2411 (
2412 p_count => x_msg_count ,
2413 p_data => x_msg_data ,
2414 p_encoded => FND_API.G_FALSE
2415 );
2416 WHEN OTHERS THEN
2417 ROLLBACK TO Delete_Mass_Asgn_Srp_Pmt_Plan;
2418 x_loading_status := 'UNEXPECTED_ERR';
2419 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2420 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2421 THEN
2422 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME ,l_api_name );
2423 END IF;
2424 FND_MSG_PUB.Count_And_Get
2425 (
2426 p_count => x_msg_count ,
2427 p_data => x_msg_data ,
2428 p_encoded => FND_API.G_FALSE
2429 );
2430
2431 END Delete_Mass_Asgn_Srp_Pmt_Plan;
2432
2433 END cn_srp_pmt_plans_pvt;