VIEW: APPS.PAY_CA_SOE_DEDN_BALANCES_RBR_V

Source

View Text - Preformatted

SELECT /*+ ORDERED */ 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_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_DEDUCTIONS' 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 ('Q', 'R', '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 /* commented by sneelapa, for bug 7375229 */ /* AND prb.tax_unit_id = paa.tax_unit_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_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_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_DEDUCTIONS'
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 ('Q'
, 'R'
, '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 /* COMMENTED BY SNEELAPA
, FOR BUG 7375229 */ /*
AND PRB.TAX_UNIT_ID = PAA.TAX_UNIT_ID */
AND PRB.DEFINED_BALANCE_ID = PBA.DEFINED_BALANCE_ID
AND PRB.EFFECTIVE_DATE BETWEEN TRUNC(PPA.EFFECTIVE_DATE
, 'Y')
AND PPA.EFFECTIVE_DATE)