View: PAY_SG_EOY_BALANCES_V

Product:	PAY - Payroll
Description:	Singapore localisation view.
Implementation/DBA Data:	APPS.PAY_SG_EOY_BALANCES_V

View Text

SELECT PPA.BUSINESS_GROUP_ID
, PAAF.PERSON_ID
, PAA.ASSIGNMENT_ACTION_ID
, PAA.ASSIGNMENT_ID
, PAA.TAX_UNIT_ID
, PAA.ACTION_SEQUENCE
, PPA.DATE_EARNED
, PPA.EFFECTIVE_DATE
, TO_CHAR(PPA.EFFECTIVE_DATE
, 'YYYY') YEAR
, PTP.PERIOD_NAME
, PBTTL.BALANCE_NAME
, PBTTL.REPORTING_NAME
, PBT.BALANCE_TYPE_ID
, SUM(DECODE(PBD.DATABASE_ITEM_SUFFIX
, '_PER_LE_RUN'
, PAY_BALANCE_PKG.GET_VALUE(PDB.DEFINED_BALANCE_ID
, PAA.ASSIGNMENT_ACTION_ID
, PAA.TAX_UNIT_ID
, NULL
, NULL
, NULL
, NULL )
, 0)) PER_RUN_BAL
, SUM(DECODE(PBD.DATABASE_ITEM_SUFFIX
, '_PER_LE_YTD'
, PAY_BALANCE_PKG.GET_VALUE(PDB.DEFINED_BALANCE_ID
, PAA.ASSIGNMENT_ACTION_ID
, PAA.TAX_UNIT_ID
, NULL
, NULL
, NULL
, NULL )
, 0)) PER_YTD_BAL
FROM PAY_BALANCE_TYPES PBT
, PAY_BALANCE_TYPES_TL PBTTL
, PAY_BALANCE_DIMENSIONS PBD
, PAY_DEFINED_BALANCES PDB
, PER_ASSIGNMENTS_F PAAF
, PAY_ASSIGNMENT_ACTIONS PAA
, PAY_PAYROLL_ACTIONS PPA
, PER_TIME_PERIODS PTP
WHERE ((PBT.LEGISLATION_CODE IS NULL
AND PBT.BUSINESS_GROUP_ID = PPA.BUSINESS_GROUP_ID) OR PBT.LEGISLATION_CODE = 'SG')
AND PPA.EFFECTIVE_DATE BETWEEN PAAF.EFFECTIVE_START_DATE
AND PAAF.EFFECTIVE_END_DATE
AND PAA.ASSIGNMENT_ID = PAAF.ASSIGNMENT_ID
AND ((PBT.BALANCE_NAME LIKE 'IR21_%' ESCAPE '\'
AND PBD.DATABASE_ITEM_SUFFIX = '_PER_LE_YTD') OR (PBT.BALANCE_NAME = 'A8A_MOA_501'
AND PBD.DATABASE_ITEM_SUFFIX = '_PER_LE_YTD') OR (PBT.BALANCE_NAME = 'A8A_MOA_502'
AND PBD.DATABASE_ITEM_SUFFIX = '_PER_LE_YTD') OR (PBT.BALANCE_NAME = 'A8A_MOA_539'
AND PBD.DATABASE_ITEM_SUFFIX = '_PER_LE_YTD') OR (PBT.BALANCE_NAME = 'GROSS PAY'
AND PBD.DATABASE_ITEM_SUFFIX = '_PER_LE_RUN'))
AND PBT.BALANCE_TYPE_ID = PBTTL.BALANCE_TYPE_ID
AND PBTTL.LANGUAGE = USERENV('LANG')
AND PPA.ACTION_TYPE IN ('R'
, 'B'
, 'I'
, 'Q')
AND PDB.BALANCE_TYPE_ID = PBT.BALANCE_TYPE_ID
AND PDB.BALANCE_DIMENSION_ID = PBD.BALANCE_DIMENSION_ID
AND PPA.TIME_PERIOD_ID = PTP.TIME_PERIOD_ID
AND PAA.PAYROLL_ACTION_ID = PPA.PAYROLL_ACTION_ID
AND PAA.ACTION_SEQUENCE = (SELECT MAX(PAA2.ACTION_SEQUENCE)
FROM PAY_ASSIGNMENT_ACTIONS PAA2
, PAY_PAYROLL_ACTIONS PPA2
, PER_ASSIGNMENTS_F PAAF2
WHERE PPA2.BUSINESS_GROUP_ID = PPA.BUSINESS_GROUP_ID
AND PAA2.TAX_UNIT_ID = PAA.TAX_UNIT_ID
AND PAAF2.PERSON_ID = PAAF.PERSON_ID
AND PAA2.ASSIGNMENT_ID = PAAF2.ASSIGNMENT_ID
AND PAA2.PAYROLL_ACTION_ID = PPA2.PAYROLL_ACTION_ID
AND TO_CHAR(PPA2.EFFECTIVE_DATE
, 'YYYY') = TO_CHAR(PPA.EFFECTIVE_DATE
, 'YYYY')
AND PPA2.ACTION_TYPE IN ('R'
, 'B'
, 'I'
, 'Q') ) GROUP BY PPA.BUSINESS_GROUP_ID
, PAAF.PERSON_ID
, PAA.ASSIGNMENT_ACTION_ID
, PAA.ASSIGNMENT_ID
, PAA.TAX_UNIT_ID
, PAA.ACTION_SEQUENCE
, PPA.DATE_EARNED
, PPA.EFFECTIVE_DATE
, TO_CHAR(PPA.EFFECTIVE_DATE
, 'YYYY')
, PTP.PERIOD_NAME
, PBTTL.BALANCE_NAME
, PBTTL.REPORTING_NAME
, PBT.BALANCE_TYPE_ID

Columns