[Home] [Help]
PACKAGE BODY: APPS.AHL_VWP_VISIT_CST_PR_PVT
Source
1 PACKAGE BODY AHL_VWP_VISIT_CST_PR_PVT AS
2 /* $Header: AHLVVCPB.pls 120.4 2007/12/18 09:59:26 sowsubra ship $ */
3
4 -- PACKAGE
5 -- Ahl_VWP_VISIT_CST_PR_PVT
6 --
7 -- PURPOSE
8 -- This package is a Private API to process Estimating Cost and Price
9 -- for a Visit 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_visit_cost_details -- update_visit_cost_details
21 -- estimate_visit_cost -- estimate_visit_price
22 -- create_price_snapshot -- create_cost_snapshot
23 -- get_visit_items_no_price -- check_for_release_visit
24
25 --
26 -- Package/App Name
27 G_PKG_NAME CONSTANT VARCHAR(30) := 'AHL_VWP_VISIT_CST_PR_PVT';
28 G_APP_NAME CONSTANT VARCHAR2(3) := 'AHL';
29
30 --------------------------------------------------------------------------
31 -- Procedure to estimate price for a specific SR --
32 --------------------------------------------------------------------------
33 PROCEDURE Estimate_SR_Price(
34 p_x_cost_price_rec IN OUT NOCOPY AHL_VWP_VISIT_CST_PR_PVT.cost_price_rec_type,
35 x_return_status OUT NOCOPY VARCHAR2);
36
37 --------------------------------------------------------------------------
38 -- Procedure to get visit cost details for a specific visit --
39 --------------------------------------------------------------------------
40 PROCEDURE get_visit_cost_details(
41 p_api_version IN NUMBER := 1.0,
42 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
43 p_commit IN VARCHAR2 := FND_API.G_FALSE,
44 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
45 p_module_type IN VARCHAR2 := 'JSP',
46 p_x_cost_price_rec IN OUT NOCOPY AHL_VWP_VISIT_CST_PR_PVT.cost_price_rec_type,
47 x_return_status OUT NOCOPY VARCHAR2,
48 x_msg_count OUT NOCOPY NUMBER,
49 x_msg_data OUT NOCOPY VARCHAR2) IS
50
51 -- Define Local Variables
52 L_API_VERSION CONSTANT NUMBER := 1.0;
53 L_API_NAME CONSTANT VARCHAR2(30) := 'get_visit_cost_details';
54 L_FULL_NAME CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || L_API_NAME;
55
56 l_cost_price_rec AHL_VWP_VISIT_CST_PR_PVT.Cost_Price_Rec_Type;
57 l_error_msg_code VARCHAR2(240);
58 l_exists VARCHAR2(1);
59
60 l_visit_status VARCHAR2(30);
61
62
63 -- Define Local Cursors
64 CURSOR visit_info_csr(p_visit_id IN NUMBER) IS
65 SELECT status_code, outside_party_flag, price_list_id, actual_price, estimated_price,
66 any_task_chg_flag, service_request_id, start_date_time, close_date_time
67 FROM ahl_visits_b
68 WHERE visit_id = p_visit_id;
69
70 CURSOR customer_id_csr(p_service_request_id IN NUMBER)IS
71 SELECT customer_id FROM cs_incidents_all_b
72 WHERE incident_id = p_service_request_id;
73
74 CURSOR visit_tasks_csr(p_visit_id IN NUMBER) IS
75 SELECT 'x' FROM ahl_visit_tasks_b
76 WHERE visit_id = p_visit_id
77 and nvl(status_code, 'x') <>'DELETED';
78
79 CURSOR price_list_name_csr(p_price_list_id IN NUMBER) IS
80 SELECT name from qp_list_headers_vl
81 WHERE list_header_id = p_price_list_id;
82
83 -- Begin Procedure code
84 BEGIN
85 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)THEN
86 fnd_log.string
87 (
88 fnd_log.level_procedure,
89 'ahl.plsql.AHL_VWP_VISIT_CST_PR_PVT.get_visit_cost_details.begin',
90 'At the start of PLSQL procedure'
91 );
92 END IF;
93
94 -- Standard start of API savepoint
95 SAVEPOINT get_visit_cost_details;
96
97 -- Initialize message list if p_init_msg_list is set to TRUE
98 IF FND_API.To_Boolean( p_init_msg_list) THEN
99 FND_MSG_PUB.Initialize;
100 END IF;
101
102 -- Initialize API return status to success
103 x_return_status := FND_API.G_RET_STS_SUCCESS;
104
105 -- Standard call to check for call compatibility.
106 IF NOT Fnd_Api.COMPATIBLE_API_CALL(l_api_version,
107 p_api_version,
108 l_api_name,G_PKG_NAME)
109 THEN
110 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
111 END IF;
112
113 l_cost_price_rec:= p_x_cost_price_rec;
114
115 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
116 fnd_log.string
117 (
118 fnd_log.level_statement,
119 'ahl.plsql.AHL_VWP_VISIT_CST_PR_PVT.get_visit_cost_details',
120 'Got request for cost record of Visit ID : ' || p_x_cost_price_rec.visit_id
121 );
122 fnd_log.string
123 (
124 fnd_log.level_statement,
125 'ahl.plsql.AHL_VWP_VISIT_CST_PR_PVT.get_visit_cost_details',
126 'Got request for update visit cost details of mr session ID : ' || p_x_cost_price_rec.mr_session_id
127 );
128 fnd_log.string
129 (
130 fnd_log.level_statement,
131 'ahl.plsql.AHL_VWP_VISIT_CST_PR_PVT.get_visit_cost_details',
132 'Got request for update visit cost details of cost session ID : ' || p_x_cost_price_rec.cost_session_id
133 );
134 END IF;
135
136 -- make sure that visit id is present in the input
137 IF(p_x_cost_price_rec.visit_id IS NULL OR p_x_cost_price_rec.visit_id = FND_API.G_MISS_NUM) THEN
138 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_VWP_CST_INPUT_MISS');
139 FND_MSG_PUB.ADD;
140 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)THEN
141 fnd_log.string
142 (
143 fnd_log.level_exception,
144 'ahl.plsql.AHL_VWP_VISIT_CST_PR_PVT.get_visit_cost_details',
145 'Visit id is mandatory but found null in input '
146 );
147 END IF;
148 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
149 END IF;
150
151 OPEN visit_info_csr(p_x_cost_price_rec.visit_id);
152 FETCH visit_info_csr INTO l_visit_status,
153 l_cost_price_rec.outside_party_flag,
154 l_cost_price_rec.price_list_id,
155 l_cost_price_rec.actual_price, l_cost_price_rec.estimated_price,
156 l_cost_price_rec.Is_Cst_Struc_updated, l_cost_price_rec.service_request_id,
157 l_cost_price_rec.visit_start_date, l_cost_price_rec.visit_end_date;
158
159 IF (visit_info_csr%NOTFOUND)THEN
160 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_VWP_CST_INV_VISIT');
161 FND_MSG_PUB.ADD;
162 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)THEN
163 fnd_log.string
164 (
165 fnd_log.level_exception,
166 'ahl.plsql.AHL_VWP_VISIT_CST_PR_PVT.get_visit_cost_details',
167 'Visit id not found in ahl_visits_b table'
168 );
169 END IF;
170 CLOSE visit_info_csr;
171 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
172 END IF;
173 CLOSE visit_info_csr;
174
175
176 -- outside party flag can be updated only when service request id is not null
177 IF(l_cost_price_rec.service_request_id IS NOT NULL)THEN
178
179 -- find out the customer id
180 OPEN customer_id_csr(l_cost_price_rec.service_request_id);
181 FETCH customer_id_csr INTO l_cost_price_rec.customer_id;
182 IF(customer_id_csr%NOTFOUND)THEN
183 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_VWP_CST_INV_SR_ID');
184 FND_MSG_PUB.ADD;
185 IF(fnd_log.level_unexpected >= fnd_log.g_current_runtime_level)THEN
186 fnd_log.string
187 (
188 fnd_log.level_unexpected,
189 'ahl.plsql.AHL_VWP_VISIT_CST_PR_PVT.get_visit_cost_details',
190 'Service request associated is invalid as record not found : l_cost_price_rec.service_request_id : '||l_cost_price_rec.service_request_id
191 );
192 END IF;
193 CLOSE customer_id_csr;
194 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
195 END IF;
196 CLOSE customer_id_csr;
197
198 -- can outside party flag be updated?
199 OPEN visit_tasks_csr(p_x_cost_price_rec.visit_id);
200 FETCH visit_tasks_csr INTO l_exists;
201 IF(visit_tasks_csr%NOTFOUND)THEN
202 l_cost_price_rec.is_outside_pty_flag_updt := 'Y';
203 ELSE
204 l_cost_price_rec.is_outside_pty_flag_updt := 'N';
205 END IF;
206 CLOSE visit_tasks_csr;
207
208 ELSE
209 l_cost_price_rec.is_outside_pty_flag_updt := 'N';
210 END IF;
211
212 -- To find out price list name
213 IF(l_cost_price_rec.price_list_id IS NOT NULL)THEN
214 OPEN price_list_name_csr(l_cost_price_rec.price_list_id);
215 FETCH price_list_name_csr INTO l_cost_price_rec.price_list_name;
216
217 IF(price_list_name_csr%NOTFOUND)THEN
218 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_VWP_CST_INV_VISIT');
219 FND_MSG_PUB.ADD;
220 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level)THEN
221 fnd_log.string
222 (
223 fnd_log.level_error,
224 'ahl.plsql.AHL_VWP_VISIT_CST_PR_PVT.get_visit_cost_details',
225 'List name not found for stored list id'
226 );
227 END IF;
228 CLOSE price_list_name_csr;
229 RAISE FND_API.G_EXC_ERROR;
230 END IF;
231
232 CLOSE price_list_name_csr;
233 END IF;
234
235 -- To find currency here
236 AHL_VWP_RULES_PVT.Check_Currency_for_Costing
237 (
238 p_visit_id =>l_cost_price_rec.visit_id,
239 x_currency_code => l_cost_price_rec.currency
240 );
241
242 -- Not to calculate cost if visit is in cancelled status
243 IF l_visit_status <>'CANCELLED' THEN
244
245 -- To call to get cost if calculated
246 AHL_VWP_COST_PVT.Calculate_WO_Cost
247 (
248 p_api_version => 1.0,
249 p_init_msg_list => Fnd_Api.G_FALSE,
250 p_commit=> Fnd_Api.G_FALSE,
251 p_validation_level => Fnd_Api.G_VALID_LEVEL_FULL,
252 p_x_cost_price_rec => l_cost_price_rec,
253 x_return_status => x_return_status
254 );
255
256 IF(x_return_status <> FND_API.G_RET_STS_SUCCESS)THEN
257 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)THEN
258 fnd_log.string
259 (
260 fnd_log.level_exception,
261 'ahl.plsql.AHL_VWP_VISIT_CST_PR_PVT.get_visit_cost_details',
262 'AHL_VWP_COST_PVT.Calculate_WO_Cost API threw error : x_return_status : ' || x_return_status
263 );
264 END IF;
265 RAISE FND_API.G_EXC_ERROR;
266 END IF;
267
268 -- To get visit cost calculated
269 IF(l_cost_price_rec.Is_Cst_Struc_updated = 'N') AND (l_cost_price_rec.workorder_id IS NOT NULL) THEN
270
271 AHL_VWP_COST_PVT.Calculate_Visit_Cost
272 (
273 p_visit_id => l_cost_price_rec.visit_id,
274 p_Session_id => l_cost_price_rec.mr_session_id,
275 x_Actual_cost => l_cost_price_rec.actual_cost,
276 x_Estimated_cost => l_cost_price_rec.estimated_cost,
277 x_return_status => x_return_status
278 );
279
280 IF(x_return_status <> FND_API.G_RET_STS_SUCCESS)THEN
281 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)THEN
282 fnd_log.string
283 (
284 fnd_log.level_exception,
285 'ahl.plsql.AHL_VWP_VISIT_CST_PR_PVT.get_visit_cost_details',
286 'AHL_VWP_COST_PVT.Calculate_Visit_Cost API threw error : x_return_status : ' || x_return_status
287 );
288 END IF;
289 RAISE FND_API.G_EXC_ERROR;
290 END IF;
291
292 -- To get profit and loss
293 AHL_VWP_COST_PVT.Get_Profit_or_Loss
294 (
295 p_actual_price => l_cost_price_rec.actual_price,
296 p_estimated_price => l_cost_price_rec.estimated_price,
297 p_actual_cost => l_cost_price_rec.actual_cost,
298 p_estimated_cost => l_cost_price_rec.estimated_cost,
299 x_actual_profit => l_cost_price_rec.actual_profit,
300 x_estimated_profit => l_cost_price_rec.estimated_profit,
301 x_return_status => x_return_status
302 );
303 END IF;
304
305 IF(x_return_status <> FND_API.G_RET_STS_SUCCESS)THEN
306 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)THEN
307 fnd_log.string
308 (
309 fnd_log.level_exception,
310 'ahl.plsql.AHL_VWP_VISIT_CST_PR_PVT.get_visit_cost_details',
311 'AHL_VWP_COST_PVT.Get_Profit_or_Loss API threw error : x_return_status : ' || x_return_status
312 );
313 END IF;
314 RAISE FND_API.G_EXC_ERROR;
315 END IF;
316 END IF; -- status <> CANCELLED
317
318 -- Check Error Message stack.
319 x_msg_count := FND_MSG_PUB.count_msg;
320 IF x_msg_count > 0 THEN
321 RAISE FND_API.G_EXC_ERROR;
322 END IF;
323
324 -- Standard check of p_commit
325 IF FND_API.TO_BOOLEAN(p_commit) THEN
326 COMMIT WORK;
327 END IF;
328
329 p_x_cost_price_rec.outside_party_flag := l_cost_price_rec.outside_party_flag;
330 p_x_cost_price_rec.price_list_id := l_cost_price_rec.price_list_id;
331 p_x_cost_price_rec.price_list_name := l_cost_price_rec.price_list_name;
332 p_x_cost_price_rec.currency := l_cost_price_rec.currency;
333
334 --Bug#4302163 fix
335 IF(l_cost_price_rec.Is_Cst_Struc_updated = 'N') THEN
336 p_x_cost_price_rec.actual_price := l_cost_price_rec.actual_price;
337 p_x_cost_price_rec.estimated_price := l_cost_price_rec.estimated_price;
338 ELSE
339 p_x_cost_price_rec.actual_price := null;
340 p_x_cost_price_rec.estimated_price := null;
341 END IF;
342
343 p_x_cost_price_rec.actual_cost := l_cost_price_rec.actual_cost;
344 p_x_cost_price_rec.estimated_cost := l_cost_price_rec.estimated_cost;
345 p_x_cost_price_rec.actual_profit := l_cost_price_rec.actual_profit;
346 p_x_cost_price_rec.estimated_profit := l_cost_price_rec.estimated_profit;
347 p_x_cost_price_rec.Is_Cst_Struc_updated := l_cost_price_rec.Is_Cst_Struc_updated;
348 p_x_cost_price_rec.service_request_id := l_cost_price_rec.service_request_id;
349 p_x_cost_price_rec.customer_id := l_cost_price_rec.customer_id;
350 p_x_cost_price_rec.is_outside_pty_flag_updt := l_cost_price_rec.is_outside_pty_flag_updt;
351 p_x_cost_price_rec.cost_session_id := l_cost_price_rec.cost_session_id;
352 p_x_cost_price_rec.mr_session_id := l_cost_price_rec.mr_session_id;
353 p_x_cost_price_rec.workorder_id := l_cost_price_rec.workorder_id;
354 p_x_cost_price_rec.visit_start_date := l_cost_price_rec.visit_start_date;
355 p_x_cost_price_rec.visit_end_date := l_cost_price_rec.visit_end_date;
356
357 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)THEN
358 fnd_log.string
359 (
360 fnd_log.level_procedure,
361 'ahl.plsql.AHL_VWP_VISIT_CST_PR_PVT.get_visit_cost_details.end',
362 'At the end of PLSQL procedure'
363 );
364 END IF;
365
366 EXCEPTION
367 WHEN FND_API.G_EXC_ERROR THEN
368 x_return_status := FND_API.G_RET_STS_ERROR;
369 ROLLBACK TO get_visit_cost_details;
370 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
371 p_data => x_msg_data,
372 p_encoded => fnd_api.g_false);
373
374
375 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
376 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
377 ROLLBACK TO get_visit_cost_details;
378 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
379 p_data => x_msg_data,
380 p_encoded => fnd_api.g_false);
381
382
383 WHEN OTHERS THEN
384 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
385 ROLLBACK TO get_visit_cost_details;
386 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
387 fnd_msg_pub.add_exc_msg(p_pkg_name => G_PKG_NAME,
388 p_procedure_name => 'get_visit_cost_details',
389 p_error_text => SUBSTR(SQLERRM,1,500));
390 END IF;
391 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
392 p_data => x_msg_data,
393 p_encoded => fnd_api.g_false);
394
395 END get_visit_cost_details;
396
397 --------------------------------------------------------------------------
398 -- Procedure to get visit cost details for a specific visit --
399 --------------------------------------------------------------------------
400 PROCEDURE update_visit_cost_details(
401 p_api_version IN NUMBER := 1.0,
402 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
403 p_commit IN VARCHAR2 := FND_API.G_FALSE,
404 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
405 p_module_type IN VARCHAR2 := 'JSP',
406 p_x_cost_price_rec IN OUT NOCOPY AHL_VWP_VISIT_CST_PR_PVT.cost_price_rec_type,
407 x_return_status OUT NOCOPY VARCHAR2,
408 x_msg_count OUT NOCOPY NUMBER,
409 x_msg_data OUT NOCOPY VARCHAR2)IS
410
411 -- Define local variables
412 L_API_VERSION CONSTANT NUMBER := 1.0;
413 L_API_NAME CONSTANT VARCHAR2(30) := 'update_visit_cost_details';
414 L_FULL_NAME CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || L_API_NAME;
415
416 l_cost_price_rec AHL_VWP_VISIT_CST_PR_PVT.cost_price_rec_type;
417 l_outside_party_flag VARCHAR2(1);
418 l_service_request_id NUMBER;
419 l_visit_status_code VARCHAR2(30);
420 l_start_date_time DATE;
421 l_close_date_time DATE;
422 l_exists VARCHAR2(1);
423 l_error_msg_code VARCHAR2(240);
424 l_valid_flag VARCHAR2(1);
425
426 -- Define local cursors
427 CURSOR visit_info_csr(p_visit_id IN NUMBER) IS
428 SELECT outside_party_flag, service_request_id,
429 status_code,start_date_time, close_date_time
430 FROM ahl_visits_b
431 WHERE visit_id = p_visit_id;
432
433 CURSOR visit_tasks_csr(p_visit_id IN NUMBER) IS
434 SELECT 'x' FROM ahl_visit_tasks_b
435 WHERE visit_id = p_visit_id
436 and nvl(status_code, 'x') <>'DELETED';
437
438 CURSOR price_list_dates_csr(p_price_list_id IN NUMBER)IS
439 SELECT start_date_active, end_date_active
440 FROM qp_list_headers_v
441 WHERE list_header_id = p_price_list_id;
442
443 l_price_list_active_start_date DATE;
444 l_price_list_active_end_date DATE;
445
446 CURSOR update_visit_csr(p_visit_id IN NUMBER)IS
447 SELECT * FROM ahl_visits_vl
448 WHERE visit_id = p_visit_id
449 FOR UPDATE OF object_version_number;
450 visit_rec update_visit_csr%ROWTYPE;
451
452 BEGIN
453 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)THEN
454 fnd_log.string
455 (
456 fnd_log.level_procedure,
457 'ahl.plsql.AHL_VWP_VISIT_CST_PR_PVT.update_visit_cost_details.begin',
458 'At the start of PLSQL procedure'
459 );
460 END IF;
461
462 -- Standard start of API savepoint
463 SAVEPOINT update_visit_cost_details;
464
465 -- Initialize message list if p_init_msg_list is set to TRUE
466 IF FND_API.To_Boolean( p_init_msg_list) THEN
467 FND_MSG_PUB.Initialize;
468 END IF;
469
470 -- Initialize API return status to success
471 x_return_status := FND_API.G_RET_STS_SUCCESS;
472 l_cost_price_rec := p_x_cost_price_rec;
473
474 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
475 fnd_log.string
476 (
477 fnd_log.level_statement,
478 'ahl.plsql.AHL_VWP_VISIT_CST_PR_PVT.update_visit_cost_details',
479 'Got request for update visit cost details of Visit ID : ' || p_x_cost_price_rec.visit_id
480 );
481 fnd_log.string
482 (
483 fnd_log.level_statement,
484 'ahl.plsql.AHL_VWP_VISIT_CST_PR_PVT.update_visit_cost_details',
485 'outside party flag : ' || p_x_cost_price_rec.outside_party_flag
486 );
487 fnd_log.string
488 (
489 fnd_log.level_statement,
490 'ahl.plsql.AHL_VWP_VISIT_CST_PR_PVT.update_visit_cost_details',
491 'Price List Name : ' || p_x_cost_price_rec.price_list_name
492 );
493 END IF;
494
495 -- Make sure that visit id is present in the input
496 IF(p_x_cost_price_rec.visit_id IS NULL OR p_x_cost_price_rec.visit_id = FND_API.G_MISS_NUM) THEN
497 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_VWP_CST_INPUT_MISS');
498 FND_MSG_PUB.ADD;
499 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)THEN
500 fnd_log.string
501 (
502 fnd_log.level_exception,
503 'ahl.plsql.AHL_VWP_VISIT_CST_PR_PVT.update_visit_cost_details',
504 'Visit id is mandatory but found null in input '
505 );
506 END IF;
507 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
508 END IF;
509
510 --make sure outside party flag is valid
511 IF (NVL(p_x_cost_price_rec.outside_party_flag,'N') NOT IN ('Y','N'))THEN
512 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_VWP_CST_INPUT_OSP_INV');
513 FND_MSG_PUB.ADD;
514 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)THEN
515 fnd_log.string
516 (
517 fnd_log.level_exception,
518 'ahl.plsql.AHL_VWP_VISIT_CST_PR_PVT.update_visit_cost_details',
519 'Input OSP Flag is invalid : ' || p_x_cost_price_rec.outside_party_flag
520 );
521 END IF;
522 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
523 END IF;
524
525 OPEN visit_info_csr(p_x_cost_price_rec.visit_id);
526 FETCH visit_info_csr INTO l_outside_party_flag,
527 l_service_request_id,
528 l_visit_status_code,
529 l_start_date_time,
530 l_close_date_time;
531
532 IF (visit_info_csr%NOTFOUND)THEN
533 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_VWP_CST_INV_VISIT');
534 FND_MSG_PUB.ADD;
535 CLOSE visit_info_csr;
536 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
537
538 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)THEN
539 fnd_log.string
540 (
541 fnd_log.level_exception,
542 'ahl.plsql.AHL_VWP_VISIT_CST_PR_PVT.update_visit_cost_details',
543 'Visit id not found in ahl_visits_b table'
544 );
545 END IF;
546
547 ELSIF (l_visit_status_code = 'CLOSED')THEN
548 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_VWP_CST_INV_VISIT_UPDT_STS');
549 FND_MSG_PUB.ADD;
550 CLOSE visit_info_csr;
551 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
552
553 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level)THEN
554 fnd_log.string
555 (
556 fnd_log.level_error,
557 'ahl.plsql.AHL_VWP_VISIT_CST_PR_PVT.update_visit_cost_details',
558 'Visit is closed so can not update outside party flag or price list'
559 );
560 END IF;
561
562 ELSIF (l_service_request_id IS NULL)THEN
563 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_VWP_CST_INV_VISIT_UPDT_NOSR');
564 FND_MSG_PUB.ADD;
565 CLOSE visit_info_csr;
566 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
567
568 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level)THEN
569 fnd_log.string
570 (
571 fnd_log.level_error,
572 'ahl.plsql.AHL_VWP_VISIT_CST_PR_PVT.update_visit_cost_details',
573 'No service request is attached to visit so can not update outside party flag or price list'
574 );
575 END IF;
576
577 ELSIF (NVL(l_cost_price_rec.outside_party_flag,'N') <> NVL(l_outside_party_flag,'N'))THEN
578 CLOSE visit_info_csr;
579
580 OPEN visit_tasks_csr(p_x_cost_price_rec.visit_id);
581 FETCH visit_tasks_csr INTO l_exists;
582
583 IF (visit_tasks_csr%FOUND)THEN
584 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_VWP_CST_INV_OSP_FLAG_MOD');
585 FND_MSG_PUB.ADD;
586 RAISE FND_API.G_EXC_ERROR;
587
588 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level)THEN
589 fnd_log.string
590 (
591 fnd_log.level_error,
592 'ahl.plsql.AHL_VWP_VISIT_CST_PR_PVT.update_visit_cost_details',
593 'Can not modify outside party flag because tasks has already been created'
594 );
595 END IF;
596
597 END IF;
598 CLOSE visit_tasks_csr;
599 ELSE
600 CLOSE visit_info_csr;
601 END IF;
602
603 IF (p_module_type = 'JSP') THEN
604 l_cost_price_rec.PRICE_LIST_ID := null;
605
606 IF (l_cost_price_rec.outside_party_flag = 'Y' AND l_cost_price_rec.price_list_name IS NULL)
607 THEN
608
609 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_VWP_CST_PRICE_LIST_MAND');
610 FND_MSG_PUB.ADD;
611 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level)THEN
612 fnd_log.string
613 (
614 fnd_log.level_error,
615 'ahl.plsql.AHL_VWP_VISIT_CST_PR_PVT.update_visit_cost_details',
616 'Price List is mandatory'
617 );
618 END IF;
619 RAISE FND_API.G_EXC_ERROR;
620
621 ELSIF (l_cost_price_rec.outside_party_flag = 'Y' AND l_cost_price_rec.price_list_name IS NOT NULL) THEN
622 AHL_VWP_RULES_PVT.Check_Price_List_Name_Or_Id
623 (
624 p_visit_id => l_cost_price_rec.visit_id,
625 p_price_list_name => l_cost_price_rec.price_list_name,
626 x_price_list_id => l_cost_price_rec.price_list_id,
627 x_return_status => x_return_status
628 );
629 IF(x_return_status <> FND_API.G_RET_STS_SUCCESS)THEN
630 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level)THEN
631 fnd_log.string
632 (
633 fnd_log.level_error,
634 'ahl.plsql.AHL_VWP_VISIT_CST_PR_PVT.update_visit_cost_details',
635 'AHL_VWP_RULES_PVT.Check_Price_List_Name_Or_Id API Threw error'
636 );
637 END IF;
638 RAISE FND_API.G_EXC_ERROR;
639 END IF;
640 ELSIF(l_cost_price_rec.outside_party_flag = 'N' AND l_cost_price_rec.price_list_name IS NOT NULL)THEN
641 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_VWP_OSP_VISIT_PR_LIST');
642 -- Please select check box 'Visit for outside party' or remove price list from LOV.
643 FND_MSG_PUB.ADD;
644 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level)THEN
645 fnd_log.string
646 (
647 fnd_log.level_error,
648 'ahl.plsql.AHL_VWP_VISIT_CST_PR_PVT.update_visit_cost_details',
649 'Price List is mandatory'
650 );
651 END IF;
652 RAISE FND_API.G_EXC_ERROR;
653 END IF;
654
655 END IF; -- Check p_module_type if 'JSP'
656
657 -- validate validity of price list here(whether active for life of visit)
658 -- fecth price list active start and end date
659 IF l_cost_price_rec.price_list_id IS NOT NULL THEN
660 OPEN price_list_dates_csr(l_cost_price_rec.price_list_id);
661 FETCH price_list_dates_csr INTO l_price_list_active_start_date, l_price_list_active_end_date;
662 CLOSE price_list_dates_csr; -- not found scenario is not possible at this step
663 END IF;
664
665 -- compare it with visit start and end dates
666 IF(l_price_list_active_start_date IS NOT NULL OR l_price_list_active_end_date IS NOT NULL)THEN
667
668 -- Check if the visit start date and visit planned end date if not null
669 IF (l_start_date_time IS NOT NULL OR l_close_date_time IS NOT NULL ) THEN
670
671 -- visit start date validation
672 IF (l_start_date_time IS NOT NULL)THEN
673
674 IF (TRUNC(l_price_list_active_start_date) > TRUNC(l_start_date_time)) OR
675 (TRUNC(l_price_list_active_end_date) < TRUNC(l_start_date_time)) THEN
676
677 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_VWP_CST_PRICE_LIST_INV_STR');
678 FND_MSG_PUB.ADD;
679 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level)THEN
680 fnd_log.string
681 (
682 fnd_log.level_error,
683 'ahl.plsql.AHL_VWP_VISIT_CST_PR_PVT.update_visit_cost_details',
684 'Price List is not active on visit start date'
685 );
686 END IF;
687
688 END IF;
689
690 END IF; -- End of visit start_date check
691
692 -- visit planned end date validation
693 IF (l_close_date_time IS NOT NULL)THEN
694
695 IF (TRUNC(l_price_list_active_start_date) > TRUNC(l_close_date_time)) OR
696 (TRUNC(l_price_list_active_end_date) < TRUNC(l_close_date_time)) THEN
697
698 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_VWP_CST_PRICE_LIST_INV_END');
699 FND_MSG_PUB.ADD;
700 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level)THEN
701 fnd_log.string
702 (
703 fnd_log.level_error,
704 'ahl.plsql.AHL_VWP_VISIT_CST_PR_PVT.update_visit_cost_details',
705 'Price List is not active on visit end date'
706 );
707 END IF;
708
709 END IF;
710
711 END IF; -- End of visit planned end date check
712
713 ELSE
714 -- Check if the visit start date and visit planned end date are null
715 -- then validate with current sysdate
716 IF (TRUNC(l_price_list_active_start_date) > TRUNC(sysdate)) OR
717 (TRUNC(l_price_list_active_end_date) < TRUNC(sysdate)) THEN
718
719 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_VWP_CST_PRICE_LIST_INV_SYS');
720 -- CHANGE THIS MESSAGE TEST AND NAME TOO -- IMPORTANT
721 FND_MSG_PUB.ADD;
722 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level)THEN
723 fnd_log.string
724 (
725 fnd_log.level_error,
726 'ahl.plsql.AHL_VWP_VISIT_CST_PR_PVT.update_visit_cost_details',
727 'Price List is not active on current todays date'
728 );
729 END IF;
730
731 END IF;
732
733 END IF; -- End of visit start_date and planned end date check
734
735 END IF; -- End of price_list active_start_date and active_end_date check
736
737 p_x_cost_price_rec.price_list_id := l_cost_price_rec.price_list_id;
738 p_x_cost_price_rec.outside_party_flag := NVL(l_cost_price_rec.outside_party_flag,'N');
739
740 -- update table.
741 OPEN update_visit_csr(p_x_cost_price_rec.visit_id);
742 FETCH update_visit_csr INTO visit_rec;
743 CLOSE update_visit_csr;--not found condition not possible at this step
744
745 -- Post 11.5.10
746 -- Added Priority and Project Template
747 -- Reema Start
748
749 AHL_VISITS_PKG.UPDATE_ROW
750 (
751 X_VISIT_ID => visit_rec.VISIT_ID,
752 X_VISIT_NUMBER => visit_rec.VISIT_NUMBER,
753 X_VISIT_TYPE_CODE => visit_rec.VISIT_TYPE_CODE,
754 X_SIMULATION_PLAN_ID => visit_rec.SIMULATION_PLAN_ID,
755 X_ITEM_INSTANCE_ID => visit_rec.ITEM_INSTANCE_ID,
756 X_ITEM_ORGANIZATION_ID => visit_rec.ITEM_ORGANIZATION_ID,
757 X_INVENTORY_ITEM_ID => visit_rec.INVENTORY_ITEM_ID,
758 X_ASSO_PRIMARY_VISIT_ID => visit_rec.ASSO_PRIMARY_VISIT_ID,
759 X_SIMULATION_DELETE_FLAG => visit_rec.SIMULATION_DELETE_FLAG,
760 X_TEMPLATE_FLAG => visit_rec.TEMPLATE_FLAG,
761 X_OUT_OF_SYNC_FLAG => visit_rec.OUT_OF_SYNC_FLAG,
762 X_PROJECT_FLAG => visit_rec.PROJECT_FLAG,
763 X_PROJECT_ID => visit_rec.PROJECT_ID,
764 X_SERVICE_REQUEST_ID => visit_rec.SERVICE_REQUEST_ID,
765 X_SPACE_CATEGORY_CODE => visit_rec.SPACE_CATEGORY_CODE,
766 X_SCHEDULE_DESIGNATOR => visit_rec.SCHEDULE_DESIGNATOR,
767 X_ATTRIBUTE_CATEGORY => visit_rec.ATTRIBUTE_CATEGORY,
768 X_ATTRIBUTE1 => visit_rec.ATTRIBUTE1,
769 X_ATTRIBUTE2 => visit_rec.ATTRIBUTE2,
770 X_ATTRIBUTE3 => visit_rec.ATTRIBUTE3,
771 X_ATTRIBUTE4 => visit_rec.ATTRIBUTE4,
772 X_ATTRIBUTE5 => visit_rec.ATTRIBUTE5,
773 X_ATTRIBUTE6 => visit_rec.ATTRIBUTE6,
774 X_ATTRIBUTE7 => visit_rec.ATTRIBUTE7,
775 X_ATTRIBUTE8 => visit_rec.ATTRIBUTE8,
776 X_ATTRIBUTE9 => visit_rec.ATTRIBUTE9,
777 X_ATTRIBUTE10 => visit_rec.ATTRIBUTE10,
778 X_ATTRIBUTE11 => visit_rec.ATTRIBUTE11,
779 X_ATTRIBUTE12 => visit_rec.ATTRIBUTE12,
780 X_ATTRIBUTE13 => visit_rec.ATTRIBUTE13,
781 X_ATTRIBUTE14 => visit_rec.ATTRIBUTE14,
782 X_ATTRIBUTE15 => visit_rec.ATTRIBUTE15,
783 X_OBJECT_VERSION_NUMBER => visit_rec.OBJECT_VERSION_NUMBER + 1,
784 X_ORGANIZATION_ID => visit_rec.ORGANIZATION_ID,
785 X_DEPARTMENT_ID => visit_rec.DEPARTMENT_ID,
786 X_STATUS_CODE => visit_rec.STATUS_CODE,
787 X_START_DATE_TIME => visit_rec.START_DATE_TIME,
788 X_close_date_time => visit_rec.close_date_time,
789 X_PRICE_LIST_ID => p_x_cost_price_rec.PRICE_LIST_ID,
790 X_ESTIMATED_PRICE => visit_rec.ESTIMATED_PRICE,
791 X_ACTUAL_PRICE => visit_rec.ACTUAL_PRICE,
792 X_OUTSIDE_PARTY_FLAG => p_x_cost_price_rec.OUTSIDE_PARTY_FLAG,
793 X_ANY_TASK_CHG_FLAG => visit_rec.ANY_TASK_CHG_FLAG,
794 X_VISIT_NAME => visit_rec.VISIT_NAME,
795 X_DESCRIPTION => visit_rec.DESCRIPTION,
796 X_LAST_UPDATE_DATE => SYSDATE,
797 X_LAST_UPDATED_BY => fnd_global.user_id,
798 X_LAST_UPDATE_LOGIN => fnd_global.login_id,
799 X_PRIORITY_CODE => visit_rec.PRIORITY_CODE,
800 X_PROJECT_TEMPLATE_ID => visit_rec.PROJECT_TEMPLATE_ID,
801 X_UNIT_SCHEDULE_ID => visit_rec.unit_schedule_id,
802 X_INV_LOCATOR_ID => visit_rec.INV_LOCATOR_ID --Added by sowsubra
803 );
804
805 -- Reema End
806
807 -- Check Error Message stack.
808 x_msg_count := FND_MSG_PUB.count_msg;
809 IF x_msg_count > 0 THEN
810 RAISE FND_API.G_EXC_ERROR;
811 END IF;
812
813 -- Standard check of p_commit
814 IF FND_API.TO_BOOLEAN(p_commit) THEN
815 COMMIT WORK;
816 END IF;
817
818 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)THEN
819 fnd_log.string
820 (
821 fnd_log.level_procedure,
822 'ahl.plsql.AHL_VWP_VISIT_CST_PR_PVT.update_visit_cost_details.end',
823 'At the end of PLSQL procedure'
824 );
825 END IF;
826
827 EXCEPTION
828 WHEN FND_API.G_EXC_ERROR THEN
829 x_return_status := FND_API.G_RET_STS_ERROR;
830 ROLLBACK TO update_visit_cost_details;
831 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
832 p_data => x_msg_data,
833 p_encoded => fnd_api.g_false);
834
835
836 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
837 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
838 ROLLBACK TO update_visit_cost_details;
839 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
840 p_data => x_msg_data,
841 p_encoded => fnd_api.g_false);
842
843
844 WHEN OTHERS THEN
845 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
846 ROLLBACK TO update_visit_cost_details;
847 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
848 fnd_msg_pub.add_exc_msg(p_pkg_name => G_PKG_NAME,
849 p_procedure_name => 'update_visit_cost_details',
850 p_error_text => SUBSTR(SQLERRM,1,500));
851 END IF;
852 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
853 p_data => x_msg_data,
854 p_encoded => fnd_api.g_false);
855 END update_visit_cost_details;
856
857 --------------------------------------------------------------------------
858 -- Procedure to estimate visit cost for a specific visit --
859 --------------------------------------------------------------------------
860 PROCEDURE estimate_visit_cost(
861 p_api_version IN NUMBER := 1.0,
862 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
863 p_commit IN VARCHAR2 := FND_API.G_FALSE,
864 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
865 p_module_type IN VARCHAR2 := 'JSP',
866 p_x_cost_price_rec IN OUT NOCOPY AHL_VWP_VISIT_CST_PR_PVT.cost_price_rec_type,
867 x_return_status OUT NOCOPY VARCHAR2,
868 x_msg_count OUT NOCOPY NUMBER,
869 x_msg_data OUT NOCOPY VARCHAR2)IS
870
871 -- Local Variables
872
873 -- Standard in/out parameters
874 l_api_name VARCHAR2(30) := 'ESTIMATE_VISIT_COST ';
875 l_api_version NUMBER := 1.0;
876 l_num_rec NUMBER;
877 l_msg_count NUMBER;
878 l_msg_data VARCHAR2(2000);
879 l_return_status VARCHAR2(1);
880 l_init_msg_list VARCHAR2(10):=FND_API.G_FALSE;
881 l_commit VARCHAR2(30) := Fnd_Api.G_FALSE;
882
883 l_release_visit_required VARCHAR2(1) :='N';
884
885 l_cost_price_rec AHL_VWP_VISIT_CST_PR_PVT.cost_price_rec_type;
886
887 BEGIN
888
889 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)THEN
890 fnd_log.string
891 (
892 fnd_log.level_procedure,
893 'ahl.plsql.AHL_VWP_VISIT_CST_PR_PVT.estimate_visit_cost.begin',
894 'At the start of PLSQL procedure'
895 );
896 END IF;
897
898 -- Standard start of API savepoint
899 SAVEPOINT estimate_visit_cost;
900
901 -- Initialize message list if p_init_msg_list is set to TRUE
902
903 IF FND_API.To_Boolean( p_init_msg_list) THEN
904 FND_MSG_PUB.Initialize;
905 END IF;
906
907 -- Initialize API return status to success
908 x_return_status := FND_API.G_RET_STS_SUCCESS;
909
910 -- Standard call to check for call compatibility.
911 IF NOT Fnd_Api.COMPATIBLE_API_CALL(l_api_version,
912 p_api_version,
913 l_api_name,G_PKG_NAME)
914 THEN
915 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
916 END IF;
917
918 l_cost_price_rec:= p_x_cost_price_rec;
919
920 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
921 fnd_log.string
922 (
923 fnd_log.level_statement,
924 'ahl.plsql.AHL_VWP_VISIT_CST_PR_PVT.estimate_visit_cost',
925 'Got request for estimating cost of Visit ID : ' || p_x_cost_price_rec.visit_id
926 );
927
928 fnd_log.string
929 (
930 fnd_log.level_statement,
931 'ahl.plsql.AHL_VWP_VISIT_CST_PR_PVT.estimate_visit_cost',
932 'input mr session id : ' || p_x_cost_price_rec.mr_session_id
933 );
934
935 fnd_log.string
936 (
937 fnd_log.level_statement,
938 'ahl.plsql.AHL_VWP_VISIT_CST_PR_PVT.estimate_visit_cost',
939 'input cost session id : ' || p_x_cost_price_rec.cost_session_id
940 );
941 END IF;
942
943 -- make sure that visit id is present in the input
944
945 IF(p_x_cost_price_rec.visit_id IS NULL OR p_x_cost_price_rec.visit_id = FND_API.G_MISS_NUM) THEN
946 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_VWP_CST_INPUT_MISS');
947 FND_MSG_PUB.ADD;
948
949 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)THEN
950 fnd_log.string
951 (
952 fnd_log.level_exception,
953 'ahl.plsql.AHL_VWP_VISIT_CST_PR_PVT.estimate_visit_cost',
954 'Visit id is mandatory but found null in input '
955 );
956 END IF;
957 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
958 END IF;
959
960 check_for_release_visit
961 (
962 p_visit_id =>p_x_cost_price_rec.visit_id,
963 x_release_visit_required =>l_release_visit_required
964 );
965
966 -- Release visit if required
967 IF l_release_visit_required ='Y' THEN
968
969 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
970 fnd_log.string
971 (
972 fnd_log.level_procedure,
973 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
974 'Before calling ahl vwp proj prod pvt.release visit'
975 );
976 END IF;
977
978 ahl_vwp_proj_prod_pvt.release_visit (
979 p_api_version => l_api_version,
980 p_init_msg_list => p_init_msg_list,
981 p_commit => l_commit,
982 p_validation_level => p_validation_level,
983 p_module_type => 'CST',
984 p_visit_id => l_cost_price_rec.visit_id,
985 x_return_status => l_return_status,
986 x_msg_count => l_msg_count,
987 x_msg_data => l_msg_data);
988
989 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
990 fnd_log.string
991 (
992 fnd_log.level_procedure,
993 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||': End API',
994 'After calling ahl vwp proj prod pvt.Release Visit task wo Return Status : '|| l_return_status
995 );
996 END IF;
997
998 -- Check Error Message stack.
999 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1000 l_msg_count := FND_MSG_PUB.count_msg;
1001 IF l_msg_count > 0 THEN
1002 RAISE FND_API.G_EXC_ERROR;
1003 END IF;
1004 END IF;
1005
1006 END IF; -- released required flag
1007
1008 -- call AHL_VWP_COST_PVT.calculate_visit_cost
1009
1010 AHL_VWP_COST_PVT.Estimate_WO_Cost
1011 (
1012 p_api_version => 1.0,
1013 p_init_msg_list => Fnd_Api.G_FALSE,
1014 p_commit=> Fnd_Api.G_FALSE,
1015 p_validation_level => Fnd_Api.G_VALID_LEVEL_FULL,
1016 p_x_cost_price_rec => l_cost_price_rec,
1017 x_return_status => x_return_status
1018 );
1019
1020 IF(x_return_status <> FND_API.G_RET_STS_SUCCESS)THEN
1021 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)THEN
1022 fnd_log.string
1023 (
1024 fnd_log.level_exception,
1025 'ahl.plsql.AHL_VWP_VISIT_CST_PR_PVT.estimate_visit_cost',
1026 'AHL_VWP_COST_PVT.Estimate_WO_Cost API threw error : x_return_status : ' || x_return_status
1027 );
1028 END IF;
1029 RAISE FND_API.G_EXC_ERROR;
1030 END IF;
1031
1032 -- Check Error Message stack.
1033 x_msg_count := FND_MSG_PUB.count_msg;
1034
1035 IF x_msg_count > 0 THEN
1036 RAISE FND_API.G_EXC_ERROR;
1037 END IF;
1038
1039 -- Standard check of p_commit
1040 IF FND_API.TO_BOOLEAN(p_commit) THEN
1041 COMMIT WORK;
1042 END IF;
1043
1044 p_x_cost_price_rec.Is_Cst_Struc_updated := l_cost_price_rec.Is_Cst_Struc_updated;
1045 p_x_cost_price_rec.cost_session_id := l_cost_price_rec.cost_session_id;
1046 p_x_cost_price_rec.mr_session_id := l_cost_price_rec.mr_session_id;
1047
1048 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)THEN
1049 fnd_log.string
1050 (
1051 fnd_log.level_procedure,
1052 'ahl.plsql.AHL_VWP_VISIT_CST_PR_PVT.estimate_visit_cost.end',
1053 'At the end of PLSQL procedure'
1054 );
1055 END IF;
1056
1057 EXCEPTION
1058
1059 WHEN FND_API.G_EXC_ERROR THEN
1060
1061 x_return_status := FND_API.G_RET_STS_ERROR;
1062 ROLLBACK TO estimate_visit_cost;
1063 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
1064 p_data => x_msg_data,
1065 p_encoded => fnd_api.g_false);
1066
1067 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1068 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1069 ROLLBACK TO estimate_visit_cost;
1070 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
1071 p_data => x_msg_data,
1072 p_encoded => fnd_api.g_false);
1073
1074 WHEN OTHERS THEN
1075 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1076 ROLLBACK TO estimate_visit_cost;
1077
1078 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1079 fnd_msg_pub.add_exc_msg(p_pkg_name => G_PKG_NAME,
1080 p_procedure_name => 'estimate_visit_cost',
1081 p_error_text => SUBSTR(SQLERRM,1,500));
1082 END IF;
1083
1084 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
1085 p_data => x_msg_data,
1086 p_encoded => fnd_api.g_false);
1087 END estimate_visit_cost;
1088
1089 --------------------------------------------------------------------------
1090 -- Procedure to estimate price for a specific SR --
1091 --bug fix #4181411
1092 -- yazhou 18-Feb-2005
1093 --------------------------------------------------------------------------
1094 PROCEDURE Estimate_SR_Price(
1095 p_x_cost_price_rec IN OUT NOCOPY AHL_VWP_VISIT_CST_PR_PVT.cost_price_rec_type,
1096 x_return_status OUT NOCOPY VARCHAR2)
1097 IS
1098
1099 -- Get all the root MR in the SR
1100
1101 CURSOR sr_summary_tasks_csr(p_task_id IN NUMBER)IS
1102 SELECT visit_task_id FROM ahl_visit_tasks_b VST
1103 WHERE VST.task_type_code = 'SUMMARY'
1104 AND VST.originating_task_id =p_task_id
1105 AND NVL(VST.status_code, 'Y') <> NVL ('DELETED', 'X')
1106 AND VST.mr_id IS NOT NULL;
1107
1108 -- Get other planned tasks in the SR
1109
1110 CURSOR sr_other_tasks_csr(p_task_id IN NUMBER)IS
1111 SELECT visit_task_id, start_date_time, end_date_time FROM ahl_visit_tasks_b VST
1112 WHERE VST.task_type_code = 'PLANNED'
1113 AND VST.originating_task_id =p_task_id
1114 AND NVL(VST.status_code, 'Y') <> NVL ('DELETED', 'X');
1115
1116 --Get min(start_time), max(end_time) for summary tasks
1117 CURSOR get_summary_task_times_csr(p_task_id IN NUMBER)IS
1118 SELECT min(start_date_time), max(end_date_time)
1119 FROM ahl_visit_tasks_b VST
1120 START WITH visit_task_id = p_task_id
1121 AND NVL(VST.status_code, 'Y') <> NVL ('DELETED', 'X')
1122 CONNECT BY originating_task_id = PRIOR visit_task_id;
1123
1124 l_cost_price_rec AHL_VWP_VISIT_CST_PR_PVT.cost_price_rec_type;
1125 l_temp_cost_price_rec AHL_VWP_VISIT_CST_PR_PVT.cost_price_rec_type;
1126
1127 l_actual_price NUMBER;
1128 l_estimated_price NUMBER;
1129
1130 l_msg_data VARCHAR2(2000);
1131 l_msg_count NUMBER;
1132
1133 BEGIN
1134 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)THEN
1135 fnd_log.string
1136 (
1137 fnd_log.level_procedure,
1138 'ahl.plsql.AHL_VWP_VISIT_CST_PR_PVT.Estimate_SR_Price.begin',
1139 'At the start of PLSQL procedure'
1140 );
1141 END IF;
1142
1143 -- Initialize API return status to success
1144 x_return_status := FND_API.G_RET_STS_SUCCESS;
1145
1146 l_cost_price_rec:= p_x_cost_price_rec;
1147
1148 -- Initialize Price variables
1149 l_cost_price_rec.actual_price := 0;
1150 l_cost_price_rec.estimated_price := 0;
1151 l_actual_price := 0;
1152 l_estimated_price := 0;
1153
1154 -- process summary tasks
1155 FOR sr_summary_tasks_rec IN sr_summary_tasks_csr(l_cost_price_rec.visit_task_id) LOOP
1156
1157
1158 OPEN get_summary_task_times_csr(sr_summary_tasks_rec.visit_task_id);
1159 FETCH get_summary_task_times_csr INTO l_cost_price_rec.Task_Start_Date,
1160 l_cost_price_rec.Task_END_Date;
1161 CLOSE get_summary_task_times_csr;
1162
1163 --initialize input
1164
1165 l_temp_cost_price_rec.visit_task_id := sr_summary_tasks_rec.visit_task_id;
1166
1167 l_temp_cost_price_rec.currency := l_cost_price_rec.currency;
1168
1169 l_temp_cost_price_rec.task_start_date := l_cost_price_rec.Task_Start_Date;
1170
1171 l_temp_cost_price_rec.customer_id := l_cost_price_rec.customer_id;
1172
1173 l_temp_cost_price_rec.actual_price := NULL;
1174
1175 l_temp_cost_price_rec.estimated_price := NULL;
1176
1177 l_temp_cost_price_rec.PRICE_LIST_ID:=l_cost_price_rec.price_list_id;
1178
1179 l_temp_cost_price_rec.Organization_Id:=l_cost_price_rec.organization_id;
1180
1181
1182 -- call api to estimate price for this summary task
1183
1184 AHL_VWP_MR_CST_PR_PVT.Estimate_MR_Price
1185 (
1186 p_api_version => 1.0,
1187 p_init_msg_list => Fnd_Api.g_false,
1188 p_commit => Fnd_Api.g_false,
1189 p_validation_level => Fnd_Api.g_valid_level_full,
1190 p_module_type => 'VST',
1191 p_x_cost_price_rec => l_temp_cost_price_rec,
1192 x_return_status => x_return_status,
1193 x_msg_count => l_msg_count,
1194 x_msg_data => l_msg_data
1195 );
1196
1197 IF(x_return_status <> FND_API.G_RET_STS_SUCCESS)THEN
1198
1199 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level)THEN
1200 fnd_log.string
1201 (
1202 fnd_log.level_error,
1203 'ahl.plsql.AHL_VWP_VISIT_CST_PR_PVT.Estimate_SR_Price',
1204 'AHL_VWP_MR_CST_PR_PVT.Estimate_MR_Price API Threw error'
1205 );
1206 END IF;
1207 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1208
1209 END IF;
1210
1211 IF(l_temp_cost_price_rec.actual_price IS NOT NULL) THEN
1212 l_actual_price := l_actual_price + l_temp_cost_price_rec.actual_price;
1213 END IF;
1214
1215 IF(l_temp_cost_price_rec.estimated_price IS NOT NULL) THEN
1216 l_estimated_price := l_estimated_price + l_temp_cost_price_rec.estimated_price;
1217 END IF;
1218
1219 END LOOP;
1220
1221
1222 -- process other tasks
1223
1224 FOR sr_other_tasks_rec IN sr_other_tasks_csr(l_cost_price_rec.visit_task_id) LOOP
1225
1226
1227 --bug fix #4181411
1228 -- yazhou 18-Feb-2005
1229
1230 l_cost_price_rec.Task_Start_Date := sr_other_tasks_rec.start_date_time;
1231 l_cost_price_rec.Task_End_Date := sr_other_tasks_rec.end_date_time;
1232
1233
1234 --initialize input
1235
1236 l_temp_cost_price_rec.visit_task_id := sr_other_tasks_rec.visit_task_id;
1237
1238 l_temp_cost_price_rec.currency := l_cost_price_rec.currency;
1239
1240 l_temp_cost_price_rec.task_start_date := l_cost_price_rec.Task_Start_Date;
1241
1242 l_temp_cost_price_rec.customer_id := l_cost_price_rec.customer_id;
1243
1244 l_temp_cost_price_rec.actual_price := NULL;
1245
1246 l_temp_cost_price_rec.estimated_price := NULL;
1247
1248 l_temp_cost_price_rec.PRICE_LIST_ID:=l_cost_price_rec.price_list_id;
1249
1250 l_temp_cost_price_rec.Organization_Id:=l_cost_price_rec.organization_id;
1251
1252
1253 -- call api to estimate price for this summary task
1254
1255 AHL_VWP_TASK_CST_PR_PVT.Estimate_Task_Price
1256 (
1257 p_api_version => 1.0,
1258 p_init_msg_list => Fnd_Api.g_false,
1259 p_commit => Fnd_Api.g_false,
1260 p_validation_level => Fnd_Api.g_valid_level_full,
1261 p_module_type => 'VST',
1262 p_x_cost_price_rec => l_temp_cost_price_rec,
1263 x_return_status => x_return_status,
1264 x_msg_count => l_msg_count,
1265 x_msg_data => l_msg_data
1266 );
1267
1268 IF(x_return_status <> FND_API.G_RET_STS_SUCCESS)THEN
1269
1270 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level)THEN
1271 fnd_log.string
1272 (
1273 fnd_log.level_error,
1274 'ahl.plsql.AHL_VWP_VISIT_CST_PR_PVT.estimate_SR_price',
1275 'AHL_VWP_TASK_CST_PR_PVT.Estimate_Task_Price API Threw error'
1276 );
1277 END IF;
1278 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1279 END IF;
1280
1281 IF(l_temp_cost_price_rec.actual_price IS NOT NULL) THEN
1282
1283 l_actual_price := l_actual_price + l_temp_cost_price_rec.actual_price;
1284
1285 END IF;
1286
1287 IF(l_temp_cost_price_rec.estimated_price IS NOT NULL) THEN
1288
1289 l_estimated_price := l_estimated_price + l_temp_cost_price_rec.estimated_price;
1290
1291 END IF;
1292
1293 END LOOP;
1294
1295
1296 --update task table with SR price
1297 Update AHL_VISIT_TASKS_B
1298 set actual_price=l_actual_price,
1299 estimated_price=l_estimated_price
1300 where visit_task_id=l_cost_price_rec.visit_task_id;
1301
1302
1303 -- assign output parameters
1304
1305 p_x_cost_price_rec.estimated_price:=l_estimated_price;
1306 p_x_cost_price_rec.actual_price:=l_actual_price;
1307
1308 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)THEN
1309 fnd_log.string
1310 (
1311 fnd_log.level_procedure,
1312 'ahl.plsql.AHL_VWP_VISIT_CST_PR_PVT.Estimate_SR_Price.end',
1313 'At the end of PLSQL procedure'
1314 );
1315 END IF;
1316 END Estimate_SR_Price;
1317
1318 --------------------------------------------------------------------------
1319 -- Procedure to estimate visit price for a specific visit --
1320 --------------------------------------------------------------------------
1321
1322 PROCEDURE estimate_visit_price(
1323 p_api_version IN NUMBER := 1.0,
1324 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1325 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1326 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1327 p_module_type IN VARCHAR2 := 'JSP',
1328 p_x_cost_price_rec IN OUT NOCOPY AHL_VWP_VISIT_CST_PR_PVT.cost_price_rec_type,
1329 x_return_status OUT NOCOPY VARCHAR2,
1330 x_msg_count OUT NOCOPY NUMBER,
1331 x_msg_data OUT NOCOPY VARCHAR2)
1332 IS
1333
1334 l_cost_price_rec AHL_VWP_VISIT_CST_PR_PVT.cost_price_rec_type;
1335 l_temp_cost_price_rec AHL_VWP_VISIT_CST_PR_PVT.cost_price_rec_type;
1336
1337
1338 l_job_status_code VARCHAR2(30);
1339 l_job_status_meaning VARCHAR2(80);
1340 l_actual_price NUMBER;
1341 l_estimated_price NUMBER;
1342 l_z number:=0;
1343 l_flag varchar2(1);
1344 l_visit_task_id AHL_VISIT_TASKS_B.visit_task_id%TYPE;
1345
1346 -- Standard in/out parameters
1347 l_api_name VARCHAR2(30) := 'ESTIMATE_VISIT_PRICE ';
1348 l_msg_data VARCHAR2(2000);
1349 l_api_version NUMBER := 1.0;
1350 l_num_rec NUMBER;
1351 l_msg_count NUMBER;
1352 l_return_status VARCHAR2(1);
1353 l_init_msg_list VARCHAR2(10):=FND_API.G_FALSE;
1354 l_commit VARCHAR2(30) := Fnd_Api.G_FALSE;
1355 --
1356 CURSOR visit_info_csr(p_visit_id IN NUMBER) IS
1357 Select VISIT_ID,
1358 PRICE_LIST_ID,
1359 SERVICE_REQUEST_ID,
1360 OUTSIDE_PARTY_FLAG,
1361 ORGANIZATION_ID,
1362 any_task_chg_flag
1363 From ahl_visits_b
1364 where visit_id=p_visit_id;
1365
1366 l_visit_rec visit_info_csr%rowtype;
1367
1368 CURSOR customer_id_csr(p_service_request_id IN NUMBER)IS
1369 SELECT customer_id FROM cs_incidents_all
1370 WHERE incident_id = p_service_request_id;
1371
1372 -- Get all the root MR in the visit
1373
1374 CURSOR summary_tasks_csr(p_visit_id IN NUMBER)IS
1375 SELECT visit_task_id FROM ahl_visit_tasks_b VST
1376 WHERE VST.visit_id = p_visit_id
1377 AND VST.task_type_code = 'SUMMARY'
1378 AND VST.originating_task_id IS NULL
1379 AND NVL(VST.status_code, 'Y') <> NVL ('DELETED', 'X')
1380 AND VST.mr_id IS NOT NULL;
1381
1382 --bug fix #4181411
1383 -- yazhou 18-Feb-2005
1384 -- Get all the root SR in the visit
1385
1386 CURSOR SR_tasks_csr(p_visit_id IN NUMBER)IS
1387 SELECT visit_task_id FROM ahl_visit_tasks_b VST
1388 WHERE VST.visit_id = p_visit_id
1389 AND VST.task_type_code = 'SUMMARY'
1390 AND VST.originating_task_id IS NULL
1391 AND NVL(VST.status_code, 'Y') <> NVL ('DELETED', 'X')
1392 AND VST.mr_id IS NULL
1393 AND VST.unit_effectivity_id IS NOT NULL;
1394
1395 --Get min(start_time), max(end_time) for summary tasks
1396 CURSOR get_summary_task_times_csr(p_task_id IN NUMBER)IS
1397 SELECT min(start_date_time), max(end_date_time)
1398 FROM ahl_visit_tasks_b VST
1399 START WITH visit_task_id = p_task_id
1400 AND NVL(VST.status_code, 'Y') <> NVL ('DELETED', 'X')
1401 CONNECT BY originating_task_id = PRIOR visit_task_id;
1402
1403 --bug fix #4181411
1404 -- yazhou 18-Feb-2005
1405 -- Get all the unassociated tasks
1406
1407 CURSOR other_tasks_csr(p_visit_id IN NUMBER)IS
1408 SELECT visit_task_id, start_date_time, end_date_time FROM ahl_visit_tasks_b VST
1409 WHERE VST.visit_id = p_visit_id
1410 AND NVL(VST.status_code, 'Y') <> NVL ('DELETED', 'X')
1411 AND VST.task_type_code = 'UNASSOCIATED';
1412
1413
1414 CURSOR update_visit_csr(p_visit_id IN NUMBER)IS
1415 SELECT * FROM ahl_visits_vl
1416 WHERE visit_id = p_visit_id
1417 FOR UPDATE OF object_version_number;
1418
1419 visit_rec update_visit_csr%ROWTYPE;
1420 l_error_msg_code VARCHAR2(240);
1421 l_valid_flag VARCHAR2(1);
1422
1423 BEGIN
1424 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)THEN
1425 fnd_log.string
1426 (
1427 fnd_log.level_procedure,
1428 'ahl.plsql.AHL_VWP_VISIT_CST_PR_PVT.estimate_visit_price.begin',
1429 'At the start of PLSQL procedure'
1430 );
1431 END IF;
1432
1433 -- Standard start of API savepoint
1434 SAVEPOINT estimate_visit_price;
1435
1436 -- Initialize message list if p_init_msg_list is set to TRUE
1437 IF FND_API.To_Boolean( p_init_msg_list) THEN
1438 FND_MSG_PUB.Initialize;
1439 END IF;
1440
1441 -- Initialize API return status to success
1442 x_return_status := FND_API.G_RET_STS_SUCCESS;
1443
1444 -- Standard call to check for call compatibility.
1445 IF NOT Fnd_Api.COMPATIBLE_API_CALL(l_api_version,
1446 p_api_version,
1447 l_api_name,G_PKG_NAME)
1448 THEN
1449 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
1450 END IF;
1451
1452 l_cost_price_rec:= p_x_cost_price_rec;
1453
1454 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
1455 fnd_log.string
1456 (
1457 fnd_log.level_statement,
1458 'ahl.plsql.AHL_VWP_VISIT_CST_PR_PVT.estimate_visit_price',
1459 'Got request for estimating of Visit ID : ' || p_x_cost_price_rec.visit_id
1460 );
1461 END IF;
1462
1463 -- make sure that visit id is present in the input
1464
1465 IF(p_x_cost_price_rec.visit_id IS NULL OR p_x_cost_price_rec.visit_id = FND_API.G_MISS_NUM) THEN
1466 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_VWP_CST_INPUT_MISS');
1467 FND_MSG_PUB.ADD;
1468 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)THEN
1469 fnd_log.string
1470 (
1471 fnd_log.level_exception,
1472 'ahl.plsql.AHL_VWP_VISIT_CST_PR_PVT.estimate_visit_price',
1473 'Visit id is mandatory but found null in input '
1474 );
1475 END IF;
1476 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1477 END IF;
1478
1479 -- get outside party flag
1480 OPEN visit_info_csr(p_x_cost_price_rec.visit_id);
1481 FETCH visit_info_csr INTO l_visit_rec;
1482
1483 IF(visit_info_csr%NOTFOUND)THEN
1484
1485 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_VWP_CST_INV_VISIT');
1486 FND_MSG_PUB.ADD;
1487
1488 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)THEN
1489 fnd_log.string
1490 (
1491 fnd_log.level_exception,
1492 'ahl.plsql.AHL_VWP_VISIT_CST_PR_PVT.estimate_visit_price',
1493 'visit info not found for input visit id'
1494 );
1495 END IF;
1496 CLOSE visit_info_csr;
1497 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1498 ELSE
1499 l_cost_price_rec.outside_party_flag := NVL(l_visit_rec.outside_party_flag,'N');
1500 END IF;
1501 CLOSE visit_info_csr;
1502
1503 -- price estimation valid or not
1504 IF(l_cost_price_rec.outside_party_flag <> 'Y')THEN
1505 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_VWP_CST_INV_PR_EST');
1506 FND_MSG_PUB.ADD;
1507
1508 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level)THEN
1509 fnd_log.string
1510 (
1511 fnd_log.level_error,
1512 'ahl.plsql.AHL_VWP_VISIT_CST_PR_PVT.estimate_visit_price',
1513 'Price estimation is restricted to outside party visit only '
1514 );
1515 END IF;
1516 RAISE FND_API.G_EXC_ERROR;
1517 END IF;
1518
1519 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level)THEN
1520 fnd_log.string
1521 (
1522 fnd_log.level_error,
1523 'ahl.plsql.AHL_VWP_VISIT_CST_PR_PVT.estimate_visit_price',
1524 'Before release_visit cursor'
1525 );
1526 END IF;
1527
1528 --Call estimate task cost
1529 Estimate_Visit_Cost (
1530 p_api_version => l_api_version,
1531 p_init_msg_list => p_init_msg_list,
1532 p_commit => l_commit,
1533 p_validation_level => p_validation_level,
1534 p_module_type => p_module_type,
1535
1536 p_x_cost_price_rec => l_cost_price_rec,
1537
1538 x_return_status => l_return_status,
1539 x_msg_count => l_msg_count,
1540 x_msg_data => l_msg_data);
1541
1542
1543 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
1544 fnd_log.string
1545 (
1546 fnd_log.level_procedure,
1547 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||': End API',
1548 'After calling ahl vwp task cost pvt.Estimate Task cost Return Status : '|| l_return_status
1549 );
1550 END IF;
1551
1552 -- Check Error Message stack.
1553 if (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1554 l_msg_count := FND_MSG_PUB.count_msg;
1555 IF l_msg_count > 0 THEN
1556 RAISE FND_API.G_EXC_ERROR;
1557 END IF;
1558 END IF;
1559
1560 --Assign the out variable
1561 p_x_cost_price_rec.cost_session_id := l_cost_price_rec.cost_session_id;
1562 p_x_cost_price_rec.mr_session_id := l_cost_price_rec.mr_session_id;
1563 p_x_cost_price_rec.Is_Cst_Struc_updated := l_cost_price_rec.Is_Cst_Struc_updated;
1564
1565
1566 --- Populate pricing attributes
1567
1568 -- find out customer id
1569 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level)THEN
1570 fnd_log.string
1571 (
1572 fnd_log.level_error,
1573 'ahl.plsql.AHL_VWP_VISIT_CST_PR_PVT.estimate_visit_price',
1574 'Before customer cursor'
1575 );
1576 END IF;
1577
1578 OPEN customer_id_csr(l_visit_rec.service_request_id);
1579 FETCH customer_id_csr INTO l_cost_price_rec.customer_id;
1580
1581 IF(customer_id_csr%NOTFOUND)THEN
1582 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_VWP_CST_INV_SRVREQ_NOCUST');
1583 FND_MSG_PUB.ADD;
1584
1585 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level)THEN
1586 fnd_log.string
1587 (
1588 fnd_log.level_error,
1589 'ahl.plsql.AHL_VWP_VISIT_CST_PR_PVT.estimate_visit_price',
1590 'Customer id not found for service request'
1591 );
1592 END IF;
1593 CLOSE customer_id_csr;
1594 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1595 END IF;
1596
1597 CLOSE customer_id_csr;
1598
1599 -- Populate currency code
1600 ahl_vwp_rules_pvt.check_currency_for_costing
1601 (
1602 p_visit_id => l_visit_rec.visit_id,
1603 x_currency_code => l_cost_price_rec.currency);
1604
1605 -- Check if currency value is null
1606 IF l_cost_price_rec.currency IS NULL THEN
1607 FND_MESSAGE.Set_Name(G_PKG_NAME,'AHL_VWP_CST_NO_CURRENCY');
1608 FND_MSG_PUB.ADD;
1609
1610 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level)THEN
1611 fnd_log.string
1612 (
1613 fnd_log.level_error,
1614 'ahl.plsql.'||G_PKG_NAME||'.'||L_API_NAME,
1615 'No curency is defined for the organization of the visit'
1616 );
1617 END IF;
1618 RAISE FND_API.G_EXC_ERROR;
1619 END IF;
1620
1621
1622 -- Get the visit price list if not passed
1623 IF (l_visit_rec.price_list_id IS NULL OR
1624 l_visit_rec.price_list_id = FND_API.G_MISS_NUM ) THEN
1625
1626 FND_MESSAGE.set_name( 'AHL','AHL_VWP_PRICE_LIST_INVALID' );
1627 FND_MSG_PUB.add;
1628
1629 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level)THEN
1630 fnd_log.string
1631 (
1632 fnd_log.level_error,
1633 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
1634 'Price list not found for Visit'
1635 );
1636 END IF;
1637 RAISE FND_API.G_EXC_ERROR;
1638 END IF;
1639
1640 l_cost_price_rec.PRICE_LIST_ID:=l_visit_rec.price_list_id;
1641 l_cost_price_rec.Organization_Id:=l_visit_rec.organization_id;
1642
1643
1644 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
1645
1646 fnd_log.string
1647 (
1648 fnd_log.level_statement,
1649 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
1650 'Customer Id : ' || l_cost_price_rec.customer_id
1651 );
1652
1653 fnd_log.string
1654 (
1655 fnd_log.level_statement,
1656 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
1657 'Visit Id : ' || l_cost_price_rec.visit_id
1658 );
1659
1660 fnd_log.string
1661 (
1662 fnd_log.level_statement,
1663 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
1664 'Currency : ' || l_cost_price_rec.currency
1665 );
1666
1667 fnd_log.string
1668 (
1669 fnd_log.level_statement,
1670 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
1671 'Price List Id : ' || l_cost_price_rec.PRICE_LIST_ID
1672 );
1673
1674 fnd_log.string
1675 (
1676 fnd_log.level_statement,
1677 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
1678 'Organization ID : ' || l_cost_price_rec.Organization_Id
1679 );
1680
1681 END IF;
1682
1683
1684
1685 -- Initialize Price variables
1686 l_cost_price_rec.actual_price := 0;
1687 l_cost_price_rec.estimated_price := 0;
1688 l_actual_price := 0;
1689 l_estimated_price := 0;
1690
1691
1692 --- Check job status
1693
1694 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
1695 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1696 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name||': ',
1697 'before check job status');
1698 END IF;
1699
1700 AHL_VWP_RULES_PVT.Check_Job_Status
1701 (
1702 p_id => l_cost_price_rec.visit_id,
1703 p_is_task_flag => 'N',
1704 x_status_code => l_job_status_code,
1705 x_status_meaning => l_job_status_meaning
1706 );
1707
1708 IF (l_job_status_code is NULL) THEN
1709 l_msg_count := FND_MSG_PUB.count_msg;
1710 IF l_msg_count > 0 THEN
1711 RAISE FND_API.G_EXC_ERROR;
1712 END IF;
1713 END IF;
1714
1715
1716 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level)THEN
1717 fnd_log.string
1718 (
1719 fnd_log.level_error,
1720 'ahl.plsql.AHL_VWP_VISIT_CST_PR_PVT.estimate_visit_price',
1721 'Before Summary TAsk cursor '
1722 );
1723 END IF;
1724
1725 -- process summary tasks
1726 FOR summary_tasks_rec IN summary_tasks_csr(l_visit_rec.visit_id) LOOP
1727
1728
1729 l_visit_task_id := summary_tasks_rec.visit_task_id;
1730 OPEN get_summary_task_times_csr(l_visit_task_id);
1731 FETCH get_summary_task_times_csr INTO l_cost_price_rec.Task_Start_Date,
1732 l_cost_price_rec.Task_END_Date;
1733 CLOSE get_summary_task_times_csr;
1734
1735 --initialize input
1736
1737 l_temp_cost_price_rec.visit_task_id := summary_tasks_rec.visit_task_id;
1738
1739 l_temp_cost_price_rec.currency := l_cost_price_rec.currency;
1740
1741 l_temp_cost_price_rec.task_start_date := l_cost_price_rec.Task_Start_Date;
1742
1743 l_temp_cost_price_rec.customer_id := l_cost_price_rec.customer_id;
1744
1745 l_temp_cost_price_rec.actual_price := NULL;
1746
1747 l_temp_cost_price_rec.estimated_price := NULL;
1748
1749 l_temp_cost_price_rec.PRICE_LIST_ID:=l_cost_price_rec.price_list_id;
1750
1751 l_temp_cost_price_rec.Organization_Id:=l_cost_price_rec.organization_id;
1752
1753
1754 -- call api to estimate price for this summary task
1755
1756 AHL_VWP_MR_CST_PR_PVT.Estimate_MR_Price
1757 (
1758 p_api_version => 1.0,
1759 p_init_msg_list => Fnd_Api.g_false,
1760 p_commit => Fnd_Api.g_false,
1761 p_validation_level => Fnd_Api.g_valid_level_full,
1762 p_module_type => 'VST',
1763 p_x_cost_price_rec => l_temp_cost_price_rec,
1764 x_return_status => x_return_status,
1765 x_msg_count => x_msg_count,
1766 x_msg_data => x_msg_data
1767 );
1768
1769 IF(x_return_status <> FND_API.G_RET_STS_SUCCESS)THEN
1770
1771 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level)THEN
1772 fnd_log.string
1773 (
1774 fnd_log.level_error,
1775 'ahl.plsql.AHL_VWP_VISIT_CST_PR_PVT.estimate_visit_price',
1776 'AHL_VWP_MR_CST_PR_PVT.Estimate_MR_Price API Threw error'
1777 );
1778 END IF;
1779 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1780
1781 END IF;
1782
1783 IF(l_temp_cost_price_rec.actual_price IS NOT NULL) THEN
1784 l_actual_price := l_actual_price + l_temp_cost_price_rec.actual_price;
1785 END IF;
1786
1787 IF(l_temp_cost_price_rec.estimated_price IS NOT NULL) THEN
1788 l_estimated_price := l_estimated_price + l_temp_cost_price_rec.estimated_price;
1789 END IF;
1790
1791 END LOOP;
1792
1793 --bug fix #4181411
1794 -- yazhou 18-Feb-2005
1795
1796 -- process SR tasks
1797 FOR SR_tasks_rec IN SR_tasks_csr(l_visit_rec.visit_id) LOOP
1798
1799 --initialize input
1800
1801 l_temp_cost_price_rec.visit_task_id := SR_tasks_rec.visit_task_id;
1802
1803 l_temp_cost_price_rec.currency := l_cost_price_rec.currency;
1804
1805 -- l_temp_cost_price_rec.task_start_date := l_cost_price_rec.Task_Start_Date;
1806
1807 l_temp_cost_price_rec.customer_id := l_cost_price_rec.customer_id;
1808
1809 l_temp_cost_price_rec.actual_price := NULL;
1810
1811 l_temp_cost_price_rec.estimated_price := NULL;
1812
1813 l_temp_cost_price_rec.PRICE_LIST_ID:=l_cost_price_rec.price_list_id;
1814
1815 l_temp_cost_price_rec.Organization_Id:=l_cost_price_rec.organization_id;
1816
1817
1818 -- call api to estimate price for this SR
1819
1820 Estimate_SR_Price
1821 (
1822 p_x_cost_price_rec => l_temp_cost_price_rec,
1823 x_return_status => x_return_status
1824 );
1825
1826 IF(x_return_status <> FND_API.G_RET_STS_SUCCESS)THEN
1827
1828 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level)THEN
1829 fnd_log.string
1830 (
1831 fnd_log.level_error,
1832 'ahl.plsql.AHL_VWP_VISIT_CST_PR_PVT.estimate_visit_price',
1833 'Estimate_SR_Price API Threw error'
1834 );
1835 END IF;
1836 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1837
1838 END IF;
1839
1840 IF(l_temp_cost_price_rec.actual_price IS NOT NULL) THEN
1841 l_actual_price := l_actual_price + l_temp_cost_price_rec.actual_price;
1842 END IF;
1843
1844 IF(l_temp_cost_price_rec.estimated_price IS NOT NULL) THEN
1845 l_estimated_price := l_estimated_price + l_temp_cost_price_rec.estimated_price;
1846 END IF;
1847
1848 END LOOP;
1849
1850
1851 -- process other tasks
1852
1853 FOR other_tasks_rec IN other_tasks_csr(p_x_cost_price_rec.visit_id) LOOP
1854
1855
1856 --bug fix #4181411
1857 -- yazhou 18-Feb-2005
1858
1859 l_cost_price_rec.Task_Start_Date := other_tasks_rec.start_date_time;
1860 l_cost_price_rec.Task_End_Date := other_tasks_rec.end_date_time;
1861
1862
1863 --initialize input
1864
1865 l_temp_cost_price_rec.visit_task_id := other_tasks_rec.visit_task_id;
1866
1867 l_temp_cost_price_rec.currency := l_cost_price_rec.currency;
1868
1869 l_temp_cost_price_rec.task_start_date := l_cost_price_rec.Task_Start_Date;
1870
1871 l_temp_cost_price_rec.customer_id := l_cost_price_rec.customer_id;
1872
1873 l_temp_cost_price_rec.actual_price := NULL;
1874
1875 l_temp_cost_price_rec.estimated_price := NULL;
1876
1877 l_temp_cost_price_rec.PRICE_LIST_ID:=l_cost_price_rec.price_list_id;
1878
1879 l_temp_cost_price_rec.Organization_Id:=l_cost_price_rec.organization_id;
1880
1881
1882 -- call api to estimate price for this summary task
1883
1884 AHL_VWP_TASK_CST_PR_PVT.Estimate_Task_Price
1885 (
1886 p_api_version => 1.0,
1887 p_init_msg_list => Fnd_Api.g_false,
1888 p_commit => Fnd_Api.g_false,
1889 p_validation_level => Fnd_Api.g_valid_level_full,
1890 p_module_type => 'VST',
1891 p_x_cost_price_rec => l_temp_cost_price_rec,
1892 x_return_status => x_return_status,
1893 x_msg_count => x_msg_count,
1894 x_msg_data => x_msg_data
1895 );
1896
1897 IF(x_return_status <> FND_API.G_RET_STS_SUCCESS)THEN
1898
1899 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level)THEN
1900 fnd_log.string
1901 (
1902 fnd_log.level_error,
1903 'ahl.plsql.AHL_VWP_VISIT_CST_PR_PVT.estimate_visit_price',
1904 'AHL_VWP_TASK_CST_PR_PVT.Estimate_Task_Price API Threw error'
1905 );
1906 END IF;
1907 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1908 END IF;
1909
1910 IF(l_temp_cost_price_rec.actual_price IS NOT NULL) THEN
1911
1912 l_actual_price := l_actual_price + l_temp_cost_price_rec.actual_price;
1913
1914 END IF;
1915
1916 IF(l_temp_cost_price_rec.estimated_price IS NOT NULL) THEN
1917
1918 l_estimated_price := l_estimated_price + l_temp_cost_price_rec.estimated_price;
1919
1920 END IF;
1921
1922 END LOOP;
1923
1924 --update the latest price info.
1925
1926 OPEN update_visit_csr(p_x_cost_price_rec.visit_id);
1927 FETCH update_visit_csr INTO visit_rec;
1928 CLOSE update_visit_csr;--not found condition not possible at this step
1929
1930 -- Post 11.5.10
1931 -- Added Priority and Project template
1932 -- Reema Start
1933
1934 AHL_VISITS_PKG.UPDATE_ROW
1935 (
1936 X_VISIT_ID => visit_rec.VISIT_ID,
1937
1938 X_VISIT_NUMBER => visit_rec.VISIT_NUMBER,
1939
1940 X_VISIT_TYPE_CODE => visit_rec.VISIT_TYPE_CODE,
1941
1942 X_SIMULATION_PLAN_ID => visit_rec.SIMULATION_PLAN_ID,
1943
1944 X_ITEM_INSTANCE_ID => visit_rec.ITEM_INSTANCE_ID,
1945
1946 X_ITEM_ORGANIZATION_ID => visit_rec.ITEM_ORGANIZATION_ID,
1947
1948 X_INVENTORY_ITEM_ID => visit_rec.INVENTORY_ITEM_ID,
1949
1950 X_ASSO_PRIMARY_VISIT_ID => visit_rec.ASSO_PRIMARY_VISIT_ID,
1951
1952 X_SIMULATION_DELETE_FLAG => visit_rec.SIMULATION_DELETE_FLAG,
1953
1954 X_TEMPLATE_FLAG => visit_rec.TEMPLATE_FLAG,
1955
1956 X_OUT_OF_SYNC_FLAG => visit_rec.OUT_OF_SYNC_FLAG,
1957
1958 X_PROJECT_FLAG => visit_rec.PROJECT_FLAG,
1959
1960 X_PROJECT_ID => visit_rec.PROJECT_ID,
1961
1962 X_SERVICE_REQUEST_ID => visit_rec.SERVICE_REQUEST_ID,
1963
1964 X_SPACE_CATEGORY_CODE => visit_rec.SPACE_CATEGORY_CODE,
1965
1966 X_SCHEDULE_DESIGNATOR => visit_rec.SCHEDULE_DESIGNATOR,
1967
1968 X_ATTRIBUTE_CATEGORY => visit_rec.ATTRIBUTE_CATEGORY,
1969
1970 X_ATTRIBUTE1 => visit_rec.ATTRIBUTE1,
1971
1972 X_ATTRIBUTE2 => visit_rec.ATTRIBUTE2,
1973
1974 X_ATTRIBUTE3 => visit_rec.ATTRIBUTE3,
1975
1976 X_ATTRIBUTE4 => visit_rec.ATTRIBUTE4,
1977
1978 X_ATTRIBUTE5 => visit_rec.ATTRIBUTE5,
1979
1980 X_ATTRIBUTE6 => visit_rec.ATTRIBUTE6,
1981
1982 X_ATTRIBUTE7 => visit_rec.ATTRIBUTE7,
1983
1984 X_ATTRIBUTE8 => visit_rec.ATTRIBUTE8,
1985
1986 X_ATTRIBUTE9 => visit_rec.ATTRIBUTE9,
1987
1988 X_ATTRIBUTE10 => visit_rec.ATTRIBUTE10,
1989
1990 X_ATTRIBUTE11 => visit_rec.ATTRIBUTE11,
1991
1992 X_ATTRIBUTE12 => visit_rec.ATTRIBUTE12,
1993
1994 X_ATTRIBUTE13 => visit_rec.ATTRIBUTE13,
1995
1996 X_ATTRIBUTE14 => visit_rec.ATTRIBUTE14,
1997
1998 X_ATTRIBUTE15 => visit_rec.ATTRIBUTE15,
1999
2000 X_OBJECT_VERSION_NUMBER => visit_rec.OBJECT_VERSION_NUMBER + 1,
2001
2002 X_ORGANIZATION_ID => visit_rec.ORGANIZATION_ID,
2003
2004 X_DEPARTMENT_ID => visit_rec.DEPARTMENT_ID,
2005
2006 X_STATUS_CODE => visit_rec.STATUS_CODE,
2007
2008 X_START_DATE_TIME => visit_rec.START_DATE_TIME,
2009
2010 X_close_date_time => visit_rec.close_date_time,
2011
2012 X_PRICE_LIST_ID => visit_rec.PRICE_LIST_ID,
2013
2014 X_ESTIMATED_PRICE => l_estimated_price,
2015
2016 X_ACTUAL_PRICE => l_actual_price,
2017
2018 X_OUTSIDE_PARTY_FLAG => visit_rec.OUTSIDE_PARTY_FLAG,
2019
2020 X_ANY_TASK_CHG_FLAG => visit_rec.ANY_TASK_CHG_FLAG,
2021
2022 X_VISIT_NAME => visit_rec.VISIT_NAME,
2023
2024 X_DESCRIPTION => visit_rec.DESCRIPTION,
2025
2026 X_LAST_UPDATE_DATE => SYSDATE,
2027
2028 X_LAST_UPDATED_BY => fnd_global.user_id,
2029
2030 X_LAST_UPDATE_LOGIN => fnd_global.login_id,
2031
2032 X_PRIORITY_CODE => visit_rec.PRIORITY_CODE,
2033 X_PROJECT_TEMPLATE_ID => visit_rec.PROJECT_TEMPLATE_ID,
2034 X_UNIT_SCHEDULE_ID => visit_rec.unit_schedule_id,
2035 X_INV_LOCATOR_ID => visit_rec.INV_LOCATOR_ID --Added by sowsubra
2036 );
2037
2038 -- Reema End
2039
2040 p_x_cost_price_rec.actual_price := l_actual_price;
2041
2042 p_x_cost_price_rec.estimated_price := l_estimated_price;
2043
2044
2045 -- Check Error Message stack.
2046 x_msg_count := FND_MSG_PUB.count_msg;
2047
2048 IF x_msg_count > 0 THEN
2049
2050 RAISE FND_API.G_EXC_ERROR;
2051
2052 END IF;
2053
2054
2055
2056 -- Standard check of p_commit
2057
2058 IF FND_API.TO_BOOLEAN(p_commit) THEN
2059 COMMIT WORK;
2060 END IF;
2061
2062 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)THEN
2063 fnd_log.string
2064 (
2065 fnd_log.level_procedure,
2066 'ahl.plsql.AHL_VWP_VISIT_CST_PR_PVT.estimate_visit_price.end',
2067 'At the end of PLSQL procedure'
2068 );
2069 END IF;
2070
2071 EXCEPTION
2072
2073 WHEN FND_API.G_EXC_ERROR THEN
2074
2075 x_return_status := FND_API.G_RET_STS_ERROR;
2076
2077 ROLLBACK TO estimate_visit_price;
2078
2079 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
2080
2081 p_data => x_msg_data,
2082
2083 p_encoded => fnd_api.g_false);
2084
2085
2086 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2087
2088 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2089
2090 ROLLBACK TO estimate_visit_price;
2091
2092 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
2093
2094 p_data => x_msg_data,
2095
2096 p_encoded => fnd_api.g_false);
2097
2098 WHEN OTHERS THEN
2099
2100 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2101
2102 ROLLBACK TO estimate_visit_price;
2103
2104 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2105
2106 fnd_msg_pub.add_exc_msg(p_pkg_name => G_PKG_NAME,
2107
2108 p_procedure_name => 'estimate_visit_price',
2109
2110 p_error_text => SUBSTR(SQLERRM,1,500));
2111
2112 END IF;
2113
2114 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
2115
2116 p_data => x_msg_data,
2117
2118 p_encoded => fnd_api.g_false);
2119
2120 END estimate_visit_price;
2121
2122 --------------------------------------------------------------------------
2123 -- Procedure to take a price snapshot for a specific visit --
2124 --------------------------------------------------------------------------
2125 PROCEDURE create_price_snapshot(
2126 p_api_version IN NUMBER := 1.0,
2127 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2128 p_commit IN VARCHAR2 := FND_API.G_FALSE,
2129 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
2130 p_module_type IN VARCHAR2 := 'JSP',
2131 p_visit_id IN NUMBER,
2132 x_return_status OUT NOCOPY VARCHAR2,
2133 x_msg_count OUT NOCOPY NUMBER,
2134 x_msg_data OUT NOCOPY VARCHAR2)IS
2135
2136
2137 CURSOR other_tasks_snapshot_csr(p_visit_id IN NUMBER) IS
2138
2139 SELECT VT.visit_task_id, VT.mr_id,VT.estimated_price,VT.actual_price,VT.visit_task_number, V.visit_number
2140 FROM ahl_visit_tasks_b VT, ahl_visits_b V
2141 WHERE VT.VISIT_ID = V.VISIT_ID
2142 AND NOT (task_type_code = 'SUMMARY' AND mr_id IS NULL)
2143 AND V.visit_id = p_visit_id
2144 AND nvl(VT.status_code,'x') <> 'DELETED'
2145 and VT.visit_task_id not in (
2146 select VST.visit_task_id
2147 from ahl_visit_tasks_b VST,
2148 AHL_MR_HEADERS_APP_V mr
2149 where vst.mr_id = mr.mr_header_id
2150 and vst.visit_id =p_visit_id
2151 AND nvl(VST.status_code,'x') <> 'DELETED'
2152 and mr.billing_item_id is not null
2153 and vst.task_type_code <>'SUMMARY');
2154
2155 l_snapshot_id NUMBER;
2156 l_snapshot_number NUMBER;
2157 L_API_VERSION CONSTANT NUMBER := 1.0;
2158 L_API_NAME CONSTANT VARCHAR2(30) := 'create_price_snapshot';
2159 L_FULL_NAME CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || L_API_NAME;
2160
2161 BEGIN
2162
2163 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)THEN
2164 fnd_log.string
2165 (
2166 fnd_log.level_procedure,
2167 'ahl.plsql.AHL_VWP_VISIT_CST_PR_PVT.create_price_snapshot.begin',
2168 'At the start of PLSQL procedure'
2169 );
2170 END IF;
2171
2172 -- Standard start of API savepoint
2173
2174 SAVEPOINT create_price_snapshot;
2175
2176 -- Initialize message list if p_init_msg_list is set to TRUE
2177
2178 IF FND_API.To_Boolean( p_init_msg_list) THEN
2179 FND_MSG_PUB.Initialize;
2180 END IF;
2181
2182 -- Initialize API return status to success
2183
2184 x_return_status := FND_API.G_RET_STS_SUCCESS;
2185
2186 -- Standard call to check for call compatibility.
2187 IF NOT Fnd_Api.COMPATIBLE_API_CALL(l_api_version,
2188 p_api_version,
2189 l_api_name,G_PKG_NAME)
2190 THEN
2191 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
2192 END IF;
2193
2194 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
2195 fnd_log.string
2196 (
2197 fnd_log.level_statement,
2198 'ahl.plsql.AHL_VWP_VISIT_CST_PR_PVT.create_price_snapshot',
2199 'Got request for tasking a price snapshot of Visit ID : ' || p_visit_id
2200 );
2201 END IF;
2202
2203 -- make sure that visit id is present in the input
2204
2205 IF(p_visit_id IS NULL OR p_visit_id = FND_API.G_MISS_NUM) THEN
2206
2207 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_VWP_CST_INPUT_MISS');
2208 FND_MSG_PUB.ADD;
2209
2210 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)THEN
2211 fnd_log.string
2212 (
2213 fnd_log.level_exception,
2214 'ahl.plsql.AHL_VWP_VISIT_CST_PR_PVT.create_price_snapshot',
2215 'Visit id is mandatory but found null in input '
2216 );
2217 END IF;
2218 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2219
2220 END IF;
2221
2222 -- get a snapshot number for ahl_snapshot_number_s for the whole set
2223 SELECT AHL_SNAPSHOTS_S.NEXTVAL INTO l_snapshot_number FROM DUAL;
2224
2225 FOR other_tasks_rec IN other_tasks_snapshot_csr(p_visit_id) LOOP
2226
2227 IF(other_tasks_rec.actual_price IS NULL OR other_tasks_rec.estimated_price IS NULL)THEN
2228
2229 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_VWP_CST_SNAP_PRC_MISS');
2230 FND_MESSAGE.Set_Token('VISIT_TASK_NUMBER',other_tasks_rec.visit_task_number);
2231 FND_MESSAGE.Set_Token('VISIT_NUMBER',other_tasks_rec.visit_number);
2232 FND_MSG_PUB.ADD;
2233
2234 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level)THEN
2235 fnd_log.string
2236 (
2237 fnd_log.level_error,
2238 'ahl.plsql.AHL_VWP_VISIT_CST_PR_PVT.create_price_snapshot',
2239 'Price not found for task id : ' || other_tasks_rec.visit_task_id
2240 );
2241 END IF;
2242 EXIT;
2243
2244 ELSE
2245
2246 --get sequence
2247 SELECT AHL_SNAPSHOTS_S.NEXTVAL INTO l_snapshot_id FROM DUAL;
2248
2249 -- take snapshot now
2250 AHL_SNAPSHOTS_PKG.INSERT_ROW
2251 (
2252 X_SNAPSHOT_ID => l_snapshot_id,
2253
2254 X_OBJECT_VERSION_NUMBER => 1,
2255
2256 X_SNAPSHOT_NUMBER => l_snapshot_number,
2257
2258 X_LAST_UPDATE_DATE => SYSDATE,
2259
2260 X_LAST_UPDATED_BY => fnd_global.user_id,
2261
2262 X_CREATION_DATE => SYSDATE,
2263
2264 X_CREATED_BY => fnd_global.user_id,
2265
2266 X_LAST_UPDATE_LOGIN => fnd_global.login_id,
2267
2268 X_VISIT_ID => p_visit_id,
2269
2270 X_VISIT_TASK_ID => other_tasks_rec.visit_task_id,
2271
2272 X_MR_ID => other_tasks_rec.mr_id,
2273
2274 X_ESTIMATED_PRICE => other_tasks_rec.estimated_price,
2275
2276 X_ACTUAL_PRICE => other_tasks_rec.actual_price,
2277
2278 X_ESTIMATED_COST => null,
2279
2280 X_ACTUAL_COST => null,
2281
2282 X_ATTRIBUTE_CATEGORY => null,
2283
2284 X_ATTRIBUTE1 => null,
2285
2286 X_ATTRIBUTE2 => null,
2287
2288 X_ATTRIBUTE3 => null,
2289
2290 X_ATTRIBUTE4 => null,
2291
2292 X_ATTRIBUTE5 => null,
2293
2294 X_ATTRIBUTE6 => null,
2295
2296 X_ATTRIBUTE7 => null,
2297
2298 X_ATTRIBUTE8 => null,
2299
2300 X_ATTRIBUTE9 => null,
2301
2302 X_ATTRIBUTE10 => null,
2303
2304 X_ATTRIBUTE11 => null,
2305
2306 X_ATTRIBUTE12 => null,
2307
2308 X_ATTRIBUTE13 => null,
2309
2310 X_ATTRIBUTE14 => null,
2311
2312 X_ATTRIBUTE15 => null
2313
2314 );
2315
2316 END IF;
2317
2318 END LOOP;
2319
2320
2321
2322 -- Check Error Message stack.
2323 x_msg_count := FND_MSG_PUB.count_msg;
2324 IF x_msg_count > 0 THEN
2325 RAISE FND_API.G_EXC_ERROR;
2326 END IF;
2327
2328 -- Standard check of p_commit
2329 IF FND_API.TO_BOOLEAN(p_commit) THEN
2330 COMMIT WORK;
2331 END IF;
2332
2333 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)THEN
2334 fnd_log.string
2335 (
2336 fnd_log.level_procedure,
2337 'ahl.plsql.AHL_VWP_VISIT_CST_PR_PVT.create_price_snapshot.end',
2338 'At the end of PLSQL procedure'
2339 );
2340 END IF;
2341
2342 EXCEPTION
2343
2344 WHEN FND_API.G_EXC_ERROR THEN
2345
2346 x_return_status := FND_API.G_RET_STS_ERROR;
2347
2348 ROLLBACK TO create_price_snapshot;
2349
2350 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
2351
2352 p_data => x_msg_data,
2353
2354 p_encoded => fnd_api.g_false);
2355
2356 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2357
2358 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2359
2360 ROLLBACK TO create_price_snapshot;
2361
2362 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
2363
2364 p_data => x_msg_data,
2365
2366 p_encoded => fnd_api.g_false);
2367
2368 WHEN OTHERS THEN
2369
2370 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2371
2372 ROLLBACK TO create_price_snapshot;
2373
2374 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2375
2376 fnd_msg_pub.add_exc_msg(p_pkg_name => G_PKG_NAME,
2377
2378 p_procedure_name => 'create_price_snapshot',
2379
2380 p_error_text => SUBSTR(SQLERRM,1,500));
2381
2382 END IF;
2383
2384 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
2385
2386 p_data => x_msg_data,
2387
2388 p_encoded => fnd_api.g_false);
2389
2390 END create_price_snapshot;
2391
2392 --------------------------------------------------------------------------
2393 -- Procedure to take a cost snapshot for a specific visit --
2394 --------------------------------------------------------------------------
2395 PROCEDURE create_cost_snapshot(
2396 p_api_version IN NUMBER := 1.0,
2397 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2398 p_commit IN VARCHAR2 := FND_API.G_FALSE,
2399 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
2400 p_module_type IN VARCHAR2 := 'JSP',
2401 p_x_cost_price_rec IN OUT NOCOPY AHL_VWP_VISIT_CST_PR_PVT.cost_price_rec_type,
2402 x_return_status OUT NOCOPY VARCHAR2,
2403 x_msg_count OUT NOCOPY NUMBER,
2404 x_msg_data OUT NOCOPY VARCHAR2)IS
2405
2406 l_cost_price_rec AHL_VWP_VISIT_CST_PR_PVT.cost_price_rec_type;
2407
2408 CURSOR summary_tasks_snapshot_csr(p_visit_id IN NUMBER) IS
2409 SELECT visit_task_id, mr_id FROM ahl_visit_tasks_vl
2410 WHERE mr_id IS NULL
2411 AND task_type_code = 'SUMMARY'
2412 AND visit_id = p_visit_id
2413 AND nvl(status_code,'x') <> 'DELETED';
2414
2415 CURSOR other_tasks_snapshot_csr(p_visit_id IN NUMBER) IS
2416 SELECT visit_task_id, mr_id FROM ahl_visit_tasks_vl
2417 WHERE visit_id = p_visit_id
2418 AND nvl(status_code,'x') <> 'DELETED'
2419 AND NOT (task_type_code = 'SUMMARY' AND mr_id IS NULL);
2420
2421
2422
2423 CURSOR workorder_csr(p_visit_task_id IN NUMBER)IS
2424 -- SELECT workorder_id, wip_entity_id FROM ahl_all_workorders_v
2425 -- WHERE visit_task_id = p_visit_task_id;
2426 -- Changed for changing the perf bug# 4919518
2427 SELECT workorder_id,wip_entity_id
2428 FROM ahl_workorders
2429 WHERE visit_task_id = p_visit_task_id
2430 AND STATUS_CODE <> '22';
2431
2432 l_workorder_id NUMBER;
2433 l_wip_entity_id NUMBER;
2434 l_actual_cost NUMBER;
2435 l_estimated_cost NUMBER;
2436 l_snapshot_id NUMBER;
2437 l_snapshot_number NUMBER;
2438
2439 L_API_VERSION CONSTANT NUMBER := 1.0;
2440 L_API_NAME CONSTANT VARCHAR2(30) := 'create_cost_snapshot';
2441 L_FULL_NAME CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || L_API_NAME;
2442
2443 BEGIN
2444 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)THEN
2445 fnd_log.string
2446 (
2447 fnd_log.level_procedure,
2448 'ahl.plsql.AHL_VWP_VISIT_CST_PR_PVT.create_cost_snapshot.begin',
2449 'At the start of PLSQL procedure'
2450 );
2451 END IF;
2452
2453 -- Standard start of API savepoint
2454 SAVEPOINT create_cost_snapshot;
2455
2456 -- Initialize message list if p_init_msg_list is set to TRUE
2457 IF FND_API.To_Boolean( p_init_msg_list) THEN
2458 FND_MSG_PUB.Initialize;
2459 END IF;
2460
2461 -- Initialize API return status to success
2462 x_return_status := FND_API.G_RET_STS_SUCCESS;
2463
2464 -- Standard call to check for call compatibility.
2465 IF NOT Fnd_Api.COMPATIBLE_API_CALL(l_api_version,
2466 p_api_version,
2467 l_api_name,G_PKG_NAME)
2468 THEN
2469 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
2470 END IF;
2471
2472 l_cost_price_rec := p_x_cost_price_rec;
2473
2474 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
2475 fnd_log.string
2476 (
2477 fnd_log.level_statement,
2478 'ahl.plsql.AHL_VWP_VISIT_CST_PR_PVT.create_cost_snapshot',
2479 'Got request for tasking a cost snapshot of Visit ID : ' || p_x_cost_price_rec.visit_id
2480 );
2481 END IF;
2482
2483 -- make sure that visit id is present in the input
2484 IF(p_x_cost_price_rec.visit_id IS NULL OR p_x_cost_price_rec.visit_id = FND_API.G_MISS_NUM) THEN
2485 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_VWP_CST_INPUT_MISS');
2486 FND_MSG_PUB.ADD;
2487 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)THEN
2488 fnd_log.string
2489 (
2490 fnd_log.level_exception,
2491 'ahl.plsql.AHL_VWP_VISIT_CST_PR_PVT.create_cost_snapshot',
2492 'Visit id is mandatory but found null in input '
2493 );
2494 END IF;
2495 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2496 END IF;
2497
2498 -- get a snapshot number for ahl_snapshot_number_s for the whole set
2499 SELECT AHL_SNAPSHOTS_S.NEXTVAL INTO l_snapshot_number FROM DUAL;
2500
2501 -- call calculate workorder cost
2502 AHL_VWP_COST_PVT.Estimate_WO_Cost
2503 (
2504 p_api_version => 1.0,
2505 p_init_msg_list => Fnd_Api.G_FALSE,
2506 p_commit=> Fnd_Api.G_FALSE,
2507 p_validation_level => Fnd_Api.G_VALID_LEVEL_FULL,
2508 p_x_cost_price_rec => l_cost_price_rec,
2509 x_return_status => x_return_status
2510 );
2511
2512 IF(x_return_status <> FND_API.G_RET_STS_SUCCESS)THEN
2513 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)THEN
2514 fnd_log.string
2515 (
2516 fnd_log.level_exception,
2517 'ahl.plsql.AHL_VWP_VISIT_CST_PR_PVT.create_cost_snapshot',
2518 'AHL_VWP_COST_PVT.Estimate_WO_Cost API threw error : x_return_status : ' || x_return_status
2519 );
2520 END IF;
2521 RAISE FND_API.G_EXC_ERROR;
2522 END IF;
2523
2524 -- take a snapshot of manually created summary tasks
2525 FOR summary_tasks_rec IN summary_tasks_snapshot_csr(p_x_cost_price_rec.visit_id) LOOP
2526 OPEN workorder_csr(summary_tasks_rec.visit_task_id);
2527 FETCH workorder_csr INTO l_workorder_id, l_wip_entity_id;
2528
2529 IF(workorder_csr%FOUND)THEN
2530 AHL_VWP_COST_PVT.Calculate_Task_Cost
2531 (
2532 p_visit_task_id => summary_tasks_rec.visit_task_id,
2533 p_session_id => p_x_cost_price_rec.mr_session_id,
2534 x_Actual_cost => l_actual_cost,
2535 x_Estimated_cost => l_estimated_cost,
2536 x_return_status => x_return_status
2537 );
2538
2539 IF(x_return_status <> FND_API.G_RET_STS_SUCCESS)THEN
2540 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_VWP_CST_CALC_TCOST_ERR');
2541 FND_MSG_PUB.ADD;
2542 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level)THEN
2543 fnd_log.string
2544 (
2545 fnd_log.level_error,
2546 'ahl.plsql.AHL_VWP_VISIT_CST_PR_PVT.create_cost_snapshot',
2547 'AHL_VWP_COST_PVT.Calculate_Task_Cost API threw error :summary: x_return_status : ' || x_return_status
2548 );
2549 END IF;
2550 CLOSE workorder_csr;
2551 EXIT;
2552 END IF;
2553
2554 --get sequence
2555 SELECT AHL_SNAPSHOTS_S.NEXTVAL INTO l_snapshot_id FROM DUAL;
2556
2557 -- take snapshot now
2558 AHL_SNAPSHOTS_PKG.INSERT_ROW
2559 (
2560 X_SNAPSHOT_ID => l_snapshot_id,
2561 X_OBJECT_VERSION_NUMBER => 1,
2562 X_SNAPSHOT_NUMBER => l_snapshot_number,
2563 X_LAST_UPDATE_DATE => SYSDATE,
2564 X_LAST_UPDATED_BY => fnd_global.user_id,
2565 X_CREATION_DATE => SYSDATE,
2566 X_CREATED_BY => fnd_global.user_id,
2567 X_LAST_UPDATE_LOGIN => fnd_global.login_id,
2568 X_VISIT_ID => p_x_cost_price_rec.visit_id,
2569 X_VISIT_TASK_ID => summary_tasks_rec.visit_task_id,
2570 X_MR_ID => summary_tasks_rec.mr_id,
2571 X_ESTIMATED_PRICE => null,
2572 X_ACTUAL_PRICE => null,
2573 X_ESTIMATED_COST => l_estimated_cost,
2574 X_ACTUAL_COST => l_actual_cost,
2575 X_ATTRIBUTE_CATEGORY => null,
2576 X_ATTRIBUTE1 => null,
2577 X_ATTRIBUTE2 => null,
2578 X_ATTRIBUTE3 => null,
2579 X_ATTRIBUTE4 => null,
2580 X_ATTRIBUTE5 => null,
2581 X_ATTRIBUTE6 => null,
2582 X_ATTRIBUTE7 => null,
2583 X_ATTRIBUTE8 => null,
2584 X_ATTRIBUTE9 => null,
2585 X_ATTRIBUTE10 => null,
2586 X_ATTRIBUTE11 => null,
2587 X_ATTRIBUTE12 => null,
2588 X_ATTRIBUTE13 => null,
2589 X_ATTRIBUTE14 => null,
2590 X_ATTRIBUTE15 => null
2591 );
2592 END IF;
2593 CLOSE workorder_csr;
2594 END LOOP;
2595
2596 -- take a snapshot of all other tasks
2597 FOR other_tasks_rec IN other_tasks_snapshot_csr(p_x_cost_price_rec.visit_id) LOOP
2598 AHL_VWP_COST_PVT.Calculate_Task_Cost
2599 (
2600 p_visit_task_id => other_tasks_rec.visit_task_id,
2601 p_session_id => p_x_cost_price_rec.mr_session_id,
2602 x_Actual_cost => l_actual_cost,
2603 x_Estimated_cost => l_estimated_cost,
2604 x_return_status => x_return_status
2605 );
2606
2607 IF(x_return_status <> FND_API.G_RET_STS_SUCCESS)THEN
2608 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_VWP_CST_CALC_TCOST_ERR');
2609 FND_MSG_PUB.ADD;
2610 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level)THEN
2611 fnd_log.string
2612 (
2613 fnd_log.level_error,
2614 'ahl.plsql.AHL_VWP_VISIT_CST_PR_PVT.create_cost_snapshot',
2615 'AHL_VWP_COST_PVT.Calculate_Task_Cost API threw error :other: x_return_status : ' || x_return_status
2616 );
2617 END IF;
2618 EXIT;
2619 END IF;
2620
2621 --get sequence
2622 SELECT AHL_SNAPSHOTS_S.NEXTVAL INTO l_snapshot_id FROM DUAL;
2623
2624 -- take snapshot now
2625 AHL_SNAPSHOTS_PKG.INSERT_ROW
2626 (
2627 X_SNAPSHOT_ID => l_snapshot_id,
2628 X_OBJECT_VERSION_NUMBER => 1,
2629 X_SNAPSHOT_NUMBER => l_snapshot_number,
2630 X_LAST_UPDATE_DATE => SYSDATE,
2631 X_LAST_UPDATED_BY => fnd_global.user_id,
2632 X_CREATION_DATE => SYSDATE,
2633 X_CREATED_BY => fnd_global.user_id,
2634 X_LAST_UPDATE_LOGIN => fnd_global.login_id,
2635 X_VISIT_ID => p_x_cost_price_rec.visit_id,
2636 X_VISIT_TASK_ID => other_tasks_rec.visit_task_id,
2637 X_MR_ID => other_tasks_rec.mr_id,
2638 X_ESTIMATED_PRICE => null,
2639 X_ACTUAL_PRICE => null,
2640 X_ESTIMATED_COST => l_estimated_cost,
2641 X_ACTUAL_COST => l_actual_cost,
2642 X_ATTRIBUTE_CATEGORY => null,
2643 X_ATTRIBUTE1 => null,
2644 X_ATTRIBUTE2 => null,
2645 X_ATTRIBUTE3 => null,
2646 X_ATTRIBUTE4 => null,
2647 X_ATTRIBUTE5 => null,
2648 X_ATTRIBUTE6 => null,
2649 X_ATTRIBUTE7 => null,
2650 X_ATTRIBUTE8 => null,
2651 X_ATTRIBUTE9 => null,
2652 X_ATTRIBUTE10 => null,
2653 X_ATTRIBUTE11 => null,
2654 X_ATTRIBUTE12 => null,
2655 X_ATTRIBUTE13 => null,
2656 X_ATTRIBUTE14 => null,
2657 X_ATTRIBUTE15 => null
2658 );
2659 END LOOP;
2660
2661 -- Check Error Message stack.
2662 x_msg_count := FND_MSG_PUB.count_msg;
2663 IF x_msg_count > 0 THEN
2664 RAISE FND_API.G_EXC_ERROR;
2665 END IF;
2666
2667 -- Standard check of p_commit
2668 IF FND_API.TO_BOOLEAN(p_commit) THEN
2669 COMMIT WORK;
2670 END IF;
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_VISIT_CST_PR_PVT.create_cost_snapshot.end',
2677 'At the end of PLSQL procedure'
2678 );
2679 END IF;
2680 EXCEPTION
2681 WHEN FND_API.G_EXC_ERROR THEN
2682 x_return_status := FND_API.G_RET_STS_ERROR;
2683 ROLLBACK TO create_cost_snapshot;
2684 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
2685 p_data => x_msg_data,
2686 p_encoded => fnd_api.g_false);
2687
2688
2689 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2690 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2691 ROLLBACK TO create_cost_snapshot;
2692 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
2693 p_data => x_msg_data,
2694 p_encoded => fnd_api.g_false);
2695
2696
2697 WHEN OTHERS THEN
2698 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2699 ROLLBACK TO create_cost_snapshot;
2700 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2701 fnd_msg_pub.add_exc_msg(p_pkg_name => G_PKG_NAME,
2702 p_procedure_name => 'create_cost_snapshot',
2703 p_error_text => SUBSTR(SQLERRM,1,500));
2704 END IF;
2705 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
2706 p_data => x_msg_data,
2707 p_encoded => fnd_api.g_false);
2708 END create_cost_snapshot;
2709
2710 --------------------------------------------------------------------------
2711 -- Procedure to find out all visit items which have no price list --
2712 --------------------------------------------------------------------------
2713 PROCEDURE get_visit_items_no_price
2714 (
2715 p_api_version IN NUMBER := 1.0,
2716 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2717 p_commit IN VARCHAR2 := FND_API.G_FALSE,
2718 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
2719 p_module_type IN VARCHAR2 := NULL,
2720 x_return_status OUT NOCOPY VARCHAR2,
2721 x_msg_count OUT NOCOPY NUMBER,
2722 x_msg_data OUT NOCOPY VARCHAR2,
2723 p_cost_price_rec IN AHL_VWP_VISIT_CST_PR_PVT.cost_price_rec_type,
2724 x_cost_price_tbl OUT NOCOPY Cost_Price_Tbl_Type
2725 )
2726 AS
2727 l_api_name VARCHAR2(30) :='GET_VISIT_ITEMS_NO_PRICE';
2728 l_msg_data VARCHAR2(2000);
2729 l_return_status VARCHAR2(1);
2730 l_flag VARCHAR2(1);
2731 l_commit VARCHAR2(1) := FND_API.G_FALSE;
2732 l_release_visit_required VARCHAR2(1) :='N';
2733 l_module_name VARCHAR2(200):='ahl.plsql.AHL_VWP_VISIT_CST_PR_PVT.get_visit_items_no_price';
2734
2735 l_cost_price_rec AHL_VWP_VISIT_CST_PR_PVT.cost_price_rec_type:=p_cost_price_rec;
2736 l_job_status_code AHL_WORKORDERS_V.JOB_STATUS_CODE%TYPE;
2737 l_job_status_meaning AHL_WORKORDERS_V.JOB_STATUS_MEANING%TYPE;
2738
2739 l_cost_price_tbl1 Cost_Price_Tbl_Type;
2740 l_cost_price_tbl Cost_Price_Tbl_Type;
2741
2742 l_api_version NUMBER := 1.0;
2743 l_index NUMBER := 0;
2744 l_msg_count NUMBER;
2745 --
2746 Cursor c_visit_csr(c_visit_id in number)
2747 Is
2748 Select visit_id,
2749 outside_party_flag,
2750 organization_id,
2751 price_list_id,
2752 service_request_id
2753 From ahl_visits_b
2754 where visit_id=c_visit_id;
2755
2756 l_visit_rec c_visit_csr%rowtype;
2757
2758 Cursor c_customer_csr(c_incident_id in number)
2759 Is
2760 Select customer_id
2761 From CS_INCIDENTS_ALL_B
2762 Where incident_id=c_incident_id;
2763
2764
2765 -- Get all the root mr
2766 Cursor c_summary_tasks_csr(c_visit_id in number)
2767 is
2768 Select visit_id,
2769 visit_task_id,
2770 originating_task_id
2771 from ahl_visit_tasks_vl
2772 where originating_task_id is null
2773 and visit_id =c_visit_id
2774 and task_type_code ='SUMMARY'
2775 AND NVL(status_code, 'Y') <> NVL ('DELETED', 'X')
2776 and mr_id is not null;
2777
2778 l_summary_recs c_summary_tasks_csr%rowtype;
2779
2780 -- Get all the unassociated tasks
2781 Cursor c_task_csr(c_visit_id in number)
2782 Is
2783 Select visit_task_id,
2784 visit_id,
2785 mr_id
2786 From ahl_visit_tasks_vl
2787 where visit_id=c_visit_id
2788 AND NVL(status_code, 'Y') <> NVL ('DELETED', 'X')
2789 and task_type_code='UNASSOCIATED';
2790
2791 l_task_csr_rec c_task_csr%rowtype;
2792
2793 --Get min(start_time), max(end_time) for summary tasks
2794 CURSOR get_summary_task_times_csr(p_task_id IN NUMBER)IS
2795 SELECT min(start_date_time), max(end_date_time)
2796 FROM ahl_visit_tasks_vl VST
2797 START WITH visit_task_id = p_task_id
2798 AND NVL(VST.status_code, 'Y') <> NVL ('DELETED', 'X')
2799 CONNECT BY originating_task_id = PRIOR visit_task_id;
2800 --
2801 BEGIN
2802
2803 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)THEN
2804 fnd_log.string
2805 (
2806 fnd_log.level_procedure,
2807 l_module_name,
2808 'Start of '||l_api_name
2809 );
2810 END IF;
2811
2812 SAVEPOINT Get_Visit_Items_no_price_PVT;
2813
2814 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version,
2815 p_api_version,
2816 l_api_name,G_PKG_NAME)
2817 THEN
2818 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2819 END IF;
2820
2821 IF FND_API.to_boolean(p_init_msg_list)
2822 THEN
2823 FND_MSG_PUB.initialize;
2824 END IF;
2825
2826 x_return_status:=FND_API.G_RET_STS_SUCCESS;
2827
2828 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
2829 fnd_log.string
2830 (
2831 fnd_log.level_statement,
2832 l_module_name,
2833 'Request to get items without price for Visit ID : ' || l_cost_price_rec.visit_id
2834 );
2835 END IF;
2836
2837
2838 -- Check for Required Parameters
2839 IF(l_cost_price_rec.visit_id IS NULL OR
2840 l_cost_price_rec.visit_id = FND_API.G_MISS_NUM) THEN
2841 FND_MESSAGE.Set_Name('AHL','AHL_VWP_CST_INPUT_MISS');
2842 FND_MSG_PUB.ADD;
2843
2844 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level)THEN
2845 fnd_log.string
2846 (
2847 fnd_log.level_error,
2848 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
2849 'Visit id is mandatory but found null in input '
2850 );
2851 END IF;
2852 RAISE FND_API.G_EXC_ERROR;
2853 END IF;
2854
2855
2856
2857 -- Get visit info
2858 Open c_visit_csr(l_cost_price_rec.visit_id);
2859 Fetch c_visit_csr into l_visit_rec;
2860 If c_visit_csr%notfound
2861 Then
2862 Fnd_Message.SET_NAME('AHL','AHL_VWP_VISIT_INVALID');
2863 Fnd_Msg_Pub.ADD;
2864 Close c_visit_csr;
2865 RAISE FND_API.G_EXC_ERROR;
2866 End if;
2867 Close c_visit_csr;
2868
2869 -- Release visit if required
2870 AHL_VWP_VISIT_CST_PR_PVT.check_for_release_visit
2871 (
2872 p_visit_id =>l_visit_rec.visit_id,
2873 x_release_visit_required =>l_release_visit_required
2874 );
2875
2876
2877 IF l_release_visit_required ='Y' THEN
2878
2879 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
2880 fnd_log.string
2881 (
2882 fnd_log.level_statement,
2883 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
2884 'Before calling ahl vwp proj prod pvt.release visit'
2885 );
2886 END IF;
2887
2888 AHL_VWP_PROJ_PROD_PVT.release_visit
2889 (
2890 p_api_version =>l_api_version,
2891 p_init_msg_list =>Fnd_Api.g_false,
2892 p_commit =>Fnd_Api.g_false,
2893 p_validation_level =>Fnd_Api.g_valid_level_full,
2894 p_module_type => 'CST',
2895 x_return_status =>l_return_Status,
2896 x_msg_count =>x_msg_count,
2897 x_msg_data =>x_msg_data,
2898 p_visit_id =>l_visit_rec.visit_id
2899 );
2900
2901 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
2902 fnd_log.string
2903 (
2904 fnd_log.level_statement,
2905 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
2906 'After calling ahl vwp proj prod pvt.Release Visit task wo Return Status : '|| l_return_status
2907 );
2908 END IF;
2909
2910 -- Check Error Message stack.
2911 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2912 l_msg_count := FND_MSG_PUB.count_msg;
2913 IF l_msg_count > 0 THEN
2914 RAISE FND_API.G_EXC_ERROR;
2915 END IF;
2916 END IF;
2917
2918 END IF; -- release required flag
2919
2920 -- Check outside party flag
2921
2922 If l_visit_rec.outside_party_flag ='N'
2923 then
2924 FND_MESSAGE.Set_Name('AHL','AHL_VWP_CST_OUTSDPRTY_FLAG');
2925 FND_MSG_PUB.ADD;
2926 RAISE FND_API.G_EXC_ERROR;
2927 --Display an error message `Visit number visit_number is not an outside party.'
2928 END if;
2929
2930 -- Populate pricing attributes
2931
2932 -- Populate price list ID
2933
2934 if l_visit_rec.price_list_id is not Null and l_visit_rec.price_list_id <> FND_API.G_MISS_NUM
2935 then
2936 l_cost_price_rec.price_list_id :=l_visit_rec.price_list_id;
2937 Else
2938 FND_MESSAGE.Set_Name('AHL','AHL_VWP_CST_PRICELISTIDNULL'); --AHL_VWP_PRICE_LIST_ID_NULL
2939 FND_MSG_PUB.ADD;
2940 RAISE FND_API.G_EXC_ERROR;
2941
2942 END IF;
2943
2944
2945 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
2946 fnd_log.string
2947 (
2948 fnd_log.level_statement,
2949 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
2950 'Price List ID :' ||l_cost_price_rec.price_list_id
2951 );
2952 END IF;
2953
2954 l_cost_price_rec.organization_id:=l_visit_rec.organization_id;
2955
2956 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
2957 fnd_log.string
2958 (
2959 fnd_log.level_statement,
2960 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
2961 'Organization ID :' ||l_cost_price_rec.organization_id
2962 );
2963 END IF;
2964
2965
2966 -- Populate customer ID
2967
2968 Open c_customer_csr(l_visit_rec.service_request_id);
2969 fetch c_customer_csr into l_cost_price_rec.customer_id;
2970 close c_customer_csr;
2971
2972 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
2973 fnd_log.string
2974 (
2975 fnd_log.level_statement,
2976 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
2977 'Customer ID :' ||l_cost_price_rec.customer_id
2978 );
2979 END IF;
2980
2981
2982 -- Populate currency code
2983
2984 AHL_VWP_RULES_PVT.check_currency_for_costing
2985 (p_visit_id =>l_visit_rec.visit_id,
2986 x_currency_code =>l_cost_price_rec.currency
2987 );
2988
2989 -- Error handling
2990 IF l_cost_price_rec.currency IS NULL THEN
2991 FND_MESSAGE.Set_Name(G_PKG_NAME,'AHL_VWP_CST_NO_CURRENCY');
2992 FND_MSG_PUB.ADD;
2993 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level)THEN
2994 fnd_log.string
2995 (
2996 fnd_log.level_error,
2997 'ahl.plsql.'||G_PKG_NAME||'.'||L_API_NAME,
2998 'No curency is defined for the organization of the visit'
2999 );
3000 END IF;
3001 RAISE FND_API.G_EXC_ERROR;
3002 END IF;
3003
3004 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
3005 fnd_log.string
3006 (
3007 fnd_log.level_statement,
3008 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
3009 'Currency Code :' ||l_cost_price_rec.currency
3010 );
3011 END IF;
3012
3013
3014 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
3015 fnd_log.string
3016 (
3017 fnd_log.level_statement,
3018 l_module_name,
3019 'Before Call to AHL_VWP_RULES_PVT.Check_Job_Status'
3020 );
3021 END IF;
3022
3023
3024 AHL_VWP_RULES_PVT.Check_Job_Status
3025 (
3026 p_id => l_cost_price_rec.visit_id,
3027 p_is_task_flag => 'N',
3028 x_status_code => l_job_status_code,
3029 x_status_meaning => l_job_status_meaning
3030 );
3031
3032 IF (l_job_status_code is NULL) THEN
3033 l_msg_count := FND_MSG_PUB.count_msg;
3034 IF l_msg_count > 0 THEN
3035 RAISE FND_API.G_EXC_ERROR;
3036 END IF;
3037 END IF;
3038
3039 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
3040 fnd_log.string
3041 (
3042 fnd_log.level_statement,
3043 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
3044 'Job Status :' ||l_job_status_code
3045 );
3046 END IF;
3047
3048
3049 --- Get all the Root MRs in the visit
3050 OPEN c_summary_tasks_csr(l_cost_price_rec.visit_id);
3051 LOOP
3052 FETCH c_summary_tasks_csr into l_summary_recs;
3053 EXIT WHEN c_summary_tasks_csr%notfound;
3054
3055 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
3056 fnd_log.string(
3057 fnd_log.level_statement,
3058 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
3059 'Current Task ID: ' || l_summary_recs.visit_task_id
3060 );
3061 END IF;
3062
3063 l_cost_price_rec.visit_task_id:=l_summary_recs.visit_task_id;
3064 OPEN get_summary_task_times_csr(l_cost_price_rec.visit_task_id);
3065 FETCH get_summary_task_times_csr INTO l_cost_price_rec.Task_Start_Date,
3066 l_cost_price_rec.Task_END_Date;
3067 CLOSE get_summary_task_times_csr;
3068
3069 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
3070 fnd_log.string
3071 (
3072 fnd_log.level_statement,
3073 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
3074 'task_start_date : ' ||l_cost_price_rec.task_start_date
3075 );
3076 END IF;
3077
3078 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
3079 fnd_log.string
3080 (
3081 fnd_log.level_statement,
3082 l_module_name,
3083 'Before Call to AHL_VWP_MR_CST_PR_PVT.get_mr_items_no_price'
3084 );
3085 END IF;
3086
3087
3088 AHL_VWP_MR_CST_PR_PVT.get_mr_items_no_price
3089 (
3090 p_api_version =>l_api_version,
3091 p_init_msg_list =>Fnd_Api.g_false,
3092 p_commit =>Fnd_Api.g_false,
3093 p_validation_level =>Fnd_Api.g_valid_level_full,
3094 p_module_type =>'VST',
3095 x_return_status =>l_return_Status,
3096 x_msg_count =>x_msg_count,
3097 x_msg_data =>x_msg_data,
3098 p_cost_price_rec =>l_cost_price_rec,
3099 x_cost_price_tbl =>l_cost_price_tbl1
3100 );
3101
3102
3103 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
3104 fnd_log.string
3105 (
3106 fnd_log.level_statement,
3107 l_module_name,
3108 'After Call to AHL_VWP_MR_CST_PR_PVT.get_mr_items_no_price'
3109 );
3110 END IF;
3111
3112 -- Merge the return value of the child to the current total
3113 IF l_cost_price_tbl.count =0 THEN
3114
3115 l_cost_price_tbl :=l_cost_price_tbl1;
3116
3117 ELSIF l_cost_price_tbl1.count > 0
3118 THEN
3119
3120 l_index:=l_cost_price_tbl.count;
3121
3122 FOR i in l_cost_price_tbl1.first .. l_cost_price_tbl1.last
3123 LOOP
3124 l_cost_price_tbl(l_index):=l_cost_price_tbl1(i);
3125 l_index:=l_index+1;
3126 END LOOP;
3127 END IF;
3128 END LOOP;
3129 CLOSE c_summary_tasks_csr;
3130
3131 -- Get all the unassociated tasks
3132
3133 OPEN c_task_csr(l_cost_price_rec.visit_id);
3134 LOOP
3135 FETCH c_task_csr into l_task_csr_rec;
3136 EXIT WHEN c_task_csr%NOTFOUND;
3137
3138 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
3139 fnd_log.string(
3140 fnd_log.level_statement,
3141 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
3142 'Current Task ID: ' || l_task_csr_rec.visit_task_id
3143 );
3144 END IF;
3145
3146 l_cost_price_rec.visit_task_id:=l_task_csr_rec.visit_task_id;
3147 --Fetch the min(start_time), max(end_time) for summary task
3148 OPEN get_summary_task_times_csr(l_cost_price_rec.visit_task_id);
3149 FETCH get_summary_task_times_csr INTO l_cost_price_rec.Task_Start_Date,
3150 l_cost_price_rec.Task_END_Date;
3151 CLOSE get_summary_task_times_csr;
3152
3153 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
3154 fnd_log.string
3155 (
3156 fnd_log.level_statement,
3157 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
3158 'task_start_date : ' ||l_cost_price_rec.task_start_date
3159 );
3160 END IF;
3161
3162 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
3163 fnd_log.string
3164 (
3165 fnd_log.level_statement,
3166 l_module_name,
3167 'Before Call to AHL_VWP_TASK_CST_PR_PVt.get_task_items_no_price'
3168 );
3169 END IF;
3170
3171 AHL_VWP_TASK_CST_PR_PVT.get_task_items_no_price
3172 (
3173 p_api_version =>p_api_version,
3174 p_init_msg_list =>fnd_api.g_false,
3175 p_commit =>fnd_api.g_false,
3176 p_validation_level =>p_validation_level,
3177 p_module_type =>'VST',
3178 x_return_status =>l_return_status,
3179 x_msg_count =>x_msg_count,
3180 x_msg_data =>x_msg_data,
3181 p_cost_price_rec =>l_cost_price_rec,
3182 x_cost_price_tbl =>l_cost_price_tbl1
3183 );
3184
3185 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
3186 fnd_log.string
3187 (
3188 fnd_log.level_statement,
3189 l_module_name,
3190 'After Call to AHL_VWP_TASK_CST_PR_PVt.get_task_items_no_price'
3191 );
3192 END IF;
3193
3194 -- Merge the return value of the child to the current total
3195 IF l_cost_price_tbl.count = 0 THEN
3196
3197 l_cost_price_tbl :=l_cost_price_tbl1;
3198
3199 ELSIF l_cost_price_tbl1.count > 0 THEN
3200
3201 l_index:=l_cost_price_tbl.count;
3202
3203 FOR i IN l_cost_price_tbl1.first .. l_cost_price_tbl1.last
3204 LOOP
3205 l_cost_price_tbl(l_index) := l_cost_price_tbl1(i);
3206 l_index := l_index + 1;
3207 END LOOP;
3208
3209 END IF;
3210
3211 END LOOP; -- All unassociated tasks
3212 CLOSE c_task_csr;
3213
3214 -- Check Error Message stack.
3215 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
3216 l_msg_count := FND_MSG_PUB.count_msg;
3217 IF l_msg_count > 0 THEN
3218 RAISE FND_API.G_EXC_ERROR;
3219 END IF;
3220 END IF;
3221
3222 x_cost_price_tbl:=l_cost_price_tbl;
3223
3224 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
3225 fnd_log.string
3226 (
3227 fnd_log.level_statement,
3228 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
3229 'Total number of items w/o price for the MR: ' || x_cost_price_tbl.count
3230 );
3231 END IF;
3232
3233 IF FND_API.TO_BOOLEAN(p_commit) THEN
3234 COMMIT;
3235 END IF;
3236
3237 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
3238 fnd_log.string
3239 (
3240 fnd_log.level_procedure,
3241 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
3242 'At the end of the procedure');
3243 END IF;
3244
3245 EXCEPTION
3246 WHEN FND_API.G_EXC_ERROR THEN
3247 x_return_status := FND_API.G_RET_STS_ERROR;
3248 ROLLBACK TO Get_Visit_Items_no_price_PVT;
3249 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
3250 p_data => x_msg_data,
3251 p_encoded => fnd_api.g_false);
3252
3253 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3254 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3255 ROLLBACK TO Get_Visit_Items_no_price_PVT;
3256 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
3257 p_data => x_msg_data,
3258 p_encoded => fnd_api.g_false);
3259 WHEN OTHERS THEN
3260 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3261 ROLLBACK TO Get_Visit_Items_no_price_PVT;
3262 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
3263 fnd_msg_pub.add_exc_msg(p_pkg_name => G_PKG_NAME,
3264 p_procedure_name => l_api_name,
3265 p_error_text => SUBSTR(SQLERRM,1,500));
3266 END IF;
3267 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
3268 p_data => x_msg_data,
3269 p_encoded => fnd_api.g_false);
3270 END Get_Visit_Items_no_price;
3271
3272 --------------------------------------------------------------------------------
3273 ------- Check various conditions and release visit if needed
3274 ---------------------------------------------------------------------------------
3275 PROCEDURE Check_for_Release_Visit
3276 (
3277 p_visit_id IN NUMBER,
3278 x_release_visit_required OUT NOCOPY VARCHAR2
3279 )
3280 AS
3281 --Bug fix #4542684 10Aug2005 yazhou starts
3282 -- Cursor to retrieve visit info
3283 Cursor c_visit_csr(c_visit_id in number)
3284 Is
3285 Select any_task_chg_flag, status_code
3286 From ahl_visits_b
3287 where visit_id=c_visit_id;
3288 --Bug fix #4542684 10Aug2005 yazhou ends
3289
3290 -- Cursor to get all tasks associated to visit
3291 -- Which should have workorders created in production
3292 CURSOR c_get_visit_tasks_cur (C_VISIT_ID IN NUMBER)
3293 IS
3294 SELECT vt.visit_id,
3295 vt.visit_task_id,
3296 vt.visit_task_number
3297 FROM ahl_visit_tasks_vl vt
3298 WHERE vt.visit_id = C_VISIT_ID
3299 AND not (vt.task_type_code = 'SUMMARY' AND VT.mr_id IS NULL)
3300 AND NVL(vt.status_code, 'Y') <> NVL ('DELETED', 'X');
3301
3302 l_visit_tasks_rec c_get_visit_tasks_cur%ROWTYPE;
3303
3304 -- Cursor to check master workorder has been created for visit
3305 CURSOR c_get_master_wo_cur (C_VISIT_ID IN NUMBER)
3306 IS
3307 SELECT workorder_id,
3308 workorder_name,
3309 wip_entity_id,
3310 visit_id,
3311 master_workorder_flag
3312 FROM ahl_workorders wo
3313 WHERE wo.visit_id = C_VISIT_ID
3314 AND wo.visit_task_id IS NULL
3315 AND wo.status_code NOT IN (22,7)
3316 AND wo.master_workorder_flag = 'Y';
3317
3318 l_master_wo_rec c_get_master_wo_cur%ROWTYPE;
3319
3320 -- Cursor to check child workorders has been created for visit tasks
3321 CURSOR c_check_wo_exists_cur (C_VISIT_TASK_ID IN NUMBER)
3322 IS
3323 SELECT 1
3324 FROM ahl_workorders wo
3325 WHERE wo.visit_task_id = C_VISIT_TASK_ID
3326 AND wo.status_code NOT IN (22,7);
3327
3328 -- Local Variables
3329
3330 l_api_name VARCHAR2(30) := 'Check_To_Release_Visit ';
3331 l_release_visit_required VARCHAR2(1) := 'N';
3332 l_any_task_chg_flag VARCHAR2(1) := 'N';
3333 --Bug fix #4542684 10Aug2005 yazhou starts
3334 l_visit_status_code VARCHAR2(30);
3335 --Bug fix #4542684 10Aug2005 yazhou ends
3336 l_api_version NUMBER := 1.0;
3337 l_dummy NUMBER;
3338
3339 BEGIN
3340
3341 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)THEN
3342 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
3343 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name||': Begin API',
3344 'At the start of the procedure');
3345 END IF;
3346
3347 --Bug fix #4542684 10Aug2005 yazhou starts
3348 -- Retrieve visit any task changed flag
3349 OPEN c_visit_csr(p_visit_id);
3350 FETCH c_visit_csr into l_any_task_chg_flag, l_visit_status_code;
3351 CLOSE c_visit_csr;
3352
3353 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
3354 fnd_log.string
3355 (
3356 fnd_log.level_statement,
3357 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
3358 '.any_task_chg_flag : ' || l_any_task_chg_flag
3359 );
3360
3361 END IF;
3362
3363 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
3364 fnd_log.string
3365 (
3366 fnd_log.level_statement,
3367 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
3368 '.visit status code : ' || l_visit_status_code
3369 );
3370
3371 END IF;
3372
3373 IF l_visit_status_code ='RELEASED' THEN
3374 x_release_visit_required := 'N';
3375 RETURN;
3376 END IF;
3377
3378 --Bug fix #4542684 10Aug2005 yazhou ends
3379
3380 -- Check an_task_chg_flag of the visit to decide whether releasing visit is required
3381 IF l_any_task_chg_flag = 'Y' THEN
3382 l_release_visit_required := 'Y';
3383 END IF;
3384
3385 -- Check for master workorder
3386
3387 IF l_release_visit_required ='N' THEN
3388
3389 OPEN c_get_master_wo_cur(p_visit_id);
3390 FETCH c_get_master_wo_cur INTO l_master_wo_rec;
3391 CLOSE c_get_master_wo_cur;
3392
3393 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
3394 fnd_log.string
3395 (
3396 fnd_log.level_statement,
3397 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
3398 'Visit Master Workorder Id : ' || l_master_wo_rec.workorder_id
3399 );
3400 END IF;
3401
3402 -- Master workorder not found then call ahl_vwp_proj_prod_pvt.release_visit
3403 IF l_master_wo_rec.workorder_id IS NULL THEN
3404
3405 l_release_visit_required := 'Y';
3406
3407 END IF;
3408 END IF;
3409
3410 -- Check if workorder have been created for all the tasks in the visit
3411 IF l_release_visit_required ='N' THEN
3412
3413 OPEN c_get_visit_tasks_cur(p_visit_id);
3414 LOOP
3415 FETCH c_get_visit_tasks_cur INTO l_visit_tasks_rec;
3416 EXIT WHEN c_get_visit_tasks_cur%NOTFOUND;
3417
3418 IF l_visit_tasks_rec.visit_task_id IS NOT NULL THEN
3419 -- Check workorder exists
3420 OPEN c_check_wo_exists_cur(l_visit_tasks_rec.visit_task_id);
3421 FETCH c_check_wo_exists_cur INTO l_dummy;
3422
3423 IF c_check_wo_exists_cur%NOTFOUND THEN
3424 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
3425 fnd_log.string
3426 (
3427 fnd_log.level_statement,
3428 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
3429 'workorder not exist for visit task '||l_visit_tasks_rec.visit_task_id
3430 );
3431 END IF;
3432
3433 l_release_visit_required := 'Y';
3434 CLOSE c_check_wo_exists_cur;
3435
3436 EXIT;
3437 END IF; -- workorder not found
3438
3439 CLOSE c_check_wo_exists_cur;
3440 END IF; -- task_id not null
3441 END LOOP;
3442
3443 CLOSE c_get_visit_tasks_cur;
3444 END IF; -- released visit flag
3445
3446 x_release_visit_required := l_release_visit_required;
3447 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
3448 fnd_log.string
3449 (
3450 fnd_log.level_statement,
3451 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
3452 'x_release_visit_required: '||x_release_visit_required
3453 );
3454 END IF;
3455
3456 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
3457 fnd_log.string
3458 (
3459 fnd_log.level_statement,
3460 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
3461 'At the end of the procedure'
3462 );
3463 END IF;
3464
3465 END Check_for_Release_Visit;
3466
3467 END AHL_VWP_VISIT_CST_PR_PVT;