1 PACKAGE BODY pay_hours_by_rate AS
2 /* $Header: payhoursbyrate.pkb 120.1 2006/04/13 14:39 ahanda noship $ */
3 /* ******************************************************************
4 * *
5 * Copyright (C) 1992 Oracle Corporation UK Ltd., *
6 * Chertsey, England. *
7 * *
8 * All rights reserved. *
9 * *
10 * This material has been provided pursuant to an agreement *
11 * containing restrictions on its use. The material is also *
12 * protected by copyright law. No part of this material may *
13 * be copied or distributed, transmitted or transcribed, in *
14 * any form or by any means, electronic, mechanical, magnetic, *
15 * manual, or otherwise, or disclosed to third parties without *
16 * the express written permission of Oracle Corporation UK Ltd, *
17 * Oracle Park, Bittams Lane, Guildford Road, Chertsey, Surrey, *
18 * England. *
19 * *
20 ******************************************************************
21
22 Change List
23 -----------
24 Date Name Vers Bug No Description
25 ----------- ---------- ----- ------- -----------------------------------
26 01-FEB-2005 ahanda 115.0 4118279 Created
27 13-APR-2006 ahanda 115.1 Added logic to get amount and return
28
29 */
30
31 FUNCTION get_input_value(p_element_type_id in number
32 ,p_input_value_name in varchar2)
33 RETURN NUMBER
34 IS
35 cursor c_input_value_id is
36 select piv.input_value_id
37 from pay_input_values_f piv
38 where piv.name = p_input_value_name
39 and piv.element_type_id = p_element_type_id;
40
41 ln_input_value_id NUMBER;
42
43 BEGIN
44 open c_input_value_id;
45 fetch c_input_value_id into ln_input_value_id;
46 close c_input_value_id;
47
48 return(ln_input_value_id);
49 END get_input_value;
50
51 FUNCTION get_result_value(p_run_result_id in number
52 ,p_element_type_id in number
53 ,p_mode in varchar2)
54 RETURN NUMBER
55 IS
56
57 cursor c_get_run_result(cp_run_result_id in number
58 ,cp_input_value_id in number) is
59 select prrv.result_value
60 from pay_run_result_values prrv
61 where prrv.run_result_id = cp_run_result_id
62 and prrv.input_value_id = cp_input_value_id;
63
64 ln_return NUMBER;
65
66 BEGIN
67 hr_utility.trace('Called get_result_value');
68 hr_utility.trace('p_mode=' ||p_mode);
69 hr_utility.trace('p_run_result_id=' ||p_run_result_id);
70 hr_utility.trace('p_element_type_id='||p_element_type_id);
71
72 /*****************************************************************
73 ** The element_type_id passed to the package will always be the
74 ** seeded element, so, it will have the Hours, Rate and
75 ** Multiple input values.
76 ** We are checking to see if the input value package variable
77 ** is null and also the element_type_id passed is the same as
78 ** before to ensure that we only call this once. Reason to check
79 ** for element is that this view/package is global so could be
80 ** called for different element type_id for diff legislation.
81 *****************************************************************/
82 if (gn_element_type_id = -1 or
83 gn_element_type_id <> p_element_type_id or
84 gn_hour_input_value_id is null) then
85
86 hr_utility.trace('Called Input Value');
87 gn_amt_input_value_id := get_input_value(p_element_type_id, 'Pay Value');
88 gn_rate_input_value_id := get_input_value(p_element_type_id, 'Rate');
89 gn_hour_input_value_id := get_input_value(p_element_type_id, 'Hours');
90 gn_mult_input_value_id := get_input_value(p_element_type_id, 'Multiple');
91 gn_element_type_id := p_element_type_id;
92 end if;
93
94 hr_utility.trace('gn_run_result_id=' ||gn_run_result_id);
95 /*****************************************************************
96 ** Check if the run_result_id passed to it has change other we
97 ** return from the cached information.
98 *****************************************************************/
99 if gn_run_result_id = -1 or gn_run_result_id <> p_run_result_id then
100 gn_run_result_id := p_run_result_id;
101 gn_amt_result_value := null;
102 gn_rate_result_value := null;
103 gn_hour_result_value := null;
104 gn_mult_result_value := null;
105
106 hr_utility.trace('Called Run Result');
107 open c_get_run_result(p_run_result_id, gn_rate_input_value_id);
108 fetch c_get_run_result into gn_rate_result_value;
109 close c_get_run_result;
110
111 open c_get_run_result(p_run_result_id, gn_hour_input_value_id);
112 fetch c_get_run_result into gn_hour_result_value;
113 close c_get_run_result;
114
115 open c_get_run_result(p_run_result_id, gn_mult_input_value_id);
116 fetch c_get_run_result into gn_mult_result_value;
117 close c_get_run_result;
118
119 open c_get_run_result(p_run_result_id, gn_amt_input_value_id);
120 fetch c_get_run_result into gn_amt_result_value;
121 close c_get_run_result;
122 end if;
123
124 if p_mode = 'Rate' then
125 ln_return := gn_rate_result_value;
126 elsif p_mode = 'Hours' then
127 ln_return := gn_hour_result_value;
128 elsif p_mode = 'Multiple' then
129 ln_return := gn_mult_result_value;
130 elsif p_mode = 'Pay Value' then
131 ln_return := gn_amt_result_value;
132 end if;
133
134 hr_utility.trace('Exiting get_result_value - ln_return='|| ln_return);
135 return(ln_return);
136
137 END get_result_value;
138
139 BEGIN
140 gn_amt_input_value_id := null;
141 gn_rate_input_value_id := null;
142 gn_hour_input_value_id := null;
143 gn_mult_input_value_id := null;
144 gn_element_type_id := -1;
145 gn_run_result_id := -1;
146
147
148 END pay_hours_by_rate;
[Home] [Help]