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