VIEW: APPS.PAY_US_EMP_TIME_IN_CITY_V

Source

View Text - Preformatted

SELECT pef.assignment_id, ctr.state_code, ctr.county_code, ctr.city_code, puc.city_name, ctr.jurisdiction_code, sum(fnd_number.canonical_to_number(pev2.screen_entry_value)), pef.effective_start_date, pef.effective_end_date from pay_element_entries_f pef, pay_element_links_f pel, pay_element_types_f pet, pay_us_emp_city_tax_rules_f ctr, pay_us_city_names puc, 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 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 pef.assignment_id = ctr.assignment_id and ctr.effective_start_date <= (select ss.effective_date from fnd_sessions ss where ss.session_id = USERENV('sessionid')) and ctr.effective_end_date >= (select ss.effective_date from fnd_sessions ss where ss.session_id = USERENV('sessionid')) and ctr.state_code = puc.state_code and ctr.county_code = puc.county_code and ctr.city_code = puc.city_code and ( puc.primary_flag = 'Y' or substr( puc.city_code,1,1) = 'U') and (pev1.element_entry_id = pef.element_entry_id and pev1.effective_start_date = pef.effective_start_date and pev1.effective_end_date = pef.effective_end_date and pev1.screen_entry_value is not null and ctr.jurisdiction_code = substr(pev1.screen_entry_value,1,11) 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') group by pef.assignment_id, ctr.state_code, ctr.county_code, ctr.city_code, puc.city_name, ctr.jurisdiction_code, pef.effective_start_date, pef.effective_end_date

View Text - HTML Formatted

SELECT PEF.ASSIGNMENT_ID
, CTR.STATE_CODE
, CTR.COUNTY_CODE
, CTR.CITY_CODE
, PUC.CITY_NAME
, CTR.JURISDICTION_CODE
, SUM(FND_NUMBER.CANONICAL_TO_NUMBER(PEV2.SCREEN_ENTRY_VALUE))
, PEF.EFFECTIVE_START_DATE
, PEF.EFFECTIVE_END_DATE
FROM PAY_ELEMENT_ENTRIES_F PEF
, PAY_ELEMENT_LINKS_F PEL
, PAY_ELEMENT_TYPES_F PET
, PAY_US_EMP_CITY_TAX_RULES_F CTR
, PAY_US_CITY_NAMES PUC
, 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 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 PEF.ASSIGNMENT_ID = CTR.ASSIGNMENT_ID
AND CTR.EFFECTIVE_START_DATE <= (SELECT SS.EFFECTIVE_DATE
FROM FND_SESSIONS SS
WHERE SS.SESSION_ID = USERENV('SESSIONID'))
AND CTR.EFFECTIVE_END_DATE >= (SELECT SS.EFFECTIVE_DATE
FROM FND_SESSIONS SS
WHERE SS.SESSION_ID = USERENV('SESSIONID'))
AND CTR.STATE_CODE = PUC.STATE_CODE
AND CTR.COUNTY_CODE = PUC.COUNTY_CODE
AND CTR.CITY_CODE = PUC.CITY_CODE
AND ( PUC.PRIMARY_FLAG = 'Y' OR SUBSTR( PUC.CITY_CODE
, 1
, 1) = 'U')
AND (PEV1.ELEMENT_ENTRY_ID = PEF.ELEMENT_ENTRY_ID
AND PEV1.EFFECTIVE_START_DATE = PEF.EFFECTIVE_START_DATE
AND PEV1.EFFECTIVE_END_DATE = PEF.EFFECTIVE_END_DATE
AND PEV1.SCREEN_ENTRY_VALUE IS NOT NULL
AND CTR.JURISDICTION_CODE = SUBSTR(PEV1.SCREEN_ENTRY_VALUE
, 1
, 11)
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') GROUP BY PEF.ASSIGNMENT_ID
, CTR.STATE_CODE
, CTR.COUNTY_CODE
, CTR.CITY_CODE
, PUC.CITY_NAME
, CTR.JURISDICTION_CODE
, PEF.EFFECTIVE_START_DATE
, PEF.EFFECTIVE_END_DATE