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