[Home] [Help]
PACKAGE BODY: APPS.CN_SRP_PERIODS_PVT
Source
1 PACKAGE BODY CN_SRP_PERIODS_PVT AS
2 /* $Header: cnvsprdb.pls 120.1.12000000.2 2007/08/06 21:21:11 jxsingh ship $ */
3
4 -- Global variable
5 G_PKG_NAME CONSTANT VARCHAR2(30) := 'CN_SRP_PERIODS_PVT';
6 G_FILE_NAME CONSTANT VARCHAR2(12) := 'cnvsprdb.pls';
7
8 --| -----------------------------------------------------------------------+
9 --| Function : Get_Pay_Period
10 --| Return : DATA TYPE:TABLE OF pay_period_rec_type%TYPE
11 --| Desc : Procedure to get pay period id, return null table if not found
12 --| -----------------------------------------------------------------------+
13 FUNCTION Get_Pay_Period
14 (p_start_date IN DATE,
15 p_end_date IN DATE,
16 p_period_set_id IN NUMBER,
17 p_period_type_id IN NUMBER,
18 p_org_id IN NUMBER) RETURN pay_period_rec_tbl_type IS
19 l_pay_period_rec_tbl pay_period_rec_tbl_type;
20 l_start_date cn_period_statuses.start_date%TYPE;
21 l_end_date cn_period_statuses.end_date%TYPE;
22 l_index NUMBER;
23
24 CURSOR c_period_rec_csr
25 ( c_start_date cn_period_statuses.start_date%TYPE,
26 c_end_date cn_period_statuses.end_date%TYPE) IS
27 SELECT period_id , start_date, end_date
28 FROM cn_period_statuses_all
29 WHERE start_date <= c_end_date
30 AND end_date >= c_start_date
31 AND period_type_id = p_period_type_id
32 AND period_set_id = p_period_set_id
33 AND org_id = p_org_id;
34
35 BEGIN
36 SELECT MIN(start_date), MAX(end_date)
37 INTO l_start_date, l_end_date
38 FROM cn_acc_period_statuses_v
39 WHERE period_status IN ('O','F')
40 AND org_id = p_org_id
41 AND ( ( p_end_date IS NOT NULL AND start_date <= p_end_date
42 AND end_date >= p_start_date )
43 OR
44 ( p_end_date IS NULL AND end_date >= p_start_date )
45 );
46
47 IF l_start_date < p_start_date THEN
48 l_start_date := p_start_date;
49 END IF;
50 IF l_end_date > p_end_date THEN
51 l_end_date := p_end_date;
52 END IF ;
53
54 l_index := Nvl(l_pay_period_rec_tbl.last,0) + 1;
55 FOR l_period_rec_csr IN c_period_rec_csr(l_start_date,l_end_date) LOOP
56 -- Get real start date/end date for this pay period
57 IF l_period_rec_csr.start_date < l_start_date THEN
58 l_period_rec_csr.start_date := l_start_date;
59 END IF;
60 IF l_period_rec_csr.end_date > l_end_date THEN
61 l_period_rec_csr.end_date := l_end_date;
62 END IF ;
63
64 l_pay_period_rec_tbl(l_index).period_id := l_period_rec_csr.period_id;
65 l_pay_period_rec_tbl(l_index).start_date := l_period_rec_csr.start_date;
66 l_pay_period_rec_tbl(l_index).end_date := l_period_rec_csr.end_date;
67 l_index := l_index + 1;
68 END LOOP;
69
70 RETURN l_pay_period_rec_tbl;
71
72 END Get_Pay_Period;
73
74 --| -----------------------------------------------------------------------+
75 --| Procedure : Sync_Accum_Balances_Start_Pd
76 --| Desc : Procedure to update begin balance and summary records in
77 --| cn_srp_periods
78 --| Note : 2 prerequisite conditions:
79 --| 1) that periods have all been created and xtd records populated
80 --| 2) there exists a summary record (null quota ID) for each actual record
81 --| -----------------------------------------------------------------------+
82
83 PROCEDURE Sync_Accum_Balances_Start_Pd
84 (p_salesrep_id IN NUMBER,
85 p_org_id IN NUMBER,
86 p_credit_type_id IN NUMBER,
87 p_role_id IN NUMBER,
88 p_start_period_id IN NUMBER) IS
89
90 l_prev_pd_id cn_period_statuses.period_id%TYPE := -1;
91 l_prev_year cn_period_statuses.period_year%TYPE := -1;
92 l_prev_quota cn_srp_periods.quota_id%TYPE := -1;
93 l_prev_spa cn_srp_periods.srp_plan_assign_id%TYPE := -1;
94 l_reset_balances boolean := false;
95 l_srp_period_id number;
96 l_cache_bal1_bbc number;
97 l_cache_bal1_bbd number;
98 l_cache_bal2_bbc number;
99 l_cache_bal2_bbd number;
100 l_cache_bal3_bbc number;
101 l_cache_bal3_bbd number;
102 l_cache_bal4_bbc number;
103 l_cache_bal4_bbd number;
104 l_cache_bal5_bbc number;
105 l_cache_bal5_bbd number;
106
107 CURSOR get_bals IS
108 SELECT /*+ index (sp, CN_SRP_PERIODS_U2)*/
109 srp_period_id, sp.period_id, quota_id,
110 p.period_year, srp_plan_assign_id,
111 balance1_ctd, balance1_dtd,
112 balance1_bbc, balance1_bbd,
113 balance2_ctd, balance2_dtd,
114 balance2_bbc, balance2_bbd,
115 balance3_ctd, balance3_dtd,
116 balance3_bbc, balance3_bbd,
117 balance4_ctd, balance4_dtd,
118 balance4_bbc, balance4_bbd,
119 balance5_ctd, balance5_dtd,
120 balance5_bbc, balance5_bbd
121 FROM cn_srp_periods_all sp, cn_period_statuses_all p
122 WHERE role_id = p_role_id
123 AND salesrep_id = p_salesrep_id
124 AND sp.org_id = p_org_id
125 AND credit_type_id = p_credit_type_id
126 AND quota_id is not null
127 AND sp.period_id = p.period_id
128 AND sp.org_id = p.org_id
129 ORDER BY quota_id, sp.period_id;
130
131 CURSOR get_summ_srp_periods IS
132 select /*+ index (p2, CN_SRP_PERIODS_U2)*/
133 p1.srp_period_id, p1.period_id,
134 nvl(sum(p2.balance1_ctd),0) balance1_ctd,
135 nvl(sum(p2.balance1_dtd),0) balance1_dtd,
136 nvl(sum(p2.balance2_ctd),0) balance2_ctd,
137 nvl(sum(p2.balance2_dtd),0) balance2_dtd,
138 nvl(sum(p2.balance3_ctd),0) balance3_ctd,
139 nvl(sum(p2.balance3_dtd),0) balance3_dtd,
140 nvl(sum(p2.balance4_ctd),0) balance4_ctd,
141 nvl(sum(p2.balance4_dtd),0) balance4_dtd,
142 nvl(sum(p2.balance5_ctd),0) balance5_ctd,
143 nvl(sum(p2.balance5_dtd),0) balance5_dtd,
144
145 nvl(sum(p2.balance1_bbc),0) balance1_bbc,
146 nvl(sum(p2.balance1_bbd),0) balance1_bbd,
147 nvl(sum(p2.balance2_bbc),0) balance2_bbc,
148 nvl(sum(p2.balance2_bbd),0) balance2_bbd,
149 nvl(sum(p2.balance3_bbc),0) balance3_bbc,
150 nvl(sum(p2.balance3_bbd),0) balance3_bbd,
151 nvl(sum(p2.balance4_bbc),0) balance4_bbc,
152 nvl(sum(p2.balance4_bbd),0) balance4_bbd,
153 nvl(sum(p2.balance5_bbc),0) balance5_bbc,
154 nvl(sum(p2.balance5_bbd),0) balance5_bbd,
155 p.period_year
156 from cn_srp_periods_all p1, cn_srp_periods_all p2,
157 cn_period_statuses_all p
158 where p1.salesrep_id = p_salesrep_id
159 and p1.credit_type_id = p_credit_type_id
160 AND p1.org_id = p_org_id
161 and p1.quota_id is null and p1.role_id is null
162 and p1.salesrep_id = p2.salesrep_id (+)
163 and p1.period_id = p2.period_id (+)
164 AND p1.org_id = p2.org_id (+)
165 and p1.period_id = p.period_id
166 and p1.credit_type_id = p2.credit_type_id (+)
167 and p2.role_id (+) is not null and p2.quota_id (+) is not null
168 AND p.org_id = p_org_id
169 group by p1.period_id, p1.srp_period_id, p.period_year
170 order by p1.period_id, p1.srp_period_id;
171
172 cursor get_carry_bal(l_period_id number) is
173 SELECT srp_period_id,
174 balance1_bbd, balance1_bbc,
175 balance2_bbd, balance2_bbc,
176 balance3_bbd, balance3_bbc,
177 balance4_bbd, balance4_bbc,
178 balance5_bbd, balance5_bbc
179 FROM cn_srp_periods_all sp
180 WHERE role_id = -1
181 AND salesrep_id = p_salesrep_id
182 AND org_id = p_org_id
183 AND credit_type_id = p_credit_type_id
184 AND quota_id = -1000
185 AND period_id = l_period_id;
186
187 cb get_carry_bal%rowtype;
188
189 BEGIN
190 -- look at profile to see if balances should be reset after each year
191 if FND_PROFILE.VALUE('CN_RESET_BALANCES_EACH_YEAR') = 'Y' then
192 l_reset_balances := true;
193 end if;
194
195 -- populate BBx columns
196 for b in get_bals loop
197 -- use previous period's BBX and XTD
198 if (l_reset_balances = true AND
199 l_prev_year <> b.period_year) OR l_prev_quota <> b.quota_id
200 OR l_prev_spa <> b.srp_plan_assign_id THEN
201 l_cache_bal1_bbc := 0;
202 l_cache_bal1_bbd := 0;
203 l_cache_bal2_bbc := 0;
204 l_cache_bal2_bbd := 0;
205 l_cache_bal3_bbc := 0;
206 l_cache_bal3_bbd := 0;
207 l_cache_bal4_bbc := 0;
208 l_cache_bal4_bbd := 0;
209 l_cache_bal5_bbc := 0;
210 l_cache_bal5_bbd := 0;
211 end if;
212
213 -- no need to update periods before start period
214 if b.period_id >= p_start_period_id then
215 update cn_srp_periods_all
216 set balance1_bbc = l_cache_bal1_bbc,
217 balance1_bbd = l_cache_bal1_bbd,
218 balance2_bbc = l_cache_bal2_bbc,
219 balance2_bbd = l_cache_bal2_bbd,
220 balance3_bbc = l_cache_bal3_bbc,
221 balance3_bbd = l_cache_bal3_bbd,
222 balance4_bbc = l_cache_bal4_bbc,
223 balance4_bbd = l_cache_bal4_bbd,
224 balance5_bbc = l_cache_bal5_bbc,
225 balance5_bbd = l_cache_bal5_bbd
226 where srp_period_id = b.srp_period_id;
227 end if;
228
229 l_cache_bal1_bbc := l_cache_bal1_bbc + nvl(b.balance1_ctd,0);
230 l_cache_bal1_bbd := l_cache_bal1_bbd + nvl(b.balance1_dtd,0);
231 l_cache_bal2_bbc := l_cache_bal2_bbc + nvl(b.balance2_ctd,0);
232 l_cache_bal2_bbd := l_cache_bal2_bbd + nvl(b.balance2_dtd,0);
233 l_cache_bal3_bbc := l_cache_bal3_bbc + nvl(b.balance3_ctd,0);
234 l_cache_bal3_bbd := l_cache_bal3_bbd + nvl(b.balance3_dtd,0);
235 l_cache_bal4_bbc := l_cache_bal4_bbc + nvl(b.balance4_ctd,0);
236 l_cache_bal4_bbd := l_cache_bal4_bbd + nvl(b.balance4_dtd,0);
237 l_cache_bal5_bbc := l_cache_bal5_bbc + nvl(b.balance5_ctd,0);
238 l_cache_bal5_bbd := l_cache_bal5_bbd + nvl(b.balance5_dtd,0);
239
240 l_prev_year := b.period_year;
241 l_prev_quota := b.quota_id;
242 l_prev_spa := b.srp_plan_assign_id;
243 end loop;
244
245 l_prev_pd_id := -1;
246 l_prev_year := -1;
247 l_cache_bal1_bbc := 0;
248 l_cache_bal1_bbd := 0;
249 l_cache_bal2_bbc := 0;
250 l_cache_bal2_bbd := 0;
251 l_cache_bal3_bbc := 0;
252 l_cache_bal3_bbd := 0;
253 l_cache_bal4_bbc := 0;
254 l_cache_bal4_bbd := 0;
255 l_cache_bal5_bbc := 0;
256 l_cache_bal5_bbd := 0;
257 for p in get_summ_srp_periods loop
258 if p.period_id >= p_start_period_id then
259 update cn_srp_periods_all
260 set balance1_ctd = p.balance1_ctd,
261 balance1_dtd = p.balance1_dtd,
262 balance2_ctd = p.balance2_ctd,
263 balance2_dtd = p.balance2_dtd,
264 balance3_ctd = p.balance3_ctd,
265 balance3_dtd = p.balance3_dtd,
266 balance4_ctd = p.balance4_ctd,
267 balance4_dtd = p.balance4_dtd,
268 balance5_ctd = p.balance5_ctd,
269 balance5_dtd = p.balance5_dtd
270 WHERE srp_period_id = p.srp_period_id;
271 end if;
272
273 if l_prev_pd_id = -1 OR
274 (l_reset_balances = true AND
275 l_prev_year <> p.period_year) THEN
276 l_cache_bal1_bbc := 0;
277 l_cache_bal1_bbd := 0;
278 l_cache_bal2_bbc := 0;
279 l_cache_bal2_bbd := 0;
280 l_cache_bal3_bbc := 0;
281 l_cache_bal3_bbd := 0;
282 l_cache_bal4_bbc := 0;
283 l_cache_bal4_bbd := 0;
284 l_cache_bal5_bbc := 0;
285 l_cache_bal5_bbd := 0;
286 end if;
287 -- ***************************************
288 -- Bug5707688 is fixed by changing > to >=
289 -- ***************************************
290 if p.period_id >= p_start_period_id then
291 update cn_srp_periods_all
292 SET balance1_bbc = l_cache_bal1_bbc,
293 balance1_bbd = l_cache_bal1_bbd,
294 balance2_bbc = l_cache_bal2_bbc,
295 balance2_bbd = l_cache_bal2_bbd,
296 balance3_bbc = l_cache_bal3_bbc,
297 balance3_bbd = l_cache_bal3_bbd,
298 balance4_bbc = l_cache_bal4_bbc,
299 balance4_bbd = l_cache_bal4_bbd,
300 balance5_bbc = l_cache_bal5_bbc,
301 balance5_bbd = l_cache_bal5_bbd
302 where srp_period_id = p.srp_period_id;
303 end if;
304
305 -- update carryover balances - equal to summary bbx - sum of bbx's for
306 -- detail records
307 -- get srp_period_id for PE -1000
308 l_srp_period_id := null;
309 open get_carry_bal(p.period_id);
310 fetch get_carry_bal into cb;
311 close get_carry_bal;
312
313 update cn_srp_periods_all
314 SET balance1_bbc=l_cache_bal1_bbc - p.balance1_bbc + cb.balance1_bbc,
315 balance1_bbd=l_cache_bal1_bbd - p.balance1_bbd + cb.balance1_bbd,
316 balance2_bbc=l_cache_bal2_bbc - p.balance2_bbc + cb.balance2_bbc,
317 balance2_bbd=l_cache_bal2_bbd - p.balance2_bbd + cb.balance2_bbd,
318 balance3_bbc=l_cache_bal3_bbc - p.balance3_bbc + cb.balance3_bbc,
319 balance3_bbd=l_cache_bal3_bbd - p.balance3_bbd + cb.balance3_bbd,
320 balance4_bbc=l_cache_bal4_bbc - p.balance4_bbc + cb.balance4_bbc,
321 balance4_bbd=l_cache_bal4_bbd - p.balance4_bbd + cb.balance4_bbd,
322 balance5_bbc=l_cache_bal5_bbc - p.balance5_bbc + cb.balance5_bbc,
323 balance5_bbd=l_cache_bal5_bbd - p.balance5_bbd + cb.balance5_bbd
324 where srp_period_id = cb.srp_period_id;
325
326 l_cache_bal1_bbc := l_cache_bal1_bbc + p.balance1_ctd;
327 l_cache_bal1_bbd := l_cache_bal1_bbd + p.balance1_dtd;
328 l_cache_bal2_bbc := l_cache_bal2_bbc + p.balance2_ctd;
329 l_cache_bal2_bbd := l_cache_bal2_bbd + p.balance2_dtd;
330 l_cache_bal3_bbc := l_cache_bal3_bbc + p.balance3_ctd;
331 l_cache_bal3_bbd := l_cache_bal3_bbd + p.balance3_dtd;
332 l_cache_bal4_bbc := l_cache_bal4_bbc + p.balance4_ctd;
333 l_cache_bal4_bbd := l_cache_bal4_bbd + p.balance4_dtd;
334 l_cache_bal5_bbc := l_cache_bal5_bbc + p.balance5_ctd;
335 l_cache_bal5_bbd := l_cache_bal5_bbd + p.balance5_dtd;
336 l_prev_pd_id := p.period_id;
337 l_prev_year := p.period_year;
338 end loop;
339
340 END Sync_Accum_Balances_Start_Pd;
341
342 PROCEDURE Sync_Accum_Balances
343 (p_salesrep_id IN NUMBER,
344 p_org_id IN NUMBER,
345 p_credit_type_id IN NUMBER,
346 p_role_id IN NUMBER) IS
347 BEGIN
348 -- no start period given... just update all periods
349 Sync_Accum_Balances_Start_Pd
350 (p_salesrep_id => p_salesrep_id,
351 p_org_id => p_org_id,
352 p_credit_type_id => p_credit_type_id,
353 p_role_id => p_role_id,
354 p_start_period_id => -1); -- no negative period ID's
355 END Sync_Accum_Balances;
356
357 --| -----------------------------------------------------------------------+
358 --| Procedure : Create_Srp_Periods
359 --| Desc : Procedure to create a new row in cn_srp_periods
360 --| Note : This is called by srp_pay_group_assign
361 --| -----------------------------------------------------------------------+
362 PROCEDURE Create_Srp_Periods
363 (p_api_version IN NUMBER,
364 p_init_msg_list IN VARCHAR2,
365 p_commit IN VARCHAR2,
366 p_validation_level IN NUMBER,
367 x_return_status OUT NOCOPY VARCHAR2,
368 x_msg_count OUT NOCOPY NUMBER,
369 x_msg_data OUT NOCOPY VARCHAR2,
370 p_salesrep_id IN NUMBER,
371 p_role_id IN NUMBER,
372 p_comp_plan_id IN NUMBER,
373 p_start_date IN DATE,
374 p_end_date IN DATE,
375 p_sync_flag IN VARCHAR2,
376 x_loading_status OUT NOCOPY VARCHAR2
377 ) IS
378 BEGIN
379 Create_Srp_Periods_Per_Quota
380 (p_api_version => p_api_version,
381 p_init_msg_list => p_init_msg_list,
382 p_commit => p_commit,
383 p_validation_level => p_validation_level,
384 x_return_status => x_return_status,
385 x_msg_count => x_msg_count,
386 x_msg_data => x_msg_data,
387 p_salesrep_id => p_salesrep_id,
388 p_role_id => p_role_id,
389 p_comp_plan_id => p_comp_plan_id,
390 p_quota_id => NULL, -- do for all quotas
391 p_start_date => p_start_date,
392 p_end_date => p_end_date,
393 p_sync_flag => p_sync_flag,
394 x_loading_status => x_loading_status);
395 END Create_Srp_Periods;
396
397 --| -----------------------------------------------------------------------+
398 --| Procedure : Create_Srp_Periods_Per_Quota
399 --| Desc : Procedure to create a new row in cn_srp_periods for a new quota
400 --| Note : This is called by srp_pay_group_assign
401 --| -----------------------------------------------------------------------+
402 PROCEDURE Create_Srp_Periods_Per_Quota
403 (p_api_version IN NUMBER,
404 p_init_msg_list IN VARCHAR2,
405 p_commit IN VARCHAR2,
406 p_validation_level IN NUMBER,
407 x_return_status OUT NOCOPY VARCHAR2,
408 x_msg_count OUT NOCOPY NUMBER,
409 x_msg_data OUT NOCOPY VARCHAR2,
410 p_salesrep_id IN NUMBER,
411 p_role_id IN NUMBER,
412 p_comp_plan_id IN NUMBER,
413 p_quota_id IN NUMBER,
414 p_start_date IN DATE,
415 p_end_date IN DATE,
416 p_sync_flag IN VARCHAR2,
417 x_loading_status OUT NOCOPY VARCHAR2
418 ) IS
419 l_api_name CONSTANT VARCHAR2(30) := 'Create_Srp_Periods';
420 l_api_version CONSTANT NUMBER := 1.0;
421
422 l_pay_period_rec_tbl pay_period_rec_tbl_type;
423 l_srp_period_id cn_srp_periods.srp_period_id%TYPE;
424 l_min_period_id cn_srp_periods.period_id%TYPE;
425 l_dummy NUMBER;
426 l_org_id NUMBER;
427 l_pg_found BOOLEAN;
428
429 CURSOR c_srp_pay_grp_csr IS
430 (SELECT spg.pay_group_id, spg.start_date, spg.end_date,
431 pg.period_set_id, pg.period_type_id
432 FROM cn_srp_pay_groups_all spg,cn_pay_groups_all pg
433 WHERE spg.salesrep_id = p_salesrep_id
434 AND spg.org_id = l_org_id
435 AND spg.pay_group_id = pg.pay_group_id
436 AND ( ( (p_end_date IS NOT NULL) AND (spg.end_date IS NOT NULL)
437 AND (spg.start_date <= p_end_date)
438 AND (spg.end_date >= p_start_date))
439 OR ((p_end_date IS NOT NULL) AND (spg.end_date IS NULL)
440 AND (spg.start_date <= p_end_date))
441 OR ((p_end_date IS NULL) AND (spg.end_date IS NOT NULL)
442 AND (spg.end_date >= p_start_date))
443 OR ((p_end_date IS NULL) AND (spg.end_date IS NULL))
444 )
445 ) ;
446
447 -- if null quota ID passed in, then loop through all quotas
448 CURSOR c_quota_csr IS
449 (SELECT credit_type_id, quota_id
450 FROM cn_quotas_all
451 WHERE quota_id IN
452 (SELECT quota_id FROM cn_quota_assigns
453 WHERE comp_plan_id = p_comp_plan_id)
454 AND quota_id = nvl(p_quota_id, quota_id));
455
456
457 CURSOR get_summ_pds(c_credit_type_id cn_srp_periods.credit_type_id%TYPE) IS
458 select p.period_id, p.start_date, p.end_date
459 from cn_period_statuses_all p, cn_repositories_all r
460 where p.period_id >= l_min_period_id
461 and r.period_type_id = p.period_type_id
462 and r.period_set_id = p.period_set_id
463 AND p.org_id = l_org_id
464 AND r.org_id = l_org_id
465 and not exists (select 1 from cn_srp_periods_all
466 where salesrep_id = p_salesrep_id and period_id = p.period_id
467 and role_id is null and quota_id is NULL
468 AND org_id = l_org_id
469 AND credit_type_id = c_credit_type_id)
470
471 order by 1;
472
473 CURSOR get_carry_pds(c_credit_type_id cn_srp_periods.credit_type_id%TYPE) IS
474 select p.period_id, p.start_date, p.end_date
475 from cn_period_statuses_all p, cn_repositories_all r
476 where p.period_id >= l_min_period_id
477 and r.period_type_id = p.period_type_id
478 and r.period_set_id = p.period_set_id
479 AND p.org_id = l_org_id
480 AND r.org_id = l_org_id
481 and not exists (select 1 from cn_srp_periods_all
482 where salesrep_id = p_salesrep_id and period_id = p.period_id
483 and role_id = -1 and quota_id = -1000
484 AND org_id = l_org_id
485 AND credit_type_id = c_credit_type_id)
486 order by 1;
487
488
489 CURSOR get_srp_cts IS
490 select distinct credit_type_id
491 from cn_srp_periods_all
492 where salesrep_id = p_salesrep_id
493 AND org_id = l_org_id
494 and quota_id is not null
495 and credit_type_id is not null;
496
497 CURSOR c_get_spa(l_start_date date) IS
498 select srp_plan_assign_id
499 from cn_srp_plan_assigns_all
500 where salesrep_id = p_salesrep_id
501 AND org_id = l_org_id
502 and role_id = p_role_id
503 and comp_plan_id = p_comp_plan_id
504 and l_start_date between start_date and nvl(end_date, l_start_date);
505
506 l_srp_pay_grp_csr c_srp_pay_grp_csr%ROWTYPE;
507 l_quota_csr c_quota_csr%ROWTYPE;
508 l_srp_plan_assign_id cn_srp_plan_assigns.srp_plan_assign_id%TYPE;
509
510 BEGIN
511 -- Standard Start of API savepoint
512 SAVEPOINT Create_Srp_Periods;
513 -- Standard call to check for call compatibility.
514 IF NOT FND_API.compatible_api_call
515 (l_api_version,p_api_version,l_api_name,g_pkg_name) THEN
516 RAISE FND_API.g_exc_unexpected_error;
517 END IF ;
518 -- Initialize message list if p_init_msg_list is set to TRUE.
519 IF FND_API.to_Boolean( p_init_msg_list ) THEN
520 FND_MSG_PUB.initialize;
521 END IF;
522 -- Initialize API return status to success
523 x_return_status := FND_API.G_RET_STS_SUCCESS;
524 x_loading_status := 'CN_INSERTED';
525
526 -- API body
527
528 -- derive org ID from comp plan ID
529 SELECT org_id
530 INTO l_org_id
531 FROM cn_comp_plans_all
532 WHERE comp_plan_id = p_comp_plan_id;
533
534 l_pg_found := FALSE;
535 OPEN c_srp_pay_grp_csr;
536 LOOP
537 FETCH c_srp_pay_grp_csr INTO l_srp_pay_grp_csr;
538 EXIT WHEN c_srp_pay_grp_csr%NOTFOUND;
539
540 l_pg_found := TRUE;
541
542 IF l_srp_pay_grp_csr.start_date < p_start_date THEN
543 l_srp_pay_grp_csr.start_date := p_start_date;
544 END IF;
545 IF ((l_srp_pay_grp_csr.end_date IS NULL)
546 OR ((l_srp_pay_grp_csr.end_date IS NOT NULL)
547 AND (p_end_date IS NOT NULL)
548 AND (l_srp_pay_grp_csr.end_date > p_end_date))) THEN
549 l_srp_pay_grp_csr.end_date := p_end_date;
550 END IF ;
551
552 l_pay_period_rec_tbl :=
553 Get_Pay_Period
554 (p_start_date => l_srp_pay_grp_csr.start_date,
555 p_end_date => l_srp_pay_grp_csr.end_date,
556 p_period_set_id => l_srp_pay_grp_csr.period_set_id,
557 p_period_type_id => l_srp_pay_grp_csr.period_type_id,
558 p_org_id => l_org_id);
559
560 OPEN c_quota_csr;
561 LOOP
562 FETCH c_quota_csr INTO l_quota_csr;
563 EXIT WHEN c_quota_csr%NOTFOUND;
564
565 FOR i IN 1 .. l_pay_period_rec_tbl.COUNT LOOP
566 SELECT count(1) INTO l_dummy
567 FROM cn_srp_periods_all
568 WHERE salesrep_id = p_salesrep_id
569 AND org_id = l_org_id
570 AND period_id = l_pay_period_rec_tbl(i).period_id
571 AND role_id = p_role_id
572 AND quota_id = l_quota_csr.quota_id
573 AND credit_type_id = l_quota_csr.credit_type_id;
574
575 -- get srp_plan_assign_id
576 OPEN c_get_spa(l_pay_period_rec_tbl(i).start_date);
577 FETCH c_get_spa INTO l_srp_plan_assign_id;
578 CLOSE c_get_spa;
579
580 IF l_dummy = 0 THEN
581 cn_srp_periods_pkg.insert_row
582 (x_srp_period_id => l_srp_period_id
583 ,x_salesrep_id => p_salesrep_id
584 ,x_org_id => l_org_id
585 ,x_period_id => l_pay_period_rec_tbl(i).period_id
586 ,x_start_date => l_pay_period_rec_tbl(i).start_date
587 ,x_end_date => l_pay_period_rec_tbl(i).end_date
588 ,x_credit_type_id => l_quota_csr.credit_type_id
589 ,x_srp_plan_assign_id => l_srp_plan_assign_id
590 ,x_role_id => p_role_id
591 ,x_quota_id => l_quota_csr.quota_id
592 ,x_pay_group_id => l_srp_pay_grp_csr.pay_group_id
593 ,x_created_by => FND_GLOBAL.USER_ID
594 ,x_creation_date => SYSDATE
595 ,x_last_update_date => SYSDATE
596 ,x_last_updated_by => FND_GLOBAL.USER_ID
597 ,x_last_update_login => FND_GLOBAL.LOGIN_ID
598 );
599 ELSE
600 -- records exist - update plan assign ID
601 update cn_srp_periods_all
602 set srp_plan_assign_id = l_srp_plan_assign_id,
603 start_date = l_pay_period_rec_tbl(i).start_date,
604 end_date = l_pay_period_rec_tbl(i).end_date
605 where salesrep_id = p_salesrep_id
606 AND org_id = l_org_id
607 AND period_id = l_pay_period_rec_tbl(i).period_id
608 AND role_id = p_role_id
609 AND quota_id = l_quota_csr.quota_id
610 AND credit_type_id = l_quota_csr.credit_type_id;
611 END IF;
612 END LOOP; -- pay_period loop
613 END LOOP; -- quota loop
614 CLOSE c_quota_csr;
615
616 END LOOP ; -- pay group assign loop
617 IF l_pg_found = FALSE then
618 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
619 THEN
620 FND_MESSAGE.SET_NAME('CN' , 'CN_SRP_PAY_GROUP_NOT_FOUND');
621 FND_MSG_PUB.Add;
622 END IF;
623 x_loading_status := 'CN_SRP_PAY_GROUP_NOT_FOUND' ;
624 RAISE FND_API.G_EXC_ERROR ;
625 END IF;
626 CLOSE c_srp_pay_grp_csr;
627
628 -- Create summary srp period records where necessary for all applicable
629 -- credit types
630 -- get min srp period ID
631 for ct in get_srp_cts loop
632 select min(period_id) into l_min_period_id
633 from cn_srp_periods_all
634 where salesrep_id = p_salesrep_id
635 AND org_id = l_org_id
636 and quota_id is not null
637 and credit_type_id = ct.credit_type_id;
638
639 -- Bug 2690859
640 -- Add ct.credit_type_id to cursor get_summ_pds() so it'll create
641 -- summary record for different credit_type_id
642 for p in get_summ_pds(ct.credit_type_id) loop
643 cn_srp_periods_pkg.insert_row
644 (x_srp_period_id => l_srp_period_id
645 ,x_salesrep_id => p_salesrep_id
646 ,x_org_id => l_org_id
647 ,x_period_id => p.period_id
648 ,x_start_date => p.start_date
649 ,x_end_date => p.end_date
650 ,x_credit_type_id => ct.credit_type_id
651 ,x_srp_plan_assign_id => null
652 ,x_role_id => null
653 ,x_quota_id => null
654 ,x_pay_group_id => null
655 ,x_created_by => FND_GLOBAL.USER_ID
656 ,x_creation_date => SYSDATE
657 ,x_last_update_date => SYSDATE
658 ,x_last_updated_by => FND_GLOBAL.USER_ID
659 ,x_last_update_login => FND_GLOBAL.LOGIN_ID
660 );
661 end loop; -- periods
662
663 for p in get_carry_pds(ct.credit_type_id) loop
664 cn_srp_periods_pkg.insert_row
665 (x_srp_period_id => l_srp_period_id
666 ,x_salesrep_id => p_salesrep_id
667 ,x_org_id => l_org_id
668 ,x_period_id => p.period_id
669 ,x_start_date => p.start_date
670 ,x_end_date => p.end_date
671 ,x_credit_type_id => ct.credit_type_id
672 ,x_srp_plan_assign_id => -1
673 ,x_role_id => -1
674 ,x_quota_id => -1000
675 ,x_pay_group_id => -1
676 ,x_created_by => FND_GLOBAL.USER_ID
677 ,x_creation_date => SYSDATE
678 ,x_last_update_date => SYSDATE
679 ,x_last_updated_by => FND_GLOBAL.USER_ID
680 ,x_last_update_login => FND_GLOBAL.LOGIN_ID
681 );
682 end loop; -- periods
683
684 -- populate begin balance columns and summary records
685 -- only if p_sync_flag is true... fixed for bug 3193482
686 IF p_sync_flag = FND_API.G_TRUE THEN
687 Sync_Accum_Balances(p_salesrep_id, l_org_id, ct.credit_type_id, p_role_id);
688 END IF;
689 end loop; -- credit types
690
691 -- End of API body.
692 -- Standard check of p_commit.
693 IF FND_API.To_Boolean( p_commit ) THEN
694 COMMIT WORK;
695 END IF;
696 -- Standard call to get message count and if count is 1, get message info.
697 FND_MSG_PUB.Count_And_Get
698 (
699 p_count => x_msg_count ,
700 p_data => x_msg_data ,
701 p_encoded => FND_API.G_FALSE
702 );
703
704 EXCEPTION
705 WHEN COLLECTION_IS_NULL THEN
706 ROLLBACK TO Create_Srp_Periods;
707 x_loading_status := 'CN_PAY_PERIOD_NOT_EXIST';
708 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
709 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
710 THEN
711 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME ,l_api_name );
712 END IF;
713 FND_MSG_PUB.Count_And_Get
714 (
715 p_count => x_msg_count ,
716 p_data => x_msg_data ,
717 p_encoded => FND_API.G_FALSE
718 );
719 WHEN FND_API.G_EXC_ERROR THEN
720 ROLLBACK TO Create_Srp_Periods;
721 x_return_status := FND_API.G_RET_STS_ERROR ;
722 FND_MSG_PUB.Count_And_Get
723 (
724 p_count => x_msg_count ,
725 p_data => x_msg_data ,
726 p_encoded => FND_API.G_FALSE
727 );
728 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
729 ROLLBACK TO Create_Srp_Periods;
730 x_loading_status := 'UNEXPECTED_ERR';
731 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
732 FND_MSG_PUB.Count_And_Get
733 (
734 p_count => x_msg_count ,
735 p_data => x_msg_data ,
736 p_encoded => FND_API.G_FALSE
737 );
738 WHEN OTHERS THEN
739 ROLLBACK TO Create_Srp_Periods;
740 /* Change Made By hithanki -- Start*/
741 ROLLBACK TO Create_Srp_Periods;
742 IF SQLCODE = '-1'
743 THEN
744 fnd_message.set_name('CN', 'CN_CREATE_ROLE_PLAN_ERR');
745 fnd_msg_pub.ADD;
746 RAISE fnd_api.g_exc_error;
747 END IF;
748 /* Change Made By hithanki -- End */
749
750 x_loading_status := 'UNEXPECTED_ERR';
751 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
752 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
753 THEN
754 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME ,l_api_name );
755 END IF;
756 FND_MSG_PUB.Count_And_Get
757 (
758 p_count => x_msg_count ,
759 p_data => x_msg_data ,
760 p_encoded => FND_API.G_FALSE
761 );
762 END Create_Srp_Periods_Per_Quota;
763
764 --| -----------------------------------------------------------------------+
765 --| Procedure : Update_Delta_Srp_Pds_No_Sync
766 --| Desc : Procedure to update row in cn_srp_periods, add deltas into it
767 --| -----------------------------------------------------------------------+
768
769 PROCEDURE Update_Delta_Srp_Pds_No_Sync
770 (p_api_version IN NUMBER,
771 p_init_msg_list IN VARCHAR2,
772 p_commit IN VARCHAR2,
773 p_validation_level IN NUMBER,
774 x_return_status OUT NOCOPY VARCHAR2,
775 x_msg_count OUT NOCOPY NUMBER,
776 x_msg_data OUT NOCOPY VARCHAR2,
777 p_del_srp_prd_rec IN delta_srp_period_rec_type,
778 x_loading_status OUT NOCOPY VARCHAR2
779 ) IS
780 l_api_name CONSTANT VARCHAR2(30) := 'Update_Delta_Srp_Pds_No_Sync';
781 l_api_version CONSTANT NUMBER := 1.0;
782
783 BEGIN
784 -- Standard Start of API savepoint
785 SAVEPOINT Update_Delta_Srp_Pds_No_Sync;
786 -- Standard call to check for call compatibility.
787 IF NOT FND_API.compatible_api_call
788 (l_api_version,p_api_version,l_api_name,g_pkg_name) THEN
789 RAISE FND_API.g_exc_unexpected_error;
790 END IF ;
791 -- Initialize message list if p_init_msg_list is set to TRUE.
792 IF FND_API.to_Boolean( p_init_msg_list ) THEN
793 FND_MSG_PUB.initialize;
794 END IF;
795 -- Initialize API return status to success
796 x_return_status := FND_API.G_RET_STS_SUCCESS;
797 x_loading_status := 'CN_UPDATED';
798 -- API body
799
800 -- Update record's ctd and dtd
801 UPDATE cn_srp_periods_all
802 SET
803 balance1_ctd = (Nvl(balance1_ctd,0) +
804 Nvl(p_del_srp_prd_rec.del_balance1_ctd,0)),
805 balance1_dtd = (Nvl(balance1_dtd,0) +
806 Nvl(p_del_srp_prd_rec.del_balance1_dtd,0)),
807 balance2_ctd = (Nvl(balance2_ctd,0) +
808 Nvl(p_del_srp_prd_rec.del_balance2_ctd,0)),
809 balance2_dtd = (Nvl(balance2_dtd,0) +
810 Nvl(p_del_srp_prd_rec.del_balance2_dtd,0)),
811 balance3_ctd = (Nvl(balance3_ctd,0) +
812 Nvl(p_del_srp_prd_rec.del_balance3_ctd,0)),
813 balance3_dtd = (Nvl(balance3_dtd,0) +
814 Nvl(p_del_srp_prd_rec.del_balance3_dtd,0)),
815 balance4_ctd = (Nvl(balance4_ctd,0) +
816 Nvl(p_del_srp_prd_rec.del_balance4_ctd,0)),
817 balance4_dtd = (Nvl(balance4_dtd,0) +
818 Nvl(p_del_srp_prd_rec.del_balance4_dtd,0)),
819 balance5_ctd = (Nvl(balance5_ctd,0) +
820 Nvl(p_del_srp_prd_rec.del_balance5_ctd,0)),
821 balance5_dtd = (Nvl(balance5_dtd,0) +
822 Nvl(p_del_srp_prd_rec.del_balance5_dtd,0))
823 WHERE srp_period_id = p_del_srp_prd_rec.srp_period_id;
824
825 -- End of API body.
826 -- Standard check of p_commit.
827 IF FND_API.To_Boolean( p_commit ) THEN
828 COMMIT WORK;
829 END IF;
830 -- Standard call to get message count and if count is 1, get message info.
831 FND_MSG_PUB.Count_And_Get
832 (
833 p_count => x_msg_count ,
834 p_data => x_msg_data ,
835 p_encoded => FND_API.G_FALSE
836 );
837
838 EXCEPTION
839 WHEN FND_API.G_EXC_ERROR THEN
840 ROLLBACK TO Update_Delta_Srp_Pds_No_Sync;
841 x_return_status := FND_API.G_RET_STS_ERROR ;
842 FND_MSG_PUB.Count_And_Get
843 (
844 p_count => x_msg_count ,
845 p_data => x_msg_data ,
846 p_encoded => FND_API.G_FALSE
847 );
848 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
849 ROLLBACK TO Update_Delta_Srp_Pds_No_Sync;
850 x_loading_status := 'UNEXPECTED_ERR';
851 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
852 FND_MSG_PUB.Count_And_Get
853 (
854 p_count => x_msg_count ,
855 p_data => x_msg_data ,
856 p_encoded => FND_API.G_FALSE
857 );
858 WHEN OTHERS THEN
859 ROLLBACK TO Update_Delta_Srp_Pds_No_Sync;
860 x_loading_status := 'UNEXPECTED_ERR';
861 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
862 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
863 THEN
864 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME ,l_api_name );
865 END IF;
866 FND_MSG_PUB.Count_And_Get
867 (
868 p_count => x_msg_count ,
869 p_data => x_msg_data ,
870 p_encoded => FND_API.G_FALSE
871 );
872 END Update_Delta_Srp_Pds_No_Sync;
873
874 --| -----------------------------------------------------------------------+
875 --| Procedure : Update_Delta_Srp_Periods
876 --| Desc : Procedure to update row in cn_srp_periods, add deltas into it
877 --| Note : updates xtd and bbx columns and summary srp periods
878 --| -----------------------------------------------------------------------+
879
880 PROCEDURE Update_Delta_Srp_Periods
881 (p_api_version IN NUMBER,
882 p_init_msg_list IN VARCHAR2,
883 p_commit IN VARCHAR2,
884 p_validation_level IN NUMBER,
885 x_return_status OUT NOCOPY VARCHAR2,
886 x_msg_count OUT NOCOPY NUMBER,
887 x_msg_data OUT NOCOPY VARCHAR2,
888 p_del_srp_prd_rec IN delta_srp_period_rec_type,
889 x_loading_status OUT NOCOPY VARCHAR2
890 ) IS
891 l_api_name CONSTANT VARCHAR2(30) := 'Update_Delta_Srp_Periods';
892 l_api_version CONSTANT NUMBER := 1.0;
893
894 -- get parameters for sync_accum_bals
895 l_salesrep_id NUMBER;
896 l_role_id NUMBER;
897 l_credit_type_id NUMBER;
898 l_org_id NUMBER;
899
900 BEGIN
901 -- Standard Start of API savepoint
902 SAVEPOINT Update_Delta_Srp_Periods;
903 -- Standard call to check for call compatibility.
904 IF NOT FND_API.compatible_api_call
905 (l_api_version,p_api_version,l_api_name,g_pkg_name) THEN
906 RAISE FND_API.g_exc_unexpected_error;
907 END IF ;
908 -- Initialize message list if p_init_msg_list is set to TRUE.
909 IF FND_API.to_Boolean( p_init_msg_list ) THEN
910 FND_MSG_PUB.initialize;
911 END IF;
912 -- Initialize API return status to success
913 x_return_status := FND_API.G_RET_STS_SUCCESS;
914 x_loading_status := 'CN_UPDATED';
915 -- API body
916
917 -- populate header info of delta srp period rec
918 BEGIN
919 SELECT salesrep_id, credit_type_id, role_id, org_id
920 INTO l_salesrep_id, l_credit_type_id, l_role_id, l_org_id
921 FROM cn_srp_periods_all
922 WHERE srp_period_id = p_del_srp_prd_rec.srp_period_id;
923 EXCEPTION
924 WHEN NO_DATA_FOUND THEN
925 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
926 THEN
927 FND_MESSAGE.SET_NAME('CN' , 'CN_SRP_PERIOD_NOT_EXIST');
928 FND_MSG_PUB.Add;
929 END IF;
930 x_loading_status := 'CN_SRP_PERIOD_NOT_EXIST' ;
931 RAISE FND_API.G_EXC_ERROR ;
932 END;
933
934 -- update balances
935 Update_Delta_Srp_Pds_No_Sync
936 (p_api_version => 1.0,
937 x_return_status => x_return_status,
938 x_msg_count => x_msg_count,
939 x_msg_data => x_msg_data,
940 p_del_srp_prd_rec => p_del_srp_prd_rec,
941 x_loading_status => x_loading_status);
942
943 if x_return_status <> FND_API.G_RET_STS_SUCCESS then
944 RAISE FND_API.G_EXC_ERROR;
945 end if;
946
947 -- populate begin balance columns and summary records
948 Sync_Accum_Balances(l_salesrep_id, l_org_id, l_credit_type_id, l_role_id);
949
950 -- End of API body.
951 -- Standard check of p_commit.
952 IF FND_API.To_Boolean( p_commit ) THEN
953 COMMIT WORK;
954 END IF;
955 -- Standard call to get message count and if count is 1, get message info.
956 FND_MSG_PUB.Count_And_Get
957 (
958 p_count => x_msg_count ,
959 p_data => x_msg_data ,
960 p_encoded => FND_API.G_FALSE
961 );
962
963 EXCEPTION
964 WHEN FND_API.G_EXC_ERROR THEN
965 ROLLBACK TO Update_Delta_Srp_Periods;
966 x_return_status := FND_API.G_RET_STS_ERROR ;
967 FND_MSG_PUB.Count_And_Get
968 (
969 p_count => x_msg_count ,
970 p_data => x_msg_data ,
971 p_encoded => FND_API.G_FALSE
972 );
973 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
974 ROLLBACK TO Update_Delta_Srp_Periods;
975 x_loading_status := 'UNEXPECTED_ERR';
976 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
977 FND_MSG_PUB.Count_And_Get
978 (
979 p_count => x_msg_count ,
980 p_data => x_msg_data ,
981 p_encoded => FND_API.G_FALSE
982 );
983 WHEN OTHERS THEN
984 ROLLBACK TO Update_Delta_Srp_Periods;
985 x_loading_status := 'UNEXPECTED_ERR';
986 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
987 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
988 THEN
989 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME ,l_api_name );
990 END IF;
991 FND_MSG_PUB.Count_And_Get
992 (
993 p_count => x_msg_count ,
994 p_data => x_msg_data ,
995 p_encoded => FND_API.G_FALSE
996 );
997
998 END Update_Delta_Srp_Periods;
999
1000 --| -----------------------------------------------------------------------+
1001 --| Procedure : Update_Pmt_Delta_Srp_Periods
1002 --| Desc : Procedure to update row in cn_srp_periods, add deltas into it
1003 --| Note : no longer used - obsolete
1004 --| -----------------------------------------------------------------------+
1005
1006 PROCEDURE Update_Pmt_Delta_Srp_Periods
1007 (p_api_version IN NUMBER,
1008 p_init_msg_list IN VARCHAR2,
1009 p_commit IN VARCHAR2,
1010 p_validation_level IN NUMBER,
1011 x_return_status OUT NOCOPY VARCHAR2,
1012 x_msg_count OUT NOCOPY NUMBER,
1013 x_msg_data OUT NOCOPY VARCHAR2,
1014 p_del_srp_prd_rec IN delta_srp_period_rec_type,
1015 x_loading_status OUT NOCOPY VARCHAR2
1016 ) IS
1017
1018 BEGIN
1019 null;
1020 END Update_Pmt_Delta_Srp_Periods ;
1021
1022 END CN_SRP_PERIODS_PVT ;