View: PAY_CA_SOE_DEDN_BALANCES_V

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

View Text

SELECT /*+ ORDERED INDEX(PAA PAY_ASSIGNMENT_ACTIONS_PK
, PPA PAY_PAYROLL_ACTIONS_PK
, PEC PAY_ELEMENT_CLASSIFICATION_UK2
, PET PAY_ELEMENT_TYPES_F_FK1
, PETL PAY_ELEMENT_TYPES_F_TL_PK
, PBT PAY_BALANCE_TYPES_PK) USE_NL(PAA
, PPA
, PEC
, PET
, PETL
, PBT ) */ PAA.ASSIGNMENT_ID
, PAA.ASSIGNMENT_ACTION_ID
, PAA.TAX_UNIT_ID
, PET.ELEMENT_TYPE_ID
, PET.CLASSIFICATION_ID
, PEC.CLASSIFICATION_NAME
, PETL.REPORTING_NAME
, DECODE(HR_GENERAL.DECODE_LOOKUP('CA_SOE_SHORT_NAME'
, UPPER(PETL.REPORTING_NAME))
, NULL
, PETL.REPORTING_NAME
, HR_GENERAL.DECODE_LOOKUP('CA_SOE_SHORT_NAME'
, UPPER(PETL.REPORTING_NAME)))
, PETL.ELEMENT_NAME
, HR_BALANCES.DECODE_BALANCE(PBT.BALANCE_TYPE_ID)
, PET.PROCESSING_PRIORITY
, 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) ))
, 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) )
, 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) )
, 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) )
, 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) )
, 'DEDUCTION'
FROM PAY_ASSIGNMENT_ACTIONS PAA
, PAY_PAYROLL_ACTIONS PPA
, PAY_ELEMENT_CLASSIFICATIONS PEC
, PAY_ELEMENT_TYPES_F PET
, PAY_ELEMENT_TYPES_F_TL PETL
, PAY_BALANCE_TYPES PBT
WHERE PET.ELEMENT_TYPE_ID = PETL.ELEMENT_TYPE_ID
AND PETL.LANGUAGE = USERENV('LANG')
AND PET.ELEMENT_INFORMATION10 = PBT.BALANCE_TYPE_ID
AND PPA.PAYROLL_ACTION_ID = PAA.PAYROLL_ACTION_ID
AND PPA.ACTION_TYPE IN ('Q'
, 'R'
, 'V'
, 'B'
, 'I')
AND PEC.CLASSIFICATION_NAME ||'' IN ('PRE-TAX DEDUCTIONS'
, 'INVOLUNTARY DEDUCTIONS'
, 'VOLUNTARY DEDUCTIONS' )
AND PEC.LEGISLATION_CODE = 'CA'
AND PEC.CLASSIFICATION_ID = PET.CLASSIFICATION_ID
AND PET.ELEMENT_INFORMATION10 IS NOT NULL
AND NVL(PPA.DATE_EARNED
, PPA.EFFECTIVE_DATE) BETWEEN PET.EFFECTIVE_START_DATE
AND PET.EFFECTIVE_END_DATE
AND EXISTS ( SELECT /*+ NO_UNNEST LEADING(ASG) */ 'X'
FROM PAY_PAYROLL_ACTIONS PACT
, PAY_ASSIGNMENT_ACTIONS ASG
, PAY_RUN_RESULTS RR
WHERE RR.ELEMENT_TYPE_ID + 0 = PET.ELEMENT_TYPE_ID
AND RR.ASSIGNMENT_ACTION_ID = ASG.ASSIGNMENT_ACTION_ID
AND ASG.ASSIGNMENT_ID = PAA.ASSIGNMENT_ID /*
AND ASG.TAX_UNIT_ID = PAA.TAX_UNIT_ID */
AND ASG.PAYROLL_ACTION_ID = PACT.PAYROLL_ACTION_ID
AND PACT.EFFECTIVE_DATE BETWEEN TRUNC(PPA.EFFECTIVE_DATE
, 'YEAR')
AND PPA.EFFECTIVE_DATE
AND PACT.ACTION_TYPE IN ('R'
, 'Q'
, 'B'
, 'I'
, 'V') ) UNION ALL SELECT PCFE.ASSIGNMENT_ID
, PCFE.ASSIGNMENT_ACTION_ID
, PCFE.TAX_UNIT_ID
, 0
, 0
, NULL
, PCFE.REPORTING_NAME
, DECODE(HR_GENERAL.DECODE_LOOKUP('CA_SOE_SHORT_NAME'
, UPPER(PCFE.REPORTING_NAME))
, NULL
, PCFE.REPORTING_NAME
, HR_GENERAL.DECODE_LOOKUP('CA_SOE_SHORT_NAME'
, UPPER(PCFE.REPORTING_NAME)))
, NULL
, PCFE.BALANCE_NAME
, DECODE(BASE_BALANCE_NAME
, 'FED WITHHELD'
, 1
, 'CPP EE WITHHELD'
, 2
, 'EI EE WITHHELD'
, 3
, 'PROV WITHHELD'
, 4
, '5' )
, DECODE(PCFE.ACTION_TYPE
, 'R'
, RUN_VALUE
, 'Q'
, RUN_VALUE
, 'P'
, CURRENT_VALUE
, 'U'
, CURRENT_VALUE
, 'V'
, RUN_VALUE )
, CURRENT_VALUE
, QTD_VALUE
, PTD_VALUE
, MONTH_VALUE
, YTD_VALUE
, 'TAX'
FROM PAY_CA_FED_EE_V PCFE
, PAY_ASSIGNMENT_ACTIONS PAA
WHERE BASE_BALANCE_NAME IN ('FED WITHHELD'
, 'CPP EE WITHHELD'
, 'EI EE WITHHELD'
, 'PROV WITHHELD')
AND PAA.ASSIGNMENT_ACTION_ID = PCFE.ASSIGNMENT_ACTION_ID UNION ALL SELECT PCPE.ASSIGNMENT_ID
, PCPE.ASSIGNMENT_ACTION_ID
, PCPE.TAX_UNIT_ID
, 0
, 0
, NULL
, PCPE.REPORTING_NAME
, DECODE(HR_GENERAL.DECODE_LOOKUP('CA_SOE_SHORT_NAME'
, UPPER(PCPE.REPORTING_NAME))
, NULL
, PCPE.REPORTING_NAME
, HR_GENERAL.DECODE_LOOKUP('CA_SOE_SHORT_NAME'
, UPPER(PCPE.REPORTING_NAME)))
, NULL
, PCPE.BALANCE_NAME
, DECODE(PCPE.BASE_BALANCE_NAME
, 'PROV WITHHELD'
, 6
, 'QPP EE WITHHELD'
, 7
, 'PPIP EE WITHHELD'
, 8
, '9')
, DECODE(PCPE.ACTION_TYPE
, 'R'
, RUN_VALUE
, 'Q'
, RUN_VALUE
, 'P'
, CURRENT_VALUE
, 'U'
, CURRENT_VALUE
, 'V'
, RUN_VALUE )
, CURRENT_VALUE
, QTD_VALUE
, PTD_VALUE
, MONTH_VALUE
, YTD_VALUE
, 'TAX'
FROM PAY_CA_PROV_EE_V PCPE
, PAY_ASSIGNMENT_ACTIONS PAA
WHERE (PCPE.BASE_BALANCE_NAME = 'PROV WITHHELD' OR PCPE.BASE_BALANCE_NAME = 'QPP EE WITHHELD' OR PCPE.BASE_BALANCE_NAME = 'PPIP EE WITHHELD')
AND PAA.ASSIGNMENT_ACTION_ID = PCPE.ASSIGNMENT_ACTION_ID

Columns