[Home] [Help]
PACKAGE BODY: APPS.EAM_SUB_RESOURCE_UTILITY_PVT
Source
1 PACKAGE BODY EAM_SUB_RESOURCE_UTILITY_PVT AS
2 /* $Header: EAMVSRUB.pls 115.4 2004/02/18 12:48:19 mmaduska noship $ */
3 /***************************************************************************
4 --
5 -- Copyright (c) 2002 Oracle Corporation, Redwood Shores, CA, USA
6 -- All rights reserved.
7 --
8 -- FILENAME
9 --
10 -- EAMVSRUB.pls
11 --
12 -- DESCRIPTION
13 --
14 -- Body of package EAM_SUB_RESOURCE_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_SUB_RESOURCE_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 * resource_id
32 * Parameters OUT NOCOPY: Sub Resource 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 , x_eam_sub_res_rec OUT NOCOPY EAM_PROCESS_WO_PUB.eam_sub_res_rec_type
45 , x_Return_status OUT NOCOPY VARCHAR2
46 )
47 IS
48 l_eam_sub_res_rec EAM_PROCESS_WO_PUB.eam_sub_res_rec_type;
49 l_return_status VARCHAR2(1):= FND_API.G_RET_STS_SUCCESS;
50 l_dummy VARCHAR2(10);
51 BEGIN
52
53 SELECT
54 resource_id
55 , uom_code
56 , basis_type
57 , usage_rate_or_amount
58 , activity_id
59 , scheduled_flag
60 , assigned_units
61 , autocharge_type
62 , standard_rate_flag
63 , applied_resource_units
64 , applied_resource_value
65 , start_date
66 , completion_date
67 , schedule_seq_num
68 , substitute_group_num
69 , replacement_group_num
70 , attribute_category
71 , attribute1
72 , attribute2
73 , attribute3
74 , attribute4
75 , attribute5
76 , attribute6
77 , attribute7
78 , attribute8
79 , attribute9
80 , attribute10
81 , attribute11
82 , attribute12
83 , attribute13
84 , attribute14
85 , attribute15
86 , department_id
87 -- , last_update_date
88 -- , last_updated_by
89 -- , creation_date
90 -- , created_by
91 -- , last_update_login
92 -- , request_id
93 -- , program_application_id
94 -- , program_id
95 -- , program_update_date
96 INTO
97 l_eam_sub_res_rec.resource_id
98 , l_eam_sub_res_rec.uom_code
99 , l_eam_sub_res_rec.basis_type
100 , l_eam_sub_res_rec.usage_rate_or_amount
101 , l_eam_sub_res_rec.activity_id
102 , l_eam_sub_res_rec.scheduled_flag
103 , l_eam_sub_res_rec.assigned_units
104 , l_eam_sub_res_rec.autocharge_type
105 , l_eam_sub_res_rec.standard_rate_flag
106 , l_eam_sub_res_rec.applied_resource_units
107 , l_eam_sub_res_rec.applied_resource_value
108 , l_eam_sub_res_rec.start_date
109 , l_eam_sub_res_rec.completion_date
110 , l_eam_sub_res_rec.schedule_seq_num
111 , l_eam_sub_res_rec.substitute_group_num
112 , l_eam_sub_res_rec.replacement_group_num
113 , l_eam_sub_res_rec.attribute_category
114 , l_eam_sub_res_rec.attribute1
115 , l_eam_sub_res_rec.attribute2
116 , l_eam_sub_res_rec.attribute3
117 , l_eam_sub_res_rec.attribute4
118 , l_eam_sub_res_rec.attribute5
119 , l_eam_sub_res_rec.attribute6
120 , l_eam_sub_res_rec.attribute7
121 , l_eam_sub_res_rec.attribute8
122 , l_eam_sub_res_rec.attribute9
123 , l_eam_sub_res_rec.attribute10
124 , l_eam_sub_res_rec.attribute11
125 , l_eam_sub_res_rec.attribute12
126 , l_eam_sub_res_rec.attribute13
127 , l_eam_sub_res_rec.attribute14
128 , l_eam_sub_res_rec.attribute15
129 , l_eam_sub_res_rec.department_id
130 -- , l_eam_sub_res_rec.last_update_date
131 -- , l_eam_sub_res_rec.last_updated_by
132 -- , l_eam_sub_res_rec.creation_date
133 -- , l_eam_sub_res_rec.created_by
134 -- , l_eam_sub_res_rec.last_update_login
135 -- , l_eam_sub_res_rec.request_id
136 -- , l_eam_sub_res_rec.program_application_id
137 -- , l_eam_sub_res_rec.program_id
138 -- , l_eam_sub_res_rec.program_update_date
139 FROM WIP_SUB_OPERATION_RESOURCES
140 WHERE wip_entity_id = p_wip_entity_id
141 AND organization_id = p_organization_id
142 AND operation_seq_num = p_operation_seq_num
143 AND resource_seq_num = p_resource_seq_num;
144
145 x_return_status := EAM_PROCESS_WO_PVT.G_RECORD_FOUND;
146 x_eam_sub_res_rec := l_eam_sub_res_rec;
147
148 EXCEPTION
149 WHEN NO_DATA_FOUND THEN
150 x_return_status := EAM_PROCESS_WO_PVT.G_RECORD_NOT_FOUND;
151 x_eam_sub_res_rec := l_eam_sub_res_rec;
152
153 WHEN OTHERS THEN
154 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
155 x_eam_sub_res_rec := l_eam_sub_res_rec;
156
157 END Query_Row;
158
159
160 /********************************************************************
161 * Procedure : Insert_Row
162 * Parameters IN : Sub Resource column record
163 * Parameters OUT NOCOPY: Message Token Table
164 * Return Status
165 * Purpose : Procedure will perfrom an insert into the
166 * wip_sub_operation_resources table.
167 *********************************************************************/
168
169 PROCEDURE Insert_Row
170 ( p_eam_sub_res_rec IN EAM_PROCESS_WO_PUB.eam_sub_res_rec_type
171 , x_mesg_token_Tbl OUT NOCOPY EAM_ERROR_MESSAGE_PVT.Mesg_Token_Tbl_Type
172 , x_return_Status OUT NOCOPY VARCHAR2
173 )
174 IS
175 BEGIN
176
177 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Writing Sub Resource rec for ' || p_eam_sub_res_rec.resource_seq_num); END IF;
178
179 -- bug no 3444091
180 if p_eam_sub_res_rec.start_date > p_eam_sub_res_rec.completion_date then
181 x_return_status := fnd_api.g_ret_sts_error;
182 fnd_message.set_name('EAM','EAM_WO_SUBR_DT_ERR');
183 return;
184 end if;
185
186
187 INSERT INTO WIP_SUB_OPERATION_RESOURCES
188 ( wip_entity_id
189 , organization_id
190 , operation_seq_num
191 , resource_seq_num
192 , resource_id
193 , uom_code
194 , basis_type
195 , usage_rate_or_amount
196 , activity_id
197 , scheduled_flag
198 , assigned_units
199 , autocharge_type
200 , standard_rate_flag
201 , applied_resource_units
202 , applied_resource_value
203 , start_date
204 , completion_date
205 , schedule_seq_num
206 , substitute_group_num
207 , replacement_group_num
208 , attribute_category
209 , attribute1
210 , attribute2
211 , attribute3
212 , attribute4
213 , attribute5
214 , attribute6
215 , attribute7
216 , attribute8
217 , attribute9
218 , attribute10
219 , attribute11
220 , attribute12
221 , attribute13
222 , attribute14
223 , attribute15
224 , department_id
225 , last_update_date
226 , last_updated_by
227 , creation_date
228 , created_by
229 , last_update_login
230 , request_id
231 , program_application_id
232 , program_id
233 , program_update_date)
234 VALUES
235 ( p_eam_sub_res_rec.wip_entity_id
236 , p_eam_sub_res_rec.organization_id
237 , p_eam_sub_res_rec.operation_seq_num
238 , p_eam_sub_res_rec.resource_seq_num
239 , p_eam_sub_res_rec.resource_id
240 , p_eam_sub_res_rec.uom_code
241 , p_eam_sub_res_rec.basis_type
242 , p_eam_sub_res_rec.usage_rate_or_amount
243 , p_eam_sub_res_rec.activity_id
244 , p_eam_sub_res_rec.scheduled_flag
245 , p_eam_sub_res_rec.assigned_units
246 , p_eam_sub_res_rec.autocharge_type
247 , p_eam_sub_res_rec.standard_rate_flag
248 , p_eam_sub_res_rec.applied_resource_units
249 , p_eam_sub_res_rec.applied_resource_value
250 , p_eam_sub_res_rec.start_date
251 , p_eam_sub_res_rec.completion_date
252 , p_eam_sub_res_rec.schedule_seq_num
253 , p_eam_sub_res_rec.substitute_group_num
254 , p_eam_sub_res_rec.replacement_group_num
255 , p_eam_sub_res_rec.attribute_category
256 , p_eam_sub_res_rec.attribute1
257 , p_eam_sub_res_rec.attribute2
258 , p_eam_sub_res_rec.attribute3
259 , p_eam_sub_res_rec.attribute4
260 , p_eam_sub_res_rec.attribute5
261 , p_eam_sub_res_rec.attribute6
262 , p_eam_sub_res_rec.attribute7
263 , p_eam_sub_res_rec.attribute8
264 , p_eam_sub_res_rec.attribute9
265 , p_eam_sub_res_rec.attribute10
266 , p_eam_sub_res_rec.attribute11
267 , p_eam_sub_res_rec.attribute12
268 , p_eam_sub_res_rec.attribute13
269 , p_eam_sub_res_rec.attribute14
270 , p_eam_sub_res_rec.attribute15
271 , p_eam_sub_res_rec.department_id
272 , SYSDATE
273 , FND_GLOBAL.user_id
274 , SYSDATE
275 , FND_GLOBAL.user_id
276 , FND_GLOBAL.login_id
277 , p_eam_sub_res_rec.request_id
278 , p_eam_sub_res_rec.program_application_id
279 , p_eam_sub_res_rec.program_id
280 , SYSDATE);
281
282
283
284 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug ('Creating new Sub Resource') ; END IF;
285
286 x_return_status := FND_API.G_RET_STS_SUCCESS;
287
288 EXCEPTION
289 WHEN OTHERS THEN
290 EAM_ERROR_MESSAGE_PVT.Add_Error_Token
291 ( p_message_name => NULL
292 , p_message_text => G_PKG_NAME ||' :Inserting Record ' || SQLERRM
293 , x_mesg_token_Tbl => x_mesg_token_tbl
294 );
295
296 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
297
298 END Insert_Row;
299
300 /********************************************************************
301 * Procedure : Update_Row
302 * Parameters IN : Sub Resource column record
303 * Parameters OUT NOCOPY: Message Token Table
304 * Return Status
305 * Purpose : Procedure will perfrom an Update on the
306 * wip_sub_operation_resources table.
307 *********************************************************************/
308
309 PROCEDURE Update_Row
310 ( p_eam_sub_res_rec IN EAM_PROCESS_WO_PUB.eam_sub_res_rec_type
311 , x_mesg_token_Tbl OUT NOCOPY EAM_ERROR_MESSAGE_PVT.Mesg_Token_Tbl_Type
312 , x_return_Status OUT NOCOPY VARCHAR2
313 )
314 IS
315 BEGIN
316
317 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Updating Sub Resource: '|| p_eam_sub_res_rec.resource_seq_num); END IF;
318
319 -- bug no 3444091
320 if p_eam_sub_res_rec.start_date > p_eam_sub_res_rec.completion_date then
321 x_return_status := fnd_api.g_ret_sts_error;
322 fnd_message.set_name('EAM','EAM_WO_SUBR_DT_ERR');
323 return;
324 end if;
325
326 UPDATE WIP_SUB_OPERATION_RESOURCES
327 SET resource_id = p_eam_sub_res_rec.resource_id
328 , uom_code = p_eam_sub_res_rec.uom_code
329 , basis_type = p_eam_sub_res_rec.basis_type
330 , usage_rate_or_amount = p_eam_sub_res_rec.usage_rate_or_amount
331 , activity_id = p_eam_sub_res_rec.activity_id
332 , scheduled_flag = p_eam_sub_res_rec.scheduled_flag
333 , assigned_units = p_eam_sub_res_rec.assigned_units
334 , autocharge_type = p_eam_sub_res_rec.autocharge_type
335 , standard_rate_flag = p_eam_sub_res_rec.standard_rate_flag
336 , applied_resource_units = p_eam_sub_res_rec.applied_resource_units
337 , applied_resource_value = p_eam_sub_res_rec.applied_resource_value
338 , start_date = p_eam_sub_res_rec.start_date
339 , completion_date = p_eam_sub_res_rec.completion_date
340 , schedule_seq_num = p_eam_sub_res_rec.schedule_seq_num
341 , substitute_group_num = p_eam_sub_res_rec.substitute_group_num
342 , replacement_group_num = p_eam_sub_res_rec.replacement_group_num
343 , attribute_category = p_eam_sub_res_rec.attribute_category
344 , attribute1 = p_eam_sub_res_rec.attribute1
345 , attribute2 = p_eam_sub_res_rec.attribute2
346 , attribute3 = p_eam_sub_res_rec.attribute3
347 , attribute4 = p_eam_sub_res_rec.attribute4
348 , attribute5 = p_eam_sub_res_rec.attribute5
349 , attribute6 = p_eam_sub_res_rec.attribute6
350 , attribute7 = p_eam_sub_res_rec.attribute7
351 , attribute8 = p_eam_sub_res_rec.attribute8
352 , attribute9 = p_eam_sub_res_rec.attribute9
353 , attribute10 = p_eam_sub_res_rec.attribute10
354 , attribute11 = p_eam_sub_res_rec.attribute11
355 , attribute12 = p_eam_sub_res_rec.attribute12
356 , attribute13 = p_eam_sub_res_rec.attribute13
357 , attribute14 = p_eam_sub_res_rec.attribute14
358 , attribute15 = p_eam_sub_res_rec.attribute15
359 , last_update_date = SYSDATE
360 , last_updated_by = FND_GLOBAL.user_id
361 , last_update_login = FND_GLOBAL.login_id
362 , request_id = p_eam_sub_res_rec.request_id
363 , program_application_id = p_eam_sub_res_rec.program_application_id
364 , program_id = p_eam_sub_res_rec.program_id
365 , program_update_date = SYSDATE
366 WHERE wip_entity_id = p_eam_sub_res_rec.wip_entity_id
367 AND organization_id = p_eam_sub_res_rec.organization_id
368 AND operation_seq_num = p_eam_sub_res_rec.operation_seq_num
369 AND resource_seq_num = p_eam_sub_res_rec.resource_seq_num;
370
371 x_return_status := FND_API.G_RET_STS_SUCCESS;
372
373 END Update_Row;
374
375 /********************************************************************
376 * Procedure : Delete_Row
377 * Parameters IN : Sub Resource column record
378 * Parameters OUT NOCOPY: Message Token Table
379 * Return Status
380 * Purpose : Procedure will perfrom an Update on the
381 * wip_sub_operation_resources table.
382 *********************************************************************/
383
384 PROCEDURE Delete_Row
385 ( p_eam_sub_res_rec IN EAM_PROCESS_WO_PUB.eam_sub_res_rec_type
386 , x_mesg_token_Tbl OUT NOCOPY EAM_ERROR_MESSAGE_PVT.Mesg_Token_Tbl_Type
387 , x_return_Status OUT NOCOPY VARCHAR2
388 )
389 IS
390 l_Mesg_Token_tbl EAM_ERROR_MESSAGE_PVT.Mesg_Token_Tbl_Type;
391 l_return_status VARCHAR2(1):= FND_API.G_RET_STS_SUCCESS;
392 BEGIN
393
394 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug ('Deleting Sub Resource Usage') ; END IF;
395
396 EAM_RES_USAGE_UTILITY_PVT.Delete_Usage
397 ( p_wip_entity_id => p_eam_sub_res_rec.wip_entity_id
398 , p_organization_id => p_eam_sub_res_rec.organization_id
399 , p_operation_seq_num => p_eam_sub_res_rec.operation_seq_num
400 , p_resource_seq_num => p_eam_sub_res_rec.resource_seq_num
401 , x_mesg_token_Tbl => l_mesg_token_Tbl
402 , x_return_Status => l_return_Status
403 );
404
405 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug ('Deleting Sub resources') ; END IF;
406
407 DELETE FROM WIP_SUB_OPERATION_RESOURCES
408 WHERE wip_entity_id = p_eam_sub_res_rec.wip_entity_id
409 AND organization_id = p_eam_sub_res_rec.organization_id
410 AND operation_seq_num = p_eam_sub_res_rec.operation_seq_num
411 AND resource_seq_num = p_eam_sub_res_rec.resource_seq_num;
412
413 x_return_status := l_return_status;
414 x_mesg_token_tbl := l_mesg_token_tbl;
415 -- x_return_status := FND_API.G_RET_STS_SUCCESS;
416
417 END Delete_Row;
418
419
420 /*********************************************************************
421 * Procedure : Perform_Writes
422 * Parameters IN : Sub Resource Column Record
423 * Parameters OUT NOCOPY: Messgae Token Table
424 * Return Status
425 * Purpose : This is the only procedure that the user will have
426 * access to when he/she needs to perform any kind of
427 * writes to the wip_operations.
428 *********************************************************************/
429
430 PROCEDURE Perform_Writes
431 ( p_eam_sub_res_rec IN EAM_PROCESS_WO_PUB.eam_sub_res_rec_type
432 , x_mesg_token_tbl OUT NOCOPY EAM_ERROR_MESSAGE_PVT.Mesg_Token_Tbl_Type
433 , x_return_status OUT NOCOPY VARCHAR2
434 )
435 IS
436 l_Mesg_Token_tbl EAM_ERROR_MESSAGE_PVT.Mesg_Token_Tbl_Type;
437 l_return_status VARCHAR2(1):= FND_API.G_RET_STS_SUCCESS;
438 BEGIN
439
440 IF p_eam_sub_res_rec.transaction_type = EAM_PROCESS_WO_PVT.G_OPR_CREATE
441 THEN
442 Insert_Row
443 ( p_eam_sub_res_rec => p_eam_sub_res_rec
444 , x_mesg_token_Tbl => l_mesg_token_tbl
445 , x_return_Status => l_return_status
446 );
447 ELSIF p_eam_sub_res_rec.transaction_type = EAM_PROCESS_WO_PVT.G_OPR_UPDATE
448 THEN
449 Update_Row
450 ( p_eam_sub_res_rec => p_eam_sub_res_rec
451 , x_mesg_token_Tbl => l_mesg_token_tbl
452 , x_return_Status => l_return_status
453 );
454 ELSIF p_eam_sub_res_rec.transaction_type = EAM_PROCESS_WO_PVT.G_OPR_DELETE
455 THEN
456 Delete_Row
457 ( p_eam_sub_res_rec => p_eam_sub_res_rec
458 , x_mesg_token_Tbl => l_mesg_token_tbl
459 , x_return_Status => l_return_status
460 );
461
462 END IF;
463
464 x_return_status := l_return_status;
465 x_mesg_token_tbl := l_mesg_token_tbl;
466
467 END Perform_Writes;
468
469 END EAM_SUB_RESOURCE_UTILITY_PVT;