eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

VIEW: APPS.PAY_CA_PROV_EE_V

Source

View Text - Preformatted


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') )

View Text - HTML Formatted

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') )