[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
18-NOV-2004 asasthan 115.4 3264740 Updated output_type for HTML
14-MAR-2005 sackumar 115.6 4222032 Change in the Range Cursor removing redundant
use of bind Variable (:payroll_action_id)
*******************************************************************/
/******************************************************************
** Package Local Variables
******************************************************************/
gv_package varchar2(50);
select ppa.start_date
,ppa.effective_date
,ppa.report_type
,ppa.report_qualifier
,ppa.business_group_id
,pay_us_payroll_utils.get_parameter('S_N',
ppa.legislative_parameters)
from pay_payroll_actions ppa
where payroll_action_id = cp_payroll_action_id;
select paa.assignment_Action_id
from pay_assignment_actions paa,
per_all_assignments_f paf,
pay_payroll_actions ppa
where ppa.business_group_id = cp_business_group_id
and ppa.effective_date between cp_start_date and cp_end_date
and ppa.action_type = 'X'
and ppa.report_type = 'W-2C PAPER'
and ppa.action_status = 'C'
and ppa.payroll_action_id = paa.payroll_action_id
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 pay_Action_interlocks pai,
pay_assignment_actions paa1,
pay_payroll_actions ppa1
where pai.locked_action_id = paa.assignment_action_id
and paa1.assignment_action_id = pai.locking_action_id
and ppa1.payroll_action_id = paa1.payroll_action_id
and ppa1.effective_date between cp_start_date and cp_end_date
and ppa1.action_type = 'X'
and ppa1.report_type = 'MARK_W2C_PAPER'
and ppa1.report_category = 'RT'
and ppa1.action_status = 'C')
and not exists
(select 'x' from pay_Action_interlocks pai,
pay_assignment_actions paa1,
pay_payroll_actions ppa1
where pai.locked_action_id = paa.assignment_action_id
and paa1.assignment_action_id = pai.locking_action_id
and ppa1.payroll_action_id = paa1.payroll_action_id
and ppa1.effective_date between cp_start_date and cp_end_date
and ppa1.action_type = 'X'
and ppa1.report_type = 'W2C'
and ppa1.report_qualifier = 'FED'
and ppa1.report_category = 'RM'
and ppa1.action_status = 'C');
'select distinct paf.person_id
from pay_assignment_actions paa,
per_all_assignments_f paf,
pay_payroll_actions ppa
where ppa.business_group_id = '|| ln_business_group_id || '
and ppa.effective_date between to_date(''' ||
to_char(ld_start_date, 'dd-mon-yyyy') || ''',''dd-mon-yyyy'')
and to_date(''' || to_char(ld_end_date, 'dd-mon-yyyy') || ''',''dd-mon-yyyy'')
and ppa.action_type = ''X''
and ppa.report_type = ''W-2C PAPER''
and ppa.action_status = ''C''
and ppa.payroll_action_id = paa.payroll_action_id
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 :payroll_action_id is not null
and not exists
(select ''x'' from pay_Action_interlocks pai,
pay_assignment_actions paa1,
pay_payroll_actions ppa1
where pai.locked_action_id = paa.assignment_action_id
and paa1.assignment_action_id = pai.locking_action_id
and ppa1.payroll_action_id = paa1.payroll_action_id
and ppa1.effective_date between to_date(''' ||
to_char(ld_start_date, 'dd-mon-yyyy') || ''',''dd-mon-yyyy'')
and to_date(''' || to_char(ld_end_date, 'dd-mon-yyyy') || ''',''dd-mon-yyyy'')
and ppa1.action_type = ''X''
and ppa1.report_type = ''MARK_W2C_PAPER''
and ppa1.action_status = ''C'')
and not exists
(select ''x'' from pay_Action_interlocks pai,
pay_assignment_actions paa1,
pay_payroll_actions ppa1
where pai.locked_action_id = paa.assignment_action_id
and paa1.assignment_action_id = pai.locking_action_id
and ppa1.payroll_action_id = paa1.payroll_action_id
and ppa1.effective_date between to_date(''' ||
to_char(ld_start_date, 'dd-mon-yyyy') || ''',''dd-mon-yyyy'')
and to_date(''' || to_char(ld_end_date, 'dd-mon-yyyy') || ''',''dd-mon-yyyy'')
and ppa1.action_type = ''X''
and ppa1.report_type = ''W2C''
and ppa1.report_qualifier = ''FED''
and ppa1.action_status = ''C'')';
select paa.assignment_id,
paa.tax_unit_id,
paf.person_id,
paa.assignment_Action_id
from pay_assignment_actions paa,
per_all_assignments_f paf,
pay_payroll_actions ppa
where ppa.business_group_id = cp_business_group_id
and ppa.effective_date between cp_start_date and cp_end_date
and ppa.action_type = 'X'
and ppa.report_type = 'W-2C PAPER'
and ppa.action_status = 'C'
and ppa.payroll_action_id = paa.payroll_action_id
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 paf.person_id between cp_start_person_id
and cp_end_person_id
and not exists
(select 'x' from pay_Action_interlocks pai,
pay_assignment_actions paa1,
pay_payroll_actions ppa1
where pai.locked_action_id = paa.assignment_action_id
and paa1.assignment_action_id = pai.locking_action_id
and ppa1.payroll_action_id = paa1.payroll_action_id
and ppa1.effective_date between cp_start_date and cp_end_date
and ppa1.action_type = 'X'
and ppa1.report_type = 'MARK_W2C_PAPER'
and ppa1.report_category = 'RT'
and ppa1.action_status = 'C')
and not exists
(select 'x' from pay_Action_interlocks pai,
pay_assignment_actions paa1,
pay_payroll_actions ppa1
where pai.locked_action_id = paa.assignment_action_id
and paa1.assignment_action_id = pai.locking_action_id
and ppa1.payroll_action_id = paa1.payroll_action_id
and ppa1.effective_date between cp_start_date and cp_end_date
and ppa1.action_type = 'X'
and ppa1.report_type = 'W2C'
and ppa1.report_qualifier = 'FED'
and ppa1.report_category = 'RM'
and ppa1.action_status = 'C');
select ppf.full_name, ppf.national_identifier, ppf.employee_number,
paf.assignment_number
from per_all_people_f ppf,
per_all_assignments_f paf
where ppf.person_id = cp_person_id
and paf.person_id = ppf.person_id
and cp_end_date between ppf.effective_start_date
and ppf.effective_end_date
and cp_end_date between paf.effective_start_date
and paf.effective_end_date;
SELECT name
FROM hr_organization_units
WHERE organization_id = cp_tax_unit_id;
select creation_date
from pay_payroll_actions ppa,
pay_assignment_actions paa
where paa.assignment_action_id = cp_assignment_action_id
and ppa.payroll_action_id = paa.payroll_action_id;
select sysdate into lv_sysdate from dual;
select pay_assignment_actions_s.nextval
into ln_w2c_asg_action
from dual;
/* Insert into pay_assignment_actions. */
hr_utility.trace('Creating Assignment Action');
/* Update the serial number column with the person id
so that the W2C report will not have
to do an additional checking against the assignment
table */
hr_utility.set_location(gv_package || '.action_creation', 30);
update pay_assignment_actions aa
set aa.serial_number = lp_person_id
where aa.assignment_action_id = ln_w2c_asg_action;
insert into pay_us_rpt_totals
(GRE_NAME,
STATE_NAME,
ATTRIBUTE1, -- FULL_NAME
ATTRIBUTE2, -- NATIONAL_IDENTIFIER
ATTRIBUTE3, -- EMPLOYEE_NUMBER
ATTRIBUTE4, -- ASSIGNMENT_NUMBER
ATTRIBUTE5, -- ASSIGNMENT_ACTION_ID
ATTRIBUTE6, -- YEAR
SESSION_ID, -- SESSION_ID
ATTRIBUTE7, -- PAPER_CREATION_DATE
ATTRIBUTE8 -- SYSDATE
)
VALUES
(lv_gre_name,
'MARKW2C_PROCESS',
lv_full_name,
lv_national_identifier,
lv_employee_number,
lv_assignment_number,
lp_assignment_action_id,
lv_year,
ln_seq_num,
lv_creation_date,
lv_sysdate
);
hr_utility.trace('Inserted lv_gre_name ' || lv_gre_name);
hr_utility.trace('Inserted lv_full_name ' || lv_full_name);
hr_utility.trace('Inserted lv_natidentifier' ||lv_national_identifier);
hr_utility.trace('Inserted lv_employee_number' ||lv_employee_number);
hr_utility.trace('Inserted lv_assignment_number' ||lv_assignment_number);
hr_utility.trace('Inserted lp_aaid' ||to_char(lp_assignment_action_id));
PROCEDURE select_ee_details(errbuf OUT nocopy VARCHAR2,
retcode OUT nocopy NUMBER,
p_seq_num IN VARCHAR2,
p_output_file_type IN VARCHAR2)
IS
cursor c_get_lookup_code(cp_lookup_meaning in varchar2) is
select lookup_code from hr_lookups
where lookup_type = 'REPORT_OUTPUT_TYPE'
and meaning = cp_lookup_meaning
and application_id = 800;
SELECT
gre_name,
attribute1, --full_name,
attribute2, --national_identifier,
attribute3, -- employee_number,
attribute4, -- assignment_number,
attribute6, -- year
attribute7, -- PAPER_CREATION_DATE
attribute8 -- Sysdate
FROM pay_us_rpt_totals
WHERE state_name = 'MARKW2C_PROCESS'
AND session_id = to_number(c_seq_num)
ORDER BY attribute6,gre_name, attribute1,attribute4,attribute5;
UPDATE fnd_concurrent_requests
SET output_file_type = 'HTML'
WHERE request_id = FND_GLOBAL.CONC_REQUEST_ID ;
DELETE FROM pay_us_rpt_totals
WHERE session_id = to_number(p_seq_num);
END select_ee_details;