[Home] [Help]
PACKAGE BODY: APPS.CSE_ASSET_UTIL_PKG
Source
1 PACKAGE BODY CSE_ASSET_UTIL_PKG AS
2 /* $Header: CSEFAUTB.pls 120.29.12010000.1 2008/07/30 05:17:36 appldev ship $ */
3
4 l_debug varchar2(1) := NVL(fnd_profile.value('cse_debug_option'),'N');
5
6 PROCEDURE debug( p_message IN varchar2) IS
7 BEGIN
8 IF l_debug = 'Y' THEN
9 cse_debug_pub.add(p_message);
10 IF nvl(fnd_global.conc_request_id, -1) <> -1 THEN
11 fnd_file.put_line(fnd_file.log, p_message);
12 END IF;
13 END IF;
14 EXCEPTION
15 WHEN others THEN
16 null;
17 END debug;
18
19 FUNCTION primary_ledger_id(
20 p_organization_id IN number)
21 RETURN number IS
22 l_ledger_id number;
23 BEGIN
24 SELECT ledger_id
25 INTO l_ledger_id
26 FROM cst_acct_info_v
27 WHERE organization_id = p_organization_id;
28
29 RETURN l_ledger_id;
30 END primary_ledger_id;
31
32
33 FUNCTION get_item_cost (
34 p_inventory_item_id IN NUMBER,
35 p_organization_id IN NUMBER)
36 RETURN number IS
37 l_item_cost number := NULL;
38 l_inventory_asset_flag varchar2(1);
39 BEGIN
40
41 SELECT nvl(inventory_asset_flag, 'N')
42 INTO l_inventory_asset_flag
43 FROM mtl_system_items_b
44 WHERE inventory_item_id = p_inventory_item_id
45 AND organization_id = p_organization_id;
46
47 IF l_inventory_asset_flag = 'N' THEN
48 l_item_cost := 0;
49 ELSE
50 l_item_cost := cst_cost_api.get_item_cost (
51 p_api_version => 1.0,
52 p_inventory_item_id => p_inventory_item_id,
53 p_organization_id => p_organization_id);
54 END IF;
55
56 RETURN l_item_cost;
57
58 EXCEPTION
59 WHEN others THEN
60 RETURN null;
61 END get_item_cost;
62
63
64 FUNCTION asset_description(
65 p_asset_attrib_rec IN OUT NOCOPY CSE_DATASTRUCTURES_PUB.asset_attrib_rec,
66 x_error_msg OUT NOCOPY VARCHAR2,
67 x_return_status OUT NOCOPY VARCHAR2) RETURN VARCHAR2
68 IS
69 x_description VARCHAR2(80);
70 l_description VARCHAR2(80);
71 x_hook_used PLS_INTEGER;
72 i NUMBER := 0;
73 e_error EXCEPTION ;
74 l_api_name VARCHAR2(100) := 'CSE_ASSET_UTIL_PKG.asset_description';
75
76 -- For Non Serialized items, Asset description is not based on item as we may
77 -- have asset for multiple items
78
79 CURSOR asset_description_cur (c_org_id IN NUMBER,c_inv_item_id IN NUMBER) IS
80 SELECT substr(msib.description,1,80) asset_description
81 FROM mtl_system_items_b msib
82 WHERE msib.organization_id = c_org_id
83 AND msib.inventory_item_id = c_inv_item_id ;
84
85 BEGIN
86 x_return_status := fnd_api.G_RET_STS_SUCCESS ;
87 i:= 0;
88 cse_asset_client_ext_stub.get_asset_description( p_asset_attrib_rec, x_description, x_hook_used, x_error_msg);
89 l_description := x_description ;
90
91 IF x_hook_used = 1 THEN
92 RETURN l_description ;
93 ELSE
94 OPEN asset_description_cur( p_asset_attrib_rec.organization_id,p_asset_attrib_rec.inventory_item_id);
95 FETCH asset_description_cur INTO l_description;
96 CLOSE asset_description_cur;
97 END IF;
98
99 RETURN l_description ;
100
101 EXCEPTION
102 WHEN NO_DATA_FOUND THEN
103 x_return_status := FND_API.G_RET_STS_ERROR ;
104 fnd_message.set_name('CSE','CSE_FA_CREATION_ATRIB_ERROR');
105 fnd_message.set_token('ASSET_ATTRIBUTE','DESCRIPTION');
106 fnd_message.set_token('CSI_TRANSACTION_ID',p_asset_attrib_rec.transaction_id);
107 x_error_msg := fnd_message.get;
108 RETURN NULL ;
109 WHEN OTHERS THEN
110 x_return_status := FND_API.G_RET_STS_ERROR ;
111 fnd_message.set_name('CSE','CSE_UNEXP_SQL_ERROR');
112 fnd_message.set_token('API_NAME',l_api_name);
113 fnd_message.set_token('SQL_ERROR',SQLERRM);
114 x_error_msg := fnd_message.get;
115 RETURN NULL ;
116 END asset_description ;
117
118 ---------------------------------------------------------------------------+
119 -- Procedure/Function Name : asset_category
120 -- Description : returns asset category ID based on either the
121 -- default logic OR
122 -- the asset category ID derived by client extension.
123 --------------------------------------------------------------------------
124 FUNCTION asset_category(
125 p_asset_attrib_rec IN OUT NOCOPY CSE_DATASTRUCTURES_PUB.asset_attrib_rec,
126 x_error_msg OUT NOCOPY VARCHAR2,
127 x_return_status OUT NOCOPY VARCHAR2) RETURN NUMBER
128 IS
129 l_category_segs VARCHAr2(2000);
130 x_hook_used PLS_INTEGER;
131 l_return_status VARCHAR2(1);
132 e_error EXCEPTION;
133 l_error_msg VARCHAR2(2000);
134 l_txn_class VARCHAR2(30);
135
136 l_api_name VARCHAR2(100) ;
137
138 BEGIN
139 x_return_status := fnd_api.G_RET_STS_SUCCESS ;
140 l_api_name := 'CSE_ASSET_UTIL_PKG.asset_category';
141 cse_asset_client_ext_stub.get_asset_category
142 (p_asset_attrib_rec, --modified the signature for R12
143 x_hook_used,
144 x_error_msg);
145
146 IF x_hook_used = 1
147 THEN
148 RETURN p_asset_attrib_rec.Asset_Category_ID ;
149 ELSE
150
151 get_txn_class (p_asset_attrib_rec => p_asset_attrib_rec ,
152 x_transaction_class => l_txn_class,
153 x_return_status => l_return_status ,
154 x_error_msg => l_error_msg);
155
156 IF l_return_status <> fnd_api.G_RET_STS_SUCCESS
157 THEN
158 RAISE e_error ;
159 END IF ;
160
161 IF l_txn_class <> G_IPV_TXN_CLASS
162 THEN
163 IF p_asset_attrib_rec.inventory_item_id IS NULL
164 THEN
165 RAISE e_error ;
166 END IF ;
167 END IF;
168
169 cse_ipa_trans_pkg.get_fa_asset_category(p_asset_attrib_rec.inventory_item_id,
170 p_asset_attrib_rec.organization_id,
171 p_asset_attrib_rec.transaction_id,
172 p_asset_attrib_rec.Asset_Category_ID,
173 l_category_segs,
174 l_return_status,
175 x_error_msg);
176
177 IF l_return_status <> fnd_api.G_RET_STS_SUCCESS
178 THEN
179 RAISE e_error ;
180 END IF ;
181 RETURN p_asset_attrib_rec.Asset_Category_ID;
182 END IF;
183 EXCEPTION
184 WHEN e_error
185 THEN
186 x_return_status := FND_API.G_RET_STS_ERROR ;
187 fnd_message.set_name('CSE','CSE_ASSET_CAT_ERROR');
188 x_error_msg := fnd_message.get;
189 RETURN NULL ;
190 WHEN OTHERS
191 THEN
192 x_return_status := FND_API.G_RET_STS_ERROR ;
193 fnd_message.set_name('CSE','CSE_UNEXP_SQL_ERROR');
194 fnd_message.set_token('API_NAME',l_api_name);
195 fnd_message.set_token('SQL_ERROR',SQLERRM);
196 x_error_msg := fnd_message.get;
197 RETURN NULL;
198 END asset_category ;
199
200 ---------------------------------------------------------------------------+
201 -- Procedure/Function Name : book_type
202 -- Description : Returns FA Book Type Code based on either the
203 -- default logic OR the FA book type code derived
204 -- by client extension.
205 -- Fan Li August 27, 2007 Support for Multiple FA Book Type
206 -- against Projects Flow
207 --------------------------------------------------------------------------
208 FUNCTION book_type(
209 p_asset_attrib_rec IN OUT NOCOPY CSE_DATASTRUCTURES_PUB.asset_attrib_rec,
210 x_error_msg OUT NOCOPY VARCHAR2,
211 x_return_status OUT NOCOPY VARCHAR2) RETURN VARCHAR2
212 IS
213 x_hook_used PLS_INTEGER;
214 l_txn_process_flag VARCHAR2(1);
215 l_asset_creation_code VARCHAR2(1);
216 e_error EXCEPTION;
217 l_api_name VARCHAR2(100) := 'CSE_ASSET_UTIL_PKG.Book_Type';
218 l_txn_ou_context NUMBER; -- Bug 6492235, added to support multiple FA book
219 BEGIN
220 x_return_status := fnd_api.G_RET_STS_SUCCESS ;
221 cse_asset_client_ext_stub.get_book_type(p_asset_attrib_rec, --modified the signature for R12
222 x_hook_used,
223 x_error_msg);
224
225 IF x_hook_used = 1
226 THEN
227 RETURN p_asset_attrib_rec.book_type_code;
228 ELSE
229 -- Changed for Multiple FA books. Get the OU context for inventory txns
230 -- If not inventory txn then l_txn_ou_context is null and site level
231 -- fa book type would be read.
232 BEGIN
233 DEBUG('inside '|| l_api_name || 'Get INV OU context');
234 SELECT ood.operating_unit
235 INTO l_txn_ou_context
236 FROM org_organization_definitions ood,
237 mtl_material_transactions mmt,
238 csi_inst_txn_details_v citdv
239 WHERE citdv.transaction_id = p_asset_attrib_rec.transaction_id
240 AND citdv.inv_material_transaction_id = mmt.transaction_id
241 AND mmt.organization_id = ood.organization_id
242 AND ROWNUM = 1;
243 EXCEPTION
244 When no_data_found then
245 -- This may be a projects flow. Get the operating unit context
246 -- from the project.
247 BEGIN
248 DEBUG('inside '|| l_api_name || 'Get PA OU context');
249 SELECT pa.ORG_ID
250 INTO l_txn_ou_context
251 FROM csi_item_instances_h ciih,
252 csi_item_instances cii,
253 csi_transactions ct,
254 pa_projects_all pa
255 WHERE ciih.transaction_id = p_asset_attrib_rec.transaction_id
256 AND ciih.instance_id = p_asset_attrib_rec.instance_id
257 AND cii.instance_id = p_asset_attrib_rec.instance_id
258 AND ct.transaction_id = p_asset_attrib_rec.transaction_id
259 AND (ciih.new_inst_usage_code = 'IN_SERVICE' OR cii.instance_usage_code = 'IN_SERVICE')
260 AND ct.transaction_type_id = 108 -- PROJECT_ITEM_IN_SERVICE
261 AND ct.transaction_status_code = 'INTERFACED_TO_PA'
262 AND ct.source_header_ref_id = nvl(cii.last_pa_project_id, source_header_ref_id)
263 AND ct.source_line_ref_id = nvl(cii.last_pa_task_id, source_line_ref_id)
264 AND pa.project_id = cii.last_pa_project_id;
265 EXCEPTION
266 When no_data_found then
267 -- This may be a receipt into Projects
268 BEGIN
269 DEBUG('inside '|| l_api_name || 'Receipt item into project');
270 SELECT cod.operating_unit
271 INTO l_txn_ou_context
272 FROM rcv_transactions rt,
273 csi_inst_txn_details_v citdv,
274 org_organization_definitions cod
275 WHERE citdv.transaction_id = p_asset_attrib_rec.transaction_id
276 AND citdv.source_transaction_type = 'PO_RECEIPT_INTO_PROJECT'
277 AND rt.transaction_id = citdv.source_dist_ref_id2
278 AND rt.organization_id = cod.organization_id;
279 EXCEPTION
280 When no_data_found then
281 l_txn_ou_context := '' ;
282 END;
283 END;
284 END ;
285
286 DEBUG('inside '|| l_api_name || 'OU context is ' || l_txn_ou_context);
287
288 p_asset_attrib_rec.book_type_code := fnd_profile.VALUE_SPECIFIC(
289 name => 'cse_fa_book_type_code',
290 ORG_ID => l_txn_ou_context
291 );
292 DEBUG('inside '|| l_api_name || 'CSE_FA_BOOK_TYPE_CODE: '
293 || p_asset_attrib_rec.book_type_code);
294
295 IF p_asset_attrib_rec.book_type_code IS NULL
296 THEN
297 RAISE e_error ;
298 END IF ;
299
300 RETURN p_asset_attrib_rec.book_type_code;
301
302 END IF; --hook used
303
304 EXCEPTION
305 WHEN e_error
306 THEN
307 x_return_status := FND_API.G_RET_STS_ERROR ;
308 fnd_message.set_name('CSE','CSE_ASSET_BOOK_ERROR');
309 x_error_msg := fnd_message.get;
310 RETURN NULL ;
311 WHEN OTHERS
312 THEN
313 x_return_status := FND_API.G_RET_STS_ERROR ;
314 fnd_message.set_name('CSE','CSE_UNEXP_SQL_ERROR');
315 fnd_message.set_token('API_NAME',l_api_name);
316 fnd_message.set_token('SQL_ERROR',SQLERRM);
317 x_error_msg := fnd_message.get;
318 RETURN NULL;
319 END book_type;
320
321 --------------------------------------------------------------------------
322 -- Description : Returns DPIS based on either the default logic
323 -- OR the DPIS derived by client extension.
324 --------------------------------------------------------------------------
325 FUNCTION date_place_in_service(
326 p_asset_attrib_rec IN OUT NOCOPY CSE_DATASTRUCTURES_PUB.asset_attrib_rec,
327 x_error_msg OUT NOCOPY VARCHAR2,
328 x_return_status OUT NOCOPY VARCHAR2) RETURN DATE
329 IS
330 l_date_place_in_service DATE;
331 x_date_place_in_service DATE;
332 l_asset_creation_code VARCHAR2(30);
333 l_transaction_date DATE;
334 l_book_type_code VARCHAR2(15) ;
335 x_hook_used PLS_INTEGER;
336 l_txn_class VARCHAR2(30);
337 l_return_status VARCHAR2(1);
338 l_serial_control_code number;
339 l_error_message varchar2(2000);
340
341 CURSOR dpi_cur (p_csi_txn_id IN NUMBER, p_inst_id IN number) IS
342 SELECT msib.asset_creation_code,
343 msib.serial_number_control_code,
344 citdv.source_transaction_date
345 FROM mtl_system_items_b msib,
346 csi_inst_txn_details_v citdv
347 WHERE msib.organization_id = citdv.inv_master_organization_id
348 AND msib.inventory_item_id = citdv.inventory_item_id
349 AND citdv.transaction_id = p_csi_txn_id
350 AND citdv.instance_id = p_inst_id;
351
352
353 CURSOR fiscal_period_cur (l_book_type_code IN VARCHAR) IS
354 SELECT start_date
355 FROM fa_book_controls fbc,
356 fa_calendar_periods fcp
357 WHERE fbc.book_type_code = l_book_type_code
358 AND fcp.calendar_type = fbc.deprn_calendar
359 AND trunc(l_transaction_date) BETWEEN fcp.start_date AND fcp.end_date;
360
361 BEGIN
362
363 x_return_status := fnd_api.g_ret_sts_success ;
364
365 debug('inside cse_asset_util_pkg.date_place_in_service');
366
367 cse_asset_client_ext_stub.get_date_place_in_service(
368 p_asset_attrib_rec,
369 x_date_place_in_service,
370 x_hook_used,
371 x_error_msg);
372
373 l_date_place_in_service := x_date_place_in_service ;
374
375 IF x_hook_used = 1 THEN
376 RETURN l_date_place_in_service ;
377 ELSE
378
379 get_txn_class (p_asset_attrib_rec => p_asset_attrib_rec ,
380 x_transaction_class => l_txn_class,
381 x_return_status => l_return_status ,
382 x_error_msg => l_error_message);
383
384 IF l_txn_class = G_IPV_TXN_CLASS OR l_txn_class = G_MOVE_TXN_CLASS THEN
385 l_date_place_in_service := to_date(null);
386 RETURN l_date_place_in_service ;
387 ELSE
388
389 OPEN dpi_cur(p_asset_attrib_rec.transaction_id, p_asset_attrib_rec.instance_id);
390 FETCH dpi_cur INTO l_asset_creation_code, l_serial_control_code, l_transaction_date;
391 CLOSE dpi_cur ;
392
393 debug(' transaction_date : '||l_transaction_date);
394 debug(' serial_control_code : '||l_serial_control_code);
395
396 IF l_serial_control_code in (2, 5) THEN
397 l_date_place_in_service := l_transaction_date;
398 ELSE
399
400 IF nvl(p_asset_attrib_rec.book_type_code, fnd_api.g_miss_char) = fnd_api.g_miss_char THEN
401 l_book_type_code := cse_asset_util_pkg.book_type(p_asset_attrib_rec,
402 x_error_msg,
403 x_return_status);
404 IF x_return_status <> fnd_api.G_RET_STS_SUCCESS THEN
405 RAISE fnd_api.g_exc_error ;
406 END IF ;
407 ELSE
408 l_book_type_code := p_asset_attrib_rec.book_type_code;
409 END IF;
410
411 OPEN fiscal_period_cur (l_book_type_code);
412 FETCH fiscal_period_cur INTO l_date_place_in_service ;
413 CLOSE fiscal_period_cur ;
414
415 END IF;
416
417 IF l_date_place_in_service IS NULL THEN
418 RAISE fnd_api.g_exc_error ;
419 END IF ;
420
421 debug(' date_placed_in_service : '||l_date_place_in_service);
422 RETURN l_date_place_in_service ;
423
424 END IF ; ---IPV/MOVE
425 END IF ; --Hook Used
426
427 EXCEPTION
428 WHEN fnd_api.g_exc_error THEN
429 x_return_status := FND_API.G_RET_STS_ERROR ;
430 fnd_message.set_name('CSE','CSE_FA_CREATION_ATRIB_ERROR');
431 fnd_message.set_token('ASSET_ATTRIBUTE','DATE_PLACED_IN_SERVICE');
432 fnd_message.set_token('CSI_TRANSACTION_ID',p_asset_attrib_rec.transaction_id);
433 x_error_msg := fnd_message.get;
434 RETURN null ;
435 END date_place_in_service ;
436
437
438 FUNCTION asset_key(
439 p_asset_attrib_rec IN OUT NOCOPY CSE_DATASTRUCTURES_PUB.asset_attrib_rec,
440 x_error_msg OUT NOCOPY VARCHAR2,
441 x_return_status OUT NOCOPY VARCHAR2) RETURN NUMBER
442 IS
443 l_asset_key_ccid NUMBER;
444 l_hook_used PLS_INTEGER;
445 l_api_name VARCHAR2(100) := 'cse_asset_util_pkg.asset_key';
446 BEGIN
447 x_return_status := fnd_api.g_ret_sts_success;
448 cse_asset_client_ext_stub.get_asset_key(p_asset_attrib_rec,
449 l_asset_key_ccid,
450 l_hook_used,
451 x_error_msg);
452 IF l_hook_used = 1 THEN
453 RETURN l_asset_key_ccid;
454 ELSE
455 RETURN null;
456 END IF;
457
458 EXCEPTION
459 WHEN OTHERS THEN
460 x_return_status := FND_API.G_RET_STS_ERROR ;
461 fnd_message.set_name('CSE','CSE_UNEXP_SQL_ERROR');
462 fnd_message.set_token('API_NAME',l_api_name);
463 fnd_message.set_token('SQL_ERROR',SQLERRM);
464 x_error_msg := fnd_message.get;
465 RETURN NULL;
466 END asset_key;
467
468 ---------------------------------------------------------------------------+
469 -- Description : returns the total wip cost of an asssemebly - comp cost
470 --------------------------------------------------------------------------
471 PROCEDURE get_wip_assembly_cost(
472 p_asset_attrib_rec IN OUT NOCOPY CSE_DATASTRUCTURES_PUB.asset_attrib_rec,
473 x_wip_assembly_cost OUT NOCOPY NUMBER,
474 x_return_status OUT NOCOPY VARCHAR2,
475 x_error_msg OUT NOCOPY VARCHAR2)
476 IS
477 l_fa_comp_cost NUMBER ;
478 l_fa_item_cost NUMBER ;
479 l_wip_entity_id NUMBER ;
480 l_wip_job_cost NUMBER ;
481 l_api_name VARCHAR2(100) ;
482
483 CURSOR wip_cost_cur(c_wip_entity_id IN NUMBER) IS
484 SELECT NVL(tl_overhead_in,0)+
485 NVL(tl_resource_in,0)+
486 NVL(tl_outside_processing_in,0)+
487 NVL(pl_overhead_in,0)+
488 NVL(pl_material_in,0)+
489 NVL(pl_material_overhead_in,0)+
490 NVL(pl_resource_in,0)+
491 NVL(pl_outside_processing_in,0)
492 FROM wip_period_balances
493 WHERE wip_entity_id = c_wip_entity_id ;
494
495 CURSOR csi_txn_inst_cur(l_wip_entity_id IN NUMBER) IS
496 SELECT citdv.instance_id,
497 citdv.inventory_item_id,
498 citdv.inv_organization_id,
499 mmt.primary_quantity
500 FROM csi_inst_txn_details_v citdv,
501 csi_i_assets cia,
502 mtl_material_transactions mmt
503 WHERE citdv.source_header_ref_id=l_wip_entity_id
504 AND cia.instance_id=citdv.instance_id
505 AND citdv.inv_material_transaction_id=mmt.transaction_id ;
506
507 BEGIN
508
509 x_return_status := fnd_api.G_RET_STS_SUCCESS ;
510 l_api_name := 'CSE_ASSET_UTIL_PKG.get_wip_assembly_cost';
511
512 debug('Begining of Calculation of Wip cost ');
513
514 OPEN wip_cost_cur(p_asset_attrib_rec.source_header_ref_id);
515 FETCH wip_cost_cur into l_wip_job_cost;
516 CLOSE wip_cost_cur;
517
518 FOR csi_txn_inst_rec in csi_txn_inst_cur(p_asset_attrib_rec.source_header_ref_id)
519 LOOP
520 l_fa_item_cost := get_item_cost (
521 p_inventory_item_id =>csi_txn_inst_rec.inventory_item_id,
522 p_organization_id => csi_txn_inst_rec.inv_organization_id);
523
524 l_fa_comp_cost:=l_fa_comp_cost+l_fa_item_cost*csi_txn_inst_rec.primary_quantity;
525
526 END LOOP;
527
528 x_wip_assembly_cost := NVL(l_wip_job_cost,0) - NVL(l_fa_comp_cost,0);
529
530 EXCEPTION
531 WHEN OTHERS THEN
532 x_return_status := FND_API.G_RET_STS_ERROR ;
533 fnd_message.set_name('CSE','CSE_UNEXP_SQL_ERROR');
534 fnd_message.set_token('API_NAME',l_api_name);
535 fnd_message.set_token('SQL_ERROR',SQLERRM);
536 x_error_msg := fnd_message.get;
537 END get_wip_assembly_cost;
538
539 FUNCTION deprn_expense_ccid(
540 p_asset_attrib_rec IN OUT NOCOPY cse_datastructures_pub.asset_attrib_rec,
541 x_error_msg OUT NOCOPY varchar2,
542 x_return_status OUT NOCOPY varchar2) RETURN number
543 IS
544
545 l_deprn_expense_ccid number;
546 l_book_type_code varchar2(15) ;
547 l_category_id number ;
548
549 l_flex_num number;
550 l_segment_num number;
551 l_temp_ccid number;
552 l_app_short_name varchar2(50);
553 l_num_of_segs number;
554 l_segments fnd_flex_ext.SegmentArray ;
555 l_deprn_expense_acct varchar2(25);
556 l_flex_code varchar2(4) := 'GL#' ;
557 l_hook_used pls_integer;
558 l_category_conc_seg varchar2(80);
559
560 l_api_name varchar2(100) := 'cse_asset_util_pkg.deprn_expense_ccod';
561 l_return_status varchar2(1) := fnd_api.g_ret_sts_success;
562 l_error_message varchar2(2000);
563
564 CURSOR fab_control_cur (c_book_type_code IN VARCHAR2) IS
565 SELECT accounting_flex_structure
566 FROM fa_book_controls
567 WHERE book_type_code = c_book_type_code ;
568
569 CURSOR fifs_acct_cur (l_flex_num IN NUMBER) IS
570 SELECT fifs.segment_num
571 FROM fnd_id_flex_segments fifs,
572 fnd_segment_attribute_values fsav
573 WHERE fifs.application_column_name = fsav.application_column_name
574 AND fifs.id_flex_num = fsav.id_flex_num
575 AND fifs.id_flex_code = fsav.id_flex_code
576 AND fifs.application_id = fsav.application_id
577 AND fsav.application_id = 101 --GL
578 AND fsav.id_flex_code = 'GL#'
579 AND fsav.id_flex_num = l_flex_num
580 AND fsav.segment_attribute_type = 'GL_ACCOUNT'
581 AND fsav.attribute_value = 'Y';
582
583 CURSOR asset_clearing_acct_cur (p_book_type_code IN VARCHAR2, p_category_id IN NUMBER) IS
584 SELECT asset_clearing_account_ccid ,
585 deprn_expense_acct
586 FROM fa_category_books
587 WHERE book_type_code = p_book_type_code
588 AND category_id = p_category_id ;
589
590 CURSOR fnd_application_cur IS
591 SELECT application_short_name
592 FROM fnd_application
593 WHERE application_id = 101 ; --GL
594
595 CURSOR fa_category_kfv_cur (l_category_id IN NUMBER) IS
596 SELECT concatenated_segments
597 FROM fa_categories_b_kfv
598 WHERE category_id = l_category_id ;
599
600 BEGIN
601
602 x_return_status := fnd_api.g_ret_sts_success;
603
604 IF nvl(p_asset_attrib_rec.book_type_code, fnd_api.g_miss_char) <> fnd_api.g_miss_char THEN
605 l_book_type_code := p_asset_attrib_rec.book_type_code;
606 ELSE
607
608 l_book_type_code := cse_asset_util_pkg.book_type(
609 p_asset_attrib_rec => p_asset_attrib_rec,
610 x_error_msg => l_error_message,
611 x_return_status => l_return_status);
612
613 IF l_return_status <> fnd_api.g_ret_sts_success THEN
614 RAISE fnd_api.g_exc_error;
615 END IF ;
616 END IF;
617
618 cse_asset_client_ext_stub.get_deprn_expense_ccid(
619 p_asset_attrib_rec => p_asset_attrib_rec,
620 x_deprn_expense_ccid => l_deprn_expense_ccid,
621 x_hook_used => l_hook_used,
622 x_error_msg => l_error_message);
623
624 IF l_hook_used = 1 THEN
625 RETURN l_deprn_expense_ccid;
626 ELSE
627
628 IF nvl(p_asset_attrib_rec.asset_category_id, fnd_api.g_miss_num) <> fnd_api.g_miss_num THEN
629 l_category_id := p_asset_attrib_rec.asset_category_id;
630 ELSE
631
632 l_category_id := cse_asset_util_pkg.asset_category(
633 p_asset_attrib_rec => p_asset_attrib_rec,
634 x_error_msg => l_error_message,
635 x_return_status => l_return_status);
636
637 IF l_return_status <> fnd_api.g_ret_sts_success THEN
638 RAISE fnd_api.g_exc_error;
639 END IF ;
640 END IF;
641
642 OPEN fab_control_cur(l_book_type_code) ;
643 FETCH fab_control_cur INTO l_flex_num ;
644 CLOSE fab_control_cur ;
645
646 OPEN fifs_acct_cur(l_flex_num);
647 FETCH fifs_acct_cur INTO l_segment_num ;
648 CLOSE fifs_acct_cur ;
649
650 OPEN asset_clearing_acct_cur(l_book_type_code, l_category_id) ;
651 FETCH asset_clearing_acct_cur INTO l_temp_ccid , l_deprn_expense_acct ;
652 CLOSE asset_clearing_acct_cur ;
653
654 IF l_temp_ccid is null THEN
655 fnd_message.set_name('CSE','CSE_ASSET_BOOK_CAT_UNDEFINED');
656 fnd_message.set_token('BOOK_TYPE_CODE',l_book_type_code);
657 OPEN fa_category_kfv_cur (l_category_id) ;
658 FETCH fa_category_kfv_cur into l_category_conc_seg ;
659 CLOSE fa_category_kfv_cur ;
660 fnd_message.set_token('ASSET_CAT',l_category_conc_seg);
661 l_error_message := fnd_message.get;
662 RAISE fnd_api.g_exc_error;
663 END IF ;
664
665 OPEN fnd_application_cur ;
666 FETCH fnd_application_cur INTO l_app_short_name ;
667 CLOSE fnd_application_cur ;
668
669 IF fnd_flex_ext.get_segments(
670 application_short_name => l_app_short_name,
671 key_flex_code => l_flex_code,
672 structure_number => l_flex_num,
673 combination_id => l_temp_ccid,
674 n_segments => l_num_of_segs,
675 segments => l_segments)
676 THEN
677
678 l_segments(l_segment_num) := l_deprn_expense_acct ;
679
680 IF fnd_flex_ext.get_combination_id(
681 application_short_name => l_app_short_name,
682 key_flex_code => l_flex_code,
683 structure_number => l_flex_num,
684 validation_date => sysdate,
685 n_segments => l_num_of_segs,
686 segments => l_segments,
687 combination_id => l_deprn_expense_ccid)
688 THEN
689 IF l_deprn_expense_ccid IS NULL THEN
690 RAISE fnd_api.g_exc_error;
691 END IF ;
692 RETURN l_deprn_expense_ccid ;
693 ELSE
694 null ;
695 END IF;
696 END IF;
697 END IF;
698
699 IF l_deprn_expense_ccid IS NULL THEN
700 RAISE fnd_api.g_exc_error ;
701 END IF;
702
703 EXCEPTION
704 WHEN fnd_api.g_exc_error THEN
705 x_return_status := fnd_api.g_ret_sts_error;
706 IF l_error_message is null THEN
707 fnd_message.set_name('CSE','CSE_ASSET_EXPENSE_ACCT_ERROR');
708 fnd_message.set_token('BOOK_TYPE_CODE',l_book_type_code);
709 l_error_message := fnd_message.get;
710 END IF;
711 x_error_msg := l_error_message;
712 RETURN null ;
713 END deprn_expense_ccid ;
714
715 ---------------------------------------------------------------------------+
716 -- Procedure/Function Name : search_method
717 -- Description : returns LIFO or FIFO search method based on either the
718 -- default logic OR
719 -- the LIFO or FIFO derived by client extension.
720 --------------------------------------------------------------------------
721 FUNCTION search_method(
722 p_asset_attrib_rec IN OUT NOCOPY CSE_DATASTRUCTURES_PUB.asset_attrib_rec,
723 x_error_msg OUT NOCOPY VARCHAR2,
724 x_return_status OUT NOCOPY VARCHAR2) RETURN VARCHAR2
725 IS
726
727 l_search_method VARCHAR2(4);
728 x_search_method VARCHAR2(4);
729 x_hook_used PLS_INTEGER;
730 e_error EXCEPTION;
731 l_api_name VARCHAR2(100) := 'CSE_ASSET_UTIL_PKG.search_method';
732
733
734 BEGIN
735 x_return_status := fnd_api.G_RET_STS_SUCCESS ;
736 cse_asset_client_ext_stub.get_search_method( p_asset_attrib_rec,
737 x_search_method,
738 x_hook_used,
739 x_error_msg);
740 l_search_method := x_search_method ;
741 IF x_hook_used = 1
742 THEN
743 RETURN l_search_method;
744 ELSE
745 IF p_asset_attrib_rec.Source_Transaction_type IN
746 ( 'PO_RECEIPT_INTO_INVENTORY',
747 'PO_RECEIPT_INTO_PROJECT',
748 'MISC_RECEIPT',
749 'ACCT_ISSUE' ,
750 'ACCT_ALIAS_ISSUE',
751 'RETURN_TO_VENDOR' ,
752 'ACCT_RECEIPT',
753 'ACCT_ALIAS_RECEIPT',
754 'ISO_ISSUE',
755 'MISC_ISSUE',
756 'PHYSICAL_INVENTORY',
757 'CYCLE_COUNT',
758 'IPV_ADJUSTMENT_TO_FA',
759 'ASSET_ITEM_MOVE',
760 'SUBINVENOTRY_TRANSFER',
761 'INTERORG_TRANSFER',
762 'ISO_REQUISITION_RECEIPT',
763 'ISO_SHIPMENT',
764 'PROJECT_ITEM_IN_SERVICE',
765 'IPV_ADJUSTMENT_TO_FA')
766 THEN
767
768 l_search_method:=G_FIFO_SEARCH;
769 ELSE
770 l_search_method:=G_LIFO_SEARCH;
771 END IF;
772
773
774 IF l_search_method IS NULL
775 THEN
776 RAISE e_error ;
777 END IF ;
778
779 RETURN l_search_method ;
780 END IF;
781
782 EXCEPTION
783 WHEN e_error
784 THEN
785 x_return_status := FND_API.G_RET_STS_ERROR ;
786 fnd_message.set_name('CSE','CSE_UNEXP_SQL_ERROR');
787 fnd_message.set_token('API_NAME',l_api_name);
788 fnd_message.set_token('SQL_ERROR',SQLERRM);
789 x_error_msg := fnd_message.get;
790 RETURN NULL;
791 WHEN OTHERS
792 THEN
793 x_return_status := FND_API.G_RET_STS_ERROR ;
794 fnd_message.set_name('CSE','CSE_UNEXP_SQL_ERROR');
795 fnd_message.set_token('API_NAME',l_api_name);
796 fnd_message.set_token('SQL_ERROR',SQLERRM);
797 x_error_msg := fnd_message.get;
798 RETURN NULL;
799 END search_method;
800
801
802 ---------------------------------------------------------------------------+
803 -- Procedure/Function Name : Payables CCID
804 -- Description : returns payables CCID based on either the
805 -- default logic OR
806 -- the Payables CCID derived by client extension.
807 --------------------------------------------------------------------------
808
809 FUNCTION payables_ccid(
810 p_asset_attrib_rec IN OUT NOCOPY CSE_DATASTRUCTURES_PUB.asset_attrib_rec,
811 x_error_msg OUT NOCOPY VARCHAR2,
812 x_return_status OUT NOCOPY VARCHAR2) RETURN NUMBER
813 IS
814
815 l_hook_used PLS_INTEGER;
816 l_txn_process_flag VARCHAR2(1);
817 l_asset_acct_ccid NUMBER ;
818 l_src_txn_id NUMBER;
819 l_book_type_code VARCHAR2(15);
820 l_category_id NUMBER ;
821 l_entity_code varchar2(100) := 'MTL_ACCOUNTING_EVENTS';
822 l_application_id number := 707; ---BOM/CST;
823 l_txn_class VARCHAR2(30);
824 l_return_status VARCHAR2(1);
825 l_error_message VARCHAR2(2000);
826
827 l_csi_txn_type_id number;
828 l_mtl_txn_id number;
829 l_po_distribution_id number;
830 l_inventory_asset_flag varchar2(1) := 'Y';
831 l_organization_id number;
832
833 l_acct_line_type number := 1;
834 l_cost_element_id number := 1;
835 l_ledger_id number;
836
837 l_sla_flag boolean := FALSE;
838
839
840 CURSOR payables_ccid_cur (c_transaction_id IN NUMBER,c_instance_id IN NUMBER) IS
841 SELECT pda.code_combination_id
842 FROM po_distributions_all pda,
843 rcv_transactions rt,
844 csi_transactions ct
845 WHERE pda.po_distribution_id = rt.po_distribution_id
846 AND rt.transaction_id = ct.source_dist_ref_id2
847 AND ct.transaction_id = c_transaction_id
848 AND ct.transaction_type_id = 105 -- rec in to project
849 UNION
850 SELECT pda.variance_account_id
851 FROM po_distributions_all pda,
852 ap_invoice_distributions_all aida ,
853 csi_transactions ct
854 WHERE pda.po_distribution_id = aida.po_distribution_id
855 AND aida.invoice_distribution_id = ct.source_dist_ref_id2
856 AND ct.transaction_id = c_transaction_id
857 AND ct.transaction_type_id = 102; -- ap ipv
858
859 CURSOR sla_ccid_cur(
860 p_mtl_txn_id IN number, p_acct_line_type IN number, p_cost_element_id in number, p_ledger_id IN number)
861 IS
862 SELECT xal.code_combination_id
863 FROM mtl_transaction_accounts mta,
864 xla_distribution_links xdl,
865 xla_ae_lines xal,
866 xla_ae_headers xah
867 WHERE mta.transaction_id = p_mtl_txn_id
868 AND mta.accounting_line_type = p_acct_line_type
869 AND nvl(mta.cost_element_id,1) = p_cost_element_id
870 AND xdl.source_distribution_type = 'MTL_TRANSACTION_ACCOUNTS'
871 AND xdl.source_distribution_id_num_1 = mta.inv_sub_ledger_id
872 AND xal.ae_header_id = xdl.ae_header_id
873 AND xal.ae_line_num = xdl.ae_line_num
874 AND xah.ae_header_id = xal.ae_header_id
875 AND xah.ledger_id = p_ledger_id;
876
877 /*
878 SELECT xlael.code_combination_id
879 FROM xla_transaction_entities xlte,
880 xla_ae_headers xlaeh,
881 xla_ae_lines xlael,
882 xla_distribution_links xdl,
883 mtl_transaction_accounts mta
884 WHERE xlte.application_id = l_application_id
885 AND xlte.entity_code = l_entity_code
886 AND xlte.source_id_int_1 = p_mtl_txn_id
887 AND xlaeh.ledger_id = p_ledger_id
888 AND xlaeh.application_id = xlte.application_id
889 AND xlaeh.entity_id = xlte.entity_id
890 AND xlael.application_id = xlte.application_id
891 AND xlael.ae_header_id = xlaeh.ae_header_id
892 AND xlael.accounting_class_code = p_acct_class_code
893 AND xdl.ae_header_id = xlael.ae_header_id
894 AND xdl.ae_line_num = xlael.ae_line_num
895 AND mta.inv_sub_ledger_id = xdl.source_distribution_id_num_1
896 AND mta.cost_element_id = 1;
897 */
898
899 CURSOR src_mv_txn_cur (c_txn_id IN NUMBER) IS
900 SELECT NVL(source_dist_ref_id2,transaction_id)
901 FROM csi_transactions
902 WHERE transaction_id = c_txn_id ;
903
904 CURSOR asset_acct_cur (c_book_type_code VARCHAR2 , c_category_id IN NUMBER) IS
905 SELECT asset_clearing_account_ccid
906 FROM fa_category_books
907 WHERE book_type_code = c_book_type_code
908 AND category_id = c_category_id ;
909
910 BEGIN
911
912 x_return_status := fnd_api.g_ret_sts_success;
913
914 debug('inside cse_asset_util_pkg.payables_ccid');
915
916 cse_asset_client_ext_stub.get_payables_ccid(
917 p_asset_attrib_rec => p_asset_attrib_rec,
918 x_payables_ccid => l_asset_acct_ccid,
919 x_hook_used => l_hook_used,
920 x_error_msg => l_error_message);
921
922 IF l_hook_used = 1 THEN
923 RETURN l_asset_acct_ccid;
924 ELSE
925
926 SELECT transaction_type_id,
927 inv_material_transaction_id,
928 source_dist_ref_id1
929 INTO l_csi_txn_type_id,
930 l_mtl_txn_id,
931 l_po_distribution_id
932 FROM csi_transactions
933 WHERE transaction_id = p_asset_attrib_rec.transaction_id;
934
935 get_txn_class (
936 p_asset_attrib_rec => p_asset_attrib_rec ,
937 x_transaction_class => l_txn_class,
938 x_return_status => l_return_status ,
939 x_error_msg => l_error_message);
940
941 IF l_return_status <> fnd_api.G_RET_STS_SUCCESS THEN
942 RAISE fnd_api.g_exc_error;
943 END IF ;
944
945 debug('txn class : '||l_txn_class);
946
947 IF l_txn_class = G_MOVE_TXN_CLASS THEN
948 OPEN src_mv_txn_cur(p_asset_attrib_rec.transaction_id);
949 FETCH src_mv_txn_cur INTO l_src_txn_id ;
950 CLOSE src_mv_txn_cur;
951
952 l_book_type_code := cse_asset_util_pkg.book_type(p_asset_attrib_rec,
953 l_error_message,
954 l_return_status);
955 IF l_return_status <> fnd_api.G_RET_STS_SUCCESS THEN
956 RAISE fnd_api.g_exc_error;
957 END IF ;
958 l_category_id := cse_asset_util_pkg.asset_category(p_asset_attrib_rec,
959 l_error_message,
960 l_return_status);
961 IF l_return_status <> fnd_api.G_RET_STS_SUCCESS THEN
962 RAISE fnd_api.g_exc_error;
963 END IF ;
964
965 OPEN asset_acct_cur (l_book_type_code,l_category_id);
966 FETCH asset_acct_cur INTO l_asset_acct_ccid ;
967 CLOSE asset_acct_cur ;
968
969 IF l_asset_acct_ccid IS NULL THEN
970 RAISE fnd_api.g_exc_error;
971 END IF ;
972
973 RETURN l_asset_acct_ccid ;
974 ELSE
975
976
977 debug('transaction_type_id : '||l_csi_txn_type_id);
978 debug('mtl_transaction_id : '||l_mtl_txn_id);
979
980 IF l_mtl_txn_id is null THEN
981
982 OPEN payables_ccid_cur(p_asset_attrib_rec.transaction_id,p_asset_attrib_rec.instance_id) ;
983 FETCH payables_ccid_cur INTO l_asset_acct_ccid ;
984 CLOSE payables_ccid_cur ;
985
986 ELSE
987
988 SELECT nvl(msi.inventory_asset_flag, 'N'),
989 mmt.organization_id
990 INTO l_inventory_asset_flag,
991 l_organization_id
992 FROM mtl_material_transactions mmt,
993 mtl_system_items msi
994 WHERE mmt.transaction_id = l_mtl_txn_id
995 AND msi.inventory_item_id = mmt.inventory_item_id
996 AND msi.organization_id = mmt.organization_id;
997
998 IF l_inventory_asset_flag = 'Y' or l_csi_txn_type_id = 112 THEN
999
1000 IF l_csi_txn_type_id = 112 THEN
1001 IF l_inventory_asset_flag = 'N' THEN
1002 l_acct_line_type := 2;
1003 --l_cost_element_id := 0;
1004 END IF;
1005 END IF;
1006
1007 l_ledger_id := primary_ledger_id(l_organization_id);
1008
1009
1010 debug('application_id : '||l_application_id);
1011 debug('entity_code : '||l_entity_code);
1012 debug('mtl_txn_id : '||l_mtl_txn_id);
1013 debug('acct_line_type : '||l_acct_line_type);
1014 debug('ledger_id : '||l_ledger_id);
1015
1016 xla_security_pkg.set_security_context(l_application_id);
1017
1018 OPEN sla_ccid_cur(l_mtl_txn_id, l_acct_line_type, l_cost_element_id, l_ledger_id);
1019 FETCH sla_ccid_cur INTO l_asset_acct_ccid;
1020 CLOSE sla_ccid_cur;
1021
1022 l_sla_flag := TRUE;
1023
1024 ELSE
1025 cse_asset_client_ext_stub.get_inv_depr_acct(
1026 p_mtl_transaction_id => p_asset_attrib_rec.transaction_id,
1027 x_dummy_acct_id => l_asset_acct_ccid,
1028 x_hook_used => l_hook_used,
1029 x_error_msg => l_error_message);
1030
1031 IF l_hook_used <> 1 THEN
1032 SELECT material_account
1033 INTO l_asset_acct_ccid
1034 FROM mtl_parameters mp
1035 WHERE mp.organization_id = l_organization_id;
1036 END IF;
1037
1038 END IF;
1039
1040 END IF;
1041
1042 IF l_asset_acct_ccid IS NULL THEN
1043 RAISE fnd_api.g_exc_error;
1044 END IF ;
1045
1046 RETURN l_asset_acct_ccid ;
1047 END IF ; --Move Txn
1048 END IF; --Hook Used
1049
1050 EXCEPTION
1051 WHEN fnd_api.g_exc_error THEN
1052 x_return_status := FND_API.G_RET_STS_ERROR ;
1053
1054 IF l_sla_flag THEN
1055 fnd_message.set_name('CSE','CSE_SLA_PAY_CCID_NOT_FOUND');
1056 fnd_message.set_token('MTL_TXN_ID', l_mtl_txn_id);
1057 ELSE
1058 fnd_message.set_name('CSE','CSE_FA_CREATION_ATRIB_ERROR');
1059 fnd_message.set_token('ASSET_ATTRIBUTE','PAYABLES_CODE_COMBINATION_ID');
1060 fnd_message.set_token('CSI_TRANSACTION_ID',p_asset_attrib_rec.transaction_id);
1061 END IF;
1062 x_error_msg := fnd_message.get;
1063 RETURN NULL ;
1064 END payables_ccid;
1065
1066 ---------------------------------------------------------------------------+
1067 -- Procedure/Function Name : tag_number
1068 -- Description : returns Tag Number based on either the
1069 -- default logic OR
1070 -- the Tag Number derived by client extension.
1071 --------------------------------------------------------------------------
1072 FUNCTION tag_number(
1073 p_asset_attrib_rec IN OUT NOCOPY CSE_DATASTRUCTURES_PUB.asset_attrib_rec,
1074 x_error_msg OUT NOCOPY VARCHAR2,
1075 x_return_status OUT NOCOPY VARCHAR2) RETURN VARCHAR2
1076 IS
1077 x_tag_number VARCHAR2(15);
1078 l_tag_number VARCHAR2(15);
1079 x_hook_used PLS_INTEGER;
1080 l_api_name VARCHAR2(100) := 'CSE_ASSET_UTIL_PKG.tag_number';
1081 BEGIN
1082 x_return_status := fnd_api.G_RET_STS_SUCCESS ;
1083 cse_asset_client_ext_stub.get_tag_number(p_asset_attrib_rec,
1084 x_tag_number,
1085 x_hook_used,
1086 x_error_msg);
1087 l_tag_number := x_tag_number ;
1088 IF x_hook_used = 1
1089 THEN
1090 RETURN l_tag_number;
1091 ELSE
1092 RETURN NULL;
1093 END IF;
1094
1095 EXCEPTION
1096 WHEN OTHERS
1097 THEN
1098 x_return_status := FND_API.G_RET_STS_ERROR ;
1099 fnd_message.set_name('CSE','CSE_UNEXP_SQL_ERROR');
1100 fnd_message.set_token('API_NAME',l_api_name);
1101 fnd_message.set_token('SQL_ERROR',SQLERRM);
1102 x_error_msg := fnd_message.get;
1103 RETURN NULL;
1104 END tag_number;
1105
1106 ---------------------------------------------------------------------------+
1107 -- Procedure/Function Name : model_number
1108 -- Description : returns Model Number based on either the
1109 -- default logic OR
1110 -- the Model Number derived by client extension.
1111 --------------------------------------------------------------------------
1112 FUNCTION model_number(
1113 p_asset_attrib_rec IN OUT NOCOPY CSE_DATASTRUCTURES_PUB.asset_attrib_rec,
1114 x_error_msg OUT NOCOPY VARCHAR2,
1115 x_return_status OUT NOCOPY VARCHAR2) RETURN VARCHAR2
1116 IS
1117 x_model_number VARCHAR2(40);
1118 l_model_number VARCHAR2(40);
1119 x_hook_used PLS_INTEGER;
1120 l_api_name VARCHAR2(100) := 'CSE_ASSET_UTIL_PKG.model_number';
1121 BEGIN
1122 x_return_status := fnd_api.G_RET_STS_SUCCESS ;
1123 cse_asset_client_ext_stub.get_model_number(p_asset_attrib_rec,
1124 x_model_number,
1125 x_hook_used,
1126 x_error_msg);
1127 l_model_number := x_model_number ;
1128 IF x_hook_used = 1
1129 THEN
1130 RETURN l_model_number;
1131 ELSE
1132 RETURN NULL;
1133 END IF;
1134 EXCEPTION
1135 WHEN OTHERS
1136 THEN
1137 x_return_status := FND_API.G_RET_STS_ERROR ;
1138 fnd_message.set_name('CSE','CSE_UNEXP_SQL_ERROR');
1139 fnd_message.set_token('API_NAME',l_api_name);
1140 fnd_message.set_token('SQL_ERROR',SQLERRM);
1141 x_error_msg := fnd_message.get;
1142 RETURN NULL;
1143 END model_number;
1144
1145 ---------------------------------------------------------------------------+
1146 -- Procedure/Function Name : manufacturer
1147 -- Description : returns Manufacturer Name based on either the
1148 -- default logic OR
1149 -- the Manufacturer Name derived by client extension.
1150 --------------------------------------------------------------------------
1151 FUNCTION manufacturer(
1152 p_asset_attrib_rec IN OUT NOCOPY CSE_DATASTRUCTURES_PUB.asset_attrib_rec,
1153 x_error_msg OUT NOCOPY VARCHAR2,
1154 x_return_status OUT NOCOPY VARCHAR2) RETURN VARCHAR2
1155 IS
1156 x_manufacturer_name VARCHAR2(30);
1157 l_manufacturer_name VARCHAR2(30);
1158 x_hook_used PLS_INTEGER;
1159 l_api_name VARCHAR2(100) := 'CSE_ASSET_UTIL_PKG.manufacturer';
1160 BEGIN
1161 x_return_status := fnd_api.G_RET_STS_SUCCESS ;
1162 cse_asset_client_ext_stub.get_manufacturer(p_asset_attrib_rec,
1163 x_manufacturer_name,
1164 x_hook_used,
1165 x_error_msg);
1166 l_manufacturer_name := x_manufacturer_name ;
1167 IF x_hook_used = 1
1168 THEN
1169 RETURN l_manufacturer_name;
1170 ELSE
1171 RETURN NULL;
1172 END IF;
1173 EXCEPTION
1174 WHEN OTHERS
1175 THEN
1176 x_return_status := FND_API.G_RET_STS_ERROR ;
1177 fnd_message.set_token('API_NAME',l_api_name);
1178 fnd_message.set_token('SQL_ERROR',SQLERRM);
1179 x_error_msg := fnd_message.get;
1180 RETURN NULL;
1181 END manufacturer;
1182
1183 ---------------------------------------------------------------------------+
1184 -- Procedure/Function Name : employee
1185 -- Description : returns Employee ID based on either the
1186 -- default logic OR
1187 -- the Employee Id derived by client extension.
1188 --------------------------------------------------------------------------
1189 FUNCTION employee(
1190 p_asset_attrib_rec IN OUT NOCOPY CSE_DATASTRUCTURES_PUB.asset_attrib_rec,
1191 x_error_msg OUT NOCOPY VARCHAR2,
1192 x_return_status OUT NOCOPY VARCHAR2) RETURN NUMBER
1193 IS
1194 l_api_name VARCHAR2(100) := 'CSE_ASSET_UTIL_PKG.employee';
1195 x_employee_id NUMBER;
1196 l_employee_id NUMBER;
1197 x_hook_used PLS_INTEGER;
1198 BEGIN
1199 x_return_status := fnd_api.G_RET_STS_SUCCESS ;
1200 cse_asset_client_ext_stub.get_employee(p_asset_attrib_rec,
1201 x_employee_id,
1202 x_hook_used,
1203 x_error_msg);
1204 l_employee_id := x_employee_id ;
1205 IF x_hook_used = 1
1206 THEN
1207 RETURN l_employee_id;
1208 ELSE
1209 RETURN NULL;
1210 END IF;
1211 EXCEPTION
1212 WHEN OTHERS
1213 THEN
1214 x_return_status := FND_API.G_RET_STS_ERROR ;
1215 fnd_message.set_token('API_NAME',l_api_name);
1216 fnd_message.set_token('SQL_ERROR',SQLERRM);
1217 x_error_msg := fnd_message.get;
1218 RETURN NULL;
1219 END employee;
1220
1221 FUNCTION inventory_item(
1222 p_asset_attrib_rec IN OUT NOCOPY CSE_DATASTRUCTURES_PUB.asset_attrib_rec)
1223 RETURN NUMBER IS
1224 l_api_name VARCHAR2(100) := 'CSE_ASSET_UTIL_PKG.inventory_item';
1225 l_inventory_item_id NUMBER;
1226 x_hook_used PLS_INTEGER;
1227 x_error_msg VARCHAR2(2000);
1228 BEGIN
1229 cse_asset_client_ext_stub.get_inventory_item(p_asset_attrib_rec, x_hook_used, x_error_msg);
1230 l_inventory_item_id := p_asset_attrib_rec.inventory_item_id ;
1231 IF x_hook_used = 1 THEN
1232 RETURN l_inventory_item_id;
1233 ELSE
1234 l_inventory_item_id:=p_asset_attrib_rec.inventory_item_id;
1235 RETURN l_inventory_item_id;
1236 END IF;
1237 EXCEPTION
1238 WHEN OTHERS THEN
1239 RETURN NULL;
1240 END inventory_item;
1241
1242 PROCEDURE get_pending_retirements
1243 (p_asset_query_rec IN OUT NOCOPY cse_datastructures_pub.asset_query_rec,
1244 p_distribution_tbl IN OUT NOCOPY cse_datastructures_pub.distribution_tbl,
1245 x_return_status OUT NOCOPY VARCHAR2,
1246 x_error_msg OUT NOCOPY VARCHAR2)
1247 IS
1248 l_cost NUMBER;
1249 l_units NUMBER;
1250 l_api_name VARCHAR2(100) := 'CSE_ASSET_UTIL_PKG.get_pending_retirements';
1251
1252 CURSOR pending_rets_cur (c_distribution_id IN NUMBER)
1253 IS
1254 SELECT SUM(DECODE(fr.status,'PENDING', NVL(fr.cost_retired,0)*(-1),
1255 NVL(fr.cost_retired,0))) cost,
1256 SUM(DECODE(fr.status,'PENDING', NVL(fr.units,0)*(-1),
1257 NVL(fr.units,0))) units
1258 FROM fa_retirements fr ,
1259 fa_distribution_history fdh
1260 WHERE fr.status IN ('PENDING','REINSTATE')
1261 AND fr.retirement_id = fdh.retirement_id
1262 AND fdh.distribution_id = c_distribution_id ;
1263
1264 CURSOR ext_ret_cur (c_distribution_id IN NUMBER)
1265 IS
1266 SELECT SUM(NVL(cost_retired,0)*(-1)) cost
1267 ,SUM(NVL(units,0)*(-1)) units
1268 FROM fa_mass_ext_retirements
1269 WHERE review_status = 'POST'
1270 AND book_type_code = p_asset_query_rec.book_type_code
1271 AND asset_id = p_asset_query_rec.asset_id ;
1272 --AND distribution_id = c_distribution_id ;
1273
1274 BEGIN
1275 x_return_status := fnd_api.G_RET_STS_SUCCESS ;
1276 debug('Begin get_pending_retirements');
1277 FOR i IN 1..p_distribution_tbl.COUNT
1278 LOOP
1279 debug('Distribution ID : '||p_distribution_tbl(i).distribution_id) ;
1280 OPEN pending_rets_cur(p_distribution_tbl(i).distribution_id) ;
1281 FETCH pending_rets_cur INTO l_cost, l_units ;
1282 debug('l_units :'||l_units);
1283 debug('l_cost :'||l_cost);
1284
1285 IF NVL(l_units,0) > 0
1286 THEN
1287 debug('There are pending retirements ...');
1288 p_asset_query_rec.pending_ret_mtl_cost :=
1289 NVL(p_asset_query_rec.pending_ret_mtl_cost,0)+l_cost ;
1290 p_distribution_tbl(i).pending_ret_units :=
1291 NVL(p_distribution_tbl(i).pending_ret_units,0)+l_units ;
1292 END IF;
1293 x_return_status := FND_API.G_RET_STS_SUCCESS ;
1294 CLOSE pending_rets_cur ;
1295
1296 OPEN ext_ret_cur (p_distribution_tbl(i).distribution_id) ;
1297 FETCH ext_ret_cur INTO l_cost, l_units ;
1298
1299 IF NVL(l_units,0) > 0
1300 THEN
1301 p_asset_query_rec.pending_ret_mtl_cost :=
1302 NVL(p_asset_query_rec.pending_ret_mtl_cost,0)+l_cost ;
1303 p_distribution_tbl(i).pending_ret_units :=
1304 NVL(p_distribution_tbl(i).pending_ret_units,0)+l_units ;
1305 END IF;
1306 x_return_status := FND_API.G_RET_STS_SUCCESS ;
1307 CLOSE ext_ret_cur ;
1308 END LOOP ;
1309 EXCEPTION
1310 WHEN OTHERS
1311 THEN
1312 x_return_status := FND_API.G_RET_STS_ERROR ;
1313 fnd_message.set_name('CSE','CSE_UNEXP_SQL_ERROR');
1314 fnd_message.set_token('API_NAME',l_api_name);
1315 fnd_message.set_token('SQL_ERROR',SQLERRM);
1316 x_error_msg := fnd_message.get;
1317
1318 END get_pending_retirements;
1319
1320 ---------------------------------------------------------------------------
1321
1322 PROCEDURE get_pending_adjustments
1323 (p_asset_query_rec IN OUT NOCOPY cse_datastructures_pub.asset_query_rec,
1324 x_return_status OUT NOCOPY VARCHAR2,
1325 x_error_msg OUT NOCOPY VARCHAR2)
1326 IS
1327 l_cost NUMBER := 0;
1328 l_units NUMBER := 0;
1329 l_total_units NUMBER := 0;
1330 l_location_units NUMBER := 0;
1331 l_unit_ratio NUMBER := 1;
1332 l_api_name VARCHAR2(100) := 'CSE_ASSET_UTIL_PKG.get_pending_adjustments';
1333 l_mass_addition_id NUMBER;
1334 CURSOR pending_adj_cur
1335 IS
1336 SELECT SUM(NVL(fma.fixed_assets_cost,0)) cost ,
1337 SUM(fma.fixed_assets_units) total_units ,
1338 fma.mass_addition_id
1339 FROM fa_mass_additions fma
1340 ,fa_massadd_distributions fmd
1341 WHERE fmd.mass_addition_id = fma.mass_addition_id
1342 AND fma.posting_status = 'POST'
1343 AND fma.book_type_code = p_asset_query_rec.book_type_code
1344 AND fma.add_to_asset_id = p_asset_query_rec.asset_id
1345 GROUP BY fma.mass_addition_id ;
1346
1347 CURSOR adj_units_cur (c_mass_addition_id IN NUMBER)
1348 IS
1349 SELECT units location_units
1350 FROM fa_massadd_distributions
1351 WHERE NVL(deprn_expense_ccid, -1)=
1352 NVL(p_asset_query_rec.deprn_expense_ccid,NVL(deprn_expense_ccid,-1))
1353 AND NVL(employee_id, -1)=
1354 NVL(p_asset_query_rec.employee_id,NVL(employee_id,-1))
1355 AND location_id = NVL(p_asset_query_rec.location_id,NVL(location_id,-1))
1356 AND mass_addition_id = c_mass_addition_id ;
1357
1358 BEGIN
1359 x_return_status := fnd_api.G_RET_STS_SUCCESS ;
1360
1361 debug('Start of get_pending_adjustment');
1362 FOR pending_adj_rec IN pending_adj_cur
1363 LOOP
1364 debug('In pending ad cur');
1365 IF (pending_adj_rec.cost = 0) OR
1366 (pending_adj_rec.total_units = 0)
1367 THEN
1368 p_asset_query_rec.pending_adj_mtl_cost :=
1369 NVL(p_asset_query_rec.pending_adj_mtl_cost,0)+0 ;
1370 ELSE
1371 l_mass_addition_id := pending_adj_rec.mass_addition_id ;
1372 l_location_units := 0;
1373
1374 -- FOR adj_units_rec IN adj_units_cur(l_mass_addition_id)
1375 -- LOOP
1376 -- debug('In adj_units cur');
1377 -- l_location_units := l_location_units + adj_units_rec.location_units ;
1378 -- END LOOP ;
1379 --
1380 -- l_unit_ratio := l_location_units/pending_adj_rec.total_units ;
1381 -- l_cost := ROUND(pending_adj_rec.cost*l_unit_ratio,2) ;
1382 -- p_asset_query_rec.pending_adj_mtl_cost :=
1383 -- NVL(p_asset_query_rec.pending_adj_mtl_cost,0)+l_cost ;
1384
1385 p_asset_query_rec.pending_adj_mtl_cost :=
1386 NVL(p_asset_query_rec.pending_adj_mtl_cost,0)+
1387 ROUND(pending_adj_rec.cost,2) ;
1388 debug('Pending Adj Cost is :'|| p_asset_query_rec.pending_adj_mtl_cost);
1389 END IF;
1390 END LOOP ;
1391
1392 EXCEPTION
1393 WHEN OTHERS
1394 THEN
1395 x_return_status := FND_API.G_RET_STS_ERROR ;
1396 fnd_message.set_name('CSE','CSE_UNEXP_SQL_ERROR');
1397 fnd_message.set_token('API_NAME',l_api_name);
1398 fnd_message.set_token('SQL_ERROR',SQLERRM);
1399 x_error_msg := fnd_message.get;
1400
1401 END get_pending_adjustments ;
1402
1403 -------------------------------------------------------------------------------
1404
1405 PROCEDURE get_catchup_dep_flag (p_asset_attrib_rec IN OUT NOCOPY CSE_DATASTRUCTURES_PUB.asset_attrib_rec,
1406 p_asset_number IN VARCHAR2,
1407 p_instance_asset_id IN NUMBER,
1408 x_catchup_flag OUT NOCOPY VARCHAR2,
1409 x_return_status OUT NOCOPY VARCHAR2,
1410 x_error_msg OUT NOCOPY VARCHAR2)
1411 IS
1412 x_hook_used NUMBER := 0;
1413 l_catchup_flag VARCHAR2(1);
1414 l_api_name VARCHAR2(100) := 'CSE_ASSET_UTIL_PKG.get_catchup_dep_flag';
1415
1416
1417
1418
1419 /*CURSOR catchup_cur (c_instance_asset_id IN NUMBER)
1420 IS
1421 SELECT DECODE(msib.asset_creation_code,'1','N','Y')
1422 FROM mtl_system_items_b msib
1423 ,csi_item_instances cii
1424 ,csi_i_assets cia
1425 WHERE msib.organization_id = cii.inv_master_organization_id
1426 AND msib.inventory_item_id = cii.inventory_item_id
1427 AND cii.instance_id = cia.instance_id
1428 AND cia.instance_asset_id = c_instance_asset_id;*/
1429
1430
1431 CURSOR catchup_cur (c_instance_asset_id IN NUMBER,c_inv_org_id IN NUMBER,c_inv_item_id IN NUMBER, c_inst_id IN NUMBER)
1432 IS
1433 SELECT DECODE(msib.asset_creation_code,'1','N','Y')
1434 FROM mtl_system_items_b msib
1435 ,csi_i_assets cia
1436 WHERE msib.organization_id = c_inv_org_id
1437 AND msib.inventory_item_id = c_inv_item_id
1438 AND cia.instance_id = c_inst_id
1439 AND cia.instance_asset_id = c_instance_asset_id;
1440
1441 BEGIN
1442 x_return_status := fnd_api.G_RET_STS_SUCCESS ;
1443 cse_asset_client_ext_stub.get_catchup_flag(p_asset_number,
1444 p_instance_asset_id,
1445 l_catchup_flag,
1446 x_hook_used,
1447 x_error_msg);
1448 IF x_hook_used = 1
1449 THEN
1450 ----x_catchup_flag is already set by client ext.
1451 x_catchup_flag := l_catchup_flag;
1452 ELSE
1453 OPEN catchup_cur (p_instance_asset_id,
1454 p_asset_attrib_rec.inv_master_organization_id,
1455 p_asset_attrib_rec.inventory_item_id,
1456 p_asset_attrib_rec.instance_id);
1457 FETCH catchup_cur INTO l_catchup_flag ;
1458 CLOSE catchup_cur ;
1459 x_catchup_flag := l_catchup_flag ;
1460 END IF ; ---Hook Used
1461
1462 END get_catchup_dep_flag ;
1463
1464 --------------------------------------------------------------------------------
1465
1466
1467 PROCEDURE get_txn_class (
1468 p_asset_attrib_rec IN OUT NOCOPY CSE_DATASTRUCTURES_PUB.asset_attrib_rec,
1469 x_transaction_class OUT NOCOPY VARCHAR2,
1470 x_return_status OUT NOCOPY VARCHAR2,
1471 x_error_msg OUT NOCOPY VARCHAR2)
1472 IS
1473 x_hook_used NUMBER ;
1474 l_txn_type VARCHAR2(30);
1475 e_error EXCEPTION;
1476 l_api_name VARCHAR2(100) ;
1477 l_asset_creation_code VARCHAR2(1);
1478 l_redeploy_flag VARCHAR2(1);
1479 l_inventory_item_id NUMBER ;
1480 l_serial_number VARCHAR2(30);
1481 l_transaction_date DATE ;
1482 L_PRIMARY_QTY NUMBER ;
1483
1484
1485 CURSOR item_type_cur(c_inv_org_id IN NUMBER,c_inv_item_id IN NUMBER) IS
1486 SELECT NVL(msib.asset_creation_code,'~')
1487 FROM mtl_system_items_b msib
1488 WHERE msib.organization_id = c_inv_org_id
1489 AND msib.inventory_item_id = c_inv_item_id;
1490
1491
1492 CURSOR item_qty_cur(c_mmt_id IN NUMBER, c_inv_id IN NUMBER) IS
1493 SELECT mmt.primary_quantity
1494 FROM mtl_material_transactions mmt
1495 WHERE mmt.transaction_id = c_mmt_id
1496 AND mmt.inventory_item_id = c_inv_id ;
1497
1498 CURSOR csi_sub_type_cur (c_transaction_id IN NUMBER) IS
1499 SELECT ctst.src_change_owner
1500 FROM csi_t_txn_line_details cttld,
1501 csi_ib_txn_types ctst
1502 WHERE cttld.source_transaction_flag = 'Y'
1503 AND cttld.csi_transaction_id = c_transaction_id
1504 AND cttld.sub_type_id = ctst.sub_type_id;
1505
1506 l_change_owner_flag VARCHAR2(1);
1507
1508 BEGIN
1509 l_api_name := 'CSE_ASSET_UTIL_PKG.check_txn_class';
1510
1511 x_return_status := fnd_api.G_RET_STS_SUCCESS ;
1512
1513 l_txn_type := p_asset_attrib_rec.source_transaction_type ;
1514
1515 OPEN item_type_cur(p_asset_attrib_rec.inv_master_organization_id, p_asset_attrib_rec.inventory_item_id) ;
1516 FETCH item_type_cur INTO l_asset_creation_code;
1517
1518 CLOSE item_type_cur ;
1519
1520 OPEN item_qty_cur(p_asset_attrib_rec.inv_material_transaction_id, p_asset_attrib_rec.inventory_item_id);
1521 FETCH item_qty_cur INTO l_primary_qty; /*BNARAYAN FOR R12*/
1522 CLOSE item_qty_cur;
1523
1524 l_serial_number := p_asset_attrib_rec.serial_number ;
1525 l_redeploy_flag := 'N' ;
1526 IF l_serial_number IS NULL THEN
1527 -- redeployment is supported only for serialized items
1528 l_redeploy_flag := 'N' ;
1529 ELSE
1530 cse_util_pkg.get_redeploy_flag(
1531 p_inventory_item_id => p_asset_attrib_rec.inventory_item_id,
1532 p_serial_number => p_asset_attrib_rec.serial_number,
1533 p_transaction_date => p_asset_attrib_rec.transaction_date,
1534 x_redeploy_flag => l_redeploy_flag,
1535 x_return_status => x_return_status,
1536 x_error_message => x_error_msg);
1537
1538 IF x_return_status <> fnd_api.G_RET_STS_SUCCESS THEN
1539 RAISE e_error ;
1540 END IF ;
1541 END IF ; --l_serial_number IS NULL
1542 ---For Redeployement
1543
1544 IF l_txn_type IN ('PO_RECEIPT_INTO_INVENTORY'
1545 ,'PO_RECEIPT_INTO_PROJECT'
1546 ,'MISC_RECEIPT'
1547 ,'PHYSICAL_INVENTORY'
1548 ,'CYCLE_COUNT'
1549 ,'RMA_RECEIPT'
1550 ,'WIP_ASSEMBLY_COMPLETION'
1551 ,'ACCT_RECEIPT'
1552 ,'ACCT_ALIAS_RECEIPT')
1553 THEN
1554 IF l_asset_creation_code = '1' THEN
1555 IF l_redeploy_flag = 'N' THEN
1556 IF l_primary_qty < 0 THEN
1557 x_transaction_class := G_ADJUST_TXN_CLASS ;
1558 ELSE
1559 x_transaction_class := G_RECEIPT_TXN_CLASS ;
1560 END IF ;
1561 ELSE --l_redelploy='Y'
1562 x_transaction_class := G_MOVE_TXN_CLASS ;
1563 END IF ; --l_redeploy_flag
1564 ELSIF l_redeploy_flag = 'Y' THEN
1565 x_transaction_class := G_MOVE_TXN_CLASS ;
1566 END IF ;
1567 ELSIF l_txn_type IN('ISSUE_TO_HZ_LOC' ,'MISC_ISSUE_HZ_LOC') THEN
1568 IF l_asset_creation_code = '1' OR l_redeploy_flag = 'Y' THEN
1569 x_transaction_class := G_MOVE_TXN_CLASS ;
1570 ELSIF l_asset_creation_code <> '1' THEN
1571 x_transaction_class := G_RECEIPT_TXN_CLASS ;
1572 END IF ;
1573 ELSIF l_txn_type IN ('ITEM_MOVE'
1574 ,'SUBINVENTORY_TRANSFER'
1575 ,'INTERORG_TRANSFER'
1576 ,'INTERORG_TRANS_SHIPMENT'
1577 ,'INTERORG_TRANS_RECEIPT'
1578 ,'ISO_SHIPMENT'
1579 ,'ISO_REQUISITION_RECEIPT'
1580 ,'ISSUE_TO_HZ_LOC'
1581 ,'MISC_ISSUE_HZ_LOC'
1582 ,'RECEIPT_HZ_LOC'
1583 ,'MISC_RECEIPT_HZ_LOC'
1584 ,'WIP_ISSUE'
1585 ,'WIP_RECEIPT'
1586 ,'RMA_RECEIPT'
1587 ,'PROJECT_BORROW'
1588 ,'PROJECT_TRANSFER'
1589 ,'PROJECT_PAYBACK'
1590 ,'SALES_ORDER_PICK'
1591 ,'CYCLE_COUNT_TRANSFER'
1592 ,'INTERORG_DIRECT_SHIP'
1593 ,'ISO_PICK'
1594 ,'PROJECT_ITEM_IN_SERVICE'
1595 ,'PROJECT_ITEM_INSTALLED'
1596 ,'PROJECT_ITEM_UNINSTALLED'
1597 ,'MISC_ISSUE_TO_PROJECT'
1598 ,'OM_SHIPMENT'
1599 ,'MISC_RECEIPT_FROM_PROJECT'
1600 ,'MOVE_ORDER_ISSUE_TO_PROJECT')
1601 THEN
1602 IF l_asset_creation_code = '1' OR l_redeploy_flag = 'Y' THEN
1603 IF l_txn_type = 'OM_SHIPMENT' THEN
1604 l_change_owner_flag := 'Y' ;
1605 OPEN csi_sub_type_cur(p_asset_attrib_rec.transaction_id) ;
1606 FETCH csi_sub_type_cur INTO l_change_owner_flag ;
1607 CLOSE csi_sub_type_cur ;
1608
1609 IF l_change_owner_flag = 'N' THEN
1610 x_transaction_class := G_MOVE_TXN_CLASS ;
1611 ELSE
1612 x_transaction_class := G_ADJUST_TXN_CLASS ;
1613 END IF ;
1614 ELSE --l_txn_type = 'OM_SHIPMENT'
1615 x_transaction_class := G_MOVE_TXN_CLASS ;
1616 END IF ; --l_txn_type = 'OM_SHIPMENT'
1617 END IF ; --l_asset_creation_code = '1' OR l_redeploy_flag = 'Y'
1618 ELSIF l_txn_type IN ( 'MISC_ISSUE'
1619 ,'ACCT_ISSUE'
1620 ,'ACCT_ALIAS_ISSUE'
1621 ,'RETURN_TO_VENDOR'
1622 ,'INT_REQ_RCPT_ADJUSTMENT'
1623 ,'SHIPMENT_RCPT_ADJUSTMENT'
1624 ,'OKE_SHIPMENT'
1625 ,'OM_SHIPMENT'
1626 ,'ISO_ISSUE'
1627 ,'MISC_RECEIPT_HZ_LOC'
1628 ,'RECEIPT_HZ_LOC')
1629 THEN
1630 IF l_txn_type NOT IN ('MISC_RECEIPT_HZ_LOC' ,'RECEIPT_HZ_LOC') THEN
1631 IF (l_asset_creation_code = '1' OR l_redeploy_flag = 'Y') THEN
1632 IF l_serial_number IS NOT NULL
1633 AND
1634 l_txn_type IN ( 'MISC_ISSUE', 'ACCT_ISSUE', 'ACCT_ALIAS_ISSUE')
1635 THEN
1636 x_transaction_class := G_MOVE_TXN_CLASS ;
1637 ELSE
1638 x_transaction_class := G_ADJUST_TXN_CLASS ;
1639 END IF ;
1640 END IF ;
1641 ELSIF l_txn_type IN ('MISC_RECEIPT_HZ_LOC', 'RECEIPT_HZ_LOC')
1642 AND
1643 l_asset_creation_code <> 1 AND l_serial_number IS NULL
1644 THEN
1645 x_transaction_class := G_ADJUST_TXN_CLASS ;
1646 END IF ;
1647
1648 ELSIF l_txn_type IN ('OUT_OF_SERVICE' ,'IN_SERVICE') THEN
1649 x_transaction_class := G_MISC_MOVE_TXN_CLASS ;
1650 ELSIF l_txn_type = 'IPV_ADJUSTMENT_TO_FA' THEN
1651 x_transaction_class := G_IPV_TXN_CLASS ;
1652 ELSE
1653 x_transaction_class := 'NONE' ;
1654 END IF ;
1655 EXCEPTION
1656 WHEN OTHERS THEN
1657 x_return_status := FND_API.G_RET_STS_ERROR ;
1658 fnd_message.set_name('CSE','CSE_UNEXP_SQL_ERROR');
1659 fnd_message.set_token('API_NAME',l_api_name);
1660 fnd_message.set_token('SQL_ERROR',SQLERRM);
1661 x_error_msg := fnd_message.get;
1662 END get_txn_class ;
1663
1664 -------------------------------------------------------------------------------
1665 -- Procedure/Function Name : validate_inst_asset
1666 -- Description : validates if the instance is already associated with the Fixed Asset
1667 -------------------------------------------------------------------------
1668
1669 /* bnarayan added for R12 */
1670 PROCEDURE validate_inst_asset (p_asset_attrib_rec IN OUT NOCOPY CSE_DATASTRUCTURES_PUB.asset_attrib_rec,
1671 X_valid OUT NOCOPY VARCHAR2,
1672 X_return_status OUT NOCOPY VARCHAR2,
1673 x_error_msg OUT NOCOPY VARCHAR2)
1674 IS
1675 l_inv_subinventory_name VARCHAR2(10);
1676 l_inv_organization_id NUMBER ;
1677 l_instance_id NUMBER;
1678 l_api_name VARCHAR2(100) := 'CSE_ASSET_UTIL_PKG.validate_inst_asset' ;
1679 l_valid varchar(1);
1680
1681
1682
1683 CURSOR c_get_asset_subinventory IS
1684 SELECT 'N' from mtl_secondary_inventories msi , csi_item_instances cii
1685 WHERE msi.asset_inventory=1
1686 AND msi.secondary_inventory_name=l_inv_subinventory_name
1687 AND msi.organization_id=cii.inv_organization_id
1688 AND msi.organization_id= l_inv_organization_id
1689 AND cii.instance_id=l_instance_id;
1690
1691 CURSOR c_instance_capitalizes IS
1692 SELECT 'N' from csi_i_assets
1693 WHERE instance_id=l_instance_id
1694 AND (active_end_date >SYSDATE OR active_end_date IS NULL );
1695
1696 CURSOR c_instance_norm is
1697 SELECT 'N' from csi_item_instances
1698 WHERE (pa_project_id IS NOT NULL OR
1699 last_pa_project_id IS NOT NULL)
1700 AND instance_id =l_instance_id
1701 AND (active_end_date >SYSDATE OR active_end_date IS NULL );
1702
1703
1704 BEGIN
1705 l_inv_subinventory_name := p_asset_attrib_rec.subinventory_name ;
1706 l_inv_organization_id := p_asset_attrib_rec.organization_id;
1707 l_instance_id := p_asset_attrib_rec.instance_id;
1708 l_valid :='E';
1709 X_Valid :='Y';
1710 x_return_status := fnd_api.G_RET_STS_SUCCESS ;
1711 OPEN c_get_asset_subinventory ;
1712 FETCH c_get_asset_subinventory INTO l_valid ;
1713 CLOSE c_get_asset_subinventory ;
1714
1715 IF (nvl(l_valid,'Y') = 'N') THEN
1716 x_valid :='N';
1717 ELSE
1718 OPEN c_instance_capitalizes ;
1719 FETCH c_instance_capitalizes INTO l_valid ;
1720 CLOSE c_instance_capitalizes ;
1721 IF (nvl(l_valid,'Y') = 'N') THEN
1722 x_valid :='N';
1723 ELSE
1724 OPEN c_instance_norm ;
1725 FETCH c_instance_norm INTO l_valid ;
1726 CLOSE c_instance_norm ;
1727 IF (nvl(l_valid,'Y') = 'N') THEN
1728 x_valid :='N';
1729 END IF;
1730 END IF;
1731 END IF;
1732 EXCEPTION
1733 WHEN OTHERS
1734 THEN
1735 x_return_status := FND_API.G_RET_STS_ERROR ;
1736 fnd_message.set_name('CSE','CSE_UNEXP_SQL_ERROR');
1737 fnd_message.set_token('API_NAME',l_api_name);
1738 fnd_message.set_token('SQL_ERROR',SQLERRM);
1739 x_error_msg := fnd_message.get;
1740
1741 END validate_inst_asset;
1742
1743
1744 PROCEDURE insert_mass_add(
1745 p_api_version IN NUMBER,
1746 p_commit IN VARCHAR2,
1747 p_init_msg_list IN VARCHAR2,
1748 p_mass_add_rec IN OUT NOCOPY fa_mass_additions%ROWTYPE,
1749 x_return_status OUT NOCOPY VARCHAR2,
1750 x_msg_count OUT NOCOPY NUMBER,
1751 x_msg_data OUT NOCOPY VARCHAR2 )
1752 IS
1753 x_error_msg VARCHAR2(2000);
1754 l_api_name VARCHAR2(100) := 'CSE_ASSET_UTIL_PKG.insert_mass_add' ;
1755
1756 l_fixed_assets_cost NUMBER ;
1757 l_payables_cost NUMBER ;
1758 l_unrevalued_cost NUMBER ;
1759
1760 l_deprn_calendar VARCHAR2(15);
1761 l_dep_date DATE;
1762
1763 l_last_dep_run_date DATE;
1764 l_period_name VARCHAR2(15);
1765
1766 CURSOR c_curr_dep_prd (c_book_type_code IN varchar) IS
1767 Select dp.period_name,
1768 bc.last_deprn_run_date,
1769 bc.deprn_calendar
1770 from fa_deprn_periods dp,
1771 fa_deprn_periods dp2,
1772 fa_deprn_periods dp3,
1773 fa_book_controls bc
1774 where dp.book_type_code =c_book_type_code
1775 and dp.period_close_date is null
1776 and dp2.book_type_code(+) = bc.distribution_source_book
1777 and dp2.period_counter(+) = bc.last_mass_copy_period_counter
1778 and dp3.book_type_code(+) = bc.book_type_code
1779 and dp3.period_counter(+) = bc.last_purge_period_counter
1780 and bc.book_type_code = c_book_type_code;
1781
1782 CURSOR c_dep_date (c_calendar_type in varchar, c_book_type_code IN varchar, c_period_name in varchar) IS
1783 SELECT END_DATE
1784 FROM FA_CALENDAR_PERIODS FAP,
1785 fa_book_controls FAC
1786 WHERE FAP.calendAr_type=c_calendar_type
1787 AND FAC.BOOk_TYPE_CODE =c_book_type_code
1788 AND FAP.PERIOD_NAME=c_period_name;
1789
1790 BEGIN
1791 x_return_status := fnd_api.G_RET_STS_SUCCESS ;
1792
1793 SELECT fa_mass_additions_s.nextval
1794 INTO p_mass_add_rec.mass_addition_id
1795 FROM SYS.DUAL ;
1796
1797 debug('inside api insert_mass_add');
1798
1799 SELECT ROUND(p_mass_add_rec.fixed_assets_cost,2) ,
1800 ROUND(p_mass_add_rec.payables_cost,2),
1801 ROUND(p_mass_add_rec.unrevalued_cost,2)
1802 INTO l_fixed_assets_cost,
1803 l_payables_cost,
1804 l_unrevalued_cost
1805 FROM SYS.dual ;
1806
1807 BEGIN
1808 OPEN c_curr_dep_prd(p_mass_add_rec.book_type_code);
1809 FETCH c_curr_dep_prd INTO l_period_name, l_last_dep_run_date,l_deprn_calendar ;
1810 CLOSE c_curr_dep_prd ;
1811 EXCEPTION
1812 WHEN others then
1813 NULL;
1814 END;
1815
1816 IF (l_period_name is not null) THEN
1817 BEGIN
1818 OPEN c_dep_date(l_deprn_calendar,p_mass_add_rec.book_type_code,l_period_name);
1819 FETCH c_dep_date INTO l_dep_date ;
1820 CLOSE c_dep_date ;
1821 EXCEPTION
1822 WHEN others then
1823 NULL;
1824 END;
1825 END IF;
1826
1827 IF TRUNC(p_mass_add_rec.date_placed_in_service) > TRUNC(l_dep_date) THEN
1828 p_mass_add_rec.transaction_date :=p_mass_add_rec.date_placed_in_service;
1829 p_mass_add_rec.TRANSACTION_TYPE_CODE:='FUTURE ADD';
1830 END IF;
1831
1832 INSERT INTO fa_mass_additions(
1833 mass_addition_id,
1834 asset_number,
1835 tag_number,
1836 description,
1837 asset_category_id,
1838 manufacturer_name,
1839 serial_number,
1840 model_number,
1841 book_type_code,
1842 date_placed_in_service,
1843 fixed_assets_cost,
1844 payables_units,
1845 fixed_assets_units,
1846 payables_code_combination_id,
1847 expense_code_combination_id,
1848 location_id,
1849 assigned_to ,
1850 feeder_system_name,
1851 create_batch_date,
1852 create_batch_id,
1853 last_update_date,
1854 last_updated_by,
1855 reviewer_comments,
1856 invoice_number,
1857 vendor_number,
1858 po_vendor_id,
1859 po_number,
1860 posting_status,
1861 queue_name,
1862 invoice_date,
1863 invoice_created_by,
1864 invoice_updated_by ,
1865 payables_cost,
1866 invoice_id,
1867 payables_batch_name,
1868 depreciate_flag,
1869 parent_mass_addition_id ,
1870 parent_asset_id,
1871 split_merged_code,
1872 ap_distribution_line_number,
1873 post_batch_id,
1874 add_to_asset_id,
1875 amortize_flag,
1876 new_master_flag,
1877 asset_key_ccid,
1878 asset_type,
1879 deprn_reserve,
1880 ytd_deprn,
1881 beginning_nbv,
1882 created_by,
1883 creation_date,
1884 last_update_login,
1885 salvage_value,
1886 accounting_date,
1887 unit_of_measure,
1888 unrevalued_cost,
1889 ytd_reval_deprn_expense,
1890 merged_code,
1891 split_code,
1892 merge_parent_mass_additions_id,
1893 split_parent_mass_additions_id,
1894 project_asset_line_id,
1895 project_id,
1896 task_id,
1897 sum_units,
1898 dist_name,
1899 inventorial,
1900 short_fiscal_year_flag,
1901 conversion_date,
1902 original_deprn_start_date,
1903 group_asset_id,
1904 cua_parent_hierarchy_id,
1905 units_to_adjust,
1906 bonus_ytd_deprn,
1907 bonus_deprn_reserve,
1908 amortize_nbv_flag,
1909 amortization_start_date,
1910 attribute14,
1911 TRANSACTION_DATE,
1912 TRANSACTION_TYPE_CODE,
1913 po_distribution_id)
1914 VALUES(
1915 p_mass_add_rec.mass_addition_id ,
1916 p_mass_add_rec.asset_number,
1917 p_mass_add_rec.tag_number,
1918 p_mass_add_rec.description,
1919 p_mass_add_rec.asset_category_id,
1920 p_mass_add_rec.manufacturer_name,
1921 p_mass_add_rec.serial_number,
1922 p_mass_add_rec.model_number,
1923 p_mass_add_rec.book_type_code,
1924 p_mass_add_rec.date_placed_in_service,
1925 l_fixed_assets_cost,
1926 p_mass_add_rec.payables_units,
1927 p_mass_add_rec.fixed_assets_units,
1928 p_mass_add_rec.payables_code_combination_id,
1929 p_mass_add_rec.expense_code_combination_id,
1930 p_mass_add_rec.location_id,
1931 p_mass_add_rec.assigned_to ,
1932 p_mass_add_rec.feeder_system_name,
1933 p_mass_add_rec.create_batch_date,
1934 p_mass_add_rec.create_batch_id,
1935 p_mass_add_rec.last_update_date,
1936 p_mass_add_rec.last_updated_by,
1937 p_mass_add_rec.reviewer_comments,
1938 p_mass_add_rec.invoice_number,
1939 p_mass_add_rec.vendor_number,
1940 p_mass_add_rec.po_vendor_id,
1941 p_mass_add_rec.po_number,
1942 p_mass_add_rec.posting_status,
1943 p_mass_add_rec.queue_name,
1944 p_mass_add_rec.invoice_date,
1945 p_mass_add_rec.invoice_created_by,
1946 p_mass_add_rec.invoice_updated_by ,
1947 l_payables_cost,
1948 p_mass_add_rec.invoice_id,
1949 p_mass_add_rec.payables_batch_name,
1950 p_mass_add_rec.depreciate_flag,
1951 p_mass_add_rec.parent_mass_addition_id ,
1952 p_mass_add_rec.parent_asset_id,
1953 p_mass_add_rec.split_merged_code,
1954 p_mass_add_rec.ap_distribution_line_number,
1955 p_mass_add_rec.post_batch_id,
1956 p_mass_add_rec.add_to_asset_id,
1957 p_mass_add_rec.amortize_flag,
1958 p_mass_add_rec.new_master_flag,
1959 p_mass_add_rec.asset_key_ccid,
1960 p_mass_add_rec.asset_type,
1961 p_mass_add_rec.deprn_reserve,
1962 p_mass_add_rec.ytd_deprn,
1963 p_mass_add_rec.beginning_nbv,
1964 p_mass_add_rec.created_by,
1965 p_mass_add_rec.creation_date,
1966 p_mass_add_rec.last_update_login,
1967 p_mass_add_rec.salvage_value,
1968 p_mass_add_rec.accounting_date,
1969 p_mass_add_rec.unit_of_measure,
1970 l_unrevalued_cost,
1971 p_mass_add_rec.ytd_reval_deprn_expense,
1972 p_mass_add_rec.merged_code,
1973 p_mass_add_rec.split_code,
1974 p_mass_add_rec.merge_parent_mass_additions_id,
1975 p_mass_add_rec.split_parent_mass_additions_id,
1976 p_mass_add_rec.project_asset_line_id,
1977 p_mass_add_rec.project_id,
1978 p_mass_add_rec.task_id,
1979 p_mass_add_rec.sum_units,
1980 p_mass_add_rec.dist_name,
1981 p_mass_add_rec.inventorial,
1982 p_mass_add_rec.short_fiscal_year_flag,
1983 p_mass_add_rec.conversion_date,
1984 p_mass_add_rec.original_deprn_start_date,
1985 p_mass_add_rec.group_asset_id,
1986 p_mass_add_rec.cua_parent_hierarchy_id,
1987 p_mass_add_rec.units_to_adjust,
1988 p_mass_add_rec.bonus_ytd_deprn,
1989 p_mass_add_rec.bonus_deprn_reserve,
1990 p_mass_add_rec.amortize_nbv_flag,
1991 p_mass_add_rec.amortization_start_date ,
1992 p_mass_add_rec.attribute14,
1993 p_mass_add_rec.TRANSACTION_date,
1994 p_mass_add_rec.TRANSACTION_TYPE_CODE,
1995 p_mass_add_rec.po_distribution_id);
1996
1997 IF p_commit = FND_API.G_TRUE THEN
1998 COMMIT ;
1999 END IF ;
2000 EXCEPTION
2001 WHEN OTHERS THEN
2002 x_error_msg := substr(sqlerrm,1,200) ;
2003 debug('SQL ERRM : '||x_error_msg);
2004 x_return_status := FND_API.G_RET_STS_ERROR ;
2005 x_msg_count := 1;
2006 fnd_message.set_name('CSE','CSE_UNEXP_SQL_ERROR');
2007 fnd_message.set_token('API_NAME',l_api_name);
2008 fnd_message.set_token('SQL_ERROR',SQLERRM);
2009 x_msg_data := fnd_message.get;
2010 END insert_mass_add;
2011
2012
2013 FUNCTION retire_non_mtl(
2014 p_asset_attrib_rec IN OUT NOCOPY CSE_DATASTRUCTURES_PUB.asset_attrib_rec,
2015 p_asset_id IN NUMBER,
2016 x_error_msg OUT NOCOPY VARCHAR2,
2017 x_return_status OUT NOCOPY VARCHAR2
2018 ) RETURN VARCHAR2
2019 IS
2020 x_retire_non_mtl_flag VARCHAR2(1) ;
2021 x_hook_used NUMBER;
2022 BEGIN
2023 x_return_status := fnd_api.G_RET_STS_SUCCESS ;
2024 cse_asset_client_ext_stub.get_non_mtl_retire_flag
2025 ( p_asset_attrib_rec,
2026 p_asset_id,
2027 x_retire_non_mtl_flag,
2028 x_hook_used,
2029 x_error_msg);
2030 IF x_hook_used = 1
2031 THEN
2032 --do nothing as x_process_txn_flag is already set by the client ext.
2033 RETURN x_retire_non_mtl_flag;
2034 ELSE
2035 x_retire_non_mtl_flag := 'N' ;
2036 END IF ; ---x_hook_used
2037 RETURN x_retire_non_mtl_flag;
2038 EXCEPTION
2039 WHEN OTHERS
2040 THEN
2041 x_return_status := fnd_api.G_RET_STS_ERROR ;
2042 END retire_non_mtl ;
2043
2044 -------------------------------------------------------------------------------
2045 --- Validates if current transaction is OK to interface to FA
2046 --- Rules : 1. There should NOT be any previous transaction PENDIG
2047 --- for any of the instances associated with the
2048 --- current transaction.
2049 -------------------------------------------------------------------------------
2050
2051 PROCEDURE is_valid_to_process(
2052 p_asset_attrib_rec IN OUT NOCOPY CSE_DATASTRUCTURES_PUB.asset_attrib_rec,
2053 x_valid_to_process OUT NOCOPY VARCHAR2,
2054 x_return_status OUT NOCOPY VARCHAR2,
2055 x_error_msg OUT NOCOPY VARCHAR2)
2056 IS
2057
2058 CURSOR pending_txns_cur
2059 IS
2060 SELECT ct1.transaction_id,
2061 ct1.transaction_date
2062 FROM csi_item_instances_h ciih1
2063 ,csi_transactions ct1
2064 ,csi_txn_types ctt
2065 WHERE ct1.transaction_id = ciih1.transaction_id
2066 AND ct1.transaction_type_id = ctt.transaction_type_id
2067 AND ct1.transaction_id <> p_asset_attrib_rec.transaction_id
2068 ----AS these transactions cannot be processed without the receipts,
2069 ---these don't qualify for this validation.
2070 AND ctt.source_transaction_type NOT IN ('INTERORG_TRANS_SHIPMENT',
2071 'ISO_SHIPMENT')
2072 AND ciih1.instance_id IN (
2073 SELECT ciih.instance_id
2074 FROM csi_item_instances_h ciih,
2075 csi_transactions ct
2076 WHERE ct.transaction_id = p_asset_attrib_rec.transaction_id
2077 AND ciih.transaction_id = ct.transaction_id)
2078 AND ct1.transaction_status_code = 'PENDING' ;
2079
2080 /*CURSOR csi_txn_date_cur
2081 IS
2082 SELECT ct.transaction_date
2083 FROM csi_transactions ct
2084 WHERE ct.transaction_id = p_transaction_id ;*/
2085
2086 l_transaction_id NUMBER ;
2087 l_current_txn_date DATE ;
2088
2089 BEGIN
2090
2091 x_valid_to_process := 'Y' ;
2092
2093 /*OPEN csi_txn_date_cur ;
2094 FETCH csi_txn_date_cur INTO l_current_txn_date ;
2095 CLOSE csi_txn_date_cur ;*/
2096
2097 FOR pending_txns_rec IN pending_txns_cur
2098 LOOP
2099 IF pending_txns_rec.transaction_date < p_asset_attrib_rec.transaction_date
2100 THEN
2101 x_valid_to_process := 'N' ;
2102 EXIT ;
2103 END IF ;
2104 END LOOP;
2105
2106 debug('Transaction : '|| p_asset_attrib_rec.transaction_id ||' is valid to process ? :'|| x_valid_to_process);
2107
2108 END is_valid_to_process ;
2109
2110 -------------------------------------------------------------------------------
2111 -- Derives Asset location based on
2112 -- 1. Inventory Org and Subinventory OR
2113 -- 2. Location ID and Location Type
2114 -------------------------------------------------------------------------------
2115
2116 PROCEDURE get_fa_location(
2117 p_inst_loc_rec IN cse_asset_util_pkg.inst_loc_rec,
2118 x_asset_location_id OUT NOCOPY NUMBER,
2119 x_return_status OUT NOCOPY VARCHAR2,
2120 x_error_msg OUT NOCOPY VARCHAR2 )
2121 IS
2122
2123 l_sysdate DATE := sysdate;
2124 l_location_type_code VARCHAR2(30) ;
2125 l_location_id NUMBER ;
2126 l_inv_subinventory_name VARCHAR2(10);
2127 l_inv_organization_id NUMBER ;
2128
2129 l_msg_data VARCHAR2(2000);
2130 l_Msg_Count NUMBER;
2131 l_Return_Status VARCHAR2(1);
2132 l_Error_Message VARCHAR2(2000);
2133 l_instance_rec csi_datastructures_pub.instance_header_rec ;
2134 l_party_header_tbl csi_datastructures_pub.party_header_tbl ;
2135 l_account_header_tbl csi_datastructures_pub.party_account_header_tbl ;
2136 l_org_header_tbl csi_datastructures_pub.org_units_header_tbl ;
2137 l_pricing_attrib_tbl csi_datastructures_pub.pricing_attribs_tbl ;
2138 l_ext_attrib_tbl csi_datastructures_pub.extend_attrib_values_tbl ;
2139 l_ext_attrib_def_tbl csi_datastructures_pub.extend_attrib_tbl ;
2140 l_asset_header_tbl csi_datastructures_pub.instance_asset_header_tbl;
2141 l_time_stamp date;
2142
2143 CURSOR fa_location_cur IS
2144 SELECT cal.fa_location_id fa_location_id
2145 FROM csi_a_locations cal
2146 WHERE cal.location_id = l_location_id
2147 AND cal.location_table = 'HR_LOCATIONS'
2148 AND l_location_type_code = 'INVENTORY'
2149 AND NVL(cal.active_start_date,l_sysdate) <= l_sysdate
2150 AND NVL(cal.active_end_date , l_sysdate) >= l_sysdate
2151 UNION
2152 SELECT cal.fa_location_id fa_location_id
2153 FROM csi_a_locations cal
2154 WHERE location_id = l_location_id
2155 AND l_location_type_code IN ('HZ_LOCATIONS', 'IN_TRANSIT')
2156 AND cal.location_table IN ('HZ_LOCATIONS','LOCATION_CODES',
2157 'HR_LOCATIONS')
2158 AND NVL(cal.active_start_date,l_sysdate) <= l_sysdate
2159 AND NVL(cal.active_end_date , l_sysdate) >= l_sysdate
2160 UNION
2161 SELECT cal.fa_location_id fa_location_id
2162 FROM csi_a_locations cal
2163 WHERE location_id = l_location_id
2164 AND l_location_type_code IN ('HR_LOCATIONS','INTERNAL_SITE')
2165 AND cal.location_table IN ('HR_LOCATIONS')
2166 AND NVL(cal.active_start_date,l_sysdate) <= l_sysdate
2167 AND NVL(cal.active_end_date , l_sysdate) >= l_sysdate
2168 UNION
2169 SELECT cal.fa_location_id fa_location_id
2170 FROM csi_a_locations cal,
2171 hz_party_sites hzps
2172 WHERE hzps.location_id = cal.location_id
2173 AND hzps.party_site_id = l_location_id -- Modified for bug 4149685
2174 AND l_location_type_code = 'HZ_PARTY_SITES'
2175 AND cal.location_table IN ('HZ_LOCATIONS','LOCATION_CODES')
2176 AND NVL(cal.active_start_date,l_sysdate) <= l_sysdate
2177 AND NVL(cal.active_end_date , l_sysdate) >= l_sysdate ;
2178
2179 BEGIN
2180
2181 x_return_status := fnd_api.g_ret_sts_success ;
2182 debug('Inside cse_asset_util_pkg.get_fa_location');
2183
2184 x_asset_location_id := NULL ;
2185
2186 debug(' p_rec.transaction_id : '||p_inst_loc_rec.transaction_id);
2187 debug(' p_rec.location_type_code : '||p_inst_loc_rec.location_type_code);
2188 debug(' p_rec.location_id : '||p_inst_loc_rec.location_id);
2189 debug(' p_rec.inv_organization_id : '||p_inst_loc_rec.inv_organization_id);
2190 debug(' p_rec.inv_subinv_name : '||p_inst_loc_rec.inv_subinventory_name);
2191
2192 l_location_id := p_inst_loc_rec.location_id ;
2193 l_location_type_code := p_inst_loc_rec.location_type_code ;
2194 l_inv_subinventory_name := p_inst_loc_rec.inv_subinventory_name ;
2195 l_inv_organization_id := p_inst_loc_rec.inv_organization_id ;
2196
2197 IF l_location_type_code IS NULL OR l_location_id is null THEN
2198
2199 l_instance_rec.instance_id := p_inst_loc_rec.instance_id ;
2200
2201 debug('Calling csi_item_instance_pub.get_item_instance_details - '||l_instance_rec.instance_id);
2202
2203 IF p_inst_loc_rec.transaction_id is not null THEN
2204 SELECT creation_date
2205 INTO l_time_stamp
2206 FROM csi_item_instances_h
2207 WHERE transaction_id = p_inst_loc_rec.transaction_id
2208 AND instance_id = p_inst_loc_rec.instance_id;
2209 ELSE
2210 l_time_stamp := p_inst_loc_rec.transaction_date;
2211 END IF;
2212
2213 debug(' time_stamp : '||to_char(l_time_stamp, 'dd-mon-yyyy hh24:mi:ss'));
2214
2215 csi_item_instance_pub.get_item_instance_details(
2216 p_api_version => 1.0,
2217 p_commit => fnd_api.g_false,
2218 p_init_msg_list => fnd_api.g_true,
2219 p_validation_level => fnd_api.g_valid_level_full,
2220 p_instance_rec => l_instance_rec,
2221 p_get_parties => fnd_api.g_false,
2222 p_party_header_tbl => l_party_header_tbl,
2223 p_get_accounts => fnd_api.g_false,
2224 p_account_header_tbl => l_account_header_tbl,
2225 p_get_org_assignments => fnd_api.g_false,
2226 p_org_header_tbl => l_org_header_tbl,
2227 p_get_pricing_attribs => fnd_api.g_false,
2228 p_pricing_attrib_tbl => l_pricing_attrib_tbl,
2229 p_get_ext_attribs => fnd_api.g_false,
2230 p_ext_attrib_tbl => l_ext_attrib_tbl,
2231 p_ext_attrib_def_tbl => l_ext_attrib_def_tbl,
2232 p_get_asset_assignments => fnd_api.g_false,
2233 p_asset_header_tbl => l_asset_header_tbl,
2234 p_resolve_id_columns => fnd_api.g_false,
2235 p_time_stamp => l_time_stamp,
2236 x_return_status => l_return_status,
2237 x_msg_count => l_msg_count,
2238 x_msg_data => l_msg_data );
2239
2240 IF NOT (l_return_status = fnd_api.g_ret_sts_success) THEN
2241 l_error_message := cse_util_pkg.dump_error_stack;
2242 RAISE fnd_api.g_exc_error;
2243 END IF;
2244
2245 IF l_location_type_code IS NULL THEN
2246 l_location_type_code := l_instance_rec.location_type_code ;
2247 END IF ;
2248
2249 IF l_location_id is NULL THEN
2250 l_location_id := l_instance_rec.location_id ;
2251 END IF ;
2252
2253 IF l_location_type_code = 'INVENTORY' THEN
2254
2255 IF l_inv_organization_id IS NULL THEN
2256 l_inv_organization_id := l_instance_rec.inv_organization_id ;
2257 END IF ;
2258
2259 IF l_inv_subinventory_name IS NULL THEN
2260 l_inv_subinventory_name := l_instance_rec.inv_subinventory_name ;
2261 END IF ;
2262 END IF ; ---INVENTORY
2263
2264 END IF ; ---get the missing parameters
2265
2266 debug(' l_location_id : '||l_location_id);
2267 debug(' l_location_type_code : '||l_location_type_code);
2268
2269 OPEN fa_location_cur ;
2270 FETCH fa_location_cur INTO x_asset_location_id ;
2271 CLOSE fa_location_cur ;
2272
2273 debug(' x_asset_location_id : '||x_asset_location_id);
2274
2275 IF x_asset_location_id IS NULL THEN
2276 RAISE fnd_api.g_exc_error ;
2277 END IF ;
2278
2279 EXCEPTION
2280 WHEN fnd_api.g_exc_error THEN
2281 x_return_status := FND_API.G_RET_STS_ERROR ;
2282 fnd_message.set_name('CSE','CSE_FA_CREATION_ATRIB_ERROR');
2283 fnd_message.set_token('ASSET_ATTRIBUTE','LOCATION');
2284 fnd_message.set_token('CSI_TRANSACTION_ID',p_inst_loc_rec.transaction_id);
2285 x_error_msg := fnd_message.get;
2286 END get_fa_location ;
2287
2288 -------------------------------------------------------------------------------
2289 -- PROCEDURE get_unit_cost ---
2290 -- Derives Unit based on
2291 -- 1. Inventory Org and Item for Inventory txns such as Misc Receipt,
2292 -- 2. Rcv txns and PO info for PO Receipt transactions.
2293 -------------------------------------------------------------------------------
2294
2295 PROCEDURE get_unit_cost(
2296 p_source_txn_type IN VARCHAR2
2297 , p_source_txn_id IN NUMBER
2298 , p_inventory_item_id IN NUMBER
2299 , p_organization_id IN NUMBER
2300 , x_unit_cost OUT NOCOPY NUMBER
2301 , x_error_msg OUT NOCOPY VARCHAR2
2302 , x_return_status OUT NOCOPY VARCHAR2)
2303 IS
2304 l_inventory_item_id NUMBER ;
2305 l_organization_id NUMBER ;
2306 l_primary_qty NUMBER ;
2307 l_po_unit_price NUMBER ;
2308 l_pla_uom_code VARCHAR2(3);
2309 l_primary_uom_code VARCHAR2(3);
2310
2311 CURSOR rcv_txn_cur
2312 IS
2313 SELECT pla.unit_price ---Unit Price for ONE UOM
2314 ,pla_muom.uom_code pla_uom_code
2315 ,rcv_muom.uom_code primary_uom_code
2316 FROM rcv_transactions rt
2317 ,po_lines_all pla
2318 ,mtl_units_of_measure pla_muom
2319 ,mtl_units_of_measure rcv_muom
2320 WHERE rt.transaction_id = p_source_txn_id
2321 AND rt.po_line_id = pla.po_line_id
2322 AND pla.unit_meas_lookup_code = pla_muom.unit_of_measure
2323 AND rt.primary_unit_of_measure = rcv_muom.unit_of_measure ;
2324
2325 BEGIN
2326 l_inventory_item_id := p_inventory_item_id ;
2327 l_organization_id := p_organization_id ;
2328
2329 IF p_source_txn_type = 'INV'
2330 THEN
2331 x_unit_cost := get_item_cost (
2332 p_inventory_item_id => l_inventory_item_id
2333 , p_organization_id => l_organization_id );
2334 debug( ' Unit Price in Primary UOM is :'|| x_unit_cost);
2335 END IF ; ---INV
2336
2337 IF p_source_txn_type = 'PO'
2338 THEN
2339 OPEN rcv_txn_cur ;
2340 FETCH rcv_txn_cur INTO l_po_unit_price, l_pla_uom_code,
2341 l_primary_uom_code ;
2342 CLOSE rcv_txn_cur ;
2343
2344 debug('PO Unit Price is :'|| l_po_unit_price ||
2345 ' In PO Lines UOM :'||l_pla_uom_code);
2346
2347 l_primary_qty :=
2348 inv_convert.inv_um_convert(
2349 item_id => l_inventory_item_id ,
2350 precision => 6,
2351 from_quantity => 1,
2352 from_unit => l_pla_uom_code ,
2353 to_unit => l_primary_uom_code,
2354 from_name => null,
2355 to_name => null);
2356 debug('ONE :'||l_pla_uom_code ||' is '||
2357 l_primary_qty || ' in '||l_primary_uom_code);
2358
2359 x_unit_cost := l_po_unit_price/l_primary_qty ;
2360 debug( ' Unit Price in Primary UOM:'||'('||l_primary_uom_code||') is : '|| x_unit_cost);
2361
2362 END IF ;---PO
2363
2364 -- Added error message for bug 4869653
2365 IF x_unit_cost IS NULL THEN
2366 debug( 'Unable to derive Cost for item : '||p_inventory_item_id ||' Org : '|| p_organization_id||' Source : '||p_source_txn_type||' ID '||p_source_txn_id);
2367 x_return_status := FND_API.G_RET_STS_ERROR ;
2368 fnd_message.set_name('CSE','CSE_UNABLE_DERIVE_COST_ERROR');
2369 fnd_message.set_token('SOURCE_TYPE_CODE',p_source_txn_type);
2370 fnd_message.set_token('SOURCE_TYPE_ID',p_source_txn_id);
2371 x_error_msg := fnd_message.get;
2372 END IF;
2373 -- End error message for bug 4869653
2374 END get_unit_cost ;
2375
2376 -------------------------------------------------------------------------------
2377 ---- is_valid_to_retire
2378 ---- Reference : FA_MASS_RET_PKG.check_addition_retirement
2379 ---- It checks if it is OK to retire the asset.
2380 ---- Fixed asset does not allow to retire the Assets , IF
2381 ---- 1.If you try to retire the asset in the same period,
2382 ---- in which it was created
2383 -------------------------------------------------------------------------------
2384 PROCEDURE is_valid_to_retire (p_asset_id IN NUMBER
2385 ,p_book_type_code IN VARCHAR2
2386 ,x_valid_to_retire_flag OUT NOCOPY VARCHAR2
2387 ,x_error_msg OUT NOCOPY VARCHAR2
2388 ,x_return_status OUT NOCOPY VARCHAR2)
2389 IS
2390 l_api_name VARCHAR2(100) := 'CSE_ASSET_UTIL_PKG.is_valid_to_retire';
2391
2392 CURSOR check_current_period_add
2393 IS
2394 SELECT 'N'
2395 FROM fa_transaction_headers th,
2396 fa_book_controls bc,
2397 fa_deprn_periods dp
2398 WHERE th.asset_id = p_asset_id
2399 AND th.book_type_code = p_book_type_code
2400 AND bc.book_type_code = th.book_type_code
2401 AND th.transaction_type_code||''
2402 = DECODE(bc.book_class,'CORPORATE','TRANSFER IN', 'ADDITION')
2403 AND th.date_effective BETWEEN dp.period_open_date
2404 AND nvl(dp.period_close_date,sysdate)
2405 AND dp.book_type_code = th.book_type_code
2406 AND dp.period_close_date is NULL ;
2407
2408 BEGIN
2409 x_valid_to_retire_flag := 'Y' ;
2410
2411 x_return_status := FND_API.G_RET_STS_SUCCESS ;
2412
2413 OPEN check_current_period_add ;
2414 FETCH check_current_period_add INTO x_valid_to_retire_flag ;
2415 CLOSE check_current_period_add ;
2416
2417 EXCEPTION
2418 WHEN OTHERS
2419 THEN
2420 x_return_status := FND_API.G_RET_STS_ERROR ;
2421 fnd_message.set_name('CSE','CSE_UNEXP_SQL_ERROR');
2422 fnd_message.set_token('API_NAME',l_api_name);
2423 fnd_message.set_token('SQL_ERROR',SQLERRM);
2424 x_error_msg := fnd_message.get;
2425 END is_valid_to_retire ;
2426
2427
2428 -------------------------------------------------------------------------------
2429 ---- Following process will identify the transaction action as
2430 ---- "Sale" or "Move" or "Rect" for Sales Order Transactions/RMA .
2431 -------------------------------------------------------------------------------
2432 PROCEDURE get_so_txn_action ( p_inst_txn_rec IN cse_asset_util_pkg.inst_txn_rec
2433 ,x_fa_action OUT NOCOPY VARCHAR2
2434 ,x_error_msg OUT NOCOPY VARCHAR2
2435 ,x_return_status OUT NOCOPY VARCHAR2
2436 )
2437 IS
2438 BEGIN
2439 NULL ;
2440 ---get the txn details such as OWNERSHIP chnage for the given instance/txn.
2441 ---Sales Transaction:
2442 --- If OWNERSHIP is Enterprise then it's "Move" transaction.
2443 --- If its ownership is Other than Enterprise then it's a "Sale" transaction.
2444 ---RMA Transaction
2445 --- If the previous ownsership is Enterprise and New ownership is also Enterprise then it's "M" (Asset Move)
2446 --- If earlier ownership is customer then treat the ownership as "Enterprise" it's "R" (Create Asset)
2447 END get_so_txn_action ;
2448
2449 -------------------------------------------------------------------------------
2450
2451 FUNCTION get_rcv_sub_ledger_id(p_rcv_transaction_id IN number) RETURN number
2452 IS
2453 l_entity_code varchar2(30) := 'RCV_ACCOUNTING_EVENTS';
2454 l_application_id number := 707;
2455 l_entity_id number;
2456 l_ae_header_id number;
2457 l_charge_account_id number;
2458 l_sub_ledger_id number := null;
2459 BEGIN
2460 -- put logic for the accrual at period end where we have to figure out the invoice's account id
2461 SELECT rcv_sub_ledger_id
2462 INTO l_sub_ledger_id
2463 from rcv_receiving_sub_ledger
2464 WHERE rcv_transaction_id = p_rcv_transaction_id
2465 AND accounting_line_type = 'Charge';
2466
2467 return(l_sub_ledger_id);
2468 EXCEPTION
2469 WHEN no_data_found THEN
2470 return(l_sub_ledger_id);
2471 END get_rcv_sub_ledger_id;
2472
2473
2474 FUNCTION get_fa_period_name (
2475 p_book_type_code IN varchar2,
2476 p_dpis IN date)
2477 RETURN varchar2 IS
2478 l_period_name varchar2(15);
2479 BEGIN
2480
2481 SELECT fcp.period_name
2482 INTO l_period_name
2483 FROM fa_book_controls fbc,
2484 fa_calendar_periods fcp
2485 WHERE fbc.book_type_code = p_book_type_code
2486 AND fcp.calendar_type = fbc.deprn_calendar
2487 AND p_dpis BETWEEN fcp.start_date AND fcp.end_date;
2488
2489 RETURN l_period_name;
2490 EXCEPTION
2491 WHEN no_data_found THEN
2492 RETURN l_period_name;
2493 WHEN too_many_rows THEN
2494 RETURN l_period_name;
2495 END get_fa_period_name;
2496
2497 FUNCTION get_ap_sla_acct_id(
2498 p_invoice_id IN number,
2499 p_invoice_dist_type IN varchar2)
2500 RETURN number
2501 IS
2502 l_entity_code varchar2(30) := 'AP_INVOICES';
2503 l_application_id number := 200;
2504 l_acct_class_code varchar2(30) := 'ACCRUAL';
2505 l_ledger_id number;
2506 l_ccid number := null;
2507
2508 CURSOR sla_ccid_cur IS
2509 SELECT xlael.code_combination_id
2510 FROM xla_transaction_entities xlte,
2511 xla_ae_headers xlaeh,
2512 xla_ae_lines xlael
2513 WHERE xlte.application_id = l_application_id
2514 AND xlte.entity_code = l_entity_code
2515 AND xlte.source_id_int_1 = p_invoice_id
2516 AND xlaeh.ledger_id = l_ledger_id
2517 AND xlaeh.application_id = xlte.application_id
2518 AND xlaeh.entity_id = xlte.entity_id
2519 and xlael.application_id = xlte.application_id
2520 AND xlael.ae_header_id = xlaeh.ae_header_id
2521 AND xlael.accounting_class_code = l_acct_class_code;
2522
2523 BEGIN
2524
2525 xla_security_pkg.set_security_context(l_application_id);
2526
2527 SELECT set_of_books_id
2528 INTO l_ledger_id
2529 FROM ap_system_parameters;
2530
2531 IF p_invoice_dist_type = 'IPV' THEN
2532 l_acct_class_code := 'IPV';
2533 END IF;
2534
2535 OPEN sla_ccid_cur;
2536 FETCH sla_ccid_cur INTO l_ccid;
2537 CLOSE sla_ccid_cur;
2538
2539 RETURN l_ccid;
2540
2541 END get_ap_sla_acct_id;
2542
2543 PROCEDURE validate_ccid_required (x_asset_key_required out nocopy varchar2) IS
2544
2545 l_asset_key_flex_struct number;
2546 l_flexfield fnd_flex_key_api.flexfield_type;
2547 l_structure fnd_flex_key_api.structure_type;
2548 l_num_segments number;
2549 l_segments fnd_flex_key_api.segment_list;
2550 l_segment fnd_flex_key_api.segment_type;
2551
2552 l_asset_key_required varchar2(1) := 'N';
2553
2554 BEGIN
2555
2556 SELECT asset_key_flex_structure
2557 INTO l_asset_key_flex_struct
2558 FROM fa_system_controls;
2559
2560 fnd_flex_key_api.set_session_mode('seed_data');
2561
2562 l_flexfield := fnd_flex_key_api.find_flexfield(
2563 appl_short_name => 'OFA',
2564 flex_code => 'KEY#');
2565
2566 l_structure := fnd_flex_key_api.find_structure(
2567 flexfield => l_flexfield,
2568 structure_number => l_asset_key_flex_struct);
2569
2570 fnd_flex_key_api.get_segments(
2571 flexfield => l_flexfield,
2572 structure => l_structure,
2573 enabled_only => TRUE,
2574 nsegments => l_num_segments,
2575 segments => l_segments);
2576
2577 l_asset_key_required := 'N';
2578
2579 IF l_num_segments > 0 THEN
2580 FOR l_ind IN 1 .. l_num_segments
2581 LOOP
2582 l_segment := fnd_flex_key_api.find_segment(l_flexfield,l_structure,l_segments(l_ind));
2583 IF l_segment.required_flag = 'Y' AND l_segment.enabled_flag = 'Y' THEN
2584 l_asset_key_required := 'Y';
2585 exit;
2586 END IF;
2587 END LOOP;
2588
2589 END IF;
2590
2591 x_asset_key_required := l_asset_key_required;
2592
2593 END validate_ccid_required;
2594
2595
2596 END cse_asset_util_pkg;