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