[Home] [Help]
PACKAGE BODY: APPS.GMD_FORMULA_SECURITY_ACCESS
Source
1 PACKAGE BODY gmd_formula_security_access AS
2 /* $Header: GMDFSFMB.pls 120.1 2005/08/04 09:28:21 txdaniel noship $ */
3
4 PROCEDURE secure_formula_access ( p_organization_id IN NUMBER,
5 p_formula_id IN NUMBER ) IS
6 l_organization_id NUMBER(15);
7 l_formula_id NUMBER(15);
8 x_return_status VARCHAR2 (200) ;
9 x_msg_count NUMBER ;
10 x_msg_data VARCHAR2 (2000) ;
11 x_return_code NUMBER;
12 v_resp_id NUMBER;
13 x_resp_id NUMBER;
14 x_user_id NUMBER;
15
16 CURSOR c_manual_formulas IS
17 SELECT user_id, responsibility_id, other_organization_id
18 FROM GMD_formula_security
19 WHERE organization_id = p_organization_id
20 AND formula_id = p_formula_id;
21 BEGIN
22
23 DELETE FROM gmd_formula_security_temp;
24
25 COMMIT;
26
27 x_resp_id := fnd_global.resp_id;
28 x_user_id := fnd_global.user_id;
29
30
31 SELECT responsibility_id INTO v_resp_id
32 FROM fnd_responsibility
33 WHERE responsibility_key = 'GMD_SECURITY_PROFILE_MGR';
34
35 -- fnd_global.apps_initialize (2060, v_resp_id , 552);
36 l_organization_id := p_organization_id;
37 l_formula_id := p_formula_id;
38
39 INSERT INTO gmd_formula_security_temp( assign_method_ind,
40 activated_ind,
41 access_type_ind,
42 organization_id,
43 user_id,
44 responsibility_id,
45 other_organization_id)
46 SELECT p.assign_method_ind,
47 'N',
48 p.access_type_ind,
49 p.organization_id,
50 p.user_id,
51 p.responsibility_id,
52 p.other_organization_id
53 FROM gmd_security_profiles p
54 WHERE organization_id = l_organization_id
55 AND assign_method_ind = 'M'
56 --Added following clause to avoid duplicate records w.r.t. bug 3495409
57 AND NOT EXISTS
58 ( SELECT 1 FROM GMD_formula_security fs
59 WHERE fs.formula_id = l_formula_id
60 AND fs.organization_id = p.organization_id
61 AND NVL(fs.user_id,-1) = NVL(p.user_id,-1)
62 AND NVL(fs.responsibility_id,-1) = NVL(p.responsibility_id,-1)
63 AND ((fs.other_organization_id = p.other_organization_id) OR
64 (fs.other_organization_id IS NULL AND p.other_organization_id IS NULL))
65 );
66
67 FOR l_users IN c_manual_formulas
68 LOOP
69 IF l_users.other_organization_id IS NULL THEN
70 UPDATE GMD_formula_security_temp
71 SET activated_ind = 'A'
72 WHERE user_id = l_users.user_id
73 AND organization_id = p_organization_id
74 AND other_organization_id IS NULL;
75
76 UPDATE GMD_formula_security_temp
77 SET activated_ind = 'A'
78 WHERE responsibility_id = l_users.responsibility_id
79 AND organization_id = p_organization_id
80 AND other_organization_id IS NULL;
81 ELSE
82 UPDATE GMD_formula_security_temp
83 SET activated_ind = 'A'
84 WHERE user_id = l_users.user_id
85 AND organization_id = p_organization_id
86 AND other_organization_id = l_users.other_organization_id;
87
88 UPDATE GMD_formula_security_temp
89 SET activated_ind = 'A'
90 WHERE responsibility_id = l_users.responsibility_id
91 AND organization_id = p_organization_id
92 AND other_organization_id = l_users.other_organization_id;
93
94 END IF;
95 END LOOP;
96
97 INSERT INTO gmd_formula_security_temp( formula_id,
98 assign_method_ind,
99 activated_ind,
100 access_type_ind,
101 organization_id,
102 user_id,
103 responsibility_id,
104 other_organization_id)
105 SELECT p.formula_id,
106 'M', --Modified 'A' to 'M' w.r.t. bug 3495409
107 'A',
108 p.access_type_ind,
109 p.organization_id,
110 p.user_id,
111 p.responsibility_id,
112 p.other_organization_id
113 FROM gmd_formula_security p
114 WHERE organization_id = l_organization_id
115 AND formula_id = l_formula_id;
116
117 COMMIT;
118 -- fnd_global.apps_initialize (x_user_id, x_resp_id , 552);
119
120 EXCEPTION
121 WHEN FND_API.G_EXC_ERROR THEN
122 -- fnd_global.apps_initialize (x_user_id, x_resp_id , 552);
123 X_return_code := SQLCODE;
124 x_return_status := FND_API.G_RET_STS_ERROR;
125 FND_MSG_PUB.Count_And_Get(p_count=>x_msg_count, p_data=>x_msg_data);
126
127 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
128 -- fnd_global.apps_initialize (x_user_id, x_resp_id , 552);
129 X_return_code := SQLCODE;
130 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
131 FND_MSG_PUB.Count_And_Get(p_count=>x_msg_count, p_data=>x_msg_data);
132
133 WHEN OTHERS THEN
134 -- fnd_global.apps_initialize (x_user_id, x_resp_id , 552);
135 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
136 FND_MESSAGE.SET_NAME('GMD', 'GMD_UNEXPECTED_ERROR');
137 FND_MESSAGE.SET_TOKEN('ERROR', SQLERRM);
138 FND_MSG_PUB.ADD;
139 FND_MSG_PUB.COUNT_AND_GET (P_count => x_msg_count,
140 P_data => x_msg_data);
141
142 END secure_formula_access;
143 END gmd_formula_security_access ;
144