1 PACKAGE PQH_PQHEEO5_XMLP_PKG AUTHID CURRENT_USER AS
2 /* $Header: PQHEEO5S.pls 120.3 2007/12/21 17:29:27 vjaganat noship $ */
3 P_BUSINESS_GROUP_ID NUMBER;
4
5 P_REPORT_YEAR VARCHAR2(4);
6
7 P_CONC_REQUEST_ID NUMBER;
8
9 P_FUNCTION_DESC VARCHAR2(260);
10
11 P_REPORT_DAY_MONTH VARCHAR2(32767);
12
13 P_TOTAL_ENROLLMENTS NUMBER;
14
15 P_TYPE_AGENCY VARCHAR2(200);
16
17 CP_REPORT_DATE DATE;
18
19 LP_REPORT_DATE VARCHAR2(20);
20
21 CP_NO_OF_SCHOOLS NUMBER;
22
23 CP_FR VARCHAR2(2000);
24
25 CP_FT VARCHAR2(2000);
26
27 CP_PR VARCHAR2(2000);
28
29 CP_PT VARCHAR2(2000);
30
31 FUNCTION BEFOREREPORT RETURN BOOLEAN;
32
33 FUNCTION CF_TOTAL_TITLEFORMULA0005(EMPLOYMENT_CATEGORY IN VARCHAR2) RETURN CHAR;
34
35 FUNCTION CF_NO_OF_ANNEXESFORMULA RETURN NUMBER;
36
37 FUNCTION BEFOREPFORM RETURN BOOLEAN;
38
39 FUNCTION AFTERPFORM RETURN BOOLEAN;
40
41 FUNCTION AFTERREPORT RETURN BOOLEAN;
42
43 FUNCTION CP_REPORT_DATE_P RETURN DATE;
44
45 FUNCTION CP_NO_OF_SCHOOLS_P RETURN NUMBER;
46
47 FUNCTION CP_FR_P RETURN VARCHAR2;
48
49 FUNCTION CP_FT_P RETURN VARCHAR2;
50
51 FUNCTION CP_PR_P RETURN VARCHAR2;
52
53 FUNCTION CP_PT_P RETURN VARCHAR2;
54 /*
55 PROCEDURE ASSERT_CONDITION(P_CONDITION IN BOOLEAN);
56
57 FUNCTION GET_BUSINESS_GROUP_ID RETURN NUMBER;
58
59 FUNCTION CHK_APPLICATION_ID(P_APPLICATION_ID IN NUMBER) RETURN VARCHAR2;
60
61 FUNCTION CORE_HR_APP_ID(P_APPLICATION_ID IN NUMBER) RETURN VARCHAR2;
62
63 FUNCTION VERTICAL_APP_ID(P_APPLICATION_ID IN NUMBER) RETURN VARCHAR2;
64
65 FUNCTION HRMS_OBJECT(P_OBJECT_NAME IN VARCHAR2) RETURN VARCHAR2;
66
67 FUNCTION GET_APPLICATION_SHORT_NAME(P_APPLICATION_ID IN VARCHAR2) RETURN VARCHAR2;
68
69 FUNCTION EFFECTIVE_DATE RETURN DATE;
70
71 FUNCTION START_OF_TIME RETURN DATE;
72
73 FUNCTION END_OF_TIME RETURN DATE;
74
75 FUNCTION PAY_VALUE RETURN VARCHAR2;
76
77 FUNCTION MONEY_UNIT RETURN VARCHAR2;
78
79 FUNCTION DEFAULT_CURRENCY_CODE(P_LEGISLATION_CODE IN VARCHAR2) RETURN VARCHAR2;
80
81 FUNCTION DEFAULT_CURRENCY_CODE(P_BUSINESS_GROUP_ID IN NUMBER) RETURN VARCHAR2;
82
83 FUNCTION LOCATION_VALID(P_LOCATION_ID IN NUMBER
84 ,P_DATE IN DATE
85 ,P_ERROR_IF_INVALID IN BOOLEAN) RETURN BOOLEAN;
86
87 FUNCTION DECODE_LOOKUP(P_LOOKUP_TYPE IN VARCHAR2
88 ,P_LOOKUP_CODE IN VARCHAR2) RETURN VARCHAR2;
89
90 FUNCTION DECODE_GRADE(P_GRADE_ID IN NUMBER) RETURN VARCHAR2;
91
92 FUNCTION DECODE_PAYROLL(P_PAYROLL_ID IN NUMBER) RETURN VARCHAR2;
93
94 FUNCTION GET_SALARY(P_PAY_BASIS_ID IN NUMBER
95 ,P_ASSIGNMENT_ID IN NUMBER) RETURN VARCHAR2;
96
97 FUNCTION DECODE_JOB(P_JOB_ID IN NUMBER) RETURN VARCHAR2;
98
99 FUNCTION DECODE_POSITION(P_POSITION_ID IN NUMBER) RETURN VARCHAR2;
100
101 FUNCTION DECODE_LOCATION(P_LOCATION_ID IN NUMBER) RETURN VARCHAR2;
102
103 FUNCTION DECODE_PAY_BASIS(P_PAY_BASIS_ID IN NUMBER) RETURN VARCHAR2;
104
105 FUNCTION DECODE_ASS_STATUS_TYPE(P_ASSIGNMENT_STATUS_TYPE_ID IN NUMBER
106 ,P_BUSINESS_GROUP_ID IN NUMBER) RETURN VARCHAR2;
107
108 FUNCTION GET_WORK_PHONE(P_PERSON_ID IN NUMBER) RETURN VARCHAR2;
109
110 FUNCTION GET_HOME_PHONE(P_PERSON_ID IN NUMBER) RETURN VARCHAR2;
111
112 PROCEDURE INIT_FORMS(P_BUSINESS_GROUP_ID IN NUMBER
113 ,P_SHORT_NAME OUT NOCOPY VARCHAR2
114 ,P_BG_NAME OUT NOCOPY VARCHAR2
115 ,P_BG_CURRENCY_CODE OUT NOCOPY VARCHAR2
116 ,P_LEGISLATION_CODE OUT NOCOPY VARCHAR2
117 ,P_SESSION_DATE IN OUT NOCOPY DATE
118 ,P_SES_YESTERDAY OUT NOCOPY DATE
119 ,P_START_OF_TIME OUT NOCOPY DATE
120 ,P_END_OF_TIME OUT NOCOPY DATE
121 ,P_SYS_DATE OUT NOCOPY DATE
122 ,P_ENABLE_HR_TRACE IN BOOLEAN);
123
124 FUNCTION CHK_GEOCODES_INSTALLED RETURN VARCHAR2;
125
126 FUNCTION GET_BUDGET(P_BUDGET_ID IN NUMBER) RETURN VARCHAR2;
127
128 FUNCTION GET_BUDGET_VERSION(P_BUDGET_ID IN NUMBER
129 ,P_BUDGET_VERSION_ID IN NUMBER) RETURN VARCHAR2;
130
131 PROCEDURE GET_ORGANIZATION(P_ORGANIZATION_ID IN NUMBER
132 ,P_ORG_NAME OUT NOCOPY VARCHAR2
133 ,P_ORG_TYPE OUT NOCOPY VARCHAR2);
134
135 FUNCTION GET_JOB(P_JOB_ID IN NUMBER) RETURN VARCHAR2;
136
137 FUNCTION GET_POSITION(P_POSITION_ID IN NUMBER) RETURN VARCHAR2;
138
139 FUNCTION GET_GRADE(P_GRADE_ID IN NUMBER) RETURN VARCHAR2;
140
141 FUNCTION GET_STATUS(P_BUSINESS_GROUP_ID IN NUMBER
142 ,P_ASSIGNMENT_STATUS_TYPE_ID IN NUMBER
143 ,P_LEGISLATION_CODE IN VARCHAR2) RETURN VARCHAR2;
144
145 FUNCTION GET_ABS_TYPE(P_ABS_ATT_TYPE_ID IN NUMBER) RETURN VARCHAR2;
146
147 PROCEDURE GET_TIME_PERIOD(P_TIME_PERIOD_ID IN NUMBER
148 ,P_PERIOD_NAME OUT NOCOPY VARCHAR2
149 ,P_START_DATE OUT NOCOPY DATE
150 ,P_END_DATE OUT NOCOPY DATE);
151
152 FUNCTION GET_BUSINESS_GROUP(P_BUSINESS_GROUP_ID IN NUMBER) RETURN VARCHAR2;
153
154 FUNCTION COUNT_ORG_SUBORDINATES(P_ORG_STRUCTURE_VERSION_ID IN NUMBER
155 ,P_PARENT_ORGANIZATION_ID IN NUMBER) RETURN NUMBER;
156
157 FUNCTION COUNT_POS_SUBORDINATES(P_POS_STRUCTURE_VERSION_ID IN NUMBER
158 ,P_PARENT_POSITION_ID IN NUMBER) RETURN NUMBER;
159
160 PROCEDURE GET_ORGANIZATION_HIERARCHY(P_ORGANIZATION_STRUCTURE_ID IN NUMBER
161 ,P_ORG_STRUCTURE_VERSION_ID IN NUMBER
162 ,P_ORG_STRUCTURE_NAME OUT NOCOPY VARCHAR2
163 ,P_ORG_VERSION OUT NOCOPY NUMBER
164 ,P_VERSION_START_DATE OUT NOCOPY DATE
165 ,P_VERSION_END_DATE OUT NOCOPY DATE);
166
167 PROCEDURE GET_POSITION_HIERARCHY(P_POSITION_STRUCTURE_ID IN NUMBER
168 ,P_POS_STRUCTURE_VERSION_ID IN NUMBER
169 ,P_POS_STRUCTURE_NAME OUT NOCOPY VARCHAR2
170 ,P_POS_VERSION OUT NOCOPY NUMBER
171 ,P_VERSION_START_DATE OUT NOCOPY DATE
172 ,P_VERSION_END_DATE OUT NOCOPY DATE);
173
174 FUNCTION GET_LOOKUP_MEANING(P_LOOKUP_TYPE IN VARCHAR2
175 ,P_LOOKUP_CODE IN VARCHAR2) RETURN VARCHAR2;
176
177 FUNCTION PERSON_MATCHING_SKILLS(P_PERSON_ID IN NUMBER
178 ,P_JOB_POSITION_ID IN NUMBER
179 ,P_JOB_POSITION_TYPE IN VARCHAR2
180 ,P_MATCHING_LEVEL IN VARCHAR2
181 ,P_NO_OF_ESSENTIAL IN NUMBER
182 ,P_NO_OF_DESIRABLE IN NUMBER) RETURN BOOLEAN;
183
184 FUNCTION GET_PAYROLL_NAME(P_SESSION_DATE IN DATE
185 ,P_PAYROLL_ID IN NUMBER) RETURN VARCHAR2;
186
187 FUNCTION GET_ELEMENT_NAME(P_SESSION_DATE IN DATE
188 ,P_ELEMENT_TYPE_ID IN NUMBER) RETURN VARCHAR2;
189
190 PROCEDURE GEN_PARTIAL_MATCHING_LEXICAL(P_CONCATENATED_SEGMENTS IN VARCHAR2
191 ,P_ID_FLEX_NUM IN NUMBER
192 ,P_MATCHING_LEXICAL IN OUT NOCOPY VARCHAR2);
193
194 PROCEDURE GET_ATTRIBUTES(P_CONCATENATED_SEGMENTS IN VARCHAR2
195 ,P_NAME IN VARCHAR2
196 ,P_SEGMENTS_USED OUT NOCOPY NUMBER
197 ,P_VALUE1 OUT NOCOPY VARCHAR2
198 ,P_VALUE2 OUT NOCOPY VARCHAR2
199 ,P_VALUE3 OUT NOCOPY VARCHAR2
200 ,P_VALUE4 OUT NOCOPY VARCHAR2
201 ,P_VALUE5 OUT NOCOPY VARCHAR2
202 ,P_VALUE6 OUT NOCOPY VARCHAR2
203 ,P_VALUE7 OUT NOCOPY VARCHAR2
204 ,P_VALUE8 OUT NOCOPY VARCHAR2
205 ,P_VALUE9 OUT NOCOPY VARCHAR2
206 ,P_VALUE10 OUT NOCOPY VARCHAR2
207 ,P_VALUE11 OUT NOCOPY VARCHAR2
208 ,P_VALUE12 OUT NOCOPY VARCHAR2
209 ,P_VALUE13 OUT NOCOPY VARCHAR2
210 ,P_VALUE14 OUT NOCOPY VARCHAR2
211 ,P_VALUE15 OUT NOCOPY VARCHAR2
212 ,P_VALUE16 OUT NOCOPY VARCHAR2
213 ,P_VALUE17 OUT NOCOPY VARCHAR2
214 ,P_VALUE18 OUT NOCOPY VARCHAR2
215 ,P_VALUE19 OUT NOCOPY VARCHAR2
216 ,P_VALUE20 OUT NOCOPY VARCHAR2
217 ,P_VALUE21 OUT NOCOPY VARCHAR2
218 ,P_VALUE22 OUT NOCOPY VARCHAR2
219 ,P_VALUE23 OUT NOCOPY VARCHAR2
220 ,P_VALUE24 OUT NOCOPY VARCHAR2
221 ,P_VALUE25 OUT NOCOPY VARCHAR2
222 ,P_VALUE26 OUT NOCOPY VARCHAR2
223 ,P_VALUE27 OUT NOCOPY VARCHAR2
224 ,P_VALUE28 OUT NOCOPY VARCHAR2
225 ,P_VALUE29 OUT NOCOPY VARCHAR2
226 ,P_VALUE30 OUT NOCOPY VARCHAR2);
227
228 PROCEDURE GET_SEGMENTS(P_CONCATENATED_SEGMENTS IN VARCHAR2
229 ,P_ID_FLEX_NUM IN NUMBER
230 ,P_SEGMENTS_USED OUT NOCOPY NUMBER
231 ,P_VALUE1 OUT NOCOPY VARCHAR2
232 ,P_VALUE2 OUT NOCOPY VARCHAR2
233 ,P_VALUE3 OUT NOCOPY VARCHAR2
234 ,P_VALUE4 OUT NOCOPY VARCHAR2
235 ,P_VALUE5 OUT NOCOPY VARCHAR2
236 ,P_VALUE6 OUT NOCOPY VARCHAR2
237 ,P_VALUE7 OUT NOCOPY VARCHAR2
238 ,P_VALUE8 OUT NOCOPY VARCHAR2
239 ,P_VALUE9 OUT NOCOPY VARCHAR2
240 ,P_VALUE10 OUT NOCOPY VARCHAR2
241 ,P_VALUE11 OUT NOCOPY VARCHAR2
242 ,P_VALUE12 OUT NOCOPY VARCHAR2
243 ,P_VALUE13 OUT NOCOPY VARCHAR2
244 ,P_VALUE14 OUT NOCOPY VARCHAR2
245 ,P_VALUE15 OUT NOCOPY VARCHAR2
246 ,P_VALUE16 OUT NOCOPY VARCHAR2
247 ,P_VALUE17 OUT NOCOPY VARCHAR2
248 ,P_VALUE18 OUT NOCOPY VARCHAR2
249 ,P_VALUE19 OUT NOCOPY VARCHAR2
250 ,P_VALUE20 OUT NOCOPY VARCHAR2
251 ,P_VALUE21 OUT NOCOPY VARCHAR2
252 ,P_VALUE22 OUT NOCOPY VARCHAR2
253 ,P_VALUE23 OUT NOCOPY VARCHAR2
254 ,P_VALUE24 OUT NOCOPY VARCHAR2
255 ,P_VALUE25 OUT NOCOPY VARCHAR2
256 ,P_VALUE26 OUT NOCOPY VARCHAR2
257 ,P_VALUE27 OUT NOCOPY VARCHAR2
258 ,P_VALUE28 OUT NOCOPY VARCHAR2
259 ,P_VALUE29 OUT NOCOPY VARCHAR2
260 ,P_VALUE30 OUT NOCOPY VARCHAR2);
261
262 PROCEDURE GET_DESC_FLEX(P_APPL_SHORT_NAME IN VARCHAR2
263 ,P_DESC_FLEX_NAME IN VARCHAR2
264 ,P_TABLE_ALIAS IN VARCHAR2
265 ,P_TITLE OUT NOCOPY VARCHAR2
266 ,P_LABEL_EXPR OUT NOCOPY VARCHAR2
267 ,P_COLUMN_EXPR OUT NOCOPY VARCHAR2);
268
269 PROCEDURE GET_DESC_FLEX_CONTEXT(P_APPL_SHORT_NAME IN VARCHAR2
270 ,P_DESC_FLEX_NAME IN VARCHAR2
271 ,P_TABLE_ALIAS IN VARCHAR2
272 ,P_TITLE OUT NOCOPY VARCHAR2
273 ,P_LABEL_EXPR OUT NOCOPY VARCHAR2
274 ,P_COLUMN_EXPR OUT NOCOPY VARCHAR2);
275
276 PROCEDURE GET_DVLPR_DESC_FLEX(P_APPL_SHORT_NAME IN VARCHAR2
277 ,P_DESC_FLEX_NAME IN VARCHAR2
278 ,P_DESC_FLEX_CONTEXT IN VARCHAR2
279 ,P_TABLE_ALIAS IN VARCHAR2
280 ,P_TITLE OUT NOCOPY VARCHAR2
281 ,P_LABEL_EXPR OUT NOCOPY VARCHAR2
282 ,P_COLUMN_EXPR OUT NOCOPY VARCHAR2);
283
284 FUNCTION GET_PERSON_NAME(P_SESSION_DATE IN DATE
285 ,P_PERSON_ID IN NUMBER) RETURN VARCHAR2;
286
287 FUNCTION GET_SALARY(P_BUSINESS_GROUP_ID IN NUMBER
288 ,P_ASSIGNMENT_ID IN NUMBER
289 ,P_REPORT_DATE IN DATE) RETURN NUMBER;
290
291 PROCEDURE GET_NEW_HIRE_CONTACT(P_PERSON_ID IN NUMBER
292 ,P_BUSINESS_GROUP_ID IN NUMBER
293 ,P_REPORT_DATE IN DATE
294 ,P_CONTACT_NAME OUT NOCOPY VARCHAR2
295 ,P_CONTACT_TITLE OUT NOCOPY VARCHAR2
296 ,P_CONTACT_PHONE OUT NOCOPY VARCHAR2);
297
298 PROCEDURE GET_ADDRESS(P_LOCATION_ID IN NUMBER
299 ,P_ADDRESS OUT NOCOPY VARCHAR2);
300
301 PROCEDURE GET_EMPLOYEE_ADDRESS(P_PERSON_ID IN NUMBER
302 ,P_ADDRESS OUT NOCOPY VARCHAR2);
303
304 PROCEDURE GET_COUNTY_ADDRESS(P_LOCATION_ID IN NUMBER
305 ,P_ADDRESS OUT NOCOPY VARCHAR2);
306
307 PROCEDURE GET_ACTIVITY(P_ESTABLISHMENT_ID IN NUMBER
308 ,P_ACTIVITY OUT NOCOPY VARCHAR2);
309
310 FUNCTION GET_CONSOLIDATION_SET(P_CONSOLIDATION_SET_ID IN NUMBER) RETURN VARCHAR2;
311
312 FUNCTION GET_PAYMENT_TYPE_NAME(P_PAYMENT_TYPE_ID IN NUMBER) RETURN VARCHAR2;
313
314 FUNCTION GET_ELEMENT_TYPE_NAME(P_ELEMENT_TYPE_ID IN NUMBER) RETURN VARCHAR2;
315
316 FUNCTION GET_TAX_UNIT(P_TAX_UNIT_ID IN NUMBER) RETURN VARCHAR2;
317
318 FUNCTION GET_PERSON_NAME(P_PERSON_ID IN NUMBER) RETURN VARCHAR2;
319
320 FUNCTION GET_PAYROLL_ACTION(P_PAYROLL_ACTION_ID IN NUMBER) RETURN VARCHAR2;
321
322 FUNCTION GET_LEGISLATION_CODE(P_BUSINESS_GROUP_ID IN NUMBER) RETURN VARCHAR2;
323
324 FUNCTION GET_DEFINED_BALANCE_ID(P_BALANCE_NAME IN VARCHAR2
325 ,P_DIMENSION_SUFFIX IN VARCHAR2
326 ,P_BUSINESS_GROUP_ID IN NUMBER) RETURN NUMBER;
327
328 FUNCTION GET_STARTUP_DEFINED_BALANCE(P_REPORTING_NAME IN VARCHAR2
329 ,P_DIMENSION_SUFFIX IN VARCHAR2) RETURN NUMBER;
330
331 FUNCTION GET_DEFINED_BALANCE_BY_TYPE(P_BOX_NUM IN VARCHAR2
332 ,P_DIMENSION_SUFFIX IN VARCHAR2) RETURN NUMBER;
333
334 FUNCTION GET_BEN_CLASS_NAME(P_SESSION_DATE IN DATE
335 ,P_BENEFIT_CLASSIFICATION_ID IN NUMBER) RETURN VARCHAR2;
336
337 FUNCTION GET_COBRA_QUALIFYING_EVENT(P_QUALIFYING_EVENT IN VARCHAR2) RETURN VARCHAR2;
338
339 FUNCTION GET_COBRA_STATUS(P_COBRA_STATUS IN VARCHAR2) RETURN VARCHAR2;
340
341 FUNCTION GET_EST_TAX_UNIT(P_STARTING_ORG_ID IN NUMBER
342 ,P_ORG_STRUCTURE_VERSION_ID IN NUMBER) RETURN NUMBER;
343
344 FUNCTION GET_ORG_HIERARCHY_NAME(P_ORG_STRUCTURE_VERSION_ID IN NUMBER) RETURN VARCHAR2;
345
346 FUNCTION GET_STATE_NAME(P_STATE_CODE IN VARCHAR2) RETURN VARCHAR2;
347
348 FUNCTION GET_ORG_NAME(P_ORGANIZATION_ID IN NUMBER
349 ,P_BUSINESS_GROUP_ID IN NUMBER) RETURN VARCHAR2;
350
354 FUNCTION GET_AAP_ORG_ID(P_AAP_NAME IN VARCHAR2
351 FUNCTION GET_CAREER_PATH_NAME(P_CAREER_PATH_ID IN NUMBER
352 ,P_BUSINESS_GROUP_ID IN NUMBER) RETURN VARCHAR2;
353
355 ,P_BUSINESS_GROUP_ID IN NUMBER) RETURN NUMBER;
356
357 PROCEDURE GET_ADDRESS_31(P_LOCATION_ID IN NUMBER
358 ,P_ADDRESS OUT NOCOPY VARCHAR2);
359
360 FUNCTION GET_LOCATION_CODE(P_LOCATION_ID IN NUMBER) RETURN VARCHAR2;
361
362 PROCEDURE GET_ADDRESS_3LINES(P_PERSON_ID IN NUMBER
363 ,P_EFFECTIVE_DATE IN DATE
364 ,P_ADDR_LINE1 OUT NOCOPY VARCHAR2
365 ,P_ADDR_LINE2 OUT NOCOPY VARCHAR2
366 ,P_CITY_STATE_ZIP OUT NOCOPY VARCHAR2);
367 */
368 FUNCTION SALARY_RANGE(P_EMP_CATEGORY IN VARCHAR2
369 ,P_SALARY IN NUMBER) RETURN NUMBER;
370
371 FUNCTION EMPLOYMENT_CATEGORY(P_EMP_CATEGORY IN VARCHAR2
372 ,P_HIRE_DATE IN DATE) RETURN VARCHAR2;
373
374 FUNCTION_DESC VARCHAR2(240);
375
376 END PQH_PQHEEO5_XMLP_PKG;