[Home] [Help]
PACKAGE BODY: APPS.OKC_PAT_PVT
Source
1 PACKAGE BODY OKC_PAT_PVT AS
2 /* $Header: OKCSPATB.pls 120.1 2005/09/12 00:17:39 mchoudha 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_ADJUSTMENTS
47 ---------------------------------------------------------------------------
48 FUNCTION get_rec (
49 p_pat_rec IN pat_rec_type,
50 x_no_data_found OUT NOCOPY BOOLEAN
51 ) RETURN pat_rec_type IS
52 CURSOR pat_pk_csr (p_id IN NUMBER) IS
53 SELECT
54 ID,
55 PAT_ID,
56 CHR_ID,
57 CLE_ID,
58 BSL_ID,
59 BCL_ID,
60 CREATED_BY,
61 CREATION_DATE,
62 LAST_UPDATED_BY,
63 LAST_UPDATE_DATE,
64 MODIFIED_FROM,
65 MODIFIED_TO,
66 MODIFIER_MECHANISM_TYPE_CODE,
67 OPERAND,
68 ARITHMETIC_OPERATOR,
69 AUTOMATIC_FLAG,
70 UPDATE_ALLOWED,
71 UPDATED_FLAG,
72 APPLIED_FLAG,
73 ON_INVOICE_FLAG,
74 PRICING_PHASE_ID,
75 CONTEXT,
76 PROGRAM_APPLICATION_ID,
77 PROGRAM_ID,
78 PROGRAM_UPDATE_DATE,
79 REQUEST_ID,
80 LIST_HEADER_ID,
81 LIST_LINE_ID,
82 LIST_LINE_TYPE_CODE,
83 CHANGE_REASON_CODE,
84 CHANGE_REASON_TEXT,
85 ESTIMATED_FLAG,
86 ADJUSTED_AMOUNT,
87 CHARGE_TYPE_CODE,
88 CHARGE_SUBTYPE_CODE,
89 RANGE_BREAK_QUANTITY,
90 ACCRUAL_CONVERSION_RATE,
91 PRICING_GROUP_SEQUENCE,
92 ACCRUAL_FLAG,
93 LIST_LINE_NO,
94 SOURCE_SYSTEM_CODE,
95 BENEFIT_QTY,
96 BENEFIT_UOM_CODE,
97 EXPIRATION_DATE,
98 MODIFIER_LEVEL_CODE,
99 PRICE_BREAK_TYPE_CODE,
100 SUBSTITUTION_ATTRIBUTE,
101 PRORATION_TYPE_CODE,
102 INCLUDE_ON_RETURNS_FLAG,
103 OBJECT_VERSION_NUMBER,
104 ATTRIBUTE1,
105 ATTRIBUTE2,
106 ATTRIBUTE3,
107 ATTRIBUTE4,
108 ATTRIBUTE5,
109 ATTRIBUTE6,
110 ATTRIBUTE7,
111 ATTRIBUTE8,
112 ATTRIBUTE9,
113 ATTRIBUTE10,
114 ATTRIBUTE11,
115 ATTRIBUTE12,
116 ATTRIBUTE13,
117 ATTRIBUTE14,
118 ATTRIBUTE15,
119 LAST_UPDATE_LOGIN,
120 REBATE_TRANSACTION_TYPE_CODE
121 FROM Okc_Price_Adjustments
122 WHERE okc_price_adjustments.id = p_id;
123 l_pat_pk pat_pk_csr%ROWTYPE;
124 l_pat_rec pat_rec_type;
125 BEGIN
126
127 IF (l_debug = 'Y') THEN
128 okc_debug.Set_Indentation('OKC_PAT_PVT');
129 okc_debug.log('500: Entered get_rec', 2);
130 END IF;
131
132 x_no_data_found := TRUE;
133 -- Get current database values
134 OPEN pat_pk_csr (p_pat_rec.id);
135 FETCH pat_pk_csr INTO
136 l_pat_rec.ID,
137 l_pat_rec.PAT_ID,
138 l_pat_rec.CHR_ID,
139 l_pat_rec.CLE_ID,
140 l_pat_rec.BSL_ID,
141 l_pat_rec.BCL_ID,
142 l_pat_rec.CREATED_BY,
143 l_pat_rec.CREATION_DATE,
144 l_pat_rec.LAST_UPDATED_BY,
145 l_pat_rec.LAST_UPDATE_DATE,
146 l_pat_rec.MODIFIED_FROM,
147 l_pat_rec.MODIFIED_TO,
148 l_pat_rec.MODIFIER_MECHANISM_TYPE_CODE,
149 l_pat_rec.OPERAND,
150 l_pat_rec.ARITHMETIC_OPERATOR,
151 l_pat_rec.AUTOMATIC_FLAG,
152 l_pat_rec.UPDATE_ALLOWED,
153 l_pat_rec.UPDATED_FLAG,
154 l_pat_rec.APPLIED_FLAG,
155 l_pat_rec.ON_INVOICE_FLAG,
156 l_pat_rec.PRICING_PHASE_ID,
157 l_pat_rec.CONTEXT,
158 l_pat_rec.PROGRAM_APPLICATION_ID,
159 l_pat_rec.PROGRAM_ID,
160 l_pat_rec.PROGRAM_UPDATE_DATE,
161 l_pat_rec.REQUEST_ID,
162 l_pat_rec.LIST_HEADER_ID,
163 l_pat_rec.LIST_LINE_ID,
164 l_pat_rec.LIST_LINE_TYPE_CODE,
165 l_pat_rec.CHANGE_REASON_CODE,
166 l_pat_rec.CHANGE_REASON_TEXT,
167 l_pat_rec.ESTIMATED_FLAG,
168 l_pat_rec.ADJUSTED_AMOUNT,
169 l_pat_rec.CHARGE_TYPE_CODE,
170 l_pat_rec.CHARGE_SUBTYPE_CODE,
171 l_pat_rec.RANGE_BREAK_QUANTITY,
172 l_pat_rec.ACCRUAL_CONVERSION_RATE,
173 l_pat_rec.PRICING_GROUP_SEQUENCE,
174 l_pat_rec.ACCRUAL_FLAG,
175 l_pat_rec.LIST_LINE_NO,
176 l_pat_rec.SOURCE_SYSTEM_CODE,
177 l_pat_rec.BENEFIT_QTY,
178 l_pat_rec.BENEFIT_UOM_CODE,
179 l_pat_rec.EXPIRATION_DATE,
180 l_pat_rec.MODIFIER_LEVEL_CODE,
181 l_pat_rec.PRICE_BREAK_TYPE_CODE,
182 l_pat_rec.SUBSTITUTION_ATTRIBUTE,
183 l_pat_rec.PRORATION_TYPE_CODE,
184 l_pat_rec.INCLUDE_ON_RETURNS_FLAG,
185 l_pat_rec.OBJECT_VERSION_NUMBER,
186 l_pat_rec.ATTRIBUTE1,
187 l_pat_rec.ATTRIBUTE2,
188 l_pat_rec.ATTRIBUTE3,
189 l_pat_rec.ATTRIBUTE4,
190 l_pat_rec.ATTRIBUTE5,
191 l_pat_rec.ATTRIBUTE6,
192 l_pat_rec.ATTRIBUTE7,
193 l_pat_rec.ATTRIBUTE8,
194 l_pat_rec.ATTRIBUTE9,
195 l_pat_rec.ATTRIBUTE10,
196 l_pat_rec.ATTRIBUTE11,
197 l_pat_rec.ATTRIBUTE12,
198 l_pat_rec.ATTRIBUTE13,
199 l_pat_rec.ATTRIBUTE14,
200 l_pat_rec.ATTRIBUTE15,
201 l_pat_rec.LAST_UPDATE_LOGIN,
202 l_pat_rec.REBATE_TRANSACTION_TYPE_CODE;
203 x_no_data_found := pat_pk_csr%NOTFOUND;
204 CLOSE pat_pk_csr;
205
206 IF (l_debug = 'Y') THEN
207 okc_debug.log('550: Leaving get_rec ', 2);
208 okc_debug.Reset_Indentation;
209 END IF;
210
211 RETURN(l_pat_rec);
212
213 END get_rec;
214
215 FUNCTION get_rec (
216 p_pat_rec IN pat_rec_type
217 ) RETURN pat_rec_type IS
218 l_row_notfound BOOLEAN := TRUE;
219 BEGIN
220
221 RETURN(get_rec(p_pat_rec, l_row_notfound));
222
223 END get_rec;
224 ---------------------------------------------------------------------------
225 -- FUNCTION get_rec for: OKC_PRICE_ADJUSTMENTS_V
226 ---------------------------------------------------------------------------
227 FUNCTION get_rec (
228 p_patv_rec IN patv_rec_type,
229 x_no_data_found OUT NOCOPY BOOLEAN
230 ) RETURN patv_rec_type IS
231 CURSOR okc_patv_pk_csr (p_id IN NUMBER) IS
232 SELECT
233 ID,
234 PAT_ID,
235 CHR_ID,
236 CLE_ID,
237 BSL_ID,
238 BCL_ID,
239 MODIFIED_FROM,
240 MODIFIED_TO,
241 MODIFIER_MECHANISM_TYPE_CODE,
242 OPERAND,
243 ARITHMETIC_OPERATOR,
244 AUTOMATIC_FLAG,
245 UPDATE_ALLOWED,
246 UPDATED_FLAG,
247 APPLIED_FLAG,
248 ON_INVOICE_FLAG,
249 PRICING_PHASE_ID,
250 CONTEXT,
251 PROGRAM_APPLICATION_ID,
252 PROGRAM_ID,
253 PROGRAM_UPDATE_DATE,
254 REQUEST_ID,
255 LIST_HEADER_ID,
256 LIST_LINE_ID,
257 LIST_LINE_TYPE_CODE,
258 CHANGE_REASON_CODE,
259 CHANGE_REASON_TEXT,
260 ESTIMATED_FLAG,
261 ADJUSTED_AMOUNT,
262 CHARGE_TYPE_CODE,
263 CHARGE_SUBTYPE_CODE,
264 RANGE_BREAK_QUANTITY,
265 ACCRUAL_CONVERSION_RATE,
266 PRICING_GROUP_SEQUENCE,
267 ACCRUAL_FLAG,
268 LIST_LINE_NO,
269 SOURCE_SYSTEM_CODE,
270 BENEFIT_QTY,
271 BENEFIT_UOM_CODE,
272 EXPIRATION_DATE,
273 MODIFIER_LEVEL_CODE,
274 PRICE_BREAK_TYPE_CODE,
275 SUBSTITUTION_ATTRIBUTE,
276 PRORATION_TYPE_CODE,
277 INCLUDE_ON_RETURNS_FLAG,
278 OBJECT_VERSION_NUMBER,
279 ATTRIBUTE1,
280 ATTRIBUTE2,
281 ATTRIBUTE3,
282 ATTRIBUTE4,
283 ATTRIBUTE5,
284 ATTRIBUTE6,
285 ATTRIBUTE7,
286 ATTRIBUTE8,
287 ATTRIBUTE9,
288 ATTRIBUTE10,
289 ATTRIBUTE11,
290 ATTRIBUTE12,
291 ATTRIBUTE13,
292 ATTRIBUTE14,
293 ATTRIBUTE15,
294 CREATED_BY,
295 CREATION_DATE,
296 LAST_UPDATED_BY,
297 LAST_UPDATE_DATE,
298 LAST_UPDATE_LOGIN,
299 REBATE_TRANSACTION_TYPE_CODE
300 FROM Okc_Price_Adjustments_V
301 WHERE okc_price_adjustments_v.id = p_id;
302 l_okc_patv_pk okc_patv_pk_csr%ROWTYPE;
303 l_patv_rec patv_rec_type;
304 BEGIN
305
306 IF (l_debug = 'Y') THEN
307 okc_debug.Set_Indentation('OKC_PAT_PVT');
308 okc_debug.log('700: Entered get_rec', 2);
309 END IF;
310
311 x_no_data_found := TRUE;
312 -- Get current database values
313 OPEN okc_patv_pk_csr (p_patv_rec.id);
314 FETCH okc_patv_pk_csr INTO
315 l_patv_rec.ID,
316 l_patv_rec.PAT_ID,
317 l_patv_rec.CHR_ID,
318 l_patv_rec.CLE_ID,
319 l_patv_rec.BSL_ID,
320 l_patv_rec.BCL_ID,
321 l_patv_rec.MODIFIED_FROM,
322 l_patv_rec.MODIFIED_TO,
323 l_patv_rec.MODIFIER_MECHANISM_TYPE_CODE,
324 l_patv_rec.OPERAND,
325 l_patv_rec.ARITHMETIC_OPERATOR,
326 l_patv_rec.AUTOMATIC_FLAG,
327 l_patv_rec.UPDATE_ALLOWED,
328 l_patv_rec.UPDATED_FLAG,
329 l_patv_rec.APPLIED_FLAG,
330 l_patv_rec.ON_INVOICE_FLAG,
331 l_patv_rec.PRICING_PHASE_ID,
332 l_patv_rec.CONTEXT,
333 l_patv_rec.PROGRAM_APPLICATION_ID,
334 l_patv_rec.PROGRAM_ID,
335 l_patv_rec.PROGRAM_UPDATE_DATE,
336 l_patv_rec.REQUEST_ID,
337 l_patv_rec.LIST_HEADER_ID,
338 l_patv_rec.LIST_LINE_ID,
339 l_patv_rec.LIST_LINE_TYPE_CODE,
340 l_patv_rec.CHANGE_REASON_CODE,
341 l_patv_rec.CHANGE_REASON_TEXT,
342 l_patv_rec.ESTIMATED_FLAG,
343 l_patv_rec.ADJUSTED_AMOUNT,
344 l_patv_rec.CHARGE_TYPE_CODE,
345 l_patv_rec.CHARGE_SUBTYPE_CODE,
346 l_patv_rec.RANGE_BREAK_QUANTITY,
347 l_patv_rec.ACCRUAL_CONVERSION_RATE,
348 l_patv_rec.PRICING_GROUP_SEQUENCE,
349 l_patv_rec.ACCRUAL_FLAG,
350 l_patv_rec.LIST_LINE_NO,
351 l_patv_rec.SOURCE_SYSTEM_CODE,
352 l_patv_rec.BENEFIT_QTY,
353 l_patv_rec.BENEFIT_UOM_CODE,
354 l_patv_rec.EXPIRATION_DATE,
355 l_patv_rec.MODIFIER_LEVEL_CODE,
356 l_patv_rec.PRICE_BREAK_TYPE_CODE,
357 l_patv_rec.SUBSTITUTION_ATTRIBUTE,
358 l_patv_rec.PRORATION_TYPE_CODE,
359 l_patv_rec.INCLUDE_ON_RETURNS_FLAG,
360 l_patv_rec.OBJECT_VERSION_NUMBER,
361 l_patv_rec.ATTRIBUTE1,
362 l_patv_rec.ATTRIBUTE2,
363 l_patv_rec.ATTRIBUTE3,
364 l_patv_rec.ATTRIBUTE4,
365 l_patv_rec.ATTRIBUTE5,
366 l_patv_rec.ATTRIBUTE6,
367 l_patv_rec.ATTRIBUTE7,
368 l_patv_rec.ATTRIBUTE8,
369 l_patv_rec.ATTRIBUTE9,
370 l_patv_rec.ATTRIBUTE10,
371 l_patv_rec.ATTRIBUTE11,
372 l_patv_rec.ATTRIBUTE12,
373 l_patv_rec.ATTRIBUTE13,
374 l_patv_rec.ATTRIBUTE14,
375 l_patv_rec.ATTRIBUTE15,
376 l_patv_rec.CREATED_BY,
377 l_patv_rec.CREATION_DATE,
378 l_patv_rec.LAST_UPDATED_BY,
379 l_patv_rec.LAST_UPDATE_DATE,
380 l_patv_rec.LAST_UPDATE_LOGIN,
381 l_patv_rec.REBATE_TRANSACTION_TYPE_CODE;
382 x_no_data_found := okc_patv_pk_csr%NOTFOUND;
383 CLOSE okc_patv_pk_csr;
384
385 IF (l_debug = 'Y') THEN
386 okc_debug.log('750: Leaving get_rec ', 2);
387 okc_debug.Reset_Indentation;
388 END IF;
389
390 RETURN(l_patv_rec);
391
392 END get_rec;
393
394 FUNCTION get_rec (
395 p_patv_rec IN patv_rec_type
396 ) RETURN patv_rec_type IS
397 l_row_notfound BOOLEAN := TRUE;
398 BEGIN
399
400 RETURN(get_rec(p_patv_rec, l_row_notfound));
401
402 END get_rec;
403
404 -------------------------------------------------------------
405 -- FUNCTION null_out_defaults for: OKC_PRICE_ADJUSTMENTS_V --
406 -------------------------------------------------------------
407 FUNCTION null_out_defaults (
408 p_patv_rec IN patv_rec_type
409 ) RETURN patv_rec_type IS
410 l_patv_rec patv_rec_type := p_patv_rec;
411 BEGIN
412
413 IF (l_debug = 'Y') THEN
414 okc_debug.Set_Indentation('OKC_PAT_PVT');
415 okc_debug.log('900: Entered null_out_defaults', 2);
416 END IF;
417
418 IF (l_patv_rec.pat_id = OKC_API.G_MISS_NUM) THEN
419 l_patv_rec.pat_id := NULL;
420 END IF;
421 IF (l_patv_rec.chr_id = OKC_API.G_MISS_NUM) THEN
422 l_patv_rec.chr_id := NULL;
423 END IF;
424 IF (l_patv_rec.cle_id = OKC_API.G_MISS_NUM) THEN
425 l_patv_rec.cle_id := NULL;
426 END IF;
427 IF (l_patv_rec.bsl_id = OKC_API.G_MISS_NUM) THEN
428 l_patv_rec.bsl_id := NULL;
429 END IF;
430 IF (l_patv_rec.bcl_id = OKC_API.G_MISS_NUM) THEN
431 l_patv_rec.bcl_id := NULL;
432 END IF;
433 IF (l_patv_rec.modified_from = OKC_API.G_MISS_NUM) THEN
434 l_patv_rec.modified_from := NULL;
435 END IF;
436 IF (l_patv_rec.modified_to = OKC_API.G_MISS_NUM) THEN
437 l_patv_rec.modified_to := NULL;
438 END IF;
439 IF (l_patv_rec.modifier_mechanism_type_code = OKC_API.G_MISS_CHAR) THEN
440 l_patv_rec.modifier_mechanism_type_code := NULL;
441 END IF;
442 IF (l_patv_rec.operand = OKC_API.G_MISS_NUM) THEN
443 l_patv_rec.operand := NULL;
444 END IF;
445 IF (l_patv_rec.arithmetic_operator = OKC_API.G_MISS_CHAR) THEN
446 l_patv_rec.arithmetic_operator := NULL;
447 END IF;
448 IF (l_patv_rec.automatic_flag = OKC_API.G_MISS_CHAR) THEN
449 l_patv_rec.automatic_flag := NULL;
450 END IF;
451 IF (l_patv_rec.update_allowed = OKC_API.G_MISS_CHAR) THEN
452 l_patv_rec.update_allowed := NULL;
453 END IF;
454 IF (l_patv_rec.updated_flag = OKC_API.G_MISS_CHAR) THEN
455 l_patv_rec.updated_flag := NULL;
456 END IF;
457 IF (l_patv_rec.applied_flag = OKC_API.G_MISS_CHAR) THEN
458 l_patv_rec.applied_flag := NULL;
459 END IF;
460 IF (l_patv_rec.on_invoice_flag = OKC_API.G_MISS_CHAR) THEN
461 l_patv_rec.on_invoice_flag := NULL;
462 END IF;
463 IF (l_patv_rec.pricing_phase_id = OKC_API.G_MISS_NUM) THEN
464 l_patv_rec.pricing_phase_id := NULL;
465 END IF;
466 IF (l_patv_rec.context = OKC_API.G_MISS_CHAR) THEN
467 l_patv_rec.context := NULL;
468 END IF;
469
470 IF (l_patv_rec.program_application_id = OKC_API.G_MISS_NUM) THEN
471 l_patv_rec.program_application_id := NULL;
472 END IF;
473 IF (l_patv_rec.program_id = OKC_API.G_MISS_NUM) THEN
474 l_patv_rec.program_id := NULL;
475 END IF;
476 IF(l_patv_rec.program_update_date = OKC_API.G_MISS_DATE) THEN
477 l_patv_rec.program_update_date := NULL;
478 END IF;
479 IF (l_patv_rec.request_id= OKC_API.G_MISS_NUM) THEN
480 l_patv_rec.request_id := NULL;
481 END IF;
482 IF (l_patv_rec.list_header_id = OKC_API.G_MISS_NUM) THEN
483 l_patv_rec.list_header_id := NULL;
484 END IF;
485
486 IF (l_patv_rec.list_line_id = OKC_API.G_MISS_NUM) THEN
487 l_patv_rec.list_line_id := NULL;
488 END IF;
489 IF (l_patv_rec.list_line_type_code = OKC_API.G_MISS_CHAR) THEN
490 l_patv_rec.list_line_type_code := NULL;
491 END IF;
492 IF (l_patv_rec.change_reason_code = OKC_API.G_MISS_CHAR) THEN
493 l_patv_rec.change_reason_code := NULL;
494 END IF;
495 IF (l_patv_rec.change_reason_text = OKC_API.G_MISS_CHAR) THEN
496 l_patv_rec.change_reason_text := NULL;
497 END IF;
498 IF (l_patv_rec.estimated_flag = OKC_API.G_MISS_CHAR) THEN
499 l_patv_rec.estimated_flag := NULL;
500 END IF;
501 IF(l_patv_rec.adjusted_amount = OKC_API.G_MISS_NUM) THEN
502 l_patv_rec.adjusted_amount := NULL;
503 END IF;
504 IF (l_patv_rec.charge_type_code = OKC_API.G_MISS_CHAR) THEN
505 l_patv_rec.charge_type_code := NULL;
506 END IF;
507 IF(l_patv_rec.charge_subtype_code = OKC_API.G_MISS_CHAR) THEN
508 l_patv_rec.charge_subtype_code := NULL;
509 END IF;
510 IF (l_patv_rec.range_break_quantity = OKC_API.G_MISS_NUM) THEN
511 l_patv_rec.range_break_quantity := NULL;
512 END IF;
513 IF (l_patv_rec.accrual_conversion_rate = OKC_API.G_MISS_NUM) THEN
514 l_patv_rec.accrual_conversion_rate := NULL;
515 END IF;
516 IF (l_patv_rec.pricing_group_sequence = OKC_API.G_MISS_NUM) THEN
517 l_patv_rec.pricing_group_sequence := NULL;
518 END IF;
519 IF(l_patv_rec.accrual_flag = OKC_API.G_MISS_CHAR) THEN
520 l_patv_rec.accrual_flag := NULL;
521 END IF;
522 IF(l_patv_rec.list_line_no = OKC_API.G_MISS_CHAR) THEN
523 l_patv_rec.list_line_no := NULL;
524 END IF;
525 IF (l_patv_rec.source_system_code = OKC_API.G_MISS_CHAR) THEN
526 l_patv_rec.source_system_code := NULL;
527 END IF;
528 IF (l_patv_rec.benefit_qty = OKC_API.G_MISS_NUM) THEN
529 l_patv_rec.benefit_qty := NULL;
530 END IF;
531 IF (l_patv_rec.benefit_uom_code = OKC_API.G_MISS_CHAR) THEN
532 l_patv_rec.benefit_uom_code := NULL;
533 END IF;
534 IF (l_patv_rec.expiration_date = OKC_API.G_MISS_DATE) THEN
535 l_patv_rec.expiration_date := NULL;
536 END IF;
537 IF (l_patv_rec.modifier_level_code = OKC_API.G_MISS_CHAR) THEN
538 l_patv_rec.modifier_level_code := NULL;
539 END IF;
540 IF (l_patv_rec.price_break_type_code = OKC_API.G_MISS_CHAR) THEN
541 l_patv_rec.price_break_type_code := NULL;
542 END IF;
543 IF (l_patv_rec.substitution_attribute = OKC_API.G_MISS_CHAR) THEN
544 l_patv_rec.substitution_attribute := NULL;
545 END IF;
546 IF (l_patv_rec.proration_type_code = OKC_API.G_MISS_CHAR) THEN
547 l_patv_rec.proration_type_code := NULL;
548 END IF;
549 IF (l_patv_rec.include_on_returns_flag = OKC_API.G_MISS_CHAR) THEN
550 l_patv_rec.include_on_returns_flag := NULL;
551 END IF;
552 IF (l_patv_rec.object_version_number = OKC_API.G_MISS_NUM) THEN
553 l_patv_rec.object_version_number := NULL;
554 END IF;
555
556 IF (l_patv_rec.attribute1 = OKC_API.G_MISS_CHAR) THEN
557 l_patv_rec.attribute1 := NULL;
558 END IF;
559 IF (l_patv_rec.attribute2 = OKC_API.G_MISS_CHAR) THEN
560 l_patv_rec.attribute2 := NULL;
561 END IF;
562 IF (l_patv_rec.attribute3 = OKC_API.G_MISS_CHAR) THEN
563 l_patv_rec.attribute3 := NULL;
564 END IF;
565 IF (l_patv_rec.attribute4 = OKC_API.G_MISS_CHAR) THEN
566 l_patv_rec.attribute4 := NULL;
567 END IF;
568 IF (l_patv_rec.attribute5 = OKC_API.G_MISS_CHAR) THEN
569 l_patv_rec.attribute5 := NULL;
570 END IF;
571 IF (l_patv_rec.attribute6 = OKC_API.G_MISS_CHAR) THEN
572 l_patv_rec.attribute6 := NULL;
573 END IF;
574 IF (l_patv_rec.attribute7 = OKC_API.G_MISS_CHAR) THEN
575 l_patv_rec.attribute7 := NULL;
576 END IF;
577 IF (l_patv_rec.attribute8 = OKC_API.G_MISS_CHAR) THEN
578 l_patv_rec.attribute8 := NULL;
579 END IF;
580 IF (l_patv_rec.attribute9 = OKC_API.G_MISS_CHAR) THEN
581 l_patv_rec.attribute9 := NULL;
582 END IF;
583 IF (l_patv_rec.attribute10 = OKC_API.G_MISS_CHAR) THEN
584 l_patv_rec.attribute10 := NULL;
585 END IF;
586 IF (l_patv_rec.attribute11 = OKC_API.G_MISS_CHAR) THEN
587 l_patv_rec.attribute11 := NULL;
588 END IF;
589 IF (l_patv_rec.attribute12 = OKC_API.G_MISS_CHAR) THEN
590 l_patv_rec.attribute12 := NULL;
591 END IF;
592 IF (l_patv_rec.attribute13 = OKC_API.G_MISS_CHAR) THEN
593 l_patv_rec.attribute13 := NULL;
594 END IF;
595 IF (l_patv_rec.attribute14 = OKC_API.G_MISS_CHAR) THEN
596 l_patv_rec.attribute14 := NULL;
597 END IF;
598 IF (l_patv_rec.attribute15 = OKC_API.G_MISS_CHAR) THEN
599 l_patv_rec.attribute15 := NULL;
600 END IF;
601 IF (l_patv_rec.created_by = OKC_API.G_MISS_NUM) THEN
602 l_patv_rec.created_by := NULL;
603 END IF;
604 IF (l_patv_rec.creation_date = OKC_API.G_MISS_DATE) THEN
605 l_patv_rec.creation_date := NULL;
606 END IF;
607 IF (l_patv_rec.last_updated_by = OKC_API.G_MISS_NUM) THEN
608 l_patv_rec.last_updated_by := NULL;
609 END IF;
610 IF (l_patv_rec.last_update_date = OKC_API.G_MISS_DATE) THEN
611 l_patv_rec.last_update_date := NULL;
612 END IF;
613 IF (l_patv_rec.last_update_login = OKC_API.G_MISS_NUM) THEN
614 l_patv_rec.last_update_login := NULL;
615 END IF;
616 IF (l_patv_rec.rebate_transaction_type_code = OKC_API.G_MISS_CHAR) THEN
617 l_patv_rec.rebate_transaction_type_code := NULL;
618 END IF;
619
620 IF (l_debug = 'Y') THEN
621 okc_debug.log('950: Leaving null_out_defaults ', 2);
622 okc_debug.Reset_Indentation;
623 END IF;
624
625 RETURN(l_patv_rec);
626
627 END null_out_defaults;
628
629 -------------------------------------------------------------------------------------
630 --Attribute Level Validation Procedures Starts(Modification on TAPI generated Code.)--
631 ---------------------------------------------------------------------------------------\\\\\\
632
633 ---------------------------------------------------------------------------------
634 -----Foreign key validations
635 ---------------------------------------------------------------------------------
636 PROCEDURE validate_list_header_id(x_return_status OUT NOCOPY VARCHAR2,
637 p_patv_rec IN patv_rec_type) is
638
639 G_NO_PARENT_RECORD CONSTANT VARCHAR2(200) := 'OKC_NO_PARENT_RECORD';
640 G_UNEXPECTED_ERROR CONSTANT VARCHAR2(200) := 'OKC_CONTRACTS_UNEXPECTED_ERROR';
641 G_SQLERRM_TOKEN CONSTANT VARCHAR2(200) := 'SQLerrm';
642 G_SQLCODE_TOKEN CONSTANT VARCHAR2(200) := 'SQLcode';
643 G_VIEW CONSTANT VARCHAR2(200) := 'QP_LIST_HEADERS_V';
644 G_EXCEPTION_HALT_VALIDATION exception;
645 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
646
647 l_dummy_var VARCHAR2(1) := '?';
648 Cursor l_patv_csr Is
649 select 'x'
650 from QP_LIST_HEADERS_B
651 where LIST_HEADER_ID = p_patv_rec.list_header_id;
652 Begin
653
654 IF (l_debug = 'Y') THEN
655 okc_debug.Set_Indentation('OKC_PAT_PVT');
656 okc_debug.log('1000: Entered validate_list_header_id', 2);
657 END IF;
658
659 -- initialize return status
660 x_return_status := OKC_API.G_RET_STS_SUCCESS;
661
662 -- enforce foreign key, if data exists
663 If (p_patv_rec.list_header_id <> OKC_API.G_MISS_NUM and
664 p_patv_rec.chr_id IS NOT NULL)
665 Then
666 Open l_patv_csr;
667 Fetch l_patv_csr Into l_dummy_var;
668 Close l_patv_csr;
669
670 -- if l_dummy_var still set to default, data was not found
671 If (l_dummy_var = '?') Then
672 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
673 p_msg_name => g_no_parent_record,
674 p_token1 => g_col_name_token,
675 p_token1_value=> 'chr_id',
676 p_token2 => g_child_table_token,
677 p_token2_value=> G_VIEW,
678 p_token3 => g_parent_table_token,
679 p_token3_value=> 'QP_LIST_HEADERS_V');
680 -- notify caller of an error
681 x_return_status := OKC_API.G_RET_STS_ERROR;
682 End If;
683 End If;
684
685 IF (l_debug = 'Y') THEN
686 okc_debug.log('1100: Leaving validate_list_header_id', 2);
687 okc_debug.Reset_Indentation;
688 END IF;
689
690 exception
691 when OTHERS then
692
693 IF (l_debug = 'Y') THEN
694 okc_debug.log('1200: Exiting validate_list_header_id:OTHERS Exception', 2);
695 okc_debug.Reset_Indentation;
696 END IF;
697
698 -- store SQL error message on message stack
699 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
700 p_msg_name => g_unexpected_error,
701 p_token1 => g_sqlcode_token,
702 p_token1_value => sqlcode,
703 p_token2 => g_sqlerrm_token,
704 p_token2_value => sqlerrm);
705 -- notify caller of an error as UNEXPETED error
706 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
707
708 -- verify that cursor was closed
709 if l_patv_csr%ISOPEN then
710 close l_patv_csr;
711 end if;
712
713 End validate_list_header_id;
714
715
716 PROCEDURE validate_list_line_id(x_return_status OUT NOCOPY VARCHAR2,
717 p_patv_rec IN patv_rec_type) is
718
719 G_NO_PARENT_RECORD CONSTANT VARCHAR2(200) := 'OKC_NO_PARENT_RECORD';
720 G_UNEXPECTED_ERROR CONSTANT VARCHAR2(200) := 'OKC_CONTRACTS_UNEXPECTED_ERROR';
721 G_SQLERRM_TOKEN CONSTANT VARCHAR2(200) := 'SQLerrm';
722 G_SQLCODE_TOKEN CONSTANT VARCHAR2(200) := 'SQLcode';
723 G_VIEW CONSTANT VARCHAR2(200) := 'QP_LIST_HEADERS_V';
724 G_EXCEPTION_HALT_VALIDATION exception;
725 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
726
727 l_dummy_var VARCHAR2(1) := '?';
728 Cursor l_patv_csr Is
729 select 'x'
730 from QP_LIST_LINES
731 where LIST_LINE_ID = p_patv_rec.list_line_id;
732 Begin
733
734 IF (l_debug = 'Y') THEN
735 okc_debug.Set_Indentation('OKC_PAT_PVT');
736 okc_debug.log('1300: Entered validate_list_line_id', 2);
737 END IF;
738
739 -- initialize return status
740 x_return_status := OKC_API.G_RET_STS_SUCCESS;
741
742 -- enforce foreign key, if data exists
743 If (p_patv_rec.list_line_id <> OKC_API.G_MISS_NUM and
744 p_patv_rec.list_line_id IS NOT NULL)
745 Then
746 Open l_patv_csr;
747 Fetch l_patv_csr Into l_dummy_var;
748 Close l_patv_csr;
749
750 -- if l_dummy_var still set to default, data was not found
751 If (l_dummy_var = '?') Then
752 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
753 p_msg_name => g_no_parent_record,
754 p_token1 => g_col_name_token,
755 p_token1_value=> 'chr_id',
756 p_token2 => g_child_table_token,
757 p_token2_value=> G_VIEW,
758 p_token3 => g_parent_table_token,
759 p_token3_value=> 'QP_LIST_LINE_V');
760 -- notify caller of an error
761 x_return_status := OKC_API.G_RET_STS_ERROR;
762 End If;
763 End If;
764
765 IF (l_debug = 'Y') THEN
766 okc_debug.log('1400: Leaving validate_list_line_id', 2);
767 okc_debug.Reset_Indentation;
768 END IF;
769
770 exception
771 when OTHERS then
772
773 IF (l_debug = 'Y') THEN
774 okc_debug.log('1500: Exiting validate_list_line_id:OTHERS Exception', 2);
775 okc_debug.Reset_Indentation;
776 END IF;
777
778 -- store SQL error message on message stack
779 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
780 p_msg_name => g_unexpected_error,
781 p_token1 => g_sqlcode_token,
782 p_token1_value => sqlcode,
783 p_token2 => g_sqlerrm_token,
784 p_token2_value => sqlerrm);
785 -- notify caller of an error as UNEXPETED error
786 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
787
788 -- verify that cursor was closed
789 if l_patv_csr%ISOPEN then
790 close l_patv_csr;
791 end if;
792
793 End validate_list_line_id;
794
795
796 ---------------------------------------------------------------------------
797 -- PROCEDURE Validate_id
798 ---------------------------------------------------------------------------
799 PROCEDURE validate_id(
800 p_patv_rec IN patv_rec_type,
801 x_return_status OUT NOCOPY VARCHAR2) IS
802 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
803 BEGIN
804
805 IF (l_debug = 'Y') THEN
806 okc_debug.Set_Indentation('OKC_PAT_PVT');
807 okc_debug.log('1600: Entered validate_id', 2);
808 END IF;
809
810 IF p_patv_rec.id = OKC_API.G_MISS_NUM OR
811 p_patv_rec.id IS NULL
812 THEN
813 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'id');
814 l_return_status := OKC_API.G_RET_STS_ERROR;
815 END IF;
816 x_return_status := l_return_status;
817
818 IF (l_debug = 'Y') THEN
819 okc_debug.log('1700: Leaving validate_id', 2);
820 okc_debug.Reset_Indentation;
821 END IF;
822
823 EXCEPTION
824 WHEN OTHERS THEN
825
826 IF (l_debug = 'Y') THEN
827 okc_debug.log('1800: Exiting validate_id:OTHERS Exception', 2);
828 okc_debug.Reset_Indentation;
829 END IF;
830
831 -- store SQL error message on message stack for caller
832 OKC_API.set_message(G_APP_NAME, G_UNEXPECTED_ERROR,G_SQLCODE_TOKEN,SQLCODE,G_SQLERRM_TOKEN,SQLERRM);
833 -- notify caller of an UNEXPECTED error
834 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
835
836 END validate_id;
837
838 ---------------------------------------------------------------------------
839 -- PROCEDURE Validate_automatic_flag
840 ---------------------------------------------------------------------------
841 PROCEDURE validate_automatic_flag(
842 p_patv_rec IN patv_rec_type,
843 x_return_status OUT NOCOPY VARCHAR2) IS
844 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
845 BEGIN
846
847 IF (l_debug = 'Y') THEN
848 okc_debug.Set_Indentation('OKC_PAT_PVT');
849 okc_debug.log('1900: Entered validate_automatic_flag', 2);
850 END IF;
851
852 -- Check if automatic_flag is Y or N.
853
854 IF p_patv_rec.automatic_flag NOT IN ('Y', 'N')
855 THEN
856 OKC_API.set_message(G_APP_NAME, G_INVALID_VALUE,G_COL_NAME_TOKEN,'automatic_flag');
857 l_return_status := OKC_API.G_RET_STS_ERROR;
858 RAISE G_EXCEPTION_HALT_VALIDATION;
859 END IF;
860 x_return_status := l_return_status;
861
862 IF (l_debug = 'Y') THEN
863 okc_debug.log('2000: Leaving validate_automatic_flag', 2);
864 okc_debug.Reset_Indentation;
865 END IF;
866
867 EXCEPTION
868 WHEN G_EXCEPTION_HALT_VALIDATION THEN
869
870 IF (l_debug = 'Y') THEN
871 okc_debug.log('2100: Exiting validate_automatic_flag:G_EXCEPTION_HALT_VALIDATION Exception', 2);
872 okc_debug.Reset_Indentation;
873 END IF;
874
875 NULL;
876 WHEN OTHERS THEN
877
878 IF (l_debug = 'Y') THEN
879 okc_debug.log('2200: Exiting validate_automatic_flag:OTHERS Exception', 2);
880 okc_debug.Reset_Indentation;
881 END IF;
882
883 -- store SQL error message on message stack for caller
884 OKC_API.set_message(G_APP_NAME, G_UNEXPECTED_ERROR,G_SQLCODE_TOKEN,SQLCODE,G_SQLERRM_TOKEN,SQLERRM);
885 -- notify caller of an UNEXPECTED error
886 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
887
888
889 END validate_automatic_flag;
890
891 ---------------------------------------------------------------------------
892 -- PROCEDURE Validate_update_allowed
893 ---------------------------------------------------------------------------
894 PROCEDURE validate_update_allowed(
895 p_patv_rec IN patv_rec_type,
896 x_return_status OUT NOCOPY VARCHAR2) IS
897 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
898 BEGIN
899
900 IF (l_debug = 'Y') THEN
901 okc_debug.Set_Indentation('OKC_PAT_PVT');
902 okc_debug.log('2300: Entered validate_update_allowed', 2);
903 END IF;
904
905
906 IF p_patv_rec.update_allowed NOT IN ('Y', 'N')
907 THEN
908 OKC_API.set_message(G_APP_NAME, G_INVALID_VALUE,G_COL_NAME_TOKEN,'update_allowed');
909 l_return_status := OKC_API.G_RET_STS_ERROR;
910 RAISE G_EXCEPTION_HALT_VALIDATION;
911 END IF;
912 x_return_status := l_return_status;
913
914 IF (l_debug = 'Y') THEN
915 okc_debug.log('2400: Leaving validate_update_allowed', 2);
916 okc_debug.Reset_Indentation;
917 END IF;
918
919 EXCEPTION
920 WHEN G_EXCEPTION_HALT_VALIDATION THEN
921
922 IF (l_debug = 'Y') THEN
923 okc_debug.log('2500: Exiting validate_update_allowed:G_EXCEPTION_HALT_VALIDATION Exception', 2);
924 okc_debug.Reset_Indentation;
925 END IF;
926
927 NULL;
928 WHEN OTHERS THEN
929
930 IF (l_debug = 'Y') THEN
931 okc_debug.log('2600: Exiting validate_update_allowed:OTHERS Exception', 2);
932 okc_debug.Reset_Indentation;
933 END IF;
934
935 -- store SQL error message on message stack for caller
936 OKC_API.set_message(G_APP_NAME, G_UNEXPECTED_ERROR,G_SQLCODE_TOKEN,SQLCODE,G_SQLERRM_TOKEN,SQLERRM);
937 -- notify caller of an UNEXPECTED error
938 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
939
940 END validate_update_allowed;
941
942
943 ---------------------------------------------------------------------------
944 -- PROCEDURE Validate_updated_flag
945 ---------------------------------------------------------------------------
946 PROCEDURE validate_updated_flag(
947 p_patv_rec IN patv_rec_type,
948 x_return_status OUT NOCOPY VARCHAR2) IS
949 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
950 BEGIN
951
952 IF (l_debug = 'Y') THEN
953 okc_debug.Set_Indentation('OKC_PAT_PVT');
954 okc_debug.log('2700: Entered validate_updated_flag', 2);
955 END IF;
956
957
958 IF p_patv_rec.updated_flag NOT IN ('Y', 'N')
959 THEN
960 OKC_API.set_message(G_APP_NAME, G_INVALID_VALUE,G_COL_NAME_TOKEN,'updated_flag');
961 l_return_status := OKC_API.G_RET_STS_ERROR;
962 RAISE G_EXCEPTION_HALT_VALIDATION;
963 END IF;
964 x_return_status := l_return_status;
965
966 IF (l_debug = 'Y') THEN
967 okc_debug.log('2800: Leaving validate_updated_flag', 2);
968 okc_debug.Reset_Indentation;
969 END IF;
970
971 EXCEPTION
972 WHEN G_EXCEPTION_HALT_VALIDATION THEN
973
974 IF (l_debug = 'Y') THEN
975 okc_debug.log('2900: Exiting validate_updated_flag:G_EXCEPTION_HALT_VALIDATION Exception', 2);
976 okc_debug.Reset_Indentation;
977 END IF;
978
979 NULL;
980 WHEN OTHERS THEN
981
982 IF (l_debug = 'Y') THEN
983 okc_debug.log('3000: Exiting validate_updated_flag:OTHERS Exception', 2);
984 okc_debug.Reset_Indentation;
985 END IF;
986
987 -- store SQL error message on message stack for caller
988 OKC_API.set_message(G_APP_NAME, G_UNEXPECTED_ERROR,G_SQLCODE_TOKEN,SQLCODE,G_SQLERRM_TOKEN,SQLERRM);
989 -- notify caller of an UNEXPECTED error
990 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
991
992 END validate_updated_flag;
993
994
995 ---------------------------------------------------------------------------
996 -- PROCEDURE Validate_applied_flag
997 ---------------------------------------------------------------------------
998 PROCEDURE validate_applied_flag(
999 p_patv_rec IN patv_rec_type,
1000 x_return_status OUT NOCOPY VARCHAR2) IS
1001 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1002 BEGIN
1003
1004 IF (l_debug = 'Y') THEN
1005 okc_debug.Set_Indentation('OKC_PAT_PVT');
1006 okc_debug.log('3100: Entered validate_applied_flag', 2);
1007 END IF;
1008
1009
1010 IF p_patv_rec.applied_flag NOT IN ('Y', 'N')
1011 THEN
1012 OKC_API.set_message(G_APP_NAME, G_INVALID_VALUE,G_COL_NAME_TOKEN,'applied_flag');
1013 l_return_status := OKC_API.G_RET_STS_ERROR;
1014 RAISE G_EXCEPTION_HALT_VALIDATION;
1015 END IF;
1016 x_return_status := l_return_status;
1017
1018 IF (l_debug = 'Y') THEN
1019 okc_debug.log('3200: Leaving validate_applied_flag', 2);
1020 okc_debug.Reset_Indentation;
1021 END IF;
1022
1023 EXCEPTION
1024 WHEN G_EXCEPTION_HALT_VALIDATION THEN
1025
1026 IF (l_debug = 'Y') THEN
1027 okc_debug.log('3300: Exiting validate_applied_flag:G_EXCEPTION_HALT_VALIDATION Exception', 2);
1028 okc_debug.Reset_Indentation;
1029 END IF;
1030
1031 NULL;
1032 WHEN OTHERS THEN
1033
1034 IF (l_debug = 'Y') THEN
1035 okc_debug.log('3400: Exiting validate_applied_flag:OTHERS Exception', 2);
1036 okc_debug.Reset_Indentation;
1037 END IF;
1038
1039 -- store SQL error message on message stack for caller
1040 OKC_API.set_message(G_APP_NAME, G_UNEXPECTED_ERROR,G_SQLCODE_TOKEN,SQLCODE,G_SQLERRM_TOKEN,SQLERRM);
1041 -- notify caller of an UNEXPECTED error
1042 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1043
1044
1045 END validate_applied_flag;
1046
1047
1048 ---------------------------------------------------------------------------
1049 -- PROCEDURE Validate_on_invoice_flag
1050 ---------------------------------------------------------------------------
1051 PROCEDURE validate_on_invoice_flag(
1052 p_patv_rec IN patv_rec_type,
1053 x_return_status OUT NOCOPY VARCHAR2) IS
1054 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1055 BEGIN
1056
1057 IF (l_debug = 'Y') THEN
1058 okc_debug.Set_Indentation('OKC_PAT_PVT');
1059 okc_debug.log('3500: Entered validate_on_invoice_flag', 2);
1060 END IF;
1061
1062
1063 IF p_patv_rec.on_invoice_flag NOT IN ('Y', 'N')
1064 THEN
1065 OKC_API.set_message(G_APP_NAME, G_INVALID_VALUE,G_COL_NAME_TOKEN,'on_invoice_flag');
1066 l_return_status := OKC_API.G_RET_STS_ERROR;
1067 RAISE G_EXCEPTION_HALT_VALIDATION;
1068 END IF;
1069 x_return_status := l_return_status;
1070
1071 IF (l_debug = 'Y') THEN
1072 okc_debug.log('3600: Leaving validate_on_invoice_flag', 2);
1073 okc_debug.Reset_Indentation;
1074 END IF;
1075
1076 EXCEPTION
1077 WHEN G_EXCEPTION_HALT_VALIDATION THEN
1078
1079 IF (l_debug = 'Y') THEN
1080 okc_debug.log('3700: Exiting validate_on_invoice_flag:G_EXCEPTION_HALT_VALIDATION Exception', 2);
1081 okc_debug.Reset_Indentation;
1082 END IF;
1083
1084 NULL;
1085 WHEN OTHERS THEN
1086
1087 IF (l_debug = 'Y') THEN
1088 okc_debug.log('3800: Exiting validate_on_invoice_flag:OTHERS Exception', 2);
1089 okc_debug.Reset_Indentation;
1090 END IF;
1091
1092 -- store SQL error message on message stack for caller
1093 OKC_API.set_message(G_APP_NAME, G_UNEXPECTED_ERROR,G_SQLCODE_TOKEN,SQLCODE,G_SQLERRM_TOKEN,SQLERRM);
1094 -- notify caller of an UNEXPECTED error
1095 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1096
1097
1098 END validate_on_invoice_flag;
1099
1100 ---------------------------------------------------------------------
1101 ----PROCEDURE Validate_chr_id -------------
1102 --------------------------------------------------------------------
1103 procedure validate_chr_id(x_return_status OUT NOCOPY VARCHAR2,
1104 p_patv_rec IN patv_rec_type) is
1105 l_dummy_var varchar2(1) := '?';
1106 cursor l_chr_csr is
1107 select 'x'
1108 from OKC_K_HEADERS_B
1109 where id = p_patv_rec.chr_id;
1110 begin
1111
1112 IF (l_debug = 'Y') THEN
1113 okc_debug.Set_Indentation('OKC_PAT_PVT');
1114 okc_debug.log('3900: Entered validate_chr_id', 2);
1115 END IF;
1116
1117 x_return_status := OKC_API.G_RET_STS_SUCCESS;
1118 if (p_patv_rec.chr_id = OKC_API.G_MISS_NUM or p_patv_rec.chr_id is NULL) then
1119 x_return_status := OKC_API.G_RET_STS_SUCCESS;
1120 return;
1121 end if;
1122 open l_chr_csr;
1123 fetch l_chr_csr into l_dummy_var;
1124 close l_chr_csr;
1125 if (l_dummy_var = '?') then
1126 OKC_API.set_message(G_APP_NAME, G_INVALID_VALUE,G_COL_NAME_TOKEN,'CHR_ID');
1127 x_return_status := OKC_API.G_RET_STS_ERROR;
1128 end if;
1129
1130 IF (l_debug = 'Y') THEN
1131 okc_debug.log('4000: Leaving validate_chr_id', 2);
1132 okc_debug.Reset_Indentation;
1133 END IF;
1134
1135 exception
1136 when OTHERS then
1137
1138 IF (l_debug = 'Y') THEN
1139 okc_debug.log('4100: Exiting validate_chr_id:OTHERS Exception', 2);
1140 okc_debug.Reset_Indentation;
1141 END IF;
1142
1143 if l_chr_csr%ISOPEN then
1144 close l_chr_csr;
1145 end if;
1146 OKC_API.set_message(p_app_name => g_app_name,
1147 p_msg_name => g_unexpected_error,
1148 p_token1 => g_sqlcode_token,
1149 p_token1_value => sqlcode,
1150 p_token2 => g_sqlerrm_token,
1151 p_token2_value => sqlerrm);
1152 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1153
1154 end validate_chr_id;
1155 --------------------------------------------
1156 ---------------------------------------------------------------------------
1157 -- PROCEDURE Validate_Attributes
1158 ---------------------------------------------------------------------------
1159 -----------------------------------------------------
1160 -- Validate_Attributes for:OKC_PRICE_ADJUSTMENTS_V --
1161 -----------------------------------------------------
1162 FUNCTION Validate_Attributes (
1163 p_patv_rec IN patv_rec_type
1164 ) RETURN VARCHAR2 IS
1165 x_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1166 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1167
1168 BEGIN
1169
1170 IF (l_debug = 'Y') THEN
1171 okc_debug.Set_Indentation('OKC_PAT_PVT');
1172 okc_debug.log('4200: Entered Validate_Attributes', 2);
1173 END IF;
1174
1175 ---------------------------------------------------------------------------------------
1176 --Attribute Level Validation Procedure Calls Starts(Modification on TAPI generated Code.)--
1177 ---------------------------------------------------------------------------------------
1178
1179 VALIDATE_id(p_patv_rec, l_return_status);
1180 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
1181 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN -- need to leave
1182 x_return_status := l_return_status;
1183 RAISE G_EXCEPTION_HALT_VALIDATION;
1184 ELSE
1185 x_return_status := l_return_status; -- record that there was an error
1186 END IF;
1187 END IF;
1188
1189 VALIDATE_automatic_flag(p_patv_rec, l_return_status);
1190 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
1191 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN -- need to leave
1192 x_return_status := l_return_status;
1193 RAISE G_EXCEPTION_HALT_VALIDATION;
1194 ELSE
1195 x_return_status := l_return_status; -- record that there was an error
1196 END IF;
1197 END IF;
1198
1199 VALIDATE_update_allowed(p_patv_rec, l_return_status);
1200 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
1201 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN -- need to leave
1202 x_return_status := l_return_status;
1203 RAISE G_EXCEPTION_HALT_VALIDATION;
1204 ELSE
1205 x_return_status := l_return_status; -- record that there was an error
1206 END IF;
1207 END IF;
1208
1209 VALIDATE_updated_flag(p_patv_rec, l_return_status);
1210 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
1211 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN -- need to leave
1212 x_return_status := l_return_status;
1213 RAISE G_EXCEPTION_HALT_VALIDATION;
1214 ELSE
1215 x_return_status := l_return_status; -- record that there was an error
1216 END IF;
1217 END IF;
1218
1219 VALIDATE_applied_flag(p_patv_rec, l_return_status);
1220 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
1221 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN -- need to leave
1222 x_return_status := l_return_status;
1223 RAISE G_EXCEPTION_HALT_VALIDATION;
1224 ELSE
1225 x_return_status := l_return_status; -- record that there was an error
1226 END IF;
1227 END IF;
1228
1229 validate_list_header_id(l_return_status,p_patv_rec);
1230 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
1231 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN -- need to leave
1232 x_return_status := l_return_status;
1233 RAISE G_EXCEPTION_HALT_VALIDATION;
1234 ELSE
1235 x_return_status := l_return_status; -- record that there was an error
1236 END IF;
1237 END IF;
1238
1239 validate_chr_id(l_return_status,p_patv_rec);
1240 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
1241 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN -- need to leave
1242 x_return_status := l_return_status;
1243 RAISE G_EXCEPTION_HALT_VALIDATION;
1244 ELSE
1245 x_return_status := l_return_status; -- record that there was an error
1246 END IF;
1247 END IF;
1248
1249
1250 validate_list_line_id(l_return_status ,p_patv_rec);
1251 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
1252 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN -- need to leave
1253 x_return_status := l_return_status;
1254 RAISE G_EXCEPTION_HALT_VALIDATION;
1255 ELSE
1256 x_return_status := l_return_status; -- record that there was an error
1257 END IF;
1258 END IF;
1259
1260
1261 VALIDATE_on_invoice_flag(p_patv_rec, l_return_status);
1262 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
1263 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN -- need to leave
1264 x_return_status := l_return_status;
1265 RAISE G_EXCEPTION_HALT_VALIDATION;
1266 ELSE
1267 x_return_status := l_return_status; -- record that there was an error
1268 END IF;
1269 END IF;
1270
1271 IF (l_debug = 'Y') THEN
1272 okc_debug.log('4300: Leaving Validate_Attributes', 2);
1273 okc_debug.Reset_Indentation;
1274 END IF;
1275
1276 RETURN(x_return_status);
1277
1278 EXCEPTION
1279 WHEN G_EXCEPTION_HALT_VALIDATION THEN
1280
1281 IF (l_debug = 'Y') THEN
1282 okc_debug.log('4400: Exiting Validate_Attributes:G_EXCEPTION_HALT_VALIDATION Exception', 2);
1283 okc_debug.Reset_Indentation;
1284 END IF;
1285
1286 return(x_return_status);
1287 WHEN OTHERS THEN
1288
1289 IF (l_debug = 'Y') THEN
1290 okc_debug.log('4500: Exiting Validate_Attributes:OTHERS Exception', 2);
1291 okc_debug.Reset_Indentation;
1292 END IF;
1293
1294 -- store SQL error message on message stack for caller
1295 OKC_API.set_message(G_APP_NAME, G_UNEXPECTED_ERROR,G_SQLCODE_TOKEN,SQLCODE,G_SQLERRM_TOKEN,SQLERRM);
1296 -- notify caller of an UNEXPECTED error
1297 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1298 return(x_return_status);
1299
1300
1301 ---------------------------------------------------------------------------------------
1302 --Attribute Level Validation Procedure Calls Ends(Modification on TAPI generated Code.)--
1303 ---------------------------------------------------------------------------------------
1304
1305
1306 END Validate_Attributes;
1307
1308 ---------------------------------------------------------------------------
1309 -- PROCEDURE Validate_Record
1310 ---------------------------------------------------------------------------
1311 -------------------------------------------------
1312 -- Validate_Record for:OKC_PRICE_ADJUSTMENTS_V --
1313 -------------------------------------------------
1314 FUNCTION Validate_Record (
1315 p_patv_rec IN patv_rec_type
1316 ) RETURN VARCHAR2 IS
1317 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1318 ------------------------------------
1319 -- FUNCTION validate_foreign_keys --
1320 ------------------------------------
1321 FUNCTION validate_foreign_keys (
1322 p_patv_rec IN patv_rec_type
1323 ) RETURN VARCHAR2 IS
1324 item_not_found_error EXCEPTION;
1325 CURSOR okc_patv_pk_csr (p_id IN NUMBER) IS
1326 SELECT
1327 'x'
1328 FROM Okc_Price_Adjustments_V
1329 WHERE okc_price_adjustments_v.id = p_id;
1330 l_okc_patv_pk okc_patv_pk_csr%ROWTYPE;
1331 CURSOR okc_chrv_pk_csr (p_id IN NUMBER) IS
1332 SELECT
1333 'x'
1334 FROM Okc_K_Headers_B
1335 WHERE okc_k_headers_b.id = p_id;
1336 l_okc_chrv_pk okc_chrv_pk_csr%ROWTYPE;
1337 CURSOR okc_clev_pk_csr (p_id IN NUMBER) IS
1338 SELECT
1339 'x'
1340 FROM Okc_K_Lines_B
1341 WHERE okc_k_lines_b.id = p_id;
1342 l_okc_clev_pk okc_clev_pk_csr%ROWTYPE;
1343
1344 /*01-SEP-2005 -mchoudha
1345 Bug#4520703: Commenting these validations as they are
1346 not required during insertion of okc_price_adjustments.
1347 Moreover, these are already validated during initial phase
1348 of Main billing.As per discussion with Umesh.
1349
1350
1351 CURSOR oks_bslv_pk_csr (p_id IN NUMBER) IS
1352 SELECT
1353 'x'
1354 FROM Oks_Bill_Sub_Lines_V
1355 WHERE oks_bill_sub_lines_v.id = p_id;
1356 l_oks_bslv_pk oks_bslv_pk_csr%ROWTYPE;
1357
1358 CURSOR okc_bclv_pk_csr (p_id IN NUMBER) IS
1359 SELECT
1360 'x'
1361 FROM Oks_Bill_Cont_Lines_V
1362 WHERE oks_bill_cont_lines_v.id = p_id;
1363 l_okc_bclv_pk okc_bclv_pk_csr%ROWTYPE;
1364 */
1365
1366
1367 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1368 l_row_notfound BOOLEAN := TRUE;
1369 BEGIN
1370
1371 IF (l_debug = 'Y') THEN
1372 okc_debug.Set_Indentation('OKC_PAT_PVT');
1373 okc_debug.log('4600: Entered validate_foreign_keys', 2);
1374 END IF;
1375
1376 IF (p_patv_rec.PAT_ID IS NOT NULL)
1377 THEN
1378 OPEN okc_patv_pk_csr(p_patv_rec.PAT_ID);
1379 FETCH okc_patv_pk_csr INTO l_okc_patv_pk;
1380 l_row_notfound := okc_patv_pk_csr%NOTFOUND;
1381 CLOSE okc_patv_pk_csr;
1382 IF (l_row_notfound) THEN
1383 OKC_API.set_message(G_APP_NAME, G_INVALID_VALUE,G_COL_NAME_TOKEN,'PAT_ID');
1384 RAISE item_not_found_error;
1385 END IF;
1386 END IF;
1387 IF (p_patv_rec.CHR_ID IS NOT NULL)
1388 THEN
1389 OPEN okc_chrv_pk_csr(p_patv_rec.CHR_ID);
1390 FETCH okc_chrv_pk_csr INTO l_okc_chrv_pk;
1391 l_row_notfound := okc_chrv_pk_csr%NOTFOUND;
1392 CLOSE okc_chrv_pk_csr;
1393 IF (l_row_notfound) THEN
1394 OKC_API.set_message(G_APP_NAME, G_INVALID_VALUE,G_COL_NAME_TOKEN,'CHR_ID');
1395 RAISE item_not_found_error;
1396 END IF;
1397 END IF;
1398 IF (p_patv_rec.CLE_ID IS NOT NULL)
1399 THEN
1400 OPEN okc_clev_pk_csr(p_patv_rec.CLE_ID);
1401 FETCH okc_clev_pk_csr INTO l_okc_clev_pk;
1402 l_row_notfound := okc_clev_pk_csr%NOTFOUND;
1403 CLOSE okc_clev_pk_csr;
1404 IF (l_row_notfound) THEN
1405 OKC_API.set_message(G_APP_NAME, G_INVALID_VALUE,G_COL_NAME_TOKEN,'CLE_ID');
1406 RAISE item_not_found_error;
1407 END IF;
1408 END IF;
1409
1410 /*01-SEP-2005 -mchoudha
1411 Bug#4520703: Commenting these validations as they are
1412 not required during insertion of okc_price_adjustments.
1413 Moreover, these are already validated during initial phase
1414 of Main billing.As per discussion with Umesh.
1415
1416 IF (p_patv_rec.BSL_ID IS NOT NULL)
1417 THEN
1418 OPEN oks_bslv_pk_csr(p_patv_rec.BSL_ID);
1419 FETCH oks_bslv_pk_csr INTO l_oks_bslv_pk;
1420 l_row_notfound := oks_bslv_pk_csr%NOTFOUND;
1421 CLOSE oks_bslv_pk_csr;
1422 IF (l_row_notfound) THEN
1423 OKC_API.set_message(G_APP_NAME, G_INVALID_VALUE,G_COL_NAME_TOKEN,'BSL_ID');
1424 RAISE item_not_found_error;
1425 END IF;
1426 END IF;
1427 IF (p_patv_rec.BCL_ID IS NOT NULL)
1428 THEN
1429 OPEN okc_bclv_pk_csr(p_patv_rec.BCL_ID);
1430 FETCH okc_bclv_pk_csr INTO l_okc_bclv_pk;
1431 l_row_notfound := okc_bclv_pk_csr%NOTFOUND;
1432 CLOSE okc_bclv_pk_csr;
1433 IF (l_row_notfound) THEN
1434 OKC_API.set_message(G_APP_NAME, G_INVALID_VALUE,G_COL_NAME_TOKEN,'BCL_ID');
1435 RAISE item_not_found_error;
1436 END IF;
1437 END IF;
1438 */
1439 IF (l_debug = 'Y') THEN
1440 okc_debug.log('4700: Leaving validate_foreign_keys', 2);
1441 okc_debug.Reset_Indentation;
1442 END IF;
1443
1444 RETURN (l_return_status);
1445
1446 EXCEPTION
1447 WHEN item_not_found_error THEN
1448
1449 IF (l_debug = 'Y') THEN
1450 okc_debug.log('4800: Exiting validate_foreign_keys:item_not_found_error Exception', 2);
1451 okc_debug.Reset_Indentation;
1452 END IF;
1453
1454 l_return_status := OKC_API.G_RET_STS_ERROR;
1455 RETURN (l_return_status);
1456
1457 END validate_foreign_keys;
1458 BEGIN
1459
1460 l_return_status := validate_foreign_keys (p_patv_rec);
1461 RETURN (l_return_status);
1462
1463 END Validate_Record;
1464
1465 ---------------------------------------------------------------------------
1466 -- PROCEDURE Migrate
1467 ---------------------------------------------------------------------------
1468 PROCEDURE migrate (
1469 p_from IN patv_rec_type,
1470 p_to IN OUT NOCOPY pat_rec_type
1471 ) IS
1472 BEGIN
1473
1474 p_to.id := p_from.id;
1475 p_to.pat_id := p_from.pat_id;
1476 p_to.chr_id := p_from.chr_id;
1477 p_to.cle_id := p_from.cle_id;
1478 p_to.bsl_id := p_from.bsl_id;
1479 p_to.bcl_id := p_from.bcl_id;
1480 p_to.created_by := p_from.created_by;
1481 p_to.creation_date := p_from.creation_date;
1482 p_to.last_updated_by := p_from.last_updated_by;
1483 p_to.last_update_date := p_from.last_update_date;
1484 p_to.modified_from := p_from.modified_from;
1485 p_to.modified_to := p_from.modified_to;
1486 p_to.modifier_mechanism_type_code := p_from.modifier_mechanism_type_code;
1487 p_to.operand := p_from.operand;
1488 p_to.arithmetic_operator := p_from.arithmetic_operator;
1489 p_to.automatic_flag := p_from.automatic_flag;
1490 p_to.update_allowed := p_from.update_allowed;
1491 p_to.updated_flag := p_from.updated_flag;
1492 p_to.applied_flag := p_from.applied_flag;
1493 p_to.on_invoice_flag := p_from.on_invoice_flag;
1494 p_to.pricing_phase_id := p_from.pricing_phase_id;
1495 p_to.context := p_from.context;
1496
1497 p_to.program_application_id := p_from.program_application_id;
1498 p_to.program_id := p_from.program_id;
1499 p_to.program_update_date:= p_from.program_update_date;
1500 p_to.request_id := p_from.request_id;
1501 p_to.list_header_id := p_from.list_header_id;
1502 p_to.list_line_id := p_from.list_line_id;
1503 p_to.list_line_type_code := p_from.list_line_type_code;
1504 p_to.change_reason_code := p_from.change_reason_code;
1505 p_to.change_reason_text := p_from.change_reason_text;
1506 p_to.estimated_flag := p_from.estimated_flag;
1507 p_to.adjusted_amount := p_from.adjusted_amount;
1508 p_to.charge_type_code := p_from.charge_type_code;
1509 p_to.charge_subtype_code := p_from.charge_subtype_code;
1510 p_to.range_break_quantity := p_from.range_break_quantity;
1511 p_to.accrual_conversion_rate := p_from.accrual_conversion_rate;
1512 p_to.pricing_group_sequence := p_from.pricing_group_sequence;
1513 p_to.accrual_flag := p_from.accrual_flag;
1514 p_to.list_line_no := p_from.list_line_no;
1515 p_to.source_system_code := p_from.source_system_code;
1516 p_to.benefit_qty := p_from.benefit_qty;
1517 p_to.benefit_uom_code := p_from.benefit_uom_code;
1518 p_to.expiration_date := p_from.expiration_date;
1519 p_to.modifier_level_code := p_from.modifier_level_code;
1520 p_to.price_break_type_code := p_from.price_break_type_code;
1521 p_to.substitution_attribute := p_from.substitution_attribute;
1522 p_to.proration_type_code := p_from.proration_type_code;
1523 p_to.include_on_returns_flag := p_from.include_on_returns_flag;
1524 p_to.object_version_number := p_from.object_version_number;
1525 p_to.attribute1 := p_from.attribute1;
1526 p_to.attribute2 := p_from.attribute2;
1527 p_to.attribute3 := p_from.attribute3;
1528 p_to.attribute4 := p_from.attribute4;
1529 p_to.attribute5 := p_from.attribute5;
1530 p_to.attribute6 := p_from.attribute6;
1531 p_to.attribute7 := p_from.attribute7;
1532 p_to.attribute8 := p_from.attribute8;
1533 p_to.attribute9 := p_from.attribute9;
1534 p_to.attribute10 := p_from.attribute10;
1535 p_to.attribute11 := p_from.attribute11;
1536 p_to.attribute12 := p_from.attribute12;
1537 p_to.attribute13 := p_from.attribute13;
1538 p_to.attribute14 := p_from.attribute14;
1539 p_to.attribute15 := p_from.attribute15;
1540 p_to.last_update_login := p_from.last_update_login;
1541 p_to.rebate_transaction_type_code := p_from.rebate_transaction_type_code;
1542
1543 END migrate;
1544 PROCEDURE migrate (
1545 p_from IN pat_rec_type,
1546 p_to IN OUT NOCOPY patv_rec_type
1547 ) IS
1548 BEGIN
1549
1550 p_to.id := p_from.id;
1551 p_to.pat_id := p_from.pat_id;
1552 p_to.chr_id := p_from.chr_id;
1553 p_to.cle_id := p_from.cle_id;
1554 p_to.bsl_id := p_from.bsl_id;
1555 p_to.bcl_id := p_from.bcl_id;
1556 p_to.created_by := p_from.created_by;
1557 p_to.creation_date := p_from.creation_date;
1558 p_to.last_updated_by := p_from.last_updated_by;
1559 p_to.last_update_date := p_from.last_update_date;
1560 p_to.modified_from := p_from.modified_from;
1561 p_to.modified_to := p_from.modified_to;
1562 p_to.modifier_mechanism_type_code := p_from.modifier_mechanism_type_code;
1563 p_to.operand := p_from.operand;
1564 p_to.arithmetic_operator := p_from.arithmetic_operator;
1565 p_to.automatic_flag := p_from.automatic_flag;
1566 p_to.update_allowed := p_from.update_allowed;
1567 p_to.updated_flag := p_from.updated_flag;
1568 p_to.applied_flag := p_from.applied_flag;
1569 p_to.on_invoice_flag := p_from.on_invoice_flag;
1570 p_to.pricing_phase_id := p_from.pricing_phase_id;
1571 p_to.context := p_from.context;
1572 p_to.program_application_id := p_from.program_application_id;
1573 p_to.program_id := p_from.program_id;
1574 p_to.program_update_date:= p_from.program_update_date;
1575 p_to.request_id := p_from.request_id;
1576 p_to.list_header_id := p_from.list_header_id;
1577 p_to.list_line_id := p_from.list_line_id;
1578 p_to.list_line_type_code := p_from.list_line_type_code;
1579 p_to.change_reason_code := p_from.change_reason_code;
1580 p_to.change_reason_text := p_from.change_reason_text;
1581 p_to.estimated_flag := p_from.estimated_flag;
1582 p_to.adjusted_amount := p_from.adjusted_amount;
1583 p_to.charge_type_code := p_from.charge_type_code;
1584 p_to.charge_subtype_code := p_from.charge_subtype_code;
1585 p_to.range_break_quantity := p_from.range_break_quantity;
1586 p_to.accrual_conversion_rate := p_from.accrual_conversion_rate;
1587 p_to.pricing_group_sequence := p_from.pricing_group_sequence;
1588 p_to.accrual_flag := p_from.accrual_flag;
1589 p_to.list_line_no := p_from.list_line_no;
1590 p_to.source_system_code := p_from.source_system_code;
1591 p_to.benefit_qty := p_from.benefit_qty;
1592 p_to.benefit_uom_code := p_from.benefit_uom_code;
1593 p_to.expiration_date := p_from.expiration_date;
1594 p_to.modifier_level_code := p_from.modifier_level_code;
1595 p_to.price_break_type_code := p_from.price_break_type_code;
1596 p_to.substitution_attribute := p_from.substitution_attribute;
1597 p_to.proration_type_code := p_from.proration_type_code;
1598 p_to.include_on_returns_flag := p_from.include_on_returns_flag;
1599 p_to.object_version_number := p_from.object_version_number;
1600
1601
1602 p_to.attribute1 := p_from.attribute1;
1603 p_to.attribute2 := p_from.attribute2;
1604 p_to.attribute3 := p_from.attribute3;
1605 p_to.attribute4 := p_from.attribute4;
1606 p_to.attribute5 := p_from.attribute5;
1607 p_to.attribute6 := p_from.attribute6;
1608 p_to.attribute7 := p_from.attribute7;
1609 p_to.attribute8 := p_from.attribute8;
1610 p_to.attribute9 := p_from.attribute9;
1611 p_to.attribute10 := p_from.attribute10;
1612 p_to.attribute11 := p_from.attribute11;
1613 p_to.attribute12 := p_from.attribute12;
1614 p_to.attribute13 := p_from.attribute13;
1615 p_to.attribute14 := p_from.attribute14;
1616 p_to.attribute15 := p_from.attribute15;
1617 p_to.last_update_login := p_from.last_update_login;
1618 p_to.rebate_transaction_type_code := p_from.rebate_transaction_type_code;
1619
1620 END migrate;
1621
1622 ---------------------------------------------------------------------------
1623 -- PROCEDURE validate_row
1624 ---------------------------------------------------------------------------
1625 ----------------------------------------------
1626 -- validate_row for:OKC_PRICE_ADJUSTMENTS_V --
1627 ----------------------------------------------
1628 PROCEDURE validate_row(
1629 p_api_version IN NUMBER,
1630 p_init_msg_list IN VARCHAR2 ,
1631 x_return_status OUT NOCOPY VARCHAR2,
1632 x_msg_count OUT NOCOPY NUMBER,
1633 x_msg_data OUT NOCOPY VARCHAR2,
1634 p_patv_rec IN patv_rec_type) IS
1635
1636 l_api_version CONSTANT NUMBER := 1;
1637 l_api_name CONSTANT VARCHAR2(30) := 'V_validate_row';
1638 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1639 l_patv_rec patv_rec_type := p_patv_rec;
1640 l_pat_rec pat_rec_type;
1641 BEGIN
1642
1643 IF (l_debug = 'Y') THEN
1644 okc_debug.Set_Indentation('OKC_PAT_PVT');
1645 okc_debug.log('5200: Entered validate_row', 2);
1646 END IF;
1647
1648 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1649 G_PKG_NAME,
1650 p_init_msg_list,
1651 l_api_version,
1652 p_api_version,
1653 '_PVT',
1654 x_return_status);
1655 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1656 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1657 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1658 RAISE OKC_API.G_EXCEPTION_ERROR;
1659 END IF;
1660 --- Validate all non-missing attributes (Item Level Validation)
1661 l_return_status := Validate_Attributes(l_patv_rec);
1662 --- If any errors happen abort API
1663 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1664 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1665 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1666 RAISE OKC_API.G_EXCEPTION_ERROR;
1667 END IF;
1668 l_return_status := Validate_Record(l_patv_rec);
1669 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1670 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1671 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1672 RAISE OKC_API.G_EXCEPTION_ERROR;
1673 END IF;
1674 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1675
1676 IF (l_debug = 'Y') THEN
1677 okc_debug.log('5300: Leaving validate_row', 2);
1678 okc_debug.Reset_Indentation;
1679 END IF;
1680
1681 EXCEPTION
1682 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1683
1684 IF (l_debug = 'Y') THEN
1685 okc_debug.log('5400: Exiting validate_row:OKC_API.G_EXCEPTION_ERROR Exception', 2);
1686 okc_debug.Reset_Indentation;
1687 END IF;
1688
1689 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1690 (
1691 l_api_name,
1692 G_PKG_NAME,
1693 'OKC_API.G_RET_STS_ERROR',
1694 x_msg_count,
1695 x_msg_data,
1696 '_PVT'
1697 );
1698 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1699
1700 IF (l_debug = 'Y') THEN
1701 okc_debug.log('5500: Exiting validate_row:OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception', 2);
1702 okc_debug.Reset_Indentation;
1703 END IF;
1704
1705 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1706 (
1707 l_api_name,
1708 G_PKG_NAME,
1709 'OKC_API.G_RET_STS_UNEXP_ERROR',
1710 x_msg_count,
1711 x_msg_data,
1712 '_PVT'
1713 );
1714 WHEN OTHERS THEN
1715
1716 IF (l_debug = 'Y') THEN
1717 okc_debug.log('5600: Exiting validate_row:OTHERS Exception', 2);
1718 okc_debug.Reset_Indentation;
1719 END IF;
1720
1721 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1722 (
1723 l_api_name,
1724 G_PKG_NAME,
1725 'OTHERS',
1726 x_msg_count,
1727 x_msg_data,
1728 '_PVT'
1729 );
1730
1731 END validate_row;
1732 ------------------------------------------
1733 -- PL/SQL TBL validate_row for:PATV_TBL --
1734 ------------------------------------------
1735 PROCEDURE validate_row(
1736 p_api_version IN NUMBER,
1737 p_init_msg_list IN VARCHAR2 ,
1738 x_return_status OUT NOCOPY VARCHAR2,
1739 x_msg_count OUT NOCOPY NUMBER,
1740 x_msg_data OUT NOCOPY VARCHAR2,
1741 p_patv_tbl IN patv_tbl_type) IS
1742
1743 l_api_version CONSTANT NUMBER := 1;
1744 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_validate_row';
1745 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1746 i NUMBER := 0;
1747 BEGIN
1748
1749 IF (l_debug = 'Y') THEN
1750 okc_debug.Set_Indentation('OKC_PAT_PVT');
1751 okc_debug.log('5700: Entered validate_row', 2);
1752 END IF;
1753
1754 OKC_API.init_msg_list(p_init_msg_list);
1755 -- Make sure PL/SQL table has records in it before passing
1756 IF (p_patv_tbl.COUNT > 0) THEN
1757 i := p_patv_tbl.FIRST;
1758 LOOP
1759 validate_row (
1760 p_api_version => p_api_version,
1761 p_init_msg_list => OKC_API.G_FALSE,
1762 x_return_status => x_return_status,
1763 x_msg_count => x_msg_count,
1764 x_msg_data => x_msg_data,
1765 p_patv_rec => p_patv_tbl(i));
1766 EXIT WHEN (i = p_patv_tbl.LAST);
1767 i := p_patv_tbl.NEXT(i);
1768 END LOOP;
1769 END IF;
1770
1771 IF (l_debug = 'Y') THEN
1772 okc_debug.log('5800: Exiting validate_row', 2);
1773 okc_debug.Reset_Indentation;
1774 END IF;
1775
1776 EXCEPTION
1777 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1778
1779 IF (l_debug = 'Y') THEN
1780 okc_debug.log('5900: Exiting validate_row:OKC_API.G_EXCEPTION_ERROR Exception', 2);
1781 okc_debug.Reset_Indentation;
1782 END IF;
1783
1784 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1785 (
1786 l_api_name,
1787 G_PKG_NAME,
1788 'OKC_API.G_RET_STS_ERROR',
1789 x_msg_count,
1790 x_msg_data,
1791 '_PVT'
1792 );
1793 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1794
1795 IF (l_debug = 'Y') THEN
1796 okc_debug.log('6000: Exiting validate_row:OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception', 2);
1797 okc_debug.Reset_Indentation;
1798 END IF;
1799
1800 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1801 (
1802 l_api_name,
1803 G_PKG_NAME,
1804 'OKC_API.G_RET_STS_UNEXP_ERROR',
1805 x_msg_count,
1806 x_msg_data,
1807 '_PVT'
1808 );
1809 WHEN OTHERS THEN
1810
1811 IF (l_debug = 'Y') THEN
1812 okc_debug.log('6100: Exiting validate_row:OTHERS Exception', 2);
1813 okc_debug.Reset_Indentation;
1814 END IF;
1815
1816 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1817 (
1818 l_api_name,
1819 G_PKG_NAME,
1820 'OTHERS',
1821 x_msg_count,
1822 x_msg_data,
1823 '_PVT'
1824 );
1825
1826 END validate_row;
1827
1828 ---------------------------------------------------------------------------
1829 -- PROCEDURE insert_row
1830 ---------------------------------------------------------------------------
1831 ------------------------------------------
1832 -- insert_row for:OKC_PRICE_ADJUSTMENTS --
1833 ------------------------------------------
1834 PROCEDURE insert_row(
1835 p_init_msg_list IN VARCHAR2 ,
1836 x_return_status OUT NOCOPY VARCHAR2,
1837 x_msg_count OUT NOCOPY NUMBER,
1838 x_msg_data OUT NOCOPY VARCHAR2,
1839 p_pat_rec IN pat_rec_type,
1840 x_pat_rec OUT NOCOPY pat_rec_type) IS
1841
1842 l_api_version CONSTANT NUMBER := 1;
1843 l_api_name CONSTANT VARCHAR2(30) := 'ADJUSTMENTS_insert_row';
1844 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1845 l_pat_rec pat_rec_type := p_pat_rec;
1846 l_def_pat_rec pat_rec_type;
1847 ----------------------------------------------
1848 -- Set_Attributes for:OKC_PRICE_ADJUSTMENTS --
1849 ----------------------------------------------
1850 FUNCTION Set_Attributes (
1851 p_pat_rec IN pat_rec_type,
1852 x_pat_rec OUT NOCOPY pat_rec_type
1853 ) RETURN VARCHAR2 IS
1854 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1855 BEGIN
1856
1857 x_pat_rec := p_pat_rec;
1858 RETURN(l_return_status);
1859
1860 END Set_Attributes;
1861 BEGIN
1862
1863 IF (l_debug = 'Y') THEN
1864 okc_debug.Set_Indentation('OKC_PAT_PVT');
1865 okc_debug.log('6300: Entered insert_row', 2);
1866 END IF;
1867
1868 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1869 p_init_msg_list,
1870 '_PVT',
1871 x_return_status);
1872 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1873 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1874 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1875 RAISE OKC_API.G_EXCEPTION_ERROR;
1876 END IF;
1877 --- Setting item attributes
1878 l_return_status := Set_Attributes(
1879 p_pat_rec, -- IN
1880 l_pat_rec); -- OUT
1881 --- If any errors happen abort API
1882 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1883 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1884 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1885 RAISE OKC_API.G_EXCEPTION_ERROR;
1886 END IF;
1887 INSERT INTO OKC_PRICE_ADJUSTMENTS(
1888 id,
1889 pat_id,
1890 chr_id,
1891 cle_id,
1892 bsl_id,
1893 bcl_id,
1894 created_by,
1895 creation_date,
1896 last_updated_by,
1897 last_update_date,
1898 modified_from,
1899 modified_to,
1900 modifier_mechanism_type_code,
1901 operand,
1902 arithmetic_operator,
1903 automatic_flag,
1904 update_allowed,
1905 updated_flag,
1906 applied_flag,
1907 on_invoice_flag,
1908 pricing_phase_id,
1909 context,
1910 program_application_id,
1911 program_id,
1912 program_update_date,
1913 request_id,
1914 list_header_id,
1915 list_line_id,
1916 list_line_type_code,
1917 change_reason_code,
1918 change_reason_text,
1919 estimated_flag,
1920 adjusted_amount,
1921 charge_type_code,
1922 charge_subtype_code,
1923 range_break_quantity,
1924 accrual_conversion_rate,
1925 pricing_group_sequence,
1926 accrual_flag,
1927 list_line_no,
1928 source_system_code,
1929 benefit_qty,
1930 benefit_uom_code,
1931 expiration_date,
1932 modifier_level_code,
1933 price_break_type_code,
1934 substitution_attribute,
1935 proration_type_code,
1936 include_on_returns_flag,
1937 object_version_number,
1938 attribute1,
1939 attribute2,
1940 attribute3,
1941 attribute4,
1942 attribute5,
1943 attribute6,
1944 attribute7,
1945 attribute8,
1946 attribute9,
1947 attribute10,
1948 attribute11,
1949 attribute12,
1950 attribute13,
1951 attribute14,
1952 attribute15,
1953 last_update_login,
1954 rebate_transaction_type_code)
1955 VALUES (
1956 l_pat_rec.id,
1957 l_pat_rec.pat_id,
1958 l_pat_rec.chr_id,
1959 l_pat_rec.cle_id,
1960 l_pat_rec.bsl_id,
1961 l_pat_rec.bcl_id,
1962 l_pat_rec.created_by,
1963 l_pat_rec.creation_date,
1964 l_pat_rec.last_updated_by,
1965 l_pat_rec.last_update_date,
1966 l_pat_rec.modified_from,
1967 l_pat_rec.modified_to,
1968 l_pat_rec.modifier_mechanism_type_code,
1969 l_pat_rec.operand,
1970 l_pat_rec.arithmetic_operator,
1971 l_pat_rec.automatic_flag,
1972 l_pat_rec.update_allowed,
1973 l_pat_rec.updated_flag,
1974 l_pat_rec.applied_flag,
1975 l_pat_rec.on_invoice_flag,
1976 l_pat_rec.pricing_phase_id,
1977 l_pat_rec.context,
1978 decode(FND_GLOBAL.PROG_APPL_ID,-1,NULL,FND_GLOBAL.PROG_APPL_ID),
1979 decode(FND_GLOBAL.CONC_PROGRAM_ID,-1,NULL,FND_GLOBAL.CONC_PROGRAM_ID),
1980 decode(FND_GLOBAL.CONC_REQUEST_ID,-1,NULL,SYSDATE),
1981 decode(FND_GLOBAL.CONC_REQUEST_ID,-1,NULL,FND_GLOBAL.CONC_REQUEST_ID),
1982 l_pat_rec.list_header_id,
1983 l_pat_rec.list_line_id,
1984 l_pat_rec.list_line_type_code,
1985 l_pat_rec.change_reason_code,
1986 l_pat_rec.change_reason_text,
1987 l_pat_rec.estimated_flag,
1988 l_pat_rec.adjusted_amount,
1989 l_pat_rec.charge_type_code,
1990 l_pat_rec.charge_subtype_code,
1991 l_pat_rec.range_break_quantity,
1992 l_pat_rec.accrual_conversion_rate,
1993 l_pat_rec.pricing_group_sequence,
1994 l_pat_rec.accrual_flag,
1995 l_pat_rec.list_line_no,
1996 l_pat_rec.source_system_code,
1997 l_pat_rec.benefit_qty,
1998 l_pat_rec.benefit_uom_code,
1999 l_pat_rec.expiration_date,
2000 l_pat_rec.modifier_level_code,
2001 l_pat_rec.price_break_type_code,
2002 l_pat_rec.substitution_attribute,
2003 l_pat_rec.proration_type_code,
2004 l_pat_rec.include_on_returns_flag,
2005 l_pat_rec.object_version_number,
2006 l_pat_rec.attribute1,
2007 l_pat_rec.attribute2,
2008 l_pat_rec.attribute3,
2009 l_pat_rec.attribute4,
2010 l_pat_rec.attribute5,
2011 l_pat_rec.attribute6,
2012 l_pat_rec.attribute7,
2013 l_pat_rec.attribute8,
2014 l_pat_rec.attribute9,
2015 l_pat_rec.attribute10,
2016 l_pat_rec.attribute11,
2017 l_pat_rec.attribute12,
2018 l_pat_rec.attribute13,
2019 l_pat_rec.attribute14,
2020 l_pat_rec.attribute15,
2021 l_pat_rec.last_update_login,
2022 l_pat_rec.rebate_transaction_type_code);
2023 -- Set OUT values
2024 x_pat_rec := l_pat_rec;
2025 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2026
2027 IF (l_debug = 'Y') THEN
2028 okc_debug.log('6400: Exiting insert_row', 2);
2029 okc_debug.Reset_Indentation;
2030 END IF;
2031
2032 EXCEPTION
2033 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2034
2035 IF (l_debug = 'Y') THEN
2036 okc_debug.log('6500: Exiting insert_row:OKC_API.G_EXCEPTION_ERROR Exception', 2);
2037 okc_debug.Reset_Indentation;
2038 END IF;
2039
2040 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2041 (
2042 l_api_name,
2043 G_PKG_NAME,
2044 'OKC_API.G_RET_STS_ERROR',
2045 x_msg_count,
2046 x_msg_data,
2047 '_PVT'
2048 );
2049 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2050
2051 IF (l_debug = 'Y') THEN
2052 okc_debug.log('6600: Exiting insert_row:OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception', 2);
2053 okc_debug.Reset_Indentation;
2054 END IF;
2055
2056 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2057 (
2058 l_api_name,
2059 G_PKG_NAME,
2060 'OKC_API.G_RET_STS_UNEXP_ERROR',
2061 x_msg_count,
2062 x_msg_data,
2063 '_PVT'
2064 );
2065 WHEN OTHERS THEN
2066
2067 IF (l_debug = 'Y') THEN
2068 okc_debug.log('6700: Exiting insert_row:OTHERS Exception', 2);
2069 okc_debug.Reset_Indentation;
2070 END IF;
2071
2072 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2073 (
2074 l_api_name,
2075 G_PKG_NAME,
2076 'OTHERS',
2077 x_msg_count,
2078 x_msg_data,
2079 '_PVT'
2080 );
2081
2082 END insert_row;
2083 --------------------------------------------
2084 -- insert_row for:OKC_PRICE_ADJUSTMENTS_V --
2085 --------------------------------------------
2086 PROCEDURE insert_row(
2087 p_api_version IN NUMBER,
2088 p_init_msg_list IN VARCHAR2 ,
2089 x_return_status OUT NOCOPY VARCHAR2,
2090 x_msg_count OUT NOCOPY NUMBER,
2091 x_msg_data OUT NOCOPY VARCHAR2,
2092 p_patv_rec IN patv_rec_type,
2093 x_patv_rec OUT NOCOPY patv_rec_type) IS
2094
2095 l_api_version CONSTANT NUMBER := 1;
2096 l_api_name CONSTANT VARCHAR2(30) := 'V_insert_row';
2097 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2098 l_patv_rec patv_rec_type;
2099 l_def_patv_rec patv_rec_type;
2100 l_pat_rec pat_rec_type;
2101 lx_pat_rec pat_rec_type;
2102 -------------------------------
2103 -- FUNCTION fill_who_columns --
2104 -------------------------------
2105 FUNCTION fill_who_columns (
2106 p_patv_rec IN patv_rec_type
2107 ) RETURN patv_rec_type IS
2108 l_patv_rec patv_rec_type := p_patv_rec;
2109 BEGIN
2110
2111 l_patv_rec.CREATION_DATE := SYSDATE;
2112 l_patv_rec.CREATED_BY := FND_GLOBAL.USER_ID;
2113 l_patv_rec.LAST_UPDATE_DATE := l_patv_rec.CREATION_DATE;
2114 l_patv_rec.LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
2115 l_patv_rec.LAST_UPDATE_LOGIN := FND_GLOBAL.LOGIN_ID;
2116 RETURN(l_patv_rec);
2117
2118 END fill_who_columns;
2119 ------------------------------------------------
2120 -- Set_Attributes for:OKC_PRICE_ADJUSTMENTS_V --
2121 ------------------------------------------------
2122 FUNCTION Set_Attributes (
2123 p_patv_rec IN patv_rec_type,
2124 x_patv_rec OUT NOCOPY patv_rec_type
2125 ) RETURN VARCHAR2 IS
2126 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2127 BEGIN
2128
2129 x_patv_rec := p_patv_rec;
2130 x_patv_rec.OBJECT_VERSION_NUMBER := 1;
2131 RETURN(l_return_status);
2132
2133 END Set_Attributes;
2134 BEGIN
2135
2136 IF (l_debug = 'Y') THEN
2137 okc_debug.Set_Indentation('OKC_PAT_PVT');
2138 okc_debug.log('7000: Entered insert_row', 2);
2139 END IF;
2140
2141 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
2142 G_PKG_NAME,
2143 p_init_msg_list,
2144 l_api_version,
2145 p_api_version,
2146 '_PVT',
2147 x_return_status);
2148 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2149 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2150 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2151 RAISE OKC_API.G_EXCEPTION_ERROR;
2152 END IF;
2153 l_patv_rec := null_out_defaults(p_patv_rec);
2154 -- Set primary key value
2155 l_patv_rec.ID := get_seq_id;
2156 --- Setting item attributes
2157 l_return_status := Set_Attributes(
2158 l_patv_rec, -- IN
2159 l_def_patv_rec); -- OUT
2160 --- If any errors happen abort API
2161 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2162 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2163 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2164 RAISE OKC_API.G_EXCEPTION_ERROR;
2165 END IF;
2166 l_def_patv_rec := fill_who_columns(l_def_patv_rec);
2167 --- Validate all non-missing attributes (Item Level Validation)
2168 l_return_status := Validate_Attributes(l_def_patv_rec);
2169 --- If any errors happen abort API
2170 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2171 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2172 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2173 RAISE OKC_API.G_EXCEPTION_ERROR;
2174 END IF;
2175 l_return_status := Validate_Record(l_def_patv_rec);
2176 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2177 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2178 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2179 RAISE OKC_API.G_EXCEPTION_ERROR;
2180 END IF;
2181 --------------------------------------
2182 -- Move VIEW record to "Child" records
2183 --------------------------------------
2184 migrate(l_def_patv_rec, l_pat_rec);
2185 --------------------------------------------
2186 -- Call the INSERT_ROW for each child record
2187 --------------------------------------------
2188 insert_row(
2189 p_init_msg_list,
2190 x_return_status,
2191 x_msg_count,
2192 x_msg_data,
2193 l_pat_rec,
2194 lx_pat_rec
2195 );
2196 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2197 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2198 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
2199 RAISE OKC_API.G_EXCEPTION_ERROR;
2200 END IF;
2201 migrate(lx_pat_rec, l_def_patv_rec);
2202 -- Set OUT values
2203 x_patv_rec := l_def_patv_rec;
2204 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2205
2206 IF (l_debug = 'Y') THEN
2207 okc_debug.log('7100: Exiting insert_row', 2);
2208 okc_debug.Reset_Indentation;
2209 END IF;
2210
2211 EXCEPTION
2212 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2213
2214 IF (l_debug = 'Y') THEN
2215 okc_debug.log('7200: Exiting insert_row:OKC_API.G_EXCEPTION_ERROR Exception', 2);
2216 okc_debug.Reset_Indentation;
2217 END IF;
2218
2219 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2220 (
2221 l_api_name,
2222 G_PKG_NAME,
2223 'OKC_API.G_RET_STS_ERROR',
2224 x_msg_count,
2225 x_msg_data,
2226 '_PVT'
2227 );
2228 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2229
2230 IF (l_debug = 'Y') THEN
2231 okc_debug.log('7300: Exiting insert_row:OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception', 2);
2232 okc_debug.Reset_Indentation;
2233 END IF;
2234
2235 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2236 (
2237 l_api_name,
2238 G_PKG_NAME,
2239 'OKC_API.G_RET_STS_UNEXP_ERROR',
2240 x_msg_count,
2241 x_msg_data,
2242 '_PVT'
2243 );
2244 WHEN OTHERS THEN
2245
2246 IF (l_debug = 'Y') THEN
2247 okc_debug.log('7400: Exiting insert_row:OTHERS Exception', 2);
2248 okc_debug.Reset_Indentation;
2249 END IF;
2250
2251 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2252 (
2253 l_api_name,
2254 G_PKG_NAME,
2255 'OTHERS',
2256 x_msg_count,
2257 x_msg_data,
2258 '_PVT'
2259 );
2260
2261 END insert_row;
2262 ----------------------------------------
2263 -- PL/SQL TBL insert_row for:PATV_TBL --
2264 ----------------------------------------
2265 PROCEDURE insert_row(
2266 p_api_version IN NUMBER,
2267 p_init_msg_list IN VARCHAR2 ,
2268 x_return_status OUT NOCOPY VARCHAR2,
2269 x_msg_count OUT NOCOPY NUMBER,
2270 x_msg_data OUT NOCOPY VARCHAR2,
2271 p_patv_tbl IN patv_tbl_type,
2272 x_patv_tbl OUT NOCOPY patv_tbl_type) IS
2273
2274 l_api_version CONSTANT NUMBER := 1;
2275 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_insert_row';
2276 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2277 i NUMBER := 0;
2278 BEGIN
2279
2280 IF (l_debug = 'Y') THEN
2281 okc_debug.Set_Indentation('OKC_PAT_PVT');
2282 okc_debug.log('7500: Entered insert_row', 2);
2283 END IF;
2284
2285 OKC_API.init_msg_list(p_init_msg_list);
2286 -- Make sure PL/SQL table has records in it before passing
2287 IF (p_patv_tbl.COUNT > 0) THEN
2288 i := p_patv_tbl.FIRST;
2289 LOOP
2290 insert_row (
2291 p_api_version => p_api_version,
2292 p_init_msg_list => OKC_API.G_FALSE,
2293 x_return_status => x_return_status,
2294 x_msg_count => x_msg_count,
2295 x_msg_data => x_msg_data,
2296 p_patv_rec => p_patv_tbl(i),
2297 x_patv_rec => x_patv_tbl(i));
2298 EXIT WHEN (i = p_patv_tbl.LAST);
2299 i := p_patv_tbl.NEXT(i);
2300 END LOOP;
2301 END IF;
2302
2303 IF (l_debug = 'Y') THEN
2304 okc_debug.log('7600: Exiting insert_row', 2);
2305 okc_debug.Reset_Indentation;
2306 END IF;
2307
2308 EXCEPTION
2309 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2310
2311 IF (l_debug = 'Y') THEN
2312 okc_debug.log('7700: Exiting insert_row:OKC_API.G_EXCEPTION_ERROR Exception', 2);
2313 okc_debug.Reset_Indentation;
2314 END IF;
2315
2316 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2317 (
2318 l_api_name,
2319 G_PKG_NAME,
2320 'OKC_API.G_RET_STS_ERROR',
2321 x_msg_count,
2322 x_msg_data,
2323 '_PVT'
2324 );
2325 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2326
2327 IF (l_debug = 'Y') THEN
2328 okc_debug.log('7800: Exiting insert_row:OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception', 2);
2329 okc_debug.Reset_Indentation;
2330 END IF;
2331
2332 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2333 (
2334 l_api_name,
2335 G_PKG_NAME,
2336 'OKC_API.G_RET_STS_UNEXP_ERROR',
2337 x_msg_count,
2338 x_msg_data,
2339 '_PVT'
2340 );
2341 WHEN OTHERS THEN
2342
2343 IF (l_debug = 'Y') THEN
2344 okc_debug.log('7900: Exiting insert_row:OTHERS Exception', 2);
2345 okc_debug.Reset_Indentation;
2346 END IF;
2347
2348 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2349 (
2350 l_api_name,
2351 G_PKG_NAME,
2352 'OTHERS',
2353 x_msg_count,
2354 x_msg_data,
2355 '_PVT'
2356 );
2357
2358 END insert_row;
2359
2360 ---------------------------------------------------------------------------
2361 -- PROCEDURE lock_row
2362 ---------------------------------------------------------------------------
2363 ----------------------------------------
2364 -- lock_row for:OKC_PRICE_ADJUSTMENTS --
2365 ----------------------------------------
2366 PROCEDURE lock_row(
2367 p_init_msg_list IN VARCHAR2 ,
2368 x_return_status OUT NOCOPY VARCHAR2,
2369 x_msg_count OUT NOCOPY NUMBER,
2370 x_msg_data OUT NOCOPY VARCHAR2,
2371 p_pat_rec IN pat_rec_type) IS
2372
2373 E_Resource_Busy EXCEPTION;
2374 PRAGMA EXCEPTION_INIT(E_Resource_Busy, -00054);
2375 CURSOR lock_csr (p_pat_rec IN pat_rec_type) IS
2376 SELECT OBJECT_VERSION_NUMBER
2377 FROM OKC_PRICE_ADJUSTMENTS
2378 WHERE ID = p_pat_rec.id
2379 AND OBJECT_VERSION_NUMBER = p_pat_rec.object_version_number
2380 FOR UPDATE OF OBJECT_VERSION_NUMBER NOWAIT;
2381
2382 CURSOR lchk_csr (p_pat_rec IN pat_rec_type) IS
2383 SELECT OBJECT_VERSION_NUMBER
2384 FROM OKC_PRICE_ADJUSTMENTS
2385 WHERE ID = p_pat_rec.id;
2386
2387 l_api_version CONSTANT NUMBER := 1;
2388 l_api_name CONSTANT VARCHAR2(30) := 'ADJUSTMENTS_lock_row';
2389 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2390 l_object_version_number OKC_PRICE_ADJUSTMENTS.OBJECT_VERSION_NUMBER%TYPE;
2391 lc_object_version_number OKC_PRICE_ADJUSTMENTS.OBJECT_VERSION_NUMBER%TYPE;
2392 l_row_notfound BOOLEAN := FALSE;
2393 lc_row_notfound BOOLEAN := FALSE;
2394 BEGIN
2395
2396 IF (l_debug = 'Y') THEN
2397 okc_debug.Set_Indentation('OKC_PAT_PVT');
2398 okc_debug.log('8000: Entered lock_row', 2);
2399 END IF;
2400
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 BEGIN
2411
2412 IF (l_debug = 'Y') THEN
2413 okc_debug.Set_Indentation('OKC_PAT_PVT');
2414 okc_debug.log('8100: Entered lock_row', 2);
2415 END IF;
2416
2417 OPEN lock_csr(p_pat_rec);
2418 FETCH lock_csr INTO l_object_version_number;
2419 l_row_notfound := lock_csr%NOTFOUND;
2420 CLOSE lock_csr;
2421
2422 IF (l_debug = 'Y') THEN
2423 okc_debug.log('8200: Exiting lock_row', 2);
2424 okc_debug.Reset_Indentation;
2425 END IF;
2426
2427 EXCEPTION
2428 WHEN E_Resource_Busy THEN
2429
2430 IF (l_debug = 'Y') THEN
2431 okc_debug.log('8300: Exiting lock_row:E_Resource_Busy Exception', 2);
2432 okc_debug.Reset_Indentation;
2433 END IF;
2434
2435 IF (lock_csr%ISOPEN) THEN
2436 CLOSE lock_csr;
2437 END IF;
2438 OKC_API.set_message(G_FND_APP,G_FORM_UNABLE_TO_RESERVE_REC);
2439 RAISE APP_EXCEPTIONS.RECORD_LOCK_EXCEPTION;
2440 END;
2441
2442 IF ( l_row_notfound ) THEN
2443 OPEN lchk_csr(p_pat_rec);
2444 FETCH lchk_csr INTO lc_object_version_number;
2445 lc_row_notfound := lchk_csr%NOTFOUND;
2446 CLOSE lchk_csr;
2447 END IF;
2448 IF (lc_row_notfound) THEN
2449 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_DELETED);
2450 RAISE OKC_API.G_EXCEPTION_ERROR;
2451 ELSIF lc_object_version_number > p_pat_rec.object_version_number THEN
2452 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
2453 RAISE OKC_API.G_EXCEPTION_ERROR;
2454 ELSIF lc_object_version_number <> p_pat_rec.object_version_number THEN
2455 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
2456 RAISE OKC_API.G_EXCEPTION_ERROR;
2457 ELSIF lc_object_version_number = -1 THEN
2458 OKC_API.set_message(G_APP_NAME,G_RECORD_LOGICALLY_DELETED);
2459 RAISE OKC_API.G_EXCEPTION_ERROR;
2460 END IF;
2461
2462 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2463
2464 IF (l_debug = 'Y') THEN
2465 okc_debug.log('8400: Exiting lock_row', 2);
2466 okc_debug.Reset_Indentation;
2467 END IF;
2468
2469 EXCEPTION
2470 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2471
2472 IF (l_debug = 'Y') THEN
2473 okc_debug.log('8500: Exiting lock_row:OKC_API.G_EXCEPTION_ERROR Exception', 2);
2474 okc_debug.Reset_Indentation;
2475 END IF;
2476
2477 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2478 (
2479 l_api_name,
2480 G_PKG_NAME,
2481 'OKC_API.G_RET_STS_ERROR',
2482 x_msg_count,
2483 x_msg_data,
2484 '_PVT'
2485 );
2486 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2487
2488 IF (l_debug = 'Y') THEN
2489 okc_debug.log('8600: Exiting lock_row:OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception', 2);
2490 okc_debug.Reset_Indentation;
2491 END IF;
2492
2493 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2494 (
2495 l_api_name,
2496 G_PKG_NAME,
2497 'OKC_API.G_RET_STS_UNEXP_ERROR',
2498 x_msg_count,
2499 x_msg_data,
2500 '_PVT'
2501 );
2502 WHEN OTHERS THEN
2503
2504 IF (l_debug = 'Y') THEN
2505 okc_debug.log('8700: Exiting lock_row:OTHERS Exception', 2);
2506 okc_debug.Reset_Indentation;
2507 END IF;
2508
2509 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2510 (
2511 l_api_name,
2512 G_PKG_NAME,
2513 'OTHERS',
2514 x_msg_count,
2515 x_msg_data,
2516 '_PVT'
2517 );
2518
2519 END lock_row;
2520 ------------------------------------------
2521 -- lock_row for:OKC_PRICE_ADJUSTMENTS_V --
2522 ------------------------------------------
2523 PROCEDURE lock_row(
2524 p_api_version IN NUMBER,
2525 p_init_msg_list IN VARCHAR2 ,
2526 x_return_status OUT NOCOPY VARCHAR2,
2527 x_msg_count OUT NOCOPY NUMBER,
2528 x_msg_data OUT NOCOPY VARCHAR2,
2529 p_patv_rec IN patv_rec_type) IS
2530
2531 l_api_version CONSTANT NUMBER := 1;
2532 l_api_name CONSTANT VARCHAR2(30) := 'V_lock_row';
2533 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2534 l_pat_rec pat_rec_type;
2535 BEGIN
2536
2537 IF (l_debug = 'Y') THEN
2538 okc_debug.Set_Indentation('OKC_PAT_PVT');
2539 okc_debug.log('8800: Entered lock_row', 2);
2540 END IF;
2541
2542 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
2543 G_PKG_NAME,
2544 p_init_msg_list,
2545 l_api_version,
2546 p_api_version,
2547 '_PVT',
2548 x_return_status);
2549 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2550 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2551 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2552 RAISE OKC_API.G_EXCEPTION_ERROR;
2553 END IF;
2554 --------------------------------------
2555 -- Move VIEW record to "Child" records
2556 --------------------------------------
2557 migrate(p_patv_rec, l_pat_rec);
2558 --------------------------------------------
2559 -- Call the LOCK_ROW for each child record
2560 --------------------------------------------
2561 lock_row(
2562 p_init_msg_list,
2563 x_return_status,
2564 x_msg_count,
2565 x_msg_data,
2566 l_pat_rec
2567 );
2568 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2569 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2570 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
2571 RAISE OKC_API.G_EXCEPTION_ERROR;
2572 END IF;
2573 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2574
2575 IF (l_debug = 'Y') THEN
2576 okc_debug.log('8900: Exiting lock_row', 2);
2577 okc_debug.Reset_Indentation;
2578 END IF;
2579
2580 EXCEPTION
2581 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2582
2583 IF (l_debug = 'Y') THEN
2584 okc_debug.log('9000: Exiting lock_row:OKC_API.G_EXCEPTION_ERROR Exception', 2);
2585 okc_debug.Reset_Indentation;
2586 END IF;
2587
2588 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2589 (
2590 l_api_name,
2591 G_PKG_NAME,
2592 'OKC_API.G_RET_STS_ERROR',
2593 x_msg_count,
2594 x_msg_data,
2595 '_PVT'
2596 );
2597 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2598
2599 IF (l_debug = 'Y') THEN
2600 okc_debug.log('9100: Exiting lock_row:OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception', 2);
2601 okc_debug.Reset_Indentation;
2602 END IF;
2603
2604 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2605 (
2606 l_api_name,
2607 G_PKG_NAME,
2608 'OKC_API.G_RET_STS_UNEXP_ERROR',
2609 x_msg_count,
2610 x_msg_data,
2611 '_PVT'
2612 );
2613 WHEN OTHERS THEN
2614
2615 IF (l_debug = 'Y') THEN
2616 okc_debug.log('9200: Exiting lock_row:OTHERS Exception', 2);
2617 okc_debug.Reset_Indentation;
2618 END IF;
2619
2620 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2621 (
2622 l_api_name,
2623 G_PKG_NAME,
2624 'OTHERS',
2625 x_msg_count,
2626 x_msg_data,
2627 '_PVT'
2628 );
2629
2630 END lock_row;
2631 --------------------------------------
2632 -- PL/SQL TBL lock_row for:PATV_TBL --
2633 --------------------------------------
2634 PROCEDURE lock_row(
2635 p_api_version IN NUMBER,
2636 p_init_msg_list IN VARCHAR2 ,
2637 x_return_status OUT NOCOPY VARCHAR2,
2638 x_msg_count OUT NOCOPY NUMBER,
2639 x_msg_data OUT NOCOPY VARCHAR2,
2640 p_patv_tbl IN patv_tbl_type) IS
2641
2642 l_api_version CONSTANT NUMBER := 1;
2643 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_lock_row';
2644 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2645 i NUMBER := 0;
2646 BEGIN
2647
2648 IF (l_debug = 'Y') THEN
2649 okc_debug.Set_Indentation('OKC_PAT_PVT');
2650 okc_debug.log('9300: Entered lock_row', 2);
2651 END IF;
2652
2653 OKC_API.init_msg_list(p_init_msg_list);
2654 -- Make sure PL/SQL table has records in it before passing
2655 IF (p_patv_tbl.COUNT > 0) THEN
2656 i := p_patv_tbl.FIRST;
2657 LOOP
2658 lock_row (
2659 p_api_version => p_api_version,
2660 p_init_msg_list => OKC_API.G_FALSE,
2661 x_return_status => x_return_status,
2662 x_msg_count => x_msg_count,
2663 x_msg_data => x_msg_data,
2664 p_patv_rec => p_patv_tbl(i));
2665 EXIT WHEN (i = p_patv_tbl.LAST);
2666 i := p_patv_tbl.NEXT(i);
2667 END LOOP;
2668 END IF;
2669
2670 IF (l_debug = 'Y') THEN
2671 okc_debug.log('9400: Exiting lock_row', 2);
2672 okc_debug.Reset_Indentation;
2673 END IF;
2674
2675 EXCEPTION
2676 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2677
2678 IF (l_debug = 'Y') THEN
2679 okc_debug.log('9500: Exiting lock_row:OKC_API.G_EXCEPTION_ERROR Exception', 2);
2680 okc_debug.Reset_Indentation;
2681 END IF;
2682
2683 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2684 (
2685 l_api_name,
2686 G_PKG_NAME,
2687 'OKC_API.G_RET_STS_ERROR',
2688 x_msg_count,
2689 x_msg_data,
2690 '_PVT'
2691 );
2692 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2693
2694 IF (l_debug = 'Y') THEN
2695 okc_debug.log('9600: Exiting lock_row:OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception', 2);
2696 okc_debug.Reset_Indentation;
2697 END IF;
2698
2699 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2700 (
2701 l_api_name,
2702 G_PKG_NAME,
2703 'OKC_API.G_RET_STS_UNEXP_ERROR',
2704 x_msg_count,
2705 x_msg_data,
2706 '_PVT'
2707 );
2708 WHEN OTHERS THEN
2709
2710 IF (l_debug = 'Y') THEN
2711 okc_debug.log('9700: Exiting lock_row:OTHERS Exception', 2);
2712 okc_debug.Reset_Indentation;
2713 END IF;
2714
2715 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2716 (
2717 l_api_name,
2718 G_PKG_NAME,
2719 'OTHERS',
2720 x_msg_count,
2721 x_msg_data,
2722 '_PVT'
2723 );
2724
2725 END lock_row;
2726
2727 ---------------------------------------------------------------------------
2728 -- PROCEDURE update_row
2729 ---------------------------------------------------------------------------
2730 ------------------------------------------
2731 -- update_row for:OKC_PRICE_ADJUSTMENTS --
2732 ------------------------------------------
2733 PROCEDURE update_row(
2734 p_init_msg_list IN VARCHAR2 ,
2735 x_return_status OUT NOCOPY VARCHAR2,
2736 x_msg_count OUT NOCOPY NUMBER,
2737 x_msg_data OUT NOCOPY VARCHAR2,
2738 p_pat_rec IN pat_rec_type,
2739 x_pat_rec OUT NOCOPY pat_rec_type) IS
2740
2741 l_api_version CONSTANT NUMBER := 1;
2742 l_api_name CONSTANT VARCHAR2(30) := 'ADJUSTMENTS_update_row';
2743 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2744 l_pat_rec pat_rec_type := p_pat_rec;
2745 l_def_pat_rec pat_rec_type;
2746 l_row_notfound BOOLEAN := TRUE;
2747 ----------------------------------
2748 -- FUNCTION populate_new_record --
2749 ----------------------------------
2750 FUNCTION populate_new_record (
2751 p_pat_rec IN pat_rec_type,
2752 x_pat_rec OUT NOCOPY pat_rec_type
2753 ) RETURN VARCHAR2 IS
2754 l_pat_rec pat_rec_type;
2755 l_row_notfound BOOLEAN := TRUE;
2756 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2757 BEGIN
2758
2759 IF (l_debug = 'Y') THEN
2760 okc_debug.Set_Indentation('OKC_PAT_PVT');
2761 okc_debug.log('9800: Entered populate_new_record', 2);
2762 END IF;
2763
2764 x_pat_rec := p_pat_rec;
2765 -- Get current database values
2766 l_pat_rec := get_rec(p_pat_rec, l_row_notfound);
2767 IF (l_row_notfound) THEN
2768 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
2769 END IF;
2770 IF (x_pat_rec.id = OKC_API.G_MISS_NUM)
2771 THEN
2772 x_pat_rec.id := l_pat_rec.id;
2773 END IF;
2774 IF (x_pat_rec.pat_id = OKC_API.G_MISS_NUM)
2775 THEN
2776 x_pat_rec.pat_id := l_pat_rec.pat_id;
2777 END IF;
2778 IF (x_pat_rec.chr_id = OKC_API.G_MISS_NUM)
2779 THEN
2780 x_pat_rec.chr_id := l_pat_rec.chr_id;
2781 END IF;
2782 IF (x_pat_rec.cle_id = OKC_API.G_MISS_NUM)
2783 THEN
2784 x_pat_rec.cle_id := l_pat_rec.cle_id;
2785 END IF;
2786 IF (x_pat_rec.bsl_id = OKC_API.G_MISS_NUM)
2787 THEN
2788 x_pat_rec.bsl_id := l_pat_rec.bsl_id;
2789 END IF;
2790 IF (x_pat_rec.bcl_id = OKC_API.G_MISS_NUM)
2791 THEN
2792 x_pat_rec.bcl_id := l_pat_rec.bcl_id;
2793 END IF;
2794 IF (x_pat_rec.created_by = OKC_API.G_MISS_NUM)
2795 THEN
2796 x_pat_rec.created_by := l_pat_rec.created_by;
2797 END IF;
2798 IF (x_pat_rec.creation_date = OKC_API.G_MISS_DATE)
2799 THEN
2800 x_pat_rec.creation_date := l_pat_rec.creation_date;
2801 END IF;
2802 IF (x_pat_rec.last_updated_by = OKC_API.G_MISS_NUM)
2803 THEN
2804 x_pat_rec.last_updated_by := l_pat_rec.last_updated_by;
2805 END IF;
2806 IF (x_pat_rec.last_update_date = OKC_API.G_MISS_DATE)
2807 THEN
2808 x_pat_rec.last_update_date := l_pat_rec.last_update_date;
2809 END IF;
2810 IF (x_pat_rec.modified_from = OKC_API.G_MISS_NUM)
2811 THEN
2812 x_pat_rec.modified_from := l_pat_rec.modified_from;
2813 END IF;
2814 IF (x_pat_rec.modified_to = OKC_API.G_MISS_NUM)
2815 THEN
2816 x_pat_rec.modified_to := l_pat_rec.modified_to;
2817 END IF;
2818 IF (x_pat_rec.modifier_mechanism_type_code = OKC_API.G_MISS_CHAR)
2819 THEN
2820 x_pat_rec.modifier_mechanism_type_code := l_pat_rec.modifier_mechanism_type_code;
2821 END IF;
2822 IF (x_pat_rec.operand = OKC_API.G_MISS_NUM)
2823 THEN
2824 x_pat_rec.operand := l_pat_rec.operand;
2825 END IF;
2826 IF (x_pat_rec.arithmetic_operator = OKC_API.G_MISS_CHAR)
2827 THEN
2828 x_pat_rec.arithmetic_operator := l_pat_rec.arithmetic_operator;
2829 END IF;
2830 IF (x_pat_rec.automatic_flag = OKC_API.G_MISS_CHAR)
2831 THEN
2832 x_pat_rec.automatic_flag := l_pat_rec.automatic_flag;
2833 END IF;
2834 IF (x_pat_rec.update_allowed = OKC_API.G_MISS_CHAR)
2835 THEN
2836 x_pat_rec.update_allowed := l_pat_rec.update_allowed;
2837 END IF;
2838 IF (x_pat_rec.updated_flag = OKC_API.G_MISS_CHAR)
2839 THEN
2840 x_pat_rec.updated_flag := l_pat_rec.updated_flag;
2841 END IF;
2842 IF (x_pat_rec.applied_flag = OKC_API.G_MISS_CHAR)
2843 THEN
2844 x_pat_rec.applied_flag := l_pat_rec.applied_flag;
2845 END IF;
2846 IF (x_pat_rec.on_invoice_flag = OKC_API.G_MISS_CHAR)
2847 THEN
2848 x_pat_rec.on_invoice_flag := l_pat_rec.on_invoice_flag;
2849 END IF;
2850 IF (x_pat_rec.pricing_phase_id = OKC_API.G_MISS_NUM)
2851 THEN
2852 x_pat_rec.pricing_phase_id := l_pat_rec.pricing_phase_id;
2853 END IF;
2854 IF (x_pat_rec.context = OKC_API.G_MISS_CHAR)
2855 THEN
2856 x_pat_rec.context := l_pat_rec.context;
2857 END IF;
2858 IF (x_pat_rec.program_application_id = OKC_API.G_MISS_NUM)
2859 THEN
2860 x_pat_rec.program_application_id := l_pat_rec.program_application_id;
2861 END IF;
2862 IF (x_pat_rec.program_id = OKC_API.G_MISS_NUM)
2863 THEN
2864 x_pat_rec.program_id := l_pat_rec.program_id;
2865 END IF;
2866 IF (x_pat_rec.program_update_date = OKC_API.G_MISS_DATE)
2867 THEN
2868 x_pat_rec.program_update_date := l_pat_rec.program_update_date;
2869 END IF;
2870 IF (x_pat_rec.request_id = OKC_API.G_MISS_NUM)
2871 THEN
2872 x_pat_rec.request_id := l_pat_rec.request_id;
2873 END IF;
2874 IF (x_pat_rec.list_header_id = OKC_API.G_MISS_NUM)
2875 THEN
2876 x_pat_rec.list_header_id := l_pat_rec.list_header_id;
2877 END IF;
2878 IF (x_pat_rec.list_line_id = OKC_API.G_MISS_NUM)
2879 THEN
2880 x_pat_rec.list_line_id := l_pat_rec.list_line_id;
2881 END IF;
2882 IF (x_pat_rec.list_line_type_code = OKC_API.G_MISS_CHAR)
2883 THEN
2884 x_pat_rec.list_line_type_code := l_pat_rec.list_line_type_code;
2885 END IF;
2886 IF (x_pat_rec.change_reason_code = OKC_API.G_MISS_CHAR)
2887 THEN
2888 x_pat_rec.change_reason_code := l_pat_rec.change_reason_code;
2889 END IF;
2890 IF (x_pat_rec.change_reason_text = OKC_API.G_MISS_CHAR)
2891 THEN
2892 x_pat_rec.change_reason_text := l_pat_rec.change_reason_text;
2893 END IF;
2894 IF (x_pat_rec.estimated_flag = OKC_API.G_MISS_CHAR)
2895 THEN
2896 x_pat_rec.estimated_flag := l_pat_rec.estimated_flag;
2897 END IF;
2898 IF (x_pat_rec.adjusted_amount = OKC_API.G_MISS_NUM)
2899 THEN
2900 x_pat_rec.adjusted_amount := l_pat_rec.adjusted_amount;
2901 END IF;
2902 IF (x_pat_rec.charge_type_code = OKC_API.G_MISS_CHAR)
2903 THEN
2904 x_pat_rec.charge_type_code := l_pat_rec.charge_type_code ;
2905 END IF;
2906 IF (x_pat_rec.charge_subtype_code = OKC_API.G_MISS_CHAR)
2907 THEN
2908 x_pat_rec.charge_subtype_code := l_pat_rec.charge_subtype_code;
2909 END IF;
2910 IF (x_pat_rec.range_break_quantity = OKC_API.G_MISS_NUM)
2911 THEN
2912 x_pat_rec.range_break_quantity := l_pat_rec.range_break_quantity;
2913 END IF;
2914 IF (x_pat_rec.accrual_conversion_rate = OKC_API.G_MISS_NUM)
2915 THEN
2916 x_pat_rec.accrual_conversion_rate := l_pat_rec.accrual_conversion_rate;
2917 END IF;
2918 IF (x_pat_rec.pricing_group_sequence = OKC_API.G_MISS_NUM)
2919 THEN
2920 x_pat_rec.pricing_group_sequence := l_pat_rec.pricing_group_sequence;
2921 END IF;
2922 IF (x_pat_rec.accrual_flag = OKC_API.G_MISS_CHAR)
2923 THEN
2924 x_pat_rec.accrual_flag := l_pat_rec.accrual_flag;
2925 END IF;
2926 IF (x_pat_rec.list_line_no = OKC_API.G_MISS_CHAR)
2927 THEN
2928 x_pat_rec.list_line_no := l_pat_rec.list_line_no;
2929 END IF;
2930 IF (x_pat_rec.source_system_code = OKC_API.G_MISS_CHAR)
2931 THEN
2932 x_pat_rec.source_system_code := l_pat_rec.source_system_code;
2933 END IF;
2934 IF (x_pat_rec.benefit_qty = OKC_API.G_MISS_NUM)
2935 THEN
2936 x_pat_rec.benefit_qty := l_pat_rec.benefit_qty;
2937 END IF;
2938 IF (x_pat_rec.benefit_uom_code = OKC_API.G_MISS_CHAR)
2939 THEN
2940 x_pat_rec.benefit_uom_code := l_pat_rec.benefit_uom_code;
2941 END IF;
2942 IF (x_pat_rec.expiration_date = OKC_API.G_MISS_DATE)
2943 THEN
2944 x_pat_rec.expiration_date := l_pat_rec.expiration_date;
2945 END IF;
2946 IF (x_pat_rec.modifier_level_code = OKC_API.G_MISS_CHAR)
2947 THEN
2948 x_pat_rec.modifier_level_code := l_pat_rec.modifier_level_code;
2949 END IF;
2950 IF (x_pat_rec.price_break_type_code = OKC_API.G_MISS_CHAR)
2951 THEN
2952 x_pat_rec.price_break_type_code := l_pat_rec.price_break_type_code;
2953 END IF;
2954 IF (x_pat_rec.substitution_attribute = OKC_API.G_MISS_CHAR)
2955 THEN
2956 x_pat_rec.substitution_attribute := l_pat_rec.substitution_attribute;
2957 END IF;
2958 IF (x_pat_rec.proration_type_code = OKC_API.G_MISS_CHAR)
2959 THEN
2960 x_pat_rec.proration_type_code := l_pat_rec.proration_type_code;
2961 END IF;
2962 IF (x_pat_rec.include_on_returns_flag = OKC_API.G_MISS_CHAR)
2963 THEN
2964 x_pat_rec.include_on_returns_flag := l_pat_rec.include_on_returns_flag;
2965 END IF;
2966 IF (x_pat_rec.object_version_number = OKC_API.G_MISS_NUM)
2967 THEN
2968 x_pat_rec.object_version_number := l_pat_rec.object_version_number;
2969 END IF;
2970
2971 IF (x_pat_rec.attribute1 = OKC_API.G_MISS_CHAR)
2972 THEN
2973 x_pat_rec.attribute1 := l_pat_rec.attribute1;
2974 END IF;
2975 IF (x_pat_rec.attribute2 = OKC_API.G_MISS_CHAR)
2976 THEN
2977 x_pat_rec.attribute2 := l_pat_rec.attribute2;
2978 END IF;
2979 IF (x_pat_rec.attribute3 = OKC_API.G_MISS_CHAR)
2980 THEN
2981 x_pat_rec.attribute3 := l_pat_rec.attribute3;
2982 END IF;
2983 IF (x_pat_rec.attribute4 = OKC_API.G_MISS_CHAR)
2984 THEN
2985 x_pat_rec.attribute4 := l_pat_rec.attribute4;
2986 END IF;
2987 IF (x_pat_rec.attribute5 = OKC_API.G_MISS_CHAR)
2988 THEN
2989 x_pat_rec.attribute5 := l_pat_rec.attribute5;
2990 END IF;
2991 IF (x_pat_rec.attribute6 = OKC_API.G_MISS_CHAR)
2992 THEN
2993 x_pat_rec.attribute6 := l_pat_rec.attribute6;
2994 END IF;
2995 IF (x_pat_rec.attribute7 = OKC_API.G_MISS_CHAR)
2996 THEN
2997 x_pat_rec.attribute7 := l_pat_rec.attribute7;
2998 END IF;
2999 IF (x_pat_rec.attribute8 = OKC_API.G_MISS_CHAR)
3000 THEN
3001 x_pat_rec.attribute8 := l_pat_rec.attribute8;
3002 END IF;
3003 IF (x_pat_rec.attribute9 = OKC_API.G_MISS_CHAR)
3004 THEN
3005 x_pat_rec.attribute9 := l_pat_rec.attribute9;
3006 END IF;
3007 IF (x_pat_rec.attribute10 = OKC_API.G_MISS_CHAR)
3008 THEN
3009 x_pat_rec.attribute10 := l_pat_rec.attribute10;
3010 END IF;
3011 IF (x_pat_rec.attribute11 = OKC_API.G_MISS_CHAR)
3012 THEN
3013 x_pat_rec.attribute11 := l_pat_rec.attribute11;
3014 END IF;
3015 IF (x_pat_rec.attribute12 = OKC_API.G_MISS_CHAR)
3016 THEN
3017 x_pat_rec.attribute12 := l_pat_rec.attribute12;
3018 END IF;
3019 IF (x_pat_rec.attribute13 = OKC_API.G_MISS_CHAR)
3020 THEN
3021 x_pat_rec.attribute13 := l_pat_rec.attribute13;
3022 END IF;
3023 IF (x_pat_rec.attribute14 = OKC_API.G_MISS_CHAR)
3024 THEN
3025 x_pat_rec.attribute14 := l_pat_rec.attribute14;
3026 END IF;
3027 IF (x_pat_rec.attribute15 = OKC_API.G_MISS_CHAR)
3028 THEN
3029 x_pat_rec.attribute15 := l_pat_rec.attribute15;
3030 END IF;
3031 IF (x_pat_rec.last_update_login = OKC_API.G_MISS_NUM)
3032 THEN
3033 x_pat_rec.last_update_login := l_pat_rec.last_update_login;
3034 END IF;
3035 IF (x_pat_rec.rebate_transaction_type_code = OKC_API.G_MISS_CHAR)
3036 THEN
3037 x_pat_rec.rebate_transaction_type_code := l_pat_rec.rebate_transaction_type_code;
3038 END IF;
3039
3040 IF (l_debug = 'Y') THEN
3041 okc_debug.log('9850: Leaving populate_new_record ', 2);
3042 okc_debug.Reset_Indentation;
3043 END IF;
3044
3045 RETURN(l_return_status);
3046
3047 END populate_new_record;
3048 ----------------------------------------------
3049 -- Set_Attributes for:OKC_PRICE_ADJUSTMENTS --
3050 ----------------------------------------------
3051 FUNCTION Set_Attributes (
3052 p_pat_rec IN pat_rec_type,
3053 x_pat_rec OUT NOCOPY pat_rec_type
3054 ) RETURN VARCHAR2 IS
3055 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3056 BEGIN
3057
3058 x_pat_rec := p_pat_rec;
3059 RETURN(l_return_status);
3060
3061 END Set_Attributes;
3062 BEGIN
3063
3064 IF (l_debug = 'Y') THEN
3065 okc_debug.Set_Indentation('OKC_PAT_PVT');
3066 okc_debug.log('10000: Entered update_row', 2);
3067 END IF;
3068
3069 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
3070 p_init_msg_list,
3071 '_PVT',
3072 x_return_status);
3073 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3074 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3075 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3076 RAISE OKC_API.G_EXCEPTION_ERROR;
3077 END IF;
3078 --- Setting item attributes
3079 l_return_status := Set_Attributes(
3080 p_pat_rec, -- IN
3081 l_pat_rec); -- OUT
3082 --- If any errors happen abort API
3083 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3084 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3085 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3086 RAISE OKC_API.G_EXCEPTION_ERROR;
3087 END IF;
3088 l_return_status := populate_new_record(l_pat_rec, l_def_pat_rec);
3089 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3090 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3091 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3092 RAISE OKC_API.G_EXCEPTION_ERROR;
3093 END IF;
3094 UPDATE OKC_PRICE_ADJUSTMENTS
3095 SET --PAT_ID = l_def_pat_rec.pat_id,
3096 --CHR_ID = l_def_pat_rec.chr_id,
3097 --CLE_ID = l_def_pat_rec.cle_id,
3098 BSL_ID = l_def_pat_rec.bsl_id,
3099 BCL_ID = l_def_pat_rec.bcl_id,
3100 --CREATED_BY = l_def_pat_rec.created_by,
3101 --CREATION_DATE = l_def_pat_rec.creation_date,
3102 LAST_UPDATED_BY = l_def_pat_rec.last_updated_by,
3103 LAST_UPDATE_DATE = l_def_pat_rec.last_update_date,
3104 MODIFIED_FROM = l_def_pat_rec.modified_from,
3105 MODIFIED_TO = l_def_pat_rec.modified_to,
3106 MODIFIER_MECHANISM_TYPE_CODE = l_def_pat_rec.modifier_mechanism_type_code,
3107 OPERAND = l_def_pat_rec.operand,
3108 ARITHMETIC_OPERATOR = l_def_pat_rec.arithmetic_operator,
3109 AUTOMATIC_FLAG = l_def_pat_rec.automatic_flag,
3110 UPDATE_ALLOWED = l_def_pat_rec.update_allowed,
3111 UPDATED_FLAG = l_def_pat_rec.updated_flag,
3112 APPLIED_FLAG = l_def_pat_rec.applied_flag,
3113 ON_INVOICE_FLAG = l_def_pat_rec.on_invoice_flag,
3114 PRICING_PHASE_ID = l_def_pat_rec.pricing_phase_id,
3115 CONTEXT = l_def_pat_rec.context,
3116 REQUEST_ID = NVL(decode(FND_GLOBAL.CONC_REQUEST_ID,-1,NULL,FND_GLOBAL.CONC_REQUEST_ID),l_def_pat_rec.request_id),
3117 PROGRAM_APPLICATION_ID = NVL(decode(FND_GLOBAL.PROG_APPL_ID,-1,NULL,FND_GLOBAL.PROG_APPL_ID),l_def_pat_rec.program_application_id),
3118 PROGRAM_ID = NVL(decode(FND_GLOBAL.CONC_PROGRAM_ID,-1,NULL,FND_GLOBAL.CONC_PROGRAM_ID),l_def_pat_rec.program_id),
3119 PROGRAM_UPDATE_DATE = decode(decode(FND_GLOBAL.CONC_REQUEST_ID,-1,NULL,SYSDATE),NULL,l_def_pat_rec.program_update_date,SYSDATE),
3120
3121 LIST_HEADER_ID = l_def_pat_rec.list_header_id,
3122 LIST_LINE_ID = l_def_pat_rec.list_line_id,
3123 LIST_LINE_TYPE_CODE = l_def_pat_rec.list_line_type_code,
3124 CHANGE_REASON_CODE = l_def_pat_rec.change_reason_code,
3125 CHANGE_REASON_TEXT = l_def_pat_rec.change_reason_text,
3126 ESTIMATED_FLAG = l_def_pat_rec.estimated_flag,
3127 ADJUSTED_AMOUNT = l_def_pat_rec.adjusted_amount,
3128 CHARGE_TYPE_CODE = l_def_pat_rec.charge_type_code,
3129 CHARGE_SUBTYPE_CODE = l_def_pat_rec.charge_subtype_code,
3130 RANGE_BREAK_QUANTITY = l_def_pat_rec.range_break_quantity,
3131 ACCRUAL_CONVERSION_RATE = l_def_pat_rec.accrual_conversion_rate,
3132 PRICING_GROUP_SEQUENCE = l_def_pat_rec.pricing_group_sequence,
3133 ACCRUAL_FLAG = l_def_pat_rec.accrual_flag,
3134 LIST_LINE_NO = l_def_pat_rec.list_line_no,
3135 SOURCE_SYSTEM_CODE = l_def_pat_rec.source_system_code,
3136 BENEFIT_QTY = l_def_pat_rec.benefit_qty,
3137 BENEFIT_UOM_CODE = l_def_pat_rec.benefit_uom_code,
3138 EXPIRATION_DATE = l_def_pat_rec.expiration_date,
3139 MODIFIER_LEVEL_CODE = l_def_pat_rec.modifier_level_code,
3140 PRICE_BREAK_TYPE_CODE = l_def_pat_rec.price_break_type_code,
3141 SUBSTITUTION_ATTRIBUTE = l_def_pat_rec.substitution_attribute,
3142 PRORATION_TYPE_CODE = l_def_pat_rec.proration_type_code,
3143 INCLUDE_ON_RETURNS_FLAG = l_def_pat_rec.include_on_returns_flag,
3144 OBJECT_VERSION_NUMBER = l_def_pat_rec.object_version_number,
3145 ATTRIBUTE1 = l_def_pat_rec.attribute1,
3146 ATTRIBUTE2 = l_def_pat_rec.attribute2,
3147 ATTRIBUTE3 = l_def_pat_rec.attribute3,
3148 ATTRIBUTE4 = l_def_pat_rec.attribute4,
3149 ATTRIBUTE5 = l_def_pat_rec.attribute5,
3150 ATTRIBUTE6 = l_def_pat_rec.attribute6,
3151 ATTRIBUTE7 = l_def_pat_rec.attribute7,
3152 ATTRIBUTE8 = l_def_pat_rec.attribute8,
3153 ATTRIBUTE9 = l_def_pat_rec.attribute9,
3154 ATTRIBUTE10 = l_def_pat_rec.attribute10,
3155 ATTRIBUTE11 = l_def_pat_rec.attribute11,
3156 ATTRIBUTE12 = l_def_pat_rec.attribute12,
3157 ATTRIBUTE13 = l_def_pat_rec.attribute13,
3158 ATTRIBUTE14 = l_def_pat_rec.attribute14,
3159 ATTRIBUTE15 = l_def_pat_rec.attribute15,
3160 LAST_UPDATE_LOGIN = l_def_pat_rec.last_update_login,
3161 REBATE_TRANSACTION_TYPE_CODE = l_def_pat_rec.rebate_transaction_type_code
3162 WHERE ID = l_def_pat_rec.id;
3163
3164 x_pat_rec := l_def_pat_rec;
3165 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
3166
3167 IF (l_debug = 'Y') THEN
3168 okc_debug.log('10100: Exiting update_row', 2);
3169 okc_debug.Reset_Indentation;
3170 END IF;
3171
3172 EXCEPTION
3173 WHEN OKC_API.G_EXCEPTION_ERROR THEN
3174
3175 IF (l_debug = 'Y') THEN
3176 okc_debug.log('10200: Exiting update_row:OKC_API.G_EXCEPTION_ERROR Exception', 2);
3177 okc_debug.Reset_Indentation;
3178 END IF;
3179
3180 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3181 (
3182 l_api_name,
3183 G_PKG_NAME,
3184 'OKC_API.G_RET_STS_ERROR',
3185 x_msg_count,
3186 x_msg_data,
3187 '_PVT'
3188 );
3189 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3190
3191 IF (l_debug = 'Y') THEN
3192 okc_debug.log('10300: Exiting update_row:OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception', 2);
3193 okc_debug.Reset_Indentation;
3194 END IF;
3195
3196 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3197 (
3198 l_api_name,
3199 G_PKG_NAME,
3200 'OKC_API.G_RET_STS_UNEXP_ERROR',
3201 x_msg_count,
3202 x_msg_data,
3203 '_PVT'
3204 );
3205 WHEN OTHERS THEN
3206
3207 IF (l_debug = 'Y') THEN
3208 okc_debug.log('10400: Exiting update_row:OTHERS Exception', 2);
3209 okc_debug.Reset_Indentation;
3210 END IF;
3211
3212 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3213 (
3214 l_api_name,
3215 G_PKG_NAME,
3216 'OTHERS',
3217 x_msg_count,
3218 x_msg_data,
3219 '_PVT'
3220 );
3221
3222 END update_row;
3223 --------------------------------------------
3224 -- update_row for:OKC_PRICE_ADJUSTMENTS_V --
3225 --------------------------------------------
3226 PROCEDURE update_row(
3227 p_api_version IN NUMBER,
3228 p_init_msg_list IN VARCHAR2 ,
3229 x_return_status OUT NOCOPY VARCHAR2,
3230 x_msg_count OUT NOCOPY NUMBER,
3231 x_msg_data OUT NOCOPY VARCHAR2,
3232 p_patv_rec IN patv_rec_type,
3233 x_patv_rec OUT NOCOPY patv_rec_type) IS
3234
3235 l_api_version CONSTANT NUMBER := 1;
3236 l_api_name CONSTANT VARCHAR2(30) := 'V_update_row';
3237 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3238 l_patv_rec patv_rec_type := p_patv_rec;
3239 l_def_patv_rec patv_rec_type;
3240 l_pat_rec pat_rec_type;
3241 lx_pat_rec pat_rec_type;
3242 -------------------------------
3243 -- FUNCTION fill_who_columns --
3244 -------------------------------
3245 FUNCTION fill_who_columns (
3246 p_patv_rec IN patv_rec_type
3247 ) RETURN patv_rec_type IS
3248 l_patv_rec patv_rec_type := p_patv_rec;
3249 BEGIN
3250
3251 l_patv_rec.LAST_UPDATE_DATE := SYSDATE;
3252 l_patv_rec.LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
3253 l_patv_rec.LAST_UPDATE_LOGIN := FND_GLOBAL.LOGIN_ID;
3254 RETURN(l_patv_rec);
3255
3256 END fill_who_columns;
3257 ----------------------------------
3258 -- FUNCTION populate_new_record --
3259 ----------------------------------
3260 FUNCTION populate_new_record (
3261 p_patv_rec IN patv_rec_type,
3262 x_patv_rec OUT NOCOPY patv_rec_type
3263 ) RETURN VARCHAR2 IS
3264 l_patv_rec patv_rec_type;
3265 l_row_notfound BOOLEAN := TRUE;
3266 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3267 BEGIN
3268
3269 IF (l_debug = 'Y') THEN
3270 okc_debug.Set_Indentation('OKC_PAT_PVT');
3271 okc_debug.log('10600: Entered populate_new_record', 2);
3272 END IF;
3273
3274 x_patv_rec := p_patv_rec;
3275 -- Get current database values
3276 l_patv_rec := get_rec(p_patv_rec, l_row_notfound);
3277 IF (l_row_notfound) THEN
3278 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
3279 END IF;
3280 IF (x_patv_rec.id = OKC_API.G_MISS_NUM)
3281 THEN
3282 x_patv_rec.id := l_patv_rec.id;
3283 END IF;
3284 IF (x_patv_rec.pat_id = OKC_API.G_MISS_NUM)
3285 THEN
3286 x_patv_rec.pat_id := l_patv_rec.pat_id;
3287 END IF;
3288 IF (x_patv_rec.chr_id = OKC_API.G_MISS_NUM)
3289 THEN
3290 x_patv_rec.chr_id := l_patv_rec.chr_id;
3291 END IF;
3292 IF (x_patv_rec.cle_id = OKC_API.G_MISS_NUM)
3293 THEN
3294 x_patv_rec.cle_id := l_patv_rec.cle_id;
3295 END IF;
3296 IF (x_patv_rec.bsl_id = OKC_API.G_MISS_NUM)
3297 THEN
3298 x_patv_rec.bsl_id := l_patv_rec.bsl_id;
3299 END IF;
3300 IF (x_patv_rec.bcl_id = OKC_API.G_MISS_NUM)
3301 THEN
3302 x_patv_rec.bcl_id := l_patv_rec.bcl_id;
3303 END IF;
3304 IF (x_patv_rec.modified_from = OKC_API.G_MISS_NUM)
3305 THEN
3306 x_patv_rec.modified_from := l_patv_rec.modified_from;
3307 END IF;
3308 IF (x_patv_rec.modified_to = OKC_API.G_MISS_NUM)
3309 THEN
3310 x_patv_rec.modified_to := l_patv_rec.modified_to;
3311 END IF;
3312 IF (x_patv_rec.modifier_mechanism_type_code = OKC_API.G_MISS_CHAR)
3313 THEN
3314 x_patv_rec.modifier_mechanism_type_code := l_patv_rec.modifier_mechanism_type_code;
3315 END IF;
3316 IF (x_patv_rec.operand = OKC_API.G_MISS_NUM)
3317 THEN
3318 x_patv_rec.operand := l_patv_rec.operand;
3319 END IF;
3320 IF (x_patv_rec.arithmetic_operator = OKC_API.G_MISS_CHAR)
3321 THEN
3322 x_patv_rec.arithmetic_operator := l_patv_rec.arithmetic_operator;
3323 END IF;
3324 IF (x_patv_rec.automatic_flag = OKC_API.G_MISS_CHAR)
3325 THEN
3326 x_patv_rec.automatic_flag := l_patv_rec.automatic_flag;
3327 END IF;
3328 IF (x_patv_rec.update_allowed = OKC_API.G_MISS_CHAR)
3329 THEN
3330 x_patv_rec.update_allowed := l_patv_rec.update_allowed;
3331 END IF;
3332 IF (x_patv_rec.updated_flag = OKC_API.G_MISS_CHAR)
3333 THEN
3334 x_patv_rec.updated_flag := l_patv_rec.updated_flag;
3335 END IF;
3336 IF (x_patv_rec.applied_flag = OKC_API.G_MISS_CHAR)
3337 THEN
3338 x_patv_rec.applied_flag := l_patv_rec.applied_flag;
3339 END IF;
3340 IF (x_patv_rec.on_invoice_flag = OKC_API.G_MISS_CHAR)
3341 THEN
3342 x_patv_rec.on_invoice_flag := l_patv_rec.on_invoice_flag;
3343 END IF;
3344 IF (x_patv_rec.pricing_phase_id = OKC_API.G_MISS_NUM)
3345 THEN
3346 x_patv_rec.pricing_phase_id := l_patv_rec.pricing_phase_id;
3347 END IF;
3348 IF (x_patv_rec.context = OKC_API.G_MISS_CHAR)
3349 THEN
3350 x_patv_rec.context := l_patv_rec.context;
3351 END IF;
3352
3353 IF (x_patv_rec.program_application_id = OKC_API.G_MISS_NUM)
3354 THEN
3355 x_patv_rec.program_application_id := l_patv_rec.program_application_id;
3356 END IF;
3357 IF (x_patv_rec.program_id = OKC_API.G_MISS_NUM)
3358 THEN
3359 x_patv_rec.program_id := l_patv_rec.program_id;
3360 END IF;
3361 IF (x_patv_rec.program_update_date = OKC_API.G_MISS_DATE)
3362 THEN
3363 x_patv_rec.program_update_date := l_patv_rec.program_update_date;
3364 END IF;
3365 IF (x_patv_rec.request_id = OKC_API.G_MISS_NUM)
3366 THEN
3367 x_patv_rec.request_id := l_patv_rec.request_id;
3368 END IF;
3369 IF (x_patv_rec.list_header_id = OKC_API.G_MISS_NUM)
3370 THEN
3371 x_patv_rec.list_header_id := l_patv_rec.list_header_id;
3372 END IF;
3373 IF (x_patv_rec.list_line_id = OKC_API.G_MISS_NUM)
3374 THEN
3375 x_patv_rec.list_line_id := l_patv_rec.list_line_id;
3376 END IF;
3377 IF (x_patv_rec.list_line_type_code = OKC_API.G_MISS_CHAR)
3378 THEN
3379 x_patv_rec.list_line_type_code := l_patv_rec.list_line_type_code;
3380 END IF;
3381 IF (x_patv_rec.change_reason_code = OKC_API.G_MISS_CHAR)
3382 THEN
3383 x_patv_rec.change_reason_code := l_patv_rec.change_reason_code;
3384 END IF;
3385 IF (x_patv_rec.change_reason_text = OKC_API.G_MISS_CHAR)
3386 THEN
3387 x_patv_rec.change_reason_text := l_patv_rec.change_reason_text;
3388 END IF;
3389 IF (x_patv_rec.estimated_flag = OKC_API.G_MISS_CHAR)
3390 THEN
3391 x_patv_rec.estimated_flag := l_patv_rec.estimated_flag;
3392 END IF;
3393 IF (x_patv_rec.adjusted_amount = OKC_API.G_MISS_NUM)
3394 THEN
3395 x_patv_rec.adjusted_amount := l_patv_rec.adjusted_amount;
3396 END IF;
3397 IF (x_patv_rec.charge_type_code = OKC_API.G_MISS_CHAR)
3398 THEN
3399 x_patv_rec.charge_type_code := l_patv_rec.charge_type_code ;
3400 END IF;
3401 IF (x_patv_rec.charge_subtype_code = OKC_API.G_MISS_CHAR)
3402 THEN
3403 x_patv_rec.charge_subtype_code := l_patv_rec.charge_subtype_code;
3404 END IF;
3405 IF (x_patv_rec.range_break_quantity = OKC_API.G_MISS_NUM)
3406 THEN
3407 x_patv_rec.range_break_quantity := l_patv_rec.range_break_quantity;
3408 END IF;
3409 IF (x_patv_rec.accrual_conversion_rate = OKC_API.G_MISS_NUM)
3410 THEN
3411 x_patv_rec.accrual_conversion_rate := l_patv_rec.accrual_conversion_rate;
3412 END IF;
3413 IF (x_patv_rec.pricing_group_sequence = OKC_API.G_MISS_NUM)
3414 THEN
3415 x_patv_rec.pricing_group_sequence := l_patv_rec.pricing_group_sequence;
3416 END IF;
3417 IF (x_patv_rec.accrual_flag = OKC_API.G_MISS_CHAR)
3418 THEN
3419 x_patv_rec.accrual_flag := l_patv_rec.accrual_flag;
3420 END IF;
3421 IF (x_patv_rec.list_line_no = OKC_API.G_MISS_CHAR)
3422 THEN
3423 x_patv_rec.list_line_no := l_patv_rec.list_line_no;
3424 END IF;
3425 IF (x_patv_rec.source_system_code = OKC_API.G_MISS_CHAR)
3426 THEN
3427 x_patv_rec.source_system_code := l_patv_rec.source_system_code;
3428 END IF;
3429 IF (x_patv_rec.benefit_qty = OKC_API.G_MISS_NUM)
3430 THEN
3431 x_patv_rec.benefit_qty := l_patv_rec.benefit_qty;
3432 END IF;
3433 IF (x_patv_rec.benefit_uom_code = OKC_API.G_MISS_CHAR)
3434 THEN
3435 x_patv_rec.benefit_uom_code := l_patv_rec.benefit_uom_code;
3436 END IF;
3437
3438 IF (x_patv_rec.expiration_date = OKC_API.G_MISS_DATE)
3439 THEN
3440 x_patv_rec.expiration_date := l_patv_rec.expiration_date;
3441 END IF;
3442 IF (x_patv_rec.modifier_level_code = OKC_API.G_MISS_CHAR)
3443 THEN
3444 x_patv_rec.modifier_level_code := l_patv_rec.modifier_level_code;
3445 END IF;
3446 IF (x_patv_rec.price_break_type_code = OKC_API.G_MISS_CHAR)
3447 THEN
3448 x_patv_rec.price_break_type_code := l_patv_rec.price_break_type_code;
3449 END IF;
3450 IF (x_patv_rec.substitution_attribute = OKC_API.G_MISS_CHAR)
3451 THEN
3452 x_patv_rec.substitution_attribute := l_patv_rec.substitution_attribute;
3453 END IF;
3454 IF (x_patv_rec.proration_type_code = OKC_API.G_MISS_CHAR)
3455 THEN
3456 x_patv_rec.proration_type_code := l_patv_rec.proration_type_code;
3457 END IF;
3458 IF (x_patv_rec.include_on_returns_flag = OKC_API.G_MISS_CHAR)
3459 THEN
3460 x_patv_rec.include_on_returns_flag := l_patv_rec.include_on_returns_flag;
3461 END IF;
3462 IF (x_patv_rec.object_version_number = OKC_API.G_MISS_NUM)
3463 THEN
3464 x_patv_rec.object_version_number := l_patv_rec.object_version_number;
3465 END IF;
3466
3467 IF (x_patv_rec.attribute1 = OKC_API.G_MISS_CHAR)
3468 THEN
3469 x_patv_rec.attribute1 := l_patv_rec.attribute1;
3470 END IF;
3471 IF (x_patv_rec.attribute2 = OKC_API.G_MISS_CHAR)
3472 THEN
3473 x_patv_rec.attribute2 := l_patv_rec.attribute2;
3474 END IF;
3475 IF (x_patv_rec.attribute3 = OKC_API.G_MISS_CHAR)
3476 THEN
3477 x_patv_rec.attribute3 := l_patv_rec.attribute3;
3478 END IF;
3479 IF (x_patv_rec.attribute4 = OKC_API.G_MISS_CHAR)
3480 THEN
3481 x_patv_rec.attribute4 := l_patv_rec.attribute4;
3482 END IF;
3483 IF (x_patv_rec.attribute5 = OKC_API.G_MISS_CHAR)
3484 THEN
3485 x_patv_rec.attribute5 := l_patv_rec.attribute5;
3486 END IF;
3487 IF (x_patv_rec.attribute6 = OKC_API.G_MISS_CHAR)
3488 THEN
3489 x_patv_rec.attribute6 := l_patv_rec.attribute6;
3490 END IF;
3491 IF (x_patv_rec.attribute7 = OKC_API.G_MISS_CHAR)
3492 THEN
3493 x_patv_rec.attribute7 := l_patv_rec.attribute7;
3494 END IF;
3495 IF (x_patv_rec.attribute8 = OKC_API.G_MISS_CHAR)
3496 THEN
3497 x_patv_rec.attribute8 := l_patv_rec.attribute8;
3498 END IF;
3499 IF (x_patv_rec.attribute9 = OKC_API.G_MISS_CHAR)
3500 THEN
3501 x_patv_rec.attribute9 := l_patv_rec.attribute9;
3502 END IF;
3503 IF (x_patv_rec.attribute10 = OKC_API.G_MISS_CHAR)
3504 THEN
3505 x_patv_rec.attribute10 := l_patv_rec.attribute10;
3506 END IF;
3507 IF (x_patv_rec.attribute11 = OKC_API.G_MISS_CHAR)
3508 THEN
3509 x_patv_rec.attribute11 := l_patv_rec.attribute11;
3510 END IF;
3511 IF (x_patv_rec.attribute12 = OKC_API.G_MISS_CHAR)
3512 THEN
3513 x_patv_rec.attribute12 := l_patv_rec.attribute12;
3514 END IF;
3515 IF (x_patv_rec.attribute13 = OKC_API.G_MISS_CHAR)
3516 THEN
3517 x_patv_rec.attribute13 := l_patv_rec.attribute13;
3518 END IF;
3519 IF (x_patv_rec.attribute14 = OKC_API.G_MISS_CHAR)
3520 THEN
3521 x_patv_rec.attribute14 := l_patv_rec.attribute14;
3522 END IF;
3523 IF (x_patv_rec.attribute15 = OKC_API.G_MISS_CHAR)
3524 THEN
3525 x_patv_rec.attribute15 := l_patv_rec.attribute15;
3526 END IF;
3527 IF (x_patv_rec.created_by = OKC_API.G_MISS_NUM)
3528 THEN
3529 x_patv_rec.created_by := l_patv_rec.created_by;
3530 END IF;
3531 IF (x_patv_rec.creation_date = OKC_API.G_MISS_DATE)
3532 THEN
3533 x_patv_rec.creation_date := l_patv_rec.creation_date;
3534 END IF;
3535 IF (x_patv_rec.last_updated_by = OKC_API.G_MISS_NUM)
3536 THEN
3537 x_patv_rec.last_updated_by := l_patv_rec.last_updated_by;
3538 END IF;
3539 IF (x_patv_rec.last_update_date = OKC_API.G_MISS_DATE)
3540 THEN
3541 x_patv_rec.last_update_date := l_patv_rec.last_update_date;
3542 END IF;
3543 IF (x_patv_rec.last_update_login = OKC_API.G_MISS_NUM)
3544 THEN
3545 x_patv_rec.last_update_login := l_patv_rec.last_update_login;
3546 END IF;
3547 IF (x_patv_rec.rebate_transaction_type_code = OKC_API.G_MISS_CHAR)
3548 THEN
3549 x_patv_rec.rebate_transaction_type_code := l_patv_rec.rebate_transaction_type_code;
3550 END IF;
3551
3552 IF (l_debug = 'Y') THEN
3553 okc_debug.log('10650: Exiting update_row', 2);
3554 okc_debug.Reset_Indentation;
3555 END IF;
3556
3557 RETURN(l_return_status);
3558
3559 END populate_new_record;
3560 ------------------------------------------------
3561 -- Set_Attributes for:OKC_PRICE_ADJUSTMENTS_V --
3562 ------------------------------------------------
3563 FUNCTION Set_Attributes (
3564 p_patv_rec IN patv_rec_type,
3565 x_patv_rec OUT NOCOPY patv_rec_type
3566 ) RETURN VARCHAR2 IS
3567 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3568 BEGIN
3569
3570 x_patv_rec := p_patv_rec;
3571 x_patv_rec.OBJECT_VERSION_NUMBER := NVL(x_patv_rec.OBJECT_VERSION_NUMBER,0)+1;
3572 RETURN(l_return_status);
3573
3574 END Set_Attributes;
3575 BEGIN
3576
3577 IF (l_debug = 'Y') THEN
3578 okc_debug.Set_Indentation('OKC_PAT_PVT');
3579 okc_debug.log('10800: Entered update_row', 2);
3580 END IF;
3581
3582 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
3583 G_PKG_NAME,
3584 p_init_msg_list,
3585 l_api_version,
3586 p_api_version,
3587 '_PVT',
3588 x_return_status);
3589 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3590 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3591 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3592 RAISE OKC_API.G_EXCEPTION_ERROR;
3593 END IF;
3594 --- Setting item attributes
3595 l_return_status := Set_Attributes(
3596 p_patv_rec, -- IN
3597 l_patv_rec); -- OUT
3598 --- If any errors happen abort API
3599 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3600 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3601 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3602 RAISE OKC_API.G_EXCEPTION_ERROR;
3603 END IF;
3604 l_return_status := populate_new_record(l_patv_rec, l_def_patv_rec);
3605 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3606 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3607 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3608 RAISE OKC_API.G_EXCEPTION_ERROR;
3609 END IF;
3610 l_def_patv_rec := fill_who_columns(l_def_patv_rec);
3611 --- Validate all non-missing attributes (Item Level Validation)
3612 l_return_status := Validate_Attributes(l_def_patv_rec);
3613 --- If any errors happen abort API
3614 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3615 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3616 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3617 RAISE OKC_API.G_EXCEPTION_ERROR;
3618 END IF;
3619 l_return_status := Validate_Record(l_def_patv_rec);
3620 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3621 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3622 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3623 RAISE OKC_API.G_EXCEPTION_ERROR;
3624 END IF;
3625
3626 --------------------------------------
3627 -- Move VIEW record to "Child" records
3628 --------------------------------------
3629 migrate(l_def_patv_rec, l_pat_rec);
3630 --------------------------------------------
3631 -- Call the UPDATE_ROW for each child record
3632 --------------------------------------------
3633 update_row(
3634 p_init_msg_list,
3635 x_return_status,
3636 x_msg_count,
3637 x_msg_data,
3638 l_pat_rec,
3639 lx_pat_rec
3640 );
3641 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3642 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3643 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
3644 RAISE OKC_API.G_EXCEPTION_ERROR;
3645 END IF;
3646 migrate(lx_pat_rec, l_def_patv_rec);
3647 x_patv_rec := l_def_patv_rec;
3648 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
3649
3650 IF (l_debug = 'Y') THEN
3651 okc_debug.log('10900: Exiting update_row', 2);
3652 okc_debug.Reset_Indentation;
3653 END IF;
3654
3655 EXCEPTION
3656 WHEN OKC_API.G_EXCEPTION_ERROR THEN
3657
3658 IF (l_debug = 'Y') THEN
3659 okc_debug.log('11000: Exiting update_row:OKC_API.G_EXCEPTION_ERROR Exception', 2);
3660 okc_debug.Reset_Indentation;
3661 END IF;
3662
3663 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3664 (
3665 l_api_name,
3666 G_PKG_NAME,
3667 'OKC_API.G_RET_STS_ERROR',
3668 x_msg_count,
3669 x_msg_data,
3670 '_PVT'
3671 );
3672 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3673
3674 IF (l_debug = 'Y') THEN
3675 okc_debug.log('11100: Exiting update_row:OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception', 2);
3676 okc_debug.Reset_Indentation;
3677 END IF;
3678
3679 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3680 (
3681 l_api_name,
3682 G_PKG_NAME,
3683 'OKC_API.G_RET_STS_UNEXP_ERROR',
3684 x_msg_count,
3685 x_msg_data,
3686 '_PVT'
3687 );
3688 WHEN OTHERS THEN
3689
3690 IF (l_debug = 'Y') THEN
3691 okc_debug.log('11200: Exiting update_row:OTHERS Exception', 2);
3692 okc_debug.Reset_Indentation;
3693 END IF;
3694
3695 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3696 (
3697 l_api_name,
3698 G_PKG_NAME,
3699 'OTHERS',
3700 x_msg_count,
3701 x_msg_data,
3702 '_PVT'
3703 );
3704
3705 END update_row;
3706 ----------------------------------------
3707 -- PL/SQL TBL update_row for:PATV_TBL --
3708 ----------------------------------------
3709 PROCEDURE update_row(
3710 p_api_version IN NUMBER,
3711 p_init_msg_list IN VARCHAR2 ,
3712 x_return_status OUT NOCOPY VARCHAR2,
3713 x_msg_count OUT NOCOPY NUMBER,
3714 x_msg_data OUT NOCOPY VARCHAR2,
3715 p_patv_tbl IN patv_tbl_type,
3716 x_patv_tbl OUT NOCOPY patv_tbl_type) IS
3717
3718 l_api_version CONSTANT NUMBER := 1;
3719 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_update_row';
3720 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3721 i NUMBER := 0;
3722 BEGIN
3723
3724 IF (l_debug = 'Y') THEN
3725 okc_debug.Set_Indentation('OKC_PAT_PVT');
3726 okc_debug.log('11300: Entered update_row', 2);
3727 END IF;
3728
3729 OKC_API.init_msg_list(p_init_msg_list);
3730 -- Make sure PL/SQL table has records in it before passing
3731 IF (p_patv_tbl.COUNT > 0) THEN
3732 i := p_patv_tbl.FIRST;
3733 LOOP
3734 update_row (
3735 p_api_version => p_api_version,
3736 p_init_msg_list => OKC_API.G_FALSE,
3737 x_return_status => x_return_status,
3738 x_msg_count => x_msg_count,
3739 x_msg_data => x_msg_data,
3740 p_patv_rec => p_patv_tbl(i),
3741 x_patv_rec => x_patv_tbl(i));
3742 EXIT WHEN (i = p_patv_tbl.LAST);
3743 i := p_patv_tbl.NEXT(i);
3744 END LOOP;
3745 END IF;
3746
3747 IF (l_debug = 'Y') THEN
3748 okc_debug.log('11400: Exiting update_row', 2);
3749 okc_debug.Reset_Indentation;
3750 END IF;
3751
3752 EXCEPTION
3753 WHEN OKC_API.G_EXCEPTION_ERROR THEN
3754
3755 IF (l_debug = 'Y') THEN
3756 okc_debug.log('11500: Exiting update_row:OKC_API.G_EXCEPTION_ERROR Exception', 2);
3757 okc_debug.Reset_Indentation;
3758 END IF;
3759
3760 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3761 (
3762 l_api_name,
3763 G_PKG_NAME,
3764 'OKC_API.G_RET_STS_ERROR',
3765 x_msg_count,
3766 x_msg_data,
3767 '_PVT'
3768 );
3769 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3770
3771 IF (l_debug = 'Y') THEN
3772 okc_debug.log('11600: Exiting update_row:OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception', 2);
3773 okc_debug.Reset_Indentation;
3774 END IF;
3775
3776 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3777 (
3778 l_api_name,
3779 G_PKG_NAME,
3780 'OKC_API.G_RET_STS_UNEXP_ERROR',
3781 x_msg_count,
3782 x_msg_data,
3783 '_PVT'
3784 );
3785 WHEN OTHERS THEN
3786
3787 IF (l_debug = 'Y') THEN
3788 okc_debug.log('11700: Exiting update_row:OTHERS Exception', 2);
3789 okc_debug.Reset_Indentation;
3790 END IF;
3791
3792 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3793 (
3794 l_api_name,
3795 G_PKG_NAME,
3796 'OTHERS',
3797 x_msg_count,
3798 x_msg_data,
3799 '_PVT'
3800 );
3801
3802 END update_row;
3803
3804 ---------------------------------------------------------------------------
3805 -- PROCEDURE delete_row
3806 ---------------------------------------------------------------------------
3807 ------------------------------------------
3808 -- delete_row for:OKC_PRICE_ADJUSTMENTS --
3809 ------------------------------------------
3810 PROCEDURE delete_row(
3811 p_init_msg_list IN VARCHAR2 ,
3812 x_return_status OUT NOCOPY VARCHAR2,
3813 x_msg_count OUT NOCOPY NUMBER,
3814 x_msg_data OUT NOCOPY VARCHAR2,
3815 p_pat_rec IN pat_rec_type) IS
3816
3817 l_api_version CONSTANT NUMBER := 1;
3818 l_api_name CONSTANT VARCHAR2(30) := 'ADJUSTMENTS_delete_row';
3819 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3820 l_pat_rec pat_rec_type:= p_pat_rec;
3821 l_row_notfound BOOLEAN := TRUE;
3822 BEGIN
3823
3824 IF (l_debug = 'Y') THEN
3825 okc_debug.Set_Indentation('OKC_PAT_PVT');
3826 okc_debug.log('11800: Entered delete_row', 2);
3827 END IF;
3828
3829 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
3830 p_init_msg_list,
3831 '_PVT',
3832 x_return_status);
3833 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3834 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3835 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3836 RAISE OKC_API.G_EXCEPTION_ERROR;
3837 END IF;
3838 DELETE FROM OKC_PRICE_ADJUSTMENTS
3839 WHERE ID = l_pat_rec.id;
3840
3841 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
3842
3843 IF (l_debug = 'Y') THEN
3844 okc_debug.log('11900: Exiting delete_row', 2);
3845 okc_debug.Reset_Indentation;
3846 END IF;
3847
3848 EXCEPTION
3849 WHEN OKC_API.G_EXCEPTION_ERROR THEN
3850
3851 IF (l_debug = 'Y') THEN
3852 okc_debug.log('12000: Exiting delete_row:OKC_API.G_EXCEPTION_ERROR Exception', 2);
3853 okc_debug.Reset_Indentation;
3854 END IF;
3855
3856 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3857 (
3858 l_api_name,
3859 G_PKG_NAME,
3860 'OKC_API.G_RET_STS_ERROR',
3861 x_msg_count,
3862 x_msg_data,
3863 '_PVT'
3864 );
3865 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3866
3867 IF (l_debug = 'Y') THEN
3868 okc_debug.log('12100: Exiting delete_row:OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception', 2);
3869 okc_debug.Reset_Indentation;
3870 END IF;
3871
3872 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3873 (
3874 l_api_name,
3875 G_PKG_NAME,
3876 'OKC_API.G_RET_STS_UNEXP_ERROR',
3877 x_msg_count,
3878 x_msg_data,
3879 '_PVT'
3880 );
3881 WHEN OTHERS THEN
3882
3883 IF (l_debug = 'Y') THEN
3884 okc_debug.log('12200: Exiting delete_row:OTHERS Exception', 2);
3885 okc_debug.Reset_Indentation;
3886 END IF;
3887
3888 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3889 (
3890 l_api_name,
3891 G_PKG_NAME,
3892 'OTHERS',
3893 x_msg_count,
3894 x_msg_data,
3895 '_PVT'
3896 );
3897
3898 END delete_row;
3899 --------------------------------------------
3900 -- delete_row for:OKC_PRICE_ADJUSTMENTS_V --
3901 --------------------------------------------
3902 PROCEDURE delete_row(
3903 p_api_version IN NUMBER,
3904 p_init_msg_list IN VARCHAR2 ,
3905 x_return_status OUT NOCOPY VARCHAR2,
3906 x_msg_count OUT NOCOPY NUMBER,
3907 x_msg_data OUT NOCOPY VARCHAR2,
3908 p_patv_rec IN patv_rec_type) IS
3909
3910 l_api_version CONSTANT NUMBER := 1;
3911 l_api_name CONSTANT VARCHAR2(30) := 'V_delete_row';
3912 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3913 l_patv_rec patv_rec_type := p_patv_rec;
3914 l_pat_rec pat_rec_type;
3915 BEGIN
3916
3917 IF (l_debug = 'Y') THEN
3918 okc_debug.Set_Indentation('OKC_PAT_PVT');
3919 okc_debug.log('12300: Entered delete_row', 2);
3920 END IF;
3921
3922 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
3923 G_PKG_NAME,
3924 p_init_msg_list,
3925 l_api_version,
3926 p_api_version,
3927 '_PVT',
3928 x_return_status);
3929 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3930 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3931 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3932 RAISE OKC_API.G_EXCEPTION_ERROR;
3933 END IF;
3934 --------------------------------------
3935 -- Move VIEW record to "Child" records
3936 --------------------------------------
3937 migrate(l_patv_rec, l_pat_rec);
3938 --------------------------------------------
3939 -- Call the DELETE_ROW for each child record
3940 --------------------------------------------
3941 delete_row(
3942 p_init_msg_list,
3943 x_return_status,
3944 x_msg_count,
3945 x_msg_data,
3946 l_pat_rec
3947 );
3948 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3949 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3950 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
3951 RAISE OKC_API.G_EXCEPTION_ERROR;
3952 END IF;
3953 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
3954
3955 IF (l_debug = 'Y') THEN
3956 okc_debug.log('12400: Exiting delete_row', 2);
3957 okc_debug.Reset_Indentation;
3958 END IF;
3959
3960 EXCEPTION
3961 WHEN OKC_API.G_EXCEPTION_ERROR THEN
3962
3963 IF (l_debug = 'Y') THEN
3964 okc_debug.log('12500: Exiting delete_row:OKC_API.G_EXCEPTION_ERROR Exception', 2);
3965 okc_debug.Reset_Indentation;
3966 END IF;
3967
3968 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3969 (
3970 l_api_name,
3971 G_PKG_NAME,
3972 'OKC_API.G_RET_STS_ERROR',
3973 x_msg_count,
3974 x_msg_data,
3975 '_PVT'
3976 );
3977 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3978
3979 IF (l_debug = 'Y') THEN
3980 okc_debug.log('12600: Exiting delete_row:OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception', 2);
3981 okc_debug.Reset_Indentation;
3982 END IF;
3983
3984 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3985 (
3986 l_api_name,
3987 G_PKG_NAME,
3988 'OKC_API.G_RET_STS_UNEXP_ERROR',
3989 x_msg_count,
3990 x_msg_data,
3991 '_PVT'
3992 );
3993 WHEN OTHERS THEN
3994
3995 IF (l_debug = 'Y') THEN
3996 okc_debug.log('12700: Exiting delete_row:OTHERS Exception', 2);
3997 okc_debug.Reset_Indentation;
3998 END IF;
3999
4000 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
4001 (
4002 l_api_name,
4003 G_PKG_NAME,
4004 'OTHERS',
4005 x_msg_count,
4006 x_msg_data,
4007 '_PVT'
4008 );
4009 END delete_row;
4010 ----------------------------------------
4011 -- PL/SQL TBL delete_row for:PATV_TBL --
4012 ----------------------------------------
4013 PROCEDURE delete_row(
4014 p_api_version IN NUMBER,
4015 p_init_msg_list IN VARCHAR2 ,
4016 x_return_status OUT NOCOPY VARCHAR2,
4017 x_msg_count OUT NOCOPY NUMBER,
4018 x_msg_data OUT NOCOPY VARCHAR2,
4019 p_patv_tbl IN patv_tbl_type) IS
4020
4021 l_api_version CONSTANT NUMBER := 1;
4022 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_delete_row';
4023 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
4024 i NUMBER := 0;
4025 BEGIN
4026
4027 IF (l_debug = 'Y') THEN
4028 okc_debug.Set_Indentation('OKC_PAT_PVT');
4029 okc_debug.log('12800: Entered delete_row', 2);
4030 END IF;
4031
4032 OKC_API.init_msg_list(p_init_msg_list);
4033 -- Make sure PL/SQL table has records in it before passing
4034 IF (p_patv_tbl.COUNT > 0) THEN
4035 i := p_patv_tbl.FIRST;
4036 LOOP
4037 delete_row (
4038 p_api_version => p_api_version,
4039 p_init_msg_list => OKC_API.G_FALSE,
4040 x_return_status => x_return_status,
4041 x_msg_count => x_msg_count,
4042 x_msg_data => x_msg_data,
4043 p_patv_rec => p_patv_tbl(i));
4044 EXIT WHEN (i = p_patv_tbl.LAST);
4045 i := p_patv_tbl.NEXT(i);
4046 END LOOP;
4047 END IF;
4048
4049 IF (l_debug = 'Y') THEN
4050 okc_debug.log('12900: Exiting delete_row', 2);
4051 okc_debug.Reset_Indentation;
4052 END IF;
4053
4054 EXCEPTION
4055 WHEN OKC_API.G_EXCEPTION_ERROR THEN
4056
4057 IF (l_debug = 'Y') THEN
4058 okc_debug.log('13000: Exiting delete_row:OKC_API.G_EXCEPTION_ERROR Exception', 2);
4059 okc_debug.Reset_Indentation;
4060 END IF;
4061
4062 x_return_status := OKC_API.HANDLE_EXCEPTIONS
4063 (
4064 l_api_name,
4065 G_PKG_NAME,
4066 'OKC_API.G_RET_STS_ERROR',
4067 x_msg_count,
4068 x_msg_data,
4069 '_PVT'
4070 );
4071 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
4072
4073 IF (l_debug = 'Y') THEN
4074 okc_debug.log('13100: Exiting delete_row:OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception', 2);
4075 okc_debug.Reset_Indentation;
4076 END IF;
4077
4078 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
4079 (
4080 l_api_name,
4081 G_PKG_NAME,
4082 'OKC_API.G_RET_STS_UNEXP_ERROR',
4083 x_msg_count,
4084 x_msg_data,
4085 '_PVT'
4086 );
4087 WHEN OTHERS THEN
4088
4089 IF (l_debug = 'Y') THEN
4090 okc_debug.log('13200: Exiting delete_row:OTHERS Exception', 2);
4091 okc_debug.Reset_Indentation;
4092 END IF;
4093
4094 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
4095 (
4096 l_api_name,
4097 G_PKG_NAME,
4098 'OTHERS',
4099 x_msg_count,
4100 x_msg_data,
4101 '_PVT'
4102 );
4103
4104 END delete_row;
4105
4106 ---------------------------------------------------------------
4107 -- Procedure for mass insert in OKC_PRICE_ADJUSTMENTS _B and TL tables
4108 ---------------------------------------------------------------
4109 PROCEDURE INSERT_ROW_UPG(x_return_status OUT NOCOPY VARCHAR2,p_patv_tbl patv_tbl_type) IS
4110 l_tabsize NUMBER := p_patv_tbl.COUNT;
4111 l_source_lang VARCHAR2(12) := okc_util.get_userenv_lang;
4112
4113 in_id OKC_DATATYPES.NumberTabTyp;
4114 in_pat_id OKC_DATATYPES.NumberTabTyp;
4115 in_chr_id OKC_DATATYPES.NumberTabTyp;
4116 in_cle_id OKC_DATATYPES.NumberTabTyp;
4117 in_bsl_id OKC_DATATYPES.NumberTabTyp;
4118 in_bcl_id OKC_DATATYPES.NumberTabTyp;
4119 in_modified_from OKC_DATATYPES.NumberTabTyp;
4120 in_modified_to OKC_DATATYPES.NumberTabTyp;
4121 in_mod_mech_type_code OKC_DATATYPES.Var90TabTyp;
4122 in_operand OKC_DATATYPES.NumberTabTyp;
4123 in_arithmetic_operator OKC_DATATYPES.Var90TabTyp;
4124 in_automatic_flag OKC_DATATYPES.Var3TabTyp;
4125 in_update_allowed OKC_DATATYPES.Var3TabTyp;
4126 in_updated_flag OKC_DATATYPES.Var3TabTyp;
4127 in_applied_flag OKC_DATATYPES.Var3TabTyp;
4128 in_on_invoice_flag OKC_DATATYPES.Var3TabTyp;
4129 in_pricing_phase_id OKC_DATATYPES.NumberTabTyp;
4130 in_context OKC_DATATYPES.Var90TabTyp;
4131 in_program_application_id OKC_DATATYPES.NumberTabTyp;
4132 in_program_id OKC_DATATYPES.NumberTabTyp;
4133 in_program_update_date OKC_DATATYPES.DateTabTyp;
4134 in_request_id OKC_DATATYPES.NumberTabTyp;
4135 in_list_header_id OKC_DATATYPES.NumberTabTyp;
4136 in_list_line_id OKC_DATATYPES.NumberTabTyp;
4137 in_list_line_type_code OKC_DATATYPES.Var90TabTyp;
4138 in_change_reason_code OKC_DATATYPES.Var90TabTyp;
4139 in_change_reason_text OKC_DATATYPES.Var1995TabTyp;
4140 in_estimated_flag OKC_DATATYPES.Var3TabTyp;
4141 in_adjusted_amount OKC_DATATYPES.NumberTabTyp;
4142 in_charge_type_code OKC_DATATYPES.Var90TabTyp;
4143 in_charge_subtype_code OKC_DATATYPES.Var90TabTyp;
4144 in_range_break_quantity OKC_DATATYPES.NumberTabTyp;
4145 in_accrual_conversion_rate OKC_DATATYPES.NumberTabTyp;
4146 in_pricing_group_sequence OKC_DATATYPES.NumberTabTyp;
4147 in_accrual_flag OKC_DATATYPES.Var3TabTyp;
4148 in_list_line_no OKC_DATATYPES.Var240TabTyp;
4149 in_source_system_code OKC_DATATYPES.Var90TabTyp;
4150 in_benefit_qty OKC_DATATYPES.NumberTabTyp;
4151 in_benefit_uom_code OKC_DATATYPES.Var3TabTyp;
4152 in_expiration_date OKC_DATATYPES.DateTabTyp;
4153 in_modifier_level_code OKC_DATATYPES.Var90TabTyp;
4154 in_price_break_type_code OKC_DATATYPES.Var90TabTyp;
4155 in_substitution_attribute OKC_DATATYPES.Var90TabTyp;
4156 in_proration_type_code OKC_DATATYPES.Var90TabTyp;
4157 in_include_on_returns_flag OKC_DATATYPES.Var3TabTyp;
4158 in_object_version_number OKC_DATATYPES.NumberTabTyp;
4159 in_attribute1 OKC_DATATYPES.Var1995TabTyp;
4160 in_attribute2 OKC_DATATYPES.Var1995TabTyp;
4161 in_attribute3 OKC_DATATYPES.Var1995TabTyp;
4162 in_attribute4 OKC_DATATYPES.Var1995TabTyp;
4163 in_attribute5 OKC_DATATYPES.Var1995TabTyp;
4164 in_attribute6 OKC_DATATYPES.Var1995TabTyp;
4165 in_attribute7 OKC_DATATYPES.Var1995TabTyp;
4166 in_attribute8 OKC_DATATYPES.Var1995TabTyp;
4167 in_attribute9 OKC_DATATYPES.Var1995TabTyp;
4168 in_attribute10 OKC_DATATYPES.Var1995TabTyp;
4169 in_attribute11 OKC_DATATYPES.Var1995TabTyp;
4170 in_attribute12 OKC_DATATYPES.Var1995TabTyp;
4171 in_attribute13 OKC_DATATYPES.Var1995TabTyp;
4172 in_attribute14 OKC_DATATYPES.Var1995TabTyp;
4173 in_attribute15 OKC_DATATYPES.Var1995TabTyp;
4174 in_created_by OKC_DATATYPES.NumberTabTyp;
4175 in_creation_date OKC_DATATYPES.DateTabTyp;
4176 in_last_updated_by OKC_DATATYPES.NumberTabTyp;
4177 in_last_update_date OKC_DATATYPES.DateTabTyp;
4178 in_last_update_login OKC_DATATYPES.NumberTabTyp;
4179 in_rebate_transaction_type_cod OKC_DATATYPES.Var30TabTyp;
4180 i number;
4181 j number;
4182 BEGIN
4183
4184 --Initialize return status
4185 x_return_status := OKC_API.G_RET_STS_SUCCESS;
4186
4187 IF (l_debug = 'Y') THEN
4188 okc_debug.Set_Indentation('OKC_PAT_PVT');
4189 okc_debug.log('13300: Entered INSERT_ROW_UPG', 2);
4190 END IF;
4191
4192 i := p_patv_tbl.FIRST; j:=0;
4193 while i is not null
4194 LOOP
4195 j:=j+1;
4196 in_id (j) := p_patv_tbl(i).id;
4197 in_pat_id (j) := p_patv_tbl(i).pat_id;
4198 in_chr_id (j) := p_patv_tbl(i).chr_id;
4199 in_cle_id (j) := p_patv_tbl(i).cle_id;
4200 in_bsl_id (j) := p_patv_tbl(i).bsl_id;
4201 in_bcl_id (j) := p_patv_tbl(i).bcl_id;
4202 in_modified_from (j) := p_patv_tbl(i).modified_from;
4203 in_modified_to (j) := p_patv_tbl(i).modified_to;
4204 in_mod_mech_type_code(j) := p_patv_tbl(i).modifier_mechanism_type_code;
4205 in_operand (j) := p_patv_tbl(i).operand;
4206 in_arithmetic_operator (j) := p_patv_tbl(i).arithmetic_operator;
4207 in_automatic_flag (j) := p_patv_tbl(i).automatic_flag;
4208 in_update_allowed (j) := p_patv_tbl(i).update_allowed;
4209 in_updated_flag (j) := p_patv_tbl(i).updated_flag;
4210 in_applied_flag (j) := p_patv_tbl(i).applied_flag;
4211 in_on_invoice_flag (j) := p_patv_tbl(i).on_invoice_flag;
4212 in_pricing_phase_id (j) := p_patv_tbl(i).pricing_phase_id;
4213 in_context (j) := p_patv_tbl(i).context;
4214 in_program_application_id (j) := p_patv_tbl(i).program_application_id;
4215 in_program_id (j) := p_patv_tbl(i).program_id;
4216 in_program_update_date (j) := p_patv_tbl(i).program_update_date;
4217 in_request_id (j) := p_patv_tbl(i).request_id;
4218 in_list_header_id (j) := p_patv_tbl(i).list_header_id;
4219 in_list_line_id (j) := p_patv_tbl(i).list_line_id;
4220 in_list_line_type_code (j) := p_patv_tbl(i).list_line_type_code;
4221 in_change_reason_code (j) := p_patv_tbl(i).change_reason_code;
4222 in_change_reason_text (j) := p_patv_tbl(i).change_reason_text;
4223 in_estimated_flag (j) := p_patv_tbl(i).estimated_flag;
4224 in_adjusted_amount (j) := p_patv_tbl(i).adjusted_amount;
4225 in_charge_type_code (j) := p_patv_tbl(i).charge_type_code;
4226 in_charge_subtype_code (j) := p_patv_tbl(i).charge_subtype_code;
4227 in_range_break_quantity (j) := p_patv_tbl(i).range_break_quantity;
4228 in_accrual_conversion_rate (j) := p_patv_tbl(i).accrual_conversion_rate;
4229 in_pricing_group_sequence (j) := p_patv_tbl(i).pricing_group_sequence;
4230 in_accrual_flag (j) := p_patv_tbl(i).accrual_flag;
4231 in_list_line_no (j) := p_patv_tbl(i).list_line_no;
4232 in_source_system_code (j) := p_patv_tbl(i).source_system_code;
4233 in_benefit_qty (j) := p_patv_tbl(i).benefit_qty;
4234 in_benefit_uom_code (j) := p_patv_tbl(i).benefit_uom_code;
4235 in_expiration_date (j) := p_patv_tbl(i).expiration_date;
4236 in_modifier_level_code (j) := p_patv_tbl(i).modifier_level_code;
4237 in_price_break_type_code (j) := p_patv_tbl(i).price_break_type_code;
4238 in_substitution_attribute (j) := p_patv_tbl(i).substitution_attribute;
4239 in_proration_type_code (j) := p_patv_tbl(i).proration_type_code;
4240 in_include_on_returns_flag (j) := p_patv_tbl(i).include_on_returns_flag;
4241 in_object_version_number (j) := p_patv_tbl(i).object_version_number;
4242 in_attribute1 (j) := p_patv_tbl(i).attribute1;
4243 in_attribute2 (j) := p_patv_tbl(i).attribute2;
4244 in_attribute3 (j) := p_patv_tbl(i).attribute3;
4245 in_attribute4 (j) := p_patv_tbl(i).attribute4;
4246 in_attribute5 (j) := p_patv_tbl(i).attribute5;
4247 in_attribute6 (j) := p_patv_tbl(i).attribute6;
4248 in_attribute7 (j) := p_patv_tbl(i).attribute7;
4249 in_attribute8 (j) := p_patv_tbl(i).attribute8;
4250 in_attribute9 (j) := p_patv_tbl(i).attribute9;
4251 in_attribute10 (j) := p_patv_tbl(i).attribute10;
4252 in_attribute11 (j) := p_patv_tbl(i).attribute11;
4253 in_attribute12 (j) := p_patv_tbl(i).attribute12;
4254 in_attribute13 (j) := p_patv_tbl(i).attribute13;
4255 in_attribute14 (j) := p_patv_tbl(i).attribute14;
4256 in_attribute15 (j) := p_patv_tbl(i).attribute15;
4257 in_created_by (j) := p_patv_tbl(i).created_by;
4258 in_creation_date (j) := p_patv_tbl(i).creation_date;
4259 in_last_updated_by (j) := p_patv_tbl(i).last_updated_by;
4260 in_last_update_date (j) := p_patv_tbl(i).last_update_date;
4261 in_last_update_login (j) := p_patv_tbl(i).last_update_login;
4262 in_rebate_transaction_type_cod (j):= p_patv_tbl(i).rebate_transaction_type_code;
4263 i:=p_patv_tbl.next(i);
4264 END LOOP;
4265
4266 FORALL i in 1..l_tabsize
4267 INSERT
4268 INTO OKC_PRICE_ADJUSTMENTS
4269 (
4270 id,
4271 pat_id,
4272 chr_id,
4273 cle_id,
4274 bsl_id,
4275 bcl_id,
4276 created_by,
4277 creation_date,
4278 last_updated_by,
4279 last_update_date,
4280 modified_from,
4281 modified_to,
4282 modifier_mechanism_type_code,
4283 operand,
4284 arithmetic_operator,
4285 automatic_flag,
4286 update_allowed,
4287 updated_flag,
4288 applied_flag,
4289 on_invoice_flag,
4290 pricing_phase_id,
4291 context,
4292 program_application_id,
4293 program_id,
4294 program_update_date,
4295 request_id,
4296 list_header_id,
4297 list_line_id,
4298 list_line_type_code,
4299 change_reason_code,
4300 change_reason_text,
4301 estimated_flag,
4302 adjusted_amount,
4303 charge_type_code,
4304 charge_subtype_code,
4305 range_break_quantity,
4306 accrual_conversion_rate,
4307 pricing_group_sequence,
4308 accrual_flag,
4309 list_line_no,
4310 source_system_code,
4311 benefit_qty,
4312 benefit_uom_code,
4313 expiration_date,
4314 modifier_level_code,
4315 price_break_type_code,
4316 substitution_attribute,
4317 proration_type_code,
4318 include_on_returns_flag,
4319 object_version_number,
4320 attribute1,
4321 attribute2,
4322 attribute3,
4323 attribute4,
4324 attribute5,
4325 attribute6,
4326 attribute7,
4327 attribute8,
4328 attribute9,
4329 attribute10,
4330 attribute11,
4331 attribute12,
4332 attribute13,
4333 attribute14,
4334 attribute15,
4335 last_update_login,
4336 rebate_transaction_type_code
4337 )
4338 VALUES (
4339 in_id(i),
4340 in_pat_id(i),
4341 in_chr_id(i),
4342 in_cle_id(i),
4343 in_bsl_id(i),
4344 in_bcl_id(i),
4345 in_created_by(i),
4346 in_creation_date(i),
4347 in_last_updated_by(i),
4348 in_last_update_date(i),
4349 in_modified_from(i),
4350 in_modified_to(i),
4351 in_mod_mech_type_code(i),
4352 in_operand(i),
4353 in_arithmetic_operator(i),
4354 in_automatic_flag(i),
4355 in_update_allowed(i),
4356 in_updated_flag(i),
4357 in_applied_flag(i),
4358 in_on_invoice_flag(i),
4359 in_pricing_phase_id(i),
4360 in_context(i),
4361
4362 in_program_application_id(i),
4363 in_program_id(i),
4364 in_program_update_date(i),
4365 in_request_id(i),
4366 in_list_header_id(i),
4367 in_list_line_id(i),
4368 in_list_line_type_code(i),
4369 in_change_reason_code(i),
4370 in_change_reason_text(i),
4371 in_estimated_flag(i),
4372 in_adjusted_amount(i),
4373 in_charge_type_code(i),
4374 in_charge_subtype_code(i),
4375 in_range_break_quantity(i),
4376 in_accrual_conversion_rate(i),
4377 in_pricing_group_sequence(i),
4378 in_accrual_flag(i),
4379 in_list_line_no(i),
4380 in_source_system_code(i),
4381 in_benefit_qty(i),
4382 in_benefit_uom_code(i),
4383 in_expiration_date(i),
4384 in_modifier_level_code(i),
4385 in_price_break_type_code(i),
4386 in_substitution_attribute(i),
4387 in_proration_type_code(i),
4388 in_include_on_returns_flag(i),
4389 in_object_version_number(i),
4390
4391 in_attribute1(i),
4392 in_attribute2(i),
4393 in_attribute3(i),
4394 in_attribute4(i),
4395 in_attribute5(i),
4396 in_attribute6(i),
4397 in_attribute7(i),
4398 in_attribute8(i),
4399 in_attribute9(i),
4400 in_attribute10(i),
4401 in_attribute11(i),
4402 in_attribute12(i),
4403 in_attribute13(i),
4404 in_attribute14(i),
4405 in_attribute15(i),
4406 in_last_update_login(i),
4407 in_rebate_transaction_type_cod(i));
4408
4409 IF (l_debug = 'Y') THEN
4410 okc_debug.log('13400: Exiting INSERT_ROW_UPG', 2);
4411 okc_debug.Reset_Indentation;
4412 END IF;
4413
4414 EXCEPTION
4415 WHEN OTHERS THEN
4416 --Store SQL error message on message stack
4417 OKC_API.SET_MESSAGE(
4418 p_app_name => G_APP_NAME,
4419 p_msg_name => G_UNEXPECTED_ERROR,
4420 p_token1 => G_SQLCODE_TOKEN,
4421 p_token1_value => SQLCODE,
4422 p_token2 => G_SQLERRM_TOKEN,
4423 p_token2_value => SQLERRM);
4424 -- notify caller of an error as UNEXPECTED error
4425 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
4426
4427 -- okc_debug.log('13500: Exiting INSERT_ROW_UPG:OTHERS Exception', 2);
4428 -- okc_debug.Reset_Indentation;
4429
4430 -- RAISE;
4431
4432 END INSERT_ROW_UPG;
4433
4434 FUNCTION create_version(
4435 p_chr_id IN NUMBER,
4436 p_major_version IN NUMBER
4437 ) RETURN VARCHAR2 IS
4438
4439 l_return_status VARCHAR2(1):= OKC_API.G_RET_STS_SUCCESS;
4440
4441 BEGIN
4442
4443 IF (l_debug = 'Y') THEN
4444 okc_debug.Set_Indentation('OKC_PAT_PVT');
4445 okc_debug.log('13600: Entered create_version', 2);
4446 END IF;
4447
4448 INSERT INTO okc_price_adjustments_h
4449 (
4450 id,
4451 pat_id,
4452 chr_id,
4453 cle_id,
4454 bsl_id,
4455 bcl_id,
4456 created_by,
4457 creation_date,
4458 last_updated_by,
4459 last_update_date,
4460 modified_from,
4461 modified_to,
4462 modifier_mechanism_type_code,
4463 operand,
4464 arithmetic_operator,
4465 automatic_flag,
4466 update_allowed,
4467 updated_flag,
4468 applied_flag,
4469 on_invoice_flag,
4470 pricing_phase_id,
4471 context,
4472 program_application_id,
4473 program_id,
4474 program_update_date,
4475 request_id,
4476 list_header_id,
4477 list_line_id,
4478 list_line_type_code,
4479 change_reason_code,
4480 change_reason_text,
4481 estimated_flag,
4482 adjusted_amount,
4483 charge_type_code,
4484 charge_subtype_code,
4485 range_break_quantity,
4486 accrual_conversion_rate,
4487 pricing_group_sequence,
4488 accrual_flag,
4489 list_line_no,
4490 source_system_code,
4491 benefit_qty,
4492 benefit_uom_code,
4493 expiration_date,
4494 modifier_level_code,
4495 price_break_type_code,
4496 substitution_attribute,
4497 proration_type_code,
4498 include_on_returns_flag,
4499 object_version_number,
4500 attribute1,
4501 attribute2,
4502 attribute3,
4503 attribute4,
4504 attribute5,
4505 attribute6,
4506 attribute7,
4507 attribute8,
4508 attribute9,
4509 attribute10,
4510 attribute11,
4511 attribute12,
4512 attribute13,
4513 attribute14,
4514 attribute15,
4515 last_update_login,
4516 rebate_transaction_type_code,
4517 major_version
4518 )
4519 SELECT
4520
4521 id,
4522 pat_id,
4523 chr_id,
4524 cle_id,
4525 bsl_id,
4526 bcl_id,
4527 created_by,
4528 creation_date,
4529 last_updated_by,
4530 last_update_date,
4531 modified_from,
4532 modified_to,
4533 modifier_mechanism_type_code,
4534 operand,
4535 arithmetic_operator,
4536 automatic_flag,
4537 update_allowed,
4538 updated_flag,
4539 applied_flag,
4540 on_invoice_flag,
4541 pricing_phase_id,
4542 context,
4543 program_application_id,
4544 program_id,
4545 program_update_date,
4546 request_id,
4547 list_header_id,
4548 list_line_id,
4549 list_line_type_code,
4550 change_reason_code,
4551 change_reason_text,
4552 estimated_flag,
4553 adjusted_amount,
4554 charge_type_code,
4555 charge_subtype_code,
4556 range_break_quantity,
4557 accrual_conversion_rate,
4558 pricing_group_sequence,
4559 accrual_flag,
4560 list_line_no,
4561 source_system_code,
4562 benefit_qty,
4563 benefit_uom_code,
4564 expiration_date,
4565 modifier_level_code,
4566 price_break_type_code,
4567 substitution_attribute,
4568 proration_type_code,
4569 include_on_returns_flag,
4570 object_version_number,
4571 attribute1,
4572 attribute2,
4573 attribute3,
4574 attribute4,
4575 attribute5,
4576 attribute6,
4577 attribute7,
4578 attribute8,
4579 attribute9,
4580 attribute10,
4581 attribute11,
4582 attribute12,
4583 attribute13,
4584 attribute14,
4585 attribute15,
4586 last_update_login,
4587 rebate_transaction_type_code,
4588 p_major_version
4589
4590 FROM okc_price_adjustments
4591 WHERE chr_id = p_chr_id;
4592
4593 IF (l_debug = 'Y') THEN
4594 okc_debug.log('13700: Exiting create_version', 2);
4595 okc_debug.Reset_Indentation;
4596 END IF;
4597
4598 RETURN l_return_status;
4599
4600 EXCEPTION
4601 -- other appropriate handlers
4602 WHEN OTHERS THEN
4603
4604 IF (l_debug = 'Y') THEN
4605 okc_debug.log('13800: Exiting create_version:OTHERS Exception', 2);
4606 okc_debug.Reset_Indentation;
4607 END IF;
4608
4609 -- store SQL error message on message stack
4610 OKC_API.SET_MESSAGE(p_app_name => okc_version_pvt.G_APP_NAME,
4611 p_msg_name => okc_version_pvt.G_UNEXPECTED_ERROR,
4612 p_token1 => okc_version_pvt.G_SQLCODE_TOKEN,
4613 p_token1_value => sqlcode,
4614 p_token2 => okc_version_pvt.G_SQLERRM_TOKEN,
4615 p_token2_value => sqlerrm);
4616
4617 -- notify UNEXPECTED error
4618 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
4619 return l_return_status;
4620
4621 END create_version;
4622
4623 FUNCTION restore_version(
4624 p_chr_id IN NUMBER,
4625 p_major_version IN NUMBER
4626 ) RETURN VARCHAR2 IS
4627
4628 l_return_status VARCHAR2(1):= OKC_API.G_RET_STS_SUCCESS;
4629
4630 BEGIN
4631
4632 IF (l_debug = 'Y') THEN
4633 okc_debug.Set_Indentation('OKC_PAT_PVT');
4634 okc_debug.log('13900: Entered restore_version', 2);
4635 END IF;
4636
4637 INSERT INTO okc_price_adjustments
4638 (
4639 id,
4640 pat_id,
4641 chr_id,
4642 cle_id,
4643 bsl_id,
4644 bcl_id,
4645 created_by,
4646 creation_date,
4647 last_updated_by,
4648 last_update_date,
4649 modified_from,
4650 modified_to,
4651 modifier_mechanism_type_code,
4652 operand,
4653 arithmetic_operator,
4654 automatic_flag,
4655 update_allowed,
4656 updated_flag,
4657 applied_flag,
4658 on_invoice_flag,
4659 pricing_phase_id,
4660 context,
4661 program_application_id,
4662 program_id,
4663 program_update_date,
4664 request_id,
4665 list_header_id,
4666 list_line_id,
4667 list_line_type_code,
4668 change_reason_code,
4669 change_reason_text,
4670 estimated_flag,
4671 adjusted_amount,
4672 charge_type_code,
4673 charge_subtype_code,
4674 range_break_quantity,
4675 accrual_conversion_rate,
4676 pricing_group_sequence,
4677 accrual_flag,
4678 list_line_no,
4679 source_system_code,
4680 benefit_qty,
4681 benefit_uom_code,
4682 expiration_date,
4683 modifier_level_code,
4684 price_break_type_code,
4685 substitution_attribute,
4686 proration_type_code,
4687 include_on_returns_flag,
4688 object_version_number,
4689 attribute1,
4690 attribute2,
4691 attribute3,
4692 attribute4,
4693 attribute5,
4694 attribute6,
4695 attribute7,
4696 attribute8,
4697 attribute9,
4698 attribute10,
4699 attribute11,
4700 attribute12,
4701 attribute13,
4702 attribute14,
4703 attribute15,
4704 last_update_login,
4705 rebate_transaction_type_code)
4706
4707 SELECT
4708
4709 id,
4710 pat_id,
4711 chr_id,
4712 cle_id,
4713 bsl_id,
4714 bcl_id,
4715 created_by,
4716 creation_date,
4717 last_updated_by,
4718 last_update_date,
4719 modified_from,
4720 modified_to,
4721 modifier_mechanism_type_code,
4722 operand,
4723 arithmetic_operator,
4724 automatic_flag,
4725 update_allowed,
4726 updated_flag,
4727 applied_flag,
4728 on_invoice_flag,
4729 pricing_phase_id,
4730 context,
4731 program_application_id,
4732 program_id,
4733 program_update_date,
4734 request_id,
4735 list_header_id,
4736 list_line_id,
4737 list_line_type_code,
4738 change_reason_code,
4739 change_reason_text,
4740 estimated_flag,
4741 adjusted_amount,
4742 charge_type_code,
4743 charge_subtype_code,
4744 range_break_quantity,
4745 accrual_conversion_rate,
4746 pricing_group_sequence,
4747 accrual_flag,
4748 list_line_no,
4749 source_system_code,
4750 benefit_qty,
4751 benefit_uom_code,
4752 expiration_date,
4753 modifier_level_code,
4754 price_break_type_code,
4755 substitution_attribute,
4756 proration_type_code,
4757 include_on_returns_flag,
4758 object_version_number,
4759 attribute1,
4760 attribute2,
4761 attribute3,
4762 attribute4,
4763 attribute5,
4764 attribute6,
4765 attribute7,
4766 attribute8,
4767 attribute9,
4768 attribute10,
4769 attribute11,
4770 attribute12,
4771 attribute13,
4772 attribute14,
4773 attribute15,
4774 last_update_login,
4775 rebate_transaction_type_code
4776
4777 FROM okc_price_adjustments_h
4778 WHERE chr_id = p_chr_id
4779 AND major_version = p_major_version;
4780
4781 IF (l_debug = 'Y') THEN
4782 okc_debug.log('14000: Exiting restore_version', 2);
4783 okc_debug.Reset_Indentation;
4784 END IF;
4785
4786 RETURN l_return_status;
4787
4788 EXCEPTION
4789 -- other appropriate handlers
4790 WHEN OTHERS THEN
4791
4792 IF (l_debug = 'Y') THEN
4793 okc_debug.log('14100: Exiting restore_version:OTHERS Exception', 2);
4794 okc_debug.Reset_Indentation;
4795 END IF;
4796
4797 -- store SQL error message on message stack
4798 OKC_API.SET_MESSAGE(p_app_name => okc_version_pvt.G_APP_NAME,
4799 p_msg_name => okc_version_pvt.G_UNEXPECTED_ERROR,
4800 p_token1 => okc_version_pvt.G_SQLCODE_TOKEN,
4801 p_token1_value => sqlcode,
4802 p_token2 => okc_version_pvt.G_SQLERRM_TOKEN,
4803 p_token2_value => sqlerrm);
4804
4805 -- notify UNEXPECTED error
4806 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
4807 return l_return_status;
4808
4809 END restore_version;
4810
4811 END OKC_PAT_PVT;