[Home] [Help]
PACKAGE BODY: APPS.CS_GET_COVERAGE_VALUES_PUB
Source
1 PACKAGE BODY CS_GET_COVERAGE_VALUES_PUB AS
2 /* $Header: csctcvgb.pls 115.0 99/07/16 08:52:00 porting ship $ */
3 -- Start of comments
4 -- API name : Get_Bill_Rates
5 -- Type : Public
6 -- Pre-reqs : None.
7 -- Function : This function will retrieve the Bill rates for
8 -- the service customer as requested.
9 -- Parameters :
10 -- IN :
11 -- p_api_version NUMBER Required
12 -- p_coverage_id NUMBER Required
13 -- p_exception_coverage_flag VARCHAR2
14 -- p_Business_process_id NUMBER Required
15 -- p_Bill_Rate_Code NUMBER Required
16 -- p_unit_of_measure_code VARCHAR2 Required
17 -- p_list_price NUMBER Required
18 -- p_init_msg_list VARCHAR2 Required
22 -- x_Percent_Rate NUMBER,
19 -- p_commit VARCHAR2 Required
20 -- OUT :
21 -- x_Flat_Rate NUMBER,
23 -- x_ltem_price NUMBER,
24 -- x_return_status VARCHAR2
25 -- x_msg_count NUMBER
26 -- x_msg_data VARCHAR2
27 --End of comments
28
29 PROCEDURE Get_Bill_Rates (
30 p_api_version IN NUMBER,
31 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
32 p_commit IN VARCHAR2 := FND_API.G_FALSE,
33 p_coverage_id IN NUMBER,
34 p_exception_coverage_flag IN VARCHAR2,
35 p_business_process_id IN NUMBER,
36 p_bill_rate_code IN VARCHAR2,
37 p_unit_of_measure_code IN VARCHAR2,
38 p_list_price IN NUMBER,
39 x_flat_rate OUT NUMBER,
40 x_percent_rate OUT NUMBER,
41 x_ltem_price OUT NUMBER,
42 x_return_status OUT VARCHAR2,
43 x_msg_count OUT NUMBER,
44 x_msg_data OUT VARCHAR2 ) IS
45 l_coverage_id CS_COVERAGES.COVERAGE_ID%TYPE;
46
47 CURSOR Bill_Rate_UOM_csr IS
48 SELECT CBR.Flat_Rate,
49 CBR.Percent_Rate
50 FROM CS_COV_BILL_RATES CBR,
51 CS_COVERAGE_TXN_GROUPS CTG,
52 CS_COV_BILLING_TYPES CBT,
53 CS_TXN_BILLING_TYPES TBT,
54 CS_LOOKUPS LKT
55 WHERE CTG.Coverage_id = l_coverage_id
56 AND CTG.Business_Process_Id = p_business_process_id
57 AND CTG.Coverage_Txn_Group_Id = CBT.Coverage_Txn_Group_Id
58 AND TBT.Txn_Billing_Type_Id = CBT.Txn_Billing_Type_Id
59 AND TBT.Billing_Type = 'L'
60 AND CBR.Coverage_Billing_Type_Id = CBT.Coverage_Billing_Type_Id
61 AND CBR.Unit_of_Measure_Code = p_unit_of_measure_code
62 AND CBR.Rate_Type_Code = p_bill_rate_code
63 AND LKT.Lookup_Code = CBR.Rate_Type_Code
64 AND LKT.Lookup_Type = 'BILLING_RATE';
65
66 CURSOR Bill_Rate_Null_UOM_csr IS
67 SELECT CBR.Flat_Rate,
68 CBR.Percent_Rate
69 FROM CS_COV_BILL_RATES CBR,
70 CS_COVERAGE_TXN_GROUPS CTG,
71 CS_COV_BILLING_TYPES CBT,
72 CS_TXN_BILLING_TYPES TBT,
73 CS_LOOKUPS LKT
74 WHERE CTG.Coverage_id = l_coverage_id
75 AND CTG.Business_Process_Id = p_business_process_id
76 AND CTG.Coverage_Txn_Group_Id = CBT.Coverage_Txn_Group_Id
77 AND TBT.Txn_Billing_Type_Id = CBT.Txn_Billing_Type_Id
78 AND TBT.Billing_Type = 'L'
79 AND CBR.Coverage_Billing_Type_Id = CBT.Coverage_Billing_Type_Id
80 AND CBR.Unit_of_Measure_Code IS NULL
81 AND CBR.Rate_Type_Code = p_bill_rate_code
82 AND LKT.Lookup_Code = CBR.Rate_Type_Code
83 AND LKT.Lookup_Type = 'BILLING_RATE';
84 l_api_name CONSTANT VARCHAR2(30) := 'Get_Bill_Rates';
85 l_api_version CONSTANT NUMBER := 1;
86 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
87 BEGIN
88 l_return_status := TAPI_DEV_KIT.START_ACTIVITY( l_api_name,
89 G_PKG_NAME,
90 l_api_version,
91 p_api_version,
92 p_init_msg_list,
93 '_pub',
94 x_return_status);
95 IF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
96 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
97 END IF;
98
99 IF (NVL(p_exception_coverage_flag,'N') = 'Y') THEN
100 CS_GET_COVERAGE_VALUES_PUB.Get_Exception_Coverage(
101 1,
102 FND_API.G_FALSE,
103 FND_API.G_FALSE,
104 p_coverage_id,
105 l_coverage_id,
106 x_return_status,
107 x_msg_count,
108 x_msg_data);
109 IF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
110 FND_MESSAGE.Set_Name ('CS','CS_CONTRACTS_VALUE_NOT_FOUND');
111 FND_MESSAGE.Set_Token('VALUE','EXCEPTION COVERAGE');
112 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
113 ELSIF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
114 FND_MESSAGE.Set_Name ('CS','CS_CONTRACTS_VALUE_NOT_FOUND');
115 FND_MESSAGE.Set_Token('VALUE','EXCEPTION COVERAGE');
116 RAISE FND_API.G_EXC_ERROR;
117 END IF;
118 ELSE
119 l_coverage_id := p_coverage_id;
120 END IF;
121
122 OPEN Bill_Rate_UOM_Csr;
123 FETCH Bill_Rate_UOM_Csr
124 INTO x_flat_rate,
125 x_percent_rate;
126
127 IF Bill_Rate_UOM_Csr%NOTFOUND THEN
128 OPEN Bill_Rate_Null_UOM_Csr;
129 FETCH Bill_Rate_Null_UOM_Csr
130 INTO x_flat_rate,
131 x_percent_rate;
132
133 -- IF Bill_Rate_Null_UOM_Csr%NOTFOUND THEN
134 -- FND_MESSAGE.Set_Name ('CS','CS_CONTRACTS_VALUE_NOT_FOUND');
135 -- FND_MESSAGE.Set_Token('VALUE','BILL RATES');
136 -- RAISE FND_API.G_EXC_ERROR;
137 -- END IF;
138
139 CLOSE Bill_Rate_Null_UOM_Csr;
140 END IF;
141 CLOSE Bill_Rate_UOM_Csr;
142
143 IF (x_percent_rate IS NOT NULL) AND
147
144 (p_list_price IS NOT NULL) THEN
145 x_ltem_price := NVL(p_list_price,0) * x_percent_rate/100;
146 END IF;
148 TAPI_DEV_KIT.END_ACTIVITY(p_commit,
149 x_msg_count,
150 x_msg_data);
151 x_return_status := FND_API.G_RET_STS_SUCCESS;
152 EXCEPTION
153 WHEN FND_API.G_EXC_ERROR THEN
154 x_return_status := TAPI_DEV_KIT.HANDLE_EXCEPTIONS
155 (l_api_name,
156 G_PKG_NAME,
157 'FND_API.G_RET_STS_ERROR',
158 x_msg_count,
159 x_msg_data,
160 '_pub');
161 APP_EXCEPTION.RAISE_EXCEPTION;
162 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
163 x_return_status := TAPI_DEV_KIT.HANDLE_EXCEPTIONS
164 (l_api_name,
165 G_PKG_NAME,
166 'FND_API.G_RET_STS_UNEXP_ERROR',
167 x_msg_count,
168 x_msg_data,
169 '_pub');
170 APP_EXCEPTION.RAISE_EXCEPTION;
171 /*
172 WHEN OTHERS THEN
173 x_return_status := TAPI_DEV_KIT.HANDLE_EXCEPTIONS
174 (l_api_name,
175 G_PKG_NAME,
176 'OTHERS',
177 x_msg_count,
178 x_msg_data,
179 '_pub');
180 */
181 END Get_Bill_Rates;
182
183 /******************************************************************************/
184
185 -- Start of comments
186 -- API name : Get_Preferred_Engineer
187 -- Type : Public
188 -- Pre-reqs : None.
189 -- Function : This function will retrieve the preferred engineer
190 -- associated with the service coverage specified.
191 --
192 -- Parameters : Specifiying the transaction group and the coverage.
193 -- IN :
194 -- p_api_version NUMBER Required
195 -- p_coverage_id NUMBER Required
196 -- p_business_process_id VARCHAR2 Required
197 -- p_exception_coverage_flag VARCHAR2
198 -- p_init_msg_list VARCHAR2 Required
199 -- p_commit VARCHAR2 Required
200 -- OUT :
201 -- x_preferred_engineer1 VARCHAR2
202 -- x_preferred_engineer2 VARCHAR2
203 -- x_return_status VARCHAR2
204 -- x_msg_count NUMBER
205 -- x_msg_data VARCHAR2
206 --End of comments
207
208 PROCEDURE Get_Preferred_Engineer (
209 p_api_version IN NUMBER,
210 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
211 p_commit IN VARCHAR2 := FND_API.G_FALSE,
212 p_coverage_id IN NUMBER,
213 p_business_process_id IN VARCHAR2,
214 p_exception_coverage_flag IN VARCHAR2,
215 x_preferred_engineer1 OUT VARCHAR2,
216 x_preferred_engineer2 OUT VARCHAR2,
217 x_return_status OUT VARCHAR2,
218 x_msg_count OUT NUMBER,
219 x_msg_data OUT VARCHAR2 ) IS
220 l_coverage_id CS_COVERAGES.COVERAGE_ID%TYPE;
221
222 CURSOR Preferred_Engineer_csr IS
223 SELECT TXN.Preferred_Engineer1,
224 TXN.Preferred_Engineer2
225 FROM CS_COVERAGE_TXN_GROUPS TXN
226 WHERE TXN.Coverage_id = l_coverage_id
227 AND TXN.Business_Process_Id = p_business_Process_id;
228
229 l_api_name CONSTANT VARCHAR2(30) := 'Get_Preferred_Engineer';
230 l_api_version CONSTANT NUMBER := 1;
231 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
232 BEGIN
233 l_return_status := TAPI_DEV_KIT.START_ACTIVITY( l_api_name,
234 G_PKG_NAME,
235 l_api_version,
236 p_api_version,
237 p_init_msg_list,
238 '_pub',
239 x_return_status);
240 IF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
241 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
242 END IF;
243
244 IF (NVL(p_exception_coverage_flag,'N') = 'Y') THEN
245 CS_GET_COVERAGE_VALUES_PUB.Get_Exception_Coverage(
246 1,
247 FND_API.G_FALSE,
248 FND_API.G_FALSE,
249 p_coverage_id,
250 l_coverage_id,
251 x_return_status,
252 x_msg_count,
253 x_msg_data);
254 IF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
255 FND_MESSAGE.Set_Name ('CS','CS_CONTRACTS_VALUE_NOT_FOUND');
256 FND_MESSAGE.Set_Token('VALUE','EXCEPTION COVERAGE');
257 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
258 ELSIF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
259 FND_MESSAGE.Set_Name ('CS','CS_CONTRACTS_VALUE_NOT_FOUND');
260 FND_MESSAGE.Set_Token('VALUE','EXCEPTION COVERAGE');
261 RAISE FND_API.G_EXC_ERROR;
262 END IF;
263 ELSE
264 l_coverage_id := p_coverage_id;
265 END IF;
266
267 OPEN Preferred_Engineer_csr;
271
268 FETCH Preferred_Engineer_csr
269 INTO x_Preferred_Engineer1,
270 x_Preferred_Engineer2;
272 IF Preferred_Engineer_csr%NOTFOUND THEN
273 FND_MESSAGE.Set_Name ('CS','CS_CONTRACTS_VALUE_NOT_FOUND');
274 FND_MESSAGE.Set_Token('VALUE','PREFERRED ENGINEER');
275 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
276 END IF;
277 CLOSE Preferred_Engineer_csr;
278
279 TAPI_DEV_KIT.END_ACTIVITY(p_commit,
280 x_msg_count,
281 x_msg_data);
282 x_return_status := FND_API.G_RET_STS_SUCCESS;
283 EXCEPTION
284 WHEN FND_API.G_EXC_ERROR THEN
285 x_return_status := TAPI_DEV_KIT.HANDLE_EXCEPTIONS
286 (l_api_name,
287 G_PKG_NAME,
288 'FND_API.G_RET_STS_ERROR',
289 x_msg_count,
290 x_msg_data,
291 '_pub');
292 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
293 x_return_status := TAPI_DEV_KIT.HANDLE_EXCEPTIONS
294 (l_api_name,
295 G_PKG_NAME,
296 'FND_API.G_RET_STS_UNEXP_ERROR',
297 x_msg_count,
298 x_msg_data,
299 '_pub');
300 WHEN OTHERS THEN
301 x_return_status := TAPI_DEV_KIT.HANDLE_EXCEPTIONS
302 (l_api_name,
303 G_PKG_NAME,
304 'OTHERS',
305 x_msg_count,
306 x_msg_data,
307 '_pub');
308 END Get_Preferred_Engineer;
309
310 /******************************************************************************/
311
312 -- Start of comments
313 -- API name : Get_Exception_Coverage
314 -- Type : Public
315 -- Pre-reqs : None.
316 -- Function : This function will retrieve the Exception coverage id
317 -- for the given coverage.
318 --
319 -- Parameters :
320 -- IN :
321 -- p_api_version NUMBER Required
322 -- p_coverage_id NUMBER Required
323 -- p_init_msg_list VARCHAR2
324 -- p_commit VARCHAR2
325 -- OUT :
326 -- x_exception_coverage_id NUMBER
327 -- x_return_status VARCHAR2
328 -- x_msg_count NUMBER
329 -- x_msg_data VARCHAR2
330 --End of comments
331
332 PROCEDURE Get_Exception_coverage (
333 p_api_version IN NUMBER,
334 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
335 p_commit IN VARCHAR2 := FND_API.G_FALSE,
336 p_coverage_id IN NUMBER,
337 x_exception_coverage_id OUT NUMBER,
338 x_return_status OUT VARCHAR2,
339 x_msg_count OUT NUMBER,
340 x_msg_data OUT VARCHAR2 ) IS
341 CURSOR Exception_Coverage_csr IS
342 SELECT COV2.Coverage_id
343 FROM CS_COVERAGES COV1,
344 CS_COVERAGES COV2
345 WHERE COV1.Coverage_id = p_coverage_id
346 AND COV2.Coverage_id = COV1.Exception_Coverage_id;
347
348 l_api_name CONSTANT VARCHAR2(30) := 'Get_Exception_Coverage';
349 l_api_version CONSTANT NUMBER := 1;
350 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
351 BEGIN
352 l_return_status := TAPI_DEV_KIT.START_ACTIVITY( l_api_name,
353 G_PKG_NAME,
354 l_api_version,
355 p_api_version,
356 p_init_msg_list,
357 '_pub',
358 x_return_status);
359 IF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
360 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
361 END IF;
362
363 OPEN Exception_Coverage_csr;
364 FETCH Exception_Coverage_csr INTO x_exception_coverage_id;
365
366 IF Exception_coverage_csr%NOTFOUND THEN
367 FND_MESSAGE.Set_Name ('CS','CS_CONTRACTS_VALUE_NOT_FOUND');
368 FND_MESSAGE.Set_Token('VALUE','EXCEPTION COVERAGE');
369 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
370 END IF;
371 CLOSE Exception_Coverage_csr;
372
373 TAPI_DEV_KIT.END_ACTIVITY(p_commit,
374 x_msg_count,
375 x_msg_data);
376 x_return_status := FND_API.G_RET_STS_SUCCESS;
377 EXCEPTION
378 WHEN FND_API.G_EXC_ERROR THEN
379 x_return_status := TAPI_DEV_KIT.HANDLE_EXCEPTIONS
380 (l_api_name,
381 G_PKG_NAME,
382 'FND_API.G_RET_STS_ERROR',
383 x_msg_count,
384 x_msg_data,
385 '_pub');
386 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
387 x_return_status := TAPI_DEV_KIT.HANDLE_EXCEPTIONS
388 (l_api_name,
389 G_PKG_NAME,
390 'FND_API.G_RET_STS_UNEXP_ERROR',
391 x_msg_count,
392 x_msg_data,
393 '_pub');
394 WHEN OTHERS THEN
395 x_return_status := TAPI_DEV_KIT.HANDLE_EXCEPTIONS
396 (l_api_name,
397 G_PKG_NAME,
398 'OTHERS',
399 x_msg_count,
400 x_msg_data,
401 '_pub');
402 END Get_Exception_Coverage;
403
404 END CS_GET_COVERAGE_VALUES_PUB;