[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT TO_NUMBER(UE.creator_id)
FROM ff_database_items DI,
ff_user_entities UE
WHERE DI.user_name = p_db_item_name
AND UE.user_entity_id = DI.user_entity_id
AND UE.creator_type = 'B'
AND UE.legislation_code = 'US'; /* Bug: 2296797 */
SELECT ppa.start_date,
ppa.effective_date,
ppa.business_group_id,
ppa.report_qualifier,
ppa.report_type
INTO p_year_start,
p_year_end,
p_business_group_id,
p_state_abbrev,
p_report_type
FROM pay_payroll_actions ppa
WHERE payroll_action_id = p_pactid;
SELECT state_code
INTO p_state_code
FROM pay_us_states
WHERE state_abbrev = p_state_abbrev;
SELECT jurisdiction_code
INTO l_jurisdiction_code
FROM pay_state_rules
WHERE state_code = p_state_abbrev;
SELECT hou.organization_id gre
FROM hr_organization_information hoi,
hr_organization_units hou
WHERE hou.business_group_id+0 = p_business_group_id AND
hoi.organization_id = hou.organization_id AND
hoi.org_information_context = 'CLASS' AND
hoi.org_information1 = 'HR_LEGAL' AND
NOT EXISTS (
SELECT 'Y'
FROM hr_organization_information
WHERE organization_id = hou.organization_id
AND org_information_context = '1099R Magnetic Report Rules');
SELECT paf.person_id,
paf.assignment_id,
paf.effective_end_date
FROM per_assignments_f paf
WHERE exists
(SELECT 'x'
FROM pay_us_emp_state_tax_rules_f pest
WHERE pest.state_code = p_state_code
AND pest.business_group_id + 0 = p_business_group_id
AND pest.effective_start_date <= p_year_end
AND pest.effective_end_date >= p_year_start
AND pest.assignment_id = paf.assignment_id
)
AND paf.effective_start_date <= p_year_end
AND paf.effective_end_date >= p_year_start
AND paf.business_group_id+0 = p_business_group_id
AND paf.assignment_type = 'E'
AND EXISTS
(SELECT 'x'
FROM pay_assignment_actions paa_act,
pay_payroll_actions ppa_act
WHERE paa_act.assignment_id = paf.assignment_id
AND paa_act.tax_unit_id = cp_tax_unit_id
AND ppa_act.payroll_action_id = paa_act.payroll_action_id
AND ppa_act.action_type IN ('R', 'Q', 'B', 'I', 'V')
AND ppa_act.effective_date BETWEEN p_year_start
AND p_year_end
AND ppa_act.date_earned BETWEEN paf.effective_start_date
AND paf.effective_end_date
AND ppa_act.action_status = 'C' )
ORDER BY 1, 3 DESC, 2;
SELECT paf.person_id,
paf.assignment_id,
paf.effective_end_date
FROM per_assignments_f paf
WHERE paf.business_group_id+0 = p_business_group_id
-- In order to avoid full table scan on per_assignment_f
-- added assignmet_id
AND paf.assignment_id >= 0
AND paf.effective_start_date <= p_year_end
AND paf.effective_end_date >= p_year_start
AND paf.assignment_type = 'E'
AND EXISTS (
SELECT 'x'
FROM pay_payroll_actions ppa_act,
pay_assignment_actions paa_act
WHERE paa_act.assignment_id = paf.assignment_id
AND paa_act.tax_unit_id = cp_tax_unit_id
AND ppa_act.payroll_action_id = paa_act.payroll_action_id
AND ppa_act.action_type IN ('R', 'Q', 'B', 'I', 'V')
AND ppa_act.effective_date
BETWEEN p_year_start AND p_year_end
AND ppa_act.date_earned
BETWEEN paf.effective_start_date AND paf.effective_end_date
AND ppa_act.action_STATUS = 'C' -- ADDED BY Djoshi
)
ORDER BY 1, 3 DESC, 2;
SELECT payroll_action_id
FROM pay_payroll_actions
WHERE report_type = 'YREND'
AND effective_date = p_year_end
AND start_date = p_year_start
AND business_group_id+0 = p_business_group_id
AND SUBSTR(legislative_parameters,
INSTR(legislative_parameters, 'TRANSFER_GRE=') +
LENGTH('TRANSFER_GRE=')) = TO_CHAR(cp_gre)
-- ADDED FOLLOWING CHECK CONDITION
AND action_status = 'C';
SELECT '1'
FROM dual
WHERE EXISTS (SELECT '1'
FROM pay_assignment_actions paa
WHERE paa.payroll_action_id = cp_payroll_action_id
AND paa.action_status = 'E'
)
AND NOT EXISTS ( SELECT '1'
FROM pay_action_parameters
WHERE parameter_name = 'FORCE_MAG_REPORT'
AND INSTR(parameter_value, 'E') > 0
);
SELECT '1'
FROM dual
WHERE EXISTS (SELECT '1'
FROM pay_assignment_actions paa
WHERE paa.payroll_action_id = cp_payroll_action_id
AND paa.action_status = 'M')
AND NOT EXISTS (SELECT '1'
FROM pay_action_parameters
WHERE parameter_name = 'FORCE_MAG_REPORT'
AND INSTR(parameter_value, 'R') > 0
);
SELECT user_entity_id
FROM ff_database_items fdi
WHERE user_name = 'A_SIT_SUBJ_WHABLE_PER_JD_GRE_YTD' ;
SELECT context_id
FROM ff_contexts
WHERE context_name = 'TAX_UNIT_ID';
SELECT context_id
FROM ff_contexts
WHERE context_name = 'JURISDICTION_CODE';
SELECT 'Y'
FROM
ff_archive_items fai,
pay_assignment_actions paa,
pay_payroll_actions ppa
WHERE ppa.PAYROLL_ACTION_ID = CP_PAYROLL_ACTION_ID
AND ppa.payroll_action_id = paa.payroll_action_id
AND paa.assignment_action_id = fai.context1
AND fai.user_entity_id = cp_user_entity_id
AND fai.value > 0
AND EXISTS
( SELECT 'Y'
FROM ff_archive_item_contexts faic1
WHERE faic1.archive_item_id = fai.archive_item_id
AND faic1.context_id = cp_context_tax_unit
AND rtrim(ltrim(faic1.context)) = cp_tax_unit_id
)
AND EXISTS
( SELECT 'Y'
FROM ff_archive_item_contexts faic2
WHERE faic2.archive_item_id = fai.archive_item_id
AND faic2.context_id = cp_context_jursidiction
AND rtrim(ltrim(substr(faic2.context,1,2))) = p_state_code
);
SELECT 'Y'
FROM
ff_archive_item_contexts con3,
ff_archive_item_contexts con2,
ff_contexts fc3,
ff_contexts fc2,
ff_archive_items target,
ff_database_items fdi
WHERE target.context1 = cp_payroll_action_id
/* context of payroll_action_id */
and fdi.user_name = 'A_FIPS_CODE_JD'
and target.user_entity_id = fdi.user_entity_id
and fc2.context_name = 'TAX_UNIT_ID'
and con2.archive_item_id = target.archive_item_id
and con2.context_id = fc2.context_id
and ltrim(rtrim(con2.context)) = to_char(cp_tax_unit_id)
and fc3.context_name = 'JURISDICTION_CODE'
and con3.archive_item_id = target.archive_item_id
and con3.context_id = fc3.context_id
and substr(ltrim(rtrim(con3.context)),1,2) = p_state_code;
SELECT SUBSTR(legislative_parameters,INSTR(legislative_parameters, 'TRANSFER_TRANS_LEGAL_CO_ID=')
+ LENGTH('TRANSFER_TRANS_LEGAL_CO_ID='),
(INSTR(legislative_parameters, 'TRANSFER_DATE=')
- INSTR(legislative_parameters, 'TRANSFER_TRANS_LEGAL_CO_ID=')
- LENGTH('TRANSFER_TRANS_LEGAL_CO_ID=')-1 ))
FROM pay_payroll_actions
WHERE report_type = 'W2'
AND effective_date = p_year_end
AND report_qualifier = p_state_abbrev
AND business_group_id = p_business_group_id
AND report_category = 'RT' ;
insert into pay_message_lines (
line_sequence,
payroll_id,
message_level,
source_id,
source_type,
line_text)
values (pay_message_lines_s.nextval,
NULL,
'F', -- it's a fatal message.
p_pactid,
'P', -- payroll action level.
message_text);
insert into pay_message_lines (
line_sequence,
payroll_id,
message_level,
source_id,
source_type,
line_text)
values (pay_message_lines_s.nextval,
NULL,
'F', -- it's a fatal message.
p_pactid,
'P', -- payroll action level.
message_text);
SELECT DISTINCT paf.person_id
FROM per_assignments_f paf,
pay_assignment_actions paa,
pay_payroll_actions ppa,
pay_payroll_actions ppa1
WHERE ppa1.payroll_action_id = :payroll_action_id
AND ppa.report_type = ''YREND''
AND ppa.business_group_id+0 = ppa1.business_group_id
AND ppa.effective_date = ppa1.effective_date
AND ppa.start_date = ppa1.start_date
AND paa.payroll_action_id = ppa.payroll_action_id
AND paa.action_status = ''C''
AND paf.assignment_id = paa.assignment_id
AND paf.effective_start_date <= ppa.effective_date
AND paf.effective_end_date >= ppa.start_date
AND paf.assignment_type = ''E''
AND not exists (
SELECT ''x''
FROM hr_organization_information hoi
WHERE hoi.organization_id = paa.tax_unit_id
and hoi.org_information_context =
''1099R Magnetic Report Rules'')
ORDER BY paf.person_id
';
SELECT DISTINCT
to_number(paa.serial_number)
FROM ff_archive_item_contexts faic,
ff_archive_items fai,
ff_database_items fdi,
pay_assignment_actions paa,
pay_payroll_actions ppa,
per_assignments_f paf,
pay_payroll_actions ppa1
WHERE
ppa1.payroll_action_id = :payroll_action_id
AND ppa.business_group_id+0 = ppa1.business_group_id
AND ppa1.effective_date = ppa.effective_date
AND ppa.report_type = ''YREND''
AND ppa.payroll_action_id = paa.payroll_action_id
and paf.assignment_id = paa.assignment_id
AND paf.assignment_type = ''E''
AND fdi.user_name = ''A_STATE_ABBREV''
AND fdi.user_entity_id = fai.user_entity_id
AND fai.archive_item_id = faic.archive_item_id
AND fai.context1 = paa.assignment_action_id
AND fai.value = ppa1.report_qualifier
AND paf.effective_start_date <= ppa.effective_date
AND paf.effective_end_date >= ppa.start_date
AND paa.action_status = ''C''
AND nvl(hr_us_w2_rep.get_w2_arch_bal(
paa.assignment_action_id,
''A_W2_STATE_WAGES'',
paa.tax_unit_id,
faic.context , 2),0) > 0
AND EXISTS ( /*+ INDEX(pustif PAY_US_STATE_TAX_INFO_F_N1) */
select ''x''
from pay_us_state_tax_info_f pustif
where substr(faic.context,1,2) = pustif.state_code
and ppa.effective_date between pustif.effective_start_date
and pustif.effective_end_date
and pustif.sit_exists = ''Y'')
AND not exists (
SELECT ''x''
FROM hr_organization_information hoi
WHERE hoi.organization_id = paa.tax_unit_id
and hoi.org_information_context =
''1099R Magnetic Report Rules''
)
order by to_number(paa.serial_number)';
SELECT
to_number(paa.serial_number),
paf.assignment_id,
paa.tax_unit_id,
paf.effective_end_date,
paa.assignment_action_id,
nvl(hr_us_w2_rep.get_w2_arch_bal(paa.assignment_action_id,
'A_W2_STATE_WAGES',
paa.tax_unit_id,
faic.context , 2),0)
FROM ff_archive_item_contexts faic,
ff_archive_items fai,
ff_database_items fdi,
pay_assignment_actions paa,
pay_payroll_actions ppa,
per_assignments_f paf,
pay_payroll_actions ppa1
WHERE
ppa1.payroll_action_id = p_pactid
and ppa.business_group_id+0 = ppa1.business_group_id
and ppa1.effective_date = ppa.effective_date
and ppa.report_type = 'YREND'
and ppa.payroll_action_id = paa.payroll_action_id
and paf.assignment_id = paa.assignment_id
and paf.assignment_type = 'E'
and fdi.user_name = 'A_STATE_ABBREV'
and fdi.user_entity_id = fai.user_entity_id
and fai.archive_item_id = faic.archive_item_id
and fai.context1 = paa.assignment_action_id
and fai.value = ppa1.report_qualifier
and paf.effective_start_date <= ppa.effective_date
and paf.effective_end_date >= ppa.start_date
and paa.action_status = 'C'
and paf.person_id BETWEEN p_stperson AND p_endperson
and nvL(hr_us_w2_rep.get_w2_arch_bal(paa.assignment_action_id,
'A_W2_STATE_WAGES',
paa.tax_unit_id,
faic.context , 2),0) > 0
and exists ( /*+ INDEX(pustif PAY_US_STATE_TAX_INFO_F_N1) */
select 'x'
from pay_us_state_tax_info_f pustif
where substr(faic.context,1,2) = pustif.state_code
and ppa.effective_date between pustif.effective_start_date
and pustif.effective_end_date
and pustif.sit_exists = 'Y'
)
and not exists
(
select 'x'
from hr_organization_information hoi
WHERE hoi.organization_id = paa.tax_unit_id
and hoi.org_information_context ='1099R Magnetic Report Rules'
)
ORDER BY 1, 3, 4 DESC, 2
FOR UPDATE OF paf.assignment_id;
SELECT paf.person_id,
paf.assignment_id,
Paa.tax_unit_id, --TO_NUMBER(hsck.segment1),
paf.effective_end_date,
paa.assignment_action_id
FROM pay_payroll_actions ppa,
pay_assignment_actions paa,
--hr_soft_coding_keyflex hsck,
per_assignments_f paf,
pay_payroll_actions ppa1
WHERE ppa1.payroll_action_id = p_pactid
AND ppa.report_type = 'YREND'
AND ppa.business_group_id+0 = ppa1.business_group_id
AND ppa.effective_date = ppa1.effective_date
AND ppa.start_date = ppa1.start_date
AND paa.payroll_action_id = ppa.payroll_action_id
AND paa.action_status = 'C'
AND paf.assignment_id = paa.assignment_id
AND paf.person_id BETWEEN p_stperson AND p_endperson
AND paf.assignment_type = 'E'
AND paf.effective_start_date <= ppa.effective_date
AND paf.effective_end_date >= ppa.start_date
--AND hsck.soft_coding_keyflex_id = paf.soft_coding_keyflex_id
--AND hsck.segment1 = paa.tax_unit_id
--AND hsck.segment1 NOT IN (
AND not exists (
SELECT 'x'
FROM hr_organization_information hoi
WHERE hoi.organization_id = paa.tax_unit_id
and hoi.org_information_context = '1099R Magnetic Report Rules')
ORDER BY 1, 3, 4 DESC, 2
FOR UPDATE OF paf.assignment_id;
select to_number(fai.value)
from ff_archive_item_contexts faic,
ff_archive_items fai,
ff_contexts fc,
ff_database_items fdi
where fdi.user_name = 'A_GROSS_EARNINGS_PER_GRE_YTD'
and fc.context_name = 'TAX_UNIT_ID'
and fai.context1 = p_assignment_action_id
and fai.user_entity_id = fdi.user_entity_id
and faic.archive_item_id = fai.archive_item_id
and faic.context_id = fc.context_id
and faic.context = p_tax_unit_id
and faic.sequence_no = 1;
SELECT pay_assignment_actions_s.nextval
INTO lockingactid
FROM dual;
--update serial number for highly compensated people for the
--state W2.
IF l_report_type = 'STW2' THEN
hr_utility.set_location(
'pay_us_magw2_reporting.create_assignement_act', 80);
UPDATE pay_assignment_actions
SET serial_number = 999999
WHERE assignment_action_id = lockingactid;