[Home] [Help]
PACKAGE BODY: APPS.PER_CHANGE_PAY_ANALYTICS_PKG
Source
1 PACKAGE BODY PER_CHANGE_PAY_ANALYTICS_PKG as
2 /* $Header: pechpanl.pkb 120.2.12020000.2 2013/03/20 20:04:12 bmaheshw ship $ */
3 Function check_refresh_jobs
4 RETURN Number
5 IS
6 l_check number;
7 BEGIN
8 SELECT 1 INTO l_check FROM PER_CHANGE_PAY_ANALYTICS WHERE NVL(JOB_ID,-1) = -2 AND NVL(PAY_BASIS_ID,-1) = -2;
9 return l_check;
10 exception
11 when no_data_found then l_check := 0;
12 return l_check;
13 end;
14
15 procedure insert_refresh_jobs
16 IS
17 pragma autonomous_transaction;
18 BEGIN
19 INSERT INTO PER_CHANGE_PAY_ANALYTICS (JOB_ID,PAY_BASIS_ID) VALUES (-2,-2);
20 commit;
21 END insert_refresh_jobs;
22
23
24 procedure delete_refresh_jobs
25 IS
26 pragma autonomous_transaction;
27 BEGIN
28 delete from PER_CHANGE_PAY_ANALYTICS WHERE NVL(JOB_ID,-1) = -2 AND NVL(PAY_BASIS_ID,-1) = -2;
29 commit;
30 END delete_refresh_jobs;
31
32 procedure PER_CHANGE_PAY_ANALYTICS_PROC(ERRBUF OUT NOCOPY varchar2,
33 RETCODE OUT NOCOPY number
34 )
35 IS
36 CURSOR ASSIGNMENTSID IS
37 SELECT paf.assignment_id
38 FROM per_all_assignments_f paf, per_assignment_status_types past
39 WHERE paf.primary_flag = 'Y'
40 AND TRUNC (SYSDATE) BETWEEN paf.effective_start_date AND paf.effective_end_date
41 AND paf.assignment_type IN ('E','C')
42 AND paf.assignment_status_type_id = past.assignment_status_type_id
43 AND past.per_system_status <>'TERM_ASSIGN' ;
44
45
46 EMP_ASSIGNMENT_ID per_all_assignments_f.assignment_id%type;
47 l_check_var NUMBER;
48 table_name_var VARCHAR2(300);
49 PAYCHANGE sys_refcursor;
50
51 TYPE CP_Analytics_Bulk_Collect IS TABLE OF PER_CHANGE_PAY_ANALYTICS%ROWTYPE;
52 PSA CP_Analytics_Bulk_Collect;
53
54 BEGIN
55
56 SELECT FND_GLOBAL.CONC_REQUEST_ID INTO Conc_Prog_Id FROM DUAL;
57
58 l_check_var := PER_CHANGE_PAY_ANALYTICS_PKG.check_refresh_jobs;
59
60 if Conc_Prog_Id = -1 and l_check_var = 1 then
61 fnd_message.set_name('PER', 'PER_SAL_ANALYTICS_REFRESH_RUNG');
62 fnd_message.raise_error;
63 end if;
64
65
66
67 PER_CHANGE_PAY_ANALYTICS_PKG.MESSAGE_LOG_PROC('ENTERING PER_CHANGE_PAY_ANALYTICS_PROC',10);
68
69 select s.table_owner||'.'||nvl(ev.table_name, s.table_name) into table_name_var
70 from user_synonyms s, dba_editioning_views ev
71 where synonym_name = 'PER_CHANGE_PAY_ANALYTICS'
72 and ev.owner(+) = s.table_owner
73 and ev.view_name(+) = s.table_name;
74
75 EXECUTE IMMEDIATE 'TRUNCATE TABLE '||trim(table_name_var);
76
77 PER_CHANGE_PAY_ANALYTICS_PKG.insert_refresh_jobs;
78
79 OPEN ASSIGNMENTSID;
80
81
82 LOOP
83
84 FETCH ASSIGNMENTSID INTO EMP_ASSIGNMENT_ID;
85
86
87 EXIT WHEN ASSIGNMENTSID%NOTFOUND;
88
89 PER_CHANGE_PAY_ANALYTICS_PKG.MESSAGE_LOG_PROC('Looping for Assignment ID '||EMP_ASSIGNMENT_ID,20);
90
91 OPEN PAYCHANGE FOR SELECT
92 sal.effective_start_date effective_start_date,
93 CASE WHEN sal.effective_start_date = TRUNC(sysdate) THEN hr_general.end_of_time
94 ELSE sal.effective_end_date
95 END effective_end_date,
96 sal.job_id job_id,
97 sal.country country,
98 sal.pay_basis_id pay_basis_id,
99 sal.supervisor_id supervisor_id,
100 SUM(sal.headcount) total_headcount,
101 percentile_cont(0.5) within GROUP(ORDER BY sal.salary) med_anl_slry,
102 sal.currency_code anl_slry_currency,
103 ROUND(AVG((sal.salary / CASE WHEN sal.grade_mid_value = 0 THEN 1 ELSE
104 sal.grade_mid_value END) * 100), 2) comparatio,
105 ROUND(AVG(((sal.salary -sal.grade_min) / CASE WHEN(sal.grade_max
106 -sal.grade_min) = 0 THEN 1 ELSE(sal.grade_max
107
108 -sal.grade_min) END) * 100), 2) range_position,
109 TRUNC(AVG(
110 CASE WHEN sal.salary <= sal.grade_min THEN 0
111 WHEN sal.salary >= sal.grade_max THEN 5
112 WHEN sal.salary <=(sal.grade_mid_value + sal.grade_min) / 2 THEN 1
113 WHEN sal.salary < sal.grade_mid_value THEN 2
114 WHEN sal.salary >=(sal.grade_mid_value + sal.grade_max) / 2 THEN 4
115 WHEN sal.salary >= sal.grade_mid_value THEN 3
116 END), 1) quartile
117 FROM
118 (SELECT DAY.EFFECTIVE_DATE EFFECTIVE_START_DATE,
119 ADD_MONTHS(DAY.EFFECTIVE_DATE, 12) -1 EFFECTIVE_END_DATE,
120 ASG.BUSINESS_GROUP_ID,
121 ASG.ASSIGNMENT_ID,
122 ASG.JOB_ID JOB_ID,
123 LOC.COUNTRY COUNTRY,
124 ASG.PAY_BASIS_ID,
125 SUPH.SUP_PERSON_ID SUPERVISOR_ID,
126 ASG.GRADE_ID,
127 PGR.RATE_ID,
128 HRI_BPL_ABV.CALC_ABV(ASG.ASSIGNMENT_ID,
129 ASG.BUSINESS_GROUP_ID, 'HEAD', PRO.CHANGE_DATE) HEADCOUNT ,
130 CASE WHEN PPB.PAY_BASIS = 'HOURLY' THEN (PPB.PAY_ANNUALIZATION_FACTOR *
131 PRO.PROPOSED_SALARY_N)
132 ELSE (PPB.PAY_ANNUALIZATION_FACTOR * PRO.PROPOSED_SALARY_N)/CASE WHEN
133
134 PER_SALADMIN_UTILITY.GET_FTE_FACTOR(ASG.ASSIGNMENT_ID
135 ,DAY.EFFECTIVE_DATE) = 0 THEN 1 ELSE
136
137 PER_SALADMIN_UTILITY.GET_FTE_FACTOR(ASG.ASSIGNMENT_ID
138 ,DAY.EFFECTIVE_DATE) END END SALARY ,
139 PGR.CURRENCY_CODE GRADE_CURRENCY ,
140 CASE WHEN PET.INPUT_CURRENCY_CODE IS NOT NULL
141 AND PGR.CURRENCY_CODE IS NOT NULL
142 AND PGR.CURRENCY_CODE <> PET.INPUT_CURRENCY_CODE
143 THEN PER_SALADMIN_UTILITY.GET_CURRENCY_RATE( PGR.CURRENCY_CODE,
144 PET.INPUT_CURRENCY_CODE, DAY.EFFECTIVE_DATE,
145
146 ASG.BUSINESS_GROUP_ID) * PGR.MINIMUM*PPB.GRADE_ANNUALIZATION_FACTOR
147 ELSE PGR.MINIMUM*PPB.GRADE_ANNUALIZATION_FACTOR END GRADE_MIN ,
148 CASE WHEN PET.INPUT_CURRENCY_CODE IS NOT NULL
149 AND PGR.CURRENCY_CODE IS NOT NULL
150 AND PGR.CURRENCY_CODE <> PET.INPUT_CURRENCY_CODE
151 THEN PER_SALADMIN_UTILITY.GET_CURRENCY_RATE( PGR.CURRENCY_CODE,
152 PET.INPUT_CURRENCY_CODE, DAY.EFFECTIVE_DATE,
153
154 ASG.BUSINESS_GROUP_ID) * PGR.MAXIMUM*PPB.GRADE_ANNUALIZATION_FACTOR ELSE
155 PGR.MAXIMUM*PPB.GRADE_ANNUALIZATION_FACTOR END
156
157 GRADE_MAX ,
158 CASE WHEN PET.INPUT_CURRENCY_CODE IS NOT NULL
159 AND PGR.CURRENCY_CODE IS NOT NULL
160 AND PGR.CURRENCY_CODE <> PET.INPUT_CURRENCY_CODE
161 THEN PER_SALADMIN_UTILITY.GET_CURRENCY_RATE( PGR.CURRENCY_CODE,
162 PET.INPUT_CURRENCY_CODE, DAY.EFFECTIVE_DATE,
163
164 ASG.BUSINESS_GROUP_ID) * PGR.MID_VALUE*PPB.GRADE_ANNUALIZATION_FACTOR
165 ELSE PGR.MID_VALUE*PPB.GRADE_ANNUALIZATION_FACTOR END
166
167 GRADE_MID_VALUE ,
168 PET.INPUT_CURRENCY_CODE CURRENCY_CODE
169 FROM PER_ALL_ASSIGNMENTS_F ASG ,
170 (SELECT level-1 SUB_RELATIVE_LEVEL,
171 paf.assignment_id SUB_ASSIGNMENT_ID,
172 paf.person_id SUB_PERSON_ID,
173 paf.supervisor_id SUP_PERSON_ID,
174
175 paf.EFFECTIVE_START_DATE EFFECTIVE_START_DATE,
176
177 paf.EFFECTIVE_END_DATE EFFECTIVE_END_DATE
178 FROM per_all_assignments_f paf
179 WHERE paf.primary_flag = 'Y'
180 AND TRUNC (SYSDATE) BETWEEN paf.effective_start_date AND
181 paf.effective_end_date
182 AND paf.assignment_type IN ('E','C')
183 START WITH paf.assignment_id = EMP_ASSIGNMENT_ID
184 CONNECT BY NOCYCLE PRIOR paf.person_id = nvl(paf.supervisor_id,-1))
185 SUPH ,
186 PER_PAY_BASES PPB ,
187 PER_PAY_PROPOSALS PRO ,
188 PAY_INPUT_VALUES_F PIV ,
189 PAY_ELEMENT_TYPES_F PET ,
190 HR_LOCATIONS_ALL LOC ,
191 PAY_GRADE_RULES_F PGR ,
192 ( SELECT TRUNC(SYSDATE) EFFECTIVE_DATE FROM DUAL
193 UNION ALL
194 SELECT ADD_MONTHS(TRUNC(SYSDATE), -12) EFFECTIVE_DATE FROM DUAL
195 UNION ALL
196 SELECT ADD_MONTHS(TRUNC(SYSDATE), -24) EFFECTIVE_DATE FROM DUAL
197 UNION ALL
198 SELECT ADD_MONTHS(TRUNC(SYSDATE), -36) EFFECTIVE_DATE FROM DUAL
199 UNION ALL
200 SELECT ADD_MONTHS(TRUNC(SYSDATE), -48) EFFECTIVE_DATE FROM DUAL ) DAY
201 WHERE DAY.EFFECTIVE_DATE BETWEEN ASG.EFFECTIVE_START_DATE AND ASG.EFFECTIVE_END_DATE
202 AND ASG.JOB_ID IS NOT NULL
203 AND SUPH.SUB_PERSON_ID = ASG.PERSON_ID
204 AND SUPH.SUB_RELATIVE_LEVEL <> 0
205 AND DAY.EFFECTIVE_DATE BETWEEN SUPH.EFFECTIVE_START_DATE AND SUPH.EFFECTIVE_END_DATE
206 AND ASG.GRADE_ID = PGR.GRADE_OR_SPINAL_POINT_ID
207 AND PPB.RATE_ID = PGR.RATE_ID
208 AND ASG.ASSIGNMENT_ID = PRO.ASSIGNMENT_ID
209 AND PRO.CHANGE_DATE = (SELECT CHANGE_DATE
210 FROM PER_PAY_PROPOSALS PRO2
211 WHERE PRO2.ASSIGNMENT_ID = PRO.ASSIGNMENT_ID
212 AND DAY.EFFECTIVE_DATE BETWEEN PRO2.CHANGE_DATE AND PRO2.DATE_TO
213 AND PRO2.APPROVED = 'Y')
214 AND ASG.PAY_BASIS_ID = PPB.PAY_BASIS_ID
215 AND PPB.INPUT_VALUE_ID = PIV.INPUT_VALUE_ID
216 AND PIV.ELEMENT_TYPE_ID = PET.ELEMENT_TYPE_ID
217 AND LOC.LOCATION_ID = ASG.LOCATION_ID
218 AND PRO.CHANGE_DATE BETWEEN PIV.EFFECTIVE_START_DATE AND PIV.EFFECTIVE_END_DATE
219 AND PRO.CHANGE_DATE BETWEEN PET.EFFECTIVE_START_DATE AND PET.EFFECTIVE_END_DATE
220 AND PRO.CHANGE_DATE BETWEEN PGR.EFFECTIVE_START_DATE AND PGR.EFFECTIVE_END_DATE )
221 SAL
222 GROUP BY SAL.EFFECTIVE_START_DATE ,
223 SAL.EFFECTIVE_END_DATE ,
224 SAL.SUPERVISOR_ID,
225 SAL.JOB_ID ,
226 SAL.COUNTRY ,
227 SAL.PAY_BASIS_ID,
228 SAL.CURRENCY_CODE;
229
230
231
232
233
234
235
236
237
238 LOOP
239
240 FETCH PAYCHANGE BULK COLLECT INTO PSA LIMIT 2000;
241
242 FORALL i IN 1..PSA.COUNT
243 INSERT INTO PER_CHANGE_PAY_ANALYTICS VALUES PSA(i);
244
245 PER_CHANGE_PAY_ANALYTICS_PKG.MESSAGE_LOG_PROC('INSERTED '||SQL%ROWCOUNT|| ' Rows in to PER_CHANGE_PAY_ANALYTICS TABLE',50);
246 EXIT WHEN PAYCHANGE%NOTFOUND;
247 COMMIT;
248 END LOOP;
249 COMMIT;
250 CLOSE PAYCHANGE;
251
252
253
254
255
256
257 END LOOP;
258
259 COMMIT;
260 CLOSE ASSIGNMENTSID;
261
262 PER_CHANGE_PAY_ANALYTICS_PKG.delete_refresh_jobs;
263 PER_CHANGE_PAY_ANALYTICS_PKG.MESSAGE_LOG_PROC('GATHERING STATISTICS FOR PER_CHANGE_PAY_ANALYTICS',80);
264 FND_STATS.GATHER_TABLE_STATS('HR','PER_CHANGE_PAY_ANALYTICS');
265 PER_CHANGE_PAY_ANALYTICS_PKG.MESSAGE_LOG_PROC('LEAVING PER_CHANGE_PAY_ANALYTICS_PROC',100);
266
267
268 EXCEPTION WHEN OTHERS THEN
269 errbuf := errbuf||SQLERRM;
270 retcode := '1';
271 PER_CHANGE_PAY_ANALYTICS_PKG.MESSAGE_LOG_PROC('Error in PER_CHANGE_PAY_ANALYTICS_PROC: '||SQLCODE,0);
272 PER_CHANGE_PAY_ANALYTICS_PKG.MESSAGE_LOG_PROC(1,0);
273 PER_CHANGE_PAY_ANALYTICS_PKG.MESSAGE_LOG_PROC('Error Msg: '||substr(SQLERRM,1,700),0);
274
275 END PER_CHANGE_PAY_ANALYTICS_PROC;
276
277 PROCEDURE MESSAGE_LOG_PROC(MESSAGE IN VARCHAR2,STAGE NUMBER)
278 IS
279 BEGIN
280 IF Conc_Prog_Id = -1 THEN
281 hr_utility.set_location (MESSAGE,STAGE);
282 ELSE
283 FND_FILE.put_line(fnd_file.log,MESSAGE);
284 END IF;
285
286 END MESSAGE_LOG_PROC;
287
288
289 END PER_CHANGE_PAY_ANALYTICS_PKG;