VIEW: APPS.PAY_CA_SOE_DEDN_BALANCES_V

Source

View Text - Preformatted

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

View Text - HTML Formatted

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