1 package body hr_cost as
2 /* $Header: pycostng.pkb 120.1.12010000.6 2008/09/15 12:26:01 pparate ship $ */
3 --
4 --
5 -- Copyright (c) Oracle Corporation 1991, 1992, 1993, 1994 All rights reserved.
6 --
7 /*
8 NAME
9 pycostng.pkb
10 --
11 DESCRIPTION
12 Procedures used for the costing process.
13 ie. called by file: pycos.lpc.
14 --
15 MODIFIED (DD-MON-YYYY)
16 alogue 23-FEB-2007 - Added p_element_type_id to get_context_value.
17 alogue 20-MAY-2005 - New get_rr_date function.
18 alogue 07-DEC-2004 - New get_context_value function.
19 alogue 22-MAR-2004 - New cost_bal_adj code and remove old obsolete
20 code.
21 alogue 08-JUL-1997 - Enhanced code to deal with negative
22 adjustments.
23 cadams 19-Mar-1996 - Fixed problem with distribute where if result_val
24 was < 0, the differance would be result_val*2 by
25 dropping the sign from result_val.
26 mwcallag 20-MAR-1995 - Removed procedure get_suspense. Now
27 performed in the 'C' code.
28 mwcallag 16-AUG-1994 - Major changes for new functionality.
29 See pycos.lpc for more information.
30 M Kaddir 16-AUG-1993 - Replaced all references to pay_name_translations
31 with hr_lookups
32 mwcallag 15-MAR-1993 - close cursor dist_rrv added
33 mwcallag 03-MAR-1993 - created.
34 */
35 -- Cache for get_rr_date
36 g_element_entry_id pay_element_entries_f.source_id%type := -1;
37 g_creator_type pay_element_entries_f.creator_type%type;
38 /*----------------------------- cost_bal_adj ---------------------------------*/
39 /*
40 NAME
41 cost_bal_adj - return whether a balance adjustment result should be
42 costed
43 --
44 DESCRIPTION
45 The function returns 'Y' if a balance adjustement should be costed ie
46 BALANCE_ADJ_COST_FLAG is = 'Y' for the element entry passed in
47 */
48 function cost_bal_adj
49 (
50 p_element_entry_id in number,
51 p_baladj_date in date
52 ) return varchar2 is
53 cost_ba varchar2(1);
54 --
55 BEGIN
56
57 select nvl(ee.balance_adj_cost_flag, 'N')
58 into cost_ba
59 from pay_element_entries_f ee
60 where ee.element_entry_id = p_element_entry_id
61 and p_baladj_date between ee.effective_start_date
62 and ee.effective_end_date;
63
64 return (cost_ba);
65
66 EXCEPTION
67 when others then
68 return('N');
69 END cost_bal_adj;
70 --
71 /*-------------------------- get_context_value ---------------------------------*/
72 /*
73 NAME
74 get_context_value - returns a value for a given context for a given
75 run result
76 --
77 DESCRIPTION
78 The function returns the value of a given context for a given run result
79 */
80 function get_context_value
81 (
82 p_inp_val_name in varchar2,
83 p_run_result_id in number,
84 p_element_type_id in number,
85 p_eff_date in date
86 ) return varchar2 is
87 cnt_value varchar2(60); --pay_run_result_values.result_value%type;
88 --
89 BEGIN
90
91 select prrv.result_value
92 into cnt_value
93 from pay_run_result_values prrv,
94 pay_input_values_f piv
95 where prrv.run_result_id = p_run_result_id
96 and piv.name = p_inp_val_name
97 and piv.input_value_id = prrv.input_value_id
98 and piv.element_type_id = p_element_type_id
99 and p_eff_date between piv.effective_start_date
100 and piv.effective_end_date;
101
102 return (cnt_value);
103
104 EXCEPTION
105 when others then
106 return(null);
107 END get_context_value;
108 --
109 /*-------------------------- get_rr_date ---------------------------------*/
110 /*
111 NAME
112 get_rr_date - returns real date of run result with an end_date
113 --
114 DESCRIPTION
115 The function returns the read date of a run result with an end_date.
116 Returns the end_date if it is a prorated run result
117 Retunrs p_date_earned if the result is derived form a Retro Entry
118 */
119 function get_rr_date
120 (
121 p_source_id in number,
122 p_source_type in varchar2,
123 p_end_date in date,
124 p_date_earned in date
125 ) return date is
126 res_date date;
127 l_start_date date;
128 l_creator_type pay_element_entries_f.creator_type%type;
129 --
130 BEGIN
131
132 res_date := p_end_date;
133
134 if (p_source_type = 'E') then
135
136 if (p_source_id = g_element_entry_id) then
137 if (g_creator_type in ('RR', 'EE', 'NR', 'PR')) then
138 res_date := p_date_earned;
139
140 end if;
141 else
142
143 g_element_entry_id := p_source_id;
144 g_creator_type := 'E';
145
146 select creator_type
147 into l_creator_type
148 from pay_element_entries_f
149 where element_entry_id = p_source_id
150 and rownum = 1;
151
152 if (l_creator_type in ('RR', 'EE', 'NR', 'PR')) then
153 res_date := p_date_earned;
154 g_creator_type := l_creator_type;
155
156 end if;
157
158 end if;
159 end if;
160
161 return(res_date);
162
163 EXCEPTION
164 when others then
165 return(res_date);
166 END get_rr_date;
167
168 --
169
170 /*-------------------------- get_cost_date ---------------------------------*/
171 /*
172 NAME
173 get_cost_date - returns date date_paid/date_earned for costing
174 elements based on action parameter COST_DATE_PAID.
175 --
176 DESCRIPTION
177 The function returns date on which the retro element is to be
178 costed based on action parameter COST_DATE_PAID. When set to N
179 date earned for the retro element is returned. If this parameter
180 is not set or set to Y, then this function behaves exactly as
181 get_rr_date.
182 */
183 function get_cost_date
184 (
185 p_source_id in number,
186 p_source_type in varchar2,
187 p_end_date in date,
188 p_date_earned in date
189 ) return date is
190 res_date date;
191 l_start_date date;
192 l_creator_type pay_element_entries_f.creator_type%type;
193 --
194 BEGIN
195
196 res_date := p_end_date;
197
198 if (p_source_type = 'E') then
199
200 if (p_source_id = g_element_entry_id) then
201 if (g_creator_type in ('RR', 'EE', 'NR', 'PR')) then
202 res_date := p_date_earned;
203
204 /*
205 * Bug 7279918: Retro elements to be costed
206 * on start date so as to cost them against
207 * the right organization in case of org change
208 */
209 DECLARE
210 l_cost_date_paid pay_action_parameters.parameter_value%TYPE := 'Y';
211 BEGIN
212 select parameter_value
213 into l_cost_date_paid
214 from pay_action_parameters
215 where parameter_name = 'COST_DATE_PAID';
216
217 if l_cost_date_paid is not null and l_cost_date_paid = 'N' then
218
219 select start_date
220 into l_start_date
221 from pay_run_results
222 where source_id = p_source_id;
223
224 if l_start_date is not null then
225 res_date := l_start_date;
226 end if;
227
228 end if;
229
230 EXCEPTION
231 when others then
232 hr_utility.trace ('Retro costing: Noraml Processing');
233 END;
234
235 end if;
236 else
237
238 g_element_entry_id := p_source_id;
239 g_creator_type := 'E';
240
241 select creator_type
242 into l_creator_type
243 from pay_element_entries_f
244 where element_entry_id = p_source_id
245 and rownum = 1;
246
247 if (l_creator_type in ('RR', 'EE', 'NR', 'PR')) then
248 res_date := p_date_earned;
249 g_creator_type := l_creator_type;
250
251 /*
252 * Bug 7279918: Retro elements to be costed
253 * on start date so as to cost them against
254 * the right organization in case of org change
255 */
256 DECLARE
257 l_cost_date_paid pay_action_parameters.parameter_value%TYPE := 'Y';
258 BEGIN
259 select parameter_value
260 into l_cost_date_paid
261 from pay_action_parameters
262 where parameter_name = 'COST_DATE_PAID';
263
264 if l_cost_date_paid is not null and l_cost_date_paid = 'N' then
265
266 select start_date
267 into l_start_date
268 from pay_run_results
269 where source_id = p_source_id;
270
271 if l_start_date is not null then
272 res_date := l_start_date;
273 end if;
274
275 end if;
276
277 EXCEPTION
278 when others then
279 hr_utility.trace ('Retro costing: Noraml Processing');
280 END;
281
282 end if;
283
284 end if;
285 end if;
286
287 return(res_date);
288
289 EXCEPTION
290 when others then
291 return(res_date);
292 END get_cost_date;
293 --
294
295 END hr_cost;
[Home] [Help]