The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT fnd_global.local_chr(13) || fnd_global.local_chr(10) INTO EOL
FROM dual;
SELECT fnd_global.local_chr(13) || fnd_global.local_chr(10) INTO EOL
FROM dual;
SELECT faic1.context Jurisdiction_code
FROM pay_assignment_actions paa, -- YREND PAA
pay_payroll_actions ppa, -- YREND PPA
ff_contexts fc1, -- FOR CITY CONTEXT
ff_archive_items fai1, -- CITY
ff_archive_item_contexts faic1, -- CITY_CONTEXT
ff_database_items fdi1 --DATABASE_ITEMS FOR CITY_WITHHELD
WHERE paa.assignment_action_id = c_asgn_act_id
AND ppa.payroll_action_id = paa.payroll_action_id
AND fc1.context_name = 'JURISDICTION_CODE'
AND faic1.context_id = fc1.context_id
AND fdi1.user_name = 'A_CITY_WITHHELD_PER_JD_GRE_YTD'
AND fdi1.user_entity_id = fai1.user_entity_id
AND fai1.context1 = paa.assignment_action_id
AND fai1.archive_item_id = faic1.archive_item_id
AND ltrim(rtrim(faic1.context)) like c_state_code||'%'
AND c_locality_code IS NULL
AND rtrim(ltrim(fai1.value)) <> '0'
AND EXISTS ( SELECT 'x' from pay_us_city_tax_info_f puctif
WHERE puctif.jurisdiction_code = ltrim(rtrim(faic1.context))
AND puctif.effective_start_date < ppa.effective_date
AND puctif.effective_end_date >= ppa.effective_date
)
UNION
SELECT faic1.context Jurisdiction_code
FROM pay_assignment_actions paa, -- YREND PAA
pay_payroll_actions ppa, -- YREND PPA
ff_contexts fc1, -- FOR CITY CONTEXT
ff_archive_items fai1, -- CITY
ff_archive_item_contexts faic1, -- CITY_CONTEXT
ff_database_items fdi1 --DATABASE_ITEMS FOR CITY_WITHHELD
WHERE paa.assignment_action_id = c_asgn_act_id
AND ppa.payroll_action_id = paa.payroll_action_id
AND fc1.context_name = 'JURISDICTION_CODE'
AND faic1.context_id = fc1.context_id
AND fdi1.user_name = 'A_CITY_WITHHELD_PER_JD_GRE_YTD'
AND fdi1.user_entity_id = fai1.user_entity_id
AND fai1.context1 = paa.assignment_action_id
AND fai1.archive_item_id = faic1.archive_item_id
AND substr(ltrim(rtrim(faic1.context)),1,2) = c_state_code
AND substr(ltrim(rtrim(faic1.context)),8,4) = substr(c_locality_code,8,4)
AND c_locality_code IS NOT NULL
AND rtrim(ltrim(fai1.value)) <> '0'
AND EXISTS ( SELECT 'x' from pay_us_city_tax_info_f puctif
WHERE puctif.jurisdiction_code = ltrim(rtrim(faic1.context))
AND puctif.effective_start_date < ppa.effective_date
AND puctif.effective_end_date >= ppa.effective_date
)
-- Bug # 6117216 SD Reporting Changes START
UNION
SELECT faic1.context Jurisdiction_code
FROM pay_assignment_actions paa, -- YREND PAA
pay_payroll_actions ppa, -- YREND PPA
ff_contexts fc1, -- FOR CITY CONTEXT
ff_archive_items fai1, -- CITY
ff_archive_item_contexts faic1, -- CITY_CONTEXT
ff_database_items fdi1 --DATABASE_ITEMS FOR CITY_WITHHELD
WHERE paa.assignment_action_id = c_asgn_act_id
AND ppa.payroll_action_id = paa.payroll_action_id
AND fc1.context_name = 'JURISDICTION_CODE'
AND faic1.context_id = fc1.context_id
AND fdi1.user_name = 'A_SCHOOL_WITHHELD_PER_JD_GRE_YTD'
AND fdi1.user_entity_id = fai1.user_entity_id
AND fai1.context1 = paa.assignment_action_id
AND fai1.archive_item_id = faic1.archive_item_id
AND ltrim(rtrim(faic1.context)) like c_state_code||'%'
-- AND c_locality_code IS NULL
AND rtrim(ltrim(fai1.value)) <> '0'
/* To Be Modified Later */
/* AND EXISTS ( SELECT 'x' from pay_us_city_tax_info_f puctif
WHERE puctif.jurisdiction_code = ltrim(rtrim(faic1.context))
AND puctif.effective_start_date < ppa.effective_date
AND puctif.effective_end_date >= ppa.effective_date
)*/;
SELECT c.city_name city_name,
n.county_name county_name,
'C' tax_type,
a.city_information1 city_code
from pay_us_city_tax_info_f a,
pay_us_city_names c,
pay_us_counties n
where sysdate between a.effective_start_date and a.effective_end_date
and a.jurisdiction_code = c_jurisdiction_code
and c.primary_flag = 'Y'
and a.city_tax = 'Y'
and c.city_code = substr(a.jurisdiction_code,8,4)
and c.county_code = substr(a.jurisdiction_code,4,3)
and c.state_code = substr(a.jurisdiction_code,1,2)
and c.county_code = n.county_code
and c.state_code = n.state_code;
SELECT distinct c.school_dst_name,
'SD' tax_type,
substr(c_jurisdiction_code,4,5) city_code
from pay_us_city_school_dsts c
where c.school_dst_code = substr(c_jurisdiction_code,4,5)
and c.state_code = substr(c_jurisdiction_code,1,2);
ltr_ue_name_table.delete;
SELECT fnd_global.local_chr(13) || fnd_global.local_chr(10) INTO EOL
FROM dual;
ltr_local_record.delete;
ltr_ue_locality.delete;