1 PACKAGE BODY PV_EXT_TEAM_COMMON_PVT AS
2 /* $Header: pvxvcomb.pls 120.0 2005/05/27 16:18:25 appldev noship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30):= 'PV_EXT_TEAM_COMMON_PVT';
5 G_FILE_NAME CONSTANT VARCHAR2(12) := 'pvxvcomb.pls';
6
7
8 /*============================================================================
9 -- API name : chk_oppty_approver
10 -- Type : Function.
11 -- Function : This function return the value 'Y' or 'N' based on the
12 -- findings that the given user is a Opportunity Approver
13 -- or not.
14 --
15 -- Pre-reqs :
16 -- Parameters :
17 -- IN :
18 -- p_user_name In VARCHAR2
19 --
20 -- OUT :
21 --
22 -- Version : Current version 1.0
23 --
24 -- Notes : Note text
25 --
26 -- End of comments
27 ============================================================================*/
28 FUNCTION chk_oppty_approver(p_user_name IN VARCHAR2 )
29 RETURN VARCHAR2
30 IS
31
32 l_oppty_approver_flg VARCHAR2(1) ;
33
34 CURSOR l_optty_approver_csr(cv_user_name VARCHAR2) IS
35 SELECT 'Y'
36 FROM jtf_auth_principal_maps jtfpm,
37 jtf_auth_principals_b jtfp1,
38 jtf_auth_domains_b jtfd,
39 jtf_auth_principals_b jtfp2,
40 jtf_auth_role_perms jtfrp,
41 jtf_auth_permissions_b jtfperm
42 WHERE jtfp1.principal_name = cv_user_name
43 AND jtfp1.is_user_flag=1
44 AND jtfp1.jtf_auth_principal_id = jtfpm.jtf_auth_principal_id
45 AND jtfpm.jtf_auth_parent_principal_id = jtfp2.jtf_auth_principal_id
46 AND jtfp2.is_user_flag=0
47 AND jtfp2.jtf_auth_principal_id = jtfrp.jtf_auth_principal_id
48 AND jtfrp.positive_flag = 1
49 AND jtfrp.jtf_auth_permission_id = jtfperm.jtf_auth_permission_id
50 AND jtfperm.permission_name = 'PV_OPPTY_CONTACT'
51 AND jtfd.jtf_auth_domain_id=jtfpm.jtf_auth_domain_id
52 AND jtfd.domain_name='CRM_DOMAIN';
53
54 BEGIN
55
56 OPEN l_optty_approver_csr(p_user_name);
57 FETCH l_optty_approver_csr INTO l_oppty_approver_flg;
58 IF l_optty_approver_csr%NOTFOUND THEN
59 l_oppty_approver_flg := 'N';
60 END IF;
61 CLOSE l_optty_approver_csr;
62
63 return l_oppty_approver_flg;
64
65 END chk_oppty_approver;
66
67 END PV_EXT_TEAM_COMMON_PVT;