[Home] [Help]
PACKAGE BODY: APPS.CN_PAYMENT_WORKSHEET_PVT
Source
1 PACKAGE BODY cn_payment_worksheet_pvt AS
2 -- $Header: cnvwkshb.pls 120.30 2009/02/09 12:05:58 rajukum ship $
3 g_api_version CONSTANT NUMBER := 1.0;
4 g_pkg_name CONSTANT VARCHAR2(30) := 'CN_Payment_Worksheet_PVT';
5 --G_last_update_date DATE := sysdate;
6 -- g_last_updated_by NUMBER := fnd_global.user_id;
7 --G_creation_date DATE := sysdate;
8 --g_created_by NUMBER := fnd_global.user_id;
9 --g_last_update_login NUMBER := fnd_global.login_id;
10 g_credit_type_id CONSTANT NUMBER := -1000;
11
12
13 PROCEDURE update_ptd_details (
14 p_salesrep_id IN NUMBER,
15 p_payrun_id IN NUMBER
16 )
17 IS
18 l_comm_ptd number ;
19 l_bonus_ptd number ;
20 l_bal NUMBER ;
21 l_bb_earn NUMBER ;
22 l_bb_pmt_recover NUMBER ;
23 l_bb4_begin NUMBER ;
24 BEGIN
25
26 BEGIN
27
28 -- get data from summary record where quota_id is null
29 SELECT SUM(nvl(balance2_bbd, 0) - nvl(balance2_bbc, 0)) prior_earning,
30 - (SUM(nvl(balance4_bbd, 0) - nvl(balance4_bbc, 0))) - (SUM(nvl(balance4_dtd, 0) - nvl(balance4_ctd, 0))),
31 SUM(nvl(balance4_bbc, 0) - nvl(balance4_bbd, 0)) begin_b4
32 INTO l_bb_earn,
33 l_bb_pmt_recover,
34 l_bb4_begin
35 FROM cn_srp_periods_all s,
36 cn_payruns_all pr
37 WHERE s.salesrep_id = p_salesrep_id
38 AND s.org_id = pr.org_id
39 AND pr.payrun_id = p_payrun_id
40 AND s.quota_id IS NULL
41 AND pr.pay_period_id = s.period_id
42 AND s.credit_type_id = g_credit_type_id ;
43
44 EXCEPTION
45 WHEN no_data_found THEN
46 l_bb_earn := 0;
47 l_bb_pmt_recover := 0;
48 END;
49
50 l_bal := nvl(l_bb_earn,0) + nvl(l_bb4_begin,0) ;
51
52 BEGIN
53
54 SELECT SUM(CASE
55 WHEN quota.quota_group_code = 'BONUS' THEN
56 nvl(cspq.commission_payed_ptd, 0)
57 ELSE
58 0
59 END) bonus_ptd,
60 SUM(CASE
61 WHEN quota.quota_group_code = 'QUOTA' THEN
62 nvl(cspq.commission_payed_ptd, 0)
63 ELSE
64 0
65 END) comm_ptd
66 INTO l_bonus_ptd,
67 l_comm_ptd
68 FROM cn_srp_period_quotas_all cspq,
69 cn_quotas_all quota,
70 cn_payruns_all pr
71 WHERE cspq.quota_id = quota.quota_id
72 AND quota.quota_id > 0
73 AND quota.org_id = cspq.org_id
74 AND pr.pay_period_id = cspq.period_id
75 AND quota.credit_type_id = -1000
76 AND pr.payrun_id = p_payrun_id
77 AND cspq.salesrep_id = p_salesrep_id
78 GROUP BY cspq.salesrep_id,
79 cspq.period_id;
80
81 EXCEPTION
82 WHEN no_data_found THEN
83 l_comm_ptd := 0 ;
84 l_bonus_ptd := 0 ;
85 END;
86
87 UPDATE cn_payment_worksheets_all w
88 SET w.comm_ptd = l_comm_ptd,
89 w.bonus_ptd = l_bonus_ptd,
90 w.comm_due_bb = l_bal
91 WHERE w.salesrep_id = p_salesrep_id
92 AND w.payrun_id = p_payrun_id
93 AND w.quota_id IS NULL ;
94
95 END update_ptd_details ;
96
97 /*
98 Procedure : conc_submit
99 Added for bug 5910965
100 */
101
102 PROCEDURE conc_submit
103 (
104 p_conc_program IN VARCHAR2,
105 p_description IN VARCHAR2,
106 p_logical_batch_id IN NUMBER,
107 p_batch_id IN NUMBER,
108 p_payrun_id IN NUMBER,
109 p_org_id IN cn_payruns.org_id%TYPE,
110 p_params IN conc_params,
111 x_request_id OUT NOCOPY NUMBER
112 ) IS
113 BEGIN
114 fnd_file.put_line(fnd_file.log, 'Conc_submit BatchId = ' || p_batch_id);
115
116 x_request_id := fnd_request.submit_request(application => 'CN',
117 program => p_conc_program,
118 description => p_description,
119 argument1 => p_batch_id,
120 argument2 => p_payrun_id,
121 argument3 => p_logical_batch_id,
122 argument4 => p_org_id
123 );
124 IF x_request_id = 0
125 THEN
126 fnd_file.put_line(fnd_file.log, 'Failed to create concurrent request for (payrun_id,batch_id) = ' || p_payrun_id ||','|| p_batch_id);
127 fnd_file.put_line(fnd_file.log, 'Conc_submit: ' || fnd_message.get);
128 raise fnd_api.G_EXC_ERROR;
129 ELSE
130 fnd_file.put_line(fnd_file.log, 'Concurrent request, ID = ' || x_request_id || ', for (payrun_id,batch_id) = ' || p_payrun_id ||','|| p_batch_id );
131 END IF;
132
133 EXCEPTION
134 WHEN OTHERS THEN
135 fnd_file.put_line(fnd_file.log, 'Conc_submit err:' || SQLERRM);
136 RAISE;
137 END conc_submit;
138
139
140
141 -- ===========================================================================
142 -- Procedure : get_pay_rec_period_ids
143 -- Description : This procedure is used to get pay period id and recover period id given
144 -- pay_interval_type_id and recoverable_interval_type_id.
145 -- Added for bug 2776847 by jjhuang.
146 -- Calls :
147 -- ===========================================================================
148 PROCEDURE get_pay_rec_period_ids
149 (
150 p_period_id IN cn_period_statuses.period_id%TYPE,
151 p_quarter_num IN cn_period_statuses.quarter_num%TYPE,
152 p_period_year IN cn_period_statuses.period_year%TYPE,
153 p_pay_interval_type_id IN cn_pmt_plans.pay_interval_type_id%TYPE,
154 p_recoverable_interval_type_id IN cn_pmt_plans.recoverable_interval_type_id%TYPE,
155 x_pay_period_id OUT NOCOPY cn_pmt_plans.pay_interval_type_id%TYPE,
156 x_rec_period_id OUT NOCOPY cn_pmt_plans.recoverable_interval_type_id%TYPE,
157 --R12
158 p_org_id IN cn_payruns.org_id%TYPE
159 ) IS
160 CURSOR get_max_period_id_in_qtr(p_quarter_num cn_period_statuses.quarter_num%TYPE, p_period_year cn_period_statuses.period_year%TYPE) IS
161 SELECT MAX(p.period_id) max_period_id
162 FROM cn_period_statuses p,
163 cn_period_types pt
164 WHERE p.quarter_num = p_quarter_num
165 AND p.period_year = p_period_year
166 AND p.period_type = pt.period_type
167 AND pt.period_type_id = 0
168 --R12
169 AND p.org_id = p_org_id
170 AND pt.org_id = p_org_id;
171
172 CURSOR get_max_period_id_in_yr(p_period_year cn_period_statuses.period_year%TYPE) IS
173 SELECT MAX(p.period_id) max_period_id
174 FROM cn_period_statuses p,
175 cn_period_types pt
176 WHERE period_year = p_period_year
177 AND p.period_type = pt.period_type
178 AND pt.period_type_id = 0
179 --R12
180 AND p.org_id = p_org_id
181 AND pt.org_id = p_org_id;
182
183 l_pay_period_id cn_pmt_plans.pay_interval_type_id%TYPE;
184 l_rec_period_id cn_pmt_plans.recoverable_interval_type_id%TYPE;
185 BEGIN
186 IF p_pay_interval_type_id = -1000 --pay interval is period
187 THEN
188 l_pay_period_id := p_period_id;
189 ELSIF p_pay_interval_type_id = -1001 --pay interval is quarter
190 THEN
191 FOR rec IN get_max_period_id_in_qtr(p_quarter_num, p_period_year)
192 LOOP
193 l_pay_period_id := rec.max_period_id;
194 END LOOP;
195 ELSIF p_pay_interval_type_id = -1002 --pay interval is year
196 THEN
197 FOR rec IN get_max_period_id_in_yr(p_period_year)
198 LOOP
199 l_pay_period_id := rec.max_period_id;
200 END LOOP;
201 END IF;
202
203 IF p_recoverable_interval_type_id = -1000 --recover interval is period
204 THEN
205 l_rec_period_id := p_period_id;
206 ELSIF p_recoverable_interval_type_id = -1001 --recover interval is quarter
207 THEN
208 FOR rec IN get_max_period_id_in_qtr(p_quarter_num, p_period_year)
209 LOOP
210 l_rec_period_id := rec.max_period_id;
211 END LOOP;
212 ELSIF p_recoverable_interval_type_id = -1002 --recover interval is year
213 THEN
214 FOR rec IN get_max_period_id_in_yr(p_period_year)
215 LOOP
216 l_rec_period_id := rec.max_period_id;
217 END LOOP;
218 END IF;
219
220 x_pay_period_id := l_pay_period_id;
221 x_rec_period_id := l_rec_period_id;
222 END get_pay_rec_period_ids;
223
224 -- ===========================================================================
225 -- Procedure : reset_payrun_id
226 -- Description : This procedure is used to Reset payrun_id to NULL in
227 -- cn_payment_transactions for PMTPLN_REC, COMMISSION and BONUS
228 -- to be included in the next payrun.
229 -- Added for bug 2776847 by jjhuang.
230 -- Calls :
231 -- ===========================================================================
232 PROCEDURE reset_payrun_id
233 (
234 p_payrun_id IN cn_payruns.payrun_id%TYPE,
235 p_salesrep_id IN cn_salesreps.salesrep_id%TYPE,
236 p_incentive_type IN cn_payruns.incentive_type_code%TYPE,
237 p_payment_group_code IN cn_pmt_plans.payment_group_code%TYPE
238 ) IS
239 BEGIN
240 IF p_incentive_type = 'ALL'
241 OR p_incentive_type IS NULL
242 THEN
243 UPDATE cn_payment_transactions ptrx
244 SET payrun_id = NULL,
245 last_update_date = SYSDATE,
246 last_updated_by = fnd_global.user_id,
247 last_update_login = fnd_global.login_id
248 WHERE ptrx.payrun_id = p_payrun_id
249 AND ptrx.credited_salesrep_id = p_salesrep_id
250 AND ptrx.incentive_type_code IN ('PMTPLN_REC', 'COMMISSION', 'BONUS')
251 AND EXISTS (SELECT 1
252 FROM cn_quotas_all q
253 WHERE q.quota_id = ptrx.quota_id
254 AND q.payment_group_code = p_payment_group_code);
255 ELSE
256 UPDATE cn_payment_transactions ptrx
257 SET payrun_id = NULL,
258 last_update_date = SYSDATE,
259 last_updated_by = fnd_global.user_id,
260 last_update_login = fnd_global.login_id
261 WHERE ptrx.payrun_id = p_payrun_id
262 AND ptrx.credited_salesrep_id = p_salesrep_id
263 AND ptrx.incentive_type_code IN ('PMTPLN_REC', decode(p_incentive_type, 'COMMISSION', 'COMMISSION', 'BONUS', 'BONUS'))
264 AND EXISTS (SELECT 1
265 FROM cn_quotas_all q
266 WHERE q.quota_id = ptrx.quota_id
267 AND q.payment_group_code = p_payment_group_code);
268 END IF;
269 END reset_payrun_id;
270
271 -- ===========================================================================
272 -- Procedure : give_min_as_pmt_plan
273 -- Description : This procedure is used to give the minimum amount as a payment
274 -- plan amount when it's a pay period, but not a recover period
275 -- when pay against commission is 'N'.
276 -- Added for bug 2776847 by jjhuang.
277 -- Calls :
278 -- ===========================================================================
279 PROCEDURE give_min_as_pmt_plan
280 (
281 p_min IN cn_pmt_plans.minimum_amount%TYPE,
282 p_min_rec_flag IN cn_pmt_plans.min_rec_flag%TYPE,
283 x_pmt_amount_adj_rec OUT NOCOPY NUMBER,
284 x_pmt_amount_adj_nrec OUT NOCOPY NUMBER
285 ) IS
286 l_pmt_amount_adj_rec NUMBER := 0;
287 l_pmt_amount_adj_nrec NUMBER := 0;
288 BEGIN
289 --minimum calculation
290 IF (p_min IS NOT NULL)
291 THEN
292 IF p_min_rec_flag = 'Y'
293 THEN
294 l_pmt_amount_adj_rec := p_min;
295 ELSE
296 l_pmt_amount_adj_nrec := p_min;
297 END IF;
298 END IF;
299
300 x_pmt_amount_adj_rec := l_pmt_amount_adj_rec;
301 x_pmt_amount_adj_nrec := l_pmt_amount_adj_nrec;
302 END give_min_as_pmt_plan;
303
304 -- ===========================================================================
305 -- Procedure : get_start_and_end_dates
306 -- Description : This procedure is used to get the start date and end date of
307 -- a interval. An interval could be period, quarter or year
308 -- depending on p_interval_type_id.
309 -- Added for bug 2776847 by jjhuang.
310 -- Calls :
311 -- ===========================================================================
312 PROCEDURE get_start_and_end_dates
313 (
314 p_interval_type_id IN NUMBER,
315 p_period_set_id IN cn_period_statuses.period_set_id%TYPE,
316 p_period_type_id IN cn_period_statuses.period_type_id%TYPE,
317 p_period_year IN cn_period_statuses.period_year%TYPE,
318 p_quarter_num IN cn_period_statuses.period_year%TYPE,
319 p_start_date IN cn_period_statuses.start_date%TYPE,
320 p_end_date IN cn_period_statuses.end_date%TYPE,
321 x_interval_start_date OUT NOCOPY cn_period_statuses.start_date%TYPE,
322 x_interval_end_date OUT NOCOPY cn_period_statuses.end_date%TYPE,
323 --R12
324 p_org_id IN cn_payruns.org_id%TYPE
325 ) IS
326 l_interval_start_date cn_period_statuses.start_date%TYPE;
327 l_interval_end_date cn_period_statuses.end_date%TYPE;
328 BEGIN
329 IF p_interval_type_id = -1000
330 THEN
331 --period
332 l_interval_start_date := p_start_date;
333 l_interval_end_date := p_end_date;
334 ELSIF p_interval_type_id = -1001
335 THEN
336 --quarter, get the start, end dates of the quarter.
337 SELECT MIN(start_date),
338 MAX(end_date)
339 INTO l_interval_start_date,
340 l_interval_end_date
341 FROM cn_period_statuses
342 WHERE period_set_id = p_period_set_id
343 AND period_type_id = p_period_type_id
344 AND quarter_num = p_quarter_num
345 AND period_year = p_period_year
346 --R12
347 AND org_id = p_org_id;
348 ELSIF p_interval_type_id = -1002
349 THEN
350 SELECT MIN(start_date),
351 MAX(end_date)
352 INTO l_interval_start_date,
353 l_interval_end_date
354 FROM cn_period_statuses
355 WHERE period_set_id = p_period_set_id
356 AND period_type_id = p_period_type_id
357 AND period_year = p_period_year
358 --R12
359 AND org_id = p_org_id;
360 END IF;
361
362 x_interval_start_date := l_interval_start_date;
363 x_interval_end_date := l_interval_end_date;
364 END get_start_and_end_dates;
365
366 -- ===========================================================================
367 -- Procedure : distribute_pmt_plan_amount
368 -- Description : This procedure is used to distribute payment plan amount evenly on
369 -- all quotas on a pay interval basis. A pay interval could be
370 -- a period, or a quarter or a year depending on the pay interval
371 -- type that associates with the payment plan.
372 -- Taken out from original calculate_totals procedure for bug 2776847 by jjhuang.
373 -- Calls :
374 -- ===========================================================================
375 PROCEDURE distribute_pmt_plan_amount
376 (
377 p_salesrep_id IN cn_salesreps.salesrep_id%TYPE,
378 p_pmt_amount_adj_rec IN NUMBER,
379 p_pmt_amount_adj_nrec IN NUMBER,
380 p_payment_group_code IN cn_srp_pmt_plans_v.payment_group_code%TYPE,
381 p_period_id IN cn_payruns.pay_period_id%TYPE,
382 p_incentive_type IN cn_quotas.incentive_type_code%TYPE,
383 x_calc_rec_tbl IN OUT NOCOPY calc_rec_tbl_type,
384 --R12
385 p_org_id IN cn_payruns.org_id%TYPE
386 ) IS
387 CURSOR get_pe_pg_count(p_payment_group_code VARCHAR2) IS
388 SELECT COUNT(DISTINCT cnq.quota_id) num_pe
389 FROM cn_srp_period_quotas cspq,
390 cn_quotas_all cnq
391 WHERE cnq.payment_group_code = p_payment_group_code
392 AND cspq.quota_id = cnq.quota_id
393 AND cnq.credit_type_id = -1000
394 AND cspq.salesrep_id = p_salesrep_id
395 AND cspq.period_id = p_period_id
396 --R12
397 AND cspq.org_id = cnq.org_id
398 AND cspq.org_id = p_org_id
399 --bug 3107646, issue 4
400 AND cnq.incentive_type_code =
401 decode(nvl(p_incentive_type, cnq.incentive_type_code), 'COMMISSION', 'COMMISSION', 'BONUS', 'BONUS', cnq.incentive_type_code)
402 -- BUG 3140343 Payee design
403 AND cspq.quota_id NOT IN (SELECT spayee.quota_id
404 FROM cn_srp_payee_assigns spayee,
405 cn_period_statuses ps
406 WHERE (spayee.salesrep_id = p_salesrep_id OR spayee.payee_id = p_salesrep_id)
407 AND ps.period_id = p_period_id
408 AND ps.end_date >= spayee.start_date
409 --R12
410 AND spayee.org_id = ps.org_id
411 AND spayee.org_id = p_org_id
412 AND ps.start_date <= nvl(spayee.end_date, ps.end_date));
413 CURSOR get_pe_pg(p_payment_group_code VARCHAR2) IS
414 SELECT DISTINCT cnq.quota_id quota_id
415 FROM cn_srp_period_quotas cspq,
416 cn_quotas_all cnq
417 WHERE cnq.payment_group_code = p_payment_group_code
418 AND cspq.quota_id = cnq.quota_id
419 AND cnq.credit_type_id = -1000
420 AND cspq.salesrep_id = p_salesrep_id
421 AND cspq.period_id = p_period_id
422 --R12
423 AND cspq.org_id = cnq.org_id
424 AND cspq.org_id = p_org_id
425 --bug 3107646, issue 4
426 AND cnq.incentive_type_code =
427 decode(nvl(p_incentive_type, cnq.incentive_type_code), 'COMMISSION', 'COMMISSION', 'BONUS', 'BONUS', cnq.incentive_type_code)
428 -- BUG 3140343 Payee design
429 AND cspq.quota_id NOT IN (SELECT spayee.quota_id
430 FROM cn_srp_payee_assigns spayee,
431 cn_period_statuses ps
432 WHERE (spayee.salesrep_id = p_salesrep_id OR spayee.payee_id = p_salesrep_id)
433 AND ps.period_id = p_period_id
434 AND ps.end_date >= spayee.start_date
435 --R12
436 AND spayee.org_id = ps.org_id
437 AND spayee.org_id = p_org_id
438 AND ps.start_date <= nvl(spayee.end_date, ps.end_date));
439 l_pe_count NUMBER := 0;
440 BEGIN
441 -- If payment plan adjustments exist, determine how to distribute them
442 IF p_pmt_amount_adj_rec <> 0
443 OR p_pmt_amount_adj_nrec <> 0
444 THEN
445 -- How many plan elements assigned to this rep have this payment group code?
446 FOR rec IN get_pe_pg_count(p_payment_group_code)
447 LOOP
448 l_pe_count := rec.num_pe;
449 END LOOP;
450
451 -- Update rec and nrec amount for each worksheet
452 -- that is created for pe that is assigned the current payment group code
453 FOR pe IN get_pe_pg(p_payment_group_code)
454 LOOP
455 FOR i IN x_calc_rec_tbl.FIRST .. x_calc_rec_tbl.LAST
456 LOOP
457 IF x_calc_rec_tbl(i).quota_id = pe.quota_id
458 THEN
459 x_calc_rec_tbl(i).pmt_amount_adj_rec := nvl(x_calc_rec_tbl(i).pmt_amount_adj_rec, 0) + p_pmt_amount_adj_rec / l_pe_count;
460 x_calc_rec_tbl(i).pmt_amount_adj_nrec := nvl(x_calc_rec_tbl(i).pmt_amount_adj_nrec, 0) + p_pmt_amount_adj_nrec / l_pe_count;
461 END IF;
462 END LOOP;
463 END LOOP; -- end of loop to fetch plan elements for current payment group code
464 END IF; --end of p_pmt_amount_adj_rec <> 0 OR p_pmt_amount_adj_nrec <> 0
465 END distribute_pmt_plan_amount;
466
467 -- ===========================================================================
468 -- Procedure : proc_pmt_trans_by_pe
469 -- Description : This procedure is used to process (sum up) all payment transactions by pe.
470 -- Taken out from original calculate_totals procedure for bug 2776847 by jjhuang.
471 -- : Comments for Bug 3198445 by jjhuang:
472 -- : 1. The following case does not exist for bug 3198445 by jjhuang:
473 -- p_payment_group_code IS NOT NULL AND p_applied_pgc.COUNT = 0
474 -- : 2. When p_payment_group_code is NULL, it includes two cases:
475 -- i. No payment plan assignments for this salesrep at this period.
476 -- ii. Post action for 1 to n-1 paymenet group codes applied to this srp at the period
477 -- where there are n payment group codes at the current period.
478 -- iii. If n payment group codes applied already given there are n payment group codes,
479 -- do nothing.
480 -- Calls :
481 -- ===========================================================================
482 PROCEDURE proc_pmt_trans_by_pe
483 (
484 p_salesrep_id IN cn_payment_worksheets.salesrep_id%TYPE,
485 p_incentive_type IN cn_payruns.incentive_type_code%TYPE,
486 p_payrun_id IN cn_payruns.payrun_id%TYPE,
487 p_payment_group_code IN cn_pmt_plans.payment_group_code%TYPE, --bug 3175375 by jjhuang.
488 p_applied_pgc IN dbms_sql.varchar2_table, --bug 3198445 by jjhuang.
489 x_calc_rec_tbl IN OUT NOCOPY calc_rec_tbl_type,
490 --R12
491 p_org_id IN cn_payruns.org_id%TYPE
492 ) IS
493 -- 2/7/03 AC Bug 2792037 get list of PE from cn_payment_transactions
494 -- 2/12/03 AC Bug 2800968 union all to old cursor(against cn_srp_periods)
495 -- to take care srp with no transaction but want to apply pmt plan
496 -- Bug 3140343 : Payee Design.
497 -- Bug 3198445 by jjhuang: Added payment_group_code for cursor get_srp_pe
498 CURSOR get_srp_pe IS
499 --Added cn_quotas_all for bug 3175375 by jjhuang.
500 SELECT DISTINCT v.quota_id,
501 v.payment_group_code
502 FROM (SELECT cnpt.quota_id,
503 cq.payment_group_code
504 FROM cn_payment_transactions cnpt,
505 cn_quotas_all cq
506 WHERE cnpt.credit_type_id = g_credit_type_id
507 AND cnpt.credited_salesrep_id = p_salesrep_id
508 AND cnpt.payrun_id = p_payrun_id
509 AND ((cnpt.incentive_type_code NOT IN ('COMMISSION', 'BONUS')) OR
510 (cnpt.incentive_type_code = nvl(p_incentive_type, cnpt.incentive_type_code)))
511 AND cnpt.quota_id = cq.quota_id
512 AND cq.payment_group_code = nvl(p_payment_group_code, cq.payment_group_code)
513 UNION ALL
514 SELECT cnsp.quota_id,
515 cnq.payment_group_code
516 FROM cn_srp_period_quotas cnsp,
517 cn_quotas_all cnq,
518 cn_payruns cnp
519 WHERE cnsp.salesrep_id = p_salesrep_id
520 AND cnq.credit_type_id = g_credit_type_id
521 AND cnq.incentive_type_code = nvl(p_incentive_type, cnq.incentive_type_code)
522 AND cnp.payrun_id = p_payrun_id
523 AND cnp.pay_period_id = cnsp.period_id
524 AND cnsp.quota_id = cnq.quota_id
525 AND cnq.payment_group_code = nvl(p_payment_group_code, cnq.payment_group_code)
526 AND NOT EXISTS (
527 -- separate queries for performance reasons. merge cartesian reported
528 SELECT 1
529 FROM cn_srp_payee_assigns_all spayee,
530 cn_period_statuses_all ps
531 WHERE (spayee.salesrep_id = p_salesrep_id)
532 AND ps.period_id = cnp.pay_period_id
533 AND ps.end_date >= spayee.start_date
534 AND ps.org_id = p_org_id
535 AND cnsp.quota_id = spayee.quota_id
536 AND ps.start_date <= nvl(spayee.end_date, ps.end_date)
537 UNION ALL
538 SELECT 1
539 FROM cn_srp_payee_assigns_all spayee,
540 cn_period_statuses_all ps
541 WHERE spayee.payee_id = p_salesrep_id
542 AND ps.period_id = cnp.pay_period_id
543 AND ps.end_date >= spayee.start_date
544 AND ps.org_id = p_org_id
545 AND cnsp.quota_id = spayee.quota_id
546 AND ps.start_date <= nvl(spayee.end_date, ps.end_date))) v;
547
548 -- Bug 3198445 by jjhuang: get distinct payment group code count.
549 --Added cn_quotas_all for bug 3175375 by jjhuang.
550 CURSOR get_pgc_count IS
551 SELECT COUNT(DISTINCT v.payment_group_code) pgc_count
552 FROM (SELECT cnpt.quota_id,
553 cq.payment_group_code
554 FROM cn_payment_transactions cnpt,
555 cn_quotas_all cq
556 WHERE cnpt.credit_type_id = g_credit_type_id
557 AND cnpt.credited_salesrep_id = p_salesrep_id
558 AND cnpt.payrun_id = p_payrun_id
559 AND ((cnpt.incentive_type_code NOT IN ('COMMISSION', 'BONUS')) OR
560 (cnpt.incentive_type_code = nvl(p_incentive_type, cnpt.incentive_type_code)))
561 AND cnpt.quota_id = cq.quota_id
562 AND cq.payment_group_code = nvl(p_payment_group_code, cq.payment_group_code)
563 UNION ALL
564 SELECT cnsp.quota_id,
565 cnq.payment_group_code
566 FROM cn_srp_period_quotas cnsp,
567 cn_quotas_all cnq,
568 cn_payruns cnp
569 WHERE cnsp.salesrep_id = p_salesrep_id
570 AND cnq.credit_type_id = g_credit_type_id
571 AND cnq.incentive_type_code = nvl(p_incentive_type, cnq.incentive_type_code)
572 AND cnp.payrun_id = p_payrun_id
573 AND cnp.pay_period_id = cnsp.period_id
574 AND cnsp.quota_id = cnq.quota_id
575 AND cnq.payment_group_code = nvl(p_payment_group_code, cnq.payment_group_code)
576 AND NOT EXISTS (
577 -- separate queries for performance reasons. merge cartesian reported
578 SELECT 1
579 FROM cn_srp_payee_assigns_all spayee,
580 cn_period_statuses_all ps
581 WHERE (spayee.salesrep_id = p_salesrep_id)
582 AND ps.period_id = cnp.pay_period_id
583 AND ps.end_date >= spayee.start_date
584 AND ps.org_id = p_org_id
585 AND cnsp.quota_id = spayee.quota_id
586 AND ps.start_date <= nvl(spayee.end_date, ps.end_date)
587 UNION ALL
588 SELECT 1
589 FROM cn_srp_payee_assigns_all spayee,
590 cn_period_statuses_all ps
591 WHERE spayee.payee_id = p_salesrep_id
592 AND ps.period_id = cnp.pay_period_id
593 AND ps.end_date >= spayee.start_date
594 AND ps.org_id = p_org_id
595 AND cnsp.quota_id = spayee.quota_id
596 AND ps.start_date <= nvl(spayee.end_date, ps.end_date))) v;
597
598 -- remove join to cn_quotas_all since can get quota_id from cnpt
599 -- 03/24/03 -9999 is used in cnupsp2.sql, change to -9990
600 CURSOR get_earnings_total_by_pe(p_quota_id NUMBER) IS
601 -- earnings to populate pmt_amount_calc
602 SELECT nvl(SUM(nvl(cnpt.amount, 0)), 0) pmt_amount_calc,
603 cnpt.quota_id quota_id,
604 0 pmt_amount_recovery,
605 0 pmt_amount_adj,
606 0 held_amount
607 FROM cn_payment_transactions cnpt
608 WHERE cnpt.credited_salesrep_id = p_salesrep_id
609 AND cnpt.incentive_type_code = nvl(p_incentive_type, cnpt.incentive_type_code)
610 AND cnpt.incentive_type_code IN ('COMMISSION', 'BONUS')
611 AND cnpt.payrun_id = p_payrun_id
612 AND nvl(hold_flag, 'N') = 'N'
613 AND nvl(cnpt.quota_id, -9990) = nvl(p_quota_id, -9990)
614 --R12
615 AND cnpt.org_id = p_org_id
616 GROUP BY cnpt.quota_id
617 UNION ALL
618 -- Recovery to populate pmt_amount_recovery
619 SELECT 0 pmt_amount_calc,
620 cnpt.quota_id quota_id,
621 nvl(SUM(nvl(cnpt.amount, 0)), 0) pmt_amount_recovery,
622 0 pmt_amount_adj,
623 0 held_amount
624 FROM cn_payment_transactions cnpt
625 WHERE cnpt.credited_salesrep_id = p_salesrep_id
626 AND cnpt.incentive_type_code = 'PMTPLN_REC'
627 AND cnpt.payrun_id = p_payrun_id
628 AND nvl(cnpt.quota_id, -9990) = nvl(p_quota_id, -9990)
629 --R12
630 AND cnpt.org_id = p_org_id
631 GROUP BY cnpt.quota_id
632 UNION ALL
633 -- to populate manual pay adjustments in pmt_amount_adj
634 SELECT 0 pmt_amount_calc,
635 cnpt.quota_id quota_id,
636 0 pmt_amount_recovery,
637 nvl(SUM(nvl(cnpt.amount, 0)), 0) pmt_amount_adj,
638 0 held_amount
639 FROM cn_payment_transactions cnpt
640 WHERE cnpt.credited_salesrep_id = p_salesrep_id
641 AND cnpt.incentive_type_code IN ('MANUAL_PAY_ADJ')
642 AND cnpt.payrun_id = p_payrun_id
643 AND nvl(cnpt.quota_id, -9990) = nvl(p_quota_id, -9990)
644 --R12
645 AND cnpt.org_id = p_org_id
646 GROUP BY cnpt.quota_id
647 UNION ALL
648 -- to populate control payments in pmt_amount_adj
649 SELECT 0 pmt_amount_calc,
650 cnpt.quota_id quota_id,
651 0 pmt_amount_recovery,
652 nvl(SUM(nvl(cnpt.payment_amount, 0)), 0) - nvl(SUM(nvl(cnpt.amount, 0)), 0) pmt_amount_adj,
653 0 held_amount
654 FROM cn_payment_transactions cnpt
655 WHERE cnpt.credited_salesrep_id = p_salesrep_id
656 AND cnpt.incentive_type_code = nvl(p_incentive_type, cnpt.incentive_type_code)
657 AND cnpt.incentive_type_code IN ('COMMISSION', 'BONUS')
658 AND nvl(cnpt.hold_flag, 'N') = 'N'
659 AND cnpt.payrun_id = p_payrun_id
660 AND nvl(cnpt.quota_id, -9990) = nvl(p_quota_id, -9990)
661 --R12
662 AND cnpt.org_id = p_org_id
663 GROUP BY cnpt.quota_id
664 UNION ALL
665 -- to populate hold in pmt_amount_adj
666 SELECT 0 pmt_amount_calc,
667 cnpt.quota_id quota_id,
668 0 pmt_amount_recovery,
669 0 pmt_amount_adj,
670 nvl(SUM(nvl(cnpt.payment_amount, 0)), 0) held_amount
671 FROM cn_payment_transactions cnpt
672 WHERE cnpt.credited_salesrep_id = p_salesrep_id
673 AND cnpt.incentive_type_code = nvl(p_incentive_type, cnpt.incentive_type_code)
674 AND cnpt.incentive_type_code IN ('COMMISSION', 'BONUS')
675 AND nvl(cnpt.hold_flag, 'N') = 'Y'
676 AND cnpt.payrun_id = p_payrun_id
677 AND nvl(cnpt.quota_id, -9990) = nvl(p_quota_id, -9990)
678 --R12
679 AND cnpt.org_id = p_org_id
680 GROUP BY cnpt.quota_id
681 UNION ALL
682 -- to populate waive recovery in pmt_amount_adj
683 -- changed recovery amount to negative for fix BUG#2545629|
684 SELECT 0 pmt_amount_calc,
685 cnpt.quota_id quota_id,
686 0 pmt_amount_recovery,
687 -nvl(SUM(nvl(cnpt.amount, 0)), 0) pmt_amount_adj,
688 0 held_amount
689 FROM cn_payment_transactions cnpt
690 WHERE cnpt.credited_salesrep_id = p_salesrep_id
691 AND cnpt.incentive_type_code = 'PMTPLN_REC'
692 AND nvl(cnpt.waive_flag, 'N') = 'Y'
693 AND cnpt.payrun_id = p_payrun_id
694 AND nvl(cnpt.quota_id, -9990) = nvl(p_quota_id, -9990)
695 --R12
696 AND cnpt.org_id = p_org_id
697 GROUP BY cnpt.quota_id;
698
699 --local variables
700 l_record_count NUMBER := 0;
701 l_pmt_amount_calc NUMBER := 0;
702 l_pmt_amount_rec NUMBER := 0;
703 l_pmt_amount_adj NUMBER := 0;
704 l_held_amount NUMBER := 0;
705 --variables used for bug 3198445 by jjhuang -begin
706 l_pgc_count NUMBER := 0;
707 l_post_action NUMBER := 0; --0 is false, 1 is true;
708 l_count NUMBER := 0;
709
710 TYPE quotas_rec_type IS RECORD(
711 quota_id cn_quotas.quota_id%TYPE);
712
713 TYPE quotas_rec_tbl_type IS TABLE OF quotas_rec_type INDEX BY BINARY_INTEGER;
714
715 l_quota_tbl quotas_rec_tbl_type;
716 l_quotas_to_apply NUMBER;
717 --variables used for bug 3198445 by jjhuang -end
718 BEGIN
719 -- Bug 3198445 by jjhuang.
720 --Find total number of payment group codes need to be applied.
721 FOR each_row IN get_pgc_count
722 LOOP
723 l_pgc_count := each_row.pgc_count;
724 END LOOP;
725
726 --Post step after applying 1 to n-1 payment group codes out of n payment group codes
727 --for this srp at the current period. For bug 3198445 by jjhuang.
728 --Only create those quotas that are in different payment group codes.
729 IF (p_payment_group_code IS NULL AND p_applied_pgc.COUNT <> 0 AND l_pgc_count <> p_applied_pgc.COUNT)
730 THEN
731 l_post_action := 1;
732 l_quotas_to_apply := 0;
733
734 FOR each_quota IN get_srp_pe
735 LOOP
736 l_count := 0;
737
738 FOR i IN p_applied_pgc.FIRST .. p_applied_pgc.LAST
739 LOOP
740 IF (each_quota.payment_group_code = p_applied_pgc(i))
741 THEN
742 l_count := l_count + 1;
743 END IF;
744 END LOOP;
745
746 IF l_count = 0
747 THEN
748 l_quota_tbl(l_quotas_to_apply).quota_id := each_quota.quota_id;
749 l_quotas_to_apply := l_quotas_to_apply + 1;
750
751 --payment group codes already applied before, do nothing.
752 ELSIF l_count > 0
753 THEN
754 NULL;
755 END IF;
756 END LOOP;
757 END IF;
758
759 -- Bug 3140343 : Payee Design.
760 l_record_count := x_calc_rec_tbl.COUNT;
761
762 IF l_post_action = 1
763 THEN
764 FOR i IN l_quota_tbl.FIRST .. l_quota_tbl.LAST
765 LOOP
766 x_calc_rec_tbl(l_record_count).quota_id := NULL;
767 x_calc_rec_tbl(l_record_count).pmt_amount_adj_rec := 0;
768 x_calc_rec_tbl(l_record_count).pmt_amount_adj_nrec := 0;
769 x_calc_rec_tbl(l_record_count).pmt_amount_calc := 0;
770 x_calc_rec_tbl(l_record_count).pmt_amount_rec := 0;
771 x_calc_rec_tbl(l_record_count).pmt_amount_ctr := 0;
772 x_calc_rec_tbl(l_record_count).held_amount := 0;
773 l_pmt_amount_calc := 0;
774 l_pmt_amount_rec := 0;
775 l_pmt_amount_adj := 0;
776 l_held_amount := 0;
777
778 FOR earnings IN get_earnings_total_by_pe(l_quota_tbl(i).quota_id)
779 LOOP
780 l_pmt_amount_calc := l_pmt_amount_calc + earnings.pmt_amount_calc;
781 l_pmt_amount_rec := l_pmt_amount_rec + earnings.pmt_amount_recovery;
782 l_pmt_amount_adj := l_pmt_amount_adj + earnings.pmt_amount_adj;
783 l_held_amount := l_held_amount + earnings.held_amount;
784 END LOOP;
785
786 x_calc_rec_tbl(l_record_count).quota_id := l_quota_tbl(i).quota_id;
787 x_calc_rec_tbl(l_record_count).pmt_amount_calc := l_pmt_amount_calc;
788 x_calc_rec_tbl(l_record_count).pmt_amount_rec := l_pmt_amount_rec;
789 x_calc_rec_tbl(l_record_count).pmt_amount_ctr := l_pmt_amount_adj;
790 x_calc_rec_tbl(l_record_count).held_amount := l_held_amount;
791 l_record_count := l_record_count + 1;
792 END LOOP;
793 -- This elsif branch includes the following cases for bug 3198445 by jjhuang.
794 -- 1. No payment plans for this srp for this period. That is:
795 -- p_payment_group_code is NULL AND p_applied_pgc.COUNT = 0
796 -- 2. Apply the current payment group code to this srp. That is:
797 -- p_payment_group_code IS NOT NULL AND p_applied_pgc.COUNT <> 0
798 ELSIF ((l_post_action = 0 AND p_payment_group_code IS NULL AND p_applied_pgc.COUNT = 0) OR
799 (l_post_action = 0 AND p_payment_group_code IS NOT NULL AND p_applied_pgc.COUNT <> 0))
800 THEN
801 FOR each_quota IN get_srp_pe
802 LOOP
803 x_calc_rec_tbl(l_record_count).quota_id := NULL;
804 x_calc_rec_tbl(l_record_count).pmt_amount_adj_rec := 0;
805 x_calc_rec_tbl(l_record_count).pmt_amount_adj_nrec := 0;
806 x_calc_rec_tbl(l_record_count).pmt_amount_calc := 0;
807 x_calc_rec_tbl(l_record_count).pmt_amount_rec := 0;
808 x_calc_rec_tbl(l_record_count).pmt_amount_ctr := 0;
809 x_calc_rec_tbl(l_record_count).held_amount := 0;
810 l_pmt_amount_calc := 0;
811 l_pmt_amount_rec := 0;
812 l_pmt_amount_adj := 0;
813 l_held_amount := 0;
814
815 FOR earnings IN get_earnings_total_by_pe(each_quota.quota_id)
816 LOOP
817 l_pmt_amount_calc := l_pmt_amount_calc + earnings.pmt_amount_calc;
818 l_pmt_amount_rec := l_pmt_amount_rec + earnings.pmt_amount_recovery;
819 l_pmt_amount_adj := l_pmt_amount_adj + earnings.pmt_amount_adj;
820 l_held_amount := l_held_amount + earnings.held_amount;
821 END LOOP;
822
823 x_calc_rec_tbl(l_record_count).quota_id := each_quota.quota_id;
824 x_calc_rec_tbl(l_record_count).pmt_amount_calc := l_pmt_amount_calc;
825 x_calc_rec_tbl(l_record_count).pmt_amount_rec := l_pmt_amount_rec;
826 x_calc_rec_tbl(l_record_count).pmt_amount_ctr := l_pmt_amount_adj;
827 x_calc_rec_tbl(l_record_count).held_amount := l_held_amount;
828 l_record_count := l_record_count + 1;
829 END LOOP;
830 -- This elsif branch includes the following case for bug 3198445 by jjhuang.
831 -- All the payment group codes have been applied to this srp. Do nothing.
832 ELSIF (l_post_action = 0 AND p_payment_group_code IS NULL AND p_applied_pgc.COUNT <> 0 AND l_pgc_count = p_applied_pgc.COUNT)
833 THEN
834 NULL;
835 END IF;
836 END proc_pmt_trans_by_pe;
837
838 -- ===========================================================================
839 -- Procedure : Calculate_totals
840 -- Description : This procedure is used to calculate totals from payment plans.
841 -- This procedure takes care of all cases which are accessiable from UI.
842 -- All other cases will be blocked by UI.
843 --
844 -- PayAgainstCommission PayInterval RecoveryInterval
845 -- Y P P
846 -- Y Q Q
847 -- Y Y Y
848 -- N P P
849 -- N Q Q
850 -- N Y Y
851 -- N P Q
852 -- N P Y
853 -- N Q Y
854 --
855 -- P stands for period, Q stands for quarter and Y stands for Year in
856 -- PayInterval and RecoveryInterval.
857 -- Y stands for yes and N stands for no in PayAgainstCommission.
858 --
859 -- The logic in this procedure is:
860 --
861 -- IF payment plans exist
862 -- THEN
863 -- IF ( srp_pmt_plan.pay_against_commission in ('Y', 'N')
864 -- AND l_period_id = l_pay_period_id
865 -- AND l_period_id = l_rec_period_id )
866 -- THEN
867 -- do payment plans;
868 -- ELSE
869 -- IF ( srp_pmt_plan.pay_against_commission = 'N'
870 -- AND l_period_id = l_pay_period_id
871 -- AND l_period_id <> l_rec_period_id )
872 -- THEN
873 -- give min as payment plan;
874 -- ELSIF ( srp_pmt_plan.pay_against_commission = 'N'
875 -- AND l_period_id <> l_pay_period_id )
876 -- THEN
877 -- reset payrun_id to NULL to be included in the next payrun calculation;
878 -- ELSIF ( srp_pmt_plan.pay_against_commission = 'Y'
879 -- AND l_period_id <> l_rec_period_id )
880 -- THEN
881 -- pay earnings;
882 -- END IF;
883 -- END IF;
884 -- END IF;
885 --
886 -- IF no payment plans
887 -- THEN
888 -- pay earnings;
889 -- END IF;
890 -- Calls :
891 -- ===========================================================================
892 PROCEDURE calculate_totals
893 (
894 p_salesrep_id IN cn_payment_worksheets.salesrep_id%TYPE,
895 p_period_id IN cn_payruns.pay_period_id%TYPE,
896 p_incentive_type IN cn_payruns.incentive_type_code%TYPE,
897 p_payrun_id IN cn_payruns.payrun_id%TYPE,
898 x_calc_rec_tbl IN OUT NOCOPY calc_rec_tbl_type,
899 --R12
900 p_org_id IN cn_payruns.org_id%TYPE
901 ) IS
902 CURSOR get_earnings_total(p_payment_group_code VARCHAR2) IS
903 SELECT SUM(cnpt.payment_amount) payment_amount
904 FROM cn_payment_transactions cnpt,
905 cn_quotas_all cnq
906 WHERE cnpt.credited_salesrep_id = p_salesrep_id
907 AND cnpt.payrun_id = p_payrun_id
908 AND cnpt.quota_id = cnq.quota_id
909 AND nvl(hold_flag, 'N') = 'N'
910 AND nvl(waive_flag, 'N') = 'N'
911 AND cnq.payment_group_code = p_payment_group_code
912 AND cnpt.incentive_type_code <> 'PMTPLN';
913
914 TYPE srppmtplncurtype IS REF CURSOR;
915
916 srp_pmt_plan_cur srppmtplncurtype;
917 srp_pmt_plan cn_srp_pmt_plans_v%ROWTYPE;
918 l_stmt VARCHAR2(4000);
919
920 -- Cursor to get the Periods
921 CURSOR get_prd_statuses(p_period_id NUMBER) IS
922 SELECT quarter_num,
923 period_year,
924 period_set_id,
925 period_type_id,
926 start_date,
927 end_date
928 FROM cn_period_statuses
929 WHERE period_id = p_period_id
930 --R12
931 AND org_id = p_org_id;
932
933 l_get_prd_statuses get_prd_statuses%ROWTYPE;
934
935 CURSOR get_qtr_sdate(p_period_set_id NUMBER, p_period_type_id NUMBER, p_period_year NUMBER, p_quarter_num NUMBER) IS
936 SELECT MIN(start_date)
937 FROM cn_period_statuses
938 WHERE period_set_id = p_period_set_id
939 AND period_type_id = p_period_type_id
940 AND period_year = p_period_year
941 AND quarter_num = p_quarter_num
942 --R12
943 AND org_id = p_org_id;
944
945 CURSOR get_qtr_edate(p_period_set_id NUMBER, p_period_type_id NUMBER, p_period_year NUMBER, p_quarter_num NUMBER) IS
946 SELECT MAX(end_date)
947 FROM cn_period_statuses
948 WHERE period_set_id = p_period_set_id
949 AND period_type_id = p_period_type_id
950 AND period_year = p_period_year
951 AND quarter_num = p_quarter_num
952 --R12
953 AND org_id = p_org_id;
954
955 CURSOR get_year_sdate(p_period_set_id NUMBER, p_period_type_id NUMBER, p_period_year NUMBER) IS
956 SELECT MIN(start_date)
957 FROM cn_period_statuses
958 WHERE period_set_id = p_period_set_id
959 AND period_type_id = p_period_type_id
960 AND period_year = p_period_year
961 AND org_id = p_org_id;
962
963 CURSOR get_year_edate(p_period_set_id NUMBER, p_period_type_id NUMBER, p_period_year NUMBER) IS
964 SELECT MAX(end_date)
965 FROM cn_period_statuses
966 WHERE period_set_id = p_period_set_id
967 AND period_type_id = p_period_type_id
968 AND period_year = p_period_year
969 AND org_id = p_org_id;
970
971 -- Bug 2875120/2892822 : combine 2 cursor
972 -- get the amount paid at the payment group code level
973 CURSOR get_itd_amount_paid(p_period_set_id NUMBER, p_period_type_id NUMBER, p_interval_sdate DATE, p_interval_edate DATE, p_pg_code cn_pmt_plans.payment_group_code%TYPE) IS
974 SELECT nvl(SUM(balance1_dtd - balance1_ctd), 0) payment
975 FROM cn_srp_periods csp,
976 cn_quotas_all q,
977 cn_period_statuses ps
978 WHERE csp.period_id = ps.period_id
979 AND ps.period_set_id = p_period_set_id
980 AND ps.period_type_id = p_period_type_id
981 AND ps.start_date >= p_interval_sdate
982 AND ps.end_date <= p_interval_edate
983 AND csp.salesrep_id = p_salesrep_id
984 AND csp.credit_type_id = g_credit_type_id
985 AND csp.quota_id = q.quota_id
986 AND q.payment_group_code = p_pg_code
987 AND csp.org_id = q.org_id
988 AND q.org_id = ps.org_id
989 AND ps.org_id = p_org_id;
990
991 --local variables
992 -- variable to hold pre - pmt plan value
993 l_net_pre_pmtplan NUMBER := 0;
994 l_itd_paid NUMBER := 0;
995 l_pe_count NUMBER := 0;
996 l_pmt_amount_adj_rec NUMBER := 0;
997 l_pmt_amount_adj_nrec NUMBER := 0;
998 -- Variables for determining if current period is eligible for
999 -- payment plan adjustments
1000 l_period_set_id NUMBER;
1001 l_period_type_id NUMBER;
1002 l_start_date DATE;
1003 l_end_date DATE;
1004 l_qtr_num NUMBER;
1005 l_year_num NUMBER;
1006 l_period_id NUMBER;
1007 l_interval_sdate DATE;
1008 l_interval_edate DATE;
1009 l_count NUMBER;
1010 l_srp_earnings NUMBER := 0;
1011 l_srp_recovery NUMBER := 0;
1012 l_total_amount NUMBER := 0;
1013 l_amount NUMBER := 0;
1014 l_earnings NUMBER := 0;
1015 l_recovery NUMBER := 0;
1016 l_ctr_amount NUMBER := 0;
1017 --variables added for bug 2776847 by jjhuang
1018 l_pay_period_id cn_period_statuses.period_id%TYPE;
1019 l_rec_period_id cn_period_statuses.period_id%TYPE;
1020 l_pmt_plan_count NUMBER := 0;
1021 -- varialve added for Bug 3140343
1022 l_ispayee NUMBER := 0;
1023 --Bug 3198445 by jjhuang
1024 l_applied_pgc dbms_sql.varchar2_table;
1025 l_applied_pgc_count NUMBER;
1026 BEGIN
1027 --
1028 -- get quarter Number and Year Number
1029 --
1030 OPEN get_prd_statuses(p_period_id);
1031
1032 FETCH get_prd_statuses
1033 INTO l_get_prd_statuses;
1034
1035 CLOSE get_prd_statuses;
1036
1037 -- Build dynamic sql for cursor
1038 l_stmt := 'SELECT v.pay_interval_type_id, v.recoverable_interval_type_id, ' || 'v.pay_against_commission, v.payment_group_code, v.minimum_amount, ' ||
1039 'v.maximum_amount, v.min_rec_flag, v.max_rec_flag, v.name ' || 'FROM ' || '(SELECT ' || ' cnpp.pay_interval_type_id,' ||
1040 ' cnpp.recoverable_interval_type_id,' || ' nvl(cnpp.pay_against_commission, ''Y'') pay_against_commission,' || ' cnpp.payment_group_code,' ||
1041 ' cspp.minimum_amount,' || ' cnpp.min_rec_flag,' || ' cnpp.max_rec_flag,' || ' cspp.maximum_amount,' || ' cnps.period_id,' ||
1042 ' cspp.salesrep_id,' || ' cnps.start_date prd_start_date,' || ' cnps.end_date prd_end_date,' || ' cnpp.name,' ||
1043 ' ROW_NUMBER() over (PARTITION BY cnpp.payment_group_code' || ' ORDER BY cspp.start_date DESC) AS row_nums ,' || ' cnpp.credit_type_id' ||
1044 ' FROM cn_srp_pmt_plans cspp,cn_pmt_plans cnpp,cn_period_statuses cnps ' || ' WHERE ' || ' cspp.salesrep_id = :p_salesrep_id' ||
1045 ' AND cnpp.pmt_plan_id = cspp.pmt_plan_id ' || ' AND cnps.period_id = :p_period_id' || ' AND cnpp.credit_type_id = -1000' ||
1046 ' AND cspp.start_date <= cnps.end_date' ||
1047 -- ' AND Nvl(cspp.end_date,cnps.start_date) >= cnps.start_date' ||
1048 --bug 3395792 by jjhuang on 1/23/04
1049 --' AND NVL(cspp.end_date, cnpp.end_date) >= cnps.end_date ' ||
1050 --for bug 3395792 on 2/4/04 by jjhuang. This is to include the following test case:
1051 --If there are two or more payment plans (with the same payment group code) within one period, for example:
1052 --pmt_plan1 from "01-MAY-2003" to "15-MAY-2003", pmt_plan2 from "16-MAY-2003" to "28-MAY-2003".
1053 ' AND NVL(NVL(cspp.end_date, cnpp.end_date),cnps.start_date) >= cnps.start_date ' || ' AND cspp.org_id = cnpp.org_id ' || --R12
1054 ' AND cnpp.org_id = cnps.org_id ' || --R12
1055 ' AND cnps.org_id = :p_org_id ' || --R12
1056 ' ) v ' || ' WHERE row_nums = 1' || ' AND EXISTS' || ' (' || ' SELECT ''x''' ||
1057 ' FROM cn_srp_period_quotas cspq, cn_quotas_all cq' || ' WHERE decode(:p_incentive_type,''ALL'', cq.incentive_type_code,' ||
1058 ' NULL, cq.incentive_type_code,' || ' :p_incentive_type) = cq.incentive_type_code' ||
1059 ' AND v.credit_type_id = cq.credit_type_id' || ' AND v.payment_group_code = cq.payment_group_code' ||
1060 ' AND v.salesrep_id = cspq.salesrep_id' || ' AND cspq.quota_id = cq.quota_id' || ' AND cspq.org_id = cq.org_id' ||
1061 ' AND v.period_id = cspq.period_id ' || ' AND cspq.org_id = cq.org_id ' || --R12
1062 ' AND cq.org_id = :p_org_id ' || --R12
1063 ' AND cspq.quota_id NOT IN ' || ' ( SELECT spayee.quota_id ' || ' FROM cn_srp_payee_assigns spayee' ||
1064 ' WHERE (spayee.salesrep_id = v.salesrep_id OR ' || ' spayee.payee_id = v.salesrep_id)' || ' AND v.prd_end_date >= spayee.start_date' ||
1065 ' AND spayee.org_id = :p_org_id' || --R12
1066 ' AND v.prd_start_date <= Nvl(spayee.end_date, v.prd_end_date) )' || ' )';
1067 -- Bug 3140343 : Payee Design. Check if this salesrep is a Payee
1068 l_ispayee := cn_api.is_payee(p_period_id => p_period_id, p_salesrep_id => p_salesrep_id, p_org_id => p_org_id);
1069
1070 -- if not a payee
1071 IF l_ispayee <> 1
1072 THEN
1073 --
1074 -- get Payment plans
1075 --
1076 -- Find payment plans assigned to the rep for the current payrun
1077 -- period that match the payment group codes of the plan elements
1078
1079 -- FOR srp_pmt_plan IN get_srp_pmt_plan LOOP
1080
1081 --Bug 3198445 by jjhuang
1082 l_applied_pgc.DELETE;
1083 l_applied_pgc_count := 0;
1084
1085 OPEN srp_pmt_plan_cur FOR l_stmt
1086 USING p_salesrep_id, p_period_id, p_org_id, p_incentive_type, p_incentive_type, p_org_id, p_org_id;
1087
1088 LOOP
1089 FETCH srp_pmt_plan_cur
1090 INTO srp_pmt_plan.pay_interval_type_id, srp_pmt_plan.recoverable_interval_type_id,
1091 srp_pmt_plan.pay_against_commission, srp_pmt_plan.payment_group_code, srp_pmt_plan.minimum_amount,
1092 srp_pmt_plan.maximum_amount, srp_pmt_plan.min_rec_flag, srp_pmt_plan.max_rec_flag, srp_pmt_plan.NAME;
1093
1094 EXIT WHEN srp_pmt_plan_cur%NOTFOUND;
1095 --With payment plans.
1096 l_pmt_plan_count := l_pmt_plan_count + 1;
1097 l_period_id := p_period_id;
1098 l_pmt_amount_adj_rec := 0;
1099 l_pmt_amount_adj_nrec := 0;
1100
1101 --Bug 2776847 by jjhuang
1102 get_pay_rec_period_ids(p_period_id => p_period_id,
1103 p_quarter_num => l_get_prd_statuses.quarter_num,
1104 p_period_year => l_get_prd_statuses.period_year,
1105 p_pay_interval_type_id => srp_pmt_plan.pay_interval_type_id,
1106 p_recoverable_interval_type_id => srp_pmt_plan.recoverable_interval_type_id,
1107 x_pay_period_id => l_pay_period_id,
1108 x_rec_period_id => l_rec_period_id,
1109 --R12
1110 p_org_id => p_org_id);
1111 --Bug 3198445 by jjhuang
1112 l_applied_pgc(l_applied_pgc_count) := srp_pmt_plan.payment_group_code;
1113
1114 --Bug 2776847 by jjhuang
1115 --It's recovery period and pay period regardless of pay_against_commission = 'Y' or 'N', do payment plans.
1116 --IF 1.
1117 IF ((srp_pmt_plan.pay_against_commission = 'Y' OR srp_pmt_plan.pay_against_commission = 'N') AND l_period_id = l_pay_period_id AND
1118 l_period_id = l_rec_period_id)
1119 THEN
1120 proc_pmt_trans_by_pe(p_salesrep_id => p_salesrep_id,
1121 p_incentive_type => p_incentive_type,
1122 p_payrun_id => p_payrun_id,
1123 p_payment_group_code => srp_pmt_plan.payment_group_code, --bug 3175375 by jjhuang.
1124 p_applied_pgc => l_applied_pgc, --Bug 3198445 by jjhuang
1125 x_calc_rec_tbl => x_calc_rec_tbl,
1126 --R12
1127 p_org_id => p_org_id);
1128 --
1129 -- get the start date and end date for the given period
1130 --
1131 l_qtr_num := l_get_prd_statuses.quarter_num;
1132 l_year_num := l_get_prd_statuses.period_year;
1133 l_period_set_id := l_get_prd_statuses.period_set_id;
1134 l_period_type_id := l_get_prd_statuses.period_type_id;
1135 l_start_date := l_get_prd_statuses.start_date;
1136 l_end_date := l_get_prd_statuses.end_date;
1137
1138 IF srp_pmt_plan.pay_interval_type_id = -1001 --interval is quarter
1139 THEN
1140 OPEN get_qtr_sdate(l_period_set_id, l_period_type_id, l_year_num, l_qtr_num);
1141
1142 FETCH get_qtr_sdate
1143 INTO l_interval_sdate;
1144
1145 CLOSE get_qtr_sdate;
1146
1147 OPEN get_qtr_edate(l_period_set_id, l_period_type_id, l_year_num, l_qtr_num);
1148
1149 FETCH get_qtr_edate
1150 INTO l_interval_edate;
1151
1152 CLOSE get_qtr_edate;
1153 ELSIF srp_pmt_plan.pay_interval_type_id = -1002
1154 THEN
1155 OPEN get_year_sdate(l_period_set_id, l_period_type_id, l_year_num);
1156
1157 FETCH get_year_sdate
1158 INTO l_interval_sdate;
1159
1160 CLOSE get_year_sdate;
1161
1162 OPEN get_year_edate(l_period_set_id, l_period_type_id, l_year_num);
1163
1164 FETCH get_year_edate
1165 INTO l_interval_edate;
1166
1167 CLOSE get_year_edate;
1168 ELSE
1169 -- pay interval is period
1170 l_interval_sdate := l_start_date;
1171 l_interval_edate := l_end_date;
1172 END IF;
1173
1174 l_itd_paid := 0;
1175
1176 --Get the cash paid interval to date
1177 -- Bug 2875120 : combine 2 cursor and for loop into one
1178 FOR amount IN get_itd_amount_paid(l_period_set_id, l_period_type_id, l_interval_sdate, l_interval_edate, srp_pmt_plan.payment_group_code)
1179 LOOP
1180 l_itd_paid := nvl(l_itd_paid, 0) + nvl(amount.payment, 0);
1181 END LOOP;
1182
1183 -- Determine due amount for current payrun from payment transactions
1184 -- Add earnings from current period
1185 l_net_pre_pmtplan := 0;
1186
1187 OPEN get_earnings_total(srp_pmt_plan.payment_group_code);
1188
1189 FETCH get_earnings_total
1190 INTO l_net_pre_pmtplan;
1191
1192 CLOSE get_earnings_total;
1193
1194 l_itd_paid := l_itd_paid + nvl(l_net_pre_pmtplan, 0);
1195
1196 IF srp_pmt_plan.minimum_amount IS NOT NULL
1197 AND srp_pmt_plan.minimum_amount > l_itd_paid
1198 THEN
1199 IF nvl(srp_pmt_plan.min_rec_flag, 'N') = 'Y'
1200 THEN
1201 l_pmt_amount_adj_rec := srp_pmt_plan.minimum_amount - l_itd_paid;
1202 ELSE
1203 l_pmt_amount_adj_nrec := srp_pmt_plan.minimum_amount - l_itd_paid;
1204 END IF;
1205 END IF; -- End of minimum calculation
1206
1207 IF srp_pmt_plan.maximum_amount IS NOT NULL
1208 AND srp_pmt_plan.maximum_amount < l_itd_paid
1209 THEN
1210 IF nvl(srp_pmt_plan.max_rec_flag, 'N') = 'Y'
1211 THEN
1212 l_pmt_amount_adj_rec := srp_pmt_plan.maximum_amount - l_itd_paid;
1213 ELSE
1214 l_pmt_amount_adj_nrec := srp_pmt_plan.maximum_amount - l_itd_paid;
1215 END IF;
1216 END IF; -- End of maximum calculation
1217
1218 --If payment plan adjustments exist, determine how to distribute them.
1219 --In other words, do distribution evenly on all quotas on pay interval basis.
1220 distribute_pmt_plan_amount(p_salesrep_id => p_salesrep_id,
1221 p_pmt_amount_adj_rec => l_pmt_amount_adj_rec,
1222 p_pmt_amount_adj_nrec => l_pmt_amount_adj_nrec,
1223 p_payment_group_code => srp_pmt_plan.payment_group_code,
1224 p_period_id => p_period_id,
1225 p_incentive_type => p_incentive_type, --bug 3107646, issue 4
1226 x_calc_rec_tbl => x_calc_rec_tbl,
1227 --R12
1228 p_org_id => p_org_id);
1229 ELSE
1230 --not recovery period, but pay period, reset payrun_id to NULL. Give min as payment plan, then distribute it.
1231 IF (srp_pmt_plan.pay_against_commission = 'N' AND l_period_id = l_pay_period_id AND l_period_id <> l_rec_period_id)
1232 THEN
1233 reset_payrun_id(p_payrun_id => p_payrun_id,
1234 p_salesrep_id => p_salesrep_id,
1235 p_incentive_type => p_incentive_type,
1236 p_payment_group_code => srp_pmt_plan.payment_group_code);
1237 proc_pmt_trans_by_pe(p_salesrep_id => p_salesrep_id,
1238 p_incentive_type => p_incentive_type,
1239 p_payrun_id => p_payrun_id,
1240 p_payment_group_code => srp_pmt_plan.payment_group_code, --bug 3175375 by jjhuang.
1241 p_applied_pgc => l_applied_pgc, --Bug 3198445 by jjhuang
1242 x_calc_rec_tbl => x_calc_rec_tbl,
1243 --R12
1244 p_org_id => p_org_id);
1245 give_min_as_pmt_plan(p_min => srp_pmt_plan.minimum_amount,
1246 p_min_rec_flag => nvl(srp_pmt_plan.min_rec_flag, 'N'),
1247 x_pmt_amount_adj_rec => l_pmt_amount_adj_rec,
1248 x_pmt_amount_adj_nrec => l_pmt_amount_adj_nrec);
1249 get_start_and_end_dates(p_interval_type_id => srp_pmt_plan.pay_interval_type_id,
1250 p_period_set_id => l_get_prd_statuses.period_set_id,
1251 p_period_type_id => l_get_prd_statuses.period_type_id,
1252 p_period_year => l_get_prd_statuses.period_year,
1253 p_quarter_num => l_get_prd_statuses.quarter_num,
1254 p_start_date => l_get_prd_statuses.start_date,
1255 p_end_date => l_get_prd_statuses.end_date,
1256 x_interval_start_date => l_interval_sdate,
1257 x_interval_end_date => l_interval_edate,
1258 --R12
1259 p_org_id => p_org_id);
1260 --Do distribution evenly on all quotas on pay interval basis.
1261 distribute_pmt_plan_amount(p_salesrep_id => p_salesrep_id,
1262 p_pmt_amount_adj_rec => l_pmt_amount_adj_rec,
1263 p_pmt_amount_adj_nrec => l_pmt_amount_adj_nrec,
1264 p_payment_group_code => srp_pmt_plan.payment_group_code,
1265 p_period_id => p_period_id,
1266 p_incentive_type => p_incentive_type, --bug 3107646, issue 4
1267 x_calc_rec_tbl => x_calc_rec_tbl,
1268 --R12
1269 p_org_id => p_org_id);
1270 --not pay period, reset payrun_id to NULL so those amount will be included into the next payrun.
1271 ELSIF (srp_pmt_plan.pay_against_commission = 'N' AND l_period_id <> l_pay_period_id)
1272 THEN
1273 reset_payrun_id(p_payrun_id => p_payrun_id,
1274 p_salesrep_id => p_salesrep_id,
1275 p_incentive_type => p_incentive_type,
1276 p_payment_group_code => srp_pmt_plan.payment_group_code);
1277 proc_pmt_trans_by_pe(p_salesrep_id => p_salesrep_id,
1278 p_incentive_type => p_incentive_type,
1279 p_payrun_id => p_payrun_id,
1280 p_payment_group_code => srp_pmt_plan.payment_group_code, --bug 3175375 by jjhuang.
1281 p_applied_pgc => l_applied_pgc, --Bug 3198445 by jjhuang
1282 x_calc_rec_tbl => x_calc_rec_tbl,
1283 --R12
1284 p_org_id => p_org_id);
1285 --not recovery period for pay_against_commission = 'Y', so pay earnings.
1286 ELSIF (srp_pmt_plan.pay_against_commission = 'Y' AND l_period_id <> l_rec_period_id)
1287 THEN
1288 proc_pmt_trans_by_pe(p_salesrep_id => p_salesrep_id,
1289 p_incentive_type => p_incentive_type,
1290 p_payrun_id => p_payrun_id,
1291 p_payment_group_code => srp_pmt_plan.payment_group_code, --bug 3175375 by jjhuang.
1292 p_applied_pgc => l_applied_pgc, --Bug 3198445 by jjhuang
1293 x_calc_rec_tbl => x_calc_rec_tbl,
1294 --R12
1295 p_org_id => p_org_id);
1296 END IF;
1297 END IF; --end of IF 1.
1298
1299 --Bug 3198445 by jjhuang
1300 l_applied_pgc_count := l_applied_pgc_count + 1;
1301 NULL;
1302 END LOOP; --end of loop FOR srp_pmt_plan IN get_srp_pmt_plan
1303
1304 CLOSE srp_pmt_plan_cur;
1305 END IF; -- end if l_ispayee <> 1
1306
1307 --For Bug 2776847 by jjhuang.
1308 --If no payment plans assigned, we need to only get x_calc_rec_tbl to pay the earnings.
1309 -- Commented out by jjhuang for bug 3198445.
1310 --proc_pmt_trans_by_pe includes the case where no payment plans assigned.
1311 proc_pmt_trans_by_pe(p_salesrep_id => p_salesrep_id,
1312 p_incentive_type => p_incentive_type,
1313 p_payrun_id => p_payrun_id,
1314 p_payment_group_code => NULL, --bug 3175375 by jjhuang.
1315 p_applied_pgc => l_applied_pgc, --Bug 3198445 by jjhuang
1316 x_calc_rec_tbl => x_calc_rec_tbl,
1317 --R12
1318 p_org_id => p_org_id);
1319 -- Commented out by jjhuang for bug 3198445. END IF;
1320 END calculate_totals;
1321
1322 -- ===========================================================================
1323 -- Procedure : Create_Worksheet
1324 -- Description: Private API to create a payment worksheet
1325 -- ===========================================================================
1326 PROCEDURE create_worksheet
1327 (
1328 p_api_version IN NUMBER,
1329 p_init_msg_list IN VARCHAR2,
1330 p_commit IN VARCHAR2,
1331 p_validation_level IN NUMBER,
1332 x_return_status OUT NOCOPY VARCHAR2,
1333 x_msg_count OUT NOCOPY NUMBER,
1334 x_msg_data OUT NOCOPY VARCHAR2,
1335 p_worksheet_rec IN worksheet_rec_type,
1336 x_loading_status OUT NOCOPY VARCHAR2,
1337 x_status OUT NOCOPY VARCHAR2
1338 ) IS
1339 l_api_name CONSTANT VARCHAR2(30) := 'Create_Worksheet';
1340 l_payment_worksheet_id NUMBER;
1341 l_calc_pmt_amount NUMBER;
1342 l_adj_pmt_amount_rec NUMBER;
1343 l_adj_pmt_amount_nrec NUMBER;
1344 l_held_amount NUMBER;
1345 l_pay_element_type_id NUMBER;
1346 l_quota_id NUMBER;
1347 l_count NUMBER := 0;
1348 l_payroll_flag VARCHAR2(01);
1349 l_period_id NUMBER;
1350 l_pbt_profile_value VARCHAR2(01) := 'N';
1351 l_calc_rec_tbl calc_rec_tbl_type;
1352 cls_posting_batch_id NUMBER;
1353 recv_posting_batch_id NUMBER;
1354 l_pmt_amount_rec NUMBER := 0;
1355 l_pmt_amount_ctr NUMBER := 0;
1356 l_incentive_type VARCHAR2(30);
1357 l_rowid VARCHAR2(30);
1358 l_srp_total NUMBER;
1359 l_pmt_total NUMBER;
1360 l_comm_total NUMBER;
1361 l_found NUMBER;
1362 l_call_from VARCHAR2(30);
1363 TYPE num_tab IS TABLE OF NUMBER;
1364 l_wk_plan_elements num_tab;
1365
1366 -- changes for bug#2568937
1367 -- for ap integration population of account
1368 l_payables_flag cn_repositories.payables_flag%TYPE;
1369 l_payables_ccid_level cn_repositories.payables_ccid_level%TYPE;
1370
1371 -- changes for bug#2568937
1372 -- for ap integration population of account
1373 --R12
1374 CURSOR get_apps IS
1375 SELECT payables_flag,
1376 payroll_flag,
1377 payables_ccid_level
1378 FROM cn_repositories
1379 WHERE org_id = p_worksheet_rec.org_id;
1380
1381 CURSOR get_worksheet IS
1382 SELECT 1
1383 FROM cn_payment_worksheets,
1384 cn_payruns
1385 WHERE cn_payment_worksheets.salesrep_id = p_worksheet_rec.salesrep_id
1386 AND cn_payment_worksheets.payrun_id = cn_payruns.payrun_id
1387 AND quota_id IS NULL
1388 AND cn_payruns.status <> 'PAID';
1389
1390 err_num NUMBER;
1391
1392 -- Get the Payrun informations
1393 CURSOR get_payrun IS
1394 SELECT payrun_id,
1395 pay_period_id,
1396 incentive_type_code,
1397 pay_date
1398 FROM cn_payruns
1399 WHERE payrun_id = p_worksheet_rec.payrun_id
1400 FOR UPDATE NOWAIT;
1401
1402 -- Get the Payrun informations for conc program
1403 CURSOR get_payrun_for_conc_program IS
1404 SELECT payrun_id,
1405 pay_period_id,
1406 incentive_type_code,
1407 pay_date
1408 FROM cn_payruns
1409 WHERE payrun_id = p_worksheet_rec.payrun_id;
1410
1411 -- Get the period information
1412 CURSOR get_prd_statuses(p_period_id NUMBER) IS
1413 SELECT quarter_num,
1414 period_year,
1415 period_set_id,
1416 period_type_id,
1417 start_date,
1418 end_date
1419 FROM cn_period_statuses
1420 WHERE period_id = p_period_id
1421 AND org_id = p_worksheet_rec.org_id;
1422
1423 CURSOR get_srp_total(p_period_id NUMBER) IS
1424 SELECT nvl(SUM(nvl(srp.balance2_dtd, 0) - nvl(srp.balance2_ctd, 0) + nvl(srp.balance2_bbd, 0) - nvl(srp.balance2_bbc, 0)), 0)
1425 FROM cn_srp_periods srp
1426 WHERE srp.period_id = p_period_id
1427 AND srp.salesrep_id = p_worksheet_rec.salesrep_id
1428 AND srp.credit_type_id = g_credit_type_id
1429 AND quota_id IS NULL
1430 AND org_id = p_worksheet_rec.org_id;
1431
1432 CURSOR get_pmt_total(p_period_id NUMBER) IS
1433 SELECT nvl(SUM(nvl(amount, 0)), 0)
1434 FROM cn_payment_transactions pmt
1435 WHERE pmt.pay_period_id <= p_period_id
1436 AND pmt.credited_salesrep_id = p_worksheet_rec.salesrep_id
1437 AND pmt.credit_type_id = g_credit_type_id
1438 AND pmt.incentive_type_code IN ('COMMISSION', 'BONUS')
1439 AND (pmt.payrun_id IS NULL OR pmt.payrun_id = p_worksheet_rec.payrun_id)
1440 --R12
1441 AND pmt.org_id = p_worksheet_rec.org_id;
1442
1443 -- 12/27/04 : Bug 4090737 Performance Issue Creating Worksheet START
1444 CURSOR get_comm_total(p_period_id NUMBER) IS
1445 SELECT
1446 nvl(SUM(nvl(commission_amount, 0)), 0)
1447 FROM cn_commission_lines_all ccl
1448 WHERE credited_salesrep_id = p_worksheet_rec.salesrep_id
1449 AND processed_period_id <= p_period_id
1450 AND credit_type_id = g_credit_type_id
1451 AND status = 'CALC'
1452 AND posting_status = 'UNPOSTED'
1453 AND srp_payee_assign_id IS NULL
1454 -- posting_status not set to posted yet
1455 AND NOT EXISTS (SELECT NULL
1456 FROM cn_payment_transactions_all pmt
1457 WHERE pmt.credited_salesrep_id = ccl.credited_salesrep_id
1458 AND pmt.commission_line_id = ccl.commission_line_id
1459 AND pmt.credit_type_id = ccl.credit_type_id
1460 AND pmt.incentive_type_code IN ('COMMISSION', 'BONUS')
1461 AND pmt.payrun_id = p_worksheet_rec.payrun_id)
1462 AND ccl.org_id = p_worksheet_rec.org_id;
1463
1464 -- 12/27/04 : Bug 4090737 Performance Issue Creating Worksheet END
1465
1466 -- Bug 3140343 : Payee Design
1467 CURSOR get_comm_total_payee(p_period_id NUMBER) IS
1468 SELECT /*+ index(cl CN_COMMISSION_LINES_N14) */
1469 nvl(SUM(nvl(commission_amount, 0)), 0)
1470 FROM cn_commission_lines cl,
1471 cn_srp_payee_assigns_all spayee
1472 WHERE cl.srp_payee_assign_id IS NOT NULL
1473 AND cl.srp_payee_assign_id = spayee.srp_payee_assign_id
1474 AND spayee.payee_id = p_worksheet_rec.salesrep_id
1475 AND cl.credited_salesrep_id = spayee.salesrep_id
1476 AND cl.processed_period_id <= p_period_id
1477 AND cl.status = 'CALC'
1478 AND cl.credit_type_id = g_credit_type_id
1479 AND cl.posting_status = 'UNPOSTED'
1480 AND cl.org_id = spayee.org_id
1481 AND cl.commission_line_id NOT IN (SELECT pmt.commission_line_id
1482 FROM cn_payment_transactions pmt
1483 WHERE pmt.credited_salesrep_id = p_worksheet_rec.salesrep_id
1484 AND pmt.credit_type_id = g_credit_type_id
1485 AND pmt.incentive_type_code IN ('COMMISSION', 'BONUS')
1486 AND pmt.payrun_id = p_worksheet_rec.payrun_id)
1487 --R12
1488 AND cl.org_id = p_worksheet_rec.org_id
1489 AND spayee.org_id = p_worksheet_rec.org_id;
1490
1491 CURSOR get_worksheet_id IS
1492 SELECT payment_worksheet_id
1493 FROM cn_payment_worksheets
1494 WHERE payrun_id = p_worksheet_rec.payrun_id
1495 AND salesrep_id = p_worksheet_rec.salesrep_id
1496 AND quota_id IS NULL;
1497
1498 l_get_payrun_rec get_payrun%ROWTYPE; -- Payrun
1499 l_get_prd_statuses get_prd_statuses%ROWTYPE; -- Period
1500 l_pmt_trans_rec cn_pmt_trans_pkg.pmt_trans_rec_type; -- PmtTrans
1501 l_batch_rec cn_prepostbatches.posting_batch_rec_type;
1502 l_tmp NUMBER := 0;
1503 l_calc_status cn_srp_intel_periods.processing_status_code%TYPE;
1504 l_ispayee NUMBER := 0;
1505 l_has_access BOOLEAN;
1506
1507 BEGIN
1508 -- Standard Start of API savepoint
1509 SAVEPOINT create_worksheet;
1510
1511 -- Standard call to check for call compatibility.
1512 IF NOT fnd_api.compatible_api_call(g_api_version, p_api_version, l_api_name, g_pkg_name)
1513 THEN
1514 RAISE fnd_api.g_exc_unexpected_error;
1515 END IF;
1516
1517 -- Initialize message list if p_init_msg_list is set to TRUE.
1518 IF fnd_api.to_boolean(p_init_msg_list)
1519 THEN
1520 fnd_msg_pub.initialize;
1521 END IF;
1522
1523 -- Initialize API return status to success
1524 x_return_status := fnd_api.g_ret_sts_success;
1525 x_loading_status := 'CN_INSERTED';
1526 --Added for R12 payment security check begin.
1527 l_has_access := cn_payment_security_pvt.get_security_access(cn_payment_security_pvt.g_type_wksht, cn_payment_security_pvt.g_access_wksht_create);
1528
1529 IF (l_has_access = FALSE)
1530 THEN
1531 RAISE fnd_api.g_exc_error;
1532 END IF;
1533
1534 -- API body
1535 -- The following validations are performed by this API
1536 -- Check for the following mandatory parameters payrun_id, salesrep_id,
1537 -- Pay run should be unpaid
1538 -- Salesrep should not be on hold -cn_salesreps.hold_payment
1539 -- Subledger entry should exist for salesrep, credit_type and period
1540 -- cn_srp_periods
1541 -- Mandatory parameters check for payrun_id, salesrep_id
1542 IF ((cn_api.chk_miss_null_num_para(p_num_para => p_worksheet_rec.payrun_id,
1543 p_obj_name => cn_api.get_lkup_meaning('PAY_RUN_NAME', 'PAY_RUN_VALIDATION_TYPE'),
1544 p_loading_status => x_loading_status,
1545 x_loading_status => x_loading_status)) = fnd_api.g_true)
1546 THEN
1547 RAISE fnd_api.g_exc_error;
1548 END IF;
1549
1550 IF ((cn_api.chk_miss_null_num_para(p_num_para => p_worksheet_rec.salesrep_id,
1551 p_obj_name => cn_api.get_lkup_meaning('SALES_PERSON', 'PAY_RUN_VALIDATION_TYPE'),
1552 p_loading_status => x_loading_status,
1553 x_loading_status => x_loading_status)) = fnd_api.g_true)
1554 THEN
1555 RAISE fnd_api.g_exc_error;
1556 END IF;
1557
1558 -- Check Payrun Status
1559 IF cn_api.chk_payrun_status_paid(p_payrun_id => p_worksheet_rec.payrun_id, p_loading_status => x_loading_status, x_loading_status => x_loading_status) =
1560 fnd_api.g_true
1561 THEN
1562 RAISE fnd_api.g_exc_error;
1563 END IF;
1564
1565 -- Check if the salesrep is on hold
1566 IF cn_api.chk_srp_hold_status(p_salesrep_id => p_worksheet_rec.salesrep_id,
1567 --R12
1568 p_org_id => p_worksheet_rec.org_id,
1569 p_loading_status => x_loading_status,
1570 x_loading_status => x_loading_status) = fnd_api.g_true
1571 THEN
1572 RAISE fnd_api.g_exc_error;
1573 END IF;
1574
1575 -- Get the Payrun
1576 BEGIN
1577 l_call_from := p_worksheet_rec.call_from;
1578
1579 IF l_call_from = cn_payment_worksheet_pvt.concurrent_program_call
1580 THEN
1581 OPEN get_payrun_for_conc_program;
1582 FETCH get_payrun_for_conc_program
1583 INTO l_get_payrun_rec;
1584 CLOSE get_payrun_for_conc_program;
1585 ELSE
1586 OPEN get_payrun;
1587 FETCH get_payrun
1588 INTO l_get_payrun_rec;
1589 CLOSE get_payrun;
1590 END IF;
1591 EXCEPTION
1592 WHEN OTHERS THEN
1593 err_num := SQLCODE;
1594 IF l_call_from = cn_payment_worksheet_pvt.concurrent_program_call
1595 THEN
1596 CLOSE get_payrun_for_conc_program;
1597 ELSE
1598 CLOSE get_payrun;
1599 END IF;
1600
1601 IF err_num = -54
1602 THEN
1603 fnd_message.set_name('CN', 'CN_INVALID_OBJECT_VERSION');
1604 fnd_msg_pub.add;
1605 RAISE fnd_api.g_exc_error;
1606 ELSE
1607 RAISE;
1608 END IF;
1609 END;
1610
1611 -- fix for bug 5334261
1612 OPEN get_worksheet;
1613 FETCH get_worksheet
1614 INTO l_found;
1615 CLOSE get_worksheet;
1616
1617 IF l_found = 1
1618 THEN
1619 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error)
1620 THEN
1621 fnd_message.set_name('CN', 'CN_DUPLICATE_WORKSHEET');
1622 fnd_msg_pub.add;
1623 END IF;
1624
1625 x_loading_status := 'CN_DUPLICATE_WORKSHEET';
1626 RAISE fnd_api.g_exc_error;
1627 END IF;
1628
1629 -- Get the Pay By Summary Profile value
1630 -- N - Pay by Summary Y - Pay by Transaction
1631 l_pbt_profile_value := cn_payment_security_pvt.get_pay_by_mode(p_worksheet_rec.payrun_id);
1632
1633 -- Bug 3140343 : Payee Design. Check if this salesrep is a Payee
1634 l_ispayee := cn_api.is_payee(p_period_id => l_get_payrun_rec.pay_period_id,
1635 p_salesrep_id => p_worksheet_rec.salesrep_id,
1636 p_org_id => p_worksheet_rec.org_id);
1637
1638 -- Check duplicate worksheet
1639 IF cn_api.chk_duplicate_worksheet(p_payrun_id => p_worksheet_rec.payrun_id,
1640 p_salesrep_id => p_worksheet_rec.salesrep_id,
1641 p_org_id => p_worksheet_rec.org_id,
1642 p_loading_status => x_loading_status,
1643 x_loading_status => x_loading_status) = fnd_api.g_true
1644 THEN
1645 RAISE fnd_api.g_exc_error;
1646 END IF;
1647
1648 -- get quarter Number and Year Number
1649 OPEN get_prd_statuses(l_get_payrun_rec.pay_period_id);
1650
1651 FETCH get_prd_statuses
1652 INTO l_get_prd_statuses;
1653
1654 CLOSE get_prd_statuses;
1655
1656 -- get the posting batch id
1657 SELECT cn_posting_batches_s.NEXTVAL
1658 INTO cls_posting_batch_id
1659 FROM dual;
1660
1661 -- if the payrun incentive type code is ALL
1662 -- we will set the incentive type as NULL
1663 -- which means we will get both Bonus and Commissions
1664 IF l_get_payrun_rec.incentive_type_code = 'ALL'
1665 THEN
1666 l_incentive_type := NULL;
1667 ELSE
1668 l_incentive_type := l_get_payrun_rec.incentive_type_code;
1669 END IF;
1670
1671 -- Main Insert started for Create Worksheet
1672 -- Call the Table hander to Insert Records
1673 cn_pmt_trans_pkg.insert_record(p_pay_by_transaction => nvl(l_pbt_profile_value, 'N'),
1674 p_salesrep_id => p_worksheet_rec.salesrep_id,
1675 p_payrun_id => p_worksheet_rec.payrun_id,
1676 p_pay_date => l_get_payrun_rec.pay_date,
1677 p_incentive_type => l_incentive_type,
1678 p_pay_period_id => l_get_payrun_rec.pay_period_id,
1679 p_credit_type_id => g_credit_type_id,
1680 p_posting_batch_id => cls_posting_batch_id,
1681 p_org_id => p_worksheet_rec.org_id);
1682
1683 -- Bug 2760379 : only check bal mismatch when it's pay by trx
1684 -- check Balance Miss Match
1685 IF l_pbt_profile_value = 'Y'
1686 THEN
1687 OPEN get_srp_total(l_get_payrun_rec.pay_period_id);
1688
1689 FETCH get_srp_total
1690 INTO l_srp_total;
1691
1692 CLOSE get_srp_total;
1693
1694 OPEN get_pmt_total(l_get_payrun_rec.pay_period_id);
1695
1696 FETCH get_pmt_total
1697 INTO l_pmt_total;
1698
1699 CLOSE get_pmt_total;
1700
1701 -- Bug 3140343 : Payee Design.
1702 IF l_ispayee <> 1
1703 THEN
1704 -- 08/26/03 : Bug 3114349 Issue 2
1705 OPEN get_comm_total(l_get_payrun_rec.pay_period_id);
1706
1707 FETCH get_comm_total
1708 INTO l_comm_total;
1709
1710 CLOSE get_comm_total;
1711 ELSE
1712 OPEN get_comm_total_payee(l_get_payrun_rec.pay_period_id);
1713
1714 FETCH get_comm_total_payee
1715 INTO l_comm_total;
1716
1717 CLOSE get_comm_total_payee;
1718 END IF;
1719
1720 IF abs(nvl(l_srp_total, 0) - nvl(l_pmt_total, 0) - nvl(l_comm_total, 0)) > .1
1721 THEN
1722 SELECT processing_status_code
1723 INTO l_calc_status
1724 FROM cn_srp_intel_periods
1725 WHERE salesrep_id = p_worksheet_rec.salesrep_id
1726 AND period_id = l_get_payrun_rec.pay_period_id
1727 AND org_id = p_worksheet_rec.org_id;
1728
1729 IF l_calc_status NOT IN ('CALCULATED', 'CLEAN', 'ROLLED_UP')
1730 THEN
1731 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error)
1732 THEN
1733 fnd_message.set_name('CN', 'CN_CALC_NOT_COMPLETE');
1734 fnd_msg_pub.add;
1735 END IF;
1736
1737 x_loading_status := 'CN_CALC_NOT_COMPLETE';
1738 RAISE fnd_api.g_exc_error;
1739 END IF;
1740
1741 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error)
1742 THEN
1743 fnd_message.set_name('CN', 'CN_WKSHT_SRP_COMM_MISMATCH');
1744 fnd_msg_pub.add;
1745 END IF;
1746
1747 x_loading_status := 'CN_WKSHT_SRP_COMM_MISMATCH';
1748 RAISE fnd_api.g_exc_error;
1749 END IF; -- end if ABS() > .1
1750 END IF; --end if l_pbt_profile_value = 'Y'
1751
1752 -- calculate values for payment plan records
1753 calculate_totals(p_salesrep_id => p_worksheet_rec.salesrep_id,
1754 p_period_id => l_get_payrun_rec.pay_period_id,
1755 p_incentive_type => l_incentive_type,
1756 p_payrun_id => l_get_payrun_rec.payrun_id,
1757 x_calc_rec_tbl => l_calc_rec_tbl,
1758 --R12
1759 p_org_id => p_worksheet_rec.org_id);
1760
1761 -- Bug 2692801 : avoid PL/SQL error when l_calc_rec_tbl is null
1762 IF l_calc_rec_tbl.COUNT > 0
1763 THEN
1764 FOR i IN l_calc_rec_tbl.FIRST .. l_calc_rec_tbl.LAST
1765 LOOP
1766 IF l_calc_rec_tbl(i).quota_id IS NOT NULL
1767 THEN
1768 IF l_calc_rec_tbl(i).pmt_amount_adj_rec <> 0
1769 OR l_calc_rec_tbl(i).pmt_amount_adj_nrec <> 0
1770 THEN
1771 -- Bug 2880233: should find pay_element for PMTPLN base on quota_id
1772
1773 -- IF l_calc_rec_tbl(i).pmt_amount_adj_rec <> 0 THEN
1774 l_pay_element_type_id := cn_api.get_pay_element_id(l_calc_rec_tbl(i).quota_id,
1775 p_worksheet_rec.salesrep_id,
1776 --R12
1777 p_worksheet_rec.org_id,
1778 l_get_payrun_rec.pay_date);
1779
1780 -- Get the Sequence Number
1781 SELECT cn_posting_batches_s.NEXTVAL
1782 INTO recv_posting_batch_id
1783 FROM dual;
1784
1785 l_batch_rec.posting_batch_id := recv_posting_batch_id;
1786 l_batch_rec.NAME := 'PMTPLN batch number:' || l_get_payrun_rec.payrun_id || ':' || p_worksheet_rec.salesrep_id || ':' ||
1787 l_calc_rec_tbl(i).quota_id || ':' || recv_posting_batch_id;
1788 l_batch_rec.created_by := fnd_global.user_id;
1789 l_batch_rec.creation_date := SYSDATE;
1790 l_batch_rec.last_updated_by := fnd_global.user_id;
1791 l_batch_rec.last_update_date := SYSDATE;
1792 l_batch_rec.last_update_login := fnd_global.login_id;
1793 -- Create the Posting Batches
1794 cn_prepostbatches.begin_record(x_operation => 'INSERT',
1795 x_rowid => l_rowid,
1796 x_posting_batch_rec => l_batch_rec,
1797 x_program_type => NULL,
1798 p_org_id => p_worksheet_rec.org_id);
1799 l_pmt_trans_rec.posting_batch_id := recv_posting_batch_id;
1800 l_pmt_trans_rec.incentive_type_code := 'PMTPLN';
1801 l_pmt_trans_rec.credit_type_id := g_credit_type_id;
1802 l_pmt_trans_rec.payrun_id := p_worksheet_rec.payrun_id;
1803 l_pmt_trans_rec.credited_salesrep_id := p_worksheet_rec.salesrep_id;
1804 l_pmt_trans_rec.payee_salesrep_id := p_worksheet_rec.salesrep_id;
1805 l_pmt_trans_rec.pay_period_id := l_get_payrun_rec.pay_period_id;
1806 l_pmt_trans_rec.hold_flag := 'N';
1807 l_pmt_trans_rec.waive_flag := 'N';
1808 l_pmt_trans_rec.paid_flag := 'N';
1809 l_pmt_trans_rec.recoverable_flag := 'N';
1810 l_pmt_trans_rec.pay_element_type_id := l_pay_element_type_id;
1811 l_pmt_trans_rec.quota_id := l_calc_rec_tbl(i).quota_id;
1812 l_pmt_trans_rec.amount := nvl(l_calc_rec_tbl(i).pmt_amount_adj_rec, 0) + nvl(l_calc_rec_tbl(i).pmt_amount_adj_nrec, 0);
1813 l_pmt_trans_rec.payment_amount := nvl(l_calc_rec_tbl(i).pmt_amount_adj_rec, 0) + nvl(l_calc_rec_tbl(i).pmt_amount_adj_nrec, 0);
1814 --R12
1815 l_pmt_trans_rec.org_id := p_worksheet_rec.org_id;
1816 l_pmt_trans_rec.object_version_number := 1;
1817 -- Create the Payment Plan Record
1818 cn_pmt_trans_pkg.insert_record(p_tran_rec => l_pmt_trans_rec);
1819 END IF;
1820
1821 IF l_calc_rec_tbl(i).quota_id <> -1000
1822 OR abs(nvl(l_calc_rec_tbl(i).pmt_amount_calc, 0)) + abs(nvl(l_calc_rec_tbl(i).pmt_amount_adj_rec, 0)) +
1823 abs(nvl(l_calc_rec_tbl(i).pmt_amount_adj_nrec, 0)) + abs(nvl(l_calc_rec_tbl(i).pmt_amount_ctr, 0)) +
1824 abs(nvl(l_calc_rec_tbl(i).pmt_amount_rec, 0)) + abs(nvl(l_calc_rec_tbl(i).held_amount, 0)) <> 0
1825 THEN
1826 -- Create the Worksheet at the Quota Level
1827 cn_payment_worksheets_pkg.insert_record(x_payrun_id => p_worksheet_rec.payrun_id,
1828 x_salesrep_id => p_worksheet_rec.salesrep_id,
1829 x_quota_id => l_calc_rec_tbl(i).quota_id,
1830 x_credit_type_id => g_credit_type_id,
1831 x_calc_pmt_amount => nvl(l_calc_rec_tbl(i).pmt_amount_calc, 0),
1832 x_adj_pmt_amount_rec => nvl(l_calc_rec_tbl(i).pmt_amount_adj_rec, 0),
1833 x_adj_pmt_amount_nrec => nvl(l_calc_rec_tbl(i).pmt_amount_adj_nrec, 0),
1834 x_adj_pmt_amount => nvl(l_calc_rec_tbl(i).pmt_amount_ctr, 0),
1835 x_held_amount => nvl(l_calc_rec_tbl(i).held_amount, 0),
1836 x_pmt_amount_recovery => nvl(l_calc_rec_tbl(i).pmt_amount_rec, 0),
1837 x_worksheet_status => 'UNPAID',
1838 x_created_by => fnd_global.user_id,
1839 x_creation_date => SYSDATE,
1840 p_org_id => p_worksheet_rec.org_id,
1841 p_object_version_number => 1);
1842 x_loading_status := 'CN_INSERTED';
1843 END IF;
1844 END IF;
1845
1846 -- for summary record
1847 l_calc_pmt_amount := nvl(l_calc_pmt_amount, 0) + nvl(l_calc_rec_tbl(i).pmt_amount_calc, 0);
1848 l_adj_pmt_amount_rec := nvl(l_adj_pmt_amount_rec, 0) + nvl(l_calc_rec_tbl(i).pmt_amount_adj_rec, 0);
1849 l_adj_pmt_amount_nrec := nvl(l_adj_pmt_amount_nrec, 0) + nvl(l_calc_rec_tbl(i).pmt_amount_adj_nrec, 0);
1850 l_pmt_amount_rec := nvl(l_pmt_amount_rec, 0) + nvl(l_calc_rec_tbl(i).pmt_amount_rec, 0);
1851 l_held_amount := nvl(l_held_amount, 0) + nvl(l_calc_rec_tbl(i).held_amount, 0);
1852 END LOOP;
1853 END IF; -- end IF l_calc_rec_tbl.COUNT > 0 THEN
1854
1855 -- Create the Summary Record for for each salesrep
1856 x_loading_status := 'CN_INSERTED';
1857
1858 -- BUG 2774167 : Check duplicate summary worksheet
1859 BEGIN
1860 l_tmp := 0;
1861
1862 SELECT 1
1863 INTO l_tmp
1864 FROM cn_payment_worksheets
1865 WHERE payrun_id = p_worksheet_rec.payrun_id
1866 AND salesrep_id = p_worksheet_rec.salesrep_id
1867 AND quota_id IS NULL;
1868
1869 IF l_tmp <> 0
1870 THEN
1871 --Error condition
1872 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error)
1873 THEN
1874 fnd_message.set_name('CN', 'CN_DUPLICATE_WORKSHEET');
1875 fnd_msg_pub.add;
1876 END IF;
1877
1878 x_loading_status := 'CN_DUPLICATE_WORKSHEET';
1879 RAISE fnd_api.g_exc_error;
1880 END IF;
1881 EXCEPTION
1882 WHEN no_data_found THEN
1883 NULL;
1884 END;
1885
1886 -- Create the Summary Record in the Worksheet
1887 cn_payment_worksheets_pkg.insert_record(x_payrun_id => p_worksheet_rec.payrun_id,
1888 x_salesrep_id => p_worksheet_rec.salesrep_id,
1889 x_credit_type_id => g_credit_type_id,
1890 x_calc_pmt_amount => nvl(l_calc_pmt_amount, 0),
1891 x_adj_pmt_amount_rec => nvl(l_adj_pmt_amount_rec, 0),
1892 x_adj_pmt_amount_nrec => nvl(l_adj_pmt_amount_nrec, 0),
1893 x_adj_pmt_amount => nvl(l_pmt_amount_ctr, 0),
1894 x_held_amount => nvl(l_held_amount, 0),
1895 x_pmt_amount_recovery => nvl(l_pmt_amount_rec, 0),
1896 x_worksheet_status => 'UNPAID',
1897 x_created_by => fnd_global.user_id,
1898 x_creation_date => SYSDATE,
1899 p_org_id => p_worksheet_rec.org_id,
1900 p_object_version_number => 1);
1901
1902 IF x_loading_status <> 'CN_INSERTED'
1903 THEN
1904 RAISE fnd_api.g_exc_error;
1905 END IF;
1906
1907 OPEN get_worksheet_id;
1908
1909 FETCH get_worksheet_id
1910 INTO l_payment_worksheet_id;
1911
1912 CLOSE get_worksheet_id;
1913
1914 update_ptd_details (
1915 p_salesrep_id => p_worksheet_rec.salesrep_id ,
1916 p_payrun_id => p_worksheet_rec.payrun_id
1917 ) ;
1918
1919 -- Bug 3140343 : Payee Design.
1920 IF l_ispayee <> 1
1921 THEN
1922 x_loading_status := 'CN_INSERTED';
1923 END IF;
1924
1925 -- Bug 3140343 : Payee Design. set commission_lines to POSTED
1926 IF l_pbt_profile_value = 'Y'
1927 THEN
1928 IF l_ispayee <> 1
1929 THEN
1930 UPDATE cn_commission_lines cls
1931 SET posting_status = 'POSTED',
1932 last_update_date = SYSDATE,
1933 last_updated_by = fnd_global.user_id,
1934 last_update_login = fnd_global.login_id
1935 WHERE posting_status <> 'POSTED'
1936 AND status = 'CALC'
1937 AND srp_payee_assign_id IS NULL
1938 AND commission_line_id IN (SELECT commission_line_id
1939 FROM cn_payment_transactions
1940 WHERE posting_batch_id = cls_posting_batch_id
1941 AND commission_line_id IS NOT NULL);
1942 ELSE
1943 --payee
1944 UPDATE cn_commission_lines cls
1945 SET posting_status = 'POSTED',
1946 last_update_date = SYSDATE,
1947 last_updated_by = fnd_global.user_id,
1948 last_update_login = fnd_global.login_id
1949 WHERE posting_status <> 'POSTED'
1950 AND status = 'CALC'
1951 AND srp_payee_assign_id IS NOT NULL
1952 AND commission_line_id IN (SELECT commission_line_id
1953 FROM cn_payment_transactions
1954 WHERE posting_batch_id = cls_posting_batch_id
1955 AND commission_line_id IS NOT NULL);
1956 END IF;
1957
1958 ELSE
1959
1960 SELECT DISTINCT pw.quota_id
1961 BULK COLLECT INTO l_wk_plan_elements
1962 FROM cn_payment_worksheets pw
1963 WHERE pw.payrun_id = l_get_payrun_rec.payrun_id
1964 AND pw.salesrep_id = p_worksheet_rec.salesrep_id
1965 AND pw.quota_id IS NOT NULL ;
1966
1967 --PBS
1968 IF l_ispayee <> 1
1969 THEN
1970
1971 FORALL m IN 1..l_wk_plan_elements.COUNT
1972 UPDATE cn_commission_lines cls
1973 SET posting_status = 'POSTED',
1974 last_update_date = SYSDATE,
1975 last_updated_by = fnd_global.user_id,
1976 last_update_login = fnd_global.login_id
1977 WHERE posting_status <> 'POSTED'
1978 AND credit_type_id = g_credit_type_id
1979 AND processed_period_id <= l_get_payrun_rec.pay_period_id
1980 AND status = 'CALC'
1981 AND srp_payee_assign_id IS NULL
1982 AND credited_salesrep_id = p_worksheet_rec.salesrep_id
1983 AND quota_id = l_wk_plan_elements(m) ;
1984
1985 ELSE
1986
1987 UPDATE cn_commission_lines clk
1988 SET posting_status = 'POSTED',
1989 last_update_date = SYSDATE,
1990 last_updated_by = fnd_global.user_id,
1991 last_update_login = fnd_global.login_id
1992 WHERE processed_period_id <= l_get_payrun_rec.pay_period_id
1993 AND status = 'CALC'
1994 AND credit_type_id = g_credit_type_id
1995 AND posting_status <> 'POSTED'
1996 AND org_id = p_worksheet_rec.org_id
1997 AND clk.srp_payee_assign_id IS NOT NULL
1998 AND EXISTS (SELECT 1
1999 FROM cn_srp_payee_assigns_all spayee,
2000 cn_payment_worksheets wksht
2001 WHERE clk.srp_payee_assign_id = spayee.srp_payee_assign_id
2002 AND spayee.quota_id = wksht.quota_id
2003 AND spayee.payee_id = p_worksheet_rec.salesrep_id
2004 AND wksht.payrun_id = l_get_payrun_rec.payrun_id
2005 AND wksht.salesrep_id = p_worksheet_rec.salesrep_id);
2006
2007 END IF; -- end IF l_ispayee <> 1
2008 END IF; -- end IF l_pbt_profile_value = 'Y'
2009
2010 -- changes for bug#2568937
2011 -- for payroll integration population of account
2012 OPEN get_apps;
2013
2014 FETCH get_apps
2015 INTO l_payables_flag, l_payroll_flag, l_payables_ccid_level;
2016
2017 CLOSE get_apps;
2018
2019 -- changes for bug#2568937
2020 -- for payroll integration population of account
2021 -- use if AP / Payroll integration has been enabled.
2022 IF l_payables_flag = 'Y'
2023 THEN
2024 -- Populate ccid's in payment worksheets
2025 IF (cn_payrun_pvt.populate_ccids(p_payrun_id => p_worksheet_rec.payrun_id,
2026 p_salesrep_id => p_worksheet_rec.salesrep_id,
2027 p_loading_status => x_loading_status,
2028 x_loading_status => x_loading_status)) = fnd_api.g_true
2029 THEN
2030 RAISE fnd_api.g_exc_unexpected_error;
2031 END IF;
2032 END IF;
2033
2034 -- End of API body.
2035 -- Standard check of p_commit.
2036 IF fnd_api.to_boolean(p_commit)
2037 THEN
2038 COMMIT WORK;
2039 END IF;
2040
2041 --
2042 -- Standard call to get message count and if count is 1, get message info.
2043 --
2044 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
2045 EXCEPTION
2046 WHEN fnd_api.g_exc_error THEN
2047 ROLLBACK TO create_worksheet;
2048 x_return_status := fnd_api.g_ret_sts_error;
2049 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
2050 WHEN fnd_api.g_exc_unexpected_error THEN
2051 ROLLBACK TO create_worksheet;
2052 x_loading_status := 'UNEXPECTED_ERR';
2053 x_return_status := fnd_api.g_ret_sts_unexp_error;
2054 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
2055 WHEN OTHERS THEN
2056 ROLLBACK TO create_worksheet;
2057 x_loading_status := 'UNEXPECTED_ERR';
2058 x_return_status := fnd_api.g_ret_sts_unexp_error;
2059
2060 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)
2061 THEN
2062 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
2063 END IF;
2064
2065 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
2066 END create_worksheet;
2067
2068 -- ===========================================================================
2069 -- Procedure : Create_Multiple_Worksheets
2070 -- Description : This API is used to create multiple worksheets
2071 -- ===========================================================================
2072 PROCEDURE create_multiple_worksheets (
2073 errbuf OUT NOCOPY VARCHAR2,
2074 retcode OUT NOCOPY NUMBER,
2075 p_batch_id IN NUMBER,
2076 p_payrun_id IN NUMBER,
2077 p_logical_batch_id IN NUMBER,
2078 --R12
2079 p_org_id IN cn_payruns.org_id%TYPE
2080 )
2081 IS
2082 l_api_name CONSTANT VARCHAR2 (30) := 'Create_Multiple_Worksheets';
2083 g_api_version CONSTANT NUMBER := 1.0;
2084 x_return_status VARCHAR2(10) := fnd_api.g_ret_sts_success;
2085 x_msg_count NUMBER;
2086 x_msg_data VARCHAR2(4000);
2087 l_worksheet_rec cn_payment_worksheet_pvt.worksheet_rec_type;
2088 x_status VARCHAR2(200);
2089 x_loading_status VARCHAR2(20) := 'CN_INSERTED';
2090 l_start_time DATE;
2091 l_error_count NUMBER := 0;
2092
2093 --Cursor below was modified by Sundar Venkat to fix bug 2775288
2094 --The following change is made to ensure, that worksheets are created
2095 --only for those salesreps, who have a valid comp. plan assignment
2096 --during the payperiod of the payrun
2097 -- Bug 3140343 : Payee Design.
2098 BEGIN
2099 --
2100 -- Initialize API return status to success
2101 --
2102 x_return_status := fnd_api.g_ret_sts_success;
2103 x_loading_status := 'CN_INSERTED';
2104 --
2105 -- API body
2106 --
2107 l_start_time := SYSDATE;
2108 fnd_file.put_line(fnd_file.log, ' Input Parameters Payrun_id = ' || p_payrun_id);
2109 fnd_file.put_line(fnd_file.log, ' Input Parameters Batch_id = ' || p_batch_id);
2110 fnd_file.put_line(fnd_file.log, ' Current time = ' || to_char(l_start_time, 'Dy DD-Mon-YYYY HH24:MI:SS'));
2111
2112 l_worksheet_rec.payrun_id := p_payrun_id;
2113 l_worksheet_rec.org_id := p_org_id;
2114 l_worksheet_rec.call_from := cn_payment_worksheet_pvt.concurrent_program_call;
2115
2116 FOR emp IN (SELECT salesrep_id
2117 FROM cn_process_batches
2118 WHERE logical_batch_id = p_logical_batch_id
2119 AND physical_batch_id = p_batch_id)
2120 LOOP
2121
2122 -- Run create worksheet for this salesrep.
2123 l_worksheet_rec.salesrep_id := emp.salesrep_id;
2124 l_worksheet_rec.call_from := cn_payment_worksheet_pvt.concurrent_program_call;
2125
2126 fnd_file.put_line(fnd_file.log, ' Create worksheet for = ' || l_worksheet_rec.salesrep_id || ' salesrepID');
2127
2128 cn_payment_worksheet_pvt.create_worksheet(p_api_version => 1.0,
2129 p_init_msg_list => 'T',
2130 p_commit => 'F',
2131 p_validation_level => fnd_api.g_valid_level_full,
2132 x_return_status => x_return_status,
2133 x_msg_count => x_msg_count,
2134 x_msg_data => x_msg_data,
2135 p_worksheet_rec => l_worksheet_rec,
2136 x_loading_status => x_loading_status,
2137 x_status => x_status);
2138
2139 IF x_return_status <> fnd_api.g_ret_sts_success
2140 THEN
2141 l_error_count := l_error_count + 1;
2142 cn_message_pkg.debug('Error when creating Worksheet for : ' || l_worksheet_rec.salesrep_id);
2143 fnd_file.put_line(fnd_file.log, 'Failed to create worksheet for ' || l_worksheet_rec.salesrep_id);
2144 FOR i IN 1 .. x_msg_count
2145 LOOP
2146 fnd_file.put_line(fnd_file.log, 'msg: ' || fnd_msg_pub.get(i, 'F'));
2147 END LOOP;
2148 fnd_file.put_line(fnd_file.log, '+------------------------------+');
2149 ROLLBACK;
2150 ELSE
2151 COMMIT;
2152 END IF;
2153
2154 END LOOP;
2155
2156
2157 IF l_error_count <> 0
2158 THEN
2159 retcode := 2;
2160 errbuf := ' Batch# '||p_batch_id||' : Creation of worksheets was not successful for some resources. Count = ' || to_char(l_error_count) ;
2161 fnd_file.put_line(fnd_file.log,errbuf) ;
2162 END IF;
2163
2164 fnd_file.put_line(fnd_file.log, ' Finish time = ' || to_char(SYSDATE, 'Dy DD-Mon-YYYY HH24:MI:SS'));
2165 fnd_file.put_line(fnd_file.log, ' Batch time = ' || (SYSDATE - l_start_time) * 1400 || ' minutes ');
2166
2167 EXCEPTION
2168 WHEN OTHERS THEN
2169 fnd_file.put_line(fnd_file.log, 'Unexpected exception in processing the (payrun_id,batch) = ' || p_payrun_id || ',' || p_batch_id);
2170 fnd_file.put_line(fnd_file.log, SQLERRM);
2171 RAISE;
2172 END create_multiple_worksheets;
2173
2174
2175 --============================================================================
2176 --Name :create_worksheet_conc
2177 --Description : Procedure which will be used as the executable for the
2178 -- : concurrent program. Create Worksheet
2179 --============================================================================
2180 PROCEDURE create_mult_worksheet_conc
2181 (
2182 errbuf OUT NOCOPY VARCHAR2,
2183 retcode OUT NOCOPY NUMBER,
2184 p_name cn_payruns.NAME%TYPE
2185 ) IS
2186 l_proc_audit_id NUMBER;
2187 l_return_status VARCHAR2(1000);
2188 l_msg_data VARCHAR2(2000);
2189 l_msg_count NUMBER;
2190 l_loading_status VARCHAR2(1000);
2191 l_status VARCHAR2(2000);
2192 l_payrun_id NUMBER;
2193 --R12
2194 l_org_id cn_payruns.org_id%TYPE;
2195 l_conc_params conc_params;
2196 errmsg VARCHAR2(4000) := '';
2197 l_max_batch_id NUMBER;
2198 salesrep_t salesrep_tab_typ;
2199 l_batch_sz NUMBER := 80;
2200
2201 CURSOR get_payrun_id_curs(c_name cn_payruns.NAME%TYPE, c_org_id cn_payruns.org_id%TYPE) IS
2202 SELECT cp.payrun_id,
2203 cp.org_id,
2204 cp.status
2205 FROM cn_payruns cp
2206 WHERE cp.NAME = c_name
2207 AND cp.org_id = c_org_id;
2208
2209 CURSOR c_payrun_srp(c_payrun_id cn_payruns.payrun_id%TYPE, c_batch_sz number) IS
2210 SELECT salesrep_id,ceil(rownum / c_batch_sz) FROM (SELECT DISTINCT cns.salesrep_id salesrep_id,
2211 cns.NAME salesrep_name
2212 FROM cn_payruns cnp,
2213 cn_srp_pay_groups cnspg,
2214 cn_salesreps cns,
2215 cn_period_statuses cnps
2216 WHERE cnp.payrun_id = c_payrun_id
2217 AND cnp.status = 'UNPAID'
2218 AND cnp.pay_group_id = cnspg.pay_group_id
2219 AND cnspg.salesrep_id = cns.salesrep_id
2220 AND cns.hold_payment = 'N'
2221 AND cnp.pay_period_id = cnps.period_id
2222 AND cnp.org_id = cnps.org_id
2223 AND cnp.org_id = cnspg.org_id
2224 AND cnp.org_id = cns.org_id
2225 AND ((cnspg.start_date <= cnps.end_date) AND (cnps.start_date <= nvl(cnspg.end_date, cnps.start_date)))
2226 AND NOT EXISTS (SELECT 1
2227 FROM cn_payment_worksheets_all cnpw
2228 WHERE cnpw.salesrep_id = cnspg.salesrep_id
2229 AND cnp.payrun_id = cnpw.payrun_id)
2230 AND (EXISTS (SELECT 1
2231 FROM cn_srp_payee_assigns cnspa
2232 WHERE ((cnspa.start_date <= cnps.end_date) AND (cnps.start_date <= nvl(cnspa.end_date, cnps.start_date)))
2233 AND cnspa.payee_id = cnspg.salesrep_id
2234 --R12
2235 AND cnspa.org_id = cnp.org_id) OR EXISTS
2236 (SELECT 1
2237 FROM cn_srp_plan_assigns cnspa
2238 WHERE ((cnspa.start_date <= cnps.end_date) AND (cnps.start_date <= nvl(cnspa.end_date, cnps.start_date)))
2239 AND cnspa.salesrep_id = cnspg.salesrep_id
2240 --R12
2241 AND cnspa.org_id = cnp.org_id)));
2242
2243 l_has_access BOOLEAN;
2244 BEGIN
2245 fnd_file.put_line(fnd_file.log, 'Entering create_mult_worksheet_conc ');
2246 retcode := 0;
2247 --Added for R12 payment security check begin.
2248 l_has_access := cn_payment_security_pvt.get_security_access(cn_payment_security_pvt.g_type_wksht, cn_payment_security_pvt.g_access_wksht_create);
2249 --Get the salesrep batch size from profile option.
2250 l_batch_sz := nvl(fnd_profile.value('CN_PMT_SRP_BATCH_SIZE'),251);
2251 fnd_file.put_line(fnd_file.log,'Batch size : ' ||l_batch_sz);
2252
2253 IF l_batch_sz < 1
2254 THEN
2255 errmsg := 'The batch size should be greater than zero.';
2256 fnd_file.put_line(fnd_file.log, errmsg);
2257 raise_application_error(-20000, errmsg);
2258 END IF;
2259
2260 IF (l_has_access = FALSE) THEN
2261 RAISE fnd_api.g_exc_error;
2262 END IF;
2263
2264 --Added for R12 payment security check end.
2265 l_org_id := mo_global.get_current_org_id;
2266
2267
2268 -- get payrun id
2269 OPEN get_payrun_id_curs(p_name, l_org_id);
2270 FETCH get_payrun_id_curs
2271 INTO l_payrun_id, l_org_id,l_status;
2272 CLOSE get_payrun_id_curs;
2273 IF l_status <> 'UNPAID'
2274 THEN
2275 errmsg := 'Worksheets can only be created for payruns in UNPAID status.';
2276 fnd_file.put_line(fnd_file.log, errmsg);
2277 raise_application_error(-20000, errmsg);
2278 END IF;
2279 cn_message_pkg.begin_batch(x_process_type => 'WKSHT',
2280 x_process_audit_id => l_proc_audit_id,
2281 x_parent_proc_audit_id => l_payrun_id,
2282 x_request_id => NULL,
2283 --R12
2284 p_org_id => l_org_id);
2285 BEGIN
2286
2287 OPEN c_payrun_srp(l_payrun_id, l_batch_sz);
2288 LOOP
2289 FETCH c_payrun_srp
2290 BULK COLLECT INTO salesrep_t LIMIT 1000;
2291
2292 -- get the salesreps for the payrun.
2293 /*SELECT salesrep_id,ceil(rownum / l_batch_sz)
2294 BULK COLLECT INTO salesrep_t
2295 FROM (SELECT DISTINCT cns.salesrep_id salesrep_id,
2296 cns.NAME salesrep_name
2297 FROM cn_payruns cnp,
2298 cn_srp_pay_groups cnspg,
2299 cn_salesreps cns,
2300 cn_period_statuses cnps
2301 WHERE cnp.payrun_id = l_payrun_id
2302 AND cnp.status = 'UNPAID'
2303 AND cnp.pay_group_id = cnspg.pay_group_id
2304 AND cnspg.salesrep_id = cns.salesrep_id
2305 AND cns.hold_payment = 'N'
2306 AND cnp.pay_period_id = cnps.period_id
2307 AND cnp.org_id = cnps.org_id
2308 AND cnp.org_id = cnspg.org_id
2309 AND cnp.org_id = cns.org_id
2310 AND ((cnspg.start_date <= cnps.end_date) AND (cnps.start_date <= nvl(cnspg.end_date, cnps.start_date)))
2311 AND NOT EXISTS (SELECT 1
2312 FROM cn_payment_worksheets_all cnpw
2313 WHERE cnpw.salesrep_id = cnspg.salesrep_id
2314 AND cnp.payrun_id = cnpw.payrun_id)
2315 AND (EXISTS (SELECT 1
2316 FROM cn_srp_payee_assigns cnspa
2317 WHERE ((cnspa.start_date <= cnps.end_date) AND (cnps.start_date <= nvl(cnspa.end_date, cnps.start_date)))
2318 AND cnspa.payee_id = cnspg.salesrep_id
2319 --R12
2320 AND cnspa.org_id = cnp.org_id) OR EXISTS
2321 (SELECT 1
2322 FROM cn_srp_plan_assigns cnspa
2323 WHERE ((cnspa.start_date <= cnps.end_date) AND (cnps.start_date <= nvl(cnspa.end_date, cnps.start_date)))
2324 AND cnspa.salesrep_id = cnspg.salesrep_id
2325 --R12
2326 AND cnspa.org_id = cnp.org_id))); */
2327 -- Call the CN_CREATE_WKSHT_INT conc program
2328 l_conc_params.conc_program_name := 'CN_CREATE_WKSHT_INT' ;
2329
2330 generic_conc_processor(p_payrun_id => l_payrun_id,
2331 p_salesrep_tbl => salesrep_t,
2332 p_org_id => l_org_id,
2333 p_params => l_conc_params,
2334 x_errbuf => errbuf,
2335 x_retcode => retcode);
2336
2337 EXIT WHEN c_payrun_srp%NOTFOUND;
2338 END LOOP;
2339 CLOSE c_payrun_srp;
2340
2341 EXCEPTION
2342 WHEN no_data_found THEN
2343 errmsg := 'No salesreps found that were eligible for worksheet creation in the payrun : ';
2344 fnd_file.put_line(fnd_file.log, errmsg);
2345 retcode := 2;
2346 errbuf := errmsg;
2347 RAISE ;
2348 WHEN OTHERS THEN
2349 fnd_file.put_line(fnd_file.log, 'Unexpected exception in cn_payment_worksheet_pvt.create_mult_worksheet_conc');
2350 fnd_file.put_line(fnd_file.log, errmsg);
2351 fnd_file.put_line(fnd_file.log, SQLERRM);
2352 RAISE;
2353 END;
2354
2355
2356 fnd_file.put_line(fnd_file.log, errbuf);
2357 fnd_file.put_line(fnd_file.log, 'Count of worksheets to be created = ' || salesrep_t.COUNT);
2358 fnd_file.put_line(fnd_file.log, 'Completed create worksheet process....');
2359
2360 IF l_return_status <> fnd_api.g_ret_sts_success
2361 THEN
2362 retcode := 2;
2363 fnd_message.set_name('CN', 'CN_CONC_REQ_FAIL');
2364 fnd_msg_pub.add;
2365 errbuf := fnd_msg_pub.get(p_msg_index => fnd_msg_pub.g_last, p_encoded => fnd_api.g_false);
2366 END IF;
2367
2368 cn_message_pkg.end_batch(l_proc_audit_id);
2369 COMMIT;
2370 END create_mult_worksheet_conc;
2371 -- ===========================================================================
2372 -- Procedure : Update_Worksheet
2373 -- Description used for Refreshing the Worksheets
2374 -- Locking and Unlocking the Worksheets
2375 -- ===========================================================================
2376 PROCEDURE update_worksheet
2377 (
2378 p_api_version IN NUMBER,
2379 p_init_msg_list IN VARCHAR2,
2380 p_commit IN VARCHAR2,
2381 p_validation_level IN NUMBER,
2382 x_return_status OUT NOCOPY VARCHAR2,
2383 x_msg_count OUT NOCOPY NUMBER,
2384 x_msg_data OUT NOCOPY VARCHAR2,
2385 p_worksheet_id IN NUMBER,
2386 p_operation IN VARCHAR2,
2387 x_status OUT NOCOPY VARCHAR2,
2388 x_loading_status OUT NOCOPY VARCHAR2,
2389 x_ovn IN OUT NOCOPY NUMBER
2390 ) IS
2391 l_api_name CONSTANT VARCHAR2(30) := 'Update_Worksheet';
2392
2393 CURSOR get_worksheet_id IS
2394 SELECT cnpw.salesrep_id,
2395 cnp.payrun_id,
2396 cnpw.worksheet_status,
2397 cnp.pay_period_id,
2398 decode(cnp.incentive_type_code, 'ALL', '', cnp.incentive_type_code) incentive_type_code,
2399 cnp.pay_date,
2400 cnpw.object_version_number ovn,
2401 cnpw.org_id
2402 FROM cn_payment_worksheets cnpw,
2403 cn_payruns cnp
2404 WHERE payment_worksheet_id = p_worksheet_id
2405 AND cnpw.payrun_id = cnp.payrun_id;
2406
2407 -- changes for bug#2568937
2408 -- for payroll integration population of account
2409 CURSOR get_apps IS
2410 SELECT payables_flag,
2411 payroll_flag,
2412 payables_ccid_level
2413 FROM cn_repositories rp,
2414 cn_payment_worksheets wk
2415 WHERE rp.org_id = wk.org_id;
2416
2417 wksht_rec get_worksheet_id%ROWTYPE;
2418 l_status cn_payment_worksheets.worksheet_status%TYPE;
2419 l_posting_batch_id NUMBER;
2420 recv_posting_batch_id NUMBER;
2421 carryover_posting_batch_id NUMBER;
2422
2423 l_calc_rec_tbl calc_rec_tbl_type;
2424 l_batch_rec cn_prepostbatches.posting_batch_rec_type;
2425 l_calc_pmt_amount NUMBER;
2426 l_adj_pmt_amount_rec NUMBER;
2427 l_adj_pmt_amount_nrec NUMBER;
2428 l_pmt_amount_rec NUMBER;
2429 l_pmt_trans_rec cn_pmt_trans_pkg.pmt_trans_rec_type; -- PmtTrans
2430 l_pmt_amount_ctr NUMBER;
2431 l_rowid VARCHAR2(30);
2432 -- changes for bug#2568937
2433 -- for payroll integration population of account
2434 l_payables_flag cn_repositories.payables_flag%TYPE;
2435 l_payroll_flag cn_repositories.payroll_flag%TYPE;
2436 l_payables_ccid_level cn_repositories.payables_ccid_level%TYPE;
2437 l_ispayee NUMBER := 0;
2438 TYPE num_tab IS TABLE OF NUMBER;
2439 l_wk_plan_elements num_tab;
2440 l_has_access BOOLEAN;
2441 l_org_id NUMBER;
2442 l_pay_by_mode VARCHAR2(1);
2443 l_srp_status cn_salesreps.status%TYPE;
2444
2445 BEGIN
2446 -- Standard Start of API savepoint
2447 SAVEPOINT update_worksheet;
2448
2449 -- Standard call to check for call compatibility.
2450 IF NOT fnd_api.compatible_api_call(g_api_version, p_api_version, l_api_name, g_pkg_name)
2451 THEN
2452 RAISE fnd_api.g_exc_unexpected_error;
2453 END IF;
2454
2455 --
2456 -- Initialize message list if p_init_msg_list is set to TRUE.
2457 --
2458 IF fnd_api.to_boolean(p_init_msg_list)
2459 THEN
2460 fnd_msg_pub.initialize;
2461 END IF;
2462
2463 --
2464 -- Initialize API return status to success
2465 --
2466 x_return_status := fnd_api.g_ret_sts_success;
2467 x_loading_status := 'CN_UPDATED';
2468
2469 OPEN get_worksheet_id;
2470
2471 FETCH get_worksheet_id
2472 INTO wksht_rec;
2473
2474 CLOSE get_worksheet_id;
2475
2476 --This part is added for OA.
2477 IF wksht_rec.ovn <> x_ovn
2478 THEN
2479 IF (fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error))
2480 THEN
2481 fnd_message.set_name('CN', 'CN_RECORD_CHANGED');
2482 fnd_msg_pub.add;
2483 END IF;
2484
2485 RAISE fnd_api.g_exc_error;
2486 END IF;
2487
2488 l_pay_by_mode := cn_payment_security_pvt.get_pay_by_mode(wksht_rec.payrun_id);
2489
2490 SELECT s.status,
2491 nvl(r.payroll_flag, 'N'),
2492 r.payables_flag
2493 INTO l_srp_status,
2494 l_payroll_flag,
2495 l_payables_flag
2496 FROM cn_salesreps s,
2497 cn_repositories_all r,
2498 cn_payruns_all pr
2499 WHERE s.salesrep_id = wksht_rec.salesrep_id
2500 AND s.org_id = r.org_id
2501 AND pr.org_id = r.org_id
2502 AND pr.payrun_id = wksht_rec.payrun_id;
2503
2504 -- Bug 3140343 : Payee Design. Check if this salesrep is a Payee
2505 l_ispayee := cn_api.is_payee(p_period_id => wksht_rec.pay_period_id, p_salesrep_id => wksht_rec.salesrep_id, p_org_id => wksht_rec.org_id);
2506
2507 IF p_operation = 'REFRESH'
2508 THEN
2509
2510 --Added for R12 payment security check end.
2511 cn_payment_security_pvt.worksheet_action(p_api_version => p_api_version,
2512 p_init_msg_list => p_init_msg_list,
2513 p_commit => 'F',
2514 p_validation_level => p_validation_level,
2515 x_return_status => x_return_status,
2516 x_msg_count => x_msg_count,
2517 x_msg_data => x_msg_data,
2518 p_worksheet_id => p_worksheet_id,
2519 p_action => p_operation);
2520
2521 IF x_return_status <> fnd_api.g_ret_sts_success
2522 THEN
2523 RAISE fnd_api.g_exc_error;
2524 END IF;
2525
2526 -- get sequence number
2527 SELECT cn_posting_batches_s.NEXTVAL
2528 INTO l_posting_batch_id
2529 FROM dual;
2530
2531 -- Refresh payment transactions
2532 IF l_pay_by_mode = 'N'
2533 THEN
2534 -- Update amount on all payment transactions
2535 -- The following change was made by Sundar for bug fix 2772834
2536 -- This will handle scenarios where a salesrep has multiple role assignments
2537 -- during the same period, with an overlapping quota assignment
2538 -- changes records that have changed and not held
2539 UPDATE cn_payment_transactions cnpt
2540 SET ( amount, payment_amount) = (SELECT SUM(balance2_bbd - balance2_bbc + balance2_dtd - balance2_ctd),
2541 SUM(balance2_bbd - balance2_bbc + balance2_dtd - balance2_ctd)
2542 FROM cn_srp_periods csp
2543 WHERE csp.period_id = wksht_rec.pay_period_id
2544 AND csp.salesrep_id = cnpt.credited_salesrep_id
2545 AND csp.quota_id = cnpt.quota_id
2546 AND csp.credit_type_id = cnpt.credit_type_id
2547 --R12
2548 AND csp.org_id = wksht_rec.org_id),
2549 pay_element_type_id = (SELECT decode(r.payroll_flag, NULL, NULL, 'N', NULL, 'Y', p.pay_element_type_id, NULL) pay_element_type_id
2550 FROM cn_quota_pay_elements p,
2551 cn_rs_salesreps s,
2552 cn_repositories r
2553 WHERE p.quota_id = cnpt.quota_id
2554 AND wksht_rec.pay_date BETWEEN p.start_date AND p.end_date
2555 AND s.salesrep_id = cnpt.credited_salesrep_id
2556 AND nvl(s.status, 'A') = p.status
2557 --R12
2558 AND p.org_id = wksht_rec.org_id
2559 AND s.org_id = wksht_rec.org_id
2560 AND r.org_id = wksht_rec.org_id),
2561 last_update_date = SYSDATE,
2562 last_updated_by = fnd_global.user_id,
2563 last_update_login = fnd_global.login_id,
2564 object_version_number = nvl(object_version_number, 0) + 1
2565 WHERE cnpt.payrun_id = wksht_rec.payrun_id
2566 AND cnpt.amount = cnpt.payment_amount
2567 AND incentive_type_code IN ('COMMISSION', 'BONUS')
2568 -- 01/03/03 gasriniv added hold flag check for bug 2710066
2569 AND (cnpt.hold_flag IS NULL OR cnpt.hold_flag = 'N')
2570 AND cnpt.credited_salesrep_id = wksht_rec.salesrep_id
2571 --R12
2572 AND cnpt.org_id = wksht_rec.org_id;
2573
2574 -- for those records that have changed, dont update the payment amount
2575 UPDATE cn_payment_transactions cnpt
2576 SET amount = (SELECT SUM(balance2_bbd - balance2_bbc + balance2_dtd - balance2_ctd)
2577 FROM cn_srp_periods csp
2578 WHERE csp.period_id = wksht_rec.pay_period_id
2579 AND csp.salesrep_id = cnpt.credited_salesrep_id
2580 AND csp.quota_id = cnpt.quota_id
2581 AND csp.credit_type_id = cnpt.credit_type_id
2582 --R12
2583 AND csp.org_id = wksht_rec.org_id),
2584 pay_element_type_id = (SELECT decode(r.payroll_flag, NULL, NULL, 'N', NULL, 'Y', p.pay_element_type_id, NULL) pay_element_type_id
2585 FROM cn_quota_pay_elements p,
2586 cn_rs_salesreps s,
2587 cn_repositories r
2588 WHERE p.quota_id = cnpt.quota_id
2589 AND wksht_rec.pay_date BETWEEN p.start_date AND p.end_date
2590 AND s.salesrep_id = cnpt.credited_salesrep_id
2591 AND nvl(s.status, 'A') = p.status
2592 --R12
2593 AND p.org_id = wksht_rec.org_id
2594 AND s.org_id = wksht_rec.org_id
2595 AND r.org_id = wksht_rec.org_id),
2596 last_update_date = SYSDATE,
2597 last_updated_by = fnd_global.user_id,
2598 last_update_login = fnd_global.login_id
2599 WHERE cnpt.payrun_id = wksht_rec.payrun_id
2600 AND cnpt.amount <> cnpt.payment_amount
2601 AND incentive_type_code IN ('COMMISSION', 'BONUS')
2602 -- 01/03/03 gasriniv added hold flag check for bug 2710066
2603 AND (cnpt.hold_flag IS NULL OR cnpt.hold_flag = 'N')
2604 AND cnpt.credited_salesrep_id = wksht_rec.salesrep_id
2605 --R12
2606 AND cnpt.org_id = wksht_rec.org_id;
2607
2608 -- Bug 2868584 :Add SUM and Group By clause
2609 -- handle scenarios where a salesrep has multiple role assignments
2610 -- during the same period, with an overlapping quota assignment
2611 INSERT INTO cn_payment_transactions
2612 (payment_transaction_id,
2613 posting_batch_id,
2614 incentive_type_code,
2615 credit_type_id,
2616 pay_period_id,
2617 amount,
2618 payment_amount,
2619 credited_salesrep_id,
2620 payee_salesrep_id,
2621 paid_flag,
2622 hold_flag,
2623 waive_flag,
2624 payrun_id,
2625 quota_id,
2626 pay_element_type_id,
2627 created_by,
2628 creation_date,
2629 --R12
2630 org_id)
2631 SELECT cn_payment_transactions_s.NEXTVAL,
2632 l_posting_batch_id,
2633 v1.incentive_type_code,
2634 v1.credit_type_id,
2635 v1.period_id,
2636 v1.amount,
2637 v1.payment_amount,
2638 v1.salesrep_id,
2639 v1.salesrep_id,
2640 'N',
2641 'N',
2642 'N',
2643 wksht_rec.payrun_id,
2644 v1.quota_id,
2645 v1.pay_element_type_id,
2646 fnd_global.user_id,
2647 SYSDATE,
2648 --R12
2649 wksht_rec.org_id
2650 FROM (SELECT q.incentive_type_code,
2651 srp.credit_type_id,
2652 srp.period_id,
2653 SUM((nvl(srp.balance2_dtd, 0) - nvl(srp.balance2_ctd, 0) + nvl(srp.balance2_bbd, 0) - nvl(srp.balance2_bbc, 0))) amount,
2654 SUM((nvl(srp.balance2_dtd, 0) - nvl(srp.balance2_ctd, 0) + nvl(srp.balance2_bbd, 0) - nvl(srp.balance2_bbc, 0))) payment_amount,
2655 srp.salesrep_id,
2656 srp.quota_id,
2657 decode(r.payroll_flag, NULL, NULL, 'N', NULL, 'Y', qp.pay_element_type_id, NULL) pay_element_type_id
2658 FROM cn_srp_periods srp,
2659 cn_quotas_all q,
2660 cn_quota_pay_elements_all qp,
2661 cn_rs_salesreps s,
2662 cn_repositories r
2663 -- 01/03/03 gasriniv added hold flag check for bug 2710066
2664 WHERE srp.salesrep_id = wksht_rec.salesrep_id
2665 AND srp.period_id = wksht_rec.pay_period_id
2666 AND srp.quota_id = q.quota_id
2667 AND srp.quota_id <> -1000
2668 -- Bug 2819874
2669 AND srp.credit_type_id = -1000
2670 AND q.incentive_type_code = decode(nvl(wksht_rec.incentive_type_code, q.incentive_type_code),
2671 'COMMISSION',
2672 'COMMISSION',
2673 'BONUS',
2674 'BONUS',
2675 q.incentive_type_code)
2676 AND qp.quota_id(+) = srp.quota_id
2677 AND wksht_rec.pay_date BETWEEN qp.start_date(+) AND qp.end_date(+)
2678 AND s.salesrep_id = srp.salesrep_id
2679 AND nvl(s.status, 'A') = nvl(qp.status, nvl(s.status, 'A'))
2680 --R12
2681 AND srp.org_id = s.org_id
2682 AND srp.org_id = r.org_id
2683 AND srp.org_id = wksht_rec.org_id
2684 AND NOT EXISTS (SELECT 'X'
2685 FROM cn_payment_transactions_all cnpt
2686 WHERE cnpt.payrun_id = wksht_rec.payrun_id
2687 AND cnpt.credited_salesrep_id = wksht_rec.salesrep_id
2688 AND cnpt.quota_id = q.quota_id
2689 AND cnpt.incentive_type_code IN ('COMMISSION', 'BONUS')
2690 -- 01/03/03 gasriniv added hold flag check for bug 2710066
2691 AND (cnpt.hold_flag IS NULL OR cnpt.hold_flag = 'N')
2692 --R12
2693 AND cnpt.org_id = wksht_rec.org_id)
2694 GROUP BY srp.quota_id,
2695 q.incentive_type_code,
2696 srp.credit_type_id,
2697 srp.period_id,
2698 srp.salesrep_id,
2699 r.payroll_flag,
2700 qp.pay_element_type_id) v1;
2701
2702 IF SQL%ROWCOUNT <> 0
2703 THEN
2704 l_batch_rec.posting_batch_id := l_posting_batch_id;
2705 l_batch_rec.NAME := 'Refresh batch number:' || wksht_rec.payrun_id || ':' || wksht_rec.salesrep_id || ':' ||
2706 l_posting_batch_id;
2707 l_batch_rec.created_by := fnd_global.user_id;
2708 l_batch_rec.creation_date := SYSDATE;
2709 l_batch_rec.last_updated_by := fnd_global.user_id;
2710 l_batch_rec.last_update_date := SYSDATE;
2711 l_batch_rec.last_update_login := fnd_global.login_id;
2712 -- Create the Posting Batches
2713 cn_prepostbatches.begin_record(x_operation => 'INSERT',
2714 x_rowid => l_rowid,
2715 x_posting_batch_rec => l_batch_rec,
2716 x_program_type => NULL,
2717 p_org_id => wksht_rec.org_id);
2718 END IF;
2719
2720 -- Bug 2819874 :Add in carry over record if exist,regardless
2721 -- incentive type code
2722 -- get sequence number
2723 SELECT cn_posting_batches_s.NEXTVAL
2724 INTO carryover_posting_batch_id
2725 FROM dual;
2726
2727 INSERT INTO cn_payment_transactions
2728 (payment_transaction_id,
2729 posting_batch_id,
2730 incentive_type_code,
2731 credit_type_id,
2732 pay_period_id,
2733 amount,
2734 payment_amount,
2735 credited_salesrep_id,
2736 payee_salesrep_id,
2737 paid_flag,
2738 hold_flag,
2739 waive_flag,
2740 payrun_id,
2741 quota_id,
2742 pay_element_type_id,
2743 created_by,
2744 creation_date,
2745 --R12
2746 org_id)
2747 SELECT cn_payment_transactions_s.NEXTVAL,
2748 carryover_posting_batch_id,
2749 'COMMISSION',
2750 srp.credit_type_id,
2751 srp.period_id,
2752 nvl((nvl(srp.balance2_dtd, 0) - nvl(srp.balance2_ctd, 0) + nvl(srp.balance2_bbd, 0) - nvl(srp.balance2_bbc, 0)), 0),
2753 nvl((nvl(srp.balance2_dtd, 0) - nvl(srp.balance2_ctd, 0) + nvl(srp.balance2_bbd, 0) - nvl(srp.balance2_bbc, 0)), 0),
2754 srp.salesrep_id,
2755 srp.salesrep_id,
2756 'N',
2757 'N',
2758 'N',
2759 wksht_rec.payrun_id,
2760 -1000,
2761 decode(r.payroll_flag, NULL, NULL, 'N', NULL, 'Y', qp.pay_element_type_id, NULL) pay_element_type_id,
2762 fnd_global.user_id,
2763 SYSDATE,
2764 --R12
2765 wksht_rec.org_id
2766 FROM cn_srp_periods srp,
2767 cn_quota_pay_elements_all qp,
2768 cn_rs_salesreps s,
2769 cn_repositories r
2770 WHERE srp.salesrep_id = wksht_rec.salesrep_id
2771 AND srp.period_id = wksht_rec.pay_period_id
2772 AND srp.credit_type_id = -1000
2773 AND srp.quota_id = -1000
2774 AND nvl((nvl(srp.balance2_dtd, 0) - nvl(srp.balance2_ctd, 0) + nvl(srp.balance2_bbd, 0) - nvl(srp.balance2_bbc, 0)), 0) <> 0
2775 AND qp.quota_id(+) = srp.quota_id
2776 AND wksht_rec.pay_date BETWEEN qp.start_date(+) AND qp.end_date(+)
2777 AND s.salesrep_id = srp.salesrep_id
2778 AND nvl(s.status, 'A') = nvl(qp.status, nvl(s.status, 'A'))
2779 --R12
2780 AND srp.org_id = s.org_id
2781 AND srp.org_id = r.org_id
2782 AND srp.org_id = wksht_rec.org_id
2783 AND NOT EXISTS (SELECT 'X'
2784 FROM cn_payment_transactions cnpt
2785 WHERE cnpt.payrun_id = wksht_rec.payrun_id
2786 AND cnpt.credited_salesrep_id = wksht_rec.salesrep_id
2787 AND cnpt.quota_id = -1000
2788 -- 07/18/03 check exist only for commission/bonus
2789 AND cnpt.incentive_type_code IN ('COMMISSION', 'BONUS')
2790 AND (cnpt.hold_flag IS NULL OR cnpt.hold_flag = 'N'));
2791
2792 IF SQL%ROWCOUNT <> 0
2793 THEN
2794 l_batch_rec.posting_batch_id := carryover_posting_batch_id;
2795 l_batch_rec.NAME := 'Refresh batch number:' || wksht_rec.payrun_id || ':' || wksht_rec.salesrep_id || ':' ||
2796 carryover_posting_batch_id;
2797 l_batch_rec.created_by := fnd_global.user_id;
2798 l_batch_rec.creation_date := SYSDATE;
2799 l_batch_rec.last_updated_by := fnd_global.user_id;
2800 l_batch_rec.last_update_date := SYSDATE;
2801 l_batch_rec.last_update_login := fnd_global.login_id;
2802 -- Create the Posting Batches
2803 cn_prepostbatches.begin_record(x_operation => 'INSERT',
2804 x_rowid => l_rowid,
2805 x_posting_batch_rec => l_batch_rec,
2806 x_program_type => NULL,
2807 p_org_id => wksht_rec.org_id);
2808 END IF;
2809
2810 -- 01/03/03 gasriniv added hold flag check for bug 2710066
2811 UPDATE cn_payment_transactions cnpt
2812 SET ( amount, payment_amount) = (SELECT cnpt.amount - SUM(cnptheld.amount),
2813 cnpt.payment_amount - SUM(cnptheld.amount)
2814 FROM cn_payment_transactions cnptheld
2815 WHERE cnptheld.payrun_id = wksht_rec.payrun_id
2816 AND cnptheld.credited_salesrep_id = wksht_rec.salesrep_id
2817 AND cnptheld.quota_id = cnpt.quota_id
2818 AND cnptheld.hold_flag = 'Y'
2819 AND cnptheld.paid_flag = 'N'
2820 --R12
2821 AND cnptheld.org_id = wksht_rec.org_id),
2822 last_update_date = SYSDATE,
2823 last_updated_by = fnd_global.user_id,
2824 last_update_login = fnd_global.login_id
2825 WHERE cnpt.payrun_id = wksht_rec.payrun_id
2826 AND cnpt.credited_salesrep_id = wksht_rec.salesrep_id
2827 AND cnpt.hold_flag = 'N'
2828 AND cnpt.paid_flag = 'N'
2829 AND incentive_type_code IN ('COMMISSION', 'BONUS')
2830 --R12
2831 AND cnpt.org_id = wksht_rec.org_id
2832 AND EXISTS (SELECT 'X'
2833 FROM cn_payment_transactions cnptchk
2834 WHERE cnptchk.payrun_id = wksht_rec.payrun_id
2835 AND cnptchk.credited_salesrep_id = wksht_rec.salesrep_id
2836 AND cnptchk.quota_id = cnpt.quota_id
2837 AND cnptchk.hold_flag = 'Y'
2838 --R12
2839 AND cnptchk.org_id = wksht_rec.org_id);
2840 ELSE
2841 -- PBT
2842
2843 -- Bug 3140343 : Payee Design
2844 IF l_ispayee <> 1
2845 THEN
2846 -- IF PBT, then create all unposted lines
2847 -- Create new payment transactions for unposted payment transactions
2848 INSERT INTO cn_payment_transactions
2849 (payment_transaction_id,
2850 posting_batch_id,
2851 trx_type,
2852 payee_salesrep_id,
2853 role_id,
2854 incentive_type_code,
2855 credit_type_id,
2856 pay_period_id,
2857 amount,
2858 commission_header_id,
2859 commission_line_id,
2860 srp_plan_assign_id,
2861 quota_id,
2862 credited_salesrep_id,
2863 processed_period_id,
2864 quota_rule_id,
2865 event_factor,
2866 payment_factor,
2867 quota_factor,
2868 input_achieved,
2869 rate_tier_id,
2870 payee_line_id,
2871 commission_rate,
2872 hold_flag,
2873 paid_flag,
2874 waive_flag,
2875 recoverable_flag,
2876 payrun_id,
2877 payment_amount,
2878 pay_element_type_id,
2879 creation_date,
2880 created_by,
2881 --R12
2882 org_id,
2883 object_version_number,
2884 processed_date)
2885 SELECT
2886 cn_payment_transactions_s.NEXTVAL,
2887 l_posting_batch_id,
2888 cl.trx_type,
2889 cl.credited_salesrep_id,
2890 cl.role_id,
2891 pe.incentive_type_code,
2892 pe.credit_type_id,
2893 cl.pay_period_id,
2894 nvl(cl.commission_amount, 0),
2895 cl.commission_header_id,
2896 cl.commission_line_id,
2897 cl.srp_plan_assign_id,
2898 cl.quota_id,
2899 cl.credited_salesrep_id,
2900 cl.processed_period_id,
2901 cl.quota_rule_id,
2902 cl.event_factor,
2903 cl.payment_factor,
2904 cl.quota_factor,
2905 cl.input_achieved,
2906 cl.rate_tier_id,
2907 cl.payee_line_id,
2908 cl.commission_rate,
2909 'N',
2910 'N',
2911 'N',
2912 'N',
2913 wksht_rec.payrun_id,
2914 nvl(cl.commission_amount, 0),
2915 -- Bug 2875120 : remove cn_api function call in sql statement
2916 decode(l_payroll_flag, NULL, NULL, 'N', NULL, 'Y', qp.pay_element_type_id, NULL) pay_element_type_id,
2917 SYSDATE,
2918 fnd_global.user_id,
2919 --R12
2920 wksht_rec.org_id,
2921 1,
2922 cl.processed_date
2923 FROM cn_commission_lines cl,
2924 cn_quotas_all pe,
2925 cn_quota_pay_elements qp
2926 WHERE cl.credited_salesrep_id = wksht_rec.salesrep_id
2927 AND cl.processed_period_id <= wksht_rec.pay_period_id
2928 AND cl.processed_date <= wksht_rec.pay_date
2929 AND cl.status = 'CALC'
2930 AND cl.srp_payee_assign_id IS NULL
2931 AND cl.posting_status = 'UNPOSTED'
2932 AND cl.quota_id = pe.quota_id
2933 AND cl.credit_type_id = -1000
2934 AND pe.incentive_type_code = decode(nvl(wksht_rec.incentive_type_code, pe.incentive_type_code),
2935 'COMMISSION',
2936 'COMMISSION',
2937 'BONUS',
2938 'BONUS',
2939 pe.incentive_type_code)
2940 AND qp.quota_id(+) = cl.quota_id
2941 AND wksht_rec.pay_date BETWEEN qp.start_date(+) AND qp.end_date(+)
2942 AND nvl(l_srp_status, 'A') = nvl(qp.status, nvl(l_srp_status, 'A'))
2943 AND cl.org_id = wksht_rec.org_id;
2944
2945 ELSE
2946 -- refresh record for Payee. Get unposted trx from comm_lines
2947 INSERT INTO cn_payment_transactions
2948 (payment_transaction_id,
2949 posting_batch_id,
2950 trx_type,
2951 payee_salesrep_id,
2952 role_id,
2953 incentive_type_code,
2954 credit_type_id,
2955 pay_period_id,
2956 amount,
2957 commission_header_id,
2958 commission_line_id,
2959 srp_plan_assign_id,
2960 quota_id,
2961 credited_salesrep_id,
2962 processed_period_id,
2963 quota_rule_id,
2964 event_factor,
2965 payment_factor,
2966 quota_factor,
2967 input_achieved,
2968 rate_tier_id,
2969 payee_line_id,
2970 commission_rate,
2971 hold_flag,
2972 paid_flag,
2973 waive_flag,
2974 recoverable_flag,
2975 payrun_id,
2976 payment_amount,
2977 pay_element_type_id,
2978 creation_date,
2979 created_by,
2980 --R12
2981 org_id,
2982 object_version_number,
2983 processed_date)
2984 SELECT
2985 cn_payment_transactions_s.NEXTVAL,
2986 l_posting_batch_id,
2987 cl.trx_type,
2988 spayee.payee_id,
2989 cl.role_id,
2990 pe.incentive_type_code,
2991 pe.credit_type_id,
2992 cl.pay_period_id,
2993 nvl(cl.commission_amount, 0),
2994 cl.commission_header_id,
2995 cl.commission_line_id,
2996 cl.srp_plan_assign_id,
2997 cl.quota_id,
2998 spayee.payee_id,
2999 cl.processed_period_id,
3000 cl.quota_rule_id,
3001 cl.event_factor,
3002 cl.payment_factor,
3003 cl.quota_factor,
3004 cl.input_achieved,
3005 cl.rate_tier_id,
3006 cl.payee_line_id,
3007 cl.commission_rate,
3008 'N',
3009 'N',
3010 'N',
3011 'N',
3012 wksht_rec.payrun_id,
3013 nvl(cl.commission_amount, 0),
3014 -- Bug 2875120 : remove cn_api function call in sql statement
3015 decode(l_payroll_flag, NULL, NULL, 'N', NULL, 'Y', qp.pay_element_type_id, NULL) pay_element_type_id,
3016 SYSDATE,
3017 fnd_global.user_id,
3018 --R12
3019 wksht_rec.org_id,
3020 1,
3021 cl.processed_date
3022 FROM cn_commission_lines cl,
3023 cn_srp_payee_assigns_all spayee,
3024 cn_quotas_all pe,
3025 cn_quota_pay_elements_all qp
3026 WHERE cl.srp_payee_assign_id IS NOT NULL
3027 AND cl.srp_payee_assign_id = spayee.srp_payee_assign_id
3028 AND spayee.payee_id = wksht_rec.salesrep_id
3029 AND cl.credited_salesrep_id = spayee.salesrep_id
3030 AND cl.processed_period_id <= wksht_rec.pay_period_id
3031 AND cl.processed_date <= wksht_rec.pay_date
3032 AND cl.status = 'CALC'
3033 AND cl.posting_status = 'UNPOSTED'
3034 AND cl.quota_id = pe.quota_id
3035 AND cl.credit_type_id = -1000
3036 AND pe.incentive_type_code = decode(nvl(wksht_rec.incentive_type_code, pe.incentive_type_code),
3037 'COMMISSION',
3038 'COMMISSION',
3039 'BONUS',
3040 'BONUS',
3041 pe.incentive_type_code)
3042 AND qp.quota_id(+) = cl.quota_id
3043 AND wksht_rec.pay_date BETWEEN qp.start_date(+) AND qp.end_date(+)
3044 AND nvl(l_srp_status, 'A') = nvl(qp.status, nvl(l_srp_status, 'A'))
3045 AND cl.org_id = spayee.org_id
3046 AND cl.org_id = wksht_rec.org_id;
3047
3048 END IF;
3049 -- end IF l_ispayee <> 1 THEN
3050
3051 -- update payrun id on all payment transactions
3052 UPDATE cn_payment_transactions cnpt
3053 SET payrun_id = wksht_rec.payrun_id,
3054 pay_element_type_id = (SELECT decode(l_payroll_flag, 'Y', p.pay_element_type_id, NULL) pay_element_type_id
3055 FROM cn_quota_pay_elements p
3056 WHERE p.quota_id = cnpt.quota_id
3057 AND wksht_rec.pay_date BETWEEN p.start_date AND p.end_date
3058 AND nvl(l_srp_status, 'A') = nvl(p.status, nvl(l_srp_status, 'A'))),
3059 last_update_date = SYSDATE,
3060 last_updated_by = fnd_global.user_id,
3061 last_update_login = fnd_global.login_id,
3062 object_version_number = nvl(object_version_number, 0) + 1
3063 WHERE credited_salesrep_id = wksht_rec.salesrep_id
3064 AND pay_period_id <= wksht_rec.pay_period_id
3065 AND incentive_type_code =
3066 decode(nvl(wksht_rec.incentive_type_code, incentive_type_code), 'COMMISSION', 'COMMISSION', 'BONUS', 'BONUS', incentive_type_code)
3067 AND incentive_type_code IN ('COMMISSION', 'BONUS')
3068 AND payrun_id IS NULL
3069 AND processed_date <= wksht_rec.pay_date;
3070
3071 -- update pay_element_type_id
3072 UPDATE cn_payment_transactions cnpt
3073 SET pay_element_type_id = (SELECT decode(l_payroll_flag, 'Y', p.pay_element_type_id, NULL) pay_element_type_id
3074 FROM cn_quota_pay_elements p
3075 WHERE p.quota_id = decode(cnpt.incentive_type_code, 'PMTPLN_REC', -1001, cnpt.quota_id)
3076 AND wksht_rec.pay_date BETWEEN p.start_date AND p.end_date
3077 AND nvl(l_srp_status, 'A') = nvl(p.status, nvl(l_srp_status, 'A'))
3078 AND p.org_id = wksht_rec.org_id),
3079 last_update_date = SYSDATE,
3080 last_updated_by = fnd_global.user_id,
3081 last_update_login = fnd_global.login_id,
3082 object_version_number = nvl(object_version_number, 0) + 1
3083 WHERE credited_salesrep_id = wksht_rec.salesrep_id
3084 AND payrun_id = wksht_rec.payrun_id;
3085 END IF;
3086 -- end IF CN_PAY_BY_MODE = 'N'
3087
3088 -- calculate totals
3089 calculate_totals(p_salesrep_id => wksht_rec.salesrep_id,
3090 p_period_id => wksht_rec.pay_period_id,
3091 p_incentive_type => wksht_rec.incentive_type_code,
3092 p_payrun_id => wksht_rec.payrun_id,
3093 x_calc_rec_tbl => l_calc_rec_tbl,
3094 --R12
3095 p_org_id => wksht_rec.org_id);
3096
3097 -- Bug 2692801 : avoid PL/SQL error when l_calc_rec_tbl is null
3098 IF l_calc_rec_tbl.COUNT > 0
3099 THEN
3100 FOR i IN l_calc_rec_tbl.FIRST .. l_calc_rec_tbl.LAST
3101 LOOP
3102 IF l_calc_rec_tbl(i).quota_id IS NOT NULL
3103 THEN
3104 IF l_calc_rec_tbl(i).pmt_amount_adj_rec <> 0
3105 OR l_calc_rec_tbl(i).pmt_amount_adj_nrec <> 0
3106 THEN
3107 UPDATE cn_payment_transactions cnpt
3108 SET amount = l_calc_rec_tbl(i).pmt_amount_adj_rec + l_calc_rec_tbl(i).pmt_amount_adj_nrec,
3109 payment_amount = l_calc_rec_tbl(i).pmt_amount_adj_rec + l_calc_rec_tbl(i).pmt_amount_adj_nrec,
3110 pay_element_type_id = (SELECT decode(r.payroll_flag, NULL, NULL, 'N', NULL, 'Y', p.pay_element_type_id, NULL) pay_element_type_id
3111 FROM cn_quota_pay_elements p,
3112 cn_rs_salesreps s,
3113 cn_repositories r
3114 WHERE p.quota_id = cnpt.quota_id
3115 AND wksht_rec.pay_date BETWEEN p.start_date AND p.end_date
3116 AND s.salesrep_id = cnpt.credited_salesrep_id
3117 AND nvl(s.status, 'A') = p.status
3118 --R12
3119 AND p.org_id = wksht_rec.org_id
3120 AND s.org_id = wksht_rec.org_id
3121 AND r.org_id = wksht_rec.org_id),
3122 last_update_date = SYSDATE,
3123 last_updated_by = fnd_global.user_id,
3124 last_update_login = fnd_global.login_id,
3125 object_version_number = nvl(object_version_number, 0) + 1
3126 WHERE credited_salesrep_id = wksht_rec.salesrep_id
3127 AND payrun_id = wksht_rec.payrun_id
3128 AND incentive_type_code = 'PMTPLN'
3129 AND quota_id = l_calc_rec_tbl(i).quota_id
3130 --R12
3131 AND cnpt.org_id = wksht_rec.org_id;
3132
3133 IF SQL%ROWCOUNT = 0
3134 THEN
3135 -- Get the Sequence Number
3136 SELECT cn_posting_batches_s.NEXTVAL
3137 INTO recv_posting_batch_id
3138 FROM dual;
3139
3140 l_batch_rec.posting_batch_id := recv_posting_batch_id;
3141 l_batch_rec.NAME := 'PMTPLN batch number:' || wksht_rec.payrun_id || ':' || wksht_rec.salesrep_id || ':' ||
3142 l_calc_rec_tbl(i).quota_id || ':' || recv_posting_batch_id;
3143 l_batch_rec.created_by := fnd_global.user_id;
3144 l_batch_rec.creation_date := SYSDATE;
3145 l_batch_rec.last_updated_by := fnd_global.user_id;
3146 l_batch_rec.last_update_date := SYSDATE;
3147 l_batch_rec.last_update_login := fnd_global.login_id;
3148 -- Create the Posting Batches
3149 cn_prepostbatches.begin_record(x_operation => 'INSERT',
3150 x_rowid => l_rowid,
3151 x_posting_batch_rec => l_batch_rec,
3152 x_program_type => NULL,
3153 p_org_id => wksht_rec.org_id);
3154 l_pmt_trans_rec.posting_batch_id := recv_posting_batch_id;
3155 l_pmt_trans_rec.incentive_type_code := 'PMTPLN';
3156 l_pmt_trans_rec.credit_type_id := -1000;
3157 l_pmt_trans_rec.payrun_id := wksht_rec.payrun_id;
3158 l_pmt_trans_rec.credited_salesrep_id := wksht_rec.salesrep_id;
3159 l_pmt_trans_rec.payee_salesrep_id := wksht_rec.salesrep_id;
3160 l_pmt_trans_rec.pay_period_id := wksht_rec.pay_period_id;
3161 l_pmt_trans_rec.hold_flag := 'N';
3162 l_pmt_trans_rec.waive_flag := 'N';
3163 l_pmt_trans_rec.paid_flag := 'N';
3164 l_pmt_trans_rec.recoverable_flag := 'N';
3165 l_pmt_trans_rec.quota_id := l_calc_rec_tbl(i).quota_id;
3166 l_pmt_trans_rec.amount := nvl(l_calc_rec_tbl(i).pmt_amount_adj_rec, 0) +
3167 nvl(l_calc_rec_tbl(i).pmt_amount_adj_nrec, 0);
3168 l_pmt_trans_rec.payment_amount := nvl(l_calc_rec_tbl(i).pmt_amount_adj_rec, 0) +
3169 nvl(l_calc_rec_tbl(i).pmt_amount_adj_nrec, 0);
3170 --R12
3171 l_pmt_trans_rec.org_id := wksht_rec.org_id;
3172 l_pmt_trans_rec.object_version_number := 1;
3173 l_pmt_trans_rec.pay_element_type_id := cn_api.get_pay_element_id(l_calc_rec_tbl(i).quota_id,
3174 wksht_rec.salesrep_id,
3175 wksht_rec.org_id,
3176 wksht_rec.pay_date);
3177 -- Create the Payment Plan Record
3178 cn_pmt_trans_pkg.insert_record(p_tran_rec => l_pmt_trans_rec);
3179 END IF;
3180 ELSE
3181 UPDATE cn_payment_transactions cnpt
3182 SET amount = 0,
3183 payment_amount = 0,
3184 pay_element_type_id = (SELECT decode(r.payroll_flag, NULL, NULL, 'N', NULL, 'Y', p.pay_element_type_id, NULL) pay_element_type_id
3185 FROM cn_quota_pay_elements p,
3186 cn_rs_salesreps s,
3187 cn_repositories r
3188 WHERE p.quota_id = cnpt.quota_id
3189 AND wksht_rec.pay_date BETWEEN p.start_date AND p.end_date
3190 AND s.salesrep_id = cnpt.credited_salesrep_id
3191 AND nvl(s.status, 'A') = p.status
3192 --R12
3193 AND p.org_id = wksht_rec.org_id
3194 AND s.org_id = wksht_rec.org_id
3195 AND r.org_id = wksht_rec.org_id),
3196 last_update_date = SYSDATE,
3197 last_updated_by = fnd_global.user_id,
3198 last_update_login = fnd_global.login_id,
3199 object_version_number = nvl(object_version_number, 0) + 1
3200 WHERE incentive_type_code = 'PMTPLN'
3201 AND payrun_id = wksht_rec.payrun_id
3202 AND credited_salesrep_id = wksht_rec.salesrep_id
3203 AND quota_id = l_calc_rec_tbl(i).quota_id
3204 --R12
3205 AND cnpt.org_id = wksht_rec.org_id;
3206 END IF; -- End IF l_calc_rec_tbl(i).pmt_amount_adj_rec <> 0
3207
3208 -- Update the Worksheet at the Quota Level
3209 UPDATE cn_payment_worksheets
3210 SET pmt_amount_calc = nvl(l_calc_rec_tbl(i).pmt_amount_calc, 0),
3211 pmt_amount_adj_rec = nvl(l_calc_rec_tbl(i).pmt_amount_adj_rec, 0),
3212 pmt_amount_adj_nrec = nvl(l_calc_rec_tbl(i).pmt_amount_adj_nrec, 0),
3213 pmt_amount_adj = nvl(l_calc_rec_tbl(i).pmt_amount_ctr, 0),
3214 pmt_amount_recovery = nvl(l_calc_rec_tbl(i).pmt_amount_rec, 0),
3215 last_update_date = SYSDATE,
3216 last_updated_by = fnd_global.user_id,
3217 last_update_login = fnd_global.login_id,
3218 object_version_number = nvl(object_version_number, 0) + 1
3219 WHERE payrun_id = wksht_rec.payrun_id
3220 AND salesrep_id = wksht_rec.salesrep_id
3221 AND quota_id = l_calc_rec_tbl(i).quota_id;
3222
3223 IF SQL%ROWCOUNT = 0
3224 AND (l_calc_rec_tbl(i)
3225 .quota_id <> -1000 OR abs(nvl(l_calc_rec_tbl(i).pmt_amount_calc, 0)) + abs(nvl(l_calc_rec_tbl(i).pmt_amount_adj_rec, 0)) +
3226 abs(nvl(l_calc_rec_tbl(i).pmt_amount_adj_nrec, 0)) + abs(nvl(l_calc_rec_tbl(i).pmt_amount_ctr, 0)) +
3227 abs(nvl(l_calc_rec_tbl(i).pmt_amount_rec, 0)) <> 0)
3228 THEN
3229 -- Create the Worksheet at the Quota Level
3230 cn_payment_worksheets_pkg.insert_record(x_payrun_id => wksht_rec.payrun_id,
3231 x_salesrep_id => wksht_rec.salesrep_id,
3232 x_quota_id => l_calc_rec_tbl(i).quota_id,
3233 x_credit_type_id => -1000,
3234 x_calc_pmt_amount => nvl(l_calc_rec_tbl(i).pmt_amount_calc, 0),
3235 x_adj_pmt_amount_rec => nvl(l_calc_rec_tbl(i).pmt_amount_adj_rec, 0),
3236 x_adj_pmt_amount_nrec => nvl(l_calc_rec_tbl(i).pmt_amount_adj_nrec, 0),
3237 x_adj_pmt_amount => nvl(l_calc_rec_tbl(i).pmt_amount_ctr, 0),
3238 x_pmt_amount_recovery => nvl(l_calc_rec_tbl(i).pmt_amount_rec, 0),
3239 x_worksheet_status => 'UNPAID',
3240 x_created_by => fnd_global.user_id,
3241 x_creation_date => SYSDATE,
3242 p_org_id => wksht_rec.org_id,
3243 p_object_version_number => 1);
3244 END IF;
3245 END IF; -- End IF l_calc_rec_tbl(i).quota_id is NOT NULL
3246
3247 -- for summary record
3248 l_calc_pmt_amount := nvl(l_calc_pmt_amount, 0) + nvl(l_calc_rec_tbl(i).pmt_amount_calc, 0);
3249 l_adj_pmt_amount_rec := nvl(l_adj_pmt_amount_rec, 0) + nvl(l_calc_rec_tbl(i).pmt_amount_adj_rec, 0);
3250 l_adj_pmt_amount_nrec := nvl(l_adj_pmt_amount_nrec, 0) + nvl(l_calc_rec_tbl(i).pmt_amount_adj_nrec, 0);
3251 l_pmt_amount_rec := nvl(l_pmt_amount_rec, 0) + nvl(l_calc_rec_tbl(i).pmt_amount_rec, 0);
3252 l_pmt_amount_ctr := nvl(l_pmt_amount_ctr, 0) + nvl(l_calc_rec_tbl(i).pmt_amount_ctr, 0);
3253 END LOOP;
3254 END IF; -- end IF l_calc_rec_tbl.COUNT > 0 THEN
3255
3256 -- UPDATE the Summary Record in the Worksheet
3257 UPDATE cn_payment_worksheets
3258 SET pmt_amount_calc = l_calc_pmt_amount,
3259 pmt_amount_adj_rec = l_adj_pmt_amount_rec,
3260 pmt_amount_adj_nrec = l_adj_pmt_amount_nrec,
3261 pmt_amount_adj = l_pmt_amount_ctr,
3262 pmt_amount_recovery = l_pmt_amount_rec,
3263 last_update_date = SYSDATE,
3264 last_updated_by = fnd_global.user_id,
3265 last_update_login = fnd_global.login_id,
3266 object_version_number = nvl(object_version_number, 0) + 1
3267 WHERE payrun_id = wksht_rec.payrun_id
3268 AND salesrep_id = wksht_rec.salesrep_id
3269 AND quota_id IS NULL;
3270
3271 update_ptd_details (
3272 p_salesrep_id => wksht_rec.salesrep_id ,
3273 p_payrun_id => wksht_rec.payrun_id
3274 ) ;
3275
3276 -- Bug 3140343 : Payee Design. set commission_lines to POSTED
3277 IF l_pay_by_mode = 'Y'
3278 THEN
3279 -- Bug 3191079 by jjhuang.
3280 IF l_ispayee <> 1
3281 THEN
3282 UPDATE cn_commission_lines cls
3283 SET posting_status = 'POSTED',
3284 last_update_date = SYSDATE,
3285 last_updated_by = fnd_global.user_id,
3286 last_update_login = fnd_global.login_id
3287 WHERE posting_status <> 'POSTED'
3288 AND status = 'CALC'
3289 AND srp_payee_assign_id IS NULL
3290 AND commission_line_id IN (SELECT commission_line_id
3291 FROM cn_payment_transactions
3292 WHERE posting_batch_id = l_posting_batch_id);
3293
3294 ELSE
3295 -- payee
3296 UPDATE cn_commission_lines cls
3297 SET posting_status = 'POSTED',
3298 last_update_date = SYSDATE,
3299 last_updated_by = fnd_global.user_id,
3300 last_update_login = fnd_global.login_id
3301 WHERE posting_status <> 'POSTED'
3302 AND status = 'CALC'
3303 AND srp_payee_assign_id IS NOT NULL
3304 AND commission_line_id IN (SELECT commission_line_id
3305 FROM cn_payment_transactions
3306 WHERE posting_batch_id = l_posting_batch_id);
3307 END IF;
3308 ELSE
3309
3310 SELECT DISTINCT pw.quota_id
3311 BULK COLLECT INTO l_wk_plan_elements
3312 FROM cn_payment_worksheets pw
3313 WHERE pw.payrun_id = wksht_rec.payrun_id
3314 AND pw.salesrep_id = wksht_rec.salesrep_id
3315 AND pw.quota_id IS NOT NULL ;
3316
3317 --PBS
3318 IF l_ispayee <> 1
3319 THEN
3320
3321 FORALL m IN 1..l_wk_plan_elements.COUNT
3322 UPDATE cn_commission_lines cls
3323 SET posting_status = 'POSTED',
3324 last_update_date = SYSDATE,
3325 last_updated_by = fnd_global.user_id,
3326 last_update_login = fnd_global.login_id
3327 WHERE posting_status <> 'POSTED'
3328 AND credit_type_id = g_credit_type_id
3329 AND processed_period_id <= wksht_rec.pay_period_id
3330 AND status = 'CALC'
3331 AND srp_payee_assign_id IS NULL
3332 --R12
3333 AND org_id = wksht_rec.org_id
3334 AND credited_salesrep_id = wksht_rec.salesrep_id
3335 AND quota_id = l_wk_plan_elements(m);
3336
3337 ELSE
3338 UPDATE cn_commission_lines clk
3339 SET posting_status = 'POSTED',
3340 last_update_date = SYSDATE,
3341 last_updated_by = fnd_global.user_id,
3342 last_update_login = fnd_global.login_id
3343 WHERE processed_period_id <= wksht_rec.pay_period_id
3344 AND status = 'CALC'
3345 AND credit_type_id = g_credit_type_id
3346 AND posting_status <> 'POSTED'
3347 AND org_id = wksht_rec.org_id
3348 AND clk.srp_payee_assign_id IS NOT NULL
3349 AND EXISTS (SELECT 1
3350 FROM cn_srp_payee_assigns_all spayee,
3351 cn_payment_worksheets wksht
3352 WHERE clk.srp_payee_assign_id = spayee.srp_payee_assign_id
3353 AND spayee.quota_id = wksht.quota_id
3354 AND spayee.payee_id = wksht_rec.salesrep_id
3355 AND wksht.payrun_id = wksht_rec.payrun_id
3356 AND wksht.salesrep_id = wksht_rec.salesrep_id);
3357
3358 END IF; -- end IF l_ispayee <> 1
3359 END IF; -- end IF l_pbt_profile_value = 'Y'
3360
3361 -- for payroll integration population of account
3362 -- changes for bug#2568937
3363 -- use if AP / Payroll integration has been enabled.
3364 IF l_payables_flag = 'Y'
3365 THEN
3366 -- Populate ccid's in payment worksheets
3367 IF (cn_payrun_pvt.populate_ccids(p_payrun_id => wksht_rec.payrun_id,
3368 p_salesrep_id => wksht_rec.salesrep_id,
3369 p_loading_status => x_loading_status,
3370 x_loading_status => x_loading_status)) = fnd_api.g_true
3371 THEN
3372 RAISE fnd_api.g_exc_unexpected_error;
3373 END IF;
3374 END IF;
3375
3376 ELSIF p_operation IN ('LOCK', 'RELEASE_HOLD')
3377 THEN
3378
3379 cn_payment_security_pvt.worksheet_action(p_api_version => p_api_version,
3380 p_init_msg_list => p_init_msg_list,
3381 p_commit => 'F',
3382 p_validation_level => p_validation_level,
3383 x_return_status => x_return_status,
3384 x_msg_count => x_msg_count,
3385 x_msg_data => x_msg_data,
3386 p_worksheet_id => p_worksheet_id,
3387 p_action => p_operation,
3388 p_do_audit => fnd_api.g_false);
3389
3390 IF x_return_status <> fnd_api.g_ret_sts_success
3391 THEN
3392 RAISE fnd_api.g_exc_error;
3393 END IF;
3394
3395 IF p_operation = 'LOCK'
3396 THEN
3397
3398 -- save current image if LOCK worksheet
3399 set_ced_and_bb(p_api_version => 1.0,
3400 x_return_status => x_return_status,
3401 x_msg_count => x_msg_count,
3402 x_msg_data => x_msg_data,
3403 p_worksheet_id => p_worksheet_id);
3404
3405 IF x_return_status <> fnd_api.g_ret_sts_success
3406 THEN
3407 RAISE fnd_api.g_exc_error;
3408 END IF;
3409 ELSIF p_operation = 'RELEASE_HOLD'
3410 THEN
3411
3412 -- Call api to release all hold pmt trx
3413 cn_pmt_trans_pvt.release_wksht_hold(p_api_version => p_api_version,
3414 p_init_msg_list => p_init_msg_list,
3415 p_commit => 'F',
3416 p_validation_level => p_validation_level,
3417 x_return_status => x_return_status,
3418 x_msg_count => x_msg_count,
3419 x_msg_data => x_msg_data,
3420 p_payment_worksheet_id => p_worksheet_id);
3421
3422 IF x_return_status <> fnd_api.g_ret_sts_success
3423 THEN
3424 RAISE fnd_api.g_exc_error;
3425 END IF;
3426 END IF;
3427
3428 -- set wksht audit
3429 cn_payment_security_pvt.worksheet_audit(p_worksheet_id => p_worksheet_id,
3430 p_payrun_id => wksht_rec.payrun_id,
3431 p_salesrep_id => wksht_rec.salesrep_id,
3432 p_action => p_operation,
3433 x_return_status => x_return_status,
3434 x_msg_count => x_msg_count,
3435 x_msg_data => x_msg_data);
3436
3437 IF x_return_status <> fnd_api.g_ret_sts_success
3438 THEN
3439 RAISE fnd_api.g_exc_error;
3440 END IF;
3441 ELSIF p_operation IN ('UNLOCK', 'SUBMIT')
3442 THEN
3443 -- 'UNLOCK', 'SUBMIT'
3444 cn_payment_security_pvt.worksheet_action(p_api_version => p_api_version,
3445 p_init_msg_list => p_init_msg_list,
3446 p_commit => 'F',
3447 p_validation_level => p_validation_level,
3448 x_return_status => x_return_status,
3449 x_msg_count => x_msg_count,
3450 x_msg_data => x_msg_data,
3451 p_worksheet_id => p_worksheet_id,
3452 p_action => p_operation);
3453
3454 IF x_return_status <> fnd_api.g_ret_sts_success
3455 THEN
3456 RAISE fnd_api.g_exc_error;
3457 END IF;
3458 ELSIF p_operation IN ('APPROVE', 'REJECT')
3459 THEN
3460 cn_payment_security_pvt.worksheet_action(p_api_version => p_api_version,
3461 p_init_msg_list => p_init_msg_list,
3462 p_commit => 'F',
3463 p_validation_level => p_validation_level,
3464 x_return_status => x_return_status,
3465 x_msg_count => x_msg_count,
3466 x_msg_data => x_msg_data,
3467 p_worksheet_id => p_worksheet_id,
3468 p_action => p_operation,
3469 p_do_audit => fnd_api.g_true);
3470
3471 IF x_return_status <> fnd_api.g_ret_sts_success
3472 THEN
3473 RAISE fnd_api.g_exc_error;
3474 END IF;
3475 ELSIF p_operation IN ('HOLD_ALL', 'RELEASE_ALL', 'RESET_TO_UNPAID')
3476 THEN
3477 cn_payment_security_pvt.worksheet_action(p_api_version => p_api_version,
3478 p_init_msg_list => p_init_msg_list,
3479 p_commit => 'F',
3480 p_validation_level => p_validation_level,
3481 x_return_status => x_return_status,
3482 x_msg_count => x_msg_count,
3483 x_msg_data => x_msg_data,
3484 p_worksheet_id => p_worksheet_id,
3485 p_action => p_operation,
3486 p_do_audit => fnd_api.g_true);
3487
3488 IF x_return_status <> fnd_api.g_ret_sts_success
3489 THEN
3490 RAISE fnd_api.g_exc_error;
3491 END IF;
3492 END IF;
3493
3494 --Update object_version_number
3495 UPDATE cn_payment_worksheets
3496 SET object_version_number = nvl(object_version_number, 0) + 1,
3497 last_update_date = SYSDATE,
3498 last_updated_by = fnd_global.user_id,
3499 last_update_login = fnd_global.login_id
3500 WHERE (payrun_id, salesrep_id) IN (SELECT payrun_id,
3501 salesrep_id
3502 FROM cn_payment_worksheets
3503 WHERE payment_worksheet_id = p_worksheet_id);
3504
3505 SELECT object_version_number
3506 INTO x_ovn
3507 FROM cn_payment_worksheets
3508 WHERE payment_worksheet_id = p_worksheet_id;
3509
3510 -- End of API body.
3511 -- Standard check of p_commit.
3512 IF fnd_api.to_boolean(p_commit)
3513 THEN
3514 COMMIT WORK;
3515 END IF;
3516
3517 -- Standard call to get message count and if count is 1, get message info.
3518 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
3519 EXCEPTION
3520 WHEN fnd_api.g_exc_error THEN
3521 ROLLBACK TO update_worksheet;
3522 x_return_status := fnd_api.g_ret_sts_error;
3523 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
3524 WHEN fnd_api.g_exc_unexpected_error THEN
3525 ROLLBACK TO update_worksheet;
3526 x_loading_status := 'UNEXPECTED_ERR';
3527 x_return_status := fnd_api.g_ret_sts_unexp_error;
3528 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
3529 WHEN OTHERS THEN
3530 ROLLBACK TO update_worksheet;
3531 x_loading_status := 'UNEXPECTED_ERR';
3532 x_return_status := fnd_api.g_ret_sts_unexp_error;
3533
3534 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)
3535 THEN
3536 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
3537 END IF;
3538
3539 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
3540 END update_worksheet;
3541
3542 -- ===========================================================================
3543 -- Procedure : delete_worksheet
3544 -- Description :
3545 -- ===========================================================================
3546 PROCEDURE delete_worksheet
3547 (
3548 p_api_version IN NUMBER,
3549 p_init_msg_list IN VARCHAR2,
3550 p_commit IN VARCHAR2,
3551 p_validation_level IN NUMBER,
3552 x_return_status OUT NOCOPY VARCHAR2,
3553 x_msg_count OUT NOCOPY NUMBER,
3554 x_msg_data OUT NOCOPY VARCHAR2,
3555 p_worksheet_id IN NUMBER,
3556 p_validation_only IN VARCHAR2,
3557 x_status OUT NOCOPY VARCHAR2,
3558 x_loading_status OUT NOCOPY VARCHAR2,
3559 p_ovn IN NUMBER
3560 ) IS
3561 l_api_name CONSTANT VARCHAR2(30) := 'Delete_Worksheet';
3562 l_profile_value VARCHAR2(02);
3563
3564 CURSOR get_worksheet_dtls IS
3565 SELECT wk.salesrep_id,
3566 wk.payrun_id,
3567 wk.org_id,
3568 pr.payrun_mode
3569 FROM cn_payment_worksheets wk,
3570 cn_payruns pr
3571 WHERE payment_worksheet_id = p_worksheet_id
3572 AND wk.payrun_id = pr.payrun_id;
3573
3574 --R12 for OA.
3575 l_validation_only VARCHAR2(1);
3576 l_has_access BOOLEAN;
3577 l_ovn NUMBER;
3578 BEGIN
3579 --
3580 -- Standard Start of API savepoint
3581 --
3582 SAVEPOINT delete_worksheet;
3583
3584 --
3585 -- Standard call to check for call compatibility.
3586 --
3587 IF NOT fnd_api.compatible_api_call(g_api_version, p_api_version, l_api_name, g_pkg_name)
3588 THEN
3589 RAISE fnd_api.g_exc_unexpected_error;
3590 END IF;
3591
3592 --
3593 -- Initialize message list if p_init_msg_list is set to TRUE.
3594 --
3595 IF fnd_api.to_boolean(p_init_msg_list)
3596 THEN
3597 fnd_msg_pub.initialize;
3598 END IF;
3599
3600 --
3601 -- Initialize API return status to success
3602 --
3603 x_return_status := fnd_api.g_ret_sts_success;
3604 x_loading_status := 'CN_DELETED';
3605
3606 -- API body
3607 --R12 for OA. When p_validation_only = 'Y', only do validation for delete from OA.
3608 l_validation_only := nvl(p_validation_only, 'N');
3609
3610 FOR wksht IN get_worksheet_dtls
3611 LOOP
3612 cn_payment_security_pvt.worksheet_action(p_api_version => p_api_version,
3613 p_init_msg_list => p_init_msg_list,
3614 p_commit => 'F',
3615 p_validation_level => p_validation_level,
3616 x_return_status => x_return_status,
3617 x_msg_count => x_msg_count,
3618 x_msg_data => x_msg_data,
3619 p_worksheet_id => p_worksheet_id,
3620 p_action => 'REMOVE',
3621 p_do_audit => fnd_api.g_false);
3622
3623 IF x_return_status <> fnd_api.g_ret_sts_success
3624 THEN
3625 RAISE fnd_api.g_exc_error;
3626 END IF;
3627
3628 --R12
3629 EXIT WHEN l_validation_only = 'Y';
3630
3631 UPDATE cn_payment_transactions
3632 SET payrun_id = NULL,
3633 waive_flag = 'N',
3634 last_update_date = SYSDATE,
3635 last_updated_by = fnd_global.user_id,
3636 last_update_login = fnd_global.login_id
3637 WHERE payrun_id = wksht.payrun_id
3638 AND credited_salesrep_id = wksht.salesrep_id
3639 AND incentive_type_code = 'PMTPLN_REC';
3640
3641 -- Bug 2760379 : Do not reset cn_commission_lines
3642 DELETE FROM cn_payment_transactions
3643 WHERE incentive_type_code IN ('PMTPLN', 'MANUAL_PAY_ADJ')
3644 AND payrun_id = wksht.payrun_id
3645 AND credited_salesrep_id = wksht.salesrep_id;
3646
3647 -- Bug 2715543
3648 IF wksht.payrun_mode = 'Y'
3649 THEN
3650 -- Bug 2760379 : Do not delete from cn_payment_transactions,
3651 -- just set the payrun_id to null
3652 -- 3. Set payrun_id to null for remaining tr
3653 -- Bug 2795606 : reset paymnet_amount when delete wkshtx
3654 UPDATE cn_payment_transactions
3655 SET payrun_id = NULL,
3656 payment_amount = amount,
3657 last_update_date = SYSDATE,
3658 last_updated_by = fnd_global.user_id,
3659 last_update_login = fnd_global.login_id
3660 WHERE payrun_id = wksht.payrun_id
3661 AND credited_salesrep_id = wksht.salesrep_id
3662 AND commission_line_id IS NOT NULL;
3663 ELSE
3664 -- Delete cn_payment_transactions for Pay by Summary
3665 DELETE FROM cn_payment_transactions
3666 WHERE payrun_id = wksht.payrun_id
3667 AND credited_salesrep_id = wksht.salesrep_id
3668 AND nvl(hold_flag, 'N') = 'N';
3669
3670 UPDATE cn_payment_transactions
3671 SET payrun_id = '',
3672 last_update_date = SYSDATE,
3673 last_updated_by = fnd_global.user_id,
3674 last_update_login = fnd_global.login_id
3675 WHERE payrun_id = wksht.payrun_id
3676 AND credited_salesrep_id = wksht.salesrep_id
3677 AND nvl(hold_flag, 'N') = 'Y';
3678 END IF;
3679
3680 -- Delete the Posting Batches
3681 DELETE FROM cn_posting_batches cnpb
3682 WHERE cnpb.posting_batch_id IN (SELECT cnpd.posting_batch_id
3683 FROM cn_payment_transactions cnpd
3684 WHERE cnpd.payrun_id = wksht.payrun_id
3685 AND cnpd.credited_salesrep_id = wksht.salesrep_id
3686 AND nvl(cnpd.hold_flag, 'N') = 'N');
3687
3688 -- add notes and audit
3689 cn_payment_security_pvt.worksheet_audit(p_worksheet_id => p_worksheet_id,
3690 p_payrun_id => wksht.payrun_id,
3691 p_salesrep_id => wksht.salesrep_id,
3692 p_action => 'REMOVE',
3693 x_return_status => x_return_status,
3694 x_msg_count => x_msg_count,
3695 x_msg_data => x_msg_data);
3696
3697 -- Delete the Worksheets
3698 cn_payment_worksheets_pkg.delete_record(p_salesrep_id => wksht.salesrep_id, p_payrun_id => wksht.payrun_id);
3699 END LOOP;
3700
3701 -- End of API body.
3702 -- Standard check of p_commit.
3703 IF fnd_api.to_boolean(p_commit)
3704 THEN
3705 COMMIT WORK;
3706 END IF;
3707
3708 --
3709 -- Standard call to get message count and if count is 1, get message info.
3710 --
3711 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
3712 EXCEPTION
3713 WHEN fnd_api.g_exc_error THEN
3714 ROLLBACK TO delete_worksheet;
3715 x_return_status := fnd_api.g_ret_sts_error;
3716 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
3717 WHEN fnd_api.g_exc_unexpected_error THEN
3718 ROLLBACK TO delete_worksheet;
3719 x_loading_status := 'UNEXPECTED_ERR';
3720 x_return_status := fnd_api.g_ret_sts_unexp_error;
3721 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
3722 WHEN OTHERS THEN
3723 ROLLBACK TO delete_worksheet;
3724 x_loading_status := 'UNEXPECTED_ERR';
3725 x_return_status := fnd_api.g_ret_sts_unexp_error;
3726
3727 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)
3728 THEN
3729 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
3730 END IF;
3731
3732 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
3733 END delete_worksheet;
3734
3735
3736 PROCEDURE get_ced_and_bb
3737 (
3738 p_api_version IN NUMBER,
3739 p_init_msg_list IN VARCHAR2,
3740 p_commit IN VARCHAR2,
3741 p_validation_level IN NUMBER,
3742 x_return_status OUT NOCOPY VARCHAR2,
3743 x_msg_count OUT NOCOPY NUMBER,
3744 x_msg_data OUT NOCOPY VARCHAR2,
3745 p_worksheet_id IN NUMBER,
3746 x_bb_prior_period_adj OUT NOCOPY NUMBER,
3747 x_bb_pmt_recovery_plans OUT NOCOPY NUMBER,
3748 x_curr_earnings OUT NOCOPY NUMBER,
3749 x_curr_earnings_due OUT NOCOPY NUMBER,
3750 x_bb_total OUT NOCOPY NUMBER
3751 ) IS
3752 l_api_name CONSTANT VARCHAR2(30) := 'get_ced_and_bb';
3753 l_api_version CONSTANT NUMBER := 1.0;
3754 l_held_amount_prior NUMBER := 0;
3755
3756 CURSOR c_wksht_csr IS
3757 SELECT w.worksheet_status wksht_status,
3758 w.quota_id,
3759 w.salesrep_id,
3760 p.status payrun_status,
3761 p.pay_period_id,
3762 p.payrun_id,
3763 w.org_id --R12
3764 FROM cn_payment_worksheets w,
3765 cn_payruns p
3766 WHERE w.payment_worksheet_id = p_worksheet_id
3767 AND w.payrun_id = p.payrun_id
3768 --R12
3769 AND w.org_id = p.org_id;
3770
3771 l_wksht_rec c_wksht_csr%ROWTYPE;
3772 BEGIN
3773 -- Standard Start of API savepoint
3774 SAVEPOINT get_ced_and_bb;
3775
3776 -- Standard call to check for call compatibility.
3777 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version, l_api_name, g_pkg_name)
3778 THEN
3779 RAISE fnd_api.g_exc_unexpected_error;
3780 END IF;
3781
3782 -- Initialize message list if p_init_msg_list is set to TRUE.
3783 IF fnd_api.to_boolean(p_init_msg_list)
3784 THEN
3785 fnd_msg_pub.initialize;
3786 END IF;
3787
3788 -- Initialize API return status to success
3789 x_return_status := fnd_api.g_ret_sts_success;
3790 -- API Body
3791 x_curr_earnings := 0;
3792 x_curr_earnings_due := 0;
3793 x_bb_prior_period_adj := 0;
3794 x_bb_pmt_recovery_plans := 0;
3795 x_bb_total := 0;
3796
3797 -- Get the Worksheet Info
3798 OPEN c_wksht_csr;
3799
3800 FETCH c_wksht_csr
3801 INTO l_wksht_rec;
3802
3803 IF c_wksht_csr%ROWCOUNT = 0
3804 THEN
3805 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error)
3806 THEN
3807 fnd_message.set_name('CN', 'CN_WKSHT_DOES_NOT_EXIST');
3808 fnd_msg_pub.add;
3809 END IF;
3810
3811 RAISE fnd_api.g_exc_error;
3812 END IF;
3813
3814 CLOSE c_wksht_csr;
3815
3816 -- only show summary record
3817 IF (l_wksht_rec.quota_id IS NULL)
3818 THEN
3819 IF ((l_wksht_rec.payrun_status <> 'UNPAID') OR (l_wksht_rec.wksht_status <> 'UNPAID'))
3820 THEN
3821 -- get data from cn_payment_worksheets
3822 SELECT bb_prior_period_adj,
3823 bb_pmt_recovery_plans,
3824 current_earnings
3825 INTO x_bb_prior_period_adj,
3826 x_bb_pmt_recovery_plans,
3827 x_curr_earnings
3828 FROM cn_payment_worksheets
3829 WHERE payment_worksheet_id = p_worksheet_id;
3830 ELSE
3831 -- get data from cn_srp_periods
3832 BEGIN
3833 -- get curr_earnings from all not null quota_id
3834 -- Bug 2690859 : add ' AND srp.credit_type_id = -1000'
3835 -- so only get functional currecny credit type records
3836 SELECT SUM(nvl(balance2_dtd, 0) - nvl(balance2_ctd, 0)) curr_earnings
3837 INTO x_curr_earnings
3838 FROM cn_srp_periods srp
3839 WHERE srp.salesrep_id = l_wksht_rec.salesrep_id
3840 AND srp.period_id = l_wksht_rec.pay_period_id
3841 AND srp.quota_id IS NOT NULL
3842 AND srp.credit_type_id = g_credit_type_id
3843 --R12
3844 AND srp.org_id = l_wksht_rec.org_id;
3845 EXCEPTION
3846 WHEN no_data_found THEN
3847 x_curr_earnings := 0;
3848 END;
3849
3850 BEGIN
3851 -- get data from summary record where quota_id is null
3852 SELECT SUM(nvl(balance2_bbd, 0) - nvl(balance2_bbc, 0)) pri_adj,
3853 - (SUM(nvl(balance4_bbd, 0) - nvl(balance4_bbc, 0))) - (SUM(nvl(balance4_dtd, 0) - nvl(balance4_ctd, 0))) pmt_recovery
3854 INTO x_bb_prior_period_adj,
3855 x_bb_pmt_recovery_plans
3856 FROM cn_srp_periods srp
3857 WHERE srp.quota_id IS NULL
3858 AND srp.salesrep_id = l_wksht_rec.salesrep_id
3859 AND srp.period_id = l_wksht_rec.pay_period_id
3860 AND srp.credit_type_id = g_credit_type_id
3861 --R12
3862 AND srp.org_id = l_wksht_rec.org_id;
3863 EXCEPTION
3864 WHEN no_data_found THEN
3865 x_bb_prior_period_adj := 0;
3866 x_bb_pmt_recovery_plans := 0;
3867 END;
3868 END IF;
3869 -- 01/03/03 pramadas added hold flag check for bug 2710066
3870 -- commented the code for Bug Fix 2849715
3871 /* BEGIN
3872 SELECT SUM(nvl(amount,0))
3873 INTO l_held_amount_prior
3874 FROM cn_payment_transactions cnpt
3875 WHERE cnpt.quota_id IS NOT NULL
3876 AND cnpt.credited_salesrep_id = l_wksht_rec.salesrep_id
3877 AND cnpt.pay_period_id < l_wksht_rec.pay_period_id
3878 AND cnpt.credit_type_id = G_credit_type_id
3879 AND cnpt.hold_flag = 'Y'
3880 AND cnpt.paid_flag ='N'
3881 ;
3882 EXCEPTION
3883 WHEN no_data_found THEN
3884 l_held_amount_prior := 0 ;
3885 END;*/
3886 -- 01/03/03 pramadas added hold flag check for bug 2710066
3887 END IF;
3888
3889 x_bb_total := nvl(x_bb_prior_period_adj, 0) + nvl(x_bb_pmt_recovery_plans, 0);
3890 x_curr_earnings_due := x_bb_total + nvl(x_curr_earnings, 0); -- + Nvl(l_held_amount_prior,0);
3891
3892 -- End of API body.
3893 -- Standard check of p_commit.
3894 IF fnd_api.to_boolean(p_commit)
3895 THEN
3896 COMMIT WORK;
3897 END IF;
3898
3899 --
3900 -- Standard call to get message count and if count is 1, get message info.
3901 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
3902 EXCEPTION
3903 WHEN fnd_api.g_exc_error THEN
3904 ROLLBACK TO get_ced_and_bb;
3905 x_return_status := fnd_api.g_ret_sts_error;
3906 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
3907 WHEN fnd_api.g_exc_unexpected_error THEN
3908 ROLLBACK TO get_ced_and_bb;
3909 x_return_status := fnd_api.g_ret_sts_unexp_error;
3910 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
3911 WHEN OTHERS THEN
3912 ROLLBACK TO get_ced_and_bb;
3913 x_return_status := fnd_api.g_ret_sts_unexp_error;
3914
3915 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)
3916 THEN
3917 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
3918 END IF;
3919
3920 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
3921 END get_ced_and_bb;
3922
3923 --============================================================================
3924 --Name :set_ced_and_bb
3925 --Description : Procedure which will be used to set value of current earning
3926 -- due, begin balance values
3927 --============================================================================
3928 PROCEDURE set_ced_and_bb
3929 (
3930 p_api_version IN NUMBER,
3931 p_init_msg_list IN VARCHAR2,
3932 p_commit IN VARCHAR2,
3933 p_validation_level IN NUMBER,
3934 x_return_status OUT NOCOPY VARCHAR2,
3935 x_msg_count OUT NOCOPY NUMBER,
3936 x_msg_data OUT NOCOPY VARCHAR2,
3937 p_worksheet_id IN NUMBER
3938 ) IS
3939 l_api_name CONSTANT VARCHAR2(30) := 'set_ced_and_bb';
3940 l_api_version CONSTANT NUMBER := 1.0;
3941 l_held_amount_prior NUMBER := 0;
3942
3943 CURSOR c_status_csr IS
3944 SELECT w.worksheet_status wksht_status,
3945 w.salesrep_id,
3946 p.status payrun_status,
3947 p.pay_period_id,
3948 p.payrun_id,
3949 w.org_id
3950 FROM cn_payment_worksheets w,
3951 cn_payruns p
3952 WHERE w.payment_worksheet_id = p_worksheet_id
3953 AND w.payrun_id = p.payrun_id;
3954
3955 l_status_rec c_status_csr%ROWTYPE;
3956
3957 CURSOR c_wksht_sum_csr(l_payrun_id cn_payruns.payrun_id%TYPE, l_srp_id cn_payment_worksheets.salesrep_id%TYPE,
3958 --R12
3959 p_org_id cn_payment_worksheets.org_id%TYPE) IS
3960 SELECT w.payment_worksheet_id,
3961 w.quota_id,
3962 w.salesrep_id,
3963 w.object_version_number
3964 FROM cn_payment_worksheets w
3965 WHERE w.payrun_id = l_payrun_id
3966 AND w.salesrep_id = l_srp_id
3967 AND w.quota_id IS NULL
3968 AND w.org_id = p_org_id;
3969
3970 l_wksht_sum_rec c_wksht_sum_csr%ROWTYPE;
3971 l_curr_earnings NUMBER := 0;
3972 s_bb_prior_period_adj NUMBER := 0;
3973 s_bb_pmt_recovery_plans NUMBER := 0;
3974 s_curr_earnings_due NUMBER := 0;
3975 l_loading_status VARCHAR2(30);
3976 -- varialve added for Bug 3140343
3977 l_ispayee NUMBER := 0;
3978 BEGIN
3979 -- Standard Start of API savepoint
3980 SAVEPOINT set_ced_and_bb;
3981
3982 -- Standard call to check for call compatibility.
3983 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version, l_api_name, g_pkg_name)
3984 THEN
3985 RAISE fnd_api.g_exc_unexpected_error;
3986 END IF;
3987
3988 -- Initialize message list if p_init_msg_list is set to TRUE.
3989 IF fnd_api.to_boolean(p_init_msg_list)
3990 THEN
3991 fnd_msg_pub.initialize;
3992 END IF;
3993
3994 -- Initialize API return status to success
3995 x_return_status := fnd_api.g_ret_sts_success;
3996
3997 -- API Body
3998 -- Get the Status Info
3999 OPEN c_status_csr;
4000
4001 FETCH c_status_csr
4002 INTO l_status_rec;
4003
4004 IF c_status_csr%ROWCOUNT = 0
4005 THEN
4006 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error)
4007 THEN
4008 fnd_message.set_name('CN', 'CN_WKSHT_DOES_NOT_EXIST');
4009 fnd_msg_pub.add;
4010 END IF;
4011
4012 RAISE fnd_api.g_exc_error;
4013 END IF;
4014
4015 CLOSE c_status_csr;
4016
4017 IF ((l_status_rec.payrun_status = 'UNPAID') AND (l_status_rec.wksht_status = 'UNPAID'))
4018 THEN
4019 -- quota_id is null, summary record
4020 FOR l_wksht_sum_rec IN c_wksht_sum_csr(l_status_rec.payrun_id, l_status_rec.salesrep_id, l_status_rec.org_id)
4021 LOOP
4022 BEGIN
4023 -- get curr_earnings from all not null quota_id
4024 -- Bug 2690859 : add ' AND srp.credit_type_id = -1000'
4025 -- so only get functional currecny credit type records
4026 SELECT SUM(nvl(balance2_dtd, 0) - nvl(balance2_ctd, 0)) curr_earnings
4027 INTO l_curr_earnings
4028 FROM cn_srp_periods srp
4029 WHERE srp.salesrep_id = l_wksht_sum_rec.salesrep_id
4030 AND srp.period_id = l_status_rec.pay_period_id
4031 AND srp.quota_id IS NOT NULL
4032 AND srp.credit_type_id = g_credit_type_id
4033 AND srp.org_id = l_status_rec.org_id;
4034 EXCEPTION
4035 WHEN no_data_found THEN
4036 l_curr_earnings := 0;
4037 END;
4038
4039 BEGIN
4040 -- get data from summary record where quota_id is null
4041 SELECT SUM(nvl(balance2_bbd, 0) - nvl(balance2_bbc, 0)) pri_adj,
4042 - (SUM(nvl(balance4_bbd, 0) - nvl(balance4_bbc, 0))) - (SUM(nvl(balance4_dtd, 0) - nvl(balance4_ctd, 0))) pmt_recovery
4043 INTO s_bb_prior_period_adj,
4044 s_bb_pmt_recovery_plans
4045 FROM cn_srp_periods srp
4046 WHERE srp.quota_id IS NULL
4047 AND srp.salesrep_id = l_wksht_sum_rec.salesrep_id
4048 AND srp.period_id = l_status_rec.pay_period_id
4049 AND srp.credit_type_id = g_credit_type_id
4050 AND srp.org_id = l_status_rec.org_id;
4051 EXCEPTION
4052 WHEN no_data_found THEN
4053 s_bb_prior_period_adj := 0;
4054 s_bb_pmt_recovery_plans := 0;
4055 END;
4056
4057 -- 01/03/03 pramadas added hold flag check for bug 2710066
4058 -- commented the code for Bug Fix 2849715
4059
4060 s_curr_earnings_due := s_bb_prior_period_adj + s_bb_pmt_recovery_plans + l_curr_earnings;
4061 -- removed + l_held_amount_prior; (held amount is in s_bb_prior_period_adj)
4062
4063 -- update worksheet record
4064 UPDATE cn_payment_worksheets
4065 SET bb_prior_period_adj = s_bb_prior_period_adj,
4066 bb_pmt_recovery_plans = s_bb_pmt_recovery_plans,
4067 current_earnings = l_curr_earnings,
4068 current_earnings_due = s_curr_earnings_due,
4069 last_update_date = SYSDATE,
4070 last_update_login = fnd_global.login_id,
4071 last_updated_by = fnd_global.user_id,
4072 object_version_number = l_wksht_sum_rec.object_version_number + 1
4073 WHERE payment_worksheet_id = l_wksht_sum_rec.payment_worksheet_id;
4074 END LOOP;
4075
4076 -- REMOVED cn_worksheet_qg_dtls code => re-create cn_worksheet_qg_dtls
4077 -- Bug 3140343 : Payee Design.
4078 END IF;
4079
4080 -- End of API body.
4081 -- Standard check of p_commit.
4082 IF fnd_api.to_boolean(p_commit)
4083 THEN
4084 COMMIT WORK;
4085 END IF;
4086
4087 --
4088 -- Standard call to get message count and if count is 1, get message info.
4089 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
4090 EXCEPTION
4091 WHEN fnd_api.g_exc_error THEN
4092 ROLLBACK TO set_ced_and_bb;
4093 x_return_status := fnd_api.g_ret_sts_error;
4094 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
4095 WHEN fnd_api.g_exc_unexpected_error THEN
4096 ROLLBACK TO set_ced_and_bb;
4097 x_return_status := fnd_api.g_ret_sts_unexp_error;
4098 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
4099 WHEN OTHERS THEN
4100 ROLLBACK TO set_ced_and_bb;
4101 x_return_status := fnd_api.g_ret_sts_unexp_error;
4102
4103 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)
4104 THEN
4105 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
4106 END IF;
4107
4108 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
4109 END set_ced_and_bb;
4110
4111
4112
4113 PROCEDURE generic_conc_processor
4114 (
4115 p_payrun_id IN NUMBER,
4116 p_params IN conc_params,
4117 p_org_id cn_payment_worksheets.org_id%TYPE,
4118 p_salesrep_tbl IN salesrep_tab_typ,
4119 x_errbuf OUT NOCOPY VARCHAR2,
4120 x_retcode OUT NOCOPY NUMBER
4121 ) IS
4122 l_payrun_id NUMBER;
4123 l_logical_batch_id NUMBER;
4124 l_max_batch_id NUMBER;
4125 l_physical_batch_id NUMBER;
4126 l_job_count NUMBER := 0;
4127 l_conc_request_id NUMBER(15) := fnd_global.conc_request_id;
4128 l_runner_count NUMBER := 0;
4129 l_error_count NUMBER := 0;
4130 l_warning_count NUMBER := 0;
4131 mysysdate CONSTANT DATE := SYSDATE;
4132 l_request_id NUMBER := 0;
4133 l_sleep_time NUMBER := to_number(nvl(fnd_profile.VALUE('CN_SLEEP_TIME'), '20'));
4134 duration NUMBER(7, 1);
4135 errmsg VARCHAR2(4000) := '';
4136 err_num NUMBER := NULL;
4137 l_org_id cn_payruns.org_id%TYPE;
4138 BEGIN
4139 -- Standard Start of API savepoint
4140 SAVEPOINT generic_conc_processor;
4141 -- SUBMIT BATCHES
4142 fnd_file.put_line(fnd_file.LOG,'Start the batching process for payrun_id = ' || p_payrun_id);
4143 l_org_id := p_org_id;
4144 BEGIN
4145 -- lock payrun when when batching
4146 BEGIN
4147 SELECT pr.PAYRUN_ID
4148 INTO l_payrun_id
4149 FROM cn_payruns pr
4150 WHERE pr.PAYRUN_ID = p_payrun_id
4151 FOR UPDATE NOWAIT;
4152
4153 EXCEPTION
4154 WHEN NO_DATA_FOUND THEN
4155 fnd_file.put_line(fnd_file.LOG,'Invalid payrun. Could not find payrun with ID = ' || l_payrun_id);
4156 cn_message_pkg.debug('Invalid payrun. Could not find payrun with ID = ' || l_payrun_id);
4157 RAISE ;
4158 END;
4159
4160 l_max_batch_id := p_salesrep_tbl(p_salesrep_tbl.COUNT).batch_id;
4161
4162 -- Get logical batch ID
4163 SELECT cn_process_batches_s2.NEXTVAL
4164 INTO l_logical_batch_id
4165 FROM sys.dual;
4166
4167 fnd_file.put_line(fnd_file.LOG,'Logical Batch ID in cn_process_batches_all = ' || l_logical_batch_id);
4168
4169 FOR currbatch IN 1 .. l_max_batch_id
4170 LOOP
4171
4172 /* Load batches into cn_process_batches*/
4173 -- sequence s1 is for Physical batch id
4174 SELECT cn_process_batches_s3.NEXTVAL
4175 INTO l_physical_batch_id
4176 FROM sys.dual;
4177
4178 FOR kk IN 1 .. p_salesrep_tbl.COUNT
4179 LOOP
4180 IF (p_salesrep_tbl(kk).batch_id = currbatch)
4181 THEN
4182 INSERT INTO cn_process_batches
4183 (process_batch_id,
4184 logical_batch_id,
4185 physical_batch_id,
4186 srp_period_id,
4187 period_id,
4188 salesrep_id,
4189 status_code,
4190 org_id,
4191 process_batch_type,
4192 creation_date,
4193 created_by,
4194 last_update_date,
4195 last_updated_by,
4196 last_update_login,
4197 request_id,
4198 program_application_id,
4199 program_id,
4200 program_update_date)
4201 VALUES
4202 (cn_process_batches_s1.NEXTVAL,
4203 l_logical_batch_id,
4204 l_physical_batch_id,
4205 1,
4206 1,
4207 p_salesrep_tbl(kk).salesrep_id,
4208 'VOID',
4209 p_org_id,
4210 p_params.conc_program_name,
4211 mysysdate,
4212 fnd_global.user_id,
4213 mysysdate,
4214 fnd_global.user_id,
4215 fnd_global.login_id,
4216 fnd_global.conc_request_id,
4217 fnd_global.prog_appl_id,
4218 fnd_global.conc_program_id,
4219 mysysdate);
4220
4221 END IF;
4222
4223 END LOOP; -- kk
4224 --COMMIT;
4225
4226 /***************** Launching Threads ***********************/
4227 l_job_count := l_job_count + 1;
4228
4229 -- SUBMIT BATCHES
4230 fnd_file.put_line(fnd_file.LOG,' Now submit physical batch id ' || l_physical_batch_id);
4231
4232 conc_submit(p_conc_program => p_params.conc_program_name,
4233 p_description => 'Runner physical batch ID ' || l_physical_batch_id,
4234 p_logical_batch_id => l_logical_batch_id,
4235 p_batch_id => l_physical_batch_id,
4236 p_org_id => l_org_id,
4237 p_payrun_id => l_payrun_id,
4238 x_request_id => l_request_id,
4239 p_params => p_params);
4240 fnd_file.put_line(fnd_file.LOG,' Created child concurrent request. ID = ' || l_request_id);
4241
4242 END LOOP; --currbatch
4243
4244 EXCEPTION
4245 WHEN OTHERS THEN
4246 ROLLBACK TO generic_conc_processor ;
4247 x_retcode := 2;
4248 x_errbuf := 'Error occured when processing payrun = ' || l_payrun_id || '. Check the error log.';
4249
4250 err_num := SQLCODE;
4251 IF err_num = -54
4252 THEN
4253 errmsg := 'This payrun is already involved in another process. Please try again later.';
4254 fnd_file.put_line(fnd_file.log, errmsg);
4255 x_errbuf := errmsg ;
4256 raise_application_error(-20000, errmsg);
4257 ELSE
4258 RAISE;
4259 END IF;
4260 END;
4261
4262 -- commit the child requests and start waiting for the children to complete
4263 COMMIT ;
4264
4265 -- Monitor batches
4266 LOOP
4267 SELECT COUNT(0)
4268 INTO l_runner_count
4269 FROM fnd_concurrent_requests fcr
4270 WHERE fcr.parent_request_id = l_conc_request_id
4271 AND fcr.phase_code <> 'C';
4272 EXIT WHEN l_runner_count = 0;
4273 dbms_lock.sleep(l_sleep_time);
4274 END LOOP;
4275
4276 FOR rs_errors IN (SELECT fcr.request_id,
4277 fcr.actual_completion_date,
4278 fcr.completion_text
4279 FROM fnd_concurrent_requests fcr
4280 WHERE parent_request_id = l_conc_request_id
4281 AND upper(status_code) = 'E')
4282 LOOP
4283 l_error_count := l_error_count + 1;
4284 IF l_error_count = 1
4285 THEN
4286 fnd_file.put_line(fnd_file.log, 'ERRORED REQUESTS');
4287 fnd_file.put_line(fnd_file.log, '================');
4288 END IF;
4289 fnd_file.put_line(fnd_file.LOG,' ' || rs_errors.request_id || ' @ ' || rs_errors.actual_completion_date || ' due to ' || rs_errors.completion_text);
4290 END LOOP;
4291
4292 -- Count the warning batches
4293 l_warning_count := 0;
4294 BEGIN
4295 SELECT COUNT(0)
4296 INTO l_warning_count
4297 FROM fnd_concurrent_requests fcr
4298 WHERE parent_request_id = l_conc_request_id
4299 AND upper(status_code) = 'G';
4300
4301 fnd_file.put_line(fnd_file.log, 'WARNING REQUESTS: ' || l_warning_count);
4302 EXCEPTION
4303 WHEN no_data_found THEN
4304 NULL;
4305 WHEN OTHERS THEN
4306 fnd_file.put_line(fnd_file.log, 'Error getting warnings: ' || SQLERRM);
4307 END;
4308
4309 duration := (SYSDATE - mysysdate) * 1440;
4310
4311 IF l_error_count <> 0
4312 THEN
4313 x_retcode := 2;
4314 x_errbuf := to_char(l_error_count) || ' batches in error';
4315 ELSIF l_warning_count <> 0
4316 THEN
4317 x_retcode := 1;
4318 x_errbuf := 'WARNINGS: ' || to_char(l_warning_count);
4319 ELSE
4320 x_retcode := 0;
4321 x_errbuf := 'SUCCESS: ';
4322 END IF;
4323
4324 x_errbuf := x_errbuf || '. Worksheet process completed in ' || to_char(duration) ||' minutes. ';
4325
4326 EXCEPTION
4327 WHEN OTHERS THEN
4328 x_retcode := 2;
4329 x_errbuf := x_errbuf || '. Error processing payrun ID = ' || l_payrun_id ;
4330 fnd_file.put_line(fnd_file.log, x_errbuf);
4331 RAISE ;
4332 END generic_conc_processor;
4333
4334 --============================================================================
4335 -- This procedure is used as executable for the concurrent program
4336 -- REFRESH_WORKSHEET".This program will take payrun name as the input
4337 -- and then call the procedure "refresh_worksheet_child" which refreshes
4338 -- worksheets.
4339 --============================================================================
4340
4341 PROCEDURE refresh_worksheet_parent
4342 (
4343 errbuf OUT NOCOPY VARCHAR2,
4344 retcode OUT NOCOPY NUMBER,
4345 p_name cn_payruns.NAME%TYPE
4346 ) IS
4347 salesrep_t salesrep_tab_typ;
4348 l_batch_sz NUMBER := 80;
4349 errmsg VARCHAR2(4000) := '';
4350 l_min_period NUMBER;
4351 l_max_period NUMBER;
4352 l_payrun_id NUMBER;
4353 x_reps_exist NUMBER := 0;
4354
4355 l_status VARCHAR2(30);
4356 --R12
4357 l_org_id cn_payruns.org_id%TYPE;
4358 l_conc_params conc_params ;
4359 l_has_access BOOLEAN;
4360
4361 CURSOR get_payrun_id_curs IS
4362 SELECT payrun_id,
4363 status
4364 FROM cn_payruns pr
4365 WHERE NAME = p_name
4366 AND org_id = mo_global.get_current_org_id;
4367
4368 CURSOR c_payrun_srp(c_payrun_id cn_payruns.payrun_id%TYPE, c_batch_sz number) IS
4369 SELECT salesrep_id,
4370 ceil(rownum / c_batch_sz)
4371 FROM (SELECT DISTINCT wk.salesrep_id
4372 FROM cn_payment_worksheets wk
4373 WHERE wk.worksheet_status = 'UNPAID'
4374 AND wk.quota_id IS NULL
4375 AND wk.payrun_id = c_payrun_id
4376 AND wk.org_id = mo_global.get_current_org_id);
4377
4378 BEGIN
4379 fnd_file.put_line(fnd_file.log, 'Input Parameters Payrun_Name =' || p_name);
4380 l_has_access := cn_payment_security_pvt.get_security_access(cn_payment_security_pvt.g_type_wksht, cn_payment_security_pvt.g_access_payrun_refresh);
4381 --Get the salesrep batch size from profile option.
4382 l_batch_sz := nvl(fnd_profile.value('CN_PMT_SRP_BATCH_SIZE'),251);
4383 fnd_file.put_line(fnd_file.log, 'Batch Size =' || to_char(l_batch_sz));
4384
4385 IF l_batch_sz < 1
4386 THEN
4387 errmsg := 'The batch size should be greater than zero.';
4388 fnd_file.put_line(fnd_file.log, errmsg);
4389 raise_application_error(-20000, errmsg);
4390 END IF;
4391
4392 -- TODO Handle error message
4393 BEGIN
4394 OPEN get_payrun_id_curs;
4395 FETCH get_payrun_id_curs
4396 INTO l_payrun_id, l_status;
4397 CLOSE get_payrun_id_curs;
4398 EXCEPTION
4399 WHEN NO_DATA_FOUND THEN
4400 errmsg := 'Invalid payrun name. Could not find payrun with the name = ' || p_name;
4401 fnd_file.put_line(fnd_file.log, errmsg);
4402 raise_application_error(-20000, errmsg);
4403 END;
4404
4405 IF l_status NOT IN ('UNPAID')
4406 THEN
4407 errmsg := 'Cannot perform worksheet refresh when paid is in status = ' || l_status;
4408 raise_application_error(-20000, errmsg);
4409 END IF;
4410
4411 BEGIN
4412
4413 OPEN c_payrun_srp(l_payrun_id, l_batch_sz);
4414 LOOP
4415 FETCH c_payrun_srp
4416 BULK COLLECT INTO salesrep_t LIMIT 1000;
4417
4418 /*SELECT salesrep_id,
4419 ceil(rownum / l_batch_sz) BULK COLLECT
4420 INTO salesrep_t
4421 FROM (SELECT DISTINCT wk.salesrep_id
4422 FROM cn_payment_worksheets wk
4423 WHERE wk.worksheet_status = 'UNPAID'
4424 AND wk.quota_id IS NULL
4425 AND wk.payrun_id = l_payrun_id
4426 AND wk.org_id = mo_global.get_current_org_id);*/
4427
4428 l_conc_params.conc_program_name := 'CN_REFRESH_WKSHT_CHILD' ;
4429
4430 -- batch
4431 generic_conc_processor(p_payrun_id => l_payrun_id,
4432 p_salesrep_tbl => salesrep_t,
4433 p_params => l_conc_params,
4434 p_org_id => mo_global.get_current_org_id,
4435 x_errbuf => errbuf,
4436 x_retcode => retcode);
4437
4438 EXIT WHEN c_payrun_srp%NOTFOUND;
4439 END LOOP;
4440 CLOSE c_payrun_srp;
4441
4442 EXCEPTION
4443 WHEN no_data_found THEN
4444 errmsg := 'No salesreps found that were eligible for worksheet creation in the payrun : ';
4445 fnd_file.put_line(fnd_file.log, errmsg);
4446 retcode := 2;
4447 errbuf := errmsg;
4448 RAISE ;
4449 END;
4450
4451 fnd_file.put_line(fnd_file.log, errbuf);
4452 fnd_file.put_line(fnd_file.LOG,' Count of worksheets to be refreshed = ' || salesrep_t.COUNT);
4453 fnd_file.put_line(fnd_file.log,' Completed refresh worksheet process....');
4454
4455 EXCEPTION
4456 WHEN OTHERS THEN
4457 fnd_file.put_line(fnd_file.LOG,'Unexpected exception in cn_payment_worksheet_pvt.refresh_worksheet_parent');
4458 fnd_file.put_line(fnd_file.log, errmsg);
4459 fnd_file.put_line(fnd_file.log, SQLERRM);
4460 RAISE;
4461 END refresh_worksheet_parent;
4462
4463 --============================================================================
4464 -- Name : refresh_worksheet_child
4465 -- Description : This procedure is used as executable for the concurrent program
4466 -- "CN_REFRESH_WKSHT_CHILD".This program will take payrun_id as the input
4467 -- and refresh worksheets for that payrun.
4468 --============================================================================
4469
4470 PROCEDURE refresh_worksheet_child
4471 (
4472 errbuf OUT NOCOPY VARCHAR2,
4473 retcode OUT NOCOPY NUMBER,
4474 p_batch_id IN NUMBER,
4475 p_payrun_id IN NUMBER,
4476 p_logical_batch_id IN NUMBER,
4477 --R12
4478 p_org_id IN cn_payruns.org_id%TYPE
4479 ) IS
4480 x_return_status VARCHAR2(10) := fnd_api.g_ret_sts_success;
4481 x_msg_count NUMBER;
4482 x_msg_data VARCHAR2(4000);
4483 l_worksheet_rec cn_payment_worksheet_pvt.worksheet_rec_type;
4484 x_status VARCHAR2(200);
4485 x_loading_status VARCHAR2(20) := 'CN_UPDATED';
4486 l_start_time DATE;
4487 l_error_count NUMBER := 0;
4488 l_ovn cn_payment_worksheets.object_version_number%TYPE;
4489 BEGIN
4490 l_start_time := SYSDATE;
4491 fnd_file.put_line(fnd_file.log, ' Input Parameters Payrun_id = ' || p_payrun_id);
4492 fnd_file.put_line(fnd_file.log, ' Input Parameters Batch_id = ' || p_batch_id);
4493 fnd_file.put_line(fnd_file.log, ' Current time = ' || to_char(l_start_time, 'Dy DD-Mon-YYYY HH24:MI:SS'));
4494
4495 l_worksheet_rec.payrun_id := p_payrun_id;
4496 l_worksheet_rec.org_id := p_org_id;
4497
4498 FOR emp IN (SELECT salesrep_id
4499 FROM cn_process_batches
4500 WHERE logical_batch_id = p_logical_batch_id
4501 AND physical_batch_id = p_batch_id)
4502 LOOP
4503 -- Run refresh worksheet for this salesrep.
4504 l_worksheet_rec.salesrep_id := emp.salesrep_id;
4505 l_worksheet_rec.call_from := cn_payment_worksheet_pvt.concurrent_program_call;
4506
4507 SELECT wk.payment_worksheet_id,wk.object_version_number
4508 INTO l_worksheet_rec.worksheet_id,l_ovn
4509 FROM cn_payment_worksheets_all wk
4510 WHERE wk.payrun_id = l_worksheet_rec.payrun_id
4511 AND wk.salesrep_id = l_worksheet_rec.salesrep_id
4512 AND quota_id IS NULL;
4513
4514 fnd_file.put_line(fnd_file.log,'Refresh worksheet for = ' || l_worksheet_rec.salesrep_id || ' salesrepID');
4515
4516 cn_payment_worksheet_pvt.update_worksheet(p_api_version => 1.0,
4517 p_init_msg_list => 'T',
4518 p_commit => 'F',
4519 p_validation_level => fnd_api.g_valid_level_full,
4520 x_return_status => x_return_status,
4521 x_msg_count => x_msg_count,
4522 x_msg_data => x_msg_data,
4523 p_worksheet_id => l_worksheet_rec.worksheet_id,
4524 p_operation => 'REFRESH',
4525 x_loading_status => x_loading_status,
4526 x_status => x_status,
4527 x_ovn => l_ovn
4528 );
4529
4530 END LOOP;
4531
4532 IF x_return_status <> fnd_api.g_ret_sts_success
4533 THEN
4534 l_error_count := l_error_count + 1;
4535
4536 --ROLLBACK TO create_single_worksheet;
4537 cn_message_pkg.debug('Error when refreshing Worksheet for : ' || l_worksheet_rec.salesrep_id);
4538 fnd_file.put_line(fnd_file.log,'Failed to refresh worksheet for ' || l_worksheet_rec.salesrep_id);
4539
4540 FOR i IN 1 .. x_msg_count
4541 LOOP
4542 fnd_file.put_line(fnd_file.log, 'msg: ' || fnd_msg_pub.get(i, 'F'));
4543 END LOOP;
4544 fnd_file.put_line(fnd_file.log, '+------------------------------+');
4545 ROLLBACK;
4546
4547 ELSE
4548
4549 COMMIT;
4550 END IF;
4551
4552 IF l_error_count <> 0
4553 THEN
4554 retcode := 2;
4555 errbuf := ' Batch# ' || p_batch_id || ' : Refresh of worksheets was not successful for some resources. Count = ' || to_char(l_error_count);
4556 fnd_file.put_line(fnd_file.log, errbuf);
4557 END IF;
4558
4559 fnd_file.put_line(fnd_file.LOG,' Finish time = ' || to_char(SYSDATE, 'Dy DD-Mon-YYYY HH24:MI:SS'));
4560 fnd_file.put_line(fnd_file.LOG, ' Batch time = ' || (SYSDATE - l_start_time) * 1400 || ' minutes ');
4561
4562 EXCEPTION
4563 WHEN OTHERS THEN
4564 fnd_file.put_line(fnd_file.LOG,'Unexpected exception in processing the (payrun_id,batch) = ' ||p_payrun_id || ',' || p_batch_id);
4565 fnd_file.put_line(fnd_file.log, SQLERRM);
4566 RAISE;
4567
4568 END refresh_worksheet_child;
4569
4570
4571
4572 END cn_payment_worksheet_pvt;