[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT 1 INTO l_check FROM PER_SALADMIN_ANALYTICS WHERE NVL(JOB_ID,-1) = -2 AND NVL(PAY_BASIS_ID,-1) = -2;
procedure insert_refresh_jobs
IS
pragma autonomous_transaction;
INSERT INTO PER_SALADMIN_ANALYTICS (JOB_ID,PAY_BASIS_ID) VALUES (-2,-2);
END insert_refresh_jobs;
procedure delete_refresh_jobs
IS
pragma autonomous_transaction;
delete from PER_SALADMIN_ANALYTICS WHERE NVL(JOB_ID,-1) = -2 AND NVL(PAY_BASIS_ID,-1) = -2;
END delete_refresh_jobs;
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
,SAL.JOB_ID JOB_ID
,SAL.COUNTRY COUNTRY
,SAL.PAY_BASIS_ID PAY_BASIS_ID
,SUM(SAL.HEADCOUNT) TOTAL_HEADCOUNT
,PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY SAL.SALARY) MED_ANL_SLRY
,SAL.CURRENCY_CODE ANL_SLRY_CURRENCY
,ROUND(AVG((SAL.SALARY/CASE WHEN SAL.GRADE_MID_VALUE = 0 THEN 1 ELSE SAL.GRADE_MID_VALUE END)*100), 2) COMPARATIO
,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
,TRUNC(AVG( CASE WHEN SAL.SALARY <= SAL.GRADE_MIN THEN 0 WHEN SAL.SALARY >= SAL.GRADE_MAX THEN 5 WHEN
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
FROM
( SELECT DAY.EFFECTIVE_DATE EFFECTIVE_START_DATE
, ADD_MONTHS(DAY.EFFECTIVE_DATE, 12) -1 EFFECTIVE_END_DATE
, ASG.BUSINESS_GROUP_ID , ASG.ASSIGNMENT_ID
, ASG.JOB_ID JOB_ID , LOC.COUNTRY COUNTRY
, ASG.PAY_BASIS_ID , ASG.GRADE_ID
, PGR.RATE_ID
, HRI_BPL_ABV.CALC_ABV( ASG.ASSIGNMENT_ID
,ASG.BUSINESS_GROUP_ID ,'HEAD'
,PRO.CHANGE_DATE) HEADCOUNT
,CASE WHEN PPB.PAY_BASIS = 'HOURLY' or fnd_profile.value('PER_ANNUAL_SALARY_ON_FTE') = 'N'
THEN (PPB.PAY_ANNUALIZATION_FACTOR * PRO.PROPOSED_SALARY_N)
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
ELSE PER_SALADMIN_UTILITY.GET_FTE_FACTOR(ASG.ASSIGNMENT_ID ,DAY.EFFECTIVE_DATE) END END SALARY
,PGR.CURRENCY_CODE GRADE_CURRENCY
,CASE WHEN PET.INPUT_CURRENCY_CODE IS NOT NULL AND PGR.CURRENCY_CODE IS NOT NULL AND PGR.CURRENCY_CODE <> PET.INPUT_CURRENCY_CODE
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
ELSE PGR.MINIMUM*PPB.GRADE_ANNUALIZATION_FACTOR END GRADE_MIN
,CASE WHEN PET.INPUT_CURRENCY_CODE IS NOT NULL AND PGR.CURRENCY_CODE IS NOT NULL AND PGR.CURRENCY_CODE <> PET.INPUT_CURRENCY_CODE
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
ELSE PGR.MAXIMUM*PPB.GRADE_ANNUALIZATION_FACTOR END GRADE_MAX
,CASE WHEN PET.INPUT_CURRENCY_CODE IS NOT NULL AND PGR.CURRENCY_CODE IS NOT NULL AND PGR.CURRENCY_CODE <> PET.INPUT_CURRENCY_CODE
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
ELSE PGR.MID_VALUE*PPB.GRADE_ANNUALIZATION_FACTOR END GRADE_MID_VALUE ,
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 ,
( 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
SELECT ADD_MONTHS(TRUNC(SYSDATE), -36) EFFECTIVE_DATE FROM DUAL UNION ALL
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
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
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
AND PIV.ELEMENT_TYPE_ID = PET.ELEMENT_TYPE_ID AND LOC.LOCATION_ID = ASG.LOCATION_ID
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
AND PRO.CHANGE_DATE BETWEEN PGR.EFFECTIVE_START_DATE AND PGR.EFFECTIVE_END_DATE ) SAL
GROUP BY SAL.EFFECTIVE_START_DATE ,SAL.EFFECTIVE_END_DATE ,SAL.JOB_ID ,SAL.COUNTRY ,SAL.PAY_BASIS_ID ,SAL.CURRENCY_CODE;
SELECT FND_GLOBAL.CONC_REQUEST_ID INTO Conc_Prog_Id FROM DUAL;
select s.table_owner||'.'||nvl(ev.table_name, s.table_name) table_name into table_name_var
from user_synonyms s, dba_editioning_views ev
where synonym_name = 'PER_SALADMIN_ANALYTICS'
and ev.owner(+) = s.table_owner
and ev.view_name(+) = s.table_name;
PER_SALADMIN_ANALYTICS_PKG.insert_refresh_jobs;
INSERT INTO PER_SALADMIN_ANALYTICS VALUES PSA(i);
--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)
-- 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);
PER_SALADMIN_ANALYTICS_PKG.MESSAGE_LOG_PROC('INSERTED '||SQL%ROWCOUNT|| ' Rows in to PER_SALADMIN_ANALYTICS TABLE',50);
PER_SALADMIN_ANALYTICS_PKG.delete_refresh_jobs;