[Home] [Help]
PACKAGE BODY: APPS.ASP_CONTACT_PREF_SECURITY_PUB
Source
4
1 PACKAGE BODY ASP_CONTACT_PREF_SECURITY_PUB AS
2 /* $Header: aspctpsb.pls 120.1.12010000.2 2009/08/12 22:32:27 hekkiral ship $ */
3
8 RETCODE OUT NOCOPY VARCHAR2,
5 PROCEDURE DELETE_NO_ACCESS_CONTACTS
6 (
7 ERRBUF OUT NOCOPY VARCHAR2,
9 P_DEBUG IN VARCHAR2
10 )
11
12 IS
13
14 set_err_var VARCHAR2(1);
15
16 l_party_id NUMBER;
17 l_category VARCHAR2(64) ;
18 l_preference_code VARCHAR2(64) ;
19 l_object_version_number NUMBER;
20
21 --l_value_varchar2 VARCHAR2(1) := FND_API.G_MISS_CHAR;
22 --l_value_number NUMBER := FND_API.G_MISS_NUM;
23 --l_value_date DATE := FND_API.G_MISS_DATE;
24
25 l_return_status VARCHAR2(1);
26 l_msg_count NUMBER;
27 l_msg_data VARCHAR2(2000);
28 l_comit_size NUMBER ;
29 l_count NUMBER ;
30
31 CURSOR C_INACTIVE_CONTACTS IS
32 SELECT
33 PP.PARTY_ID,
34 PP.VALUE_NUMBER,
35 PP.OBJECT_VERSION_NUMBER
36 FROM
37 HZ_RELATIONSHIPS HR,
38 HZ_PARTY_PREFERENCES PP
39 WHERE
40 PP.MODULE = 'SALES_BOOKMARKS'
41 AND PP.CATEGORY = 'BOOKMARKED_PARTY_RELATIONSHIP'
42 AND PP.PREFERENCE_CODE = 'PARTY_ID'
43 AND PP.VALUE_NUMBER = HR.PARTY_ID
44 AND ( (TRUNC(SYSDATE) NOT BETWEEN TRUNC(NVL(HR.START_DATE,SYSDATE)) AND TRUNC(NVL(HR.END_DATE,SYSDATE)))
45 OR HR.STATUS <> 'A')
46 AND HR.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
47 AND HR.OBJECT_TABLE_NAME = 'HZ_PARTIES'
48 AND HR.DIRECTIONAL_FLAG = 'F';
49
50
51 CURSOR C_SALES_TEAM_ACCESS IS
52 SELECT
53 PP.PARTY_ID,
54 PP.VALUE_NUMBER,
55 PP.OBJECT_VERSION_NUMBER
56 FROM
57 HZ_RELATIONSHIPS HR,
58 HZ_PARTY_PREFERENCES PP,
59 JTF_RS_RESOURCE_EXTNS res,
60 PER_ALL_PEOPLE_F per
61 WHERE
62 PP.MODULE = 'SALES_BOOKMARKS'
63 AND PP.CATEGORY = 'BOOKMARKED_PARTY_RELATIONSHIP'
64 AND PP.PREFERENCE_CODE = 'PARTY_ID'
65 AND PP.VALUE_NUMBER = HR.PARTY_ID
66 AND TRUNC(SYSDATE) BETWEEN TRUNC(NVL(HR.START_DATE,SYSDATE)) AND TRUNC(NVL(HR.END_DATE,SYSDATE))
67 AND HR.STATUS = 'A'
68 AND HR.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
69 AND HR.OBJECT_TABLE_NAME = 'HZ_PARTIES'
70 AND HR.DIRECTIONAL_FLAG = 'F'
71 AND TRUNC(NVL(per.effective_end_date, SYSDATE)) >= TRUNC(SYSDATE)
72 AND res.category = 'EMPLOYEE'
73 AND res.source_id = per.person_id
74 AND PP.PARTY_ID = per.PARTY_ID
75 AND NOT EXISTS(
76 SELECT 1
77 FROM as_accesses_all
78 WHERE sales_lead_id IS NULL
79 AND lead_id IS NULL
80 AND customer_id = Hr.object_id -- organization in the relationship
81 AND salesforce_id = res.resource_id)
82 AND NOT EXISTS (
83 SELECT jrrm.group_id,resource_id
84 FROM jtf_rs_rep_managers jrrm ,
85 jtf_rs_group_usages jrgu
86
87 WHERE jrgu.usage in ('SALES', 'PRM')
88 AND jrgu.group_id = jrrm.group_id
89 AND jrrm.start_date_active <= trunc(SYSDATE)
90 AND NVL(jrrm.end_date_active, SYSDATE) >= trunc(SYSDATE)
91 AND jrrm.parent_resource_id = jrrm.resource_id
92 AND jrrm.parent_resource_id = res.resource_id
93 AND jrrm.hierarchy_type = 'MGR_TO_MGR');
94
95
96 CURSOR C_MANAGER_ACCESS IS
97 SELECT
98 PP.PARTY_ID,
99 PP.VALUE_NUMBER,
100 PP.OBJECT_VERSION_NUMBER
101 FROM
102 HZ_RELATIONSHIPS HR,
103 HZ_PARTY_PREFERENCES PP,
104 JTF_RS_RESOURCE_EXTNS res,
105 PER_ALL_PEOPLE_F per
106 WHERE
107 PP.MODULE = 'SALES_BOOKMARKS'
108 AND PP.CATEGORY = 'BOOKMARKED_PARTY_RELATIONSHIP'
109 AND PP.PREFERENCE_CODE = 'PARTY_ID'
110 AND PP.VALUE_NUMBER = HR.PARTY_ID
111 AND TRUNC(SYSDATE) BETWEEN TRUNC(NVL(HR.START_DATE,SYSDATE)) AND TRUNC(NVL(HR.END_DATE,SYSDATE))
112 AND HR.STATUS = 'A'
113 AND HR.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
114 AND HR.OBJECT_TABLE_NAME = 'HZ_PARTIES'
115 AND HR.DIRECTIONAL_FLAG = 'F'
116 AND TRUNC(NVL(per.effective_end_date, SYSDATE)) >= TRUNC(SYSDATE)
117 AND res.category = 'EMPLOYEE'
118 AND res.source_id = per.person_id
119 AND PP.PARTY_ID = per.PARTY_ID
120 AND NOT EXISTS(
121 SELECT 1
122 FROM JTF_RS_REP_MANAGERS jrrm,
123 JTF_RS_GROUP_USAGES jrgu,
124 AS_ACCESSES_ALL aaa
125 WHERE jrgu.usage IN ('SALES', 'PRM')
126 AND jrgu.group_id = jrrm.group_id
127 AND jrrm.hierarchy_type IN ('MGR_TO_MGR', 'MGR_TO_REP')
128 AND jrrm.resource_id = aaa.salesforce_id
129 AND TRUNC(jrrm.start_date_active) <= TRUNC(SYSDATE)
130 AND TRUNC(NVL(jrrm.end_date_active, SYSDATE)) >= TRUNC(SYSDATE)
131 AND aaa.sales_group_id = jrrm.group_id
132 AND aaa.lead_id IS NULL
133 AND aaa.sales_lead_id IS NULL
134 AND aaa.customer_id = Hr.object_id -- organization in the relationship
135 AND jrrm.parent_resource_id = res.resource_id
136 AND jrrm.parent_resource_id <> jrrm.resource_id
137 UNION ALL
138 SELECT 1
139 FROM as_accesses_all
140 WHERE sales_lead_id IS NULL
141 AND lead_id IS NULL
142 AND customer_id = Hr.object_id -- organization in the relationship
143 AND salesforce_id = res.resource_id)
144 AND EXISTS (
145 SELECT jrrm.group_id,resource_id
146 FROM jtf_rs_rep_managers jrrm ,
147 jtf_rs_group_usages jrgu
148 WHERE jrgu.usage in ('SALES', 'PRM')
149 AND jrgu.group_id = jrrm.group_id
153 AND jrrm.parent_resource_id = res.resource_id
150 AND jrrm.start_date_active <= trunc(SYSDATE)
151 AND NVL(jrrm.end_date_active, SYSDATE) >= trunc(SYSDATE)
152 AND jrrm.parent_resource_id = jrrm.resource_id
154 AND jrrm.hierarchy_type = 'MGR_TO_MGR');
155
156 l_cust_access VARCHAR2(1);
157
158 BEGIN
159
160 l_category := 'BOOKMARKED_PARTY_RELATIONSHIP';
161 l_preference_code := 'PARTY_ID';
162
163 set_err_var := 'N';
164 l_comit_size := 10000;
165 l_count := 0;
166
167 l_cust_access := FND_PROFILE.VALUE('ASN_CUST_ACCESS');
168
169 For C_INACTIVE_CONTACTS_REC IN C_INACTIVE_CONTACTS
170 LOOP
171
172 BEGIN
173
174 HZ_PREFERENCE_PUB.REMOVE(
175 P_PARTY_ID => C_INACTIVE_CONTACTS_REC.PARTY_ID,
176 P_CATEGORY => l_category,
177 P_PREFERENCE_CODE => l_preference_code,
178 --P_VALUE_VARCHAR2 => l_value_varchar2,
179 P_VALUE_NUMBER => C_INACTIVE_CONTACTS_REC.VALUE_NUMBER,
180 --P_VALUE_DATE => l_value_date,
181 P_OBJECT_VERSION_NUMBER => C_INACTIVE_CONTACTS_REC.OBJECT_VERSION_NUMBER,
182 X_RETURN_STATUS => l_return_status,
183 X_MSG_COUNT => l_msg_count,
184 X_MSG_DATA => l_msg_data);
185
186 l_count := l_count + 1;
187 if(l_count >= l_comit_size) then
188 commit;
189 l_count := 0;
190 end if;
191
192 EXCEPTION
193 WHEN fnd_api.g_exc_error
194 THEN
195 fnd_file.put_line(fnd_file.log, SQLCODE||':'||SQLERRM);
196 WHEN fnd_api.g_exc_unexpected_error
197 THEN
198 fnd_file.put_line(fnd_file.log, SQLCODE||':'||SQLERRM);
199 WHEN OTHERS
200 THEN
201 fnd_file.put_line(fnd_file.log, SQLCODE||':'||SQLERRM);
202 END;
203
204 END LOOP;
205
206 commit;
207
208 IF l_cust_access = 'T' THEN -- 7650889 delete contacts based on security profile ASN: Customer Access Privilege
209 l_count := 0;
210
211 For C_SALES_TEAM_ACCESS_REC IN C_SALES_TEAM_ACCESS
212 loop
213
214 BEGIN
215
216 HZ_PREFERENCE_PUB.REMOVE(
217 P_PARTY_ID => C_SALES_TEAM_ACCESS_REC.PARTY_ID,
218 P_CATEGORY => l_category,
219 P_PREFERENCE_CODE => l_preference_code,
220 --P_VALUE_VARCHAR2 => l_value_varchar2,
221 P_VALUE_NUMBER => C_SALES_TEAM_ACCESS_REC.VALUE_NUMBER,
222 --P_VALUE_DATE => l_value_date,
223 P_OBJECT_VERSION_NUMBER => C_SALES_TEAM_ACCESS_REC.OBJECT_VERSION_NUMBER,
224 X_RETURN_STATUS => l_return_status,
225 X_MSG_COUNT => l_msg_count,
226 X_MSG_DATA => l_msg_data);
227
228 l_count := l_count + 1;
229 if(l_count >= l_comit_size) then
230 commit;
231 l_count := 0;
232 end if;
233
234
235 EXCEPTION
236 WHEN fnd_api.g_exc_error
237 THEN
238 fnd_file.put_line(fnd_file.log, SQLCODE||':'||SQLERRM);
239 WHEN fnd_api.g_exc_unexpected_error
240 THEN
241 fnd_file.put_line(fnd_file.log, SQLCODE||':'||SQLERRM);
242 WHEN OTHERS
243 THEN
244 fnd_file.put_line(fnd_file.log, SQLCODE||':'||SQLERRM);
245 END;
246
247 END LOOP;
248
249 commit;
250 l_count := 0;
251
252 For C_MANAGER_ACCESS_REC IN C_MANAGER_ACCESS
253 loop
254
255 BEGIN
256
257 HZ_PREFERENCE_PUB.REMOVE(
258 P_PARTY_ID => C_MANAGER_ACCESS_REC.PARTY_ID,
259 P_CATEGORY => l_category,
260 P_PREFERENCE_CODE => l_preference_code,
261 --P_VALUE_VARCHAR2 => l_value_varchar2,
262 P_VALUE_NUMBER => C_MANAGER_ACCESS_REC.VALUE_NUMBER,
263 --P_VALUE_DATE => l_value_date,
264 P_OBJECT_VERSION_NUMBER => C_MANAGER_ACCESS_REC.OBJECT_VERSION_NUMBER,
265 X_RETURN_STATUS => l_return_status,
266 X_MSG_COUNT => l_msg_count,
267 X_MSG_DATA => l_msg_data);
268
269 l_count := l_count + 1;
270 if(l_count >= l_comit_size) then
271 commit;
272 l_count := 0;
273 end if;
274
275
276 EXCEPTION
277 WHEN fnd_api.g_exc_error
278 THEN
279 fnd_file.put_line(fnd_file.log, SQLCODE||':'||SQLERRM);
280 WHEN fnd_api.g_exc_unexpected_error
281 THEN
282 fnd_file.put_line(fnd_file.log, SQLCODE||':'||SQLERRM);
283 WHEN OTHERS
284 THEN
285 fnd_file.put_line(fnd_file.log, SQLCODE||':'||SQLERRM);
286 END;
287
288 END LOOP;
289
290 commit;
291 END IF;
292
293 EXCEPTION
294 WHEN fnd_api.g_exc_error
295 THEN
296 fnd_file.put_line(fnd_file.log, sqlcode||':'||sqlerrm);
297 WHEN fnd_api.g_exc_unexpected_error
298 THEN
299 fnd_file.put_line(fnd_file.log, sqlcode||':'||sqlerrm);
300 WHEN OTHERS
301 THEN
305 END DELETE_NO_ACCESS_CONTACTS;
302 fnd_file.put_line(fnd_file.log, sqlcode||':'||sqlerrm);
303
304
306
307 END ASP_CONTACT_PREF_SECURITY_PUB;