1 PACKAGE BODY PER_PERRPCTM_XMLP_PKG AS
2 /* $Header: PERRPCTMB.pls 120.2 2007/12/17 07:23:28 srikrish noship $ */
3 FUNCTION BEFOREREPORT RETURN BOOLEAN IS
4 BEGIN
5 DECLARE
6 CURSOR CSR_ORG_STRUCTURE_VERSION(P_ORG_STRUCTURE_VERSION_ID IN NUMBER) IS
7 SELECT
8 BUSINESS_GROUP_ID
9 FROM
10 PER_ORG_STRUCTURE_VERSIONS
11 WHERE ORG_STRUCTURE_VERSION_ID = P_ORG_STRUCTURE_VERSION_ID;
12 CURSOR CSR_ORGANIZATION(P_ORGANIZATION_ID IN NUMBER) IS
13 SELECT
14 BUSINESS_GROUP_ID
15 FROM
16 HR_ALL_ORGANIZATION_UNITS
17 WHERE ORGANIZATION_ID = P_ORGANIZATION_ID;
18 V_ORGANIZATION_NAME VARCHAR2(240);
19 V_ORGANIZATION_TYPE VARCHAR2(80);
20 V_ORG_STRUCTURE_NAME VARCHAR2(30);
21 V_ORG_VERSION NUMBER;
22 V_VERSION_START_DATE DATE;
23 V_VERSION_END_DATE DATE;
24 V_LEAV_REASON1 VARCHAR2(80);
25 V_LEAV_REASON2 VARCHAR2(80);
26 V_LEAV_REASON3 VARCHAR2(80);
27 V_LEAV_REASON4 VARCHAR2(80);
28 V_LEAV_REASON5 VARCHAR2(80);
29 V_LEAV_REASON6 VARCHAR2(80);
30 BEGIN
31 -- HR_STANDARD.EVENT('BEFORE REPORT');
32 IF P_ALTERNATE = 'Y' THEN
33 P_LOOKUP_TYPE := 'FR_ENDING_REASON';
34 P_REASON_COLUMN := 'P.PDS_INFORMATION2';
35 ELSE
36 P_LOOKUP_TYPE := 'HR_CWK_TERMINATION_REASONS';
37 P_REASON_COLUMN := 'P.TERMINATION_REASON';
38 END IF;
39 IF (P_ORG_STRUCTURE_VERSION_ID IS NOT NULL) THEN
40 OPEN CSR_ORG_STRUCTURE_VERSION(P_ORG_STRUCTURE_VERSION_ID);
41 FETCH CSR_ORG_STRUCTURE_VERSION
42 INTO
43 P_BUSINESS_GROUP_ID;
44 CLOSE CSR_ORG_STRUCTURE_VERSION;
45 ELSE
46 OPEN CSR_ORGANIZATION(P_PARENT_ORGANIZATION_ID);
47 FETCH CSR_ORGANIZATION
48 INTO
49 P_BUSINESS_GROUP_ID;
50 CLOSE CSR_ORGANIZATION;
51 END IF;
52 IF (P_BUSINESS_GROUP_ID IS NOT NULL) THEN
53 C_BUSINESS_GROUP_NAME := HR_REPORTS.GET_BUSINESS_GROUP(P_BUSINESS_GROUP_ID);
54 ELSE
55 C_BUSINESS_GROUP_NAME := C_GLOBAL_HIERARCHY;
56 END IF;
57 HR_REPORTS.GET_ORGANIZATION(P_PARENT_ORGANIZATION_ID
58 ,V_ORGANIZATION_NAME
59 ,V_ORGANIZATION_TYPE);
60 C_PARENT_ORGANIZATION_NAME := V_ORGANIZATION_NAME;
61 IF P_ORG_STRUCTURE_VERSION_ID IS NOT NULL THEN
62 HR_REPORTS.GET_ORGANIZATION_HIERARCHY(NULL
63 ,P_ORG_STRUCTURE_VERSION_ID
64 ,V_ORG_STRUCTURE_NAME
65 ,V_ORG_VERSION
66 ,V_VERSION_START_DATE
67 ,V_VERSION_END_DATE);
68 C_ORG_STRUCTURE_NAME := V_ORG_STRUCTURE_NAME;
69 ELSE
70 C_ORG_STRUCTURE_NAME := '';
71 END IF;
72 IF P_PARENT_ORGANIZATION_ID IS NOT NULL THEN
73 P_ORG_MATCHING := ' where p.organization_id = :p_parent_organization_id ';
74 ELSIF P_ORG_STRUCTURE_VERSION_ID IS NOT NULL THEN
75 P_ORG_MATCHING := ' where p.organization_id in ( select organization_id_child from per_org_structure_elements where org_structure_version_id = TO_CHAR(P_ORG_STRUCTURE_VERSION_ID) union '||
76 'select distinct organization_id_parent from per_org_structure_elements where org_structure_version_id = ' || TO_CHAR(P_ORG_STRUCTURE_VERSION_ID) || ')';
77 END IF;
78 IF P_LEAV_REASON1 IS NOT NULL THEN
79 SELECT
80 MEANING
81 INTO
82 V_LEAV_REASON1
83 FROM
84 HR_LOOKUPS
85 WHERE LOOKUP_TYPE = P_LOOKUP_TYPE
86 AND LOOKUP_CODE = P_LEAV_REASON1;
87 C_LEAV_REASON1 := V_LEAV_REASON1;
88 C_HEAD_REASONS := V_LEAV_REASON1;
89 END IF;
90 IF P_LEAV_REASON2 IS NOT NULL THEN
91 SELECT
92 MEANING
93 INTO
94 V_LEAV_REASON2
95 FROM
96 HR_LOOKUPS
97 WHERE LOOKUP_TYPE = P_LOOKUP_TYPE
98 AND LOOKUP_CODE = P_LEAV_REASON2;
99 C_LEAV_REASON2 := V_LEAV_REASON2;
100 C_HEAD_REASONS := C_HEAD_REASONS || ',' || V_LEAV_REASON2;
101 END IF;
102 IF P_LEAV_REASON3 IS NOT NULL THEN
103 SELECT
104 MEANING
105 INTO
106 V_LEAV_REASON3
107 FROM
108 HR_LOOKUPS
109 WHERE LOOKUP_TYPE = P_LOOKUP_TYPE
110 AND LOOKUP_CODE = P_LEAV_REASON3;
111 C_LEAV_REASON3 := V_LEAV_REASON3;
112 C_HEAD_REASONS := C_HEAD_REASONS || ',' || V_LEAV_REASON3;
113 END IF;
114 IF P_LEAV_REASON4 IS NOT NULL THEN
115 SELECT
116 MEANING
117 INTO
118 V_LEAV_REASON4
119 FROM
120 HR_LOOKUPS
121 WHERE LOOKUP_TYPE = P_LOOKUP_TYPE
122 AND LOOKUP_CODE = P_LEAV_REASON4;
123 C_LEAV_REASON4 := V_LEAV_REASON4;
124 C_HEAD_REASONS := C_HEAD_REASONS || ',' || V_LEAV_REASON4;
125 END IF;
126 IF P_LEAV_REASON5 IS NOT NULL THEN
127 SELECT
128 MEANING
129 INTO
130 V_LEAV_REASON5
131 FROM
132 HR_LOOKUPS
133 WHERE LOOKUP_TYPE = P_LOOKUP_TYPE
134 AND LOOKUP_CODE = P_LEAV_REASON5;
135 C_LEAV_REASON5 := V_LEAV_REASON5;
136 C_HEAD_REASONS := C_HEAD_REASONS || ',' || V_LEAV_REASON5;
137 END IF;
138 IF P_LEAV_REASON6 IS NOT NULL THEN
139 SELECT
140 MEANING
141 INTO
142 V_LEAV_REASON6
143 FROM
144 HR_LOOKUPS
145 WHERE LOOKUP_TYPE = P_LOOKUP_TYPE
146 AND LOOKUP_CODE = P_LEAV_REASON6;
147 C_LEAV_REASON6 := V_LEAV_REASON6;
148 C_HEAD_REASONS := C_HEAD_REASONS || ',' || V_LEAV_REASON6;
149 END IF;
150 END;
151 --Display parameters introduced
152 P_DATE_TO_T := to_char(P_DATE_TO,'DD-MON-YYYY');
153 P_DATE_FROM_T := to_char(P_DATE_FROM,'DD-MON-YYYY');
154 P_SESSION_DATE_T := to_char(P_SESSION_DATE,'DD-MON-YYYY');
155
156 RETURN (TRUE);
157 END BEFOREREPORT;
158
159 FUNCTION AFTERREPORT RETURN BOOLEAN IS
160 BEGIN
161 -- HR_STANDARD.EVENT('AFTER REPORT');
162 RETURN (TRUE);
163 END AFTERREPORT;
164
165 FUNCTION C_BUSINESS_GROUP_NAME_P RETURN VARCHAR2 IS
166 BEGIN
167 RETURN C_BUSINESS_GROUP_NAME;
168 END C_BUSINESS_GROUP_NAME_P;
169
170 FUNCTION C_REPORT_SUBTITLE_P RETURN VARCHAR2 IS
171 BEGIN
172 RETURN C_REPORT_SUBTITLE;
173 END C_REPORT_SUBTITLE_P;
174
175 FUNCTION C_PARENT_ORGANIZATION_NAME_P RETURN VARCHAR2 IS
176 BEGIN
177 RETURN C_PARENT_ORGANIZATION_NAME;
178 END C_PARENT_ORGANIZATION_NAME_P;
179
180 FUNCTION C_ORG_STRUCTURE_NAME_P RETURN VARCHAR2 IS
181 BEGIN
182 RETURN C_ORG_STRUCTURE_NAME;
183 END C_ORG_STRUCTURE_NAME_P;
184
185 FUNCTION C_HEAD_REASONS_P RETURN VARCHAR2 IS
186 BEGIN
187 RETURN C_HEAD_REASONS;
188 END C_HEAD_REASONS_P;
189
190 FUNCTION C_LEAV_REASON1_P RETURN VARCHAR2 IS
191 BEGIN
192 RETURN C_LEAV_REASON1;
193 END C_LEAV_REASON1_P;
194
195 FUNCTION C_LEAV_REASON2_P RETURN VARCHAR2 IS
196 BEGIN
197 RETURN C_LEAV_REASON2;
198 END C_LEAV_REASON2_P;
199
200 FUNCTION C_LEAV_REASON3_P RETURN VARCHAR2 IS
201 BEGIN
202 RETURN C_LEAV_REASON3;
203 END C_LEAV_REASON3_P;
204
205 FUNCTION C_LEAV_REASON4_P RETURN VARCHAR2 IS
206 BEGIN
207 RETURN C_LEAV_REASON4;
208 END C_LEAV_REASON4_P;
209
210 FUNCTION C_LEAV_REASON5_P RETURN VARCHAR2 IS
211 BEGIN
212 RETURN C_LEAV_REASON5;
213 END C_LEAV_REASON5_P;
214
215 FUNCTION C_LEAV_REASON6_P RETURN VARCHAR2 IS
216 BEGIN
217 RETURN C_LEAV_REASON6;
218 END C_LEAV_REASON6_P;
219
220 FUNCTION C_GLOBAL_HIERARCHY_P RETURN VARCHAR2 IS
221 BEGIN
222 RETURN C_GLOBAL_HIERARCHY;
223 END C_GLOBAL_HIERARCHY_P;
224
225 END PER_PERRPCTM_XMLP_PKG;
[Home] [Help]