[Home] [Help]
PACKAGE BODY: APPS.OKL_OKC_MIGRATION_A_PVT
Source
1 PACKAGE BODY OKL_OKC_MIGRATION_A_PVT AS
2 /* $Header: OKLROMAB.pls 115.9 2004/04/13 11:28:28 rnaik noship $ */
3 G_PKG_NAME CONSTANT VARCHAR2(200) := 'OKL_OKC_MIGRATION_A_PVT';
4 G_APP_NAME CONSTANT VARCHAR2(3) := OKL_API.G_APP_NAME;
5 G_UNEXPECTED_ERROR CONSTANT VARCHAR(200) := 'OKC_CONTRACTS_UNEXPECTED_ERROR';
6 G_SQLERRM_TOKEN CONSTANT VARCHAR2(200) := 'SQLerrm';
7 G_SQLCODE_TOKEN CONSTANT VARCHAR2(200) := 'SQLcode';
8
9 ---------------------------------------------------------------------------------------------------
10 -- Local Procedure to migrate from Locally declared catv record type
11 -- to OKC catv declared record type
12 PROCEDURE migrate_catv(p_from IN catv_rec_type,
13 p_to OUT NOCOPY OKC_K_ARTICLE_PUB.catv_rec_type) IS
14 BEGIN
15 p_to.id := p_from.id;
16 p_to.chr_id := p_from.chr_id;
17 p_to.cle_id := p_from.cle_id;
18 p_to.cat_id := p_from.cat_id;
19 p_to.object_version_number := p_from.object_version_number;
20 p_to.sfwt_flag := p_from.sfwt_flag;
21 p_to.sav_sae_id := p_from.sav_sae_id;
22 p_to.sav_sav_release := p_from.sav_sav_release;
23 p_to.sbt_code := p_from.sbt_code;
24 p_to.dnz_chr_id := p_from.dnz_chr_id;
25 p_to.comments := p_from.comments;
26 p_to.fulltext_yn := p_from.fulltext_yn;
27 p_to.variation_description := p_from.variation_description;
28 p_to.name := p_from.name;
29 p_to.attribute_category := p_from.attribute_category;
30 p_to.attribute1 := p_from.attribute1;
31 p_to.attribute2 := p_from.attribute2;
32 p_to.attribute3 := p_from.attribute3;
33 p_to.attribute4 := p_from.attribute4;
34 p_to.attribute5 := p_from.attribute5;
35 p_to.attribute6 := p_from.attribute6;
36 p_to.attribute7 := p_from.attribute7;
37 p_to.attribute8 := p_from.attribute8;
38 p_to.attribute9 := p_from.attribute9;
39 p_to.attribute10 := p_from.attribute10;
40 p_to.attribute11 := p_from.attribute11;
41 p_to.attribute12 := p_from.attribute12;
42 p_to.attribute13 := p_from.attribute13;
43 p_to.attribute14 := p_from.attribute14;
44 p_to.attribute15 := p_from.attribute15;
45 p_to.cat_type := p_from.cat_type;
46 p_to.created_by := p_from.created_by;
47 p_to.creation_date := p_from.creation_date;
48 p_to.last_updated_by := p_from.last_updated_by;
49 p_to.last_update_date := p_from.last_update_date;
50 p_to.last_update_login := p_from.last_update_login;
51 END migrate_catv;
52 ---------------------------------------------------------------------------------------------------
53 -- Local Procedure to migrate from OKC declared catv record type
54 -- to Locally declared catv record type
55 PROCEDURE migrate_catv(p_from IN OKC_K_ARTICLE_PUB.catv_rec_type,
56 p_to OUT NOCOPY catv_rec_type ) IS
57 BEGIN
58 p_to.id := p_from.id;
59 p_to.chr_id := p_from.chr_id;
60 p_to.cle_id := p_from.cle_id;
61 p_to.cat_id := p_from.cat_id;
62 p_to.object_version_number := p_from.object_version_number;
63 p_to.sfwt_flag := p_from.sfwt_flag;
64 p_to.sav_sae_id := p_from.sav_sae_id;
65 p_to.sav_sav_release := p_from.sav_sav_release;
66 p_to.sbt_code := p_from.sbt_code;
67 p_to.dnz_chr_id := p_from.dnz_chr_id;
68 p_to.comments := p_from.comments;
69 p_to.fulltext_yn := p_from.fulltext_yn;
70 p_to.variation_description := p_from.variation_description;
71 p_to.name := p_from.name;
72 p_to.attribute_category := p_from.attribute_category;
73 p_to.attribute1 := p_from.attribute1;
74 p_to.attribute2 := p_from.attribute2;
75 p_to.attribute3 := p_from.attribute3;
76 p_to.attribute4 := p_from.attribute4;
77 p_to.attribute5 := p_from.attribute5;
78 p_to.attribute6 := p_from.attribute6;
79 p_to.attribute7 := p_from.attribute7;
80 p_to.attribute8 := p_from.attribute8;
81 p_to.attribute9 := p_from.attribute9;
82 p_to.attribute10 := p_from.attribute10;
83 p_to.attribute11 := p_from.attribute11;
84 p_to.attribute12 := p_from.attribute12;
85 p_to.attribute13 := p_from.attribute13;
86 p_to.attribute14 := p_from.attribute14;
87 p_to.attribute15 := p_from.attribute15;
88 p_to.cat_type := p_from.cat_type;
89 p_to.created_by := p_from.created_by;
90 p_to.creation_date := p_from.creation_date;
91 p_to.last_updated_by := p_from.last_updated_by;
92 p_to.last_update_date := p_from.last_update_date;
93 p_to.last_update_login := p_from.last_update_login;
94
95 END migrate_catv;
96
97 ---------------------------------------------------------------------------------------------------
98 -- Local Procedure to migrate from Locally declared rgpv record type
99 -- to OKC rgpv declared record type
100 PROCEDURE migrate_rgpv(p_from IN rgpv_rec_type,
101 p_to OUT NOCOPY OKC_RULE_PUB.rgpv_rec_type) IS
102 BEGIN
103 p_to.id := p_from.id;
104 p_to.object_version_number := p_from.object_version_number;
105 p_to.sfwt_flag := p_from.sfwt_flag;
106 p_to.rgd_code := p_from.rgd_code;
107 p_to.sat_code := p_from.sat_code;
108 p_to.rgp_type := p_from.rgp_type;
109 p_to.cle_id := p_from.cle_id;
110 p_to.chr_id := p_from.chr_id;
111 p_to.dnz_chr_id := p_from.dnz_chr_id;
112 p_to.parent_rgp_id := p_from.parent_rgp_id;
113 p_to.comments := p_from.comments;
114 p_to.attribute_category := p_from.attribute_category;
115 p_to.attribute1 := p_from.attribute1;
116 p_to.attribute2 := p_from.attribute2;
117 p_to.attribute3 := p_from.attribute3;
118 p_to.attribute4 := p_from.attribute4;
119 p_to.attribute5 := p_from.attribute5;
120 p_to.attribute6 := p_from.attribute6;
121 p_to.attribute7 := p_from.attribute7;
122 p_to.attribute8 := p_from.attribute8;
123 p_to.attribute9 := p_from.attribute9;
124 p_to.attribute10 := p_from.attribute10;
125 p_to.attribute11 := p_from.attribute11;
126 p_to.attribute12 := p_from.attribute12;
127 p_to.attribute13 := p_from.attribute13;
128 p_to.attribute14 := p_from.attribute14;
129 p_to.attribute15 := p_from.attribute15;
130 p_to.created_by := p_from.created_by;
131 p_to.creation_date := p_from.creation_date;
132 p_to.last_updated_by := p_from.last_updated_by;
133 p_to.last_update_date := p_from.last_update_date;
134 p_to.last_update_login := p_from.last_update_login;
135 END migrate_rgpv;
136 ---------------------------------------------------------------------------------------------------
137 -- Local Procedure to migrate from OKC declared rgpv record type
138 -- to Locally declared rgpv record type
139 PROCEDURE migrate_rgpv(p_from IN OKC_RULE_PUB.rgpv_rec_type,
140 p_to OUT NOCOPY rgpv_rec_type ) IS
141 BEGIN
142 p_to.id := p_from.id;
143 p_to.object_version_number := p_from.object_version_number;
144 p_to.sfwt_flag := p_from.sfwt_flag;
145 p_to.rgd_code := p_from.rgd_code;
146 p_to.sat_code := p_from.sat_code;
147 p_to.rgp_type := p_from.rgp_type;
148 p_to.cle_id := p_from.cle_id;
149 p_to.chr_id := p_from.chr_id;
150 p_to.dnz_chr_id := p_from.dnz_chr_id;
151 p_to.parent_rgp_id := p_from.parent_rgp_id;
152 p_to.comments := p_from.comments;
153 p_to.attribute_category := p_from.attribute_category;
154 p_to.attribute1 := p_from.attribute1;
155 p_to.attribute2 := p_from.attribute2;
156 p_to.attribute3 := p_from.attribute3;
157 p_to.attribute4 := p_from.attribute4;
158 p_to.attribute5 := p_from.attribute5;
159 p_to.attribute6 := p_from.attribute6;
160 p_to.attribute7 := p_from.attribute7;
161 p_to.attribute8 := p_from.attribute8;
162 p_to.attribute9 := p_from.attribute9;
163 p_to.attribute10 := p_from.attribute10;
164 p_to.attribute11 := p_from.attribute11;
165 p_to.attribute12 := p_from.attribute12;
166 p_to.attribute13 := p_from.attribute13;
167 p_to.attribute14 := p_from.attribute14;
168 p_to.attribute15 := p_from.attribute15;
169 p_to.created_by := p_from.created_by;
170 p_to.creation_date := p_from.creation_date;
171 p_to.last_updated_by := p_from.last_updated_by;
172 p_to.last_update_date := p_from.last_update_date;
173 p_to.last_update_login := p_from.last_update_login;
174
175 END migrate_rgpv;
176 ---------------------------------------------------------------------------------------------------
177 -- Local Procedure to migrate from OKC declared msg record type
178 -- to Locally declared msg record type
179 PROCEDURE migrate_msgv(p_from IN OKC_QA_CHECK_PUB.msg_tbl_type,
180 p_to OUT NOCOPY msg_tbl_type ) IS
181 i NUMBER;
182 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
183 BEGIN
184
185 -- x_return_status:= OKC_API.G_RET_STS_SUCCESS;
186 if (p_from.COUNT>0) then
187 i := p_from.FIRST;
188 LOOP
189 p_to(i).severity := p_from(i).severity;
190 p_to(i).name := p_from(i).name;
191 p_to(i).description := p_from(i).description;
192 p_to(i).package_name := p_from(i).package_name;
193 p_to(i).procedure_name := p_from(i).procedure_name;
194 p_to(i).error_status := p_from(i).error_status;
195 p_to(i).data := p_from(i).data;
196 EXIT WHEN (i=p_from.LAST);
197 i := p_from.NEXT(i);
198 END LOOP;
199 end if;
200
201 END migrate_msgv;
202
203 ---------------------------------------------------------------------------------------------------
204
205 -- INSERTING ARTICLE
206
207 PROCEDURE insert_row(
208 p_api_version IN NUMBER,
209 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
210 x_return_status OUT NOCOPY VARCHAR2,
211 x_msg_count OUT NOCOPY NUMBER,
212 x_msg_data OUT NOCOPY VARCHAR2,
213 p_catv_rec IN catv_rec_type,
214 x_catv_rec OUT NOCOPY catv_rec_type) IS
215
216 l_catv_rec catv_rec_type;
217 l_okc_catv_rec_in okc_k_article_pub.catv_rec_type;
218 l_okc_catv_rec_out okc_k_article_pub.catv_rec_type;
219
220 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_K_ARTICLE';
221 l_api_version CONSTANT NUMBER := 1.0;
222 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
223 BEGIN
224 -- call START_ACTIVITY to create savepoint, check compatibility
225 -- and initialize message list
226 l_return_status := OKC_API.START_ACTIVITY(
227 p_api_name => l_api_name,
228 p_pkg_name => g_pkg_name,
229 p_init_msg_list => p_init_msg_list,
230 l_api_version => l_api_version,
231 p_api_version => p_api_version,
232 p_api_type => g_api_type,
233 x_return_status => x_return_status);
234
235 -- check if activity started successfully
236 If (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
237 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
238 Elsif (l_return_status = OKC_API.G_RET_STS_ERROR) then
239 raise OKC_API.G_EXCEPTION_ERROR;
240 End If;
241
242 l_catv_rec := p_catv_rec;
243 -- call procedure in complex API
244
245 migrate_catv(p_from => l_catv_rec,
246 p_to => l_okc_catv_rec_in);
247
248 OKC_K_ARTICLE_PUB.create_k_article(
249 p_api_version => p_api_version,
250 p_init_msg_list => p_init_msg_list,
251 x_return_status => x_return_status,
252 x_msg_count => x_msg_count,
253 x_msg_data => x_msg_data,
254 p_catv_rec => l_okc_catv_rec_in,
255 x_catv_rec => l_okc_catv_rec_out);
256
257 -- check return status
258 If x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR Then
259 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
260 Elsif x_return_status = OKC_API.G_RET_STS_ERROR Then
261 raise OKC_API.G_EXCEPTION_ERROR;
262 End If;
263
264 migrate_catv(p_from => l_okc_catv_rec_out,
265 p_to => x_catv_rec);
266
267 -- end activity
268 OKC_API.END_ACTIVITY( x_msg_count => x_msg_count,
269 x_msg_data => x_msg_data);
270 EXCEPTION
271 when OKC_API.G_EXCEPTION_ERROR then
272 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
273 p_api_name => l_api_name,
274 p_pkg_name => g_pkg_name,
275 p_exc_name => 'OKC_API.G_RET_STS_ERROR',
276 x_msg_count => x_msg_count,
277 x_msg_data => x_msg_data,
278 p_api_type => g_api_type);
279
280 when OKC_API.G_EXCEPTION_UNEXPECTED_ERROR then
281 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
282 p_api_name => l_api_name,
283 p_pkg_name => g_pkg_name,
284 p_exc_name => 'OKC_API.G_RET_STS_UNEXP_ERROR',
285 x_msg_count => x_msg_count,
286 x_msg_data => x_msg_data,
287 p_api_type => g_api_type);
288
289 when OTHERS then
290 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
291 p_api_name => l_api_name,
292 p_pkg_name => g_pkg_name,
293 p_exc_name => 'OTHERS',
294 x_msg_count => x_msg_count,
295 x_msg_data => x_msg_data,
296 p_api_type => g_api_type);
297
298 END insert_row;
299
300 -----------------------------------------------------------------------------------------------------------
301 -- UPDATING ARTICLE
302
303 PROCEDURE update_row(
304 p_api_version IN NUMBER,
305 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
306 x_return_status OUT NOCOPY VARCHAR2,
307 x_msg_count OUT NOCOPY NUMBER,
308 x_msg_data OUT NOCOPY VARCHAR2,
309 p_catv_rec IN catv_rec_type,
310 x_catv_rec OUT NOCOPY catv_rec_type) IS
311
312 l_catv_rec catv_rec_type;
313 l_okc_catv_rec_in okc_k_article_pub.catv_rec_type;
314 l_okc_catv_rec_out okc_k_article_pub.catv_rec_type;
315
316 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_K_ARTICLE';
317 l_api_version CONSTANT NUMBER := 1.0;
318 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
319 BEGIN
320 -- call START_ACTIVITY to create savepoint, check compatibility
321 -- and initialize message list
322 l_return_status := OKC_API.START_ACTIVITY(
323 p_api_name => l_api_name,
324 p_pkg_name => g_pkg_name,
325 p_init_msg_list => p_init_msg_list,
326 l_api_version => l_api_version,
327 p_api_version => p_api_version,
328 p_api_type => g_api_type,
329 x_return_status => x_return_status);
330
331 -- check if activity started successfully
332 If (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
333 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
334 Elsif (l_return_status = OKC_API.G_RET_STS_ERROR) then
335 raise OKC_API.G_EXCEPTION_ERROR;
336 End If;
337
338 l_catv_rec := p_catv_rec;
339 -- call procedure in complex API
340
341 migrate_catv(p_from => l_catv_rec,
342 p_to => l_okc_catv_rec_in);
343
344 OKC_K_ARTICLE_PUB.update_k_article(
345 p_api_version => p_api_version,
346 p_init_msg_list => p_init_msg_list,
347 x_return_status => x_return_status,
348 x_msg_count => x_msg_count,
349 x_msg_data => x_msg_data,
350 p_catv_rec => l_okc_catv_rec_in,
351 x_catv_rec => l_okc_catv_rec_out);
352
353 -- check return status
354 If x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR Then
355 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
356 Elsif x_return_status = OKC_API.G_RET_STS_ERROR Then
357 raise OKC_API.G_EXCEPTION_ERROR;
358 End If;
359
360 migrate_catv(p_from => l_okc_catv_rec_out,
361 p_to => x_catv_rec);
362
363 -- end activity
364 OKC_API.END_ACTIVITY( x_msg_count => x_msg_count,
365 x_msg_data => x_msg_data);
366 EXCEPTION
367 when OKC_API.G_EXCEPTION_ERROR then
368 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
369 p_api_name => l_api_name,
370 p_pkg_name => g_pkg_name,
371 p_exc_name => 'OKC_API.G_RET_STS_ERROR',
372 x_msg_count => x_msg_count,
373 x_msg_data => x_msg_data,
374 p_api_type => g_api_type);
375
376 when OKC_API.G_EXCEPTION_UNEXPECTED_ERROR then
377 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
378 p_api_name => l_api_name,
379 p_pkg_name => g_pkg_name,
380 p_exc_name => 'OKC_API.G_RET_STS_UNEXP_ERROR',
381 x_msg_count => x_msg_count,
382 x_msg_data => x_msg_data,
383 p_api_type => g_api_type);
384
385 when OTHERS then
386 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
387 p_api_name => l_api_name,
388 p_pkg_name => g_pkg_name,
389 p_exc_name => 'OTHERS',
390 x_msg_count => x_msg_count,
391 x_msg_data => x_msg_data,
392 p_api_type => g_api_type);
393
394 END update_row;
395
396 -----------------------------------------------------------------------------------------------------------
397 -- DELETING ARTICLE
398
399 PROCEDURE delete_row(
400 p_api_version IN NUMBER,
401 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
402 x_return_status OUT NOCOPY VARCHAR2,
403 x_msg_count OUT NOCOPY NUMBER,
404 x_msg_data OUT NOCOPY VARCHAR2,
405 p_catv_rec IN catv_rec_type) IS
406
407 l_catv_rec catv_rec_type;
408 l_okc_catv_rec_in okc_k_article_pub.catv_rec_type;
409 l_okc_catv_rec_out okc_k_article_pub.catv_rec_type;
410
411 l_api_name CONSTANT VARCHAR2(30) := 'DELETE_K_ARTICLE';
412 l_api_version CONSTANT NUMBER := 1.0;
413 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
414 BEGIN
415 -- call START_ACTIVITY to create savepoint, check compatibility
416 -- and initialize message list
417 l_return_status := OKC_API.START_ACTIVITY(
418 p_api_name => l_api_name,
419 p_pkg_name => g_pkg_name,
420 p_init_msg_list => p_init_msg_list,
421 l_api_version => l_api_version,
422 p_api_version => p_api_version,
423 p_api_type => g_api_type,
424 x_return_status => x_return_status);
425
426 -- check if activity started successfully
427 If (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
428 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
429 Elsif (l_return_status = OKC_API.G_RET_STS_ERROR) then
430 raise OKC_API.G_EXCEPTION_ERROR;
431 End If;
432
433 l_catv_rec := p_catv_rec;
434 -- call procedure in complex API
435
436 migrate_catv(p_from => l_catv_rec,
437 p_to => l_okc_catv_rec_in);
438
439 OKC_K_ARTICLE_PUB.delete_k_article(
440 p_api_version => p_api_version,
441 p_init_msg_list => p_init_msg_list,
442 x_return_status => x_return_status,
443 x_msg_count => x_msg_count,
444 x_msg_data => x_msg_data,
445 p_catv_rec => l_okc_catv_rec_in);
446
447 -- check return status
448 If x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR Then
449 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
450 Elsif x_return_status = OKC_API.G_RET_STS_ERROR Then
451 raise OKC_API.G_EXCEPTION_ERROR;
452 End If;
453
454 -- end activity
455 OKC_API.END_ACTIVITY( x_msg_count => x_msg_count,
456 x_msg_data => x_msg_data);
457 EXCEPTION
458 when OKC_API.G_EXCEPTION_ERROR then
459 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
460 p_api_name => l_api_name,
461 p_pkg_name => g_pkg_name,
462 p_exc_name => 'OKC_API.G_RET_STS_ERROR',
463 x_msg_count => x_msg_count,
464 x_msg_data => x_msg_data,
465 p_api_type => g_api_type);
466
467 when OKC_API.G_EXCEPTION_UNEXPECTED_ERROR then
468 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
469 p_api_name => l_api_name,
470 p_pkg_name => g_pkg_name,
471 p_exc_name => 'OKC_API.G_RET_STS_UNEXP_ERROR',
472 x_msg_count => x_msg_count,
473 x_msg_data => x_msg_data,
474 p_api_type => g_api_type);
475
476 when OTHERS then
477 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
478 p_api_name => l_api_name,
479 p_pkg_name => g_pkg_name,
480 p_exc_name => 'OTHERS',
481 x_msg_count => x_msg_count,
482 x_msg_data => x_msg_data,
483 p_api_type => g_api_type);
484
485 END delete_row;
486
487 ---------------------------------------------------------------------
488 -- MIGRATION API FOR calling api OKC_RULE_PUB
489 ---------------------------------------------------------------------
490 --INSERTING ROW Rule Groups
491 PROCEDURE insert_row(
492 p_api_version IN NUMBER,
493 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
494 x_return_status OUT NOCOPY VARCHAR2,
495 x_msg_count OUT NOCOPY NUMBER,
496 x_msg_data OUT NOCOPY VARCHAR2,
497 p_rgpv_rec IN rgpv_rec_type,
498 x_rgpv_rec OUT NOCOPY rgpv_rec_type) IS
499
500 l_rgpv_rec rgpv_rec_type;
501 l_okc_rgpv_rec_in okc_rule_pub.rgpv_rec_type;
502 l_okc_rgpv_rec_out okc_rule_pub.rgpv_rec_type;
503
504 l_api_name CONSTANT VARCHAR2(30) := 'INSERT_ROW';
505 l_api_version CONSTANT NUMBER := 1.0;
506 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
507 BEGIN
508 -- call START_ACTIVITY to create savepoint, check compatibility
509 -- and initialize message list
510 l_return_status := OKC_API.START_ACTIVITY(
511 p_api_name => l_api_name,
512 p_pkg_name => g_pkg_name,
513 p_init_msg_list => p_init_msg_list,
514 l_api_version => l_api_version,
515 p_api_version => p_api_version,
516 p_api_type => g_api_type,
517 x_return_status => x_return_status);
518
519 -- check if activity started successfully
520 If (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
521 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
522 Elsif (l_return_status = OKC_API.G_RET_STS_ERROR) then
523 raise OKC_API.G_EXCEPTION_ERROR;
524 End If;
525
526 l_rgpv_rec := p_rgpv_rec;
527 -- call procedure in complex API
528
529 migrate_rgpv(p_from => l_rgpv_rec,
530 p_to => l_okc_rgpv_rec_in);
531
532 OKC_RULE_PUB.create_rule_group(
533 p_api_version => p_api_version,
534 p_init_msg_list => p_init_msg_list,
535 x_return_status => x_return_status,
536 x_msg_count => x_msg_count,
537 x_msg_data => x_msg_data,
538 p_rgpv_rec => l_okc_rgpv_rec_in,
539 x_rgpv_rec => l_okc_rgpv_rec_out);
540
541 -- check return status
542 If x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR Then
543 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
544 Elsif x_return_status = OKC_API.G_RET_STS_ERROR Then
545 raise OKC_API.G_EXCEPTION_ERROR;
546 End If;
547
548 migrate_rgpv(p_from => l_okc_rgpv_rec_out,
549 p_to => x_rgpv_rec);
550
551 -- end activity
552 OKC_API.END_ACTIVITY( x_msg_count => x_msg_count,
553 x_msg_data => x_msg_data);
554 EXCEPTION
555 when OKC_API.G_EXCEPTION_ERROR then
556 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
557 p_api_name => l_api_name,
558 p_pkg_name => g_pkg_name,
559 p_exc_name => 'OKC_API.G_RET_STS_ERROR',
560 x_msg_count => x_msg_count,
561 x_msg_data => x_msg_data,
562 p_api_type => g_api_type);
563
564 when OKC_API.G_EXCEPTION_UNEXPECTED_ERROR then
565 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
566 p_api_name => l_api_name,
567 p_pkg_name => g_pkg_name,
568 p_exc_name => 'OKC_API.G_RET_STS_UNEXP_ERROR',
569 x_msg_count => x_msg_count,
570 x_msg_data => x_msg_data,
571 p_api_type => g_api_type);
572
573 when OTHERS then
574 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
575 p_api_name => l_api_name,
576 p_pkg_name => g_pkg_name,
577 p_exc_name => 'OTHERS',
578 x_msg_count => x_msg_count,
579 x_msg_data => x_msg_data,
580 p_api_type => g_api_type);
581
582 END insert_row;
583
584 -----------------------------------------------------------------------------------------------------------
585 -- UPDATING ROW Rule Groups
586 PROCEDURE update_row(
587 p_api_version IN NUMBER,
588 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
589 x_return_status OUT NOCOPY VARCHAR2,
590 x_msg_count OUT NOCOPY NUMBER,
591 x_msg_data OUT NOCOPY VARCHAR2,
592 p_rgpv_rec IN rgpv_rec_type,
593 x_rgpv_rec OUT NOCOPY rgpv_rec_type) IS
594
595 l_rgpv_rec rgpv_rec_type;
596 l_okc_rgpv_rec_in okc_rule_pub.rgpv_rec_type;
597 l_okc_rgpv_rec_out okc_rule_pub.rgpv_rec_type;
598
599 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_ROW';
600 l_api_version CONSTANT NUMBER := 1.0;
601 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
602 BEGIN
603 -- call START_ACTIVITY to create savepoint, check compatibility
604 -- and initialize message list
605 l_return_status := OKC_API.START_ACTIVITY(
606 p_api_name => l_api_name,
607 p_pkg_name => g_pkg_name,
608 p_init_msg_list => p_init_msg_list,
609 l_api_version => l_api_version,
610 p_api_version => p_api_version,
611 p_api_type => g_api_type,
612 x_return_status => x_return_status);
613
614 -- check if activity started successfully
615 If (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
616 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
617 Elsif (l_return_status = OKC_API.G_RET_STS_ERROR) then
618 raise OKC_API.G_EXCEPTION_ERROR;
619 End If;
620
621 l_rgpv_rec := p_rgpv_rec;
622 -- call procedure in complex API
623
624 migrate_rgpv(p_from => l_rgpv_rec,
625 p_to => l_okc_rgpv_rec_in);
626
627 OKC_RULE_PUB.update_rule_group(
628 p_api_version => p_api_version,
629 p_init_msg_list => p_init_msg_list,
630 x_return_status => x_return_status,
631 x_msg_count => x_msg_count,
632 x_msg_data => x_msg_data,
633 p_rgpv_rec => l_okc_rgpv_rec_in,
634 x_rgpv_rec => l_okc_rgpv_rec_out);
635
636 -- check return status
637 If x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR Then
638 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
639 Elsif x_return_status = OKC_API.G_RET_STS_ERROR Then
640 raise OKC_API.G_EXCEPTION_ERROR;
641 End If;
642
643 migrate_rgpv(p_from => l_okc_rgpv_rec_out,
644 p_to => x_rgpv_rec);
645
646 -- end activity
647 OKC_API.END_ACTIVITY( x_msg_count => x_msg_count,
648 x_msg_data => x_msg_data);
649 EXCEPTION
650 when OKC_API.G_EXCEPTION_ERROR then
651 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
652 p_api_name => l_api_name,
653 p_pkg_name => g_pkg_name,
654 p_exc_name => 'OKC_API.G_RET_STS_ERROR',
655 x_msg_count => x_msg_count,
656 x_msg_data => x_msg_data,
657 p_api_type => g_api_type);
658
659 when OKC_API.G_EXCEPTION_UNEXPECTED_ERROR then
660 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
661 p_api_name => l_api_name,
662 p_pkg_name => g_pkg_name,
663 p_exc_name => 'OKC_API.G_RET_STS_UNEXP_ERROR',
664 x_msg_count => x_msg_count,
665 x_msg_data => x_msg_data,
666 p_api_type => g_api_type);
667
668 when OTHERS then
669 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
670 p_api_name => l_api_name,
671 p_pkg_name => g_pkg_name,
672 p_exc_name => 'OTHERS',
673 x_msg_count => x_msg_count,
674 x_msg_data => x_msg_data,
675 p_api_type => g_api_type);
676
677 END update_row;
678
679 -----------------------------------------------------------------------------------------------------------
680 -- DELETING ROW Rule Groups
681
682 PROCEDURE delete_row(
683 p_api_version IN NUMBER,
684 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
685 x_return_status OUT NOCOPY VARCHAR2,
686 x_msg_count OUT NOCOPY NUMBER,
687 x_msg_data OUT NOCOPY VARCHAR2,
688 p_rgpv_rec IN rgpv_rec_type) IS
689
690 l_rgpv_rec rgpv_rec_type;
691 l_okc_rgpv_rec_in okc_rule_pub.rgpv_rec_type;
692 l_okc_rgpv_rec_out okc_rule_pub.rgpv_rec_type;
693
694 l_api_name CONSTANT VARCHAR2(30) := 'DELETE_ROW';
695 l_api_version CONSTANT NUMBER := 1.0;
696 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
697 BEGIN
698 -- call START_ACTIVITY to create savepoint, check compatibility
699 -- and initialize message list
700 l_return_status := OKC_API.START_ACTIVITY(
701 p_api_name => l_api_name,
702 p_pkg_name => g_pkg_name,
703 p_init_msg_list => p_init_msg_list,
704 l_api_version => l_api_version,
705 p_api_version => p_api_version,
706 p_api_type => g_api_type,
707 x_return_status => x_return_status);
708
709 -- check if activity started successfully
710 If (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
711 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
712 Elsif (l_return_status = OKC_API.G_RET_STS_ERROR) then
713 raise OKC_API.G_EXCEPTION_ERROR;
714 End If;
715
716 l_rgpv_rec := p_rgpv_rec;
717 -- call procedure in complex API
718
719 migrate_rgpv(p_from => l_rgpv_rec,
720 p_to => l_okc_rgpv_rec_in);
721
722 OKC_RULE_PUB.delete_rule_group(
723 p_api_version => p_api_version,
724 p_init_msg_list => p_init_msg_list,
725 x_return_status => x_return_status,
726 x_msg_count => x_msg_count,
727 x_msg_data => x_msg_data,
728 p_rgpv_rec => l_okc_rgpv_rec_in);
729
730 -- check return status
731 If x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR Then
732 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
733 Elsif x_return_status = OKC_API.G_RET_STS_ERROR Then
734 raise OKC_API.G_EXCEPTION_ERROR;
735 End If;
736
737 -- end activity
738 OKC_API.END_ACTIVITY( x_msg_count => x_msg_count,
739 x_msg_data => x_msg_data);
740 EXCEPTION
741 when OKC_API.G_EXCEPTION_ERROR then
742 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
743 p_api_name => l_api_name,
744 p_pkg_name => g_pkg_name,
745 p_exc_name => 'OKC_API.G_RET_STS_ERROR',
746 x_msg_count => x_msg_count,
747 x_msg_data => x_msg_data,
748 p_api_type => g_api_type);
749
750 when OKC_API.G_EXCEPTION_UNEXPECTED_ERROR then
751 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
752 p_api_name => l_api_name,
753 p_pkg_name => g_pkg_name,
754 p_exc_name => 'OKC_API.G_RET_STS_UNEXP_ERROR',
755 x_msg_count => x_msg_count,
756 x_msg_data => x_msg_data,
757 p_api_type => g_api_type);
758
759 when OTHERS then
760 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
761 p_api_name => l_api_name,
762 p_pkg_name => g_pkg_name,
763 p_exc_name => 'OTHERS',
764 x_msg_count => x_msg_count,
765 x_msg_data => x_msg_data,
766 p_api_type => g_api_type);
767
768 END delete_row;
769 ---------------------------------------------------------------------------------
770 -- execute_qa_check_list
771
772 PROCEDURE execute_qa_check_list(
773 p_api_version IN NUMBER,
774 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
775 x_return_status OUT NOCOPY VARCHAR2,
776 x_msg_count OUT NOCOPY NUMBER,
777 x_msg_data OUT NOCOPY VARCHAR2,
778 p_qcl_id IN NUMBER,
779 p_chr_id IN NUMBER,
780 x_msg_tbl OUT NOCOPY msg_tbl_type) IS
781
782 l_msgv_rec qa_msg_rec_type;
783 l_okc_msgv_rec_out qa_msg_rec_type;
784 l_okc_msgv_tbl_out okc_qa_check_pub.msg_tbl_type;
785
786 l_api_name CONSTANT VARCHAR2(30) := 'execute_qa_check_list';
787 l_api_version CONSTANT NUMBER := 1.0;
788 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
789 BEGIN
790 -- call START_ACTIVITY to create savepoint, check compatibility
791 -- and initialize message list
792 l_return_status := OKC_API.START_ACTIVITY(
793 p_api_name => l_api_name,
794 p_pkg_name => g_pkg_name,
795 p_init_msg_list => p_init_msg_list,
796 l_api_version => l_api_version,
797 p_api_version => p_api_version,
798 p_api_type => g_api_type,
799 x_return_status => x_return_status);
800
801 -- check if activity started successfully
802 If (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
803 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
804 Elsif (l_return_status = OKC_API.G_RET_STS_ERROR) then
805 raise OKC_API.G_EXCEPTION_ERROR;
806 End If;
807
808 OKC_QA_CHECK_PUB.execute_qa_check_list(
809 p_api_version => p_api_version,
810 p_init_msg_list => p_init_msg_list,
811 x_return_status => x_return_status,
812 x_msg_count => x_msg_count,
813 x_msg_data => x_msg_data,
814 p_qcl_id => p_qcl_id,
815 p_chr_id => p_chr_id,
816 x_msg_tbl => l_okc_msgv_tbl_out);
817
818 -- check return status
819 If x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR Then
820 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
821 Elsif x_return_status = OKC_API.G_RET_STS_ERROR Then
822 raise OKC_API.G_EXCEPTION_ERROR;
823 End If;
824
825 migrate_msgv(p_from => l_okc_msgv_tbl_out,
826 p_to => x_msg_tbl);
827
828 -- end activity
829 OKC_API.END_ACTIVITY( x_msg_count => x_msg_count,
830 x_msg_data => x_msg_data);
831 EXCEPTION
832 when OKC_API.G_EXCEPTION_ERROR then
833 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
834 p_api_name => l_api_name,
835 p_pkg_name => g_pkg_name,
836 p_exc_name => 'OKC_API.G_RET_STS_ERROR',
837 x_msg_count => x_msg_count,
838 x_msg_data => x_msg_data,
839 p_api_type => g_api_type);
840
841 when OKC_API.G_EXCEPTION_UNEXPECTED_ERROR then
842 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
843 p_api_name => l_api_name,
844 p_pkg_name => g_pkg_name,
845 p_exc_name => 'OKC_API.G_RET_STS_UNEXP_ERROR',
846 x_msg_count => x_msg_count,
847 x_msg_data => x_msg_data,
848 p_api_type => g_api_type);
849
850 when OTHERS then
851 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
852 p_api_name => l_api_name,
853 p_pkg_name => g_pkg_name,
854 p_exc_name => 'OTHERS',
855 x_msg_count => x_msg_count,
856 x_msg_data => x_msg_data,
857 p_api_type => g_api_type);
858
859 END execute_qa_check_list;
860 --
861 -- The below Update allowed has been replaced by full implementation
862 -- of the same. This is because there is a bug in okc_contract_pub.
863 --update_allowed. As soon as the bug is solved, the immedialtely below
864 -- function needs to be used and the full implementation removed.
865 --FUNCTION Update_Allowed(p_chr_id IN NUMBER) RETURN VARCHAR2 IS
866 --BEGIN
867 -- return (OKC_CONTRACT_PUB.Update_Allowed(p_chr_id));
868 --END Update_Allowed;
869
870 --
871 -- function that checkes whether a contract is updateable or not
872 -- returns 'Y' if updateable, 'N' if not.
873 -- returns OKC_API.G_RET_STS_ERROR or OKC_API.G_RET_STS_UNEXP_ERROR
874 -- in case of error
875 --
876 FUNCTION Update_Allowed(p_chr_id IN NUMBER) RETURN VARCHAR2 Is
877 l_sts_code OKC_ASSENTS.STS_CODE%TYPE;
878 l_scs_code OKC_ASSENTS.SCS_CODE%TYPE;
879 l_return_value VARCHAR2(1) := 'N';
880
881 Cursor l_chrv_csr Is
882 SELECT sts_code, scs_code
883 FROM OKC_K_HEADERS_B
884 WHERE id = p_chr_id;
885
886 Cursor l_astv_csr Is
887 SELECT upper(substr(allowed_yn,1,1))
888 FROM okc_assents
889 WHERE sts_code = l_sts_code
890 AND scs_code = l_scs_code
891 AND opn_code = 'UPDATE';
892 BEGIN
893 -- get status from contract headers
894 Open l_chrv_csr;
895 Fetch l_chrv_csr Into l_sts_code, l_scs_code;
896 If l_chrv_csr%FOUND Then
897 Close l_chrv_csr;
898 Open l_astv_csr;
899 Fetch l_astv_csr into l_return_value;
900 If (l_return_value not in ('Y','N')) Then
901 l_return_value := OKC_API.G_RET_STS_UNEXP_ERROR;
902 End If;
903 Close l_astv_csr;
904 Else
905 Close l_chrv_csr;
906 End If;
907 return l_return_value;
908 Exception
909 when OTHERS then
910 -- store SQL error message on message stack
911 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
912 p_msg_name => g_unexpected_error,
913 p_token1 => g_sqlcode_token,
914 p_token1_value => sqlcode,
915 p_token2 => g_sqlerrm_token,
916 p_token2_value => sqlerrm);
917
918 -- notify caller of an UNEXPETED error
919 l_return_value := OKC_API.G_RET_STS_UNEXP_ERROR;
920 END Update_Allowed;
921
922 END; -- Package Body OKL_OKC_MIGRATION_A_PVT