[Home] [Help]
PACKAGE BODY: APPS.CSM_PROFILE_EVENT_PKG
Source
1 PACKAGE BODY CSM_PROFILE_EVENT_PKG AS
2 /* $Header: csmeprfb.pls 120.32.12020000.3 2013/04/09 10:58:08 saradhak ship $ */
3
4 g_pub_item VARCHAR2(30) := 'CSF_M_PROFILES';
5
6 -- get the profiles to be deleted
7 CURSOR l_profiles_del_csr
8 IS
9 SELECT acc.access_id,acc.user_id FROM csm_profile_option_values_acc acc
10 WHERE NOT EXISTS (SELECT 1 FROM fnd_profile_option_values val
11 WHERE acc.profile_option_id = val.profile_option_id
12 AND acc.application_id = val.application_id
13 AND acc.level_id = val.level_id
14 AND acc.level_value = val.level_value
15 AND (acc.level_id <> 10003 OR acc.level_value_application_id=val.level_value_application_id))
16 UNION
17 SELECT access_id,user_id FROM csm_profile_option_values_acc ACC
18 WHERE NOT EXISTS( SELECT 1 FROM FND_PROFILE_OPTIONS OPT
19 WHERE OPT.PROFILE_OPTION_ID = ACC.PROFILE_OPTION_ID
20 AND OPT.APPLICATION_ID = ACC.APPLICATION_ID
21 AND NVL(OPT.start_date_active, SYSDATE) <= SYSDATE
22 AND NVL(OPT.end_date_active, SYSDATE) >= SYSDATE
23 AND (opt.profile_option_name = 'CSF_M_RECIPIENTS_BOUNDARY'
24 OR opt.profile_option_name = 'CSF_MOBILE_TASK_TIMES_UPDATABLE'
25 OR opt.profile_option_name = 'CSF_DEBRIEF_OVERLAPPING_LABOR'
26 OR opt.profile_option_name = 'INC_DEFAULT_INCIDENT_SEVERITY'
27 OR opt.profile_option_name = 'INC_DEFAULT_INCIDENT_URGENCY'
28 OR opt.profile_option_name = 'INC_DEFAULT_INCIDENT_STATUS'
29 OR opt.profile_option_name = 'INC_DEFAULT_INCIDENT_TYPE'
30 OR opt.profile_option_name = 'JTF_TIME_UOM_CLASS'
31 OR opt.profile_option_name = 'ICX_PREFERRED_CURRENCY'
32 OR opt.profile_option_name = 'JTF_TASK_DEFAULT_ASSIGNEE_STATUS'
33 OR opt.profile_option_name = 'CS_SR_RESTRICT_IB'
34 OR opt.profile_option_name = 'SERVER_TIMEZONE_ID'
35 OR opt.profile_option_name = 'CLIENT_TIMEZONE_ID'
36 OR opt.profile_option_name = 'CSF_BUSINESS_PROCESS'
37 OR opt.profile_option_name = 'CSM_SEARCH_RESULT_SET_SIZE'
38 OR opt.profile_option_name = 'CSM_IB_ITEMS_AT_LOCATION'
39 OR opt.profile_option_name = 'CSM_ITEM_CATEGORY_SET_FILTER'
40 OR opt.profile_option_name = 'CSM_ITEM_CATEGORY_FILTER'
41 OR opt.profile_option_name = 'CS_INV_VALIDATION_ORG'
42 OR opt.profile_option_name = 'CSM_ENABLE_CREATE_SR'
43 OR opt.profile_option_name = 'CSM_ENABLE_CREATE_TASK'
44 OR opt.profile_option_name = 'CSM_ENABLE_UPDATE_ASSIGNMENTS'
45 OR opt.profile_option_name = 'CSM_ENABLE_VIEW_CUST_PRODUCTS'
46 OR opt.profile_option_name = 'CSM_MAX_READINGS_PER_COUNTER'
47 OR opt.profile_option_name = 'CSF_RETURN_REASON'
48 OR opt.profile_option_name = 'CSFW_DEFAULT_DISTANCE_UNIT'
49 OR opt.profile_option_name = 'CSF_CAPTURE_TRAVEL'
50 OR opt.profile_option_name = 'CSM_LABOR_LINE_TOTAL_CHECK'
51 OR opt.profile_option_name = 'ICX_DATE_FORMAT_MASK'
52 OR opt.profile_option_name = 'JTM_TIMEPICKER_FORMAT'
53 OR opt.profile_option_name = 'CSM_TIME_REASONABILITY_CHECK_APPLY'
54 OR opt.profile_option_name = 'ICX_NUMERIC_CHARACTERS'
55 OR opt.profile_option_name = 'CSF_LABOR_DEBRIEF_DEFAULT_UOM'
56 OR opt.profile_option_name = 'CSF_UOM_HOURS'
57 OR opt.profile_option_name = 'CSZ_DEFAULT_CONTACT_BY'
58 OR opt.profile_option_name = 'HZ_REF_TERRITORY'
59 OR opt.profile_option_name = 'HZ_REF_LANG'
60 OR opt.profile_option_name = 'HZ_LANG_FOR_COUNTRY_DISPLAY'
61 OR opt.profile_option_name = 'CSM_MAX_ATTACHMENT_SIZE' --For PPC
62 OR opt.profile_option_name = 'CSF_UOM_MINUTES'
63 OR opt.profile_option_name = 'CSF_DEFAULT_TASK_STATUS_PERSONAL'
64 OR opt.profile_option_name = 'CSF_DEFAULT_TASK_TYPE_PERSONAL'
65 OR opt.profile_option_name = 'CSF:DEFAULT DEBRIEF BUSINESS PROCESS'
66 OR opt.profile_option_name = 'CSF:DEFAULT DEBRIEF TRANSACTION TYPE'
67 OR opt.profile_option_name = 'INV:EXPENSE_TO_ASSET_TRANSFER'
68 OR opt.profile_option_name = 'JTF_PROFILE_DEFAULT_CURRENCY'
69 OR opt.profile_option_name = 'CSF_DEFAULT_TASK_ASSIGNED_STATUS'
70 OR opt.profile_option_name = 'JTF_TASK_DEFAULT_TASK_STATUS'
71 OR opt.profile_option_name = 'JTF_TASK_DEFAULT_TASK_PRIORITY'
72 OR opt.profile_option_name = 'CSFW_PLANNED_TASK_WINDOW'
73 OR opt.profile_option_name = 'CS_SR_CONTACT_MANDATORY'
74 OR opt.profile_option_name = 'CSM_RESTRICT_DEBRIEF'
75 OR opt.profile_option_name = 'CSM_RESTRICT_ORDERS'
76 OR opt.profile_option_name = 'CSM_RESTRICT_TRANSFERS'
77 OR opt.profile_option_name = 'JTF_TASK_DEFAULT_TASK_TYPE'
78 OR opt.profile_option_name = 'CSM_ACCOUNT_MESSAGE_INTERCEPTION'
79 OR opt.profile_option_name = 'CSM_AS_DATA_DOWNLOAD_INTERVAL'
80 OR opt.profile_option_name = 'CSM_AS_DATA_UPLOAD_INTERVAL'
81 OR opt.profile_option_name = 'CSM_ENABLE_AS_STATUS_NFN'
82 OR opt.profile_option_name = 'CSM_NFN_SYNC_ERROR'
83 OR opt.profile_option_name = 'CSM_NOTIFY_DEFERRED'
84 OR opt.profile_option_name = 'CSF_MANDATORY_LABOR_DEBRIEF'
85 OR opt.profile_option_name = 'CSF_MANDATORY_RESOLUTION_CODE'
86 OR opt.profile_option_name = 'CSF_DEFAULT_LABOR_DEBRIEF_DATETIME'
87 OR opt.profile_option_name = 'CSF_RESTRICT_SERVICE_REQUEST_CREATION_ TO_ SCHEDULED_SITES'
88 OR opt.profile_option_name = 'CSM_WIRELESS_URL'
89 OR opt.profile_option_name = 'CSM_ONLINE_ACCESS'
90 OR opt.profile_option_name = 'CSF:DEFAULT DEBRIEF BUSINESS PROCESS'
91 OR opt.profile_option_name = 'CSF_DEFAULT_LABOR_DEBRIEF_DATETIME'
92 OR opt.profile_option_name = 'CSF_DEFAULT_DEBRIEF_SAC_TRAVEL'
93 OR opt.profile_option_name = 'CSM_ALLOW_FREE_FORM_IB'
94 OR opt.profile_option_name = 'CSF: MANDATORY_LABOR_DEBRIEF'
95 OR opt.profile_option_name = 'CSF_DEBRIEF_LABOR_SAC'
96 OR opt.profile_option_name = 'CSF_DEBRIEF_TRAVEL_SAC'
97 OR opt.profile_option_name = 'JTF_NTS_NOTE_STATUS'
98 or opt.profile_option_name = 'CSM_FREE_FORM_ITEM'
99 or opt.profile_option_name = 'CSF_DEBRIEF_SERVICE_DATE'
100 OR opt.profile_option_name = 'CSM_DIS_TASK_SCHED_CONFLICT_WARNING'
101 OR opt.profile_option_name = 'JTF_NTS_NOTE_TYPE'
102 OR opt.profile_option_name = 'CSF_DEBRIEF_RESTRICT_INSTANCE'
103 OR opt.profile_option_name = 'CSF_ENABLE_RETURN_ROUTING_DBF'
104 OR opt.profile_option_name = 'CSP_PART_REQ_DEF_SHIP_TO'
105 OR opt.profile_option_name = 'CSF_GOOGLE_MAP_KEY'
106 OR opt.profile_option_name = 'CSF_GOOGLE_MAP_CLIENT_ID'
107 OR opt.profile_option_name = 'CSF_GOOGLE_MAP_ADDENDUM'
108 OR opt.profile_option_name = 'CSM_CUSTOMIZATION_KEY'
109 OR opt.profile_option_name = 'CSM_DFLT_TASK_STATUS_SYNC_UPD'));
110
111
112
113 /**
114 Refreshes the CSM_PROFILE_VALUES_ACC table, and marks dirty for users accordingly
115 -- MODIFICATION HISTORY
116 -- Person Date Comments
117 -- Sajesh 14-jun-2012 revamped code
118 */
119 PROCEDURE Refresh_Acc (p_status OUT NOCOPY VARCHAR2,
120 p_message OUT NOCOPY VARCHAR2)
121 IS
122 PRAGMA AUTONOMOUS_TRANSACTION;
123 l_markDirty boolean;
124 l_sqlerrno varchar2(20);
125 l_sqlerrmsg varchar2(2000);
126
127 -- get the profiles with values to be inserted
128 CURSOR l_profiles_ins_csr
129 IS
130 SELECT csm_profiles_acc_s.NEXTVAL as ACCESS_ID,au.user_id,application_id,profile_option_id,level_id,level_value,LEVEL_VALUE_APPLICATION_ID,
131 fnd_profile.value_specific(profile_option_name,au.user_id,au.responsibility_id,au.APP_ID) PRF_VALUE,profile_option_name prf_name
132 FROM asg_user au,
133 (SELECT opt.profile_option_name,val.application_id, val.profile_option_id,au.user_id,max(val.level_id) as LEVEL_ID ,
134 decode(max(val.level_id),10004,au.user_id,10003,asg_base.get_resp_id(au.user_name),10002,asg_base.get_application_id(au.user_name),0) LEVEL_VALUE,
135 decode(max(val.level_id),10003,max(level_value_application_id),null) LEVEL_VALUE_APPLICATION_ID
136 FROM fnd_profile_options opt,
137 fnd_profile_option_values val,
138 asg_user au
139 WHERE opt.profile_option_name in ( 'CSF_M_RECIPIENTS_BOUNDARY' ,'CSF_MOBILE_TASK_TIMES_UPDATABLE' ,'CSF_DEBRIEF_OVERLAPPING_LABOR' ,'INC_DEFAULT_INCIDENT_SEVERITY'
140 ,'INC_DEFAULT_INCIDENT_URGENCY' ,'JTF_TIME_UOM_CLASS' ,'ICX_PREFERRED_CURRENCY' ,'JTF_TASK_DEFAULT_ASSIGNEE_STATUS' ,'CS_SR_RESTRICT_IB' ,'SERVER_TIMEZONE_ID'
141 ,'CLIENT_TIMEZONE_ID' ,'CSF_BUSINESS_PROCESS' ,'CSM_SEARCH_RESULT_SET_SIZE' ,'CSM_IB_ITEMS_AT_LOCATION' ,'CSM_ITEM_CATEGORY_SET_FILTER' ,'CSM_ITEM_CATEGORY_FILTER'
142 ,'CS_INV_VALIDATION_ORG' ,'INC_DEFAULT_INCIDENT_TYPE' ,'INC_DEFAULT_INCIDENT_STATUS' ,'CSM_ENABLE_CREATE_SR'
143 ,'CSM_ENABLE_CREATE_TASK' ,'CSM_ENABLE_UPDATE_ASSIGNMENTS'
144 ,'CSM_ENABLE_VIEW_CUST_PRODUCTS' ,'CSM_MAX_READINGS_PER_COUNTER' ,'CSF_RETURN_REASON' ,'CSFW_DEFAULT_DISTANCE_UNIT'
145 ,'CSF_CAPTURE_TRAVEL' ,'CSM_LABOR_LINE_TOTAL_CHECK'
146 ,'ICX_DATE_FORMAT_MASK' ,'JTM_TIMEPICKER_FORMAT' ,'CSM_TIME_REASONABILITY_CHECK_APPLY','ICX_NUMERIC_CHARACTERS' ,'CSF_LABOR_DEBRIEF_DEFAULT_UOM'
147 ,'CSF_UOM_HOURS' ,'CSZ_DEFAULT_CONTACT_BY' ,'HZ_REF_TERRITORY' ,'HZ_REF_LANG' ,'HZ_LANG_FOR_COUNTRY_DISPLAY' ,'CSM_MAX_ATTACHMENT_SIZE','CSF_UOM_MINUTES'
148 ,'CSF_DEFAULT_TASK_STATUS_PERSONAL' ,'CSF_DEFAULT_TASK_TYPE_PERSONAL' ,'CSF:DEFAULT DEBRIEF BUSINESS PROCESS' ,'CSF:DEFAULT DEBRIEF TRANSACTION TYPE'
149 ,'INV:EXPENSE_TO_ASSET_TRANSFER' ,'JTF_PROFILE_DEFAULT_CURRENCY' ,'CSF_DEFAULT_TASK_ASSIGNED_STATUS'
150 ,'JTF_TASK_DEFAULT_TASK_STATUS' ,'JTF_TASK_DEFAULT_TASK_PRIORITY'
151 ,'CSFW_PLANNED_TASK_WINDOW' ,'CS_SR_CONTACT_MANDATORY' ,'CSM_RESTRICT_DEBRIEF' ,'CSM_RESTRICT_ORDERS' ,'CSM_RESTRICT_TRANSFERS' ,'JTF_TASK_DEFAULT_TASK_TYPE'
152 ,'CSM_ACCOUNT_MESSAGE_INTERCEPTION' ,'CSM_AS_DATA_DOWNLOAD_INTERVAL','CSM_AS_DATA_UPLOAD_INTERVAL'
153 ,'CSM_ENABLE_AS_STATUS_NFN' ,'CSM_NFN_SYNC_ERROR' ,'CSM_NOTIFY_DEFERRED'
154 ,'CSF_MANDATORY_LABOR_DEBRIEF','CSF_MANDATORY_RESOLUTION_CODE' ,'CSF_DEFAULT_LABOR_DEBRIEF_DATETIME' ,'CSF_RESTRICT_SERVICE_REQUEST_CREATION_ TO_ SCHEDULED_SITES'
155 ,'CSM_WIRELESS_URL' ,'CSM_ONLINE_ACCESS' ,'CSF:DEFAULT DEBRIEF BUSINESS PROCESS' ,'CSF_DEFAULT_LABOR_DEBRIEF_DATETIME' ,'CSF_DEFAULT_DEBRIEF_SAC_TRAVEL'
156 ,'CSM_ALLOW_FREE_FORM_IB' ,'CSF: MANDATORY_LABOR_DEBRIEF' ,'CSF_DEBRIEF_LABOR_SAC' ,'CSF_DEBRIEF_TRAVEL_SAC' ,'JTF_NTS_NOTE_STATUS' ,'CSM_FREE_FORM_ITEM'
157 ,'CSF_DEBRIEF_SERVICE_DATE','CSM_DIS_TASK_SCHED_CONFLICT_WARNING' ,'JTF_NTS_NOTE_TYPE','CSF_DEBRIEF_RESTRICT_INSTANCE' ,'CSF_ENABLE_RETURN_ROUTING_DBF'
158 ,'CSP_PART_REQ_DEF_SHIP_TO','CSF_GOOGLE_MAP_KEY','CSF_GOOGLE_MAP_CLIENT_ID','CSF_GOOGLE_MAP_ADDENDUM','CSM_CUSTOMIZATION_KEY','CSM_DFLT_TASK_STATUS_SYNC_UPD')
159 AND val.application_id = opt.application_id
160 AND val.profile_option_id = opt.profile_option_id
161 AND NVL(opt.start_date_active, SYSDATE) <= SYSDATE
162 AND NVL(opt.end_date_active, SYSDATE) >= SYSDATE
163 AND level_id in (10001,10002,10003,10004)
164 AND (level_id<> 10002 OR level_value=au.APP_ID)
165 AND (level_id <> 10003 OR level_value=au.responsibility_id)
166 AND (level_id <> 10004 OR level_value=au.user_id)
167 AND au.enabled='Y'
168 group by opt.profile_option_name,val.application_id, val.profile_option_id,au.user_id,au.user_name
169 ) tab
170 WHERE au.USER_ID=tab.user_id
171 AND not exists(SELECT 1 FROM csm_profile_option_values_acc acc
172 WHERE acc.application_id = tab.application_id
173 AND acc.profile_option_id = tab.profile_option_id
174 AND acc.user_id = tab.user_id);
175
176 -- get the profiles to be updated
177 CURSOR l_profiles_upd_csr
178 IS
179 SELECT acc.access_id,acc.user_id,val.level_id,val.level_value,val.LEVEL_VALUE_APPLICATION_ID,val.profile_option_value
180 FROM fnd_profile_option_values val,
181 csm_profile_option_values_acc acc,asg_user au
182 WHERE val.profile_option_id = acc.profile_option_id
183 AND val.application_id = acc.application_id
184 AND val.level_id in (10001,10002,10003,10004)
185 AND (val.level_id<> 10002 OR val.level_value=au.app_id)
186 AND (val.level_id <> 10003 OR val.level_value=au.responsibility_id)
187 AND (val.level_id <> 10004 OR val.level_value=au.user_id)
188 AND au.user_id=acc.user_id
189 AND (
190 acc.level_id < val.level_id --finer level profile available
191 OR
192 (acc.level_id = val.level_id --current level modified
193 AND acc.level_value = val.level_value
194 AND (acc.level_id <> 10003 OR acc.level_value_application_id=val.level_value_application_id)
195 AND NVL(val.profile_option_value,-1) <> NVL(acc.profile_option_value,-1)
196 )
197 );
198
199 TYPE num_tab_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
200 TYPE char_tab_type IS TABLE OF VARCHAR2(4000) INDEX BY BINARY_INTEGER;
201
202 l_tab_access_id ASG_DOWNLOAD.ACCESS_LIST;
203 l_tab_user_id ASG_DOWNLOAD.USER_LIST;
204 l_tab_app_id num_tab_type;
205 l_tab_prf_id num_tab_type;
206 l_tab_l_id num_tab_type;
207 l_tab_l_val num_tab_type;
208 l_tab_lva_id num_tab_type;
209 l_tab_pf_val char_tab_type;
210 l_tab_prf_name char_tab_type;
211
212 BEGIN
213
214 CSM_UTIL_PKG.LOG('Entering Profile refresh Acc' , 'CSM_PROFILE_EVENT_PKG.refresh_acc',FND_LOG.LEVEL_EVENT);
215
216 /*****DELETES*****************/
217
218 CSM_UTIL_PKG.LOG('Processing Deletes' , 'CSM_PROFILE_EVENT_PKG.refresh_acc',FND_LOG.LEVEL_EVENT);
219
220 OPEN l_profiles_del_csr;
221 LOOP
222 l_tab_access_id.DELETE;
223 l_tab_user_id.DELETE;
224 FETCH l_profiles_del_csr BULK COLLECT INTO l_tab_access_id,l_tab_user_id LIMIT 1000;
225 EXIT WHEN l_tab_access_id.COUNT = 0;
226
227 CSM_UTIL_PKG.LOG('Bulk deleted ' || l_tab_access_id.count || ' records from csm_profile_option_values_acc' ,
228 'CSM_PROFILE_EVENT_PKG.refresh_acc',FND_LOG.LEVEL_EVENT);
229
230 l_markdirty := asg_download.mark_dirty(
231 P_PUB_ITEM => g_pub_item
232 , p_accessList => l_tab_access_id
233 , p_userid_list => l_tab_user_id
234 , p_dml_type => 'D'
235 , P_TIMESTAMP => sysdate
236 );
237
238 FORALL i IN 1..l_tab_access_id.COUNT
239 DELETE FROM csm_profile_option_values_acc WHERE access_id = l_tab_access_id(i);
240
241 COMMIT;
242 END LOOP;
243 CLOSE l_profiles_del_csr;
244
245 COMMIT;
246 /*****************UPDATES*****************/
247
248 CSM_UTIL_PKG.LOG('Processing Updates' , 'CSM_PROFILE_EVENT_PKG.refresh_acc',FND_LOG.LEVEL_EVENT);
249
250 OPEN l_profiles_upd_csr;
251 LOOP
252 l_tab_access_id.DELETE;
253 l_tab_user_id.DELETE;
254 l_tab_l_id.DELETE;
255 l_tab_l_val.DELETE;
256 l_tab_lva_id.DELETE;
257 l_tab_pf_val.DELETE;
258
259 FETCH l_profiles_upd_csr BULK COLLECT INTO l_tab_access_id,l_tab_user_id,l_tab_l_id,l_tab_l_val,l_tab_lva_id,l_tab_pf_val LIMIT 1000;
260 EXIT WHEN l_tab_access_id.COUNT = 0;
261
262 CSM_UTIL_PKG.LOG('Bulk updated ' || l_tab_access_id.count || ' records in csm_profile_option_values_acc' ,
263 'CSM_PROFILE_EVENT_PKG.refresh_acc',FND_LOG.LEVEL_EVENT);
264
265 l_markdirty := asg_download.mark_dirty(
266 P_PUB_ITEM => g_pub_item
267 , p_accessList => l_tab_access_id
268 , p_userid_list => l_tab_user_id
269 , p_dml_type => 'U'
270 , P_TIMESTAMP => sysdate
271 );
272
273 FORALL i IN 1..l_tab_access_id.COUNT
274 UPDATE csm_profile_option_values_acc
275 SET LEVEL_ID = l_tab_l_id(i)
276 ,LEVEL_VALUE = l_tab_l_val(i)
277 ,LEVEL_VALUE_APPLICATION_ID =l_tab_lva_id(i)
278 ,profile_option_value = l_tab_pf_val(i)
279 ,last_update_date = sysdate
280 WHERE access_id = l_tab_access_id(i);
281
282 COMMIT;
283 END LOOP;
284 CLOSE l_profiles_upd_csr;
285
286 COMMIT;
287 /********************* INSERTS *****************************/
288
289 CSM_UTIL_PKG.LOG('Processing Inserts' , 'CSM_PROFILE_EVENT_PKG.refresh_acc',FND_LOG.LEVEL_EVENT);
290
291 OPEN l_profiles_ins_csr;
292 LOOP
293 l_tab_access_id.DELETE;
294 l_tab_user_id.DELETE;
295 l_tab_app_id.DELETE;
296 l_tab_prf_id.DELETE;
297 l_tab_l_id.DELETE;
298 l_tab_l_val.DELETE;
299 l_tab_lva_id.DELETE;
300 l_tab_pf_val.DELETE;
301 l_tab_prf_name.DELETE;
302
303 FETCH l_profiles_ins_csr BULK COLLECT INTO l_tab_access_id,l_tab_user_id,l_tab_app_id,l_tab_prf_id,
304 l_tab_l_id,l_tab_l_val,l_tab_lva_id,l_tab_pf_val,l_tab_prf_name LIMIT 1000;
305 EXIT WHEN l_tab_access_id.COUNT = 0;
306
307 CSM_UTIL_PKG.LOG('Bulk inserted ' || l_tab_access_id.count || ' records into csm_profile_option_values_acc' ,
308 'CSM_PROFILE_EVENT_PKG.refresh_acc',FND_LOG.LEVEL_EVENT);
309
310
311 FORALL i IN 1..l_tab_access_id.COUNT
312 INSERT INTO csm_profile_option_values_acc(access_id, user_id, application_id, profile_option_id,
313 level_id, level_value, level_value_application_id, profile_option_value,profile_option_name,
314 created_by,creation_date, last_updated_by, last_update_date, last_update_login )
315 VALUES (l_tab_access_id(I),l_tab_user_id(I),l_tab_app_id(I),l_tab_prf_id(I),
316 l_tab_l_id(I),l_tab_l_val(I),l_tab_lva_id(I),l_tab_pf_val(I),l_tab_prf_name(I),
317 fnd_global.user_id, sysdate, fnd_global.user_id,sysdate, fnd_global.login_id);
318
319 l_markdirty := asg_download.mark_dirty(
320 P_PUB_ITEM => g_pub_item
321 , p_accessList => l_tab_access_id
322 , p_userid_list => l_tab_user_id
323 , p_dml_type => 'I'
324 , P_TIMESTAMP => sysdate
325 );
326
327 COMMIT;
328 END LOOP;
329 CLOSE l_profiles_ins_csr;
330
331
332 -- set the program update date in jtm_con_request_data to sysdate
333 UPDATE jtm_con_request_data
334 SET last_run_date = sysdate,
335 last_update_date = SYSDATE
336 WHERE package_name = 'CSM_PROFILE_EVENT_PKG'
337 AND procedure_name = 'REFRESH_ACC';
338
339 p_status := 'FINE';
340 p_message := 'CSM_PROFILE_EVENT_PKG.Refresh_Acc Executed successfully';
341
342 COMMIT;
343
344 CSM_UTIL_PKG.LOG('Leaving Profile refresh Acc' , 'CSM_PROFILE_EVENT_PKG.refresh_acc',FND_LOG.LEVEL_EVENT);
345 EXCEPTION
346 WHEN others THEN
347 l_sqlerrno := to_char(SQLCODE);
348 l_sqlerrmsg := substr(SQLERRM, 1,200);
349 ROLLBACK;
350 p_status := 'ERROR';
351 p_message := 'Error in CSM_PROFILE_EVENT_PKG.Refresh_Acc: ' || l_sqlerrno || ':' || l_sqlerrmsg;
352 csm_util_pkg.log('CSM_PROFILE_EVENT_PKG ERROR : ' || l_sqlerrno || ':' || l_sqlerrmsg, 'CSM_PROFILE_EVENT_PKG.REFRESH_ACC', FND_LOG.LEVEL_EXCEPTION);
353 fnd_file.put_line(fnd_file.log, 'CSM_PROFILE_EVENT_PKG ERROR : ' || l_sqlerrno || ':' || l_sqlerrmsg);
354 END Refresh_Acc;
355
356
357 /***
358 ** Populates the user's acc table with the profiles upon user creation
359 ***/
360
361 PROCEDURE refresh_user_acc(p_user_id IN NUMBER)
362 IS
363 l_markDirty boolean;
364 l_sqlerrno varchar2(20);
365 l_sqlerrmsg varchar2(2000);
366
367 -- get the profiles with values to be inserted
368 CURSOR l_profiles_ins_csr
369 IS
370 SELECT csm_profiles_acc_s.NEXTVAL as ACCESS_ID,au.user_id,application_id,profile_option_id,level_id,level_value,LEVEL_VALUE_APPLICATION_ID,
371 fnd_profile.value_specific(profile_option_name,au.user_id,au.responsibility_id,au.APP_ID) PRF_VALUE,profile_option_name prf_name
372 FROM asg_user au,
373 (SELECT opt.profile_option_name,val.application_id, val.profile_option_id,au.user_id,max(val.level_id) as LEVEL_ID ,
374 decode(max(val.level_id),10004,au.user_id,10003,asg_base.get_resp_id(au.user_name),10002,asg_base.get_application_id(au.user_name),0) LEVEL_VALUE,
375 decode(max(val.level_id),10003,max(level_value_application_id),null) LEVEL_VALUE_APPLICATION_ID
376 FROM fnd_profile_options opt,
377 fnd_profile_option_values val,
378 asg_user au
379 WHERE opt.profile_option_name in ( 'CSF_M_RECIPIENTS_BOUNDARY' ,'CSF_MOBILE_TASK_TIMES_UPDATABLE' ,'CSF_DEBRIEF_OVERLAPPING_LABOR'
380 ,'INC_DEFAULT_INCIDENT_SEVERITY' ,'INC_DEFAULT_INCIDENT_URGENCY'
381 ,'JTF_TIME_UOM_CLASS' ,'ICX_PREFERRED_CURRENCY' ,'JTF_TASK_DEFAULT_ASSIGNEE_STATUS' ,'CS_SR_RESTRICT_IB' ,'SERVER_TIMEZONE_ID'
382 ,'CLIENT_TIMEZONE_ID' ,'CSF_BUSINESS_PROCESS'
383 ,'CSM_SEARCH_RESULT_SET_SIZE' ,'CSM_IB_ITEMS_AT_LOCATION' ,'CSM_ITEM_CATEGORY_SET_FILTER' ,'CSM_ITEM_CATEGORY_FILTER'
384 ,'CS_INV_VALIDATION_ORG' ,'INC_DEFAULT_INCIDENT_TYPE'
385 ,'INC_DEFAULT_INCIDENT_STATUS' ,'CSM_ENABLE_CREATE_SR' ,'CSM_ENABLE_CREATE_TASK' ,'CSM_ENABLE_UPDATE_ASSIGNMENTS'
386 ,'CSM_ENABLE_VIEW_CUST_PRODUCTS' ,'CSM_MAX_READINGS_PER_COUNTER'
387 ,'CSF_RETURN_REASON' ,'CSFW_DEFAULT_DISTANCE_UNIT' ,'CSF_CAPTURE_TRAVEL' ,'CSM_LABOR_LINE_TOTAL_CHECK' ,'ICX_DATE_FORMAT_MASK'
388 ,'JTM_TIMEPICKER_FORMAT' ,'CSM_TIME_REASONABILITY_CHECK_APPLY'
389 ,'ICX_NUMERIC_CHARACTERS' ,'CSF_LABOR_DEBRIEF_DEFAULT_UOM' ,'CSF_UOM_HOURS' ,'CSZ_DEFAULT_CONTACT_BY'
390 ,'HZ_REF_TERRITORY' ,'HZ_REF_LANG' ,'HZ_LANG_FOR_COUNTRY_DISPLAY' ,'CSM_MAX_ATTACHMENT_SIZE'
391 ,'CSF_UOM_MINUTES' ,'CSF_DEFAULT_TASK_STATUS_PERSONAL' ,'CSF_DEFAULT_TASK_TYPE_PERSONAL' ,'CSF:DEFAULT DEBRIEF BUSINESS PROCESS'
392 ,'CSF:DEFAULT DEBRIEF TRANSACTION TYPE','INV:EXPENSE_TO_ASSET_TRANSFER' ,'JTF_PROFILE_DEFAULT_CURRENCY' ,'CSF_DEFAULT_TASK_ASSIGNED_STATUS'
393 ,'JTF_TASK_DEFAULT_TASK_STATUS' ,'JTF_TASK_DEFAULT_TASK_PRIORITY' ,'CSFW_PLANNED_TASK_WINDOW'
394 ,'CS_SR_CONTACT_MANDATORY' ,'CSM_RESTRICT_DEBRIEF' ,'CSM_RESTRICT_ORDERS' ,'CSM_RESTRICT_TRANSFERS' ,'JTF_TASK_DEFAULT_TASK_TYPE'
395 ,'CSM_ACCOUNT_MESSAGE_INTERCEPTION' ,'CSM_AS_DATA_DOWNLOAD_INTERVAL'
396 ,'CSM_AS_DATA_UPLOAD_INTERVAL' ,'CSM_ENABLE_AS_STATUS_NFN' ,'CSM_NFN_SYNC_ERROR' ,'CSM_NOTIFY_DEFERRED'
397 ,'CSF_MANDATORY_LABOR_DEBRIEF' ,'CSF_MANDATORY_RESOLUTION_CODE'
398 ,'CSF_DEFAULT_LABOR_DEBRIEF_DATETIME' ,'CSF_RESTRICT_SERVICE_REQUEST_CREATION_ TO_ SCHEDULED_SITES' ,'CSM_WIRELESS_URL'
399 ,'CSM_ONLINE_ACCESS' ,'CSF:DEFAULT DEBRIEF BUSINESS PROCESS'
400 ,'CSF_DEFAULT_LABOR_DEBRIEF_DATETIME' ,'CSF_DEFAULT_DEBRIEF_SAC_TRAVEL' ,'CSM_ALLOW_FREE_FORM_IB' ,'CSF: MANDATORY_LABOR_DEBRIEF'
401 ,'CSF_DEBRIEF_LABOR_SAC' ,'CSF_DEBRIEF_TRAVEL_SAC'
402 ,'JTF_NTS_NOTE_STATUS' ,'CSM_FREE_FORM_ITEM' ,'CSF_DEBRIEF_SERVICE_DATE' ,'CSM_DIS_TASK_SCHED_CONFLICT_WARNING'
403 ,'JTF_NTS_NOTE_TYPE','CSF_DEBRIEF_RESTRICT_INSTANCE' ,'CSF_ENABLE_RETURN_ROUTING_DBF','CSP_PART_REQ_DEF_SHIP_TO'
404 ,'CSF_GOOGLE_MAP_KEY','CSF_GOOGLE_MAP_CLIENT_ID','CSF_GOOGLE_MAP_ADDENDUM','CSM_CUSTOMIZATION_KEY','CSM_DFLT_TASK_STATUS_SYNC_UPD')
405 AND val.application_id = opt.application_id
406 AND val.profile_option_id = opt.profile_option_id
407 AND NVL(opt.start_date_active, SYSDATE) <= SYSDATE
408 AND NVL(opt.end_date_active, SYSDATE) >= SYSDATE
409 AND level_id in (10001,10002,10003,10004)
410 AND (level_id<> 10002 OR level_value=au.APP_ID)
411 AND (level_id <> 10003 OR level_value=au.responsibility_id)
412 AND (level_id <> 10004 OR level_value=au.user_id)
413 AND au.user_id=p_user_id
414 group by opt.profile_option_name,val.application_id, val.profile_option_id,au.user_id,au.user_name
415 ) tab
416 WHERE au.USER_ID=tab.user_id
417 AND not exists(SELECT 1 FROM csm_profile_option_values_acc acc
418 WHERE acc.application_id = tab.application_id
419 AND acc.profile_option_id = tab.profile_option_id
420 AND acc.user_id = tab.user_id);
421
422 TYPE num_tab_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
423 TYPE char_tab_type IS TABLE OF VARCHAR2(4000) INDEX BY BINARY_INTEGER;
424
425 l_tab_access_id ASG_DOWNLOAD.ACCESS_LIST;
426 l_tab_user_id ASG_DOWNLOAD.USER_LIST;
427 l_tab_app_id num_tab_type;
428 l_tab_prf_id num_tab_type;
429 l_tab_l_id num_tab_type;
430 l_tab_l_val num_tab_type;
431 l_tab_lva_id num_tab_type;
432 l_tab_pf_val char_tab_type;
433 l_tab_prf_name char_tab_type;
434
435
436 BEGIN
437
438 CSM_UTIL_PKG.LOG('Entering Profile Inserts Acc for new user: '||p_user_id , 'CSM_PROFILE_EVENT_PKG.refresh_acc',FND_LOG.LEVEL_EVENT);
439
440 OPEN l_profiles_ins_csr;
441 LOOP
442 l_tab_access_id.DELETE;
443 l_tab_user_id.DELETE;
444 l_tab_app_id.DELETE;
445 l_tab_prf_id.DELETE;
446 l_tab_l_id.DELETE;
447 l_tab_l_val.DELETE;
448 l_tab_lva_id.DELETE;
449 l_tab_pf_val.DELETE;
450 l_tab_prf_name.DELETE;
451
452 FETCH l_profiles_ins_csr BULK COLLECT INTO l_tab_access_id,l_tab_user_id,l_tab_app_id,l_tab_prf_id,
453 l_tab_l_id,l_tab_l_val,l_tab_lva_id,l_tab_pf_val,l_tab_prf_name LIMIT 1000;
454 EXIT WHEN l_tab_access_id.COUNT = 0;
455
456 CSM_UTIL_PKG.LOG('Bulk inserted ' || l_tab_access_id.count || ' records into csm_profile_option_values_acc' ,
457 'CSM_PROFILE_EVENT_PKG.refresh_acc',FND_LOG.LEVEL_EVENT);
458
459
460 FORALL i IN 1..l_tab_access_id.COUNT
461 INSERT INTO csm_profile_option_values_acc(access_id, user_id, application_id, profile_option_id,
462 level_id, level_value, level_value_application_id, profile_option_value,profile_option_name,
463 created_by,creation_date, last_updated_by, last_update_date, last_update_login )
464 VALUES (l_tab_access_id(I),l_tab_user_id(I),l_tab_app_id(I),l_tab_prf_id(I),
465 l_tab_l_id(I),l_tab_l_val(I),l_tab_lva_id(I),l_tab_pf_val(I),l_tab_prf_name(I),
466 fnd_global.user_id, sysdate, fnd_global.user_id,sysdate, fnd_global.login_id);
467
468 l_markdirty := asg_download.mark_dirty(
469 P_PUB_ITEM => g_pub_item
470 , p_accessList => l_tab_access_id
471 , p_userid_list => l_tab_user_id
472 , p_dml_type => 'I'
473 , P_TIMESTAMP => sysdate
474 );
475
476 END LOOP;
477 CLOSE l_profiles_ins_csr;
478 CSM_UTIL_PKG.LOG('Leaving Profile Insert Acc for user'||p_user_id , 'CSM_PROFILE_EVENT_PKG.refresh_acc',FND_LOG.LEVEL_EVENT);
479 EXCEPTION
480 WHEN OTHERS THEN
481 l_sqlerrno := to_char(SQLCODE);
482 l_sqlerrmsg := substr(SQLERRM, 1,2000);
483 l_sqlerrmsg := ' Exception in refresh_user_acc for user_id:'|| to_char(p_user_id) || ':' || l_sqlerrno || ':' || l_sqlerrmsg;
484 CSM_UTIL_PKG.LOG(l_sqlerrmsg, 'csm_profile_event_pkg.refresh_user_acc',FND_LOG.LEVEL_EXCEPTION);
485 RAISE;
486 END refresh_user_acc;
487
488 /* SAJESH : Check if required later
489 IF PROFILES with no "value" set, is required, then add this inside "tab" of insert cursor */
490 /*
491 UNION ALL
492 SELECT opt.profile_option_name,opt.application_id, opt.profile_option_id,au.user_id,null as LEVEL_ID , null LEVEL_VALUE, null LEVEL_VALUE_APPLICATION_ID
493 FROM fnd_profile_options opt,asg_user au
494 WHERE opt.profile_option_name in ( 'CSF_M_RECIPIENTS_BOUNDARY' ,'CSF_MOBILE_TASK_TIMES_UPDATABLE' ,'CSF_DEBRIEF_OVERLAPPING_LABOR'
495 ,'INC_DEFAULT_INCIDENT_SEVERITY' ,'INC_DEFAULT_INCIDENT_URGENCY'
496 ,'JTF_TIME_UOM_CLASS' ,'ICX_PREFERRED_CURRENCY' ,'JTF_TASK_DEFAULT_ASSIGNEE_STATUS' ,'CS_SR_RESTRICT_IB' ,'SERVER_TIMEZONE_ID'
497 ,'CLIENT_TIMEZONE_ID' ,'CSF_BUSINESS_PROCESS'
498 ,'CSM_SEARCH_RESULT_SET_SIZE' ,'CSM_IB_ITEMS_AT_LOCATION' ,'CSM_ITEM_CATEGORY_SET_FILTER' ,'CSM_ITEM_CATEGORY_FILTER'
499 ,'CS_INV_VALIDATION_ORG' ,'INC_DEFAULT_INCIDENT_TYPE'
500 ,'INC_DEFAULT_INCIDENT_STATUS' ,'CSM_ENABLE_CREATE_SR' ,'CSM_ENABLE_CREATE_TASK' ,'CSM_ENABLE_UPDATE_ASSIGNMENTS'
501 ,'CSM_ENABLE_VIEW_CUST_PRODUCTS' ,'CSM_MAX_READINGS_PER_COUNTER'
502 ,'CSF_RETURN_REASON' ,'CSFW_DEFAULT_DISTANCE_UNIT' ,'CSF_CAPTURE_TRAVEL' ,'CSM_LABOR_LINE_TOTAL_CHECK' ,'ICX_DATE_FORMAT_MASK'
503 ,'JTM_TIMEPICKER_FORMAT' ,'CSM_TIME_REASONABILITY_CHECK_APPLY'
504 ,'ICX_NUMERIC_CHARACTERS' ,'CSF_LABOR_DEBRIEF_DEFAULT_UOM' ,'CSF_UOM_HOURS' ,'CSZ_DEFAULT_CONTACT_BY'
505 ,'HZ_REF_TERRITORY' ,'HZ_REF_LANG' ,'HZ_LANG_FOR_COUNTRY_DISPLAY' ,'CSM_MAX_ATTACHMENT_SIZE'
506 ,'CSF_UOM_MINUTES' ,'CSF_DEFAULT_TASK_STATUS_PERSONAL' ,'CSF_DEFAULT_TASK_TYPE_PERSONAL' ,'CSF:DEFAULT DEBRIEF BUSINESS PROCESS'
507 ,'CSF:DEFAULT DEBRIEF TRANSACTION TYPE','INV:EXPENSE_TO_ASSET_TRANSFER' ,'JTF_PROFILE_DEFAULT_CURRENCY' ,'CSF_DEFAULT_TASK_ASSIGNED_STATUS'
508 ,'JTF_TASK_DEFAULT_TASK_STATUS' ,'JTF_TASK_DEFAULT_TASK_PRIORITY' ,'CSFW_PLANNED_TASK_WINDOW'
509 ,'CS_SR_CONTACT_MANDATORY' ,'CSM_RESTRICT_DEBRIEF' ,'CSM_RESTRICT_ORDERS' ,'CSM_RESTRICT_TRANSFERS' ,'JTF_TASK_DEFAULT_TASK_TYPE'
510 ,'CSM_ACCOUNT_MESSAGE_INTERCEPTION' ,'CSM_AS_DATA_DOWNLOAD_INTERVAL'
511 ,'CSM_AS_DATA_UPLOAD_INTERVAL' ,'CSM_ENABLE_AS_STATUS_NFN' ,'CSM_NFN_SYNC_ERROR' ,'CSM_NOTIFY_DEFERRED'
512 ,'CSF_MANDATORY_LABOR_DEBRIEF' ,'CSF_MANDATORY_RESOLUTION_CODE'
513 ,'CSF_DEFAULT_LABOR_DEBRIEF_DATETIME' ,'CSF_RESTRICT_SERVICE_REQUEST_CREATION_ TO_ SCHEDULED_SITES' ,'CSM_WIRELESS_URL'
514 ,'CSM_ONLINE_ACCESS' ,'CSF:DEFAULT DEBRIEF BUSINESS PROCESS'
515 ,'CSF_DEFAULT_LABOR_DEBRIEF_DATETIME' ,'CSF_DEFAULT_DEBRIEF_SAC_TRAVEL' ,'CSM_ALLOW_FREE_FORM_IB' ,'CSF: MANDATORY_LABOR_DEBRIEF'
516 ,'CSF_DEBRIEF_LABOR_SAC' ,'CSF_DEBRIEF_TRAVEL_SAC'
517 ,'JTF_NTS_NOTE_STATUS' ,'CSM_FREE_FORM_ITEM' ,'CSF_DEBRIEF_SERVICE_DATE' ,'CSM_DIS_TASK_SCHED_CONFLICT_WARNING'
518 ,'JTF_NTS_NOTE_TYPE','CSF_DEBRIEF_RESTRICT_INSTANCE' ,'CSF_ENABLE_RETURN_ROUTING_DBF','CSP_PART_REQ_DEF_SHIP_TO'
519 ,'CSF_GOOGLE_MAP_KEY','CSF_GOOGLE_MAP_CLIENT_ID','CSF_GOOGLE_MAP_ADDENDUM','CSM_CUSTOMIZATION_KEY','CSM_DFLT_TASK_STATUS_SYNC_UPD')
520 AND NVL(opt.start_date_active, SYSDATE) <= SYSDATE
521 AND NVL(opt.end_date_active, SYSDATE) >= SYSDATE
522 AND NOT EXISTS(SELECT 1 FROM fnd_profile_option_values val WHERE val.application_id = opt.application_id AND val.profile_option_id = opt.profile_option_id)
523 AND au.enabled='Y'
524 */
525
526 /****** AFTER INSERT IS COMPLETE, there may be profiles with values set not at site but at app/resp/user level not macthing user-s ; don't think it;s required to be downloaded
527 later if required add this sql too
528 ******/
529 /*
530 SELECT csm_profiles_acc_s.NEXTVAL as ACCESS_ID,au.user_id,opt.profile_option_name,opt.application_id, opt.profile_option_id,au.user_id,
531 null as LEVEL_ID , null LEVEL_VALUE, null LEVEL_VALUE_APPLICATION_ID,opt.profile_option_name
532 FROM fnd_profile_options opt,asg_user au
533 WHERE opt.profile_option_name in ( 'CSF_M_RECIPIENTS_BOUNDARY' ,'CSF_MOBILE_TASK_TIMES_UPDATABLE' ,'CSF_DEBRIEF_OVERLAPPING_LABOR'
534 ,'INC_DEFAULT_INCIDENT_SEVERITY' ,'INC_DEFAULT_INCIDENT_URGENCY'
535 ,'JTF_TIME_UOM_CLASS' ,'ICX_PREFERRED_CURRENCY' ,'JTF_TASK_DEFAULT_ASSIGNEE_STATUS' ,'CS_SR_RESTRICT_IB' ,'SERVER_TIMEZONE_ID'
536 ,'CLIENT_TIMEZONE_ID' ,'CSF_BUSINESS_PROCESS'
537 ,'CSM_SEARCH_RESULT_SET_SIZE' ,'CSM_IB_ITEMS_AT_LOCATION' ,'CSM_ITEM_CATEGORY_SET_FILTER' ,'CSM_ITEM_CATEGORY_FILTER'
538 ,'CS_INV_VALIDATION_ORG' ,'INC_DEFAULT_INCIDENT_TYPE'
539 ,'INC_DEFAULT_INCIDENT_STATUS' ,'CSM_ENABLE_CREATE_SR' ,'CSM_ENABLE_CREATE_TASK' ,'CSM_ENABLE_UPDATE_ASSIGNMENTS'
540 ,'CSM_ENABLE_VIEW_CUST_PRODUCTS' ,'CSM_MAX_READINGS_PER_COUNTER'
541 ,'CSF_RETURN_REASON' ,'CSFW_DEFAULT_DISTANCE_UNIT' ,'CSF_CAPTURE_TRAVEL' ,'CSM_LABOR_LINE_TOTAL_CHECK' ,'ICX_DATE_FORMAT_MASK'
542 ,'JTM_TIMEPICKER_FORMAT' ,'CSM_TIME_REASONABILITY_CHECK_APPLY'
543 ,'ICX_NUMERIC_CHARACTERS' ,'CSF_LABOR_DEBRIEF_DEFAULT_UOM' ,'CSF_UOM_HOURS' ,'CSZ_DEFAULT_CONTACT_BY'
544 ,'HZ_REF_TERRITORY' ,'HZ_REF_LANG' ,'HZ_LANG_FOR_COUNTRY_DISPLAY' ,'CSM_MAX_ATTACHMENT_SIZE'
545 ,'CSF_UOM_MINUTES' ,'CSF_DEFAULT_TASK_STATUS_PERSONAL' ,'CSF_DEFAULT_TASK_TYPE_PERSONAL' ,'CSF:DEFAULT DEBRIEF BUSINESS PROCESS'
546 ,'CSF:DEFAULT DEBRIEF TRANSACTION TYPE','INV:EXPENSE_TO_ASSET_TRANSFER' ,'JTF_PROFILE_DEFAULT_CURRENCY' ,'CSF_DEFAULT_TASK_ASSIGNED_STATUS'
547 ,'JTF_TASK_DEFAULT_TASK_STATUS' ,'JTF_TASK_DEFAULT_TASK_PRIORITY' ,'CSFW_PLANNED_TASK_WINDOW'
548 ,'CS_SR_CONTACT_MANDATORY' ,'CSM_RESTRICT_DEBRIEF' ,'CSM_RESTRICT_ORDERS' ,'CSM_RESTRICT_TRANSFERS' ,'JTF_TASK_DEFAULT_TASK_TYPE'
549 ,'CSM_ACCOUNT_MESSAGE_INTERCEPTION' ,'CSM_AS_DATA_DOWNLOAD_INTERVAL'
550 ,'CSM_AS_DATA_UPLOAD_INTERVAL' ,'CSM_ENABLE_AS_STATUS_NFN' ,'CSM_NFN_SYNC_ERROR' ,'CSM_NOTIFY_DEFERRED'
551 ,'CSF_MANDATORY_LABOR_DEBRIEF' ,'CSF_MANDATORY_RESOLUTION_CODE'
552 ,'CSF_DEFAULT_LABOR_DEBRIEF_DATETIME' ,'CSF_RESTRICT_SERVICE_REQUEST_CREATION_ TO_ SCHEDULED_SITES' ,'CSM_WIRELESS_URL'
553 ,'CSM_ONLINE_ACCESS' ,'CSF:DEFAULT DEBRIEF BUSINESS PROCESS'
554 ,'CSF_DEFAULT_LABOR_DEBRIEF_DATETIME' ,'CSF_DEFAULT_DEBRIEF_SAC_TRAVEL' ,'CSM_ALLOW_FREE_FORM_IB' ,'CSF: MANDATORY_LABOR_DEBRIEF'
555 ,'CSF_DEBRIEF_LABOR_SAC' ,'CSF_DEBRIEF_TRAVEL_SAC'
556 ,'JTF_NTS_NOTE_STATUS' ,'CSM_FREE_FORM_ITEM' ,'CSF_DEBRIEF_SERVICE_DATE' ,'CSM_DIS_TASK_SCHED_CONFLICT_WARNING'
557 ,'JTF_NTS_NOTE_TYPE','CSF_DEBRIEF_RESTRICT_INSTANCE' ,'CSF_ENABLE_RETURN_ROUTING_DBF','CSP_PART_REQ_DEF_SHIP_TO'
558 ,'CSF_GOOGLE_MAP_KEY','CSF_GOOGLE_MAP_CLIENT_ID','CSF_GOOGLE_MAP_ADDENDUM','CSM_CUSTOMIZATION_KEY','CSM_DFLT_TASK_STATUS_SYNC_UPD')
559 AND NVL(opt.start_date_active, SYSDATE) <= SYSDATE
560 AND NVL(opt.end_date_active, SYSDATE) >= SYSDATE
561 AND NOT EXISTS(SELECT 1 FROM csm_profile_option_values_acc acc WHERE acc.application_id = opt.application_id AND acc.profile_option_id = opt.profile_option_id)
562 AND au.enabled='Y'
563 */
564
565 END CSM_PROFILE_EVENT_PKG;