eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
PACKAGE BODY: APPS.CSM_PROFILE_EVENT_PKG

Source


1 PACKAGE BODY CSM_PROFILE_EVENT_PKG AS
2 /* $Header: csmeprfb.pls 120.22.12010000.2 2008/11/17 11:34:21 trajasek ship $ */
3 
4 g_pub_item VARCHAR2(30) := 'CSF_M_PROFILES';
5 
6 FUNCTION get_all_omfs_resp_palm_users(p_responsibility_id IN NUMBER)
7 RETURN 	 asg_download.user_list
8 IS
9 i NUMBER;
10 l_all_omfs_palm_users_list asg_download.user_list;
11 
12 CURSOR l_omfs_resp_palm_users_csr(p_resp_id IN number)
13 IS
14 SELECT au.user_id
15 FROM   asg_user_pub_resps aupr,
16        asg_user au
17 WHERE  aupr.pub_name = 'SERVICEP'
18 AND    aupr.responsibility_id = p_resp_id
19 AND    au.user_name = aupr.user_name;
20 
21 BEGIN
22   i := 0;
23   FOR r_omfs_resp_palm_users_rec IN l_omfs_resp_palm_users_csr(p_responsibility_id) LOOP
24   		i := i + 1;
25         l_all_omfs_palm_users_list(i) := r_omfs_resp_palm_users_rec.user_id;
26   END LOOP;
27 
28   RETURN l_all_omfs_palm_users_list;
29 
30 END get_all_omfs_resp_palm_users;
31 
32 PROCEDURE insert_profiles_acc(p_access_id IN number, p_user_id IN number, p_application_id IN number,
33                               p_profile_option_id IN number, p_level_id IN number,
34                               p_level_value IN number, p_level_value_application_id IN number,
35                               p_profile_option_value IN varchar2, p_creation_date IN date)
36 IS
37 BEGIN
38   INSERT INTO csm_profile_option_values_acc(access_id,
39                                             user_id,
40                                             application_id,
41                                             profile_option_id,
42                                             level_id,
43                                             level_value,
44                                             level_value_application_id,
45                                             profile_option_value,
46                                             created_by,
47                                             creation_date,
48                                             last_updated_by,
49                                             last_update_date,
50                                             last_update_login
51                                             )
52                                     VALUES (p_access_id,
53                                             p_user_id,
54                                             p_application_id,
55                                             p_profile_option_id,
56                                             p_level_id,
57                                             p_level_value,
58                                             p_level_value_application_id,
59                                             p_profile_option_value,
60                                             fnd_global.user_id,
61                                             p_creation_date,
62                                             fnd_global.user_id,
63                                             p_creation_date,
64                                             fnd_global.login_id
65                                             );
66 
67 EXCEPTION
68  WHEN OTHERS THEN
69     RAISE;
70 END insert_profiles_acc;
71 
72 /**
73 Refreshes the CSM_PROFILE_VALUES_ACC table, and marks dirty for users accordingly
74 -- MODIFICATION HISTORY
75 -- Person      Date    Comments
76 -- Anurag     09/23/02 Added conditions for JTM_CREDIT_CARD_ENABLED in the cursor
77                        where clauses
78 */
79 PROCEDURE Refresh_Acc (p_status OUT NOCOPY VARCHAR2,
80                        p_message OUT NOCOPY VARCHAR2)
81 IS
82 PRAGMA AUTONOMOUS_TRANSACTION;
83 l_pub_item          varchar2(30) := 'CSF_M_PROFILES';
84 l_prog_update_date  jtm_con_request_data.last_run_date%TYPE;
85 l_access_id         jtm_fnd_lookups_acc.access_id%TYPE;
86 l_user_id           fnd_user.user_id%TYPE;
87 l_resp_id           fnd_responsibility.responsibility_id%TYPE;
88 l_app_id            fnd_application.application_id%TYPE;
89 l_markdirty         boolean;
90 l_all_omfs_palm_user_list asg_download.user_list;
91 l_null_user_list          asg_download.user_list;
92 l_single_access_id_list   asg_download.access_list;
93 --a null list
94 l_null_access_list        asg_download.access_list;
95 l_run_date  date;
96 l_sqlerrno  varchar2(20);
97 l_sqlerrmsg varchar2(2000);
98 
99 CURSOR l_last_run_date_csr(p_pub_item IN varchar2)
100 IS
101 SELECT nvl(last_run_date, (sysdate - 365*50))
102 FROM   jtm_con_request_data
103 WHERE  package_name   = 'CSM_PROFILE_EVENT_PKG'
104 AND    procedure_name = 'REFRESH_ACC';
105 
106 --Bug 5257429
107 /*WHENEVER A NEW PROFILE IS ADDED TO INSERT CURSOR, PLEASE DON'T FORGET TO ADD
108 THAT PROFILE TO C_PURGE CURSOR WHICH WILL OTHERWISE REMOVE IT*/
109 --Cursor to insert all profiles(without profile value)
110 CURSOR l_profiles_wovalue_ins_csr
111 IS
112 SELECT csm_profiles_acc_s.NEXTVAL as ACCESS_ID, au.user_id ,
113        opt.profile_option_id,  opt.application_id
114 FROM   fnd_profile_options opt,
115        ASG_USER au
116 WHERE (opt.profile_option_name = 'CSF_M_RECIPIENTS_BOUNDARY'
117   OR opt.profile_option_name = 'CSF_MOBILE_TASK_TIMES_UPDATABLE'
118   OR opt.profile_option_name = 'CSF_DEBRIEF_OVERLAPPING_LABOR'
119   OR opt.profile_option_name  = 'INC_DEFAULT_INCIDENT_SEVERITY'
120   OR opt.profile_option_name  = 'INC_DEFAULT_INCIDENT_URGENCY'
121   OR opt.profile_option_name  = 'JTF_TIME_UOM_CLASS'
122   OR opt.profile_option_name  = 'ICX_PREFERRED_CURRENCY'
123   OR opt.profile_option_name  = 'JTF_TASK_DEFAULT_ASSIGNEE_STATUS'
124   OR opt.profile_option_name  = 'CS_SR_RESTRICT_IB'
125   OR opt.profile_option_name  = 'SERVER_TIMEZONE_ID'
126   OR opt.profile_option_name  = 'CLIENT_TIMEZONE_ID'
127   OR opt.profile_option_name  = 'CSF_BUSINESS_PROCESS'
128   OR opt.profile_option_name  = 'CSM_SEARCH_RESULT_SET_SIZE'
129   OR opt.profile_option_name  = 'CSM_IB_ITEMS_AT_LOCATION'
130   OR opt.profile_option_name  = 'CSM_ITEM_CATEGORY_SET_FILTER'
131   OR opt.profile_option_name  = 'CSM_ITEM_CATEGORY_FILTER'
132   OR opt.profile_option_name  = 'CS_INV_VALIDATION_ORG'
133   OR opt.profile_option_name  = 'INC_DEFAULT_INCIDENT_TYPE'
134   OR opt.profile_option_name  = 'INC_DEFAULT_INCIDENT_STATUS'
135   OR opt.profile_option_name  = 'CSM_ENABLE_CREATE_SR'
136   OR opt.profile_option_name  = 'CSM_ENABLE_CREATE_TASK'
137   OR opt.profile_option_name  = 'CSM_ENABLE_UPDATE_ASSIGNMENTS'
138   OR opt.profile_option_name  = 'CSM_ENABLE_VIEW_CUST_PRODUCTS'
139   OR opt.profile_option_name  = 'CSM_MAX_READINGS_PER_COUNTER'
140   OR opt.profile_option_name = 'CSF_RETURN_REASON'
141   OR opt.profile_option_name  = 'CSFW_DEFAULT_DISTANCE_UNIT'
142   OR opt.profile_option_name  = 'CSF_CAPTURE_TRAVEL'
143   OR opt.profile_option_name  = 'CSM_LABOR_LINE_TOTAL_CHECK'   --new CSM profile, obsoleted CSL profile
144   OR opt.profile_option_name  = 'ICX_DATE_FORMAT_MASK'
145   OR opt.profile_option_name  = 'JTM_TIMEPICKER_FORMAT'
146   OR opt.profile_option_name  = 'CSM_TIME_REASONABILITY_CHECK_APPLY' --new CSM profile, obsoleted CSL profile
147   OR opt.profile_option_name  = 'ICX_NUMERIC_CHARACTERS'
148   OR opt.profile_option_name  = 'CSF_LABOR_DEBRIEF_DEFAULT_UOM'
149   OR opt.profile_option_name  = 'CSF_UOM_HOURS'
150   OR opt.profile_option_name  = 'CSZ_DEFAULT_CONTACT_BY'
151   OR opt.profile_option_name  = 'HZ_REF_TERRITORY'
152   OR opt.profile_option_name  = 'HZ_REF_LANG'
153   OR opt.profile_option_name  = 'HZ_LANG_FOR_COUNTRY_DISPLAY'
154   OR opt.profile_option_name  = 'CSM_MAX_ATTACHMENT_SIZE'  --For PPC
155   OR opt.profile_option_name  = 'CSF_UOM_MINUTES'
156   OR opt.profile_option_name  = 'CSF_DEFAULT_TASK_STATUS_PERSONAL'
157   OR opt.profile_option_name  = 'CSF_DEFAULT_TASK_TYPE_PERSONAL'
158   OR opt.profile_option_name  = 'CSF:DEFAULT DEBRIEF BUSINESS PROCESS'
159   OR opt.profile_option_name  = 'CSF:DEFAULT DEBRIEF TRANSACTION TYPE'
160   OR opt.profile_option_name  = 'INV:EXPENSE_TO_ASSET_TRANSFER'
161   OR opt.profile_option_name  = 'JTF_PROFILE_DEFAULT_CURRENCY'
162   OR opt.profile_option_name  = 'CSF_DEFAULT_TASK_ASSIGNED_STATUS'
163   OR opt.profile_option_name  = 'JTF_TASK_DEFAULT_TASK_STATUS'
164   OR opt.profile_option_name  = 'JTF_TASK_DEFAULT_TASK_PRIORITY'
165   OR opt.profile_option_name  = 'CSFW_PLANNED_TASK_WINDOW'
166   OR opt.profile_option_name  = 'CS_SR_CONTACT_MANDATORY'
167   OR opt.profile_option_name  = 'CSM_RESTRICT_DEBRIEF'
168   OR opt.profile_option_name  = 'CSM_RESTRICT_ORDERS'
169   OR opt.profile_option_name  = 'CSM_RESTRICT_TRANSFERS'
170   OR opt.profile_option_name  = 'JTF_TASK_DEFAULT_TASK_TYPE'
171   )
172 AND NVL(opt.start_date_active, SYSDATE) <= SYSDATE
173 AND NVL(opt.end_date_active,   SYSDATE) >= SYSDATE
174 AND NOT EXISTS
175 (SELECT 1
176  FROM csm_profile_option_values_acc acc
177  WHERE acc.profile_option_id  = opt.profile_option_id
178  AND acc.application_id       = opt.application_id
179  AND acc.user_id              = au.user_id
180 );
181 -- get the profiles with values to be inserted
182 CURSOR l_profiles_ins_csr(p_last_upd_date date,
183                           p_csm_appl_id fnd_application.application_id%TYPE,
184                           p_csm_resp_id fnd_responsibility.responsibility_id%TYPE)
185 IS
186 SELECT val.application_id, val.profile_option_id, val.level_id, val.level_value,
187        val.level_value_application_id, val.profile_option_value, opt.profile_option_name
188 FROM   fnd_profile_options opt,
189        fnd_profile_option_values val
190 WHERE (opt.profile_option_name = 'CSF_M_RECIPIENTS_BOUNDARY'
191 --  OR opt.profile_option_name = 'CSF_M_AGENDA_ALLOWCHANGESCOMPLETEDTASK'   ---end_dated
192   OR opt.profile_option_name = 'CSF_MOBILE_TASK_TIMES_UPDATABLE'
193   OR opt.profile_option_name = 'CSF_DEBRIEF_OVERLAPPING_LABOR'
194   OR opt.profile_option_name  = 'INC_DEFAULT_INCIDENT_SEVERITY'
195   OR opt.profile_option_name  = 'INC_DEFAULT_INCIDENT_URGENCY'
196   OR opt.profile_option_name  = 'JTF_TIME_UOM_CLASS'
197   OR opt.profile_option_name  = 'ICX_PREFERRED_CURRENCY'
198   OR opt.profile_option_name  = 'JTF_TASK_DEFAULT_ASSIGNEE_STATUS'
199   OR opt.profile_option_name  = 'CS_SR_RESTRICT_IB'
200   OR opt.profile_option_name  = 'SERVER_TIMEZONE_ID'
201   OR opt.profile_option_name  = 'CLIENT_TIMEZONE_ID'
202   OR opt.profile_option_name  = 'CSF_BUSINESS_PROCESS'
203   OR opt.profile_option_name  = 'CSM_SEARCH_RESULT_SET_SIZE'
204   OR opt.profile_option_name  = 'CSM_IB_ITEMS_AT_LOCATION'
205   OR opt.profile_option_name  = 'CSM_ITEM_CATEGORY_SET_FILTER'
206   OR opt.profile_option_name  = 'CSM_ITEM_CATEGORY_FILTER'
207   OR opt.profile_option_name  = 'CS_INV_VALIDATION_ORG'
208   OR opt.profile_option_name  = 'INC_DEFAULT_INCIDENT_TYPE'
209   OR opt.profile_option_name  = 'INC_DEFAULT_INCIDENT_STATUS'
210   OR opt.profile_option_name  = 'CSM_ENABLE_CREATE_SR'
211   OR opt.profile_option_name  = 'CSM_ENABLE_CREATE_TASK'
212   OR opt.profile_option_name  = 'CSM_ENABLE_UPDATE_ASSIGNMENTS'
213   OR opt.profile_option_name  = 'CSM_ENABLE_VIEW_CUST_PRODUCTS'
214   OR opt.profile_option_name  = 'CSM_MAX_READINGS_PER_COUNTER'
215   OR opt.profile_option_name = 'CSF_RETURN_REASON'
216   --R 12 updates
217   OR opt.profile_option_name  = 'CSFW_DEFAULT_DISTANCE_UNIT'
218   OR opt.profile_option_name  = 'CSF_CAPTURE_TRAVEL'
219   OR opt.profile_option_name  = 'CSM_LABOR_LINE_TOTAL_CHECK'   --new CSM profile, obsoleted CSL profile
220   OR opt.profile_option_name  = 'ICX_DATE_FORMAT_MASK'
221   OR opt.profile_option_name  = 'JTM_TIMEPICKER_FORMAT'
222   OR opt.profile_option_name  = 'CSM_TIME_REASONABILITY_CHECK_APPLY' --new CSM profile, obsoleted CSL profile
223   OR opt.profile_option_name  = 'ICX_NUMERIC_CHARACTERS'
224   OR opt.profile_option_name  = 'CSF_LABOR_DEBRIEF_DEFAULT_UOM'
225   OR opt.profile_option_name  = 'CSF_UOM_HOURS'
226   OR opt.profile_option_name  = 'CSZ_DEFAULT_CONTACT_BY'
227   OR opt.profile_option_name  = 'HZ_REF_TERRITORY'
228   OR opt.profile_option_name  = 'HZ_REF_LANG'
229   OR opt.profile_option_name  = 'HZ_LANG_FOR_COUNTRY_DISPLAY'
230   OR opt.profile_option_name  = 'CSM_MAX_ATTACHMENT_SIZE'  --For PPC
231   OR opt.profile_option_name  = 'CSF_UOM_MINUTES'
232   OR opt.profile_option_name  = 'CSF_DEFAULT_TASK_STATUS_PERSONAL'
233   OR opt.profile_option_name  = 'CSF_DEFAULT_TASK_TYPE_PERSONAL'
234   OR opt.profile_option_name  = 'CSF:DEFAULT DEBRIEF BUSINESS PROCESS'
235   OR opt.profile_option_name  = 'CSF:DEFAULT DEBRIEF TRANSACTION TYPE'
236   OR opt.profile_option_name  = 'INV:EXPENSE_TO_ASSET_TRANSFER'
237   OR opt.profile_option_name  = 'JTF_PROFILE_DEFAULT_CURRENCY'
238   OR opt.profile_option_name  = 'CSF_DEFAULT_TASK_ASSIGNED_STATUS'
239   OR opt.profile_option_name  = 'JTF_TASK_DEFAULT_TASK_STATUS'
240   OR opt.profile_option_name  = 'JTF_TASK_DEFAULT_TASK_PRIORITY'
241   OR opt.profile_option_name  = 'CSFW_PLANNED_TASK_WINDOW'
242   OR opt.profile_option_name  = 'CS_SR_CONTACT_MANDATORY'
243   OR opt.profile_option_name  = 'CSM_RESTRICT_DEBRIEF'
244   OR opt.profile_option_name  = 'CSM_RESTRICT_ORDERS'
245   OR opt.profile_option_name  = 'CSM_RESTRICT_TRANSFERS'
246   OR opt.profile_option_name  = 'JTF_TASK_DEFAULT_TASK_TYPE'
247   )
248 AND val.application_id = opt.application_id
249 AND val.profile_option_id = opt.profile_option_id
250 AND NVL(opt.start_date_active, SYSDATE) <= SYSDATE
251 AND NVL(opt.end_date_active,   SYSDATE) >= SYSDATE
252 AND (  (val.level_id = 10001)
253     OR (val.level_id = 10004 AND val.level_value IN (SELECT USER_ID FROM ASG_USER WHERE ENABLED= 'Y'))
254     OR (val.level_id = 10002 AND val.level_value = p_csm_appl_id)
255     OR (val.level_id = 10003 AND val.level_value = p_csm_resp_id)
256     )
257 AND NOT EXISTS
258 (SELECT 1
259  FROM csm_profile_option_values_acc acc
260  WHERE acc.profile_option_id = val.profile_option_id
261  AND acc.application_id = val.application_id
262  AND acc.level_id = val.level_id
263  AND acc.level_value = val.level_value
264  AND acc.level_id <> 10003
265  UNION
266  SELECT 1
267  FROM csm_profile_option_values_acc acc,
268       fnd_responsibility resp
269  WHERE acc.profile_option_id = val.profile_option_id
270  AND acc.application_id = val.application_id
271  AND acc.level_id = val.level_id
272  AND acc.level_value = val.level_value
273  AND acc.level_id = 10003
274  AND acc.level_value = resp.responsibility_id
275  AND acc.level_value_application_id = resp.application_id
276  AND SYSDATE BETWEEN nvl(resp.start_date, sysdate) AND nvl(resp.end_date, sysdate)
277  )
278  ORDER BY val.application_id, val.profile_option_id, val.level_id desc ;
279 
280 -- get the profiles to be updated
281 CURSOR l_profiles_upd_csr
282 IS
283 SELECT val.application_id,
284   val.profile_option_id,
285   val.level_id,
286   val.level_value,
287   val.level_value_application_id,
288   val.profile_option_value,
289   acc.user_id,
290   acc.access_id
291 FROM fnd_profile_option_values val,
292      csm_profile_option_values_acc acc
293 WHERE val.profile_option_id = acc.profile_option_id
294  AND val.application_id     = acc.application_id
295  AND acc.level_id           = val.level_id
296  AND acc.level_value        = val.level_value
297  AND NVL(val.profile_option_value,-1) <> NVL(acc.profile_option_value,-1);
298 
299 -- get the profiles to be deleted
300 CURSOR l_profiles_del_csr(p_last_upd_date date)
301 IS
302 SELECT acc.access_id, acc.application_id, acc.profile_option_id, acc.level_id, acc.level_value,
303        acc.level_value_application_id, acc.profile_option_value, opt.profile_option_name,
304        acc.user_id
305 FROM  csm_profile_option_values_acc acc,
306       fnd_profile_options opt
307 WHERE acc.profile_option_id = opt.profile_option_id
311 AND (opt.profile_option_name = 'CSF_M_RECIPIENTS_BOUNDARY'
308 AND   acc.application_id = opt.application_id
309 AND acc.level_id IS NOT NULL
310 AND acc.level_value IS NOT NULL
312 --  OR opt.profile_option_name = 'CSF_M_AGENDA_ALLOWCHANGESCOMPLETEDTASK'
313   OR opt.profile_option_name = 'CSF_MOBILE_TASK_TIMES_UPDATABLE'
314   OR opt.profile_option_name = 'CSF_DEBRIEF_OVERLAPPING_LABOR'
315   OR opt.profile_option_name  = 'INC_DEFAULT_INCIDENT_SEVERITY'
316   OR opt.profile_option_name  = 'INC_DEFAULT_INCIDENT_URGENCY'
317   OR opt.profile_option_name  = 'JTF_TIME_UOM_CLASS'
318   OR opt.profile_option_name  = 'ICX_PREFERRED_CURRENCY'
319   OR opt.profile_option_name  = 'JTF_TASK_DEFAULT_ASSIGNEE_STATUS'
320   OR opt.profile_option_name  = 'CS_SR_RESTRICT_IB'
321   OR opt.profile_option_name  = 'SERVER_TIMEZONE_ID'
322   OR opt.profile_option_name  = 'CLIENT_TIMEZONE_ID'
323   OR opt.profile_option_name  = 'CSF_BUSINESS_PROCESS'
324   OR opt.profile_option_name  = 'CSM_SEARCH_RESULT_SET_SIZE'
325   OR opt.profile_option_name  = 'CSM_IB_ITEMS_AT_LOCATION'
326   OR opt.profile_option_name  = 'CSM_ITEM_CATEGORY_SET_FILTER'
327   OR opt.profile_option_name  = 'CSM_ITEM_CATEGORY_FILTER'
328   OR opt.profile_option_name  = 'CS_INV_VALIDATION_ORG'
329   OR opt.profile_option_name  = 'INC_DEFAULT_INCIDENT_TYPE'
330   OR opt.profile_option_name  = 'INC_DEFAULT_INCIDENT_STATUS'
331   OR opt.profile_option_name  = 'CSM_ENABLE_CREATE_SR'
332   OR opt.profile_option_name  = 'CSM_ENABLE_CREATE_TASK'
333   OR opt.profile_option_name  = 'CSM_ENABLE_UPDATE_ASSIGNMENTS'
334   OR opt.profile_option_name  = 'CSM_ENABLE_VIEW_CUST_PRODUCTS'
335   OR opt.profile_option_name  = 'CSM_MAX_READINGS_PER_COUNTER'
336   OR opt.profile_option_name = 'CSF_RETURN_REASON'
337   --R 12 updates
338   OR opt.profile_option_name  = 'CSFW_DEFAULT_DISTANCE_UNIT'
339   OR opt.profile_option_name  = 'CSF_CAPTURE_TRAVEL'
340   OR opt.profile_option_name  = 'CSM_LABOR_LINE_TOTAL_CHECK'
341   OR opt.profile_option_name  = 'ICX_DATE_FORMAT_MASK'
342   OR opt.profile_option_name  = 'JTM_TIMEPICKER_FORMAT'
343   OR opt.profile_option_name  = 'CSM_TIME_REASONABILITY_CHECK_APPLY'
344   OR opt.profile_option_name  = 'ICX_NUMERIC_CHARACTERS'
345   OR opt.profile_option_name  = 'CSF_LABOR_DEBRIEF_DEFAULT_UOM'
346   OR opt.profile_option_name  = 'CSF_UOM_HOURS'
347   OR opt.profile_option_name  = 'CSZ_DEFAULT_CONTACT_BY'
348   OR opt.profile_option_name  = 'HZ_REF_TERRITORY'
349   OR opt.profile_option_name  = 'HZ_REF_LANG'
350   OR opt.profile_option_name  = 'HZ_LANG_FOR_COUNTRY_DISPLAY'
351   OR opt.profile_option_name  = 'CSM_MAX_ATTACHMENT_SIZE'  --For PPC
352   OR opt.profile_option_name  = 'CSF_UOM_MINUTES'
353   OR opt.profile_option_name  = 'CSF_DEFAULT_TASK_STATUS_PERSONAL'
354   OR opt.profile_option_name  = 'CSF_DEFAULT_TASK_TYPE_PERSONAL'
355   OR opt.profile_option_name  = 'CSF:DEFAULT DEBRIEF BUSINESS PROCESS'
356   OR opt.profile_option_name  = 'CSF:DEFAULT DEBRIEF TRANSACTION TYPE'
357   OR opt.profile_option_name  = 'INV:EXPENSE_TO_ASSET_TRANSFER'
358   OR opt.profile_option_name  = 'JTF_PROFILE_DEFAULT_CURRENCY'
359   OR opt.profile_option_name  = 'CSF_DEFAULT_TASK_ASSIGNED_STATUS'
360   OR opt.profile_option_name  = 'JTF_TASK_DEFAULT_TASK_STATUS'
361   OR opt.profile_option_name  = 'JTF_TASK_DEFAULT_TASK_PRIORITY'
362   OR opt.profile_option_name  = 'CSFW_PLANNED_TASK_WINDOW'
363   OR opt.profile_option_name  = 'CS_SR_CONTACT_MANDATORY'
364   OR opt.profile_option_name  = 'CSM_RESTRICT_DEBRIEF'
365   OR opt.profile_option_name  = 'CSM_RESTRICT_ORDERS'
366   OR opt.profile_option_name  = 'CSM_RESTRICT_TRANSFERS'
367   OR opt.profile_option_name  = 'JTF_TASK_DEFAULT_TASK_TYPE'
368   )
369 AND NOT EXISTS
370 (SELECT 1
371  FROM fnd_profile_option_values val
372  WHERE val.application_id = acc.application_id
373  AND val.profile_option_id = acc.profile_option_id
374  AND val.level_id = acc.level_id
375  AND val.level_value = acc.level_value
376  AND val.level_id <> 10003
377  UNION
378  SELECT 1
379  FROM fnd_profile_option_values val,
380       fnd_responsibility resp
381  WHERE val.application_id = acc.application_id
382  AND val.profile_option_id = acc.profile_option_id
383  AND val.level_id = acc.level_id
384  AND val.level_value = acc.level_value
385  AND val.level_id = 10003
386  AND val.level_value = resp.responsibility_id
387  AND val.level_value_application_id = resp.application_id
388  AND SYSDATE BETWEEN nvl(resp.start_date, sysdate) AND nvl(resp.end_date, sysdate)
389  )
390  ORDER BY acc.profile_option_id, acc.level_id desc
391  FOR UPDATE OF acc.profile_option_value, acc.level_id, acc.level_value nowait
392  ;
393 
394 --Bug 5257429
395 CURSOR c_purge IS
396  SELECT /*+ index(ACC CSM_PROFILE_VALUES_ACC_N1) */
397         ACC.APPLICATION_ID,
398         ACC.PROFILE_OPTION_ID
399  FROM csm_profile_option_values_acc ACC
400  WHERE NOT EXISTS( SELECT 1
401                    FROM  FND_PROFILE_OPTIONS OPT
402                    WHERE OPT.PROFILE_OPTION_ID = ACC.PROFILE_OPTION_ID
403                    AND   OPT.APPLICATION_ID = ACC.APPLICATION_ID
404 		   AND   OPT.PROFILE_OPTION_NAME IN
405                    ( 'CSF_M_RECIPIENTS_BOUNDARY'
406                    , 'CSF_MOBILE_TASK_TIMES_UPDATABLE'
407                    , 'CSF_DEBRIEF_OVERLAPPING_LABOR'
408                    , 'INC_DEFAULT_INCIDENT_SEVERITY'
409                    , 'INC_DEFAULT_INCIDENT_URGENCY'
410                    , 'JTF_TIME_UOM_CLASS'
411                    , 'ICX_PREFERRED_CURRENCY'
412                    , 'JTF_TASK_DEFAULT_ASSIGNEE_STATUS'
413                    , 'CS_SR_RESTRICT_IB'
417                    , 'CSM_SEARCH_RESULT_SET_SIZE'
414                    , 'SERVER_TIMEZONE_ID'
415                    , 'CLIENT_TIMEZONE_ID'
416                    , 'CSF_BUSINESS_PROCESS'
418                    , 'CSM_IB_ITEMS_AT_LOCATION'
419                    , 'CSM_ITEM_CATEGORY_SET_FILTER'
420                    , 'CSM_ITEM_CATEGORY_FILTER'
421                    , 'CS_INV_VALIDATION_ORG'
422                    , 'INC_DEFAULT_INCIDENT_TYPE'
423                    , 'INC_DEFAULT_INCIDENT_STATUS'
424                    , 'CSM_ENABLE_CREATE_SR'
425                    , 'CSM_ENABLE_CREATE_TASK'
426                    , 'CSM_ENABLE_UPDATE_ASSIGNMENTS'
427                    , 'CSM_ENABLE_VIEW_CUST_PRODUCTS'
428                    , 'CSM_MAX_READINGS_PER_COUNTER'
429                    , 'CSF_RETURN_REASON'
430                    , 'CSFW_DEFAULT_DISTANCE_UNIT'
431                    , 'CSF_CAPTURE_TRAVEL'
432                    , 'CSM_LABOR_LINE_TOTAL_CHECK'
433                    , 'ICX_DATE_FORMAT_MASK'
434                    , 'JTM_TIMEPICKER_FORMAT'
435                    , 'CSM_TIME_REASONABILITY_CHECK_APPLY'
436                    , 'ICX_NUMERIC_CHARACTERS'
437                    , 'CSF_LABOR_DEBRIEF_DEFAULT_UOM'
438                    , 'CSF_UOM_HOURS'
439                    , 'CSZ_DEFAULT_CONTACT_BY'
440                    , 'HZ_REF_TERRITORY'
441                    , 'HZ_REF_LANG'
442                    , 'HZ_LANG_FOR_COUNTRY_DISPLAY'
443                    , 'CSM_MAX_ATTACHMENT_SIZE'  --For PPC
444                    , 'CSF_UOM_MINUTES'
445                    , 'CSF_DEFAULT_TASK_STATUS_PERSONAL'
446                    , 'CSF_DEFAULT_TASK_TYPE_PERSONAL'
447                    , 'CSF:DEFAULT DEBRIEF BUSINESS PROCESS'
448                    , 'CSF:DEFAULT DEBRIEF TRANSACTION TYPE'
449                    , 'INV:EXPENSE_TO_ASSET_TRANSFER'
450                    , 'JTF_PROFILE_DEFAULT_CURRENCY'
451                    , 'CSF_DEFAULT_TASK_ASSIGNED_STATUS'
452                    , 'JTF_TASK_DEFAULT_TASK_STATUS'
453                    , 'JTF_TASK_DEFAULT_TASK_PRIORITY'
454                    , 'CSFW_PLANNED_TASK_WINDOW'
455                    , 'CS_SR_CONTACT_MANDATORY'
456                    , 'CSM_RESTRICT_DEBRIEF'
457                    , 'CSM_RESTRICT_ORDERS'
458                    , 'CSM_RESTRICT_TRANSFERS'
459                    , 'JTF_TASK_DEFAULT_TASK_TYPE'
460                  )
461                  AND NVL(OPT.start_date_active, SYSDATE) <= SYSDATE
462                  AND NVL(OPT.end_date_active,   SYSDATE) >= SYSDATE
463                  );
464 
465 TYPE PURGE_TAB IS TABLE OF c_purge%ROWTYPE;
466 l_tab PURGE_TAB;
467 
468 --Bug 5257429
469 CURSOR c_get_accessID(b_app_id NUMBER, b_prfopt_id NUMBER)
470 IS
471  SELECT ACC.ACCESS_ID,ACC.USER_ID
472  FROM   csm_profile_option_values_acc ACC
473  WHERE ACC.APPLICATION_ID= b_app_id
474  AND   ACC.PROFILE_OPTION_ID= b_prfopt_id;
475 
476 
477 CURSOR l_get_old_profile_csr (p_profile_option_id IN number, p_user_id IN number)
478 IS
479 SELECT access_id, profile_option_value, level_id
480 FROM csm_profile_option_values_acc
481 WHERE profile_option_id = p_profile_option_id
482 AND user_id = p_user_id
483 ORDER BY level_id desc
484 FOR UPDATE OF profile_option_value, level_id, level_value, last_update_date nowait;
485 
486 -- get the value at the next profile level
487 CURSOR c_profiles_csr ( p_profile_option_name VARCHAR2,
488                        p_user_id IN NUMBER DEFAULT NULL,
489                        p_csm_resp_id IN NUMBER DEFAULT NULL,
490                        p_csm_app_id IN NUMBER DEFAULT NULL
491                       )
492 IS
493 SELECT val.profile_option_value,
494        val.level_id,
495        val.level_value,
496        val.level_value_application_id,
497        val.profile_option_id
498 FROM fnd_profile_options       opt,
499      fnd_profile_option_values val
500 WHERE opt.profile_option_name = p_profile_option_name
501 AND NVL(opt.start_date_active, SYSDATE) <= SYSDATE
502 AND NVL(opt.end_date_active,   SYSDATE) >= SYSDATE
503 AND opt.application_id      = val.application_id
504 AND opt.profile_option_id   = val.profile_option_id
505 AND ( ( val.level_id      = 10001
506       )
507         OR
508       ( val.level_id    = 10002    AND
509         val.level_value = p_csm_app_id
510       ) OR
511       ( val.level_id    = 10003    AND
512         val.level_value = p_csm_resp_id
513       ) OR
514       ( val.level_id    = 10004    AND
515         val.level_value = p_user_id
516        )
517     )
518 ORDER BY val.level_id DESC;
519 
520 r_profiles_rec c_profiles_csr%ROWTYPE;
521 
522 CURSOR c_profile ( p_profile_option_name IN VARCHAR2,
523                    p_user_level_value  IN  NUMBER DEFAULT NULL,
524                    p_csm_resp_id IN NUMBER default NULL,
525                    p_csm_app_id IN NUMBER DEFAULT NULL
526                  ) IS
527 SELECT val.profile_option_value,
528        val.level_id,
529        val.level_value,
530        val.level_value_application_id
531 FROM fnd_profile_options       opt,
532      fnd_profile_option_values val
533 WHERE opt.profile_option_name = p_profile_option_name
534 AND NVL(opt.start_date_active, SYSDATE) <= SYSDATE
535 AND NVL(opt.end_date_active,   SYSDATE) >= SYSDATE
536 AND opt.application_id      = val.application_id
540       OR
537 AND opt.profile_option_id   = val.profile_option_id
538 AND ( ( val.level_id      = 10001
539       )
541      ( val.level_id    = 10002    AND
542        val.level_value = p_csm_app_id
543      ) OR
544      ( val.level_id    = 10003    AND
545       val.level_value = p_csm_resp_id
546      ) OR
547      ( val.level_id    = 10004    AND
548        val.level_value = p_user_level_value
549      )
550    )
551 ORDER BY val.level_id DESC;
552 
553 cursor c_csm_appl is
554 SELECT APPLICATION_ID
555 FROM fnd_application
556 where application_short_name = 'CSM';
557 
558 cursor c_csm_resp(c_user_id NUMBER) is
559 select RESPONSIBILITY_ID
560 from   ASG_USER
561 where  USER_ID = c_user_id;
562 
563 CURSOR c_profile_seq IS
564 SELECT csm_profiles_acc_s.NEXTVAL
565 FROM dual;
566 
567 l_csm_appl_id                 fnd_application.application_id%TYPE;
568 l_csm_resp_id                 fnd_responsibility.responsibility_id%TYPE;
569 l_old_profile_option_value    fnd_profile_option_values.profile_option_value%TYPE;
570 l_old_level_id                fnd_profile_option_values.level_id%TYPE;
571 l_profile_option_value        fnd_profile_option_values.profile_option_value%TYPE;
572 l_level_id                    fnd_profile_option_values.level_id%TYPE;
573 l_level_value                 fnd_profile_option_values.level_value%TYPE;
574 l_level_value_application_id  fnd_profile_option_values.level_value_application_id%TYPE;
575 
576 TYPE num_tab_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
577 l_acc_tab   num_tab_type;
578 l_user_tab  num_tab_type;
579 
580 BEGIN
581  -- data program is run
582  l_run_date := SYSDATE;
583 
584  -- get last conc program update date
585  OPEN l_last_run_date_csr(g_pub_item);
586  FETCH l_last_run_date_csr INTO l_prog_update_date;
587  CLOSE l_last_run_date_csr;
588 
589  -- get csm application id
590  OPEN c_csm_appl;
591  FETCH c_csm_appl INTO l_csm_appl_id;
592  CLOSE c_csm_appl;
593 
594 
595 --Bug 5257429
596 OPEN c_purge;
597 FETCH c_purge BULK COLLECT INTO l_tab;
598 CLOSE c_purge;
599 
600 
601   CSM_UTIL_PKG.LOG('Entering DELETE to remove ' || l_tab.count||' records',
602                              'CSM_PROFILE_EVENT_PKG.REFRESH_ACC',FND_LOG.LEVEL_PROCEDURE);
603 
604   FOR I IN 1..l_tab.COUNT
605   LOOP
606     OPEN c_get_accessID(l_tab(I).APPLICATION_ID,l_tab(I).PROFILE_OPTION_ID);
607     FETCH c_get_accessID BULK COLLECT INTO l_acc_tab,l_user_tab;
608     CLOSE c_get_accessID;
609 
610     FOR J IN 1..l_user_tab.COUNT
611     LOOP
612     l_markdirty := CSM_UTIL_PKG.MakeDirtyForUser(g_pub_item,l_acc_tab(J) ,l_user_tab(J), 'D', sysdate );
613     END LOOP;
614 
615     FORALL J IN 1..l_acc_tab.COUNT
616     DELETE FROM csm_profile_option_values_acc WHERE ACCESS_ID=l_acc_tab(J);
617 
618   END LOOP;
619 
620 COMMIT;
621 
622 --- process profile wovalue inserts
623  FOR r_profiles_ins_rec IN l_profiles_wovalue_ins_csr LOOP
624 
625            -- insert into csm_profile_option_values_acc
626            insert_profiles_acc(r_profiles_ins_rec.access_id,r_profiles_ins_rec.user_id,r_profiles_ins_rec.application_id,
627                              r_profiles_ins_rec.profile_option_id, 10001,
628                              0, NULL,
629                              NULL,l_run_date);
630 
631            --mark dirty the SDQ for the user
632               l_markdirty := CSM_UTIL_PKG.MakeDirtyForUser(g_pub_item,
633                        r_profiles_ins_rec.access_id, r_profiles_ins_rec.user_id, ASG_DOWNLOAD.INS, SYSDATE);
634 
635  END LOOP; -- process profile wovalue inserts
636 
637   COMMIT;
638 
639  --process deletes
640  FOR r_profiles_del_rec IN l_profiles_del_csr(l_prog_update_date) LOOP
641 
642   -- initialize the user list
643   l_all_omfs_palm_user_list := l_null_user_list;
644   l_resp_id := -99;
645   l_app_id := -99;
646   -- set the old profile option value
647   l_old_profile_option_value := r_profiles_del_rec.profile_option_value;
648 
649   IF r_profiles_del_rec.level_id = 10004 THEN
650     l_all_omfs_palm_user_list(1) := r_profiles_del_rec.level_value;
651   ELSIF r_profiles_del_rec.level_id = 10003 THEN
652     l_resp_id := r_profiles_del_rec.level_value;
653     l_all_omfs_palm_user_list(1) := r_profiles_del_rec.user_id;
654   ELSIF r_profiles_del_rec.level_id = 10002 THEN
655     l_app_id := r_profiles_del_rec.level_value;
656     l_all_omfs_palm_user_list(1) := r_profiles_del_rec.user_id;
657   ELSE
658     -- get the specific user deleted
659     l_all_omfs_palm_user_list(1) := r_profiles_del_rec.user_id;
660   END IF;
661 
662   -- loop for all the valid omfs palm users based on profile level_id
663   FOR i IN 1..l_all_omfs_palm_user_list.count LOOP
664     l_user_id := l_all_omfs_palm_user_list(i);
665 
666 	 -- get csm responsibility id
667  	OPEN c_csm_resp(l_user_id);
668 	FETCH c_csm_resp INTO l_csm_resp_id;
669  	CLOSE c_csm_resp;
670 
671     IF csm_util_pkg.is_palm_user(l_user_id) THEN
672       OPEN c_profiles_csr(r_profiles_del_rec.profile_option_name, l_user_id, l_csm_resp_id, l_csm_appl_id);
673       FETCH c_profiles_csr INTO r_profiles_rec;
674       IF c_profiles_csr%FOUND THEN
675          IF r_profiles_rec.profile_option_value IS NULL THEN
679             r_profiles_rec.level_value := 0;
676             -- get profile at site level
677             fnd_profile.GET(NAME => r_profiles_del_rec.profile_option_name, VAL => r_profiles_rec.profile_option_value );
678             r_profiles_rec.level_id := 10001;
680          END IF;
681 
682          UPDATE csm_profile_option_values_acc
683          SET profile_option_value = r_profiles_rec.profile_option_value,
684              level_id =  r_profiles_rec.level_id,
685              level_value = r_profiles_rec.level_value,
686              level_value_application_id = r_profiles_rec.level_value_application_id,
687              last_update_date = l_run_date
688          WHERE CURRENT OF l_profiles_del_csr;
689 
690          --mark dirty the SDQ for the user
691          l_markdirty := CSM_UTIL_PKG.MakeDirtyForUser(g_pub_item,
692                             r_profiles_del_rec.access_id, l_user_id, ASG_DOWNLOAD.UPD, SYSDATE);
693       ELSE
694           --No value set for this profile at any level and hence set back to site level with value null
695          UPDATE csm_profile_option_values_acc
696          SET profile_option_value = NULL,
697              level_id =  10001,
698              level_value = 0,
699              level_value_application_id = NULL,
700              last_update_date = l_run_date
701          WHERE CURRENT OF l_profiles_del_csr;
702             --mark dirty the SDQ for the user
703          l_markdirty := CSM_UTIL_PKG.MakeDirtyForUser(g_pub_item,
704                             r_profiles_del_rec.access_id, l_user_id, ASG_DOWNLOAD.UPD, SYSDATE);
705 
706       END IF;
707       CLOSE c_profiles_csr;
708     ELSE
709          DELETE FROM csm_profile_option_values_acc WHERE profile_option_id = r_profiles_del_rec.profile_option_id
710          AND user_id = l_user_id;
711     END IF; -- if valid omfs user
712 
713   END LOOP; --palm omfs user loop
714 
715  END LOOP; -- process deletes
716   COMMIT;
717 
718   --process updates STARTS
719  FOR r_profiles_upd_rec IN l_profiles_upd_csr LOOP
720 
721             UPDATE csm_profile_option_values_acc
722             SET   profile_option_value = r_profiles_upd_rec.profile_option_value,
723                   last_update_date     = l_run_date
724             WHERE USER_ID             = r_profiles_upd_rec.user_id
725             AND   profile_option_id   = r_profiles_upd_rec.profile_option_id
726             AND   level_id            = r_profiles_upd_rec.level_id
727             AND   level_value         = r_profiles_upd_rec.level_value;
728 
729                 --mark dirty the SDQ for the user
730                 l_markdirty := CSM_UTIL_PKG.MakeDirtyForUser(g_pub_item,
731                                r_profiles_upd_rec.access_id, r_profiles_upd_rec.user_id, ASG_DOWNLOAD.UPD, SYSDATE);
732  END LOOP;
733   COMMIT;
734  --process updates ENDS
735 
736  --process inserts STARTS
737  FOR r_profiles_ins_rec IN l_profiles_ins_csr(l_prog_update_date,
738      l_csm_appl_id, l_csm_resp_id) LOOP
739 
740   -- initialize the user list
741   l_all_omfs_palm_user_list := l_null_user_list;
742 
743   IF r_profiles_ins_rec.level_id = 10004 THEN
744     l_all_omfs_palm_user_list(1) := r_profiles_ins_rec.level_value;
745   ELSIF r_profiles_ins_rec.level_id = 10003 THEN
746     -- get all the omfs palm users for the responsibility id
747 
748     l_all_omfs_palm_user_list := get_all_omfs_resp_palm_users(r_profiles_ins_rec.level_value);
749   ELSE
750     -- get all the omfs palm users
751     l_all_omfs_palm_user_list := csm_util_pkg.get_all_omfs_palm_user_list;
752 
753   END IF;
754 
755   -- loop for all the valid omfs palm users based on profile level_id
756   FOR i IN 1..l_all_omfs_palm_user_list.COUNT LOOP
757     l_user_id := l_all_omfs_palm_user_list(i);
758 
759     IF (r_profiles_ins_rec.level_id <> 10004 OR (r_profiles_ins_rec.level_id = 10004
760                                            AND csm_util_pkg.is_palm_user(l_user_id))) THEN
761 
762       -- delete any lower levels that exist for this profile for the user
763       OPEN l_get_old_profile_csr(r_profiles_ins_rec.profile_option_id, l_user_id);
764       FETCH l_get_old_profile_csr INTO l_access_id, l_old_profile_option_value, l_old_level_id;
765       IF l_get_old_profile_csr%FOUND THEN
766         -- only call the WF if a profile at a higher level is added; update the acc table with the new value
767         IF r_profiles_ins_rec.level_id > NVL(l_old_level_id,0) THEN
768 
769            UPDATE csm_profile_option_values_acc
770            SET profile_option_value = r_profiles_ins_rec.profile_option_value,
771                level_id = r_profiles_ins_rec.level_id,
772                level_value = r_profiles_ins_rec.level_value,
773                level_value_application_id = r_profiles_ins_rec.level_value_application_id,
774                last_update_date = l_run_date
775            WHERE CURRENT OF l_get_old_profile_csr;
776 
777            IF is_mfs_profile(p_profile_option_name=>r_profiles_ins_rec.profile_option_name) THEN
778                 --mark dirty the SDQ for the user
779                 l_markdirty := CSM_UTIL_PKG.MakeDirtyForUser(g_pub_item,
780                                l_access_id, l_user_id, ASG_DOWNLOAD.UPD, SYSDATE);
781            END IF;
782         END IF;
783 
784       ELSE -- not found so insert the record(mostly this case is not used as the record is already available)
785 
786         IF (r_profiles_ins_rec.level_id = 10004 AND r_profiles_ins_rec.level_value = l_user_id) OR
790            OPEN c_profile_seq;
787                    (r_profiles_ins_rec.level_id <> 10004) THEN
788 
789            -- get the access_id
791            FETCH c_profile_seq INTO l_access_id;
792            CLOSE c_profile_seq;
793 
794            -- insert into csm_profile_option_values_acc
795            insert_profiles_acc(l_access_id,l_user_id,r_profiles_ins_rec.application_id,
796                              r_profiles_ins_rec.profile_option_id, r_profiles_ins_rec.level_id,
797                              r_profiles_ins_rec.level_value, r_profiles_ins_rec.level_value_application_id,
798                              r_profiles_ins_rec.profile_option_value,l_run_date);
799 
800            --mark dirty the SDQ for the user
801               l_markdirty := CSM_UTIL_PKG.MakeDirtyForUser(g_pub_item,
802                        l_access_id, l_user_id, ASG_DOWNLOAD.INS, SYSDATE);
803 
804          END IF;
805        END IF;
806        CLOSE l_get_old_profile_csr;
807    END IF; --- check of is_palm_user for level 10004
808   END LOOP; --palm omfs user loop
809 
810  END LOOP; -- process inserts
811 
812   -- set the program update date in jtm_con_request_data to sysdate
813   UPDATE jtm_con_request_data
814   SET last_run_date = l_run_date,
815       last_update_date = SYSDATE
816   WHERE package_name = 'CSM_PROFILE_EVENT_PKG'
817     AND procedure_name = 'REFRESH_ACC';
818 
819  p_status := 'FINE';
820  p_message :=  'CSM_PROFILE_EVENT_PKG.Refresh_Acc Executed successfully';
821 
822   COMMIT;
823 
824  EXCEPTION
825   WHEN others THEN
826      l_sqlerrno := to_char(SQLCODE);
827      l_sqlerrmsg := substr(SQLERRM, 1,200);
828      ROLLBACK;
829      p_status := 'ERROR';
830      p_message := 'Error in CSM_PROFILE_EVENT_PKG.Refresh_Acc: ' || l_sqlerrno || ':' || l_sqlerrmsg;
831      csm_util_pkg.log('CSM_PROFILE_EVENT_PKG ERROR : ' || l_sqlerrno || ':' || l_sqlerrmsg, 'CSM_PROFILE_EVENT_PKG.REFRESH_ACC', FND_LOG.LEVEL_EXCEPTION);
832      fnd_file.put_line(fnd_file.log, 'CSM_PROFILE_EVENT_PKG ERROR : ' || l_sqlerrno || ':' || l_sqlerrmsg);
833 
834 END Refresh_Acc;
835 
836 /***
837 ** Populates the user's acc table with the profiles upon user creation
838 ***/
839 
840 PROCEDURE refresh_user_acc(p_user_id IN NUMBER)
841 IS
842 l_sqlerrno VARCHAR2(20);
843 l_sqlerrmsg VARCHAR2(4000);
844 l_error_msg VARCHAR2(4000);
845 l_return_status VARCHAR2(2000);
846 
847 l_run_date DATE;
848 l_access_id jtm_fnd_lookups_acc.access_id%TYPE;
849 l_markdirty BOOLEAN;
850 
851 -- get the profiles to be inserted for the new user
852 CURSOR l_profiles_ins_csr(p_user_id IN number,
853                           p_csm_appl_id fnd_application.application_id%TYPE,
854                           p_csm_resp_id fnd_responsibility.responsibility_id%TYPE
855                          )
856 IS
857 SELECT val.application_id, val.profile_option_id, val.level_id, val.level_value,
858        val.level_value_application_id, val.profile_option_value, opt.profile_option_name
859 FROM  fnd_profile_options opt,
860       fnd_profile_option_values val
861 WHERE (opt.profile_option_name = 'CSF_M_RECIPIENTS_BOUNDARY'
862 --  OR opt.profile_option_name = 'CSF_M_AGENDA_ALLOWCHANGESCOMPLETEDTASK'
863   OR opt.profile_option_name = 'CSF_DEBRIEF_OVERLAPPING_LABOR'
864   OR opt.profile_option_name = 'CSF_MOBILE_TASK_TIMES_UPDATABLE'
865   OR opt.profile_option_name  = 'INC_DEFAULT_INCIDENT_SEVERITY'
866   OR opt.profile_option_name  = 'INC_DEFAULT_INCIDENT_URGENCY'
867   OR opt.profile_option_name  = 'JTF_TIME_UOM_CLASS'
868   OR opt.profile_option_name  = 'ICX_PREFERRED_CURRENCY'
869   OR opt.profile_option_name  = 'JTF_TASK_DEFAULT_ASSIGNEE_STATUS'
870   OR opt.profile_option_name  = 'CS_SR_RESTRICT_IB'
871   OR opt.profile_option_name  = 'SERVER_TIMEZONE_ID'
872   OR opt.profile_option_name  = 'CLIENT_TIMEZONE_ID'
873   OR opt.profile_option_name  = 'CSF_BUSINESS_PROCESS'
874   OR opt.profile_option_name  = 'CSM_SEARCH_RESULT_SET_SIZE'
875   OR opt.profile_option_name  = 'CSM_IB_ITEMS_AT_LOCATION'
876   OR opt.profile_option_name  = 'CSM_ITEM_CATEGORY_SET_FILTER'
877   OR opt.profile_option_name  = 'CSM_ITEM_CATEGORY_FILTER'
878   OR opt.profile_option_name  = 'CS_INV_VALIDATION_ORG'
879   OR opt.profile_option_name  = 'INC_DEFAULT_INCIDENT_TYPE'
880   OR opt.profile_option_name  = 'INC_DEFAULT_INCIDENT_STATUS'
881   OR opt.profile_option_name  = 'CSM_ENABLE_CREATE_SR'
882   OR opt.profile_option_name  = 'CSM_ENABLE_CREATE_TASK'
883   OR opt.profile_option_name  = 'CSM_ENABLE_UPDATE_ASSIGNMENTS'
884   OR opt.profile_option_name  = 'CSM_ENABLE_VIEW_CUST_PRODUCTS'
885   OR opt.profile_option_name  = 'CSM_MAX_READINGS_PER_COUNTER'
886   --bug4172005
887   OR opt.profile_option_name = 'CSF_RETURN_REASON'
888   --R 12 updates
889   OR opt.profile_option_name  = 'CSFW_DEFAULT_DISTANCE_UNIT'
890   OR opt.profile_option_name  = 'CSF_CAPTURE_TRAVEL'
891   OR opt.profile_option_name  = 'CSM_LABOR_LINE_TOTAL_CHECK'
892   OR opt.profile_option_name  = 'ICX_DATE_FORMAT_MASK'
893   OR opt.profile_option_name  = 'JTM_TIMEPICKER_FORMAT'
894   OR opt.profile_option_name  = 'CSM_TIME_REASONABILITY_CHECK_APPLY'
895   OR opt.profile_option_name  = 'ICX_NUMERIC_CHARACTERS'
896   OR opt.profile_option_name  = 'CSF_LABOR_DEBRIEF_DEFAULT_UOM'
897   OR opt.profile_option_name  = 'CSF_UOM_HOURS'
898   OR opt.profile_option_name  = 'CSZ_DEFAULT_CONTACT_BY'
899   OR opt.profile_option_name  = 'HZ_REF_TERRITORY'
900   OR opt.profile_option_name  = 'HZ_REF_LANG'
901   OR opt.profile_option_name  = 'HZ_LANG_FOR_COUNTRY_DISPLAY'
905   OR opt.profile_option_name  = 'CSF_DEFAULT_TASK_TYPE_PERSONAL'
902   OR opt.profile_option_name  = 'CSM_MAX_ATTACHMENT_SIZE'  --For PPC
903   OR opt.profile_option_name  = 'CSF_UOM_MINUTES'
904   OR opt.profile_option_name  = 'CSF_DEFAULT_TASK_STATUS_PERSONAL'
906   OR opt.profile_option_name  = 'CSF:DEFAULT DEBRIEF BUSINESS PROCESS'
907   OR opt.profile_option_name  = 'CSF:DEFAULT DEBRIEF TRANSACTION TYPE'
908   OR opt.profile_option_name  = 'INV:EXPENSE_TO_ASSET_TRANSFER'
909   OR opt.profile_option_name  = 'JTF_PROFILE_DEFAULT_CURRENCY'
910   OR opt.profile_option_name  = 'CSF_DEFAULT_TASK_ASSIGNED_STATUS'
911   OR opt.profile_option_name  = 'JTF_TASK_DEFAULT_TASK_STATUS'
912   OR opt.profile_option_name  = 'JTF_TASK_DEFAULT_TASK_PRIORITY'
913   OR opt.profile_option_name  = 'CSFW_PLANNED_TASK_WINDOW'
914   OR opt.profile_option_name  = 'CS_SR_CONTACT_MANDATORY'
915   OR opt.profile_option_name  = 'CSM_RESTRICT_DEBRIEF'
916   OR opt.profile_option_name  = 'CSM_RESTRICT_ORDERS'
917   OR opt.profile_option_name  = 'CSM_RESTRICT_TRANSFERS'
918   OR opt.profile_option_name  = 'JTF_TASK_DEFAULT_TASK_TYPE'
919   )
920 AND val.application_id IS NOT NULL
921 AND val.application_id = opt.application_id
922 AND val.profile_option_id = opt.profile_option_id
923 AND (  (val.level_id = 10001)
924     OR (val.level_id = 10004 AND val.level_value = p_user_id)
925     OR (val.level_id = 10002 AND val.level_value = p_csm_appl_id)
926     OR (val.level_id = 10003 AND val.level_value = p_csm_resp_id)
927     )
928 AND NOT EXISTS
929 (SELECT 1
930  FROM csm_profile_option_values_acc acc
931  WHERE acc.profile_option_id = val.profile_option_id
932  AND acc.application_id = val.application_id
933  AND acc.level_id = val.level_id
934  AND acc.level_value = val.level_value
935  AND val.level_id <> 10003
936 -- AND NVl(acc.level_value_application_id, -1) = NVL(val.level_value_application_id, -1)
937  AND acc.user_id = p_user_id
938  UNION
939  SELECT 1
940  FROM csm_profile_option_values_acc acc,
941       fnd_responsibility resp
942  WHERE acc.profile_option_id = val.profile_option_id
943  AND acc.application_id = val.application_id
944  AND acc.level_id = val.level_id
945  AND acc.level_value = val.level_value
946  AND acc.level_id = 10003
947  AND acc.level_value = resp.responsibility_id
948  AND acc.level_value_application_id = resp.application_id
949  AND acc.user_id = p_user_id
950  AND SYSDATE BETWEEN nvl(resp.start_date, sysdate) AND nvl(resp.end_date, sysdate)
951  )
952  ORDER BY val.application_id, val.profile_option_id, val.level_id desc
953  ;
954 
955 CURSOR l_get_old_profile_csr (p_profile_option_id IN number, p_user_id IN number)
956 IS
957 SELECT access_id, profile_option_value, level_id
958 FROM csm_profile_option_values_acc
959 WHERE profile_option_id = p_profile_option_id
960 AND user_id = p_user_id
961 ORDER BY level_id desc
962 FOR UPDATE OF profile_option_value, level_id, level_value, last_update_date NOWAIT;
963 
964 l_old_profile_option_value fnd_profile_option_values.profile_option_value%TYPE;
965 l_old_level_id fnd_profile_option_values.level_id%TYPE;
966 
967 CURSOR c_csm_appl IS
968 SELECT APPLICATION_ID
969 FROM fnd_application
970 WHERE application_short_name = 'CSM';
971 
972 CURSOR  c_csm_resp (c_user_id NUMBER) IS
973 SELECT  RESPONSIBILITY_ID
974 FROM 	asg_user
975 WHERE   user_id = c_user_id;
976 
977 CURSOR c_profile_seq IS
978 SELECT csm_profiles_acc_s.NEXTVAL
979 FROM dual;
980 
981 l_csm_appl_id fnd_application.application_id%TYPE;
982 l_csm_resp_id fnd_responsibility.responsibility_id%TYPE;
983 
984 BEGIN
985    l_run_date := SYSDATE;
986 
987    -- get csm application id
988    OPEN c_csm_appl;
989    FETCH c_csm_appl INTO l_csm_appl_id;
990    CLOSE c_csm_appl;
991 
992    -- get csm responsibility id
993    OPEN c_csm_resp(p_user_id);
994    FETCH c_csm_resp INTO l_csm_resp_id;
995    CLOSE c_csm_resp;
996 
997   -- process inserts
998    FOR r_profiles_ins_rec IN l_profiles_ins_csr(p_user_id, l_csm_appl_id, l_csm_resp_id) LOOP
999       -- delete any lower levels that exist for this profile for the user
1000       OPEN l_get_old_profile_csr(r_profiles_ins_rec.profile_option_id, p_user_id);
1001       FETCH l_get_old_profile_csr INTO l_access_id, l_old_profile_option_value, l_old_level_id;
1002       IF l_get_old_profile_csr%FOUND THEN
1003         -- only call the WF if a profile at a higher level is added; update the acc table with the new value
1004         IF r_profiles_ins_rec.level_id > NVL(l_old_level_id,0) THEN
1005            UPDATE csm_profile_option_values_acc
1006            SET profile_option_value = r_profiles_ins_rec.profile_option_value,
1007                level_id = r_profiles_ins_rec.level_id,
1008                level_value = r_profiles_ins_rec.level_value,
1009                level_value_application_id = r_profiles_ins_rec.level_value_application_id,
1010                last_update_date = l_run_date
1011            WHERE CURRENT OF l_get_old_profile_csr;
1012 
1013         IF is_mfs_profile(p_profile_option_name=>r_profiles_ins_rec.profile_option_name) THEN
1014             --mark dirty the SDQ for the user
1015             l_markdirty := CSM_UTIL_PKG.MakeDirtyForUser(g_pub_item,
1016                             l_access_id, p_user_id, ASG_DOWNLOAD.UPD, SYSDATE);
1017         END IF;
1018 
1019            -- start the profile_option_value_upd WF
1020 --           start_profile_upd_wf(l_access_id, r_profiles_ins_rec.profile_option_name,r_profiles_ins_rec.profile_option_value,
1024         IF (r_profiles_ins_rec.level_id = 10004 AND r_profiles_ins_rec.level_value = p_user_id) OR
1021 --                                l_old_profile_option_value, p_user_id);
1022         END IF;
1023       ELSE
1025                    (r_profiles_ins_rec.level_id <> 10004) THEN
1026 
1027            -- get the access_id
1028            OPEN c_profile_seq;
1029            FETCH c_profile_seq INTO l_access_id;
1030            CLOSE c_profile_seq;
1031 
1032            -- insert into csm_profile_option_values_acc
1033            insert_profiles_acc(l_access_id,p_user_id,r_profiles_ins_rec.application_id,
1034                              r_profiles_ins_rec.profile_option_id, r_profiles_ins_rec.level_id,
1035                              r_profiles_ins_rec.level_value, r_profiles_ins_rec.level_value_application_id,
1036                              r_profiles_ins_rec.profile_option_value,l_run_date);
1037 
1038            IF is_mfs_profile(p_profile_option_name=>r_profiles_ins_rec.profile_option_name) THEN
1039               --mark dirty the SDQ for the user
1040               l_markdirty := CSM_UTIL_PKG.MakeDirtyForUser(g_pub_item,
1041                              l_access_id, p_user_id, ASG_DOWNLOAD.INS, SYSDATE);
1042            END IF;
1043 
1044            -- start the profile_option_value_upd WF using null for the old profle option value
1045 --           start_profile_upd_wf(l_access_id, r_profiles_ins_rec.profile_option_name,r_profiles_ins_rec.profile_option_value,
1046 --                             NULL, p_user_id);
1047 
1048         END IF;
1049       END IF;
1050       CLOSE l_get_old_profile_csr;
1051 
1052    END LOOP; -- process inserts
1053 
1054 EXCEPTION
1055   	WHEN OTHERS THEN
1056         l_sqlerrno := to_char(SQLCODE);
1057         l_sqlerrmsg := substr(SQLERRM, 1,2000);
1058         l_error_msg := ' Exception in  refresh_user_acc for user_id:'
1059                        || to_char(p_user_id) || ':' || l_sqlerrno || ':' || l_sqlerrmsg;
1060         CSM_UTIL_PKG.LOG(l_error_msg, 'csm_profile_event_pkg.refresh_user_acc',FND_LOG.LEVEL_EXCEPTION);
1061         RAISE;
1062 END refresh_user_acc;
1063 
1064 FUNCTION IS_MFS_PROFILE(p_profile_option_name IN VARCHAR2) RETURN BOOLEAN
1065 IS
1066 l_sqlerrno VARCHAR2(20);
1067 l_sqlerrmsg VARCHAR2(4000);
1068 l_error_msg VARCHAR2(4000);
1069 l_return_status VARCHAR2(2000);
1070 
1071 l_profile_option_name fnd_profile_options.profile_option_name%TYPE;
1072 
1073 BEGIN
1074    l_profile_option_name := p_profile_option_name;
1075 
1076    IF (l_profile_option_name = 'CSF_M_RECIPIENTS_BOUNDARY'
1077 --        OR l_profile_option_name = 'CSF_M_AGENDA_ALLOWCHANGESCOMPLETEDTASK'
1078         OR l_profile_option_name = 'CSF_DEBRIEF_OVERLAPPING_LABOR'
1079         OR l_profile_option_name = 'CSF_MOBILE_TASK_TIMES_UPDATABLE'
1080         OR l_profile_option_name  = 'INC_DEFAULT_INCIDENT_SEVERITY'
1081         OR l_profile_option_name  = 'INC_DEFAULT_INCIDENT_URGENCY'
1082         OR l_profile_option_name  = 'JTF_TIME_UOM_CLASS'
1083         OR l_profile_option_name  = 'ICX_PREFERRED_CURRENCY'
1084         OR l_profile_option_name  = 'JTF_TASK_DEFAULT_ASSIGNEE_STATUS'
1085         OR l_profile_option_name  = 'CS_SR_RESTRICT_IB'
1086         OR l_profile_option_name  = 'SERVER_TIMEZONE_ID'
1087         OR l_profile_option_name  = 'CLIENT_TIMEZONE_ID'
1088      	OR l_profile_option_name  = 'CSF_BUSINESS_PROCESS'
1089      	OR l_profile_option_name  = 'CSM_SEARCH_RESULT_SET_SIZE'
1090         OR l_profile_option_name  = 'CSM_IB_ITEMS_AT_LOCATION'
1091         OR l_profile_option_name  = 'CSM_ITEM_CATEGORY_SET_FILTER'
1092         OR l_profile_option_name  = 'CSM_ITEM_CATEGORY_FILTER'
1093         OR l_profile_option_name  = 'CS_INV_VALIDATION_ORG'
1094         OR l_profile_option_name  = 'INC_DEFAULT_INCIDENT_TYPE'
1095         OR l_profile_option_name  = 'CSM_ENABLE_CREATE_SR'
1096         OR l_profile_option_name  = 'CSM_ENABLE_CREATE_TASK'
1097         OR l_profile_option_name  = 'CSM_ENABLE_UPDATE_ASSIGNMENTS'
1098         OR l_profile_option_name  = 'CSM_ENABLE_VIEW_CUST_PRODUCTS'
1099     	--bug4172005
1100 	    OR l_profile_option_name = 'CSF_RETURN_REASON'
1101         OR l_profile_option_name  = 'INC_DEFAULT_INCIDENT_STATUS'
1102 		  --R 12 updates
1103 		OR l_profile_option_name  = 'CSFW_DEFAULT_DISTANCE_UNIT'
1104   		OR l_profile_option_name  = 'CSF_CAPTURE_TRAVEL'
1105   		OR l_profile_option_name  = 'CSM_LABOR_LINE_TOTAL_CHECK'
1106   		OR l_profile_option_name  = 'ICX_DATE_FORMAT_MASK'
1107   		OR l_profile_option_name  = 'JTM_TIMEPICKER_FORMAT'
1108   		OR l_profile_option_name  = 'CSM_TIME_REASONABILITY_CHECK_APPLY'
1109   		OR l_profile_option_name  = 'ICX_NUMERIC_CHARACTERS'
1110   		OR l_profile_option_name  = 'CSF_LABOR_DEBRIEF_DEFAULT_UOM'
1111   		OR l_profile_option_name  = 'CSF_UOM_HOURS'
1112   		OR l_profile_option_name  = 'CSZ_DEFAULT_CONTACT_BY'
1113 		OR l_profile_option_name  = 'HZ_REF_TERRITORY'
1114   		OR l_profile_option_name  = 'HZ_REF_LANG'
1115   		OR l_profile_option_name  = 'HZ_LANG_FOR_COUNTRY_DISPLAY'
1116         OR l_profile_option_name  = 'CSM_MAX_ATTACHMENT_SIZE'  --For PPC
1117   		OR l_profile_option_name  = 'CSF_UOM_MINUTES'
1118   		OR l_profile_option_name  = 'CSF_DEFAULT_TASK_STATUS_PERSONAL'
1119   		OR l_profile_option_name  = 'CSF_DEFAULT_TASK_TYPE_PERSONAL'
1120         OR l_profile_option_name  = 'CSF:DEFAULT DEBRIEF BUSINESS PROCESS'
1121         OR l_profile_option_name  = 'CSF:DEFAULT DEBRIEF TRANSACTION TYPE'
1122         OR l_profile_option_name  = 'INV:EXPENSE_TO_ASSET_TRANSFER'
1123         OR l_profile_option_name  = 'JTF_PROFILE_DEFAULT_CURRENCY'
1124         OR l_profile_option_name  = 'CSF_DEFAULT_TASK_ASSIGNED_STATUS'
1125         OR l_profile_option_name  = 'JTF_TASK_DEFAULT_TASK_STATUS'
1126         OR l_profile_option_name  = 'JTF_TASK_DEFAULT_TASK_PRIORITY'
1127         OR l_profile_option_name  = 'CSFW_PLANNED_TASK_WINDOW'
1128         OR l_profile_option_name  = 'CS_SR_CONTACT_MANDATORY'
1129         OR l_profile_option_name  = 'CSM_RESTRICT_DEBRIEF'
1130         OR l_profile_option_name  = 'CSM_RESTRICT_ORDERS'
1131         OR l_profile_option_name  = 'CSM_RESTRICT_TRANSFERS'
1132         OR l_profile_option_name  = 'JTF_TASK_DEFAULT_TASK_TYPE'
1133 		) THEN
1134         RETURN TRUE;
1135     ELSE
1136         RETURN FALSE;
1137     END IF;
1138 
1139 EXCEPTION
1140  WHEN OTHERS THEN
1141         l_sqlerrno := TO_CHAR(SQLCODE);
1142         l_sqlerrmsg := SUBSTR(SQLERRM, 1,2000);
1143         l_error_msg := ' Exception in IS_MFS_PROFILE for profile_option_name:' || l_profile_option_name
1144                            || ':' || l_sqlerrno || ':' || l_sqlerrmsg;
1145         CSM_UTIL_PKG.LOG(l_error_msg, 'CSM_PROFILE_EVENT_PKG.IS_MFS_PROFILE',FND_LOG.LEVEL_EXCEPTION);
1146         RETURN FALSE;
1147 END IS_MFS_PROFILE;
1148 
1149 END CSM_PROFILE_EVENT_PKG;