[Home] [Help]
PACKAGE BODY: APPS.IGI_IMP_IAC_PURGE_IAC_PKG
Source
1 PACKAGE BODY IGI_IMP_IAC_PURGE_IAC_PKG AS
2 -- $Header: igiimpib.pls 120.11 2007/08/01 10:47:48 npandya ship $
3
4 --===========================FND_LOG.START=====================================
5
6 g_state_level NUMBER := FND_LOG.LEVEL_STATEMENT;
7 g_proc_level NUMBER := FND_LOG.LEVEL_PROCEDURE;
8 g_event_level NUMBER := FND_LOG.LEVEL_EVENT;
9 g_excep_level NUMBER := FND_LOG.LEVEL_EXCEPTION;
10 g_error_level NUMBER := FND_LOG.LEVEL_ERROR;
11 g_unexp_level NUMBER := FND_LOG.LEVEL_UNEXPECTED;
12 g_path VARCHAR2(100) := 'IGI.PLSQL.igiimpib.igi_imp_iac_purge_iac_pkg.';
13
14 --===========================FND_LOG.END=====================================
15
16 Procedure Purge_Iac_Data (
17 errbuf OUT NOCOPY VARCHAR2 ,
18 retcode OUT NOCOPY NUMBER ,
19 p_book_type_code VARCHAR2 ,
20 p_cat_struct_id NUMBER ,
21 p_category_id NUMBER )
22 IS
23
24 -- Cursor to fetch the interface control record
25 CURSOR c_ctrl IS
26 SELECT *
27 FROM igi_imp_iac_interface_ctrl ic
28 WHERE ic.book_type_code = p_book_type_code
29 AND ic.category_id = nvl(p_Category_id,ic.category_id);
30
31 -- Cursor to fetch the assets from the interface table
32 CURSOR c_txns(cp_book VARCHAR2) IS
33 SELECT 'Y'
34 FROM igi_iac_transaction_headers it
35 WHERE it.book_type_code = cp_book
36 AND it.category_id = nvl(p_Category_id,it.category_id)
37 AND NOT ( nvl(it.transaction_sub_type,'AA') = 'IMPLEMENTATION')
38 AND rownum = 1 ;
39
40 l_prd_rec igi_iac_types.prd_rec ;
41 l_period_counter NUMBER(15) ;
42 l_txns_flag VARCHAR2(1) := 'N' ;
43 l_corporate_book VARCHAR2(15) ;
44 l_path_name VARCHAR2(150) := g_path||'purge_iac_data';
45
46 IGI_IMP_PURGE_IAC_EXCEPTION EXCEPTION ;
47
48 Begin -- procedure purge_iac_data
49
50 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,p_full_path => l_path_name,
51 p_string => '*************** Starting Purge IAC Data... ******************');
52
53 -- initialise the retcode
54 Retcode := 2 ;
55 --
56 -- Check if the category has already been transferred
57 --
58 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,p_full_path => l_path_name,
59 p_string => 'Checking if the category has already been transferred ...');
60
61 IF P_category_id is not null Then
62 For ctrlrec in c_ctrl loop
63
64 If ctrlrec.transfer_status <> 'C' then
65 fnd_message.set_name ('IGI','IGI_IMP_IAC_TRANSFER_NOT_DONE');
66 igi_iac_debug_pkg.debug_other_msg(p_level => g_state_level,
67 p_full_path => l_path_name,
68 p_remove_from_stack => FALSE);
69 Errbuf := fnd_message.get;
70 fnd_file.put_line(fnd_file.log, errbuf);
71 retcode := 2 ;
72 RETURN ;
73 end if;
74
75 end loop ;
76 End if;
77
78
79 --Get the period information
80
81 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
82 p_full_path => l_path_name,
83 p_string => ' Getting the period information ...');
84 BEGIN
85 SELECT ic.period_counter-1 , ic.corp_book
86 INTO l_period_counter , l_corporate_book
87 FROM igi_imp_iac_controls ic
88 WHERE ic.book_type_code = p_book_type_code ;
89 EXCEPTION
90 WHEN OTHERS THEN
91 igi_iac_debug_pkg.debug_other_string(p_level => g_unexp_level,
92 p_full_path => l_path_name,
93 p_string => 'Error : Fetching period counter from control '|| sqlerrm);
94 fnd_message.set_name ('IGI','IGI_IAC_PURGE_IAC_ERROR');
95 fnd_message.set_token('ERROR', sqlerrm);
96 fnd_message.set_token('OPERATION', 'fetch period and corporate book info');
97 Errbuf := fnd_message.get;
98 fnd_file.put_line(fnd_file.log, errbuf);
99 raise igi_imp_purge_iac_exception ;
100 END;
101
102 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,p_full_path => l_path_name,
103 p_string => 'Checking if there have been transactions after transfer ...');
104 l_txns_flag := 'N' ;
105 For txnrec in c_txns(l_corporate_book) loop
106 l_txns_flag := 'Y' ;
107 end loop ;
108
109 If l_txns_flag = 'Y' Then
110 fnd_message.set_name ('IGI','IGI_IMP_IAC_TXNS_AFTER_TFR');
111 igi_iac_debug_pkg.debug_other_msg(p_level => g_state_level,p_full_path => l_path_name,
112 p_remove_from_stack => FALSE);
113 Errbuf := fnd_message.get;
114 fnd_file.put_line(fnd_file.log, errbuf);
115 retcode := 2 ;
116 RETURN ;
117 End If;
118
119 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,p_full_path => l_path_name,
120 p_string => 'Fetching period Info for counter : '|| l_period_counter );
121
122 IF ( NOT( Igi_Iac_Common_Utils.Get_Period_Info_For_Counter (
123 l_corporate_book ,
124 l_period_Counter ,
125 l_prd_rec
126 )))
127 THEN
128 fnd_message.set_name ('IGI','IGI_IAC_PURGE_IAC_ERROR');
129 fnd_message.set_token('ERROR', ' ');
130 fnd_message.set_token('OPERATION', 'fetch period info for period counter');
131 igi_iac_debug_pkg.debug_other_msg(p_level => g_error_level,
132 p_full_path => l_path_name,
133 p_remove_from_stack => FALSE);
134 Errbuf := fnd_message.get;
135 fnd_file.put_line(fnd_file.log, errbuf);
136 raise igi_imp_purge_iac_exception ;
137 END IF;
138
139 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,p_full_path => l_path_name,
140 p_string => 'Deleting igi_iac_adjustments ...');
141
142 Delete igi_iac_adjustments a
143 Where a.book_type_code = l_corporate_book
144 and exists ( select i.asset_id
145 from igi_imp_iac_interface I
146 where i.book_type_code = p_book_type_code
147 and i.category_id = nvl(p_category_id,i.category_id)
148 and i.asset_id = a.asset_id );
149
150 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,p_full_path => l_path_name,
151 p_string => SQL%rowcount || ' rows deleted.');
152
153 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
154 p_full_path => l_path_name,
155 p_string => 'Deleting igi_iac_det_balances ...');
156
157 Delete from igi_iac_det_balances d
158 Where d.book_type_code = l_corporate_book
159 and exists ( select i.asset_id
160 from igi_imp_iac_interface I
161 where i.book_type_code = p_book_type_code
162 and i.category_id = nvl(p_category_id,i.category_id)
163 and i.asset_id = d.asset_id );
164
165 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
166 p_full_path => l_path_name,
167 p_string => SQL%rowcount || ' rows deleted.');
168
169 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
170 p_full_path => l_path_name,
171 p_string => 'Deleting igi_iac_fa_deprn ...');
172
173 Delete from igi_iac_fa_deprn d
174 Where d.book_type_code = l_corporate_book
175 and exists ( select i.asset_id
176 from igi_imp_iac_interface I
177 where i.book_type_code = p_book_type_code
178 and i.category_id = nvl(p_category_id,i.category_id)
179 and i.asset_id = d.asset_id );
180
181 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
182 p_full_path => l_path_name,
183 p_string => SQL%rowcount || ' rows deleted.');
184
185 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
186 p_full_path => l_path_name,
187 p_string => 'Deleting igi_iac_asset_balances ...');
188
189 Delete from igi_iac_asset_balances b
190 Where b.book_type_code = l_corporate_book
191 and exists ( select i.asset_id
192 from igi_imp_iac_interface I
193 where i.book_type_code = p_book_type_code
194 and i.category_id = nvl(p_category_id,i.category_id)
195 and i.asset_id = b.asset_id );
196
197 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
198 p_full_path => l_path_name,
199 p_string => SQL%rowcount || ' rows deleted.');
200
201
202 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
203 p_full_path => l_path_name,
204 p_string => 'Deleting igi_iac_transaction_headers ...');
205
206 Delete from igi_iac_transaction_headers t
207 Where t.book_type_code = l_corporate_book
208 and exists ( select i.asset_id
209 from igi_imp_iac_interface I
210 where i.book_type_code = p_book_type_code
211 and i.category_id = Nvl(p_category_id,i.category_id)
212 and i.asset_id = t.asset_id );
213
214 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
215 p_full_path => l_path_name,
216 p_string => SQL%rowcount || ' rows deleted.');
217
218 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
219 p_full_path => l_path_name,
220 p_string => 'Deleting igi_iac_revaluations ...');
221
222 Delete igi_iac_revaluations r
223 where r.revaluation_id in ( select c.revaluation_id
224 from igi_iac_reval_categories c
225 where c.book_type_code = l_corporate_book
226 and category_id = nvl(p_category_id,category_id) );
227
228 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
229 p_full_path => l_path_name,
230 p_string => SQL%rowcount || ' rows deleted.');
231
232 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
233 p_full_path => l_path_name,
234 p_string => 'Deleting igi_iac_reval_categories ...');
235
236 Delete igi_iac_reval_categories c
237 where c.book_type_code = l_corporate_book
238 and c.category_id = nvl(p_category_id,c.category_id) ;
239
240 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
241 p_full_path => l_path_name,
242 p_string => SQL%rowcount || ' rows deleted.');
243
244 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
245 p_full_path => l_path_name,
246 p_string => 'Deleting igi_iac_revaluatio_rates ...');
247
248 Delete igi_iac_revaluation_rates c
249 where c.book_type_code = l_corporate_book
250 and EXISTS ( SELECT a.asset_id
251 FROM fa_additions a
252 WHERE a.asset_category_id = nvl(p_category_id,a.asset_category_id)
253 AND a.asset_id = c.asset_id );
254
255 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
256 p_full_path => l_path_name,
257 p_string => SQL%rowcount || ' rows deleted.');
258
259 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
260 p_full_path => l_path_name,
261 p_string => 'Updating igi_imp_iac_interface_ctrl status to Not transferred ...');
262 Update igi_imp_iac_interface_ctrl c
263 set c.transfer_status = 'N'
264 where c.book_type_code = p_book_type_code
265 and c.category_id = nvl(p_category_id,c.category_id) ;
266 IF ( SQL%rowcount = 0 ) THEN
267 igi_iac_debug_pkg.debug_other_string(p_level => g_error_level,
268 p_full_path => l_path_name,
269 p_string => 'ERROR : Could not set igi_imp_iac_interface_ctrl status to NOT TRANSFERRED');
270 fnd_message.set_name ('IGI','IGI_IAC_PURGE_IAC_ERROR');
271 fnd_message.set_token('ERROR', 'igi_imp_iac_interface_ctrl table does not contain anyrows for given book and category');
272 fnd_message.set_token('OPERATION', 'update transfer status to Not Transferred');
273 igi_iac_debug_pkg.debug_other_msg(p_level => g_error_level,
274 p_full_path => l_path_name,
275 p_remove_from_stack => FALSE);
276 Errbuf := fnd_message.get;
277 fnd_file.put_line(fnd_file.log, errbuf);
278 raise igi_imp_purge_iac_exception ;
279 END IF;
280 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
281 p_full_path => l_path_name,
282 p_string => SQL%rowcount || ' rows updated.');
283
284 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
285 p_full_path => l_path_name,
286 p_string => 'Updating igi_imp_iac_interface status to Not transferred ...');
287 Update igi_imp_iac_interface i
288 set i.transferred_flag = 'N'
289 where i.book_type_code = p_book_type_code
290 and i.category_id = nvl(p_category_id,i.category_id) ;
291 IF ( SQL%rowcount = 0 ) THEN
292 igi_iac_debug_pkg.debug_other_string(p_level => g_error_level,
293 p_full_path => l_path_name,
294 p_string => 'ERROR : Could not set igi_imp_iac_interface status to NOT TRANSFERRED');
295 fnd_message.set_name ('IGI','IGI_IAC_PURGE_IAC_ERROR');
296 fnd_message.set_token('ERROR', 'igi_imp_iac_interface table does not contain anyrows for given book and category');
297 fnd_message.set_token('OPERATION', 'update transfer flag to N');
298 igi_iac_debug_pkg.debug_other_msg(p_level => g_error_level,
299 p_full_path => l_path_name,
300 p_remove_from_stack => FALSE);
301 Errbuf := fnd_message.get;
302 fnd_file.put_line(fnd_file.log, errbuf);
303 raise igi_imp_purge_iac_exception ;
304 END IF;
305 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
306 p_full_path => l_path_name,
307 p_string => SQL%rowcount || ' rows updated.');
308
309
310 COMMIT;
311 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
312 p_full_path => l_path_name,
313 p_string => 'Purge IAC Data Successfully completed.');
314 retcode := 0 ;
315 RETURN ;
316
317 EXCEPTION
318 WHEN igi_imp_purge_iac_exception THEN
319 ROLLBACK WORK ;
320 igi_iac_debug_pkg.debug_other_string(p_level => g_error_level,
321 p_full_path => l_path_name,
322 p_string => 'ERROR : Purge IAC Data failed - '|| sqlerrm);
323 retcode := 2;
324 RETURN ;
325 WHEN OTHERS THEN
326 ROLLBACK WORK ;
327 igi_iac_debug_pkg.debug_other_string(p_level => g_unexp_level,
328 p_full_path => l_path_name,
329 p_string => 'Unknown Exception : Purge IAC Data failed - '|| sqlerrm);
330 fnd_message.set_name ('IGI','IGI_IAC_PURGE_IAC_ERROR');
331 fnd_message.set_token('ERROR', sqlerrm);
332 fnd_message.set_token('OPERATION', ' ');
333 Errbuf := fnd_message.get;
334 retcode := 2;
335 RETURN ;
336 END; -- end of procedure purge iac data
337 END; -- end of package
338
339