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