1 PACKAGE BODY FA_TRANSFERS_PKG as
2 /* $Header: faxtfrb.pls 120.8 2009/03/27 10:20:10 bridgway ship $ */
3
4 PROCEDURE INITIALIZE(X_Asset_Id NUMBER,
5 X_Book_Type_Code IN OUT NOCOPY VARCHAR2,
6 X_From_Block VARCHAR2,
7 X_Transaction_Date_Entered IN OUT NOCOPY DATE,
8 X_Acct_Flex_Num IN OUT NOCOPY NUMBER,
9 X_Calendar_Period_Open_Date IN OUT NOCOPY DATE,
10 X_Calendar_Period_Close_Date IN OUT NOCOPY DATE,
11 X_Transfer_In_PC IN OUT NOCOPY NUMBER,
12 X_Current_PC IN OUT NOCOPY NUMBER,
13 X_FY_Start_Date IN OUT NOCOPY DATE,
14 X_FY_End_Date IN OUT NOCOPY DATE,
15 X_Max_Transaction_Date IN OUT NOCOPY DATE,
16 X_Just_Added_To_Tax_Book IN OUT NOCOPY VARCHAR2,
17 X_Asset_Type IN OUT NOCOPY VARCHAR2,
18 X_Category_Id IN OUT NOCOPY NUMBER,
19 X_Calling_Fn VARCHAR2
20 , p_log_level_rec IN FA_API_TYPES.log_level_rec_type) IS
21 Lv_Count Number;
22 Lv_Fiscal_Year Number(4);
23 Lv_Fiscal_Year_Name Varchar2(30);
24 Lv_AH_Units Number;
25 Lv_DH_Units Number;
26 Lv_Message Varchar2(30);
27 Validation_Error Exception;
28 BEGIN
29 if (X_From_Block = 'ASSETS_FDR' or X_From_Block = 'ASSET') then
30 begin
31 select bc.book_type_code
32 into X_Book_Type_Code
33 from fa_book_controls bc,
34 fa_books bk
35 where bk.asset_id = X_Asset_Id
36 and bk.book_type_code = bc.book_type_code
37 and bk.date_ineffective is null
38 and nvl(bc.date_ineffective, sysdate+1) > sysdate
39 and bc.book_class = 'CORPORATE';
40 exception
41 when no_data_found then
42 lv_message := 'FA_TFR_ADD_ASSET_TO_BOOK';
43 raise validation_error;
44 when others then raise;
45 end;
46 --
47 select greatest(calendar_period_open_date,
48 least(sysdate, calendar_period_close_date)),
49 calendar_period_open_date,
50 calendar_period_close_date,
51 period_counter
52 into X_Transaction_Date_Entered,
53 X_Calendar_Period_Open_Date,
54 X_Calendar_Period_Close_Date,
55 X_Current_PC
56 from fa_deprn_periods
57 where book_type_code = X_Book_Type_Code
58 and period_close_date is null;
59 --
60 select dp.period_counter
61 into X_Transfer_In_PC
62 from fa_deprn_periods dp, fa_transaction_headers th
63 where th.asset_id = X_Asset_Id
64 and th.book_type_code = X_Book_Type_Code
65 and th.transaction_type_code = 'TRANSFER IN'
66 and dp.book_type_code = X_Book_Type_Code
67 and th.date_effective between dp.period_open_date
68 and nvl(dp.period_close_date, sysdate);
69 --
70 select fiscal_year_name, current_fiscal_year
71 into lv_fiscal_year_name, lv_fiscal_year
72 from fa_book_controls
73 where book_type_code = X_Book_Type_Code;
74 --
75 if X_Transfer_In_PC < X_Current_PC then
76 -- used for trans_date_entered validation only
77 select start_date, end_date
78 into X_FY_Start_Date, X_FY_End_Date
79 from fa_fiscal_year
80 where fiscal_year = lv_fiscal_year
81 and fiscal_year_name = lv_fiscal_year_name;
82 --
83
84 /* select max(transaction_date_entered)
85 into X_Max_Transaction_Date
86 from fa_transaction_headers
87 where asset_id = X_Asset_Id
88 and book_type_code = X_Book_Type_Code; */
89
90 if not FA_UTIL_PVT.get_latest_trans_date
91 (p_calling_fn => 'fa_transfers_pkg.initialize'
92 ,p_asset_id => X_Asset_Id
93 ,p_book => X_Book_Type_Code
94 ,x_latest_trans_date => X_Max_Transaction_Date, p_log_level_rec => p_log_level_rec) then
95
96 raise FND_API.G_EXC_UNEXPECTED_ERROR;
97 end if;
98
99 end if; /* if X_Transfer_In_PC < X_Current_PC */
100 end if; /* if X_From_Block = ASSET or ASSETS_FDR */
101 --
102 if X_From_Block <> 'FA_BOOKS' /* no asset_id yet for tfr in */ then
103 -- make sure units are in sync
104 select units into lv_ah_units
105 from fa_asset_history
106 where asset_id = X_Asset_Id
107 and date_ineffective is null;
108 --
109 select sum(units_assigned - nvl(transaction_units, 0))
110 into lv_dh_units
111 from fa_distribution_history
112 where asset_id = X_Asset_Id
113 and book_type_code = X_Book_Type_Code
114 and date_ineffective is null;
115 --
116 if lv_ah_units <> lv_dh_units then
117 --units are out of sync. Don't allow this transaction
118 lv_message := 'FA_UNITS_DIFFERENT';
119 raise validation_error;
120 end if;
121 --
122 select count(*)
123 into lv_count
124 from fa_books bk, fa_book_controls bc
125 where bk.asset_id = X_Asset_Id
126 and bk.date_ineffective is null
127 and bk.book_type_code = bc.book_type_code
128 and bc.book_class = 'BUDGET'
129 and nvl(bc.date_ineffective, sysdate+1) > sysdate;
130 --
131 -- can't be in Budget book. error.
132 if lv_count > 0 then
133 lv_message := 'FA_CANT_TRANSACT';
134 raise validation_error;
135 end if;
136 -- Pending Retirements?
137 select count(*)
138 into lv_count
139 from fa_retirements
140 where asset_id = X_Asset_Id
141 and book_type_code = X_Book_Type_Code
142 and status in ('PENDING','REINSTATE');
143 --
144 if lv_count > 0 then
145 lv_message := 'FA_RET_PENDING_RETIREMENTS';
146 raise validation_error;
147 end if;
148 -- Asset already fully retired?
149 select count(*)
150 into lv_count
151 from fa_books
152 where asset_id = X_Asset_Id
153 and book_type_code = X_Book_Type_Code
154 and date_ineffective is null
155 and period_counter_fully_retired is not null;
156 --
157 if lv_count > 0 then
158 lv_message := 'FA_SHARED_RETIRED_ASSET';
159 raise validation_error;
160 end if;
161 --
162 -- if asset in (CIP) ADDITION period in assoc TAX books, update
163 -- distributions in those books
164 -- do this in the form, using INS_DETAIL
165 select count(1)
166 into lv_count
167 from fa_transaction_headers th,
168 fa_deprn_periods dp,
169 fa_book_controls bc
170 where bc.book_class <> 'CORPORATE'
171 and bc.distribution_source_book = X_Book_Type_Code
172 and th.book_type_code = bc.book_type_code
173 and th.asset_id = X_Asset_Id
174 and th.transaction_type_code||'' in ('ADDITION','CIP ADDITION')
175 and dp.book_type_code = bc.book_type_code
176 and dp.period_close_date is null
177 and th.date_effective >= dp.period_open_date
178 and bc.date_ineffective is null;
179 --
180 if lv_count > 0 then
181 X_Just_Added_To_Tax_Book := 'TRUE';
182 end if;
183 --
184 -- used in Transfers user exit
185 select asset_type, category_id
186 into X_Asset_Type, X_Category_Id
187 from fa_asset_history
188 where asset_id = X_Asset_Id
189 and date_ineffective is null;
190 end if; /* if X_From_Block <> 'BOOKS' */
191 --
192 select accounting_flex_structure
193 into X_Acct_Flex_Num
194 from fa_book_controls
195 where book_type_code = X_Book_Type_Code;
196 EXCEPTION
197 WHEN Validation_Error THEN
198 FA_STANDARD_PKG.RAISE_ERROR
199 (Called_Fn => 'FA_TRANSFER_PKG.Initialize',
200 Calling_Fn => X_Calling_Fn,
201 Name => LV_Message, p_log_level_rec => p_log_level_rec);
202 WHEN Others THEN
203 FA_STANDARD_PKG.RAISE_ERROR
204 (Called_Fn => 'FA_TRANSFER_PKG.Initialize',
205 Calling_Fn => X_Calling_Fn, p_log_level_rec => p_log_level_rec);
206 END Initialize;
207
208 END FA_TRANSFERS_PKG;