1 PACKAGE BODY pay_standard_check AS
2 /* $Header: pystdchk.pkb 120.2 2006/01/30 07:08:56 alogue noship $ */
3
4 cursor csr_relevant_total_chk(l_batch_id in number,
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
14 and hrl.lookup_type = 'CONTROL_TYPE' -- restrict to relevant lookup
15 and hrl.lookup_code like '_TOTAL_COLUMN_%' -- must be a standard total
16
17 and pbl.element_type_id = piv.element_type_id -- join piv-pbl on element_type_id
18 and pbl.batch_id = l_batch_id ; -- restrict to batch_id parameter
19 -- and effective_date_condition. depends on wether its the effective date or sysdate thats inportant for processing
20 -- probably the effective date - just use pay_input_values
21
22
23 -- function to wrap up csr_relevant_total_chk. This is called by check_control but
24 -- also by the BEE form.
25
26 function relevant_total_chk(p_batch_id in number,p_meaning in varchar2) return boolean
27 is
28 -- success or failure of cursor
29 l_csr_result varchar2(1);
30
31 begin
32 open csr_relevant_total_chk(p_batch_id,p_meaning);
33 fetch csr_relevant_total_chk into l_csr_result;
34
35 if csr_relevant_total_chk%NOTFOUND then -- dont even attempt summation.
36 close csr_relevant_total_chk;
37 return false;
38 else
39 close csr_relevant_total_chk;
40 return true;
41 end if;
42
43 end relevant_total_chk;
44
45 -- NAME
46 -- pay_user_check.check_control
47 --
48 -- DESCRIPTION
49 -- Given a batch id it will carry out any standard totals
50 -- and insert any necessary messages into the message table and
51 -- update statuses that are required. The p_standard_status is updated
52 -- to true to signify that the control total has been dealt with
53 -- at this point. If the control total is not
54 -- recognised as standard then no action is taken.
55
56 --
57
58
59
60 procedure check_control
61 (
62 p_batch_id in number,
63 p_control_type in varchar2,
64 p_control_total in varchar2,
65 p_std_status out nocopy varchar2,
66 p_std_message out nocopy varchar2
67 ) is
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
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
82 where piv.element_type_id = l_element_type_id
83 and sysdate between piv.effective_start_date and piv.effective_end_date
84 order by piv.display_sequence, piv.name;
85
86
87 -- for totals
88 l_running_total number := 0;
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;
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
103 l_bee_iv_upgrade pay_upgrade_status.status%type;
104
105 --
106 --
107 begin
108
109 hr_utility.set_location('pay_std_check::check_control',1);
110
111
112 p_std_status := 'C';
113 --
114 -- Control code exists (as checked in calling routine) so we can select the meaning
115 --
116 select hlk.meaning into l_meaning
117 from hr_lookups hlk
118 where hlk.lookup_type = 'CONTROL_TYPE'
119 and hlk.lookup_code = p_control_type;
120
121 -- Deal with special values of p_control_type
122
123 if(p_control_type='_COUNT_LINES_') then
124
125 p_std_status := 'V'; -- set to valid to signify the total will be interpreted
126 -- as a standard control
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
136 -- Set status to signify an error and load up message text
137 p_std_status:='E';
138 hr_utility.set_message(801,'HR_34850_WRONG_NUM_BATCH_LINES');
139 hr_utility.set_message_token('N_LINES_IN_BATCH',l_n_lines);
140 hr_utility.set_message_token('N_LINES_CONTROL_TOTAL',p_control_total);
141 p_std_message:=hr_utility.get_message;
142
143 end if;
144
145 end if;
146
147 -- Deal with a standard total
148 if (instr (p_control_type,'_TOTAL_COLUMN_') = 1) then
149 -- only check if control is recognisable as an arithmetic sum.
150 -- _TOTAL_COLUMN_ must be the first characters in the string
151
152 p_std_status := 'V'; -- set to valid
153
154 -- precheck that the total is relevant to this batch
155
156 if (relevant_total_chk(p_batch_id,l_meaning)=false) then
157 -- dont even attempt summation.
158 -- No element in batch has this input value.
159 -- Set status to error and insert relevant error in message table
160 p_std_status:='E';
161 hr_utility.set_message(801,'HR_34851_IRRELEVANT_CONTROL_TOTAL');
162 hr_utility.set_message_token('NAME_OF_ENTRY_TO_SUM',l_meaning);
163 p_std_message:=hr_utility.get_message;
164
165 else
166
167 -- Do the summation. We need to build a sql statement for each
168 -- element individually as the column number (in pay) for the
169 -- same named input value may differ between elements
170
171 -- First of all find out if BEE_IV_UPG upgrade has been executed
172
173 select business_group_id
174 into l_business_group_id
175 from pay_batch_headers
176 where batch_id = p_batch_id;
177
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;
187 for l_input_value in csr_ordered_input_values(l_element.element_type_id) loop
188 l_value_number := l_value_number +1;
189 if(l_input_value.name = l_meaning) then
190
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 --
200 begin
201 execute immediate l_sql_stat into l_element_total;
202 exception -- could not perform summation for some reason
203 when others then
204 p_std_status:='E';
205 hr_utility.set_message(801,'HR_34853_CANT_SUM_COLUMN');
206 hr_utility.set_message_token('NAME_OF_ENTRY_TO_SUM',l_meaning);
207 p_std_message:=hr_utility.get_message;
208 --
209 end;
210 --
211 l_running_total := l_running_total + nvl(l_element_total,0);
212 --
213 exit;
214 end if;
215 end loop;
216
217 end loop;
218
219 -- compare calculated total with control total
220
221 if(l_running_total <> nvl(p_control_total,0)) then
222 p_std_status:='E';
223 hr_utility.set_message(801,'HR_34852_SUMMATION_NE_TOTAL');
224 hr_utility.set_message_token('NAME_OF_ENTRY_TO_SUM',l_meaning);
225 hr_utility.set_message_token('BATCH_TOTAL',l_running_total);
226 hr_utility.set_message_token('CONTROL_TOTAL',p_control_total);
227 p_std_message:=hr_utility.get_message;
228 end if;
229
230 end if;
231 end if;
232
233 end check_control;
234
235
236 end pay_standard_check;
[Home] [Help]