[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select puw.assignment_action_id control_number,
nvl(ssn,'Applied For') SSN,
first_name||
decode(middle_name,null,' ',
' '||substr(middle_name,1,1)||' ') ||
pre_name_adjunt emp_name,
last_name , -- Bug 4523389
hr_us_w2_rep.get_per_item(p_asg_action_id,
'A_PER_SUFFIX') emp_suffix,
/* Bug 5575567
decode(pa.address_line1,null,null,pa.address_line1 ||'\r')||
decode(pa.address_line2,null,null,pa.address_line2||'\r') ||
decode(pa.address_line3,null,null,pa.address_line3||'\r') ||
decode(pa.town_or_city,null,null,pa.town_or_city ||' ')||
decode(pa.region_2,null,null,pa.region_2||' ')|| pa.postal_code employee_address,
*/
decode(W2_WAGES_TIPS_COMPENSATION,0,'',W2_WAGES_TIPS_COMPENSATION) wages_tips_compensation,
decode(W2_FED_IT_WITHHELD,0,'',W2_FED_IT_WITHHELD) fit_withheld,
decode(W2_SOCIAL_SECURITY_WAGES,0,'',W2_SOCIAL_SECURITY_WAGES) ss_wages,
decode(W2_SST_WITHHELD,0,'',W2_SST_WITHHELD) ss_withheld,
decode(W2_MED_WAGES_TIPS,0,'',W2_MED_WAGES_TIPS) med_wages,
decode(W2_MED_TAX_WITHHELD,0,'',W2_MED_TAX_WITHHELD) med_withheld,
decode(W2_SOCIAL_SECURITY_TIPS,0,'',W2_SOCIAL_SECURITY_TIPS) ss_tips,
decode(W2_ALLOCATED_TIPS,0,'',W2_ALLOCATED_TIPS) allocated_tips,
decode(W2_ADV_EIC_PAYMENT,0,'',W2_ADV_EIC_PAYMENT) eic_payment,
decode(W2_DEPENDENT_CARE_BEN,0,'',W2_DEPENDENT_CARE_BEN) dependent_care,
decode(W2_NONQUAL_PLANS,0,'',W2_NONQUAL_PLANS) non_qual_plan,
decode(W2_STATUTORY_EMPLOYEE,'X','Y',null,'N',' ','N')
stat_employee,
decode(W2_RETIREMENT_PLAN,'X','Y',null,'N',' ','N')
retirement_plan,
decode(W2_THIRD_PARTY_SICK_PAY,'X','Y',null,
'N',' ','N') sick_pay,
person_id , puw.assignment_id -- bug 5575567
from pay_us_wages_w2_v puw
--per_addresses pa
where puw.assignment_action_id = p_asg_action_id
/* bug 5575567
and pa.primary_flag = 'Y'
and pa.person_id = puw.person_id */
and puw.tax_unit_id = p_tax_unit_id
and puw.year = p_year;
select w2_balance_code,
w2_balance_code_value
from pay_us_code_w2_v
where w2_balance_code_value > 0
and assignment_action_id = p_asg_action_id
order by w2_balance_code;
SELECT substr(w2_other_meaning,1,10) w2_other_code,
w2_other_value
from pay_us_other_w2_v
where w2_other_value > 0
and assignment_action_id = p_asg_action_id;
SELECT 'BOONOCC' w2_other_code,
w2_local_income_tax -
decode(sign(w2_local_wages - 16666), -1,
w2_local_wages * 0.0015, 25) w2_other_value
from pay_us_locality_w2_v
where state_abbrev = 'KY'
and tax_type = 'COUNTY'
and substr(jurisdiction,1,6) = '18-015'
and assignment_action_id = p_asg_action_id
and w2_local_income_tax > 0;
SELECT 'BOONMH' w2_other_code,
decode(sign(w2_local_wages - 16666), -1,
w2_local_wages * 0.0015, 25) w2_other_value
from pay_us_locality_w2_v
where state_abbrev = 'KY'
and tax_type = 'COUNTY'
and substr(jurisdiction,1,6) = '18-015'
and assignment_action_id = p_asg_action_id
and w2_local_income_tax > 0;
SELECT locality_name locality,
decode(w2_local_wages,0,'',w2_local_wages) locality_wages,
w2_local_income_tax locality_tax,
jurisdiction jurisdiction,
state_abbrev state_code,
tax_type
FROM pay_us_locality_w2_v
WHERE assignment_action_id = p_asg_action_id
/*commented for 4102684
and W2_LOCAL_INCOME_TAX > 0*/
-- and (jurisdiction <> '18-015-0000')
order by state_code, tax_type;
SELECT 1 , substr(state_abbrev,1,2) state_code,
substr(state_ein,1,20) state_ein,
to_char(decode(W2_STATE_WAGES,0,'',W2_STATE_WAGES),'9999999990.99') state_wages,
to_char(decode(W2_STATE_INCOME_TAX,0,'',W2_STATE_INCOME_TAX),'9999999990.99') state_tax
FROM pay_us_state_w2_v state
WHERE assignment_action_id = p_asg_action_id
and ( (w2_state_wages > 0) or
(W2_STATE_INCOME_TAX > 0) ) /* 6809739 */
and state_ein <> 'FLI P.P. #'
union all
SELECT 2 , substr(state_abbrev,1,2) state_code,
substr(state_ein,1,20) state_ein,
nvl(W2_STATE_WAGES,'') state_wages,
trim(decode(to_char(to_number(W2_STATE_INCOME_TAX ),'9999999990.99'),'0.0' , ' ',to_char(to_number(W2_STATE_INCOME_TAX ),'9999999990.99') || ' - FLI' )) state_tax
FROM pay_us_state_w2_v state
WHERE assignment_action_id = p_asg_action_id
and ( (w2_state_wages <> ' ') or
(to_char(to_number(W2_STATE_INCOME_TAX ),'9999999990.99') <> 0) ) /* 6809739 */
and state_ein = 'FLI P.P. #'
order by state_code , 1 ;
select federal_ein federal_ein,
tax_unit_name employer_name,
substr(decode(put.address_line_1,null,null,put.address_line_1||pay_us_w2_info_pkg.EOL),1,45)||
substr(decode(put.address_line_2,null,null,put.address_line_2||pay_us_w2_info_pkg.EOL),1,45)||
substr(decode(put.address_line_3,null,null,put.address_line_3||pay_us_w2_info_pkg.EOL),1,45)||
decode(put.town_or_city,null,null,put.town_or_city||' ')||
decode(state,null,null,state||' ')||put.postal_code
employer_address
from pay_us_w2_tax_unit_v put
where tax_unit_id = p_tax_unit_id
and year = p_year;
select federal_ein federal_ein,
tax_unit_name employer_name,
decode(put.address_line_1,null,null,substr(put.address_line_1,1,45)||pay_us_w2_info_pkg.EOL)||
decode(put.address_line_2,null,null,substr(put.address_line_2,1,45)||pay_us_w2_info_pkg.EOL)||
decode(put.address_line_3,null,null,substr(put.address_line_3,1,45)||pay_us_w2_info_pkg.EOL)||
decode(put.town_or_city,null,null,put.town_or_city||' ')||
decode(state,null,null,state||' ')||put.postal_code
employer_address
from pay_us_w2_tax_unit_v put
where tax_unit_id = p_tax_unit_id
and year = p_year;
select business_group_id
from hr_all_organization_units /*hr_organization_units*/
where organization_id = p_tax_unit_id;
select payroll_action_id
from pay_assignment_actions
where assignment_action_id = p_asg_action_id;
SELECT NVL(TO_DATE(hr_us_w2_mt.get_parameter('EFFECTIVE_DATE',
ppa.legislative_parameters),'YYYY/MM/DD'),SYSDATE) session_date
FROM pay_payroll_actions ppa
WHERE payroll_action_id = pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID');
select l_first_name||decode(l_middle_name,null,' ',
' '||substr(l_middle_name,1,1)||' ') ||
hr_us_w2_rep.get_per_item(p_asg_action_id,
'A_PER_PREFIX' ) into l_w2_fields.emp_name from dual;
select nvl(value,'N') into l_nr_flag
from ff_database_items fdi,
ff_archive_items fai
where user_name = 'A_IN_NR_FLAG'
and fdi.user_entity_id = fai.user_entity_id
and fai.context1 = p_asg_action_id;
select nvl(value,'00-000-0000') into l_nr_jd
from ff_database_items fdi,
ff_archive_items fai
where fdi.user_name = 'A_IN_RES_JD'
and fdi.user_entity_id = fai.user_entity_id
and context1 = p_asg_action_id;
l_state_local_tab.delete;
l_state_tab.delete;
l_local_tab.delete;
l_box12_tab.delete;
l_box14_tab.delete;
SELECT paa1.assignment_action_id, -- archiver asg action
paa.assignment_action_id,
hr_us_w2_mt.get_parameter('GRE_ID',ppa.legislative_parameters),
hr_us_w2_mt.get_parameter('Year',ppa.legislative_parameters),
hr_us_w2_mt.get_parameter('p_instr_template',ppa.legislative_parameters),
hr_us_w2_mt.get_parameter('print_instrunction',ppa.legislative_parameters)
FROM pay_assignment_actions paa,
pay_payroll_actions ppa,
pay_assignment_actions paa1,
pay_payroll_actions ppa1
where ppa.payroll_action_id = paa.payroll_action_id
and ppa.payroll_action_id = pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID')
and paa.assignment_action_id = pay_magtape_generic.get_parameter_value('TRANSFER_ACT_ID')
and paa.serial_number = paa1.assignment_action_id
and paa1.payroll_action_id = ppa1.payroll_action_id
and ppa1.report_type = 'YREND'
and ppa1.action_type = 'X'
and ppa1.action_status = 'C'
and ppa1.effective_date = ppa.effective_date;
l_state_local_tab.delete;
l_state_tab.delete;
l_local_tab.delete;
l_box12_tab.delete;
l_box14_tab.delete;
select translate(ltrim(value),',',' ')
into TEMP_UTL
from v$parameter
where name = 'utl_file_dir';
select substrb(TEMP_UTL, 1, instr(TEMP_UTL,' ') - 1)
into g_temp_dir
from dual ;