161:
162: CURSOR l_omfs_palm_users_csr
163: IS
164: SELECT usr.USER_ID
165: FROM asg_user_pub_resps pubresp
166: , asg_user usr
167: WHERE usr.enabled = 'Y'
168: AND pubresp.user_name = usr.user_name
169: AND pubresp.pub_name ='SERVICEP';
162: CURSOR l_omfs_palm_users_csr
163: IS
164: SELECT usr.USER_ID
165: FROM asg_user_pub_resps pubresp
166: , asg_user usr
167: WHERE usr.enabled = 'Y'
168: AND pubresp.user_name = usr.user_name
169: AND pubresp.pub_name ='SERVICEP';
170: --Cursor when data routed to owner profile is set
170: --Cursor when data routed to owner profile is set
171: CURSOR l_route_omfs_palm_users_csr
172: IS
173: SELECT usr.USER_ID
174: FROM asg_user_pub_resps pubresp
175: , asg_user usr
176: WHERE usr.enabled = 'Y'
177: AND pubresp.user_name = usr.user_name
178: AND pubresp.pub_name ='SERVICEP'
171: CURSOR l_route_omfs_palm_users_csr
172: IS
173: SELECT usr.USER_ID
174: FROM asg_user_pub_resps pubresp
175: , asg_user usr
176: WHERE usr.enabled = 'Y'
177: AND pubresp.user_name = usr.user_name
178: AND pubresp.pub_name ='SERVICEP'
179: AND usr.USER_ID = usr.OWNER_ID;
205:
206: CURSOR l_omfs_palm_resources_csr
207: IS
208: SELECT usr.resource_id
209: FROM asg_user_pub_resps pubresp
210: , asg_user usr
211: WHERE usr.enabled = 'Y'
212: AND pubresp.user_name = usr.user_name
213: AND pubresp.pub_name = 'SERVICEP';
206: CURSOR l_omfs_palm_resources_csr
207: IS
208: SELECT usr.resource_id
209: FROM asg_user_pub_resps pubresp
210: , asg_user usr
211: WHERE usr.enabled = 'Y'
212: AND pubresp.user_name = usr.user_name
213: AND pubresp.pub_name = 'SERVICEP';
214:
214:
215: CURSOR l_route_omfs_palm_res_csr
216: IS
217: SELECT usr.resource_id
218: FROM asg_user_pub_resps pubresp
219: , asg_user usr
220: WHERE usr.enabled = 'Y'
221: AND pubresp.user_name = usr.user_name
222: AND pubresp.pub_name = 'SERVICEP'
215: CURSOR l_route_omfs_palm_res_csr
216: IS
217: SELECT usr.resource_id
218: FROM asg_user_pub_resps pubresp
219: , asg_user usr
220: WHERE usr.enabled = 'Y'
221: AND pubresp.user_name = usr.user_name
222: AND pubresp.pub_name = 'SERVICEP'
223: AND usr.USER_ID = usr.OWNER_ID;
276: is
277: cursor c_user_id (p_user_name varchar2)
278: is
279: select user_id
280: from asg_user
281: where user_name = p_user_name;
282:
283: l_user_id asg_user.user_id%TYPE;
284:
279: select user_id
280: from asg_user
281: where user_name = p_user_name;
282:
283: l_user_id asg_user.user_id%TYPE;
284:
285: begin
286:
287: --select user_id into l_user_id
284:
285: begin
286:
287: --select user_id into l_user_id
288: --from asg_user
289: --where user_name = p_user_name;
290:
291: OPEN c_user_id(p_user_name);
292: FETCH c_user_id INTO l_user_id;
301: is
302: cursor c_user_id (p_user_name varchar2)
303: is
304: select user_id
305: from asg_user
306: where user_name = p_user_name;
307:
308: l_user_id asg_user.user_id%TYPE;
309:
304: select user_id
305: from asg_user
306: where user_name = p_user_name;
307:
308: l_user_id asg_user.user_id%TYPE;
309:
310: v_client_timezone varchar2(100);
311: v_server_timezone varchar2(100);
312: l_server_time date;
313:
314: begin
315:
316: --select user_id into l_user_id
317: --from asg_user
318: --where user_name = p_user_name;
319:
320: OPEN c_user_id(p_user_name);
321: FETCH c_user_id INTO l_user_id;
724: IS
725: CURSOR l_is_palm_resource_csr(p_resource_id IN jtf_rs_resource_extns.resource_id%TYPE)
726: IS--R12 on multiple responsibility
727: SELECT 1
728: FROM asg_user_pub_resps pubresp
729: , asg_user usr
730: WHERE usr.enabled = 'Y'
731: AND pubresp.user_name = usr.user_name
732: AND pubresp.pub_name = 'SERVICEP'
725: CURSOR l_is_palm_resource_csr(p_resource_id IN jtf_rs_resource_extns.resource_id%TYPE)
726: IS--R12 on multiple responsibility
727: SELECT 1
728: FROM asg_user_pub_resps pubresp
729: , asg_user usr
730: WHERE usr.enabled = 'Y'
731: AND pubresp.user_name = usr.user_name
732: AND pubresp.pub_name = 'SERVICEP'
733: AND usr.resource_id = p_resource_id;
774: IS
775: CURSOR l_responsibilities_csr (b_user_id NUMBER)--R12 For multiple responsibility
776: IS
777: SELECT 1
778: FROM asg_user_pub_resps pubresp
779: , asg_user usr
780: WHERE usr.enabled = 'Y'
781: AND pubresp.user_name = usr.user_name
782: AND pubresp.pub_name = 'SERVICEP'
775: CURSOR l_responsibilities_csr (b_user_id NUMBER)--R12 For multiple responsibility
776: IS
777: SELECT 1
778: FROM asg_user_pub_resps pubresp
779: , asg_user usr
780: WHERE usr.enabled = 'Y'
781: AND pubresp.user_name = usr.user_name
782: AND pubresp.pub_name = 'SERVICEP'
783: AND usr.user_id = b_user_id;
828:
829: CURSOR l_tl_omfs_palm_users_csr is
830: --R12 For multiple responsibility
831: select usr.USER_ID
832: FROM asg_user_pub_resps pubresp
833: , asg_user usr
834: WHERE usr.LANGUAGE = p_language
835: AND usr.enabled = 'Y'
836: AND pubresp.user_name = usr.user_name
829: CURSOR l_tl_omfs_palm_users_csr is
830: --R12 For multiple responsibility
831: select usr.USER_ID
832: FROM asg_user_pub_resps pubresp
833: , asg_user usr
834: WHERE usr.LANGUAGE = p_language
835: AND usr.enabled = 'Y'
836: AND pubresp.user_name = usr.user_name
837: AND pubresp.pub_name ='SERVICEP';
838:
839: CURSOR l_route_tl_omfs_palm_users_csr is
840: --R12 For multiple responsibility
841: select usr.USER_ID
842: FROM asg_user_pub_resps pubresp
843: , asg_user usr
844: WHERE usr.LANGUAGE = p_language
845: AND usr.enabled = 'Y'
846: AND pubresp.user_name = usr.user_name
839: CURSOR l_route_tl_omfs_palm_users_csr is
840: --R12 For multiple responsibility
841: select usr.USER_ID
842: FROM asg_user_pub_resps pubresp
843: , asg_user usr
844: WHERE usr.LANGUAGE = p_language
845: AND usr.enabled = 'Y'
846: AND pubresp.user_name = usr.user_name
847: AND pubresp.pub_name ='SERVICEP'
872:
873: CURSOR l_tl_omfs_palm_resources_csr is
874: --R12 For multiple responsibility
875: select usr.RESOURCE_ID
876: FROM asg_user_pub_resps pubresp
877: , asg_user usr
878: WHERE usr.LANGUAGE = p_language
879: AND usr.enabled = 'Y'
880: AND pubresp.user_name = usr.user_name
873: CURSOR l_tl_omfs_palm_resources_csr is
874: --R12 For multiple responsibility
875: select usr.RESOURCE_ID
876: FROM asg_user_pub_resps pubresp
877: , asg_user usr
878: WHERE usr.LANGUAGE = p_language
879: AND usr.enabled = 'Y'
880: AND pubresp.user_name = usr.user_name
881: AND pubresp.pub_name ='SERVICEP';
882:
883: CURSOR l_route_tl_omfs_palm_res_csr is
884: --R12 For multiple responsibility
885: select usr.RESOURCE_ID
886: FROM asg_user_pub_resps pubresp
887: , asg_user usr
888: WHERE usr.LANGUAGE = p_language
889: AND usr.enabled = 'Y'
890: AND pubresp.user_name = usr.user_name
883: CURSOR l_route_tl_omfs_palm_res_csr is
884: --R12 For multiple responsibility
885: select usr.RESOURCE_ID
886: FROM asg_user_pub_resps pubresp
887: , asg_user usr
888: WHERE usr.LANGUAGE = p_language
889: AND usr.enabled = 'Y'
890: AND pubresp.user_name = usr.user_name
891: AND pubresp.pub_name ='SERVICEP'
913: return VARCHAR2
914: IS
915: CURSOR c_language_csr(p_user_id number) IS
916: SELECT language
917: FROM asg_user
918: WHERE user_id = p_user_id;
919:
920: l_language asg_user.language%TYPE;
921:
916: SELECT language
917: FROM asg_user
918: WHERE user_id = p_user_id;
919:
920: l_language asg_user.language%TYPE;
921:
922: BEGIN
923: OPEN c_language_csr(p_user_id);
924: FETCH c_language_csr INTO l_language;
930:
931: FUNCTION get_user_name(p_user_id IN number)
932: RETURN varchar2
933: IS
934: l_user_name asg_user.user_name%TYPE;
935:
936: CURSOR l_get_user_name(p_user_id IN number)
937: IS
938: SELECT user_name
935:
936: CURSOR l_get_user_name(p_user_id IN number)
937: IS
938: SELECT user_name
939: FROM asg_user
940: WHERE user_id = p_user_id;
941:
942: BEGIN
943: OPEN l_get_user_name(p_user_id);
1952: FUNCTION is_mfs_group(p_group_id NUMBER) RETURN BOOLEAN
1953: IS
1954: CURSOR c_mfs_group(b_group_id NUMBER) IS
1955: SELECT 1
1956: FROM ASG_USER
1957: WHERE GROUP_ID=b_group_id;
1958:
1959: l_mfs_grp NUMBER:=0;
1960: BEGIN
1974: FUNCTION get_group_owner(p_group_id NUMBER) RETURN NUMBER
1975: IS
1976: CURSOR c_group_owner(b_group_id NUMBER) IS
1977: SELECT USER_ID
1978: FROM ASG_USER
1979: WHERE OWNER_ID=USER_ID
1980: AND GROUP_ID=b_group_id;
1981:
1982: /*
1981:
1982: /*
1983: CURSOR c_group_owner(b_group_id NUMBER) IS
1984: SELECT USER_ID
1985: FROM ASG_USER
1986: WHERE ROLE_CODE = 'MFS_OWNER'
1987: AND GROUP_ID=b_group_id;*/
1988:
1989: l_grp_owner NUMBER;
2028: IS
2029: CURSOR c_get_owner(b_user_id NUMBER)
2030: IS
2031: SELECT OWNER_ID
2032: FROM ASG_USER
2033: WHERE USER_ID= p_user_id;
2034:
2035: l_owner_id NUMBER;
2036: BEGIN