[Home] [Help]
PACKAGE BODY: APPS.PEFRUSDT
Source
1 package body pefrusdt as
2 /* $Header: pefrusdt.pkb 120.1 2005/06/15 01:55:42 sbairagi noship $ */
3 function get_table_value (p_bus_group_id in number,
4 p_table_name in varchar2,
5 p_col_name in varchar2,
6 p_row_value in varchar2,
7 p_effective_date in date default null)
8 return varchar2 is
9 l_effective_date date;
10 l_range_or_match pay_user_tables.range_or_match%type;
11 l_value pay_user_column_instances_f.value%type;
12 cached boolean := FALSE;
13 g_leg_code varchar2(2);
14 begin
15 --
16 -- Use either the supplied date, or the date from fnd_sessions
17 --
18 if (p_effective_date is null) then
19 begin
20 select effective_date
21 into l_effective_date
22 from fnd_sessions
23 where session_id = userenv('sessionid');
24 end;
25 else
26 l_effective_date := p_effective_date;
27 end if;
28 --
29 -- get the legislation code:
30 --
31 begin
32 if cached = FALSE THEN
33 select legislation_code
34 into g_leg_code
35 from per_business_groups
36 where business_group_id = p_bus_group_id;
37 cached := TRUE;
38 end if;
39 end;
40 --
41 -- get the type of query to be performed, either range or match
42 --
43 select range_or_match
44 into l_range_or_match
45 from pay_user_tables
46 where upper(user_table_name) = upper(p_table_name)
47 and nvl (business_group_id,
48 p_bus_group_id) = p_bus_group_id;
49 --
50 if (l_range_or_match = 'M') then -- matched
51 begin
52 select CINST.value
53 into l_value
54 from pay_user_column_instances_f CINST
55 , pay_user_columns C
56 , pay_user_rows_f R
57 , pay_user_tables TAB
58 where upper(TAB.user_table_name) = upper(p_table_name)
59 and nvl (TAB.business_group_id,
60 p_bus_group_id) = p_bus_group_id
61 and nvl (TAB.legislation_code,
62 g_leg_code) = g_leg_code
63 and C.user_table_id = TAB.user_table_id
64 and nvl (C.business_group_id,
65 p_bus_group_id) = p_bus_group_id
66 and nvl (C.legislation_code,
67 g_leg_code) = g_leg_code
68 and upper (C.user_column_name) = upper (p_col_name)
69 and CINST.user_column_id = C.user_column_id
70 and R.user_table_id = TAB.user_table_id
71 and l_effective_date between R.effective_start_date
72 and R.effective_end_date
73 and nvl (R.business_group_id,
74 p_bus_group_id) = p_bus_group_id
75 and nvl (R.legislation_code,
76 g_leg_code) = g_leg_code
77 and decode
78 (TAB.user_key_units,
79 'D', to_char(fnd_date.canonical_to_date(p_row_value)),
80 'N', p_row_value,
81 'T', upper (p_row_value),
82 null) =
83 decode
84 (TAB.user_key_units,
85 'D', to_char(fnd_date.canonical_to_date(R.row_low_range_or_name)),
86 'N', R.row_low_range_or_name,
87 'T', upper (R.row_low_range_or_name),
88 null)
89 and CINST.user_row_id = R.user_row_id
90 and l_effective_date between CINST.effective_start_date
91 and CINST.effective_end_date
92 and nvl (CINST.business_group_id,
93 p_bus_group_id) = p_bus_group_id
94 and nvl (CINST.legislation_code,
95 g_leg_code) = g_leg_code;
96 --
97 return l_value;
98 exception
99 when others then return null;
100 end;
101 else -- range
102 begin
103 select CINST.value
104 into l_value
105 from pay_user_column_instances_f CINST
106 , pay_user_columns C
107 , pay_user_rows_f R
108 , pay_user_tables TAB
109 where upper(TAB.user_table_name) = upper(p_table_name)
110 and nvl (TAB.business_group_id,
111 p_bus_group_id) = p_bus_group_id
112 and nvl (TAB.legislation_code,
113 g_leg_code) = g_leg_code
114 and C.user_table_id = TAB.user_table_id
115 and nvl (C.business_group_id,
116 p_bus_group_id) = p_bus_group_id
117 and nvl (C.legislation_code,
118 g_leg_code) = g_leg_code
119 and upper (C.user_column_name) = upper (p_col_name)
120 and CINST.user_column_id = C.user_column_id
121 and R.user_table_id = TAB.user_table_id
122 and l_effective_date between R.effective_start_date
123 and R.effective_end_date
124 and nvl (R.business_group_id,
125 p_bus_group_id) = p_bus_group_id
126 and nvl (R.legislation_code,
127 g_leg_code) = g_leg_code
128 and to_number (p_row_value)
129 between to_number (R.row_low_range_or_name)
130 and to_number (R.row_high_range)
131 and TAB.user_key_units = 'N'
132 and CINST.user_row_id = R.user_row_id
133 and l_effective_date between CINST.effective_start_date
134 and CINST.effective_end_date
135 and nvl (CINST.business_group_id,
136 p_bus_group_id) = p_bus_group_id
137 and nvl (CINST.legislation_code,
138 g_leg_code) = g_leg_code;
139 --
140 return l_value;
141 exception
142 when others then return null;
143 end;
144 end if;
145
146 end get_table_value;
147 --
148 END pefrusdt;