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