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