1 PACKAGE hr_mee_views_gen AUTHID CURRENT_USER AS
2 /* $Header: hrmegviw.pkh 120.6.12010000.2 2009/07/17 08:22:24 gpurohit ship $ */
3
4 TYPE gtt_segment IS RECORD (
5 value VARCHAR2(240)
6 );
7
8 g_hours_per_day NUMBER:=8;
9
10 --bug 5890210
11 function getCostCenter(
12 p_assignment_id NUMBER
13 ) return varchar2;
14 --bug 5890210
15
16 FUNCTION getCompRatio(
17 p_from_currency IN VARCHAR2
18 ,p_to_currency IN VARCHAR2
19 ,p_annual_salary IN NUMBER
20 ,p_annual_grade_mid_value IN NUMBER
21 ,p_eff_date IN DATE) RETURN NUMBER;
22
23 Function getCompRatio(
24 p_from_currency IN VARCHAR2
25 ,p_to_currency IN VARCHAR2
26 ,p_assignment_id in number
27 ,P_Effective_Date in date
28 ,p_proposed_salary IN NUMBER
29 ,p_pay_annual_factor IN number
30 ,p_pay_basis in varchar2
31 ,p_grade_annual_factor in number
32 ,p_grade_basis in varchar2
33 ,p_grade_mid_value in number
34 ) return number;
35
36 FUNCTION convertAmount(
37 p_from_currency IN VARCHAR2
38 ,p_to_currency IN VARCHAR2
39 ,p_amount IN NUMBER
40 ,p_eff_Date IN DATE DEFAULT NULL
41 ) RETURN NUMBER;
42
43 FUNCTION get_grade_details(
44 p_assignment_id IN number,
45 p_mode in varchar2
46 ) RETURN NUMBER;
47
48 function get_step_details(
49 p_step_id in number,
50 p_eff_date in date,
51 p_mode in varchar2
52 ) return varchar2;
53
54 function get_step_num(
55 p_step_id in number,
56 p_eff_date in date
57 ) return number;
58
59 FUNCTION convertDuration(
60 p_from_duration_units IN VARCHAR2
61 ,p_to_duration_units IN VARCHAR2
62 ,p_from_duration IN NUMBER) RETURN NUMBER;
63
64 FUNCTION amtInLoginPrsnCurrency(
65 p_from_currency IN VARCHAR2
66 ,p_amount IN NUMBER
67 ,p_eff_date IN DATE) RETURN NUMBER;
68
69 FUNCTION getEffDate RETURN DATE;
70
71 FUNCTION getAvgClassesPerYear(p_person_id IN NUMBER) RETURN NUMBER;
72
73 FUNCTION getTrngDays(p_person_id IN NUMBER) RETURN NUMBER;
74
75 FUNCTION getTrngDaysYTD(p_person_id IN NUMBER) RETURN NUMBER;
76
77 FUNCTION getTrngHrs(p_person_id IN NUMBER) RETURN NUMBER;
78
79 FUNCTION getTrngCost(p_person_id IN NUMBER) RETURN NUMBER;
80
81 FUNCTION getTrngCostYTD(p_person_id IN NUMBER) RETURN NUMBER;
82
83 FUNCTION get_training_center (p_training_center_id in number) RETURN VARCHAR2;
84
85 FUNCTION getTrngPrctOnPayroll(p_person_id IN NUMBER) RETURN NUMBER;
86
87 FUNCTION getLoginPrsnCurrencyCode RETURN VARCHAR2;
88
89 FUNCTION getClassesTaken(
90 p_person_id IN NUMBER
91 ,p_eff_date IN DATE Default getEffDate) RETURN NUMBER;
92
93 FUNCTION getFutureClasses(
94 p_person_id IN NUMBER
95 ,p_eff_date IN DATE Default getEffDate) RETURN NUMBER;
96
97 FUNCTION getReqClasses(p_person_id IN NUMBER) RETURN NUMBER;
98
99 FUNCTION getReqClassesYTD(p_person_id IN NUMBER) RETURN NUMBER;
100
101 FUNCTION getReqClassesCompleted(p_person_id IN NUMBER) RETURN NUMBER;
102
103 FUNCTION getReqClassesCompletedYTD(p_person_id IN NUMBER) RETURN NUMBER;
104
105 FUNCTION getReqClassesEnrolled(p_person_id IN NUMBER) RETURN NUMBER;
106
107 FUNCTION getYOSDenominator(p_person_id IN NUMBER) RETURN NUMBER;
108
109 FUNCTION getYOS(
110 p_person_id IN NUMBER
111 ,p_eff_date IN DATE Default getEffDate) RETURN NUMBER;
112
113 TYPE segmentsTable IS TABLE OF gtt_segment INDEX BY BINARY_INTEGER;
114
115 FUNCTION getAsgGradeRule(p_pay_proposal_id IN NUMBER) RETURN ROWID;
116
117 PRAGMA RESTRICT_REFERENCES (getAsgGradeRule, WNDS);
118
119 FUNCTION getAsgProposalId(p_assignment_id IN NUMBER) RETURN NUMBER;
120
121 PRAGMA RESTRICT_REFERENCES (getAsgProposalId, WNDS);
122
123 FUNCTION getPrsnApplicationId(p_person_id IN NUMBER) RETURN NUMBER;
124
125 PRAGMA RESTRICT_REFERENCES (getPrsnApplicationId, WNDS);
126
127 FUNCTION getPrsnPerformanceId(p_person_id IN NUMBER) RETURN NUMBER;
128
129 PRAGMA RESTRICT_REFERENCES (getPrsnPerformanceId, WNDS);
130
131 FUNCTION get_total_absences(p_person_id IN NUMBER)
132 RETURN NUMBER;
133
134 PRAGMA RESTRICT_REFERENCES
135 (get_total_absences, WNDS);
136
137 FUNCTION get_total_absence_hours(p_person_id IN NUMBER)
138 RETURN NUMBER;
139
140 PRAGMA RESTRICT_REFERENCES
141 (get_total_absence_hours, WNDS);
142
143 FUNCTION get_total_absence_days(p_person_id IN NUMBER)
144 RETURN NUMBER;
145
146 PRAGMA RESTRICT_REFERENCES
147 (get_total_absence_days, WNDS);
148
149 FUNCTION get_years_of_service(p_person_id IN NUMBER)
150 RETURN NUMBER;
151
152 FUNCTION getAYOS(p_person_id IN NUMBER,p_eff_date IN DATE Default getEffDate)
153 RETURN NUMBER;
154
155 FUNCTION get_last_application_date(p_person_id IN NUMBER)
156 RETURN DATE;
157
158 PRAGMA RESTRICT_REFERENCES
159 (get_last_application_date, WNDS);
160
161 FUNCTION get_past_classes(p_person_id IN NUMBER)
162 RETURN NUMBER;
163
164 FUNCTION get_future_classes(p_person_id IN NUMBER)
165 RETURN NUMBER;
166
167 FUNCTION get_other_classes(p_person_id IN NUMBER)
168 RETURN NUMBER;
169
170 PRAGMA RESTRICT_REFERENCES
171 (get_other_classes, WNDS);
172
173 FUNCTION get_currency(p_assignment_id IN per_assignments_f.assignment_id%TYPE
174 ,p_change_date IN DATE)
175
176 RETURN pay_element_types_f.input_currency_code%TYPE;
177
178 PRAGMA RESTRICT_REFERENCES
179 (get_currency, WNDS);
180
181 FUNCTION get_annual_salary(
182 p_assignment_id IN per_assignments_f.assignment_id%TYPE,
183 p_change_date IN DATE
184 )
185 RETURN VARCHAR2;
186
187 PRAGMA RESTRICT_REFERENCES
188 (get_annual_salary, WNDS);
189
190 FUNCTION get_job(p_job_id IN per_assignments_f.job_id%TYPE)
191 RETURN VARCHAR2;
192
193 PRAGMA RESTRICT_REFERENCES
194 (get_job, WNDS);
195
196 FUNCTION get_grade(p_grade_id IN per_assignments_f.grade_id%TYPE)
197 RETURN VARCHAR2;
198
199 PRAGMA RESTRICT_REFERENCES
200 (get_grade, WNDS);
201
202 FUNCTION get_position(p_position_id IN per_assignments_f.position_id%TYPE
203 ,p_effective_date IN DATE DEFAULT TRUNC(SYSDATE))
204 RETURN VARCHAR2;
205 PRAGMA RESTRICT_REFERENCES
206 (get_position, WNDS);
207
208 PROCEDURE get_segment_value(
209 p_flex_code IN VARCHAR2
210 ,p_flex_num IN VARCHAR2
211 ,p_segment_name1 IN VARCHAR2 DEFAULT NULL
212 ,p_segment_name2 IN VARCHAR2 DEFAULT NULL
213 ,p_segment hr_mee_views_gen.segmentsTable
214 ,p_result OUT nocopy VARCHAR2
215 );
216
217 PRAGMA RESTRICT_REFERENCES
218 (get_segment_value, WNDS);
219
220 FUNCTION get_currency_format(
221 p_curcode pay_element_types_f.input_currency_code%TYPE,
222 p_effective_date DATE
223 )
224 RETURN VARCHAR2;
225
226 PRAGMA RESTRICT_REFERENCES
227 (get_currency_format, WNDS);
228
229 FUNCTION GET_CONTACTS_TYPE_LIST(
230 p_person_id IN per_contact_relationships.person_id%TYPE
231 ,p_contact_id IN per_contact_relationships.contact_person_id%TYPE
232 ,p_effective_date IN DATE DEFAULT TRUNC(SYSDATE))
233 RETURN VARCHAR2;
234
235 FUNCTION is_emergency_contact(
236 p_person_id IN per_contact_relationships.person_id%TYPE
237 ,p_contact_id IN per_contact_relationships.contact_person_id%TYPE
238 ,p_effective_date IN DATE DEFAULT TRUNC(SYSDATE))
239 RETURN NUMBER;
240
241 -- ------------------------------------------------------------------------
242 --|---------------------< get_display_job_name>----------------------------|
243 -- ------------------------------------------------------------------------
244 --
245 -- Description:
246 -- This function calls a procedure get_job_info by passing it the job_id
247 -- and returns the job name which is one of the out parameters of
248 -- the procedure get_job_info.
249 -- The function is to be called in a select query where procedure cannot
250 -- be called. This function is to be called inplace of the function
251 -- get_job, which does not separate the concatenated segment's values by
252 -- the segment seperator fetched from the profile.
253 --
254 -- If the HR Views responsibilty profiles HR_JOB_KEYFLEX_SEGMENT1 and
255 -- HR_JOB_KEYFLEX_SEGMENT2 are set and enabled then these values will
256 -- be returned with an intermediate separator. Otherwise the
257 -- per_jobs.name value will be returned.
258 --
259 -- Pre Conditions:
260 -- In Arguments:
261 -- Name Reqd Type Description
262 -- p_job_id YES NUMBER job ID of a job whose
263 -- name is to be shown.
264 -- Post Success:
265 --
266 -- Returns the job name corresponding job_id.
267 --
268 -- Post Failure:
269 --
270 -- Developer Implementation Notes:
271 --
272 -- Access Status:
273 --
274 -- -----------------------------------------------------------------------------
275 FUNCTION get_display_job_name(
276 p_job_id IN per_assignments_f.job_id%TYPE)
277 RETURN VARCHAR2;
278
279 FUNCTION getTrngScore(
280 p_person_id IN NUMBER,
281 p_event_id IN NUMBER)
282 RETURN NUMBER;
283
284 -- Bug 4513393 Begin
285 FUNCTION getTrngEndDate(
286 p_person_id IN NUMBER,
287 p_event_id IN NUMBER)
288 RETURN DATE;
289 -- Bug 4513393 Ends
290
291 END hr_mee_views_gen;