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