[Home] [Help]
PACKAGE BODY: APPS.CN_PAYMENT_SECURITY_PVT
Source
1 PACKAGE BODY cn_payment_security_pvt AS
2 -- $Header: cnvpmscb.pls 120.20 2006/05/03 13:07:12 rnagired ship $
3 g_pkg_name CONSTANT VARCHAR2 (30) := 'CN_PAYMENT_SECURITY_PVT';
4 g_file_name CONSTANT VARCHAR2 (12) := 'cnvpmscb.pls';
5
6
7 PROCEDURE pmt_raise_event(
8 p_type VARCHAR2,
9 p_event_name VARCHAR2,
10 p_payrun_id NUMBER,
11 p_salesrep_id NUMBER := NULL )
12 IS
13 l_obj_id NUMBER := -1 ;
14 l_key VARCHAR2(1000);
15 l_list wf_parameter_list_t;
16 l_event_name VARCHAR2(1000);
17 g_evt_prefix varchar2(1000) := 'oracle.apps.cn.payment.paysheet.' ;
18 l_ovn NUMBER := -1;
19 BEGIN
20 IF p_type = 'WORKSHEET' THEN
21 SELECT payment_worksheet_id, object_version_number
22 INTO l_obj_id, l_ovn
23 FROM cn_payment_worksheets
24 WHERE payrun_id = p_payrun_id
25 AND salesrep_id = p_salesrep_id
26 AND quota_id IS NULL ;
27
28 l_key := p_event_name || '-' || l_obj_id || '-' || l_ovn ;
29 wf_event.AddParameterToList('PAYRUN_ID' , p_payrun_id, l_list);
30 wf_event.AddParameterToList('SALESREP_ID', p_salesrep_id, l_list);
31 wf_event.AddParameterToList('WORKSHEET_ID', l_obj_id, l_list) ;
32
33 ELSIF p_type = 'PAYRUN' THEN
34 SELECT payrun_id, object_version_number
35 INTO l_obj_id, l_ovn
36 FROM cn_payruns
37 WHERE payrun_id = p_payrun_id ;
38
39 l_key := p_event_name || '-' || l_obj_id || '-' || l_ovn ;
40 wf_event.AddParameterToList('PAYRUN_ID' , p_payrun_id, l_list);
41 END IF ;
42
43 IF p_type IN ('PAYRUN', 'WORKSHEET') THEN
44 l_event_name := g_evt_prefix || p_event_name ;
45 -- Raise Event
46 wf_event.raise
47 (p_event_name => l_event_name,
48 p_event_key => l_key,
49 p_parameters => l_list);
50 l_list.DELETE;
51 END IF ;
52 END ;
53
54 -- Start of comments
55 -- API name : get_pay_by_mode
56 -- Type : Public.
57 -- Function : Pay By Mode for the payrun
58 -- Pre-reqs : None.
59 -- Parameters :
60 -- IN :
61 -- p_payrun_id IN NUMBER
62 -- OUT :
63 -- End of comments
64 FUNCTION get_pay_by_mode (p_payrun_id IN NUMBER)
65 RETURN VARCHAR2
66 IS
67 l_ret_val varchar2(1) ;
68 BEGIN
69 select payrun_mode
70 into l_ret_val
71 from cn_payruns
72 where payrun_id = p_payrun_id ;
73
74 IF l_ret_val NOT IN ('Y','N')
75 THEN
76 -- resource not exist for this user
77 fnd_message.set_name ('CN', 'CN_INVALID_PAYBYMODE');
78 fnd_msg_pub.ADD;
79 RAISE fnd_api.g_exc_error;
80 END IF ;
81 RETURN l_ret_val ;
82 END;
83
84
85 -- Start of comments
86 -- API name : Is_Superuser
87 -- Type : Private.
88 -- Function : Return 1 if current FND user is a super user in
89 -- payment administartive hierarchy
90 -- Pre-reqs : None.
91 -- Parameters :
92 -- IN :
93 -- p_period_id IN NUMBER
94 -- OUT :
95 -- Version : Current version 1.0
96 -- Notes : Return 1 if current fnd user is root node in
97 -- Payment administrative hierarchy
98 --
99 -- End of comments
100 FUNCTION is_superuser (
101 p_period_id IN NUMBER,
102 p_org_id IN NUMBER
103 )
104 RETURN NUMBER
105 IS
106 l_tmp NUMBER := 0;
107 l_resource_id jtf_rs_resource_extns.resource_id%TYPE;
108 BEGIN
109 l_resource_id := NULL;
110
111 BEGIN
112 SELECT resource_id
113 INTO l_resource_id
114 FROM jtf_rs_resource_extns
115 WHERE user_id = fnd_global.user_id;
116 EXCEPTION
117 WHEN NO_DATA_FOUND
118 THEN
119 -- resource not exist for this user
120 fnd_message.set_name ('CN', 'CN_USER_RESOURCE_NF');
121 fnd_msg_pub.ADD;
122 RAISE fnd_api.g_exc_error;
123 END;
124
125 -- Get number of parent_resource for current resource
126 SELECT SUM (DECODE (resource_id, parent_resource_id, 0, 1))
127 INTO l_tmp
128 FROM
129 -- check if user is in analyst hierarchy in this period,
130 -- if view empty, not exist and not a super user, l_tmp will become NULL
131 (SELECT m1.parent_resource_id,
132 m1.resource_id
133 FROM cn_period_statuses pr,
134 jtf_rs_group_usages u1,
135 jtf_rs_rep_managers m1
136 WHERE p_period_id IS NOT NULL
137 AND pr.period_id = p_period_id
138 AND pr.org_id=p_org_id
139 AND u1.USAGE = 'COMP_PAYMENT'
140 AND ((m1.start_date_active <= pr.end_date) AND (pr.start_date <= NVL (m1.end_date_active, pr.start_date)))
141 AND u1.GROUP_ID = m1.GROUP_ID
142 AND m1.resource_id = l_resource_id
143 AND m1.hierarchy_type IN ('MGR_TO_MGR', 'MGR_TO_REP')
144 AND m1.CATEGORY <> 'TBH') v1;
145
146 IF l_tmp IS NULL OR l_tmp > 0
147 THEN
148 RETURN 0;
149 ELSE
150 RETURN 1;
151 END IF;
152 EXCEPTION
153 WHEN NO_DATA_FOUND
154 THEN
155 RETURN 0;
156 END is_superuser;
157
158 -- Start of comments
159 -- API name : Is_Manager
160 -- Type : Private.
161 -- Function : Return 1 if current FND user is a manager in
162 -- payment administartive hierarchy
163 -- Pre-reqs : None.
164 -- Parameters :
165 -- IN :
166 -- p_period_id IN NUMBER
167 -- OUT :
168 -- Version : Current version 1.0
169 -- Notes : Return 1 if current fnd user is a manager in
170 -- Payment administrative hierarchy
171 --
172 -- End of comments
173 FUNCTION is_manager (
174 p_period_id IN NUMBER,
175 p_org_id IN NUMBER
176 )
177 RETURN NUMBER
178 IS
179 l_tmp NUMBER;
180 l_resource_id jtf_rs_resource_extns.resource_id%TYPE;
181 BEGIN
182 l_resource_id := NULL;
183
184 BEGIN
185 SELECT resource_id
186 INTO l_resource_id
187 FROM jtf_rs_resource_extns
188 WHERE user_id = fnd_global.user_id;
189 EXCEPTION
190 WHEN NO_DATA_FOUND
191 THEN
192 -- resource not exist for this user
193 fnd_message.set_name ('CN', 'CN_USER_RESOURCE_NF');
194 fnd_msg_pub.ADD;
195 RAISE fnd_api.g_exc_error;
196 END;
197
198 SELECT 1
199 INTO l_tmp
200 FROM DUAL
201 WHERE EXISTS (
202 SELECT 1
203 FROM cn_period_statuses pr,
204 jtf_rs_group_usages u1,
205 jtf_rs_rep_managers m1
206 WHERE p_period_id IS NOT NULL
207 AND pr.period_id = p_period_id
208 AND pr.org_id=p_org_id
209 AND u1.USAGE = 'COMP_PAYMENT'
210 AND ((m1.start_date_active <= pr.end_date) AND (pr.start_date <= NVL (m1.end_date_active, pr.start_date)))
211 AND u1.GROUP_ID = m1.GROUP_ID
212 AND m1.parent_resource_id = l_resource_id
213 AND m1.hierarchy_type IN ('MGR_TO_MGR', 'MGR_TO_REP')
214 AND m1.CATEGORY <> 'TBH');
215
216 RETURN 1;
217 EXCEPTION
218 WHEN NO_DATA_FOUND
219 THEN
220 RETURN 0;
221 END is_manager;
222
223 --
224 -- Procedure : Paid_Payrun_Audit
225 -- This procedue will update payrun status to paid, insert audit record
226 -- into cn_reasons, update records in cn_pay_approval_flow
227 -- Should call this procedure at the end of pay_payrun procedure
228 --
229 PROCEDURE paid_payrun_audit (
230 p_payrun_id IN NUMBER,
231 x_return_status OUT NOCOPY VARCHAR2,
232 x_msg_count OUT NOCOPY NUMBER,
233 x_msg_data OUT NOCOPY VARCHAR2
234 )
235 IS
236 l_api_name CONSTANT VARCHAR2 (30) := 'Paid_Payrun_Audit';
237 l_loading_status VARCHAR2 (30);
238 l_note_id NUMBER;
239 l_msg_name VARCHAR2(200);
240 l_note_msg VARCHAR2(240);
241
242 CURSOR c_wksht_csr
243 IS
244 SELECT payment_worksheet_id
245 FROM cn_payment_worksheets
246 WHERE payrun_id = p_payrun_id;
247
248 --R12
249 l_ovn NUMBER;
250 l_has_access BOOLEAN;
251
252 CURSOR getobj
253 IS
254 SELECT object_version_number
255 FROM cn_payruns
256 WHERE payrun_id = p_payrun_id;
257 BEGIN
258 -- Initialize API return status to success
259 x_return_status := fnd_api.g_ret_sts_success;
260
261 OPEN getobj;
262
263 FETCH getobj
264 INTO l_ovn;
265
266 --Added for R12 payment security check begin.
267 l_has_access := get_security_access (g_type_payrun, g_access_payrun_pay);
268
269 IF (l_has_access = FALSE)
270 THEN
271 RAISE fnd_api.g_exc_error;
272 END IF;
273
274 fnd_message.set_name('CN', 'CN_PAYSHEET_PAY_NOTE');
275 l_note_msg := fnd_message.get;
276
277 -- for each worksheet in this payrun, add audit history to 'Pay Payrun'
278 FOR l_wksht_csr IN c_wksht_csr
279 LOOP
280
281 jtf_notes_pub.create_note
282 (p_api_version => 1.0,
283 x_return_status => x_return_status,
284 x_msg_count => x_msg_count,
285 x_msg_data => x_msg_data,
286 p_source_object_id => l_wksht_csr.payment_worksheet_id,
287 p_source_object_code => 'CN_PAYMENT_WORKSHEETS',
288 p_notes => l_note_msg,
289 p_notes_detail => l_note_msg,
290 p_note_type => 'CN_SYSGEN', -- for system generated
291 x_jtf_note_id => l_note_id -- returned
292 );
293
294 END LOOP;
295
296 -- call cn_pay_approval_flow_pvt.pay_payrun
297 cn_pay_approval_flow_pvt.pay_payrun (p_api_version => 1.0,
298 x_return_status => x_return_status,
299 x_msg_count => x_msg_count,
300 x_msg_data => x_msg_data,
301 p_payrun_id => p_payrun_id
302 );
303
304 IF x_return_status <> fnd_api.g_ret_sts_success
305 THEN
306 RAISE fnd_api.g_exc_error;
307 END IF;
308
309 -- update payrun status='PAID'
310 cn_payruns_pkg.UPDATE_RECORD (x_payrun_id => p_payrun_id,
311 x_status => 'PAID',
312 x_last_updated_by => fnd_global.user_id,
313 x_last_update_date => SYSDATE,
314 x_last_update_login => fnd_global.login_id,
315 x_object_version_number => l_ovn
316 );
317
318 -- raise wf event
319 pmt_raise_event(
320 p_type => 'PAYRUN',
321 p_event_name => 'pay' ,
322 p_payrun_id => p_payrun_id ) ;
323
324 EXCEPTION
325 WHEN fnd_api.g_exc_error
326 THEN
327 x_return_status := fnd_api.g_ret_sts_error;
328 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
329 WHEN OTHERS
330 THEN
331 x_return_status := fnd_api.g_ret_sts_unexp_error;
332
333 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error)
334 THEN
335 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
336 END IF;
337
338 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
339 END paid_payrun_audit;
340
341 --
342 -- Procedure : Payrun_Audit
343 -- Procedure to update payrun status and enter audit info into cn_reasons
344 --
345 PROCEDURE payrun_audit (
346 p_payrun_id IN NUMBER,
347 p_action IN VARCHAR2,
348 x_return_status OUT NOCOPY VARCHAR2,
349 x_msg_count OUT NOCOPY NUMBER,
350 x_msg_data OUT NOCOPY VARCHAR2
351 )
352 IS
353 l_api_name CONSTANT VARCHAR2 (30) := 'Payrun_Audit';
354 l_new_status cn_payruns.status%TYPE := NULL;
355 l_loading_status VARCHAR2 (30);
356 l_ovn NUMBER;
357 l_has_access BOOLEAN;
358 l_event_name VARCHAR2 (30);
359 CURSOR getobj
360 IS
361 SELECT object_version_number
362 FROM cn_payruns
363 WHERE payrun_id = p_payrun_id;
364 BEGIN
365 -- Initialize API return status to success
366 x_return_status := fnd_api.g_ret_sts_success;
367
368 OPEN getobj;
369
370 FETCH getobj
371 INTO l_ovn;
372
373 -- pay apyrun should call paid_payrun_audit
374 IF p_action <> 'PAY'
375 THEN
376
377 -- update payrun status
378 IF p_action = 'FREEZE'
379 THEN
380 --Added for R12 payment security check begin.
381 l_has_access := get_security_access (g_type_payrun, g_access_payrun_freeze);
382
383 IF (l_has_access = FALSE)
384 THEN
385 RAISE fnd_api.g_exc_error;
386 END IF;
387
388 --Added for R12 payment security check end.
389 l_new_status := 'FROZEN';
390 l_event_name := 'freeze' ;
391
392 ELSIF p_action = 'UNFREEZE' OR p_action = 'REFRESH' OR p_action = 'REMOVE'
393 THEN
397 l_has_access := get_security_access (g_type_payrun, g_access_payrun_unfreeze);
394 --Added for R12 payment security check begin.
395 IF p_action = 'UNFREEZE'
396 THEN
398
399 IF (l_has_access = FALSE)
400 THEN
401 RAISE fnd_api.g_exc_error;
402 END IF;
403 l_event_name := 'unfreeze' ;
404
405 ELSIF p_action = 'REFRESH'
406 THEN
407 l_has_access := get_security_access (g_type_payrun, g_access_payrun_refresh);
408
409 IF (l_has_access = FALSE)
410 THEN
411 RAISE fnd_api.g_exc_error;
412 END IF;
413 l_event_name := 'refresh' ;
414
415 ELSIF p_action = 'REMOVE'
416 THEN
417 l_has_access := get_security_access (g_type_payrun, g_access_payrun_delete);
418
419 IF (l_has_access = FALSE)
420 THEN
421 RAISE fnd_api.g_exc_error;
422 END IF;
423 l_event_name := 'delete' ;
424
425 END IF;
426
427 --Added for R12 payment security check end.
428 l_new_status := 'UNPAID';
429 END IF;
430
431 cn_payruns_pkg.UPDATE_RECORD (x_payrun_id => p_payrun_id,
432 x_status => l_new_status,
433 x_last_updated_by => fnd_global.user_id,
434 x_last_update_date => SYSDATE,
435 x_last_update_login => fnd_global.login_id,
436 x_object_version_number => l_ovn
437 );
438 -- raise wf event
439 pmt_raise_event(
440 p_type => 'PAYRUN',
441 p_event_name => l_event_name ,
442 p_payrun_id => p_payrun_id ) ;
443
444 END IF;
445 EXCEPTION
446 WHEN fnd_api.g_exc_error
447 THEN
448 x_return_status := fnd_api.g_ret_sts_error;
449 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
450 WHEN OTHERS
451 THEN
452 x_return_status := fnd_api.g_ret_sts_unexp_error;
453
454 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error)
455 THEN
456 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
457 END IF;
458
459 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
460 END payrun_audit;
461
462
463 --===================================================================================
464 -- Procedure : Worksheet_Audit
465 -- Procedure to update worksheet status and enter audit info into notes
466 -- This procedure expects validation to have been done already (worksheet_action p_do_audit => fnd_api.g_false)
467 --===================================================================================
468 PROCEDURE worksheet_audit (
469 p_worksheet_id IN NUMBER,
470 p_payrun_id IN NUMBER,
471 p_salesrep_id IN NUMBER,
472 p_action IN VARCHAR2,
473 p_do_approval_flow IN VARCHAR2 := fnd_api.g_true,
474 x_return_status OUT NOCOPY VARCHAR2,
475 x_msg_count OUT NOCOPY NUMBER,
476 x_msg_data OUT NOCOPY VARCHAR2
477 )
478 IS
479 l_api_name CONSTANT VARCHAR2 (30) := 'Worksheet_Audit';
480 l_new_status cn_payment_worksheets.worksheet_status%TYPE := NULL;
481 l_loading_status VARCHAR2 (30);
482 l_org_id NUMBER;
483 l_has_access BOOLEAN;
484 l_ovn NUMBER ;
485 l_note_msg VARCHAR2(240);
486 l_note_id NUMBER;
487 l_msg_name VARCHAR2(200);
488
489 l_event_name VARCHAR2(80);
490
491 BEGIN
492 -- Initialize API return status to success
493 x_return_status := fnd_api.g_ret_sts_success;
494
495 -- update worksheet status
496 IF p_action = 'LOCK'
497 THEN
498 l_msg_name:='CN_PAYSHEET_LOCKED_NOTE';
499 l_new_status := 'LOCKED';
500 l_event_name := 'lock' ;
501
502 ELSIF p_action = 'UNLOCK' OR p_action = 'REFRESH' OR p_action = 'REMOVE' OR p_action = 'RELEASE_HOLD'
503 OR p_action = 'RESET_TO_UNPAID'
504 THEN
505 IF p_action = 'UNLOCK'
506 THEN
507 l_msg_name:='CN_PAYSHEET_UNLOCK_NOTE';
508 l_event_name := 'unlock' ;
509
510 ELSIF p_action = 'REFRESH'
511 THEN
512 l_msg_name:='CN_PAYSHEET_REFRESH_NOTE';
513 l_event_name := 'refresh' ;
514
515 ELSIF p_action = 'REMOVE'
516 THEN
517 l_msg_name:='CN_PAYESHEET_REMOVE_NOTE';
521 THEN
518 l_event_name := 'delete' ;
519
520 ELSIF p_action = 'RELEASE_HOLD'
522 l_msg_name:='CN_PAYSHEET_RELEASE_HOLD_NOTE';
523 l_event_name := 'release' ;
524
525 ELSIF p_action = 'RESET_TO_UNPAID'
526 THEN
527 l_msg_name:='CN_PAYSHEET_RESET_NOTE';
528 l_event_name := 'release' ;
529
530 END IF;
531
532 l_new_status := 'UNPAID';
533
534 ELSIF p_action IN ( 'HOLD_ALL', 'RELEASE_ALL' )
535 THEN
536 IF p_action = 'HOLD_ALL' THEN
537 l_msg_name:='CN_PAYSHEET_HOLDALL_NOTE';
538 l_event_name := 'holdall' ;
539 ELSE
540 l_msg_name:='CN_PAYSHEET_RELEASEALL_NOTE';
541 l_event_name := 'releaseall' ;
542 END IF ;
543 l_new_status := 'PROCESSING' ;
544
545 ELSIF p_action = 'SUBMIT'
546 THEN
547 l_new_status := 'SUBMITTED';
548
549 IF p_do_approval_flow = fnd_api.g_true
550 THEN
551 cn_pay_approval_flow_pvt.submit_worksheet (p_api_version => 1.0,
552 x_return_status => x_return_status,
553 x_msg_count => x_msg_count,
554 x_msg_data => x_msg_data,
555 p_worksheet_id => p_worksheet_id
556 );
557
558 IF x_return_status <> fnd_api.g_ret_sts_success
559 THEN
560 RAISE fnd_api.g_exc_error;
561 END IF;
562 END IF;
563 l_msg_name:='CN_PAYSHEET_SUBMIT_NOTE';
564 l_event_name := 'submit' ;
565
566 ELSIF p_action = 'APPROVE'
567 THEN
568 l_new_status := 'APPROVED';
569
570 IF p_do_approval_flow = fnd_api.g_true
571 THEN
572 cn_pay_approval_flow_pvt.approve_worksheet (p_api_version => 1.0,
573 x_return_status => x_return_status,
574 x_msg_count => x_msg_count,
575 x_msg_data => x_msg_data,
576 p_worksheet_id => p_worksheet_id
577 );
578
579 IF x_return_status <> fnd_api.g_ret_sts_success
580 THEN
581 RAISE fnd_api.g_exc_error;
582 END IF;
583 END IF;
584 l_msg_name:='CN_PAYESHEET_APPROVE_NOTE';
585 l_event_name := 'approve' ;
586
587 ELSIF p_action = 'REJECT'
588 THEN
589 l_new_status := 'UNPAID';
590
591 IF p_do_approval_flow = fnd_api.g_true
592 THEN
593 cn_pay_approval_flow_pvt.reject_worksheet (p_api_version => 1.0,
594 x_return_status => x_return_status,
595 x_msg_count => x_msg_count,
596 x_msg_data => x_msg_data,
597 p_worksheet_id => p_worksheet_id
598 );
599
600 IF x_return_status <> fnd_api.g_ret_sts_success
601 THEN
602 RAISE fnd_api.g_exc_error;
603 END IF;
604 END IF;
605 l_msg_name:='CN_PAYESHEET_REJECT_NOTE';
606 l_event_name := 'reject' ;
607
608 END IF;
609
610 cn_payment_worksheets_pkg.UPDATE_STATUS (p_salesrep_id => p_salesrep_id,
611 p_payrun_id => p_payrun_id,
612 p_worksheet_status => l_new_status
613 );
614
615 fnd_message.set_name('CN', l_msg_name);
616 l_note_msg := fnd_message.get;
617 IF p_action <> 'REMOVE' THEN
618 jtf_notes_pub.create_note
619 (p_api_version => 1.0,
620 x_return_status => x_return_status,
621 x_msg_count => x_msg_count,
622 x_msg_data => x_msg_data,
623 p_source_object_id => p_worksheet_id,
624 p_source_object_code => 'CN_PAYMENT_WORKSHEETS',
625 p_notes => l_note_msg,
626 p_notes_detail => l_note_msg,
627 p_note_type => 'CN_SYSGEN', -- for system generated
628 x_jtf_note_id => l_note_id -- returned
629 );
630 ELSE
631 jtf_notes_pub.create_note
632 (p_api_version => 1.0,
633 x_return_status => x_return_status,
637 p_source_object_code => 'CN_PAYRUNS',
634 x_msg_count => x_msg_count,
635 x_msg_data => x_msg_data,
636 p_source_object_id => p_payrun_id,
638 p_notes => l_note_msg,
639 p_notes_detail => l_note_msg,
640 p_note_type => 'CN_SYSGEN', -- for system generated
641 x_jtf_note_id => l_note_id -- returned
642 );
643 END IF;
644
645 pmt_raise_event(
646 p_type => 'WORKSHEET',
647 p_event_name => l_event_name,
648 p_payrun_id => p_payrun_id,
649 p_salesrep_id => p_salesrep_id) ;
650
651 EXCEPTION
652 WHEN fnd_api.g_exc_error
653 THEN
654 x_return_status := fnd_api.g_ret_sts_error;
655 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
656 WHEN OTHERS
657 THEN
658 x_return_status := fnd_api.g_ret_sts_unexp_error;
659
660 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error)
661 THEN
662 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
663 END IF;
664
665 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
666 END worksheet_audit;
667
668 -- Start of comments
669 -- API name : Payrun_Action
670 -- Type : Private.
671 -- Function : Procedure to check if the payrun action is valid.
672 -- Pre-reqs : None.
673 -- Parameters :
674 -- IN : p_api_version IN NUMBER Required
675 -- p_init_msg_list IN VARCHAR2 Optional
676 -- Default = FND_API.G_FALSE
677 -- p_commit IN VARCHAR2 Optional
678 -- Default = FND_API.G_FALSE
679 -- p_validation_level IN NUMBER Optional
680 -- Default = FND_API.G_VALID_LEVEL_FULL
681 -- p_payrun_id IN NUMBER
682 -- p_action IN VARCHAR2
683 -- OUT : x_return_status OUT VARCHAR2(1)
684 -- x_msg_count OUT NUMBER
685 -- x_msg_data OUT VARCHAR2(2000)
686 -- Version : Current version 1.0
687 --
688 -- Notes : Note text
689 --
690 -- End of comments
691 PROCEDURE payrun_action (
692 p_api_version IN NUMBER,
693 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
694 p_commit IN VARCHAR2 := fnd_api.g_false,
695 p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
696 x_return_status OUT NOCOPY VARCHAR2,
697 x_msg_count OUT NOCOPY NUMBER,
698 x_msg_data OUT NOCOPY VARCHAR2,
699 p_payrun_id IN NUMBER,
700 p_action IN VARCHAR2,
701 p_do_audit IN VARCHAR2 := fnd_api.g_true
702 )
703 IS
704 l_api_name CONSTANT VARCHAR2 (30) := 'Payrun_Action';
705 l_api_version CONSTANT NUMBER := 1.0;
706 l_period_id cn_period_statuses.period_id%TYPE;
707 l_payrun_status cn_payruns.status%TYPE;
708 l_tmp NUMBER;
709 l_temp NUMBER ;
710
711 CURSOR c
712 IS
713 SELECT status
714 FROM cn_payruns
715 WHERE payrun_id = p_payrun_id
716 FOR UPDATE OF status NOWAIT
717 ;
718
719 tlinfo c%ROWTYPE;
720
721 CURSOR cw
722 IS
723 SELECT worksheet_status
724 FROM cn_payment_worksheets
725 WHERE payrun_id = p_payrun_id
726 FOR UPDATE OF worksheet_status NOWAIT
727 ;
728
729 tlinfo2 cw%ROWTYPE;
730 err_num NUMBER;
731 l_has_access BOOLEAN;
732 BEGIN
733 -- Standard Start of API savepoint
734 SAVEPOINT payrun_action;
735
736 -- Standard call to check for call compatibility.
737 IF NOT fnd_api.compatible_api_call (l_api_version, p_api_version, l_api_name, g_pkg_name)
738 THEN
739 RAISE fnd_api.g_exc_unexpected_error;
740 END IF;
741
742 -- Initialize message list if p_init_msg_list is set to TRUE.
743 IF fnd_api.to_boolean (p_init_msg_list)
744 THEN
745 fnd_msg_pub.initialize;
746 END IF;
747
748 -- Initialize API return status to success
749 x_return_status := fnd_api.g_ret_sts_success;
750
751 -- API body
752 -- Get payrun information
753 BEGIN
754 SELECT pay_period_id,
755 status
756 INTO l_period_id,
757 l_payrun_status
758 FROM cn_payruns
762 THEN
759 WHERE payrun_id = p_payrun_id;
760 EXCEPTION
761 WHEN NO_DATA_FOUND
763 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
764 THEN
765 fnd_message.set_name ('CN', 'CN_PAYRUN_DOES_NOT_EXIST');
766 fnd_msg_pub.ADD;
767 END IF;
768
769 RAISE fnd_api.g_exc_error;
770 END;
771
772 BEGIN
773 -- lock payrun for update
774 OPEN c;
775
776 FETCH c
777 INTO tlinfo;
778
779 CLOSE c;
780
781 -- lock worksheet for preventing update while updating payrun
782 OPEN cw;
783
784 FETCH cw
785 INTO tlinfo2;
786
787 CLOSE cw;
788 EXCEPTION
789 WHEN OTHERS
790 THEN
791 err_num := SQLCODE;
792
793 IF err_num = -54
794 THEN
795 fnd_message.set_name ('CN', 'CN_INVALID_OBJECT_VERSION');
796 fnd_msg_pub.ADD;
797 RAISE fnd_api.g_exc_error;
798 ELSE
799 RAISE;
800 END IF;
801 END;
802
803 -- Check if user is Super User. Only super user can perform payrun action
804 /* Comment out for R12 payment security
805 IF is_superuser(p_period_id => l_period_id) = 0 THEN
806 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
807 FND_MESSAGE.SET_NAME ('CN','CN_PAYRUN_NOT_SU');
808 FND_MSG_PUB.Add;
809 END IF;
810 RAISE FND_API.G_EXC_ERROR ;
811 END IF;
812 */
813
814 -- cannot perform action on paid payrun
815 IF l_payrun_status = 'PAID'
816 THEN
817 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
818 THEN
819 fnd_message.set_name ('CN', 'CN_PAYRUN_PAID');
820 fnd_msg_pub.ADD;
821 END IF;
822
823 RAISE fnd_api.g_exc_error;
824 END IF;
825
826 -- should never perform any payrun action when one of the worksheets is processing
827 SELECT COUNT(1)
828 INTO l_temp
829 FROM cn_payment_worksheets
830 WHERE worksheet_status IN ('PROCESSING', 'FAILED')
831 AND payrun_id = p_payrun_id
832 AND rownum < 2;
833
834 IF l_temp > 0 THEN
835 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
836 THEN
837 fnd_message.set_name ('CN', 'CN_WKSHT_STILL_PROCESSING');
838 fnd_msg_pub.ADD;
839 END IF;
840 RAISE fnd_api.g_exc_error;
841 END IF;
842
843 -- Check for each action
844 -- p_action = 'REFRESH' OR 'REMOVE' OR 'FREEZE'
845 IF p_action = 'REFRESH' OR p_action = 'REMOVE' OR p_action = 'FREEZE'
846 THEN
847 --Added for R12 payment security check begin.
848 IF p_action = 'REFRESH'
849 THEN
850 l_has_access := get_security_access (g_type_payrun, g_access_payrun_refresh);
851
852 IF (l_has_access = FALSE)
853 THEN
854 RAISE fnd_api.g_exc_error;
855 END IF;
856 ELSIF p_action = 'REMOVE'
857 THEN
858 l_has_access := get_security_access (g_type_payrun, g_access_payrun_delete);
859
860 IF (l_has_access = FALSE)
861 THEN
862 RAISE fnd_api.g_exc_error;
863 END IF;
864 ELSIF p_action = 'FREEZE'
865 THEN
866 l_has_access := get_security_access (g_type_payrun, g_access_payrun_freeze);
867
868 IF (l_has_access = FALSE)
869 THEN
870 RAISE fnd_api.g_exc_error;
871 END IF;
872 END IF;
873
874 --Added for R12 payment security check end.
875 IF l_payrun_status <> 'UNPAID'
876 THEN
877 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
878 THEN
879 fnd_message.set_name ('CN', 'CN_PAYRUN_ACTION_UNPAID');
880 fnd_msg_pub.ADD;
881 END IF;
882
883 RAISE fnd_api.g_exc_error;
884 END IF;
885 -- p_action = 'UNFREEZE'
886 ELSIF p_action = 'UNFREEZE'
887 THEN
888 --Added for R12 payment security check begin.
889 l_has_access := get_security_access (g_type_payrun, g_access_payrun_unfreeze);
890
891 IF (l_has_access = FALSE)
892 THEN
893 RAISE fnd_api.g_exc_error;
894 END IF;
895
896 --Added for R12 payment security check end.
897 IF l_payrun_status <> 'FROZEN'
898 THEN
899 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
900 THEN
901 fnd_message.set_name ('CN', 'CN_PAYRUN_ACTION_UNFREEZE');
902 fnd_msg_pub.ADD;
903 END IF;
904
905 RAISE fnd_api.g_exc_error;
906 END IF;
907 -- p_action = 'PAY'
908 ELSIF p_action = 'PAY'
912
909 THEN
910 --Added for R12 payment security check begin.
911 l_has_access := get_security_access (g_type_payrun, g_access_payrun_pay);
913 IF (l_has_access = FALSE)
914 THEN
915 RAISE fnd_api.g_exc_error;
916 END IF;
917
918 --Added for R12 payment security check end.
919 IF NVL (fnd_profile.VALUE ('CN_CHK_WKSHT_STATUS'), 'Y') = 'Y'
920 THEN
921 -- all worksheet should be 'APPROVED' if profile = 'Y'
922 BEGIN
923 SELECT 1
924 INTO l_tmp
925 FROM DUAL
926 WHERE EXISTS (SELECT 1
927 FROM cn_payment_worksheets
928 WHERE payrun_id = p_payrun_id AND worksheet_status <> 'APPROVED');
929
930 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
931 THEN
932 fnd_message.set_name ('CN', 'CN_PAYRUN_ACTION_PAY');
933 fnd_msg_pub.ADD;
934 END IF;
935
936 RAISE fnd_api.g_exc_error;
937 EXCEPTION
938 WHEN NO_DATA_FOUND
939 THEN
940 NULL;
941 END;
942 ELSE
943 IF l_payrun_status <> 'FROZEN'
944 THEN
945 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
946 THEN
947 fnd_message.set_name ('CN', 'CN_PAYRUN_ACTION_PAY_FRZ');
948 fnd_msg_pub.ADD;
949 END IF;
950
951 RAISE fnd_api.g_exc_error;
952 END IF;
953 END IF;
954 ELSE
955 -- invalid p_action
956 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
957 THEN
958 fnd_message.set_name ('CN', 'CN_PAYRUN_ACTION_NOT_EXIST');
959 fnd_msg_pub.ADD;
960 END IF;
961
962 RAISE fnd_api.g_exc_error;
963 END IF;
964
965 IF fnd_api.to_boolean (p_do_audit)
966 THEN
967 -- update audit table and payrun status
968 payrun_audit (p_payrun_id => p_payrun_id,
969 p_action => p_action,
970 x_return_status => x_return_status,
971 x_msg_count => x_msg_count,
972 x_msg_data => x_msg_data
973 );
974
975 IF x_return_status <> fnd_api.g_ret_sts_success
976 THEN
977 RAISE fnd_api.g_exc_error;
978 END IF;
979 END IF;
980
981 -- End of API body.
982 -- Standard check of p_commit.
983 IF fnd_api.to_boolean (p_commit)
984 THEN
985 COMMIT WORK;
986 END IF;
987
988 -- Standard call to get message count and if count is 1, get message info.
989 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
990 EXCEPTION
991 WHEN fnd_api.g_exc_error
992 THEN
993 ROLLBACK TO payrun_action;
994 x_return_status := fnd_api.g_ret_sts_error;
995 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
996 WHEN fnd_api.g_exc_unexpected_error
997 THEN
998 ROLLBACK TO payrun_action;
999 x_return_status := fnd_api.g_ret_sts_unexp_error;
1000 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
1001 WHEN OTHERS
1002 THEN
1003 ROLLBACK TO payrun_action;
1004 x_return_status := fnd_api.g_ret_sts_unexp_error;
1005
1006 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error)
1007 THEN
1008 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
1009 END IF;
1010
1011 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
1012 END payrun_action;
1013
1014 -- Start of comments
1015 -- API name : Worksheet_action
1016 -- Type : Private.
1017 -- Function : Procedure to check if the worksheet action is valid.
1018 -- Pre-reqs : None.
1019 -- Parameters :
1020 -- IN : p_api_version IN NUMBER Required
1021 -- p_init_msg_list IN VARCHAR2 Optional
1022 -- Default = FND_API.G_FALSE
1023 -- p_commit IN VARCHAR2 Optional
1024 -- Default = FND_API.G_FALSE
1025 -- p_validation_level IN NUMBER Optional
1026 -- Default = FND_API.G_VALID_LEVEL_FULL
1027 -- p_worksheet_id IN NUMBER
1028 -- p_action IN VARCHAR2
1029 -- OUT : x_return_status OUT VARCHAR2(1)
1030 -- x_msg_count OUT NUMBER
1031 -- x_msg_data OUT VARCHAR2(2000)
1032 -- Version : Current version 1.0
1033 --
1034 -- Notes : Note text
1035 --
1036 -- End of comments
1040 p_commit IN VARCHAR2 := fnd_api.g_false,
1037 PROCEDURE worksheet_action (
1038 p_api_version IN NUMBER,
1039 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
1041 p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
1042 x_return_status OUT NOCOPY VARCHAR2,
1043 x_msg_count OUT NOCOPY NUMBER,
1044 x_msg_data OUT NOCOPY VARCHAR2,
1045 p_worksheet_id IN NUMBER,
1046 p_action IN VARCHAR2,
1047 p_do_audit IN VARCHAR2 := fnd_api.g_true
1048 )
1049 IS
1050 l_api_name CONSTANT VARCHAR2 (30) := 'Worksheet_action';
1051 l_api_version CONSTANT NUMBER := 1.0;
1052 l_resource_id jtf_rs_resource_extns.resource_id%TYPE;
1053 l_period_id cn_period_statuses.period_id%TYPE;
1054 l_worksheet_rec cn_payment_worksheets%ROWTYPE;
1055 l_payrun_status cn_payruns.status%TYPE;
1056 l_pay_period_id cn_payruns.pay_period_id%TYPE;
1057 l_assigned_to_user_id cn_salesreps.assigned_to_user_id%TYPE := NULL;
1058 l_tmp NUMBER;
1059 l_org_id NUMBER;
1060
1064 IS
1061 CURSOR c (
1062 c_payrun_id cn_payruns.payrun_id%TYPE
1063 )
1065 SELECT status
1066 FROM cn_payruns
1067 WHERE payrun_id = c_payrun_id
1068 --FOR UPDATE OF status NOWAIT
1069 ;
1070
1071 tlinfo c%ROWTYPE;
1072
1073 CURSOR cw
1074 IS
1075 SELECT worksheet_status
1076 FROM cn_payment_worksheets
1077 WHERE payment_worksheet_id = p_worksheet_id
1078 --FOR UPDATE OF worksheet_status NOWAIT
1079 ;
1080
1081 tlinfo2 cw%ROWTYPE;
1082 err_num NUMBER;
1083 l_has_access BOOLEAN;
1084 BEGIN
1085 -- Standard Start of API savepoint
1086 SAVEPOINT worksheet_action;
1087
1088 -- Standard call to check for call compatibility.
1089 IF NOT fnd_api.compatible_api_call (l_api_version, p_api_version, l_api_name, g_pkg_name)
1090 THEN
1091 RAISE fnd_api.g_exc_unexpected_error;
1092 END IF;
1093
1094 -- Initialize message list if p_init_msg_list is set to TRUE.
1095 IF fnd_api.to_boolean (p_init_msg_list)
1096 THEN
1097 fnd_msg_pub.initialize;
1098 END IF;
1099
1100 -- Initialize API return status to success
1101 x_return_status := fnd_api.g_ret_sts_success;
1102
1103 -- API body
1104 -- Get worksheet information
1105 BEGIN
1106 SELECT wk.payrun_id,
1107 wk.salesrep_id,
1108 wk.worksheet_status,
1109 pay.status,
1110 pay.pay_period_id,
1111 s.assigned_to_user_id,
1112 pay.org_id
1113 INTO l_worksheet_rec.payrun_id,
1114 l_worksheet_rec.salesrep_id,
1115 l_worksheet_rec.worksheet_status,
1116 l_payrun_status,
1117 l_pay_period_id,
1118 l_assigned_to_user_id,
1119 l_org_id
1120 FROM cn_payment_worksheets wk,
1121 cn_payruns pay,
1122 cn_salesreps s
1123 WHERE wk.payment_worksheet_id = p_worksheet_id
1124 AND pay.payrun_id = wk.payrun_id
1125 AND s.salesrep_id = wk.salesrep_id
1126 --R12
1127 AND wk.org_id = pay.org_id
1128 AND wk.org_id = s.org_id;
1129 EXCEPTION
1130 WHEN NO_DATA_FOUND
1131 THEN
1132 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
1133 THEN
1134 fnd_message.set_name ('CN', 'CN_WKSHT_DOES_NOT_EXIST');
1135 fnd_msg_pub.ADD;
1136 END IF;
1137
1138 RAISE fnd_api.g_exc_error;
1139 END;
1140
1141 BEGIN
1142 -- lock payrun for preventing update while updating wksht
1143 OPEN c (l_worksheet_rec.payrun_id);
1144
1145 FETCH c
1146 INTO tlinfo;
1147
1148 CLOSE c;
1149
1150 -- lock worksheet for update
1151 OPEN cw;
1152
1153 FETCH cw
1154 INTO tlinfo2;
1155
1156 CLOSE cw;
1157 EXCEPTION
1158 WHEN OTHERS
1159 THEN
1160 err_num := SQLCODE;
1161
1162 IF err_num = -54
1163 THEN
1164 fnd_message.set_name ('CN', 'CN_INVALID_OBJECT_VERSION');
1165 fnd_msg_pub.ADD;
1166 RAISE fnd_api.g_exc_error;
1167 ELSE
1168 RAISE;
1169 END IF;
1170 END;
1171
1172 -- cannot perform action on paid payrun
1173 IF l_payrun_status = 'PAID'
1174 THEN
1175 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
1176 THEN
1177 fnd_message.set_name ('CN', 'CN_PAYRUN_PAID');
1178 fnd_msg_pub.ADD;
1179 END IF;
1180
1181 RAISE fnd_api.g_exc_error;
1182 END IF;
1183
1184 -- Get login user resource_id
1185 l_resource_id := NULL;
1186
1187 BEGIN
1188 SELECT resource_id
1189 INTO l_resource_id
1190 FROM jtf_rs_resource_extns
1191 WHERE user_id = fnd_global.user_id;
1192 EXCEPTION
1193 WHEN NO_DATA_FOUND
1194 THEN
1195 -- resource not exist for this user
1196 fnd_message.set_name ('CN', 'CN_USER_RESOURCE_NF');
1197 fnd_msg_pub.ADD;
1198 RAISE fnd_api.g_exc_error;
1199 END;
1200
1201 -- check if user can access this worksheet when
1202 -- l_assigned_to_user_id is not null
1203 IF l_assigned_to_user_id IS NOT NULL
1204 THEN
1205 -- Bug 3498950 3/8/04 ACHUNG:skip check for super user
1206 IF (is_superuser (l_pay_period_id,l_org_id) = 1)
1207 THEN
1208 NULL;
1209 ELSE
1210 BEGIN
1211 SELECT 1
1212 INTO l_tmp
1213 FROM DUAL
1214 WHERE EXISTS (
1215 SELECT 1
1216 FROM jtf_rs_group_usages u2,
1217 jtf_rs_rep_managers m2,
1218 jtf_rs_resource_extns_vl re2,
1219
1220 -- start inline view
1221 -- get all rows for a login user in jtf_rs_rep_managers
1222 -- with period = p_period_id
1223 (SELECT DISTINCT m1.resource_id,
1227 jtf_rs_group_usages u1,
1224 GREATEST (pr.start_date, m1.start_date_active) start_date,
1225 LEAST (pr.end_date, NVL (m1.end_date_active, pr.end_date)) end_date
1226 FROM cn_period_statuses pr,
1228 jtf_rs_rep_managers m1
1229 WHERE pr.period_id = l_pay_period_id
1230 AND pr.org_id=l_org_id
1231 AND u1.USAGE = 'COMP_PAYMENT'
1232 AND m1.resource_id = l_resource_id
1233 AND ( (m1.start_date_active <= pr.end_date)
1234 AND (pr.start_date <= NVL (m1.end_date_active, pr.start_date))
1235 )
1236 AND u1.GROUP_ID = m1.GROUP_ID
1237 AND m1.parent_resource_id = m1.resource_id
1238 AND m1.hierarchy_type IN ('MGR_TO_MGR', 'REP_TO_REP')
1239 AND m1.CATEGORY <> 'TBH') v3
1240 -- end inlive view v3
1241 WHERE re2.user_id = l_assigned_to_user_id
1242 AND u2.USAGE = 'COMP_PAYMENT'
1243 AND u2.GROUP_ID = m2.GROUP_ID
1244 AND m2.parent_resource_id = v3.resource_id
1245 AND ((m2.start_date_active <= v3.end_date) AND (v3.start_date <= NVL (m2.end_date_active, v3.start_date)))
1246 AND m2.CATEGORY <> 'TBH'
1247 AND m2.hierarchy_type IN ('MGR_TO_MGR', 'MGR_TO_REP', 'REP_TO_REP')
1248 AND m2.resource_id = re2.resource_id);
1249 EXCEPTION
1250 WHEN NO_DATA_FOUND
1251 THEN
1252 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
1253 THEN
1254 fnd_message.set_name ('CN', 'CN_NO_SRP_ACCESS');
1255 fnd_msg_pub.ADD;
1256 END IF;
1257
1258 RAISE fnd_api.g_exc_error;
1259 END;
1260
1261 NULL;
1262 END IF; -- Bug 3498950
1263 END IF;
1264
1265 -- Check for each action
1266 -- p_action = 'REFRESH' OR 'REMOVE' OR 'LOCK' OR 'RELEASE_HOLD'
1267 IF p_action = 'REFRESH' OR p_action = 'REMOVE' OR p_action = 'LOCK' OR p_action = 'RELEASE_HOLD'
1268 THEN
1269 --Added for R12 payment security check begin.
1270 IF p_action = 'REFRESH'
1271 THEN
1272 l_has_access := get_security_access (g_type_wksht, g_access_wksht_refresh);
1273
1274 IF (l_has_access = FALSE)
1275 THEN
1276 RAISE fnd_api.g_exc_error;
1277 END IF;
1278 ELSIF p_action = 'REMOVE'
1279 THEN
1280 l_has_access := get_security_access (g_type_wksht, g_access_wksht_delete);
1281
1282 IF (l_has_access = FALSE)
1283 THEN
1284 RAISE fnd_api.g_exc_error;
1285 END IF;
1286 ELSIF p_action = 'LOCK'
1287 THEN
1288 l_has_access := get_security_access (g_type_wksht, g_access_wksht_lock);
1289
1290 IF (l_has_access = FALSE)
1291 THEN
1292 RAISE fnd_api.g_exc_error;
1293 END IF;
1294 ELSIF p_action = 'RELEASE_HOLD'
1295 THEN
1296 l_has_access := get_security_access (g_type_wksht, g_access_wksht_release_holds);
1297
1298 IF (l_has_access = FALSE)
1299 THEN
1300 RAISE fnd_api.g_exc_error;
1301 END IF;
1302 END IF;
1303
1304 --Added for R12 payment security check end.
1305 IF l_payrun_status <> 'UNPAID' OR l_worksheet_rec.worksheet_status <> 'UNPAID'
1306 THEN
1307 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
1308 THEN
1309 fnd_message.set_name ('CN', 'CN_WKSHT_ACTION_UNPAID');
1310 fnd_msg_pub.ADD;
1311 END IF;
1312
1313 RAISE fnd_api.g_exc_error;
1314 END IF;
1315 ELSIF p_action IN ( 'HOLD_ALL' , 'RELEASE_ALL' )
1316 THEN
1317 l_has_access := get_security_access (g_type_wksht, g_access_wksht_release_holds);
1318
1319 IF (l_has_access = FALSE)
1320 THEN
1321 RAISE fnd_api.g_exc_error;
1322 END IF;
1323
1324 --Added for R12 payment security check end.
1325 IF l_payrun_status <> 'UNPAID' OR l_worksheet_rec.worksheet_status NOT IN ('PROCESSING', 'UNPAID')
1326 THEN
1327 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
1328 THEN
1329 fnd_message.set_name ('CN', 'CN_WKSHT_NOT_PROCESSING');
1330 fnd_msg_pub.ADD;
1331 END IF;
1332
1333 RAISE fnd_api.g_exc_error;
1334 END IF;
1335
1336 ELSIF p_action IN ( 'RESET_TO_UNPAID' )
1337 THEN
1338 l_has_access := get_security_access (g_type_wksht, g_access_wksht_release_holds);
1339
1340 IF (l_has_access = FALSE)
1341 THEN
1342 RAISE fnd_api.g_exc_error;
1343 END IF;
1344
1345 --Added for R12 payment security check end.
1346 IF l_payrun_status <> 'UNPAID' OR (l_worksheet_rec.worksheet_status NOT IN ('PROCESSING','FAILED'))
1347 THEN
1351 fnd_msg_pub.ADD;
1348 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
1349 THEN
1350 fnd_message.set_name ('CN', 'CN_WKSHT_NOT_PROCESSING');
1352 END IF;
1353
1354 RAISE fnd_api.g_exc_error;
1355 END IF;
1356
1357 -- p_action = 'UNLOCK'
1358 ELSIF p_action = 'UNLOCK'
1359 THEN
1360 --Added for R12 payment security check begin.
1361 l_has_access := get_security_access (g_type_wksht, g_access_wksht_unlock);
1362
1363 IF (l_has_access = FALSE)
1364 THEN
1365 RAISE fnd_api.g_exc_error;
1366 END IF;
1367
1368 --Added for R12 payment security check end.
1369 IF l_payrun_status <> 'UNPAID' OR l_worksheet_rec.worksheet_status <> 'LOCKED'
1370 THEN
1371 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
1372 THEN
1373 fnd_message.set_name ('CN', 'CN_WKSHT_ACTION_UNLOCK');
1374 fnd_msg_pub.ADD;
1375 END IF;
1376
1377 RAISE fnd_api.g_exc_error;
1378 END IF;
1379 -- p_action = 'SUBMIT'
1380 ELSIF p_action = 'SUBMIT'
1381 THEN
1382 --Added for R12 payment security check begin.
1383 l_has_access := get_security_access (g_type_wksht, g_access_wksht_submit);
1384
1385 IF (l_has_access = FALSE)
1386 THEN
1387 RAISE fnd_api.g_exc_error;
1388 END IF;
1389
1390 --Added for R12 payment security check end.
1391 IF l_worksheet_rec.worksheet_status <> 'LOCKED'
1392 THEN
1393 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
1394 THEN
1395 fnd_message.set_name ('CN', 'CN_WKSHT_ACTION_SUBMIT');
1396 fnd_msg_pub.ADD;
1397 END IF;
1398
1399 RAISE fnd_api.g_exc_error;
1400 END IF;
1401
1402 ELSIF p_action = 'REJECT' OR p_action = 'APPROVE'
1403 THEN
1404 --Added for R12 payment security check begin.
1405 IF p_action = 'REJECT'
1406 THEN
1407 l_has_access := get_security_access (g_type_wksht, g_access_wksht_reject);
1408
1409 IF (l_has_access = FALSE)
1410 THEN
1411 RAISE fnd_api.g_exc_error;
1412 END IF;
1413 ELSIF p_action = 'APPROVE'
1414 THEN
1415 l_has_access := get_security_access (g_type_wksht, g_access_wksht_approve);
1416
1417 IF (l_has_access = FALSE)
1418 THEN
1419 RAISE fnd_api.g_exc_error;
1420 END IF;
1421 END IF;
1422
1423 --Added for R12 payment security check end.
1424 --- commented by fred
1425 IF l_worksheet_rec.worksheet_status <> 'SUBMITTED' AND l_worksheet_rec.worksheet_status <> 'APPROVED'
1426 THEN
1427 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
1428 THEN
1429 fnd_message.set_name ('CN', 'CN_WKSHT_ACTION_APPROVE');
1430 fnd_msg_pub.ADD;
1431 END IF;
1432
1433 RAISE fnd_api.g_exc_error;
1434 END IF;
1435
1436 -- only manager or superuser can perform submit and approve
1437 -- Check if user is manager
1438 /* comment out as R12 payment security has been changed.
1439 IF is_superuser(p_period_id => l_pay_period_id) = 0 AND is_manager(p_period_id => l_pay_period_id) = 0 THEN
1440 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1441 FND_MESSAGE.SET_NAME ('CN','CN_WKSHT_NOT_MGR');
1442 FND_MSG_PUB.Add;
1443 END IF;
1444 RAISE FND_API.G_EXC_ERROR ;
1445 END IF;
1446 */
1447
1448 -- l_assigned_to_user_id is null
1449 -- if wksht is submitted or approved, need to check in
1450 -- cn_pay_approval_flow table to find out which analysts this wksht
1451 -- submit_to and see if current user has access to these analyst
1452 -- only need to check if p_action = SUBMIT or APPROVE
1453 IF l_assigned_to_user_id IS NULL
1454 THEN
1455 -- Bug 3498950 3/8/04 ACHUNG:skip check for super user
1456 IF (is_superuser (l_pay_period_id,l_org_id) = 1)
1457 THEN
1458 NULL;
1459 ELSE
1460 BEGIN
1461 SELECT 1
1462 INTO l_tmp
1463 FROM DUAL
1464 WHERE EXISTS (
1465 SELECT 1
1466 FROM jtf_rs_group_usages u2,
1467 jtf_rs_rep_managers m2,
1468
1469 -- start inline view
1470 -- get all rows for a login user in jtf_rs_rep_managers
1471 -- with period = p_period_id
1472 (SELECT DISTINCT m1.resource_id,
1473 GREATEST (pr.start_date, m1.start_date_active) start_date,
1474 LEAST (pr.end_date, NVL (m1.end_date_active, pr.end_date)) end_date
1475 FROM cn_period_statuses pr,
1476 jtf_rs_group_usages u1,
1477 jtf_rs_rep_managers m1
1478 WHERE pr.period_id = l_pay_period_id
1479 AND pr.org_id=l_org_id
1480 AND u1.USAGE = 'COMP_PAYMENT'
1484 )
1481 AND m1.resource_id = l_resource_id
1482 AND ( (m1.start_date_active <= pr.end_date)
1483 AND (pr.start_date <= NVL (m1.end_date_active, pr.start_date))
1485 AND u1.GROUP_ID = m1.GROUP_ID
1486 AND m1.parent_resource_id = m1.resource_id
1487 AND m1.hierarchy_type IN ('MGR_TO_MGR', 'REP_TO_REP')
1488 AND m1.CATEGORY <> 'TBH') v3
1489 -- end inlive view v3
1490 WHERE u2.USAGE = 'COMP_PAYMENT'
1491 AND u2.GROUP_ID = m2.GROUP_ID
1492 AND m2.parent_resource_id = v3.resource_id
1493 AND ((m2.start_date_active <= v3.end_date) AND (v3.start_date <= NVL (m2.end_date_active, v3.start_date)))
1494 AND m2.CATEGORY <> 'TBH'
1495 AND m2.hierarchy_type IN ('MGR_TO_MGR', 'MGR_TO_REP', 'REP_TO_REP')
1496 AND m2.resource_id IN (SELECT DISTINCT submit_to_resource_id
1497 FROM cn_pay_approval_flow
1498 WHERE payment_worksheet_id = p_worksheet_id));
1499 EXCEPTION
1500 WHEN NO_DATA_FOUND
1501 THEN
1502 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
1503 THEN
1504 fnd_message.set_name ('CN', 'CN_NO_SRP_ACCESS');
1505 fnd_msg_pub.ADD;
1506 END IF;
1507
1508 RAISE fnd_api.g_exc_error;
1509 END;
1510 END IF; -- Bug 3498950
1511 END IF;
1512
1513 -- Chekc if same user try to approve the wksht again
1514 IF p_action = 'APPROVE'
1515 THEN
1516 --Added for R12 payment security check begin.
1517 l_has_access := get_security_access (g_type_wksht, g_access_wksht_approve);
1518
1519 IF (l_has_access = FALSE)
1520 THEN
1521 RAISE fnd_api.g_exc_error;
1522 END IF;
1523
1524 --Added for R12 payment security check end.
1525 SELECT COUNT (1)
1526 INTO l_tmp
1527 FROM cn_pay_approval_flow
1528 WHERE payment_worksheet_id = p_worksheet_id
1529 AND submit_by_user_id = fnd_global.user_id AND approval_status = 'APPROVED';
1530
1531 IF l_tmp > 0
1532 THEN
1533 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
1534 THEN
1535 fnd_message.set_name ('CN', 'CN_SAME_USER_APPROVE');
1536 fnd_msg_pub.ADD;
1537 END IF;
1538
1539 RAISE fnd_api.g_exc_error;
1540 END IF;
1541 END IF;
1542
1543 ELSE
1544 -- invalid p_action
1545 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
1546 THEN
1547 fnd_message.set_name ('CN', 'CN_WKSHT_ACTION_NOT_EXIST');
1548 fnd_msg_pub.ADD;
1549 END IF;
1550
1551 RAISE fnd_api.g_exc_error;
1552 END IF;
1553
1554 IF fnd_api.to_boolean (p_do_audit)
1555 THEN
1556 -- update audit table and worksheet status
1557 worksheet_audit (p_worksheet_id => p_worksheet_id,
1558 p_payrun_id => l_worksheet_rec.payrun_id,
1559 p_salesrep_id => l_worksheet_rec.salesrep_id,
1560 p_action => p_action,
1561 x_return_status => x_return_status,
1562 x_msg_count => x_msg_count,
1563 x_msg_data => x_msg_data
1564 );
1565
1566 IF x_return_status <> fnd_api.g_ret_sts_success
1567 THEN
1568 RAISE fnd_api.g_exc_error;
1569 END IF;
1570 END IF;
1571
1572 -- End of API body.
1573 -- Standard check of p_commit.
1574 IF fnd_api.to_boolean (p_commit)
1575 THEN
1576 COMMIT WORK;
1577 END IF;
1578
1579 -- Standard call to get message count and if count is 1, get message info.
1580 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
1581 EXCEPTION
1582 WHEN fnd_api.g_exc_error
1583 THEN
1584 ROLLBACK TO worksheet_action;
1585 x_return_status := fnd_api.g_ret_sts_error;
1586 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
1587 WHEN fnd_api.g_exc_unexpected_error
1588 THEN
1589 ROLLBACK TO worksheet_action;
1590 x_return_status := fnd_api.g_ret_sts_unexp_error;
1591 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
1592 WHEN OTHERS
1593 THEN
1594 ROLLBACK TO worksheet_action;
1595 x_return_status := fnd_api.g_ret_sts_unexp_error;
1596
1597 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error)
1598 THEN
1599 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
1600 END IF;
1601
1602 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
1606 FUNCTION get_security_access (
1603 END worksheet_action;
1604
1605 --R12 payment security
1607 p_type IN VARCHAR2,
1608 p_access IN VARCHAR2
1609 )
1610 RETURN BOOLEAN
1611 IS
1612 l_ret_val BOOLEAN := FALSE;
1613 l_func_name VARCHAR2 (50) := 'CN_PMT';
1614 l_separator VARCHAR2 (1) := '_';
1615 l_type VARCHAR2 (20);
1616 l_access VARCHAR2 (20);
1617 BEGIN
1618 --Get permission.
1619 l_func_name := l_func_name || l_separator || p_type || l_separator || p_access;
1620 l_ret_val := fnd_function.test_instance (function_name => l_func_name, user_name => fnd_global.user_name);
1621
1622 --If no access, then push the error on stacks.
1623 IF l_ret_val = FALSE
1624 THEN
1625 IF p_type = g_type_payrun
1626 THEN
1627 l_type := 'payment batch';
1628 ELSIF p_type = g_type_wksht
1629 THEN
1630 l_type := 'paysheet';
1631 END IF;
1632
1633 l_access := LOWER (p_access);
1634
1635 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
1636 THEN
1637 fnd_message.set_name ('CN', 'CN_PMT_NO_ACCESS');
1638 fnd_message.set_token ('TYPE', l_type);
1639 fnd_message.set_token ('ACCESS', l_access);
1640 fnd_msg_pub.ADD;
1641 END IF;
1642 END IF; --l_ret_val = FALSE
1643
1644 RETURN l_ret_val;
1645 EXCEPTION
1646 WHEN OTHERS
1647 THEN
1648 l_ret_val := FALSE;
1649 RETURN l_ret_val;
1650 END get_security_access;
1651
1652 FUNCTION getPermission(funcName in varchar2)
1653 RETURN varchar2
1654 IS
1655 l_ret_val BOOLEAN := FALSE;
1656 ret VARCHAR2(1) :='N';
1657
1658 Begin
1659
1660 l_ret_val := fnd_function.test_instance
1661 (
1662 function_name => funcName,
1663 user_name=> fnd_global.user_name
1664 );
1665
1666 --If no access, then push the error on stacks.
1667 IF l_ret_val = TRUE
1668 THEN
1669 ret:='Y';
1670 RETURN ret;
1671 else
1672 RETURN ret;
1673 END IF; --l_ret_val = FALSE
1674
1675
1676 EXCEPTION
1677 WHEN OTHERS
1678 THEN
1679 ret := 'N';
1680
1681 RETURN ret;
1682
1683 END getPermission;
1684
1685
1686
1687
1688
1689
1690 FUNCTION getDataAccess(p_payrun_id in number,p_assigned_to_user_id in number,p_user_id in number) return varchar2
1691 as
1692 ret varchar2(1);
1693 begin
1694 SELECT DECODE( (SELECT 1 FROM dual WHERE EXISTS ( SELECT 1
1695 FROM jtf_rs_group_usages u2,jtf_rs_rep_managers m2,jtf_rs_resource_extns_vl re2,
1696 (SELECT DISTINCT m1.resource_id, GREATEST(pr.start_date,m1.start_date_active) start_date,LEAST(pr.end_date,Nvl(m1.end_date_active,pr.end_date)) end_date
1697 FROM cn_period_statuses pr,jtf_rs_group_usages u1, jtf_rs_rep_managers m1
1698 WHERE pr.period_id = (SELECT p1.pay_period_id FROM cn_payruns p1 WHERE p1.payrun_id = p_payrun_id)
1699 AND pr.org_id =(SELECT org_id FROM cn_payruns where payrun_id=p_payrun_id)
1700 AND u1.usage = 'COMP_PAYMENT'
1701 AND m1.resource_id = ( SELECT resource_id FROM jtf_rs_resource_extns WHERE user_id = p_user_id)
1702 AND ((m1.start_date_active <= pr.end_date) AND (pr.start_date <= Nvl(m1.end_date_active,pr.start_date)))
1703 AND u1.group_id = m1.group_id
1704 AND m1.parent_resource_id = m1.resource_id
1705 AND m1.hierarchy_type IN ('MGR_TO_MGR','REP_TO_REP') AND m1.category <> 'TBH' ) v3
1706 WHERE u2.usage = 'COMP_PAYMENT'
1707 AND u2.group_id = m2.group_id
1708 AND m2.parent_resource_id = v3.resource_id AND ((m2.start_date_active <= v3.end_date)
1709 AND (v3.start_date <= Nvl(m2.end_date_active,v3.start_date))) AND m2.category <> 'TBH'
1710 AND m2.hierarchy_type IN ('MGR_TO_MGR','MGR_TO_REP','REP_TO_REP')
1711 AND m2.resource_id = re2.resource_id
1712 AND re2.user_id = Nvl(p_assigned_to_user_id,re2.user_id))) ,1,'Y',NULL,'N','N')
1713 into ret
1714 from dual;
1715 return nvl(ret,'N');
1716 end getDataAccess;
1717
1718
1719
1720
1721 FUNCTION UpdPayShtAccess(p_payrun_id in number,p_assigned_to_user_id in number,p_user_id in number)
1722 return varchar2
1723 as
1724 l_dret varchar2(1):='Y';
1725 l_sret varchar2(1):='Y';
1726 begin
1727 l_dret :=getDataAccess(p_payrun_id,p_assigned_to_user_id,p_user_id);
1728 l_sret :=getPermission('CN_PMT_WKSHT_VIEW');
1729
1730
1731 select decode(l_dret,l_sret,decode(l_dret,'Y','Y','N'),'N') into l_sret from dual;
1732 return l_sret;
1733
1734 EXCEPTION
1735 WHEN OTHERS
1736 THEN
1737 l_sret := 'N';
1738
1739 RETURN l_sret;
1740 end UpdPayShtAccess;
1741
1742 END cn_payment_security_pvt;