[Home] [Help]
PACKAGE BODY: APPS.FA_CUA_HR_REINSTATEMENTS_PKG
Source
1 PACKAGE BODY FA_CUA_HR_REINSTATEMENTS_PKG as
2 /* $Header: FACHRINMB.pls 120.4 2009/04/16 14:43:04 bridgway ship $ \ */
3
4 g_log_level_rec fa_api_types.log_level_rec_type;
5
6 PROCEDURE reinstate ( x_batch_num IN NUMBER
7 , x_conc_request_id IN NUMBER
8 , x_book_type_code IN VARCHAR2
9 , x_retirement_date IN DATE
10 , x_currency_code IN VARCHAR2
11 , x_fy_start_date IN DATE
12 , x_fy_end_date IN DATE
13 , x_attribute_category IN VARCHAR2
14 , x_attribute1 IN VARCHAR2
15 , x_attribute2 IN VARCHAR2
16 , x_attribute3 IN VARCHAR2
17 , x_attribute4 IN VARCHAR2
18 , x_attribute5 IN VARCHAR2
19 , x_attribute6 IN VARCHAR2
20 , x_attribute7 IN VARCHAR2
21 , x_attribute8 IN VARCHAR2
22 , x_attribute9 IN VARCHAR2
23 , x_attribute10 IN VARCHAR2
24 , x_attribute11 IN VARCHAR2
25 , x_attribute12 IN VARCHAR2
26 , x_attribute13 IN VARCHAR2
27 , x_attribute14 IN VARCHAR2
28 , x_attribute15 IN VARCHAR2
29 , TH_attribute_category IN VARCHAR2
30 , TH_attribute1 IN VARCHAR2
31 , TH_attribute2 IN VARCHAR2
32 , TH_attribute3 IN VARCHAR2
33 , TH_attribute4 IN VARCHAR2
34 , TH_attribute5 IN VARCHAR2
35 , TH_attribute6 IN VARCHAR2
36 , TH_attribute7 IN VARCHAR2
37 , TH_attribute8 IN VARCHAR2
38 , TH_attribute9 IN VARCHAR2
39 , TH_attribute10 IN VARCHAR2
40 , TH_attribute11 IN VARCHAR2
41 , TH_attribute12 IN VARCHAR2
42 , TH_attribute13 IN VARCHAR2
43 , TH_attribute14 IN VARCHAR2
44 , TH_attribute15 IN VARCHAR2 , p_log_level_rec IN FA_API_TYPES.log_level_rec_type) IS
45
46 /* Local Variables holding Mass Retirements Information */
47
48 LV_Retirement_Rowid rowid;
49 LV_Created_By fa_mass_retirements.Created_By%TYPE;
50 LV_Last_Update_Login fa_mass_retirements.Last_Update_Login%TYPE;
51 LV_Asset_Id fa_additions.asset_id%TYPE;
52 LV_Retirement_Status fa_retirements.status%TYPE;
53 LV_Retirement_Id fa_retirements.retirement_id%TYPE;
54 LV_Transaction_Header_In fa_retirements.transaction_header_id_in%TYPE;
55 LV_Transaction_Header_out fa_retirements.transaction_header_id_out%TYPE;
56 LV_Cost_Retired fa_retirements.cost_retired%TYPE;
57 LV_Units fa_retirements.units%TYPE;
58 LV_date_effective fa_retirements.date_effective%TYPE;
59 LV_Ret_Prorate_Convention fa_retirements.retirement_prorate_convention%TYPE;
60 LV_Nbv_Retired fa_retirements.nbv_retired%TYPE;
61 LV_Gain_Loss_Amount fa_retirements.gain_loss_amount%TYPE;
62 LV_Proceeds_Of_Sale fa_retirements.proceeds_of_sale%TYPE;
63 LV_Cost_Of_Removal fa_retirements.cost_of_removal%TYPE;
64 LV_Gain_Loss_Type_Code fa_retirements.gain_loss_type_code%TYPE;
65 LV_Retirement_Type_Code fa_retirements.retirement_type_code%TYPE;
66 LV_Itc_Recaptured fa_retirements.itc_recaptured%TYPE;
67 LV_Itc_Recapture_Id fa_retirements.itc_recapture_id%TYPE;
68 LV_Stl_Method_Code fa_retirements.stl_method_code%TYPE;
69 LV_Stl_Life_In_Months fa_retirements.stl_life_in_months%TYPE;
70 LV_Stl_Deprn_Amount fa_retirements.stl_deprn_amount%TYPE;
71 LV_Reval_Reserve_Retired fa_retirements.reval_reserve_retired%TYPE;
72 LV_Unrevalued_Cost_Retired fa_retirements.unrevalued_cost_retired%TYPE;
73
74 LV_Precision NUMBER;
75 LV_Ext_Precision NUMBER;
76 LV_Min_Acct_Unit NUMBER;
77 LV_Mass_Reference_Id NUMBER;
78
79 /* Control Variables */
80 LV_Varchar2_Dummy VARCHAR2(80);
81 LV_Number_Dummy NUMBER(15);
82 LV_Today_Datetime DATE;
83 LV_Today_Date DATE;
84 v_sysdate DATE;
85 v_user NUMBER;
89 l_api_version number := 1;
86 v_last_update_login NUMBER;
87 lv_book_type_code varchar2(30);
88 lv_current_cost number;
90 l_init_msg_list varchar2(1) := FND_API.G_FALSE;
91 l_commit varchar2(1) := FND_API.G_FALSE;
92 l_validation_level number := FND_API.G_VALID_LEVEL_FULL;
93 l_return_status varchar2(1) := FND_API.G_FALSE;
94 l_msg_count number := 0;
95 l_msg_data varchar2(512);
96 l_trans_rec FA_API_TYPES.trans_rec_type;
97 l_asset_hdr_rec FA_API_TYPES.asset_hdr_rec_type;
98 l_asset_retire_rec FA_API_TYPES.asset_retire_rec_type;
99 l_asset_dist_tbl FA_API_TYPES.asset_dist_tbl_type;
100 l_subcomp_tbl FA_API_TYPES.subcomp_tbl_type;
101 l_inv_tbl FA_API_TYPES.inv_tbl_type;
102
103 CURSOR qualified_assets IS
104 SELECT ret.asset_id,
105 ret.rowid,
106 ret.status,
107 ret.retirement_id,
108 ret.transaction_header_id_in,
109 -- ret.transaction_header_id_out
110 ret.cost_retired,
111 ret.units,
112 ret.date_effective,
113 ret.retirement_prorate_convention,
114 ret.nbv_retired,
115 ret.gain_loss_amount,
116 ret.proceeds_of_sale,
117 ret.cost_of_removal,
118 ret.gain_loss_type_code,
119 ret.retirement_type_code,
120 ret.itc_recaptured ,
121 ret.itc_recapture_id ,
122 ret.stl_method_code ,
123 ret.stl_life_in_months ,
124 ret.stl_deprn_amount ,
125 ret.reval_reserve_retired,
126 ret.unrevalued_cost_retired,
127 ret.book_type_code,
128 ihrd.current_cost
129 FROM fa_retirements ret,
130 fa_hr_retirement_details ihrd
131 WHERE ihrd.batch_id = x_batch_num
132 AND ret.retirement_id = ihrd.retirement_id
133 AND ret.transaction_header_id_out IS NULL
134 AND ret.date_retired BETWEEN x_fy_Start_Date
135 AND x_fy_End_Date
136 FOR UPDATE NOWAIT;
137
138 CURSOR C_dist_lines IS
139 SELECT distribution_id,
140 units_Assigned,
141 transaction_header_id_in,
142 transaction_units,
143 transaction_header_id_out
144 FROM fa_distribution_history
145 WHERE retirement_id = LV_retirement_id
146 FOR UPDATE NOWAIT ;
147
148 TYPE ErrorRecTyp IS RECORD(
149 rejection_reason VARCHAR2(250) );
150
151 TYPE ErrorTabTyp IS TABLE OF ErrorRecTyp
152 INDEX BY BINARY_INTEGER;
153
154 v_Error_Tab ErrorTabTyp; -- error table
155
156 BEGIN -- hr_Reinstate
157
158 -- initializing parameters
159 v_sysdate:= sysdate;
160 v_user:= nvl(TO_NUMBER(fnd_profile.value('USER_ID')),-1);
161 v_last_update_login:= nvl(TO_NUMBER(fnd_profile.value('LOGIN_ID')),-1);
162
163 -- use the concurrent request id as the mass reference.
164 LV_Mass_Reference_Id := x_Conc_Request_Id;
165
166 FND_CURRENCY.GET_INFO(x_Currency_Code,
167 LV_Precision,
168 LV_Ext_Precision,
169 LV_Min_Acct_Unit);
170
171 OPEN qualified_assets;
172 LOOP -- qualified_assets
173 FETCH qualified_assets INTO LV_Asset_Id,
174 LV_retirement_rowid,
175 LV_Retirement_Status,
176 LV_Retirement_Id,
177 LV_Transaction_Header_In,
178 -- LV_transaction_header_out,
179 LV_Cost_Retired,
180 LV_units,
181 LV_date_effective,
182 LV_ret_prorate_convention,
183 LV_nbv_retired,
184 LV_gain_loss_amount,
185 LV_proceeds_of_sale,
186 LV_cost_of_removal,
187 LV_gain_loss_type_code,
188 LV_retirement_type_code,
189 LV_itc_recaptured ,
190 LV_itc_recapture_id ,
191 LV_stl_method_code ,
192 LV_stl_life_in_months ,
193 LV_stl_deprn_amount ,
194 LV_reval_reserve_retired,
195 LV_unrevalued_cost_retired,
196 LV_book_type_code,
197 LV_current_cost;
198 EXIT WHEN qualified_assets%NOTFOUND;
199
200 l_asset_retire_rec.retirement_id := LV_Retirement_Id;
201
202 IF LV_Retirement_Status = 'PENDING' THEN
203
204 FA_RETIREMENT_PUB.undo_retirement(
205 p_api_version => l_api_version
206 ,p_init_msg_list => l_init_msg_list
207 ,p_commit => l_commit
208 ,p_validation_level => l_validation_level
209 ,p_calling_fn => 'FA_CUA_HR_REINSTATEMENTS_PKG.Reinstate'
210 ,x_return_status => l_return_status
211 ,x_msg_count => l_msg_count
212 ,x_msg_data => l_msg_data
213
214 ,px_trans_rec => l_trans_rec
215 ,px_asset_hdr_rec => l_asset_hdr_rec
219
216 ,px_asset_retire_rec => l_asset_retire_rec);
217
218 ELSIF LV_Retirement_Status = 'PROCESSED' THEN
220 FA_RETIREMENT_PUB.do_reinstatement(
221 p_api_version => l_api_version
222 ,p_init_msg_list => l_init_msg_list
223 ,p_commit => l_commit
224 ,p_validation_level => l_validation_level
225 ,p_calling_fn => 'FA_CUA_HR_REINSTATEMENTS_PKG.Reinstate'
226 ,x_return_status => l_return_status
227 ,x_msg_count => l_msg_count
228 ,x_msg_data => l_msg_data
229
230 ,px_trans_rec => l_trans_rec
231 ,px_asset_hdr_rec => l_asset_hdr_rec
232 ,px_asset_retire_rec => l_asset_retire_rec
233 ,p_asset_dist_tbl => l_asset_dist_tbl
234 ,p_subcomp_tbl => l_subcomp_tbl
235 ,p_inv_tbl => l_inv_tbl);
236
237 END IF; -- LV_Retirement_Status
238
239 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) then
240 rollback;
241
242 UPDATE fa_hr_retirement_headers
243 set status_code = 'RF' -- Reinstatement Failed
244 where batch_id = x_batch_num;
245 END IF;
246
247 END LOOP; -- qualified_assets
248 CLOSE qualified_assets;
249
250 update fa_hr_retirement_headers
251 set status_code = 'RC' -- reinstatement completed
252 where batch_id = x_batch_num;
253
254 update fa_hr_retirement_details
255 set status_code = 'RI' -- reinstated
256 where batch_id = x_batch_num;
257
258 commit;
259 EXCEPTION -- hr_Reinstate
260 WHEN Others THEN
261 rollback;
262
263 UPDATE fa_hr_retirement_headers
264 set status_code = 'RF' -- Reinstatement Failed
265 where batch_id = x_batch_num;
266 END Reinstate;
267 --
268
269 PROCEDURE conc_request( ERRBUF OUT NOCOPY VARCHAR2,
270 RETCODE OUT NOCOPY VARCHAR2,
271 x_from_batch_num IN NUMBER,
272 x_to_batch_num IN NUMBER) IS
273
274 CURSOR hr_reinstatement IS
275 SELECT ihrh.batch_id,
276 ihrh.concurrent_request_id,
277 ihrh.book_type_code,
278 ihrh.retire_date ,
279 sob.currency_code,
280 ffy.start_date,
281 ffy.end_date
282 , ihrh.attribute_category
283 , ihrh.attribute1
284 , ihrh.attribute2
285 , ihrh.attribute3
286 , ihrh.attribute4
287 , ihrh.attribute5
288 , ihrh.attribute6
289 , ihrh.attribute7
290 , ihrh.attribute8
291 , ihrh.attribute9
292 , ihrh.attribute10
293 , ihrh.attribute11
294 , ihrh.attribute12
295 , ihrh.attribute13
296 , ihrh.attribute14
297 , ihrh.attribute15
298 , ihrh.TH_attribute_category
299 , ihrh.TH_attribute1
300 , ihrh.TH_attribute2
301 , ihrh.TH_attribute3
302 , ihrh.TH_attribute4
303 , ihrh.TH_attribute5
304 , ihrh.TH_attribute6
305 , ihrh.TH_attribute7
306 , ihrh.TH_attribute8
307 , ihrh.TH_attribute9
308 , ihrh.TH_attribute10
309 , ihrh.TH_attribute11
310 , ihrh.TH_attribute12
311 , ihrh.TH_attribute13
312 , ihrh.TH_attribute14
313 , ihrh.TH_attribute15
314 FROM fa_hr_retirement_headers ihrh,
315 fa_book_controls fbc,
316 gl_sets_of_books sob,
317 fa_fiscal_year ffy
318 WHERE ihrh.batch_id >= nvl(x_from_batch_num, ihrh.batch_id )
319 AND ihrh.batch_id <= nvl(x_to_batch_num, ihrh.batch_id)
320 AND ihrh.status_code IN ( 'RP', 'CP', 'RC') --completely processed or completely reinstated
321 AND ihrh.book_type_code = fbc.book_type_code
322 AND fbc.set_of_books_id = sob.set_of_books_id
323 AND ffy.fiscal_year_name = fbc.fiscal_year_name
324 AND ffy.fiscal_year = fbc.current_fiscal_year
325 FOR UPDATE NOWAIT;
326
327 BEGIN
328
329 FOR hrh_rec IN hr_reinstatement LOOP
330
331 update fa_hr_retirement_headers
332 set status_code = 'RP'
333 where batch_id = hrh_rec.batch_id;
334
335 update fa_hr_retirement_details
336 set status_code = 'RP'
337 where batch_id = hrh_rec.batch_id;
338
339 commit;
340
341 reinstate ( hrh_rec.batch_id,
342 hrh_rec.concurrent_request_id,
343 hrh_rec.book_type_code,
344 hrh_rec.retire_date,
345 hrh_rec.currency_code,
346 hrh_rec.start_date,
347 hrh_rec.end_date
348 , hrh_rec.attribute_category
349 , hrh_rec.attribute1
350 , hrh_rec.attribute2
351 , hrh_rec.attribute3
352 , hrh_rec.attribute4
353 , hrh_rec.attribute5
354 , hrh_rec.attribute6
355 , hrh_rec.attribute7
356 , hrh_rec.attribute8
357 , hrh_rec.attribute9
358 , hrh_rec.attribute10
359 , hrh_rec.attribute11
360 , hrh_rec.attribute12
361 , hrh_rec.attribute13
362 , hrh_rec.attribute14
363 , hrh_rec.attribute15
364 , hrh_rec.TH_attribute_category
365 , hrh_rec.TH_attribute1
366 , hrh_rec.TH_attribute2
367 , hrh_rec.TH_attribute3
368 , hrh_rec.TH_attribute4
369 , hrh_rec.TH_attribute5
370 , hrh_rec.TH_attribute6
371 , hrh_rec.TH_attribute7
372 , hrh_rec.TH_attribute8
373 , hrh_rec.TH_attribute9
374 , hrh_rec.TH_attribute10
375 , hrh_rec.TH_attribute11
376 , hrh_rec.TH_attribute12
377 , hrh_rec.TH_attribute13
378 , hrh_rec.TH_attribute14
379 , hrh_rec.TH_attribute15
380 , g_log_level_rec);
381 END LOOP;
382 END conc_request;
383
384 END FA_CUA_HR_REINSTATEMENTS_PKG;