View: PAY_NZ_ER_CAL_MTH_BAL_V

Product:	PAY - Payroll
Description:	New Zealand localisation view.
Implementation/DBA Data:	APPS.PAY_NZ_ER_CAL_MTH_BAL_V

View Text

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

Columns