[Home] [Help]
PACKAGE BODY: APPS.PAY_KR_BAL_INIT_PKG
Source
1 package body pay_kr_bal_init_pkg as
2 /* $Header: pykrbini.pkb 120.1 2005/06/30 03:58:54 pdesu noship $ */
3 --
4 -- Constants
5 --
6 c_package varchar2(31) := ' pay_kr_bal_init_pkg.';
7 c_sot date := fnd_date.canonical_to_date('0001/01/01');
8 c_eot date := fnd_date.canonical_to_date('4712/12/31');
9 c_iv_limit number := 15;
10 ------------------------------------------------------------------------
11 procedure create_structure(
12 p_batch_id in number,
13 p_classification_id in number,
14 p_element_name_prefix in varchar2)
15 ------------------------------------------------------------------------
16 is
17 l_proc varchar2(61) := c_package || 'create_bal_init_struct';
18 l_business_group_id per_business_groups_perf.business_group_id%TYPE;
19 l_business_group_name per_business_groups_perf.name%TYPE;
20 l_legislation_code per_business_groups_perf.legislation_code%TYPE;
21 l_currency_code per_business_groups_perf.currency_code%TYPE;
22 l_classification_name pay_element_classifications.classification_name%TYPE;
23 l_element_type_id pay_element_types_f.element_type_id%TYPE;
24 l_element_name pay_element_types_f.element_name%TYPE;
25 l_element_link_id number;
26 l_input_value_id number;
27 l_counter number;
28 l_dummy varchar2(255);
29 --
30 -- Cursor to derive necessary parameters in later phase.
31 --
32 cursor csr_init is
33 select pbg.business_group_id,
34 pbg.name,
35 pbg.legislation_code,
36 pec.classification_name,
37 pbg.currency_code
38 from pay_element_classifications pec,
39 per_business_groups_perf pbg,
40 pay_balance_batch_headers h
41 where h.batch_id = p_batch_id
42 and upper(pbg.name) = upper(h.business_group_name)
43 and pec.classification_id = p_classification_id
44 and pec.balance_initialization_flag = 'Y'
45 and nvl(pec.business_group_id, pbg.business_group_id) = pbg.business_group_id
46 and nvl(pec.legislation_code, pbg.legislation_code) = pbg.legislation_code;
47 --
48 -- Cursor to return balance types without balance initialization element feed
49 -- for current batch_id
50 --
51 cursor csr_balance_wo_feed is
52 select pbt.balance_type_id,
53 pbt.balance_name,
54 pbt.balance_uom
55 from pay_balance_types pbt
56 where upper(pbt.balance_name) IN
57 (
58 select upper(balance_name) balance_name
59 from pay_balance_batch_lines
60 where batch_id = p_batch_id
61 )
62 and nvl(pbt.business_group_id, l_business_group_id) = l_business_group_id
63 and nvl(pbt.legislation_code, l_legislation_code) = l_legislation_code
64 and not exists(
65 select null
66 from pay_element_classifications pec,
67 pay_element_types_f pet,
68 pay_input_values_f piv,
69 pay_balance_feeds_f pbf
70 where pbf.balance_type_id = pbt.balance_type_id
71 and pbf.effective_start_date = c_sot
72 and pbf.effective_end_date = c_eot
73 and nvl(pbf.business_group_id, l_business_group_id) = l_business_group_id
74 and nvl(pbf.legislation_code, l_legislation_code) = l_legislation_code
75 and piv.input_value_id = pbf.input_value_id
76 and piv.effective_start_date = c_sot
77 and piv.effective_end_date = c_eot
78 and pet.element_type_id = piv.element_type_id
79 and pet.effective_start_date = c_sot
80 and pet.effective_end_date = c_eot
81 and pec.classification_id = pet.classification_id
82 and pec.balance_initialization_flag = 'Y')
83 order by pbt.balance_uom, pbt.balance_name;
84 --------------------------------------------------------------
85 procedure create_et_el(
86 p_element_type_id out NOCOPY number,
87 p_element_name out NOCOPY varchar2,
88 p_element_link_id out NOCOPY number)
89 --------------------------------------------------------------
90 is
91 function element_name return varchar2
92 is
93 l_max_seq number;
94 l_seq number;
95 cursor csr_et is
96 select element_name
97 from pay_element_types_f
98 where element_name like replace(p_element_name, '_', '\_') || '%' escape '\';
99 begin
100 l_max_seq := 1;
101 p_element_name := p_element_name_prefix || '_';
102 --
103 for l_rec in csr_et loop
104 begin
105 l_seq := to_number(replace(l_rec.element_name, p_element_name));
106 exception
107 when others then
108 exit;
109 end;
110 --
111 if l_seq >= l_max_seq then
112 l_max_seq := l_seq + 1;
113 end if;
114 end loop;
115 --
116 return p_element_name || to_char(l_max_seq);
117 end element_name;
118 begin
119 p_element_name := element_name;
120 --
121 p_element_type_id := pay_db_pay_setup.create_element(
122 p_element_name => p_element_name,
123 p_effective_start_date => c_sot,
124 p_effective_end_date => c_eot,
125 p_classification_name => l_classification_name,
126 p_input_currency_code => l_currency_code,
127 p_output_currency_code => l_currency_code,
128 p_processing_type => 'N',
129 p_adjustment_only_flag => 'Y',
130 p_process_in_run_flag => 'Y',
131 p_business_group_name => l_business_group_name,
132 p_post_termination_rule => 'Final Close');
133 --
134 update pay_element_types_f
135 set element_information1 = 'B'
136 where element_type_id = p_element_type_id;
137 --
138 p_element_link_id := pay_db_pay_setup.create_element_link(
139 p_element_name => p_element_name,
140 p_link_to_all_pyrlls_fl => 'Y',
141 p_standard_link_flag => 'N',
142 p_effective_start_date => c_sot,
143 p_effective_end_date => c_eot,
144 p_business_group_name => l_business_group_name);
145 end create_et_el;
146 --------------------------------------------------------------
147 procedure create_iv_ivl_bf(
148 p_balance_type_id in number,
149 p_balance_uom in varchar2,
150 p_element_type_id in number,
151 p_element_name in varchar2,
152 p_element_link_id in number,
153 p_input_value_name in varchar2,
154 p_display_sequence in number)
155 --------------------------------------------------------------
156 is
157 begin
158 l_input_value_id := pay_db_pay_setup.create_input_value(
159 p_element_name => p_element_name,
160 p_name => p_input_value_name,
161 p_uom_code => p_balance_uom,
162 p_business_group_name => l_business_group_name,
163 p_effective_start_date => c_sot,
164 p_effective_end_date => c_eot,
165 p_display_sequence => p_display_sequence);
166 --
167 hr_input_values.create_link_input_value(
168 p_insert_type => 'INSERT_INPUT_VALUE',
169 p_element_link_id => p_element_link_id,
170 p_input_value_id => l_input_value_id,
171 p_input_value_name => p_input_value_name,
172 p_costable_type => NULL,
173 p_validation_start_date => c_sot,
174 p_validation_end_date => c_eot,
175 p_default_value => NULL,
176 p_max_value => NULL,
177 p_min_value => NULL,
178 p_warning_or_error_flag => NULL,
179 p_hot_default_flag => NULL,
180 p_legislation_code => NULL,
181 p_pay_value_name => NULL,
182 p_element_type_id => p_element_type_id);
183 --
184 hr_balances.ins_balance_feed(
185 p_option => 'INS_MANUAL_FEED',
186 p_input_value_id => l_input_value_id,
187 p_element_type_id => p_element_type_id,
188 p_primary_classification_id => NULL,
189 p_sub_classification_id => NULL,
190 p_sub_classification_rule_id => NULL,
191 p_balance_type_id => p_balance_type_id,
192 p_scale => '1',
193 p_session_date => c_sot,
194 p_business_group => l_business_group_id,
195 p_legislation_code => NULL,
196 p_mode => 'USER');
197 end create_iv_ivl_bf;
198 begin
199 hr_utility.set_location(l_proc, 10);
200 --
201 hr_api.mandatory_arg_error(
202 p_api_name => l_proc,
203 p_argument => 'batch_id',
204 p_argument_value => p_batch_id);
205 hr_api.mandatory_arg_error(
206 p_api_name => l_proc,
207 p_argument => 'classification_id',
208 p_argument_value => p_classification_id);
209 l_dummy := p_element_name_prefix;
210 hr_chkfmt.checkformat(
211 value => l_dummy,
212 format => 'PAY_NAME',
213 output => l_dummy,
214 minimum => null,
215 maximum => null,
216 nullok => 'N',
217 rgeflg => l_dummy,
218 curcode => null);
219 --
220 -- Derives necessary local variables and checks the input variables
221 -- are correct or not at the same time.
222 -- The following SQL will raise NO_DATA_FOUND if input variables
223 -- are not correct.
224 --
225 open csr_init;
226 fetch csr_init into
227 l_business_group_id,
228 l_business_group_name,
229 l_legislation_code,
230 l_classification_name,
231 l_currency_code;
232 if csr_init%NOTFOUND then
233 close csr_init;
234 fnd_message.set_name('PAY', 'PAY_KR_BAL_INIT_INV_PARAM');
235 fnd_message.raise_error;
236 end if;
237 close csr_init;
238 --
239 -- Loop of balances without initial balance feed for current batch_id.
240 --
241 for l_rec in csr_balance_wo_feed loop
242 if l_counter is null or l_counter >= c_iv_limit then
243 l_counter := 1;
244 --
245 -- Create element type and element link.
246 --
247 create_et_el(
248 p_element_type_id => l_element_type_id,
249 p_element_name => l_element_name,
250 p_element_link_id => l_element_link_id);
251 else
252 l_counter := l_counter + 1;
253 end if;
254 --
255 -- Create input_value, link_input_value and balance_feed.
256 --
257 create_iv_ivl_bf(
258 p_balance_type_id => l_rec.balance_type_id,
259 p_balance_uom => l_rec.balance_uom,
260 p_element_type_id => l_element_type_id,
261 p_element_name => l_element_name,
262 p_element_link_id => l_element_link_id,
263 p_input_value_name => rtrim(substr(l_rec.balance_name, 1, 27)) || '_' || to_char(l_counter),
264 p_display_sequence => l_counter);
265 end loop;
266 end create_structure;
267 ------------------------------------------------------------------------
268 procedure create_structure(
269 errbuf out NOCOPY varchar2,
270 retcode out NOCOPY number,
271 p_batch_id in number,
272 p_classification_id in number,
273 p_element_name_prefix in varchar2)
274 ------------------------------------------------------------------------
275 is
276 begin
277 --
278 -- errbuf and retcode are special parameters needed for the SRS.
279 -- retcode = 0 means no error and retcode = 2 means an error occurred.
280 --
281 create_structure(
282 p_batch_id => p_batch_id,
283 p_classification_id => p_classification_id,
284 p_element_name_prefix => p_element_name_prefix);
285 --
286 commit;
287 end create_structure;
288 --
289 end pay_kr_bal_init_pkg;