[Home] [Help]
PACKAGE BODY: APPS.AHL_VWP_MR_CST_PR_PVT
Source
1 PACKAGE BODY AHL_VWP_MR_CST_PR_PVT AS
2 /* $Header: AHLVMCPB.pls 120.4 2006/06/07 08:27:20 anraj noship $ */
3
4 G_PKG_NAME VARCHAR2(30):='AHL_VWP_MR_CST_PR_PVT';
5 G_MODULE_NAME VARCHAR2(250):='ahl.plsql.AHL_VWP_MR_CST_PR_PVT.';
6 G_DEBUG VARCHAR2(1) := AHL_DEBUG_PUB.is_log_enabled;
7 G_APP_NAME CONSTANT VARCHAR2(3) := 'AHL';
8
9 -----------------------------------------------------------
10 -- PACKAGE
11 -- AHL_VWP_MR_CST_PR_PVT
12 --
13 -- PROCEDURES
14 --
15 -- NOTES
16 --
17 -- HISTORY
18 -- This package is Created By Rajanath Tadikonda (rtadikon) to implement
19 -- Costing.
20 --
21 --
22 -----------------------------------------------------------------
23
24 ---------------------------------------------------------------------
25 -- PROCEDURE
26 -- ESTIMATE_MR_COST
27 -- ESTIMATE_MR_PRICE
28 -- Get_MR_Items_No_Price
29 -- Get_MR_Cost_Details
30 ---------------------------------------------------------------------
31 PROCEDURE LOG_MESSAGE
32 (p_message_text in varchar2,
33 p_api_name in varchar2
34 )
35 as
36 l_api_name varchar2(10);
37 begin
38 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
39 fnd_log.string
40 (
41 fnd_log.level_statement,
42 G_MODULE_NAME||p_api_name,
43 p_message_text
44 );
45 END IF;
46 end LOG_MESSAGE;
47
48 PROCEDURE POPULATE_COST_PRICE_REC
49 (
50 p_x_cost_price_rec IN OUT NOCOPY AHL_VWP_VISIT_CST_PR_PVT.cost_price_rec_type
51 )
52 AS
53 Cursor c_task_csr(c_visit_task_id in number)
54 Is
55 Select VISIT_ID,
56 VISIT_TASK_ID,
57 MR_ID,
58 ACTUAL_COST,
59 ESTIMATED_PRICE,
60 PRICE_LIST_ID,
61 SERVICE_REQUEST_ID,
62 VISIT_TASK_NAME,
63 VISIT_TASK_NUMBER
64 From ahl_visit_tasks_VL
65 where visit_task_id=c_visit_task_id;
66
67 l_task_rec c_task_csr%rowtype;
68
69 l_visit_task_id AHL_VISIT_TASKS_B.visit_task_id%TYPE;
70
71 Cursor c_customer (c_incident_id in number)
72 Is
73 select customer_id
74 from CS_INCIDENTS_ALL_B
75 where incident_id=c_incident_id;
76
77
78 Cursor c_visit_csr(c_visit_id in number)
79 Is
80 Select VISIT_ID,
81 ACTUAL_PRICE,
82 ESTIMATED_PRICE,
83 PRICE_LIST_ID,
84 SERVICE_REQUEST_ID,
85 OUTSIDE_PARTY_FLAG,
86 ORGANIZATION_ID,
87 START_DATE_TIME,
88 CLOSE_DATE_TIME
89 From ahl_visits_vl
90 where visit_id=c_visit_id;
91 l_visit_rec c_visit_csr%rowtype;
92
93 Cursor c_work_csr(c_visit_task_id in number)
94 Is
95 Select a.WORKORDER_ID,a.MASTER_WORKORDER_FLAG
96 From ahl_workorders a
97 where a.visit_task_id=c_visit_task_id
98 and a.status_code <>'22' and a.status_code <>'7'
99 and a.master_workorder_flag='Y';
100 l_work_rec c_work_csr%rowtype;
101
102 -- AnRaj: Changed query for fixing the prformance bug# 4919272
103 Cursor c_mr_csr(c_mr_header_id in number)
104 Is
105 /*
106 Select MR_HEADER_ID,TITLE,DESCRIPTION,BILLING_ITEM_ID,BILLING_ITEM
107 From ahl_mr_headers_v
108 where mr_header_id=c_mr_header_id;
109 */
110 select MR_HEADER_ID,
111 TITLE,
112 DESCRIPTION,
113 BILLING_ITEM_ID,
114 ( SELECT DISTINCT CONCATENATED_SEGMENTS
115 FROM MTL_SYSTEM_ITEMS_KFV
116 WHERE INVENTORY_ITEM_ID=BILLING_ITEM_ID) BILLING_ITEM
117 from AHL_MR_HEADERS_APP_V
118 where mr_header_id=c_mr_header_id ;
119 l_mr_rec c_mr_csr%rowtype;
120
121
122 BEGIN
123 open c_task_csr(p_x_cost_price_rec.visit_task_id);
124 fetch c_task_csr into l_task_rec;
125 close c_task_csr;
126
127 open c_mr_csr(l_task_rec.mr_id);
128 fetch c_mr_csr into l_mr_rec;
129 close c_mr_csr;
130
131
132 open c_visit_csr(l_task_rec.visit_id);
133 fetch c_visit_csr into l_visit_rec;
134 close c_visit_csr;
135
136 open c_customer (l_visit_rec.service_request_id);
137 fetch c_customer into p_x_cost_price_rec.customer_id;
138 close c_customer;
139
140 open c_work_csr(p_x_cost_price_rec.visit_task_id);
141 fetch c_work_csr into l_work_rec;
142 close c_work_csr;
143
144 AHL_VWP_RULES_PVT.check_currency_for_costing
145 (p_visit_id =>l_task_rec.visit_id,
146 x_currency_code =>p_x_cost_price_rec.currency
147 );
148
149 p_x_cost_price_rec.VISIT_ID :=l_task_rec.visit_id;
150 p_x_cost_price_rec.MR_ID :=l_task_rec.mr_id;
151 --p_x_cost_price_rec.ACTUAL_COST :=l_task_rec.ACTUAL_COST;
152 --p_x_cost_price_rec.ESTIMATED_COST :=l_task_rec.
153 --p_x_cost_price_rec.ACTUAL_PRICE :=l_task_rec.
154 p_x_cost_price_rec.ESTIMATED_PRICE :=l_task_rec.estimated_price;
155 p_x_cost_price_rec.OUTSIDE_PARTY_FLAG :=l_visit_rec.OUTSIDE_PARTY_FLAG;
156 p_x_cost_price_rec.PRICE_LIST_ID :=l_task_rec.PRICE_LIST_ID;
157 p_x_cost_price_rec.SERVICE_REQUEST_ID :=l_task_rec.SERVICE_REQUEST_ID;
158 p_x_cost_price_rec.ORGANIZATION_ID :=l_visit_rec.ORGANIZATION_ID;
159 p_x_cost_price_rec.VISIT_START_DATE :=l_visit_rec.START_DATE_TIME;
160 p_x_cost_price_rec.VISIT_END_DATE :=l_visit_rec.CLOSE_DATE_TIME;
161 --p_x_cost_price_rec.TASK_START_DATE :=l_task_rec.TASK_START_DATE;
162 --p_x_cost_price_rec.TASK_END_DATE :=l_task_rec.TASK_END_DATE;
163 p_x_cost_price_rec.TASK_NAME :=l_task_rec.VISIT_TASK_NAME;
164 p_x_cost_price_rec.VISIT_TASK_NUMBER :=l_task_rec.VISIT_TASK_NUMBER;
165 p_x_cost_price_rec.MR_TITLE :=l_mr_rec.TITLE;
166 p_x_cost_price_rec.MR_DESCRIPTION :=l_mr_rec.DESCRIPTION;
167 p_x_cost_price_rec.BILLING_ITEM_ID :=l_mr_rec.BILLING_ITEM_ID;
168 p_x_cost_price_rec.ITEM_NAME :=l_mr_rec.BILLING_ITEM;
169 --p_x_cost_price_rec.ITEM_DESCRIPTION :=l_mr_rec.item_Description;
170 p_x_cost_price_rec.WORKORDER_ID :=l_work_rec.workorder_id;
171 p_x_cost_price_rec.MASTER_WO_FLAG :=l_work_rec.MASTER_WORKORDER_FLAG;
172
173 END;
174
175 PROCEDURE VALIDATE_EST_MR_COST
176 (
177 p_cost_price_rec IN OUT NOCOPY AHL_VWP_VISIT_CST_PR_PVT.cost_price_rec_type,
178 x_return_status OUT NOCOPY VARCHAR2
179 )
180 AS
181 BEGIN
182 x_return_status:=FND_API.G_RET_STS_SUCCESS;
183
184 IF p_cost_price_rec.visit_task_id IS NULL
185 THEN
186 FND_MESSAGE.SET_NAME('AHL','AHL_VWP_ESTMCOST_TASK_ID_NULL');
187 FND_MSG_PUB.ADD;
188 x_return_status:=Fnd_Api.g_ret_sts_error;
189 END IF;
190 /*
191 IF p_cost_price_rec.mr_session_id IS NULL
192 THEN
193 FND_MESSAGE.SET_NAME('AHL','AHL_VWP_ESTMCOST_MRSS_ID_NULL');
194 FND_MSG_PUB.ADD;
195 END IF;
196
197 IF p_cost_price_rec.cost_session_id IS NULL
198 THEN
199 FND_MESSAGE.SET_NAME('AHL','AHL_VWP_ESTMCOST_MCSS_ID_NULL');
200 FND_MSG_PUB.ADD;
201 END IF;
202 */
203 END VALIDATE_EST_MR_COST;
204
205 PROCEDURE ESTIMATE_MR_Cost (
206 p_api_version IN NUMBER,
207 p_init_msg_list IN VARCHAR2 := Fnd_Api.g_false,
208 p_commit IN VARCHAR2 :=Fnd_Api.g_false,
209 p_validation_level IN NUMBER := Fnd_Api.g_valid_level_full,
210 p_module_type IN VARCHAR2,
211 p_x_cost_price_rec IN OUT NOCOPY AHL_VWP_VISIT_CST_PR_PVT.cost_price_rec_type,
212 x_return_status OUT NOCOPY VARCHAR2,
213 x_msg_count OUT NOCOPY NUMBER,
214 x_msg_data OUT NOCOPY VARCHAR2
215 )
216 IS
217
218 -- Cursor to retrieve visit info
219 Cursor c_visit_csr(c_visit_task_id in number)
220 Is
221 Select v.visit_id, v.any_task_chg_flag
222 From ahl_visits_b V, ahl_visit_tasks_b T
223 where T.visit_task_id=c_visit_task_id
224 AND V.visit_id = T.visit_id;
225
226 l_visit_rec c_visit_csr%rowtype;
227
228 -- Local Variables
229
230 -- Standard in/out parameters
231 l_api_name VARCHAR2(30) := 'ESTIMATE_MR_COST ';
232 l_api_version NUMBER := 1.0;
233 l_num_rec NUMBER;
234 l_msg_count NUMBER;
235 l_msg_data VARCHAR2(2000);
236 l_return_status VARCHAR2(1);
237 l_init_msg_list VARCHAR2(10):=FND_API.G_FALSE;
238 l_commit VARCHAR2(30) := Fnd_Api.G_FALSE;
239
240
241 l_release_visit_required VARCHAR2(1) :='N';
242
243 l_cost_price_rec AHL_VWP_VISIT_CST_PR_PVT.cost_price_rec_type;
244
245 BEGIN
246
247 SAVEPOINT ESTIMATE_MR_Cost_PVT;
248
249 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version,
250 p_api_version,
251 l_api_name,G_PKG_NAME)
252 THEN
253 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
254 END IF;
255
256 IF FND_API.to_boolean(p_init_msg_list)
257 THEN
258 FND_MSG_PUB.initialize;
259 END IF;
260
261 -- Initialize API return status to success
262
263 x_return_status := FND_API.G_RET_STS_SUCCESS;
264
265 l_cost_price_rec:= p_x_cost_price_rec;
266
267
268 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
269 THEN
270 FND_LOG.STRING
271 (
272 FND_LOG.LEVEL_PROCEDURE,
273 G_MODULE_NAME||l_api_name,
274 'START OF '||L_API_NAME
275 );
276 END IF;
277
278
279 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
280
281 fnd_log.string
282
283 (
284
285 fnd_log.level_statement,
286
287 'ahl.plsql.AHL_VWP_MR_CST_PR_PVT.estimate_MR_cost',
288
289 'Got request for estimating cost of Visit Task ID : ' || p_x_cost_price_rec.visit_task_id
290
291 );
292
293 fnd_log.string
294
295 (
296
297 fnd_log.level_statement,
298
299 'ahl.plsql.AHL_VWP_MR_CST_PR_PVT.estimate_MR_cost',
300
301 'input mr session id : ' || p_x_cost_price_rec.mr_session_id
302
303 );
304
305 fnd_log.string
306
307 (
308
309 fnd_log.level_statement,
310
311 'ahl.plsql.AHL_VWP_MR_CST_PR_PVT.estimate_MR_cost',
312
313 'input cost session id : ' || p_x_cost_price_rec.cost_session_id
314
315 );
316
317 END IF;
318
319
320 VALIDATE_EST_MR_COST
321 (p_cost_price_rec =>l_cost_price_rec,
322 x_return_status =>x_return_status
323 );
324
325 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
326 IF G_DEBUG='Y' THEN
327 AHL_DEBUG_PUB.Debug( G_MODULE_NAME||l_api_name ||' Validate Estimate MR Cost errored');
328 END IF;
329 RAISE Fnd_Api.G_EXC_ERROR;
330 END IF;
331
332
333 -- Retrieve visit information
334
335 open c_visit_csr(l_cost_price_rec.visit_task_id);
336 fetch c_visit_csr into l_visit_rec;
337 close c_visit_csr;
338
339 -- Need to release the visit only if this API is called from front-end direcly
340
341 IF p_module_type = 'JSP' THEN
342
343 AHL_VWP_VISIT_CST_PR_PVT.check_for_release_visit
344 (
345 p_visit_id =>l_visit_rec.visit_id,
346 x_release_visit_required =>l_release_visit_required
347 );
348
349 -- Release visit if required
350 IF l_release_visit_required ='Y' THEN
351
352 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
353 fnd_log.string
354 (
355 fnd_log.level_procedure,
356 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
357 'Before calling ahl vwp proj prod pvt.release visit'
358 );
359
360 END IF;
361
362 ahl_vwp_proj_prod_pvt.release_visit (
363 p_api_version => l_api_version,
364 p_init_msg_list => p_init_msg_list,
365 p_commit => l_commit,
366 p_validation_level => p_validation_level,
367 p_module_type => 'CST',
368 p_visit_id => l_visit_rec.visit_id,
369 x_return_status => l_return_status,
370 x_msg_count => l_msg_count,
371 x_msg_data => l_msg_data);
372
373 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
374 fnd_log.string
375 (
376 fnd_log.level_procedure,
377 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||': End API',
378 'After calling ahl vwp proj prod pvt.Release Visit task wo Return Status : '|| l_return_status
379 );
380 END IF;
381
382 -- Check Error Message stack.
383 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
384 l_msg_count := FND_MSG_PUB.count_msg;
385 IF l_msg_count > 0 THEN
386 RAISE FND_API.G_EXC_ERROR;
387 END IF;
388 END IF;
389
390 END IF; -- released required flag
391
392 END IF; --- p_module type = 'JSP'
393
394 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
395 fnd_log.string
396 (
397 fnd_log.level_statement, G_MODULE_NAME||l_api_name,
398 'Before Call to AHL_VWP_COST_PVT.estimate_wo_cost'
399 );
400 END IF;
401
402 AHL_VWP_COST_PVT.estimate_wo_cost
403 (p_x_cost_price_rec =>l_cost_price_rec,
404 x_return_status =>x_return_status);
405
406 IF FND_MSG_PUB.count_msg > 0
407 THEN
408 x_msg_count := l_msg_count;
409 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
410 RAISE FND_API.G_EXC_ERROR;
411 END IF;
412
413 IF FND_API.TO_BOOLEAN(p_commit) THEN
414 COMMIT;
415 END IF;
416
417
418 p_x_cost_price_rec.Is_Cst_Struc_updated := l_cost_price_rec.Is_Cst_Struc_updated;
419
420 p_x_cost_price_rec.cost_session_id := l_cost_price_rec.cost_session_id;
421
422 p_x_cost_price_rec.mr_session_id := l_cost_price_rec.mr_session_id;
423
424 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
425 THEN
426 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
427 G_MODULE_NAME||l_api_name,
428 'At the start of the procedure');
429 END IF;
430
431
432 EXCEPTION
433 WHEN Fnd_Api.g_exc_error THEN
434 ROLLBACK TO ESTIMATE_MR_Cost_PVT;
435 x_return_status := Fnd_Api.g_ret_sts_error;
436 Fnd_Msg_Pub.count_and_get (
437 p_encoded => Fnd_Api.g_false,
438 p_count => x_msg_count,
439 p_data => x_msg_data
440 );
441 WHEN Fnd_Api.g_exc_unexpected_error THEN
442 ROLLBACK TO ESTIMATE_MR_Cost_PVT;
443 x_return_status := Fnd_Api.g_ret_sts_unexp_error ;
444 Fnd_Msg_Pub.count_and_get (
445 p_encoded => Fnd_Api.g_false,
446 p_count => x_msg_count,
447 p_data => x_msg_data
448 );
449 WHEN OTHERS THEN
450 ROLLBACK TO ESTIMATE_MR_Cost_PVT;
451 x_return_status := Fnd_Api.g_ret_sts_unexp_error ;
452 IF Fnd_Msg_Pub.check_msg_level (Fnd_Msg_Pub.g_msg_lvl_unexp_error)
453 THEN
454 Fnd_Msg_Pub.add_exc_msg (G_PKG_NAME, l_api_name);
455 END IF;
456 Fnd_Msg_Pub.count_and_get (
457 p_encoded => Fnd_Api.g_false,
458 p_count => x_msg_count,
459 p_data => x_msg_data
460 );
461 END ESTIMATE_MR_COST;
462
463
464 ------
465 ------ Estimate MR Price
466 ------
467
468 PROCEDURE ESTIMATE_MR_PRICE (
469 p_api_version IN NUMBER,
470 p_init_msg_list IN VARCHAR2 := Fnd_Api.g_false,
471 p_commit IN VARCHAR2 :=Fnd_Api.g_false,
472 p_validation_level IN NUMBER := Fnd_Api.g_valid_level_full,
473 p_module_type IN VARCHAR2,
474 p_x_cost_price_rec IN OUT NOCOPY AHL_VWP_VISIT_CST_PR_PVT.cost_price_rec_type,
475 x_return_status OUT NOCOPY VARCHAR2,
476 x_msg_count OUT NOCOPY NUMBER,
477 x_msg_data OUT NOCOPY VARCHAR2
478 )
479 AS
480 -- Cursor to get task price and mr info
481
482 Cursor c_task_csr(c_visit_task_id in number)
483 Is
484 Select visit_task_id, visit_id,
485 actual_price,
486 estimated_price,
487 mr_id,
488 start_date_time,
489 end_date_time
490 From ahl_visit_tasks_b
491 where visit_task_id=c_visit_task_id;
492
493 l_task_rec c_task_csr%rowtype;
494
495 -- Cursor to retrieve visit info
496 Cursor c_visit_csr(c_visit_id in number)
497 Is
498 Select VISIT_ID,
499 ACTUAL_PRICE,
500 ESTIMATED_PRICE,
501 PRICE_LIST_ID,
502 SERVICE_REQUEST_ID,
503 OUTSIDE_PARTY_FLAG,
504 ORGANIZATION_ID,
505 START_DATE_TIME,
506 any_task_chg_flag
507 From ahl_visits_b
508 where visit_id=c_visit_id;
509
510 l_visit_rec c_visit_csr%rowtype;
511
512 -- Cursor to get all the immediate child of the given task
513 Cursor c_all_task_csr(c_visit_task_id in number,c_visit_id in number)
514 Is
515 Select visit_task_id,
516 task_type_code,
517 mr_id,
518 start_date_time,
519 end_date_time
520 from ahl_visit_tasks_b
521 where visit_id = c_visit_id
522 AND NVL(status_code, 'Y') <> NVL ('DELETED', 'X')
523 and originating_task_id = c_visit_task_id
524 and mr_id is not null;
525
526 l_all_task_rec c_all_task_csr%rowtype;
527
528 -- Cursor to get customer ID with given service request ID
529
530 Cursor c_customer (c_incident_id in number)
531 Is
532 select customer_id
533 from CS_INCIDENTS_ALL_B
534 where incident_id=c_incident_id;
535
536 l_customer_id CS_INCIDENTS_ALL_B.CUSTOMER_ID%TYPE;
537
538 -- Cursor to get MR billing Item and UOM code
539
540 -- AnRaj: Changed query for fixing the prformance bug# 4919272
541 Cursor c_mr_csr(c_mr_id in number, c_org_id in number)
542 Is
543 SELECT mr.billing_item_id, mr.mr_header_id,mtls.primary_uom_code UOM_CODE
544 FROM AHL_MR_HEADERS_APP_V mr, mtl_system_items_b mtls
545 WHERE mr.mr_header_id = c_mr_id
546 AND mr.billing_item_id = mtls.inventory_item_id
547 AND mtls.organization_id = c_org_id
548 AND billing_item_id IS NOT NULL;
549
550 --Post11510. Added to get summary task start, end time
551 CURSOR get_summary_task_times_csr(x_task_id IN NUMBER)IS
552 SELECT min(start_date_time)
553 FROM ahl_visit_tasks_vl VST
554 START WITH visit_task_id = x_task_id
555 AND NVL(VST.status_code, 'Y') <> NVL ('DELETED', 'X')
556 CONNECT BY originating_task_id = PRIOR visit_task_id;
557
558 l_mr_rec c_mr_csr%rowtype;
559
560 -- Define local variables
561
562 l_z number:=0;
563
564 l_flag varchar2(1);
565 l_price NUMBER:=0;
566 l_module_type VARCHAR2(30);
567
568 l_job_status_code VARCHAR2(30);
569 l_job_status_meaning VARCHAR2(80);
570
571
572 l_cost_price_rec AHL_VWP_VISIT_CST_PR_PVT.cost_price_rec_type:=p_x_cost_price_rec;
573 l_temp_cost_price_rec AHL_VWP_VISIT_CST_PR_PVT.cost_price_rec_type;
574
575 -- Standard in/out parameters
576 l_api_name VARCHAR2(30) := 'ESTIMATE_MR_PRICE ';
577 L_FULL_NAME CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || L_API_NAME;
578 l_api_version NUMBER := 1.0;
579 l_num_rec NUMBER;
580 l_msg_count NUMBER;
581 l_msg_data VARCHAR2(2000);
582 l_return_status VARCHAR2(1);
583 l_init_msg_list VARCHAR2(10):=FND_API.G_FALSE;
584 l_commit VARCHAR2(30) := Fnd_Api.G_FALSE;
585
586 -- Variables for price calculation
587 l_actual_price NUMBER:=0;
588 l_estimate_price NUMBER:=0;
589 l_actual_price_diff NUMBER:=0;
590 l_estimated_price_diff NUMBER:=0;
591
592 -- for task derivation
593 BEGIN
594
595 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)THEN
596 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
597 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name||': Begin API',
598 'At the start of the procedure');
599 END IF;
600
601 -- Standard start of API savepoint
602 SAVEPOINT ESTIMATE_MR_PRICE_PVT;
603
604 -- Initialize message list if p_init_msg_list is set to TRUE
605 IF FND_API.To_Boolean( p_init_msg_list) THEN
606 FND_MSG_PUB.Initialize;
607 END IF;
608
609 -- Initialize API return status to success
610 x_return_status := FND_API.G_RET_STS_SUCCESS;
611
612
613 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
614 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
615 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name||': ',
616 'Request for Estimating Price for Task ID: '||l_cost_price_rec.visit_task_id);
617 END IF;
618
619 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
620 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
621 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name||': ',
622 'Request for Estimating Price for Currency Code: '||l_cost_price_rec.currency);
623 END IF;
624
625
626 -- Check for Required Parameters
627 IF(l_cost_price_rec.visit_task_id IS NULL OR
628 l_cost_price_rec.visit_task_id = FND_API.G_MISS_NUM) THEN
629 FND_MESSAGE.Set_Name(G_PKG_NAME,'AHL_VWP_CST_INPUT_MISS');
630 FND_MSG_PUB.ADD;
631
632
633 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
634 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
635 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name||': ',
636 'Visit Task id is mandatory but found null in input ');
637 END IF;
638 RAISE FND_API.G_EXC_ERROR;
639 END IF;
640
641 -- Retrieve visit task info
642
643 OPEN c_task_csr(l_cost_price_rec.visit_task_id) ;
644 FETCH c_task_csr INTO l_task_rec;
645
646 IF c_task_csr%NOTFOUND THEN
647 FND_MESSAGE.set_name( 'AHL','AHL_VWP_VISIT_TASK_INVALID' );
648 FND_MSG_PUB.add;
649
650 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
651 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
652 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name||': ',
653 ' Visit Task not found in ahl_visit_tasks_b table ');
654 END IF;
655
656 CLOSE c_task_csr;
657 RAISE FND_API.G_EXC_ERROR;
658 END IF;
659 CLOSE c_task_csr;
660
661 -- Retrieve visit info
662
663 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
664 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
665 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name||': ',
666 'After Task Rec ');
667 END IF;
668
669 open c_visit_csr(l_task_rec.visit_id);
670 fetch c_visit_csr into l_visit_rec;
671 close c_visit_csr;
672
673
674 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
675 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
676 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name||': ',
677 'After Visit Rec ');
678
679 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
680 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name||': ',
681 'p_module_type: '||p_module_type);
682 END IF;
683
684 -- Need to release the visit only if this API is called from front-end direcly
685
686 IF p_module_type = 'JSP' THEN
687
688 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
689 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
690 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name||': ',
691 'Before calling ahl vwp task cost.Estimate Task Cost');
692 END IF;
693
694 --Call estimate task cost
695 Estimate_MR_Cost (
696 p_api_version => l_api_version,
697 p_init_msg_list => p_init_msg_list,
698 p_commit => l_commit,
699 p_validation_level => p_validation_level,
700 p_module_type => p_module_type,
701 p_x_cost_price_rec => l_cost_price_rec,
702 x_return_status => l_return_status,
703 x_msg_count => l_msg_count,
704 x_msg_data => l_msg_data);
705
706
707 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
708 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
709 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||': End API',
710 'After calling ahl vwp task cost pvt.Estimate Task cost Return Status : '|| l_return_status
711 );
712 END IF;
713
714 -- Check Error Message stack.
715 if (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
716 l_msg_count := FND_MSG_PUB.count_msg;
717 IF l_msg_count > 0 THEN
718 RAISE FND_API.G_EXC_ERROR;
719 END IF;
720 END IF;
721
722
723 --Assign the out variable
724 p_x_cost_price_rec.cost_session_id := l_cost_price_rec.cost_session_id;
725 p_x_cost_price_rec.mr_session_id := l_cost_price_rec.mr_session_id;
726 p_x_cost_price_rec.Is_Cst_Struc_updated := l_cost_price_rec.Is_Cst_Struc_updated;
727
728
729 END IF; --Module type JSP
730
731 --- Check job status
732
733 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
734 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
735 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name||': ',
736 'before check job status');
737 END IF;
738
739 AHL_VWP_RULES_PVT.check_job_status
740 (
741 p_id =>l_cost_price_rec.visit_task_id,
742 p_is_task_flag =>'Y',
743 x_status_code =>l_job_status_code,
744 x_status_meaning =>l_job_status_meaning
745 );
746
747 IF (l_job_status_code is NULL) THEN
748 l_msg_count := FND_MSG_PUB.count_msg;
749 IF l_msg_count > 0 THEN
750 RAISE FND_API.G_EXC_ERROR;
751 END IF;
752 END IF;
753
754
755
756 --- Populate attributes required by pricing API
757
758 -- Populate customer ID if not passed
759 IF (l_cost_price_rec.Customer_Id is null) OR
760 (l_cost_price_rec.Customer_Id = FND_API.G_MISS_NUM) THEN
761
762 open c_customer (l_visit_rec.service_request_id);
763 fetch c_customer into l_cost_price_rec.Customer_Id;
764 close c_customer;
765 END IF;
766
767
768 -- Populate currency code if not passed
769 IF(l_cost_price_rec.currency IS NULL OR
770 l_cost_price_rec.currency = FND_API.G_MISS_CHAR) THEN
771
772 ahl_vwp_rules_pvt.check_currency_for_costing
773 (
774 p_visit_id => l_task_rec.visit_id,
775 x_currency_code => l_cost_price_rec.currency);
776
777 -- Error Handling
778 IF l_cost_price_rec.currency IS NULL THEN
779 FND_MESSAGE.Set_Name(G_PKG_NAME,'AHL_VWP_CST_NO_CURRENCY');
780 FND_MSG_PUB.ADD;
781
782 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level)THEN
783 fnd_log.string(
784 fnd_log.level_error,
785 'ahl.plsql.'||G_PKG_NAME||'.'||L_API_NAME,
786 'No curency is defined for the organization of the visit'
787 );
788 END IF;
789 RAISE FND_API.G_EXC_ERROR;
790 END IF;
791 END IF; --If currency is null
792
793
794 -- Get the visit price list if not passed
795 IF (l_visit_rec.price_list_id IS NULL OR
796 l_visit_rec.price_list_id = FND_API.G_MISS_NUM ) THEN
797 FND_MESSAGE.set_name( 'AHL','AHL_VWP_PRICE_LIST_INVALID' );
798 FND_MSG_PUB.add;
799 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level)THEN
800 fnd_log.string
801 (
802 fnd_log.level_error,
803 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
804 'Price list not found for Visit'
805 );
806 END IF;
807 RAISE FND_API.G_EXC_ERROR;
808 END IF;
809
810 l_cost_price_rec.PRICE_LIST_ID:=l_visit_rec.price_list_id;
811 l_cost_price_rec.Organization_Id:=l_visit_rec.organization_id;
812
813
814 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
815
816 fnd_log.string
817 (
818 fnd_log.level_statement,
819 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
820 'Customer Id : ' || l_cost_price_rec.customer_id
821 );
822
823 fnd_log.string
824 (
825 fnd_log.level_statement,
826 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
827 'Visit Id : ' || l_task_rec.visit_id
828 );
829
830 fnd_log.string
831 (
832 fnd_log.level_statement,
833 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
834 'Currency : ' || l_cost_price_rec.currency
835 );
836
837 fnd_log.string
838 (
839 fnd_log.level_statement,
840 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
841 'Price List Id : ' || l_cost_price_rec.PRICE_LIST_ID
842 );
843
844 fnd_log.string
845 (
846 fnd_log.level_statement,
847 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
848 'Organization ID : ' || l_cost_price_rec.Organization_Id
849 );
850
851 fnd_log.string
852 (
853 fnd_log.level_statement,
854 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
855 'MR ID : ' || l_task_rec.mr_id
856 );
857
858 fnd_log.string
859 (
860 fnd_log.level_statement,
861 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
862 'Task Start Time : ' || l_task_rec.START_DATE_TIME
863 );
864
865 END IF;
866
867 -- Retrieve MR info
868 open c_mr_csr(l_task_rec.mr_id, l_visit_rec.organization_id);
869
870 fetch c_mr_csr into l_mr_rec;
871
872
873 if c_mr_csr%found
874 then -- billing item is associated to MR
875
876 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
877 fnd_log.string
878 (fnd_log.level_statement,
879 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
880 'billing item found' );
881 END IF;
882
883
884 OPEN get_summary_task_times_csr(l_cost_price_rec.visit_task_id);
885 FETCH get_summary_task_times_csr INTO l_cost_price_rec.task_start_date ;
886 CLOSE get_summary_task_times_csr;
887
888 -- Task Date can not be null
889 IF (l_cost_price_rec.task_start_date IS NULL OR
890 l_cost_price_rec.task_start_date = FND_API.G_MISS_DATE ) THEN
891
892 FND_MESSAGE.set_name( 'AHL','AHL_VWP_VALIDATE_ERROR' );
893 FND_MSG_PUB.add;
894
895 IF G_DEBUG='Y' THEN
896 Ahl_Debug_Pub.debug( l_full_name ||'Task Start Date is null');
897 END IF;
898
899 RAISE FND_API.G_EXC_ERROR;
900 END IF;
901
902 AHL_VWP_PRICE_PVT.get_item_price
903 (p_item_id =>l_mr_rec.billing_item_id,
904 p_price_list_id =>l_cost_price_rec.price_list_id,
905 p_customer_id =>l_cost_price_rec.customer_id,
906 p_currency_code =>l_cost_price_rec.currency,
907 p_effective_date =>l_cost_price_rec.task_start_date,
908 p_uom_code =>l_mr_rec.uom_code,
909 x_item_price =>l_price,
910 x_return_status =>l_return_status
911 );
912
913
914
915 -- Check Error Message stack.
916 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
917 l_msg_count := FND_MSG_PUB.count_msg;
918 IF l_msg_count > 0 THEN
919 RAISE FND_API.G_EXC_ERROR;
920 END IF;
921 END IF;
922
923 l_cost_price_rec.actual_price := nvl(l_price,0);
924 l_cost_price_rec.estimated_price := nvl(l_price,0);
925 l_actual_price := nvl(l_price,0);
926 l_estimate_price := nvl(l_price,0);
927
928
929 --- If no billing item is associated then calculate price for all the immediate childen
930
931 Else
932
933 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
934 fnd_log.string(
935 fnd_log.level_statement,
936 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
937 'Billing Item not found');
938
939
940 END IF;
941
942 --initialize input
943
944 l_temp_cost_price_rec.currency := l_cost_price_rec.currency;
945
946 l_temp_cost_price_rec.customer_id := l_cost_price_rec.customer_id;
947
948 l_temp_cost_price_rec.actual_price := NULL;
949
950 l_temp_cost_price_rec.estimated_price := NULL;
951
952 l_temp_cost_price_rec.PRICE_LIST_ID:=l_cost_price_rec.price_list_id;
953
954 l_temp_cost_price_rec.Organization_Id:=l_cost_price_rec.organization_id;
955
956
957 IF P_MODULE_TYPE='JSP' THEN
958 l_module_type:='MR';
959 ELSE
960 l_module_type:='VST';
961 END IF;
962
963
964
965 -- get all the immediate child tasks
966 open c_all_task_csr(l_cost_price_rec.visit_task_id,l_task_rec.visit_id);
967 loop
968 fetch c_all_task_csr into l_all_task_rec;
969 exit when c_all_task_csr%notfound;
970
971
972 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
973 fnd_log.string
974 (
975 fnd_log.level_statement,
976 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
977 'Task ID: ' || l_all_task_rec.visit_task_id
978 );
979
980
981 fnd_log.string
982 (
983 fnd_log.level_statement,
984 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
985 'Task Type Code: ' || l_all_task_rec.task_type_code
986 );
987
988
989 fnd_log.string
990 (
991 fnd_log.level_statement,
992 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
993 'Task MR ID: ' || l_all_task_rec.MR_ID
994 );
995
996 END IF;
997
998 l_temp_cost_price_rec.visit_task_id := l_all_task_rec.visit_task_id;
999 l_temp_cost_price_rec.Task_Start_Date := l_all_task_rec.START_DATE_TIME;
1000
1001
1002 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1003 fnd_log.string
1004 (
1005 fnd_log.level_statement,
1006 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
1007 'task_start_date : ' ||l_temp_cost_price_rec.task_start_date
1008 );
1009 END IF;
1010
1011
1012 If l_all_task_rec.task_type_code = 'SUMMARY' and l_all_task_rec.MR_ID is not Null
1013 then -- Child MRs
1014
1015
1016
1017 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1018 fnd_log.string
1019 (
1020 fnd_log.level_statement,
1021 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
1022 'Before call to AHL_VWP_MR_CST_PR_PVT.estimate_mr_price'
1023 );
1024 END IF;
1025
1026
1027
1028 AHL_VWP_MR_CST_PR_PVT.estimate_mr_price
1029 (
1030 p_api_version =>l_api_version,
1031 p_init_msg_list =>Fnd_Api.g_false,
1032 p_commit =>Fnd_Api.g_false,
1033 p_validation_level =>Fnd_Api.g_valid_level_full,
1034 P_MODULE_TYPE =>l_module_type,
1035 x_return_status =>x_return_Status,
1036 x_msg_count =>x_msg_count,
1037 x_msg_data =>x_msg_data,
1038 p_x_cost_price_rec =>l_temp_cost_price_rec);
1039
1040
1041 else --- all other tasks
1042
1043 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
1044
1045 fnd_log.string
1046 (
1047 fnd_log.level_statement,
1048 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
1049 'Unplanned/Planned/Unassociated Tasks'
1050 );
1051
1052 END IF;
1053
1054
1055 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1056 fnd_log.string
1057 (
1058 fnd_log.level_statement,
1059 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
1060 'Before call to AHL_VWP_TASK_CST_PR_PVT.estimate_task_price'
1061 );
1062 END IF;
1063
1064
1065 AHL_VWP_TASK_CST_PR_PVT.estimate_task_price
1066 (
1067 p_api_version =>l_api_version,
1068 p_init_msg_list =>Fnd_Api.g_false,
1069 p_commit =>Fnd_Api.g_false,
1070 p_validation_level =>Fnd_Api.g_valid_level_full,
1071 p_module_type =>l_module_type,
1072 x_return_status =>x_return_Status,
1073 x_msg_count =>x_msg_count,
1074 x_msg_data =>x_msg_data,
1075 p_x_cost_price_rec =>l_temp_cost_price_rec);
1076
1077 end if; -- if child mr or other tasks
1078
1079
1080 l_actual_price := nvl(l_actual_price,0) + nvl(l_temp_cost_price_rec.actual_price,0);
1081 l_estimate_price := nvl(l_estimate_price,0) +nvl(l_temp_cost_price_rec.estimated_price,0);
1082
1083
1084 end loop;
1085 close c_all_task_csr;
1086
1087 end if; -- c_mr_csr
1088
1089 close c_mr_csr;
1090
1091 --- Assign the total price
1092
1093 l_cost_price_rec.actual_price := l_actual_price;
1094 l_cost_price_rec.estimated_price := l_estimate_price;
1095
1096 -- Update Visit price if there is any change in price
1097 -- Only required when called from front-end directly
1098
1099 If p_module_type = 'JSP'
1100 then
1101
1102 If l_task_rec.Actual_price > l_actual_price
1103 then
1104 l_actual_price_diff := l_task_rec.Actual_price-l_actual_price;
1105 l_visit_rec.actual_price := l_visit_rec.actual_price - l_actual_price_diff;
1106 Else
1107 l_actual_price_diff := l_actual_price - l_task_rec.Actual_price ;
1108 l_visit_rec.actual_price := l_visit_rec.actual_price + l_actual_price_diff;
1109 End if;
1110
1111 If l_task_rec. Estimated_price > l_estimate_price
1112 then
1113 l_estimated_price_diff := l_task_rec.Estimated_price - l_estimate_price;
1114 l_visit_rec.Estimated_price :=l_visit_rec.Estimated_price - l_estimated_price_diff;
1115 Else
1116 l_estimated_price_diff := l_estimate_price - l_task_rec. Estimated_price ;
1117 l_visit_rec.Estimated_price :=l_visit_rec. Estimated_price + l_estimated_price_diff;
1118 End if;
1119
1120 Update AHL_VISITS_B
1121 set actual_price=l_visit_rec.actual_price,
1122 estimated_price=l_visit_rec.estimated_price
1123 where visit_id=l_visit_rec.visit_id;
1124
1125
1126 Update AHL_VISIT_TASKS_B
1127 set actual_price=l_actual_price,
1128 estimated_price=l_estimate_price
1129 where visit_task_id=l_task_rec.visit_task_id;
1130
1131
1132 Else -- called from Visit
1133
1134
1135 Update AHL_VISIT_TASKS_B
1136 set actual_price=l_actual_price,
1137 estimated_price=l_estimate_price
1138 where visit_task_id=l_task_rec.visit_task_id;
1139 End if;
1140
1141 -- assign output parameters
1142
1143 p_x_cost_price_rec.estimated_price:=l_cost_price_rec.estimated_price;
1144 p_x_cost_price_rec.actual_price:=l_cost_price_rec.actual_price;
1145
1146
1147 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
1148
1149 fnd_log.string
1150 (
1151 fnd_log.level_statement,
1152 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
1153 'MR Estimated Price: ' || p_x_cost_price_rec.estimated_price
1154 );
1155
1156 fnd_log.string
1157 (
1158 fnd_log.level_statement,
1159 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
1160 'MR Actual Price: ' || p_x_cost_price_rec.actual_price
1161 );
1162
1163 END IF;
1164
1165
1166 IF FND_API.TO_BOOLEAN(p_commit) THEN
1167 COMMIT;
1168 END IF;
1169
1170 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1171 THEN
1172 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
1173 G_MODULE_NAME||'.'||l_api_name,'At the end of the procedure');
1174 END IF;
1175
1176
1177 EXCEPTION
1178 WHEN Fnd_Api.g_exc_error THEN
1179 ROLLBACK TO ESTIMATE_MR_PRICE_PVT;
1180 x_return_status := Fnd_Api.g_ret_sts_error;
1181 Fnd_Msg_Pub.count_and_get (
1182 p_encoded => Fnd_Api.g_false,
1183 p_count => x_msg_count,
1184 p_data => x_msg_data
1185 );
1186 WHEN Fnd_Api.g_exc_unexpected_error THEN
1187 ROLLBACK TO ESTIMATE_MR_PRICE_PVT;
1188 x_return_status := Fnd_Api.g_ret_sts_unexp_error ;
1189 Fnd_Msg_Pub.count_and_get (
1190 p_encoded => Fnd_Api.g_false,
1191 p_count => x_msg_count,
1192 p_data => x_msg_data
1193 );
1194 WHEN OTHERS THEN
1195 ROLLBACK TO ESTIMATE_MR_PRICE_PVT;
1196 x_return_status := Fnd_Api.g_ret_sts_unexp_error ;
1197 IF Fnd_Msg_Pub.check_msg_level (Fnd_Msg_Pub.g_msg_lvl_unexp_error)
1198 THEN
1199 Fnd_Msg_Pub.add_exc_msg (G_PKG_NAME, l_api_name);
1200 END IF;
1201 Fnd_Msg_Pub.count_and_get (
1202 p_encoded => Fnd_Api.g_false,
1203 p_count => x_msg_count,
1204 p_data => x_msg_data
1205 );
1206 END ESTIMATE_MR_PRICE;
1207
1208
1209 PROCEDURE get_mr_items_no_price(
1210 p_api_version IN NUMBER,
1211 p_init_msg_list IN VARCHAR2 := Fnd_Api.g_false,
1212 p_commit IN VARCHAR2 :=Fnd_Api.g_false,
1213 p_validation_level IN NUMBER := Fnd_Api.g_valid_level_full,
1214 p_module_type IN VARCHAR2,
1215 x_return_status OUT NOCOPY VARCHAR2,
1216 x_msg_count OUT NOCOPY NUMBER,
1217 x_msg_data OUT NOCOPY VARCHAR2,
1218 p_cost_price_rec IN AHL_VWP_VISIT_CST_PR_PVT.cost_price_rec_type,
1219 x_cost_price_tbl OUT NOCOPY AHL_VWP_VISIT_CST_PR_PVT.cost_price_tbl_type
1220
1221 )
1222 AS
1223
1224 Cursor c_task_csr(c_task_id number)
1225 Is
1226 Select visit_task_id,
1227 visit_id,
1228 price_list_id,
1229 mr_id, start_date_time
1230 from Ahl_visit_tasks_vl
1231 where visit_task_id=c_task_id;
1232 l_task_rec c_task_csr%rowtype;
1233
1234 Cursor c_visit_csr(c_visit_id number)
1235 Is
1236 Select visit_id,
1237 price_list_id,
1238 service_Request_id,
1239 organization_id,
1240 outside_party_flag
1241 From AHL_VISITS_B
1242 Where VISIT_ID=C_VISIT_ID;
1243
1244 l_visit_rec c_visit_csr%rowtype;
1245
1246 -- AnRaj: Changed query for fixing the prformance bug# 4919272
1247 Cursor c_mr_header(c_mr_header_id number)
1248 Is
1249 /*SELECT 1
1250 From AHL_MR_HEADERS_V
1251 WHERE MR_HEADER_ID=C_MR_HEADER_ID;
1252 */
1253 SELECT 1
1254 From AHL_MR_HEADERS_APP_V
1255 WHERE MR_HEADER_ID=C_MR_HEADER_ID ;
1256
1257 -- Cursor to get MR billing Item and UOM code
1258 -- AnRaj: Changed query for fixing the prformance bug# 4919272
1259 Cursor c_mr_csr(c_mr_id in number, c_org_id in number)
1260 Is
1261 SELECT mr.billing_item_id,
1262 mr.mr_header_id,
1263 mtls.primary_uom_code UOM_CODE
1264 FROM AHL_MR_HEADERS_APP_V mr, mtl_system_items_b mtls
1265 WHERE mr.MR_HEADER_ID = C_MR_ID
1266 AND mr.BILLING_ITEM_ID = mtls.INVENTORY_ITEM_ID
1267 AND mtls.organization_id = c_org_id
1268 AND billing_item_id IS NOT NULL ;
1269
1270 l_mr_rec c_mr_csr%rowtype;
1271
1272 -- Cursor to get all the immediate child of the given task
1273 Cursor c_all_task_csr(c_visit_task_id in number,c_visit_id in number)
1274 Is
1275 Select visit_task_id,task_type_code,
1276 mr_id, start_date_time
1277 from ahl_visit_tasks_b
1278 where visit_id = c_visit_id
1279 AND NVL(status_code, 'Y') <> NVL ('DELETED', 'X')
1280 and originating_task_id = c_visit_task_id
1281 and mr_id is not null;
1282
1283 l_all_task_rec c_all_task_csr%rowtype;
1284
1285 -- Cusor to get customer id
1286
1287 Cursor c_customer_csr (c_incident_id in number)
1288 Is
1289 select customer_id
1290 from CS_INCIDENTS_ALL_B
1291 where incident_id=c_incident_id;
1292
1293
1294 -- AnRaj: Changed query for fixing the prformance bug# 4919272
1295 -- Cursor to get task infomation
1296 /*Select mr_name,
1297 mr_description,
1298 task_number,
1299 task_name
1300 from AHL_SEARCH_VISIT_TASK_V
1301 Where TASK_ID=C_VISIT_TASK_ID;
1302 */
1303 -- AnRaj: Changed the query to remove the use of AHL_MR_HEADERS_VL
1304 -- Bug Number : 5208387
1305 Cursor c_task_info(c_visit_task_id in number)
1306 Is
1307 select mrb.title mr_name,
1308 mrtl.description mr_description,
1309 visit_task_number task_number,
1310 visit_task_name task_name
1311 from ahl_visit_tasks_vl tsk,
1312 ahl_mr_headers_tl mrtl,
1313 ahl_mr_headers_b mrb,
1314 ahl_mr_routes mrr,
1315 ahl_visits_vl avts
1316 where tsk.mr_route_id = mrr.mr_route_id(+)
1317 and mrr.mr_header_id = mrb.mr_header_id(+)
1318 and mrb.mr_header_id = mrtl.mr_header_id (+)
1319 and mrtl.language(+) = USERENV('LANG')
1320 and nvl(tsk.status_code,'X') <> 'DELETED'
1321 and avts.visit_id = tsk.visit_id
1322 and avts.template_flag = 'N'
1323 and tsk.task_type_code <> 'SUMMARY'
1324 AND visit_task_id=c_visit_task_id
1325 UNION
1326 select title mr_name,
1327 mrh.description mr_description,
1328 visit_task_number task_number,
1329 visit_task_name task_name
1330 from ahl_visit_tasks_vl tsk,
1331 ahl_mr_headers_vl mrh,
1332 AHL_VISITS_VL AVTS
1333 where MRH.MR_HEADER_ID = TSK.MR_ID
1334 AND NVL(TSK.STATUS_CODE,'X') <> 'DELETED'
1335 AND AVTS.VISIT_ID = TSK.VISIT_ID
1336 AND AVTS.TEMPLATE_FLAG = 'N'
1337 AND VISIT_TASK_ID=c_visit_task_id;
1338
1339 l_task_info_rec c_task_info%rowtype;
1340
1341 -- cursor to get item information
1342 Cursor c_item_info(c_item_id in number,c_org_id in number)
1343 Is
1344 /*
1345 Select CONCATENATED_SEGMENTS,DESCRIPTION,INVENTORY_ORG_ID,organization_name
1346 FROM AHL_MTL_ITEMS_OU_V
1347 WHERE INVENTORY_ITEM_ID=C_ITEM_ID
1348 AND INVENTORY_ORG_ID=C_ORG_ID;
1349 */
1350 -- AnRaj: Changed the cursor query for issues mentioned in Bug Number:5258318
1351 select mtl.concatenated_segments,
1352 mtl.description,
1353 mtl.organization_id inventory_org_id,
1354 hou.name organization_name
1355 from mtl_system_items_kfv mtl,
1356 hr_organization_units hou,
1357 inv_organization_info_v org
1358 where mtl.organization_id = org.organization_id
1359 and hou.organization_id = org.organization_id
1360 and nvl (org.operating_unit, mo_global.get_current_org_id ())=mo_global.get_current_org_id()
1361 and mtl.inventory_item_id=c_item_id
1362 and mtl.organization_id=c_org_id ;
1363
1364
1365 l_item_info_rec c_item_info%rowtype;
1366
1367 --Post11510. Added to get summary task start, end time
1368 CURSOR get_summary_task_times_csr(x_task_id IN NUMBER)IS
1369 SELECT min(start_date_time)
1370 FROM ahl_visit_tasks_vl VST
1371 START WITH visit_task_id = x_task_id
1372 AND NVL(VST.status_code, 'Y') <> NVL ('DELETED', 'X')
1373 CONNECT BY originating_task_id = PRIOR visit_task_id;
1374
1375 -- Local variables
1376
1377 l_cost_price_rec AHL_VWP_VISIT_CST_PR_PVT.cost_price_rec_type:=p_cost_price_rec;
1378 l_temp_cost_price_rec AHL_VWP_VISIT_CST_PR_PVT.cost_price_rec_type;
1379
1380 L_API_VERSION NUMBER := 1.0;
1381 L_API_NAME VARCHAR2(30) :='GET_MR_ITEMS_NO_PRICE';
1382 L_FULL_NAME CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || L_API_NAME;
1383 l_msg_data VARCHAR2(2000);
1384 l_return_status VARCHAR2(1);
1385 l_msg_count NUMBER;
1386
1387 l_release_visit_required VARCHAR2(1) :='N';
1388
1389
1390 l_cost_price_tbl AHL_VWP_VISIT_CST_PR_PVT.cost_price_tbl_type;
1391 l_cost_price_tbl1 AHL_VWP_VISIT_CST_PR_PVT.cost_price_tbl_type;
1392
1393 l_job_status_code ahl_workorders_v.job_status_code%type;
1394 l_job_status_mean ahl_workorders_v.job_status_meaning%type;
1395
1396 l_price number:=0;
1397 l_index NUMBER:=0;
1398
1399 l_dummy NUMBER;
1400 l_z number:=0;
1401 l_flag varchar2(1);
1402 l_module_type VARCHAR2(30);
1403
1404
1405 BEGIN
1406
1407 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)THEN
1408 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
1409 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name||': Begin API',
1410 'At the start of the procedure');
1411 END IF;
1412
1413 SAVEPOINT Get_MR_Items_No_Price_pvt;
1414
1415 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version,
1416 p_api_version,
1417 l_api_name,G_PKG_NAME)
1418 THEN
1419 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1420 END IF;
1421
1422 IF FND_API.to_boolean(p_init_msg_list)
1423 THEN
1424 FND_MSG_PUB.initialize;
1425 END IF;
1426
1427 -- initialize return status
1428
1429 x_return_status:=FND_API.G_RET_STS_SUCCESS;
1430
1431 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
1432 fnd_log.string
1433 (
1434 fnd_log.level_statement,
1435 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
1436 'Request to get items without price for Task ID : ' || l_cost_price_rec.visit_task_id
1437 );
1438
1439 END IF;
1440
1441
1442 -- Check for Required Parameters
1443 IF(l_cost_price_rec.visit_task_id IS NULL OR
1444 l_cost_price_rec.visit_task_id = FND_API.G_MISS_NUM) THEN
1445 FND_MESSAGE.Set_Name('AHL','AHL_VWP_CST_INPUT_MISS');
1446 FND_MSG_PUB.ADD;
1447 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level)THEN
1448 fnd_log.string
1449 (
1450 fnd_log.level_error,
1451 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
1452 'Visit Task id is mandatory but found null in input '
1453 );
1454 END IF;
1455 RAISE FND_API.G_EXC_ERROR;
1456 END IF;
1457
1458 -- retrieve task info
1459
1460 Open c_task_csr(l_cost_price_rec.visit_task_id);
1461
1462 Fetch c_task_csr into l_task_rec;
1463
1464 IF c_task_csr%NOTFOUND THEN
1465 FND_MESSAGE.set_name( 'AHL','AHL_VWP_VISIT_TASK_INVALID' );
1466 FND_MSG_PUB.add;
1467 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level)THEN
1468 fnd_log.string
1469 (
1470 fnd_log.level_error,
1471 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
1472 'Visit Task not found in ahl_visit_tasks_b table'
1473 );
1474 END IF;
1475 CLOSE c_task_csr;
1476 RAISE FND_API.G_EXC_ERROR;
1477 END IF;
1478 CLOSE c_task_csr;
1479
1480 l_cost_price_rec.visit_id:=l_task_rec.visit_id;
1481
1482 -- Get visit info
1483
1484 Open c_visit_csr(l_task_rec.visit_id);
1485 Fetch c_visit_csr into l_visit_rec;
1486 If c_visit_csr%notfound
1487 Then
1488 Fnd_Message.SET_NAME('AHL','AHL_VWP_VISIT_INVALID');
1489 Fnd_Msg_Pub.ADD;
1490 Close c_visit_csr;
1491 RAISE FND_API.G_EXC_ERROR;
1492 End if;
1493 Close c_visit_csr;
1494
1495 -- Need to release visit only if the module type is JSP
1496
1497 If p_module_type = 'JSP' then
1498
1499 AHL_VWP_VISIT_CST_PR_PVT.check_for_release_visit
1500 (
1501 p_visit_id =>l_task_rec.visit_id,
1502 x_release_visit_required =>l_release_visit_required
1503 );
1504
1505 -- Release visit if required
1506 IF l_release_visit_required ='Y' THEN
1507
1508 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
1509 fnd_log.string
1510 (
1511 fnd_log.level_statement,
1512 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
1513 'Before calling ahl vwp proj prod pvt.release visit'
1514 );
1515
1516 END IF;
1517
1518 AHL_VWP_PROJ_PROD_PVT.release_visit
1519 (
1520 p_api_version =>l_api_version,
1521 p_init_msg_list =>Fnd_Api.g_false,
1522 p_commit =>Fnd_Api.g_false,
1523 p_validation_level =>Fnd_Api.g_valid_level_full,
1524 p_module_type => 'CST',
1525 x_return_status =>l_return_Status,
1526 x_msg_count =>x_msg_count,
1527 x_msg_data =>x_msg_data,
1528 p_visit_id =>l_task_rec.visit_id
1529 );
1530
1531 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
1532 fnd_log.string
1533 (
1534 fnd_log.level_statement,
1535 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
1536 'After calling ahl vwp proj prod pvt.Release Visit task wo Return Status : '|| l_return_status
1537 );
1538 END IF;
1539
1540 -- Check Error Message stack.
1541 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1542 l_msg_count := FND_MSG_PUB.count_msg;
1543 IF l_msg_count > 0 THEN
1544 RAISE FND_API.G_EXC_ERROR;
1545 END IF;
1546 END IF;
1547
1548 END IF; -- release required flag
1549
1550 END IF; -- FOR P_MODULE_TYPE
1551
1552 -- check if MR id is valid
1553 IF l_task_rec.mr_id is not null
1554 Then
1555 Open c_mr_header(l_task_rec.mr_id);
1556 Fetch c_mr_header into l_dummy;
1557 If c_mr_header%notfound
1558 Then
1559 Fnd_Message.SET_NAME('AHL','AHL_VWP_MR_HEADER_ID_NULL');
1560 Fnd_Msg_Pub.ADD;
1561 Close c_mr_header;
1562 RAISE FND_API.G_EXC_ERROR;
1563 End if;
1564 Close c_mr_header;
1565 End if;
1566
1567 -- Check outside party flag
1568
1569 If l_visit_rec.outside_party_flag ='N'
1570 then
1571 FND_MESSAGE.Set_Name('AHL','AHL_VWP_CST_OUTSDPRTY_FLAG');
1572 FND_MSG_PUB.ADD;
1573 RAISE FND_API.G_EXC_ERROR;
1574 --Display an error message `Visit number visit_number is not an outside party.'
1575 END if;
1576
1577
1578 -- Populate pricing attributes
1579
1580 -- Populate price list ID
1581
1582 if l_visit_rec.price_list_id is not Null and l_visit_rec.price_list_id <> FND_API.G_MISS_NUM
1583 then
1584 l_cost_price_rec.price_list_id :=l_visit_rec.price_list_id;
1585 Else
1586 FND_MESSAGE.Set_Name('AHL','AHL_VWP_CST_PRICELISTIDNULL'); --AHL_VWP_PRICE_LIST_ID_NULL
1587 FND_MSG_PUB.ADD;
1588 RAISE FND_API.G_EXC_ERROR;
1589
1590 END IF;
1591
1592
1593 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
1594 fnd_log.string
1595 (
1596 fnd_log.level_statement,
1597 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
1598 'Price List ID :' ||l_cost_price_rec.price_list_id
1599 );
1600 END IF;
1601
1602
1603 l_cost_price_rec.organization_id:=l_visit_rec.organization_id;
1604
1605
1606 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
1607 fnd_log.string
1608 (
1609 fnd_log.level_statement,
1610 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
1611 'Organization ID :' ||l_cost_price_rec.organization_id
1612 );
1613 END IF;
1614
1615 -- Populate customer ID if not passed
1616 IF (l_cost_price_rec.Customer_Id is null) OR
1617 (l_cost_price_rec.Customer_Id = FND_API.G_MISS_NUM) THEN
1618
1619 Open c_customer_csr(l_visit_rec.service_request_id);
1620 fetch c_customer_csr into l_cost_price_rec.customer_id;
1621 close c_customer_csr;
1622 END IF;
1623
1624 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
1625 fnd_log.string
1626 (
1627 fnd_log.level_statement,
1628 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
1629 'Customer ID :' ||l_cost_price_rec.customer_id
1630 );
1631 END IF;
1632
1633 -- Populate currency code if not passed
1634 IF(l_cost_price_rec.currency IS NULL OR
1635 l_cost_price_rec.currency = FND_API.G_MISS_CHAR) THEN
1636
1637 AHL_VWP_RULES_PVT.check_currency_for_costing
1638 (p_visit_id =>l_task_rec.visit_id,
1639 x_currency_code =>l_cost_price_rec.currency
1640 );
1641
1642 -- Error handling
1643 IF l_cost_price_rec.currency IS NULL THEN
1644 FND_MESSAGE.Set_Name(G_PKG_NAME,'AHL_VWP_CST_NO_CURRENCY');
1645 FND_MSG_PUB.ADD;
1646 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level)THEN
1647 fnd_log.string
1648 (
1649 fnd_log.level_error,
1650 'ahl.plsql.'||G_PKG_NAME||'.'||L_API_NAME,
1651 'No curency is defined for the organization of the visit'
1652 );
1653 END IF;
1654 RAISE FND_API.G_EXC_ERROR;
1655 END IF;
1656 END IF; --If currency is null
1657
1658 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
1659 fnd_log.string
1660 (
1661 fnd_log.level_statement,
1662 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
1663 'Currency Code :' ||l_cost_price_rec.currency
1664 );
1665 END IF;
1666
1667 --- Check for job status
1668
1669 ahl_vwp_rules_pvt.Check_Job_Status
1670 (
1671 p_id => l_cost_price_rec.visit_task_id,
1672 p_is_task_flag => 'Y',
1673 x_status_code => l_job_status_code,
1674 x_status_meaning => l_job_status_mean);
1675
1676 IF (l_job_status_code is NULL) THEN
1677 l_msg_count := FND_MSG_PUB.count_msg;
1678 IF l_msg_count > 0 THEN
1679 RAISE FND_API.G_EXC_ERROR;
1680 END IF;
1681 END IF;
1682
1683 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
1684 fnd_log.string
1685 (
1686 fnd_log.level_statement,
1687 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
1688 'Job Status :' ||l_job_status_code
1689 );
1690 fnd_log.string
1691 (
1692 fnd_log.level_statement,
1693 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
1694 'task start date passed in :' ||l_cost_price_rec.task_start_date
1695 );
1696 END IF;
1697
1698 --- Check to see if billing item is associated
1699 open c_mr_csr(l_task_rec.mr_id, l_visit_rec.organization_id);
1700 fetch c_mr_csr into l_mr_rec;
1701
1702 IF c_mr_csr%found
1703 then ---- billing item is associated to MR
1704
1705 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
1706 fnd_log.string(fnd_log.level_statement,
1707 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
1708 'billing item found');
1709 END IF;
1710
1711 OPEN get_summary_task_times_csr(l_cost_price_rec.visit_task_id);
1712 FETCH get_summary_task_times_csr INTO l_cost_price_rec.task_start_date ;
1713 CLOSE get_summary_task_times_csr;
1714
1715 -- Task Date can not be null
1716 IF (l_cost_price_rec.task_start_date IS NULL OR
1717 l_cost_price_rec.task_start_date = FND_API.G_MISS_DATE ) THEN
1718
1719 FND_MESSAGE.set_name( 'AHL','AHL_VWP_VALIDATE_ERROR' );
1720 FND_MSG_PUB.add;
1721
1722 IF G_DEBUG='Y' THEN
1723 Ahl_Debug_Pub.debug( l_full_name ||'Task Start Date is null');
1724 END IF;
1725
1726 RAISE FND_API.G_EXC_ERROR;
1727 END IF;
1728
1729 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
1730
1731 fnd_log.string
1732 (
1733 fnd_log.level_statement,
1734 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
1735 'current task start date :' ||l_cost_price_rec.task_start_date
1736 );
1737 END IF;
1738
1739 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1740 fnd_log.string
1741 (
1742 fnd_log.level_statement,
1743 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
1744 'Before call to AHL_VWP_PRICE_PVT.get_item_price'
1745 );
1746 END IF;
1747
1748 AHL_VWP_PRICE_PVT.get_item_price
1749 (
1750 p_item_id =>l_mr_rec.billing_item_id,
1751 p_price_list_id =>l_cost_price_rec.price_list_id,
1752 p_customer_id =>l_cost_price_rec.customer_id,
1753 p_currency_code =>l_cost_price_rec.currency,
1754 p_effective_date =>l_cost_price_rec.task_start_date,
1755 p_uom_code =>l_mr_rec.uom_code,
1756 x_item_price =>l_price,
1757 x_return_status =>l_return_status
1758 );
1759
1760 -- Check Error Message stack.
1761 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1762 l_msg_count := FND_MSG_PUB.count_msg;
1763 IF l_msg_count > 0 THEN
1764 RAISE FND_API.G_EXC_ERROR;
1765 END IF;
1766 END IF;
1767
1768 -- no price set up for the mr billing item
1769
1770 If l_price is Null
1771 then
1772 l_cost_price_tbl(0).billing_item_id := l_mr_rec.billing_item_id;
1773 l_cost_price_tbl(0).visit_task_id := l_task_rec.visit_task_id;
1774 l_cost_price_tbl(0).mr_id := l_mr_rec.mr_header_id;
1775
1776
1777 --- Populate infomation to be displayed on UI
1778
1779
1780 open c_task_info(l_cost_price_tbl(0).visit_task_id);
1781 fetch c_task_info into l_task_info_rec;
1782 If c_task_info%found
1783 then
1784 l_cost_price_tbl(0).mr_Title:=l_task_info_rec.MR_name;
1785 l_cost_price_tbl(0).MR_Description:= l_task_info_rec.MR_Description;
1786 l_cost_price_tbl(0).Visit_task_number := l_task_info_rec.task_number;
1787 l_cost_price_tbl(0).task_name := l_task_info_rec.task_name;
1788 End if;
1789 close c_task_info;
1790
1791 open c_item_info(l_cost_price_tbl(0).billing_item_id,l_visit_rec.organization_id);
1792 fetch c_item_info into l_item_info_rec;
1793 If c_item_info%found
1794 then
1795 l_cost_price_tbl(0).Item_name := l_item_info_rec.concatenated_segments;
1796 l_cost_price_tbl(0).Item_Description := l_item_info_rec.DESCRIPTION;
1797 l_cost_price_tbl(0).Organization_name:=l_item_info_rec.organization_name;
1798 End if;
1799 close c_item_info;
1800
1801 End if; -- item price is null
1802
1803 --- If no billing item is associated then check the item price for all the immediate childen
1804
1805 Else
1806
1807 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
1808 fnd_log.string
1809 (
1810 fnd_log.level_statement,
1811 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
1812 'Billing Item not found'
1813 );
1814 END IF;
1815
1816 --initialize input
1817
1818 l_temp_cost_price_rec.currency := l_cost_price_rec.currency;
1819
1820 l_temp_cost_price_rec.customer_id := l_cost_price_rec.customer_id;
1821
1822 l_temp_cost_price_rec.PRICE_LIST_ID:=l_cost_price_rec.price_list_id;
1823
1824 l_temp_cost_price_rec.Organization_Id:=l_cost_price_rec.organization_id;
1825
1826 IF P_MODULE_TYPE='JSP' THEN
1827 l_module_type:='MR';
1828 END IF;
1829
1830 -- get all the immediate child tasks
1831 open c_all_task_csr(l_cost_price_rec.visit_task_id,l_task_rec.visit_id);
1832 loop
1833 fetch c_all_task_csr into l_all_task_rec;
1834 exit when c_all_task_csr%notfound;
1835
1836
1837 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
1838 fnd_log.string
1839 (
1840 fnd_log.level_statement,
1841 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
1842 'Current Task ID: ' || l_all_task_rec.visit_task_id
1843 );
1844
1845 fnd_log.string
1846 (
1847 fnd_log.level_statement,
1848 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
1849 'Task Type Code: ' || l_all_task_rec.task_type_code
1850 );
1851
1852 fnd_log.string
1853 (
1854 fnd_log.level_statement,
1855 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
1856 'Task MR ID: ' || l_all_task_rec.MR_ID
1857 );
1858
1859 END IF;
1860
1861 --initialize input
1862 l_temp_cost_price_rec.visit_task_id := l_all_task_rec.visit_task_id;
1863 l_temp_cost_price_rec.Task_Start_Date := l_all_task_rec.START_DATE_TIME;
1864
1865 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1866 fnd_log.string
1867 (
1868 fnd_log.level_statement,
1869 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
1870 'task_start_date : ' ||l_temp_cost_price_rec.task_start_date
1871 );
1872 END IF;
1873
1874 If l_all_task_rec.task_type_code = 'SUMMARY' and l_all_task_rec.MR_ID is not Null
1875 then -- Child MRs
1876
1877
1878 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1879 fnd_log.string
1880 (
1881 fnd_log.level_statement,
1882 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
1883 'Child MR'
1884 );
1885
1886 fnd_log.string
1887 (
1888 fnd_log.level_statement,
1889 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
1890 'Before call to AHL_VWP_MR_CST_PR_PVT.get_mr_items_no_price'
1891 );
1892 END IF;
1893
1894 AHL_VWP_MR_CST_PR_PVT.get_mr_items_no_price
1895 (
1896 p_api_version =>l_api_version,
1897 p_init_msg_list =>Fnd_Api.g_false,
1898 p_commit =>Fnd_Api.g_false,
1899 p_validation_level =>Fnd_Api.g_valid_level_full,
1900 p_module_type =>l_module_type,
1901 x_return_status =>l_return_status,
1902 x_msg_count =>x_msg_count,
1903 x_msg_data =>x_msg_data,
1904 p_cost_price_rec =>l_temp_cost_price_rec,
1905 x_cost_price_tbl =>l_cost_price_tbl1
1906 );
1907 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1908 RAISE Fnd_Api.G_EXC_ERROR;
1909 END IF;
1910 else --- all other tasks
1911
1912
1913 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1914 fnd_log.string
1915 (
1916 fnd_log.level_statement,
1917 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
1918 'Unplanned/Planned/Unassociated Tasks'
1919 );
1920 fnd_log.string
1921 (
1922 fnd_log.level_statement,
1923 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
1924 'Before call to AHL_VWP_TASK_CST_PR_PVT.GET_TASK_ITEMS_NO_PRICE'
1925 );
1926
1927 END IF;
1928
1929
1930 AHL_VWP_TASK_CST_PR_PVT.GET_TASK_ITEMS_NO_PRICE
1931 (
1932 p_api_version =>p_api_version,
1933 p_init_msg_list =>Fnd_Api.G_FALSE,
1934 p_commit =>Fnd_Api.G_FALSE,
1935 p_validation_level =>Fnd_Api.G_VALID_LEVEL_FULL,
1936 p_module_type =>l_module_type,
1937 x_return_status =>l_return_status,
1938 x_msg_count =>x_msg_count,
1939 x_msg_data =>x_msg_data,
1940 p_cost_price_rec =>l_temp_cost_price_rec,
1941 x_cost_price_tbl =>l_cost_price_tbl1
1942 );
1943 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1944 RAISE Fnd_Api.G_EXC_ERROR;
1945 END IF;
1946
1947 END IF; ----- if child mr or other tasks
1948
1949 -- Merge the return value of the child to the current total
1950 if l_cost_price_tbl.count =0 then
1951
1952 l_cost_price_tbl :=l_cost_price_tbl1;
1953
1954 elsif l_cost_price_tbl1.count >0
1955 then
1956
1957 l_index:=l_cost_price_tbl.count;
1958
1959 for i in l_cost_price_tbl1.first .. l_cost_price_tbl1.last
1960 loop
1961 l_cost_price_tbl(l_index):=l_cost_price_tbl1(i);
1962 l_index:=l_index+1;
1963 end loop;
1964
1965 end if;
1966
1967 end loop;
1968 close c_all_task_csr;
1969
1970 end if; -- billing item associated to mr
1971
1972 close c_mr_csr;
1973
1974 -- Check Error Message stack.
1975 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1976 l_msg_count := FND_MSG_PUB.count_msg;
1977 IF l_msg_count > 0 THEN
1978 RAISE FND_API.G_EXC_ERROR;
1979 END IF;
1980 END IF;
1981
1982 x_cost_price_tbl:=l_cost_price_tbl;
1983
1984
1985 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
1986
1987 fnd_log.string
1988 (
1989 fnd_log.level_statement,
1990 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
1991 'Total number of items w/o price for the MR: ' || x_cost_price_tbl.count
1992 );
1993
1994 END IF;
1995
1996 IF FND_API.TO_BOOLEAN(p_commit) THEN
1997 COMMIT;
1998 END IF;
1999
2000 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
2001 THEN
2002 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
2003 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
2004 'At the end of the procedure');
2005 END IF;
2006
2007
2008 EXCEPTION
2009 WHEN Fnd_Api.g_exc_error THEN
2010 ROLLBACK TO Get_MR_Items_No_Price_pvt;
2011 x_return_status := Fnd_Api.g_ret_sts_error;
2012 Fnd_Msg_Pub.count_and_get (
2013 p_encoded => Fnd_Api.g_false,
2014 p_count => x_msg_count,
2015 p_data => x_msg_data
2016 );
2017 WHEN Fnd_Api.g_exc_unexpected_error THEN
2018 ROLLBACK TO Get_MR_Items_No_Price_pvt;
2019 x_return_status := Fnd_Api.g_ret_sts_unexp_error ;
2020 Fnd_Msg_Pub.count_and_get (
2021 p_encoded => Fnd_Api.g_false,
2022 p_count => x_msg_count,
2023 p_data => x_msg_data
2024 );
2025 WHEN OTHERS THEN
2026 ROLLBACK TO Get_MR_Items_No_Price_pvt;
2027 x_return_status := Fnd_Api.g_ret_sts_unexp_error ;
2028 IF Fnd_Msg_Pub.check_msg_level (Fnd_Msg_Pub.g_msg_lvl_unexp_error)
2029 THEN
2030 Fnd_Msg_Pub.add_exc_msg (G_PKG_NAME, l_api_name);
2031 END IF;
2032 Fnd_Msg_Pub.count_and_get (
2033 p_encoded => Fnd_Api.g_false,
2034 p_count => x_msg_count,
2035 p_data => x_msg_data
2036 );
2037
2038 END Get_MR_Items_No_Price;
2039
2040
2041
2042 PROCEDURE Get_MR_Cost_Details(
2043 p_api_version IN NUMBER,
2044 p_init_msg_list IN VARCHAR2 :=Fnd_Api.g_false,
2045 p_commit IN VARCHAR2 :=Fnd_Api.g_false,
2046 p_validation_level IN NUMBER :=Fnd_Api.g_valid_level_full,
2047 p_module_type IN VARCHAR2,
2048 p_x_cost_price_rec IN OUT NOCOPY AHL_VWP_VISIT_CST_PR_PVT.cost_price_rec_type,
2049 x_return_status OUT NOCOPY VARCHAR2,
2050 x_msg_count OUT NOCOPY NUMBER,
2051 x_msg_data OUT NOCOPY VARCHAR2
2052 )
2053 AS
2054 l_api_name CONSTANT VARCHAR2(30) := 'Get_MR_Cost_Details';
2055 l_api_version CONSTANT NUMBER := 1.0;
2056 l_num_rec NUMBER;
2057 l_msg_count NUMBER;
2058 l_msg_data VARCHAR2(2000);
2059 l_return_status VARCHAR2(1);
2060 l_init_msg_list VARCHAR2(10):=FND_API.G_FALSE;
2061
2062 l_visit_status VARCHAR2(30);
2063
2064
2065 -- Define Local Cursors
2066 CURSOR visit_info_csr(p_visit_id IN NUMBER) IS
2067 SELECT status_code
2068 FROM ahl_visits_b
2069 WHERE visit_id = p_visit_id;
2070
2071
2072 Cursor c_task_rec(c_visit_task_id NUMBER)
2073 Is
2074 SELECT actual_price,ESTIMATEd_price, MR_Id
2075 FROM AHL_VISIT_TASKS_VL
2076 WHERE VISIT_TASK_ID=C_VISIT_TASK_ID;
2077
2078 l_visit_task_rec c_task_rec%rowtype;
2079
2080 l_cost_price_rec AHL_VWP_VISIT_CST_PR_PVT.cost_price_rec_type:=p_x_cost_price_rec;
2081
2082
2083 l_job_status_code varchar2(30);
2084 l_job_status_meaning varchar2(80);
2085
2086 -- AnRaj: Changed query for fixing the prformance bug# 4919272
2087 Cursor c_mr_csr(c_mr_id in number)
2088 Is
2089 /*
2090 Select title, billing_item_id,description,mr_header_id
2091 From ahl_mr_headers_v
2092 where mr_header_id=c_mr_id;
2093 */
2094 Select title, billing_item_id,description,mr_header_id
2095 From AHL_MR_HEADERS_APP_V
2096 where mr_header_id=c_mr_id;
2097
2098 l_mr_rec c_mr_csr%rowtype;
2099
2100
2101 BEGIN
2102
2103 SAVEPOINT Get_MR_Cost_Details_PVT;
2104
2105 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version,
2106 p_api_version,
2107 l_api_name,G_PKG_NAME)
2108 THEN
2109 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2110 END IF;
2111
2112 IF FND_API.to_boolean(p_init_msg_list)
2113 THEN
2114 FND_MSG_PUB.initialize;
2115 END IF;
2116
2117 x_return_status:=FND_API.G_RET_STS_SUCCESS;
2118
2119 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
2120 THEN
2121 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
2122 G_MODULE_NAME||'.'||l_api_name,'At the start of the procedure');
2123 END IF;
2124
2125 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
2126 THEN
2127 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
2128 G_MODULE_NAME||'.'||l_api_name,'Visit Task ID:'||p_x_cost_price_rec.visit_task_id);
2129 END IF;
2130
2131 POPULATE_COST_PRICE_REC(l_cost_price_rec);
2132
2133 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
2134 THEN
2135 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
2136 G_MODULE_NAME||'.'||l_api_name,'Visit ID:'||l_cost_price_rec.visit_id);
2137 END IF;
2138
2139 OPEN visit_info_csr(l_cost_price_rec.visit_id);
2140 FETCH visit_info_csr INTO l_visit_status;
2141
2142
2143 IF (visit_info_csr%NOTFOUND)THEN
2144 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_VWP_CST_INV_VISIT');
2145 FND_MSG_PUB.ADD;
2146 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)THEN
2147 fnd_log.string
2148 (
2149 fnd_log.level_exception,
2150 G_MODULE_NAME||'.'||l_api_name,
2151 'Visit id not found in ahl_visits_b table'
2152 );
2153 END IF;
2154 CLOSE visit_info_csr;
2155 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2156 END IF;
2157 CLOSE visit_info_csr;
2158
2159 --
2160 --DBMS_OUTPUT.PUT_LINE('STAGE 2');
2161
2162 If l_cost_price_rec.visit_task_id is not null then
2163 --DBMS_OUTPUT.PUT_LINE('STAGE 2');
2164
2165 open c_task_rec(l_cost_price_rec.visit_task_id);
2166 fetch c_task_rec into l_visit_task_rec;
2167 if c_task_rec%found
2168 then
2169 If l_visit_task_rec.actual_price is Null
2170 then
2171 l_cost_price_rec.actual_price:= NULL;
2172 Else
2173 l_cost_price_rec.actual_price:=l_visit_task_rec.actual_price;
2174 end if;
2175
2176 If l_visit_task_rec.ESTIMATEd_price is null
2177 then
2178 l_cost_price_rec.ESTIMATEd_price := NULL;
2179 Else
2180 l_cost_price_rec.ESTIMATEd_price := l_visit_task_rec.ESTIMATEd_price;
2181 end if;
2182 end if;
2183 end if;
2184 close c_task_rec;
2185
2186
2187
2188 -- Not to calculate cost if visit is in cancelled status
2189 IF l_visit_status <>'CANCELLED' THEN
2190
2191
2192 log_message('Before call to AHL_VWP_COST_PVT.estimate_wo_cost',l_api_name);
2193
2194 log_message('before l_cost_price_rec.visit_id'||l_cost_price_rec.visit_id,l_api_name);
2195 log_message('before call to calculate_wo_cost l_cost_price_rec.estimated_price:'||l_cost_price_rec.estimated_price,l_api_name);
2196 log_message('before call to calculate_wo_cost l_cost_price_rec.estimated_cost :'||l_cost_price_rec.estimated_cost ,l_api_name);
2197
2198 AHL_VWP_COST_PVT.calculate_wo_cost(
2199 p_api_version =>p_api_version,
2200 p_init_msg_list =>p_init_msg_list,
2201 p_commit =>FND_API.G_FALSE,
2202 p_validation_level =>p_validation_level,
2203 p_x_cost_price_rec =>l_cost_price_rec,
2204 x_return_status =>x_return_status
2205 );
2206 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2207 RAISE Fnd_Api.G_EXC_ERROR;
2208 END IF;
2209
2210 log_message('after call to calculate_wo_cost l_cost_price_rec.visit_id'||l_cost_price_rec.visit_id,l_api_name);
2211 log_message('after call to calculate_wo_cost l_cost_price_rec.estimated_price:'||l_cost_price_rec.estimated_price,l_api_name);
2212 log_message('after call to calculate_wo_cost l_cost_price_rec.estimated_cost :'||l_cost_price_rec.estimated_cost ,l_api_name);
2213
2214
2215 IF(l_cost_price_rec.Is_Cst_Struc_updated = 'N') AND (l_cost_price_rec.workorder_id IS NOT NULL)
2216 then
2217 log_message('Before call to AHL_VWP_COST_PVT.calculate_mr_cost',l_api_name);
2218 log_message('bef call to calculate_mr_cost l_cost_price_rec.visit_id'||l_cost_price_rec.visit_id,l_api_name);
2219 log_message('bef call to calculate_mr_cost l_cost_price_rec.estimated_price:'||l_cost_price_rec.estimated_price,l_api_name);
2220 log_message('bef call to calculate_mr_cost l_cost_price_rec.estimated_cost :'||l_cost_price_rec.estimated_cost ,l_api_name);
2221
2222
2223 AHL_VWP_COST_PVT.calculate_mr_cost
2224 (
2225 p_visit_task_id =>l_cost_price_rec.visit_task_id ,
2226 p_session_id =>l_cost_price_rec.mr_session_id,
2227 x_actual_cost =>l_cost_price_rec.actual_cost,
2228 x_ESTIMATEd_cost =>l_cost_price_rec.ESTIMATEd_cost,
2229 x_return_status =>x_return_status
2230 );
2231 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2232 RAISE Fnd_Api.G_EXC_ERROR;
2233 END IF;
2234
2235 log_message('after call to calculate_mr_cost l_cost_price_rec.visit_id'||l_cost_price_rec.visit_id,l_api_name);
2236 log_message('after call to calculate_mr_cost l_cost_price_rec.estimated_price:'||l_cost_price_rec.estimated_price,l_api_name);
2237 log_message('after call to calculate_mr_cost l_cost_price_rec.estimated_cost :'||l_cost_price_rec.estimated_cost ,l_api_name);
2238
2239
2240 log_message(
2241 'Before call to AHL_VWP_COST_PVT.get_profit_or_loss',l_api_name
2242 );
2243
2244
2245 AHL_VWP_COST_PVT.get_profit_or_loss
2246 (
2247 p_actual_price =>l_cost_price_rec.actual_price ,
2248 p_ESTIMATEd_price =>l_cost_price_rec.ESTIMATEd_price,
2249 p_actual_cost =>l_cost_price_rec.actual_cost,
2250 p_ESTIMATEd_cost =>l_cost_price_rec.ESTIMATEd_cost,
2251 x_actual_profit =>l_cost_price_rec.actual_profit,
2252 x_ESTIMATEd_profit =>l_cost_price_rec.ESTIMATEd_profit,
2253 x_return_status =>x_return_status
2254 );
2255 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2256 RAISE Fnd_Api.G_EXC_ERROR;
2257 END IF;
2258
2259 end if;
2260
2261
2262 END IF; -- status <> CANCELLED
2263
2264
2265 If l_cost_price_rec.MR_Id is not null
2266 then
2267 open c_mr_csr(l_cost_price_rec.MR_Id);
2268 Fetch c_mr_csr into l_mr_rec;
2269 if c_mr_csr%found
2270 then
2271 l_cost_price_rec.mr_title:=l_mr_rec.Title ;
2272 l_cost_price_rec.billing_item_id:=l_mr_rec.billing_item_id;
2273 l_cost_price_rec.mr_description := l_mr_rec.description;
2274 end if;
2275 close c_mr_csr;
2276 end if;
2277
2278 IF FND_MSG_PUB.count_msg > 0
2279 THEN
2280 x_msg_count := l_msg_count;
2281 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2282 RAISE FND_API.G_EXC_ERROR;
2283 END IF;
2284
2285 p_x_cost_price_rec:=l_cost_price_rec;
2286 log_message('billing_item_id :'||l_mr_rec.billing_item_id,l_api_name);
2287
2288
2289 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
2290 THEN
2291 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
2292 G_MODULE_NAME||'.'||l_api_name,'At the end of the procedure');
2293 END IF;
2294
2295
2296 EXCEPTION
2297 WHEN Fnd_Api.g_exc_error THEN
2298 ROLLBACK TO Get_MR_Cost_Details_PVT;
2299 x_return_status := Fnd_Api.g_ret_sts_error;
2300 Fnd_Msg_Pub.count_and_get (
2301 p_encoded => Fnd_Api.g_false,
2302 p_count => x_msg_count,
2303 p_data => x_msg_data
2304 );
2305 WHEN Fnd_Api.g_exc_unexpected_error THEN
2306 ROLLBACK TO Get_MR_Cost_Details_PVT;
2307 x_return_status := Fnd_Api.g_ret_sts_unexp_error ;
2308 Fnd_Msg_Pub.count_and_get (
2309 p_encoded => Fnd_Api.g_false,
2310 p_count => x_msg_count,
2311 p_data => x_msg_data
2312 );
2313 WHEN OTHERS THEN
2314 ROLLBACK TO Get_MR_Cost_Details_PVT;
2315 x_return_status := Fnd_Api.g_ret_sts_unexp_error ;
2316 IF Fnd_Msg_Pub.check_msg_level (Fnd_Msg_Pub.g_msg_lvl_unexp_error)
2317 THEN
2318 Fnd_Msg_Pub.add_exc_msg (G_PKG_NAME, l_api_name);
2319 END IF;
2320 Fnd_Msg_Pub.count_and_get (
2321 p_encoded => Fnd_Api.g_false,
2322 p_count => x_msg_count,
2323 p_data => x_msg_data
2324 );
2325
2326 END Get_MR_Cost_Details;
2327
2328 END AHL_VWP_MR_CST_PR_PVT;