[Home] [Help]
PACKAGE BODY: APPS.CSM_MTL_PARAMETERS_EVENT_PKG
Source
1 PACKAGE BODY CSM_MTL_PARAMETERS_EVENT_PKG AS
2 /* $Header: csmemtpb.pls 120.1 2005/07/25 00:14:32 trajasek noship $*/
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_table_name1 CONSTANT VARCHAR2(30) := 'MTL_PARAMETERS';
15 g_acc_table_name1 CONSTANT VARCHAR2(30) := 'CSM_MTL_PARAMETERS_ACC';
16 g_acc_sequence_name1 CONSTANT VARCHAR2(30) := 'CSM_MTL_PARAMETERS_ACC_S';
17 g_publication_item_name1 CONSTANT CSM_ACC_PKG.t_publication_item_list :=
18 CSM_ACC_PKG.t_publication_item_list('CSM_MTL_PARAMETERS');
19 g_pk1_name1 CONSTANT VARCHAR2(30) := 'ORGANIZATION_ID';
20 g_pub_item CONSTANT VARCHAR2(30) := 'CSM_MTL_PARAMETERS';
21
22 PROCEDURE Refresh_Acc (p_status OUT NOCOPY VARCHAR2,
23 p_message OUT NOCOPY VARCHAR2)
24 IS
25 PRAGMA AUTONOMOUS_TRANSACTION;
26 l_updates_cur CSM_UTIL_PKG.Changed_Records_Cur_Type;
27 l_inserts_cur CSM_UTIL_PKG.Changed_Records_Cur_Type;
28 l_dsql varchar2(2048);
29 l_pub_item VARCHAR2(30) := 'CSM_MTL_PARAMETERS';
30 l_prog_update_date jtm_con_request_data.last_run_date%TYPE;
31 l_access_id jtm_fnd_lookups_acc.access_id%TYPE;
32 l_mark_dirty boolean;
33 l_all_omfs_palm_resource_list asg_download.user_list;
34 l_null_resource_list asg_download.user_list;
35 l_single_access_id_list asg_download.access_list;
36 --a null list
37 l_null_access_list asg_download.access_list;
38 l_run_date date;
39 l_sqlerrno varchar2(20);
40 l_sqlerrmsg varchar2(2000);
41
42 CURSOR l_last_run_date_csr
43 IS
44 SELECT nvl(last_run_date, (sysdate - 365*50))
45 FROM jtm_con_request_data
46 WHERE package_name = 'CSM_MTL_PARAMETERS_EVENT_PKG'
47 AND procedure_name = 'REFRESH_ACC';
48
49 -- inserts cur
50 CURSOR l_mtl_parameters_ins_csr
51 IS
52 SELECT mtlp.organization_id
53 FROM mtl_parameters mtlp
54 WHERE NOT EXISTS
55 (SELECT 1
56 FROM csm_mtl_parameters_acc acc
57 WHERE acc.organization_id = mtlp.organization_id
58 );
59
60 -- updates cur
61 CURSOR l_mtl_parameters_upd_csr(p_last_upd_date date)
62 IS
63 SELECT acc.access_id, mtlp.organization_id
64 FROM mtl_parameters mtlp,
65 csm_mtl_parameters_acc acc
66 WHERE (mtlp.creation_date < p_last_upd_date AND mtlp.last_update_date > p_last_upd_date)
67 AND acc.organization_id = mtlp.organization_id;
68
69 -- deletes cur
70 CURSOR l_mtl_parameters_del_csr
71 IS
72 SELECT acc.access_id, acc.organization_id
73 FROM csm_mtl_parameters_acc acc
74 WHERE NOT EXISTS
75 (SELECT 1
76 FROM mtl_parameters mtlp
77 WHERE mtlp.organization_id = acc.organization_id
78 );
79
80 BEGIN
81 -- data program is run
82 l_run_date := SYSDATE;
83
84 -- get last conc program update date
85 OPEN l_last_run_date_csr;
86 FETCH l_last_run_date_csr INTO l_prog_update_date;
87 CLOSE l_last_run_date_csr;
88
89 -- process all deletes
90 FOR r_mtl_parameters_del_rec IN l_mtl_parameters_del_csr LOOP
91
92 --get the users with this language
93 l_all_omfs_palm_resource_list := l_null_resource_list;
94 l_all_omfs_palm_resource_list := csm_util_pkg.get_all_omfs_palm_res_list;
95
96 l_access_id := r_mtl_parameters_del_rec.access_id;
97
98 --nullify the access list
99 l_single_access_id_list := l_null_access_list;
100 FOR i in 1 .. l_all_omfs_palm_resource_list.COUNT LOOP
101 l_single_access_id_list(i) := l_access_id;
102 END LOOP;
103
104 --mark dirty the SDQ for all users
105 IF l_single_access_id_list.count > 0 THEN
106 l_mark_dirty := CSM_UTIL_PKG.MakeDirtyForResource('CSM_MTL_PARAMETERS',
107 l_single_access_id_list, l_all_omfs_palm_resource_list,
108 ASG_DOWNLOAD.DEL, sysdate);
109 END IF;
110
111 -- delete from acc table
112 DELETE FROM csm_mtl_parameters_acc WHERE access_id = l_access_id;
113 END LOOP;
114
115 -- process all updates
116 FOR r_mtl_parameters_upd_rec IN l_mtl_parameters_upd_csr(l_prog_update_date) LOOP
117
118 --get the users with this language
119 l_all_omfs_palm_resource_list := l_null_resource_list;
120 l_all_omfs_palm_resource_list := csm_util_pkg.get_all_omfs_palm_res_list;
121
122 l_access_id := r_mtl_parameters_upd_rec.access_id;
123
124 --nullify the access list
125 l_single_access_id_list := l_null_access_list;
126 FOR i in 1 .. l_all_omfs_palm_resource_list.COUNT LOOP
127 l_single_access_id_list(i) := l_access_id;
128 END LOOP;
129
130 --mark dirty the SDQ for all users
131 IF l_single_access_id_list.count > 0 THEN
132 l_mark_dirty := CSM_UTIL_PKG.MakeDirtyForResource('CSM_MTL_PARAMETERS',
133 l_single_access_id_list, l_all_omfs_palm_resource_list,
134 ASG_DOWNLOAD.UPD, sysdate);
135 END IF;
136
137 END LOOP;
138
139 -- process all inserts
140 FOR r_mtl_parameters_ins_rec IN l_mtl_parameters_ins_csr LOOP
141
142 --get the users with this language
143 l_all_omfs_palm_resource_list := l_null_resource_list;
144 l_all_omfs_palm_resource_list := csm_util_pkg.get_all_omfs_palm_res_list;
145
146 SELECT csm_mtl_parameters_acc_s.nextval
147 INTO l_access_id
148 FROM dual;
149
150 --nullify the access list
151 l_single_access_id_list := l_null_access_list;
152 FOR i in 1 .. l_all_omfs_palm_resource_list.COUNT LOOP
153 l_single_access_id_list(i) := l_access_id;
154 END LOOP;
155
156 --mark dirty the SDQ for all users
157 IF l_single_access_id_list.count > 0 THEN
158 l_mark_dirty := CSM_UTIL_PKG.MakeDirtyForResource('CSM_MTL_PARAMETERS',
159 l_single_access_id_list, l_all_omfs_palm_resource_list,
160 ASG_DOWNLOAD.INS, sysdate);
161 END IF;
162
163 INSERT INTO csm_mtl_parameters_acc (access_id,
164 organization_id,
165 counter,
166 created_by,
167 creation_date,
168 last_updated_by,
169 last_update_date,
170 last_update_login
171 )
172 VALUES (l_access_id,
173 r_mtl_parameters_ins_rec.organization_id,
174 1,
175 fnd_global.user_id,
176 l_run_date,
177 fnd_global.user_id,
178 l_run_date,
179 fnd_global.login_id
180 );
181
182 END LOOP;
183
184 -- set the program update date in jtm_con_request_data to sysdate
185 UPDATE jtm_con_request_data
186 SET last_run_date = l_run_date
187 WHERE package_name = 'CSM_MTL_PARAMETERS_EVENT_PKG'
188 AND procedure_name = 'REFRESH_ACC';
189
190 COMMIT;
191
192 p_status := 'FINE';
193 p_message := 'CSM_MTL_PARAMETERS_EVENT_PKG.Refresh_Acc Executed successfully';
194
195 EXCEPTION
196 WHEN others THEN
197 l_sqlerrno := to_char(SQLCODE);
198 l_sqlerrmsg := substr(SQLERRM, 1,2000);
199 p_status := 'ERROR';
200 p_message := 'Error in CSM_MTL_PARAMETERS_EVENT_PKG.Refresh_Acc: ' || l_sqlerrno || ':' || l_sqlerrmsg;
201 ROLLBACK;
202 fnd_file.put_line(fnd_file.log, 'CSM_MTL_PARAMETERS_EVENT_PKG ERROR : ' || l_sqlerrno || ':' || l_sqlerrmsg);
203 END Refresh_Acc;
204
205 END CSM_MTL_PARAMETERS_EVENT_PKG;