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