[Home] [Help]
PACKAGE BODY: APPS.EAM_RES_INST_UTILITY_PVT
Source
1 PACKAGE BODY EAM_RES_INST_UTILITY_PVT AS
2 /* $Header: EAMVRIUB.pls 120.2 2006/06/19 22:55:59 cboppana noship $ */
3 /***************************************************************************
4 --
5 -- Copyright (c) 2002 Oracle Corporation, Redwood Shores, CA, USA
6 -- All rights reserved.
7 --
8 -- FILENAME
9 --
10 -- EAMVRIUB.pls
11 --
12 -- DESCRIPTION
13 --
14 -- Body of package EAM_RES_INST_UTILITY_PVT
15 --
16 -- NOTES
17 --
18 -- HISTORY
19 --
20 -- 30-JUN-2002 Kenichi Nagumo Initial Creation
21 ***************************************************************************/
22
23 G_PKG_NAME CONSTANT VARCHAR2(30) := 'EAM_RES_INST_UTILITY_PVT';
24
25 /*********************************************************************
26 * Procedure : Query_Row
27 * Parameters IN : wip entity id
28 * organization Id
29 * operation_seq_num
30 * resource_seq_num
31 * instance_id
32 * Parameters OUT NOCOPY: Resource Instances column record
33 * Mesg token Table
34 * Return Status
35 * Purpose : Procedure will query the database record
36 * and return with those records.
37 ***********************************************************************/
38
39 PROCEDURE Query_Row
40 ( p_wip_entity_id IN NUMBER
41 , p_organization_id IN NUMBER
42 , p_operation_seq_num IN NUMBER
43 , p_resource_seq_num IN NUMBER
44 , p_instance_id IN NUMBER
45 , p_serial_number IN VARCHAR2
46 , x_eam_res_inst_rec OUT NOCOPY EAM_PROCESS_WO_PUB.eam_res_inst_rec_type
47 , x_Return_status OUT NOCOPY VARCHAR2
48 )
49 IS
50 l_eam_res_inst_rec EAM_PROCESS_WO_PUB.eam_res_inst_rec_type;
51 l_return_status VARCHAR2(1):= FND_API.G_RET_STS_SUCCESS;
52 l_dummy varchar2(10);
53 BEGIN
54
55 IF p_serial_number is null THEN
56
57 SELECT
58 start_date
59 , completion_date
60 , batch_id
61 INTO
62 l_eam_res_inst_rec.start_date
63 , l_eam_res_inst_rec.completion_date
64 , l_eam_res_inst_rec.top_level_batch_id
65 FROM wip_op_resource_instances
66 WHERE wip_entity_id = p_wip_entity_id
67 AND organization_id = p_organization_id
68 AND operation_seq_num = p_operation_seq_num
69 AND resource_seq_num = p_resource_seq_num
70 AND instance_id = p_instance_id
71 AND serial_number IS null;
72
73 x_return_status := EAM_PROCESS_WO_PVT.G_RECORD_FOUND;
74 x_eam_res_inst_rec := l_eam_res_inst_rec;
75
76 ELSE
77
78 SELECT
79 serial_number
80 , start_date
81 , completion_date
82 , batch_id
83 INTO
84 l_eam_res_inst_rec.serial_number
85 , l_eam_res_inst_rec.start_date
86 , l_eam_res_inst_rec.completion_date
87 , l_eam_res_inst_rec.top_level_batch_id
88 FROM wip_op_resource_instances
89 WHERE wip_entity_id = p_wip_entity_id
90 AND organization_id = p_organization_id
91 AND operation_seq_num = p_operation_seq_num
92 AND resource_seq_num = p_resource_seq_num
93 AND instance_id = p_instance_id
94 AND serial_number = p_serial_number;
95
96 x_return_status := EAM_PROCESS_WO_PVT.G_RECORD_FOUND;
97 x_eam_res_inst_rec := l_eam_res_inst_rec;
98
99 END IF;
100
101 EXCEPTION
102 WHEN NO_DATA_FOUND THEN
103 x_return_status := EAM_PROCESS_WO_PVT.G_RECORD_NOT_FOUND;
104 x_eam_res_inst_rec := l_eam_res_inst_rec;
105
106 WHEN OTHERS THEN
107 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
108 x_eam_res_inst_rec := l_eam_res_inst_rec;
109
110 END Query_Row;
111
112
113 /********************************************************************
114 * Procedure : Insert_Row
115 * Parameters IN : Resource Instances column record
116 * Parameters OUT NOCOPY: Message Token Table
117 * Return Status
118 * Purpose : Procedure will perfrom an insert into the
119 * win_op_resource_instances table.
120 *********************************************************************/
121
122 PROCEDURE Insert_Row
123 ( p_eam_res_inst_rec IN EAM_PROCESS_WO_PUB.eam_res_inst_rec_type
124 , x_mesg_token_Tbl OUT NOCOPY EAM_ERROR_MESSAGE_PVT.Mesg_Token_Tbl_Type
125 , x_return_Status OUT NOCOPY VARCHAR2
126 )
127 IS
128 BEGIN
129
130 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Writing Resource Instance rec for ' || p_eam_res_inst_rec.instance_id); END IF;
131
132 -- bug no 3444091
133 if p_eam_res_inst_rec.start_date > p_eam_res_inst_rec.completion_date then
134 x_return_status := fnd_api.g_ret_sts_error;
135 fnd_message.set_name('EAM','EAM_WO_RS_IN_DT_ERR');
136 return;
137 end if;
138
139 INSERT INTO WIP_OP_RESOURCE_INSTANCES
140 ( wip_entity_id
141 , organization_id
142 , operation_seq_num
143 , resource_seq_num
144 , instance_id
145 , serial_number
146 , start_date
147 , completion_date
148 , batch_id
149 , last_update_date
150 , last_updated_by
151 , creation_date
152 , created_by
153 , last_update_login
154 )
155 VALUES
156 ( p_eam_res_inst_rec.wip_entity_id
157 , p_eam_res_inst_rec.organization_id
158 , p_eam_res_inst_rec.operation_seq_num
159 , p_eam_res_inst_rec.resource_seq_num
160 , p_eam_res_inst_rec.instance_id
161 , p_eam_res_inst_rec.serial_number
162 , p_eam_res_inst_rec.start_date
163 , p_eam_res_inst_rec.completion_date
164 , p_eam_res_inst_rec.top_level_batch_id
165 , SYSDATE
166 , FND_GLOBAL.user_id
167 , SYSDATE
168 , FND_GLOBAL.user_id
169 , FND_GLOBAL.login_id
170 );
171
172
173 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug ('Creating new Resource Instances') ; END IF;
174
175 x_return_status := FND_API.G_RET_STS_SUCCESS;
176
177 EXCEPTION
178 WHEN OTHERS THEN
179 EAM_ERROR_MESSAGE_PVT.Add_Error_Token
180 ( p_message_name => NULL
181 , p_message_text => G_PKG_NAME ||' :Inserting Record ' || SQLERRM
182 , x_mesg_token_Tbl => x_mesg_token_tbl
183 );
184
185 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
186
187 END Insert_Row;
188
189 /********************************************************************
190 * Procedure : Update_Row
191 * Parameters IN : Resource Instances column record
192 * Parameters OUT NOCOPY: Message Token Table
193 * Return Status
194 * Purpose : Procedure will perfrom an Update on the
195 * wip_op_resource_instances table.
196 *********************************************************************/
197
198 PROCEDURE Update_Row
199 ( p_eam_res_inst_rec IN EAM_PROCESS_WO_PUB.eam_res_inst_rec_type
200 , x_mesg_token_Tbl OUT NOCOPY EAM_ERROR_MESSAGE_PVT.Mesg_Token_Tbl_Type
201 , x_return_Status OUT NOCOPY VARCHAR2
202 )
203 IS
204 BEGIN
205
206 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Updating Resource Instance: '|| p_eam_res_inst_rec.instance_id); END IF;
207
208 -- bug no 3444091
209 if p_eam_res_inst_rec.start_date > p_eam_res_inst_rec.completion_date then
210 x_return_status := fnd_api.g_ret_sts_error;
211 fnd_message.set_name('EAM','EAM_WO_RS_IN_DT_ERR');
212 return;
213 end if;
214
215 UPDATE WIP_OP_RESOURCE_INSTANCES
216 SET serial_number = p_eam_res_inst_rec.serial_number
217 , start_date = p_eam_res_inst_rec.start_date
218 , completion_date = p_eam_res_inst_rec.completion_date
219 , batch_id = p_eam_res_inst_rec.top_level_batch_id
220 , last_update_date = SYSDATE
221 , last_updated_by = FND_GLOBAL.user_id
222 , last_update_login = FND_GLOBAL.login_id
223 WHERE wip_entity_id = p_eam_res_inst_rec.wip_entity_id
224 AND organization_id = p_eam_res_inst_rec.organization_id
225 AND operation_seq_num = p_eam_res_inst_rec.operation_seq_num
226 AND resource_seq_num = p_eam_res_inst_rec.resource_seq_num
227 AND instance_id = p_eam_res_inst_rec.instance_id
228 AND (serial_number IS NULL OR (serial_number = p_eam_res_inst_rec.serial_number));
229
230
231 x_return_status := FND_API.G_RET_STS_SUCCESS;
232
233 END Update_Row;
234
235 /********************************************************************
236 * Procedure : Delete_Row
237 * Parameters IN : Resource Instances column record
238 * Parameters OUT NOCOPY: Message Token Table
239 * Return Status
240 * Purpose : Procedure will perfrom an Update on the
241 * wip_op_resource_instances table.
242 *********************************************************************/
243
244 PROCEDURE Delete_Row
245 ( p_eam_res_inst_rec IN EAM_PROCESS_WO_PUB.eam_res_inst_rec_type
246 , x_mesg_token_Tbl OUT NOCOPY EAM_ERROR_MESSAGE_PVT.Mesg_Token_Tbl_Type
247 , x_return_Status OUT NOCOPY VARCHAR2
248 )
249 IS
250 BEGIN
251
252 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Deleting Resource Instance: '|| p_eam_res_inst_rec.instance_id); END IF;
253
254 DELETE FROM WIP_OP_RESOURCE_INSTANCES
255 WHERE wip_entity_id = p_eam_res_inst_rec.wip_entity_id
256 AND organization_id = p_eam_res_inst_rec.organization_id
257 AND operation_seq_num = p_eam_res_inst_rec.operation_seq_num
258 AND resource_seq_num = p_eam_res_inst_rec.resource_seq_num
259 AND instance_id = p_eam_res_inst_rec.instance_id
260 AND (serial_number IS NULL OR (serial_number=p_eam_res_inst_rec.serial_number));
261
262 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Deleting Resource Instance Usages: '|| p_eam_res_inst_rec.instance_id); END IF;
263
264 IF p_eam_res_inst_rec.serial_number is NULL THEN
265 DELETE FROM WIP_OPERATION_RESOURCE_USAGE
266 WHERE wip_entity_id = p_eam_res_inst_rec.wip_entity_id
267 AND organization_id = p_eam_res_inst_rec.organization_id
268 AND operation_seq_num = p_eam_res_inst_rec.operation_seq_num
269 AND resource_seq_num = p_eam_res_inst_rec.resource_seq_num
270 AND instance_id = p_eam_res_inst_rec.instance_id;
271 ELSE
272 DELETE FROM WIP_OPERATION_RESOURCE_USAGE
273 WHERE wip_entity_id = p_eam_res_inst_rec.wip_entity_id
274 AND organization_id = p_eam_res_inst_rec.organization_id
275 AND operation_seq_num = p_eam_res_inst_rec.operation_seq_num
276 AND resource_seq_num = p_eam_res_inst_rec.resource_seq_num
277 AND instance_id = p_eam_res_inst_rec.instance_id
278 AND serial_number = p_eam_res_inst_rec.serial_number;
279 END IF;
280
281
282 x_return_status := FND_API.G_RET_STS_SUCCESS;
283
284 END Delete_Row;
285
286
287 /*********************************************************************
288 * Procedure : Perform_Writes
289 * Parameters IN : Resource Instances Column Record
290 * Parameters OUT NOCOPY: Messgae Token Table
291 * Return Status
292 * Purpose : This is the only procedure that the user will have
293 * access to when he/she needs to perform any kind of
294 * writes to the wip_operations.
295 *********************************************************************/
296
297 PROCEDURE Perform_Writes
298 ( p_eam_res_inst_rec IN EAM_PROCESS_WO_PUB.eam_res_inst_rec_type
299 , x_mesg_token_tbl OUT NOCOPY EAM_ERROR_MESSAGE_PVT.Mesg_Token_Tbl_Type
300 , x_return_status OUT NOCOPY VARCHAR2
301 )
302 IS
303 l_Mesg_Token_tbl EAM_ERROR_MESSAGE_PVT.Mesg_Token_Tbl_Type;
304 l_return_status VARCHAR2(1):= FND_API.G_RET_STS_SUCCESS;
305 BEGIN
306
307 IF p_eam_res_inst_rec.transaction_type = EAM_PROCESS_WO_PVT.G_OPR_CREATE
308 THEN
309 Insert_Row
310 ( p_eam_res_inst_rec => p_eam_res_inst_rec
311 , x_mesg_token_Tbl => l_mesg_token_tbl
312 , x_return_Status => l_return_status
313 );
314 ELSIF p_eam_res_inst_rec.transaction_type = EAM_PROCESS_WO_PVT.G_OPR_UPDATE
315 THEN
316 Update_Row
317 ( p_eam_res_inst_rec => p_eam_res_inst_rec
318 , x_mesg_token_Tbl => l_mesg_token_tbl
319 , x_return_Status => l_return_status
320 );
321 ELSIF p_eam_res_inst_rec.transaction_type = EAM_PROCESS_WO_PVT.G_OPR_DELETE
322 THEN
323 Delete_Row
324 ( p_eam_res_inst_rec => p_eam_res_inst_rec
325 , x_mesg_token_Tbl => l_mesg_token_tbl
326 , x_return_Status => l_return_status
327 );
328
329 END IF;
330
331 x_return_status := l_return_status;
332 x_mesg_token_tbl := l_mesg_token_tbl;
333
334 END Perform_Writes;
335
336 END EAM_RES_INST_UTILITY_PVT;