[Home] [Help]
PACKAGE BODY: APPS.CSM_LOOKUP_EVENT_PKG
Source
1 PACKAGE BODY CSM_LOOKUP_EVENT_PKG AS
2 /* $Header: csmelkub.pls 120.15 2007/12/28 00:54:13 rsripada 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')
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')
78 OR (val.view_application_id = 690 AND val.lookup_type = 'JTF_TASK_ESC_LEVEL')
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 )
89 AND NOT EXISTS
90 (SELECT 1
91 FROM csm_lookups_acc acc
92 WHERE acc.lookup_type = val.lookup_type
93 AND acc.language = val.language
94 AND acc.lookup_code = val.lookup_code
95 AND acc.security_group_id = val.security_group_id
96 AND acc.view_application_id = val.view_application_id
97 )
98 ;
99
100 --updates cur
101 CURSOR l_lookups_upd_csr(p_last_upd_date date)
102 IS
103 SELECT acc.access_id, val.lookup_type,
104 val.view_application_id,
105 val.lookup_code,
106 val.security_group_id,
107 val.language,
108 val.last_update_date
109 FROM fnd_lookup_values val,
110 csm_lookups_acc acc
111 WHERE acc.lookup_type = val.lookup_type
112 AND acc.language = val.language
113 AND acc.lookup_code = val.lookup_code
114 AND acc.security_group_id = val.security_group_id
115 AND acc.view_application_id = val.view_application_id
116 AND val.last_update_date >= p_last_upd_date
117 AND enabled_flag = 'Y';
118
119 -- deletes cur
120 CURSOR l_lookups_del_csr(p_last_upd_date date)
121 IS
122 SELECT acc.access_id,
123 acc.lookup_type,
124 acc.view_application_id,
125 acc.lookup_code,
126 acc.security_group_id,
127 acc.language,
128 acc.last_update_date
129 FROM csm_lookups_acc acc
130 WHERE NOT EXISTS
131 (SELECT 1
132 FROM fnd_lookup_values val
133 WHERE acc.lookup_type = val.lookup_type
134 AND acc.language = val.language
135 AND acc.lookup_code = val.lookup_code
136 AND acc.security_group_id = val.security_group_id
137 AND acc.view_application_id = val.view_application_id
138 AND val.enabled_flag = 'Y'
139 );
140
141 BEGIN
142 -- data program is run
143 l_run_date := SYSDATE;
144
145 -- get last conc program update date
146 OPEN l_last_run_date_csr(l_pub_item);
147 FETCH l_last_run_date_csr INTO l_prog_update_date;
148 CLOSE l_last_run_date_csr;
149
150 -- process all deletes
151 FOR r_lookups_del_rec IN l_lookups_del_csr(l_prog_update_date) LOOP
152
153 --get the users with this language
154 l_tl_omfs_palm_resource_list := l_null_resource_list;
155 l_tl_omfs_palm_resource_list := csm_util_pkg.get_tl_omfs_palm_resources(r_lookups_del_rec.language);
156
157 l_access_id := r_lookups_del_rec.access_id;
158
159 --nullify the access list
160 l_single_access_id_list := l_null_access_list;
161 FOR i in 1 .. l_tl_omfs_palm_resource_list.COUNT LOOP
162 l_single_access_id_list(i) := l_access_id;
163 END LOOP;
164
165 --mark dirty the SDQ for all users
166 IF l_single_access_id_list.count > 0 THEN
167 l_mark_dirty := CSM_UTIL_PKG.MakeDirtyForResource('CSF_M_LOOKUPS',
168 l_single_access_id_list, l_tl_omfs_palm_resource_list,
169 ASG_DOWNLOAD.DEL, sysdate);
170 END IF;
171
172 -- delete from acc table
173 DELETE FROM csm_lookups_acc WHERE access_id = l_access_id;
174 END LOOP;
175
176 -- process all updates
177 FOR r_lookups_upd_rec IN l_lookups_upd_csr(l_prog_update_date) LOOP
178
179 --get the users with this language
180 l_tl_omfs_palm_resource_list := l_null_resource_list;
181 l_tl_omfs_palm_resource_list := csm_util_pkg.get_tl_omfs_palm_resources(r_lookups_upd_rec.language);
182
183 l_access_id := r_lookups_upd_rec.access_id;
184
185 --nullify the access list
186 l_single_access_id_list := l_null_access_list;
187 FOR i in 1 .. l_tl_omfs_palm_resource_list.COUNT LOOP
188 l_single_access_id_list(i) := l_access_id;
189 END LOOP;
190
191 --mark dirty the SDQ for all users
192 IF l_single_access_id_list.count > 0 then
193 l_mark_dirty := CSM_UTIL_PKG.MakeDirtyForResource('CSF_M_LOOKUPS',
194 l_single_access_id_list, l_tl_omfs_palm_resource_list,
195 ASG_DOWNLOAD.UPD, sysdate);
196 END IF;
197
198 END LOOP;
199
200 -- process all inserts
201 FOR r_lookups_ins_rec IN l_lookups_ins_csr(l_prog_update_date) LOOP
202
203 --get the users with this language
204 l_tl_omfs_palm_resource_list := l_null_resource_list;
205 l_tl_omfs_palm_resource_list := csm_util_pkg.get_tl_omfs_palm_resources(r_lookups_ins_rec.language);
206
207 SELECT csm_lookups_acc_s.nextval
208 INTO l_access_id
209 FROM dual;
210
211 --nullify the access list
212 l_single_access_id_list := l_null_access_list;
213 FOR i in 1 .. l_tl_omfs_palm_resource_list.COUNT LOOP
214 l_single_access_id_list(i) := l_access_id;
215 END LOOP;
216
217 --mark dirty the SDQ for all users
218 IF l_single_access_id_list.count > 0 THEN
219 l_mark_dirty := CSM_UTIL_PKG.MakeDirtyForResource('CSF_M_LOOKUPS',
220 l_single_access_id_list, l_tl_omfs_palm_resource_list,
221 ASG_DOWNLOAD.INS, sysdate);
222 END IF;
223
224 INSERT INTO csm_lookups_acc (access_id,
225 lookup_type,
226 language,
227 lookup_code,
228 security_group_id,
229 view_application_id,
230 created_by,
231 creation_date,
232 last_updated_by,
233 last_update_date,
234 last_update_login
235 )
236 VALUES (l_access_id,
237 r_lookups_ins_rec.lookup_type,
238 r_lookups_ins_rec.language,
239 r_lookups_ins_rec.lookup_code,
240 r_lookups_ins_rec.security_group_id,
241 r_lookups_ins_rec.view_application_id,
242 fnd_global.user_id,
243 l_run_date,
244 fnd_global.user_id,
245 l_run_date,
246 fnd_global.login_id
247 );
248
249 END LOOP;
250
251 -- set the program update date in jtm_con_request_data to sysdate
252 UPDATE jtm_con_request_data
253 SET last_run_date = l_run_date
254 WHERE package_name = 'CSM_LOOKUP_EVENT_PKG'
255 AND procedure_name = 'REFRESH_ACC';
256
257 COMMIT;
258
259 p_status := 'FINE';
260 p_message := 'CSM_LOOKUP_EVENT_PKG.Refresh_Acc Executed successfully';
261
262 EXCEPTION
263 WHEN others THEN
264 l_sqlerrno := to_char(SQLCODE);
265 l_sqlerrmsg := substr(SQLERRM, 1,2000);
266 p_status := 'Error';
267 p_message := 'Error in CSM_LOOKUP_EVENT_PKG.Refresh_Acc : ' || l_sqlerrno || ':' || l_sqlerrmsg;
268 fnd_file.put_line(fnd_file.log, p_message);
269 END Refresh_Acc;
270 END CSM_LOOKUP_EVENT_PKG;