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