[Home] [Help]
PACKAGE BODY: APPS.GHR_GHRCPDFO_XMLP_PKG
Source
1 PACKAGE BODY GHR_GHRCPDFO_XMLP_PKG AS
2 /* $Header: GHRCPDFOB.pls 120.0 2007/12/04 08:01:59 srikrish noship $ */
3 FUNCTION AFTERPFORM RETURN BOOLEAN IS
4 BEGIN
5 IF FILENAME IS NOT NULL THEN
6 --DESNAME := FILENAME;
7 null;
8 END IF;
9 DELETE FROM GHR_CPDF_TEMP
10 WHERE REPORT_TYPE = 'GHRCPDFO'
11 AND SESSION_ID = USERENV('sessionid');
12 POPULATE_DATA;
13 RETURN (TRUE);
14 END AFTERPFORM;
15
16 PROCEDURE POPULATE_DATA IS
17 /* P_ORGANIZATION_HIERARCHY NUMBER := P_ORGANIZATION_HIERARCHY;
18 P_HIERARCHY_VERSION NUMBER := P_HIERARCHY_VERSION;
19 P_REPORT_DATE DATE := P_REPORT_DATE;
20 P_AGENCY_CODE VARCHAR2(30) := P_AGENCY_CODE;
21 P_AGENCY_SUBELEMENT VARCHAR2(30) := P_AGENCY_SUBELEMENT;*/
22 L_COUNT NUMBER;
23 L_DUMMY NUMBER;
24 L_ORG_STR_VERSION_ID PER_ORG_STRUCTURE_VERSIONS.ORG_STRUCTURE_VERSION_ID%TYPE;
25 L_BUS_GROUP_ID PER_ORGANIZATION_STRUCTURES.BUSINESS_GROUP_ID%TYPE;
26 CURSOR C_PARENT IS
27 SELECT
28 DISTINCT
29 0 LVL,
30 ORGANIZATION_ID_PARENT ID,
31 ORG_STRUCTURE_VERSION_ID
32 FROM
33 PER_ORG_STRUCTURE_ELEMENTS ELE
34 WHERE ELE.ORGANIZATION_ID_PARENT NOT IN (
35 SELECT
36 ORGANIZATION_ID_CHILD
37 FROM
38 PER_ORG_STRUCTURE_ELEMENTS ELE,
39 PER_ORG_STRUCTURE_VERSIONS VER
40 WHERE VER.ORGANIZATION_STRUCTURE_ID = P_ORGANIZATION_HIERARCHY
41 AND VER.ORG_STRUCTURE_VERSION_ID = ELE.ORG_STRUCTURE_VERSION_ID
42 AND VER.ORG_STRUCTURE_VERSION_ID = P_HIERARCHY_VERSION )
43 AND ELE.ORG_STRUCTURE_VERSION_ID = P_HIERARCHY_VERSION;
44 CURSOR C_REGULAR IS
45 SELECT
46 DISTINCT
47 ORG.ORGANIZATION_ID ID,
48 SUBSTR(PPDK.SEGMENT4
49 ,1
50 ,2) AGENCY,
51 SUBSTR(PPEI.POEI_INFORMATION5
52 ,1
53 ,18) COMPONENT,
54 RPAD(NVL(OEI.ORG_INFORMATION11
55 ,HRU.NAME)
56 ,179
57 ,' ') TITLE,
58 PPDK.SEGMENT4 AGENCY_SUBELEMENT
59 FROM
60 PER_ORGANIZATION_UNITS ORG,
61 HR_ORGANIZATION_INFORMATION OEI,
62 HR_ORGANIZATION_UNITS HRU,
63 PER_POSITION_EXTRA_INFO PPEI,
64 PER_POSITIONS PPOS,
65 PER_POSITION_DEFINITIONS PPDK
66 WHERE OEI.ORGANIZATION_ID = ORG.ORGANIZATION_ID
67 AND PPOS.ORGANIZATION_ID = OEI.ORGANIZATION_ID
68 AND PPOS.POSITION_ID = PPEI.POSITION_ID
69 AND PPDK.POSITION_DEFINITION_ID = PPOS.POSITION_DEFINITION_ID
70 AND PPEI.INFORMATION_TYPE LIKE 'GHR_US_POS_GRP1'
71 AND OEI.ORG_INFORMATION_CONTEXT = 'GHR_US_ORG_REPORTING_INFO'
72 AND OEI.ORGANIZATION_ID = HRU.ORGANIZATION_ID
73 AND PPDK.SEGMENT4 LIKE P_AGENCY_CODE || NVL(P_AGENCY_SUBELEMENT
74 ,'%')
75 AND ORG.DATE_FROM <= P_REPORT_DATE
76 AND HRU.DATE_FROM <= P_REPORT_DATE
77 AND ( PPOS.DATE_END >= P_REPORT_DATE
78 OR PPOS.DATE_END IS NULL )
79 ORDER BY
80 2,
81 3;
82 CURSOR C_DETAIL(P_ID IN NUMBER) IS
83 SELECT
84 DISTINCT
85 SUBSTR(PPDK.SEGMENT4
86 ,1
87 ,2) AGENCY,
88 SUBSTR(PPEI.POEI_INFORMATION5
89 ,1
90 ,18) COMPONENT,
91 RPAD(NVL(OEI.ORG_INFORMATION11
92 ,HRU.NAME)
93 ,179
94 ,' ') TITLE,
95 OEI.ORGANIZATION_ID ID,
96 HRU.ORGANIZATION_ID ORG_ID
97 FROM
98 HR_ORGANIZATION_INFORMATION OEI,
99 HR_ORGANIZATION_UNITS HRU,
100 PER_POSITION_EXTRA_INFO PPEI,
101 PER_POSITIONS PPOS,
102 PER_POSITION_DEFINITIONS PPDK
103 WHERE OEI.ORGANIZATION_ID = P_ID
104 AND PPOS.ORGANIZATION_ID = OEI.ORGANIZATION_ID
105 AND PPOS.POSITION_ID = PPEI.POSITION_ID
106 AND PPDK.POSITION_DEFINITION_ID = PPOS.POSITION_DEFINITION_ID
107 AND PPEI.INFORMATION_TYPE LIKE 'GHR_US_POS_GRP1'
108 AND OEI.ORG_INFORMATION_CONTEXT = 'GHR_US_ORG_REPORTING_INFO'
109 AND OEI.ORGANIZATION_ID = HRU.ORGANIZATION_ID
110 AND PPDK.SEGMENT4 LIKE P_AGENCY_CODE || NVL(P_AGENCY_SUBELEMENT
111 ,'%')
112 AND ( PPOS.DATE_END >= P_REPORT_DATE
113 OR PPOS.DATE_END IS NULL );
114 R_DETAIL C_DETAIL%ROWTYPE;
115 PROCEDURE GET_CHILDREN(P_PARENT_ID IN NUMBER
116 ,P_ORG_STR_VERSION_ID IN NUMBER
117 ,P_LEVEL IN NUMBER) IS
118 CURSOR C_CHILD(P_PARENT_ID IN NUMBER,P_ORG_STR_VERSION_ID IN NUMBER) IS
119 SELECT
120 DISTINCT
121 A.ORGANIZATION_ID_CHILD ID,
122 A.ORGANIZATION_ID_PARENT PARENT_ID,
123 SUBSTR(PPDK.SEGMENT4
124 ,1
125 ,2) AGENCY,
126 SUBSTR(PPEI.POEI_INFORMATION5
127 ,1
128 ,18) COMPONENT,
129 RPAD(NVL(OEI.ORG_INFORMATION11
130 ,HRU.NAME)
131 ,179
132 ,' ') TITLE,
133 PPDK.SEGMENT4 AGENCY_SUBELEMENT
134 FROM
135 PER_ORG_STRUCTURE_ELEMENTS A,
136 PER_ORG_STRUCTURE_VERSIONS B,
137 HR_ORGANIZATION_INFORMATION OEI,
138 HR_ORGANIZATION_UNITS HRU,
139 PER_POSITION_EXTRA_INFO PPEI,
140 PER_POSITIONS PPOS,
141 PER_POSITION_DEFINITIONS PPDK
142 WHERE A.ORG_STRUCTURE_VERSION_ID = B.ORG_STRUCTURE_VERSION_ID
143 AND PPOS.ORGANIZATION_ID = OEI.ORGANIZATION_ID
144 AND OEI.ORGANIZATION_ID = HRU.ORGANIZATION_ID
145 AND PPDK.POSITION_DEFINITION_ID = PPOS.POSITION_DEFINITION_ID
146 AND PPOS.POSITION_ID = PPEI.POSITION_ID
147 AND PPEI.INFORMATION_TYPE LIKE 'GHR_US_POS_GRP1'
148 AND A.ORG_STRUCTURE_VERSION_ID = P_ORG_STR_VERSION_ID
149 AND A.ORGANIZATION_ID_PARENT = P_PARENT_ID
150 AND B.DATE_FROM <= P_REPORT_DATE
151 AND HRU.DATE_FROM <= P_REPORT_DATE
152 AND A.ORG_STRUCTURE_ELEMENT_ID IN (
153 SELECT
154 ORG_STRUCTURE_ELEMENT_ID
155 FROM
156 PER_ORG_STRUCTURE_ELEMENTS
157 START WITH ORGANIZATION_ID_PARENT = P_PARENT_ID
158 CONNECT BY PRIOR ORGANIZATION_ID_CHILD = ORGANIZATION_ID_PARENT )
159 AND OEI.ORGANIZATION_ID = A.ORGANIZATION_ID_CHILD
160 AND OEI.ORG_INFORMATION_CONTEXT = 'GHR_US_ORG_REPORTING_INFO'
161 AND PPDK.SEGMENT4 LIKE P_AGENCY_CODE || NVL(P_AGENCY_SUBELEMENT
162 ,'%')
163 AND ( PPOS.DATE_END >= P_REPORT_DATE
164 OR PPOS.DATE_END IS NULL )
165 ORDER BY
166 2,
167 3;
168 BEGIN
169 FOR R_CHILD IN C_CHILD(P_PARENT_ID,P_ORG_STR_VERSION_ID) LOOP
170 L_COUNT := L_COUNT + 1;
171 INSERT INTO GHR_CPDF_TEMP
172 (REPORT_TYPE
173 ,SESSION_ID
174 ,AWARD_AMOUNT
175 ,BENEFIT_AMOUNT
176 ,FROM_BASIC_PAY
177 ,FROM_LOCALITY_ADJ
178 ,AGENCY_CODE
179 ,ORGANIZATIONAL_COMPONENT
180 ,FROM_OCC_CODE
181 ,FROM_NATIONAL_IDENTIFIER)
182 VALUES ('GHRCPDFO'
183 ,USERENV('SESSIONID')
184 ,R_CHILD.ID
185 ,R_CHILD.PARENT_ID
186 ,L_COUNT
187 ,P_LEVEL
188 ,R_CHILD.AGENCY
189 ,R_CHILD.COMPONENT
190 ,SUBSTR(R_CHILD.TITLE
191 ,1
192 ,150)
193 ,SUBSTR(R_CHILD.TITLE
194 ,151));
195 GET_CHILDREN(R_CHILD.ID
196 ,P_ORG_STR_VERSION_ID
197 ,P_LEVEL + 1);
198 END LOOP;
199 END GET_CHILDREN;
200 BEGIN
201 P_CONC_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
202 /*SRW.USER_EXIT('FND SRWINIT')*/NULL;
203 IF NOT (P_ORGANIZATION_HIERARCHY IS NULL AND P_HIERARCHY_VERSION IS NULL) THEN
204 SELECT
205 VER.ORG_STRUCTURE_VERSION_ID,
206 STRUC.BUSINESS_GROUP_ID BUS_ID
207 INTO L_ORG_STR_VERSION_ID,L_BUS_GROUP_ID
208 FROM
209 PER_ORGANIZATION_STRUCTURES STRUC,
210 PER_ORG_STRUCTURE_VERSIONS VER
211 WHERE STRUC.ORGANIZATION_STRUCTURE_ID = P_ORGANIZATION_HIERARCHY
212 AND STRUC.ORGANIZATION_STRUCTURE_ID = VER.ORGANIZATION_STRUCTURE_ID
213 AND VER.ORG_STRUCTURE_VERSION_ID = P_HIERARCHY_VERSION
214 AND P_REPORT_DATE >= VER.DATE_FROM
215 AND P_REPORT_DATE <= DECODE(VER.DATE_TO
216 ,NULL
217 ,P_REPORT_DATE
218 ,VER.DATE_TO);
219 L_COUNT := 1;
220 FOR R_PARENT IN C_PARENT LOOP
221 FOR R_DETAIL IN C_DETAIL(R_PARENT.ID) LOOP
222 INSERT INTO GHR_CPDF_TEMP
223 (REPORT_TYPE
224 ,SESSION_ID
225 ,AWARD_AMOUNT
226 ,BENEFIT_AMOUNT
227 ,FROM_BASIC_PAY
228 ,FROM_LOCALITY_ADJ
229 ,AGENCY_CODE
230 ,ORGANIZATIONAL_COMPONENT
231 ,FROM_OCC_CODE
232 ,FROM_NATIONAL_IDENTIFIER)
233 VALUES ('GHRCPDFO'
234 ,USERENV('SESSIONID')
235 ,R_PARENT.ID
236 ,NULL
237 ,L_COUNT
238 ,1
239 ,R_DETAIL.AGENCY
240 ,R_DETAIL.COMPONENT
241 ,SUBSTR(R_DETAIL.TITLE
242 ,1
243 ,150)
244 ,SUBSTR(R_DETAIL.TITLE
245 ,151));
246 L_COUNT := L_COUNT + 1;
247 END LOOP;
248 GET_CHILDREN(R_PARENT.ID
249 ,L_ORG_STR_VERSION_ID
250 ,2);
251 END LOOP;
252 ELSE
253 FOR R_REGULAR IN C_REGULAR LOOP
254 INSERT INTO GHR_CPDF_TEMP
255 (REPORT_TYPE
256 ,SESSION_ID
257 ,AWARD_AMOUNT
258 ,BENEFIT_AMOUNT
259 ,FROM_BASIC_PAY
260 ,AGENCY_CODE
261 ,ORGANIZATIONAL_COMPONENT
262 ,FROM_OCC_CODE
263 ,FROM_NATIONAL_IDENTIFIER)
264 VALUES ('GHRCPDFO'
265 ,USERENV('SESSIONID')
266 ,R_REGULAR.ID
267 ,NULL
268 ,NULL
269 ,R_REGULAR.AGENCY
270 ,R_REGULAR.COMPONENT
271 ,SUBSTR(R_REGULAR.TITLE
272 ,1
273 ,150)
274 ,SUBSTR(R_REGULAR.TITLE
275 ,151));
276 END LOOP;
277 END IF;
278 END POPULATE_DATA;
279
280 FUNCTION AFTERREPORT RETURN BOOLEAN IS
281 BEGIN
282 ROLLBACK;
283 /*SRW.USER_EXIT('FND SRWEXIT')*/NULL;
284 RETURN (TRUE);
285 END AFTERREPORT;
286
287 FUNCTION BEFOREREPORT RETURN BOOLEAN IS
288 BEGIN
289 P_CONC_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
290 /*SRW.USER_EXIT('FND SRWINIT')*/NULL;
291 RETURN (TRUE);
292 END BEFOREREPORT;
293
294 FUNCTION BETWEENPAGE RETURN BOOLEAN IS
295 BEGIN
296 RETURN (TRUE);
297 END BETWEENPAGE;
298
299 FUNCTION P_AGENCY_SUBELEMENTVALIDTRIGGE RETURN BOOLEAN IS
300 BEGIN
301 RETURN (TRUE);
302 END P_AGENCY_SUBELEMENTVALIDTRIGGE;
303
304 FUNCTION BEFOREPFORM RETURN BOOLEAN IS
305 BEGIN
306 RETURN (TRUE);
307 END BEFOREPFORM;
308
309 END GHR_GHRCPDFO_XMLP_PKG;