[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT ASA.assignment_id
FROM pay_assignment_actions ASA
WHERE ASA.payroll_action_id = p_pyrll_action_id
AND ASA.action_status = 'C';
SELECT COUNT(ELE.element_entry_id)
INTO l_taxsep_count
FROM pay_element_entries_f ELE,
pay_element_entry_values_f EEV,
pay_input_values_f IPV
WHERE ELE.assignment_id = asgrec.assignment_id
AND fnd_date.canonical_to_date(p_payact_earned_date)
BETWEEN ELE.effective_start_date
AND ELE.effective_end_date
AND EEV.element_entry_id = ELE.element_entry_id
AND NVL(EEV.screen_entry_value,'N') = 'Y'
AND fnd_date.canonical_to_date(p_payact_earned_date)
BETWEEN EEV.effective_start_date
AND EEV.effective_end_date
AND IPV.input_value_id = EEV.input_value_id
AND UPPER(IPV.name) = 'TAX SEPARATELY'
AND EXISTS (SELECT 'x'
FROM pay_element_entries_f ELE2,
pay_element_entry_values_f EEV2,
pay_input_values_f IPV2
WHERE ELE2.assignment_id
= asgrec.assignment_id
AND ELE2.element_entry_id
= ELE.element_entry_id
AND fnd_date.canonical_to_date(p_payact_earned_date)
BETWEEN ELE2.effective_start_date
AND ELE2.effective_end_date
AND EEV2.element_entry_id
= ELE2.element_entry_id
AND EEV2.screen_entry_value = 'N'
AND fnd_date.canonical_to_date(p_payact_earned_date)
BETWEEN EEV2.effective_start_date
AND EEV2.effective_end_date
AND IPV2.input_value_id
= EEV2.input_value_id
AND UPPER(IPV2.name)
= 'SEPARATE CHECK');
SELECT COUNT(ELE.element_entry_id)
INTO l_dednproc_count
FROM pay_element_entries_f ELE,
pay_element_entry_values_f EEV,
pay_input_values_f IPV
WHERE ELE.assignment_id = asgrec.assignment_id
AND fnd_date.canonical_to_date(p_payact_earned_date)
BETWEEN ELE.effective_start_date
AND ELE.effective_end_date
AND EEV.element_entry_id = ELE.element_entry_id
AND EEV.screen_entry_value <> 'A'
AND fnd_date.canonical_to_date(p_payact_earned_date)
BETWEEN EEV.effective_start_date
AND EEV.effective_end_date
AND IPV.input_value_id = EEV.input_value_id
AND UPPER(IPV.name) = 'DEDUCTION PROCESSING'
AND EXISTS (SELECT 'x'
FROM pay_element_entries_f ELE2,
pay_element_entry_values_f EEV2,
pay_input_values_f IPV2
WHERE ELE2.assignment_id = asgrec.assignment_id
AND ELE2.element_entry_id
= ELE.element_entry_id
AND fnd_date.canonical_to_date(p_payact_earned_date)
BETWEEN ELE2.effective_start_date
AND ELE2.effective_end_date
AND EEV2.element_entry_id
= ELE2.element_entry_id
AND EEV2.screen_entry_value = 'N'
AND fnd_date.canonical_to_date(p_payact_earned_date)
BETWEEN EEV2.effective_start_date
AND EEV2.effective_end_date
AND IPV2.input_value_id
= EEV2.input_value_id
AND UPPER(IPV2.name) = 'SEPARATE CHECK')
AND EXISTS (SELECT 'x'
FROM pay_element_entries_f ELE3,
pay_element_entry_values_f EEV3,
pay_input_values_f IPV3
WHERE ELE3.assignment_id = asgrec.assignment_id
AND ELE3.element_entry_id
= ELE.element_entry_id
AND fnd_date.canonical_to_date(p_payact_earned_date)
BETWEEN ELE3.effective_start_date
AND ELE3.effective_end_date
AND EEV3.element_entry_id
= ELE3.element_entry_id
AND EEV3.screen_entry_value = 'N'
AND fnd_date.canonical_to_date(p_payact_earned_date)
BETWEEN EEV3.effective_start_date
AND EEV3.effective_end_date
AND IPV3.input_value_id
= EEV3.input_value_id
AND UPPER(IPV3.name)
= 'TAX SEPARATELY');
SELECT COUNT(ELE.element_entry_id)
INTO l_dp_nots_count
FROM pay_element_entries_f ELE,
pay_element_entry_values_f EEV,
pay_input_values_f IPV
WHERE ELE.assignment_id = asgrec.assignment_id
AND fnd_date.canonical_to_date(p_payact_earned_date)
BETWEEN ELE.effective_start_date
AND ELE.effective_end_date
AND EEV.element_entry_id = ELE.element_entry_id
AND EEV.screen_entry_value <> 'A'
AND fnd_date.canonical_to_date(p_payact_earned_date)
BETWEEN EEV.effective_start_date
AND EEV.effective_end_date
AND IPV.input_value_id = EEV.input_value_id
AND UPPER(IPV.name) = 'DEDUCTION PROCESSING'
AND EXISTS (SELECT 'x'
FROM pay_element_entries_f ELE2,
pay_element_entry_values_f EEV2,
pay_input_values_f IPV2
WHERE ELE2.assignment_id = asgrec.assignment_id
AND ELE2.element_entry_id
= ELE.element_entry_id
AND fnd_date.canonical_to_date(p_payact_earned_date)
BETWEEN ELE2.effective_start_date
AND ELE2.effective_end_date
AND EEV2.element_entry_id
= ELE2.element_entry_id
AND EEV2.screen_entry_value = 'N'
AND fnd_date.canonical_to_date(p_payact_earned_date)
BETWEEN EEV2.effective_start_date
AND EEV2.effective_end_date
AND IPV2.input_value_id
= EEV2.input_value_id
AND UPPER(IPV2.name) = 'SEPARATE CHECK')
AND NOT EXISTS (SELECT 'x'
FROM pay_element_entries_f ELE3,
pay_element_links_f ELI3,
pay_input_values_f IPV3
WHERE ELE3.assignment_id = asgrec.assignment_id
AND ELE3.element_entry_id
= ELE.element_entry_id
AND fnd_date.canonical_to_date(p_payact_earned_date)
BETWEEN ELE3.effective_start_date
AND ELE3.effective_end_date
AND ELI3.element_link_id
= ELE3.element_link_id
AND fnd_date.canonical_to_date(p_payact_earned_date)
BETWEEN ELI3.effective_start_date
AND ELI3.effective_end_date
AND IPV3.element_type_id
= ELI3.element_type_id
AND UPPER(IPV3.name)
= 'TAX SEPARATELY');
SELECT hr_assignment_sets_s.nextval
INTO l_ts_asg_set_id
FROM sys.dual;
INSERT INTO hr_assignment_sets (
ASSIGNMENT_SET_ID
,BUSINESS_GROUP_ID
,PAYROLL_ID
,ASSIGNMENT_SET_NAME)
VALUES ( l_ts_asg_set_id
,p_business_group_id
,p_payroll_id
,l_set_name);
INSERT INTO hr_assignment_set_amendments
(ASSIGNMENT_ID
,ASSIGNMENT_SET_ID
,INCLUDE_OR_EXCLUDE
)
VALUES
( asgrec.assignment_id
,g_taxsep_asg_sets.set_id(l_ts_counter)
,'I'
);
SELECT ASA.assignment_id
FROM pay_assignment_actions ASA
WHERE ASA.payroll_action_id = p_pyrll_action_id
AND ASA.action_status = 'C';
SELECT COUNT(ELE.element_entry_id)
INTO l_sc_count
FROM pay_element_entries_f ELE,
pay_element_entry_values_f EEV,
pay_input_values_f IPV
WHERE ELE.assignment_id = asgrec.assignment_id
AND fnd_date.canonical_to_date(p_payact_earned_date)
BETWEEN ELE.effective_start_date
AND ELE.effective_end_date
AND EEV.element_entry_id = ELE.element_entry_id
AND NVL(EEV.screen_entry_value,'N') = 'Y'
AND fnd_date.canonical_to_date(p_payact_earned_date)
BETWEEN EEV.effective_start_date
AND EEV.effective_end_date
AND IPV.input_value_id = EEV.input_value_id
AND UPPER(IPV.name) = 'SEPARATE CHECK';
SELECT hr_assignment_sets_s.nextval
INTO l_sc_asg_set_id
FROM sys.dual;
INSERT INTO hr_assignment_sets (
ASSIGNMENT_SET_ID
,BUSINESS_GROUP_ID
,PAYROLL_ID
,ASSIGNMENT_SET_NAME)
VALUES ( l_sc_asg_set_id
,p_business_group_id
,p_payroll_id
,l_set_name);
INSERT INTO hr_assignment_set_amendments
(ASSIGNMENT_ID
,ASSIGNMENT_SET_ID
,INCLUDE_OR_EXCLUDE
)
VALUES
( asgrec.assignment_id
,g_sepcheck_asg_sets.set_id(l_sc_counter)
,'I'
);
select 1
into dummy
from sys.dual
where exists (select ''
from pay_assignment_actions
where payroll_action_id = p_payroll_act_id
and action_status <> 'C');
procedure delete_sepcheck_asg_set
is
l_num_sepchecks number;
delete from hr_assignment_set_amendments
where ASSIGNMENT_SET_ID = g_sepcheck_asg_sets.set_id(l_sc_counter);
delete from hr_assignment_sets
where ASSIGNMENT_SET_ID = g_sepcheck_asg_sets.set_id(l_sc_counter);
end delete_sepcheck_asg_set;
procedure delete_taxsep_asg_set
is
l_num_taxsep number;
delete from hr_assignment_set_amendments
where ASSIGNMENT_SET_ID = g_taxsep_asg_sets.set_id(l_ts_counter);
delete from hr_assignment_sets
where ASSIGNMENT_SET_ID = g_taxsep_asg_sets.set_id(l_ts_counter);
end delete_taxsep_asg_set;
select payroll_action_id
from pay_payroll_actions
where target_payroll_action_id = p_primary_action_id
and payroll_id = p_payroll_id
and action_type = 'R'
and effective_date = fnd_date.canonical_to_date(p_date_paid)
and legislative_parameters like '%SPECIALPROC%'
order by action_sequence;
select payroll_action_id
from pay_payroll_actions
where target_payroll_action_id = p_primary_action_id
and payroll_id = p_payroll_id
and action_type = 'R'
and effective_date = fnd_date.canonical_to_date(p_date_paid)
and legislative_parameters like '%SEPCHECK%'
order by action_sequence;
select distinct asg2.payroll_action_id
into l_prepay_id
from pay_assignment_actions asg2,
pay_action_interlocks pai,
pay_payroll_actions ppa,
pay_assignment_actions asg1
where asg1.payroll_action_id = actrec.payroll_action_id
and asg1.assignment_action_id = pai.locked_action_id
and asg2.assignment_action_id = pai.locking_action_id
and ppa.payroll_action_id = asg2.payroll_action_id
and ppa.action_type = 'P';
update pay_payroll_actions
set consolidation_set_id = p_sc_con_set_id
where payroll_action_id = actrec.payroll_action_id;
SELECT payroll_action_id
INTO l_sc_preact_id
FROM pay_payroll_actions
WHERE request_id = l_req_id
AND payroll_id = p_payroll_id
AND action_type = 'P'
AND effective_date = fnd_date.canonical_to_date(p_date_paid);
update pay_payroll_actions
set consolidation_set_id = p_master_con_set_id
where payroll_action_id = actrec.payroll_action_id;
update pay_payroll_actions
set consolidation_set_id = p_master_con_set_id,
target_payroll_action_id = p_primary_action_id
where payroll_action_id = l_sc_preact_id;
SELECT pay_consolidation_sets_s.nextval
INTO l_sc_consoset_id
FROM sys.dual;
INSERT INTO pay_consolidation_sets (
CONSOLIDATION_SET_ID,
BUSINESS_GROUP_ID,
CONSOLIDATION_SET_NAME)
VALUES ( l_sc_consoset_id,
p_business_group_id,
l_sc_consoset_name);
SELECT payroll_action_id
INTO l_sc_payact_id
FROM pay_payroll_actions
WHERE request_id = l_req_id
AND payroll_id = p_payroll_id
AND action_type = 'R'
AND effective_date = fnd_date.canonical_to_date(p_date_paid);
SELECT payroll_action_id
INTO l_sc_preact_id
FROM pay_payroll_actions
WHERE request_id = l_req_id
AND payroll_id = p_payroll_id
AND action_type = 'P'
AND effective_date = fnd_date.canonical_to_date(p_date_paid);
UPDATE pay_payroll_actions
SET consolidation_set_id = p_consolidation_set_id,
assignment_set_id = NULL,
target_payroll_action_id = p_primary_action_id
WHERE payroll_action_id = l_sc_payact_id;
UPDATE pay_payroll_actions
SET consolidation_set_id = p_consolidation_set_id,
target_payroll_action_id = p_primary_action_id
WHERE payroll_action_id = l_sc_preact_id;
DELETE from pay_consolidation_sets
WHERE consolidation_set_id = l_sc_preact_id;
delete_sepcheck_asg_set;
SELECT payroll_action_id
INTO l_ts_payact_id
FROM pay_payroll_actions
WHERE request_id = l_req_id
AND payroll_id = p_payroll_id
AND action_type = 'R'
AND effective_date = fnd_date.canonical_to_date(p_date_paid);
UPDATE pay_payroll_actions
SET assignment_set_id = NULL,
target_payroll_action_id = p_primary_action_id
WHERE payroll_action_id = l_ts_payact_id;
delete_taxsep_asg_set;
SELECT DISTINCT business_group_id
INTO l_business_group_id
FROM pay_payrolls_f
WHERE payroll_id = p_payroll_id;
SELECT payroll_action_id
INTO l_payact_id
FROM pay_payroll_actions
WHERE request_id = l_req_id
AND payroll_id = p_payroll_id
AND action_type = 'R'
AND effective_date = fnd_date.canonical_to_date(p_date_paid);
UPDATE pay_payroll_actions
SET pay_advice_message = p_pay_advice_message
WHERE payroll_action_id = l_payact_id;
select payroll_id,
consolidation_set_id,
fnd_date.canonical_to_date(date_earned),
fnd_date.canonical_to_date(effective_date),
assignment_set_id,
element_set_id,
business_group_id
into l_payroll_id,
l_consolidation_set_id,
l_earned_date,
l_date_paid,
l_assignment_set_id,
l_ele_set_id,
l_business_group_id
from pay_payroll_actions
where payroll_action_id = l_primary_action;
UPDATE pay_payroll_actions
SET assignment_set_id = NULL
WHERE assignment_set_id = p_assignment_set_id;
DELETE FROM hr_assignment_set_amendments
WHERE assignment_set_id = p_assignment_set_id;