1 PACKAGE BODY CSM_LOOKUP_EVENT_PKG AS
2 /* $Header: csmelkub.pls 120.17.12020000.2 2013/04/09 10:55:44 saradhak ship $ */
3
4 -- To modify this template, edit file PKGBODY.TXT in TEMPLATE
5 -- directory of SQL Navigator
6 --
7 -- Purpose: Briefly explain the functionality of the package body
8 --
9 -- MODIFICATION HISTORY
10 -- Person Date Comments
11 -- --------- ------ ------------------------------------------
12 -- Enter procedure, function bodies as shown below
13
14 g_all_palm_res_list asg_download.user_list;
15
16 /**
17 Refreshes the CSM_LOOKUP_TYPES_ACC table, and marks dirty for users accordingly
18 -- MODIFICATION HISTORY
19 -- Person Date Comments
20 -- Anurag 09/23/02 Added conditions for CS_CREDIT_CARD_TYPES in the cursor
21 where clauses
22 */
23 procedure Refresh_Acc (p_status OUT NOCOPY VARCHAR2,
24 p_message OUT NOCOPY VARCHAR2)
25 IS
26 PRAGMA AUTONOMOUS_TRANSACTION;
27 --variable Declarations
28 l_prog_update_date jtm_con_request_data.last_run_date%TYPE;
29 l_access_id jtm_fnd_lookups_acc.access_id%TYPE;
30 l_mark_dirty boolean;
31 l_run_date date;
32 l_sqlerrno VARCHAR2(20);
33 l_sqlerrmsg varchar2(2000);
34 l_pub_item varchar2(30) := 'CSF_M_LOOKUPS';
35 l_tl_omfs_palm_resource_list asg_download.user_list;
36 l_null_resource_list asg_download.user_list;
37 l_single_access_id_list asg_download.access_list;
38 --a null list
39 l_null_access_list asg_download.access_list;
40
41 CURSOR l_last_run_date_csr(p_pub_item IN varchar2)
42 IS
43 SELECT nvl(last_run_date, (sysdate - 365*50))
44 FROM jtm_con_request_data
45 WHERE package_name = 'CSM_LOOKUP_EVENT_PKG'
46 AND procedure_name = 'REFRESH_ACC';
47
48 -- inserts cursor
49 CURSOR l_lookups_ins_csr(p_last_upd_date date)
50 IS
51 SELECT val.lookup_type,
52 val.view_application_id,
53 val.lookup_code,
54 val.security_group_id,
55 val.language,
56 val.last_update_date
57 FROM fnd_lookup_values val
58 WHERE enabled_flag = 'Y'
59 AND (
60 (val.view_application_id = 0 AND val.lookup_type = 'JTF_RS_RESOURCE_TYPES' AND val.lookup_code IN ('RS_EMPLOYEE','RS_GROUP'))
61 OR
62 (val.view_application_id = 0 AND val.lookup_type = 'JTF_NOTE_STATUS')
63 OR (val.view_application_id = 0 AND val.lookup_type = 'JTF_NOTE_TYPE' AND
64 (val.lookup_code in (select object_id
65 from jtf_object_mappings
66 where object_code like 'JTF_NOTE_TYPE%' and
67 source_object_code in ('SR', 'TASK', 'PARTY', 'CP', 'SD', 'OKS_COV_NOTE')) OR
68 NOT EXISTS ( SELECT 1
69 FROM jtf_object_mappings
70 WHERE object_id = val.lookup_code)))
71 OR (val.view_application_id = 170 AND val.lookup_type = 'REQUEST_PROBLEM_CODE')
72 OR (val.view_application_id = 0 AND val.lookup_type = 'CSF_MATERIAL_REASON')
73 OR (val.view_application_id = 0 AND val.lookup_type = 'CSF_EXPENSE_REASON')
74 OR (val.view_application_id = 0 AND val.lookup_type = 'CSF_LABOR_REASON')
78 OR (val.view_application_id = 690 AND val.lookup_type = 'JTF_TASK_ESC_LEVEL')
75 OR (val.view_application_id = 0 AND val.lookup_type = 'CSP_RECOVERED_PART_DISP_CODE')
76 OR (val.view_application_id = 170 AND val.lookup_type = 'REQUEST_RESOLUTION_CODE')
77 OR (val.view_application_id = 170 AND val.lookup_type = 'CS_CTR_MISC_READING_TYPE')
79 OR (val.view_application_id = 222 AND val.lookup_type = 'CREDIT_MEMO_REASON')
80 OR (val.view_application_id = 542 AND val.lookup_type = 'CSI_ACCOUNTING_CLASS_CODE')
81 OR (val.view_application_id = 542 AND val.lookup_type = 'CSI_INST_TYPE_CODE')
82 OR (val.view_application_id = 542 AND val.lookup_type = 'CSI_COUNTER_DIRECTION_TYPE')
83 OR (val.view_application_id = 542 AND val.lookup_type = 'CSI_CTR_READING_RESET_TYPE')
84 OR (val.view_application_id = 0 AND val.lookup_type = 'ATCHMT_DOCUMENT_TYPE')
85 OR (val.view_application_id = 170 AND val.lookup_type = 'CS_SR_CONTACT_TYPE')
86 OR (val.view_application_id = 542 AND val.lookup_type = 'CSI_CTR_PROPERTY_LOV_TYPE')
87 OR (val.view_application_id = 0 AND val.lookup_type = 'YES_NO')
88 OR (val.view_application_id = 0 AND val.lookup_type = 'CSM_CLIENT_QUERY_TYPES')
89 OR (val.view_application_id = 0 AND val.lookup_type = 'CSM_QUERY_STATUSES')
90 OR (val.view_application_id = 0 AND val.lookup_type = 'CSM_QUERY_TYPE')
91 OR (val.view_application_id = 0 AND val.lookup_type = 'CSM_EXECUTION_MODE')
92 OR (val.view_application_id = 0 AND val.lookup_type = 'CSM_OUTPUT_FORMAT')
93 OR (val.view_application_id = 0 AND val.lookup_type = 'CSP_RETURN_TYPE')
94 OR (val.view_application_id = 0 AND val.lookup_type = 'CSF_DEBRIEF_INST_TRANS')
95 OR (val.view_application_id = 660 AND val.lookup_type = 'LINE_FLOW_STATUS')
96 OR (val.view_application_id = 201 AND val.lookup_type = 'SHIPMENT LINE STATUS')
97 OR (val.view_application_id = 0 AND val.lookup_type = 'CSP_SHIP_TO_LOCATION')
98 OR (val.lookup_type='MIS_CSF_DEBRIEFL_LINE_LKP' AND val.lookup_code = 'MIS_CSF_DEBRIEF_LIINE_QTY') --Apps IT Uses this
99 OR (val.view_application_id = 0 AND val.lookup_type IN ('MISCS_DOA_TYPE','MISCS_DETECTION_CODE','MISCS_FAULT_TYPE','MISCS_FIELD_SYMPTOM_CODES'))
100 )
101 AND NOT EXISTS
102 (SELECT 1
103 FROM csm_lookups_acc acc
104 WHERE acc.lookup_type = val.lookup_type
105 AND acc.language = val.language
106 AND acc.lookup_code = val.lookup_code
107 AND acc.security_group_id = val.security_group_id
108 AND acc.view_application_id = val.view_application_id
109 );
110
111 --updates cur
112 CURSOR l_lookups_upd_csr(p_last_upd_date date)
113 IS
114 SELECT acc.access_id, val.lookup_type,
115 val.view_application_id,
116 val.lookup_code,
117 val.security_group_id,
118 val.language,
119 val.last_update_date
120 FROM fnd_lookup_values val,
121 csm_lookups_acc acc
122 WHERE acc.lookup_type = val.lookup_type
123 AND acc.language = val.language
124 AND acc.lookup_code = val.lookup_code
125 AND acc.security_group_id = val.security_group_id
126 AND acc.view_application_id = val.view_application_id
127 AND val.last_update_date >= p_last_upd_date
128 AND enabled_flag = 'Y';
129
130 -- deletes cur
131 CURSOR l_lookups_del_csr(p_last_upd_date date)
132 IS
133 SELECT acc.access_id,
134 acc.lookup_type,
135 acc.view_application_id,
136 acc.lookup_code,
137 acc.security_group_id,
138 acc.language,
139 acc.last_update_date
140 FROM csm_lookups_acc acc
141 WHERE NOT EXISTS
142 (SELECT 1
143 FROM fnd_lookup_values val
144 WHERE acc.lookup_type = val.lookup_type
145 AND acc.language = val.language
146 AND acc.lookup_code = val.lookup_code
147 AND acc.security_group_id = val.security_group_id
148 AND acc.view_application_id = val.view_application_id
149 AND val.enabled_flag = 'Y'
150 );
151
152 BEGIN
153 -- data program is run
154 l_run_date := SYSDATE;
155
156 -- get last conc program update date
157 OPEN l_last_run_date_csr(l_pub_item);
158 FETCH l_last_run_date_csr INTO l_prog_update_date;
159 CLOSE l_last_run_date_csr;
160
161 -- process all deletes
162 FOR r_lookups_del_rec IN l_lookups_del_csr(l_prog_update_date) LOOP
163
164 --get the users with this language
165 l_tl_omfs_palm_resource_list := l_null_resource_list;
166 l_tl_omfs_palm_resource_list := csm_util_pkg.get_tl_omfs_palm_resources(r_lookups_del_rec.language);
167
168 l_access_id := r_lookups_del_rec.access_id;
169
170 --nullify the access list
171 l_single_access_id_list := l_null_access_list;
172 FOR i in 1 .. l_tl_omfs_palm_resource_list.COUNT LOOP
173 l_single_access_id_list(i) := l_access_id;
174 END LOOP;
175
176 --mark dirty the SDQ for all users
177 IF l_single_access_id_list.count > 0 THEN
178 l_mark_dirty := CSM_UTIL_PKG.MakeDirtyForResource('CSF_M_LOOKUPS',
179 l_single_access_id_list, l_tl_omfs_palm_resource_list,
180 ASG_DOWNLOAD.DEL, sysdate);
181 END IF;
182
183 -- delete from acc table
184 DELETE FROM csm_lookups_acc WHERE access_id = l_access_id;
185 END LOOP;
186
187 -- process all updates
188 FOR r_lookups_upd_rec IN l_lookups_upd_csr(l_prog_update_date) LOOP
189
190 --get the users with this language
191 l_tl_omfs_palm_resource_list := l_null_resource_list;
192 l_tl_omfs_palm_resource_list := csm_util_pkg.get_tl_omfs_palm_resources(r_lookups_upd_rec.language);
193
194 l_access_id := r_lookups_upd_rec.access_id;
195
196 --nullify the access list
197 l_single_access_id_list := l_null_access_list;
198 FOR i in 1 .. l_tl_omfs_palm_resource_list.COUNT LOOP
199 l_single_access_id_list(i) := l_access_id;
200 END LOOP;
201
202 --mark dirty the SDQ for all users
203 IF l_single_access_id_list.count > 0 then
204 l_mark_dirty := CSM_UTIL_PKG.MakeDirtyForResource('CSF_M_LOOKUPS',
205 l_single_access_id_list, l_tl_omfs_palm_resource_list,
206 ASG_DOWNLOAD.UPD, sysdate);
207 END IF;
208
209 END LOOP;
210
211 -- process all inserts
212 FOR r_lookups_ins_rec IN l_lookups_ins_csr(l_prog_update_date) LOOP
213
214 --get the users with this language
215 l_tl_omfs_palm_resource_list := l_null_resource_list;
216 l_tl_omfs_palm_resource_list := csm_util_pkg.get_tl_omfs_palm_resources(r_lookups_ins_rec.language);
217
218 SELECT csm_lookups_acc_s.nextval
219 INTO l_access_id
220 FROM dual;
221
222 --nullify the access list
223 l_single_access_id_list := l_null_access_list;
224 FOR i in 1 .. l_tl_omfs_palm_resource_list.COUNT LOOP
225 l_single_access_id_list(i) := l_access_id;
226 END LOOP;
227
228 --mark dirty the SDQ for all users
229 IF l_single_access_id_list.count > 0 THEN
230 l_mark_dirty := CSM_UTIL_PKG.MakeDirtyForResource('CSF_M_LOOKUPS',
231 l_single_access_id_list, l_tl_omfs_palm_resource_list,
232 ASG_DOWNLOAD.INS, sysdate);
233 END IF;
234
235 INSERT INTO csm_lookups_acc (access_id,
236 lookup_type,
237 language,
238 lookup_code,
239 security_group_id,
240 view_application_id,
241 created_by,
242 creation_date,
243 last_updated_by,
244 last_update_date,
245 last_update_login
246 )
247 VALUES (l_access_id,
248 r_lookups_ins_rec.lookup_type,
249 r_lookups_ins_rec.language,
250 r_lookups_ins_rec.lookup_code,
251 r_lookups_ins_rec.security_group_id,
252 r_lookups_ins_rec.view_application_id,
253 fnd_global.user_id,
254 l_run_date,
255 fnd_global.user_id,
256 l_run_date,
257 fnd_global.login_id
258 );
259
260 END LOOP;
261
262 -- set the program update date in jtm_con_request_data to sysdate
263 UPDATE jtm_con_request_data
264 SET last_run_date = l_run_date
265 WHERE package_name = 'CSM_LOOKUP_EVENT_PKG'
266 AND procedure_name = 'REFRESH_ACC';
267
268 COMMIT;
269
270 p_status := 'FINE';
271 p_message := 'CSM_LOOKUP_EVENT_PKG.Refresh_Acc Executed successfully';
272
273 EXCEPTION
274 WHEN others THEN
275 l_sqlerrno := to_char(SQLCODE);
276 l_sqlerrmsg := substr(SQLERRM, 1,2000);
277 p_status := 'Error';
278 p_message := 'Error in CSM_LOOKUP_EVENT_PKG.Refresh_Acc : ' || l_sqlerrno || ':' || l_sqlerrmsg;
279 fnd_file.put_line(fnd_file.log, p_message);
280 END Refresh_Acc;
281 END CSM_LOOKUP_EVENT_PKG;
[Home] [Help]