This query selects the RATE_AMOUNT from CMP_SALARY_PAY_COMPONENTS and rounds the value to two decimal places using the ROUND function. It joins PAY_RATE_DEFINITIONS_F to identify the pay rate definition associated with the salary component. The query also joins PER_ALL_ASSIGNMENTS_M to link the salary pay component with an employee assignment. It filters records to ensure the assignment is an active primary employee assignment with the latest effective change. Additionally, effective date conditions using SYSDATE ensure that the salary component, rate definition, and assignment records are currently valid. The result returns the rounded salary pay component rate amount.
SQL Query Section
SELECT
ROUND(cmpcc2.Rate_Amount,2)
FROM
PAY_RATE_DEFINITIONS_F prdff1 ,
cmp_salary_pay_components cmpcc2 ,
per_all_assignments_m paamm3
WHERE
TRUNC(SYSDATE) BETWEEN cmpcc2.SALARY_DATE_FROM AND NVL(cmpcc2.SALARY_DATE_TO,TO_DATE('31-12-4712','DD-MM-YYYY'))
AND prdff1.rate_definition_id=cmpcc2.pay_rate_definition_id
--AND prdff1.base_name like ('%Thirteenth Month Pay%')
AND cmpcc2.assignment_id = paamm3.assignment_id
AND paamm3.assignment_type in ('E')
AND paamm3.effective_latest_change = 'Y'
AND paamm3.assignment_status_type = 'ACTIVE'
AND paamm3.primary_assignment_flag = 'Y'
--AND cmpcc2.assignment_id = TO_NUMBER(:{PARAMETER.P_ASSIGNMENT_ID})
AND TRUNC(SYSDATE) BETWEEN prdff1.effective_start_date AND prdff1.effective_end_date
AND TRUNC(SYSDATE) BETWEEN paamm3.effective_start_date AND paamm3.effective_end_date
Output Results
Related Posts
SQL Query to Pull W2
Posted on
