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