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