[Home] [Help]
PACKAGE BODY: APPS.FA_RETIRE_PKG
Source
1 PACKAGE BODY FA_RETIRE_PKG as
2 /* $Header: faxretb.pls 120.11 2009/03/27 04:26:04 bridgway ship $ */
3
4 g_print_debug boolean := fa_cache_pkg.fa_print_debug;
5
6 PROCEDURE Initialize(X_Book_Type_Code VARCHAR2,
7 X_Asset_Id NUMBER,
8 X_Cost OUT NOCOPY NUMBER,
9 X_Current_Units OUT NOCOPY NUMBER,
10 X_Date_Retired OUT NOCOPY DATE,
11 X_Current_Fiscal_Year IN OUT NOCOPY NUMBER,
12 X_Book_Class IN OUT NOCOPY VARCHAR2,
13 X_FY_Start_Date OUT NOCOPY DATE,
14 X_FY_End_Date OUT NOCOPY DATE,
15 X_Current_Period_Counter OUT NOCOPY NUMBER,
16 X_Asset_Added_PC OUT NOCOPY NUMBER,
17 X_Calendar_Period_Close_Date OUT NOCOPY DATE,
18 X_Max_Transaction_Date_Entered OUT NOCOPY DATE,
19 X_Asset_Type IN OUT NOCOPY VARCHAR2,
20 X_Ret_Prorate_Convention IN OUT NOCOPY VARCHAR2,
21 X_Use_STL_Retirements_Flag IN OUT NOCOPY VARCHAR2,
22 X_STL_Method_Code IN OUT NOCOPY VARCHAR2,
23 X_STL_Life_In_Months OUT NOCOPY NUMBER,
24 X_Calling_Fn VARCHAR2,
25 p_log_level_rec IN FA_API_TYPES.log_level_rec_type) IS
26 LV_Fiscal_Year_Name VARCHAR2(30);
27 LV_Count NUMBER;
28 LV_Category_Id NUMBER;
29 LV_Date_Placed_In_Service DATE;
30 LV_PC_Fully_Retired NUMBER;
31 LV_Unit_Adjustment_Flag VARCHAR2(3);
32 LV_Message VARCHAR2(50);
33 Validation_Error EXCEPTION;
34 BEGIN
35 -- check if there is an add-to-asset transaction pending
36 SELECT count(*)
37 INTO LV_Count
38 FROM FA_MASS_ADDITIONS
39 WHERE BOOK_TYPE_CODE = X_Book_Type_Code
40 AND ADD_TO_ASSET_ID = X_Asset_Id
41 AND POSTING_STATUS NOT IN ('POSTED','MERGED','SPLIT','DELETE');
42 --
43 if (LV_Count <> 0) then
44 Lv_Message := 'FA_RET_CANT_RET_INCOMPLETE_ASS';
45 raise Validation_Error;
46 end if;
47 -- check if another retirement/reinstatement already pending
48 SELECT count(*)
49 INTO LV_Count
50 FROM FA_RETIREMENTS
51 WHERE ASSET_ID = X_Asset_Id
52 AND BOOK_TYPE_CODE = X_Book_Type_Code
53 AND STATUS IN ('PENDING','REINSTATE');
54 --
55 if (LV_Count <> 0) then
56 Lv_Message := 'FA_RET_PENDING_RETIREMENTS';
57 raise Validation_Error;
58 end if;
59 --
60 SELECT
61 bk.cost,
62 bk.date_placed_in_service,
63 bk.period_counter_fully_retired,
64 ah.units,
65 ah.category_id,
66 ah.asset_type,
67 greatest(least(dp.calendar_period_close_date, sysdate),
68 dp.calendar_period_open_date),
69 dp.period_counter,
70 dp.calendar_period_close_date,
71 ad.unit_adjustment_flag
72 INTO
73 X_Cost,
74 LV_Date_Placed_In_Service,
75 LV_PC_Fully_Retired,
76 X_Current_Units,
77 LV_Category_Id,
78 X_Asset_Type,
79 X_Date_Retired,
80 X_Current_Period_Counter,
81 X_Calendar_Period_Close_Date,
82 LV_Unit_Adjustment_Flag
83 FROM
84 fa_books bk,
85 fa_asset_history ah,
86 fa_deprn_periods dp,
87 fa_additions ad
88 WHERE
89 bk.book_type_code = X_Book_Type_Code and
90 bk.asset_id = X_Asset_Id and
91 bk.date_ineffective is null
92 and
93 ah.asset_id = X_Asset_Id and
94 ah.date_ineffective is null
95 and
96 dp.book_type_code = X_Book_Type_Code and
97 dp.period_close_Date is null
98 and
99 ad.asset_id = X_Asset_Id;
100 --
101 if (LV_PC_Fully_Retired is not null) then
102 Lv_Message := 'FA_SHARED_RETIRED_ASSET';
103 raise Validation_Error;
104 end if;
105 --
106 if (LV_Unit_Adjustment_Flag = 'YES') then
107 Lv_Message := 'FA_RET_CHANGE_UNITS_TFR_FORM';
108 raise Validation_Error;
109 end if;
110 --
111 SELECT current_fiscal_year,book_class,fiscal_year_name
112 INTO X_Current_Fiscal_Year,X_Book_Class,LV_Fiscal_Year_Name
113 FROM fa_book_controls
114 WHERE book_type_code = X_Book_Type_Code;
115 --
116 SELECT start_date, end_date
117 INTO X_FY_Start_Date, X_FY_End_Date
118 FROM FA_FISCAL_YEAR
119 WHERE Fiscal_Year = X_Current_Fiscal_Year
120 AND Fiscal_Year_Name = LV_Fiscal_Year_Name;
121 -- Check when asset was added
122 SELECT dp.period_counter
123 INTO X_Asset_Added_PC
124 FROM FA_TRANSACTION_HEADERS TH,
125 FA_DEPRN_PERIODS DP
126 WHERE TH.ASSET_ID = X_ASSET_ID
127 AND TH.BOOK_TYPE_CODE = X_BOOK_TYPE_CODE
128 AND TH.TRANSACTION_TYPE_CODE||'' =
129 DECODE(X_Book_Class,'CORPORATE','TRANSFER IN', 'ADDITION')
130 AND TH.DATE_EFFECTIVE BETWEEN DP.PERIOD_OPEN_DATE
131 AND NVL(DP.PERIOD_CLOSE_DATE, SYSDATE)
132 AND DP.BOOK_TYPE_CODE = X_BOOK_TYPE_CODE;
133 --
134
135 -- commenting for bug 3768867
136 /*SELECT max(transaction_date_entered)
137 INTO X_Max_Transaction_Date_Entered
138 FROM fa_transaction_headers
139 WHERE asset_id = X_Asset_Id
140 and book_type_code = X_Book_Type_Code
141 and transaction_type_code not in ('REINSTATEMENT','FULL RETIREMENT')
142 and transaction_type_code not like '%/VOID';*/ -- bug2107509
143
144 -- adding for bug 3768867
145 if not FA_UTIL_PVT.get_latest_trans_date
146 (p_calling_fn => 'fa_retire_pkg.initialize'
147 ,p_asset_id => X_Asset_Id
148 ,p_book => X_Book_Type_Code
149 ,x_latest_trans_date => X_Max_Transaction_Date_Entered
150 ,p_log_level_rec => p_log_level_rec) then
151 raise FND_API.G_EXC_UNEXPECTED_ERROR;
152 end if;
153
154 --
155 SELECT retirement_prorate_convention,
156 use_stl_retirements_flag,
157 stl_method_code,
158 stl_life_in_months
159 INTO X_Ret_Prorate_Convention,
160 X_Use_STL_Retirements_Flag,
161 X_STL_Method_Code,
162 X_STL_Life_In_Months
163 FROM fa_category_book_defaults
164 WHERE book_type_code = X_Book_Type_Code
165 and category_id = LV_Category_Id
166 and LV_Date_Placed_In_Service between start_dpis and
167 nvl(end_dpis,LV_Date_Placed_In_Service);
168 --
169 if (X_Use_STL_Retirements_Flag = 'NO') then
170 X_STL_Method_Code := NULL;
171 X_STL_Life_In_Months := NULL;
172 end if;
173 EXCEPTION
174 WHEN Validation_Error THEN
175 FA_STANDARD_PKG.RAISE_ERROR
176 (Called_Fn => 'FA_RETIRE_PKG.Initialize',
177 Calling_Fn => X_Calling_Fn,
178 Name => LV_Message
179 ,p_log_level_rec => p_log_level_rec);
180 WHEN Others THEN
181 FA_STANDARD_PKG.RAISE_ERROR
182 (Called_Fn => 'FA_RETIRE_PKG.Initialize',
183 Calling_Fn => X_Calling_Fn
184 ,p_log_level_rec => p_log_level_rec);
185 END Initialize;
186 --
187 END FA_RETIRE_PKG;