[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.2 2005/06/24 22:03:09 bridgway ship $ \ */
3
4 PROCEDURE reinstate ( x_batch_num IN NUMBER
5 , x_conc_request_id IN NUMBER
6 , x_book_type_code IN VARCHAR2
7 , x_retirement_date IN DATE
8 , x_currency_code IN VARCHAR2
9 , x_fy_start_date IN DATE
10 , x_fy_end_date IN DATE
11 , x_attribute_category IN VARCHAR2
12 , x_attribute1 IN VARCHAR2
13 , x_attribute2 IN VARCHAR2
14 , x_attribute3 IN VARCHAR2
15 , x_attribute4 IN VARCHAR2
16 , x_attribute5 IN VARCHAR2
17 , x_attribute6 IN VARCHAR2
18 , x_attribute7 IN VARCHAR2
19 , x_attribute8 IN VARCHAR2
20 , x_attribute9 IN VARCHAR2
21 , x_attribute10 IN VARCHAR2
22 , x_attribute11 IN VARCHAR2
23 , x_attribute12 IN VARCHAR2
24 , x_attribute13 IN VARCHAR2
25 , x_attribute14 IN VARCHAR2
26 , x_attribute15 IN VARCHAR2
27 , TH_attribute_category IN VARCHAR2
28 , TH_attribute1 IN VARCHAR2
29 , TH_attribute2 IN VARCHAR2
30 , TH_attribute3 IN VARCHAR2
31 , TH_attribute4 IN VARCHAR2
32 , TH_attribute5 IN VARCHAR2
33 , TH_attribute6 IN VARCHAR2
34 , TH_attribute7 IN VARCHAR2
35 , TH_attribute8 IN VARCHAR2
36 , TH_attribute9 IN VARCHAR2
37 , TH_attribute10 IN VARCHAR2
38 , TH_attribute11 IN VARCHAR2
39 , TH_attribute12 IN VARCHAR2
40 , TH_attribute13 IN VARCHAR2
41 , TH_attribute14 IN VARCHAR2
42 , TH_attribute15 IN VARCHAR2 ) IS
43
44 /* Local Variables holding Mass Retirements Information */
45
46 LV_Retirement_Rowid rowid;
47 LV_Created_By fa_mass_retirements.Created_By%TYPE;
48 LV_Last_Update_Login fa_mass_retirements.Last_Update_Login%TYPE;
49 LV_Asset_Id fa_additions.asset_id%TYPE;
50 LV_Retirement_Status fa_retirements.status%TYPE;
51 LV_Retirement_Id fa_retirements.retirement_id%TYPE;
52 LV_Transaction_Header_In fa_retirements.transaction_header_id_in%TYPE;
53 LV_Transaction_Header_out fa_retirements.transaction_header_id_out%TYPE;
54 LV_Cost_Retired fa_retirements.cost_retired%TYPE;
55 LV_Units fa_retirements.units%TYPE;
56 LV_date_effective fa_retirements.date_effective%TYPE;
57 LV_Ret_Prorate_Convention fa_retirements.retirement_prorate_convention%TYPE;
58 LV_Nbv_Retired fa_retirements.nbv_retired%TYPE;
59 LV_Gain_Loss_Amount fa_retirements.gain_loss_amount%TYPE;
60 LV_Proceeds_Of_Sale fa_retirements.proceeds_of_sale%TYPE;
61 LV_Cost_Of_Removal fa_retirements.cost_of_removal%TYPE;
62 LV_Gain_Loss_Type_Code fa_retirements.gain_loss_type_code%TYPE;
63 LV_Retirement_Type_Code fa_retirements.retirement_type_code%TYPE;
64 LV_Itc_Recaptured fa_retirements.itc_recaptured%TYPE;
65 LV_Itc_Recapture_Id fa_retirements.itc_recapture_id%TYPE;
66 LV_Stl_Method_Code fa_retirements.stl_method_code%TYPE;
67 LV_Stl_Life_In_Months fa_retirements.stl_life_in_months%TYPE;
68 LV_Stl_Deprn_Amount fa_retirements.stl_deprn_amount%TYPE;
69 LV_Reval_Reserve_Retired fa_retirements.reval_reserve_retired%TYPE;
70 LV_Unrevalued_Cost_Retired fa_retirements.unrevalued_cost_retired%TYPE;
71
72 LV_Precision NUMBER;
73 LV_Ext_Precision NUMBER;
74 LV_Min_Acct_Unit NUMBER;
75 LV_Mass_Reference_Id NUMBER;
76
77 /* Control Variables */
78 LV_Varchar2_Dummy VARCHAR2(80);
79 LV_Number_Dummy NUMBER(15);
80 LV_Today_Datetime DATE;
81 LV_Today_Date DATE;
82 v_sysdate DATE;
83 v_user NUMBER;
84 v_last_update_login NUMBER;
85 lv_book_type_code varchar2(15);
86 lv_current_cost number;
87 l_api_version number := 1;
88 l_init_msg_list varchar2(1) := FND_API.G_FALSE;
89 l_commit varchar2(1) := FND_API.G_FALSE;
90 l_validation_level number := FND_API.G_VALID_LEVEL_FULL;
91 l_return_status varchar2(1) := FND_API.G_FALSE;
92 l_msg_count number := 0;
93 l_msg_data varchar2(512);
94 l_trans_rec FA_API_TYPES.trans_rec_type;
95 l_asset_hdr_rec FA_API_TYPES.asset_hdr_rec_type;
96 l_asset_retire_rec FA_API_TYPES.asset_retire_rec_type;
97 l_asset_dist_tbl FA_API_TYPES.asset_dist_tbl_type;
98 l_subcomp_tbl FA_API_TYPES.subcomp_tbl_type;
99 l_inv_tbl FA_API_TYPES.inv_tbl_type;
100
101 CURSOR qualified_assets IS
102 SELECT ret.asset_id,
103 ret.rowid,
104 ret.status,
105 ret.retirement_id,
106 ret.transaction_header_id_in,
107 -- ret.transaction_header_id_out
108 ret.cost_retired,
109 ret.units,
110 ret.date_effective,
111 ret.retirement_prorate_convention,
112 ret.nbv_retired,
113 ret.gain_loss_amount,
114 ret.proceeds_of_sale,
115 ret.cost_of_removal,
116 ret.gain_loss_type_code,
117 ret.retirement_type_code,
118 ret.itc_recaptured ,
119 ret.itc_recapture_id ,
120 ret.stl_method_code ,
121 ret.stl_life_in_months ,
122 ret.stl_deprn_amount ,
123 ret.reval_reserve_retired,
124 ret.unrevalued_cost_retired,
125 ret.book_type_code,
126 ihrd.current_cost
127 FROM fa_retirements ret,
128 fa_hr_retirement_details ihrd
129 WHERE ihrd.batch_id = x_batch_num
130 AND ret.retirement_id = ihrd.retirement_id
131 AND ret.transaction_header_id_out IS NULL
132 AND ret.date_retired BETWEEN x_fy_Start_Date
133 AND x_fy_End_Date
134 FOR UPDATE NOWAIT;
135
136 CURSOR C_dist_lines IS
137 SELECT distribution_id,
138 units_Assigned,
139 transaction_header_id_in,
140 transaction_units,
141 transaction_header_id_out
142 FROM fa_distribution_history
143 WHERE retirement_id = LV_retirement_id
144 FOR UPDATE NOWAIT ;
145
146 TYPE ErrorRecTyp IS RECORD(
147 rejection_reason VARCHAR2(250) );
148
149 TYPE ErrorTabTyp IS TABLE OF ErrorRecTyp
150 INDEX BY BINARY_INTEGER;
151
152 v_Error_Tab ErrorTabTyp; -- error table
153
154 BEGIN -- hr_Reinstate
155
156 -- initializing parameters
157 v_sysdate:= sysdate;
158 v_user:= nvl(TO_NUMBER(fnd_profile.value('USER_ID')),-1);
159 v_last_update_login:= nvl(TO_NUMBER(fnd_profile.value('LOGIN_ID')),-1);
160
161 -- use the concurrent request id as the mass reference.
162 LV_Mass_Reference_Id := x_Conc_Request_Id;
163
164 FND_CURRENCY.GET_INFO(x_Currency_Code,
165 LV_Precision,
166 LV_Ext_Precision,
167 LV_Min_Acct_Unit);
168
169 OPEN qualified_assets;
170 LOOP -- qualified_assets
171 FETCH qualified_assets INTO LV_Asset_Id,
172 LV_retirement_rowid,
173 LV_Retirement_Status,
174 LV_Retirement_Id,
175 LV_Transaction_Header_In,
176 -- LV_transaction_header_out,
177 LV_Cost_Retired,
178 LV_units,
179 LV_date_effective,
180 LV_ret_prorate_convention,
181 LV_nbv_retired,
182 LV_gain_loss_amount,
183 LV_proceeds_of_sale,
184 LV_cost_of_removal,
185 LV_gain_loss_type_code,
186 LV_retirement_type_code,
187 LV_itc_recaptured ,
188 LV_itc_recapture_id ,
189 LV_stl_method_code ,
190 LV_stl_life_in_months ,
191 LV_stl_deprn_amount ,
192 LV_reval_reserve_retired,
193 LV_unrevalued_cost_retired,
194 LV_book_type_code,
195 LV_current_cost;
196 EXIT WHEN qualified_assets%NOTFOUND;
197
198 l_asset_retire_rec.retirement_id := LV_Retirement_Id;
199
200 IF LV_Retirement_Status = 'PENDING' THEN
201
202 FA_RETIREMENT_PUB.undo_retirement(
203 p_api_version => l_api_version
204 ,p_init_msg_list => l_init_msg_list
205 ,p_commit => l_commit
206 ,p_validation_level => l_validation_level
207 ,p_calling_fn => 'FA_CUA_HR_REINSTATEMENTS_PKG.Reinstate'
208 ,x_return_status => l_return_status
209 ,x_msg_count => l_msg_count
210 ,x_msg_data => l_msg_data
211
212 ,px_trans_rec => l_trans_rec
213 ,px_asset_hdr_rec => l_asset_hdr_rec
214 ,px_asset_retire_rec => l_asset_retire_rec
215 );
216
217 ELSIF LV_Retirement_Status = 'PROCESSED' THEN
218
219 FA_RETIREMENT_PUB.do_reinstatement(
220 p_api_version => l_api_version
221 ,p_init_msg_list => l_init_msg_list
222 ,p_commit => l_commit
223 ,p_validation_level => l_validation_level
224 ,p_calling_fn => 'FA_CUA_HR_REINSTATEMENTS_PKG.Reinstate'
225 ,x_return_status => l_return_status
226 ,x_msg_count => l_msg_count
227 ,x_msg_data => l_msg_data
228
229 ,px_trans_rec => l_trans_rec
230 ,px_asset_hdr_rec => l_asset_hdr_rec
231 ,px_asset_retire_rec => l_asset_retire_rec
232 ,p_asset_dist_tbl => l_asset_dist_tbl
233 ,p_subcomp_tbl => l_subcomp_tbl
234 ,p_inv_tbl => l_inv_tbl
235 );
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 END LOOP;
381 END conc_request;
382
383 END FA_CUA_HR_REINSTATEMENTS_PKG;