1 PACKAGE BODY PER_SALADMIN_ANALYTICS_PKG as
2 /* $Header: pesalanl.pkb 120.3 2012/01/04 11:27:06 bmaheshw noship $ */
3 Function check_refresh_jobs
4 RETURN Number
5 IS
6 l_check number;
7 BEGIN
8 SELECT 1 INTO l_check FROM PER_SALADMIN_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_SALADMIN_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_SALADMIN_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_SALADMIN_ANALYTICS_PROC(ERRBUF OUT NOCOPY varchar2,
33 RETCODE OUT NOCOPY number
34 )
35 IS
36 TYPE Sal_Analytics_Bulk_Collect IS TABLE OF PER_SALADMIN_ANALYTICS%ROWTYPE;
37 l_check_var NUMBER;
38 table_name_var VARCHAR2(300);
39 PSA Sal_Analytics_Bulk_Collect;
40 CURSOR SALANALYTICS IS SELECT /*+ LEADING(SAL.DAY) */ SAL.EFFECTIVE_START_DATE EFFECTIVE_START_DATE ,CASE WHEN SAL.EFFECTIVE_START_DATE = TRUNC(SYSDATE) THEN HR_GENERAL.END_OF_TIME ELSE SAL.EFFECTIVE_END_DATE END EFFECTIVE_END_DATE
41 ,SAL.JOB_ID JOB_ID
42 ,SAL.COUNTRY COUNTRY
43 ,SAL.PAY_BASIS_ID PAY_BASIS_ID
44 ,SUM(SAL.HEADCOUNT) TOTAL_HEADCOUNT
45 ,PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY SAL.SALARY) MED_ANL_SLRY
46 ,SAL.CURRENCY_CODE ANL_SLRY_CURRENCY
47 ,ROUND(AVG((SAL.SALARY/CASE WHEN SAL.GRADE_MID_VALUE = 0 THEN 1 ELSE SAL.GRADE_MID_VALUE END)*100), 2) COMPARATIO
48 ,ROUND(AVG(((SAL.SALARY - SAL.GRADE_MIN)/CASE WHEN (SAL.GRADE_MAX-SAL.GRADE_MIN) = 0 THEN 1 ELSE (SAL.GRADE_MAX-SAL.GRADE_MIN) END)*100), 2) RANGE_POSITION
49 ,TRUNC(AVG( CASE WHEN SAL.SALARY <= SAL.GRADE_MIN THEN 0 WHEN SAL.SALARY >= SAL.GRADE_MAX THEN 5 WHEN
50 SAL.SALARY <= (SAL.GRADE_MID_VALUE + SAL.GRADE_MIN)/2 THEN 1 WHEN SAL.SALARY < SAL.GRADE_MID_VALUE THEN 2 WHEN SAL.SALARY >= (SAL.GRADE_MID_VALUE + SAL.GRADE_MAX)/2 THEN 4 WHEN SAL.SALARY >= SAL.GRADE_MID_VALUE THEN 3 END ), 1) QUARTILE
51 FROM
52 ( SELECT DAY.EFFECTIVE_DATE EFFECTIVE_START_DATE
53 , ADD_MONTHS(DAY.EFFECTIVE_DATE, 12) -1 EFFECTIVE_END_DATE
54 , ASG.BUSINESS_GROUP_ID , ASG.ASSIGNMENT_ID
55 , ASG.JOB_ID JOB_ID , LOC.COUNTRY COUNTRY
56 , ASG.PAY_BASIS_ID , ASG.GRADE_ID
57 , PGR.RATE_ID
58 , HRI_BPL_ABV.CALC_ABV( ASG.ASSIGNMENT_ID
59 ,ASG.BUSINESS_GROUP_ID ,'HEAD'
60 ,PRO.CHANGE_DATE) HEADCOUNT
61 ,CASE WHEN PPB.PAY_BASIS = 'HOURLY' or fnd_profile.value('PER_ANNUAL_SALARY_ON_FTE') = 'N'
62 THEN (PPB.PAY_ANNUALIZATION_FACTOR * PRO.PROPOSED_SALARY_N)
63 ELSE (PPB.PAY_ANNUALIZATION_FACTOR * PRO.PROPOSED_SALARY_N)/CASE WHEN PER_SALADMIN_UTILITY.GET_FTE_FACTOR(ASG.ASSIGNMENT_ID ,DAY.EFFECTIVE_DATE) = 0 THEN 1
64 ELSE PER_SALADMIN_UTILITY.GET_FTE_FACTOR(ASG.ASSIGNMENT_ID ,DAY.EFFECTIVE_DATE) END END SALARY
65 ,PGR.CURRENCY_CODE GRADE_CURRENCY
66 ,CASE WHEN PET.INPUT_CURRENCY_CODE IS NOT NULL AND PGR.CURRENCY_CODE IS NOT NULL AND PGR.CURRENCY_CODE <> PET.INPUT_CURRENCY_CODE
67 THEN PER_SALADMIN_UTILITY.GET_CURRENCY_RATE( PGR.CURRENCY_CODE, PET.INPUT_CURRENCY_CODE, DAY.EFFECTIVE_DATE, ASG.BUSINESS_GROUP_ID) * PGR.MINIMUM*PPB.GRADE_ANNUALIZATION_FACTOR
68 ELSE PGR.MINIMUM*PPB.GRADE_ANNUALIZATION_FACTOR END GRADE_MIN
69 ,CASE WHEN PET.INPUT_CURRENCY_CODE IS NOT NULL AND PGR.CURRENCY_CODE IS NOT NULL AND PGR.CURRENCY_CODE <> PET.INPUT_CURRENCY_CODE
70 THEN PER_SALADMIN_UTILITY.GET_CURRENCY_RATE( PGR.CURRENCY_CODE, PET.INPUT_CURRENCY_CODE, DAY.EFFECTIVE_DATE, ASG.BUSINESS_GROUP_ID) * PGR.MAXIMUM*PPB.GRADE_ANNUALIZATION_FACTOR
71 ELSE PGR.MAXIMUM*PPB.GRADE_ANNUALIZATION_FACTOR END GRADE_MAX
72 ,CASE WHEN PET.INPUT_CURRENCY_CODE IS NOT NULL AND PGR.CURRENCY_CODE IS NOT NULL AND PGR.CURRENCY_CODE <> PET.INPUT_CURRENCY_CODE
73 THEN PER_SALADMIN_UTILITY.GET_CURRENCY_RATE( PGR.CURRENCY_CODE, PET.INPUT_CURRENCY_CODE, DAY.EFFECTIVE_DATE, ASG.BUSINESS_GROUP_ID) * PGR.MID_VALUE*PPB.GRADE_ANNUALIZATION_FACTOR
74 ELSE PGR.MID_VALUE*PPB.GRADE_ANNUALIZATION_FACTOR END GRADE_MID_VALUE ,
75 PET.INPUT_CURRENCY_CODE CURRENCY_CODE FROM PER_ALL_ASSIGNMENTS_F ASG ,PER_PAY_BASES PPB ,PER_PAY_PROPOSALS PRO ,PAY_INPUT_VALUES_F PIV ,PAY_ELEMENT_TYPES_F PET ,HR_LOCATIONS_ALL LOC ,PAY_GRADE_RULES_F PGR ,
76 ( SELECT TRUNC(SYSDATE) EFFECTIVE_DATE FROM DUAL UNION ALL SELECT ADD_MONTHS(TRUNC(SYSDATE), -12) EFFECTIVE_DATE FROM DUAL UNION ALL SELECT ADD_MONTHS(TRUNC(SYSDATE), -24) EFFECTIVE_DATE FROM DUAL UNION ALL
77 SELECT ADD_MONTHS(TRUNC(SYSDATE), -36) EFFECTIVE_DATE FROM DUAL UNION ALL
78 SELECT ADD_MONTHS(TRUNC(SYSDATE), -48) EFFECTIVE_DATE FROM DUAL ) DAY WHERE DAY.EFFECTIVE_DATE BETWEEN ASG.EFFECTIVE_START_DATE AND ASG.EFFECTIVE_END_DATE AND ASG.JOB_ID IS NOT NULL
79 AND ASG.GRADE_ID = PGR.GRADE_OR_SPINAL_POINT_ID AND PPB.RATE_ID = PGR.RATE_ID AND ASG.ASSIGNMENT_ID = PRO.ASSIGNMENT_ID AND PRO.CHANGE_DATE = (SELECT MAX(CHANGE_DATE) FROM PER_PAY_PROPOSALS PRO2
80 WHERE PRO2.ASSIGNMENT_ID = PRO.ASSIGNMENT_ID AND PRO2.CHANGE_DATE <= DAY.EFFECTIVE_DATE AND PRO2.APPROVED = 'Y') AND ASG.PAY_BASIS_ID = PPB.PAY_BASIS_ID AND PPB.INPUT_VALUE_ID = PIV.INPUT_VALUE_ID
81 AND PIV.ELEMENT_TYPE_ID = PET.ELEMENT_TYPE_ID AND LOC.LOCATION_ID = ASG.LOCATION_ID
82 AND PRO.CHANGE_DATE BETWEEN PIV.EFFECTIVE_START_DATE AND PIV.EFFECTIVE_END_DATE AND PRO.CHANGE_DATE BETWEEN PET.EFFECTIVE_START_DATE AND PET.EFFECTIVE_END_DATE
83 AND PRO.CHANGE_DATE BETWEEN PGR.EFFECTIVE_START_DATE AND PGR.EFFECTIVE_END_DATE ) SAL
84 GROUP BY SAL.EFFECTIVE_START_DATE ,SAL.EFFECTIVE_END_DATE ,SAL.JOB_ID ,SAL.COUNTRY ,SAL.PAY_BASIS_ID ,SAL.CURRENCY_CODE;
85
86 BEGIN
87
88 l_check_var := PER_SALADMIN_ANALYTICS_PKG.check_refresh_jobs;
89
90 if fnd_global.conc_request_id = -1 and l_check_var = 1 then
91 fnd_message.set_name('PER', 'PER_SAL_ANALYTICS_REFRESH_RUNG');
92 fnd_message.raise_error;
93 end if;
94
95
96
97
98 SELECT FND_GLOBAL.CONC_REQUEST_ID INTO Conc_Prog_Id FROM DUAL;
99
100 PER_SALADMIN_ANALYTICS_PKG.MESSAGE_LOG_PROC('ENTERING PER_SALADMIN_ANALYTICS_PROC',10);
101
102 select s.table_owner||'.'||nvl(ev.table_name, s.table_name) table_name into table_name_var
103 from user_synonyms s, dba_editioning_views ev
104 where synonym_name = 'PER_SALADMIN_ANALYTICS'
105 and ev.owner(+) = s.table_owner
106 and ev.view_name(+) = s.table_name;
107
108 EXECUTE IMMEDIATE 'TRUNCATE TABLE '||trim(table_name_var);
109
110 PER_SALADMIN_ANALYTICS_PKG.insert_refresh_jobs;
111
112 PER_SALADMIN_ANALYTICS_PKG.MESSAGE_LOG_PROC('TRUNCATED TABLE PER_SALADMIN_ANALYTICS TABLE',20);
113
114 OPEN SALANALYTICS;
115
116 LOOP
117
118 FETCH SALANALYTICS BULK COLLECT INTO PSA LIMIT 2000;
119
120 FORALL i IN 1..PSA.COUNT
121 INSERT INTO PER_SALADMIN_ANALYTICS VALUES PSA(i);
122 --INSERT INTO SALANALYTICS (EFFECTIVE_START_DATE,EFFECTIVE_END_DATE,JOB_IDCOUNTRY,PAY_BASIS_ID,TOTAL_HEADCOUNT,MED_ANL_SLRY,ANL_SLRY_CURRENCY,COMPARATIO,RANGE_POSITION,QUARTILE)
123 -- VALUES(PSA.EFFECTIVE_START_DATE,PSA.EFFECTIVE_END_DATE,PSA.JOB_IDCOUNTRY,PSA.PAY_BASIS_ID,PSA.TOTAL_HEADCOUNT,PSA.MED_ANL_SLRY,PSA.ANL_SLRY_CURRENCY,PSA.COMPARATIO,PSA.RANGE_POSITION,PSA.QUARTILE);
124 PER_SALADMIN_ANALYTICS_PKG.MESSAGE_LOG_PROC('INSERTED '||SQL%ROWCOUNT|| ' Rows in to PER_SALADMIN_ANALYTICS TABLE',50);
125 EXIT WHEN SALANALYTICS%NOTFOUND;
126 COMMIT;
127 END LOOP;
128 COMMIT;
129 CLOSE SALANALYTICS;
130
131 PER_SALADMIN_ANALYTICS_PKG.delete_refresh_jobs;
132
133 PER_SALADMIN_ANALYTICS_PKG.MESSAGE_LOG_PROC('GATHERING STATISTICS FOR PER_SALADMIN_ANALYTICS',80);
134 FND_STATS.GATHER_TABLE_STATS('HR','PER_SALADMIN_ANALYTICS');
135 PER_SALADMIN_ANALYTICS_PKG.MESSAGE_LOG_PROC('LEAVING PER_SALADMIN_ANALYTICS_PROC',100);
136 EXCEPTION WHEN OTHERS THEN
137 errbuf := errbuf||SQLERRM;
138 retcode := '1';
139 PER_SALADMIN_ANALYTICS_PKG.MESSAGE_LOG_PROC('Error in PER_SALADMIN_ANALYTICS_PROC: '||SQLCODE,0);
140 PER_SALADMIN_ANALYTICS_PKG.MESSAGE_LOG_PROC(1,0);
141 PER_SALADMIN_ANALYTICS_PKG.MESSAGE_LOG_PROC('Error Msg: '||substr(SQLERRM,1,700),0);
142
143
144 END PER_SALADMIN_ANALYTICS_PROC;
145
146 PROCEDURE MESSAGE_LOG_PROC(MESSAGE IN VARCHAR2,STAGE NUMBER)
147 IS
148 BEGIN
149 IF Conc_Prog_Id = -1 THEN
150 hr_utility.set_location (MESSAGE,STAGE);
151 ELSE
152 FND_FILE.put_line(fnd_file.log,MESSAGE);
153 END IF;
154
155 END MESSAGE_LOG_PROC;
156
157 END PER_SALADMIN_ANALYTICS_PKG;
158
[Home] [Help]