[Home] [Help]
PACKAGE BODY: APPS.OKL_RULE_PUB
Source
1 PACKAGE BODY okl_rule_pub AS
2 /* $Header: OKLPRULB.pls 120.5 2006/12/18 12:49:27 nikshah noship $ */
3
4 G_GEN_COMMENTS VARCHAR2(1) := 'T';
5 subtype okc_rulv_rec_type is okc_rule_pub.rulv_rec_type;
6 TYPE okc_rulv_tbl_type IS TABLE OF okc_rulv_rec_type INDEX BY BINARY_INTEGER;
7 ---------------------------------------------------------------------------
8 -- FUNCTION migrate_rulv out
9 ---------------------------------------------------------------------------
10 FUNCTION migrate_rulv_out
11 (p_rulv_rec IN okc_rulv_rec_type) return rulv_rec_type IS
12 l_rulv_rec rulv_rec_type;
13 BEGIN
14 l_rulv_rec.id := p_rulv_rec.id;
15 l_rulv_rec.object_version_number := p_rulv_rec.object_version_number;
16 l_rulv_rec.created_by := p_rulv_rec.created_by;
17 l_rulv_rec.creation_date := p_rulv_rec.creation_date;
18 l_rulv_rec.last_updated_by := p_rulv_rec.last_updated_by;
19 l_rulv_rec.last_update_date := p_rulv_rec.last_update_date;
20 l_rulv_rec.last_update_login := p_rulv_rec.last_update_login;
21 l_rulv_rec.object1_id1 := p_rulv_rec.object1_id1;
22 l_rulv_rec.object2_id1 := p_rulv_rec.object2_id1;
23 l_rulv_rec.object3_id1 := p_rulv_rec.object3_id1;
24 l_rulv_rec.object1_id2 := p_rulv_rec.object1_id2;
25 l_rulv_rec.object2_id2 := p_rulv_rec.object2_id2;
26 l_rulv_rec.object3_id2 := p_rulv_rec.object3_id2;
27 l_rulv_rec.jtot_object1_code := p_rulv_rec.jtot_object1_code;
28 l_rulv_rec.jtot_object2_code := p_rulv_rec.jtot_object2_code;
29 l_rulv_rec.jtot_object3_code := p_rulv_rec.jtot_object3_code;
30 l_rulv_rec.sfwt_flag := p_rulv_rec.sfwt_flag;
31 l_rulv_rec.rgp_id := p_rulv_rec.rgp_id;
32 l_rulv_rec.dnz_chr_id := p_rulv_rec.dnz_chr_id;
33 l_rulv_rec.priority := p_rulv_rec.priority;
34 l_rulv_rec.std_template_yn := p_rulv_rec.std_template_yn;
35 l_rulv_rec.warn_yn := p_rulv_rec.warn_yn;
36 l_rulv_rec.comments := p_rulv_rec.comments;
37 l_rulv_rec.attribute_category := p_rulv_rec.attribute_category;
38 l_rulv_rec.attribute1 := p_rulv_rec.attribute1;
39 l_rulv_rec.attribute2 := p_rulv_rec.attribute2;
40 l_rulv_rec.attribute3 := p_rulv_rec.attribute3;
41 l_rulv_rec.attribute4 := p_rulv_rec.attribute4;
42 l_rulv_rec.attribute5 := p_rulv_rec.attribute5;
43 l_rulv_rec.attribute6 := p_rulv_rec.attribute6;
44 l_rulv_rec.attribute7 := p_rulv_rec.attribute7;
45 l_rulv_rec.attribute8 := p_rulv_rec.attribute8;
46 l_rulv_rec.attribute9 := p_rulv_rec.attribute9;
47 l_rulv_rec.attribute10 := p_rulv_rec.attribute10;
48 l_rulv_rec.attribute11 := p_rulv_rec.attribute11;
49 l_rulv_rec.attribute12 := p_rulv_rec.attribute12;
50 l_rulv_rec.attribute13 := p_rulv_rec.attribute13;
51 l_rulv_rec.attribute14 := p_rulv_rec.attribute14;
52 l_rulv_rec.attribute15 := p_rulv_rec.attribute15;
53 --l_rulv_rec.text := p_rulv_rec2.text;
54 l_rulv_rec.rule_information_category := p_rulv_rec.rule_information_category;
55 l_rulv_rec.rule_information1 := p_rulv_rec.rule_information1;
56 l_rulv_rec.rule_information2 := p_rulv_rec.rule_information2;
57 l_rulv_rec.rule_information3 := p_rulv_rec.rule_information3;
58 l_rulv_rec.rule_information4 := p_rulv_rec.rule_information4;
59 l_rulv_rec.rule_information5 := p_rulv_rec.rule_information5;
60 l_rulv_rec.rule_information6 := p_rulv_rec.rule_information6;
61 l_rulv_rec.rule_information7 := p_rulv_rec.rule_information7;
62 l_rulv_rec.rule_information8 := p_rulv_rec.rule_information8;
63 l_rulv_rec.rule_information9 := p_rulv_rec.rule_information9;
64 l_rulv_rec.rule_information10 := p_rulv_rec.rule_information10;
65 l_rulv_rec.rule_information11 := p_rulv_rec.rule_information11;
66 l_rulv_rec.rule_information12 := p_rulv_rec.rule_information12;
67 l_rulv_rec.rule_information13 := p_rulv_rec.rule_information13;
68 l_rulv_rec.rule_information14 := p_rulv_rec.rule_information14;
69 l_rulv_rec.rule_information15 := p_rulv_rec.rule_information15;
70 l_rulv_rec.template_yn := NVL(p_rulv_rec.template_yn,'N');
71 l_rulv_rec.ans_set_jtot_object_code := NVL(p_rulv_rec.ans_set_jtot_object_code,'');
72 l_rulv_rec.ans_set_jtot_object_id1 := NVL(p_rulv_rec.ans_set_jtot_object_id1,'');
73 l_rulv_rec.ans_set_jtot_object_id2 := NVL(p_rulv_rec.ans_set_jtot_object_id2,'');
74 l_rulv_rec.display_sequence := NVL(p_rulv_rec.display_sequence,'');
75
76 RETURN (l_rulv_rec);
77 END migrate_rulv_out;
78 ---------------------------------------------------------------------------
79 -- FUNCTION migrate_rulv
80 ---------------------------------------------------------------------------
81 FUNCTION migrate_rulv (
82 p_rulv_rec1 IN rulv_rec_type,
83 p_rulv_rec2 IN rulv_rec_type
84 ) RETURN okc_rulv_rec_type IS
85 l_rulv_rec okc_rulv_rec_type;
86 BEGIN
87 l_rulv_rec.id := p_rulv_rec1.id;
88 l_rulv_rec.object_version_number := p_rulv_rec1.object_version_number;
89 l_rulv_rec.created_by := p_rulv_rec1.created_by;
90 l_rulv_rec.creation_date := p_rulv_rec1.creation_date;
91 l_rulv_rec.last_updated_by := p_rulv_rec1.last_updated_by;
92 l_rulv_rec.last_update_date := p_rulv_rec1.last_update_date;
93 l_rulv_rec.last_update_login := p_rulv_rec1.last_update_login;
94 l_rulv_rec.object1_id1 := p_rulv_rec2.object1_id1;
95 l_rulv_rec.object2_id1 := p_rulv_rec2.object2_id1;
96 l_rulv_rec.object3_id1 := p_rulv_rec2.object3_id1;
97 l_rulv_rec.object1_id2 := p_rulv_rec2.object1_id2;
98 l_rulv_rec.object2_id2 := p_rulv_rec2.object2_id2;
99 l_rulv_rec.object3_id2 := p_rulv_rec2.object3_id2;
100 l_rulv_rec.jtot_object1_code := p_rulv_rec2.jtot_object1_code;
101 l_rulv_rec.jtot_object2_code := p_rulv_rec2.jtot_object2_code;
102 l_rulv_rec.jtot_object3_code := p_rulv_rec2.jtot_object3_code;
103 l_rulv_rec.sfwt_flag := p_rulv_rec2.sfwt_flag;
104 l_rulv_rec.rgp_id := p_rulv_rec2.rgp_id;
105 l_rulv_rec.dnz_chr_id := p_rulv_rec2.dnz_chr_id;
106 l_rulv_rec.priority := p_rulv_rec2.priority;
107 l_rulv_rec.std_template_yn := p_rulv_rec2.std_template_yn;
108 l_rulv_rec.warn_yn := p_rulv_rec2.warn_yn;
109 l_rulv_rec.comments := p_rulv_rec2.comments;
110 l_rulv_rec.attribute_category := p_rulv_rec2.attribute_category;
111 l_rulv_rec.attribute1 := p_rulv_rec2.attribute1;
112 l_rulv_rec.attribute2 := p_rulv_rec2.attribute2;
113 l_rulv_rec.attribute3 := p_rulv_rec2.attribute3;
114 l_rulv_rec.attribute4 := p_rulv_rec2.attribute4;
115 l_rulv_rec.attribute5 := p_rulv_rec2.attribute5;
116 l_rulv_rec.attribute6 := p_rulv_rec2.attribute6;
117 l_rulv_rec.attribute7 := p_rulv_rec2.attribute7;
118 l_rulv_rec.attribute8 := p_rulv_rec2.attribute8;
119 l_rulv_rec.attribute9 := p_rulv_rec2.attribute9;
120 l_rulv_rec.attribute10 := p_rulv_rec2.attribute10;
121 l_rulv_rec.attribute11 := p_rulv_rec2.attribute11;
122 l_rulv_rec.attribute12 := p_rulv_rec2.attribute12;
123 l_rulv_rec.attribute13 := p_rulv_rec2.attribute13;
124 l_rulv_rec.attribute14 := p_rulv_rec2.attribute14;
125 l_rulv_rec.attribute15 := p_rulv_rec2.attribute15;
126 --l_rulv_rec.text := p_rulv_rec2.text;
127 l_rulv_rec.rule_information_category := p_rulv_rec2.rule_information_category;
128 l_rulv_rec.rule_information1 := p_rulv_rec2.rule_information1;
129 l_rulv_rec.rule_information2 := p_rulv_rec2.rule_information2;
130 l_rulv_rec.rule_information3 := p_rulv_rec2.rule_information3;
131 l_rulv_rec.rule_information4 := p_rulv_rec2.rule_information4;
132 l_rulv_rec.rule_information5 := p_rulv_rec2.rule_information5;
133 l_rulv_rec.rule_information6 := p_rulv_rec2.rule_information6;
134 l_rulv_rec.rule_information7 := p_rulv_rec2.rule_information7;
135 l_rulv_rec.rule_information8 := p_rulv_rec2.rule_information8;
136 l_rulv_rec.rule_information9 := p_rulv_rec2.rule_information9;
137 l_rulv_rec.rule_information10 := p_rulv_rec2.rule_information10;
138 l_rulv_rec.rule_information11 := p_rulv_rec2.rule_information11;
139 l_rulv_rec.rule_information12 := p_rulv_rec2.rule_information12;
140 l_rulv_rec.rule_information13 := p_rulv_rec2.rule_information13;
141 l_rulv_rec.rule_information14 := p_rulv_rec2.rule_information14;
142 l_rulv_rec.rule_information15 := p_rulv_rec2.rule_information15;
143 l_rulv_rec.template_yn := NVL(p_rulv_rec2.template_yn,'N');
144 l_rulv_rec.ans_set_jtot_object_code := NVL(p_rulv_rec2.ans_set_jtot_object_code,'');
145 l_rulv_rec.ans_set_jtot_object_id1 := NVL(p_rulv_rec2.ans_set_jtot_object_id1,'');
146 l_rulv_rec.ans_set_jtot_object_id2 := NVL(p_rulv_rec2.ans_set_jtot_object_id2,'');
147 l_rulv_rec.display_sequence := NVL(p_rulv_rec2.display_sequence,'');
148
149 RETURN (l_rulv_rec);
150 END migrate_rulv;
151
152 --------------------------------------
153 -- PROCEDURE create_rule
154 --------------------------------------
155 PROCEDURE create_rule(
156 p_api_version IN NUMBER,
157 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
158 x_return_status OUT NOCOPY VARCHAR2,
159 x_msg_count OUT NOCOPY NUMBER,
160 x_msg_data OUT NOCOPY VARCHAR2,
161 p_rulv_rec IN rulv_rec_type,
162 x_rulv_rec OUT NOCOPY rulv_rec_type,
163 p_euro_conv_yn IN VARCHAR2) IS
164
165 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
166 l_api_name CONSTANT VARCHAR2(30) := 'create_rule';
167 l_rulv_rec rulv_rec_type := p_rulv_rec;
168 l_rulv_rec_okc okc_rulv_rec_type;
169 l_rulv_rec_okc_out okc_rulv_rec_type;
170 l_clob clob;
171
172 -- Start 14-Sep-2005 Manu
173 CURSOR lc_category_csr (p_chr_id IN OKC_K_HEADERS_V.ID%TYPE) IS
174 SELECT scs_code
175 FROM OKC_K_HEADERS_V
176 WHERE id = p_chr_id;
177 l_scs_code OKC_K_HEADERS_V.SCS_CODE%TYPE;
178 -- End 14-Sep-2005 Manu
179 BEGIN
180 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
181 p_init_msg_list,
182 '_PUB',
183 x_return_status);
184 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
185 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
186 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
187 raise OKC_API.G_EXCEPTION_ERROR;
188 END IF;
189 g_rulv_rec := l_rulv_rec;
190 /************************************************************
191 g_rulv_rec := p_rulv_rec;
192 IF (DBMS_LOB.istemporary(p_rulv_rec.TEXT) = 1) THEN
193 DBMS_LOB.CREATETEMPORARY(g_rulv_rec.TEXT,FALSE,DBMS_LOB.CALL);
194 l_clob := p_rulv_rec.TEXT;
195 DBMS_LOB.OPEN(l_clob, DBMS_LOB.LOB_READONLY);
196 DBMS_LOB.OPEN(g_rulv_rec.TEXT, DBMS_LOB.LOB_READWRITE);
197 DBMS_LOB.COPY(dest_lob => g_rulv_rec.TEXT,
198 src_lob => l_clob,
199 amount => dbms_lob.getlength(l_clob));
200 DBMS_LOB.CLOSE(g_rulv_rec.TEXT);
201 DBMS_LOB.CLOSE(l_clob);
202 DBMS_LOB.freetemporary(l_clob);
203 END IF;
204 ************************************************************/
205 okc_util.call_user_hook(x_return_status, g_pkg_name, l_api_name, 'B');
206 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
207 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
208 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
209 raise OKC_API.G_EXCEPTION_ERROR;
210 END IF;
211
212 --Bug 5697488 start by nikshah
213 IF (g_rulv_rec.rule_information_category = 'LABACC') THEN
214 g_rulv_rec.rule_information15 := '12.0';
215 END IF;
216 --Bug 5697488 end by nikshah
217
218 l_rulv_rec_okc := migrate_rulv(l_rulv_rec, g_rulv_rec);
219
220 OKC_RULE_PUB.create_rule(
221 p_api_version => p_api_version,
222 p_init_msg_list => p_init_msg_list,
223 x_return_status => x_return_status,
224 x_msg_count => x_msg_count,
225 x_msg_data => x_msg_data,
226 p_rulv_rec => l_rulv_rec_okc,
227 x_rulv_rec => l_rulv_rec_okc_out,
228 p_euro_conv_yn => p_euro_conv_yn);
229
230 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
231 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
232 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
233 raise OKC_API.G_EXCEPTION_ERROR;
234 END IF;
235
236 x_rulv_rec := migrate_rulv_out(l_rulv_rec_okc_out);
237
238 g_rulv_rec := x_rulv_rec;
239 okc_util.call_user_hook(x_return_status, g_pkg_name, l_api_name, 'A');
240 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
241 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
242 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
243 raise OKC_API.G_EXCEPTION_ERROR;
244 END IF;
245
246 --code to flip status to 'INCOMPLETE' for lease contract if this is an edit point
247 -- edit points for lease contract are any modifications between statuses
248 -- 'PASSED' and 'APPROVED'
249 If (x_rulv_rec.dnz_chr_id is not NULL) AND (x_rulv_rec.dnz_chr_id <> OKL_API.G_MISS_NUM) Then
250
251 okl_contract_status_pub.cascade_lease_status_edit
252 (p_api_version => p_api_version,
253 p_init_msg_list => p_init_msg_list,
254 x_return_status => x_return_status,
255 x_msg_count => x_msg_count,
256 x_msg_data => x_msg_data,
257 p_chr_id => x_rulv_rec.dnz_chr_id);
258
259 If x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR Then
260 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
261 Elsif x_return_status = OKC_API.G_RET_STS_ERROR Then
262 raise OKC_API.G_EXCEPTION_ERROR;
263 End If;
264
265 End If;
266
267 -- Start 14-Sep-2005 Manu
268 --Flip status of the PROGRAM agreement from 'PASSED' to 'INCOMPLETE'
269 If (x_rulv_rec.dnz_chr_id is not NULL) AND (x_rulv_rec.dnz_chr_id <> OKL_API.G_MISS_NUM) Then
270 OPEN lc_category_csr (p_chr_id => x_rulv_rec.dnz_chr_id);
271 FETCH lc_category_csr INTO l_scs_code;
272 CLOSE lc_category_csr;
273 IF (l_scs_code = 'PROGRAM' ) THEN
274 OKL_VENDOR_PROGRAM_PVT.passed_to_incomplete(
275 p_api_version => p_api_version
276 ,p_init_msg_list => p_init_msg_list
277 ,x_return_status => x_return_status
278 ,x_msg_count => x_msg_count
279 ,x_msg_data => x_msg_data
280 ,p_program_id => x_rulv_rec.dnz_chr_id
281 );
282
283 If x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR Then
284 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
285 Elsif x_return_status = OKC_API.G_RET_STS_ERROR Then
286 raise OKC_API.G_EXCEPTION_ERROR;
287 End If;
288 END IF;
289 End If;
290 -- End 14-Sep-2005 Manu
291
292 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
293 EXCEPTION
294 WHEN OKC_API.G_EXCEPTION_ERROR THEN
295 x_return_status := OKC_API.HANDLE_EXCEPTIONS
296 (l_api_name
297 ,G_PKG_NAME
298 ,'OKC_API.G_RET_STS_ERROR'
299 ,x_msg_count
300 ,x_msg_data
301 ,'_PUB');
302 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
303 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
304 (l_api_name
305 ,G_PKG_NAME
306 ,'OKC_API.G_RET_STS_UNEXP_ERROR'
307 ,x_msg_count
308 ,x_msg_data
309 ,'_PUB');
310 WHEN OTHERS THEN
311 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
312 (l_api_name
313 ,G_PKG_NAME
314 ,'OTHERS'
315 ,x_msg_count
316 ,x_msg_data
317 ,'_PUB');
318 END create_rule;
319
320 --------------------------------------
321 -- PROCEDURE create_rule
322 --------------------------------------
323 PROCEDURE create_rule(
324 p_api_version IN NUMBER,
325 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
326 x_return_status OUT NOCOPY VARCHAR2,
327 x_msg_count OUT NOCOPY NUMBER,
328 x_msg_data OUT NOCOPY VARCHAR2,
329 p_rulv_rec IN rulv_rec_type,
330 x_rulv_rec OUT NOCOPY rulv_rec_type)
331 IS
332 BEGIN
333
334 create_rule(
335 p_api_version => p_api_version,
336 p_init_msg_list => p_init_msg_list,
337 x_return_status => x_return_status,
338 x_msg_count => x_msg_count,
339 x_msg_data => x_msg_data,
340 p_rulv_rec => p_rulv_rec,
341 x_rulv_rec => x_rulv_rec,
342 p_euro_conv_yn => 'N');
343
344 EXCEPTION
345 WHEN G_EXCEPTION_HALT_VALIDATION THEN
346 NULL;
347 WHEN OTHERS THEN
348 -- store SQL error message on message stack
349 OKC_API.SET_MESSAGE(
350 p_app_name => G_APP_NAME,
351 p_msg_name => G_UNEXPECTED_ERROR,
352 p_token1 => G_SQLCODE_TOKEN,
353 p_token1_value => SQLCODE,
354 p_token2 => G_SQLERRM_TOKEN,
355 p_token2_value => SQLERRM);
356 -- notify caller of an error as UNEXPETED error
357 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
358 END create_rule;
359
360 --------------------------------------
361 -- PROCEDURE create_rule
362 --------------------------------------
363 PROCEDURE create_rule(
364 p_api_version IN NUMBER,
365 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
366 x_return_status OUT NOCOPY VARCHAR2,
367 x_msg_count OUT NOCOPY NUMBER,
368 x_msg_data OUT NOCOPY VARCHAR2,
369 p_rulv_tbl IN rulv_tbl_type,
370 x_rulv_tbl OUT NOCOPY rulv_tbl_type) IS
371 i NUMBER := 0;
372 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
373 BEGIN
374 x_return_status := OKC_API.G_RET_STS_SUCCESS;
375 IF p_rulv_tbl.COUNT > 0 THEN
376 i := p_rulv_tbl.FIRST;
377 LOOP
378 create_rule(
379 p_api_version => p_api_version,
380 p_init_msg_list => p_init_msg_list,
381 x_return_status => l_return_status,
382 x_msg_count => x_msg_count,
383 x_msg_data => x_msg_data,
384 p_rulv_rec => p_rulv_tbl(i),
385 x_rulv_rec => x_rulv_tbl(i),
386 p_euro_conv_yn => 'N');
387 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
388 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
389 x_return_status := l_return_status;
390 raise G_EXCEPTION_HALT_VALIDATION;
391 ELSE
392 x_return_status := l_return_status;
393 END IF;
394 END IF;
395 EXIT WHEN (i = p_rulv_tbl.LAST);
396 i := p_rulv_tbl.NEXT(i);
397 END LOOP;
398 END IF;
399 EXCEPTION
400 WHEN G_EXCEPTION_HALT_VALIDATION THEN
401 NULL;
402 WHEN OTHERS THEN
403 -- store SQL error message on message stack
404 OKC_API.SET_MESSAGE(
405 p_app_name => G_APP_NAME,
406 p_msg_name => G_UNEXPECTED_ERROR,
407 p_token1 => G_SQLCODE_TOKEN,
408 p_token1_value => SQLCODE,
409 p_token2 => G_SQLERRM_TOKEN,
410 p_token2_value => SQLERRM);
411 -- notify caller of an error as UNEXPETED error
412 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
413 END create_rule;
414
415 --------------------------------------
416 -- PROCEDURE create_rule
417 --------------------------------------
418 PROCEDURE create_rule(
419 p_api_version IN NUMBER,
420 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
421 x_return_status OUT NOCOPY VARCHAR2,
422 x_msg_count OUT NOCOPY NUMBER,
423 x_msg_data OUT NOCOPY VARCHAR2,
424 p_rulv_tbl IN rulv_tbl_type,
425 x_rulv_tbl OUT NOCOPY rulv_tbl_type,
426 p_euro_conv_yn IN VARCHAR2)
427 IS
428 i NUMBER := 0;
429 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
430 BEGIN
431 x_return_status := OKC_API.G_RET_STS_SUCCESS;
432 IF p_rulv_tbl.COUNT > 0 THEN
433 i := p_rulv_tbl.FIRST;
434 LOOP
435 create_rule(
436 p_api_version => p_api_version,
437 p_init_msg_list => p_init_msg_list,
438 x_return_status => l_return_status,
439 x_msg_count => x_msg_count,
440 x_msg_data => x_msg_data,
441 p_rulv_rec => p_rulv_tbl(i),
442 x_rulv_rec => x_rulv_tbl(i),
443 p_euro_conv_yn => p_euro_conv_yn);
444 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
445 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
446 x_return_status := l_return_status;
447 raise G_EXCEPTION_HALT_VALIDATION;
448 ELSE
449 x_return_status := l_return_status;
450 END IF;
451 END IF;
452 EXIT WHEN (i = p_rulv_tbl.LAST);
453 i := p_rulv_tbl.NEXT(i);
454 END LOOP;
455 END IF;
456 EXCEPTION
457 WHEN G_EXCEPTION_HALT_VALIDATION THEN
458 NULL;
459 WHEN OTHERS THEN
460 -- store SQL error message on message stack
461 OKC_API.SET_MESSAGE(
462 p_app_name => G_APP_NAME,
463 p_msg_name => G_UNEXPECTED_ERROR,
464 p_token1 => G_SQLCODE_TOKEN,
465 p_token1_value => SQLCODE,
466 p_token2 => G_SQLERRM_TOKEN,
467 p_token2_value => SQLERRM);
468 -- notify caller of an error as UNEXPETED error
469 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
470 END create_rule;
471
472 --Bug# 2981308 : overloaded procedure with p_edit_mode
473 --------------------------------------
474 -- PROCEDURE update_rule
475 --------------------------------------
476 PROCEDURE update_rule(
477 p_api_version IN NUMBER,
478 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
479 x_return_status OUT NOCOPY VARCHAR2,
480 x_msg_count OUT NOCOPY NUMBER,
481 x_msg_data OUT NOCOPY VARCHAR2,
482 p_rulv_rec IN rulv_rec_type,
483 p_edit_mode IN VARCHAR2,
484 x_rulv_rec OUT NOCOPY rulv_rec_type) IS
485
486 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
487 l_api_name CONSTANT VARCHAR2(30) := 'update_rule';
488 l_rulv_rec rulv_rec_type := p_rulv_rec;
489 l_rulv_rec_okc okc_rulv_rec_type;
490 l_rulv_rec_okc_out okc_rulv_rec_type;
491 l_clob clob;
492
493 -- Start 14-Sep-2005 Manu
494 CURSOR lc_category_csr (p_chr_id IN OKC_K_HEADERS_V.ID%TYPE) IS
495 SELECT scs_code
496 FROM OKC_K_HEADERS_V
497 WHERE id = p_chr_id;
498 l_scs_code OKC_K_HEADERS_V.SCS_CODE%TYPE;
499 -- End 14-Sep-2005 Manu
500 BEGIN
501 x_return_status := OKL_API.G_RET_STS_SUCCESS;
502 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
503 p_init_msg_list,
504 '_PUB',
505 x_return_status);
506 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
507 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
508 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
509 raise OKC_API.G_EXCEPTION_ERROR;
510 END IF;
511
512 g_rulv_rec := p_rulv_rec;
513 /*******************************************************************
514 IF (DBMS_LOB.istemporary(p_rulv_rec.TEXT) = 1) THEN
515 DBMS_LOB.CREATETEMPORARY(g_rulv_rec.TEXT,FALSE,DBMS_LOB.CALL);
516 l_clob := p_rulv_rec.TEXT;
517 DBMS_LOB.OPEN(l_clob, DBMS_LOB.LOB_READONLY);
518 DBMS_LOB.OPEN(g_rulv_rec.TEXT, DBMS_LOB.LOB_READWRITE);
519 DBMS_LOB.COPY(dest_lob => g_rulv_rec.TEXT,
520 src_lob => l_clob,
521 amount => dbms_lob.getlength(l_clob));
522 DBMS_LOB.CLOSE(g_rulv_rec.TEXT);
523 DBMS_LOB.CLOSE(l_clob);
524 DBMS_LOB.freetemporary(l_clob);
525 END IF;
526 *********************************************************************/
527 okc_util.call_user_hook(x_return_status, g_pkg_name, l_api_name, 'B');
528 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
529 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
530 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
531 raise OKC_API.G_EXCEPTION_ERROR;
532 END IF;
533 --Bug 5697488 start by nikshah
534 IF (g_rulv_rec.rule_information_category = 'LABACC') THEN
535 g_rulv_rec.rule_information15 := '12.0';
536 END IF;
537 --Bug 5697488 end by nikshah
538 l_rulv_rec_okc := migrate_rulv(l_rulv_rec, g_rulv_rec);
539
540 OKC_RULE_PUB.update_rule(
541 p_api_version => p_api_version,
542 p_init_msg_list => p_init_msg_list,
543 x_return_status => x_return_status,
544 x_msg_count => x_msg_count,
545 x_msg_data => x_msg_data,
546 p_rulv_rec => l_rulv_rec_okc,
547 x_rulv_rec => l_rulv_rec_okc_out);
548
549 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
550 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
551 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
552 raise OKC_API.G_EXCEPTION_ERROR;
553 END IF;
554
555 x_rulv_rec := migrate_rulv_out(l_rulv_rec_okc_out);
556
557 g_rulv_rec := x_rulv_rec;
558 okc_util.call_user_hook(x_return_status, g_pkg_name, l_api_name, 'A');
559 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
560 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
561 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
562 raise OKC_API.G_EXCEPTION_ERROR;
563 END IF;
564
565 --Bug# 3388812 : cascade status based on p_edit_mode parameter
566 If p_edit_mode = 'Y' then
567 --code to flip status to 'INCOMPLETE' for lease contract if this is an edit point
568 -- edit points for lease contract are any modifications between statuses
569 -- 'PASSED' and 'APPROVED'
570 If (x_rulv_rec.dnz_chr_id is not NULL) AND (x_rulv_rec.dnz_chr_id <> OKL_API.G_MISS_NUM) Then
571
572 okl_contract_status_pub.cascade_lease_status_edit
573 (p_api_version => p_api_version,
574 p_init_msg_list => p_init_msg_list,
575 x_return_status => x_return_status,
576 x_msg_count => x_msg_count,
577 x_msg_data => x_msg_data,
578 p_chr_id => x_rulv_rec.dnz_chr_id);
579
580 If x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR Then
581 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
582 Elsif x_return_status = OKC_API.G_RET_STS_ERROR Then
583 raise OKC_API.G_EXCEPTION_ERROR;
584 End If;
585
586 End If;
587
588 -- Start 14-Sep-2005 Manu
589 --Flip status of the PROGRAM agreement from 'PASSED' to 'INCOMPLETE'
590 If (x_rulv_rec.dnz_chr_id is not NULL) AND (x_rulv_rec.dnz_chr_id <> OKL_API.G_MISS_NUM) Then
591 OPEN lc_category_csr (p_chr_id => x_rulv_rec.dnz_chr_id);
592 FETCH lc_category_csr INTO l_scs_code;
593 CLOSE lc_category_csr;
594 IF (l_scs_code = 'PROGRAM' ) THEN
595 OKL_VENDOR_PROGRAM_PVT.passed_to_incomplete(
596 p_api_version => p_api_version
597 ,p_init_msg_list => p_init_msg_list
598 ,x_return_status => x_return_status
599 ,x_msg_count => x_msg_count
600 ,x_msg_data => x_msg_data
601 ,p_program_id => x_rulv_rec.dnz_chr_id
602 );
603
604 If x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR Then
605 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
606 Elsif x_return_status = OKC_API.G_RET_STS_ERROR Then
607 raise OKC_API.G_EXCEPTION_ERROR;
608 End If;
609 END IF;
610 End If;
611 -- End 14-Sep-2005 Manu
612 End If;
613
614 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
615 EXCEPTION
616 WHEN OKC_API.G_EXCEPTION_ERROR THEN
617 x_return_status := OKC_API.HANDLE_EXCEPTIONS
618 (l_api_name
619 ,G_PKG_NAME
620 ,'OKC_API.G_RET_STS_ERROR'
621 ,x_msg_count
622 ,x_msg_data
623 ,'_PUB');
624 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
625 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
626 (l_api_name
627 ,G_PKG_NAME
628 ,'OKC_API.G_RET_STS_UNEXP_ERROR'
629 ,x_msg_count
630 ,x_msg_data
631 ,'_PUB');
632 WHEN OTHERS THEN
633 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
634 (l_api_name
635 ,G_PKG_NAME
636 ,'OTHERS'
637 ,x_msg_count
638 ,x_msg_data
639 ,'_PUB');
640 END update_rule;
641
642 --------------------------------------
643 -- PROCEDURE update_rule
644 --------------------------------------
645 PROCEDURE update_rule(
646 p_api_version IN NUMBER,
647 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
648 x_return_status OUT NOCOPY VARCHAR2,
649 x_msg_count OUT NOCOPY NUMBER,
650 x_msg_data OUT NOCOPY VARCHAR2,
651 p_rulv_rec IN rulv_rec_type,
652 x_rulv_rec OUT NOCOPY rulv_rec_type) IS
653
654 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
655 l_api_name CONSTANT VARCHAR2(30) := 'update_rule';
656 l_rulv_rec rulv_rec_type := p_rulv_rec;
657 l_rulv_rec_okc okc_rulv_rec_type;
658 l_rulv_rec_okc_out okc_rulv_rec_type;
659 l_clob clob;
660
661 BEGIN
662 x_return_status := OKL_API.G_RET_STS_SUCCESS;
663 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
664 p_init_msg_list,
665 '_PUB',
666 x_return_status);
667 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
668 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
669 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
670 raise OKC_API.G_EXCEPTION_ERROR;
671 END IF;
672
673 update_rule(
674 p_api_version => p_api_version,
675 p_init_msg_list => p_init_msg_list,
676 x_return_status => x_return_status,
677 x_msg_count => x_msg_count,
678 x_msg_data => x_msg_data,
679 p_rulv_rec => l_rulv_rec,
680 p_edit_mode => 'Y',
681 x_rulv_rec => x_rulv_rec);
682
683 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
684 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
685 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
686 raise OKC_API.G_EXCEPTION_ERROR;
687 END IF;
688
689
690 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
691 EXCEPTION
692 WHEN OKC_API.G_EXCEPTION_ERROR THEN
693 x_return_status := OKC_API.HANDLE_EXCEPTIONS
694 (l_api_name
695 ,G_PKG_NAME
696 ,'OKC_API.G_RET_STS_ERROR'
697 ,x_msg_count
698 ,x_msg_data
699 ,'_PUB');
700 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
701 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
702 (l_api_name
703 ,G_PKG_NAME
704 ,'OKC_API.G_RET_STS_UNEXP_ERROR'
705 ,x_msg_count
706 ,x_msg_data
707 ,'_PUB');
708 WHEN OTHERS THEN
709 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
710 (l_api_name
711 ,G_PKG_NAME
712 ,'OTHERS'
713 ,x_msg_count
714 ,x_msg_data
715 ,'_PUB');
716 END update_rule;
717
718
719 --Bug# 3388812 : overloaded procedure with p_edit_mode parameter
720 --------------------------------------
721 -- PROCEDURE update_rule
722 --------------------------------------
723 PROCEDURE update_rule(
724 p_api_version IN NUMBER,
725 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
726 x_return_status OUT NOCOPY VARCHAR2,
727 x_msg_count OUT NOCOPY NUMBER,
728 x_msg_data OUT NOCOPY VARCHAR2,
729 p_rulv_tbl IN rulv_tbl_type,
730 p_edit_mode IN VARCHAR2,
731 x_rulv_tbl OUT NOCOPY rulv_tbl_type) IS
732 i NUMBER := 0;
733 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
734 BEGIN
735 x_return_status := OKC_API.G_RET_STS_SUCCESS;
736 IF p_rulv_tbl.COUNT > 0 THEN
737 i := p_rulv_tbl.FIRST;
738 LOOP
739 update_rule(
740 p_api_version => p_api_version,
741 p_init_msg_list => p_init_msg_list,
742 x_return_status => l_return_status,
743 x_msg_count => x_msg_count,
744 x_msg_data => x_msg_data,
745 p_rulv_rec => p_rulv_tbl(i),
746 p_edit_mode => p_edit_mode,
747 x_rulv_rec => x_rulv_tbl(i));
748 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
749 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
750 x_return_status := l_return_status;
751 raise G_EXCEPTION_HALT_VALIDATION;
752 ELSE
753 x_return_status := l_return_status;
754 END IF;
755 END IF;
756 EXIT WHEN (i = p_rulv_tbl.LAST);
757 i := p_rulv_tbl.NEXT(i);
758 END LOOP;
759 END IF;
760 EXCEPTION
761 WHEN G_EXCEPTION_HALT_VALIDATION THEN
762 NULL;
763 WHEN OTHERS THEN
764 -- store SQL error message on message stack
765 OKC_API.SET_MESSAGE(
766 p_app_name => G_APP_NAME,
767 p_msg_name => G_UNEXPECTED_ERROR,
768 p_token1 => G_SQLCODE_TOKEN,
769 p_token1_value => SQLCODE,
770 p_token2 => G_SQLERRM_TOKEN,
771 p_token2_value => SQLERRM);
772 -- notify caller of an error as UNEXPETED error
773 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
774 END update_rule;
775
776 --------------------------------------
777 -- PROCEDURE update_rule
778 --------------------------------------
779 PROCEDURE update_rule(
780 p_api_version IN NUMBER,
781 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
782 x_return_status OUT NOCOPY VARCHAR2,
783 x_msg_count OUT NOCOPY NUMBER,
784 x_msg_data OUT NOCOPY VARCHAR2,
785 p_rulv_tbl IN rulv_tbl_type,
786 x_rulv_tbl OUT NOCOPY rulv_tbl_type) IS
787 i NUMBER := 0;
788 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
789 BEGIN
790 x_return_status := OKC_API.G_RET_STS_SUCCESS;
791 IF p_rulv_tbl.COUNT > 0 THEN
792 i := p_rulv_tbl.FIRST;
793 LOOP
794 update_rule(
795 p_api_version => p_api_version,
796 p_init_msg_list => p_init_msg_list,
797 x_return_status => l_return_status,
798 x_msg_count => x_msg_count,
799 x_msg_data => x_msg_data,
800 p_rulv_rec => p_rulv_tbl(i),
801 x_rulv_rec => x_rulv_tbl(i));
802 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
803 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
804 x_return_status := l_return_status;
805 raise G_EXCEPTION_HALT_VALIDATION;
806 ELSE
807 x_return_status := l_return_status;
808 END IF;
809 END IF;
810 EXIT WHEN (i = p_rulv_tbl.LAST);
811 i := p_rulv_tbl.NEXT(i);
812 END LOOP;
813 END IF;
814 EXCEPTION
815 WHEN G_EXCEPTION_HALT_VALIDATION THEN
816 NULL;
817 WHEN OTHERS THEN
818 -- store SQL error message on message stack
819 OKC_API.SET_MESSAGE(
820 p_app_name => G_APP_NAME,
821 p_msg_name => G_UNEXPECTED_ERROR,
822 p_token1 => G_SQLCODE_TOKEN,
823 p_token1_value => SQLCODE,
824 p_token2 => G_SQLERRM_TOKEN,
825 p_token2_value => SQLERRM);
826 -- notify caller of an error as UNEXPETED error
827 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
828 END update_rule;
829
830 --------------------------------------
831 -- PROCEDURE validate_rule
832 --------------------------------------
833 PROCEDURE validate_rule(
834 p_api_version IN NUMBER,
835 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
836 x_return_status OUT NOCOPY VARCHAR2,
837 x_msg_count OUT NOCOPY NUMBER,
838 x_msg_data OUT NOCOPY VARCHAR2,
839 p_rulv_rec IN rulv_rec_type) IS
840 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
841 l_api_name CONSTANT VARCHAR2(30) := 'validate_rule';
842 l_rulv_rec rulv_rec_type := p_rulv_rec;
843 l_rulv_rec_okc okc_rulv_rec_type;
844 l_rulv_rec_okc_out okc_rulv_rec_type;
845 BEGIN
846 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
847 p_init_msg_list,
848 '_PUB',
849 x_return_status);
850 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
851 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
852 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
853 raise OKC_API.G_EXCEPTION_ERROR;
854 END IF;
855 g_rulv_rec := p_rulv_rec;
856 okc_util.call_user_hook(x_return_status, g_pkg_name, l_api_name, 'B');
857 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
858 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
859 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
860 raise OKC_API.G_EXCEPTION_ERROR;
861 END IF;
862 l_rulv_rec_okc := migrate_rulv(l_rulv_rec, g_rulv_rec);
863
864 OKC_RULE_PUB.validate_rule(
865 p_api_version => p_api_version,
866 p_init_msg_list => p_init_msg_list,
867 x_return_status => x_return_status,
868 x_msg_count => x_msg_count,
869 x_msg_data => x_msg_data,
870 p_rulv_rec => l_rulv_rec_okc);
871
872 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
873 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
874 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
875 raise OKC_API.G_EXCEPTION_ERROR;
876 END IF;
877
878 g_rulv_rec := l_rulv_rec;
879 okc_util.call_user_hook(x_return_status, g_pkg_name, l_api_name, 'A');
880 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
881 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
882 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
883 raise OKC_API.G_EXCEPTION_ERROR;
884 END IF;
885 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
886 EXCEPTION
887 WHEN OKC_API.G_EXCEPTION_ERROR THEN
888 x_return_status := OKC_API.HANDLE_EXCEPTIONS
889 (l_api_name
890 ,G_PKG_NAME
891 ,'OKC_API.G_RET_STS_ERROR'
892 ,x_msg_count
893 ,x_msg_data
894 ,'_PUB');
895 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
896 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
897 (l_api_name
898 ,G_PKG_NAME
899 ,'OKC_API.G_RET_STS_UNEXP_ERROR'
900 ,x_msg_count
901 ,x_msg_data
902 ,'_PUB');
903 WHEN OTHERS THEN
904 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
905 (l_api_name
906 ,G_PKG_NAME
907 ,'OTHERS'
908 ,x_msg_count
909 ,x_msg_data
910 ,'_PUB');
911 END validate_rule;
912
913 --------------------------------------
914 -- PROCEDURE validate_rule
915 --------------------------------------
916 PROCEDURE validate_rule(
917 p_api_version IN NUMBER,
918 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
919 x_return_status OUT NOCOPY VARCHAR2,
920 x_msg_count OUT NOCOPY NUMBER,
921 x_msg_data OUT NOCOPY VARCHAR2,
922 p_rulv_tbl IN rulv_tbl_type) IS
923 i NUMBER := 0;
924 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
925 BEGIN
926 x_return_status := OKC_API.G_RET_STS_SUCCESS;
927 IF p_rulv_tbl.COUNT > 0 THEN
928 i := p_rulv_tbl.FIRST;
929 LOOP
930 validate_rule(
931 p_api_version => p_api_version,
932 p_init_msg_list => p_init_msg_list,
933 x_return_status => l_return_status,
934 x_msg_count => x_msg_count,
935 x_msg_data => x_msg_data,
936 p_rulv_rec => p_rulv_tbl(i));
937 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
938 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
939 x_return_status := l_return_status;
940 raise G_EXCEPTION_HALT_VALIDATION;
941 ELSE
942 x_return_status := l_return_status;
943 END IF;
944 END IF;
945 EXIT WHEN (i = p_rulv_tbl.LAST);
946 i := p_rulv_tbl.NEXT(i);
947 END LOOP;
948 END IF;
949 EXCEPTION
950 WHEN G_EXCEPTION_HALT_VALIDATION THEN
951 NULL;
952 WHEN OTHERS THEN
953 -- store SQL error message on message stack
954 OKC_API.SET_MESSAGE(
955 p_app_name => G_APP_NAME,
956 p_msg_name => G_UNEXPECTED_ERROR,
957 p_token1 => G_SQLCODE_TOKEN,
958 p_token1_value => SQLCODE,
959 p_token2 => G_SQLERRM_TOKEN,
960 p_token2_value => SQLERRM);
961 -- notify caller of an error as UNEXPETED error
962 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
963 END validate_rule;
964
965 --------------------------------------
966 -- PROCEDURE delete_rule
967 --------------------------------------
968 PROCEDURE delete_rule(
969 p_api_version IN NUMBER,
970 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
971 x_return_status OUT NOCOPY VARCHAR2,
972 x_msg_count OUT NOCOPY NUMBER,
973 x_msg_data OUT NOCOPY VARCHAR2,
974 p_rulv_rec IN rulv_rec_type) IS
975 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
976 l_api_name CONSTANT VARCHAR2(30) := 'delete_rule';
977 l_rulv_rec rulv_rec_type := p_rulv_rec;
978 l_rulv_rec_okc okc_rulv_rec_type;
979 l_rulv_rec_okc_out okc_rulv_rec_type;
980
981 --cursor to find out chr id required to flip status at edit point
982 CURSOR chr_id_crs (p_rul_id IN NUMBER) is
983 SELECT DNZ_CHR_ID
984 FROM OKC_RULES_B
985 WHERE ID = P_RUL_ID;
986
987 l_dnz_chr_id OKC_RULES_B.dnz_chr_id%TYPE;
988
989 -- Start 14-Sep-2005 Manu
990 CURSOR lc_category_csr (p_chr_id IN OKC_K_HEADERS_V.ID%TYPE) IS
991 SELECT scs_code
992 FROM OKC_K_HEADERS_V
993 WHERE id = p_chr_id;
994 l_scs_code OKC_K_HEADERS_V.SCS_CODE%TYPE;
995 -- End 14-Sep-2005 Manu
996
997 BEGIN
998 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
999 p_init_msg_list,
1000 '_PUB',
1001 x_return_status);
1002 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1003 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1004 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1005 raise OKC_API.G_EXCEPTION_ERROR;
1006 END IF;
1007 g_rulv_rec := p_rulv_rec;
1008 okc_util.call_user_hook(x_return_status, g_pkg_name, l_api_name, 'B');
1009 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1010 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1011 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
1012 raise OKC_API.G_EXCEPTION_ERROR;
1013 END IF;
1014
1015 --code to flip status to 'INCOMPLETE' for lease contract if this is an edit point
1016 -- edit points for lease contract are any modifications between statuses
1017 -- 'PASSED' and 'APPROVED'
1018 If (p_rulv_rec.dnz_chr_id is NULL OR p_rulv_rec.dnz_chr_id = OKL_API.G_MISS_NUM) THEN
1019 Open chr_id_crs(p_rul_id => p_rulv_rec.id);
1020 Fetch chr_id_crs into l_dnz_chr_id;
1021 If chr_id_crs%NOTFOUND THEN
1022 null;
1023 End If;
1024 Close chr_id_crs;
1025 Else
1026 l_dnz_chr_id := p_rulv_rec.dnz_chr_id;
1027 End If;
1028
1029 If (l_dnz_chr_id is not NULL AND l_dnz_chr_id <> OKL_API.G_MISS_NUM) Then
1030
1031 okl_contract_status_pub.cascade_lease_status_edit
1032 (p_api_version => p_api_version,
1033 p_init_msg_list => p_init_msg_list,
1034 x_return_status => x_return_status,
1035 x_msg_count => x_msg_count,
1036 x_msg_data => x_msg_data,
1037 p_chr_id => l_dnz_chr_id);
1038
1039 If x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR Then
1040 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1041 Elsif x_return_status = OKC_API.G_RET_STS_ERROR Then
1042 raise OKC_API.G_EXCEPTION_ERROR;
1043 End If;
1044
1045 End If;
1046
1047 -- Start 14-Sep-2005 Manu
1048 --Flip status of the PROGRAM agreement from 'PASSED' to 'INCOMPLETE'
1049 If (l_dnz_chr_id is not NULL) AND (l_dnz_chr_id <> OKL_API.G_MISS_NUM) Then
1050 OPEN lc_category_csr (p_chr_id => l_dnz_chr_id);
1051 FETCH lc_category_csr INTO l_scs_code;
1052 CLOSE lc_category_csr;
1053 IF (l_scs_code = 'PROGRAM' ) THEN
1054 OKL_VENDOR_PROGRAM_PVT.passed_to_incomplete(
1055 p_api_version => p_api_version
1056 ,p_init_msg_list => p_init_msg_list
1057 ,x_return_status => x_return_status
1058 ,x_msg_count => x_msg_count
1059 ,x_msg_data => x_msg_data
1060 ,p_program_id => l_dnz_chr_id
1061 );
1062
1063 If x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR Then
1064 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1065 Elsif x_return_status = OKC_API.G_RET_STS_ERROR Then
1066 raise OKC_API.G_EXCEPTION_ERROR;
1067 End If;
1068 END IF;
1069 End If;
1070 -- End 14-Sep-2005 Manu
1071
1072 l_rulv_rec_okc := migrate_rulv(l_rulv_rec, g_rulv_rec);
1073 OKC_RULE_PUB.delete_rule(
1074 p_api_version => p_api_version,
1075 p_init_msg_list => p_init_msg_list,
1076 x_return_status => x_return_status,
1077 x_msg_count => x_msg_count,
1078 x_msg_data => x_msg_data,
1079 p_rulv_rec => l_rulv_rec_okc);
1080
1081 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1082 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1083 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
1084 raise OKC_API.G_EXCEPTION_ERROR;
1085 END IF;
1086
1087 g_rulv_rec := l_rulv_rec;
1088 okc_util.call_user_hook(x_return_status, g_pkg_name, l_api_name, 'A');
1089 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1090 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1091 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
1092 raise OKC_API.G_EXCEPTION_ERROR;
1093 END IF;
1094 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1095 EXCEPTION
1096 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1097 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1098 (l_api_name
1099 ,G_PKG_NAME
1100 ,'OKC_API.G_RET_STS_ERROR'
1101 ,x_msg_count
1102 ,x_msg_data
1103 ,'_PUB');
1104 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1105 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1106 (l_api_name
1107 ,G_PKG_NAME
1108 ,'OKC_API.G_RET_STS_UNEXP_ERROR'
1109 ,x_msg_count
1110 ,x_msg_data
1111 ,'_PUB');
1112 WHEN OTHERS THEN
1113 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1114 (l_api_name
1115 ,G_PKG_NAME
1116 ,'OTHERS'
1117 ,x_msg_count
1118 ,x_msg_data
1119 ,'_PUB');
1120 END delete_rule;
1121
1122 --------------------------------------
1123 -- PROCEDURE delete_rule
1124 --------------------------------------
1125 PROCEDURE delete_rule(
1126 p_api_version IN NUMBER,
1127 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1128 x_return_status OUT NOCOPY VARCHAR2,
1129 x_msg_count OUT NOCOPY NUMBER,
1130 x_msg_data OUT NOCOPY VARCHAR2,
1131 p_rulv_tbl IN rulv_tbl_type) IS
1132 i NUMBER := 0;
1133 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1134 BEGIN
1135 x_return_status := OKC_API.G_RET_STS_SUCCESS;
1136 IF p_rulv_tbl.COUNT > 0 THEN
1137 i := p_rulv_tbl.FIRST;
1138 LOOP
1139 delete_rule(
1140 p_api_version => p_api_version,
1141 p_init_msg_list => p_init_msg_list,
1142 x_return_status => l_return_status,
1143 x_msg_count => x_msg_count,
1144 x_msg_data => x_msg_data,
1145 p_rulv_rec => p_rulv_tbl(i));
1146 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
1147 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1148 x_return_status := l_return_status;
1149 raise G_EXCEPTION_HALT_VALIDATION;
1150 ELSE
1151 x_return_status := l_return_status;
1152 END IF;
1153 END IF;
1154 EXIT WHEN (i = p_rulv_tbl.LAST);
1155 i := p_rulv_tbl.NEXT(i);
1156 END LOOP;
1157 END IF;
1158 EXCEPTION
1159 WHEN G_EXCEPTION_HALT_VALIDATION THEN
1160 NULL;
1161 WHEN OTHERS THEN
1162 -- store SQL error message on message stack
1163 OKC_API.SET_MESSAGE(
1164 p_app_name => G_APP_NAME,
1165 p_msg_name => G_UNEXPECTED_ERROR,
1166 p_token1 => G_SQLCODE_TOKEN,
1167 p_token1_value => SQLCODE,
1168 p_token2 => G_SQLERRM_TOKEN,
1169 p_token2_value => SQLERRM);
1170 -- notify caller of an error as UNEXPETED error
1171 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1172 END delete_rule;
1173
1174 --------------------------------------
1175 -- PROCEDURE lock_rule
1176 --------------------------------------
1177 PROCEDURE lock_rule(
1178 p_api_version IN NUMBER,
1179 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1180 x_return_status OUT NOCOPY VARCHAR2,
1181 x_msg_count OUT NOCOPY NUMBER,
1182 x_msg_data OUT NOCOPY VARCHAR2,
1183 p_rulv_rec IN rulv_rec_type) IS
1184 l_rulv_rec rulv_rec_type := p_rulv_rec;
1185 l_rulv_rec_okc okc_rulv_rec_type;
1186 l_rulv_rec_okc_out okc_rulv_rec_type;
1187
1188 BEGIN
1189 g_rulv_rec := l_rulv_rec;
1190 l_rulv_rec_okc := migrate_rulv(l_rulv_rec, g_rulv_rec);
1191
1192 OKC_RULE_PUB.lock_rule(
1193 p_api_version => p_api_version,
1194 p_init_msg_list => p_init_msg_list,
1195 x_return_status => x_return_status,
1196 x_msg_count => x_msg_count,
1197 x_msg_data => x_msg_data,
1198 p_rulv_rec => l_rulv_rec_okc);
1199 END lock_rule;
1200
1201 --------------------------------------
1202 -- PROCEDURE lock_rule
1203 --------------------------------------
1204 PROCEDURE lock_rule(
1205 p_api_version IN NUMBER,
1206 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1207 x_return_status OUT NOCOPY VARCHAR2,
1208 x_msg_count OUT NOCOPY NUMBER,
1209 x_msg_data OUT NOCOPY VARCHAR2,
1210 p_rulv_tbl IN rulv_tbl_type) IS
1211 i NUMBER := 0;
1212 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1213 BEGIN
1214 x_return_status := OKC_API.G_RET_STS_SUCCESS;
1215 IF p_rulv_tbl.COUNT > 0 THEN
1216 i := p_rulv_tbl.FIRST;
1217 LOOP
1218 lock_rule(
1219 p_api_version => p_api_version,
1220 p_init_msg_list => p_init_msg_list,
1221 x_return_status => l_return_status,
1222 x_msg_count => x_msg_count,
1223 x_msg_data => x_msg_data,
1224 p_rulv_rec => p_rulv_tbl(i));
1225 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
1226 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1227 x_return_status := l_return_status;
1228 raise G_EXCEPTION_HALT_VALIDATION;
1229 ELSE
1230 x_return_status := l_return_status;
1231 END IF;
1232 END IF;
1233 EXIT WHEN (i = p_rulv_tbl.LAST);
1234 i := p_rulv_tbl.NEXT(i);
1235 END LOOP;
1236 END IF;
1237 EXCEPTION
1238 WHEN G_EXCEPTION_HALT_VALIDATION THEN
1239 NULL;
1240 WHEN OTHERS THEN
1241 -- store SQL error message on message stack
1242 OKC_API.SET_MESSAGE(
1243 p_app_name => G_APP_NAME,
1244 p_msg_name => G_UNEXPECTED_ERROR,
1245 p_token1 => G_SQLCODE_TOKEN,
1246 p_token1_value => SQLCODE,
1247 p_token2 => G_SQLERRM_TOKEN,
1248 p_token2_value => SQLERRM);
1249 -- notify caller of an error as UNEXPETED error
1250 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1251 END lock_rule;
1252
1253 ---------------------------------------------------------------------------
1254 -- FUNCTION migrate_rgpv
1255 ---------------------------------------------------------------------------
1256 FUNCTION migrate_rgpv (
1257 p_rgpv_rec1 IN rgpv_rec_type,
1258 p_rgpv_rec2 IN rgpv_rec_type
1259 ) RETURN rgpv_rec_type IS
1260 l_rgpv_rec rgpv_rec_type;
1261 BEGIN
1262 l_rgpv_rec.id := p_rgpv_rec1.id;
1263 l_rgpv_rec.object_version_number := p_rgpv_rec1.object_version_number;
1264 l_rgpv_rec.created_by := p_rgpv_rec1.created_by;
1265 l_rgpv_rec.creation_date := p_rgpv_rec1.creation_date;
1266 l_rgpv_rec.last_updated_by := p_rgpv_rec1.last_updated_by;
1267 l_rgpv_rec.last_update_date := p_rgpv_rec1.last_update_date;
1268 l_rgpv_rec.last_update_login := p_rgpv_rec1.last_update_login;
1269 l_rgpv_rec.rgd_code := p_rgpv_rec2.rgd_code;
1270 l_rgpv_rec.sat_code := p_rgpv_rec2.sat_code;
1271 l_rgpv_rec.rgp_type := p_rgpv_rec2.rgp_type;
1272 l_rgpv_rec.cle_id := p_rgpv_rec2.cle_id;
1273 l_rgpv_rec.chr_id := p_rgpv_rec2.chr_id;
1274 l_rgpv_rec.dnz_chr_id := p_rgpv_rec2.dnz_chr_id;
1275 l_rgpv_rec.parent_rgp_id := p_rgpv_rec2.parent_rgp_id;
1276 l_rgpv_rec.sfwt_flag := p_rgpv_rec2.sfwt_flag;
1277 l_rgpv_rec.comments := p_rgpv_rec2.comments;
1278 l_rgpv_rec.attribute_category := p_rgpv_rec2.attribute_category;
1279 l_rgpv_rec.attribute1 := p_rgpv_rec2.attribute1;
1280 l_rgpv_rec.attribute2 := p_rgpv_rec2.attribute2;
1281 l_rgpv_rec.attribute3 := p_rgpv_rec2.attribute3;
1282 l_rgpv_rec.attribute4 := p_rgpv_rec2.attribute4;
1283 l_rgpv_rec.attribute5 := p_rgpv_rec2.attribute5;
1284 l_rgpv_rec.attribute6 := p_rgpv_rec2.attribute6;
1285 l_rgpv_rec.attribute7 := p_rgpv_rec2.attribute7;
1286 l_rgpv_rec.attribute8 := p_rgpv_rec2.attribute8;
1287 l_rgpv_rec.attribute9 := p_rgpv_rec2.attribute9;
1288 l_rgpv_rec.attribute10 := p_rgpv_rec2.attribute10;
1289 l_rgpv_rec.attribute11 := p_rgpv_rec2.attribute11;
1290 l_rgpv_rec.attribute12 := p_rgpv_rec2.attribute12;
1291 l_rgpv_rec.attribute13 := p_rgpv_rec2.attribute13;
1292 l_rgpv_rec.attribute14 := p_rgpv_rec2.attribute14;
1293 l_rgpv_rec.attribute15 := p_rgpv_rec2.attribute15;
1294
1295 RETURN (l_rgpv_rec);
1296 END migrate_rgpv;
1297
1298 --------------------------------------
1299 -- PROCEDURE create_rule_group
1300 --------------------------------------
1301 PROCEDURE create_rule_group(
1302 p_api_version IN NUMBER,
1303 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1304 x_return_status OUT NOCOPY VARCHAR2,
1305 x_msg_count OUT NOCOPY NUMBER,
1306 x_msg_data OUT NOCOPY VARCHAR2,
1307 p_rgpv_rec IN rgpv_rec_type,
1308 x_rgpv_rec OUT NOCOPY rgpv_rec_type) IS
1309 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1310 l_api_name CONSTANT VARCHAR2(30) := 'create_rule_group';
1311 l_rgpv_rec rgpv_rec_type := p_rgpv_rec;
1312 BEGIN
1313 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1314 p_init_msg_list,
1315 '_PUB',
1316 x_return_status);
1317 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1318 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1319 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1320 raise OKC_API.G_EXCEPTION_ERROR;
1321 END IF;
1322 g_rgpv_rec := p_rgpv_rec;
1323
1324 okc_util.call_user_hook(x_return_status, g_pkg_name, l_api_name, 'B');
1325 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1326 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1327 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
1328 raise OKC_API.G_EXCEPTION_ERROR;
1329 END IF;
1330 l_rgpv_rec := migrate_rgpv(l_rgpv_rec, g_rgpv_rec);
1331
1332 okl_okc_migration_pvt.create_rule_group(
1333 p_api_version => p_api_version,
1334 p_init_msg_list => p_init_msg_list,
1335 x_return_status => x_return_status,
1336 x_msg_count => x_msg_count,
1337 x_msg_data => x_msg_data,
1338 p_rgpv_rec => l_rgpv_rec,
1339 x_rgpv_rec => x_rgpv_rec);
1340
1341 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1342 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1343 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
1344 raise OKC_API.G_EXCEPTION_ERROR;
1345 END IF;
1346
1347 g_rgpv_rec := x_rgpv_rec;
1348 okc_util.call_user_hook(x_return_status, g_pkg_name, l_api_name, 'A');
1349 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1350 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1351 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
1352 raise OKC_API.G_EXCEPTION_ERROR;
1353 END IF;
1354 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1355 EXCEPTION
1356 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1357 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1358 (l_api_name
1359 ,G_PKG_NAME
1360 ,'OKC_API.G_RET_STS_ERROR'
1361 ,x_msg_count
1362 ,x_msg_data
1363 ,'_PUB');
1364 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1365 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1366 (l_api_name
1367 ,G_PKG_NAME
1368 ,'OKC_API.G_RET_STS_UNEXP_ERROR'
1369 ,x_msg_count
1370 ,x_msg_data
1371 ,'_PUB');
1372 WHEN OTHERS THEN
1373 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1374 (l_api_name
1375 ,G_PKG_NAME
1376 ,'OTHERS'
1377 ,x_msg_count
1378 ,x_msg_data
1379 ,'_PUB');
1380 END create_rule_group;
1381
1382 --------------------------------------
1383 -- PROCEDURE create_rule_group
1384 --------------------------------------
1385 PROCEDURE create_rule_group(
1386 p_api_version IN NUMBER,
1387 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1388 x_return_status OUT NOCOPY VARCHAR2,
1389 x_msg_count OUT NOCOPY NUMBER,
1390 x_msg_data OUT NOCOPY VARCHAR2,
1391 p_rgpv_tbl IN rgpv_tbl_type,
1392 x_rgpv_tbl OUT NOCOPY rgpv_tbl_type) IS
1393 i NUMBER := 0;
1394 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1395 BEGIN
1396 x_return_status := OKC_API.G_RET_STS_SUCCESS;
1397 IF p_rgpv_tbl.COUNT > 0 THEN
1398 i := p_rgpv_tbl.FIRST;
1399 LOOP
1400 create_rule_group(
1401 p_api_version => p_api_version,
1402 p_init_msg_list => p_init_msg_list,
1403 x_return_status => l_return_status,
1404 x_msg_count => x_msg_count,
1405 x_msg_data => x_msg_data,
1406 p_rgpv_rec => p_rgpv_tbl(i),
1407 x_rgpv_rec => x_rgpv_tbl(i));
1408 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
1409 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1410 x_return_status := l_return_status;
1411 raise G_EXCEPTION_HALT_VALIDATION;
1412 ELSE
1413 x_return_status := l_return_status;
1414 END IF;
1415 END IF;
1416 EXIT WHEN (i = p_rgpv_tbl.LAST);
1417 i := p_rgpv_tbl.NEXT(i);
1418 END LOOP;
1419 END IF;
1420 EXCEPTION
1421 WHEN G_EXCEPTION_HALT_VALIDATION THEN
1422 NULL;
1423 WHEN OTHERS THEN
1424 -- store SQL error message on message stack
1425 OKC_API.SET_MESSAGE(
1426 p_app_name => G_APP_NAME,
1427 p_msg_name => G_UNEXPECTED_ERROR,
1428 p_token1 => G_SQLCODE_TOKEN,
1429 p_token1_value => SQLCODE,
1430 p_token2 => G_SQLERRM_TOKEN,
1431 p_token2_value => SQLERRM);
1432 -- notify caller of an error as UNEXPETED error
1433 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1434 END create_rule_group;
1435
1436 --------------------------------------
1437 -- PROCEDURE update_rule_group
1438 --------------------------------------
1439 PROCEDURE update_rule_group(
1440 p_api_version IN NUMBER,
1441 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1442 x_return_status OUT NOCOPY VARCHAR2,
1443 x_msg_count OUT NOCOPY NUMBER,
1444 x_msg_data OUT NOCOPY VARCHAR2,
1445 p_rgpv_rec IN rgpv_rec_type,
1446 x_rgpv_rec OUT NOCOPY rgpv_rec_type) IS
1447 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1448 l_api_name CONSTANT VARCHAR2(30) := 'update_rule_group';
1449 l_rgpv_rec rgpv_rec_type := p_rgpv_rec;
1450 BEGIN
1451 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1452 p_init_msg_list,
1453 '_PUB',
1454 x_return_status);
1455 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1456 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1457 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1458 raise OKC_API.G_EXCEPTION_ERROR;
1459 END IF;
1460 g_rgpv_rec := p_rgpv_rec;
1461 okc_util.call_user_hook(x_return_status, g_pkg_name, l_api_name, 'B');
1462 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1463 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1464 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
1465 raise OKC_API.G_EXCEPTION_ERROR;
1466 END IF;
1467 l_rgpv_rec := migrate_rgpv(l_rgpv_rec, g_rgpv_rec);
1468
1469 okl_okc_migration_pvt.update_rule_group(
1470 p_api_version => p_api_version,
1471 p_init_msg_list => p_init_msg_list,
1472 x_return_status => x_return_status,
1473 x_msg_count => x_msg_count,
1474 x_msg_data => x_msg_data,
1475 p_rgpv_rec => l_rgpv_rec,
1476 x_rgpv_rec => x_rgpv_rec);
1477
1478 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1479 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1480 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
1481 raise OKC_API.G_EXCEPTION_ERROR;
1482 END IF;
1483
1484 g_rgpv_rec := x_rgpv_rec;
1485 okc_util.call_user_hook(x_return_status, g_pkg_name, l_api_name, 'A');
1486 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1487 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1488 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
1489 raise OKC_API.G_EXCEPTION_ERROR;
1490 END IF;
1491 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1492 EXCEPTION
1493 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1494 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1495 (l_api_name
1496 ,G_PKG_NAME
1497 ,'OKC_API.G_RET_STS_ERROR'
1498 ,x_msg_count
1499 ,x_msg_data
1500 ,'_PUB');
1501 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1502 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1503 (l_api_name
1504 ,G_PKG_NAME
1505 ,'OKC_API.G_RET_STS_UNEXP_ERROR'
1506 ,x_msg_count
1507 ,x_msg_data
1508 ,'_PUB');
1509 WHEN OTHERS THEN
1510 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1511 (l_api_name
1512 ,G_PKG_NAME
1513 ,'OTHERS'
1514 ,x_msg_count
1515 ,x_msg_data
1516 ,'_PUB');
1517 END update_rule_group;
1518
1519 --------------------------------------
1520 -- PROCEDURE update_rule_group
1521 --------------------------------------
1522 PROCEDURE update_rule_group(
1523 p_api_version IN NUMBER,
1524 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1525 x_return_status OUT NOCOPY VARCHAR2,
1526 x_msg_count OUT NOCOPY NUMBER,
1527 x_msg_data OUT NOCOPY VARCHAR2,
1528 p_rgpv_tbl IN rgpv_tbl_type,
1529 x_rgpv_tbl OUT NOCOPY rgpv_tbl_type) IS
1530 i NUMBER := 0;
1531 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1532 BEGIN
1533 x_return_status := OKC_API.G_RET_STS_SUCCESS;
1534 IF p_rgpv_tbl.COUNT > 0 THEN
1535 i := p_rgpv_tbl.FIRST;
1536 LOOP
1537 update_rule_group(
1538 p_api_version => p_api_version,
1539 p_init_msg_list => p_init_msg_list,
1540 x_return_status => l_return_status,
1541 x_msg_count => x_msg_count,
1542 x_msg_data => x_msg_data,
1543 p_rgpv_rec => p_rgpv_tbl(i),
1544 x_rgpv_rec => x_rgpv_tbl(i));
1545 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
1546 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1547 x_return_status := l_return_status;
1548 raise G_EXCEPTION_HALT_VALIDATION;
1549 ELSE
1550 x_return_status := l_return_status;
1551 END IF;
1552 END IF;
1553 EXIT WHEN (i = p_rgpv_tbl.LAST);
1554 i := p_rgpv_tbl.NEXT(i);
1555 END LOOP;
1556 END IF;
1557 EXCEPTION
1558 WHEN G_EXCEPTION_HALT_VALIDATION THEN
1559 NULL;
1560 WHEN OTHERS THEN
1561 -- store SQL error message on message stack
1562 OKC_API.SET_MESSAGE(
1563 p_app_name => G_APP_NAME,
1564 p_msg_name => G_UNEXPECTED_ERROR,
1565 p_token1 => G_SQLCODE_TOKEN,
1566 p_token1_value => SQLCODE,
1567 p_token2 => G_SQLERRM_TOKEN,
1568 p_token2_value => SQLERRM);
1569 -- notify caller of an error as UNEXPETED error
1570 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1571 END update_rule_group;
1572
1573 --------------------------------------
1574 -- PROCEDURE delete_rule_group
1575 --------------------------------------
1576 PROCEDURE delete_rule_group(
1577 p_api_version IN NUMBER,
1578 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1579 x_return_status OUT NOCOPY VARCHAR2,
1580 x_msg_count OUT NOCOPY NUMBER,
1581 x_msg_data OUT NOCOPY VARCHAR2,
1582 p_rgpv_rec IN rgpv_rec_type) IS
1583 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1584 l_api_name CONSTANT VARCHAR2(30) := 'delete_rule_group';
1585 l_rgpv_rec rgpv_rec_type := p_rgpv_rec;
1586 BEGIN
1587 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1588 p_init_msg_list,
1589 '_PUB',
1590 x_return_status);
1591 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1592 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1593 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1594 raise OKC_API.G_EXCEPTION_ERROR;
1595 END IF;
1596 g_rgpv_rec := p_rgpv_rec;
1597 okc_util.call_user_hook(x_return_status, g_pkg_name, l_api_name, 'B');
1598 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1599 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1600 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
1601 raise OKC_API.G_EXCEPTION_ERROR;
1602 END IF;
1603
1604 okl_okc_migration_pvt.delete_rule_group(
1605 p_api_version => p_api_version,
1606 p_init_msg_list => p_init_msg_list,
1607 x_return_status => x_return_status,
1608 x_msg_count => x_msg_count,
1609 x_msg_data => x_msg_data,
1610 p_rgpv_rec => p_rgpv_rec);
1611
1612 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1613 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1614 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
1615 raise OKC_API.G_EXCEPTION_ERROR;
1616 END IF;
1617
1618 g_rgpv_rec := l_rgpv_rec;
1619 okc_util.call_user_hook(x_return_status, g_pkg_name, l_api_name, 'A');
1620 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1621 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1622 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
1623 raise OKC_API.G_EXCEPTION_ERROR;
1624 END IF;
1625 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1626 EXCEPTION
1627 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1628 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1629 (l_api_name
1630 ,G_PKG_NAME
1631 ,'OKC_API.G_RET_STS_ERROR'
1632 ,x_msg_count
1633 ,x_msg_data
1634 ,'_PUB');
1635 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1636 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1637 (l_api_name
1638 ,G_PKG_NAME
1639 ,'OKC_API.G_RET_STS_UNEXP_ERROR'
1640 ,x_msg_count
1641 ,x_msg_data
1642 ,'_PUB');
1643 WHEN OTHERS THEN
1644 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1645 (l_api_name
1646 ,G_PKG_NAME
1647 ,'OTHERS'
1648 ,x_msg_count
1649 ,x_msg_data
1650 ,'_PUB');
1651 END delete_rule_group;
1652
1653 --------------------------------------
1654 -- PROCEDURE delete_rule_group
1655 --------------------------------------
1656 PROCEDURE delete_rule_group(
1657 p_api_version IN NUMBER,
1658 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1659 x_return_status OUT NOCOPY VARCHAR2,
1660 x_msg_count OUT NOCOPY NUMBER,
1661 x_msg_data OUT NOCOPY VARCHAR2,
1662 p_rgpv_tbl IN rgpv_tbl_type) IS
1663 i NUMBER := 0;
1664 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1665 BEGIN
1666 x_return_status := OKC_API.G_RET_STS_SUCCESS;
1667 IF p_rgpv_tbl.COUNT > 0 THEN
1668 i := p_rgpv_tbl.FIRST;
1669 LOOP
1670 delete_rule_group(
1671 p_api_version => p_api_version,
1672 p_init_msg_list => p_init_msg_list,
1673 x_return_status => l_return_status,
1674 x_msg_count => x_msg_count,
1675 x_msg_data => x_msg_data,
1676 p_rgpv_rec => p_rgpv_tbl(i));
1677 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
1678 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1679 x_return_status := l_return_status;
1680 raise G_EXCEPTION_HALT_VALIDATION;
1681 ELSE
1682 x_return_status := l_return_status;
1683 END IF;
1684 END IF;
1685 EXIT WHEN (i = p_rgpv_tbl.LAST);
1686 i := p_rgpv_tbl.NEXT(i);
1687 END LOOP;
1688 END IF;
1689 EXCEPTION
1690 WHEN G_EXCEPTION_HALT_VALIDATION THEN
1691 NULL;
1692 WHEN OTHERS THEN
1693 -- store SQL error message on message stack
1694 OKC_API.SET_MESSAGE(
1695 p_app_name => G_APP_NAME,
1696 p_msg_name => G_UNEXPECTED_ERROR,
1697 p_token1 => G_SQLCODE_TOKEN,
1698 p_token1_value => SQLCODE,
1699 p_token2 => G_SQLERRM_TOKEN,
1700 p_token2_value => SQLERRM);
1701 -- notify caller of an error as UNEXPETED error
1702 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1703 END delete_rule_group;
1704
1705 --------------------------------------
1706 -- PROCEDURE lock_rule_group
1707 --------------------------------------
1708 PROCEDURE lock_rule_group(
1709 p_api_version IN NUMBER,
1710 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1711 x_return_status OUT NOCOPY VARCHAR2,
1712 x_msg_count OUT NOCOPY NUMBER,
1713 x_msg_data OUT NOCOPY VARCHAR2,
1714 p_rgpv_rec IN rgpv_rec_type) IS
1715 BEGIN
1716 okl_okc_migration_pvt.lock_rule_group(
1717 p_api_version => p_api_version,
1718 p_init_msg_list => p_init_msg_list,
1719 x_return_status => x_return_status,
1720 x_msg_count => x_msg_count,
1721 x_msg_data => x_msg_data,
1722 p_rgpv_rec => p_rgpv_rec);
1723 END lock_rule_group;
1724
1725 --------------------------------------
1726 -- PROCEDURE lock_rule_group
1727 --------------------------------------
1728 PROCEDURE lock_rule_group(
1729 p_api_version IN NUMBER,
1730 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1731 x_return_status OUT NOCOPY VARCHAR2,
1732 x_msg_count OUT NOCOPY NUMBER,
1733 x_msg_data OUT NOCOPY VARCHAR2,
1734 p_rgpv_tbl IN rgpv_tbl_type) IS
1735 i NUMBER := 0;
1736 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1737 BEGIN
1738 x_return_status := OKC_API.G_RET_STS_SUCCESS;
1739 IF p_rgpv_tbl.COUNT > 0 THEN
1740 i := p_rgpv_tbl.FIRST;
1741 LOOP
1742 lock_rule_group(
1743 p_api_version => p_api_version,
1744 p_init_msg_list => p_init_msg_list,
1745 x_return_status => l_return_status,
1746 x_msg_count => x_msg_count,
1747 x_msg_data => x_msg_data,
1748 p_rgpv_rec => p_rgpv_tbl(i));
1749 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
1750 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1751 x_return_status := l_return_status;
1752 raise G_EXCEPTION_HALT_VALIDATION;
1753 ELSE
1754 x_return_status := l_return_status;
1755 END IF;
1756 END IF;
1757 EXIT WHEN (i = p_rgpv_tbl.LAST);
1758 i := p_rgpv_tbl.NEXT(i);
1759 END LOOP;
1760 END IF;
1761 EXCEPTION
1762 WHEN G_EXCEPTION_HALT_VALIDATION THEN
1763 NULL;
1764 WHEN OTHERS THEN
1765 -- store SQL error message on message stack
1766 OKC_API.SET_MESSAGE(
1767 p_app_name => G_APP_NAME,
1768 p_msg_name => G_UNEXPECTED_ERROR,
1769 p_token1 => G_SQLCODE_TOKEN,
1770 p_token1_value => SQLCODE,
1771 p_token2 => G_SQLERRM_TOKEN,
1772 p_token2_value => SQLERRM);
1773 -- notify caller of an error as UNEXPETED error
1774 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1775 END lock_rule_group;
1776
1777 --------------------------------------
1778 -- PROCEDURE validate_rule_group
1779 --------------------------------------
1780 PROCEDURE validate_rule_group(
1781 p_api_version IN NUMBER,
1782 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1783 x_return_status OUT NOCOPY VARCHAR2,
1784 x_msg_count OUT NOCOPY NUMBER,
1785 x_msg_data OUT NOCOPY VARCHAR2,
1786 p_rgpv_rec IN rgpv_rec_type) IS
1787 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1788 l_api_name CONSTANT VARCHAR2(30) := 'validate_rule_group';
1789 l_rgpv_rec rgpv_rec_type := p_rgpv_rec;
1790 BEGIN
1791 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1792 p_init_msg_list,
1793 '_PUB',
1794 x_return_status);
1795 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1796 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1797 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1798 raise OKC_API.G_EXCEPTION_ERROR;
1799 END IF;
1800 g_rgpv_rec := p_rgpv_rec;
1801 okc_util.call_user_hook(x_return_status, g_pkg_name, l_api_name, 'B');
1802 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1803 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1804 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
1805 raise OKC_API.G_EXCEPTION_ERROR;
1806 END IF;
1807
1808 okl_okc_migration_pvt.validate_rule_group(
1809 p_api_version => p_api_version,
1810 p_init_msg_list => p_init_msg_list,
1811 x_return_status => x_return_status,
1812 x_msg_count => x_msg_count,
1813 x_msg_data => x_msg_data,
1814 p_rgpv_rec => p_rgpv_rec);
1815
1816 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1817 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1818 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
1819 raise OKC_API.G_EXCEPTION_ERROR;
1820 END IF;
1821
1822 g_rgpv_rec := l_rgpv_rec;
1823 okc_util.call_user_hook(x_return_status, g_pkg_name, l_api_name, 'A');
1824 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1825 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1826 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
1827 raise OKC_API.G_EXCEPTION_ERROR;
1828 END IF;
1829 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1830 EXCEPTION
1831 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1832 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1833 (l_api_name
1834 ,G_PKG_NAME
1835 ,'OKC_API.G_RET_STS_ERROR'
1836 ,x_msg_count
1837 ,x_msg_data
1838 ,'_PUB');
1839 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1840 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1841 (l_api_name
1842 ,G_PKG_NAME
1843 ,'OKC_API.G_RET_STS_UNEXP_ERROR'
1844 ,x_msg_count
1845 ,x_msg_data
1846 ,'_PUB');
1847 WHEN OTHERS THEN
1848 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1849 (l_api_name
1850 ,G_PKG_NAME
1851 ,'OTHERS'
1852 ,x_msg_count
1853 ,x_msg_data
1854 ,'_PUB');
1855 END validate_rule_group;
1856
1857 --------------------------------------
1858 -- PROCEDURE validate_rule_group
1859 --------------------------------------
1860 PROCEDURE validate_rule_group(
1861 p_api_version IN NUMBER,
1862 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1863 x_return_status OUT NOCOPY VARCHAR2,
1864 x_msg_count OUT NOCOPY NUMBER,
1865 x_msg_data OUT NOCOPY VARCHAR2,
1866 p_rgpv_tbl IN rgpv_tbl_type) IS
1867 i NUMBER := 0;
1868 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1869 BEGIN
1870 x_return_status := OKC_API.G_RET_STS_SUCCESS;
1871 IF p_rgpv_tbl.COUNT > 0 THEN
1872 i := p_rgpv_tbl.FIRST;
1873 LOOP
1874 validate_rule_group(
1875 p_api_version => p_api_version,
1876 p_init_msg_list => p_init_msg_list,
1877 x_return_status => l_return_status,
1878 x_msg_count => x_msg_count,
1879 x_msg_data => x_msg_data,
1880 p_rgpv_rec => p_rgpv_tbl(i));
1881 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
1882 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1883 x_return_status := l_return_status;
1884 raise G_EXCEPTION_HALT_VALIDATION;
1885 ELSE
1886 x_return_status := l_return_status;
1887 END IF;
1888 END IF;
1889 EXIT WHEN (i = p_rgpv_tbl.LAST);
1890 i := p_rgpv_tbl.NEXT(i);
1891 END LOOP;
1892 END IF;
1893 EXCEPTION
1894 WHEN G_EXCEPTION_HALT_VALIDATION THEN
1895 NULL;
1896 WHEN OTHERS THEN
1897 -- store SQL error message on message stack
1898 OKC_API.SET_MESSAGE(
1899 p_app_name => G_APP_NAME,
1900 p_msg_name => G_UNEXPECTED_ERROR,
1901 p_token1 => G_SQLCODE_TOKEN,
1902 p_token1_value => SQLCODE,
1903 p_token2 => G_SQLERRM_TOKEN,
1904 p_token2_value => SQLERRM);
1905 -- notify caller of an error as UNEXPETED error
1906 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1907 END validate_rule_group;
1908
1909 ---------------------------------------------------------------------------
1910 -- FUNCTION migrate_rmpv
1911 ---------------------------------------------------------------------------
1912 FUNCTION migrate_rmpv (
1913 p_rmpv_rec1 IN rmpv_rec_type,
1914 p_rmpv_rec2 IN rmpv_rec_type
1915 ) RETURN rmpv_rec_type IS
1916 l_rmpv_rec rmpv_rec_type;
1917 BEGIN
1918 --added after similar okc fix
1919 l_rmpv_rec.id := p_rmpv_rec2.id;
1920 --added after similar okc bug fix
1921 l_rmpv_rec.rgp_id := p_rmpv_rec2.rgp_id;
1922 l_rmpv_rec.rrd_id := p_rmpv_rec2.rrd_id;
1923 l_rmpv_rec.cpl_id := p_rmpv_rec2.cpl_id;
1924 l_rmpv_rec.dnz_chr_id := p_rmpv_rec2.dnz_chr_id;
1925 l_rmpv_rec.object_version_number := p_rmpv_rec1.object_version_number;
1926 l_rmpv_rec.created_by := p_rmpv_rec1.created_by;
1927 l_rmpv_rec.creation_date := p_rmpv_rec1.creation_date;
1928 l_rmpv_rec.last_updated_by := p_rmpv_rec1.last_updated_by;
1929 l_rmpv_rec.last_update_date := p_rmpv_rec1.last_update_date;
1930 l_rmpv_rec.last_update_login := p_rmpv_rec1.last_update_login;
1931
1932 RETURN (l_rmpv_rec);
1933 END migrate_rmpv;
1934
1935 --------------------------------------
1936 -- PROCEDURE create_rg_mode_pty_role
1937 --------------------------------------
1938 PROCEDURE create_rg_mode_pty_role(
1939 p_api_version IN NUMBER,
1940 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1941 x_return_status OUT NOCOPY VARCHAR2,
1942 x_msg_count OUT NOCOPY NUMBER,
1943 x_msg_data OUT NOCOPY VARCHAR2,
1944 p_rmpv_rec IN rmpv_rec_type,
1945 x_rmpv_rec OUT NOCOPY rmpv_rec_type) IS
1946 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1947 l_api_name CONSTANT VARCHAR2(30) := 'create_rg_mode_pty_role';
1948 l_rmpv_rec rmpv_rec_type := p_rmpv_rec;
1949 BEGIN
1950 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1951 p_init_msg_list,
1952 '_PUB',
1953 x_return_status);
1954 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1955 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1956 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1957 raise OKC_API.G_EXCEPTION_ERROR;
1958 END IF;
1959 g_rmpv_rec := p_rmpv_rec;
1960 okc_util.call_user_hook(x_return_status, g_pkg_name, l_api_name, 'B');
1961 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1962 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1963 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
1964 raise OKC_API.G_EXCEPTION_ERROR;
1965 END IF;
1966 l_rmpv_rec := migrate_rmpv(l_rmpv_rec, g_rmpv_rec);
1967
1968 okl_okc_migration_pvt.create_rg_mode_pty_role(
1969 p_api_version => p_api_version,
1970 p_init_msg_list => p_init_msg_list,
1971 x_return_status => x_return_status,
1972 x_msg_count => x_msg_count,
1973 x_msg_data => x_msg_data,
1974 p_rmpv_rec => l_rmpv_rec,
1975 x_rmpv_rec => x_rmpv_rec);
1976
1977 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1978 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1979 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
1980 raise OKC_API.G_EXCEPTION_ERROR;
1981 END IF;
1982
1983 g_rmpv_rec := x_rmpv_rec;
1984 okc_util.call_user_hook(x_return_status, g_pkg_name, l_api_name, 'A');
1985 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1986 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1987 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
1988 raise OKC_API.G_EXCEPTION_ERROR;
1989 END IF;
1990 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1991 EXCEPTION
1992 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1993 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1994 (l_api_name
1995 ,G_PKG_NAME
1996 ,'OKC_API.G_RET_STS_ERROR'
1997 ,x_msg_count
1998 ,x_msg_data
1999 ,'_PUB');
2000 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2001 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2002 (l_api_name
2003 ,G_PKG_NAME
2004 ,'OKC_API.G_RET_STS_UNEXP_ERROR'
2005 ,x_msg_count
2006 ,x_msg_data
2007 ,'_PUB');
2008 WHEN OTHERS THEN
2009 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2010 (l_api_name
2011 ,G_PKG_NAME
2012 ,'OTHERS'
2013 ,x_msg_count
2014 ,x_msg_data
2015 ,'_PUB');
2016 END create_rg_mode_pty_role;
2017
2018 --------------------------------------
2019 -- PROCEDURE create_rg_mode_pty_role
2020 --------------------------------------
2021 PROCEDURE create_rg_mode_pty_role(
2022 p_api_version IN NUMBER,
2023 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
2024 x_return_status OUT NOCOPY VARCHAR2,
2025 x_msg_count OUT NOCOPY NUMBER,
2026 x_msg_data OUT NOCOPY VARCHAR2,
2027 p_rmpv_tbl IN rmpv_tbl_type,
2028 x_rmpv_tbl OUT NOCOPY rmpv_tbl_type) IS
2029 i NUMBER := 0;
2030 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2031 BEGIN
2032 x_return_status := OKC_API.G_RET_STS_SUCCESS;
2033 IF p_rmpv_tbl.COUNT > 0 THEN
2034 i := p_rmpv_tbl.FIRST;
2035 LOOP
2036 create_rg_mode_pty_role(
2037 p_api_version => p_api_version,
2038 p_init_msg_list => p_init_msg_list,
2039 x_return_status => l_return_status,
2040 x_msg_count => x_msg_count,
2041 x_msg_data => x_msg_data,
2042 p_rmpv_rec => p_rmpv_tbl(i),
2043 x_rmpv_rec => x_rmpv_tbl(i));
2044 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
2045 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2046 x_return_status := l_return_status;
2047 raise G_EXCEPTION_HALT_VALIDATION;
2048 ELSE
2049 x_return_status := l_return_status;
2050 END IF;
2051 END IF;
2052 EXIT WHEN (i = p_rmpv_tbl.LAST);
2053 i := p_rmpv_tbl.NEXT(i);
2054 END LOOP;
2055 END IF;
2056 EXCEPTION
2057 WHEN G_EXCEPTION_HALT_VALIDATION THEN
2058 NULL;
2059 WHEN OTHERS THEN
2060 -- store SQL error message on message stack
2061 OKC_API.SET_MESSAGE(
2062 p_app_name => G_APP_NAME,
2063 p_msg_name => G_UNEXPECTED_ERROR,
2064 p_token1 => G_SQLCODE_TOKEN,
2065 p_token1_value => SQLCODE,
2066 p_token2 => G_SQLERRM_TOKEN,
2067 p_token2_value => SQLERRM);
2068 -- notify caller of an error as UNEXPETED error
2069 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
2070 END create_rg_mode_pty_role;
2071
2072 --------------------------------------
2073 -- PROCEDURE update_rg_mode_pty_role
2074 --------------------------------------
2075 PROCEDURE update_rg_mode_pty_role(
2076 p_api_version IN NUMBER,
2077 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
2078 x_return_status OUT NOCOPY VARCHAR2,
2079 x_msg_count OUT NOCOPY NUMBER,
2080 x_msg_data OUT NOCOPY VARCHAR2,
2081 p_rmpv_rec IN rmpv_rec_type,
2082 x_rmpv_rec OUT NOCOPY rmpv_rec_type) IS
2083 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2084 l_api_name CONSTANT VARCHAR2(30) := 'update_rg_mode_pty_role';
2085 l_rmpv_rec rmpv_rec_type := p_rmpv_rec;
2086 BEGIN
2087 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
2088 p_init_msg_list,
2089 '_PUB',
2090 x_return_status);
2091 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2092 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2093 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2094 raise OKC_API.G_EXCEPTION_ERROR;
2095 END IF;
2096 g_rmpv_rec := p_rmpv_rec;
2097 okc_util.call_user_hook(x_return_status, g_pkg_name, l_api_name, 'B');
2098 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2099 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2100 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
2101 raise OKC_API.G_EXCEPTION_ERROR;
2102 END IF;
2103 l_rmpv_rec := migrate_rmpv(l_rmpv_rec, g_rmpv_rec);
2104
2105 okl_okc_migration_pvt.update_rg_mode_pty_role(
2106 p_api_version => p_api_version,
2107 p_init_msg_list => p_init_msg_list,
2108 x_return_status => x_return_status,
2109 x_msg_count => x_msg_count,
2110 x_msg_data => x_msg_data,
2111 p_rmpv_rec => l_rmpv_rec,
2112 x_rmpv_rec => x_rmpv_rec);
2113
2114 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2115 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2116 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
2117 raise OKC_API.G_EXCEPTION_ERROR;
2118 END IF;
2119
2120 g_rmpv_rec := x_rmpv_rec;
2121 okc_util.call_user_hook(x_return_status, g_pkg_name, l_api_name, 'A');
2122 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2123 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2124 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
2125 raise OKC_API.G_EXCEPTION_ERROR;
2126 END IF;
2127 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2128 EXCEPTION
2129 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2130 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2131 (l_api_name
2132 ,G_PKG_NAME
2133 ,'OKC_API.G_RET_STS_ERROR'
2134 ,x_msg_count
2135 ,x_msg_data
2136 ,'_PUB');
2137 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2138 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2139 (l_api_name
2140 ,G_PKG_NAME
2141 ,'OKC_API.G_RET_STS_UNEXP_ERROR'
2142 ,x_msg_count
2143 ,x_msg_data
2144 ,'_PUB');
2145 WHEN OTHERS THEN
2146 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2147 (l_api_name
2148 ,G_PKG_NAME
2149 ,'OTHERS'
2150 ,x_msg_count
2151 ,x_msg_data
2152 ,'_PUB');
2153 END update_rg_mode_pty_role;
2154
2155 --------------------------------------
2156 -- PROCEDURE update_rg_mode_pty_role
2157 --------------------------------------
2158 PROCEDURE update_rg_mode_pty_role(
2159 p_api_version IN NUMBER,
2160 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
2161 x_return_status OUT NOCOPY VARCHAR2,
2162 x_msg_count OUT NOCOPY NUMBER,
2163 x_msg_data OUT NOCOPY VARCHAR2,
2164 p_rmpv_tbl IN rmpv_tbl_type,
2165 x_rmpv_tbl OUT NOCOPY rmpv_tbl_type) IS
2166 i NUMBER := 0;
2167 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2168 BEGIN
2169 x_return_status := OKC_API.G_RET_STS_SUCCESS;
2170 IF p_rmpv_tbl.COUNT > 0 THEN
2171 i := p_rmpv_tbl.FIRST;
2172 LOOP
2173 update_rg_mode_pty_role(
2174 p_api_version => p_api_version,
2175 p_init_msg_list => p_init_msg_list,
2176 x_return_status => l_return_status,
2177 x_msg_count => x_msg_count,
2178 x_msg_data => x_msg_data,
2179 p_rmpv_rec => p_rmpv_tbl(i),
2180 x_rmpv_rec => x_rmpv_tbl(i));
2181 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
2182 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2183 x_return_status := l_return_status;
2184 raise G_EXCEPTION_HALT_VALIDATION;
2185 ELSE
2186 x_return_status := l_return_status;
2187 END IF;
2188 END IF;
2189 EXIT WHEN (i = p_rmpv_tbl.LAST);
2190 i := p_rmpv_tbl.NEXT(i);
2191 END LOOP;
2192 END IF;
2193 EXCEPTION
2194 WHEN G_EXCEPTION_HALT_VALIDATION THEN
2195 NULL;
2196 WHEN OTHERS THEN
2197 -- store SQL error message on message stack
2198 OKC_API.SET_MESSAGE(
2199 p_app_name => G_APP_NAME,
2200 p_msg_name => G_UNEXPECTED_ERROR,
2201 p_token1 => G_SQLCODE_TOKEN,
2202 p_token1_value => SQLCODE,
2203 p_token2 => G_SQLERRM_TOKEN,
2204 p_token2_value => SQLERRM);
2205 -- notify caller of an error as UNEXPETED error
2206 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
2207 END update_rg_mode_pty_role;
2208
2209 --------------------------------------
2210 -- PROCEDURE delete_rg_mode_pty_role
2211 --------------------------------------
2212 PROCEDURE delete_rg_mode_pty_role(
2213 p_api_version IN NUMBER,
2214 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
2215 x_return_status OUT NOCOPY VARCHAR2,
2216 x_msg_count OUT NOCOPY NUMBER,
2217 x_msg_data OUT NOCOPY VARCHAR2,
2218 p_rmpv_rec IN rmpv_rec_type) IS
2219 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2220 l_api_name CONSTANT VARCHAR2(30) := 'delete_rg_mode_pty_role';
2221 l_rmpv_rec rmpv_rec_type := p_rmpv_rec;
2222 BEGIN
2223 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
2224 p_init_msg_list,
2225 '_PUB',
2226 x_return_status);
2227 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2228 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2229 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2230 raise OKC_API.G_EXCEPTION_ERROR;
2231 END IF;
2232 g_rmpv_rec := p_rmpv_rec;
2233 okc_util.call_user_hook(x_return_status, g_pkg_name, l_api_name, 'B');
2234 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2235 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2236 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
2237 raise OKC_API.G_EXCEPTION_ERROR;
2238 END IF;
2239
2240 okl_okc_migration_pvt.delete_rg_mode_pty_role(
2241 p_api_version => p_api_version,
2242 p_init_msg_list => p_init_msg_list,
2243 x_return_status => x_return_status,
2244 x_msg_count => x_msg_count,
2245 x_msg_data => x_msg_data,
2246 p_rmpv_rec => p_rmpv_rec);
2247
2248 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2249 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2250 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
2251 raise OKC_API.G_EXCEPTION_ERROR;
2252 END IF;
2253
2254 g_rmpv_rec := l_rmpv_rec;
2255 okc_util.call_user_hook(x_return_status, g_pkg_name, l_api_name, 'A');
2256 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2257 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2258 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
2259 raise OKC_API.G_EXCEPTION_ERROR;
2260 END IF;
2261 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2262 EXCEPTION
2263 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2264 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2265 (l_api_name
2266 ,G_PKG_NAME
2267 ,'OKC_API.G_RET_STS_ERROR'
2268 ,x_msg_count
2269 ,x_msg_data
2270 ,'_PUB');
2271 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2272 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2273 (l_api_name
2274 ,G_PKG_NAME
2275 ,'OKC_API.G_RET_STS_UNEXP_ERROR'
2276 ,x_msg_count
2277 ,x_msg_data
2278 ,'_PUB');
2279 WHEN OTHERS THEN
2280 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2281 (l_api_name
2282 ,G_PKG_NAME
2283 ,'OTHERS'
2284 ,x_msg_count
2285 ,x_msg_data
2286 ,'_PUB');
2287 END delete_rg_mode_pty_role;
2288
2289 --------------------------------------
2290 -- PROCEDURE delete_rg_mode_pty_role
2291 --------------------------------------
2292 PROCEDURE delete_rg_mode_pty_role(
2293 p_api_version IN NUMBER,
2294 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
2295 x_return_status OUT NOCOPY VARCHAR2,
2296 x_msg_count OUT NOCOPY NUMBER,
2297 x_msg_data OUT NOCOPY VARCHAR2,
2298 p_rmpv_tbl IN rmpv_tbl_type) IS
2299 i NUMBER := 0;
2300 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2301 BEGIN
2302 x_return_status := OKC_API.G_RET_STS_SUCCESS;
2303 IF p_rmpv_tbl.COUNT > 0 THEN
2304 i := p_rmpv_tbl.FIRST;
2305 LOOP
2306 delete_rg_mode_pty_role(
2307 p_api_version => p_api_version,
2308 p_init_msg_list => p_init_msg_list,
2309 x_return_status => l_return_status,
2310 x_msg_count => x_msg_count,
2311 x_msg_data => x_msg_data,
2312 p_rmpv_rec => p_rmpv_tbl(i));
2313 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
2314 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2315 x_return_status := l_return_status;
2316 raise G_EXCEPTION_HALT_VALIDATION;
2317 ELSE
2318 x_return_status := l_return_status;
2319 END IF;
2320 END IF;
2321 EXIT WHEN (i = p_rmpv_tbl.LAST);
2322 i := p_rmpv_tbl.NEXT(i);
2323 END LOOP;
2324 END IF;
2325 EXCEPTION
2326 WHEN G_EXCEPTION_HALT_VALIDATION THEN
2327 NULL;
2328 WHEN OTHERS THEN
2329 -- store SQL error message on message stack
2330 OKC_API.SET_MESSAGE(
2331 p_app_name => G_APP_NAME,
2332 p_msg_name => G_UNEXPECTED_ERROR,
2333 p_token1 => G_SQLCODE_TOKEN,
2334 p_token1_value => SQLCODE,
2335 p_token2 => G_SQLERRM_TOKEN,
2336 p_token2_value => SQLERRM);
2337 -- notify caller of an error as UNEXPETED error
2338 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
2339 END delete_rg_mode_pty_role;
2340
2341 --------------------------------------
2342 -- PROCEDURE lock_rg_mode_pty_role
2343 --------------------------------------
2344 PROCEDURE lock_rg_mode_pty_role(
2345 p_api_version IN NUMBER,
2346 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
2347 x_return_status OUT NOCOPY VARCHAR2,
2348 x_msg_count OUT NOCOPY NUMBER,
2349 x_msg_data OUT NOCOPY VARCHAR2,
2350 p_rmpv_rec IN rmpv_rec_type) IS
2351 BEGIN
2352 okl_okc_migration_pvt.lock_rg_mode_pty_role(
2353 p_api_version => p_api_version,
2354 p_init_msg_list => p_init_msg_list,
2355 x_return_status => x_return_status,
2356 x_msg_count => x_msg_count,
2357 x_msg_data => x_msg_data,
2358 p_rmpv_rec => p_rmpv_rec);
2359 END lock_rg_mode_pty_role;
2360
2361 --------------------------------------
2362 -- PROCEDURE lock_rg_mode_pty_role
2363 --------------------------------------
2364 PROCEDURE lock_rg_mode_pty_role(
2365 p_api_version IN NUMBER,
2366 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
2367 x_return_status OUT NOCOPY VARCHAR2,
2368 x_msg_count OUT NOCOPY NUMBER,
2369 x_msg_data OUT NOCOPY VARCHAR2,
2370 p_rmpv_tbl IN rmpv_tbl_type) IS
2371 i NUMBER := 0;
2372 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2373 BEGIN
2374 x_return_status := OKC_API.G_RET_STS_SUCCESS;
2375 IF p_rmpv_tbl.COUNT > 0 THEN
2376 i := p_rmpv_tbl.FIRST;
2377 LOOP
2378 lock_rg_mode_pty_role(
2379 p_api_version => p_api_version,
2380 p_init_msg_list => p_init_msg_list,
2381 x_return_status => l_return_status,
2382 x_msg_count => x_msg_count,
2383 x_msg_data => x_msg_data,
2384 p_rmpv_rec => p_rmpv_tbl(i));
2385 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
2386 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2387 x_return_status := l_return_status;
2388 raise G_EXCEPTION_HALT_VALIDATION;
2389 ELSE
2390 x_return_status := l_return_status;
2391 END IF;
2392 END IF;
2393 EXIT WHEN (i = p_rmpv_tbl.LAST);
2394 i := p_rmpv_tbl.NEXT(i);
2395 END LOOP;
2396 END IF;
2397 EXCEPTION
2398 WHEN G_EXCEPTION_HALT_VALIDATION THEN
2399 NULL;
2400 WHEN OTHERS THEN
2401 -- store SQL error message on message stack
2402 OKC_API.SET_MESSAGE(
2403 p_app_name => G_APP_NAME,
2404 p_msg_name => G_UNEXPECTED_ERROR,
2405 p_token1 => G_SQLCODE_TOKEN,
2406 p_token1_value => SQLCODE,
2407 p_token2 => G_SQLERRM_TOKEN,
2408 p_token2_value => SQLERRM);
2409 -- notify caller of an error as UNEXPETED error
2410 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
2411 END lock_rg_mode_pty_role;
2412
2413 --------------------------------------
2414 -- PROCEDURE validate_rg_mode_pty_role
2415 --------------------------------------
2416 PROCEDURE validate_rg_mode_pty_role(
2417 p_api_version IN NUMBER,
2418 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
2419 x_return_status OUT NOCOPY VARCHAR2,
2420 x_msg_count OUT NOCOPY NUMBER,
2421 x_msg_data OUT NOCOPY VARCHAR2,
2422 p_rmpv_rec IN rmpv_rec_type) IS
2423 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2424 l_api_name CONSTANT VARCHAR2(30) := 'validate_rg_mode_pty_role';
2425 l_rmpv_rec rmpv_rec_type := p_rmpv_rec;
2426 BEGIN
2427 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
2428 p_init_msg_list,
2429 '_PUB',
2430 x_return_status);
2431 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2432 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2433 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2434 raise OKC_API.G_EXCEPTION_ERROR;
2435 END IF;
2436 g_rmpv_rec := p_rmpv_rec;
2437 okc_util.call_user_hook(x_return_status, g_pkg_name, l_api_name, 'B');
2438 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2439 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2440 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
2441 raise OKC_API.G_EXCEPTION_ERROR;
2442 END IF;
2443
2444 okl_okc_migration_pvt.validate_rg_mode_pty_role(
2445 p_api_version => p_api_version,
2446 p_init_msg_list => p_init_msg_list,
2447 x_return_status => x_return_status,
2448 x_msg_count => x_msg_count,
2449 x_msg_data => x_msg_data,
2450 p_rmpv_rec => p_rmpv_rec);
2451
2452 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2453 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2454 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
2455 raise OKC_API.G_EXCEPTION_ERROR;
2456 END IF;
2457
2458 g_rmpv_rec := l_rmpv_rec;
2459 okc_util.call_user_hook(x_return_status, g_pkg_name, l_api_name, 'A');
2460 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2461 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2462 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
2463 raise OKC_API.G_EXCEPTION_ERROR;
2464 END IF;
2465 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2466 EXCEPTION
2467 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2468 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2469 (l_api_name
2470 ,G_PKG_NAME
2471 ,'OKC_API.G_RET_STS_ERROR'
2472 ,x_msg_count
2473 ,x_msg_data
2474 ,'_PUB');
2475 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2476 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2477 (l_api_name
2478 ,G_PKG_NAME
2479 ,'OKC_API.G_RET_STS_UNEXP_ERROR'
2480 ,x_msg_count
2481 ,x_msg_data
2482 ,'_PUB');
2483 WHEN OTHERS THEN
2484 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2485 (l_api_name
2486 ,G_PKG_NAME
2487 ,'OTHERS'
2488 ,x_msg_count
2489 ,x_msg_data
2490 ,'_PUB');
2491 END validate_rg_mode_pty_role;
2492
2493 --------------------------------------
2494 -- PROCEDURE validate_rg_mode_pty_role
2495 --------------------------------------
2496 PROCEDURE validate_rg_mode_pty_role(
2497 p_api_version IN NUMBER,
2498 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
2499 x_return_status OUT NOCOPY VARCHAR2,
2500 x_msg_count OUT NOCOPY NUMBER,
2501 x_msg_data OUT NOCOPY VARCHAR2,
2502 p_rmpv_tbl IN rmpv_tbl_type) IS
2503 i NUMBER := 0;
2504 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2505 BEGIN
2506 x_return_status := OKC_API.G_RET_STS_SUCCESS;
2507 IF p_rmpv_tbl.COUNT > 0 THEN
2508 i := p_rmpv_tbl.FIRST;
2509 LOOP
2510 validate_rg_mode_pty_role(
2511 p_api_version => p_api_version,
2512 p_init_msg_list => p_init_msg_list,
2513 x_return_status => l_return_status,
2514 x_msg_count => x_msg_count,
2515 x_msg_data => x_msg_data,
2516 p_rmpv_rec => p_rmpv_tbl(i));
2517 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
2518 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2519 x_return_status := l_return_status;
2520 raise G_EXCEPTION_HALT_VALIDATION;
2521 ELSE
2522 x_return_status := l_return_status;
2523 END IF;
2524 END IF;
2525 EXIT WHEN (i = p_rmpv_tbl.LAST);
2526 i := p_rmpv_tbl.NEXT(i);
2527 END LOOP;
2528 END IF;
2529 EXCEPTION
2530 WHEN G_EXCEPTION_HALT_VALIDATION THEN
2531 NULL;
2532 WHEN OTHERS THEN
2533 -- store SQL error message on message stack
2534 OKC_API.SET_MESSAGE(
2535 p_app_name => G_APP_NAME,
2536 p_msg_name => G_UNEXPECTED_ERROR,
2537 p_token1 => G_SQLCODE_TOKEN,
2538 p_token1_value => SQLCODE,
2539 p_token2 => G_SQLERRM_TOKEN,
2540 p_token2_value => SQLERRM);
2541 -- notify caller of an error as UNEXPETED error
2542 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
2543 END validate_rg_mode_pty_role;
2544 /*
2545 ---------------------------------------------------------------------------
2546 -- FUNCTION migrate_ctiv
2547 ---------------------------------------------------------------------------
2548 FUNCTION migrate_ctiv (
2549 p_ctiv_rec1 IN ctiv_rec_type,
2550 p_ctiv_rec2 IN ctiv_rec_type
2551 ) RETURN ctiv_rec_type IS
2552 l_ctiv_rec ctiv_rec_type;
2553 BEGIN
2554 l_ctiv_rec.rul_id := p_ctiv_rec2.rul_id;
2555 l_ctiv_rec.tve_id := p_ctiv_rec2.tve_id;
2556 l_ctiv_rec.dnz_chr_id := p_ctiv_rec2.dnz_chr_id;
2557 l_ctiv_rec.object_version_number := p_ctiv_rec1.object_version_number;
2558 l_ctiv_rec.created_by := p_ctiv_rec1.created_by;
2559 l_ctiv_rec.creation_date := p_ctiv_rec1.creation_date;
2560 l_ctiv_rec.last_updated_by := p_ctiv_rec1.last_updated_by;
2561 l_ctiv_rec.last_update_date := p_ctiv_rec1.last_update_date;
2562 l_ctiv_rec.last_update_login := p_ctiv_rec1.last_update_login;
2563
2564 RETURN (l_ctiv_rec);
2565 END migrate_ctiv;
2566
2567 --------------------------------------
2568 -- PROCEDURE create_cover_time
2569 --------------------------------------
2570 PROCEDURE create_cover_time(
2571 p_api_version IN NUMBER,
2572 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
2573 x_return_status OUT NOCOPY VARCHAR2,
2574 x_msg_count OUT NOCOPY NUMBER,
2575 x_msg_data OUT NOCOPY VARCHAR2,
2576 p_ctiv_rec IN ctiv_rec_type,
2577 x_ctiv_rec OUT NOCOPY ctiv_rec_type) IS
2578 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2579 l_api_name CONSTANT VARCHAR2(30) := 'create_cover_time';
2580 l_ctiv_rec ctiv_rec_type := p_ctiv_rec;
2581 BEGIN
2582 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
2583 p_init_msg_list,
2584 '_PUB',
2585 x_return_status);
2586 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2587 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2588 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2589 raise OKC_API.G_EXCEPTION_ERROR;
2590 END IF;
2591 g_ctiv_rec := p_ctiv_rec;
2592 okc_util.call_user_hook(x_return_status, g_pkg_name, l_api_name, 'B');
2593 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2594 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2595 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
2596 raise OKC_API.G_EXCEPTION_ERROR;
2597 END IF;
2598 l_ctiv_rec := migrate_ctiv(l_ctiv_rec, g_ctiv_rec);
2599
2600 OKC_RULE_PUB.create_cover_time(
2601 p_api_version => p_api_version,
2602 p_init_msg_list => p_init_msg_list,
2603 x_return_status => x_return_status,
2604 x_msg_count => x_msg_count,
2605 x_msg_data => x_msg_data,
2606 p_ctiv_rec => l_ctiv_rec,
2607 x_ctiv_rec => x_ctiv_rec);
2608
2609 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2610 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2611 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
2612 raise OKC_API.G_EXCEPTION_ERROR;
2613 END IF;
2614
2615 g_ctiv_rec := x_ctiv_rec;
2616 okc_util.call_user_hook(x_return_status, g_pkg_name, l_api_name, 'A');
2617 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2618 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2619 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
2620 raise OKC_API.G_EXCEPTION_ERROR;
2621 END IF;
2622 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2623 EXCEPTION
2624 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2625 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2626 (l_api_name
2627 ,G_PKG_NAME
2628 ,'OKC_API.G_RET_STS_ERROR'
2629 ,x_msg_count
2630 ,x_msg_data
2631 ,'_PUB');
2632 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2633 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2634 (l_api_name
2635 ,G_PKG_NAME
2636 ,'OKC_API.G_RET_STS_UNEXP_ERROR'
2637 ,x_msg_count
2638 ,x_msg_data
2639 ,'_PUB');
2640 WHEN OTHERS THEN
2641 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2642 (l_api_name
2643 ,G_PKG_NAME
2644 ,'OTHERS'
2645 ,x_msg_count
2646 ,x_msg_data
2647 ,'_PUB');
2648 END create_cover_time;
2649
2650 --------------------------------------
2651 -- PROCEDURE create_cover_time
2652 --------------------------------------
2653 PROCEDURE create_cover_time(
2654 p_api_version IN NUMBER,
2655 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
2656 x_return_status OUT NOCOPY VARCHAR2,
2657 x_msg_count OUT NOCOPY NUMBER,
2658 x_msg_data OUT NOCOPY VARCHAR2,
2659 p_ctiv_tbl IN ctiv_tbl_type,
2660 x_ctiv_tbl OUT NOCOPY ctiv_tbl_type) IS
2661 i NUMBER := 0;
2662 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2663 BEGIN
2664 x_return_status := OKC_API.G_RET_STS_SUCCESS;
2665 IF p_ctiv_tbl.COUNT > 0 THEN
2666 i := p_ctiv_tbl.FIRST;
2667 LOOP
2668 create_cover_time(
2669 p_api_version => p_api_version,
2670 p_init_msg_list => p_init_msg_list,
2671 x_return_status => l_return_status,
2672 x_msg_count => x_msg_count,
2673 x_msg_data => x_msg_data,
2674 p_ctiv_rec => p_ctiv_tbl(i),
2675 x_ctiv_rec => x_ctiv_tbl(i));
2676 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
2677 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2678 x_return_status := l_return_status;
2679 raise G_EXCEPTION_HALT_VALIDATION;
2680 ELSE
2681 x_return_status := l_return_status;
2682 END IF;
2683 END IF;
2684 EXIT WHEN (i = p_ctiv_tbl.LAST);
2685 i := p_ctiv_tbl.NEXT(i);
2686 END LOOP;
2687 END IF;
2688 EXCEPTION
2689 WHEN G_EXCEPTION_HALT_VALIDATION THEN
2690 NULL;
2691 WHEN OTHERS THEN
2692 -- store SQL error message on message stack
2693 OKC_API.SET_MESSAGE(
2694 p_app_name => G_APP_NAME,
2695 p_msg_name => G_UNEXPECTED_ERROR,
2696 p_token1 => G_SQLCODE_TOKEN,
2697 p_token1_value => SQLCODE,
2698 p_token2 => G_SQLERRM_TOKEN,
2699 p_token2_value => SQLERRM);
2700 -- notify caller of an error as UNEXPETED error
2701 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
2702 END create_cover_time;
2703
2704 --------------------------------------
2705 -- PROCEDURE update_cover_time
2706 --------------------------------------
2707 PROCEDURE update_cover_time(
2708 p_api_version IN NUMBER,
2709 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
2710 x_return_status OUT NOCOPY VARCHAR2,
2711 x_msg_count OUT NOCOPY NUMBER,
2712 x_msg_data OUT NOCOPY VARCHAR2,
2713 p_ctiv_rec IN ctiv_rec_type,
2714 x_ctiv_rec OUT NOCOPY ctiv_rec_type) IS
2715 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2716 l_api_name CONSTANT VARCHAR2(30) := 'update_cover_time';
2717 l_ctiv_rec ctiv_rec_type := p_ctiv_rec;
2718 BEGIN
2719 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
2720 p_init_msg_list,
2721 '_PUB',
2722 x_return_status);
2723 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2724 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2725 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2726 raise OKC_API.G_EXCEPTION_ERROR;
2727 END IF;
2728 g_ctiv_rec := p_ctiv_rec;
2729 okc_util.call_user_hook(x_return_status, g_pkg_name, l_api_name, 'B');
2730 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2731 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2732 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
2733 raise OKC_API.G_EXCEPTION_ERROR;
2734 END IF;
2735 l_ctiv_rec := migrate_ctiv(l_ctiv_rec, g_ctiv_rec);
2736
2737 OKC_RULE_PUB.update_cover_time(
2738 p_api_version => p_api_version,
2739 p_init_msg_list => p_init_msg_list,
2740 x_return_status => x_return_status,
2741 x_msg_count => x_msg_count,
2742 x_msg_data => x_msg_data,
2743 p_ctiv_rec => l_ctiv_rec,
2744 x_ctiv_rec => x_ctiv_rec);
2745
2746 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2747 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2748 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
2749 raise OKC_API.G_EXCEPTION_ERROR;
2750 END IF;
2751
2752 g_ctiv_rec := x_ctiv_rec;
2753 okc_util.call_user_hook(x_return_status, g_pkg_name, l_api_name, 'A');
2754 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2755 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2756 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
2757 raise OKC_API.G_EXCEPTION_ERROR;
2758 END IF;
2759 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2760 EXCEPTION
2761 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2762 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2763 (l_api_name
2764 ,G_PKG_NAME
2765 ,'OKC_API.G_RET_STS_ERROR'
2766 ,x_msg_count
2767 ,x_msg_data
2768 ,'_PUB');
2769 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2770 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2771 (l_api_name
2772 ,G_PKG_NAME
2773 ,'OKC_API.G_RET_STS_UNEXP_ERROR'
2774 ,x_msg_count
2775 ,x_msg_data
2776 ,'_PUB');
2777 WHEN OTHERS THEN
2778 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2779 (l_api_name
2780 ,G_PKG_NAME
2781 ,'OTHERS'
2782 ,x_msg_count
2783 ,x_msg_data
2784 ,'_PUB');
2785 END update_cover_time;
2786
2787 --------------------------------------
2788 -- PROCEDURE update_cover_time
2789 --------------------------------------
2790 PROCEDURE update_cover_time(
2791 p_api_version IN NUMBER,
2792 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
2793 x_return_status OUT NOCOPY VARCHAR2,
2794 x_msg_count OUT NOCOPY NUMBER,
2795 x_msg_data OUT NOCOPY VARCHAR2,
2796 p_ctiv_tbl IN ctiv_tbl_type,
2797 x_ctiv_tbl OUT NOCOPY ctiv_tbl_type) IS
2798 i NUMBER := 0;
2799 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2800 BEGIN
2801 x_return_status := OKC_API.G_RET_STS_SUCCESS;
2802 IF p_ctiv_tbl.COUNT > 0 THEN
2803 i := p_ctiv_tbl.FIRST;
2804 LOOP
2805 update_cover_time(
2806 p_api_version => p_api_version,
2807 p_init_msg_list => p_init_msg_list,
2808 x_return_status => l_return_status,
2809 x_msg_count => x_msg_count,
2810 x_msg_data => x_msg_data,
2811 p_ctiv_rec => p_ctiv_tbl(i),
2812 x_ctiv_rec => x_ctiv_tbl(i));
2813 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
2814 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2815 x_return_status := l_return_status;
2816 raise G_EXCEPTION_HALT_VALIDATION;
2817 ELSE
2818 x_return_status := l_return_status;
2819 END IF;
2820 END IF;
2821 EXIT WHEN (i = p_ctiv_tbl.LAST);
2822 i := p_ctiv_tbl.NEXT(i);
2823 END LOOP;
2824 END IF;
2825 EXCEPTION
2826 WHEN G_EXCEPTION_HALT_VALIDATION THEN
2827 NULL;
2828 WHEN OTHERS THEN
2829 -- store SQL error message on message stack
2830 OKC_API.SET_MESSAGE(
2831 p_app_name => G_APP_NAME,
2832 p_msg_name => G_UNEXPECTED_ERROR,
2833 p_token1 => G_SQLCODE_TOKEN,
2834 p_token1_value => SQLCODE,
2835 p_token2 => G_SQLERRM_TOKEN,
2836 p_token2_value => SQLERRM);
2837 -- notify caller of an error as UNEXPETED error
2838 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
2839 END update_cover_time;
2840
2841 --------------------------------------
2842 -- PROCEDURE delete_cover_time
2843 --------------------------------------
2844 PROCEDURE delete_cover_time(
2845 p_api_version IN NUMBER,
2846 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
2847 x_return_status OUT NOCOPY VARCHAR2,
2848 x_msg_count OUT NOCOPY NUMBER,
2849 x_msg_data OUT NOCOPY VARCHAR2,
2850 p_ctiv_rec IN ctiv_rec_type) IS
2851 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2852 l_api_name CONSTANT VARCHAR2(30) := 'delete_cover_time';
2853 l_ctiv_rec ctiv_rec_type := p_ctiv_rec;
2854 BEGIN
2855 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
2856 p_init_msg_list,
2857 '_PUB',
2858 x_return_status);
2859 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2860 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2861 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2862 raise OKC_API.G_EXCEPTION_ERROR;
2863 END IF;
2864 g_ctiv_rec := p_ctiv_rec;
2865 okc_util.call_user_hook(x_return_status, g_pkg_name, l_api_name, 'B');
2866 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2867 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2868 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
2869 raise OKC_API.G_EXCEPTION_ERROR;
2870 END IF;
2871
2872 OKC_RULE_PUB.delete_cover_time(
2873 p_api_version => p_api_version,
2874 p_init_msg_list => p_init_msg_list,
2875 x_return_status => x_return_status,
2876 x_msg_count => x_msg_count,
2877 x_msg_data => x_msg_data,
2878 p_ctiv_rec => p_ctiv_rec);
2879
2880 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2881 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2882 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
2883 raise OKC_API.G_EXCEPTION_ERROR;
2884 END IF;
2885
2886 g_ctiv_rec := l_ctiv_rec;
2887 okc_util.call_user_hook(x_return_status, g_pkg_name, l_api_name, 'A');
2888 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2889 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2890 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
2891 raise OKC_API.G_EXCEPTION_ERROR;
2892 END IF;
2893 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2894 EXCEPTION
2895 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2896 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2897 (l_api_name
2898 ,G_PKG_NAME
2899 ,'OKC_API.G_RET_STS_ERROR'
2900 ,x_msg_count
2901 ,x_msg_data
2902 ,'_PUB');
2903 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2904 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2905 (l_api_name
2906 ,G_PKG_NAME
2907 ,'OKC_API.G_RET_STS_UNEXP_ERROR'
2908 ,x_msg_count
2909 ,x_msg_data
2910 ,'_PUB');
2911 WHEN OTHERS THEN
2912 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2913 (l_api_name
2914 ,G_PKG_NAME
2915 ,'OTHERS'
2916 ,x_msg_count
2917 ,x_msg_data
2918 ,'_PUB');
2919 END delete_cover_time;
2920
2921 --------------------------------------
2922 -- PROCEDURE delete_cover_time
2923 --------------------------------------
2924 PROCEDURE delete_cover_time(
2925 p_api_version IN NUMBER,
2926 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
2927 x_return_status OUT NOCOPY VARCHAR2,
2928 x_msg_count OUT NOCOPY NUMBER,
2929 x_msg_data OUT NOCOPY VARCHAR2,
2930 p_ctiv_tbl IN ctiv_tbl_type) IS
2931 i NUMBER := 0;
2932 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2933 BEGIN
2934 x_return_status := OKC_API.G_RET_STS_SUCCESS;
2935 IF p_ctiv_tbl.COUNT > 0 THEN
2936 i := p_ctiv_tbl.FIRST;
2937 LOOP
2938 delete_cover_time(
2939 p_api_version => p_api_version,
2940 p_init_msg_list => p_init_msg_list,
2941 x_return_status => l_return_status,
2942 x_msg_count => x_msg_count,
2943 x_msg_data => x_msg_data,
2944 p_ctiv_rec => p_ctiv_tbl(i));
2945 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
2946 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2947 x_return_status := l_return_status;
2948 raise G_EXCEPTION_HALT_VALIDATION;
2949 ELSE
2950 x_return_status := l_return_status;
2951 END IF;
2952 END IF;
2953 EXIT WHEN (i = p_ctiv_tbl.LAST);
2954 i := p_ctiv_tbl.NEXT(i);
2955 END LOOP;
2956 END IF;
2957 EXCEPTION
2958 WHEN G_EXCEPTION_HALT_VALIDATION THEN
2959 NULL;
2960 WHEN OTHERS THEN
2961 -- store SQL error message on message stack
2962 OKC_API.SET_MESSAGE(
2963 p_app_name => G_APP_NAME,
2964 p_msg_name => G_UNEXPECTED_ERROR,
2965 p_token1 => G_SQLCODE_TOKEN,
2966 p_token1_value => SQLCODE,
2967 p_token2 => G_SQLERRM_TOKEN,
2968 p_token2_value => SQLERRM);
2969 -- notify caller of an error as UNEXPETED error
2970 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
2971 END delete_cover_time;
2972
2973 --------------------------------------
2974 -- PROCEDURE lock_cover_time
2975 --------------------------------------
2976 PROCEDURE lock_cover_time(
2977 p_api_version IN NUMBER,
2978 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
2979 x_return_status OUT NOCOPY VARCHAR2,
2980 x_msg_count OUT NOCOPY NUMBER,
2981 x_msg_data OUT NOCOPY VARCHAR2,
2982 p_ctiv_rec IN ctiv_rec_type) IS
2983 BEGIN
2984 OKC_RULE_PUB.lock_cover_time(
2985 p_api_version => p_api_version,
2986 p_init_msg_list => p_init_msg_list,
2987 x_return_status => x_return_status,
2988 x_msg_count => x_msg_count,
2989 x_msg_data => x_msg_data,
2990 p_ctiv_rec => p_ctiv_rec);
2991 END lock_cover_time;
2992
2993 --------------------------------------
2994 -- PROCEDURE lock_cover_time
2995 --------------------------------------
2996 PROCEDURE lock_cover_time(
2997 p_api_version IN NUMBER,
2998 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
2999 x_return_status OUT NOCOPY VARCHAR2,
3000 x_msg_count OUT NOCOPY NUMBER,
3001 x_msg_data OUT NOCOPY VARCHAR2,
3002 p_ctiv_tbl IN ctiv_tbl_type) IS
3003 i NUMBER := 0;
3004 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3005 BEGIN
3006 x_return_status := OKC_API.G_RET_STS_SUCCESS;
3007 IF p_ctiv_tbl.COUNT > 0 THEN
3008 i := p_ctiv_tbl.FIRST;
3009 LOOP
3010 lock_cover_time(
3011 p_api_version => p_api_version,
3012 p_init_msg_list => p_init_msg_list,
3013 x_return_status => l_return_status,
3014 x_msg_count => x_msg_count,
3015 x_msg_data => x_msg_data,
3016 p_ctiv_rec => p_ctiv_tbl(i));
3017 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
3018 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3019 x_return_status := l_return_status;
3020 raise G_EXCEPTION_HALT_VALIDATION;
3021 ELSE
3022 x_return_status := l_return_status;
3023 END IF;
3024 END IF;
3025 EXIT WHEN (i = p_ctiv_tbl.LAST);
3026 i := p_ctiv_tbl.NEXT(i);
3027 END LOOP;
3028 END IF;
3029 EXCEPTION
3030 WHEN G_EXCEPTION_HALT_VALIDATION THEN
3031 NULL;
3032 WHEN OTHERS THEN
3033 -- store SQL error message on message stack
3034 OKC_API.SET_MESSAGE(
3035 p_app_name => G_APP_NAME,
3036 p_msg_name => G_UNEXPECTED_ERROR,
3037 p_token1 => G_SQLCODE_TOKEN,
3038 p_token1_value => SQLCODE,
3039 p_token2 => G_SQLERRM_TOKEN,
3040 p_token2_value => SQLERRM);
3041 -- notify caller of an error as UNEXPETED error
3042 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
3043 END lock_cover_time;
3044
3045 --------------------------------------
3046 -- PROCEDURE validate_cover_time
3047 --------------------------------------
3048 PROCEDURE validate_cover_time(
3049 p_api_version IN NUMBER,
3050 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
3051 x_return_status OUT NOCOPY VARCHAR2,
3052 x_msg_count OUT NOCOPY NUMBER,
3053 x_msg_data OUT NOCOPY VARCHAR2,
3054 p_ctiv_rec IN ctiv_rec_type) IS
3055 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3056 l_api_name CONSTANT VARCHAR2(30) := 'validate_cover_time';
3057 l_ctiv_rec ctiv_rec_type := p_ctiv_rec;
3058 BEGIN
3059 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
3060 p_init_msg_list,
3061 '_PUB',
3062 x_return_status);
3063 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3064 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3065 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3066 raise OKC_API.G_EXCEPTION_ERROR;
3067 END IF;
3068 g_ctiv_rec := p_ctiv_rec;
3069 okc_util.call_user_hook(x_return_status, g_pkg_name, l_api_name, 'B');
3070 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3071 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3072 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
3073 raise OKC_API.G_EXCEPTION_ERROR;
3074 END IF;
3075
3076 OKC_RULE_PUB.validate_cover_time(
3077 p_api_version => p_api_version,
3078 p_init_msg_list => p_init_msg_list,
3079 x_return_status => x_return_status,
3080 x_msg_count => x_msg_count,
3081 x_msg_data => x_msg_data,
3082 p_ctiv_rec => p_ctiv_rec);
3083
3084 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3085 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3086 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
3087 raise OKC_API.G_EXCEPTION_ERROR;
3088 END IF;
3089
3090 g_ctiv_rec := l_ctiv_rec;
3091 okc_util.call_user_hook(x_return_status, g_pkg_name, l_api_name, 'A');
3092 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3093 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3094 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
3095 raise OKC_API.G_EXCEPTION_ERROR;
3096 END IF;
3097 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
3098 EXCEPTION
3099 WHEN OKC_API.G_EXCEPTION_ERROR THEN
3100 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3101 (l_api_name
3102 ,G_PKG_NAME
3103 ,'OKC_API.G_RET_STS_ERROR'
3104 ,x_msg_count
3105 ,x_msg_data
3106 ,'_PUB');
3107 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3108 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3109 (l_api_name
3110 ,G_PKG_NAME
3111 ,'OKC_API.G_RET_STS_UNEXP_ERROR'
3112 ,x_msg_count
3113 ,x_msg_data
3114 ,'_PUB');
3115 WHEN OTHERS THEN
3116 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3117 (l_api_name
3118 ,G_PKG_NAME
3119 ,'OTHERS'
3120 ,x_msg_count
3121 ,x_msg_data
3122 ,'_PUB');
3123 END validate_cover_time;
3124
3125 --------------------------------------
3126 -- PROCEDURE validate_cover_time
3127 --------------------------------------
3128 PROCEDURE validate_cover_time(
3129 p_api_version IN NUMBER,
3130 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
3131 x_return_status OUT NOCOPY VARCHAR2,
3132 x_msg_count OUT NOCOPY NUMBER,
3133 x_msg_data OUT NOCOPY VARCHAR2,
3134 p_ctiv_tbl IN ctiv_tbl_type) IS
3135 i NUMBER := 0;
3136 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3137 BEGIN
3138 x_return_status := OKC_API.G_RET_STS_SUCCESS;
3139 IF p_ctiv_tbl.COUNT > 0 THEN
3140 i := p_ctiv_tbl.FIRST;
3141 LOOP
3142 validate_cover_time(
3143 p_api_version => p_api_version,
3144 p_init_msg_list => p_init_msg_list,
3145 x_return_status => l_return_status,
3146 x_msg_count => x_msg_count,
3147 x_msg_data => x_msg_data,
3148 p_ctiv_rec => p_ctiv_tbl(i));
3149 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
3150 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3151 x_return_status := l_return_status;
3152 raise G_EXCEPTION_HALT_VALIDATION;
3153 ELSE
3154 x_return_status := l_return_status;
3155 END IF;
3156 END IF;
3157 EXIT WHEN (i = p_ctiv_tbl.LAST);
3158 i := p_ctiv_tbl.NEXT(i);
3159 END LOOP;
3160 END IF;
3161 EXCEPTION
3162 WHEN G_EXCEPTION_HALT_VALIDATION THEN
3163 NULL;
3164 WHEN OTHERS THEN
3165 -- store SQL error message on message stack
3166 OKC_API.SET_MESSAGE(
3167 p_app_name => G_APP_NAME,
3168 p_msg_name => G_UNEXPECTED_ERROR,
3169 p_token1 => G_SQLCODE_TOKEN,
3170 p_token1_value => SQLCODE,
3171 p_token2 => G_SQLERRM_TOKEN,
3172 p_token2_value => SQLERRM);
3173 -- notify caller of an error as UNEXPETED error
3174 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
3175 END validate_cover_time;
3176
3177 ---------------------------------------------------------------------------
3178 -- FUNCTION migrate_rilv
3179 ---------------------------------------------------------------------------
3180 FUNCTION migrate_rilv (
3181 p_rilv_rec1 IN rilv_rec_type,
3182 p_rilv_rec2 IN rilv_rec_type
3183 ) RETURN rilv_rec_type IS
3184 l_rilv_rec rilv_rec_type;
3185 BEGIN
3186 l_rilv_rec.tve_id := p_rilv_rec2.tve_id;
3187 l_rilv_rec.rul_id := p_rilv_rec2.rul_id;
3188 l_rilv_rec.dnz_chr_id := p_rilv_rec2.dnz_chr_id;
3189 l_rilv_rec.duration := p_rilv_rec2.duration;
3190 l_rilv_rec.uom_code := p_rilv_rec2.uom_code;
3191 l_rilv_rec.object_version_number := p_rilv_rec1.object_version_number;
3192 l_rilv_rec.created_by := p_rilv_rec1.created_by;
3193 l_rilv_rec.creation_date := p_rilv_rec1.creation_date;
3194 l_rilv_rec.last_updated_by := p_rilv_rec1.last_updated_by;
3195 l_rilv_rec.last_update_date := p_rilv_rec1.last_update_date;
3196 l_rilv_rec.last_update_login := p_rilv_rec1.last_update_login;
3197
3198 RETURN (l_rilv_rec);
3199 END migrate_rilv;
3200
3201 --------------------------------------
3202 -- PROCEDURE create_react_interval
3203 --------------------------------------
3204 PROCEDURE create_react_interval(
3205 p_api_version IN NUMBER,
3206 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
3207 x_return_status OUT NOCOPY VARCHAR2,
3208 x_msg_count OUT NOCOPY NUMBER,
3209 x_msg_data OUT NOCOPY VARCHAR2,
3210 p_rilv_rec IN rilv_rec_type,
3211 x_rilv_rec OUT NOCOPY rilv_rec_type) IS
3212 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3213 l_api_name CONSTANT VARCHAR2(30) := 'create_react_interval';
3214 l_rilv_rec rilv_rec_type := p_rilv_rec;
3215 BEGIN
3216 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
3217 p_init_msg_list,
3218 '_PUB',
3219 x_return_status);
3220 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3221 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3222 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3223 raise OKC_API.G_EXCEPTION_ERROR;
3224 END IF;
3225 g_rilv_rec := p_rilv_rec;
3226 okc_util.call_user_hook(x_return_status, g_pkg_name, l_api_name, 'B');
3227 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3228 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3229 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
3230 raise OKC_API.G_EXCEPTION_ERROR;
3231 END IF;
3232 l_rilv_rec := migrate_rilv(l_rilv_rec, g_rilv_rec);
3233
3234 OKC_RULE_PUB.create_react_interval(
3235 p_api_version => p_api_version,
3236 p_init_msg_list => p_init_msg_list,
3237 x_return_status => x_return_status,
3238 x_msg_count => x_msg_count,
3239 x_msg_data => x_msg_data,
3240 p_rilv_rec => l_rilv_rec,
3241 x_rilv_rec => x_rilv_rec);
3242
3243 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3244 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3245 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
3246 raise OKC_API.G_EXCEPTION_ERROR;
3247 END IF;
3248
3249 g_rilv_rec := x_rilv_rec;
3250 okc_util.call_user_hook(x_return_status, g_pkg_name, l_api_name, 'A');
3251 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3252 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3253 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
3254 raise OKC_API.G_EXCEPTION_ERROR;
3255 END IF;
3256 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
3257 EXCEPTION
3258 WHEN OKC_API.G_EXCEPTION_ERROR THEN
3259 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3260 (l_api_name
3261 ,G_PKG_NAME
3262 ,'OKC_API.G_RET_STS_ERROR'
3263 ,x_msg_count
3264 ,x_msg_data
3265 ,'_PUB');
3266 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3267 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3268 (l_api_name
3269 ,G_PKG_NAME
3270 ,'OKC_API.G_RET_STS_UNEXP_ERROR'
3271 ,x_msg_count
3272 ,x_msg_data
3273 ,'_PUB');
3274 WHEN OTHERS THEN
3275 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3276 (l_api_name
3277 ,G_PKG_NAME
3278 ,'OTHERS'
3279 ,x_msg_count
3280 ,x_msg_data
3281 ,'_PUB');
3282 END create_react_interval;
3283
3284 --------------------------------------
3285 -- PROCEDURE create_react_interval
3286 --------------------------------------
3287 PROCEDURE create_react_interval(
3288 p_api_version IN NUMBER,
3289 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
3290 x_return_status OUT NOCOPY VARCHAR2,
3291 x_msg_count OUT NOCOPY NUMBER,
3292 x_msg_data OUT NOCOPY VARCHAR2,
3293 p_rilv_tbl IN rilv_tbl_type,
3294 x_rilv_tbl OUT NOCOPY rilv_tbl_type) IS
3295 i NUMBER := 0;
3296 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3297 BEGIN
3298 x_return_status := OKC_API.G_RET_STS_SUCCESS;
3299 IF p_rilv_tbl.COUNT > 0 THEN
3300 i := p_rilv_tbl.FIRST;
3301 LOOP
3302 create_react_interval(
3303 p_api_version => p_api_version,
3304 p_init_msg_list => p_init_msg_list,
3305 x_return_status => l_return_status,
3306 x_msg_count => x_msg_count,
3307 x_msg_data => x_msg_data,
3308 p_rilv_rec => p_rilv_tbl(i),
3309 x_rilv_rec => x_rilv_tbl(i));
3310 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
3311 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3312 x_return_status := l_return_status;
3313 raise G_EXCEPTION_HALT_VALIDATION;
3314 ELSE
3315 x_return_status := l_return_status;
3316 END IF;
3317 END IF;
3318 EXIT WHEN (i = p_rilv_tbl.LAST);
3319 i := p_rilv_tbl.NEXT(i);
3320 END LOOP;
3321 END IF;
3322 EXCEPTION
3323 WHEN G_EXCEPTION_HALT_VALIDATION THEN
3324 NULL;
3325 WHEN OTHERS THEN
3326 -- store SQL error message on message stack
3327 OKC_API.SET_MESSAGE(
3328 p_app_name => G_APP_NAME,
3329 p_msg_name => G_UNEXPECTED_ERROR,
3330 p_token1 => G_SQLCODE_TOKEN,
3331 p_token1_value => SQLCODE,
3332 p_token2 => G_SQLERRM_TOKEN,
3333 p_token2_value => SQLERRM);
3334 -- notify caller of an error as UNEXPETED error
3335 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
3336 END create_react_interval;
3337
3338 --------------------------------------
3339 -- PROCEDURE update_react_interval
3340 --------------------------------------
3341 PROCEDURE update_react_interval(
3342 p_api_version IN NUMBER,
3343 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
3344 x_return_status OUT NOCOPY VARCHAR2,
3345 x_msg_count OUT NOCOPY NUMBER,
3346 x_msg_data OUT NOCOPY VARCHAR2,
3347 p_rilv_rec IN rilv_rec_type,
3348 x_rilv_rec OUT NOCOPY rilv_rec_type) IS
3349 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3350 l_api_name CONSTANT VARCHAR2(30) := 'update_react_interval';
3351 l_rilv_rec rilv_rec_type := p_rilv_rec;
3352 BEGIN
3353 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
3354 p_init_msg_list,
3355 '_PUB',
3356 x_return_status);
3357 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3358 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3359 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3360 raise OKC_API.G_EXCEPTION_ERROR;
3361 END IF;
3362 g_rilv_rec := p_rilv_rec;
3363 okc_util.call_user_hook(x_return_status, g_pkg_name, l_api_name, 'B');
3364 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3365 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3366 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
3367 raise OKC_API.G_EXCEPTION_ERROR;
3368 END IF;
3369 l_rilv_rec := migrate_rilv(l_rilv_rec, g_rilv_rec);
3370
3371 OKC_RULE_PUB.update_react_interval(
3372 p_api_version => p_api_version,
3373 p_init_msg_list => p_init_msg_list,
3374 x_return_status => x_return_status,
3375 x_msg_count => x_msg_count,
3376 x_msg_data => x_msg_data,
3377 p_rilv_rec => l_rilv_rec,
3378 x_rilv_rec => x_rilv_rec);
3379
3380 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3381 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3382 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
3383 raise OKC_API.G_EXCEPTION_ERROR;
3384 END IF;
3385
3386 g_rilv_rec := x_rilv_rec;
3387 okc_util.call_user_hook(x_return_status, g_pkg_name, l_api_name, 'A');
3388 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3389 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3390 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
3391 raise OKC_API.G_EXCEPTION_ERROR;
3392 END IF;
3393 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
3394 EXCEPTION
3395 WHEN OKC_API.G_EXCEPTION_ERROR THEN
3396 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3397 (l_api_name
3398 ,G_PKG_NAME
3399 ,'OKC_API.G_RET_STS_ERROR'
3400 ,x_msg_count
3401 ,x_msg_data
3402 ,'_PUB');
3403 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3404 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3405 (l_api_name
3406 ,G_PKG_NAME
3407 ,'OKC_API.G_RET_STS_UNEXP_ERROR'
3408 ,x_msg_count
3409 ,x_msg_data
3410 ,'_PUB');
3411 WHEN OTHERS THEN
3412 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3413 (l_api_name
3414 ,G_PKG_NAME
3415 ,'OTHERS'
3416 ,x_msg_count
3417 ,x_msg_data
3418 ,'_PUB');
3419 END update_react_interval;
3420
3421 --------------------------------------
3422 -- PROCEDURE update_react_interval
3423 --------------------------------------
3424 PROCEDURE update_react_interval(
3425 p_api_version IN NUMBER,
3426 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
3427 x_return_status OUT NOCOPY VARCHAR2,
3428 x_msg_count OUT NOCOPY NUMBER,
3429 x_msg_data OUT NOCOPY VARCHAR2,
3430 p_rilv_tbl IN rilv_tbl_type,
3431 x_rilv_tbl OUT NOCOPY rilv_tbl_type) IS
3432 i NUMBER := 0;
3433 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3434 BEGIN
3435 x_return_status := OKC_API.G_RET_STS_SUCCESS;
3436 IF p_rilv_tbl.COUNT > 0 THEN
3437 i := p_rilv_tbl.FIRST;
3438 LOOP
3439 update_react_interval(
3440 p_api_version => p_api_version,
3441 p_init_msg_list => p_init_msg_list,
3442 x_return_status => l_return_status,
3443 x_msg_count => x_msg_count,
3444 x_msg_data => x_msg_data,
3445 p_rilv_rec => p_rilv_tbl(i),
3446 x_rilv_rec => x_rilv_tbl(i));
3447 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
3448 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3449 x_return_status := l_return_status;
3450 raise G_EXCEPTION_HALT_VALIDATION;
3451 ELSE
3452 x_return_status := l_return_status;
3453 END IF;
3454 END IF;
3455 EXIT WHEN (i = p_rilv_tbl.LAST);
3456 i := p_rilv_tbl.NEXT(i);
3457 END LOOP;
3458 END IF;
3459 EXCEPTION
3460 WHEN G_EXCEPTION_HALT_VALIDATION THEN
3461 NULL;
3462 WHEN OTHERS THEN
3463 -- store SQL error message on message stack
3464 OKC_API.SET_MESSAGE(
3465 p_app_name => G_APP_NAME,
3466 p_msg_name => G_UNEXPECTED_ERROR,
3467 p_token1 => G_SQLCODE_TOKEN,
3468 p_token1_value => SQLCODE,
3469 p_token2 => G_SQLERRM_TOKEN,
3470 p_token2_value => SQLERRM);
3471 -- notify caller of an error as UNEXPETED error
3472 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
3473 END update_react_interval;
3474
3475 --------------------------------------
3476 -- PROCEDURE delete_react_interval
3477 --------------------------------------
3478 PROCEDURE delete_react_interval(
3479 p_api_version IN NUMBER,
3480 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
3481 x_return_status OUT NOCOPY VARCHAR2,
3482 x_msg_count OUT NOCOPY NUMBER,
3483 x_msg_data OUT NOCOPY VARCHAR2,
3484 p_rilv_rec IN rilv_rec_type) IS
3485 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3486 l_api_name CONSTANT VARCHAR2(30) := 'delete_react_interval';
3487 l_rilv_rec rilv_rec_type := p_rilv_rec;
3488 BEGIN
3489 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
3490 p_init_msg_list,
3491 '_PUB',
3492 x_return_status);
3493 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3494 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3495 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3496 raise OKC_API.G_EXCEPTION_ERROR;
3497 END IF;
3498 g_rilv_rec := p_rilv_rec;
3499 okc_util.call_user_hook(x_return_status, g_pkg_name, l_api_name, 'B');
3500 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3501 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3502 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
3503 raise OKC_API.G_EXCEPTION_ERROR;
3504 END IF;
3505
3506 OKC_RULE_PUB.delete_react_interval(
3507 p_api_version => p_api_version,
3508 p_init_msg_list => p_init_msg_list,
3509 x_return_status => x_return_status,
3510 x_msg_count => x_msg_count,
3511 x_msg_data => x_msg_data,
3512 p_rilv_rec => p_rilv_rec);
3513
3514 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3515 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3516 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
3517 raise OKC_API.G_EXCEPTION_ERROR;
3518 END IF;
3519
3520 g_rilv_rec := l_rilv_rec;
3521 okc_util.call_user_hook(x_return_status, g_pkg_name, l_api_name, 'A');
3522 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3523 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3524 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
3525 raise OKC_API.G_EXCEPTION_ERROR;
3526 END IF;
3527 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
3528 EXCEPTION
3529 WHEN OKC_API.G_EXCEPTION_ERROR THEN
3530 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3531 (l_api_name
3532 ,G_PKG_NAME
3533 ,'OKC_API.G_RET_STS_ERROR'
3534 ,x_msg_count
3535 ,x_msg_data
3536 ,'_PUB');
3537 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3538 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3539 (l_api_name
3540 ,G_PKG_NAME
3541 ,'OKC_API.G_RET_STS_UNEXP_ERROR'
3542 ,x_msg_count
3543 ,x_msg_data
3544 ,'_PUB');
3545 WHEN OTHERS THEN
3546 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3547 (l_api_name
3548 ,G_PKG_NAME
3549 ,'OTHERS'
3550 ,x_msg_count
3551 ,x_msg_data
3552 ,'_PUB');
3553 END delete_react_interval;
3554
3555 --------------------------------------
3556 -- PROCEDURE delete_react_interval
3557 --------------------------------------
3558 PROCEDURE delete_react_interval(
3559 p_api_version IN NUMBER,
3560 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
3561 x_return_status OUT NOCOPY VARCHAR2,
3562 x_msg_count OUT NOCOPY NUMBER,
3563 x_msg_data OUT NOCOPY VARCHAR2,
3564 p_rilv_tbl IN rilv_tbl_type) IS
3565 i NUMBER := 0;
3566 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3567 BEGIN
3568 x_return_status := OKC_API.G_RET_STS_SUCCESS;
3569 IF p_rilv_tbl.COUNT > 0 THEN
3570 i := p_rilv_tbl.FIRST;
3571 LOOP
3572 delete_react_interval(
3573 p_api_version => p_api_version,
3574 p_init_msg_list => p_init_msg_list,
3575 x_return_status => l_return_status,
3576 x_msg_count => x_msg_count,
3577 x_msg_data => x_msg_data,
3578 p_rilv_rec => p_rilv_tbl(i));
3579 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
3580 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3581 x_return_status := l_return_status;
3582 raise G_EXCEPTION_HALT_VALIDATION;
3583 ELSE
3584 x_return_status := l_return_status;
3585 END IF;
3586 END IF;
3587 EXIT WHEN (i = p_rilv_tbl.LAST);
3588 i := p_rilv_tbl.NEXT(i);
3589 END LOOP;
3590 END IF;
3591 EXCEPTION
3592 WHEN G_EXCEPTION_HALT_VALIDATION THEN
3593 NULL;
3594 WHEN OTHERS THEN
3595 -- store SQL error message on message stack
3596 OKC_API.SET_MESSAGE(
3597 p_app_name => G_APP_NAME,
3598 p_msg_name => G_UNEXPECTED_ERROR,
3599 p_token1 => G_SQLCODE_TOKEN,
3600 p_token1_value => SQLCODE,
3601 p_token2 => G_SQLERRM_TOKEN,
3602 p_token2_value => SQLERRM);
3603 -- notify caller of an error as UNEXPETED error
3604 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
3605 END delete_react_interval;
3606
3607 --------------------------------------
3608 -- PROCEDURE lock_react_interval
3609 --------------------------------------
3610 PROCEDURE lock_react_interval(
3611 p_api_version IN NUMBER,
3612 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
3613 x_return_status OUT NOCOPY VARCHAR2,
3614 x_msg_count OUT NOCOPY NUMBER,
3615 x_msg_data OUT NOCOPY VARCHAR2,
3616 p_rilv_rec IN rilv_rec_type) IS
3617 BEGIN
3618 OKC_RULE_PUB.lock_react_interval(
3619 p_api_version => p_api_version,
3620 p_init_msg_list => p_init_msg_list,
3621 x_return_status => x_return_status,
3622 x_msg_count => x_msg_count,
3623 x_msg_data => x_msg_data,
3624 p_rilv_rec => p_rilv_rec);
3625 END lock_react_interval;
3626
3627 --------------------------------------
3628 -- PROCEDURE lock_react_interval
3629 --------------------------------------
3630 PROCEDURE lock_react_interval(
3631 p_api_version IN NUMBER,
3632 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
3633 x_return_status OUT NOCOPY VARCHAR2,
3634 x_msg_count OUT NOCOPY NUMBER,
3635 x_msg_data OUT NOCOPY VARCHAR2,
3636 p_rilv_tbl IN rilv_tbl_type) IS
3637 i NUMBER := 0;
3638 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3639 BEGIN
3640 x_return_status := OKC_API.G_RET_STS_SUCCESS;
3641 IF p_rilv_tbl.COUNT > 0 THEN
3642 i := p_rilv_tbl.FIRST;
3643 LOOP
3644 lock_react_interval(
3645 p_api_version => p_api_version,
3646 p_init_msg_list => p_init_msg_list,
3647 x_return_status => l_return_status,
3648 x_msg_count => x_msg_count,
3649 x_msg_data => x_msg_data,
3650 p_rilv_rec => p_rilv_tbl(i));
3651 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
3652 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3653 x_return_status := l_return_status;
3654 raise G_EXCEPTION_HALT_VALIDATION;
3655 ELSE
3656 x_return_status := l_return_status;
3657 END IF;
3658 END IF;
3659 EXIT WHEN (i = p_rilv_tbl.LAST);
3660 i := p_rilv_tbl.NEXT(i);
3661 END LOOP;
3662 END IF;
3663 EXCEPTION
3664 WHEN G_EXCEPTION_HALT_VALIDATION THEN
3665 NULL;
3666 WHEN OTHERS THEN
3667 -- store SQL error message on message stack
3668 OKC_API.SET_MESSAGE(
3669 p_app_name => G_APP_NAME,
3670 p_msg_name => G_UNEXPECTED_ERROR,
3671 p_token1 => G_SQLCODE_TOKEN,
3672 p_token1_value => SQLCODE,
3673 p_token2 => G_SQLERRM_TOKEN,
3674 p_token2_value => SQLERRM);
3675 -- notify caller of an error as UNEXPETED error
3676 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
3677 END lock_react_interval;
3678
3679 --------------------------------------
3680 -- PROCEDURE validate_react_interval
3681 --------------------------------------
3682 PROCEDURE validate_react_interval(
3683 p_api_version IN NUMBER,
3684 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
3685 x_return_status OUT NOCOPY VARCHAR2,
3686 x_msg_count OUT NOCOPY NUMBER,
3687 x_msg_data OUT NOCOPY VARCHAR2,
3688 p_rilv_rec IN rilv_rec_type) IS
3689 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3690 l_api_name CONSTANT VARCHAR2(30) := 'validate_react_interval';
3691 l_rilv_rec rilv_rec_type := p_rilv_rec;
3692 BEGIN
3693 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
3694 p_init_msg_list,
3695 '_PUB',
3696 x_return_status);
3697 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3698 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3699 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3700 raise OKC_API.G_EXCEPTION_ERROR;
3701 END IF;
3702 g_rilv_rec := p_rilv_rec;
3703 okc_util.call_user_hook(x_return_status, g_pkg_name, l_api_name, 'B');
3704 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3705 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3706 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
3707 raise OKC_API.G_EXCEPTION_ERROR;
3708 END IF;
3709
3710 OKC_RULE_PUB.validate_react_interval(
3711 p_api_version => p_api_version,
3712 p_init_msg_list => p_init_msg_list,
3713 x_return_status => x_return_status,
3714 x_msg_count => x_msg_count,
3715 x_msg_data => x_msg_data,
3716 p_rilv_rec => p_rilv_rec);
3717
3718 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3719 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3720 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
3721 raise OKC_API.G_EXCEPTION_ERROR;
3722 END IF;
3723
3724 g_rilv_rec := l_rilv_rec;
3725 okc_util.call_user_hook(x_return_status, g_pkg_name, l_api_name, 'A');
3726 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3727 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3728 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
3729 raise OKC_API.G_EXCEPTION_ERROR;
3730 END IF;
3731 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
3732 EXCEPTION
3733 WHEN OKC_API.G_EXCEPTION_ERROR THEN
3734 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3735 (l_api_name
3736 ,G_PKG_NAME
3737 ,'OKC_API.G_RET_STS_ERROR'
3738 ,x_msg_count
3739 ,x_msg_data
3740 ,'_PUB');
3741 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3742 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3743 (l_api_name
3744 ,G_PKG_NAME
3745 ,'OKC_API.G_RET_STS_UNEXP_ERROR'
3746 ,x_msg_count
3747 ,x_msg_data
3748 ,'_PUB');
3749 WHEN OTHERS THEN
3750 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3751 (l_api_name
3752 ,G_PKG_NAME
3753 ,'OTHERS'
3754 ,x_msg_count
3755 ,x_msg_data
3756 ,'_PUB');
3757 END validate_react_interval;
3758
3759 --------------------------------------
3760 -- PROCEDURE validate_react_interval
3761 --------------------------------------
3762 PROCEDURE validate_react_interval(
3763 p_api_version IN NUMBER,
3764 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
3765 x_return_status OUT NOCOPY VARCHAR2,
3766 x_msg_count OUT NOCOPY NUMBER,
3767 x_msg_data OUT NOCOPY VARCHAR2,
3768 p_rilv_tbl IN rilv_tbl_type) IS
3769 i NUMBER := 0;
3770 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3771 BEGIN
3772 x_return_status := OKC_API.G_RET_STS_SUCCESS;
3773 IF p_rilv_tbl.COUNT > 0 THEN
3774 i := p_rilv_tbl.FIRST;
3775 LOOP
3776 validate_react_interval(
3777 p_api_version => p_api_version,
3778 p_init_msg_list => p_init_msg_list,
3779 x_return_status => l_return_status,
3780 x_msg_count => x_msg_count,
3781 x_msg_data => x_msg_data,
3782 p_rilv_rec => p_rilv_tbl(i));
3783 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
3784 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3785 x_return_status := l_return_status;
3786 raise G_EXCEPTION_HALT_VALIDATION;
3787 ELSE
3788 x_return_status := l_return_status;
3789 END IF;
3790 END IF;
3791 EXIT WHEN (i = p_rilv_tbl.LAST);
3792 i := p_rilv_tbl.NEXT(i);
3793 END LOOP;
3794 END IF;
3795 EXCEPTION
3796 WHEN G_EXCEPTION_HALT_VALIDATION THEN
3797 NULL;
3798 WHEN OTHERS THEN
3799 -- store SQL error message on message stack
3800 OKC_API.SET_MESSAGE(
3801 p_app_name => G_APP_NAME,
3802 p_msg_name => G_UNEXPECTED_ERROR,
3803 p_token1 => G_SQLCODE_TOKEN,
3804 p_token1_value => SQLCODE,
3805 p_token2 => G_SQLERRM_TOKEN,
3806 p_token2_value => SQLERRM);
3807 -- notify caller of an error as UNEXPETED error
3808 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
3809 END validate_react_interval;
3810 */
3811 --------------------------------------
3812 -- PROCEDURE add_language
3813 --------------------------------------
3814 PROCEDURE add_language IS
3815 BEGIN
3816 OKC_RULE_PUB.ADD_LANGUAGE;
3817 END add_language;
3818
3819 function rule_meaning(p_rle_code varchar2) return varchar2
3820 is
3821 -- cursor c1 is select meaning from fnd_lookups
3822 -- where lookup_type='OKC_RULE_DEF'
3823 -- and enabled_flag='Y'
3824 -- and lookup_code=p_rle_code;
3825
3826 --rule striping
3827 cursor c1 is
3828 select meaning
3829 from okc_rule_defs_v
3830 where rule_code = p_rle_code;
3831
3832 r varchar2(100);
3833 begin
3834 open c1;
3835 fetch c1 into r;
3836 close c1;
3837 return r;
3838 end;
3839
3840 --
3841 -- client call
3842 --
3843 function get_new_code
3844 (p_rgd_code in varchar2, p_rdf_code in varchar2, p_intent varchar2, p_number number)
3845 return varchar2 is
3846 CURSOR l_csr IS
3847 SELECT JTOT_OBJECT_CODE
3848 FROM okc_rule_def_sources_v
3849 WHERE RGR_RGD_CODE = p_rgd_code
3850 and RGR_RDF_CODE = p_rdf_code
3851 and BUY_OR_SELL = p_intent
3852 and OBJECT_ID_NUMBER = p_number
3853 and sysdate between start_date and nvl(end_date,sysdate)
3854 ;
3855 l_code varchar2(100);
3856 begin
3857 open l_csr;
3858 fetch l_csr into l_code;
3859 close l_csr;
3860 return l_code;
3861 end get_new_code;
3862
3863 function uncomment(p_where varchar2,p_what varchar2) return varchar2 is
3864 l_where varchar2(4000);
3865 l_rest varchar2(4000);
3866 pos number;
3867 pos1 number;
3868 begin
3869 pos:= instr(p_where,'/*');
3870 if (pos=0) then
3871 return p_where;
3872 else
3873 l_where := substr(p_where,1,pos-1);
3874 l_rest := substr(p_where,pos);
3875 end if;
3876 if (p_what='Y') then
3877 return l_where;
3878 end if;
3879 pos:= instr(l_rest,'--LOV');
3880 if (pos<>0) then
3881 l_rest := substr(l_rest,pos);
3882 pos:= instr(l_rest,fnd_global.newline);
3883 l_rest := substr(l_rest,pos+length(fnd_global.newline));
3884 pos1:= instr(l_rest,'*/');
3885 l_where := l_where||' '||substr(l_rest,1,pos1-1);
3886 l_rest := substr(l_rest,pos1+2);
3887 end if;
3888 if (p_what='LOV') then
3889 return l_where;
3890 end if;
3891 pos:= instr(l_rest,'--'||p_what);
3892 if (pos<>0) then
3893 l_rest := substr(l_rest,pos+2+length(p_what));
3894 pos:= instr(l_rest,fnd_global.newline);
3895 l_rest := substr(l_rest,pos+length(fnd_global.newline));
3896 pos1:= instr(l_rest,'*/');
3897 l_where := l_where||' '||substr(l_rest,1,pos1-1);
3898 end if;
3899 return l_where;
3900 end;
3901
3902 --
3903 --for old code server function
3904 --for new code usage on client with get_new_code combined
3905 --
3906 --
3907 -- (select 'object_code' object_code,id1,id2,name value,description
3908 -- from 'from_table'
3909 -- where 'where_clause')
3910 --
3911 function get_object_sql(p_object_code in varchar2,p_clause_yn in varchar2) return varchar2 is
3912 CURSOR l_csr IS
3913 SELECT
3914 '(select ''' || p_object_code || ''' object_code, id1, id2, name value, description from '
3915 ||from_table select_clause
3916 -- ||decode(p_clause_yn,'Y',decode(where_clause,'','',' where '||where_clause))|| ')'
3917 ,where_clause
3918 FROM JTF_OBJECTS_VL
3919 WHERE object_code = p_object_code;
3920 l_sql varchar2(4000);
3921 l_where varchar2(4000);
3922 begin
3923 if (p_object_code is NULL) then return
3924 '(select ''object_code'' object_code, ''id1'' id1, ''id2'' id2, ''value'' value, ''description'' description from dual where 0=1)';
3925 end if;
3926 open l_csr;
3927 fetch l_csr into l_sql,l_where;
3928 close l_csr;
3929 if (p_clause_yn='N' or l_where is null) then
3930 return l_sql||')';
3931 else
3932 --+
3933 l_where := uncomment(l_where,p_clause_yn);
3934 if (l_where is null) then
3935 return l_sql||')';
3936 else
3937 return l_sql||' where '||l_where||')';
3938 end if;
3939 --+
3940 end if;
3941 end get_object_sql;
3942
3943 function get_object_sql(p_object_code in varchar2,p_cpl_id in number) return varchar2 is
3944 CURSOR c1 IS
3945 SELECT object1_id1 from okc_k_party_roles_v
3946 where id = p_cpl_id;
3947 l1 varchar2(40);
3948
3949 CURSOR l_csr IS
3950 SELECT
3951 '(select ''' || p_object_code || ''' object_code, id1, id2, name value, description from '
3952 ||from_table||decode(where_clause,
3953 '',' where status = ''A'' and party_id = '''||l1||'''',
3954 ' where status = ''A'' and party_id = '''||l1||''' and '||where_clause)|| ')'
3955 FROM JTF_OBJECTS_VL
3956 WHERE object_code = p_object_code;
3957 l_sql varchar2(4000);
3958 begin
3959 if (p_object_code is NULL) then return
3960 '(select ''object_code'' object_code, ''id1'' id1, ''id2'' id2, ''value'' value, ''description'' description from dual where 0=1)';
3961 end if;
3962 open c1;
3963 fetch c1 into l1;
3964 close c1;
3965 open l_csr;
3966 fetch l_csr into l_sql;
3967 close l_csr;
3968 return l_sql;
3969 end get_object_sql;
3970
3971 function get_object_sql(p_object_code in varchar2) return varchar2 is
3972 begin
3973 return get_object_sql(p_object_code,'N');
3974 end;
3975
3976 function get_object_val
3977 (p_object_code in varchar2, p_object_id1 in varchar2, p_object_id2 in varchar2)
3978 return varchar2 is
3979 --
3980 --old data only, server call
3981 --
3982 TYPE ref_type IS REF CURSOR;
3983 l_ref_csr ref_type;
3984 l_sql varchar2(4000);
3985 l_val varchar2(2000);
3986 begin
3987 if (p_object_code is NULL or p_object_id1 is NULL) then return NULL;
3988 end if;
3989 l_sql :=
3990 'select value from '||get_object_sql(p_object_code,'Y')
3991 ||' where id1=:1 and id2=:2';
3992 OPEN l_ref_csr FOR l_sql
3993 USING p_object_id1,p_object_id2;
3994 FETCH l_ref_csr INTO l_val;
3995 CLOSE l_ref_csr;
3996 return l_val;
3997 end get_object_val;
3998
3999 function get_object_dsc
4000 (p_object_code in varchar2, p_object_id1 in varchar2, p_object_id2 in varchar2)
4001 return varchar2 is
4002 --
4003 --old data only, server call
4004 --
4005 TYPE ref_type IS REF CURSOR;
4006 l_ref_csr ref_type;
4007 l_sql varchar2(4000);
4008 l_val varchar2(2000);
4009 begin
4010 if (p_object_code is NULL or p_object_id1 is NULL) then return NULL;
4011 end if;
4012 l_sql :=
4013 'select description from '||get_object_sql(p_object_code,'Y')
4014 ||' where id1=:1 and id2=:2';
4015 OPEN l_ref_csr FOR l_sql
4016 USING p_object_id1,p_object_id2;
4017 FETCH l_ref_csr INTO l_val;
4018 CLOSE l_ref_csr;
4019 return l_val;
4020 end get_object_dsc;
4021
4022 --
4023 --new data, client call
4024 --
4025 procedure get_object_ids(
4026 p_value in varchar2,
4027 p_sql in varchar2,
4028 x_object_code out nocopy varchar2,
4029 x_id1 out nocopy varchar2,
4030 x_id2 out nocopy varchar2,
4031 x_desc out nocopy varchar2
4032 ) is
4033 TYPE ref_type IS REF CURSOR;
4034 l_ref_csr ref_type;
4035 l_sql varchar2(4000);
4036 l_val varchar2(2000);
4037 begin
4038 if (p_value is NULL or p_sql is NULL) then return;
4039 end if;
4040 l_sql :=
4041 'select object_code, id1, id2, description from '||p_sql
4042 ||' where value=:1';
4043 OPEN l_ref_csr FOR l_sql
4044 USING p_value;
4045 FETCH l_ref_csr INTO x_object_code, x_id1, x_id2, x_desc;
4046 CLOSE l_ref_csr;
4047 end get_object_ids;
4048
4049 procedure get_object_ids(
4050 p_value in varchar2,
4051 p_desc in varchar2,
4052 p_sql in varchar2,
4053 x_object_code out nocopy varchar2,
4054 x_id1 out nocopy varchar2,
4055 x_id2 out nocopy varchar2,
4056 x_desc out nocopy varchar2
4057 ) is
4058 TYPE ref_type IS REF CURSOR;
4059 l_ref_csr ref_type;
4060 l_sql varchar2(4000);
4061 l_val varchar2(2000);
4062 begin
4063 if (p_value is NULL or p_sql is NULL) then return;
4064 end if;
4065 l_sql :=
4066 'select object_code, id1, id2, description from '||p_sql
4067 ||' where value=:1 and ((:2 is null and description is null) or (:3 = description))';
4068 OPEN l_ref_csr FOR l_sql
4069 USING p_value, p_desc, p_desc;
4070 FETCH l_ref_csr INTO x_object_code, x_id1, x_id2, x_desc;
4071 CLOSE l_ref_csr;
4072 end get_object_ids;
4073
4074 --
4075 --new on client
4076 --old on server
4077 --
4078 function get_flex_sql(p_rdf_code in varchar2, p_col_name in varchar2,p_clause_yn in varchar2) return varchar2 is
4079 --
4080 -- (select id, value, meaning description
4081 -- from 'application_table_name'
4082 -- where 'additional_where_clause' --get rid of where and order by)
4083 --
4084 CURSOR l_csr IS
4085 -- select 'select '||tbl.ID_COLUMN_NAME||' id, '
4086 -- ||decode(col.END_USER_COLUMN_NAME,'TVE_ID',
4087 --'comments value, '''' description from ',
4088 --tbl.VALUE_COLUMN_NAME||' value, '||NVL(tbl.MEANING_COLUMN_NAME,'''''')||' description from ')
4089 -- ||tbl.APPLICATION_TABLE_NAME sel,
4090 -- tbl.ADDITIONAL_WHERE_CLAUSE whr
4091 -- from fnd_descr_flex_col_usage_vl col,
4092 -- fnd_flex_validation_tables tbl
4093 -- where col.application_id=510
4094 -- and col.descriptive_flexfield_name='OKC Rule Developer DF'
4095 -- and col.descriptive_flex_context_code=p_rdf_code
4096 -- and col.application_column_name=p_col_name
4097 -- and col.FLEX_VALUE_SET_ID=tbl.FLEX_VALUE_SET_ID
4098 -- ;
4099 --rule striping
4100 select 'select '||tbl.ID_COLUMN_NAME||' id, '
4101 ||decode(col.END_USER_COLUMN_NAME,'TVE_ID',
4102 'comments value, '''' description from ',
4103 tbl.VALUE_COLUMN_NAME||' value, '||NVL(tbl.MEANING_COLUMN_NAME,'''''')||' description from ')
4104 ||tbl.APPLICATION_TABLE_NAME sel,
4105 tbl.ADDITIONAL_WHERE_CLAUSE whr
4106 from fnd_descr_flex_col_usage_vl col,
4107 fnd_flex_validation_tables tbl,
4108 okc_rule_defs_v rdefv
4109 where col.application_id = rdefv.application_id
4110 and col.descriptive_flexfield_name = rdefv.descriptive_flexfield_name
4111 and col.descriptive_flex_context_code = rdefv.rule_code
4112 and col.application_column_name = p_col_name
4113 and rdefv.rule_code = p_rdf_code
4114 and col.FLEX_VALUE_SET_ID=tbl.FLEX_VALUE_SET_ID;
4115
4116 l_sel varchar2(4000);
4117 l_whr varchar2(4000);
4118 begin
4119 open l_csr;
4120 fetch l_csr into l_sel, l_whr;
4121 close l_csr;
4122 if (l_sel is null) then return null;
4123 end if;
4124 if (l_whr is null or p_clause_yn='N') then return '('||l_sel||')';
4125 end if;
4126 l_whr := upper(l_whr);
4127 if (substr(l_whr,1,6)='WHERE ') then l_whr:=substr(l_whr,7);
4128 end if;
4129 if (instr(l_whr,'ORDER BY')<>0) then l_whr:=substr(l_whr,1,instr(l_whr,'ORDER BY')-1);
4130 end if;
4131 if (l_whr is null) then return '('||l_sel||')';
4132 end if;
4133 --+
4134 l_whr := uncomment(l_whr,p_clause_yn);
4135 if (l_whr is null) then return '('||l_sel||')';
4136 end if;
4137 --+
4138 return '('||l_sel||' where '||l_whr||')';
4139 end get_flex_sql;
4140
4141 function get_flex_sql(p_rdf_code in varchar2, p_col_name in varchar2) return varchar2 is
4142 begin
4143 return get_flex_sql(p_rdf_code, p_col_name,'N');
4144 end;
4145
4146 --
4147 --old data only, server call
4148 --
4149 function get_flex_val(p_rdf_code in varchar2, p_col_name in varchar2, p_id in varchar2)
4150 return varchar2 is
4151 TYPE ref_type IS REF CURSOR;
4152 l_ref_csr ref_type;
4153 l_sql varchar2(4000);
4154 l_val varchar2(2000);
4155 begin
4156 if (p_id is NULL) then return NULL;
4157 end if;
4158 l_sql := get_flex_sql(p_rdf_code, p_col_name,'Y');
4159 if (l_sql is null) then return NULL;
4160 end if;
4161 l_sql := 'select value from '||l_sql||' where id=:1';
4162 OPEN l_ref_csr FOR l_sql
4163 USING p_id;
4164 FETCH l_ref_csr INTO l_val;
4165 CLOSE l_ref_csr;
4166 return l_val;
4167 end get_flex_val;
4168
4169 --
4170 --old data only, server call
4171 --
4172 function get_flex_dsc(p_rdf_code in varchar2, p_col_name in varchar2, p_id in varchar2)
4173 return varchar2 is
4174 TYPE ref_type IS REF CURSOR;
4175 l_ref_csr ref_type;
4176 l_sql varchar2(4000);
4177 l_val varchar2(2000);
4178 begin
4179 if (p_id is NULL) then return NULL;
4180 end if;
4181 l_sql := get_flex_sql(p_rdf_code, p_col_name,'Y');
4182 if (l_sql is null) then return NULL;
4183 end if;
4184 l_sql := 'select description from '||l_sql||' where id=:1';
4185 OPEN l_ref_csr FOR l_sql
4186 USING p_id;
4187 FETCH l_ref_csr INTO l_val;
4188 CLOSE l_ref_csr;
4189 return l_val;
4190 end get_flex_dsc;
4191
4192 --
4193 --new data only, client call
4194 --
4195 procedure get_flex_ids(
4196 p_value varchar2,
4197 p_sql in varchar2,
4198 x_id out nocopy varchar2,
4199 x_desc out nocopy varchar2
4200 ) is
4201 TYPE ref_type IS REF CURSOR;
4202 l_ref_csr ref_type;
4203 l_sql varchar2(4000);
4204 l_val varchar2(2000);
4205 begin
4206 if (p_value is NULL or p_sql is NULL) then return;
4207 end if;
4208 l_sql :=
4209 'select id, description from '||p_sql
4210 ||' where value=:1';
4211 OPEN l_ref_csr FOR l_sql
4212 USING p_value;
4213 FETCH l_ref_csr INTO x_id, x_desc;
4214 CLOSE l_ref_csr;
4215 end get_flex_ids;
4216
4217 procedure get_flex_ids(
4218 p_value varchar2,
4219 p_desc varchar2,
4220 p_sql in varchar2,
4221 x_id out nocopy varchar2,
4222 x_desc out nocopy varchar2
4223 ) is
4224 TYPE ref_type IS REF CURSOR;
4225 l_ref_csr ref_type;
4226 l_sql varchar2(4000);
4227 l_val varchar2(2000);
4228 begin
4229 if (p_value is NULL or p_sql is NULL) then return;
4230 end if;
4231 l_sql :=
4232 'select id, description from '||p_sql
4233 ||' where value=:1 and ((:2 is null and description is null) or (:3 = description))';
4234 OPEN l_ref_csr FOR l_sql
4235 USING p_value, p_desc, p_desc;
4236 FETCH l_ref_csr INTO x_id, x_desc;
4237 CLOSE l_ref_csr;
4238 end get_flex_ids;
4239
4240 function euro_YN(rle_code varchar2, p_chr_id number) return varchar2 is
4241 begin
4242 if (rle_code='CVN') then
4243 if(okc_currency_api.get_ou_currency(p_chr_id)='EMU') then
4244 return 'Y';
4245 end if;
4246 end if;
4247 return 'N';
4248 end;
4249
4250 function gen_comments return varchar2 is
4251 begin
4252 return G_GEN_COMMENTS;
4253 end;
4254
4255 procedure no_comments is
4256 begin
4257 G_GEN_COMMENTS := 'F';
4258 end;
4259
4260 function euro_yn(auth_org_id number) return varchar2 is
4261 begin
4262 if okc_currency_api.get_currency_type(
4263 okc_currency_api.get_ou_currency(
4264 nvl(auth_org_id,nvl(OKL_CONTEXT.get_okc_org_id,-99)) --dkagrawa changed for MOAC
4265 ),sysdate) like 'EMU%' then
4266 return 'Y';
4267 else return 'N';
4268 end if;
4269 exception
4270 when others then return 'N';
4271 end;
4272
4273 procedure issue_savepoint (sp varchar2) is
4274 begin
4275 dbms_transaction.savepoint(sp);
4276 exception when others then NULL;
4277 end;
4278
4279 procedure rollback_savepoint (sp varchar2) is
4280 begin
4281 rollback to sp;
4282 exception when others then NULL;
4283 end;
4284
4285 procedure initialize(x out nocopy rulv_tbl_type) is
4286 begin
4287 x(1).last_update_date := sysdate;
4288 end;
4289
4290 procedure initialize(x out nocopy rgpv_tbl_type) is
4291 begin
4292 x(1).last_update_date := sysdate;
4293 end;
4294
4295 procedure initialize(x out nocopy rmpv_tbl_type) is
4296 begin
4297 x(1).last_update_date := sysdate;
4298 end;
4299
4300 END OKL_RULE_PUB;