DBA Data[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;