[Home] [Help]
PACKAGE BODY: APPS.OKL_SETUPPQVALUES_PVT
Source
1 Package BODY Okl_Setuppqvalues_Pvt AS
2 /* $Header: OKLRSUVB.pls 120.20 2007/09/26 08:25:39 rajnisku noship $ */
3 -- Start of wraper code generated automatically by Debug code generator
4 L_MODULE VARCHAR2(40) := 'LEASE.SETUP.PRODUCTS';
5 L_DEBUG_ENABLED CONSTANT VARCHAR2(10) := OKL_DEBUG_PUB.CHECK_LOG_ENABLED;
6 L_LEVEL_PROCEDURE NUMBER;
7 IS_DEBUG_PROCEDURE_ON BOOLEAN;
8 -- End of wraper code generated automatically by Debug code generator
9
10 G_ITEM_NOT_FOUND_ERROR EXCEPTION;
11 G_COLUMN_TOKEN CONSTANT VARCHAR2(100) := 'COLUMN';
12 G_TABLE_TOKEN CONSTANT VARCHAR2(200) := 'OKL_TABLE_NAME'; --- CHG001
13 G_BOOK_CLASS_MISMATCH CONSTANT VARCHAR2(200) := 'OKL_BOOK_CLASS_MISMATCH';
14 G_TAX_OWNER_MISMATCH CONSTANT VARCHAR2(200) := 'OKL_TAX_OWNER_MISMATCH';
15 G_INT_CALC_BASIS_MISMATCH CONSTANT VARCHAR2(200) := 'OKL_INT_CALC_BASIS_MISMATCH';
16 G_REVENUE_REC_METD_MISMATCH CONSTANT VARCHAR2(200) := 'OKL_REVENUE_REC_METD_MISMATCH';
17 G_INVESTOR_MISMATCH CONSTANT VARCHAR2(200) := 'OKL_INVESTOR_MISMATCH';
18 G_REV_REC_NO_UPDATE CONSTANT VARCHAR2(200) := 'OKL_REV_REC_NO_UPDATE';
19
20
21 G_OPLEASE_LESSEE_MISMATCH CONSTANT VARCHAR2(200) := 'OKL_OPLEASE_LESSEE_MISMATCH'; --- CHG001
22 G_LOAN_LESSOR_MISMATCH CONSTANT VARCHAR2(200) := 'OKL_LOAN_ LESSOR_MISMATCH'; --- CHG001
23 G_LOANREV_LESSOR_MISMATCH CONSTANT VARCHAR2(200) := 'OKL_LOANREV_LESSOR_MISMATCH'; --- CHG001
24
25
26 G_FLT_FAC_LOAN_REV_MISMATCH CONSTANT VARCHAR2(200) := 'OKL_FLT_FAC_LOAN_REV_MISMATCH'; --- CHG001
27 G_FLT_FAC_EST_BILL_MISMATCH CONSTANT VARCHAR2(200) := 'OKL_FLT_FAC_EST_BILL_MISMATCH'; --- CHG001
28 ---------------------------------------------------------------------------
29 -- PROCEDURE get_rec for: Okl_Pdt_Pqy_Vals_v
30 ---------------------------------------------------------------------------
31 PROCEDURE get_rec (
32 p_pqvv_rec IN pqvv_rec_type,
33 x_no_data_found OUT NOCOPY BOOLEAN,
34 x_return_status OUT NOCOPY VARCHAR2,
35 x_pqvv_rec OUT NOCOPY pqvv_rec_type
36 ) IS
37 CURSOR okl_pqvv_pk_csr (p_id IN NUMBER) IS
38 SELECT
39 ID,
40 OBJECT_VERSION_NUMBER,
41 PDQ_ID,
42 PDT_ID,
43 QVE_ID,
44 FROM_DATE,
45 TO_DATE,
46
47
48 CREATED_BY,
49 CREATION_DATE,
50
51 LAST_UPDATED_BY,
52 LAST_UPDATE_DATE,
53 LAST_UPDATE_LOGIN
54 FROM Okl_Pdt_Pqy_Vals_V
55 WHERE okl_pdt_pqy_vals_v.id = p_id;
56 l_okl_pqvv_pk okl_pqvv_pk_csr%ROWTYPE;
57 l_pqvv_rec pqvv_rec_type;
58 BEGIN
59 x_return_status := Okl_Api.G_RET_STS_SUCCESS;
60 x_no_data_found := TRUE;
61 -- Get current database values
62 OPEN okl_pqvv_pk_csr (p_pqvv_rec.id);
63 FETCH okl_pqvv_pk_csr INTO
64 l_pqvv_rec.ID,
65 l_pqvv_rec.OBJECT_VERSION_NUMBER,
66 l_pqvv_rec.PDQ_ID,
67 l_pqvv_rec.PDT_ID,
68 l_pqvv_rec.QVE_ID,
69 l_pqvv_rec.FROM_DATE,
70 l_pqvv_rec.TO_DATE,
71 l_pqvv_rec.CREATED_BY,
72 l_pqvv_rec.CREATION_DATE,
73 l_pqvv_rec.LAST_UPDATED_BY,
74 l_pqvv_rec.LAST_UPDATE_DATE,
75 l_pqvv_rec.LAST_UPDATE_LOGIN;
76 x_no_data_found := okl_pqvv_pk_csr%NOTFOUND;
77 CLOSE okl_pqvv_pk_csr;
78 x_pqvv_rec := l_pqvv_rec;
79 EXCEPTION
80 WHEN OTHERS THEN
81 -- store SQL error message on message stack
82 Okl_Api.SET_MESSAGE(p_app_name => G_APP_NAME,
83 p_msg_name => G_UNEXPECTED_ERROR,
84 p_token1 => G_SQLCODE_TOKEN,
85 p_token1_value => SQLCODE,
86 p_token2 => G_SQLERRM_TOKEN,
87 p_token2_value => SQLERRM);
88 -- notify UNEXPECTED error for calling API.
89 x_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
90
91 IF (okl_pqvv_pk_csr%ISOPEN) THEN
92 CLOSE okl_pqvv_pk_csr;
93 END IF;
94
95 END get_rec;
96
97 ---------------------------------------------------------------------------
98 -- PROCEDURE get_parent_dates for: Okl_Pdt_Pqy_Vals_v
99 ---------------------------------------------------------------------------
100
101 PROCEDURE get_parent_dates(
102 p_pqvv_rec IN pqvv_rec_type,
103 x_no_data_found OUT NOCOPY BOOLEAN,
104 x_return_status OUT NOCOPY VARCHAR2,
105 x_pdtv_rec OUT NOCOPY pdtv_rec_type
106 ) IS
107 CURSOR okl_pdt_pk_csr (p_pdt_id IN NUMBER) IS
108 SELECT FROM_DATE,
109 TO_DATE
110 FROM Okl_products_V pdtv
111 WHERE pdtv.id = p_pdt_id;
112 l_okl_pdtv_pk okl_pdt_pk_csr%ROWTYPE;
113 l_pdtv_rec pdtv_rec_type;
114 BEGIN
115 x_return_status := Okl_Api.G_RET_STS_SUCCESS;
116 x_no_data_found := TRUE;
117 -- Get current database values
118 OPEN okl_pdt_pk_csr (p_pqvv_rec.pdt_id);
119 FETCH okl_pdt_pk_csr INTO
120 l_pdtv_rec.FROM_DATE,
121 l_pdtv_rec.TO_DATE;
122 x_no_data_found := okl_pdt_pk_csr%NOTFOUND;
123 CLOSE okl_pdt_pk_csr;
124 x_pdtv_rec := l_pdtv_rec;
125 EXCEPTION
126 WHEN OTHERS THEN
127 -- store SQL error message on message stack
128 Okl_Api.SET_MESSAGE(p_app_name => G_APP_NAME,
129 p_msg_name => G_UNEXPECTED_ERROR,
130 p_token1 => G_SQLCODE_TOKEN,
131 p_token1_value => SQLCODE,
132 p_token2 => G_SQLERRM_TOKEN,
133 p_token2_value => SQLERRM);
134 -- notify UNEXPECTED error for calling API.
135 x_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
136
137
138 IF (okl_pdt_pk_csr%ISOPEN) THEN
139 CLOSE okl_pdt_pk_csr;
140
141 END IF;
142
143 END get_parent_dates;
144
145
146
147 -----------------------------------------------------------------------------
148
149 -- PROCEDURE check_constraints for: Okl_Pdt_Pqy_Vals_v
150 -----------------------------------------------------------------------------
151
152
153 PROCEDURE Check_Constraints (
154 p_api_version IN NUMBER,
155 p_init_msg_list IN VARCHAR2 DEFAULT Okl_Api.G_FALSE,
156 p_pqvv_rec IN pqvv_rec_type,
157 p_db_pqvv_rec IN pqvv_rec_type,
158 x_return_status OUT NOCOPY VARCHAR2,
159 x_msg_count OUT NOCOPY NUMBER,
160 x_msg_data OUT NOCOPY VARCHAR2,
161 x_valid OUT NOCOPY BOOLEAN
162 ) IS
163 CURSOR okl_pqvv_chk_upd(p_pdt_id NUMBER
164 ) IS
165 SELECT '1' FROM okl_k_headers_v khdr
166 WHERE khdr.pdt_id = p_pdt_id;
167
168 CURSOR okl_pqv_pdt_fk_csr (p_pdt_id IN Okl_Products_V.ID%TYPE,
169 p_date IN Okl_Products_V.TO_DATE%TYPE
170 ) IS
171 SELECT '1'
172 FROM Okl_products_V pdt
173 WHERE pdt.ID = p_pdt_id
174 AND NVL(pdt.TO_DATE, p_date) < p_date;
175
176 CURSOR okl_pqv_constraints_csr(p_qve_id IN Okl_Pdt_Pqy_Vals_V.QVE_ID%TYPE,
177 p_from_date IN Okl_Pdt_Pqy_Vals_V.FROM_DATE%TYPE,
178 p_to_date IN Okl_Pdt_Pqy_Vals_V.TO_DATE%TYPE
179 ) IS
180 SELECT '1'
181 FROM Okl_Pqy_Values_V qve
182 WHERE qve.ID = p_qve_id
183 AND ((qve.FROM_DATE > p_from_date OR
184 p_from_date > NVL(qve.TO_DATE,p_from_date)) OR
185 NVL(qve.TO_DATE, p_to_date) < p_to_date);
186
187 CURSOR c1(p_pdt_id okl_pdt_pqy_vals_v.pdt_id%TYPE,
188 p_pdq_id okl_pdt_pqy_vals_v.pdq_id%TYPE) IS
189 SELECT '1'
190 FROM okl_pdt_pqy_vals_v
191 WHERE pdt_id = p_pdt_id
192 AND pdq_id = p_pdq_id
193 AND id <> NVL(p_pqvv_rec.id,-9999);
194
195 CURSOR pdt_parameters_csr(cp_pdt_id IN Okl_Pdt_Pqy_Vals_V.QVE_ID%TYPE)
196 IS
197 SELECT pqy.id pqy_id,pqy.name name,
198 qve.id qve_id,qve.value value
199 FROM okl_pdt_pqy_vals_v pqv,
200 okl_pqy_values_v qve,
201 okl_pdt_qualitys_v pqy
202 WHERE pqv.pdt_id = cp_pdt_id
203 AND pqy.ID = qve.PQY_ID
204 AND qve.ID = pqv.QVE_ID
205 AND pqy.name IN ('LEASE','INVESTOR','TAXOWNER','INTEREST_CALCULATION_BASIS','REVENUE_RECOGNITION_METHOD');
206
207
208 CURSOR pdt_parameters_csr1(cp_pdt_id IN Okl_Pdt_Pqy_Vals_V.QVE_ID%TYPE)
209 IS
210 SELECT pqy.id pqy_id,pqy.name name,
211 qve.id qve_id,qve.value value
212 FROM okl_pdt_pqy_vals_v pqv,
213 okl_pqy_values_v qve,
214 okl_pdt_qualitys_v pqy
215 WHERE pqv.pdt_id = cp_pdt_id
216 AND pqy.ID = qve.PQY_ID
217 AND qve.ID = pqv.QVE_ID
218 AND pqy.name IN ('REVENUE_RECOGNITION_METHOD','INTEREST_CALCULATION_BASIS');
219
220 CURSOR csr_loan_rev(p_pdt_id okl_pdt_pqy_vals_v.pdt_id%TYPE)
221 IS
222 SELECT a.deal_type,a. REVENUE_RECOGNITION_METHOD,A.INTEREST_CALCULATION_BASIS
223 FROM okl_product_parameters_v a
224 WHERE a.id = p_pdt_id;
225
226
227 cursor chk_pqy_value(p_pdq_id okl_pdt_pqy_vals_v.pdq_id%TYPE)
228 IS
229 SELECT pqy.name
230 FROM okl_pdt_qualitys pqy,
231 okl_pdt_pqys pdq
232 WHERE pdq.id = p_pdq_id
233 and pqy.id = pdq.pqy_id;
234
235 cursor chk_deal_type(p_pdt_id okl_pdt_pqy_vals_v.pdt_id%TYPE,
236 p_qve_id okl_pdt_pqy_vals_v.qve_id%TYPE)
237 IS
238 SELECT DISTINCT C.deal_type
239 FROM okl_products_v a,
240 okl_ae_tmpt_sets_v b,
241 OKL_ST_GEN_TMPT_SETS c
242 WHERE a.aes_id = b.id
243 AND b.gts_id = c.id
244 AND a.id = p_pdt_id
245 intersect
246 select value from okl_pqy_values qve
247 where qve.id = p_qve_id;
248
249
250 cursor chk_investor(p_pdt_id okl_pdt_pqy_vals_v.pdt_id%TYPE,
251 p_qve_id okl_pdt_pqy_vals_v.qve_id%TYPE)
252 IS
253 SELECT DISTINCT decode (C.deal_type, 'SALE','SECURITIZATION','SYNDICATION','SYNDICATION') DEAL_TYPE
254 FROM okl_products_v a,
255 okl_ae_tmpt_sets_v b,
256 OKL_ST_GEN_TMPT_SETS c
257 WHERE a.aes_id = b.id
258 AND b.gts_id = c.id
259 AND a.id = p_pdt_id
260 intersect
261 select value from okl_pqy_values qve
262 where qve.id = p_qve_id;
263
264 cursor chk_tax_owner(p_pdt_id okl_pdt_pqy_vals_v.pdt_id%TYPE,
265 p_qve_id okl_pdt_pqy_vals_v.qve_id%TYPE)
266 IS
267 SELECT DISTINCT C.TAX_OWNER
268 FROM okl_products_v a,
269 okl_ae_tmpt_sets_v b,
270 OKL_ST_GEN_TMPT_SETS c
271 WHERE a.aes_id = b.id
272 AND b.gts_id = c.id
273 AND a.id = p_pdt_id
274 intersect
275 select value from okl_pqy_values qve
276 where qve.id = p_qve_id;
277
278 cursor chk_intrst_calc_mthd(p_pdt_id okl_pdt_pqy_vals_v.pdt_id%TYPE,
279 p_qve_id okl_pdt_pqy_vals_v.qve_id%TYPE)
280 IS
281 SELECT DISTINCT C.INTEREST_CALC_METH_CODE
282 FROM okl_products_v a,
283 okl_ae_tmpt_sets_v b,
284 OKL_ST_GEN_TMPT_SETS c
285 WHERE a.aes_id = b.id
286 AND b.gts_id = c.id
287 AND a.id = p_pdt_id
288 intersect
289 select value from okl_pqy_values qve
290 where qve.id = p_qve_id;
291
292 cursor chk_rev_rec_methd(p_pdt_id okl_pdt_pqy_vals_v.pdt_id%TYPE,
293 p_qve_id okl_pdt_pqy_vals_v.qve_id%TYPE)
294 IS
295 SELECT DISTINCT C.REVENUE_RECOG_METH_CODE
296 FROM okl_products_v a,
297 okl_ae_tmpt_sets_v b,
298 OKL_ST_GEN_TMPT_SETS c
299 WHERE a.aes_id = b.id
300 AND b.gts_id = c.id
301 AND a.id = p_pdt_id
302 intersect
303 select value from okl_pqy_values qve
304 where qve.id = p_qve_id;
305
306 Cursor csr_rev_rec_no_update(p_pdt_id okl_pdt_pqy_vals_v.pdt_id%TYPE) IS
307 SELECT '1'
308 FROM okl_product_parameters_v pdt
309 where reporting_pdt_id = p_pdt_id;
310
311 csr_rec pdt_parameters_csr%ROWTYPE;
312 l_leaseop_lessee_mismatch NUMBER(4):=0;
313 l_loan_lessor_mismatch NUMBER(4):=0;
314 l_loanrev_lessor_mismatch NUMBER(4):=0;
315 l_FLT_FAC_loan_rev_mismatch NUMBER(4):=0;
316 l_FLT_FAC_EST_BILL_mismatch NUMBER(4):=0;
317 l_REV_REC_NO_UPDATE VARCHAR2(1);
318
319 l_check VARCHAR2(1) := '?';
320 l_deal_type VARCHAR2(500);
321 l_investor VARCHAR2(500);
322
323 l_tax_owner VARCHAR2(50);
324 l_pqy_value VARCHAR2(50);
325 l_row_not_found BOOLEAN := FALSE;
326 l_sysdate DATE := to_date(to_char(SYSDATE, 'DD/MM/YYYY'), 'DD/MM/YYYY');
327 l_unq_tbl Okc_Util.unq_tbl_type;
328 l_pqv_status VARCHAR2(1);
329 l_row_found BOOLEAN := FALSE;
330 l_token_1 VARCHAR2(999);
331 l_token_2 VARCHAR2(999);
332 l_token_3 VARCHAR2(999);
333 l_token_4 VARCHAR2(999);
334 l_token_5 VARCHAR2(999);
335 l_token_6 VARCHAR2(999);
336 l_token_7 VARCHAR2(999);
337
338 BEGIN
339 x_valid := TRUE;
340 x_return_status := Okl_Api.G_RET_STS_SUCCESS;
341
342
343 l_token_1 := Okl_Accounting_Util.Get_Message_Token(p_region_code => 'OKL_LP_PDT_PQV_VAL_SUMRY',
344 p_attribute_code => 'OKL_PDT_QUALITY_VALUES');
345
346
347 l_token_2 := Okl_Accounting_Util.Get_Message_Token(p_region_code => 'OKL_LP_PRODUCT_SERCH',
348 p_attribute_code => 'OKL_PRODUCTS');
349
350 l_token_3 := l_token_1 ||','||l_token_2;
351
352
353 l_token_4 := Okl_Accounting_Util.Get_Message_Token(p_region_code => 'OKL_CONTRACT_DTLS',
354 p_attribute_code => 'OKL_KDTLS_CONTRACT');
355
356 l_token_5 := Okl_Accounting_Util.Get_Message_Token(p_region_code => 'OKL_LP_PDT_PQV_VAL_CREATE',
357 p_attribute_code => 'OKL_PRODUCT_QUALITY_VALUE');
358
359 l_token_6 := Okl_Accounting_Util.Get_Message_Token(p_region_code => 'OKL_LP_PDT_PQV_VAL_SUMRY',
360 p_attribute_code => 'OKL_VALUE');
361
362 l_token_7 := Okl_Accounting_Util.Get_Message_Token(p_region_code => 'OKL_LP_PQVALS_CRUPD',
363 p_attribute_code => 'OKL_PRODUCT_QUALITY_VALUES');
364
365 -- Check for pqvv valid dates
366 OPEN okl_pqvv_chk_upd(p_pqvv_rec.pdt_id);
367
368 FETCH okl_pqvv_chk_upd INTO l_check;
369 l_row_not_found := okl_pqvv_chk_upd%NOTFOUND;
370 CLOSE okl_pqvv_chk_upd;
371
372 IF l_row_not_found = FALSE THEN
373 Okl_Api.SET_MESSAGE(p_app_name => G_APP_NAME,
374 p_msg_name => G_IN_USE,
375 p_token1 => G_PARENT_TABLE_TOKEN,
376 p_token1_value => l_token_1,
377 p_token2 => G_CHILD_TABLE_TOKEN,
378 p_token2_value => l_token_4);
379 x_valid := FALSE;
380 x_return_status := Okl_Api.G_RET_STS_ERROR;
381 RAISE G_EXCEPTION_HALT_PROCESSING;
382 END IF;
383
384 Open chk_pqy_value(p_pqvv_rec.pdq_id);
385 fetch chk_pqy_value into l_pqy_value;
386 if chk_pqy_value%notfound then
387 null;
388 end if;
389 close chk_pqy_value;
390
391
392 IF p_pqvv_rec.id <> Okl_Api.G_MISS_NUM OR
393 p_pqvv_rec.id IS NOT NULL THEN
394
395 if ltrim(rtrim(l_pqy_value)) IN ('REVENUE_RECOGNITION_METHOD') THEN
396
397 -- Check csr_rev_rec_no_update
398 OPEN csr_rev_rec_no_update(p_pqvv_rec.pdt_id);
399
400 FETCH csr_rev_rec_no_update INTO l_REV_REC_NO_UPDATE;
401
402 IF csr_rev_rec_no_update%FOUND AND p_pqvv_rec.qve_id <> p_db_pqvv_rec.qve_id then
403 Okl_Api.SET_MESSAGE(p_app_name => G_APP_NAME,
404 p_msg_name => G_REV_REC_NO_UPDATE);
405 x_return_status := Okl_Api.G_RET_STS_ERROR;
406 RAISE G_EXCEPTION_HALT_PROCESSING;
407 END IF;
408
409 CLOSE csr_rev_rec_no_update;
410
411 END IF;
412 END IF;
413
414 if ltrim(rtrim(l_pqy_value)) IN ('INVESTOR') THEN
415
416
417 -- Check chk_investor
418 OPEN chk_investor(p_pqvv_rec.pdt_id,p_pqvv_rec.qve_id);
419
420 FETCH chk_investor INTO l_investor;
421
422 if chk_investor%NOTFOUND then
423
424
425 Okl_Api.SET_MESSAGE(p_app_name => G_APP_NAME,
426 p_msg_name => G_INVESTOR_MISMATCH);
427 x_return_status := Okl_Api.G_RET_STS_ERROR;
428 RAISE G_EXCEPTION_HALT_PROCESSING;
429 END IF;
430
431 CLOSE chk_investor;
432 END IF;
433
434 if ltrim(rtrim(l_pqy_value)) IN ('LEASE') THEN
435
436
437 -- Check chk_deal_type
438 OPEN chk_deal_type(p_pqvv_rec.pdt_id,p_pqvv_rec.qve_id);
439
440 FETCH chk_deal_type INTO l_deal_type;
441
442 if chk_deal_type%NOTFOUND then
443
444
445 Okl_Api.SET_MESSAGE(p_app_name => G_APP_NAME,
446 p_msg_name => G_BOOK_CLASS_MISMATCH);
447 x_return_status := Okl_Api.G_RET_STS_ERROR;
448 RAISE G_EXCEPTION_HALT_PROCESSING;
449 END IF;
450
451 CLOSE chk_deal_type;
452 END IF;
453
454 if ltrim(rtrim(l_pqy_value)) = 'TAXOWNER' THEN
455
456 -- Check chk_tax_owner
457 OPEN chk_tax_owner(p_pqvv_rec.pdt_id,p_pqvv_rec.qve_id);
458
459 FETCH chk_tax_owner INTO l_tax_owner;
460
461 if chk_tax_owner%NOTFOUND then
462
463 Okl_Api.SET_MESSAGE(p_app_name => G_APP_NAME,
464 p_msg_name => G_BOOK_CLASS_MISMATCH);
465 x_return_status := Okl_Api.G_RET_STS_ERROR;
466 RAISE G_EXCEPTION_HALT_PROCESSING;
467 END IF;
468
469
470 CLOSE chk_tax_owner;
471 END IF;
472
473
474 if ltrim(rtrim(l_pqy_value)) IN ('INTEREST_CALCULATION_BASIS') THEN
475
476
477 -- Check chk_deal_type
478 OPEN chk_intrst_calc_mthd(p_pqvv_rec.pdt_id,p_pqvv_rec.qve_id);
479
480 FETCH chk_intrst_calc_mthd INTO l_deal_type;
481
482 if chk_intrst_calc_mthd%NOTFOUND then
483
484
485 Okl_Api.SET_MESSAGE(p_app_name => G_APP_NAME,
486 p_msg_name => G_INT_CALC_BASIS_MISMATCH);
487 x_return_status := Okl_Api.G_RET_STS_ERROR;
488 RAISE G_EXCEPTION_HALT_PROCESSING;
489 END IF;
490
491 CLOSE chk_intrst_calc_mthd;
492 END IF;
493
494
495 if ltrim(rtrim(l_pqy_value)) IN ('REVENUE_RECOGNITION_METHOD') THEN
496
497
498 -- Check chk_deal_type
499 OPEN chk_rev_rec_methd(p_pqvv_rec.pdt_id,p_pqvv_rec.qve_id);
500
501 FETCH chk_rev_rec_methd INTO l_deal_type;
502
503 if chk_rev_rec_methd%NOTFOUND then
504
505
506 Okl_Api.SET_MESSAGE(p_app_name => G_APP_NAME,
507 p_msg_name => G_REVENUE_REC_METD_MISMATCH);
508 x_return_status := Okl_Api.G_RET_STS_ERROR;
509 RAISE G_EXCEPTION_HALT_PROCESSING;
510 END IF;
511
512 CLOSE chk_rev_rec_methd;
513 END IF;
514
515
516 -- check for uniquness.
517 IF p_pqvv_rec.id = Okl_Api.G_MISS_NUM THEN
518 OPEN c1(p_pqvv_rec.pdt_id,
519 p_pqvv_rec.pdq_id);
520 FETCH c1 INTO l_pqv_status;
521 l_row_found := c1%FOUND;
522 CLOSE c1;
523 IF l_row_found THEN
524
525 Okl_Api.SET_MESSAGE(p_app_name => G_APP_NAME,
526 p_msg_name => 'OKL_COLUMN_NOT_UNIQUE',
527 p_token1 => G_TABLE_TOKEN,
528 p_token1_value => l_token_1,
529 p_token2 => G_COLUMN_TOKEN,
530 p_token2_value => l_token_6);
531
532 x_valid := FALSE;
533 x_return_status := Okl_Api.G_RET_STS_ERROR;
534 RAISE G_EXCEPTION_HALT_PROCESSING;
535 END IF;
536 END IF;
537
538 BEGIN
539 FOR CSR_REC IN pdt_parameters_csr(p_pqvv_rec.pdt_id)
540 LOOP
541 IF csr_rec.name = 'LEASE' AND csr_rec.value = 'LEASEOP' THEN
542 SELECT COUNT(b.id)
543 INTO l_leaseop_lessee_mismatch
544 FROM OKL_pdt_pqy_vals_V a,
545 okl_pdt_qualitys_v b,
546 okl_pqy_values_v c
547 WHERE a.pdt_ID = p_pqvv_rec.pdt_id
548 AND b.id = c.pqy_id
549 AND c.id = p_pqvv_rec.qve_id
550 AND c.value = 'LESSEE'
551 AND b.name = 'TAXOWNER';
552 ELSIF csr_rec.name = 'TAXOWNER' and csr_rec.value = 'LESSEE' THEN
553 SELECT COUNT(b.id)
554 INTO l_leaseop_lessee_mismatch
555 FROM OKL_pdt_pqy_vals_V a,
556 okl_pdt_qualitys_v b,
557 okl_pqy_values_v c
558 WHERE a.pdt_ID = p_pqvv_rec.pdt_id
559 AND b.id = c.pqy_id
560 AND c.id = p_pqvv_rec.qve_id
561 AND c.value = 'LEASEOP'
562 AND b.name = 'LEASE';
563 END IF;
564
565 IF csr_rec.name = 'INTEREST_CALCULATION_BASIS' and csr_rec.value = 'FLOAT_FACTORS' THEN
566 SELECT COUNT(b.id)
567 INTO l_FLT_FAC_loan_rev_mismatch
568 FROM OKL_pdt_pqy_vals_V a,
569 okl_pdt_qualitys_v b,
570 okl_pqy_values_v c
571 WHERE a.pdt_ID = p_pqvv_rec.pdt_id
572 AND b.id = c.pqy_id
573 AND c.id = p_pqvv_rec.qve_id
574 AND c.value = 'LOAN-REVOLVING'
575 AND b.name = 'LEASE';
576 ELSIF csr_rec.name = 'LEASE' and csr_rec.value = 'LOAN-REVOLVING' THEN
577 SELECT COUNT(b.id)
578 INTO l_FLT_FAC_loan_rev_mismatch
579 FROM OKL_pdt_pqy_vals_V a,
580 okl_pdt_qualitys_v b,
581 okl_pqy_values_v c
582 WHERE a.pdt_ID = p_pqvv_rec.pdt_id
583 AND b.id = c.pqy_id
584 AND c.id = p_pqvv_rec.qve_id
585 AND c.value = 'FLOAT_FACTORS'
586 AND b.name = 'INTEREST_CALCULATION_BASIS';
587 END IF;
588
589
590 IF csr_rec.name = 'REVENUE_RECOGNITION_METHOD' and csr_rec.value = 'ESTIMATED_AND_BILLED' THEN
591 SELECT COUNT(b.id)
592 INTO l_FLT_FAC_EST_BILL_mismatch
593 FROM OKL_pdt_pqy_vals_V a,
594 okl_pdt_qualitys_v b,
595 okl_pqy_values_v c
596 WHERE a.pdt_ID = p_pqvv_rec.pdt_id
597 AND b.id = c.pqy_id
598 AND c.id = p_pqvv_rec.qve_id
599 AND c.value = 'FLOAT_FACTORS'
600 AND b.name = 'INTEREST_CALCULATION_BASIS';
601 ELSIF csr_rec.name = 'INTEREST_CALCULATION_BASIS' and csr_rec.value = 'FLOAT_FACTORS' THEN
602 SELECT COUNT(b.id)
603 INTO l_FLT_FAC_EST_BILL_mismatch
604 FROM OKL_pdt_pqy_vals_V a,
605 okl_pdt_qualitys_v b,
606 okl_pqy_values_v c
607 WHERE a.pdt_ID = p_pqvv_rec.pdt_id
608 AND b.id = c.pqy_id
609 AND c.id = p_pqvv_rec.qve_id
610 AND c.value = 'ESTIMATED_AND_BILLED'
611 AND b.name = 'REVENUE_RECOGNITION_METHOD';
612 END IF;
613
614
615 IF csr_rec.name = 'LEASE' AND csr_rec.value = 'LOAN' THEN
616 SELECT COUNT(b.id)
617
618 INTO l_loan_lessor_mismatch
619 FROM OKL_pdt_pqy_vals_V a,
620 okl_pdt_qualitys_v b,
621 okl_pqy_values_v c
622 WHERE a.pdt_ID = p_pqvv_rec.pdt_id
623 AND b.id = c.pqy_id
624 AND c.id = p_pqvv_rec.qve_id
625 AND c.value = 'LESSOR'
626
627 AND b.name = 'TAXOWNER';
628 ELSIF csr_rec.name = 'TAXOWNER' AND csr_rec.value = 'LESSOR' THEN
629 SELECT COUNT(b.id)
630 INTO l_leaseop_lessee_mismatch
631 FROM OKL_pdt_pqy_vals_V a,
632 okl_pdt_qualitys_v b,
633 okl_pqy_values_v c
634 WHERE a.pdt_ID = p_pqvv_rec.pdt_id
635 AND b.id = c.pqy_id
636 AND c.id = p_pqvv_rec.qve_id
637 AND c.value = 'LOAN'
638 AND b.name = 'LEASE';
639 END IF;
640
641 IF csr_rec.name = 'LEASE' AND csr_rec.value = 'LOAN-REVOLVING' THEN
642 SELECT COUNT(b.id)
643
644 INTO l_loanrev_lessor_mismatch
645 FROM OKL_pdt_pqy_vals_V a,
646 okl_pdt_qualitys_v b,
647 okl_pqy_values_v c
648 WHERE a.pdt_ID = p_pqvv_rec.pdt_id
649 AND b.id = c.pqy_id
650 AND c.id = p_pqvv_rec.qve_id
651 AND c.value = 'LESSOR'
652 AND b.name = 'TAXOWNER';
653 ELSIF csr_rec.name = 'TAXOWNER' AND csr_rec.value = 'LESSOR' THEN
654 SELECT COUNT(b.id)
655 INTO l_leaseop_lessee_mismatch
656 FROM OKL_pdt_pqy_vals_V a,
657 okl_pdt_qualitys_v b,
658 okl_pqy_values_v c
659 WHERE a.pdt_ID = p_pqvv_rec.pdt_id
660 AND b.id = c.pqy_id
661 AND c.id = p_pqvv_rec.qve_id
662 AND c.value = 'LOAN-REVOLVING'
663 AND b.name = 'LEASE';
664 END IF;
665 END LOOP;
666
667 IF l_FLT_FAC_loan_rev_mismatch <> 0 THEN
668 Okl_Api.SET_MESSAGE(p_app_name => G_APP_NAME,
669 p_msg_name => G_FLT_FAC_LOAN_REV_MISMATCH);
670 x_valid := FALSE;
671 x_return_status := Okl_Api.G_RET_STS_ERROR;
672 RAISE G_EXCEPTION_HALT_PROCESSING;
673 END IF;
674
675 IF l_FLT_FAC_EST_BILL_mismatch <> 0 THEN
676 Okl_Api.SET_MESSAGE(p_app_name => G_APP_NAME,
677 p_msg_name => G_FLT_FAC_EST_BILL_MISMATCH);
678 x_valid := FALSE;
679 x_return_status := Okl_Api.G_RET_STS_ERROR;
680 RAISE G_EXCEPTION_HALT_PROCESSING;
681 END IF;
682
683
684 IF l_leaseop_lessee_mismatch <> 0 THEN
685 Okl_Api.SET_MESSAGE(p_app_name => G_APP_NAME,
686 p_msg_name => G_OPLEASE_LESSEE_MISMATCH);
687 x_valid := FALSE;
688 x_return_status := Okl_Api.G_RET_STS_ERROR;
689 RAISE G_EXCEPTION_HALT_PROCESSING;
690 END IF;
691
692 IF l_loan_lessor_mismatch <> 0 THEN
693 Okl_Api.SET_MESSAGE(p_app_name => G_APP_NAME,
694 p_msg_name => G_LOAN_LESSOR_MISMATCH);
695 x_valid := FALSE;
696 x_return_status := Okl_Api.G_RET_STS_ERROR;
697 RAISE G_EXCEPTION_HALT_PROCESSING;
698 END IF;
699
700 IF l_loanrev_lessor_mismatch <> 0 THEN
701 Okl_Api.SET_MESSAGE(p_app_name => G_APP_NAME,
702 p_msg_name => G_LOANREV_LESSOR_MISMATCH);
703 x_valid := FALSE;
704 x_return_status := Okl_Api.G_RET_STS_ERROR;
705 RAISE G_EXCEPTION_HALT_PROCESSING;
706 END IF;
707
708 END;
709
710 -- Check for constraints dates
711 IF p_pqvv_rec.id = Okl_Api.G_MISS_NUM THEN
712 OPEN okl_pqv_constraints_csr(p_pqvv_rec.qve_id,
713 p_pqvv_rec.from_date,
714 p_pqvv_rec.TO_DATE);
715 FETCH okl_pqv_constraints_csr INTO l_check;
716 l_row_not_found := okl_pqv_constraints_csr%NOTFOUND;
717 CLOSE okl_pqv_constraints_csr;
718
719 IF l_row_not_found = FALSE THEN
720 Okl_Api.SET_MESSAGE(p_app_name => G_APP_NAME,
721 p_msg_name => G_DATES_MISMATCH,
722 p_token1 => G_PARENT_TABLE_TOKEN,
723 p_token1_value => l_token_7,
724 p_token2 => G_CHILD_TABLE_TOKEN,
725 p_token2_value => l_token_3);
726 x_valid := FALSE;
727 x_return_status := Okl_Api.G_RET_STS_ERROR;
728 RAISE G_EXCEPTION_HALT_PROCESSING;
729 END IF;
730 END IF;
731
732
733 EXCEPTION
734
735 WHEN G_EXCEPTION_HALT_PROCESSING THEN
736 -- no processing necessary; validation can continue
737 -- with the next column
738 NULL;
739 WHEN OTHERS THEN
740 -- store SQL error message on message stack
741 Okl_Api.SET_MESSAGE(p_app_name => G_APP_NAME,
742 p_msg_name => G_UNEXPECTED_ERROR,
743 p_token1 => G_SQLCODE_TOKEN,
744 p_token1_value => SQLCODE,
745
746 p_token2 => G_SQLERRM_TOKEN,
747 p_token2_value => SQLERRM);
748 x_valid := FALSE;
749 x_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
750
751 IF (okl_pqvv_chk_upd%ISOPEN) THEN
752 CLOSE okl_pqvv_chk_upd;
753 END IF;
754
755 IF (okl_pqv_pdt_fk_csr%ISOPEN) THEN
756 CLOSE okl_pqv_pdt_fk_csr;
757 END IF;
758
759 IF (okl_pqv_constraints_csr%ISOPEN) THEN
760 CLOSE okl_pqv_constraints_csr;
761
762 END IF;
763
764 IF (c1%ISOPEN) THEN
765 CLOSE c1;
766 END IF;
767
768 IF (chk_deal_type%ISOPEN) THEN
769 CLOSE chk_deal_type;
770 END IF;
771
772 IF (chk_investor%ISOPEN) THEN
773 CLOSE chk_investor;
774 END IF;
775
776
777 IF (chk_tax_owner%ISOPEN) THEN
778 CLOSE chk_tax_owner;
779 END IF;
780
781 IF (chk_pqy_value%ISOPEN) THEN
782 CLOSE chk_pqy_value;
783 END IF;
784
785
786 END Check_Constraints;
787
788 ---------------------------------------------------------------------------
789 -- PROCEDURE Validate_Qve_Id
790 ---------------------------------------------------------------------------
791 -- Start of comments
792 --
793 -- Procedure Name : Validate_Qve_Id
794 -- Description :
795 -- Business Rules :
796 -- Parameters :
797 -- Version : 1.0
798 -- End of comments
799 ---------------------------------------------------------------------------
800 PROCEDURE Validate_Qve_Id(p_pqvv_rec IN pqvv_rec_type
801 ,x_return_status OUT NOCOPY VARCHAR2)
802 IS
803 CURSOR okl_qvev_pk_csr (p_id IN NUMBER) IS
804 SELECT '1'
805 FROM okl_pqy_values_v
806 WHERE okl_pqy_values_v.id = p_id;
807
808 l_qve_status VARCHAR2(1);
809 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
810 l_row_notfound BOOLEAN := TRUE;
811 l_token_1 VARCHAR2(999);
812
813 BEGIN
814 -- initialize return status
815 x_return_status := Okl_Api.G_RET_STS_SUCCESS;
816
817 l_token_1 := Okl_Accounting_Util.Get_Message_Token(p_region_code => 'OKL_LP_PDT_PQV_VAL_CREATE',
818 p_attribute_code => 'OKL_PRODUCT_QUALITY_VALUE');
819
820 -- check for data before processing
821 IF (p_pqvv_rec.qve_id IS NULL) OR
822 (p_pqvv_rec.qve_id = Okl_Api.G_MISS_NUM) THEN
823 -- Start of wraper code generated automatically by Debug code generator for Okl_Api.SET_MESSAGE
824 IF(L_DEBUG_ENABLED='Y') THEN
825 L_LEVEL_PROCEDURE :=FND_LOG.LEVEL_PROCEDURE;
826 IS_DEBUG_PROCEDURE_ON := OKL_DEBUG_PUB.Check_Log_On(L_MODULE, L_LEVEL_PROCEDURE);
827 END IF;
828 IF(IS_DEBUG_PROCEDURE_ON) THEN
829 BEGIN
830 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'Begin Debug OKLRSUVB.pls call Okl_Api.SET_MESSAGE ');
831 END;
832 END IF;
833 Okl_Api.SET_MESSAGE(p_app_name => Okl_Pqv_Pvt.g_app_name
834 ,p_msg_name => Okl_Pqv_Pvt.g_required_value
835 ,p_token1 => Okl_Pqv_Pvt.g_col_name_token
836 ,p_token1_value => l_token_1);
837
838 IF(IS_DEBUG_PROCEDURE_ON) THEN
839 BEGIN
840 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'End Debug OKLRSUVB.pls call Okl_Api.SET_MESSAGE ');
841 END;
842 END IF;
843 -- End of wraper code generated automatically by Debug code generator for Okl_Api.SET_MESSAGE
844 x_return_status := Okl_Api.G_RET_STS_ERROR;
845 RAISE G_EXCEPTION_HALT_PROCESSING;
846 END IF;
847
848 IF (p_pqvv_rec.QVE_ID IS NOT NULL)
849 THEN
850 OPEN okl_qvev_pk_csr(p_pqvv_rec.QVE_ID);
851 FETCH okl_qvev_pk_csr INTO l_qve_status;
852 l_row_notfound := okl_qvev_pk_csr%NOTFOUND;
853 CLOSE okl_qvev_pk_csr;
854 IF (l_row_notfound) THEN
855 -- Start of wraper code generated automatically by Debug code generator for Okl_Api.set_message
856 IF(IS_DEBUG_PROCEDURE_ON) THEN
857 BEGIN
858 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'Begin Debug OKLRSUVB.pls call Okl_Api.set_message ');
859 END;
860 END IF;
861 Okl_Api.set_message(Okl_Pqv_Pvt.G_APP_NAME, Okl_Pqv_Pvt.G_INVALID_VALUE,Okl_Pqv_Pvt.G_COL_NAME_TOKEN,l_token_1);
862 IF(IS_DEBUG_PROCEDURE_ON) THEN
863 BEGIN
864 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'End Debug OKLRSUVB.pls call Okl_Api.set_message ');
865
866
867 END;
868 END IF;
869 -- End of wraper code generated automatically by Debug code generator for Okl_Api.set_message
870 RAISE G_ITEM_NOT_FOUND_ERROR;
871 END IF;
872 END IF;
873
874 EXCEPTION
875 WHEN G_EXCEPTION_HALT_PROCESSING THEN
876 -- no processing necessary; validation can continue
877 -- with the next column
878 NULL;
879 WHEN G_ITEM_NOT_FOUND_ERROR THEN
880 x_return_status := Okl_Api.G_RET_STS_ERROR;
881
882 WHEN OTHERS THEN
883 -- store SQL error message on message stack for caller
884 Okl_Api.SET_MESSAGE(p_app_name => Okl_Pqv_Pvt.g_app_name,
885 p_msg_name => Okl_Pqv_Pvt.g_unexpected_error,
886 p_token1 => Okl_Pqv_Pvt.g_sqlcode_token,
887 p_token1_value => SQLCODE,
888 p_token2 => Okl_Pqv_Pvt.g_sqlerrm_token,
889 p_token2_value => SQLERRM);
890
891 -- notify caller of an UNEXPECTED error
892 x_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
893
894 END Validate_Qve_Id;
895
896 ---------------------------------------------------------------------------
897 -- FUNCTION Validate_Attributes
898 ---------------------------------------------------------------------------
899 --Function Name : Validate_Attributes
900 -- Description :
901 -- Business Rules :
902 -- Parameters :
903 -- Version : 1.0
904 ---------------------------------------------------------------------------
905
906 FUNCTION Validate_Attributes (
907 p_pqvv_rec IN pqvv_rec_type
908 ) RETURN VARCHAR2 IS
909 x_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
910 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
911 BEGIN
912 -- Validate_Qve_Id
913 Validate_Qve_Id(p_pqvv_rec,x_return_status);
914 IF (x_return_status <> Okl_Api.G_RET_STS_SUCCESS) THEN
915 IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
916 -- need to leave
917 l_return_status := x_return_status;
918 RAISE G_EXCEPTION_HALT_PROCESSING;
919 ELSE
920 -- record that there was an error
921 l_return_status := x_return_status;
922 END IF;
923 END IF;
924 RETURN(l_return_status);
925 EXCEPTION
926 WHEN G_EXCEPTION_HALT_PROCESSING THEN
927 -- just come out with return status
928 NULL;
929 RETURN (l_return_status);
930
931 WHEN OTHERS THEN
932 -- store SQL error message on message stack for caller
933 Okl_Api.SET_MESSAGE(p_app_name => Okl_Pqv_Pvt.g_app_name,
934 p_msg_name => Okl_Pqv_Pvt.g_unexpected_error,
935 p_token1 => Okl_Pqv_Pvt.g_sqlcode_token,
936 p_token1_value => SQLCODE,
937
938 p_token2 => Okl_Pqv_Pvt.g_sqlerrm_token,
939 p_token2_value => SQLERRM);
940 -- notify caller of an UNEXPECTED error
941 l_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
942 RETURN(l_return_status);
943
944 END Validate_Attributes;
945
946
947 ---------------------------------------------------------------------------
948 -- PROCEDURE insert_pqvvalues for: Okl_Pdt_Pqy_Vals_v
949 ---------------------------------------------------------------------------
950 PROCEDURE insert_pqvalues(
951 p_api_version IN NUMBER,
952 p_init_msg_list IN VARCHAR2 DEFAULT Okl_Api.G_FALSE,
953 x_return_status OUT NOCOPY VARCHAR2,
954 x_msg_count OUT NOCOPY NUMBER,
955 x_msg_data OUT NOCOPY VARCHAR2,
956 p_pqyv_rec IN pqyv_rec_type,
957 p_pdtv_rec IN pdtv_rec_type,
958 p_pqvv_rec IN pqvv_rec_type,
959 x_pqvv_rec OUT NOCOPY pqvv_rec_type
960 ) IS
961 l_api_version CONSTANT NUMBER := 1;
962 l_api_name CONSTANT VARCHAR2(30) := 'insert_pqvalues';
963 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
964 l_sysdate DATE := to_date(to_char(SYSDATE, 'DD/MM/YYYY'), 'DD/MM/YYYY');
965 l_valid BOOLEAN;
966
967 l_pqvv_rec pqvv_rec_type;
968 l_db_pqvv_rec pqvv_rec_type;
969
970 l_pdtv_rec pdtv_rec_type;
971 l_row_notfound BOOLEAN := TRUE;
972 l_no_data_found BOOLEAN := TRUE;
973 BEGIN
974 x_return_status := Okl_Api.G_RET_STS_SUCCESS;
975
976 l_pqvv_rec := p_pqvv_rec;
977
978 l_return_status := Okl_Api.START_ACTIVITY(p_api_name => l_api_name,
979 p_pkg_name => G_PKG_NAME,
980 p_init_msg_list => p_init_msg_list,
981 l_api_version => l_api_version,
982 p_api_version => p_api_version,
983 p_api_type => '_PVT',
984 x_return_status => l_return_status);
985
986 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
987 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
988 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
989 RAISE Okl_Api.G_EXCEPTION_ERROR;
990 END IF;
991
992 get_parent_dates(p_pqvv_rec => l_pqvv_rec,
993 x_no_data_found => l_row_notfound,
994 x_return_status => l_return_status,
995 x_pdtv_rec => l_pdtv_rec);
996
997
998 IF (l_row_notfound) THEN
999 l_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
1000 ELSIF l_return_status = Okl_Api.G_RET_STS_ERROR THEN
1001 RAISE Okl_Api.G_EXCEPTION_ERROR;
1002 ELSIF l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR THEN
1003 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
1004 END IF;
1005
1006 --assign parent dates.
1007
1008 l_pqvv_rec.from_date := l_pdtv_rec.from_date;
1009 l_pqvv_rec.TO_DATE := l_pdtv_rec.TO_DATE;
1010
1011 /* call check_constraints to check the validity of this relationship */
1012
1013 Check_Constraints(p_api_version => p_api_version,
1014 p_init_msg_list => p_init_msg_list,
1015 p_pqvv_rec => l_pqvv_rec,
1016 p_db_pqvv_rec => l_db_pqvv_rec,
1017 x_return_status => l_return_status,
1018 x_msg_count => x_msg_count,
1019 x_msg_data => x_msg_data,
1020 x_valid => l_valid);
1021
1022 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
1023 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
1024 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) OR
1025 (l_return_status = Okl_Api.G_RET_STS_SUCCESS AND
1026 l_valid <> TRUE) THEN
1027 x_return_status := Okl_Api.G_RET_STS_ERROR;
1028 RAISE Okl_Api.G_EXCEPTION_ERROR;
1029 END IF;
1030
1031 /* public api to insert pqyvalues */
1032
1033 -- Start of wraper code generated automatically by Debug code generator for Okl_Pqy_Values_Pub.insert_pqy_values
1034 IF(L_DEBUG_ENABLED='Y') THEN
1035 L_LEVEL_PROCEDURE :=FND_LOG.LEVEL_PROCEDURE;
1036 IS_DEBUG_PROCEDURE_ON := OKL_DEBUG_PUB.Check_Log_On(L_MODULE, L_LEVEL_PROCEDURE);
1037 END IF;
1038 IF(IS_DEBUG_PROCEDURE_ON) THEN
1039 BEGIN
1040
1041 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'Begin Debug OKLRSUVB.pls call Okl_Pqy_Values_Pub.insert_pqy_values ');
1042 END;
1043 END IF;
1044 Okl_Pqy_Values_Pub.insert_pqy_values(p_api_version => p_api_version,
1045 p_init_msg_list => p_init_msg_list,
1046 x_return_status => l_return_status,
1047 x_msg_count => x_msg_count,
1048 x_msg_data => x_msg_data,
1049 p_pqvv_rec => l_pqvv_rec,
1050 x_pqvv_rec => x_pqvv_rec);
1051 IF(IS_DEBUG_PROCEDURE_ON) THEN
1052 BEGIN
1053 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'End Debug OKLRSUVB.pls call Okl_Pqy_Values_Pub.insert_pqy_values ');
1054 END;
1055 END IF;
1056 -- End of wraper code generated automatically by Debug code generator for Okl_Pqy_Values_Pub.insert_pqy_values
1057
1058 IF l_return_status = Okl_Api.G_RET_STS_ERROR THEN
1059 RAISE Okl_Api.G_EXCEPTION_ERROR;
1060 ELSIF l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR THEN
1061 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
1062 END IF;
1063
1064 Okl_Api.END_ACTIVITY(x_msg_count => x_msg_count,
1065 x_msg_data => x_msg_data);
1066 EXCEPTION
1067 WHEN Okl_Api.G_EXCEPTION_ERROR THEN
1068 x_return_status := Okl_Api.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
1069 p_pkg_name => G_PKG_NAME,
1070
1071 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
1072 x_msg_count => x_msg_count,
1073
1074 x_msg_data => x_msg_data,
1075 p_api_type => '_PVT');
1076 WHEN Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR THEN
1077 x_return_status := Okl_Api.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
1078 p_pkg_name => G_PKG_NAME,
1079 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
1080 x_msg_count => x_msg_count,
1081 x_msg_data => x_msg_data,
1082 p_api_type => '_PVT');
1083 WHEN OTHERS THEN
1084 x_return_status := Okl_Api.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
1085 p_pkg_name => G_PKG_NAME,
1086 p_exc_name => 'OTHERS',
1087 x_msg_count => x_msg_count,
1088 x_msg_data => x_msg_data,
1089 p_api_type => '_PVT');
1090
1091 END insert_pqvalues;
1092
1093 ---------------------------------------------------------------------------
1094 -- PROCEDURE update_pqvvalues for: Okl_Pdt_Pqy_Vals_v
1095 ---------------------------------------------------------------------------
1096 PROCEDURE update_pqvalues(
1097 p_api_version IN NUMBER,
1098 p_init_msg_list IN VARCHAR2 DEFAULT Okl_Api.G_FALSE,
1099 x_return_status OUT NOCOPY VARCHAR2,
1100 x_msg_count OUT NOCOPY NUMBER,
1101 x_msg_data OUT NOCOPY VARCHAR2,
1102 p_pqyv_rec IN pqyv_rec_type,
1103 p_pdtv_rec IN pdtv_rec_type,
1104 p_pqvv_rec IN pqvv_rec_type,
1105 x_pqvv_rec OUT NOCOPY pqvv_rec_type
1106 ) IS
1107 l_api_version CONSTANT NUMBER := 1;
1108 l_api_name CONSTANT VARCHAR2(30) := 'update_pqvalues';
1109 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
1110 l_sysdate DATE := to_date(to_char(SYSDATE, 'DD/MM/YYYY'), 'DD/MM/YYYY');
1111 l_valid BOOLEAN;
1112 l_pqvv_rec pqvv_rec_type;
1113 l_pdtv_rec pdtv_rec_type;
1114 l_db_pqvv_rec pqvv_rec_type;
1115 l_row_notfound BOOLEAN := TRUE;
1116 l_no_data_found BOOLEAN := TRUE;
1117
1118
1119 BEGIN
1120 x_return_status := Okl_Api.G_RET_STS_SUCCESS;
1121
1122 l_pqvv_rec := p_pqvv_rec;
1123
1124 l_return_status := Okl_Api.START_ACTIVITY(p_api_name => l_api_name,
1125 p_pkg_name => G_PKG_NAME,
1126 p_init_msg_list => p_init_msg_list,
1127 l_api_version => l_api_version,
1128 p_api_version => p_api_version,
1129 p_api_type => '_PVT',
1130 x_return_status => l_return_status);
1131
1132 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
1133 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
1134 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
1135 RAISE Okl_Api.G_EXCEPTION_ERROR;
1136 END IF;
1137
1138 /* fetch old details from the database */
1139
1140 get_rec(p_pqvv_rec => l_pqvv_rec,
1141 x_return_status => l_return_status,
1142
1143 x_no_data_found => l_no_data_found,
1144 x_pqvv_rec => l_db_pqvv_rec);
1145
1146 IF l_return_status <> Okl_Api.G_RET_STS_SUCCESS OR
1147 l_no_data_found = TRUE THEN
1148 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
1149 END IF;
1150
1151 get_parent_dates(p_pqvv_rec => l_pqvv_rec,
1152 x_no_data_found => l_row_notfound,
1153 x_return_status => l_return_status,
1154 x_pdtv_rec => l_pdtv_rec);
1155
1156 IF (l_row_notfound) THEN
1157
1158 l_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
1159 ELSIF l_return_status = Okl_Api.G_RET_STS_ERROR THEN
1160 RAISE Okl_Api.G_EXCEPTION_ERROR;
1161 ELSIF l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR THEN
1162 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
1163 END IF;
1164
1165 --assign parent dates.
1166
1167 l_pqvv_rec.from_date := l_pdtv_rec.from_date;
1168 l_pqvv_rec.TO_DATE := l_pdtv_rec.TO_DATE;
1169
1170
1171 /* call check_constraints to check the validity of this relationship */
1172
1173 Check_Constraints(p_api_version => p_api_version,
1174 p_init_msg_list => p_init_msg_list,
1175 p_pqvv_rec => l_pqvv_rec,
1176 p_db_pqvv_rec => l_db_pqvv_rec,
1177 x_return_status => l_return_status,
1178 x_msg_count => x_msg_count,
1179 x_msg_data => x_msg_data,
1180 x_valid => l_valid);
1181
1182 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
1183 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
1184 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) OR
1185 (l_return_status = Okl_Api.G_RET_STS_SUCCESS AND
1186 l_valid <> TRUE) THEN
1187 x_return_status := Okl_Api.G_RET_STS_ERROR;
1188 RAISE Okl_Api.G_EXCEPTION_ERROR;
1189
1190 END IF;
1191
1192 /* public api to update pqyvalues */
1193
1194
1195 -- Start of wraper code generated automatically by Debug code generator for Okl_Pqy_Values_Pub.update_pqy_values
1196 IF(L_DEBUG_ENABLED='Y') THEN
1197 L_LEVEL_PROCEDURE :=FND_LOG.LEVEL_PROCEDURE;
1198 IS_DEBUG_PROCEDURE_ON := OKL_DEBUG_PUB.Check_Log_On(L_MODULE, L_LEVEL_PROCEDURE);
1199 END IF;
1200 IF(IS_DEBUG_PROCEDURE_ON) THEN
1201 BEGIN
1202 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'Begin Debug OKLRSUVB.pls call Okl_Pqy_Values_Pub.update_pqy_values ');
1203 END;
1204 END IF;
1205 Okl_Pqy_Values_Pub.update_pqy_values(p_api_version => p_api_version,
1206 p_init_msg_list => p_init_msg_list,
1207 x_return_status => l_return_status,
1208 x_msg_count => x_msg_count,
1209 x_msg_data => x_msg_data,
1210 p_pqvv_rec => l_pqvv_rec,
1211 x_pqvv_rec => x_pqvv_rec);
1212 IF(IS_DEBUG_PROCEDURE_ON) THEN
1213 BEGIN
1214 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'End Debug OKLRSUVB.pls call Okl_Pqy_Values_Pub.update_pqy_values ');
1215 END;
1216 END IF;
1217 -- End of wraper code generated automatically by Debug code generator for Okl_Pqy_Values_Pub.update_pqy_values
1218
1219 IF l_return_status = Okl_Api.G_RET_STS_ERROR THEN
1220 RAISE Okl_Api.G_EXCEPTION_ERROR;
1221 ELSIF l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR THEN
1222 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
1223 END IF;
1224
1225 Okl_Api.END_ACTIVITY(x_msg_count => x_msg_count,
1226 x_msg_data => x_msg_data);
1227 EXCEPTION
1228 WHEN Okl_Api.G_EXCEPTION_ERROR THEN
1229 x_return_status := Okl_Api.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
1230 p_pkg_name => G_PKG_NAME,
1231 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
1232 x_msg_count => x_msg_count,
1233 x_msg_data => x_msg_data,
1234 p_api_type => '_PVT');
1235 WHEN Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR THEN
1236 x_return_status := Okl_Api.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
1237 p_pkg_name => G_PKG_NAME,
1238 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
1239 x_msg_count => x_msg_count,
1240 x_msg_data => x_msg_data,
1241 p_api_type => '_PVT');
1242 WHEN OTHERS THEN
1243 x_return_status := Okl_Api.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
1244 p_pkg_name => G_PKG_NAME,
1245 p_exc_name => 'OTHERS',
1246 x_msg_count => x_msg_count,
1247 x_msg_data => x_msg_data,
1248 p_api_type => '_PVT');
1249
1250 END update_pqvalues;
1251 PROCEDURE update_pqvalues(
1252 p_api_version IN NUMBER,
1253 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
1254 x_return_status OUT NOCOPY VARCHAR2,
1255 x_msg_count OUT NOCOPY NUMBER,
1256 x_msg_data OUT NOCOPY VARCHAR2,
1257 p_pqyv_rec IN pqyv_rec_type,
1258 p_pdtv_rec IN pdtv_rec_type,
1259 p_pqvv_tbl IN pqvv_tbl_type,
1260 x_pqvv_tbl OUT NOCOPY pqvv_tbl_type) IS
1261 l_api_name CONSTANT VARCHAR2(30) := 'update_pqvalues';
1262 l_overall_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1263 i NUMBER := 0;
1264 l_api_version CONSTANT NUMBER := 1;
1265 BEGIN
1266 x_return_status := Okl_Api.G_RET_STS_SUCCESS;
1267 l_overall_status := Okl_Api.START_ACTIVITY(p_api_name => l_api_name,
1268 p_pkg_name => G_PKG_NAME,
1269 p_init_msg_list => p_init_msg_list,
1270 l_api_version => l_api_version,
1271 p_api_version => p_api_version,
1272 p_api_type => '_PVT',
1273 x_return_status => l_overall_status);
1274 -- Make sure PL/SQL table has records in it before passing
1275 IF (p_pqvv_tbl.COUNT > 0) THEN
1276 i := p_pqvv_tbl.FIRST;
1277 LOOP
1278 update_pqvalues(
1279 p_api_version => p_api_version,
1280 p_init_msg_list => OKL_API.G_FALSE,
1281 x_return_status => x_return_status,
1282 x_msg_count => x_msg_count,
1283 x_msg_data => x_msg_data,
1284 p_pqyv_rec => p_pqyv_rec,
1285 p_pdtv_rec => p_pdtv_rec,
1286 p_pqvv_rec => p_pqvv_tbl(i),
1287 x_pqvv_rec => x_pqvv_tbl(i)
1288 );
1289 IF x_return_status = OKL_API.G_RET_STS_ERROR THEN
1290 RAISE OKL_API.G_EXCEPTION_ERROR;
1291 ELSIF x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR THEN
1292 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1293 END IF;
1294
1295 EXIT WHEN (i = p_pqvv_tbl.LAST);
1296 i := p_pqvv_tbl.NEXT(i);
1297 END LOOP;
1298 END IF;
1299 Okl_Api.END_ACTIVITY(x_msg_count => x_msg_count,
1300 x_msg_data => x_msg_data);
1301 EXCEPTION
1302 WHEN Okl_Api.G_EXCEPTION_ERROR THEN
1303 x_return_status := Okl_Api.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
1304 p_pkg_name => G_PKG_NAME,
1305 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
1306 x_msg_count => x_msg_count,
1307 x_msg_data => x_msg_data,
1308 p_api_type => '_PVT');
1309 WHEN Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR THEN
1310
1311 x_return_status := Okl_Api.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
1312 p_pkg_name => G_PKG_NAME,
1313 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
1314 x_msg_count => x_msg_count,
1315 x_msg_data => x_msg_data,
1316
1317 p_api_type => '_PVT');
1318 WHEN OTHERS THEN
1319 x_return_status := Okl_Api.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
1320 p_pkg_name => G_PKG_NAME,
1321 p_exc_name => 'OTHERS',
1322 x_msg_count => x_msg_count,
1323 x_msg_data => x_msg_data,
1324 p_api_type => '_PVT');
1325
1326
1327
1328
1329 END update_pqvalues;
1330 PROCEDURE insert_pqvalues(
1331 p_api_version IN NUMBER,
1332 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
1333 x_return_status OUT NOCOPY VARCHAR2,
1334 x_msg_count OUT NOCOPY NUMBER,
1335 x_msg_data OUT NOCOPY VARCHAR2,
1336 p_pqyv_rec IN pqyv_rec_type,
1337 p_pdtv_rec IN pdtv_rec_type,
1338 p_pqvv_tbl IN pqvv_tbl_type,
1339 x_pqvv_tbl OUT NOCOPY pqvv_tbl_type) IS
1340 l_api_name CONSTANT VARCHAR2(30) := 'insert_pqvalues';
1341 l_overall_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1342 i NUMBER := 0;
1343 l_api_version CONSTANT NUMBER := 1;
1344 l_pqyv_rec pqyv_rec_type ;
1345 l_pdtv_rec pdtv_rec_type;
1346 BEGIN
1347 x_return_status := Okl_Api.G_RET_STS_SUCCESS;
1348 l_overall_status := Okl_Api.START_ACTIVITY(p_api_name => l_api_name,
1349 p_pkg_name => G_PKG_NAME,
1350 p_init_msg_list => p_init_msg_list,
1351 l_api_version => l_api_version,
1352 p_api_version => p_api_version,
1353 p_api_type => '_PVT',
1354 x_return_status => l_overall_status);
1355
1356 -- Make sure PL/SQL table has records in it before passing
1357 IF (p_pqvv_tbl.COUNT > 0) THEN
1358 i := p_pqvv_tbl.FIRST;
1359
1360 LOOP
1361
1362
1363 insert_pqvalues(
1364 p_api_version => p_api_version,
1365 p_init_msg_list => OKL_API.G_FALSE,
1366 x_return_status => x_return_status,
1367 x_msg_count => x_msg_count,
1368 x_msg_data => x_msg_data,
1369 p_pqyv_rec => p_pqyv_rec,
1370 p_pdtv_rec => p_pdtv_rec,
1371 p_pqvv_rec => p_pqvv_tbl(i),
1372 x_pqvv_rec => x_pqvv_tbl(i)
1373 );
1374
1375 IF x_return_status = OKL_API.G_RET_STS_ERROR THEN
1376 RAISE OKL_API.G_EXCEPTION_ERROR;
1377 ELSIF x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR THEN
1378 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1379 END IF;
1380
1381 EXIT WHEN (i = p_pqvv_tbl.LAST);
1382 i := p_pqvv_tbl.NEXT(i);
1383 END LOOP;
1384 END IF;
1385 Okl_Api.END_ACTIVITY(x_msg_count => x_msg_count,
1386 x_msg_data => x_msg_data);
1387 EXCEPTION
1388 WHEN Okl_Api.G_EXCEPTION_ERROR THEN
1389 x_return_status := Okl_Api.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
1390 p_pkg_name => G_PKG_NAME,
1391 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
1392 x_msg_count => x_msg_count,
1393 x_msg_data => x_msg_data,
1394 p_api_type => '_PVT');
1395 WHEN Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR THEN
1396
1397 x_return_status := Okl_Api.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
1398 p_pkg_name => G_PKG_NAME,
1399 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
1400 x_msg_count => x_msg_count,
1401 x_msg_data => x_msg_data,
1402
1403 p_api_type => '_PVT');
1404 WHEN OTHERS THEN
1405 x_return_status := Okl_Api.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
1406 p_pkg_name => G_PKG_NAME,
1407 p_exc_name => 'OTHERS',
1408 x_msg_count => x_msg_count,
1409 x_msg_data => x_msg_data,
1410 p_api_type => '_PVT');
1411
1412
1413
1414
1415 END insert_pqvalues;
1416
1417 END Okl_Setuppqvalues_Pvt;