[Home] [Help]
PACKAGE BODY: APPS.CN_PAYMENT_SECURITY_PVT
Source
1 PACKAGE BODY cn_payment_security_pvt AS
2 -- $Header: cnvpmscb.pls 120.20.12010000.2 2008/09/18 20:29:09 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
142 AND m1.resource_id = l_resource_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
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',
291 x_jtf_note_id => l_note_id -- returned
288 p_notes => l_note_msg,
289 p_notes_detail => l_note_msg,
290 p_note_type => 'CN_SYSGEN', -- for system generated
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
394 --Added for R12 payment security check begin.
395 IF p_action = 'UNFREEZE'
396 THEN
397 l_has_access := get_security_access (g_type_payrun, g_access_payrun_unfreeze);
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';
432 x_status => l_new_status,
429 END IF;
430
431 cn_payruns_pkg.UPDATE_RECORD (x_payrun_id => p_payrun_id,
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';
518 l_event_name := 'delete' ;
519
520 ELSIF p_action = 'RELEASE_HOLD'
521 THEN
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';
567 THEN
564 l_event_name := 'submit' ;
565
566 ELSIF p_action = 'APPROVE'
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,
634 x_msg_count => x_msg_count,
635 x_msg_data => x_msg_data,
636 p_source_object_id => p_payrun_id,
637 p_source_object_code => 'CN_PAYRUNS',
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
685 -- x_msg_data OUT VARCHAR2(2000)
682 -- p_action IN VARCHAR2
683 -- OUT : x_return_status OUT VARCHAR2(1)
684 -- x_msg_count OUT NUMBER
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
759 WHERE payrun_id = p_payrun_id;
760 EXCEPTION
761 WHEN NO_DATA_FOUND
762 THEN
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)
839 END IF;
836 THEN
837 fnd_message.set_name ('CN', 'CN_WKSHT_STILL_PROCESSING');
838 fnd_msg_pub.ADD;
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'
909 THEN
910 --Added for R12 payment security check begin.
911 l_has_access := get_security_access (g_type_payrun, g_access_payrun_pay);
912
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.
992 THEN
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
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
1037 PROCEDURE worksheet_action (
1038 p_api_version IN NUMBER,
1039 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
1040 p_commit 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
1061 CURSOR c (
1062 c_payrun_id cn_payruns.payrun_id%TYPE
1063 )
1064 IS
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,
1125 AND s.salesrep_id = wk.salesrep_id
1122 cn_salesreps s
1123 WHERE wk.payment_worksheet_id = p_worksheet_id
1124 AND pay.payrun_id = wk.payrun_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,
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,
1227 jtf_rs_group_usages u1,
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;
1259 END;
1256 END IF;
1257
1258 RAISE fnd_api.g_exc_error;
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 --fix for the Bug 7415126
1304 IF NVL (fnd_profile.VALUE ('CN_CHK_WKSHT_STATUS'), 'Y') = 'Y'
1305 THEN
1306 --Added for R12 payment security check end.
1307 IF l_payrun_status <> 'UNPAID' OR l_worksheet_rec.worksheet_status <> 'UNPAID'
1308 THEN
1309 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
1310 THEN
1311 fnd_message.set_name ('CN', 'CN_WKSHT_ACTION_UNPAID');
1312 fnd_msg_pub.ADD;
1313 END IF;
1314
1315 RAISE fnd_api.g_exc_error;
1316 END IF;
1317 ELSE
1318
1319 IF l_payrun_status = 'PAID' OR l_worksheet_rec.worksheet_status <> 'UNPAID'
1320 THEN
1321 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
1322 THEN
1323 fnd_message.set_name ('CN', 'CN_WKSHT_ACTION_UNPAID');
1324 fnd_msg_pub.ADD;
1325 END IF;
1326
1327 RAISE fnd_api.g_exc_error;
1328 END IF;
1329 END IF;
1330
1331
1332
1333 ELSIF p_action IN ( 'HOLD_ALL' , 'RELEASE_ALL' )
1334 THEN
1335 l_has_access := get_security_access (g_type_wksht, g_access_wksht_release_holds);
1336
1337 IF (l_has_access = FALSE)
1338 THEN
1339 RAISE fnd_api.g_exc_error;
1340 END IF;
1341
1342 --Added for R12 payment security check end.
1343 IF l_payrun_status <> 'UNPAID' OR l_worksheet_rec.worksheet_status NOT IN ('PROCESSING', 'UNPAID')
1344 THEN
1345 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
1346 THEN
1347 fnd_message.set_name ('CN', 'CN_WKSHT_NOT_PROCESSING');
1348 fnd_msg_pub.ADD;
1349 END IF;
1350
1351 RAISE fnd_api.g_exc_error;
1352 END IF;
1353
1354 ELSIF p_action IN ( 'RESET_TO_UNPAID' )
1355 THEN
1356 l_has_access := get_security_access (g_type_wksht, g_access_wksht_release_holds);
1357
1358 IF (l_has_access = FALSE)
1359 THEN
1360 RAISE fnd_api.g_exc_error;
1361 END IF;
1362
1363 --Added for R12 payment security check end.
1364 IF l_payrun_status <> 'UNPAID' OR (l_worksheet_rec.worksheet_status NOT IN ('PROCESSING','FAILED'))
1365 THEN
1366 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
1367 THEN
1368 fnd_message.set_name ('CN', 'CN_WKSHT_NOT_PROCESSING');
1369 fnd_msg_pub.ADD;
1370 END IF;
1371
1372 RAISE fnd_api.g_exc_error;
1373 END IF;
1374
1375 -- p_action = 'UNLOCK'
1376 ELSIF p_action = 'UNLOCK'
1377 THEN
1378 --Added for R12 payment security check begin.
1379 l_has_access := get_security_access (g_type_wksht, g_access_wksht_unlock);
1380
1381 IF (l_has_access = FALSE)
1382 THEN
1383 RAISE fnd_api.g_exc_error;
1384 END IF;
1385
1386 --Added for R12 payment security check end.
1387 IF l_payrun_status <> 'UNPAID' OR l_worksheet_rec.worksheet_status <> 'LOCKED'
1388 THEN
1389 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
1390 THEN
1391 fnd_message.set_name ('CN', 'CN_WKSHT_ACTION_UNLOCK');
1392 fnd_msg_pub.ADD;
1393 END IF;
1394
1395 RAISE fnd_api.g_exc_error;
1396 END IF;
1397 -- p_action = 'SUBMIT'
1398 ELSIF p_action = 'SUBMIT'
1399 THEN
1400 --Added for R12 payment security check begin.
1401 l_has_access := get_security_access (g_type_wksht, g_access_wksht_submit);
1402
1403 IF (l_has_access = FALSE)
1404 THEN
1405 RAISE fnd_api.g_exc_error;
1406 END IF;
1407
1411 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
1408 --Added for R12 payment security check end.
1409 IF l_worksheet_rec.worksheet_status <> 'LOCKED'
1410 THEN
1412 THEN
1413 fnd_message.set_name ('CN', 'CN_WKSHT_ACTION_SUBMIT');
1414 fnd_msg_pub.ADD;
1415 END IF;
1416
1417 RAISE fnd_api.g_exc_error;
1418 END IF;
1419
1420 ELSIF p_action = 'REJECT' OR p_action = 'APPROVE'
1421 THEN
1422 --Added for R12 payment security check begin.
1423 IF p_action = 'REJECT'
1424 THEN
1425 l_has_access := get_security_access (g_type_wksht, g_access_wksht_reject);
1426
1427 IF (l_has_access = FALSE)
1428 THEN
1429 RAISE fnd_api.g_exc_error;
1430 END IF;
1431 ELSIF p_action = 'APPROVE'
1432 THEN
1433 l_has_access := get_security_access (g_type_wksht, g_access_wksht_approve);
1434
1435 IF (l_has_access = FALSE)
1436 THEN
1437 RAISE fnd_api.g_exc_error;
1438 END IF;
1439 END IF;
1440
1441 IF NVL (fnd_profile.VALUE ('CN_CHK_WKSHT_STATUS'), 'Y') = 'Y'
1442 THEN
1443
1444 --Added for R12 payment security check end.
1445 --- commented by fred
1446 IF l_worksheet_rec.worksheet_status <> 'SUBMITTED' AND l_worksheet_rec.worksheet_status <> 'APPROVED'
1447 THEN
1448 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
1449 THEN
1450 fnd_message.set_name ('CN', 'CN_WKSHT_ACTION_APPROVE');
1451 fnd_msg_pub.ADD;
1452 END IF;
1453
1454 RAISE fnd_api.g_exc_error;
1455 END IF;
1456
1457 END IF;
1458
1459 -- only manager or superuser can perform submit and approve
1460 -- Check if user is manager
1461 /* comment out as R12 payment security has been changed.
1462 IF is_superuser(p_period_id => l_pay_period_id) = 0 AND is_manager(p_period_id => l_pay_period_id) = 0 THEN
1463 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1464 FND_MESSAGE.SET_NAME ('CN','CN_WKSHT_NOT_MGR');
1465 FND_MSG_PUB.Add;
1466 END IF;
1467 RAISE FND_API.G_EXC_ERROR ;
1468 END IF;
1469 */
1470
1471 -- l_assigned_to_user_id is null
1472 -- if wksht is submitted or approved, need to check in
1473 -- cn_pay_approval_flow table to find out which analysts this wksht
1474 -- submit_to and see if current user has access to these analyst
1475 -- only need to check if p_action = SUBMIT or APPROVE
1476 IF l_assigned_to_user_id IS NULL
1477 THEN
1478 -- Bug 3498950 3/8/04 ACHUNG:skip check for super user
1479 IF (is_superuser (l_pay_period_id,l_org_id) = 1)
1480 THEN
1481 NULL;
1482 ELSE
1483 BEGIN
1484 SELECT 1
1485 INTO l_tmp
1486 FROM DUAL
1487 WHERE EXISTS (
1488 SELECT 1
1489 FROM jtf_rs_group_usages u2,
1490 jtf_rs_rep_managers m2,
1491
1492 -- start inline view
1493 -- get all rows for a login user in jtf_rs_rep_managers
1494 -- with period = p_period_id
1495 (SELECT DISTINCT m1.resource_id,
1496 GREATEST (pr.start_date, m1.start_date_active) start_date,
1497 LEAST (pr.end_date, NVL (m1.end_date_active, pr.end_date)) end_date
1498 FROM cn_period_statuses pr,
1499 jtf_rs_group_usages u1,
1500 jtf_rs_rep_managers m1
1501 WHERE pr.period_id = l_pay_period_id
1502 AND pr.org_id=l_org_id
1503 AND u1.USAGE = 'COMP_PAYMENT'
1504 AND m1.resource_id = l_resource_id
1505 AND ( (m1.start_date_active <= pr.end_date)
1506 AND (pr.start_date <= NVL (m1.end_date_active, pr.start_date))
1507 )
1508 AND u1.GROUP_ID = m1.GROUP_ID
1509 AND m1.parent_resource_id = m1.resource_id
1510 AND m1.hierarchy_type IN ('MGR_TO_MGR', 'REP_TO_REP')
1511 AND m1.CATEGORY <> 'TBH') v3
1512 -- end inlive view v3
1513 WHERE u2.USAGE = 'COMP_PAYMENT'
1514 AND u2.GROUP_ID = m2.GROUP_ID
1515 AND m2.parent_resource_id = v3.resource_id
1516 AND ((m2.start_date_active <= v3.end_date) AND (v3.start_date <= NVL (m2.end_date_active, v3.start_date)))
1517 AND m2.CATEGORY <> 'TBH'
1518 AND m2.hierarchy_type IN ('MGR_TO_MGR', 'MGR_TO_REP', 'REP_TO_REP')
1519 AND m2.resource_id IN (SELECT DISTINCT submit_to_resource_id
1520 FROM cn_pay_approval_flow
1521 WHERE payment_worksheet_id = p_worksheet_id));
1525 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
1522 EXCEPTION
1523 WHEN NO_DATA_FOUND
1524 THEN
1526 THEN
1527 fnd_message.set_name ('CN', 'CN_NO_SRP_ACCESS');
1528 fnd_msg_pub.ADD;
1529 END IF;
1530
1531 RAISE fnd_api.g_exc_error;
1532 END;
1533 END IF; -- Bug 3498950
1534 END IF;
1535
1536 -- Chekc if same user try to approve the wksht again
1537 IF p_action = 'APPROVE'
1538 THEN
1539 --Added for R12 payment security check begin.
1540 l_has_access := get_security_access (g_type_wksht, g_access_wksht_approve);
1541
1542 IF (l_has_access = FALSE)
1543 THEN
1544 RAISE fnd_api.g_exc_error;
1545 END IF;
1546
1547 --Added for R12 payment security check end.
1548 SELECT COUNT (1)
1549 INTO l_tmp
1550 FROM cn_pay_approval_flow
1551 WHERE payment_worksheet_id = p_worksheet_id
1552 AND submit_by_user_id = fnd_global.user_id AND approval_status = 'APPROVED';
1553
1554 IF l_tmp > 0
1555 THEN
1556 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
1557 THEN
1558 fnd_message.set_name ('CN', 'CN_SAME_USER_APPROVE');
1559 fnd_msg_pub.ADD;
1560 END IF;
1561
1562 RAISE fnd_api.g_exc_error;
1563 END IF;
1564 END IF;
1565
1566 ELSE
1567 -- invalid p_action
1568 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
1569 THEN
1570 fnd_message.set_name ('CN', 'CN_WKSHT_ACTION_NOT_EXIST');
1571 fnd_msg_pub.ADD;
1572 END IF;
1573
1574 RAISE fnd_api.g_exc_error;
1575 END IF;
1576
1577 IF fnd_api.to_boolean (p_do_audit)
1578 THEN
1579 -- update audit table and worksheet status
1580 worksheet_audit (p_worksheet_id => p_worksheet_id,
1581 p_payrun_id => l_worksheet_rec.payrun_id,
1582 p_salesrep_id => l_worksheet_rec.salesrep_id,
1583 p_action => p_action,
1584 x_return_status => x_return_status,
1585 x_msg_count => x_msg_count,
1586 x_msg_data => x_msg_data
1587 );
1588
1589 IF x_return_status <> fnd_api.g_ret_sts_success
1590 THEN
1591 RAISE fnd_api.g_exc_error;
1592 END IF;
1593 END IF;
1594
1595 -- End of API body.
1596 -- Standard check of p_commit.
1597 IF fnd_api.to_boolean (p_commit)
1598 THEN
1599 COMMIT WORK;
1600 END IF;
1601
1602 -- Standard call to get message count and if count is 1, get message info.
1603 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
1604 EXCEPTION
1605 WHEN fnd_api.g_exc_error
1606 THEN
1607 ROLLBACK TO worksheet_action;
1608 x_return_status := fnd_api.g_ret_sts_error;
1609 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
1610 WHEN fnd_api.g_exc_unexpected_error
1611 THEN
1612 ROLLBACK TO worksheet_action;
1613 x_return_status := fnd_api.g_ret_sts_unexp_error;
1614 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
1615 WHEN OTHERS
1616 THEN
1617 ROLLBACK TO worksheet_action;
1618 x_return_status := fnd_api.g_ret_sts_unexp_error;
1619
1620 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error)
1621 THEN
1622 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
1623 END IF;
1624
1625 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
1626 END worksheet_action;
1627
1628 --R12 payment security
1629 FUNCTION get_security_access (
1630 p_type IN VARCHAR2,
1631 p_access IN VARCHAR2
1632 )
1633 RETURN BOOLEAN
1634 IS
1635 l_ret_val BOOLEAN := FALSE;
1636 l_func_name VARCHAR2 (50) := 'CN_PMT';
1637 l_separator VARCHAR2 (1) := '_';
1638 l_type VARCHAR2 (20);
1639 l_access VARCHAR2 (20);
1640 BEGIN
1641 --Get permission.
1642 l_func_name := l_func_name || l_separator || p_type || l_separator || p_access;
1643 l_ret_val := fnd_function.test_instance (function_name => l_func_name, user_name => fnd_global.user_name);
1644
1645 --If no access, then push the error on stacks.
1646 IF l_ret_val = FALSE
1647 THEN
1648 IF p_type = g_type_payrun
1649 THEN
1650 l_type := 'payment batch';
1651 ELSIF p_type = g_type_wksht
1652 THEN
1653 l_type := 'paysheet';
1654 END IF;
1655
1656 l_access := LOWER (p_access);
1657
1658 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
1659 THEN
1660 fnd_message.set_name ('CN', 'CN_PMT_NO_ACCESS');
1661 fnd_message.set_token ('TYPE', l_type);
1665 END IF; --l_ret_val = FALSE
1662 fnd_message.set_token ('ACCESS', l_access);
1663 fnd_msg_pub.ADD;
1664 END IF;
1666
1667 RETURN l_ret_val;
1668 EXCEPTION
1669 WHEN OTHERS
1670 THEN
1671 l_ret_val := FALSE;
1672 RETURN l_ret_val;
1673 END get_security_access;
1674
1675 FUNCTION getPermission(funcName in varchar2)
1676 RETURN varchar2
1677 IS
1678 l_ret_val BOOLEAN := FALSE;
1679 ret VARCHAR2(1) :='N';
1680
1681 Begin
1682
1683 l_ret_val := fnd_function.test_instance
1684 (
1685 function_name => funcName,
1686 user_name=> fnd_global.user_name
1687 );
1688
1689 --If no access, then push the error on stacks.
1690 IF l_ret_val = TRUE
1691 THEN
1692 ret:='Y';
1693 RETURN ret;
1694 else
1695 RETURN ret;
1696 END IF; --l_ret_val = FALSE
1697
1698
1699 EXCEPTION
1700 WHEN OTHERS
1701 THEN
1702 ret := 'N';
1703
1704 RETURN ret;
1705
1706 END getPermission;
1707
1708
1709
1710
1711
1712
1713 FUNCTION getDataAccess(p_payrun_id in number,p_assigned_to_user_id in number,p_user_id in number) return varchar2
1714 as
1715 ret varchar2(1);
1716 begin
1717 SELECT DECODE( (SELECT 1 FROM dual WHERE EXISTS ( SELECT 1
1718 FROM jtf_rs_group_usages u2,jtf_rs_rep_managers m2,jtf_rs_resource_extns_vl re2,
1719 (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
1720 FROM cn_period_statuses pr,jtf_rs_group_usages u1, jtf_rs_rep_managers m1
1721 WHERE pr.period_id = (SELECT p1.pay_period_id FROM cn_payruns p1 WHERE p1.payrun_id = p_payrun_id)
1722 AND pr.org_id =(SELECT org_id FROM cn_payruns where payrun_id=p_payrun_id)
1723 AND u1.usage = 'COMP_PAYMENT'
1724 AND m1.resource_id = ( SELECT resource_id FROM jtf_rs_resource_extns WHERE user_id = p_user_id)
1725 AND ((m1.start_date_active <= pr.end_date) AND (pr.start_date <= Nvl(m1.end_date_active,pr.start_date)))
1726 AND u1.group_id = m1.group_id
1727 AND m1.parent_resource_id = m1.resource_id
1728 AND m1.hierarchy_type IN ('MGR_TO_MGR','REP_TO_REP') AND m1.category <> 'TBH' ) v3
1729 WHERE u2.usage = 'COMP_PAYMENT'
1730 AND u2.group_id = m2.group_id
1731 AND m2.parent_resource_id = v3.resource_id AND ((m2.start_date_active <= v3.end_date)
1732 AND (v3.start_date <= Nvl(m2.end_date_active,v3.start_date))) AND m2.category <> 'TBH'
1733 AND m2.hierarchy_type IN ('MGR_TO_MGR','MGR_TO_REP','REP_TO_REP')
1734 AND m2.resource_id = re2.resource_id
1735 AND re2.user_id = Nvl(p_assigned_to_user_id,re2.user_id))) ,1,'Y',NULL,'N','N')
1736 into ret
1737 from dual;
1738 return nvl(ret,'N');
1739 end getDataAccess;
1740
1741
1742
1743
1744 FUNCTION UpdPayShtAccess(p_payrun_id in number,p_assigned_to_user_id in number,p_user_id in number)
1745 return varchar2
1746 as
1747 l_dret varchar2(1):='Y';
1748 l_sret varchar2(1):='Y';
1749 begin
1750 l_dret :=getDataAccess(p_payrun_id,p_assigned_to_user_id,p_user_id);
1751 l_sret :=getPermission('CN_PMT_WKSHT_VIEW');
1752
1753
1754 select decode(l_dret,l_sret,decode(l_dret,'Y','Y','N'),'N') into l_sret from dual;
1755 return l_sret;
1756
1757 EXCEPTION
1758 WHEN OTHERS
1759 THEN
1760 l_sret := 'N';
1761
1762 RETURN l_sret;
1763 end UpdPayShtAccess;
1764
1765 END cn_payment_security_pvt;