[Home] [Help]
PACKAGE BODY: APPS.OKC_CGC_PVT
Source
1 PACKAGE BODY OKC_CGC_PVT AS
2 /* $Header: OKCSCGCB.pls 120.0 2005/05/26 09:46:16 appldev noship $ */
3
4 l_debug VARCHAR2(1) := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'),'N');
5
6 g_qry_clause Varchar2(2000);
7
8 ---------------------------------------------------------------------------
9 -- FUNCTION get_seq_id
10 ---------------------------------------------------------------------------
11 FUNCTION get_seq_id RETURN NUMBER IS
12 BEGIN
13 RETURN(okc_p_util.raw_to_number(sys_guid()));
14 END get_seq_id;
15
16 ---------------------------------------------------------------------------
17 -- PROCEDURE qc
18 ---------------------------------------------------------------------------
19 PROCEDURE qc IS
20 BEGIN
21 null;
22 END qc;
23
24 ---------------------------------------------------------------------------
25 -- PROCEDURE change_version
26 ---------------------------------------------------------------------------
27 PROCEDURE change_version IS
28 BEGIN
29 null;
30 END change_version;
31
32 ---------------------------------------------------------------------------
33 -- PROCEDURE api_copy
34 ---------------------------------------------------------------------------
35 PROCEDURE api_copy IS
36 BEGIN
37 null;
38 END api_copy;
39
40 ---------------------------------------------------------------------------
41 -- FUNCTION get_rec for: OKC_K_GRPINGS
42 ---------------------------------------------------------------------------
43 FUNCTION get_rec (
44 p_cgc_rec IN cgc_rec_type,
45 x_no_data_found OUT NOCOPY BOOLEAN
46 ) RETURN cgc_rec_type IS
47 CURSOR cgc_pk_csr (p_id IN NUMBER) IS
48 SELECT
49 ID,
50 CGP_PARENT_ID,
51 INCLUDED_CGP_ID,
52 INCLUDED_CHR_ID,
53 SCS_CODE,
54 OBJECT_VERSION_NUMBER,
55 CREATED_BY,
56 CREATION_DATE,
57 LAST_UPDATED_BY,
58 LAST_UPDATE_DATE,
59 LAST_UPDATE_LOGIN
60 FROM Okc_K_Grpings
61 WHERE okc_K_grpings.id = p_id;
62 l_cgc_pk cgc_pk_csr%ROWTYPE;
63 l_cgc_rec cgc_rec_type;
64 BEGIN
65 x_no_data_found := TRUE;
66 -- Get current database values
67 OPEN cgc_pk_csr (p_cgc_rec.id);
68 FETCH cgc_pk_csr INTO
69 l_cgc_rec.ID,
70 l_cgc_rec.CGP_PARENT_ID,
71 l_cgc_rec.INCLUDED_CGP_ID,
72 l_cgc_rec.INCLUDED_CHR_ID,
73 l_cgc_rec.SCS_CODE,
74 l_cgc_rec.OBJECT_VERSION_NUMBER,
75 l_cgc_rec.CREATED_BY,
76 l_cgc_rec.CREATION_DATE,
77 l_cgc_rec.LAST_UPDATED_BY,
78 l_cgc_rec.LAST_UPDATE_DATE,
79 l_cgc_rec.LAST_UPDATE_LOGIN;
80 x_no_data_found := cgc_pk_csr%NOTFOUND;
81 CLOSE cgc_pk_csr;
82 RETURN(l_cgc_rec);
83 END get_rec;
84
85 FUNCTION get_rec (
86 p_cgc_rec IN cgc_rec_type
87 ) RETURN cgc_rec_type IS
88 l_row_notfound BOOLEAN := TRUE;
89 BEGIN
90 RETURN(get_rec(p_cgc_rec, l_row_notfound));
91 END get_rec;
92 ---------------------------------------------------------------------------
93 -- FUNCTION get_rec for: OKC_K_GRPINGS_V
94 ---------------------------------------------------------------------------
95 FUNCTION get_rec (
96 p_cgcv_rec IN cgcv_rec_type,
97 x_no_data_found OUT NOCOPY BOOLEAN
98 ) RETURN cgcv_rec_type IS
99 CURSOR okc_cgcv_pk_csr (p_id IN NUMBER) IS
100 SELECT
101 ID,
102 OBJECT_VERSION_NUMBER,
103 CGP_PARENT_ID,
104 INCLUDED_CHR_ID,
105 INCLUDED_CGP_ID,
106 SCS_CODE,
107 CREATED_BY,
108 CREATION_DATE,
109 LAST_UPDATED_BY,
110 LAST_UPDATE_DATE,
111 LAST_UPDATE_LOGIN
112 FROM Okc_K_Grpings_V
113 WHERE okc_K_grpings_v.id = p_id;
114 l_okc_cgcv_pk okc_cgcv_pk_csr%ROWTYPE;
115 l_cgcv_rec cgcv_rec_type;
116 BEGIN
117 x_no_data_found := TRUE;
118 -- Get current database values
119 OPEN okc_cgcv_pk_csr (p_cgcv_rec.id);
120 FETCH okc_cgcv_pk_csr INTO
121 l_cgcv_rec.ID,
122 l_cgcv_rec.OBJECT_VERSION_NUMBER,
123 l_cgcv_rec.CGP_PARENT_ID,
124 l_cgcv_rec.INCLUDED_CHR_ID,
125 l_cgcv_rec.INCLUDED_CGP_ID,
126 l_cgcv_rec.SCS_CODE,
127 l_cgcv_rec.CREATED_BY,
128 l_cgcv_rec.CREATION_DATE,
129 l_cgcv_rec.LAST_UPDATED_BY,
130 l_cgcv_rec.LAST_UPDATE_DATE,
131 l_cgcv_rec.LAST_UPDATE_LOGIN;
132 x_no_data_found := okc_cgcv_pk_csr%NOTFOUND;
133 CLOSE okc_cgcv_pk_csr;
134 RETURN(l_cgcv_rec);
135 END get_rec;
136
137 FUNCTION get_rec (
138 p_cgcv_rec IN cgcv_rec_type
139 ) RETURN cgcv_rec_type IS
140 l_row_notfound BOOLEAN := TRUE;
141 BEGIN
142 RETURN(get_rec(p_cgcv_rec, l_row_notfound));
143 END get_rec;
144
145 ------------------------------------------------------------
146 -- FUNCTION null_out_defaults for: OKC_K_GRPINGS_V --
147 ------------------------------------------------------------
148 FUNCTION null_out_defaults (
149 p_cgcv_rec IN cgcv_rec_type
150 ) RETURN cgcv_rec_type IS
151 l_cgcv_rec cgcv_rec_type := p_cgcv_rec;
152 BEGIN
153 IF (l_cgcv_rec.object_version_number = OKC_API.G_MISS_NUM) THEN
154 l_cgcv_rec.object_version_number := NULL;
155 END IF;
156 IF (l_cgcv_rec.cgp_parent_id = OKC_API.G_MISS_NUM) THEN
157 l_cgcv_rec.cgp_parent_id := NULL;
158 END IF;
159 IF (l_cgcv_rec.included_chr_id = OKC_API.G_MISS_NUM) THEN
160 l_cgcv_rec.included_chr_id := NULL;
161 END IF;
162 IF (l_cgcv_rec.included_cgp_id = OKC_API.G_MISS_NUM) THEN
163 l_cgcv_rec.included_cgp_id := NULL;
164 END IF;
165 IF (l_cgcv_rec.scs_code = OKC_API.G_MISS_CHAR) THEN
166 l_cgcv_rec.scs_code := NULL;
167 END IF;
168 IF (l_cgcv_rec.created_by = OKC_API.G_MISS_NUM) THEN
169 l_cgcv_rec.created_by := NULL;
170 END IF;
171 IF (l_cgcv_rec.creation_date = OKC_API.G_MISS_DATE) THEN
172 l_cgcv_rec.creation_date := NULL;
173 END IF;
174 IF (l_cgcv_rec.last_updated_by = OKC_API.G_MISS_NUM) THEN
175 l_cgcv_rec.last_updated_by := NULL;
176 END IF;
177 IF (l_cgcv_rec.last_update_date = OKC_API.G_MISS_DATE) THEN
178 l_cgcv_rec.last_update_date := NULL;
179 END IF;
180 IF (l_cgcv_rec.last_update_login = OKC_API.G_MISS_NUM) THEN
181 l_cgcv_rec.last_update_login := NULL;
182 END IF;
183 RETURN(l_cgcv_rec);
184 END null_out_defaults;
185 ---------------------------------------------------
186 PROCEDURE Validate_Id(x_return_status OUT NOCOPY VARCHAR2,
187 p_cgcv_rec IN cgcv_rec_type) IS
188 BEGIN
189 x_return_status := OKC_API.G_RET_STS_SUCCESS;
190 IF p_cgcv_rec.id = OKC_API.G_MISS_NUM OR
191 p_cgcv_rec.id IS NULL
192 THEN
193 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'id');
194 x_return_status := OKC_API.G_RET_STS_ERROR;
195 raise G_EXCEPTION_HALT_VALIDATION;
196 END IF;
197 EXCEPTION
198 WHEN G_EXCEPTION_HALT_VALIDATION THEN
199 NULL;
200 WHEN OTHERS THEN
201 OKC_API.set_message(p_app_name => g_app_name,
202 p_msg_name => g_unexpected_error,
203 p_token1 => g_sqlcode_token,
204 p_token1_value => sqlcode,
205 p_token2 => g_sqlerrm_token,
206 p_token2_value => sqlerrm);
207 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
208 END Validate_Id;
209 ---------------------------------------------------------------------------
210 PROCEDURE Validate_Obj_Version_Num(x_return_status OUT NOCOPY VARCHAR2,
211 p_cgcv_rec IN cgcv_rec_type) IS
212 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
213 BEGIN
214 x_return_status := OKC_API.G_RET_STS_SUCCESS;
215 IF p_cgcv_rec.object_version_number = OKC_API.G_MISS_NUM OR
216 p_cgcv_rec.object_version_number IS NULL
217 THEN
218 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'object_version_number');
219 x_return_status := OKC_API.G_RET_STS_ERROR;
220 raise G_EXCEPTION_HALT_VALIDATION;
221 END IF;
222 EXCEPTION
223 WHEN G_EXCEPTION_HALT_VALIDATION THEN
224 NULL;
225 WHEN OTHERS THEN
226 OKC_API.set_message(p_app_name => g_app_name,
227 p_msg_name => g_unexpected_error,
228 p_token1 => g_sqlcode_token,
229 p_token1_value => sqlcode,
230 p_token2 => g_sqlerrm_token,
231 p_token2_value => sqlerrm);
232 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
233 END Validate_Obj_Version_Num;
234 ---------------------------------------------------------------------------
235 PROCEDURE Validate_Cgp_Parent_Id(x_return_status OUT NOCOPY VARCHAR2,
236 p_cgcv_rec IN cgcv_rec_type) IS
237 BEGIN
238 x_return_status := OKC_API.G_RET_STS_SUCCESS;
239 IF p_cgcv_rec.cgp_parent_id = OKC_API.G_MISS_NUM OR
240 p_cgcv_rec.cgp_parent_id IS NULL
241 THEN
242 ---OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'cgp_parent_id');
243 OKC_API.SET_MESSAGE('OKC', 'OKC_INVALID_CGP_PARENT_ID');
244 x_return_status := OKC_API.G_RET_STS_ERROR;
245 raise G_EXCEPTION_HALT_VALIDATION;
246 END IF;
247 EXCEPTION
248 WHEN G_EXCEPTION_HALT_VALIDATION THEN
249 NULL;
250 WHEN OTHERS THEN
251 OKC_API.set_message(p_app_name => g_app_name,
252 p_msg_name => g_unexpected_error,
253 p_token1 => g_sqlcode_token,
254 p_token1_value => sqlcode,
255 p_token2 => g_sqlerrm_token,
256 p_token2_value => sqlerrm);
257 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
258 END Validate_Cgp_Parent_Id;
259 ---------------------------------------------------------------------------
260 ---------------------------------------------------------------------------
261 -- PROCEDURE Validate_Attributes
262 ---------------------------------------------------------------------------
263 ----------------------------------------------------
264 -- Validate_Attributes for:OKC_K_GRPINGS_V --
265 ----------------------------------------------------
266 FUNCTION Validate_Attributes (
267 p_cgcv_rec IN cgcv_rec_type
268 ) RETURN VARCHAR2 IS
269 x_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
270 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
271 BEGIN
272 Validate_Obj_Version_Num(l_return_status, p_cgcv_rec);
273 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
274 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
275 x_return_status := l_return_status;
276 raise G_EXCEPTION_HALT_VALIDATION;
277 ELSE
278 x_return_status := l_return_status;
279 END IF;
280 END IF;
281 Validate_Cgp_Parent_Id(l_return_status, p_cgcv_rec);
282 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
283 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
284 x_return_status := l_return_status;
285 raise G_EXCEPTION_HALT_VALIDATION;
286 ELSE
287 x_return_status := l_return_status;
288 END IF;
289 END IF;
290 RETURN(x_return_status);
291 EXCEPTION
292 WHEN G_EXCEPTION_HALT_VALIDATION THEN
293 RETURN(x_return_status);
294 WHEN OTHERS THEN
295 OKC_API.set_message(p_app_name => g_app_name,
296 p_msg_name => g_unexpected_error,
297 p_token1 => g_sqlcode_token,
298 p_token1_value => sqlcode,
299 p_token2 => g_sqlerrm_token,
300 p_token2_value => sqlerrm);
301 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
302 RETURN(x_return_status);
303 END Validate_Attributes;
304
305 ---------------------------------------------------------------------------
306 -- PROCEDURE Validate_Record
307 ---------------------------------------------------------------------------
308 ------------------------------------------------
309 -- Validate_Record for:OKC_K_GRPINGS_V --
310 ------------------------------------------------
311 FUNCTION Validate_Record (
312 p_cgcv_rec IN cgcv_rec_type
313 ) RETURN VARCHAR2 IS
314 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
315 ------------------------------------
316 -- FUNCTION validate_foreign_keys --
317 ------------------------------------
318 FUNCTION validate_foreign_keys (
319 p_cgcv_rec IN cgcv_rec_type
320 ) RETURN VARCHAR2 IS
321 item_not_found_error EXCEPTION;
322 CURSOR okc_cgpv_pk_csr (p_id IN NUMBER) IS
323 SELECT 'x'
324 FROM Okc_K_Groups_V
325 WHERE okc_K_groups_v.id = p_id;
326 CURSOR okc_chrv_pk_csr (p_id IN NUMBER) IS
327 SELECT 'x'
328 FROM Okc_K_Headers_V
329 WHERE okc_k_headers_v.id = p_id;
330 l_okc_cgpv_dummy VARCHAR2(1);
331 l_okc_chrv_dummy VARCHAR2(1);
332 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
333 l_row_notfound BOOLEAN := TRUE;
334 BEGIN
335 IF (p_cgcv_rec.CGP_PARENT_ID IS NOT NULL)
336 THEN
337 OPEN okc_cgpv_pk_csr(p_cgcv_rec.CGP_PARENT_ID);
338 FETCH okc_cgpv_pk_csr INTO l_okc_cgpv_dummy;
339 l_row_notfound := okc_cgpv_pk_csr%NOTFOUND;
340 CLOSE okc_cgpv_pk_csr;
341 IF (l_row_notfound) THEN
342 ----OKC_API.set_message(G_APP_NAME, G_INVALID_VALUE, G_COL_NAME_TOKEN, 'CGP_PARENT_ID');
343 OKC_API.SET_MESSAGE('OKC', 'OKC_INVALID_CGP_PARENT_ID');
344 RAISE item_not_found_error;
345 END IF;
346 END IF;
347 IF (p_cgcv_rec.INCLUDED_CGP_ID IS NOT NULL)
348 THEN
349 OPEN okc_cgpv_pk_csr(p_cgcv_rec.INCLUDED_CGP_ID);
350 FETCH okc_cgpv_pk_csr INTO l_okc_cgpv_dummy;
351 l_row_notfound := okc_cgpv_pk_csr%NOTFOUND;
352 CLOSE okc_cgpv_pk_csr;
353 IF (l_row_notfound) THEN
354 OKC_API.SET_MESSAGE('OKC', 'OKC_INVALID_CGP_PARENT_ID');--Bug#2836703
355 ----OKC_API.set_message(G_APP_NAME, G_INVALID_VALUE, G_COL_NAME_TOKEN, 'INCLUDED_CGP_ID');
356 RAISE item_not_found_error;
357 END IF;
358 END IF;
359 IF (p_cgcv_rec.INCLUDED_CHR_ID IS NOT NULL)
360 THEN
361 OPEN okc_chrv_pk_csr(p_cgcv_rec.INCLUDED_CHR_ID);
362 FETCH okc_chrv_pk_csr INTO l_okc_chrv_dummy;
363 l_row_notfound := okc_chrv_pk_csr%NOTFOUND;
364 CLOSE okc_chrv_pk_csr;
365 IF (l_row_notfound) THEN
366 OKC_API.set_message(G_APP_NAME, G_INVALID_VALUE, G_COL_NAME_TOKEN, 'INCLUDED_CHR_ID');
367 RAISE item_not_found_error;
368 END IF;
369 END IF;
370 RETURN (l_return_status);
371 EXCEPTION
372 WHEN item_not_found_error THEN
373 l_return_status := OKC_API.G_RET_STS_ERROR;
374 RETURN (l_return_status);
375 END validate_foreign_keys;
376 ----------------------------------------------------
377 FUNCTION validate_unique_keys (
378 p_cgcv_rec IN cgcv_rec_type
379 ) RETURN VARCHAR2 IS
380 unique_key_error EXCEPTION;
381 CURSOR okc_chr_csr (p_id IN okc_k_grpings_v.id%TYPE,
382 p_cgp_parent_id IN okc_k_grpings_v.cgp_parent_id%TYPE,
383 p_included_chr_id IN okc_k_grpings_v.included_chr_id%TYPE) IS
384 SELECT 'x'
385 FROM Okc_K_Grpings_V
386 WHERE okc_K_grpings_v.cgp_parent_id = p_cgp_parent_id
387 AND okc_K_grpings_v.included_chr_id = p_included_chr_id
388 AND ((p_id IS NULL)
389 OR (p_id IS NOT NULL
390 AND id <> p_id));
391 CURSOR okc_cgp_csr (p_id IN okc_k_grpings_v.id%TYPE,
392 p_cgp_parent_id IN okc_k_grpings_v.cgp_parent_id%TYPE,
393 p_included_cgp_id IN okc_k_grpings_v.included_cgp_id%TYPE) IS
394 SELECT 'x'
395 FROM Okc_K_Grpings_V
396 WHERE okc_K_grpings_v.cgp_parent_id = p_cgp_parent_id
397 AND okc_K_grpings_v.included_cgp_id = p_included_cgp_id
398 AND ((p_id IS NULL)
399 OR (p_id IS NOT NULL
400 AND id <> p_id));
401 l_okc_cgp_dummy VARCHAR2(1);
402 l_okc_chr_dummy VARCHAR2(1);
403 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
404 l_row_found BOOLEAN := FALSE;
405 BEGIN
406 IF (p_cgcv_rec.INCLUDED_CHR_ID IS NOT NULL) THEN
407 OPEN okc_chr_csr(p_cgcv_rec.ID,
408 p_cgcv_rec.CGP_PARENT_ID,
409 p_cgcv_rec.INCLUDED_CHR_ID);
410 FETCH okc_chr_csr INTO l_okc_chr_dummy;
411 l_row_found := okc_chr_csr%FOUND;
412 CLOSE okc_chr_csr;
413 IF (l_row_found) THEN
414 OKC_API.set_message(G_APP_NAME, G_INVALID_VALUE, G_COL_NAME_TOKEN, 'INCLUDED_CHR_ID');
415 RAISE unique_key_error;
416 END IF;
417 END IF;
418 IF (p_cgcv_rec.INCLUDED_CGP_ID IS NOT NULL) THEN
419 OPEN okc_cgp_csr(p_cgcv_rec.ID,
420 p_cgcv_rec.CGP_PARENT_ID,
421 p_cgcv_rec.INCLUDED_CGP_ID);
422 FETCH okc_cgp_csr INTO l_okc_cgp_dummy;
423 l_row_found := okc_cgp_csr%FOUND;
424 CLOSE okc_cgp_csr;
425 IF (l_row_found) THEN
426 OKC_API.set_message(G_APP_NAME, G_INVALID_VALUE, G_COL_NAME_TOKEN, 'INCLUDED_CGP_ID');
427 RAISE unique_key_error;
428 END IF;
429 END IF;
430 RETURN (l_return_status);
431 EXCEPTION
432 WHEN unique_key_error THEN
433 l_return_status := OKC_API.G_RET_STS_ERROR;
434 RETURN (l_return_status);
435 END validate_unique_keys;
436 BEGIN
437 IF (p_cgcv_rec.included_chr_id = OKC_API.G_MISS_NUM OR
438 p_cgcv_rec.included_chr_id IS NULL) AND
439 (p_cgcv_rec.included_cgp_id = OKC_API.G_MISS_NUM OR
440 p_cgcv_rec.included_cgp_id IS NULL) THEN
441 OKC_API.set_message(G_APP_NAME, 'OKC_ARC_MANDATORY', 'G_COL_NAME1', 'INCLUDED_CGP_ID',
442 'G_COL_NAME2', 'INCLUDED_CHR_ID');
443 l_return_status := OKC_API.G_RET_STS_ERROR;
444 raise G_EXCEPTION_HALT_VALIDATION;
445 END IF;
446 IF (p_cgcv_rec.included_chr_id IS NOT NULL) AND
447 (p_cgcv_rec.included_cgp_id IS NOT NULL) THEN
448 OKC_API.set_message(G_APP_NAME, 'OKC_ARC_VIOLATED', 'G_COL_NAME1', 'INCLUDED_CGP_ID',
449 'G_COL_NAME2', 'INCLUDED_CHR_ID');
450 l_return_status := OKC_API.G_RET_STS_ERROR;
451 raise G_EXCEPTION_HALT_VALIDATION;
452 END IF;
453 l_return_status := validate_foreign_keys (p_cgcv_rec);
454 IF l_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
455 raise G_EXCEPTION_HALT_VALIDATION;
456 END IF;
457 l_return_status := validate_unique_keys (p_cgcv_rec);
458 IF l_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
459 raise G_EXCEPTION_HALT_VALIDATION;
460 END IF;
461 RETURN (l_return_status);
462 EXCEPTION
463 WHEN G_EXCEPTION_HALT_VALIDATION THEN
464 RETURN(l_return_status);
465 WHEN OTHERS THEN
466 OKC_API.set_message(p_app_name => g_app_name,
467 p_msg_name => g_unexpected_error,
468 p_token1 => g_sqlcode_token,
469 p_token1_value => sqlcode,
470 p_token2 => g_sqlerrm_token,
471 p_token2_value => sqlerrm);
472 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
473 RETURN(l_return_status);
474 END Validate_Record;
475
476 ---------------------------------------------------------------------------
477 -- PROCEDURE Populate_Subclass
478 ---------------------------------------------------------------------------
479 PROCEDURE Populate_Subclass(p_cgcv_rec IN OUT NOCOPY cgcv_rec_type,
480 x_return_status OUT NOCOPY Varchar2) IS
481 cursor c1(p_id okc_k_headers_b.id%TYPE) is
482 select scs_code
483 from okc_k_headers_b
484 where id = p_id;
485 BEGIN
486 x_return_status := OKC_API.G_RET_STS_SUCCESS;
487 If p_cgcv_rec.included_chr_id Is Not Null Then
488 If p_cgcv_rec.scs_code Is Null Then
489 Open c1(p_cgcv_rec.included_chr_id);
490 Fetch c1 Into p_cgcv_rec.scs_code;
491 Close c1;
492 End If;
493 End If;
494 EXCEPTION
495 WHEN OTHERS THEN
496 OKC_API.set_message(p_app_name => g_app_name,
497 p_msg_name => g_unexpected_error,
498 p_token1 => g_sqlcode_token,
499 p_token1_value => sqlcode,
500 p_token2 => g_sqlerrm_token,
501 p_token2_value => sqlerrm);
502 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
503 END Populate_Subclass;
504
505 ---------------------------------------------------------------------------
506 -- PROCEDURE Migrate
507 ---------------------------------------------------------------------------
508 PROCEDURE migrate (
509 p_from IN cgcv_rec_type,
510 p_to OUT NOCOPY cgc_rec_type
511 ) IS
512 BEGIN
513 p_to.id := p_from.id;
514 p_to.cgp_parent_id := p_from.cgp_parent_id;
515 p_to.included_cgp_id := p_from.included_cgp_id;
516 p_to.included_chr_id := p_from.included_chr_id;
517 p_to.scs_code := p_from.scs_code;
518 p_to.object_version_number := p_from.object_version_number;
519 p_to.created_by := p_from.created_by;
520 p_to.creation_date := p_from.creation_date;
521 p_to.last_updated_by := p_from.last_updated_by;
522 p_to.last_update_date := p_from.last_update_date;
523 p_to.last_update_login := p_from.last_update_login;
524 END migrate;
525 PROCEDURE migrate (
526 p_from IN cgc_rec_type,
527 p_to IN OUT NOCOPY cgcv_rec_type
528 ) IS
529 BEGIN
530 p_to.id := p_from.id;
531 p_to.cgp_parent_id := p_from.cgp_parent_id;
532 p_to.included_cgp_id := p_from.included_cgp_id;
533 p_to.included_chr_id := p_from.included_chr_id;
534 p_to.scs_code := p_from.scs_code;
535 p_to.object_version_number := p_from.object_version_number;
536 p_to.created_by := p_from.created_by;
537 p_to.creation_date := p_from.creation_date;
538 p_to.last_updated_by := p_from.last_updated_by;
539 p_to.last_update_date := p_from.last_update_date;
540 p_to.last_update_login := p_from.last_update_login;
541 END migrate;
542
543 ---------------------------------------------------------------------------
544 -- PROCEDURE validate_row
545 ---------------------------------------------------------------------------
546 ---------------------------------------------
547 -- validate_row for:OKC_K_GRPINGS_V --
548 ---------------------------------------------
549 PROCEDURE validate_row(
550 p_api_version IN NUMBER,
551 p_init_msg_list IN VARCHAR2 ,
552 x_return_status OUT NOCOPY VARCHAR2,
553 x_msg_count OUT NOCOPY NUMBER,
554 x_msg_data OUT NOCOPY VARCHAR2,
555 p_cgcv_rec IN cgcv_rec_type) IS
556
557 l_api_version CONSTANT NUMBER := 1;
558 l_api_name CONSTANT VARCHAR2(30) := 'V_validate_row';
559 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
560 l_cgcv_rec cgcv_rec_type := p_cgcv_rec;
561 l_cgc_rec cgc_rec_type;
562 BEGIN
563 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
564 G_PKG_NAME,
565 p_init_msg_list,
566 l_api_version,
567 p_api_version,
568 '_PVT',
569 x_return_status);
570 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
571 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
572 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
573 RAISE OKC_API.G_EXCEPTION_ERROR;
574 END IF;
575 --- Validate all non-missing attributes (Item Level Validation)
576 l_return_status := Validate_Attributes(l_cgcv_rec);
577 --- If any errors happen abort API
578 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
579 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
580 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
581 RAISE OKC_API.G_EXCEPTION_ERROR;
582 END IF;
583 l_return_status := Validate_Record(l_cgcv_rec);
584 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
585 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
586 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
587 RAISE OKC_API.G_EXCEPTION_ERROR;
588 END IF;
589 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
590 EXCEPTION
591 WHEN OKC_API.G_EXCEPTION_ERROR THEN
592 x_return_status := OKC_API.HANDLE_EXCEPTIONS
593 (
594 l_api_name,
595 G_PKG_NAME,
596 'OKC_API.G_RET_STS_ERROR',
597 x_msg_count,
598 x_msg_data,
599 '_PVT'
600 );
601 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
602 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
603 (
604 l_api_name,
605 G_PKG_NAME,
606 'OKC_API.G_RET_STS_UNEXP_ERROR',
607 x_msg_count,
608 x_msg_data,
609 '_PVT'
610 );
611 WHEN OTHERS THEN
612 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
613 (
614 l_api_name,
615 G_PKG_NAME,
616 'OTHERS',
617 x_msg_count,
618 x_msg_data,
619 '_PVT'
620 );
621 END validate_row;
622 ------------------------------------------
623 -- PL/SQL TBL validate_row for:CGCV_TBL --
624 ------------------------------------------
625 PROCEDURE validate_row(
626 p_api_version IN NUMBER,
627 p_init_msg_list IN VARCHAR2 ,
628 x_return_status OUT NOCOPY VARCHAR2,
629 x_msg_count OUT NOCOPY NUMBER,
630 x_msg_data OUT NOCOPY VARCHAR2,
631 p_cgcv_tbl IN cgcv_tbl_type) IS
632
633 l_api_version CONSTANT NUMBER := 1;
634 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_validate_row';
635 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
636 i NUMBER := 0;
637 BEGIN
638 OKC_API.init_msg_list(p_init_msg_list);
639 -- Make sure PL/SQL table has records in it before passing
640 IF (p_cgcv_tbl.COUNT > 0) THEN
641 i := p_cgcv_tbl.FIRST;
642 LOOP
643 validate_row (
644 p_api_version => p_api_version,
645 p_init_msg_list => p_init_msg_list,
646 x_return_status => x_return_status,
647 x_msg_count => x_msg_count,
648 x_msg_data => x_msg_data,
649 p_cgcv_rec => p_cgcv_tbl(i));
650 EXIT WHEN (i = p_cgcv_tbl.LAST);
651 i := p_cgcv_tbl.NEXT(i);
652 END LOOP;
653 END IF;
654 EXCEPTION
655 WHEN OKC_API.G_EXCEPTION_ERROR THEN
656 x_return_status := OKC_API.HANDLE_EXCEPTIONS
657 (
658 l_api_name,
659 G_PKG_NAME,
660 'OKC_API.G_RET_STS_ERROR',
661 x_msg_count,
662 x_msg_data,
663 '_PVT'
664 );
665 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
666 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
667 (
668 l_api_name,
669 G_PKG_NAME,
670 'OKC_API.G_RET_STS_UNEXP_ERROR',
671 x_msg_count,
672 x_msg_data,
673 '_PVT'
674 );
675 WHEN OTHERS THEN
676 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
677 (
678 l_api_name,
679 G_PKG_NAME,
680 'OTHERS',
681 x_msg_count,
682 x_msg_data,
683 '_PVT'
684 );
685 END validate_row;
686
687 ---------------------------------------------------------------------------
688 -- PROCEDURE insert_row
689 ---------------------------------------------------------------------------
690 -----------------------------------------
691 -- insert_row for:OKC_K_GRPINGS --
692 -----------------------------------------
693 PROCEDURE insert_row(
694 p_init_msg_list IN VARCHAR2 ,
695 x_return_status OUT NOCOPY VARCHAR2,
696 x_msg_count OUT NOCOPY NUMBER,
697 x_msg_data OUT NOCOPY VARCHAR2,
698 p_cgc_rec IN cgc_rec_type,
699 x_cgc_rec OUT NOCOPY cgc_rec_type) IS
700
701 l_api_version CONSTANT NUMBER := 1;
702 l_api_name CONSTANT VARCHAR2(30) := 'GRPINGS_insert_row';
703 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
704 l_cgc_rec cgc_rec_type := p_cgc_rec;
705 l_def_cgc_rec cgc_rec_type;
706 ---------------------------------------------
707 -- Set_Attributes for:OKC_K_GRPINGS --
708 ---------------------------------------------
709 FUNCTION Set_Attributes (
710 p_cgc_rec IN cgc_rec_type,
711 x_cgc_rec OUT NOCOPY cgc_rec_type
712 ) RETURN VARCHAR2 IS
713 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
714 BEGIN
715 x_cgc_rec := p_cgc_rec;
716 RETURN(l_return_status);
717 END Set_Attributes;
718 BEGIN
719 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
720 p_init_msg_list,
721 '_PVT',
722 x_return_status);
723 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
724 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
725 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
726 RAISE OKC_API.G_EXCEPTION_ERROR;
727 END IF;
728 --- Setting item attributes
729 l_return_status := Set_Attributes(
730 p_cgc_rec, -- IN
731 l_cgc_rec); -- OUT
732 --- If any errors happen abort API
733 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
734 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
735 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
736 RAISE OKC_API.G_EXCEPTION_ERROR;
737 END IF;
738 INSERT INTO OKC_K_GRPINGS(
739 id,
740 cgp_parent_id,
741 included_cgp_id,
742 included_chr_id,
743 scs_code,
744 object_version_number,
745 created_by,
746 creation_date,
747 last_updated_by,
748 last_update_date,
749 last_update_login)
750 VALUES (
751 l_cgc_rec.id,
752 l_cgc_rec.cgp_parent_id,
753 l_cgc_rec.included_cgp_id,
754 l_cgc_rec.included_chr_id,
755 l_cgc_rec.scs_code,
756 l_cgc_rec.object_version_number,
757 l_cgc_rec.created_by,
758 l_cgc_rec.creation_date,
759 l_cgc_rec.last_updated_by,
760 l_cgc_rec.last_update_date,
761 l_cgc_rec.last_update_login);
762 -- Set OUT values
763 x_cgc_rec := l_cgc_rec;
764 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
765 EXCEPTION
766 WHEN OKC_API.G_EXCEPTION_ERROR THEN
767 x_return_status := OKC_API.HANDLE_EXCEPTIONS
768 (
769 l_api_name,
770 G_PKG_NAME,
771 'OKC_API.G_RET_STS_ERROR',
772 x_msg_count,
773 x_msg_data,
774 '_PVT'
775 );
776 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
777 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
778 (
779 l_api_name,
780 G_PKG_NAME,
781 'OKC_API.G_RET_STS_UNEXP_ERROR',
782 x_msg_count,
783 x_msg_data,
784 '_PVT'
785 );
786 WHEN OTHERS THEN
787 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
788 (
789 l_api_name,
790 G_PKG_NAME,
791 'OTHERS',
792 x_msg_count,
793 x_msg_data,
794 '_PVT'
795 );
796 END insert_row;
797 -------------------------------------------
798 -- insert_row for:OKC_K_GRPINGS_V --
799 -------------------------------------------
800 PROCEDURE insert_row(
801 p_api_version IN NUMBER,
802 p_init_msg_list IN VARCHAR2 ,
803 x_return_status OUT NOCOPY VARCHAR2,
804 x_msg_count OUT NOCOPY NUMBER,
805 x_msg_data OUT NOCOPY VARCHAR2,
806 p_cgcv_rec IN cgcv_rec_type,
807 x_cgcv_rec OUT NOCOPY cgcv_rec_type) IS
808
809 l_api_version CONSTANT NUMBER := 1;
810 l_api_name CONSTANT VARCHAR2(30) := 'V_insert_row';
811 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
812 l_cgcv_rec cgcv_rec_type;
813 l_def_cgcv_rec cgcv_rec_type;
814 l_cgc_rec cgc_rec_type;
815 lx_cgc_rec cgc_rec_type;
816 -------------------------------
817 -- FUNCTION fill_who_columns --
818 -------------------------------
819 FUNCTION fill_who_columns (
820 p_cgcv_rec IN cgcv_rec_type
821 ) RETURN cgcv_rec_type IS
822 l_cgcv_rec cgcv_rec_type := p_cgcv_rec;
823 BEGIN
824 l_cgcv_rec.CREATION_DATE := SYSDATE;
825 l_cgcv_rec.CREATED_BY := FND_GLOBAL.USER_ID;
826 l_cgcv_rec.LAST_UPDATE_DATE := l_cgcv_rec.CREATION_DATE;
827 l_cgcv_rec.LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
828 l_cgcv_rec.LAST_UPDATE_LOGIN := FND_GLOBAL.LOGIN_ID;
829 RETURN(l_cgcv_rec);
830 END fill_who_columns;
831 -----------------------------------------------
832 -- Set_Attributes for:OKC_K_GRPINGS_V --
833 -----------------------------------------------
834 FUNCTION Set_Attributes (
835 p_cgcv_rec IN cgcv_rec_type,
836 x_cgcv_rec OUT NOCOPY cgcv_rec_type
837 ) RETURN VARCHAR2 IS
838 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
839 BEGIN
840 x_cgcv_rec := p_cgcv_rec;
841 x_cgcv_rec.OBJECT_VERSION_NUMBER := 1;
842 RETURN(l_return_status);
843 END Set_Attributes;
844 BEGIN
845 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
846 G_PKG_NAME,
847 p_init_msg_list,
848 l_api_version,
849 p_api_version,
850 '_PVT',
851 x_return_status);
852 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
853 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
854 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
855 RAISE OKC_API.G_EXCEPTION_ERROR;
856 END IF;
857 l_cgcv_rec := null_out_defaults(p_cgcv_rec);
858 -- Set primary key value
859 l_cgcv_rec.ID := get_seq_id;
860 --- Setting item attributes
861 l_return_status := Set_Attributes(
862 l_cgcv_rec, -- IN
863 l_def_cgcv_rec); -- OUT
864 --- If any errors happen abort API
865 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
866 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
867 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
868 RAISE OKC_API.G_EXCEPTION_ERROR;
869 END IF;
870 l_def_cgcv_rec := fill_who_columns(l_def_cgcv_rec);
871 --- Validate all non-missing attributes (Item Level Validation)
872 l_return_status := Validate_Attributes(l_def_cgcv_rec);
873 --- If any errors happen abort API
874 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
875 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
876 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
877 RAISE OKC_API.G_EXCEPTION_ERROR;
878 END IF;
879 l_return_status := Validate_Record(l_def_cgcv_rec);
880 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
881 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
882 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
883 RAISE OKC_API.G_EXCEPTION_ERROR;
884 END IF;
885 -- Populate the denormalized scs_code here
886 Populate_Subclass(l_def_cgcv_rec, l_return_status);
887 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
888 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
889 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
890 RAISE OKC_API.G_EXCEPTION_ERROR;
891 END IF;
892 --------------------------------------
893 -- Move VIEW record to "Child" records
894 --------------------------------------
895 migrate(l_def_cgcv_rec, l_cgc_rec);
896 --------------------------------------------
897 -- Call the INSERT_ROW for each child record
898 --------------------------------------------
899 insert_row(
900 p_init_msg_list,
901 x_return_status,
902 x_msg_count,
903 x_msg_data,
904 l_cgc_rec,
905 lx_cgc_rec
906 );
907 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
908 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
909 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
910 RAISE OKC_API.G_EXCEPTION_ERROR;
911 END IF;
912 migrate(lx_cgc_rec, l_def_cgcv_rec);
913 -- Set OUT values
914 x_cgcv_rec := l_def_cgcv_rec;
915 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
916 EXCEPTION
917 WHEN OKC_API.G_EXCEPTION_ERROR THEN
918 x_return_status := OKC_API.HANDLE_EXCEPTIONS
919 (
920 l_api_name,
921 G_PKG_NAME,
922 'OKC_API.G_RET_STS_ERROR',
923 x_msg_count,
924 x_msg_data,
925 '_PVT'
926 );
927 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
928 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
929 (
930 l_api_name,
931 G_PKG_NAME,
932 'OKC_API.G_RET_STS_UNEXP_ERROR',
933 x_msg_count,
934 x_msg_data,
935 '_PVT'
936 );
937 WHEN OTHERS THEN
938 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
939 (
940 l_api_name,
941 G_PKG_NAME,
942 'OTHERS',
943 x_msg_count,
944 x_msg_data,
945 '_PVT'
946 );
947 END insert_row;
948 ----------------------------------------
949 -- PL/SQL TBL insert_row for:CGCV_TBL --
950 ----------------------------------------
951 PROCEDURE insert_row(
952 p_api_version IN NUMBER,
953 p_init_msg_list IN VARCHAR2 ,
954 x_return_status OUT NOCOPY VARCHAR2,
955 x_msg_count OUT NOCOPY NUMBER,
956 x_msg_data OUT NOCOPY VARCHAR2,
957 p_cgcv_tbl IN cgcv_tbl_type,
958 x_cgcv_tbl OUT NOCOPY cgcv_tbl_type) IS
959
960 l_api_version CONSTANT NUMBER := 1;
961 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_insert_row';
962 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
963 i NUMBER := 0;
964 BEGIN
965 OKC_API.init_msg_list(p_init_msg_list);
966 -- Make sure PL/SQL table has records in it before passing
967 IF (p_cgcv_tbl.COUNT > 0) THEN
968 i := p_cgcv_tbl.FIRST;
969 LOOP
970 insert_row (
971 p_api_version => p_api_version,
972 p_init_msg_list => p_init_msg_list,
973 x_return_status => x_return_status,
974 x_msg_count => x_msg_count,
975 x_msg_data => x_msg_data,
976 p_cgcv_rec => p_cgcv_tbl(i),
977 x_cgcv_rec => x_cgcv_tbl(i));
978 EXIT WHEN (i = p_cgcv_tbl.LAST);
979 i := p_cgcv_tbl.NEXT(i);
980 END LOOP;
981 END IF;
982 EXCEPTION
983 WHEN OKC_API.G_EXCEPTION_ERROR THEN
984 x_return_status := OKC_API.HANDLE_EXCEPTIONS
985 (
986 l_api_name,
987 G_PKG_NAME,
988 'OKC_API.G_RET_STS_ERROR',
989 x_msg_count,
990 x_msg_data,
991 '_PVT'
992 );
993 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
994 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
995 (
996 l_api_name,
997 G_PKG_NAME,
998 'OKC_API.G_RET_STS_UNEXP_ERROR',
999 x_msg_count,
1000 x_msg_data,
1001 '_PVT'
1002 );
1003 WHEN OTHERS THEN
1004 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1005 (
1006 l_api_name,
1007 G_PKG_NAME,
1008 'OTHERS',
1009 x_msg_count,
1010 x_msg_data,
1011 '_PVT'
1012 );
1013 END insert_row;
1014
1015 ---------------------------------------------------------------------------
1016 -- PROCEDURE lock_row
1017 ---------------------------------------------------------------------------
1018 ---------------------------------------
1019 -- lock_row for:OKC_K_GRPINGS --
1020 ---------------------------------------
1021 PROCEDURE lock_row(
1022 p_init_msg_list IN VARCHAR2 ,
1023 x_return_status OUT NOCOPY VARCHAR2,
1024 x_msg_count OUT NOCOPY NUMBER,
1025 x_msg_data OUT NOCOPY VARCHAR2,
1026 p_cgc_rec IN cgc_rec_type) IS
1027
1028 E_Resource_Busy EXCEPTION;
1029 PRAGMA EXCEPTION_INIT(E_Resource_Busy, -00054);
1030 CURSOR lock_csr (p_cgc_rec IN cgc_rec_type) IS
1031 SELECT OBJECT_VERSION_NUMBER
1032 FROM OKC_K_GRPINGS
1033 WHERE ID = p_cgc_rec.id
1034 AND OBJECT_VERSION_NUMBER = p_cgc_rec.object_version_number
1035 FOR UPDATE OF OBJECT_VERSION_NUMBER NOWAIT;
1036
1037 CURSOR lchk_csr (p_cgc_rec IN cgc_rec_type) IS
1038 SELECT OBJECT_VERSION_NUMBER
1039 FROM OKC_K_GRPINGS
1040 WHERE ID = p_cgc_rec.id;
1041 l_api_version CONSTANT NUMBER := 1;
1042 l_api_name CONSTANT VARCHAR2(30) := 'GRPINGS_lock_row';
1043 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1044 l_object_version_number OKC_K_GRPINGS.OBJECT_VERSION_NUMBER%TYPE;
1045 lc_object_version_number OKC_K_GRPINGS.OBJECT_VERSION_NUMBER%TYPE;
1046 l_row_notfound BOOLEAN := FALSE;
1047 lc_row_notfound BOOLEAN := FALSE;
1048 BEGIN
1049 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1050 p_init_msg_list,
1051 '_PVT',
1052 x_return_status);
1053 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1054 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1055 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1056 RAISE OKC_API.G_EXCEPTION_ERROR;
1057 END IF;
1058 BEGIN
1059 OPEN lock_csr(p_cgc_rec);
1060 FETCH lock_csr INTO l_object_version_number;
1061 l_row_notfound := lock_csr%NOTFOUND;
1062 CLOSE lock_csr;
1063 EXCEPTION
1064 WHEN E_Resource_Busy THEN
1065 IF (lock_csr%ISOPEN) THEN
1066 CLOSE lock_csr;
1067 END IF;
1068 OKC_API.set_message(G_FND_APP,G_FORM_UNABLE_TO_RESERVE_REC);
1069 RAISE APP_EXCEPTIONS.RECORD_LOCK_EXCEPTION;
1070 END;
1071
1072 IF ( l_row_notfound ) THEN
1073 OPEN lchk_csr(p_cgc_rec);
1074 FETCH lchk_csr INTO lc_object_version_number;
1075 lc_row_notfound := lchk_csr%NOTFOUND;
1076 CLOSE lchk_csr;
1077 END IF;
1078 IF (lc_row_notfound) THEN
1079 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_DELETED);
1080 RAISE OKC_API.G_EXCEPTION_ERROR;
1081 ELSIF lc_object_version_number > p_cgc_rec.object_version_number THEN
1082 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
1083 RAISE OKC_API.G_EXCEPTION_ERROR;
1084 ELSIF lc_object_version_number <> p_cgc_rec.object_version_number THEN
1085 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
1086 RAISE OKC_API.G_EXCEPTION_ERROR;
1087 ELSIF lc_object_version_number = -1 THEN
1088 OKC_API.set_message(G_APP_NAME,G_RECORD_LOGICALLY_DELETED);
1089 RAISE OKC_API.G_EXCEPTION_ERROR;
1090 END IF;
1091 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1092 EXCEPTION
1093 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1094 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1095 (
1096 l_api_name,
1097 G_PKG_NAME,
1098 'OKC_API.G_RET_STS_ERROR',
1099 x_msg_count,
1100 x_msg_data,
1101 '_PVT'
1102 );
1103 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1104 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1105 (
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 '_PVT'
1112 );
1113 WHEN OTHERS THEN
1114 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1115 (
1116 l_api_name,
1117 G_PKG_NAME,
1118 'OTHERS',
1119 x_msg_count,
1120 x_msg_data,
1121 '_PVT'
1122 );
1123 END lock_row;
1124 -----------------------------------------
1125 -- lock_row for:OKC_K_GRPINGS_V --
1126 -----------------------------------------
1127 PROCEDURE lock_row(
1128 p_api_version IN NUMBER,
1129 p_init_msg_list IN VARCHAR2 ,
1130 x_return_status OUT NOCOPY VARCHAR2,
1131 x_msg_count OUT NOCOPY NUMBER,
1132 x_msg_data OUT NOCOPY VARCHAR2,
1133 p_cgcv_rec IN cgcv_rec_type) IS
1134
1135 l_api_version CONSTANT NUMBER := 1;
1136 l_api_name CONSTANT VARCHAR2(30) := 'V_lock_row';
1137 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1138 l_cgc_rec cgc_rec_type;
1139 BEGIN
1140 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1141 G_PKG_NAME,
1142 p_init_msg_list,
1143 l_api_version,
1144 p_api_version,
1145 '_PVT',
1146 x_return_status);
1147 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1148 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1149 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1150 RAISE OKC_API.G_EXCEPTION_ERROR;
1151 END IF;
1152 --------------------------------------
1153 -- Move VIEW record to "Child" records
1154 --------------------------------------
1155 migrate(p_cgcv_rec, l_cgc_rec);
1156 --------------------------------------------
1157 -- Call the LOCK_ROW for each child record
1158 --------------------------------------------
1159 lock_row(
1160 p_init_msg_list,
1161 x_return_status,
1162 x_msg_count,
1163 x_msg_data,
1164 l_cgc_rec
1165 );
1166 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1167 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1168 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
1169 RAISE OKC_API.G_EXCEPTION_ERROR;
1170 END IF;
1171 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1172 EXCEPTION
1173 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1174 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1175 (
1176 l_api_name,
1177 G_PKG_NAME,
1178 'OKC_API.G_RET_STS_ERROR',
1179 x_msg_count,
1180 x_msg_data,
1181 '_PVT'
1182 );
1183 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1184 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1185 (
1186 l_api_name,
1187 G_PKG_NAME,
1188 'OKC_API.G_RET_STS_UNEXP_ERROR',
1189 x_msg_count,
1190 x_msg_data,
1191 '_PVT'
1192 );
1193 WHEN OTHERS THEN
1194 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1195 (
1196 l_api_name,
1197 G_PKG_NAME,
1198 'OTHERS',
1199 x_msg_count,
1200 x_msg_data,
1201 '_PVT'
1202 );
1203 END lock_row;
1204 --------------------------------------
1205 -- PL/SQL TBL lock_row for:CGCV_TBL --
1206 --------------------------------------
1207 PROCEDURE lock_row(
1208 p_api_version IN NUMBER,
1209 p_init_msg_list IN VARCHAR2 ,
1210 x_return_status OUT NOCOPY VARCHAR2,
1211 x_msg_count OUT NOCOPY NUMBER,
1212 x_msg_data OUT NOCOPY VARCHAR2,
1213 p_cgcv_tbl IN cgcv_tbl_type) IS
1214
1215 l_api_version CONSTANT NUMBER := 1;
1216 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_lock_row';
1217 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1218 i NUMBER := 0;
1219 BEGIN
1220 OKC_API.init_msg_list(p_init_msg_list);
1221 -- Make sure PL/SQL table has records in it before passing
1222 IF (p_cgcv_tbl.COUNT > 0) THEN
1223 i := p_cgcv_tbl.FIRST;
1224 LOOP
1225 lock_row (
1226 p_api_version => p_api_version,
1227 p_init_msg_list => p_init_msg_list,
1228 x_return_status => x_return_status,
1229 x_msg_count => x_msg_count,
1230 x_msg_data => x_msg_data,
1231 p_cgcv_rec => p_cgcv_tbl(i));
1232 EXIT WHEN (i = p_cgcv_tbl.LAST);
1233 i := p_cgcv_tbl.NEXT(i);
1234 END LOOP;
1235 END IF;
1236 EXCEPTION
1237 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1238 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1239 (
1240 l_api_name,
1241 G_PKG_NAME,
1242 'OKC_API.G_RET_STS_ERROR',
1243 x_msg_count,
1244 x_msg_data,
1245 '_PVT'
1246 );
1247 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1248 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1249 (
1250 l_api_name,
1251 G_PKG_NAME,
1252 'OKC_API.G_RET_STS_UNEXP_ERROR',
1253 x_msg_count,
1254 x_msg_data,
1255 '_PVT'
1256 );
1257 WHEN OTHERS THEN
1258 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1259 (
1260 l_api_name,
1261 G_PKG_NAME,
1262 'OTHERS',
1263 x_msg_count,
1264 x_msg_data,
1265 '_PVT'
1266 );
1267 END lock_row;
1268
1269 ---------------------------------------------------------------------------
1270 -- PROCEDURE update_row
1271 ---------------------------------------------------------------------------
1272 -----------------------------------------
1273 -- update_row for:OKC_K_GRPINGS --
1274 -----------------------------------------
1275 PROCEDURE update_row(
1276 p_init_msg_list IN VARCHAR2 ,
1277 x_return_status OUT NOCOPY VARCHAR2,
1278 x_msg_count OUT NOCOPY NUMBER,
1279 x_msg_data OUT NOCOPY VARCHAR2,
1280 p_cgc_rec IN cgc_rec_type,
1281 x_cgc_rec OUT NOCOPY cgc_rec_type) IS
1282
1283 l_api_version CONSTANT NUMBER := 1;
1284 l_api_name CONSTANT VARCHAR2(30) := 'GRPINGS_update_row';
1285 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1286 l_cgc_rec cgc_rec_type := p_cgc_rec;
1287 l_def_cgc_rec cgc_rec_type;
1288 l_row_notfound BOOLEAN := TRUE;
1289 ----------------------------------
1290 -- FUNCTION populate_new_record --
1291 ----------------------------------
1292 FUNCTION populate_new_record (
1293 p_cgc_rec IN cgc_rec_type,
1294 x_cgc_rec OUT NOCOPY cgc_rec_type
1295 ) RETURN VARCHAR2 IS
1296 l_cgc_rec cgc_rec_type;
1297 l_row_notfound BOOLEAN := TRUE;
1298 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1299 BEGIN
1300 x_cgc_rec := p_cgc_rec;
1301 -- Get current database values
1302 l_cgc_rec := get_rec(p_cgc_rec, l_row_notfound);
1303 IF (l_row_notfound) THEN
1304 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1305 END IF;
1306 IF (x_cgc_rec.id = OKC_API.G_MISS_NUM)
1307 THEN
1308 x_cgc_rec.id := l_cgc_rec.id;
1309 END IF;
1310 IF (x_cgc_rec.cgp_parent_id = OKC_API.G_MISS_NUM)
1311 THEN
1312 x_cgc_rec.cgp_parent_id := l_cgc_rec.cgp_parent_id;
1313 END IF;
1314 IF (x_cgc_rec.included_cgp_id = OKC_API.G_MISS_NUM)
1315 THEN
1316 x_cgc_rec.included_cgp_id := l_cgc_rec.included_cgp_id;
1317 END IF;
1318 IF (x_cgc_rec.included_chr_id = OKC_API.G_MISS_NUM)
1319 THEN
1320 x_cgc_rec.included_chr_id := l_cgc_rec.included_chr_id;
1321 END IF;
1322 IF (x_cgc_rec.scs_code = OKC_API.G_MISS_CHAR)
1323 THEN
1324 x_cgc_rec.scs_code := l_cgc_rec.scs_code;
1325 END IF;
1326 IF (x_cgc_rec.object_version_number = OKC_API.G_MISS_NUM)
1327 THEN
1328 x_cgc_rec.object_version_number := l_cgc_rec.object_version_number;
1329 END IF;
1330 IF (x_cgc_rec.created_by = OKC_API.G_MISS_NUM)
1331 THEN
1332 x_cgc_rec.created_by := l_cgc_rec.created_by;
1333 END IF;
1334 IF (x_cgc_rec.creation_date = OKC_API.G_MISS_DATE)
1335 THEN
1336 x_cgc_rec.creation_date := l_cgc_rec.creation_date;
1337 END IF;
1338 IF (x_cgc_rec.last_updated_by = OKC_API.G_MISS_NUM)
1339 THEN
1340 x_cgc_rec.last_updated_by := l_cgc_rec.last_updated_by;
1341 END IF;
1342 IF (x_cgc_rec.last_update_date = OKC_API.G_MISS_DATE)
1343 THEN
1344 x_cgc_rec.last_update_date := l_cgc_rec.last_update_date;
1345 END IF;
1346 IF (x_cgc_rec.last_update_login = OKC_API.G_MISS_NUM)
1347 THEN
1348 x_cgc_rec.last_update_login := l_cgc_rec.last_update_login;
1349 END IF;
1350 RETURN(l_return_status);
1351 END populate_new_record;
1352 ---------------------------------------------
1353 -- Set_Attributes for:OKC_K_GRPINGS --
1354 ---------------------------------------------
1355 FUNCTION Set_Attributes (
1356 p_cgc_rec IN cgc_rec_type,
1357 x_cgc_rec OUT NOCOPY cgc_rec_type
1358 ) RETURN VARCHAR2 IS
1359 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1360 BEGIN
1361 x_cgc_rec := p_cgc_rec;
1362 RETURN(l_return_status);
1363 END Set_Attributes;
1364 BEGIN
1365 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1366 p_init_msg_list,
1367 '_PVT',
1368 x_return_status);
1369 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1370 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1371 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1372 RAISE OKC_API.G_EXCEPTION_ERROR;
1373 END IF;
1374 --- Setting item attributes
1375 l_return_status := Set_Attributes(
1376 p_cgc_rec, -- IN
1377 l_cgc_rec); -- OUT
1378 --- If any errors happen abort API
1379 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1380 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1381 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1382 RAISE OKC_API.G_EXCEPTION_ERROR;
1383 END IF;
1384 l_return_status := populate_new_record(l_cgc_rec, l_def_cgc_rec);
1385 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1386 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1387 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1388 RAISE OKC_API.G_EXCEPTION_ERROR;
1389 END IF;
1390 UPDATE OKC_K_GRPINGS
1391 SET CGP_PARENT_ID = l_def_cgc_rec.cgp_parent_id,
1392 INCLUDED_CGP_ID = l_def_cgc_rec.included_cgp_id,
1393 INCLUDED_CHR_ID = l_def_cgc_rec.included_chr_id,
1394 SCS_CODE = l_def_cgc_rec.scs_code,
1395 OBJECT_VERSION_NUMBER = l_def_cgc_rec.object_version_number,
1396 CREATED_BY = l_def_cgc_rec.created_by,
1397 CREATION_DATE = l_def_cgc_rec.creation_date,
1398 LAST_UPDATED_BY = l_def_cgc_rec.last_updated_by,
1399 LAST_UPDATE_DATE = l_def_cgc_rec.last_update_date,
1400 LAST_UPDATE_LOGIN = l_def_cgc_rec.last_update_login
1401 WHERE ID = l_def_cgc_rec.id;
1402
1403 x_cgc_rec := l_def_cgc_rec;
1404 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1405 EXCEPTION
1406 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1407 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1408 (
1409 l_api_name,
1410 G_PKG_NAME,
1411 'OKC_API.G_RET_STS_ERROR',
1412 x_msg_count,
1413 x_msg_data,
1414 '_PVT'
1415 );
1416 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1417 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1418 (
1419 l_api_name,
1420 G_PKG_NAME,
1421 'OKC_API.G_RET_STS_UNEXP_ERROR',
1422 x_msg_count,
1423 x_msg_data,
1424 '_PVT'
1425 );
1426 WHEN OTHERS THEN
1427 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1428 (
1429 l_api_name,
1430 G_PKG_NAME,
1431 'OTHERS',
1432 x_msg_count,
1433 x_msg_data,
1434 '_PVT'
1435 );
1436 END update_row;
1437 -------------------------------------------
1438 -- update_row for:OKC_K_GRPINGS_V --
1439 -------------------------------------------
1440 PROCEDURE update_row(
1441 p_api_version IN NUMBER,
1442 p_init_msg_list IN VARCHAR2 ,
1443 x_return_status OUT NOCOPY VARCHAR2,
1444 x_msg_count OUT NOCOPY NUMBER,
1445 x_msg_data OUT NOCOPY VARCHAR2,
1446 p_cgcv_rec IN cgcv_rec_type,
1447 x_cgcv_rec OUT NOCOPY cgcv_rec_type) IS
1448
1449 l_api_version CONSTANT NUMBER := 1;
1450 l_api_name CONSTANT VARCHAR2(30) := 'V_update_row';
1451 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1452 l_cgcv_rec cgcv_rec_type := p_cgcv_rec;
1453 l_def_cgcv_rec cgcv_rec_type;
1454 l_cgc_rec cgc_rec_type;
1455 lx_cgc_rec cgc_rec_type;
1456 -------------------------------
1457 -- FUNCTION fill_who_columns --
1458 -------------------------------
1459 FUNCTION fill_who_columns (
1460 p_cgcv_rec IN cgcv_rec_type
1461 ) RETURN cgcv_rec_type IS
1462 l_cgcv_rec cgcv_rec_type := p_cgcv_rec;
1463 BEGIN
1464 l_cgcv_rec.LAST_UPDATE_DATE := SYSDATE;
1465 l_cgcv_rec.LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
1466 l_cgcv_rec.LAST_UPDATE_LOGIN := FND_GLOBAL.LOGIN_ID;
1467 RETURN(l_cgcv_rec);
1468 END fill_who_columns;
1469 ----------------------------------
1470 -- FUNCTION populate_new_record --
1471 ----------------------------------
1472 FUNCTION populate_new_record (
1473 p_cgcv_rec IN cgcv_rec_type,
1474 x_cgcv_rec OUT NOCOPY cgcv_rec_type
1475 ) RETURN VARCHAR2 IS
1476 l_cgcv_rec cgcv_rec_type;
1477 l_row_notfound BOOLEAN := TRUE;
1478 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1479 BEGIN
1480 x_cgcv_rec := p_cgcv_rec;
1481 -- Get current database values
1482 l_cgcv_rec := get_rec(p_cgcv_rec, l_row_notfound);
1483 IF (l_row_notfound) THEN
1484 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1485 END IF;
1486 IF (x_cgcv_rec.id = OKC_API.G_MISS_NUM)
1487 THEN
1488 x_cgcv_rec.id := l_cgcv_rec.id;
1489 END IF;
1490 IF (x_cgcv_rec.object_version_number = OKC_API.G_MISS_NUM)
1491 THEN
1492 x_cgcv_rec.object_version_number := l_cgcv_rec.object_version_number;
1493 END IF;
1494 IF (x_cgcv_rec.cgp_parent_id = OKC_API.G_MISS_NUM)
1495 THEN
1496 x_cgcv_rec.cgp_parent_id := l_cgcv_rec.cgp_parent_id;
1497 END IF;
1498 IF (x_cgcv_rec.included_chr_id = OKC_API.G_MISS_NUM)
1499 THEN
1500 x_cgcv_rec.included_chr_id := l_cgcv_rec.included_chr_id;
1501 END IF;
1502 IF (x_cgcv_rec.included_cgp_id = OKC_API.G_MISS_NUM)
1503 THEN
1504 x_cgcv_rec.included_cgp_id := l_cgcv_rec.included_cgp_id;
1505 END IF;
1506 IF (x_cgcv_rec.scs_code = OKC_API.G_MISS_CHAR)
1507 THEN
1508 x_cgcv_rec.scs_code := l_cgcv_rec.scs_code;
1509 END IF;
1510 IF (x_cgcv_rec.created_by = OKC_API.G_MISS_NUM)
1511 THEN
1512 x_cgcv_rec.created_by := l_cgcv_rec.created_by;
1513 END IF;
1514 IF (x_cgcv_rec.creation_date = OKC_API.G_MISS_DATE)
1515 THEN
1516 x_cgcv_rec.creation_date := l_cgcv_rec.creation_date;
1517 END IF;
1518 IF (x_cgcv_rec.last_updated_by = OKC_API.G_MISS_NUM)
1519 THEN
1520 x_cgcv_rec.last_updated_by := l_cgcv_rec.last_updated_by;
1521 END IF;
1522 IF (x_cgcv_rec.last_update_date = OKC_API.G_MISS_DATE)
1523 THEN
1524 x_cgcv_rec.last_update_date := l_cgcv_rec.last_update_date;
1525 END IF;
1526 IF (x_cgcv_rec.last_update_login = OKC_API.G_MISS_NUM)
1527 THEN
1528 x_cgcv_rec.last_update_login := l_cgcv_rec.last_update_login;
1529 END IF;
1530 RETURN(l_return_status);
1531 END populate_new_record;
1532 -----------------------------------------------
1533 -- Set_Attributes for:OKC_K_GRPINGS_V --
1534 -----------------------------------------------
1535 FUNCTION Set_Attributes (
1536 p_cgcv_rec IN cgcv_rec_type,
1537 x_cgcv_rec OUT NOCOPY cgcv_rec_type
1538 ) RETURN VARCHAR2 IS
1539 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1540 BEGIN
1541 x_cgcv_rec := p_cgcv_rec;
1542 x_cgcv_rec.OBJECT_VERSION_NUMBER := NVL(x_cgcv_rec.OBJECT_VERSION_NUMBER, 0) + 1;
1543 RETURN(l_return_status);
1544 END Set_Attributes;
1545 BEGIN
1546 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1547 G_PKG_NAME,
1548 p_init_msg_list,
1549 l_api_version,
1550 p_api_version,
1551 '_PVT',
1552 x_return_status);
1553 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1554 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1555 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1556 RAISE OKC_API.G_EXCEPTION_ERROR;
1557 END IF;
1558 --- Setting item attributes
1559 l_return_status := Set_Attributes(
1560 p_cgcv_rec, -- IN
1561 l_cgcv_rec); -- OUT
1562 --- If any errors happen abort API
1563 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1564 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1565 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1566 RAISE OKC_API.G_EXCEPTION_ERROR;
1567 END IF;
1568 l_return_status := populate_new_record(l_cgcv_rec, l_def_cgcv_rec);
1569 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1570 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1571 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1572 RAISE OKC_API.G_EXCEPTION_ERROR;
1573 END IF;
1574 l_def_cgcv_rec := fill_who_columns(l_def_cgcv_rec);
1575 --- Validate all non-missing attributes (Item Level Validation)
1576 l_return_status := Validate_Attributes(l_def_cgcv_rec);
1577 --- If any errors happen abort API
1578 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1579 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1580 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1581 RAISE OKC_API.G_EXCEPTION_ERROR;
1582 END IF;
1583 l_return_status := Validate_Record(l_def_cgcv_rec);
1584 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1585 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1586 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1587 RAISE OKC_API.G_EXCEPTION_ERROR;
1588 END IF;
1589 -- Populate the denormalized scs_code here
1590 Populate_Subclass(l_def_cgcv_rec, l_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
1597 --------------------------------------
1598 -- Move VIEW record to "Child" records
1599 --------------------------------------
1600 migrate(l_def_cgcv_rec, l_cgc_rec);
1601 --------------------------------------------
1602 -- Call the UPDATE_ROW for each child record
1603 --------------------------------------------
1604 update_row(
1605 p_init_msg_list,
1606 x_return_status,
1607 x_msg_count,
1608 x_msg_data,
1609 l_cgc_rec,
1610 lx_cgc_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 migrate(lx_cgc_rec, l_def_cgcv_rec);
1618 x_cgcv_rec := l_def_cgcv_rec;
1619 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1620 EXCEPTION
1621 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1622 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1623 (
1624 l_api_name,
1625 G_PKG_NAME,
1626 'OKC_API.G_RET_STS_ERROR',
1627 x_msg_count,
1628 x_msg_data,
1629 '_PVT'
1630 );
1631 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1632 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1633 (
1634 l_api_name,
1635 G_PKG_NAME,
1636 'OKC_API.G_RET_STS_UNEXP_ERROR',
1637 x_msg_count,
1638 x_msg_data,
1639 '_PVT'
1640 );
1641 WHEN OTHERS THEN
1642 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1643 (
1644 l_api_name,
1645 G_PKG_NAME,
1646 'OTHERS',
1647 x_msg_count,
1648 x_msg_data,
1649 '_PVT'
1650 );
1651 END update_row;
1652 ----------------------------------------
1653 -- PL/SQL TBL update_row for:CGCV_TBL --
1654 ----------------------------------------
1655 PROCEDURE update_row(
1656 p_api_version IN NUMBER,
1657 p_init_msg_list IN VARCHAR2 ,
1658 x_return_status OUT NOCOPY VARCHAR2,
1659 x_msg_count OUT NOCOPY NUMBER,
1660 x_msg_data OUT NOCOPY VARCHAR2,
1661 p_cgcv_tbl IN cgcv_tbl_type,
1662 x_cgcv_tbl OUT NOCOPY cgcv_tbl_type) IS
1663
1664 l_api_version CONSTANT NUMBER := 1;
1665 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_update_row';
1666 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1667 i NUMBER := 0;
1668 BEGIN
1669 OKC_API.init_msg_list(p_init_msg_list);
1670 -- Make sure PL/SQL table has records in it before passing
1671 IF (p_cgcv_tbl.COUNT > 0) THEN
1672 i := p_cgcv_tbl.FIRST;
1673 LOOP
1674 update_row (
1675 p_api_version => p_api_version,
1676 p_init_msg_list => p_init_msg_list,
1677 x_return_status => x_return_status,
1678 x_msg_count => x_msg_count,
1679 x_msg_data => x_msg_data,
1680 p_cgcv_rec => p_cgcv_tbl(i),
1681 x_cgcv_rec => x_cgcv_tbl(i));
1682 EXIT WHEN (i = p_cgcv_tbl.LAST);
1683 i := p_cgcv_tbl.NEXT(i);
1684 END LOOP;
1685 END IF;
1686 EXCEPTION
1687 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1688 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1689 (
1690 l_api_name,
1691 G_PKG_NAME,
1692 'OKC_API.G_RET_STS_ERROR',
1693 x_msg_count,
1694 x_msg_data,
1695 '_PVT'
1696 );
1697 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1698 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1699 (
1700 l_api_name,
1701 G_PKG_NAME,
1702 'OKC_API.G_RET_STS_UNEXP_ERROR',
1703 x_msg_count,
1704 x_msg_data,
1705 '_PVT'
1706 );
1707 WHEN OTHERS THEN
1708 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1709 (
1710 l_api_name,
1711 G_PKG_NAME,
1712 'OTHERS',
1713 x_msg_count,
1714 x_msg_data,
1715 '_PVT'
1716 );
1717 END update_row;
1718
1719 ---------------------------------------------------------------------------
1720 -- PROCEDURE delete_row
1721 ---------------------------------------------------------------------------
1722 -----------------------------------------
1723 -- delete_row for:OKC_K_GRPINGS --
1724 -----------------------------------------
1725 PROCEDURE delete_row(
1726 p_init_msg_list IN VARCHAR2 ,
1727 x_return_status OUT NOCOPY VARCHAR2,
1728 x_msg_count OUT NOCOPY NUMBER,
1729 x_msg_data OUT NOCOPY VARCHAR2,
1730 p_cgc_rec IN cgc_rec_type) IS
1731
1732 l_api_version CONSTANT NUMBER := 1;
1733 l_api_name CONSTANT VARCHAR2(30) := 'GRPINGS_delete_row';
1734 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1735 l_cgc_rec cgc_rec_type:= p_cgc_rec;
1736 l_row_notfound BOOLEAN := TRUE;
1737 BEGIN
1738 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1739 p_init_msg_list,
1740 '_PVT',
1741 x_return_status);
1742 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1743 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1744 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1745 RAISE OKC_API.G_EXCEPTION_ERROR;
1746 END IF;
1747 DELETE FROM OKC_K_GRPINGS
1748 WHERE ID = l_cgc_rec.id;
1749
1750 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1751 EXCEPTION
1752 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1753 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1754 (
1755 l_api_name,
1756 G_PKG_NAME,
1757 'OKC_API.G_RET_STS_ERROR',
1758 x_msg_count,
1759 x_msg_data,
1760 '_PVT'
1761 );
1762 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1763 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1764 (
1765 l_api_name,
1766 G_PKG_NAME,
1767 'OKC_API.G_RET_STS_UNEXP_ERROR',
1768 x_msg_count,
1769 x_msg_data,
1770 '_PVT'
1771 );
1772 WHEN OTHERS THEN
1773 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1774 (
1775 l_api_name,
1776 G_PKG_NAME,
1777 'OTHERS',
1778 x_msg_count,
1779 x_msg_data,
1780 '_PVT'
1781 );
1782 END delete_row;
1783 -------------------------------------------
1784 -- delete_row for:OKC_K_GRPINGS_V --
1785 -------------------------------------------
1786 PROCEDURE delete_row(
1787 p_api_version IN NUMBER,
1788 p_init_msg_list IN VARCHAR2 ,
1789 x_return_status OUT NOCOPY VARCHAR2,
1790 x_msg_count OUT NOCOPY NUMBER,
1791 x_msg_data OUT NOCOPY VARCHAR2,
1792 p_cgcv_rec IN cgcv_rec_type) IS
1793
1794 l_api_version CONSTANT NUMBER := 1;
1795 l_api_name CONSTANT VARCHAR2(30) := 'V_delete_row';
1796 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1797 l_cgcv_rec cgcv_rec_type := p_cgcv_rec;
1798 l_cgc_rec cgc_rec_type;
1799 BEGIN
1800 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1801 G_PKG_NAME,
1802 p_init_msg_list,
1803 l_api_version,
1804 p_api_version,
1805 '_PVT',
1806 x_return_status);
1807 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1808 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1809 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1810 RAISE OKC_API.G_EXCEPTION_ERROR;
1811 END IF;
1812 --------------------------------------
1813 -- Move VIEW record to "Child" records
1814 --------------------------------------
1815 migrate(l_cgcv_rec, l_cgc_rec);
1816 --------------------------------------------
1817 -- Call the DELETE_ROW for each child record
1818 --------------------------------------------
1819 delete_row(
1820 p_init_msg_list,
1821 x_return_status,
1822 x_msg_count,
1823 x_msg_data,
1824 l_cgc_rec
1825 );
1826 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1827 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1828 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
1829 RAISE OKC_API.G_EXCEPTION_ERROR;
1830 END IF;
1831 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1832 EXCEPTION
1833 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1834 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1835 (
1836 l_api_name,
1837 G_PKG_NAME,
1838 'OKC_API.G_RET_STS_ERROR',
1839 x_msg_count,
1840 x_msg_data,
1841 '_PVT'
1842 );
1843 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1844 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1845 (
1846 l_api_name,
1847 G_PKG_NAME,
1848 'OKC_API.G_RET_STS_UNEXP_ERROR',
1849 x_msg_count,
1850 x_msg_data,
1851 '_PVT'
1852 );
1853 WHEN OTHERS THEN
1854 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1855 (
1856 l_api_name,
1857 G_PKG_NAME,
1858 'OTHERS',
1859 x_msg_count,
1860 x_msg_data,
1861 '_PVT'
1862 );
1863 END delete_row;
1864 ----------------------------------------
1865 -- PL/SQL TBL delete_row for:CGCV_TBL --
1866 ----------------------------------------
1867 PROCEDURE delete_row(
1868 p_api_version IN NUMBER,
1869 p_init_msg_list IN VARCHAR2 ,
1870 x_return_status OUT NOCOPY VARCHAR2,
1871 x_msg_count OUT NOCOPY NUMBER,
1872 x_msg_data OUT NOCOPY VARCHAR2,
1873 p_cgcv_tbl IN cgcv_tbl_type) IS
1874
1875 l_api_version CONSTANT NUMBER := 1;
1876 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_delete_row';
1877 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1878 i NUMBER := 0;
1879 BEGIN
1880 OKC_API.init_msg_list(p_init_msg_list);
1881 -- Make sure PL/SQL table has records in it before passing
1882 IF (p_cgcv_tbl.COUNT > 0) THEN
1883 i := p_cgcv_tbl.FIRST;
1884 LOOP
1885 delete_row (
1886 p_api_version => p_api_version,
1887 p_init_msg_list => p_init_msg_list,
1888 x_return_status => x_return_status,
1889 x_msg_count => x_msg_count,
1890 x_msg_data => x_msg_data,
1891 p_cgcv_rec => p_cgcv_tbl(i));
1892 EXIT WHEN (i = p_cgcv_tbl.LAST);
1893 i := p_cgcv_tbl.NEXT(i);
1894 END LOOP;
1895 END IF;
1896 EXCEPTION
1897 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1898 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1899 (
1900 l_api_name,
1901 G_PKG_NAME,
1902 'OKC_API.G_RET_STS_ERROR',
1903 x_msg_count,
1904 x_msg_data,
1905 '_PVT'
1906 );
1907 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1908 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1909 (
1910 l_api_name,
1911 G_PKG_NAME,
1912 'OKC_API.G_RET_STS_UNEXP_ERROR',
1913 x_msg_count,
1914 x_msg_data,
1915 '_PVT'
1916 );
1917 WHEN OTHERS THEN
1918 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1919 (
1920 l_api_name,
1921 G_PKG_NAME,
1922 'OTHERS',
1923 x_msg_count,
1924 x_msg_data,
1925 '_PVT'
1926 );
1927 END delete_row;
1928
1929 PROCEDURE insert_row_upg(x_return_status OUT NOCOPY VARCHAR2, p_cgcv_tbl IN cgcv_tbl_type) IS
1930 l_tabsize NUMBER := p_cgcv_tbl.COUNT;
1931 in_id OKC_DATATYPES.NumberTabTyp;
1932 in_object_version_number OKC_DATATYPES.NumberTabTyp;
1933 in_cgp_parent_id OKC_DATATYPES.NumberTabTyp;
1934 in_included_chr_id OKC_DATATYPES.NumberTabTyp;
1935 in_included_cgp_id OKC_DATATYPES.NumberTabTyp;
1936 in_scs_code OKC_DATATYPES.Var30TabTyp;
1937 in_created_by OKC_DATATYPES.Number15TabTyp;
1938 in_creation_date OKC_DATATYPES.DateTabTyp;
1939 in_last_updated_by OKC_DATATYPES.Number15TabTyp;
1940 in_last_update_date OKC_DATATYPES.DateTabTyp;
1941 in_last_update_login OKC_DATATYPES.Number15TabTyp;
1942 i NUMBER := p_cgcv_tbl.FIRST;
1943 j NUMBER := 0;
1944
1945 BEGIN
1946 -- Initializing Return status
1947 x_return_status := OKC_API.G_RET_STS_SUCCESS;
1948 -- pkoganti 08/26/2000
1949 -- replace for loop with while loop to handle
1950 -- gaps in pl/sql table indexes.
1951 -- Example:
1952 -- consider a pl/sql table(A) with the following elements
1953 -- A(1) = 10
1954 -- A(2) = 20
1955 -- A(6) = 30
1956 -- A(7) = 40
1957 --
1958 -- The for loop was erroring for indexes 3,4,5, the while loop
1959 -- along with the NEXT operator would handle the missing indexes
1960 -- with out causing the API to fail.
1961 --
1962 WHILE i IS NOT NULL
1963 LOOP
1964 j := j + 1;
1965
1966 in_id(j) := p_cgcv_tbl(i).id;
1967 in_object_version_number(j) := p_cgcv_tbl(i).object_version_number;
1968 in_cgp_parent_id(j) := p_cgcv_tbl(i).cgp_parent_id;
1969 in_included_chr_id(j) := p_cgcv_tbl(i).included_chr_id;
1970 in_included_cgp_id(j) := p_cgcv_tbl(i).included_cgp_id;
1971 in_scs_code(j) := p_cgcv_tbl(i).scs_code;
1972 in_created_by(j) := p_cgcv_tbl(i).created_by;
1973 in_creation_date(j) := p_cgcv_tbl(i).creation_date;
1974 in_last_updated_by(j) := p_cgcv_tbl(i).last_updated_by;
1975 in_last_update_date(j) := p_cgcv_tbl(i).last_update_date;
1976 in_last_update_login(j) := p_cgcv_tbl(i).last_update_login;
1977
1978 i := p_cgcv_tbl.NEXT(i);
1979 END LOOP;
1980
1981 FORALL i in 1..l_tabsize
1982 INSERT INTO OKC_K_GRPINGS (
1983 id,
1984 object_version_number,
1985 cgp_parent_id,
1986 included_chr_id,
1987 included_cgp_id,
1988 scs_code,
1989 created_by,
1990 creation_date,
1991 last_updated_by,
1992 last_update_date,
1993 last_update_login)
1994 VALUES (
1995 in_id(i),
1996 in_object_version_number(i),
1997 in_cgp_parent_id(i),
1998 in_included_chr_id(i),
1999 in_included_cgp_id(i),
2000 in_scs_code(i),
2001 in_created_by(i),
2002 in_creation_date(i),
2003 in_last_updated_by(i),
2004 in_last_update_date(i),
2005 in_last_update_login(i));
2006 EXCEPTION
2007 WHEN OTHERS THEN
2008 -- RAISE;
2009 -- store SQL error message on message stack
2010 OKC_API.SET_MESSAGE(
2011 p_app_name => G_APP_NAME,
2012 p_msg_name => G_UNEXPECTED_ERROR,
2013 p_token1 => G_SQLCODE_TOKEN,
2014 p_token1_value => SQLCODE,
2015 p_token2 => G_SQLERRM_TOKEN,
2016 p_token2_value => SQLERRM);
2017 -- notify caller of an error as UNEXPETED error
2018 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
2019
2020 END;
2021
2022 PROCEDURE Set_Search_String(p_srch_str IN VARCHAR2,
2023 x_return_status OUT NOCOPY VARCHAR2) IS
2024 BEGIN
2025 x_return_status := OKC_API.G_RET_STS_SUCCESS;
2026 g_qry_clause := p_srch_str;
2027 EXCEPTION
2028 WHEN OTHERS THEN
2029 x_return_status := OKC_API.G_RET_STS_ERROR;
2030 END;
2031
2032 PROCEDURE Get_Queried_Contracts(p_cgp_parent_id IN NUMBER,
2033 x_qry_k_tbl OUT NOCOPY qry_k_tbl,
2034 x_return_status OUT NOCOPY VARCHAR2) IS
2035 l_qry Varchar2(2000);
2036 l_qry_k_tbl qry_k_tbl;
2037 l_index Number;
2038 l_id Number;
2039 l_contract_number Varchar2(255);
2040 k_csr QueryKCursor;
2041 BEGIN
2042 x_return_status := OKC_API.G_RET_STS_SUCCESS;
2043 -- dbms_output.put_line('g_qry_clause : ' || g_qry_clause);
2044 IF g_qry_clause IS NOT NULL THEN
2045 l_qry := 'select chrv.id, chrv.contract_number || ' ||
2046 'decode(chrv.contract_number_modifier, null, null,' ||
2047 ''' ('' || chrv.contract_number_modifier || '')'') contract_number' ||
2048 ' from okc_k_headers_v chrv where ' || g_qry_clause ||
2049 ' and chrv.id not in (select included_chr_id from okc_k_grpings' ||
2050 ' where cgp_parent_id = :1' ||
2051 ' and included_chr_id is not null)' ||
2052 ' order by 2';
2053 END IF;
2054 l_index := 1;
2055 OPEN k_csr FOR l_qry USING p_cgp_parent_id ;
2056 LOOP
2057 FETCH k_csr INTO l_id, l_contract_number;
2058 EXIT WHEN k_csr%NOTFOUND;
2059 l_qry_k_tbl(l_index).id := l_id;
2060 l_qry_k_tbl(l_index).contract_number := l_contract_number;
2061 l_index := l_index + 1;
2062 END LOOP;
2063 CLOSE k_csr;
2064 x_qry_k_tbl := l_qry_k_tbl;
2065 EXCEPTION
2066 WHEN OTHERS THEN
2067 OKC_API.set_message(p_app_name => g_app_name,
2068 p_msg_name => g_unexpected_error,
2069 p_token1 => g_sqlcode_token,
2070 p_token1_value => sqlcode,
2071 p_token2 => g_sqlerrm_token,
2072 p_token2_value => sqlerrm);
2073 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
2074 END Get_Queried_Contracts;
2075
2076 END OKC_CGC_PVT;