[Home] [Help]
PACKAGE BODY: APPS.PER_PERRPRTM_XMLP_PKG
Source
1 PACKAGE BODY PER_PERRPRTM_XMLP_PKG AS
2 /* $Header: PERRPRTMB.pls 120.1 2007/12/06 11:34:11 amakrish noship $ */
3 FUNCTION BEFOREREPORT RETURN BOOLEAN IS
4 BEGIN
5 DECLARE
6 L_PROFILE_ID NUMBER;
7 L_VIEW_ALL_FLAG NUMBER;
8 CURSOR CSR_VIEW_ALL_FLAG(L_PFID IN NUMBER) IS
9 SELECT
10 DECODE(VIEW_ALL_ORGANIZATIONS_FLAG
11 ,'Y'
12 ,1
13 ,0)
14 FROM
15 PER_SECURITY_PROFILES
16 WHERE SECURITY_PROFILE_ID = L_PFID;
17 CURSOR CSR_ORG_STRUCTURE_VERSION(P_ORG_STRUCTURE_VERSION_ID IN NUMBER) IS
18 SELECT
19 BUSINESS_GROUP_ID
20 FROM
21 PER_ORG_STRUCTURE_VERSIONS
22 WHERE ORG_STRUCTURE_VERSION_ID = P_ORG_STRUCTURE_VERSION_ID;
23 CURSOR CSR_ORGANIZATION(P_ORGANIZATION_ID IN NUMBER) IS
24 SELECT
25 BUSINESS_GROUP_ID
26 FROM
27 HR_ALL_ORGANIZATION_UNITS
28 WHERE ORGANIZATION_ID = P_ORGANIZATION_ID;
29 V_ORGANIZATION_NAME VARCHAR2(240);
30 V_ORGANIZATION_TYPE VARCHAR2(80);
31 V_ORG_STRUCTURE_NAME VARCHAR2(30);
32 V_ORG_VERSION NUMBER;
33 V_VERSION_START_DATE DATE;
34 V_VERSION_END_DATE DATE;
35 V_LEAV_REASON1 VARCHAR2(30);
36 V_LEAV_RDESC1 VARCHAR2(80);
37 V_LEAV_REASON2 VARCHAR2(30);
38 V_LEAV_RDESC2 VARCHAR2(80);
39 V_LEAV_REASON3 VARCHAR2(30);
40 V_LEAV_RDESC3 VARCHAR2(80);
41 V_LEAV_REASON4 VARCHAR2(30);
42 V_LEAV_RDESC4 VARCHAR2(80);
43 V_LEAV_REASON5 VARCHAR2(30);
44 V_LEAV_RDESC5 VARCHAR2(80);
45 V_LEAV_REASON6 VARCHAR2(30);
46 V_LEAV_RDESC6 VARCHAR2(80);
47 BEGIN
48 --HR_STANDARD.EVENT('BEFORE REPORT');
49 IF P_ALTERNATE = 'Y' THEN
50 P_LOOKUP_TYPE := 'FR_ENDING_REASON';
51 P_REASON_COLUMN := 'P.PDS_INFORMATION2';
52 ELSE
53 P_LOOKUP_TYPE := 'LEAV_REAS';
54 P_REASON_COLUMN := 'P.LEAVING_REASON';
55 END IF;
56 IF (P_ORG_STRUCTURE_VERSION_ID IS NOT NULL) THEN
57 OPEN CSR_ORG_STRUCTURE_VERSION(P_ORG_STRUCTURE_VERSION_ID);
58 FETCH CSR_ORG_STRUCTURE_VERSION
59 INTO P_BUSINESS_GROUP_ID;
60 CLOSE CSR_ORG_STRUCTURE_VERSION;
61 ELSE
62 OPEN CSR_ORGANIZATION(P_PARENT_ORGANIZATION_ID);
63 FETCH CSR_ORGANIZATION
64 INTO P_BUSINESS_GROUP_ID;
65 CLOSE CSR_ORGANIZATION;
66 END IF;
67 IF (P_BUSINESS_GROUP_ID IS NOT NULL) THEN
68 C_BUSINESS_GROUP_NAME := HR_REPORTS.GET_BUSINESS_GROUP(P_BUSINESS_GROUP_ID);
69 ELSE
70 C_BUSINESS_GROUP_NAME := C_GLOBAL_HIERARCHY;
71 END IF;
72 HR_REPORTS.GET_ORGANIZATION(P_PARENT_ORGANIZATION_ID
73 ,V_ORGANIZATION_NAME
74 ,V_ORGANIZATION_TYPE);
75 C_PARENT_ORGANIZATION_NAME := V_ORGANIZATION_NAME;
76 IF P_ORG_STRUCTURE_VERSION_ID IS NOT NULL THEN
77 HR_REPORTS.GET_ORGANIZATION_HIERARCHY(NULL
78 ,P_ORG_STRUCTURE_VERSION_ID
79 ,V_ORG_STRUCTURE_NAME
80 ,V_ORG_VERSION
81 ,V_VERSION_START_DATE
82 ,V_VERSION_END_DATE);
83 C_ORG_STRUCTURE_NAME := V_ORG_STRUCTURE_NAME;
84 ELSE
85 C_ORG_STRUCTURE_NAME := '';
86 END IF;
87 IF P_PARENT_ORGANIZATION_ID IS NOT NULL THEN
88 P_ORG_MATCHING := ' where p.organization_id = :p_parent_organization_id ';
89 ELSIF P_ORG_STRUCTURE_VERSION_ID IS NOT NULL THEN
90 P_ORG_MATCHING := ' where p.organization_id in ( select organization_id_child ' || ' from per_org_structure_elements ' || ' where org_structure_version_id = ' ||
91 TO_CHAR(P_ORG_STRUCTURE_VERSION_ID) || ' union ' || ' select distinct organization_id_parent ' ||
92 ' from per_org_structure_elements ' || ' where org_structure_version_id = ' || TO_CHAR(P_ORG_STRUCTURE_VERSION_ID) || ')';
93 ELSE
94 L_PROFILE_ID := FND_GLOBAL.PER_SECURITY_PROFILE_ID;
95 OPEN CSR_VIEW_ALL_FLAG(L_PROFILE_ID);
96 FETCH CSR_VIEW_ALL_FLAG
97 INTO L_VIEW_ALL_FLAG;
98 CLOSE CSR_VIEW_ALL_FLAG;
99 IF L_PROFILE_ID IS NULL OR L_VIEW_ALL_FLAG = 1 THEN
100 P_ORG_MATCHING := ' where p.organization_id in ( select HOI.organization_id ' || 'from hr_organization_units hou , hr_organization_information hoi ' || 'where hou.business_group_id= ' || P_BUSINESS_GROUP_ID ||
101 ' and hou.organization_id=hoi.organization_id and hoi.ORG_INFORMATION1 = ''HR_ORG'' ) ';
102 /*SRW.MESSAGE(10
103 ,'' || P_ORG_MATCHING)*/NULL;
104 ELSE
105 P_ORG_MATCHING := ' where p.organization_id in ( select organization_id ' || 'FROM PER_ORGANIZATION_LIST WHERE SECURITY_PROFILE_ID =' || L_PROFILE_ID || ')';
106 END IF;
107 END IF;
108 IF P_LEAV_REASON1 IS NOT NULL THEN
109 SELECT
110 SUBSTR(MEANING
111 ,1
112 ,30),
113 MEANING
114 INTO V_LEAV_REASON1,V_LEAV_RDESC1
115 FROM
116 HR_LOOKUPS
117 WHERE LOOKUP_TYPE = P_LOOKUP_TYPE
118 AND LOOKUP_CODE = P_LEAV_REASON1;
119 C_LEAV_REASON1 := V_LEAV_REASON1;
120 C_HEAD_REASONS := V_LEAV_RDESC1;
121 END IF;
122 IF P_LEAV_REASON2 IS NOT NULL THEN
123 SELECT
124 SUBSTR(MEANING
125 ,1
126 ,30),
127 MEANING
128 INTO V_LEAV_REASON2,V_LEAV_RDESC2
129 FROM
130 HR_LOOKUPS
131 WHERE LOOKUP_TYPE = P_LOOKUP_TYPE
132 AND LOOKUP_CODE = P_LEAV_REASON2;
133 C_LEAV_REASON2 := V_LEAV_REASON2;
134 C_HEAD_REASONS := C_HEAD_REASONS || ',' || V_LEAV_RDESC2;
135 END IF;
136 IF P_LEAV_REASON3 IS NOT NULL THEN
137 SELECT
138 SUBSTR(MEANING
139 ,1
140 ,30),
141 MEANING
142 INTO V_LEAV_REASON3,V_LEAV_RDESC3
143 FROM
144 HR_LOOKUPS
145 WHERE LOOKUP_TYPE = P_LOOKUP_TYPE
146 AND LOOKUP_CODE = P_LEAV_REASON3;
147 C_LEAV_REASON3 := V_LEAV_REASON3;
148 C_HEAD_REASONS := C_HEAD_REASONS || ',' || V_LEAV_RDESC3;
149 END IF;
150 IF P_LEAV_REASON4 IS NOT NULL THEN
151 SELECT
152 SUBSTR(MEANING
153 ,1
154 ,30),
155 MEANING
156 INTO V_LEAV_REASON4,V_LEAV_RDESC4
157 FROM
158 HR_LOOKUPS
159 WHERE LOOKUP_TYPE = P_LOOKUP_TYPE
160 AND LOOKUP_CODE = P_LEAV_REASON4;
161 C_LEAV_REASON4 := V_LEAV_REASON4;
162 C_HEAD_REASONS := C_HEAD_REASONS || ',' || V_LEAV_RDESC4;
163 END IF;
164 IF P_LEAV_REASON5 IS NOT NULL THEN
165 SELECT
166 SUBSTR(MEANING
167 ,1
168 ,30),
169 MEANING
170 INTO V_LEAV_REASON5,V_LEAV_RDESC5
171 FROM
172 HR_LOOKUPS
173 WHERE LOOKUP_TYPE = P_LOOKUP_TYPE
174 AND LOOKUP_CODE = P_LEAV_REASON5;
175 C_LEAV_REASON5 := V_LEAV_REASON5;
176 C_HEAD_REASONS := C_HEAD_REASONS || ',' || V_LEAV_RDESC5;
177 END IF;
178 IF P_LEAV_REASON6 IS NOT NULL THEN
179 SELECT
180 SUBSTR(MEANING
181 ,1
182 ,30),
183 MEANING
184 INTO V_LEAV_REASON6,V_LEAV_RDESC6
185 FROM
186 HR_LOOKUPS
187 WHERE LOOKUP_TYPE = P_LOOKUP_TYPE
188 AND LOOKUP_CODE = P_LEAV_REASON6;
189 C_LEAV_REASON6 := V_LEAV_REASON6;
190 C_HEAD_REASONS := C_HEAD_REASONS || ',' || V_LEAV_RDESC6;
191 END IF;
192 END;
193 RETURN (TRUE);
194 END BEFOREREPORT;
195
196 FUNCTION AFTERREPORT RETURN BOOLEAN IS
197 BEGIN
198 --HR_STANDARD.EVENT('AFTER REPORT');
199 RETURN (TRUE);
200 END AFTERREPORT;
201
202 FUNCTION C_BUSINESS_GROUP_NAME_P RETURN VARCHAR2 IS
203 BEGIN
204 RETURN C_BUSINESS_GROUP_NAME;
205 END C_BUSINESS_GROUP_NAME_P;
206
207 FUNCTION C_REPORT_SUBTITLE_P RETURN VARCHAR2 IS
208 BEGIN
209 RETURN C_REPORT_SUBTITLE;
210 END C_REPORT_SUBTITLE_P;
211
212 FUNCTION C_PARENT_ORGANIZATION_NAME_P RETURN VARCHAR2 IS
213 BEGIN
214 RETURN C_PARENT_ORGANIZATION_NAME;
215 END C_PARENT_ORGANIZATION_NAME_P;
216
217 FUNCTION C_ORG_STRUCTURE_NAME_P RETURN VARCHAR2 IS
218 BEGIN
219 RETURN C_ORG_STRUCTURE_NAME;
220 END C_ORG_STRUCTURE_NAME_P;
221
222 FUNCTION C_HEAD_REASONS_P RETURN VARCHAR2 IS
223 BEGIN
224 RETURN C_HEAD_REASONS;
225 END C_HEAD_REASONS_P;
226
227 FUNCTION C_LEAV_REASON1_P RETURN VARCHAR2 IS
228 BEGIN
229 RETURN C_LEAV_REASON1;
230 END C_LEAV_REASON1_P;
231
232 FUNCTION C_LEAV_REASON2_P RETURN VARCHAR2 IS
233 BEGIN
234 RETURN C_LEAV_REASON2;
235 END C_LEAV_REASON2_P;
236
237 FUNCTION C_LEAV_REASON3_P RETURN VARCHAR2 IS
238 BEGIN
239 RETURN C_LEAV_REASON3;
240 END C_LEAV_REASON3_P;
241
242 FUNCTION C_LEAV_REASON4_P RETURN VARCHAR2 IS
243 BEGIN
244 RETURN C_LEAV_REASON4;
245 END C_LEAV_REASON4_P;
246
247 FUNCTION C_LEAV_REASON5_P RETURN VARCHAR2 IS
248 BEGIN
249 RETURN C_LEAV_REASON5;
250 END C_LEAV_REASON5_P;
251
252 FUNCTION C_LEAV_REASON6_P RETURN VARCHAR2 IS
253 BEGIN
254 RETURN C_LEAV_REASON6;
255 END C_LEAV_REASON6_P;
256
257 FUNCTION C_GLOBAL_HIERARCHY_P RETURN VARCHAR2 IS
258 BEGIN
259 RETURN C_GLOBAL_HIERARCHY;
260 END C_GLOBAL_HIERARCHY_P;
261
262 END PER_PERRPRTM_XMLP_PKG;