The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT DISTINCT
hoi.organization_id
FROM hr_organization_information hoi
,hr_organization_units hou1
WHERE hoi.org_information1 = p_state
AND hou1.business_group_id = p_business_group_id
AND hoi.organization_id = hou1.organization_id
AND hoi.org_information_context = 'State Tax Rules'
AND nvl (p_tax_unit_id
,hoi.organization_id) = hoi.organization_id
AND nvl (hoi.org_information16
,'No') = 'No'
AND nvl (hoi.org_information20
,'No') = 'No'
AND (
(
p_state IN ('CA','ME')
)
OR NOT EXISTS
(
SELECT 'x'
FROM hr_organization_information hoi2
,hr_organization_units hou2
WHERE hoi2.organization_id = hoi.organization_id
AND hoi2.org_information_context = '1099R Magnetic Report Rules'
AND hoi2.org_information2 IS NOT NULL
AND hou2.business_group_id = p_business_group_id
AND hoi2.organization_id = hou2.organization_id
)
);
SELECT flv.meaning
FROM fnd_lookup_values flv
WHERE flv.lookup_type = cp_state_abbrv||'_SQWL_MEDIA_TYPE'
AND flv.lookup_code = cp_format
AND flv.enabled_flag = 'Y'
AND LANGUAGE='US';
/*SELECT distinct puar.tax_unit_id
FROM hr_organization_information HOI,
per_assignments_f ASG,
pay_us_asg_reporting puar,
pay_state_rules SR
WHERE SR.state_code = 'NY' --l_state
AND puar.jurisdiction_code like substr(SR.jurisdiction_code ,1,2)||'%'
AND ASG.assignment_id = puar.assignment_id
AND ASG.assignment_type = 'E'
AND ASG.effective_start_date <= to_date('31-MAR-2010') --l_effective_date
AND ASG.effective_end_date >= to_date('01-JAN-2010') --l_start_date
AND ASG.business_group_id + 0 = 0 --l_business_group_id
AND (('NY' IN ( 'CA','ME'))
OR (not exists (select 'x'
from hr_organization_information HOI2
where HOI2.organization_id = puar.tax_unit_id
AND HOI2.ORG_INFORMATION_CONTEXT = '1099R Magnetic Report Rules'
AND HOI2.ORG_INFORMATION2 is not null)))
AND HOI.organization_id = puar.tax_unit_id
AND HOI.ORG_INFORMATION_CONTEXT = 'State Tax Rules'
AND HOI.ORG_INFORMATION1 = 'NY' --l_state
AND NVL(HOI.ORG_INFORMATION16,'No') = 'No'
AND NVL(HOI.ORG_INFORMATION20,'No') = 'No'
AND ASG.payroll_id is not null; */
SELECT pay_payroll_actions_s.nextval
INTO ln_locking_action_id
FROM dual;
hr_utility.trace('inserting payroll action: '||ln_locking_action_id);
INSERT
INTO pay_payroll_actions
(payroll_action_id
,action_type
,business_group_id
,consolidation_set_id
,payroll_id
,action_population_status
,action_status
,effective_date
,comments
,object_version_number
,pay_advice_message
,report_type
,report_qualifier
,request_id
,report_category
,start_date)
VALUES
(ln_locking_action_id
,'X'
,p_business_group_id
,NULL
,NULL
,'C'
,'C'
,ld_quater_end_date
,NULL
,1
,NULL
,'SQWL_PARENT'
,p_state
,fnd_profile.value ('CONC_REQUEST_ID')
,p_format
,ld_quater_start_date);
SELECT state_code INTO lv_state_code
FROM pay_us_states
WHERE STATE_ABBREV = p_state;
SELECT count(1) into l_previous_sqwl_run_check from pay_payroll_actions
WHERE report_type = 'SQWL'
AND report_qualifier = p_state
AND business_group_id = p_business_group_id
AND effective_date = fnd_date.canonical_to_date (p_effective_date)
AND pay_core_utils.get_parameter('TRANSFER_GRE',legislative_parameters) = c_get_all_gres_rec.organization_id;
select count(paa.assignment_action_id)
into l_chk_gre_assign_actions
from pay_assignment_actions paa,
pay_payroll_actions pact,
pay_payrolls_f ppf,
pay_us_emp_fed_tax_rules_f peft
where pact.effective_date
between ld_Quater_start_date
and ld_Quater_end_date
and pact.payroll_action_id=paa.payroll_action_id
and pact.action_type in ('R', 'Q', 'I','B')
and paa.action_status = 'C'
and pact.action_status = 'C'
and paa.tax_unit_id = c_get_all_gres_rec.organization_id
and ppf.payroll_id = pact.payroll_id
and ppf.business_group_id =p_business_group_id
and peft.ASSIGNMENT_ID = paa.ASSIGNMENT_ID
and pact.EFFECTIVE_DATE between
peft.EFFECTIVE_START_DATE and peft.EFFECTIVE_END_DATE
and peft.SUI_STATE_CODE = lv_state_code;
SELECT pay_us_reporting_utils_pkg.get_file_name(c_get_all_gres_rec.organization_id,
'SQWL',
p_state,
to_char(ld_Quater_end_date,'YYYY/MM/DD') ||' 00:00:00',
substr(lv_format_meaning,1,1) )
INTO l_file_name
FROM dual;
SELECT pay_us_reporting_utils_pkg.get_file_name( p_business_group_id,
'SQWL',
p_state,
to_char(ld_Quater_end_date,'YYYY/MM/DD') ||' 00:00:00',
substr(lv_format_meaning,1,1) )
INTO l_file_name
FROM dual;
SELECT pact.payroll_action_id
FROM pay_payroll_actions pact
WHERE action_type = 'X'
AND report_type in ('SQWL', 'SMWL') /* Modified for Bug 14456648 */
AND pay_us_payroll_utils.get_parameter ('TRANSFER_PARENT_PAYROLL_ACTION'
,pact.legislative_parameters) = p_payroll_action_id;
SELECT report_type
FROM pay_payroll_actions
WHERE payroll_action_id = p_payroll_action_id;
SELECT count (payroll_action_id)
FROM pay_payroll_actions p1
WHERE pay_us_payroll_utils.get_parameter ('TRANSFER_PARENT_PAYROLL_ACTION'
,p1.legislative_parameters) IN
(
SELECT pay_us_payroll_utils.get_parameter ('TRANSFER_PARENT_PAYROLL_ACTION'
,legislative_parameters)
FROM pay_payroll_actions
WHERE payroll_action_id = cp_payroll_action_id
);
SELECT report_type INTO l_report_type
FROM pay_payroll_actions
WHERE payroll_action_id = p_payroll_action_id;
SELECT pay_us_payroll_utils.get_parameter('TRANSFER_PARENT_PAYROLL_ACTION',legislative_parameters)
INTO l_parent_payroll_action_id from pay_payroll_actions
WHERE payroll_action_id = p_payroll_action_id;
SELECT count(payroll_action_id) into l_child_count
FROM pay_payroll_actions p1
WHERE pay_us_payroll_utils.get_parameter('TRANSFER_PARENT_PAYROLL_ACTION',p1.legislative_parameters)
= l_parent_payroll_action_id;
SELECT DISTINCT
hoi.organization_id
FROM hr_organization_information hoi
,hr_organization_units hou1
WHERE hoi.org_information1 = p_state
AND hou1.business_group_id = p_business_group_id
AND hoi.organization_id = hou1.organization_id
AND hoi.org_information_context = 'State Tax Rules'
AND nvl (p_tax_unit_id
,hoi.organization_id) = hoi.organization_id
AND nvl (hoi.org_information16
,'No') = 'No'
AND nvl (hoi.org_information20
,'No') = 'No'
AND (
(
p_state IN ('CA','ME')
)
OR NOT EXISTS
(
SELECT 'x'
FROM hr_organization_information hoi2
,hr_organization_units hou2
WHERE hoi2.organization_id = hoi.organization_id
AND hoi2.org_information_context = '1099R Magnetic Report Rules'
AND hoi2.org_information2 IS NOT NULL
AND hou2.business_group_id = p_business_group_id
AND hoi2.organization_id = hou2.organization_id
)
);
SELECT flv.meaning
FROM fnd_lookup_values flv
WHERE flv.lookup_type = cp_state_abbrv||'_SMWL_MEDIA_TYPE'
AND flv.lookup_code = cp_format
AND flv.enabled_flag = 'Y'
AND LANGUAGE='US';
/*SELECT distinct puar.tax_unit_id
FROM hr_organization_information HOI,
per_assignments_f ASG,
pay_us_asg_reporting puar,
pay_state_rules SR
WHERE SR.state_code = 'NY' --l_state
AND puar.jurisdiction_code like substr(SR.jurisdiction_code ,1,2)||'%'
AND ASG.assignment_id = puar.assignment_id
AND ASG.assignment_type = 'E'
AND ASG.effective_start_date <= to_date('31-MAR-2010') --l_effective_date
AND ASG.effective_end_date >= to_date('01-JAN-2010') --l_start_date
AND ASG.business_group_id + 0 = 0 --l_business_group_id
AND (('NY' IN ( 'CA','ME'))
OR (not exists (select 'x'
from hr_organization_information HOI2
where HOI2.organization_id = puar.tax_unit_id
AND HOI2.ORG_INFORMATION_CONTEXT = '1099R Magnetic Report Rules'
AND HOI2.ORG_INFORMATION2 is not null)))
AND HOI.organization_id = puar.tax_unit_id
AND HOI.ORG_INFORMATION_CONTEXT = 'State Tax Rules'
AND HOI.ORG_INFORMATION1 = 'NY' --l_state
AND NVL(HOI.ORG_INFORMATION16,'No') = 'No'
AND NVL(HOI.ORG_INFORMATION20,'No') = 'No'
AND ASG.payroll_id is not null; */
SELECT pay_payroll_actions_s.nextval
INTO ln_locking_action_id
FROM dual;
hr_utility.trace('inserting payroll action: '||ln_locking_action_id);
INSERT
INTO pay_payroll_actions
(payroll_action_id
,action_type
,business_group_id
,consolidation_set_id
,payroll_id
,action_population_status
,action_status
,effective_date
,comments
,object_version_number
,pay_advice_message
,report_type
,report_qualifier
,request_id
,report_category
,start_date)
VALUES
(ln_locking_action_id
,'X'
,p_business_group_id
,NULL
,NULL
,'C'
,'C'
,ld_month_end_date
,NULL
,1
,NULL
,'SMWL_PARENT'
,p_state
,fnd_profile.value ('CONC_REQUEST_ID')
,p_format
,ld_month_start_date);
SELECT state_code INTO lv_state_code
FROM pay_us_states
WHERE STATE_ABBREV = p_state;
SELECT count(1) into l_previous_smwl_run_check from pay_payroll_actions
WHERE report_type = 'SMWL'
AND report_qualifier = p_state
AND business_group_id = p_business_group_id
AND effective_date = fnd_date.canonical_to_date (p_effective_date)
AND pay_core_utils.get_parameter('TRANSFER_GRE',legislative_parameters) = c_get_all_gres_rec.organization_id;
select count(paa.assignment_action_id)
into l_chk_gre_assign_actions
from pay_assignment_actions paa,
pay_payroll_actions pact,
pay_payrolls_f ppf,
pay_us_emp_fed_tax_rules_f peft
where pact.effective_date
between ld_Month_start_date
and ld_Month_end_date
and pact.payroll_action_id=paa.payroll_action_id
and pact.action_type in ('R', 'Q', 'I','B')
and paa.action_status = 'C'
and pact.action_status = 'C'
and paa.tax_unit_id = c_get_all_gres_rec.organization_id
and ppf.payroll_id = pact.payroll_id
and ppf.business_group_id =p_business_group_id
and peft.ASSIGNMENT_ID = paa.ASSIGNMENT_ID
and pact.EFFECTIVE_DATE between
peft.EFFECTIVE_START_DATE and peft.EFFECTIVE_END_DATE
and peft.SUI_STATE_CODE = lv_state_code;
SELECT pay_us_reporting_utils_pkg.get_file_name(c_get_all_gres_rec.organization_id,
'SQWL',
p_state,
to_char(ld_Month_end_date,'YYYY/MM/DD') ||' 00:00:00',
substr(lv_format_meaning,1,1) )
INTO l_file_name
FROM dual;
SELECT pay_us_reporting_utils_pkg.get_file_name( p_business_group_id,
'SQWL',
p_state,
to_char(ld_Month_end_date,'YYYY/MM/DD') ||' 00:00:00',
substr(lv_format_meaning,1,1) )
INTO l_file_name
FROM dual;