VIEW: APPS.PAY_CA_SOE_DEDN_BALANCES_RR_V

Source

View Text - Preformatted

SELECT paa.assignment_id, paa.assignment_action_id, paa.tax_unit_id, pet.element_type_id, pet.classification_id, pec.classification_name, petl.reporting_name, pet.reporting_name base_reporting_name, decode(hr_general.decode_lookup('CA_SOE_SHORT_NAME',upper(pet.reporting_name)),NULL,petl.reporting_name,hr_general.decode_lookup('CA_SOE_SHORT_NAME',upper(pet.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) ) CURRENT_VALUE , 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) ) QTD_VALUE , 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) ) PTD_VALUE , 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) ) MONTH_VALUE , 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) ) YTD_VALUE , 'DEDUCTION' FROM pay_balance_types pbt, pay_element_classifications pec , pay_element_types_f_tl petl, pay_element_types_f pet, pay_assignment_actions paa, pay_payroll_actions ppa 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 /*+ 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 /* commented by sneelapa, for bug 7375229 */ /* 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') )

View Text - HTML Formatted

SELECT PAA.ASSIGNMENT_ID
, PAA.ASSIGNMENT_ACTION_ID
, PAA.TAX_UNIT_ID
, PET.ELEMENT_TYPE_ID
, PET.CLASSIFICATION_ID
, PEC.CLASSIFICATION_NAME
, PETL.REPORTING_NAME
, PET.REPORTING_NAME BASE_REPORTING_NAME
, DECODE(HR_GENERAL.DECODE_LOOKUP('CA_SOE_SHORT_NAME'
, UPPER(PET.REPORTING_NAME))
, NULL
, PETL.REPORTING_NAME
, HR_GENERAL.DECODE_LOOKUP('CA_SOE_SHORT_NAME'
, UPPER(PET.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) ) CURRENT_VALUE
, 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) ) QTD_VALUE
, 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) ) PTD_VALUE
, 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) ) MONTH_VALUE
, 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) ) YTD_VALUE
, 'DEDUCTION'
FROM PAY_BALANCE_TYPES PBT
, PAY_ELEMENT_CLASSIFICATIONS PEC
, PAY_ELEMENT_TYPES_F_TL PETL
, PAY_ELEMENT_TYPES_F PET
, PAY_ASSIGNMENT_ACTIONS PAA
, PAY_PAYROLL_ACTIONS PPA
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 /*+ 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 /* COMMENTED BY SNEELAPA
, FOR BUG 7375229 */ /*
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') )