AP EBTax Tax Rates Report
SELECT AL1.state_code,
AL1.county,
AL1.tax_jurisdiction,
AL1.percentage_rate,
AL1.effective_from,
AL1.effective_to,
AL1.active_flag,
AL1.last_update,
AL1.last_updt_user_id,
AL1.last_updt_name
FROM (SELECT DISTINCT '1-STATE' TYPE,
ST.geography_name State_Code,
Upper(flv.meaning) State_Name,
NULL County,
NULL City,
ZR.tax_jurisdiction_code Tax_Jurisdiction,
ZR.tax_rate_code Tax_Rate_Code,
ZR.effective_from Effective_From,
ZR.effective_to Effective_To,
ZR.tax_regime_code Tax_Regime,
ZR.tax Tax,
ZR.tax_status_code Tax_Status,
ZR.rate_type_code Rate_Type,
ZR.percentage_rate Percentage_Rate,
ZR.active_flag Active_Flag,
ZR.default_rate_flag Default_Rate_Flag,
ZR.default_flg_effective_from Dflt_Flg_Eff_From,
ZR.default_flg_effective_to Dflt_Flg_Eff_To,
ZR.creation_date Creation_Date,
ZR.last_update_date Last_Update,
ZR.user_id Last_Updt_User_Id,
ZR.username Last_Updt_Name,
NULL County2,
NULL City2
FROM fnd_lookup_values FLV,
zx_jurisdictions_b ZJ,
hz_geographies ST,
(SELECT RTB.tax_jurisdiction_code,
RTB.tax_rate_code,
RTB.effective_from,
RTB.effective_to,
RTB.tax_regime_code,
RTB.tax,
RTB.tax_status_code,
RTB.rate_type_code,
RTB.percentage_rate,
RTB.active_flag,
RTB.default_rate_flag,
RTB.default_flg_effective_from,
RTB.default_flg_effective_to,
RTB.creation_date,
RTB.last_update_date,
USR.user_id,
USR.username
FROM zx_rates_b RTB,
per_users USR
WHERE Nvl(effective_to, SYSDATE + 1) > SYSDATE
AND Nvl(default_flg_effective_to, SYSDATE + 1) > SYSDATE
AND USR.username = RTB.last_updated_by) zr
WHERE flv.lookup_type = 'STATE'
AND flv.LANGUAGE = 'US'
AND FLV.view_application_id = 222
AND Upper(ST.geography_name) = FLV.lookup_code
AND ZJ.zone_geography_id = ST.geography_id
AND ZJ.tax_jurisdiction_code = ZR.tax_jurisdiction_code
AND 'US - SUT' = ZR.tax_regime_code
AND 'STATE' = ZR.tax
--
--
UNION
SELECT DISTINCT '2-COUNTY' TYPE,
HG.geography_element2 State_Code,
Upper(flv.meaning) State_Name,
Upper(HG.geography_code) County,
NULL City,
ZR.tax_jurisdiction_code Tax_Jurisdiction,
ZR.tax_rate_code Tax_Rate_Code,
ZR.effective_from Effective_From,
ZR.effective_to Effective_To,
ZR.tax_regime_code Tax_Regime,
ZR.tax Tax,
ZR.tax_status_code Tax_Status,
ZR.rate_type_code Rate_Type,
ZR.percentage_rate Percentage_Rate,
ZR.active_flag Active_Flag,
ZR.default_rate_flag Default_Rate_Flag,
ZR.default_flg_effective_from Dflt_Flg_Eff_From,
ZR.default_flg_effective_to Dflt_Flg_Eff_To,
ZR.creation_date Creation_Date,
ZR.last_update_date Last_Update,
ZR.user_id Last_Updt_User_Id,
ZR.username Last_Updt_Name,
Nvl(hg.geography_code, '0') County2,
'0' City2
FROM hz_geographies HG,
zx_jurisdictions_b ZJ,
fnd_lookup_values FLV,
(SELECT RTB.tax_jurisdiction_code,
RTB.tax_rate_code,
RTB.effective_from,
RTB.effective_to,
RTB.tax_regime_code,
RTB.tax,
RTB.tax_status_code,
RTB.rate_type_code,
RTB.percentage_rate,
RTB.active_flag,
RTB.default_rate_flag,
RTB.default_flg_effective_from,
RTB.default_flg_effective_to,
RTB.creation_date,
RTB.last_update_date,
USR.user_id,
USR.username
FROM zx_rates_b RTB,
per_users USR
WHERE Nvl(effective_to, SYSDATE + 1) > SYSDATE
AND Nvl(default_flg_effective_to, SYSDATE + 1) > SYSDATE
AND USR.username = RTB.last_updated_by) ZR
WHERE HG.geography_element2 = FLV.lookup_code
AND FLV.lookup_type = 'STATE'
AND FLV.LANGUAGE = 'US'
AND ZJ.zone_geography_id = HG.geography_id
AND ZJ.tax_jurisdiction_code = ZR.tax_jurisdiction_code
AND 'US - SUT' = ZR.tax_regime_code
AND 'COUNTY' = zr.tax
--
--
UNION
SELECT DISTINCT '3-CITY' TYPE,
CITY.geography_element2_code State_Code,
Upper(flv.meaning) State_Name,
Upper(CITY.geography_element3) County,
Upper(CITY.geography_name) City,
ZR.tax_jurisdiction_code
Tax_Jurisdiction,
ZR.tax_rate_code Tax_Rate_Code,
ZR.effective_from Effective_From,
ZR.effective_to Effective_To,
ZR.tax_regime_code Tax_Regime,
ZR.tax Tax,
ZR.tax_status_code Tax_Status,
ZR.rate_type_code Rate_Type,
ZR.percentage_rate Percentage_Rate
,
ZR.active_flag
Active_Flag,
ZR.default_rate_flag
Default_Rate_Flag,
ZR.default_flg_effective_from
Dflt_Flg_Eff_From,
ZR.default_flg_effective_to Dflt_Flg_Eff_To
,
ZR.creation_date
Creation_Date,
ZR.last_update_date Last_Update,
ZR.user_id
Last_Updt_User_Id,
ZR.username Last_Updt_Name,
Nvl(Upper(CITY.geography_element3), '0') County2,
Nvl(Upper(CITY.geography_name), '0') City2
FROM hz_geographies CITY,
zx_jurisdictions_b ZJ,
fnd_lookup_values FLV,
(SELECT RTB.tax_jurisdiction_code,
RTB.tax_rate_code,
RTB.effective_from,
RTB.effective_to,
RTB.tax_regime_code,
RTB.tax,
RTB.tax_status_code,
RTB.rate_type_code,
RTB.percentage_rate,
RTB.active_flag,
RTB.default_rate_flag,
RTB.default_flg_effective_from,
RTB.default_flg_effective_to,
RTB.creation_date,
RTB.last_update_date,
USR.user_id,
USR.username
FROM zx_rates_b RTB,
per_users USR
WHERE Nvl(effective_to, SYSDATE + 1) > SYSDATE
AND Nvl(default_flg_effective_to, SYSDATE + 1) > SYSDATE
AND USR.username = RTB.last_updated_by) ZR
WHERE CITY.geography_element2_code = FLV.lookup_code
AND FLV.lookup_type = 'STATE'
AND flv.LANGUAGE = 'US'
AND ZJ.zone_geography_id = CITY.geography_id
AND ZJ.tax_jurisdiction_code = ZR.tax_jurisdiction_code
AND 'US - SUT' = zr.tax_regime_code
AND 'CITY' = zr.tax) AL1
WHERE 1 = 1
AND Al1.state_code = Nvl(:p_state_code, Al1.state_code)
ORDER BY al1.county,
al1.tax_jurisdiction
No comments:
Post a Comment