VIEW: APPS.PAY_US_BALANCE_VALUES_V

Source

View Text - Preformatted

SELECT paa.assignment_action_id, paa.assignment_id, paa.payroll_action_id, ppa.effective_date, pbt.balance_type_id, pbt.balance_name, pbd.balance_dimension_id, pbd.dimension_name, pbd.database_item_suffix, pbd.description dimension_description, pdb.defined_balance_id, pbd.route_id, Pay_Balance_Pkg.get_value(pdb.defined_balance_id, paa.assignment_action_id, paa.tax_unit_id, pac_jur.jurisdiction_code, NULL, pac_tax_group.org_information5, ppa.effective_date) value, pac_jur.jurisdiction_code, pac_tax_group.org_information5 tax_group FROM pay_payroll_actions ppa, pay_balance_types pbt, pay_balance_dimensions pbd, pay_defined_balances pdb, pay_assignment_actions paa, pay_us_asg_reporting pac_jur, hr_organization_information pac_tax_group WHERE paa.payroll_action_id = ppa.payroll_action_id AND pdb.balance_type_id = pbt.balance_type_id AND pdb.balance_dimension_id = pbd.balance_dimension_id AND paa.assignment_id = pac_jur.assignment_id AND paa.tax_unit_id = pac_tax_group.organization_id AND UPPER(pac_tax_group.org_information_context) = 'FEDERAL TAX RULES' and pac_jur.rowid = (select min(par.rowid) from pay_us_asg_reporting par where par.assignment_id = paa.assignment_id and decode(pbt.jurisdiction_level, 0,par.jurisdiction_code, substr(par.jurisdiction_code, 1, pbt.jurisdiction_level)) = decode(pbt.jurisdiction_level, 0,par.jurisdiction_code, substr(pac_jur.jurisdiction_code, 1, pbt.jurisdiction_level)) ) AND EXISTS (SELECT '' FROM pay_balance_feeds_f pbf, pay_run_result_values prrv, pay_run_results prr WHERE prr.assignment_action_id = paa.assignment_action_id AND prr.run_result_id = prrv.run_result_id AND prrv.input_value_id = pbf.input_value_id AND pbf.balance_type_id = pbt.balance_type_id AND ppa.effective_date BETWEEN pbf.effective_start_date AND pbf.effective_end_date ) AND NOT EXISTS (SELECT '' FROM ff_route_context_usages frcu, ff_contexts fc WHERE frcu.route_id = pbd.route_id AND fc.context_id = frcu.context_id AND fc.context_name NOT IN ('TAX_GROUP', 'JURISDICTION_CODE', 'TAX_UNIT_ID', 'ASSIGNMENT_ACTION_ID') )

View Text - HTML Formatted

SELECT PAA.ASSIGNMENT_ACTION_ID
, PAA.ASSIGNMENT_ID
, PAA.PAYROLL_ACTION_ID
, PPA.EFFECTIVE_DATE
, PBT.BALANCE_TYPE_ID
, PBT.BALANCE_NAME
, PBD.BALANCE_DIMENSION_ID
, PBD.DIMENSION_NAME
, PBD.DATABASE_ITEM_SUFFIX
, PBD.DESCRIPTION DIMENSION_DESCRIPTION
, PDB.DEFINED_BALANCE_ID
, PBD.ROUTE_ID
, PAY_BALANCE_PKG.GET_VALUE(PDB.DEFINED_BALANCE_ID
, PAA.ASSIGNMENT_ACTION_ID
, PAA.TAX_UNIT_ID
, PAC_JUR.JURISDICTION_CODE
, NULL
, PAC_TAX_GROUP.ORG_INFORMATION5
, PPA.EFFECTIVE_DATE) VALUE
, PAC_JUR.JURISDICTION_CODE
, PAC_TAX_GROUP.ORG_INFORMATION5 TAX_GROUP
FROM PAY_PAYROLL_ACTIONS PPA
, PAY_BALANCE_TYPES PBT
, PAY_BALANCE_DIMENSIONS PBD
, PAY_DEFINED_BALANCES PDB
, PAY_ASSIGNMENT_ACTIONS PAA
, PAY_US_ASG_REPORTING PAC_JUR
, HR_ORGANIZATION_INFORMATION PAC_TAX_GROUP
WHERE PAA.PAYROLL_ACTION_ID = PPA.PAYROLL_ACTION_ID
AND PDB.BALANCE_TYPE_ID = PBT.BALANCE_TYPE_ID
AND PDB.BALANCE_DIMENSION_ID = PBD.BALANCE_DIMENSION_ID
AND PAA.ASSIGNMENT_ID = PAC_JUR.ASSIGNMENT_ID
AND PAA.TAX_UNIT_ID = PAC_TAX_GROUP.ORGANIZATION_ID
AND UPPER(PAC_TAX_GROUP.ORG_INFORMATION_CONTEXT) = 'FEDERAL TAX RULES'
AND PAC_JUR.ROWID = (SELECT MIN(PAR.ROWID)
FROM PAY_US_ASG_REPORTING PAR
WHERE PAR.ASSIGNMENT_ID = PAA.ASSIGNMENT_ID
AND DECODE(PBT.JURISDICTION_LEVEL
, 0
, PAR.JURISDICTION_CODE
, SUBSTR(PAR.JURISDICTION_CODE
, 1
, PBT.JURISDICTION_LEVEL)) = DECODE(PBT.JURISDICTION_LEVEL
, 0
, PAR.JURISDICTION_CODE
, SUBSTR(PAC_JUR.JURISDICTION_CODE
, 1
, PBT.JURISDICTION_LEVEL)) )
AND EXISTS (SELECT ''
FROM PAY_BALANCE_FEEDS_F PBF
, PAY_RUN_RESULT_VALUES PRRV
, PAY_RUN_RESULTS PRR
WHERE PRR.ASSIGNMENT_ACTION_ID = PAA.ASSIGNMENT_ACTION_ID
AND PRR.RUN_RESULT_ID = PRRV.RUN_RESULT_ID
AND PRRV.INPUT_VALUE_ID = PBF.INPUT_VALUE_ID
AND PBF.BALANCE_TYPE_ID = PBT.BALANCE_TYPE_ID
AND PPA.EFFECTIVE_DATE BETWEEN PBF.EFFECTIVE_START_DATE
AND PBF.EFFECTIVE_END_DATE )
AND NOT EXISTS (SELECT ''
FROM FF_ROUTE_CONTEXT_USAGES FRCU
, FF_CONTEXTS FC
WHERE FRCU.ROUTE_ID = PBD.ROUTE_ID
AND FC.CONTEXT_ID = FRCU.CONTEXT_ID
AND FC.CONTEXT_NAME NOT IN ('TAX_GROUP'
, 'JURISDICTION_CODE'
, 'TAX_UNIT_ID'
, 'ASSIGNMENT_ACTION_ID') )