[Home] [Help]
PACKAGE BODY: APPS.CST_EAMJOB_ACTESTIMATE
Source
1 PACKAGE BODY CST_EAMJOB_ACTESTIMATE AS
2 /* $Header: CSTPJACB.pls 115.3 2003/11/25 02:30:10 lsoo ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'CST_EAMJOB_ACTESTIMATE';
5
6 /* ============================================================== */
7 -- FUNCTION
8 -- Get_eamCostElement()
9 --
10 -- DESCRIPTION
11 -- Function to return the correct eAM cost element, based on
12 -- the transaction mode and the resource id of a transaction.
13 --
14 -- PARAMETERS
15 -- p_txn_mode (1=material, 2=resource)
16 -- p_org_id
17 -- p_resource_id (optional; to be passed only for a resource tranx)
18 --
19 /* ================================================================= */
20
21 FUNCTION Get_eamCostElement(
22 p_txn_mode IN NUMBER,
23 p_org_id IN NUMBER,
24 p_resource_id IN NUMBER := NULL)
25 RETURN number IS
26
27 l_eam_cost_element NUMBER;
28 l_resource_type NUMBER;
29 l_stmt_num NUMBER;
30 l_debug VARCHAR2(80);
31
32 BEGIN
33 -------------------------------------------------------------------
34 -- Determine eAM cost element.
35 -- 1 (equipment) ==> resource type 1 'machine'
36 -- 2 (labor) ==> resource type 2 'person'
37 -- 3 (material) ==> inventory or direct item
38 -- For other resource types, use the default eAM cost element
39 -- from eAM parameters
40 --------------------------------------------------------------------
41
42 l_debug := fnd_profile.value('MRP_DEBUG');
43
44 if (l_debug = 'Y') THEN
45 fnd_file.put_line(fnd_file.log, 'In Get_eamCostElement');
46 end if;
47
48
49 IF p_txn_mode = 1 THEN -- material
50 l_eam_cost_element := 3;
51 ELSE -- resource
52 IF p_resource_id IS NOT NULL THEN
53 l_stmt_num := 200;
54 SELECT resource_type
55 INTO l_resource_type
56 FROM bom_resources
57 WHERE organization_id = p_org_id
58 AND resource_id = p_resource_id;
59 END IF; -- end checking resource id
60
61 IF l_resource_type in (1,2) THEN
62 l_eam_cost_element := l_resource_type;
63 ELSE
64 l_stmt_num := 210;
65 SELECT def_eam_cost_element_id
66 into l_eam_cost_element
67 FROM wip_eam_parameters
68 WHERE organization_id = p_org_id;
69 END IF; -- end checking resource type
70 END IF; -- end checking txn mode
71
72 if (l_debug = 'Y') THEN
73 fnd_file.put_line(fnd_file.log, 'l_eam_cost_element: '|| to_char(l_eam_cost_element));
74 fnd_file.put_line(fnd_file.log, 'resource id: '|| to_char(p_resource_id));
75 end if;
76
77 RETURN l_eam_cost_element;
78
79 EXCEPTION
80 WHEN OTHERS THEN
81 FND_FILE.PUT_LINE(FND_FILE.LOG,'Get_eamCostElement - statement '
82 || l_stmt_num || ': '
83 || substr(SQLERRM,1,200));
84
85 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)
86 THEN
87 FND_MSG_PUB.add_exc_msg
88 ( 'CST_EAMJOB_ACTESTIMATE'
89 , '.Get_eamCostElement : Statement -'||to_char(l_stmt_num)
90 );
91 END IF;
92
93 RETURN 0;
94 END Get_eamCostElement;
95
96
97 ---------------------------------------------------------------------------
98 -- PROCEDURE --
99 -- Get_DeptCostCatg --
100 -- --
101 -- --
102 -- DESCRIPTION --
103 -- This API returns the cost category of the department --
104 -- --
105 -- PURPOSE: --
106 -- Oracle Applications Rel 11i.9 --
107 -- --
108 -- --
109 -- HISTORY: --
110 -- 08/7/02 Hemant Gosain Created --
111 ----------------------------------------------------------------------------
112 PROCEDURE Get_DeptCostCatg (
113 p_api_version IN NUMBER,
114 p_init_msg_list IN VARCHAR2
115 := FND_API.G_FALSE,
116 p_commit IN VARCHAR2
117 := FND_API.G_FALSE,
118 p_validation_level IN NUMBER
119 := FND_API.G_VALID_LEVEL_FULL, p_debug IN VARCHAR2 := 'N',
120
121 p_department_id IN NUMBER := NULL,
122 p_organization_id IN NUMBER := NULL,
123
124 p_user_id IN NUMBER,
125 p_request_id IN NUMBER,
126 p_prog_id IN NUMBER,
127 p_prog_app_id IN NUMBER,
128 p_login_id IN NUMBER,
129
130 x_dept_cost_catg OUT NOCOPY NUMBER,
131 x_return_status OUT NOCOPY VARCHAR2,
132 x_msg_count OUT NOCOPY NUMBER,
133 x_msg_data OUT NOCOPY VARCHAR2 ) IS
134
135 l_api_name CONSTANT VARCHAR2(30) := 'Get_DeptCostCatg';
136 l_api_version CONSTANT NUMBER := 1.0;
137 l_api_message VARCHAR2(10000);
138 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
139
140 l_organization_id NUMBER;
141
142 l_msg_count NUMBER := 0;
143 l_msg_data VARCHAR2(8000) := '';
144 l_dept_cost_catg NUMBER := NULL;
145 l_stmt_num NUMBER;
146
147 BEGIN
148
149 -------------------------------------------------------------------------
150 -- standard start of API savepoint
151 -------------------------------------------------------------------------
152 SAVEPOINT Get_DeptCostCatg;
153
154 l_stmt_num := 5;
155
156 -------------------------------------------------------------------------
157 -- standard call to check for call compatibility
158 -------------------------------------------------------------------------
159 IF NOT fnd_api.compatible_api_call (
160 l_api_version,
161 p_api_version,
162 l_api_name,
163 G_PKG_NAME ) then
164
165 RAISE fnd_api.g_exc_unexpected_error;
166
167 END IF;
168 -------------------------------------------------------------------------
169 -- Initialize message list if p_init_msg_list is set to TRUE
170 -------------------------------------------------------------------------
171
172 l_stmt_num := 10;
173
174 IF FND_API.to_Boolean(p_init_msg_list) THEN
175 FND_MSG_PUB.initialize;
176 END IF;
177
178
179 -------------------------------------------------------------------------
180 -- initialize api return status to success
181 -------------------------------------------------------------------------
182 x_return_status := fnd_api.g_ret_sts_success;
183
184 -- assign to local variables
185
186 l_stmt_num := 15;
187 l_dept_cost_catg := NULL;
188
189
190
191 IF p_department_id IS NOT NULL THEN
192
193 l_stmt_num := 20;
194
195 SELECT maint_cost_category,
196 organization_id
197 INTO l_dept_cost_catg,
198 l_organization_id
199 FROM bom_departments
200 WHERE department_id = p_department_id;
201
202 ELSE
203 l_stmt_num := 25;
204
205 l_organization_id := p_organization_id;
206
207 END IF;
208
209 IF l_dept_cost_catg IS NULL THEN
210 l_stmt_num := 30;
211
212 SELECT def_maint_cost_category
213 INTO l_dept_cost_catg
214 FROM wip_eam_parameters
215 WHERE organization_id = l_organization_id;
216
217 END IF;
218
219 l_stmt_num := 35;
220
221 IF l_dept_cost_catg IS NOT NULL THEN
222
223 l_stmt_num := 40;
224
225 x_dept_cost_catg := l_dept_cost_catg;
226
227 ELSE
228
229 l_stmt_num := 45;
230
231 l_api_message := 'Could not obtain Cost Category for Dept: '
232 ||TO_CHAR(p_department_id);
233 FND_MSG_PUB.ADD_EXC_MSG('CST_EAMJOB_ACTESTIMATE', 'Get_DeptCostCatg('
234 ||TO_CHAR(l_stmt_num)
235 ||'): ', l_api_message);
236 RAISE FND_API.g_exc_error;
237
238 END IF;
239
240 l_stmt_num := 50;
241
242 ---------------------------------------------------------------------------
243 -- Standard check of p_commit
244 ---------------------------------------------------------------------------
245
246 IF FND_API.to_Boolean(p_commit) THEN
247 COMMIT WORK;
248 END IF;
249
250 ---------------------------------------------------------------------------
251 -- Standard Call to get message count and if count = 1, get message info
252 ---------------------------------------------------------------------------
253
254 FND_MSG_PUB.Count_And_Get (
255 p_count => x_msg_count,
256 p_data => x_msg_data );
257
258
259 EXCEPTION
260
261 WHEN fnd_api.g_exc_error THEN
262 x_return_status := fnd_api.g_ret_sts_error;
263
264 -- Get message count and data
265 fnd_msg_pub.count_and_get
266 ( p_count => x_msg_count
267 , p_data => x_msg_data
268 );
269 --
270 WHEN fnd_api.g_exc_unexpected_error THEN
271 x_return_status := fnd_api.g_ret_sts_unexp_error ;
272
273 -- Get message count and data
274 fnd_msg_pub.count_and_get
275 ( p_count => x_msg_count
276 , p_data => x_msg_data
277 );
278 --
279 WHEN OTHERS THEN
280 x_return_status := fnd_api.g_ret_sts_unexp_error ;
281 --
282 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)
283 THEN
284 fnd_msg_pub.add_exc_msg
285 ( 'CST_EAMJOB_ACTESTIMATE'
286 , 'Get_DeptCostCatg : l_stmt_num - '||to_char(l_stmt_num)
287 );
288
289 END IF;
290 -- Get message count and data
291 fnd_msg_pub.count_and_get
292 ( p_count => x_msg_count
293 , p_data => x_msg_data
294 );
295
296 END Get_DeptCostCatg;
297
298 --------------------------------------------------------------------------n
299 -- PROCEDURE --
300 -- Compute_Activity_Estimate --
301 -- --
302 -- --
303 -- DESCRIPTION --
304 -- This API Computes the estimate for an asset activity --
305 -- --
306 -- PURPOSE: --
307 -- Oracle Applications Rel 11i.9 --
308 -- --
309 -- --
310
311 -- --
312 -- --
313 -- HISTORY: --
314 -- 08/07/02 Hemant G Created --
315 ----------------------------------------------------------------------------
316 PROCEDURE Compute_Activity_Estimate (
317 p_api_version IN NUMBER,
318 p_init_msg_list IN VARCHAR2
319 := FND_API.G_FALSE,
320 p_commit IN VARCHAR2
321 := FND_API.G_FALSE,
322 p_validation_level IN NUMBER
323 := FND_API.G_VALID_LEVEL_FULL,
324 p_debug IN VARCHAR2 := 'N',
325
326 p_activity_item_id IN NUMBER,
327 p_organization_id IN NUMBER,
328 p_alt_bom_designator IN VARCHAR2 := NULL,
329 p_alt_rtg_designator IN VARCHAR2 := NULL,
330 p_cost_group_id IN NUMBER := NULL,
331 p_effective_datetime IN VARCHAR2 :=
332 fnd_date.date_to_canonical(SYSDATE),
333
334 p_user_id IN NUMBER,
335 p_request_id IN NUMBER,
336 p_prog_id IN NUMBER,
337 p_prog_app_id IN NUMBER,
338 p_login_id IN NUMBER,
339
340 x_ActivityEstimateTable OUT NOCOPY ActivityEstimateTable,
341 x_return_status OUT NOCOPY VARCHAR2,
342 x_msg_count OUT NOCOPY NUMBER,
343 x_msg_data OUT NOCOPY VARCHAR2 ) IS
344
345 l_api_name CONSTANT VARCHAR2(30) := 'Compute_Activity_Estimate';
346 l_api_version CONSTANT NUMBER := 1.0;
347
348 l_msg_count NUMBER := 0;
349 l_msg_data VARCHAR2(8000) := '';
350
351 l_effective_datetime DATE :=
352 fnd_date.canonical_to_date(p_effective_datetime);
353 l_eam_item_type NUMBER := 0;
354 l_rates_ct NUMBER := 0;
355 l_lot_size NUMBER := 0;
356 l_round_unit NUMBER := 0;
357 l_precision NUMBER := 0;
358 l_ext_precision NUMBER := 0;
359 l_cost_group_id NUMBER := 0;
360 l_primary_cost_method NUMBER := 0;
361 l_maint_cost_category NUMBER := 0;
362 l_eam_cost_element NUMBER := 0;
363 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
364 l_api_message VARCHAR2(10000);
365 l_stmt_num NUMBER;
366 l_dept_id NUMBER := 0;
367 l_dummy NUMBER := 0;
368 l_asset_group_item_id NUMBER := 0;
369 l_asset_number VARCHAR2(80) := '';
370 l_department_id NUMBER := 0;
371 l_ActivityEstimateTable ActivityEstimateTable := ActivityEstimateTable();
372
373 CURSOR c_bor IS
374 SELECT bos.operation_seq_num operation_seq_num,
375 decode(br.functional_currency_flag,
376 1, 1,
377 NVL(crc.resource_rate,0))
378 * bomres.usage_rate_or_amount
379 * decode(bomres.basis_type,
380 1, l_lot_size,
381 2, 1,
382 1) raw_resource_value,
383
384
385 ROUND(decode(br.functional_currency_flag,
386 1, 1,
387 NVL(crc.resource_rate,0))
388 * bomres.usage_rate_or_amount
389 * decode(bomres.basis_type,
390 1, l_lot_size,
391 2, 1,
392 1) ,l_ext_precision) resource_value,
393
394 bomres.resource_id resource_id,
395 bomres.resource_seq_num resource_seq_num,
396 bomres.basis_type basis_type,
397 bomres.usage_rate_or_amount
398 * decode(bomres.basis_type,
399 1, l_lot_size,
400 2, 1,
401 1) usage_rate_or_amount,
402 bomres.standard_rate_flag standard_flag,
403 bos.department_id department_id,
404 br.functional_currency_flag functional_currency_flag,
405 br.cost_element_id cost_element_id,
406 br.resource_type resource_type
407 FROM bom_operational_routings bor,
408 bom_operation_resources bomres,
409 bom_operation_sequences bos,
410 bom_resources br,
411 cst_resource_costs crc
412 WHERE
413 bor.assembly_item_id = p_activity_item_id
414 AND bor.organization_id = p_organization_id
415 AND bor.pending_from_ecn IS NULL
416 AND bor.routing_type = 1
417 AND ( NVL(bor.alternate_routing_designator, 'none')
418 =NVL(p_alt_rtg_designator, 'none')
419 OR (
420 (p_alt_rtg_designator IS NOT NULL)
421 AND (bor.alternate_routing_designator IS NULL)
422 AND NOT EXISTS
423 (SELECT 'X'
424 FROM bom_operational_routings bor1
425 WHERE bor1.assembly_item_id = bor.assembly_item_id
426 AND bor1.organization_id = p_organization_id
427 AND bor1.pending_from_ecn is NULL
428 AND bor1.alternate_routing_designator =
429 p_alt_rtg_designator
430 AND bor1.routing_type = 1
431 )
432 )
433 )
434 AND bos.implementation_date IS NOT NULL
435 AND bos.routing_sequence_id =
436 bor.common_routing_sequence_id
437
438 AND bos.effectivity_date <= l_effective_datetime
439 AND NVL( bos.disable_date, l_effective_datetime + 1)
440 > l_effective_datetime
441
442 AND NVL( bos.eco_for_production, 2 ) = 2
443 AND bomres.operation_sequence_id = bos.operation_sequence_id
444 AND NVL( bomres.acd_type, 1 ) <> 3
445 AND br.resource_id = bomres.resource_id
446 AND br.organization_id = p_organization_id
447 AND br.allow_costs_flag = 1
448 AND crc.resource_id = bomres.resource_id
449 AND crc.cost_type_id = l_rates_ct;
450
451 CURSOR c_rbo ( p_resource_id NUMBER,
452 p_dept_id NUMBER,
453 p_org_id NUMBER,
454 p_res_units NUMBER,
455 p_res_value NUMBER) IS
456
457 SELECT cdo.overhead_id ovhd_id,
458 cdo.rate_or_amount actual_cost,
459 cdo.basis_type basis_type,
460 ROUND(cdo.rate_or_amount *
461 decode(cdo.basis_type,
462 3, p_res_units,
463 p_res_value), l_ext_precision) rbo_value,
464 cdo.department_id
465 FROM cst_resource_overheads cro,
466 cst_department_overheads cdo
467 WHERE cdo.department_id = p_dept_id
468 AND cdo.organization_id = p_org_id
469 AND cdo.cost_type_id = l_rates_ct
470 AND cdo.basis_type IN (3,4)
471 AND cro.cost_type_id = cdo.cost_type_id
472 AND cro.resource_id = p_resource_id
473 AND cro.overhead_id = cdo.overhead_id
474 AND cro.organization_id = cdo.organization_id;
475
476
477 CURSOR c_bbom IS
478 SELECT bic.operation_seq_num operation_seq_num,
479 bos.department_id department_id,
480 ROUND (SUM(NVL(component_quantity,0) *
481 DECODE(msi.stock_enabled_flag,
482 'N',decode(msi.eam_item_type,
483 3,decode(wep.issue_zero_cost_flag,
484 'Y', 0,
485 NVL(bic.unit_price,0)),
486 NVL(bic.unit_price,0)),
487 decode(msi.eam_item_type,
488 3,decode(wep.issue_zero_cost_flag,
489 'Y', 0,
490 NVL(ccicv.item_cost,0)),
491 NVL(ccicv.item_cost,0))
492 )
493 ), l_ext_precision
494 ) mat_value
495 FROM bom_bill_of_materials bbom,
496 bom_inventory_components bic,
497 cst_cg_item_costs_view ccicv,
498 bom_operational_routings bor,
499 bom_operation_sequences bos,
500 mtl_system_items_b msi,
501 wip_eam_parameters wep
502 WHERE bbom.organization_id = p_organization_id
503 AND bbom.assembly_item_id = p_activity_item_id
504 AND bbom.assembly_type = 1
505 AND ( (bbom.Alternate_bom_designator IS NULL
506 AND p_alt_bom_designator IS NULL)
507 OR
508 (p_alt_bom_designator IS NOT NULL
509 AND
510 bbom.alternate_bom_designator = p_alt_bom_designator)
511 OR ((p_alt_bom_designator IS NOT NULL)
512 AND (bbom.alternate_bom_designator IS NULL)
513 AND NOT EXISTS
514 (SELECT 'X'
515 FROM bom_bill_of_materials bbom1
516 WHERE bbom1.assembly_item_id = bbom.assembly_item_id
517 AND bbom1.organization_id = bbom.organization_id
518 AND bbom1.alternate_bom_designator
519 = p_alt_bom_designator)
520 )
521 )
522 AND bor.organization_id = p_organization_id
523 AND bor.assembly_item_id = p_activity_item_id
524 AND bor.pending_from_ecn IS NULL
525 AND bor.routing_type = 1
526 AND ( NVL(bor.alternate_routing_designator, 'none')
527 =NVL(p_alt_rtg_designator, 'none')
528 OR (
529 (p_alt_rtg_designator IS NOT NULL)
530 AND (bor.alternate_routing_designator IS NULL)
531 AND NOT EXISTS
532 (SELECT 'X'
533 FROM bom_operational_routings bor1
534 WHERE bor1.assembly_item_id = bor.assembly_item_id
535 AND bor1.organization_id = p_organization_id
536 AND bor1.pending_from_ecn is NULL
537 AND bor1.alternate_routing_designator =
538 p_alt_rtg_designator
539 AND bor1.routing_type = 1
540 )
541 )
542 )
543 AND bos.implementation_date IS NOT NULL
544 AND bos.routing_sequence_id =
545 bor.common_routing_sequence_id
546
547 AND bos.effectivity_date <= l_effective_datetime
548 AND NVL( bos.disable_date, l_effective_datetime + 1)
549 > l_effective_datetime
550 AND NVL( bos.eco_for_production, 2 ) = 2
551 AND bos.operation_seq_num = bic.operation_seq_num
552 AND bic.bill_sequence_id = bbom.common_bill_sequence_id
553 AND NVL(bic.acd_type,1) <> 3
554 AND NVL(bic.eco_for_production,2) = 2
555 AND bic.wip_supply_type IN (1,4)
556 AND (bic.effectivity_date <=
557 fnd_date.canonical_to_date(p_effective_datetime))
558 AND NVL(bic.disable_date,
559 fnd_date.canonical_to_date(p_effective_datetime)+1) >
560 fnd_date.canonical_to_date(p_effective_datetime)
561 AND ccicv.inventory_item_id(+) = bic.component_item_id
562 AND ccicv.organization_id(+) = p_organization_id
563 AND ccicv.cost_group_id(+) = decode(l_primary_cost_method,1,1,
564 l_cost_group_id)
565 AND msi.inventory_item_id = bic.component_item_id
566 AND msi.organization_id = p_organization_id
567 AND wep.organization_id = p_organization_id
568 GROUP BY bic.operation_seq_num, bos.department_id;
569
570 BEGIN
571
572 -------------------------------------------------------------------------
573 -- standard start of API savepoint
574 -------------------------------------------------------------------------
575 SAVEPOINT Compute_Activity_Estimate;
576
577 -------------------------------------------------------------------------
578 -- standard call to check for call compatibility
579 -------------------------------------------------------------------------
580 l_stmt_num := 5;
581
582 IF NOT fnd_api.compatible_api_call (
583 l_api_version,
584 p_api_version,
585 l_api_name,
586 G_PKG_NAME ) then
587
588 RAISE fnd_api.g_exc_unexpected_error;
589
590 END IF;
591
592 -------------------------------------------------------------------------
593 -- Initialize message list if p_init_msg_list is set to TRUE
594 -------------------------------------------------------------------------
595
596 IF FND_API.to_Boolean(p_init_msg_list) THEN
597 FND_MSG_PUB.initialize;
598 END IF;
599
600
601 -------------------------------------------------------------------------
602 -- initialize api return status to success
603 -------------------------------------------------------------------------
604 x_return_status := fnd_api.g_ret_sts_success;
605
606 -- assign to local variables
607
608 -------------------------------------------------------------------------
609 -- Check Item Type is Activity
610 -------------------------------------------------------------------------
611
612 l_stmt_num := 10;
613
614 SELECT NVL(eam_item_type,-1)
615 INTO l_eam_item_type
616 FROM mtl_system_items msi
617 WHERE msi.organization_id = p_organization_id
618 AND msi.inventory_item_id = p_activity_item_id;
619
620 IF l_eam_item_type <> 2 THEN
621
622 l_api_message := 'The following Item is not of type Activity: '
623 ||TO_CHAR(p_activity_item_id);
624
625 FND_MSG_PUB.ADD_EXC_MSG('CST_EAMJOB_ACTESTIMATE', 'COMPUTE_ACTIVITY_EST('
626 ||TO_CHAR(l_stmt_num)
627 ||'): ', l_api_message);
628 RAISE FND_API.g_exc_error;
629
630 END IF;
631
632 -------------------------------------------------------------------------
633 -- Get the Org's default cost group
634 -------------------------------------------------------------------------
635
636 IF (p_cost_group_id IS NULL) THEN
637
638 l_stmt_num := 15;
639
640 SELECT NVL(default_cost_group_id,-1)
641 INTO l_cost_group_id
642 FROM mtl_parameters
643 WHERE organization_id = p_organization_id;
644
645 ELSE
646
647 l_stmt_num := 20;
648
649 l_cost_group_id := p_cost_group_id;
650
651 END IF;
652
653 -------------------------------------------------------------------------
654 -- Derive the currency extended precision for the organization
655 -------------------------------------------------------------------------
656 l_stmt_num := 25;
657
658 CSTPUTIL.CSTPUGCI(p_organization_id,
659 l_round_unit,
660 l_precision,
661 l_ext_precision);
662
663
664 -------------------------------------------------------------------------
665 -- Derive valuation rates cost type based on organization's cost method
666 -------------------------------------------------------------------------
667
668 l_stmt_num := 30;
669
670 SELECT decode (mp.primary_cost_method,
671 1, mp.primary_cost_method,
672 NVL(mp.avg_rates_cost_type_id,-1)),
673 mp.primary_cost_method
674 INTO l_rates_ct,
675 l_primary_cost_method
676 FROM mtl_parameters mp
677 WHERE mp.organization_id = p_organization_id;
678
679 IF (l_rates_ct = -1) THEN
680 l_api_message := 'Rates Type not defined for Org: '
681 ||TO_CHAR(p_organization_id);
682
683 FND_MSG_PUB.ADD_EXC_MSG('CST_EAMJOB_ACTESTIMATE', 'COMPUTE_ACTIVITY_EST('
684 ||TO_CHAR(l_stmt_num)
685 ||'): ', l_api_message);
686 RAISE FND_API.g_exc_error;
687
688 ELSE
689
690 l_stmt_num := 35;
691
692 BEGIN
693
694 SELECT lot_size
695 INTO l_lot_size
696 FROM cst_item_costs cic
697 WHERE cic.organization_id = p_organization_id
698 AND cic.inventory_item_id = p_activity_item_id
699 AND cic.cost_type_id = l_rates_ct;
700
701 EXCEPTION
702 WHEN others then
703 l_lot_size := 1;
704 END;
705
706 END IF;
707
708 IF (p_debug = 'Y') THEN
709 l_api_message := l_api_message||' Rates Ct: '||TO_CHAR(l_rates_ct);
710 l_api_message := l_api_message||' Lot Size: '||TO_CHAR(l_lot_size);
711 l_api_message := l_api_message||' Ext Precision: '
712 ||TO_CHAR(l_ext_precision);
713 l_api_message := l_api_message||' Activity Item Id: '
714 ||TO_CHAR(p_activity_item_id);
715 l_api_message := l_api_message||' Cg Id: '||TO_CHAR(l_cost_group_id);
716 l_api_message := l_api_message||' Cost Method: '
717 ||TO_CHAR(l_primary_cost_method);
718
719 FND_MESSAGE.SET_NAME ('BOM', 'CST_API_MESSAGE');
720 FND_MESSAGE.SET_TOKEN ('TEXT', l_api_message);
721 FND_MSG_PUB.add;
722
723
724 END IF;
725
726 -------------------------------------------------------------------------
727 -- Compute Resource Costs (BOR)
728 -------------------------------------------------------------------------
729 l_stmt_num := 40;
730
731 FOR c_bor_rec IN c_bor LOOP
732
733 IF (p_debug = 'Y') THEN
734
735 l_api_message :=' Op: ';
736 l_api_message :=l_api_message||TO_CHAR(c_bor_rec.operation_seq_num);
737 l_api_message :=l_api_message||' Department Id: ';
738 l_api_message :=l_api_message||TO_CHAR(c_bor_rec.department_id);
739 l_api_message :=l_api_message||' Resource Type: ';
740 l_api_message :=l_api_message||TO_CHAR(c_bor_rec.resource_type);
741 l_api_message :=l_api_message||' BOR,Value: '
742 ||TO_CHAR(c_bor_rec.resource_value);
743
744 FND_MESSAGE.SET_NAME ('BOM', 'CST_API_MESSAGE');
745 FND_MESSAGE.SET_TOKEN ('TEXT', l_api_message);
746 FND_MSG_PUB.add;
747
748 END IF;
749
750 l_stmt_num := 45;
751
752 Get_DeptCostCatg (
753 p_api_version => 1.0,
754 p_department_id => c_bor_rec.department_id,
755
756 p_user_id => p_user_id,
757 p_request_id => p_request_id,
758 p_prog_id => p_prog_id,
759 p_prog_app_id => p_prog_app_id,
760 p_login_id => p_login_id,
761
762 x_dept_cost_catg => l_maint_cost_category,
763 x_return_status => l_return_status,
764
765 x_msg_count => l_msg_count,
766 x_msg_data => l_msg_data );
767
768 IF l_return_status <> FND_API.g_ret_sts_success THEN
769
770 l_api_message := 'Get_DeptCostCatg returned error';
771 FND_MSG_PUB.ADD_EXC_MSG('CST_EAMJOB_ACTESTIMATE', 'COMPUTE_Activty_Estimate('
772 ||TO_CHAR(l_stmt_num)
773 ||'): ', l_api_message);
774 RAISE FND_API.g_exc_error;
775
776 END IF;
777
778 l_stmt_num := 50;
779
780 l_eam_cost_element :=
781 Get_eamCostElement(p_txn_mode => 2,
782 p_org_id => p_organization_id,
783 p_resource_id => c_bor_rec.resource_id);
784
785 IF l_eam_cost_element = 0 THEN
786
787 l_api_message := 'Get_eamCostElement returned error';
788 FND_MSG_PUB.ADD_EXC_MSG('CST_EAMJOB_ACTESTIMATE', 'COMPUTE_JOB_ESTIMATES('
789 ||TO_CHAR(l_stmt_num)
790 ||'): ', l_api_message);
791 RAISE FND_API.g_exc_error;
792
793 END IF;
794
795 IF (p_debug = 'Y') THEN
796
797 l_api_message :=' MCC: ';
798 l_api_message :=l_api_message||TO_CHAR(l_maint_cost_category);
799 l_api_message :=l_api_message||' CE: '||TO_CHAR(l_eam_cost_element);
800 FND_MESSAGE.SET_NAME ('BOM', 'CST_API_MESSAGE');
801 FND_MESSAGE.SET_TOKEN ('TEXT', l_api_message);
802
803 FND_MSG_PUB.add;
804
805 END IF;
806
807 l_stmt_num := 55;
808
809 l_ActivityEstimateTable.EXTEND;
810 l_ActivityEstimateTable(l_ActivityEstimateTable.LAST).record_type := 1;
811 l_ActivityEstimateTable(l_ActivityEstimateTable.LAST).organization_id :=
812 p_organization_id;
813 l_ActivityEstimateTable(l_ActivityEstimateTable.LAST).activity_item_id :=
814 p_activity_item_id;
815 l_ActivityEstimateTable(l_ActivityEstimateTable.LAST).resource_id :=
816 c_bor_rec.resource_id;
817 l_ActivityEstimateTable(l_ActivityEstimateTable.LAST).op_seq_num :=
818 c_bor_rec.operation_seq_num;
819 l_ActivityEstimateTable(l_ActivityEstimateTable.LAST).maint_cost_catg :=
820 l_maint_cost_category;
821 l_ActivityEstimateTable(l_ActivityEstimateTable.LAST).eam_cost_element :=
822 l_eam_cost_element;
823 l_ActivityEstimateTable(l_ActivityEstimateTable.LAST).cost_value :=
824 c_bor_rec.resource_value;
825
826 -----------------------------------------------------------------------
827 -- Compute Resource Based Overheads Costs (RBO)
828 -----------------------------------------------------------------------
829
830 l_stmt_num := 60;
831
832 FOR c_rbo_rec IN c_rbo(c_bor_rec.resource_id,
833 c_bor_rec.department_id,
834 p_organization_id,
835 c_bor_rec.usage_rate_or_amount,
836 c_bor_rec.raw_resource_value)
837 LOOP
838
839 IF (p_debug = 'Y') THEN
840
841 l_api_message :=' Op: ';
842 l_api_message :=l_api_message||TO_CHAR(c_bor_rec.operation_seq_num);
843 l_api_message :=l_api_message||' RBO,Value: '||TO_CHAR(c_rbo_rec.rbo_value);
844 l_api_message :=l_api_message||' MCC: ';
845 l_api_message :=l_api_message||TO_CHAR(l_maint_cost_category);
846 l_api_message :=l_api_message||' CE: '||TO_CHAR(l_eam_cost_element);
847 FND_MESSAGE.SET_NAME ('BOM', 'CST_API_MESSAGE');
848 FND_MESSAGE.SET_TOKEN ('TEXT', l_api_message);
849 FND_MSG_PUB.add;
850
851 END IF;
852
853 l_stmt_num := 65;
854
855 l_ActivityEstimateTable.EXTEND;
856 l_ActivityEstimateTable(l_ActivityEstimateTable.LAST).record_type := 2;
857 l_ActivityEstimateTable(l_ActivityEstimateTable.LAST).organization_id :=
858 p_organization_id;
859 l_ActivityEstimateTable(l_ActivityEstimateTable.LAST).activity_item_id :=
860 p_activity_item_id;
861 l_ActivityEstimateTable(l_ActivityEstimateTable.LAST).op_seq_num :=
862 c_bor_rec.operation_seq_num;
863 l_ActivityEstimateTable(l_ActivityEstimateTable.LAST).resource_id :=
864 c_bor_rec.resource_id;
865 l_ActivityEstimateTable(l_ActivityEstimateTable.LAST).overhead_id :=
866 c_rbo_rec.ovhd_id;
867 l_ActivityEstimateTable(l_ActivityEstimateTable.LAST).maint_cost_catg :=
868 l_maint_cost_category;
869 l_ActivityEstimateTable(l_ActivityEstimateTable.LAST).eam_cost_element:=
870 l_eam_cost_element;
871 l_ActivityEstimateTable(l_ActivityEstimateTable.LAST).cost_value :=
872 c_rbo_rec.rbo_value;
873
874 END LOOP; -- c_rbo_rec
875
876 END LOOP; --c_bor_rec
877
878 -------------------------------------------------------------------------
879 -- Compute Material Costs
880 -------------------------------------------------------------------------
881
882 l_stmt_num := 70;
883
884 FOR c_bbom_rec IN c_bbom LOOP
885
886 l_stmt_num := 75;
887
888 IF (p_debug = 'Y') THEN
889
890 l_api_message :=' Op: ';
891 l_api_message :=l_api_message||TO_CHAR(c_bbom_rec.operation_seq_num);
892 l_api_message :=l_api_message||' Department Id: ' ;
893 l_api_message :=l_api_message||TO_CHAR(c_bbom_rec.department_id);
894 l_api_message :=l_api_message||' WRO,Value: '
895 ||TO_CHAR(c_bbom_rec.mat_value);
896 FND_MESSAGE.SET_NAME ('BOM', 'CST_API_MESSAGE');
897 FND_MESSAGE.SET_TOKEN ('TEXT', l_api_message);
898 FND_MSG_PUB.add;
899
900 END IF;
901
902 l_stmt_num := 80;
903
904 Get_DeptCostCatg (
905 p_api_version => 1.0,
906 p_organization_id => p_organization_id,
907 p_department_id => c_bbom_rec.department_id,
908
909 p_user_id => p_user_id,
910 p_request_id => p_request_id,
911 p_prog_id => p_prog_id,
912 p_prog_app_id => p_prog_app_id,
913 p_login_id => p_login_id,
914
915 x_dept_cost_catg => l_maint_cost_category,
916 x_return_status => l_return_status,
917 x_msg_count => l_msg_count,
918 x_msg_data => l_msg_data );
919
920 IF l_return_status <> FND_API.g_ret_sts_success THEN
921
922 l_api_message := 'Get_DeptCostCatg returned error';
923
924 FND_MSG_PUB.ADD_EXC_MSG('CST_EAMJOAB_ACTESTIMATE', 'COMPUTE_ACTIVITY_EST('
925 ||TO_CHAR(l_stmt_num)
926 ||'): ', l_api_message);
927 RAISE FND_API.g_exc_error;
928
929 END IF;
930
931 l_stmt_num := 85;
932
933 l_eam_cost_element :=
934 Get_eamCostElement(p_txn_mode => 1,
935 p_org_id => p_organization_id);
936
937 IF l_eam_cost_element = 0 THEN
938
939 l_api_message := 'Get_eamCostElement returned error';
940
941 FND_MSG_PUB.ADD_EXC_MSG('CST_EAMJOB_ACTESTIMATE', 'COMPUTE_ACTIVITY_ESTIMATE('
942 ||TO_CHAR(l_stmt_num)
943 ||'): ', l_api_message);
944 RAISE FND_API.g_exc_error;
945
946 END IF;
947
948 IF (p_debug = 'Y') THEN
949
950 l_api_message :=' MCC: ';
951
952 l_api_message :=l_api_message||TO_CHAR(l_maint_cost_category);
953 l_api_message :=l_api_message||' CE: '||TO_CHAR(l_eam_cost_element);
954 FND_MESSAGE.SET_NAME ('BOM', 'CST_API_MESSAGE');
955 FND_MESSAGE.SET_TOKEN ('TEXT', l_api_message);
956 FND_MSG_PUB.add;
957
958 END IF;
959
960 l_stmt_num := 90;
961
962 l_ActivityEstimateTable.EXTEND;
963 l_ActivityEstimateTable(l_ActivityEstimateTable.LAST).record_type := 3;
964 l_ActivityEstimateTable(l_ActivityEstimateTable.LAST).organization_id :=
965 p_organization_id;
966 l_ActivityEstimateTable(l_ActivityEstimateTable.LAST).activity_item_id :=
967 p_activity_item_id;
968 l_ActivityEstimateTable(l_ActivityEstimateTable.LAST).op_seq_num :=
969 c_bbom_rec.operation_seq_num;
970 l_ActivityEstimateTable(l_ActivityEstimateTable.LAST).maint_cost_catg :=
971 l_maint_cost_category;
972 l_ActivityEstimateTable(l_ActivityEstimateTable.LAST).eam_cost_element :=
973 l_eam_cost_element;
974 l_ActivityEstimateTable(l_ActivityEstimateTable.LAST).cost_value :=
975 c_bbom_rec.mat_value;
976
977 END LOOP;
978
979 l_stmt_num := 95;
980
981 x_ActivityEstimateTable := l_ActivityEstimateTable;
982
983 ---------------------------------------------------------------------------
984 -- Standard check of p_commit
985 ---------------------------------------------------------------------------
986
987 IF FND_API.to_Boolean(p_commit) THEN
988 COMMIT WORK;
989 END IF;
990
991 ---------------------------------------------------------------------------
992 -- Standard Call to get message count and if count = 1, get message info
993 ---------------------------------------------------------------------------
994
995 FND_MSG_PUB.Count_And_Get (
996 p_count => x_msg_count,
997 p_data => x_msg_data );
998
999 EXCEPTION
1000
1001 WHEN fnd_api.g_exc_error THEN
1002 x_return_status := fnd_api.g_ret_sts_error;
1003
1004 -- Get message count and data
1005 fnd_msg_pub.count_and_get
1006 ( p_count => x_msg_count
1007 , p_data => x_msg_data
1008 );
1009 --
1010 WHEN fnd_api.g_exc_unexpected_error THEN
1011 x_return_status := fnd_api.g_ret_sts_unexp_error ;
1012
1013 -- Get message count and data
1014 fnd_msg_pub.count_and_get
1015 ( p_count => x_msg_count
1016 , p_data => x_msg_data
1017 );
1018 --
1019 WHEN OTHERS THEN
1020 x_return_status := fnd_api.g_ret_sts_unexp_error ;
1021 --
1022 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)
1023 THEN
1024 fnd_msg_pub.add_exc_msg
1025 ( 'CST_EAMJOB_ACTESTIMATE'
1026
1027 , 'Compute_Activity_Estimate: l_stmt_num - '||to_char(l_stmt_num)
1028 );
1029
1030 END IF;
1031 -- Get message count and data
1032 fnd_msg_pub.count_and_get
1033 ( p_count => x_msg_count
1034 , p_data => x_msg_data
1035 );
1036
1037 END Compute_Activity_Estimate;
1038
1039 --------------------------------------------------------------------------n
1040 -- PROCEDURE --
1041 -- Get_Activity_Estimate --
1042 -- --
1043 -- --
1044 -- DESCRIPTION --
1045 -- This API Computes the estimate for an asset activity --
1046 -- --
1047 -- PURPOSE: --
1048 -- Oracle Applications Rel 11i.9 --
1049 -- --
1050 -- --
1051
1052 -- --
1053 -- --
1054 -- HISTORY: --
1055 -- 08/07/02 Hemant G Created --
1056 ----------------------------------------------------------------------------
1057 PROCEDURE Get_Activity_Estimate (
1058 p_api_version IN NUMBER,
1059 p_init_msg_list IN VARCHAR2
1060 := FND_API.G_FALSE,
1061 p_commit IN VARCHAR2
1062 := FND_API.G_FALSE,
1063 p_validation_level IN NUMBER
1064 := FND_API.G_VALID_LEVEL_FULL,
1065 p_debug IN VARCHAR2 := 'N',
1066
1067 p_activity_item_id IN NUMBER,
1068 p_organization_id IN NUMBER,
1069 p_alt_bom_designator IN VARCHAR2 := NULL,
1070 p_alt_rtg_designator IN VARCHAR2 := NULL,
1071 p_cost_group_id IN NUMBER := NULL,
1072 p_effective_datetime IN VARCHAR2 :=
1073 fnd_date.date_to_canonical(SYSDATE),
1074
1075 p_user_id IN NUMBER,
1076 p_request_id IN NUMBER,
1077 p_prog_id IN NUMBER,
1078 p_prog_app_id IN NUMBER,
1079 p_login_id IN NUMBER,
1080
1081 x_activity_estimate_record_id OUT NOCOPY NUMBER,
1082 x_return_status OUT NOCOPY VARCHAR2,
1083 x_msg_count OUT NOCOPY NUMBER,
1084 x_msg_data OUT NOCOPY VARCHAR2 ) IS
1085
1086 l_api_name CONSTANT VARCHAR2(30) := 'Compute_Activity_Estimate';
1087 l_api_version CONSTANT NUMBER := 1.0;
1088 l_api_message VARCHAR2(10000);
1089 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
1090
1091 l_stmt_num NUMBER;
1092 l_msg_count NUMBER := 0;
1093 l_msg_data VARCHAR2(8000) := '';
1094
1095 l_activity_estimate_record_id NUMBER := -1;
1096
1097 l_ActivityEstimateTable ActivityEstimateTable := ActivityEstimateTable();
1098
1099 BEGIN
1100
1101 -------------------------------------------------------------------------
1102 -- standard start of API savepoint
1103 -------------------------------------------------------------------------
1104 SAVEPOINT Compute_Activity_Estimate;
1105
1106 -------------------------------------------------------------------------
1107 -- standard call to check for call compatibility
1108 -------------------------------------------------------------------------
1109 l_stmt_num := 5;
1110
1111 IF NOT fnd_api.compatible_api_call (
1112 l_api_version,
1113 p_api_version,
1114 l_api_name,
1115 G_PKG_NAME ) then
1116
1117 RAISE fnd_api.g_exc_unexpected_error;
1118
1119 END IF;
1120
1121 -------------------------------------------------------------------------
1122 -- Initialize message list if p_init_msg_list is set to TRUE
1123 -------------------------------------------------------------------------
1124
1125 IF FND_API.to_Boolean(p_init_msg_list) THEN
1126 FND_MSG_PUB.initialize;
1127 END IF;
1128
1129
1130 -------------------------------------------------------------------------
1131 -- initialize api return status to success
1132 -------------------------------------------------------------------------
1133 x_return_status := fnd_api.g_ret_sts_success;
1134
1135 -- assign to local variables
1136
1137 Compute_Activity_Estimate (
1138 p_api_version => 1.0,
1139
1140 p_activity_item_id => p_activity_item_id,
1141 p_organization_id => p_organization_id,
1142 p_alt_bom_designator => p_alt_bom_designator,
1143 p_alt_rtg_designator => p_alt_rtg_designator,
1144 p_cost_group_id => p_cost_group_id,
1145 p_effective_datetime => p_effective_datetime,
1146
1147 p_user_id => p_user_id,
1148 p_request_id => p_request_id,
1149 p_prog_id => p_prog_id,
1150 p_prog_app_id => p_prog_app_id,
1151 p_login_id => p_login_id,
1152
1153 x_ActivityEstimateTable => l_ActivityEstimateTable,
1154 x_return_status => l_return_status,
1155 x_msg_count => l_msg_count,
1156 x_msg_data => l_msg_data ) ;
1157
1158 IF (l_return_status <> FND_API.g_ret_sts_success) THEN
1159
1160 l_api_message := 'Compute_Activity_Estimate returned error';
1161
1162 FND_MSG_PUB.ADD_EXC_MSG('CST_EAMJOB_ACTESTIMATE', 'GET_ACTIVITY_EST('
1163 ||TO_CHAR(l_stmt_num)
1164 ||'): ', l_api_message);
1165 RAISE FND_API.g_exc_error;
1166
1167 END IF;
1168
1169 IF l_ActivityEstimateTable.EXISTS(1) THEN
1170
1171 SELECT cst_eam_activity_estimates_s.nextval
1172 INTO l_activity_estimate_record_id
1173 FROM DUAL;
1174
1175
1176 FOR j IN l_ActivityEstimateTable.FIRST .. l_ActivityEstimateTable.LAST LOOP
1177
1178 INSERT INTO cst_eam_activity_estimates (
1179 activity_estimate_record_id,
1180 record_type,
1181 organization_id,
1182 activity_item_id,
1183 eam_cost_element,
1184 maint_cost_category,
1185 cost_value)
1186
1187 VALUES (
1188 l_activity_estimate_record_id,
1189 'D',
1190 l_ActivityEstimateTable(j).organization_id,
1191 l_ActivityEstimateTable(j).activity_item_id,
1192 l_ActivityEstimateTable(j).eam_cost_element,
1193 l_ActivityEstimateTable(j).maint_cost_catg,
1194 l_ActivityEstimateTable(j).cost_value
1195 );
1196
1197 END LOOP;
1198
1199 INSERT INTO cst_eam_activity_estimates (
1200 activity_estimate_record_id,
1201 record_type,
1202 organization_id,
1203 activity_item_id,
1204 eam_cost_element,
1205 maint_cost_category,
1206 cost_value)
1207
1208 SELECT l_activity_estimate_record_id,
1209 'S' record_type,
1210 organization_id organization_id,
1211 activity_item_id activity_item_id,
1212 eam_cost_element eam_cost_element,
1213 maint_cost_category maint_cost_category,
1214 SUM(cost_value) cost_value
1215 FROM cst_eam_activity_estimates caet
1216 GROUP BY l_activity_estimate_record_id,
1217 record_type,
1218 organization_id,
1219 activity_item_id,
1220 eam_cost_element,
1221 maint_cost_category;
1222
1223 END IF;
1224
1225 x_activity_estimate_record_id := l_activity_estimate_record_id;
1226
1227 ---------------------------------------------------------------------------
1228 -- Standard check of p_commit
1229 ---------------------------------------------------------------------------
1230
1231 IF FND_API.to_Boolean(p_commit) THEN
1232 COMMIT WORK;
1233 END IF;
1234
1235 ---------------------------------------------------------------------------
1236 -- Standard Call to get message count and if count = 1, get message info
1237 ---------------------------------------------------------------------------
1238
1239 FND_MSG_PUB.Count_And_Get (
1240 p_count => x_msg_count,
1241 p_data => x_msg_data );
1242
1243
1244 EXCEPTION
1245
1246 WHEN fnd_api.g_exc_error THEN
1247 x_return_status := fnd_api.g_ret_sts_error;
1248
1249 -- Get message count and data
1250 fnd_msg_pub.count_and_get
1251 ( p_count => x_msg_count
1252 , p_data => x_msg_data
1253 );
1254 --
1255 WHEN fnd_api.g_exc_unexpected_error THEN
1256 x_return_status := fnd_api.g_ret_sts_unexp_error ;
1257
1258 -- Get message count and data
1259 fnd_msg_pub.count_and_get
1260 ( p_count => x_msg_count
1261 , p_data => x_msg_data
1262 );
1263 --
1264 WHEN OTHERS THEN
1265 x_return_status := fnd_api.g_ret_sts_unexp_error ;
1266 --
1267 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)
1268 THEN
1269 fnd_msg_pub.add_exc_msg
1270 ( 'CST_EAMJOB_ACTESTIMATE'
1271
1272 , 'Get_Activity_Estimate: l_stmt_num - '||to_char(l_stmt_num)
1273 );
1274
1275 END IF;
1276 -- Get message count and data
1277 fnd_msg_pub.count_and_get
1278 ( p_count => x_msg_count
1279 , p_data => x_msg_data
1280 );
1281
1282
1283 END Get_Activity_Estimate;
1284
1285
1286 END CST_EAMJOB_ACTESTIMATE;
1287