View: PAY_NZ_ASG_CAL_MTH_BAL_V

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

View Text

SELECT ASG.ASSIGNMENT_ID
, SCL.SEGMENT1
, SES.EFFECTIVE_DATE
, FLOOR(SUM(DECODE(PBT.BALANCE_NAME
, 'GROSS EARNINGS'
, TO_NUMBER(TARGET.RESULT_VALUE)* FEED.SCALE
, 'WITHHOLDING PAYMENTS RECORD'
, TO_NUMBER(TARGET.RESULT_VALUE) * FEED.SCALE
, 'RETRO ORDINARY TAXABLE EARNINGS'
, TO_NUMBER(TARGET.RESULT_VALUE) * FEED.SCALE
, 0)))
, FLOOR(SUM(DECODE(PBT.BALANCE_NAME
, 'EARNINGS NOT LIABLE FOR ACC EP'
, TO_NUMBER(TARGET.RESULT_VALUE)
, 'RETRO EARNINGS NOT LIABLE FOR ACC EP'
, TO_NUMBER(TARGET.RESULT_VALUE)
, 0)))
, SUBSTR(PAY_NZ_TAX.EXTRA_EMOL_AT_LOW_TAX_RATE(ASG.ASSIGNMENT_ID
, SES.EFFECTIVE_DATE)
, 1
, 1)
, SUM(DECODE(PBT.BALANCE_NAME
, 'PAYE TAX DEDUCTIONS'
, TO_NUMBER(TARGET.RESULT_VALUE)
, 'WITHHOLDING TAX DEDUCTIONS RECORD'
, TO_NUMBER(TARGET.RESULT_VALUE) * FEED.SCALE
, 'RETRO PAYE TAX DEDUCTIONS'
, TO_NUMBER(TARGET.RESULT_VALUE) * FEED.SCALE
, 0))
, SUM(DECODE(PBT.BALANCE_NAME
, 'CHILD SUPPORT DEDUCTIONS'
, TO_NUMBER(TARGET.RESULT_VALUE) * FEED.SCALE
, 0))
, SUBSTR(PAY_NZ_TAX.CHILD_SUPPORT_CODE(ASG.ASSIGNMENT_ID
, SES.EFFECTIVE_DATE)
, 1
, 1)
, SUM(DECODE(PBT.BALANCE_NAME
, 'STUDENT LOAN DEDUCTIONS'
, TO_NUMBER(TARGET.RESULT_VALUE) * FEED.SCALE
, 'RETRO STUDENT LOAN DEDUCTIONS'
, TO_NUMBER(TARGET.RESULT_VALUE) * FEED.SCALE
, 0)) /* ADDED FOR BUG 5846247 */
, SUM(DECODE( PBT.BALANCE_NAME
, 'KIWISAVER EMPLOYEE CONTRIBUTIONS'
, TO_NUMBER(TARGET.RESULT_VALUE) * FEED.SCALE
, 0))
, SUM(DECODE( PBT.BALANCE_NAME
, 'KIWISAVER EMPLOYER CONTRIBUTIONS'
, TO_NUMBER(TARGET.RESULT_VALUE) * FEED.SCALE
, 0))
FROM HR_SOFT_CODING_KEYFLEX SCL
, PER_ALL_ASSIGNMENTS_F ASG
, PAY_BALANCE_TYPES PBT
, PAY_RUN_RESULT_VALUES TARGET
, PAY_RUN_RESULTS RR
, PAY_BALANCE_FEEDS_F FEED
, PAY_ASSIGNMENT_ACTIONS ASSACT
, PAY_PAYROLL_ACTIONS PACT
, FND_SESSIONS SES
, PAY_INPUT_VALUES_F PIV
WHERE SES.SESSION_ID = USERENV('SESSIONID')
AND FEED.INPUT_VALUE_ID = TARGET.INPUT_VALUE_ID
AND FEED.BALANCE_TYPE_ID = PBT.BALANCE_TYPE_ID
AND PBT.LEGISLATION_CODE = 'NZ'
AND TARGET.INPUT_VALUE_ID = PIV.INPUT_VALUE_ID
AND PIV.INPUT_VALUE_ID = FEED.INPUT_VALUE_ID
AND FEED.EFFECTIVE_START_DATE <= LAST_DAY(SES.EFFECTIVE_DATE)
AND FEED.EFFECTIVE_END_DATE >= TO_DATE('01/' || TO_CHAR(SES.EFFECTIVE_DATE
, 'MM/YYYY')
, 'DD/MM/YYYY')
AND FEED.EFFECTIVE_START_DATE = (SELECT MAX(FEED2.EFFECTIVE_START_DATE)
FROM PAY_BALANCE_FEEDS_F FEED2
WHERE FEED2.INPUT_VALUE_ID = FEED.INPUT_VALUE_ID
AND FEED2.BALANCE_TYPE_ID = FEED.BALANCE_TYPE_ID
AND FEED2.EFFECTIVE_START_DATE <= LAST_DAY(SES.EFFECTIVE_DATE) )
AND TARGET.RESULT_VALUE <> '0'
AND TARGET.RUN_RESULT_ID = RR.RUN_RESULT_ID
AND RR.ASSIGNMENT_ACTION_ID = ASSACT.ASSIGNMENT_ACTION_ID
AND ASSACT.PAYROLL_ACTION_ID = PACT.PAYROLL_ACTION_ID
AND PACT.EFFECTIVE_DATE BETWEEN FEED.EFFECTIVE_START_DATE
AND FEED.EFFECTIVE_END_DATE
AND PACT.EFFECTIVE_DATE BETWEEN TO_DATE('01/' || TO_CHAR(SES.EFFECTIVE_DATE
, 'MM/YYYY')
, 'DD/MM/YYYY')
AND LAST_DAY(SES.EFFECTIVE_DATE)
AND RR.STATUS IN ('P'
, 'PA')
AND ASG.ASSIGNMENT_ID = ASSACT.ASSIGNMENT_ID
AND ASG.EFFECTIVE_START_DATE <= LAST_DAY(SES.EFFECTIVE_DATE)
AND ASG.EFFECTIVE_END_DATE >= TO_DATE('01/' || TO_CHAR(SES.EFFECTIVE_DATE
, 'MM/YYYY')
, 'DD/MM/YYYY')
AND ASG.EFFECTIVE_START_DATE = (SELECT MAX(ASG2.EFFECTIVE_START_DATE)
FROM PER_ALL_ASSIGNMENTS_F ASG2
WHERE ASG2.ASSIGNMENT_ID = ASG.ASSIGNMENT_ID
AND ASG2.EFFECTIVE_START_DATE <= LAST_DAY(SES.EFFECTIVE_DATE) )
AND SCL.SOFT_CODING_KEYFLEX_ID = ASG.SOFT_CODING_KEYFLEX_ID
AND PBT.BALANCE_NAME IN ('GROSS EARNINGS'
, 'EARNINGS NOT LIABLE FOR ACC EP'
, 'PAYE TAX DEDUCTIONS'
, 'CHILD SUPPORT DEDUCTIONS'
, 'STUDENT LOAN DEDUCTIONS'
, 'WITHHOLDING PAYMENTS RECORD'
, 'WITHHOLDING TAX DEDUCTIONS RECORD'
, 'RETRO ORDINARY TAXABLE EARNINGS'
, 'RETRO PAYE TAX DEDUCTIONS'
, 'RETRO STUDENT LOAN DEDUCTIONS'
, 'RETRO EARNINGS NOT LIABLE FOR ACC EP' /*ADDED FOR BUG 5846247 */
, 'KIWISAVER EMPLOYEE CONTRIBUTIONS'
, 'KIWISAVER EMPLOYER CONTRIBUTIONS') GROUP BY ASG.ASSIGNMENT_ID
, SCL.SEGMENT1
, SES.EFFECTIVE_DATE

Columns