View: PAY_SG_PAYSGNSL_BAL_BY_DATE_V

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

View Text

SELECT HOU.BUSINESS_GROUP_ID BUSINESS_GROUP_ID
, HOU.ORGANIZATION_ID ORGANIZATION_ID
, HOI.ORG_INFORMATION2 CPF_NO
, PAF.ASSIGNMENT_ID ASSIGNMENT_ID
, PAF.PERSON_ID PERSON_ID
, PAF.EFFECTIVE_START_DATE ASG_EFFECTIVE_START_DATE
, PAF.EFFECTIVE_END_DATE ASG_EFFECTIVE_END_DATE
, NVL(PPOS.NAME
, PJT.NAME) POSITION_NAME
, SUBSTR(PETF.ELEMENT_NAME
, 1
, 50) ELEMENT_NAME
, PEEF.EFFECTIVE_START_DATE ELE_EFFECTIVE_START_DATE
, PEEF.EFFECTIVE_END_DATE ELE_EFFECTIVE_END_DATE
, PESM.ELEMENT_SET_ID ELEMENT_SET_ID
, PAA.ACTION_SEQUENCE ACTION_SEQUENCE
, PAY_BALANCE_PKG.GET_VALUE(PDB.DEFINED_BALANCE_ID
, PAA.ASSIGNMENT_ACTION_ID
, PAA.TAX_UNIT_ID
, NULL
, NULL
, NULL
, NULL ) VALUE
, PPA.EFFECTIVE_DATE EFFECTIVE_DATE
, PAY_SG_EXC.GET_EXPIRY_DATE(PDB.DEFINED_BALANCE_ID
, PAA.ASSIGNMENT_ACTION_ID) EXPIRY_DATE
FROM HR_ORGANIZATION_UNITS HOU
, HR_ORGANIZATION_INFORMATION HOI
, PER_POSITIONS PPOS
, PER_JOBS_TL PJT
, HR_SOFT_CODING_KEYFLEX HSCK
, PAY_ELEMENT_TYPES_F PETF
, PER_ASSIGNMENTS_F PAF
, PAY_PAYROLL_ACTIONS PPA
, PAY_BALANCE_TYPES PBT
, PAY_BALANCE_TYPES_TL PBTTL
, PAY_BALANCE_DIMENSIONS PBD
, PAY_DEFINED_BALANCES PDB
, PAY_ASSIGNMENT_ACTIONS PAA
, (SELECT PECR1.ELEMENT_SET_ID
, PETF1.ELEMENT_TYPE_ID
, PETF1.EFFECTIVE_START_DATE
, PETF1.EFFECTIVE_END_DATE
FROM PAY_ELEMENT_TYPES_F PETF1
, PAY_ELE_CLASSIFICATION_RULES PECR1
, PAY_ELEMENT_SETS PES1
, HR_LOOKUPS HL1
WHERE PETF1.CLASSIFICATION_ID = PECR1.CLASSIFICATION_ID
AND PECR1.ELEMENT_SET_ID = PES1.ELEMENT_SET_ID
AND PES1.ELEMENT_SET_TYPE = 'R'
AND HL1.LOOKUP_TYPE = 'PROCESSING_TYPE'
AND HL1.LOOKUP_CODE = PETF1.PROCESSING_TYPE
AND NOT EXISTS (SELECT 1
FROM PAY_ELEMENT_TYPE_RULES PETR1
WHERE PETR1.ELEMENT_SET_ID = PECR1.ELEMENT_SET_ID
AND PETR1.ELEMENT_TYPE_ID = PETF1.ELEMENT_TYPE_ID
AND PETR1.INCLUDE_OR_EXCLUDE = 'E') UNION SELECT PETR2.ELEMENT_SET_ID
, PETF2.ELEMENT_TYPE_ID
, PETF2.EFFECTIVE_START_DATE
, PETF2.EFFECTIVE_END_DATE
FROM PAY_ELEMENT_TYPES_F PETF2
, PAY_ELEMENT_TYPE_RULES PETR2
, HR_LOOKUPS HL2
WHERE PETR2.INCLUDE_OR_EXCLUDE = 'I'
AND PETF2.ELEMENT_TYPE_ID = PETR2.ELEMENT_TYPE_ID
AND HL2.LOOKUP_TYPE = 'PROCESSING_TYPE'
AND HL2.LOOKUP_CODE = PETF2.PROCESSING_TYPE ) PESM
, PAY_ELEMENT_ENTRIES_F PEEF
, PAY_ELEMENT_LINKS_F PELF
WHERE HOI.ORG_INFORMATION_CONTEXT||'' = 'SG_LEGAL_ENTITY'
AND PBD.DIMENSION_NAME = '_ASG_MONTH'
AND ((PBT.LEGISLATION_CODE IS NULL
AND PBT.BUSINESS_GROUP_ID = PPA.BUSINESS_GROUP_ID) OR (PBT.LEGISLATION_CODE = 'SG'
AND PBT.BUSINESS_GROUP_ID IS NULL))
AND ((PETF.LEGISLATION_CODE IS NULL
AND PETF.BUSINESS_GROUP_ID = PPA.BUSINESS_GROUP_ID) OR (PETF.LEGISLATION_CODE = 'SG'
AND PETF.BUSINESS_GROUP_ID IS NULL))
AND PBTTL.LANGUAGE = USERENV('LANG')
AND HOU.ORGANIZATION_ID = HOI.ORGANIZATION_ID
AND TO_CHAR(HOU.ORGANIZATION_ID) = HSCK.SEGMENT1
AND HSCK.SOFT_CODING_KEYFLEX_ID = PAF.SOFT_CODING_KEYFLEX_ID
AND PAF.POSITION_ID = PPOS.POSITION_ID (+)
AND PAF.JOB_ID = PJT.JOB_ID (+)
AND PJT.LANGUAGE (+) = USERENV('LANG')
AND PPA.EFFECTIVE_DATE BETWEEN PETF.EFFECTIVE_START_DATE
AND PETF.EFFECTIVE_END_DATE
AND PETF.ELEMENT_TYPE_ID = PESM.ELEMENT_TYPE_ID
AND PETF.EFFECTIVE_START_DATE = PESM.EFFECTIVE_START_DATE
AND PETF.EFFECTIVE_END_DATE = PESM.EFFECTIVE_END_DATE
AND TO_CHAR(PBT.BALANCE_TYPE_ID) = PETF.ELEMENT_INFORMATION1
AND PAF.ASSIGNMENT_ID = PAA.ASSIGNMENT_ID
AND PBT.BALANCE_TYPE_ID = PBTTL.BALANCE_TYPE_ID
AND PAA.PAYROLL_ACTION_ID = PPA.PAYROLL_ACTION_ID
AND PDB.BALANCE_TYPE_ID = PBT.BALANCE_TYPE_ID
AND PDB.BALANCE_DIMENSION_ID = PBD.BALANCE_DIMENSION_ID
AND NOT EXISTS (SELECT ''
FROM FF_ROUTE_CONTEXT_USAGES FRCU
, FF_CONTEXTS FC
WHERE FRCU.ROUTE_ID = PBD.ROUTE_ID
AND FC.CONTEXT_ID = FRCU.CONTEXT_ID
AND FC.CONTEXT_NAME NOT IN ('TAX_UNIT_ID'
, 'ASSIGNMENT_ACTION_ID')
AND NOT EXISTS (SELECT ''
FROM PAY_ACTION_CONTEXTS PAC
WHERE PAC.ASSIGNMENT_ACTION_ID = PAA.ASSIGNMENT_ACTION_ID
AND PAC.CONTEXT_ID = FRCU.CONTEXT_ID) )
AND PAA.ACTION_SEQUENCE = (SELECT MAX(PAA2.ACTION_SEQUENCE)
FROM PAY_ASSIGNMENT_ACTIONS PAA2
, PAY_PAYROLL_ACTIONS PPA2
, PAY_BALANCE_FEEDS_F PBF2
, PAY_BALANCE_TYPES PBT2
, PAY_RUN_RESULTS PRR2
WHERE PPA2.ACTION_TYPE IN ('Q'
, 'R')
AND PAA2.ASSIGNMENT_ID = PAA.ASSIGNMENT_ID
AND PBT2.BALANCE_TYPE_ID = PBT.BALANCE_TYPE_ID
AND TRUNC(ADD_MONTHS(PPA2.EFFECTIVE_DATE
, 1)
, 'MM')-1 = TRUNC(ADD_MONTHS(PPA.EFFECTIVE_DATE
, 1)
, 'MM')-1
AND PPA2.PAYROLL_ACTION_ID = PAA2.PAYROLL_ACTION_ID
AND PRR2.ASSIGNMENT_ACTION_ID = PAA2.ASSIGNMENT_ACTION_ID
AND PBF2.BUSINESS_GROUP_ID = PBT2.BUSINESS_GROUP_ID
AND NVL(PBF2.LEGISLATION_CODE
, 'SG') = NVL(PBT2.LEGISLATION_CODE
, 'SG')
AND PBF2.BALANCE_TYPE_ID = PBT2.BALANCE_TYPE_ID
AND PPA2.EFFECTIVE_DATE BETWEEN PBF2.EFFECTIVE_START_DATE
AND PBF2.EFFECTIVE_END_DATE )
AND PEEF.ASSIGNMENT_ID = PAF.ASSIGNMENT_ID
AND PPA.EFFECTIVE_DATE BETWEEN PEEF.EFFECTIVE_START_DATE
AND PEEF.EFFECTIVE_END_DATE
AND PETF.ELEMENT_TYPE_ID = PELF.ELEMENT_TYPE_ID
AND PELF.ELEMENT_LINK_ID = PEEF.ELEMENT_LINK_ID

Columns