[Home] [Help]
PACKAGE BODY: APPS.AHL_VWP_TASK_CST_PR_PVT
Source
1 PACKAGE BODY AHL_VWP_TASK_CST_PR_PVT AS
2 /* $Header: AHLVTCPB.pls 120.7 2006/05/24 13:14:53 anraj noship $ */
3 -----------------------------------------------------------
4 -- PACKAGE
5 -- Ahl_VWP_TASK_CST_PR_PVT
6 --
7 -- PURPOSE
8 -- This package is a Private API to process Estimating Cost and Price
9 -- for a Task It contains specification for pl/sql records and tables
10 --
11 --
12 -- NOTES
13 --
14 --
15 -- HISTORY
16 -- 25-AUG-2003 SSURAPAN Created.
17 --
18 --
19 -- PROCEDURES
20 -- get_task_cost_details -- update_task_cost_details
21 -- estimate_task_cost -- estimate_task_price
22 -- get_task_items_no_price
23 -- GET_OTHER_TASK_ITEMS -- GET_UNASSOCIATED_ITEMS
24
25 -----------------------------------------------------------
26 -- Declare Constants --
27 -----------------------------------------------------------
28 G_PKG_NAME CONSTANT VARCHAR(30) := 'AHL_VWP_TASK_CST_PR_PVT';
29 G_APP_NAME CONSTANT VARCHAR2(3) := 'AHL';
30 G_DEBUG VARCHAR2(1) := AHL_DEBUG_PUB.is_log_enabled;
31
32 PROCEDURE GET_OTHER_TASK_ITEMS (
33 p_api_version IN NUMBER,
34 p_init_msg_list IN VARCHAR2 := Fnd_Api.G_FALSE,
35 p_commit IN VARCHAR2 := Fnd_Api.G_FALSE,
36 p_validation_level IN NUMBER := Fnd_Api.G_VALID_LEVEL_FULL,
37 p_module_type IN VARCHAR2 := NULL,
38 x_return_status OUT NOCOPY VARCHAR2,
39 x_msg_count OUT NOCOPY NUMBER,
40 x_msg_data OUT NOCOPY VARCHAR2,
41 p_visit_task_id IN NUMBER,
42 p_job_status_code IN VARCHAR2,
43 p_task_start_time IN DATE,
44 x_item_tbl OUT NOCOPY AHL_VWP_RULES_PVT.ITEM_TBL_TYPE
45 );
46
47
48 PROCEDURE GET_UNASSOCIATED_ITEMS (
49 p_api_version IN NUMBER,
50 p_init_msg_list IN VARCHAR2 := Fnd_Api.G_FALSE,
51 p_commit IN VARCHAR2 := Fnd_Api.G_FALSE,
52 p_validation_level IN NUMBER := Fnd_Api.G_VALID_LEVEL_FULL,
53 p_module_type IN VARCHAR2 := NULL,
54 x_return_status OUT NOCOPY VARCHAR2,
55 x_msg_count OUT NOCOPY NUMBER,
56 x_msg_data OUT NOCOPY VARCHAR2,
57 p_visit_task_id IN NUMBER,
58 p_job_status_code IN VARCHAR2,
59 x_item_tbl OUT NOCOPY AHL_VWP_RULES_PVT.ITEM_TBL_TYPE
60 );
61
62 --
63 -- Start of Comments --
64 -- Procedure name : Get_Task_Cost_Details
65 -- Type : Private(Called from Estimate and cost/price Task UI
66 --
67 -- Function :
68 --
69 --
70 -- Pre-reqs :
71 -- Parameters :
72 --
73 -- Standard IN Parameters :
74 -- p_api_version IN NUMBER Required
75 -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
76 -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
77 -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
78 --
79 -- Standard OUT Parameters :
80 -- x_return_status OUT VARCHAR2 Required
81 -- x_msg_count OUT NUMBER Required
82 -- x_msg_data OUT VARCHAR2 Required
83 --
84 -- Get Task Cost Details Parameters:
85 -- p_x_cost_price_rec IN OUT NOCOPY AHL_VWP_VISIT_CST_PR_PVT.Cost_Price_Rec_Type,
86 -- Contains Cost/Price infor mation relates to Vist and its Task
87 --
88 -- Version :
89 -- Initial Version 1.0
90 --
91 -- End of Comments.
92
93 PROCEDURE Get_Task_Cost_Details (
94 p_api_version IN NUMBER,
95 p_init_msg_list IN VARCHAR2 := Fnd_Api.G_FALSE,
96 p_commit IN VARCHAR2 := Fnd_Api.G_FALSE,
97 p_validation_level IN NUMBER := Fnd_Api.G_VALID_LEVEL_FULL,
98 p_module_type IN VARCHAR2 := NULL,
99 p_x_cost_price_rec IN OUT NOCOPY AHL_VWP_VISIT_CST_PR_PVT.cost_price_rec_type,
100 x_return_status OUT NOCOPY VARCHAR2,
101 x_msg_count OUT NOCOPY NUMBER,
102 x_msg_data OUT NOCOPY VARCHAR2
103 )
104 IS
105 l_visit_status VARCHAR2(30);
106
107 -- Define Local Cursors
108 CURSOR visit_info_csr(p_visit_id IN NUMBER) IS
109 SELECT status_code
110 FROM ahl_visits_b
111 WHERE visit_id = p_visit_id;
112
113 --Cursor to retrieve Costs associated to Task
114 CURSOR Get_task_cost_price_cur (C_VISIT_TASK_ID IN NUMBER)
115 IS
116 SELECT vt.visit_id,
117 vt.visit_task_id,
118 vt.estimated_price,
119 vt.actual_price,
120 vt.price_list_id,
121 vt.mr_route_id,
122 vt.mr_id,
123 vs.outside_party_flag,
124 vs.start_date_time,
125 vs.close_date_time,
126 ci.customer_id
127
128 FROM ahl_visit_tasks_b vt,
129 ahl_visits_b vs,
130 cs_incidents_all_b ci
131
132 WHERE vt.visit_task_id = C_VISIT_TASK_ID
133 AND vt.visit_id=vs.visit_id
134 AND vs.service_request_id = ci.incident_id(+)
135 AND NVL(vt.status_code, 'Y') <> NVL ('DELETED', 'X');
136
137
138 --Cursor to retrieve name for defined price list id
139 CURSOR Get_price_list_name_cur (C_LIST_HEADER_ID IN NUMBER)
140 IS
141 SELECT name
142 FROM qp_list_headers
143 WHERE list_header_id = C_LIST_HEADER_ID;
144
145 -- AnRaj: Query changed for fixing performance bug 4919475
146 -- Get Billing item id for the associated mr
147 /* SELECT mh.billing_item_id,mh.billing_item, mh.title
148 FROM ahl_mr_routes_v mr, ahl_mr_headers_v mh
149 WHERE mr.mr_header_id = mh.mr_header_id
150 AND mr.mr_header_id =C_MR_ID;*/
151 CURSOR Get_billing_item_cur (C_MR_ID IN NUMBER)
152 IS
153 SELECT mh.billing_item_id,mtl.CONCATENATED_SEGMENTS billing_item, mh.title
154 FROM ahl_mr_routes_v mr, AHL_MR_HEADERS_VL mh,MTL_SYSTEM_ITEMS_KFV mtl
155 WHERE mr.mr_header_id = mh.mr_header_id
156 AND mtl.INVENTORY_ITEM_ID= MH.BILLING_ITEM_ID
157 AND mr.mr_header_id = C_MR_ID
158 AND mh.APPLICATION_USG_CODE=RTRIM(LTRIM(FND_PROFILE.VALUE('AHL_APPLN_USAGE'))) ;
159
160
161 --Added by amagrawa to retrieve task number from Task ID
162 CURSOR c_task_number (p_task_id IN NUMBER)
163 IS
164 SELECT VISIT_TASK_NUMBER
165 FROM ahl_visit_tasks_b
166 where visit_task_id = p_task_id;
167
168 --Standard local variables
169 l_task_number number;
170 l_api_name CONSTANT VARCHAR2(30) := 'Get_Task_Cost_Details';
171 l_api_version CONSTANT NUMBER := 1.0;
172
173 l_msg_data VARCHAR2(2000);
174 l_return_status VARCHAR2(1);
175 l_msg_count NUMBER;
176
177 l_default VARCHAR2(30);
178 l_error_msg_code VARCHAR2(30);
179 l_module_type VARCHAR2(10);
180 l_found VARCHAR2(1);
181 l_valid_flag VARCHAR2(1);
182 l_commit VARCHAR2(10) := Fnd_Api.G_FALSE;
183
184 l_wo_actual_cost NUMBER;
185 l_wo_estimated_cost NUMBER;
186 l_idx NUMBER;
187 j NUMBER := 0;
188
189 l_task_cost_price_rec Get_task_cost_price_cur%ROWTYPE;
190 l_cost_price_rec AHL_VWP_VISIT_CST_PR_PVT.cost_price_rec_type := p_x_cost_price_rec;
191
192 BEGIN
193 --
194 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)THEN
195 fnd_log.string
196 (
197 fnd_log.level_procedure,
198 'ahl.plsql.AHL_VWP_TASK_CST_PR_PVT.Get_Task_Cost_Details.begin',
199 'At the start of PLSQL procedure'
200 );
201 END IF;
202
203 -- Standard start of API savepoint
204 SAVEPOINT Get_Task_Cost_Details;
205
206 -- Initialize message list if p_init_msg_list is set to TRUE
207 IF FND_API.To_Boolean( p_init_msg_list) THEN
208 FND_MSG_PUB.Initialize;
209 END IF;
210
211 -- Initialize API return status to success
212 x_return_status := FND_API.G_RET_STS_SUCCESS;
213
214 --
215 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
216 fnd_log.string
217 (
218 fnd_log.level_statement,
219 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
220 'Request for cost details of Visit Task ID : ' || l_cost_price_rec.visit_task_id
221 );
222 fnd_log.string
223 (
224 fnd_log.level_statement,
225 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
226 'Request for task cost details of Cost Session ID : ' || l_cost_price_rec.cost_session_id
227 );
228 fnd_log.string
229 (
230 fnd_log.level_statement,
231 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
232 'Request for task cost details of MR Session ID : ' || l_cost_price_rec.mr_session_id
233 );
234 END IF;
235
236 -- Check for Required Parameters
237 IF(l_cost_price_rec.visit_task_id IS NULL OR
238 l_cost_price_rec.visit_task_id = FND_API.G_MISS_NUM) THEN
239 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_VWP_CST_INPUT_MISS');
240 FND_MSG_PUB.ADD;
241 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level)THEN
242 fnd_log.string
243 (
244 fnd_log.level_error,
245 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
246 'Visit Task id is mandatory but found null in input '
247 );
248 END IF;
249 RAISE FND_API.G_EXC_ERROR;
250 END IF;
251
252 -- Get Cost and Price associated to task
253 OPEN Get_task_cost_price_cur(p_x_cost_price_rec.visit_task_id);
254 FETCH Get_task_cost_price_cur INTO l_task_cost_price_rec;
255 IF Get_task_cost_price_cur%NOTFOUND THEN
256 --Added by amagrawa
257 OPEN c_task_number(p_x_cost_price_rec.visit_task_id);
258 FETCH c_task_number into l_task_number;
259 close c_task_number;
260 --End of changes by amagrawa
261 FND_MESSAGE.set_name( 'AHL','AHL_VWP_TASK_INVALID' );
262 FND_MESSAGE.SET_TOKEN('TASK_NUM',l_task_number);
263 FND_MSG_PUB.add;
264 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level)THEN
265 fnd_log.string
266 (
267 fnd_log.level_error,
268 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
269 'Visit Task not found in ahl_visit_tasks_b table'
270 );
271 END IF;
272 CLOSE Get_task_cost_price_cur;
273 RAISE FND_API.G_EXC_ERROR;
274 END IF;
275 CLOSE Get_task_cost_price_cur;
276
277 -- Assign values to out variable
278 l_cost_price_rec.ACTUAL_PRICE := l_task_cost_price_rec.actual_price;
279 l_cost_price_rec.ESTIMATED_PRICE := l_task_cost_price_rec.estimated_price;
280 l_cost_price_rec.PRICE_LIST_ID := l_task_cost_price_rec.price_list_id;
281 l_cost_price_rec.VISIT_ID := l_task_cost_price_rec.visit_id;
282 l_cost_price_rec.OUTSIDE_PARTY_FLAG := l_task_cost_price_rec.outside_party_flag;
283 l_cost_price_rec.CUSTOMER_ID := l_task_cost_price_rec.customer_id;
284 l_cost_price_rec.MR_ID := l_task_cost_price_rec.mr_id;
285 -- Both task dates are visit's start date and planned end dates
286 l_cost_price_rec.task_start_date := l_task_cost_price_rec.start_date_time;
287 l_cost_price_rec.task_end_date := l_task_cost_price_rec.close_date_time;
288
289 --log messages
290 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
291 fnd_log.string
292 (
293 fnd_log.level_statement,
294 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
295 'Actual Price : '||l_cost_price_rec.ACTUAL_PRICE
296 );
297 fnd_log.string
298 (
299 fnd_log.level_statement,
300 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
301 'Estimated Price : '||l_cost_price_rec.ESTIMATED_PRICE
302 );
303 fnd_log.string
304 (
305 fnd_log.level_statement,
306 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
307 'Price List Id: '||l_cost_price_rec.PRICE_LIST_ID
308 );
309 fnd_log.string
310 (
311 fnd_log.level_statement,
312 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
313 'Visit Id : '||l_cost_price_rec.VISIT_ID
314 );
315 fnd_log.string
316 (
317 fnd_log.level_statement,
318 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
319 'Outside Party Flag : '||l_cost_price_rec.OUTSIDE_PARTY_FLAG
320 );
321 fnd_log.string
322 (
323 fnd_log.level_statement,
324 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
325 'Customer Id: '||l_cost_price_rec.CUSTOMER_ID
326 );
327 fnd_log.string
328 (
329 fnd_log.level_statement,
330 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
331 'MR Route Id: '||l_task_cost_price_rec.mr_route_id
332 );
333 END IF;
334
335
336 --Get Price list name from qp_list_headers table
337 IF l_task_cost_price_rec.price_list_id IS NOT NULL THEN
338 --
339 OPEN Get_price_list_name_cur(l_task_cost_price_rec.price_list_id);
340 FETCH Get_price_list_name_cur INTO l_cost_price_rec.price_list_name;
341 IF Get_price_list_name_cur%NOTFOUND THEN
342 FND_MESSAGE.set_name( 'AHL','AHL_VWP_PRICE_LIST_INVALID' );
343 FND_MSG_PUB.add;
344 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level)THEN
345 fnd_log.string
346 (
347 fnd_log.level_error,
348 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
349 'Price List Name not found in qp_list_headers table'
350 );
351 END IF;
352 CLOSE Get_price_list_name_cur;
353 RAISE FND_API.G_EXC_ERROR;
354 END IF;
355 CLOSE Get_price_list_name_cur;
356
357 END IF; -- Price list not null
358
359 --Log messages
360 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
361 fnd_log.string
362 (
363 fnd_log.level_statement,
364 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
365 'Price List Name from QP_LIST_HEADERS: '||l_cost_price_rec.price_list_name
366 );
367 fnd_log.string
368 (
369 fnd_log.level_statement,
370 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
371 'Mr Route Id : '||l_task_cost_price_rec.MR_ROUTE_ID
372 );
373 END IF;
374
375 -- Get billing item id
376 IF (l_task_cost_price_rec.mr_id IS NOT NULL AND
377 l_task_cost_price_rec.mr_id <> FND_API.G_MISS_NUM ) THEN
378 -- Retrieve billing item
379 OPEN Get_billing_item_cur(l_task_cost_price_rec.mr_id);
380 FETCH Get_billing_item_cur INTO l_cost_price_rec.billing_item_id,l_cost_price_rec.item_name,l_cost_price_rec.mr_title;
381 CLOSE Get_billing_item_cur;
382 END IF; --Mr route id not null
383
384 --Log messages
385 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
386 fnd_log.string
387 (
388 fnd_log.level_statement,
389 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
390 'Billing Item Id : '||l_cost_price_rec.billing_item_id
391 );
392 fnd_log.string
393 (
394 fnd_log.level_statement,
395 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
396 'Is Cst Pr Info Required flag : '||l_cost_price_rec.Is_Cst_Pr_Info_Required
397 );
398 fnd_log.string
399 (
400 fnd_log.level_statement,
401 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
402 'Billing Item : '||l_cost_price_rec.item_name
403 );
404 END IF;
405 --
406 OPEN visit_info_csr(l_cost_price_rec.visit_id);
407 FETCH visit_info_csr INTO l_visit_status;
408
409 IF (visit_info_csr%NOTFOUND)THEN
410 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_VWP_CST_INV_VISIT');
411 FND_MSG_PUB.ADD;
412 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)THEN
413 fnd_log.string
414 (
415 fnd_log.level_exception,
416 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
417 'Visit id not found in ahl_visits_b table'
418 );
419 END IF;
420 CLOSE visit_info_csr;
421 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
422 END IF;
423 CLOSE visit_info_csr;
424
425 -- Not to calculate cost if visit is in cancelled status
426 IF l_visit_status <>'CANCELLED' THEN
427
428 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)THEN
429 fnd_log.string
430 (
431 fnd_log.level_procedure,
432 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
433 'Before calling ahl_vwp_cost_pvt.Calculate Wo Cost'
434 );
435 END IF;
436
437 --Call ahl_vwp_cost_pvt.calculate_wo_cose
438 ahl_vwp_cost_pvt.calculate_wo_cost
439 ( p_x_cost_price_rec => l_cost_price_rec,
440 x_return_status => l_return_status);
441
442 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
443 fnd_log.string
444 (
445 fnd_log.level_procedure,
446 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||': End API',
447 'After calling Calculate wo cost Return Status is: '|| l_return_status
448 );
449 END IF;
450
451 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
452 fnd_log.string
453 (
454 fnd_log.level_statement,
455 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
456 'Cost Session ID : ' || l_cost_price_rec.cost_session_id
457 );
458 fnd_log.string
459 (
460 fnd_log.level_statement,
461 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
462 'MR Session ID : ' || l_cost_price_rec.mr_session_id
463 );
464 END IF;
465
466 -- Check Error Message stack.
467 l_msg_count := FND_MSG_PUB.count_msg;
468 IF l_msg_count > 0 THEN
469 x_return_status := FND_API.G_RET_STS_ERROR;
470 RAISE FND_API.G_EXC_ERROR;
471 END IF;
472
473 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
474 fnd_log.string
475 (
476 fnd_log.level_statement,
477 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||': Derieved',
478 'Is Cst Struc Updated Flag: '|| l_cost_price_rec.Is_Cst_Struc_updated
479 );
480 END IF;
481
482 IF(l_cost_price_rec.Is_Cst_Struc_updated = 'N') AND (l_cost_price_rec.workorder_id IS NOT NULL) THEN
483
484 --Log message
485 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
486 fnd_log.string
487 (
488 fnd_log.level_statement,
489 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
490 'Inside Cost Struc Updated flag = N, Workorder Id: '|| l_cost_price_rec.workorder_id
491 );
492 fnd_log.string
493 (
494 fnd_log.level_statement,
495 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
496 ' MR Session ID : ' || l_cost_price_rec.mr_session_id
497 );
498 fnd_log.string
499 (
500 fnd_log.level_statement,
501 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
502 'Before calling ahl vwp cost pvt.calculate task cost '
503 );
504 END IF;
505
506 ahl_vwp_cost_pvt.calculate_task_cost
507 (
508 p_visit_task_id => l_cost_price_rec.visit_task_id,
509 p_session_id => l_cost_price_rec.mr_session_id,
510 x_Actual_cost => l_wo_actual_cost,
511 x_Estimated_cost => l_wo_estimated_cost,
512 x_return_status => l_return_status);
513
514 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
515 fnd_log.string
516 (
517 fnd_log.level_procedure,
518 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||': End API',
519 'After calling ahl vwp cost pvt.Calculate task Cost Return Status is: '|| l_return_status
520 );
521 END IF;
522
523 -- Check Error Message stack.
524 l_msg_count := FND_MSG_PUB.count_msg;
525 IF l_msg_count > 0 THEN
526 x_return_status := FND_API.G_RET_STS_ERROR;
527 RAISE FND_API.G_EXC_ERROR;
528 END IF;
529
530 --Log message
531 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
532 fnd_log.string
533 (
534 fnd_log.level_statement,
535 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':Derieved ',
536 'Actual Cost : '|| l_wo_actual_cost
537 );
538 fnd_log.string
539 (
540 fnd_log.level_statement,
541 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':Derieved',
542 'Estimated Cost : '|| l_wo_estimated_cost
543 );
544
545 END IF;
546
547 --Assign derived values
548 l_cost_price_rec.actual_cost := l_wo_actual_cost;
549 l_cost_price_rec.estimated_cost := l_wo_estimated_cost;
550
551 END IF;
552
553 --Log message
554 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
555 fnd_log.string
556 (
557 fnd_log.level_procedure,
558 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
559 'Before calling ahl vwp cost pvt.get profit or loss '
560 );
561 END IF;
562
563 -- Call get proft or loss
564 ahl_vwp_cost_pvt.get_profit_or_loss
565 (
566 p_actual_price => l_cost_price_rec.actual_price,
567 p_estimated_price => l_cost_price_rec.estimated_price,
568 p_actual_cost => l_cost_price_rec.actual_cost,
569 p_estimated_cost => l_cost_price_rec.estimated_cost,
570 x_actual_profit => l_cost_price_rec.actual_profit,
571 x_estimated_profit => l_cost_price_rec.estimated_profit,
572 x_return_status => l_return_status);
573
574 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
575 fnd_log.string
576 (
577 fnd_log.level_procedure,
578 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||': End API',
579 'After calling ahl vwp cost pvt.Get Profit Or Loss Return Status is: '|| l_return_status
580 );
581
582 END IF;
583
584 -- Check Error Message stack.
585 l_msg_count := FND_MSG_PUB.count_msg;
586 IF l_msg_count > 0 THEN
587 x_return_status := FND_API.G_RET_STS_ERROR;
588 RAISE FND_API.G_EXC_ERROR;
589 END IF;
590
591 --Log message
592 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
593 fnd_log.string
594 (
595 fnd_log.level_statement,
596 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':Derieved ',
597 'Actual Profit : '|| l_cost_price_rec.actual_profit
598 );
599
600 fnd_log.string
601 (
602 fnd_log.level_statement,
603 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':Derieved',
604 'Estimated Profit : '|| l_cost_price_rec.estimated_profit
605 );
606
607 END IF;
608
609 END IF; -- status <> CANCELLED
610
611 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
612
613 fnd_log.string
614 (
615 fnd_log.level_statement,
616 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
617 'Before calling ahl vwp cost pvt.check currency for costing '
618 );
619 END IF;
620 -- Get Currency
621 ahl_vwp_rules_pvt.check_currency_for_costing
622 (
623 p_visit_id => l_cost_price_rec.visit_id,
624 x_currency_code => l_cost_price_rec.currency);
625
626
627 --Log message
628 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
629
630 fnd_log.string
631 (
632 fnd_log.level_statement,
633 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':Derieved ',
634 'Curency code : '|| l_cost_price_rec.currency
635 );
636 END IF;
637
638 -- Assign derived values
639 p_x_cost_price_rec := l_cost_price_rec;
640
641 --Log messages
642 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
643 fnd_log.string
644 (
645 fnd_log.level_statement,
646 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':Derieved ',
647 'Actual Price : '|| p_x_cost_price_rec.actual_price
648 );
649 fnd_log.string
650 (
651 fnd_log.level_statement,
652 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':Derieved',
653 'Estimated Price : '|| p_x_cost_price_rec.estimated_price
654 );
655
656 fnd_log.string
657 (
658 fnd_log.level_statement,
659 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':Derieved',
660 'Actual Cost : '|| p_x_cost_price_rec.actual_cost
661 );
662
663 fnd_log.string
664 (
665 fnd_log.level_statement,
666 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':Derieved',
667 'Estimated Cost : '|| p_x_cost_price_rec.estimated_cost
668 );
669
670 fnd_log.string
671 (
672 fnd_log.level_statement,
673 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':Derieved',
674 'Actual Profit : '|| p_x_cost_price_rec.actual_profit
675 );
676
677 fnd_log.string
678 (
679 fnd_log.level_statement,
680 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':Derieved',
681 'Estimated Profit : '|| p_x_cost_price_rec.estimated_profit
682 );
683
684 fnd_log.string
685 (
686 fnd_log.level_statement,
687 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':Derieved',
688 'Cost Session Id : '|| p_x_cost_price_rec.cost_session_id
689 );
690
691 fnd_log.string
692 (
693 fnd_log.level_statement,
694 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':Derieved',
695 'Mr Session Id : '|| p_x_cost_price_rec.mr_session_id
696 );
697
698 fnd_log.string
699 (
700 fnd_log.level_statement,
701 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':Derieved',
702 'Price list Id : '|| p_x_cost_price_rec.price_list_id
703 );
704
705 fnd_log.string
706 (
707 fnd_log.level_statement,
708 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':Derieved',
709 'Price list Name : '|| p_x_cost_price_rec.price_list_name
710 );
711 fnd_log.string
712 (
713 fnd_log.level_statement,
714 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':Derieved',
715 'OutSide Party Flag : '|| p_x_cost_price_rec.outside_party_flag
716 );
717 fnd_log.string
718 (
719 fnd_log.level_statement,
720 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':Derieved',
721 'Customer Id : '|| p_x_cost_price_rec.customer_id
722 );
723 fnd_log.string
724 (
725 fnd_log.level_statement,
726 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':Derieved',
727 'Workorder Id : '|| p_x_cost_price_rec.workorder_id
728 );
729 fnd_log.string
730 (
731 fnd_log.level_statement,
732 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':Derieved',
733 'Task Start Date : '|| p_x_cost_price_rec.task_start_date
734 );
735 fnd_log.string
736 (
737 fnd_log.level_statement,
738 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':Derieved',
739 'Task End Date : '|| p_x_cost_price_rec.task_end_date
740 );
741
742 fnd_log.string
743 (
744 fnd_log.level_statement,
745 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
746 'Billing Item : '||p_x_cost_price_rec.item_name
747 );
748
749 END IF;
750
751 -- Check Error Message stack.
752 x_msg_count := FND_MSG_PUB.count_msg;
753 IF x_msg_count > 0 THEN
754 RAISE FND_API.G_EXC_ERROR;
755 END IF;
756
757 -- Standard check of p_commit
758 p_x_cost_price_rec:=l_cost_price_rec;
759 IF FND_API.TO_BOOLEAN(p_commit) THEN
760 COMMIT WORK;
761 END IF;
762
763 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)THEN
764 fnd_log.string
765 (
766 fnd_log.level_procedure,
767 'ahl.plsql.AHL_VWP_TASK_CST_PR_PVT.Get_Task_Cost_Details.end',
768 'At the end of PLSQL procedure'
769 );
770 END IF;
771 EXCEPTION
772 WHEN FND_API.G_EXC_ERROR THEN
773 x_return_status := FND_API.G_RET_STS_ERROR;
774 ROLLBACK TO Get_Task_Cost_Details;
775 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
776 p_data => x_msg_data,
777 p_encoded => fnd_api.g_false);
778
779
780 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
781 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
782 ROLLBACK TO Get_Task_Cost_Details;
783 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
784 p_data => x_msg_data,
785 p_encoded => fnd_api.g_false);
786
787
788 WHEN OTHERS THEN
789 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
790 ROLLBACK TO Get_Task_Cost_Details;
791 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
792 fnd_msg_pub.add_exc_msg(p_pkg_name => G_PKG_NAME,
793 p_procedure_name => 'Get_Task_Cost_Details',
794 p_error_text => SUBSTR(SQLERRM,1,500));
795 END IF;
796 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
797 p_data => x_msg_data,
798 p_encoded => fnd_api.g_false);
799
800 END Get_Task_Cost_Details;
801
802 -- Start of Comments --
803 -- Procedure name : Update Task Cost Details
804 -- Type : Private(Called from Estimate and cost/price Task UI
805 --
806 -- Function : To update task price list
807 --
808 --
809 -- Pre-reqs :
810 -- Parameters :
811 --
812 -- Standard IN Parameters :
813 -- p_api_version IN NUMBER Required
814 -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
815 -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
816 -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
817 --
818 -- Standard OUT Parameters :
819 -- x_return_status OUT VARCHAR2 Required
820 -- x_msg_count OUT NUMBER Required
821 -- x_msg_data OUT VARCHAR2 Required
822 --
823 -- Update Task Cost Details Parameters:
824 -- p_cost_price_rec IN Cost_price_rec_type, Required
825 --
826 -- Version :
827 -- Initial Version 1.0
828 --
829 -- End of Comments.
830
831 PROCEDURE Update_Task_Cost_Details (
832 p_api_version IN NUMBER,
833 p_init_msg_list IN VARCHAR2 := Fnd_Api.G_FALSE,
834 p_commit IN VARCHAR2 := Fnd_Api.G_FALSE,
835 p_validation_level IN NUMBER := Fnd_Api.G_VALID_LEVEL_FULL,
836 p_module_type IN VARCHAR2 := NULL,
837 p_cost_price_rec IN AHL_VWP_VISIT_CST_PR_PVT.cost_price_rec_type,
838 x_return_status OUT NOCOPY VARCHAR2,
839 x_msg_count OUT NOCOPY NUMBER,
840 x_msg_data OUT NOCOPY VARCHAR2
841 )
842 IS
843 -- Get visit task details
844 CURSOR Get_visit_task_dtls_cur (c_visit_task_id IN NUMBER)
845 IS
846 SELECT vt.price_list_id,
847 vt.visit_id,
848 vt.visit_task_id,
849 vt.object_version_number,
850 vt.visit_task_number,
851 vt.visit_task_name
852 FROM ahl_visit_tasks_vl vt,
853 cs_incidents_all_b ci
854 WHERE visit_task_id = c_visit_task_id
855 AND NVL(vt.status_code, 'Y') <> NVL ('DELETED', 'X');
856 l_visit_task_dtls_rec Get_visit_task_dtls_cur%ROWTYPE;
857
858 -- Get visit details
859 CURSOR Get_visit_dtls_cur (c_visit_id IN NUMBER)
860 IS
861 SELECT vs.visit_id,
862 vs.start_date_time,
863 vs.close_date_time,
864 vs.service_request_id,
865 ci.customer_id
866 FROM ahl_visits_vl vs,
867 cs_incidents_all_b ci
868 WHERE visit_id = c_visit_id
869 AND vs.service_request_id = ci.incident_id(+)
870 AND NVL(vs.status_code, 'Y') <> NVL ('DELETED', 'X');
871 l_visit_dtls_rec Get_visit_dtls_cur%ROWTYPE;
872
873
874 CURSOR price_list_id_csr(p_price_list_name IN VARCHAR2,p_customer_id IN NUMBER)
875 IS
876 SELECT qlhv.list_header_id
877 FROM qp_list_headers_vl qlhv, qp_qualifiers qpq
878 WHERE qlhv.list_type_code = 'PRL'
879 AND upper(qlhv.name) like upper(p_price_list_name)
880 AND qpq.QUALIFIER_ATTR_VALUE = p_customer_id
881 AND qpq.list_header_id=qlhv.list_header_id
882 AND qpq.qualifier_context = 'CUSTOMER'
883 AND qpq.qualifier_attribute = 'QUALIFIER_ATTRIBUTE16';
884
885
886 -- Get price list name
887 CURSOR Get_price_list_cur (c_price_list_id IN NUMBER)
888 IS
889 SELECT start_date_active,
890 end_date_active
891 FROM QP_LIST_HEADERS
892 WHERE list_header_id = c_price_list_id;
893 l_price_list_rec Get_price_list_cur%ROWTYPE;
894
895 -- Local Variables
896 l_api_name CONSTANT VARCHAR2(30) := 'Update_Task_Cost_Details';
897 l_api_version CONSTANT NUMBER := 1.0;
898
899 l_msg_data VARCHAR2(2000);
900 l_default VARCHAR2(30);
901 l_error_msg_code VARCHAR2(30);
902 l_module_type VARCHAR2(10);
903 l_commit VARCHAR2(10) := Fnd_Api.G_FALSE;
904 l_return_status VARCHAR2(1);
905 l_flag VARCHAR2(1);
906 l_valid_flag VARCHAR2(1);
907
908 l_price_list_active_start_date DATE;
909 l_price_list_active_end_date DATE;
910 l_start_date_time DATE;
911 l_close_date_time DATE;
912
913 x_price_list_id NUMBER;
914 p_price_list_name VARCHAR2(30);
915 l_msg_count NUMBER;
916 i NUMBER;
917
918 l_cost_price_rec AHL_VWP_VISIT_CST_PR_PVT.cost_price_rec_type := p_cost_price_rec;
919
920 BEGIN
921
922 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)THEN
923 fnd_log.string
924 (
925 fnd_log.level_procedure,
926 'ahl.plsql.AHL_VWP_TASK_CST_PR_PVT.Update_Task_Cost_Details.begin',
927 'At the start of PLSQL procedure'
928 );
929
930 END IF;
931
932 -- Standard start of API savepoint
933 SAVEPOINT Update_Task_Cost_Details;
934
935 -- Initialize message list if p_init_msg_list is set to TRUE
936 IF FND_API.To_Boolean( p_init_msg_list) THEN
937 FND_MSG_PUB.Initialize;
938 END IF;
939
940 -- Initialize API return status to success
941 x_return_status := FND_API.G_RET_STS_SUCCESS;
942 --
943 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
944 fnd_log.string
945 (
946 fnd_log.level_statement,
947 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
948 'Request for Updating cost details for Task ID : ' || l_cost_price_rec.visit_task_id
949 );
950 END IF;
951
952 -- Check for Required Parameters
953 IF(l_cost_price_rec.visit_task_id IS NULL OR
954 l_cost_price_rec.visit_task_id = FND_API.G_MISS_NUM) THEN
955 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_VWP_CST_INPUT_MISS');
956 FND_MSG_PUB.ADD;
957 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level)THEN
958 fnd_log.string
959 (
960 fnd_log.level_error,
961 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
962 'Visit Task id is mandatory but found null in input '
963 );
964 END IF;
965 RAISE FND_API.G_EXC_ERROR;
966 END IF;
967
968 -- Retrieve task related information
969 OPEN Get_visit_task_dtls_cur(l_cost_price_rec.visit_task_id);
970 FETCH Get_visit_task_dtls_cur INTO l_visit_task_dtls_rec;
971 IF Get_visit_task_dtls_cur%NOTFOUND THEN
972 FND_MESSAGE.set_name( 'AHL','AHL_VWP_TASK_INVALID' );
973 FND_MESSAGE.Set_Token('TASK_NUM', l_cost_price_rec.visit_task_number);
974 FND_MSG_PUB.add;
975 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level)THEN
976 fnd_log.string
977 (
978 fnd_log.level_error,
979 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
980 'Task id not found in ahl_visit_tasks_b table'
981 );
982 END IF;
983 CLOSE Get_visit_task_dtls_cur;
984 RAISE FND_API.G_EXC_ERROR;
985 END IF;
986 CLOSE Get_visit_task_dtls_cur;
987
988 --Assign
989 l_cost_price_rec.task_name := l_visit_task_dtls_rec.visit_task_name;
990 l_cost_price_rec.visit_id := l_visit_task_dtls_rec.visit_id;
991 l_cost_price_rec.object_version_number := l_visit_task_dtls_rec.object_version_number;
992
993 -- Retrieve visit related information
994 OPEN Get_visit_dtls_cur(l_cost_price_rec.visit_id);
995 FETCH Get_visit_dtls_cur INTO l_visit_dtls_rec;
996 IF Get_visit_dtls_cur%NOTFOUND THEN
997 FND_MESSAGE.set_name( 'AHL','AHL_VWP_VISIT_INVALID' );
998 FND_MSG_PUB.add;
999 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level)THEN
1000 fnd_log.string
1001 (
1002 fnd_log.level_error,
1003 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
1004 'Visit id not found in ahl_visits_b table'
1005 );
1006 END IF;
1007 CLOSE Get_visit_dtls_cur;
1008 RAISE FND_API.G_EXC_ERROR;
1009 END IF;
1010 CLOSE Get_visit_dtls_cur;
1011
1012 -- Assign
1013 l_cost_price_rec.visit_start_date := trunc(l_visit_dtls_rec.start_date_time);
1014 l_cost_price_rec.visit_end_date := trunc(l_visit_dtls_rec.close_date_time);
1015
1016 -- Convert price list name to price list ID
1017 l_cost_price_rec.price_list_id := NULL;
1018 IF l_cost_price_rec.price_list_name IS NOT NULL THEN
1019 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
1020 fnd_log.string
1021 (
1022 fnd_log.level_statement,
1023 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
1024 'Price List conversion : ' || l_cost_price_rec.price_list_name
1025 );
1026 END IF;
1027 p_price_list_name := l_cost_price_rec.price_list_name;
1028
1029 -- First look if any SR for visit and if has customer defined
1030 IF l_visit_dtls_rec.service_request_id IS NOT NULL AND
1031 l_visit_dtls_rec.customer_id IS NOT NULL THEN
1032
1033 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
1034 fnd_log.string
1035 (
1036 fnd_log.level_statement,
1037 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
1038 'Check for customer Id and service request id : '
1039 );
1040 END IF;
1041
1042 l_cost_price_rec.customer_id := l_visit_dtls_rec.customer_id;
1043
1044 -- Find out the price list id
1045 OPEN price_list_id_csr(p_price_list_name,l_visit_dtls_rec.customer_id);
1046 FETCH price_list_id_csr INTO l_cost_price_rec.price_list_id;
1047 IF (price_list_id_csr%NOTFOUND)THEN
1048 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_VWP_CST_PLIST_NFOUND');
1049 FND_MESSAGE.Set_Token('PRICE_LIST',p_price_list_name);
1050 FND_MSG_PUB.ADD;
1051
1052 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level)THEN
1053 fnd_log.string
1054 (
1055 fnd_log.level_error,
1056 'ahl.plsql.'||G_PKG_NAME||'.'||L_API_NAME,
1057 'Valid price list not found with price list name ' || p_price_list_name
1058 );
1059 END IF;
1060
1061 x_return_status := FND_API.G_RET_STS_ERROR;
1062 END IF;
1063 CLOSE price_list_id_csr;
1064
1065
1066 END IF;
1067 END IF;
1068
1069 --
1070 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
1071 THEN
1072 fnd_log.string
1073 (
1074 fnd_log.level_statement,
1075 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
1076 'Price List Id:'||l_cost_price_rec.price_list_id
1077 );
1078 END IF;
1079
1080 IF l_cost_price_rec.price_list_id IS NOT NULL THEN
1081 OPEN Get_price_list_cur(l_cost_price_rec.price_list_id);
1082 FETCH Get_price_list_cur INTO l_price_list_rec;
1083 IF Get_price_list_cur%NOTFOUND THEN
1084 FND_MESSAGE.set_name( 'AHL','AHL_VWP_PRICE_LIST_INVALID' );
1085 FND_MSG_PUB.add;
1086 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level)THEN
1087 fnd_log.string
1088 (
1089 fnd_log.level_error,
1090 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
1091 'Price List Name not found in qp_list_headers table'
1092 );
1093 END IF;
1094 END IF;
1095 CLOSE Get_price_list_cur;
1096
1097 -- Check for dates fall in between price list
1098 -- compare it with visit start and end dates
1099 l_price_list_active_start_date := l_price_list_rec.start_date_active;
1100 l_price_list_active_end_date := l_price_list_rec.end_date_active;
1101 l_start_date_time := l_cost_price_rec.visit_start_date;
1102 l_close_date_time := l_cost_price_rec.visit_end_date;
1103
1104 IF(l_price_list_active_start_date IS NOT NULL OR l_price_list_active_end_date IS NOT NULL)THEN
1105
1106 -- Check if the visit start date and visit planned end date if not null
1107 IF (l_start_date_time IS NOT NULL OR l_close_date_time IS NOT NULL ) THEN
1108
1109 -- visit start date validation
1110 IF (l_start_date_time IS NOT NULL)THEN
1111
1112 IF (TRUNC(l_price_list_active_start_date) > TRUNC(l_start_date_time)) OR
1113 (TRUNC(l_price_list_active_end_date) < TRUNC(l_start_date_time)) THEN
1114
1115 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_VWP_CST_PRICE_LIST_INV_STR');
1116 FND_MSG_PUB.ADD;
1117 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level)THEN
1118 fnd_log.string
1119 (
1120 fnd_log.level_error,
1121 'ahl.plsql.AHL_VWP_VISIT_CST_PR_PVT.update_task_cost_details',
1122 'Price List is not active on visit start date'
1123 );
1124 END IF;
1125
1126 END IF;
1127
1128 END IF; -- End of visit start_date check
1129
1130 -- visit planned end date validation
1131 IF (l_close_date_time IS NOT NULL)THEN
1132
1133 IF (TRUNC(l_price_list_active_start_date) > TRUNC(l_close_date_time)) OR
1134 (TRUNC(l_price_list_active_end_date) < TRUNC(l_close_date_time)) THEN
1135
1136 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_VWP_CST_PRICE_LIST_INV_END');
1137 FND_MSG_PUB.ADD;
1138 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level)THEN
1139 fnd_log.string
1140 (
1141 fnd_log.level_error,
1142 'ahl.plsql.AHL_VWP_VISIT_CST_PR_PVT.update_task_cost_details',
1143 'Price List is not active on visit end date'
1144 );
1145 END IF;
1146
1147 END IF;
1148
1149 END IF; -- End of l_close_date_time visit planned end date check
1150
1151 ELSE -- Else of visit start date and visit planned end date null check
1152
1153 -- Check if the visit start date and visit planned end date are null
1154 -- then validate with current sysdate
1155 IF (l_price_list_active_start_date IS NOT NULL AND TRUNC(l_price_list_active_start_date) > TRUNC(sysdate))
1156 OR
1157 (l_price_list_active_end_date IS NOT NULL AND TRUNC(l_price_list_active_end_date) < TRUNC(sysdate))
1158 THEN
1159
1160 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_VWP_CST_PRICE_LIST_INV_SYS');
1161 -- CHANGE THIS MESSAGE TEST AND NAME TOO -- IMPORTANT
1162 FND_MSG_PUB.ADD;
1163 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level)THEN
1164 fnd_log.string
1165 (
1166 fnd_log.level_error,
1167 'ahl.plsql.AHL_VWP_VISIT_CST_PR_PVT.update_task_cost_details',
1168 'Price List is not active on current todays date'
1169 );
1170 END IF;
1171
1172 END IF;
1173
1174 END IF; -- End of visit start_date and planned end date check
1175
1176 END IF; -- End of price_list active_start_date and active_end_date check
1177
1178 END IF; -- Check for price list id not null
1179
1180 -- End of changes by Shbhanda on 30th Dec 2003 --
1181
1182 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
1183 fnd_log.string
1184 (
1185 fnd_log.level_statement,
1186 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
1187 ' Before Update Ahl Visit Tasks B Table, Price List Id: ' || l_cost_price_rec.price_list_id
1188 );
1189 fnd_log.string
1190 (
1191 fnd_log.level_statement,
1192 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
1193 ' Before Update Ahl Visit Tasks B Table, Estimated Price: ' || l_cost_price_rec.estimated_price
1194 );
1195 fnd_log.string
1196 (
1197 fnd_log.level_statement,
1198 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
1199 ' Before Update Ahl Visit Tasks B Table, Actual Price: ' || l_cost_price_rec.actual_price
1200 );
1201 fnd_log.string
1202 (
1203 fnd_log.level_statement,
1204 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
1205 ' Before Update Ahl Visit Tasks B Table, Actual Cost: ' || l_cost_price_rec.actual_cost
1206 );
1207 END IF;
1208
1209 -- Update the task record with cost details
1210 UPDATE AHL_VISIT_TASKS_B SET
1211 PRICE_LIST_ID = l_cost_price_rec.price_list_id,
1212 ESTIMATED_PRICE = l_cost_price_rec.estimated_price,
1213 ACTUAL_PRICE = l_cost_price_rec.actual_price,
1214 ACTUAL_COST = l_cost_price_rec.actual_cost,
1215 LAST_UPDATE_DATE = SYSDATE,
1216 LAST_UPDATED_BY = Fnd_Global.USER_ID,
1217 LAST_UPDATE_LOGIN = Fnd_Global.LOGIN_ID,
1218 OBJECT_VERSION_NUMBER = l_cost_price_rec.object_version_number + 1
1219 WHERE VISIT_TASK_ID = l_cost_price_rec.visit_task_id;
1220
1221 -- Check Error Message stack.
1222 x_msg_count := FND_MSG_PUB.count_msg;
1223 IF x_msg_count > 0 THEN
1224 RAISE FND_API.G_EXC_ERROR;
1225 END IF;
1226
1227 -- Standard check of p_commit
1228 IF FND_API.TO_BOOLEAN(p_commit) THEN
1229 COMMIT WORK;
1230 END IF;
1231
1232 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)THEN
1233 fnd_log.string
1234 (
1235 fnd_log.level_procedure,
1236 'ahl.plsql.AHL_VWP_TASK_CST_PR_PVT.Update_Task_Cost_Details.end',
1237 'At the end of PLSQL procedure'
1238 );
1239 END IF;
1240 EXCEPTION
1241 WHEN FND_API.G_EXC_ERROR THEN
1242 x_return_status := FND_API.G_RET_STS_ERROR;
1243 ROLLBACK TO Update_Task_Cost_Details;
1244 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
1245 p_data => x_msg_data,
1246 p_encoded => fnd_api.g_false);
1247
1248
1249 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1250 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1251 ROLLBACK TO Update_Task_Cost_Details;
1252 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
1253 p_data => x_msg_data,
1254 p_encoded => fnd_api.g_false);
1255
1256
1257 WHEN OTHERS THEN
1258 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1259 ROLLBACK TO Update_Task_Cost_Details;
1260 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1261 fnd_msg_pub.add_exc_msg(p_pkg_name => G_PKG_NAME,
1262 p_procedure_name => 'Update_Task_Cost_Details',
1263 p_error_text => SUBSTR(SQLERRM,1,500));
1264 END IF;
1265 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
1266 p_data => x_msg_data,
1267 p_encoded => fnd_api.g_false);
1268
1269 END Update_Task_Cost_Details;
1270
1271
1272 -- Start of Comments --
1273 -- Procedure name : Estimate_Task_Cost
1274 -- Type : Private(Called from Estimate and cost/price Task UI
1275 --
1276 -- Function : To get task estimated cost and actual cost
1277 --
1278 --
1279 -- Pre-reqs :
1280 -- Parameters :
1281 --
1282 -- Standard IN Parameters :
1283 -- p_api_version IN NUMBER Required
1284 -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
1285 -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
1286 -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
1287 --
1288 -- Standard OUT Parameters :
1289 -- x_return_status OUT VARCHAR2 Required
1290 -- x_msg_count OUT NUMBER Required
1291 -- x_msg_data OUT VARCHAR2 Required
1292 --
1293 -- Estimate Task Cost Parameters:
1294 -- p_x_cost_price_rec IN OUT NOCOPY AHL_VWP_VISIT_CST_PR_PVT.Cost_Price_Rec_Type,
1295 -- Contains Cost/Price infor mation relates to Vist and its Task
1296 --
1297 -- Version :
1298 -- Initial Version 1.0
1299 --
1300 -- End of Comments.
1301
1302 PROCEDURE Estimate_Task_Cost (
1303 p_api_version IN NUMBER,
1304 p_init_msg_list IN VARCHAR2 := Fnd_Api.G_FALSE,
1305 p_commit IN VARCHAR2 := Fnd_Api.G_FALSE,
1306 p_validation_level IN NUMBER := Fnd_Api.G_VALID_LEVEL_FULL,
1307 p_module_type IN VARCHAR2 := NULL,
1308 p_x_cost_price_rec IN OUT NOCOPY AHL_VWP_VISIT_CST_PR_PVT.cost_price_rec_type,
1309 x_return_status OUT NOCOPY VARCHAR2,
1310 x_msg_count OUT NOCOPY NUMBER,
1311 x_msg_data OUT NOCOPY VARCHAR2 )
1312 IS
1313
1314 -- To get visit info
1315 CURSOR Get_visit_task_cur (C_VISIT_TASK_ID IN NUMBER)
1316 IS
1317 SELECT vt.visit_id,
1318 vt.visit_task_id,
1319 vs.any_task_chg_flag
1320 FROM ahl_visit_tasks_b vt,
1321 ahl_visits_b vs
1322 WHERE vt.visit_task_id = C_VISIT_TASK_ID
1323 AND vt.visit_id = vs.visit_id
1324 AND NVL(vt.status_code, 'Y') <> NVL ('DELETED', 'X');
1325
1326 l_visit_task_rec Get_visit_task_cur%ROWTYPE;
1327
1328 --
1329 l_api_name CONSTANT VARCHAR2(30) := 'Estimate_Task_Cost';
1330 l_api_version CONSTANT NUMBER := 1.0;
1331 l_msg_data VARCHAR2(2000);
1332 l_return_status VARCHAR2(1);
1333 l_msg_count NUMBER;
1334 l_commit VARCHAR2(10) := Fnd_Api.G_FALSE;
1335 --
1336 l_cost_price_rec AHL_VWP_VISIT_CST_PR_PVT.cost_price_rec_type := p_x_cost_price_rec;
1337 --
1338
1339 l_release_visit_required VARCHAR2(1) :='N';
1340
1341 BEGIN
1342
1343 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)THEN
1344 fnd_log.string
1345 (
1346 fnd_log.level_procedure,
1347 'ahl.plsql.AHL_VWP_TASK_CST_PR_PVT.Estimate_Task_Cost.begin',
1348 'At the start of PLSQL procedure'
1349 );
1350
1351 END IF;
1352
1353 -- Standard start of API savepoint
1354 SAVEPOINT Estimate_Task_Cost;
1355
1356 -- Initialize message list if p_init_msg_list is set to TRUE
1357 IF FND_API.To_Boolean( p_init_msg_list) THEN
1358 FND_MSG_PUB.Initialize;
1359 END IF;
1360 -- Initialize API return status to success
1361 x_return_status := FND_API.G_RET_STS_SUCCESS;
1362 --
1363 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
1364 fnd_log.string
1365 (
1366 fnd_log.level_statement,
1367 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
1368 'Request for Estimating cost for Task ID : ' || l_cost_price_rec.visit_task_id
1369 );
1370
1371 fnd_log.string
1372 (
1373 fnd_log.level_statement,
1374 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
1375 'Got request for task cost details of Cost Session ID : ' || l_cost_price_rec.cost_session_id
1376 );
1377
1378 fnd_log.string
1379 (
1380 fnd_log.level_statement,
1381 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
1382 'Got request for task cost details of MR Session ID : ' || l_cost_price_rec.mr_session_id
1383 );
1384
1385 END IF;
1386 -- Check for Required Parameters
1387 IF(l_cost_price_rec.visit_task_id IS NULL OR
1388 l_cost_price_rec.visit_task_id = FND_API.G_MISS_NUM) THEN
1389 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_VWP_CST_INPUT_MISS');
1390 FND_MSG_PUB.ADD;
1391 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level)THEN
1392 fnd_log.string
1393 (
1394 fnd_log.level_error,
1395 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
1396 'Visit Task id is mandatory but found null in input '
1397 );
1398 END IF;
1399 RAISE FND_API.G_EXC_ERROR;
1400 END IF;
1401
1402 --Required to get visit id
1403 OPEN Get_visit_task_cur(l_cost_price_rec.visit_task_id);
1404 FETCH Get_visit_task_cur INTO l_visit_task_rec;
1405 CLOSE Get_visit_task_cur;
1406
1407
1408 -- Need to release the visit only if this API is called from front-end direcly
1409
1410 IF p_module_type = 'JSP' THEN
1411
1412 AHL_VWP_VISIT_CST_PR_PVT.check_for_release_visit
1413 (
1414 p_visit_id =>l_visit_task_rec.visit_id,
1415 x_release_visit_required =>l_release_visit_required
1416 );
1417
1418 -- Release visit if required
1419 IF l_release_visit_required ='Y' THEN
1420
1421 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
1422 fnd_log.string
1423 (
1424 fnd_log.level_statement,
1425 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
1426 'Before calling ahl vwp proj prod pvt.release visit'
1427 );
1428
1429 END IF;
1430
1431 ahl_vwp_proj_prod_pvt.release_visit (
1432 p_api_version => l_api_version,
1433 p_init_msg_list => p_init_msg_list,
1434 p_commit => l_commit,
1435 p_validation_level => p_validation_level,
1436 p_module_type => 'CST',
1437 p_visit_id => l_visit_task_rec.visit_id,
1438 x_return_status => l_return_status,
1439 x_msg_count => l_msg_count,
1440 x_msg_data => l_msg_data);
1441
1442 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
1443 fnd_log.string
1444 (
1445 fnd_log.level_statement,
1446 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||': End API',
1447 'After calling ahl vwp proj prod pvt.Release Visit task wo Return Status : '|| l_return_status
1448 );
1449 END IF;
1450
1451 -- Check Error Message stack.
1452 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1453 l_msg_count := FND_MSG_PUB.count_msg;
1454 IF l_msg_count > 0 THEN
1455 RAISE FND_API.G_EXC_ERROR;
1456 END IF;
1457 END IF;
1458
1459 END IF; -- released required flag
1460
1461 END IF; --- p_module type = 'JSP'
1462
1463
1464 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
1465 fnd_log.string
1466 (
1467 fnd_log.level_statement,
1468 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
1469 'Before calling ahl vwp cost pvt.calculate wo cost '
1470 );
1471 END IF;
1472
1473 -- Call AHL_VWP_COST_PVT.estimate_wo_cost
1474 ahl_vwp_cost_pvt.estimate_wo_cost
1475 ( p_x_cost_price_rec => l_cost_price_rec,
1476 x_return_status => l_return_status);
1477
1478 -- Check return status.
1479 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1480 RAISE FND_API.G_EXC_ERROR;
1481 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1482 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1483 END IF;
1484
1485 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
1486 fnd_log.string
1487 (
1488 fnd_log.level_procedure,
1489 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||': End API',
1490 'After calling ahl vwp cost pvt.Calculate Wo Cost Return Status : '|| l_return_status
1491 );
1492 END IF;
1493
1494
1495 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
1496 THEN
1497 fnd_log.string
1498 (
1499 fnd_log.level_statement,
1500 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
1501 'Is Cst Struc updated Flag: ' || l_cost_price_rec.Is_Cst_Struc_updated
1502 );
1503 fnd_log.string
1504 (
1505 fnd_log.level_statement,
1506 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
1507 'Cost Session Id: ' || l_cost_price_rec.cost_session_id
1508 );
1509 fnd_log.string
1510 (
1511 fnd_log.level_statement,
1512 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
1513 'Mr Session Id: ' || l_cost_price_rec.mr_session_id
1514 );
1515
1516 END IF;
1517 --Assign the out variable
1518 p_x_cost_price_rec.cost_session_id := l_cost_price_rec.cost_session_id;
1519 p_x_cost_price_rec.mr_session_id := l_cost_price_rec.mr_session_id;
1520 p_x_cost_price_rec.Is_Cst_Struc_updated := l_cost_price_rec.Is_Cst_Struc_updated;
1521
1522 -- Standard check of p_commit
1523 IF FND_API.TO_BOOLEAN(p_commit) THEN
1524 COMMIT WORK;
1525 END IF;
1526
1527 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)THEN
1528 fnd_log.string
1529 (
1530 fnd_log.level_procedure,
1531 'ahl.plsql.AHL_VWP_TASK_CST_PR_PVT.Estimate_Task_Cost.end',
1532 'At the end of PLSQL procedure'
1533 );
1534 END IF;
1535 EXCEPTION
1536 WHEN FND_API.G_EXC_ERROR THEN
1537 x_return_status := FND_API.G_RET_STS_ERROR;
1538 ROLLBACK TO Estimate_Task_Cost;
1539 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
1540 p_data => x_msg_data,
1541 p_encoded => fnd_api.g_false);
1542
1543
1544 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1545 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1546 ROLLBACK TO Estimate_Task_Cost;
1547 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
1548 p_data => x_msg_data,
1549 p_encoded => fnd_api.g_false);
1550
1551
1552 WHEN OTHERS THEN
1553 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1554 ROLLBACK TO Estimate_Task_Cost;
1555 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1556 fnd_msg_pub.add_exc_msg(p_pkg_name => G_PKG_NAME,
1557 p_procedure_name => 'Estimate_Task_Cost',
1558 p_error_text => SUBSTR(SQLERRM,1,500));
1559 END IF;
1560 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
1561 p_data => x_msg_data,
1562 p_encoded => fnd_api.g_false);
1563
1564 END Estimate_Task_Cost;
1565
1566 -- Start of Comments --
1567 -- Procedure name : Estimate_Task_Price
1568 -- Type : Private(Called from Estimate and cost/price Task UI
1569 --
1570 -- Function :To get Task Estimated Price and Actual Price
1571 --
1572 --
1573 -- Pre-reqs :
1574 -- Parameters :
1575 --
1576 -- Standard IN Parameters :
1577 -- p_api_version IN NUMBER Required
1578 -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
1579 -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
1580 -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
1581 --
1582 -- Standard OUT Parameters :
1583 -- x_return_status OUT VARCHAR2 Required
1584 -- x_msg_count OUT NUMBER Required
1585 -- x_msg_data OUT VARCHAR2 Required
1586 --
1587 -- Estimate Task Price Parameters:
1588 -- p_x_cost_price_rec IN OUT NOCOPY AHL_VWP_VISIT_CST_PR_PVT.Cost_Price_Rec_Type,
1589 -- Contains Cost/Price infor mation relates to Vist and its Task
1590 --
1591 -- Version :
1592 -- Initial Version 1.0
1593 --
1594 -- End of Comments.
1595
1596 PROCEDURE Estimate_Task_Price (
1597 p_api_version IN NUMBER,
1598 p_init_msg_list IN VARCHAR2 := Fnd_Api.G_FALSE,
1599 p_commit IN VARCHAR2 := Fnd_Api.G_FALSE,
1600 p_validation_level IN NUMBER := Fnd_Api.G_VALID_LEVEL_FULL,
1601 p_module_type IN VARCHAR2 := NULL,
1602 p_x_cost_price_rec IN OUT NOCOPY AHL_VWP_VISIT_CST_PR_PVT.cost_price_rec_type,
1603 x_return_status OUT NOCOPY VARCHAR2,
1604 x_msg_count OUT NOCOPY NUMBER,
1605 x_msg_data OUT NOCOPY VARCHAR2
1606 )
1607 IS
1608
1609 -- Cursor to get visit and task cost details
1610 CURSOR Get_visit_task_dtls_cur (C_VISIT_TASK_ID IN NUMBER)
1611 IS
1612 SELECT vs.visit_id,
1613 vs.visit_number,
1614 vs.actual_price visit_actual_price,
1615 vs.estimated_price visit_estimated_price,
1616 vs.object_version_number visit_object_version_number,
1617 vs.organization_id,
1618 vs.any_task_chg_flag,
1619 nvl(vt.price_list_id, vs.price_list_id) price_list_id,
1620 vt.visit_task_id,
1621 vt.visit_task_number,
1622 vt.object_version_number task_object_version_number,
1623 vt.actual_price task_actual_price,
1624 vt.estimated_price task_estimated_price,
1625 vt.mr_id,
1626 vt.task_type_code,
1627 mr_route_id,
1628 vt.originating_task_id,
1629 vt.service_request_id,
1630 cs.customer_id,
1631 vt.start_date_time, --Post11510 cxcheng added
1632 vt.end_date_time
1633 FROM ahl_visits_vl vs,
1634 ahl_visit_tasks_vl vt,
1635 cs_incidents_all_b cs
1636 WHERE vs.visit_id = vt.visit_id
1637 AND vs.service_request_id = cs.incident_id(+)
1638 AND vt.visit_task_id = C_VISIT_TASK_ID
1639 AND NVL(vt.status_code, 'Y') <> NVL ('DELETED', 'X');
1640
1641 l_visit_task_dtls_rec Get_visit_task_dtls_cur%ROWTYPE;
1642
1643 -- Cursor to get parent mr
1644 CURSOR Get_parent_task_cur (C_ORIG_TASK_ID IN NUMBER,
1645 C_VISIT_ID IN NUMBER)
1646 IS
1647 SELECT distinct(vt.visit_task_id) visit_task_id,
1648 vt.object_version_number,
1649 vt.visit_task_number,
1650 vt.mr_id,
1651 vt.mr_route_id,
1652 vt.actual_price,
1653 vt.estimated_price
1654 FROM ahl_visit_tasks_b vt
1655 WHERE visit_id = C_VISIT_ID
1656 AND NVL(vt.status_code, 'Y') <> NVL ('DELETED', 'X')
1657 START WITH visit_task_id = C_ORIG_TASK_ID
1658 CONNECT BY PRIOR originating_task_id = visit_task_id;
1659
1660 l_parent_task_rec Get_parent_task_cur%ROWTYPE;
1661
1662 --Standard local variables
1663 l_api_name CONSTANT VARCHAR2(30) := 'Estimate_Task_Price';
1664 L_FULL_NAME CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || L_API_NAME;
1665 l_api_version CONSTANT NUMBER := 1.0;
1666 l_msg_data VARCHAR2(2000);
1667 l_return_status VARCHAR2(1);
1668 l_msg_count NUMBER;
1669 l_dummy NUMBER;
1670
1671 --
1672 l_cost_price_rec AHL_VWP_VISIT_CST_PR_PVT.cost_price_rec_type :=p_x_cost_price_rec;
1673
1674 l_route_id NUMBER;
1675 l_estimated_price NUMBER :=0;
1676 l_actual_price NUMBER :=0;
1677 l_act_price_dif NUMBER;
1678 l_estimate_price_dif NUMBER;
1679
1680 l_job_status_code VARCHAR2(30);
1681 l_job_status_mean VARCHAR2(80);
1682
1683 -- Varibles for start date time
1684 l_default VARCHAR2(30);
1685 i NUMBER;
1686
1687 --Validate visit variables
1688
1689 l_commit VARCHAR2(10) := Fnd_Api.G_FALSE;
1690
1691 BEGIN
1692 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)THEN
1693 fnd_log.string
1694 (
1695 fnd_log.level_procedure,
1696 'ahl.plsql.AHL_VWP_TASK_CST_PR_PVT.Estimate_Task_Price.begin',
1697 'At the start of PLSQL procedure'
1698 );
1699
1700 END IF;
1701 -- Standard start of API savepoint
1702 SAVEPOINT Estimate_Task_Price;
1703 -- Initialize message list if p_init_msg_list is set to TRUE
1704 IF FND_API.To_Boolean( p_init_msg_list) THEN
1705 FND_MSG_PUB.Initialize;
1706 END IF;
1707 -- Initialize API return status to success
1708 x_return_status := FND_API.G_RET_STS_SUCCESS;
1709 --
1710 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
1711 fnd_log.string
1712 (
1713 fnd_log.level_statement,
1714 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
1715 'Request for Estimating Price for Task ID : ' || l_cost_price_rec.visit_task_id
1716 );
1717
1718 END IF;
1719
1720 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
1721 fnd_log.string
1722 (
1723 fnd_log.level_statement,
1724 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
1725 'Request for Estimating Task Price for Currency code : ' || l_cost_price_rec.currency
1726 );
1727
1728 END IF;
1729
1730 -- Check for Required Parameters
1731 IF(l_cost_price_rec.visit_task_id IS NULL OR
1732 l_cost_price_rec.visit_task_id = FND_API.G_MISS_NUM) THEN
1733 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_VWP_CST_INPUT_MISS');
1734 FND_MSG_PUB.ADD;
1735 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level)THEN
1736 fnd_log.string
1737 (
1738 fnd_log.level_error,
1739 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
1740 'Visit Task id is mandatory but found null in input '
1741 );
1742 END IF;
1743 RAISE FND_API.G_EXC_ERROR;
1744 END IF;
1745
1746 -- Retrieve visit info
1747 OPEN Get_visit_task_dtls_cur(l_cost_price_rec.visit_task_id) ;
1748 FETCH Get_visit_task_dtls_cur INTO l_visit_task_dtls_rec;
1749 IF Get_visit_task_dtls_cur%NOTFOUND THEN
1750 FND_MESSAGE.set_name( 'AHL','AHL_VWP_VISIT_TASK_INVALID' );
1751 FND_MSG_PUB.add;
1752 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level)THEN
1753 fnd_log.string
1754 (
1755 fnd_log.level_error,
1756 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
1757 'Visit Task not found in ahl_visit_tasks_b table'
1758 );
1759 END IF;
1760 CLOSE Get_visit_task_dtls_cur;
1761 RAISE FND_API.G_EXC_ERROR;
1762 END IF;
1763 CLOSE Get_visit_task_dtls_cur;
1764
1765 --Assign derieved values
1766 l_cost_price_rec.price_list_id := l_visit_task_dtls_rec.price_list_id;
1767 l_cost_price_rec.visit_id := l_visit_task_dtls_rec.visit_id;
1768 l_cost_price_rec.mr_id := l_visit_task_dtls_rec.mr_id;
1769 l_cost_price_rec.customer_id := l_visit_task_dtls_rec.customer_id;
1770 l_cost_price_rec.organization_id := l_visit_task_dtls_rec.organization_id;
1771
1772 --
1773 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
1774 fnd_log.string
1775 (
1776 fnd_log.level_statement,
1777 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
1778 'Price List Id : ' || l_cost_price_rec.price_list_id
1779 );
1780
1781 fnd_log.string
1782 (
1783 fnd_log.level_statement,
1784 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
1785 'Customer Id : ' || l_cost_price_rec.customer_id
1786 );
1787
1788 fnd_log.string
1789 (
1790 fnd_log.level_statement,
1791 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
1792 'Visit Id : ' || l_cost_price_rec.visit_id
1793 );
1794
1795 END IF;
1796
1797 IF l_cost_price_rec.currency IS NULL THEN
1798 -- Get Currency
1799 ahl_vwp_rules_pvt.check_currency_for_costing
1800 (
1801 p_visit_id => l_cost_price_rec.visit_id,
1802 x_currency_code => l_cost_price_rec.currency);
1803 -- error handling
1804 IF l_cost_price_rec.currency IS NULL THEN
1805 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_VWP_CST_NO_CURRENCY');
1806 FND_MSG_PUB.ADD;
1807 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level)THEN
1808 fnd_log.string
1809 (
1810 fnd_log.level_error,
1811 'ahl.plsql.'||G_PKG_NAME||'.'||L_API_NAME,
1812 'No curency is defined for the organization of the visit'
1813 );
1814 END IF;
1815 RAISE FND_API.G_EXC_ERROR;
1816 END IF; -- error handling
1817 END IF; --If currency is null
1818
1819 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
1820 fnd_log.string
1821 (
1822 fnd_log.level_statement,
1823 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
1824 'Currency : ' || l_cost_price_rec.currency
1825 );
1826
1827 END IF;
1828
1829 -- Validation for Price list, Task has price list associated consider it else use
1830 -- visit price list. If both not exists then raise an error message
1831 IF (l_cost_price_rec.price_list_id IS NULL OR
1832 l_cost_price_rec.price_list_id = FND_API.G_MISS_NUM ) THEN
1833 FND_MESSAGE.set_name( 'AHL','AHL_VWP_PRICE_LIST_INVALID' );
1834 FND_MSG_PUB.add;
1835 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level)THEN
1836 fnd_log.string
1837 (
1838 fnd_log.level_error,
1839 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
1840 'Price list not found for either Task Or Visit'
1841 );
1842 END IF;
1843 RAISE FND_API.G_EXC_ERROR;
1844 END IF;
1845
1846 IF p_module_type = 'JSP' THEN
1847
1848 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
1849 fnd_log.string
1850 (
1851 fnd_log.level_procedure,
1852 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
1853 'Before calling ahl vwp task cost.Estimate Task Cost '
1854 );
1855
1856 END IF;
1857
1858 --Call estimate task cost
1859 Estimate_Task_Cost (
1860 p_api_version => l_api_version,
1861 p_init_msg_list => p_init_msg_list,
1862 p_commit => l_commit,
1863 p_validation_level => p_validation_level,
1864 p_module_type => p_module_type,
1865 p_x_cost_price_rec => l_cost_price_rec,
1866 x_return_status => l_return_status,
1867 x_msg_count => l_msg_count,
1868 x_msg_data => l_msg_data);
1869
1870 -- Check return status.
1871 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1872 RAISE FND_API.G_EXC_ERROR;
1873 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1874 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1875 END IF;
1876
1877 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
1878 fnd_log.string
1879 (
1880 fnd_log.level_procedure,
1881 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||': End API',
1882 'After calling ahl vwp task cost pvt.Estimate Task cost Return Status : '|| l_return_status
1883 );
1884 END IF;
1885
1886
1887
1888 --Assign the out variable
1889 p_x_cost_price_rec.cost_session_id := l_cost_price_rec.cost_session_id;
1890 p_x_cost_price_rec.mr_session_id := l_cost_price_rec.mr_session_id;
1891 p_x_cost_price_rec.Is_Cst_Struc_updated := l_cost_price_rec.Is_Cst_Struc_updated;
1892
1893
1894 END IF; --Module type JSP
1895
1896 -- If the task has mrs associated and task type is summary get estimated price for the MR
1897
1898 IF (l_cost_price_rec.mr_id IS NOT NULL AND l_visit_task_dtls_rec.task_type_code = 'SUMMARY')
1899 THEN
1900 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
1901 fnd_log.string
1902 (
1903 fnd_log.level_procedure,
1904 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
1905 'Before calling ahl vwp mr cst pr pvt.Estimate Mr Price'
1906 );
1907
1908 END IF;
1909
1910 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
1911 fnd_log.string
1912 (
1913 fnd_log.level_statement,
1914 'ahl.plsql.AHL_VWP_TASK_CST_PR_PVT.Estimate_Task_Price',
1915 'Mr Id : ' || l_cost_price_rec.mr_id
1916 );
1917
1918 END IF;
1919
1920 --Call ahl_vwp_mr_cst_pr_pvt.estimate_mr_price
1921 ahl_vwp_mr_cst_pr_pvt.Estimate_MR_Price (
1922 p_api_version => l_api_version,
1923 p_init_msg_list => p_init_msg_list,
1924 p_commit => l_commit,
1925 p_validation_level => p_validation_level,
1926 p_x_cost_price_rec => l_cost_price_rec,
1927 p_module_type => p_module_type,
1928 x_return_status => l_return_status,
1929 x_msg_count => l_msg_count,
1930 x_msg_data => l_msg_data);
1931
1932 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
1933 fnd_log.string
1934 (
1935 fnd_log.level_procedure,
1936 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||': End API',
1937 'After calling ahl vwp cst pr pvt.Estimate Mr Price Return Status : '|| l_return_status
1938 );
1939 END IF;
1940
1941 -- Check return status.
1942 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1943 RAISE FND_API.G_EXC_ERROR;
1944 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1945 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1946 END IF;
1947
1948 --Assign returned values
1949 l_estimated_price := l_cost_price_rec.estimated_price;
1950 l_actual_price := l_cost_price_rec.actual_price;
1951
1952 --Log messages
1953 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
1954 fnd_log.string
1955 (
1956 fnd_log.level_statement,
1957 'ahl.plsql.AHL_VWP_TASK_CST_PR_PVT.Estimate_Task_Price',
1958 'Derieved value from Estimate Mr price for Summary task, Estimated Price : ' || l_estimated_price
1959 );
1960
1961 fnd_log.string
1962 (
1963 fnd_log.level_statement,
1964 'ahl.plsql.AHL_VWP_TASK_CST_PR_PVT.Estimate_Task_Price',
1965 'Derieved value from Estimate Mr price for Summary task, Actual Price : ' || l_actual_price
1966 );
1967
1968 END IF;
1969
1970 Else --- other type of tasks (planned/unplanned/unassociated)
1971
1972
1973
1974 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
1975 fnd_log.string
1976 (
1977 fnd_log.level_procedure,
1978 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
1979 'Before calling ahl vwp rules pvt.Check Job Status'
1980 );
1981
1982 END IF;
1983
1984
1985 --Check job status
1986 ahl_vwp_rules_pvt.Check_Job_Status
1987 (p_id => l_cost_price_rec.visit_task_id,
1988 p_is_task_flag => 'Y',
1989 x_status_code => l_job_status_code,
1990 x_status_meaning => l_job_status_mean);
1991
1992 --Log Messages
1993 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
1994 fnd_log.string
1995 (
1996 fnd_log.level_procedure,
1997 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||': End API',
1998 'After calling ahl vwp rules pvt.Check Job Status, Return Status : '|| l_return_status
1999 );
2000 END IF;
2001
2002 -- error hanling
2003 IF (l_job_status_code is NULL) THEN
2004 l_msg_count := FND_MSG_PUB.count_msg;
2005 IF l_msg_count > 0 THEN
2006 RAISE FND_API.G_EXC_ERROR;
2007 END IF;
2008 END IF;
2009
2010 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
2011 fnd_log.string
2012 (
2013 fnd_log.level_statement,
2014 'ahl.plsql.AHL_VWP_TASK_CST_PR_PVT.Estimate_Task_Price',
2015 ' Job Status Code: ' || l_job_status_code
2016 );
2017 fnd_log.string
2018 (
2019 fnd_log.level_statement,
2020 'ahl.plsql.AHL_VWP_TASK_CST_PR_PVT.Estimate_Task_Price',
2021 ' Job Status Mean: ' || l_job_status_mean
2022 );
2023 fnd_log.string
2024 (
2025 fnd_log.level_statement,
2026 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
2027 'task start date :' ||l_cost_price_rec.task_start_date
2028 );
2029 END IF;
2030
2031 -- If job is in draft status
2032
2033 IF l_job_status_code = 17
2034 THEN
2035
2036 -- For Unplanned/Planned tasks
2037 -- Derive estimated price
2038 -- for unassociated task estimated price is zero
2039
2040 IF (l_cost_price_rec.mr_id IS NOT NULL AND l_visit_task_dtls_rec.mr_route_id IS NOT NULL )THEN
2041
2042 -- Retrieve route id
2043 SELECT ROUTE_ID INTO l_route_id
2044 FROM ahl_mr_routes_v
2045 WHERE mr_route_id = l_visit_task_dtls_rec.mr_route_id;
2046
2047 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
2048 fnd_log.string
2049 (
2050 fnd_log.level_statement,
2051 'ahl.plsql.AHL_VWP_TASK_CST_PR_PVT.Estimate_Task_Price',
2052 ' For task with Route Id: ' || l_route_id
2053 );
2054 END IF;
2055
2056 -- derive task start time if job is in draft status
2057 -- and API is called from front-end directly
2058
2059
2060 IF p_module_type='JSP'
2061 THEN
2062
2063 --Use the task start date and end date.
2064 l_cost_price_rec.task_start_date :=l_visit_task_dtls_rec.start_date_time;
2065 l_cost_price_rec.task_end_date := l_visit_task_dtls_rec.end_date_time;
2066
2067 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
2068 fnd_log.string
2069 (
2070 fnd_log.level_statement,
2071 'ahl.plsql.AHL_VWP_TASK_CST_PR_PVT.Estimate_Task_Price',
2072 ' Derieved task start date: ' || l_cost_price_rec.task_start_date
2073 );
2074
2075 END IF;
2076
2077 End IF; -- p_module is JSP
2078
2079 --When Job Status is DRAFT, estimated price for tasks w/Route will be derived
2080 -- based on route material requirements and resource requirements
2081
2082 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
2083 fnd_log.string
2084 (
2085 fnd_log.level_procedure,
2086 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
2087 'Before calling ahl vwp price pvt.Get Task Estimated Price'
2088 );
2089
2090 END IF;
2091
2092 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
2093 fnd_log.string
2094 (
2095 fnd_log.level_statement,
2096 'ahl.plsql.AHL_VWP_TASK_CST_PR_PVT.Estimate_Task_Price',
2097 ' Before Calling Task Estimated Price, Route Id: ' || l_route_id
2098 );
2099 fnd_log.string
2100 (
2101 fnd_log.level_statement,
2102 'ahl.plsql.AHL_VWP_TASK_CST_PR_PVT.Estimate_Task_Price',
2103 ' Before Calling Task Estimated Price, Price List Id: ' || l_cost_price_rec.price_list_id
2104 );
2105
2106 fnd_log.string
2107 (
2108 fnd_log.level_statement,
2109 'ahl.plsql.AHL_VWP_TASK_CST_PR_PVT.Estimate_Task_Price',
2110 ' Before Calling Task Estimated Price, Customer Id: ' || l_cost_price_rec.customer_id
2111 );
2112 fnd_log.string
2113 (
2114 fnd_log.level_statement,
2115 'ahl.plsql.AHL_VWP_TASK_CST_PR_PVT.Estimate_Task_Price',
2116 ' Before Calling Task Estimated Price, Currency: ' || l_cost_price_rec.currency
2117 );
2118 fnd_log.string
2119 (
2120 fnd_log.level_statement,
2121 'ahl.plsql.AHL_VWP_TASK_CST_PR_PVT.Estimate_Task_Price',
2122 ' Before Calling Task Estimated Price, Task Start Date: ' || l_cost_price_rec.task_start_date
2123 );
2124 fnd_log.string
2125 (
2126 fnd_log.level_statement,
2127 'ahl.plsql.AHL_VWP_TASK_CST_PR_PVT.Estimate_Task_Price',
2128 ' Before Calling Task Estimated Price, Organization Id: ' || l_cost_price_rec.organization_id
2129 );
2130
2131 END IF;
2132
2133 -- Task Date can not be null
2134 IF (l_cost_price_rec.task_start_date IS NULL OR
2135 l_cost_price_rec.task_start_date = FND_API.G_MISS_DATE ) THEN
2136
2137 FND_MESSAGE.set_name( 'AHL','AHL_VWP_VALIDATE_ERROR' );
2138 FND_MSG_PUB.add;
2139
2140 IF G_DEBUG='Y' THEN
2141 Ahl_Debug_Pub.debug( l_full_name ||'Task Start Date is null');
2142 END IF;
2143
2144 RAISE FND_API.G_EXC_ERROR;
2145 END IF;
2146
2147 ahl_vwp_price_pvt.Get_Task_Estimated_Price (
2148 p_visit_task_id => l_cost_price_rec.visit_task_id,
2149 p_route_id => l_route_id,
2150 p_price_list_id => l_cost_price_rec.price_list_id,
2151 p_customer_id => l_cost_price_rec.customer_id,
2152 p_currency_code => l_cost_price_rec.currency,
2153 p_effective_date => l_cost_price_rec.task_start_date,
2154 p_organization_id => l_cost_price_rec.organization_id,
2155 x_estimated_price => l_estimated_price,
2156 x_return_status => l_return_status);
2157
2158 --Log messages
2159 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
2160 fnd_log.string
2161 (
2162 fnd_log.level_procedure,
2163 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||': End API',
2164 'After calling ahl vwp price pvt.Get Task Estimated Price, Return Status : '|| l_return_status
2165 );
2166 END IF;
2167
2168 -- Check return status.
2169 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
2170 RAISE FND_API.G_EXC_ERROR;
2171 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2172 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2173 END IF;
2174
2175 --Assign derived value
2176 l_cost_price_rec.estimated_price := l_estimated_price;
2177
2178 ELSE -- Unassociated Task
2179
2180 l_cost_price_rec.estimated_price := 0;
2181
2182 END IF; --Planned/Unplanned Tasks
2183
2184 l_cost_price_rec.actual_price := 0;
2185
2186 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
2187 fnd_log.string
2188 (
2189 fnd_log.level_statement,
2190 'ahl.plsql.AHL_VWP_TASK_CST_PR_PVT.Estimate_Task_Price',
2191 ' Derieved Value for Estimated Price: ' || l_cost_price_rec.estimated_price
2192 );
2193 fnd_log.string
2194 (
2195 fnd_log.level_statement,
2196 'ahl.plsql.AHL_VWP_TASK_CST_PR_PVT.Estimate_Task_Price',
2197 ' Derieved Value for Actual Price: ' || l_cost_price_rec.actual_price
2198 );
2199 END IF;
2200
2201 ELSE
2202 --If job status other than 'DRAFT''
2203
2204 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
2205 fnd_log.string
2206 (
2207 fnd_log.level_procedure,
2208 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
2209 'Before calling ahl vwp price pvt.Get Job Estimated Price'
2210 );
2211
2212 END IF;
2213
2214 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
2215 fnd_log.string
2216 (
2217 fnd_log.level_statement,
2218 'ahl.plsql.AHL_VWP_TASK_CST_PR_PVT.Estimate_Task_Price',
2219 ' Before Calling Job Estimated Price, Price List Id: ' || l_cost_price_rec.price_list_id
2220 );
2221 fnd_log.string
2222 (
2223 fnd_log.level_statement,
2224 'ahl.plsql.AHL_VWP_TASK_CST_PR_PVT.Estimate_Task_Price',
2225 ' Before Calling Job Estimated Price, Customer Id: ' || l_cost_price_rec.customer_id
2226 );
2227 fnd_log.string
2228 (
2229 fnd_log.level_statement,
2230 'ahl.plsql.AHL_VWP_TASK_CST_PR_PVT.Estimate_Task_Price',
2231 ' Before Calling Job Estimated Price, Currency: ' || l_cost_price_rec.currency
2232 );
2233
2234 END IF;
2235
2236 ahl_vwp_price_pvt.Get_Job_Estimated_Price (
2237 p_visit_task_id => l_cost_price_rec.visit_task_id,
2238 p_price_list_id => l_cost_price_rec.price_list_id,
2239 p_customer_id => l_cost_price_rec.customer_id,
2240 p_currency_code => l_cost_price_rec.currency,
2241 x_estimated_price => l_estimated_price,
2242 x_return_status => l_return_status);
2243 --Log messages
2244 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
2245 fnd_log.string
2246 (
2247 fnd_log.level_procedure,
2248 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||': End API',
2249 'After calling ahl vwp price pvt.Get Job Estimated Price, Return Status : '|| l_return_status
2250 );
2251 END IF;
2252
2253 -- Check return status.
2254 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
2255 RAISE FND_API.G_EXC_ERROR;
2256 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2257 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2258 END IF;
2259
2260 -- Assign
2261 l_cost_price_rec.estimated_price := l_estimated_price;
2262
2263 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
2264 fnd_log.string
2265 (
2266 fnd_log.level_statement,
2267 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
2268 ' Derieved value from Job Estimated Price, Estimated Price: ' || l_estimated_price
2269 );
2270 END IF;
2271
2272 -- Call ahl_vwp_price_pvt.get_task_actual_price
2273 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
2274 fnd_log.string
2275 (
2276 fnd_log.level_procedure,
2277 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
2278 'Before calling ahl vwp price pvt.Get Job Actual Price'
2279 );
2280
2281 END IF;
2282
2283 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
2284 fnd_log.string
2285 (
2286 fnd_log.level_statement,
2287 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
2288 ' Before Calling Job Actual Price, Price List Id: ' || l_cost_price_rec.price_list_id
2289 );
2290 fnd_log.string
2291 (
2292 fnd_log.level_statement,
2293 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
2294 ' Before Calling Job Actual Price, Customer Id: ' || l_cost_price_rec.customer_id
2295 );
2296 fnd_log.string
2297 (
2298 fnd_log.level_statement,
2299 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
2300 ' Before Calling Job Actual Price, Currency: ' || l_cost_price_rec.currency
2301 );
2302
2303 END IF;
2304
2305 ahl_vwp_price_pvt.Get_job_Actual_Price (
2306 p_visit_task_id => l_cost_price_rec.visit_task_id,
2307 p_price_list_id => l_cost_price_rec.price_list_id,
2308 p_customer_id => l_cost_price_rec.customer_id,
2309 p_currency_code => l_cost_price_rec.currency,
2310 x_actual_price => l_actual_price,
2311 x_return_status => l_return_status);
2312
2313 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
2314 fnd_log.string
2315 (
2316 fnd_log.level_procedure,
2317 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||': End API',
2318 'After calling ahl vwp rules pvt.Get Job Actual Price, Return Status : '|| l_return_status
2319 );
2320 END IF;
2321
2322 -- Check return status.
2323 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
2324 RAISE FND_API.G_EXC_ERROR;
2325 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2326 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2327 END IF;
2328
2329 --Assign derived value
2330 l_cost_price_rec.actual_price := l_actual_price;
2331
2332 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
2333 fnd_log.string
2334 (
2335 fnd_log.level_statement,
2336 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
2337 ' Derieved value from Job Actual Price, Actual Price: ' || l_cost_price_rec.actual_price
2338 );
2339
2340 END IF;
2341
2342 END IF; -- Draft status
2343
2344 END IF; -- MR Summary Task
2345
2346 -- If calling from front-end directly, and the new estimated price or actual price
2347 -- is different from the previous one, then need to adjust the value stored at Visit level
2348 -- And all the parent MR level
2349
2350 IF p_module_type = 'JSP' THEN
2351
2352 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
2353 fnd_log.string
2354 (
2355 fnd_log.level_statement,
2356 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
2357 ' If p_module_type = JSP : ' || p_module_type
2358 );
2359
2360 END IF;
2361
2362 IF (l_cost_price_rec.actual_price <> nvl(l_visit_task_dtls_rec.task_actual_price,0) OR
2363 l_cost_price_rec.estimated_price <> nvl(l_visit_task_dtls_rec.task_estimated_price,0) ) THEN
2364
2365 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
2366 fnd_log.string
2367 (
2368 fnd_log.level_statement,
2369 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
2370 ' After l_Actual Price <> l_visit_task_dtls_rec.task_actual_price: ' || l_visit_task_dtls_rec.task_actual_price
2371 );
2372 fnd_log.string
2373 (
2374 fnd_log.level_statement,
2375 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
2376 ' After l_Estimated Price <> l_visit_task_dtls_rec.task_estimated_price: ' || l_visit_task_dtls_rec.task_estimated_price
2377 );
2378
2379 END IF;
2380
2381 -- for Unassociated tasks or top level MR tasks, only adjust visit level
2382
2383 IF (l_visit_task_dtls_rec.task_type_code <> 'UNASSOCIATED'
2384 AND l_visit_task_dtls_rec.originating_task_id IS NOT NULL) THEN
2385
2386 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
2387 fnd_log.string
2388 (
2389 fnd_log.level_statement,
2390 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
2391 ' If task_type_code <> UNASSOCIATED and task is not at the top level: ' || l_visit_task_dtls_rec.task_type_code
2392 );
2393
2394 END IF;
2395 -- Check for all parent MRs exists for originating task
2396 OPEN Get_parent_task_cur(l_visit_task_dtls_rec.originating_task_id,
2397 l_visit_task_dtls_rec.visit_id);
2398 LOOP
2399 FETCH Get_parent_task_cur INTO l_parent_task_rec;
2400 EXIT WHEN Get_parent_task_cur%NOTFOUND;
2401 --
2402 IF l_parent_task_rec.mr_id IS NOT NULL THEN
2403
2404 IF (l_parent_task_rec.estimated_price IS NOT NULL OR
2405 l_parent_task_rec.actual_price IS NOT NULL ) THEN
2406 -- Actual Price
2407 IF nvl(l_visit_task_dtls_rec.task_actual_price,0) > l_cost_price_rec.actual_price THEN
2408 l_act_price_dif := (nvl(l_visit_task_dtls_rec.task_actual_price,0) - l_cost_price_rec.actual_price);
2409 l_parent_task_rec.actual_price := (nvl(l_parent_task_rec.actual_price,0) - l_act_price_dif);
2410 ELSE
2411 l_act_price_dif := (l_cost_price_rec.actual_price - nvl(l_visit_task_dtls_rec.task_actual_price,0));
2412 l_parent_task_rec.actual_price := (nvl(l_parent_task_rec.actual_price,0) + l_act_price_dif);
2413 END IF;
2414 -- Estimated price
2415 IF nvl(l_visit_task_dtls_rec.task_estimated_price,0) > l_cost_price_rec.estimated_price THEN
2416 l_estimate_price_dif := (nvl(l_visit_task_dtls_rec.task_estimated_price,0) - l_cost_price_rec.estimated_price);
2417 l_parent_task_rec.estimated_price := (nvl(l_parent_task_rec.estimated_price,0) - l_estimate_price_dif);
2418 ELSE
2419 l_estimate_price_dif := (l_cost_price_rec.estimated_price - nvl(l_visit_task_dtls_rec.task_estimated_price,0));
2420 l_parent_task_rec.estimated_price := (nvl(l_parent_task_rec.estimated_price,0) + l_estimate_price_dif);
2421 END IF;
2422
2423 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
2424 fnd_log.string
2425 (
2426 fnd_log.level_statement,
2427 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
2428 ' Before updating parent task ahl_visit_tasks_b, Actual Price: ' || l_parent_task_rec.actual_price
2429 );
2430 fnd_log.string
2431 (
2432 fnd_log.level_statement,
2433 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
2434 ' Before updating parent task ahl_visit_tasks_b, Estimated Price: ' || l_parent_task_rec.estimated_price
2435 );
2436
2437 END IF;
2438
2439 -- Update visit tasks table with new actual and estimate prices
2440 UPDATE ahl_visit_tasks_b
2441 SET actual_price = l_parent_task_rec.actual_price,
2442 estimated_price = l_parent_task_rec.estimated_price,
2443 object_version_number = l_parent_task_rec.object_version_number + 1
2444 WHERE visit_task_id = l_parent_task_rec.visit_task_id;
2445 ELSE
2446 -- If null adjust with derived values
2447 UPDATE ahl_visit_tasks_b
2448 SET actual_price = l_cost_price_rec.actual_price,
2449 estimated_price = l_cost_price_rec.estimated_price,
2450 object_version_number = l_parent_task_rec.object_version_number + 1
2451 WHERE visit_task_id = l_parent_task_rec.visit_task_id;
2452
2453 END IF; --Mr id not null
2454 END IF; --Parent estimate price or Parent actual price not null
2455 END LOOP;
2456 CLOSE Get_parent_task_cur;
2457 END IF; --Unassociated
2458
2459 -- Visit should be updated with latest values
2460 -- Actual Price
2461 IF nvl(l_visit_task_dtls_rec.task_actual_price,0) > l_cost_price_rec.actual_price THEN
2462 l_act_price_dif := (nvl(l_visit_task_dtls_rec.task_actual_price,0) - l_cost_price_rec.actual_price);
2463 l_visit_task_dtls_rec.visit_actual_price := (nvl(l_visit_task_dtls_rec.visit_actual_price,0) - l_act_price_dif);
2464 ELSE
2465 l_act_price_dif := (l_cost_price_rec.actual_price - nvl(l_visit_task_dtls_rec.task_actual_price,0));
2466 l_visit_task_dtls_rec.visit_actual_price := (nvl(l_visit_task_dtls_rec.visit_actual_price,0) + l_act_price_dif);
2467 END IF;
2468 -- Estimated Price
2469 IF nvl(l_visit_task_dtls_rec.task_estimated_price,0) > l_cost_price_rec.estimated_price THEN
2470 l_estimate_price_dif := (nvl(l_visit_task_dtls_rec.task_estimated_price,0) - l_cost_price_rec.estimated_price);
2471 l_visit_task_dtls_rec.visit_estimated_price := (nvl(l_visit_task_dtls_rec.visit_estimated_price,0) - l_estimate_price_dif);
2472 ELSE
2473 l_estimate_price_dif := (l_cost_price_rec.estimated_price - nvl(l_visit_task_dtls_rec.task_estimated_price,0));
2474 l_visit_task_dtls_rec.visit_estimated_price := (nvl(l_visit_task_dtls_rec.visit_estimated_price,0) + l_estimate_price_dif);
2475 END IF;
2476
2477 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
2478 fnd_log.string
2479 (
2480 fnd_log.level_statement,
2481 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
2482 ' Before updating visit ahl_visits_b, Actual Price: ' || l_visit_task_dtls_rec.visit_actual_price
2483 );
2484 fnd_log.string
2485 (
2486 fnd_log.level_statement,
2487 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
2488 ' Before updating visit ahl_visits_b, Estimated Price: ' || l_visit_task_dtls_rec.visit_estimated_price
2489 );
2490
2491 END IF;
2492
2493 -- Update Visit cost details with new values
2494 UPDATE AHL_VISITS_B
2495 SET actual_price = l_visit_task_dtls_rec.visit_actual_price,
2496 estimated_price = l_visit_task_dtls_rec.visit_estimated_price,
2497 object_version_number = l_visit_task_dtls_rec.visit_object_version_number + 1
2498 WHERE visit_id = l_visit_task_dtls_rec.visit_id;
2499
2500 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
2501 fnd_log.string
2502 (
2503 fnd_log.level_statement,
2504 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
2505 ' Before updating task ahl_visit_tasks_b, Actual Price: ' || l_actual_price
2506 );
2507 fnd_log.string
2508 (
2509 fnd_log.level_statement,
2510 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
2511 ' Before updating task ahl_visit_tasks_b, Estimated Price: ' || l_estimated_price
2512 );
2513
2514 END IF;
2515
2516 END IF; -- new values are different from old values
2517
2518 END IF; -- Module type is JSP
2519
2520 -- Update task cost details with new values
2521 UPDATE AHL_VISIT_TASKS_B
2522 SET actual_price = l_cost_price_rec.actual_price,
2523 estimated_price = l_cost_price_rec.estimated_price,
2524 object_version_number = l_visit_task_dtls_rec.task_object_version_number + 1
2525 WHERE visit_task_id = l_visit_task_dtls_rec.visit_task_id;
2526
2527 -- Assign out variable
2528 p_x_cost_price_rec.actual_price := l_cost_price_rec.actual_price;
2529 p_x_cost_price_rec.estimated_price := l_cost_price_rec.estimated_price;
2530
2531
2532 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
2533 fnd_log.string
2534 (
2535 fnd_log.level_statement,
2536 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
2537 ' End of API p_x_cost_price_rec Actual Price: ' || p_x_cost_price_rec.actual_price
2538 );
2539 fnd_log.string
2540 (
2541 fnd_log.level_statement,
2542 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
2543 ' End of API p_x_cost_price_rec Estimated Price: ' || p_x_cost_price_rec.estimated_price
2544 );
2545
2546 fnd_log.string
2547 (
2548 fnd_log.level_statement,
2549 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
2550 ' End of API p_x_cost_price_rec Cost Session Id: ' || p_x_cost_price_rec.cost_session_id
2551 );
2552 fnd_log.string
2553 (
2554 fnd_log.level_statement,
2555 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
2556 ' End of API p_x_cost_price_rec Mr Session Id: ' || p_x_cost_price_rec.mr_session_id
2557 );
2558
2559 END IF;
2560
2561 -- Check Error Message stack.
2562 x_msg_count := FND_MSG_PUB.count_msg;
2563 IF x_msg_count > 0 THEN
2564 RAISE FND_API.G_EXC_ERROR;
2565 END IF;
2566
2567 -- Standard check of p_commit
2568 IF FND_API.TO_BOOLEAN(p_commit) THEN
2569 COMMIT WORK;
2570 END IF;
2571
2572 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)THEN
2573 fnd_log.string
2574 (
2575 fnd_log.level_procedure,
2576 'ahl.plsql.AHL_VWP_TASK_CST_PR_PVT.Estimate_Task_Price.end',
2577 'At the end of PLSQL procedure'
2578 );
2579 END IF;
2580
2581 EXCEPTION
2582 WHEN FND_API.G_EXC_ERROR THEN
2583 x_return_status := FND_API.G_RET_STS_ERROR;
2584 ROLLBACK TO Estimate_Task_Price;
2585 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
2586 p_data => x_msg_data,
2587 p_encoded => fnd_api.g_false);
2588
2589
2590 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2591 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2592 ROLLBACK TO Estimate_Task_Price;
2593 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
2594 p_data => x_msg_data,
2595 p_encoded => fnd_api.g_false);
2596
2597
2598 WHEN OTHERS THEN
2599 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2600 ROLLBACK TO Estimate_Task_Price;
2601 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2602 fnd_msg_pub.add_exc_msg(p_pkg_name => G_PKG_NAME,
2603 p_procedure_name => 'Estimate_Task_Price',
2604 p_error_text => SUBSTR(SQLERRM,1,500));
2605 END IF;
2606 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
2607 p_data => x_msg_data,
2608 p_encoded => fnd_api.g_false);
2609 END Estimate_Task_Price;
2610
2611 -- -- Start of Comments --
2612 -- Procedure name : Get_Node_Cost_Details
2613 -- Type : Private(Called from Estimate and cost/price Task UI
2614 --
2615 -- Function : To update task price list
2616 --
2617 --
2618 -- Pre-reqs :
2619 -- Parameters :
2620 --
2621 -- Standard IN Parameters :
2622 -- p_api_version IN NUMBER Required
2623 -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
2624 -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
2625 -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
2626 --
2627 -- Standard OUT Parameters :
2628 -- x_return_status OUT VARCHAR2 Required
2629 -- x_msg_count OUT NUMBER Required
2630 -- x_msg_data OUT VARCHAR2 Required
2631 --
2632 -- Get Node Details Parameters:
2633 -- p_x_cost_price_rec IN OUT Cost_price_rec_type, Required
2634 --
2635 -- Version :
2636 -- Initial Version 1.0
2637 --
2638 -- End of Comments.
2639
2640 PROCEDURE Get_Node_Cost_Details (
2641 p_api_version IN NUMBER,
2642 p_init_msg_list IN VARCHAR2 := Fnd_Api.G_FALSE,
2643 p_commit IN VARCHAR2 := Fnd_Api.G_FALSE,
2644 p_validation_level IN NUMBER := Fnd_Api.G_VALID_LEVEL_FULL,
2645 p_module_type IN VARCHAR2 := NULL,
2646 x_return_status OUT NOCOPY VARCHAR2,
2647 x_msg_count OUT NOCOPY NUMBER,
2648 x_msg_data OUT NOCOPY VARCHAR2,
2649 p_x_cost_price_rec IN OUT NOCOPY AHL_VWP_VISIT_CST_PR_PVT.cost_price_rec_type
2650 )
2651 AS
2652
2653 l_cost_price_rec AHL_VWP_VISIT_CST_PR_PVT.cost_price_rec_type:=p_x_cost_price_rec;
2654 l_api_version CONSTANT NUMBER := 1.0;
2655 l_api_name CONSTANT VARCHAR2(30) := 'GET_NODE_COST_DETAILS';
2656
2657 -- yazhou 09Aug2005 starts
2658 -- bug fix #4542676
2659 -- Cursor to get visit status
2660 CURSOR visit_info_csr(c_visit_id IN NUMBER) IS
2661 SELECT status_code
2662 FROM ahl_visits_b
2663 WHERE visit_id = c_visit_id;
2664
2665 l_visit_status VARCHAR2(30);
2666 -- yazhou 09Aug2005 ends
2667
2668 BEGIN
2669
2670 SAVEPOINT Get_Node_Cost_Details_pvt;
2671
2672 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)THEN
2673 fnd_log.string
2674 (
2675 fnd_log.level_procedure,
2676 'ahl.plsql.AHL_VWP_TASK_CST_PR_PVT.Get_Node_Cost_Details',
2677 'At the start of procedure Get_Node_Cost_Details_pvt and the values are visit_id:'||l_cost_price_rec.visit_id||' visit task_id'||l_cost_price_rec.visit_task_id||
2678 'mr_Sesion_id'||l_cost_price_rec.mr_session_id|| ' cost session_id '||l_cost_price_rec.cost_Session_id
2679 );
2680 END IF;
2681
2682 -- Standard call to check for call compatibility
2683 IF NOT Fnd_Api.Compatible_API_Call(l_api_version, p_api_version, l_api_name,
2684 G_PKG_NAME) THEN
2685 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
2686 END IF;
2687
2688 -- Initialize message list if p_init_msg_list is set to TRUE
2689 IF Fnd_Api.To_Boolean(p_init_msg_list) THEN
2690 Fnd_Msg_Pub.Initialize;
2691 END IF;
2692
2693 -- Initialize API return status to success
2694 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
2695
2696 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
2697 fnd_log.string
2698 (
2699 fnd_log.level_statement,
2700 'AHL_VWP_TASK_CST_PR_PVT.Get_Node_Cost_Details Visit Task Id'||l_cost_price_rec.visit_task_id,
2701 'Visit ID: ' || l_cost_price_rec.visit_id
2702 );
2703 END IF;
2704
2705
2706 If l_cost_price_rec.visit_task_id is not null
2707 then
2708
2709 -- yazhou 09Aug2005 starts
2710 -- bug fix #4542676
2711
2712 OPEN visit_info_csr(l_cost_price_rec.visit_id);
2713 FETCH visit_info_csr INTO l_visit_status;
2714
2715 IF (visit_info_csr%NOTFOUND)THEN
2716 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_VWP_CST_INV_VISIT');
2717 FND_MSG_PUB.ADD;
2718 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)THEN
2719 fnd_log.string
2720 (
2721 fnd_log.level_exception,
2722 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
2723 'Visit id not found in ahl_visits_b table'
2724 );
2725 END IF;
2726 CLOSE visit_info_csr;
2727 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2728 END IF;
2729 CLOSE visit_info_csr;
2730
2731 -- Not to calculate cost if visit is in cancelled status
2732 IF l_visit_status <>'CANCELLED' THEN
2733
2734 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)THEN
2735 fnd_log.string
2736 (
2737 fnd_log.level_procedure,
2738 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
2739 'Before call to AHL_VWP_COST_PVT.calculate_wo_cost'
2740 );
2741 END IF;
2742
2743 AHL_VWP_COST_PVT.calculate_wo_cost
2744 (p_api_version => p_api_version,
2745 p_init_msg_list => Fnd_Api.G_FALSE,
2746 p_commit => Fnd_Api.G_FALSE,
2747 p_validation_level =>Fnd_Api.G_VALID_LEVEL_FULL,
2748 p_x_cost_price_rec => l_cost_price_rec,
2749 x_return_status =>x_return_status );
2750
2751 --x_msg_count := Fnd_Msg_Pub.count_msg;
2752 IF x_return_status <> Fnd_Api.G_RET_STS_SUCCESS THEN
2753
2754 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)THEN
2755 fnd_log.string
2756 (
2757 fnd_log.level_procedure,
2758 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
2759 'Error thrown by AHL_VWP_COST_PVT.calculate_wo_cost'
2760 );
2761 END IF;
2762
2763 RAISE Fnd_Api.G_EXC_ERROR;
2764 END IF;
2765
2766
2767 IF (l_cost_price_rec.Is_Cst_Struc_updated = 'N') AND (l_cost_price_rec.workorder_id IS NOT NULL) THEN
2768
2769
2770 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)THEN
2771 fnd_log.string
2772 (
2773 fnd_log.level_procedure,
2774 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
2775 'Before call to AHL_VWP_COST_PVT.calculate_task_cost'
2776 );
2777 END IF;
2778
2779 AHL_VWP_COST_PVT.calculate_node_cost(
2780 p_visit_task_id =>l_cost_price_rec.visit_task_id,
2781 p_session_id => l_cost_price_rec.cost_session_id,
2782 x_actual_cost=>l_cost_price_rec.actual_cost,
2783 x_estimated_cost =>l_cost_price_rec.estimated_cost,
2784 x_return_status =>x_return_status );
2785
2786 END IF;
2787
2788 END IF; --Visit status is not cancelled
2789
2790 -- yazhou 09Aug2005 ends
2791
2792 ELSE
2793 -- Error : Visit Task id is null
2794 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level)THEN
2795 fnd_log.string
2796 (
2797 fnd_log.level_error,
2798 'ahl.plsql.AHL_VWP_TASK_CST_PR_PVT.Get_Node_Cost_Details',
2799 'Error : visit task id is null'
2800 );
2801
2802 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_VWP_CST_INPUT_MISS');
2803 FND_MSG_PUB.ADD;
2804 RAISE FND_API.G_EXC_ERROR;
2805 END IF;
2806
2807 END IF;
2808
2809 x_msg_count := Fnd_Msg_Pub.count_msg;
2810 IF x_msg_count > 0 THEN
2811 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)THEN
2812 fnd_log.string
2813 (
2814 fnd_log.level_procedure,
2815 'ahl.plsql.AHL_VWP_COST_PVT.calculate_task_cost',
2816 'Error thrown by AHL_VWP_COST_PVT.calculate_task_cost'
2817 );
2818 END IF;
2819
2820 RAISE Fnd_Api.G_EXC_ERROR;
2821 END IF;
2822
2823 p_x_cost_price_rec:= l_cost_price_rec;
2824
2825
2826 IF Fnd_Api.TO_BOOLEAN(p_commit) THEN
2827 COMMIT WORK;
2828 END IF;
2829
2830
2831 EXCEPTION
2832 WHEN FND_API.G_EXC_ERROR THEN
2833 x_return_status := FND_API.G_RET_STS_ERROR;
2834 ROLLBACK TO Get_Node_Cost_Details_pvt;
2835 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
2836 p_data => x_msg_data,
2837 p_encoded => fnd_api.g_false);
2838
2839
2840 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2841 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2842 ROLLBACK TO Get_Node_Cost_Details_pvt;
2843 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
2844 p_data => x_msg_data,
2845 p_encoded => fnd_api.g_false);
2846
2847
2848 WHEN OTHERS THEN
2849 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2850 ROLLBACK TO Get_Node_Cost_Details_pvt;
2851 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2852 fnd_msg_pub.add_exc_msg(p_pkg_name => G_PKG_NAME,
2853 p_procedure_name => 'Get_Node_Cost_Details',
2854 p_error_text => SUBSTR(SQLERRM,1,500));
2855 END IF;
2856 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
2857 p_data => x_msg_data,
2858 p_encoded => fnd_api.g_false);
2859
2860
2861 end Get_Node_Cost_Details;
2862
2863
2864 PROCEDURE GET_TASK_ITEMS_NO_PRICE (
2865 p_api_version IN NUMBER,
2866 p_init_msg_list IN VARCHAR2 := Fnd_Api.G_FALSE,
2867 p_commit IN VARCHAR2 := Fnd_Api.G_FALSE,
2868 p_validation_level IN NUMBER := Fnd_Api.G_VALID_LEVEL_FULL,
2869 p_module_type IN VARCHAR2 := NULL,
2870 x_return_status OUT NOCOPY VARCHAR2,
2871 x_msg_count OUT NOCOPY NUMBER,
2872 x_msg_data OUT NOCOPY VARCHAR2,
2873 p_cost_price_rec IN AHL_VWP_VISIT_CST_PR_PVT.cost_price_rec_type,
2874 x_cost_price_tbl OUT NOCOPY AHL_VWP_VISIT_CST_PR_PVT.cost_price_tbl_type
2875 )
2876 AS
2877 -- Local Variables
2878
2879 l_valid_flag varchar2(1);
2880 l_error_msg_code varchar2(30);
2881
2882 l_cost_price_rec AHL_VWP_VISIT_CST_PR_PVT.cost_price_rec_type:=p_cost_price_rec;
2883
2884 l_job_status_code VARCHAR2(30);
2885 l_job_status_mean VARCHAR2(80);
2886
2887 l_item_tbl AHL_VWP_RULES_PVT.ITEM_TBL_TYPE;
2888 l_x_item_tbl AHL_VWP_RULES_PVT.ITEM_TBL_TYPE;
2889
2890 l_cost_price_tbl AHL_VWP_VISIT_CST_PR_PVT.cost_price_tbl_type;
2891
2892 l_api_name VARCHAR2(30) := 'GET_TASK_ITEMS_NO_PRICE';
2893 l_api_version NUMBER := 1.0;
2894 l_msg_data VARCHAR2(2000);
2895 l_return_status VARCHAR2(1);
2896 l_msg_count NUMBER;
2897
2898 l_z number:=0;
2899 l_flag varchar2(1);
2900
2901 l_release_visit_required VARCHAR2(1) :='N';
2902
2903 -- Get task info
2904
2905 Cursor c_task_csr(c_visit_task_id in number)
2906 Is
2907 Select visit_task_id,
2908 task_type_code,
2909 mr_id,
2910 visit_id,
2911 price_list_id,
2912 service_request_id,
2913 start_date_time,
2914 end_date_time
2915 from ahl_visit_tasks_vl
2916 where visit_task_id=c_visit_task_id;
2917
2918 l_task_rec c_task_csr%rowtype;
2919
2920 -- Get visit info
2921
2922 Cursor c_visit_csr(c_visit_id in number)
2923 Is
2924 Select visit_id,
2925 price_list_id,
2926 outside_party_flag,
2927 service_request_id,
2928 organization_id,
2929 any_task_chg_flag
2930 from ahl_visits_vl
2931 where visit_id=c_visit_id;
2932
2933 l_visit_rec c_visit_csr%rowtype;
2934
2935 -- Get customer info
2936
2937 Cursor c_customer_csr(c_sr_req_id in number)
2938 Is
2939 select Customer_id
2940 from CS_INCIDENTS_ALL_B
2941 where incident_id=c_sr_req_id;
2942
2943
2944 -- AnRaj: Query changed for fixing performance bug 4919475
2945 /*
2946 Select mr_name,
2947 mr_description,
2948 task_number,
2949 task_name
2950 from AHL_SEARCH_VISIT_TASK_V
2951 Where TASK_ID=C_VISIT_TASK_ID;
2952 */
2953 Cursor c_task_info(c_visit_task_id in number)
2954 Is
2955 select mrb.title mr_name,
2956 mrtl.description mr_description,
2957 visit_task_number task_number,
2958 visit_task_name task_name
2959 from ahl_visit_tasks_vl tsk,
2960 ahl_mr_headers_tl mrtl,
2961 ahl_mr_headers_b mrb,
2962 ahl_mr_routes mrr,
2963 ahl_visits_vl avts
2964 where tsk.mr_route_id = mrr.mr_route_id(+)
2965 and mrr.mr_header_id = mrb.mr_header_id(+)
2966 and mrb.mr_header_id = mrtl.mr_header_id (+)
2967 and mrtl.language(+) = USERENV('LANG')
2968 and nvl(tsk.status_code,'X') <> 'DELETED'
2969 and avts.visit_id = tsk.visit_id
2970 and avts.template_flag = 'N'
2971 and tsk.task_type_code <> 'SUMMARY'
2972 and visit_task_id=c_visit_task_id
2973 UNION
2974 select title mr_name,
2975 mrh.description mr_description,
2976 visit_task_number task_number,
2977 visit_task_name task_name
2978 from ahl_visit_tasks_vl tsk,
2979 ahl_mr_headers_vl mrh,
2980 AHL_VISITS_VL AVTS
2981 where MRH.MR_HEADER_ID = TSK.MR_ID
2982 AND NVL(TSK.STATUS_CODE,'X') <> 'DELETED'
2983 AND AVTS.VISIT_ID = TSK.VISIT_ID
2984 AND AVTS.TEMPLATE_FLAG = 'N'
2985 AND VISIT_TASK_ID=C_VISIT_TASK_ID;
2986
2987 l_task_info_rec c_task_info%rowtype;
2988
2989 -- AnRaj: Query changed for fixing performance bug 4919475
2990 /*
2991 Select CONCATENATED_SEGMENTS,DESCRIPTION,INVENTORY_ORG_ID,organization_name
2992 FROM AHL_MTL_ITEMS_OU_V
2993 WHERE INVENTORY_ITEM_ID=C_ITEM_ID
2994 AND INVENTORY_ORG_ID=C_ORG_ID;
2995 */
2996 Cursor c_item_info(c_item_id in number,c_org_id in number)
2997 Is
2998 SELECT mtl.CONCATENATED_SEGMENTS,
2999 mtl.DESCRIPTION,
3000 mtl.organization_id INVENTORY_ORG_ID,
3001 hou.name organization_name
3002 FROM mtl_system_items_kfv mtl,hr_organization_units hou,inv_organization_info_v org
3003 WHERE mtl.organization_id = org.organization_id
3004 AND hou.organization_id = org.organization_id
3005 AND NVL (org.operating_unit, mo_global.get_current_org_id ()) =mo_global.get_current_org_id()
3006 AND mtl.inventory_item_id=c_item_id
3007 AND mtl.organization_id=c_org_id;
3008
3009 l_item_info_rec c_item_info%rowtype;
3010
3011
3012 BEGIN
3013
3014 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)THEN
3015 fnd_log.string
3016 (
3017 fnd_log.level_procedure,
3018 'ahl.plsql.AHL_VWP_TASK_CST_PR_PVT.GET_TASK_ITEMS_NO_PRICE.begin',
3019 'At the start of PLSQL procedure'
3020 );
3021
3022 END IF;
3023
3024 SAVEPOINT GET_TASK_ITEMS_NO_PRICE_PVT;
3025
3026 -- Standard call to check for call compatibility
3027 IF NOT Fnd_Api.Compatible_API_Call(l_api_version, p_api_version, l_api_name,
3028 G_PKG_NAME) THEN
3029 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
3030 END IF;
3031
3032 -- Initialize message list if p_init_msg_list is set to TRUE
3033 IF Fnd_Api.To_Boolean(p_init_msg_list) THEN
3034 Fnd_Msg_Pub.Initialize;
3035 END IF;
3036
3037 -- Initialize API return status to success
3038 x_return_status := FND_API.G_RET_STS_SUCCESS;
3039
3040 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
3041 fnd_log.string
3042 (
3043 fnd_log.level_statement,
3044 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
3045 'Request to get items without price for Task ID : ' || l_cost_price_rec.visit_task_id
3046 );
3047
3048 END IF;
3049
3050 -- Check for Required Parameters
3051 IF(l_cost_price_rec.visit_task_id IS NULL OR
3052 l_cost_price_rec.visit_task_id = FND_API.G_MISS_NUM) THEN
3053 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_VWP_CST_INPUT_MISS');
3054 FND_MSG_PUB.ADD;
3055 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level)THEN
3056 fnd_log.string
3057 (
3058 fnd_log.level_error,
3059 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
3060 'Visit Task id is mandatory but found null in input '
3061 );
3062 END IF;
3063 RAISE FND_API.G_EXC_ERROR;
3064 END IF;
3065
3066
3067
3068 open c_task_csr(p_cost_price_rec.visit_Task_id);
3069 fetch c_task_csr into l_task_rec;
3070
3071
3072 IF c_task_csr%NOTFOUND THEN
3073 FND_MESSAGE.set_name( 'AHL','AHL_VWP_VISIT_TASK_INVALID' );
3074 FND_MSG_PUB.add;
3075 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level)THEN
3076 fnd_log.string
3077 (
3078 fnd_log.level_error,
3079 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
3080 'Visit Task not found in ahl_visit_tasks_b table'
3081 );
3082 END IF;
3083 CLOSE c_task_csr;
3084 RAISE FND_API.G_EXC_ERROR;
3085 END IF;
3086 CLOSE c_task_csr;
3087
3088 l_cost_price_rec.visit_id:=l_task_rec.visit_id;
3089
3090
3091 -- need to add notfound exception handling later
3092
3093 open c_visit_csr(l_task_rec.visit_id);
3094 fetch c_visit_csr into l_visit_rec;
3095 close c_visit_csr;
3096
3097
3098 If p_module_type = 'JSP' then
3099
3100 AHL_VWP_VISIT_CST_PR_PVT.check_for_release_visit
3101 (
3102 p_visit_id =>l_task_rec.visit_id,
3103 x_release_visit_required =>l_release_visit_required
3104 );
3105
3106 -- Release visit if required
3107 IF l_release_visit_required ='Y' THEN
3108
3109 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
3110 fnd_log.string
3111 (
3112 fnd_log.level_procedure,
3113 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
3114 'Before calling ahl vwp proj prod pvt.release visit'
3115 );
3116
3117 END IF;
3118
3119 AHL_VWP_PROJ_PROD_PVT.release_visit
3120 (
3121 p_api_version =>l_api_version,
3122 p_init_msg_list =>Fnd_Api.g_false,
3123 p_commit =>Fnd_Api.g_false,
3124 p_validation_level =>Fnd_Api.g_valid_level_full,
3125 p_module_type => 'CST',
3126 x_return_status =>l_return_Status,
3127 x_msg_count =>x_msg_count,
3128 x_msg_data =>x_msg_data,
3129 p_visit_id =>l_task_rec.visit_id
3130 );
3131
3132 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
3133 fnd_log.string
3134 (
3135 fnd_log.level_procedure,
3136 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||': End API',
3137 'After calling ahl vwp proj prod pvt.Release Visit task wo Return Status : '|| l_return_status
3138 );
3139 END IF;
3140
3141 -- Check Error Message stack.
3142 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
3143 l_msg_count := FND_MSG_PUB.count_msg;
3144 IF l_msg_count > 0 THEN
3145 RAISE FND_API.G_EXC_ERROR;
3146 END IF;
3147 END IF;
3148
3149 END IF; -- release required flag
3150
3151 END IF; -- FOR P_MODULE_TYPE
3152
3153
3154
3155 -- Populate pricing attributes
3156
3157 If l_visit_rec.outside_party_flag ='N'
3158 then
3159 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_VWP_CST_OUTSDPRTY_FLAG');
3160 FND_MSG_PUB.ADD;
3161 RAISE FND_API.G_EXC_ERROR;
3162 --Display an error message `Visit number visit_number is not an outside party.'
3163 Else
3164 If l_task_rec.price_list_id is not Null
3165 then
3166 l_cost_price_rec.price_list_id :=l_task_rec.price_list_id;
3167 Elsif l_visit_rec.price_list_id is not Null
3168 then
3169 l_cost_price_rec.price_list_id :=l_visit_rec.price_list_id;
3170 Else
3171 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_VWP_CST_PRICELISTIDNULL');
3172 FND_MSG_PUB.ADD;
3173 RAISE FND_API.G_EXC_ERROR;
3174
3175 END IF;
3176
3177 End if;
3178
3179 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
3180 fnd_log.string
3181 (
3182 fnd_log.level_statement,
3183 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
3184 'Price List ID :' ||l_cost_price_rec.price_list_id
3185 );
3186 END IF;
3187
3188
3189 l_cost_price_rec.organization_id:=l_visit_rec.organization_id;
3190
3191
3192 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
3193 fnd_log.string
3194 (
3195 fnd_log.level_statement,
3196 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
3197 'Organization ID :' ||l_cost_price_rec.organization_id
3198 );
3199 END IF;
3200
3201 -- Populate customer ID if not passed
3202 IF (l_cost_price_rec.Customer_Id is null) OR
3203 (l_cost_price_rec.Customer_Id = FND_API.G_MISS_NUM) THEN
3204
3205 Open c_customer_csr(l_visit_rec.service_request_id);
3206 fetch c_customer_csr into l_cost_price_rec.customer_id;
3207 close c_customer_csr;
3208 END IF;
3209
3210 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
3211 fnd_log.string
3212 (
3213 fnd_log.level_statement,
3214 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
3215 'Customer ID :' ||l_cost_price_rec.customer_id
3216 );
3217 END IF;
3218
3219 -- Populate currency code if not passed
3220 IF(l_cost_price_rec.currency IS NULL OR
3221 l_cost_price_rec.currency = FND_API.G_MISS_CHAR) THEN
3222
3223 AHL_VWP_RULES_PVT.check_currency_for_costing
3224 (p_visit_id =>l_task_rec.visit_id,
3225 x_currency_code =>l_cost_price_rec.currency
3226 );
3227
3228 -- Check for value is null
3229 IF l_cost_price_rec.currency IS NULL THEN
3230 FND_MESSAGE.Set_Name(G_PKG_NAME,'AHL_VWP_CST_NO_CURRENCY');
3231 FND_MSG_PUB.ADD;
3232 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
3233 fnd_log.string
3234 (
3235 fnd_log.level_statement,
3236 'ahl.plsql.'||G_PKG_NAME||'.'||L_API_NAME,
3237 'No curency is defined for the organization of the visit'
3238 );
3239 END IF;
3240 RAISE FND_API.G_EXC_ERROR;
3241 END IF;
3242 END IF; --If currency is null
3243
3244 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
3245 fnd_log.string
3246 (
3247 fnd_log.level_statement,
3248 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
3249 'Currency Code :' ||l_cost_price_rec.currency
3250 );
3251 END IF;
3252
3253
3254
3255 ahl_vwp_rules_pvt.Check_Job_Status
3256 (
3257 p_id => l_cost_price_rec.visit_task_id,
3258 p_is_task_flag => 'Y',
3259 x_status_code => l_job_status_code,
3260 x_status_meaning => l_job_status_mean);
3261
3262 IF (l_job_status_code is NULL) THEN
3263 l_msg_count := FND_MSG_PUB.count_msg;
3264 IF l_msg_count > 0 THEN
3265 RAISE FND_API.G_EXC_ERROR;
3266 END IF;
3267 END IF;
3268
3269 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
3270 fnd_log.string
3271 (
3272 fnd_log.level_statement,
3273 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
3274 'Job Status :' ||l_job_status_code
3275 );
3276 fnd_log.string
3277 (
3278 fnd_log.level_statement,
3279 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
3280 'task start date :' ||l_cost_price_rec.task_start_date
3281 );
3282 END IF;
3283
3284
3285
3286 IF (l_job_status_code='17'
3287 and l_cost_price_rec.task_start_date is null)
3288 THEN
3289
3290 l_cost_price_rec.Task_Start_Date := l_task_rec.start_date_time;
3291 END IF; -- job status
3292
3293
3294
3295 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
3296 fnd_log.string
3297 (
3298 fnd_log.level_statement,
3299 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
3300 'Task Start Date is: ' ||l_cost_price_rec.Task_Start_Date
3301 );
3302 END IF;
3303
3304 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
3305 fnd_log.string
3306 (
3307 fnd_log.level_statement,
3308 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
3309 'Task Type is: ' ||l_task_rec.task_type_code
3310 );
3311 END IF;
3312
3313
3314 If l_task_rec.MR_ID is not Null and l_task_rec.task_type_code='SUMMARY'
3315 then
3316
3317 AHL_VWP_MR_CST_PR_PVT.get_mr_items_no_price
3318 (
3319 p_api_version =>l_api_version,
3320 p_init_msg_list =>Fnd_Api.g_false,
3321 p_commit =>Fnd_Api.g_false,
3322 p_validation_level =>Fnd_Api.g_valid_level_full,
3323 p_module_type =>p_module_type,
3324 x_return_status =>l_return_status,
3325 x_msg_count =>x_msg_count,
3326 x_msg_data =>x_msg_data,
3327 p_cost_price_rec =>l_cost_price_rec,
3328 x_cost_price_tbl =>l_cost_price_tbl
3329 );
3330 else
3331
3332
3333
3334 If l_task_rec.task_type_code='UNASSOCIATED'
3335 then
3336 AHL_VWP_TASK_CST_PR_PVT.Get_Unassociated_Items
3337 (
3338 p_api_version =>p_api_version,
3339 p_init_msg_list =>Fnd_Api.G_FALSE,
3340 p_commit =>Fnd_Api.G_FALSE,
3341 p_validation_level =>Fnd_Api.G_VALID_LEVEL_FULL,
3342 p_module_type =>NULL,
3343 x_return_status =>l_return_Status,
3344 x_msg_count =>x_msg_count,
3345 x_msg_data =>x_msg_data,
3346 p_visit_task_id =>l_task_rec.visit_task_id,
3347 p_job_status_code =>l_job_status_code,
3348 x_item_tbl =>l_item_tbl
3349 );
3350
3351 elsif l_task_rec.MR_ID is not Null and (l_task_rec.task_type_code ='PLANNED'
3352 OR l_task_rec.task_type_code ='UNPLANNED')
3353 then
3354 AHL_VWP_TASK_CST_PR_PVT.get_other_task_items
3355 (
3356 p_api_version =>p_api_version,
3357 p_init_msg_list =>Fnd_Api.G_FALSE,
3358 p_commit =>Fnd_Api.G_FALSE,
3359 p_validation_level =>Fnd_Api.G_VALID_LEVEL_FULL,
3360 p_module_type =>NULL,
3361 x_return_status =>l_return_Status,
3362 x_msg_count =>x_msg_count,
3363 x_msg_data =>x_msg_data,
3364 p_visit_task_id =>l_task_rec.visit_task_id,
3365 p_job_status_code =>l_job_status_code,
3366 p_task_start_time =>l_cost_price_rec.Task_Start_Date,
3367 x_item_tbl =>l_item_tbl
3368 );
3369 end if;
3370
3371 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
3372 THEN
3373 fnd_log.string
3374 (
3375 fnd_log.level_statement,
3376 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
3377 'Number of rows in Item List table : ' ||l_item_tbl.count
3378 );
3379 END IF;
3380
3381
3382 AHL_VWP_PRICE_PVT.get_items_without_price
3383 (
3384 p_item_tbl =>l_item_tbl,
3385 p_price_list_id =>l_cost_price_rec.price_list_id,
3386 p_customer_id =>l_cost_price_rec.customer_id,
3387 p_currency_code =>l_cost_price_rec.currency,
3388 x_item_tbl =>l_x_item_tbl,
3389 x_return_status =>l_return_status
3390 );
3391
3392 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
3393 THEN
3394 fnd_log.string
3395 (
3396 fnd_log.level_statement,
3397 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
3398 'Number of rows in Item Without Price table : ' ||l_item_tbl.count
3399 );
3400 END IF;
3401
3402 If l_x_item_tbl.count > 0 then
3403 For i in l_x_item_tbl.first .. l_x_item_tbl.last
3404 loop
3405 l_cost_price_tbl(i).billing_item_id := l_x_item_tbl(i).item_id;
3406 l_cost_price_tbl(i).visit_task_id := l_x_item_tbl(i).visit_task_id;
3407 End loop;
3408 End if;
3409
3410 if l_cost_price_tbl.count >0
3411 then
3412 For i in l_cost_price_tbl.first .. l_cost_price_tbl.last
3413 loop
3414 open c_task_info(l_cost_price_tbl(i).visit_task_id);
3415 fetch c_task_info into l_task_info_rec;
3416 If c_task_info%found
3417 then
3418 l_cost_price_tbl(i).mr_Title:=l_task_info_rec.MR_name;
3419 l_cost_price_tbl(i).MR_Description:= l_task_info_rec.MR_Description;
3420 l_cost_price_tbl(i).Visit_task_number := l_task_info_rec.task_number;
3421 l_cost_price_tbl(i).task_name := l_task_info_rec.task_name;
3422 End if;
3423 close c_task_info;
3424
3425 open c_item_info(l_cost_price_tbl(i).billing_item_id,l_visit_rec.organization_id);
3426 fetch c_item_info into l_item_info_rec;
3427 If c_item_info%found
3428 then
3429 l_cost_price_tbl(i).Item_name := l_item_info_rec.concatenated_segments;
3430 l_cost_price_tbl(i).Item_Description := l_item_info_rec.DESCRIPTION;
3431 l_cost_price_tbl(i).Organization_name:=l_item_info_rec.organization_name;
3432 End if;
3433 close c_item_info;
3434
3435 End loop;
3436 end if;
3437
3438 End IF; --- mr or other tasks
3439
3440 -- Check Error Message stack.
3441 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
3442 l_msg_count := FND_MSG_PUB.count_msg;
3443 IF l_msg_count > 0 THEN
3444 RAISE FND_API.G_EXC_ERROR;
3445 END IF;
3446 END IF;
3447
3448 x_cost_price_tbl:=l_cost_price_tbl;
3449
3450 -- Standard check of p_commit
3451 IF FND_API.TO_BOOLEAN(p_commit) THEN
3452 COMMIT;
3453 END IF;
3454
3455
3456 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)THEN
3457 fnd_log.string
3458 (
3459 fnd_log.level_procedure,
3460 'ahl.plsql.AHL_VWP_TASK_CST_PR_PVT.GET_TASK_ITEMS_NO_PRICE.begin',
3461 'At the end of PLSQL procedure'
3462 );
3463
3464 END IF;
3465
3466 EXCEPTION
3467 WHEN FND_API.G_EXC_ERROR THEN
3468 x_return_status := FND_API.G_RET_STS_ERROR;
3469 ROLLBACK TO GET_TASK_ITEMS_NO_PRICE_PVT;
3470 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
3471 p_data => x_msg_data,
3472 p_encoded => fnd_api.g_false);
3473
3474
3475 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3476 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3477 ROLLBACK TO GET_TASK_ITEMS_NO_PRICE_PVT;
3478 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
3479 p_data => x_msg_data,
3480 p_encoded => fnd_api.g_false);
3481
3482
3483 WHEN OTHERS THEN
3484 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3485 ROLLBACK TO GET_TASK_ITEMS_NO_PRICE_PVT;
3486 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
3487 fnd_msg_pub.add_exc_msg(p_pkg_name => G_PKG_NAME,
3488 p_procedure_name => L_API_NAME,
3489 p_error_text => SUBSTR(SQLERRM,1,500));
3490 END IF;
3491 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
3492 p_data => x_msg_data,
3493 p_encoded => fnd_api.g_false);
3494
3495
3496 END GET_TASK_ITEMS_NO_PRICE;
3497
3498 -- GET_TASK_ITEMS_NO_PRICE Parameters: modified code as per dld 1.9
3499 -- GET_UNASSOCIATED_ITEMS Parameters
3500 -- GET_MR_SUMMARY_ITEMS
3501 -- GET_OTHER_TASK_ITEMS
3502 -- Refer for more details \\Industry1-nt\telecom\Advanced Services Online\300 DLD\11.5.10\VWP\Costing_DLD_Part2_V1.8.doc
3503 --
3504 PROCEDURE GET_UNASSOCIATED_ITEMS (
3505 p_api_version IN NUMBER,
3506 p_init_msg_list IN VARCHAR2 := Fnd_Api.G_FALSE,
3507 p_commit IN VARCHAR2 := Fnd_Api.G_FALSE,
3508 p_validation_level IN NUMBER := Fnd_Api.G_VALID_LEVEL_FULL,
3509 p_module_type IN VARCHAR2 := NULL,
3510 x_return_status OUT NOCOPY VARCHAR2,
3511 x_msg_count OUT NOCOPY NUMBER,
3512 x_msg_data OUT NOCOPY VARCHAR2,
3513 p_visit_task_id IN NUMBER,
3514 p_job_status_code IN VARCHAR2,
3515 x_item_tbl OUT NOCOPY AHL_VWP_RULES_PVT.ITEM_TBL_TYPE
3516 )
3517 AS
3518 l_api_name VARCHAR2(30):='GET_UNASSOCIATED_ITEMS';
3519 l_api_version NUMBER:= 1.0;
3520 l_msg_data VARCHAR2(2000);
3521 l_return_status VARCHAR2(1);
3522 l_msg_count NUMBER;
3523
3524
3525 l_item_tbl1 AHL_VWP_RULES_PVT.ITEM_TBL_TYPE;
3526 l_item_tbl2 AHL_VWP_RULES_PVT.ITEM_TBL_TYPE;
3527 l_item_tbl AHL_VWP_RULES_PVT.ITEM_TBL_TYPE;
3528
3529 BEGIN
3530
3531 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)THEN
3532 fnd_log.string
3533 (
3534 fnd_log.level_procedure,
3535 'ahl.plsql.AHL_VWP_TASK_CST_PR_PVT.GET_UNASSOCIATED_ITEMS.begin',
3536 'At the start of PLSQL procedure'
3537 );
3538
3539 END IF;
3540
3541 SAVEPOINT GET_UNASSOCIATED_ITEMS_PVT;
3542
3543 If p_job_status_code<>'17'
3544 then
3545
3546 AHL_VWP_PRICE_PVT.Check_Item_for_Resource_Trans
3547 (
3548 p_visit_task_id =>p_visit_task_id,
3549 x_item_tbl =>l_item_tbl1,
3550 x_return_status =>l_return_status
3551 );
3552
3553 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
3554 fnd_log.string
3555 (
3556 fnd_log.level_statement,
3557 'ahl.plsql.AHL_VWP_TASK_CST_PR_PVT.GET_UNASSOCIATED_ITEMS.begin',
3558 'Number of items for resource transactions: ' || l_item_tbl1.count
3559 );
3560
3561 END IF;
3562
3563 AHL_VWP_PRICE_PVT.Check_Item_for_Materials_Trans
3564 (
3565 p_visit_task_id =>p_visit_task_id,
3566 x_item_tbl =>l_item_tbl2,
3567 x_return_status =>l_return_status
3568 );
3569
3570
3571 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
3572 fnd_log.string
3573 (
3574 fnd_log.level_statement,
3575 'ahl.plsql.AHL_VWP_TASK_CST_PR_PVT.GET_UNASSOCIATED_ITEMS.begin',
3576 'Number of items for materials transactions: ' || l_item_tbl2.count
3577 );
3578
3579 END IF;
3580
3581 end if;
3582
3583 -- Check Error Message stack.
3584 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
3585 l_msg_count := FND_MSG_PUB.count_msg;
3586 IF l_msg_count > 0 THEN
3587 RAISE FND_API.G_EXC_ERROR;
3588 END IF;
3589 END IF;
3590
3591 ahl_vwp_rules_pvt.merge_for_unique_items
3592 (
3593 p_item_tbl1 =>l_item_tbl1,
3594 p_item_tbl2 =>l_item_tbl2,
3595 x_item_tbl =>l_item_tbl
3596 );
3597
3598 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
3599 fnd_log.string
3600 (
3601 fnd_log.level_statement,
3602 'ahl.plsql.AHL_VWP_TASK_CST_PR_PVT.GET_UNASSOCIATED_ITEMS.begin',
3603 'Number of items for material and resource transactions: ' || l_item_tbl.count
3604 );
3605
3606 END IF;
3607
3608 x_item_tbl:=l_item_tbl;
3609
3610 -- Standard check of p_commit
3611 IF FND_API.TO_BOOLEAN(p_commit) THEN
3612 COMMIT WORK;
3613 END IF;
3614
3615 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
3616 fnd_log.string
3617 (
3618 fnd_log.level_statement,
3619 'ahl.plsql.AHL_VWP_TASK_CST_PR_PVT.GET_UNASSOCIATED_ITEMS.end',
3620 'At the end of PLSQL procedure'
3621 );
3622
3623 END IF;
3624
3625 EXCEPTION
3626 WHEN FND_API.G_EXC_ERROR THEN
3627 x_return_status := FND_API.G_RET_STS_ERROR;
3628 ROLLBACK TO GET_UNASSOCIATED_ITEMS_PVT;
3629 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
3630 p_data => x_msg_data,
3631 p_encoded => fnd_api.g_false);
3632
3633
3634 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3635 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3636 ROLLBACK TO GET_UNASSOCIATED_ITEMS_PVT;
3637 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
3638 p_data => x_msg_data,
3639 p_encoded => fnd_api.g_false);
3640
3641
3642 WHEN OTHERS THEN
3643 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3644 ROLLBACK TO GET_UNASSOCIATED_ITEMS_PVT;
3645 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
3646 fnd_msg_pub.add_exc_msg(p_pkg_name => G_PKG_NAME,
3647 p_procedure_name => L_API_NAME,
3648 p_error_text => SUBSTR(SQLERRM,1,500));
3649 END IF;
3650 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
3651 p_data => x_msg_data,
3652 p_encoded => fnd_api.g_false);
3653 END GET_UNASSOCIATED_ITEMS;
3654
3655
3656 PROCEDURE GET_OTHER_TASK_ITEMS (
3657 p_api_version IN NUMBER,
3658 p_init_msg_list IN VARCHAR2 := Fnd_Api.G_FALSE,
3659 p_commit IN VARCHAR2 := Fnd_Api.G_FALSE,
3660 p_validation_level IN NUMBER := Fnd_Api.G_VALID_LEVEL_FULL,
3661 p_module_type IN VARCHAR2 := NULL,
3662 x_return_status OUT NOCOPY VARCHAR2,
3663 x_msg_count OUT NOCOPY NUMBER,
3664 x_msg_data OUT NOCOPY VARCHAR2,
3665 p_visit_task_id IN NUMBER,
3666 p_job_status_code IN VARCHAR2,
3667 p_task_start_time IN DATE,
3668 x_item_tbl OUT NOCOPY AHL_VWP_RULES_PVT.ITEM_TBL_TYPE
3669 )
3670 AS
3671
3672 Cursor c_task_csr (c_visit_task_id in number)
3673 Is
3674 Select a.mr_route_id,a.mr_id,b.organization_id
3675 From AHL_VISIT_TASKS_B a,ahl_visits_b b
3676 Where a.visit_task_id=c_visit_task_id
3677 and a.visit_id=b.visit_id;
3678
3679 l_task_rec c_task_csr%rowtype;
3680
3681 Cursor c_mr_route_csr(c_mr_route_id in number)
3682 Is
3683 Select mr_route_id,route_id
3684 From ahl_mr_Routes_v
3685 where mr_route_id=c_mr_route_id;
3686
3687 l_mr_route_rec c_mr_route_csr%rowtype;
3688
3689 l_item_tbl_res AHL_VWP_RULES_PVT.ITEM_TBL_TYPE;
3690 l_item_tbl_mat AHL_VWP_RULES_PVT.ITEM_TBL_TYPE;
3691 l_item_tbl1 AHL_VWP_RULES_PVT.ITEM_TBL_TYPE;
3692 l_item_tbl2 AHL_VWP_RULES_PVT.ITEM_TBL_TYPE;
3693 l_item_tbl AHL_VWP_RULES_PVT.ITEM_TBL_TYPE;
3694
3695 l_api_name VARCHAR2(30):='GET_OTHER_TASK_ITEMS';
3696 L_FULL_NAME CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || L_API_NAME;
3697 l_api_version NUMBER:= 1.0;
3698 l_msg_data VARCHAR2(2000);
3699 l_return_status VARCHAR2(1);
3700 l_msg_count NUMBER;
3701
3702 BEGIN
3703
3704 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)THEN
3705 fnd_log.string
3706 (
3707 fnd_log.level_procedure,
3708 'ahl.plsql.AHL_VWP_TASK_CST_PR_PVT.GET_OTHER_TASK_ITEMS.begin',
3709 'At the begining of PLSQL procedure'
3710 );
3711
3712 END IF;
3713
3714 SAVEPOINT GET_OTHER_TASK_ITEMS_PVT;
3715
3716 open c_task_csr(p_visit_task_id);
3717 fetch c_task_csr into l_task_rec;
3718 close c_task_csr;
3719
3720 -- Job in draft status
3721
3722 If p_job_status_code='17'
3723 then
3724
3725 -- Task Date can not be null
3726 IF (p_task_start_time IS NULL OR
3727 p_task_start_time = FND_API.G_MISS_DATE ) THEN
3728
3729 FND_MESSAGE.set_name( 'AHL','AHL_VWP_VALIDATE_ERROR' );
3730 FND_MSG_PUB.add;
3731
3732 IF G_DEBUG='Y' THEN
3733 Ahl_Debug_Pub.debug( l_full_name ||'Task Start Date is null');
3734 END IF;
3735
3736 RAISE FND_API.G_EXC_ERROR;
3737 END IF;
3738
3739 -- need to add notfound exception handling here
3740
3741 open c_mr_route_csr (l_task_rec.mr_route_id);
3742 fetch c_mr_route_csr into l_mr_route_rec;
3743 close c_mr_route_csr;
3744
3745 AHL_VWP_PRICE_PVT.check_item_for_rt_res_req
3746 (
3747 p_visit_task_id =>p_visit_task_id,
3748 p_route_id =>l_mr_route_Rec.route_id,
3749 p_organization_id =>l_task_rec.organization_id,
3750 p_effective_date =>p_task_start_time,
3751 x_item_tbl =>l_item_tbl_res,
3752 x_return_status =>l_return_status
3753 );
3754
3755
3756 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
3757 fnd_log.string
3758 (
3759 fnd_log.level_statement,
3760 'ahl.plsql.AHL_VWP_TASK_CST_PR_PVT.GET_OTHER_TASK_ITEMS',
3761 'Number of items for route resource requirements: ' || l_item_tbl_res.count
3762 );
3763
3764 END IF;
3765
3766 AHL_VWP_PRICE_PVT.Check_Item_for_Rt_Mat_Req
3767 (
3768 p_visit_task_id =>p_visit_task_id,
3769 p_route_id =>l_mr_route_rec.route_id,
3770 p_effective_date =>p_task_start_time,
3771 x_item_tbl =>l_item_tbl_mat,
3772 x_return_status =>l_return_status
3773 );
3774
3775
3776 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
3777 fnd_log.string
3778 (
3779 fnd_log.level_statement,
3780 'ahl.plsql.AHL_VWP_TASK_CST_PR_PVT.GET_OTHER_TASK_ITEMS',
3781 'Number of items for route material requirements: ' || l_item_tbl_mat.count
3782 );
3783
3784 END IF;
3785
3786 ahl_vwp_rules_pvt.merge_for_unique_items
3787 (
3788 p_item_tbl1 =>l_item_tbl_res,
3789 p_item_tbl2 =>l_item_tbl_mat,
3790 x_item_tbl =>l_item_tbl
3791 );
3792
3793
3794 Else
3795
3796 AHL_VWP_PRICE_PVT.check_item_for_prod_res_req
3797 (
3798 p_visit_task_id =>p_visit_task_id,
3799 x_item_tbl =>l_item_tbl_res,
3800 x_return_status =>l_return_status
3801 );
3802
3803 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
3804 fnd_log.string
3805 (
3806 fnd_log.level_statement,
3807 'ahl.plsql.AHL_VWP_TASK_CST_PR_PVT.GET_OTHER_TASK_ITEMS',
3808 'Number of items for production resource requirements: ' || l_item_tbl_res.count
3809 );
3810
3811 END IF;
3812
3813 AHL_VWP_PRICE_PVT.check_item_for_prod_mat_req
3814 (
3815 p_visit_task_id =>p_visit_task_id,
3816 x_item_tbl =>l_item_tbl_mat,
3817 x_return_status =>l_return_status
3818 );
3819
3820 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
3821 fnd_log.string
3822 (
3823 fnd_log.level_statement,
3824 'ahl.plsql.AHL_VWP_TASK_CST_PR_PVT.GET_OTHER_TASK_ITEMS',
3825 'Number of items for production material requirements: ' || l_item_tbl_mat.count
3826 );
3827
3828 END IF;
3829
3830 ahl_vwp_rules_pvt.merge_for_unique_items
3831 (
3832 p_item_tbl1 =>l_item_tbl_res,
3833 p_item_tbl2 =>l_item_tbl_mat,
3834 x_item_tbl =>l_item_tbl2
3835 );
3836
3837 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
3838 fnd_log.string
3839 (
3840 fnd_log.level_statement,
3841 'ahl.plsql.AHL_VWP_TASK_CST_PR_PVT.GET_OTHER_TASK_ITEMS',
3842 'Number of items for production material and resource requirements: ' || l_item_tbl.count
3843 );
3844
3845 END IF;
3846
3847 AHL_VWP_PRICE_PVT.Check_Item_for_Resource_Trans
3848 (
3849 p_visit_task_id =>p_visit_task_id,
3850 x_item_tbl =>l_item_tbl_res,
3851 x_return_status =>l_return_status
3852 );
3853
3854 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
3855 fnd_log.string
3856 (
3857 fnd_log.level_statement,
3858 'ahl.plsql.AHL_VWP_TASK_CST_PR_PVT.GET_OTHER_TASK_ITEMS',
3859 'Number of items for production resource transactions: ' || l_item_tbl_res.count
3860 );
3861
3862 END IF;
3863
3864 AHL_VWP_PRICE_PVT.Check_Item_for_Materials_Trans
3865 (
3866 p_visit_task_id =>p_visit_task_id,
3867 x_item_tbl =>l_item_tbl_mat,
3868 x_return_status =>l_return_status
3869 );
3870
3871 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
3872 fnd_log.string
3873 (
3874 fnd_log.level_statement,
3875 'ahl.plsql.AHL_VWP_TASK_CST_PR_PVT.GET_OTHER_TASK_ITEMS',
3876 'Number of items for production material transactions: ' || l_item_tbl_mat.count
3877 );
3878
3879 END IF;
3880
3881 ahl_vwp_rules_pvt.merge_for_unique_items
3882 (
3883 p_item_tbl1 =>l_item_tbl_res,
3884 p_item_tbl2 =>l_item_tbl_mat,
3885 x_item_tbl =>l_item_tbl1
3886 );
3887
3888 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
3889 fnd_log.string
3890 (
3891 fnd_log.level_statement,
3892 'ahl.plsql.AHL_VWP_TASK_CST_PR_PVT.GET_OTHER_TASK_ITEMS',
3893 'Number of items for production material and resource transactions: ' || l_item_tbl1.count
3894 );
3895
3896 END IF;
3897
3898
3899 ahl_vwp_rules_pvt.merge_for_unique_items
3900 (
3901 p_item_tbl1 =>l_item_tbl2,
3902 p_item_tbl2 =>l_item_tbl1,
3903 x_item_tbl =>l_item_tbl
3904 );
3905
3906
3907 End if;
3908
3909 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
3910
3911 fnd_log.string
3912 (
3913 fnd_log.level_statement,
3914 'ahl.plsql.AHL_VWP_TASK_CST_PR_PVT.GET_OTHER_TASK_ITEMS',
3915 'Number of items for all requirements and transactions: ' || l_item_tbl.count
3916 );
3917
3918 END IF;
3919
3920 -- Check Error Message stack.
3921 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
3922 l_msg_count := FND_MSG_PUB.count_msg;
3923 IF l_msg_count > 0 THEN
3924 RAISE FND_API.G_EXC_ERROR;
3925 END IF;
3926 END IF;
3927
3928 x_item_tbl:=l_item_tbl;
3929
3930 -- Standard check of p_commit
3931 IF FND_API.TO_BOOLEAN(p_commit) THEN
3932 COMMIT WORK;
3933 END IF;
3934
3935 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
3936 fnd_log.string
3937 (
3938 fnd_log.level_statement,
3939 'ahl.plsql.AHL_VWP_TASK_CST_PR_PVT.GET_OTHER_TASK_ITEMS.end',
3940 'At the end of PLSQL procedure'
3941 );
3942
3943 END IF;
3944
3945
3946 EXCEPTION
3947 WHEN FND_API.G_EXC_ERROR THEN
3948 x_return_status := FND_API.G_RET_STS_ERROR;
3949 ROLLBACK TO GET_OTHER_TASK_ITEMS_PVT;
3950 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
3951 p_data => x_msg_data,
3952 p_encoded => fnd_api.g_false);
3953
3954
3955 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3956 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3957 ROLLBACK TO GET_OTHER_TASK_ITEMS_PVT;
3958 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
3959 p_data => x_msg_data,
3960 p_encoded => fnd_api.g_false);
3961
3962
3963 WHEN OTHERS THEN
3964 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3965 ROLLBACK TO GET_OTHER_TASK_ITEMS_PVT;
3966 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
3967 fnd_msg_pub.add_exc_msg(p_pkg_name => G_PKG_NAME,
3968 p_procedure_name => L_API_NAME,
3969 p_error_text => SUBSTR(SQLERRM,1,500));
3970 END IF;
3971 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
3972 p_data => x_msg_data,
3973 p_encoded => fnd_api.g_false);
3974
3975 END GET_OTHER_TASK_ITEMS;
3976
3977
3978 END AHL_VWP_TASK_CST_PR_PVT;