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