[Home] [Help]
PACKAGE BODY: APPS.OKL_SETUPPRODUCTS_PVT
Source
1 PACKAGE BODY Okl_Setupproducts_Pvt AS
2 /* $Header: OKLRSPDB.pls 120.28.12010000.5 2008/10/03 21:30:25 apaul 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_IN_USE CONSTANT VARCHAR2(100) := 'OKL_IN_USE';
10 G_BOOK_CLASS_MISS CONSTANT VARCHAR2(200) := 'OKL_BOOK_CLASS_MISS';
11 G_PDT_NOT_VALIDATED CONSTANT VARCHAR2(200) := 'OKL_PDT_NOT_VALIDATED';
12 G_PDT_SUBMTD_FOR_APPROVAL CONSTANT VARCHAR2(200) := 'OKL_PDT_SUBMTD_FOR_APPROVAL';
13 G_PTL_AES_BC_MISMATCH CONSTANT VARCHAR2(200) := 'OKL_PTL_AES_BC_MISMATCH';
14 G_PDT_IN_PEND_APPROVAL CONSTANT VARCHAR2(200) := 'OKL_PDT_IN_PEND_APPROVAL';
15 G_PDT_APPROVED CONSTANT VARCHAR2(200) := 'OKL_PDT_APPROVED';
16 G_PDT_VALDTION_NOT_VALID CONSTANT VARCHAR2(200) := 'OKL_PDT_VALDTION_NOT_VALID';
17
18
19 -- product Stream Type
20 SUBTYPE psyv_rec_type IS Okl_Pdt_Stys_Pub.psyv_rec_type;
21 SUBTYPE psyv_tbl_type IS Okl_Pdt_Stys_Pub.psyv_tbl_type;
22
23 ---------------------------------------------------------------------------
24 -- PROCEDURE get_rec for: OKL_PRODUCTS_V
25 ---------------------------------------------------------------------------
26 PROCEDURE get_rec (
27 p_pdtv_rec IN pdtv_rec_type,
28 x_return_status OUT NOCOPY VARCHAR2,
29 x_no_data_found OUT NOCOPY BOOLEAN,
30 x_pdtv_rec OUT NOCOPY pdtv_rec_type
31 ) IS
32 CURSOR okl_pdtv_pk_csr (p_id IN NUMBER) IS
33 SELECT
34 ID,
35 OBJECT_VERSION_NUMBER,
36 NAME,
37 VERSION,
38 NVL(DESCRIPTION,Okl_Api.G_MISS_CHAR) DESCRIPTION,
39 AES_ID,
40 PTL_ID,
41 REPORTING_PDT_ID,
42 NVL(LEGACY_PRODUCT_YN, Okl_Api.G_MISS_CHAR) LEGACY_PRODUCT_YN,
43 FROM_DATE,
44 NVL(TO_DATE,Okl_Api.G_MISS_DATE) TO_DATE,
45 NVL(product_status_code, Okl_Api.G_MISS_CHAR) product_status_code,
46 NVL(ATTRIBUTE_CATEGORY, Okl_Api.G_MISS_CHAR) ATTRIBUTE_CATEGORY,
47 NVL(ATTRIBUTE1, Okl_Api.G_MISS_CHAR) ATTRIBUTE1,
48 NVL(ATTRIBUTE2, Okl_Api.G_MISS_CHAR) ATTRIBUTE2,
49 NVL(ATTRIBUTE3, Okl_Api.G_MISS_CHAR) ATTRIBUTE3,
50 NVL(ATTRIBUTE4, Okl_Api.G_MISS_CHAR) ATTRIBUTE4,
51 NVL(ATTRIBUTE5, Okl_Api.G_MISS_CHAR) ATTRIBUTE5,
52 NVL(ATTRIBUTE6, Okl_Api.G_MISS_CHAR) ATTRIBUTE6,
53 NVL(ATTRIBUTE7, Okl_Api.G_MISS_CHAR) ATTRIBUTE7,
54 NVL(ATTRIBUTE8, Okl_Api.G_MISS_CHAR) ATTRIBUTE8,
55 NVL(ATTRIBUTE9, Okl_Api.G_MISS_CHAR) ATTRIBUTE9,
56 NVL(ATTRIBUTE10, Okl_Api.G_MISS_CHAR) ATTRIBUTE10,
57 NVL(ATTRIBUTE11, Okl_Api.G_MISS_CHAR) ATTRIBUTE11,
58 NVL(ATTRIBUTE12, Okl_Api.G_MISS_CHAR) ATTRIBUTE12,
59 NVL(ATTRIBUTE13, Okl_Api.G_MISS_CHAR) ATTRIBUTE13,
60 NVL(ATTRIBUTE14, Okl_Api.G_MISS_CHAR) ATTRIBUTE14,
61 NVL(ATTRIBUTE15, Okl_Api.G_MISS_CHAR) ATTRIBUTE15,
62 CREATED_BY,
63 CREATION_DATE,
64 LAST_UPDATED_BY,
65 LAST_UPDATE_DATE,
66 NVL(LAST_UPDATE_LOGIN, Okl_Api.G_MISS_NUM) LAST_UPDATE_LOGIN
67 FROM Okl_Products_V
68 WHERE okl_products_v.id = p_id;
69 l_okl_pdtv_pk okl_pdtv_pk_csr%ROWTYPE;
70 l_pdtv_rec pdtv_rec_type;
71 BEGIN
72 x_return_status := Okl_Api.G_RET_STS_SUCCESS;
73 x_no_data_found := TRUE;
74
75 -- Get current database values
76 OPEN okl_pdtv_pk_csr (p_pdtv_rec.id);
77 FETCH okl_pdtv_pk_csr INTO
78 l_pdtv_rec.ID,
79 l_pdtv_rec.OBJECT_VERSION_NUMBER,
80 l_pdtv_rec.NAME,
81 l_pdtv_rec.VERSION,
82 l_pdtv_rec.DESCRIPTION,
83 l_pdtv_rec.AES_ID,
84 l_pdtv_rec.PTL_ID,
85 l_pdtv_rec.REPORTING_PDT_ID,
86
87 l_pdtv_rec.LEGACY_PRODUCT_YN,
88 l_pdtv_rec.FROM_DATE,
89 l_pdtv_rec.TO_DATE,
90 l_pdtv_rec.product_status_code,
91 l_pdtv_rec.ATTRIBUTE_CATEGORY,
92 l_pdtv_rec.ATTRIBUTE1,
93
94
95 l_pdtv_rec.ATTRIBUTE2,
96 l_pdtv_rec.ATTRIBUTE3,
97 l_pdtv_rec.ATTRIBUTE4,
98 l_pdtv_rec.ATTRIBUTE5,
99 l_pdtv_rec.ATTRIBUTE6,
100 l_pdtv_rec.ATTRIBUTE7,
101 l_pdtv_rec.ATTRIBUTE8,
102 l_pdtv_rec.ATTRIBUTE9,
103 l_pdtv_rec.ATTRIBUTE10,
104 l_pdtv_rec.ATTRIBUTE11,
105 l_pdtv_rec.ATTRIBUTE12,
106 l_pdtv_rec.ATTRIBUTE13,
107 l_pdtv_rec.ATTRIBUTE14,
108 l_pdtv_rec.ATTRIBUTE15,
109 l_pdtv_rec.CREATED_BY,
110 l_pdtv_rec.CREATION_DATE,
111 l_pdtv_rec.LAST_UPDATED_BY,
112 l_pdtv_rec.LAST_UPDATE_DATE,
113 l_pdtv_rec.LAST_UPDATE_LOGIN;
114 x_no_data_found := okl_pdtv_pk_csr%NOTFOUND;
115 CLOSE okl_pdtv_pk_csr;
116 x_pdtv_rec := l_pdtv_rec;
117 EXCEPTION
118 WHEN OTHERS THEN
119 -- store SQL error message on message stack
120 Okl_Api.SET_MESSAGE(p_app_name => G_APP_NAME,
121 p_msg_name => G_UNEXPECTED_ERROR,
122 p_token1 => G_SQLCODE_TOKEN,
123 p_token1_value => SQLCODE,
124 p_token2 => G_SQLERRM_TOKEN,
125 p_token2_value => SQLERRM);
126 -- notify UNEXPECTED error for calling API.
127 x_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
128
129 IF (okl_pdtv_pk_csr%ISOPEN) THEN
130 CLOSE okl_pdtv_pk_csr;
131 END IF;
132
133 END get_rec;
134
135 ---------------------------------------------------------------------------
136 -- PROCEDURE delete_pdt_pqy_vals for: OKL_PDT_PQY_VALS
137 -- Private procedure called from delete_pqvalues.
138 ---------------------------------------------------------------------------
139
140 PROCEDURE delete_pdt_pqy_vals(
141 p_api_version IN NUMBER
142 ,p_init_msg_list IN VARCHAR2 DEFAULT okl_api.G_FALSE
143 ,x_return_status OUT NOCOPY VARCHAR2
144 ,x_msg_count OUT NOCOPY NUMBER
145 ,x_msg_data OUT NOCOPY VARCHAR2
146 ,p_pdtv_rec IN pdtv_rec_type) IS
147 i PLS_INTEGER :=0;
148 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
149 l_del_pqvv_tbl OKL_PQY_VALUES_PUB.pqvv_tbl_type;
150
151 CURSOR pqv_csr IS
152 SELECT pqvv.id
153 FROM okl_pdt_pqy_vals_v pqvv
154 WHERE pqvv.pdt_id = p_pdtv_rec.id;
155
156 BEGIN
157
158 FOR pqv_rec IN pqv_csr
159 LOOP
160 i := i + 1;
161 l_del_pqvv_tbl(i).id := pqv_rec.id;
162 END LOOP;
163 IF l_del_pqvv_tbl.COUNT > 0 THEN
164 /* public api to delete product option values */
165 -- Start of wraper code generated automatically by Debug code generator for OKL_PQY_VALUES_PUB.delete_pqy_values
166 IF(L_DEBUG_ENABLED='Y') THEN
167 L_LEVEL_PROCEDURE :=FND_LOG.LEVEL_PROCEDURE;
168 IS_DEBUG_PROCEDURE_ON := OKL_DEBUG_PUB.Check_Log_On(L_MODULE, L_LEVEL_PROCEDURE);
169 END IF;
170 IF(IS_DEBUG_PROCEDURE_ON) THEN
171 BEGIN
172 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'Begin Debug OKLRSPDB.pls call OKL_PQY_VALUES_PUB.delete_pqy_values ');
173 END;
174 END IF;
175 OKL_PQY_VALUES_PUB.delete_pqy_values(p_api_version => p_api_version,
176 p_init_msg_list => p_init_msg_list,
177 x_return_status => l_return_status,
178 x_msg_count => x_msg_count,
179 x_msg_data => x_msg_data,
180 p_pqvv_tbl => l_del_pqvv_tbl);
181 IF(IS_DEBUG_PROCEDURE_ON) THEN
182 BEGIN
183 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'End Debug OKLRSPDB.pls call OKL_PQY_VALUES_PUB.delete_pqy_values ');
184 END;
185 END IF;
186
187 -- End of wraper code generated automatically by Debug code generator for OKL_PQY_VALUES_PUB.delete_pqy_values
188
189 IF x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR THEN
190 RAISE G_EXCEPTION_HALT_PROCESSING;
191 ELSE
192
193 IF x_return_status <> Okl_Api.G_RET_STS_SUCCESS THEN
194
195 l_return_status := x_return_status;
196
197 END IF;
198 END IF;
199 END IF;
200
201 EXCEPTION
202 WHEN G_EXCEPTION_HALT_PROCESSING THEN
203 NULL;
204 WHEN OTHERS THEN
205 Okl_Api.SET_MESSAGE(p_app_name => g_app_name
206 ,p_msg_name => g_unexpected_error
207 ,p_token1 => g_sqlcode_token
208 ,p_token1_value => SQLCODE
209 ,p_token2 => g_sqlerrm_token
210 ,p_token2_value => SQLERRM);
211
212 x_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
213 END delete_pdt_pqy_vals;
214
215 ---------------------------------------------------------------------------
216 -- PROCEDURE delete_pdt_psy_vals for: OKL_PDT_PQY_VALS
217 -- Private procedure called from delete_pqvalues.
218 ---------------------------------------------------------------------------
219
220 PROCEDURE delete_pdt_psys(
221 p_api_version IN NUMBER
222 ,p_init_msg_list IN VARCHAR2 DEFAULT okl_api.G_FALSE
223 ,x_return_status OUT NOCOPY VARCHAR2
224 ,x_msg_count OUT NOCOPY NUMBER
225 ,x_msg_data OUT NOCOPY VARCHAR2
226 ,p_pdtv_rec IN pdtv_rec_type) IS
227 i PLS_INTEGER :=0;
228 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
229 l_del_psyv_tbl Okl_Pdt_Stys_Pub.psyv_tbl_type;
230
231 CURSOR psy_csr IS
232 SELECT psyv.id
233 FROM okl_prod_strm_types_v psyv
234 WHERE psyv.pdt_id = p_pdtv_rec.id;
235
236 BEGIN
237
238 FOR psy_rec IN psy_csr
239 LOOP
240 i := i + 1;
241 l_del_psyv_tbl(i).id := psy_rec.id;
242 END LOOP;
243
244 IF l_del_psyv_tbl.COUNT > 0 THEN
245
246 Okl_Pdt_Stys_Pub.delete_pdt_stys(p_api_version => p_api_version,
247 p_init_msg_list => p_init_msg_list,
248 x_return_status => l_return_status,
249 x_msg_count => x_msg_count,
250 x_msg_data => x_msg_data,
251 p_psyv_tbl => l_del_psyv_tbl);
252
253
254 IF x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR THEN
255 RAISE G_EXCEPTION_HALT_PROCESSING;
256 ELSE
257 IF x_return_status <> Okl_Api.G_RET_STS_SUCCESS THEN
258 l_return_status := x_return_status;
259 END IF;
260 END IF;
261 END IF;
262
263 EXCEPTION
264 WHEN G_EXCEPTION_HALT_PROCESSING THEN
265 NULL;
266 WHEN OTHERS THEN
267 Okl_Api.SET_MESSAGE(p_app_name => g_app_name
268 ,p_msg_name => g_unexpected_error
269 ,p_token1 => g_sqlcode_token
270 ,p_token1_value => SQLCODE
271 ,p_token2 => g_sqlerrm_token
272 ,p_token2_value => SQLERRM);
273
274 x_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
275 END delete_pdt_psys;
276
277
278 ---------------------------------------------------------------------------
279 -- PROCEDURE check_constraints for: OKL_PRODUCTS_V
280 -- To verify whether the dates are valid in the following entities
281 -- 1. Quality Value
282 -- 2. Option
283 -- 3. Option Value
284 -- 4. Product Template
285 ---------------------------------------------------------------------------
286
287 PROCEDURE Check_Constraints (
288 p_api_version IN NUMBER,
289 p_init_msg_list IN VARCHAR2 DEFAULT okl_api.G_FALSE,
290 x_msg_count OUT NOCOPY NUMBER,
291 x_msg_data OUT NOCOPY VARCHAR2,
292 p_upd_pdtv_rec IN pdtv_rec_type,
293 p_pdtv_rec IN pdtv_rec_type,
294 p_db_pdtv_rec IN pdtv_rec_type,
295
296
297 x_return_status OUT NOCOPY VARCHAR2,
298
299 x_valid OUT NOCOPY BOOLEAN
300 ) IS
301 CURSOR okl_contracts_csr (p_pdt_id IN Okl_Products_V.ID%TYPE,
302 p_from_date IN Okl_Products_V.FROM_DATE%TYPE,
303 p_to_date IN Okl_Products_V.TO_DATE%TYPE
304 ) IS
305 SELECT '1'
306 FROM Okl_K_Headers_V khr,
307 Okl_K_Headers_Full_V khf
308 WHERE khr.PDT_ID = p_pdt_id
309 AND khf.ID = khr.ID
310 AND khf.START_DATE BETWEEN p_from_date AND p_to_date;
311
312
313 CURSOR okl_pdtv_chk(p_pdt_id NUMBER
314 ) IS
315 SELECT '1' FROM okl_k_headers_v khdr
316 WHERE khdr.pdt_id = p_pdt_id;
317
318 CURSOR okl_pdt_constraints_csr (p_pdt_id IN Okl_Products_V.ID%TYPE,
319 p_from_date IN Okl_Products_V.FROM_DATE%TYPE,
320 p_to_date IN Okl_Products_V.TO_DATE%TYPE
321 ) IS
322 SELECT '1'
323 FROM Okl_Pqy_Values_V qve,
324 Okl_Pdt_Pqy_Vals_V pqv
325 WHERE pqv.PDT_ID = p_pdt_id
326 AND qve.ID = pqv.QVE_ID
327 AND ((qve.FROM_DATE > p_from_date OR
328 p_from_date > NVL(qve.TO_DATE,p_from_date)) OR
329 NVL(qve.TO_DATE, p_to_date) < p_to_date)
330 UNION ALL
331 SELECT '2'
332 FROM Okl_Pdt_Opts_V pon,
333 Okl_Options_V opt
334 WHERE pon.PDT_ID = p_pdt_id
335 AND opt.ID = pon.OPT_ID
336 AND ((opt.FROM_DATE > p_from_date OR
337 p_from_date > NVL(opt.TO_DATE,p_from_date)) OR
338 NVL(opt.TO_DATE, p_to_date) < p_to_date)
339 UNION ALL
340 SELECT '3'
341 FROM Okl_Pdt_Opts_V pon,
342 Okl_Pdt_Opt_Vals_V pov,
343 Okl_Opt_Values_V ove
344 WHERE pon.PDT_ID = p_pdt_id
345 AND pov.PON_ID = pon.ID
346 AND ove.ID = pov.OVE_ID
347 AND ((ove.FROM_DATE > p_from_date OR
348 p_from_date > NVL(ove.TO_DATE,p_from_date)) OR
349 NVL(ove.TO_DATE, p_to_date) < p_to_date);
350 /* UNION ALL
351 SELECT '4'
352 FROM Okl_Strm_Type_b sty,
353 Okl_Prod_Strm_Types_v psy
354 WHERE psy.PDT_ID = p_pdt_id
355 AND psy.sty_id = sty.ID
356 AND ((sty.START_DATE > p_from_date OR
357 p_from_date > NVL(sty.END_DATE,p_from_date)) OR
358 NVL(sty.END_DATE, p_to_date) < p_to_date)
359 */
360
361
362 CURSOR okl_pdt_aes_ptl_csr (p_aes_id IN Okl_Products_V.AES_ID%TYPE,
363 p_ptl_id IN Okl_Products_V.PTL_ID%TYPE,
364 p_from_date IN Okl_Products_V.FROM_DATE%TYPE,
365 p_to_date IN Okl_Products_V.TO_DATE%TYPE
366 ) IS
367 SELECT '1'
368 FROM Okl_Ae_Tmpt_Sets_V aes
369 WHERE aes.ID = p_aes_id
370 AND ((aes.START_DATE > p_from_date OR
371 p_from_date > NVL(aes.END_DATE,p_from_date)) OR
372 NVL(aes.END_DATE, p_to_date) < p_to_date)
373 UNION ALL
374 SELECT '2'
375 FROM Okl_Pdt_Templates_V PTL
376 WHERE PTL.ID = p_ptl_id
377 AND ((PTL.FROM_DATE > p_from_date OR
378 p_from_date > NVL(PTL.TO_DATE,p_from_date)) OR
379 NVL(PTL.TO_DATE, p_to_date) < p_to_date);
380
381 CURSOR c1(p_name okl_products_v.name%TYPE,
382 p_version okl_products_v.version%TYPE) IS
383 SELECT '1'
384 FROM okl_products_v
385 WHERE name = p_name
386
387 AND version = p_version;
388
389 CURSOR choose_qlty_csr(cp_ptl_id okl_pdt_templates_v.id%TYPE) IS
390 SELECT DISTINCT pqy.id id,
391 pqy.name name
392 FROM okl_pdt_qualitys_v pqy,
393 okl_pdt_pqys_v pdq
394 WHERE pqy.id = pdq.pqy_id
395 AND pdq.ptl_id = cp_ptl_id
396
397 AND pqy.name IN ('LEASE','INVESTOR','TAXOWNER');
398
399 l_pdt_status VARCHAR2(1);
400 l_chk_bc VARCHAR2(100);
401 l_chk_aes_bc VARCHAR2(100);
402 l_chk_ptl_bc VARCHAR2(100);
403 l_token_1 VARCHAR2(1999);
404 l_token_2 VARCHAR2(1999);
405 l_token_3 VARCHAR2(1999);
406 l_token_4 VARCHAR2(1999);
407 l_token_5 VARCHAR2(1999);
408 l_token_6 VARCHAR2(1999);
409 l_token_7 VARCHAR2(1999);
410 l_token_8 VARCHAR2(1999);
411 l_token_9 VARCHAR2(1999);
412
413 l_token_10 VARCHAR2(1999);
414
415 csr_rec choose_qlty_csr%ROWTYPE;
416 l_quality_miss_cnt NUMBER(4):=0;
417 l_pdt_ptl_changed NUMBER(4):=0;
418 l_pdt_psy_changed NUMBER(4):=0;
419 l_lease_values_miss_cnt NUMBER(4):=0;
420 l_taxown_values_miss_cnt NUMBER(4):=0;
421 l_sec_values_miss_cnt NUMBER(4):=0;
422 l_invalid_product_cnt NUMBER(4):=0;
423 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
424 l_row_found BOOLEAN := FALSE;
425 l_check VARCHAR2(1) := '?';
426 l_row_not_found BOOLEAN := FALSE;
427 l_name okl_products_v.name%TYPE;
428 l_to_date okl_products_v.TO_DATE%TYPE;
429 BEGIN
430 x_valid := TRUE;
431 x_return_status := Okl_Api.G_RET_STS_SUCCESS;
432
433 l_token_1 := Okl_Accounting_Util.Get_Message_Token(p_region_code => 'OKL_LP_PRODUCT_SERCH',
434 p_attribute_code => 'OKL_PRODUCTS');
435
436 l_token_2 := Okl_Accounting_Util.Get_Message_Token(p_region_code => 'OKL_LP_PRODUCT_TEMPLATE_SERCH',
437 p_attribute_code => 'OKL_PRODUCT_TEMPLATES');
438
439 l_token_3 := Okl_Accounting_Util.Get_Message_Token(p_region_code => 'OKL_LP_TEMPLATE_SETS',
440 p_attribute_code => 'OKL_TEMPLATE_SETS');
441 l_token_4 := l_token_2 ||','||l_token_3;
442
443 l_token_5 := Okl_Accounting_Util.Get_Message_Token(p_region_code => 'OKL_LP_PQVALS_CRUPD',
444 p_attribute_code => 'OKL_PRODUCT_QUALITY_VALUES');
445
446 l_token_6 := Okl_Accounting_Util.Get_Message_Token(p_region_code => 'OKL_LP_OPTION_SERCH',
447 p_attribute_code => 'OKL_OPTIONS');
448
449 l_token_7 := Okl_Accounting_Util.Get_Message_Token(p_region_code => 'OKL_LP_OPTVAL_SERCH',
450 p_attribute_code => 'OKL_OPTION_VALUES');
451
452 l_token_8 := l_token_5 ||','|| l_token_6 || ',' || l_token_7;
453
454 l_token_9 := Okl_Accounting_Util.Get_Message_Token(p_region_code => 'OKL_CONTRACT_DTLS',
455 p_attribute_code => 'OKL_KDTLS_CONTRACT');
456
457
458 l_token_10 := Okl_Accounting_Util.Get_Message_Token(p_region_code => 'OKL_LP_PRODUCT_CRUPD',
459 p_attribute_code => 'OKL_PRODUCT');
460
461 -- Halt the processing if the Product is active(used by a contract).
462 IF p_pdtv_rec.id <> Okl_Api.G_MISS_NUM THEN
463 OPEN okl_pdtv_chk(p_upd_pdtv_rec.id);
464
465
466 FETCH okl_pdtv_chk INTO l_check;
467 l_row_not_found := okl_pdtv_chk%NOTFOUND;
468 CLOSE okl_pdtv_chk;
469
470 IF l_row_not_found = FALSE THEN
471
472 OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
473 p_msg_name => G_IN_USE,
474 p_token1 => G_PARENT_TABLE_TOKEN,
475 p_token1_value => l_token_10,
476 p_token2 => G_CHILD_TABLE_TOKEN,
477 p_token2_value => l_token_9);
478
479
480 x_return_status := OKL_API.G_RET_STS_ERROR;
481 RAISE G_EXCEPTION_HALT_PROCESSING;
482 END IF;
483 END IF;
484
485
486
487 -- Fix for g_miss_date
488 IF p_pdtv_rec.TO_DATE = Okl_Api.G_MISS_DATE THEN
489 l_to_date := NULL;
490 ELSE
491 l_to_date := p_pdtv_rec.TO_DATE;
492 END IF;
493
494 IF p_pdtv_rec.id = Okl_Api.G_MISS_NUM THEN
495 l_name := Okl_Accounting_Util.okl_upper(p_pdtv_rec.name);
496
497 OPEN c1(l_name,
498 p_pdtv_rec.version);
499 FETCH c1 INTO l_pdt_status;
500 l_row_found := c1%FOUND;
501 CLOSE c1;
502 IF l_row_found THEN
503
504
505
506 Okl_Api.set_message('OKL',G_UNQS, G_TABLE_TOKEN, l_token_1);
507 x_return_status := Okl_Api.G_RET_STS_ERROR;
508 x_valid := FALSE;
509 RAISE G_EXCEPTION_HALT_PROCESSING;
510 END IF;
511 END IF;
512
513
514 IF p_pdtv_rec.id <> Okl_Api.G_MISS_NUM AND
515 p_db_pdtv_rec.aes_id <> p_pdtv_rec.aes_id THEN
516 SELECT COUNT(aes.id)
517 INTO l_pdt_psy_changed
518 FROM
519 okl_ae_tmpt_sets_v aes,
520 okl_prod_strm_types_v psy
521 WHERE aes.id = p_db_pdtv_rec.aes_id
522 and psy.pdt_id = p_upd_pdtv_rec.id;
523
524 -- check to see if the product already has a streams attached to it , if yes delete the
525 -- old child records.
526 IF l_pdt_psy_changed > 0 THEN
527 delete_pdt_psys(p_api_version => p_api_version,
528 p_init_msg_list => p_init_msg_list,
529 x_return_status => l_return_status,
530 x_msg_count => x_msg_count,
531 x_msg_data => x_msg_data,
532 p_pdtv_rec => p_pdtv_rec);
533
534 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
535
536
537 x_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
538 RAISE G_EXCEPTION_HALT_PROCESSING;
539 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR)THEN
540
541
542 x_return_status := Okl_Api.G_RET_STS_ERROR;
543 RAISE G_EXCEPTION_HALT_PROCESSING;
544
545 END IF;
546
547 END IF;
548 END IF;
549
550 /*===============================
551 -- multi gaap validations BEGIN
552 =================================*/
553
554 BEGIN
555 -- CHECK TO DISALLOW REPORTING PRODUCT FOR LOCAL SECURITIZATION PRODUCT
556 SELECT COUNT(pqy.id)
557 INTO l_invalid_product_cnt
558 FROM
559 okl_pdt_qualitys_v pqy,
560 okl_pdt_pqys_v pdq
561 where pqy.id = pdq.pqy_id
562 AND pdq.ptl_id = p_pdtv_rec.ptl_id
563 AND pqy.name = 'INVESTOR';
564
565 IF l_invalid_product_cnt > 0 AND (p_upd_pdtv_rec.reporting_pdt_id IS NOT NULL AND
566 p_upd_pdtv_rec.reporting_pdt_id <> OKL_API.G_MISS_NUM) THEN
567
568
569 Okl_Api.SET_MESSAGE(p_app_name => G_APP_NAME,
570 p_msg_name => G_INVALID_PDT);
571 x_valid := FALSE;
572 x_return_status := Okl_Api.G_RET_STS_ERROR;
573 RAISE G_EXCEPTION_HALT_PROCESSING;
574 END IF;
575
576 -- CHECK TO verify if atlease one quality LEASE BOOK CLASSIFICATION /TAX OWNER oR
577 -- INVESTOR AGREEMENT CLASSIFICATION is defined for a product.
578 SELECT COUNT(pqy.id)
579 INTO l_quality_miss_cnt
580 FROM
581 okl_pdt_qualitys_v pqy,
582 okl_pdt_pqys_v pdq
583 WHERE pqy.id = pdq.pqy_id
584 AND pdq.ptl_id = p_pdtv_rec.ptl_id
585 AND pqy.name IN ('LEASE','INVESTOR','TAXOWNER');
586
587
588 IF l_quality_miss_cnt = 0 THEN
589
590
591 Okl_Api.SET_MESSAGE(p_app_name => G_APP_NAME,
592 p_msg_name => G_LEASE_SEC_TAXOWN_MISS);
593 x_valid := FALSE;
594 x_return_status := Okl_Api.G_RET_STS_ERROR;
595 RAISE G_EXCEPTION_HALT_PROCESSING;
596 ELSE
597 -- CHECK TO verify if all the quality attached to the product have values defined
598 FOR csr_rec in choose_qlty_csr(p_pdtv_rec.ptl_id)
599 LOOP
600
601 IF p_pdtv_rec.id <> Okl_Api.G_MISS_NUM AND
602 p_db_pdtv_rec.ptl_id <> p_pdtv_rec.ptl_id THEN
603 SELECT COUNT(pqv.id)
604
605 INTO l_pdt_ptl_changed
606 FROM okl_pqy_values_v qve,
607 okl_pdt_qualitys pqy,
608 okl_pdt_pqys_v pdq,
609 okl_pdt_pqy_vals pqv
610 WHERE qve.pqy_id = pqy.id
611 AND pqv.qve_id = qve.id
612 AND pqv.pdq_id = pdq.id
613 AND pdq.ptl_id = p_db_pdtv_rec.ptl_id
614 AND pqv.pdt_id = p_upd_pdtv_rec.id;
615 -- check to see if the product already has a product template attached to it , if yes delete the
616 -- old child records.
617 IF l_pdt_ptl_changed > 0 THEN
618 delete_pdt_pqy_vals(p_api_version => p_api_version,
619 p_init_msg_list => p_init_msg_list,
620 x_return_status => l_return_status,
621 x_msg_count => x_msg_count,
622 x_msg_data => x_msg_data,
623 p_pdtv_rec => p_pdtv_rec);
624
625 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
626 x_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
627 RAISE G_EXCEPTION_HALT_PROCESSING;
628 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR)THEN
629 x_return_status := Okl_Api.G_RET_STS_ERROR;
630 RAISE G_EXCEPTION_HALT_PROCESSING;
631 END IF;
632
633 END IF;
634 END IF;
635
636 IF csr_rec.name = 'LEASE' THEN
637
638 SELECT COUNT(qve.id)
639 INTO l_lease_values_miss_cnt
640 FROM okl_pqy_values_v qve
641 WHERE qve.pqy_id = csr_rec.id
642 AND qve.value IN ('LEASEDF','LEASEOP','LEASEST','LOAN','LOAN-REVOLVING');
643
644 IF l_lease_values_miss_cnt <> 5 THEN
645
646
647
648 Okl_Api.SET_MESSAGE(p_app_name => G_APP_NAME,
649 p_msg_name => G_LEASE_VALUES_MISS);
650 x_valid := FALSE;
651 x_return_status := Okl_Api.G_RET_STS_ERROR;
652 RAISE G_EXCEPTION_HALT_PROCESSING;
653 END IF;
654
655 END IF;
656
657 IF csr_rec.name = 'TAXOWNER' THEN
658 SELECT COUNT(qve.id)
659 INTO l_taxown_values_miss_cnt
660 FROM okl_pqy_values_v qve
661 WHERE qve.pqy_id = csr_rec.id
662 AND qve.value IN ('LESSEE','LESSOR');
663
664 IF l_taxown_values_miss_cnt <> 2 THEN
665
666
667 Okl_Api.SET_MESSAGE(p_app_name => G_APP_NAME,
668 p_msg_name => G_TAXOWN_VALUES_MISS);
669 x_valid := FALSE;
670 x_return_status := Okl_Api.G_RET_STS_ERROR;
671 RAISE G_EXCEPTION_HALT_PROCESSING;
672 END IF;
673 END IF;
674
675 IF csr_rec.name = 'INVESTOR' THEN
676
677 SELECT COUNT(qve.id)
678 INTO l_sec_values_miss_cnt
679 FROM
680 okl_pqy_values_v qve
681 WHERE qve.pqy_id = csr_rec.id
682 AND qve.value IN ('SECURITIZATION','SYNDICATION');
683
684 IF l_sec_values_miss_cnt <> 2 THEN
685
686
687
688 Okl_Api.SET_MESSAGE(p_app_name => G_APP_NAME,
689 p_msg_name => G_SEC_VALUES_MISS);
690 x_valid := FALSE;
691 x_return_status := Okl_Api.G_RET_STS_ERROR;
692 RAISE G_EXCEPTION_HALT_PROCESSING;
693 END IF;
694 END IF;
695 END LOOP;
696 END IF;
697
698 END;
699
700 /*===============================
701 -- multi gaap validations END
702 =================================*/
703
704 -- Check for contract dates
705
706 IF p_pdtv_rec.id <> Okl_Api.G_MISS_NUM THEN
707 OPEN okl_contracts_csr (p_pdtv_rec.id,
708 p_pdtv_rec.from_date,
709 l_to_date);
710 FETCH okl_contracts_csr INTO l_check;
711 l_row_not_found := okl_contracts_csr%NOTFOUND;
712 CLOSE okl_contracts_csr;
713
714 IF l_row_not_found = FALSE THEN
715
716
717 Okl_Api.SET_MESSAGE(p_app_name => G_APP_NAME,
718 p_msg_name => G_DATES_MISMATCH,
719 p_token1 => G_PARENT_TABLE_TOKEN,
720 p_token1_value => l_token_9,
721 p_token2 => G_CHILD_TABLE_TOKEN,
722 p_token2_value => l_token_1);
723 x_valid := FALSE;
724 x_return_status := Okl_Api.G_RET_STS_ERROR;
725 RAISE G_EXCEPTION_HALT_PROCESSING;
726 END IF;
727 END IF;
728
729 -- Check for constraints dates in the case of update only
730 IF p_upd_pdtv_rec.id <> Okl_Api.G_MISS_NUM THEN
731 OPEN okl_pdt_constraints_csr (p_upd_pdtv_rec.id,
732 p_pdtv_rec.from_date,
733 l_to_date);
734 FETCH okl_pdt_constraints_csr INTO l_check;
735 l_row_not_found := okl_pdt_constraints_csr%NOTFOUND;
736 CLOSE okl_pdt_constraints_csr;
737
738
739 IF l_row_not_found = FALSE THEN
740
741
742 Okl_Api.SET_MESSAGE(p_app_name => G_APP_NAME,
743 p_msg_name => G_DATES_MISMATCH,
744 p_token1 => G_PARENT_TABLE_TOKEN,
745 p_token1_value => l_token_8,
746 p_token2 => G_CHILD_TABLE_TOKEN,
747 p_token2_value => l_token_1);
748
749 x_valid := FALSE;
750 x_return_status := Okl_Api.G_RET_STS_ERROR;
751 RAISE G_EXCEPTION_HALT_PROCESSING;
752 END IF;
753
754
755 END IF;
756
757 /* check the aes_id and ptl_id specified is valid */
758 OPEN okl_pdt_aes_ptl_csr (p_pdtv_rec.aes_id,
759 p_pdtv_rec.ptl_id,
760 p_pdtv_rec.from_date,
761 l_to_date);
762 FETCH okl_pdt_aes_ptl_csr INTO l_check;
763 l_row_not_found := okl_pdt_aes_ptl_csr%NOTFOUND;
764 CLOSE okl_pdt_aes_ptl_csr;
765
766 IF l_row_not_found = FALSE THEN
767
768 Okl_Api.SET_MESSAGE(p_app_name => G_APP_NAME,
769 p_msg_name => G_DATES_MISMATCH,
770 p_token1 => G_PARENT_TABLE_TOKEN,
771 p_token1_value => l_token_4,
772 p_token2 => G_CHILD_TABLE_TOKEN,
773 p_token2_value => l_token_1);
774 x_valid := FALSE;
775 x_return_status := Okl_Api.G_RET_STS_ERROR;
776 RAISE G_EXCEPTION_HALT_PROCESSING;
777 END IF;
778
779
780
781 EXCEPTION
782 WHEN G_EXCEPTION_HALT_PROCESSING THEN
783
784
785 -- no processing necessary; validation can continue
786
787 -- with the next column
788 NULL;
789 WHEN OTHERS THEN
790 -- store SQL error message on message stack
791 Okl_Api.SET_MESSAGE(p_app_name => G_APP_NAME,
792 p_msg_name => G_UNEXPECTED_ERROR,
793 p_token1 => G_SQLCODE_TOKEN,
794 p_token1_value => SQLCODE,
795 p_token2 => G_SQLERRM_TOKEN,
796 p_token2_value => SQLERRM);
797 x_valid := FALSE;
798 x_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
799
800 IF (okl_contracts_csr%ISOPEN) THEN
801 CLOSE okl_contracts_csr;
802 END IF;
803
804 IF (okl_pdt_constraints_csr%ISOPEN) THEN
805 CLOSE okl_pdt_constraints_csr;
806
807 END IF;
808
809 IF (okl_pdt_aes_ptl_csr%ISOPEN) THEN
810 CLOSE okl_pdt_aes_ptl_csr;
811 END IF;
812
813 IF (c1%ISOPEN) THEN
814 CLOSE c1;
815 END IF;
816
817 IF (okl_pdtv_chk%ISOPEN) THEN
818 CLOSE okl_pdtv_chk;
819 END IF;
820
821 END Check_Constraints;
822
823 ---------------------------------------------------------------------------
824 -- PROCEDURE Getpdt_parameters
825 ---------------------------------------------------------------------------
826 -- Start of comments
827 --
828 -- Procedure Name : Getpdt_parameters
829 -- Description :
830 -- Business Rules :
831 -- Parameters :
832 -- Version : 1.0
833 -- End of comments
834 ---------------------------------------------------------------------------
835 PROCEDURE Getpdt_parameters(
836 p_api_version IN NUMBER,
837 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
838 x_return_status OUT NOCOPY VARCHAR2,
839 x_no_data_found OUT NOCOPY BOOLEAN,
840 x_msg_count OUT NOCOPY NUMBER,
841 x_msg_data OUT NOCOPY VARCHAR2,
842 p_pdtv_rec IN pdtv_rec_type,
843 p_product_date IN DATE DEFAULT SYSDATE,
844 p_pdt_parameter_rec OUT NOCOPY pdt_parameters_rec_type
845 )
846 IS
847 CURSOR okl_pdt_parameters_cur(cp_pdt_id IN okl_products_v.id%TYPE)
848 IS
849 SELECT
850 Name,
851 Product_subclass,
852 Deal_Type,
853 Tax_Owner,
854 Revenue_Recognition_Method,
855 Interest_Calculation_Basis,
856 reporting_pdt_id,
857 reporting_product
858 FROM okl_product_parameters_v
859 WHERE id = cp_pdt_id;
860
861 l_no_data_found BOOLEAN;
862 l_pdt_parameters_rec pdt_parameters_rec_type;
863
864 BEGIN
865
866 -- initialize return status
867 x_return_status := Okl_Api.G_RET_STS_SUCCESS;
868 x_no_data_found := TRUE;
869
870 -- Get current database values
871 OPEN okl_pdt_parameters_cur (p_pdtv_rec.id);
872 FETCH okl_pdt_parameters_cur INTO
873 l_pdt_parameters_rec.name,
874 l_pdt_parameters_rec.product_subclass,
875 l_pdt_parameters_rec.deal_type,
876 l_pdt_parameters_rec.tax_owner,
877 l_pdt_parameters_rec.Revenue_Recognition_Method,
878 l_pdt_parameters_rec.Interest_Calculation_Basis,
879 l_pdt_parameters_rec.reporting_pdt_id,
880 l_pdt_parameters_rec.reporting_product;
881 x_no_data_found := okl_pdt_parameters_cur%NOTFOUND;
882 CLOSE okl_pdt_parameters_cur;
883
884 IF x_no_data_found = TRUE THEN
885 Okl_Api.SET_MESSAGE(p_app_name => G_APP_NAME,
886
887 p_msg_name => G_PRODUCT_SETUP_INCOMPLETE);
888 x_return_status := Okl_Api.G_RET_STS_ERROR;
889 END IF;
890
891 p_pdt_parameter_rec := l_pdt_parameters_rec;
892
893 EXCEPTION
894 WHEN OTHERS THEN
895 -- store SQL error message on message stack for caller
896 Okl_Api.SET_MESSAGE(p_app_name => Okl_Pdt_Pvt.g_app_name,
897 p_msg_name => Okl_Pdt_Pvt.g_unexpected_error,
898 p_token1 => Okl_Pdt_Pvt.g_sqlcode_token,
899 p_token1_value => SQLCODE,
900 p_token2 => Okl_Pdt_Pvt.g_sqlerrm_token,
901 p_token2_value => SQLERRM);
902
903 -- notify caller of an UNEXPECTED error
904 x_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
905
906 IF (okl_pdt_parameters_cur%ISOPEN) THEN
907 CLOSE okl_pdt_parameters_cur;
908 END IF;
909
910 END Getpdt_parameters;
911
912
913 ---------------------------------------------------------------------------
914 -- FUNCTION exist_subscription
915 ---------------------------------------------------------------------------
916 -- Start of comments
917 --
918 -- Procedure Name : exist_subscription
919 -- Description : Return 'Y' if there are some active subscription for
920 -- the given event Otherwise it returns 'N'
921 -- Business Rules :
922 -- Parameters :
923 -- Version : 1.0
924 -- End of comments
925 ---------------------------------------------------------------------------
926
927 FUNCTION exist_subscription(p_event_name IN VARCHAR2) RETURN VARCHAR2
928 IS
929 CURSOR exist_subscription IS
930 SELECT 'Y'
931 FROM wf_event_subscriptions a,
932 wf_events b
933 WHERE a.event_filter_guid = b.guid
934 AND a.status = 'ENABLED'
935 AND b.name = p_event_name
936 AND ROWNUM = 1;
937 l_yn VARCHAR2(1);
938 BEGIN
939 OPEN exist_subscription;
940 FETCH exist_subscription INTO l_yn;
941 IF exist_subscription%NOTFOUND THEN
942 l_yn := 'N';
943 END IF;
944 CLOSE exist_subscription;
945 RETURN l_yn;
946 END;
947
948
949 ---------------------------------------------------------------------------
950 -- product_approval_process
951 ---------------------------------------------------------------------------
952 -- Start of comments
953 --
954
955 -- Procedure Name : product_approval_process
956 -- Description : procedure to submit product for approval
957 -- the given event Otherwise it returns 'N'
958 -- Business Rules :
959 -- Parameters :
960 -- Version : 1.0
961 -- End of comments
962 ---------------------------------------------------------------------------
963
964 PROCEDURE product_approval_process
965 ( p_api_version IN NUMBER,
966 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
967 x_return_status OUT NOCOPY VARCHAR2,
968 x_msg_count OUT NOCOPY NUMBER,
969 x_msg_data OUT NOCOPY VARCHAR2,
970 p_pdtv_rec IN pdtv_rec_type)
971 IS
972 l_parameter_list wf_parameter_list_t;
973 l_key VARCHAR2(240);
974 l_found VARCHAR2(1);
975 l_event_name VARCHAR2(240) := 'oracle.apps.okl.llap.productapprovalprocess';
976
977 -- Selects the nextval from sequence, used later for defining event key
978 CURSOR okl_key_csr IS
979 SELECT okl_wf_item_s.NEXTVAL
980 FROM dual;
981
982 -- Get product Details
983 CURSOR c_fetch_pdt_dtls(p_pdt_id OKl_products_V.ID%TYPE)
984 IS
985 SELECT pdt.product_status_code status
986
987 FROM okl_products_v pdt
988 WHERE pdt.id = p_pdt_id;
989
990 -- modification by dcshanmu for bug 5999276 ends
991 -- Get OU id for the ATS
992 CURSOR c_get_ou_for_aes_id_csr(p_aes_id OKl_products_V.AES_ID%TYPE)
993 IS
994 SELECT ORG_ID
995 FROM OKL_AE_TMPT_SETS_ALL aes
996 WHERE aes.ID = p_aes_id;
997
998 l_org_id NUMBER;
999 -- modification by dcshanmu for bug 5999276 ends
1000
1001
1002 l_seq NUMBER ;
1003 l_status OKL_PRODUCTS_V.product_status_code%TYPE;
1004 l_pdt_id OKL_PRODUCTS_V.ID%TYPE;
1005 l_return_status VARCHAR2(3) := OKL_API.G_RET_STS_SUCCESS;
1006 l_api_version NUMBER := 1.0;
1007 l_init_msg_list VARCHAR2(10) := OKL_API.G_FALSE;
1008 l_api_name CONSTANT VARCHAR2(30) := 'product_approval_process';
1009 l_no_data_found BOOLEAN := TRUE;
1010 l_pdtv_rec pdtv_rec_type;
1011 x_pdtv_rec pdtv_rec_type;
1012 l_upd_pdtv_rec pdtv_rec_type;
1013
1014 BEGIN
1015
1016 -- SAVEPOINT product_approval_process;
1017 -- initialize return status
1018 x_return_status := Okl_Api.G_RET_STS_SUCCESS;
1019
1020 l_return_status := Okl_Api.START_ACTIVITY(p_api_name => l_api_name,
1021 p_pkg_name => G_PKG_NAME,
1022 p_init_msg_list => p_init_msg_list,
1023 l_api_version => l_api_version,
1024
1025 p_api_version => p_api_version,
1026 p_api_type => '_PVT',
1027 x_return_status => l_return_status);
1028 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
1029 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
1030 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
1031 RAISE Okl_Api.G_EXCEPTION_ERROR;
1032 END IF;
1033
1034 l_UPD_pdtv_rec.id := p_pdtv_rec.id;
1035
1036 -- Get the product name and status
1037 OPEN c_fetch_pdt_dtls(p_pdtv_rec.id);
1038 FETCH c_fetch_pdt_dtls INTO l_status;
1039
1040 IF c_fetch_pdt_dtls%NOTFOUND THEN
1041 OKL_API.set_message(p_app_name => G_APP_NAME,
1042 p_msg_name => G_NO_MATCHING_RECORD,
1043 p_token1 => G_COL_NAME_TOKEN,
1044 p_token1_value => 'OKC_PRODUCTS_V.ID');
1045 RAISE G_EXCEPTION_HALT_PROCESSING;
1046 END IF;
1047 CLOSE c_fetch_pdt_dtls;
1048
1049 IF l_status = 'PENDING APPROVAL' THEN
1050 --This product has been already submitted for approval.
1051 OKL_API.set_message(p_app_name => G_APP_NAME,
1052 p_msg_name => G_PDT_IN_PEND_APPROVAL);
1053 RAISE OKL_API.G_EXCEPTION_ERROR;
1054 ELSIF l_status = 'APPROVED' THEN
1055 --This product has been approved.
1056 OKL_API.set_message(p_app_name => G_APP_NAME,
1057 p_msg_name => G_PDT_APPROVED);
1058 RAISE OKL_API.G_EXCEPTION_ERROR;
1059 ELSIF l_status = 'PASSED' THEN
1060 -- normal processing
1061 NULL;
1062 ELSE
1063 OKL_API.set_message(p_app_name => G_APP_NAME,
1064 p_msg_name => G_PDT_NOT_VALIDATED);
1065 --RAISE G_EXCEPTION_HALT_PROCESSING;
1066 RAISE OKL_API.G_EXCEPTION_ERROR;
1067 END IF;
1068
1069 -- Test if there are any active subscritions
1070 -- if it is the case then execute the subscriptions
1071 l_found := exist_subscription(l_event_name);
1072 IF l_found = 'Y' THEN
1073 --Get the item key
1074 OPEN okl_key_csr;
1075 FETCH okl_key_csr INTO l_seq;
1076 CLOSE okl_key_csr;
1077
1078 l_key := l_event_name ||l_seq ;
1079
1080 -- modification by dcshanmu for bug 5999276 starts
1081 -- Get the operating unit id for the ATS
1082 OPEN c_get_ou_for_aes_id_csr(p_pdtv_rec.aes_id);
1083 FETCH c_get_ou_for_aes_id_csr INTO l_org_id;
1084
1085 CLOSE c_get_ou_for_aes_id_csr;
1086
1087 IF l_org_id IS NULL THEN
1088 OKL_API.set_message(p_app_name => G_APP_NAME,
1089 p_msg_name => G_NO_MATCHING_RECORD,
1090 p_token1 => G_COL_NAME_TOKEN,
1091 p_token1_value => 'OKC_PRODUCTS_V.AES_ID');
1092 RAISE G_EXCEPTION_HALT_PROCESSING;
1093 END IF;
1094 -- modification by dcshanmu for bug 5999276 ends
1095
1096 --Set Parameters
1097 wf_event.AddParameterToList('TRANSACTION_ID',TO_CHAR(p_pdtv_rec.ID),l_parameter_list);
1098 --added by akrangan
1099 -- modified by dcshanmu for bug 5999276 starts
1100 wf_event.AddParameterToList('ORG_ID',l_org_id ,l_parameter_list);
1101 -- We need to status to Approved Pending since We are sending for approval
1102
1103 update_product_status(p_api_version => l_api_version,
1104 p_init_msg_list => l_init_msg_list,
1105 x_return_status => l_return_status,
1106 x_msg_count => x_msg_count,
1107 x_msg_data => x_msg_data,
1108 p_pdt_status => G_PDT_STS_PENDING_APPROVAL,
1109 p_pdt_id => p_pdtv_rec.ID);
1110
1111
1112 IF x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR THEN
1113
1114 RAISE G_EXCEPTION_HALT_PROCESSING;
1115 ELSE
1116 IF x_return_status <> Okl_Api.G_RET_STS_SUCCESS THEN
1117 l_return_status := x_return_status;
1118 END IF;
1119 END IF;
1120
1121 -- Raise Event
1122 -- It is overloaded function so use according to requirement
1123
1124 wf_event.RAISE( p_event_name => l_event_name
1125 ,p_event_key => l_key
1126 ,p_parameters => l_parameter_list);
1127
1128 l_parameter_list.DELETE;
1129 ELSE
1130 FND_MESSAGE.SET_NAME('OKL', 'OKL_NO_EVENT');
1131 FND_MSG_PUB.ADD;
1132 x_return_status := OKL_API.G_RET_STS_ERROR ;
1133 END IF;
1134
1135 Okl_Api.END_ACTIVITY(x_msg_count => x_msg_count,
1136 x_msg_data => x_msg_data);
1137
1138 EXCEPTION
1139 WHEN G_EXCEPTION_HALT_PROCESSING THEN
1140
1141 -- no processing necessary; validation can continue
1142 -- with the next column
1143 NULL;
1144 WHEN Okl_Api.G_EXCEPTION_ERROR THEN
1145 x_return_status := Okl_Api.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
1146 p_pkg_name => G_PKG_NAME,
1147 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
1148 x_msg_count => x_msg_count,
1149 x_msg_data => x_msg_data,
1150 p_api_type => '_PVT');
1151
1152 IF c_fetch_pdt_dtls%ISOPEN THEN
1153 CLOSE c_fetch_pdt_dtls;
1154 END IF;
1155
1156 IF okl_key_csr%ISOPEN THEN
1157 CLOSE okl_key_csr;
1158 END IF;
1159 WHEN OTHERS THEN
1160 --ROLLBACK TO product_approval_process;
1161 -- store SQL error message on message stack for caller
1162 Okl_Api.SET_MESSAGE(p_app_name => Okl_Pdt_Pvt.g_app_name,
1163 p_msg_name => Okl_Pdt_Pvt.g_unexpected_error,
1164 p_token1 => Okl_Pdt_Pvt.g_sqlcode_token,
1165 p_token1_value => SQLCODE,
1166 p_token2 => Okl_Pdt_Pvt.g_sqlerrm_token,
1167 p_token2_value => SQLERRM);
1168
1169 -- notify caller of an UNEXPECTED error
1170 x_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
1171
1172
1173
1174 IF okl_key_csr%ISOPEN THEN
1175 CLOSE okl_key_csr;
1176 END IF;
1177
1178
1179 IF c_fetch_pdt_dtls%ISOPEN THEN
1180 CLOSE c_fetch_pdt_dtls;
1181 END IF;
1182
1183 END product_approval_process;
1184
1185 -----------------------------------------------------------------------------
1186 -- PROCEDURE get_agent
1187 -----------------------------------------------------------------------------
1188 -- Start of comments
1189 --
1190 -- Procedure Name : get_agent
1191 -- Description :
1192 -- Business Rules :
1193 -- Parameters :
1194 -- Version : 1.0
1195 -- End of comments
1196 ---------------------------------------------------------------------------
1197
1198 PROCEDURE get_agent(p_user_id IN NUMBER,
1199 x_return_status OUT NOCOPY VARCHAR2,
1200 x_name OUT NOCOPY VARCHAR2,
1201 x_description OUT NOCOPY VARCHAR2) IS
1202
1203 CURSOR wf_users_csr(c_user_id NUMBER)
1204 IS
1205 SELECT NAME, DISPLAY_NAME
1206 FROM WF_USERS
1207 WHERE orig_system_id = c_user_id
1208 AND ORIG_SYSTEM = G_WF_USER_ORIG_SYSTEM_HR;
1209
1210 CURSOR fnd_users_csr(c_user_id NUMBER)
1211 IS
1212 SELECT USER_NAME, DESCRIPTION
1213
1214 FROM FND_USER
1215 WHERE user_id = c_user_id;
1216 BEGIN
1217 x_return_status := OKL_API.G_RET_STS_SUCCESS;
1218 OPEN wf_users_csr(p_user_id);
1219 FETCH wf_users_csr INTO x_name, x_description;
1220 CLOSE wf_users_csr;
1221 IF x_name IS NULL THEN
1222 OPEN fnd_users_csr(p_user_id);
1223 FETCH fnd_users_csr INTO x_name, x_description;
1224 CLOSE fnd_users_csr;
1225 IF x_name IS NULL THEN
1226 x_name := G_DEFAULT_USER_DESC;
1227 x_description := G_DEFAULT_USER_DESC;
1228 END IF;
1229 END IF;
1230 EXCEPTION
1231 WHEN OTHERS THEN
1232 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
1233 END get_agent;
1234
1235
1236
1237 -----------------------------------------------------------------------------
1238 -- PROCEDURE set_additionalparameters
1239 -----------------------------------------------------------------------------
1240 -- Start of comments
1241
1242 --
1243 -- Procedure Name : procedure to addtional parameters for approval process
1244 -- Description :
1245 -- Business Rules :
1246 -- Parameters :
1247 -- Version : 1.0
1248 -- End of comments
1249 ------------------------------------------------------------------------------
1250 PROCEDURE set_additionalparameters(
1251 itemtype IN VARCHAR2,
1252 itemkey IN VARCHAR2,
1253 actid IN NUMBER,
1254 funcmode IN VARCHAR2,
1255 resultout OUT NOCOPY VARCHAR2) AS
1256
1257 -- Get the valid application id from FND
1258 CURSOR c_get_app_id_csr
1259 IS
1260 SELECT APPLICATION_ID
1261 FROM FND_APPLICATION
1262 WHERE APPLICATION_SHORT_NAME = G_APP_NAME;
1263
1264 -- Get the Transaction Type Id from OAM
1265 -- modification of where condn by dcshanmu for bug 5999276 starts
1266 CURSOR c_get_trx_type_csr(c_trx_type VARCHAR2)
1267 IS
1268 SELECT transaction_type_id,
1269 fnd_application_id
1270 FROM ame_transaction_types_v
1271 WHERE DESCRIPTION='OKL LP Product Approval Process';
1272 -- modification of where condn by dcshanmu for bug 5999276 starts
1273
1274 CURSOR l_wf_item_key_csr IS
1275 SELECT okl_wf_item_s.NEXTVAL item_key
1276 FROM dual;
1277
1278 l_return_status VARCHAR2(3) := OKL_API.G_RET_STS_SUCCESS;
1279 l_api_version NUMBER := 1.0;
1280 l_api_name CONSTANT VARCHAR2(30) := 'set_additionalparameters';
1281
1282 l_msg_count NUMBER;
1283 l_init_msg_list VARCHAR2(10) := OKL_API.G_FALSE;
1284 l_msg_data VARCHAR2(2000);
1285
1286 l_parameter_list wf_parameter_list_t;
1287 l_key VARCHAR2(240);
1288 l_event_name VARCHAR2(240);
1289 l_pdt_id OKL_PRODUCTS_V.ID%TYPE;
1290
1291 l_application_id FND_APPLICATION.APPLICATION_ID%TYPE;
1292 l_trans_appl_id AME_CALLING_APPS.APPLICATION_ID%TYPE;
1293 l_trans_type_id AME_CALLING_APPS.TRANSACTION_TYPE_ID%TYPE;
1294
1295 l_requester VARCHAR2(200);
1296 l_name VARCHAR2(200);
1297 l_requester_id VARCHAR2(200);
1298 l_message VARCHAR2(30000);
1299 X_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1300
1301 BEGIN
1302
1303 -- Create Internal Transaction
1304
1305 l_pdt_id := wf_engine.GetItemAttrText(itemtype => itemtype,
1306 itemkey => itemkey,
1307 aname => G_WF_ITM_PRODUCT_ID);
1308
1309
1310 -- Get the user id, Item key
1311 l_requester_id := FND_GLOBAL.USER_ID;
1312
1313 get_agent(p_user_id => l_requester_id,
1314 x_return_status => x_return_status,
1315
1316 x_name => l_requester,
1317 x_description => l_name);
1318
1319 -- Get the Application ID
1320 OPEN c_get_app_id_csr;
1321 FETCH c_get_app_id_csr INTO l_application_id;
1322 IF c_get_app_id_csr%NOTFOUND THEN
1323 RAISE OKL_API.G_EXCEPTION_ERROR;
1324 END IF;
1325 CLOSE c_get_app_id_csr;
1326
1327 -- Get the Transaction Type ID
1328 OPEN c_get_trx_type_csr(G_TRANS_APP_NAME);
1329 FETCH c_get_trx_type_csr INTO l_trans_type_id,
1330 l_trans_appl_id;
1331 IF c_get_trx_type_csr%NOTFOUND THEN
1332 RAISE OKL_API.G_EXCEPTION_ERROR;
1333 END IF;
1334 CLOSE c_get_trx_type_csr;
1335
1336 IF l_application_id = l_trans_appl_id THEN
1337
1338 l_message := '<p>The Product will be completed following your approval.</p>';
1339
1340 wf_engine.SetItemAttrText ( itemtype=> itemtype,
1341 itemkey => itemkey,
1342 aname => G_WF_ITM_MESSAGE_DESCRIPTION,
1343 avalue => l_message);
1344
1345
1346
1347 wf_engine.SetItemAttrText ( itemtype=> itemtype,
1348 itemkey => itemkey,
1349 aname => G_WF_ITM_PRODUCT_ID,
1350 avalue => l_pdt_id);
1351
1352
1353 wf_engine.SetItemAttrText ( itemtype=> itemtype,
1354 itemkey => itemkey,
1355 aname => G_WF_ITM_APPLICATION_ID,
1356 avalue => l_application_id);
1357
1358 wf_engine.SetItemAttrText ( itemtype=> itemtype,
1359 itemkey => itemkey,
1360 aname => G_WF_ITM_TRANSACTION_TYPE_ID,
1361 avalue => 'OKLLPPAP');
1362
1363 wf_engine.SetItemAttrText ( itemtype=> itemtype,
1364 itemkey => itemkey,
1365 aname => G_WF_ITM_REQUESTER,
1366 avalue => l_requester);
1367
1368
1369 wf_engine.SetItemAttrText ( itemtype=> itemtype,
1370 itemkey => itemkey,
1371 aname => G_WF_ITM_REQUESTER_ID,
1372 avalue => l_requester_id);
1373
1374
1375 END IF; -- l_application_id
1376
1377 EXCEPTION
1378 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1379 IF c_get_app_id_csr%ISOPEN THEN
1380 CLOSE c_get_app_id_csr;
1381 END IF;
1382 IF c_get_trx_type_csr%ISOPEN THEN
1383
1384 CLOSE c_get_trx_type_csr;
1385 END IF;
1386
1387 wf_core.context(G_PKG_NAME,
1388 l_api_name,
1389 itemtype,
1390 itemkey,
1391 TO_CHAR(actid),
1392 funcmode);
1393 RAISE;
1394
1395 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1396 IF c_get_app_id_csr%ISOPEN THEN
1397 CLOSE c_get_app_id_csr;
1398 END IF;
1399 IF c_get_trx_type_csr%ISOPEN THEN
1400 CLOSE c_get_trx_type_csr;
1401 END IF;
1402
1403 wf_core.context(G_PKG_NAME,
1404 l_api_name,
1405 itemtype,
1406 itemkey,
1407 TO_CHAR(actid),
1408 funcmode);
1409 RAISE;
1410
1411
1412 WHEN OTHERS THEN
1413 IF c_get_app_id_csr%ISOPEN THEN
1414 CLOSE c_get_app_id_csr;
1415
1416 END IF;
1417 IF c_get_trx_type_csr%ISOPEN THEN
1418 CLOSE c_get_trx_type_csr;
1419 END IF;
1420
1421 wf_core.context('OKL_SETUPPRODUCTS_PVT',
1422 'set_additionalparameters',
1423 itemtype,
1424 itemkey,
1425 TO_CHAR(actid),
1426 funcmode);
1427
1428 resultout := 'ERROR';
1429 RAISE;
1430 END set_additionalparameters;
1431
1432 ----------------------------------------------------------------------
1433 --- procedure to update product status
1434 ----------------------------------------------------------------------
1435 -- Start of comments
1436 --
1437 -- Procedure Name : update_product_status
1438 -- Description : procedure to update product status code.
1439 -- Business Rules :
1440 -- Parameters :
1441 -- Version : 1.0
1442 -- End of comments
1443 ------------------------------------------------------------------------
1444 PROCEDURE update_product_status(
1445
1446 p_api_version IN NUMBER,
1447 p_init_msg_list IN VARCHAR2,
1448 x_return_status OUT NOCOPY VARCHAR2,
1449 x_msg_count OUT NOCOPY NUMBER,
1450 x_msg_data OUT NOCOPY VARCHAR2,
1451 p_pdt_status IN VARCHAR2,
1452 p_pdt_id IN VARCHAR2) IS
1453
1454 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_PRODUCT_STATUS';
1455 l_api_version CONSTANT NUMBER := 1;
1456 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1457 l_pdtv_rec pdtv_rec_type;
1458 l_upd_pdtv_rec pdtv_rec_type;
1459 x_pdtv_rec pdtv_rec_type;
1460 l_no_data_found BOOLEAN := TRUE;
1461
1462 BEGIN
1463 x_return_status := Okl_Api.G_RET_STS_SUCCESS;
1464
1465 l_UPD_pdtv_rec.id := p_pdt_id;
1466 l_upd_pdtv_rec.product_status_code := p_pdt_status;
1467
1468 UPDATE OKL_PRODUCTS SET PRODUCT_STATUS_CODE = l_upd_pdtv_rec.product_status_code
1469 WHERE ID = l_UPD_pdtv_rec.id;
1470
1471 /*
1472 Okl_Products_Pub.update_products(p_api_version => p_api_version,
1473 p_init_msg_list => p_init_msg_list,
1474 x_return_status => l_return_status,
1475 x_msg_count => x_msg_count,
1476 x_msg_data => x_msg_data,
1477 p_pdtv_rec => l_upd_pdtv_rec,
1478 x_pdtv_rec => x_pdtv_rec);
1479
1480 */
1481
1482 IF l_return_status = Okl_Api.G_RET_STS_ERROR THEN
1483 RAISE Okl_Api.G_EXCEPTION_ERROR;
1484 ELSIF l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR THEN
1485 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
1486 END IF;
1487
1488 EXCEPTION
1489 WHEN Okl_Api.G_EXCEPTION_ERROR THEN
1490
1491 x_return_status := Okl_Api.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
1492 p_pkg_name => G_PKG_NAME,
1493 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
1494 x_msg_count => x_msg_count,
1495 x_msg_data => x_msg_data,
1496 p_api_type => '_PVT');
1497 WHEN Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR THEN
1498 x_return_status := Okl_Api.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
1499 p_pkg_name => G_PKG_NAME,
1500 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
1501 x_msg_count => x_msg_count,
1502 x_msg_data => x_msg_data,
1503 p_api_type => '_PVT');
1504 WHEN OTHERS THEN
1505 x_return_status := Okl_Api.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
1506 p_pkg_name => G_PKG_NAME,
1507 p_exc_name => 'OTHERS',
1508 x_msg_count => x_msg_count,
1509 x_msg_data => x_msg_data,
1510 p_api_type => '_PVT');
1511
1512
1513 END update_product_status;
1514
1515 ---------------------------------------------------------------------------------
1516 -- PROCEDURE get_approval_status
1517 ---------------------------------------------------------------------------------
1518 -- Start of comments
1519 --
1520
1521 -- Procedure Name : get_approval_status
1522 -- Description : procedure to get approval status from workflow.
1523 -- Business Rules :
1524 -- Parameters :
1525 -- Version : 1.0
1526 -- End of comments
1527 ----------------------------------------------------------------------------------
1528
1529 PROCEDURE get_approval_status(itemtype IN VARCHAR2,
1530 itemkey IN VARCHAR2,
1531 actid IN NUMBER,
1532 funcmode IN VARCHAR2,
1533 resultout OUT NOCOPY VARCHAR2)
1534 IS
1535
1536 l_return_status VARCHAR2(3) := OKL_API.G_RET_STS_SUCCESS;
1537 l_api_version NUMBER := 1.0;
1538 l_msg_count NUMBER;
1539 l_init_msg_list VARCHAR2(10) := OKL_API.G_FALSE;
1540 l_msg_data VARCHAR2(2000);
1541 l_api_name VARCHAR2(30) := 'get_approval_status';
1542
1543 l_pdt_id OKC_K_HEADERS_V.ID%TYPE;
1544 l_approved_yn VARCHAR2(30);
1545
1546 BEGIN
1547 -- We getting the contract_Id from WF
1548 l_pdt_id := wf_engine.GetItemAttrText(itemtype => itemtype,
1549 itemkey => itemkey,
1550 aname => 'TRANSACTION_ID');
1551
1552
1553 --Run Mode
1554 IF funcmode = 'RUN' THEN
1555 l_approved_yn := wf_engine.GetItemAttrText (itemtype => itemtype,
1556 itemkey => itemkey,
1557 aname => G_WF_ITM_APPROVED_YN);
1558
1559 IF l_approved_yn = G_WF_ITM_APPROVED_YN_YES THEN
1560
1561 update_product_status(p_api_version => l_api_version,
1562 p_init_msg_list => l_init_msg_list,
1563 x_return_status => l_return_status,
1564 x_msg_count => l_msg_count,
1565 x_msg_data => l_msg_data,
1566 p_pdt_status => G_PDT_STS_APPROVED,
1567 p_pdt_id => l_pdt_id);
1568
1569
1570 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1571 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1572 ELSIF (l_return_Status = OKL_API.G_RET_STS_ERROR) THEN
1573 RAISE OKL_API.G_EXCEPTION_ERROR;
1574 END IF;
1575
1576 ELSE
1577
1578 update_product_status(p_api_version => l_api_version,
1579 p_init_msg_list => l_init_msg_list,
1580 x_return_status => l_return_status,
1581 x_msg_count => l_msg_count,
1582 x_msg_data => l_msg_data,
1583 p_pdt_status => G_PDT_STS_INVALID,
1584 p_pdt_id => l_pdt_id);
1585
1586
1587 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1588 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1589 ELSIF (l_return_Status = OKL_API.G_RET_STS_ERROR) THEN
1590 RAISE OKL_API.G_EXCEPTION_ERROR;
1591
1592
1593 END IF;
1594
1595
1596 END IF;
1597
1598 resultout := 'COMPLETE:';
1599 RETURN;
1600 END IF;
1601
1602 --Transfer Mode
1603 IF funcmode = 'TRANSFER' THEN
1604 resultout := wf_engine.eng_null;
1605 RETURN;
1606 END IF;
1607 -- CANCEL mode
1608 IF (funcmode = 'CANCEL') THEN
1609 resultout := 'COMPLETE:';
1610 RETURN;
1611 END IF;
1612 -- TIMEOUT mode
1613 IF (funcmode = 'TIMEOUT') THEN
1614 resultout := 'COMPLETE:';
1615 RETURN;
1616 END IF;
1617
1618 EXCEPTION
1619 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1620
1621 wf_core.context(G_PKG_NAME,
1622 l_api_name,
1623 itemtype,
1624 itemkey,
1625 TO_CHAR(actid),
1626 funcmode);
1627 RAISE;
1628 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1629 wf_core.context(G_PKG_NAME,
1630 l_api_name,
1631 itemtype,
1632 itemkey,
1633 TO_CHAR(actid),
1634 funcmode);
1635 RAISE;
1636 WHEN OTHERS THEN
1637 wf_core.context(G_PKG_NAME,
1638 l_api_name,
1639 itemtype,
1640 itemkey,
1641 TO_CHAR(actid),
1642 funcmode);
1643 RAISE;
1644 END get_approval_status;
1645
1646
1647 ---------------------------------------------------------------------------
1648 -- PROCEDURE Validate_book_class
1649 ---------------------------------------------------------------------------
1650 -- Start of comments
1651 --
1652
1653 -- Procedure Name : Validate_book_class
1654 -- Description :
1655 -- Business Rules :
1656 -- Parameters :
1657 -- Version : 1.0
1658 -- End of comments
1659 ---------------------------------------------------------------------------
1660 PROCEDURE Validate_book_class(p_pdtv_rec IN OUT NOCOPY pdtv_rec_type
1661 ,x_return_status OUT NOCOPY VARCHAR2 )
1662 IS
1663
1664 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
1665 l_token_1 VARCHAR2(1999);
1666
1667
1668 CURSOR c2(p_pdt_id NUMBER) IS
1669 SELECT '1'
1670 FROM okl_product_parameters_v a
1671 where a.id = p_pdt_id;
1672
1673
1674 CURSOR c1(p_pdt_id NUMBER) IS
1675 SELECT DISTINCT a.deal_type
1676 FROM okl_product_parameters_v a,
1677 okl_ae_tmpt_sets_v b,
1678 OKL_ST_GEN_TMPT_SETS c
1679 WHERE a.aes_id = b.id
1680 AND b.gts_id = c.id
1681 AND a.deal_type = c.deal_type
1682 AND a.id = p_pdt_id;
1683
1684 /*
1685 cursor chk_deal_type(p_pdt_id okl_pdt_pqy_vals_v.pdt_id%TYPE,
1686 p_aes_id okl_products_v.aes_id%TYPE)
1687 IS
1688 SELECT DISTINCT C.deal_type
1689 FROM okl_products_v a,
1690 okl_ae_tmpt_sets_v b,
1691 OKL_ST_GEN_TMPT_SETS c
1692 WHERE a.aes_id = b.id
1693 AND b.gts_id = c.id
1694
1695 AND a.id = p_pdt_id
1696 and a.aes_id = p_aes_id
1697 intersect
1698 select DEAL_TYPE from okl_product_parameters_v ppar
1699 where ppar.id = p_pdt_id;
1700
1701
1702 cursor chk_deal_type(p_pdt_id okl_pdt_pqy_vals_v.pdt_id%TYPE,
1703 p_qve_id okl_pdt_pqy_vals_v.qve_id%TYPE)
1704 IS
1705 SELECT DISTINCT C.deal_type
1706 FROM okl_products_v a,
1707 okl_ae_tmpt_sets_v b,
1708 OKL_ST_GEN_TMPT_SETS c
1709 WHERE a.aes_id = b.id
1710 AND b.gts_id = c.id
1711 AND a.id = p_pdt_id
1712 intersect
1713 select value from okl_pqy_values qve
1714 where qve.id = p_qve_id;
1715 */
1716
1717
1718 l_row_found VARCHAR2(20);
1719 l_found VARCHAR2(10);
1720
1721
1722 BEGIN
1723 -- initialize return status
1724 x_return_status := Okl_Api.G_RET_STS_SUCCESS;
1725
1726 l_token_1 := Okl_Accounting_Util.Get_Message_Token('OKL_LP_PRODUCT_CRUPD','OKL_NAME');
1727
1728 OPEN c2(p_pdtv_rec.id);
1729 FETCH c2 INTO l_found;
1730
1731 if c2%found then
1732
1733 OPEN c1(p_pdtv_rec.id);
1734 FETCH c1 INTO l_row_found;
1735 IF (c1%NOTFOUND) THEN
1736 Okl_Api.SET_MESSAGE(p_app_name => G_APP_NAME,
1737 p_msg_name => G_BOOK_CLASS_MISMATCH);
1738 x_return_status := Okl_Api.G_RET_STS_ERROR;
1739 RAISE G_EXCEPTION_HALT_PROCESSING;
1740
1741 END IF;
1742 CLOSE c1;
1743
1744 end if;
1745 close c2;
1746
1747 EXCEPTION
1748 WHEN G_EXCEPTION_HALT_PROCESSING THEN
1749 -- no processing necessary; validation can continue
1750 -- with the next column
1751 NULL;
1752
1753
1754 WHEN OTHERS THEN
1755 -- store SQL error message on message stack for caller
1756 Okl_Api.SET_MESSAGE(p_app_name => Okl_Pdt_Pvt.g_app_name,
1757 p_msg_name => Okl_Pdt_Pvt.g_unexpected_error,
1758
1759 p_token1 => Okl_Pdt_Pvt.g_sqlcode_token,
1760 p_token1_value => SQLCODE,
1761 p_token2 => Okl_Pdt_Pvt.g_sqlerrm_token,
1762 p_token2_value => SQLERRM);
1763
1764 -- notify caller of an UNEXPECTED error
1765 x_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
1766
1767 END Validate_book_class;
1768
1769
1770 ---------------------------------------------------------------------------
1771 -- PROCEDURE check_accrual_streams
1772 ---------------------------------------------------------------------------
1773 -- Start of comments
1774 --
1775 -- Procedure Name : check_accrual_streams
1776 -- Description :
1777 -- Business Rules :
1778 -- Parameters :
1779 -- Version : 1.0
1780 -- End of comments
1781 ---------------------------------------------------------------------------
1782 PROCEDURE check_accrual_streams(p_pdtv_rec IN OUT NOCOPY pdtv_rec_type
1783 ,x_return_status OUT NOCOPY VARCHAR2 )
1784 IS
1785
1786 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
1787 l_token_1 VARCHAR2(1999);
1788 /*
1789 CURSOR c1(p_pdt_id NUMBER) IS
1790 SELECT name
1791 FROM OKL_PROD_STRM_TYPES_UV a
1792 WHERE pdt_id = p_pdt_id
1793 INTERSECT
1794 SELECT STY_NAME FROM OKL_ST_GEN_TMPT_CNTRCT_UV
1795 WHERE PDT_ID = p_pdt_id;
1796
1797 */
1798 l_row_found VARCHAR2(20);
1799
1800 BEGIN
1801 null;
1802 /*
1803 -- initialize return status
1804 x_return_status := Okl_Api.G_RET_STS_SUCCESS;
1805
1806 l_token_1 := Okl_Accounting_Util.Get_Message_Token('OKL_LP_PDT_TMPL_CREATE_UPDATE','OKL_PRODUCT_TEMPLATE');
1807
1808 OPEN c1(p_pdtv_rec.id);
1809 FETCH c1 INTO l_row_found;
1810 IF (c1%NOTFOUND) THEN
1811 Okl_Api.SET_MESSAGE(p_app_name => Okl_Pdt_Pvt.g_app_name
1812 ,p_msg_name => Okl_Pdt_Pvt.g_required_value
1813 ,p_token1 => Okl_Pdt_Pvt.g_col_name_token
1814 ,p_token1_value => l_token_1);
1815
1816 x_return_status := Okl_Api.G_RET_STS_ERROR;
1817 RAISE G_EXCEPTION_HALT_PROCESSING;
1818
1819 END IF;
1820
1821 CLOSE c1;
1822 */
1823 EXCEPTION
1824 WHEN G_EXCEPTION_HALT_PROCESSING THEN
1825 -- no processing necessary; validation can continue
1826 -- with the next column
1827 NULL;
1828
1829 WHEN OTHERS THEN
1830 -- store SQL error message on message stack for caller
1831 Okl_Api.SET_MESSAGE(p_app_name => Okl_Pdt_Pvt.g_app_name,
1832 p_msg_name => Okl_Pdt_Pvt.g_unexpected_error,
1833
1834 p_token1 => Okl_Pdt_Pvt.g_sqlcode_token,
1835 p_token1_value => SQLCODE,
1836 p_token2 => Okl_Pdt_Pvt.g_sqlerrm_token,
1837 p_token2_value => SQLERRM);
1838
1839 -- notify caller of an UNEXPECTED error
1840 x_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
1841
1842 END check_accrual_streams;
1843
1844 ---------------------------------------------------------------------------------
1845 -- PROCEDURE validate_product
1846 ---------------------------------------------------------------------------------
1847 -- Start of comments
1848 --
1849 -- Procedure Name : validate_product
1850 -- Description : procedure to validate product.
1851 -- Business Rules :
1852 -- Parameters :
1853 -- Version : 1.0
1854
1855 -- End of comments
1856 ----------------------------------------------------------------------------------
1857 PROCEDURE validate_product( p_api_version IN NUMBER,
1858 p_init_msg_list IN VARCHAR2,
1859 x_return_status OUT NOCOPY VARCHAR2,
1860 x_msg_count OUT NOCOPY NUMBER,
1861 x_msg_data OUT NOCOPY VARCHAR2,
1862 p_pdtv_rec IN pdtv_rec_type,
1863 x_pdtv_rec OUT NOCOPY pdtv_rec_type
1864 ) is
1865 l_api_name CONSTANT VARCHAR2(30) := 'VALIDATE_PRODUCT';
1866 l_api_version CONSTANT NUMBER := 1;
1867 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
1868 l_valid BOOLEAN;
1869 l_pdtv_rec pdtv_rec_type := p_pdtv_rec;
1870 l_upd_pdtv_rec pdtv_rec_type := p_pdtv_rec;
1871 l_db_pdtv_rec pdtv_rec_type;
1872 l_no_data_found BOOLEAN := TRUE;
1873 l_init_msg_list VARCHAR2(10) := OKL_API.G_FALSE;
1874 l_msg_count NUMBER;
1875 l_msg_data VARCHAR2(2000);
1876 l_check_dt VARCHAR2(1) := '?';
1877 --rkuttiya added for Multi GAAP Project
1878 l_deal_type VARCHAR2(150);
1879 l_deal_type1 VARCHAR2(40);
1880 l_chk_bc VARCHAR2(100);
1881 l_tax_upfront_sty_id OKL_SYSTEM_PARAMS.tax_upfront_sty_id%TYPE;
1882 l_gtt_id OKL_ST_GEN_TEMPLATES.ID%TYPE;
1883 l_primary_sty_id OKL_ST_GEN_TMPT_LNS.primary_sty_id%TYPE;
1884 l_stream_name okl_strm_type_v.styb_purpose_meaning%TYPE;
1885
1886 -- Cursor to fetch the Product Quality Values
1887 CURSOR get_pp_csr(p_pdt_id NUMBER)
1888 IS
1889 SELECT id pdt_id,
1890 product_subclass,
1891 deal_type,
1892 deal_type_meaning,
1893 tax_owner,
1894 tax_owner_meaning,
1895 revenue_recognition_method,
1896 revenue_recognition_meaning,
1897 interest_calculation_basis,
1898 interest_calculation_meaning
1899 FROM okl_product_parameters_v pp
1900 WHERE pp.id = p_pdt_id;
1901
1902 CURSOR get_sgt_values_csr( p_pdt_id NUMBER)
1903 IS
1904 SELECT gts.name sgt_name,
1905 gts.pricing_engine,
1906 gts.deal_type,
1907 gts.tax_owner,
1908 gts.interest_calc_meth_code,
1909 gts.revenue_recog_meth_code
1910 FROM OKL_PRODUCTS_V PDT,
1911 OKL_AE_TMPT_SETS_V AES,
1912 OKL_ST_GEN_TMPT_SETS GTS
1913 WHERE PDT.AES_ID = AES.ID
1914 AND AES.GTS_ID = GTS.ID
1915 AND PDT.ID = p_pdt_id;
1916
1917 CURSOR chk_ptl_aes_bc(p_aes_id IN Okl_Products_V.AES_ID%TYPE,
1918 p_ptl_id IN Okl_Products_V.PTL_ID%TYPE,
1919 p_pdt_id IN Okl_Products_V.id%TYPE)
1920 IS
1921 SELECT DISTINCT DECODE(C.product_type,'FINANCIAL','LEASE','INVESTOR')
1922 FROM okl_ae_tmpt_sets_v b,
1923 OKL_ST_GEN_TMPT_SETS c
1924 WHERE b.gts_id = c.id
1925 AND b.id = p_aes_id
1926 INTERSECT
1927 SELECT DISTINCT PQY.NAME
1928 FROM okl_PDT_PQYS_V PDQ,
1929 OKL_PQY_VALUES_V QVE,OKL_PDT_QUALITYS_V PQY
1930 WHERE PQY.ID = QVE.PQY_ID
1931 AND PQY.ID= PDQ.PQY_ID
1932 AND PDQ.PTL_ID = p_PTL_ID
1933 AND pqy.name IN('LEASE','INVESTOR');
1934
1935 CURSOR c_tax_sty_id_cur IS
1936 SELECT tax_upfront_sty_id
1937 FROM OKL_SYSTEM_PARAMS;
1938
1939 CURSOR c_st_gen_templates_cur (p_pdt_id IN okl_products_v.id%TYPE)
1940 IS
1941 SELECT gttv.id gtt_id, gtsv.name name, gttv.version version
1942 FROM
1943 OKL_ST_GEN_TEMPLATES GTTV,
1944 OKL_ST_GEN_TMPT_SETS GTSV,
1945
1946 okl_ae_tmpt_sets_v AES,
1947 okl_products_v PDT
1948 WHERE
1949 GTTV.gts_id = GTSV.id AND
1950 GTTV.tmpt_status = 'ACTIVE' AND
1951 GTSV.id = AES.gts_id AND
1952 AES.id = PDT.aes_id AND
1953 PDT.id = p_pdt_id AND
1954 GTSV.product_type ='FINANCIAL';
1955
1956 CURSOR c_st_gen_template_lns_cur(p_gtt_id IN OKL_ST_GEN_TEMPLATES.id%TYPE,
1957 p_tax_upfront_sty_id IN OKL_SYSTEM_PARAMS.tax_upfront_sty_id%TYPE)
1958 IS
1959 SELECT GTLV.PRIMARY_STY_ID
1960 FROM
1961 OKL_ST_GEN_TEMPLATES GTTV,
1962 OKL_ST_GEN_TMPT_LNS GTLV
1963 WHERE
1964 GTTV.ID = p_gtt_id AND
1965 GTTV.ID = GTLV.gtt_id AND
1966 GTLV.PRIMARY_STY_ID = p_tax_upfront_sty_id AND
1967 GTLV.PRIMARY_YN = 'Y' ;
1968
1969 CURSOR c_stream_name(p_id IN okl_strm_type_v.ID%TYPE)
1970 IS
1971 SELECT styb_purpose_meaning
1972 FROM okl_strm_type_v
1973 WHERE ID = p_id;
1974
1975 -- Bug 6803437: Start
1976 -- Cursor to fetch the Reporting Product Status
1977 CURSOR get_rep_pdt_sts_code_csr( p_pdt_id NUMBER )
1978 IS
1979 SELECT rp.product_status_code rp_pdt_sts_code
1980 ,rp.name rp_pdt_name
1981 ,rp.id rp_pdt_id -- Bug 7134895
1982 FROM okl_products np,
1983 okl_products rp
1984 WHERE rp.id = np.reporting_pdt_id
1985 AND np.id = p_pdt_id;
1986 -- Bug 6803437: End
1987
1988 --rkuttiya added for 12.1.1 Multi GAAP Project
1989 --to check whether the reporting product is attached to any other contract
1990 CURSOR okl_rpt_pdtv_chk(p_pdt_id NUMBER) IS
1991 SELECT '1'
1992 FROM okl_k_headers_v khdr
1993 WHERE khdr.pdt_id = p_pdt_id;
1994 --
1995
1996 /* Bug 7134895 */
1997 l_rpt_pdt_id OKL_PRODUCTS.ID%TYPE := null;
1998 l_rev_rec_method okl_product_parameters_v.revenue_recognition_method%TYPE := null;
1999 l_int_calc_basis okl_product_parameters_v.interest_calculation_basis%TYPE := null;
2000 l_rpt_rev_rec_method okl_product_parameters_v.revenue_recognition_method%TYPE := null;
2001 l_rpt_int_calc_basis okl_product_parameters_v.interest_calculation_basis%TYPE := null;
2002 l_qv_found BOOLEAN;
2003 l_inv_deal_type OKL_ST_GEN_TMPT_SETS.deal_type%TYPE;
2004 -- Bug 6803437: Start
2005 l_rp_pdt_sts_code VARCHAR2(30);
2006 l_rp_pdt_name OKL_PRODUCTS_V.NAME%TYPE;
2007 l_raise_exception BOOLEAN := FALSE;
2008 -- Bug 6803437: End
2009 --rkuttiya added for 12.1.1 Multi GAAP Project
2010 l_rpt_deal_type VARCHAR2(150);
2011 l_deal_type_meaning VARCHAR2(4000);
2012 l_pricing_engine VARCHAR2(30);
2013 l_rpt_pricing_engine VARCHAR2(30);
2014 l_check VARCHAR2(1);
2015 BEGIN
2016
2017 x_return_status := Okl_Api.G_RET_STS_SUCCESS;
2018
2019 l_return_status := Okl_Api.START_ACTIVITY(p_api_name => l_api_name,
2020 p_pkg_name => G_PKG_NAME,
2021 p_init_msg_list => p_init_msg_list,
2022 l_api_version => l_api_version,
2023 p_api_version => p_api_version,
2024 p_api_type => '_PVT',
2025 x_return_status => l_return_status);
2026 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
2027 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
2028 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
2029 RAISE Okl_Api.G_EXCEPTION_ERROR;
2030 END IF;
2031
2032
2033 -- Validation process is kicked only when the product is in invalid or new status.
2034 IF l_pdtv_rec.product_status_code IN ('INVALID','NEW') THEN
2035
2036 /*=========================================
2037 -- user defined streams validations BEGIN
2038 ==========================================*/
2039
2040
2041 -- Bookclass and taxowner on the product template should match the bookclass
2042 -- and taxowner on the stream template.
2043 OPEN chk_ptl_aes_bc(l_pdtv_rec.aes_id,l_pdtv_rec.ptl_id,l_pdtv_rec.id);
2044 FETCH chk_ptl_aes_bc INTO l_chk_bc;
2045 CLOSE chk_ptl_aes_bc;
2046
2047 IF (l_chk_bc IS NULL) THEN
2048
2049 Okl_Api.SET_MESSAGE(p_app_name => G_APP_NAME,
2050 p_msg_name => G_PTL_AES_BC_MISMATCH);
2051 x_return_status := Okl_Api.G_RET_STS_ERROR;
2052 RAISE OKL_API.G_EXCEPTION_ERROR;
2053 END IF;
2054
2055 /*mansrini Tax enhancement proj: If the product is of financial type, check upfront tax id matches*/
2056
2057 OPEN c_tax_sty_id_cur;
2058 FETCH c_tax_sty_id_cur INTO l_tax_upfront_sty_id;
2059 CLOSE c_tax_sty_id_cur;
2060
2061 IF l_tax_upfront_sty_id IS NOT NULL THEN
2062
2063 FOR r_st_gen_templates_rec IN c_st_gen_templates_cur(l_pdtv_rec.id) LOOP
2064
2065 OPEN c_st_gen_template_lns_cur(r_st_gen_templates_rec.gtt_id,l_tax_upfront_sty_id);
2066 FETCH c_st_gen_template_lns_cur INTO l_primary_sty_id;
2067
2068 IF c_st_gen_template_lns_cur%NOTFOUND THEN
2069 CLOSE c_st_gen_template_lns_cur;
2070
2071 OPEN c_stream_name(l_tax_upfront_sty_id);
2072 FETCH c_stream_name INTO l_stream_name;
2073 CLOSE c_stream_name;
2074
2075 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
2076 p_msg_name => G_TAX_STYID_MISMATCH,
2077 p_token1 => 'SGT_NAME',
2078 p_token1_value => r_st_gen_templates_rec.name,
2079 p_token2 => 'SGT_VERSION',
2080 p_token2_value => r_st_gen_templates_rec.version,
2081 p_token3 => 'STRM_NAME',
2082 p_token3_value => l_stream_name);
2083
2084
2085 x_return_status := Okl_Api.G_RET_STS_ERROR;
2086 RAISE OKL_API.G_EXCEPTION_ERROR;
2087
2088 END IF;
2089
2090 IF c_st_gen_template_lns_cur%ISOPEN THEN
2091 CLOSE c_st_gen_template_lns_cur;
2092 END IF;
2093
2094 END LOOP;
2095
2096 END IF;
2097 -- check to see if the product book class is defined
2098 l_qv_found := FALSE;
2099 FOR pdt_rec IN get_pp_csr(l_pdtv_rec.id)
2100 LOOP
2101 l_qv_found := TRUE;
2102 l_rev_rec_method := pdt_rec.revenue_recognition_method;
2103 l_int_calc_basis := pdt_rec.interest_calculation_basis;
2104 --rkuttiya added for 12.1.1 MultiGAAP project
2105 l_deal_type := pdt_rec.deal_type;
2106 l_deal_type_meaning := pdt_rec.deal_type_meaning;
2107 --
2108 FOR sgt_rec IN get_sgt_values_csr( l_pdtv_rec.id )
2109 LOOP
2110 l_pricing_engine := sgt_rec.pricing_engine;
2111 -- Check the Deal Type, Tax Owner, Interest Calculation Basis, Revenue Recognition Basis
2112 IF pdt_rec.product_subclass = 'INVESTOR'
2113 THEN
2114 -- For Investor products just need to check the INVESTOR quality value alone
2115 IF sgt_rec.deal_type = 'SALE'
2116 THEN
2117 -- In the SGT, though the meaning has been changed to Securitization, the lookup code
2118 -- is still remaining as SALE, but the Product ones has been changed to SECURITIZATION
2119 l_inv_deal_type := 'SECURITIZATION';
2120 ELSE
2121 l_inv_deal_type := sgt_rec.deal_type;
2122 END IF;
2123 IF pdt_rec.deal_type <> l_inv_deal_type
2124 THEN
2125 OKL_API.SET_MESSAGE(
2126 p_app_name => G_APP_NAME,
2127 p_msg_name => 'OKL_NEW_INVESTOR_MISMATCH',
2128 p_token1 => 'PQVALUE',
2129 p_token1_value => OKL_ACCOUNTING_UTIL.GET_LOOKUP_MEANING( 'OKL_SECURITIZATION_TYPE', pdt_rec.deal_type ),
2130 p_token2 => 'SGTVALUE',
2131 p_token2_value => OKL_ACCOUNTING_UTIL.GET_LOOKUP_MEANING( 'OKL_STREAM_INV_BOOK_CLASS', sgt_rec.deal_type) );
2132 x_return_status := Okl_Api.G_RET_STS_ERROR;
2133 RAISE OKL_API.G_EXCEPTION_ERROR;
2134 END IF;
2135 ELSIF pdt_rec.product_subclass = 'LEASE'
2136 THEN
2137 -- Deal Type, Tax Owner, Interest Calculation Basis, Revenue Recognition Basis
2138 -- should match with that of the SGT Quality Values
2139 IF pdt_rec.deal_type <> sgt_rec.deal_type OR
2140 pdt_rec.tax_owner <> sgt_rec.tax_owner OR
2141 pdt_rec.interest_calculation_basis <> sgt_rec.interest_calc_meth_code OR
2142 pdt_rec.revenue_recognition_method <> sgt_rec.revenue_recog_meth_code
2143 THEN
2144 Okl_Api.SET_MESSAGE(
2145 p_app_name => G_APP_NAME,
2146 p_msg_name => 'OKL_NEW_PDT_QUAL_MISMATCH',
2147 p_token1 => 'SGTDEALTYPE',
2148 p_token1_value => OKL_ACCOUNTING_UTIL.GET_LOOKUP_MEANING( 'OKL_STREAM_ALL_BOOK_CLASS',sgt_rec.deal_type ),
2149 p_token2 => 'SGTTAXOWNER',
2150 p_token2_value => OKL_ACCOUNTING_UTIL.GET_LOOKUP_MEANING( 'OKL_TAX_OWNER',sgt_rec.tax_owner),
2151 p_token3 => 'SGTINTCALC',
2152 p_token3_value => OKL_ACCOUNTING_UTIL.GET_LOOKUP_MEANING( 'OKL_INTEREST_CALCULATION_BASIS',sgt_rec.interest_calc_meth_code ),
2153 p_token4 => 'SGTRRB',
2154 p_token4_value => OKL_ACCOUNTING_UTIL.GET_LOOKUP_MEANING( 'OKL_REVENUE_RECOGNITION_METHOD',sgt_rec.revenue_recog_meth_code)
2155 );
2156 x_return_status := Okl_Api.G_RET_STS_ERROR;
2157 RAISE OKL_API.G_EXCEPTION_ERROR;
2158 END IF;
2159
2160 END IF; -- IF pdt_rec.product_subclass
2161 /* rkuttiya commented out following code for bug 7385171
2162 * remove validation for variable rate
2163 --Bug 4728496 dpsingh start
2164 IF ( (pdt_rec.interest_calculation_basis IN ('FLOAT_FACTORS','CATCHUP/CLEANUP') OR pdt_rec.revenue_recognition_method IN ('ESTIMATED_AND_BILLED','ACTUAL')) AND l_pdtv_rec.reporting_pdt_id IS NOT NULL)
2165 THEN
2166 Okl_Api.SET_MESSAGE(
2167 p_app_name => G_APP_NAME,
2168 p_msg_name => 'OKL_REP_PDT_ATT_VAR_PDT'
2169 );
2170 RAISE OKL_API.G_EXCEPTION_ERROR;
2171 END IF;
2172 --Bug 4728496 dpsingh end*/
2173
2174 END LOOP; -- FOR sgt_rec
2175 END LOOP; -- FOR pdt_rec
2176 IF l_qv_found = FALSE
2177 THEN
2178 -- Show the error message saying that user should enter all the
2179 -- Quality values.
2180 OKL_API.SET_MESSAGE(
2181 p_app_name => G_APP_NAME,
2182 p_msg_name => 'OKL_PDT_QVALS_UNDEFINED');
2183 x_return_status := Okl_Api.G_RET_STS_ERROR;
2184 RAISE OKL_API.G_EXCEPTION_ERROR;
2185 END IF;
2186 /*=======================================
2187 -- user defined streams validations END
2188 ========================================*/
2189 -- Bug 6803437: Start
2190 -- New Validation:
2191 -- The Reporting Product (if any) associated to the Product being validated
2192 -- should be in Approved Status.
2193 l_rpt_pdt_id := null; -- Bug 7134895
2194 l_rp_pdt_sts_code := 'APPROVED';
2195 FOR t_rec IN get_rep_pdt_sts_code_csr( p_pdt_id => l_pdtv_rec.id )
2196 LOOP
2197 l_rp_pdt_sts_code := t_rec.rp_pdt_sts_code;
2198 l_rp_pdt_name := t_rec.rp_pdt_name;
2199 l_rpt_pdt_id := t_rec.rp_pdt_id; -- Bug 7134895
2200 END LOOP;
2201 IF l_rp_pdt_sts_code <> 'APPROVED'
2202 THEN
2203 -- Raise an Exception and return x_valid as FALSE
2204 OKL_API.set_message(
2205 p_app_name => G_APP_NAME
2206 ,p_msg_name => 'OKL_REP_PDT_NOT_APPROVED'
2207 ,p_token1 => 'REPPRODUCT'
2208 ,p_token1_value => l_rp_pdt_name
2209 );
2210 x_return_status := Okl_Api.G_RET_STS_ERROR;
2211 RAISE OKL_API.G_EXCEPTION_ERROR;
2212 END IF; -- IF l_rp_pdt_sts_code
2213
2214 -- Bug 7134895
2215 l_raise_exception := FALSE;
2216 IF (l_rpt_pdt_id IS NOT NULL AND l_rpt_pdt_id <> OKL_API.G_MISS_NUM) THEN
2217 -- Get rev_rec_method and int_calc_basis for reporting product and
2218 -- compare against the base product if they are same
2219 FOR rpt_pdt_rec IN get_pp_csr(l_rpt_pdt_id)
2220 LOOP
2221 l_rpt_rev_rec_method := rpt_pdt_rec.revenue_recognition_method;
2222 l_rpt_int_calc_basis := rpt_pdt_rec.interest_calculation_basis;
2223 --rkuttiya added for 12.1.1 MultiGAAP Project
2224 l_rpt_deal_type := rpt_pdt_rec.deal_type;
2225 FOR sgt_rpt_rec IN get_sgt_values_csr(l_rpt_pdt_id)
2226 LOOP
2227 l_rpt_pricing_engine := sgt_rpt_rec.pricing_engine;
2228 END LOOP;
2229 END LOOP;
2230 --rkuttiya added for 12.1.1 Multi Gaap Project
2231 /* -- Bug 7450075
2232 OPEN okl_rpt_pdtv_chk(l_rpt_pdt_id);
2233 FETCH okl_rpt_pdtv_chk INTO l_check;
2234 CLOSE okl_rpt_pdtv_chk;
2235
2236 IF l_check IS NOT NULL THEN
2237 Okl_Api.SET_MESSAGE(
2238 p_app_name => G_APP_NAME,
2239 p_msg_name => 'OKL_PDT_RPT_KHR_ASSOC'
2240 );
2241 x_return_status := Okl_Api.G_RET_STS_ERROR;
2242 RAISE OKL_API.G_EXCEPTION_ERROR;
2243 END IF; */
2244
2245 IF (l_rpt_rev_rec_method <> l_rev_rec_method) THEN
2246 Okl_Api.SET_MESSAGE(
2247 p_app_name => G_APP_NAME,
2248 p_msg_name => 'OKL_PDT_RPT_RRM_MISMATCH'
2249 );
2250 x_return_status := Okl_Api.G_RET_STS_ERROR;
2251 l_raise_exception := TRUE;
2252 END IF;
2253
2254 IF (l_rpt_int_calc_basis <> l_int_calc_basis) THEN
2255 Okl_Api.SET_MESSAGE(
2256 p_app_name => G_APP_NAME,
2257 p_msg_name => 'OKL_PDT_RPT_ICB_MISMATCH'
2258 );
2259 x_return_status := Okl_Api.G_RET_STS_ERROR;
2260 RAISE OKL_API.G_EXCEPTION_ERROR;
2261 END IF;
2262
2263 --rkuttiya added for 12.1.1 Multi GAAP project
2264 IF (l_deal_type = 'LOAN') AND (l_rpt_deal_type <> 'LOAN') THEN
2265 Okl_Api.SET_MESSAGE(
2266 p_app_name => G_APP_NAME,
2267 p_msg_name => 'OKL_PDT_RPT_SELECT_LOAN'
2268 );
2269 x_return_status := Okl_Api.G_RET_STS_ERROR;
2270 RAISE OKL_API.G_EXCEPTION_ERROR;
2271 END IF;
2272
2273 IF (l_deal_type = 'LOAN-REVOLVING') AND (l_rpt_deal_type <>
2274 'LOAN-REVOLVING') THEN
2275 Okl_Api.SET_MESSAGE(
2276 p_app_name => G_APP_NAME,
2277 p_msg_name => 'OKL_PDT_RPT_SELECT_REVLOAN'
2278 );
2279 x_return_status := Okl_Api.G_RET_STS_ERROR;
2280 RAISE OKL_API.G_EXCEPTION_ERROR;
2281 END IF;
2282
2283 IF (l_deal_type IN ('LEASEDF','LEASEOP','LEASEST')) AND
2284 (l_rpt_deal_type NOT IN ('LEASEDF','LEASEOP','LEASEST')) THEN
2285 Okl_Api.SET_MESSAGE(
2286 p_app_name => G_APP_NAME,
2287 p_msg_name => 'OKL_PDT_RPT_SELECT_LEASE'
2288 );
2289 x_return_status := Okl_Api.G_RET_STS_ERROR;
2290 RAISE OKL_API.G_EXCEPTION_ERROR;
2291 END IF;
2292
2293 IF l_pricing_engine <> l_rpt_pricing_engine THEN
2294 Okl_Api.SET_MESSAGE(
2295 p_app_name => G_APP_NAME,
2296 p_msg_name => 'OKL_PDT_RPT_SELECT_SGT',
2297 p_token1 => 'PRICINGENG',
2298 p_token1_value => l_pricing_engine
2299 );
2300 x_return_status := Okl_Api.G_RET_STS_ERROR;
2301 RAISE OKL_API.G_EXCEPTION_ERROR;
2302 END IF;
2303 --rkuttiya end validations for Multi GAAP
2304 END IF;
2305
2306 IF (l_raise_exception) THEN
2307 RAISE OKL_API.G_EXCEPTION_ERROR;
2308 END IF;
2309
2310 -- update the product for any user changes.
2311 update_products(p_api_version => l_api_version,
2312 p_init_msg_list => l_init_msg_list,
2313 x_return_status => l_return_status,
2314 x_msg_count => l_msg_count,
2315 x_msg_data => l_msg_data,
2316 p_pdtv_rec => l_pdtv_rec,
2317 x_pdtv_rec => x_pdtv_rec);
2318
2319
2320
2321
2322 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2323 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2324 ELSIF (l_return_Status = OKL_API.G_RET_STS_ERROR) THEN
2325 RAISE OKL_API.G_EXCEPTION_ERROR;
2326 END IF;
2327
2328
2329 -- update the product status to 'passed' if the changes are valid and all the
2330 -- other validations are through.
2331
2332 l_pdtv_rec.product_status_code := 'PASSED';
2333
2334 update_product_status(p_api_version => l_api_version,
2335 p_init_msg_list => l_init_msg_list,
2336 x_return_status => l_return_status,
2337 x_msg_count => l_msg_count,
2338 x_msg_data => l_msg_data,
2339 p_pdt_status => l_pdtv_rec.product_status_code,
2340 p_pdt_id => l_pdtv_rec.id);
2341
2342
2343 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2344
2345 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2346
2347 ELSIF (l_return_Status = OKL_API.G_RET_STS_ERROR) THEN
2348 RAISE OKL_API.G_EXCEPTION_ERROR;
2349 END IF;
2350 ELSE
2351 -- product cannot be validated while pending approval status/approved/passed status.
2352
2353 Okl_Api.SET_MESSAGE(p_app_name => G_APP_NAME,
2354 p_msg_name => G_PDT_VALDTION_NOT_VALID);
2355 x_return_status := Okl_Api.G_RET_STS_ERROR;
2356 RAISE OKL_API.G_EXCEPTION_ERROR;
2357 END IF;
2358
2359
2360 Okl_Api.END_ACTIVITY(x_msg_count => x_msg_count,
2361 x_msg_data => x_msg_data);
2362
2363 EXCEPTION
2364 WHEN Okl_Api.G_EXCEPTION_ERROR THEN
2365 x_return_status := Okl_Api.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
2366 p_pkg_name => G_PKG_NAME,
2367 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
2368 x_msg_count => x_msg_count,
2369 x_msg_data => x_msg_data,
2370 p_api_type => '_PVT');
2371 WHEN Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR THEN
2372 x_return_status := Okl_Api.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
2373 p_pkg_name => G_PKG_NAME,
2374 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
2375 x_msg_count => x_msg_count,
2376
2377 x_msg_data => x_msg_data,
2378 p_api_type => '_PVT');
2379 WHEN OTHERS THEN
2380 x_return_status := Okl_Api.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
2381 p_pkg_name => G_PKG_NAME,
2382 p_exc_name => 'OTHERS',
2383 x_msg_count => x_msg_count,
2384 x_msg_data => x_msg_data,
2385 p_api_type => '_PVT');
2386
2387 IF (chk_ptl_aes_bc%ISOPEN) THEN
2388 CLOSE chk_ptl_aes_bc;
2389 END IF;
2390
2391
2392 END validate_product;
2393
2394
2395
2396
2397
2398 ---------------------------------------------------------------------------
2399 -- PROCEDURE Validate_Aes_Id
2400 ---------------------------------------------------------------------------
2401 -- Start of comments
2402 --
2403 -- Procedure Name : Validate_Aes_Id
2404 -- Description :
2405 -- Business Rules :
2406 -- Parameters :
2407 -- Version : 1.0
2408 -- End of comments
2409 ---------------------------------------------------------------------------
2410 PROCEDURE Validate_Aes_Id(p_pdtv_rec IN pdtv_rec_type
2411 ,x_return_status OUT NOCOPY VARCHAR2 )
2412 IS
2413
2414 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
2415 l_aes_status VARCHAR2(1);
2416 l_row_notfound BOOLEAN := TRUE;
2417 l_token_1 VARCHAR2(1999);
2418 CURSOR okl_aesv_pk_csr (p_id IN NUMBER) IS
2419
2420 SELECT '1'
2421 FROM okl_ae_tmpt_sets_v
2422 WHERE okl_ae_tmpt_sets_v.id = p_id;
2423
2424 BEGIN
2425 -- initialize return status
2426 x_return_status := Okl_Api.G_RET_STS_SUCCESS;
2427 l_token_1 := Okl_Accounting_Util.Get_Message_Token('OKL_LP_TEMPLATE_SETS','OKL_TEMPLATE_SET');
2428
2429 -- check for data before processing
2430 IF (p_pdtv_rec.aes_id IS NULL) OR
2431 (p_pdtv_rec.aes_id = Okl_Api.G_MISS_NUM) THEN
2432 -- Start of wraper code generated automatically by Debug code generator for Okl_Api.SET_MESSAGE
2433 IF(L_DEBUG_ENABLED='Y') THEN
2434
2435 L_LEVEL_PROCEDURE :=FND_LOG.LEVEL_PROCEDURE;
2436 IS_DEBUG_PROCEDURE_ON := OKL_DEBUG_PUB.Check_Log_On(L_MODULE, L_LEVEL_PROCEDURE);
2437 END IF;
2438 IF(IS_DEBUG_PROCEDURE_ON) THEN
2439 BEGIN
2440 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'Begin Debug OKLRSPDB.pls call Okl_Api.SET_MESSAGE ');
2441 END;
2442 END IF;
2443 Okl_Api.SET_MESSAGE(p_app_name => Okl_Pdt_Pvt.g_app_name
2444 ,p_msg_name => Okl_Pdt_Pvt.g_required_value
2445 ,p_token1 => Okl_Pdt_Pvt.g_col_name_token
2446 ,p_token1_value => l_token_1);
2447 IF(IS_DEBUG_PROCEDURE_ON) THEN
2448 BEGIN
2449 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'End Debug OKLRSPDB.pls call Okl_Api.SET_MESSAGE ');
2450 END;
2451 END IF;
2452 -- End of wraper code generated automatically by Debug code generator for Okl_Api.SET_MESSAGE
2453 x_return_status := Okl_Api.G_RET_STS_ERROR;
2454 RAISE G_EXCEPTION_HALT_PROCESSING;
2455 END IF;
2456
2457 IF (p_pdtv_rec.AES_ID IS NOT NULL) THEN
2458 OPEN okl_aesv_pk_csr(p_pdtv_rec.AES_ID);
2459 FETCH okl_aesv_pk_csr INTO l_aes_status;
2460 l_row_notfound := okl_aesv_pk_csr%NOTFOUND;
2461 CLOSE okl_aesv_pk_csr;
2462 IF (l_row_notfound) THEN
2463 -- Start of wraper code generated automatically by Debug code generator for Okl_Api.set_message
2464 IF(IS_DEBUG_PROCEDURE_ON) THEN
2465 BEGIN
2466 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'Begin Debug OKLRSPDB.pls call Okl_Api.set_message ');
2467 END;
2468 END IF;
2469 Okl_Api.set_message(Okl_Pdt_Pvt.G_APP_NAME, Okl_Pdt_Pvt.G_INVALID_VALUE,Okl_Pdt_Pvt.G_COL_NAME_TOKEN,l_token_1);
2470 IF(IS_DEBUG_PROCEDURE_ON) THEN
2471
2472 BEGIN
2473 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'End Debug OKLRSPDB.pls call Okl_Api.set_message ');
2474 END;
2475 END IF;
2476 -- End of wraper code generated automatically by Debug code generator for Okl_Api.set_message
2477 RAISE G_EXCEPTION_HALT_PROCESSING;
2478 END IF;
2479 END IF;
2480
2481 EXCEPTION
2482 WHEN G_EXCEPTION_HALT_PROCESSING THEN
2483 -- no processing necessary; validation can continue
2484 -- with the next column
2485 NULL;
2486 WHEN OTHERS THEN
2487 -- store SQL error message on message stack for caller
2488 Okl_Api.SET_MESSAGE(p_app_name => Okl_Pdt_Pvt.g_app_name,
2489 p_msg_name => Okl_Pdt_Pvt.g_unexpected_error,
2490 p_token1 => Okl_Pdt_Pvt.g_sqlcode_token,
2491 p_token1_value => SQLCODE,
2492 p_token2 => Okl_Pdt_Pvt.g_sqlerrm_token,
2493 p_token2_value => SQLERRM);
2494
2495
2496 -- notify caller of an UNEXPECTED error
2497 x_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
2498
2499 END Validate_Aes_Id;
2500
2501 ---------------------------------------------------------------------------
2502 -- PROCEDURE Validate_Ptl_Id
2503 ---------------------------------------------------------------------------
2504 -- Start of comments
2505 --
2506 -- Procedure Name : Validate_Ptl_Id
2507 -- Description :
2508 -- Business Rules :
2509 -- Parameters :
2510 -- Version : 1.0
2511 -- End of comments
2512 ---------------------------------------------------------------------------
2513 PROCEDURE Validate_Ptl_Id(p_pdtv_rec IN pdtv_rec_type
2514 ,x_return_status OUT NOCOPY VARCHAR2 )
2515 IS
2516
2517 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
2518 l_ptl_status VARCHAR2(1);
2519 l_row_notfound BOOLEAN := TRUE;
2520 l_token_1 VARCHAR2(1999);
2521
2522 CURSOR okl_ptlv_pk_csr (p_id IN NUMBER) IS
2523 SELECT '1'
2524 FROM okl_pdt_templates_v
2525 WHERE okl_pdt_templates_v.id = p_id;
2526
2527 BEGIN
2528 -- initialize return status
2529 x_return_status := Okl_Api.G_RET_STS_SUCCESS;
2530
2531 l_token_1 := Okl_Accounting_Util.Get_Message_Token('OKL_LP_PDT_TMPL_CREATE_UPDATE','OKL_PRODUCT_TEMPLATE');
2532
2533 -- check for data before processing
2534 IF (p_pdtv_rec.ptl_id IS NULL) OR
2535 (p_pdtv_rec.ptl_id = Okl_Api.G_MISS_NUM) THEN
2536 -- Start of wraper code generated automatically by Debug code generator for Okl_Api.SET_MESSAGE
2537
2538 IF(L_DEBUG_ENABLED='Y') THEN
2539 L_LEVEL_PROCEDURE :=FND_LOG.LEVEL_PROCEDURE;
2540 IS_DEBUG_PROCEDURE_ON := OKL_DEBUG_PUB.Check_Log_On(L_MODULE, L_LEVEL_PROCEDURE);
2541 END IF;
2542 IF(IS_DEBUG_PROCEDURE_ON) THEN
2543 BEGIN
2544 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'Begin Debug OKLRSPDB.pls call Okl_Api.SET_MESSAGE ');
2545 END;
2546 END IF;
2547 Okl_Api.SET_MESSAGE(p_app_name => Okl_Pdt_Pvt.g_app_name
2548 ,p_msg_name => Okl_Pdt_Pvt.g_required_value
2549 ,p_token1 => Okl_Pdt_Pvt.g_col_name_token
2550 ,p_token1_value => l_token_1);
2551 IF(IS_DEBUG_PROCEDURE_ON) THEN
2552 BEGIN
2553 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'End Debug OKLRSPDB.pls call Okl_Api.SET_MESSAGE ');
2554 END;
2555 END IF;
2556 -- End of wraper code generated automatically by Debug code generator for Okl_Api.SET_MESSAGE
2557 x_return_status := Okl_Api.G_RET_STS_ERROR;
2558 RAISE G_EXCEPTION_HALT_PROCESSING;
2559 END IF;
2560
2561 IF (p_pdtv_rec.PTL_ID IS NOT NULL) THEN
2562 OPEN okl_ptlv_pk_csr(p_pdtv_rec.PTL_ID);
2563 FETCH okl_ptlv_pk_csr INTO l_ptl_status;
2564 l_row_notfound := okl_ptlv_pk_csr%NOTFOUND;
2565 CLOSE okl_ptlv_pk_csr;
2566 IF (l_row_notfound) THEN
2567 -- Start of wraper code generated automatically by Debug code generator for Okl_Api.set_message
2568 IF(IS_DEBUG_PROCEDURE_ON) THEN
2569 BEGIN
2570 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'Begin Debug OKLRSPDB.pls call Okl_Api.set_message ');
2571
2572 END;
2573 END IF;
2574 Okl_Api.set_message(Okl_Pdt_Pvt.G_APP_NAME, Okl_Pdt_Pvt.G_INVALID_VALUE,Okl_Pdt_Pvt.G_COL_NAME_TOKEN,l_token_1);
2575 IF(IS_DEBUG_PROCEDURE_ON) THEN
2576 BEGIN
2577 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'End Debug OKLRSPDB.pls call Okl_Api.set_message ');
2578 END;
2579 END IF;
2580 -- End of wraper code generated automatically by Debug code generator for Okl_Api.set_message
2581 RAISE G_EXCEPTION_HALT_PROCESSING;
2582 END IF;
2583 END IF;
2584
2585 EXCEPTION
2586 WHEN G_EXCEPTION_HALT_PROCESSING THEN
2587 -- no processing necessary; validation can continue
2588 -- with the next column
2589 NULL;
2590 WHEN OTHERS THEN
2591 -- store SQL error message on message stack for caller
2592 Okl_Api.SET_MESSAGE(p_app_name => Okl_Pdt_Pvt.g_app_name,
2593 p_msg_name => Okl_Pdt_Pvt.g_unexpected_error,
2594 p_token1 => Okl_Pdt_Pvt.g_sqlcode_token,
2595 p_token1_value => SQLCODE,
2596
2597 p_token2 => Okl_Pdt_Pvt.g_sqlerrm_token,
2598 p_token2_value => SQLERRM);
2599
2600 -- notify caller of an UNEXPECTED error
2601 x_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
2602
2603 END Validate_Ptl_Id;
2604
2605 ---------------------------------------------------------------------------
2606 -- PROCEDURE Validate_From_Date
2607 ---------------------------------------------------------------------------
2608 -- Start of comments
2609 --
2610 -- Procedure Name : Validate_From_Date
2611 -- Description :
2612 -- Business Rules :
2613 -- Parameters :
2614 -- Version : 1.0
2615 -- End of comments
2616 ---------------------------------------------------------------------------
2617 PROCEDURE Validate_From_Date(p_pdtv_rec IN pdtv_rec_type
2618 ,x_return_status OUT NOCOPY VARCHAR2 )
2619 IS
2620
2621 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
2622 l_token_1 VARCHAR2(1999);
2623
2624
2625 BEGIN
2626 -- initialize return status
2627 x_return_status := Okl_Api.G_RET_STS_SUCCESS;
2628
2629 l_token_1 := Okl_Accounting_Util.Get_Message_Token('OKL_LP_PRODUCT_CRUPD','OKL_EFFECTIVE_FROM');
2630
2631 -- check for data before processing
2632 IF (p_pdtv_rec.from_date IS NULL) OR
2633 (p_pdtv_rec.from_date = Okl_Api.G_MISS_DATE) THEN
2634 -- Start of wraper code generated automatically by Debug code generator for Okl_Api.SET_MESSAGE
2635 IF(L_DEBUG_ENABLED='Y') THEN
2636 L_LEVEL_PROCEDURE :=FND_LOG.LEVEL_PROCEDURE;
2637 IS_DEBUG_PROCEDURE_ON := OKL_DEBUG_PUB.Check_Log_On(L_MODULE, L_LEVEL_PROCEDURE);
2638 END IF;
2639 IF(IS_DEBUG_PROCEDURE_ON) THEN
2640
2641 BEGIN
2642 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'Begin Debug OKLRSPDB.pls call Okl_Api.SET_MESSAGE ');
2643 END;
2644 END IF;
2645 Okl_Api.SET_MESSAGE(p_app_name => Okl_Pdt_Pvt.g_app_name
2646 ,p_msg_name => Okl_Pdt_Pvt.g_required_value
2647 ,p_token1 => Okl_Pdt_Pvt.g_col_name_token
2648 ,p_token1_value => l_token_1);
2649 IF(IS_DEBUG_PROCEDURE_ON) THEN
2650 BEGIN
2651 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'End Debug OKLRSPDB.pls call Okl_Api.SET_MESSAGE ');
2652 END;
2653 END IF;
2654 -- End of wraper code generated automatically by Debug code generator for Okl_Api.SET_MESSAGE
2655 x_return_status := Okl_Api.G_RET_STS_ERROR;
2656 RAISE G_EXCEPTION_HALT_PROCESSING;
2657 END IF;
2658
2659 EXCEPTION
2660 WHEN G_EXCEPTION_HALT_PROCESSING THEN
2661 -- no processing necessary; validation can continue
2662 -- with the next column
2663 NULL;
2664
2665 WHEN OTHERS THEN
2666 -- store SQL error message on message stack for caller
2667 Okl_Api.SET_MESSAGE(p_app_name => Okl_Pdt_Pvt.g_app_name,
2668 p_msg_name => Okl_Pdt_Pvt.g_unexpected_error,
2669 p_token1 => Okl_Pdt_Pvt.g_sqlcode_token,
2670 p_token1_value => SQLCODE,
2671
2672 p_token2 => Okl_Pdt_Pvt.g_sqlerrm_token,
2673 p_token2_value => SQLERRM);
2674
2675 -- notify caller of an UNEXPECTED error
2676 x_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
2677
2678 END Validate_From_Date;
2679
2680 ---------------------------------------------------------------------------
2681 -- PROCEDURE Validate_Name
2682 ---------------------------------------------------------------------------
2683 -- Start of comments
2684 --
2685 -- Procedure Name : Validate_Name
2686 -- Description :
2687 -- Business Rules :
2688 -- Parameters :
2689 -- Version : 1.0
2690 -- End of comments
2691 ---------------------------------------------------------------------------
2692 PROCEDURE Validate_Name(p_pdtv_rec IN OUT NOCOPY pdtv_rec_type
2693 ,x_return_status OUT NOCOPY VARCHAR2 )
2694 IS
2695
2696 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
2697
2698 l_token_1 VARCHAR2(1999);
2699
2700 BEGIN
2701 -- initialize return status
2702 x_return_status := Okl_Api.G_RET_STS_SUCCESS;
2703 l_token_1 := Okl_Accounting_Util.Get_Message_Token('OKL_LP_PRODUCT_CRUPD','OKL_NAME');
2704
2705 -- check for data before processing
2706 IF (p_pdtv_rec.name IS NULL) OR
2707 (p_pdtv_rec.name = Okl_Api.G_MISS_CHAR) THEN
2708 -- Start of wraper code generated automatically by Debug code generator for Okl_Api.SET_MESSAGE
2709 IF(L_DEBUG_ENABLED='Y') THEN
2710 L_LEVEL_PROCEDURE :=FND_LOG.LEVEL_PROCEDURE;
2711 IS_DEBUG_PROCEDURE_ON := OKL_DEBUG_PUB.Check_Log_On(L_MODULE, L_LEVEL_PROCEDURE);
2712 END IF;
2713 IF(IS_DEBUG_PROCEDURE_ON) THEN
2714 BEGIN
2715 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'Begin Debug OKLRSPDB.pls call Okl_Api.SET_MESSAGE ');
2716 END;
2717 END IF;
2718 Okl_Api.SET_MESSAGE(p_app_name => Okl_Pdt_Pvt.g_app_name
2719 ,p_msg_name => Okl_Pdt_Pvt.g_required_value
2720 ,p_token1 => Okl_Pdt_Pvt.g_col_name_token
2721 ,p_token1_value => l_token_1);
2722 IF(IS_DEBUG_PROCEDURE_ON) THEN
2723 BEGIN
2724 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'End Debug OKLRSPDB.pls call Okl_Api.SET_MESSAGE ');
2725
2726 END;
2727 END IF;
2728 -- End of wraper code generated automatically by Debug code generator for Okl_Api.SET_MESSAGE
2729 x_return_status := Okl_Api.G_RET_STS_ERROR;
2730 RAISE G_EXCEPTION_HALT_PROCESSING;
2731 END IF;
2732 p_pdtv_rec.name := Okl_Accounting_Util.okl_upper(p_pdtv_rec.name);
2733 EXCEPTION
2734 WHEN G_EXCEPTION_HALT_PROCESSING THEN
2735 -- no processing necessary; validation can continue
2736 -- with the next column
2737 NULL;
2738
2739 WHEN OTHERS THEN
2740 -- store SQL error message on message stack for caller
2741 Okl_Api.SET_MESSAGE(p_app_name => Okl_Pdt_Pvt.g_app_name,
2742 p_msg_name => Okl_Pdt_Pvt.g_unexpected_error,
2743
2744 p_token1 => Okl_Pdt_Pvt.g_sqlcode_token,
2745 p_token1_value => SQLCODE,
2746 p_token2 => Okl_Pdt_Pvt.g_sqlerrm_token,
2747 p_token2_value => SQLERRM);
2748
2749 -- notify caller of an UNEXPECTED error
2750 x_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
2751
2752 END Validate_Name;
2753
2754
2755 ---------------------------------------------------------------------------
2756 -- FUNCTION Validate_Attributes
2757 ---------------------------------------------------------------------------
2758 -- Start of comments
2759 --
2760 -- Function Name : Validate_Attributes
2761 -- Description :
2762 -- Business Rules :
2763 -- Parameters :
2764 -- Version : 1.0
2765 -- End of comments
2766 ---------------------------------------------------------------------------
2767
2768 FUNCTION Validate_Attributes (
2769 p_pdtv_rec IN OUT NOCOPY pdtv_rec_type
2770 ) RETURN VARCHAR2 IS
2771
2772 x_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
2773 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
2774 l_pdtv_rec pdtv_rec_type := p_pdtv_rec;
2775 BEGIN
2776
2777 -- Validate_Name
2778 Validate_Name(l_pdtv_rec, x_return_status);
2779 IF (x_return_status <> Okl_Api.G_RET_STS_SUCCESS) THEN
2780 IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
2781 -- need to leave
2782 l_return_status := x_return_status;
2783 RAISE G_EXCEPTION_HALT_PROCESSING;
2784 ELSE
2785 -- record that there was an error
2786 l_return_status := x_return_status;
2787 END IF;
2788 END IF;
2789
2790 -- Validate_Aes_Id
2791 Validate_Aes_Id(l_pdtv_rec, x_return_status);
2792 IF (x_return_status <> Okl_Api.G_RET_STS_SUCCESS) THEN
2793 IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
2794 -- need to leave
2795 l_return_status := x_return_status;
2796 RAISE G_EXCEPTION_HALT_PROCESSING;
2797 ELSE
2798
2799 -- record that there was an error
2800 l_return_status := x_return_status;
2801 END IF;
2802 END IF;
2803
2804 -- Validate_Ptl_Id
2805 Validate_Ptl_Id(l_pdtv_rec, x_return_status);
2806 IF (x_return_status <> Okl_Api.G_RET_STS_SUCCESS) THEN
2807 IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
2808 -- need to leave
2809 l_return_status := x_return_status;
2810 RAISE G_EXCEPTION_HALT_PROCESSING;
2811 ELSE
2812 -- record that there was an error
2813 l_return_status := x_return_status;
2814 END IF;
2815 END IF;
2816
2817 -- Validate_From_Date
2818 Validate_From_Date(l_pdtv_rec, x_return_status);
2819 IF (x_return_status <> Okl_Api.G_RET_STS_SUCCESS) THEN
2820 IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
2821 -- need to leave
2822 l_return_status := x_return_status;
2823 RAISE G_EXCEPTION_HALT_PROCESSING;
2824 ELSE
2825 -- record that there was an error
2826 l_return_status := x_return_status;
2827
2828 END IF;
2829 END IF;
2830
2831 p_pdtv_rec := l_pdtv_rec;
2832
2833 RETURN(l_return_status);
2834 EXCEPTION
2835 WHEN G_EXCEPTION_HALT_PROCESSING THEN
2836 -- just come out with return status
2837 NULL;
2838 RETURN (l_return_status);
2839
2840 WHEN OTHERS THEN
2841 -- store SQL error message on message stack for caller
2842 Okl_Api.SET_MESSAGE(p_app_name => Okl_Pdt_Pvt.g_app_name,
2843 p_msg_name => Okl_Pdt_Pvt.g_unexpected_error,
2844 p_token1 => Okl_Pdt_Pvt.g_sqlcode_token,
2845 p_token1_value => SQLCODE,
2846
2847 p_token2 => Okl_Pdt_Pvt.g_sqlerrm_token,
2848 p_token2_value => SQLERRM);
2849 -- notify caller of an UNEXPECTED error
2850 l_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
2851 RETURN(l_return_status);
2852
2853 END Validate_Attributes;
2854
2855
2856 ---------------------------------------------------------------------------
2857 -- PROCEDURE reorganize_inputs
2858 -- This procedure is to reset the attributes in the input structure based
2859 -- on the data from database
2860 ---------------------------------------------------------------------------
2861 PROCEDURE reorganize_inputs (
2862 p_upd_pdtv_rec IN OUT NOCOPY pdtv_rec_type,
2863 p_db_pdtv_rec IN pdtv_rec_type
2864 ) IS
2865 l_upd_pdtv_rec pdtv_rec_type;
2866 l_db_pdtv_rec pdtv_rec_type;
2867 BEGIN
2868
2869 /* create a temporary record with all relevant details from db and upd records */
2870 l_upd_pdtv_rec := p_upd_pdtv_rec;
2871
2872 l_db_pdtv_rec := p_db_pdtv_rec;
2873
2874 IF l_upd_pdtv_rec.product_status_code = l_db_pdtv_rec.product_status_code THEN
2875
2876 l_upd_pdtv_rec.product_status_code := Okl_Api.G_MISS_CHAR;
2877 END IF;
2878
2879 IF l_upd_pdtv_rec.description = l_db_pdtv_rec.description THEN
2880
2881 l_upd_pdtv_rec.description := Okl_Api.G_MISS_CHAR;
2882 END IF;
2883
2884 IF to_date(to_char(l_upd_pdtv_rec.from_date, 'DD/MM/YYYY'), 'DD/MM/YYYY') = to_date(to_char(l_db_pdtv_rec.from_date, 'DD/MM/YYYY'), 'DD/MM/YYYY') THEN
2885 l_upd_pdtv_rec.from_date := Okl_Api.G_MISS_DATE;
2886 END IF;
2887
2888 IF to_date(to_char(l_upd_pdtv_rec.TO_DATE, 'DD/MM/YYYY'), 'DD/MM/YYYY') = to_date(to_char(l_db_pdtv_rec.TO_DATE, 'DD/MM/YYYY'), 'DD/MM/YYYY') THEN
2889 l_upd_pdtv_rec.TO_DATE := Okl_Api.G_MISS_DATE;
2890 END IF;
2891
2892 IF l_upd_pdtv_rec.legacy_product_yn = l_db_pdtv_rec.legacy_product_yn THEN
2893 l_upd_pdtv_rec.legacy_product_yn := Okl_Api.G_MISS_CHAR;
2894 END IF;
2895
2896 IF l_upd_pdtv_rec.aes_id = l_db_pdtv_rec.aes_id THEN
2897
2898 l_upd_pdtv_rec.aes_id := Okl_Api.G_MISS_NUM;
2899
2900 END IF;
2901
2902 IF l_upd_pdtv_rec.ptl_id = l_db_pdtv_rec.ptl_id THEN
2903 l_upd_pdtv_rec.ptl_id := Okl_Api.G_MISS_NUM;
2904 END IF;
2905
2906 IF l_upd_pdtv_rec.reporting_pdt_id = l_db_pdtv_rec.reporting_pdt_id THEN
2907 l_upd_pdtv_rec.reporting_pdt_id := Okl_Api.G_MISS_NUM;
2908 END IF;
2909
2910 IF l_upd_pdtv_rec.attribute_category = l_db_pdtv_rec.attribute_category THEN
2911 l_upd_pdtv_rec.attribute_category := Okl_Api.G_MISS_CHAR;
2912 END IF;
2913
2914 IF l_upd_pdtv_rec.attribute1 = l_db_pdtv_rec.attribute1 THEN
2915 l_upd_pdtv_rec.attribute1 := Okl_Api.G_MISS_CHAR;
2916 END IF;
2917
2918 IF l_upd_pdtv_rec.attribute2 = l_db_pdtv_rec.attribute2 THEN
2919 l_upd_pdtv_rec.attribute2 := Okl_Api.G_MISS_CHAR;
2920 END IF;
2921
2922 IF l_upd_pdtv_rec.attribute3 = l_db_pdtv_rec.attribute3 THEN
2923 l_upd_pdtv_rec.attribute3 := Okl_Api.G_MISS_CHAR;
2924 END IF;
2925
2926 IF l_upd_pdtv_rec.attribute4 = l_db_pdtv_rec.attribute4 THEN
2927 l_upd_pdtv_rec.attribute4 := Okl_Api.G_MISS_CHAR;
2928 END IF;
2929
2930
2931 IF l_upd_pdtv_rec.attribute5 = l_db_pdtv_rec.attribute5 THEN
2932 l_upd_pdtv_rec.attribute5 := Okl_Api.G_MISS_CHAR;
2933 END IF;
2934
2935 IF l_upd_pdtv_rec.attribute6 = l_db_pdtv_rec.attribute6 THEN
2936 l_upd_pdtv_rec.attribute6 := Okl_Api.G_MISS_CHAR;
2937 END IF;
2938
2939 IF l_upd_pdtv_rec.attribute7 = l_db_pdtv_rec.attribute7 THEN
2940 l_upd_pdtv_rec.attribute7 := Okl_Api.G_MISS_CHAR;
2941 END IF;
2942
2943 IF l_upd_pdtv_rec.attribute8 = l_db_pdtv_rec.attribute8 THEN
2944 l_upd_pdtv_rec.attribute8 := Okl_Api.G_MISS_CHAR;
2945 END IF;
2946
2947 IF l_upd_pdtv_rec.attribute9 = l_db_pdtv_rec.attribute9 THEN
2948 l_upd_pdtv_rec.attribute9 := Okl_Api.G_MISS_CHAR;
2949 END IF;
2950
2951 IF l_upd_pdtv_rec.attribute10 = l_db_pdtv_rec.attribute10 THEN
2952 l_upd_pdtv_rec.attribute10 := Okl_Api.G_MISS_CHAR;
2953 END IF;
2954
2955 IF l_upd_pdtv_rec.attribute11 = l_db_pdtv_rec.attribute11 THEN
2956 l_upd_pdtv_rec.attribute11 := Okl_Api.G_MISS_CHAR;
2957
2958 END IF;
2959
2960 IF l_upd_pdtv_rec.attribute12 = l_db_pdtv_rec.attribute12 THEN
2961 l_upd_pdtv_rec.attribute12 := Okl_Api.G_MISS_CHAR;
2962 END IF;
2963
2964 IF l_upd_pdtv_rec.attribute13 = l_db_pdtv_rec.attribute13 THEN
2965 l_upd_pdtv_rec.attribute13 := Okl_Api.G_MISS_CHAR;
2966 END IF;
2967
2968 IF l_upd_pdtv_rec.attribute14 = l_db_pdtv_rec.attribute14 THEN
2969 l_upd_pdtv_rec.attribute14 := Okl_Api.G_MISS_CHAR;
2970 END IF;
2971
2972
2973 IF l_upd_pdtv_rec.attribute15 = l_db_pdtv_rec.attribute15 THEN
2974 l_upd_pdtv_rec.attribute15 := Okl_Api.G_MISS_CHAR;
2975 END IF;
2976
2977 p_upd_pdtv_rec := l_upd_pdtv_rec;
2978
2979 END reorganize_inputs;
2980
2981 ---------------------------------------------------------------------------
2982 -- FUNCTION defaults_to_actuals
2983 -- This function creates an output record with changed information from the
2984 -- input structure and unchanged details from the database
2985 ---------------------------------------------------------------------------
2986 FUNCTION defaults_to_actuals (
2987 p_upd_pdtv_rec IN pdtv_rec_type,
2988 p_db_pdtv_rec IN pdtv_rec_type
2989 ) RETURN pdtv_rec_type IS
2990 l_pdtv_rec pdtv_rec_type;
2991 BEGIN
2992 /* create a temporary record with all relevant details from db and upd records */
2993 l_pdtv_rec := p_db_pdtv_rec;
2994
2995 IF p_upd_pdtv_rec.aes_id <> Okl_Api.G_MISS_NUM THEN
2996 l_pdtv_rec.aes_id := p_upd_pdtv_rec.aes_id;
2997 END IF;
2998
2999 IF p_upd_pdtv_rec.ptl_id <> Okl_Api.G_MISS_NUM THEN
3000
3001 l_pdtv_rec.ptl_id := p_upd_pdtv_rec.ptl_id;
3002 END IF;
3003
3004 IF p_upd_pdtv_rec.reporting_pdt_id <> Okl_Api.G_MISS_NUM THEN
3005 l_pdtv_rec.reporting_pdt_id := p_upd_pdtv_rec.reporting_pdt_id;
3006 END IF;
3007
3008 IF p_upd_pdtv_rec.description <> Okl_Api.G_MISS_CHAR THEN
3009 l_pdtv_rec.description := p_upd_pdtv_rec.description;
3010 END IF;
3011
3012
3013 IF p_upd_pdtv_rec.product_status_code <> Okl_Api.G_MISS_CHAR THEN
3014 l_pdtv_rec.product_status_code := p_upd_pdtv_rec.product_status_code;
3015 END IF;
3016
3017
3018 IF p_upd_pdtv_rec.legacy_product_yn <> Okl_Api.G_MISS_CHAR THEN
3019 l_pdtv_rec.legacy_product_yn := p_upd_pdtv_rec.legacy_product_yn;
3020 END IF;
3021
3022 IF p_upd_pdtv_rec.from_date <> Okl_Api.G_MISS_DATE THEN
3023 l_pdtv_rec.from_date := p_upd_pdtv_rec.from_date;
3024 END IF;
3025
3026 IF p_upd_pdtv_rec.TO_DATE <> Okl_Api.G_MISS_DATE THEN
3027 l_pdtv_rec.TO_DATE := p_upd_pdtv_rec.TO_DATE;
3028 END IF;
3029
3030 IF p_upd_pdtv_rec.attribute_category <> Okl_Api.G_MISS_CHAR THEN
3031
3032 l_pdtv_rec.attribute_category := p_upd_pdtv_rec.attribute_category;
3033 END IF;
3034
3035 IF p_upd_pdtv_rec.attribute1 <> Okl_Api.G_MISS_CHAR THEN
3036 l_pdtv_rec.attribute1 := p_upd_pdtv_rec.attribute1;
3037 END IF;
3038
3039 IF p_upd_pdtv_rec.attribute2 <> Okl_Api.G_MISS_CHAR THEN
3040 l_pdtv_rec.attribute2 := p_upd_pdtv_rec.attribute2;
3041 END IF;
3042
3043 IF p_upd_pdtv_rec.attribute3 <> Okl_Api.G_MISS_CHAR THEN
3044 l_pdtv_rec.attribute3 := p_upd_pdtv_rec.attribute3;
3045 END IF;
3046
3047 IF p_upd_pdtv_rec.attribute4 <> Okl_Api.G_MISS_CHAR THEN
3048 l_pdtv_rec.attribute4 := p_upd_pdtv_rec.attribute4;
3049 END IF;
3050
3051 IF p_upd_pdtv_rec.attribute5 <> Okl_Api.G_MISS_CHAR THEN
3052 l_pdtv_rec.attribute5 := p_upd_pdtv_rec.attribute5;
3053 END IF;
3054
3055 IF p_upd_pdtv_rec.attribute6 <> Okl_Api.G_MISS_CHAR THEN
3056 l_pdtv_rec.attribute6 := p_upd_pdtv_rec.attribute6;
3057 END IF;
3058
3059 IF p_upd_pdtv_rec.attribute7 <> Okl_Api.G_MISS_CHAR THEN
3060 l_pdtv_rec.attribute7 := p_upd_pdtv_rec.attribute7;
3061 END IF;
3062
3063 IF p_upd_pdtv_rec.attribute8 <> Okl_Api.G_MISS_CHAR THEN
3064 l_pdtv_rec.attribute8 := p_upd_pdtv_rec.attribute8;
3065 END IF;
3066
3067
3068 IF p_upd_pdtv_rec.attribute9 <> Okl_Api.G_MISS_CHAR THEN
3069 l_pdtv_rec.attribute9 := p_upd_pdtv_rec.attribute9;
3070 END IF;
3071
3072
3073 IF p_upd_pdtv_rec.attribute10 <> Okl_Api.G_MISS_CHAR THEN
3074 l_pdtv_rec.attribute10 := p_upd_pdtv_rec.attribute10;
3075 END IF;
3076
3077 IF p_upd_pdtv_rec.attribute11 <> Okl_Api.G_MISS_CHAR THEN
3078 l_pdtv_rec.attribute11 := p_upd_pdtv_rec.attribute11;
3079 END IF;
3080
3081 IF p_upd_pdtv_rec.attribute12 <> Okl_Api.G_MISS_CHAR THEN
3082 l_pdtv_rec.attribute12 := p_upd_pdtv_rec.attribute12;
3083 END IF;
3084
3085 IF p_upd_pdtv_rec.attribute13 <> Okl_Api.G_MISS_CHAR THEN
3086 l_pdtv_rec.attribute13 := p_upd_pdtv_rec.attribute13;
3087 END IF;
3088
3089 IF p_upd_pdtv_rec.attribute14 <> Okl_Api.G_MISS_CHAR THEN
3090 l_pdtv_rec.attribute14 := p_upd_pdtv_rec.attribute14;
3091 END IF;
3092
3093 IF p_upd_pdtv_rec.attribute15 <> Okl_Api.G_MISS_CHAR THEN
3094 l_pdtv_rec.attribute15 := p_upd_pdtv_rec.attribute15;
3095 END IF;
3096
3097 RETURN l_pdtv_rec;
3098 END defaults_to_actuals;
3099
3100 ---------------------------------------------------------------------------
3101
3102 -- PROCEDURE check_updates
3103 -- To verify whether the requested changes from the screen are valid or not
3104 ---------------------------------------------------------------------------
3105 PROCEDURE check_updates (
3106 p_api_version IN NUMBER,
3107 p_init_msg_list IN VARCHAR2 DEFAULT okl_api.G_FALSE,
3108 x_msg_count OUT NOCOPY NUMBER,
3109 x_msg_data OUT NOCOPY VARCHAR2,
3110 p_upd_pdtv_rec IN pdtv_rec_type,
3111 p_db_pdtv_rec IN pdtv_rec_type,
3112 p_pdtv_rec IN pdtv_rec_type,
3113 x_return_status OUT NOCOPY VARCHAR2
3114 ) IS
3115 l_upd_pdtv_rec pdtv_rec_type;
3116 l_pdtv_rec pdtv_rec_type;
3117 l_db_pdtv_rec pdtv_rec_type;
3118 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
3119 l_valid BOOLEAN;
3120 l_sysdate DATE := to_date(to_char(SYSDATE, 'DD/MM/YYYY'), 'DD/MM/YYYY');
3121 BEGIN
3122
3123 x_return_status := Okl_Api.G_RET_STS_SUCCESS;
3124 l_pdtv_rec := p_pdtv_rec;
3125 l_upd_pdtv_rec := p_upd_pdtv_rec;
3126 l_db_pdtv_rec := p_db_pdtv_rec;
3127
3128 /* check for start date greater than sysdate */
3129 /*IF to_date(to_char(l_upd_pdtv_rec.from_date, 'DD/MM/YYYY'), 'DD/MM/YYYY') <> to_date(to_char(OKL_API.G_MISS_DATE, 'DD/MM/YYYY'), 'DD/MM/YYYY') AND
3130 to_date(to_char(l_upd_pdtv_rec.from_date, 'DD/MM/YYYY'), 'DD/MM/YYYY') < l_sysdate THEN
3131 OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
3132 p_msg_name => G_START_DATE);
3133
3134 x_return_status := OKL_API.G_RET_STS_ERROR;
3135 RAISE G_EXCEPTION_HALT_PROCESSING;
3136 END IF; */
3137
3138 /* check for the records with from and to dates less than sysdate */
3139
3140 /* IF to_date(to_char(l_upd_pdtv_rec.TO_DATE, 'DD/MM/YYYY'), 'DD/MM/YYYY') < l_sysdate THEN
3141 OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
3142 p_msg_name => G_PAST_RECORDS);
3143 x_return_status := OKL_API.G_RET_STS_ERROR;
3144 RAISE G_EXCEPTION_HALT_PROCESSING;
3145 END IF;*/
3146
3147 /* if the start date is in the past, the start date cannot be
3148 modified */
3149 /* IF to_date(to_char(l_upd_pdtv_rec.from_date, 'DD/MM/YYYY'), 'DD/MM/YYYY') <> to_date(to_char(OKL_API.G_MISS_DATE, 'DD/MM/YYYY'), 'DD/MM/YYYY') AND
3150 to_date(to_char(P_db_pdtv_rec.from_date, 'DD/MM/YYYY'), 'DD/MM/YYYY') <= l_sysdate THEN
3151 OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
3152 p_msg_name => 'OKL_NOT_ALLOWED',
3153 p_token1 => G_COL_NAME_TOKEN,
3154 p_token1_value => 'START_DATE');
3155 x_return_status := OKL_API.G_RET_STS_ERROR;
3156 RAISE G_EXCEPTION_HALT_PROCESSING;
3157 END IF; */
3158
3159 IF p_upd_pdtv_rec.from_date <> Okl_Api.G_MISS_DATE OR
3160 p_upd_pdtv_rec.TO_DATE <> Okl_Api.G_MISS_DATE OR
3161 p_upd_pdtv_rec.reporting_pdt_id <> Okl_Api.G_MISS_NUM OR
3162 p_upd_pdtv_rec.aes_id <> Okl_Api.G_MISS_NUM OR
3163 p_upd_pdtv_rec.ptl_id <> Okl_Api.G_MISS_NUM OR
3164 -- Handle the condition when the Reporting product is being passed as NULL where as the DB has it
3165 ( p_upd_pdtv_rec.reporting_pdt_id IS NULL AND l_db_pdtv_rec.reporting_pdt_id IS NOT NULL )
3166 THEN
3167
3168
3169 /* call check_overlaps */
3170 /* Okl_Setuppdttemplates_Pvt.check_overlaps(p_id => l_upd_pdtv_rec.id,
3171 p_name => l_pdtv_rec.name,
3172 p_from_date => l_pdtv_rec.from_date,
3173 p_to_date => l_pdtv_rec.TO_DATE,
3174
3175
3176 p_table => 'Okl_Products_V',
3177 x_return_status => l_return_status,
3178 x_valid => l_valid);
3179 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3180 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
3181 RAISE G_EXCEPTION_HALT_PROCESSING;
3182 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) OR
3183 (l_return_status = OKL_API.G_RET_STS_SUCCESS AND
3184 l_valid <> TRUE) THEN
3185 x_return_status := OKL_API.G_RET_STS_ERROR;
3186 RAISE G_EXCEPTION_HALT_PROCESSING;
3187 END IF;*/
3188
3189 /* call check_constraints */
3190 Check_Constraints(p_api_version => p_api_version,
3191 p_init_msg_list => p_init_msg_list,
3192 x_msg_count => x_msg_count,
3193 x_msg_data => x_msg_data,
3194 p_upd_pdtv_rec => l_upd_pdtv_rec,
3195 p_pdtv_rec => l_pdtv_rec,
3196 p_db_pdtv_rec => l_db_pdtv_rec,
3197 x_return_status => l_return_status,
3198 x_valid => l_valid);
3199
3200 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
3201 x_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
3202 RAISE G_EXCEPTION_HALT_PROCESSING;
3203 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) OR
3204 (l_return_status = Okl_Api.G_RET_STS_SUCCESS AND
3205
3206 l_valid <> TRUE) THEN
3207 x_return_status := Okl_Api.G_RET_STS_ERROR;
3208 RAISE G_EXCEPTION_HALT_PROCESSING;
3209 END IF;
3210
3211
3212 IF l_valid <> TRUE THEN
3213 x_return_status := Okl_Api.G_RET_STS_ERROR;
3214 RAISE G_EXCEPTION_HALT_PROCESSING;
3215 END IF;
3216
3217 END IF;
3218
3219 EXCEPTION
3220 WHEN G_EXCEPTION_HALT_PROCESSING THEN
3221 -- no processing necessary; validation can continue
3222 -- with the next column
3223 NULL;
3224
3225 WHEN OTHERS THEN
3226 -- store SQL error message on message stack for caller
3227 Okl_Api.SET_MESSAGE(p_app_name => G_APP_NAME,
3228 p_msg_name => G_UNEXPECTED_ERROR,
3229 p_token1 => G_SQLCODE_TOKEN,
3230 p_token1_value => SQLCODE,
3231 p_token2 => G_SQLERRM_TOKEN,
3232 p_token2_value => SQLERRM );
3233 -- notify caller of an UNEXPECTED error
3234 x_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
3235
3236 END check_updates;
3237
3238
3239 ---------------------------------------------------------------------------
3240 -- PROCEDURE determine_action for: OKL_PRODUCTS_V
3241 -- This function helps in determining the various checks to be performed
3242 -- for the new/updated record and also helps in determining whether a new
3243 -- version is required or not
3244 ---------------------------------------------------------------------------
3245 FUNCTION determine_action (
3246 p_upd_pdtv_rec IN pdtv_rec_type,
3247 p_db_pdtv_rec IN pdtv_rec_type,
3248 p_date IN DATE
3249 ) RETURN VARCHAR2 IS
3250 l_action VARCHAR2(1);
3251 l_sysdate DATE := to_date(to_char(SYSDATE, 'DD/MM/YYYY'), 'DD/MM/YYYY');
3252 BEGIN
3253 /* Scenario 1: Only description and/or descriptive flexfield changes */
3254
3255 IF (p_upd_pdtv_rec.from_date = Okl_Api.G_MISS_DATE AND
3256 p_upd_pdtv_rec.TO_DATE = Okl_Api.G_MISS_DATE AND
3257 p_upd_pdtv_rec.aes_id = Okl_Api.G_MISS_NUM AND
3258 p_upd_pdtv_rec.ptl_id = Okl_Api.G_MISS_NUM AND
3259 p_upd_pdtv_rec.reporting_pdt_id = Okl_Api.G_MISS_NUM) THEN
3260 --p_db_pdtv_rec.from_date = l_sysdate THEN
3261 l_action := '1';
3262 /* Scenario 2: only changing description/descriptive flexfield changes
3263 and end date for all records or changing anything for a future record other
3264 than start date or modified start date is less than existing start date */
3265 ELSE
3266 l_action := '2';
3267 END IF;
3268
3269 RETURN(l_action);
3270 END determine_action;
3271
3272 ---------------------------------------------------------------------------
3273 -- PROCEDURE get_prod_strm_types for: OKL_PRODUCTS_V
3274
3275 -- To fetch the product Stream Types that are attached to the existing
3276 -- version of the product
3277 ---------------------------------------------------------------------------
3278 PROCEDURE get_prod_strm_types (p_upd_pdtv_rec IN pdtv_rec_type,
3279 p_pdtv_rec IN pdtv_rec_type,
3280
3281 p_flag IN VARCHAR2,
3282 x_return_status OUT NOCOPY VARCHAR2,
3283 x_count OUT NOCOPY NUMBER,
3284 x_psyv_tbl OUT NOCOPY psyv_tbl_type
3285 ) IS
3286 CURSOR okl_psyv_fk_csr (p_pdt_id IN Okl_prod_strm_types_V.pdt_id%TYPE) IS
3287 SELECT ID,
3288 PDT_ID,
3289 STY_ID,
3290 ACCRUAL_YN,
3291 FROM_DATE,
3292 TO_DATE
3293 FROM Okl_prod_strm_types_V psy
3294 WHERE psy.PDT_ID = p_pdt_id;
3295
3296 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
3297 l_count NUMBER := 0;
3298 l_psyv_tbl psyv_tbl_type;
3299
3300 BEGIN
3301 x_return_status := Okl_Api.G_RET_STS_SUCCESS;
3302
3303 -- Get current database values
3304 FOR okl_psy_rec IN okl_psyv_fk_csr(p_upd_pdtv_rec.id)
3305 LOOP
3306
3307 IF p_flag = G_UPDATE THEN
3308 l_psyv_tbl(l_count).ID := okl_psy_rec.ID;
3309 END IF;
3310 l_psyv_tbl(l_count).PDT_ID := p_pdtv_rec.ID;
3311 l_psyv_tbl(l_count).STY_ID := okl_psy_rec.STY_ID;
3312 l_psyv_tbl(l_count).ACCRUAL_YN := okl_psy_rec.ACCRUAL_YN;
3313 IF p_upd_pdtv_rec.from_date <> Okl_Api.G_MISS_DATE THEN
3314 l_psyv_tbl(l_count).from_date := p_upd_pdtv_rec.from_date;
3315 ELSE
3316 l_psyv_tbl(l_count).from_date := okl_psy_rec.from_date;
3317 END IF;
3318 IF p_upd_pdtv_rec.TO_DATE <> Okl_Api.G_MISS_DATE THEN
3319 l_psyv_tbl(l_count).TO_DATE := p_upd_pdtv_rec.TO_DATE;
3320 ELSE
3321 l_psyv_tbl(l_count).TO_DATE := okl_psy_rec.TO_DATE;
3322 END IF;
3323 l_count := l_count + 1;
3324 END LOOP;
3325
3326 x_count := l_count;
3327 x_psyv_tbl := l_psyv_tbl;
3328
3329 EXCEPTION
3330 WHEN OTHERS THEN
3331 -- store SQL error message on message stack
3332 Okl_Api.SET_MESSAGE(p_app_name => G_APP_NAME,
3333 p_msg_name => G_UNEXPECTED_ERROR,
3334 p_token1 => G_SQLCODE_TOKEN,
3335 p_token1_value => SQLCODE,
3336 p_token2 => G_SQLERRM_TOKEN,
3337 p_token2_value => SQLERRM );
3338 -- notify UNEXPECTED error for calling API.
3339 x_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
3340
3341
3342 IF (okl_psyv_fk_csr%ISOPEN) THEN
3343 CLOSE okl_psyv_fk_csr;
3344 END IF;
3345
3346 END get_prod_strm_types;
3347
3348 ---------------------------------------------------------------------------
3349 -- PROCEDURE get_pdt_pqy_vals for: OKL_PRODUCTS_V
3350 -- To fetch the product quality values that are attached to the existing
3351 -- version of the product
3352 ---------------------------------------------------------------------------
3353 PROCEDURE get_pdt_pqy_vals (p_upd_pdtv_rec IN pdtv_rec_type,
3354 p_pdtv_rec IN pdtv_rec_type,
3355 p_flag IN VARCHAR2,
3356 x_return_status OUT NOCOPY VARCHAR2,
3357 x_count OUT NOCOPY NUMBER,
3358 x_pqvv_tbl OUT NOCOPY pqvv_tbl_type
3359 ) IS
3360 CURSOR okl_pqvv_fk_csr (p_pdt_id IN Okl_Pdt_Pqy_Vals_V.pdt_id%TYPE) IS
3361 SELECT ID,
3362 PDQ_ID,
3363 QVE_ID,
3364 FROM_DATE,
3365 TO_DATE
3366 FROM Okl_pdt_pqy_vals_V pqv
3367 WHERE pqv.PDT_ID = p_pdt_id;
3368
3369 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
3370 l_count NUMBER := 0;
3371 l_pqvv_tbl pqvv_tbl_type;
3372
3373 BEGIN
3374
3375 x_return_status := Okl_Api.G_RET_STS_SUCCESS;
3376
3377 -- Get current database values
3378 FOR okl_pqv_rec IN okl_pqvv_fk_csr(p_upd_pdtv_rec.id)
3379 LOOP
3380 IF p_flag = G_UPDATE THEN
3381 l_pqvv_tbl(l_count).ID := okl_pqv_rec.ID;
3382 END IF;
3383
3384 l_pqvv_tbl(l_count).PDT_ID := p_pdtv_rec.ID;
3385 l_pqvv_tbl(l_count).PDQ_ID := okl_pqv_rec.PDQ_ID;
3386 l_pqvv_tbl(l_count).QVE_ID := okl_pqv_rec.QVE_ID;
3387 IF p_upd_pdtv_rec.from_date <> Okl_Api.G_MISS_DATE THEN
3388 l_pqvv_tbl(l_count).from_date := p_upd_pdtv_rec.from_date;
3389 ELSE
3390 l_pqvv_tbl(l_count).from_date := okl_pqv_rec.from_date;
3391 END IF;
3392 IF p_upd_pdtv_rec.TO_DATE <> Okl_Api.G_MISS_DATE THEN
3393 l_pqvv_tbl(l_count).TO_DATE := p_upd_pdtv_rec.TO_DATE;
3394 ELSE
3395 l_pqvv_tbl(l_count).TO_DATE := okl_pqv_rec.TO_DATE;
3396 END IF;
3397 l_count := l_count + 1;
3398 END LOOP;
3399
3400 x_count := l_count;
3401 x_pqvv_tbl := l_pqvv_tbl;
3402
3403 EXCEPTION
3404 WHEN OTHERS THEN
3405 -- store SQL error message on message stack
3406 Okl_Api.SET_MESSAGE(p_app_name => G_APP_NAME,
3407
3408 p_msg_name => G_UNEXPECTED_ERROR,
3409 p_token1 => G_SQLCODE_TOKEN,
3410 p_token1_value => SQLCODE,
3411 p_token2 => G_SQLERRM_TOKEN,
3412 p_token2_value => SQLERRM );
3413 -- notify UNEXPECTED error for calling API.
3414 x_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
3415
3416 IF (okl_pqvv_fk_csr%ISOPEN) THEN
3417 CLOSE okl_pqvv_fk_csr;
3418 END IF;
3419
3420 END get_pdt_pqy_vals;
3421
3422 ---------------------------------------------------------------------------
3423 -- PROCEDURE get_pdt_opts for: OKL_PRODUCTS_V
3424 -- To fetch the product options that are attached to the existing
3425 -- version of the product
3426 ---------------------------------------------------------------------------
3427 PROCEDURE get_pdt_opts (p_upd_pdtv_rec IN pdtv_rec_type,
3428 p_pdtv_rec IN pdtv_rec_type,
3429 p_flag IN VARCHAR2,
3430 x_return_status OUT NOCOPY VARCHAR2,
3431 x_count OUT NOCOPY NUMBER,
3432 x_ponv_tbl OUT NOCOPY ponv_tbl_type
3433 ) IS
3434 CURSOR okl_ponv_fk_csr (p_pdt_id IN Okl_Pdt_Opts_V.pdt_id%TYPE) IS
3435 SELECT ID,
3436 OPT_ID,
3437 OPTIONAL_YN,
3438 FROM_DATE,
3439 TO_DATE
3440 FROM Okl_Pdt_Opts_V pon
3441 WHERE pon.PDT_ID = p_pdt_id;
3442
3443
3444 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
3445 l_count NUMBER := 0;
3446 l_ponv_tbl ponv_tbl_type;
3447
3448 BEGIN
3449 x_return_status := Okl_Api.G_RET_STS_SUCCESS;
3450
3451 -- Get current database values
3452 FOR okl_pon_rec IN okl_ponv_fk_csr(p_upd_pdtv_rec.id)
3453 LOOP
3454 IF p_flag = G_UPDATE THEN
3455 l_ponv_tbl(l_count).ID := okl_pon_rec.ID;
3456 END IF;
3457 l_ponv_tbl(l_count).PDT_ID := p_pdtv_rec.ID;
3458 l_ponv_tbl(l_count).OPT_ID := okl_pon_rec.OPT_ID;
3459 l_ponv_tbl(l_count).OPTIONAL_YN := okl_pon_rec.OPTIONAL_YN;
3460 IF p_upd_pdtv_rec.from_date <> Okl_Api.G_MISS_DATE THEN
3461 l_ponv_tbl(l_count).from_date := p_upd_pdtv_rec.from_date;
3462 ELSE
3463 l_ponv_tbl(l_count).from_date := okl_pon_rec.from_date;
3464 END IF;
3465 IF p_upd_pdtv_rec.TO_DATE <> Okl_Api.G_MISS_DATE THEN
3466 l_ponv_tbl(l_count).TO_DATE := p_upd_pdtv_rec.TO_DATE;
3467 ELSE
3468 l_ponv_tbl(l_count).TO_DATE := okl_pon_rec.TO_DATE;
3469 END IF;
3470 l_count := l_count + 1;
3471 END LOOP;
3472
3473 x_count := l_count;
3474
3475 x_ponv_tbl := l_ponv_tbl;
3476
3477 EXCEPTION
3478 WHEN OTHERS THEN
3479 -- store SQL error message on message stack
3480 Okl_Api.SET_MESSAGE(p_app_name => G_APP_NAME,
3481 p_msg_name => G_UNEXPECTED_ERROR,
3482 p_token1 => G_SQLCODE_TOKEN,
3483 p_token1_value => SQLCODE,
3484 p_token2 => G_SQLERRM_TOKEN,
3485 p_token2_value => SQLERRM );
3486
3487 -- notify UNEXPECTED error for calling API.
3488 x_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
3489
3490 IF (okl_ponv_fk_csr%ISOPEN) THEN
3491 CLOSE okl_ponv_fk_csr;
3492 END IF;
3493
3494 END get_pdt_opts;
3495
3496 ---------------------------------------------------------------------------
3497 -- PROCEDURE get_pdt_opt_vals for: OKL_PRODUCTS_V
3498 -- To fetch the valid option values for the attached options to the existing
3499 -- version of the product
3500 ---------------------------------------------------------------------------
3501 PROCEDURE get_pdt_opt_vals (p_upd_pdtv_rec IN pdtv_rec_type,
3502 p_pdtv_rec IN pdtv_rec_type,
3503 p_ponv_tbl IN ponv_tbl_type,
3504 p_flag IN VARCHAR2,
3505 x_return_status OUT NOCOPY VARCHAR2,
3506 x_count OUT NOCOPY NUMBER,
3507 x_povv_tbl OUT NOCOPY povv_tbl_type
3508
3509 ) IS
3510 CURSOR okl_povv_fk_csr (p_pdt_id IN Okl_Products_V.id%TYPE) IS
3511 SELECT pov.ID ID,
3512 ove.OPT_ID OPT_ID,
3513 pov.OVE_ID OVE_ID,
3514 pov.FROM_DATE FROM_DATE,
3515 pov.TO_DATE TO_DATE
3516 FROM Okl_Pdt_Opts_V pon,
3517 Okl_Pdt_Opt_Vals_V pov,
3518 Okl_Opt_Values_V ove
3519 WHERE pon.PDT_ID = p_pdt_id
3520 AND pov.PON_ID = pon.ID
3521 AND ove.ID = pov.OVE_ID;
3522
3523 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
3524 l_count NUMBER := 0;
3525 l_povv_tbl povv_tbl_type;
3526 i NUMBER := 0;
3527
3528 BEGIN
3529 x_return_status := Okl_Api.G_RET_STS_SUCCESS;
3530
3531 -- Get current database values
3532 FOR okl_pov_rec IN okl_povv_fk_csr(p_upd_pdtv_rec.id)
3533 LOOP
3534 IF p_flag = G_UPDATE THEN
3535 l_povv_tbl(l_count).ID := okl_pov_rec.ID;
3536 END IF;
3537 l_povv_tbl(l_count).OVE_ID := okl_pov_rec.OVE_ID;
3538 FOR i IN p_ponv_tbl.FIRST .. p_ponv_tbl.LAST
3539 LOOP
3540 IF p_ponv_tbl(i).opt_id = okl_pov_rec.opt_id THEN
3541 l_povv_tbl(l_count).pon_id := p_ponv_tbl(i).id;
3542 END IF;
3543 END LOOP;
3544
3545 IF p_upd_pdtv_rec.from_date <> Okl_Api.G_MISS_DATE THEN
3546 l_povv_tbl(l_count).from_date := p_upd_pdtv_rec.from_date;
3547 ELSE
3548 l_povv_tbl(l_count).from_date := okl_pov_rec.from_date;
3549 END IF;
3550 IF p_upd_pdtv_rec.TO_DATE <> Okl_Api.G_MISS_DATE THEN
3551 l_povv_tbl(l_count).TO_DATE := p_upd_pdtv_rec.TO_DATE;
3552 ELSE
3553 l_povv_tbl(l_count).TO_DATE := okl_pov_rec.TO_DATE;
3554 END IF;
3555 l_count := l_count + 1;
3556 END LOOP;
3557
3558 x_count := l_count;
3559 x_povv_tbl := l_povv_tbl;
3560
3561 EXCEPTION
3562 WHEN OTHERS THEN
3563 -- store SQL error message on message stack
3564 Okl_Api.SET_MESSAGE(p_app_name => G_APP_NAME,
3565 p_msg_name => G_UNEXPECTED_ERROR,
3566 p_token1 => G_SQLCODE_TOKEN,
3567 p_token1_value => SQLCODE,
3568 p_token2 => G_SQLERRM_TOKEN,
3569 p_token2_value => SQLERRM );
3570 -- notify UNEXPECTED error for calling API.
3571 x_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
3572
3573 IF (okl_povv_fk_csr%ISOPEN) THEN
3574
3575 CLOSE okl_povv_fk_csr;
3576 END IF;
3577
3578 END get_pdt_opt_vals;
3579
3580 ---------------------------------------------------------------------------
3581 -- PROCEDURE copy_update_constraints for: OKL_PRODUCTS_V
3582 -- To copy constraints data from one version to the other
3583 ---------------------------------------------------------------------------
3584 PROCEDURE copy_update_constraints (p_api_version IN NUMBER,
3585 p_init_msg_list IN VARCHAR2 DEFAULT Okl_Api.G_FALSE,
3586 p_upd_pdtv_rec IN pdtv_rec_type,
3587 p_db_pdtv_rec IN pdtv_rec_type,
3588 p_pdtv_rec IN pdtv_rec_type,
3589
3590 p_flag IN VARCHAR2,
3591 x_return_status OUT NOCOPY VARCHAR2,
3592 x_msg_count OUT NOCOPY NUMBER,
3593 x_msg_data OUT NOCOPY VARCHAR2
3594 ) IS
3595 l_upd_pdtv_rec pdtv_rec_type; /* input copy */
3596 l_pdtv_rec pdtv_rec_type; /* latest with the retained changes */
3597 l_db_pdtv_rec pdtv_rec_type; /* for db copy */
3598 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
3599 l_pqv_count NUMBER := 0;
3600 l_pon_count NUMBER := 0;
3601 l_pov_count NUMBER := 0;
3602 l_psy_count NUMBER := 0;
3603 l_pqvv_tbl pqvv_tbl_type;
3604 l_out_pqvv_tbl pqvv_tbl_type;
3605 l_ponv_tbl ponv_tbl_type;
3606 l_out_ponv_tbl ponv_tbl_type;
3607 l_povv_tbl povv_tbl_type;
3608 l_out_povv_tbl povv_tbl_type;
3609
3610 l_psyv_tbl psyv_tbl_type;
3611 l_out_psyv_tbl psyv_tbl_type;
3612
3613 BEGIN
3614 x_return_status := Okl_Api.G_RET_STS_SUCCESS;
3615 l_upd_pdtv_rec := p_pdtv_rec;
3616 l_pdtv_rec := p_pdtv_rec;
3617 l_db_pdtv_rec := p_db_pdtv_rec;
3618
3619 /* product Stream Types carryover */
3620 get_prod_strm_types(p_upd_pdtv_rec => l_upd_pdtv_rec,
3621 p_pdtv_rec => l_pdtv_rec,
3622 p_flag => p_flag,
3623 x_return_status => l_return_status,
3624 x_count => l_psy_count,
3625 x_psyv_tbl => l_psyv_tbl);
3626 IF l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR THEN
3627 x_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
3628 RAISE G_EXCEPTION_HALT_PROCESSING;
3629 END IF;
3630
3631 IF l_pqv_count > 0 THEN
3632 IF p_flag = G_UPDATE THEN
3633 -- Start of wraper code generated automatically by Debug code generator for Okl_Pdt_Stys_Pub.update_pdt_stys
3634 IF(L_DEBUG_ENABLED='Y') THEN
3635 L_LEVEL_PROCEDURE :=FND_LOG.LEVEL_PROCEDURE;
3636 IS_DEBUG_PROCEDURE_ON := OKL_DEBUG_PUB.Check_Log_On(L_MODULE, L_LEVEL_PROCEDURE);
3637 END IF;
3638 IF(IS_DEBUG_PROCEDURE_ON) THEN
3639 BEGIN
3640 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'Begin Debug OKLRSPDB.pls call Okl_Pdt_Stys_Pub.update_pdt_stys ');
3641 END;
3642 END IF;
3643 Okl_Pdt_Stys_Pub.update_pdt_stys(p_api_version => p_api_version,
3644 p_init_msg_list => p_init_msg_list,
3645 x_return_status => l_return_status,
3646
3647 x_msg_count => x_msg_count,
3648 x_msg_data => x_msg_data,
3649 p_psyv_tbl => l_psyv_tbl,
3650 x_psyv_tbl => l_out_psyv_tbl);
3651 IF(IS_DEBUG_PROCEDURE_ON) THEN
3652 BEGIN
3653 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'End Debug OKLRSPDB.pls call Okl_Pdt_Stys_Pub.update_pdt_stys ');
3654 END;
3655 END IF;
3656 -- End of wraper code generated automatically by Debug code generator for Okl_Pdt_Stys_Pub.update_pdt_stys
3657 ELSE
3658 -- Start of wraper code generated automatically by Debug code generator for Okl_Pdt_Stys_Pub.insert_pdt_stys
3659 IF(IS_DEBUG_PROCEDURE_ON) THEN
3660 BEGIN
3661 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'Begin Debug OKLRSPDB.pls call Okl_Pdt_Stys_Pub.insert_pdt_stys ');
3662 END;
3663 END IF;
3664 Okl_Pdt_Stys_Pub.insert_pdt_stys(p_api_version => p_api_version,
3665 p_init_msg_list => p_init_msg_list,
3666 x_return_status => l_return_status,
3667 x_msg_count => x_msg_count,
3668 x_msg_data => x_msg_data,
3669 p_psyv_tbl => l_psyv_tbl,
3670 x_psyv_tbl => l_out_psyv_tbl);
3671 IF(IS_DEBUG_PROCEDURE_ON) THEN
3672 BEGIN
3673 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'End Debug OKLRSPDB.pls call Okl_Pdt_Stys_Pub.insert_pdt_stys ');
3674
3675 END;
3676 END IF;
3677 -- End of wraper code generated automatically by Debug code generator for Okl_Pdt_Stys_Pub.insert_pdt_stys
3678 END IF;
3679 IF l_return_status = Okl_Api.G_RET_STS_ERROR THEN
3680 x_return_status := Okl_Api.G_RET_STS_ERROR;
3681 RAISE G_EXCEPTION_HALT_PROCESSING;
3682 ELSIF l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR THEN
3683 x_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
3684 RAISE G_EXCEPTION_HALT_PROCESSING;
3685 END IF;
3686 END IF;
3687
3688 /* product quality values carryover */
3689 get_pdt_pqy_vals(p_upd_pdtv_rec => l_upd_pdtv_rec,
3690 p_pdtv_rec => l_pdtv_rec,
3691 p_flag => p_flag,
3692
3693 x_return_status => l_return_status,
3694 x_count => l_pqv_count,
3695 x_pqvv_tbl => l_pqvv_tbl);
3696 IF l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR THEN
3697 x_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
3698 RAISE G_EXCEPTION_HALT_PROCESSING;
3699 END IF;
3700
3701 IF l_pqv_count > 0 THEN
3702 IF p_flag = G_UPDATE THEN
3703 -- Start of wraper code generated automatically by Debug code generator for Okl_Pqy_Values_Pub.update_pqy_values
3704 IF(IS_DEBUG_PROCEDURE_ON) THEN
3705 BEGIN
3706 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'Begin Debug OKLRSPDB.pls call Okl_Pqy_Values_Pub.update_pqy_values ');
3707 END;
3708 END IF;
3709 Okl_Pqy_Values_Pub.update_pqy_values(p_api_version => p_api_version,
3710
3711 p_init_msg_list => p_init_msg_list,
3712 x_return_status => l_return_status,
3713 x_msg_count => x_msg_count,
3714 x_msg_data => x_msg_data,
3715 p_pqvv_tbl => l_pqvv_tbl,
3716 x_pqvv_tbl => l_out_pqvv_tbl);
3717 IF(IS_DEBUG_PROCEDURE_ON) THEN
3718 BEGIN
3719 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'End Debug OKLRSPDB.pls call Okl_Pqy_Values_Pub.update_pqy_values ');
3720 END;
3721 END IF;
3722 -- End of wraper code generated automatically by Debug code generator for Okl_Pqy_Values_Pub.update_pqy_values
3723 ELSE
3724 -- Start of wraper code generated automatically by Debug code generator for Okl_Pqy_Values_Pub.insert_pqy_values
3725 IF(IS_DEBUG_PROCEDURE_ON) THEN
3726 BEGIN
3727 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'Begin Debug OKLRSPDB.pls call Okl_Pqy_Values_Pub.insert_pqy_values ');
3728 END;
3729 END IF;
3730 Okl_Pqy_Values_Pub.insert_pqy_values(p_api_version => p_api_version,
3731 p_init_msg_list => p_init_msg_list,
3732 x_return_status => l_return_status,
3733 x_msg_count => x_msg_count,
3734 x_msg_data => x_msg_data,
3735 p_pqvv_tbl => l_pqvv_tbl,
3736 x_pqvv_tbl => l_out_pqvv_tbl);
3737 IF(IS_DEBUG_PROCEDURE_ON) THEN
3738 BEGIN
3739 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'End Debug OKLRSPDB.pls call Okl_Pqy_Values_Pub.insert_pqy_values ');
3740 END;
3741 END IF;
3742 -- End of wraper code generated automatically by Debug code generator for Okl_Pqy_Values_Pub.insert_pqy_values
3743 END IF;
3744 IF l_return_status = Okl_Api.G_RET_STS_ERROR THEN
3745 x_return_status := Okl_Api.G_RET_STS_ERROR;
3746 RAISE G_EXCEPTION_HALT_PROCESSING;
3747 ELSIF l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR THEN
3748
3749 x_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
3750 RAISE G_EXCEPTION_HALT_PROCESSING;
3751 END IF;
3752 END IF;
3753
3754 /* product options carryover */
3755 get_pdt_opts(p_upd_pdtv_rec => l_upd_pdtv_rec,
3756 p_pdtv_rec => l_pdtv_rec,
3757 p_flag => p_flag,
3758 x_return_status => l_return_status,
3759 x_count => l_pon_count,
3760 x_ponv_tbl => l_ponv_tbl);
3761 IF l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR THEN
3762 x_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
3763 RAISE G_EXCEPTION_HALT_PROCESSING;
3764 END IF;
3765
3766 IF l_pon_count > 0 THEN
3767 IF p_flag = G_UPDATE THEN
3768 -- Start of wraper code generated automatically by Debug code generator for Okl_Product_Options_Pub.update_product_options
3769 IF(IS_DEBUG_PROCEDURE_ON) THEN
3770 BEGIN
3771 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'Begin Debug OKLRSPDB.pls call Okl_Product_Options_Pub.update_product_options ');
3772 END;
3773 END IF;
3774
3775 Okl_Product_Options_Pub.update_product_options(p_api_version => p_api_version,
3776 p_init_msg_list => p_init_msg_list,
3777 x_return_status => l_return_status,
3778 x_msg_count => x_msg_count,
3779 x_msg_data => x_msg_data,
3780 p_ponv_tbl => l_ponv_tbl,
3781 x_ponv_tbl => l_out_ponv_tbl);
3782 IF(IS_DEBUG_PROCEDURE_ON) THEN
3783 BEGIN
3784 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'End Debug OKLRSPDB.pls call Okl_Product_Options_Pub.update_product_options ');
3785 END;
3786 END IF;
3787 -- End of wraper code generated automatically by Debug code generator for Okl_Product_Options_Pub.update_product_options
3788 ELSE
3789 -- Start of wraper code generated automatically by Debug code generator for Okl_Product_Options_Pub.insert_product_options
3790 IF(IS_DEBUG_PROCEDURE_ON) THEN
3791 BEGIN
3792 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'Begin Debug OKLRSPDB.pls call Okl_Product_Options_Pub.insert_product_options ');
3793 END;
3794 END IF;
3795
3796 Okl_Product_Options_Pub.insert_product_options(p_api_version => p_api_version,
3797 p_init_msg_list => p_init_msg_list,
3798 x_return_status => l_return_status,
3799 x_msg_count => x_msg_count,
3800 x_msg_data => x_msg_data,
3801 p_ponv_tbl => l_ponv_tbl,
3802 x_ponv_tbl => l_out_ponv_tbl);
3803 IF(IS_DEBUG_PROCEDURE_ON) THEN
3804 BEGIN
3805 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'End Debug OKLRSPDB.pls call Okl_Product_Options_Pub.insert_product_options ');
3806 END;
3807 END IF;
3808 -- End of wraper code generated automatically by Debug code generator for Okl_Product_Options_Pub.insert_product_options
3809 END IF;
3810 IF l_return_status = Okl_Api.G_RET_STS_ERROR THEN
3811
3812 x_return_status := Okl_Api.G_RET_STS_ERROR;
3813 RAISE G_EXCEPTION_HALT_PROCESSING;
3814 ELSIF l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR THEN
3815 x_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
3816 RAISE G_EXCEPTION_HALT_PROCESSING;
3817 END IF;
3818
3819 END IF;
3820
3821 /* valid product option values carryover */
3822 get_pdt_opt_vals(p_upd_pdtv_rec => l_upd_pdtv_rec,
3823 p_pdtv_rec => l_pdtv_rec,
3824 p_ponv_tbl => l_out_ponv_tbl,
3825 p_flag => p_flag,
3826 x_return_status => l_return_status,
3827 x_count => l_pov_count,
3828 x_povv_tbl => l_povv_tbl);
3829 IF l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR THEN
3830 x_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
3831 RAISE G_EXCEPTION_HALT_PROCESSING;
3832 END IF;
3833
3834 IF l_pov_count > 0 THEN
3835 IF p_flag = G_UPDATE THEN
3836 -- Start of wraper code generated automatically by Debug code generator for Okl_Pdt_Opt_Vals_Pub.update_pdt_opt_vals
3837 IF(IS_DEBUG_PROCEDURE_ON) THEN
3838 BEGIN
3839 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'Begin Debug OKLRSPDB.pls call Okl_Pdt_Opt_Vals_Pub.update_pdt_opt_vals ');
3840 END;
3841 END IF;
3842 Okl_Pdt_Opt_Vals_Pub.update_pdt_opt_vals(p_api_version => p_api_version,
3843 p_init_msg_list => p_init_msg_list,
3844 x_return_status => l_return_status,
3845 x_msg_count => x_msg_count,
3846 x_msg_data => x_msg_data,
3847 p_povv_tbl => l_povv_tbl,
3848 x_povv_tbl => l_out_povv_tbl);
3849 IF(IS_DEBUG_PROCEDURE_ON) THEN
3850
3851 BEGIN
3852 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'End Debug OKLRSPDB.pls call Okl_Pdt_Opt_Vals_Pub.update_pdt_opt_vals ');
3853 END;
3854 END IF;
3855 -- End of wraper code generated automatically by Debug code generator for Okl_Pdt_Opt_Vals_Pub.update_pdt_opt_vals
3856 ELSE
3857 -- Start of wraper code generated automatically by Debug code generator for Okl_Pdt_Opt_Vals_Pub.insert_pdt_opt_vals
3858 IF(IS_DEBUG_PROCEDURE_ON) THEN
3859 BEGIN
3860 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'Begin Debug OKLRSPDB.pls call Okl_Pdt_Opt_Vals_Pub.insert_pdt_opt_vals ');
3861 END;
3862 END IF;
3863 Okl_Pdt_Opt_Vals_Pub.insert_pdt_opt_vals(p_api_version => p_api_version,
3864 p_init_msg_list => p_init_msg_list,
3865 x_return_status => l_return_status,
3866 x_msg_count => x_msg_count,
3867 x_msg_data => x_msg_data,
3868 p_povv_tbl => l_povv_tbl,
3869 x_povv_tbl => l_out_povv_tbl);
3870 IF(IS_DEBUG_PROCEDURE_ON) THEN
3871 BEGIN
3872 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'End Debug OKLRSPDB.pls call Okl_Pdt_Opt_Vals_Pub.insert_pdt_opt_vals ');
3873 END;
3874
3875 END IF;
3876 -- End of wraper code generated automatically by Debug code generator for Okl_Pdt_Opt_Vals_Pub.insert_pdt_opt_vals
3877 END IF;
3878 IF l_return_status = Okl_Api.G_RET_STS_ERROR THEN
3879 x_return_status := Okl_Api.G_RET_STS_ERROR;
3880 RAISE G_EXCEPTION_HALT_PROCESSING;
3881 ELSIF l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR THEN
3882 x_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
3883 RAISE G_EXCEPTION_HALT_PROCESSING;
3884 END IF;
3885
3886 END IF;
3887
3888
3889 EXCEPTION
3890 WHEN G_EXCEPTION_HALT_PROCESSING THEN
3891 -- no processing necessary; validation can continue
3892 -- with the next column
3893 NULL;
3894
3895 WHEN OTHERS THEN
3896 -- store SQL error message on message stack
3897 Okl_Api.SET_MESSAGE(p_app_name => G_APP_NAME,
3898
3899 p_msg_name => G_UNEXPECTED_ERROR,
3900 p_token1 => G_SQLCODE_TOKEN,
3901 p_token1_value => SQLCODE,
3902 p_token2 => G_SQLERRM_TOKEN,
3903 p_token2_value => SQLERRM );
3904 -- notify UNEXPECTED error for calling API.
3905 x_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
3906
3907 END copy_update_constraints;
3908
3909 ---------------------------------------------------------------------------
3910 -- PROCEDURE insert_products for: OKL_PRODUCTS_V
3911 ---------------------------------------------------------------------------
3912
3913 PROCEDURE insert_products(p_api_version IN NUMBER,
3914 p_init_msg_list IN VARCHAR2 DEFAULT Okl_Api.G_FALSE,
3915 x_return_status OUT NOCOPY VARCHAR2,
3916 x_msg_count OUT NOCOPY NUMBER,
3917 x_msg_data OUT NOCOPY VARCHAR2,
3918 p_pdtv_rec IN pdtv_rec_type,
3919 x_pdtv_rec OUT NOCOPY pdtv_rec_type
3920 ) IS
3921 l_api_version CONSTANT NUMBER := 1;
3922 l_api_name CONSTANT VARCHAR2(30) := 'insert_products';
3923 l_valid BOOLEAN := TRUE;
3924 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
3925 --return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
3926 l_pdtv_rec pdtv_rec_type;
3927 l_db_pdtv_rec pdtv_rec_type;
3928 l_sysdate DATE := to_date(to_char(SYSDATE, 'DD/MM/YYYY'), 'DD/MM/YYYY');
3929
3930 CURSOR chk_ptl_aes_bc(p_aes_id IN Okl_Products_V.AES_ID%TYPE,
3931 p_ptl_id IN Okl_Products_V.PTL_ID%TYPE,
3932 p_pdt_id IN Okl_Products_V.id%TYPE)
3933 IS
3934 SELECT DISTINCT DECODE(C.product_type,'FINANCIAL','LEASE','INVESTOR')
3935 FROM okl_ae_tmpt_sets_v b,
3936 OKL_ST_GEN_TMPT_SETS c
3937 WHERE b.gts_id = c.id
3938 AND b.id = p_aes_id
3939 INTERSECT
3940 SELECT DISTINCT PQY.NAME
3941 FROM okl_PDT_PQYS_V PDQ,
3942 OKL_PQY_VALUES_V QVE,OKL_PDT_QUALITYS_V PQY
3943 WHERE PQY.ID = QVE.PQY_ID
3944 AND PQY.ID= PDQ.PQY_ID
3945 AND PDQ.PTL_ID = p_PTL_ID
3946 AND pqy.name IN('LEASE','INVESTOR');
3947
3948 l_chk_bc VARCHAR2(100);
3949
3950
3951 BEGIN
3952 x_return_status := Okl_Api.G_RET_STS_SUCCESS;
3953
3954 l_pdtv_rec := p_pdtv_rec;
3955
3956 l_return_status := Okl_Api.START_ACTIVITY(p_api_name => l_api_name,
3957 p_pkg_name => G_PKG_NAME,
3958 p_init_msg_list => p_init_msg_list,
3959 l_api_version => l_api_version,
3960 p_api_version => p_api_version,
3961 p_api_type => '_PVT',
3962 x_return_status => l_return_status);
3963 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
3964 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
3965 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
3966 RAISE Okl_Api.G_EXCEPTION_ERROR;
3967 END IF;
3968
3969
3970 l_return_status := Validate_Attributes(l_pdtv_rec);
3971 --- If any errors happen abort API
3972 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
3973 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
3974
3975 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
3976 RAISE Okl_Api.G_EXCEPTION_ERROR;
3977 END IF;
3978
3979 OPEN chk_ptl_aes_bc(l_pdtv_rec.aes_id,l_pdtv_rec.ptl_id,l_pdtv_rec.id);
3980 FETCH chk_ptl_aes_bc INTO l_chk_bc;
3981 CLOSE chk_ptl_aes_bc;
3982
3983 IF l_chk_bc IS NULL THEN
3984
3985 Okl_Api.SET_MESSAGE(p_app_name => G_APP_NAME,
3986 p_msg_name => G_PTL_AES_BC_MISMATCH);
3987 x_return_status := Okl_Api.G_RET_STS_ERROR;
3988 RAISE OKL_API.G_EXCEPTION_ERROR;
3989 END IF;
3990
3991
3992
3993 /* check for the records with from and to dates less than sysdate */
3994 /* IF to_date(to_char(l_pdtv_rec.from_date, 'DD/MM/YYYY'), 'DD/MM/YYYY') < l_sysdate OR
3995 to_date(to_char(l_pdtv_rec.TO_DATE, 'DD/MM/YYYY'), 'DD/MM/YYYY') < l_sysdate THEN
3996 OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
3997 p_msg_name => G_PAST_RECORDS);
3998 RAISE OKL_API.G_EXCEPTION_ERROR;
3999 END IF;
4000 */
4001 /* call check_constraints */
4002 Check_Constraints(p_api_version => p_api_version,
4003 p_init_msg_list => p_init_msg_list,
4004 x_msg_count => x_msg_count,
4005 x_msg_data => x_msg_data,
4006 p_upd_pdtv_rec => l_pdtv_rec,
4007 p_pdtv_rec => l_pdtv_rec,
4008 p_db_pdtv_rec => l_db_pdtv_rec,
4009 x_return_status => l_return_status,
4010 x_valid => l_valid);
4011
4012 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
4013
4014 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
4015 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) OR
4016 (l_return_status = Okl_Api.G_RET_STS_SUCCESS AND
4017 l_valid <> TRUE) THEN
4018 RAISE Okl_Api.G_EXCEPTION_ERROR;
4019 END IF;
4020
4021 /* public api to insert products */
4022 -- Start of wraper code generated automatically by Debug code generator for Okl_Products_Pub.insert_products
4023 IF(L_DEBUG_ENABLED='Y') THEN
4024 L_LEVEL_PROCEDURE :=FND_LOG.LEVEL_PROCEDURE;
4025 IS_DEBUG_PROCEDURE_ON := OKL_DEBUG_PUB.Check_Log_On(L_MODULE, L_LEVEL_PROCEDURE);
4026 END IF;
4027 IF(IS_DEBUG_PROCEDURE_ON) THEN
4028 BEGIN
4029 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'Begin Debug OKLRSPDB.pls call Okl_Products_Pub.insert_products ');
4030 END;
4031 END IF;
4032
4033
4034
4035 l_pdtv_rec.PRODUCT_STATUS_CODE := G_PDT_STS_NEW;
4036
4037 Okl_Products_Pub.insert_products(p_api_version => p_api_version,
4038 p_init_msg_list => p_init_msg_list,
4039 x_return_status => l_return_status,
4040 x_msg_count => x_msg_count,
4041 x_msg_data => x_msg_data,
4042 p_pdtv_rec => l_pdtv_rec,
4043 x_pdtv_rec => x_pdtv_rec);
4044
4045
4046 IF(IS_DEBUG_PROCEDURE_ON) THEN
4047 BEGIN
4048 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'End Debug OKLRSPDB.pls call Okl_Products_Pub.insert_products ');
4049 END;
4050 END IF;
4051 -- End of wraper code generated automatically by Debug code generator for Okl_Products_Pub.insert_products
4052
4053 IF l_return_status = Okl_Api.G_RET_STS_ERROR THEN
4054 RAISE Okl_Api.G_EXCEPTION_ERROR;
4055
4056 ELSIF l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR THEN
4057 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
4058 END IF;
4059 Okl_Api.END_ACTIVITY(x_msg_count => x_msg_count,
4060 x_msg_data => x_msg_data);
4061 EXCEPTION
4062 WHEN Okl_Api.G_EXCEPTION_ERROR THEN
4063 x_return_status := Okl_Api.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
4064 p_pkg_name => G_PKG_NAME,
4065 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
4066 x_msg_count => x_msg_count,
4067 x_msg_data => x_msg_data,
4068 p_api_type => '_PVT');
4069 WHEN Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR THEN
4070 x_return_status := Okl_Api.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
4071 p_pkg_name => G_PKG_NAME,
4072 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
4073 x_msg_count => x_msg_count,
4074
4075 x_msg_data => x_msg_data,
4076 p_api_type => '_PVT');
4077 WHEN OTHERS THEN
4078 x_return_status := Okl_Api.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
4079 p_pkg_name => G_PKG_NAME,
4080 p_exc_name => 'OTHERS',
4081 x_msg_count => x_msg_count,
4082 x_msg_data => x_msg_data,
4083 p_api_type => '_PVT');
4084
4085 END insert_products;
4086
4087
4088 ---------------------------------------------------------------------------
4089 -- PROCEDURE update_products for: OKL_PRODUCTS_V
4090 ---------------------------------------------------------------------------
4091 PROCEDURE update_products(p_api_version IN NUMBER,
4092 p_init_msg_list IN VARCHAR2 DEFAULT Okl_Api.G_FALSE,
4093 x_return_status OUT NOCOPY VARCHAR2,
4094 x_msg_count OUT NOCOPY NUMBER,
4095 x_msg_data OUT NOCOPY VARCHAR2,
4096 p_pdtv_rec IN pdtv_rec_type,
4097 x_pdtv_rec OUT NOCOPY pdtv_rec_type
4098 ) IS
4099 l_api_version CONSTANT NUMBER := 1;
4100 l_api_name CONSTANT VARCHAR2(30) := 'update_products';
4101 l_validated VARCHAR2(2000);
4102 l_no_data_found BOOLEAN := TRUE;
4103 l_valid BOOLEAN := TRUE;
4104 l_oldversion_enddate DATE := to_date(to_char(SYSDATE, 'DD/MM/YYYY'), 'DD/MM/YYYY');
4105 l_sysdate DATE := to_date(to_char(SYSDATE, 'DD/MM/YYYY'), 'DD/MM/YYYY');
4106 l_db_pdtv_rec pdtv_rec_type; /* database copy */
4107 l_upd_pdtv_rec pdtv_rec_type; /* input copy */
4108 l_pdtv_rec pdtv_rec_type; /* latest with the retained changes */
4109 l_tmp_pdtv_rec pdtv_rec_type; /* for any other purposes */
4110 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
4111 -- x_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
4112 l_action VARCHAR2(1);
4113 l_new_version VARCHAR2(100);
4114
4115 l_pqv_count NUMBER := 0;
4116 l_pon_count NUMBER := 0;
4117 l_pov_count NUMBER := 0;
4118 l_pqvv_tbl pqvv_tbl_type;
4119 l_out_pqvv_tbl pqvv_tbl_type;
4120 l_ponv_tbl ponv_tbl_type;
4121 l_out_ponv_tbl ponv_tbl_type;
4122 l_povv_tbl povv_tbl_type;
4123 l_out_povv_tbl povv_tbl_type;
4124 l_chk_bc VARCHAR2(100);
4125
4126
4127
4128 CURSOR chk_ptl_aes_bc(p_aes_id IN Okl_Products_V.AES_ID%TYPE,
4129 p_ptl_id IN Okl_Products_V.PTL_ID%TYPE,
4130 p_pdt_id IN Okl_Products_V.id%TYPE)
4131 IS
4132 SELECT DISTINCT DECODE(C.product_type,'FINANCIAL','LEASE','INVESTOR')
4133 FROM okl_ae_tmpt_sets_v b,
4134 OKL_ST_GEN_TMPT_SETS c
4135 WHERE b.gts_id = c.id
4136 AND b.id = p_aes_id
4137 INTERSECT
4138 SELECT DISTINCT PQY.NAME
4139 FROM okl_PDT_PQYS_V PDQ,
4140 OKL_PQY_VALUES_V QVE,OKL_PDT_QUALITYS_V PQY
4141 WHERE PQY.ID = QVE.PQY_ID
4142 AND PQY.ID= PDQ.PQY_ID
4143 AND PDQ.PTL_ID = p_PTL_ID
4144 AND pqy.name IN('LEASE','INVESTOR');
4145
4146 BEGIN
4147
4148 x_return_status := Okl_Api.G_RET_STS_SUCCESS;
4149 l_upd_pdtv_rec := p_pdtv_rec;
4150
4151
4152 l_return_status := Okl_Api.START_ACTIVITY(p_api_name => l_api_name,
4153 p_pkg_name => G_PKG_NAME,
4154 p_init_msg_list => p_init_msg_list,
4155 l_api_version => l_api_version,
4156 p_api_version => p_api_version,
4157
4158 p_api_type => '_PVT',
4159 x_return_status => l_return_status);
4160 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
4161 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
4162 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
4163 RAISE Okl_Api.G_EXCEPTION_ERROR;
4164 END IF;
4165
4166
4167
4168 /* fetch old details from the database */
4169 get_rec(p_pdtv_rec => l_upd_pdtv_rec,
4170 x_return_status => l_return_status,
4171 x_no_data_found => l_no_data_found,
4172 x_pdtv_rec => l_db_pdtv_rec);
4173 IF l_return_status <> Okl_Api.G_RET_STS_SUCCESS OR
4174
4175 l_no_data_found = TRUE THEN
4176 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
4177 END IF;
4178
4179 -- updates not allowed when the product is in pending approval status.
4180 --IF l_db_pdtv_rec.product_status_code IN ('PENDING APPROVAL') THEN
4181 -- Okl_Api.SET_MESSAGE(p_app_name => G_APP_NAME,
4182 -- p_msg_name => G_PDT_SUBMTD_FOR_APPROVAL);
4183 -- x_return_status := Okl_Api.G_RET_STS_ERROR;
4184 -- RAISE OKL_API.G_EXCEPTION_ERROR;
4185 --END IF;
4186
4187 OPEN chk_ptl_aes_bc(l_upd_pdtv_rec.aes_id,l_upd_pdtv_rec.ptl_id,l_upd_pdtv_rec.id);
4188 FETCH chk_ptl_aes_bc INTO l_chk_bc;
4189 CLOSE chk_ptl_aes_bc;
4190
4191 IF l_chk_bc IS NULL THEN
4192
4193 Okl_Api.SET_MESSAGE(p_app_name => G_APP_NAME,
4194 p_msg_name => G_PTL_AES_BC_MISMATCH);
4195 x_return_status := Okl_Api.G_RET_STS_ERROR;
4196 RAISE OKL_API.G_EXCEPTION_ERROR;
4197 END IF;
4198
4199
4200 /* to reorganize the input accordingly */
4201 reorganize_inputs(p_upd_pdtv_rec => l_upd_pdtv_rec,
4202 p_db_pdtv_rec => l_db_pdtv_rec);
4203
4204 /* check for start date greater than sysdate */
4205 /*IF to_date(to_char(l_upd_pdtv_rec.from_date, 'DD/MM/YYYY'), 'DD/MM/YYYY') <> to_date(to_char(OKL_API.G_MISS_DATE, 'DD/MM/YYYY'), 'DD/MM/YYYY') AND
4206 to_date(to_char(l_upd_pdtv_rec.from_date, 'DD/MM/YYYY'), 'DD/MM/YYYY') < l_sysdate THEN
4207 OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
4208 p_msg_name => G_START_DATE);
4209 RAISE OKL_API.G_EXCEPTION_ERROR;
4210 END IF; */
4211
4212 /* check for the records with start and end dates less than sysdate */
4213 /* IF to_date(to_char(l_db_pdtv_rec.from_date, 'DD/MM/YYYY'), 'DD/MM/YYYY') < l_sysdate AND
4214 to_date(to_char(l_db_pdtv_rec.TO_DATE, 'DD/MM/YYYY'), 'DD/MM/YYYY') < l_sysdate THEN
4215
4216 OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
4217 p_msg_name => G_PAST_RECORDS);
4218 RAISE OKL_API.G_EXCEPTION_ERROR;
4219 END IF;
4220 */
4221 /* determine how the processing to be done */
4222 l_action := determine_action(p_upd_pdtv_rec => l_upd_pdtv_rec,
4223 p_db_pdtv_rec => l_db_pdtv_rec,
4224 p_date => l_sysdate);
4225
4226
4227 /* Scenario 1: only changing description */
4228 IF l_action = '1' THEN
4229
4230 /* public api to update products */
4231 -- Start of wraper code generated automatically by Debug code generator for Okl_Products_Pub.update_products
4232 /*
4233 IF(L_DEBUG_ENABLED='Y') THEN
4234 L_LEVEL_PROCEDURE :=FND_LOG.LEVEL_PROCEDURE;
4235 IS_DEBUG_PROCEDURE_ON := OKL_DEBUG_PUB.Check_Log_On(L_MODULE, L_LEVEL_PROCEDURE);
4236 END IF;
4237 IF(IS_DEBUG_PROCEDURE_ON) THEN
4238 BEGIN
4239 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'Begin Debug OKLRSPDB.pls call Okl_Products_Pub.update_products ');
4240 END;
4241 END IF;
4242 */
4243
4244
4245 IF P_pdtv_rec.product_status_code NOT IN ('PENDING APPROVAL','APPROVED') THEN
4246 l_upd_pdtv_rec.product_status_code := 'PASSED';
4247 END IF;
4248
4249 Okl_Products_Pub.update_products(p_api_version => p_api_version,
4250 p_init_msg_list => p_init_msg_list,
4251 x_return_status => l_return_status,
4252 x_msg_count => x_msg_count,
4253 x_msg_data => x_msg_data,
4254 p_pdtv_rec => l_upd_pdtv_rec,
4255 x_pdtv_rec => x_pdtv_rec);
4256 IF(IS_DEBUG_PROCEDURE_ON) THEN
4257 BEGIN
4258 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'End Debug OKLRSPDB.pls call Okl_Products_Pub.update_products ');
4259
4260 END;
4261 END IF;
4262 -- End of wraper code generated automatically by Debug code generator for Okl_Products_Pub.update_products
4263
4264 IF l_return_status = Okl_Api.G_RET_STS_ERROR THEN
4265
4266 RAISE Okl_Api.G_EXCEPTION_ERROR;
4267 ELSIF l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR THEN
4268
4269 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
4270 END IF;
4271
4272 /* Scenario 2: only changing description and end date for all records
4273 or modified start date is less than existing start date for a future record */
4274
4275 ELSIF l_action = '2' THEN
4276 /* create a temporary record with all relevant details from db and upd records */
4277 l_pdtv_rec := defaults_to_actuals(p_upd_pdtv_rec => l_upd_pdtv_rec,
4278 p_db_pdtv_rec => l_db_pdtv_rec);
4279 /* check the changes */
4280
4281 check_updates(p_api_version => p_api_version,
4282 p_init_msg_list => p_init_msg_list,
4283 x_msg_count => x_msg_count,
4284 x_msg_data => x_msg_data,
4285 p_upd_pdtv_rec => l_upd_pdtv_rec,
4286 p_db_pdtv_rec => l_db_pdtv_rec,
4287 p_pdtv_rec => l_pdtv_rec,
4288 x_return_status => l_return_status);
4289
4290 IF l_return_status = Okl_Api.G_RET_STS_ERROR THEN
4291
4292 IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
4293 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
4294 ELSIF (x_return_status = Okl_Api.G_RET_STS_ERROR) THEN
4295 RAISE Okl_Api.G_EXCEPTION_ERROR;
4296 END IF;
4297
4298 RAISE Okl_Api.G_EXCEPTION_ERROR;
4299
4300 ELSIF l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR THEN
4301 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
4302 END IF;
4303
4304
4305
4306 /* check the changes for product pricing template*/
4307 -- Start of wraper code generated automatically by Debug code generator for Okl_Setup_Prd_Prctempl_Pub.check_product_constraints
4308 IF(IS_DEBUG_PROCEDURE_ON) THEN
4309 BEGIN
4310 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'Begin Debug OKLRSPDB.pls call Okl_Setup_Prd_Prctempl_Pub.check_product_constraints ');
4311 END;
4312 END IF;
4313
4314
4315 Okl_Setup_Prd_Prctempl_Pub.check_product_constraints(
4316
4317 p_api_version => p_api_version,
4318 p_init_msg_list => p_init_msg_list,
4319 x_return_status => l_return_status,
4320 x_msg_count => x_msg_count,
4321 x_msg_data => x_msg_data,
4322 p_pdtv_rec => l_pdtv_rec,
4323 x_validated => l_validated);
4324 IF(IS_DEBUG_PROCEDURE_ON) THEN
4325 BEGIN
4326 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'End Debug OKLRSPDB.pls call Okl_Setup_Prd_Prctempl_Pub.check_product_constraints ');
4327 END;
4328
4329 END IF;
4330 -- End of wraper code generated automatically by Debug code generator for Okl_Setup_Prd_Prctempl_Pub.check_product_constraints
4331 IF l_return_status = Okl_Api.G_RET_STS_ERROR THEN
4332 RAISE Okl_Api.G_EXCEPTION_ERROR;
4333 ELSIF l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR THEN
4334 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
4335 END IF;
4336
4337
4338
4339 /* public api to update formulae */
4340 -- Start of wraper code generated automatically by Debug code generator for Okl_Products_Pub.update_products
4341 IF(IS_DEBUG_PROCEDURE_ON) THEN
4342 BEGIN
4343 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'Begin Debug OKLRSPDB.pls call Okl_Products_Pub.update_products ');
4344 END;
4345 END IF;
4346
4347
4348 IF nvl(l_upd_pdtv_rec.ptl_id,Okl_Api.G_MISS_num) = Okl_Api.G_MISS_num THEN
4349 l_upd_pdtv_rec.ptl_id := l_db_pdtv_rec.ptl_id;
4350 end if;
4351
4352 IF nvl(l_upd_pdtv_rec.aes_id,Okl_Api.G_MISS_num) = Okl_Api.G_MISS_num THEN
4353 l_upd_pdtv_rec.aes_id := l_db_pdtv_rec.aes_id;
4354 end if;
4355
4356 --rkuttiya added condition for reporting product id
4357 /* Following is not required as user should be able to unassign reporting product.
4358 -- racheruv .. bug 7159594
4359 -- 12.1.1. Multi GAAP Project
4360 IF nvl(l_upd_pdtv_rec.reporting_pdt_id,Okl_Api.G_MISS_num) = Okl_Api.G_MISS_num
4361 THEN
4362 l_upd_pdtv_rec.reporting_pdt_id := l_db_pdtv_rec.reporting_pdt_id;
4363 END IF;
4364 --
4365 */
4366
4367 -- updates not allowed when the product is in pending approval status.
4368 IF l_db_pdtv_rec.ptl_id <> l_upd_pdtv_rec.ptl_id or
4369 l_db_pdtv_rec.aes_id <> l_upd_pdtv_rec.aes_id OR
4370 --racheruv added for 12.1.1 Multi GAAP Project Bug 7159594
4371 nvl(l_db_pdtv_rec.reporting_pdt_id, -1) <> l_upd_pdtv_rec.reporting_pdt_id THEN
4372
4373
4374 l_upd_pdtv_rec.product_status_code := 'INVALID';
4375
4376 END IF;
4377
4378
4379
4380 Okl_Products_Pub.update_products(p_api_version => p_api_version,
4381 p_init_msg_list => p_init_msg_list,
4382 x_return_status => l_return_status,
4383 x_msg_count => x_msg_count,
4384 x_msg_data => x_msg_data,
4385 p_pdtv_rec => l_upd_pdtv_rec,
4386
4387 x_pdtv_rec => x_pdtv_rec);
4388 IF(IS_DEBUG_PROCEDURE_ON) THEN
4389 BEGIN
4390 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'End Debug OKLRSPDB.pls call Okl_Products_Pub.update_products ');
4391 END;
4392 END IF;
4393 -- End of wraper code generated automatically by Debug code generator for Okl_Products_Pub.update_products
4394 IF l_return_status = Okl_Api.G_RET_STS_ERROR THEN
4395 RAISE Okl_Api.G_EXCEPTION_ERROR;
4396 ELSIF l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR THEN
4397 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
4398 END IF;
4399
4400 /* update constraints */
4401 /*copy_update_constraints(p_api_version => p_api_version,
4402 p_init_msg_list => p_init_msg_list,
4403 p_upd_pdtv_rec => l_upd_pdtv_rec,
4404 p_db_pdtv_rec => l_db_pdtv_rec,
4405 p_pdtv_rec => l_pdtv_rec,
4406 p_flag => G_UPDATE,
4407 x_return_status => l_return_status,
4408 x_msg_count => x_msg_count,
4409 x_msg_data => x_msg_data);
4410 IF l_return_status = OKL_API.G_RET_STS_ERROR THEN
4411 RAISE OKL_API.G_EXCEPTION_ERROR;
4412 ELSIF l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR THEN
4413 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
4414 END IF;*/
4415
4416 END IF;
4417
4418 Okl_Api.END_ACTIVITY(x_msg_count => x_msg_count,
4419 x_msg_data => x_msg_data);
4420 EXCEPTION
4421 WHEN Okl_Api.G_EXCEPTION_ERROR THEN
4422 x_return_status := Okl_Api.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
4423 p_pkg_name => G_PKG_NAME,
4424 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
4425 x_msg_count => x_msg_count,
4426 x_msg_data => x_msg_data,
4427 p_api_type => '_PVT');
4428 WHEN Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR THEN
4429
4430 x_return_status := Okl_Api.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
4431 p_pkg_name => G_PKG_NAME,
4432 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
4433 x_msg_count => x_msg_count,
4434 x_msg_data => x_msg_data,
4435 p_api_type => '_PVT');
4436 WHEN OTHERS THEN
4437 x_return_status := Okl_Api.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
4438 p_pkg_name => G_PKG_NAME,
4439 p_exc_name => 'OTHERS',
4440 x_msg_count => x_msg_count,
4441 x_msg_data => x_msg_data,
4442 p_api_type => '_PVT');
4443 END update_products;
4444 END Okl_Setupproducts_Pvt;