View: PAY_CA_PROV_EE_V

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

View Text

SELECT PAA.ASSIGNMENT_ID
, PAA.TAX_UNIT_ID
, PAA.ASSIGNMENT_ACTION_ID
, PAA.ACTION_STATUS
, PPA.PAYROLL_ACTION_ID
, PPA.ACTION_TYPE
, PPA.EFFECTIVE_DATE
, PBS.ATTRIBUTE_1
, PBTTL.BALANCE_NAME
, PBTTL.REPORTING_NAME||'('||PCP.PROVINCE_ABBREV||')'
, PBT.REPORTING_NAME||'('||PCP.PROVINCE_ABBREV||')' BASE_REPORTING_NAME
, PBS.BUSINESS_GROUP_ID
, PBS.LEGISLATION_CODE
, PCP.PROVINCE_ABBREV
, PAY_CA_BALANCE_PKG.CALL_CA_BALANCE_GET_VALUE( PBS.ATTRIBUTE_1
, 'CURRENT'
, PAA.ASSIGNMENT_ACTION_ID
, PAA.ASSIGNMENT_ID
, PPA.EFFECTIVE_DATE
, PAY_CA_BALANCE_VIEW_PKG.GET_SESSION_VAR('REPORT_LEVEL')
, PAA.TAX_UNIT_ID
, PAY_CA_BALANCE_VIEW_PKG.GET_SESSION_VAR('BUSINESS_GROUP')
, PCP.PROVINCE_ABBREV) CURRENT_VALUE
, PAY_CA_BALANCE_PKG.CALL_CA_BALANCE_GET_VALUE( PBS.ATTRIBUTE_1
, 'RUN'
, PAA.ASSIGNMENT_ACTION_ID
, PAA.ASSIGNMENT_ID
, PPA.EFFECTIVE_DATE
, PAY_CA_BALANCE_VIEW_PKG.GET_SESSION_VAR('REPORT_LEVEL')
, PAA.TAX_UNIT_ID
, PAY_CA_BALANCE_VIEW_PKG.GET_SESSION_VAR('BUSINESS_GROUP')
, PCP.PROVINCE_ABBREV) RUN_VALUE
, PAY_CA_BALANCE_PKG.CALL_CA_BALANCE_GET_VALUE( PBS.ATTRIBUTE_1
, 'PYDATE'
, PAA.ASSIGNMENT_ACTION_ID
, PAA.ASSIGNMENT_ID
, PPA.EFFECTIVE_DATE
, PAY_CA_BALANCE_VIEW_PKG.GET_SESSION_VAR('REPORT_LEVEL')
, PAA.TAX_UNIT_ID
, PAY_CA_BALANCE_VIEW_PKG.GET_SESSION_VAR('BUSINESS_GROUP')
, PCP.PROVINCE_ABBREV) PYDATE_VALUE
, PAY_CA_BALANCE_PKG.CALL_CA_BALANCE_GET_VALUE( PBS.ATTRIBUTE_1
, 'MONTH'
, PAA.ASSIGNMENT_ACTION_ID
, PAA.ASSIGNMENT_ID
, PPA.EFFECTIVE_DATE
, PAY_CA_BALANCE_VIEW_PKG.GET_SESSION_VAR('REPORT_LEVEL')
, PAA.TAX_UNIT_ID
, PAY_CA_BALANCE_VIEW_PKG.GET_SESSION_VAR('BUSINESS_GROUP')
, PCP.PROVINCE_ABBREV) MONTH_VALUE
, PAY_CA_BALANCE_PKG.CALL_CA_BALANCE_GET_VALUE( PBS.ATTRIBUTE_1
, 'QTD'
, PAA.ASSIGNMENT_ACTION_ID
, PAA.ASSIGNMENT_ID
, PPA.EFFECTIVE_DATE
, PAY_CA_BALANCE_VIEW_PKG.GET_SESSION_VAR('REPORT_LEVEL')
, PAA.TAX_UNIT_ID
, PAY_CA_BALANCE_VIEW_PKG.GET_SESSION_VAR('BUSINESS_GROUP')
, PCP.PROVINCE_ABBREV) QTD_VALUE
, PAY_CA_BALANCE_PKG.CALL_CA_BALANCE_GET_VALUE( PBS.ATTRIBUTE_1
, 'PTD'
, PAA.ASSIGNMENT_ACTION_ID
, PAA.ASSIGNMENT_ID
, PPA.EFFECTIVE_DATE
, PAY_CA_BALANCE_VIEW_PKG.GET_SESSION_VAR('REPORT_LEVEL')
, PAA.TAX_UNIT_ID
, PAY_CA_BALANCE_VIEW_PKG.GET_SESSION_VAR('BUSINESS_GROUP')
, PCP.PROVINCE_ABBREV) PTD_VALUE
, PAY_CA_BALANCE_PKG.CALL_CA_BALANCE_GET_VALUE( PBS.ATTRIBUTE_1
, 'YTD'
, PAA.ASSIGNMENT_ACTION_ID
, PAA.ASSIGNMENT_ID
, PPA.EFFECTIVE_DATE
, PAY_CA_BALANCE_VIEW_PKG.GET_SESSION_VAR('REPORT_LEVEL')
, PAA.TAX_UNIT_ID
, PAY_CA_BALANCE_VIEW_PKG.GET_SESSION_VAR('BUSINESS_GROUP')
, PCP.PROVINCE_ABBREV) YTD_VALUE
FROM PAY_PAYROLL_ACTIONS PPA
, PAY_ASSIGNMENT_ACTIONS PAA
, PAY_BALANCE_SETS PBS
, PAY_CA_PROVINCES_V PCP
, PAY_BALANCE_TYPES PBT
, PAY_BALANCE_TYPES_TL PBTTL
WHERE PPA.PAYROLL_ACTION_ID = PAA.PAYROLL_ACTION_ID
AND PPA.ACTION_TYPE IN ('Q'
, 'R'
, 'V'
, 'B'
, 'I')
AND PBS.ATTRIBUTE_1 = PBT.BALANCE_NAME
AND PBT.LEGISLATION_CODE = 'CA'
AND PBT.BALANCE_TYPE_ID = PBTTL.BALANCE_TYPE_ID
AND PBTTL.LANGUAGE = USERENV('LANG')
AND SUBSTR(PBS.BALANCE_SET_NAME
, 1
, 7) = 'PROV_EE'
AND EXISTS (SELECT 'X'
FROM PAY_CA_LEGISLATION_INFO PLI
WHERE PLI.JURISDICTION_CODE = PCP.PROVINCE_ABBREV
AND PLI.INFORMATION_TYPE = 'TAX_RELATIONSHIPS'
AND PPA.EFFECTIVE_DATE BETWEEN PLI.START_DATE
AND PLI.END_DATE
AND PLI.INFORMATION_VALUE = DECODE(SUBSTR(PBS.ATTRIBUTE_1
, 1
, 2)
, 'FE'
, 'FED'
, 'EI'
, 'EIM'
, 'CP'
, 'CPP'
, 'QP'
, 'QPP'
, 'PR'
, 'PRV'
, 'PP'
, 'PPIP') )

Columns