[Home] [Help]
PACKAGE BODY: APPS.AHL_VWP_COST_PVT
Source
1 PACKAGE BODY AHL_VWP_COST_PVT AS
5 -- PURPOSE
2 /* $Header: AHLVCSTB.pls 120.6.12020000.2 2012/12/14 07:48:35 shnatu ship $ */
3 -----------------------------------------------------------------------
4 --
6 -- This package specification is a Private API for managing
7 -- Planning --> Visit Work Package --> Visit or MR or Tasks COSTING
8 -- which involves integration of Complex Maintainance, Repair
9 -- and Overhauling (CMRO) with COST MANAGEMENT related
10 -- procedures in Complex Maintainance, Repair and Overhauling(CMRO)
11 --
12 --
13 -- Calculate_Visit_Cost -- Calculate_MR_Cost
14 -- Calculate_Task_Cost -- Get_WO_Cost
15 -- Push_MR_Cost_Hierarchy -- Rollup_MR_Cost_Hierarchy
16 -- Calculate_WO_Cost -- Estimate_WO_Cost
17 -- Get_Profit_or_Loss -- Insert_Cst_Wo_Hierarchy
18 -- Create_Wo_Cost_Structure -- Create_Wo_Dependencies
19 --
20 --
21 -- NOTES
22 --
23 --
24 -- HISTORY
25 -- 28-AUG-2003 SHBHANDA 11.5.10. VWP-Costing Enhancements
26 -- 19-SEP-2003 SHBHANDA Incorporated APIs
27 -- Insert_Cst_Wo_Hierarchy -- Srini
28 -- Create_Wo_Cost_Structure -- Srini
29 -- Create_Wo_Dependencies -- ShivaK
30 --
31 -- 24-SEP-2003 SHBHANDA Made call for CST_eamCost_PUB.Compute_Job_Estimate API
32 -- in Rollup_MR_Cost_Hierarchy API
33 --
34 -- 08-OCT-2003 SHBHANDA Made call for CST_eamCost_PUB.Delete_eamPerBal API
35 -- prior to calling the Compute_Job_Estimate API
36 -- in Rollup_MR_Cost_Hierarchy API
37 --
38 -- 14-OCT-2003 SHBHANDA Incorporated Estimate_WO_Cost to be called from
39 -- Estimate_Visit/MR/Task_Cost API.
40 -- Also made changes to Calculated_WO_Cost
41 -- in Rollup_MR_Cost_Hierarchy API
42 --
43 -- 07-NOV-2007 RBHAVSAR Added PROCEDURE level logs when entering and exiting a procedure.
44 -- Returned the status before returning from the procedure.
45 -- Replaced all fnd_log.level_procedure with STATEMENT
46 -- level logs and added more STATEMENT level logs at
47 -- key decision points. Removed some tabs in the code.
48 -----------------------------------------------------------------
49
50
51 -----------------------------------------------------------------
52 -- Define Global CONSTANTS --
53 -----------------------------------------------------------------
54 -- Package/App Name
55 G_PKG_NAME CONSTANT VARCHAR(30) := 'AHL_VWP_COST_PVT';
56 G_APP_NAME CONSTANT VARCHAR2(3) := 'AHL';
57 G_DEBUG VARCHAR2(1) := AHL_DEBUG_PUB.is_log_enabled;
58 ------------------------------------
59 -- Common constants and variables --
60 ------------------------------------
61 l_log_current_level NUMBER := fnd_log.g_current_runtime_level;
62 l_log_statement NUMBER := fnd_log.level_statement;
63 l_log_procedure NUMBER := fnd_log.level_procedure;
64 l_log_error NUMBER := fnd_log.level_error;
65 l_log_unexpected NUMBER := fnd_log.level_unexpected;
66 ----------------------------------------------------------------------
67 -- START: Defining procedures BODY --
68 ----------------------------------------------------------------------
69
70
71 ---------------------------------------------------------------------------------
72 -- Procedure to create Workorder Cost Structure from visit Cost Hierarchy --
73 ---------------------------------------------------------------------------------
74 PROCEDURE Create_Wo_Cost_Structure (
75 p_api_version IN NUMBER,
76 p_init_msg_list IN VARCHAR2 := Fnd_Api.G_FALSE,
77 p_commit IN VARCHAR2 := Fnd_Api.G_FALSE,
78 p_validation_level IN NUMBER := Fnd_Api.G_VALID_LEVEL_FULL,
79 p_visit_id IN NUMBER,
80 x_cost_session_id OUT NOCOPY NUMBER,
81 x_return_status OUT NOCOPY VARCHAR2,
82 x_msg_count OUT NOCOPY NUMBER,
83 x_msg_data OUT NOCOPY VARCHAR2)
84 IS
85
86 -- Get master workorder details
87 CURSOR c_master_wo_cur (C_VISIT_ID IN NUMBER)
88 IS
89 SELECT workorder_id,wip_entity_id, visit_id
90 FROM ahl_workorders
91 WHERE visit_id = C_VISIT_ID AND VISIT_TASK_ID IS NULL
92 AND master_workorder_flag = 'Y'
93 AND STATUS_CODE NOT IN ('22','7');
94
95 --Get child tasks details
96 CURSOR c_child_tasks_cur (C_VISIT_ID IN NUMBER)
97 IS
98 SELECT visit_task_id,cost_parent_id,level,
99 task_type_code,originating_task_id
100 FROM ahl_visit_tasks_b
101 WHERE visit_id = C_VISIT_ID
102 AND NVL(status_code,'Y') <> 'DELETED'
103 START WITH cost_parent_id IS NULL
104 -- SKPATHAK :: Bug 13574991 :: 16-FEB-2012 :: FPed all fixes on R121 codeline
105 --SKPATHAK :: 18-APR-2011 :: Bug 12339739 :: Added the below condition to start with clause so as to improve performance
106 AND visit_id = C_VISIT_ID
107 CONNECT BY PRIOR visit_task_id = cost_parent_id
108 ORDER BY LEVEL;
109
110 -- yazhou 27-Jun-2006 starts
111 -- fix for bug#5377347, to include cost of the canceled workorder (7-Cancelled)
112 -- Since one task may map to multiple canceled workorders, query for the latest one
113
114 -- Get child workorder details
115 CURSOR c_child_wo_cur (C_VISIT_TASK_ID IN NUMBER)
119 WHERE visit_task_id = C_VISIT_TASK_ID
116 IS
117 SELECT workorder_id,wip_entity_id,visit_task_id
118 FROM ahl_workorders
120 -- AND STATUS_CODE NOT IN ('22','7');
121 AND STATUS_CODE <>'22'
122 ORDER BY LAST_UPDATE_DATE DESC;
123
124 -- yazhou 27-Jun-2006 ends
125
126
127 l_api_name CONSTANT VARCHAR2(30) := 'Create_Wo_Cost_Structure';
128 L_DEBUG_KEY CONSTANT VARCHAR2(100) := 'ahl.plsql.' || G_PKG_NAME || '.' || L_API_NAME;
129
130 l_msg_data VARCHAR2(2000);
131 l_return_status VARCHAR2(1);
132
133 l_msg_count NUMBER;
134 l_child_entity NUMBER;
135 l_child_id NUMBER;
136 l_parent_entity NUMBER;
137 l_parent_id NUMBER;
138 l_session_id NUMBER;
139 l_application_id NUMBER;
140
141 l_master_wo_rec c_master_wo_cur%ROWTYPE;
142 l_child_tasks_rec c_child_tasks_cur%ROWTYPE;
143 l_child_wo_rec c_child_wo_cur%ROWTYPE;
144 l_cst_job_tbl cst_job_tbl;
145 i number := 0;
146
147 BEGIN
148
149 IF (l_log_procedure >= l_log_current_level) THEN
150 fnd_log.string(l_log_procedure,
151 L_DEBUG_KEY ||'.begin',
152 'At the start of PL SQL procedure. Visit id = ' || p_visit_id );
153 END IF;
154
155 -- Standard start of API savepoint
156 SAVEPOINT Create_Wo_Cost_Structure;
157
158 -- Initialize message list if p_init_msg_list is set to TRUE
159 IF FND_API.To_Boolean( p_init_msg_list) THEN
160 FND_MSG_PUB.Initialize;
161 END IF;
162
163 -- Initialize API return status to success
164 x_return_status := FND_API.G_RET_STS_SUCCESS;
165
166 --Get progarm application id
167 l_application_id := FND_GLOBAL.prog_appl_id;
168
169 -- Get master workorder
170 OPEN c_master_wo_cur(p_visit_id);
171 FETCH c_master_wo_cur INTO l_master_wo_rec;
172 IF c_master_wo_cur%FOUND THEN
173
174 l_cst_job_tbl(i).object_id := l_master_wo_rec.wip_entity_id;
175 l_cst_job_tbl(i).object_type := 2;
176 l_cst_job_tbl(i).parent_object_id := l_master_wo_rec.wip_entity_id;
177 l_cst_job_tbl(i).parent_object_type := 2;
178 l_cst_job_tbl(i).level_num := 0;
179 l_cst_job_tbl(i).program_application_id := l_application_id;
180
181 END IF;
182 i := i + 1;
183 CLOSE c_master_wo_cur;
184
185 IF (l_log_statement >= l_log_current_level) THEN
186 fnd_log.string(l_log_statement,
187 L_DEBUG_KEY,
188 'Master Wo Object Id : ' || l_master_wo_rec.wip_entity_id ||
189 'Master Wo Parent Object Id : ' || l_master_wo_rec.wip_entity_id ||
190 'Application Id : ' || l_application_id);
191 END IF;
192
193 -- Process child work orders
194 OPEN c_child_tasks_cur(p_visit_id);
195
196 LOOP
197 FETCH c_child_tasks_cur INTO l_child_tasks_rec;
198 EXIT WHEN c_child_tasks_cur%NOTFOUND;
199 --Get associated wip entity id for the task
200 OPEN c_child_wo_cur(l_child_tasks_rec.visit_task_id);
201 FETCH c_child_wo_cur INTO l_child_wo_rec;
202 IF c_child_wo_cur%FOUND THEN
203 l_child_entity := 2;
204 l_child_id := l_child_wo_rec.wip_entity_id;
205
206 ELSE
207 l_child_entity := 1;
208 l_child_id := l_child_tasks_rec.visit_task_id;
209
210 END IF;
211 CLOSE c_child_wo_cur;
212
213 -- Find parent object id and parent type
214 IF l_child_tasks_rec.cost_parent_id IS NOT NULL THEN
218 l_parent_entity := 2;
215 OPEN c_child_wo_cur(l_child_tasks_rec.cost_parent_id);
216 FETCH c_child_wo_cur INTO l_child_wo_rec;
217 IF c_child_wo_cur%FOUND THEN
219 l_parent_id := l_child_wo_rec.wip_entity_id;
220
221 ELSE
222 l_parent_entity := 1;
223 l_parent_id := l_child_tasks_rec.cost_parent_id;--PRAKKUM :: 02/11/2011 :: Fixed costing summary task issue :: Bug# Internal
224
225 END IF;
226 CLOSE c_child_wo_cur;
227 -- if Cost parent is null, get master workorder
228 ELSE
229 l_parent_entity := 2;
230 l_parent_id := l_master_wo_rec.wip_entity_id;
231 END IF; --Cost parent is not null
232
233 -- Assign processed values
234 l_cst_job_tbl(i).object_id := l_child_id;
235 l_cst_job_tbl(i).object_type := l_child_entity;
236 l_cst_job_tbl(i).parent_object_id := l_parent_id;
237 l_cst_job_tbl(i).parent_object_type := l_parent_entity;
238 l_cst_job_tbl(i).level_num := l_child_tasks_rec.level;
239 l_cst_job_tbl(i).program_application_id := l_application_id;
240
241 IF (l_log_statement >= l_log_current_level) THEN
242 fnd_log.string(l_log_statement,
243 L_DEBUG_KEY,
244 'Visit Task Id : ' || l_child_tasks_rec.visit_task_id ||
245 'Associated Child Object Id : ' || l_child_id ||
246 'Associated Child Object Type : ' || l_child_entity ||
247 'Level No : ' || l_child_tasks_rec.level ||
248 'Associated Parent Object Id : ' || l_parent_id ||
249 'Associated Parent Object Type : ' || l_parent_entity );
250 END IF;
251
252 i := i + 1;
253
254 --
255 END LOOP;
256 CLOSE c_child_tasks_cur;
257
258 IF (l_log_statement >= l_log_current_level) THEN
259 fnd_log.string(l_log_statement,
260 L_DEBUG_KEY,
261 'Before Calling Insert Cst Wo Hierarchy ' ||l_cst_job_tbl.count);
262 END IF;
263
264 -- Call Insert procedure to load into interface table
265 Insert_Cst_Wo_Hierarchy (
266 p_cst_job_tbl => l_cst_job_tbl,
267 p_commit => p_commit,
268 x_session_id => x_cost_session_id,
269 x_return_status => l_return_status);
270
271 IF (l_log_statement >= l_log_current_level) THEN
272 fnd_log.string(l_log_statement,
273 L_DEBUG_KEY,
274 'After Calling Insert cost workorder hierarchy, Return Status = ' || l_return_status ||
275 'Session id :' ||x_cost_session_id);
276 END IF;
277
278 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
279
280 IF (l_log_statement >= l_log_current_level) THEN
281 fnd_log.string(l_log_statement,
282 L_DEBUG_KEY,
283 'Errors from Insert cost workorder hierarchy' || x_msg_count);
284 END IF;
285
286 RAISE Fnd_Api.g_exc_error;
287 END IF;
288
289 IF (l_log_procedure >= l_log_current_level) THEN
290 fnd_log.string(l_log_procedure,
291 L_DEBUG_KEY ||'.end',
292 'At the end of PL SQL procedure. Return Status = ' || l_return_status);
293 END IF;
294
295 EXCEPTION
296 WHEN FND_API.G_EXC_ERROR THEN
297 x_return_status := FND_API.G_RET_STS_ERROR;
298 ROLLBACK TO Create_Wo_Cost_Structure;
299 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
300 p_data => x_msg_data,
301 p_encoded => fnd_api.g_false);
302
303
304 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
305 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
306 ROLLBACK TO Create_Wo_Cost_Structure;
307 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
308 p_data => x_msg_data,
309 p_encoded => fnd_api.g_false);
310
311
312 WHEN OTHERS THEN
313 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
314 ROLLBACK TO Create_Wo_Cost_Structure;
315 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
316 fnd_msg_pub.add_exc_msg(p_pkg_name => G_PKG_NAME,
317 p_procedure_name => 'Create_Wo_Cost_Structure',
318 p_error_text => SUBSTR(SQLERRM,1,500));
319 END IF;
320 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
321 p_data => x_msg_data,
322 p_encoded => fnd_api.g_false);
323 END Create_Wo_Cost_Structure;
324
325
326 ---------------------------------------------------------------------------------
327 -- Procedure to create Workorder Scheduling dependencies --
328 ---------------------------------------------------------------------------------
329 PROCEDURE Create_Wo_Dependencies
330 (
331 p_api_version IN NUMBER,
332 p_init_msg_list IN VARCHAR2 := Fnd_Api.G_FALSE,
336 x_MR_session_id OUT NOCOPY NUMBER,
333 p_commit IN VARCHAR2 := Fnd_Api.G_FALSE,
334 p_validation_level IN NUMBER := Fnd_Api.G_VALID_LEVEL_FULL,
335 p_visit_id IN NUMBER,
337 x_return_status OUT NOCOPY VARCHAR2,
338 x_msg_count OUT NOCOPY NUMBER,
339 x_msg_data OUT NOCOPY VARCHAR2
340 )
341 IS
342 l_api_name VARCHAR2(30) := 'Create_Wo_Dependencies';
343 L_DEBUG_KEY CONSTANT VARCHAR2(100) := 'ahl.plsql.' || G_PKG_NAME || '.' || L_API_NAME;
344
345 l_return_status VARCHAR2(1);
346
347 l_msg_count NUMBER;
348 l_visit_wip_entity_id NUMBER;
349 l_application_id NUMBER;
350 i NUMBER := 0;
351
352 l_cst_job_tbl cst_job_tbl;
353
354
355 -- yazhou 27-Jun-2006 starts
356 -- fix for bug#5377347, to include cost of the canceled workorder (7-Cancelled)
357
358 -- Get the Visit Workorder
359 CURSOR c_get_visit_wo( c_visit_id NUMBER)
360 IS
361 SELECT wip_entity_id
362 FROM AHL_WORKORDERS
363 WHERE visit_id = c_visit_id
364 AND visit_task_id IS NULL
365 AND STATUS_CODE NOT IN ('22','7')
366 AND master_workorder_flag = 'Y';
367
368
369 -- Since one task may map to multiple canceled workorders, query for the latest one
370
371 -- Get the Child Workorders for the Visit
372 CURSOR c_get_child_workorders( c_wip_entity_id NUMBER)
373 IS
374 SELECT parent_object_id,
375 child_object_id,
376 level
377 FROM WIP_SCHED_RELATIONSHIPS
378 WHERE parent_object_type_id = 1
379 AND child_object_type_id = 1
380 AND not exists (select 1 from ahl_workorders awo1
381 where awo1.wip_entity_id = parent_object_id
382 and ( awo1.status_code ='22'
383 OR (awo1.status_code ='7'
384 AND awo1.LAST_UPDATE_DATE <> (select MAX(LAST_UPDATE_DATE)
385 from ahl_workorders awo2
386 where visit_task_id = awo1.visit_task_id)))
387 )
388 AND not exists (select 1 from ahl_workorders awo1
389 where awo1.wip_entity_id = child_object_id
390 and ( awo1.status_code ='22'
391 OR (awo1.status_code ='7'
392 AND awo1.LAST_UPDATE_DATE <> (select MAX(LAST_UPDATE_DATE)
393 from ahl_workorders awo2
394 where visit_task_id = awo1.visit_task_id)))
395 )
396 START WITH parent_object_id = c_wip_entity_id
397 AND relationship_type = 1
398 CONNECT BY parent_object_id = PRIOR child_object_id
399 AND relationship_type = 1
400 ORDER BY level;
401
402 -- yazhou 27-Jun-2006 ends
403
404 BEGIN
405 IF (l_log_procedure >= l_log_current_level) THEN
406 fnd_log.string(l_log_procedure,
407 L_DEBUG_KEY ||'.begin',
408 'At the start of PL SQL procedure. Visit Id = ' || p_visit_id);
409 END IF;
410
411 -- Initialize API return status to success
412 x_return_status := FND_API.G_RET_STS_SUCCESS;
413
414 -- Standard Start of API savepoint
415 SAVEPOINT create_wo_dependencies_PVT;
416
417 l_application_id := FND_GLOBAL.prog_appl_id;
418
419 OPEN c_get_visit_wo( p_visit_id );
420 FETCH c_get_visit_wo
421 INTO l_visit_wip_entity_id;
422 CLOSE c_get_visit_wo;
423
424 -- Populate the Visit Record
425 l_cst_job_tbl(i).object_id := l_visit_wip_entity_id;
426 l_cst_job_tbl(i).object_type := 2;
427 l_cst_job_tbl(i).parent_object_id := l_visit_wip_entity_id;
428 l_cst_job_tbl(i).parent_object_type := 2;
429 l_cst_job_tbl(i).level_num := 0;
430 l_cst_job_tbl(i).program_application_id := l_application_id;
431
432 IF (l_log_statement >= l_log_current_level) THEN
433 fnd_log.string(l_log_statement,
434 L_DEBUG_KEY,
435 'Level-Parent Object Id - Child Object Id' || l_cst_job_tbl(i).level_num ||
436 '----' || l_cst_job_tbl(i).parent_object_id || '-----' || l_cst_job_tbl(i).object_id);
437 END IF;
438
439 -- Populate the Child Workorders for the Visit
440 FOR child_cur IN c_get_child_workorders( l_visit_wip_entity_id ) LOOP
441 i := i + 1;
442 l_cst_job_tbl(i).object_id := child_cur.child_object_id;
443 l_cst_job_tbl(i).object_type := 2;
444 l_cst_job_tbl(i).parent_object_id := child_cur.parent_object_id;
445 l_cst_job_tbl(i).parent_object_type := 2;
446 l_cst_job_tbl(i).level_num := child_cur.level;
447 l_cst_job_tbl(i).program_application_id := l_application_id;
448
449 IF (l_log_statement >= l_log_current_level) THEN
450 fnd_log.string(l_log_statement,
451 L_DEBUG_KEY,
452 'Level-Parent Object Id - Child Object Id' || l_cst_job_tbl(i).level_num || '----' || l_cst_job_tbl(i).parent_object_id || '-----' || l_cst_job_tbl(i).object_id);
453 END IF;
454
455 END LOOP;
456
457 IF (l_log_statement >= l_log_current_level) THEN
458 fnd_log.string(l_log_statement,
459 L_DEBUG_KEY,
460 'Before Calling Insert_Cst_Wo_Hierarchy');
461 END IF;
462
463 Insert_Cst_Wo_Hierarchy
464 (
465 p_cst_job_tbl => l_cst_job_tbl,
466 p_commit => FND_API.G_FALSE,
467 x_session_id => x_MR_session_id,
468 x_return_status => l_return_status
469 );
470
471 IF (l_log_statement >= l_log_current_level) THEN
472 fnd_log.string(l_log_statement,
473 L_DEBUG_KEY,
474 'After Calling Insert_Cst_Wo_Hierarchy, Return Status = ' || l_return_status);
475 END IF;
476
477 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
478 IF (l_log_statement >= l_log_current_level) THEN
479 fnd_log.string(l_log_statement,
480 L_DEBUG_KEY,
481 'Errors from Insert cost workorder hierarchy' || x_msg_count);
482 END IF;
483
484 x_return_status := Fnd_Api.G_RET_STS_ERROR;
485 RAISE Fnd_Api.g_exc_error;
486 END IF;
487
488 IF (l_log_procedure >= l_log_current_level) THEN
489 fnd_log.string(l_log_procedure,
490 L_DEBUG_KEY ||'.end',
491 'At the end of PL SQL procedure. Return Status = ' || x_return_status ||
492 ' x_session_id = ' || x_MR_session_id );
493 END IF;
494 EXCEPTION
495
496 WHEN FND_API.G_EXC_ERROR THEN
497 ROLLBACK TO create_wo_dependencies_PVT;
498 x_return_status := FND_API.G_RET_STS_ERROR;
499 FND_MSG_PUB.count_and_get
500 (
501 p_encoded => FND_API.G_FALSE,
502 p_count => x_msg_count,
503 p_data => x_msg_data
504 );
505
506 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
507 ROLLBACK TO create_wo_dependencies_PVT;
508 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
509 FND_MSG_PUB.count_and_get
510 (
511 p_encoded => FND_API.G_FALSE,
512 p_count => x_msg_count,
513 p_data => x_msg_data
514 );
515
516 WHEN OTHERS THEN
517 ROLLBACK TO create_wo_dependencies_PVT;
518 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
519 IF FND_MSG_PUB.check_msg_level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
520 THEN
521 FND_MSG_PUB.add_exc_msg
522 (
523 p_pkg_name => G_PKG_NAME,
524 p_procedure_name => l_api_name,
525 p_error_text => SUBSTRB(SQLERRM,1,240)
526 );
527 END IF;
528 FND_MSG_PUB.count_and_get
529 (
530 p_encoded => FND_API.G_FALSE,
531 p_count => x_msg_count,
532 p_data => x_msg_data
533 );
534
535 END Create_Wo_Dependencies;
536
537 ---------------------------------------------------------------------------------
538 -- Procedure to insert Visits cost hierarchy structure and MR Hierarchy structure
539 -- into Costing interface table CST_EAM_HIERARCHY_SNAPSHOT
540 ---------------------------------------------------------------------------------
541 PROCEDURE Insert_Cst_Wo_Hierarchy (
542 p_cst_job_tbl IN Cst_Job_Tbl,
543 p_commit IN VARCHAR2 := Fnd_Api.G_FALSE,
544 x_session_id OUT NOCOPY NUMBER,
545 x_return_status OUT NOCOPY VARCHAR2)
546 IS
547
548 l_api_name CONSTANT VARCHAR2(30) := 'Insert_Cst_Wo_Hierarchy';
549 L_DEBUG_KEY CONSTANT VARCHAR2(100) := 'ahl.plsql.' || G_PKG_NAME || '.' || L_API_NAME;
550
554 l_msg_count NUMBER;
551 l_return_status VARCHAR2(1);
552 l_msg_data VARCHAR2(2000);
553
555 l_group_id NUMBER;
556
557 l_cst_job_tbl cst_job_tbl := p_cst_job_tbl;
558
559 BEGIN
560
561 IF (l_log_procedure >= l_log_current_level) THEN
562 fnd_log.string(l_log_procedure,
563 L_DEBUG_KEY ||'.begin',
564 'At the start of PL SQL procedure.');
565 END IF;
566
567 -- Initialize API return status to success
568 x_return_status := FND_API.G_RET_STS_SUCCESS;
569
570 -- Get Cost Session Id
571 SELECT MTL_EAM_ASSET_ACTIVITIES_S.nextval INTO l_group_id
572 FROM DUAL;
573
574 -- Validation for sequence
575 IF l_group_id IS NULL THEN
576 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_CST_EAM_SEQ_NOT_EXIST');
577 FND_MSG_PUB.ADD;
578 IF (l_log_statement >= l_log_current_level) THEN
579 fnd_log.string(l_log_statement,
580 L_DEBUG_KEY,
581 'MTL_EAM_ASSET_ACTIVITIES_S Sequence not exists ');
582 END IF;
583 RAISE Fnd_Api.g_exc_error;
584 END IF;
585
586 -- loop through all the records
587 IF l_cst_job_tbl.COUNT> 0 THEN
588 FOR i IN l_cst_job_tbl.FIRST..l_cst_job_tbl.LAST
589 LOOP
590
591 IF (l_log_statement >= l_log_current_level) THEN
592 fnd_log.string(l_log_statement,
593 L_DEBUG_KEY,
594 'Call INSERT CST_EAM_HIERARCHY_SNAPSHOT ');
595 END IF;
596 INSERT INTO CST_EAM_HIERARCHY_SNAPSHOT(
597 GROUP_ID,
598 OBJECT_ID,
599 OBJECT_TYPE,
600 PARENT_OBJECT_ID,
601 PARENT_OBJECT_TYPE ,
602 LEVEL_NUM,
603 LAST_UPDATE_DATE,
604 LAST_UPDATED_BY,
605 CREATION_DATE,
606 CREATED_BY,
607 REQUEST_ID,
608 PROGRAM_APPLICATION_ID,
609 LAST_UPDATE_LOGIN)
610 VALUES
611 (
612 l_group_id,
613 l_cst_job_tbl(i).object_id,
614 l_cst_job_tbl(i).object_type,
615 l_cst_job_tbl(i).parent_object_id,
616 l_cst_job_tbl(i).parent_object_type,
617 l_cst_job_tbl(i).level_num,
618 sysdate,
619 fnd_global.user_id,
620 sysdate,
621 fnd_global.user_id,
622 null,
623 l_cst_job_tbl(i).program_application_id,
624 fnd_global.login_id
625 );
626
627 END LOOP;
628 END IF;
629
630 -- Assign Cost Session Id
631 x_session_id := l_group_id;
632
633 -- x_return_status := FND_API.G_RET_STS_SUCCESS;
634
635 IF (l_log_procedure >= l_log_current_level) THEN
636 fnd_log.string(l_log_procedure,
637 L_DEBUG_KEY ||'.end',
638 'At the end of PL SQL procedure. ' ||
639 'Value of Session ID : ' || x_session_id);
640 END IF;
641
642
643 END Insert_Cst_Wo_Hierarchy;
644
645 --------------------------------------------------------------------------
646 -- Procedure to calculate visit's actual and estimated cost --
647 --------------------------------------------------------------------------
648 PROCEDURE Calculate_Visit_Cost(
649 p_visit_id IN NUMBER,
650 p_Session_id IN NUMBER,
651 x_Actual_cost OUT NOCOPY NUMBER,
652 x_Estimated_cost OUT NOCOPY NUMBER,
653 x_return_status OUT NOCOPY VARCHAR2)
654 IS
655 -- Define local Variables
656 l_actual_cost NUMBER := 0;
657 l_estimated_cost NUMBER := 0;
658 l_wip_Id NUMBER;
659 l_count NUMBER;
660 l_OSP_cost NUMBER;
661
662 l_return_status VARCHAR2(1);
663
664 L_API_NAME CONSTANT VARCHAR2(30) := 'Calculate_Visit_Cost';
665 L_DEBUG_KEY CONSTANT VARCHAR2(100) := 'ahl.plsql.' || G_PKG_NAME || '.' || L_API_NAME;
666
667 -- Define local cursors
671 FROM AHL_WORKORDERS AWO
668 -- To find out WIP_Entity_Id for the visit
669 CURSOR c_job(x_id IN NUMBER) IS
670 SELECT AWO.WIP_ENTITY_ID
672 WHERE AWO.VISIT_ID = x_id
673 AND VISIT_TASK_ID IS NULL
674 AND AWO.STATUS_CODE NOT IN ('22','7')
675 AND AWO.MASTER_WORKORDER_FLAG = 'Y';
676
677 -- yazhou 27-Jun-2006 starts
678 -- fix for bug#5377347, to include cost of the canceled workorder (7-cancelled)
679 -- Since one task may map to multiple canceled workorders, query for the latest one
680
681 -- To find out to find out all task's Workorder_Id within visit except those,
682 -- which have task_type_code = 'Summary' and MR_id is Null i.e manually created Summary task
683 CURSOR c_WO_tasks (x_id IN NUMBER) IS
684 SELECT AVTB.VISIT_TASK_ID, AWV.WORKORDER_ID
685 FROM AHL_VISIT_TASKS_B AVTB, AHL_WORKORDERS AWV
686 WHERE AVTB.VISIT_TASK_ID = AWV.VISIT_TASK_ID
687 AND AVTB.VISIT_ID = x_id
688 AND NVL(AVTB.status_code, 'Y') <> NVL ('DELETED', 'X')
689 -- AND AWV.STATUS_CODE NOT IN ('22','7')
690 AND AWV.STATUS_CODE <> '22'
691 AND not (AVTB.MR_ID is Null and AVTB.task_type_code='SUMMARY')
692 ORDER BY AWV.LAST_UPDATE_DATE DESC;
693
694 -- yazhou 27-Jun-2006 ends
695
696 c_WO_tasks_rec c_WO_tasks%ROWTYPE;
697
698
699 -- To find out if jobs is an internal job or an OSP job
700 CURSOR c_OSP_Job (x_id IN NUMBER) IS
701 SELECT * FROM AHL_OSP_ORDER_LINES
702 WHERE WORKORDER_ID = x_id;
703 c_OSP_Job_rec c_OSP_Job%ROWTYPE;
704
705 BEGIN
706 ------------------------Initialize Body------------------------------------
707
708 IF (l_log_procedure >= l_log_current_level) THEN
709 fnd_log.string(l_log_procedure,
710 L_DEBUG_KEY ||'.begin',
711 'At the start of PL SQL procedure.' ||
712 'Visit ID : ' || p_visit_id || 'Session ID : ' || p_Session_id);
713 END IF;
714
715 -- Standard start of API savepoint
716 SAVEPOINT Calculate_Visit_Cost;
717
718 -- Initialize API return status to success
719 x_return_status := FND_API.G_RET_STS_SUCCESS;
720
721 ------------------------Start of API Body------------------------------------
722
723 -- make sure that visit id is present in the input
724
725 IF(p_visit_id IS NULL OR p_visit_id = FND_API.G_MISS_NUM) THEN
726
727 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_VWP_CST_INPUT_MISS');
728 FND_MSG_PUB.ADD;
729 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
730
731 END IF;
732
733
734 -- Cursor to find out Wip_Entity_Id for the visit
735 OPEN c_job(p_visit_id);
736 FETCH c_job INTO l_wip_Id;
737 CLOSE c_job;
738
739 -- Debug statements
740 IF (l_log_statement >= l_log_current_level) THEN
741 fnd_log.string(l_log_statement,
742 L_DEBUG_KEY,
743 'Before calling Get_WO_Cost API to find Cost' ||
744 'WIP ENTITY ID = ' || l_wip_Id );
745 END IF;
746
747 -- Call Get_WO_Cost to find Cost for the visit from Cost Mgnt Entity
748 Get_WO_Cost (
749 p_Session_Id => p_Session_id,
750 p_Id => l_wip_Id,
751 p_program_id => FND_GLOBAL.Prog_Appl_Id,
752 x_Actual_cost => l_Actual_cost,
756
753 x_Estimated_cost => l_Estimated_cost,
754 x_return_status => l_return_status);
755
757 -- Debug statements
758 IF (l_log_statement >= l_log_current_level) THEN
759 fnd_log.string(l_log_statement,
760 L_DEBUG_KEY,
761 'After calling Get_WO_Cost API to find Cost, Return Status = ' || l_return_status ||
762 'Actual Cost : ' || l_actual_cost ||
763 'Estimated Cost : ' || l_estimated_cost );
764 END IF;
765
766 -- Cursor to find all tasks under this current Visit
767 OPEN c_WO_tasks(p_visit_id);
768 LOOP
769 FETCH c_WO_tasks INTO c_WO_tasks_rec;
770 EXIT WHEN c_WO_tasks%NOTFOUND;
771
772 -- Cursor to find if any OSP jobs present for the tasks in Visit
773 OPEN c_OSP_Job(c_WO_tasks_rec.Workorder_Id);
774 FETCH c_OSP_Job INTO c_OSP_Job_rec;
775
776 IF c_OSP_Job%FOUND THEN
777 -- Debug statements
778 IF (l_log_statement >= l_log_current_level) THEN
779 fnd_log.string(l_log_statement,
780 L_DEBUG_KEY,
781 'Before calling Get_OSP_Cost API to find Cost for OSP Job' );
782 END IF;
783
784 AHL_OSP_COST_PVT.Get_OSP_Cost
785 (
786 x_return_status => l_return_status,
787 p_workorder_id => c_WO_tasks_rec.Workorder_Id,
788 x_osp_cost => l_osp_cost
789 );
790
791
792 l_actual_cost := l_actual_cost + l_OSP_cost;
793 l_estimated_cost := l_estimated_cost + l_OSP_cost;
794
795 -- Debug statements
796 IF (l_log_statement >= l_log_current_level) THEN
797 fnd_log.string(l_log_statement,
798 L_DEBUG_KEY,
799 'After calling Get_OSP_Cost API, Return Status = ' || l_return_status ||
800 'Actual Cost : ' || l_actual_cost ||
801 'Estimated Cost : ' || l_estimated_cost );
802 END IF;
803
804 END IF;
805 CLOSE c_OSP_Job;
806
807 END LOOP;
808 CLOSE c_WO_tasks;
809
810
811 x_Actual_cost := l_actual_cost;
812 x_Estimated_cost := l_Estimated_cost;
813
814 ------------------------End of API Body------------------------------------
815
816 --x_return_status := FND_API.G_RET_STS_SUCCESS;
817
818 IF (l_log_procedure >= l_log_current_level) THEN
819 fnd_log.string(l_log_procedure,
820 L_DEBUG_KEY ||'.end',
821 'At the end of PL SQL procedure. Return Status = ' || l_return_status ||
822 'Actual Cost : ' || l_actual_cost ||
823 'Estimated Cost : ' || l_estimated_cost );
824 END IF;
825
826 ------------------------Terminate API Body------------------------------------
827 END Calculate_Visit_Cost;
828
829
830 --------------------------------------------------------------------------
831 -- Procedure to calculate visit's MR actual and estimated cost --
832 --------------------------------------------------------------------------
833 PROCEDURE Calculate_MR_Cost(
834 p_visit_task_id IN NUMBER,
835 p_Session_id IN NUMBER,
836 x_Actual_cost OUT NOCOPY NUMBER,
837 x_Estimated_cost OUT NOCOPY NUMBER,
838 x_return_status OUT NOCOPY VARCHAR2 )
839 IS
840 -- Define local Variables
841 l_actual_cost NUMBER := 0;
842 l_estimated_cost NUMBER := 0;
843
844 l_wip_Id NUMBER;
845 l_count NUMBER;
846 l_OSP_cost NUMBER;
847
848 l_return_status VARCHAR2(1);
849
850 L_API_NAME CONSTANT VARCHAR2(30) := 'Calculate_MR_Cost';
851 L_DEBUG_KEY CONSTANT VARCHAR2(100) := 'ahl.plsql.' || G_PKG_NAME || '.' || L_API_NAME;
852
853 -- Define local Cursors
854 -- To find out viist's task details
855 CURSOR c_task(x_id IN NUMBER)IS
856 SELECT * FROM AHL_VISIT_TASKS_B
857 WHERE VISIT_TASK_ID = x_id;
858 c_task_rec c_task%ROWTYPE;
859
860 -- yazhou 27-Jun-2006 starts
861 -- fix for bug#5377347, to include cost of the canceled workorder (7-cancelled)
862 -- Since one task may map to multiple canceled workorders, query for the latest one
863
864 -- To find out WIP_Entity_Id for the MR task
865 CURSOR c_job(x_id IN NUMBER) IS
866 SELECT AWO.WIP_ENTITY_ID
867 FROM AHL_WORKORDERS AWO
868 WHERE AWO.VISIT_TASK_ID = x_id
869 -- AND AWO.STATUS_CODE NOT IN ('22','7')
870 AND AWO.STATUS_CODE <>'22'
871 AND AWO.MASTER_WORKORDER_FLAG = 'Y'
872 ORDER BY LAST_UPDATE_DATE DESC;
873
874 -- yazhou 27-Jun-2006 ends
875
876
877 -- To find out all tasks under the given MR
878 CURSOR c_MR_task(x_id IN NUMBER) IS
879 SELECT visit_task_id
880 FROM ahl_visit_tasks_b
881 WHERE mr_id is not null
882 START WITH visit_task_id = x_id
883 CONNECT BY PRIOR visit_task_id = originating_task_id;
884 c_MR_task_rec c_MR_task%ROWTYPE;
885
886 -- yazhou 27-Jun-2006 starts
887 -- fix for bug#5377347, to include cost of the canceled workorder (7-cancelled)
888 -- Since one task may map to multiple canceled workorders, query for the latest one
889
890 -- To find out to find out task's Workorder_Id
891 CURSOR c_WO_tasks (x_id IN NUMBER) IS
895 -- AND AWO.STATUS_CODE NOT IN ('22','7')
892 SELECT AWV.WORKORDER_ID
893 FROM AHL_WORKORDERS AWV
894 WHERE AWV.VISIT_TASK_ID = x_id
896 AND AWV.STATUS_CODE <>'22'
897 ORDER BY LAST_UPDATE_DATE DESC;
898
899 -- yazhou 27-Jun-2006 ends
900
901 c_WO_tasks_rec c_WO_tasks%ROWTYPE;
902
903
904 -- To find out if jobs is an internal job or an OSP job
905 CURSOR c_OSP_Job (x_id IN NUMBER) IS
906 SELECT * FROM AHL_OSP_ORDER_LINES
907 WHERE WORKORDER_ID = x_id;
908 c_OSP_Job_rec c_OSP_Job%ROWTYPE;
909
910 BEGIN
911 ------------------------Initialize Body------------------------------------
912
913 IF (l_log_procedure >= l_log_current_level) THEN
914 fnd_log.string(l_log_procedure,
915 L_DEBUG_KEY ||'.begin',
916 'At the start of PL SQL procedure.' ||
917 'Visit Task ID : ' || p_visit_task_id || 'Session ID : ' || p_Session_id);
918
919 END IF;
920
921 -- Standard start of API savepoint
922 SAVEPOINT Calculate_MR_Cost;
923
924 -- Initialize API return status to success
925 x_return_status := FND_API.G_RET_STS_SUCCESS;
926
927 ------------------------Start of API Body------------------------------------
928 -- Validate Visit Task and Session Exists
929 IF p_visit_task_id IS NULL THEN
930 Fnd_Message.Set_Name('AHL','AHL_CST_VISIT_TASK_ID_NULL');
931 Fnd_Msg_Pub.ADD;
932 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
933 END IF;
934
935 -- Cursor to find out MR_Id value for the Summary Task
936 OPEN c_task(p_visit_task_id);
937 FETCH c_task INTO c_task_rec;
938 CLOSE c_task;
939
940 IF c_task_rec.MR_Id IS NOT NULL THEN
941
942 IF (l_log_statement >= l_log_current_level) THEN
943 fnd_log.string(l_log_statement,
944 L_DEBUG_KEY,
945 'MR ID is not null : ' || c_task_rec.MR_Id );
946 END IF;
947 -- Cursor to find out WIP entity Id for the Current task
948 OPEN c_job(p_visit_task_id);
949 FETCH c_job INTO l_wip_Id;
950 CLOSE c_job;
951
952 -- Debug statements
953
954 IF (l_log_statement >= l_log_current_level) THEN
955 fnd_log.string(l_log_statement,
956 L_DEBUG_KEY,
957 'WIP ENTITY ID : ' || l_wip_Id ||
958 'Before calling Get_WO_Cost API to find Cost' );
959 END IF;
960
961 Get_WO_Cost (
962 p_Session_Id => p_Session_id,
963 p_Id => l_wip_Id,
964 p_program_id => FND_GLOBAL.Prog_Appl_Id,
965 x_Actual_cost => l_actual_cost,
966 x_Estimated_cost => l_estimated_cost,
967 x_return_status => l_return_status);
968
969 -- Debug statements
970 IF (l_log_statement >= l_log_current_level) THEN
971 fnd_log.string(l_log_statement,
972 L_DEBUG_KEY,
973 'After calling Get_WO_Cost API to find Cost' ||
974 'Actual Cost : ' || l_actual_cost ||
975 'Estimated Cost : ' || l_estimated_cost );
976 END IF;
977
978 -- Cursor to find all tasks with routes under this current MR
979 OPEN c_MR_task(p_visit_task_id);
980 LOOP
981 FETCH c_MR_task INTO c_MR_task_rec;
982 EXIT WHEN c_MR_task%NOTFOUND;
983
984 -- Cursor to find if any jobs present for the tasks in Visit
985 OPEN c_WO_tasks(c_MR_task_rec.visit_task_id);
986 FETCH c_WO_tasks INTO c_WO_tasks_rec;
987 CLOSE c_WO_tasks;
988
989 -- Cursor to find if any OSP jobs present for the tasks in Visit
990 OPEN c_OSP_Job(c_WO_tasks_rec.Workorder_Id);
991 FETCH c_OSP_Job INTO c_OSP_Job_rec;
992
993 IF c_OSP_Job%FOUND THEN
994
995 -- Debug statements
996 IF (l_log_statement >= l_log_current_level) THEN
997 fnd_log.string(l_log_statement,
998 L_DEBUG_KEY,
999 'Before calling Get_OSP_Cost API to find Cost for OSP Job' );
1000 END IF;
1001
1002 AHL_OSP_COST_PVT.Get_OSP_Cost
1003 (
1004 x_return_status => l_return_status,
1005 p_workorder_id => c_WO_tasks_rec.Workorder_Id,
1006 x_osp_cost => l_osp_cost
1007 );
1008
1009 l_actual_cost := l_actual_cost + nvl(l_OSP_cost,0);
1010 l_estimated_cost := l_estimated_cost + nvl(l_OSP_cost,0);
1011
1012 -- Debug statements
1013
1014 IF (l_log_statement >= l_log_current_level) THEN
1015 fnd_log.string(l_log_statement,
1016 L_DEBUG_KEY,
1017 'After calling Get_OSP_Cost API to find Cost for OSP Job, , Return Status = ' || l_return_status ||
1018 'Actual Cost : ' || l_actual_cost ||
1019 'Estimated Cost : ' || l_estimated_cost);
1020 END IF;
1021
1022 END IF;
1023 CLOSE c_OSP_Job;
1024
1025 END LOOP;
1026 CLOSE c_MR_task;
1027 END IF; -- MR ID is not null
1028
1029
1033 ------------------------End of API Body------------------------------------
1030 x_actual_cost := l_actual_cost;
1031 x_estimated_cost := l_estimated_cost;
1032
1034 --x_return_status := FND_API.G_RET_STS_SUCCESS;
1035
1036 IF (l_log_procedure >= l_log_current_level) THEN
1037 fnd_log.string(l_log_procedure,
1038 L_DEBUG_KEY ||'.end',
1039 'At the end of PL SQL procedure. Return Status = ' || l_return_status ||
1040 'Actual Cost : ' || l_actual_cost ||
1041 'Estimated Cost : ' || l_estimated_cost);
1042 END IF;
1043
1044 ------------------------Terminate API Body------------------------------------
1045 END Calculate_MR_Cost;
1046
1047 --------------------------------------------------------------------------
1048 -- Procedure to calculate visit's Cost Structure Node actual and estimated cost --
1049 --------------------------------------------------------------------------
1050 PROCEDURE Calculate_Node_Cost(
1051 p_visit_task_id IN NUMBER,
1052 p_session_id IN NUMBER,
1053 x_Actual_cost OUT NOCOPY NUMBER,
1054 x_Estimated_cost OUT NOCOPY NUMBER,
1055 x_return_status OUT NOCOPY VARCHAR2 )
1056 IS
1057 l_actual_cost NUMBER := 0;
1058 l_estimated_cost NUMBER := 0;
1059
1060 l_OSP_cost NUMBER;
1061 l_return_status VARCHAR2(1);
1062
1063 L_API_NAME CONSTANT VARCHAR2(30) := 'Calculate_Node_Cost';
1064 L_DEBUG_KEY CONSTANT VARCHAR2(100) := 'ahl.plsql.' || G_PKG_NAME || '.' || L_API_NAME;
1065
1066 -- Define local cursors
1067 -- To find out viist's task details
1068 CURSOR c_task(x_id IN NUMBER)IS
1069 SELECT * FROM AHL_VISIT_TASKS_B
1070 WHERE VISIT_TASK_ID = x_id;
1071 c_task_rec c_task%ROWTYPE;
1072
1073 -- yazhou 27-Jun-2006 starts
1074 -- fix for bug#5377347, to include cost of the canceled workorder (7-cancelled)
1075 -- Since one task may map to multiple canceled workorders, query for the latest one
1076
1077 -- To find out to find out task's Workorder_Id
1078 CURSOR c_WO_tasks (x_id IN NUMBER) IS
1079 SELECT AWV.WORKORDER_ID, AWV.wip_entity_id
1080 FROM AHL_WORKORDERS AWV
1081 WHERE AWV.VISIT_TASK_ID = x_id
1082 -- AND AWV.STATUS_CODE NOT IN ('22','7')
1083 AND AWV.STATUS_CODE <>'22'
1084 ORDER BY LAST_UPDATE_DATE DESC;
1085
1086 -- yazhou 27-Jun-2006 ends
1087
1088 c_WO_tasks_rec c_WO_tasks%ROWTYPE;
1089
1090 -- To find out if jobs is an internal job or an OSP job
1091 CURSOR c_OSP_Job (x_id IN NUMBER) IS
1092 SELECT * FROM AHL_OSP_ORDER_LINES
1093 WHERE WORKORDER_ID = x_id;
1094 c_OSP_Job_rec c_OSP_Job%ROWTYPE;
1095
1096 -- To find out all tasks using the given task as cost parent
1097 CURSOR c_Cost_Parent_task(x_id IN NUMBER) IS
1098 SELECT visit_task_id
1099 FROM ahl_visit_tasks_b
1100 START WITH visit_task_id = x_id
1101 CONNECT BY PRIOR visit_task_id = cost_parent_id;
1102 c_Cost_Parent_task_rec c_Cost_Parent_task%ROWTYPE;
1103
1104 BEGIN
1105 ------------------------Initialize Body------------------------------------
1106 IF (l_log_procedure >= l_log_current_level) THEN
1107 fnd_log.string(l_log_procedure,
1108 L_DEBUG_KEY ||'.begin',
1109 'At the start of PL SQL procedure.' ||
1110 'Value of Visit Task ID : ' || p_visit_task_id ||
1111 'Cost Session ID : ' || p_Session_id );
1112 END IF;
1113
1114 -- Standard start of API savepoint
1115 SAVEPOINT Calculate_Node_Cost;
1116
1120 ------------------------Start of API Body------------------------------------
1117 -- Initialize API return status to success
1118 x_return_status := FND_API.G_RET_STS_SUCCESS;
1119
1121 -- Validate Visit Task
1122 IF p_Visit_task_id IS NULL THEN
1123 Fnd_Message.Set_Name('AHL','AHL_CST_VISIT_TASK_ID_NULL');
1124 Fnd_Msg_Pub.ADD;
1125 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1126 END IF;
1127
1128 OPEN c_task(p_Visit_task_id);
1129 FETCH c_task INTO c_task_rec;
1130 CLOSE c_task;
1131
1132 -- for manually created summary task representing MRs
1133
1134 IF (l_log_statement >= l_log_current_level) THEN
1135 fnd_log.string(l_log_statement,
1136 L_DEBUG_KEY,
1140
1137 'TASK TYPE CODE is = ' || c_task_rec.TASK_TYPE_CODE ||
1138 'MR ID is = ' || c_task_rec.mr_id );
1139 END IF;
1141 IF c_task_rec.TASK_TYPE_CODE = 'SUMMARY' and c_task_rec.mr_id is null
1142 THEN
1143
1144 -- Debug statements
1145 IF (l_log_statement >= l_log_current_level) THEN
1146 fnd_log.string(l_log_statement,
1147 L_DEBUG_KEY,
1148 'Before calling Get_WO_Cost API to find Cost' );
1149 END IF;
1150
1151 Get_WO_Cost (
1152 p_Session_Id => p_Session_id,
1153 p_Id => p_Visit_task_id,
1154 p_program_id => FND_GLOBAL.Prog_Appl_Id,
1155 x_Actual_cost => l_actual_cost,
1156 x_Estimated_cost => l_estimated_cost,
1157 x_return_status => l_return_status);
1158
1159 -- Debug statements
1160 IF (l_log_statement >= l_log_current_level) THEN
1161 fnd_log.string(l_log_statement,
1162 L_DEBUG_KEY,
1163 'After calling Get_WO_Cost API to find Cost, , Return Status = ' || l_return_status);
1164 END IF;
1165
1166 ELSE -- all other tasks
1167
1168 OPEN c_WO_tasks(p_Visit_task_id);
1169 FETCH c_WO_tasks INTO c_WO_tasks_rec;
1170 CLOSE c_WO_tasks;
1171
1172 -- Debug statements
1173 IF (l_log_statement >= l_log_current_level) THEN
1174 fnd_log.string(l_log_statement,
1175 L_DEBUG_KEY,
1176 'Before calling Get_WO_Cost API to find Cost' ||
1177 'WIP ENTITY ID = ' || c_WO_tasks_rec.wip_entity_id ||
1178 'Value of Work Order ID = ' || c_WO_tasks_rec.workorder_id );
1179
1180 END IF;
1181
1182 Get_WO_Cost (
1183 p_Session_Id => p_Session_id,
1184 p_Id => c_WO_tasks_rec.wip_entity_id,
1185 p_program_id => FND_GLOBAL.Prog_Appl_Id,
1186 x_Actual_cost => l_actual_cost,
1187 x_Estimated_cost => l_estimated_cost,
1188 x_return_status => l_return_status);
1189
1190 -- Debug statements
1191 IF (l_log_statement >= l_log_current_level) THEN
1192 fnd_log.string(l_log_statement,
1193 L_DEBUG_KEY,
1194 'After calling Get_OSP_Cost API to find Cost for OSP Job, Return Status = ' || l_return_status ||
1195 'Value of p_Session_Id : ' || p_Session_Id ||
1196 'Actual Cost : ' || l_actual_cost ||
1197 'Estimated Cost : ' || l_estimated_cost );
1198 END IF;
1199
1200 END IF; -- task type = summary and mr_id is null
1201
1202 -- Cursor to find all tasks using the current task as cost parent
1203 OPEN c_Cost_Parent_task(p_visit_task_id);
1204 LOOP
1205 FETCH c_Cost_Parent_task INTO c_Cost_Parent_task_rec;
1206 EXIT WHEN c_Cost_Parent_task%NOTFOUND;
1207
1208 -- Cursor to find if any jobs present for the tasks in Visit
1209 OPEN c_WO_tasks(c_Cost_Parent_task_rec.visit_task_id);
1210 FETCH c_WO_tasks INTO c_WO_tasks_rec;
1211
1212 if c_WO_tasks%FOUND then
1213
1214 -- Cursor to find if any OSP jobs present for the tasks in Visit
1215 OPEN c_OSP_Job(c_WO_tasks_rec.Workorder_Id);
1216 FETCH c_OSP_Job INTO c_OSP_Job_rec;
1217
1218 IF c_OSP_Job%FOUND THEN
1219
1220 -- Debug statements
1221 IF (l_log_statement >= l_log_current_level) THEN
1222 fnd_log.string(l_log_statement,
1223 L_DEBUG_KEY,
1224 'Before calling Get_OSP_Cost API to find Cost for OSP Job' );
1225 END IF;
1226
1227 AHL_OSP_COST_PVT.Get_OSP_Cost
1228 (
1229 x_return_status => l_return_status,
1230 p_workorder_id => c_WO_tasks_rec.Workorder_Id,
1231 x_osp_cost => l_osp_cost
1232 );
1233
1234 l_actual_cost := l_actual_cost + nvl(l_OSP_cost,0);
1235 l_estimated_cost := l_estimated_cost + nvl(l_OSP_cost,0);
1236
1237 -- Debug statements
1238 IF (l_log_statement >= l_log_current_level) THEN
1239 fnd_log.string(l_log_statement,
1240 L_DEBUG_KEY,
1241 'After calling Get_OSP_Cost API to find Cost for OSP Job, Return Status = ' || l_return_status ||
1242 'Actual Cost : ' || l_actual_cost ||
1243 'Estimated Cost : ' || l_estimated_cost );
1244 END IF;
1245
1246 END IF;
1247 CLOSE c_OSP_Job;
1248 END IF;
1249 CLOSE c_WO_tasks;
1250 END LOOP;
1251 CLOSE c_Cost_Parent_task;
1252
1253
1254 x_actual_cost := l_actual_cost;
1255 x_estimated_cost := l_estimated_cost;
1256
1257 ------------------------End of API Body------------------------------------
1258
1259 IF (l_log_procedure >= l_log_current_level) THEN
1260 fnd_log.string(l_log_procedure,
1261 L_DEBUG_KEY ||'.end',
1262 'At the end of PL SQL procedure. Return Status = ' || l_return_status ||
1268
1263 'Task Actual Cost : ' || x_actual_cost ||
1264 'Task Estimated Cost : ' || x_estimated_cost );
1265 END IF;
1266
1267 x_return_status := FND_API.G_RET_STS_SUCCESS;
1269 ------------------------Terminate API Body------------------------------------
1270 END Calculate_Node_Cost;
1271
1272
1273 --------------------------------------------------------------------------
1274 -- Procedure to calculate visit's task actual and estimated cost --
1275 --------------------------------------------------------------------------
1276 PROCEDURE Calculate_Task_Cost(
1277 p_visit_task_id IN NUMBER,
1278 p_session_id IN NUMBER,
1279 x_Actual_cost OUT NOCOPY NUMBER,
1280 x_Estimated_cost OUT NOCOPY NUMBER,
1281 x_return_status OUT NOCOPY VARCHAR2 )
1282 IS
1283 l_actual_cost NUMBER := 0;
1284 l_estimated_cost NUMBER := 0;
1285
1286 l_wip_Id NUMBER;
1287 l_WO_Id NUMBER;
1288
1289 l_OSP_cost NUMBER;
1290 l_return_status VARCHAR2(1);
1291
1295 -- Define local cursors
1292 L_API_NAME CONSTANT VARCHAR2(30) := 'Calculate_Task_Cost';
1293 L_DEBUG_KEY CONSTANT VARCHAR2(100) := 'ahl.plsql.' || G_PKG_NAME || '.' || L_API_NAME;
1294
1296 -- To find out viist's task details
1297 CURSOR c_task(x_id IN NUMBER)IS
1298 SELECT * FROM AHL_VISIT_TASKS_B
1299 WHERE VISIT_TASK_ID = x_id;
1300 c_task_rec c_task%ROWTYPE;
1301
1302 -- yazhou 27-Jun-2006 starts
1303 -- fix for bug#5377347, to include cost of the canceled workorder (7-cancelled)
1304 -- Since one task may map to multiple canceled workorders, query for the latest one
1305
1306 -- To find out WIP_Entity_Id for the task
1307 CURSOR c_job(x_id IN NUMBER) IS
1308 SELECT AWO.WIP_ENTITY_ID, AWO.WORKORDER_ID
1309 FROM AHL_WORKORDERS AWO
1310 WHERE AWO.VISIT_TASK_ID = x_id
1311 -- AND AWO.STATUS_CODE NOT IN ('22','7')
1312 AND AWO.STATUS_CODE <>'22'
1313 AND AWO.MASTER_WORKORDER_FLAG = 'N'
1314 ORDER BY LAST_UPDATE_DATE DESC;
1315 -- yazhou 27-Jun-2006 ends
1316
1317 -- To find out if jobs is an internal job or an OSP job
1318 CURSOR c_OSP_Job (x_id IN NUMBER) IS
1319 SELECT * FROM AHL_OSP_ORDER_LINES
1320 WHERE WORKORDER_ID = x_id;
1321 c_OSP_Job_rec c_OSP_Job%ROWTYPE;
1322
1323 BEGIN
1324 ------------------------Initialize Body------------------------------------
1325
1326 IF (l_log_procedure >= l_log_current_level) THEN
1327 fnd_log.string(l_log_procedure,
1328 L_DEBUG_KEY ||'.begin',
1329 'At the start of PL SQL procedure.' ||
1330 'Value of Visit Task ID : ' || p_visit_task_id ||
1331 'Session ID : ' || p_Session_id );
1332 END IF;
1333
1334 -- Standard start of API savepoint
1335 SAVEPOINT Calculate_Task_Cost;
1336
1337 -- Initialize API return status to success
1338 x_return_status := FND_API.G_RET_STS_SUCCESS;
1339
1340 ------------------------Start of API Body------------------------------------
1341
1342 -- Validate Visit Task
1343 IF p_Visit_task_id IS NULL THEN
1344 Fnd_Message.Set_Name('AHL','AHL_CST_VISIT_TASK_ID_NULL');
1345 Fnd_Msg_Pub.ADD;
1346 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1347 END IF;
1348
1349 OPEN c_task(p_Visit_task_id);
1350 FETCH c_task INTO c_task_rec;
1351 CLOSE c_task;
1352
1353 IF (l_log_statement >= l_log_current_level) THEN
1354 fnd_log.string(l_log_statement,
1355 L_DEBUG_KEY,
1356 'TASK TYPE CODE = ' || c_task_rec.TASK_TYPE_CODE ||
1357 'MR ID = ' || c_task_rec.mr_id );
1358 END IF;
1359
1360 -- for summary task representing MRs
1361
1362 IF c_task_rec.TASK_TYPE_CODE = 'SUMMARY' and c_task_rec.mr_id is not null THEN
1363
1364 IF (l_log_statement >= l_log_current_level) THEN
1365 fnd_log.string(l_log_statement,
1366 L_DEBUG_KEY,
1367 'Before Calling Calculate_MR_Cost : MR-summary tasks' );
1368 END IF;
1369
1370 Calculate_MR_Cost
1371 (p_Visit_task_id => p_Visit_task_id,
1372 p_Session_id => p_Session_id,
1373 x_Actual_cost => l_actual_cost,
1374 x_Estimated_cost => l_estimated_cost,
1375 x_return_status => l_return_status
1376 );
1377
1378 IF (l_log_statement >= l_log_current_level) THEN
1379 fnd_log.string(l_log_statement,
1380 L_DEBUG_KEY,
1381 'After calling Calculate_MR_Cost, Return Status = ' || l_return_status ||
1382 'Actual Cost : ' || l_actual_cost ||
1383 'Estimated Cost : ' || l_estimated_cost );
1384 END IF;
1385 ELSIF c_task_rec.TASK_TYPE_CODE <> 'SUMMARY'
1386 THEN
1387 -- for planned/unplanned/unassociate tasks
1388
1389 OPEN c_job(p_Visit_task_id);
1390 FETCH c_job INTO l_wip_Id, l_WO_Id;
1391 CLOSE c_job;
1392
1393 -- Debug statements
1394 IF (l_log_statement >= l_log_current_level) THEN
1395 fnd_log.string(l_log_statement,
1396 L_DEBUG_KEY,
1397 'Before calling Get_WO_Cost API to find Cost : Non-summary tasks ' ||
1398 'WIP ENTITY ID : ' || l_wip_Id ||
1399 'Value of Work Order ID : ' || l_WO_Id );
1400 END IF;
1401
1402 Get_WO_Cost (
1403 p_Session_Id => p_Session_id,
1404 p_Id => l_wip_Id,
1405 p_program_id => FND_GLOBAL.Prog_Appl_Id,
1406 x_Actual_cost => l_actual_cost,
1407 x_Estimated_cost => l_estimated_cost,
1408 x_return_status => l_return_status);
1409
1410 -- Debug statements
1411 IF (l_log_statement >= l_log_current_level) THEN
1412 fnd_log.string(l_log_statement,
1413 L_DEBUG_KEY,
1414 'After calling Get_OSP_Cost API to find Cost for OSP Job, Return Status = ' || l_return_status ||
1415 'Actual Cost : ' || l_actual_cost ||
1416 'Estimated Cost : ' || l_estimated_cost );
1417 END IF;
1418
1419 OPEN c_OSP_Job(l_WO_Id);
1420 FETCH c_OSP_Job INTO c_OSP_Job_rec;
1421
1422 IF c_OSP_Job%FOUND THEN
1423
1424 IF (l_log_statement >= l_log_current_level) THEN
1428 END IF;
1425 fnd_log.string(l_log_statement,
1426 L_DEBUG_KEY,
1427 'Before calling AHL_OSP_COST_PVT.Get_OSP_Cost' );
1429 AHL_OSP_COST_PVT.Get_OSP_Cost
1430 (
1431 x_return_status => l_return_status,
1432 p_workorder_id => l_WO_Id,
1433 x_osp_cost => l_osp_cost
1434 );
1435
1436 l_actual_cost := l_actual_cost + nvl(l_OSP_cost,0);
1437 l_estimated_cost := l_estimated_cost + nvl(l_OSP_cost,0);
1438
1439 IF (l_log_statement >= l_log_current_level) THEN
1440 fnd_log.string(l_log_statement,
1441 L_DEBUG_KEY,
1442 'After calling AHL_OSP_COST_PVT.Get_OSP_Cost, Return Status = ' || l_return_status ||
1443 'l_actual_cost = ' || l_actual_cost ||
1444 'l_estimated_cost = ' || l_estimated_cost);
1445 END IF;
1446 END IF;
1447 CLOSE c_OSP_Job;
1448
1449 END IF;
1450
1451
1452 x_actual_cost := l_actual_cost;
1453 x_estimated_cost := l_estimated_cost;
1454
1455 ------------------------End of API Body------------------------------------
1456
1457 IF (l_log_procedure >= l_log_current_level) THEN
1458 fnd_log.string(l_log_procedure,
1459 L_DEBUG_KEY ||'.end',
1460 'At the end of PL SQL procedure. Return Status = ' || l_return_status ||
1461 'Task Actual Cost : ' || x_actual_cost ||
1462 'Task Estimated Cost : ' || x_estimated_cost );
1463 END IF;
1464
1465 x_return_status := FND_API.G_RET_STS_SUCCESS;
1466
1467 ------------------------Terminate API Body------------------------------------
1468 END Calculate_Task_Cost;
1469
1470
1471 --------------------------------------------------------------------------
1472 -- Procedure to disitnguish between various conditions mainly Visit status,
1473 -- Session and Job existence for further calculation of actual and estimated cost
1474 -- Called from get_visit_cost_details/get_task_cost_details/get_MR_cost_details
1475 --------------------------------------------------------------------------
1476 PROCEDURE Calculate_WO_Cost(
1477 p_api_version IN NUMBER := 1.0,
1478 p_init_msg_list IN VARCHAR2 := Fnd_Api.G_FALSE,
1479 p_commit IN VARCHAR2 := Fnd_Api.G_FALSE,
1480 p_validation_level IN NUMBER := Fnd_Api.G_VALID_LEVEL_FULL,
1481 p_x_cost_price_rec IN OUT NOCOPY AHL_VWP_VISIT_CST_PR_PVT.Cost_price_rec_type,
1482 x_return_status OUT NOCOPY VARCHAR2)
1483 IS
1484 -- Define Local Variables
1485 l_count NUMBER;
1486
1487 l_visit_id NUMBER;
1488 l_mr_Session_id NUMBER := NULL;
1489 l_cost_session_id NUMBER := NULL;
1490
1491 l_return_status VARCHAR2(1);
1492 l_cost_rollup_flag VARCHAR2(1):='N';
1493 l_cost_price_rec AHL_VWP_VISIT_CST_PR_PVT.Cost_Price_Rec_Type := p_x_cost_price_rec;
1494
1495 L_API_NAME CONSTANT VARCHAR2(30) := 'Calculate_WO_Cost';
1496 L_DEBUG_KEY CONSTANT VARCHAR2(100) := 'ahl.plsql.' || G_PKG_NAME || '.' || L_API_NAME;
1497
1498 -- Define Local Cursors
1499 -- To find all visit related details
1500 CURSOR c_visit_details (x_id IN NUMBER) IS
1501 SELECT * FROM AHL_VISITS_VL
1502 WHERE VISIT_ID = x_id;
1503 visit_rec c_visit_details%ROWTYPE;
1504
1505 -- To find visit id for a given task
1506 CURSOR c_task (x_T_id IN NUMBER) IS
1507 SELECT visit_id FROM AHL_VISIT_TASKS_VL
1508 WHERE VISIT_TASK_ID = x_T_id;
1509
1510 -- To find if job exists for the visit at shop floor
1511 -- in 'Draft' status
1512 CURSOR c_job_visit(x_id IN NUMBER) IS
1513 SELECT WORKORDER_ID FROM AHL_WORKORDERS
1514 WHERE VISIT_ID = x_id
1515 AND VISIT_TASK_ID IS NULL
1516 AND STATUS_CODE NOT IN ('22','7')
1517 AND MASTER_WORKORDER_FLAG = 'Y';
1518
1519 BEGIN
1520 ------------------------Initialize Body------------------------------------
1521
1522 IF (l_log_procedure >= l_log_current_level) THEN
1523 fnd_log.string(l_log_procedure,
1524 L_DEBUG_KEY ||'.begin',
1525 'At the start of PL SQL procedure.' ||
1526 'Value of Visit ID : ' || l_cost_price_rec.visit_id ||
1527 'Value of Cost Session ID : ' || l_cost_price_rec.cost_session_Id ||
1528 'Value of MR Session ID : ' || l_cost_price_rec.MR_Session_Id );
1529 END IF;
1530
1531 -- Standard start of API savepoint
1532 SAVEPOINT Calculate_WO_Cost;
1533
1534 -- Initialize API return status to success
1535 x_return_status := FND_API.G_RET_STS_SUCCESS;
1536
1537 l_Visit_Id := l_cost_price_rec.visit_Id;
1538 l_MR_Session_Id := l_cost_price_rec.MR_Session_Id;
1539 l_Cost_Session_Id := l_cost_price_rec.Cost_Session_Id;
1540
1541
1542 IF l_visit_id IS NULL OR l_visit_id = FND_API.G_MISS_NUM THEN
1543
1544 -- Whether call for Calculate_WO_Cost is from Visit OR Task
1545 IF l_cost_price_rec.visit_task_id IS NOT NULL AND l_cost_price_rec.visit_task_id <> FND_API.G_MISS_NUM THEN
1546
1547 -- Cursor for task related information in search task view
1548 OPEN c_task(l_cost_price_rec.visit_task_id);
1549 FETCH c_task INTO l_visit_id;
1550 CLOSE c_task;
1551
1552 END IF;
1553
1557 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_VWP_CST_INPUT_MISS');
1554 -- Validate Visit exists
1555 IF l_visit_id IS NULL OR l_visit_id = FND_API.G_MISS_NUM THEN
1556
1558
1559 FND_MSG_PUB.ADD;
1560
1561 IF (l_log_statement >= l_log_current_level) THEN
1562 fnd_log.string(l_log_statement,
1563 L_DEBUG_KEY,
1564 'Visit id is mandatory but found null in input ' );
1565 END IF;
1566
1567 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1568
1569 END IF;
1570
1571 END IF;
1572
1573 -- Check if workorder already exists for the visit
1574
1575 OPEN c_job_visit(l_visit_id);
1576 FETCH c_job_visit INTO l_cost_price_rec.workorder_id;
1577 CLOSE c_job_visit;
1578
1579 IF (l_log_statement >= l_log_current_level) THEN
1580 fnd_log.string(l_log_statement,
1581 L_DEBUG_KEY,
1582 'work order ID is : '||l_cost_price_rec.workorder_id );
1583 END IF;
1584
1585 -- Cursor to fetch all visit details
1586 OPEN c_visit_details(l_visit_id);
1587 FETCH c_visit_details INTO visit_rec;
1588 CLOSE c_visit_details;
1589
1590
1594 IF visit_rec.any_task_chg_flag = 'Y' THEN
1591 -- If any task changed flag is 'Y' then
1592 -- return with l_cost_price_rec.Is_Cst_Struc_updated set to 'Y'
1593
1595 l_cost_price_rec.Is_Cst_Struc_updated := 'Y';
1596 ELSE
1597 l_cost_price_rec.Is_Cst_Struc_updated := 'N';
1598
1599
1600 -- If any task changed flag is 'N' then
1601 -- Check visit status
1602 -- When Visit is in 'PLANNING' status
1603
1604 IF visit_rec.status_code = 'PLANNING' THEN
1605
1606 -- workorder created already
1607 IF l_cost_price_rec.workorder_id is not null and
1608 l_cost_price_rec.workorder_id <> FND_API.G_MISS_NUM THEN
1609
1610 l_cost_rollup_flag := 'Y';
1611
1612 END IF; -- Check for l_count
1613
1614 ELSE -- Else for visit status
1615 -- When Visit is in 'RELEASED' status
1616
1617 l_cost_rollup_flag := 'Y';
1618
1619 END IF; -- Check for visit status
1620
1621 IF (l_log_statement >= l_log_current_level) THEN
1622 fnd_log.string(l_log_statement,
1623 L_DEBUG_KEY,
1624 'l_cost_rollup_flag = ' || l_cost_rollup_flag );
1625 END IF;
1626
1627 IF l_cost_rollup_flag ='Y' AND l_cost_price_rec.workorder_id is not null THEN
1628
1629 -- Check if MR Session and Cost Session ID
1630 -- are present in Released status for Visit
1631 IF l_MR_Session_Id IS NOT NULL AND l_cost_session_Id IS NOT NULL THEN
1632
1633 IF (l_log_statement >= l_log_current_level) THEN
1634 fnd_log.string(l_log_statement,
1635 L_DEBUG_KEY,
1636 'Before calling Rollup_MR_Cost_Hierarchy API: ' );
1637 END IF;
1638
1639 -- Call for Rollup_MR_Cost_Hierarchy API
1640 Rollup_MR_Cost_Hierarchy
1641 (
1642 p_api_version => p_api_version,
1643 p_init_msg_list => p_init_msg_list,
1644 p_commit => p_commit,
1645 p_visit_id => l_visit_id,
1646 p_MR_session_Id => l_MR_session_Id,
1647 p_Cost_Session_Id => l_Cost_Session_Id,
1648 x_return_status => l_return_status
1649 );
1650
1651 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1652 RAISE Fnd_Api.G_EXC_ERROR;
1653 END IF;
1654
1655 IF (l_log_statement >= l_log_current_level) THEN
1656 fnd_log.string(l_log_statement,
1657 L_DEBUG_KEY,
1658 'After calling Rollup_MR_Cost_Hierarchy API, Return Status = ' || l_return_status ||
1659 'Visit Id : ' || l_visit_id );
1660 END IF;
1661
1662 ELSE
1663 -- Else for MR Session and Cost Session ID
1664 -- are not present in Released Status for Visit
1665
1666 IF (l_log_statement >= l_log_current_level) THEN
1667 fnd_log.string(l_log_statement,
1668 L_DEBUG_KEY,
1669 'Before calling Push_MR_Cost_Hierarchy API: ' );
1670 END IF;
1671
1672 -- Call for Push_MR_Cost_Hierarchy API
1673 Push_MR_Cost_Hierarchy
1674 (
1675 p_api_version => p_api_version,
1676 p_init_msg_list => p_init_msg_list,
1677 p_commit => p_commit,
1678 p_validation_level => p_validation_level,
1679 p_visit_id => l_visit_id,
1680 x_MR_session_Id => l_MR_session_Id,
1681 x_Cost_Session_Id => l_Cost_Session_Id,
1682 x_return_status => l_return_status
1683 );
1684
1685 IF (l_log_statement >= l_log_current_level) THEN
1686 fnd_log.string(l_log_statement,
1687 L_DEBUG_KEY,
1688 'After calling Push_MR_Cost_Hierarchy API, Return Status = ' || l_return_status ||
1689 'Visit Id : ' || l_visit_id );
1690 END IF;
1691
1692 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1693 RAISE Fnd_Api.G_EXC_ERROR;
1694 END IF;
1695
1696 IF (l_log_statement >= l_log_current_level) THEN
1697 fnd_log.string(l_log_statement,
1698 L_DEBUG_KEY,
1699 'Before calling Rollup_MR_Cost_Hierarchy API: ' );
1700 END IF;
1701
1702 -- Call for Rollup_MR_Cost_Hierarchy API
1703 Rollup_MR_Cost_Hierarchy
1704 (
1705 p_api_version => p_api_version,
1709 p_MR_session_Id => l_MR_session_Id,
1706 p_init_msg_list => p_init_msg_list,
1707 p_commit => p_commit,
1708 p_visit_id => l_visit_id,
1710 p_Cost_Session_Id => l_Cost_Session_Id,
1711 x_return_status => l_return_status
1712 );
1713
1714 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1715 RAISE Fnd_Api.G_EXC_ERROR;
1716 END IF;
1717
1718 IF (l_log_statement >= l_log_current_level) THEN
1719 fnd_log.string(l_log_statement,
1720 L_DEBUG_KEY,
1721 'After calling Rollup_MR_Cost_Hierarchy API, Return Status = ' || l_return_status ||
1722 'Visit Id : ' || l_visit_id);
1723 END IF;
1724
1725 END IF; -- Check for Session Ids
1726
1727 END IF; -- l_cost_rollup_flag
1728
1729 END IF; -- visit_rec.any_task_chg_flag = 'Y'
1730
1731 p_x_cost_price_rec.MR_Session_Id := l_MR_Session_Id;
1732 p_x_cost_price_rec.Cost_Session_Id := l_Cost_Session_Id;
1733 p_x_cost_price_rec.Is_Cst_Struc_updated := l_cost_price_rec.Is_Cst_Struc_updated;
1734 p_x_cost_price_rec.workorder_id := l_cost_price_rec.workorder_id;
1735
1736 x_return_status := FND_API.G_RET_STS_SUCCESS;
1737
1738 ------------------------End of API Body------------------------------------
1739
1740 IF (l_log_procedure >= l_log_current_level) THEN
1741 fnd_log.string(l_log_procedure,
1742 L_DEBUG_KEY ||'.end',
1743 'At the end of PL SQL procedure. Return Status = ' || l_return_status ||
1744 'MR Session ID: ' || p_x_cost_price_rec.MR_Session_Id ||
1745 'Cost Session ID: ' || p_x_cost_price_rec.Cost_Session_Id ||
1746 'Is_Cst_Struc_updated flag: ' || p_x_cost_price_rec.Is_Cst_Struc_updated ||
1747 'Work order ID: ' || p_x_cost_price_rec.workorder_id );
1748 END IF;
1749
1750 ------------------------Terminate API Body------------------------------------
1751 END Calculate_WO_Cost;
1752
1753
1754
1755 --------------------------------------------------------------------------
1756 -- Procedure to disitnguish between various conditions mainly Visit status,
1757 -- Session and Job existence for further calculation of actual and estimated cost
1758 -- Called from estimate_visit_cost/estimate_MR_cost/estimate_task_cost
1759 --------------------------------------------------------------------------
1760 PROCEDURE Estimate_WO_Cost(
1761 p_api_version IN NUMBER := 1.0,
1762 p_init_msg_list IN VARCHAR2 := Fnd_Api.G_FALSE,
1763 p_commit IN VARCHAR2 := Fnd_Api.G_FALSE,
1764 p_validation_level IN NUMBER := Fnd_Api.G_VALID_LEVEL_FULL,
1765 p_x_cost_price_rec IN OUT NOCOPY AHL_VWP_VISIT_CST_PR_PVT.Cost_price_rec_type,
1766 x_return_status OUT NOCOPY VARCHAR2)
1767 IS
1768 -- Define Local Variables
1769
1770 l_visit_id NUMBER;
1771 l_mr_Session_id NUMBER := NULL;
1772 l_cost_session_id NUMBER := NULL;
1773
1774 l_return_status VARCHAR2(1);
1775
1776 l_cost_price_rec AHL_VWP_VISIT_CST_PR_PVT.Cost_Price_Rec_Type := p_x_cost_price_rec;
1777
1778 L_API_NAME CONSTANT VARCHAR2(30) := 'Estimate_WO_Cost';
1779 L_DEBUG_KEY CONSTANT VARCHAR2(100) := 'ahl.plsql.' || G_PKG_NAME || '.' || L_API_NAME;
1780
1781 -- Define Local Cursors
1782 -- To find task related information when its for a visit
1783 CURSOR c_task (x_T_id IN NUMBER) IS
1784 SELECT * FROM AHL_VISIT_TASKS_VL
1785 WHERE VISIT_TASK_ID = x_T_id;
1786 task_rec c_task%ROWTYPE;
1787
1788 BEGIN
1789 ------------------------Initialize Body------------------------------------
1790
1791 IF (l_log_procedure >= l_log_current_level) THEN
1792 fnd_log.string(l_log_procedure,
1793 L_DEBUG_KEY ||'.begin',
1794 'At the start of PL SQL procedure.' ||
1795 'Value of Visit ID : ' || l_cost_price_rec.visit_id ||
1796 'Value of Cost Session ID : ' || l_cost_price_rec.cost_session_Id ||
1797 'Value of MR Session ID : ' || l_cost_price_rec.MR_Session_Id );
1798 END IF;
1799
1800 -- Standard start of API savepoint
1801 SAVEPOINT Estimate_WO_Cost;
1802
1803 -- Initialize API return status to success
1804 x_return_status := FND_API.G_RET_STS_SUCCESS;
1805
1806 l_Visit_Id := l_cost_price_rec.visit_Id;
1807 l_MR_Session_Id := l_cost_price_rec.MR_Session_Id;
1808 l_Cost_Session_Id := l_cost_price_rec.Cost_Session_Id;
1809
1810 -- Whether call for Calculate_WO_Cost is from Visit OR Task
1811 IF l_cost_price_rec.visit_task_id IS NOT NULL AND l_cost_price_rec.visit_task_id <> FND_API.G_MISS_NUM THEN
1812
1813 -- Cursor for task related information in search task view
1814 OPEN c_task(l_cost_price_rec.visit_task_id);
1815 FETCH c_task INTO task_rec;
1816 CLOSE c_task;
1817
1818 l_visit_id := task_rec.visit_id;
1819 END IF;
1820
1821 -- yazhou 25-Jul-2005 starts
1822 -- push the cost structure ever time estimate cost button is clicked
1823
1824 --push the cost structure if session id not present
1825
1826 -- IF l_MR_Session_Id IS NULL OR l_cost_session_Id IS NULL THEN
1827
1831 fnd_log.string(l_log_statement,
1828 -- yazhou 25-Jul-2005 ends
1829
1830 IF (l_log_statement >= l_log_current_level) THEN
1832 L_DEBUG_KEY,
1833 'Before calling Push_MR_Cost_Hierarchy API: ' );
1834 END IF;
1835
1836 -- Call for Push_MR_Cost_Hierarchy API
1837 Push_MR_Cost_Hierarchy
1838 (
1839 p_api_version => p_api_version,
1840 p_init_msg_list => p_init_msg_list,
1841 p_commit => p_commit,
1842 p_validation_level => p_validation_level,
1843 p_visit_id => l_visit_id,
1844 x_MR_session_Id => l_MR_session_Id,
1845 x_Cost_Session_Id => l_Cost_Session_Id,
1846 x_return_status => l_return_status
1847 );
1848
1849 IF (l_log_statement >= l_log_current_level) THEN
1850 fnd_log.string(l_log_statement,
1851 L_DEBUG_KEY,
1852 'After calling Push_MR_Cost_Hierarchy API, Return Status = ' || l_return_status );
1853 END IF;
1854
1855 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1856 RAISE Fnd_Api.G_EXC_ERROR;
1857 END IF;
1858
1859 IF (l_log_statement >= l_log_current_level) THEN
1860 fnd_log.string(l_log_statement,
1861 L_DEBUG_KEY,
1862 'Before calling Rollup_MR_Cost_Hierarchy API: ' );
1863 END IF;
1864
1865 -- yazhou 25-Jul-2005 starts
1866
1867
1868 -- END IF; -- session id is null
1869
1870
1871 -- yazhou 25-Jul-2005 ends
1872
1873 -- Call for Rollup_MR_Cost_Hierarchy API
1874 Rollup_MR_Cost_Hierarchy
1875 (
1876 p_api_version => p_api_version,
1877 p_init_msg_list => p_init_msg_list,
1878 p_commit => p_commit,
1879 p_visit_id => l_visit_id,
1880 p_MR_session_Id => l_MR_session_Id,
1881 p_Cost_Session_Id => l_Cost_Session_Id,
1882 x_return_status => l_return_status
1883 );
1884
1885 IF (l_log_statement >= l_log_current_level) THEN
1886 fnd_log.string(l_log_statement,
1887 L_DEBUG_KEY,
1888 'After calling Rollup_MR_Cost_Hierarchy API, Return Status = ' || l_return_status);
1889 END IF;
1890
1891 -- Assign Is_Cst_Struc_updated to 'N'
1892 l_cost_price_rec.Is_Cst_Struc_updated := 'N';
1893
1894 -- Assign values to in-out rectype
1895 p_x_cost_price_rec.MR_Session_Id := l_MR_Session_Id;
1896 p_x_cost_price_rec.Cost_Session_Id := l_Cost_Session_Id;
1897 p_x_cost_price_rec.Is_Cst_Struc_updated := l_cost_price_rec.Is_Cst_Struc_updated;
1898
1899 x_return_status := FND_API.G_RET_STS_SUCCESS;
1900
1901 ------------------------End of API Body------------------------------------
1902
1903
1904 IF (l_log_procedure >= l_log_current_level) THEN
1905 fnd_log.string(l_log_procedure,
1906 L_DEBUG_KEY ||'.end',
1907 'At the end of PL SQL procedure. Return Status = ' || l_return_status ||
1908 'Value of Cost Session ID : ' || p_x_cost_price_rec.cost_session_Id ||
1909 'Value of MR Session ID : ' || p_x_cost_price_rec.MR_Session_Id );
1910 END IF;
1911
1912
1913 ------------------------Terminate API Body------------------------------------
1914 END Estimate_WO_Cost;
1915
1916
1917
1918 --------------------------------------------------------------------------
1919 -- Procedure to find Visit/MR/Task actual and estimated costs in cost hierarchy table
1920 --------------------------------------------------------------------------
1921 PROCEDURE Get_WO_Cost(
1922 p_Session_Id IN NUMBER,
1923 p_Id IN NUMBER,
1924 p_program_id IN NUMBER,
1925 x_Actual_cost OUT NOCOPY NUMBER,
1926 x_Estimated_cost OUT NOCOPY NUMBER,
1927 x_return_status OUT NOCOPY VARCHAR2 )
1928 IS
1929 L_API_NAME CONSTANT VARCHAR2(30) := 'Get_WO_Cost';
1930 L_DEBUG_KEY CONSTANT VARCHAR2(100) := 'ahl.plsql.' || G_PKG_NAME || '.' || L_API_NAME;
1931
1932 -- Define Local Cursors
1933 CURSOR c_cost_csr(x_session_id IN NUMBER, x_id IN NUMBER, x_prg_id IN NUMBER) IS
1934 /*SELECT OBJECT_ID, SUM (ACTUAL_COST) ACTUAL_COST,
1935 SUM (ESTIMATED_COST) ESTIMATED_COST
1936 FROM AHL_VWP_ROLLUP_COSTS_V
1937 WHERE GROUP_ID = x_session_id
1938 AND OBJECT_ID = x_id
1939 AND PROGRAM_APPLICATION_ID = x_prg_id
1940 GROUP BY OBJECT_ID; */
1941
1942 SELECT *
1943 FROM AHL_VWP_ROLLUP_COSTS_V
1944 WHERE GROUP_ID = x_session_id
1945 AND OBJECT_ID = x_id
1946 AND PROGRAM_APPLICATION_ID = x_prg_id;
1947
1948 c_cost_csr_rec c_cost_csr%ROWTYPE;
1949
1950 BEGIN
1951 ------------------------Initialize Body------------------------------------
1952
1953 IF (l_log_procedure >= l_log_current_level) THEN
1954 fnd_log.string(l_log_procedure,
1955 L_DEBUG_KEY ||'.begin',
1956 'At the start of PL SQL procedure.' ||
1957 'Value for Session ID : ' || p_Session_Id ||
1961
1958 'Value for Object ID : ' || p_id ||
1959 'Value for Program Application ID : ' || p_program_id );
1960 END IF;
1962 -- Standard start of API savepoint
1963 SAVEPOINT Get_WO_Cost;
1964
1965 -- Initialize API return status to success
1966 x_return_status := FND_API.G_RET_STS_SUCCESS;
1967
1968 OPEN c_cost_csr(p_session_id, p_id, p_program_id);
1969 FETCH c_cost_csr INTO c_cost_csr_rec;
1970 IF c_cost_csr%FOUND THEN
1971 x_actual_cost := nvl(c_cost_csr_rec.actual_cost,0);
1972 x_estimated_cost := nvl(c_cost_csr_rec.estimated_cost,0);
1973
1974 ELSE
1975 x_actual_cost := 0;
1976 x_estimated_cost := 0;
1977
1978 END IF;
1979 CLOSE c_cost_csr;
1980
1981 x_return_status := FND_API.G_RET_STS_SUCCESS;
1982
1983 ------------------------End of API Body------------------------------------
1984
1985 IF (l_log_procedure >= l_log_current_level) THEN
1986 fnd_log.string(l_log_procedure,
1987 L_DEBUG_KEY ||'.end',
1988 'At the end of PL SQL procedure. Return Status = ' || x_return_status ||
1989 'Value for Actual Cost : ' || x_Actual_Cost ||
1990 'Value for Estimated Cost : ' || x_Estimated_Cost );
1991 END IF;
1992
1993 ------------------------Terminate API Body------------------------------------
1994 END Get_WO_Cost;
1995
1996
1997
1998
1999 --------------------------------------------------------------------------
2000 -- Procedure to rollup costs of Visit/MR/Task from Costing table
2001 -- 'CST_EAM_HIERARCHY_SNAPSHOT' and inserting in CST_EAM_ROLLUP_COSTS --
2002 --------------------------------------------------------------------------
2003 PROCEDURE Rollup_MR_Cost_Hierarchy(
2004 p_api_version IN NUMBER := 1.0,
2005 p_init_msg_list IN VARCHAR2 := Fnd_Api.G_FALSE,
2006 p_commit IN VARCHAR2 := Fnd_Api.G_FALSE,
2007 p_validation_level IN NUMBER := Fnd_Api.G_VALID_LEVEL_FULL,
2008 p_visit_id IN NUMBER,
2009 p_MR_session_Id IN NUMBER,
2010 p_cost_session_id IN NUMBER,
2011 x_return_status OUT NOCOPY VARCHAR2)
2012 IS
2013 -- Define Local Variables
2014 l_cost_session_id NUMBER;
2015 l_MR_session_id NUMBER;
2016 l_msg_count NUMBER;
2017
2018 l_return_status VARCHAR2(1);
2019 l_msg_data VARCHAR2(2000);
2020
2021 -- yazhou 22Sept2005 starts
2022 -- Bug fix#4617326
2023 l_entity_id_tab CSTPECEP.wip_entity_id_type;
2024 l_index NUMBER;
2025
2026 L_API_NAME CONSTANT VARCHAR2(30) := 'Rollup_MR_Cost_Hierarchy';
2027 L_DEBUG_KEY CONSTANT VARCHAR2(100) := 'ahl.plsql.' || G_PKG_NAME || '.' || L_API_NAME;
2028
2029 -- yazhou 22Sept2005 ends
2030
2031 -- Define Local Cursors
2032 -- To find out all visit related details
2033 CURSOR c_visit_details (x_id IN NUMBER) IS
2034 SELECT * FROM AHL_VISITS_VL
2035 WHERE VISIT_ID = x_id;
2036 visit_rec c_visit_details%ROWTYPE;
2037
2038 -- yazhou 22Sept2005 starts
2039 -- yazhou 19Jun200 starts
2040 -- Bug fix#5239507
2041 -- Should not call Compute_Job_Estimate for closed workorders (status '12')
2042
2043 -- yazhou 27-Jun-2006 starts
2044 -- fix for bug#5377347, to include cost of the canceled workorder (7-Cancelled)
2045 -- Since one task may map to multiple canceled workorders, query for the latest one
2046
2047 -- To find out all wip enities for the current visit
2048 CURSOR c_wip_jobs (x_id IN NUMBER) IS
2049 SELECT wip_entity_id
2050 FROM ahl_workorders
2051 WHERE visit_id = x_id AND STATUS_CODE NOT IN ('22','12');
2052
2053 -- yazhou 27-Jun-2006 ends
2054
2055 -- yazhou 19Jun200 ends
2056
2057 -- yazhou 22Sept2005 starts
2058
2059 c_wip_jobs_rec c_wip_jobs%ROWTYPE;
2060
2061
2062 BEGIN
2063
2064 IF (l_log_procedure >= l_log_current_level) THEN
2065 fnd_log.string(l_log_procedure,
2066 L_DEBUG_KEY ||'.begin',
2067 'At the start of PL SQL procedure.' ||
2068 'Value of Cost Session ID : ' || p_cost_session_id ||
2069 'Value of MR Session ID : ' || p_MR_session_id );
2070 END IF;
2071
2072 -- Standard start of API savepoint
2073 SAVEPOINT Rollup_MR_Cost_Hierarchy;
2074
2075 -- Initialize API return status to success
2076 x_return_status := FND_API.G_RET_STS_SUCCESS;
2077
2078 OPEN c_visit_details(p_visit_id);
2079 FETCH c_visit_details INTO visit_rec;
2080 CLOSE c_visit_details;
2081
2082 -- Call for CST_EAMCOST_PUB.Delete_eamPerBal API
2083 -- To reset WIP_EAM_PERIOD_BALANCES for the work
2084 -- orders before calling the Work Order Cost Rollup API
2085
2086 -- yazhou 22Sept2005 starts
2087 -- Bug fix#4617326
2088
2089 l_index :=1;
2090
2091 OPEN c_wip_jobs(p_visit_id);
2092 LOOP
2093 FETCH c_wip_jobs INTO c_wip_jobs_rec;
2094 EXIT WHEN c_wip_jobs%NOTFOUND;
2095 IF c_wip_jobs%FOUND THEN
2096
2097 l_entity_id_tab(l_index) := c_wip_jobs_rec.wip_entity_id;
2098 l_index := l_index +1;
2099 END IF;
2100
2101 END LOOP;
2102 CLOSE c_wip_jobs;
2103
2104 IF (l_log_statement >= l_log_current_level) THEN
2105 fnd_log.string(l_log_statement,
2106 L_DEBUG_KEY,
2110 CST_EAMCOST_PUB.Delete_eamPerBal (
2107 'Before Calling CST_EAMCOST_PUB.Delete_eamPerBal' );
2108 END IF;
2109
2111 p_api_version => 1.0,
2112 p_init_msg_list => Fnd_Api.G_FALSE,
2113 p_commit => Fnd_Api.G_FALSE,
2114 p_validation_level => Fnd_Api.G_VALID_LEVEL_FULL,
2115
2116 x_return_status => l_return_status,
2117 x_msg_count => l_msg_count,
2118 x_msg_data => l_msg_data,
2119
2120 p_entity_id_tab => l_entity_id_tab,
2121 p_org_id => visit_rec.organization_id,
2122 p_type => 1);
2123
2124 IF (l_log_statement >= l_log_current_level) THEN
2125 fnd_log.string(l_log_statement,
2126 L_DEBUG_KEY,
2127 'After Calling CST_EAMCOST_PUB.Delete_eamPerBal, Return Status = ' || l_return_status );
2128 END IF;
2129
2130 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2131
2132 IF (l_log_statement >= l_log_current_level) THEN
2133 fnd_log.string(l_log_statement,
2134 L_DEBUG_KEY,
2135 'Errors from CST_EAMCOST_PUB.Delete_eamPerBal API -COST SESSION' || l_msg_count );
2136 END IF;
2137
2138 RAISE Fnd_Api.g_exc_error;
2139 END IF;
2140
2141 -- yazhou 22Sept2005 ends
2142
2143 -- Call for CST_EAMCOST_PUB.Compute_Job_Estimate API
2144 -- To generate the estimates for the work orders before
2145 -- calling the Work Order Cost Rollup API
2146
2147 OPEN c_wip_jobs(p_visit_id);
2148 LOOP
2149 FETCH c_wip_jobs INTO c_wip_jobs_rec;
2150 EXIT WHEN c_wip_jobs%NOTFOUND;
2151 IF c_wip_jobs%FOUND THEN
2152
2153 IF (l_log_statement >= l_log_current_level) THEN
2154 fnd_log.string(l_log_statement,
2155 L_DEBUG_KEY,
2156 'Before calling CST_EAMCOST_PUB.Compute_Job_Estimate' );
2157 END IF;
2158
2159 CST_EAMCOST_PUB.Compute_Job_Estimate (
2160 p_api_version => 1.0,
2161 p_init_msg_list => Fnd_Api.G_FALSE,
2162 p_commit => Fnd_Api.G_FALSE,
2163 p_validation_level => Fnd_Api.G_VALID_LEVEL_FULL,
2164 p_debug => 'N',
2165 p_wip_entity_id => c_wip_jobs_rec.wip_entity_id,
2166 p_user_id => fnd_global.user_id,
2167 p_request_id => NULL,
2168 p_prog_id => NULL,
2169 p_prog_app_id => fnd_global.PROG_APPL_ID,
2170 p_login_id => fnd_global.login_id,
2171 x_return_status => l_return_status,
2172 x_msg_count => l_msg_count,
2173 x_msg_data => l_msg_data);
2174
2175 IF (l_log_statement >= l_log_current_level) THEN
2176 fnd_log.string(l_log_statement,
2177 L_DEBUG_KEY,
2178 'After calling CST_EAMCOST_PUB.Compute_Job_Estimate, Return Status = ' || l_return_status);
2179 END IF;
2180
2181 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2182
2183 IF (l_log_statement >= l_log_current_level) THEN
2184 fnd_log.string(l_log_statement,
2185 L_DEBUG_KEY,
2186 'Errors from CST_EAMCOST_PUB.Delete_eamPerBal API -COST SESSION' || l_msg_count );
2187 END IF;
2188
2189 CLOSE c_wip_jobs;
2190 RAISE Fnd_Api.g_exc_error;
2191 END IF;
2192
2193 END IF;
2194
2195 END LOOP;
2196 CLOSE c_wip_jobs;
2197
2198 IF (l_log_statement >= l_log_current_level) THEN
2199 fnd_log.string(l_log_statement,
2200 L_DEBUG_KEY,
2201 'Before Calling CST_EAMCOST_PUB.Rollup_WorkOrderCost-COST SESSION' );
2202 END IF;
2203
2204
2205 -- Call for CST_EAMCOST_PUB.Rollup_WorkOrderCost API
2206 -- FOR VISIT COST STRUCTURE
2207 -- To calculate the cumulative costs of each hierarchy node
2208 -- and populate the CST_EAM_ROLLUP_COSTS with that information.
2209
2210 CST_EAMCOST_PUB.Rollup_WorkOrderCost
2211 (
2212 p_api_version => 1.0,
2213 p_init_msg_list => Fnd_Api.G_FALSE,
2214 p_commit => Fnd_Api.G_FALSE,
2215 p_group_id => p_cost_session_id,
2216 p_organization_id => visit_rec.organization_id,
2217 p_user_id => Fnd_Global.USER_ID,
2218 p_prog_appl_id => Fnd_Global.PROG_APPL_ID,
2219 x_return_status => l_return_status
2220
2221 );
2222
2223 IF (l_log_statement >= l_log_current_level) THEN
2224 fnd_log.string(l_log_statement,
2225 L_DEBUG_KEY,
2226 'After Calling CST_EAMCOST_PUB.Rollup_WorkOrderCost-COST SESSION, Return Status = ' || l_return_status );
2227 END IF;
2228
2229
2230 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2231
2232 IF (l_log_statement >= l_log_current_level) THEN
2233 fnd_log.string(l_log_statement,
2234 L_DEBUG_KEY,
2235 'Errors from CST_EAMCOST_PUB.Rollup_WorkOrderCosts API -COST SESSION' || l_msg_count );
2236 END IF;
2237
2241 END IF;
2238 RAISE Fnd_Api.g_exc_error;
2239 ELSE
2240 FND_MSG_PUB.Initialize;
2242
2243 IF (l_log_statement >= l_log_current_level) THEN
2244 fnd_log.string(l_log_statement,
2245 L_DEBUG_KEY,
2246 'Before Calling CST_EAMCOST_PUB.Rollup_WorkOrderCost-MR SESSION' );
2247 END IF;
2248
2249 -- Call for CST_EAMCOST_PUB.Rollup_WorkOrderCost API
2250 -- FOR WORKORDER SCHEDULLING DEPENDENCIES
2251 -- To calculate the cumulative costs of each hierarchy node
2252 -- and populate the CST_EAM_ROLLUP_COSTS with that information.
2253
2254
2255 CST_EAMCOST_PUB.Rollup_WorkOrderCost
2256 (
2257 p_api_version => 1.0,
2258 p_init_msg_list => Fnd_Api.G_FALSE,
2259 p_commit => Fnd_Api.G_FALSE,
2260 p_group_id => p_MR_session_id,
2261 p_organization_id => visit_rec.organization_id,
2262 p_user_id => Fnd_Global.USER_ID,
2263 p_prog_appl_id => Fnd_Global.PROG_APPL_ID,
2264 x_return_status => l_return_status
2265
2266 );
2267
2268 IF (l_log_statement >= l_log_current_level) THEN
2269 fnd_log.string(l_log_statement,
2270 L_DEBUG_KEY,
2271 'After Calling CST_EAMCOST_PUB.Rollup_WorkOrderCost-MR SESSION, Return Status = ' || l_return_status);
2272 END IF;
2273
2274 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2275
2276 IF (l_log_statement >= l_log_current_level) THEN
2277 fnd_log.string(l_log_statement,
2278 L_DEBUG_KEY,
2279 'Errors from CST_EAMCOST_PUB.Rollup_WorkOrderCosts API -MR SESSION' || l_msg_count);
2280 END IF;
2281
2282 RAISE Fnd_Api.g_exc_error;
2283 ELSE
2284 FND_MSG_PUB.Initialize;
2285 END IF;
2286
2287 ------------------------End of API Body------------------------------------
2288
2289 IF (l_log_procedure >= l_log_current_level) THEN
2290 fnd_log.string(l_log_procedure,
2291 L_DEBUG_KEY ||'.end',
2292 'At the end of PL SQL procedure. Return Status = ' || l_return_status);
2293 END IF;
2294
2295 --x_return_status := FND_API.G_RET_STS_SUCCESS;
2296
2297 ------------------------Terminate API Body------------------------------------
2298 END Rollup_MR_Cost_Hierarchy;
2299
2300
2301 --------------------------------------------------------------------------
2302 -- Procedure to push Visit/MR/Task Schedulling dependencies and
2303 -- Cost Hierarchy structure by inserting in Costing tables --
2304 --------------------------------------------------------------------------
2305 PROCEDURE Push_MR_Cost_Hierarchy(
2306 p_api_version IN NUMBER := 1.0,
2307 p_init_msg_list IN VARCHAR2 := Fnd_Api.G_FALSE,
2308 p_commit IN VARCHAR2 := Fnd_Api.G_FALSE,
2309 p_validation_level IN NUMBER := Fnd_Api.G_VALID_LEVEL_FULL,
2310 p_visit_id IN NUMBER,
2311 x_cost_session_id OUT NOCOPY NUMBER,
2312 x_MR_session_id OUT NOCOPY NUMBER,
2313 x_return_status OUT NOCOPY VARCHAR2 )
2314 IS
2315 -- Define Local Variables
2316 l_cost_session_id NUMBER;
2317 l_MR_session_id NUMBER;
2318 l_msg_count NUMBER;
2319
2320 l_msg_data VARCHAR2(2000);
2321 l_return_status VARCHAR2(1);
2322
2323 L_API_NAME CONSTANT VARCHAR2(30) := 'Push_MR_Cost_Hierarchy';
2324 L_DEBUG_KEY CONSTANT VARCHAR2(100) := 'ahl.plsql.' || G_PKG_NAME || '.' || L_API_NAME;
2325
2326 BEGIN
2327 ------------------------Initialize Body------------------------------------
2328
2329 IF (l_log_procedure >= l_log_current_level) THEN
2330 fnd_log.string(l_log_procedure,
2331 L_DEBUG_KEY ||'.begin',
2332 'At the start of PL SQL procedure. Visit Id = ' || p_visit_id);
2333 END IF;
2334
2335 -- Standard start of API savepoint
2336 SAVEPOINT Push_MR_Cost_Hierarchy;
2337
2338 -- Initialize API return status to success
2339 x_return_status := FND_API.G_RET_STS_SUCCESS;
2340
2341 IF (l_log_statement >= l_log_current_level) THEN
2342 fnd_log.string(l_log_statement,
2343 L_DEBUG_KEY,
2344 'Before Calling Create_WO_Cost_Structure: Value of Visit ID : ' || p_visit_id );
2345 END IF;
2346
2347 -- Calling Create_WO_Cost_Structure API
2348 Create_WO_Cost_Structure
2349 (
2350 p_api_version => 1.0,
2351 p_init_msg_list => Fnd_Api.G_FALSE,
2352 p_commit => Fnd_Api.G_FALSE,
2353 p_validation_level => Fnd_Api.G_VALID_LEVEL_FULL,
2354 p_visit_id => p_visit_id,
2355 x_cost_session_id => l_cost_session_id,
2356 x_return_status => l_return_status,
2357 x_msg_count => l_msg_count,
2358 x_msg_data => l_msg_data
2359 );
2360
2361 IF (l_log_statement >= l_log_current_level) THEN
2362 fnd_log.string(l_log_statement,
2363 L_DEBUG_KEY,
2364 'After Calling AHL_VWP_CST_WO_PVT.Create_WO_Cost_Structure, Return Status = ' || l_return_status);
2365 END IF;
2366
2367 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2368
2369 IF (l_log_statement >= l_log_current_level) THEN
2373 END IF;
2370 fnd_log.string(l_log_statement,
2371 L_DEBUG_KEY,
2372 'Errors from Create_WO_Cost_Structure API' || l_msg_count) ;
2374
2375 RAISE Fnd_Api.g_exc_error;
2376 END IF;
2377
2378 IF (l_log_statement >= l_log_current_level) THEN
2379 fnd_log.string(l_log_statement,
2380 L_DEBUG_KEY,
2381 'Before Calling AHL_VWP_CST_WO_PVT.Create_WO_Dependencies' );
2382 END IF;
2383
2384 Create_WO_Dependencies
2385 (
2386 p_api_version => 1.0,
2387 p_init_msg_list => Fnd_Api.G_FALSE,
2388 p_commit => Fnd_Api.G_FALSE,
2389 p_validation_level => Fnd_Api.G_VALID_LEVEL_FULL,
2390 p_visit_id => p_visit_id,
2391 x_MR_session_id => l_MR_session_id,
2392 x_return_status => l_return_status,
2393 x_msg_count => l_msg_count,
2394 x_msg_data => l_msg_data
2395 );
2396
2397 IF (l_log_statement >= l_log_current_level) THEN
2398 fnd_log.string(l_log_statement,
2399 L_DEBUG_KEY,
2400 'After Calling AHL_VWP_CST_WO_PVT.Create_WO_Dependencies, Return Status = ' || l_return_status);
2401 END IF;
2402
2403 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2404
2405 IF (l_log_statement >= l_log_current_level) THEN
2406 fnd_log.string(l_log_statement,
2407 L_DEBUG_KEY,
2408 'Errors from Create_WO_Dependencies API' || l_msg_count );
2409 END IF;
2410
2411 RAISE Fnd_Api.g_exc_error;
2412 END IF;
2413
2414 x_MR_session_id := l_MR_session_id;
2415 x_cost_session_id := l_cost_session_id;
2416
2417 x_return_status := FND_API.G_RET_STS_SUCCESS;
2418
2419 ------------------------End of API Body------------------------------------
2420 IF (l_log_procedure >= l_log_current_level) THEN
2421 fnd_log.string(l_log_procedure,
2422 L_DEBUG_KEY ||'.end',
2423 'At the end of PL SQL procedure. Return Status = ' || l_return_status ||
2424 'x_MR_session_id = ' || x_MR_session_id ||
2425 'x_cost_session_id = ' || x_cost_session_id );
2426 END IF;
2427
2428 ------------------------Terminate API Body------------------------------------
2429 END Push_MR_Cost_Hierarchy;
2430
2431
2432 ---------------------------------------------------------------------------------
2433 -- Procedure to calculate and get Visit/MR/Task actual and estimated profit/loss--
2434 ----------------------------------------------------------------------------------
2435 PROCEDURE Get_Profit_or_Loss(
2436 p_actual_price IN NUMBER,
2437 p_estimated_price IN NUMBER,
2438 p_actual_cost IN NUMBER,
2439 p_estimated_cost IN NUMBER,
2440 x_actual_profit OUT NOCOPY NUMBER,
2441 x_estimated_profit OUT NOCOPY NUMBER,
2442 x_return_status OUT NOCOPY VARCHAR2)
2443 IS
2444 L_API_NAME CONSTANT VARCHAR2(30) := 'Get_Profit_or_Loss';
2445 L_DEBUG_KEY CONSTANT VARCHAR2(100) := 'ahl.plsql.' || G_PKG_NAME || '.' || L_API_NAME;
2446
2447 BEGIN
2448 ------------------------Initialize Body------------------------------------
2449
2450 IF (l_log_procedure >= l_log_current_level) THEN
2451 fnd_log.string(l_log_procedure,
2452 L_DEBUG_KEY ||'.begin',
2453 'At the start of PL SQL procedure.' ||
2454 'Actual Price : ' || p_actual_price || '***' || 'Actual Cost : ' || p_actual_cost ||
2455 'Estimated Price : ' || p_estimated_price || '***' || 'Actual Price : ' || p_actual_price );
2456 END IF;
2457
2458 -- Standard start of API savepoint
2459 SAVEPOINT Get_Profit_or_Loss;
2460
2461 -- Initialize API return status to success
2462 x_return_status := FND_API.G_RET_STS_SUCCESS;
2463
2464 IF p_actual_price IS NOT NULL AND p_actual_cost IS NOT NULL THEN
2465 x_actual_profit := p_actual_price - p_actual_cost;
2466 ELSE
2467 x_actual_profit := NULL;
2468 END IF;
2469
2470 IF p_estimated_price IS NOT NULL AND p_estimated_cost IS NOT NULL THEN
2471 x_estimated_profit := p_estimated_price - p_estimated_cost;
2472 ELSE
2473 x_estimated_profit := NULL;
2474 END IF;
2475
2476 ------------------------End of API Body------------------------------------
2477 IF (l_log_procedure >= l_log_current_level) THEN
2478 fnd_log.string(l_log_procedure,
2479 L_DEBUG_KEY ||'.end',
2480 'At the end of PL SQL procedure. Return Status = ' || x_return_status ||
2481 'Actual Profit : ' || x_actual_profit ||
2482 'Estimated Profit : ' || x_estimated_profit );
2483 END IF;
2484
2485 --x_return_status := FND_API.G_RET_STS_SUCCESS;
2486
2487 ------------------------Terminate API Body------------------------------------
2488 END Get_Profit_or_Loss;
2489
2490 ----------------------------------------------------------------------
2491 -- END: Defining procedures BODY --
2492 ----------------------------------------------------------------------
2493
2494 END AHL_VWP_COST_PVT;