[Home] [Help]
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,
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:
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,
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,
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)
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:
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