[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
25-OCT-2004 schauhan 115.11 3601799 Added selection criteria for "All"
if the report is Run with print
option "Reprint All W2c".
Made changes to w2crpt_range_cursor
and w2crpt_action_creation Cursor.
05-NOV-2004 schauhan 115.12 Added 'Distinct' to the Range Cursor
w2crpt_range_cursor.
22-NOV-2004 ahanda 115.13 3601799 Fixed issue in the bug. Changed the
action creation, range and sort
procedures.
16-DEC-2004 ahanda 115.14 4039440 Changed sort code to reduce length
to get around c-code issue.
14-MAR-2005 sackumar 115.15 4222032 Change in the Range Cursor removing redundant
use of bind Variable (:payroll_action_id)
04-AUG-2005 pragupta 115.16 3679317 Change in procedure sort_action. Removed
+0 from paf and hou for removing FTS and
performance improvement
14-MAR-2006 ppanda 115.17 4583575 To reduce the shared memory per_all_assignments_f
used instead of per_assignments_f.
31-MAR-2006 sodhingr 115.18 5111088 removed the comment from sort_cursor to fix signal
11 error.
05-MAR-2009 asgugupt 115.19 6349762 Adding Order by clause in Range Cursor
05-FEB-2013 skchalla 115.20 13599887 Added procedures/ffunction required to
generate the XML for W-2c
06-FEB-2013 skchalla 115.21 13599887 Added an escape sequence to resolve the compilation error.
Modified the CP parameter 'GRE_ID' to 'TRANSFER_GRE' to use the
same parameter for both W2-c paper and W2-c PDF
06-FEB-2013 skchalla 115.23 13599887 Added locking mechanism for W2-c PDF.
08-Feb-2013 skchalla 115.24 13599887 Removed invalid package references.
22-Feb-2013 skchalla 115.25 16000014 Removed the logical error in get_w2c_data
fuction while processing the Box 12
28-Feb-2013 skchalla 115.26 16391213 Modified the get_w2c_data function not to generate
extra W-2c copies.
01-Mar-2013 skchalla 115.27 16391171 Modified the get_w2c_data function.
01-Mar-2013 skchalla 115.28 16391171 Modified the get_w2c_data function for reporting the
NJ FLI balance.
04-Mar-2013 skchalla 115.29 16426068 Modified the get_w2c_data function.
06-Mar-2013 skchalla 115.30 16434271 Modified the get_w2c_data function and
create_xml_string function
07-Mar-2013 skchalla 115.31 16440238 Modified create_xml_string fuction.
18-Mar-2013 pkoduri 115.32 16434271 Corrected the date to be passed to fetch the State EIN.
this has to be YEPP arvhive date.
19-Mar-2013 skchalla 115.33 16398337 Modified the sort_action cursor.
20-Mar-201 skchalla 115.34 16398337 Modified the sort_action cursor.
04-Apr-2013 skchalla 115.35 16571508 Restricting the XML generation when there are no corrections.
10-Apr-2013 skchalla 115.36 16571508 Modified the code to display '-0-' instead of null when
any monetary field being changed either from or to a zero.
23-May-2013 skchalla 115.37 16815610 Modified the get_w2c_data function to remove the extra spaces from the values.
24-May-2013 skchalla 115.38 16815610 Replaced to_number funtion with the trim function to get the decimal places always
*****************************************************************************/
gv_procedure_name VARCHAR2(100);
select
pay_us_payroll_utils.get_parameter('TRANSFER_GRE',ppa.legislative_parameters),
pay_us_payroll_utils.get_parameter('PER_ID',ppa.legislative_parameters),
pay_us_payroll_utils.get_parameter('ASG_SET',ppa.legislative_parameters),
pay_us_payroll_utils.get_parameter('PRINT',ppa.legislative_parameters),
effective_date,
start_date,
business_group_id,
pay_us_payroll_utils.get_parameter('S1',ppa.legislative_parameters),
pay_us_payroll_utils.get_parameter('S2',ppa.legislative_parameters),
pay_us_payroll_utils.get_parameter('S3',ppa.legislative_parameters),
to_date(pay_us_payroll_utils.get_parameter('EFFECTIVE_DATE',
ppa.legislative_parameters)
,'dd-mon-yyyy')
from pay_payroll_actions ppa
where ppa.payroll_action_id = cp_payroll_action_id;
Purpose : This returns the select statement that is
used to created the range rows for the
W-2C PAPER.
Arguments :
Notes : Calls procedure - get_payroll_action_info
******************************************************************/
PROCEDURE w2crpt_range_cursor(
p_payroll_action_id in number
,p_sqlstr out nocopy varchar2)
IS
ld_end_date DATE;
'select distinct asg.person_id person_id
from per_all_assignments_f asg
where person_id = ' || ln_person_id ||
' and :p_payroll_action_id is not null ';
'select distinct paf.person_id
from hr_assignment_set_amendments asgset,
per_all_assignments_f paf
where assignment_set_id = ' || ln_asg_set || '
and asgset.assignment_id = paf.assignment_id
and asgset.include_or_exclude = ''I''
and :payroll_action_id is not null order by paf.person_id';
'select distinct paa.serial_number
from pay_assignment_actions paa,
pay_payroll_actions ppa
where ppa.report_type = ''W2C_PRE_PROCESS''
and ppa.effective_date = add_months(''' || ld_start_date || ''',12) -1
and ppa.business_group_id+0 = ' || ln_business_group_id ||'
and ltrim(rtrim(
substr(ppa.legislative_parameters,
instr(ppa.legislative_parameters,''TRANSFER_GRE='')
+ length(''TRANSFER_GRE='')
,instr(ppa.legislative_parameters,'' '',2))))
= to_char(' || ln_tax_unit_id || ')
and paa.payroll_action_id = ppa.payroll_action_id
and paa.action_status = ''C''
and paa.tax_unit_id = ' || ln_tax_unit_id || '
and :payroll_action_id is not null
order by paa.serial_number';
CURSOR c_selected_asg_set(cp_start_person in number
,cp_end_person in number
,cp_asg_set in number) is
select distinct paf.person_id
from hr_assignment_set_amendments asgset,
per_all_assignments_f paf
where assignment_set_id = cp_asg_set
and asgset.include_or_exclude = 'I'
and paf.assignment_id = asgset.assignment_id
and paf.person_id between cp_start_person
and cp_end_person;
CURSOR c_select_all_person(cp_start_person in number,
cp_end_person in number,
cp_start_date in date,
cp_business_group_id in number,
cp_tax_unit_id in number) IS
select distinct paf.person_id
from pay_assignment_actions paa,
pay_payroll_actions ppa,
per_all_assignments_f paf
where ppa.report_type = 'W-2C PAPER'
and ppa.report_category = 'REPORT'
and ppa.report_qualifier = 'DEFAULT'
and ppa.effective_date = add_months(cp_start_date,12) -1
and ppa.business_group_id = cp_business_group_id
and ppa.legislative_parameters like '%' || cp_tax_unit_id || '%'
and paa.payroll_action_id = ppa.payroll_action_id
and paa.action_status = 'C'
and paa.tax_unit_id = cp_tax_unit_id
and paf.assignment_id = paa.assignment_id
and paf.effective_end_date =
(SELECT max(paf1.effective_end_date)
FROM per_all_assignments_f paf1
WHERE paf1.assignment_id = paf.assignment_id
AND paf1.effective_start_date <= ppa.effective_date)
and paf.person_id between cp_start_person and cp_end_person;
select ppa.report_type, paa.assignment_id,
paa.assignment_action_id
from pay_payroll_actions ppa,
pay_assignment_actions paa,
per_all_assignments_f paf
where paa.assignment_id = paf.assignment_id
and paf.person_id = cp_person_id
and paf.effective_start_date <= cp_effective_date
and paf.effective_end_date >= cp_start_date
and paa.tax_unit_id = cp_tax_unit_id
and paa.action_status = 'C'
and ppa.payroll_action_id = paa.payroll_action_id
and ppa.effective_date = cp_effective_date
and ppa.report_type in ('W2C_PRE_PROCESS','W2C_XML','W-2C PAPER')
and paf.effective_end_date =
(SELECT max(paf1.effective_end_date)
FROM per_all_assignments_f paf1
WHERE paf1.assignment_id = paf.assignment_id
AND paf1.effective_start_date <= ppa.effective_date)
order by paa.assignment_action_id desc;
select ppa.report_type, paa.assignment_id,
paa.assignment_action_id
from pay_payroll_actions ppa,
pay_assignment_actions paa,
per_all_assignments_f paf
where paa.assignment_id = paf.assignment_id
and paf.person_id = cp_person_id
and paf.effective_start_date <= cp_effective_date
and paf.effective_end_date >= cp_start_date
and paa.tax_unit_id = cp_tax_unit_id
and paa.action_status = 'C'
and ppa.payroll_action_id = paa.payroll_action_id
and ppa.effective_date = cp_effective_date
and ppa.report_type = 'W2C_PRE_PROCESS'
and ppa.report_category = 'RT'
and ppa.report_qualifier = 'FED'
and ppa.business_group_id = cp_business_group_id
and paf.effective_end_date =
(SELECT max(paf1.effective_end_date)
FROM per_all_assignments_f paf1
WHERE paf1.assignment_id = paf.assignment_id
AND paf1.effective_start_date <= ppa.effective_date)
and exists (select 1
from pay_action_interlocks pai,
pay_assignment_actions paa_paper,
pay_payroll_Actions ppa_paper
where pai.locked_action_id = paa.assignment_action_id
and paa_paper.assignment_Action_id = pai.locking_action_id
and ppa_paper.payroll_Action_id = paa_paper.payroll_Action_id
and ppa_paper.report_type in ('W-2C PAPER','W2C_XML')
and ppa_paper.report_category = 'REPORT'
and ppa_paper.report_qualifier = 'DEFAULT'
and ppa_paper.effective_date = cp_effective_date
and ppa_paper.business_group_id = cp_business_group_id)
order by paa.assignment_action_id desc;
select ppa.report_type,
paa.assignment_action_id,
substr(paa.serial_number, 1,15) prev_action_id
from pay_payroll_actions ppa,
pay_assignment_actions paa,
pay_action_interlocks pai
where pai.locking_action_id = cp_w2cpp_action
and paa.assignment_action_id = pai.locked_action_id
and ppa.payroll_action_id = paa.payroll_action_id;
select substr(full_name,1,48), employee_number
from per_all_people_f
where person_id = cp_person_id
order by effective_end_date desc;
select pay_assignment_actions_s.nextval
into ln_w2c_asg_action
from dual;
/* Insert into pay_assignment_actions. */
hr_nonrun_asact.insact(ln_w2c_asg_action
,ln_primary_assignment_id
,p_payroll_action_id
,p_chunk
,ln_tax_unit_id);
** Update the serial number column with the assignment action
** of the last two archive processes
***************************************************************/
ln_serial_number := lpad(ln_asg_act_to_lock,15,0)||
lpad(ln_second_last_arch_action,15,0);
update pay_assignment_actions aa
set aa.serial_number = ln_serial_number
where aa.assignment_action_id = ln_w2c_asg_action;
select pay_assignment_actions_s.nextval
into ln_w2c_asg_action
from dual;
/* Insert into pay_assignment_actions. */
hr_nonrun_asact.insact(ln_w2c_asg_action
,ln_primary_assignment_id
,p_payroll_action_id
,p_chunk
,ln_tax_unit_id);
** Update the serial number column with the assignment action
** of the last two archive processes
***************************************************************/
ln_serial_number := lpad(ln_asg_act_to_lock,15,0)||
lpad(ln_second_last_arch_action,15,0);
update pay_assignment_actions aa
set aa.serial_number = ln_serial_number
where aa.assignment_action_id = ln_w2c_asg_action;
open c_selected_asg_set(p_start_person_id
,p_end_person_id
,ln_asg_set);
hr_utility.trace('Opened cusor c_selected_asg_set');
fetch c_selected_asg_set into ln_set_person_id;
if c_selected_asg_set%notfound then
hr_utility.trace('No Person found for reporting in this chunk');
close c_selected_asg_set;
open c_select_all_person(p_start_person_id
,p_end_person_id
,ld_start_date
,ln_business_group_id
,ln_tax_unit_id);
hr_utility.trace('Opened cusor c_select_all_person');
fetch c_select_all_person into ln_person_id;
if c_select_all_person%notfound then
hr_utility.trace('No Person found for reporting in this chunk.');
close c_select_all_person;
select to_char(ld_end_date,'YYYY') into l_year from dual;
'select mt.rowid
from hr_organization_units hou, hr_locations_all hl,
per_periods_of_service pps, per_all_assignments_f paf,
pay_assignment_actions mt
where mt.payroll_action_id = :p_payroll_action_id
and paf.assignment_id = mt.assignment_id -- Bug 3679317 ( +0 removed)
and paf.effective_start_date = (select max(paf2.effective_start_date)
from per_all_assignments_f paf2 where paf2.assignment_id = paf.assignment_id
and paf2.effective_start_date <= to_date(''31-DEC-''||'''||l_year||''',''DD-MM-YYYY''))
and paf.effective_end_date >= to_date(''01-JAN-''||'''||l_year||''',''DD-MM-YYYY'')
and paf.assignment_type = ''E'' and hou.organization_id = paf.organization_id
and pps.period_of_service_id = paf.period_of_service_id
and pps.person_id = paf.person_id and hl.location_id = paf.location_id
and hou.business_group_id = '''|| ln_business_group_id ||'''
order by decode('''||lv_sort1||''', ''Employee_Name'',
hr_us_w2_rep.get_per_item(to_number(substr(mt.serial_number,1,15)), ''A_PER_LAST_NAME'')||'' ''
||hr_us_w2_rep.get_per_item(to_number(substr(mt.serial_number,1,15)), ''A_PER_FIRST_NAME'')||'' ''
||substr(hr_us_w2_rep.get_per_item(to_number(substr(mt.serial_number,1,15)), ''A_PER_MIDDLE_NAMES''),1,1),
''SSN'',nvl(hr_us_w2_rep.get_per_item( to_number(substr(mt.serial_number,1,15)), ''A_PER_NATIONAL_IDENTIFIER''),
''Applied For''),
''Zip_Code'',hr_us_w2_rep.get_w2_postal_code( paf.person_id,'''|| ld_session_date ||'''),
''Organization'',hou.name, ''Location'',hl.location_code,
''Termination_Reason'',decode(leaving_reason,null,''ZZ'',hr_us_w2_rep.get_leav_reason(leaving_reason)),
hr_us_w2_rep.get_per_item(to_number(substr(mt.serial_number,1,15)), ''A_PER_LAST_NAME'')||'' ''
||hr_us_w2_rep.get_per_item(to_number(substr(mt.serial_number,1,15)), ''A_PER_FIRST_NAME'')||'' ''
||substr(hr_us_w2_rep.get_per_item(to_number(substr(mt.serial_number,1,15)), ''A_PER_MIDDLE_NAMES''),1,1)),
decode('''||lv_sort2||''', ''Employee_Name'',
hr_us_w2_rep.get_per_item(to_number(substr(mt.serial_number,1,15)), ''A_PER_LAST_NAME'')||'' ''
||hr_us_w2_rep.get_per_item(to_number(substr(mt.serial_number,1,15)), ''A_PER_FIRST_NAME'')||'' ''
||substr(hr_us_w2_rep.get_per_item(to_number(substr(mt.serial_number,1,15)), ''A_PER_MIDDLE_NAMES''),1,1),
''SSN'',nvl(hr_us_w2_rep.get_per_item( to_number(substr(mt.serial_number,1,15)), ''A_PER_NATIONAL_IDENTIFIER''), ''Applied For''),
''Zip_Code'',hr_us_w2_rep.get_w2_postal_code( paf.person_id,'''|| ld_session_date ||'''),
''Organization'',hou.name, ''Location'',hl.location_code,
''Termination_Reason'',decode(leaving_reason,null,''ZZ'',hr_us_w2_rep.get_leav_reason(leaving_reason)),
hr_us_w2_rep.get_per_item(to_number(substr(mt.serial_number,1,15)), ''A_PER_LAST_NAME'')||'' ''
||hr_us_w2_rep.get_per_item(to_number(substr(mt.serial_number,1,15)), ''A_PER_FIRST_NAME'')||'' ''
||substr(hr_us_w2_rep.get_per_item(to_number(substr(mt.serial_number,1,15)), ''A_PER_MIDDLE_NAMES''),1,1)),
decode('''||lv_sort3||''', ''Employee_Name'', hr_us_w2_rep.get_per_item(to_number(substr(mt.serial_number,1,15)),
''A_PER_LAST_NAME'')||'' ''
||hr_us_w2_rep.get_per_item(to_number(substr(mt.serial_number,1,15)), ''A_PER_FIRST_NAME'')||'' ''
||substr(hr_us_w2_rep.get_per_item(to_number(substr(mt.serial_number,1,15)), ''A_PER_MIDDLE_NAMES''),1,1),
''SSN'',nvl(hr_us_w2_rep.get_per_item( to_number(substr(mt.serial_number,1,15)), ''A_PER_NATIONAL_IDENTIFIER''),
''Applied For''), ''Zip_Code'',hr_us_w2_rep.get_w2_postal_code( paf.person_id,'''|| ld_session_date ||'''),
''Organization'',hou.name, ''Location'',hl.location_code, ''Termination_Reason'',decode(leaving_reason,null,''ZZ'',
hr_us_w2_rep.get_leav_reason(leaving_reason)),
hr_us_w2_rep.get_per_item(to_number(substr(mt.serial_number,1,15)), ''A_PER_LAST_NAME'')||'' ''
||hr_us_w2_rep.get_per_item(to_number(substr(mt.serial_number,1,15)), ''A_PER_FIRST_NAME'')||'' ''
||substr(hr_us_w2_rep.get_per_item(to_number(substr(mt.serial_number,1,15)), ''A_PER_MIDDLE_NAMES''),1,1))';
select puw.assignment_action_id control_number,
nvl(ssn,'Applied For') SSN,
first_name||
decode(middle_name,null,' ',
' '||substr(middle_name,1,1)||' ') ||
pre_name_adjunt emp_name,
last_name ,
hr_us_w2_rep.get_per_item(p_asg_action_id,
'A_PER_SUFFIX') emp_suffix,
trim(to_char(decode(W2_WAGES_TIPS_COMPENSATION,0,'',W2_WAGES_TIPS_COMPENSATION),'9999999990.99')) wages_tips_compensation,
trim(to_char(decode(W2_FED_IT_WITHHELD,0,'',W2_FED_IT_WITHHELD),'9999999990.99')) fit_withheld,
trim(to_char(decode(W2_SOCIAL_SECURITY_WAGES,0,'',W2_SOCIAL_SECURITY_WAGES),'9999999990.99')) ss_wages,
trim(to_char(decode(W2_SST_WITHHELD,0,'',W2_SST_WITHHELD),'9999999990.99')) ss_withheld,
trim(to_char(decode(W2_MED_WAGES_TIPS,0,'',W2_MED_WAGES_TIPS),'9999999990.99')) med_wages,
trim(to_char(decode(W2_MED_TAX_WITHHELD,0,'',W2_MED_TAX_WITHHELD),'9999999990.99')) med_withheld,
trim(to_char(decode(W2_SOCIAL_SECURITY_TIPS,0,'',W2_SOCIAL_SECURITY_TIPS),'9999999990.99')) ss_tips,
trim(to_char(decode(W2_ALLOCATED_TIPS,0,'',W2_ALLOCATED_TIPS),'9999999990.99')) allocated_tips,
trim(to_char(decode(W2_ADV_EIC_PAYMENT,0,'',W2_ADV_EIC_PAYMENT),'9999999990.99')) eic_payment,
trim(to_char(decode(W2_DEPENDENT_CARE_BEN,0,'',W2_DEPENDENT_CARE_BEN),'9999999990.99')) dependent_care,
trim(to_char(decode(W2_NONQUAL_PLANS,0,'',W2_NONQUAL_PLANS),'9999999990.99')) non_qual_plan,
decode(W2_STATUTORY_EMPLOYEE,'X','Y',null,'N',' ','N')
stat_employee,
decode(W2_RETIREMENT_PLAN,'X','Y',null,'N',' ','N')
retirement_plan,
decode(W2_THIRD_PARTY_SICK_PAY,'X','Y',null,
'N',' ','N') sick_pay,
person_id , puw.assignment_id -- bug 5575567
from pay_us_wages_w2c_v puw
--per_addresses pa
where puw.assignment_action_id = p_asg_action_id
/* bug 5575567
and pa.primary_flag = 'Y'
and pa.person_id = puw.person_id */
and puw.tax_unit_id = p_tax_unit_id
and puw.year = p_year;
select w2_balance_code,
trim(to_char(decode(w2_balance_code_value,0,null,w2_balance_code_value),'9999999990.99'))
from pay_us_code_w2c_v
where assignment_action_id = p_asg_action_id
and tax_unit_id = p_tax_unit_id
order by w2_balance_code;
select w2_balance_code,
trim(to_char(decode(w2_balance_code_value,0,null,w2_balance_code_value),'9999999990.99'))
from pay_us_code_w2c_v
where assignment_action_id = p_prev_asg_action_id
and w2_balance_code = p_code
and tax_unit_id = p_tax_unit_id
order by w2_balance_code;
SELECT substr(w2_other_meaning,1,10) w2_other_code,
trim(to_char(decode(w2_other_value,0,null,w2_other_value),'9999999990.99'))
from pay_us_other_w2c_v
where tax_unit_id = p_tax_unit_id
and assignment_action_id = p_asg_action_id;
SELECT substr(w2_other_meaning,1,10) w2_other_code,
trim(to_char(decode(w2_other_value,0,null,w2_other_value),'9999999990.99'))
from pay_us_other_w2c_v
where tax_unit_id = p_tax_unit_id
and substr(w2_other_meaning,1,10) = p_code
and assignment_action_id = p_prev_asg_action_id;
SELECT locality_name locality,
trim(to_char(decode(w2_local_wages,0,'',w2_local_wages),'9999999990.99')) locality_wages,
trim(to_char(decode(w2_local_income_tax,0,'',w2_local_income_tax),'9999999990.99')) locality_tax,
jurisdiction jurisdiction,
state_abbrev state_code,
tax_type
FROM pay_us_locality_w2c_v
WHERE assignment_action_id = p_asg_action_id
and tax_unit_id = p_tax_unit_id
order by state_code, tax_type;
SELECT trim(to_char(decode(w2_local_wages,0,'',w2_local_wages),'9999999990.99')) locality_wages,
trim(to_char(decode(w2_local_income_tax,0,'',w2_local_income_tax),'9999999990.99')) locality_tax
FROM pay_us_locality_w2c_v
WHERE assignment_action_id = p_prev_asg_action_id
and tax_unit_id = p_tax_unit_id
and jurisdiction = p_jurisdiction
and tax_type = p_tax_type;
SELECT 1 , substr(state_abbrev,1,2) state_code,
substr(state_ein,1,20) state_ein,
trim(to_char(decode(W2_STATE_WAGES,0,'',W2_STATE_WAGES),'9999999990.99')) state_wages,
trim(to_char(decode(W2_STATE_INCOME_TAX,0,'',W2_STATE_INCOME_TAX),'9999999990.99')) state_tax,
jurisdiction
FROM pay_us_state_w2c_v state
WHERE assignment_action_id = p_asg_action_id
/*and ( (w2_state_wages > 0) or
(W2_STATE_INCOME_TAX > 0) ) 6809739 */
and state_ein <> 'FLI P.P. #'
and tax_unit_id = p_tax_unit_id
union all
SELECT 2 , substr(state_abbrev,1,2) state_code,
substr(state_ein,1,20) state_ein,
trim(to_char(decode(W2_STATE_WAGES,0,'',W2_STATE_WAGES),'9999999990.99')) state_wages,
trim(decode(to_char(to_number(W2_STATE_INCOME_TAX ),'9999999990.99'),'0.0' , ' ',to_char(to_number(W2_STATE_INCOME_TAX ),'9999999990.99') || ' - FLI' )) state_tax,
jurisdiction
FROM pay_us_state_w2c_v state
WHERE assignment_action_id = p_asg_action_id
and ( (w2_state_wages <> ' ') or
(to_char(to_number(W2_STATE_INCOME_TAX ),'9999999990.99') <> 0) ) /* 6809739 */
and state_ein = 'FLI P.P. #'
and tax_unit_id = p_tax_unit_id
order by state_code , 1 ;
SELECT 1 ,
substr(state_ein,1,20) state_ein,
trim(to_char(decode(W2_STATE_WAGES,0,'',W2_STATE_WAGES),'9999999990.99')) state_wages,
trim(to_char(decode(W2_STATE_INCOME_TAX,0,'',W2_STATE_INCOME_TAX),'9999999990.99')) state_tax
FROM pay_us_state_w2c_v state
WHERE assignment_action_id = p_asg_action_id
/*and ( (w2_state_wages > 0) or
(W2_STATE_INCOME_TAX > 0) ) 6809739 */
and state_ein <> 'FLI P.P. #'
and tax_unit_id = p_tax_unit_id
and state_abbrev = p_state
order by 2,1 ;
SELECT 2 ,
substr(state_ein,1,20) state_ein,
nvl(W2_STATE_WAGES,'') state_wages,
trim(decode(to_char(to_number(W2_STATE_INCOME_TAX ),'9999999990.99'),'0.0' , ' ',to_char(to_number(W2_STATE_INCOME_TAX ),'9999999990.99') || ' - FLI' )) state_tax
FROM pay_us_state_w2c_v state
WHERE assignment_action_id = p_asg_action_id
and ( (w2_state_wages <> ' ') or
(to_char(to_number(W2_STATE_INCOME_TAX ),'9999999990.99') <> 0) ) /* 6809739 */
and state_ein = 'FLI P.P. #'
and tax_unit_id = p_tax_unit_id
and state_abbrev = p_state
order by 2,1 ;
select federal_ein federal_ein,
tax_unit_name employer_name,
decode(put.address_line_1,null,null,substr(put.address_line_1,1,45)||pay_us_w2c_rpt.EOL)||
decode(put.address_line_2,null,null,substr(put.address_line_2,1,45)||pay_us_w2c_rpt.EOL)||
decode(put.address_line_3,null,null,substr(put.address_line_3,1,45)||pay_us_w2c_rpt.EOL)||
decode(put.town_or_city,null,null,put.town_or_city||' ')||
decode(state,null,null,state||' ')||put.postal_code
employer_address
from pay_us_w2_tax_unit_v put
where tax_unit_id = p_tax_unit_id
and year = p_year;
select business_group_id
from hr_all_organization_units /*hr_organization_units*/
where organization_id = p_tax_unit_id;
select payroll_action_id
from pay_assignment_actions
where assignment_action_id = p_asg_action_id;
SELECT NVL(TO_DATE(TO_CHAR(TO_DATE(hr_us_w2_mt.get_parameter('EFFECTIVE_DATE',
ppa.legislative_parameters),'DD-MON-YYYY'),'YYYY/MM/DD'),'YYYY/MM/DD'),SYSDATE) session_date
FROM pay_payroll_actions ppa
WHERE payroll_action_id = pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID');
select ppa.payroll_action_id
from pay_payroll_actions ppa
where ppa.report_type = 'YREND'
and ppa.effective_date = cp_w2c_eff_date
and pay_us_w2c_arch.get_parameter('TRANSFER_GRE',ppa.legislative_parameters)
= cp_w2c_tax_unit_id;
select fai.value
into lv_ein
from ff_database_items fdi,
ff_archive_items fai,
ff_archive_item_contexts fac,
ff_archive_item_contexts fac1
where fai.user_entity_id = fdi.user_entity_id
and fai.context1 = to_char(ln_eoy_pactid)
and fac.archive_item_id = fai.archive_item_id
and ltrim(rtrim(fac.context)) = to_char(p_w2c_tax_unit_id)
and fac1.archive_item_id = fai.archive_item_id
and fac1.context = p_jurisdiction
and fdi.user_name = 'A_STATE_TAX_RULES_ORG_SIT_COMPANY_STATE_ID';
select l_first_name||decode(l_middle_name,null,' ',
' '||substr(l_middle_name,1,1)||' ') ||
hr_us_w2_rep.get_per_item(p_asg_action_id,
'A_PER_PREFIX' ) into l_w2c_fields_c.emp_name from dual;
select l_first_name||decode(l_middle_name,null,' ',
' '||substr(l_middle_name,1,1)||' ') ||
hr_us_w2_rep.get_per_item(p_prev_asg_action_id,
'A_PER_PREFIX' ) into l_w2c_fields_o.emp_name from dual;
select decode(l_box14_boonmh_value_c,0,'-0-',l_box14_boonmh_value_c) into l_box14_tab(l_b14_total_count+1).box14_meaning_c from dual;
select decode(l_box14_boonmh_value_o,0,'-0-',l_box14_boonmh_value_o) into l_box14_tab(l_b14_total_count+1).box14_meaning_o from dual;
select nvl(value,'N') into l_nr_flag
from ff_database_items fdi,
ff_archive_items fai
where user_name = 'A_IN_NR_FLAG'
and fdi.user_entity_id = fai.user_entity_id
and fai.context1 = p_asg_action_id;
select nvl(value,'00-000-0000') into l_nr_jd
from ff_database_items fdi,
ff_archive_items fai
where fdi.user_name = 'A_IN_RES_JD'
and fdi.user_entity_id = fai.user_entity_id
and context1 = p_asg_action_id;
select l_w2c_fields_c.emp_name||l_w2c_fields_c.last_name||' '||l_w2c_fields_c.emp_suffix
into lv_full_name from sys.dual;
l_state_local_tab.delete;
l_state_tab.delete;
l_local_tab.delete;
l_box12_tab.delete;
l_box14_tab.delete;
SELECT substr(paa.serial_number, 1,15),--W2c Pre process action id
substr(paa.serial_number, 16,15),--Prev pre process action id (W2c pre process/YEPP)
hr_us_w2_mt.get_parameter('TRANSFER_GRE',ppa.legislative_parameters),
hr_us_w2_mt.get_parameter('Year',ppa.legislative_parameters),
hr_us_w2_mt.get_parameter('p_instr_template',ppa.legislative_parameters),
hr_us_w2_mt.get_parameter('print_instrunction',ppa.legislative_parameters)
FROM pay_assignment_actions paa,
pay_payroll_actions ppa
where ppa.payroll_action_id = paa.payroll_action_id
and paa.assignment_action_id = pay_magtape_generic.get_parameter_value('TRANSFER_ACT_ID');
l_state_local_tab.delete;
l_state_tab.delete;
l_local_tab.delete;
l_box12_tab.delete;
l_box14_tab.delete;
select translate(ltrim(value),',',' ')
into TEMP_UTL
from v$parameter
where name = 'utl_file_dir';
select substrb(TEMP_UTL, 1, instr(TEMP_UTL,' ') - 1)
into g_temp_dir
from dual ;