1 PACKAGE BODY EDW_LOOKUP_PKG AS
2 /* $Header: poafklkb.pls 120.1 2005/06/13 12:42:35 sriswami noship $ */
3
4 Function Lookup_code_fk(
5 p_lookup_table in VARCHAR2,
6 p_lookup_type in VARCHAR2,
7 p_lookup_code in VARCHAR2) return VARCHAR2 IS
8
9 v_lookup VARCHAR2(240) := 'NA_EDW';
10 cursor c is
11 select lookup_code_pk
12 from edw_lookup_code_fkv
13 where lookup_type = p_lookup_type
14 and lookup_code = p_lookup_code
15 and table_code = p_lookup_table;
16
17 BEGIN
18 if(p_lookup_table is not NULL and
19 p_lookup_type is not NULL and
20 p_lookup_code is not NULL) then
21
22 OPEN c;
23 FETCH c into v_lookup;
24 CLOSE c;
25 end if;
26
27 return (v_lookup);
28
29
30 EXCEPTION when others then
31 if c%ISOPEN then
32 CLOSE c;
33 end if;
34
35 return('NA_EDW');
36
37 END Lookup_code_fk;
38
39
40 Function Lookup_code_fk(
41 p_lookup_table in VARCHAR2,
42 p_lookup_type in VARCHAR2,
43 p_lookup_code in number) return VARCHAR2 IS
44
45 v_lookup VARCHAR2(240) := 'NA_EDW';
46 cursor c is
47 select lookup_code_pk
48 from edw_lookup_code_fkv
49 where lookup_type = p_lookup_type
50 and lookup_code = to_char (p_lookup_code)
51 and table_code = p_lookup_table;
52
53 BEGIN
54 if(p_lookup_table is not NULL and
55 p_lookup_type is not NULL and
56 p_lookup_code is not NULL) then
57
58 OPEN c;
59 FETCH c into v_lookup;
60 CLOSE c;
61 end if;
62
63 return (v_lookup);
64
65 EXCEPTION when others then
66 if c%isopen then
67 close c;
68 end if;
69 return('NA_EDW');
70
71 END Lookup_code_fk;
72
73 END EDW_LOOKUP_PKG; --package body
[Home] [Help]