[Home] [Help]
5: l_meaning in varchar2) is
6: select 'X'
7: from pay_input_values_f piv,
8: hr_lookups hrl,
9: pay_batch_lines pbl
10:
11: where l_meaning = piv.name -- restrict to input value parameter
12: and piv.name = hrl.meaning -- join piv-hrl on name of input value
13:
68:
69: -- cursor to return all the distinct elements in a given batch
70: cursor csr_distinct_elements(l_batch_id in number) is
71: select distinct element_type_id, element_name
72: from pay_batch_lines
73: where batch_id=l_batch_id;
74:
75: -- cursor to return an ordered list of input value names according to
76: -- the criteria used by the BEE form for associating input values with
73: where batch_id=l_batch_id;
74:
75: -- cursor to return an ordered list of input value names according to
76: -- the criteria used by the BEE form for associating input values with
77: -- pay_batch_lines columns
78:
79: cursor csr_ordered_input_values(l_element_type_id in number) is
80: select piv.name
81: from pay_input_values_f piv
89: l_element_total number;
90: -- for the dynamic sql statements used in totalling
91: l_sql_stat varchar2(1000);
92: -- for a single batch line
93: l_batch_line pay_batch_lines%rowtype;
94: -- for the (translated) meanings from hr_lookups
95: l_meaning hr_lookups.meaning%TYPE := null;
96: -- for the number of lines in a batch
97: l_n_lines number := 0;
94: -- for the (translated) meanings from hr_lookups
95: l_meaning hr_lookups.meaning%TYPE := null;
96: -- for the number of lines in a batch
97: l_n_lines number := 0;
98: -- for the column of pay_batch_lines holding the values to be summed
99: l_value_number number;
100: -- batches bus grp id
101: l_business_group_id number;
102: -- status of BEE_IV_UPG upgrade
127:
128: -- get number of lines in batch
129:
130: select count(*) into l_n_lines
131: from pay_batch_lines bal
132: where bal.batch_id = p_batch_id;
133:
134: if(l_n_lines <> nvl(p_control_total,0)) then
135:
178: l_bee_iv_upgrade := pay_core_utils.get_upgrade_status(l_business_group_id,'BEE_IV_UPG');
179:
180: for l_element in csr_distinct_elements(p_batch_id) loop
181:
182: -- get the column number in pay_batch_lines that we need to sum.
183: -- We do this by finding the first column in pay_batch_lines that matches
184: -- the control total name.
185:
186: l_value_number:=0;
179:
180: for l_element in csr_distinct_elements(p_batch_id) loop
181:
182: -- get the column number in pay_batch_lines that we need to sum.
183: -- We do this by finding the first column in pay_batch_lines that matches
184: -- the control total name.
185:
186: l_value_number:=0;
187: for l_input_value in csr_ordered_input_values(l_element.element_type_id) loop
191: -- found the number of the value column that contains the values for l_meaning
192: -- Build and excute dynamic sql statment to do the summation
193: --
194: if (l_bee_iv_upgrade = 'N') then
195: l_sql_stat:='select sum(value_'||l_value_number||') from pay_batch_lines where element_type_id='||l_element.element_type_id||' and batch_id='||p_batch_id;
196: else
197: l_sql_stat:='select sum(fnd_number.canonical_to_number(value_'||l_value_number||')) from pay_batch_lines where element_type_id='||l_element.element_type_id||' and batch_id='||p_batch_id;
198: end if;
199: --
193: --
194: if (l_bee_iv_upgrade = 'N') then
195: l_sql_stat:='select sum(value_'||l_value_number||') from pay_batch_lines where element_type_id='||l_element.element_type_id||' and batch_id='||p_batch_id;
196: else
197: l_sql_stat:='select sum(fnd_number.canonical_to_number(value_'||l_value_number||')) from pay_batch_lines where element_type_id='||l_element.element_type_id||' and batch_id='||p_batch_id;
198: end if;
199: --
200: begin
201: execute immediate l_sql_stat into l_element_total;