[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
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
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,
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
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);