[Home] [Help]
PACKAGE BODY: APPS.OKL_VERSION_PVT
Source
1 package body okl_version_pvt as
2 /* $Header: OKLRVERB.pls 120.6 2006/11/13 07:24:44 dpsingh noship $ */
3 ----------------------------------------------------------------------------------------
4 -- GLOBAL OKL MESSAGES
5 ----------------------------------------------------------------------------------------
6 G_INVALID_VALUE CONSTANT VARCHAR2(200) := 'OKL_INVALID_VALUE';
7 G_REQUIRED_VALUE CONSTANT VARCHAR2(200) := 'OKL_REQUIRED_VALUE';
8 G_NO_MATCHING_RECORD CONSTANT VARCHAR2(200) := 'OKL_LLA_NO_MATCHING_RECORD';
9 G_LINE_RECORD CONSTANT VARCHAR2(200) := 'OKL_LLA_LINE_RECORD';
10 G_ITEM_RECORD CONSTANT VARCHAR2(200) := 'OKL_LLA_ITEM_RECORD';
11 G_UNEXPECTED_ERROR CONSTANT VARCHAR2(200) := 'OKL_CONTRACTS_UNEXPECTED_ERROR';
12 G_CREATE_VER_ERROR CONSTANT VARCHAR2(200) := 'OKL_LLA_CREATE_VER_ERROR';
13 G_RESTORE_VER_ERROR CONSTANT VARCHAR2(200) := 'OKL_LLA_RESTORE_VER_ERROR';
14 G_DELETE_VER_ERROR CONSTANT VARCHAR2(200) := 'OKL_LLA_DELETE_VER_ERROR ';
15 G_DELETE_BASE_ERROR CONSTANT VARCHAR2(200) := 'OKL_LLA_DELETE_BASE_ERROR';
16 G_STATUS CONSTANT VARCHAR2(200) := 'OKL_LLA_STATUS';
17 G_FIN_LINE_LTY_CODE CONSTANT OKC_LINE_STYLES_V.LTY_CODE%TYPE := 'FREE_FORM1';
18 G_MODEL_LINE_LTY_CODE CONSTANT OKC_LINE_STYLES_V.LTY_CODE%TYPE := 'ITEM';
19 G_ADDON_LINE_LTY_CODE CONSTANT OKC_LINE_STYLES_V.LTY_CODE%TYPE := 'ADD_ITEM';
20 G_FA_LINE_LTY_CODE CONSTANT OKC_LINE_STYLES_V.LTY_CODE%TYPE := 'FIXED_ASSET';
21 G_INST_LINE_LTY_CODE CONSTANT OKC_LINE_STYLES_V.LTY_CODE%TYPE := 'FREE_FORM2';
22 G_IB_LINE_LTY_CODE CONSTANT OKC_LINE_STYLES_V.LTY_CODE%TYPE := 'INST_ITEM';
23 G_ID2 CONSTANT VARCHAR2(200) := '#';
24 G_TRY_TYPE CONSTANT OKL_TRX_TYPES_V.TRY_TYPE%TYPE := 'TIE';
25 G_TLS_TYPE CONSTANT OKC_LINE_STYLES_V.LSE_TYPE%TYPE := 'TLS';
26 G_SLS_TYPE CONSTANT OKC_LINE_STYLES_V.LSE_TYPE%TYPE := 'SLS';
27 G_LEASE_SCS_CODE CONSTANT OKC_K_HEADERS_V.SCS_CODE%TYPE := 'LEASE';
28 G_LOAN_SCS_CODE CONSTANT OKC_K_HEADERS_V.SCS_CODE%TYPE := 'LOAN';
29 ----------------------------------------------------------------------------------------
30 -- GLOBAL EXCEPTION
31 ----------------------------------------------------------------------------------------
32 G_COL_NAME_TOKEN CONSTANT VARCHAR2(200) := OKL_API.G_COL_NAME_TOKEN;
33 G_EXCEPTION_HALT_VALIDATION EXCEPTION;
34 G_EXCEPTION_STOP_VALIDATION EXCEPTION;
35 G_API_TYPE CONSTANT VARCHAR2(4) := '_PVT';
36 G_API_VERSION CONSTANT NUMBER := 1.0;
37 G_SCOPE CONSTANT VARCHAR2(4) := '_PVT';
38 ----------------------------------------------------------------------------------------
39 --Added by dpsingh for LE uptake
40 CURSOR contract_num_csr (p_ctr_id1 NUMBER) IS
41 SELECT contract_number
42 FROM OKC_K_HEADERS_B
43 WHERE id = p_ctr_id1;
44
45 -- Start of comments
46 -- Procedure Name : Create_fa_version
47 -- Description : creates Records FA in
48 -- OKL_CONTRACT_ASSET_H
49 -- Business Rules :
50 -- Parameters :
51 -- Version : 1.0
52 -- End of comments
53 PROCEDURE Create_fa_version(
54 p_api_version IN NUMBER,
55 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
56 x_return_status OUT NOCOPY VARCHAR2,
57 x_msg_count OUT NOCOPY NUMBER,
58 x_msg_data OUT NOCOPY VARCHAR2,
59 p_chr_id IN NUMBER,
60 p_major_version IN NUMBER) IS
61 l_api_version CONSTANT NUMBER := 1;
62 l_api_name CONSTANT VARCHAR2(30) := 'OKL_VERSION_CREATE_FA';
63 l_vfav_rec OKL_VERSION_FA_PUB.vfav_rec_type;
64 lx_vfav_rec OKL_VERSION_FA_PUB.vfav_rec_type;
65
66 CURSOR c_get_fa_line_id(p_dnz_chr_id OKC_K_LINES_V.DNZ_CHR_ID%TYPE)
67 IS
68 SELECT cle.id,
69 cle.dnz_chr_id,
70 cim.object1_id1,
71 cim.object1_id2
72 FROM okc_subclass_top_line stl,
73 okc_line_styles_b lse2,
74 okc_line_styles_b lse1,
75 okc_k_items_v cim,
76 okc_k_lines_v cle
77 WHERE cle.dnz_chr_id = p_dnz_chr_id
78 AND cle.id = cim.cle_id
79 AND cim.dnz_chr_id = cle.dnz_chr_id
80 AND lse1.id = cle.lse_id
81 AND lse1.lty_code = G_FA_LINE_LTY_CODE
82 AND lse1.lse_parent_id = lse2.id
83 AND lse2.lty_code = G_FIN_LINE_LTY_CODE
84 AND lse2.id = stl.lse_id
85 AND stl.scs_code in (G_LEASE_SCS_CODE,G_LOAN_SCS_CODE);
86
87 CURSOR c_asset_info(p_object1_id1 OKC_K_ITEMS_V.OBJECT1_ID1%TYPE,
88 p_object1_id2 OKC_K_ITEMS_V.OBJECT1_ID2%TYPE)
89 IS
90 SELECT *
91 FROM OKX_ASSETS_V
92 WHERE id1 = p_object1_id1
93 AND id2 = p_object1_id2;
94
95 r_asset_info c_asset_info%ROWTYPE;
96 r_get_fa_line_id c_get_fa_line_id%ROWTYPE;
97
98 --Bug# 2981308 : cursor to fetch asset key ccid
99 cursor l_fab_csr(p_asset_id in number) is
100 select fab.asset_key_ccid
101 from fa_additions_b fab
102 where fab.asset_id = p_asset_id;
103
104 l_asset_key_id fa_additions_b.asset_key_ccid%TYPE;
105 --Bug# 2981308
106 --Added by dpsingh for LE uptake
107 l_cntrct_number OKC_K_HEADERS_B.CONTRACT_NUMBER%TYPE;
108 l_legal_entity_id NUMBER;
109
110 BEGIN
111 x_return_status := OKL_API.G_RET_STS_SUCCESS;
112 --version FA Details
113 -- Call start_activity to create savepoint, check compatibility
114 -- and initialize message list
115 x_return_status := OKL_API.START_ACTIVITY (
116 l_api_name
117 ,p_init_msg_list
118 ,'_PVT'
119 ,x_return_status);
120 -- Check if activity started successfully
121 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
122 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
123 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
124 RAISE OKL_API.G_EXCEPTION_ERROR;
125 END IF;
126 FOR r_get_fa_line_id IN c_get_fa_line_id(p_chr_id) LOOP
127 IF c_get_fa_line_id%NOTFOUND THEN
128 OKL_API.set_message(p_app_name => G_APP_NAME,
129 p_msg_name => G_NO_MATCHING_RECORD,
130 p_token1 => G_COL_NAME_TOKEN,
131 p_token1_value => 'Fixed Asset Line id');
132 x_return_status := OKL_API.G_RET_STS_ERROR;
133 EXIT WHEN (x_return_status = OKL_API.G_RET_STS_ERROR);
134 END IF;
135 -- To get the Info all the fixed asset lines
136 -- And the build the VFAV record
137 OPEN c_asset_info(r_get_fa_line_id.object1_id1,
138 r_get_fa_line_id.object1_id2);
139 IF c_asset_info%NOTFOUND THEN
140 OKL_API.set_message(p_app_name => G_APP_NAME,
141 p_msg_name => G_NO_MATCHING_RECORD,
142 p_token1 => G_COL_NAME_TOKEN,
143 p_token1_value => 'OKX_ASSETS_V.OBJECT1_ID1');
144 x_return_status := OKL_API.G_RET_STS_ERROR;
145 EXIT WHEN (x_return_status = OKL_API.G_RET_STS_ERROR);
146 END IF;
147 FETCH c_asset_info INTO r_asset_info;
148 CLOSE c_asset_info;
149 l_vfav_rec.major_version := p_major_version;
150 l_vfav_rec.fa_cle_id := r_get_fa_line_id.id;
151 l_vfav_rec.dnz_chr_id := r_get_fa_line_id.dnz_chr_id;
152 l_vfav_rec.asset_number := r_asset_info.asset_number;
153 l_vfav_rec.name := r_asset_info.name;
154 l_vfav_rec.description := r_asset_info.description;
155 l_vfav_rec.asset_id := r_asset_info.asset_id;
156 l_vfav_rec.corporate_book := r_asset_info.corporate_book;
157 l_vfav_rec.life_in_months := r_asset_info.life_in_months;
158 l_vfav_rec.original_cost := r_asset_info.original_cost;
159 l_vfav_rec.cost := r_asset_info.cost;
160 l_vfav_rec.adjusted_cost := r_asset_info.adjusted_cost;
161 l_vfav_rec.current_units := r_asset_info.current_units;
162 l_vfav_rec.new_used := r_asset_info.new_used;
163 l_vfav_rec.in_service_date := r_asset_info.in_service_date;
164 l_vfav_rec.model_number := r_asset_info.model_number;
165 l_vfav_rec.asset_type := r_asset_info.asset_type;
166 l_vfav_rec.salvage_value := r_asset_info.salvage_value;
167 l_vfav_rec.percent_salvage_value := r_asset_info.percent_salvage_value;
168 l_vfav_rec.depreciation_category := r_asset_info.depreciation_category;
169 l_vfav_rec.deprn_method_code := r_asset_info.deprn_method_code;
170 l_vfav_rec.deprn_start_date := r_asset_info.deprn_start_date;
171 l_vfav_rec.rate_adjustment_factor := r_asset_info.rate_adjustment_factor;
172 l_vfav_rec.basic_rate := r_asset_info.basic_rate;
173 l_vfav_rec.adjusted_rate := r_asset_info.adjusted_rate;
174 l_vfav_rec.start_date_active := r_asset_info.start_date_active;
175 l_vfav_rec.end_date_active := r_asset_info.end_date_active;
176 l_vfav_rec.status := ltrim(rtrim(r_asset_info.status));
177 l_vfav_rec.primary_uom_code := r_asset_info.primary_uom_code;
178
179 --------------------------------------
180 --Bug# 2981308 : fetch asset key ccid
181 --------------------------------------
182 open l_fab_csr(p_asset_id => r_asset_info.asset_id);
183 fetch l_fab_csr into l_asset_key_id;
184 if l_fab_csr%NOTFOUND then
185 null;
186 end if;
187 close l_fab_csr;
188
189 l_vfav_rec.asset_key_id := l_asset_key_id;
190 -------------------------------------
191 --bug# 2981308 : fetch asset key ccid
192 ------------------------------------
193 --Added by dpsingh for LE Uptake
194 l_legal_entity_id := OKL_LEGAL_ENTITY_UTIL.get_khr_le_id(p_chr_id) ;
195 IF l_legal_entity_id IS NOT NULL THEN
196 l_vfav_rec.legal_entity_id := l_legal_entity_id;
197 ELSE
198 -- get the contract number
199 OPEN contract_num_csr(p_chr_id);
200 FETCH contract_num_csr INTO l_cntrct_number;
201 CLOSE contract_num_csr;
202 Okl_Api.set_message(p_app_name => g_app_name,
203 p_msg_name => 'OKL_LE_NOT_EXIST_CNTRCT',
204 p_token1 => 'CONTRACT_NUMBER',
205 p_token1_value => l_cntrct_number);
206 RAISE OKL_API.G_EXCEPTION_ERROR;
207 END IF;
208
209 -- Now we are going to install into OKL_CONTRACT_ASSET_H table
210 OKL_VERSION_FA_PUB.create_version_fa(
211 p_api_version => p_api_version,
212 p_init_msg_list => p_init_msg_list,
213 x_return_status => x_return_status,
214 x_msg_count => x_msg_count,
215 x_msg_data => x_msg_data,
216 p_vfav_rec => l_vfav_rec,
217 x_vfav_rec => lx_vfav_rec);
218 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
219 EXIT WHEN (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR);
220 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
221 EXIT WHEN (x_return_status = OKL_API.G_RET_STS_ERROR);
222 END IF;
223 END LOOP;
224 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
225 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
226 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
227 RAISE OKL_API.G_EXCEPTION_ERROR;
228 END IF;
229 OKL_API.END_ACTIVITY (x_msg_count,
230 x_msg_data );
231 EXCEPTION
232 WHEN OKL_API.G_EXCEPTION_ERROR THEN
233 --Bug# 2981308
234 if l_fab_csr%ISOPEN then
235 close l_fab_csr;
236 end if;
237 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
238 l_api_name,
239 G_PKG_NAME,
240 'OKL_API.G_RET_STS_ERROR',
241 x_msg_count,
242 x_msg_data,
243 '_PVT');
244 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
245 --Bug# 2981308
246 if l_fab_csr%ISOPEN then
247 close l_fab_csr;
248 end if;
249 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
250 l_api_name,
251 G_PKG_NAME,
252 'OKL_API.G_RET_STS_UNEXP_ERROR',
253 x_msg_count,
254 x_msg_data,
255 '_PVT');
256 WHEN OTHERS THEN
257 --Bug# 2981308
258 if l_fab_csr%ISOPEN then
259 close l_fab_csr;
260 end if;
261 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
262 l_api_name,
263 G_PKG_NAME,
264 'OTHERS',
265 x_msg_count,
266 x_msg_data,
267 '_PVT');
268 END Create_fa_version;
269 ----------------------------------------------------------------------------------------
270 -- Start of comments
271 -- Procedure Name : Create_ib_version
272 -- Description : creates Records IB in
273 -- OKL_CONTRACT_IB_H tables
274 -- Business Rules :
275 -- Parameters :
276 -- Version : 1.0
277 -- End of comments
278 PROCEDURE Create_ib_version(
279 p_api_version IN NUMBER,
280 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
281 x_return_status OUT NOCOPY VARCHAR2,
282 x_msg_count OUT NOCOPY NUMBER,
283 x_msg_data OUT NOCOPY VARCHAR2,
284 p_chr_id IN NUMBER,
285 p_major_version IN NUMBER) IS
286 l_api_version CONSTANT NUMBER := 1;
287 l_api_name CONSTANT VARCHAR2(30) := 'OKL_VERSION_CREATE_IB';
288 l_vibv_rec OKL_VERSION_IB_PUB.vibv_rec_type;
289 lx_vibv_rec OKL_VERSION_IB_PUB.vibv_rec_type;
290
291 CURSOR c_get_ib_line_id(p_dnz_chr_id OKC_K_LINES_V.DNZ_CHR_ID%TYPE)
292 IS
293 SELECT cle.id,
294 cle.dnz_chr_id,
295 cim.object1_id1,
296 cim.object1_id2
297 FROM okc_subclass_top_line stl,
298 okc_line_styles_b lse3,
299 okc_line_styles_b lse2,
300 okc_line_styles_b lse1,
301 okc_k_items_v cim,
302 okc_k_lines_b cle
303 WHERE cle.lse_id = lse1.id
304 AND cle.id = cim.cle_id
305 AND cle.dnz_chr_id = cim.dnz_chr_id
306 AND lse1.lty_code = G_IB_LINE_LTY_CODE
307 AND lse1.lse_parent_id = lse2.id
308 AND lse2.lty_code = G_INST_LINE_LTY_CODE
309 AND lse2.lse_parent_id = lse3.id
310 AND lse3.lty_code = G_FIN_LINE_LTY_CODE
311 AND lse3.id = stl.lse_id
312 AND stl.scs_code in (G_LEASE_SCS_CODE,G_LOAN_SCS_CODE)
313 AND cle.cle_id in (SELECT cle.id
314 FROM okc_subclass_top_line stl,
315 okc_line_styles_b lse2,
316 okc_line_styles_b lse1,
317 okc_k_lines_b cle
318 WHERE cle.dnz_chr_id = p_dnz_chr_id
319 AND cle.lse_id = lse1.id
320 AND lse1.lty_code = G_INST_LINE_LTY_CODE
321 AND lse1.lse_parent_id = lse2.id
322 AND lse2.lty_code = G_FIN_LINE_LTY_CODE
323 AND lse2.id = stl.lse_id
324 AND stl.scs_code in (G_LEASE_SCS_CODE,G_LOAN_SCS_CODE));
325
326
327 CURSOR c_ib_info(p_object1_id1 OKC_K_ITEMS_V.OBJECT1_ID1%TYPE,
328 p_object1_id2 OKC_K_ITEMS_V.OBJECT1_ID2%TYPE) IS
329 SELECT *
330 FROM OKX_INSTALL_ITEMS_V
331 WHERE id1 = p_object1_id1
332 AND id2 = p_object1_id2;
333
334 r_ib_info c_ib_info%ROWTYPE;
335 r_get_ib_line_id c_get_ib_line_id%ROWTYPE;
336
337 --Added by dpsingh for LE uptake
338 l_cntrct_number OKC_K_HEADERS_B.CONTRACT_NUMBER%TYPE;
339 l_legal_entity_id NUMBER;
340
341 BEGIN
342 x_return_status := OKL_API.G_RET_STS_SUCCESS;
343 --version IB Details
344 -- Call start_activity to create savepoint, check compatibility
345 -- and initialize message list
346 x_return_status := OKL_API.START_ACTIVITY (
347 l_api_name
348 ,p_init_msg_list
349 ,'_PVT'
350 ,x_return_status);
351 -- Check if activity started successfully
352 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
353 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
354 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
355 RAISE OKL_API.G_EXCEPTION_ERROR;
356 END IF;
357 FOR r_get_ib_line_id IN c_get_ib_line_id(p_chr_id) LOOP
358 IF c_get_ib_line_id%NOTFOUND THEN
359 OKL_API.set_message(p_app_name => G_APP_NAME,
360 p_msg_name => G_NO_MATCHING_RECORD,
361 p_token1 => G_COL_NAME_TOKEN,
362 p_token1_value => 'Install Base line id');
363 x_return_status := OKL_API.G_RET_STS_ERROR;
364 EXIT WHEN (x_return_status = OKL_API.G_RET_STS_ERROR);
365 END IF;
366 -- To get the Info all the fixed asset lines
367 -- And the build the VFAV record
368 OPEN c_ib_info(r_get_ib_line_id.object1_id1,
369 r_get_ib_line_id.object1_id2);
370 IF c_ib_info%NOTFOUND THEN
371 OKL_API.set_message(p_app_name => G_APP_NAME,
372 p_msg_name => G_NO_MATCHING_RECORD,
373 p_token1 => G_COL_NAME_TOKEN,
374 p_token1_value => 'OKX_INSTALL_ITEMS_V.OBJECT1_ID1');
375 x_return_status := OKL_API.G_RET_STS_ERROR;
376 EXIT WHEN (x_return_status = OKL_API.G_RET_STS_ERROR);
377 END IF;
378 FETCH c_ib_info INTO r_ib_info;
379 CLOSE c_ib_info;
380 l_vibv_rec.major_version := p_major_version;
381 l_vibv_rec.ib_cle_id := r_get_ib_line_id.id;
382 l_vibv_rec.dnz_chr_id := r_get_ib_line_id.dnz_chr_id;
383 l_vibv_rec.name := r_ib_info.name;
384 l_vibv_rec.description := r_ib_info.description;
385 l_vibv_rec.inventory_item_id := r_ib_info.inventory_item_id;
386 l_vibv_rec.current_serial_number := r_ib_info.serial_number;
387 l_vibv_rec.install_site_use_id := r_ib_info.install_location_id;
388 l_vibv_rec.quantity := r_ib_info.quantity;
389
390 --Added by dpsingh for LE Uptake
391 l_legal_entity_id := OKL_LEGAL_ENTITY_UTIL.get_khr_le_id(p_chr_id) ;
392 IF l_legal_entity_id IS NOT NULL THEN
393 l_vibv_rec.legal_entity_id := l_legal_entity_id;
394 ELSE
395 -- get the contract number
396 OPEN contract_num_csr(p_chr_id);
397 FETCH contract_num_csr INTO l_cntrct_number;
398 CLOSE contract_num_csr;
399 Okl_Api.set_message(p_app_name => g_app_name,
400 p_msg_name => 'OKL_LE_NOT_EXIST_CNTRCT',
401 p_token1 => 'CONTRACT_NUMBER',
402 p_token1_value => l_cntrct_number);
403 RAISE OKL_API.G_EXCEPTION_ERROR;
404 END IF;
405 -- Now we are going to install into OKL_CONTRACT_IB_H table
406 OKL_VERSION_IB_PUB.create_version_ib(
407 p_api_version => p_api_version,
408 p_init_msg_list => p_init_msg_list,
409 x_return_status => x_return_status,
410 x_msg_count => x_msg_count,
411 x_msg_data => x_msg_data,
412 p_vibv_rec => l_vibv_rec,
413 x_vibv_rec => lx_vibv_rec);
414 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
415 EXIT WHEN (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR);
416 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
417 EXIT WHEN (x_return_status = OKL_API.G_RET_STS_ERROR);
418 END IF;
419 END LOOP;
420 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
421 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
422 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
423 RAISE OKL_API.G_EXCEPTION_ERROR;
424 END IF;
425 OKL_API.END_ACTIVITY (x_msg_count,
426 x_msg_data );
427 EXCEPTION
428 WHEN OKL_API.G_EXCEPTION_ERROR THEN
429 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
430 l_api_name,
431 G_PKG_NAME,
432 'OKL_API.G_RET_STS_ERROR',
433 x_msg_count,
434 x_msg_data,
435 '_PVT');
436 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
437 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
438 l_api_name,
439 G_PKG_NAME,
440 'OKL_API.G_RET_STS_UNEXP_ERROR',
441 x_msg_count,
442 x_msg_data,
443 '_PVT');
444 WHEN OTHERS THEN
445 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
446 l_api_name,
447 G_PKG_NAME,
448 'OTHERS',
449 x_msg_count,
450 x_msg_data,
451 '_PVT');
452 END Create_ib_version;
453 ---------------------------------------------------------------------------------------------------------
454 -- Start of comments
455 -- Procedure Name : version_contract
456 -- Description : creates new version of a contract (OKL part)
457 -- Business Rules :
458 -- Parameters :
459 -- Version : 1.0
460 -- End of comments
461 PROCEDURE version_contract_extra(
462 p_api_version IN NUMBER,
463 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
464 x_return_status OUT NOCOPY VARCHAR2,
465 x_msg_count OUT NOCOPY NUMBER,
466 x_msg_data OUT NOCOPY VARCHAR2,
467 p_chr_id IN NUMBER,
468 p_major_version IN NUMBER) IS
469 l_api_version CONSTANT NUMBER := 1;
470 l_api_name CONSTANT VARCHAR2(30) := 'OKL_VERSION_CREATE';
471
472 BEGIN
473 x_return_status := OKL_API.G_RET_STS_SUCCESS;
474 -- Call start_activity to create savepoint, check compatibility
475 -- and initialize message list
476 x_return_status := OKL_API.START_ACTIVITY (
477 l_api_name
478 ,p_init_msg_list
479 ,'_PVT'
480 ,x_return_status);
481 -- Check if activity started successfully
482 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
483 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
484 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
485 RAISE OKL_API.G_EXCEPTION_ERROR;
486 END IF;
487 --version contract header
488 x_return_status:=OKL_KHR_PVT.Create_Version(p_chr_id,
489 p_major_version);
490 --- If any errors happen abort API
491 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
492 OKL_API.set_message(p_app_name => G_APP_NAME,
493 p_msg_name => G_CREATE_VER_ERROR,
494 p_token1 => G_COL_NAME_TOKEN,
495 p_token1_value => 'OKL_K_HEADERS_H');
496 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
497 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
498 OKL_API.set_message(p_app_name => G_APP_NAME,
499 p_msg_name => G_CREATE_VER_ERROR,
500 p_token1 => G_COL_NAME_TOKEN,
501 p_token1_value => 'OKL_K_HEADERS_H');
502 RAISE OKL_API.G_EXCEPTION_ERROR;
503 END IF;
504 --version contract lines
505 x_return_status:=OKL_KLE_PVT.Create_Version(p_chr_id,
506 p_major_version);
507 --- If any errors happen abort API
508 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
509 OKL_API.set_message(p_app_name => G_APP_NAME,
510 p_msg_name => G_CREATE_VER_ERROR,
511 p_token1 => G_COL_NAME_TOKEN,
512 p_token1_value => 'OKL_K_LINES_H');
513 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
514 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
515 OKL_API.set_message(p_app_name => G_APP_NAME,
516 p_msg_name => G_CREATE_VER_ERROR,
517 p_token1 => G_COL_NAME_TOKEN,
518 p_token1_value => 'OKL_K_LINES_H');
519 RAISE OKL_API.G_EXCEPTION_ERROR;
520 END IF;
521 --version Supplier Invoice Details
522 x_return_status:=OKL_SID_PVT.Create_Version(p_chr_id,
523 p_major_version);
524 --- If any errors happen abort API
525 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
526 OKL_API.set_message(p_app_name => G_APP_NAME,
527 p_msg_name => G_CREATE_VER_ERROR,
528 p_token1 => G_COL_NAME_TOKEN,
529 p_token1_value => 'OKL_SUPP_INVOICE_DTLS_H');
530 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
531 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
532 OKL_API.set_message(p_app_name => G_APP_NAME,
533 p_msg_name => G_CREATE_VER_ERROR,
534 p_token1 => G_COL_NAME_TOKEN,
535 p_token1_value => 'OKL_SUPP_INVOICE_DTLS_H');
536 RAISE OKL_API.G_EXCEPTION_ERROR;
537 END IF;
538
539 --Version Party Payment Hdr, fmiao
540 x_return_status:=OKL_LDB_PVT.Create_Version(p_chr_id,
541 p_major_version);
542 --- If any errors happen abort API
543 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
544 OKL_API.set_message(p_app_name => G_APP_NAME,
545 p_msg_name => G_CREATE_VER_ERROR,
546 p_token1 => G_COL_NAME_TOKEN,
547 p_token1_value => 'OKL_PARTY_PAYMENT_HDR_H');
548 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
549 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
550 OKL_API.set_message(p_app_name => G_APP_NAME,
551 p_msg_name => G_CREATE_VER_ERROR,
552 p_token1 => G_COL_NAME_TOKEN,
553 p_token1_value => 'OKL_PARTY_PAYMENT_HDR_H');
554 RAISE OKL_API.G_EXCEPTION_ERROR;
555 END IF;
556
557 --------------
558 --Bug# 3143522
559 ---------------
560 --version party payment details
561 x_return_status:=OKL_PYD_PVT.Create_Version(p_chr_id,
562 p_major_version);
563 --- If any errors happen abort API
564 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
565 OKL_API.set_message(p_app_name => G_APP_NAME,
566 p_msg_name => G_CREATE_VER_ERROR,
567 p_token1 => G_COL_NAME_TOKEN,
568 p_token1_value => 'OKL_PARTY_PAYMENT_DTLS_H');
569 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
570 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
571 OKL_API.set_message(p_app_name => G_APP_NAME,
572 p_msg_name => G_CREATE_VER_ERROR,
573 p_token1 => G_COL_NAME_TOKEN,
574 p_token1_value => 'OKL_PARTY_PAYMENT_DTLS_H');
575 RAISE OKL_API.G_EXCEPTION_ERROR;
576 END IF;
577 --------------
578 --End Bug# 3143522
579 ---------------
580 --------------
581 --Bug# 4558486
582 --------------
583 --Version Okl_K_Party_Roles
584 x_return_status:=OKL_KPL_PVT.Create_Version(p_chr_id,
585 p_major_version);
586 --- If any errors happen abort API
587 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
588 OKL_API.set_message(p_app_name => G_APP_NAME,
589 p_msg_name => G_CREATE_VER_ERROR,
590 p_token1 => G_COL_NAME_TOKEN,
591 p_token1_value => 'OKL_K_PARTY_ROLES_H');
592 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
593 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
594 OKL_API.set_message(p_app_name => G_APP_NAME,
595 p_msg_name => G_CREATE_VER_ERROR,
596 p_token1 => G_COL_NAME_TOKEN,
597 p_token1_value => 'OKL_K_PARTY_ROLES_H');
598 RAISE OKL_API.G_EXCEPTION_ERROR;
599 END IF;
600 --------------
601 --End Bug# 4558486
602 --------------
603 --------------
604 --Bug# 3379716:
605 ---------------
606 --version streams
607 OKL_STREAMS_PVT.version_stream(
608 p_api_version => p_api_version,
609 p_init_msg_list => p_init_msg_list,
610 x_return_status => x_return_status,
611 x_msg_count => x_msg_count,
612 x_msg_data => x_msg_data,
613 p_khr_id => p_chr_id,
614 p_major_version => p_major_version);
615
616 --- If any errors happen abort API
617 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
618 OKL_API.set_message(p_app_name => G_APP_NAME,
619 p_msg_name => G_CREATE_VER_ERROR,
620 p_token1 => G_COL_NAME_TOKEN,
621 p_token1_value => 'OKL_STREAMS_H');
622 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
623 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
624 OKL_API.set_message(p_app_name => G_APP_NAME,
625 p_msg_name => G_CREATE_VER_ERROR,
626 p_token1 => G_COL_NAME_TOKEN,
627 p_token1_value => 'OKL_STREAMS_H');
628 RAISE OKL_API.G_EXCEPTION_ERROR;
629 END IF;
630 --------------
631 --End Bug# 3379716:
632 ---------------
633
634 -- Versioning the Fixed asset Information
635 Create_fa_version(p_api_version => p_api_version,
636 p_init_msg_list => p_init_msg_list,
637 x_return_status => x_return_status,
638 x_msg_count => x_msg_count,
639 x_msg_data => x_msg_data,
640 p_chr_id => p_chr_id,
641 p_major_version => p_major_version);
642 --- If any errors happen abort API
643 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
644 OKL_API.set_message(p_app_name => G_APP_NAME,
645 p_msg_name => G_CREATE_VER_ERROR,
646 p_token1 => G_COL_NAME_TOKEN,
647 p_token1_value => 'OKL_CONTRACT_ASSET_H');
648 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
649 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
650 OKL_API.set_message(p_app_name => G_APP_NAME,
651 p_msg_name => G_CREATE_VER_ERROR,
652 p_token1 => G_COL_NAME_TOKEN,
653 p_token1_value => 'OKL_CONTRACT_ASSET_H');
654 RAISE OKL_API.G_EXCEPTION_ERROR;
655 END IF;
656 -- Versioning the Install Base information
657 Create_ib_version(p_api_version => p_api_version,
658 p_init_msg_list => p_init_msg_list,
659 x_return_status => x_return_status,
660 x_msg_count => x_msg_count,
661 x_msg_data => x_msg_data,
662 p_chr_id => p_chr_id,
663 p_major_version => p_major_version);
664 --- If any errors happen abort API
665 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
666 OKL_API.set_message(p_app_name => G_APP_NAME,
667 p_msg_name => G_CREATE_VER_ERROR,
668 p_token1 => G_COL_NAME_TOKEN,
669 p_token1_value => 'OKL_CONTRACT_IB_H');
670 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
671 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
672 OKL_API.set_message(p_app_name => G_APP_NAME,
673 p_msg_name => G_CREATE_VER_ERROR,
674 p_token1 => G_COL_NAME_TOKEN,
675 p_token1_value => 'OKL_CONTRACT_IB_H');
676 RAISE OKL_API.G_EXCEPTION_ERROR;
677 END IF;
678 OKL_API.END_ACTIVITY (x_msg_count,
679 x_msg_data );
680 EXCEPTION
681 WHEN OKL_API.G_EXCEPTION_ERROR THEN
682 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
683 l_api_name,
684 G_PKG_NAME,
685 'OKL_API.G_RET_STS_ERROR',
686 x_msg_count,
687 x_msg_data,
688 '_PVT');
689 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
690 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
691 l_api_name,
692 G_PKG_NAME,
693 'OKL_API.G_RET_STS_UNEXP_ERROR',
694 x_msg_count,
695 x_msg_data,
696 '_PVT');
697 WHEN OTHERS THEN
698 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
699 l_api_name,
700 G_PKG_NAME,
701 'OTHERS',
702 x_msg_count,
703 x_msg_data,
704 '_PVT');
705 END version_contract_extra;
706 ----------------------------------------------------------------------------------------
707 -- Start of comments
708 -- Procedure Name : restore_version_extra
709 -- Description : restores saved version of a contract for change request (OKL part)
710 -- Business Rules : the number of the change request version is -1
711 -- Parameters :
712 -- Version : 1.0
713 -- End of comments
714 PROCEDURE restore_version_extra(
715 p_api_version IN NUMBER,
716 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
717 x_return_status OUT NOCOPY VARCHAR2,
718 x_msg_count OUT NOCOPY NUMBER,
719 x_msg_data OUT NOCOPY VARCHAR2,
720 p_chr_id IN NUMBER,
721 p_major_version IN NUMBER) IS
722 l_api_version CONSTANT NUMBER := 1;
723 l_api_name CONSTANT VARCHAR2(30) := 'OKL_RESTORE_VERSION';
724 BEGIN
725 x_return_status := OKL_API.G_RET_STS_SUCCESS;
726 -- Call start_activity to create savepoint, check compatibility
727 -- and initialize message list
728 x_return_status := OKL_API.START_ACTIVITY (
729 l_api_name
730 ,p_init_msg_list
731 ,'_PVT'
732 ,x_return_status);
733 -- Check if activity started successfully
734 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
735 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
736 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
737 RAISE OKL_API.G_EXCEPTION_ERROR;
738 END IF;
739 --version contract header
740 x_return_status:=OKL_KHR_PVT.Restore_Version(p_chr_id,
741 p_major_version);
742 --- If any errors happen abort API
743 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
744 OKL_API.set_message(p_app_name => G_APP_NAME,
745 p_msg_name => G_RESTORE_VER_ERROR,
746 p_token1 => G_COL_NAME_TOKEN,
747 p_token1_value => 'OKL_K_HEADERS_H');
748 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
749 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
750 OKL_API.set_message(p_app_name => G_APP_NAME,
751 p_msg_name => G_RESTORE_VER_ERROR,
752 p_token1 => G_COL_NAME_TOKEN,
753 p_token1_value => 'OKL_K_HEADERS_H');
754 RAISE OKL_API.G_EXCEPTION_ERROR;
755 END IF;
756 --version contract lines
757 x_return_status:=OKL_KLE_PVT.Restore_Version(p_chr_id,
758 p_major_version);
759 --- If any errors happen abort API
760 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
761 OKL_API.set_message(p_app_name => G_APP_NAME,
762 p_msg_name => G_RESTORE_VER_ERROR,
763 p_token1 => G_COL_NAME_TOKEN,
764 p_token1_value => 'OKL_K_LINES_H');
765 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
766 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
767 OKL_API.set_message(p_app_name => G_APP_NAME,
768 p_msg_name => G_RESTORE_VER_ERROR,
769 p_token1 => G_COL_NAME_TOKEN,
770 p_token1_value => 'OKL_K_LINES_H');
771 RAISE OKL_API.G_EXCEPTION_ERROR;
772 END IF;
773 x_return_status:=OKL_SID_PVT.Restore_Version(p_chr_id,
774 p_major_version);
775 --- If any errors happen abort API
776 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
777 OKL_API.set_message(p_app_name => G_APP_NAME,
778 p_msg_name => G_RESTORE_VER_ERROR,
779 p_token1 => G_COL_NAME_TOKEN,
780 p_token1_value => 'OKL_SUPP_INVOICE_DTLS_H');
781 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
782 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
783 OKL_API.set_message(p_app_name => G_APP_NAME,
784 p_msg_name => G_RESTORE_VER_ERROR,
785 p_token1 => G_COL_NAME_TOKEN,
786 p_token1_value => 'OKL_SUPP_INVOICE_DTLS_H');
787 RAISE OKL_API.G_EXCEPTION_ERROR;
788 END IF;
789 -------------
790 --version party payment hdr,fmiao
791 -------------
792 x_return_status:=OKL_LDB_PVT.Restore_Version(p_chr_id,
793 p_major_version);
794 --- If any errors happen abort API
795 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
796 OKL_API.set_message(p_app_name => G_APP_NAME,
797 p_msg_name => G_RESTORE_VER_ERROR,
798 p_token1 => G_COL_NAME_TOKEN,
799 p_token1_value => 'OKL_PARTY_PAYMENT_HDR_H');
800 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
801 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
802 OKL_API.set_message(p_app_name => G_APP_NAME,
803 p_msg_name => G_RESTORE_VER_ERROR,
804 p_token1 => G_COL_NAME_TOKEN,
805 p_token1_value => 'OKL_PARTY_PAYMENT_HDR_H');
806 RAISE OKL_API.G_EXCEPTION_ERROR;
807 END IF;
808 -------------
809 --Bug# 3143522
810 -------------
811 x_return_status:=OKL_PYD_PVT.Restore_Version(p_chr_id,
812 p_major_version);
813 --- If any errors happen abort API
814 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
815 OKL_API.set_message(p_app_name => G_APP_NAME,
816 p_msg_name => G_RESTORE_VER_ERROR,
817 p_token1 => G_COL_NAME_TOKEN,
818 p_token1_value => 'OKL_PARTY_PAYMENT_DTLS_H');
819 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
820 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
821 OKL_API.set_message(p_app_name => G_APP_NAME,
822 p_msg_name => G_RESTORE_VER_ERROR,
823 p_token1 => G_COL_NAME_TOKEN,
824 p_token1_value => 'OKL_PARTY_PAYMENT_DTLS_H');
825 RAISE OKL_API.G_EXCEPTION_ERROR;
826 END IF;
827 -------------
828 --End Bug# 3143522
829 -------------
830
831 -------------
832 --Bug# 4558486
833 -------------
834 --Version Okl_K_Party_Roles
835 x_return_status:=OKL_KPL_PVT.Restore_Version(p_chr_id,
836 p_major_version);
837 --- If any errors happen abort API
838 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
839 OKL_API.set_message(p_app_name => G_APP_NAME,
840 p_msg_name => G_RESTORE_VER_ERROR,
841 p_token1 => G_COL_NAME_TOKEN,
842 p_token1_value => 'OKL_K_PARTY_ROLES_H');
843 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
844 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
845 OKL_API.set_message(p_app_name => G_APP_NAME,
846 p_msg_name => G_RESTORE_VER_ERROR,
847 p_token1 => G_COL_NAME_TOKEN,
848 p_token1_value => 'OKL_K_PARTY_ROLES_H');
849 RAISE OKL_API.G_EXCEPTION_ERROR;
850 END IF;
851 -------------
852 --End Bug# 4558486
853 -------------
854
855
856 -- More thought for Restore of OKL_CONTRACT_ASSET_H and
857 -- OKL_CONTRACT_IB_H tables is to be give so untill then we
858 -- not code for restore of these tables
859 OKL_API.END_ACTIVITY (x_msg_count,
860 x_msg_data );
861
862 EXCEPTION
863 WHEN OKL_API.G_EXCEPTION_ERROR THEN
864 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
865 l_api_name,
866 G_PKG_NAME,
867 'OKL_API.G_RET_STS_ERROR',
868 x_msg_count,
869 x_msg_data,
870 '_PVT');
871 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
872 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
873 l_api_name,
874 G_PKG_NAME,
875 'OKL_API.G_RET_STS_UNEXP_ERROR',
876 x_msg_count,
877 x_msg_data,
878 '_PVT');
879 WHEN OTHERS THEN
880 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
881 l_api_name,
882 G_PKG_NAME,
883 'OTHERS',
884 x_msg_count,
885 x_msg_data,
886 '_PVT');
887 END restore_version_extra;
888 ----------------------------------------------------------------------------------------
889 -- Start of comments
890 -- Procedure Name : delete_base
891 -- Description : removes data from the base tables (OKL part)
892 -- Business Rules : required prior to restoring data from a version
893 -- Parameters :
894 -- Version : 1.0
895 -- End of comments
896 PROCEDURE delete_base(
897 p_api_version IN NUMBER,
898 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
899 x_return_status OUT NOCOPY VARCHAR2,
900 x_msg_count OUT NOCOPY NUMBER,
901 x_msg_data OUT NOCOPY VARCHAR2,
902 p_chr_id IN NUMBER) IS
903 l_api_version CONSTANT NUMBER := 1;
904 l_api_name CONSTANT VARCHAR2(30) := 'OKL_DELETE_BASE';
905 BEGIN
906 x_return_status := OKL_API.G_RET_STS_SUCCESS;
907 -- Call start_activity to create savepoint, check compatibility
908 -- and initialize message list
909 x_return_status := OKL_API.START_ACTIVITY (
910 l_api_name
911 ,p_init_msg_list
912 ,'_PVT'
913 ,x_return_status);
914 -- Check if activity started successfully
915 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
916 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
917 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
918 RAISE OKL_API.G_EXCEPTION_ERROR;
919 END IF;
920 -- Deleting the Header
921 DELETE FROM OKL_K_HEADERS
922 WHERE id = p_chr_id;
923 -- Deleting the Lines
924 DELETE FROM OKL_K_LINES
925 WHERE id IN (SELECT id
926 FROM OKC_K_LINES_B
927 WHERE dnz_chr_id = p_chr_id);
928 -- Deleting the supplier Invoice Details
929 DELETE FROM OKL_SUPP_INVOICE_DTLS_H
930 WHERE cle_id in (select id from okc_k_lines_b where dnz_chr_id = p_chr_id);
931 -- Deleting the FA Version
932 DELETE FROM OKL_CONTRACT_ASSET_H
933 WHERE DNZ_CHR_ID = p_chr_id;
934 -- Deleting the IB VERSION
935 DELETE FROM OKL_CONTRACT_IB_H
936 WHERE DNZ_CHR_ID = p_chr_id;
937 OKL_API.END_ACTIVITY (x_msg_count,
938 x_msg_data );
939 EXCEPTION
940 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
941 OKL_API.set_message(p_app_name => G_APP_NAME,
942 p_msg_name => G_DELETE_BASE_ERROR);
943 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
944 l_api_name,
945 G_PKG_NAME,
946 'OKL_API.G_RET_STS_UNEXP_ERROR',
947 x_msg_count,
948 x_msg_data,
949 '_PVT');
950 WHEN OTHERS THEN
951 OKL_API.set_message(p_app_name => G_APP_NAME,
952 p_msg_name => G_DELETE_BASE_ERROR);
953 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
954 l_api_name,
955 G_PKG_NAME,
956 'OTHERS',
957 x_msg_count,
958 x_msg_data,
959 '_PVT');
960 END delete_base;
961 ----------------------------------------------------------------------------------------
962 -- Start of comments
963 -- Procedure Name : delete_version
964 -- Description : removes data from the history tables (OKL part)
965 -- Business Rules : required after the version is created
966 -- Parameters :
967 -- Version : 1.0
968 -- End of comments
969 PROCEDURE delete_version (
970 p_api_version IN NUMBER,
971 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
972 x_return_status OUT NOCOPY VARCHAR2,
973 x_msg_count OUT NOCOPY NUMBER,
974 x_msg_data OUT NOCOPY VARCHAR2,
975 p_chr_id IN NUMBER,
976 p_major_version IN NUMBER,
977 p_minor_version IN NUMBER,
978 p_called_from IN VARCHAR2) IS
979 l_major_version NUMBER := p_major_version;
980 l_minor_version NUMBER := p_minor_version;
981 l_api_version CONSTANT NUMBER := 1;
982 l_api_name CONSTANT VARCHAR2(30) := 'OKL_DELETE_VERSION';
983 BEGIN
984 x_return_status := OKL_API.G_RET_STS_SUCCESS;
985 -- Call start_activity to create savepoint, check compatibility
986 -- and initialize message list
987 x_return_status := OKL_API.START_ACTIVITY (
988 l_api_name
989 ,p_init_msg_list
990 ,'_PVT'
991 ,x_return_status);
992 -- Check if activity started successfully
993 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
994 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
995 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
996 RAISE OKL_API.G_EXCEPTION_ERROR;
997 END IF;
998 -- Deleting the Header
999 DELETE FROM OKL_K_HEADERS_H
1000 WHERE id= p_chr_id
1001 AND ((MAJOR_VERSION = -1
1002 AND p_called_from = 'ERASE_SAVED_VERSION') OR
1003 (p_called_from = 'RESTORE_VERSION'
1004 AND (major_version >= l_major_version OR
1005 major_version = -1)));
1006 -- Deleting the Lines
1007 DELETE FROM OKL_K_LINES_H
1008 WHERE ID in (select id from okc_k_lines_b where dnz_chr_id = p_chr_id)
1009 AND ((MAJOR_VERSION = -1
1010 AND p_called_from = 'ERASE_SAVED_VERSION') OR
1011 (p_called_from = 'RESTORE_VERSION'
1012 AND (major_version >= l_major_version OR
1013 major_version = -1)));
1014 -- Deleting the supplier Invoice Details
1015 DELETE FROM OKL_SUPP_INVOICE_DTLS_H
1016 WHERE cle_id in (select id from okc_k_lines_b where dnz_chr_id = p_chr_id)
1017 AND ((MAJOR_VERSION = -1
1018 AND p_called_from = 'ERASE_SAVED_VERSION') OR
1019 (p_called_from = 'RESTORE_VERSION'
1020 AND (major_version >= l_major_version OR
1021 major_version = -1)));
1022 -- Deleting the FA Version
1023 DELETE FROM OKL_CONTRACT_ASSET_H
1024 WHERE DNZ_CHR_ID = p_chr_id
1025 AND ((MAJOR_VERSION = -1
1026 AND p_called_from = 'ERASE_SAVED_VERSION') OR
1027 (p_called_from = 'RESTORE_VERSION'
1028 AND (major_version >= l_major_version OR
1029 major_version = -1)));
1030 -- Deleting the IB VERSION
1031 DELETE FROM OKL_CONTRACT_IB_H
1032 WHERE DNZ_CHR_ID = p_chr_id
1033 AND ((MAJOR_VERSION = -1
1034 AND p_called_from = 'ERASE_SAVED_VERSION') OR
1035 (p_called_from = 'RESTORE_VERSION'
1036 AND (major_version >= l_major_version OR
1037 major_version = -1)));
1038 OKL_API.END_ACTIVITY (x_msg_count,
1039 x_msg_data );
1040 EXCEPTION
1041 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1042 OKL_API.set_message(p_app_name => G_APP_NAME,
1043 p_msg_name => G_DELETE_VER_ERROR);
1044 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
1045 l_api_name,
1046 G_PKG_NAME,
1047 'OKL_API.G_RET_STS_UNEXP_ERROR',
1048 x_msg_count,
1049 x_msg_data,
1050 '_PVT');
1051 WHEN OTHERS THEN
1052 OKL_API.set_message(p_app_name => G_APP_NAME,
1053 p_msg_name => G_DELETE_VER_ERROR);
1054 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
1055 l_api_name,
1056 G_PKG_NAME,
1057 'OTHERS',
1058 x_msg_count,
1059 x_msg_data,
1060 '_PVT');
1061 END delete_version;
1062 ----------------------------------------------------------------------------------------
1063 --------------------------------- Version Creation -------------------------------------
1064 ----------------------------------------------------------------------------------------
1065 -- Start of comments
1066 -- Procedure Name : version_contract
1067 -- Description : creates new version of a contract
1068 -- Business Rules :
1069 -- Parameters :
1070 -- Version : 1.0
1071 -- End of comments
1072 PROCEDURE version_contract(
1073 p_api_version IN NUMBER,
1074 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
1075 x_return_status OUT NOCOPY VARCHAR2,
1076 x_msg_count OUT NOCOPY NUMBER,
1077 x_msg_data OUT NOCOPY VARCHAR2,
1078 p_cvmv_rec IN cvmv_rec_type,
1079 x_cvmv_rec OUT NOCOPY cvmv_rec_type,
1080 p_commit IN VARCHAR2 DEFAULT OKL_API.G_FALSE) IS
1081 l_api_version CONSTANT NUMBER := 1;
1082 l_api_name CONSTANT VARCHAR2(30) := 'VERSION_CONTRACT';
1083 l_chr_id NUMBER;
1084 l_major_version NUMBER;
1085 lv_sts_code OKC_K_HEADERS_V.STS_CODE%TYPE;
1086 CURSOR c_get_sts_code(p_chr_id OKC_K_HEADERS_V.ID%TYPE)
1087 IS
1088 SELECT st.ste_code
1089 FROM OKC_K_HEADERS_V chr,
1090 okc_statuses_b st
1091 WHERE chr.id = p_chr_id
1092 and st.code = chr.sts_code;
1093 BEGIN
1094 x_return_status := OKL_API.G_RET_STS_SUCCESS;
1095 x_return_status := OKL_API.START_ACTIVITY(l_api_name,
1096 G_PKG_NAME,
1097 p_init_msg_list,
1098 l_api_version,
1099 p_api_version,
1100 '_PVT',
1101 x_return_status);
1102 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1103 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1104 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1105 RAISE OKL_API.G_EXCEPTION_ERROR;
1106 END IF;
1107 -- Get the sts code since we can version only active contract
1108 OPEN c_get_sts_code(p_cvmv_rec.chr_id);
1109 IF c_get_sts_code%NOTFOUND THEN
1110 OKL_API.set_message(p_app_name => G_APP_NAME,
1111 p_msg_name => G_NO_MATCHING_RECORD,
1112 p_token1 => G_COL_NAME_TOKEN,
1113 p_token1_value => 'OKC_K_HEADERS_V.STS_CODE');
1114 RAISE OKL_API.G_EXCEPTION_ERROR;
1115 END IF;
1116 FETCH c_get_sts_code INTO lv_sts_code;
1117 CLOSE c_get_sts_code;
1118 IF lv_sts_code = 'ACTIVE' THEN
1119 -- Create the version of the OKC part
1120 OKL_OKC_MIGRATION_PVT.version_contract(
1121 p_api_version => p_api_version,
1122 p_init_msg_list => p_init_msg_list,
1123 x_return_status => x_return_status,
1124 x_msg_count => x_msg_count,
1125 x_msg_data => x_msg_data,
1126 p_cvmv_rec => p_cvmv_rec,
1127 p_commit => OKL_API.G_FALSE,
1128 x_cvmv_rec => x_cvmv_rec);
1129 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1130 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1131 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1132 RAISE OKL_API.G_EXCEPTION_ERROR;
1133 END IF;
1134 -- Create the version of the OKL part
1135 l_chr_id :=x_cvmv_rec.chr_id;
1136 l_major_version :=x_cvmv_rec.major_version;
1137 version_contract_extra(p_api_version => p_api_version,
1138 p_init_msg_list => OKL_API.G_FALSE,
1139 x_return_status => x_return_status,
1140 x_msg_count => x_msg_count,
1141 x_msg_data => x_msg_data,
1142 p_chr_id => l_chr_id,
1143 p_major_version => l_major_version);
1144
1145 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1146 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1147 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1148 RAISE OKL_API.G_EXCEPTION_ERROR;
1149 END IF;
1150 ELSE
1151 OKL_API.set_message(p_app_name => G_APP_NAME,
1152 p_msg_name => G_STATUS,
1153 p_token1 => 'STATUS',
1154 p_token1_value => lv_sts_code);
1155 RAISE OKL_API.G_EXCEPTION_ERROR;
1156 END IF;
1157 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
1158 -- IF (p_commit = OKL_API.G_TRUE) THEN
1159 -- COMMIT;
1160 -- END IF;
1161 EXCEPTION
1162 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1163 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
1164 l_api_name,
1165 G_PKG_NAME,
1166 'OKL_API.G_RET_STS_ERROR',
1167 x_msg_count,
1168 x_msg_data,
1169 '_PVT');
1170 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1171 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
1172 l_api_name,
1173 G_PKG_NAME,
1174 'OKL_API.G_RET_STS_UNEXP_ERROR',
1175 x_msg_count,
1176 x_msg_data,
1177 '_PVT');
1178 WHEN OTHERS THEN
1179 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
1180 l_api_name,
1181 G_PKG_NAME,
1182 'OTHERS',
1183 x_msg_count,
1184 x_msg_data,
1185 '_PVT');
1186 END Version_Contract;
1187 ----------------------------------------------------------------------------------------
1188 ----------------------------------- Save Version ---------------------------------------
1189 ----------------------------------------------------------------------------------------
1190 -- Start of comments
1191 -- Procedure Name : save_version
1192 -- Description : save new version of a contract for change request
1193 -- Business Rules : the number of the change request version is -1
1194 -- Parameters :
1195 -- Version : 1.0
1196 -- End of comments
1197 PROCEDURE save_version(
1198 p_api_version IN NUMBER,
1199 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
1200 x_return_status OUT NOCOPY VARCHAR2,
1201 x_msg_count OUT NOCOPY NUMBER,
1202 x_msg_data OUT NOCOPY VARCHAR2,
1203 p_chr_id IN NUMBER,
1204 p_commit IN VARCHAR2 DEFAULT OKL_API.G_FALSE) IS
1205 l_api_version CONSTANT NUMBER := 1;
1206 l_api_name CONSTANT VARCHAR2(30) := 'SAVE_VERSION';
1207 l_major_version CONSTANT NUMBER := -1;
1208 l_chr_id NUMBER := p_chr_id;
1209 BEGIN
1210 x_return_status := OKL_API.G_RET_STS_SUCCESS;
1211 x_return_status := OKL_API.START_ACTIVITY(l_api_name,
1212 G_PKG_NAME,
1213 p_init_msg_list,
1214 l_api_version,
1215 p_api_version,
1216 '_PVT',
1217 x_return_status);
1218 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1219 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1220 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1221 RAISE OKL_API.G_EXCEPTION_ERROR;
1222 END IF;
1223 delete_version(p_api_version => l_api_version,
1224 p_init_msg_list => p_init_msg_list,
1225 x_return_status => x_return_status,
1226 x_msg_count => x_msg_count,
1227 x_msg_data => x_msg_data,
1228 p_chr_id => p_chr_id,
1229 p_major_version => -1,
1230 p_minor_version => null,
1231 p_called_from => 'ERASE_SAVED_VERSION');
1232
1233 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1234 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1235 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1236 RAISE OKL_API.G_EXCEPTION_ERROR;
1237 END IF;
1238 OKC_VERSION_PUB.save_version(
1239 p_api_version => p_api_version,
1240 p_init_msg_list => p_init_msg_list,
1241 x_return_status => x_return_status,
1242 x_msg_count => x_msg_count,
1243 x_msg_data => x_msg_data,
1244 p_chr_id => p_chr_id,
1245 p_commit => OKL_API.G_FALSE);
1246 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1247 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1248 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1249 RAISE OKL_API.G_EXCEPTION_ERROR;
1250 END IF;
1251 version_contract_extra(p_api_version => p_api_version,
1252 p_init_msg_list => OKL_API.G_FALSE,
1253 x_return_status => x_return_status,
1254 x_msg_count => x_msg_count,
1255 x_msg_data => x_msg_data,
1256 p_chr_id => l_chr_id,
1257 p_major_version => l_major_version);
1258 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1259 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1260 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1261 RAISE OKL_API.G_EXCEPTION_ERROR;
1262 END IF;
1263 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
1264 -- IF (p_commit = OKL_API.G_TRUE) THEN
1265 -- COMMIT;
1266 -- END IF;
1267 EXCEPTION
1268 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1269 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
1270 l_api_name,
1271 G_PKG_NAME,
1272 'OKL_API.G_RET_STS_ERROR',
1273 x_msg_count,
1274 x_msg_data,
1275 '_PVT');
1276 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1277 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
1278 l_api_name,
1279 G_PKG_NAME,
1280 'OKL_API.G_RET_STS_UNEXP_ERROR',
1281 x_msg_count,
1282 x_msg_data,
1283 '_PVT');
1284 WHEN OTHERS THEN
1285 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
1286 l_api_name,
1287 G_PKG_NAME,
1288 'OTHERS',
1289 x_msg_count,
1290 x_msg_data,
1291 '_PVT');
1292 END Save_Version;
1293 ----------------------------------------------------------------------------------------
1294 ------------------------------- Erase Saved Version ------------------------------------
1295 ----------------------------------------------------------------------------------------
1296 -- Start of comments
1297 -- Procedure Name : erase_saved_version
1298 -- Description : erases the saved version of a contract for change request
1299 -- Business Rules : the number of the change request version is -1
1300 -- Parameters :
1301 -- Version : 1.0
1302 -- End of comments
1303 PROCEDURE erase_saved_version(
1304 p_api_version IN NUMBER,
1305 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
1306 x_return_status OUT NOCOPY VARCHAR2,
1307 x_msg_count OUT NOCOPY NUMBER,
1308 x_msg_data OUT NOCOPY VARCHAR2,
1309 p_chr_id IN NUMBER,
1310 p_commit IN VARCHAR2 DEFAULT OKL_API.G_FALSE) IS
1311 l_api_version CONSTANT NUMBER := 1;
1312 l_api_name CONSTANT VARCHAR2(30) := 'ERASE_SAVED_VERSION';
1313 BEGIN
1314 x_return_status := OKL_API.G_RET_STS_SUCCESS;
1315 x_return_status := OKL_API.START_ACTIVITY(l_api_name,
1316 G_PKG_NAME,
1317 p_init_msg_list,
1318 l_api_version,
1319 p_api_version,
1320 '_PVT',
1321 x_return_status);
1322 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1323 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1324 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1325 RAISE OKL_API.G_EXCEPTION_ERROR;
1326 END IF;
1327 delete_version(p_api_version => l_api_version,
1328 p_init_msg_list => p_init_msg_list,
1329 x_return_status => x_return_status,
1330 x_msg_count => x_msg_count,
1331 x_msg_data => x_msg_data,
1332 p_chr_id => p_chr_id,
1333 p_major_version => -1,
1334 p_minor_version => null,
1335 p_called_from => 'ERASE_SAVED_VERSION');
1336 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1337 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1338 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1339 RAISE OKL_API.G_EXCEPTION_ERROR;
1340 END IF;
1341 OKC_VERSION_PUB.erase_saved_version(
1342 p_chr_id => p_chr_id,
1343 p_api_version => p_api_version,
1344 p_init_msg_list => p_init_msg_list,
1345 x_return_status => x_return_status,
1346 x_msg_count => x_msg_count,
1347 p_commit => OKL_API.G_FALSE,
1348 x_msg_data => x_msg_data);
1349 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1350 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1351 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1352 RAISE OKL_API.G_EXCEPTION_ERROR;
1353 END IF;
1354 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
1355 -- IF (p_commit = OKL_API.G_TRUE) THEN
1356 -- COMMIT;
1357 -- END IF;
1358 EXCEPTION
1359 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1360 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
1361 l_api_name,
1362 G_PKG_NAME,
1363 'OKL_API.G_RET_STS_ERROR',
1364 x_msg_count,
1365 x_msg_data,
1366 '_PVT');
1367 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1368 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
1369 l_api_name,
1370 G_PKG_NAME,
1371 'OKL_API.G_RET_STS_UNEXP_ERROR',
1372 x_msg_count,
1373 x_msg_data,
1374 '_PVT');
1375 WHEN OTHERS THEN
1376 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
1377 l_api_name,
1378 G_PKG_NAME,
1379 'OTHERS',
1380 x_msg_count,
1381 x_msg_data,
1382 '_PVT');
1383 END erase_saved_version;
1384 ----------------------------------------------------------------------------------------
1385 ---------------------------------- Restore Version -------------------------------------
1386 ----------------------------------------------------------------------------------------
1387 -- Start of comments
1388 -- Procedure Name : restore_version
1389 -- Description : restores saved version of a contract for change request
1390 -- Business Rules : the number of the change request version is -1
1391 -- Parameters :
1392 -- Version : 1.0
1393 -- End of comments
1394 PROCEDURE restore_version(
1395 p_api_version IN NUMBER,
1396 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
1397 x_return_status OUT NOCOPY VARCHAR2,
1398 x_msg_count OUT NOCOPY NUMBER,
1399 x_msg_data OUT NOCOPY VARCHAR2,
1400 p_chr_id IN NUMBER,
1401 p_commit IN VARCHAR2 DEFAULT OKL_API.G_FALSE) IS
1402 l_api_version CONSTANT NUMBER := 1;
1403 l_api_name CONSTANT VARCHAR2(30) := 'RESTORE_VERSION';
1404 l_chr_id NUMBER;
1405 l_major_version NUMBER;
1406 l_minor_version NUMBER;
1407 l_minus_version NUMBER;
1408 cursor v_csr is
1409 SELECT object_version_number,
1410 minor_version
1411 FROM okc_k_vers_numbers_h
1412 WHERE chr_id= p_chr_id
1413 AND MAJOR_VERSION = -1;
1414 BEGIN
1415 x_return_status := OKL_API.G_RET_STS_SUCCESS;
1416 x_return_status := OKL_API.START_ACTIVITY(l_api_name,
1417 G_PKG_NAME,
1418 p_init_msg_list,
1419 l_api_version,
1420 p_api_version,
1421 '_PVT',
1422 x_return_status);
1423 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1424 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1425 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1426 RAISE OKL_API.G_EXCEPTION_ERROR;
1427 END IF;
1428 OPEN v_csr;
1429 FETCH v_csr into l_major_version,
1430 l_minor_version;
1431 CLOSE v_csr;
1432 delete_base(p_api_version => p_api_version,
1433 p_init_msg_list => p_init_msg_list,
1434 x_return_status => x_return_status,
1435 x_msg_count => x_msg_count,
1436 x_msg_data => x_msg_data,
1437 p_chr_id => p_chr_id);
1438 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1439 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1440 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1441 RAISE OKL_API.G_EXCEPTION_ERROR;
1442 END IF;
1443
1444 OKC_VERSION_PUB.restore_version(
1445 p_api_version => p_api_version,
1446 p_init_msg_list => p_init_msg_list,
1447 x_return_status => x_return_status,
1448 x_msg_count => x_msg_count,
1449 x_msg_data => x_msg_data,
1450 p_chr_id => p_chr_id,
1451 p_commit => OKL_API.G_FALSE);
1452 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1453 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1454 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1455 RAISE OKL_API.G_EXCEPTION_ERROR;
1456 END IF;
1457 l_chr_id := p_chr_id;
1458 l_minus_version := -1;
1459 restore_version_extra(p_api_version => p_api_version,
1460 p_init_msg_list => OKL_API.G_FALSE,
1461 x_return_status => x_return_status,
1462 x_msg_count => x_msg_count,
1463 x_msg_data => x_msg_data,
1464 p_chr_id => l_chr_id,
1465 p_major_version => l_minus_version);
1466 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1467 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1468 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1469 RAISE OKL_API.G_EXCEPTION_ERROR;
1470 END IF;
1471 delete_version(p_api_version => p_api_version,
1472 p_init_msg_list => p_init_msg_list,
1473 x_return_status => x_return_status,
1474 x_msg_count => x_msg_count,
1475 x_msg_data => x_msg_data,
1476 p_chr_id => p_chr_id,
1477 p_major_version => l_major_version,
1478 p_minor_version => l_minor_version,
1479 p_called_from => 'RESTORE_VERSION');
1480 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1481 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1482 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1483 RAISE OKL_API.G_EXCEPTION_ERROR;
1484 END IF;
1485 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
1486 -- IF (p_commit = OKL_API.G_TRUE) THEN
1487 -- COMMIT;
1488 -- END IF;
1489 EXCEPTION
1490 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1491 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
1492 l_api_name,
1493 G_PKG_NAME,
1494 'OKL_API.G_RET_STS_ERROR',
1495 x_msg_count,
1496 x_msg_data,
1497 '_PVT');
1498 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1499 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
1500 l_api_name,
1501 G_PKG_NAME,
1502 'OKL_API.G_RET_STS_UNEXP_ERROR',
1503 x_msg_count,
1504 x_msg_data,
1505 '_PVT');
1506 WHEN OTHERS THEN
1507 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
1508 l_api_name,
1509 G_PKG_NAME,
1510 'OTHERS',
1511 x_msg_count,
1512 x_msg_data,
1513 '_PVT');
1514 END restore_version;
1515 END okl_version_pvt;