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_SOE_EARN_BALANCES_RBR_V

Source

View Text - Preformatted


SELECT /*+ ORDERED */ paa.assignment_action_id, paa.assignment_id, paa.tax_unit_id, pet.element_type_id, pet.classification_id, pec.classification_name, petl.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_assignment_actions paa, pay_payroll_actions ppa, pay_bal_attribute_definitions pbad, pay_balance_attributes pba, pay_defined_balances pdb, pay_balance_types pbt, pay_element_classifications pec , pay_element_types_f pet, pay_element_types_f_tl petl WHERE pet.element_type_id = petl.element_type_id AND petl.language = userenv('LANG') AND (pet.legislation_code = 'CA' OR pet.business_group_id = ppa.business_group_id ) 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 AND EXISTS ( SELECT 1 FROM pay_run_balances prb WHERE prb.assignment_id = paa.assignment_id AND prb.defined_balance_id = pba.defined_balance_id AND prb.effective_date BETWEEN TRUNC(ppa.effective_date , 'Y') AND ppa.effective_date)

View Text - HTML Formatted

SELECT /*+ ORDERED */ PAA.ASSIGNMENT_ACTION_ID
, PAA.ASSIGNMENT_ID
, PAA.TAX_UNIT_ID
, PET.ELEMENT_TYPE_ID
, PET.CLASSIFICATION_ID
, PEC.CLASSIFICATION_NAME
, PETL.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_ASSIGNMENT_ACTIONS PAA
, PAY_PAYROLL_ACTIONS PPA
, PAY_BAL_ATTRIBUTE_DEFINITIONS PBAD
, PAY_BALANCE_ATTRIBUTES PBA
, PAY_DEFINED_BALANCES PDB
, PAY_BALANCE_TYPES PBT
, PAY_ELEMENT_CLASSIFICATIONS PEC
, PAY_ELEMENT_TYPES_F PET
, PAY_ELEMENT_TYPES_F_TL PETL
WHERE PET.ELEMENT_TYPE_ID = PETL.ELEMENT_TYPE_ID
AND PETL.LANGUAGE = USERENV('LANG')
AND (PET.LEGISLATION_CODE = 'CA' OR PET.BUSINESS_GROUP_ID = PPA.BUSINESS_GROUP_ID )
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
AND EXISTS ( SELECT 1
FROM PAY_RUN_BALANCES PRB
WHERE PRB.ASSIGNMENT_ID = PAA.ASSIGNMENT_ID
AND PRB.DEFINED_BALANCE_ID = PBA.DEFINED_BALANCE_ID
AND PRB.EFFECTIVE_DATE BETWEEN TRUNC(PPA.EFFECTIVE_DATE
, 'Y')
AND PPA.EFFECTIVE_DATE)