View: PAY_PAYGBBAL_VALUES_V

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

View Text

SELECT DISTINCT PAA.ASSIGNMENT_ID
, PAA.ASSIGNMENT_ACTION_ID
, PBT.BALANCE_TYPE_ID
, PBTTL.BALANCE_NAME
, PDB.DEFINED_BALANCE_ID
, PBD.DATABASE_ITEM_SUFFIX
, PAY_BALANCE_PKG.GET_VALUE(PDB.DEFINED_BALANCE_ID
, PAA.ASSIGNMENT_ACTION_ID) VALUE
FROM PAY_BALANCE_TYPES PBT
, PAY_BALANCE_TYPES_TL PBTTL
, PAY_DEFINED_BALANCES PDB
, PAY_BALANCE_DIMENSIONS PBD
, PAY_ASSIGNMENT_ACTIONS PAA
, PAY_PAYROLL_ACTIONS PPA
WHERE PBT.BALANCE_TYPE_ID = PBTTL.BALANCE_TYPE_ID
AND PBTTL.LANGUAGE = USERENV('LANG')
AND (PBT.LEGISLATION_CODE IS NULL OR PBT.LEGISLATION_CODE = 'GB')
AND PBT.BALANCE_TYPE_ID = PDB.BALANCE_TYPE_ID
AND PBD.BALANCE_DIMENSION_ID = PDB.BALANCE_DIMENSION_ID
AND NOT (PBD.DIMENSION_NAME LIKE '%ELEMENT%')
AND NOT (PBD.DIMENSION_NAME LIKE '%PER_CO%')
AND PAA.PAYROLL_ACTION_ID = PPA.PAYROLL_ACTION_ID
AND ( PDB.LEGISLATION_CODE = 'GB' OR PDB.BUSINESS_GROUP_ID = PPA.BUSINESS_GROUP_ID)
AND NOT EXISTS ( SELECT 1
FROM PAY_ACTION_CONTEXTS PCA
, FF_ROUTE_CONTEXT_USAGES FRC
, FF_CONTEXTS FFC
WHERE PCA.ASSIGNMENT_ACTION_ID = PAA.ASSIGNMENT_ACTION_ID
AND PBD.ROUTE_ID = FRC.ROUTE_ID
AND FRC.CONTEXT_ID = FFC.CONTEXT_ID
AND FFC.CONTEXT_ID = PCA.CONTEXT_ID) UNION ALL SELECT DISTINCT PAA.ASSIGNMENT_ID
, PAA.ASSIGNMENT_ACTION_ID
, PBT.BALANCE_TYPE_ID
, PBTTL.BALANCE_NAME
, PDB.DEFINED_BALANCE_ID
, HR_GBBAL.GET_ELEMENT_REFERENCE(PRR.RUN_RESULT_ID
, PBD.DATABASE_ITEM_SUFFIX) DATABASE_ITEM_SUFFIX
, DECODE(PBD.DATABASE_ITEM_SUFFIX
, '_ELEMENT_PTD'
, HR_GBBAL.CALC_ELEMENT_PTD_BAL(PAA.ASSIGNMENT_ACTION_ID
, PBT.BALANCE_TYPE_ID
, PRR.SOURCE_ID)
, '_ELEMENT_ITD'
, HR_GBBAL.CALC_ELEMENT_ITD_BAL(PAA.ASSIGNMENT_ACTION_ID
, PBT.BALANCE_TYPE_ID
, PRR.SOURCE_ID)
, NULL) VALUE
FROM PAY_BALANCE_TYPES PBT
, PAY_BALANCE_TYPES_TL PBTTL
, PAY_DEFINED_BALANCES PDB
, PAY_BALANCE_DIMENSIONS PBD
, PAY_ASSIGNMENT_ACTIONS PAA
, PAY_RUN_RESULT_VALUES PRRV
, PAY_RUN_RESULTS PRR
, PAY_BALANCE_FEEDS_F FEED
, PAY_ASSIGNMENT_ACTIONS PAA2
, PAY_PAYROLL_ACTIONS PPA
WHERE PBT.BALANCE_TYPE_ID = PBTTL.BALANCE_TYPE_ID
AND PBTTL.LANGUAGE = USERENV('LANG')
AND (PBT.LEGISLATION_CODE IS NULL OR PBT.LEGISLATION_CODE = 'GB')
AND PBD.DIMENSION_NAME LIKE '%ELEMENT%'
AND PBT.BALANCE_TYPE_ID = PDB.BALANCE_TYPE_ID
AND PBD.BALANCE_DIMENSION_ID = PDB.BALANCE_DIMENSION_ID
AND PAA.PAYROLL_ACTION_ID = PPA.PAYROLL_ACTION_ID
AND ( PDB.LEGISLATION_CODE = 'GB' OR PDB.BUSINESS_GROUP_ID =PPA.BUSINESS_GROUP_ID)
AND PBT.BALANCE_TYPE_ID = FEED.BALANCE_TYPE_ID
AND PRR.RUN_RESULT_ID = PRRV.RUN_RESULT_ID
AND FEED.INPUT_VALUE_ID = PRRV.INPUT_VALUE_ID
AND PRRV.RESULT_VALUE IS NOT NULL
AND PRRV.RESULT_VALUE != '0'
AND PAA2.ACTION_SEQUENCE <= PAA.ACTION_SEQUENCE
AND PAA2.ASSIGNMENT_ACTION_ID = PRR.ASSIGNMENT_ACTION_ID
AND PAA2.ASSIGNMENT_ID = PAA.ASSIGNMENT_ID
AND PBD.DIMENSION_NAME <> '_ELEMENT_CO_REF_ITD' GROUP BY PRR.RUN_RESULT_ID
, PAA.ASSIGNMENT_ID
, PAA.ASSIGNMENT_ACTION_ID
, PBT.BALANCE_TYPE_ID
, PBTTL.BALANCE_NAME
, PDB.DEFINED_BALANCE_ID
, PBD.DATABASE_ITEM_SUFFIX
, PRR.SOURCE_ID UNION ALL SELECT DISTINCT PAA.ASSIGNMENT_ID
, PAA.ASSIGNMENT_ACTION_ID
, PBT.BALANCE_TYPE_ID
, PBTTL.BALANCE_NAME
, PDB.DEFINED_BALANCE_ID
, HR_GBBAL.GET_CONTEXT_REFERENCES(PCA.CONTEXT_VALUE
, PBD.DATABASE_ITEM_SUFFIX) DATABASE_ITEM_SUFFIX
, PAY_BALANCE_PKG.GET_VALUE(PDB.DEFINED_BALANCE_ID
, PAA.ASSIGNMENT_ACTION_ID
, NULL
, NULL
, PCA.CONTEXT_ID
, PCA.CONTEXT_VALUE
, NULL
, NULL) VALUE
FROM PAY_ASSIGNMENT_ACTIONS PAA
, PAY_PAYROLL_ACTIONS PPA
, PAY_BALANCE_TYPES PBT
, PAY_BALANCE_TYPES_TL PBTTL
, PAY_DEFINED_BALANCES PDB
, PAY_BALANCE_DIMENSIONS PBD
, PAY_ACTION_CONTEXTS PCA
, FF_ROUTE_CONTEXT_USAGES FRC
, FF_CONTEXTS FFC
WHERE PBT.BALANCE_TYPE_ID = PBTTL.BALANCE_TYPE_ID
AND PBTTL.LANGUAGE = USERENV('LANG')
AND (PBT.LEGISLATION_CODE IS NULL OR PBT.LEGISLATION_CODE = 'GB')
AND PAA.PAYROLL_ACTION_ID = PPA.PAYROLL_ACTION_ID
AND PBT.BALANCE_TYPE_ID = PDB.BALANCE_TYPE_ID
AND PDB.BALANCE_DIMENSION_ID = PBD.BALANCE_DIMENSION_ID
AND ( PDB.LEGISLATION_CODE = 'GB' OR PDB.LEGISLATION_CODE = PPA.BUSINESS_GROUP_ID)
AND NVL(PBT.BUSINESS_GROUP_ID
, PPA.BUSINESS_GROUP_ID)=PPA.BUSINESS_GROUP_ID
AND PCA.ASSIGNMENT_ACTION_ID = PAA.ASSIGNMENT_ACTION_ID
AND PBD.ROUTE_ID = FRC.ROUTE_ID
AND FRC.CONTEXT_ID = FFC.CONTEXT_ID
AND FFC.CONTEXT_ID = PCA.CONTEXT_ID
AND NOT (PBD.DIMENSION_NAME LIKE '%ELEMENT%')
AND (FFC.CONTEXT_NAME <> 'SOURCE_TEXT' OR (FFC.CONTEXT_NAME = 'SOURCE_TEXT'
AND EXISTS ( SELECT 1
FROM PAY_RUN_RESULTS RR
, PAY_RUN_RESULT_VALUES RRV
, PAY_INPUT_VALUES_F PIV
, PAY_ELEMENT_TYPES_F PETF
WHERE RR.ASSIGNMENT_ACTION_ID = PCA.ASSIGNMENT_ACTION_ID
AND RR.ELEMENT_TYPE_ID = PETF.ELEMENT_TYPE_ID
AND RR.RUN_RESULT_ID = RRV.RUN_RESULT_ID
AND PIV.INPUT_VALUE_ID = RRV.INPUT_VALUE_ID
AND PIV.NAME = 'REFERENCE'
AND NVL(RRV.RESULT_VALUE
, 'UNKNOWN') = PCA.CONTEXT_VALUE
AND PETF.ELEMENT_NAME IN ( 'CAO SCOTLAND'
, 'CAO SCOTLAND NTPP'
, 'CMA SCOTLAND'
, 'CMA SCOTLAND NTPP'
, 'COURT ORDER'
, 'COURT ORDER NTPP'
, 'COURT ORDER NON PRIORITY'
, 'COURT ORDER NON PRIORITY NTPP'
, 'EAS SCOTLAND'
, 'EAS SCOTLAND NTPP'
, 'SETUP COURT ORDER BALANCE' ) ) ) ) UNION ALL SELECT DISTINCT PAA.ASSIGNMENT_ID
, PAA.ASSIGNMENT_ACTION_ID
, PBT.BALANCE_TYPE_ID
, PBTTL.BALANCE_NAME
, PDB.DEFINED_BALANCE_ID
, HR_GBBAL.GET_ELEMENT_REFERENCE(PRR.RUN_RESULT_ID
, PBD.DATABASE_ITEM_SUFFIX) DATABASE_ITEM_SUFFIX
, HR_GBBAL.CALC_ELEMENT_CO_ITD_BAL(PAA.ASSIGNMENT_ACTION_ID
, PBT.BALANCE_TYPE_ID
, PRR.SOURCE_ID
, PRRV1.RESULT_VALUE) VALUE
FROM PAY_BALANCE_TYPES PBT
, PAY_BALANCE_TYPES_TL PBTTL
, PAY_DEFINED_BALANCES PDB
, PAY_BALANCE_DIMENSIONS PBD
, PAY_ASSIGNMENT_ACTIONS PAA
, PAY_RUN_RESULT_VALUES PRRV
, PAY_INPUT_VALUES_F PIV
, PAY_RUN_RESULT_VALUES PRRV1
, PAY_RUN_RESULTS PRR
, PAY_BALANCE_FEEDS_F FEED
, PAY_ASSIGNMENT_ACTIONS PAA2
, PAY_PAYROLL_ACTIONS PPA
WHERE PBT.BALANCE_TYPE_ID = PBTTL.BALANCE_TYPE_ID
AND PBTTL.LANGUAGE = USERENV('LANG')
AND (PBT.LEGISLATION_CODE IS NULL OR PBT.LEGISLATION_CODE = 'GB')
AND PAA.PAYROLL_ACTION_ID = PPA.PAYROLL_ACTION_ID
AND ( PDB.LEGISLATION_CODE = 'GB' OR PDB.BUSINESS_GROUP_ID = PPA.BUSINESS_GROUP_ID)
AND PBD.DIMENSION_NAME LIKE '_ELEMENT_CO_REF_ITD'
AND PBT.BALANCE_TYPE_ID = PDB.BALANCE_TYPE_ID
AND PBD.BALANCE_DIMENSION_ID = PDB.BALANCE_DIMENSION_ID
AND PBT.BALANCE_TYPE_ID = FEED.BALANCE_TYPE_ID
AND PRR.RUN_RESULT_ID = PRRV.RUN_RESULT_ID
AND FEED.INPUT_VALUE_ID = PRRV.INPUT_VALUE_ID
AND PRRV.RESULT_VALUE IS NOT NULL
AND PRRV.RESULT_VALUE != '0'
AND PAA2.ACTION_SEQUENCE <= PAA.ACTION_SEQUENCE
AND PIV.NAME = 'REFERENCE'
AND PIV.INPUT_VALUE_ID = PRRV1.INPUT_VALUE_ID
AND PRRV1.RUN_RESULT_ID = PRRV.RUN_RESULT_ID
AND PRRV1.RESULT_VALUE IS NOT NULL
AND PRRV1.RESULT_VALUE != '0'
AND PAA2.ASSIGNMENT_ACTION_ID = PRR.ASSIGNMENT_ACTION_ID
AND PAA2.ASSIGNMENT_ID = PAA.ASSIGNMENT_ID GROUP BY PRR.RUN_RESULT_ID
, PAA.ASSIGNMENT_ID
, PAA.ASSIGNMENT_ACTION_ID
, PBT.BALANCE_TYPE_ID
, PBTTL.BALANCE_NAME
, PDB.DEFINED_BALANCE_ID
, PBD.DATABASE_ITEM_SUFFIX
, PRR.SOURCE_ID
, PRRV1.RESULT_VALUE

Columns