[Home] [Help]
PACKAGE BODY: APPS.WIP_OPERATIONS_GRP
Source
1 PACKAGE BODY WIP_OPERATIONS_GRP AS
2 /* $Header: wipopgpb.pls 115.6 2004/06/17 13:49:50 mraman noship $ */
3
4 procedure WIP_PERCENTAGE_COMPLETE
5 ( p_api_version IN NUMBER,
6 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
7 p_wip_entity_id IN NUMBER,
8 x_Percentage_complete OUT NOCOPY NUMBER,
9 x_scheduled_hours OUT NOCOPY NUMBER,
10 x_return_status OUT NOCOPY VARCHAR2,
11 x_msg_data OUT NOCOPY VARCHAR2,
12 x_msg_count OUT NOCOPY NUMBER
13 )
14 IS
15 l_total_complete NUMBER :=0;
16 l_scheduled_hours NUMBER; -- Total Scheduled Hours
17 l_hrVal Number ;
18 l_uomClass VARCHAR2(10);
19 l_usage Number ;
20 l_progress Number ;
21 l_op_seq_num number ;
22 l_hrUOM VARCHAR2(3):= fnd_profile.value('BOM:HOUR_UOM_CODE');--hour UOM
23
24 /* Cursor to pick up all the resource usages which are based on time*/
25 CURSOR Cresource IS
26 SELECT operation_seq_num,sum(inv_convert.inv_um_convert(0,
27 NULL,
28 wor.scheduled_units * assigned_units,
29 wor.UOM_CODE,
30 l_hrUom,
31 NULL,
32 NULL ))
33 FROM wip_operation_resources_v wor
34 WHERE wor.wip_entity_id = p_wip_entity_id
35 AND wor.UOM_CODE in (SELECT UOM_CODE
36 FROM mtl_units_of_measure
37 WHERE UOM_CLASS = l_uomclass)
38 GROUP BY wor.operation_seq_num ;
39
40 /* Cursor to get the progress percentage for an operation seq number */
41 CURSOR Cprogress(l_op_seq_num number) IS
42 SELECT progress_percentage
43 FROM wip_operations
44 WHERE operation_seq_num = l_op_seq_num
45 AND wip_entity_id = p_wip_entity_id ;
46
47
48 BEGIN
49
50 SELECT conversion_rate, uom_class
51 INTO l_hrVal, l_uomClass
52 FROM mtl_uom_conversions
53 WHERE uom_code = l_hrUOM
54 AND nvl(disable_date, sysdate + 1) > sysdate
55 AND inventory_item_id = 0;
56
57 OPEN CResource ;
58 LOOP
59 FETCH CResource INTO l_op_seq_num , l_usage ;
60 EXIT WHEN Cresource%NOTFOUND ;
61 OPEN Cprogress(l_op_seq_num);
62 FETCH Cprogress INTO l_progress ;
63 l_total_complete := l_total_complete + NVL(l_progress,0) *
64 NVL(l_usage,0) ;
65 CLOSE Cprogress;
66 END loop;
67 CLOSE CResource ;
68
69
70 SELECT
71 sum(inv_convert.inv_um_convert( 0 ,
72 NULL ,
73 wor.scheduled_units * assigned_units,
74 wor.UOM_CODE,
75 l_hrUOM,
76 NULL,
77 NULL ))
78 INTO l_scheduled_hours
79 FROM wip_operation_resources_v wor
80 WHERE wor.wip_entity_id = p_wip_entity_id
81 AND wor.UOM_CODE in (SELECT UOM_CODE
82 FROM mtl_units_of_measure
83 WHERE UOM_CLASS = l_uomclass);
84
85 x_percentage_complete := l_total_complete / l_scheduled_hours;
86
87 x_scheduled_hours := l_scheduled_hours ;
88
89 -- Standard call to get message count and if count is 1, get message info.
90 FND_MSG_PUB.Count_And_Get
91 ( p_count => x_msg_count,
92 p_data => x_msg_data
93 );
94
95
96 EXCEPTION
97 WHEN NO_DATA_FOUND THEN
98 x_return_status := FND_API.G_RET_STS_ERROR;
99 fnd_message.set_name('WIP', 'NO_DATA_AVAILABLE');
100 FND_MSG_PUB.Add;
101 FND_MSG_PUB.Count_And_Get
102 ( p_count => x_msg_count,
103 p_data => x_msg_data
104 );
105
106 WHEN ZERO_DIVIDE THEN
107 x_return_status := FND_API.G_RET_STS_ERROR;
108 fnd_message.set_name('WIP', 'WIP_NO_SCHEDULED_HRS');
109 FND_MSG_PUB.Add;
110 FND_MSG_PUB.Count_And_Get
111 ( p_count => x_msg_count,
112 p_data => x_msg_data
113 );
114 x_scheduled_hours := 0;
115 WHEN OTHERS THEN
116 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
117 IF FND_MSG_PUB.Check_Msg_Level
118 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
119 THEN
120 FND_MSG_PUB.Add_Exc_Msg
121 ( 'WIP_OPERATIONS_GRP',
122 'WIP_PERCENTAGE_COMPLETE'
123 );
124 END IF;
125 FND_MSG_PUB.Count_And_Get
126 ( p_count => x_msg_count,
127 p_data => x_msg_data
128 );
129
130
131 END WIP_PERCENTAGE_COMPLETE;
132
133
134 END WIP_OPERATIONS_GRP;