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_US_EMP_TIME_IN_STATE_V

Source

View Text - Preformatted


SELECT pef.assignment_id, pest.state_code, pus.state_name, pest.jurisdiction_code, pest.filing_status_code, fcl.meaning, pest.withholding_allowances, pest.sit_additional_tax, pest.sit_override_amount, pest.sit_override_rate, sum(fnd_number.canonical_to_number(pev2.screen_entry_value)) time_in_state, pef.effective_start_date, pef.effective_end_date from pay_element_entries_f pef, pay_us_emp_state_tax_rules_f pest, fnd_common_lookups fcl, pay_us_states pus, pay_state_rules psr, pay_element_links_f pel, pay_element_types_f pet, pay_element_entry_values_f pev1, pay_element_entry_values_f pev2, pay_input_values_f piv1, pay_input_values_f piv2 WHERE pef.creator_type = 'UT' and pef.element_link_id = pel.element_link_id and pel.element_type_id = pet.element_type_id and pet.element_name = 'VERTEX' and pest.effective_start_date <= (select ss.effective_date from fnd_sessions ss where ss.session_id = USERENV('SESSIONID')) and pef.effective_start_date <= (select ss.effective_date from fnd_sessions ss where ss.session_id = USERENV('SESSIONID')) and pef.effective_end_date >= (select ss.effective_date from fnd_sessions ss where ss.session_id = USERENV('SESSIONID')) and pest.effective_end_date >= (select ss.effective_date from fnd_sessions ss where ss.session_id = USERENV('SESSIONID')) and pef.assignment_id = pest.assignment_id and pest.state_code = pus.state_code and psr.state_code = pus.state_abbrev and (pef.element_entry_id = pev1.element_entry_id and pef.effective_start_date = pev1.effective_start_date and pef.effective_end_date = pev1.effective_end_date and pev1.screen_entry_value is not null and pest.state_code = substr(pev1.screen_entry_value,1,2) and pev1.input_value_id = piv1.input_value_id and piv1.name = 'Jurisdiction') and (pev2.element_entry_id = pev1.element_entry_id and pev2.effective_start_date = pev1.effective_start_date and pev2.effective_end_date = pev1.effective_end_date and pev2.screen_entry_value is not null and piv2.input_value_id = pev2.input_value_id and piv2.name = 'Percentage') and fcl.application_id = 800 and fcl.lookup_type = psr.fs_lookup_type and lpad(fcl.lookup_code,2,0) = pest.filing_status_code group by pef.assignment_id, pest.state_code, pus.state_name, pest.jurisdiction_code, pest.filing_status_code, fcl.meaning, pest.withholding_allowances, pest.sit_additional_tax, pest.sit_override_amount, pest.sit_override_rate, pef.effective_start_date, pef.effective_end_date

View Text - HTML Formatted

SELECT PEF.ASSIGNMENT_ID
, PEST.STATE_CODE
, PUS.STATE_NAME
, PEST.JURISDICTION_CODE
, PEST.FILING_STATUS_CODE
, FCL.MEANING
, PEST.WITHHOLDING_ALLOWANCES
, PEST.SIT_ADDITIONAL_TAX
, PEST.SIT_OVERRIDE_AMOUNT
, PEST.SIT_OVERRIDE_RATE
, SUM(FND_NUMBER.CANONICAL_TO_NUMBER(PEV2.SCREEN_ENTRY_VALUE)) TIME_IN_STATE
, PEF.EFFECTIVE_START_DATE
, PEF.EFFECTIVE_END_DATE
FROM PAY_ELEMENT_ENTRIES_F PEF
, PAY_US_EMP_STATE_TAX_RULES_F PEST
, FND_COMMON_LOOKUPS FCL
, PAY_US_STATES PUS
, PAY_STATE_RULES PSR
, PAY_ELEMENT_LINKS_F PEL
, PAY_ELEMENT_TYPES_F PET
, PAY_ELEMENT_ENTRY_VALUES_F PEV1
, PAY_ELEMENT_ENTRY_VALUES_F PEV2
, PAY_INPUT_VALUES_F PIV1
, PAY_INPUT_VALUES_F PIV2
WHERE PEF.CREATOR_TYPE = 'UT'
AND PEF.ELEMENT_LINK_ID = PEL.ELEMENT_LINK_ID
AND PEL.ELEMENT_TYPE_ID = PET.ELEMENT_TYPE_ID
AND PET.ELEMENT_NAME = 'VERTEX'
AND PEST.EFFECTIVE_START_DATE <= (SELECT SS.EFFECTIVE_DATE
FROM FND_SESSIONS SS
WHERE SS.SESSION_ID = USERENV('SESSIONID'))
AND PEF.EFFECTIVE_START_DATE <= (SELECT SS.EFFECTIVE_DATE
FROM FND_SESSIONS SS
WHERE SS.SESSION_ID = USERENV('SESSIONID'))
AND PEF.EFFECTIVE_END_DATE >= (SELECT SS.EFFECTIVE_DATE
FROM FND_SESSIONS SS
WHERE SS.SESSION_ID = USERENV('SESSIONID'))
AND PEST.EFFECTIVE_END_DATE >= (SELECT SS.EFFECTIVE_DATE
FROM FND_SESSIONS SS
WHERE SS.SESSION_ID = USERENV('SESSIONID'))
AND PEF.ASSIGNMENT_ID = PEST.ASSIGNMENT_ID
AND PEST.STATE_CODE = PUS.STATE_CODE
AND PSR.STATE_CODE = PUS.STATE_ABBREV
AND (PEF.ELEMENT_ENTRY_ID = PEV1.ELEMENT_ENTRY_ID
AND PEF.EFFECTIVE_START_DATE = PEV1.EFFECTIVE_START_DATE
AND PEF.EFFECTIVE_END_DATE = PEV1.EFFECTIVE_END_DATE
AND PEV1.SCREEN_ENTRY_VALUE IS NOT NULL
AND PEST.STATE_CODE = SUBSTR(PEV1.SCREEN_ENTRY_VALUE
, 1
, 2)
AND PEV1.INPUT_VALUE_ID = PIV1.INPUT_VALUE_ID
AND PIV1.NAME = 'JURISDICTION')
AND (PEV2.ELEMENT_ENTRY_ID = PEV1.ELEMENT_ENTRY_ID
AND PEV2.EFFECTIVE_START_DATE = PEV1.EFFECTIVE_START_DATE
AND PEV2.EFFECTIVE_END_DATE = PEV1.EFFECTIVE_END_DATE
AND PEV2.SCREEN_ENTRY_VALUE IS NOT NULL
AND PIV2.INPUT_VALUE_ID = PEV2.INPUT_VALUE_ID
AND PIV2.NAME = 'PERCENTAGE')
AND FCL.APPLICATION_ID = 800
AND FCL.LOOKUP_TYPE = PSR.FS_LOOKUP_TYPE
AND LPAD(FCL.LOOKUP_CODE
, 2
, 0) = PEST.FILING_STATUS_CODE GROUP BY PEF.ASSIGNMENT_ID
, PEST.STATE_CODE
, PUS.STATE_NAME
, PEST.JURISDICTION_CODE
, PEST.FILING_STATUS_CODE
, FCL.MEANING
, PEST.WITHHOLDING_ALLOWANCES
, PEST.SIT_ADDITIONAL_TAX
, PEST.SIT_OVERRIDE_AMOUNT
, PEST.SIT_OVERRIDE_RATE
, PEF.EFFECTIVE_START_DATE
, PEF.EFFECTIVE_END_DATE