VIEW: APPS.PAY_NZ_ER_CAL_MTH_BAL_V

Source

View Text - Preformatted

SELECT b.organization_id ,b.effective_date ,sum(gross_earnings) ,sum(earnings_not_liable_for_acc_ep) ,sum(paye_deductions) ,sum(child_support_deductions) ,sum(student_loan_deductions) ,sum(kiwisaver_ee_contributions) /* Added for bug 5846247 */ ,sum(kiwisaver_er_contributions) /* Added for bug 5846247 */ from pay_nz_asg_cal_mth_bal_v b, pay_element_types_f et, pay_assignment_actions assact, fnd_sessions s, per_assignments_f a, pay_run_results prr, pay_payroll_actions pact where (gross_earnings-(paye_deductions+child_support_deductions+student_loan_deductions)) >= 0 and et.element_name in ('PAYE Information', 'Withholding Tax Information Record') and et.effective_start_date <= last_day(s.effective_date) and et.effective_end_date >= to_date('01/' || to_char(s.effective_date, 'mm/yyyy'), 'dd/mm/yyyy') and et.effective_start_date = (select max(et2.effective_start_date) from pay_element_types_f et2 where et2.element_type_id = et.element_type_id and et2.effective_start_date <= last_day(s.effective_date) ) and b.assignment_id = a.assignment_id and a.effective_start_date <= last_day(s.effective_date) and a.effective_end_date >= to_date('01/' || to_char(s.effective_date, 'mm/yyyy'), 'dd/mm/yyyy') and a.effective_start_date = (select max(a2.effective_start_date) from per_assignments_f a2 where a2.person_id = a.person_id and a2.assignment_id = a.assignment_id and a2.effective_start_date <= last_day(s.effective_date) ) and assact.assignment_action_id = ( select max(assact4.assignment_action_id) from pay_assignment_actions assact4 ,pay_payroll_actions pact4 ,pay_run_results prr4 where assact4.assignment_id = a.assignment_id and assact4.assignment_action_id = prr4.assignment_action_id and pact4.payroll_action_id = assact4.payroll_action_id and pact4.effective_date between to_date('01/' || to_char(s.effective_date, 'mm/yyyy'), 'dd/mm/yyyy') and last_day(s.effective_date) and prr4.element_type_id = et.element_type_id ) and prr.element_type_id = et.element_type_id and prr.assignment_action_id = assact.assignment_action_id and pact.payroll_action_id = assact.payroll_action_id and assact.assignment_id = a.assignment_id and s.session_id = userenv('SESSIONID') and gross_earnings >= 0 and earnings_not_liable_for_acc_ep >= 0 and paye_deductions >= 0 and child_support_deductions >= 0 and student_loan_deductions >= 0 group by b.organization_id, b.effective_date

View Text - HTML Formatted

SELECT B.ORGANIZATION_ID
, B.EFFECTIVE_DATE
, SUM(GROSS_EARNINGS)
, SUM(EARNINGS_NOT_LIABLE_FOR_ACC_EP)
, SUM(PAYE_DEDUCTIONS)
, SUM(CHILD_SUPPORT_DEDUCTIONS)
, SUM(STUDENT_LOAN_DEDUCTIONS)
, SUM(KIWISAVER_EE_CONTRIBUTIONS) /* ADDED FOR BUG 5846247 */
, SUM(KIWISAVER_ER_CONTRIBUTIONS) /* ADDED FOR BUG 5846247 */
FROM PAY_NZ_ASG_CAL_MTH_BAL_V B
, PAY_ELEMENT_TYPES_F ET
, PAY_ASSIGNMENT_ACTIONS ASSACT
, FND_SESSIONS S
, PER_ASSIGNMENTS_F A
, PAY_RUN_RESULTS PRR
, PAY_PAYROLL_ACTIONS PACT
WHERE (GROSS_EARNINGS-(PAYE_DEDUCTIONS+CHILD_SUPPORT_DEDUCTIONS+STUDENT_LOAN_DEDUCTIONS)) >= 0
AND ET.ELEMENT_NAME IN ('PAYE INFORMATION'
, 'WITHHOLDING TAX INFORMATION RECORD')
AND ET.EFFECTIVE_START_DATE <= LAST_DAY(S.EFFECTIVE_DATE)
AND ET.EFFECTIVE_END_DATE >= TO_DATE('01/' || TO_CHAR(S.EFFECTIVE_DATE
, 'MM/YYYY')
, 'DD/MM/YYYY')
AND ET.EFFECTIVE_START_DATE = (SELECT MAX(ET2.EFFECTIVE_START_DATE)
FROM PAY_ELEMENT_TYPES_F ET2
WHERE ET2.ELEMENT_TYPE_ID = ET.ELEMENT_TYPE_ID
AND ET2.EFFECTIVE_START_DATE <= LAST_DAY(S.EFFECTIVE_DATE) )
AND B.ASSIGNMENT_ID = A.ASSIGNMENT_ID
AND A.EFFECTIVE_START_DATE <= LAST_DAY(S.EFFECTIVE_DATE)
AND A.EFFECTIVE_END_DATE >= TO_DATE('01/' || TO_CHAR(S.EFFECTIVE_DATE
, 'MM/YYYY')
, 'DD/MM/YYYY')
AND A.EFFECTIVE_START_DATE = (SELECT MAX(A2.EFFECTIVE_START_DATE)
FROM PER_ASSIGNMENTS_F A2
WHERE A2.PERSON_ID = A.PERSON_ID
AND A2.ASSIGNMENT_ID = A.ASSIGNMENT_ID
AND A2.EFFECTIVE_START_DATE <= LAST_DAY(S.EFFECTIVE_DATE) )
AND ASSACT.ASSIGNMENT_ACTION_ID = ( SELECT MAX(ASSACT4.ASSIGNMENT_ACTION_ID)
FROM PAY_ASSIGNMENT_ACTIONS ASSACT4
, PAY_PAYROLL_ACTIONS PACT4
, PAY_RUN_RESULTS PRR4
WHERE ASSACT4.ASSIGNMENT_ID = A.ASSIGNMENT_ID
AND ASSACT4.ASSIGNMENT_ACTION_ID = PRR4.ASSIGNMENT_ACTION_ID
AND PACT4.PAYROLL_ACTION_ID = ASSACT4.PAYROLL_ACTION_ID
AND PACT4.EFFECTIVE_DATE BETWEEN TO_DATE('01/' || TO_CHAR(S.EFFECTIVE_DATE
, 'MM/YYYY')
, 'DD/MM/YYYY')
AND LAST_DAY(S.EFFECTIVE_DATE)
AND PRR4.ELEMENT_TYPE_ID = ET.ELEMENT_TYPE_ID )
AND PRR.ELEMENT_TYPE_ID = ET.ELEMENT_TYPE_ID
AND PRR.ASSIGNMENT_ACTION_ID = ASSACT.ASSIGNMENT_ACTION_ID
AND PACT.PAYROLL_ACTION_ID = ASSACT.PAYROLL_ACTION_ID
AND ASSACT.ASSIGNMENT_ID = A.ASSIGNMENT_ID
AND S.SESSION_ID = USERENV('SESSIONID')
AND GROSS_EARNINGS >= 0
AND EARNINGS_NOT_LIABLE_FOR_ACC_EP >= 0
AND PAYE_DEDUCTIONS >= 0
AND CHILD_SUPPORT_DEDUCTIONS >= 0
AND STUDENT_LOAN_DEDUCTIONS >= 0 GROUP BY B.ORGANIZATION_ID
, B.EFFECTIVE_DATE