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