[Home] [Help]
PACKAGE BODY: APPS.OKL_TBC_DEFINITIONS_PUB
Source
1 PACKAGE BODY OKL_TBC_DEFINITIONS_PUB AS
2 /* $Header: OKLPTBCB.pls 120.4 2007/03/20 11:24:12 asawanka noship $ */
3
4
5 FUNCTION Validate_Record (
6 p_tbcv_rec IN tbcv_rec_type,
7 p_rec_number IN NUMBER
8
9
10 ) RETURN VARCHAR2 IS
11
12 CURSOR l_checkduplicate_tbc_csr(cp_tax_attribute_def_id IN VARCHAR2) IS
13 SELECT result_code
14 FROM OKL_TAX_ATTR_DEFINITIONS
15 WHERE result_type_code = 'TBC_CODE'
16 AND nvl(TRY_ID, -99999) = nvl(p_tbcv_rec.TRY_ID,-99999)
17 AND nvl(STY_ID, -99999) = nvl(p_tbcv_rec.STY_ID,-99999)
18 AND nvl(BOOK_CLASS_CODE,'XXXXX') = nvl(p_tbcv_rec.BOOK_CLASS_CODE,'XXXXX')
19 AND nvl(TAX_COUNTRY_CODE,'XXXXX') = nvl(p_tbcv_rec.TAX_COUNTRY_CODE,'XXXXX')
20 AND nvl(TAX_ATTRIBUTE_DEF_ID, -99999) <> nvl(cp_tax_attribute_def_id,-99999)
21 AND nvl(EXPIRE_FLAG,'N')<> 'Y' ;
22
23 CURSOR l_checkduplicate_pc_csr(cp_tax_attribute_def_id IN VARCHAR2) IS
24 SELECT result_code
25 FROM OKL_TAX_ATTR_DEFINITIONS
26 WHERE result_type_code = 'PC_CODE'
27 AND nvl(PURCHASE_OPTION_CODE,'XXXXX') = nvl(p_tbcv_rec.PURCHASE_OPTION_CODE,'XXXXX')
28 AND nvl(STY_ID, -99999) = nvl(p_tbcv_rec.STY_ID,-99999)
29 AND nvl(INT_DISCLOSED_CODE,'N') = nvl(p_tbcv_rec.INT_DISCLOSED_CODE,'N')
30 AND nvl(TITLE_TRNSFR_CODE,'N') = nvl(p_tbcv_rec.TITLE_TRNSFR_CODE,'N')
31 AND nvl(SALE_LEASE_BACK_CODE,'N') = nvl(p_tbcv_rec.SALE_LEASE_BACK_CODE,'N')
32 AND nvl(LEASE_PURCHASED_CODE,'N') = nvl(p_tbcv_rec.LEASE_PURCHASED_CODE,'N')
33 AND nvl(TAX_COUNTRY_CODE,'XXXXX') = nvl(p_tbcv_rec.TAX_COUNTRY_CODE,'XXXXX')
34 AND nvl(TAX_ATTRIBUTE_DEF_ID, -99999) <> nvl(cp_tax_attribute_def_id,-99999)
35 AND nvl(EXPIRE_FLAG,'N')<> 'Y' ;
36
37 CURSOR l_checkduplicate_ufc_csr(cp_tax_attribute_def_id IN VARCHAR2) IS
38 SELECT result_code
39 FROM OKL_TAX_ATTR_DEFINITIONS
40 WHERE result_type_code = 'UFC_CODE'
41 AND nvl(PURCHASE_OPTION_CODE,'XXXXX') = nvl(p_tbcv_rec.PURCHASE_OPTION_CODE,'XXXXX')
42 AND nvl(PDT_ID, -99999) = nvl(p_tbcv_rec.PDT_ID,-99999)
43 AND nvl(STY_ID, -99999) = nvl(p_tbcv_rec.STY_ID,-99999)
44 AND nvl(TRY_ID, -99999) = nvl(p_tbcv_rec.TRY_ID,-99999)
45 AND nvl(LEASE_PURCHASED_CODE,'N') = nvl(p_tbcv_rec.LEASE_PURCHASED_CODE,'N')
46 AND nvl(EQUIP_USAGE_CODE,'XXXXX') = nvl(p_tbcv_rec.EQUIP_USAGE_CODE,'XXXXX')
47 AND nvl(VENDOR_SITE_ID,-99999) = nvl(p_tbcv_rec.VENDOR_SITE_ID,-99999)
48 AND nvl(INT_DISCLOSED_CODE,'N') = nvl(p_tbcv_rec.INT_DISCLOSED_CODE,'N')
49 AND nvl(TITLE_TRNSFR_CODE,'N') = nvl(p_tbcv_rec.TITLE_TRNSFR_CODE,'N')
50 AND nvl(SALE_LEASE_BACK_CODE,'N') = nvl(p_tbcv_rec.SALE_LEASE_BACK_CODE,'N')
51 AND nvl(TAX_COUNTRY_CODE,'XXXXX') = nvl(p_tbcv_rec.TAX_COUNTRY_CODE,'XXXXX')
52 AND nvl(TERM_QUOTE_TYPE_CODE,'XXXXX') = nvl(p_tbcv_rec.TERM_QUOTE_TYPE_CODE,'XXXXX')
53 AND nvl(TERM_QUOTE_REASON_CODE,'XXXXX') = nvl(p_tbcv_rec.TERM_QUOTE_REASON_CODE,'XXXXX')
54 AND nvl(EXPIRE_FLAG,'N')<> 'Y'
55 AND nvl(TAX_ATTRIBUTE_DEF_ID, -99999) <> nvl(cp_tax_attribute_def_id,-99999)
56
57 AND ( ( -- This condition will allow cases where DB FROm and To are NULL and also Screen FROM and TO are null
58 --(AGE_OF_EQUIP_FROM IS NOT NULL OR AGE_OF_EQUIP_TO IS NOT NULL OR p_tbcv_rec.AGE_OF_EQUIP_FROM IS NOT NULL OR p_tbcv_rec.AGE_OF_EQUIP_TO IS NOT NULL )
59 -- AND
60 -- this condition will prevent exact matches (including cases where some values are null)
61 (nvl(AGE_OF_EQUIP_FROM,-99999) = nvl(p_tbcv_rec.AGE_OF_EQUIP_FROM,-99999) AND
62 nvl(AGE_OF_EQUIP_TO, -99999) = nvl(p_tbcv_rec.AGE_OF_EQUIP_TO,-99999)
63 )
64 )
65 OR -- age of equipment from can not be null for comparison purposes (when TO is not null),
66 -- as we can assume it is 0, if null
67 -- so this condition takes care of scenarios where both Froms and both Tos have a value
68 -- OR any of the FROMs are null and both Tos have a value
69 (--nvl(AGE_OF_EQUIP_FROM,0) IS NOT NULL AND nvl(p_tbcv_rec.AGE_OF_EQUIP_FROM,0) IS NOT NULL AND
70 AGE_OF_EQUIP_TO IS NOT NULL AND p_tbcv_rec.AGE_OF_EQUIP_TO IS NOT NULL AND
71 ( (nvl(p_tbcv_rec.AGE_OF_EQUIP_FROM,0) < nvl(AGE_OF_EQUIP_FROM,0) AND p_tbcv_rec.AGE_OF_EQUIP_TO >= nvl(AGE_OF_EQUIP_FROM,0))
72 OR
73 (nvl(p_tbcv_rec.AGE_OF_EQUIP_FROM,0) >= nvl(AGE_OF_EQUIP_FROM,0) AND nvl(p_tbcv_rec.AGE_OF_EQUIP_FROM,0) <= AGE_OF_EQUIP_TO) --AND p_tbcv_rec.AGE_OF_EQUIP_TO > AGE_OF_EQUIP_TO)
74 )
75
76 )
77 OR
78 ( AGE_OF_EQUIP_TO IS NULL AND p_tbcv_rec.AGE_OF_EQUIP_TO IS NULL AND
79 -- In this case Both the FROMs can not be null together or have the same value, as it will get captured in condition 1
80 -- here, either DB FROM is Null and Screen FROM is not null --> This combination is ok
81 -- OR DB FROM is not null and Screen FROM is null --> this combinatio is ok
82 -- OR both FROMs have a value(differenr value) --> restrict this combination
83 AGE_OF_EQUIP_FROM IS NOT NULL AND p_tbcv_rec.AGE_OF_EQUIP_FROM IS NOT NULL -- The 2 FROMs can not have same value at this point
84 )
85 OR
86 ( AGE_OF_EQUIP_TO IS NULL AND p_tbcv_rec.AGE_OF_EQUIP_TO IS NOT NULL AND -- TO in DB is Null,TO on screen is not null
87 -- In this case following scenarios are possible
88 -- DB FROM is Null (DB To is also NUll) FROM on the screen can be considered to be be >=0 (0 if null), since TO on screen is not null - OK
89 -- DB FROM >=0, SCREEN TO < DB FROM - ok
90 -- DB FROM >=0, SCREEN TO >= DB FROM - restrict this condition
91 AGE_OF_EQUIP_FROM >= 0 AND p_tbcv_rec.AGE_OF_EQUIP_TO >= AGE_OF_EQUIP_FROM
92 )
93 OR
94 ( AGE_OF_EQUIP_TO IS NOT NULL AND p_tbcv_rec.AGE_OF_EQUIP_TO IS NULL AND
95 -- In this case following scenarios are possible
96 -- DB FROM can be considered to be >=0 (0 if null), since DB TO is not null, so there is a fixed age range defined in DB
97 -- SCREEN FROM is null (TO is always NULL) - OK
98 -- screen from >=0, SCREEN FROM > DB TO - ok
99 -- screen from >=0, screen from <= db to - RESTRICT THIS CONDITION
100 p_tbcv_rec.AGE_OF_EQUIP_FROM >=0 AND p_tbcv_rec.AGE_OF_EQUIP_FROM <= AGE_OF_EQUIP_TO
101 )
102 ) ;
103
104 CURSOR okl_tbc_res_code_fk_csr (p_lookup_code IN VARCHAR2) IS
105 SELECT classification_name
106 FROM zx_fc_business_categories_v
107 WHERE classification_code = p_lookup_code;
108
109 CURSOR okl_pc_res_code_fk_csr (p_lookup_code IN VARCHAR2) IS
110 SELECT classification_name
111 FROM zx_fc_product_categories_v
112 WHERE classification_code = p_lookup_code;
113
114 CURSOR okl_ufc_res_code_fk_csr (p_lookup_code IN VARCHAR2) IS
115 SELECT classification_name
116 FROM zx_fc_user_defined_v
117 WHERE classification_code = p_lookup_code;
118
119 l_result_code VARCHAR2(300) := 'XXXXX';
120 x_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
121 l_meaning VARCHAR2(80);
122
123 l_msg_name VARCHAR2(80);
124 l_token1 VARCHAR2(10);
125
126 BEGIN
127
128 IF ((p_tbcv_rec.RESULT_CODE = OKL_API.G_MISS_CHAR OR p_tbcv_rec.RESULT_CODE IS NULL) ) THEN
129
130 x_return_status := OKC_API.G_RET_STS_ERROR;
131 --Unable to create Category definition as mandatory attributes are provided.
132 OKL_API.set_message(p_app_name => 'OKL',
133 p_msg_name => 'OKL_TX_NO_TBC_ATTR');
134 RAISE G_EXCEPTION_HALT_VALIDATION;
135
136 END IF;
137 IF (p_tbcv_rec.AGE_OF_EQUIP_FROM IS NOT NULL AND p_tbcv_rec.AGE_OF_EQUIP_FROM <> OKL_API.G_MISS_NUM) AND
138 (p_tbcv_rec.AGE_OF_EQUIP_TO IS NOT NULL AND p_tbcv_rec.AGE_OF_EQUIP_TO <> OKL_API.G_MISS_NUM) THEN
139 IF p_tbcv_rec.AGE_OF_EQUIP_TO < p_tbcv_rec.AGE_OF_EQUIP_FROM THEN
140 x_return_status := OKC_API.G_RET_STS_ERROR;
141 --Unable to create Transcation Business Category definition as none of the attributes are provided.
142 OKL_API.set_message(p_app_name => 'OKL',
143 p_msg_name => 'OKL_TX_INVALID_AGE_RANGE');
144 RAISE G_EXCEPTION_HALT_VALIDATION;
145
146 END IF;
147 END IF;
148
149
150 IF (p_tbcv_rec.result_type_code = 'TBC_CODE') THEN
151 OPEN l_checkduplicate_tbc_csr(p_tbcv_rec.tax_attribute_def_id);
152 FETCH l_checkduplicate_tbc_csr INTO l_result_code;
153 CLOSE l_checkduplicate_tbc_csr;
154 IF l_result_code <> 'XXXXX' THEN
155 OPEN okl_tbc_res_code_fk_csr(l_result_code);
156 FETCH okl_tbc_res_code_fk_csr INTO l_meaning;
157 CLOSE okl_tbc_res_code_fk_csr;
158 END IF;
159
160 l_msg_name := 'OKL_TX_DUP_TBC_UI_ERR';
161 l_token1 := 'TBC';
162
163 ELSIF (p_tbcv_rec.result_type_code = 'PC_CODE') THEN
164 OPEN l_checkduplicate_pc_csr(p_tbcv_rec.tax_attribute_def_id);
165 FETCH l_checkduplicate_pc_csr INTO l_result_code;
166 CLOSE l_checkduplicate_pc_csr;
167 IF l_result_code <> 'XXXXX' THEN
168 OPEN okl_pc_res_code_fk_csr(l_result_code);
169 FETCH okl_pc_res_code_fk_csr INTO l_meaning;
170 CLOSE okl_pc_res_code_fk_csr;
171 END IF;
172
173 l_msg_name := 'OKL_TX_DUP_PC_UI_ERR';
174 l_token1 := 'PC_CODE';
175
176 ELSIF (p_tbcv_rec.result_type_code = 'UFC_CODE') THEN
177 OPEN l_checkduplicate_ufc_csr(p_tbcv_rec.tax_attribute_def_id);
178 FETCH l_checkduplicate_ufc_csr INTO l_result_code;
179 CLOSE l_checkduplicate_ufc_csr;
180 IF l_result_code <> 'XXXXX' THEN
181 OPEN okl_ufc_res_code_fk_csr(l_result_code);
182 FETCH okl_ufc_res_code_fk_csr INTO l_meaning;
183 CLOSE okl_ufc_res_code_fk_csr;
184 END IF;
185
186 l_msg_name := 'OKL_TX_DUP_UFC_ERR';
187 l_token1 := 'UFC_CODE';
188
189 END IF;
190
191 -- There can be at the most one duplicate record.
192 IF l_result_code <> 'XXXXX' THEN
193 x_return_status := OKC_API.G_RET_STS_ERROR;
194 -- Another Category already exists for this combination of tax determinants.
195 -- modified by dcshanmu for eBTax project - modification start
196 -- modified default values passed to p_msg_name and p_token1
197 IF l_token1 = 'UFC_CODE' THEN
198 OKL_API.set_message(p_app_name => 'OKL',
199 p_msg_name => l_msg_name,
200 p_token1 => l_token1,
201 p_token1_value => l_meaning);
202
203 ELSE
204
205 OKL_API.set_message(p_app_name => 'OKL',
206 p_msg_name => l_msg_name,
207 p_token1 => l_token1,
208 p_token1_value => l_meaning,
209 p_token2 => 'REC_NO',
210 p_token2_value => p_rec_number);
211 END IF;
212 RAISE G_EXCEPTION_HALT_VALIDATION;
213
214 END IF;
215
216 RETURN (x_return_status);
217 EXCEPTION
218 WHEN G_EXCEPTION_HALT_VALIDATION THEN
219 RETURN(x_return_status);
220 WHEN OTHERS THEN
221 OKL_API.SET_MESSAGE( p_app_name => G_APP_NAME
222 ,p_msg_name => G_UNEXPECTED_ERROR
223 ,p_token1 => G_SQLCODE_TOKEN
224 ,p_token1_value => SQLCODE
225 ,p_token2 => G_SQLERRM_TOKEN
226 ,p_token2_value => SQLERRM);
227 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
228 RETURN(x_return_status);
229 END Validate_Record;
230
231 PROCEDURE insert_tbc_definition(
232 p_api_version IN NUMBER,
233 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
234 x_return_status OUT NOCOPY VARCHAR2,
235 x_msg_count OUT NOCOPY NUMBER,
236 x_msg_data OUT NOCOPY VARCHAR2,
237 p_tbcv_rec IN tbcv_rec_type,
238 x_tbcv_rec OUT NOCOPY tbcv_rec_type) IS
239
240
241 l_api_version NUMBER ;
242 l_init_msg_list VARCHAR2(1) ;
243 l_return_status VARCHAR2(1);
244 l_msg_count NUMBER ;
245 l_msg_data VARCHAR2(2000);
246 lp_tbcv_rec tbcv_rec_type;
247 lx_tbcv_rec tbcv_rec_type;
248
249 BEGIN
250 OKL_API.init_msg_list(p_init_msg_list);
251 SAVEPOINT tbc_insert ;
252
253
254 l_api_version := p_api_version ;
255 l_init_msg_list := p_init_msg_list ;
256 l_return_status := x_return_status ;
257 l_msg_count := x_msg_count ;
258 l_msg_data := x_msg_data ;
259 lp_tbcv_rec := p_tbcv_rec;
260 lx_tbcv_rec := x_tbcv_rec;
261
262
263
264
265
266
267 okl_tbc_pvt.insert_row(
268 l_api_version
269 ,l_init_msg_list
270 ,l_return_status
271 ,l_msg_count
272 ,l_msg_data
273 ,lp_tbcv_rec
274 ,lx_tbcv_rec);
275
276 IF ( l_return_status = FND_API.G_RET_STS_ERROR ) THEN
277 RAISE FND_API.G_EXC_ERROR;
278 ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR ) THEN
279 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
280 END IF;
281
282 --Copy value of OUT variable in the IN record type
283 lp_tbcv_rec := lx_tbcv_rec;
284
285
286
287
288
289 --Assign value to OUT variables
290 x_tbcv_rec := lx_tbcv_rec;
291 x_return_status := l_return_status ;
292 x_msg_count := l_msg_count ;
293 x_msg_data := l_msg_data ;
294
295 EXCEPTION
296
297 WHEN FND_API.G_EXC_ERROR THEN
298 ROLLBACK TO tbc_insert;
299 x_return_status := FND_API.G_RET_STS_ERROR;
300 x_msg_count := l_msg_count ;
301 x_msg_data := l_msg_data ;
302 FND_MSG_PUB.count_and_get(
303 p_count => x_msg_count
304 ,p_data => x_msg_data);
305 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
306 ROLLBACK TO tbc_insert;
307 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
308 x_msg_count := l_msg_count ;
309 x_msg_data := l_msg_data ;
310 FND_MSG_PUB.count_and_get(
311 p_count => x_msg_count
312 ,p_data => x_msg_data);
313 WHEN OTHERS THEN
314 ROLLBACK TO tbc_insert;
315 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
316 x_msg_count := l_msg_count ;
317 x_msg_data := l_msg_data ;
318 FND_MSG_PUB.ADD_EXC_MSG('OKL_TBC_DEFINITIONS_PUB','insert_tbc_definition');
319 FND_MSG_PUB.count_and_get(
320 p_count => x_msg_count
321 ,p_data => x_msg_data);
322 END insert_tbc_definition;
323
324 PROCEDURE insert_tbc_definition(
325 p_api_version IN NUMBER,
326 p_init_msg_list IN VARCHAR2,
327 x_return_status OUT NOCOPY VARCHAR2,
328 x_msg_count OUT NOCOPY NUMBER,
329 x_msg_data OUT NOCOPY VARCHAR2,
330 p_tbcv_tbl IN tbcv_tbl_type,
331 x_tbcv_tbl OUT NOCOPY tbcv_tbl_type) IS
332
333 l_api_version NUMBER;
334 l_init_msg_list VARCHAR2(1) ;
335 l_msg_data VARCHAR2(2000);
336 l_msg_count NUMBER ;
337 l_return_status VARCHAR2(1);
338 lp_tbcv_tbl tbcv_tbl_type;
339 lx_tbcv_tbl tbcv_tbl_type;
340 lx_tbcv_rec tbcv_rec_type;
341
342 BEGIN
343 OKL_API.init_msg_list(p_init_msg_list);
344 SAVEPOINT tbc_insert;
345
346
347 l_api_version := p_api_version ;
348 l_init_msg_list := p_init_msg_list ;
349 l_return_status := x_return_status ;
350 l_msg_count := x_msg_count ;
351 l_msg_data := x_msg_data ;
352 lp_tbcv_tbl := p_tbcv_tbl;
353 lx_tbcv_tbl := x_tbcv_tbl;
354 FOR i IN lp_tbcv_tbl.FIRST..lp_tbcv_tbl.LAST LOOP
355 IF lp_tbcv_tbl.EXISTS(i) THEN
356 l_return_status := validate_record (lp_tbcv_tbl(i), i);
357
358 IF ( l_return_status = FND_API.G_RET_STS_ERROR ) THEN
359 RAISE FND_API.G_EXC_ERROR;
360 ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR ) THEN
361 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
362 END IF;
363
364 okl_tbc_pvt.insert_row(
365 l_api_version
366 ,l_init_msg_list
367 ,l_return_status
368 ,l_msg_count
369 ,l_msg_data
370 ,lp_tbcv_tbl(i)
371 ,lx_tbcv_rec);
372 lx_tbcv_tbl(i) := lx_tbcv_rec;
373
374 IF ( l_return_status = FND_API.G_RET_STS_ERROR ) THEN
375 RAISE FND_API.G_EXC_ERROR;
376 ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR ) THEN
377 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
378 END IF;
379
380 END IF;
381
382 END LOOP;
383
384 --Assign value to OUT variables
385 x_tbcv_tbl := lx_tbcv_tbl;
386 x_return_status := l_return_status ;
387 x_msg_count := l_msg_count ;
388 x_msg_data := l_msg_data ;
389
390
391 EXCEPTION
392
393 WHEN FND_API.G_EXC_ERROR THEN
394 ROLLBACK TO tbc_insert;
395 x_return_status := FND_API.G_RET_STS_ERROR;
396 x_msg_count := l_msg_count ;
397 x_msg_data := l_msg_data ;
398 FND_MSG_PUB.count_and_get(
399 p_count => x_msg_count
400 ,p_data => x_msg_data);
401 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
402 ROLLBACK TO tbc_insert;
403 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
404 x_msg_count := l_msg_count ;
405 x_msg_data := l_msg_data ;
406 FND_MSG_PUB.count_and_get(
407 p_count => x_msg_count
408 ,p_data => x_msg_data);
409 WHEN OTHERS THEN
410 ROLLBACK TO tbc_insert;
411 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
412 x_msg_count := l_msg_count ;
413 x_msg_data := l_msg_data ;
414 FND_MSG_PUB.ADD_EXC_MSG('OKL_TBC_DEFINITIONS_PUB','insert_tbc_definition');
415 FND_MSG_PUB.count_and_get(
416 p_count => x_msg_count
417 ,p_data => x_msg_data);
418 END insert_tbc_definition;
419
420 PROCEDURE lock_tbc_definition(
421 p_api_version IN NUMBER,
422 p_init_msg_list IN VARCHAR2,
423 x_return_status OUT NOCOPY VARCHAR2,
424 x_msg_count OUT NOCOPY NUMBER,
425 x_msg_data OUT NOCOPY VARCHAR2,
426 p_tbcv_rec IN tbcv_rec_type) IS
427
428 BEGIN
429 okl_tbc_pvt.lock_row(
430 p_api_version,
431 p_init_msg_list,
432 x_return_status,
433 x_msg_count,
434 x_msg_data,
435 p_tbcv_rec);
436
437 IF ( x_return_status = FND_API.G_RET_STS_ERROR ) THEN
438 RAISE FND_API.G_EXC_ERROR;
439 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR ) THEN
440 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
441 END IF;
442
443
444 EXCEPTION
445
446 WHEN FND_API.G_EXC_ERROR THEN
447 FND_MSG_PUB.count_and_get(
448 p_count => x_msg_count
449 ,p_data => x_msg_data);
450 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
451 FND_MSG_PUB.count_and_get(
452 p_count => x_msg_count
453 ,p_data => x_msg_data);
454 WHEN OTHERS THEN
455 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
456 FND_MSG_PUB.ADD_EXC_MSG('OKL_TBC_DEFINITIONS_PUB','lock_tbc_definition');
457 FND_MSG_PUB.count_and_get(
458 p_count => x_msg_count
459 ,p_data => x_msg_data);
460 END lock_tbc_definition;
461
462
463 PROCEDURE lock_tbc_definition(
464 p_api_version IN NUMBER
465 ,p_init_msg_list IN VARCHAR2
466 ,x_return_status OUT NOCOPY VARCHAR2
467 ,x_msg_count OUT NOCOPY NUMBER
468 ,x_msg_data OUT NOCOPY VARCHAR2
469 ,p_tbcv_tbl IN tbcv_tbl_type) IS
470
471 BEGIN
472 okl_tbc_pvt.lock_row(
473 p_api_version,
474 p_init_msg_list,
475 x_return_status,
476 x_msg_count,
477 x_msg_data,
478 p_tbcv_tbl);
479
480 IF ( x_return_status = FND_API.G_RET_STS_ERROR ) THEN
481 RAISE FND_API.G_EXC_ERROR;
482 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR ) THEN
483 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
484 END IF;
485
486
487 EXCEPTION
488
489 WHEN FND_API.G_EXC_ERROR THEN
490 FND_MSG_PUB.count_and_get(
491 p_count => x_msg_count
492 ,p_data => x_msg_data);
493 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
494 FND_MSG_PUB.count_and_get(
495 p_count => x_msg_count
496 ,p_data => x_msg_data);
497 WHEN OTHERS THEN
498 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
499 FND_MSG_PUB.ADD_EXC_MSG('OKL_TBC_DEFINITIONS_PUB','lock_tbc_definition');
500 FND_MSG_PUB.count_and_get(
501 p_count => x_msg_count
502 ,p_data => x_msg_data);
503 END lock_tbc_definition;
504
505
506 PROCEDURE update_tbc_definition(
507 p_api_version IN NUMBER
508 ,p_init_msg_list IN VARCHAR2
509 ,x_return_status OUT NOCOPY VARCHAR2
510 ,x_msg_count OUT NOCOPY NUMBER
511 ,x_msg_data OUT NOCOPY VARCHAR2
512 ,p_tbcv_rec IN tbcv_rec_type
513 ,x_tbcv_rec OUT NOCOPY tbcv_rec_type) IS
514
515 l_api_version NUMBER ;
516 l_init_msg_list VARCHAR2(1) ;
517 l_return_status VARCHAR2(1);
518 l_msg_count NUMBER ;
519 l_msg_data VARCHAR2(2000);
520 lp_tbcv_rec tbcv_rec_type;
521 lx_tbcv_rec tbcv_rec_type;
522
523 BEGIN
524
525 SAVEPOINT tbc_update;
526
527
528 l_api_version := p_api_version ;
529 l_init_msg_list := p_init_msg_list ;
530 l_return_status := x_return_status ;
531 l_msg_count := x_msg_count ;
532 l_msg_data := x_msg_data ;
533 lp_tbcv_rec := p_tbcv_rec;
534 lx_tbcv_rec := x_tbcv_rec;
535
536
537
538
539
540 okl_tbc_pvt.update_row(
541 l_api_version
542 ,l_init_msg_list
543 ,l_return_status
544 ,l_msg_count
545 ,l_msg_data
546 ,lp_tbcv_rec
547 ,lx_tbcv_rec);
548
549 IF ( l_return_status = FND_API.G_RET_STS_ERROR ) THEN
550 RAISE FND_API.G_EXC_ERROR;
551 ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR ) THEN
552 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
553 END IF;
554
555 --Copy value of OUT variable in the IN record type
556 lp_tbcv_rec := lx_tbcv_rec;
557
558
559
560
561 --Assign value to OUT variables
562 x_tbcv_rec := lx_tbcv_rec;
563 x_return_status := l_return_status ;
564 x_msg_count := l_msg_count ;
565 x_msg_data := l_msg_data ;
566
567
568 EXCEPTION
569
570 WHEN FND_API.G_EXC_ERROR THEN
571 ROLLBACK TO tbc_update;
572 x_return_status := FND_API.G_RET_STS_ERROR;
573 x_msg_count := l_msg_count ;
574 x_msg_data := l_msg_data ;
575 FND_MSG_PUB.count_and_get(
576 p_count => x_msg_count
577 ,p_data => x_msg_data);
578 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
579 ROLLBACK TO tbc_update;
580 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
581 x_msg_count := l_msg_count ;
582 x_msg_data := l_msg_data ;
583 FND_MSG_PUB.count_and_get(
584 p_count => x_msg_count
585 ,p_data => x_msg_data);
586 WHEN OTHERS THEN
587 ROLLBACK TO tbc_update;
588 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
589 x_msg_count := l_msg_count ;
590 x_msg_data := l_msg_data ;
591 FND_MSG_PUB.ADD_EXC_MSG('OKL_TBC_DEFINITIONS_PUB','update_tbc_definition');
592 FND_MSG_PUB.count_and_get(
593 p_count => x_msg_count
594 ,p_data => x_msg_data);
595 END update_tbc_definition;
596
597 PROCEDURE update_tbc_definition(
598 p_api_version IN NUMBER,
599 p_init_msg_list IN VARCHAR2,
600 x_return_status OUT NOCOPY VARCHAR2,
601 x_msg_count OUT NOCOPY NUMBER,
602 x_msg_data OUT NOCOPY VARCHAR2,
603 p_tbcv_tbl IN tbcv_tbl_type,
604 x_tbcv_tbl OUT NOCOPY tbcv_tbl_type) IS
605
606 l_api_version NUMBER;
607 l_init_msg_list VARCHAR2(200);
608 l_msg_data VARCHAR2(100);
609 l_msg_count NUMBER ;
610 l_return_status VARCHAR2(1);
611 lp_tbcv_tbl tbcv_tbl_type;
612 lx_tbcv_tbl tbcv_tbl_type;
613
614 BEGIN
615
616 SAVEPOINT tbc_update;
617
618
619 lp_tbcv_tbl := p_tbcv_tbl;
620 lx_tbcv_tbl := x_tbcv_tbl;
621 l_api_version := p_api_version ;
622 l_init_msg_list := p_init_msg_list ;
623 l_msg_data := x_msg_data ;
624 l_msg_count := x_msg_count ;
625
626
627
628
629
630 okl_tbc_pvt.update_row(
631 l_api_version
632 ,l_init_msg_list
633 ,l_return_status
634 ,l_msg_count
635 ,l_msg_data
636 ,lp_tbcv_tbl
637 ,lx_tbcv_tbl);
638
639 IF ( l_return_status = FND_API.G_RET_STS_ERROR ) THEN
640 RAISE FND_API.G_EXC_ERROR;
641 ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR ) THEN
642 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
643 END IF;
644
645 --Copy value of OUT variable in the IN record type
646 lp_tbcv_tbl := lx_tbcv_tbl;
647
648
649
650 --Assign value to OUT variables
651 x_tbcv_tbl := lx_tbcv_tbl;
652 x_return_status := l_return_status ;
653 x_msg_count := l_msg_count ;
654 x_msg_data := l_msg_data ;
655
656
657 EXCEPTION
658
659 WHEN FND_API.G_EXC_ERROR THEN
660 ROLLBACK TO tbc_update;
661 x_return_status := FND_API.G_RET_STS_ERROR;
662 x_msg_count := l_msg_count ;
663 x_msg_data := l_msg_data ;
664 FND_MSG_PUB.count_and_get(
665 p_count => x_msg_count
666 ,p_data => x_msg_data);
667 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
668 ROLLBACK TO tbc_update;
669 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
670 x_msg_count := l_msg_count ;
671 x_msg_data := l_msg_data ;
672 FND_MSG_PUB.count_and_get(
673 p_count => x_msg_count
674 ,p_data => x_msg_data);
675 WHEN OTHERS THEN
676 ROLLBACK TO tbc_update;
677 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
678 x_msg_count := l_msg_count ;
679 x_msg_data := l_msg_data ;
680 FND_MSG_PUB.ADD_EXC_MSG('OKL_TBC_DEFINITIONS_PUB','update_tbc_definition');
681 FND_MSG_PUB.count_and_get(
682 p_count => x_msg_count
683 ,p_data => x_msg_data);
684 END update_tbc_definition;
685
686 --Put custom code for cascade delete by developer
687 PROCEDURE delete_tbc_definition(
688 p_api_version IN NUMBER,
689 p_init_msg_list IN VARCHAR2,
690 x_return_status OUT NOCOPY VARCHAR2,
691 x_msg_count OUT NOCOPY NUMBER,
692 x_msg_data OUT NOCOPY VARCHAR2,
693 p_tbcv_rec IN tbcv_rec_type) IS
694
695 i NUMBER :=0;
696 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
697 l_api_version NUMBER ;
698 l_init_msg_list VARCHAR2(1) ;
699 l_msg_data VARCHAR2(2000);
700 l_msg_count NUMBER ;
701 lp_tbcv_rec tbcv_rec_type;
702 --lx_tbcv_rec tbcv_rec_type;
703
704 BEGIN
705
706 SAVEPOINT tbc_delete;
707
708
709 l_api_version := p_api_version ;
710 l_init_msg_list := p_init_msg_list ;
711 l_msg_data := x_msg_data;
712 l_msg_count := x_msg_count ;
713 lp_tbcv_rec := p_tbcv_rec;
714 --lx_tbcv_rec := p_tbcv_rec;
715
716
717
718
719 --Delete the Master
720 okl_tbc_pvt.delete_row(
721 l_api_version
722 ,l_init_msg_list
723 ,l_return_status
724 ,l_msg_count
725 ,l_msg_data
726 ,lp_tbcv_rec);
727
728 IF ( l_return_status = FND_API.G_RET_STS_ERROR ) THEN
729 RAISE FND_API.G_EXC_ERROR;
730 ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR ) THEN
731 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
732 END IF;
733
734
735
736
737
738 --Assign value to OUT variables
739 x_msg_count := l_msg_count ;
740 x_msg_data := l_msg_data ;
741 x_return_status := l_return_status ;
742
743 EXCEPTION
744
745 WHEN FND_API.G_EXC_ERROR THEN
746 ROLLBACK TO tbc_delete;
747 x_return_status := FND_API.G_RET_STS_ERROR;
748 x_msg_count := l_msg_count ;
749 x_msg_data := l_msg_data ;
750 FND_MSG_PUB.count_and_get(
751 p_count => x_msg_count
752 ,p_data => x_msg_data);
753 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
754 ROLLBACK TO tbc_delete;
755 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
756 x_msg_count := l_msg_count ;
757 x_msg_data := l_msg_data ;
758 FND_MSG_PUB.count_and_get(
759 p_count => x_msg_count
760 ,p_data => x_msg_data);
761 WHEN OTHERS THEN
762 ROLLBACK TO tbc_delete;
763 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
764 x_msg_count := l_msg_count ;
765 x_msg_data := l_msg_data ;
766 FND_MSG_PUB.ADD_EXC_MSG('OKL_TBC_DEFINITIONS_PUB','delete_tbc_definition');
767 FND_MSG_PUB.count_and_get(
768 p_count => x_msg_count
769 ,p_data => x_msg_data);
770 END delete_tbc_definition;
771
772
773 PROCEDURE delete_tbc_definition(
774 p_api_version IN NUMBER,
775 p_init_msg_list IN VARCHAR2,
776 x_return_status OUT NOCOPY VARCHAR2,
777 x_msg_count OUT NOCOPY NUMBER,
778 x_msg_data OUT NOCOPY VARCHAR2,
779 p_tbcv_tbl IN tbcv_tbl_type) IS
780
781 i NUMBER := 0;
782 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
783 l_api_version NUMBER := p_api_version ;
784 l_init_msg_list VARCHAR2(1) := p_init_msg_list ;
785 l_msg_data VARCHAR2(2000);
786 l_msg_count NUMBER ;
787 lp_tbcv_tbl tbcv_tbl_type;
788 --lx_tbcv_tbl tbcv_tbl_type;
789
790 BEGIN
791
792 SAVEPOINT tbc_delete;
793
794
795 l_api_version := p_api_version ;
796 l_init_msg_list := p_init_msg_list ;
797 l_msg_data := x_msg_data;
798 l_msg_count := x_msg_count ;
799 lp_tbcv_tbl := p_tbcv_tbl;
800 --lx_tbcv_tbl := p_tbcv_tbl;
801
802
803
804
805 --Delete the Master
806 okl_tbc_pvt.delete_row(
807 l_api_version
808 ,l_init_msg_list
809 ,l_return_status
810 ,l_msg_count
811 ,l_msg_data
812 ,lp_tbcv_tbl);
813
814 IF ( l_return_status = FND_API.G_RET_STS_ERROR ) THEN
815 RAISE FND_API.G_EXC_ERROR;
816 ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR ) THEN
817 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
818 END IF;
819
820
821
822
823
824 --Assign value to OUT variables
825 x_msg_count := l_msg_count ;
826 x_msg_data := l_msg_data ;
827 x_return_status := l_return_status ;
828 EXCEPTION
829
830 WHEN FND_API.G_EXC_ERROR THEN
831 ROLLBACK TO tbc_delete;
832 x_return_status := FND_API.G_RET_STS_ERROR;
833 x_msg_count := l_msg_count ;
834 x_msg_data := l_msg_data ;
835 FND_MSG_PUB.count_and_get(
836 p_count => x_msg_count
837 ,p_data => x_msg_data);
838 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
839 ROLLBACK TO tbc_delete;
840 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
841 x_msg_count := l_msg_count ;
842 x_msg_data := l_msg_data ;
843 FND_MSG_PUB.count_and_get(
844 p_count => x_msg_count
845 ,p_data => x_msg_data);
846 WHEN OTHERS THEN
847 ROLLBACK TO tbc_delete;
848 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
849 x_msg_count := l_msg_count ;
850 x_msg_data := l_msg_data ;
851 FND_MSG_PUB.ADD_EXC_MSG('OKL_TBC_DEFINITIONS_PUB','delete_tbc_definition');
852 FND_MSG_PUB.count_and_get(
853 p_count => x_msg_count
854 ,p_data => x_msg_data);
855 END delete_tbc_definition;
856
857
858
859 PROCEDURE validate_tbc_definition(
860 p_api_version IN NUMBER,
861 p_init_msg_list IN VARCHAR2,
862 x_return_status OUT NOCOPY VARCHAR2,
863 x_msg_count OUT NOCOPY NUMBER,
864 x_msg_data OUT NOCOPY VARCHAR2,
865 p_tbcv_rec IN tbcv_rec_type) IS
866
867 l_api_version NUMBER ;
868 l_init_msg_list VARCHAR2(1) ;
869 l_msg_data VARCHAR2(2000);
870 l_msg_count NUMBER ;
871 lp_tbcv_rec tbcv_rec_type;
872 lx_tbcv_rec tbcv_rec_type;
873 l_return_status VARCHAR2(1);
874
875 BEGIN
876
877 SAVEPOINT tbc_validate;
878
879
880 l_api_version := p_api_version ;
881 l_init_msg_list := p_init_msg_list ;
882 l_msg_data := x_msg_data;
883 l_msg_count := x_msg_count ;
884 lp_tbcv_rec := p_tbcv_rec;
885 lx_tbcv_rec := p_tbcv_rec;
886
887
888
889
890 okl_tbc_pvt.validate_row(
891 l_api_version
892 ,l_init_msg_list
893 ,l_return_status
894 ,l_msg_count
895 ,l_msg_data
896 ,lp_tbcv_rec);
897
898 IF ( l_return_status = FND_API.G_RET_STS_ERROR ) THEN
899 RAISE FND_API.G_EXC_ERROR;
900 ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR ) THEN
901 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
902 END IF;
903
904 --Copy value of OUT variable in the IN record type
905 lp_tbcv_rec := lx_tbcv_rec;
906
907
908
909
910
911
912
913
914 --Assign value to OUT variables
915 x_msg_count := l_msg_count ;
916 x_msg_data := l_msg_data ;
917
918 x_return_status := l_return_status ;
919
920 EXCEPTION
921
922 WHEN FND_API.G_EXC_ERROR THEN
923 ROLLBACK TO tbc_validate;
924 x_return_status := FND_API.G_RET_STS_ERROR;
925 x_msg_count := l_msg_count ;
926 x_msg_data := l_msg_data ;
927 FND_MSG_PUB.count_and_get(
928 p_count => x_msg_count
929 ,p_data => x_msg_data);
930 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
931 ROLLBACK TO tbc_validate;
932 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
933 x_msg_count := l_msg_count ;
934 x_msg_data := l_msg_data ;
935 FND_MSG_PUB.count_and_get(
936 p_count => x_msg_count
937 ,p_data => x_msg_data);
938 WHEN OTHERS THEN
939 ROLLBACK TO tbc_validate;
940 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
941 x_msg_count := l_msg_count ;
942 x_msg_data := l_msg_data ;
943 FND_MSG_PUB.ADD_EXC_MSG('OKL_TBC_DEFINITIONS_PUB','validate_tbc_definition');
944 FND_MSG_PUB.count_and_get(
945 p_count => x_msg_count
946 ,p_data => x_msg_data);
947 END validate_tbc_definition;
948
949 PROCEDURE validate_tbc_definition(
950 p_api_version IN NUMBER,
951 p_init_msg_list IN VARCHAR2,
952 x_return_status OUT NOCOPY VARCHAR2,
953 x_msg_count OUT NOCOPY NUMBER,
954 x_msg_data OUT NOCOPY VARCHAR2,
955 p_tbcv_tbl IN tbcv_tbl_type) IS
956
957 l_api_version NUMBER ;
958 l_init_msg_list VARCHAR2(1) ;
959 l_msg_data VARCHAR2(2000);
960 l_msg_count NUMBER ;
961 lp_tbcv_tbl tbcv_tbl_type;
962 lx_tbcv_tbl tbcv_tbl_type;
963 l_return_status VARCHAR2(1);
964
965 BEGIN
966
967 SAVEPOINT tbc_validate;
968
969
970 l_api_version := p_api_version ;
971 l_init_msg_list := p_init_msg_list ;
972 l_msg_data := x_msg_data;
973 l_msg_count := x_msg_count ;
974 lp_tbcv_tbl := p_tbcv_tbl;
975 lx_tbcv_tbl := p_tbcv_tbl;
976
977
978
979 okl_tbc_pvt.validate_row(
980 p_api_version
981 ,p_init_msg_list
982 ,x_return_status
983 ,x_msg_count
984 ,x_msg_data
985 ,lp_tbcv_tbl);
986
987 IF ( l_return_status = FND_API.G_RET_STS_ERROR ) THEN
988 RAISE FND_API.G_EXC_ERROR;
989 ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR ) THEN
990 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
991 END IF;
992
993 --Copy value of OUT variable in the IN record type
994 lp_tbcv_tbl := lx_tbcv_tbl;
995
996
997
998
999
1000 --Assign value to OUT variables
1001 x_msg_count := l_msg_count ;
1002 x_msg_data := l_msg_data ;
1003
1004 x_return_status := l_return_status ;
1005
1006 EXCEPTION
1007
1008 WHEN FND_API.G_EXC_ERROR THEN
1009 ROLLBACK TO tbc_validate;
1010 x_return_status := FND_API.G_RET_STS_ERROR;
1011 x_msg_count := l_msg_count ;
1012 x_msg_data := l_msg_data ;
1013 FND_MSG_PUB.count_and_get(
1014 p_count => x_msg_count
1015 ,p_data => x_msg_data);
1016 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1017 ROLLBACK TO tbc_validate;
1018 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1019 x_msg_count := l_msg_count ;
1020 x_msg_data := l_msg_data ;
1021 FND_MSG_PUB.count_and_get(
1022 p_count => x_msg_count
1023 ,p_data => x_msg_data);
1024 WHEN OTHERS THEN
1025 ROLLBACK TO tbc_validate;
1026 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1027 x_msg_count := l_msg_count ;
1028 x_msg_data := l_msg_data ;
1029 FND_MSG_PUB.ADD_EXC_MSG('OKL_TBC_DEFINITIONS_PUB','validate_tbc_definition');
1030 FND_MSG_PUB.count_and_get(
1031 p_count => x_msg_count
1032 ,p_data => x_msg_data);
1033 END validate_tbc_definition;
1034
1035 END OKL_TBC_DEFINITIONS_PUB;