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