[Home] [Help]
PACKAGE BODY: APPS.JTF_RS_CONC_REPMGR_PUB
Source
1 package body jtf_rs_conc_repmgr_pub as
2 /* $Header: jtfrsbpb.pls 120.0 2005/05/11 08:19:21 appldev ship $ */
3 -- Type: Public
4 -- Purpose: Inserts IN the JTF_RS_REP_MANAGERS
5 -- Modification History
6 -- DATE NAME PURPOSE
7 -- 4-DEC-2000 SR CHOUDHURY CREATED
8 -- 5-FEB-2001 N SINGHAI Added procedure sync_role_relation to be called
9 -- from new concurrent program JTFRSRMG
10 --
11
12 G_PKG_NAME VARCHAR2(30) := 'JTF_RS_CONC_REPMGR_PUB';
13 procedure populate_repmgr
14 (ERRBUF OUT NOCOPY VARCHAR2,
15 RETCODE OUT NOCOPY VARCHAR2)
16 is
17
18 --alter sequence JTF.JTF_RS_REP_MANAGERS_S cache 1000;
19
20 variable_mesg varchar2(1000);
21 validation_fail EXCEPTION;
22
23 --***************************************--
24 -- This cursor only gets manager records --
25 --***************************************--
26 cursor GET_GRPDENORM is
27 select /*+ parallel(rrel) */ rrel.role_relate_id
28 from JTF_RS_ROLE_RELATIONS rrel
29 where rrel.role_resource_type = 'RS_GROUP_MEMBER';
30
31
32 l_role_relate_id NUMBER;
33 x_return_status VARCHAR2(1);
34 x_msg_count NUMBER;
35 x_msg_data VARCHAR2(2000);
36 l_write_dir VARCHAR2(2000);
37 my_message VARCHAR2(2000);
38 Invalid_dir EXCEPTION;
39
40 i_commit NUMBER := 0;
41 commit_counter NUMBER := 1000;
42 i_analyze NUMBER := 0;
43 analyze_counter NUMBER := 1000;
44 i number;
45
46 BEGIN
47
48 --**************************--
49 -- Reporting manager denorm --
50 --**************************--
51 --************************************--
52 -- Take out denorm API in this script --
53 -- We will have another script to do --
54 -- denormalization --
55 --************************************--
56
57 i_commit := 0;
58 i_analyze := 0;
59 analyze_counter := 1000;
60
61
62 x_return_status := FND_API.G_RET_STS_SUCCESS;
63 open GET_GRPDENORM;
64 loop
65 begin
66 savepoint RM_GRPDENORM;
67 fetch GET_GRPDENORM into l_role_relate_id;
68 exit when GET_GRPDENORM%NOTFOUND;
69
70 jtf_rs_rep_mgr_denorm_pvt.insert_rep_manager_migr
71 ( P_API_VERSION => 1.0,
72 P_ROLE_RELATE_ID => l_role_relate_id,
73 X_RETURN_STATUS => x_return_status,
74 X_MSG_COUNT => x_msg_count,
75 X_MSG_DATA => x_msg_data
76 );
77
78 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
79 for i in 1..x_msg_count
80 loop
81 fnd_file.put_line(fnd_file.log, (fnd_msg_pub.get(i,
82 p_encoded => fnd_api.g_false)));
83
84
85 end loop;
86 END IF;
87
88 IF(i_commit > commit_counter) THEN
89 commit;
90 i_commit := 0;
91 ELSE
92 i_commit := i_commit + 1;
93 END IF;
94
95 exception
96 when others then
97 fnd_file.put_line(fnd_file.log, sqlcode||':'||sqlerrm);
98 ROLLBACK to RM_GRPDENORM;
99 end;
100 end loop;
101 close GET_GRPDENORM;
102
103 commit;
104
105 EXCEPTION
106 WHEN fnd_api.g_exc_unexpected_error
107 THEN
108 fnd_file.put_line(fnd_file.log, sqlcode||':'||sqlerrm);
109 ROLLBACK TO TERMINATE_EMPLOYEE_SP;
110
111 WHEN OTHERS
112 THEN
113 fnd_file.put_line(fnd_file.log, sqlcode||':'||sqlerrm);
114 ROLLBACK TO TERMINATE_EMPLOYEE_SP;
115 fnd_file.put_line(fnd_file.log, sqlerrm);
116
117 end populate_repmgr;
118
119 PROCEDURE sync_rep_mgr
120 (ERRBUF OUT NOCOPY VARCHAR2,
121 RETCODE OUT NOCOPY VARCHAR2)
122 IS
123 l_operation_flag VARCHAR2(1);
124 l_init_msg_list VARCHAR2(10) := FND_API.G_FALSE;
125 l_return_status VARCHAR2(200);
126 l_msg_count NUMBER;
127 l_msg_data VARCHAR2(200);
128 x_return_status VARCHAR2(200);
129 i NUMBER;
130 halt_operation EXCEPTION;
131
132
133 CURSOR c_get_role IS
134 SELECT role_relate_id,
135 operation_flag,
136 rowid row_id
137 FROM JTF_RS_CHGD_ROLE_RELATIONS
138 ORDER BY creation_date ;
139 -- FOR UPDATE OF role_relate_id;
140
141 BEGIN
142
143 SAVEPOINT CONC_SP;
144
145 FOR l_role IN c_get_role LOOP
146
147 BEGIN
148
149 SAVEPOINT CONC_ROLE_SP;
150
151 IF l_role.operation_flag = 'I' THEN
152 JTF_RS_REP_MGR_DENORM_PVT.INSERT_REP_MANAGER
153 ( P_API_VERSION => 1.0,
154 P_INIT_MSG_LIST => l_init_msg_list,
155 P_COMMIT => FND_API.G_TRUE,
156 P_ROLE_RELATE_ID => l_role.role_relate_id,
157 X_RETURN_STATUS => l_return_status,
158 X_MSG_COUNT => l_msg_count,
159 X_MSG_DATA => l_msg_data);
160
161 IF(l_return_status <> fnd_api.g_ret_sts_success)
162 THEN
163 FOR i in 1..l_msg_count
164 LOOP
165 fnd_file.put_line(fnd_file.log, (fnd_msg_pub.get(i, p_encoded => fnd_api.g_false)));
166 END LOOP;
167 RAISE halt_operation;
168 END IF;
169 END IF;
170
171 IF l_role.operation_flag = 'U' THEN
172
173 --call to UPDATE records in jtf_rs_rep_managers
174 JTF_RS_REP_MGR_DENORM_PVT.UPDATE_REP_MANAGER
175 ( P_API_VERSION => 1.0,
176 P_INIT_MSG_LIST => l_init_msg_list,
177 P_COMMIT => FND_API.G_TRUE,
178 P_ROLE_RELATE_ID => l_role.role_relate_id,
179 X_RETURN_STATUS => l_return_status,
180 X_MSG_COUNT => l_msg_count,
181 X_MSG_DATA => l_msg_data);
182
183 IF(l_return_status <> fnd_api.g_ret_sts_success)
184 THEN
185 FOR i in 1..l_msg_count
186 LOOP
187 fnd_file.put_line(fnd_file.log, (fnd_msg_pub.get(i, p_encoded => fnd_api.g_false)));
188 END LOOP;
189 RAISE halt_operation;
190 END IF;
191 END IF;
192
193 IF l_role.operation_flag = 'D' THEN
194
195 JTF_RS_REP_MGR_DENORM_PVT.DELETE_MEMBERS
196 ( P_API_VERSION => 1.0,
197 P_INIT_MSG_LIST => l_init_msg_list,
198 P_COMMIT => FND_API.G_TRUE,
199 P_ROLE_RELATE_ID => l_role.role_relate_id,
200 X_RETURN_STATUS => l_return_status,
201 X_MSG_COUNT => l_msg_count,
202 X_MSG_DATA => l_msg_data);
203
204 IF(l_return_status <> fnd_api.g_ret_sts_success)
205 THEN
206 FOR i in 1..l_msg_count
207 LOOP
208 fnd_file.put_line(fnd_file.log, (fnd_msg_pub.get(i, p_encoded => fnd_api.g_false)));
209 END LOOP;
210 RAISE halt_operation;
211 END IF;
212 END IF;
213
214 -- After sucessful completion delete the row
215
216 DELETE FROM JTF_RS_CHGD_ROLE_RELATIONS
217 --WHERE CURRENT OF c_get_role ;
218 WHERE role_relate_id = l_role.role_relate_id
219 AND rowid = l_role.row_id ;
220
221 COMMIT;
222
223 EXCEPTION WHEN halt_operation THEN
224 ROLLBACK TO CONC_ROLE_SP;
225 END;
226
227 END LOOP;
228
229 EXCEPTION
230 WHEN fnd_api.g_exc_unexpected_error THEN
231 fnd_file.put_line(fnd_file.log, sqlcode||':'||sqlerrm);
232 ROLLBACK TO CONC_SP;
233 WHEN OTHERS THEN
234 fnd_file.put_line(fnd_file.log, sqlcode||':'||sqlerrm);
235 ROLLBACK TO CONC_SP;
236
237 END sync_rep_mgr;
238
239 end jtf_rs_conc_repmgr_pub;