1 PACKAGE BODY HR_TMPLT as
2 /* $Header: hrfcrlct.pkb 120.2 2005/06/09 18:02:46 pganguly noship $ */
3 function get_item_name(
4 p_item_id in number ,
5 p_application_id in number ,
6 p_form_id in number
7 )
8 return varchar2 is
9 l_return varchar2(80);
10 begin
11 if p_item_id is null then
12 l_return := null;
13 else
14 select full_item_name
15 into l_return
16 from hr_form_items_b
17 where form_item_id = p_item_id
18 and application_id = p_application_id
19 and form_id = p_form_id;
20 end if;
21 return l_return;
22 end get_item_name;
23
24 function get_formula_name(p_formula_id in number) return varchar2 is
25 l_return varchar2(80);
26 begin
27 if p_formula_id is null then
28 l_return := null;
29 else
30 select formula_name
31 into l_return
32 from ff_formulas_f
33 where formula_id = p_formula_id
34 and sysdate between effective_start_date and effective_end_date;
35 end if;
36 return l_return;
37 end get_formula_name;
38
39 FUNCTION get_formula_name(p_element_type_id IN NUMBER,
40 p_effective_date IN DATE) RETURN VARCHAR2 IS
41 l_return varchar2(80);
42
43 CURSOR cur_formula_name IS
44 SELECT
45 ff.formula_name
46 FROM
47 pay_status_processing_rules_f pspr,
48 ff_formulas_f ff
49 WHERE
50 pspr.element_type_id = p_element_type_id AND
51 p_effective_date BETWEEN pspr.effective_start_date AND
52 pspr.effective_end_date AND
53 pspr.formula_id = ff.formula_id AND
54 p_effective_date BETWEEN ff.effective_start_date AND
55 ff.effective_end_date;
56 BEGIN
57
58 IF p_element_type_id IS NULL THEN
59 l_return := null;
60 ELSE
61 OPEN cur_formula_name;
62 FETCH cur_formula_name
63 INTO l_return;
64 CLOSE cur_formula_name;
65 END IF;
66
67 RETURN l_return;
68
69 END get_formula_name;
70
71 function get_legislation_name(p_formula_id in number) return varchar2 is
72 l_return varchar2(80);
73 l_leg_code varchar2(30);
74 begin
75 if p_formula_id is null then
76 l_return := null;
77 else
78 select legislation_code
79 into l_leg_code
80 from ff_formulas_f
81 where formula_id = p_formula_id
82 and sysdate between effective_start_date and effective_end_date;
83
84 if l_leg_code is null then
85 l_return := null;
86 else
87 select territory_short_name
88 into l_return
89 from fnd_territories_vl
90 where territory_code = l_leg_code;
91 end if;
92 end if;
93 return l_return;
94 end get_legislation_name;
95 function get_bus_group_name(p_formula_id in number) return varchar2 is
96 l_return varchar2(60);
97 l_bus_group_id number;
98 begin
99 if p_formula_id is null then
100 l_return := null;
101 else
102 select business_group_id
103 into l_bus_group_id
104 from ff_formulas_f
105 where formula_id = p_formula_id
106 and sysdate between effective_start_date and effective_end_date;
107
108 if l_bus_group_id is null then
109 l_return := null;
110 else
111 select name
112 into l_return
113 from per_business_groups
114 where business_group_id = l_bus_group_id;
115 end if;
116 end if;
117 return l_return;
118 end get_bus_group_name;
119 function get_tab_page_name(
120 p_form_tab_page_id in number,
121 p_form_canvas_id in number
122 )
123 return varchar2 is
124 l_return varchar2(30);
125 begin
126 if p_form_tab_page_id is null then
127 l_return := null;
128 else
129 select tab_page_name
130 into l_return
131 from hr_form_tab_pages_b
132 where form_tab_page_id = p_form_tab_page_id
133 and form_canvas_id = p_form_canvas_id;
134 end if;
135 return l_return;
136 end get_tab_page_name;
137 function get_template_name(
138 p_form_template_id in number
139 )
140 return varchar2 is
141 l_return varchar2(30);
142 begin
143 if p_form_template_id is null then
144 l_return := null;
145 else
146 select template_name
147 into l_return
148 from hr_form_templates_b
149 where form_template_id = p_form_template_id;
150 end if;
151 return l_return;
152 end get_template_name;
153
154 function get_application_name(
155 p_form_template_id in number
156 )
157 return varchar2 is
158 l_return varchar2(50);
159 begin
160 if p_form_template_id is null then
161 l_return := null;
162 else
163 select application_short_name
164 into l_return
165 from hr_form_templates_b hft,
166 fnd_application fa
167 where hft.form_template_id = p_form_template_id
168 and fa.application_id = hft.application_id;
169 end if;
170 return l_return;
171 end get_application_name;
172
173 function get_form_name(
174 p_form_template_id in number
175 )
176 return varchar2 is
177 l_return varchar2(50);
178 begin
179 if p_form_template_id is null then
180 l_return := null;
181 else
182 select form_name
183 into l_return
184 from hr_form_templates_b hft,
185 fnd_form ff
186 where hft.form_template_id = p_form_template_id
187 and ff.application_id = hft.application_id
188 and ff.form_id = hft.form_id;
189 end if;
190 return l_return;
191 end get_form_name;
192
193 FUNCTION get_balance_category(
194 p_category_id IN NUMBER
195 )
196 RETURN VARCHAR2 IS
197 l_return pay_balance_categories_f.category_name%TYPE;
198
199 CURSOR cur_balance_category
200 IS SELECT
201 bcf.category_name
202 FROM
203 pay_balance_categories_f bcf
204 WHERE
205 bcf.balance_category_id = p_category_id AND
206 SYSDATE BETWEEN bcf.effective_start_date AND
207 bcf.effective_end_date;
208
209 BEGIN
210
211 IF p_category_id IS NULL then
212 l_return := NULL;
213 ELSE
214 OPEN cur_balance_category;
215 FETCH cur_balance_category
216 INTO l_return;
217 CLOSE cur_balance_category;
218 END IF;
219
220 RETURN l_return;
221
222 END get_balance_category;
223
224 END HR_TMPLT;
[Home] [Help]