1 package BODY OKL_UTIL AS
2 /* $Header: OKLRUTLB.pls 120.7.12010000.3 2008/11/17 06:24:01 kkorrapo ship $ */
3
4
5
6 ----------------------------------------------------------------------------
7 --Get country FOR before active line
8 ----------------------------------------------------------------------------
9
10 FUNCTION get_preactive_line_inst
11 (p_financial_line IN NUMBER)
12 RETURN VARCHAR2 IS
13
14 CURSOR c_install_site ( p_k_financial_line NUMBER)IS
15 SELECT party_site_uses.country
16 FROM
17 OKC_K_LINES_B FREEFORM2_CLE,
18 OKC_LINE_STYLES_B FREEFORM2_LSE,
19 OKC_K_LINES_B INSTITEM_CLE,
20 OKC_LINE_STYLES_B INSTITEM_LSE,
21 OKL_TXL_ITM_INSTS OTITM ,
22 OKX_PARTY_SITE_USES_V PARTY_SITE_USES,
23 OKX_COUNTRIES_V OKX_COUNTRY
24 WHERE party_site_uses.country = okx_country.id1
25 AND party_site_uses.site_use_type = 'INSTALL_AT'
26 AND PARTY_SITE_USES.ID1 = OTITM.object_id1_new -- location
27 AND OTITM.OBJECT_ID2_NEW = PARTY_SITE_USES.ID2
28 AND OTITM.jtot_object_code_new = 'OKX_PARTSITE'
29 AND INSTITEM_CLE.ID = OTITM.KLE_ID
30 AND INSTITEM_CLE.CLE_ID = FREEFORM2_CLE.ID
31 AND INSTITEM_CLE.LSE_ID = INSTITEM_LSE.ID
32 AND INSTITEM_LSE.lty_code = 'INST_ITEM'
33 AND FREEFORM2_CLE.CLE_ID = p_k_financial_line
34 AND FREEFORM2_CLE.LSE_ID = FREEFORM2_LSE.ID
35 AND FREEFORM2_LSE.lty_code = 'FREE_FORM2' ;
36
37
38 l_country_code OKX_COUNTRIES_V.id1%TYPE;
39
40 BEGIN
41
42
43 OPEN c_install_site(p_financial_line);
44 FETCH c_install_site INTO l_country_code;
45 CLOSE c_install_site;
46
47 RETURN(l_country_code);
48
49
50 End get_preactive_line_inst;
51
52
53
54 ----------------------------------------------------------------------------
55 -- --Get country FOR after active line
56 ----------------------------------------------------------------------------
57
58 FUNCTION get_active_line_inst_country
59 (p_financial_line IN NUMBER)
60 RETURN VARCHAR2 IS
61
62 CURSOR c_install_site ( p_k_financial_line NUMBER)IS
63 select PARTY_SITES_USES.country
64 from
65 OKC_K_LINES_B instance_CLE,
66 OKC_LINE_STYLES_B instance_LS,
67 OKC_K_LINES_B IB_CLE,
68 OKC_LINE_STYLES_B IB_LS,
69 CSI_ITEM_INSTANCES INSTALL_BASE,
70 OKC_K_ITEMS INSTANCE_ITEM,
71 HZ_PARTY_SITES PARTY_SITES,
72 OKX_PARTY_SITE_USES_V PARTY_SITES_USES
73 WHERE
74 PARTY_SITES_USES.site_use_type = 'INSTALL_AT'
75 AND PARTY_SITES_USES.PARTY_SITE_ID = PARTY_SITES.PARTY_SITE_ID
76 AND PARTY_SITES.PARTY_SITE_ID = INSTALL_BASE.INSTALL_LOCATION_ID
77 AND INSTALL_BASE.INSTALL_LOCATION_TYPE_CODE = 'HZ_PARTY_SITES' -- Fix for Canon bug 3551010
78 AND INSTALL_BASE.instance_id = INSTANCE_ITEM.object1_id1 AND --Fix for 3837619
79 '#' = INSTANCE_ITEM.object1_id2 AND
80 INSTANCE_ITEM.CLE_ID = IB_CLE.ID
81 and IB_LS.LTY_CODE = 'INST_ITEM'
82 AND IB_LS.ID = IB_CLE.LSE_ID
83 AND IB_CLE.cle_id = instance_CLE.ID
84 AND instance_LS.LTY_CODE = 'FREE_FORM2'
85 AND instance_LS.ID = instance_CLE.LSE_ID
86 AND instance_CLE.cle_id = p_k_financial_line
87 union
88 select HZ_LOCATIONS.country
89 from
90 OKC_K_LINES_B instance_CLE,
91 OKC_LINE_STYLES_B instance_LS,
92 OKC_K_LINES_B IB_CLE,
93 OKC_LINE_STYLES_B IB_LS,
94 CSI_ITEM_INSTANCES INSTALL_BASE,
95 OKC_K_ITEMS INSTANCE_ITEM,
96 HZ_LOCATIONS HZ_LOCATIONS
97 WHERE
98 HZ_LOCATIONS.LOCATION_ID = INSTALL_BASE.INSTALL_LOCATION_ID
99 AND INSTALL_BASE.INSTALL_LOCATION_TYPE_CODE = 'HZ_LOCATIONS' -- Fix for Canon bug 3551010
100 AND INSTALL_BASE.instance_id = INSTANCE_ITEM.object1_id1 AND --Fix for Bug 3837619
101 '#' = INSTANCE_ITEM.object1_id2 AND
102 INSTANCE_ITEM.CLE_ID = IB_CLE.ID
103 and IB_LS.LTY_CODE = 'INST_ITEM'
104 AND IB_LS.ID = IB_CLE.LSE_ID
105 AND IB_CLE.cle_id = instance_CLE.ID
106 AND instance_LS.LTY_CODE = 'FREE_FORM2'
107 AND instance_LS.ID = instance_CLE.LSE_ID
108 AND instance_CLE.cle_id = p_k_financial_line ;
109
110
111 l_country_code OKX_COUNTRIES_V.id1%TYPE;
112
113
114
115 BEGIN
116
117
118 OPEN c_install_site(p_financial_line);
119 FETCH c_install_site INTO l_country_code;
120 CLOSE c_install_site;
121
122 RETURN(l_country_code);
123
124
125 End get_active_line_inst_country;
126
127
128
129
130
131
132
133
134
135
136
137 ---------------------------------------------------------------------
138
139 ---Get Record Status
140 ---------------------------------------------------------------------
141 FUNCTION get_rec_status (p_start_date IN DATE, p_end_date IN DATE)
142 RETURN VARCHAR2 IS
143
144 lv_sysdate DATE := TRUNC(SYSDATE);
145
146 BEGIN
147
148 IF TRUNC(p_start_date) <= lv_sysdate AND NVL(TRUNC(p_end_date),
149 lv_sysdate) >= lv_sysdate THEN
150 RETURN 'ACTIVE';
151 ELSIF TRUNC(p_start_date) > lv_sysdate THEN
152 RETURN 'FUTURE';
153 ELSIF TRUNC(p_end_date) < lv_sysdate THEN
154 RETURN 'EXPIRED';
155 ELSE
156 RETURN 'UNKNOWN';
157 END IF;
158
159 END get_rec_status;
160
161
162 ---------------------------------------------------------
163 FUNCTION check_from_to_number_range(p_from_number IN NUMBER ,p_to_number IN NUMBER ) RETURN VARCHAR2 IS
164 x_return_status VARCHAR2(1) := Okc_Api.G_RET_STS_SUCCESS;
165 BEGIN
166 IF (p_from_number IS NULL) OR (p_to_number IS NULL) THEN
167 x_return_status := Okc_Api.G_RET_STS_ERROR;
168 ELSE
169 IF (p_to_number < p_from_number) THEN
170 x_return_status := Okc_Api.G_RET_STS_ERROR;
171 END IF;
172 END IF;
173 RETURN (x_return_status);
174 EXCEPTION
175 WHEN OTHERS THEN
176 x_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
177 RETURN(x_return_status);
178 END check_from_to_number_range;
179 ---------------------------------------------------------------------------
180 -- Lookup Code Validation
181 ---------------------------------------------------------------------------
182 FUNCTION check_lookup_code(p_lookup_type IN VARCHAR2,
183 p_lookup_code IN VARCHAR2) RETURN VARCHAR2 IS
184 x_return_status VARCHAR2(1):= Okl_Api.G_RET_STS_SUCCESS;
185 l_sysdate DATE := SYSDATE ;
186 l_dummy_var VARCHAR2(1) := '?';
187 CURSOR l_lookup_code_csr IS
188 SELECT 'X'
189 FROM fnd_lookups fndlup
190 WHERE fndlup.lookup_type = p_lookup_type
191 AND fndlup.lookup_code = p_lookup_code
192 AND l_sysdate BETWEEN
193 NVL(fndlup.start_date_active,l_sysdate)
194 AND NVL(fndlup.end_date_active,l_sysdate);
195 BEGIN
196 OPEN l_lookup_code_csr;
197 FETCH l_lookup_code_csr INTO l_dummy_var;
198 CLOSE l_lookup_code_csr;
199 -- if l_dummy_var still set to default, data was not found
200 IF (l_dummy_var = '?') THEN
201 -- notify caller of an error
202 x_return_status := Okl_Api.G_RET_STS_ERROR;
203 END IF;
204 RETURN (x_return_status);
205 EXCEPTION
206 WHEN OTHERS THEN
207 -- notify caller of an UNEXPECTED error
208 x_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
209 -- verify that cursor was closed
210 IF l_lookup_code_csr%ISOPEN THEN
211 CLOSE l_lookup_code_csr;
212 END IF;
213 RETURN(x_return_status);
214 END check_lookup_code;
215
216 ----------------------------------------------------------------------------
217 FUNCTION check_domain_yn(p_col_value IN VARCHAR2)RETURN VARCHAR2 IS
218 x_return_status VARCHAR2(1):= Okl_Api.G_RET_STS_SUCCESS;
219 BEGIN
220 IF (p_col_value IS NULL)OR (p_col_value = OKC_API.G_MISS_CHAR) THEN
221 x_return_status:=Okl_Api.G_RET_STS_ERROR;
222 ELSE
223 IF UPPER(p_col_value) NOT IN('Y','N') THEN
224 x_return_status:=Okl_Api.G_RET_STS_ERROR;
225 END IF;
226 END IF;
227 RETURN (x_return_status);
228 EXCEPTION
229 WHEN OTHERS THEN
230 x_return_status:=Okl_Api.G_RET_STS_UNEXP_ERROR;
231 RETURN (x_return_status);
232 END check_domain_yn;
233 ---------------------------------------------------------------------------
234 FUNCTION check_domain_amount (p_col_value IN NUMBER)
235 RETURN VARCHAR2 IS
236 x_return_status VARCHAR2(1):= Okl_Api.G_RET_STS_SUCCESS;
237 BEGIN
238 IF (p_col_value IS NULL) OR (p_col_value = Okl_Api.G_MISS_NUM) THEN
239 x_return_status:=Okl_Api.G_RET_STS_ERROR;
240 --check in domain
241 ELSE
242 IF p_col_value < 0 THEN
243 x_return_status:=Okl_Api.G_RET_STS_ERROR;
244 END IF;
245 END IF;
246 RETURN (x_return_status);
247 EXCEPTION
248 WHEN OTHERS THEN
249 -- notify UNEXPECTED error
250 x_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
251 RETURN (x_return_status);
252 END check_domain_amount;
253 ---------------------------------------------------------------------
254 FUNCTION check_from_to_date_range(p_from_date IN DATE,p_to_date IN DATE )
255 RETURN VARCHAR2 IS
256 x_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
257 BEGIN
258 IF p_from_date IS NOT NULL AND
259 p_to_date IS NOT NULL THEN
260 IF p_to_date < p_from_date THEN
261 x_return_status :=Okl_Api.G_RET_STS_ERROR;
262 END IF;
263 ELSIF (p_from_date IS NULL) AND
264 (p_to_date IS NOT NULL) THEN
265 x_return_status :=Okl_Api.G_RET_STS_ERROR;
266 END IF;
267 RETURN (x_return_status);
268 EXCEPTION
269 WHEN OTHERS THEN
270 x_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
271 RETURN(x_return_status);
272 END check_from_to_date_range;
273
274
275
276
277 FUNCTION check_org_id (
278 p_org_id IN VARCHAR2,
279 p_null_allowed IN VARCHAR2 DEFAULT 'Y')
280 RETURN VARCHAR2 IS
281
282 l_result VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
283 l_select_result VARCHAR2(1) := '?';
284 l_sysdate DATE := SYSDATE;
285
286 CURSOR C1 IS
287 SELECT 'S'
288 FROM hr_all_organization_units hou -- gboomina modified for Bug 6691305
289 WHERE hou.organization_id = TO_NUMBER(p_org_id)
290 AND l_sysdate BETWEEN NVL (hou.date_from, l_sysdate)
291 AND NVL (hou.date_to, l_sysdate);
292
293 BEGIN
294
295 IF NVL (p_null_allowed, 'N') <> 'Y' AND p_org_id IS NULL THEN
296 l_result := Okl_Api.G_RET_STS_UNEXP_ERROR;
297 END IF;
298
299 IF p_org_id IS NOT NULL THEN
300 OPEN C1;
301 FETCH C1 INTO l_select_result;
302 CLOSE C1;
303 IF l_select_result ='?' THEN
304 l_result := Okl_Api.G_RET_STS_ERROR;
305 END IF;
306 END IF;
307
308 RETURN (l_result);
309
310 EXCEPTION
311 WHEN OTHERS THEN
312 l_result:=Okl_Api.G_RET_STS_UNEXP_ERROR;
313 IF C1%ISOPEN THEN
314 CLOSE C1;
315 END IF;
316 RETURN l_result;
317
318 END check_org_id;
319
320 --Bug 7022258-Added by kkorrapo
321 FUNCTION get_next_seq_num(
322 p_seq_name IN VARCHAR2,
323 p_table_name IN VARCHAR2,
324 p_col_name IN VARCHAR2)
325 RETURN VARCHAR2
326 IS
327 l_next_val NUMBER;
328 l_col_value VARCHAR2(150);
329 l_seq_stmt VARCHAR2(100);
330 l_query_stmt VARCHAR2(100);
331 TYPE l_csr_typ IS REF CURSOR;
332 l_ref_csr l_csr_typ;
333 CURSOR c_get_prefix(c_table_name IN VARCHAR2) IS
334 SELECT DECODE(c_table_name,'OKL_LEASE_QUOTES_B',LSEQTE_SEQ_PREFIX_TXT,'OKL_QUICK_QUOTES_B',QCKQTE_SEQ_PREFIX_TXT,'OKL_LEASE_OPPORTUNITIES_B',LSEOPP_SEQ_PREFIX_TXT,'OKL_LEASE_APPLICATIONS_B',LSEAPP_SEQ_PREFIX_TXT)
335 FROM okl_system_params;
336 l_prefix VARCHAR2(30);
337 l_value VARCHAR(250);
338 BEGIN
339 l_next_val := 0;
340 l_seq_stmt := 'SELECT ' || p_seq_name || '.NEXTVAL FROM DUAL';
341 l_query_stmt := 'SELECT ' ||
342 p_col_name ||
343 ' FROM ' ||
344 p_table_name ||
345 ' WHERE '||
346 p_col_name || ' = :1 ';
347 --get prefix
348 OPEN c_get_prefix(p_table_name);
349 FETCH c_get_prefix INTO l_prefix;
350 CLOSE c_get_prefix;
351
352 LOOP
353 --Execute the dynamic sql for obtaining next value of sequence
354 OPEN l_ref_csr FOR l_seq_stmt;
355 FETCH l_ref_csr INTO l_next_val;
356 IF l_ref_csr%NOTFOUND THEN
357 EXIT;
358 END IF;
359 CLOSE l_ref_csr;
360
361
362 IF l_prefix IS NOT NULL THEN
363 l_value := l_prefix || TO_CHAR(l_next_val);
364 ELSE
365 l_value := TO_CHAR(l_next_val);
366 END IF;
367
368 --Execute the dynamic sql for validating uniqueness of the next value from sequence
369 OPEN l_ref_csr FOR l_query_stmt USING l_value;
370 FETCH l_ref_csr INTO l_col_value;
371 IF l_ref_csr%NOTFOUND THEN
372 EXIT;
373 END IF;
374 CLOSE l_ref_csr;
375 END LOOP;
376 RETURN l_value;
377 EXCEPTION
378 WHEN OTHERS
379 THEN
380 IF l_ref_csr%ISOPEN
381 THEN
382 CLOSE l_ref_csr;
383 END IF;
384 RETURN 0;
385 END get_next_seq_num;
386 FUNCTION validate_seq_num(
387 p_seq_name IN VARCHAR2,
388 p_table_name IN VARCHAR2,
389 p_col_name IN VARCHAR2,
390 p_value IN VARCHAR2)
391 RETURN varchar2
392 IS
393 l_col_value VARCHAR2(150);
394 l_query_stmt VARCHAR2(100);
395 TYPE l_csr_typ IS REF CURSOR;
396 l_ref_csr l_csr_typ;
397 CURSOR c_get_prefix(c_table_name IN VARCHAR2) IS
398 SELECT DECODE(c_table_name,'OKL_LEASE_QUOTES_B',LSEQTE_SEQ_PREFIX_TXT,'OKL_QUICK_QUOTES_B',QCKQTE_SEQ_PREFIX_TXT,'OKL_LEASE_OPPORTUNITIES_B',LSEOPP_SEQ_PREFIX_TXT,'OKL_LEASE_APPLICATIONS_B',LSEAPP_SEQ_PREFIX_TXT)
399 FROM okl_system_params;
400 l_prefix VARCHAR2(30);
401 BEGIN
402 l_query_stmt := 'SELECT ' ||
403 p_col_name ||
404 ' FROM ' ||
405 p_table_name ||
406 ' WHERE '||
407 p_col_name || ' = :1 ';
408 --get prefix
409 OPEN c_get_prefix(p_table_name);
410 FETCH c_get_prefix INTO l_prefix;
411 CLOSE c_get_prefix;
412
413 IF l_prefix IS NOT NULL THEN
414 IF INSTR(p_value,l_prefix) <> 1 THEN
415 okl_api.set_message(p_app_name => g_app_name
416 ,p_msg_name => 'OKL_NO_PREFIX'
417 ,p_token1 => 'COL_NAME'
418 ,p_token1_value => p_value
419 ,p_token2 => 'PREFIX'
420 ,p_token2_value => l_prefix);
421 RETURN 'N';
422 END IF;
423 END IF;
424
425 --Execute the dynamic sql for validating uniqueness of the next value from sequence
426 OPEN l_ref_csr FOR l_query_stmt USING p_value;
427 FETCH l_ref_csr INTO l_col_value;
428 IF l_ref_csr%NOTFOUND THEN
429 CLOSE l_ref_csr;
430 RETURN 'Y';
431 ELSE
432 CLOSE l_ref_csr;
433 okl_api.set_message(p_app_name => g_app_name
434 ,p_msg_name => 'OKL_DUPLICATE_CURE_REQUEST'
435 ,p_token1 => 'COL_NAME'
436 ,p_token1_value => p_value);
437 RETURN 'N';
438 END IF;
439 EXCEPTION
440 WHEN OTHERS
441 THEN
442 IF l_ref_csr%ISOPEN
443 THEN
444 CLOSE l_ref_csr;
445 END IF;
446 RETURN 0;
447 END validate_seq_num;
448 --Bug 7022258--Addition end
449
450 END Okl_Util;
[Home] [Help]