[Home] [Help]
PACKAGE BODY: APPS.EAM_UTILITY_GRP
Source
1 PACKAGE BODY EAM_UTILITY_GRP AS
2 /* $Header: EAMGUTLB.pls 120.0 2005/06/22 14:10:21 amondal noship $ */
3 G_PKG_NAME CONSTANT VARCHAR2(30) := 'EAM_UTILITY_GRP';
4 /***************************************************************************
5 --
6 -- Copyright (c) 2002 Oracle Corporation, Redwood Shores, CA, USA
7 -- All rights reserved.
8 --
9 -- FILENAME
10 --
11 -- EAMGUTLB.pls
12 --
13 -- DESCRIPTION
14 --
15 -- Body of package EAM_UTILITY_GRP
16 --
17 -- NOTES
18 --
19 -- HISTORY
20 --
21 -- 16-MAY-2005 Anju Gupta Initial Creation
22 ***************************************************************************/
23
24 --------------------------------------------------------------------------
25 -- PROCEDURE --
26 -- Get_ReplacedRebuilds --
27 -- --
28 -- DESCRIPTION --
29 -- This API is used to determine the list of rebuildables that were --
30 -- replaced from a work order while executing the work order. --
31 -- --
32 -- It can be invoked from within any product in Oracle Applications. --
33 -- Currently IB team will invoke it while processing completion of --
34 -- EAM work orders to build the genealogy correctly. --
35 -- --
36 -- PURPOSE: --
37 -- Oracle Applications Rel 12 --
38 -- --
39 -- HISTORY: --
40 -- 05/16/05 Anju Gupta Created --
41 ----------------------------------------------------------------------------
42
43 PROCEDURE Get_ReplacedRebuilds (
44
45 p_api_version IN NUMBER,
46 p_commit IN VARCHAR2 := FND_API.G_FALSE,
47 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
48 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
49
50 p_wip_entity_id IN number,
51 p_organization_id IN number,
52
53 x_replaced_rebuild_tbl OUT nocopy EAM_UTILITY_GRP.REPLACE_REBUILD_tbl_type,
54 x_return_status OUT nocopy varchar2,
55 x_msg_count OUT NOCOPY NUMBER,
56 x_msg_data OUT NOCOPY VARCHAR2) IS
57
58 l_api_name CONSTANT VARCHAR2(30) := 'Get_ReplacedRebuilds';
59 l_api_version CONSTANT NUMBER := 1.0;
60
61 l_api_message VARCHAR2(10000);
62
63 l_msg_count NUMBER := 0;
64 l_msg_data VARCHAR2(8000) := '';
65
66
67 l_stmt_num NUMBER := 0;
68 l_entity_type NUMBER := 0;
69 l_cnt NUMBER := 0;
70
71 l_replacerebuild_rec EAM_UTILITY_GRP.REPLACE_REBUILD_rec_type;
72
73
74 CURSOR c_rebuilds IS
75
76 select wdj.maintenance_object_id
77 from WIP_DISCRETE_JOBS wdj
78 where wdj.parent_wip_entity_id = p_wip_entity_id
79 and wdj.organization_id = p_organization_id
80 and wdj.manual_rebuild_flag = 'N'
81 and wdj.maintenance_object_type = 3;
82
83
84 Begin
85
86 -------------------------------------------------------------------------
87 -- Establish savepoint
88 -------------------------------------------------------------------------
89
90 SAVEPOINT ReplacedRebuild_GRP;
91
92 -------------------------------------------------------------------------
93 -- standard call to check for call compatibility
94 -------------------------------------------------------------------------
95 IF NOT fnd_api.compatible_api_call (
96 l_api_version,
97 p_api_version,
98 l_api_name,
99 G_PKG_NAME ) then
100
101 RAISE fnd_api.g_exc_unexpected_error;
102
103 END IF;
104
105 ---------------------------------------------------------------------------
106 -- Initialize message list if p_init_msg_list is set to TRUE
107 ---------------------------------------------------------------------------
108
109 IF FND_API.to_Boolean(p_init_msg_list) THEN
110 FND_MSG_PUB.initialize;
111 END IF;
112
113
114 -------------------------------------------------------------------------
115 -- initialize api return status to success
116 -------------------------------------------------------------------------
117
118 l_stmt_num := 10;
119 x_return_status := fnd_api.g_ret_sts_success;
120
121 -------------------------------------------------------------------------
122 -- Validate parameters passed to the API
123 -------------------------------------------------------------------------
124
125 SELECT entity_type
126 INTO l_entity_type
127 FROM wip_entities we
128 WHERE we.wip_entity_id = p_wip_entity_id
129 and we.organization_id = p_organization_id;
130
131 IF (l_entity_type NOT IN (6,7)) THEN
132
133 l_api_message := l_api_message|| 'Invalid WIP entity type: '
134 ||TO_CHAR(l_entity_type)
135 ||' WIP Entity: '
136 ||TO_CHAR(p_wip_entity_id);
137
138 FND_MSG_PUB.ADD_EXC_MSG('EAM_UTILITY_GRP', 'Get_ReplacedRebuild('
139 ||TO_CHAR(l_stmt_num)
140 ||'): ', l_api_message);
141 RAISE FND_API.g_exc_error;
142 END IF;
143
144 -------------------------------------------------------------------------
145 -- Initialize common variables
146 -------------------------------------------------------------------------
147
148 l_stmt_num := 20;
149
150
151 l_cnt := 1;
152
153 -------------------------------------------------------------------------
154 -- Get the Replaced Rebuilds for the Work Order
155 -------------------------------------------------------------------------
156
157 l_stmt_num := 30;
158
159 FOR c_rebuilds_rec IN c_rebuilds
160 LOOP
161
162 l_replacerebuild_rec.instance_id := c_rebuilds_rec.maintenance_object_id;
163
164 x_replaced_rebuild_tbl(l_cnt) := l_replacerebuild_rec;
165
166 l_cnt := l_cnt + 1;
167
168 END LOOP;
169
170 l_stmt_num := 50;
171
172 ---------------------------------------------------------------------------
173 -- Standard Call to get message count and if count = 1, get message info
174 ---------------------------------------------------------------------------
175
176 FND_MSG_PUB.Count_And_Get (
177 p_count => x_msg_count,
178 p_data => x_msg_data );
179
180 EXCEPTION
181
182 WHEN fnd_api.g_exc_error THEN
183 x_return_status := fnd_api.g_ret_sts_error;
184
185 -- Get message count and data
186 fnd_msg_pub.count_and_get
187 ( p_count => x_msg_count
188 , p_data => x_msg_data
189 );
190 --
191 WHEN fnd_api.g_exc_unexpected_error THEN
192 x_return_status := fnd_api.g_ret_sts_unexp_error ;
193
194 -- Get message count and data
195 fnd_msg_pub.count_and_get
196 ( p_count => x_msg_count
197 , p_data => x_msg_data
198 );
199
200 WHEN OTHERS THEN
201 x_return_status := fnd_api.g_ret_sts_unexp_error ;
202 --
203 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)
204 THEN
205 fnd_msg_pub.add_exc_msg
206 ( 'EAM_UTILITY_GRP'
207 , 'Get_ReplacedRebuilds : l_stmt_num - '||to_char(l_stmt_num)
208 );
209
210 END IF;
211 -- Get message count and data
212 fnd_msg_pub.count_and_get
213 ( p_count => x_msg_count
214 , p_data => x_msg_data
215 );
216
217
218 end Get_ReplacedRebuilds;
219
220
221
222 /********************************************
223 *Get Next Maintenance Date for an Equipment*
224 *based on EAM work orders in the system
225 ****************************************/
226
227 FUNCTION get_next_maintenance_date( p_organization_id IN NUMBER,
228 p_resource_id IN NUMBER,
229 p_gen_object_id IN NUMBER) RETURN DATE IS
230
231 l_maint_date DATE;
232 BEGIN
233 if (p_gen_object_id is not null) then
234
235 select min(wdj.scheduled_start_date) as NEXT_MAINT_DATE
236 into l_maint_date
237 from wip_discrete_jobs wdj, wip_entities we, csi_item_instances cii, wip_operations wo
238 where we.entity_type = 6
239 and wdj.wip_entity_id = we.wip_entity_id
240 and wdj.organization_id = we.organization_id
241 and wdj.maintenance_object_type = 3
242 and wdj.maintenance_object_id = cii.instance_id
243 and wdj.organization_id = cii.last_vld_organization_id
244 and cii.equipment_gen_object_id = p_gen_object_id
245 and wdj.organization_id = wo.organization_id (+)
246 and wdj.wip_entity_id = wo.wip_entity_id (+)
247 and wdj.status_type in (1,3,17)
248 and (nvl(wdj.shutdown_type,1) > 1 OR nvl(wo.shutdown_type,1) > 1 )
249 and wdj.scheduled_start_date > sysdate;
250
251 end if;
252
253 RETURN l_maint_date;
254
255 EXCEPTION
256 WHEN OTHERS THEN
257 RETURN l_maint_date;
258
259
260 END;
261
262 END EAM_UTILITY_GRP;