[Home] [Help]
PACKAGE BODY: APPS.PQP_GB_PSI_GRADE_CODES
Source
1 PACKAGE BODY PQP_GB_PSI_GRADE_CODES AS
2 -- /* $Header: pqpgbpsigrd.pkb 120.0 2006/04/13 04:59:48 anshghos noship $ */
3
4 -- Exceptions
5 hr_application_error exception;
6 pragma exception_init (hr_application_error, -20001);
7
8
9 g_nested_level NUMBER(5) := pqp_utilities.g_nested_level;
10
11 -- ----------------------------------------------------------------------------
12 -- |--------------------------------< debug >---------------------------------|
13 -- ----------------------------------------------------------------------------
14
15 PROCEDURE DEBUG (p_trace_message IN VARCHAR2, p_trace_location IN NUMBER)
16 IS
17
18 --
19 BEGIN
20 --
21
22 pqp_utilities.DEBUG (
23 p_trace_message => p_trace_message
24 ,p_trace_location => p_trace_location
25 );
26 --
27 END DEBUG;
28
29
30 -- This procedure is used for debug purposes
31 -- debug_enter checks the debug flag and sets the trace on/off
32 --
33 -- ----------------------------------------------------------------------------
34 -- |----------------------------< debug_enter >-------------------------------|
35 -- ----------------------------------------------------------------------------
36
37 PROCEDURE debug_enter (p_proc_name IN VARCHAR2, p_trace_on IN VARCHAR2)
38 IS
39 BEGIN
40 --
41 IF g_debug THEN
42 IF pqp_utilities.g_nested_level = 0 THEN
43 hr_utility.trace_on(NULL, 'REQID'); -- Pipe name REQIDnnnnn
44 END IF;
45 pqp_utilities.debug_enter (
46 p_proc_name => p_proc_name
47 ,p_trace_on => p_trace_on
48 );
49 END IF;
50 --
51
52 END debug_enter;
53
54
55 -- This procedure is used for debug purposes
56 --
57 -- ----------------------------------------------------------------------------
58 -- |----------------------------< debug_exit >--------------------------------|
59 -- ----------------------------------------------------------------------------
60
61 PROCEDURE debug_exit (p_proc_name IN VARCHAR2, p_trace_off IN VARCHAR2)
62 IS
63 BEGIN
64 --
65 IF g_debug THEN
66 pqp_utilities.debug_exit (
67 p_proc_name => p_proc_name
68 ,p_trace_off => p_trace_off
69 );
70
71 IF pqp_utilities.g_nested_level = 0 THEN
72 hr_utility.trace_off;
73 END IF;
74 END IF;
75 --
76 END debug_exit;
77
78
79 -- This procedure is used for debug purposes
80 --
81 -- ----------------------------------------------------------------------------
82 -- |----------------------------< debug_others >------------------------------|
83 -- ----------------------------------------------------------------------------
84
85 PROCEDURE debug_others (p_proc_name IN VARCHAR2, p_proc_step IN NUMBER)
86 IS
87 BEGIN
88 --
89 pqp_utilities.debug_others (
90 p_proc_name => p_proc_name
91 ,p_proc_step => p_proc_step
92 );
93 --
94 END debug_others;
95
96
97
98 -- This procedure is used to clear all cached global variables
99 --
100 -- ----------------------------------------------------------------------------
101 -- |----------------------------< clear_cache >-------------------------------|
102 -- ----------------------------------------------------------------------------
103 PROCEDURE clear_cache
104 IS
105 --
106 l_proc_name VARCHAR2(80) := g_proc_name || 'clear_cache';
107 l_proc_step PLS_INTEGER;
108 --
109 BEGIN
110 --
111 IF g_debug
112 THEN
113 l_proc_step := 10;
114 debug_enter(l_proc_name);
115 END IF;
116
117
118
119 IF g_debug
120 THEN
121 debug_exit(l_proc_name);
122 END IF;
123 EXCEPTION
124 WHEN others THEN
125 IF SQLCODE <> hr_utility.hr_error_number
126 THEN
127 debug_others (l_proc_name, l_proc_step);
128 IF g_debug
129 THEN
130 DEBUG ( 'Leaving: '
131 || l_proc_name, -999);
132 END IF;
133 fnd_message.raise_error;
134 ELSE
135 RAISE;
136 END IF;
137 END clear_cache;
138
139
140 -- ----------------------------------------------------------------------------
141 -- |------------------------< chk_grade_codes_crit >-------------------|
142 -- ----------------------------------------------------------------------------
143
144 FUNCTION chk_grade_codes_crit
145 (p_business_group_id IN NUMBER
146 ,p_grade_id IN VARCHAR2
147 )
148 RETURN VARCHAR2
149 IS
150 --
151 l_proc_name VARCHAR2(61):=
152 g_proc_name||'chk_grade_codes_crit';
153
154 l_return VARCHAR2(1) := 'N';
155 l_grade_id NUMBER;
156 l_value NUMBER;
157
158 --
159 BEGIN
160 --
161
162
163 debug_enter(l_proc_name);
164
165 IF g_business_group_id IS NULL
166 THEN
167 clear_cache;
168 g_business_group_id := p_business_group_id;
169 g_paypoint := PQP_GB_PSI_FUNCTIONS.paypoint(p_business_group_id);
170 g_debug := pqp_gb_psi_functions.check_debug(g_business_group_id);
171
172 IF g_paypoint = ''
173 or
174 NOT PQP_GB_PSI_FUNCTIONS.is_alphanumeric(p_string=> g_paypoint)
175 THEN
176 l_value := PQP_GB_PSI_FUNCTIONS.raise_extract_warning
177 (p_assignment_id => -1
178 ,p_error_number => 94453
179 ,p_error_text => 'BEN_94453_INV_PAYPOINT'
180 );
181 END IF;
182
183 END IF;
184
185 debug('Entering chk_grade_codes_crit ...',10);
186
187 l_grade_id := FND_NUMBER.canonical_to_number(p_grade_id);
188
189
190 IF l_grade_id = -1
191 THEN
192 l_return := 'Y';
193 debug('l_grade_id (was null) : ' || l_grade_id, 14);
194 ELSE
195
196 OPEN csr_grade_details
197 (p_business_group_id => p_business_group_id
198 ,p_grade_id => p_grade_id
199 );
200 FETCH csr_grade_details into g_grade_details;
201 IF csr_grade_details%FOUND THEN
202 l_return := 'Y';
203 -- check for special characters
204 IF NOT PQP_GB_PSI_FUNCTIONS.is_alphanumeric
205 (p_string=> g_grade_details.information2) THEN
206
207 debug('raising warning for : '|| g_grade_details.name);
208 l_value := PQP_GB_PSI_FUNCTIONS.raise_extract_warning
209 (p_assignment_id => -(p_grade_id)
210 ,p_error_number => 94452
211 ,p_error_text => 'BEN_94452_INV_GRADE_CODE'
212 ,p_token1 => g_grade_details.name
213 );
214 END IF;
215 ELSE
216 l_return := 'N';
217 END IF;
218 CLOSE csr_grade_details;
219 END IF;
220
221 debug('l_return : ' || l_return,17);
222
223 debug('Exiting chk_grade_codes_crit ...',20);
224 debug_exit(l_proc_name);
225
226 return l_return;
227
228 EXCEPTION
229 WHEN others THEN
230 IF SQLCODE <> hr_utility.hr_error_number
231 THEN
232 debug_others (l_proc_name, 10);
233 IF g_debug
234 THEN
235 DEBUG ( 'Leaving: '
236 || l_proc_name, -999);
237 END IF;
238 fnd_message.raise_error;
239 ELSE
240 RAISE;
241 END IF;
242
243 END chk_grade_codes_crit;
244
245
246
247
248 -- ----------------------------------------------------------------------------
249 -- |------------------------< grade_extract_main >----------------------------|
250 -- ----------------------------------------------------------------------------
251
252 FUNCTION grade_extract_main
253 (p_rule_parameter IN VARCHAR2 -- parameter
254 ,p_output OUT NOCOPY VARCHAR2
255 )
256 RETURN number IS
257 --
258
259 l_proc_name VARCHAR2(61):=
260 g_proc_name||'grade_extract_main';
261 l_value number;
262 l_effective_date DATE;
263 --
264 BEGIN
265
266 debug_enter(l_proc_name);
267
268 -- switch on the trace
269
270 debug('Entering basic_extract_main ...',0);
271 debug('p_rule_parameter'||p_rule_parameter,1);
272
273
274 -- select the function call based on the parameter being passed to the rule
275 IF p_rule_parameter = 'PayPoint' THEN
276 debug('About to enter PayPoint location',20);
277 p_output := g_paypoint;
278 debug('paypoint : '|| p_output,25);
279
280 ELSIF p_rule_parameter = 'SubsGrade' THEN
281 p_output := g_grade_details.information2;
282 debug('SubsGrade : '|| p_output,30);
283 ELSIF p_rule_parameter = 'GradeDesc' THEN
284 p_output := g_grade_details.information3;
285 debug('GradeDesc : '|| p_output,35);
286 ELSIF p_rule_parameter = 'IndGradeFlag' THEN
287 p_output := g_grade_details.information4;
288 debug('IndGradeFlag : '|| p_output,40);
289 ELSIF p_rule_parameter = 'UniformGrade' THEN
290 p_output := g_grade_details.information5;
291 debug('UniformGrade : '|| p_output,45);
292
293 ELSE
294 p_output := '';
295 END IF;
296
297 debug_exit(l_proc_name);
298 RETURN 0;
299
300 EXCEPTION
301 WHEN others THEN
302 IF SQLCODE <> hr_utility.hr_error_number
303 THEN
304 debug_others (l_proc_name, 10);
305 IF g_debug
306 THEN
307 DEBUG ( 'Leaving: '
308 || l_proc_name, -999);
309 END IF;
310 fnd_message.raise_error;
311 ELSE
312 RAISE;
313 END IF;
314
315 END grade_extract_main;
316
317
318 -- ----------------------------------------------------------------------------
319 -- |------------------------< grade_codes_post_processing >------------------|
320 -- ----------------------------------------------------------------------------
321
322 FUNCTION grade_codes_post_processing RETURN VARCHAR2
323 IS
324
325 l_proc_name VARCHAR2(61):=
326 g_proc_name||'grade_codes_post_processing';
327
328 BEGIN -- grade_codes_post_proc_rule
329
330 debug_enter(l_proc_name);
331
332 debug_exit(l_proc_name);
333
334 RETURN 'Y';
335
336 EXCEPTION
337 WHEN others THEN
338 IF SQLCODE <> hr_utility.hr_error_number
339 THEN
340 debug_others (l_proc_name, 10);
341 IF g_debug
342 THEN
343 DEBUG ( 'Leaving: '
344 || l_proc_name, -999);
345 END IF;
346 fnd_message.raise_error;
347 ELSE
348 RAISE;
349 END IF;
350
351 END grade_codes_post_processing; -- basic_data_post_proc_rule
352
353
354 END PQP_GB_PSI_GRADE_CODES;