[Home] [Help]
PACKAGE BODY: APPS.EAM_CAP_MAIN_COST_PVT
Source
1 package body EAM_CAP_MAIN_COST_PVT as
2 /* $Header: EAMCOMCB.pls 120.7.12010000.7 2009/01/09 00:01:46 lakmohan ship $ */
3
4 PROCEDURE initiate_capitalization (p_eam_wo_bills_tbl IN eam_cap_main_cost_pvt.eam_wo_bills_tbl,
5 x_return_status OUT NOCOPY VARCHAR2,
6 x_msg_data OUT NOCOPY VARCHAR2,
7 x_msg_count OUT NOCOPY NUMBER) IS
8
9 l_fa_asset_id NUMBER := NULL;
10 l_cost NUMBER := NULL;
11 l_book_type_code VARCHAR2(15) := NULL;
12 l_asset_category_id NUMBER := NULL;
13 l_asset_number VARCHAR2(15) := NULL;
14 l_mass_addition_id NUMBER := NULL;
15 l_in_use_flag VARCHAR2(3) := NULL;
16 l_deprn_calendar VARCHAR2(15);
17 l_dep_date DATE;
18 l_last_dep_run_date DATE;
19 l_period_name VARCHAR2(15);
20 l_transaction_type_code VARCHAR2(15) := NULL;
21 l_transaction_date DATE := NULL;
22 l_add_to_asset BOOLEAN := TRUE;
23 l_post_asset_category_id NUMBER := NULL;
24 l_merged_code VARCHAR2(3) := NULL;
25 l_merge_parent_id NUMBER := NULL;
26 l_posting_status VARCHAR2(6) := NULL;
27 l_queue_name VARCHAR2(6) := NULL;
28 csi_asset_rec_not_found EXCEPTION;
29 fa_not_found EXCEPTION;
30 cost_not_defined EXCEPTION;
31 book_type_not_defined EXCEPTION;
32
33 CURSOR c_inst_asset_info(pc_asset_group_id IN NUMBER,
34 pc_rebuild_item_id IN NUMBER,
35 pc_asset_number IN VARCHAR2,
36 pc_rebuild_serial_number IN VARCHAR2,
37 pc_org_id IN NUMBER,
38 pc_inventory_item_id IN NUMBER) IS
39 SELECT cia.fa_asset_id,
40 cia.fa_book_type_code
41 FROM csi_item_instances cii,
42 csi_i_assets cia
43 WHERE cii.instance_id = cia.instance_id
44 AND cii.inventory_item_id = NVL(pc_asset_group_id,pc_rebuild_item_id)
45 AND cii.instance_number = NVL(pc_asset_number, pc_rebuild_serial_number)
46 AND cia.asset_quantity > 0
47 AND cia.update_status = 'IN_SERVICE';
48
49 r_inst_asset_info c_inst_asset_info%rowtype;
50
51 CURSOR c_fa_asset_number (pc_fixed_asset_id IN NUMBER) IS
52 SELECT fa.asset_category_id,
53 fa.asset_number,
54 fa.in_use_flag
55 FROM fa_additions_b fa
56 WHERE asset_id = pc_fixed_asset_id;
57
58 r_fa_asset_number c_fa_asset_number%rowtype;
59
60
61 --Bug 6640036 Add function to retrieve the open FA period and its end date
62
63 CURSOR c_dep_date (c_calendar_type in varchar, c_book_type_code IN varchar, c_period_name in varchar) IS
64 SELECT END_DATE
65 FROM FA_CALENDAR_PERIODS FAP,
66 fa_book_controls FAC
67 WHERE FAP.calendar_type=c_calendar_type
68 AND FAC.BOOk_TYPE_CODE =c_book_type_code
69 AND FAP.PERIOD_NAME=c_period_name;
70
71 CURSOR c_curr_dep_prd (c_book_type_code IN varchar) IS
72 Select dp.period_name,
73 bc.last_deprn_run_date,
74 bc.deprn_calendar
75 from fa_deprn_periods dp,
76 fa_deprn_periods dp2,
77 fa_deprn_periods dp3,
78 fa_book_controls bc
79 where dp.book_type_code =c_book_type_code
80 and dp.period_close_date is null
81 and dp2.book_type_code(+) = bc.distribution_source_book
82 and dp2.period_counter(+) = bc.last_mass_copy_period_counter
83 and dp3.book_type_code(+) = bc.book_type_code
84 and dp3.period_counter(+) = bc.last_purge_period_counter
85 and bc.book_type_code = c_book_type_code;
86
87
88 BEGIN
89
90 SAVEPOINT initiate_capitalization;
91
92 x_return_status := fnd_api.G_RET_STS_SUCCESS ;
93
94 -- Insert into the FA_MASS_ADDITIONS and EAM_WORK_ORER_BILLS tables
95
96 IF p_eam_wo_bills_tbl.count > 0 THEN
97
98 FOR i IN p_eam_wo_bills_tbl.first .. p_eam_wo_bills_tbl.last LOOP
99
100 l_fa_asset_id := NULL;
101 l_cost := NULL;
102 l_book_type_code := NULL;
103 l_asset_category_id := NULL;
104 l_asset_number := NULL;
105 l_in_use_flag := NULL;
106 l_add_to_asset := TRUE;
107 l_post_asset_category_id := NULL;
108 -- Get the asset information from CSI_I_ASSETS
109 OPEN c_inst_asset_info(p_eam_wo_bills_tbl(i).asset_group_id,
110 p_eam_wo_bills_tbl(i).rebuild_item_id,
111 p_eam_wo_bills_tbl(i).asset_number,
112 p_eam_wo_bills_tbl(i).rebuild_serial_number,
113 p_eam_wo_bills_tbl(i).organization_id,
114 p_eam_wo_bills_tbl(i).billed_inventory_item_id);
115 FETCH c_inst_asset_info INTO r_inst_asset_info;
116 IF c_inst_asset_info%FOUND then
117 l_fa_asset_id := r_inst_asset_info.fa_asset_id;
118 l_book_type_code := r_inst_asset_info.fa_book_type_code;
119 CLOSE c_inst_asset_info;
120 ELSE
121 CLOSE c_inst_asset_info;
122 l_add_to_asset := FALSE;
123 l_book_type_code := get_book_type(p_eam_wo_bills_tbl(i).organization_id);
124 IF l_book_type_code IS NULL THEN
125 RAISE book_type_not_defined;
126 END IF ;
127 END IF;-- Added CU Project
128
129 IF l_add_to_asset = TRUE then
130 -- Get the FA Asset Number from FA_ADDITIONS_B
131 OPEN c_fa_asset_number (l_fa_asset_id);
132 FETCH c_fa_asset_number INTO r_fa_asset_number;
133 IF c_fa_asset_number%FOUND THEN
134 l_asset_category_id := r_fa_asset_number.asset_category_id;
135 l_asset_number := r_fa_asset_number.asset_number;
136 l_in_use_flag := r_fa_asset_number.in_use_flag;
137 CLOSE c_fa_asset_number;
138 ELSE
139 CLOSE c_fa_asset_number;
140 RAISE fa_not_found;
141 END IF;
142
143 -- Get the FA Cost
144 l_cost := get_fa_book_cost (l_fa_asset_id,
145 l_book_type_code);
146 IF l_cost IS NULL THEN
147 Raise cost_not_defined;
148 END IF;
149 ELSE
150 l_post_asset_category_id := get_asset_category_id (nvl(p_eam_wo_bills_tbl(i).asset_group_id,p_eam_wo_bills_tbl(i).rebuild_item_id),
151 p_eam_wo_bills_tbl(i).organization_id);
152 END IF; --Added for CU Project
153
154 --Bug 6640036 Get the current open period and its end date
155 BEGIN
156 OPEN c_curr_dep_prd(l_book_type_code);
157 FETCH c_curr_dep_prd INTO l_period_name, l_last_dep_run_date,l_deprn_calendar ;
158 CLOSE c_curr_dep_prd ;
159 EXCEPTION
160 WHEN others then
161 NULL;
162 END;
163
164 IF (l_period_name is not null) THEN
165 BEGIN
166 OPEN c_dep_date(l_deprn_calendar,l_book_type_code,l_period_name);
167 FETCH c_dep_date INTO l_dep_date ;
168 CLOSE c_dep_date ;
169 EXCEPTION
170 WHEN others then
171 NULL;
172 END;
173 END IF;
174
175 --Bug 6640036 Check the date against the current open period date
176 IF TRUNC(sysdate) > TRUNC(l_dep_date) THEN
177 IF l_add_to_asset = TRUE then
178 l_transaction_type_code := 'FUTURE ADJ';
179 l_transaction_date := sysdate;
180 /*Added for CU Project*/
181 ELSE
182 l_transaction_type_code := 'FUTURE ADD';
183 l_transaction_date := sysdate;
184 END IF;
185
186 END IF;
187
188 SELECT fa_mass_additions_s.nextval
189 INTO l_mass_addition_id
190 FROM SYS.DUAL ;
191
192 IF p_eam_wo_bills_tbl.count > 0 AND l_add_to_asset = FALSE THEN
193 IF i = p_eam_wo_bills_tbl.first THEN
194 l_merged_code := 'MP';
195 l_posting_status := 'NEW';
196 l_queue_name := 'NEW';
197 ELSE
198 l_merged_code := 'MC';
199 l_posting_status := 'MERGED';
200 l_queue_name := 'POST';
201 END IF;
202 END IF;
203
204 INSERT INTO fa_mass_additions
205 (mass_addition_id,
206 add_to_asset_id,
207 asset_category_id,
208 book_type_code,
209 fixed_assets_cost,
210 feeder_system_name,
211 posting_status,
212 queue_name,
213 transaction_date, -- Bug 6640036
214 transaction_type_code,
215 in_use_flag,
216 reviewer_comments,
217 payables_code_combination_id,
218 payables_cost,
219 merged_code,
220 MERGE_PARENT_MASS_ADDITIONS_ID, -- Bug 7678186
221 CREATED_BY,
222 CREATION_DATE,
223 LAST_UPDATED_BY,
224 LAST_UPDATE_DATE,
225 LAST_UPDATE_LOGIN
226 )
227 VALUES
228 (l_mass_addition_id,
229 l_fa_asset_id,
230 l_post_asset_category_id,
231 l_book_type_code,
232 p_eam_wo_bills_tbl(i).billed_amount, -- Add to table will be passed in from form
233 'ENTERPRISE ASSET MANAGEMENT',
234 decode(l_fa_asset_id, null, l_posting_status,'POST'),
235 decode(l_fa_asset_id, null, l_queue_name,'ADD TO ASSET'), --Added decode for CU Project
236 l_transaction_date, -- Bug 6640036
237 l_transaction_type_code, --Bug 6640036
238 nvl(l_in_use_flag,'N'),
239 p_eam_wo_bills_tbl(i).comments,
240 p_eam_wo_bills_tbl(i).offset_account_ccid, -- Offset/Clearance Account
241 p_eam_wo_bills_tbl(i).billed_amount, -- To credit Clearance A/C with this amount
242 l_merged_code,
243 l_merge_parent_id,
244 fnd_global.user_id,
245 sysdate,
246 fnd_global.user_id,
247 sysdate,
248 fnd_global.user_id);
249
250
251 INSERT INTO eam_work_order_bills(
252 organization_id,
253 wip_entity_id,
254 operation_seq_num,
255 inventory_item_id,
256 resource_id,
257 billed_inventory_item_id,
258 billed_uom_code,
259 billed_quantity,
260 cost_or_listprice,
261 costplus_percentage,
262 billed_amount,
263 billing_method,
264 fixed_asset_number,
265 mass_addition_id,
266 capitalization_date,
267 comments,
268 offset_account_ccid,
269 CREATED_BY,
270 CREATION_DATE,
271 LAST_UPDATED_BY,
272 LAST_UPDATE_DATE,
273 LAST_UPDATE_LOGIN )
274 VALUES (
275 p_eam_wo_bills_tbl(i).organization_id,
276 p_eam_wo_bills_tbl(i).wip_entity_id,
277 p_eam_wo_bills_tbl(i).operation_seq_num,
278 p_eam_wo_bills_tbl(i).billed_inventory_item_id,
279 p_eam_wo_bills_tbl(i).resource_id,
280 nvl(p_eam_wo_bills_tbl(i).billed_inventory_item_id,-123456),
281 p_eam_wo_bills_tbl(i).billed_uom_code,
282 p_eam_wo_bills_tbl(i).billed_quantity,
283 l_cost,
284 p_eam_wo_bills_tbl(i).COSTPLUS_PERCENTAGE, -- = as entered by the user
285 p_eam_wo_bills_tbl(i).billed_amount, -- = Calculated Amount
286 3, -- (Capitalzation)
287 l_fa_asset_id,
288 l_mass_addition_id,
289 sysdate,
290 p_eam_wo_bills_tbl(i).comments,
291 p_eam_wo_bills_tbl(i).offset_account_ccid,
292 fnd_global.user_id,
293 sysdate,
294 fnd_global.user_id,
295 sysdate,
296 fnd_global.user_id);
297
298 -- Added Bug 7678186
299
300 IF i = p_eam_wo_bills_tbl.first AND l_add_to_asset = FALSE THEN
301 l_merge_parent_id := l_mass_addition_id;
302 END IF;
303
304 END LOOP;
305 END IF;
306
307 EXCEPTION
308
309 WHEN csi_asset_rec_not_found THEN
310 ROLLBACK TO initiate_capitalization;
311 x_return_status := FND_API.G_RET_STS_ERROR ;
312 fnd_message.set_name('EAM','EAM_CSI_REC_NOT_FOUND');
313 x_msg_data := fnd_message.get;
314 x_msg_count := 1;
315
316 WHEN cost_not_defined THEN
317 ROLLBACK TO initiate_capitalization;
318 x_return_status := FND_API.G_RET_STS_ERROR ;
319 fnd_message.set_name('EAM','EAM_COST_NOT_DEFINED');
320 x_msg_data := fnd_message.get;
321 x_msg_count := 1;
322
323 WHEN book_type_not_defined THEN
324 ROLLBACK TO initiate_capitalization;
325 x_return_status := FND_API.G_RET_STS_ERROR ;
326 fnd_message.set_name('EAM','EAM_BOOK_TYPE_NOT_DEFINED');
327 x_msg_data := fnd_message.get;
328 x_msg_count := 1;
329
330 WHEN others THEN
331 ROLLBACK TO initiate_capitalization;
332 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
333 fnd_message.set_name('EAM','EAM_ATP_SYSTEM_ERROR');
334 x_msg_data := fnd_message.get;
335 x_msg_count := 1;
336
337 END initiate_capitalization;
338
339 FUNCTION get_fa_book_cost (l_asset_id IN NUMBER,
340 l_book_type_code IN VARCHAR2) RETURN NUMBER IS
341
342 l_cost NUMBER := NULL;
343
344 BEGIN
345 SELECT cost
346 INTO l_cost
347 FROM fa_books
348 WHERE transaction_header_id_out IS NULL
349 AND asset_id = l_asset_id
350 AND book_type_code = l_book_type_code;
351
352 RETURN l_cost;
353
354 EXCEPTION
355 when NO_DATA_FOUND then
356 l_cost := NULL;
357 RETURN l_cost;
358 END get_fa_book_cost;
359
360 FUNCTION get_book_type (l_org_id IN NUMBER) RETURN VARCHAR2 IS
361 l_txn_ou_context NUMBER := NULL;
362 l_book_type_code VARCHAR2(15) := NULL;
363 BEGIN
364 SELECT ood.operating_unit
365 INTO l_txn_ou_context
366 FROM org_organization_definitions ood
367 WHERE ood.organization_id = l_org_id
368 AND ROWNUM = 1;
369
370 l_book_type_code := fnd_profile.VALUE_SPECIFIC(
371 name => 'eam_fa_book_type_code',
372 ORG_ID => l_txn_ou_context
373 );
374 RETURN l_book_type_code;
375 EXCEPTION
376 when NO_DATA_FOUND then
377 l_book_type_code := NULL;
378 RETURN l_book_type_code;
379
380
381
382 END get_book_type;
383
384 FUNCTION get_asset_category_id (l_inventory_item_id IN NUMBER,
385 l_org_id IN NUMBER) RETURN NUMBER IS
386
387 l_post_asset_category_id NUMBER := NULL;
388
389 BEGIN
390 select asset_category_id
391 into l_post_asset_category_id
392 from mtl_system_items_b
393 where inventory_item_id = l_inventory_item_id
394 and organization_id = l_org_id;
395
396 RETURN l_post_asset_category_id;
397
398 EXCEPTION
399 when NO_DATA_FOUND then
400 l_post_asset_category_id := NULL;
401 RETURN l_post_asset_category_id;
402 END get_asset_category_id;
403
404 END EAM_CAP_MAIN_COST_PVT;