[Home] [Help]
PACKAGE BODY: APPS.CN_PAYRUN_PVT
Source
1 PACKAGE BODY CN_PAYRUN_PVT as
2 -- $Header: cnvprunb.pls 120.20 2007/11/29 16:20:51 rnagired 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_worksheets IS
2401 SELECT payment_worksheet_id,object_version_number
2402 FROM cn_payment_worksheets
2403 WHERE payrun_id = p_payrun_id
2404 AND quota_id = null;
2405
2406 G_LAST_UPDATE_DATE DATE := sysdate;
2407 G_LAST_UPDATED_BY NUMBER := fnd_global.user_id;
2408 G_CREATION_DATE DATE := sysdate;
2409 G_CREATED_BY NUMBER := fnd_global.user_id;
2410 G_LAST_UPDATE_LOGIN NUMBER := fnd_global.login_id;
2411 g_credit_type_id CONSTANT NUMBER := -1000;
2412
2413 l_has_access BOOLEAN;
2414 l_note_msg VARCHAR2(240);
2415 l_note_id NUMBER;
2416 l_org_id NUMBER := -999;
2417 l_pmtbatch_name VARCHAR2(80);
2418
2419 BEGIN
2420 --
2421 -- Standard Start of API savepoint
2422 --
2423 SAVEPOINT Delete_Payrun;
2424 --
2425 -- Standard call to check for call compatibility.
2426 --
2427 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
2428 p_api_version ,
2429 l_api_name ,
2430 G_PKG_NAME )
2431 THEN
2432 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2433 END IF;
2434 --
2435 -- Initialize message list if p_init_msg_list is set to TRUE.
2436 --
2437 IF FND_API.to_Boolean( p_init_msg_list ) THEN
2438 FND_MSG_PUB.initialize;
2439 END IF;
2440 --
2441 -- Initialize API return status to success
2442 --
2443 x_return_status := FND_API.G_RET_STS_SUCCESS;
2444 x_loading_status := 'CN_DELETED';
2445 --
2446 -- API Body
2447 --
2448
2449
2450 OPEN get_old_record;
2451 FETCH get_old_record INTO l_old_record;
2452 IF get_old_record%rowcount = 0 THEN
2453
2454 --Error condition
2455 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
2456 THEN
2457 fnd_message.set_name('CN', 'CN_PAYRUN_DOES_NOT_EXIST');
2458 fnd_msg_pub.add;
2459 END IF;
2460
2461 x_loading_status := 'CN_PAYRUN_DOES_NOT_EXIST';
2462 RAISE FND_API.G_EXC_ERROR;
2463
2464 END IF;
2465 CLOSE get_old_record;
2466
2467 l_pmtbatch_name := l_old_record.NAME;
2468 l_org_id:=l_old_record.org_id;
2469
2470 IF l_old_record.status IN ('PAID', 'RETURNED_FUNDS', 'PAID_WITH_RETURNS')
2471 THEN
2472 --Error condition
2473 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
2474 THEN
2475 fnd_message.set_name('CN', 'CN_PAYRUN_PAID');
2476 fnd_msg_pub.add;
2477 END IF;
2478
2479 x_loading_status := 'CN_PAYRUN_PAID';
2480 RAISE FND_API.G_EXC_ERROR;
2481
2482 END IF;
2483
2484 -- Section included by Sundar Venkat on 07 Mar 2002
2485 -- Procedure to check if the payrun action is valid.
2486 CN_PAYMENT_SECURITY_PVT.Payrun_Action
2487 ( p_api_version => 1.0,
2488 p_init_msg_list => fnd_api.g_true,
2489 p_validation_level => fnd_api.g_valid_level_full,
2490 x_return_status => x_return_status,
2491 x_msg_count => x_msg_count,
2492 x_msg_data => x_msg_data,
2493 p_payrun_id => p_payrun_id,
2494 p_action => 'REMOVE'
2495 );
2496
2497 IF x_return_status <> FND_API.g_ret_sts_success THEN
2498 RAISE FND_API.G_EXC_ERROR;
2499 END IF;
2500
2501
2502
2503 FOR worksheets in get_worksheets
2504 LOOP
2505
2506 cn_payment_worksheet_pvt.delete_worksheet(
2507 p_api_version => p_api_version,
2508 p_init_msg_list => p_init_msg_list,
2509 p_commit => fnd_api.g_false,
2510 p_validation_level => p_validation_level,
2511 x_return_status => x_return_status,
2512 x_msg_count => x_msg_count,
2513 x_msg_data => x_msg_data,
2514 p_worksheet_id => worksheets.payment_worksheet_id,
2515 p_validation_only => p_validation_level, --R12
2516 x_status => x_status,
2517 x_loading_status => x_loading_status,
2518 p_ovn =>worksheets.object_version_number);
2519
2520 IF x_return_status <> FND_API.g_ret_sts_success THEN
2521 RAISE FND_API.G_EXC_ERROR;
2522 END IF;
2523
2524 END LOOP;
2525
2526 IF p_validation_only <> 'Y' THEN
2527 cn_payruns_pkg.delete_record
2528 (x_payrun_id => p_payrun_id) ;
2529
2530 x_loading_status := 'CN_DELETED';
2531
2532 -- End of API body.
2533 -- Standard check of p_commit.
2534 IF (l_org_id <> -999) THEN
2535 fnd_message.set_name ('CN', 'CN_PMT_DEL_NOTE');
2536 fnd_message.set_token ('PMTBATCH_NAME', l_pmtbatch_name);
2537 l_note_msg := fnd_message.get;
2538 jtf_notes_pub.create_note
2539 (p_api_version => 1.0,
2540 x_return_status => x_return_status,
2541 x_msg_count => x_msg_count,
2542 x_msg_data => x_msg_data,
2543 p_source_object_id => l_org_id,
2544 p_source_object_code => 'CN_REPOSITORIES',
2545 p_notes => l_note_msg,
2546 p_notes_detail => l_note_msg,
2547 p_note_type => 'CN_SYSGEN', -- for system generated
2548 x_jtf_note_id => l_note_id -- returned
2549 );
2550 END IF;
2551 END IF;
2552
2553 IF FND_API.To_Boolean( p_commit ) THEN
2554 COMMIT WORK;
2555 END IF;
2556
2557 --
2558 -- Standard call to get message count and if count is 1, get message info.
2559 --
2560 FND_MSG_PUB.Count_And_Get
2561 (
2562 p_count => x_msg_count ,
2563 p_data => x_msg_data ,
2564 p_encoded => FND_API.G_FALSE
2565 );
2566 EXCEPTION
2567 WHEN FND_API.G_EXC_ERROR THEN
2568 ROLLBACK TO Delete_Payrun;
2569 x_return_status := FND_API.G_RET_STS_ERROR ;
2570 FND_MSG_PUB.Count_And_Get
2571 (
2572 p_count => x_msg_count ,
2573 p_data => x_msg_data ,
2574 p_encoded => FND_API.G_FALSE
2575 );
2576 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2577 ROLLBACK TO Delete_Payrun;
2578 x_loading_status := 'UNEXPECTED_ERR';
2579 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2580 FND_MSG_PUB.Count_And_Get
2581 (
2582 p_count => x_msg_count ,
2583 p_data => x_msg_data ,
2584 p_encoded => FND_API.G_FALSE
2585 );
2586 WHEN OTHERS THEN
2587 ROLLBACK TO Delete_Payrun;
2588 x_loading_status := 'UNEXPECTED_ERR';
2589 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2590 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2591 THEN
2592 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME ,l_api_name );
2593 END IF;
2594 FND_MSG_PUB.Count_And_Get
2595 (
2596 p_count => x_msg_count ,
2597 p_data => x_msg_data ,
2598 p_encoded => FND_API.G_FALSE
2599 );
2600
2601 END Delete_Payrun;
2602
2603 -- ===========================================================================
2604 -- Procedure : Pay_payrun_Approve_Wksht
2605 -- Description: Approve all whskt before pay a payrun
2606 -- ===========================================================================
2607
2608 PROCEDURE Pay_Payrun_Approve_Wksht
2609 (p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
2610 x_return_status OUT NOCOPY VARCHAR2,
2611 x_msg_count OUT NOCOPY NUMBER,
2612 x_msg_data OUT NOCOPY VARCHAR2,
2613 p_payrun_id IN cn_payruns.payrun_id%TYPE )
2614 IS
2615
2616 l_api_name CONSTANT VARCHAR2(30) := 'Pay_Payrun_Approve_Wksht';
2617 l_api_version CONSTANT NUMBER := 1.0;
2618
2619 CURSOR get_unpaid_wksht IS
2620 SELECT payment_worksheet_id, salesrep_id
2621 FROM cn_payment_worksheets
2622 WHERE payrun_id = p_payrun_id AND worksheet_status = 'UNPAID'
2623 AND quota_id IS NULL ;
2624
2625 CURSOR get_unapprove_wksht IS
2626 SELECT payment_worksheet_id, salesrep_id
2627 FROM cn_payment_worksheets
2628 WHERE payrun_id = p_payrun_id AND worksheet_status <> 'APPROVED'
2629 AND quota_id IS NULL;
2630
2631 BEGIN
2632 --
2633 -- Standard Start of API savepoint
2634 --
2635 SAVEPOINT Pay_Payrun_Approve_Wksht;
2636 --
2637 -- Initialize API return status to success
2638 --
2639 x_return_status := FND_API.G_RET_STS_SUCCESS;
2640 --
2641 -- API body
2642 --
2643
2644 -- Lock any unpaid worksheets so cn_worksheet_qg_dtls and wksht bb cols
2645 -- will get populate
2646 FOR unpaid_rec IN get_unpaid_wksht LOOP
2647 -- save current image if LOCK worksheet
2648 cn_payment_worksheet_pvt.set_ced_and_bb ( p_api_version => 1.0,
2649 x_return_status => x_return_status,
2650 x_msg_count => x_msg_count,
2651 x_msg_data => x_msg_data,
2652 p_worksheet_id => unpaid_rec.payment_worksheet_id
2653 );
2654
2655 IF x_return_status <> FND_API.g_ret_sts_success THEN
2656 RAISE FND_API.G_EXC_ERROR;
2657 END IF;
2658
2659 -- validate lock and audit worksheet
2660 cn_payment_security_pvt.worksheet_action(p_api_version => 1.0,
2661 p_init_msg_list => fnd_api.g_false,
2662 p_commit => 'F',
2663 p_validation_level => p_validation_level,
2664 x_return_status => x_return_status,
2665 x_msg_count => x_msg_count,
2666 x_msg_data => x_msg_data,
2667 p_worksheet_id => unpaid_rec.payment_worksheet_id,
2668 p_action => 'LOCK',
2669 p_do_audit => fnd_api.g_true
2670 );
2671
2672 IF x_return_status <> FND_API.g_ret_sts_success THEN
2673 RAISE FND_API.G_EXC_ERROR;
2674 END IF;
2675 END LOOP;
2676
2677 -- Approve all wksht
2678 FOR unapprove_rec IN get_unapprove_wksht LOOP
2679 -- validate
2680 cn_payment_security_pvt.worksheet_action(p_api_version => 1.0,
2681 p_init_msg_list => fnd_api.g_false,
2682 p_commit => 'F',
2683 p_validation_level => p_validation_level,
2684 x_return_status => x_return_status,
2685 x_msg_count => x_msg_count,
2686 x_msg_data => x_msg_data,
2687 p_worksheet_id => unapprove_rec.payment_worksheet_id,
2688 p_action => 'APPROVE',
2689 p_do_audit => fnd_api.g_false
2690 );
2691
2692 -- set wksht audit
2693 CN_PAYMENT_SECURITY_PVT.Worksheet_Audit
2694 (p_worksheet_id => unapprove_rec.payment_worksheet_id,
2695 p_payrun_id => p_payrun_id,
2696 p_salesrep_id => unapprove_rec.salesrep_id,
2697 p_action => 'APPROVE',
2698 p_do_approval_flow => FND_API.G_FALSE,
2699 x_return_status => x_return_status,
2700 x_msg_count => x_msg_count,
2701 x_msg_data => x_msg_data);
2702
2703 IF x_return_status <> FND_API.g_ret_sts_success THEN
2704 RAISE FND_API.G_EXC_ERROR;
2705 END IF;
2706 END LOOP;
2707
2708 -- End of API body
2709
2710 --
2711 -- Standard call to get message count and if count is 1, get message info.
2712 --
2713
2714 FND_MSG_PUB.Count_And_Get
2715 (p_count => x_msg_count ,
2716 p_data => x_msg_data ,
2717 p_encoded => FND_API.G_FALSE);
2718
2719 EXCEPTION
2720 WHEN FND_API.G_EXC_ERROR THEN
2721 ROLLBACK TO Pay_Payrun_Approve_Wksht;
2722 x_return_status := FND_API.G_RET_STS_ERROR ;
2723 FND_MSG_PUB.Count_And_Get
2724 (p_count => x_msg_count ,
2725 p_data => x_msg_data ,
2726 p_encoded => FND_API.G_FALSE
2727 );
2728 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2729 ROLLBACK TO Pay_Payrun_Approve_Wksht;
2730 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2731 FND_MSG_PUB.Count_And_Get
2732 (
2733 p_count => x_msg_count ,
2734 p_data => x_msg_data ,
2735 p_encoded => FND_API.G_FALSE
2736 );
2737 WHEN OTHERS THEN
2738 ROLLBACK TO Pay_Payrun_Approve_Wksht;
2739 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2740 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2741 THEN
2742 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME ,l_api_name );
2743 END IF;
2744 FND_MSG_PUB.Count_And_Get
2745 (
2746 p_count => x_msg_count ,
2747 p_data => x_msg_data ,
2748 p_encoded => FND_API.G_FALSE
2749 );
2750 END Pay_Payrun_Approve_Wksht;
2751
2752 -- ===========================================================================
2753 -- Procedure : Pay_payrun
2754 -- Description: To pay a payrun
2755 -- Update the subledger
2756 -- Modified logic to achieve the Payment Plan Amount.
2757 -- Kumar Sivasankaran 11/12/2001
2758 -- Matt Blum 05/02/2002
2759 -- ===========================================================================
2760
2761 PROCEDURE Pay_Payrun
2762 ( p_api_version IN NUMBER,
2763 p_init_msg_list IN VARCHAR2,
2764 p_commit IN VARCHAR2,
2765 p_validation_level IN NUMBER,
2766 x_return_status OUT NOCOPY VARCHAR2,
2767 x_msg_count OUT NOCOPY NUMBER,
2768 x_msg_data OUT NOCOPY VARCHAR2,
2769 p_payrun_id IN cn_payruns.payrun_id%TYPE,
2770 p_x_obj_ver_number IN OUT NOCOPY cn_payruns.object_version_number%TYPE,
2771 x_status OUT NOCOPY VARCHAR2,
2772 x_loading_status OUT NOCOPY VARCHAR2
2773 ) IS
2774
2775 l_api_name CONSTANT VARCHAR2(30) := 'Pay_Payrun';
2776 l_api_version CONSTANT NUMBER := 1.0;
2777
2778 -- for balances
2779 l_ctrl_pmt_amount NUMBER := 0;
2780 -- l_hold_pmt NUMBER := 0;
2781 l_pmt_amount_rec NUMBER := 0;
2782 l_pmt_amount_nrec NUMBER := 0;
2783 l_recovery NUMBER := 0;
2784 l_tot_recovery NUMBER := 0;
2785 l_waive_recovery NUMBER := 0;
2786 l_pmt_amount_calc NUMBER := 0;
2787 l_tot_pmt_amount_calc NUMBER := 0;
2788 l_adj_amount NUMBER := 0;
2789 l_rec_amt NUMBER := 0;
2790 l_quota_id NUMBER;
2791
2792 -- general stuff
2793 l_posting_batch_id NUMBER;
2794 l_batch_name VARCHAR2(30);
2795 l_credit_type_id NUMBER := -1000;
2796
2797 -- for payroll integration
2798 l_element_type_id NUMBER;
2799 l_payables_flag cn_repositories.payables_flag%TYPE;
2800 l_payroll_flag cn_repositories.payroll_flag%TYPE;
2801 l_payables_ccid_level cn_repositories.payables_ccid_level%TYPE;
2802
2803 l_pmt_trx_rec cn_pmt_trans_pkg.pmt_trans_rec_type;
2804 l_batch_rec cn_prepostbatches.posting_batch_rec_type;
2805
2806 -- for API calls
2807 l_loading_status VARCHAR2(30);
2808 l_rowid VARCHAR2(30);
2809
2810 l_srp_prd_rec cn_srp_periods_pvt.delta_srp_period_rec_type
2811 := cn_srp_periods_pvt.g_miss_delta_srp_period_rec;
2812 l_note_msg VARCHAR2(240);
2813 l_note_id NUMBER;
2814 l_status_meaning cn_payruns.status%TYPE;
2815
2816 CURSOR get_payrun IS
2817 SELECT pay_period_id, pay_date, accounting_period_id, org_id
2818 ,name, cn_api.get_lkup_meaning(cn_payruns.status,'PAYRUN_STATUS') statusmeaning
2819 FROM cn_payruns
2820 WHERE payrun_id = p_payrun_id;
2821
2822 l_payrun_rec get_payrun%ROWTYPE;
2823
2824 CURSOR get_salesreps_in_payrun(p_org_id cn_payruns.org_id%TYPE) IS
2825 SELECT distinct salesrep_id
2826 FROM cn_payment_worksheets
2827 WHERE payrun_id = p_payrun_id
2828 --R12
2829 AND org_id = p_org_id;
2830
2831 CURSOR get_worksheet_data_for_pe (p_salesrep_id NUMBER, p_org_id cn_payruns.org_id%TYPE)IS
2832 SELECT salesrep_id, quota_id, credit_type_id,
2833 pmt_amount_calc, pmt_amount_adj, pmt_amount_adj_rec,
2834 pmt_amount_adj_nrec, pmt_amount_recovery
2835 FROM cn_payment_worksheets
2836 WHERE payrun_id = p_payrun_id
2837 AND salesrep_id = p_salesrep_id
2838 AND quota_id is not null;
2839
2840
2841
2842 CURSOR get_srp_period(p_salesrep_id NUMBER,
2843 p_period_id NUMBER,
2844 p_quota_id NUMBER,
2845 p_org_id cn_payruns.org_id%TYPE) IS
2846 SELECT srp_period_id
2847 FROM cn_srp_periods
2848 WHERE salesrep_id = p_salesrep_id
2849 AND credit_type_id = l_credit_type_id
2850 AND period_id = p_period_id
2851 AND (quota_id = p_quota_id
2852 OR
2853 (p_quota_id IS NULL AND quota_id IS NULL))
2854 --R12
2855 AND org_id = p_org_id
2856 AND ROWNUM < 2 ; -- Bug 2819874
2857
2858 -- for payroll integration
2859 CURSOR get_apps(p_org_id cn_payruns.org_id%TYPE) IS
2860 SELECT payables_flag, payroll_flag, payables_ccid_level
2861 FROM cn_repositories
2862 --R12
2863 WHERE org_id = p_org_id;
2864
2865
2866
2867 -- for wkshts with null quota ID - get from transactions
2868 CURSOR get_control_pmt(p_salesrep_id NUMBER,
2869 p_quota_id NUMBER,
2870 p_org_id cn_payruns.org_id%TYPE) IS
2871 SELECT nvl(sum(nvl(payment_amount,0) - nvl(amount,0)),0) control_payment
2872 FROM cn_payment_transactions
2873 WHERE payrun_id = p_payrun_id
2874 AND credited_salesrep_id = p_salesrep_id
2875 AND credit_type_id = l_credit_type_id
2876 AND (quota_id = p_quota_id
2877 OR
2878 (p_quota_id IS NULL AND quota_id IS NULL))
2879 AND incentive_type_code <> 'PMTPLN_REC'
2880 AND nvl(hold_flag, 'N') = 'N';
2881
2882 CURSOR get_hold_pmt(p_salesrep_id NUMBER,
2883 p_quota_id NUMBER) IS
2884 SELECT nvl(sum(nvl(amount,0)),0) hold_payment
2885 FROM cn_payment_transactions
2886 WHERE payrun_id = p_payrun_id
2887 AND credited_salesrep_id = p_salesrep_id
2888 AND credit_type_id = l_credit_type_id
2889 AND (quota_id = p_quota_id
2890 OR
2891 (p_quota_id IS NULL AND quota_id IS NULL))
2892 AND incentive_type_code <> 'PMTPLN_REC'
2893 AND nvl(hold_flag, 'N') = 'Y';
2894
2895 -- Bug 2795606 : use amount not pmt_amt since get_cp will handle adj amt
2896
2897 CURSOR get_man_pay_adj(p_salesrep_id NUMBER,
2898 p_quota_id NUMBER,
2899 p_org_id cn_payruns.org_id%TYPE) IS
2900 SELECT nvl(sum(nvl(amount,0)),0) man_pay_adj, recoverable_flag
2901 FROM cn_payment_transactions
2902 WHERE payrun_id = p_payrun_id
2903 AND credited_salesrep_id = p_salesrep_id
2904 AND credit_type_id = l_credit_type_id
2905 AND quota_id = p_quota_id
2906 AND incentive_type_code = 'MANUAL_PAY_ADJ'
2907 GROUP BY recoverable_flag;
2908
2909 CURSOR get_payment_details(p_salesrep_id NUMBER,
2910 p_quota_id NUMBER,
2911 p_org_id cn_payruns.org_id%TYPE) IS
2912 SELECT nvl(pmt_amount_calc,0),
2913 nvl(pmt_amount_adj_rec,0),
2914 nvl(pmt_amount_adj_nrec,0),
2915 -nvl(pmt_amount_recovery,0)
2916 FROM cn_payment_worksheets
2917 WHERE payrun_id = p_payrun_id
2918 AND salesrep_id = p_salesrep_id
2919 AND credit_type_id = l_credit_type_id
2920 AND (quota_id = p_quota_id
2921 OR
2922 (p_quota_id IS NULL AND quota_id IS NULL));
2923
2924 CURSOR get_waive_rec(p_salesrep_id NUMBER,
2925 p_quota_id NUMBER,
2926 p_org_id cn_payruns.org_id%TYPE) IS
2927 SELECT -nvl(sum(nvl(payment_amount,0)),0)
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 incentive_type_code = 'PMTPLN_REC'
2933 AND nvl(hold_flag, 'N') = 'N'
2934 AND waive_flag = 'Y'
2935 AND (quota_id = p_quota_id
2936 OR
2937 (p_quota_id IS NULL AND quota_id IS NULL))
2938 --R12
2939 AND org_id = p_org_id;
2940
2941 -- get carry over srp_periods record
2942 CURSOR carry_over_srp_period(c_salesrep_id NUMBER,
2943 c_period_id NUMBER,
2944 p_org_id cn_payruns.org_id%TYPE) IS
2945 SELECT sprd.srp_period_id
2946 FROM cn_srp_periods sprd
2947 WHERE
2948 sprd.salesrep_id = c_salesrep_id
2949 AND sprd.period_id = c_period_id
2950 AND sprd.quota_id = -1000
2951 AND sprd.credit_type_id = -1000
2952 --R12
2953 AND org_id = p_org_id;
2954
2955 -- Get sync_accum records. Bug 3151860
2956 CURSOR sync_accum(c_salesrep_id NUMBER,
2957 c_period_id NUMBER,
2958 p_org_id cn_payruns.org_id%TYPE) IS
2959 SELECT DISTINCT sprd.role_id
2960 FROM cn_srp_periods sprd
2961 WHERE
2962 sprd.salesrep_id = c_salesrep_id
2963 AND sprd.period_id = c_period_id
2964 AND sprd.credit_type_id = -1000
2965 --R12
2966 AND org_id = p_org_id;
2967
2968 G_LAST_UPDATE_DATE DATE := sysdate;
2969 G_LAST_UPDATED_BY NUMBER := fnd_global.user_id;
2970 G_CREATION_DATE DATE := sysdate;
2971 G_CREATED_BY NUMBER := fnd_global.user_id;
2972 G_LAST_UPDATE_LOGIN NUMBER := fnd_global.login_id;
2973
2974 l_has_access BOOLEAN;
2975
2976 BEGIN
2977 --
2978 -- Standard Start of API savepoint
2979 --
2980 SAVEPOINT Pay_Payrun;
2981 --
2982 -- Standard call to check for call compatibility.
2983 --
2984 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
2985 p_api_version ,
2986 l_api_name ,
2987 G_PKG_NAME )
2988 THEN
2989 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2990 END IF;
2991 --
2992 -- Initialize message list if p_init_msg_list is set to TRUE.
2993 --
2994 IF FND_API.to_Boolean( p_init_msg_list ) THEN
2995 FND_MSG_PUB.initialize;
2996 END IF;
2997 --
2998 -- Initialize API return status to success
2999 --
3000 x_return_status := FND_API.G_RET_STS_SUCCESS;
3001 x_loading_status := 'CN_UPDATED';
3002
3003
3004
3005 --Validate if payrun is valid
3006 IF ((cn_api.chk_miss_null_num_para
3007 (p_num_para => p_payrun_id,
3008 p_obj_name =>
3009 cn_api.get_lkup_meaning('PAY_RUN_NAME', 'PAY_RUN_VALIDATION_TYPE'),
3010 p_loading_status => x_loading_status,
3011 x_loading_status => x_loading_status)) = FND_API.G_TRUE)
3012 THEN
3013 RAISE FND_API.G_EXC_ERROR ;
3014 END IF;
3015
3016 OPEN get_payrun;
3017 FETCH get_payrun INTO l_payrun_rec;
3018 IF get_payrun%rowcount = 0 THEN
3019 --Error condition
3020 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
3021 THEN
3022 fnd_message.set_name('CN', 'CN_INVALID_PAYRUN');
3023 fnd_msg_pub.add;
3024 END IF;
3025
3026 x_loading_status := 'CN_INVALID_PAYRUN';
3027 CLOSE get_payrun;
3028 RAISE FND_API.G_EXC_ERROR;
3029
3030 END IF;
3031 CLOSE get_payrun;
3032 l_status_meaning := l_payrun_rec.statusmeaning;
3033
3034 -- initialize payrun action
3035 cn_payment_security_pvt.payrun_action
3036 (p_api_version => 1.0,
3037 x_return_status => x_return_status,
3038 x_msg_count => x_msg_count,
3039 x_msg_data => x_msg_data,
3040 p_payrun_id => p_payrun_id,
3041 p_action => 'PAY');
3042 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3043 RAISE FND_API.G_EXC_ERROR;
3044 END IF;
3045
3046 -- Need to auto-approve all wkshts if CN_CHK_WKSHT_STATUS = N
3047 IF nvl(fnd_profile.value('CN_CHK_WKSHT_STATUS'), 'Y') = 'N' THEN
3048 -- Lock and Approve all wkshts
3049 Pay_Payrun_Approve_Wksht
3050 (x_return_status => x_return_status,
3051 x_msg_count => x_msg_count,
3052 x_msg_data => x_msg_data,
3053 p_payrun_id => p_payrun_id);
3054 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
3055 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3056 END IF;
3057 END IF;
3058
3059 -- Check if AP / Payroll integration has been enabled.
3060 OPEN get_apps(l_payrun_rec.org_id);
3061 FETCH get_apps INTO l_payables_flag, l_payroll_flag, l_payables_ccid_level;
3062 CLOSE get_apps;
3063
3064 IF l_payroll_flag = 'Y'
3065 THEN
3066 IF NOT validate_pay(
3067 p_payrun =>p_payrun_id,
3068 p_pay_date =>l_payrun_rec.pay_date,
3069 p_loading_status =>x_loading_status,
3070 x_loading_status =>x_loading_status)
3071 THEN
3072 RAISE fnd_api.g_exc_error;
3073 END IF;
3074 END IF;
3075
3076 -- process hold transactions
3077 UPDATE cn_payment_transactions
3078 SET payrun_id = '',
3079 LAST_UPDATE_DATE = Sysdate,
3080 LAST_UPDATED_BY = G_LAST_UPDATED_BY,
3081 LAST_UPDATE_LOGIN = G_LAST_UPDATE_LOGIN
3082 WHERE payrun_id = p_payrun_id
3083 AND hold_flag = 'Y'
3084 --R12
3085 AND org_id = l_payrun_rec.org_id;
3086
3087 -- set transactions to paid for this payrun
3088 UPDATE cn_payment_transactions
3089 SET paid_flag = 'Y',
3090 LAST_UPDATE_DATE = Sysdate,
3091 LAST_UPDATED_BY = G_LAST_UPDATED_BY,
3092 LAST_UPDATE_LOGIN = G_LAST_UPDATE_LOGIN
3093 WHERE payrun_id = p_payrun_id
3094 --R12
3095 AND org_id = l_payrun_rec.org_id;
3096
3097
3098 -- open a transaction (used to be called posting) batch to be used
3099 -- when PMTPLN_REC payment transactions are added
3100 cn_prepostbatches.get_UID(l_posting_batch_id);
3101 l_batch_rec.posting_batch_id := l_posting_batch_id;
3102 l_batch_rec.name := 'Payment recoveries for payrun ' ||
3103 p_payrun_id ||'-'||
3104 l_posting_batch_id;
3105
3106 -- shouldn't need to pass who columns to TH but pass them for now
3107 l_batch_rec.created_by := fnd_global.user_id;
3108 l_batch_rec.creation_date := sysdate;
3109 l_batch_rec.last_updated_by := fnd_global.user_id;
3110 l_batch_rec.last_update_date := sysdate;
3111 l_batch_rec.last_update_login:= fnd_global.login_id;
3112
3113 -- call table handler
3114 --cn_prepostbatches.insert_record(l_batch_rec);
3115 -- use old API
3116 cn_prepostbatches.Begin_Record
3117 (x_operation => 'INSERT',
3118 x_rowid => l_rowid,
3119 x_posting_batch_rec => l_batch_rec,
3120 x_program_type => null,
3121 p_org_id => l_payrun_rec.org_id);
3122
3123 FOR each_srp IN get_salesreps_in_payrun(l_payrun_rec.org_id)
3124 LOOP
3125
3126 l_srp_prd_rec.del_balance1_ctd := 0;
3127 l_srp_prd_rec.del_balance1_dtd := 0;
3128 l_srp_prd_rec.del_balance2_ctd := 0;
3129 l_srp_prd_rec.del_balance4_dtd := 0;
3130 l_srp_prd_rec.del_balance4_ctd := 0;
3131 l_srp_prd_rec.del_balance5_dtd := 0;
3132 l_srp_prd_rec.del_balance5_ctd := 0;
3133 l_pmt_amount_calc := 0;
3134 l_ctrl_pmt_amount := 0;
3135 l_pmt_amount_rec := 0;
3136 l_pmt_amount_nrec := 0;
3137 l_recovery := 0;
3138 l_waive_recovery := 0;
3139 -- l_hold_pmt := 0;
3140 l_tot_recovery := 0;
3141 l_tot_pmt_amount_calc := 0;
3142 l_pmt_amount_rec := 0;
3143
3144
3145 -- loop through worksheets with quota_id
3146 FOR wksht IN get_worksheet_data_for_pe (each_srp.salesrep_id,l_payrun_rec.org_id)
3147 LOOP
3148
3149 l_quota_id := wksht.quota_id;
3150 l_recovery := 0;
3151 l_pmt_amount_calc := 0;
3152 l_pmt_amount_rec := 0;
3153 l_pmt_amount_nrec := 0;
3154 -- l_hold_pmt := 0;
3155
3156 -- payment plan amount non-recoverable
3157 OPEN get_payment_details(wksht.salesrep_id, wksht.quota_id, l_payrun_rec.org_id);
3158 FETCH get_payment_details
3159 INTO l_pmt_amount_calc,
3160 l_pmt_amount_rec,
3161 l_pmt_amount_nrec,
3162 l_recovery;
3163 CLOSE get_payment_details;
3164 l_tot_pmt_amount_calc := l_tot_pmt_amount_calc + l_pmt_amount_calc;
3165
3166 -- control payment
3167 open get_control_pmt(wksht.salesrep_id, wksht.quota_id, l_payrun_rec.org_id);
3168 fetch get_control_pmt into l_ctrl_pmt_amount;
3169 close get_control_pmt;
3170 l_pmt_amount_rec := l_pmt_amount_rec + l_ctrl_pmt_amount;
3171
3172 -- waive recovery
3173 open get_waive_rec(wksht.salesrep_id, wksht.quota_id, l_payrun_rec.org_id);
3174 fetch get_waive_rec into l_waive_recovery;
3175 close get_waive_rec;
3176
3177 -- hold payment
3178 --open get_hold_pmt(wksht.salesrep_id, wksht.quota_id);
3179 --fetch get_hold_pmt into l_hold_pmt;
3180 --close get_hold_pmt;
3181 l_pmt_amount_rec := nvl(l_pmt_amount_rec, 0);-- - nvl(l_hold_pmt, 0);
3182 -- manual pay adjustment
3183 FOR i IN get_man_pay_adj(wksht.salesrep_id, wksht.quota_id, l_payrun_rec.org_id)
3184 LOOP
3185 IF i.recoverable_flag = 'Y'
3186 THEN
3187 l_pmt_amount_rec := l_pmt_amount_rec + i.man_pay_adj;
3188 ELSE
3189 l_pmt_amount_nrec := l_pmt_amount_nrec + i.man_pay_adj;
3190 END IF;
3191 END LOOP;
3192
3193 -- assign balance columns
3194 open get_srp_period(wksht.salesrep_id,
3195 l_payrun_rec.pay_period_id,
3196 wksht.quota_id,
3197 l_payrun_rec.org_id);
3198 LOOP
3199 fetch get_srp_period into l_srp_prd_rec.srp_period_id;
3200 EXIT WHEN get_srp_period%notfound;
3201
3202 -- changed for bug 2545629
3203 l_srp_prd_rec.del_balance1_ctd := l_recovery - l_waive_recovery;
3204 l_srp_prd_rec.del_balance1_dtd := l_pmt_amount_calc +
3205 l_pmt_amount_rec +
3206 l_pmt_amount_nrec;
3207 l_srp_prd_rec.del_balance2_ctd := l_pmt_amount_calc;
3208 l_srp_prd_rec.del_balance4_dtd := l_pmt_amount_rec;
3209 l_srp_prd_rec.del_balance4_ctd := l_recovery ;
3210 l_srp_prd_rec.del_balance5_dtd := l_pmt_amount_nrec + l_waive_recovery;
3211 l_srp_prd_rec.del_balance5_ctd := l_pmt_amount_nrec + l_waive_recovery;
3212
3213 l_tot_recovery := l_tot_recovery + l_recovery;
3214
3215
3216 -- update srp periods.09-22-03 BUG 3151860 : change to No_Sync
3217 cn_srp_periods_pvt.Update_Delta_Srp_Pds_No_Sync
3218 (p_api_version => 1.0,
3219 x_return_status => x_return_status,
3220 x_msg_count => x_msg_count,
3221 x_msg_data => x_msg_data,
3222 p_del_srp_prd_rec => l_srp_prd_rec,
3223 x_loading_status => l_loading_status);
3224
3225 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS)
3226 THEN
3227 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3228 END IF;
3229 END LOOP; -- end get_srp_period cursor loop
3230
3231 -- quota not active in this period, use carry over quota
3232 IF get_srp_period%ROWCOUNT = 0 THEN
3233
3234 l_srp_prd_rec.del_balance1_ctd := l_recovery - l_waive_recovery;
3235 l_srp_prd_rec.del_balance1_dtd := l_pmt_amount_calc +
3236 l_pmt_amount_rec + l_pmt_amount_nrec;
3237 l_srp_prd_rec.del_balance2_ctd := l_pmt_amount_calc;
3238 l_srp_prd_rec.del_balance4_dtd := l_pmt_amount_rec;
3239 l_srp_prd_rec.del_balance4_ctd := l_recovery ;
3240 l_srp_prd_rec.del_balance5_dtd := l_pmt_amount_nrec + l_waive_recovery;
3241 l_srp_prd_rec.del_balance5_ctd := l_pmt_amount_nrec + l_waive_recovery;
3242
3243 l_tot_recovery := l_tot_recovery + l_recovery;
3244
3245 OPEN carry_over_srp_period
3246 (wksht.salesrep_id,l_payrun_rec.pay_period_id, l_payrun_rec.org_id);
3247 FETCH carry_over_srp_period INTO l_srp_prd_rec.srp_period_id;
3248 CLOSE carry_over_srp_period;
3249
3250 -- update srp periods.09-22-03 BUG 3151860 : change to No_Sync
3251 cn_srp_periods_pvt.Update_Delta_Srp_Pds_No_Sync
3252 (p_api_version => 1.0,
3253 x_return_status => x_return_status,
3254 x_msg_count => x_msg_count,
3255 x_msg_data => x_msg_data,
3256 p_del_srp_prd_rec => l_srp_prd_rec,
3257 x_loading_status => l_loading_status);
3258
3259 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS)
3260 THEN
3261 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3262 END IF;
3263 END IF; -- end IF get_srp_period%ROWCOUNT = 0 THEN
3264
3265 CLOSE get_srp_period;
3266
3267 -- contribute to adjusted amount
3268 l_adj_amount:= l_pmt_amount_rec;
3269
3270 -- if needed, create PMTPLN_REC records in cn_payment_trasnactions
3271 IF l_adj_amount <> 0
3272 THEN
3273 -- quota ID here actually refers to pay element type ID
3274 -- quota ID = -1001 for pmt_pln_rec(Bug 2880233)
3275 l_element_type_id :=
3276 cn_api.get_pay_element_id(-1001, wksht.salesrep_id, l_payrun_rec.org_id, l_payrun_rec.pay_date);
3277 END IF;
3278
3279 IF nvl(l_adj_amount,0) <> 0
3280 THEN
3281 -- call table handler
3282 l_pmt_trx_rec.posting_batch_id := l_posting_batch_id;
3283 l_pmt_trx_rec.incentive_type_code := 'PMTPLN_REC';
3284 l_pmt_trx_rec.credit_type_id := l_credit_type_id;
3285 l_pmt_trx_rec.pay_period_id := l_payrun_rec.pay_period_id;
3286 l_pmt_trx_rec.amount := -l_adj_amount;
3287 l_pmt_trx_rec.payment_amount := -l_adj_amount;
3288 l_pmt_trx_rec.credited_salesrep_id := wksht.salesrep_id;
3289 l_pmt_trx_rec.payee_salesrep_id := wksht.salesrep_id;
3290 l_pmt_trx_rec.paid_flag := 'N';
3291 l_pmt_trx_rec.hold_flag := 'N';
3292 l_pmt_trx_rec.waive_flag := 'N';
3293 l_pmt_trx_rec.pay_element_type_id := l_element_type_id;
3294 l_pmt_trx_rec.quota_id := wksht.quota_id;
3295 --R12
3296 l_pmt_trx_rec.org_id := l_payrun_rec.org_id;
3297 l_pmt_trx_rec.object_version_number := 1;
3298
3299 cn_pmt_trans_pkg.insert_record(l_pmt_trx_rec);
3300
3301 END IF;
3302 END LOOP; -- worksheets with pe
3303
3304 -- After Cntpmtrb.pls 115.12.1158.11, cn_payment_transaction cannot create
3305 -- record with null quota_id, so remove code for handle null quota_id
3306
3307 -- call sync_accum for this salesrep. 09-22-03 BUG 3151860
3308 FOR l_sync_accum IN sync_accum(each_srp.salesrep_id,l_payrun_rec.pay_period_id, l_payrun_rec.org_id) LOOP
3309 cn_srp_periods_pvt.Sync_Accum_Balances_Start_Pd
3310 (p_salesrep_id => each_srp.salesrep_id,
3311 --R12
3312 p_org_id => l_payrun_rec.org_id,
3313 p_credit_type_id => -1000,
3314 p_role_id => l_sync_accum.role_id,
3315 p_start_period_id => l_payrun_rec.pay_period_id);
3316 END LOOP;
3317
3318 END LOOP; -- each srp loop
3319
3320 cn_payment_security_pvt.paid_payrun_audit
3321 (p_payrun_id => p_payrun_id,
3322 x_return_status => x_return_status,
3323 x_msg_count => x_msg_count,
3324 x_msg_data => x_msg_data);
3325 if x_return_status <> FND_API.G_RET_STS_SUCCESS then
3326 RAISE FND_API.G_EXC_ERROR;
3327 end if;
3328
3329 -- use if AP / Payroll integration has been enabled.
3330 IF l_payables_flag = 'Y'
3331 THEN
3332 -- Populate ccid's in payment worksheets (already done)
3333 /* IF (populate_ccids
3334 (p_payrun_id => p_payrun_id,
3335 p_loading_status => x_loading_status,
3336 x_loading_status => x_loading_status
3337 )) = fnd_api.g_true
3338 THEN
3339 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3340 END IF;
3341 */
3342
3343 -- populate ap interface
3344 IF (populate_ap_interface
3345 (p_payrun_id => p_payrun_id,
3346 p_loading_status => x_loading_status,
3347 x_loading_status => x_loading_status
3348 )) = fnd_api.g_true
3349 THEN
3350 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3351 END IF;
3352
3353 END IF;
3354 x_loading_status := 'CN_UPDATED';
3355
3356 -- Payroll Integration Start here
3357 -- Added on 02/19/01
3358 -- Kumar Sivasankaran
3359 IF l_payroll_flag = 'Y' THEN
3360 BUILD_BEE_API
3361 (x_return_status => x_return_status,
3362 x_msg_count => x_msg_count,
3363 x_msg_data => x_msg_data,
3364 p_payrun_id => p_payrun_id,
3365 p_loading_status => x_loading_status,
3366 x_loading_status => x_loading_status);
3367
3368 IF x_return_status <> FND_API.g_ret_sts_success THEN
3369 RAISE FND_API.G_EXC_ERROR;
3370 END IF;
3371 END IF;
3372
3373 OPEN get_payrun;
3374 FETCH get_payrun INTO l_payrun_rec;
3375 CLOSE get_payrun;
3376
3377 fnd_message.set_name('CN', 'CN_PMT_UPD_NOTE');
3378 fnd_message.set_token('NEW', l_payrun_rec.statusmeaning);
3379 fnd_message.set_token('OLD', l_status_meaning);
3380 l_note_msg := fnd_message.get;
3381 jtf_notes_pub.create_note
3382 (p_api_version => 1.0,
3383 x_return_status => x_return_status,
3384 x_msg_count => x_msg_count,
3385 x_msg_data => x_msg_data,
3386 p_source_object_id => p_payrun_id,
3387 p_source_object_code => 'CN_PAYRUNS',
3388 p_notes => l_note_msg,
3389 p_notes_detail => l_note_msg,
3390 p_note_type => 'CN_SYSGEN', -- for system generated
3391 x_jtf_note_id => l_note_id -- returned
3392 );
3393
3394
3395 -- End of API body
3396 -- Standard check of p_commit.
3397 IF FND_API.To_Boolean( p_commit ) THEN
3398 COMMIT WORK;
3399 END IF;
3400
3401 --
3402 -- Standard call to get message count and if count is 1, get message info.
3403 --
3404
3405 FND_MSG_PUB.Count_And_Get
3406 (
3407 p_count => x_msg_count ,
3408 p_data => x_msg_data ,
3409 p_encoded => FND_API.G_FALSE
3410 );
3411
3412 EXCEPTION
3413 WHEN FND_API.G_EXC_ERROR THEN
3414 ROLLBACK TO Pay_Payrun;
3415 x_return_status := FND_API.G_RET_STS_ERROR ;
3416 FND_MSG_PUB.Count_And_Get
3417 (
3418 p_count => x_msg_count ,
3419 p_data => x_msg_data ,
3420 p_encoded => FND_API.G_FALSE
3421 );
3422 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3423 ROLLBACK TO Pay_Payrun;
3424 x_loading_status := 'UNEXPECTED_ERR';
3425 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3426 FND_MSG_PUB.Count_And_Get
3427 (
3428 p_count => x_msg_count ,
3429 p_data => x_msg_data ,
3430 p_encoded => FND_API.G_FALSE
3431 );
3432 WHEN OTHERS THEN
3433 ROLLBACK TO Pay_Payrun;
3434 x_loading_status := 'UNEXPECTED_ERR';
3435 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3436 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3437 THEN
3438 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME ,l_api_name );
3439 END IF;
3440 FND_MSG_PUB.Count_And_Get
3441 (
3442 p_count => x_msg_count ,
3443 p_data => x_msg_data ,
3444 p_encoded => FND_API.G_FALSE
3445 );
3446 END Pay_Payrun;
3447
3448 --============================================================================
3449 --Name : delete_payrun_conc
3450 --Description : Procedure which will be used as the executable for the
3451 -- : concurrent program. delete payrun
3452 --
3453 --============================================================================
3454 PROCEDURE delete_payrun_conc
3455 ( errbuf OUT NOCOPY VARCHAR2,
3456 retcode OUT NOCOPY NUMBER ,
3457 p_name cn_payruns.name%TYPE,
3458 --R12
3459 p_org_name hr_operating_units.name%TYPE) IS
3460
3461 l_proc_audit_id NUMBER;
3462 l_return_status VARCHAR2(1000);
3463 l_msg_data VARCHAR2(2000);
3464 l_msg_count NUMBER;
3465 l_loading_status VARCHAR2(1000);
3466 l_status VARCHAR2(2000);
3467
3468 l_payrun_id NUMBER;
3469
3470
3471 Cursor get_payrun_id_curs IS
3472 select cp.payrun_id , cp.org_id,cp.OBJECT_VERSION_NUMBER
3473 from cn_payruns cp,
3474 --R12
3475 hr_operating_units hou
3476 where cp.name = p_name
3477 and cp.org_id = hou.organization_id
3478 and hou.name = p_org_name;
3479
3480 --R12
3481 l_org_id cn_payruns.org_id%TYPE;
3482 l_obj cn_payruns.object_version_number%type;
3483
3484 BEGIN
3485
3486 retcode := 0;
3487 -- Initial message list
3488 FND_MSG_PUB.initialize;
3489
3490 -- get payrun id
3491 open get_payrun_id_curs;
3492 fetch get_payrun_id_curs into l_payrun_id, l_org_id,l_obj;
3493 close get_payrun_id_curs;
3494
3495 cn_message_pkg.begin_batch
3496 ( x_process_type => 'DPRUN',
3497 x_process_audit_id => l_proc_audit_id,
3498 x_parent_proc_audit_id => l_proc_audit_id,
3499 x_request_id => NULL,
3500 --R12
3501 p_org_id => l_org_id
3502 );
3503
3504 cn_message_pkg.debug('***************************************************');
3505 cn_message_pkg.debug('Delete Payrun');
3506
3507 --call the create worksheet api
3508 CN_Payrun_PVT.delete_payrun
3509 (p_api_version => 1.0,
3510 p_init_msg_list => fnd_api.g_true,
3511 p_commit => fnd_api.g_false,
3512 p_validation_level => fnd_api.g_valid_level_full,
3513 x_return_status => l_return_status,
3514 x_msg_count => l_msg_count,
3515 x_msg_data => l_msg_data,
3516 p_payrun_id => l_payrun_id,
3517 p_validation_only => 'N',
3518 x_status => l_status,
3519 x_loading_status => l_loading_status );
3520
3521 IF l_return_status <> FND_API.g_ret_sts_success
3522 THEN
3523 retcode := 2;
3524 errbuf := FND_MSG_PUB.get(p_msg_index => fnd_msg_pub.G_LAST,
3525 p_encoded => FND_API.G_FALSE);
3526 cn_message_pkg.debug('Error for delete payrun : '||errbuf);
3527 ELSE
3528 COMMIT;
3529 END IF;
3530
3531 END delete_payrun_conc;
3532 --============================================================================
3533 END CN_Payrun_PVT;