View: PAY_CA_EARN_BALANCES_ALL_RBR_V

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

View Text

SELECT PAA.ASSIGNMENT_ACTION_ID
, PAA.ASSIGNMENT_ID
, PAA.TAX_UNIT_ID
, PET.ELEMENT_TYPE_ID
, PET.CLASSIFICATION_ID
, PEC.CLASSIFICATION_NAME
, PETL.REPORTING_NAME
, PET.REPORTING_NAME BASE_REPORTING_NAME
, PETL.ELEMENT_NAME
, PET.PROCESSING_PRIORITY
, HR_BALANCES.DECODE_BALANCE(PBT.BALANCE_TYPE_ID)
, PBT.BALANCE_TYPE_ID
, PET.ELEMENT_INFORMATION10
, PET.ELEMENT_INFORMATION12
, PET.BUSINESS_GROUP_ID
, DECODE(PPA.ACTION_TYPE
, 'R'
, PAY_CA_BALANCE_PKG.CALL_CA_BALANCE_GET_VALUE ( PBT.BALANCE_NAME
, 'RUN'
, PAA.ASSIGNMENT_ACTION_ID
, PAA.ASSIGNMENT_ID
, NULL
, PAY_CA_BALANCE_VIEW_PKG.GET_SESSION_VAR('REPORT_LEVEL')
, PAA.TAX_UNIT_ID
, PBT.BUSINESS_GROUP_ID
, NVL(PAY_CA_BALANCE_VIEW_PKG.GET_SESSION_VAR('JURISDICTION_CODE')
, NULL) )
, 'Q'
, PAY_CA_BALANCE_PKG.CALL_CA_BALANCE_GET_VALUE ( PBT.BALANCE_NAME
, 'RUN'
, PAA.ASSIGNMENT_ACTION_ID
, PAA.ASSIGNMENT_ID
, NULL
, PAY_CA_BALANCE_VIEW_PKG.GET_SESSION_VAR('REPORT_LEVEL')
, PAA.TAX_UNIT_ID
, PBT.BUSINESS_GROUP_ID
, NVL(PAY_CA_BALANCE_VIEW_PKG.GET_SESSION_VAR('JURISDICTION_CODE')
, NULL) )
, 'P'
, PAY_CA_BALANCE_PKG.CALL_CA_BALANCE_GET_VALUE ( PBT.BALANCE_NAME
, 'CURRENT'
, PAA.ASSIGNMENT_ACTION_ID
, PAA.ASSIGNMENT_ID
, NULL
, PAY_CA_BALANCE_VIEW_PKG.GET_SESSION_VAR('REPORT_LEVEL')
, PAA.TAX_UNIT_ID
, PBT.BUSINESS_GROUP_ID
, NVL(PAY_CA_BALANCE_VIEW_PKG.GET_SESSION_VAR('JURISDICTION_CODE')
, NULL) )
, 'U'
, PAY_CA_BALANCE_PKG.CALL_CA_BALANCE_GET_VALUE ( PBT.BALANCE_NAME
, 'CURRENT'
, PAA.ASSIGNMENT_ACTION_ID
, PAA.ASSIGNMENT_ID
, NULL
, PAY_CA_BALANCE_VIEW_PKG.GET_SESSION_VAR('REPORT_LEVEL')
, PAA.TAX_UNIT_ID
, PBT.BUSINESS_GROUP_ID
, NVL(PAY_CA_BALANCE_VIEW_PKG.GET_SESSION_VAR('JURISDICTION_CODE')
, NULL) )
, 'V'
, PAY_CA_BALANCE_PKG.CALL_CA_BALANCE_GET_VALUE ( PBT.BALANCE_NAME
, 'RUN'
, PAA.ASSIGNMENT_ACTION_ID
, PAA.ASSIGNMENT_ID
, NULL
, PAY_CA_BALANCE_VIEW_PKG.GET_SESSION_VAR('REPORT_LEVEL')
, PAA.TAX_UNIT_ID
, PBT.BUSINESS_GROUP_ID
, NVL(PAY_CA_BALANCE_VIEW_PKG.GET_SESSION_VAR('JURISDICTION_CODE')
, NULL) )
, 0)
, DECODE(PET.ELEMENT_INFORMATION12
, NULL
, 0
, DECODE(PPA.ACTION_TYPE
, 'R'
, PAY_CA_BALANCE_PKG.CALL_CA_BALANCE_GET_VALUE ( DECODE (PET.ELEMENT_INFORMATION12
, NULL
, NULL
, PAY_CA_PAYRG_PKG.HOURS_BAL_NAME (PET.ELEMENT_INFORMATION12))
, 'RUN'
, PAA.ASSIGNMENT_ACTION_ID
, PAA.ASSIGNMENT_ID
, NULL
, PAY_CA_BALANCE_VIEW_PKG.GET_SESSION_VAR('REPORT_LEVEL')
, PAA.TAX_UNIT_ID
, PBT.BUSINESS_GROUP_ID
, NVL(PAY_CA_BALANCE_VIEW_PKG.GET_SESSION_VAR('JURISDICTION_CODE')
, NULL) )
, 'Q'
, PAY_CA_BALANCE_PKG.CALL_CA_BALANCE_GET_VALUE ( DECODE (PET.ELEMENT_INFORMATION12
, NULL
, NULL
, PAY_CA_PAYRG_PKG.HOURS_BAL_NAME (PET.ELEMENT_INFORMATION12))
, 'RUN'
, PAA.ASSIGNMENT_ACTION_ID
, PAA.ASSIGNMENT_ID
, NULL
, PAY_CA_BALANCE_VIEW_PKG.GET_SESSION_VAR('REPORT_LEVEL')
, PAA.TAX_UNIT_ID
, PBT.BUSINESS_GROUP_ID
, NVL(PAY_CA_BALANCE_VIEW_PKG.GET_SESSION_VAR('JURISDICTION_CODE')
, NULL) )
, 'P'
, PAY_CA_BALANCE_PKG.CALL_CA_BALANCE_GET_VALUE ( DECODE (PET.ELEMENT_INFORMATION12
, NULL
, NULL
, PAY_CA_PAYRG_PKG.HOURS_BAL_NAME (PET.ELEMENT_INFORMATION12))
, 'CURRENT'
, PAA.ASSIGNMENT_ACTION_ID
, PAA.ASSIGNMENT_ID
, NULL
, PAY_CA_BALANCE_VIEW_PKG.GET_SESSION_VAR('REPORT_LEVEL')
, PAA.TAX_UNIT_ID
, PBT.BUSINESS_GROUP_ID
, NVL(PAY_CA_BALANCE_VIEW_PKG.GET_SESSION_VAR('JURISDICTION_CODE')
, NULL) )
, 'U'
, PAY_CA_BALANCE_PKG.CALL_CA_BALANCE_GET_VALUE ( DECODE (PET.ELEMENT_INFORMATION12
, NULL
, NULL
, PAY_CA_PAYRG_PKG.HOURS_BAL_NAME (PET.ELEMENT_INFORMATION12))
, 'CURRENT'
, PAA.ASSIGNMENT_ACTION_ID
, PAA.ASSIGNMENT_ID
, NULL
, PAY_CA_BALANCE_VIEW_PKG.GET_SESSION_VAR('REPORT_LEVEL')
, PAA.TAX_UNIT_ID
, PBT.BUSINESS_GROUP_ID
, NVL(PAY_CA_BALANCE_VIEW_PKG.GET_SESSION_VAR('JURISDICTION_CODE')
, NULL) )
, 'V'
, PAY_CA_BALANCE_PKG.CALL_CA_BALANCE_GET_VALUE ( DECODE (PET.ELEMENT_INFORMATION12
, NULL
, NULL
, PAY_CA_PAYRG_PKG.HOURS_BAL_NAME (PET.ELEMENT_INFORMATION12))
, 'RUN'
, PAA.ASSIGNMENT_ACTION_ID
, PAA.ASSIGNMENT_ID
, NULL
, PAY_CA_BALANCE_VIEW_PKG.GET_SESSION_VAR('REPORT_LEVEL')
, PAA.TAX_UNIT_ID
, PBT.BUSINESS_GROUP_ID
, NVL(PAY_CA_BALANCE_VIEW_PKG.GET_SESSION_VAR('JURISDICTION_CODE')
, NULL) )
, 0))
, PAY_CA_BALANCE_PKG.CALL_CA_BALANCE_GET_VALUE ( PBT.BALANCE_NAME
, 'CURRENT'
, PAA.ASSIGNMENT_ACTION_ID
, PAA.ASSIGNMENT_ID
, NULL
, PAY_CA_BALANCE_VIEW_PKG.GET_SESSION_VAR('REPORT_LEVEL')
, PAA.TAX_UNIT_ID
, PBT.BUSINESS_GROUP_ID
, NVL(PAY_CA_BALANCE_VIEW_PKG.GET_SESSION_VAR('JURISDICTION_CODE')
, NULL) )
, DECODE(PET.ELEMENT_INFORMATION12
, NULL
, 0
, PAY_CA_BALANCE_PKG.CALL_CA_BALANCE_GET_VALUE ( DECODE (PET.ELEMENT_INFORMATION12
, NULL
, NULL
, PAY_CA_PAYRG_PKG.HOURS_BAL_NAME (PET.ELEMENT_INFORMATION12))
, 'CURRENT'
, PAA.ASSIGNMENT_ACTION_ID
, PAA.ASSIGNMENT_ID
, NULL
, PAY_CA_BALANCE_VIEW_PKG.GET_SESSION_VAR('REPORT_LEVEL')
, PAA.TAX_UNIT_ID
, PBT.BUSINESS_GROUP_ID
, NVL(PAY_CA_BALANCE_VIEW_PKG.GET_SESSION_VAR('JURISDICTION_CODE')
, NULL) ))
, PAY_CA_BALANCE_PKG.CALL_CA_BALANCE_GET_VALUE ( PBT.BALANCE_NAME
, 'PTD'
, PAA.ASSIGNMENT_ACTION_ID
, PAA.ASSIGNMENT_ID
, NULL
, PAY_CA_BALANCE_VIEW_PKG.GET_SESSION_VAR('REPORT_LEVEL')
, PAA.TAX_UNIT_ID
, PBT.BUSINESS_GROUP_ID
, NVL(PAY_CA_BALANCE_VIEW_PKG.GET_SESSION_VAR('JURISDICTION_CODE')
, NULL) )
, DECODE(PET.ELEMENT_INFORMATION12
, NULL
, 0
, PAY_CA_BALANCE_PKG.CALL_CA_BALANCE_GET_VALUE ( DECODE (PET.ELEMENT_INFORMATION12
, NULL
, NULL
, PAY_CA_PAYRG_PKG.HOURS_BAL_NAME (PET.ELEMENT_INFORMATION12))
, 'PTD'
, PAA.ASSIGNMENT_ACTION_ID
, PAA.ASSIGNMENT_ID
, NULL
, PAY_CA_BALANCE_VIEW_PKG.GET_SESSION_VAR('REPORT_LEVEL')
, PAA.TAX_UNIT_ID
, PBT.BUSINESS_GROUP_ID
, NVL(PAY_CA_BALANCE_VIEW_PKG.GET_SESSION_VAR('JURISDICTION_CODE')
, NULL) ))
, PAY_CA_BALANCE_PKG.CALL_CA_BALANCE_GET_VALUE ( PBT.BALANCE_NAME
, 'QTD'
, PAA.ASSIGNMENT_ACTION_ID
, PAA.ASSIGNMENT_ID
, NULL
, PAY_CA_BALANCE_VIEW_PKG.GET_SESSION_VAR('REPORT_LEVEL')
, PAA.TAX_UNIT_ID
, PBT.BUSINESS_GROUP_ID
, NVL(PAY_CA_BALANCE_VIEW_PKG.GET_SESSION_VAR('JURISDICTION_CODE')
, NULL) )
, DECODE(PET.ELEMENT_INFORMATION12
, NULL
, 0
, PAY_CA_BALANCE_PKG.CALL_CA_BALANCE_GET_VALUE ( DECODE (PET.ELEMENT_INFORMATION12
, NULL
, NULL
, PAY_CA_PAYRG_PKG.HOURS_BAL_NAME (PET.ELEMENT_INFORMATION12))
, 'QTD'
, PAA.ASSIGNMENT_ACTION_ID
, PAA.ASSIGNMENT_ID
, NULL
, PAY_CA_BALANCE_VIEW_PKG.GET_SESSION_VAR('REPORT_LEVEL')
, PAA.TAX_UNIT_ID
, PBT.BUSINESS_GROUP_ID
, NVL(PAY_CA_BALANCE_VIEW_PKG.GET_SESSION_VAR('JURISDICTION_CODE')
, NULL) ))
, DECODE(PET.ELEMENT_INFORMATION12
, NULL
, 0
, PAY_CA_BALANCE_PKG.CALL_CA_BALANCE_GET_VALUE ( DECODE (PET.ELEMENT_INFORMATION12
, NULL
, NULL
, PAY_CA_PAYRG_PKG.HOURS_BAL_NAME (PET.ELEMENT_INFORMATION12))
, 'MONTH'
, PAA.ASSIGNMENT_ACTION_ID
, PAA.ASSIGNMENT_ID
, NULL
, PAY_CA_BALANCE_VIEW_PKG.GET_SESSION_VAR('REPORT_LEVEL')
, PAA.TAX_UNIT_ID
, PBT.BUSINESS_GROUP_ID
, NVL(PAY_CA_BALANCE_VIEW_PKG.GET_SESSION_VAR('JURISDICTION_CODE')
, NULL) ))
, DECODE(PET.ELEMENT_INFORMATION12
, NULL
, 0
, PAY_CA_BALANCE_PKG.CALL_CA_BALANCE_GET_VALUE ( DECODE (PET.ELEMENT_INFORMATION12
, NULL
, NULL
, PAY_CA_PAYRG_PKG.HOURS_BAL_NAME (PET.ELEMENT_INFORMATION12))
, 'YTD'
, PAA.ASSIGNMENT_ACTION_ID
, PAA.ASSIGNMENT_ID
, NULL
, PAY_CA_BALANCE_VIEW_PKG.GET_SESSION_VAR('REPORT_LEVEL')
, PAA.TAX_UNIT_ID
, PBT.BUSINESS_GROUP_ID
, NVL(PAY_CA_BALANCE_VIEW_PKG.GET_SESSION_VAR('JURISDICTION_CODE')
, NULL) ))
, PAY_CA_BALANCE_PKG.CALL_CA_BALANCE_GET_VALUE ( PBT.BALANCE_NAME
, 'MONTH'
, PAA.ASSIGNMENT_ACTION_ID
, PAA.ASSIGNMENT_ID
, NULL
, PAY_CA_BALANCE_VIEW_PKG.GET_SESSION_VAR('REPORT_LEVEL')
, PAA.TAX_UNIT_ID
, PBT.BUSINESS_GROUP_ID
, NVL(PAY_CA_BALANCE_VIEW_PKG.GET_SESSION_VAR('JURISDICTION_CODE')
, NULL) )
, PAY_CA_BALANCE_PKG.CALL_CA_BALANCE_GET_VALUE ( PBT.BALANCE_NAME
, 'YTD'
, PAA.ASSIGNMENT_ACTION_ID
, PAA.ASSIGNMENT_ID
, NULL
, PAY_CA_BALANCE_VIEW_PKG.GET_SESSION_VAR('REPORT_LEVEL')
, PAA.TAX_UNIT_ID
, PBT.BUSINESS_GROUP_ID
, NVL(PAY_CA_BALANCE_VIEW_PKG.GET_SESSION_VAR('JURISDICTION_CODE')
, NULL) )
FROM PAY_BALANCE_TYPES PBT
, PAY_ELEMENT_CLASSIFICATIONS PEC
, PAY_ELEMENT_TYPES_F_TL PETL
, PAY_ELEMENT_TYPES_F PET
, PAY_ASSIGNMENT_ACTIONS PAA
, PAY_PAYROLL_ACTIONS PPA
, PAY_DEFINED_BALANCES PDB
, PAY_BALANCE_ATTRIBUTES PBA
, PAY_BAL_ATTRIBUTE_DEFINITIONS PBAD
WHERE PET.ELEMENT_TYPE_ID = PETL.ELEMENT_TYPE_ID
AND PETL.LANGUAGE = USERENV('LANG')
AND PBT.BALANCE_TYPE_ID = PET.ELEMENT_INFORMATION10
AND PBAD.ATTRIBUTE_NAME = 'PAY_CA_EARNINGS'
AND PBAD.LEGISLATION_CODE = 'CA'
AND PBAD.BUSINESS_GROUP_ID IS NULL
AND PBA.ATTRIBUTE_ID = PBAD.ATTRIBUTE_ID
AND PDB.DEFINED_BALANCE_ID = PBA.DEFINED_BALANCE_ID
AND PDB.BALANCE_TYPE_ID = PBT.BALANCE_TYPE_ID
AND PPA.PAYROLL_ACTION_ID = PAA.PAYROLL_ACTION_ID
AND PPA.ACTION_TYPE IN ('R'
, 'Q'
, 'V'
, 'B'
, 'I')
AND PEC.LEGISLATION_CODE = 'CA'
AND PEC.CLASSIFICATION_ID = PET.CLASSIFICATION_ID
AND NVL(PPA.DATE_EARNED
, PPA.EFFECTIVE_DATE) BETWEEN PET.EFFECTIVE_START_DATE
AND PET.EFFECTIVE_END_DATE

Columns