[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
03-Jan-2011 skchalla 115.21 10287981 Modified employee_data(),employee_header() and insert_pr_w2_totals()
to Include New field Hire act wages (Y/N)
10435466 Modified employee_data(),employee_header() and insert_pr_w2_totals()
to Include New field to dsplay the Hire act wages
11-Jan-2011 skchalla 115.23 10435466 Modified employee_data() and resolved issue with the EE address line 3
03-Jan-2011 sgotlasw 115.24 13462230 Modified employee_data(),employee_header() and insert_pr_w2_totals()
to Include PR W2 Charitable Contributions, PR W2 Contr To Save and Double Money,
W2 Health Coverage.
21-Nov-2012 Pkoduri 115.25 15874201 Corrected the Employee Address line 1 and 2 to display 40 Chars on register.
*/
/************************************************************
** Local Package Variables
************************************************************/
gv_title VARCHAR2(100) := 'W-3 PR Transmittal of Withholding Statements';
PROCEDURE insert_pr_w2_totals(errbuf OUT nocopy VARCHAR2,
retcode OUT nocopy NUMBER,
p_seq_num IN VARCHAR2,
p_report_type IN VARCHAR2) is
cursor c_er_total (c_seq_num in VARCHAR2,p_report_type in VARCHAR2) is
SELECT
attribute8 er_name,
attribute9 ein,
attribute11 er_addr_line_1,
attribute12 er_addr_line_2,
attribute13 er_addr_line_3,
attribute14 er_addr_city,
attribute15 er_addr_state,
attribute16 er_addr_zip,
attribute17 er_phone,
attribute10 year,
sum(value1) pension,
sum(value2) wages,
sum(value3) commissions,
sum(value4) allowances,
sum(value5) tips,
sum(value6) total,
sum(value7) reimb,
sum(value8) tax,
sum(value9) ret_fund,
sum(value10) coda,
sum(value11) ss_wages,
sum(value12) ss_tax,
sum(value13) med_wages,
sum(value14) med_tax,
sum(value15) ss_tips,
sum(value16) ss_tax_on_tips,
sum(value17) med_tax_on_tips,
sum(value18) hire_act_wages, --10435466
sum(value19) w2_charitable_contributions, --13462230
sum(value20) w2_save_and_double_money, --13462230
sum(value21) w2_health_coverage --13462230
FROM pay_us_rpt_totals
WHERE attribute2 = p_report_type
AND session_id = to_number(p_seq_num)
GROUP BY attribute8,attribute9, attribute11,attribute12,attribute13,attribute14,attribute15,attribute16,attribute17,attribute10;
SELECT
attribute8 er_name,
attribute9 ein,
attribute11 er_addr_line_1,
attribute12 er_addr_line_2,
attribute13 er_addr_line_3,
attribute14 er_addr_city,
attribute15 er_addr_state,
attribute16 er_addr_zip,
attribute17 er_phone,
attribute10 year,
attribute3 ee_name,
attribute4 ee_last_name,
attribute18 ee_addr_line_1,
attribute19 ee_addr_line_2,
attribute20 ee_addr_line_3,
attribute21 ee_addr_city,
attribute22 ee_addr_state,
attribute23 ee_addr_zip,
attribute24 ee_addr_country,
attribute25 ee_number,
attribute26 ee_wc,
attribute27 ee_loc,
attribute28 ee_hire_act,--10287981
attribute5 ssn, --Change 5855662
attribute6 status,
attribute7 spouse_ssn,
value1 pension,
value2 wages,
value3 commissions,
value4 allowances,
value5 tips,
value6 total,
value7 reimb,
value8 tax,
value9 ret_fund,
value10 coda,
value11 ss_wages,
value12 ss_tax,
value13 med_wages,
value14 med_tax,
value15 ss_tips,
value16 ss_tax_on_tips,
value17 med_tax_on_tips,
value18 hire_act_wages, --10435466
value19 w2_charitable_contributions, --13462230
value20 w2_save_and_double_money, --13462230
value21 w2_health_coverage --13462230
FROM pay_us_rpt_totals
WHERE attribute2 = p_report_type
AND session_id = to_number(p_seq_num);
/* Modified report to insert records with
report_type of 'PRW2REGISTER' for employee
detail and we can have the Total as a SUM
from the detail records. So now we are not
inserting seperate SUM and hence passing the
report type of 'PRW2REGISTER'
*/
i := 0;
select count(*)
into ln_count
FROM pay_us_rpt_totals
WHERE attribute2 = 'PRW2REGISTER'
AND session_id = to_number(p_seq_num);
DELETE from pay_us_rpt_totals
WHERE attribute2 = 'PRW2REGISTER'
AND session_id = to_number(p_seq_num);
select count(*)
into ln_count
FROM pay_us_rpt_totals
WHERE attribute2 = p_report_type
AND session_id = to_number(p_seq_num);
DELETE FROM pay_us_rpt_totals
WHERE attribute2 = p_report_type
AND session_id = to_number(p_seq_num);