[Home] [Help]
PACKAGE BODY: APPS.CSM_UOM_EVENT_PKG
Source
1 PACKAGE BODY CSM_UOM_EVENT_PKG AS
2 /* $Header: csmeuomb.pls 120.2 2006/04/06 05:19:34 trajasek noship $ */
3
4 PROCEDURE INSERT_CSM_UOM_TL_ACC (p_access_id IN number,
5 p_uom_code mtl_units_of_measure_tl.uom_code%TYPE,
6 p_language mtl_units_of_measure_tl.language%TYPE
7 )
8 IS
9 l_sysdate DATE;
10 BEGIN
11 l_sysdate := SYSDATE;
12
13 INSERT INTO csm_unit_of_measure_tl_acc (access_id, uom_code, language, created_by, creation_date,
14 last_updated_by, last_update_date, last_update_login
15 )
16 VALUES (p_access_id, p_uom_code, p_language, fnd_global.user_id, l_sysdate,
17 fnd_global.user_id, l_sysdate, fnd_global.login_id);
18 EXCEPTION
19 WHEN others THEN
20 RAISE;
21
22 END;-- end INSERT_CSM_UOM_TL_ACC
23
24
25 PROCEDURE Refresh_acc(p_status OUT NOCOPY VARCHAR2,
26 p_message OUT NOCOPY VARCHAR2)
27 IS
28 PRAGMA AUTONOMOUS_TRANSACTION;
29
30 --variable Declartions
31 l_pub_item varchar2(30) := 'CSF_M_UOM';
32 l_run_date date;
33 l_sqlerrno varchar2(20);
34 l_sqlerrmsg varchar2(2000);
35 l_mark_dirty boolean;
36 l_prog_update_date jtm_con_request_data.last_run_date%TYPE;
37 l_access_id jtm_fnd_lookups_acc.access_id%TYPE;
38 l_tl_omfs_palm_resource_list asg_download.user_list;
39 l_null_resource_list asg_download.user_list;
40 l_single_access_id_list asg_download.access_list;
41 l_null_access_list asg_download.access_list;
42 l_pkvalueslist asg_download.pk_list;
43 l_null_pkvalueslist asg_download.pk_list;
44
45 CURSOR l_last_run_date_csr(p_pub_item IN varchar2)
46 IS
47 SELECT nvl(last_run_date, (sysdate - 365*50))
48 FROM jtm_con_request_data
49 WHERE package_name = 'CSM_UOM_EVENT_PKG'
50 AND procedure_name = 'REFRESH_ACC';
51
52 -- inserts cursor
53 CURSOR l_uom_ins_csr
54 IS
55 SELECT uom.uom_code,
56 uom.language
57 FROM mtl_units_of_measure_tl uom
58 WHERE NOT EXISTS
59 (SELECT 1
60 FROM csm_unit_of_measure_tl_acc acc
61 WHERE acc.uom_code = uom.uom_code
62 AND acc.language = uom.language
63 );
64
65 -- updates cur
66 CURSOR l_uom_upd_csr(p_last_upd_date date)
67 IS
68 SELECT acc.access_id,
69 uom.uom_code,
70 uom.language
71 FROM mtl_units_of_measure_tl uom,
72 csm_unit_of_measure_tl_acc acc
73 WHERE acc.uom_code = uom.uom_code
74 AND acc.language = uom.language
75 AND uom.last_update_date > p_last_upd_date;
76
77 -- deletes cursor
78 CURSOR l_uom_del_csr
79 IS
80 SELECT acc.access_id,
81 acc.uom_code,
82 acc.language
83 FROM csm_unit_of_measure_tl_acc acc
84 WHERE NOT EXISTS
85 (SELECT 1
86 FROM mtl_units_of_measure_tl uom
87 WHERE acc.uom_code = uom.uom_code
88 AND acc.language = uom.language
89 );
90
91 BEGIN
92 -- data program is run
93 l_run_date := SYSDATE;
94
95 -- SAVEPOINT pre_refresh;
96
97 -- get last conc program update date
98 OPEN l_last_run_date_csr(l_pub_item);
99 FETCH l_last_run_date_csr INTO l_prog_update_date;
100 CLOSE l_last_run_date_csr;
101
102 l_pkvalueslist := l_null_pkvalueslist;
103
104 -- process all deletes
105 FOR r_uom_del_rec IN l_uom_del_csr LOOP
106
107 --get the users with this language
108 l_tl_omfs_palm_resource_list := l_null_resource_list;
109 l_tl_omfs_palm_resource_list := csm_util_pkg.get_tl_omfs_palm_resources(r_uom_del_rec.language);
110
111 l_access_id := r_uom_del_rec.access_id;
112
113 --nullify the access list
114 l_single_access_id_list := l_null_access_list;
115
116 FOR i in 1 .. l_tl_omfs_palm_resource_list.COUNT LOOP
117 l_single_access_id_list(i) := l_access_id;
118 END LOOP;
119
120 l_pkvalueslist(1) := r_uom_del_rec.uom_code;
121 l_pkvalueslist(2) := r_uom_del_rec.language;
122
123 --mark dirty the SDQ for all users
124 IF l_single_access_id_list.count > 0 THEN
125 l_mark_dirty := CSM_UTIL_PKG.MakeDirtyForResource(l_pub_item,
126 l_single_access_id_list,
127 l_tl_omfs_palm_resource_list,
128 ASG_DOWNLOAD.DEL,
129 SYSDATE );
130 END IF;
131
132 -- delete from acc table
133 DELETE FROM csm_unit_of_measure_tl_acc WHERE access_id = l_access_id;
134 END LOOP;
135
136 -- process all updates
137 FOR r_uom_upd_rec IN l_uom_upd_csr(l_prog_update_date) LOOP
138
139 --get the users with this language
140 l_tl_omfs_palm_resource_list := l_null_resource_list;
141 l_tl_omfs_palm_resource_list := csm_util_pkg.get_tl_omfs_palm_resources(r_uom_upd_rec.language);
142
143 l_access_id := r_uom_upd_rec.access_id;
144
145 --nullify the access list
146 l_single_access_id_list := l_null_access_list;
147
148 FOR i in 1 .. l_tl_omfs_palm_resource_list.COUNT LOOP
149 l_single_access_id_list(i) := l_access_id;
150 END LOOP;
151
152 --mark dirty the SDQ for all users
153 IF l_single_access_id_list.count > 0 THEN
154 l_mark_dirty := CSM_UTIL_PKG.MakeDirtyForResource(l_pub_item,
155 l_single_access_id_list,
156 l_tl_omfs_palm_resource_list,
157 ASG_DOWNLOAD.UPD,
158 sysdate);
159 END IF;
160
161 END LOOP;
162
163 -- process all inserts
164 FOR r_uom_ins_rec IN l_uom_ins_csr LOOP
165
166 --get the users with this language
167 l_tl_omfs_palm_resource_list := l_null_resource_list;
168 l_tl_omfs_palm_resource_list := csm_util_pkg.get_tl_omfs_palm_resources(r_uom_ins_rec.language);
169
170 SELECT csm_unit_of_measure_tl_acc_s.nextval
171 INTO l_access_id
172 FROM dual;
173
174 --nullify the access list
175 l_single_access_id_list := l_null_access_list;
176
177 FOR i in 1 .. l_tl_omfs_palm_resource_list.COUNT LOOP
178 l_single_access_id_list(i) := l_access_id;
179 END LOOP;
180
181 --mark dirty the SDQ for all users
182 IF l_single_access_id_list.count > 0 THEN
183 l_mark_dirty := CSM_UTIL_PKG.MakeDirtyForResource(l_pub_item,
184 l_single_access_id_list,
185 l_tl_omfs_palm_resource_list,
186 ASG_DOWNLOAD.INS,
187 sysdate);
188 END IF;
189
190 -- insert into csm_unit_of_measure_tl_acc
191 INSERT_CSM_UOM_TL_ACC (l_access_id, r_uom_ins_rec.uom_code , r_uom_ins_rec.language);
192
193 END LOOP;
194
195 -- set the program update date in jtm_con_request_data to sysdate
196 UPDATE jtm_con_request_data
197 SET last_run_date = l_run_date
198 WHERE package_name = 'CSM_UOM_EVENT_PKG'
199 AND procedure_name = 'REFRESH_ACC';
200
201 COMMIT;
202
203 p_status := 'FINE';
204 p_message := 'CSM_UOM_EVENT_PKG.Refresh_Acc Executed successfully';
205
206 EXCEPTION
207 WHEN others THEN
208 l_sqlerrno := to_char(SQLCODE);
209 l_sqlerrmsg := substr(SQLERRM, 1,1000);
210 p_status := 'ERROR';
211 p_message := 'Error in CSM_UOM_EVENT_PKG.Refresh_Acc :' || l_sqlerrno || ':' || l_sqlerrmsg;
212 ROLLBACK TO pre_refresh;
213 fnd_file.put_line(fnd_file.log, 'CSM_UOM_EVENT_PKG ERROR : ' || l_sqlerrno || ':' || l_sqlerrmsg);
214 END Refresh_acc;
215
216 END CSM_UOM_EVENT_PKG;