1 PACKAGE BODY PQH_PQHUSEPR_XMLP_PKG AS
2 /* $Header: PQHUSEPRB.pls 120.1 2007/12/07 06:49:33 vjaganat noship $ */
3 FUNCTION BEFOREREPORT RETURN BOOLEAN IS
4 BEGIN
5 -- HR_STANDARD.EVENT('BEFORE REPORT');
6 CP_BUSINESS_GROUP_NAME := GET_BUSINESS_GROUP(P_BUSINESS_GROUP_ID);
7 RETURN (TRUE);
8 END BEFOREREPORT;
9
10 FUNCTION CF_GETTENURECOUNTFORMULA RETURN NUMBER IS
11 L_TENURED_SUP_CNT NUMBER;
12 L_TEN_CUR_YR_SUP_CNT NUMBER;
13 L_TT_SUP_CNT NUMBER;
14 L_TT_FINAL_YR_SUP_CNT NUMBER;
15 L_TE_CUR_YR_SUP_CNT NUMBER;
16 L_TD_CUR_YR_SUP_CNT NUMBER;
17 L_START_OF_TIME DATE := HR_GENERAL.START_OF_TIME;
18 BEGIN
19 CP_TENURED_CNT := PQH_TENURE_DETAIL.GETPERIODSTATUSCOUNT(P_SUPERVISOR_ID
20 ,'01'
21 ,L_START_OF_TIME
22 ,SYSDATE);
23 CP_TENTRACK_CNT := PQH_TENURE_DETAIL.GETPERIODSTATUSCOUNT(P_SUPERVISOR_ID
24 ,'02'
25 ,L_START_OF_TIME
26 ,SYSDATE);
27 CP_TENELIGIBLE_CNT := PQH_TENURE_DETAIL.GETPERIODSTATUSCOUNT(P_SUPERVISOR_ID
28 ,'04'
29 ,L_START_OF_TIME
30 ,SYSDATE);
31 CP_TENDENIED_CNT := PQH_TENURE_DETAIL.GETPERIODSTATUSCOUNT(P_SUPERVISOR_ID
32 ,'05'
33 ,L_START_OF_TIME
34 ,SYSDATE);
35 RETURN 0;
36 END CF_GETTENURECOUNTFORMULA;
37
38 FUNCTION AFTERPFORM RETURN BOOLEAN IS
39 BEGIN
40 IF P_DIRECT_OR_ALL = 'A' THEN
41 P_AND_CLAUSE := 'where 0=0';
42 END IF;
43 RETURN (TRUE);
44 END AFTERPFORM;
45
46 FUNCTION CF_JOB_NAMEFORMULA(JOB_ID IN NUMBER) RETURN CHAR IS
47 CURSOR JOB_NM_CUR IS
48 SELECT
49 NAME
50 FROM
51 PER_JOBS_VL
52 WHERE JOB_ID = JOB_ID
53 AND BUSINESS_GROUP_ID = P_BUSINESS_GROUP_ID
54 AND sysdate between NVL(DATE_FROM
55 ,SYSDATE)
56 AND NVL(DATE_TO
57 ,SYSDATE);
58 L_JOB_NAME VARCHAR2(240);
59 BEGIN
60 IF JOB_ID IS NOT NULL THEN
61 OPEN JOB_NM_CUR;
62 FETCH JOB_NM_CUR
63 INTO
64 L_JOB_NAME;
65 CLOSE JOB_NM_CUR;
66 END IF;
67 RETURN (L_JOB_NAME);
68 END CF_JOB_NAMEFORMULA;
69
70 FUNCTION CF_ACADEMIC_RANKFORMULA(RANK_ID IN VARCHAR2) RETURN CHAR IS
71 CURSOR ACADEMIC_RANK_CUR IS
72 SELECT
73 MEANING
74 FROM
75 HR_LOOKUPS
76 WHERE LOOKUP_TYPE = 'PQH_ACADEMIC_RANK'
77 AND LOOKUP_CODE = RANK_ID;
78 L_ACADEMIC_RANK VARCHAR2(40);
79 BEGIN
80 IF RANK_ID IS NOT NULL THEN
81 OPEN ACADEMIC_RANK_CUR;
82 FETCH ACADEMIC_RANK_CUR
83 INTO
84 L_ACADEMIC_RANK;
85 CLOSE ACADEMIC_RANK_CUR;
86 END IF;
87 RETURN (L_ACADEMIC_RANK);
88 END CF_ACADEMIC_RANKFORMULA;
89
90 FUNCTION CF_TENURE_STATUSFORMULA(TENURE_STATUS_CD IN VARCHAR2
91 ,RANK_ID IN VARCHAR2) RETURN CHAR IS
92 CURSOR TENURE_STATUS_CUR IS
93 SELECT
94 MEANING
95 FROM
96 HR_LOOKUPS
97 WHERE LOOKUP_TYPE = 'PQH_TENURE_STATUS'
98 AND LOOKUP_CODE = TENURE_STATUS_CD;
99 L_TENURE_STATUS VARCHAR2(40);
100 BEGIN
101 IF RANK_ID IS NOT NULL THEN
102 OPEN TENURE_STATUS_CUR;
103 FETCH TENURE_STATUS_CUR
104 INTO
105 L_TENURE_STATUS;
106 CLOSE TENURE_STATUS_CUR;
107 END IF;
108 RETURN (L_TENURE_STATUS);
109 END CF_TENURE_STATUSFORMULA;
110
111 FUNCTION CF_MANAGER_NAMEFORMULA(MANAGER_ID IN NUMBER) RETURN VARCHAR2 IS
112 BEGIN
113 RETURN (GETMANAGERNAME(MANAGER_ID));
114 END CF_MANAGER_NAMEFORMULA;
115
116 FUNCTION CF_DIRECT_OR_HIERARCHIALFORMUL RETURN CHAR IS
117 BEGIN
118 IF P_DIRECT_OR_ALL = 'D' THEN
119 RETURN ('Direct Report');
120 ELSE
121 RETURN ('Hierarchical Report');
122 END IF;
123 END CF_DIRECT_OR_HIERARCHIALFORMUL;
124
125 FUNCTION GETMANAGERNAME(P_MANAGER_ID IN NUMBER) RETURN VARCHAR2 IS
126 CURSOR MGR_NM_CUR IS
127 SELECT
128 LAST_NAME || ', ' || FIRST_NAME
129 FROM
130 PER_ALL_PEOPLE_F
131 WHERE PERSON_ID = P_MANAGER_ID
132 AND sysdate between EFFECTIVE_START_DATE
133 AND EFFECTIVE_END_DATE;
134 L_MANAGER_NM VARCHAR2(240);
135 BEGIN
136 OPEN MGR_NM_CUR;
137 FETCH MGR_NM_CUR
138 INTO
139 L_MANAGER_NM;
140 CLOSE MGR_NM_CUR;
141 RETURN (L_MANAGER_NM);
142 END GETMANAGERNAME;
143
144 FUNCTION CF_MGR_NAMEFORMULA RETURN CHAR IS
145 BEGIN
146 RETURN (GETMANAGERNAME(P_SUPERVISOR_ID));
147 END CF_MGR_NAMEFORMULA;
148
149 FUNCTION AFTERREPORT RETURN BOOLEAN IS
150 BEGIN
151 -- HR_STANDARD.EVENT('AFTER REPORT');
152 RETURN (TRUE);
153 END AFTERREPORT;
154
155 FUNCTION CP_BUSINESS_GROUP_NAME_P RETURN VARCHAR2 IS
156 BEGIN
157 RETURN CP_BUSINESS_GROUP_NAME;
158 END CP_BUSINESS_GROUP_NAME_P;
159
160 FUNCTION CP_TENURED_CNT_P RETURN NUMBER IS
161 BEGIN
162 RETURN CP_TENURED_CNT;
163 END CP_TENURED_CNT_P;
164
165 FUNCTION CP_TENTRACK_CNT_P RETURN NUMBER IS
166 BEGIN
167 RETURN CP_TENTRACK_CNT;
168 END CP_TENTRACK_CNT_P;
169
170 FUNCTION CP_TENELIGIBLE_CNT_P RETURN NUMBER IS
171 BEGIN
172 RETURN CP_TENELIGIBLE_CNT;
173 END CP_TENELIGIBLE_CNT_P;
174
175 FUNCTION CP_TENDENIED_CNT_P RETURN NUMBER IS
176 BEGIN
177 RETURN CP_TENDENIED_CNT;
178 END CP_TENDENIED_CNT_P;
179
180
181
182 FUNCTION GET_BUSINESS_GROUP(P_BUSINESS_GROUP_ID IN NUMBER) RETURN VARCHAR2 IS
183 X0 VARCHAR2(2000);
184 BEGIN
185 X0 := HR_REPORTS.GET_BUSINESS_GROUP(P_BUSINESS_GROUP_ID);
186 RETURN X0;
187 END GET_BUSINESS_GROUP;
188 END PQH_PQHUSEPR_XMLP_PKG;
[Home] [Help]