[Home] [Help]
PACKAGE BODY: APPS.CN_PAYRUN_PVT
Source
1 PACKAGE BODY CN_PAYRUN_PVT as
2 -- $Header: cnvprunb.pls 120.24.12020000.2 2012/07/19 11:29:35 nbombili ship $
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'CN_Payrun_PVT';
5
6 -- ===========================================================================
7 -- Procedure : Build_Parse_Fetch_Call
8 -- Description : Procedure will be called from BUILD_BEE_API for
9 -- every salesrep, quota_id, element type id
10 -- ===========================================================================
11
12 PROCEDURE Refresh_Payrun
13 ( p_api_version IN NUMBER,
14 p_init_msg_list IN VARCHAR2,
15 p_commit IN VARCHAR2,
16 p_validation_level IN NUMBER,
17 p_payrun_id IN cn_payruns.payrun_id%TYPE,
18 x_return_status OUT NOCOPY VARCHAR2,
19 x_msg_count OUT NOCOPY NUMBER,
20 x_msg_data OUT NOCOPY VARCHAR2,
21 x_status OUT NOCOPY VARCHAR2,
22 x_loading_status OUT NOCOPY VARCHAR2
23 ) IS
24
25 l_api_name CONSTANT VARCHAR2(30) := 'Refresh_Payrun';
26 l_api_version CONSTANT NUMBER := 1.0;
27
28 CURSOR get_old_record IS
29 SELECT status, payrun_id,org_id,object_version_number
30 FROM cn_payruns
31 WHERE payrun_id = p_payrun_id;
32 l_old_record get_old_record%ROWTYPE;
33
34 CURSOR get_worksheets(p_org_id cn_payruns.org_id%TYPE) IS
35 SELECT payment_worksheet_id,object_version_number
36 FROM cn_payment_worksheets
37 WHERE payrun_id = p_payrun_id
38 AND quota_id IS NULL
39 AND worksheet_status = 'UNPAID'
40 --R12
41 AND org_id = p_org_id;
42
43
44 BEGIN
45 --
46 -- Standard Start of API savepoint
47 --
48 SAVEPOINT Refresh_Payrun;
49 --
50 -- Standard call to check for call compatibility.
51 --
52 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
53 p_api_version ,
54 l_api_name ,
55 G_PKG_NAME )
56 THEN
57 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
58 END IF;
59 --
60 -- Initialize message list if p_init_msg_list is set to TRUE.
61 --
62 IF FND_API.to_Boolean( p_init_msg_list ) THEN
63 FND_MSG_PUB.initialize;
64 END IF;
65 --
66 -- Initialize API return status to success
67 --
68 x_return_status := FND_API.G_RET_STS_SUCCESS;
69 x_loading_status := 'CN_DELETED';
70 --
71 -- API Body
72 --
73
74 OPEN get_old_record;
75 FETCH get_old_record INTO l_old_record;
76
77 IF get_old_record%rowcount = 0 THEN
78 --Error condition
79 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)THEN
80 fnd_message.set_name('CN', 'CN_PAYRUN_DOES_NOT_EXIST');
81 fnd_msg_pub.add;
82 END IF;
83 x_loading_status := 'CN_PAYRUN_DOES_NOT_EXIST';
84 RAISE FND_API.G_EXC_ERROR;
85 END IF;
86
87 CLOSE get_old_record;
88
89 IF l_old_record.status IN ('PAID', 'RETURNED_FUNDS', 'PAID_WITH_RETURNS') THEN
90 --Error condition
91 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)THEN
92 fnd_message.set_name('CN', 'CN_PAYRUN_PAID');
93 fnd_msg_pub.add;
94 END IF;
95 x_loading_status := 'CN_PAYRUN_PAID';
96 RAISE FND_API.G_EXC_ERROR;
97 END IF;
98
99 IF l_old_record.status = 'FROZEN' THEN
100 --Error condition
101 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)THEN
102 fnd_message.set_name('CN', 'CN_PAYRUN_FROZEN');
103 fnd_msg_pub.add;
104 END IF;
105 x_loading_status := 'CN_PAYRUN_FROZEN';
106 RAISE FND_API.G_EXC_ERROR;
107 END IF;
108
109 -- Section included by Sundar Venkat on 07 Mar 2002
110 -- Procedure to check if the payrun action is valid.
111 CN_PAYMENT_SECURITY_PVT.Payrun_Action
112 ( p_api_version => 1.0,
113 p_init_msg_list => fnd_api.g_true,
114 p_validation_level => fnd_api.g_valid_level_full,
115 x_return_status => x_return_status,
116 x_msg_count => x_msg_count,
117 x_msg_data => x_msg_data,
118 p_payrun_id => p_payrun_id,
119 p_action => 'REFRESH'
120 );
121
122 IF x_return_status <> FND_API.g_ret_sts_success
123 THEN
124 RAISE FND_API.G_EXC_ERROR;
125 END IF;
126
127
128 FOR worksheets in get_worksheets(l_old_record.org_id)
129 LOOP
130 cn_payment_worksheet_pvt.update_Worksheet
131 (p_api_version => p_api_version,
132 p_init_msg_list => p_init_msg_list,
133 p_commit => fnd_api.g_false,
134 p_validation_level => p_validation_level,
135 x_return_status => x_return_status,
136 x_msg_count => x_msg_count,
137 x_msg_data => x_msg_data,
138 p_worksheet_id => worksheets.payment_worksheet_id,
139 p_operation => 'REFRESH',
140 x_status => x_status,
141 x_loading_status => x_loading_status,
142 --R12
143 x_ovn => worksheets.object_version_number );
144
145 IF x_return_status <> FND_API.g_ret_sts_success
146 THEN
147 RAISE FND_API.G_EXC_ERROR;
148 END IF;
149
150 END LOOP;
151
152
153 -- End of API body.
154 -- Standard check of p_commit.
155
156 IF FND_API.To_Boolean( p_commit ) THEN
157 COMMIT WORK;
158 END IF;
159
160 --
161 -- Standard call to get message count and if count is 1, get message info.
162 --
163 FND_MSG_PUB.Count_And_Get
164 (
165 p_count => x_msg_count ,
166 p_data => x_msg_data ,
167 p_encoded => FND_API.G_FALSE
168 );
169 EXCEPTION
170 WHEN FND_API.G_EXC_ERROR THEN
171 ROLLBACK TO Refresh_Payrun;
172 x_return_status := FND_API.G_RET_STS_ERROR ;
173 FND_MSG_PUB.Count_And_Get
174 (
175 p_count => x_msg_count ,
176 p_data => x_msg_data ,
177 p_encoded => FND_API.G_FALSE
178 );
179 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
180 ROLLBACK TO Refresh_Payrun;
181 x_loading_status := 'UNEXPECTED_ERR';
182 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
183 FND_MSG_PUB.Count_And_Get
184 (
185 p_count => x_msg_count ,
186 p_data => x_msg_data ,
187 p_encoded => FND_API.G_FALSE
188 );
189 WHEN OTHERS THEN
190 ROLLBACK TO Refresh_Payrun;
191 x_loading_status := 'UNEXPECTED_ERR';
192 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
193 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
194 THEN
195 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME ,l_api_name );
196 END IF;
197 FND_MSG_PUB.Count_And_Get
198 (
199 p_count => x_msg_count ,
200 p_data => x_msg_data ,
201 p_encoded => FND_API.G_FALSE
202 );
203
204 END;
205 -- ===========================================================================
206 -- Procedure : Build_Parse_Fetch_Call
207 -- Description : Procedure will be called from BUILD_BEE_API for
208 -- every salesrep, quota_id, element type id
209 -- ===========================================================================
210
211 PROCEDURE Build_Parse_Fetch_Call
212 (x_return_status OUT NOCOPY VARCHAR2,
213 x_msg_count OUT NOCOPY NUMBER,
214 x_msg_data OUT NOCOPY VARCHAR2,
215 p_payrun_id IN NUMBER,
216 p_salesrep_id IN NUMBER,
217 p_element_type_id IN NUMBER,
218 p_quota_id IN NUMBER,
219 p_Incentive_type_code IN VARCHAR2,
220 p_amount IN NUMBER,
221 p_loading_status IN VARCHAR2,
222 x_loading_status OUT NOCOPY VARCHAR2,
223 x_batch_id IN OUT NOCOPY NUMBER ) IS
224
225 l_api_name CONSTANT VARCHAR2(30) := 'Build_Parse_Fetch_Call';
226 l_flag_payment_transactions VARCHAR2(1) := 'N';
227
228
229 CURSOR get_payruns IS
230 SELECT name,
231 org_id,
232 pay_period_id,
233 pay_date
234 FROM cn_payruns
235 WHERE payrun_id = p_payrun_id ;
236
237 l_payrun_rec get_payruns%ROWTYPE;
238
239 --
240 -- get the assignment id and business group id
241 --
242 CURSOR get_assign_id (p_org_id cn_payment_transactions.org_id%TYPE ) IS
243 SELECT p.assignment_id assignment_id,
244 p.assignment_number assignment_number,
245 rre.source_business_grp_id source_business_grp_id,
246 rs.status status
247 FROM jtf_rs_salesreps rs,
248 jtf_rs_resource_extns rre,
249 per_assignments_f p,
250 cn_payruns ps
251 WHERE rs.salesrep_id = p_salesrep_id
252 AND rs.org_id = p_org_id
253 AND rs.resource_id = rre.resource_id
254 AND rre.category = 'EMPLOYEE'
255 AND rre.source_id = p.person_id
256 AND ps.payrun_id = p_payrun_id
257 AND ps.pay_date BETWEEN p.effective_start_date AND Nvl(p.effective_end_date,ps.pay_date) AND p.assignment_type = 'E';
258
259 l_assignment_rec get_assign_id%ROWTYPE;
260
261 --
262 -- Get the Element Inputs
263 --
264 -- Bug 2880233: onlt pmtpln_rec need hard code quota_id
265 -- Bug 3504917: order by should be display_seq, then name
266 CURSOR pay_element_inputs ( p_status IN VARCHAR2,
267 p_currency_code IN VARCHAR2 ) IS
268 SELECT tab.name table_name , col.name column_name,
269 piv.display_sequence line_number,
270 piv.input_value_id element_input_id ,
271 piv.uom uom, piv.name element_input_name
272 FROM cn_pay_element_inputs pei,
273 cn_quota_pay_elements qpe,
274 cn_payruns p,
275 cn_objects tab,
276 cn_objects col,
277 pay_element_types_f pet,
278 pay_input_values_f piv
279 WHERE qpe.pay_element_type_id = p_element_type_id
280 AND qpe.quota_id = decode(p_incentive_type_code,
281 'PMTPLN_REC' , -1001,
282 p_quota_id)
283 AND qpe.status = p_status
284 AND p.payrun_id = p_payrun_id
285 AND p.pay_date BETWEEN qpe.start_date AND qpe.end_date
286 AND pet.input_currency_code = p_currency_code
287 AND qpe.pay_element_type_id = pet.element_type_id
288 AND qpe.start_date >= pet.effective_start_date
289 AND qpe.end_date <= pet.effective_end_date
290 AND qpe.quota_pay_element_id = pei.quota_pay_element_id
291 AND trunc(pet.effective_start_date) = trunc(piv.effective_start_date)
292 AND trunc(pet.effective_end_date) = trunc(piv.effective_end_date)
293 AND pei.element_input_id = piv.input_value_id
294 AND tab.object_id = pei.tab_object_id
295 AND col.object_id = pei.col_object_id
296 AND col.table_id = pei.tab_object_id
297 AND p.org_id = tab.org_id
298 -- AND p.org_id = col.org_id
299 UNION
300 SELECT null table_name ,
301 null column_name,
302 piv.display_sequence line_number,
303 piv.input_value_id element_input_id,
304 piv.uom uom, piv.name element_input_name
305 FROM
306 pay_input_values_f piv,
307 pay_element_types_f pet,
308 cn_quota_pay_elements qpe,
309 cn_payruns p
310 WHERE qpe.pay_element_type_id = p_element_type_id
311 AND qpe.quota_id = decode(p_incentive_type_code,
312 'PMTPLN_REC' , -1001,
313 p_quota_id)
314 AND qpe.status = p_status
315 AND p.payrun_id = p_payrun_id
316 AND p.pay_date BETWEEN qpe.start_date AND qpe.end_Date
317 AND pet.input_currency_code = p_currency_code
318 AND qpe.pay_element_type_id = pet.element_type_id
319 AND qpe.start_date >= pet.effective_start_date
320 AND qpe.end_date <= pet.effective_end_date
321 AND trunc(pet.effective_start_date) = trunc(piv.effective_start_date)
322 AND trunc(pet.effective_end_date) = trunc(piv.effective_end_date)
323 AND pet.element_type_id = piv.element_type_id
324 AND not exists ( select 1 from cn_pay_element_inputs cpei
325 WHERE cpei.quota_pay_element_id = qpe.quota_pay_element_id
326 AND qpe.pay_element_type_id = piv.element_type_id
327 AND cpei.element_input_id = piv.input_value_id )
328 ORDER by line_number, element_input_name ;
329
330
331 Cursor get_element_name ( p_element_type_id IN NUMBER ) IS
332 Select element_name
333 from pay_element_types_f
334 where element_type_id = p_element_type_id ;
335
336 --
337 -- Cursor
338 --
339 TYPE rc IS ref cursor;
340 main_cursor rc;
341
342 -- Default where clause and from Clause
343
344 --Modified by Sundar Venkat for bug fix 2660893
345 -- AC 04/09/03 2892822 : need to join with p_Incentive_type_code
346 l_where VARCHAR2(2000) :=
347 ' CN_PAYRUNS.PAYRUN_ID = :B1 '
348 ||' AND CN_SALESREPS.SALESREP_ID = :B2 '
349 ||' AND CN_PAYRUNS.ORG_ID = CN_SALESREPS.ORG_ID '
350 ||' AND CN_PAYMENT_TRANSACTIONS.QUOTA_ID = :B3'
351 ||' AND CN_PAYMENT_TRANSACTIONS.PAYRUN_ID = CN_PAYRUNS.PAYRUN_ID'
352 ||' AND CN_PAYMENT_TRANSACTIONS.INCENTIVE_TYPE_CODE = :B4 '
353 ||' AND CN_PAYMENT_TRANSACTIONS.CREDITED_SALESREP_ID = CN_SALESREPS.SALESREP_ID'
354 ||' AND CN_PAYMENT_TRANSACTIONS.PAY_ELEMENT_TYPE_ID IS NOT NULL';
355
356 l_from VARCHAR2(2000) := ' CN_PAYRUNS, CN_SALESREPS, CN_PAYMENT_TRANSACTIONS ';
357
358 l_select VARCHAR2(32000) ;
359
360 -- Total Input Values Defined
361 l_count NUMBER := 0;
362
363
364
365 -- Total 15 Param is allowed
366 l_param1 VARCHAR2(2000);
367 l_param2 VARCHAR2(2000);
368 l_param3 VARCHAR2(2000);
369 l_param4 VARCHAR2(2000);
370 l_param5 VARCHAR2(2000);
371 l_param6 VARCHAR2(2000);
372 l_param7 VARCHAR2(2000);
373 l_param8 VARCHAR2(2000);
374 l_param9 VARCHAR2(2000);
375 l_param10 VARCHAR2(2000);
376 l_param11 VARCHAR2(2000);
377 l_param12 VARCHAR2(2000);
378 l_param13 VARCHAR2(2000);
379 l_param14 VARCHAR2(2000);
380 l_param15 VARCHAR2(2000);
381
382 l_batch_id NUMBER;
383 l_object_version_number NUMBER;
384 l_batch_line_id NUMBER;
385 l_element_name pay_element_types_f.element_name%type;
386
387 l_mask varchar2(100) ;
388
389 CURSOR cn_repositories_cur IS
390 SELECT glsob.set_of_books_id set_of_books_id,
391 glsob.currency_code currency_code
392 FROM gl_sets_of_books glsob,
393 cn_repositories cnr,
394 cn_payruns cnp
395 WHERE cnr.set_of_books_id = glsob.set_of_books_id
396 AND cnr.org_id = cnp.org_id
397 AND cnp.payrun_id = p_payrun_id;
398
399 l_repositories_rec cn_repositories_cur%ROWTYPE;
400
401 --Bug 3995491(11.5.8 bug 3925653, 11.5.10 bug 3995477) by jjhuang on 11/5/04.
402 l_action_if_exists hr_lookups.lookup_code%TYPE;
403
404 BEGIN
405
406 --
407 -- Set the status
408 --
409 x_loading_status := p_loading_status;
410 x_return_status := FND_API.G_RET_STS_SUCCESS;
411
412 --
413 -- get Payruns detail, org_id, period_id, pay_date, name
414 --
415 l_batch_id := x_batch_id;
416
417 --Bug 3995491(11.5.8 bug 3925653, 11.5.10 bug 3995477) by jjhuang on 11/5/04.
418 l_action_if_exists := fnd_profile.value('CN_PAYROLL_ACTION_IF_ENTRY_EXISTS');
419
420 --
421 -- get cn_repositories
422 --
423
424 OPEN cn_repositories_cur;
425 FETCH cn_repositories_cur INTO l_repositories_rec;
426 CLOSE cn_repositories_cur;
427
428 --
429 -- Get currency Mask
430 --
431
432 l_mask := fnd_currency.get_format_mask((Nvl(l_repositories_rec.currency_code,'USD')),20);
433
434 --
435 -- get Payruns detail, org_id, period_id, pay_date, name
436 --
437
438 open get_payruns;
439 fetch get_payruns into l_payrun_rec;
440 close get_payruns;
441
442 --Bug 3314913 by jjhuang on 12/15/03. In length(cn_payruns.name)=80, but the length for integration only needs 30.
443 l_payrun_rec.name := substr(l_payrun_rec.name, 1, 30);
444
445 --
446 -- get assignment , assignment_id, bussiness group id, status
447 --
448 open get_assign_id(l_payrun_rec.org_id);
449 fetch get_assign_id into l_assignment_rec;
450 close get_assign_id;
451
452 IF l_assignment_rec.source_business_grp_id is not null THEN
453
454 --
455 -- Get the inputs, column name, column_value, table_name
456 --
457
458 --
459 -- Get the Element Name
460 --
461
462 open get_element_name ( p_element_type_id);
463 fetch get_element_name into l_element_name;
464 close get_element_name;
465
466
467
468 FOR tab_columns IN pay_element_inputs( nvl(l_assignment_rec.status,'A'),
469 l_repositories_rec.currency_code)
470 LOOP
471
472 -- AC 04/09/2003 2892822 : should not use p_amount, cause p_amount is
473 -- sum of all pmt trx with same quota_id,srp_id,incentive_type,
474 -- pay_element_type_id but the dynamic sql statement here cannot do a
475 -- group by so should use original payment_amount
476
477 IF tab_columns.table_name = 'CN_PAYMENT_TRANSACTIONS' and
478 tab_columns.column_name = 'PAYMENT_AMOUNT' THEN
479 l_select := l_select || tab_columns.table_name
480 ||'.'||tab_columns.column_name -- nvl(p_amount,0)
481 || ' C_'||l_count|| ',';
482 --Commented by Sundar Venkat for bug fix 2660893
483 --IF l_flag_payment_transactions = 'N' THEN
484 -- l_from := l_from || ', ' || tab_columns.table_name ;
485 -- l_where := l_where || ' AND CN_PAYMENT_TRANSACTIONS.PAYRUN_ID = CN_PAYRUNS.PAYRUN_ID ' ;
486 -- l_flag_payment_transactions := 'Y';
487 --END IF ;
488
489 ELSIF tab_columns.table_name iS NOT NULL THEN
490
491 l_select := l_select || tab_columns.table_name
492 ||'.'||tab_columns.column_name
493 || ' C_'||l_count|| ',';
494 --Commented by Sundar Venkat for bug fix 2660893
495 -- IF l_flag_payment_transactions = 'N' AND tab_columns.table_name = 'CN_PAYMENT_TRANSACTIONS' THEN
496 -- l_from := l_from || ', ' || tab_columns.table_name ;
497 -- l_where := l_where || ' AND CN_PAYMENT_TRANSACTIONS.PAYRUN_ID= CN_PAYRUNS.PAYRUN_ID ' ;
498 -- l_flag_payment_transactions := 'Y';
499 -- END IF ;
500
501
502 ELSE
503 l_select := l_select || 'NULL'
504 || ' C_'||l_count|| ',';
505 END IF;
506 l_count := pay_element_inputs%ROWCOUNT;
507 END LOOP;
508
509 --
510 -- remove the extra comma
511 --
512 l_select := substr(l_select, 1, length(l_select)-1);
513
514 --
515 -- check if the l_count
516 --
517 IF l_count < 15 THEN
518 FOR i in 1..(15 - l_count) LOOP
519 if l_select is null then
520 l_select := l_select || ' NULL ' ;
521 else
522 l_select := l_select || ',' || ' NULL ' ;
523 end if;
524 END LOOP;
525 END IF;
526
527
528 --
529 -- get the where clause
530 --
531 l_select := ' SELECT ' || l_select
532 || ' FROM ' || l_from
533 || ' WHERE ' || l_where;
534
535 --
536 -- Processs only if there is column mapping.
537 --
538 IF l_count > 0
539 THEN
540 --Modified by Sundar Venkat for bug fix 2660893
541 open main_cursor for l_select using p_payrun_id, p_salesrep_id, p_quota_id, p_incentive_type_code ;
542 loop
543
544 fetch main_cursor into l_param1
545 ,l_param2
546 ,l_param3
547 ,l_param4
548 ,l_param5
549 ,l_param6
550 ,l_param7
551 ,l_param8
552 ,l_param9
553 ,l_param10
554 ,l_param11
555 ,l_param12
556 ,l_param13
557 ,l_param14
558 ,l_param15;
559 exit when main_cursor%notfound;
560
561 -- Call the BEE Interface
562
563 IF l_batch_id IS NULL THEN
564
565 IF l_action_if_exists IS NULL
566 THEN
567 PAY_BATCH_ELEMENT_ENTRY_API.CREATE_BATCH_HEADER
568 (p_validate => FALSE,
569 p_session_date => l_payrun_rec.pay_date,
570 p_batch_name => l_payrun_rec.name,
571 p_business_group_id => l_assignment_rec.source_business_grp_id,
572 p_batch_reference => l_payrun_rec.name,
573 p_batch_source => 'CN',
574 p_comments => NULL,
575 p_purge_after_transfer => 'N',
576 p_batch_id => l_batch_id,
577 P_OBJECT_VERSION_NUMBER => l_object_version_number );
578 ELSE
579 IF l_action_if_exists = 'U' THEN
580 PAY_BATCH_ELEMENT_ENTRY_API.CREATE_BATCH_HEADER
581 (p_validate => FALSE,
582 p_session_date => l_payrun_rec.pay_date,
583 p_batch_name => l_payrun_rec.name,
584 p_business_group_id => l_assignment_rec.source_business_grp_id,
585 p_action_if_exists => l_action_if_exists,
586 p_batch_reference => l_payrun_rec.name,
587 p_batch_source => 'CN',
588 p_comments => NULL,
589 p_date_effective_changes => 'C',
590 p_purge_after_transfer => 'N',
591 p_batch_id => l_batch_id,
592 P_OBJECT_VERSION_NUMBER => l_object_version_number );
593 ELSE
594 PAY_BATCH_ELEMENT_ENTRY_API.CREATE_BATCH_HEADER
595 (p_validate => FALSE,
596 p_session_date => l_payrun_rec.pay_date,
597 p_batch_name => l_payrun_rec.name,
598 p_business_group_id => l_assignment_rec.source_business_grp_id,
599 p_action_if_exists => l_action_if_exists,
600 p_batch_reference => l_payrun_rec.name,
601 p_batch_source => 'CN',
602 p_comments => NULL,
603 p_purge_after_transfer => 'N',
604 p_batch_id => l_batch_id,
605 P_OBJECT_VERSION_NUMBER => l_object_version_number );
606 END IF ;
607 END IF;
608
609
610 IF l_batch_id IS NULL THEN
611 --Error condition
612 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
613 THEN
614 fnd_message.set_name('CN', 'CN_BATCH_HEADER_FAILED');
615 fnd_msg_pub.add;
616 END IF;
617
618 x_loading_status := 'CN_BATCH_HEADER_FAILED';
619 RAISE FND_API.G_EXC_ERROR;
620
621 END IF;
622 END IF;
623
624 PAY_BATCH_ELEMENT_ENTRY_API.CREATE_BATCH_LINE
625 (p_validate => FALSE,
626 p_session_date => l_payrun_rec.pay_date,
627 p_batch_id => l_batch_id,
628 p_batch_line_status => 'U',
629 p_assignment_id => l_assignment_rec.assignment_id,
630 p_assignment_number => l_assignment_rec.assignment_number,
631 p_element_type_id => p_element_type_id,
632 p_element_name => l_element_name,
633 p_effective_date => l_payrun_rec.pay_date,
634 p_entry_type => 'E',
635 p_value_1 => l_param1,
636 p_value_2 => l_param2,
637 p_value_3 => l_param3,
638 p_value_4 => l_param4,
639 p_value_5 => l_param5,
640 p_value_6 => l_param6,
641 p_value_7 => l_param7,
642 p_value_8 => l_param8,
643 p_value_9 => l_param9,
644 p_value_10 => l_param10,
645 p_value_11 => l_param11,
646 p_value_12 => l_param12,
647 p_value_13 => l_param13,
648 p_value_14 => l_param14,
649 p_value_15 => l_param15,
650 p_batch_line_id => l_batch_line_id,
651 p_object_version_number => l_object_version_number);
652
653
654 IF l_batch_line_id IS NULL THEN
655 --Error condition
656 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
657 THEN
658 fnd_message.set_name('CN', 'CN_BATCH_LINE_FAILED');
659 fnd_msg_pub.add;
660 END IF;
661
662 x_loading_status := 'CN_BATCH_LINE_FAILED';
663 RAISE FND_API.G_EXC_ERROR;
664
665 END IF;
666
667 end loop;
668 close main_cursor;
669 END IF;
670
671 END IF; -- business group id is not null
672
673 x_batch_id := l_batch_id;
674
675 -- End of Building and Calling BEE API
676 -- Standard call to get message count and if count is 1,
677 -- get message info.
678
679 FND_MSG_PUB.Count_And_Get
680 (
681 p_count => x_msg_count,
682 p_data => x_msg_data,
683 p_encoded => FND_API.G_FALSE
684 );
685
686 EXCEPTION
687 WHEN FND_API.G_EXC_ERROR THEN
688 x_return_status := FND_API.G_RET_STS_ERROR ;
689
690 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
691 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
692 x_loading_status := 'UNEXPECTED_ERR';
693
694 WHEN OTHERS THEN
695 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
696 x_loading_status := 'UNEXPECTED_ERR';
697 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
698 THEN
699 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME ,l_api_name );
700 END IF;
701
702 END;
703 -- ===========================================================================
704 -- Procedure : Build and call the BEE API
705 -- Description : called from pay Payrun passing the payrun id as the input
706 -- input parameter.
707 -- ===========================================================================
708 PROCEDURE BUILD_BEE_API
709 (x_return_status OUT NOCOPY VARCHAR2,
710 x_msg_count OUT NOCOPY NUMBER,
711 x_msg_data OUT NOCOPY VARCHAR2,
712 p_payrun_id IN NUMBER,
713 p_loading_status IN VARCHAR2,
714 x_loading_status OUT NOCOPY VARCHAR2) IS
715
716 l_api_name CONSTANT VARCHAR2(30) := 'Build_Bee_Api';
717
718 --group by clause modified by Sundar Venkat for bug fix 2660893
719
720 CURSOR payment_curs IS
721 SELECT sum(nvl(pt.payment_amount,0)) payment_amount,
722 pt.credited_salesrep_id,
723 pt.pay_element_type_id,
724 pt.quota_id,
725 pt.incentive_type_code
726 FROM cn_payment_transactions pt
727 WHERE pt.payrun_id = p_payrun_id
728 AND nvl(waive_flag,'N') = 'N'
729 AND nvl(hold_flag, 'N') = 'N'
730 GROUP BY pt.quota_id,
731 pt.credited_salesrep_id,
732 pt.pay_element_type_id,
733 pt.incentive_type_code;
734
735
736 l_rec_nrec_amount NUMBER := 0;
737 x_batch_id NUMBER := NULL;
738
739 BEGIN
740
741 --
742 -- Initialize API return status to success
743 --
744 x_loading_status := p_loading_status ;
745 x_return_status := FND_API.G_RET_STS_SUCCESS;
746
747 IF p_payrun_id IS NULL THEN
748 --Error condition
749 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
750 THEN
751 fnd_message.set_name('CN', 'CN_INVALID_PAYRUN');
752 fnd_msg_pub.add;
753 END IF;
754 x_loading_status := 'CN_INVALID_PAYRUN';
755 RAISE FND_API.G_EXC_ERROR;
756 END IF;
757
758
759 FOR payment IN payment_curs LOOP
760
761 Build_Parse_Fetch_Call
762 (x_return_status => x_return_status,
763 x_msg_count => x_msg_count,
764 x_msg_data => x_msg_data,
765 p_payrun_id => p_payrun_id,
766 p_salesrep_id => payment.credited_salesrep_id,
767 p_element_type_id=> payment.pay_element_type_id,
768 p_quota_id => payment.quota_id,
769 p_incentive_type_code => payment.incentive_type_code,
770 p_amount => payment.payment_amount,
771 p_loading_status => x_loading_status,
772 x_loading_status => x_loading_status,
773 x_batch_id => x_batch_id);
774
775 IF x_return_status <> FND_API.g_ret_sts_success THEN
776 RAISE FND_API.G_EXC_ERROR;
777 END IF;
778 END LOOP;
779
780
781 -- End of Building and Calling BEE API
782 -- Standard call to get message count and if count is 1,
783 -- get message info.
784
785 FND_MSG_PUB.Count_And_Get
786 (
787 p_count => x_msg_count,
788 p_data => x_msg_data,
789 p_encoded => FND_API.G_FALSE
790 );
791
792 EXCEPTION
793 WHEN FND_API.G_EXC_ERROR THEN
794 x_return_status := FND_API.G_RET_STS_ERROR ;
795
796 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
797 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
798 x_loading_status := 'UNEXPECTED_ERR';
799
800 WHEN OTHERS THEN
801 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
802 x_loading_status := 'UNEXPECTED_ERR';
803 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
804 THEN
805 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME ,l_api_name );
806 END IF;
807
808 END Build_BEE_Api;
809
810
811 FUNCTION validate_pay
812 (
813 p_payrun IN NUMBER,
814 p_pay_date IN DATE,
815 p_loading_status IN VARCHAR2,
816 x_loading_status OUT NOCOPY VARCHAR2
817 )
818 --RETURN VARCHAR2 IS
819 RETURN BOOLEAN
820 IS
821
822 l_api_name CONSTANT VARCHAR2(30) := 'Validate_pay';
823 l_quota_payelements NUMBER;
824 l_num_pe_mapping NUMBER;
825 l_pe_name varchar2(80);
826
827
828 CURSOR get_quotas(p_payrun cn_payruns.payrun_id%TYPE) IS
829 select distinct w.quota_id,q.name
830 from cn_payment_worksheets w,
831 cn_quotas q,cn_salesreps cns
832 where payrun_id=p_payrun
833 and w.quota_id is not null
834 and w.quota_id=q.quota_id
835 and w.salesrep_id=cns.salesrep_id
836 and w.org_id = cns.org_id
837 and cns.type='EMPLOYEE';
838
839
840 CURSOR get_quota_payelements(p_quotaId NUMBER,p_pay_date DATE) IS
841 select COUNT(*) from
842 cn_quota_pay_elements
843 where quota_id = p_quotaId
844 and p_pay_date
845 between
846 START_DATE and END_DATE;
847
848 CURSOR get_num_pe_mapping (p_quota_id NUMBER) IS
849 select COUNT(*) from
850 cn_pay_element_inputs_all cp,
851 cn_quota_pay_elements_all cq
852 WHERE
853 cp.quota_pay_element_id = cq.quota_pay_element_id
854 AND cq.quota_id =p_quota_id;
855
856
857
858
859 BEGIN
860 x_loading_status := p_loading_status ;
861
862 FOR quotas IN get_quotas(p_payrun)
863 LOOP
864 l_quota_payelements := 0;
865 l_num_pe_mapping :=0;
866
867 OPEN get_quota_payelements(quotas.quota_id,p_pay_date);
868 FETCH get_quota_payelements INTO l_quota_payelements;
869 CLOSE get_quota_payelements;
870
871
872
873 IF l_quota_payelements = 0 THEN
874 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
875 THEN
876 fnd_message.set_name('CN', 'CN_PE_PAYE_MAPPING');
877 fnd_msg_pub.add;
878 END IF;
879
880 x_loading_status := 'CN_PE_PAYE_MAPPING';
881 RAISE FND_API.G_EXC_ERROR;
882 END IF;
883
884 OPEN get_num_pe_mapping(quotas.quota_id);
885 FETCH get_num_pe_mapping INTO l_num_pe_mapping;
886 CLOSE get_num_pe_mapping;
887
888 IF l_num_pe_mapping = 0 THEN
889 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
890 THEN
891 fnd_message.set_name('CN', 'CN_PE_MAPPING');
892 fnd_message.set_token('PE_NAME', quotas.name);
893 fnd_msg_pub.add;
894 END IF;
895
896 x_loading_status := 'CN_PE_MAPPING';
897 RAISE FND_API.G_EXC_ERROR;
898 END IF;
899
900 END LOOP;
901
902 RETURN TRUE;
903
904 EXCEPTION
905 WHEN FND_API.G_EXC_ERROR THEN
906 RETURN FALSE;
907
908 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
909 x_loading_status := 'UNEXPECTED_ERR';
910 RETURN FALSE;
911
912 WHEN OTHERS THEN
913 x_loading_status := 'UNEXPECTED_ERR';
914
915 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
916 THEN
917 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME ,l_api_name );
918 END IF;
919 RETURN FALSE;
920
921
922 END validate_pay;
923
924
925
926
927 -- ===========================================================================
928 -- Procedure : Validate_pay_date
929 -- Description : This procedure is used to check if the pay_date < = start_date
930 -- Calls :
931 -- ===========================================================================
932 FUNCTION validate_pay_date
933 (
934 p_pay_date IN DATE,
935 p_start_date IN DATE,
936 p_loading_status IN VARCHAR2,
937 x_loading_status OUT NOCOPY VARCHAR2
938 ) RETURN VARCHAR2 IS
939
940 l_api_name CONSTANT VARCHAR2(30) := 'Validate_pay_date';
941
942 BEGIN
943
944 --
945 -- Initialize API return status to success
946 --
947 x_loading_status := p_loading_status ;
948
949 IF p_pay_date < p_start_date
950 THEN
951
952 --Error condition
953 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
954 THEN
955 fnd_message.set_name('CN', 'CN_INVALID_PAY_DATE');
956 fnd_msg_pub.add;
957 END IF;
958
959 x_loading_status := 'CN_INVALID_PAY_DATE';
960 RAISE FND_API.G_EXC_ERROR;
961
962 END IF;
963
964 RETURN fnd_api.g_false;
965
966 EXCEPTION
967 WHEN FND_API.G_EXC_ERROR THEN
968 RETURN fnd_api.g_true;
969
970 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
971 x_loading_status := 'UNEXPECTED_ERR';
972 RETURN fnd_api.g_true;
973
974 WHEN OTHERS THEN
975 x_loading_status := 'UNEXPECTED_ERR';
976 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
977 THEN
978 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME ,l_api_name );
979 END IF;
980 RETURN fnd_api.g_true;
981
982 END validate_pay_date;
983
984
985
986 -- ===========================================================================
987 --
988 -- Procedure : Validate_payrun_status
989 -- Description : This procedure is used to check if the pay status is null or unpaid
990 -- Calls :
991 --
992 -- ===========================================================================
993 FUNCTION validate_payrun_status
994 (
995 p_status IN cn_payruns.status%TYPE,
996 p_loading_status IN VARCHAR2,
997 x_loading_status OUT NOCOPY VARCHAR2
998 ) RETURN VARCHAR2 IS
999
1000 l_api_name CONSTANT VARCHAR2(30) := 'Validate_status';
1001
1002 BEGIN
1003
1004 --
1005 -- Initialize API return status to success
1006 --
1007 x_loading_status := p_loading_status ;
1008
1009
1010 IF p_status <> '' OR p_status <> 'UNPAID'
1011 THEN
1012
1013 --Error condition
1014 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
1015 THEN
1016 fnd_message.set_name('CN', 'CN_INVALID_PAYRUN_STATUS');
1017 fnd_msg_pub.add;
1018 END IF;
1019
1020 x_loading_status := 'CN_INVALID_PAYRUN_STATUS';
1021 RAISE FND_API.G_EXC_ERROR;
1022
1023 END IF;
1024
1025 RETURN fnd_api.g_false;
1026
1027 EXCEPTION
1028 WHEN FND_API.G_EXC_ERROR THEN
1029 RETURN fnd_api.g_true;
1030
1031 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1032 x_loading_status := 'UNEXPECTED_ERR';
1033 RETURN fnd_api.g_true;
1034
1035 WHEN OTHERS THEN
1036 x_loading_status := 'UNEXPECTED_ERR';
1037
1038 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1039 THEN
1040 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME ,l_api_name );
1041 END IF;
1042 RETURN fnd_api.g_true;
1043
1044 END validate_payrun_status;
1045 -- ===========================================================================
1046 --
1047 -- Procedure : Validate_name_unique
1048 -- Description : This procedure is used to check if the name of the pay run is unique
1049 -- Calls :
1050 --
1051 -- ===========================================================================
1052 FUNCTION validate_name_unique
1053 (
1054 p_name IN cn_payruns.name%TYPE,
1055 p_org_id IN cn_payruns.org_id%TYPE,
1056 p_loading_status IN VARCHAR2,
1057 x_loading_status OUT NOCOPY VARCHAR2
1058 ) RETURN VARCHAR2 IS
1059
1060 l_api_name CONSTANT VARCHAR2(30) := 'Validate_name_unique';
1061
1062 CURSOR get_count IS
1063 SELECT COUNT(1)
1064 FROM cn_payruns
1065 WHERE name = p_name
1066 AND org_id=p_org_id;
1067
1068 l_count NUMBER;
1069
1070 BEGIN
1071
1072 --
1073 -- Initialize API return status to success
1074 --
1075 x_loading_status := p_loading_status ;
1076
1077 OPEN get_count;
1078 FETCH get_count INTO l_count;
1079 CLOSE get_count;
1080
1081 IF l_count <> 0 THEN
1082
1083 --Error condition
1084 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
1085 THEN
1086 fnd_message.set_name('CN', 'CN_NUNIQUE_PAYRUN_NAME');
1087 fnd_msg_pub.add;
1088 END IF;
1089
1090 x_loading_status := 'CN_NUNIQUE_PAYRUN_NAME';
1091 RAISE FND_API.G_EXC_ERROR;
1092
1093 END IF;
1094
1095 RETURN fnd_api.g_false;
1096
1097 EXCEPTION
1098 WHEN FND_API.G_EXC_ERROR THEN
1099 RETURN fnd_api.g_true;
1100
1101 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1102 x_loading_status := 'UNEXPECTED_ERR';
1103 RETURN fnd_api.g_true;
1104
1105 WHEN OTHERS THEN
1106 x_loading_status := 'UNEXPECTED_ERR';
1107
1108 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1109 THEN
1110 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME ,l_api_name );
1111 END IF;
1112 RETURN fnd_api.g_true;
1113
1114 END validate_name_unique;
1115
1116
1117
1118 -- ===========================================================================
1119 --
1120 -- Procedure : Validate_pay_group
1121 -- Description : This procedure is used to validate if the pay group exists
1122 -- Calls :
1123 --
1124 -- ===========================================================================
1125
1126 FUNCTION validate_pay_group
1127 (
1128 p_pay_group_id IN cn_payruns.pay_group_id%TYPE,
1129 p_loading_status IN VARCHAR2,
1130 x_loading_status OUT NOCOPY VARCHAR2
1131 ) RETURN VARCHAR2 IS
1132
1133 l_api_name CONSTANT VARCHAR2(30) := 'Validate_pay_group';
1134
1135 CURSOR get_count IS
1136 SELECT COUNT(1)
1137 FROM cn_pay_groups
1138 WHERE pay_group_id = p_pay_group_id;
1139
1140 l_count NUMBER;
1141
1142 BEGIN
1143
1144 --
1145 -- Initialize API return status to success
1146 --
1147 x_loading_status := p_loading_status ;
1148
1149 OPEN get_count;
1150 FETCH get_count INTO l_count;
1151 CLOSE get_count;
1152
1153 IF l_count = 0 THEN
1154
1155 --Error condition
1156 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
1157 THEN
1158 fnd_message.set_name('CN', 'CN_INVALID_PAY_GROUP');
1159 fnd_msg_pub.add;
1160 END IF;
1161
1162 x_loading_status := 'CN_INVALID_PAY_GROUP';
1163 RAISE FND_API.G_EXC_ERROR;
1164
1165 END IF;
1166
1167 RETURN fnd_api.g_false;
1168
1169 EXCEPTION
1170 WHEN FND_API.G_EXC_ERROR THEN
1171 RETURN fnd_api.g_true;
1172
1173 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1174 x_loading_status := 'UNEXPECTED_ERR';
1175 RETURN fnd_api.g_true;
1176
1177 WHEN OTHERS THEN
1178 x_loading_status := 'UNEXPECTED_ERR';
1179
1180 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1181 THEN
1182 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME ,l_api_name );
1183 END IF;
1184 RETURN fnd_api.g_true;
1185
1186 END validate_pay_group;
1187
1188
1189 -- ===========================================================================
1190 --
1191 -- Procedure : Validate_pay_period
1192 -- Description : This procedure is used to validate if the pay period exists
1193 -- Calls :
1194 --
1195 -- ===========================================================================
1196
1197 FUNCTION validate_pay_period
1198 (
1199 p_pay_group_id IN cn_payruns.pay_group_id%TYPE,
1200 p_pay_period_id IN cn_payruns.pay_period_id%TYPE,
1201 p_loading_status IN VARCHAR2,
1202 x_loading_status OUT NOCOPY VARCHAR2
1203 ) RETURN VARCHAR2 IS
1204
1205 l_api_name CONSTANT VARCHAR2(30) := 'Validate_pay_period';
1206
1207 CURSOR get_count IS
1208 SELECT COUNT(1)
1209 FROM cn_pay_groups cnpg,
1210 cn_period_statuses cnps
1211 WHERE cnpg.pay_group_id = p_pay_group_id
1212 AND cnpg.period_set_name = cnps.period_set_name
1213 AND cnpg.period_type = cnps.period_type
1214 AND cnps.period_id = p_pay_period_id
1215 AND cnpg.org_id = cnps.org_id;
1216
1217 l_count NUMBER;
1218
1219 BEGIN
1220
1221 --
1222 -- Initialize API return status to success
1223 --
1224 x_loading_status := p_loading_status ;
1225
1226 OPEN get_count;
1227 FETCH get_count INTO l_count;
1228 CLOSE get_count;
1229
1230 IF l_count = 0 THEN
1231
1232 --Error condition
1233 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
1234 THEN
1235 fnd_message.set_name('CN', 'CN_INVALID_PAY_PERIOD');
1236 fnd_msg_pub.add;
1237 END IF;
1238
1239 x_loading_status := 'CN_INVALID_PAY_PERIOD';
1240 RAISE FND_API.G_EXC_ERROR;
1241
1242 END IF;
1243
1244 RETURN fnd_api.g_false;
1245
1246 EXCEPTION
1247 WHEN FND_API.G_EXC_ERROR THEN
1248 RETURN fnd_api.g_true;
1249
1250 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1251 x_loading_status := 'UNEXPECTED_ERR';
1252 RETURN fnd_api.g_true;
1253
1254 WHEN OTHERS THEN
1255 x_loading_status := 'UNEXPECTED_ERR';
1256
1257 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1258 THEN
1259 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME ,l_api_name );
1260 END IF;
1261 RETURN fnd_api.g_true;
1262
1263 END validate_pay_period;
1264
1265
1266 -- ===========================================================================
1267 --
1268 -- Procedure : Check_unpaid_payrun
1269 -- Description : This procedure is used to check if an unpaid payrun exists
1270 -- Calls :
1271 --
1272 -- ===========================================================================
1273
1274 FUNCTION check_unpaid_payrun
1275 (
1276 p_pay_group_id IN cn_payruns.pay_group_id%TYPE,
1277 p_loading_status IN VARCHAR2,
1278 x_loading_status OUT NOCOPY VARCHAR2
1279 ) RETURN VARCHAR2 IS
1280
1281 l_api_name CONSTANT VARCHAR2(30) := 'Check_unpaid_payrun';
1282
1283 CURSOR get_count IS
1284 SELECT COUNT(1)
1285 FROM cn_payruns
1286 WHERE pay_group_id = p_pay_group_id
1287 AND status <> 'PAID';
1288
1289 l_count NUMBER;
1290
1291 BEGIN
1292
1293 --
1294 -- Initialize API return status to success
1295 --
1296 x_loading_status := p_loading_status ;
1297
1298 OPEN get_count;
1299 FETCH get_count INTO l_count;
1300 CLOSE get_count;
1301
1302 IF l_count <> 0 THEN
1303
1304 --Error condition
1305 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
1306 THEN
1307 fnd_message.set_name('CN', 'CN_UNPAID_PAYRUN_EXISTS');
1308 fnd_msg_pub.add;
1309 END IF;
1310
1311 x_loading_status := 'CN_UNPAID_PAYRUN_EXISTS';
1312 RAISE FND_API.G_EXC_ERROR;
1313
1314 END IF;
1315
1316 RETURN fnd_api.g_false;
1317
1318 EXCEPTION
1319 WHEN FND_API.G_EXC_ERROR THEN
1320 RETURN fnd_api.g_true;
1321
1322 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1323 x_loading_status := 'UNEXPECTED_ERR';
1324 RETURN fnd_api.g_true;
1325
1326 WHEN OTHERS THEN
1327 x_loading_status := 'UNEXPECTED_ERR';
1328
1329 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1330 THEN
1331 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME ,l_api_name );
1332 END IF;
1333 RETURN fnd_api.g_true;
1334
1335 END check_unpaid_payrun;
1336
1337 -- ===========================================================================
1338 --
1339 -- Procedure : Chk_last_paid_prd
1340 -- Description : This procedure is used to check which period got paid last
1341 -- Calls :
1342 --
1343 -- ===========================================================================
1344
1345 FUNCTION chk_last_paid_prd
1346 (
1347 p_pay_group_id IN cn_payruns.pay_group_id%TYPE,
1348 p_org_id IN cn_payruns.org_id%TYPE,
1349 p_pay_period_id IN cn_payruns.pay_period_id%TYPE,
1350 p_loading_status IN VARCHAR2,
1351 x_loading_status OUT NOCOPY VARCHAR2
1352 ) RETURN VARCHAR2 IS
1353
1354 l_api_name CONSTANT VARCHAR2(30) := 'Chk_last_paid_prd';
1355
1356 CURSOR get_last_pay_period IS
1357 SELECT pay_period_id
1358 FROM cn_payruns
1359 WHERE pay_group_id = p_pay_group_id
1360 ORDER BY payrun_id desc ;
1361
1362 CURSOR get_period_range (p_period_id IN cn_period_statuses.period_id%TYPE) IS
1363 SELECT start_date, end_date
1364 FROM cn_period_statuses
1365 WHERE period_id = p_period_id
1366 AND org_id=p_org_id;
1367
1368 l_get_period_range_rec get_period_range%ROWTYPE;
1369
1370
1371 l_pay_period_id cn_payruns.pay_period_id%TYPE;
1372 l_last_start_date cn_period_statuses.start_date%TYPE;
1373 l_cur_start_date cn_period_statuses.start_date%TYPE;
1374
1375 BEGIN
1376
1377 --
1378 -- Initialize API return status to success
1379 --
1380 x_loading_status := p_loading_status ;
1381
1382 OPEN get_last_pay_period;
1383 FETCH get_last_pay_period INTO l_pay_period_id;
1384 CLOSE get_last_pay_period;
1385
1386 OPEN get_period_range (l_pay_period_id);
1387 FETCH get_period_range INTO l_get_period_range_rec;
1388 CLOSE get_period_range;
1389 l_last_start_date := l_get_period_range_rec.start_date;
1390
1391 OPEN get_period_range (p_pay_period_id);
1392 FETCH get_period_range INTO l_get_period_range_rec;
1393 CLOSE get_period_range;
1394 l_cur_start_date := l_get_period_range_rec.start_date;
1395
1396
1397 IF l_last_start_date > l_cur_start_date THEN
1398
1399 --Error condition
1400 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
1401 THEN
1402 fnd_message.set_name('CN', 'CN_NEWER_PAYRUN_EXISTS');
1403 fnd_msg_pub.add;
1404 END IF;
1405
1406 x_loading_status := 'CN_NEWER_PAYRUN_EXISTS';
1407 RAISE FND_API.G_EXC_ERROR;
1408
1409 END IF;
1410
1411 RETURN fnd_api.g_false;
1412
1413 EXCEPTION
1414 WHEN FND_API.G_EXC_ERROR THEN
1415 RETURN fnd_api.g_true;
1416
1417 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1418 x_loading_status := 'UNEXPECTED_ERR';
1419 RETURN fnd_api.g_true;
1420
1421 WHEN OTHERS THEN
1422 x_loading_status := 'UNEXPECTED_ERR';
1423
1424 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1425 THEN
1426 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME ,l_api_name );
1427 END IF;
1428 RETURN fnd_api.g_true;
1429
1430 END chk_last_paid_prd;
1431
1432
1433
1434 -- ===========================================================================
1435 --
1436 -- Procedure : populate_ap_interface
1437 -- Description : This is used to populate the AP interface for
1438 -- salesreps in the specified payrun who are of
1439 -- type supplier contact.
1440 -- Calls :
1441 --
1442 -- ===========================================================================
1443
1444 FUNCTION populate_ap_interface
1445 (
1446 p_payrun_id IN cn_payruns.payrun_id%TYPE,
1447 p_loading_status IN VARCHAR2,
1448 x_loading_status OUT NOCOPY VARCHAR2
1449 ) RETURN VARCHAR2 IS
1450
1451 G_LAST_UPDATE_DATE DATE := sysdate;
1452 G_LAST_UPDATED_BY NUMBER := fnd_global.user_id;
1453 G_CREATION_DATE DATE := sysdate;
1454 G_CREATED_BY NUMBER := fnd_global.user_id;
1455 G_LAST_UPDATE_LOGIN NUMBER := fnd_global.login_id;
1456
1457 l_api_name CONSTANT VARCHAR2(30) := 'populate_ap_interface';
1458 --for Performance issues SQL ID 16120772 we had to break this sql
1459 /*
1460 CURSOR get_vendors IS
1461 SELECT cns.salesrep_id, pvs.vendor_id supplier_id,
1462 pvc.vendor_site_id supplier_site_id,
1463 currency_code
1464 FROM cn_salesreps cns,
1465 cn_payment_worksheets cnpw,
1466 po_vendor_sites pvs,
1467 po_vendor_contacts pvc
1468 WHERE cnpw.payrun_id = p_payrun_id
1469 AND cnpw.org_id=cns.org_id
1470 AND cnpw.salesrep_id = cns.salesrep_id
1471 AND cnpw.quota_id IS NULL
1472 AND cns.source_id = pvc.vendor_contact_id
1473 AND pvc.vendor_site_id = pvs.vendor_site_id
1474 AND cns.type = 'SUPPLIER_CONTACT';
1475 */
1476
1477
1478
1479
1480
1481 CURSOR get_salesreps IS
1482 SELECT cns.salesrep_id,cns.currency_code,cns.source_id
1483 FROM cn_salesreps cns,
1484 cn_payment_worksheets cnpw
1485 WHERE cnpw.payrun_id = p_payrun_id
1486 AND cnpw.org_id=cns.org_id
1487 AND cnpw.salesrep_id = cns.salesrep_id
1488 AND cnpw.quota_id IS NULL
1489 AND cns.type = 'SUPPLIER_CONTACT'
1490 AND cns.source_id IS NOT NULL;
1491
1492
1493 CURSOR get_vendors(p_source_id IN cn_payment_transactions.credited_salesrep_id%TYPE) IS
1494 SELECT pvs.vendor_id supplier_id,
1495 pvc.vendor_site_id supplier_site_id
1496 FROM
1497 po_vendor_sites pvs,
1498 po_vendor_contacts pvc
1499 WHERE
1500 pvc.vendor_site_id = pvs.vendor_site_id
1501 AND pvc.vendor_contact_id = p_source_id;
1502
1503
1504
1505
1506 --Bug 2922190 by Julia Huang.
1507 CURSOR get_invoice_lines (p_salesrep_id IN cn_payment_transactions.credited_salesrep_id%TYPE) IS
1508 SELECT payment_transaction_id,
1509 ROUND(payment_amount,2) payment_amount,
1510 liability_ccid,
1511 expense_ccid
1512 FROM cn_payment_transactions
1513 WHERE payrun_id = p_payrun_id
1514 AND credited_salesrep_id = p_salesrep_id
1515 AND nvl(hold_flag,'N') = 'N';
1516
1517 CURSOR get_pay_date IS
1518 SELECT pay_date,org_id
1519 FROM cn_payruns
1520 WHERE payrun_id = p_payrun_id;
1521 l_pay_date DATE;
1522
1523 CURSOR get_functional_currency IS
1524 SELECT currency_code
1525 FROM gl_sets_of_books glsob,
1526 cn_repositories cnr,
1527 cn_payruns cnp
1528 WHERE cnr.set_of_books_id = glsob.set_of_books_id
1529 AND cnr.org_id = cnp.org_id
1530 AND cnp.payrun_id= p_payrun_id;
1531 l_functional_currency gl_sets_of_books.currency_code%TYPE;
1532
1533 l_lookup_type VARCHAR2(30) := 'STANDARD';
1534 l_org_id number;
1535 l_supplier_id po_vendor_sites.vendor_id%TYPE;
1536 l_supplier_site_id po_vendor_sites.vendor_site_id%TYPE;
1537
1538 BEGIN
1539
1540 --
1541 -- Initialize API return status to success
1542 --
1543 x_loading_status := p_loading_status ;
1544
1545 OPEN get_pay_date;
1546 FETCH get_pay_date INTO l_pay_date,l_org_id;
1547 CLOSE get_pay_date;
1548
1549 IF l_pay_date IS NULL
1550 THEN
1551 l_pay_date := Sysdate;
1552 END IF;
1553
1554 OPEN get_functional_currency;
1555 FETCH get_functional_currency INTO l_functional_currency;
1556 CLOSE get_functional_currency;
1557
1558 -- Fetch all the salesrep (who are vendors) who are eligible to be paid
1559 -- FOR vendor IN get_vendors
1560 FOR salesreps IN get_salesreps
1561 LOOP
1562 OPEN get_vendors(salesreps.source_id);
1563 FETCH get_vendors INTO l_supplier_id,l_supplier_site_id;
1564 CLOSE get_vendors;
1565
1566 FOR invoice IN get_invoice_lines(salesreps.salesrep_id)
1567 LOOP
1568
1569 -- Create a record in ap_invoices_interface
1570 -- for each Payment Transaction line in OSC (for the invoice)
1571
1572 -- Need to change the inv import later to pass the liability
1573 -- and expense accounts for the invoice
1574 --
1575 -- Added By Kumar Sivasankaran
1576 -- Refer Bug # 2160284
1577 --
1578 IF nvl(invoice.payment_amount,0) < 0 THEN
1579 l_lookup_type := 'CREDIT';
1580 ELSE
1581 l_lookup_type := 'STANDARD';
1582 END IF;
1583
1584 INSERT INTO ap_invoices_interface
1585 (invoice_id,
1586 org_id,
1587 invoice_num,
1588 invoice_date,
1589 vendor_id,
1590 vendor_site_id,
1591 invoice_amount,
1592 invoice_currency_code,
1593 payment_currency_code,
1594 source,
1595 accts_pay_code_combination_id,
1596 invoice_type_lookup_code,
1597 CREATION_DATE,
1598 CREATED_BY,
1599 LAST_UPDATE_DATE,
1600 LAST_UPDATED_BY,
1601 LAST_UPDATE_LOGIN)
1602 VALUES
1603 (ap_invoices_interface_s.NEXTVAL,
1604 l_org_id,
1605 invoice.payment_transaction_id,
1606 l_pay_date,
1607 l_supplier_id,
1608 l_supplier_site_id,
1609 invoice.payment_amount,
1610 l_functional_currency,
1611 Nvl(salesreps.currency_code, l_functional_currency),
1612 'ORACLE_SALES_COMPENSATION',
1613 invoice.liability_ccid,
1614 l_lookup_type,
1615 G_CREATION_DATE,
1616 G_CREATED_BY,
1617 G_LAST_UPDATE_DATE,
1618 G_LAST_UPDATED_BY,
1619 G_LAST_UPDATE_LOGIN);
1620
1621 -- Create a record in ap_invoice_lines_interface
1622 -- for each Payment Transaction line in OSC (for the distribution)
1623
1624 INSERT INTO ap_invoice_lines_interface
1625 (invoice_id,
1626 invoice_line_id,
1627 line_number,
1628 amount,
1629 dist_code_combination_id,
1630 line_type_lookup_code,
1631 org_id,
1632 CREATION_DATE,
1633 CREATED_BY,
1634 LAST_UPDATE_DATE,
1635 LAST_UPDATED_BY,
1636 LAST_UPDATE_LOGIN)
1637 VALUES
1638 (ap_invoices_interface_s.CURRVAL,
1639 ap_invoice_lines_interface_s.NEXTVAL,
1640 1,
1641 invoice.payment_amount,
1642 invoice.expense_ccid,
1643 'ITEM',
1644 l_org_id,
1645 G_CREATION_DATE,
1646 G_CREATED_BY,
1647 G_LAST_UPDATE_DATE,
1648 G_LAST_UPDATED_BY,
1649 G_LAST_UPDATE_LOGIN);
1650
1651 END LOOP;
1652
1653 END LOOP;
1654
1655 RETURN fnd_api.g_false;
1656
1657 EXCEPTION
1658 WHEN FND_API.G_EXC_ERROR THEN
1659 RETURN fnd_api.g_true;
1660
1661 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1662 x_loading_status := 'UNEXPECTED_ERR';
1663 RETURN fnd_api.g_true;
1664
1665 WHEN OTHERS THEN
1666 x_loading_status := 'UNEXPECTED_ERR';
1667
1668 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1669 THEN
1670 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME ,l_api_name );
1671 END IF;
1672 RETURN fnd_api.g_true;
1673
1674 END populate_ap_interface;
1675
1676 -- ===========================================================================
1677 -- Procedure : populate_ccids
1678 -- Description : This is used to populate the expense and liability ccids
1679 -- Calls :
1680 -- ===========================================================================
1681
1682 FUNCTION populate_ccids
1683 (
1684 p_payrun_id IN cn_payruns.payrun_id%TYPE,
1685 p_salesrep_id IN cn_payment_worksheets.salesrep_id%TYPE,
1686 --p_start_date IN DATE,
1687 --p_end_date IN DATE,
1688 -- Bug 3866089 (the same as 11.5.8 bug 3841926, 11.5.10 3866116) by jjhuang on 11/1/04
1689 p_pmt_tran_id IN cn_payment_transactions.payment_transaction_id%TYPE DEFAULT NULL,
1690 p_loading_status OUT NOCOPY VARCHAR2,
1691 x_loading_status OUT NOCOPY VARCHAR2
1692 ) RETURN VARCHAR2 IS
1693
1694 l_api_name CONSTANT VARCHAR2(30) := 'populate_ccids';
1695
1696 CURSOR get_vendors IS
1697 SELECT cns.salesrep_id
1698 FROM cn_salesreps cns,
1699 cn_payruns cnr
1700 WHERE
1701 cns.type = 'SUPPLIER_CONTACT'
1702 AND cns.salesrep_id = p_salesrep_id
1703 AND cns.org_id =cnr.org_id
1704 AND cnr.payrun_id=p_payrun_id;
1705
1706
1707 CURSOR get_invoice_lines IS
1708 SELECT payment_transaction_id
1709 FROM cn_payment_transactions
1710 WHERE payrun_id = p_payrun_id
1711 AND credited_salesrep_id = p_salesrep_id
1712 AND nvl(paid_flag, 'N') = 'N' -- is null -- Bug 2822874
1713 AND payee_salesrep_id = p_salesrep_id
1714 AND payment_transaction_id = NVL(p_pmt_tran_id, payment_transaction_id) -- Bug 3866089 (the same as 11.5.8 bug 3841926, 11.5.10 3866116) by jjhuang on 11/1/04
1715 AND hold_flag = 'N' ;
1716
1717 l_user fnd_user.user_name%TYPE;
1718 l_payables_flag cn_repositories.payables_flag%TYPE;
1719
1720 BEGIN
1721
1722 --
1723 -- Initialize API return status to success
1724 --
1725 x_loading_status := p_loading_status ;
1726 --chaged
1727 SELECT Nvl(payables_flag, 'N')
1728 INTO l_payables_flag
1729 FROM cn_repositories cr,cn_payruns cp
1730 where cp.payrun_id = p_payrun_id
1731 and cp.org_id=cr.org_id;
1732
1733 IF l_payables_flag = 'Y' THEN
1734
1735 SELECT user_name
1736 INTO l_user
1737 FROM fnd_user
1738 WHERE user_id = fnd_global.user_id;
1739
1740 -- Fetch all the salesrep (who are vendors) who are eligible to be paid
1741 FOR vendor IN get_vendors
1742 LOOP
1743
1744 FOR invoice IN get_invoice_lines
1745 LOOP
1746 -- inititate wf process CN Account Generator
1747 cn_wf_pmt_pkg.startprocess
1748 ( p_posting_detail_id => invoice.payment_transaction_id,
1749 p_RequestorUsername => l_user,
1750 p_ProcessOwner => l_user,
1751 p_WorkflowProcess => 'CNACCGEN',
1752 p_Item_Type => 'CNACCGEN');
1753
1754 END LOOP;
1755
1756 END LOOP;
1757
1758 END IF;
1759
1760 RETURN fnd_api.g_false;
1761
1762 EXCEPTION
1763 WHEN FND_API.G_EXC_ERROR THEN
1764 RETURN fnd_api.g_true;
1765
1766 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1767 x_loading_status := 'UNEXPECTED_ERR';
1768 RETURN fnd_api.g_true;
1769
1770 WHEN OTHERS THEN
1771 x_loading_status := 'UNEXPECTED_ERR';
1772
1773 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1774 THEN
1775 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME ,l_api_name );
1776 END IF;
1777 RETURN fnd_api.g_true;
1778
1779 END populate_ccids;
1780
1781 -- ===========================================================================
1782
1783 -- Procedure : Create_Payrun
1784 -- Description: Private API to create a payrun
1785 -- Calls :
1786 --
1787 -- ===========================================================================
1788
1789 PROCEDURE create_payrun
1790 (
1791 p_api_version IN NUMBER,
1792 p_init_msg_list IN VARCHAR2 ,
1793 p_commit IN VARCHAR2,
1794 p_validation_level IN NUMBER,
1795 x_return_status OUT NOCOPY VARCHAR2,
1796 x_msg_count OUT NOCOPY NUMBER,
1797 x_msg_data OUT NOCOPY VARCHAR2,
1798 p_payrun_rec IN OUT NOCOPY payrun_rec_type,
1799 x_loading_status OUT NOCOPY VARCHAR2,
1800 x_status OUT NOCOPY VARCHAR2
1801 ) IS
1802
1803 l_api_name CONSTANT VARCHAR2(30) := 'Create_Payrun';
1804 l_api_version CONSTANT NUMBER := 1.0;
1805
1806 G_LAST_UPDATE_DATE DATE := sysdate;
1807 G_LAST_UPDATED_BY NUMBER := fnd_global.user_id;
1808 G_CREATION_DATE DATE := sysdate;
1809 G_CREATED_BY NUMBER := fnd_global.user_id;
1810 G_LAST_UPDATE_LOGIN NUMBER := fnd_global.login_id;
1811 g_credit_type_id CONSTANT NUMBER := -1000;
1812
1813 l_has_access BOOLEAN;
1814 l_note_msg VARCHAR2(240);
1815 l_note_id NUMBER;
1816 l_profile_value VARCHAR2(1);
1817
1818 BEGIN
1819 --
1820 -- Standard Start of API savepoint
1821 --
1822 SAVEPOINT Create_Payrun;
1823 --
1824 -- Standard call to check for call compatibility.
1825 --
1826 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
1827 p_api_version ,
1828 l_api_name ,
1829 G_PKG_NAME )
1830 THEN
1831 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1832 END IF;
1833 --
1834 -- Initialize message list if p_init_msg_list is set to TRUE.
1835 --
1836 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1837 FND_MSG_PUB.initialize;
1838 END IF;
1839 --
1840 -- Initialize API return status to success
1841 --
1842 x_return_status := FND_API.G_RET_STS_SUCCESS;
1843 x_loading_status := 'CN_INSERTED';
1844 --
1845 -- API body
1846 --Added for R12 payment security check begin.
1847 l_has_access := CN_PAYMENT_SECURITY_PVT.get_security_access(
1848 CN_PAYMENT_SECURITY_PVT.g_type_payrun,
1849 CN_PAYMENT_SECURITY_PVT.g_access_payrun_create);
1850 IF ( l_has_access = FALSE)
1851 THEN
1852 RAISE FND_API.G_EXC_ERROR ;
1853 END IF;
1854 --Added for R12 payment security check end.
1855
1856 -- Mandatory parameters check for name, pay group id, pay period id, pay date
1857
1858 IF ( (cn_api.chk_miss_null_char_para
1859 (p_char_para => p_payrun_rec.name,
1860 p_obj_name =>
1861 cn_api.get_lkup_meaning('PAY_RUN_NAME', 'PAY_RUN_VALIDATION_TYPE'),
1862 p_loading_status => x_loading_status,
1863 x_loading_status => x_loading_status)) = FND_API.G_TRUE )
1864 THEN
1865 RAISE FND_API.G_EXC_ERROR ;
1866 END IF;
1867
1868 IF ( (cn_api.chk_miss_null_num_para
1869 (p_num_para => p_payrun_rec.pay_group_id,
1870 p_obj_name =>
1871 cn_api.get_lkup_meaning('PAY_GROUP_NAME', 'PAY_RUN_VALIDATION_TYPE'),
1872 p_loading_status => x_loading_status,
1873 x_loading_status => x_loading_status)) = FND_API.G_TRUE )
1874 THEN
1875 RAISE FND_API.G_EXC_ERROR ;
1876 END IF;
1877
1878 IF ( (cn_api.chk_miss_null_num_para
1879 (p_num_para => p_payrun_rec.pay_period_id,
1880 p_obj_name =>
1881 cn_api.get_lkup_meaning('PAY_PERIOD', 'PAY_RUN_VALIDATION_TYPE'),
1882 p_loading_status => x_loading_status,
1883 x_loading_status => x_loading_status)) = FND_API.G_TRUE )
1884 THEN
1885 RAISE FND_API.G_EXC_ERROR ;
1886 END IF;
1887
1888 IF ( (cn_api.chk_miss_null_date_para
1889 (p_date_para => p_payrun_rec.pay_date,
1890 p_obj_name =>
1891 cn_api.get_lkup_meaning('PAY_DATE', 'PAY_RUN_VALIDATION_TYPE'),
1892 p_loading_status => x_loading_status,
1893 x_loading_status => x_loading_status)) = FND_API.G_TRUE )
1894 THEN
1895 RAISE FND_API.G_EXC_ERROR ;
1896 END IF;
1897
1898
1899 -- The following validations are performed by this API
1900 -- pay date should be on or after the end date of the pay period
1901 -- status should be either null or UNPAID
1902 -- name should be unique
1903 -- check if from credit type id is valid
1904 -- validate if pay group exists
1905 -- pay_period must exist in the specified pay_group
1906 -- New payrun can only be created for a prd that is after the prd last paid
1907 -- New payrun can be created only if no unpaid exists
1908 -- Mandatory parameters payrun id, name, pay group id, pay period id, pay date
1909
1910
1911 -- Check if unpaid payruns exist for the current pay group
1912
1913 IF check_unpaid_payrun(
1914 p_pay_group_id => p_payrun_rec.pay_group_id,
1915 -- p_org_id => p_payrun_rec.org_id,
1916 p_loading_status => x_loading_status,
1917 x_loading_status => x_loading_status
1918 ) = fnd_api.g_true
1919 THEN
1920 RAISE fnd_api.g_exc_error;
1921 END IF;
1922
1923
1924 -- Check if newer payruns exist for the current pay group
1925
1926 IF chk_last_paid_prd(
1927 p_pay_group_id => p_payrun_rec.pay_group_id,
1928 p_org_id => p_payrun_rec.org_id,
1929 p_pay_period_id => p_payrun_rec.pay_period_id,
1930 p_loading_status => x_loading_status,
1931 x_loading_status => x_loading_status
1932 ) = fnd_api.g_true
1933 THEN
1934 RAISE fnd_api.g_exc_error;
1935 END IF;
1936
1937
1938
1939
1940 -- Validate that pay date is on or after the end date of the pay period
1941
1942 IF validate_pay_date(
1943 p_pay_date => p_payrun_rec.pay_date,
1944 p_start_date => p_payrun_rec.pay_period_start_date,
1945 p_loading_status => x_loading_status,
1946 x_loading_status => x_loading_status
1947 ) = fnd_api.g_true
1948 THEN
1949 RAISE fnd_api.g_exc_error;
1950 END IF;
1951
1952
1953 IF validate_payrun_status(
1954 p_status => p_payrun_rec.status,
1955 p_loading_status => x_loading_status,
1956 x_loading_status => x_loading_status
1957 ) = fnd_api.g_true
1958 THEN
1959 RAISE fnd_api.g_exc_error;
1960 END IF;
1961
1962
1963 IF validate_name_unique(
1964 p_name => p_payrun_rec.name,
1965 p_org_id => p_payrun_rec.org_id,
1966 p_loading_status => x_loading_status,
1967 x_loading_status => x_loading_status
1968 ) = fnd_api.g_true
1969 THEN
1970 RAISE fnd_api.g_exc_error;
1971 END IF;
1972
1973
1974 IF validate_pay_group(
1975 p_pay_group_id => p_payrun_rec.pay_group_id,
1976 p_loading_status => x_loading_status,
1977 x_loading_status => x_loading_status
1978 ) = fnd_api.g_true
1979 THEN
1980 RAISE fnd_api.g_exc_error;
1981 END IF;
1982
1983
1984 IF validate_pay_period(
1985 p_pay_group_id => p_payrun_rec.pay_group_id,
1986 p_pay_period_id => p_payrun_rec.pay_period_id,
1987 p_loading_status => x_loading_status,
1988 x_loading_status => x_loading_status
1989 ) = fnd_api.g_true
1990 THEN
1991 RAISE fnd_api.g_exc_error;
1992 END IF;
1993
1994 IF p_payrun_rec.incentive_type_code NOT IN ('ALL', 'COMMISSION', 'BONUS')
1995 THEN
1996 --Error condition
1997 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
1998 THEN
1999 fnd_message.set_name('CN', 'CN_INVALID_INCENTIVE_TYPE');
2000 fnd_msg_pub.add;
2001 END IF;
2002
2003 x_loading_status := 'CN_INVALID_INCENTIVE_TYPE';
2004 RAISE FND_API.G_EXC_ERROR;
2005 END IF;
2006
2007
2008 l_profile_value := fnd_profile.value('CN_PAY_BY_TRANSACTION');
2009 If (l_profile_value IS NULL OR (l_profile_value <> 'Y' AND l_profile_value <> 'N'))
2010 THEN
2011
2012 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
2013 THEN
2014 fnd_message.set_name('CN', 'CN_PAY_BY_TRANSACTION_PROFILE');
2015 fnd_msg_pub.add;
2016 END IF;
2017 x_loading_status := 'CN_PAY_BY_TRANSACTION_PROFILE';
2018 RAISE FND_API.G_EXC_ERROR;
2019 END IF;
2020
2021 cn_payruns_pkg.insert_record(
2022 x_payrun_id => p_payrun_rec.payrun_id
2023 ,x_name => rtrim(ltrim(p_payrun_rec.name))
2024 ,x_pay_period_id => p_payrun_rec.pay_period_id
2025 ,x_incentive_type_code=> p_payrun_rec.incentive_type_code
2026 ,x_pay_group_id => p_payrun_rec.pay_group_id
2027 ,x_pay_date => p_payrun_rec.pay_date
2028 ,x_accounting_period_id=>p_payrun_rec.accounting_period_id
2029 ,x_batch_id =>p_payrun_rec.batch_id
2030 ,x_status =>Nvl(p_payrun_rec.status,'UNPAID')
2031 ,x_Created_By =>g_created_by
2032 ,x_Creation_Date =>g_creation_date
2033 ,x_object_version_number =>1,
2034 x_org_id =>p_payrun_rec.org_id,
2035 x_payrun_mode =>l_profile_value
2036 ) ;
2037
2038
2039 x_loading_status := 'CN_INSERTED';
2040
2041 -- End of API body.
2042 fnd_message.set_name('CN', 'CN_PMT_CRE_NOTE');
2043 fnd_message.set_token('PMTBATCH_NAME', rtrim(ltrim(p_payrun_rec.name)));
2044 l_note_msg := fnd_message.get;
2045
2046 jtf_notes_pub.create_note
2047 (p_api_version => 1.0,
2048 x_return_status => x_return_status,
2049 x_msg_count => x_msg_count,
2050 x_msg_data => x_msg_data,
2051 p_source_object_id => p_payrun_rec.payrun_id,
2052 p_source_object_code => 'CN_PAYRUNS',
2053 p_notes => l_note_msg,
2054 p_notes_detail => l_note_msg,
2055 p_note_type => 'CN_SYSGEN', -- for system generated
2056 x_jtf_note_id => l_note_id -- returned
2057 );
2058
2059 cn_payment_security_pvt.pmt_raise_event(
2060 p_type => 'PAYRUN',
2061 p_event_name => 'create',
2062 p_payrun_id => p_payrun_rec.payrun_id ) ;
2063
2064 -- Standard check of p_commit.
2065 IF FND_API.To_Boolean( p_commit ) THEN
2066 COMMIT WORK;
2067 END IF;
2068
2069 --
2070 -- Standard call to get message count and if count is 1, get message info.
2071 --
2072
2073 FND_MSG_PUB.Count_And_Get
2074 (
2075 p_count => x_msg_count ,
2076 p_data => x_msg_data ,
2077 p_encoded => FND_API.G_FALSE
2078 );
2079
2080 EXCEPTION
2081 WHEN FND_API.G_EXC_ERROR THEN
2082 ROLLBACK TO Create_Payrun;
2083 x_return_status := FND_API.G_RET_STS_ERROR ;
2084 FND_MSG_PUB.Count_And_Get
2085 (
2086 p_count => x_msg_count ,
2087 p_data => x_msg_data ,
2088 p_encoded => FND_API.G_FALSE
2089 );
2090 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2091 ROLLBACK TO Create_Payrun;
2092 x_loading_status := 'UNEXPECTED_ERR';
2093 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2094 FND_MSG_PUB.Count_And_Get
2095 (
2096 p_count => x_msg_count ,
2097 p_data => x_msg_data ,
2098 p_encoded => FND_API.G_FALSE
2099 );
2100 WHEN OTHERS THEN
2101 ROLLBACK TO Create_Payrun;
2102 x_loading_status := 'UNEXPECTED_ERR';
2103 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2104 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2105 THEN
2106 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME ,l_api_name );
2107 END IF;
2108 FND_MSG_PUB.Count_And_Get
2109 (
2110 p_count => x_msg_count ,
2111 p_data => x_msg_data ,
2112 p_encoded => FND_API.G_FALSE
2113 );
2114 END Create_Payrun;
2115 -- ===========================================================================
2116 -- Procedure : Update Payrun
2117 -- Description : This is a public procedure to update payruns
2118 -- Called during Refresh/Freeze/Unfreeze payruns
2119 -- ===========================================================================
2120
2121 PROCEDURE Update_Payrun
2122 ( p_api_version IN NUMBER,
2123 p_init_msg_list IN VARCHAR2,
2124 p_commit IN VARCHAR2,
2125 p_validation_level IN NUMBER,
2126 x_return_status OUT NOCOPY VARCHAR2,
2127 x_msg_count OUT NOCOPY NUMBER,
2128 x_msg_data OUT NOCOPY VARCHAR2,
2129 p_payrun_id IN cn_payruns.payrun_id%TYPE,
2130 p_x_obj_ver_number IN OUT NOCOPY cn_payruns.object_version_number%TYPE,
2131 p_action IN VARCHAR2,
2132 x_status OUT NOCOPY VARCHAR2,
2133 x_loading_status OUT NOCOPY VARCHAR2
2134 )
2135 IS
2136
2137 l_api_name CONSTANT VARCHAR2(30) := 'Update_Payrun';
2138 l_api_version CONSTANT NUMBER := 1.0;
2139
2140 G_LAST_UPDATE_DATE DATE := sysdate;
2141 G_LAST_UPDATED_BY NUMBER := fnd_global.user_id;
2142 G_CREATION_DATE DATE := sysdate;
2143 G_CREATED_BY NUMBER := fnd_global.user_id;
2144 G_LAST_UPDATE_LOGIN NUMBER := fnd_global.login_id;
2145
2146 l_status cn_payruns.status%TYPE;
2147 l_status_meaning cn_payruns.status%TYPE;
2148 l_payrun_id NUMBER;
2149 l_note_msg VARCHAR2(240);
2150 l_note_id NUMBER;
2151
2152
2153
2154
2155 CURSOR get_old_record IS
2156 SELECT status, payrun_id,name,object_version_number,
2157 cn_api.get_lkup_meaning(cn_payruns.status,'PAYRUN_STATUS') statusmeaning
2158 FROM cn_payruns
2159 WHERE payrun_id = p_payrun_id;
2160 l_old_record get_old_record%ROWTYPE;
2161
2162 CURSOR get_wksht_csr IS
2163 SELECT payment_worksheet_id
2164 FROM cn_payment_worksheets
2165 WHERE payrun_id = p_payrun_id AND worksheet_status = 'UNPAID'
2166 AND quota_id IS NULL;
2167
2168 l_has_access BOOLEAN;
2169
2170 BEGIN
2171
2172 -- Standard Start of API savepoint
2173 SAVEPOINT Update_Payrun;
2174
2175 -- Standard call to check for call compatibility.
2176 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
2177 p_api_version ,
2178 l_api_name ,
2179 G_PKG_NAME )
2180 THEN
2181 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2182 END IF;
2183
2184 -- Initialize message list if p_init_msg_list is set to TRUE.
2185 IF FND_API.to_Boolean( p_init_msg_list ) THEN
2186 FND_MSG_PUB.initialize;
2187 END IF;
2188
2189 -- Initialize API return status to success
2190 x_return_status := FND_API.G_RET_STS_SUCCESS;
2191 x_loading_status := 'CN_UPDATED';
2192
2193 -- API Body
2194 OPEN get_old_record;
2195 FETCH get_old_record INTO l_old_record;
2196 close get_old_record;
2197
2198 l_status_meaning := l_old_record.statusmeaning;
2199 --This part is added for OA.
2200 IF l_old_record.object_version_number <> p_x_obj_ver_number
2201 THEN
2202 IF (fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error))
2203 THEN
2204 fnd_message.set_name ('CN', 'CN_RECORD_CHANGED');
2205 fnd_msg_pub.ADD;
2206 END IF;
2207
2208 RAISE fnd_api.g_exc_error;
2209 END IF;
2210
2211
2212 -- Step 1
2213 -- Procedure to check if the payrun action is valid.
2214 CN_PAYMENT_SECURITY_PVT.Payrun_Action
2215 ( p_api_version => 1.0,
2216 p_init_msg_list => fnd_api.g_true,
2217 p_validation_level => fnd_api.g_valid_level_full,
2218 x_return_status => x_return_status,
2219 x_msg_count => x_msg_count,
2220 x_msg_data => x_msg_data,
2221 p_payrun_id => p_payrun_id,
2222 p_action => p_action ,
2223 p_do_audit => fnd_api.g_false
2224 );
2225
2226 IF x_return_status <> FND_API.g_ret_sts_success THEN
2227 RAISE FND_API.G_EXC_ERROR;
2228 END IF;
2229
2230 -- Step 2
2231 -- Check Action Type and perform action accordingly
2232 -- Check for FREEZE/UNFREEZE is removed since, the status is updated
2233 -- in CN_PAYMENT_SECURITY_PVT.Payrun_Action
2234
2235 IF p_action = 'REFRESH' THEN
2236
2237
2238 --Bug fix 2502453
2239
2240 Refresh_Payrun
2241 ( p_api_version => 1.0,
2242 p_init_msg_list => fnd_api.g_true,
2243 p_commit => fnd_api.g_false,
2244 p_validation_level => fnd_api.g_valid_level_full,
2245 p_payrun_id => p_payrun_id,
2246 x_return_status => x_return_status,
2247 x_msg_count => x_msg_count,
2248 x_msg_data => x_msg_data,
2249 x_status => x_status,
2250 x_loading_status => x_loading_status
2251 );
2252
2253 IF x_return_status <> FND_API.g_ret_sts_success THEN
2254 RAISE FND_API.G_EXC_ERROR;
2255 END IF;
2256
2257
2258 ELSIF p_action in ('FREEZE','UNFREEZE') THEN
2259 IF (p_action = 'FREEZE') THEN
2260
2261 FOR l_wksht_rec IN get_wksht_csr loop
2262 -- save image
2263 cn_payment_worksheet_pvt.set_ced_and_bb
2264 ( p_api_version => 1.0,
2265 x_return_status => x_return_status,
2266 x_msg_count => x_msg_count,
2267 x_msg_data => x_msg_data,
2268 p_worksheet_id => l_wksht_rec.payment_worksheet_id
2269 );
2270 IF x_return_status <> FND_API.g_ret_sts_success THEN
2271 RAISE FND_API.G_EXC_ERROR;
2272 END IF;
2273 END LOOP;
2274 END IF;
2275
2276 -- Bug 3391231: ACHUNG 01/21/04
2277 -- move the audit after set_ced_and_bb.Otherwise
2278 -- record won't be created since set_ced_and_bb need payrun
2279 -- status = 'UNPAID'
2280 CN_PAYMENT_SECURITY_PVT.Payrun_Audit
2281 (p_payrun_id => p_payrun_id,
2282 p_action => p_action,
2283 x_return_status => x_return_status,
2284 x_msg_count => x_msg_count,
2285 x_msg_data => x_msg_data);
2286
2287 IF x_return_status <> FND_API.g_ret_sts_success THEN
2288 RAISE FND_API.G_EXC_ERROR;
2289 END IF;
2290
2291 END IF;
2292
2293 OPEN get_old_record;
2294 FETCH get_old_record INTO l_old_record;
2295 close get_old_record;
2296
2297 IF p_action <> 'REFRESH' THEN
2298 fnd_message.set_name('CN', 'CN_PMT_UPD_NOTE');
2299 fnd_message.set_token('NEW', l_old_record.statusmeaning);
2300 fnd_message.set_token('OLD', l_status_meaning);
2301 l_note_msg := fnd_message.get;
2302 ELSE
2303 fnd_message.set_name('CN', 'CN_PMT_REF_NOTE');
2304 fnd_message.set_token('PMTBATCH_NAME', l_old_record.name);
2305 l_note_msg := fnd_message.get;
2306 END IF;
2307 jtf_notes_pub.create_note
2308 (p_api_version => 1.0,
2309 x_return_status => x_return_status,
2310 x_msg_count => x_msg_count,
2311 x_msg_data => x_msg_data,
2312 p_source_object_id => p_payrun_id,
2313 p_source_object_code => 'CN_PAYRUNS',
2314 p_notes => l_note_msg,
2315 p_notes_detail => l_note_msg,
2316 p_note_type => 'CN_SYSGEN', -- for system generated
2317 x_jtf_note_id => l_note_id -- returned
2318 );
2319
2320 -- End of API body.
2321 -- Standard check of p_commit.
2322
2323 IF FND_API.To_Boolean( p_commit ) THEN
2324 COMMIT WORK;
2325 END IF;
2326
2327 -- Standard call to get message count and if count is 1, get message info.
2328 FND_MSG_PUB.Count_And_Get
2329 (
2330 p_count => x_msg_count ,
2331 p_data => x_msg_data ,
2332 p_encoded => FND_API.G_FALSE
2333 );
2334 EXCEPTION
2335 WHEN FND_API.G_EXC_ERROR THEN
2336 ROLLBACK TO Update_Payrun;
2337 x_return_status := FND_API.G_RET_STS_ERROR ;
2338 FND_MSG_PUB.Count_And_Get
2339 (
2340 p_count => x_msg_count ,
2341 p_data => x_msg_data ,
2342 p_encoded => FND_API.G_FALSE
2343 );
2344 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2345 ROLLBACK TO Update_Payrun;
2346 x_loading_status := 'UNEXPECTED_ERR';
2347 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2348 FND_MSG_PUB.Count_And_Get
2349 (
2350 p_count => x_msg_count ,
2351 p_data => x_msg_data ,
2352 p_encoded => FND_API.G_FALSE
2353 );
2354 WHEN OTHERS THEN
2355 ROLLBACK TO Update_Payrun;
2356 x_loading_status := 'UNEXPECTED_ERR';
2357 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2358 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2359 THEN
2360 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME ,l_api_name );
2361 END IF;
2362 FND_MSG_PUB.Count_And_Get
2363 (
2364 p_count => x_msg_count ,
2365 p_data => x_msg_data ,
2366 p_encoded => FND_API.G_FALSE
2367 );
2368
2369 END update_payrun;
2370 -- ===========================================================================
2371 -- Procedure Name : Delete Payrun
2372 --
2373 -- ===========================================================================
2374
2375 PROCEDURE Delete_Payrun
2376 ( p_api_version IN NUMBER,
2377 p_init_msg_list IN VARCHAR2,
2378 p_commit IN VARCHAR2,
2379 p_validation_level IN NUMBER,
2380 x_return_status OUT NOCOPY VARCHAR2,
2381 x_msg_count OUT NOCOPY NUMBER,
2382 x_msg_data OUT NOCOPY VARCHAR2,
2383 p_payrun_id IN cn_payruns.payrun_id%TYPE,
2384 p_validation_only IN VARCHAR2,
2385 x_status OUT NOCOPY VARCHAR2,
2386 x_loading_status OUT NOCOPY VARCHAR2
2387 ) IS
2388
2389 l_api_name CONSTANT VARCHAR2(30)
2390 := 'Delete_Payrun';
2391 l_api_version CONSTANT NUMBER := 1.0;
2392
2393
2394 CURSOR get_old_record IS
2395 SELECT status, payrun_id,name,org_id
2396 FROM cn_payruns
2397 WHERE payrun_id = p_payrun_id;
2398 l_old_record get_old_record%ROWTYPE;
2399
2400 CURSOR get_conc_request(l_pay_run_name varchar2) IS
2401 select distinct request_id
2402 from fnd_concurrent_requests
2403 where oracle_id = 900 and request_date >= (sysdate -1)
2404 and status_code not in ('C', 'E','D') and argument_text = l_pay_run_name;
2405 l_request get_conc_request%ROWTYPE;
2406
2407 CURSOR get_worksheets IS
2408 SELECT payment_worksheet_id,object_version_number
2409 FROM cn_payment_worksheets
2410 WHERE payrun_id = p_payrun_id
2411 AND quota_id is null;
2412
2413 G_LAST_UPDATE_DATE DATE := sysdate;
2414 G_LAST_UPDATED_BY NUMBER := fnd_global.user_id;
2415 G_CREATION_DATE DATE := sysdate;
2416 G_CREATED_BY NUMBER := fnd_global.user_id;
2417 G_LAST_UPDATE_LOGIN NUMBER := fnd_global.login_id;
2418 g_credit_type_id CONSTANT NUMBER := -1000;
2419
2420 l_has_access BOOLEAN;
2421 l_note_msg VARCHAR2(240);
2422 l_note_id NUMBER;
2423 l_org_id NUMBER := -999;
2424 l_pmtbatch_name VARCHAR2(80);
2425 l_request_id NUMBER := -999;
2426
2427 BEGIN
2428 --
2429 -- Standard Start of API savepoint
2430 --
2431 SAVEPOINT Delete_Payrun;
2432 --
2433 -- Standard call to check for call compatibility.
2434 --
2435 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
2436 p_api_version ,
2437 l_api_name ,
2438 G_PKG_NAME )
2439 THEN
2440 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2441 END IF;
2442 --
2443 -- Initialize message list if p_init_msg_list is set to TRUE.
2444 --
2445 IF FND_API.to_Boolean( p_init_msg_list ) THEN
2446 FND_MSG_PUB.initialize;
2447 END IF;
2448 --
2449 -- Initialize API return status to success
2450 --
2451 x_return_status := FND_API.G_RET_STS_SUCCESS;
2452 x_loading_status := 'CN_DELETED';
2453 --
2454 -- API Body
2455 --
2456
2457
2458 OPEN get_old_record;
2459 FETCH get_old_record INTO l_old_record;
2460 IF get_old_record%rowcount = 0 THEN
2461
2462 --Error condition
2463 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
2464 THEN
2465 fnd_message.set_name('CN', 'CN_PAYRUN_DOES_NOT_EXIST');
2466 fnd_msg_pub.add;
2467 END IF;
2468
2469 x_loading_status := 'CN_PAYRUN_DOES_NOT_EXIST';
2470 RAISE FND_API.G_EXC_ERROR;
2471
2472 END IF;
2473 CLOSE get_old_record;
2474
2475 l_pmtbatch_name := l_old_record.NAME;
2476 l_org_id:=l_old_record.org_id;
2477
2478 OPEN get_conc_request(l_pmtbatch_name);
2479 FETCH get_conc_request INTO l_request;
2480 IF get_conc_request%rowcount > 0 THEN
2481 l_request_id := l_request.request_id;
2482
2483 --Error condition
2484 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
2485 THEN
2486 fnd_message.set_name('CN', 'CN_CONC_CREATE_WKSHEET_PENDING');
2487 fnd_message.set_token ('REQUESTID', l_request_id);
2488 fnd_msg_pub.add;
2489 END IF;
2490
2491 x_loading_status := 'CN_CONC_CREATE_WKSHEET_PENDING';
2492 RAISE FND_API.G_EXC_ERROR;
2493
2494 END IF;
2495 CLOSE get_conc_request;
2496
2497 IF l_old_record.status IN ('PAID', 'RETURNED_FUNDS', 'PAID_WITH_RETURNS')
2498 THEN
2499 --Error condition
2500 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
2501 THEN
2502 fnd_message.set_name('CN', 'CN_PAYRUN_PAID');
2503 fnd_msg_pub.add;
2504 END IF;
2505
2506 x_loading_status := 'CN_PAYRUN_PAID';
2507 RAISE FND_API.G_EXC_ERROR;
2508
2509 END IF;
2510
2511 -- Section included by Sundar Venkat on 07 Mar 2002
2512 -- Procedure to check if the payrun action is valid.
2513 CN_PAYMENT_SECURITY_PVT.Payrun_Action
2514 ( p_api_version => 1.0,
2515 p_init_msg_list => fnd_api.g_true,
2516 p_validation_level => fnd_api.g_valid_level_full,
2517 x_return_status => x_return_status,
2518 x_msg_count => x_msg_count,
2519 x_msg_data => x_msg_data,
2520 p_payrun_id => p_payrun_id,
2521 p_action => 'REMOVE'
2522 );
2523
2524 IF x_return_status <> FND_API.g_ret_sts_success THEN
2525 RAISE FND_API.G_EXC_ERROR;
2526 END IF;
2527
2528
2529
2530 FOR worksheets in get_worksheets
2531 LOOP
2532
2533 cn_payment_worksheet_pvt.delete_worksheet(
2534 p_api_version => p_api_version,
2535 p_init_msg_list => p_init_msg_list,
2536 p_commit => fnd_api.g_false,
2537 p_validation_level => p_validation_level,
2538 x_return_status => x_return_status,
2539 x_msg_count => x_msg_count,
2540 x_msg_data => x_msg_data,
2541 p_worksheet_id => worksheets.payment_worksheet_id,
2542 p_validation_only => p_validation_only, --R12
2543 x_status => x_status,
2544 x_loading_status => x_loading_status,
2545 p_ovn =>worksheets.object_version_number);
2546
2547 IF x_return_status <> FND_API.g_ret_sts_success THEN
2548 RAISE FND_API.G_EXC_ERROR;
2549 END IF;
2550
2551 END LOOP;
2552
2553 IF p_validation_only <> 'Y' THEN
2554 cn_payruns_pkg.delete_record
2555 (x_payrun_id => p_payrun_id) ;
2556
2557 x_loading_status := 'CN_DELETED';
2558
2559 -- End of API body.
2560 -- Standard check of p_commit.
2561 IF (l_org_id <> -999) THEN
2562 fnd_message.set_name ('CN', 'CN_PMT_DEL_NOTE');
2563 fnd_message.set_token ('PMTBATCH_NAME', l_pmtbatch_name);
2564 l_note_msg := fnd_message.get;
2565 jtf_notes_pub.create_note
2566 (p_api_version => 1.0,
2567 x_return_status => x_return_status,
2568 x_msg_count => x_msg_count,
2569 x_msg_data => x_msg_data,
2570 p_source_object_id => l_org_id,
2571 p_source_object_code => 'CN_REPOSITORIES',
2572 p_notes => l_note_msg,
2573 p_notes_detail => l_note_msg,
2574 p_note_type => 'CN_SYSGEN', -- for system generated
2575 x_jtf_note_id => l_note_id -- returned
2576 );
2577 END IF;
2578 END IF;
2579
2580 IF FND_API.To_Boolean( p_commit ) THEN
2581 COMMIT WORK;
2582 END IF;
2583
2584 --
2585 -- Standard call to get message count and if count is 1, get message info.
2586 --
2587 FND_MSG_PUB.Count_And_Get
2588 (
2589 p_count => x_msg_count ,
2590 p_data => x_msg_data ,
2591 p_encoded => FND_API.G_FALSE
2592 );
2593 EXCEPTION
2594 WHEN FND_API.G_EXC_ERROR THEN
2595 ROLLBACK TO Delete_Payrun;
2596 x_return_status := FND_API.G_RET_STS_ERROR ;
2597 FND_MSG_PUB.Count_And_Get
2598 (
2599 p_count => x_msg_count ,
2600 p_data => x_msg_data ,
2601 p_encoded => FND_API.G_FALSE
2602 );
2603 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2604 ROLLBACK TO Delete_Payrun;
2605 x_loading_status := 'UNEXPECTED_ERR';
2606 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2607 FND_MSG_PUB.Count_And_Get
2608 (
2609 p_count => x_msg_count ,
2610 p_data => x_msg_data ,
2611 p_encoded => FND_API.G_FALSE
2612 );
2613 WHEN OTHERS THEN
2614 ROLLBACK TO Delete_Payrun;
2615 x_loading_status := 'UNEXPECTED_ERR';
2616 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2617 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2618 THEN
2619 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME ,l_api_name );
2620 END IF;
2621 FND_MSG_PUB.Count_And_Get
2622 (
2623 p_count => x_msg_count ,
2624 p_data => x_msg_data ,
2625 p_encoded => FND_API.G_FALSE
2626 );
2627
2628 END Delete_Payrun;
2629
2630 -- ===========================================================================
2631 -- Procedure : Pay_payrun_Approve_Wksht
2632 -- Description: Approve all whskt before pay a payrun
2633 -- ===========================================================================
2634
2635 PROCEDURE Pay_Payrun_Approve_Wksht
2636 (p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
2637 x_return_status OUT NOCOPY VARCHAR2,
2638 x_msg_count OUT NOCOPY NUMBER,
2639 x_msg_data OUT NOCOPY VARCHAR2,
2640 p_payrun_id IN cn_payruns.payrun_id%TYPE )
2641 IS
2642
2643 l_api_name CONSTANT VARCHAR2(30) := 'Pay_Payrun_Approve_Wksht';
2644 l_api_version CONSTANT NUMBER := 1.0;
2645
2646 CURSOR get_unpaid_wksht IS
2647 SELECT payment_worksheet_id, salesrep_id
2648 FROM cn_payment_worksheets
2649 WHERE payrun_id = p_payrun_id AND worksheet_status = 'UNPAID'
2650 AND quota_id IS NULL ;
2651
2652 CURSOR get_unapprove_wksht IS
2653 SELECT payment_worksheet_id, salesrep_id
2654 FROM cn_payment_worksheets
2655 WHERE payrun_id = p_payrun_id AND worksheet_status <> 'APPROVED'
2656 AND quota_id IS NULL;
2657
2658 BEGIN
2659 --
2660 -- Standard Start of API savepoint
2661 --
2662 SAVEPOINT Pay_Payrun_Approve_Wksht;
2663 --
2664 -- Initialize API return status to success
2665 --
2666 x_return_status := FND_API.G_RET_STS_SUCCESS;
2667 --
2668 -- API body
2669 --
2670
2671 -- Lock any unpaid worksheets so cn_worksheet_qg_dtls and wksht bb cols
2672 -- will get populate
2673 FOR unpaid_rec IN get_unpaid_wksht LOOP
2674 -- save current image if LOCK worksheet
2675 cn_payment_worksheet_pvt.set_ced_and_bb ( p_api_version => 1.0,
2676 x_return_status => x_return_status,
2677 x_msg_count => x_msg_count,
2678 x_msg_data => x_msg_data,
2679 p_worksheet_id => unpaid_rec.payment_worksheet_id
2680 );
2681
2682 IF x_return_status <> FND_API.g_ret_sts_success THEN
2683 RAISE FND_API.G_EXC_ERROR;
2684 END IF;
2685
2686 -- validate lock and audit worksheet
2687 cn_payment_security_pvt.worksheet_action(p_api_version => 1.0,
2688 p_init_msg_list => fnd_api.g_false,
2689 p_commit => 'F',
2690 p_validation_level => p_validation_level,
2691 x_return_status => x_return_status,
2692 x_msg_count => x_msg_count,
2693 x_msg_data => x_msg_data,
2694 p_worksheet_id => unpaid_rec.payment_worksheet_id,
2695 p_action => 'LOCK',
2696 p_do_audit => fnd_api.g_true
2697 );
2698
2699 IF x_return_status <> FND_API.g_ret_sts_success THEN
2700 RAISE FND_API.G_EXC_ERROR;
2701 END IF;
2702 END LOOP;
2703
2704 -- Approve all wksht
2705 FOR unapprove_rec IN get_unapprove_wksht LOOP
2706 -- validate
2707 cn_payment_security_pvt.worksheet_action(p_api_version => 1.0,
2708 p_init_msg_list => fnd_api.g_false,
2709 p_commit => 'F',
2710 p_validation_level => p_validation_level,
2711 x_return_status => x_return_status,
2712 x_msg_count => x_msg_count,
2713 x_msg_data => x_msg_data,
2714 p_worksheet_id => unapprove_rec.payment_worksheet_id,
2715 p_action => 'APPROVE',
2716 p_do_audit => fnd_api.g_false
2717 );
2718
2719 -- set wksht audit
2720 CN_PAYMENT_SECURITY_PVT.Worksheet_Audit
2721 (p_worksheet_id => unapprove_rec.payment_worksheet_id,
2722 p_payrun_id => p_payrun_id,
2723 p_salesrep_id => unapprove_rec.salesrep_id,
2724 p_action => 'APPROVE',
2725 p_do_approval_flow => FND_API.G_FALSE,
2726 x_return_status => x_return_status,
2727 x_msg_count => x_msg_count,
2728 x_msg_data => x_msg_data);
2729
2730 IF x_return_status <> FND_API.g_ret_sts_success THEN
2731 RAISE FND_API.G_EXC_ERROR;
2732 END IF;
2733 END LOOP;
2734
2735 -- End of API body
2736
2737 --
2738 -- Standard call to get message count and if count is 1, get message info.
2739 --
2740
2741 FND_MSG_PUB.Count_And_Get
2742 (p_count => x_msg_count ,
2743 p_data => x_msg_data ,
2744 p_encoded => FND_API.G_FALSE);
2745
2746 EXCEPTION
2747 WHEN FND_API.G_EXC_ERROR THEN
2748 ROLLBACK TO Pay_Payrun_Approve_Wksht;
2749 x_return_status := FND_API.G_RET_STS_ERROR ;
2750 FND_MSG_PUB.Count_And_Get
2751 (p_count => x_msg_count ,
2752 p_data => x_msg_data ,
2753 p_encoded => FND_API.G_FALSE
2754 );
2755 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2756 ROLLBACK TO Pay_Payrun_Approve_Wksht;
2757 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2758 FND_MSG_PUB.Count_And_Get
2759 (
2760 p_count => x_msg_count ,
2761 p_data => x_msg_data ,
2762 p_encoded => FND_API.G_FALSE
2763 );
2764 WHEN OTHERS THEN
2765 ROLLBACK TO Pay_Payrun_Approve_Wksht;
2766 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2767 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2768 THEN
2769 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME ,l_api_name );
2770 END IF;
2771 FND_MSG_PUB.Count_And_Get
2772 (
2773 p_count => x_msg_count ,
2774 p_data => x_msg_data ,
2775 p_encoded => FND_API.G_FALSE
2776 );
2777 END Pay_Payrun_Approve_Wksht;
2778
2779 -- ===========================================================================
2780 -- Procedure : Pay_payrun
2781 -- Description: To pay a payrun
2782 -- Update the subledger
2783 -- Modified logic to achieve the Payment Plan Amount.
2784 -- Kumar Sivasankaran 11/12/2001
2785 -- Matt Blum 05/02/2002
2786 -- ===========================================================================
2787
2788 PROCEDURE Pay_Payrun
2789 ( p_api_version IN NUMBER,
2790 p_init_msg_list IN VARCHAR2,
2791 p_commit IN VARCHAR2,
2792 p_validation_level IN NUMBER,
2793 x_return_status OUT NOCOPY VARCHAR2,
2794 x_msg_count OUT NOCOPY NUMBER,
2795 x_msg_data OUT NOCOPY VARCHAR2,
2796 p_payrun_id IN cn_payruns.payrun_id%TYPE,
2797 p_x_obj_ver_number IN OUT NOCOPY cn_payruns.object_version_number%TYPE,
2798 x_status OUT NOCOPY VARCHAR2,
2799 x_loading_status OUT NOCOPY VARCHAR2
2800 ) IS
2801
2802 l_api_name CONSTANT VARCHAR2(30) := 'Pay_Payrun';
2803 l_api_version CONSTANT NUMBER := 1.0;
2804
2805 -- for balances
2806 l_ctrl_pmt_amount NUMBER := 0;
2807 -- l_hold_pmt NUMBER := 0;
2808 l_pmt_amount_rec NUMBER := 0;
2809 l_pmt_amount_nrec NUMBER := 0;
2810 l_recovery NUMBER := 0;
2811 l_tot_recovery NUMBER := 0;
2812 l_waive_recovery NUMBER := 0;
2813 l_pmt_amount_calc NUMBER := 0;
2814 l_tot_pmt_amount_calc NUMBER := 0;
2815 l_adj_amount NUMBER := 0;
2816 l_rec_amt NUMBER := 0;
2817 l_quota_id NUMBER;
2818
2819 -- general stuff
2820 l_posting_batch_id NUMBER;
2821 l_batch_name VARCHAR2(30);
2822 l_credit_type_id NUMBER := -1000;
2823
2824 -- for payroll integration
2825 l_element_type_id NUMBER;
2826 l_payables_flag cn_repositories.payables_flag%TYPE;
2827 l_payroll_flag cn_repositories.payroll_flag%TYPE;
2828 l_payables_ccid_level cn_repositories.payables_ccid_level%TYPE;
2829
2830 l_pmt_trx_rec cn_pmt_trans_pkg.pmt_trans_rec_type;
2831 l_batch_rec cn_prepostbatches.posting_batch_rec_type;
2832
2833 -- for API calls
2834 l_loading_status VARCHAR2(30);
2835 l_rowid VARCHAR2(30);
2836
2837 l_srp_prd_rec cn_srp_periods_pvt.delta_srp_period_rec_type
2838 := cn_srp_periods_pvt.g_miss_delta_srp_period_rec;
2839 l_note_msg VARCHAR2(240);
2840 l_note_id NUMBER;
2841 l_status_meaning cn_payruns.status%TYPE;
2842
2843 CURSOR get_payrun IS
2844 SELECT pay_period_id, pay_date, accounting_period_id, org_id
2845 ,name, cn_api.get_lkup_meaning(cn_payruns.status,'PAYRUN_STATUS') statusmeaning
2846 FROM cn_payruns
2847 WHERE payrun_id = p_payrun_id;
2848
2849 l_payrun_rec get_payrun%ROWTYPE;
2850
2851 CURSOR get_salesreps_in_payrun(p_org_id cn_payruns.org_id%TYPE) IS
2852 SELECT distinct salesrep_id
2853 FROM cn_payment_worksheets
2854 WHERE payrun_id = p_payrun_id
2855 --R12
2856 AND org_id = p_org_id;
2857
2858 CURSOR get_worksheet_data_for_pe (p_salesrep_id NUMBER, p_org_id cn_payruns.org_id%TYPE)IS
2859 SELECT salesrep_id, quota_id, credit_type_id,
2860 pmt_amount_calc, pmt_amount_adj, pmt_amount_adj_rec,
2861 pmt_amount_adj_nrec, pmt_amount_recovery
2862 FROM cn_payment_worksheets
2863 WHERE payrun_id = p_payrun_id
2864 AND salesrep_id = p_salesrep_id
2865 AND quota_id is not null;
2866
2867
2868
2869 CURSOR get_srp_period(p_salesrep_id NUMBER,
2870 p_period_id NUMBER,
2871 p_quota_id NUMBER,
2872 p_org_id cn_payruns.org_id%TYPE) IS
2873 SELECT srp_period_id
2874 FROM cn_srp_periods
2875 WHERE salesrep_id = p_salesrep_id
2876 AND credit_type_id = l_credit_type_id
2877 AND period_id = p_period_id
2878 AND (quota_id = p_quota_id
2879 OR
2880 (p_quota_id IS NULL AND quota_id IS NULL))
2881 --R12
2882 AND org_id = p_org_id
2883 AND ROWNUM < 2 ; -- Bug 2819874
2884
2885 -- for payroll integration
2886 CURSOR get_apps(p_org_id cn_payruns.org_id%TYPE) IS
2887 SELECT payables_flag, payroll_flag, payables_ccid_level
2888 FROM cn_repositories
2889 --R12
2890 WHERE org_id = p_org_id;
2891
2892
2893
2894 -- for wkshts with null quota ID - get from transactions
2895 CURSOR get_control_pmt(p_salesrep_id NUMBER,
2896 p_quota_id NUMBER,
2897 p_org_id cn_payruns.org_id%TYPE) IS
2898 SELECT nvl(sum(nvl(payment_amount,0) - nvl(amount,0)),0) control_payment
2899 FROM cn_payment_transactions
2900 WHERE payrun_id = p_payrun_id
2901 AND credited_salesrep_id = p_salesrep_id
2902 AND credit_type_id = l_credit_type_id
2903 AND (quota_id = p_quota_id
2904 OR
2905 (p_quota_id IS NULL AND quota_id IS NULL))
2906 AND incentive_type_code <> 'PMTPLN_REC'
2907 AND nvl(hold_flag, 'N') = 'N';
2908
2909 CURSOR get_hold_pmt(p_salesrep_id NUMBER,
2910 p_quota_id NUMBER) IS
2911 SELECT nvl(sum(nvl(amount,0)),0) hold_payment
2912 FROM cn_payment_transactions
2913 WHERE payrun_id = p_payrun_id
2914 AND credited_salesrep_id = p_salesrep_id
2915 AND credit_type_id = l_credit_type_id
2916 AND (quota_id = p_quota_id
2917 OR
2918 (p_quota_id IS NULL AND quota_id IS NULL))
2919 AND incentive_type_code <> 'PMTPLN_REC'
2920 AND nvl(hold_flag, 'N') = 'Y';
2921
2922 -- Bug 2795606 : use amount not pmt_amt since get_cp will handle adj amt
2923
2924 CURSOR get_man_pay_adj(p_salesrep_id NUMBER,
2925 p_quota_id NUMBER,
2926 p_org_id cn_payruns.org_id%TYPE) IS
2927 SELECT nvl(sum(nvl(amount,0)),0) man_pay_adj, recoverable_flag
2928 FROM cn_payment_transactions
2929 WHERE payrun_id = p_payrun_id
2930 AND credited_salesrep_id = p_salesrep_id
2931 AND credit_type_id = l_credit_type_id
2932 AND quota_id = p_quota_id
2933 AND incentive_type_code = 'MANUAL_PAY_ADJ'
2934 GROUP BY recoverable_flag;
2935
2936 CURSOR get_payment_details(p_salesrep_id NUMBER,
2937 p_quota_id NUMBER,
2938 p_org_id cn_payruns.org_id%TYPE) IS
2939 SELECT nvl(pmt_amount_calc,0),
2940 nvl(pmt_amount_adj_rec,0),
2941 nvl(pmt_amount_adj_nrec,0),
2942 -nvl(pmt_amount_recovery,0)
2943 FROM cn_payment_worksheets
2944 WHERE payrun_id = p_payrun_id
2945 AND salesrep_id = p_salesrep_id
2946 AND credit_type_id = l_credit_type_id
2947 AND (quota_id = p_quota_id
2948 OR
2949 (p_quota_id IS NULL AND quota_id IS NULL));
2950
2951 CURSOR get_waive_rec(p_salesrep_id NUMBER,
2952 p_quota_id NUMBER,
2953 p_org_id cn_payruns.org_id%TYPE) IS
2954 SELECT -nvl(sum(nvl(payment_amount,0)),0)
2955 FROM cn_payment_transactions
2956 WHERE payrun_id = p_payrun_id
2957 AND credited_salesrep_id = p_salesrep_id
2958 AND credit_type_id = l_credit_type_id
2959 AND incentive_type_code = 'PMTPLN_REC'
2960 AND nvl(hold_flag, 'N') = 'N'
2961 AND waive_flag = 'Y'
2962 AND (quota_id = p_quota_id
2963 OR
2964 (p_quota_id IS NULL AND quota_id IS NULL))
2965 --R12
2966 AND org_id = p_org_id;
2967
2968 -- get carry over srp_periods record
2969 CURSOR carry_over_srp_period(c_salesrep_id NUMBER,
2970 c_period_id NUMBER,
2971 p_org_id cn_payruns.org_id%TYPE) IS
2972 SELECT sprd.srp_period_id
2973 FROM cn_srp_periods sprd
2974 WHERE
2975 sprd.salesrep_id = c_salesrep_id
2976 AND sprd.period_id = c_period_id
2977 AND sprd.quota_id = -1000
2978 AND sprd.credit_type_id = -1000
2979 --R12
2980 AND org_id = p_org_id;
2981
2982 -- Get sync_accum records. Bug 3151860
2983 CURSOR sync_accum(c_salesrep_id NUMBER,
2984 c_period_id NUMBER,
2985 p_org_id cn_payruns.org_id%TYPE) IS
2986 SELECT DISTINCT sprd.role_id
2987 FROM cn_srp_periods sprd
2988 WHERE
2989 sprd.salesrep_id = c_salesrep_id
2990 AND sprd.period_id = c_period_id
2991 AND sprd.credit_type_id = -1000
2992 --R12
2993 AND org_id = p_org_id;
2994
2995 G_LAST_UPDATE_DATE DATE := sysdate;
2996 G_LAST_UPDATED_BY NUMBER := fnd_global.user_id;
2997 G_CREATION_DATE DATE := sysdate;
2998 G_CREATED_BY NUMBER := fnd_global.user_id;
2999 G_LAST_UPDATE_LOGIN NUMBER := fnd_global.login_id;
3000
3001 l_has_access BOOLEAN;
3002
3003 BEGIN
3004 --
3005 -- Standard Start of API savepoint
3006 --
3007 SAVEPOINT Pay_Payrun;
3008 --
3009 -- Standard call to check for call compatibility.
3010 --
3011 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
3012 p_api_version ,
3013 l_api_name ,
3014 G_PKG_NAME )
3015 THEN
3016 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3017 END IF;
3018 --
3019 -- Initialize message list if p_init_msg_list is set to TRUE.
3020 --
3021 IF FND_API.to_Boolean( p_init_msg_list ) THEN
3022 FND_MSG_PUB.initialize;
3023 END IF;
3024 --
3025 -- Initialize API return status to success
3026 --
3027 x_return_status := FND_API.G_RET_STS_SUCCESS;
3028 x_loading_status := 'CN_UPDATED';
3029
3030
3031
3032 --Validate if payrun is valid
3033 IF ((cn_api.chk_miss_null_num_para
3034 (p_num_para => p_payrun_id,
3035 p_obj_name =>
3036 cn_api.get_lkup_meaning('PAY_RUN_NAME', 'PAY_RUN_VALIDATION_TYPE'),
3037 p_loading_status => x_loading_status,
3038 x_loading_status => x_loading_status)) = FND_API.G_TRUE)
3039 THEN
3040 RAISE FND_API.G_EXC_ERROR ;
3041 END IF;
3042
3043 OPEN get_payrun;
3044 FETCH get_payrun INTO l_payrun_rec;
3045 IF get_payrun%rowcount = 0 THEN
3046 --Error condition
3047 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
3048 THEN
3049 fnd_message.set_name('CN', 'CN_INVALID_PAYRUN');
3050 fnd_msg_pub.add;
3051 END IF;
3052
3053 x_loading_status := 'CN_INVALID_PAYRUN';
3054 CLOSE get_payrun;
3055 RAISE FND_API.G_EXC_ERROR;
3056
3057 END IF;
3058 CLOSE get_payrun;
3059 l_status_meaning := l_payrun_rec.statusmeaning;
3060
3061 -- initialize payrun action
3062 cn_payment_security_pvt.payrun_action
3063 (p_api_version => 1.0,
3064 x_return_status => x_return_status,
3065 x_msg_count => x_msg_count,
3066 x_msg_data => x_msg_data,
3067 p_payrun_id => p_payrun_id,
3068 p_action => 'PAY');
3069 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3070 RAISE FND_API.G_EXC_ERROR;
3071 END IF;
3072
3073 -- Need to auto-approve all wkshts if CN_CHK_WKSHT_STATUS = N
3074 IF nvl(fnd_profile.value('CN_CHK_WKSHT_STATUS'), 'Y') = 'N' THEN
3075 -- Lock and Approve all wkshts
3076 Pay_Payrun_Approve_Wksht
3077 (x_return_status => x_return_status,
3078 x_msg_count => x_msg_count,
3079 x_msg_data => x_msg_data,
3080 p_payrun_id => p_payrun_id);
3081 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
3082 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3083 END IF;
3084 END IF;
3085
3086 -- Check if AP / Payroll integration has been enabled.
3087 OPEN get_apps(l_payrun_rec.org_id);
3088 FETCH get_apps INTO l_payables_flag, l_payroll_flag, l_payables_ccid_level;
3089 CLOSE get_apps;
3090
3091 IF l_payroll_flag = 'Y'
3092 THEN
3093 IF NOT validate_pay(
3094 p_payrun =>p_payrun_id,
3095 p_pay_date =>l_payrun_rec.pay_date,
3096 p_loading_status =>x_loading_status,
3097 x_loading_status =>x_loading_status)
3098 THEN
3099 RAISE fnd_api.g_exc_error;
3100 END IF;
3101 END IF;
3102
3103 -- process hold transactions
3104 UPDATE cn_payment_transactions
3105 SET payrun_id = '',
3106 LAST_UPDATE_DATE = Sysdate,
3107 LAST_UPDATED_BY = G_LAST_UPDATED_BY,
3108 LAST_UPDATE_LOGIN = G_LAST_UPDATE_LOGIN
3109 WHERE payrun_id = p_payrun_id
3110 AND hold_flag = 'Y'
3111 --R12
3112 AND org_id = l_payrun_rec.org_id;
3113
3114 -- set transactions to paid for this payrun
3115 UPDATE cn_payment_transactions
3116 SET paid_flag = 'Y',
3117 LAST_UPDATE_DATE = Sysdate,
3118 LAST_UPDATED_BY = G_LAST_UPDATED_BY,
3119 LAST_UPDATE_LOGIN = G_LAST_UPDATE_LOGIN
3120 WHERE payrun_id = p_payrun_id
3121 --R12
3122 AND org_id = l_payrun_rec.org_id;
3123
3124
3125 -- open a transaction (used to be called posting) batch to be used
3126 -- when PMTPLN_REC payment transactions are added
3127 cn_prepostbatches.get_UID(l_posting_batch_id);
3128 l_batch_rec.posting_batch_id := l_posting_batch_id;
3129 l_batch_rec.name := 'Payment recoveries for payrun ' ||
3130 p_payrun_id ||'-'||
3131 l_posting_batch_id;
3132
3133 -- shouldn't need to pass who columns to TH but pass them for now
3134 l_batch_rec.created_by := fnd_global.user_id;
3135 l_batch_rec.creation_date := sysdate;
3136 l_batch_rec.last_updated_by := fnd_global.user_id;
3137 l_batch_rec.last_update_date := sysdate;
3138 l_batch_rec.last_update_login:= fnd_global.login_id;
3139
3140 -- call table handler
3141 --cn_prepostbatches.insert_record(l_batch_rec);
3142 -- use old API
3143 cn_prepostbatches.Begin_Record
3144 (x_operation => 'INSERT',
3145 x_rowid => l_rowid,
3146 x_posting_batch_rec => l_batch_rec,
3147 x_program_type => null,
3148 p_org_id => l_payrun_rec.org_id);
3149
3150 FOR each_srp IN get_salesreps_in_payrun(l_payrun_rec.org_id)
3151 LOOP
3152
3153 l_srp_prd_rec.del_balance1_ctd := 0;
3154 l_srp_prd_rec.del_balance1_dtd := 0;
3155 l_srp_prd_rec.del_balance2_ctd := 0;
3156 l_srp_prd_rec.del_balance4_dtd := 0;
3157 l_srp_prd_rec.del_balance4_ctd := 0;
3158 l_srp_prd_rec.del_balance5_dtd := 0;
3159 l_srp_prd_rec.del_balance5_ctd := 0;
3160 l_pmt_amount_calc := 0;
3161 l_ctrl_pmt_amount := 0;
3162 l_pmt_amount_rec := 0;
3163 l_pmt_amount_nrec := 0;
3164 l_recovery := 0;
3165 l_waive_recovery := 0;
3166 -- l_hold_pmt := 0;
3167 l_tot_recovery := 0;
3168 l_tot_pmt_amount_calc := 0;
3169 l_pmt_amount_rec := 0;
3170
3171
3172 -- loop through worksheets with quota_id
3173 FOR wksht IN get_worksheet_data_for_pe (each_srp.salesrep_id,l_payrun_rec.org_id)
3174 LOOP
3175
3176 l_quota_id := wksht.quota_id;
3177 l_recovery := 0;
3178 l_pmt_amount_calc := 0;
3179 l_pmt_amount_rec := 0;
3180 l_pmt_amount_nrec := 0;
3181 -- l_hold_pmt := 0;
3182
3183 -- payment plan amount non-recoverable
3184 OPEN get_payment_details(wksht.salesrep_id, wksht.quota_id, l_payrun_rec.org_id);
3185 FETCH get_payment_details
3186 INTO l_pmt_amount_calc,
3187 l_pmt_amount_rec,
3188 l_pmt_amount_nrec,
3189 l_recovery;
3190 CLOSE get_payment_details;
3191 l_tot_pmt_amount_calc := l_tot_pmt_amount_calc + l_pmt_amount_calc;
3192
3193 -- control payment
3194 open get_control_pmt(wksht.salesrep_id, wksht.quota_id, l_payrun_rec.org_id);
3195 fetch get_control_pmt into l_ctrl_pmt_amount;
3196 close get_control_pmt;
3197 l_pmt_amount_rec := l_pmt_amount_rec + l_ctrl_pmt_amount;
3198
3199 -- waive recovery
3200 open get_waive_rec(wksht.salesrep_id, wksht.quota_id, l_payrun_rec.org_id);
3201 fetch get_waive_rec into l_waive_recovery;
3202 close get_waive_rec;
3203
3204 -- hold payment
3205 --open get_hold_pmt(wksht.salesrep_id, wksht.quota_id);
3206 --fetch get_hold_pmt into l_hold_pmt;
3207 --close get_hold_pmt;
3208 l_pmt_amount_rec := nvl(l_pmt_amount_rec, 0);-- - nvl(l_hold_pmt, 0);
3209 -- manual pay adjustment
3210 FOR i IN get_man_pay_adj(wksht.salesrep_id, wksht.quota_id, l_payrun_rec.org_id)
3211 LOOP
3212 IF i.recoverable_flag = 'Y'
3213 THEN
3214 l_pmt_amount_rec := l_pmt_amount_rec + i.man_pay_adj;
3215 ELSE
3216 l_pmt_amount_nrec := l_pmt_amount_nrec + i.man_pay_adj;
3217 END IF;
3218 END LOOP;
3219
3220 -- assign balance columns
3221 open get_srp_period(wksht.salesrep_id,
3222 l_payrun_rec.pay_period_id,
3223 wksht.quota_id,
3224 l_payrun_rec.org_id);
3225 LOOP
3226 fetch get_srp_period into l_srp_prd_rec.srp_period_id;
3227 EXIT WHEN get_srp_period%notfound;
3228
3229 -- changed for bug 2545629
3230 l_srp_prd_rec.del_balance1_ctd := l_recovery - l_waive_recovery;
3231 l_srp_prd_rec.del_balance1_dtd := l_pmt_amount_calc +
3232 l_pmt_amount_rec +
3233 l_pmt_amount_nrec;
3234 l_srp_prd_rec.del_balance2_ctd := l_pmt_amount_calc;
3235 l_srp_prd_rec.del_balance4_dtd := l_pmt_amount_rec;
3236 l_srp_prd_rec.del_balance4_ctd := l_recovery ;
3237 l_srp_prd_rec.del_balance5_dtd := l_pmt_amount_nrec + l_waive_recovery;
3238 l_srp_prd_rec.del_balance5_ctd := l_pmt_amount_nrec + l_waive_recovery;
3239
3240 l_tot_recovery := l_tot_recovery + l_recovery;
3241
3242
3243 -- update srp periods.09-22-03 BUG 3151860 : change to No_Sync
3244 cn_srp_periods_pvt.Update_Delta_Srp_Pds_No_Sync
3245 (p_api_version => 1.0,
3246 x_return_status => x_return_status,
3247 x_msg_count => x_msg_count,
3248 x_msg_data => x_msg_data,
3249 p_del_srp_prd_rec => l_srp_prd_rec,
3250 x_loading_status => l_loading_status);
3251
3252 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS)
3253 THEN
3254 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3255 END IF;
3256 END LOOP; -- end get_srp_period cursor loop
3257
3258 -- quota not active in this period, use carry over quota
3259 IF get_srp_period%ROWCOUNT = 0 THEN
3260
3261 l_srp_prd_rec.del_balance1_ctd := l_recovery - l_waive_recovery;
3262 l_srp_prd_rec.del_balance1_dtd := l_pmt_amount_calc +
3263 l_pmt_amount_rec + l_pmt_amount_nrec;
3264 l_srp_prd_rec.del_balance2_ctd := l_pmt_amount_calc;
3265 l_srp_prd_rec.del_balance4_dtd := l_pmt_amount_rec;
3266 l_srp_prd_rec.del_balance4_ctd := l_recovery ;
3267 l_srp_prd_rec.del_balance5_dtd := l_pmt_amount_nrec + l_waive_recovery;
3268 l_srp_prd_rec.del_balance5_ctd := l_pmt_amount_nrec + l_waive_recovery;
3269
3270 l_tot_recovery := l_tot_recovery + l_recovery;
3271
3272 OPEN carry_over_srp_period
3273 (wksht.salesrep_id,l_payrun_rec.pay_period_id, l_payrun_rec.org_id);
3274 FETCH carry_over_srp_period INTO l_srp_prd_rec.srp_period_id;
3275 CLOSE carry_over_srp_period;
3276
3277 -- update srp periods.09-22-03 BUG 3151860 : change to No_Sync
3278 cn_srp_periods_pvt.Update_Delta_Srp_Pds_No_Sync
3279 (p_api_version => 1.0,
3280 x_return_status => x_return_status,
3281 x_msg_count => x_msg_count,
3282 x_msg_data => x_msg_data,
3283 p_del_srp_prd_rec => l_srp_prd_rec,
3284 x_loading_status => l_loading_status);
3285
3286 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS)
3287 THEN
3288 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3289 END IF;
3290 END IF; -- end IF get_srp_period%ROWCOUNT = 0 THEN
3291
3292 CLOSE get_srp_period;
3293
3294 -- contribute to adjusted amount
3295 l_adj_amount:= l_pmt_amount_rec;
3296
3297 -- if needed, create PMTPLN_REC records in cn_payment_trasnactions
3298 IF l_adj_amount <> 0
3299 THEN
3300 -- quota ID here actually refers to pay element type ID
3301 -- quota ID = -1001 for pmt_pln_rec(Bug 2880233)
3302 l_element_type_id :=
3303 cn_api.get_pay_element_id(-1001, wksht.salesrep_id, l_payrun_rec.org_id, l_payrun_rec.pay_date);
3304 END IF;
3305
3306 IF nvl(l_adj_amount,0) <> 0
3307 THEN
3308 -- call table handler
3309 l_pmt_trx_rec.posting_batch_id := l_posting_batch_id;
3310 l_pmt_trx_rec.incentive_type_code := 'PMTPLN_REC';
3311 l_pmt_trx_rec.credit_type_id := l_credit_type_id;
3312 l_pmt_trx_rec.pay_period_id := l_payrun_rec.pay_period_id;
3313 l_pmt_trx_rec.amount := -l_adj_amount;
3314 l_pmt_trx_rec.payment_amount := -l_adj_amount;
3315 l_pmt_trx_rec.credited_salesrep_id := wksht.salesrep_id;
3316 l_pmt_trx_rec.payee_salesrep_id := wksht.salesrep_id;
3317 l_pmt_trx_rec.paid_flag := 'N';
3318 l_pmt_trx_rec.hold_flag := 'N';
3319 l_pmt_trx_rec.waive_flag := 'N';
3320 l_pmt_trx_rec.pay_element_type_id := l_element_type_id;
3321 l_pmt_trx_rec.quota_id := wksht.quota_id;
3322 --R12
3323 l_pmt_trx_rec.org_id := l_payrun_rec.org_id;
3324 l_pmt_trx_rec.object_version_number := 1;
3325
3326 cn_pmt_trans_pkg.insert_record(l_pmt_trx_rec);
3327
3328 END IF;
3329 END LOOP; -- worksheets with pe
3330
3331 -- After Cntpmtrb.pls 115.12.1158.11, cn_payment_transaction cannot create
3332 -- record with null quota_id, so remove code for handle null quota_id
3333
3334 -- call sync_accum for this salesrep. 09-22-03 BUG 3151860
3335 FOR l_sync_accum IN sync_accum(each_srp.salesrep_id,l_payrun_rec.pay_period_id, l_payrun_rec.org_id) LOOP
3336 cn_srp_periods_pvt.Sync_Accum_Balances_Start_Pd
3337 (p_salesrep_id => each_srp.salesrep_id,
3338 --R12
3339 p_org_id => l_payrun_rec.org_id,
3340 p_credit_type_id => -1000,
3341 p_role_id => l_sync_accum.role_id,
3342 p_start_period_id => l_payrun_rec.pay_period_id);
3343 END LOOP;
3344
3345 END LOOP; -- each srp loop
3346
3347 cn_payment_security_pvt.paid_payrun_audit
3348 (p_payrun_id => p_payrun_id,
3349 x_return_status => x_return_status,
3350 x_msg_count => x_msg_count,
3351 x_msg_data => x_msg_data);
3352 if x_return_status <> FND_API.G_RET_STS_SUCCESS then
3353 RAISE FND_API.G_EXC_ERROR;
3354 end if;
3355
3356 -- use if AP / Payroll integration has been enabled.
3357 IF l_payables_flag = 'Y'
3358 THEN
3359 -- Populate ccid's in payment worksheets (already done)
3360 /* IF (populate_ccids
3361 (p_payrun_id => p_payrun_id,
3362 p_loading_status => x_loading_status,
3363 x_loading_status => x_loading_status
3364 )) = fnd_api.g_true
3365 THEN
3366 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3367 END IF;
3368 */
3369
3370 -- populate ap interface
3371 IF (populate_ap_interface
3372 (p_payrun_id => p_payrun_id,
3373 p_loading_status => x_loading_status,
3374 x_loading_status => x_loading_status
3375 )) = fnd_api.g_true
3376 THEN
3377 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3378 END IF;
3379
3380 END IF;
3381 x_loading_status := 'CN_UPDATED';
3382
3383 -- Payroll Integration Start here
3384 -- Added on 02/19/01
3385 -- Kumar Sivasankaran
3386 IF l_payroll_flag = 'Y' THEN
3387 BUILD_BEE_API
3388 (x_return_status => x_return_status,
3389 x_msg_count => x_msg_count,
3390 x_msg_data => x_msg_data,
3391 p_payrun_id => p_payrun_id,
3392 p_loading_status => x_loading_status,
3393 x_loading_status => x_loading_status);
3394
3395 IF x_return_status <> FND_API.g_ret_sts_success THEN
3396 RAISE FND_API.G_EXC_ERROR;
3397 END IF;
3398 END IF;
3399
3400 OPEN get_payrun;
3401 FETCH get_payrun INTO l_payrun_rec;
3402 CLOSE get_payrun;
3403
3404 fnd_message.set_name('CN', 'CN_PMT_UPD_NOTE');
3405 fnd_message.set_token('NEW', l_payrun_rec.statusmeaning);
3406 fnd_message.set_token('OLD', l_status_meaning);
3407 l_note_msg := fnd_message.get;
3408 jtf_notes_pub.create_note
3409 (p_api_version => 1.0,
3410 x_return_status => x_return_status,
3411 x_msg_count => x_msg_count,
3412 x_msg_data => x_msg_data,
3413 p_source_object_id => p_payrun_id,
3414 p_source_object_code => 'CN_PAYRUNS',
3415 p_notes => l_note_msg,
3416 p_notes_detail => l_note_msg,
3417 p_note_type => 'CN_SYSGEN', -- for system generated
3418 x_jtf_note_id => l_note_id -- returned
3419 );
3420
3421
3422 -- End of API body
3423 -- Standard check of p_commit.
3424 IF FND_API.To_Boolean( p_commit ) THEN
3425 COMMIT WORK;
3426 END IF;
3427
3428 --
3429 -- Standard call to get message count and if count is 1, get message info.
3430 --
3431
3432 FND_MSG_PUB.Count_And_Get
3433 (
3434 p_count => x_msg_count ,
3435 p_data => x_msg_data ,
3436 p_encoded => FND_API.G_FALSE
3437 );
3438
3439 EXCEPTION
3440 WHEN FND_API.G_EXC_ERROR THEN
3441 ROLLBACK TO Pay_Payrun;
3442 x_return_status := FND_API.G_RET_STS_ERROR ;
3443 FND_MSG_PUB.Count_And_Get
3444 (
3445 p_count => x_msg_count ,
3446 p_data => x_msg_data ,
3447 p_encoded => FND_API.G_FALSE
3448 );
3449 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3450 ROLLBACK TO Pay_Payrun;
3451 x_loading_status := 'UNEXPECTED_ERR';
3452 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3453 FND_MSG_PUB.Count_And_Get
3454 (
3455 p_count => x_msg_count ,
3456 p_data => x_msg_data ,
3457 p_encoded => FND_API.G_FALSE
3458 );
3459 WHEN OTHERS THEN
3460 ROLLBACK TO Pay_Payrun;
3461 x_loading_status := 'UNEXPECTED_ERR';
3462 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3463 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3464 THEN
3465 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME ,l_api_name );
3466 END IF;
3467 FND_MSG_PUB.Count_And_Get
3468 (
3469 p_count => x_msg_count ,
3470 p_data => x_msg_data ,
3471 p_encoded => FND_API.G_FALSE
3472 );
3473 END Pay_Payrun;
3474
3475 --============================================================================
3476 --Name : delete_payrun_conc
3477 --Description : Procedure which will be used as the executable for the
3478 -- : concurrent program. delete payrun
3479 --
3480 --============================================================================
3481 PROCEDURE delete_payrun_conc
3482 ( errbuf OUT NOCOPY VARCHAR2,
3483 retcode OUT NOCOPY NUMBER ,
3484 p_name cn_payruns.name%TYPE,
3485 --R12
3486 p_org_name hr_operating_units.name%TYPE) IS
3487
3488 l_proc_audit_id NUMBER;
3489 l_return_status VARCHAR2(1000);
3490 l_msg_data VARCHAR2(2000);
3491 l_msg_count NUMBER;
3492 l_loading_status VARCHAR2(1000);
3493 l_status VARCHAR2(2000);
3494
3495 l_payrun_id NUMBER;
3496
3497
3498 Cursor get_payrun_id_curs IS
3499 select cp.payrun_id , cp.org_id,cp.OBJECT_VERSION_NUMBER
3500 from cn_payruns cp,
3501 --R12
3502 hr_operating_units hou
3503 where cp.name = p_name
3504 and cp.org_id = hou.organization_id
3505 and hou.name = p_org_name;
3506
3507 --R12
3508 l_org_id cn_payruns.org_id%TYPE;
3509 l_obj cn_payruns.object_version_number%type;
3510
3511 BEGIN
3512
3513 retcode := 0;
3514 -- Initial message list
3515 FND_MSG_PUB.initialize;
3516
3517 -- get payrun id
3518 open get_payrun_id_curs;
3519 fetch get_payrun_id_curs into l_payrun_id, l_org_id,l_obj;
3520 close get_payrun_id_curs;
3521
3522 cn_message_pkg.begin_batch
3523 ( x_process_type => 'DPRUN',
3524 x_process_audit_id => l_proc_audit_id,
3525 x_parent_proc_audit_id => l_proc_audit_id,
3526 x_request_id => NULL,
3527 --R12
3528 p_org_id => l_org_id
3529 );
3530
3531 cn_message_pkg.debug('***************************************************');
3532 cn_message_pkg.debug('Delete Payrun');
3533
3534 --call the create worksheet api
3535 CN_Payrun_PVT.delete_payrun
3536 (p_api_version => 1.0,
3537 p_init_msg_list => fnd_api.g_true,
3538 p_commit => fnd_api.g_false,
3539 p_validation_level => fnd_api.g_valid_level_full,
3540 x_return_status => l_return_status,
3541 x_msg_count => l_msg_count,
3542 x_msg_data => l_msg_data,
3543 p_payrun_id => l_payrun_id,
3544 p_validation_only => 'N',
3545 x_status => l_status,
3546 x_loading_status => l_loading_status );
3547
3548 IF l_return_status <> FND_API.g_ret_sts_success
3549 THEN
3550 retcode := 2;
3551 errbuf := FND_MSG_PUB.get(p_msg_index => fnd_msg_pub.G_LAST,
3552 p_encoded => FND_API.G_FALSE);
3553 cn_message_pkg.debug('Error for delete payrun : '||errbuf);
3554 ELSE
3555 COMMIT;
3556 END IF;
3557
3558 END delete_payrun_conc;
3559 --============================================================================
3560 END CN_Payrun_PVT;