[Home] [Help]
PACKAGE BODY: APPS.PQP_GB_SWF_VALIDATIONS
Source
1 PACKAGE BODY PQP_GB_SWF_VALIDATIONS AS
2 /* $Header: pqpgbswfv.pkb 120.0.12010000.1 2009/12/07 10:04:16 parusia noship $ */
3 /* Copyright (c) Oracle Corporation 2005. All rights reserved. */
4 /*
5 PRODUCT
6 Oracle Public Sector Payroll - GB Localisation School Workforce
7
8 NAME
9 PQP_GB_SWF_VALIDATIONS package
10
11 DESCRIPTION
12 This package contains utility functions for School Workforce
13 configuration setup validation.
14
15 MODIFICATION HISTORY
16 Person Date Version Bug Comments
17 --------- ---------- -------------- ------- --------------------------------
18 P Arusia 8-Jul-2009 115.0 8682922 This package contains utility
19 functions for School Workforce
20 configuration setup validation
21 */
22
23 -------------------------------------------------------------------------------
24 -- PACKAGE BODY --
25 -------------------------------------------------------------------------------
26
27 function get_lookup_meaning (p_lookup_type varchar2
28 , p_code varchar2)
29 return varchar2 is
30 cursor csr_lookup_meaning is
31 select meaning
32 from hr_lookups
33 where lookup_type = p_lookup_type
34 and lookup_code = p_code ;
35 l_meaning varchar2(100);
36 begin
37 open csr_lookup_meaning;
38 fetch csr_lookup_meaning into l_meaning ;
39 close csr_lookup_meaning ;
40
41 return l_meaning ;
42
43 end get_lookup_meaning ;
44
45
46 --
47 /* procedure to verify that an application information value should not
48 be already mapped with any DCSF value.
49 Returns true if the value is not already mapped
50 Retruns false if the value is already mapped
51 */
52 --
53 procedure chk_single_unique_mapping(p_configuration_value_id number
54 , p_business_group_id number
55 , p_pcv_information_category varchar2
56 , p_information_column varchar2
57 , p_value varchar2
58 , p_return out NOCOPY boolean
59 ) as
60 l_count number;
61 l_query varchar2(4000) ;
62 l_value_start_meaning varchar2(100);
63 l_value_end_meaning varchar2(100);
64 begin
65 l_query := 'select count(*)
66 from pqp_configuration_values
67 where pcv_information_category = '''|| p_pcv_information_category || '''' ||
68 ' and business_group_id = ''' || p_business_group_id || '''' ||
69 ' and ' || p_information_column || ' = ''' || p_value || '''' ||
70 ' and ( ' || nvl(to_char(p_configuration_value_id),'null') || ' is null ' ||
71 ' or configuration_value_id <> ' || nvl(to_char(p_configuration_value_id),'null') || ' )';
72 hr_utility.trace('l_query = '|| l_query );
73
74 execute immediate l_query into l_count;
75
76 if l_count > 0 then
77 p_return := false ;
78 else
79 p_return := true ;
80 end if ;
81 end chk_single_unique_mapping;
82
83 --
84 /* procedure to verify that an application information value should not
85 be already mapped with any DCSF value when it is possible to
86 map a range of values directly with a DCSF code.
90 --
87 Returns true if the value is not already mapped
88 Retruns false if the value is already mapped
89 */
91 procedure chk_range_unique_mapping(
92 p_configuration_value_id number
93 , p_business_group_id number
94 , p_pcv_information_category varchar2
95 , p_information_start_column varchar2
96 , p_information_end_column varchar2
97 , p_value_start varchar2
98 , p_value_end varchar2
99 , p_return out NOCOPY boolean
100 ) as
101 l_count number;
102 l_query varchar2(4000) ;
103 l_value_end varchar2(100);
104 begin
105 if p_value_end is null then
106 l_value_end := p_value_start ;
107 else
108 l_value_end := p_value_end;
109 end if;
110
111 l_query := 'select count(*)
112 from pqp_configuration_values
113 where pcv_information_category = '''|| p_pcv_information_category || '''' ||
114 ' and business_group_id = ''' || p_business_group_id || '''' ||
115 ' and ( ' || p_information_start_column || ' between ''' || p_value_start || '''' ||
116 ' and ''' || l_value_end || ''' ' ||
117 ' or ' || nvl(p_information_end_column, p_information_start_column) ||
118 ' between ''' || p_value_start || '''' ||
119 ' and ''' || l_value_end || ''' ' ||
120 ' or ''' || p_value_start || ''' between '|| p_information_start_column ||
121 ' and ' || nvl(p_information_end_column, p_information_start_column) ||
122 ' or ''' || l_value_end || ''' between '|| p_information_start_column ||
123 ' and ' || nvl(p_information_end_column, p_information_start_column) ||
124 ' ) ' ||
125 ' and ( ' || nvl(to_char(p_configuration_value_id),'null') || ' is null ' ||
126 ' or configuration_value_id <> ' || nvl(to_char(p_configuration_value_id),'null') || ' )';
127
128 hr_utility.trace('l_query = '|| l_query );
129
130 execute immediate l_query into l_count;
131
132 if l_count > 0 then
133 p_return := false ;
134 else
135 p_return := true ;
136 end if ;
137 end chk_range_unique_mapping;
138
139
140 --
141 /* procedure to verify that a spine point value should not
142 be already mapped with any DCSF value.
143 Returns true if the value is not already mapped
144 Retruns false if the value is already mapped
145 */
146 --
147 procedure chk_spine_pt_unique_mapping(
148 p_configuration_value_id number
149 , p_business_group_id number
150 , p_pcv_information_category varchar2
151 , p_payscale_column varchar2
152 , p_information_start_column varchar2
153 , p_information_end_column varchar2
154 , p_payscale_value varchar2
155 , p_value_start varchar2
156 , p_value_end varchar2
157 , p_return out NOCOPY boolean
158 ) as
159 l_count number;
160 l_query varchar2(4000) ;
161 l_updated_val_end varchar2(100);
162 l_updated_val_start varchar2(100);
163 begin
164 hr_utility.trace('Spine Point mapping -- ');
165 if p_value_start is null then
166 l_updated_val_start := 'null';
167 else
168 l_updated_val_start := ''''||p_value_start||'''';
169 end if;
170
171 if p_value_end is null then
172 l_updated_val_end := l_updated_val_start ;
173 else
174 l_updated_val_end := ''''||p_value_end||'''';
175 end if;
176
177 l_query := 'select count(*)
178 from pqp_configuration_values
179 where pcv_information_category = '''|| p_pcv_information_category || '''' ||
180 ' and business_group_id = ''' || p_business_group_id || '''' ||
181 ' and ' || p_payscale_column || ' = ''' || p_payscale_value || '''' ||
182 ' and ( ' || l_updated_val_start || ' is null ' ||
183 ' or ' || p_information_start_column || ' is null ' ||
184 ' or ' || p_information_start_column || ' between ' || l_updated_val_start ||
185 ' and ' || l_updated_val_end ||
186 ' or ' || nvl(p_information_end_column, p_information_start_column) ||
187 ' between ' || l_updated_val_start ||
188 ' and ' || l_updated_val_end ||
189 ' or ' || l_updated_val_start || ' between '|| p_information_start_column ||
190 ' and ' || nvl(p_information_end_column, p_information_start_column) ||
191 ' or ' || l_updated_val_end || ' between '|| p_information_start_column ||
192 ' and ' || nvl(p_information_end_column, p_information_start_column) ||
193 ' ) ' ||
194 ' and ( ' || nvl(to_char(p_configuration_value_id),'null') || ' is null ' ||
195 ' or configuration_value_id <> ' || nvl(to_char(p_configuration_value_id),'null') || ' )';
196
197 hr_utility.trace('l_query = '|| l_query );
198
199 execute immediate l_query into l_count;
200
201 if l_count > 0 then
202 p_return := false ;
203 else
204 p_return := true ;
205 end if ;
206 end chk_spine_pt_unique_mapping;
207
208 --
209 /* procedure to verify that a Hours configuration for a contract type
210 should not be already done.
211 Returns true if the the hours data is NOT already mapped for given contract type
212 Retruns false if it is already mapped
213 */
214 --
215 procedure chk_hours_cntrct_tp_unq_map(
216 p_configuration_value_id number
217 , p_business_group_id number
218 , p_pcv_information_category varchar2
219 , p_information_column varchar2
220 , p_value varchar2
221 , p_return out NOCOPY boolean
222 ) as
223 l_count number;
224 l_query varchar2(4000) ;
225 l_updated_val varchar2(50);
226 begin
227 if p_value is null then
228 l_updated_val := 'null';
229 else
230 l_updated_val := ''''||p_value||'''';
231 end if;
232 l_query := 'select count(*)
233 from pqp_configuration_values
234 where pcv_information_category = '''|| p_pcv_information_category || '''' ||
235 ' and business_group_id = ''' || p_business_group_id || '''' ||
236 ' and ( ' || l_updated_val || ' is null ' ||
237 ' or ' || p_information_column || ' is null ' ||
238 ' or ' || p_information_column || ' = ' || l_updated_val ||
239 ' ) ' ||
240 ' and ( ' || nvl(to_char(p_configuration_value_id),'null') || ' is null ' ||
241 ' or configuration_value_id <> ' || nvl(to_char(p_configuration_value_id),'null') || ' )';
242
243 hr_utility.trace('l_query = '|| l_query );
244
245 execute immediate l_query into l_count;
246
247 if l_count > 0 then
248 p_return := false ;
249 else
250 p_return := true ;
251 end if ;
252 end chk_hours_cntrct_tp_unq_map;
253
254
255 /* This procedure returns true, if for the given configuration_type
256 only the lookup name mentioned currently is being used. If some other
257 lookup name has been used, then it retruns false
258 */
259 procedure chk_unique_lookup_name(
260 p_configuration_value_id number
261 , p_business_group_id number
262 , p_pcv_information_category varchar2
263 , p_information_column varchar2
264 , p_value varchar2
265 , p_return out NOCOPY boolean
266 ) IS
267 l_count number;
268 l_query varchar2(4000) ;
269 begin
270 l_query := 'select count(*)
271 from pqp_configuration_values
272 where pcv_information_category = '''|| p_pcv_information_category || '''' ||
273 ' and business_group_id = ''' || p_business_group_id || '''' ||
274 ' and ' || p_information_column || ' <> ''' || p_value || '''' ||
275 ' and (' || nvl(to_char(p_configuration_value_id),'null') || ' is null ' ||
276 ' or configuration_value_id <> ' || nvl(to_char(p_configuration_value_id),'null') || ' )';
277 hr_utility.trace('l_query = '|| l_query );
278
279 execute immediate l_query into l_count;
280
281 if l_count > 0 then
282 p_return := false ;
283 else
284 p_return := true ;
285 end if ;
286 end chk_unique_lookup_name ;
287
288 END PQP_GB_SWF_VALIDATIONS;