View: PAY_BALANCES_V

Product:	PAY - Payroll
Description:
Implementation/DBA Data:	APPS.PAY_BALANCES_V

View Text

SELECT RRS.ASSIGNMENT_ACTION_ID
, (BTTL.BALANCE_NAME || DECODE(FF_DBI_UTILS_PKG.TRANSLATION_SUPPORTED(PBG.LEGISLATION_CODE)
, 'Y'
, BMTL.DATABASE_ITEM_SUFFIX
, BM.DATABASE_ITEM_SUFFIX)) BALANCE_NAME_AND_SUFFIX
, BD.DEFINED_BALANCE_ID
, BT.BALANCE_TYPE_ID
, BM.BALANCE_DIMENSION_ID
, PAY_PAYROLL_ACTIONS_PKG.LATEST_BALANCE_EXISTS (RRS.ASSIGNMENT_ACTION_ID
, BD.DEFINED_BALANCE_ID) LATEST_BALANCE_EXIST
, BT.BALANCE_UOM C_BALANCE_UOM
, HR_GENERAL.DECODE_LOOKUP('UNITS'
, BT.BALANCE_UOM) D_BALANCE_UOM
, BT.CURRENCY_CODE D_CURRENCY_CODE
, BD.BUSINESS_GROUP_ID + 0 BUSINESS_GROUP_ID
, BD.LEGISLATION_CODE
, MIN(BAF.EFFECTIVE_START_DATE) EFFECTIVE_START_DATE
, MAX(BAF.EFFECTIVE_END_DATE) EFFECTIVE_END_DATE
FROM PAY_ASSIGNMENT_ACTIONS PAA
, PAY_PAYROLL_ACTIONS PPA
, PAY_RUN_RESULTS RRS
, PAY_RUN_RESULT_VALUES RRV
, PAY_BALANCE_FEEDS_F BAF
, PAY_BALANCE_TYPES_TL BTTL
, PAY_BALANCE_TYPES BT
, PAY_DEFINED_BALANCES BD
, PAY_BALANCE_DIMENSIONS BM
, PAY_BALANCE_DIMENSIONS_TL BMTL
, PER_BUSINESS_GROUPS_PERF PBG
WHERE PPA.PAYROLL_ACTION_ID = PAA.PAYROLL_ACTION_ID
AND PBG.BUSINESS_GROUP_ID = PPA.BUSINESS_GROUP_ID
AND RRS.ASSIGNMENT_ACTION_ID = PAA.ASSIGNMENT_ACTION_ID
AND BT.BALANCE_TYPE_ID = BTTL.BALANCE_TYPE_ID
AND BTTL.LANGUAGE = USERENV('LANG')
AND RRS.RUN_RESULT_ID = RRV.RUN_RESULT_ID
AND RRV.INPUT_VALUE_ID = BAF.INPUT_VALUE_ID
AND PPA.EFFECTIVE_DATE BETWEEN BAF.EFFECTIVE_START_DATE
AND BAF.EFFECTIVE_END_DATE
AND BAF.BALANCE_TYPE_ID = BT.BALANCE_TYPE_ID
AND BT.BALANCE_TYPE_ID = BD.BALANCE_TYPE_ID
AND BD.BALANCE_DIMENSION_ID = BM.BALANCE_DIMENSION_ID
AND BM.BALANCE_DIMENSION_ID = BMTL.BALANCE_DIMENSION_ID
AND BMTL.LANGUAGE = USERENV('LANG')
AND ((NOT EXISTS (SELECT 1
FROM FF_ROUTE_CONTEXT_USAGES RCU
, FF_CONTEXTS CON
WHERE RCU.ROUTE_ID = BM.ROUTE_ID
AND RCU.CONTEXT_ID = CON.CONTEXT_ID
AND CON.CONTEXT_NAME NOT IN ('ASSIGNMENT_ACTION_ID'
, 'TAX_UNIT_ID') ) ) OR (EXISTS (SELECT 'Y'
FROM PAY_LEGISLATIVE_FIELD_INFO LFI
WHERE LFI.VALIDATION_TYPE = 'BLOCK_PROPERTY'
AND LFI.VALIDATION_NAME = 'DISPLAY'
AND LFI.RULE_TYPE = 'BALANCE_BREAKDOWN'
AND LFI.RULE_MODE = 'Y'
AND LFI.FIELD_NAME = 'CONTEXT_VALUE'
AND LFI.TARGET_LOCATION = 'PAYWSACT'
AND LFI.LEGISLATION_CODE = PBG.LEGISLATION_CODE)
AND NOT EXISTS (SELECT 1
FROM FF_ROUTE_CONTEXT_USAGES RCU
, FF_CONTEXTS CON
WHERE RCU.ROUTE_ID = BM.ROUTE_ID
AND RCU.CONTEXT_ID = CON.CONTEXT_ID
AND (CON.CONTEXT_NAME NOT IN ('ASSIGNMENT_ACTION_ID'
, 'TAX_UNIT_ID'
, 'JURISDICTION_CODE'
, 'TAX_GROUP'
, 'SOURCE_ID'
, 'ORIGINAL_ENTRY_ID'
, 'SOURCE_TEXT'
, 'TIME_DEFINITION_ID'
, 'BALANCE_DATE'
, 'SOURCE_TEXT2' ) OR (CON.CONTEXT_NAME IN ('JURISDICTION_CODE'
, 'TAX_GROUP'
, 'SOURCE_ID'
, 'ORIGINAL_ENTRY_ID'
, 'SOURCE_TEXT'
, 'TIME_DEFINITION_ID'
, 'BALANCE_DATE'
, 'SOURCE_TEXT2' )
AND NOT EXISTS (SELECT 1
FROM PAY_ACTION_CONTEXTS PAC
WHERE PAC.ASSIGNMENT_ACTION_ID = RRS.ASSIGNMENT_ACTION_ID
AND PAC.CONTEXT_ID = RCU.CONTEXT_ID) ) ) ) ) ) GROUP BY RRS.ASSIGNMENT_ACTION_ID
, (BTTL.BALANCE_NAME || DECODE(FF_DBI_UTILS_PKG.TRANSLATION_SUPPORTED(PBG.LEGISLATION_CODE)
, 'Y'
, BMTL.DATABASE_ITEM_SUFFIX
, BM.DATABASE_ITEM_SUFFIX))
, BD.DEFINED_BALANCE_ID
, BT.BALANCE_TYPE_ID
, BM.BALANCE_DIMENSION_ID
, PAY_PAYROLL_ACTIONS_PKG.LATEST_BALANCE_EXISTS (RRS.ASSIGNMENT_ACTION_ID
, BD.DEFINED_BALANCE_ID)
, BT.BALANCE_UOM
, HR_GENERAL.DECODE_LOOKUP('UNITS'
, BT.BALANCE_UOM)
, BT.CURRENCY_CODE
, BD.BUSINESS_GROUP_ID
, BD.LEGISLATION_CODE

Columns