[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT 1 INTO l_check FROM PER_CHANGE_PAY_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_CHANGE_PAY_ANALYTICS (JOB_ID,PAY_BASIS_ID) VALUES (-2,-2);
END insert_refresh_jobs;
procedure delete_refresh_jobs
IS
pragma autonomous_transaction;
delete from PER_CHANGE_PAY_ANALYTICS WHERE NVL(JOB_ID,-1) = -2 AND NVL(PAY_BASIS_ID,-1) = -2;
END delete_refresh_jobs;
SELECT paf.assignment_id
FROM per_all_assignments_f paf, per_assignment_status_types past
WHERE paf.primary_flag = 'Y'
AND TRUNC (SYSDATE) BETWEEN paf.effective_start_date AND paf.effective_end_date
AND paf.assignment_type IN ('E','C')
AND paf.assignment_status_type_id = past.assignment_status_type_id
AND past.per_system_status <>'TERM_ASSIGN' ;
SELECT FND_GLOBAL.CONC_REQUEST_ID INTO Conc_Prog_Id FROM DUAL;
select s.table_owner||'.'||nvl(ev.table_name, s.table_name) into table_name_var
from user_synonyms s, dba_editioning_views ev
where synonym_name = 'PER_CHANGE_PAY_ANALYTICS'
and ev.owner(+) = s.table_owner
and ev.view_name(+) = s.table_name;
PER_CHANGE_PAY_ANALYTICS_PKG.insert_refresh_jobs;
OPEN PAYCHANGE FOR SELECT
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,
sal.supervisor_id supervisor_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,
SUPH.SUP_PERSON_ID SUPERVISOR_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' 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 ,
(SELECT level-1 SUB_RELATIVE_LEVEL,
paf.assignment_id SUB_ASSIGNMENT_ID,
paf.person_id SUB_PERSON_ID,
paf.supervisor_id SUP_PERSON_ID,
paf.EFFECTIVE_START_DATE EFFECTIVE_START_DATE,
paf.EFFECTIVE_END_DATE EFFECTIVE_END_DATE
FROM per_all_assignments_f paf
WHERE paf.primary_flag = 'Y'
AND TRUNC (SYSDATE) BETWEEN paf.effective_start_date AND
paf.effective_end_date
AND paf.assignment_type IN ('E','C')
START WITH paf.assignment_id = EMP_ASSIGNMENT_ID
CONNECT BY NOCYCLE PRIOR paf.person_id = nvl(paf.supervisor_id,-1))
SUPH ,
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 SUPH.SUB_PERSON_ID = ASG.PERSON_ID
AND SUPH.SUB_RELATIVE_LEVEL <> 0
AND DAY.EFFECTIVE_DATE BETWEEN SUPH.EFFECTIVE_START_DATE AND SUPH.EFFECTIVE_END_DATE
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 CHANGE_DATE
FROM PER_PAY_PROPOSALS PRO2
WHERE PRO2.ASSIGNMENT_ID = PRO.ASSIGNMENT_ID
AND DAY.EFFECTIVE_DATE BETWEEN PRO2.CHANGE_DATE AND PRO2.DATE_TO
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.SUPERVISOR_ID,
SAL.JOB_ID ,
SAL.COUNTRY ,
SAL.PAY_BASIS_ID,
SAL.CURRENCY_CODE;
INSERT INTO PER_CHANGE_PAY_ANALYTICS VALUES PSA(i);
PER_CHANGE_PAY_ANALYTICS_PKG.MESSAGE_LOG_PROC('INSERTED '||SQL%ROWCOUNT|| ' Rows in to PER_CHANGE_PAY_ANALYTICS TABLE',50);
PER_CHANGE_PAY_ANALYTICS_PKG.delete_refresh_jobs;