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_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, pbt1 PAY_BALANCE_TYPES_PK) USE_NL(paa, ppa, pec, pet, petl, pbt, pbt1 ) */ paa.assignment_action_id, paa.assignment_id, paa.tax_unit_id, pet.element_type_id, pet.classification_id, pec.classification_name, petl.reporting_name, pet.reporting_name base_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 ( pbt1.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 ( pbt1.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 ( pbt1.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 ( pbt1.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 ( pbt1.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)) , 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 ( pbt1.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 ,'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 ( pbt1.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 ,'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 ( pbt1.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 ( pbt1.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) )) , decode(pet.element_information12,NULL,0, pay_ca_balance_pkg.call_ca_balance_get_value ( pbt1.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) )) , 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_element_classifications pec ,pay_element_types_f pet ,pay_element_types_f_tl petl ,pay_balance_types pbt ,pay_balance_types pbt1 WHERE PET.element_type_id = PETL.element_type_id AND PETL.language = userenv('LANG') and (( pet.element_information12 is not null AND pbt1.balance_type_id = pet.element_information12) OR (pet.element_information12 is null ) ) AND pbt.balance_type_id = pet.element_information10 AND pet.element_information12 = pbt1.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 ('Earnings', 'Supplemental Earnings', 'Taxable Benefits' , 'Non-payroll Payments') 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 /*+ 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','V','B','I') )

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
, PBT1 PAY_BALANCE_TYPES_PK) USE_NL(PAA
, PPA
, PEC
, PET
, PETL
, PBT
, PBT1 ) */ PAA.ASSIGNMENT_ACTION_ID
, PAA.ASSIGNMENT_ID
, PAA.TAX_UNIT_ID
, PET.ELEMENT_TYPE_ID
, PET.CLASSIFICATION_ID
, PEC.CLASSIFICATION_NAME
, PETL.REPORTING_NAME
, PET.REPORTING_NAME BASE_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 ( PBT1.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 ( PBT1.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 ( PBT1.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 ( PBT1.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 ( PBT1.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))
, 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 ( PBT1.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
, '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 ( PBT1.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
, '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 ( PBT1.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 ( PBT1.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) ))
, DECODE(PET.ELEMENT_INFORMATION12
, NULL
, 0
, PAY_CA_BALANCE_PKG.CALL_CA_BALANCE_GET_VALUE ( PBT1.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) ))
, 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_ELEMENT_CLASSIFICATIONS PEC
, PAY_ELEMENT_TYPES_F PET
, PAY_ELEMENT_TYPES_F_TL PETL
, PAY_BALANCE_TYPES PBT
, PAY_BALANCE_TYPES PBT1
WHERE PET.ELEMENT_TYPE_ID = PETL.ELEMENT_TYPE_ID
AND PETL.LANGUAGE = USERENV('LANG')
AND (( PET.ELEMENT_INFORMATION12 IS NOT NULL
AND PBT1.BALANCE_TYPE_ID = PET.ELEMENT_INFORMATION12) OR (PET.ELEMENT_INFORMATION12 IS NULL ) )
AND PBT.BALANCE_TYPE_ID = PET.ELEMENT_INFORMATION10
AND PET.ELEMENT_INFORMATION12 = PBT1.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 ('EARNINGS'
, 'SUPPLEMENTAL EARNINGS'
, 'TAXABLE BENEFITS'
, 'NON-PAYROLL PAYMENTS')
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 /*+ 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'
, 'V'
, 'B'
, 'I') )