[Home] [Help]
PACKAGE BODY: APPS.OKS_COD_PVT
Source
1 PACKAGE BODY OKS_COD_PVT AS
2 /* $Header: OKSRCODB.pls 120.2 2006/05/26 23:10:03 jvarghes noship $ */
3 ---------------------------------------------------------------------------
4 -- PROCEDURE load_error_tbl
5 ---------------------------------------------------------------------------
6 PROCEDURE load_error_tbl (
7 px_error_rec IN OUT NOCOPY OKC_API.ERROR_REC_TYPE,
8 px_error_tbl IN OUT NOCOPY OKC_API.ERROR_TBL_TYPE) IS
9
10 j INTEGER := NVL(px_error_tbl.LAST, 0) + 1;
11 last_msg_idx INTEGER := FND_MSG_PUB.COUNT_MSG;
12 l_msg_idx INTEGER := FND_MSG_PUB.G_NEXT;
13 BEGIN
14 -- FND_MSG_PUB has a small error in it. If we call FND_MSG_PUB.COUNT_AND_GET before
15 -- we call FND_MSG_PUB.GET, the variable FND_MSG_PUB uses to control the index of the
16 -- message stack gets set to 1. This makes sense until we call FND_MSG_PUB.GET which
17 -- automatically increments the index by 1, (making it 2), however, when the GET function
18 -- attempts to pull message 2, we get a NO_DATA_FOUND exception because there isn't any
19 -- message 2. To circumvent this problem, check the amount of messages and compensate.
20 -- Again, this error only occurs when 1 message is on the stack because COUNT_AND_GET
21 -- will only update the index variable when 1 and only 1 message is on the stack.
22 IF (last_msg_idx = 1) THEN
23 l_msg_idx := FND_MSG_PUB.G_FIRST;
24 END IF;
25 LOOP
26 fnd_msg_pub.get(
27 p_msg_index => l_msg_idx,
28 p_encoded => fnd_api.g_false,
29 p_data => px_error_rec.msg_data,
30 p_msg_index_out => px_error_rec.msg_count);
31 px_error_tbl(j) := px_error_rec;
32 j := j + 1;
33 EXIT WHEN (px_error_rec.msg_count = last_msg_idx);
34 END LOOP;
35 END load_error_tbl;
36 ---------------------------------------------------------------------------
37 -- FUNCTION find_highest_exception
38 ---------------------------------------------------------------------------
39 -- Finds the highest exception (G_RET_STS_UNEXP_ERROR)
40 -- in a OKC_API.ERROR_TBL_TYPE, and returns it.
41 FUNCTION find_highest_exception(
42 p_error_tbl IN OKC_API.ERROR_TBL_TYPE
43 ) RETURN VARCHAR2 IS
44 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
45 i INTEGER := 1;
46 BEGIN
47 IF (p_error_tbl.COUNT > 0) THEN
48 i := p_error_tbl.FIRST;
49 LOOP
50 IF (p_error_tbl(i).error_type <> OKC_API.G_RET_STS_SUCCESS) THEN
51 IF (l_return_status <> OKC_API.G_RET_STS_UNEXP_ERROR) THEN
52 l_return_status := p_error_tbl(i).error_type;
53 END IF;
54 END IF;
55 EXIT WHEN (i = p_error_tbl.LAST);
56 i := p_error_tbl.NEXT(i);
57 END LOOP;
58 END IF;
59 RETURN(l_return_status);
60 END find_highest_exception;
61 ---------------------------------------------------------------------------
62 -- FUNCTION get_seq_id
63 ---------------------------------------------------------------------------
64 FUNCTION get_seq_id RETURN NUMBER IS
65 BEGIN
66 RETURN(okc_p_util.raw_to_number(sys_guid()));
67 END get_seq_id;
68
69 ---------------------------------------------------------------------------
70 -- PROCEDURE qc
71 ---------------------------------------------------------------------------
72 PROCEDURE qc IS
73 BEGIN
74 null;
75 END qc;
76
77 ---------------------------------------------------------------------------
78 -- PROCEDURE change_version
79 ---------------------------------------------------------------------------
80 PROCEDURE change_version IS
81 BEGIN
82 null;
83 END change_version;
84
85 ---------------------------------------------------------------------------
86 -- PROCEDURE api_copy
87 ---------------------------------------------------------------------------
88 PROCEDURE api_copy IS
89 BEGIN
90 null;
91 END api_copy;
92
93 ---------------------------------------------------------------------------
94 -- FUNCTION get_rec for: OKS_K_ORDER_DETAILS_V
95 ---------------------------------------------------------------------------
96 FUNCTION get_rec (
97 p_codv_rec IN codv_rec_type,
98 x_no_data_found OUT NOCOPY BOOLEAN
99 ) RETURN codv_rec_type IS
100 CURSOR oks_kodv_pk_csr (p_id IN NUMBER) IS
101 SELECT
102 ID,
103 COD_ID,
104 APPLY_ALL_YN,
105 LINE_RENEWAL_TYPE,
106 RENEWAL_TYPE,
107 PO_REQUIRED_YN,
108 RENEWAL_PRICING_TYPE,
109 MARKUP_PERCENT,
110 LINK_ORDER_HEADER_ID,
111 END_DATE,
112 COD_TYPE,
113 ORDER_LINE_ID1,
114 ORDER_LINE_ID2,
115 OBJECT_VERSION_NUMBER,
116 CREATED_BY,
117 CREATION_DATE,
118 LAST_UPDATED_BY,
119 LAST_UPDATE_DATE,
120 LINK_ORD_LINE_ID1,
121 LINK_ORD_LINE_ID2,
122 LINK_CHR_ID,
123 LINK_CLE_ID,
124 PRICE_LIST_ID1,
125 PRICE_LIST_ID2,
126 CHR_ID,
127 CLE_ID,
128 CONTACT_ID,
129 SITE_ID,
130 EMAIL_ID,
131 PHONE_ID,
132 FAX_ID,
133 BILLING_PROFILE_ID,
134 RENEWAL_APPROVAL_FLAG
135 FROM Oks_K_Order_Details_V
136 WHERE oks_k_order_details_v.id = p_id;
137 l_oks_kodv_pk oks_kodv_pk_csr%ROWTYPE;
138 l_codv_rec codv_rec_type;
139 BEGIN
140 x_no_data_found := TRUE;
141 -- Get current database values
142 OPEN oks_kodv_pk_csr (p_codv_rec.id);
143 FETCH oks_kodv_pk_csr INTO
144 l_codv_rec.id,
145 l_codv_rec.cod_id,
146 l_codv_rec.apply_all_yn,
147 l_codv_rec.line_renewal_type,
148 l_codv_rec.renewal_type,
149 l_codv_rec.po_required_yn,
150 l_codv_rec.renewal_pricing_type,
151 l_codv_rec.markup_percent,
152 l_codv_rec.link_order_header_id,
153 l_codv_rec.end_date,
154 l_codv_rec.cod_type,
155 l_codv_rec.order_line_id1,
156 l_codv_rec.order_line_id2,
157 l_codv_rec.object_version_number,
158 l_codv_rec.created_by,
159 l_codv_rec.creation_date,
160 l_codv_rec.last_updated_by,
161 l_codv_rec.last_update_date,
162 l_codv_rec.link_ord_line_id1,
163 l_codv_rec.link_ord_line_id2,
164 l_codv_rec.link_chr_id,
165 l_codv_rec.link_cle_id,
166 l_codv_rec.price_list_id1,
167 l_codv_rec.price_list_id2,
168 l_codv_rec.chr_id,
169 l_codv_rec.cle_id,
170 l_codv_rec.contact_id,
171 l_codv_rec.site_id,
172 l_codv_rec.email_id,
173 l_codv_rec.phone_id,
174 l_codv_rec.fax_id,
175 l_codv_rec.billing_profile_id,
176 l_codv_rec.RENEWAL_APPROVAL_FLAG;
177 x_no_data_found := oks_kodv_pk_csr%NOTFOUND;
178 CLOSE oks_kodv_pk_csr;
179 RETURN(l_codv_rec);
180 END get_rec;
181
182 ------------------------------------------------------------------
183 -- This version of get_rec sets error messages if no data found --
184 ------------------------------------------------------------------
185 FUNCTION get_rec (
186 p_codv_rec IN codv_rec_type,
187 x_return_status OUT NOCOPY VARCHAR2
188 ) RETURN codv_rec_type IS
189 l_codv_rec codv_rec_type;
190 l_row_notfound BOOLEAN := TRUE;
191 BEGIN
192 x_return_status := OKC_API.G_RET_STS_SUCCESS;
193 l_codv_rec := get_rec(p_codv_rec, l_row_notfound);
194 IF (l_row_notfound) THEN
195 OKC_API.set_message(G_APP_NAME,G_INVALID_VALUE,G_COL_NAME_TOKEN,'ID');
196 x_return_status := OKC_API.G_RET_STS_ERROR;
197 END IF;
198 RETURN(l_codv_rec);
199 END get_rec;
200 -----------------------------------------------------------
201 -- So we don't have to pass an "l_row_notfound" variable --
202 -----------------------------------------------------------
203 FUNCTION get_rec (
204 p_codv_rec IN codv_rec_type
205 ) RETURN codv_rec_type IS
206 l_row_not_found BOOLEAN := TRUE;
207 BEGIN
208 RETURN(get_rec(p_codv_rec, l_row_not_found));
209 END get_rec;
210 ---------------------------------------------------------------------------
211 -- FUNCTION get_rec for: OKS_K_ORDER_DETAILS
212 ---------------------------------------------------------------------------
213 FUNCTION get_rec (
214 p_cod_rec IN cod_rec_type,
215 x_no_data_found OUT NOCOPY BOOLEAN
216 ) RETURN cod_rec_type IS
217 CURSOR oks_k_order_details_pk_csr (p_id IN NUMBER) IS
218 SELECT
219 ID,
220 COD_TYPE,
221 LINK_ORDER_HEADER_ID,
222 ORDER_LINE_ID1,
223 ORDER_LINE_ID2,
224 APPLY_ALL_YN,
225 RENEWAL_TYPE,
226 LINE_RENEWAL_TYPE,
227 END_DATE,
228 OBJECT_VERSION_NUMBER,
229 CREATED_BY,
230 CREATION_DATE,
231 LAST_UPDATED_BY,
232 LAST_UPDATE_DATE,
233 PO_REQUIRED_YN,
234 RENEWAL_PRICING_TYPE,
235 MARKUP_PERCENT,
236 PRICE_LIST_ID1,
237 PRICE_LIST_ID2,
238 LINK_ORD_LINE_ID1,
239 LINK_ORD_LINE_ID2,
240 LINK_CHR_ID,
241 LINK_CLE_ID,
242 CHR_ID,
243 CLE_ID,
244 COD_ID,
245 CONTACT_ID,
246 SITE_ID,
247 EMAIL_ID,
248 PHONE_ID,
249 FAX_ID,
250 BILLING_PROFILE_ID,
251 RENEWAL_APPROVAL_FLAG
252 FROM Oks_K_Order_Details
253 WHERE oks_k_order_details.id = p_id;
254 l_oks_k_order_details_pk oks_k_order_details_pk_csr%ROWTYPE;
255 l_cod_rec cod_rec_type;
256 BEGIN
257 x_no_data_found := TRUE;
258 -- Get current database values
259 OPEN oks_k_order_details_pk_csr (p_cod_rec.id);
260 FETCH oks_k_order_details_pk_csr INTO
261 l_cod_rec.id,
262 l_cod_rec.cod_type,
263 l_cod_rec.link_order_header_id,
264 l_cod_rec.order_line_id1,
265 l_cod_rec.order_line_id2,
266 l_cod_rec.apply_all_yn,
267 l_cod_rec.renewal_type,
268 l_cod_rec.line_renewal_type,
269 l_cod_rec.end_date,
270 l_cod_rec.object_version_number,
271 l_cod_rec.created_by,
272 l_cod_rec.creation_date,
273 l_cod_rec.last_updated_by,
274 l_cod_rec.last_update_date,
275 l_cod_rec.po_required_yn,
276 l_cod_rec.renewal_pricing_type,
277 l_cod_rec.markup_percent,
278 l_cod_rec.price_list_id1,
279 l_cod_rec.price_list_id2,
280 l_cod_rec.link_ord_line_id1,
281 l_cod_rec.link_ord_line_id2,
282 l_cod_rec.link_chr_id,
283 l_cod_rec.link_cle_id,
284 l_cod_rec.chr_id,
285 l_cod_rec.cle_id,
286 l_cod_rec.cod_id,
287 l_cod_rec.contact_id,
288 l_cod_rec.site_id,
289 l_cod_rec.email_id,
290 l_cod_rec.phone_id,
291 l_cod_rec.fax_id,
292 l_cod_rec.billing_profile_id,
293 l_cod_rec.RENEWAL_APPROVAL_FLAG;
294 x_no_data_found := oks_k_order_details_pk_csr%NOTFOUND;
295 CLOSE oks_k_order_details_pk_csr;
296 RETURN(l_cod_rec);
297 END get_rec;
298
299 ------------------------------------------------------------------
300 -- This version of get_rec sets error messages if no data found --
301 ------------------------------------------------------------------
302 FUNCTION get_rec (
303 p_cod_rec IN cod_rec_type,
304 x_return_status OUT NOCOPY VARCHAR2
305 ) RETURN cod_rec_type IS
306 l_cod_rec cod_rec_type;
307 l_row_notfound BOOLEAN := TRUE;
308 BEGIN
309 x_return_status := OKC_API.G_RET_STS_SUCCESS;
310 l_cod_rec := get_rec(p_cod_rec, l_row_notfound);
311 IF (l_row_notfound) THEN
312 OKC_API.set_message(G_APP_NAME,G_INVALID_VALUE,G_COL_NAME_TOKEN,'ID');
313 x_return_status := OKC_API.G_RET_STS_ERROR;
314 END IF;
315 RETURN(l_cod_rec);
316 END get_rec;
317 -----------------------------------------------------------
318 -- So we don't have to pass an "l_row_notfound" variable --
319 -----------------------------------------------------------
320 FUNCTION get_rec (
321 p_cod_rec IN cod_rec_type
322 ) RETURN cod_rec_type IS
323 l_row_not_found BOOLEAN := TRUE;
324 BEGIN
325 RETURN(get_rec(p_cod_rec, l_row_not_found));
326 END get_rec;
327 ---------------------------------------------------------------------------
328 -- FUNCTION null_out_defaults for: OKS_K_ORDER_DETAILS_V
329 ---------------------------------------------------------------------------
330 FUNCTION null_out_defaults (
331 p_codv_rec IN codv_rec_type
332 ) RETURN codv_rec_type IS
333 l_codv_rec codv_rec_type := p_codv_rec;
334 BEGIN
335 IF (l_codv_rec.id = OKC_API.G_MISS_NUM ) THEN
336 l_codv_rec.id := NULL;
337 END IF;
338 IF (l_codv_rec.cod_id = OKC_API.G_MISS_NUM ) THEN
339 l_codv_rec.cod_id := NULL;
340 END IF;
341 IF (l_codv_rec.apply_all_yn = OKC_API.G_MISS_CHAR ) THEN
342 l_codv_rec.apply_all_yn := NULL;
343 END IF;
344 IF (l_codv_rec.line_renewal_type = OKC_API.G_MISS_CHAR ) THEN
345 l_codv_rec.line_renewal_type := NULL;
346 END IF;
347 IF (l_codv_rec.renewal_type = OKC_API.G_MISS_CHAR ) THEN
348 l_codv_rec.renewal_type := NULL;
349 END IF;
350 IF (l_codv_rec.po_required_yn = OKC_API.G_MISS_CHAR ) THEN
351 l_codv_rec.po_required_yn := NULL;
352 END IF;
353 IF (l_codv_rec.renewal_pricing_type = OKC_API.G_MISS_CHAR ) THEN
354 l_codv_rec.renewal_pricing_type := NULL;
355 END IF;
356 IF (l_codv_rec.markup_percent = OKC_API.G_MISS_NUM ) THEN
357 l_codv_rec.markup_percent := NULL;
358 END IF;
359 IF (l_codv_rec.link_order_header_id = OKC_API.G_MISS_NUM ) THEN
360 l_codv_rec.link_order_header_id := NULL;
361 END IF;
362 IF (l_codv_rec.end_date = OKC_API.G_MISS_DATE ) THEN
363 l_codv_rec.end_date := NULL;
364 END IF;
365 IF (l_codv_rec.cod_type = OKC_API.G_MISS_CHAR ) THEN
366 l_codv_rec.cod_type := NULL;
367 END IF;
368 IF (l_codv_rec.order_line_id1 = OKC_API.G_MISS_CHAR ) THEN
369 l_codv_rec.order_line_id1 := NULL;
370 END IF;
371 IF (l_codv_rec.order_line_id2 = OKC_API.G_MISS_CHAR ) THEN
372 l_codv_rec.order_line_id2 := NULL;
373 END IF;
374 IF (l_codv_rec.object_version_number = OKC_API.G_MISS_NUM ) THEN
375 l_codv_rec.object_version_number := NULL;
376 END IF;
377 IF (l_codv_rec.created_by = OKC_API.G_MISS_NUM ) THEN
378 l_codv_rec.created_by := NULL;
379 END IF;
380 IF (l_codv_rec.creation_date = OKC_API.G_MISS_DATE ) THEN
381 l_codv_rec.creation_date := NULL;
382 END IF;
383 IF (l_codv_rec.last_updated_by = OKC_API.G_MISS_NUM ) THEN
384 l_codv_rec.last_updated_by := NULL;
385 END IF;
386 IF (l_codv_rec.last_update_date = OKC_API.G_MISS_DATE ) THEN
387 l_codv_rec.last_update_date := NULL;
388 END IF;
389 IF (l_codv_rec.link_ord_line_id1 = OKC_API.G_MISS_CHAR ) THEN
390 l_codv_rec.link_ord_line_id1 := NULL;
391 END IF;
392 IF (l_codv_rec.link_ord_line_id2 = OKC_API.G_MISS_CHAR ) THEN
393 l_codv_rec.link_ord_line_id2 := NULL;
394 END IF;
395 IF (l_codv_rec.link_chr_id = OKC_API.G_MISS_NUM ) THEN
396 l_codv_rec.link_chr_id := NULL;
397 END IF;
398 IF (l_codv_rec.link_cle_id = OKC_API.G_MISS_NUM ) THEN
399 l_codv_rec.link_cle_id := NULL;
400 END IF;
401 IF (l_codv_rec.price_list_id1 = OKC_API.G_MISS_CHAR ) THEN
402 l_codv_rec.price_list_id1 := NULL;
403 END IF;
404 IF (l_codv_rec.price_list_id2 = OKC_API.G_MISS_CHAR ) THEN
405 l_codv_rec.price_list_id2 := NULL;
406 END IF;
407 IF (l_codv_rec.chr_id = OKC_API.G_MISS_NUM ) THEN
408 l_codv_rec.chr_id := NULL;
409 END IF;
410 IF (l_codv_rec.cle_id = OKC_API.G_MISS_NUM ) THEN
411 l_codv_rec.cle_id := NULL;
412 END IF;
413 IF (l_codv_rec.contact_id = OKC_API.G_MISS_NUM ) THEN
414 l_codv_rec.contact_id := NULL;
415 END IF;
416 IF (l_codv_rec.site_id = OKC_API.G_MISS_NUM ) THEN
417 l_codv_rec.site_id := NULL;
418 END IF;
419 IF (l_codv_rec.email_id = OKC_API.G_MISS_NUM ) THEN
420 l_codv_rec.email_id := NULL;
421 END IF;
422 IF (l_codv_rec.phone_id = OKC_API.G_MISS_NUM ) THEN
423 l_codv_rec.phone_id := NULL;
424 END IF;
425 IF (l_codv_rec.fax_id = OKC_API.G_MISS_NUM ) THEN
426 l_codv_rec.fax_id := NULL;
427 END IF;
428 IF (l_codv_rec.billing_profile_id = OKC_API.G_MISS_NUM ) THEN
429 l_codv_rec.billing_profile_id := NULL;
430 END IF;
431 IF (l_codv_rec.RENEWAL_APPROVAL_FLAG = OKC_API.G_MISS_CHAR ) THEN
432 l_codv_rec.RENEWAL_APPROVAL_FLAG := NULL;
433 END IF;
434
435 RETURN(l_codv_rec);
436 END null_out_defaults;
437 ---------------------------------
438 -- Validate_Attributes for: ID --
439 ---------------------------------
440 PROCEDURE validate_id(
441 x_return_status OUT NOCOPY VARCHAR2,
442 p_id IN NUMBER) IS
443 BEGIN
444 x_return_status := OKC_API.G_RET_STS_SUCCESS;
445 IF (p_id = OKC_API.G_MISS_NUM OR
446 p_id IS NULL)
447 THEN
448 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'id');
449 x_return_status := OKC_API.G_RET_STS_ERROR;
450 RAISE G_EXCEPTION_HALT_VALIDATION;
451 END IF;
452 EXCEPTION
453 WHEN G_EXCEPTION_HALT_VALIDATION THEN
454 null;
455 WHEN OTHERS THEN
456 OKC_API.SET_MESSAGE( p_app_name => G_APP_NAME
457 ,p_msg_name => G_UNEXPECTED_ERROR
458 ,p_token1 => G_SQLCODE_TOKEN
459 ,p_token1_value => SQLCODE
460 ,p_token2 => G_SQLERRM_TOKEN
461 ,p_token2_value => SQLERRM);
462 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
463 END validate_id;
464 -------------------------------------------
465 -- Validate_Attributes for: APPLY_ALL_YN --
466 -------------------------------------------
467 PROCEDURE validate_apply_all_yn(
468 x_return_status OUT NOCOPY VARCHAR2,
469 p_apply_all_yn IN VARCHAR2) IS
470 BEGIN
471 x_return_status := OKC_API.G_RET_STS_SUCCESS;
472 IF (p_apply_all_yn = OKC_API.G_MISS_CHAR OR
473 p_apply_all_yn IS NULL)
474 THEN
475 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'apply_all_yn');
476 x_return_status := OKC_API.G_RET_STS_ERROR;
477 RAISE G_EXCEPTION_HALT_VALIDATION;
478 END IF;
479 EXCEPTION
480 WHEN G_EXCEPTION_HALT_VALIDATION THEN
481 null;
482 WHEN OTHERS THEN
483 OKC_API.SET_MESSAGE( p_app_name => G_APP_NAME
484 ,p_msg_name => G_UNEXPECTED_ERROR
485 ,p_token1 => G_SQLCODE_TOKEN
486 ,p_token1_value => SQLCODE
487 ,p_token2 => G_SQLERRM_TOKEN
488 ,p_token2_value => SQLERRM);
489 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
490 END validate_apply_all_yn;
491 ------------------------------------------------
492 -- Validate_Attributes for: LINE_RENEWAL_TYPE --
493 ------------------------------------------------
494 PROCEDURE validate_line_renewal_type(
495 x_return_status OUT NOCOPY VARCHAR2,
496 p_line_renewal_type IN VARCHAR2) IS
497 BEGIN
498 x_return_status := OKC_API.G_RET_STS_SUCCESS;
499 IF (p_line_renewal_type = OKC_API.G_MISS_CHAR OR
500 p_line_renewal_type IS NULL)
501 THEN
502 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'line_renewal_type');
503 x_return_status := OKC_API.G_RET_STS_ERROR;
504 RAISE G_EXCEPTION_HALT_VALIDATION;
505 END IF;
506 EXCEPTION
507 WHEN G_EXCEPTION_HALT_VALIDATION THEN
508 null;
509 WHEN OTHERS THEN
510 OKC_API.SET_MESSAGE( p_app_name => G_APP_NAME
511 ,p_msg_name => G_UNEXPECTED_ERROR
512 ,p_token1 => G_SQLCODE_TOKEN
513 ,p_token1_value => SQLCODE
514 ,p_token2 => G_SQLERRM_TOKEN
515 ,p_token2_value => SQLERRM);
516 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
517 END validate_line_renewal_type;
518 ---------------------------------------
519 -- Validate_Attributes for: COD_TYPE --
520 ---------------------------------------
521 PROCEDURE validate_cod_type(
522 x_return_status OUT NOCOPY VARCHAR2,
523 p_cod_type IN VARCHAR2) IS
524 BEGIN
525 x_return_status := OKC_API.G_RET_STS_SUCCESS;
526 IF (p_cod_type = OKC_API.G_MISS_CHAR OR
527 p_cod_type IS NULL)
528 THEN
529 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'cod_type');
530 x_return_status := OKC_API.G_RET_STS_ERROR;
531 RAISE G_EXCEPTION_HALT_VALIDATION;
532 END IF;
533 EXCEPTION
534 WHEN G_EXCEPTION_HALT_VALIDATION THEN
535 null;
536 WHEN OTHERS THEN
537 OKC_API.SET_MESSAGE( p_app_name => G_APP_NAME
538 ,p_msg_name => G_UNEXPECTED_ERROR
539 ,p_token1 => G_SQLCODE_TOKEN
540 ,p_token1_value => SQLCODE
541 ,p_token2 => G_SQLERRM_TOKEN
542 ,p_token2_value => SQLERRM);
543 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
544 END validate_cod_type;
545 ---------------------------------------------
546 -- Validate_Attributes for: ORDER_LINE_ID1 --
547 ---------------------------------------------
548 PROCEDURE validate_order_line_id1(
549 x_return_status OUT NOCOPY VARCHAR2,
550 p_order_line_id1 IN VARCHAR2) IS
551 BEGIN
552 x_return_status := OKC_API.G_RET_STS_SUCCESS;
553 IF (p_order_line_id1 = OKC_API.G_MISS_CHAR OR
554 p_order_line_id1 IS NULL)
555 THEN
556 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'order_line_id1');
557 x_return_status := OKC_API.G_RET_STS_ERROR;
558 RAISE G_EXCEPTION_HALT_VALIDATION;
559 END IF;
560 EXCEPTION
561 WHEN G_EXCEPTION_HALT_VALIDATION THEN
562 null;
563 WHEN OTHERS THEN
564 OKC_API.SET_MESSAGE( p_app_name => G_APP_NAME
565 ,p_msg_name => G_UNEXPECTED_ERROR
566 ,p_token1 => G_SQLCODE_TOKEN
567 ,p_token1_value => SQLCODE
568 ,p_token2 => G_SQLERRM_TOKEN
569 ,p_token2_value => SQLERRM);
570 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
571 END validate_order_line_id1;
572 ---------------------------------------------
573 -- Validate_Attributes for: ORDER_LINE_ID2 --
574 ---------------------------------------------
575 PROCEDURE validate_order_line_id2(
576 x_return_status OUT NOCOPY VARCHAR2,
577 p_order_line_id2 IN VARCHAR2) IS
578 BEGIN
579 x_return_status := OKC_API.G_RET_STS_SUCCESS;
580 IF (p_order_line_id2 = OKC_API.G_MISS_CHAR OR
581 p_order_line_id2 IS NULL)
582 THEN
583 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'order_line_id2');
584 x_return_status := OKC_API.G_RET_STS_ERROR;
585 RAISE G_EXCEPTION_HALT_VALIDATION;
586 END IF;
587 EXCEPTION
588 WHEN G_EXCEPTION_HALT_VALIDATION THEN
589 null;
590 WHEN OTHERS THEN
591 OKC_API.SET_MESSAGE( p_app_name => G_APP_NAME
592 ,p_msg_name => G_UNEXPECTED_ERROR
593 ,p_token1 => G_SQLCODE_TOKEN
594 ,p_token1_value => SQLCODE
595 ,p_token2 => G_SQLERRM_TOKEN
596 ,p_token2_value => SQLERRM);
597 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
598 END validate_order_line_id2;
599 ----------------------------------------------------
600 -- Validate_Attributes for: OBJECT_VERSION_NUMBER --
601 ----------------------------------------------------
602 PROCEDURE validate_object_version_number(
603 x_return_status OUT NOCOPY VARCHAR2,
604 p_object_version_number IN NUMBER) IS
605 BEGIN
606 x_return_status := OKC_API.G_RET_STS_SUCCESS;
607 IF (p_object_version_number = OKC_API.G_MISS_NUM OR
608 p_object_version_number IS NULL)
609 THEN
610 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'object_version_number');
611 x_return_status := OKC_API.G_RET_STS_ERROR;
612 RAISE G_EXCEPTION_HALT_VALIDATION;
613 END IF;
614 EXCEPTION
615 WHEN G_EXCEPTION_HALT_VALIDATION THEN
616 null;
617 WHEN OTHERS THEN
618 OKC_API.SET_MESSAGE( p_app_name => G_APP_NAME
619 ,p_msg_name => G_UNEXPECTED_ERROR
620 ,p_token1 => G_SQLCODE_TOKEN
621 ,p_token1_value => SQLCODE
622 ,p_token2 => G_SQLERRM_TOKEN
623 ,p_token2_value => SQLERRM);
624 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
625 END validate_object_version_number;
626 ---------------------------------------------------------------------------
627 -- FUNCTION Validate_Attributes
628 ---------------------------------------------------------------------------
629 ---------------------------------------------------
630 -- Validate_Attributes for:OKS_K_ORDER_DETAILS_V --
631 ---------------------------------------------------
632 FUNCTION Validate_Attributes (
633 p_codv_rec IN codv_rec_type
634 ) RETURN VARCHAR2 IS
635 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
636 x_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
637 BEGIN
638 -----------------------------
639 -- Column Level Validation --
640 -----------------------------
641 -- ***
642 -- id
643 -- ***
644 validate_id(x_return_status, p_codv_rec.id);
645 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
646 l_return_status := x_return_status;
647 RAISE G_EXCEPTION_HALT_VALIDATION;
648 END IF;
649
650 -- ***
651 -- apply_all_yn
652 -- ***
653 validate_apply_all_yn(x_return_status, p_codv_rec.apply_all_yn);
654 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
655 l_return_status := x_return_status;
656 RAISE G_EXCEPTION_HALT_VALIDATION;
657 END IF;
658
659 -- ***
660 -- line_renewal_type
661 -- ***
662 validate_line_renewal_type(x_return_status, p_codv_rec.line_renewal_type);
663 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
664 l_return_status := x_return_status;
665 RAISE G_EXCEPTION_HALT_VALIDATION;
666 END IF;
667
668 -- ***
669 -- cod_type
670 -- ***
671 validate_cod_type(x_return_status, p_codv_rec.cod_type);
672 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
673 l_return_status := x_return_status;
674 RAISE G_EXCEPTION_HALT_VALIDATION;
675 END IF;
676
677 -- ***
678 -- order_line_id1
679 -- ***
680 validate_order_line_id1(x_return_status, p_codv_rec.order_line_id1);
681 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
682 l_return_status := x_return_status;
683 RAISE G_EXCEPTION_HALT_VALIDATION;
684 END IF;
685
686 -- ***
687 -- order_line_id2
688 -- ***
689 validate_order_line_id2(x_return_status, p_codv_rec.order_line_id2);
690 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
691 l_return_status := x_return_status;
692 RAISE G_EXCEPTION_HALT_VALIDATION;
693 END IF;
694
695 -- ***
696 -- object_version_number
697 -- ***
698 validate_object_version_number(x_return_status, p_codv_rec.object_version_number);
699 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
700 l_return_status := x_return_status;
701 RAISE G_EXCEPTION_HALT_VALIDATION;
702 END IF;
703
704 RETURN(l_return_status);
705 EXCEPTION
706 WHEN G_EXCEPTION_HALT_VALIDATION THEN
707 RETURN(l_return_status);
708 WHEN OTHERS THEN
709 OKC_API.SET_MESSAGE( p_app_name => G_APP_NAME
710 ,p_msg_name => G_UNEXPECTED_ERROR
711 ,p_token1 => G_SQLCODE_TOKEN
712 ,p_token1_value => SQLCODE
713 ,p_token2 => G_SQLERRM_TOKEN
714 ,p_token2_value => SQLERRM);
715 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
716 RETURN(l_return_status);
717 END Validate_Attributes;
718 ---------------------------------------------------------------------------
719 -- PROCEDURE Validate_Record
720 ---------------------------------------------------------------------------
721 -----------------------------------------------
722 -- Validate Record for:OKS_K_ORDER_DETAILS_V --
723 -----------------------------------------------
724 FUNCTION Validate_Record (
725 p_codv_rec IN codv_rec_type,
726 p_db_codv_rec IN codv_rec_type
727 ) RETURN VARCHAR2 IS
728 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
729 BEGIN
730 RETURN (l_return_status);
731 END Validate_Record;
732 FUNCTION Validate_Record (
733 p_codv_rec IN codv_rec_type
734 ) RETURN VARCHAR2 IS
735 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
736 l_db_codv_rec codv_rec_type := get_rec(p_codv_rec);
737 BEGIN
738 l_return_status := Validate_Record(p_codv_rec => p_codv_rec,
739 p_db_codv_rec => l_db_codv_rec);
740 RETURN (l_return_status);
741 END Validate_Record;
742
743 ---------------------------------------------------------------------------
744 -- PROCEDURE Migrate
745 ---------------------------------------------------------------------------
746 PROCEDURE migrate (
747 p_from IN codv_rec_type,
748 p_to IN OUT NOCOPY cod_rec_type
749 ) IS
750 BEGIN
751 p_to.id := p_from.id;
752 p_to.cod_type := p_from.cod_type;
753 p_to.link_order_header_id := p_from.link_order_header_id;
754 p_to.order_line_id1 := p_from.order_line_id1;
755 p_to.order_line_id2 := p_from.order_line_id2;
756 p_to.apply_all_yn := p_from.apply_all_yn;
757 p_to.renewal_type := p_from.renewal_type;
758 p_to.line_renewal_type := p_from.line_renewal_type;
759 p_to.end_date := p_from.end_date;
760 p_to.object_version_number := p_from.object_version_number;
761 p_to.created_by := p_from.created_by;
762 p_to.creation_date := p_from.creation_date;
763 p_to.last_updated_by := p_from.last_updated_by;
764 p_to.last_update_date := p_from.last_update_date;
765 p_to.po_required_yn := p_from.po_required_yn;
766 p_to.renewal_pricing_type := p_from.renewal_pricing_type;
767 p_to.markup_percent := p_from.markup_percent;
768 p_to.price_list_id1 := p_from.price_list_id1;
769 p_to.price_list_id2 := p_from.price_list_id2;
770 p_to.link_ord_line_id1 := p_from.link_ord_line_id1;
771 p_to.link_ord_line_id2 := p_from.link_ord_line_id2;
772 p_to.link_chr_id := p_from.link_chr_id;
773 p_to.link_cle_id := p_from.link_cle_id;
774 p_to.chr_id := p_from.chr_id;
775 p_to.cle_id := p_from.cle_id;
776 p_to.cod_id := p_from.cod_id;
777 p_to.contact_id := p_from.contact_id;
778 p_to.site_id := p_from.site_id;
779 p_to.email_id := p_from.email_id;
780 p_to.phone_id := p_from.phone_id;
781 p_to.fax_id := p_from.fax_id;
782 p_to.billing_profile_id := p_from.billing_profile_id;
783 p_to.RENEWAL_APPROVAL_FLAG := p_from.RENEWAL_APPROVAL_FLAG;
784 END migrate;
785 PROCEDURE migrate (
786 p_from IN cod_rec_type,
787 p_to IN OUT NOCOPY codv_rec_type
788 ) IS
789 BEGIN
790 p_to.id := p_from.id;
791 p_to.cod_id := p_from.cod_id;
792 p_to.apply_all_yn := p_from.apply_all_yn;
793 p_to.line_renewal_type := p_from.line_renewal_type;
794 p_to.renewal_type := p_from.renewal_type;
795 p_to.po_required_yn := p_from.po_required_yn;
796 p_to.renewal_pricing_type := p_from.renewal_pricing_type;
797 p_to.markup_percent := p_from.markup_percent;
798 p_to.link_order_header_id := p_from.link_order_header_id;
799 p_to.end_date := p_from.end_date;
800 p_to.cod_type := p_from.cod_type;
801 p_to.order_line_id1 := p_from.order_line_id1;
802 p_to.order_line_id2 := p_from.order_line_id2;
803 p_to.object_version_number := p_from.object_version_number;
804 p_to.created_by := p_from.created_by;
805 p_to.creation_date := p_from.creation_date;
806 p_to.last_updated_by := p_from.last_updated_by;
807 p_to.last_update_date := p_from.last_update_date;
808 p_to.link_ord_line_id1 := p_from.link_ord_line_id1;
809 p_to.link_ord_line_id2 := p_from.link_ord_line_id2;
810 p_to.link_chr_id := p_from.link_chr_id;
811 p_to.link_cle_id := p_from.link_cle_id;
812 p_to.price_list_id1 := p_from.price_list_id1;
813 p_to.price_list_id2 := p_from.price_list_id2;
814 p_to.chr_id := p_from.chr_id;
815 p_to.cle_id := p_from.cle_id;
816 p_to.contact_id := p_from.contact_id;
817 p_to.site_id := p_from.site_id;
818 p_to.email_id := p_from.email_id;
819 p_to.phone_id := p_from.phone_id;
820 p_to.fax_id := p_from.fax_id;
821 p_to.billing_profile_id := p_from.billing_profile_id;
822 p_to.RENEWAL_APPROVAL_FLAG := p_from.RENEWAL_APPROVAL_FLAG;
823
824 END migrate;
825 ---------------------------------------------------------------------------
826 -- PROCEDURE validate_row
827 ---------------------------------------------------------------------------
828 --------------------------------------------
829 -- validate_row for:OKS_K_ORDER_DETAILS_V --
830 --------------------------------------------
831 PROCEDURE validate_row(
832 p_api_version IN NUMBER,
833 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
834 x_return_status OUT NOCOPY VARCHAR2,
835 x_msg_count OUT NOCOPY NUMBER,
836 x_msg_data OUT NOCOPY VARCHAR2,
837 p_codv_rec IN codv_rec_type) IS
838
839 l_api_version CONSTANT NUMBER := 1;
840 l_api_name CONSTANT VARCHAR2(30) := 'V_validate_row';
841 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
842 l_codv_rec codv_rec_type := p_codv_rec;
843 l_cod_rec cod_rec_type;
844 l_cod_rec cod_rec_type;
845 BEGIN
846 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
847 G_PKG_NAME,
848 p_init_msg_list,
849 l_api_version,
850 p_api_version,
851 '_PVT',
852 x_return_status);
853 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
854 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
855 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
856 RAISE OKC_API.G_EXCEPTION_ERROR;
857 END IF;
858 --- Validate all non-missing attributes (Item Level Validation)
859 l_return_status := Validate_Attributes(l_codv_rec);
860 --- If any errors happen abort API
861 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
862 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
863 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
864 RAISE OKC_API.G_EXCEPTION_ERROR;
865 END IF;
866 l_return_status := Validate_Record(l_codv_rec);
867 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
868 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
869 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
870 RAISE OKC_API.G_EXCEPTION_ERROR;
871 END IF;
872 x_return_status := l_return_status;
873 EXCEPTION
874 WHEN OKC_API.G_EXCEPTION_ERROR THEN
875 x_return_status := OKC_API.HANDLE_EXCEPTIONS
876 (
877 l_api_name,
878 G_PKG_NAME,
879 'OKC_API.G_RET_STS_ERROR',
880 x_msg_count,
881 x_msg_data,
882 '_PVT'
883 );
884 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
885 x_return_status := OKC_API.HANDLE_EXCEPTIONS
886 (
887 l_api_name,
888 G_PKG_NAME,
889 'OKC_API.G_RET_STS_UNEXP_ERROR',
890 x_msg_count,
891 x_msg_data,
892 '_PVT'
893 );
894 WHEN OTHERS THEN
895 x_return_status := OKC_API.HANDLE_EXCEPTIONS
896 (
897 l_api_name,
898 G_PKG_NAME,
899 'OTHERS',
900 x_msg_count,
901 x_msg_data,
902 '_PVT'
903 );
904 END validate_row;
905 -------------------------------------------------------
906 -- PL/SQL TBL validate_row for:OKS_K_ORDER_DETAILS_V --
907 -------------------------------------------------------
908 PROCEDURE validate_row(
909 p_api_version IN NUMBER,
910 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
911 x_return_status OUT NOCOPY VARCHAR2,
912 x_msg_count OUT NOCOPY NUMBER,
913 x_msg_data OUT NOCOPY VARCHAR2,
914 p_codv_tbl IN codv_tbl_type,
915 px_error_tbl IN OUT NOCOPY OKC_API.ERROR_TBL_TYPE) IS
916
917 l_api_version CONSTANT NUMBER := 1;
918 l_api_name CONSTANT VARCHAR2(30) := 'V_error_tbl_validate_row';
919 i NUMBER := 0;
920 BEGIN
921 OKC_API.init_msg_list(p_init_msg_list);
922 -- Make sure PL/SQL table has records in it before passing
923 IF (p_codv_tbl.COUNT > 0) THEN
924 i := p_codv_tbl.FIRST;
925 LOOP
926 DECLARE
927 l_error_rec OKC_API.ERROR_REC_TYPE;
928 BEGIN
929 l_error_rec.api_name := l_api_name;
930 l_error_rec.api_package := G_PKG_NAME;
931 l_error_rec.idx := i;
932 validate_row (
933 p_api_version => p_api_version,
934 p_init_msg_list => OKC_API.G_FALSE,
935 x_return_status => l_error_rec.error_type,
936 x_msg_count => l_error_rec.msg_count,
937 x_msg_data => l_error_rec.msg_data,
938 p_codv_rec => p_codv_tbl(i));
939 IF (l_error_rec.error_type <> OKC_API.G_RET_STS_SUCCESS) THEN
940 l_error_rec.sqlcode := SQLCODE;
941 load_error_tbl(l_error_rec, px_error_tbl);
942 ELSE
943 x_msg_count := l_error_rec.msg_count;
944 x_msg_data := l_error_rec.msg_data;
945 END IF;
946 EXCEPTION
947 WHEN OKC_API.G_EXCEPTION_ERROR THEN
948 l_error_rec.error_type := OKC_API.G_RET_STS_ERROR;
949 l_error_rec.sqlcode := SQLCODE;
950 load_error_tbl(l_error_rec, px_error_tbl);
951 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
952 l_error_rec.error_type := OKC_API.G_RET_STS_UNEXP_ERROR;
953 l_error_rec.sqlcode := SQLCODE;
954 load_error_tbl(l_error_rec, px_error_tbl);
955 WHEN OTHERS THEN
956 l_error_rec.error_type := 'OTHERS';
957 l_error_rec.sqlcode := SQLCODE;
958 load_error_tbl(l_error_rec, px_error_tbl);
959 END;
960 EXIT WHEN (i = p_codv_tbl.LAST);
961 i := p_codv_tbl.NEXT(i);
962 END LOOP;
963 END IF;
964 -- Loop through the error_tbl to find the error with the highest severity
965 -- and return it.
966 x_return_status := find_highest_exception(px_error_tbl);
967 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
968 EXCEPTION
969 WHEN OKC_API.G_EXCEPTION_ERROR THEN
970 x_return_status := OKC_API.HANDLE_EXCEPTIONS
971 (
972 l_api_name,
973 G_PKG_NAME,
974 'OKC_API.G_RET_STS_ERROR',
975 x_msg_count,
976 x_msg_data,
977 '_PVT'
978 );
979 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
980 x_return_status := OKC_API.HANDLE_EXCEPTIONS
981 (
982 l_api_name,
983 G_PKG_NAME,
984 'OKC_API.G_RET_STS_UNEXP_ERROR',
985 x_msg_count,
986 x_msg_data,
987 '_PVT'
988 );
989 WHEN OTHERS THEN
990 x_return_status := OKC_API.HANDLE_EXCEPTIONS
991 (
992 l_api_name,
993 G_PKG_NAME,
994 'OTHERS',
995 x_msg_count,
996 x_msg_data,
997 '_PVT'
998 );
999 END validate_row;
1000
1001 -------------------------------------------------------
1002 -- PL/SQL TBL validate_row for:OKS_K_ORDER_DETAILS_V --
1003 -------------------------------------------------------
1004 PROCEDURE validate_row(
1005 p_api_version IN NUMBER,
1006 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1007 x_return_status OUT NOCOPY VARCHAR2,
1008 x_msg_count OUT NOCOPY NUMBER,
1009 x_msg_data OUT NOCOPY VARCHAR2,
1010 p_codv_tbl IN codv_tbl_type) IS
1011
1012 l_api_version CONSTANT NUMBER := 1;
1013 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_validate_row';
1014 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1015 l_error_tbl OKC_API.ERROR_TBL_TYPE;
1016 BEGIN
1017 OKC_API.init_msg_list(p_init_msg_list);
1018 -- Make sure PL/SQL table has records in it before passing
1019 IF (p_codv_tbl.COUNT > 0) THEN
1020 validate_row (
1021 p_api_version => p_api_version,
1022 p_init_msg_list => OKC_API.G_FALSE,
1023 x_return_status => x_return_status,
1024 x_msg_count => x_msg_count,
1025 x_msg_data => x_msg_data,
1026 p_codv_tbl => p_codv_tbl,
1027 px_error_tbl => l_error_tbl);
1028 END IF;
1029 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1030 EXCEPTION
1031 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1032 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1033 (
1034 l_api_name,
1035 G_PKG_NAME,
1036 'OKC_API.G_RET_STS_ERROR',
1037 x_msg_count,
1038 x_msg_data,
1039 '_PVT'
1040 );
1041 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1042 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1043 (
1044 l_api_name,
1045 G_PKG_NAME,
1046 'OKC_API.G_RET_STS_UNEXP_ERROR',
1047 x_msg_count,
1048 x_msg_data,
1049 '_PVT'
1050 );
1051 WHEN OTHERS THEN
1052 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1053 (
1054 l_api_name,
1055 G_PKG_NAME,
1056 'OTHERS',
1057 x_msg_count,
1058 x_msg_data,
1059 '_PVT'
1060 );
1061 END validate_row;
1062
1063 ---------------------------------------------------------------------------
1064 -- PROCEDURE insert_row
1065 ---------------------------------------------------------------------------
1066 ----------------------------------------
1067 -- insert_row for:OKS_K_ORDER_DETAILS --
1068 ----------------------------------------
1069 PROCEDURE insert_row(
1070 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1071 x_return_status OUT NOCOPY VARCHAR2,
1072 x_msg_count OUT NOCOPY NUMBER,
1073 x_msg_data OUT NOCOPY VARCHAR2,
1074 p_cod_rec IN cod_rec_type,
1075 x_cod_rec OUT NOCOPY cod_rec_type) IS
1076
1077 l_api_version CONSTANT NUMBER := 1;
1078 l_api_name CONSTANT VARCHAR2(30) := 'B_insert_row';
1079 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1080 l_cod_rec cod_rec_type := p_cod_rec;
1081 l_def_cod_rec cod_rec_type;
1082 --------------------------------------------
1083 -- Set_Attributes for:OKS_K_ORDER_DETAILS --
1084 --------------------------------------------
1085 FUNCTION Set_Attributes (
1086 p_cod_rec IN cod_rec_type,
1087 x_cod_rec OUT NOCOPY cod_rec_type
1088 ) RETURN VARCHAR2 IS
1089 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1090 BEGIN
1091 x_cod_rec := p_cod_rec;
1092 RETURN(l_return_status);
1093 END Set_Attributes;
1094 BEGIN
1095 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1096 p_init_msg_list,
1097 '_PVT',
1098 x_return_status);
1099 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1100 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1101 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1102 RAISE OKC_API.G_EXCEPTION_ERROR;
1103 END IF;
1104 --- Setting item atributes
1105 l_return_status := Set_Attributes(
1106 p_cod_rec, -- IN
1107 l_cod_rec); -- OUT
1108 --- If any errors happen abort API
1109 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1110 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1111 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1112 RAISE OKC_API.G_EXCEPTION_ERROR;
1113 END IF;
1114 INSERT INTO OKS_K_ORDER_DETAILS(
1115 id,
1116 cod_type,
1117 link_order_header_id,
1118 order_line_id1,
1119 order_line_id2,
1120 apply_all_yn,
1121 renewal_type,
1122 line_renewal_type,
1123 end_date,
1124 object_version_number,
1125 created_by,
1126 creation_date,
1127 last_updated_by,
1128 last_update_date,
1129 po_required_yn,
1130 renewal_pricing_type,
1131 markup_percent,
1132 price_list_id1,
1133 price_list_id2,
1134 link_ord_line_id1,
1135 link_ord_line_id2,
1136 link_chr_id,
1137 link_cle_id,
1138 chr_id,
1139 cle_id,
1140 cod_id,
1141 contact_id,
1142 site_id,
1143 email_id,
1144 phone_id,
1145 fax_id,
1146 billing_profile_id,
1147 RENEWAL_APPROVAL_FLAG)
1148 VALUES (
1149 l_cod_rec.id,
1150 l_cod_rec.cod_type,
1151 l_cod_rec.link_order_header_id,
1152 l_cod_rec.order_line_id1,
1153 l_cod_rec.order_line_id2,
1154 l_cod_rec.apply_all_yn,
1155 l_cod_rec.renewal_type,
1156 l_cod_rec.line_renewal_type,
1157 l_cod_rec.end_date,
1158 l_cod_rec.object_version_number,
1159 l_cod_rec.created_by,
1160 l_cod_rec.creation_date,
1161 l_cod_rec.last_updated_by,
1162 l_cod_rec.last_update_date,
1163 l_cod_rec.po_required_yn,
1164 l_cod_rec.renewal_pricing_type,
1165 l_cod_rec.markup_percent,
1166 l_cod_rec.price_list_id1,
1167 l_cod_rec.price_list_id2,
1168 l_cod_rec.link_ord_line_id1,
1169 l_cod_rec.link_ord_line_id2,
1170 l_cod_rec.link_chr_id,
1171 l_cod_rec.link_cle_id,
1172 l_cod_rec.chr_id,
1173 l_cod_rec.cle_id,
1174 l_cod_rec.cod_id,
1175 l_cod_rec.contact_id,
1176 l_cod_rec.site_id,
1177 l_cod_rec.email_id,
1178 l_cod_rec.phone_id,
1179 l_cod_rec.fax_id,
1180 l_cod_rec.billing_profile_id,
1181 l_cod_rec.RENEWAL_APPROVAL_FLAG);
1182 -- Set OUT values
1183 x_cod_rec := l_cod_rec;
1184 x_return_status := l_return_status;
1185 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1186 EXCEPTION
1187 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1188 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1189 (
1190 l_api_name,
1191 G_PKG_NAME,
1192 'OKC_API.G_RET_STS_ERROR',
1193 x_msg_count,
1194 x_msg_data,
1195 '_PVT'
1196 );
1197 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1198 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1199 (
1200 l_api_name,
1201 G_PKG_NAME,
1202 'OKC_API.G_RET_STS_UNEXP_ERROR',
1203 x_msg_count,
1204 x_msg_data,
1205 '_PVT'
1206 );
1207 WHEN OTHERS THEN
1208 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1209 (
1210 l_api_name,
1211 G_PKG_NAME,
1212 'OTHERS',
1213 x_msg_count,
1214 x_msg_data,
1215 '_PVT'
1216 );
1217 END insert_row;
1218 -------------------------------------------
1219 -- insert_row for :OKS_K_ORDER_DETAILS_V --
1220 -------------------------------------------
1221 PROCEDURE insert_row(
1222 p_api_version IN NUMBER,
1223 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1224 x_return_status OUT NOCOPY VARCHAR2,
1225 x_msg_count OUT NOCOPY NUMBER,
1226 x_msg_data OUT NOCOPY VARCHAR2,
1227 p_codv_rec IN codv_rec_type,
1228 x_codv_rec OUT NOCOPY codv_rec_type) IS
1229
1230 l_api_version CONSTANT NUMBER := 1;
1231 l_api_name CONSTANT VARCHAR2(30) := 'V_insert_row';
1232 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1233 l_codv_rec codv_rec_type := p_codv_rec;
1234 l_def_codv_rec codv_rec_type;
1235 l_cod_rec cod_rec_type;
1236 lx_cod_rec cod_rec_type;
1237 -------------------------------
1238 -- FUNCTION fill_who_columns --
1239 -------------------------------
1240 FUNCTION fill_who_columns (
1241 p_codv_rec IN codv_rec_type
1242 ) RETURN codv_rec_type IS
1243 l_codv_rec codv_rec_type := p_codv_rec;
1244 BEGIN
1245 l_codv_rec.CREATION_DATE := SYSDATE;
1246 l_codv_rec.CREATED_BY := FND_GLOBAL.USER_ID;
1247 l_codv_rec.LAST_UPDATE_DATE := l_codv_rec.CREATION_DATE;
1248 l_codv_rec.LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
1249 --l_codv_rec.LAST_UPDATE_LOGIN := FND_GLOBAL.LOGIN_ID;
1250 RETURN(l_codv_rec);
1251 END fill_who_columns;
1252 ----------------------------------------------
1253 -- Set_Attributes for:OKS_K_ORDER_DETAILS_V --
1254 ----------------------------------------------
1255 FUNCTION Set_Attributes (
1256 p_codv_rec IN codv_rec_type,
1257 x_codv_rec OUT NOCOPY codv_rec_type
1258 ) RETURN VARCHAR2 IS
1259 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1260 BEGIN
1261 x_codv_rec := p_codv_rec;
1262 x_codv_rec.OBJECT_VERSION_NUMBER := 1;
1263 RETURN(l_return_status);
1264 END Set_Attributes;
1265 BEGIN
1266 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1267 G_PKG_NAME,
1268 p_init_msg_list,
1269 l_api_version,
1270 p_api_version,
1271 '_PVT',
1272 x_return_status);
1273 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1274 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1275 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1276 RAISE OKC_API.G_EXCEPTION_ERROR;
1277 END IF;
1278 l_codv_rec := null_out_defaults(p_codv_rec);
1279 -- Set primary key value
1280 l_codv_rec.ID := get_seq_id;
1281 -- Setting item attributes
1282 l_return_Status := Set_Attributes(
1283 l_codv_rec, -- IN
1284 l_def_codv_rec); -- OUT
1285 --- If any errors happen abort API
1286 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1287 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1288 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1289 RAISE OKC_API.G_EXCEPTION_ERROR;
1290 END IF;
1291 l_def_codv_rec := fill_who_columns(l_def_codv_rec);
1292 --- Validate all non-missing attributes (Item Level Validation)
1293 l_return_status := Validate_Attributes(l_def_codv_rec);
1294 --- If any errors happen abort API
1295 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1296 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1297 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1298 RAISE OKC_API.G_EXCEPTION_ERROR;
1299 END IF;
1300 l_return_status := Validate_Record(l_def_codv_rec);
1301 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1302 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1303 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1304 RAISE OKC_API.G_EXCEPTION_ERROR;
1305 END IF;
1306 -----------------------------------------
1307 -- Move VIEW record to "Child" records --
1308 -----------------------------------------
1309 migrate(l_def_codv_rec, l_cod_rec);
1310 -----------------------------------------------
1311 -- Call the INSERT_ROW for each child record --
1312 -----------------------------------------------
1313 insert_row(
1314 p_init_msg_list,
1315 l_return_status,
1316 x_msg_count,
1317 x_msg_data,
1318 l_cod_rec,
1319 lx_cod_rec
1320 );
1321 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1322 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1323 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1324 RAISE OKC_API.G_EXCEPTION_ERROR;
1325 END IF;
1326 migrate(lx_cod_rec, l_def_codv_rec);
1327 -- Set OUT values
1328 x_codv_rec := l_def_codv_rec;
1329 x_return_status := l_return_status;
1330 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1331 EXCEPTION
1332 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1333 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1334 (
1335 l_api_name,
1336 G_PKG_NAME,
1337 'OKC_API.G_RET_STS_ERROR',
1338 x_msg_count,
1339 x_msg_data,
1340 '_PVT'
1341 );
1342 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1343 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1344 (
1345 l_api_name,
1346 G_PKG_NAME,
1347 'OKC_API.G_RET_STS_UNEXP_ERROR',
1348 x_msg_count,
1349 x_msg_data,
1350 '_PVT'
1351 );
1352 WHEN OTHERS THEN
1353 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1354 (
1355 l_api_name,
1356 G_PKG_NAME,
1357 'OTHERS',
1358 x_msg_count,
1359 x_msg_data,
1360 '_PVT'
1361 );
1362 END insert_row;
1363 ----------------------------------------
1364 -- PL/SQL TBL insert_row for:CODV_TBL --
1365 ----------------------------------------
1366 PROCEDURE insert_row(
1367 p_api_version IN NUMBER,
1368 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1369 x_return_status OUT NOCOPY VARCHAR2,
1370 x_msg_count OUT NOCOPY NUMBER,
1371 x_msg_data OUT NOCOPY VARCHAR2,
1372 p_codv_tbl IN codv_tbl_type,
1373 x_codv_tbl OUT NOCOPY codv_tbl_type,
1374 px_error_tbl IN OUT NOCOPY OKC_API.ERROR_TBL_TYPE) IS
1375
1376 l_api_version CONSTANT NUMBER := 1;
1377 l_api_name CONSTANT VARCHAR2(30) := 'V_error_tbl_insert_row';
1378 i NUMBER := 0;
1379 BEGIN
1380 OKC_API.init_msg_list(p_init_msg_list);
1381 -- Make sure PL/SQL table has records in it before passing
1382 IF (p_codv_tbl.COUNT > 0) THEN
1383 i := p_codv_tbl.FIRST;
1384 LOOP
1385 DECLARE
1386 l_error_rec OKC_API.ERROR_REC_TYPE;
1387 BEGIN
1388 l_error_rec.api_name := l_api_name;
1389 l_error_rec.api_package := G_PKG_NAME;
1390 l_error_rec.idx := i;
1391 insert_row (
1392 p_api_version => p_api_version,
1393 p_init_msg_list => OKC_API.G_FALSE,
1394 x_return_status => l_error_rec.error_type,
1395 x_msg_count => l_error_rec.msg_count,
1396 x_msg_data => l_error_rec.msg_data,
1397 p_codv_rec => p_codv_tbl(i),
1398 x_codv_rec => x_codv_tbl(i));
1399 IF (l_error_rec.error_type <> OKC_API.G_RET_STS_SUCCESS) THEN
1400 l_error_rec.sqlcode := SQLCODE;
1401 load_error_tbl(l_error_rec, px_error_tbl);
1402 ELSE
1403 x_msg_count := l_error_rec.msg_count;
1404 x_msg_data := l_error_rec.msg_data;
1405 END IF;
1406 EXCEPTION
1407 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1408 l_error_rec.error_type := OKC_API.G_RET_STS_ERROR;
1409 l_error_rec.sqlcode := SQLCODE;
1410 load_error_tbl(l_error_rec, px_error_tbl);
1411 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1412 l_error_rec.error_type := OKC_API.G_RET_STS_UNEXP_ERROR;
1413 l_error_rec.sqlcode := SQLCODE;
1414 load_error_tbl(l_error_rec, px_error_tbl);
1415 WHEN OTHERS THEN
1416 l_error_rec.error_type := 'OTHERS';
1417 l_error_rec.sqlcode := SQLCODE;
1418 load_error_tbl(l_error_rec, px_error_tbl);
1419 END;
1420 EXIT WHEN (i = p_codv_tbl.LAST);
1421 i := p_codv_tbl.NEXT(i);
1422 END LOOP;
1423 END IF;
1424 -- Loop through the error_tbl to find the error with the highest severity
1425 -- and return it.
1426 x_return_status := find_highest_exception(px_error_tbl);
1427 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1428 EXCEPTION
1429 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1430 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1431 (
1432 l_api_name,
1433 G_PKG_NAME,
1434 'OKC_API.G_RET_STS_ERROR',
1435 x_msg_count,
1436 x_msg_data,
1437 '_PVT'
1438 );
1439 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1440 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1441 (
1442 l_api_name,
1443 G_PKG_NAME,
1444 'OKC_API.G_RET_STS_UNEXP_ERROR',
1445 x_msg_count,
1446 x_msg_data,
1447 '_PVT'
1448 );
1449 WHEN OTHERS THEN
1450 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1451 (
1452 l_api_name,
1453 G_PKG_NAME,
1454 'OTHERS',
1455 x_msg_count,
1456 x_msg_data,
1457 '_PVT'
1458 );
1459 END insert_row;
1460
1461 ----------------------------------------
1462 -- PL/SQL TBL insert_row for:CODV_TBL --
1463 ----------------------------------------
1464 PROCEDURE insert_row(
1465 p_api_version IN NUMBER,
1466 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1467 x_return_status OUT NOCOPY VARCHAR2,
1468 x_msg_count OUT NOCOPY NUMBER,
1469 x_msg_data OUT NOCOPY VARCHAR2,
1470 p_codv_tbl IN codv_tbl_type,
1471 x_codv_tbl OUT NOCOPY codv_tbl_type) IS
1472
1473 l_api_version CONSTANT NUMBER := 1;
1474 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_insert_row';
1475 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1476 l_error_tbl OKC_API.ERROR_TBL_TYPE;
1477 BEGIN
1478 OKC_API.init_msg_list(p_init_msg_list);
1479 -- Make sure PL/SQL table has records in it before passing
1480 IF (p_codv_tbl.COUNT > 0) THEN
1481 insert_row (
1482 p_api_version => p_api_version,
1483 p_init_msg_list => OKC_API.G_FALSE,
1484 x_return_status => x_return_status,
1485 x_msg_count => x_msg_count,
1486 x_msg_data => x_msg_data,
1487 p_codv_tbl => p_codv_tbl,
1488 x_codv_tbl => x_codv_tbl,
1489 px_error_tbl => l_error_tbl);
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 (
1496 l_api_name,
1497 G_PKG_NAME,
1498 'OKC_API.G_RET_STS_ERROR',
1499 x_msg_count,
1500 x_msg_data,
1501 '_PVT'
1502 );
1503 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1504 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1505 (
1506 l_api_name,
1507 G_PKG_NAME,
1508 'OKC_API.G_RET_STS_UNEXP_ERROR',
1509 x_msg_count,
1510 x_msg_data,
1511 '_PVT'
1512 );
1513 WHEN OTHERS THEN
1514 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1515 (
1516 l_api_name,
1517 G_PKG_NAME,
1518 'OTHERS',
1519 x_msg_count,
1520 x_msg_data,
1521 '_PVT'
1522 );
1523 END insert_row;
1524
1525 ---------------------------------------------------------------------------
1526 -- PROCEDURE lock_row
1527 ---------------------------------------------------------------------------
1528 --------------------------------------
1529 -- lock_row for:OKS_K_ORDER_DETAILS --
1530 --------------------------------------
1531 PROCEDURE lock_row(
1532 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1533 x_return_status OUT NOCOPY VARCHAR2,
1534 x_msg_count OUT NOCOPY NUMBER,
1535 x_msg_data OUT NOCOPY VARCHAR2,
1536 p_cod_rec IN cod_rec_type) IS
1537
1538 E_Resource_Busy EXCEPTION;
1539 PRAGMA EXCEPTION_INIT(E_Resource_Busy, -00054);
1540 CURSOR lock_csr (p_cod_rec IN cod_rec_type) IS
1541 SELECT OBJECT_VERSION_NUMBER
1542 FROM OKS_K_ORDER_DETAILS
1543 WHERE ID = p_cod_rec.id
1544 AND OBJECT_VERSION_NUMBER = p_cod_rec.object_version_number
1545 FOR UPDATE OF OBJECT_VERSION_NUMBER NOWAIT;
1546
1547 CURSOR lchk_csr (p_cod_rec IN cod_rec_type) IS
1548 SELECT OBJECT_VERSION_NUMBER
1549 FROM OKS_K_ORDER_DETAILS
1550 WHERE ID = p_cod_rec.id;
1551 l_api_version CONSTANT NUMBER := 1;
1552 l_api_name CONSTANT VARCHAR2(30) := 'B_lock_row';
1553 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1554 l_object_version_number OKS_K_ORDER_DETAILS.OBJECT_VERSION_NUMBER%TYPE;
1555 lc_object_version_number OKS_K_ORDER_DETAILS.OBJECT_VERSION_NUMBER%TYPE;
1556 l_row_notfound BOOLEAN := FALSE;
1557 lc_row_notfound BOOLEAN := FALSE;
1558 BEGIN
1559 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1560 p_init_msg_list,
1561 '_PVT',
1562 x_return_status);
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 BEGIN
1569 OPEN lock_csr(p_cod_rec);
1570 FETCH lock_csr INTO l_object_version_number;
1571 l_row_notfound := lock_csr%NOTFOUND;
1572 CLOSE lock_csr;
1573 EXCEPTION
1574 WHEN E_Resource_Busy THEN
1575 IF (lock_csr%ISOPEN) THEN
1576 CLOSE lock_csr;
1577 END IF;
1578 OKC_API.set_message(G_FND_APP,G_FORM_UNABLE_TO_RESERVE_REC);
1579 RAISE APP_EXCEPTIONS.RECORD_LOCK_EXCEPTION;
1580 END;
1581
1582 IF ( l_row_notfound ) THEN
1583 OPEN lchk_csr(p_cod_rec);
1584 FETCH lchk_csr INTO lc_object_version_number;
1585 lc_row_notfound := lchk_csr%NOTFOUND;
1586 CLOSE lchk_csr;
1587 END IF;
1588 IF (lc_row_notfound) THEN
1589 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_DELETED);
1590 RAISE OKC_API.G_EXCEPTION_ERROR;
1591 ELSIF lc_object_version_number > p_cod_rec.object_version_number THEN
1592 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
1593 RAISE OKC_API.G_EXCEPTION_ERROR;
1594 ELSIF lc_object_version_number <> p_cod_rec.object_version_number THEN
1595 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
1596 RAISE OKC_API.G_EXCEPTION_ERROR;
1597 ELSIF lc_object_version_number = -1 THEN
1598 OKC_API.set_message(G_APP_NAME,G_RECORD_LOGICALLY_DELETED);
1599 RAISE OKC_API.G_EXCEPTION_ERROR;
1600 END IF;
1601 x_return_status := l_return_status;
1602 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1603 EXCEPTION
1604 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1605 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1606 (
1607 l_api_name,
1608 G_PKG_NAME,
1609 'OKC_API.G_RET_STS_ERROR',
1610 x_msg_count,
1611 x_msg_data,
1612 '_PVT'
1613 );
1614 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1615 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1616 (
1617 l_api_name,
1618 G_PKG_NAME,
1619 'OKC_API.G_RET_STS_UNEXP_ERROR',
1620 x_msg_count,
1621 x_msg_data,
1622 '_PVT'
1623 );
1624 WHEN OTHERS THEN
1625 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1626 (
1627 l_api_name,
1628 G_PKG_NAME,
1629 'OTHERS',
1630 x_msg_count,
1631 x_msg_data,
1632 '_PVT'
1633 );
1634 END lock_row;
1635 -----------------------------------------
1636 -- lock_row for: OKS_K_ORDER_DETAILS_V --
1637 -----------------------------------------
1638 PROCEDURE lock_row(
1639 p_api_version IN NUMBER,
1640 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1641 x_return_status OUT NOCOPY VARCHAR2,
1642 x_msg_count OUT NOCOPY NUMBER,
1643 x_msg_data OUT NOCOPY VARCHAR2,
1644 p_codv_rec IN codv_rec_type) IS
1645
1646 l_api_version CONSTANT NUMBER := 1;
1647 l_api_name CONSTANT VARCHAR2(30) := 'V_lock_row';
1648 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1649 l_cod_rec cod_rec_type;
1650 BEGIN
1651 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1652 G_PKG_NAME,
1653 p_init_msg_list,
1654 l_api_version,
1655 p_api_version,
1656 '_PVT',
1657 x_return_status);
1658 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1659 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1660 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1661 RAISE OKC_API.G_EXCEPTION_ERROR;
1662 END IF;
1663 -----------------------------------------
1664 -- Move VIEW record to "Child" records --
1665 -----------------------------------------
1666 migrate(p_codv_rec, l_cod_rec);
1667 ---------------------------------------------
1668 -- Call the LOCK_ROW for each child record --
1669 ---------------------------------------------
1670 lock_row(
1671 p_init_msg_list,
1672 l_return_status,
1673 x_msg_count,
1674 x_msg_data,
1675 l_cod_rec
1676 );
1677 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1678 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1679 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1680 RAISE OKC_API.G_EXCEPTION_ERROR;
1681 END IF;
1682 x_return_status := l_return_status;
1683 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1684 EXCEPTION
1685 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1686 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1687 (
1688 l_api_name,
1689 G_PKG_NAME,
1690 'OKC_API.G_RET_STS_ERROR',
1691 x_msg_count,
1692 x_msg_data,
1693 '_PVT'
1694 );
1695 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1696 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1697 (
1698 l_api_name,
1699 G_PKG_NAME,
1700 'OKC_API.G_RET_STS_UNEXP_ERROR',
1701 x_msg_count,
1702 x_msg_data,
1703 '_PVT'
1704 );
1705 WHEN OTHERS THEN
1706 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1707 (
1708 l_api_name,
1709 G_PKG_NAME,
1710 'OTHERS',
1711 x_msg_count,
1712 x_msg_data,
1713 '_PVT'
1714 );
1715 END lock_row;
1716 --------------------------------------
1717 -- PL/SQL TBL lock_row for:CODV_TBL --
1718 --------------------------------------
1719 PROCEDURE lock_row(
1720 p_api_version IN NUMBER,
1721 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1722 x_return_status OUT NOCOPY VARCHAR2,
1723 x_msg_count OUT NOCOPY NUMBER,
1724 x_msg_data OUT NOCOPY VARCHAR2,
1725 p_codv_tbl IN codv_tbl_type,
1726 px_error_tbl IN OUT NOCOPY OKC_API.ERROR_TBL_TYPE) IS
1727
1728 l_api_version CONSTANT NUMBER := 1;
1729 l_api_name CONSTANT VARCHAR2(30) := 'V_error_tbl_lock_row';
1730 i NUMBER := 0;
1731 BEGIN
1732 OKC_API.init_msg_list(p_init_msg_list);
1733 -- Make sure PL/SQL table has recrods in it before passing
1734 IF (p_codv_tbl.COUNT > 0) THEN
1735 i := p_codv_tbl.FIRST;
1736 LOOP
1737 DECLARE
1738 l_error_rec OKC_API.ERROR_REC_TYPE;
1739 BEGIN
1740 l_error_rec.api_name := l_api_name;
1741 l_error_rec.api_package := G_PKG_NAME;
1742 l_error_rec.idx := i;
1743 lock_row(
1744 p_api_version => p_api_version,
1745 p_init_msg_list => OKC_API.G_FALSE,
1746 x_return_status => l_error_rec.error_type,
1747 x_msg_count => l_error_rec.msg_count,
1748 x_msg_data => l_error_rec.msg_data,
1749 p_codv_rec => p_codv_tbl(i));
1750 IF (l_error_rec.error_type <> OKC_API.G_RET_STS_SUCCESS) THEN
1751 l_error_rec.sqlcode := SQLCODE;
1752 load_error_tbl(l_error_rec, px_error_tbl);
1753 ELSE
1754 x_msg_count := l_error_rec.msg_count;
1755 x_msg_data := l_error_rec.msg_data;
1756 END IF;
1757 EXCEPTION
1758 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1759 l_error_rec.error_type := OKC_API.G_RET_STS_ERROR;
1760 l_error_rec.sqlcode := SQLCODE;
1761 load_error_tbl(l_error_rec, px_error_tbl);
1762 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1763 l_error_rec.error_type := OKC_API.G_RET_STS_UNEXP_ERROR;
1764 l_error_rec.sqlcode := SQLCODE;
1765 load_error_tbl(l_error_rec, px_error_tbl);
1766 WHEN OTHERS THEN
1767 l_error_rec.error_type := 'OTHERS';
1768 l_error_rec.sqlcode := SQLCODE;
1769 load_error_tbl(l_error_rec, px_error_tbl);
1770 END;
1771 EXIT WHEN (i = p_codv_tbl.LAST);
1772 i := p_codv_tbl.NEXT(i);
1773 END LOOP;
1774 END IF;
1775 -- Loop through the error_tbl to find the error with the highest severity
1776 -- and return it.
1777 x_return_status := find_highest_exception(px_error_tbl);
1778 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1779 EXCEPTION
1780 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1781 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1782 (
1783 l_api_name,
1784 G_PKG_NAME,
1785 'OKC_API.G_RET_STS_ERROR',
1786 x_msg_count,
1787 x_msg_data,
1788 '_PVT'
1789 );
1790 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1791 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1792 (
1793 l_api_name,
1794 G_PKG_NAME,
1795 'OKC_API.G_RET_STS_UNEXP_ERROR',
1796 x_msg_count,
1797 x_msg_data,
1798 '_PVT'
1799 );
1800 WHEN OTHERS THEN
1801 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1802 (
1803 l_api_name,
1804 G_PKG_NAME,
1805 'OTHERS',
1806 x_msg_count,
1807 x_msg_data,
1808 '_PVT'
1809 );
1810 END lock_row;
1811 --------------------------------------
1812 -- PL/SQL TBL lock_row for:CODV_TBL --
1813 --------------------------------------
1814 PROCEDURE lock_row(
1815 p_api_version IN NUMBER,
1816 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1817 x_return_status OUT NOCOPY VARCHAR2,
1818 x_msg_count OUT NOCOPY NUMBER,
1819 x_msg_data OUT NOCOPY VARCHAR2,
1820 p_codv_tbl IN codv_tbl_type) IS
1821
1822 l_api_version CONSTANT NUMBER := 1;
1823 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_lock_row';
1824 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1825 l_error_tbl OKC_API.ERROR_TBL_TYPE;
1826 BEGIN
1827 OKC_API.init_msg_list(p_init_msg_list);
1828 -- Make sure PL/SQL table has recrods in it before passing
1829 IF (p_codv_tbl.COUNT > 0) THEN
1830 lock_row(
1831 p_api_version => p_api_version,
1832 p_init_msg_list => OKC_API.G_FALSE,
1833 x_return_status => x_return_status,
1834 x_msg_count => x_msg_count,
1835 x_msg_data => x_msg_data,
1836 p_codv_tbl => p_codv_tbl,
1837 px_error_tbl => l_error_tbl);
1838 END IF;
1839 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1840 EXCEPTION
1841 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1842 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1843 (
1844 l_api_name,
1845 G_PKG_NAME,
1846 'OKC_API.G_RET_STS_ERROR',
1847 x_msg_count,
1848 x_msg_data,
1849 '_PVT'
1850 );
1851 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1852 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1853 (
1854 l_api_name,
1855 G_PKG_NAME,
1856 'OKC_API.G_RET_STS_UNEXP_ERROR',
1857 x_msg_count,
1858 x_msg_data,
1859 '_PVT'
1860 );
1861 WHEN OTHERS THEN
1862 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1863 (
1864 l_api_name,
1865 G_PKG_NAME,
1866 'OTHERS',
1867 x_msg_count,
1868 x_msg_data,
1869 '_PVT'
1870 );
1871 END lock_row;
1872 ---------------------------------------------------------------------------
1873 -- PROCEDURE update_row
1874 ---------------------------------------------------------------------------
1875 ----------------------------------------
1876 -- update_row for:OKS_K_ORDER_DETAILS --
1877 ----------------------------------------
1878 PROCEDURE update_row(
1879 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1880 x_return_status OUT NOCOPY VARCHAR2,
1881 x_msg_count OUT NOCOPY NUMBER,
1882 x_msg_data OUT NOCOPY VARCHAR2,
1883 p_cod_rec IN cod_rec_type,
1884 x_cod_rec OUT NOCOPY cod_rec_type) IS
1885
1886 l_api_version CONSTANT NUMBER := 1;
1887 l_api_name CONSTANT VARCHAR2(30) := 'B_update_row';
1888 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1889 l_cod_rec cod_rec_type := p_cod_rec;
1890 l_def_cod_rec cod_rec_type;
1891 l_row_notfound BOOLEAN := TRUE;
1892 ----------------------------------
1893 -- FUNCTION populate_new_record --
1894 ----------------------------------
1895 FUNCTION populate_new_record (
1896 p_cod_rec IN cod_rec_type,
1897 x_cod_rec OUT NOCOPY cod_rec_type
1898 ) RETURN VARCHAR2 IS
1899 l_cod_rec cod_rec_type;
1900 l_row_notfound BOOLEAN := TRUE;
1901 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1902 BEGIN
1903 x_cod_rec := p_cod_rec;
1904 -- Get current database values
1905 l_cod_rec := get_rec(p_cod_rec, l_return_status);
1906 IF (l_return_status = OKC_API.G_RET_STS_SUCCESS) THEN
1907 IF (x_cod_rec.id = OKC_API.G_MISS_NUM)
1908 THEN
1909 x_cod_rec.id := l_cod_rec.id;
1910 END IF;
1911 IF (x_cod_rec.cod_type = OKC_API.G_MISS_CHAR)
1912 THEN
1913 x_cod_rec.cod_type := l_cod_rec.cod_type;
1914 END IF;
1915 IF (x_cod_rec.link_order_header_id = OKC_API.G_MISS_NUM)
1916 THEN
1917 x_cod_rec.link_order_header_id := l_cod_rec.link_order_header_id;
1918 END IF;
1919 IF (x_cod_rec.order_line_id1 = OKC_API.G_MISS_CHAR)
1920 THEN
1921 x_cod_rec.order_line_id1 := l_cod_rec.order_line_id1;
1922 END IF;
1923 IF (x_cod_rec.order_line_id2 = OKC_API.G_MISS_CHAR)
1924 THEN
1925 x_cod_rec.order_line_id2 := l_cod_rec.order_line_id2;
1926 END IF;
1927 IF (x_cod_rec.apply_all_yn = OKC_API.G_MISS_CHAR)
1928 THEN
1929 x_cod_rec.apply_all_yn := l_cod_rec.apply_all_yn;
1930 END IF;
1931 IF (x_cod_rec.renewal_type = OKC_API.G_MISS_CHAR)
1932 THEN
1933 x_cod_rec.renewal_type := l_cod_rec.renewal_type;
1934 END IF;
1935 IF (x_cod_rec.line_renewal_type = OKC_API.G_MISS_CHAR)
1936 THEN
1937 x_cod_rec.line_renewal_type := l_cod_rec.line_renewal_type;
1938 END IF;
1939 IF (x_cod_rec.end_date = OKC_API.G_MISS_DATE)
1940 THEN
1941 x_cod_rec.end_date := l_cod_rec.end_date;
1942 END IF;
1943 IF (x_cod_rec.object_version_number = OKC_API.G_MISS_NUM)
1944 THEN
1945 x_cod_rec.object_version_number := l_cod_rec.object_version_number;
1946 END IF;
1947 IF (x_cod_rec.created_by = OKC_API.G_MISS_NUM)
1948 THEN
1949 x_cod_rec.created_by := l_cod_rec.created_by;
1950 END IF;
1951 IF (x_cod_rec.creation_date = OKC_API.G_MISS_DATE)
1952 THEN
1953 x_cod_rec.creation_date := l_cod_rec.creation_date;
1954 END IF;
1955 IF (x_cod_rec.last_updated_by = OKC_API.G_MISS_NUM)
1956 THEN
1957 x_cod_rec.last_updated_by := l_cod_rec.last_updated_by;
1958 END IF;
1959 IF (x_cod_rec.last_update_date = OKC_API.G_MISS_DATE)
1960 THEN
1961 x_cod_rec.last_update_date := l_cod_rec.last_update_date;
1962 END IF;
1963 IF (x_cod_rec.po_required_yn = OKC_API.G_MISS_CHAR)
1964 THEN
1965 x_cod_rec.po_required_yn := l_cod_rec.po_required_yn;
1966 END IF;
1967 IF (x_cod_rec.renewal_pricing_type = OKC_API.G_MISS_CHAR)
1968 THEN
1969 x_cod_rec.renewal_pricing_type := l_cod_rec.renewal_pricing_type;
1970 END IF;
1971 IF (x_cod_rec.markup_percent = OKC_API.G_MISS_NUM)
1972 THEN
1973 x_cod_rec.markup_percent := l_cod_rec.markup_percent;
1974 END IF;
1975 IF (x_cod_rec.price_list_id1 = OKC_API.G_MISS_CHAR)
1976 THEN
1977 x_cod_rec.price_list_id1 := l_cod_rec.price_list_id1;
1978 END IF;
1979 IF (x_cod_rec.price_list_id2 = OKC_API.G_MISS_CHAR)
1980 THEN
1981 x_cod_rec.price_list_id2 := l_cod_rec.price_list_id2;
1982 END IF;
1983 IF (x_cod_rec.link_ord_line_id1 = OKC_API.G_MISS_CHAR)
1984 THEN
1985 x_cod_rec.link_ord_line_id1 := l_cod_rec.link_ord_line_id1;
1986 END IF;
1987 IF (x_cod_rec.link_ord_line_id2 = OKC_API.G_MISS_CHAR)
1988 THEN
1989 x_cod_rec.link_ord_line_id2 := l_cod_rec.link_ord_line_id2;
1990 END IF;
1991 IF (x_cod_rec.link_chr_id = OKC_API.G_MISS_NUM)
1992 THEN
1993 x_cod_rec.link_chr_id := l_cod_rec.link_chr_id;
1994 END IF;
1995 IF (x_cod_rec.link_cle_id = OKC_API.G_MISS_NUM)
1996 THEN
1997 x_cod_rec.link_cle_id := l_cod_rec.link_cle_id;
1998 END IF;
1999 IF (x_cod_rec.chr_id = OKC_API.G_MISS_NUM)
2000 THEN
2001 x_cod_rec.chr_id := l_cod_rec.chr_id;
2002 END IF;
2003 IF (x_cod_rec.cle_id = OKC_API.G_MISS_NUM)
2004 THEN
2005 x_cod_rec.cle_id := l_cod_rec.cle_id;
2006 END IF;
2007 IF (x_cod_rec.cod_id = OKC_API.G_MISS_NUM)
2008 THEN
2009 x_cod_rec.cod_id := l_cod_rec.cod_id;
2010 END IF;
2011 IF (x_cod_rec.contact_id = OKC_API.G_MISS_NUM)
2012 THEN
2013 x_cod_rec.contact_id := l_cod_rec.contact_id;
2014 END IF;
2015 IF (x_cod_rec.site_id = OKC_API.G_MISS_NUM)
2016 THEN
2017 x_cod_rec.site_id := l_cod_rec.site_id;
2018 END IF;
2019 IF (x_cod_rec.email_id = OKC_API.G_MISS_NUM)
2020 THEN
2021 x_cod_rec.email_id := l_cod_rec.email_id;
2022 END IF;
2023 IF (x_cod_rec.phone_id = OKC_API.G_MISS_NUM)
2024 THEN
2025 x_cod_rec.phone_id := l_cod_rec.phone_id;
2026 END IF;
2027 IF (x_cod_rec.fax_id = OKC_API.G_MISS_NUM)
2028 THEN
2029 x_cod_rec.fax_id := l_cod_rec.fax_id;
2030 END IF;
2031 IF (x_cod_rec.billing_profile_id = OKC_API.G_MISS_NUM)
2032 THEN
2033 x_cod_rec.billing_profile_id := l_cod_rec.billing_profile_id;
2034 END IF;
2035 IF (x_cod_rec.RENEWAL_APPROVAL_FLAG = OKC_API.G_MISS_CHAR)
2036 THEN
2037 x_cod_rec.RENEWAL_APPROVAL_FLAG := l_cod_rec.RENEWAL_APPROVAL_FLAG;
2038 END IF;
2039
2040 END IF;
2041 RETURN(l_return_status);
2042 END populate_new_record;
2043 --------------------------------------------
2044 -- Set_Attributes for:OKS_K_ORDER_DETAILS --
2045 --------------------------------------------
2046 FUNCTION Set_Attributes (
2047 p_cod_rec IN cod_rec_type,
2048 x_cod_rec OUT NOCOPY cod_rec_type
2049 ) RETURN VARCHAR2 IS
2050 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2051 BEGIN
2052 x_cod_rec := p_cod_rec;
2053 x_cod_rec.OBJECT_VERSION_NUMBER := p_cod_rec.OBJECT_VERSION_NUMBER + 1;
2054 RETURN(l_return_status);
2055 END Set_Attributes;
2056 BEGIN
2057 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
2058 p_init_msg_list,
2059 '_PVT',
2060 x_return_status);
2061 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2062 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2063 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2064 RAISE OKC_API.G_EXCEPTION_ERROR;
2065 END IF;
2066 --- Setting item attributes
2067 l_return_status := Set_Attributes(
2068 p_cod_rec, -- IN
2069 l_cod_rec); -- OUT
2070 --- If any errors happen abort API
2071 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2072 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2073 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2074 RAISE OKC_API.G_EXCEPTION_ERROR;
2075 END IF;
2076 l_return_status := populate_new_record(l_cod_rec, l_def_cod_rec);
2077 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2078 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2079 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2080 RAISE OKC_API.G_EXCEPTION_ERROR;
2081 END IF;
2082 UPDATE OKS_K_ORDER_DETAILS
2083 SET COD_TYPE = l_def_cod_rec.cod_type,
2084 LINK_ORDER_HEADER_ID = l_def_cod_rec.link_order_header_id,
2085 ORDER_LINE_ID1 = l_def_cod_rec.order_line_id1,
2086 ORDER_LINE_ID2 = l_def_cod_rec.order_line_id2,
2087 APPLY_ALL_YN = l_def_cod_rec.apply_all_yn,
2088 RENEWAL_TYPE = l_def_cod_rec.renewal_type,
2089 LINE_RENEWAL_TYPE = l_def_cod_rec.line_renewal_type,
2090 END_DATE = l_def_cod_rec.end_date,
2091 OBJECT_VERSION_NUMBER = l_def_cod_rec.object_version_number,
2092 CREATED_BY = l_def_cod_rec.created_by,
2093 CREATION_DATE = l_def_cod_rec.creation_date,
2094 LAST_UPDATED_BY = l_def_cod_rec.last_updated_by,
2095 LAST_UPDATE_DATE = l_def_cod_rec.last_update_date,
2096 PO_REQUIRED_YN = l_def_cod_rec.po_required_yn,
2097 RENEWAL_PRICING_TYPE = l_def_cod_rec.renewal_pricing_type,
2098 MARKUP_PERCENT = l_def_cod_rec.markup_percent,
2099 PRICE_LIST_ID1 = l_def_cod_rec.price_list_id1,
2100 PRICE_LIST_ID2 = l_def_cod_rec.price_list_id2,
2101 LINK_ORD_LINE_ID1 = l_def_cod_rec.link_ord_line_id1,
2102 LINK_ORD_LINE_ID2 = l_def_cod_rec.link_ord_line_id2,
2103 LINK_CHR_ID = l_def_cod_rec.link_chr_id,
2104 LINK_CLE_ID = l_def_cod_rec.link_cle_id,
2105 CHR_ID = l_def_cod_rec.chr_id,
2106 CLE_ID = l_def_cod_rec.cle_id,
2107 COD_ID = l_def_cod_rec.cod_id,
2108 CONTACT_ID = l_def_cod_rec.contact_id,
2109 SITE_ID = l_def_cod_rec.site_id,
2110 EMAIL_ID = l_def_cod_rec.email_id,
2111 PHONE_ID = l_def_cod_rec.phone_id,
2112 FAX_ID = l_def_cod_rec.fax_id,
2113 BILLING_PROFILE_ID = l_def_cod_rec.billing_profile_id,
2114 RENEWAL_APPROVAL_FLAG = l_def_cod_rec.RENEWAL_APPROVAL_FLAG
2115 WHERE ID = l_def_cod_rec.id;
2116
2117 x_cod_rec := l_cod_rec;
2118 x_return_status := l_return_status;
2119 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2120 EXCEPTION
2121 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2122 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2123 (
2124 l_api_name,
2125 G_PKG_NAME,
2126 'OKC_API.G_RET_STS_ERROR',
2127 x_msg_count,
2128 x_msg_data,
2129 '_PVT'
2130 );
2131 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2132 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2133 (
2134 l_api_name,
2135 G_PKG_NAME,
2136 'OKC_API.G_RET_STS_UNEXP_ERROR',
2137 x_msg_count,
2138 x_msg_data,
2139 '_PVT'
2140 );
2141 WHEN OTHERS THEN
2142 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2143 (
2144 l_api_name,
2145 G_PKG_NAME,
2146 'OTHERS',
2147 x_msg_count,
2148 x_msg_data,
2149 '_PVT'
2150 );
2151 END update_row;
2152 ------------------------------------------
2153 -- update_row for:OKS_K_ORDER_DETAILS_V --
2154 ------------------------------------------
2155 PROCEDURE update_row(
2156 p_api_version IN NUMBER,
2157 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
2158 x_return_status OUT NOCOPY VARCHAR2,
2159 x_msg_count OUT NOCOPY NUMBER,
2160 x_msg_data OUT NOCOPY VARCHAR2,
2161 p_codv_rec IN codv_rec_type,
2162 x_codv_rec OUT NOCOPY codv_rec_type) IS
2163
2164 l_api_version CONSTANT NUMBER := 1;
2165 l_api_name CONSTANT VARCHAR2(30) := 'V_update_row';
2166 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2167 l_codv_rec codv_rec_type := p_codv_rec;
2168 l_def_codv_rec codv_rec_type;
2169 l_db_codv_rec codv_rec_type;
2170 l_cod_rec cod_rec_type;
2171 lx_cod_rec cod_rec_type;
2172 -------------------------------
2173 -- FUNCTION fill_who_columns --
2174 -------------------------------
2175 FUNCTION fill_who_columns (
2176 p_codv_rec IN codv_rec_type
2177 ) RETURN codv_rec_type IS
2178 l_codv_rec codv_rec_type := p_codv_rec;
2179 BEGIN
2180 l_codv_rec.LAST_UPDATE_DATE := SYSDATE;
2181 l_codv_rec.LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
2182 --l_codv_rec.LAST_UPDATE_LOGIN := FND_GLOBAL.LOGIN_ID;
2183 RETURN(l_codv_rec);
2184 END fill_who_columns;
2185 ----------------------------------
2186 -- FUNCTION populate_new_record --
2187 ----------------------------------
2188 FUNCTION populate_new_record (
2189 p_codv_rec IN codv_rec_type,
2190 x_codv_rec OUT NOCOPY codv_rec_type
2191 ) RETURN VARCHAR2 IS
2192 l_row_notfound BOOLEAN := TRUE;
2193 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2194 BEGIN
2195 x_codv_rec := p_codv_rec;
2196 -- Get current database values
2197 -- NOTE: Never assign the OBJECT_VERSION_NUMBER. Force the user to pass it
2198 -- so it may be verified through LOCK_ROW.
2199 l_db_codv_rec := get_rec(p_codv_rec, l_return_status);
2200 IF (l_return_status = OKC_API.G_RET_STS_SUCCESS) THEN
2201 IF (x_codv_rec.id = OKC_API.G_MISS_NUM)
2202 THEN
2203 x_codv_rec.id := l_db_codv_rec.id;
2204 END IF;
2205 IF (x_codv_rec.cod_id = OKC_API.G_MISS_NUM)
2206 THEN
2207 x_codv_rec.cod_id := l_db_codv_rec.cod_id;
2208 END IF;
2209 IF (x_codv_rec.apply_all_yn = OKC_API.G_MISS_CHAR)
2210 THEN
2211 x_codv_rec.apply_all_yn := l_db_codv_rec.apply_all_yn;
2212 END IF;
2213 IF (x_codv_rec.line_renewal_type = OKC_API.G_MISS_CHAR)
2214 THEN
2215 x_codv_rec.line_renewal_type := l_db_codv_rec.line_renewal_type;
2216 END IF;
2217 IF (x_codv_rec.renewal_type = OKC_API.G_MISS_CHAR)
2218 THEN
2219 x_codv_rec.renewal_type := l_db_codv_rec.renewal_type;
2220 END IF;
2221 IF (x_codv_rec.po_required_yn = OKC_API.G_MISS_CHAR)
2222 THEN
2223 x_codv_rec.po_required_yn := l_db_codv_rec.po_required_yn;
2224 END IF;
2225 IF (x_codv_rec.renewal_pricing_type = OKC_API.G_MISS_CHAR)
2226 THEN
2227 x_codv_rec.renewal_pricing_type := l_db_codv_rec.renewal_pricing_type;
2228 END IF;
2229 IF (x_codv_rec.markup_percent = OKC_API.G_MISS_NUM)
2230 THEN
2231 x_codv_rec.markup_percent := l_db_codv_rec.markup_percent;
2232 END IF;
2233 IF (x_codv_rec.link_order_header_id = OKC_API.G_MISS_NUM)
2234 THEN
2235 x_codv_rec.link_order_header_id := l_db_codv_rec.link_order_header_id;
2236 END IF;
2237 IF (x_codv_rec.end_date = OKC_API.G_MISS_DATE)
2238 THEN
2239 x_codv_rec.end_date := l_db_codv_rec.end_date;
2240 END IF;
2241 IF (x_codv_rec.cod_type = OKC_API.G_MISS_CHAR)
2242 THEN
2243 x_codv_rec.cod_type := l_db_codv_rec.cod_type;
2244 END IF;
2245 IF (x_codv_rec.order_line_id1 = OKC_API.G_MISS_CHAR)
2246 THEN
2247 x_codv_rec.order_line_id1 := l_db_codv_rec.order_line_id1;
2248 END IF;
2249 IF (x_codv_rec.order_line_id2 = OKC_API.G_MISS_CHAR)
2250 THEN
2251 x_codv_rec.order_line_id2 := l_db_codv_rec.order_line_id2;
2252 END IF;
2253 IF (x_codv_rec.created_by = OKC_API.G_MISS_NUM)
2254 THEN
2255 x_codv_rec.created_by := l_db_codv_rec.created_by;
2256 END IF;
2257 IF (x_codv_rec.creation_date = OKC_API.G_MISS_DATE)
2258 THEN
2259 x_codv_rec.creation_date := l_db_codv_rec.creation_date;
2260 END IF;
2261 IF (x_codv_rec.last_updated_by = OKC_API.G_MISS_NUM)
2262 THEN
2263 x_codv_rec.last_updated_by := l_db_codv_rec.last_updated_by;
2264 END IF;
2265 IF (x_codv_rec.last_update_date = OKC_API.G_MISS_DATE)
2266 THEN
2267 x_codv_rec.last_update_date := l_db_codv_rec.last_update_date;
2268 END IF;
2269 IF (x_codv_rec.link_ord_line_id1 = OKC_API.G_MISS_CHAR)
2270 THEN
2271 x_codv_rec.link_ord_line_id1 := l_db_codv_rec.link_ord_line_id1;
2272 END IF;
2273 IF (x_codv_rec.link_ord_line_id2 = OKC_API.G_MISS_CHAR)
2274 THEN
2275 x_codv_rec.link_ord_line_id2 := l_db_codv_rec.link_ord_line_id2;
2276 END IF;
2277 IF (x_codv_rec.link_chr_id = OKC_API.G_MISS_NUM)
2278 THEN
2279 x_codv_rec.link_chr_id := l_db_codv_rec.link_chr_id;
2280 END IF;
2281 IF (x_codv_rec.link_cle_id = OKC_API.G_MISS_NUM)
2282 THEN
2283 x_codv_rec.link_cle_id := l_db_codv_rec.link_cle_id;
2284 END IF;
2285 IF (x_codv_rec.price_list_id1 = OKC_API.G_MISS_CHAR)
2286 THEN
2287 x_codv_rec.price_list_id1 := l_db_codv_rec.price_list_id1;
2288 END IF;
2289 IF (x_codv_rec.price_list_id2 = OKC_API.G_MISS_CHAR)
2290 THEN
2291 x_codv_rec.price_list_id2 := l_db_codv_rec.price_list_id2;
2292 END IF;
2293 IF (x_codv_rec.chr_id = OKC_API.G_MISS_NUM)
2294 THEN
2295 x_codv_rec.chr_id := l_db_codv_rec.chr_id;
2296 END IF;
2297 IF (x_codv_rec.cle_id = OKC_API.G_MISS_NUM)
2298 THEN
2299 x_codv_rec.cle_id := l_db_codv_rec.cle_id;
2300 END IF;
2301 IF (x_codv_rec.contact_id = OKC_API.G_MISS_NUM)
2302 THEN
2303 x_codv_rec.contact_id := l_db_codv_rec.contact_id;
2304 END IF;
2305 IF (x_codv_rec.site_id = OKC_API.G_MISS_NUM)
2306 THEN
2307 x_codv_rec.site_id := l_db_codv_rec.site_id;
2308 END IF;
2309 IF (x_codv_rec.email_id = OKC_API.G_MISS_NUM)
2310 THEN
2311 x_codv_rec.email_id := l_db_codv_rec.email_id;
2312 END IF;
2313 IF (x_codv_rec.phone_id = OKC_API.G_MISS_NUM)
2314 THEN
2315 x_codv_rec.phone_id := l_db_codv_rec.phone_id;
2316 END IF;
2317 IF (x_codv_rec.fax_id = OKC_API.G_MISS_NUM)
2318 THEN
2319 x_codv_rec.fax_id := l_db_codv_rec.fax_id;
2320 END IF;
2321 IF (x_codv_rec.billing_profile_id = OKC_API.G_MISS_NUM)
2322 THEN
2323 x_codv_rec.billing_profile_id := l_db_codv_rec.billing_profile_id;
2324 END IF;
2325
2326 IF (x_codv_rec.RENEWAL_APPROVAL_FLAG = OKC_API.G_MISS_CHAR)
2327 THEN
2328 x_codv_rec.RENEWAL_APPROVAL_FLAG := l_db_codv_rec.RENEWAL_APPROVAL_FLAG;
2329 END IF;
2330
2331 END IF;
2332 RETURN(l_return_status);
2333 END populate_new_record;
2334 ----------------------------------------------
2335 -- Set_Attributes for:OKS_K_ORDER_DETAILS_V --
2336 ----------------------------------------------
2337 FUNCTION Set_Attributes (
2338 p_codv_rec IN codv_rec_type,
2339 x_codv_rec OUT NOCOPY codv_rec_type
2340 ) RETURN VARCHAR2 IS
2341 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2342 BEGIN
2343 x_codv_rec := p_codv_rec;
2344 RETURN(l_return_status);
2345 END Set_Attributes;
2346 BEGIN
2347 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
2348 G_PKG_NAME,
2349 p_init_msg_list,
2350 l_api_version,
2351 p_api_version,
2352 '_PVT',
2353 x_return_status);
2354 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2355 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2356 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2357 RAISE OKC_API.G_EXCEPTION_ERROR;
2358 END IF;
2359 --- Setting item attributes
2360 l_return_status := Set_Attributes(
2361 p_codv_rec, -- IN
2362 x_codv_rec); -- OUT
2363 --- If any errors happen abort API
2364 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2365 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2366 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2367 RAISE OKC_API.G_EXCEPTION_ERROR;
2368 END IF;
2369 l_return_status := populate_new_record(l_codv_rec, l_def_codv_rec);
2370 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2371 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2372 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2373 RAISE OKC_API.G_EXCEPTION_ERROR;
2374 END IF;
2375 l_def_codv_rec := fill_who_columns(l_def_codv_rec);
2376 --- Validate all non-missing attributes (Item Level Validation)
2377 l_return_status := Validate_Attributes(l_def_codv_rec);
2378 --- If any errors happen abort API
2379 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2380 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2381 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2382 RAISE OKC_API.G_EXCEPTION_ERROR;
2383 END IF;
2384 l_return_status := Validate_Record(l_def_codv_rec, l_db_codv_rec);
2385 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2386 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2387 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2388 RAISE OKC_API.G_EXCEPTION_ERROR;
2389 END IF;
2390
2391 -- Lock the Record
2392 lock_row(
2393 p_api_version => p_api_version,
2394 p_init_msg_list => p_init_msg_list,
2395 x_return_status => l_return_status,
2396 x_msg_count => x_msg_count,
2397 x_msg_data => x_msg_data,
2398 p_codv_rec => p_codv_rec);
2399 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2400 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2401 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2402 RAISE OKC_API.G_EXCEPTION_ERROR;
2403 END IF;
2404
2405 -----------------------------------------
2406 -- Move VIEW record to "Child" records --
2407 -----------------------------------------
2408 migrate(l_def_codv_rec, l_cod_rec);
2409 -----------------------------------------------
2410 -- Call the UPDATE_ROW for each child record --
2411 -----------------------------------------------
2412 update_row(
2413 p_init_msg_list,
2414 l_return_status,
2415 x_msg_count,
2416 x_msg_data,
2417 l_cod_rec,
2418 lx_cod_rec
2419 );
2420 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2421 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2422 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2423 RAISE OKC_API.G_EXCEPTION_ERROR;
2424 END IF;
2425 migrate(lx_cod_rec, l_def_codv_rec);
2426 x_codv_rec := l_def_codv_rec;
2427 x_return_status := l_return_status;
2428 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2429 EXCEPTION
2430 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2431 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2432 (
2433 l_api_name,
2434 G_PKG_NAME,
2435 'OKC_API.G_RET_STS_ERROR',
2436 x_msg_count,
2437 x_msg_data,
2438 '_PVT'
2439 );
2440 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2441 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2442 (
2443 l_api_name,
2444 G_PKG_NAME,
2445 'OKC_API.G_RET_STS_UNEXP_ERROR',
2446 x_msg_count,
2447 x_msg_data,
2448 '_PVT'
2449 );
2450 WHEN OTHERS THEN
2451 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2452 (
2453 l_api_name,
2454 G_PKG_NAME,
2455 'OTHERS',
2456 x_msg_count,
2457 x_msg_data,
2458 '_PVT'
2459 );
2460 END update_row;
2461 ----------------------------------------
2462 -- PL/SQL TBL update_row for:codv_tbl --
2463 ----------------------------------------
2464 PROCEDURE update_row(
2465 p_api_version IN NUMBER,
2466 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
2467 x_return_status OUT NOCOPY VARCHAR2,
2468 x_msg_count OUT NOCOPY NUMBER,
2469 x_msg_data OUT NOCOPY VARCHAR2,
2470 p_codv_tbl IN codv_tbl_type,
2471 x_codv_tbl OUT NOCOPY codv_tbl_type,
2472 px_error_tbl IN OUT NOCOPY OKC_API.ERROR_TBL_TYPE) IS
2473
2474 l_api_version CONSTANT NUMBER := 1;
2475 l_api_name CONSTANT VARCHAR2(30) := 'V_error_tbl_update_row';
2476 i NUMBER := 0;
2477 BEGIN
2478 OKC_API.init_msg_list(p_init_msg_list);
2479 -- Make sure PL/SQL table has records in it before passing
2480 IF (p_codv_tbl.COUNT > 0) THEN
2481 i := p_codv_tbl.FIRST;
2482 LOOP
2483 DECLARE
2484 l_error_rec OKC_API.ERROR_REC_TYPE;
2485 BEGIN
2486 l_error_rec.api_name := l_api_name;
2487 l_error_rec.api_package := G_PKG_NAME;
2488 l_error_rec.idx := i;
2489 update_row (
2490 p_api_version => p_api_version,
2491 p_init_msg_list => OKC_API.G_FALSE,
2492 x_return_status => l_error_rec.error_type,
2493 x_msg_count => l_error_rec.msg_count,
2494 x_msg_data => l_error_rec.msg_data,
2495 p_codv_rec => p_codv_tbl(i),
2496 x_codv_rec => x_codv_tbl(i));
2497 IF (l_error_rec.error_type <> OKC_API.G_RET_STS_SUCCESS) THEN
2498 l_error_rec.sqlcode := SQLCODE;
2499 load_error_tbl(l_error_rec, px_error_tbl);
2500 ELSE
2501 x_msg_count := l_error_rec.msg_count;
2502 x_msg_data := l_error_rec.msg_data;
2503 END IF;
2504 EXCEPTION
2505 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2506 l_error_rec.error_type := OKC_API.G_RET_STS_ERROR;
2507 l_error_rec.sqlcode := SQLCODE;
2508 load_error_tbl(l_error_rec, px_error_tbl);
2509 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2510 l_error_rec.error_type := OKC_API.G_RET_STS_UNEXP_ERROR;
2511 l_error_rec.sqlcode := SQLCODE;
2512 load_error_tbl(l_error_rec, px_error_tbl);
2513 WHEN OTHERS THEN
2514 l_error_rec.error_type := 'OTHERS';
2515 l_error_rec.sqlcode := SQLCODE;
2516 load_error_tbl(l_error_rec, px_error_tbl);
2517 END;
2518 EXIT WHEN (i = p_codv_tbl.LAST);
2519 i := p_codv_tbl.NEXT(i);
2520 END LOOP;
2521 END IF;
2522 -- Loop through the error_tbl to find the error with the highest severity
2523 -- and return it.
2524 x_return_status := find_highest_exception(px_error_tbl);
2525 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2526 EXCEPTION
2527 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2528 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2529 (
2530 l_api_name,
2531 G_PKG_NAME,
2532 'OKC_API.G_RET_STS_ERROR',
2533 x_msg_count,
2534 x_msg_data,
2535 '_PVT'
2536 );
2537 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2538 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2539 (
2540 l_api_name,
2541 G_PKG_NAME,
2542 'OKC_API.G_RET_STS_UNEXP_ERROR',
2543 x_msg_count,
2544 x_msg_data,
2545 '_PVT'
2546 );
2547 WHEN OTHERS THEN
2548 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2549 (
2550 l_api_name,
2551 G_PKG_NAME,
2552 'OTHERS',
2553 x_msg_count,
2554 x_msg_data,
2555 '_PVT'
2556 );
2557 END update_row;
2558
2559 ----------------------------------------
2560 -- PL/SQL TBL update_row for:CODV_TBL --
2561 ----------------------------------------
2562 PROCEDURE update_row(
2563 p_api_version IN NUMBER,
2564 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
2565 x_return_status OUT NOCOPY VARCHAR2,
2566 x_msg_count OUT NOCOPY NUMBER,
2567 x_msg_data OUT NOCOPY VARCHAR2,
2568 p_codv_tbl IN codv_tbl_type,
2569 x_codv_tbl OUT NOCOPY codv_tbl_type) IS
2570
2571 l_api_version CONSTANT NUMBER := 1;
2572 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_update_row';
2573 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2574 l_error_tbl OKC_API.ERROR_TBL_TYPE;
2575 BEGIN
2576 OKC_API.init_msg_list(p_init_msg_list);
2577 -- Make sure PL/SQL table has records in it before passing
2578 IF (p_codv_tbl.COUNT > 0) THEN
2579 update_row (
2580 p_api_version => p_api_version,
2581 p_init_msg_list => OKC_API.G_FALSE,
2582 x_return_status => x_return_status,
2583 x_msg_count => x_msg_count,
2584 x_msg_data => x_msg_data,
2585 p_codv_tbl => p_codv_tbl,
2586 x_codv_tbl => x_codv_tbl,
2587 px_error_tbl => l_error_tbl);
2588 END IF;
2589 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2590 EXCEPTION
2591 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2592 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2593 (
2594 l_api_name,
2595 G_PKG_NAME,
2596 'OKC_API.G_RET_STS_ERROR',
2597 x_msg_count,
2598 x_msg_data,
2599 '_PVT'
2600 );
2601 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2602 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2603 (
2604 l_api_name,
2605 G_PKG_NAME,
2606 'OKC_API.G_RET_STS_UNEXP_ERROR',
2607 x_msg_count,
2608 x_msg_data,
2609 '_PVT'
2610 );
2611 WHEN OTHERS THEN
2612 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2613 (
2614 l_api_name,
2615 G_PKG_NAME,
2616 'OTHERS',
2617 x_msg_count,
2618 x_msg_data,
2619 '_PVT'
2620 );
2621 END update_row;
2622
2623 ---------------------------------------------------------------------------
2624 -- PROCEDURE delete_row
2625 ---------------------------------------------------------------------------
2626 ----------------------------------------
2627 -- delete_row for:OKS_K_ORDER_DETAILS --
2628 ----------------------------------------
2629 PROCEDURE delete_row(
2630 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
2631 x_return_status OUT NOCOPY VARCHAR2,
2632 x_msg_count OUT NOCOPY NUMBER,
2633 x_msg_data OUT NOCOPY VARCHAR2,
2634 p_cod_rec IN cod_rec_type) IS
2635
2636 l_api_version CONSTANT NUMBER := 1;
2637 l_api_name CONSTANT VARCHAR2(30) := 'B_delete_row';
2638 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2639 l_cod_rec cod_rec_type := p_cod_rec;
2640 l_row_notfound BOOLEAN := TRUE;
2641 BEGIN
2642 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
2643 p_init_msg_list,
2644 '_PVT',
2645 x_return_status);
2646 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2647 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2648 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2649 RAISE OKC_API.G_EXCEPTION_ERROR;
2650 END IF;
2651
2652 DELETE FROM OKS_K_ORDER_DETAILS
2653 WHERE ID = p_cod_rec.id;
2654
2655 x_return_status := l_return_status;
2656 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2657 EXCEPTION
2658 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2659 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2660 (
2661 l_api_name,
2662 G_PKG_NAME,
2663 'OKC_API.G_RET_STS_ERROR',
2664 x_msg_count,
2665 x_msg_data,
2666 '_PVT'
2667 );
2668 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2669 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2670 (
2671 l_api_name,
2672 G_PKG_NAME,
2673 'OKC_API.G_RET_STS_UNEXP_ERROR',
2674 x_msg_count,
2675 x_msg_data,
2676 '_PVT'
2677 );
2678 WHEN OTHERS THEN
2679 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2680 (
2681 l_api_name,
2682 G_PKG_NAME,
2683 'OTHERS',
2684 x_msg_count,
2685 x_msg_data,
2686 '_PVT'
2687 );
2688 END delete_row;
2689 ------------------------------------------
2690 -- delete_row for:OKS_K_ORDER_DETAILS_V --
2691 ------------------------------------------
2692 PROCEDURE delete_row(
2693 p_api_version IN NUMBER,
2694 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
2695 x_return_status OUT NOCOPY VARCHAR2,
2696 x_msg_count OUT NOCOPY NUMBER,
2697 x_msg_data OUT NOCOPY VARCHAR2,
2698 p_codv_rec IN codv_rec_type) IS
2699
2700 l_api_version CONSTANT NUMBER := 1;
2701 l_api_name CONSTANT VARCHAR2(30) := 'V_delete_row';
2702 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2703 l_codv_rec codv_rec_type := p_codv_rec;
2704 l_cod_rec cod_rec_type;
2705 BEGIN
2706 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
2707 G_PKG_NAME,
2708 p_init_msg_list,
2709 l_api_version,
2710 p_api_version,
2711 '_PVT',
2712 x_return_status);
2713 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2714 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2715 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2716 RAISE OKC_API.G_EXCEPTION_ERROR;
2717 END IF;
2718 -----------------------------------------
2719 -- Move VIEW record to "Child" records --
2720 -----------------------------------------
2721 migrate(l_codv_rec, l_cod_rec);
2722 -----------------------------------------------
2723 -- Call the DELETE_ROW for each child record --
2724 -----------------------------------------------
2725 delete_row(
2726 p_init_msg_list,
2727 l_return_status,
2728 x_msg_count,
2729 x_msg_data,
2730 l_cod_rec
2731 );
2732 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2733 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2734 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2735 RAISE OKC_API.G_EXCEPTION_ERROR;
2736 END IF;
2737 x_return_status := l_return_status;
2738 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2739 EXCEPTION
2740 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2741 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2742 (
2743 l_api_name,
2744 G_PKG_NAME,
2745 'OKC_API.G_RET_STS_ERROR',
2746 x_msg_count,
2747 x_msg_data,
2748 '_PVT'
2749 );
2750 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2751 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2752 (
2753 l_api_name,
2754 G_PKG_NAME,
2755 'OKC_API.G_RET_STS_UNEXP_ERROR',
2756 x_msg_count,
2757 x_msg_data,
2758 '_PVT'
2759 );
2760 WHEN OTHERS THEN
2761 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2762 (
2763 l_api_name,
2764 G_PKG_NAME,
2765 'OTHERS',
2766 x_msg_count,
2767 x_msg_data,
2768 '_PVT'
2769 );
2770 END delete_row;
2771 -----------------------------------------------------
2772 -- PL/SQL TBL delete_row for:OKS_K_ORDER_DETAILS_V --
2773 -----------------------------------------------------
2774 PROCEDURE delete_row(
2775 p_api_version IN NUMBER,
2776 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
2777 x_return_status OUT NOCOPY VARCHAR2,
2778 x_msg_count OUT NOCOPY NUMBER,
2779 x_msg_data OUT NOCOPY VARCHAR2,
2780 p_codv_tbl IN codv_tbl_type,
2781 px_error_tbl IN OUT NOCOPY OKC_API.ERROR_TBL_TYPE) IS
2782
2783 l_api_version CONSTANT NUMBER := 1;
2784 l_api_name CONSTANT VARCHAR2(30) := 'V_error_tbl_delete_row';
2785 i NUMBER := 0;
2786 BEGIN
2787 OKC_API.init_msg_list(p_init_msg_list);
2788 -- Make sure PL/SQL table has records in it before passing
2789 IF (p_codv_tbl.COUNT > 0) THEN
2790 i := p_codv_tbl.FIRST;
2791 LOOP
2792 DECLARE
2793 l_error_rec OKC_API.ERROR_REC_TYPE;
2794 BEGIN
2795 l_error_rec.api_name := l_api_name;
2796 l_error_rec.api_package := G_PKG_NAME;
2797 l_error_rec.idx := i;
2798 delete_row (
2799 p_api_version => p_api_version,
2800 p_init_msg_list => OKC_API.G_FALSE,
2801 x_return_status => l_error_rec.error_type,
2802 x_msg_count => l_error_rec.msg_count,
2803 x_msg_data => l_error_rec.msg_data,
2804 p_codv_rec => p_codv_tbl(i));
2805 IF (l_error_rec.error_type <> OKC_API.G_RET_STS_SUCCESS) THEN
2806 l_error_rec.sqlcode := SQLCODE;
2807 load_error_tbl(l_error_rec, px_error_tbl);
2808 ELSE
2809 x_msg_count := l_error_rec.msg_count;
2810 x_msg_data := l_error_rec.msg_data;
2811 END IF;
2812 EXCEPTION
2813 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2814 l_error_rec.error_type := OKC_API.G_RET_STS_ERROR;
2815 l_error_rec.sqlcode := SQLCODE;
2816 load_error_tbl(l_error_rec, px_error_tbl);
2817 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2818 l_error_rec.error_type := OKC_API.G_RET_STS_UNEXP_ERROR;
2819 l_error_rec.sqlcode := SQLCODE;
2820 load_error_tbl(l_error_rec, px_error_tbl);
2821 WHEN OTHERS THEN
2822 l_error_rec.error_type := 'OTHERS';
2823 l_error_rec.sqlcode := SQLCODE;
2824 load_error_tbl(l_error_rec, px_error_tbl);
2825 END;
2826 EXIT WHEN (i = p_codv_tbl.LAST);
2827 i := p_codv_tbl.NEXT(i);
2828 END LOOP;
2829 END IF;
2830 -- Loop through the error_tbl to find the error with the highest severity
2831 -- and return it.
2832 x_return_status := find_highest_exception(px_error_tbl);
2833 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2834 EXCEPTION
2835 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2836 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2837 (
2838 l_api_name,
2839 G_PKG_NAME,
2840 'OKC_API.G_RET_STS_ERROR',
2841 x_msg_count,
2842 x_msg_data,
2843 '_PVT'
2844 );
2845 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2846 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2847 (
2848 l_api_name,
2849 G_PKG_NAME,
2850 'OKC_API.G_RET_STS_UNEXP_ERROR',
2851 x_msg_count,
2852 x_msg_data,
2853 '_PVT'
2854 );
2855 WHEN OTHERS THEN
2856 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2857 (
2858 l_api_name,
2859 G_PKG_NAME,
2860 'OTHERS',
2861 x_msg_count,
2862 x_msg_data,
2863 '_PVT'
2864 );
2865 END delete_row;
2866
2867 -----------------------------------------------------
2868 -- PL/SQL TBL delete_row for:OKS_K_ORDER_DETAILS_V --
2869 -----------------------------------------------------
2870 PROCEDURE delete_row(
2871 p_api_version IN NUMBER,
2872 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
2873 x_return_status OUT NOCOPY VARCHAR2,
2874 x_msg_count OUT NOCOPY NUMBER,
2875 x_msg_data OUT NOCOPY VARCHAR2,
2876 p_codv_tbl IN codv_tbl_type) IS
2877
2878 l_api_version CONSTANT NUMBER := 1;
2879 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_delete_row';
2880 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2881 l_error_tbl OKC_API.ERROR_TBL_TYPE;
2882 BEGIN
2883 OKC_API.init_msg_list(p_init_msg_list);
2884 -- Make sure PL/SQL table has records in it before passing
2885 IF (p_codv_tbl.COUNT > 0) THEN
2886 delete_row (
2887 p_api_version => p_api_version,
2888 p_init_msg_list => OKC_API.G_FALSE,
2889 x_return_status => x_return_status,
2890 x_msg_count => x_msg_count,
2891 x_msg_data => x_msg_data,
2892 p_codv_tbl => p_codv_tbl,
2893 px_error_tbl => l_error_tbl);
2894 END IF;
2895 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2896 EXCEPTION
2897 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2898 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2899 (
2900 l_api_name,
2901 G_PKG_NAME,
2902 'OKC_API.G_RET_STS_ERROR',
2903 x_msg_count,
2904 x_msg_data,
2905 '_PVT'
2906 );
2907 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2908 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2909 (
2910 l_api_name,
2911 G_PKG_NAME,
2912 'OKC_API.G_RET_STS_UNEXP_ERROR',
2913 x_msg_count,
2914 x_msg_data,
2915 '_PVT'
2916 );
2917 WHEN OTHERS THEN
2918 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2919 (
2920 l_api_name,
2921 G_PKG_NAME,
2922 'OTHERS',
2923 x_msg_count,
2924 x_msg_data,
2925 '_PVT'
2926 );
2927 END delete_row;
2928
2929 END OKS_COD_PVT;