[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
29-FEB-2012 ybudamal 115.36 13715397 Added procedure update_state_or_local_wh_link
to update the state W4 withholding link and local
tax form link for the State if local link exists.
Otherwise creates a new local tax form link.
*****************************************************************************/
/*****************************************************************************
** Package Local Variables
*****************************************************************************/
gv_package VARCHAR2(100) := 'pay_us_payroll_utils';
select futa_wage_limit, futa_rate,
ss_ee_wage_limit, ss_ee_rate,
ss_er_wage_limit, ss_er_rate,
medi_ee_rate, medi_er_rate,
fed_information1, fed_information2
from pay_us_federal_tax_info_f
where cp_effective_date between effective_start_date
and effective_end_date
and fed_information_category = cp_fed_info_category;
select state_code,
sit_exists,
sui_ee_wage_limit,
sui_er_wage_limit,
decode(sdi_ee_wage_limit,
NULL, STA_INFORMATION1,
0, STA_INFORMATION1,
sdi_ee_wage_limit) sdi_ee_wage_limit,
sdi_er_wage_limit,
nvl(sta_information17,'N'),
sta_information19 futa_rate,
sta_information21 sdi1_ee_wage_limit
from pay_us_state_tax_info_f
where cp_effective_date between effective_start_date
and effective_end_date
and sta_information_category = 'State tax limit rate info'
order by 1 ;
select jurisdiction_code,
county_tax,
head_tax,
school_tax
from pay_us_county_tax_info_f
where cp_effective_date between effective_start_date
and effective_end_date
and cnty_information_category = 'County tax status info'
order by 1 ;
select jurisdiction_code,
city_tax,
head_tax,
school_tax
from pay_us_city_tax_info_f
where cp_effective_date between effective_start_date
and effective_end_date
and jurisdiction_code = cp_jurisdiction_code
and city_information_category = 'City tax status info';
select effective_date from fnd_sessions fs
where session_id = userenv('sessionid');
select 'Y' into lv_value from dual
where exists (
select 'x'
from pay_wc_funds wcf, pay_us_states uss
where uss.state_code = lv_state_code
and uss.state_abbrev = wcf.state_code
and wcf.business_group_id =
nvl(hr_general.get_business_group_id, wcf.business_group_id));
select 1
from pay_bal_attribute_definitions
where attribute_name = c_attribute_name
and legislation_code = p_legislation_code;
select /*+ ORDERED */ count(*)
from pay_bal_attribute_definitions pbad,
pay_balance_attributes pba,
pay_balance_validation pbv
where pbad.attribute_name = cp_attribute_name
and pbad.attribute_id = pba.attribute_id
and (pba.business_group_id = cp_business_group_id
OR
pba.legislation_code = p_legislation_code)
and pba.defined_balance_id = pbv.defined_balance_id
and pbv.business_group_id = cp_business_group_id
and NVL(pbv.balance_load_date, cp_start_date) <= cp_start_date
and nvl(pbv.run_balance_status, 'I') = 'V';
select count(*)
from pay_bal_attribute_definitions pbad,
pay_balance_attributes pba
where pbad.attribute_name = cp_attribute_name
and pbad.attribute_id = pba.attribute_id
and (pba.business_group_id = cp_business_group_id
OR
pba.legislation_code = p_legislation_code );
select lei_information1
from hr_location_extra_info hlei
where hlei.location_id = cp_location_id
and information_type = 'US_LOC_REP_PREFERENCES';
select org_information1
from hr_organization_information hoi
where organization_id = cp_organization_id
and org_information_context = cp_org_information_context;
select nvl(min(assignment_action_id),-1)
from pay_assignment_actions paa,
pay_payroll_actions ppa,
pay_payrolls_f ppf
where ppa.business_group_id +0 = cp_business_group_id
and ppa.payroll_action_id = paa.payroll_action_id
and ppa.effective_date between cp_start_date and cp_end_date
and ppa.action_type in ('R','Q','I','B','V')
and ppf.payroll_id = ppa.payroll_id
and ppa.business_group_id +0 = ppf.business_group_id
and paa.tax_unit_id = nvl(cp_tax_unit_id, paa.tax_unit_id)
and ppf.payroll_id = nvl(cp_payroll_id, ppf.payroll_id);
Name : update_state_or_local_wh_link
Purpose : This procedure updates the state W4 withholding link and local
tax form link for the State if local link exists. Otherwise
creates a new local tax form link.
Arguments : p_state - Name of the State
p_state_or_local_tax_form - State/Local form to update
p_link - New Link to be used
Notes :
*****************************************************************************/
PROCEDURE update_state_or_local_wh_link(errbuf OUT NOCOPY VARCHAR2,
retcode OUT NOCOPY NUMBER,
p_state_or_local_tax_form IN VARCHAR2,
p_state IN VARCHAR2,
p_link IN VARCHAR2) IS
/* Declaration of local Variables */
l_sysdate DATE := TRUNC(SYSDATE);
l_procedure_name VARCHAR2(50) := 'update_state_or_local_wh_link';
SELECT lookup_type,
view_application_id,
lookup_code,
security_group_id,
language
FROM fnd_lookup_values
WHERE lookup_type = v_lookup_type
AND lookup_code = v_lookup_code
AND language = userenv ('LANG');
SELECT state_name
FROM pay_us_states
WHERE state_abbrev = v_state;
SELECT sg.security_group_id
FROM fnd_lookup_types lt, fnd_security_groups sg
WHERE lt.lookup_type = 'PAY_US_LOCAL_PDF_LINK_W4'
AND lt.view_application_id = v_application_id
AND lt.security_group_id = sg.security_group_id
AND sg.security_group_key ='STANDARD';
SELECT application_id
FROM fnd_application
WHERE application_short_name = 'AU';
/* Update the State Link for the Lookup */
UPDATE fnd_lookup_values
SET description = p_link,
meaning = 'User updated: ' || LTRIM(meaning, 'User updated: '),
last_updated_by = l_app_user,
last_update_date = l_sysdate
WHERE lookup_type = l_lookup_type
AND view_application_id = l_view_application_id
AND lookup_code = l_lookup_code
AND security_group_id = l_security_group_id
AND language = l_language;
/* Insert the new Lookup Code */
INSERT INTO fnd_lookup_values
( lookup_type
,language
,lookup_code
,meaning
,description
,enabled_flag
,created_by
,creation_date
,last_updated_by
,last_update_login
,last_update_date
,source_lang
,security_group_id
,view_application_id )
VALUES
( 'PAY_US_LOCAL_PDF_LINK_W4'
,'US'
,p_state
,'HTTP hyperlink to '|| l_state_name || ' local pdf form'
,p_link
,'Y'
,l_app_user
,l_sysdate
,l_app_user
,0
,l_sysdate
,'US'
,l_security_group_id1
,l_application_id );
/* Update the Local Link for the Lookup */
UPDATE fnd_lookup_values
SET description = p_link,
last_updated_by = l_app_user,
last_update_date = l_sysdate
WHERE lookup_type = l_lookup_type
AND view_application_id = l_view_application_id
AND lookup_code = l_lookup_code
AND security_group_id = l_security_group_id
AND language = l_language;
END update_state_or_local_wh_link;