CREATE OR REPLACE PACKAGE BODY DA."BEC_PYE_PKG" AS /* This package is for several CMIC-GL Forms/scripts- The Name should have been changed to Bec_pye_pkg */ -- Bi-Weekly payroll change: p_text is not used. PROCEDURE bec_biwkpr_actchg ( p_text IN VARCHAR2, fromdate IN DATE, todate IN DATE ) AS -- If local db output file is needed, uncomment --output_path VARCHAR2 (50); --output_filename VARCHAR2 (20); output_line VARCHAR2 (450); output_file UTL_FILE.file_type; -- SMTP email variables conn UTL_SMTP.connection; req UTL_HTTP.req; resp UTL_HTTP.resp; DATA RAW (20000); cursor_count NUMBER := 0; -- All the data is gathered in the output cursor, all logic here. CURSOR output_cursor IS SELECT phy_comp_code, phy_work_comp_code, phy_data_type, '401k loans' AS scode, '401k loans BW - Debit' AS sdesc, '401k loans' AS rcode, '401k loans BW - Debit' AS rdesc, phy_emp_dept_code, phy_emp_cr_acc_code, SUM (phy_amount) AS phy_amount, phy_post_date FROM pyemppayhist WHERE phy_emp_cr_acc_code = '21506' AND phy_ppr_year >= to_char(fromdate,'yyyy') AND phy_date BETWEEN fromdate AND todate AND phy_prn_code = 'BW' AND phy_emp_dept_code != '00' GROUP BY phy_emp_dept_code, phy_comp_code, phy_work_comp_code, phy_data_type, phy_emp_cr_acc_code, phy_post_date UNION ALL SELECT '03', '03', 'G', '401k loans' AS scode, '401k loans BW - Credit' AS sdesc, '401k loans' AS rcode, '401k loans BW - Credit' AS rdesc, '00', '21506', SUM (phy_amount) * -1 AS phy_amount, SYSDATE FROM pyemppayhist WHERE phy_emp_cr_acc_code = '21506' AND phy_ppr_year >= to_char(fromdate,'yyyy') AND phy_date BETWEEN fromdate AND todate AND phy_prn_code = 'BW'; /* UNION ALL SELECT phy_comp_code, phy_work_comp_code, phy_data_type, 'Notes Received' AS scode, 'Notes Received BW -1' AS sdesc, 'Notes Received' AS rcode, 'Notes Received BW -1' AS rdesc, phy_emp_dept_code, phy_emp_cr_acc_code, SUM (phy_amount) * -1 AS phy_amount, phy_post_date FROM pyemppayhist WHERE phy_emp_cr_acc_code = '13148' AND phy_emp_dept_code != '00' AND phy_ppr_year >= to_char(fromdate,'yyyy') AND phy_date BETWEEN fromdate AND todate AND phy_prn_code = 'BW' GROUP BY phy_emp_dept_code, phy_comp_code, phy_work_comp_code, phy_data_type, phy_emp_cr_acc_code, phy_post_date UNION ALL SELECT '03', '03', 'G', 'Notes Received' AS scode, 'Notes Received BW' AS sdesc, 'AUTO' AS rcode, 'Notes Received BW' AS rdesc, '00', '13148', SUM (phy_amount), SYSDATE FROM pyemppayhist WHERE phy_ppr_year >= to_char(fromdate,'yyyy') AND phy_date BETWEEN fromdate AND todate AND phy_emp_dept_code != '00' AND phy_emp_cr_acc_code = '13148' AND phy_prn_code = 'BW'; */ -- create a instance of the cursor sal_rec output_cursor%ROWTYPE; BEGIN -- SMTP Email Header section conn := bec_mail.begin_mail (sender => 'CMiC ', recipients => USER || '<' || USER || '@beckgroup.com>,' || '"itstaff"', subject => sys_context('USERENV','DB_NAME') ||' - '|| 'Bi-Weekly Payroll Changes - Notes Received', mime_type => bec_mail.multipart_mime_type ); -- SMTP email text section bec_mail.attach_text (conn => conn, DATA => '
Automated journal entry to move payroll entries from various departments in account 21506 to department 00.
When payroll is processed, the 401K loan payments credit goes to the home department and this moves them to 00.

' || fromdate || ' to ' || todate || '
', mime_type => 'text/html' ); -- SMTP email attachment start section bec_mail.begin_attachment (conn => conn, mime_type => 'text/plain', inline => FALSE, filename => '401kloans' || TO_CHAR (SYSDATE, 'MMDDYY' ) || '.txt', transfer_enc => '' ); -- Output to a local file section, uncomment var section also. --output_filename := 'AG_'||TRUNC(SYSDATE)||'.txt'; ---TO_CHAR(SYSDATE,'HH24MI-MMDDYY')||'.txt'; --output_file := UTL_FILE.FOPEN ('/transfer', output_filename, 'w'); -- Loop thru sal_rec -- First time to gather count of cursor to remove last ctrl feed. FOR sal_rec IN output_cursor LOOP cursor_count := output_cursor%ROWCOUNT; END LOOP; -- Loop thru sal_rec -- Please note the UTL_TCP.CRLF is to place a hard return in email file FOR sal_rec IN output_cursor LOOP output_line := '"' || sal_rec.phy_comp_code || '","' || sal_rec.phy_work_comp_code || '","' || sal_rec.phy_data_type || '","' || sal_rec.scode || '","' || sal_rec.sdesc || '","' || sal_rec.scode || '","' || sal_rec.rdesc || '","' || '' || '","' || '' || '","' || '' || '","' || sal_rec.phy_emp_dept_code || '","' || sal_rec.phy_emp_cr_acc_code || '","' || TO_CHAR (todate, 'DD-MON-YYYY') || '","' || sal_rec.phy_amount || '","' || '' || '","' || '' || '","' || '' || '","' || '' || '","' || '' || '","' || '' || '","' || '' || '","' || '' || '","' || '' || '","' || '' || '","' || '' || '","' || '' || '","' || '' || '","' || '' || '","' || '' || '","' || '' || '","' || '' || '","' || TO_CHAR (todate, 'DD-MON-YYYY') || '","",""'; IF (output_cursor%ROWCOUNT = cursor_count) THEN output_line := output_line || ''; ELSE output_line := output_line || UTL_TCP.crlf; END IF; -- SMTP write one line to attachment bec_mail.write_text (conn => conn, MESSAGE => output_line); -- UTL_FILE.PUT_LINE (output_file, output_line); END LOOP; -- Close output file if needed --UTL_FILE.FCLOSE(output_file); -- Close attachement in email. -- Close email and send it. --bec_mail.end_attachment (conn => conn); --bec_mail.write_text (conn => conn, MESSAGE =>''); bec_mail.end_mail (conn => conn); -- End of bec_biwkpr_actchg proc END; -- Weekly payroll change: p_text is not used. PROCEDURE bec_wkpr_actchg ( p_text IN VARCHAR2, fromdate IN DATE, todate IN DATE ) AS -- If local db output file is needed, uncomment -- output_path VARCHAR2 (50); -- output_filename VARCHAR2 (20); output_line VARCHAR2 (450); output_file UTL_FILE.file_type; -- SMTP email variables conn UTL_SMTP.connection; req UTL_HTTP.req; resp UTL_HTTP.resp; DATA RAW (20000); cursor_count NUMBER := 0; -- All the data is gathered in the output cursor, all logic here. CURSOR output_cursor IS -- SELECT phy_comp_code, phy_comp_code AS compcode2, 'G' as phy_data_type , -- 'UNIO' AS scode, 'UNION FRINGE - Debit' AS sdesc, -- 'UNIO' AS rcode, 'UNION FRINGE - Debit' AS rdesc, -- '00' as phy_dept_code,'20614' as phy_gl_acc_code, -- /*phy_dept_code, phy_gl_acc_code,*/ -- SUM (phy_amount) AS phy_amount, -- phy_post_date -- FROM pyemppayhist -- WHERE phy_gl_acc_code = '60020' -- AND phy_ppr_year >= to_char(fromdate,'yyyy') -- AND phy_date BETWEEN fromdate AND todate -- AND phy_prn_code = 'WK' -- -- AND phy_dept_code!='WHSE' -- GROUP BY phy_dept_code, -- phy_comp_code, -- phy_data_type, -- phy_gl_acc_code, -- phy_post_date -- UNION ALL -- SELECT '03', '03', 'G', 'UNIO' AS scode, 'UNION FRINGE - Debit' AS sdesc, -- 'UNIO' AS rcode, 'UNION FRINGE - Debit' AS rdesc, '00', '20614', -- SUM (phy_amount) as phy_amount, SYSDATE -- FROM pyemppayhist -- WHERE phy_gl_acc_code = '60020' -- --AND phy_dept_code!='WHSE' -- AND phy_ppr_year >= to_char(fromdate,'yyyy') -- AND phy_date BETWEEN fromdate AND todate -- AND phy_prn_code = 'WK' -- UNION ALL SELECT phy_comp_code, phy_comp_code compcode2, 'G' as phy_data_type, 'UNIO' AS scode, 'UNION FRINGE - Debit' AS sdesc, 'UNIO' AS rcode, 'UNION FRINGE - Debit' AS rdesc, -- (CASE phy_emp_dept_code when 'WHSE' THEN '00' ELSE phy_emp_dept_code end), phy_emp_cr_acc_code, phy_emp_dept_code phy_dept_code, phy_emp_cr_acc_code phy_gl_acc_code, SUM (phy_amount) AS phy_amount, phy_post_date FROM pyemppayhist WHERE phy_emp_cr_acc_code = '20614' -- AND phy_emp_dept_code!='WHSE' AND phy_emp_dept_code != '00' AND phy_ppr_year >= to_char(fromdate,'yyyy') AND phy_date BETWEEN fromdate AND todate AND phy_prn_code = 'WK' GROUP BY phy_emp_dept_code, phy_comp_code, phy_emp_cr_acc_code, phy_post_date UNION ALL SELECT '03', '03', 'G', 'UNIO' , 'UNION FRINGE - Credit' , 'UNIO' , 'UNION FRINGE - Credit' , '00', '20614', SUM (phy_amount) * -1 , SYSDATE FROM pyemppayhist WHERE phy_ppr_year >= to_char(fromdate,'yyyy') AND phy_date BETWEEN fromdate AND todate -- AND phy_emp_dept_code!='WHSE' AND phy_emp_dept_code != '00' AND phy_emp_cr_acc_code = '20614' AND phy_prn_code = 'WK'; -- create a instance of the cursor sal_rec output_cursor%ROWTYPE; BEGIN -- SMTP Email Header section conn := bec_mail.begin_mail (sender => 'CMiC ', recipients => USER || '<' || USER || '@beckgroup.com>,' || '"itstaff"', subject => sys_context('USERENV','DB_NAME') ||' - '|| 'Weekly Payroll Changes - Union Fringe', mime_type => bec_mail.multipart_mime_type ); -- SMTP email text section bec_mail.attach_text (conn => conn, DATA => '
The attached file includes weekly payroll changes to be imported via CMiC batch import.
This import moves payroll entries from various departments and account 60020 to department 00 and account 20614.
When a weekly union employee charging to overhead departments the fringe debit goes to 60020 and it goes to department 00 and 20614.
Also journal entries move payroll entries from departments other than 00 and acct 20614 to dept 00 and account 20614.
When union fringes are calculated the credit is going to the home department of each person.

' || fromdate || ' to ' || todate || '
', mime_type => 'text/html' ); -- SMTP email attachement start section bec_mail.begin_attachment (conn => conn, mime_type => 'text/plain', inline => FALSE, filename => 'wkunion' || TO_CHAR (SYSDATE, 'MMDDYY' ) || '.txt', transfer_enc => '' ); -- Output to a local file section, uncomment var section also. -- output_filename := 'AG_'||TRUNC(SYSDATE)||'.txt'; ---TO_CHAR(SYSDATE,'HH24MI-MMDDYY')||'.txt'; -- output_file := UTL_FILE.FOPEN ('/transfer', output_filename, 'w'); -- Loop thru sal_rec -- First time to gather count of cursor to remove last ctrl feed. FOR sal_rec IN output_cursor LOOP cursor_count := output_cursor%ROWCOUNT; END LOOP; -- Loop thru sal_rec -- Please note the UTL_TCP.CRLF is to place a hard return in email file. FOR sal_rec IN output_cursor LOOP output_line := '"' || sal_rec.phy_comp_code || '","' || sal_rec.compcode2 || '","' || sal_rec.phy_data_type || '","' || sal_rec.scode || '","' || sal_rec.sdesc || '","' || sal_rec.rcode || '","' || sal_rec.rdesc || '","' || '' || '","' || '' || '","' || '' || '","' || sal_rec.phy_dept_code || '","' || sal_rec.phy_gl_acc_code || '","' || TO_CHAR (todate, 'DD-MON-YYYY') || '","' || sal_rec.phy_amount || '","' || '' || '","' || '' || '","' || '' || '","' || '' || '","' || '' || '","' || '' || '","' || '' || '","' || '' || '","' || '' || '","' || '' || '","' || '' || '","' || '' || '","' || '' || '","' || '' || '","' || '' || '","' || '' || '","' || '' || '","' || TO_CHAR (todate, 'DD-MON-YYYY') || '","",""'; IF (output_cursor%ROWCOUNT = cursor_count) THEN output_line := output_line || ''; ELSE output_line := output_line || UTL_TCP.crlf; END IF; -- SMTP write one line to attachment bec_mail.write_text (conn => conn, MESSAGE => output_line); -- UTL_FILE.PUT_LINE (output_file, output_line); END LOOP; -- Close output file if needed --UTL_FILE.FCLOSE(output_file); -- Close attachement in email. -- Close email and send it. --bec_mail.end_attachment (conn => conn); bec_mail.end_mail (conn => conn); -- End of bec_biwkpr_actchg proc END; PROCEDURE bec_mondept_actchg ( p_text IN VARCHAR2, todate IN DATE, YEAR IN NUMBER, fromperiod IN NUMBER, toperiod IN NUMBER ) AS -- If local db output file is needed, uncomment -- output_path VARCHAR2 (50); -- output_filename VARCHAR2 (20); output_line VARCHAR2 (450); output_file UTL_FILE.file_type; -- SMTP email variables conn UTL_SMTP.connection; req UTL_HTTP.req; resp UTL_HTTP.resp; DATA RAW (20000); cursor_count NUMBER := 0; add_back number := 0; -- All the data is gathered in the output cursor, all logic here. CURSOR output_cursor IS select * from (SELECT phy_emp_no, phy_work_comp_code phy_comp_code, phy_work_comp_code, phy_data_type, 'DEPTCHG' AS scode, 'Burden Correction - PW '||fromperiod||'-'||toperiod AS sdesc, -- 'Burden Home Dept Adj-PW '||fromperiod||'-'||toperiod AS sdesc, 'DEPTCHG' AS rcode, substr('Burden Correction - '||emp_last_name,1,60) AS rdesc, -- substr('Home Dept Burden Corr PW 1-22 - '||emp_last_name,1,60) AS rdesc, case when phy_gl_acc_code='60129' then 'YEND' else phy_dept_code end phy_dept_code, -- phy_dept_code, phy_gl_acc_code, round(sum(phy_amount * cc.percent * -1),2) phy_amount, sysdate AS phy_post_date FROM pyemppayhist, pyemployee_table, (select bb.tsh_work_comp_code, aa.tsh_emp_no, bb.tsh_dept_code, case when aa.total_hours = 0 then 0 else nvl(bb.non_home_hours / aa.total_hours,0) end percent from (select tsh_emp_no, sum(nvl(tsh_normal_hours,0)+nvl(tsh_ot_hours * 1.5,0)+nvl(tsh_dot_hours * 2,0)+nvl(tsh_other_hours,0)) total_hours from pyemptimsht where tsh_ppr_year = year and tsh_ppr_period between fromperiod and toperiod and tsh_process_flag = 'P' and tsh_data_type = 'G' group by tsh_emp_no) aa, (select tsh_work_comp_code, tsh_emp_no, tsh_dept_code, sum(case when tsh_home_dept_code != tsh_dept_code then nvl(tsh_normal_hours,0)+nvl(tsh_ot_hours * 1.5,0)+nvl(tsh_dot_hours * 2,0)+nvl(tsh_other_hours,0) else 0 end) non_home_hours from pyemptimsht where tsh_ppr_year = year and tsh_ppr_period between fromperiod and toperiod and tsh_process_flag = 'P' and tsh_data_type = 'G' group by tsh_work_comp_code, tsh_emp_no, tsh_dept_code) bb where aa.tsh_emp_no = bb.tsh_emp_no (+) and case when aa.total_hours = 0 then 0 else nvl(bb.non_home_hours / aa.total_hours,0) end <> 0) cc WHERE ((phy_tran_type = 'BN' AND phy_emplr_contr_flag = 'Y') OR (phy_tran_type = 'DE' AND phy_emplr_contr_flag = 'Y') OR (phy_tran_type = 'TX' AND phy_emplr_contr_flag = 'Y') OR (phy_tran_type = 'WC' AND phy_emplr_contr_flag = 'Y')) AND phy_prn_code = 'BW' AND phy_ppr_year = year AND phy_ppr_period BETWEEN fromperiod AND toperiod and phy_emp_no = cc.tsh_emp_no (+) and phy_job is null and phy_emp_no = emp_no and phy_dept_code='DESTI' and not (PHY_WORK_COMP_CODE = '76' and phy_gl_acc_code in ( '60157', '60129') ) GROUP BY phy_emp_no, phy_work_comp_code, phy_work_comp_code, phy_data_type, emp_last_name, phy_dept_code, phy_gl_acc_code HAVING round(sum(phy_amount * -1 * cc.percent),2) <> 0 UNION ALL SELECT phy_emp_no, cc.tsh_work_comp_code phy_comp_code, phy_work_comp_code, phy_data_type, 'DEPTCHG' AS scode, 'Burden Correction - PW '||fromperiod||'-'||toperiod AS sdesc, 'DEPTCHG' AS rcode, substr('Burden Correction - '||emp_last_name,1,60) AS rdesc, case when phy_gl_acc_code='60129' then 'YEND' else cc.tsh_dept_code end phy_dept_code, -- cc.tsh_dept_code phy_dept_code, phy_gl_acc_code, round(sum(phy_amount * cc.percent),2) phy_amount, sysdate AS phy_post_date FROM pyemppayhist, pyemployee_table, (select bb.tsh_work_comp_code, aa.tsh_emp_no, bb.tsh_dept_code, case when aa.total_hours = 0 then 0 else nvl(bb.non_home_hours / aa.total_hours,0) end percent from (select tsh_emp_no, sum(nvl(tsh_normal_hours,0)+nvl(tsh_ot_hours * 1.5,0)+nvl(tsh_dot_hours * 2,0)+nvl(tsh_other_hours,0)) total_hours from pyemptimsht where tsh_ppr_year = year and tsh_ppr_period between fromperiod and toperiod and tsh_process_flag = 'P' and tsh_data_type = 'G' and tsh_prn_code = 'BW' group by tsh_emp_no) aa, (select tsh_work_comp_code, tsh_emp_no, tsh_dept_code, sum(case when tsh_home_dept_code != tsh_dept_code then nvl(tsh_normal_hours,0)+nvl(tsh_ot_hours * 1.5,0)+nvl(tsh_dot_hours * 2,0)+nvl(tsh_other_hours,0) else 0 end) non_home_hours from pyemptimsht where tsh_ppr_year = year and tsh_ppr_period between fromperiod and toperiod and tsh_process_flag = 'P' and tsh_data_type = 'G' and tsh_prn_code = 'BW' group by tsh_work_comp_code, tsh_emp_no, tsh_dept_code) bb where aa.tsh_emp_no = bb.tsh_emp_no (+) and case when aa.total_hours = 0 then 0 else nvl(bb.non_home_hours / aa.total_hours,0) end <> 0) cc WHERE ((phy_tran_type = 'BN' AND phy_emplr_contr_flag = 'Y') OR (phy_tran_type = 'DE' AND phy_emplr_contr_flag = 'Y') OR (phy_tran_type = 'TX' AND phy_emplr_contr_flag = 'Y') OR (phy_tran_type = 'WC' AND phy_emplr_contr_flag = 'Y')) AND phy_prn_code = 'BW' AND phy_ppr_year = year AND phy_ppr_period BETWEEN fromperiod AND toperiod and phy_emp_no = cc.tsh_emp_no (+) and phy_job is null and phy_emp_no = emp_no and phy_dept_code='DESTI' and not (PHY_WORK_COMP_CODE = '76' and phy_gl_acc_code in ( '60157', '60129') ) GROUP BY phy_emp_no, cc.tsh_work_comp_code, phy_work_comp_code, phy_data_type, emp_last_name, cc.tsh_dept_code, phy_gl_acc_code HAVING round(sum(phy_amount * -1 * cc.percent),2) <> 0); -- where (phy_emp_no not in (select emp_no from da.pyemployee_table where emp_home_dept_code = 'DESTI') and phy_emp_no != '117225') ; -- create a instance of the cursor output_rec output_cursor%ROWTYPE; BEGIN insert into bec_debug ( object_type, object_name, event_date, numeric_value_1, error_desc) values ( 'PKG', 'AG_mondept', sysdate, null, substr(p_text,1,60) ); commit; -- SMTP Email Header section conn := bec_mail.begin_mail (sender => 'CMiC Journal ', recipients => USER || '<' || USER || '@beckgroup.com>,' || '"itstaff"', subject => sys_context('USERENV','DB_NAME') ||' - '|| 'Monthly Department Payroll Changes',--jkb added DB name on 20180410 mime_type => bec_mail.multipart_mime_type ); -- SMTP email text section bec_mail.attach_text (conn => conn, DATA => '
The attached file includes monthly payroll changes to be imported via CMiC batch import.

Period: ' || fromperiod || '/' || YEAR || ' to ' || toperiod || '/' || YEAR || '
', mime_type => 'text/html' ); -- SMTP email attachement start section bec_mail.begin_attachment (conn => conn, mime_type => 'text/plain', inline => FALSE, filename => 'mondept' || TO_CHAR (SYSDATE, 'MMDDYY' ) || '.txt', transfer_enc => '' ); -- Output to a local file section, uncomment var section also. -- output_filename := 'AG_'||TRUNC(SYSDATE)||'.txt'; ---TO_CHAR(SYSDATE,'HH24MI-MMDDYY')||'.txt'; -- output_file := UTL_FILE.FOPEN ('/transfer', output_filename, 'w'); -- Loop thru output_rec -- First time to gather count of cursor to remove last ctrl feed. FOR output_rec IN output_cursor LOOP cursor_count := output_cursor%ROWCOUNT; END LOOP; -- Loop thru output_rec -- Please note the UTL_TCP.CRLF is to place a hard return in email file. FOR output_rec IN output_cursor LOOP add_back := add_back + output_rec.phy_amount; IF (output_cursor%ROWCOUNT = cursor_count) then output_rec.phy_amount := output_rec.phy_amount - add_back; end if; output_line := '"' || output_rec.phy_comp_code || '","' -- || output_rec.phy_work_comp_code ||'03' || '","' || output_rec.phy_data_type || '","' || output_rec.scode || '","' || output_rec.sdesc || '","' || output_rec.rcode || '","' || output_rec.rdesc || '","' || '' || '","' || '' || '","' || '' || '","' || output_rec.phy_dept_code || '","' || output_rec.phy_gl_acc_code || '","' || TO_CHAR (todate, 'DD-MON-YYYY') || '","' || output_rec.phy_amount || '","' || '' || '","' || '' || '","' || '' || '","' || '' || '","' || '' || '","' || '' || '","' || '' || '","' || '' || '","' || '' || '","' || '' || '","' || '' || '","' || '' || '","' || '' || '","' || '' || '","' || '' || '","' || '' || '","' || '' || '","' || TO_CHAR (todate, 'DD-MON-YYYY') || '","",""'; IF (output_cursor%ROWCOUNT = cursor_count) THEN output_line := output_line || ''; ELSE output_line := output_line || UTL_TCP.crlf; END IF; -- SMTP write one line to attachment bec_mail.write_text (conn => conn, MESSAGE => output_line); -- UTL_FILE.PUT_LINE (output_file, output_line); END LOOP; -- Close output file if needed --UTL_FILE.FCLOSE(output_file); -- Close attachement in email. -- Close email and send it. --bec_mail.end_attachment (conn => conn); bec_mail.end_mail (conn => conn); -- End of bec_biwkpr_actchg proc END; PROCEDURE bec_truckallowance_actchg ( p_text IN VARCHAR2, fromdate IN DATE, todate IN DATE ) AS -- If local db output file is needed, uncomment -- output_path VARCHAR2 (50); -- output_filename VARCHAR2 (20); output_line VARCHAR2 (450); output_file UTL_FILE.file_type; -- SMTP email variables conn UTL_SMTP.connection; req UTL_HTTP.req; resp UTL_HTTP.resp; DATA RAW (20000); cursor_count NUMBER := 0; -- All the data is gathered in the output cursor, all logic here. CURSOR output_cursor IS SELECT phy_comp_code, phy_comp_code AS compcode2, phy_data_type, 'TRCK' AS scode, 'Truck Allow - Credit' AS sdesc, 'TRCK' AS rcode, 'Truck Allow - Credit' AS rdesc, phy_dept_code, phy_gl_acc_code, SUM (phy_amount) * -1 AS phy_amount, phy_post_date FROM pyemppayhist WHERE phy_gl_acc_code = '20626' AND phy_ppr_year >= to_char(fromdate,'yyyy') AND phy_date BETWEEN fromdate AND todate GROUP BY phy_dept_code, phy_comp_code, phy_data_type, phy_gl_acc_code, phy_post_date UNION ALL SELECT '03', '03', 'G', 'TRCK' AS scode, 'Truck Allow - Debit' AS sdesc, 'TRCK' AS rcode, 'Truck Allow - Debit' AS rdesc, '00', '20626', SUM (phy_amount), SYSDATE FROM pyemppayhist WHERE phy_gl_acc_code = '20626' AND phy_ppr_year >= to_char(fromdate,'yyyy') AND phy_date BETWEEN fromdate AND todate; -- create a instance of the cursor sal_rec output_cursor%ROWTYPE; BEGIN -- SMTP Email Header section conn := bec_mail.begin_mail (sender => 'CMiC ', recipients => USER || '<' || USER || '@beckgroup.com>,' || '"itstaff"', subject => sys_context('USERENV','DB_NAME') ||' - '|| 'Truck Allowance Benefit Payroll Changes', mime_type => bec_mail.multipart_mime_type ); -- SMTP email text section bec_mail.attach_text (conn => conn, DATA => '
The attached file includes Truck Allowance Benefit payroll changes to be imported via CMiC batch import. Changes move cost from department 00 on account 20626 to the various departments on account 20626.

Period: ' || fromdate || ' to ' || todate || '
', mime_type => 'text/html' ); -- SMTP email attachement start section bec_mail.begin_attachment (conn => conn, mime_type => 'text/plain', inline => FALSE, filename => 'truckallow' || TO_CHAR (SYSDATE, 'MMDDYY' ) || '.txt', transfer_enc => '' ); -- Output to a local file section, uncomment var section also. -- output_filename := 'AG_'||TRUNC(SYSDATE)||'.txt'; ---TO_CHAR(SYSDATE,'HH24MI-MMDDYY')||'.txt'; -- output_file := UTL_FILE.FOPEN ('/transfer', output_filename, 'w'); -- Loop thru sal_rec -- First time to gather count of cursor to remove last ctrl feed. FOR sal_rec IN output_cursor LOOP cursor_count := output_cursor%ROWCOUNT; END LOOP; -- Loop thru sal_rec -- Please note the UTL_TCP.CRLF is to place a hard return in email file. FOR sal_rec IN output_cursor LOOP output_line := '"' || sal_rec.phy_comp_code || '","' || sal_rec.phy_comp_code || '","' || sal_rec.phy_data_type || '","' || sal_rec.scode || '","' || sal_rec.sdesc || '","' || sal_rec.rcode || '","' || sal_rec.rdesc || '","' || '' || '","' || '' || '","' || '' || '","' || sal_rec.phy_dept_code || '","' || sal_rec.phy_gl_acc_code || '","' || TO_CHAR (todate, 'DD-MON-YYYY') || '","' || sal_rec.phy_amount || '","' || '' || '","' || '' || '","' || '' || '","' || '' || '","' || '' || '","' || '' || '","' || '' || '","' || '' || '","' || '' || '","' || '' || '","' || '' || '","' || '' || '","' || '' || '","' || '' || '","' || '' || '","' || '' || '","' || '' || '","' || TO_CHAR (todate, 'DD-MON-YYYY') || '","",""'; IF (output_cursor%ROWCOUNT = cursor_count) THEN output_line := output_line || ''; ELSE output_line := output_line || UTL_TCP.crlf; END IF; -- SMTP write one line to attachment bec_mail.write_text (conn => conn, MESSAGE => output_line); -- UTL_FILE.PUT_LINE (output_file, output_line); END LOOP; -- Close output file if needed --UTL_FILE.FCLOSE(output_file); -- Close attachement in email. -- Close email and send it. --bec_mail.end_attachment (conn => conn); bec_mail.end_mail (conn => conn); -- End of bec_biwkpr_actchg proc END; FUNCTION bec_salary_amt ( in_comp_code IN da.pyemppayhist.phy_comp_code%TYPE, in_emp_no IN da.pyemppayhist.phy_emp_no%TYPE, in_year IN da.pyemppayhist.phy_ppr_year%TYPE, in_period IN da.pyemppayhist.phy_ppr_period%TYPE, in_prn_code IN da.pyemppayhist.phy_prn_code%TYPE) RETURN da.pyemppayhist.phy_pay_amount%TYPE IS v_val da.pyemppayhist.phy_pay_amount%TYPE; dummy number; hold_effective_date date; hold_end_date date; BEGIN BEGIN select ppr_end_date into hold_end_date from pycompayprd where ppr_comp_code = in_comp_code and ppr_prn_code = in_prn_code and ppr_year = in_year and ppr_period = in_period; EXCEPTION when NO_DATA_FOUND then v_val := 999999999.90; goto bypass; END; BEGIN select distinct max(emh_effective_date) into hold_effective_date from pyemphist where emh_comp_code = in_comp_code and emh_emp_no = in_emp_no and emh_effective_date <= hold_end_date; EXCEPTION when NO_DATA_FOUND then v_val := 999999999.91; goto bypass; END; BEGIN select max(case when emh_emp_type = 'S' then emh_annual_salary else emh_hour_rate * 2080 end) into v_val from pyemphist where emh_comp_code = in_comp_code and emh_emp_no = in_emp_no and emh_effective_date = hold_effective_date; EXCEPTION when NO_DATA_FOUND then v_val := 999999999.92; goto bypass; END; <> return(v_val); END bec_salary_amt; PROCEDURE bec_manhour_email( in_year IN NUMBER) AS -- Procedure generates asset import file and emails it to current user. -- If local db output file is needed, uncomment output_path VARCHAR2 (50); output_filename VARCHAR2 (20); output_line VARCHAR2 (750); -- output_file UTL_FILE.file_type; -- SMTP email variables conn UTL_SMTP.connection; req UTL_HTTP.req; resp UTL_HTTP.resp; DATA RAW (20000); cursor_count number:=0; tab varchar2(2):=CHR(9); CURSOR output_cursor IS select a.tsh_prn_code, c.region_cd, c.office_loc, sum(nvl(tsh_normal_hours,0) + nvl(tsh_ot_hours,0) + nvl(tsh_dot_hours,0) + nvl(tsh_other_hours,0)) as Hours from pyemptimsht a, pyemployee_table b, uetd_classifier c where a.tsh_ppr_year = in_year and a.tsh_process_flag = 'P' and a.tsh_emp_no = b.emp_no and b.emp_home_dept_code = c.department and b.emp_comp_code = c.comp_code group by a.tsh_prn_code, c.region_cd , c.office_loc; -- create a instance of the cursor sal_rec output_cursor%ROWTYPE; BEGIN -- SMTP Email Header section conn := bec_mail.begin_mail (sender => 'CMiC ', recipients => user ||'<'||user||'@beckgroup.com>,'|| '"itstaff"', subject => sys_context('USERENV','DB_NAME') ||' - '|| 'Man Hours Report', mime_type => bec_mail.multipart_mime_type ); -- SMTP email text section bec_mail.attach_text (conn => conn, DATA => '
The attached file includes man hours report.
', mime_type => 'text/html' ); -- SMTP email attachement start section bec_mail.begin_attachment (conn => conn, mime_type => 'application/vnd.ms-excel', inline => FALSE, filename => 'ManHours'||to_char(sysdate, 'YY') ||'.xls', transfer_enc => '' ); -- Output to a local file section, uncomment var section also. -- output_filename := 'AG_'||TRUNC(SYSDATE)||'.txt'; ---TO_CHAR(SYSDATE,'HH24MI-MMDDYY')||'.txt'; -- output_file := UTL_FILE.FOPEN ('/transfer', output_filename, 'w'); --output_line :=('*** ' || 'ManHours'||to_char(sysdate, 'YY') ||'.xls ***'|| UTL_TCP.CRLF --||'MIME-Version: 1.0' || UTL_TCP.CRLF --||'X-Document-Type: Workbook' || UTL_TCP.CRLF --||'Content-Type: multipart/related; boundary=3D"----=_NextPart_ExcelWorkbook"'|| UTL_TCP.CRLF|| UTL_TCP.CRLF --|| '------=_NextPart_ExcelWorkbook'|| UTL_TCP.CRLF --|| 'Content-Location: books.xls'|| UTL_TCP.CRLF --|| 'Content-Transfer-Encoding: quoted-printable' || UTL_TCP.CRLF --|| 'Content-Type: text/html; charset=3D"us-ascii"'|| UTL_TCP.CRLF || UTL_TCP.CRLF --); --bec_mail.write_text (conn => conn, MESSAGE => output_line ); output_line :=(''|| UTL_TCP.CRLF ||''|| UTL_TCP.CRLF --||''|| UTL_TCP.CRLF ||''|| UTL_TCP.CRLF ||''|| UTL_TCP.CRLF ||''|| UTL_TCP.CRLF ||''|| UTL_TCP.CRLF ||'Man Hours'|| UTL_TCP.CRLF -- ||''|| UTL_TCP.CRLF ||''|| UTL_TCP.CRLF || ''|| UTL_TCP.CRLF || ''|| UTL_TCP.CRLF || ''|| UTL_TCP.CRLF ||''|| UTL_TCP.CRLF ||''|| UTL_TCP.CRLF --||''|| UTL_TCP.CRLF || UTL_TCP.CRLF ); bec_mail.write_text (conn => conn, MESSAGE => output_line ); --output_line :=('------=_NextPart_ExcelWorkbook' || UTL_TCP.CRLF --||'Content-Location: sheet1.htm'|| UTL_TCP.CRLF --||'Content-Transfer-Encoding: quoted-printable'|| UTL_TCP.CRLF --||'Content-Type: text/html; charset=3D"us-ascii"'|| UTL_TCP.CRLF || UTL_TCP.CRLF --); --bec_mail.write_text (conn => conn, MESSAGE => output_line ); output_line :=('' || UTL_TCP.CRLF ); bec_mail.write_text (conn => conn, MESSAGE => output_line ); output_line :=( ''|| UTL_TCP.CRLF ); bec_mail.write_text (conn => conn, MESSAGE => output_line ); -- Loop thru sal_rec -- First time to gather count of cursor to remove last ctrl feed. FOR sal_rec IN output_cursor LOOP cursor_count:= output_cursor%ROWCOUNT; END LOOP; -- Loop thru sal_rec -- Please note the UTL_TCP.CRLF is to place a hard return in email file. FOR sal_rec IN output_cursor LOOP output_line := '' ||'' ||'' ||''; if (output_cursor%ROWCOUNT=cursor_count) THEN output_line:= output_line || ''; ELSE output_line:= output_line || UTL_TCP.CRLF; end if; -- SMTP write one line to attachment bec_mail.write_text (conn => conn, MESSAGE => output_line ); -- UTL_FILE.PUT_LINE (output_file, output_line); END LOOP; output_line :=('
TSH_PRNREGION_CDOFFICE_LOCHOURS
'||sal_rec.tsh_prn_code||''|| sal_rec.region_cd||''|| sal_rec.office_loc||''|| sal_rec.Hours||'
'||UTL_TCP.CRLF||''|| UTL_TCP.CRLF); bec_mail.write_text (conn => conn, MESSAGE => output_line ); --output_line :=('------=_NextPart_ExcelWorkbook' || UTL_TCP.CRLF --||'Content-Location: sheet2.htm'|| UTL_TCP.CRLF --||'Content-Transfer-Encoding: quoted-printable'|| UTL_TCP.CRLF --||'Content-Type: text/html; charset=3D"us-ascii"'|| UTL_TCP.CRLF || UTL_TCP.CRLF --); bec_mail.write_text (conn => conn, MESSAGE => output_line ); -- Close output file if needed -- UTL_FILE.FCLOSE(output_file); -- Close attachement in email. -- Close email and send it. --bec_mail.end_attachment (conn => conn); bec_mail.end_mail (conn => conn); -- End of bec_manhour_email proc END; PROCEDURE bec_emptimecard_email( in_year IN NUMBER, in_period IN NUMBER ) AS -- output_file UTL_FILE.file_type; -- SMTP email variables conn UTL_SMTP.connection; req UTL_HTTP.req; resp UTL_HTTP.resp; DATA RAW (20000); cursor_count number:=0; tab varchar2(2):=CHR(9); CURSOR emp_cursor IS SELECT a.emp_no, a.emp_first_name||' '||a.emp_last_name AS NAME, a.emp_home_dept_code, nvl(cc.emp_no,118911) time_mgr, nvl(l.emp_email_address,'henryclark@beckgroup.com') mgr_email, CASE WHEN a.emp_email_address LIKE '%@%' THEN a.emp_email_address ELSE a.emp_email_address||'@beckgroup.com' END AS emp_email_address, NVL(aa.total_hours,0) AS total_hours, NVL(bb.total_hours,0) AS total_hours_not_submitted FROM PYEMPLOYEE_TABLE a, (SELECT tsh_emp_no, SUM(NVL(tsh_day_01,0) + NVL(tsh_day_02,0) + NVL(tsh_day_03,0) + NVL(tsh_day_04,0) + NVL(tsh_day_05,0) + NVL(tsh_day_06,0) + NVL(tsh_day_07,0) + NVL(tsh_day_08,0) + NVL(tsh_day_09,0) + NVL(tsh_day_10,0) + NVL(tsh_day_11,0) + NVL(tsh_day_12,0) + NVL(tsh_day_13,0) + NVL(tsh_day_14,0)) AS total_hours FROM PYEMPTIMSHT_CERT WHERE tsh_ppr_period = in_period AND tsh_ppr_year = in_year AND tsh_prn_code IN ('BW') GROUP BY tsh_emp_no) aa, (SELECT tsh_emp_no, SUM(NVL(tsh_day_01,0) + NVL(tsh_day_02,0) + NVL(tsh_day_03,0) + NVL(tsh_day_04,0) + NVL(tsh_day_05,0) + NVL(tsh_day_06,0) + NVL(tsh_day_07,0) + NVL(tsh_day_08,0) + NVL(tsh_day_09,0) + NVL(tsh_day_10,0) + NVL(tsh_day_11,0) + NVL(tsh_day_12,0) + NVL(tsh_day_13,0) + NVL(tsh_day_14,0)) AS total_hours FROM PYEMPTIMSHT_CERT WHERE tsh_ppr_period = in_period AND tsh_ppr_year = in_year AND tsh_prn_code IN ('BW') AND NVL(tsh_submit_flag,'N') not in ('Y','R') GROUP BY tsh_emp_no) bb, uetd_classifier cc, pyemployee_table_tn l WHERE l.emp_no=cc.emp_no and a.emp_home_dept_code = cc.department and cc.comp_code = '03' and a.emp_no = aa.tsh_emp_no (+) AND a.emp_no = bb.tsh_emp_no (+) AND a.emp_pyg_code IN ('BW03','PT03') AND a.emp_status = 'A' AND (NVL(aa.total_hours,0) < 80 OR NVL(bb.total_hours,0) <> 0) and a.emp_no not in ('110084') and a.emp_home_dept_code <> 'DESTI' and cc.emp_no not in ('110054') --jkb added 20190627 and a.emp_dept_code <> 'FURL' --jkb added 20200416 /* -- beck tech DESTI department removed from warnings per L Dicus 1/7/2021 union all SELECT a.emp_no, a.emp_first_name||' '||a.emp_last_name AS NAME, a.emp_home_dept_code, nvl(cc.team_ldr_mgr,118911) time_mgr, nvl(l.emp_email_address,'henryclark@beckgroup.com') ldr_email, CASE WHEN a.emp_email_address LIKE '%@%' THEN a.emp_email_address ELSE a.emp_email_address||'@beckgroup.com' END AS emp_email_address, NVL(aa.total_hours,0) AS total_hours, NVL(bb.total_hours,0) AS total_hours_not_submitted FROM PYEMPLOYEE_TABLE a, (SELECT tsh_emp_no, SUM(NVL(tsh_day_01,0) + NVL(tsh_day_02,0) + NVL(tsh_day_03,0) + NVL(tsh_day_04,0) + NVL(tsh_day_05,0) + NVL(tsh_day_06,0) + NVL(tsh_day_07,0) + NVL(tsh_day_08,0) + NVL(tsh_day_09,0) + NVL(tsh_day_10,0) + NVL(tsh_day_11,0) + NVL(tsh_day_12,0) + NVL(tsh_day_13,0) + NVL(tsh_day_14,0)) AS total_hours FROM PYEMPTIMSHT_CERT WHERE tsh_ppr_period = in_period AND tsh_ppr_year = in_year AND tsh_prn_code IN ('BW') GROUP BY tsh_emp_no) aa, (SELECT tsh_emp_no, SUM(NVL(tsh_day_01,0) + NVL(tsh_day_02,0) + NVL(tsh_day_03,0) + NVL(tsh_day_04,0) + NVL(tsh_day_05,0) + NVL(tsh_day_06,0) + NVL(tsh_day_07,0) + NVL(tsh_day_08,0) + NVL(tsh_day_09,0) + NVL(tsh_day_10,0) + NVL(tsh_day_11,0) + NVL(tsh_day_12,0) + NVL(tsh_day_13,0) + NVL(tsh_day_14,0)) AS total_hours FROM PYEMPTIMSHT_CERT WHERE tsh_ppr_period = in_period AND tsh_ppr_year = in_year AND tsh_prn_code IN ('BW') AND NVL(tsh_submit_flag,'N') not in ('Y','R') GROUP BY tsh_emp_no) bb, bec_employee_info cc, pyemployee_table_tn l WHERE l.emp_no = cc.team_ldr_mgr and a.emp_no = cc.EMP_NO (+) and a.emp_no = aa.tsh_emp_no (+) AND a.emp_no = bb.tsh_emp_no (+) AND a.emp_pyg_code IN ('BW03','PT03') AND a.emp_status = 'A' AND (NVL(aa.total_hours,0) < 80 OR NVL(bb.total_hours,0) <> 0) and a.emp_no not in ('110084') and a.emp_home_dept_code = 'DESTI' and cc.emp_no not in ('110054') --jkb added 20190627 and a.emp_dept_code <> 'FURL' --jkb added 20200416 */ ORDER BY emp_email_address; -- create a instance of the cursor emp_rec PYEMPLOYEE_TABLE%ROWTYPE; l_maicon utl_smtp.connection; email_address VARCHAR2 (100); emp_list VARCHAR2 (8000); emp_list2 VARCHAR2 (8000); emp_list3 VARCHAR2 (8000); hold_emp_no VARCHAR2 (16); hold_email_address VARCHAR2 (100); output_path VARCHAR2 (50); output_filename VARCHAR2 (20); output_header VARCHAR2 (312); output_line VARCHAR2 (255); output_file UTL_FILE.FILE_TYPE; hold_error VARCHAR2 (100); hold_subject varchar2 (500); hold_cutoff_date varchar2 (500); BEGIN -- output_filename := 'E-TIME.txt'; -- output_file := UTL_FILE.FOPEN ('/transfer', output_filename, 'w'); select payalerth, payalertd into hold_subject, hold_cutoff_date from uetd_paystubmsg where comp_code = '03'; output_header := 'Emp ID Name Hours Entered Hours Not Submitted'; FOR emp_rec IN emp_cursor LOOP email_address := emp_rec.emp_email_address; IF SUBSTR(email_address,1,1) = '@' THEN hold_error := 'E-Mail Address Missing: '||emp_rec.NAME; DBMS_OUTPUT.PUT_LINE(hold_error); GOTO bypass_employee; END IF; if emp_rec.emp_no=117275 then goto bypass_employee; end if; l_maicon := utl_smtp.open_connection('localhost'); utl_smtp.helo(l_maicon,'hostname'); utl_smtp.mail(l_maicon,'oracle@beckgroup.com'); utl_smtp.rcpt(l_maicon,email_address); -- utl_smtp.rcpt(l_maicon,'henryclark@beckgroup.com'); utl_smtp.DATA(l_maicon,'From: cmic@beckgroup.com' || utl_tcp.crlf || 'To:'||email_address|| utl_tcp.crlf || 'Subject: ' || sys_context('USERENV','DB_NAME') ||' - '||hold_subject|| utl_tcp.crlf || 'Our records at ' || to_char(sysdate,'HH:MM') ||' indicate you have only entered '||NVL(emp_rec.total_hours,0)||' hours for the current pay period of which '||NVL(emp_rec.total_hours_not_submitted,0)||' have not been submitted.'||utl_tcp.crlf|| 'Please make additional entries (if necessary) to prevent any delay in the processing of your paycheck. '||utl_tcp.crlf||utl_tcp.crlf||utl_tcp.crlf||utl_tcp.crlf||utl_tcp.crlf|| 'This is an automated message - please do not respond to sender'||utl_tcp.crlf||utl_tcp.crlf|| 'This message will be automatically sent, as a reminder, to each user who has not entered at least 80 hours.'||utl_tcp.crlf|| 'You must complete the entry of your time by '||hold_cutoff_date||utl_tcp.crlf||utl_tcp.crlf|| 'If you have any questions please email payroll@beckgroup.com'||utl_tcp.crlf||utl_tcp.crlf|| 'Use this link to navigate to E-Time and enter timecards' ||utl_tcp.crlf || 'http://erp.beckgroup.com'||utl_tcp.crlf|| 'and then click E-time'||utl_tcp.crlf); utl_smtp.quit(l_maicon); <> emp_list := emp_list||emp_rec.emp_no||' '||RPAD(INITCAP(emp_rec.NAME),40)||' '||TO_CHAR(NVL(emp_rec.total_hours,0),999.99)||' '||TO_CHAR(NVL(emp_rec.total_hours_not_submitted,0),999.99)||utl_tcp.crlf; IF LENGTH(emp_list) > 7000 THEN emp_list3 := emp_list2; emp_list2 := emp_list; emp_list := NULL; END IF; if emp_rec.time_mgr in ('113822','113842','113664','112421','113567','110084','113737','110128') then hold_emp_no := '118911'; else hold_emp_no := emp_rec.time_mgr; end if; if hold_emp_no = '118911' then goto bypass_department; end if; hold_email_address := emp_rec.mgr_email; if emp_rec.emp_no=117275 then hold_email_address := 'lindadicus@beckgroup.com'; end if; IF SUBSTR(hold_email_address,1,1) = '@' THEN hold_error := 'Department E-Mail Address Missing: '||emp_rec.emp_home_dept_code; DBMS_OUTPUT.PUT_LINE(hold_error); GOTO bypass_department; END IF; --This is hard coded as the Phoenix office has employees from California and Phoenix -- l_maicon := utl_smtp.open_connection('localhost'); utl_smtp.helo(l_maicon,'hostname'); utl_smtp.mail(l_maicon,'oracle@beckgroup.com'); --utl_smtp.rcpt(l_maicon,'henryclark@beckgroup.com'); utl_smtp.rcpt(l_maicon,hold_email_address); utl_smtp.DATA(l_maicon,'From: cmic@beckgroup.com' || utl_tcp.crlf || 'To:'||hold_email_address|| utl_tcp.crlf || 'Subject: ' || sys_context('USERENV','DB_NAME') ||' - '||hold_subject|| utl_tcp.crlf || 'Our records at ' || to_char(sysdate,'HH:MM') ||' indicate '||emp_rec.NAME||' only entered '||NVL(emp_rec.total_hours,0)||' hours for the current pay period. If this is not correct please have the employee complete the entry of their time to prevent'||utl_tcp.crlf|| 'any delay in the processing of their paycheck. '||utl_tcp.crlf||utl_tcp.crlf||utl_tcp.crlf||utl_tcp.crlf||utl_tcp.crlf|| 'This is an automated message - please do not respond to sender'||utl_tcp.crlf||utl_tcp.crlf|| 'This message will be automatically sent, as a reminder, to each user who has not entered at least 80 hours.'||utl_tcp.crlf|| 'They must complete the entry of their time by '||hold_cutoff_date||utl_tcp.crlf||utl_tcp.crlf|| 'If you have any questions please email payroll@beckgroup.com'||utl_tcp.crlf); utl_smtp.quit(l_maicon); <> -- This is hard coded as the Phoenix office has employees from California and Phoenix -- --IF hold_email_address = 'rosesenerchia@beckgroup.com' THEN -- l_maicon := utl_smtp.open_connection('localhost'); -- utl_smtp.helo(l_maicon,'hostname'); -- utl_smtp.mail(l_maicon,'oracle@beckgroup.com'); -- utl_smtp.rcpt(l_maicon,'shelleyreilly@beckgroup.com'); --utl_smtp.rcpt(l_maicon,'henryclark@beckgroup.com'); -- utl_smtp.DATA(l_maicon,'From: cmic@beckgroup.com' || utl_tcp.crlf || -- 'To:'||'shelleyreilly@beckgroup.com'|| utl_tcp.crlf || -- 'Subject: CMIC E-Time Reminder - Please Read !!!! ' || utl_tcp.crlf || -- 'Our records at ' || to_char(sysdate,'HH:MM') ||' indicate '||emp_rec.NAME||' only entered '||NVL(emp_rec.total_hours,0)||' hours for the current pay period. If this is not correct please have the employee complete the entry of their time to prevent'||utl_tcp.crlf|| -- 'any delay in the processing of their paycheck. '||utl_tcp.crlf||utl_tcp.crlf||utl_tcp.crlf||utl_tcp.crlf||utl_tcp.crlf|| -- 'This is an automated message - please do not respond to sender'||utl_tcp.crlf||utl_tcp.crlf|| -- 'This message will be automatically sent, as a reminder, to each user who has not entered at least 80 hours.'||utl_tcp.crlf|| -- 'They must complete the entry of their time by Sunday at midnight.'||utl_tcp.crlf||utl_tcp.crlf|| -- 'If you have any questions please call Linda Johnson at 214-303-6618.'||utl_tcp.crlf); -- utl_smtp.quit(l_maicon); -- END IF; output_line := email_address||' '||hold_email_address; -- UTL_FILE.PUT_LINE (output_file, output_line); END LOOP; -- UTL_FILE.FCLOSE(output_file); l_maicon := utl_smtp.open_connection('localhost'); utl_smtp.helo(l_maicon,'hostname'); utl_smtp.mail(l_maicon,'oracle@beckgroup.com'); utl_smtp.rcpt(l_maicon,'lindadicus@beckgroup.com'); --utl_smtp.rcpt(l_maicon,'henryclark@beckgroup.com'); utl_smtp.DATA(l_maicon,'From: cmic@beckgroup.com' || utl_tcp.crlf || 'To:'||'lindadicus@beckgroup.com'|| utl_tcp.crlf || 'Subject: ' || sys_context('USERENV','DB_NAME') ||' - '|| 'Employees Who Entered Less Than 80 Hours - Please Read !!!! '|| to_char(sysdate,'HH:MM') || utl_tcp.crlf || 'This message will be automatically sent, as a reminder, to each user who has not entered at least 80 hours by the cutoff time and date.'||utl_tcp.crlf||utl_tcp.crlf|| output_header||utl_tcp.crlf|| RTRIM(emp_list)|| RTRIM(emp_list2)|| RTRIM(emp_list3)||utl_tcp.crlf); utl_smtp.quit(l_maicon); l_maicon := utl_smtp.open_connection('localhost'); utl_smtp.helo(l_maicon,'hostname'); utl_smtp.mail(l_maicon,'cmicalert@beckgroup.com'); utl_smtp.rcpt(l_maicon,'itstaff@beckgroup.com'); utl_smtp.DATA(l_maicon,'From: cmicalert@beckgroup.com' || utl_tcp.crlf || 'To:'||'itstaff@beckgroup.com'|| utl_tcp.crlf || 'Subject: ' || sys_context('USERENV','DB_NAME') ||' - '|| 'Employees Who Entered Less Than 80 Hours - Please Read !!!! '|| to_char(sysdate,'HH:MM') || utl_tcp.crlf || 'This message will be automatically sent, as a reminder, to each user who has not entered at least 80 hours on the cutoff time and date.'||utl_tcp.crlf||utl_tcp.crlf|| output_header||utl_tcp.crlf|| RTRIM(emp_list)|| RTRIM(emp_list2)|| RTRIM(emp_list3)||utl_tcp.crlf); utl_smtp.quit(l_maicon); END; PROCEDURE bec_over80hr_email( in_year IN NUMBER, in_period IN NUMBER) AS -- output_file UTL_FILE.file_type; -- SMTP email variables conn UTL_SMTP.connection; req UTL_HTTP.req; resp UTL_HTTP.resp; DATA RAW (20000); cursor_count number:=0; tab varchar2(2):=CHR(9); CURSOR emp_cursor IS SELECT emp_no, emp_first_name||' '||emp_last_name AS NAME, emp_home_dept_code, CASE WHEN emp_email_address LIKE '%@%' THEN emp_email_address ELSE emp_email_address||'@beckgroup.com' END AS emp_email_address, emp_pyg_code, case when emp_type = 'S' then 'Salaried' else 'Hourly' end employee_type, to_char(NVL(aa.total_hours,0),'999D00') AS total_hours FROM PYEMPLOYEE_TABLE, ( SELECT tsh_emp_no, total_hours from ( SELECT tsh_emp_no, SUM(NVL(tsh_day_01,0) + NVL(tsh_day_02,0) + NVL(tsh_day_03,0) + NVL(tsh_day_04,0) + NVL(tsh_day_05,0) + NVL(tsh_day_06,0) + NVL(tsh_day_07,0) + NVL(tsh_day_08,0) + NVL(tsh_day_09,0) + NVL(tsh_day_10,0) + NVL(tsh_day_11,0) + NVL(tsh_day_12,0) + NVL(tsh_day_13,0) + NVL(tsh_day_14,0)) AS total_hours FROM PYEMPTIMSHT_CERT WHERE tsh_ppr_period = in_period AND tsh_ppr_year = in_year AND tsh_prn_code IN ('BW') GROUP BY tsh_emp_no) where tsh_emp_no in ( select emp_no from pyemployee_table where EMP_STATUS = 'A' ) AND total_hours > 80 ) aa WHERE emp_no = aa.tsh_emp_no (+) AND emp_status = 'A' AND aa.total_hours > 80 ORDER BY emp_type, emp_last_name, emp_first_name; -- create a instance of the cursor emp_rec PYEMPLOYEE_TABLE%ROWTYPE; l_maicon utl_smtp.connection; email_address VARCHAR2 (100); emp_list VARCHAR2 (8000); emp_list2 VARCHAR2 (8000); emp_list3 VARCHAR2 (8000); hold_emp_no VARCHAR2 (16); hold_email_address VARCHAR2 (100); output_path VARCHAR2 (50); output_filename VARCHAR2 (20); output_header VARCHAR2 (312); output_line VARCHAR2 (255); output_file UTL_FILE.FILE_TYPE; hold_error VARCHAR2 (100); BEGIN -- output_filename := 'E-TIME.txt'; -- output_file := UTL_FILE.FOPEN ('/transfer', output_filename, 'w'); output_header := 'Emp ID Name Employee Type Department Hours Entered'; FOR emp_rec IN emp_cursor LOOP email_address := emp_rec.emp_email_address; IF SUBSTR(email_address,1,1) = '@' THEN hold_error := 'E-Mail Address Missing: '||emp_rec.NAME; DBMS_OUTPUT.PUT_LINE(hold_error); GOTO bypass_employee; END IF; IF emp_rec.employee_type != 'Hourly' then GOTO bypass_employee; END IF; l_maicon := utl_smtp.open_connection('localhost'); utl_smtp.helo(l_maicon,'hostname'); utl_smtp.mail(l_maicon,'oracle@beckgroup.com'); utl_smtp.rcpt(l_maicon,email_address); utl_smtp.DATA(l_maicon,'From: cmic@beckgroup.com' || utl_tcp.crlf || 'To:'||email_address|| utl_tcp.crlf || 'Subject: ' || sys_context('USERENV','DB_NAME') ||' - '|| 'CMIC E-Time Over 80 Hours - Please Read !!!! ' || utl_tcp.crlf || 'Our records indicate you have entered '||NVL(emp_rec.total_hours,0)||' hours for the current pay period.'||utl_tcp.crlf|| 'Please note over 80 hours will require approval by a manager '||utl_tcp.crlf||utl_tcp.crlf||utl_tcp.crlf||utl_tcp.crlf||utl_tcp.crlf|| 'This is an automated message - please do not respond to sender'||utl_tcp.crlf||utl_tcp.crlf|| 'This message will be automatically sent, as a reminder, to each user who has entered more than 80 hours.'||utl_tcp.crlf|| 'You must complete the entry of your time by Sunday at 5pm.' ||utl_tcp.crlf||utl_tcp.crlf|| 'Use this link to navigate to E-Time and enter timecards ----> http://etime.beckgroup.com'||utl_tcp.crlf); utl_smtp.quit(l_maicon); <> if emp_rec.emp_home_dept_code in ('DSGN','ATLDN') and emp_rec.employee_type = 'Salaried' then goto bypass_listing; end if; emp_list := emp_list||emp_rec.emp_no||' '||RPAD(INITCAP(emp_rec.NAME),40)||' '||RPAD(emp_rec.employee_type,17)||RPAD(emp_rec.emp_home_dept_code,'15')||TO_CHAR(NVL(emp_rec.total_hours,0),999.99)||utl_tcp.crlf; IF LENGTH(emp_list) > 7000 THEN emp_list3 := emp_list2; emp_list2 := emp_list; emp_list := NULL; END IF; <> BEGIN SELECT emp_no INTO hold_emp_no FROM UETD_CLASSIFIER WHERE department = emp_rec.emp_home_dept_code AND comp_code = '03'; EXCEPTION WHEN NO_DATA_FOUND THEN hold_emp_no := '113664'; END; BEGIN SELECT CASE WHEN emp_email_address LIKE '%@%' THEN emp_email_address ELSE emp_email_address||'@beckgroup.com' END INTO hold_email_address FROM PYEMPLOYEE_TABLE WHERE emp_no = hold_emp_no; EXCEPTION WHEN NO_DATA_FOUND THEN hold_emp_no := 'henryclark@beckgroup.com'; END; IF SUBSTR(hold_email_address,1,1) = '@' THEN hold_error := 'Department E-Mail Address Missing: '||emp_rec.emp_home_dept_code; DBMS_OUTPUT.PUT_LINE(hold_error); GOTO bypass_department; END IF; <> output_line := email_address||' '||hold_email_address; -- UTL_FILE.PUT_LINE (output_file, output_line); END LOOP; -- UTL_FILE.FCLOSE(output_file); l_maicon := utl_smtp.open_connection('localhost'); utl_smtp.helo(l_maicon,'hostname'); utl_smtp.mail(l_maicon,'oracle@beckgroup.com'); utl_smtp.rcpt(l_maicon,'lindadicus@beckgroup.com'); utl_smtp.DATA(l_maicon,'From: cmic@beckgroup.com' || utl_tcp.crlf || 'To:'||'lindadicus@beckgroup.com'|| utl_tcp.crlf || 'Subject: ' || sys_context('USERENV','DB_NAME') ||' - '|| 'Hourly Employees Who Entered MORE Than 80 Hours - Please Read !!!!' || utl_tcp.crlf || 'This message will be automatically sent, as a reminder, to each user who has entered at least 81 hours on the last Friday of the payroll period.'||utl_tcp.crlf||utl_tcp.crlf|| output_header||utl_tcp.crlf|| RTRIM(emp_list)|| RTRIM(emp_list2)|| RTRIM(emp_list3)||utl_tcp.crlf); l_maicon := utl_smtp.open_connection('localhost'); utl_smtp.helo(l_maicon,'hostname'); utl_smtp.mail(l_maicon,'oracle@beckgroup.com'); utl_smtp.rcpt(l_maicon,'heatherhood@beckgroup.com'); utl_smtp.DATA(l_maicon,'From: cmic@beckgroup.com' || utl_tcp.crlf || 'To:'||'heatherhood@beckgroup.com'|| utl_tcp.crlf || 'Subject: ' || sys_context('USERENV','DB_NAME') ||' - '|| 'Hourly Employees Who Entered MORE Than 80 Hours - Please Read !!!!' || utl_tcp.crlf || 'This message will be automatically sent, as a reminder, to each user who has entered at least 81 hours on the last Friday of the payroll period.'||utl_tcp.crlf||utl_tcp.crlf|| output_header||utl_tcp.crlf|| RTRIM(emp_list)|| RTRIM(emp_list2)|| RTRIM(emp_list3)||utl_tcp.crlf); utl_smtp.quit(l_maicon); l_maicon := utl_smtp.open_connection('localhost'); utl_smtp.helo(l_maicon,'hostname'); utl_smtp.mail(l_maicon,'cmicalert@beckgroup.com'); utl_smtp.rcpt(l_maicon,'itstaff@beckgroup.com'); utl_smtp.DATA(l_maicon,'From: cmicalert@beckgroup.com' || utl_tcp.crlf || 'To:'||'itstaff@beckgroup.com'|| utl_tcp.crlf || 'Subject: ' || sys_context('USERENV','DB_NAME') ||' - '|| 'Hourly Employees Who Entered MORE Than 80 Hours - Please Read !!!!' || utl_tcp.crlf || 'This message will be automatically sent, as a reminder, to each user who has entered at least 81 hours on the last Friday of the payroll period.'||utl_tcp.crlf||utl_tcp.crlf|| output_header||utl_tcp.crlf|| RTRIM(emp_list)|| RTRIM(emp_list2)|| RTRIM(emp_list3)||utl_tcp.crlf); utl_smtp.quit(l_maicon); COMMIT; END; PROCEDURE bec_laborcost_email( from_date IN date, to_date IN date ) AS -- Procedure generates asset import file and emails it to current user. -- If local db output file is needed, uncomment output_path VARCHAR2 (50); output_filename VARCHAR2 (20); output_line VARCHAR2 (750); -- output_file UTL_FILE.file_type; -- SMTP email variables conn UTL_SMTP.connection; req UTL_HTTP.req; resp UTL_HTTP.resp; DATA RAW (20000); cursor_count number:=0; tab varchar2(2):=CHR(9); CURSOR output_cursor IS select jcdt_comp_code, jcdt_job_code, jcdt_phs_code, jcdt_cat_code, jcdt_unit, jcdt_post_date, jcdt_dept_code, jcdt_acc_code, jcdt_src_desc, jcdt_amt from jcdetail where jcdt_post_date between from_date and to_date and jcdt_type_code = 'C' and jcdt_cat_code like '1%'; -- create a instance of the cursor sal_rec output_cursor%ROWTYPE; BEGIN -- SMTP Email Header section conn := bec_mail.begin_mail (sender => 'CMiC ', recipients => user ||'<'||user||'@beckgroup.com>,'|| '"itstaff"', subject => sys_context('USERENV','DB_NAME') ||' - '|| 'Labor Cost Report', mime_type => bec_mail.multipart_mime_type ); -- SMTP email text section bec_mail.attach_text (conn => conn, DATA => '
The attached file includes Labor Cost transaction detail..
', mime_type => 'text/html' ); -- SMTP email attachement start section bec_mail.begin_attachment (conn => conn, mime_type => 'application/vnd.ms-excel', inline => FALSE, filename => 'LaborCost'||to_char(from_date,'MMYY')||'-'||to_char(to_date, 'MMYY') ||'.xls', transfer_enc => '' ); -- Output to a local file section, uncomment var section also. -- output_filename := 'AG_'||TRUNC(SYSDATE)||'.txt'; ---TO_CHAR(SYSDATE,'HH24MI-MMDDYY')||'.txt'; -- output_file := UTL_FILE.FOPEN ('/transfer', output_filename, 'w'); --output_line :=('*** ' ||to_char(from_date,'MMYY')||'-'||to_char(to_date, 'MMYY') ||'.xls ***'|| UTL_TCP.CRLF --||'MIME-Version: 1.0' || UTL_TCP.CRLF --||'X-Document-Type: Workbook' || UTL_TCP.CRLF --||'Content-Type: multipart/related; boundary=3D"----=_NextPart_ExcelWorkbook"'|| UTL_TCP.CRLF|| UTL_TCP.CRLF --|| '------=_NextPart_ExcelWorkbook'|| UTL_TCP.CRLF --|| 'Content-Location: books.xls'|| UTL_TCP.CRLF --|| 'Content-Transfer-Encoding: quoted-printable' || UTL_TCP.CRLF --|| 'Content-Type: text/html; charset=3D"us-ascii"'|| UTL_TCP.CRLF || UTL_TCP.CRLF --); --bec_mail.write_text (conn => conn, MESSAGE => output_line ); output_line :=(''|| UTL_TCP.CRLF ||''|| UTL_TCP.CRLF ||''|| UTL_TCP.CRLF ||''|| UTL_TCP.CRLF ||''|| UTL_TCP.CRLF ||''|| UTL_TCP.CRLF ||'Sheet1'|| UTL_TCP.CRLF ||''|| UTL_TCP.CRLF || ''|| UTL_TCP.CRLF || ''|| UTL_TCP.CRLF || ''|| UTL_TCP.CRLF ||''|| UTL_TCP.CRLF ||''|| UTL_TCP.CRLF || UTL_TCP.CRLF ); --bec_mail.write_text (conn => conn, MESSAGE => output_line ); --output_line :=('------=_NextPart_ExcelWorkbook' || UTL_TCP.CRLF --||'Content-Location: sheet1.htm'|| UTL_TCP.CRLF --||'Content-Transfer-Encoding: quoted-printable'|| UTL_TCP.CRLF --||'Content-Type: text/html; charset=3D"us-ascii"'|| UTL_TCP.CRLF || UTL_TCP.CRLF --); bec_mail.write_text (conn => conn, MESSAGE => output_line ); output_line :=('' || UTL_TCP.CRLF ); bec_mail.write_text (conn => conn, MESSAGE => output_line ); output_line :=( ''|| UTL_TCP.CRLF ); bec_mail.write_text (conn => conn, MESSAGE => output_line ); -- Loop thru sal_rec -- First time to gather count of cursor to remove last ctrl feed. FOR sal_rec IN output_cursor LOOP cursor_count:= output_cursor%ROWCOUNT; if cursor_count>64000 then output_line :=('Data exceeds 64000 rows' || UTL_TCP.CRLF ); bec_mail.write_text (conn => conn, MESSAGE => output_line ); goto escape; end if; END LOOP; -- Loop thru sal_rec -- Please note the UTL_TCP.CRLF is to place a hard return in email file. FOR sal_rec IN output_cursor LOOP output_line := '' ||'' ||'' ||'' ||'' ||'' ||'' ||'' ||'' ||''; if (output_cursor%ROWCOUNT=cursor_count) THEN output_line:= output_line || ''; ELSE output_line:= output_line || UTL_TCP.CRLF; end if; -- SMTP write one line to attachment bec_mail.write_text (conn => conn, MESSAGE => output_line ); -- UTL_FILE.PUT_LINE (output_file, output_line); END LOOP; <> output_line :=('
CompanyJob CodePhaseCategoryUnits Post DateDeptAcc CodeDescriptionAmount
'||sal_rec.jcdt_comp_code||''|| sal_rec.jcdt_job_code||''|| sal_rec.jcdt_phs_code||''|| sal_rec.jcdt_cat_code||''|| sal_rec.jcdt_unit||''|| sal_rec.jcdt_post_date||''|| sal_rec.jcdt_dept_code||''|| sal_rec.jcdt_acc_code||''|| sal_rec.jcdt_src_desc||''|| sal_rec.jcdt_amt||'
'||UTL_TCP.CRLF||''|| UTL_TCP.CRLF|| UTL_TCP.CRLF ); bec_mail.write_text (conn => conn, MESSAGE => output_line ); -- Close output file if needed -- UTL_FILE.FCLOSE(output_file); -- Close attachement in email. -- Close email and send it. --bec_mail.end_attachment (conn => conn); bec_mail.end_mail (conn => conn); -- End of bec_laborcost_email proc END; PROCEDURE bec_laborcostx_email( from_date IN date, to_date IN date ) AS -- Procedure generates asset import file and emails it to current user. -- If local db output file is needed, uncomment output_path VARCHAR2 (50); output_filename VARCHAR2 (20); output_line VARCHAR2 (750); -- output_file UTL_FILE.file_type; -- SMTP email variables conn UTL_SMTP.connection; req UTL_HTTP.req; resp UTL_HTTP.resp; DATA RAW (20000); cursor_count number:=0; tab varchar2(2):=CHR(9); CURSOR output_cursor IS select phy_work_comp_code, phy_job, phy_phase, phy_category, emp_home_dept_code, phy_dept_code, phy_gl_acc_code, ppr_posting_date, sum(phy_hrs_worked) hours, sum(case when phy_tran_type = 'BW' then phy_charge_out_amt else phy_amount end) amount from pyemppayhist, pycompayprd, pyemployee_table where phy_comp_code = ppr_comp_code and phy_prn_code = ppr_prn_code and phy_ppr_year = ppr_year and phy_ppr_period = ppr_period and phy_emp_no = emp_no and ppr_posting_date between from_date and to_date and phy_hire_flag is null and phy_job is not null and phy_dept_code != '00' group by phy_work_comp_code, phy_job, phy_phase, phy_category, emp_home_dept_code, phy_dept_code, phy_gl_acc_code, ppr_posting_date order by phy_work_comp_code, phy_job, phy_phase, phy_category, emp_home_dept_code, phy_dept_code, phy_gl_acc_code, ppr_posting_date; -- create a instance of the cursor sal_rec output_cursor%ROWTYPE; BEGIN -- SMTP Email Header section conn := bec_mail.begin_mail (sender => 'CMiC ', recipients => user ||'<'||user||'@beckgroup.com>,'|| '"itstaff"', subject => sys_context('USERENV','DB_NAME') ||' - '|| 'Labor Cost Report', mime_type => bec_mail.multipart_mime_type ); -- SMTP email text section bec_mail.attach_text (conn => conn, DATA => '
The attached file includes Labor Cost transaction detail..
', mime_type => 'text/html' ); -- SMTP email attachement start section bec_mail.begin_attachment (conn => conn, mime_type => 'application/vnd.ms-excel', inline => FALSE, filename => 'LaborCost'||to_char(from_date,'MMYY')||'-'||to_char(to_date, 'MMYY') ||'.xls', transfer_enc => '' ); -- Output to a local file section, uncomment var section also. output_line :=(''|| UTL_TCP.CRLF ||''|| UTL_TCP.CRLF ||''|| UTL_TCP.CRLF ||''|| UTL_TCP.CRLF ||''|| UTL_TCP.CRLF ||''|| UTL_TCP.CRLF ||'Sheet1'|| UTL_TCP.CRLF ||''|| UTL_TCP.CRLF || ''|| UTL_TCP.CRLF || ''|| UTL_TCP.CRLF || ''|| UTL_TCP.CRLF ||''|| UTL_TCP.CRLF ||''|| UTL_TCP.CRLF || UTL_TCP.CRLF ); bec_mail.write_text (conn => conn, MESSAGE => output_line ); output_line :=('' || UTL_TCP.CRLF ); bec_mail.write_text (conn => conn, MESSAGE => output_line ); output_line :=( ''|| UTL_TCP.CRLF ); bec_mail.write_text (conn => conn, MESSAGE => output_line ); -- Loop thru sal_rec -- First time to gather count of cursor to remove last ctrl feed. FOR sal_rec IN output_cursor LOOP cursor_count:= output_cursor%ROWCOUNT; if cursor_count>64000 then output_line :=('Data exceeds 64000 rows' || UTL_TCP.CRLF ); bec_mail.write_text (conn => conn, MESSAGE => output_line ); goto escape; end if; END LOOP; -- Loop thru sal_rec -- Please note the UTL_TCP.CRLF is to place a hard return in email file. FOR sal_rec IN output_cursor LOOP output_line := '' ||'' ||'' ||'' ||'' ||'' ||'' ||'' ||'' ||''; if (output_cursor%ROWCOUNT=cursor_count) THEN output_line:= output_line || ''; ELSE output_line:= output_line || UTL_TCP.CRLF; end if; -- SMTP write one line to attachment bec_mail.write_text (conn => conn, MESSAGE => output_line ); -- UTL_FILE.PUT_LINE (output_file, output_line); END LOOP; <> output_line :=('
CompanyJob CodePhaseCategoryEmp Home DeptJob Dept Acc CodePost DateUnitsAmount
'||sal_rec.phy_work_comp_code||''|| sal_rec.phy_job||''|| sal_rec.phy_phase||''|| sal_rec.phy_category||''|| sal_rec.emp_home_dept_code||''|| sal_rec.phy_dept_code||''|| sal_rec.phy_gl_acc_code||''|| sal_rec.ppr_posting_date||''|| sal_rec.hours||''|| sal_rec.amount||'
'||UTL_TCP.CRLF||''|| UTL_TCP.CRLF|| UTL_TCP.CRLF ); bec_mail.write_text (conn => conn, MESSAGE => output_line ); -- Close output file if needed -- UTL_FILE.FCLOSE(output_file); -- Close attachement in email. -- Close email and send it. --bec_mail.end_attachment (conn => conn); bec_mail.end_mail (conn => conn); -- End of bec_laborcost_email proc END; PROCEDURE bec_laborcost2_email( from_date IN date, to_date IN date ) AS -- Procedure generates asset import file and emails it to current user. -- If local db output file is needed, uncomment output_path VARCHAR2 (50); output_filename VARCHAR2 (20); output_line VARCHAR2 (750); -- output_file UTL_FILE.file_type; -- SMTP email variables conn UTL_SMTP.connection; req UTL_HTTP.req; resp UTL_HTTP.resp; DATA RAW (20000); cursor_count number:=0; tab varchar2(2):=CHR(9); CURSOR output_cursor IS select phy_work_comp_code, phy_job, phy_phase, phy_category, phy_emp_no, emp_last_name||', '||emp_first_name name, emp_home_dept_code, phy_dept_code, phy_gl_acc_code, ppr_posting_date, sum(phy_hrs_worked) hours, sum(case when phy_tran_type = 'BW' then phy_charge_out_amt else phy_amount end) amount from pyemppayhist, pycompayprd, pyemployee_table where phy_comp_code = ppr_comp_code and phy_prn_code = ppr_prn_code and phy_ppr_year = ppr_year and phy_ppr_period = ppr_period and phy_emp_no = emp_no and ppr_posting_date between from_date and to_date and phy_hire_flag is null and phy_job is not null and phy_dept_code != '00' group by phy_work_comp_code, phy_job, phy_phase, phy_category, phy_emp_no, emp_last_name||', '||emp_first_name, emp_home_dept_code, phy_dept_code, phy_gl_acc_code, ppr_posting_date order by phy_work_comp_code, phy_job, phy_phase, phy_category, phy_emp_no, emp_home_dept_code, phy_dept_code, phy_gl_acc_code, ppr_posting_date; -- create a instance of the cursor sal_rec output_cursor%ROWTYPE; BEGIN -- SMTP Email Header section conn := bec_mail.begin_mail (sender => 'CMiC ', recipients => user ||'<'||user||'@beckgroup.com>,'|| '"itstaff"', subject => sys_context('USERENV','DB_NAME') ||' - '|| 'Labor Cost Report', mime_type => bec_mail.multipart_mime_type ); -- SMTP email text section bec_mail.attach_text (conn => conn, DATA => '
The attached file includes Labor Cost transaction detail..
', mime_type => 'text/html' ); -- SMTP email attachement start section bec_mail.begin_attachment (conn => conn, mime_type => 'application/vnd.ms-excel', inline => FALSE, filename => 'LaborCost'||to_char(from_date,'MMYY')||'-'||to_char(to_date, 'MMYY') ||'.xls', transfer_enc => '' ); -- Output to a local file section, uncomment var section also. output_line :=(''|| UTL_TCP.CRLF ||''|| UTL_TCP.CRLF ||''|| UTL_TCP.CRLF ||''|| UTL_TCP.CRLF ||''|| UTL_TCP.CRLF ||''|| UTL_TCP.CRLF ||'Sheet1'|| UTL_TCP.CRLF ||''|| UTL_TCP.CRLF || ''|| UTL_TCP.CRLF || ''|| UTL_TCP.CRLF || ''|| UTL_TCP.CRLF ||''|| UTL_TCP.CRLF ||''|| UTL_TCP.CRLF || UTL_TCP.CRLF ); bec_mail.write_text (conn => conn, MESSAGE => output_line ); output_line :=('' || UTL_TCP.CRLF ); bec_mail.write_text (conn => conn, MESSAGE => output_line ); output_line :=( ''|| UTL_TCP.CRLF ); bec_mail.write_text (conn => conn, MESSAGE => output_line ); -- Loop thru sal_rec -- First time to gather count of cursor to remove last ctrl feed. FOR sal_rec IN output_cursor LOOP cursor_count:= output_cursor%ROWCOUNT; if cursor_count>64000 then output_line :=('Data exceeds 64000 rows' || UTL_TCP.CRLF ); bec_mail.write_text (conn => conn, MESSAGE => output_line ); goto escape; end if; END LOOP; -- Loop thru sal_rec -- Please note the UTL_TCP.CRLF is to place a hard return in email file. FOR sal_rec IN output_cursor LOOP output_line := '' ||'' ||'' ||'' ||'' ||'' ||'' ||'' ||'' ||'' ||'' ||''; if (output_cursor%ROWCOUNT=cursor_count) THEN output_line:= output_line || ''; ELSE output_line:= output_line || UTL_TCP.CRLF; end if; -- SMTP write one line to attachment bec_mail.write_text (conn => conn, MESSAGE => output_line ); -- UTL_FILE.PUT_LINE (output_file, output_line); END LOOP; <> output_line :=('
CompanyJob CodePhaseCategoryEmp IDEmp NameEmp Home DeptJob Dept Acc CodePost DateUnitsAmount
'||sal_rec.phy_work_comp_code||''|| sal_rec.phy_job||''|| sal_rec.phy_phase||''|| sal_rec.phy_category||''|| sal_rec.phy_emp_no||''|| sal_rec.name||''|| sal_rec.emp_home_dept_code||''|| sal_rec.phy_dept_code||''|| sal_rec.phy_gl_acc_code||''|| sal_rec.ppr_posting_date||''|| sal_rec.hours||''|| sal_rec.amount||'
'||UTL_TCP.CRLF||''|| UTL_TCP.CRLF|| UTL_TCP.CRLF ); bec_mail.write_text (conn => conn, MESSAGE => output_line ); -- Close output file if needed -- UTL_FILE.FCLOSE(output_file); -- Close attachement in email. -- Close email and send it. --bec_mail.end_attachment (conn => conn); bec_mail.end_mail (conn => conn); -- End of bec_laborcost_email proc END; PROCEDURE bec_empnopto_xls ( p_text IN VARCHAR2, fromdate IN DATE, todate IN DATE ) AS -- Procedure generates asset import file and emails it to current user. -- If local db output file is needed, uncomment output_path VARCHAR2 (50); output_filename VARCHAR2 (20); output_line VARCHAR2 (750); -- output_file UTL_FILE.file_type; -- SMTP email variables conn UTL_SMTP.connection; req UTL_HTTP.req; resp UTL_HTTP.resp; DATA RAW (20000); cursor_count number:=0; tab varchar2(2):=CHR(9); CURSOR output_cursor IS --select jcdt_comp_code, jcdt_job_code, jcdt_phs_code, jcdt_cat_code, jcdt_unit, jcdt_post_date, jcdt_dept_code, jcdt_acc_code, jcdt_src_desc, jcdt_amt --from jcdetail where jcdt_post_date between fromdate and todate and jcdt_type_code = 'C' and jcdt_cat_code like '1%'; select tsh_emp_no as EMP_NO, tsh_date as "DATE", emp_last_name||', '||emp_first_name name, EMP_HOME_DEPT_CODE as dept_home, tsh_job_code as JOB_CODE, tsh_phase as PHASE, tsh_category as CATEGORY, tsh_normal_hours as Normal_Hours from pyemptimsht, pyemployee_table where tsh_emp_no = emp_no --and tsh_process_flag = 'P' and tsh_ppr_year >= to_char(fromdate,'yyyy') and tsh_date between fromdate and todate and tsh_normal_hours <> 0 and tsh_prn_code = 'BW'; -- create a instance of the cursor sal_rec output_cursor%ROWTYPE; BEGIN -- SMTP Email Header section conn := bec_mail.begin_mail (sender => 'CMiC ', recipients => user ||'<'||user||'@beckgroup.com>,'|| '"itstaff"', subject => sys_context('USERENV','DB_NAME') ||' - '|| 'EMP PTO Report', mime_type => bec_mail.multipart_mime_type ); -- SMTP email text section bec_mail.attach_text (conn => conn, DATA => '
The attached file includes EMP PTO Report.
', mime_type => 'text/html' ); -- SMTP email attachement start section bec_mail.begin_attachment (conn => conn, mime_type => 'application/vnd.ms-excel', inline => FALSE, filename => 'EMP PTO Report'||to_char(fromdate,'MMYY')||'-'||to_char(todate, 'MMYY') ||'.xls', transfer_enc => '' ); -- Output to a local file section, uncomment var section also. -- output_filename := 'AG_'||TRUNC(SYSDATE)||'.txt'; ---TO_CHAR(SYSDATE,'HH24MI-MMDDYY')||'.txt'; -- output_file := UTL_FILE.FOPEN ('/transfer', output_filename, 'w'); --output_line :=('*** ' ||to_char(from_date,'MMYY')||'-'||to_char(to_date, 'MMYY') ||'.xls ***'|| UTL_TCP.CRLF --||'MIME-Version: 1.0' || UTL_TCP.CRLF --||'X-Document-Type: Workbook' || UTL_TCP.CRLF --||'Content-Type: multipart/related; boundary=3D"----=_NextPart_ExcelWorkbook"'|| UTL_TCP.CRLF|| UTL_TCP.CRLF --|| '------=_NextPart_ExcelWorkbook'|| UTL_TCP.CRLF --|| 'Content-Location: books.xls'|| UTL_TCP.CRLF --|| 'Content-Transfer-Encoding: quoted-printable' || UTL_TCP.CRLF --|| 'Content-Type: text/html; charset=3D"us-ascii"'|| UTL_TCP.CRLF || UTL_TCP.CRLF --); --bec_mail.write_text (conn => conn, MESSAGE => output_line ); output_line :=(''|| UTL_TCP.CRLF ||''|| UTL_TCP.CRLF ||''|| UTL_TCP.CRLF ||''|| UTL_TCP.CRLF ||''|| UTL_TCP.CRLF ||''|| UTL_TCP.CRLF ||'Sheet1'|| UTL_TCP.CRLF ||''|| UTL_TCP.CRLF || ''|| UTL_TCP.CRLF || ''|| UTL_TCP.CRLF || ''|| UTL_TCP.CRLF ||''|| UTL_TCP.CRLF ||''|| UTL_TCP.CRLF || UTL_TCP.CRLF ); --bec_mail.write_text (conn => conn, MESSAGE => output_line ); --output_line :=('------=_NextPart_ExcelWorkbook' || UTL_TCP.CRLF --||'Content-Location: sheet1.htm'|| UTL_TCP.CRLF --||'Content-Transfer-Encoding: quoted-printable'|| UTL_TCP.CRLF --||'Content-Type: text/html; charset=3D"us-ascii"'|| UTL_TCP.CRLF || UTL_TCP.CRLF --); bec_mail.write_text (conn => conn, MESSAGE => output_line ); output_line :=('' || UTL_TCP.CRLF ); bec_mail.write_text (conn => conn, MESSAGE => output_line ); output_line :=( ''|| UTL_TCP.CRLF ); bec_mail.write_text (conn => conn, MESSAGE => output_line ); -- Loop thru sal_rec -- First time to gather count of cursor to remove last ctrl feed. FOR sal_rec IN output_cursor LOOP cursor_count:= output_cursor%ROWCOUNT; if cursor_count>64000 then output_line :=('Data exceeds 64000 rows' || UTL_TCP.CRLF ); bec_mail.write_text (conn => conn, MESSAGE => output_line ); goto escape; end if; END LOOP; -- Loop thru sal_rec -- Please note the UTL_TCP.CRLF is to place a hard return in email file. FOR sal_rec IN output_cursor LOOP output_line := '' ||'' ||'' ||'' ||'' ||'' ||'' ||''; if (output_cursor%ROWCOUNT=cursor_count) THEN output_line:= output_line || ''; ELSE output_line:= output_line || UTL_TCP.CRLF; end if; -- SMTP write one line to attachment bec_mail.write_text (conn => conn, MESSAGE => output_line ); -- UTL_FILE.PUT_LINE (output_file, output_line); END LOOP; <> output_line :=('
Emp NoDateNameHomeDeptJobCode PhaseCategoryNormal Hours
'||sal_rec.emp_no||''|| sal_rec.date||''|| sal_rec.name||''|| sal_rec.dept_home||''|| sal_rec.job_code||''|| sal_rec.phase||''|| sal_rec.category||''|| sal_rec.normal_hours||'
'||UTL_TCP.CRLF||''|| UTL_TCP.CRLF|| UTL_TCP.CRLF ); bec_mail.write_text (conn => conn, MESSAGE => output_line ); -- Close output file if needed -- UTL_FILE.FCLOSE(output_file); -- Close attachement in email. -- Close email and send it. --bec_mail.end_attachment (conn => conn); bec_mail.end_mail (conn => conn); -- End of bec_laborcost_email proc END; PROCEDURE bec_payrollaudit_email( in_bwyear IN NUMBER, in_bwstartpp IN NUMBER, in_bwendpp IN NUMBER, in_wkyear IN NUMBER, in_wkstartpp IN NUMBER, in_wkendpp IN NUMBER) AS -- Procedure generates asset import file and emails it to current user. -- If local db output file is needed, uncomment output_path VARCHAR2 (50); output_filename VARCHAR2 (20); output_line VARCHAR2 (750); -- output_file UTL_FILE.file_type; -- SMTP email variables conn UTL_SMTP.connection; req UTL_HTTP.req; resp UTL_HTTP.resp; DATA RAW (32000); cursor_count number:=0; tab varchar2(2):=CHR(9); CURSOR output_cursor IS select phy_prn_code, phy_ppr_period, phy_gl_acc_code, sum(amount) as amount from ( select phy_prn_code , phy_ppr_period, phy_tran_type, phy_tran_code, phy_gl_acc_code, sum(phy_amount) amount from pyemppayhist where phy_prn_code in ('BW','EXP','BON','BBON','OMRP','EQU') and phy_ppr_year = in_bwyear and phy_ppr_period between in_bwstartpp and in_bwendpp and phy_tran_code in ('180','220','250','260','270','290','310','340','342','345','360','440','450','480','485','2000','2090','250','920') and phy_hire_flag is null group by phy_prn_code, phy_ppr_period, phy_tran_type, phy_tran_code, phy_gl_acc_code union all select phy_prn_code, phy_ppr_period, phy_tran_type, phy_tran_code, phy_gl_acc_code, sum(phy_amount) amount from pyemppayhist where phy_prn_code in ('BW','EXP','BON','BBON','OMRP','EQU') and phy_ppr_year = in_bwyear and phy_ppr_period between in_bwstartpp and in_bwendpp and phy_tran_type in ('LE','BW') and phy_comp_code = '03' and phy_hire_flag is null and nvl(phy_adjustment,'N') = 'N' and phy_tran_code = 'NWHR' group by phy_prn_code, phy_ppr_period, phy_tran_type, phy_tran_code, phy_gl_acc_code having sum(phy_pay_amount) <> 0 union all select phy_prn_code, phy_ppr_period, phy_tran_type, phy_tran_code, phy_gl_acc_code, sum(phy_amount) amount from pyemppayhist where phy_prn_code in ('BW','EXP','BON','BBON','OMRP','EQU') and phy_ppr_year = in_bwyear and phy_ppr_period between in_bwstartpp and in_bwendpp and phy_tran_type in ('LE','BW') and phy_comp_code = '03' and phy_hire_flag is null and nvl(phy_adjustment,'N') = 'N' and phy_tran_code = 'OVHR' group by phy_prn_code, phy_ppr_period, phy_tran_type, phy_tran_code, phy_gl_acc_code having sum(phy_pay_amount) <> 0 union all select phy_prn_code, phy_ppr_period, phy_tran_type, phy_tran_code, phy_gl_acc_code, sum(phy_amount) amount from pyemppayhist where phy_prn_code in ('BW','EXP','BON','BBON','OMRP','EQU') and phy_ppr_year = in_bwyear and phy_ppr_period between in_bwstartpp and in_bwendpp and phy_tran_type in ('LE','BW') and phy_comp_code = '03' and phy_hire_flag is null and nvl(phy_adjustment,'N') = 'N' and phy_tran_code = 'DTHR' group by phy_prn_code, phy_ppr_period, phy_tran_type, phy_tran_code, phy_gl_acc_code having sum(phy_pay_amount) <> 0 union all select phy_prn_code, phy_ppr_period, phy_tran_type, phy_tran_code, phy_gl_acc_code, sum(phy_amount) amount from pyemppayhist where phy_prn_code in ('BW','EXP','BON','BBON','OMRP','EQU') and phy_ppr_year = in_bwyear and phy_ppr_period between in_bwstartpp and in_bwendpp and phy_tran_type in ('LE','BW') and phy_comp_code = '03' and phy_tran_code = 'SEV' group by phy_prn_code, phy_ppr_period, phy_tran_type, phy_tran_code, phy_gl_acc_code having sum(phy_pay_amount) <> 0 union all select phy_prn_code, phy_ppr_period, phy_tran_type, phy_tran_code, phy_gl_acc_code, sum(phy_amount) amount from pyemppayhist where phy_prn_code in ('BW','EXP','BON','BBON','OMRP','EQU') and phy_ppr_year = in_bwyear and phy_ppr_period between in_bwstartpp and in_bwendpp and phy_tran_type in ('LE','BW') and phy_comp_code = '03' and phy_tran_code = 'PTO' group by phy_prn_code, phy_ppr_period, phy_tran_type, phy_tran_code, phy_gl_acc_code having sum(phy_pay_amount) <> 0 union all select phy_prn_code, phy_ppr_period, phy_tran_type, phy_tran_code, phy_gl_acc_code, sum(phy_amount) amount from pyemppayhist where phy_prn_code in ('BW','EXP','BON','BBON','OMRP','EQU') and phy_ppr_year = in_bwyear and phy_ppr_period between in_bwstartpp and in_bwendpp and phy_tran_type in ('LE','BW') and phy_comp_code = '03' and phy_tran_code = 'OTH' group by phy_prn_code, phy_ppr_period, phy_tran_type, phy_tran_code, phy_gl_acc_code having sum(phy_pay_amount) <> 0 union all select phy_prn_code , phy_ppr_period, phy_tran_type, phy_tran_code, phy_gl_acc_code, sum(phy_amount) amount from pyemppayhist where phy_prn_code = 'WK' and phy_ppr_year = in_wkyear and phy_ppr_period between in_wkstartpp and in_wkendpp and phy_tran_code in ('180','220','250','260','270','290','310','340','342','345','360','440','450','480','485','2000','2090','250','920') and phy_hire_flag is null group by phy_prn_code, phy_ppr_period, phy_tran_type, phy_tran_code, phy_gl_acc_code union all select phy_prn_code, phy_ppr_period, phy_tran_type, phy_tran_code, phy_gl_acc_code, sum(phy_amount) amount from pyemppayhist where phy_prn_code = 'WK' and phy_ppr_year = in_wkyear and phy_ppr_period between in_wkstartpp and in_wkendpp and phy_tran_type in ('LE','BW') and phy_comp_code = '03' and phy_tran_code = 'NWHR' group by phy_prn_code, phy_ppr_period, phy_tran_type, phy_tran_code, phy_gl_acc_code having sum(phy_pay_amount) <> 0 union all select phy_prn_code, phy_ppr_period, phy_tran_type, phy_tran_code, phy_gl_acc_code, sum(phy_amount) amount from pyemppayhist where phy_prn_code = 'WK' and phy_ppr_year = in_wkyear and phy_ppr_period between in_wkstartpp and in_wkendpp and phy_tran_type in ('LE','BW') and phy_comp_code = '03' and phy_tran_code = 'OVHR' group by phy_prn_code, phy_ppr_period, phy_tran_type, phy_tran_code, phy_gl_acc_code having sum(phy_pay_amount) <> 0 union all select phy_prn_code, phy_ppr_period, phy_tran_type, phy_tran_code, phy_gl_acc_code, sum(phy_amount) amount from pyemppayhist where phy_prn_code = 'WK' and phy_ppr_year = in_wkyear and phy_ppr_period between in_wkstartpp and in_wkendpp and phy_tran_type in ('LE','BW') and phy_comp_code = '03' and phy_tran_code = 'DTHR' group by phy_prn_code, phy_ppr_period, phy_tran_type, phy_tran_code, phy_gl_acc_code having sum(phy_pay_amount) <> 0 union all select phy_prn_code, phy_ppr_period, phy_tran_type, phy_tran_code, phy_gl_acc_code, sum(phy_amount) amount from pyemppayhist where phy_prn_code = 'WK' and phy_ppr_year = in_wkyear and phy_ppr_period between in_wkstartpp and in_wkendpp and phy_tran_type in ('LE','BW') and phy_comp_code = '03' and phy_tran_code = 'SEV' group by phy_prn_code, phy_ppr_period, phy_tran_type, phy_tran_code, phy_gl_acc_code having sum(phy_pay_amount) <> 0 union all select phy_prn_code, phy_ppr_period, phy_tran_type, phy_tran_code, phy_gl_acc_code, sum(phy_amount) amount from pyemppayhist where phy_prn_code = 'WK' and phy_ppr_year = in_wkyear and phy_ppr_period between in_wkstartpp and in_wkendpp and phy_tran_type in ('LE','BW') and phy_comp_code = '03' and phy_tran_code = 'PTO' group by phy_prn_code, phy_ppr_period, phy_tran_type, phy_tran_code, phy_gl_acc_code having sum(phy_pay_amount) <> 0 union all select phy_prn_code, phy_ppr_period, phy_tran_type, phy_tran_code, phy_gl_acc_code, sum(phy_amount) amount from pyemppayhist where phy_prn_code = 'WK' and phy_ppr_year = in_wkyear and phy_ppr_period between in_wkstartpp and in_wkendpp and phy_tran_type in ('LE','BW') and phy_comp_code = '03' and phy_tran_code = 'OTH' group by phy_prn_code, phy_ppr_period, phy_tran_type, phy_tran_code, phy_gl_acc_code having sum(phy_pay_amount) <> 0 ) group by phy_prn_code, phy_ppr_period, phy_gl_acc_code order by 1,2,3; -- create a instance of the cursor sal_rec output_cursor%ROWTYPE; BEGIN -- SMTP Email Header section conn := bec_mail.begin_mail (sender => 'CMiC ', recipients => user ||'<'||user||'@beckgroup.com>', subject => sys_context('USERENV','DB_NAME') ||' - '|| 'Payroll Audit Report BW:'||in_bwyear ||' '||in_bwstartpp||'-'||in_bwendpp|| ',WK:'||in_wkyear||' '||in_wkstartpp||'-'||in_wkendpp, mime_type => bec_mail.multipart_mime_type ); -- SMTP email text section bec_mail.attach_text (conn => conn, DATA => '
The attached file includes the payroll audit report .
', mime_type => 'text/html' ); -- SMTP email attachement start section bec_mail.begin_attachment (conn => conn, mime_type => 'application/vnd.ms-excel', inline => FALSE, filename => 'Payroll Audit'||to_char(sysdate, 'MMDDYY') ||'.xls', transfer_enc => '' ); output_line :=(''|| UTL_TCP.CRLF ||''|| UTL_TCP.CRLF ||''|| UTL_TCP.CRLF ||''|| UTL_TCP.CRLF ||''|| UTL_TCP.CRLF ||''|| UTL_TCP.CRLF ||'Sheet1'|| UTL_TCP.CRLF ||''|| UTL_TCP.CRLF || ''|| UTL_TCP.CRLF || ''|| UTL_TCP.CRLF || ''|| UTL_TCP.CRLF ||''|| UTL_TCP.CRLF ||''|| UTL_TCP.CRLF || UTL_TCP.CRLF ); bec_mail.write_text (conn => conn, MESSAGE => output_line ); output_line :=('' || UTL_TCP.CRLF ); bec_mail.write_text (conn => conn, MESSAGE => output_line ); output_line :=( ''|| UTL_TCP.CRLF ); bec_mail.write_text (conn => conn, MESSAGE => output_line ); -- Loop thru sal_rec -- First time to gather count of cursor to remove last ctrl feed. FOR sal_rec IN output_cursor LOOP cursor_count:= output_cursor%ROWCOUNT; END LOOP; -- Loop thru sal_rec -- Please note the UTL_TCP.CRLF is to place a hard return in email file. FOR sal_rec IN output_cursor LOOP output_line := '' ||'' ||'' ||''; if (output_cursor%ROWCOUNT=cursor_count) THEN output_line:= output_line || ''; ELSE output_line:= output_line || UTL_TCP.CRLF; end if; -- SMTP write one line to attachment bec_mail.write_text (conn => conn, MESSAGE => output_line ); -- UTL_FILE.PUT_LINE (output_file, output_line); END LOOP; output_line :=('
PRN CODEPERIODACC CODEAMOUNT
'||sal_rec.phy_prn_code||''|| sal_rec.phy_ppr_period||''|| sal_rec.phy_gl_acc_code||''|| sal_rec.amount||'
'||UTL_TCP.CRLF|| UTL_TCP.CRLF); bec_mail.write_text (conn => conn, MESSAGE => output_line ); -- Close output file if needed -- UTL_FILE.FCLOSE(output_file); -- Close attachement in email. -- Close email and send it. bec_mail.end_attachment (conn => conn); bec_mail.end_mail (conn => conn); -- End of bec_manhour_email proc END; PROCEDURE bec_payrollaudit_emailx( in_bwyear IN NUMBER, in_bwstartpp IN NUMBER, in_bwendpp IN NUMBER, in_wkyear IN NUMBER, in_wkstartpp IN NUMBER, in_wkendpp IN NUMBER) AS -- Procedure generates asset import file and emails it to current user. -- If local db output file is needed, uncomment output_path VARCHAR2 (50); output_filename VARCHAR2 (50); output_line VARCHAR2 (1000); -- output_file UTL_FILE.file_type; -- SMTP email variables conn UTL_SMTP.connection; req UTL_HTTP.req; resp UTL_HTTP.resp; DATA RAW (32000); cursor_count number:=0; tab varchar2(2):=CHR(9); CURSOR output_cursor IS select phy_work_comp_code, phy_ppr_period, phy_emp_no, phy_date, phy_job, phy_phase, phy_category, phy_dept_code, phy_gl_acc_code, phy_tran_type, phy_tran_code, phy_batch_num, phy_amount from (select phy_work_comp_code, phy_ppr_period, phy_emp_no, phy_date, phy_job, phy_phase, phy_category, phy_dept_code, phy_gl_acc_code, phy_tran_type, phy_tran_code, phy_batch_num, case when phy_charge_out_amt is not null then phy_charge_out_amt else phy_amount end phy_amount from pyemppayhist where phy_prn_code = 'BW' and phy_hire_flag is null and phy_tran_code||phy_rate_code != 'NWHROTH' and phy_tran_code in ('NWHR','OVHR','SEV','OTH','PTO','360','320','415','450','180','380','250','390','440') and phy_ppr_year = in_bwyear and phy_ppr_period between in_bwstartpp and in_bwendpp union all select phy_work_comp_code, phy_ppr_period, phy_emp_no, phy_date, phy_job, phy_phase, phy_category, phy_dept_code, phy_gl_acc_code, phy_tran_type, phy_tran_code, phy_batch_num, case when phy_charge_out_amt is not null then phy_charge_out_amt else phy_amount end phy_amount from pyemppayhist where phy_prn_code = 'WK' and phy_hire_flag is null and phy_tran_code||phy_rate_code != 'NWHROTH' and phy_tran_code in ('NWHR','OVHR','DTHR','SEV','OTH','PTO','2000','920','2090','250','412','413') and phy_ppr_year = in_wkyear and phy_ppr_period between in_wkstartpp and in_wkendpp) order by 1,2,3; -- create a instance of the cursor sal_rec output_cursor%ROWTYPE; BEGIN -- SMTP Email Header section conn := bec_mail.begin_mail (sender => 'CMiC ', recipients => user ||'<'||user||'@beckgroup.com>,', subject => sys_context('USERENV','DB_NAME') ||' - '|| 'Payroll Audit Report BW:'||in_bwyear ||' '||in_bwstartpp||'-'||in_bwendpp|| ',WK:'||in_wkyear||' '||in_wkstartpp||'-'||in_wkendpp, mime_type => bec_mail.multipart_mime_type ); -- SMTP email text section bec_mail.attach_text (conn => conn, DATA => '
The attached file includes the payroll audit report .
', mime_type => 'text/html' ); -- SMTP email attachement start section bec_mail.begin_attachment (conn => conn, mime_type => 'application/vnd.ms-excel', inline => FALSE, filename => 'Payroll Audit'||to_char(sysdate, 'MMDDYY') ||'.xls', transfer_enc => '' ); output_line :=(''|| UTL_TCP.CRLF ||''|| UTL_TCP.CRLF ||''|| UTL_TCP.CRLF ||''|| UTL_TCP.CRLF ||''|| UTL_TCP.CRLF ||''|| UTL_TCP.CRLF ||'Sheet1'|| UTL_TCP.CRLF ||''|| UTL_TCP.CRLF || ''|| UTL_TCP.CRLF || ''|| UTL_TCP.CRLF || ''|| UTL_TCP.CRLF ||''|| UTL_TCP.CRLF ||''|| UTL_TCP.CRLF || UTL_TCP.CRLF ); bec_mail.write_text (conn => conn, MESSAGE => output_line ); output_line :=('' || UTL_TCP.CRLF ); bec_mail.write_text (conn => conn, MESSAGE => output_line ); -- Loop thru sal_rec -- First time to gather count of cursor to remove last ctrl feed. FOR sal_rec IN output_cursor LOOP cursor_count:= output_cursor%ROWCOUNT; END LOOP; output_line := ''||''||''|| ''||''||''||''|| ''||''|| ''||''||''||''; bec_mail.write_text (conn => conn, MESSAGE => output_line ); -- Loop thru sal_rec -- Please note the UTL_TCP.CRLF is to place a hard return in email file. FOR sal_rec IN output_cursor LOOP output_line := ''||''||''|| ''||''||''||''|| ''||''|| ''||''||''||''; if (output_cursor%ROWCOUNT=cursor_count) THEN output_line:= output_line || ''; ELSE output_line:= output_line || UTL_TCP.CRLF; end if; -- SMTP write one line to attachment bec_mail.write_text (conn => conn, MESSAGE => output_line ); -- UTL_FILE.PUT_LINE (output_file, output_line); END LOOP; output_line :=('
'||'Company'||''|| 'Period'||''|| 'Emp No'||''|| 'Date'||''|| 'Job'||''|| 'Phase'||''|| 'Category'||''|| 'Dept'||''|| 'Account'||''|| 'Tran Type'||''|| 'Tran Code'||''|| 'Batch'||''|| 'Amount'||'
'||sal_rec.phy_work_comp_code||''|| sal_rec.phy_ppr_period||''|| sal_rec.phy_emp_no||''|| sal_rec.phy_date||''|| sal_rec.phy_job||''|| sal_rec.phy_phase||''|| sal_rec.phy_category||''|| sal_rec.phy_dept_code||''|| sal_rec.phy_gl_acc_code||''|| sal_rec.phy_tran_type||''|| sal_rec.phy_tran_code||''|| sal_rec.phy_batch_num||''|| sal_rec.phy_amount||'
'||UTL_TCP.CRLF|| UTL_TCP.CRLF); bec_mail.write_text (conn => conn, MESSAGE => output_line ); -- Close output file if needed -- UTL_FILE.FCLOSE(output_file); -- Close attachement in email. -- Close email and send it. bec_mail.end_attachment (conn => conn); bec_mail.end_mail (conn => conn); -- End of bec_manhour_email proc END; PROCEDURE bec_payrollwages_email( in_bwyear IN NUMBER, in_bwstartpp IN NUMBER, in_bwendpp IN NUMBER, in_wkyear IN NUMBER, in_wkstartpp IN NUMBER, in_wkendpp IN NUMBER) AS output_path VARCHAR2 (50); output_filename VARCHAR2 (50); output_line VARCHAR2 (1000); r number; -- output_file UTL_FILE.file_type; -- SMTP email variables conn UTL_SMTP.connection; req UTL_HTTP.req; resp UTL_HTTP.resp; DATA RAW (32000); cursor_count number:=0; tab varchar2(2):=CHR(9); dummy number; cursor biweekly_cursor is select ppr_period from pycompayprd where ppr_prn_code = 'BW' and ppr_year = in_bwyear and ppr_period between in_bwstartpp and in_bwendpp order by ppr_period; biweekly_rec biweekly_cursor%ROWTYPE; -- hcc removed per l.dicus 2/29/16, dded back 4/13/2016 cursor weekly_cursor is select ppr_period from pycompayprd where ppr_prn_code = 'WK' and ppr_year = in_wkyear and ppr_period between in_wkstartpp and in_wkendpp order by ppr_period; weekly_rec weekly_cursor%ROWTYPE; cursor output_cursor IS select phy_emp_no, prn_code, name, emp_wrl_code, sum(gross_wages) gross_wages, sum(fed_taxable) fed_taxable, sum(fed_tax) fed_tax, sum(state_taxable) state_taxable, sum(sut_taxable) sut_taxable, sum(imp_life) imp_life, sum(k401) k401, sum(k401_cu) k401_cu, sum(k401_nq) k401_nq, sum(medical) medical, sum(vision) vision, sum(hsa) hsa, sum(emr_hsa) emrhsa, sum(hdhp) hdhp, sum(dental) dental, sum(fsa) fsa, sum(fsa2) fsa2, sum(tob_sur), sum(fica_wages) fica_wages, sum(medi_wages) medi_wages, sum(emp_fica_tax) emp_fica_tax, sum(emp_medi_tax) emp_medi_tax, sum(emr_fica_tax) emr_fica_tax, sum(emr_medi_tax) emr_medi_tax, sum(other) other, emp_trd_code , sum(tob_sur) tob_sur from da.bec_temp_pr group by phy_emp_no, prn_code, name, emp_wrl_code, emp_trd_code order by phy_emp_no; sal_rec output_cursor%ROWTYPE; BEGIN delete from da.bec_temp_pr; commit; r := 1; if in_bwstartpp is not null then FOR biweekly_rec IN biweekly_cursor LOOP insert into da.bec_temp_pr (PHY_EMP_NO,PRN_CODE,PERIOD,NAME,EMP_WRL_CODE,GROSS_WAGES,FED_TAXABLE,STATE_TAXABLE,SUT_TAXABLE,IMP_LIFE, K401,K401_CU,K401_NQ,MEDICAL,VISION,HSA,HDHP,DENTAL,FSA,FSA2, FICA_WAGES,MEDI_WAGES,EMP_FICA_TAX,EMP_MEDI_TAX,EMR_FICA_TAX,EMR_MEDI_TAX,EMR_HSA,OTHER, emp_trd_code,tob_sur, fed_tax ) select phy_emp_no, 'BW', biweekly_rec.ppr_period, emp_last_name||', '||emp_first_name name, emp_wrl_code, max(gross_wages) gross_wages, sum(case when phy_tran_code = 'FED' then phy_gross_earning else 0 end) fed_taxable, sum(case when phy_tran_code = 'STA' then phy_gross_earning else 0 end) state_taxable, sum(case when phy_tran_code = 'SUT' then phy_gross_earning else 0 end) sut_taxable, sum(case when phy_tran_type = 'DE' and phy_tran_code = '1100' then phy_amount else 0 end) imp_life, sum(case when phy_tran_type = 'DE' and phy_tran_code = '1000' and nvl(phy_emplr_contr_flag,'N') = 'N' then phy_amount else 0 end) k401, sum(case when phy_tran_type = 'DE' and phy_tran_code = '1220' then phy_amount else 0 end) k401_cu, sum(case when phy_tran_type = 'DE' and phy_tran_code = '1008' then phy_amount else 0 end) k401_nq, sum(case when phy_tran_type = 'DE' and phy_tran_code = '1055' then phy_amount else 0 end) medical, sum(case when phy_tran_type = 'DE' and phy_tran_code = '1245' then phy_amount else 0 end) vision, sum(case when (phy_tran_type = 'DE' and phy_tran_code = '1290') then phy_amount else 0 end) hsa, -- or (phy_tran_type = 'BN' and phy_tran_code = '405') then phy_amount else 0 end) hsa, sum(case when phy_tran_type = 'DE' and phy_tran_code = '1305' then phy_amount else 0 end) hdhp, sum(case when phy_tran_type = 'DE' and phy_tran_code = '1155' then phy_amount else 0 end) dental, sum(case when phy_tran_type = 'DE' and phy_tran_code = '1255' then phy_amount else 0 end) fsa, sum(case when phy_tran_type = 'DE' and phy_tran_code = '1265' then phy_amount else 0 end) fsa2, sum(case when phy_tran_code='EEFI' then phy_gross_earning else 0 end) fica_wages, sum(case when phy_tran_code='EEMD' then phy_gross_earning else 0 end) medi_wages, sum(case when phy_tran_code='EEFI' then phy_amount else 0 end) emp_fica_tax, sum(case when phy_tran_code='EEMD' then phy_amount else 0 end) emp_medi_tax, sum(case when phy_tran_code='ERFI' then phy_amount else 0 end) emr_fica_tax, sum(case when phy_tran_code='ERMD' then phy_amount else 0 end) emr_medi_tax, sum(case when (phy_tran_type = 'BN' and phy_tran_code = '405') then phy_amount else 0 end) emr_hsa, sum(case when (phy_tran_type = 'BN' and phy_tran_code = '350') then phy_amount else 0 end) other, emp_trd_code, -- hcc 20170302 sum(case when phy_tran_type = 'DE' and phy_tran_code = '1058' then phy_amount else 0 end) tob_sur, -- hcc 20170919 sum(case when phy_tran_code = 'FED' then phy_amount else 0 end) fed_tax from pyemppayhist, pyemployee_table, (select phy_emp_no gross_emp_no, sum(gross_wages) gross_wages from (select phy_emp_no, sum(phy_amount) gross_wages from pyemppayhist where phy_ppr_year = in_bwyear and phy_ppr_period = biweekly_rec.ppr_period and phy_prn_code in ('BW', 'BBON', 'BON', 'EXP', 'EQU', 'OMRP', 'NQ','COM') and phy_tran_code in ('2000','2090','180','240','250','270','290','310','320','340','342', '350', '345','360','380','390','480','485', 'DIS','DTHR','MIL','OVHR','SEV','405', '440' ) --jkb added 440 2201202 and phy_emp_no in (select distinct phy_emp_no from pyemppayhist where phy_ppr_year = in_bwyear and phy_ppr_period = biweekly_rec.ppr_period and phy_hire_flag is not null and phy_tran_type = 'BW') group by phy_emp_no union all select phy_emp_no, sum(phy_pay_amount) taxable from pyemppayhist where phy_ppr_year = in_bwyear and phy_ppr_period = biweekly_rec.ppr_period and phy_prn_code in ('BW', 'BBON', 'BON', 'EXP', 'EQU', 'OMRP', 'NQ','COM') and phy_tran_code in ('NWHR') and phy_hire_flag is not null and phy_emp_no in (select distinct phy_emp_no from pyemppayhist where phy_ppr_year = in_bwyear and phy_ppr_period = biweekly_rec.ppr_period and phy_hire_flag is not null and phy_tran_type = 'BW') group by phy_emp_no union all select phy_emp_no, sum(phy_pay_amount) taxable from pyemppayhist where phy_ppr_year = in_bwyear and phy_ppr_period = biweekly_rec.ppr_period and phy_prn_code in ('BW', 'BBON', 'BON', 'EXP', 'EQU', 'OMRP', 'NQ','COM') and phy_tran_code in ('NWHR') and phy_hire_flag is null and nvl(phy_adjustment,'X') = 'Y' and phy_rate_code != 'OTH' and phy_emp_no in (select distinct phy_emp_no from pyemppayhist where phy_ppr_year = in_bwyear and phy_ppr_period = biweekly_rec.ppr_period and phy_hire_flag is not null and phy_tran_type = 'BW') group by phy_emp_no union all select phy_emp_no, sum(phy_amount) taxable from pyemppayhist where phy_ppr_year = in_bwyear and phy_ppr_period = biweekly_rec.ppr_period and phy_prn_code in ('BW', 'BBON', 'BON', 'EXP', 'EQU', 'OMRP', 'NQ','COM') and phy_tran_code in ('2000','2090','180','240','250','270','290','310','320','340','342', '350', '345','360','380','390','480','485', 'DIS','DTHR','MIL','NWHR','OVHR','PTO','SEV','OTH','405', '440','BERV','JURY' ) --jkb added 440 2201202 and phy_emp_no not in (select distinct phy_emp_no from pyemppayhist where phy_ppr_year = in_bwyear and phy_ppr_period = biweekly_rec.ppr_period and phy_hire_flag is not null and phy_tran_type = 'BW') group by phy_emp_no union all select phy_emp_no, sum(phy_amount) taxable from pyemppayhist where phy_ppr_year = in_bwyear and phy_ppr_period = biweekly_rec.ppr_period and phy_prn_code in ('BW', 'BBON', 'BON', 'EXP', 'EQU', 'OMRP', 'NQ','COM') and phy_tran_code = 'PTO' and ( phy_tran_type <> 'BW' or phy_emp_type='S' ) and nvl(phy_hrs_worked,0) = 0 and phy_adjustment = 'Y' group by phy_emp_no ) group by phy_emp_no) where phy_emp_no = gross_emp_no (+) and phy_emp_no = emp_no and phy_ppr_year = in_bwyear and phy_ppr_period = biweekly_rec.ppr_period and phy_prn_code in ('BW', 'BBON', 'BON', 'EXP', 'EQU', 'OMRP', 'NQ','COM') group by phy_emp_no, biweekly_rec.ppr_period, emp_last_name||', '||emp_first_name, emp_wrl_code, emp_trd_code; END LOOP; end if; -- hcc removed per l.dicus 2/29/16, added back 4/13/2016 if in_wkstartpp is not null then FOR weekly_rec IN weekly_cursor LOOP insert into da.bec_temp_pr (PHY_EMP_NO,PRN_CODE,PERIOD,NAME,EMP_WRL_CODE,GROSS_WAGES,FED_TAXABLE,STATE_TAXABLE,SUT_TAXABLE,IMP_LIFE, K401,K401_CU,K401_NQ,MEDICAL,VISION,HSA,HDHP,DENTAL,FSA,FSA2, FICA_WAGES,MEDI_WAGES,EMP_FICA_TAX,EMP_MEDI_TAX,EMR_FICA_TAX,EMR_MEDI_TAX,EMR_HSA,OTHER, emp_trd_code, tob_sur, fed_tax ) select phy_emp_no, 'WK', weekly_rec.ppr_period, emp_last_name||', '||emp_first_name name, emp_wrl_code, max(gross_wages) gross_wages, sum(case when phy_tran_code = 'FED' then phy_gross_earning else 0 end) fed_taxable, sum(case when phy_tran_code = 'STA' then phy_gross_earning else 0 end) state_taxable, sum(case when phy_tran_code = 'SUT' then phy_gross_earning else 0 end) sut_taxable, sum(case when phy_tran_type = 'DE' and phy_tran_code = '1100' then phy_amount else 0 end) imp_life, sum(case when phy_tran_type = 'DE' and phy_tran_code = '1000' and nvl(phy_emplr_contr_flag,'N') = 'N' then phy_amount else 0 end) k401, sum(case when phy_tran_type = 'DE' and phy_tran_code = '1220' then phy_amount else 0 end) k401_cu, sum(case when phy_tran_type = 'DE' and phy_tran_code = '1008' then phy_amount else 0 end) k401_nq, sum(case when phy_tran_type = 'DE' and phy_tran_code = '1055' then phy_amount else 0 end) medical, sum(case when phy_tran_type = 'DE' and phy_tran_code = '1245' then phy_amount else 0 end) vision, sum(case when phy_tran_type = 'DE' and phy_tran_code = '1290' then phy_amount else 0 end) hsa, sum(case when phy_tran_type = 'DE' and phy_tran_code = '1305' then phy_amount else 0 end) hdhp, sum(case when phy_tran_type = 'DE' and phy_tran_code = '1155' then phy_amount else 0 end) dental, sum(case when phy_tran_type = 'DE' and phy_tran_code = '1255' then phy_amount else 0 end) fsa, sum(case when phy_tran_type = 'DE' and phy_tran_code = '1265' then phy_amount else 0 end) fsa2, sum(case when phy_tran_code='EEFI' then phy_gross_earning else 0 end) fica_wages, sum(case when phy_tran_code='EEMD' then phy_gross_earning else 0 end) medi_wages, sum(case when phy_tran_code='EEFI' then phy_amount else 0 end) emp_fica_tax, sum(case when phy_tran_code='EEMD' then phy_amount else 0 end) emp_medi_tax, sum(case when phy_tran_code='ERFI' then phy_amount else 0 end) emr_fica_tax, sum(case when phy_tran_code='ERMD' then phy_amount else 0 end) emr_medi_tax, sum(case when (phy_tran_type = 'BN' and phy_tran_code = '405') then phy_amount else 0 end) emr_hsa, sum(case when (phy_tran_type = 'BN' and phy_tran_code = '350') then phy_amount else 0 end) other, emp_trd_code, sum(case when phy_tran_type = 'DE' and phy_tran_code = '1058' then phy_amount else 0 end) tob_sur, -- hcc 20170919 sum(case when phy_tran_code = 'FED' then phy_amount else 0 end) fed_tax from pyemppayhist, pyemployee_table, (select phy_emp_no gross_emp_no, sum(gross_wages) gross_wages from (select phy_emp_no, sum(phy_amount) gross_wages from pyemppayhist where phy_ppr_year = in_wkyear and phy_ppr_period = weekly_rec.ppr_period and to_char(phy_actual_pay_date,'yyyy') = in_wkyear and phy_prn_code in ('WK') and phy_tran_code in ('2090','180','250','270','290','310','320','340','342','345','360','380','390','480','485', 'DIS','DTHR','MIL','OVHR','SEV','NWHR') and phy_emp_no in (select distinct phy_emp_no from pyemppayhist where phy_ppr_year = in_wkyear and phy_ppr_period = weekly_rec.ppr_period and to_char(phy_actual_pay_date,'yyyy') = in_wkyear and phy_prn_code in ('WK') and phy_hire_flag is not null and phy_tran_type = 'BW') group by phy_emp_no union all select phy_emp_no, sum(phy_pay_amount) taxable from pyemppayhist where phy_ppr_year = in_wkyear and phy_ppr_period = weekly_rec.ppr_period and to_char(phy_actual_pay_date,'yyyy') = in_wkyear and phy_prn_code in ('WK') and phy_tran_code in ('NWHR') and phy_hire_flag is null and nvl(phy_adjustment,'X') = 'Y' and phy_rate_code != 'OTH' and phy_emp_no in (select distinct phy_emp_no from pyemppayhist where phy_ppr_year = in_wkyear and phy_ppr_period = weekly_rec.ppr_period and to_char(phy_actual_pay_date,'yyyy') = in_wkyear and phy_prn_code in ('WK') and phy_hire_flag is not null and phy_tran_type = 'BW') group by phy_emp_no union all select phy_emp_no, sum(phy_amount) taxable from pyemppayhist where phy_ppr_year = in_wkyear and phy_ppr_period = weekly_rec.ppr_period and to_char(phy_actual_pay_date,'yyyy') = in_wkyear and phy_prn_code in ('WK') and phy_tran_code in ('2090','180','250','270','290','310','320','340','342','345','360','380','390','480','485', 'DIS','DTHR','MIL','NWHR','OVHR','PTO','SEV','OTH','BERV','JURY') and phy_emp_no not in (select distinct phy_emp_no from pyemppayhist where phy_ppr_year = in_wkyear and phy_ppr_period = weekly_rec.ppr_period and to_char(phy_actual_pay_date,'yyyy') = in_wkyear and phy_prn_code in ('WK') and phy_hire_flag is not null and phy_tran_type = 'BW') group by phy_emp_no ) group by phy_emp_no) where phy_emp_no = gross_emp_no (+) and phy_emp_no = emp_no and phy_ppr_year = in_wkyear and phy_ppr_period = weekly_rec.ppr_period and to_char(phy_actual_pay_date,'yyyy') = in_wkyear and phy_prn_code in ('WK') group by phy_emp_no, weekly_rec.ppr_period, emp_last_name||', '||emp_first_name, emp_wrl_code, emp_trd_code; END LOOP; end if; -- SMTP Email Header section conn := bec_mail.begin_mail (sender => 'CMiC ', recipients => user||'<'||user||'@beckgroup.com>,', subject => sys_context('USERENV','DB_NAME') ||' - '||in_bwyear||' Taxable Wages BW '||in_bwstartpp||'-'||in_bwendpp, mime_type => bec_mail.multipart_mime_type ); -- SMTP email text section bec_mail.attach_text (conn => conn, DATA => '
The attached file includes the payroll taxable wages report.
', mime_type => 'text/html' ); -- SMTP email attachement start section bec_mail.begin_attachment (conn => conn, mime_type => 'application/vnd.ms-excel', inline => FALSE, filename => in_bwyear||'_Taxable_Wages_'||to_char(sysdate, 'MMDDYY') ||'.xls', transfer_enc => '' ); output_line :=(''|| UTL_TCP.CRLF ||''|| UTL_TCP.CRLF ||''|| UTL_TCP.CRLF ||''|| UTL_TCP.CRLF ||''|| UTL_TCP.CRLF ||''|| UTL_TCP.CRLF ||'Sheet1'|| UTL_TCP.CRLF ||''|| UTL_TCP.CRLF || ''|| UTL_TCP.CRLF || ''|| UTL_TCP.CRLF || ''|| UTL_TCP.CRLF ||''|| UTL_TCP.CRLF ||''|| UTL_TCP.CRLF || UTL_TCP.CRLF ); bec_mail.write_text (conn => conn, MESSAGE => output_line ); output_line :=('' || UTL_TCP.CRLF ); bec_mail.write_text (conn => conn, MESSAGE => output_line ); FOR sal_rec IN output_cursor LOOP cursor_count:= output_cursor%ROWCOUNT; END LOOP; -- do header output_line := ''|| ''|| ''|| ''|| ''|| ''|| ''|| ''|| ''|| ''|| ''|| ''|| ''|| ''|| ''|| ''|| ''|| ''|| ''|| ''|| ''|| ''|| ''|| ''|| ''|| ''|| ''|| ''|| ''|| ''|| ''|| ''|| ''|| ''|| ''|| ''|| ''|| ''|| ''|| ''|| ''|| ''|| '' ; bec_mail.write_text (conn => conn, MESSAGE => output_line ); FOR sal_rec IN output_cursor LOOP r := r+1; output_line := ''|| ''|| ''|| ''|| ''|| ''|| ''|| ''|| ''|| ''|| ''|| ''|| ''|| ''|| ''|| ''|| ''|| ''|| ''|| ''|| ''|| ''|| ''|| ''|| ''|| ''|| ''|| ''|| ''|| ''|| ''|| ''|| ''|| ''|| ''|| ''|| ''|| ''|| ''|| ''|| ''|| ''|| '' ; if (output_cursor%ROWCOUNT=cursor_count) THEN output_line:= output_line || ''; ELSE output_line:= output_line || UTL_TCP.CRLF; end if; bec_mail.write_text (conn => conn, MESSAGE => output_line ); END LOOP; output_line :=('
'||'Emp No'||''||'Emp Name'||''||'Prn Code'||''||'Work Location'||''||'Gross Wages'||''||'Federal Wages'||''||'State Wages'||''||'SUI Wages'||''||'FICA Wages'||''||'MEDI Wages'||''||'Imp Life 1100'||''||'401K 1000'||''||'401 CU 1220'||''||'401K NQ 1008'||''||'Medical 1055'||''||'Vision 1245'||''||'HSA 1290'||''||'HDHP 1305'||''||'Dental 1155'||''||'FSA 1255'||''||'FSA 1265'||''||'Tob Sur 1058'||''||'Gross - Federal'||''||'Pre-Tax'||''||'Fed Variance'||''||'Emp Fica Tax'||''||'Emp Medi Tax'||''||'Emr Fica Tax'||''||'Emr Medi Tax'||''||'Calc Emp Fica Tax'||''||'Calc Emp Medi Tax'||''||'Calc Emr Fica Tax'||''||'Calc Emr Medi Tax'||''||'Emp Fica Tax Variance'||''||'Emp Medi Tax Variance'||''||'Emr Fica Tax Variance'||''||'Emr Medi Tax Variance'||''||'HSA 405'||''||'Other 350'||''||'Emp Trade Code'||''||'Fed Tax'||'
'||sal_rec.PHY_EMP_NO||''||sal_rec.NAME||''||sal_rec.prn_code||''||sal_rec.emp_wrl_code||''||sal_rec.GROSS_WAGES||''||sal_rec.FED_TAXABLE||''||sal_rec.STATE_TAXABLE||''||sal_rec.SUT_TAXABLE||''||sal_rec.fica_wages||''||sal_rec.medi_wages||''||sal_rec.IMP_LIFE||''||sal_rec.K401||''||sal_rec.K401_CU||''||sal_rec.K401_NQ||''||sal_rec.MEDICAL||''||sal_rec.VISION||''||sal_rec.HSA||''||sal_rec.HDHP||''||sal_rec.DENTAL||''||sal_rec.FSA||''||sal_rec.FSA2||''||sal_rec.TOB_SUR||''||'=E'||r||'-X'||r||'-AM'||r||'-AL'||r||''||'=SUM(L'||r||':V'||r||')'||''||'=ROUND(W'||r||'-F'||r||'+AM'||r||',2)'||''||sal_rec.emp_fica_tax||''||sal_rec.emp_medi_tax||''||sal_rec.emr_fica_tax||''||sal_rec.emr_medi_tax||''||'=ROUND(SUM(I'||r||'*.062'||'),2)'||''||'=IF(J'||r||'<= 200000,ROUND(SUM(J'||r||'*0.0145),2),ROUND(SUM(J'||r||'*0.0145),2)+ROUND(SUM((J'||r||'-200000)*0.009),2))'||''||'=ROUND(SUM(I'||r||'*.062'||'),2)'||''||'=ROUND(SUM(J'||r||'*.0145'||'),2)'||''||'=AD'||r||'-Z'||r||''||'=AE'||r||'-AA'||r||''||'=AF'||r||'-AB'||r||''||'=AG'||r||'-AC'||r||''||sal_rec.EMRHSA||''||sal_rec.OTHER||''||sal_rec.emp_trd_code||''||sal_rec.fed_tax||'
'||UTL_TCP.CRLF|| UTL_TCP.CRLF); bec_mail.write_text (conn => conn, MESSAGE => output_line ); bec_mail.end_attachment (conn => conn); bec_mail.end_mail (conn => conn); END; PROCEDURE bec_vitality_eligibility_file as hold_emp_no varchar2(16); CURSOR emp_cursor is select a.emp_sin_no emp_ss, a.emp_no emp_no, 'BECK' emp_vit_no, b.region_no emp_region, -- to_char(case when case when c.adjservice is null then nvl(a.emp_re_hire_date,a.emp_hire_date) else c.adjservice end <= to_date('101110','MMDDYY') then to_date('101110','MMDDYY') -- else case when c.adjservice is null then nvl(a.emp_re_hire_date,a.emp_hire_date) else c.adjservice end end,'YYYYMMDD') emp_vit_hire_date, to_char(case when nvl(a.emp_re_hire_date,a.emp_hire_date) <= to_date('101110','MMDDYY') then to_date('101110','MMDDYY') else nvl(a.emp_re_hire_date,a.emp_hire_date) end,'YYYYMMDD') emp_vit_hire_date, to_char(case when (a.emp_status = 'A' and c.empsevdate is not null) then c.empsevdate else a.emp_termination_date end,'YYYYMMDD') emp_vit_term_date, to_char(case when c.adjservice is null then nvl(a.emp_re_hire_date,a.emp_hire_date) else c.adjservice end,'YYYYMMDD') emp_hire_date, to_char(case when (a.emp_status = 'A' and c.empsevdate is not null) then c.empsevdate else a.emp_termination_date end,'YYYYMMDD') emp_term_date, substr(a.emp_last_name,1,30) emp_last_name, substr(a.emp_first_name,1,30) emp_first_name, substr(a.emp_middle_name,1,1) emp_middle_initial, case when a.emp_phone is not null then 'HP' else null end emp_phone_indicator, substr(replace(replace(replace(replace(replace(a.emp_phone,'(',''),')',''),'-',''),' ',''),'/',''),1,10) emp_phone, case when a.emp_work_phone is not null then 'WP' else null end emp_work_phone_indicator, substr(replace(replace(replace(replace(replace(a.emp_work_phone,'(',''),')',''),'-',''),' ',''),'/',''),1,10) emp_work_phone, case when a.emp_email_address is not null then 'EM' else null end emp_email_indicator, substr(a.emp_email_address,1,40) emp_email_address, substr(replace(a.emp_ph_address1,',',''),1,40) emp_ph_addr_1, substr(replace(a.emp_ph_address2,',',''),1,40) emp_ph_addr_2, substr(a.emp_ph_address3,1,30) emp_ph_city, a.emp_region_code emp_ph_state, substr(a.emp_ph_zip_code,1,5) emp_ph_zip, to_char(a.emp_date_of_birth,'YYYYMMDD') emp_birth_date, a.emp_sex emp_gender, case when a.emp_marital_status = 'S' then 'I' when a.emp_marital_status in ('W','D') then 'I' else a.emp_marital_status end emp_marital_status, substr(a.emp_address1,1,40) emp_addr_1, substr(a.emp_address2,1,40) emp_addr_2, substr(a.emp_address3,1,30) emp_city, a.emp_state_code emp_state, substr(a.emp_zip_code,1,5) emp_zip FROM pyemployee_table a, uetd_classifier b, uetd_additional c WHERE a.emp_comp_code = b.comp_code (+) and a.emp_home_dept_code = b.department (+) and a.emp_no = c.emp_num (+) and a.emp_prn_code = 'BW' and (a.emp_status = 'A' or a.emp_termination_date > to_date('101110','MMDDYY')) --a.emp_termination_date between to_date(to_char(sysdate,'MM')||'/01/'||to_char(sysdate,'YY'),'MM/DD/YY') and last_day(trunc(sysdate))) ORDER BY a.emp_no; emp_rec emp_cursor%ROWTYPE; CURSOR dep_cursor is select case when edp_rel_code in ('SPOUSE') then '01' else '24' end dep_relation, substr(edp_first_name,1,30) dep_first_name, substr(edp_middle_name,1,1) dep_middle_initial, substr(edp_last_name,1,30) dep_last_name, to_char(edp_date_birth,'YYYYMMDD') dep_birth_date, edp_sex dep_gender, to_char(b.depeligdat,'YYYYMMDD') dep_elig_date, to_char(b.deptermdat,'YYYYMMDD') dep_term_date, null dep_emp_hire_date, null dep_emp_term_date, case when edp_email_address is not null then 'EM' else null end dep_email_indicator, substr(edp_email_address,1,40) dep_email_address FROM hremrelatives, pyemployee_table, uetd_additional a, uetd_depbeninfo b WHERE employee_emp_no_working_as = emp_no (+) and emp_no = a.emp_num (+) and employee_emp_no = hold_emp_no and -- edp_medical_supp_flag = 1 and employee_emp_no = b.emp_num (+) and edp_seq_no = b.hrrelnum (+) and b.depwellnes = 'Yes'; dep_rec dep_cursor%ROWTYPE; output_path VARCHAR2 (50); output_line VARCHAR2 (4000); conn UTL_SMTP.connection; req UTL_HTTP.req; resp UTL_HTTP.resp; DATA RAW (20000); BEGIN conn := bec_mail.begin_mail (sender => 'cmic@beckgroup.com', recipients => USER|| '<'||USER|| '@beckgroup.com>,', -- || '"Alan Golden"', subject => sys_context('USERENV','DB_NAME') ||' - '|| 'Vitality Eligibility File', mime_type => bec_mail.multipart_mime_type ); -- SMTP email text section bec_mail.attach_text (conn => conn, -- DATA => '
The attached file includes the Vitality Eligibility File in CSV Format.
', DATA => 'The attached file includes the Vitality Eligibility File in CSV Format', mime_type => 'text/html' ); -- SMTP email attachement start section bec_mail.begin_attachment (conn => conn, mime_type => 'text/plain', inline => FALSE, filename => 'beck'||to_char(sysdate, 'MMDDYY') ||'.csv', transfer_enc => ''); output_line := '"'||'Employee SSN'||'","'||'Employee ID'||'","'||'Employer ID' ||'","'||'Branch ID'||'","'||'Vitality Coverage Effective date'||'","'||'Vitality Coverage Termination date' ||'","'||'Employment Start Date'||'","'||'Employment End Date'||'","'||'Last Name' ||'","'||'First Name'||'","'||'Middle Initial'||'","'||'Home Phone Indicator' ||'","'||'Home Phone Number'||'","'||'Work Phone Indicator' ||'","'||'Work Phone Number'||'","'||'Email Address Indicator'||'","'||'E-mail address' ||'","'||'Residence Address1'||'","'||'Residence Address2'||'","'||'Residence City' ||'","'||'Residence State'||'","'||'Residence Zip Code' ||'","'||'Date of Birth'||'","'||'Gender'||'","'||'Marital Status' ||'","'||'Mailing Address1'||'","'||'Mailing Address2' ||'","'||'Mailing City'||'","'||'Mailing State' ||'","'||'Mailing Zip' ||'","Dep 1 Relationship Code","Dep 1 Last Name","Dep 1 First Name","Dep 1 Middle Initial","Dep 1 Date of Birth","Dep 1 Gender","Dep 1 Vitality Coverage Effective date","Dep 1 Vitality Coverage Termination date","Dep 1 Employment Start date","Dep 1 Employment End date","Dep 1 Email address Indicator","Dep 1 Email Address",' ||'","Dep 2 Relationship Code","Dep 2 Last Name","Dep 2 First Name","Dep 2 Middle Initial","Dep 2 Date of Birth","Dep 2 Gender","Dep 2 Vitality Coverage Effective date","Dep 2 Vitality Coverage Termination date","Dep 2 Employment Start date","Dep 2 Employment End date","Dep 2 Email address Indicator","Dep 2 Email Address",' ||'","Dep 3 Relationship Code","Dep 3 Last Name","Dep 3 First Name","Dep 3 Middle Initial","Dep 3 Date of Birth","Dep 3 Gender","Dep 3 Vitality Coverage Effective date","Dep 3 Vitality Coverage Termination date","Dep 3 Employment Start date","Dep 3 Employment End date","Dep 3 Email address Indicator","Dep 3 Email Address",' ||'","Dep 4 Relationship Code","Dep 4 Last Name","Dep 4 First Name","Dep 4 Middle Initial","Dep 4 Date of Birth","Dep 4 Gender","Dep 4 Vitality Coverage Effective date","Dep 4 Vitality Coverage Termination date","Dep 4 Employment Start date","Dep 4 Employment End date","Dep 4 Email address Indicator","Dep 4 Email Address",' ||'","Dep 5 Relationship Code","Dep 5 Last Name","Dep 5 First Name","Dep 5 Middle Initial","Dep 5 Date of Birth","Dep 5 Gender","Dep 5 Vitality Coverage Effective date","Dep 5 Vitality Coverage Termination date","Dep 5 Employment Start date","Dep 5 Employment End date","Dep 5 Email address Indicator","Dep 5 Email Address",' ||'","Dep 6 Relationship Code","Dep 6 Last Name","Dep 6 First Name","Dep 6 Middle Initial","Dep 6 Date of Birth","Dep 6 Gender","Dep 6 Vitality Coverage Effective date","Dep 6 Vitality Coverage Termination date","Dep 6 Employment Start date","Dep 6 Employment End date","Dep 6 Email address Indicator","Dep 6 Email Address",' ||'","Dep 7 Relationship Code","Dep 7 Last Name","Dep 7 First Name","Dep 7 Middle Initial","Dep 7 Date of Birth","Dep 7 Gender","Dep 7 Vitality Coverage Effective date","Dep 7 Vitality Coverage Termination date","Dep 7 Employment Start date","Dep 7 Employment End date","Dep 7 Email address Indicator","Dep 7 Email Address",' ||'","Dep 8 Relationship Code","Dep 8 Last Name","Dep 8 First Name","Dep 8 Middle Initial","Dep 8 Date of Birth","Dep 8 Gender","Dep 8 Vitality Coverage Effective date","Dep 8 Vitality Coverage Termination date","Dep 8 Employment Start date","Dep 8 Employment End date","Dep 8 Email address Indicator","Dep 8 Email Address",' ||'","Dep 9 Relationship Code","Dep 9 Last Name","Dep 9 First Name","Dep 9 Middle Initial","Dep 9 Date of Birth","Dep 9 Gender","Dep 9 Vitality Coverage Effective date","Dep 9 Vitality Coverage Termination date","Dep 9 Employment Start date","Dep 9 Employment End date","Dep 9 Email address Indicator","Dep 9 Email Address",' ||'","Dep 10 Relationship Code","Dep 10 Last Name","Dep 10 First Name","Dep 10 Middle Initial","Dep 10 Date of Birth","Dep 10 Gender","Dep 10 Vitality Coverage Effective date","Dep 10 Vitality Coverage Termination date","Dep 10 Employment Start date","Dep 10 Employment End date","Dep 10 Email address Indicator","Dep 10 Email Address"' ||UTL_TCP.CRLF; bec_mail.write_text (conn => conn, MESSAGE => output_line); FOR emp_rec IN emp_cursor LOOP hold_emp_no := emp_rec.emp_no; output_line := '"'||lpad(emp_rec.emp_ss,'9','0')||'","'||emp_rec.emp_no||'","'||emp_rec.emp_vit_no ||'","'||emp_rec.emp_region||'","'||emp_rec.emp_vit_hire_date||'","'||emp_rec.emp_vit_term_date ||'","'||emp_rec.emp_hire_date||'","'||emp_rec.emp_term_date||'","'||emp_rec.emp_last_name ||'","'||emp_rec.emp_first_name||'","'||emp_rec.emp_middle_initial||'","'||emp_rec.emp_phone_indicator ||'","'||emp_rec.emp_phone||'","'||emp_rec.emp_work_phone_indicator ||'","'||emp_rec.emp_work_phone||'","'||emp_rec.emp_email_indicator||'","'||emp_rec.emp_email_address ||'","'||emp_rec.emp_addr_1||'","'||emp_rec.emp_addr_2||'","'||emp_rec.emp_city ||'","'||emp_rec.emp_state||'","'||emp_rec.emp_zip ||'","'||emp_rec.emp_birth_date||'","'||emp_rec.emp_gender||'","'||emp_rec.emp_marital_status ||'","'||emp_rec.emp_addr_1||'","'||emp_rec.emp_addr_2 ||'","'||emp_rec.emp_city||'","'||emp_rec.emp_state ||'","'||emp_rec.emp_zip; FOR dep_rec in dep_cursor LOOP output_line := output_line||'","'||dep_rec.dep_relation||'","'||dep_rec.dep_last_name||'","'||dep_rec.dep_first_name||'","'||dep_rec.dep_middle_initial||'","'||dep_rec.dep_birth_date||'","'||dep_rec.dep_gender||'","' ||dep_rec.dep_elig_date||'","'||dep_rec.dep_term_date||'","'||dep_rec.dep_emp_hire_date||'","'||dep_rec.dep_emp_term_date||'","'||dep_rec.dep_email_indicator||'","'||dep_rec.dep_email_address; END LOOP; output_line := output_line||'"'||UTL_TCP.CRLF; bec_mail.write_text (conn => conn, MESSAGE => output_line); END LOOP; bec_mail.end_attachment (conn =>conn); bec_mail.end_mail (conn => conn); END; -- End of Vitality Eligibility File PROCEDURE etime_correction_email( in_year IN NUMBER, in_period IN NUMBER ) AS -- output_file UTL_FILE.file_type; -- SMTP email variables conn UTL_SMTP.connection; req UTL_HTTP.req; resp UTL_HTTP.resp; DATA RAW (20000); cursor_count number:=0; tab varchar2(2):=CHR(9); CURSOR emp_cursor IS SELECT emp_no, emp_first_name||' '||emp_last_name AS NAME, emp_home_dept_code, CASE WHEN emp_email_address LIKE '%@%' THEN emp_email_address ELSE emp_email_address||'@beckgroup.com' END AS emp_email_address, NVL(aa.total_hours,0) AS total_hours, NVL(bb.total_hours,0) AS total_hours_not_submitted FROM PYEMPLOYEE_TABLE, (SELECT tsh_emp_no, SUM(NVL(tsh_day_01,0) + NVL(tsh_day_02,0) + NVL(tsh_day_03,0) + NVL(tsh_day_04,0) + NVL(tsh_day_05,0) + NVL(tsh_day_06,0) + NVL(tsh_day_07,0) + NVL(tsh_day_08,0) + NVL(tsh_day_09,0) + NVL(tsh_day_10,0) + NVL(tsh_day_11,0) + NVL(tsh_day_12,0) + NVL(tsh_day_13,0) + NVL(tsh_day_14,0)) AS total_hours FROM PYEMPTIMSHT_CERT WHERE tsh_ppr_period = in_period AND tsh_ppr_year = in_year AND tsh_prn_code IN ('BW') GROUP BY tsh_emp_no) aa, (SELECT tsh_emp_no, SUM(NVL(tsh_day_01,0) + NVL(tsh_day_02,0) + NVL(tsh_day_03,0) + NVL(tsh_day_04,0) + NVL(tsh_day_05,0) + NVL(tsh_day_06,0) + NVL(tsh_day_07,0) + NVL(tsh_day_08,0) + NVL(tsh_day_09,0) + NVL(tsh_day_10,0) + NVL(tsh_day_11,0) + NVL(tsh_day_12,0) + NVL(tsh_day_13,0) + NVL(tsh_day_14,0)) AS total_hours FROM PYEMPTIMSHT_CERT WHERE tsh_ppr_period = in_period AND tsh_ppr_year = in_year AND tsh_prn_code IN ('BW') AND NVL(tsh_submit_flag,'N') not in ('Y','R') GROUP BY tsh_emp_no) bb WHERE emp_no = aa.tsh_emp_no (+) AND emp_no = bb.tsh_emp_no (+) AND emp_pyg_code IN ('BW03','PT03') AND emp_status = 'A' and emp_dept_code <> 'FURL' --jkb added 20200416 AND (NVL(aa.total_hours,0) < 80 OR NVL(bb.total_hours,0) <> 0) ORDER BY emp_email_address; -- create a instance of the cursor emp_rec PYEMPLOYEE_TABLE%ROWTYPE; l_maicon utl_smtp.connection; email_address VARCHAR2 (100); emp_list VARCHAR2 (8000); emp_list2 VARCHAR2 (8000); emp_list3 VARCHAR2 (8000); hold_emp_no VARCHAR2 (16); hold_email_address VARCHAR2 (100); output_path VARCHAR2 (50); output_filename VARCHAR2 (20); output_header VARCHAR2 (312); output_line VARCHAR2 (255); output_file UTL_FILE.FILE_TYPE; hold_error VARCHAR2 (100); BEGIN -- output_filename := 'E-TIME.txt'; -- output_file := UTL_FILE.FOPEN ('/transfer', output_filename, 'w'); output_header := 'Emp ID Name Hours Entered Hours Not Submitted'; FOR emp_rec IN emp_cursor LOOP email_address := emp_rec.emp_email_address; IF SUBSTR(email_address,1,1) = '@' THEN hold_error := 'E-Mail Address Missing: '||emp_rec.NAME; DBMS_OUTPUT.PUT_LINE(hold_error); GOTO bypass_employee; END IF; l_maicon := utl_smtp.open_connection('localhost'); utl_smtp.helo(l_maicon,'hostname'); utl_smtp.mail(l_maicon,'oracle@beckgroup.com'); utl_smtp.rcpt(l_maicon,email_address); -- utl_smtp.rcpt(l_maicon,'henryclark@beckgroup.com'); utl_smtp.DATA(l_maicon,'From: cmic@beckgroup.com' || utl_tcp.crlf || 'To:'||email_address|| utl_tcp.crlf || 'Subject: ' || sys_context('USERENV','DB_NAME') ||' - '|| 'Correction!, - CMIC E-Time due by Wednesday Dec 22nd ! ' || utl_tcp.crlf || 'Our records at ' || to_char(sysdate,'HH:MM') ||' indicate you have only entered '||NVL(emp_rec.total_hours,0)||' hours for the current pay period of which '||NVL(emp_rec.total_hours_not_submitted,0)||' have not been submitted.'||utl_tcp.crlf|| 'Please make additional entries (if necessary) to prevent any delay in the processing of your paycheck. '||utl_tcp.crlf||utl_tcp.crlf||utl_tcp.crlf||utl_tcp.crlf||utl_tcp.crlf|| 'This is an automated message - please do not respond to sender'||utl_tcp.crlf||utl_tcp.crlf|| 'This message will be automatically sent, as a reminder, to each user who has not entered at least 80 hours.'||utl_tcp.crlf|| 'You must complete the entry of your time by entered and submitted by Wednesday at midnight.' ||utl_tcp.crlf||utl_tcp.crlf|| 'If you have any questions please email payroll@beckgroup.com'||utl_tcp.crlf||utl_tcp.crlf|| 'Use this link to navigate to E-Time and enter timecards ----> http://erp.beckgroup.com'||utl_tcp.crlf); utl_smtp.quit(l_maicon); <> emp_list := emp_list||emp_rec.emp_no||' '||RPAD(INITCAP(emp_rec.NAME),40)||' '||TO_CHAR(NVL(emp_rec.total_hours,0),999.99)||' '||TO_CHAR(NVL(emp_rec.total_hours_not_submitted,0),999.99)||utl_tcp.crlf; IF LENGTH(emp_list) > 7000 THEN emp_list3 := emp_list2; emp_list2 := emp_list; emp_list := NULL; END IF; BEGIN SELECT emp_no INTO hold_emp_no FROM UETD_CLASSIFIER WHERE department = emp_rec.emp_home_dept_code AND comp_code = '03' and emp_no not in ('113822','113842','113664'); EXCEPTION WHEN NO_DATA_FOUND THEN hold_emp_no := '118911'; END; BEGIN SELECT CASE WHEN emp_email_address LIKE '%@%' THEN emp_email_address ELSE emp_email_address||'@beckgroup.com' END INTO hold_email_address FROM PYEMPLOYEE_TABLE WHERE emp_no = hold_emp_no; EXCEPTION WHEN NO_DATA_FOUND THEN hold_emp_no := '118911'; END; IF SUBSTR(hold_email_address,1,1) = '@' THEN hold_error := 'Department E-Mail Address Missing: '||emp_rec.emp_home_dept_code; DBMS_OUTPUT.PUT_LINE(hold_error); GOTO bypass_department; END IF; --This is hard coded as the Phoenix office has employees from California and Phoenix -- l_maicon := utl_smtp.open_connection('localhost'); utl_smtp.helo(l_maicon,'hostname'); utl_smtp.mail(l_maicon,'oracle@beckgroup.com'); --utl_smtp.rcpt(l_maicon,'henryclark@beckgroup.com'); utl_smtp.rcpt(l_maicon,hold_email_address); utl_smtp.DATA(l_maicon,'From: cmic@beckgroup.com' || utl_tcp.crlf || 'To:'||hold_email_address|| utl_tcp.crlf || 'Subject: ' || sys_context('USERENV','DB_NAME') ||' - '|| 'Correction!, - CMIC E-Time due by Wednesday Dec 22nd !' || utl_tcp.crlf || 'Our records at ' || to_char(sysdate,'HH:MM') ||' indicate '||emp_rec.NAME||' only entered '||NVL(emp_rec.total_hours,0)||' hours for the current pay period. If this is not correct please have the employee complete the entry of their time to prevent'||utl_tcp.crlf|| 'any delay in the processing of their paycheck. '||utl_tcp.crlf||utl_tcp.crlf||utl_tcp.crlf||utl_tcp.crlf||utl_tcp.crlf|| 'This is an automated message - please do not respond to sender'||utl_tcp.crlf||utl_tcp.crlf|| 'This message will be automatically sent, as a reminder, to each user who has not entered at least 80 hours.'||utl_tcp.crlf|| 'They must complete the entry of their time by Wednesday at midnight.'||utl_tcp.crlf||utl_tcp.crlf|| 'If you have any questions please email payroll@beckgroup.com'||utl_tcp.crlf); utl_smtp.quit(l_maicon); <> output_line := email_address||' '||hold_email_address; END LOOP; -- l_maicon := utl_smtp.open_connection('localhost'); -- utl_smtp.helo(l_maicon,'hostname'); -- utl_smtp.mail(l_maicon,'oracle@beckgroup.com'); -- utl_smtp.rcpt(l_maicon,'lindajohnson@beckgroup.com'); --utl_smtp.rcpt(l_maicon,'henryclark@beckgroup.com'); -- utl_smtp.DATA(l_maicon,'From: cmic@beckgroup.com' || utl_tcp.crlf || -- 'To:'||'lindajohnson@beckgroup.com'|| utl_tcp.crlf || -- 'Subject: Employees Who Entered Less Than 80 Hours - Please Read !!!! '|| to_char(sysdate,'HH:MM') || utl_tcp.crlf || -- 'This message will be automatically sent, as a reminder, to each user who has not entered at least 80 hours on the last Friday of the payroll period.'||utl_tcp.crlf||utl_tcp.crlf|| -- output_header||utl_tcp.crlf|| -- RTRIM(emp_list)|| -- RTRIM(emp_list2)|| -- RTRIM(emp_list3)||utl_tcp.crlf); -- utl_smtp.quit(l_maicon); l_maicon := utl_smtp.open_connection('localhost'); utl_smtp.helo(l_maicon,'hostname'); utl_smtp.mail(l_maicon,'cmicalert@beckgroup.com'); utl_smtp.rcpt(l_maicon,'itstaff@beckgroup.com'); utl_smtp.DATA(l_maicon,'From: cmicalert@beckgroup.com' || utl_tcp.crlf || 'To:'||'itstaff@beckgroup.com'|| utl_tcp.crlf || 'Subject: ' || sys_context('USERENV','DB_NAME') ||' - '|| 'Employees Who Entered Less Than 80 Hours - Please Read !!!! '|| to_char(sysdate,'HH:MM') || utl_tcp.crlf || 'This message will be automatically sent, as a reminder, to each user who has not entered at least 80 hours on the last Friday of the payroll period.'||utl_tcp.crlf||utl_tcp.crlf|| output_header||utl_tcp.crlf|| RTRIM(emp_list)|| RTRIM(emp_list2)|| RTRIM(emp_list3)||utl_tcp.crlf); utl_smtp.quit(l_maicon); END; PROCEDURE bec_vitality_update_file ( in_list IN NUMBER ) as hold_emp_no varchar2(16); CURSOR emp_cursor is select a.emp_sin_no emp_ss, a.emp_no emp_no, 'BECK' emp_vit_no, b.region_no emp_region, to_char(case when case when c.adjservice is null then nvl(a.emp_re_hire_date,a.emp_hire_date) else c.adjservice end <= to_date('101110','MMDDYY') then to_date('101110','MMDDYY') else case when c.adjservice is null then nvl(a.emp_re_hire_date,a.emp_hire_date) else c.adjservice end end,'YYYYMMDD') emp_vit_hire_date, to_char(case when (a.emp_status = 'A' and c.empsevdate is not null) then c.empsevdate else a.emp_termination_date end,'YYYYMMDD') emp_vit_term_date, to_char(case when c.adjservice is null then nvl(a.emp_re_hire_date,a.emp_hire_date) else c.adjservice end,'YYYYMMDD') emp_hire_date, to_char(case when (a.emp_status = 'A' and c.empsevdate is not null) then c.empsevdate else a.emp_termination_date end,'YYYYMMDD') emp_term_date, substr(a.emp_last_name,1,30) emp_last_name, substr(a.emp_first_name,1,30) emp_first_name, substr(a.emp_middle_name,1,1) emp_middle_initial, case when a.emp_phone is not null then 'HP' else null end emp_phone_indicator, substr(replace(replace(replace(replace(replace(a.emp_phone,'(',''),')',''),'-',''),' ',''),'/',''),1,10) emp_phone, case when a.emp_work_phone is not null then 'WP' else null end emp_work_phone_indicator, substr(replace(replace(replace(replace(replace(a.emp_work_phone,'(',''),')',''),'-',''),' ',''),'/',''),1,10) emp_work_phone, case when a.emp_email_address is not null then 'EM' else null end emp_email_indicator, substr(a.emp_email_address,1,40) emp_email_address, substr(replace(a.emp_ph_address1,',',''),1,40) emp_ph_addr_1, substr(replace(a.emp_ph_address2,',',''),1,40) emp_ph_addr_2, substr(a.emp_ph_address3,1,30) emp_ph_city, a.emp_region_code emp_ph_state, substr(a.emp_ph_zip_code,1,5) emp_ph_zip, to_char(a.emp_date_of_birth,'YYYYMMDD') emp_birth_date, a.emp_sex emp_gender, case when a.emp_marital_status = 'S' then 'I' when a.emp_marital_status in ('W','D') then 'I' else a.emp_marital_status end emp_marital_status, substr(a.emp_address1,1,40) emp_addr_1, substr(a.emp_address2,1,40) emp_addr_2, substr(a.emp_address3,1,30) emp_city, a.emp_state_code emp_state, substr(a.emp_zip_code,1,5) emp_zip FROM pyemployee_table a, uetd_classifier b, uetd_additional c WHERE a.emp_comp_code = b.comp_code (+) and a.emp_home_dept_code = b.department (+) and a.emp_no = c.emp_num (+) and a.emp_no in in_list and a.emp_prn_code = 'BW' and (a.emp_status = 'A' or a.emp_termination_date between to_date(to_char(sysdate,'MM')||'/01/'||to_char(sysdate,'YY'),'MM/DD/YY') and last_day(trunc(sysdate))) ORDER BY a.emp_no; emp_rec emp_cursor%ROWTYPE; output_path VARCHAR2 (50); output_line VARCHAR2 (4000); conn UTL_SMTP.connection; req UTL_HTTP.req; resp UTL_HTTP.resp; DATA RAW (20000); BEGIN conn := bec_mail.begin_mail (sender => 'cmic@beckgroup.com', recipients => USER|| '<'||USER|| '@beckgroup.com>,', -- || '"Henry Clark"', subject => sys_context('USERENV','DB_NAME') ||' - '|| 'Vitality Update File', mime_type => bec_mail.multipart_mime_type ); -- SMTP email text section bec_mail.attach_text (conn => conn, -- DATA => '
The attached file includes the Vitality Eligibility File in CSV Format.
', DATA => 'The attached file includes the Vitality Update File in CSV Format', mime_type => 'text/html' ); -- SMTP email attachement start section bec_mail.begin_attachment (conn => conn, mime_type => 'text/plain', inline => FALSE, filename => 'beck'||to_char(sysdate, 'MMDDYY') ||'.csv', transfer_enc => ''); output_line := '"'||'Employee SSN'||'","'||'Employee ID'||'","'||'Employer ID' ||'","'||'Branch ID'||'","'||'Vitality Coverage Effective date'||'","'||'Vitality Coverage Termination date' ||'","'||'Employment Start Date'||'","'||'Employment End Date'||'","'||'Last Name' ||'","'||'First Name'||'","'||'Middle Initial'||'","'||'Home Phone Indicator' ||'","'||'Home Phone Number'||'","'||'Work Phone Indicator' ||'","'||'Work Phone Number'||'","'||'Email Address Indicator'||'","'||'E-mail address' ||'","'||'Residence Address1'||'","'||'Residence Address2'||'","'||'Residence City' ||'","'||'Residence State'||'","'||'Residence Zip Code' ||'","'||'Date of Birth'||'","'||'Gender'||'","'||'Marital Status' ||'","'||'Mailing Address1'||'","'||'Mailing Address2' ||'","'||'Mailing City'||'","'||'Mailing State' ||'","'||'Mailing Zip' ||'","Dep 1 Relationship Code","Dep 1 Last Name","Dep 1 First Name","Dep 1 Middle Initial","Dep 1 Date of Birth","Dep 1 Gender","Dep 1 Vitality Coverage Effective date","Dep 1 Vitality Coverage Termination date","Dep 1 Employment Start date","Dep 1 Employment End date","Dep 1 Email address Indicator","Dep 1 Email Address",' ||'","Dep 2 Relationship Code","Dep 2 Last Name","Dep 2 First Name","Dep 2 Middle Initial","Dep 2 Date of Birth","Dep 2 Gender","Dep 2 Vitality Coverage Effective date","Dep 2 Vitality Coverage Termination date","Dep 2 Employment Start date","Dep 2 Employment End date","Dep 2 Email address Indicator","Dep 2 Email Address",' ||'","Dep 3 Relationship Code","Dep 3 Last Name","Dep 3 First Name","Dep 3 Middle Initial","Dep 3 Date of Birth","Dep 3 Gender","Dep 3 Vitality Coverage Effective date","Dep 3 Vitality Coverage Termination date","Dep 3 Employment Start date","Dep 3 Employment End date","Dep 3 Email address Indicator","Dep 3 Email Address",' ||'","Dep 4 Relationship Code","Dep 4 Last Name","Dep 4 First Name","Dep 4 Middle Initial","Dep 4 Date of Birth","Dep 4 Gender","Dep 4 Vitality Coverage Effective date","Dep 4 Vitality Coverage Termination date","Dep 4 Employment Start date","Dep 4 Employment End date","Dep 4 Email address Indicator","Dep 4 Email Address",' ||'","Dep 5 Relationship Code","Dep 5 Last Name","Dep 5 First Name","Dep 5 Middle Initial","Dep 5 Date of Birth","Dep 5 Gender","Dep 5 Vitality Coverage Effective date","Dep 5 Vitality Coverage Termination date","Dep 5 Employment Start date","Dep 5 Employment End date","Dep 5 Email address Indicator","Dep 5 Email Address",' ||'","Dep 6 Relationship Code","Dep 6 Last Name","Dep 6 First Name","Dep 6 Middle Initial","Dep 6 Date of Birth","Dep 6 Gender","Dep 6 Vitality Coverage Effective date","Dep 6 Vitality Coverage Termination date","Dep 6 Employment Start date","Dep 6 Employment End date","Dep 6 Email address Indicator","Dep 6 Email Address",' ||'","Dep 7 Relationship Code","Dep 7 Last Name","Dep 7 First Name","Dep 7 Middle Initial","Dep 7 Date of Birth","Dep 7 Gender","Dep 7 Vitality Coverage Effective date","Dep 7 Vitality Coverage Termination date","Dep 7 Employment Start date","Dep 7 Employment End date","Dep 7 Email address Indicator","Dep 7 Email Address",' ||'","Dep 8 Relationship Code","Dep 8 Last Name","Dep 8 First Name","Dep 8 Middle Initial","Dep 8 Date of Birth","Dep 8 Gender","Dep 8 Vitality Coverage Effective date","Dep 8 Vitality Coverage Termination date","Dep 8 Employment Start date","Dep 8 Employment End date","Dep 8 Email address Indicator","Dep 8 Email Address",' ||'","Dep 9 Relationship Code","Dep 9 Last Name","Dep 9 First Name","Dep 9 Middle Initial","Dep 9 Date of Birth","Dep 9 Gender","Dep 9 Vitality Coverage Effective date","Dep 9 Vitality Coverage Termination date","Dep 9 Employment Start date","Dep 9 Employment End date","Dep 9 Email address Indicator","Dep 9 Email Address",' ||'","Dep 10 Relationship Code","Dep 10 Last Name","Dep 10 First Name","Dep 10 Middle Initial","Dep 10 Date of Birth","Dep 10 Gender","Dep 10 Vitality Coverage Effective date","Dep 10 Vitality Coverage Termination date","Dep 10 Employment Start date","Dep 10 Employment End date","Dep 10 Email address Indicator","Dep 10 Email Address"' ||UTL_TCP.CRLF; bec_mail.write_text (conn => conn, MESSAGE => output_line); FOR emp_rec IN emp_cursor LOOP hold_emp_no := emp_rec.emp_no; output_line := '"'||lpad(emp_rec.emp_ss,'9','0')||'","'||emp_rec.emp_no||'","'||emp_rec.emp_vit_no ||'","'||emp_rec.emp_region||'","'||emp_rec.emp_vit_hire_date||'","'||emp_rec.emp_vit_term_date ||'","'||emp_rec.emp_hire_date||'","'||emp_rec.emp_term_date||'","'||emp_rec.emp_last_name ||'","'||emp_rec.emp_first_name||'","'||emp_rec.emp_middle_initial||'","'||emp_rec.emp_phone_indicator ||'","'||emp_rec.emp_phone||'","'||emp_rec.emp_work_phone_indicator ||'","'||emp_rec.emp_work_phone||'","'||emp_rec.emp_email_indicator||'","'||emp_rec.emp_email_address ||'","'||emp_rec.emp_addr_1||'","'||emp_rec.emp_addr_2||'","'||emp_rec.emp_city ||'","'||emp_rec.emp_state||'","'||emp_rec.emp_zip ||'","'||emp_rec.emp_birth_date||'","'||emp_rec.emp_gender||'","'||emp_rec.emp_marital_status ||'","'||emp_rec.emp_addr_1||'","'||emp_rec.emp_addr_2 ||'","'||emp_rec.emp_city||'","'||emp_rec.emp_state ||'","'||emp_rec.emp_zip; output_line := output_line||'"'||UTL_TCP.CRLF; bec_mail.write_text (conn => conn, MESSAGE => output_line); END LOOP; bec_mail.end_attachment (conn =>conn); bec_mail.end_mail (conn => conn); END; -- End of Vitality UPDATE File PROCEDURE bec_empdeptchg_file ( empyear IN NUMBER, empperiod IN NUMBER ) as hold_emp_no varchar2(16); CURSOR empdeptchg_cursor is select b.emp_comp_code emp_comp_code, a.emp_no emp_no, B.EMP_NAME emp_name, B.emp_prn_code prn_code, a.emp_trd_code emp_trade, b.emp_job_code emp_job_code, b.emp_job_name emp_job_name, c.percent_complete emp_pct_complete, c.start_date emp_start_date, c.projected_completion_date emp_projected_completion_date, c.projectcit emp_project_city FROM pyemployee_table a, bec_emp_chargeout_detail b, (select job_comp_code, job_code, projectcit, (case when job_est_start_date is not null then job_est_start_date else pmp_start_date end) start_date, (case when job_est_compl_date is not null then job_est_compl_date else pmp_end_date end) + nvl(case when da.bec_job_status_pk.schedule_days_override(job_comp_code,job_code,'OWNER') is not null then da.bec_job_status_pk.schedule_days_override(job_comp_code,job_code,'OWNER') else da.bec_job_status_pk.schedule_days(pmp_comp_code, pmp_proj_code,'OWNER') end,0) + nvl(case when da.bec_job_status_pk.schedule_days_override(job_comp_code,job_code,'PENDING') is not null then da.bec_job_status_pk.schedule_days_override(job_comp_code,job_code,'PENDING') else da.bec_job_status_pk.schedule_days(pmp_comp_code, pmp_proj_code,'PENDING') end,0) projected_completion_date, case when case when job_est_compl_date is not null then job_est_compl_date else pmp_end_date end + (case when da.bec_job_status_pk.schedule_days_override(job_comp_code,job_code,'OWNER') is not null then da.bec_job_status_pk.schedule_days_override(job_comp_code,job_code,'OWNER') else da.bec_job_status_pk.schedule_days(pmp_comp_code, pmp_proj_code,'OWNER') end) - case when job_est_start_date is not null then job_est_start_date else pmp_start_date end = 0 then 0 else round((case when nvl(case when da.bec_job_status_pk.schedule_date_override(job_comp_code,job_code,'SCH_FINAL') is not null then da.bec_job_status_pk.schedule_date_override(job_comp_code,job_code,'SCH_FINAL') else da.bec_job_status_pk.schedule_date(pmp_proj_oraseq,'SCH_FINAL') end,TRUNC(sysdate)) >= TRUNC(sysdate) THEN (TRUNC(sysdate) - case when job_est_start_date is not null then job_est_start_date else pmp_start_date end) ELSE case when da.bec_job_status_pk.schedule_date_override(job_comp_code,job_code,'SCH_FINAL') is not null then da.bec_job_status_pk.schedule_date_override(job_comp_code,job_code,'SCH_FINAL') else da.bec_job_status_pk.schedule_date(pmp_proj_oraseq,'SCH_FINAL') end - case when job_est_start_date is not null then job_est_start_date else pmp_start_date end END) / (case when job_est_compl_date is not null then job_est_compl_date else pmp_end_date end + (case when da.bec_job_status_pk.schedule_days_override(job_comp_code,job_code,'OWNER') is not null then da.bec_job_status_pk.schedule_days_override(job_comp_code,job_code,'OWNER') else da.bec_job_status_pk.schedule_days(pmp_comp_code, pmp_proj_code,'OWNER') end) - case when job_est_start_date is not null then job_est_start_date else pmp_start_date end) * 100, 2) end percent_complete from da.pmproject_table, da.jcjob_table, da.uetd_projectadd where pmp_proj_oraseq = proj_seq (+) and pmp_comp_code = job_comp_code and pmp_job_code = case when job_ctrl_code = 'ALL' then job_code else job_ctrl_code end) c WHERE a.emp_comp_code = b.EMP_comp_code (+) and a.emp_no = b.emp_no (+) and b.emp_PPR_YEAR = empyear and b.emp_ppr_period = empperiod and b.emp_work_comp_code = job_comp_code (+) and b.emp_job_code = job_code (+) ORDER BY a.emp_no ; emp_rec empdeptchg_cursor%ROWTYPE; output_path VARCHAR2 (50); output_line VARCHAR2 (4000); conn UTL_SMTP.connection; req UTL_HTTP.req; resp UTL_HTTP.resp; DATA RAW (20000); BEGIN conn := bec_mail.begin_mail (sender => 'cmic@beckgroup.com', recipients => USER|| '<'||USER|| '@beckgroup.com>,', -- || '"Henry Clark"', subject => sys_context('USERENV','DB_NAME') ||' - '|| 'EMPDEPTCHG File', mime_type => bec_mail.multipart_mime_type ); -- SMTP email text section bec_mail.attach_text (conn => conn, DATA => 'The attached file includes the EMPDEPTCHG File in CSV Format', mime_type => 'text/html' ); -- SMTP email attachement start section bec_mail.begin_attachment (conn => conn, mime_type => 'text/plain', inline => FALSE, filename => 'beck'||to_char(sysdate, 'MMDDYY') ||'.csv', transfer_enc => ''); output_line := '"'||'Employee #'||'","'||'Employee Name'||'","'||'Pay Run Code'||'","'||'Trade' ||'","'||'Dept-Job Charged'||'","'||'Dept-Job Name'||'","'||'Percent Complete'||'","'||'Start Date'||'","'||'Projected Completion Date'||'","'||'Project City'||'",'||UTL_TCP.CRLF||UTL_TCP.CRLF; bec_mail.write_text (conn => conn, MESSAGE => output_line); FOR emp_rec IN empdeptchg_cursor LOOP hold_emp_no := emp_rec.emp_no; output_line := '"'||emp_rec.emp_no||'","'||emp_rec.emp_name||'","'||emp_rec.prn_code ||'","'||emp_rec.emp_trade||'","'||emp_rec.emp_job_code||' - ","'||emp_rec.emp_job_name||'","'||emp_rec.emp_pct_complete||'","'||emp_rec.emp_start_date||'","'||emp_rec.emp_projected_completion_date||'","'||emp_rec.emp_project_city; output_line := output_line||'"'||UTL_TCP.CRLF; bec_mail.write_text (conn => conn, MESSAGE => output_line); END LOOP; bec_mail.end_attachment (conn =>conn); bec_mail.end_mail (conn => conn); END; -- End of EMPDEPTCHG PROCEDURE bec_manhourbtw_email( start_date IN VARCHAR2, end_date in VARCHAR2, parameter in VARCHAR2) AS -- Procedure generates asset import file and emails it to current user. -- If local db output file is needed, uncomment output_path VARCHAR2 (50); output_filename VARCHAR2 (20); output_line VARCHAR2 (750); -- output_file UTL_FILE.file_type; -- SMTP email variables conn UTL_SMTP.connection; req UTL_HTTP.req; resp UTL_HTTP.resp; DATA RAW (20000); cursor_count number:=0; tab varchar2(2):=CHR(9); summary_hours number(15,2); CURSOR output_cursor IS select a.tsh_prn_code, c.region_cd, c.office_loc, sum(nvl(tsh_normal_hours,0) + nvl(tsh_ot_hours,0) + nvl(tsh_dot_hours,0) + nvl(tsh_other_hours,0)) as Hours from pyemptimsht a, pyemployee_table b, uetd_classifier c where a.tsh_ppr_year >= to_char(to_date(start_date,'MMDDYYYY'),'yyyy') and a.tsh_date between to_date(start_date,'MMDDYYYY') and to_date(end_date,'MMDDYYYY') and a.tsh_process_flag = 'P' and a.tsh_emp_no = b.emp_no and b.emp_home_dept_code = c.department (+) and b.emp_comp_code = c.comp_code (+) and nvl(trim(a.tsh_oh_type),'XXX') = case when parameter = 'T' then nvl(trim(a.tsh_oh_type),'XXX') else 'XXX' end group by a.tsh_prn_code, c.region_cd , c.office_loc order by c.region_cd, a.tsh_prn_code, c.office_loc; -- create a instance of the cursor sal_rec output_cursor%ROWTYPE; BEGIN -- SMTP Email Header section conn := bec_mail.begin_mail (sender => 'CMIC ', recipients => user ||'<'||user||'@beckgroup.com>,', -- recipients => '"Henry Clark"', subject => sys_context('USERENV','DB_NAME') ||' - '|| 'Man Hours Report Between '||substr(start_date,1,2)||'/'||substr(start_date,3,2)||'/'||substr(start_date,5,4)||' and '||substr(end_date,1,2)||'/'||substr(end_date,3,2)||'/'||substr(end_date,5,4)||'.', mime_type => bec_mail.multipart_mime_type ); -- SMTP email text section bec_mail.attach_text (conn => conn, DATA => '
The attached file includes man hours report.
', mime_type => 'text/html' ); -- SMTP email attachement start section bec_mail.begin_attachment (conn => conn, mime_type => 'application/vnd.ms-excel', inline => FALSE, filename => 'ManHoursBtw'||to_char(sysdate, 'YY') ||'.xls', transfer_enc => '' ); -- Output to a local file section, uncomment var section also. -- output_filename := 'AG_'||TRUNC(SYSDATE)||'.txt'; ---TO_CHAR(SYSDATE,'HH24MI-MMDDYY')||'.txt'; -- output_file := UTL_FILE.FOPEN ('/transfer', output_filename, 'w'); --output_line :=('*** ' || 'ManHours'||to_char(sysdate, 'YY') ||'.xls ***'|| UTL_TCP.CRLF --||'MIME-Version: 1.0' || UTL_TCP.CRLF --||'X-Document-Type: Workbook' || UTL_TCP.CRLF --||'Content-Type: multipart/related; boundary=3D"----=_NextPart_ExcelWorkbook"'|| UTL_TCP.CRLF|| UTL_TCP.CRLF --|| '------=_NextPart_ExcelWorkbook'|| UTL_TCP.CRLF --|| 'Content-Location: books.xls'|| UTL_TCP.CRLF --|| 'Content-Transfer-Encoding: quoted-printable' || UTL_TCP.CRLF --|| 'Content-Type: text/html; charset=3D"us-ascii"'|| UTL_TCP.CRLF || UTL_TCP.CRLF --); --bec_mail.write_text (conn => conn, MESSAGE => output_line ); output_line :=(''|| UTL_TCP.CRLF ||''|| UTL_TCP.CRLF ||''|| UTL_TCP.CRLF ||''|| UTL_TCP.CRLF ||''|| UTL_TCP.CRLF ||''|| UTL_TCP.CRLF ||''|| UTL_TCP.CRLF ||'First Sheet'|| UTL_TCP.CRLF ||''|| UTL_TCP.CRLF || ''|| UTL_TCP.CRLF || ''|| UTL_TCP.CRLF || ''|| UTL_TCP.CRLF ||''|| UTL_TCP.CRLF ||''|| UTL_TCP.CRLF --||''|| UTL_TCP.CRLF || UTL_TCP.CRLF ); bec_mail.write_text (conn => conn, MESSAGE => output_line ); --output_line :=('------=_NextPart_ExcelWorkbook' || UTL_TCP.CRLF --||'Content-Location: sheet1.htm'|| UTL_TCP.CRLF --||'Content-Transfer-Encoding: quoted-printable'|| UTL_TCP.CRLF --||'Content-Type: text/html; charset=3D"us-ascii"'|| UTL_TCP.CRLF || UTL_TCP.CRLF --); --bec_mail.write_text (conn => conn, MESSAGE => output_line ); output_line :=('' || UTL_TCP.CRLF ); bec_mail.write_text (conn => conn, MESSAGE => output_line ); output_line :=( ''|| UTL_TCP.CRLF ); bec_mail.write_text (conn => conn, MESSAGE => output_line ); -- Loop thru sal_rec -- First time to gather count of cursor to remove last ctrl feed. FOR sal_rec IN output_cursor LOOP cursor_count:= output_cursor%ROWCOUNT; END LOOP; -- Loop thru sal_rec -- Please note the UTL_TCP.CRLF is to place a hard return in email file. FOR sal_rec IN output_cursor LOOP output_line := '' ||'' ||'' ||''; if (output_cursor%ROWCOUNT=cursor_count) THEN select sum(nvl(tsh_normal_hours,0) + nvl(tsh_ot_hours,0) + nvl(tsh_dot_hours,0) + nvl(tsh_other_hours,0)) into summary_hours from pyemptimsht a, pyemployee_table b, uetd_classifier c where a.tsh_ppr_year >= to_char(to_date(start_date,'MMDDYYYY'),'yyyy') and a.tsh_date between to_date(start_date,'MMDDYYYY') and to_date(end_date,'MMDDYYYY') and a.tsh_process_flag = 'P' and a.tsh_emp_no = b.emp_no and b.emp_home_dept_code = c.department (+) and b.emp_comp_code = c.comp_code (+) and nvl(trim(a.tsh_oh_type),'XXX') = case when parameter = 'T' then nvl(trim(a.tsh_oh_type),'XXX') else 'XXX' end; output_line:= output_line || '' ||'' ||'' ||''||''; ELSE output_line:= output_line || UTL_TCP.CRLF; end if; -- SMTP write one line to attachment bec_mail.write_text (conn => conn, MESSAGE => output_line ); -- UTL_FILE.PUT_LINE (output_file, output_line); END LOOP; output_line :=('
Weekly/BiweeklyDivisionOfficeHours
'||sal_rec.tsh_prn_code||''|| sal_rec.region_cd||''|| sal_rec.office_loc||''|| to_char(sal_rec.Hours,'999,999,999.00')||'
'||' '||''||' '||''||'*** TOTAL ***'||''|| to_char(summary_hours,'999,999,999.00')||'
'||UTL_TCP.CRLF||''|| UTL_TCP.CRLF); -- Close output file if needed -- UTL_FILE.FCLOSE(output_file); -- Close attachement in email. -- Close email and send it. --bec_mail.end_attachment (conn => conn); bec_mail.end_mail (conn => conn); -- End of bec_manhourbtw_email proc END; PROCEDURE bec_manhourbtwjob_email( start_date IN VARCHAR2, end_date in VARCHAR2, parameter in VARCHAR2) AS -- Procedure generates asset import file and emails it to current user. -- If local db output file is needed, uncomment output_path VARCHAR2 (50); output_filename VARCHAR2 (20); output_line VARCHAR2 (750); -- output_file UTL_FILE.file_type; -- SMTP email variables conn UTL_SMTP.connection; req UTL_HTTP.req; resp UTL_HTTP.resp; DATA RAW (20000); cursor_count number:=0; tab varchar2(2):=CHR(9); summary_hours number(15,2); CURSOR output_cursor IS select a.tsh_work_comp_code, nvl(a.tsh_job_code,'G&A') tsh_job_code, nvl(d.job_name,'Overhead') job_name, a.tsh_prn_code, c.region_cd, c.office_loc, to_char(sum(nvl(tsh_normal_hours,0) + nvl(tsh_ot_hours,0) + nvl(tsh_dot_hours,0) + nvl(tsh_other_hours,0)),'999,999,999.00') as Hours from pyemptimsht a, pyemployee_table b, uetd_classifier c, jcjob_table d where a.tsh_ppr_year >= to_char(to_date(start_date,'MMDDYYYY'),'yyyy') and a.tsh_date between to_date(start_date,'MMDDYYYY') and to_date(end_date,'MMDDYYYY') and a.tsh_process_flag = 'P' and a.tsh_emp_no = b.emp_no and b.emp_home_dept_code = c.department and b.emp_comp_code = c.comp_code and a.tsh_work_comp_code = d.job_comp_code (+) and a.tsh_job_code = d.job_code (+) and nvl(trim(a.tsh_oh_type),'XXX') = case when parameter = 'T' then nvl(trim(a.tsh_oh_type),'XXX') else 'XXX' end group by a.tsh_work_comp_code, a.tsh_job_code, d.job_name, a.tsh_prn_code, c.region_cd , c.office_loc order by a.tsh_work_comp_code, a.tsh_job_code, c.region_cd , c.office_loc, a.tsh_job_code, d.job_name; -- create a instance of the cursor sal_rec output_cursor%ROWTYPE; BEGIN -- SMTP Email Header section conn := bec_mail.begin_mail (sender => 'CMIC ', recipients => user ||'<'||user||'@beckgroup.com>,', -- recipients => '"Henry Clark"', subject => sys_context('USERENV','DB_NAME') ||' - '|| 'Man Hours Report Between '||substr(start_date,1,2)||'/'||substr(start_date,3,2)||'/'||substr(start_date,5,4)||' and '||substr(end_date,1,2)||'/'||substr(end_date,3,2)||'/'||substr(end_date,5,4)||' by Job', mime_type => bec_mail.multipart_mime_type ); -- SMTP email text section bec_mail.attach_text (conn => conn, DATA => '
The attached file includes man hours report.
', mime_type => 'text/html' ); -- SMTP email attachement start section bec_mail.begin_attachment (conn => conn, mime_type => 'application/vnd.ms-excel', inline => FALSE, filename => 'ManHoursByJob'||to_char(sysdate, 'YY') ||'.xls', transfer_enc => '' ); -- Output to a local file section, uncomment var section also. -- output_filename := 'AG_'||TRUNC(SYSDATE)||'.txt'; ---TO_CHAR(SYSDATE,'HH24MI-MMDDYY')||'.txt'; -- output_file := UTL_FILE.FOPEN ('/transfer', output_filename, 'w'); --output_line :=('*** ' || 'ManHours'||to_char(sysdate, 'YY') ||'.xls ***'|| UTL_TCP.CRLF --||'MIME-Version: 1.0' || UTL_TCP.CRLF --||'X-Document-Type: Workbook' || UTL_TCP.CRLF --||'Content-Type: multipart/related; boundary=3D"----=_NextPart_ExcelWorkbook"'|| UTL_TCP.CRLF|| UTL_TCP.CRLF- --|| '------=_NextPart_ExcelWorkbook'|| UTL_TCP.CRLF --|| 'Content-Location: books.xls'|| UTL_TCP.CRLF --|| 'Content-Transfer-Encoding: quoted-printable' || UTL_TCP.CRLF --|| 'Content-Type: text/html; charset=3D"us-ascii"'|| UTL_TCP.CRLF || UTL_TCP.CRLF --); bec_mail.write_text (conn => conn, MESSAGE => output_line ); output_line :=(''|| UTL_TCP.CRLF ||''|| UTL_TCP.CRLF ||''|| UTL_TCP.CRLF ||''|| UTL_TCP.CRLF ||''|| UTL_TCP.CRLF ||''|| UTL_TCP.CRLF ||''|| UTL_TCP.CRLF ||'Man Hours'|| UTL_TCP.CRLF ||''|| UTL_TCP.CRLF || ''|| UTL_TCP.CRLF || ''|| UTL_TCP.CRLF ||''|| UTL_TCP.CRLF ||''|| UTL_TCP.CRLF --||''|| UTL_TCP.CRLF || UTL_TCP.CRLF ); bec_mail.write_text (conn => conn, MESSAGE => output_line ); --output_line :=('------=_NextPart_ExcelWorkbook' || UTL_TCP.CRLF --||'Content-Location: sheet1.htm'|| UTL_TCP.CRLF --||'Content-Transfer-Encoding: quoted-printable'|| UTL_TCP.CRLF --||'Content-Type: text/html; charset=3D"us-ascii"'|| UTL_TCP.CRLF || UTL_TCP.CRLF --); bec_mail.write_text (conn => conn, MESSAGE => output_line ); output_line :=('' || UTL_TCP.CRLF ); bec_mail.write_text (conn => conn, MESSAGE => output_line ); output_line :=( ''|| UTL_TCP.CRLF ); bec_mail.write_text (conn => conn, MESSAGE => output_line ); -- Loop thru sal_rec -- First time to gather count of cursor to remove last ctrl feed. FOR sal_rec IN output_cursor LOOP cursor_count:= output_cursor%ROWCOUNT; END LOOP; -- Loop thru sal_rec -- Please note the UTL_TCP.CRLF is to place a hard return in email file. FOR sal_rec IN output_cursor LOOP output_line := '' ||'' ||'' ||'' ||'' ||'' ||''; if (output_cursor%ROWCOUNT=cursor_count) THEN select sum(nvl(tsh_normal_hours,0) + nvl(tsh_ot_hours,0) + nvl(tsh_dot_hours,0) + nvl(tsh_other_hours,0)) into summary_hours from pyemptimsht a, pyemployee_table b, uetd_classifier c, jcjob_table d where a.tsh_ppr_year >= to_char(to_date(start_date,'MMDDYYYY'),'yyyy') and a.tsh_date between to_date(start_date,'MMDDYYYY') and to_date(end_date,'MMDDYYYY') and a.tsh_process_flag = 'P' and a.tsh_emp_no = b.emp_no and b.emp_home_dept_code = c.department and b.emp_comp_code = c.comp_code and a.tsh_work_comp_code = d.job_comp_code (+) and a.tsh_job_code = d.job_code (+) and nvl(trim(a.tsh_oh_type),'XXX') = case when parameter = 'T' then nvl(trim(a.tsh_oh_type),'XXX') else 'XXX' end; output_line:= output_line || '' ||'' ||'' ||'' ||'' ||'' ||'' || ''; ELSE output_line:= output_line || UTL_TCP.CRLF; end if; -- SMTP write one line to attachment bec_mail.write_text (conn => conn, MESSAGE => output_line ); -- UTL_FILE.PUT_LINE (output_file, output_line); END LOOP; output_line :=('
COMPANYJOBNAMEPRN CODEREGION CODEOFFICE LOCATIONHOURS
'||sal_rec.tsh_work_comp_code||''||sal_rec.tsh_job_code||''||sal_rec.job_name||''||sal_rec.tsh_prn_code||''|| sal_rec.region_cd||''|| sal_rec.office_loc||''||sal_rec.Hours||'
'||' '||''||' '||''||' '||''||' '||''||' '||''||'*** TOTAL *** '||''||to_char(summary_hours,'999,999,999.00')||'
'||UTL_TCP.CRLF||' '|| UTL_TCP.CRLF); bec_mail.write_text (conn => conn, MESSAGE => output_line ); -- Close output file if needed -- UTL_FILE.FCLOSE(output_file); -- Close attachement in email. -- Close email and send it. --bec_mail.end_attachment (conn => conn); bec_mail.end_mail (conn => conn); -- End of bec_manhoubtwjobr_email proc END; PROCEDURE bec_neghrs_email( in_year IN NUMBER, in_period IN NUMBER) AS -- output_file UTL_FILE.file_type; -- SMTP email variables conn UTL_SMTP.connection; req UTL_HTTP.req; resp UTL_HTTP.resp; DATA RAW (20000); cursor_count number:=0; tab varchar2(2):=CHR(9); CURSOR emp_cursor IS SELECT emp_no, emp_first_name||' '||emp_last_name AS NAME, emp_home_dept_code, CASE WHEN emp_email_address LIKE '%@%' THEN emp_email_address ELSE emp_email_address||'@beckgroup.com' END AS emp_email_address, emp_pyg_code, case when emp_type = 'S' then 'Salaried' else 'Hourly' end employee_type, to_char(NVL(aa.total_hours,0),'9999D00') AS total_hours FROM PYEMPLOYEE_TABLE, (SELECT tsh_emp_no, total_hours from ( SELECT tsh_emp_no, SUM( NVL(case when tsh_day_01 > 0 then tsh_day_01 else 0 end,0) + NVL(case when tsh_day_02 > 0 then tsh_day_02 else 0 end,0) + NVL(case when tsh_day_03 > 0 then tsh_day_03 else 0 end,0) + NVL(case when tsh_day_04 > 0 then tsh_day_04 else 0 end,0) + NVL(case when tsh_day_05 > 0 then tsh_day_05 else 0 end,0) + NVL(case when tsh_day_06 > 0 then tsh_day_06 else 0 end,0) + NVL(case when tsh_day_07 > 0 then tsh_day_07 else 0 end,0) + NVL(case when tsh_day_08 > 0 then tsh_day_08 else 0 end,0) + NVL(case when tsh_day_09 > 0 then tsh_day_09 else 0 end,0) + NVL(case when tsh_day_10 > 0 then tsh_day_10 else 0 end,0) + NVL(case when tsh_day_11 > 0 then tsh_day_11 else 0 end,0) + NVL(case when tsh_day_12 > 0 then tsh_day_12 else 0 end,0) + NVL(case when tsh_day_13 > 0 then tsh_day_13 else 0 end,0) + NVL(case when tsh_day_14 > 0 then tsh_day_14 else 0 end,0)) AS total_hours FROM PYEMPTIMSHT_CERT WHERE tsh_ppr_period = in_period AND tsh_ppr_year = in_year AND tsh_prn_code IN ('BW') GROUP BY tsh_emp_no) where tsh_emp_no in ( select emp_no from pyemployee_table where EMP_STATUS = 'A' and emp_home_dept_code not in ('DSGN','ATLDN','AUSDN','DESTI','TAMID') AND total_hours > 80 )) aa WHERE emp_no = aa.tsh_emp_no (+) AND emp_status = 'A' AND aa.total_hours > 80 ORDER BY emp_type, emp_last_name, emp_first_name; -- create a instance of the cursor emp_rec PYEMPLOYEE_TABLE%ROWTYPE; l_maicon utl_smtp.connection; email_address VARCHAR2 (100); emp_list VARCHAR2 (8000); emp_list2 VARCHAR2 (8000); emp_list3 VARCHAR2 (8000); hold_emp_no VARCHAR2 (16); hold_email_address VARCHAR2 (100); output_path VARCHAR2 (50); output_filename VARCHAR2 (20); output_header VARCHAR2 (312); output_line VARCHAR2 (255); output_file UTL_FILE.FILE_TYPE; hold_error VARCHAR2 (100); BEGIN output_header := 'Emp ID Name Employee Type Department Hours Entered'; FOR emp_rec IN emp_cursor LOOP emp_list := emp_list||emp_rec.emp_no||' '||RPAD(INITCAP(emp_rec.NAME),40)||' '||RPAD(emp_rec.employee_type,17)||RPAD(emp_rec.emp_home_dept_code,'15')||TO_CHAR(NVL(emp_rec.total_hours,0),999.99)||utl_tcp.crlf; IF LENGTH(emp_list) > 7000 THEN emp_list3 := emp_list2; emp_list2 := emp_list; emp_list := NULL; END IF; END LOOP; l_maicon := utl_smtp.open_connection('localhost'); utl_smtp.helo(l_maicon,'hostname'); utl_smtp.mail(l_maicon,'alert@beckgroup.com'); utl_smtp.rcpt(l_maicon,'lindadicus@beckgroup.com'); utl_smtp.DATA(l_maicon,'From: alert@beckgroup.com' || utl_tcp.crlf || 'To:'||'lindadicus@beckgroup.com'|| utl_tcp.crlf || 'Subject: ' || sys_context('USERENV','DB_NAME') ||' - '|| 'Hourly Employees Who Entered MORE Than 80 Positive Hours - Please Read !!!!' || utl_tcp.crlf ||utl_tcp.crlf||utl_tcp.crlf|| output_header||utl_tcp.crlf|| RTRIM(emp_list)|| RTRIM(emp_list2)|| RTRIM(emp_list3)||utl_tcp.crlf); utl_smtp.quit(l_maicon); l_maicon := utl_smtp.open_connection('localhost'); utl_smtp.helo(l_maicon,'hostname'); utl_smtp.mail(l_maicon,'alert@beckgroup.com'); utl_smtp.rcpt(l_maicon,'heatherhood@beckgroup.com'); utl_smtp.DATA(l_maicon,'From: alert@beckgroup.com' || utl_tcp.crlf || 'To:'||'heatherhood@beckgroup.com'|| utl_tcp.crlf || 'Subject: ' || sys_context('USERENV','DB_NAME') ||' - '|| 'Hourly Employees Who Entered MORE Than 80 Positive Hours - Please Read !!!!' || utl_tcp.crlf ||utl_tcp.crlf||utl_tcp.crlf|| output_header||utl_tcp.crlf|| RTRIM(emp_list)|| RTRIM(emp_list2)|| RTRIM(emp_list3)||utl_tcp.crlf); utl_smtp.quit(l_maicon); l_maicon := utl_smtp.open_connection('localhost'); utl_smtp.helo(l_maicon,'hostname'); utl_smtp.mail(l_maicon,'cmicalert@beckgroup.com'); utl_smtp.rcpt(l_maicon,'itstaff@beckgroup.com'); utl_smtp.DATA(l_maicon,'From: cmicalert@beckgroup.com' || utl_tcp.crlf || 'To:'||'itstaff@beckgroup.com'|| utl_tcp.crlf || 'Subject: ' || sys_context('USERENV','DB_NAME') ||' - '|| 'Hourly Employees Who Entered MORE Than 80 Positive Hours - Please Read !!!!' || utl_tcp.crlf ||utl_tcp.crlf||utl_tcp.crlf|| output_header||utl_tcp.crlf|| RTRIM(emp_list)|| RTRIM(emp_list2)|| RTRIM(emp_list3)||utl_tcp.crlf); utl_smtp.quit(l_maicon); COMMIT; END; FUNCTION bec_last_paid ( in_emp_no IN VARCHAR2 ) RETURN VARCHAR2 IS v_val varchar2(200); hold_yr_period VARCHAR2 (6); CURSOR emp_cursor IS select phy_job pay_job, sum(phy_hrs_paid) pay_hours from da.pyemppayhist where phy_emp_no = in_emp_no and phy_ppr_year = substr(hold_yr_period,1,4) and phy_ppr_period = substr(hold_yr_period,5,2) and phy_hrs_paid is not null and phy_hire_flag is null and phy_prn_code in ('BW','WK') and phy_tran_code = 'NWHR' and phy_job is not null group by phy_job having sum(phy_hrs_paid) > 20; emp_rec emp_cursor%ROWTYPE; BEGIN select max(phy_ppr_year||trim(to_char(phy_ppr_period,'00'))) max_period into hold_yr_period from da.pyemppayhist where phy_emp_no = in_emp_no and phy_prn_code in ('BW','WK'); v_val := null; FOR emp_rec IN emp_cursor LOOP v_val := v_val||' '||emp_rec.pay_job; END LOOP; return (v_val); END; PROCEDURE bec_design_burden_reimb( in_year IN NUMBER, in_period IN NUMBER) AS conn UTL_SMTP.connection; req UTL_HTTP.req; resp UTL_HTTP.resp; DATA RAW (20000); cursor_count number:=0; CURSOR job_cursor IS select phy_work_comp_code, phy_job, phy_phase, phy_category, ppr_posting_date, sum(phy_amount) * -1 phy_amount from da.pyemppayhist, da.pycompayprd where ppr_comp_code = '03' and ppr_prn_code = 'BW' and ppr_year = in_year and ppr_period = in_period and phy_ppr_year = in_year and phy_ppr_period = in_period and nvl(phy_emplr_contr_flag,'N') = 'Y' and exists (select 'X' from pyemployee_table where phy_emp_no = emp_no and (emp_home_dept_code = 'DSGN' or emp_home_dept_code like '%DN' or emp_home_dept_code like '%ID' or emp_home_dept_code = 'COMEDD' or emp_home_dept_code = 'CLTMEQ') and emp_prn_code = 'BW') and phy_job is not null group by phy_work_comp_code, phy_job, phy_phase, phy_category, ppr_posting_date order by phy_work_comp_code, phy_job, phy_phase, phy_category; job_rec DA.PYEMPPAYHIST%ROWTYPE; CURSOR reimb_cursor IS select '03' reimb_comp_code, emp_home_dept_code reimb_dept_code, '60003' reimb_acc_code, ppr_posting_date, sum(phy_amount) reimb_amount from da.pyemppayhist, da.pyemployee_table, da.pycompayprd where ppr_comp_code = '03' and ppr_prn_code = 'BW' and ppr_year = in_year and ppr_period = in_period and phy_emp_no = emp_no and phy_ppr_year = in_year and phy_ppr_period = in_period and nvl(phy_emplr_contr_flag,'N') = 'Y' and exists (select 'X' from pyemployee_table where phy_emp_no = emp_no and (emp_home_dept_code = 'DSGN' or emp_home_dept_code like '%DN' or emp_home_dept_code like '%ID' or emp_home_dept_code = 'COMEDD' or emp_home_dept_code = 'CLTMEQ') and emp_prn_code = 'BW') and phy_job is not null group by emp_home_dept_code, ppr_posting_date order by emp_home_dept_code; reimb_rec DA.PYEMPPAYHIST%ROWTYPE; CURSOR batch_cursor IS SELECT fbh_jc_comp_code, SUM(fbh_jc_amt) AS amount FROM beck.bec_jc_foreign_batch GROUP BY fbh_jc_comp_code; batch_rec beck.bec_jc_foreign_batch%ROWTYPE; CURSOR output_cursor IS SELECT * FROM beck.bec_jc_foreign_batch ORDER BY fbh_jc_comp_code, fbh_jc_job_code; sal_rec beck.bec_jc_foreign_batch%ROWTYPE; hold_amount NUMBER (8,2); output_path VARCHAR2 (50); output_line VARCHAR2 (500); hold_dept VARCHAR2 (6); hold_error VARCHAR2 (100); hold_job_cost_flag VARCHAR2 (1); hold_job_status_code VARCHAR2 (1); BEGIN conn := bec_mail.begin_mail (sender => 'CMiC ', -- recipients => user ||'<'||user||'@beckgroup.com>,', recipients => 'henryclark,', subject => sys_context('USERENV','DB_NAME') ||' - '|| 'Design Benefit Reimbursement File from '||sys_context('userenv','db_name'), mime_type => bec_mail.multipart_mime_type ); -- SMTP email text section bec_mail.attach_text (conn => conn, DATA => '
The attached file includes Design burden reimbursement charges to be imported via CMiC batch import.
Please check the Error_' ||to_char(sysdate, 'MMDDYY')||'.txt file for process completion.
'||UTL_TCP.CRLF||'
', mime_type => 'text/html' ); -- SMTP email attachement start section bec_mail.begin_attachment (conn => conn, mime_type => 'text/plain', inline => FALSE, filename => 'Error_'||to_char(sysdate, 'MMDDYY') ||'.txt', transfer_enc => '' ); DELETE FROM beck.bec_jc_foreign_batch; COMMIT; FOR job_rec IN job_cursor LOOP BEGIN INSERT INTO beck.bec_jc_foreign_batch VALUES (job_rec.phy_work_comp_code, '03', 'J', 'DSGN Burden Reim', 'Burden Reimb (Period '|| in_year||'-'||in_period||')', 'DSGN Burden Reim', 'Design Burden Reimbursement (Year '||in_year||' Period '||in_period||')', job_rec.phy_job, job_rec.phy_phase, job_rec.phy_category, null, null, TO_CHAR(job_rec.ppr_posting_date,'DD-MON-YYYY'), job_rec.phy_amount, NULL, '$', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, TO_CHAR(job_rec.ppr_posting_date,'DD-MON-YYYY')); COMMIT; EXCEPTION WHEN DUP_VAL_ON_INDEX THEN hold_error := 'Duplicate Index on Insert '|| job_rec.phy_job||' - '||job_rec.phy_phase; bec_mail.write_text (conn => conn, MESSAGE => hold_error||UTL_TCP.CRLF); WHEN OTHERS THEN hold_error := 'Job Rec Error on Insert '|| job_rec.phy_job||' - '||job_rec.phy_phase; bec_mail.write_text (conn => conn, MESSAGE => hold_error||UTL_TCP.CRLF); END; END LOOP; FOR reimb_rec IN reimb_cursor LOOP BEGIN INSERT INTO beck.bec_jc_foreign_batch VALUES (reimb_rec.reimb_comp_code, '03', 'G', 'DSGN Burden Reim', 'Burden Reimb (Period '|| in_year||'-'||in_period||')', 'DSGN Burden Reim', 'Design Burden Reimbursement (Year '||in_year||' Period '||in_period||')', null, null, null, reimb_rec.reimb_dept_code, reimb_rec.reimb_acc_code, TO_CHAR(reimb_rec.ppr_posting_date,'DD-MON-YYYY'), reimb_rec.reimb_amount, NULL, '$', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, TO_CHAR(reimb_rec.ppr_posting_date,'DD-MON-YYYY')); COMMIT; EXCEPTION WHEN DUP_VAL_ON_INDEX THEN hold_error := 'Duplicate Index on Insert '|| reimb_rec.reimb_dept_code||' - '||reimb_rec.reimb_acc_code; bec_mail.write_text (conn => conn, MESSAGE => hold_error||UTL_TCP.CRLF); WHEN OTHERS THEN hold_error := 'Reimbursement Error on Insert '|| reimb_rec.reimb_dept_code||' - '||reimb_rec.reimb_acc_code; bec_mail.write_text (conn => conn, MESSAGE => hold_error||UTL_TCP.CRLF); END; END LOOP; commit; hold_error := 'Complete '; bec_mail.write_text (conn => conn, MESSAGE => hold_error||UTL_TCP.CRLF); bec_mail.end_attachment (conn =>conn); bec_mail.begin_attachment (conn => conn, mime_type => 'text/plain', inline => FALSE, filename => 'DSGNReimb_'||to_char(sysdate, 'MMDDYY') ||'.txt', transfer_enc => '' ); FOR sal_rec IN output_cursor LOOP output_line := '"'||sal_rec.FBH_JC_COMP_CODE||'","'||sal_rec.FBH_JC_DSRC_COMP_CODE||'","'||sal_rec.FBH_JC_DIST_TYPE_CODE ||'","'||sal_rec.FBH_JC_SRC_CODE||'","'||sal_rec.FBH_JC_SRC_DESC||'","'||sal_rec.FBH_JC_REF_CODE ||'","'||sal_rec.FBH_JC_REF_DESC||'","'||sal_rec.FBH_JC_JOB_CODE||'","'||sal_rec.FBH_JC_PHS_CODE ||'","'||sal_rec.FBH_JC_CAT_CODE||'","'||sal_rec.FBH_JC_DEPT_CODE||'","'||sal_rec.FBH_JC_ACC_CODE ||'","'||TO_CHAR(sal_rec.FBH_JC_REF_DATE,'DD-MON-YYYY')||'",'||sal_rec.FBH_JC_AMT||',"'||sal_rec.FBH_JC_UNIT ||'","'||sal_rec.FBH_JC_WM_CODE||'","'||sal_rec.FBH_JC_WBSV_CODE1||'","'||sal_rec.FBH_JC_WBSV_CODE2 ||'","'||sal_rec.FBH_JC_WBSV_CODE3||'","'||sal_rec.FBH_JC_WBSV_CODE4||'","'||sal_rec.FBH_JC_EQP_CODE ||'","'||sal_rec.FBH_JC_TRANCODE_CODE||'","'||sal_rec.FBH_JC_COMPON_CODE||'","'||sal_rec.FBH_JC_TAV_CODE1 ||'","'||sal_rec.FBH_JC_TAV_CODE2||'","'||sal_rec.FBH_JC_TAV_CODE3||'","'||sal_rec.FBH_JC_TAV_CODE4 ||'","'||sal_rec.FBH_JC_JOB_LONG_CODE||'","'||sal_rec.FBH_OVERRIDE_BILL_AMT ||'","'||sal_rec.FBH_OVERRIDE_BILL_RATE||'","'||sal_rec.FBH_OVERRIDE_TRADE_CODE ||'","'||TO_CHAR(sal_rec.FBH_POST_DATE,'DD-MON-YYYY')||'","",""'; bec_mail.write_text (conn => conn, MESSAGE => output_line||UTL_TCP.CRLF); END LOOP; bec_mail.end_mail (conn => conn ); END; PROCEDURE bec_salary_planning( in_year IN NUMBER, in_version IN VARCHAR2) AS conn UTL_SMTP.connection; req UTL_HTTP.req; resp UTL_HTTP.resp; DATA RAW (20000); cursor_count number:=0; CURSOR sal_cursor IS SELECT to_char(sysdate,'YYYY') sps_year, in_version sps_version, a.emp_no sps_emp_id, case when a.emp_nick_name is not null then INITCAP(a.emp_nick_name) else INITCAP(a.emp_first_name) end sps_first_name, CASE WHEN a.emp_last_name LIKE '%III' THEN INITCAP(SUBSTR(a.emp_last_name,1,LENGTH(a.emp_last_name) - 3))||'III' WHEN a.emp_last_name LIKE '%II' THEN INITCAP(SUBSTR(a.emp_last_name,1,LENGTH(a.emp_last_name) - 2))||'II' ELSE INITCAP(emp_last_name) END sps_last_name, case when b.adjservice is null then TO_CHAR(a.emp_hire_date,'MM/DD/YYYY') else TO_CHAR(b.adjservice,'MM/DD/YYYY') end sps_hire_date, to_char(to_date('12312099','MMDDYYYY'),'MM/DD/YYYY') sps_term_date, a.emp_comp_code||'-'||a.emp_home_dept_code sps_home_dept, null sps_comments, null sps_extra_info_1, null sps_extra_info_2, null sps_extra_info_3, null sps_extra_info_4, null sps_extra_info_5, 1 sps_max_fte, 'Hired' sps_is_vacant, null sps_other_1, null sps_other_2, a.emp_no sps_position_id, a.emp_trd_code sps_desc, a.emp_comp_code||'-'||a.emp_home_dept_code sps_pos_home_dept, 'Group 1' sps_pos_group_id, case when a.emp_type = 'H' then 'Hourly' else 'Salary' end sps_hrly_or_salary, null sps_pay_cycle_override, null sps_home_group, null sps_std_hrs_override, null sps_override_pay_cycle, null sps_override_home_group, null sps_override_std_hrs, 'Permanent' sps_pos_classification, 'Full-time' sps_status, null sps_other_attrib, 'D' sps_step_id, 'Default' sps_description, null sps_stp_max_no_fte, null sps_stp_alloted_hrs, null sps_stp_max_annual, null sps_stp_hour, null sps_stp_comp_type, null sps_stp_ann_comp_per_fte, null sps_stp_hrly_comp_per_fte, null sps_stp_comp_apply_max, null sps_stp_eff_date_override, null sps_stp_override_eff_date, 1 sps_eps_fte_full_year, case when b.adjservice is null then TO_CHAR(a.emp_hire_date,'MM/DD/YYYY') else TO_CHAR(b.adjservice,'MM/DD/YYYY') end sps_eps_start_date, to_char(to_date('12312099','MMDDYYYY'),'MM/DD/YYYY') sps_eps_end_date, -- c.comp_type sps_eps_comp_type, -- c.annual_amt sps_eps_ann_comp_per_fte, -- c.hourly_amt sps_eps_hrly_comp_per_fte, 'Regular Wages' sps_eps_comp_type, ROUND (CASE WHEN emp_annual_salary IS NULL THEN (emp_hourly_rate * 2080) ELSE emp_annual_salary END,0) sps_eps_ann_comp_per_fte, null sps_eps_hrly_comp_per_fte, null sps_eps_eff_date_override, null sps_eps_override_eff_date, c.benefit_code sps_ebo_benefit, case when c.benefit_code is null then null else 'Y' end sps_ebo_include, case when c.benefit_code is null then null else 'Y' end sps_ebo_override, -- case when c.benefit_code = 'Life Dep' then -- round(ROUND (CASE WHEN emp_annual_salary IS NULL THEN -- (emp_hourly_rate * 2080) -- ELSE -- emp_annual_salary -- END,0) * (benefit_amt / 100),2) -- else -- c.benefit_amt * 26 end c.benefit_amt sps_ebo_override_amount from da.pyemployee_table a, da.uetd_additional b, (select ELD_EMP_NO, 'Life Dep' benefit_code, ELD_BD_AMOUNT benefit_amt from da.HRELECTEDPLANS_EM where eld_end_date is null and eld_code = '110' UNION ALL select ELD_EMP_NO, 'Vision' benefit_code, ELD_BD_AMOUNT benefit_amt from da.HRELECTEDPLANS_EM where eld_end_date is null and eld_code = '150' UNION ALL select ELD_EMP_NO, 'LT Care' benefit_code, ELD_BD_AMOUNT benefit_amt from da.HRELECTEDPLANS_EM where eld_end_date is null and eld_code = '130' UNION ALL select ELD_EMP_NO, 'Medical' benefit_code, ELD_BD_AMOUNT benefit_amt from da.HRELECTEDPLANS_EM where eld_end_date is null and eld_code = '170' UNION ALL select EMB_EMP_NO, 'Medical' benefit_code, case when EMB_BEN_AMOUNT is null then COB_BEN_AMOUNT else EMB_BEN_AMOUNT end benefit_amt from da.PYEMPBEN, PYCOMBEN where emb_ben_code = cob_ben_code and emb_comp_code = cob_comp_code and emb_plan_code = cob_plan_code and emb_plo_code = cob_plo_code and emb_prn_code = cob_prn_code and emb_end_date is null and emb_ben_code = '420') c where a.emp_no = b.emp_num (+) and a.emp_prn_code = 'BW' and a.emp_status = 'A' and a.emp_no = c.eld_emp_no (+); sal_rec DA.HRELECTEDPLANS_EM%ROWTYPE; output_line VARCHAR2 (3000); BEGIN conn := bec_mail.begin_mail (sender => 'CMiC ', recipients => user ||'<'||user||'@beckgroup.com>,', -- recipients => 'henryclark,', subject => sys_context('USERENV','DB_NAME') ||' - '|| 'Salary Planning File' , mime_type => bec_mail.multipart_mime_type ); -- SMTP email text section bec_mail.attach_text (conn => conn, DATA => '
The attached file includes the FMW Salary Planning File for the Current Year.'||UTL_TCP.CRLF||'
', mime_type => 'text/html' ); bec_mail.begin_attachment (conn => conn, mime_type => 'application/vnd.ms-excel', inline => FALSE, filename => 'SalaryPlanning'||to_char(sysdate, 'MMDDYY') ||'.xls', transfer_enc => '' ); output_line :=(''|| UTL_TCP.CRLF ||''|| UTL_TCP.CRLF ||''|| UTL_TCP.CRLF ||''|| UTL_TCP.CRLF ||''|| UTL_TCP.CRLF ||''|| UTL_TCP.CRLF ||'Sheet1'|| UTL_TCP.CRLF || ''|| UTL_TCP.CRLF || ''|| UTL_TCP.CRLF || ''|| UTL_TCP.CRLF || ''|| UTL_TCP.CRLF ||''|| UTL_TCP.CRLF ||''|| UTL_TCP.CRLF || UTL_TCP.CRLF ); bec_mail.write_text (conn => conn, MESSAGE => output_line ); output_line :=('' || UTL_TCP.CRLF ); bec_mail.write_text (conn => conn, MESSAGE => output_line ); output_line := ''; output_line := output_line||''; output_line := output_line||''; output_line := output_line||''; output_line := output_line||''; output_line := output_line||''; output_line := output_line||''; output_line := output_line||''; output_line := output_line||''; output_line := output_line||''; output_line := output_line||''; output_line := output_line||''; output_line := output_line||''; output_line := output_line||''; output_line := output_line||''; output_line := output_line||''; output_line := output_line||''; output_line := output_line||''; output_line := output_line||''; output_line := output_line||''; output_line := output_line||''; output_line := output_line||''; output_line := output_line||''; output_line := output_line||''; output_line := output_line||''; output_line := output_line||''; output_line := output_line||''; output_line := output_line||''; output_line := output_line||''; output_line := output_line||''; output_line := output_line||''; output_line := output_line||''; output_line := output_line||''; output_line := output_line||''; output_line := output_line||''; output_line := output_line||''; output_line := output_line||''; output_line := output_line||''; output_line := output_line||''; output_line := output_line||''; output_line := output_line||''; output_line := output_line||''; output_line := output_line||''; output_line := output_line||''; output_line := output_line||''; output_line := output_line||''; output_line := output_line||''; output_line := output_line||''; output_line := output_line||''; output_line := output_line||''; output_line := output_line||''; output_line := output_line||''; output_line := output_line||''; output_line := output_line||''; output_line := output_line||''; output_line := output_line||''; output_line := output_line||''; output_line := output_line|| UTL_TCP.CRLF; bec_mail.write_text (conn => conn, MESSAGE => output_line ); FOR sal_rec IN sal_cursor LOOP output_line := ''; output_line := output_line||''; output_line := output_line||''; output_line := output_line||''; output_line := output_line||''; output_line := output_line||''; output_line := output_line||''; output_line := output_line||''; output_line := output_line||''; output_line := output_line||''; output_line := output_line||''; output_line := output_line||''; output_line := output_line||''; output_line := output_line||''; output_line := output_line||''; output_line := output_line||''; output_line := output_line||''; output_line := output_line||''; output_line := output_line||''; output_line := output_line||''; output_line := output_line||''; output_line := output_line||''; output_line := output_line||''; output_line := output_line||''; output_line := output_line||''; output_line := output_line||''; output_line := output_line||''; output_line := output_line||''; output_line := output_line||''; output_line := output_line||''; output_line := output_line||''; output_line := output_line||''; output_line := output_line||''; output_line := output_line||''; output_line := output_line||''; output_line := output_line||''; output_line := output_line||''; output_line := output_line||''; output_line := output_line||''; output_line := output_line||''; output_line := output_line||''; output_line := output_line||''; output_line := output_line||''; output_line := output_line||''; output_line := output_line||''; output_line := output_line||''; output_line := output_line||''; output_line := output_line||''; output_line := output_line||''; output_line := output_line||''; output_line := output_line||''; output_line := output_line||''; output_line := output_line||''; output_line := output_line||''; output_line := output_line||''; output_line := output_line||''; output_line := output_line||''; output_line := output_line|| UTL_TCP.CRLF; bec_mail.write_text (conn => conn, MESSAGE => output_line ); END LOOP; bec_mail.end_attachment (conn =>conn); bec_mail.end_mail (conn => conn ); END; PROCEDURE bec_safety_hours( in_from_date IN DATE, in_to_date IN DATE, in_type IN VARCHAR2, in_sc IN VARCHAR2, in_vendor IN VARCHAR2) AS conn UTL_SMTP.connection; req UTL_HTTP.req; resp UTL_HTTP.resp; DATA RAW (20000); cursor_count number:=0; summary_hours number(15,2); CURSOR office_cursor IS select case when tsh_dept_code like 'ATT%' then tsh_dept_code when region_cd = 'Corporate Office' then 'Corporate' else office_loc end location, to_char(sum(nvl(tsh_normal_hours,0)+nvl(tsh_ot_hours,0)+nvl(tsh_dot_hours,0)),'999,999,999.99') reg_hours from da.pyemptimsht, da.uetd_classifier where tsh_work_comp_code = comp_code (+) and tsh_dept_code = department (+) and TSH_PPR_YEAR >= to_char(in_from_date,'yyyy') and tsh_date between in_from_date and in_to_date group by case when tsh_dept_code like 'ATT%' then tsh_dept_code when region_cd = 'Corporate Office' then 'Corporate' else office_loc end order by case when tsh_dept_code like 'ATT%' then tsh_dept_code when region_cd = 'Corporate Office' then 'Corporate' else office_loc end; office_rec DA.PYEMPTIMSHT%ROWTYPE; CURSOR region_cursor IS select case when tsh_dept_code like 'ATT%' then tsh_dept_code else region_cd end location, to_char(sum(nvl(tsh_normal_hours,0)+nvl(tsh_ot_hours,0)+nvl(tsh_dot_hours,0)),'999,999,999.99') as reg_hours from da.pyemptimsht, da.uetd_classifier where tsh_work_comp_code = comp_code (+) and tsh_dept_code = department (+) and TSH_PPR_YEAR >= to_char(in_from_date,'yyyy') and tsh_date between in_from_date and in_to_date group by case when tsh_dept_code like 'ATT%' then tsh_dept_code else region_cd end order by case when tsh_dept_code like 'ATT%' then tsh_dept_code else region_cd end; region_rec DA.PYEMPTIMSHT%ROWTYPE; CURSOR job_cursor IS select tsh_work_comp_code tsh_comp_code, case when tsh_job_code is null then 'G&A' else tsh_job_code end tsh_job_code, case when tsh_job_code is null then 'Overhead' else job_name end tsh_job_name, to_char(sum(nvl(tsh_normal_hours,0)+nvl(tsh_ot_hours,0)+nvl(tsh_dot_hours,0)),'999,999,999.99') as tsh_reg_hours from da.pyemptimsht, da.jcjob_table where tsh_work_comp_code = job_comp_code (+) and tsh_job_code = job_code (+) and TSH_PPR_YEAR >= to_char(in_from_date,'yyyy') and tsh_date between in_from_date and in_to_date group by tsh_work_comp_code, case when tsh_job_code is null then 'G&A' else tsh_job_code end, case when tsh_job_code is null then 'Overhead' else job_name end order by tsh_work_comp_code, case when tsh_job_code is null then 'G&A' else tsh_job_code end; job_rec DA.PYEMPTIMSHT%ROWTYPE; CURSOR sub_office_cursor IS select case when job_default_dept_code like 'ATT%' then job_default_dept_code else nvl(office_loc,'N/A') end location, to_char(sum(insv_amt),'999,999,999.99') reg_hours from da.insvou, da.scmast, da.jcjob_table, da.uetd_classifier, (select vou_comp_code, vou_num, vou_ven_code, vou_cont_code from da.voucher where vou_cont_code is not null and vou_reg_inv_num = 0 and vou_post_date between in_from_date and in_to_date and vou_comp_code != '62') where insv_vou_num = vou_num and vou_comp_code = scmst_comp_code and vou_ven_code = scmst_ven_code and vou_cont_code = scmst_cont_code and scmst_chg_code = '000' and scmst_comp_code = job_comp_code and scmst_job_code = job_code and job_comp_code = comp_code (+) and job_default_dept_code = department (+) and insv_cover_type_code = 'SUBLABOR' group by case when job_default_dept_code like 'ATT%' then job_default_dept_code else nvl(office_loc,'N/A') end order by case when job_default_dept_code like 'ATT%' then job_default_dept_code else nvl(office_loc,'N/A') end; sub_office_rec DA.SCMAST%ROWTYPE; CURSOR sub_region_cursor IS select case when job_default_dept_code like 'ATT%' then job_default_dept_code else nvl(region_cd,'N/A') end location, to_char(sum(insv_amt),'999,999,999.99') reg_hours from da.insvou, da.scmast, da.jcjob_table, da.uetd_classifier, (select vou_comp_code, vou_num, vou_ven_code, vou_cont_code from da.voucher where vou_cont_code is not null and vou_reg_inv_num = 0 and vou_post_date between in_from_date and in_to_date and vou_comp_code != '62') where insv_vou_num = vou_num and vou_comp_code = scmst_comp_code and vou_ven_code = scmst_ven_code and vou_cont_code = scmst_cont_code and scmst_chg_code = '000' and scmst_comp_code = job_comp_code and scmst_job_code = job_code and job_comp_code = comp_code (+) and job_default_dept_code = department (+) and insv_cover_type_code = 'SUBLABOR' group by case when job_default_dept_code like 'ATT%' then job_default_dept_code else nvl(region_cd,'N/A') end order by case when job_default_dept_code like 'ATT%' then job_default_dept_code else nvl(region_cd,'N/A') end; sub_region_rec DA.SCMAST%ROWTYPE; CURSOR sub_job_cursor IS select job_comp_code as job_comp_code, case when scmst_job_code is null then 'G&A' else scmst_job_code end as job_code, case when scmst_job_code is null then 'Overhead' else job_name end as job_name, to_char(sum(insv_amt),'999,999,999.99') reg_hours from da.insvou, da.scmast, da.jcjob_table, da.uetd_classifier, (select vou_comp_code, vou_num, vou_ven_code, vou_cont_code from da.voucher where vou_cont_code is not null and vou_reg_inv_num = 0 and vou_post_date between in_from_date and in_to_date and vou_comp_code != '62') where insv_vou_num = vou_num and vou_comp_code = scmst_comp_code and vou_ven_code = scmst_ven_code and vou_cont_code = scmst_cont_code and scmst_chg_code = '000' and scmst_comp_code = job_comp_code and scmst_job_code = job_code and job_comp_code = comp_code (+) and job_default_dept_code = department (+) and insv_cover_type_code = 'SUBLABOR' group by job_comp_code, case when scmst_job_code is null then 'G&A' else scmst_job_code end, case when scmst_job_code is null then 'Overhead' else job_name end order by job_comp_code, case when scmst_job_code is null then 'G&A' else scmst_job_code end; sub_job_rec DA.SCMAST%ROWTYPE; ------------------------------------------ CURSOR sub_office_ven_cursor IS select case when job_default_dept_code like 'ATT%' then job_default_dept_code else nvl(office_loc,'N/A') end location, scmst_ven_code, bp_name, to_char(nvl(sum(insv_amt),0),'999,999,999.99') reg_hours from da.insvou, da.scmast, da.bpartners_table, da.jcjob_table, da.uetd_classifier, (select vou_comp_code, vou_num, vou_ven_code, vou_cont_code from da.voucher where vou_cont_code is not null and vou_reg_inv_num = 0 and vou_post_date between in_from_date and in_to_date and vou_comp_code != '62') where insv_vou_num = vou_num and vou_comp_code = scmst_comp_code and vou_ven_code = scmst_ven_code and vou_cont_code = scmst_cont_code and scmst_chg_code = '000' and scmst_comp_code = job_comp_code and scmst_job_code = job_code and job_comp_code = comp_code (+) and job_default_dept_code = department (+) and insv_cover_type_code = 'SUBLABOR' and scmst_ven_code = bp_code (+) group by case when job_default_dept_code like 'ATT%' then job_default_dept_code else nvl(office_loc,'N/A') end, scmst_ven_code, bp_name order by case when job_default_dept_code like 'ATT%' then job_default_dept_code else nvl(office_loc,'N/A') end, scmst_ven_code; sub_office_ven_rec DA.SCMAST%ROWTYPE; CURSOR sub_region_ven_cursor IS select case when job_default_dept_code like 'ATT%' then job_default_dept_code else nvl(region_cd,'N/A') end location, scmst_ven_code, bp_name, to_char(nvl(sum(insv_amt),0),'999,999,999.99') reg_hours from da.insvou, da.scmast, da.bpartners_table, da.jcjob_table, da.uetd_classifier, (select vou_comp_code, vou_num, vou_ven_code, vou_cont_code from da.voucher where vou_cont_code is not null and vou_reg_inv_num = 0 and vou_post_date between in_from_date and in_to_date and vou_comp_code != '62') where insv_vou_num = vou_num and vou_comp_code = scmst_comp_code and vou_ven_code = scmst_ven_code and vou_cont_code = scmst_cont_code and scmst_chg_code = '000' and scmst_comp_code = job_comp_code and scmst_job_code = job_code and job_comp_code = comp_code (+) and job_default_dept_code = department (+) and insv_cover_type_code = 'SUBLABOR' and scmst_ven_code = bp_code (+) group by case when job_default_dept_code like 'ATT%' then job_default_dept_code else nvl(region_cd,'N/A') end, scmst_ven_code, bp_name order by case when job_default_dept_code like 'ATT%' then job_default_dept_code else nvl(region_cd,'N/A') end, scmst_ven_code; sub_region_ven_rec DA.SCMAST%ROWTYPE; CURSOR sub_job_ven_cursor IS select job_comp_code as job_comp_code, case when scmst_job_code is null then 'G&A' else scmst_job_code end as job_code, case when scmst_job_code is null then 'Overhead' else job_name end as job_name, scmst_ven_code, bp_name, to_char(nvl(sum(insv_amt),0),'999,999,999.99') reg_hours from da.insvou, da.scmast, da.bpartners_table, da.jcjob_table, da.uetd_classifier, (select vou_comp_code, vou_num, vou_ven_code, vou_cont_code from da.voucher where vou_cont_code is not null and vou_reg_inv_num = 0 and vou_post_date between in_from_date and in_to_date and vou_comp_code != '62') where insv_vou_num = vou_num and vou_comp_code = scmst_comp_code and vou_ven_code = scmst_ven_code and vou_cont_code = scmst_cont_code and scmst_chg_code = '000' and scmst_comp_code = job_comp_code and scmst_job_code = job_code and job_comp_code = comp_code (+) and job_default_dept_code = department (+) and insv_cover_type_code = 'SUBLABOR' and scmst_ven_code = bp_code (+) group by job_comp_code, case when scmst_job_code is null then 'G&A' else scmst_job_code end, case when scmst_job_code is null then 'Overhead' else job_name end, scmst_ven_code, bp_name order by job_comp_code, case when scmst_job_code is null then 'G&A' else scmst_job_code end, scmst_ven_code; sub_job_ven_rec DA.SCMAST%ROWTYPE; ------------------------------------------ output_line VARCHAR2 (3000); BEGIN conn := bec_mail.begin_mail (sender => 'CMiC ', recipients => user ||'<'||user||'@beckgroup.com>,', -- recipients => 'henryclark,', subject => sys_context('USERENV','DB_NAME') ||' - '|| 'Payroll Safety Hours' , mime_type => bec_mail.multipart_mime_type ); -- SMTP email text section bec_mail.attach_text (conn => conn, DATA => '
The attached file includes the safety hours data requested for '||to_char(in_from_date,'MM/DD/YYYY')||' to '||to_char(in_to_date,'MM/DD/YYYY')||UTL_TCP.CRLF||'
', mime_type => 'text/html' ); bec_mail.begin_attachment (conn => conn, mime_type => 'application/vnd.ms-excel', inline => FALSE, filename => 'SafetyHours'||to_char(in_to_date,'MMDDYY')||'.xls', transfer_enc => '' ); output_line :=(''|| UTL_TCP.CRLF ||''|| UTL_TCP.CRLF ||''|| UTL_TCP.CRLF ||''|| UTL_TCP.CRLF ||''|| UTL_TCP.CRLF ||''|| UTL_TCP.CRLF ||'Sheet1'|| UTL_TCP.CRLF || ''|| UTL_TCP.CRLF || ''|| UTL_TCP.CRLF || ''|| UTL_TCP.CRLF || ''|| UTL_TCP.CRLF ||''|| UTL_TCP.CRLF ||''|| UTL_TCP.CRLF || UTL_TCP.CRLF ); bec_mail.write_text (conn => conn, MESSAGE => output_line ); output_line :=('
YEARVERSIONEMP_EMPLOYEE_IDEMP_FIRST_NAMEEMP_LAST_NAMEEMP_HIRE_DATEEMP_TERMINATION_DATEEMP_HOME_DEPTEMP_COMMENTSEMP_EXTRA_INFO_1EMP_EXTRA_INFO_2EMP_EXTRA_INFO_3EMP_EXTRA_INFO_4EMP_EXTRA_INFO_5EMP_MAX_FTEEMP_IS_VACANTEMP_OTHER_1EMP_OTHER_2POS_POSITION_IDPOS_DESCRIPTIONPOS_HOME_DEPTPOS_GROUP_IDPOS_HRLY_OR_SALARYPOS_PAY_CYCLE_OVERRIDEPOS_HOME_GROUPPOS_STD_HRS_OVERRIDEPOS_OVERRIDE_PAY_CYCLEPOS_OVERRIDE_HOME_GROUPPOS_OVERRIDE_STD_HRSPOS_CLASSIFICATIONPOS_STATUSPOS_OTHER_ATTRIBSTP_STEP_IDSTP_DESCRIPTIONSTP_MAX_NO_FTESTP_ALLOTTED_HRSSTP_MAX_ANNUALSTP_MAX_HOURSTP_COMP_TYPESTP_ANN_COMP_PER_FTESTP_HRLY_COMP_PER_FTESTP_COMP_APPLY_MAXSTP_EFFECTIVE_DATE_OVERRIDESTP_OVERRIDE_EFF_DATEEPS_FTE_FULL_YEAREPS_START_DATEEPS_END_DATEEPS_COMP_TYPEEPS_ANN_COMP_PER_FTEEPS_HRLY_COMP_PER_FTEEPS_EFFECTIVE_DATE_OVERRIDEEPS_OVERRIDE_EFFECTIVE_DATEEBO_BENEFITEBO_INCLUDEEBO_OVERRIDEEBO_OVBERRIDE_AMOUNT
'||sal_rec.sps_year||''||sal_rec.sps_version||''||sal_rec.sps_emp_id||''||sal_rec.sps_first_name||''||sal_rec.sps_last_name||''||sal_rec.sps_hire_date||''||sal_rec.sps_term_date||''||sal_rec.sps_home_dept||''||sal_rec.sps_comments||''||sal_rec.sps_extra_info_1||''||sal_rec.sps_extra_info_2||''||sal_rec.sps_extra_info_3||''||sal_rec.sps_extra_info_4||''||sal_rec.sps_extra_info_5||''||sal_rec.sps_max_fte||''||sal_rec.sps_is_vacant||''||sal_rec.sps_other_1||''||sal_rec.sps_other_2||''||sal_rec.sps_position_id||''||sal_rec.sps_desc||''||sal_rec.sps_pos_home_dept||''||sal_rec.sps_pos_group_id||''||sal_rec.sps_hrly_or_salary||''||sal_rec.sps_pay_cycle_override||''||sal_rec.sps_home_group||''||sal_rec.sps_std_hrs_override||''||sal_rec.sps_override_pay_cycle||''||sal_rec.sps_override_home_group||''||sal_rec.sps_override_std_hrs||''||sal_rec.sps_pos_classification||''||sal_rec.sps_status||''||sal_rec.sps_other_attrib||''||sal_rec.sps_step_id||''||sal_rec.sps_description||''||sal_rec.sps_stp_max_no_fte||''||sal_rec.sps_stp_alloted_hrs||''||sal_rec.sps_stp_max_annual||''||sal_rec.sps_stp_hour||''||sal_rec.sps_stp_comp_type||''||sal_rec.sps_stp_ann_comp_per_fte||''||sal_rec.sps_stp_hrly_comp_per_fte||''||sal_rec.sps_stp_comp_apply_max||''||sal_rec.sps_stp_eff_date_override||''||sal_rec.sps_stp_override_eff_date||''||sal_rec.sps_eps_fte_full_year||''||sal_rec.sps_eps_start_date||''||sal_rec.sps_eps_end_date||''||sal_rec.sps_eps_comp_type||''||sal_rec.sps_eps_ann_comp_per_fte||''||sal_rec.sps_eps_hrly_comp_per_fte||''||sal_rec.sps_eps_eff_date_override||''||sal_rec.sps_eps_override_eff_date||''||sal_rec.sps_ebo_benefit||''||sal_rec.sps_ebo_include||''||sal_rec.sps_ebo_override||''||sal_rec.sps_ebo_override_amount||'
' || UTL_TCP.CRLF ); bec_mail.write_text (conn => conn, MESSAGE => output_line ); if (in_type = 'O' and in_sc = 'N' and in_vendor = 'N') then output_line := ''; output_line := output_line||''; output_line := output_line||''; output_line := output_line|| UTL_TCP.CRLF; bec_mail.write_text (conn => conn, MESSAGE => output_line ); FOR office_rec IN office_cursor LOOP output_line := ''; output_line := output_line||''; output_line := output_line||''; output_line := output_line|| UTL_TCP.CRLF; bec_mail.write_text (conn => conn, MESSAGE => output_line ); END LOOP; select sum(nvl(tsh_normal_hours,0)+nvl(tsh_ot_hours,0)+nvl(tsh_dot_hours,0)) into summary_hours from da.pyemptimsht, da.uetd_classifier where tsh_work_comp_code = comp_code (+) and tsh_dept_code = department (+) and tsh_ppr_year >= to_char(in_from_date,'yyyy') and tsh_date between in_from_date and in_to_date; output_line := ''; output_line := output_line||''; output_line := output_line||''; output_line := output_line|| UTL_TCP.CRLF; bec_mail.write_text (conn => conn, MESSAGE => output_line ); end if; if (in_type = 'R' and in_sc = 'N' and in_vendor = 'N') then output_line := ''; output_line := output_line||''; output_line := output_line||''; output_line := output_line|| UTL_TCP.CRLF; bec_mail.write_text (conn => conn, MESSAGE => output_line ); FOR region_rec IN region_cursor LOOP output_line := ''; output_line := output_line||''; output_line := output_line||''; output_line := output_line|| UTL_TCP.CRLF; bec_mail.write_text (conn => conn, MESSAGE => output_line ); END LOOP; select sum(nvl(tsh_normal_hours,0)+nvl(tsh_ot_hours,0)+nvl(tsh_dot_hours,0)) into summary_hours from da.pyemptimsht, da.uetd_classifier where tsh_work_comp_code = comp_code (+) and tsh_dept_code = department (+) and tsh_ppr_year >= to_char(in_from_date,'yyyy') and tsh_date between in_from_date and in_to_date; output_line := ''; output_line := output_line||''; output_line := output_line||''; output_line := output_line|| UTL_TCP.CRLF; bec_mail.write_text (conn => conn, MESSAGE => output_line ); end if; if (in_type = 'J' and in_sc = 'N' and in_vendor = 'N') then output_line := ''; output_line := output_line||''; output_line := output_line||''; output_line := output_line||''; output_line := output_line||''; output_line := output_line|| UTL_TCP.CRLF; bec_mail.write_text (conn => conn, MESSAGE => output_line ); FOR job_rec IN job_cursor LOOP output_line := ''; output_line := output_line||''; output_line := output_line||''; output_line := output_line||''; output_line := output_line||''; output_line := output_line|| UTL_TCP.CRLF; bec_mail.write_text (conn => conn, MESSAGE => output_line ); END LOOP; select sum(nvl(tsh_normal_hours,0)+nvl(tsh_ot_hours,0)+nvl(tsh_dot_hours,0)) into summary_hours from da.pyemptimsht, da.jcjob_table where tsh_work_comp_code = job_comp_code (+) and tsh_job_code = job_code (+) and tsh_ppr_year >= to_char(in_from_date,'yyyy') and tsh_date between in_from_date and in_to_date; output_line := ''; output_line := output_line||''; output_line := output_line||''; output_line := output_line||''; output_line := output_line||''; output_line := output_line|| UTL_TCP.CRLF; bec_mail.write_text (conn => conn, MESSAGE => output_line ); end if; if (in_type = 'O' and in_sc = 'Y' and in_vendor = 'N') then output_line := ''; output_line := ''; output_line := output_line|| UTL_TCP.CRLF; bec_mail.write_text (conn => conn, MESSAGE => output_line ); output_line := ''; output_line := output_line||''; output_line := output_line||''; output_line := output_line|| UTL_TCP.CRLF; bec_mail.write_text (conn => conn, MESSAGE => output_line ); FOR sub_office_rec IN sub_office_cursor LOOP output_line := ''; output_line := output_line||''; output_line := output_line||''; output_line := output_line|| UTL_TCP.CRLF; bec_mail.write_text (conn => conn, MESSAGE => output_line ); END LOOP; select sum(insv_amt) reg_hours into summary_hours from da.insvou, da.scmast, da.jcjob_table, da.uetd_classifier, (select vou_comp_code, vou_num, vou_ven_code, vou_cont_code from da.voucher where vou_cont_code is not null and vou_reg_inv_num = 0 and vou_post_date between in_from_date and in_to_date and vou_comp_code != '62') where insv_vou_num = vou_num and vou_comp_code = scmst_comp_code and vou_ven_code = scmst_ven_code and vou_cont_code = scmst_cont_code and scmst_chg_code = '000' and scmst_comp_code = job_comp_code and scmst_job_code = job_code and job_comp_code = comp_code (+) and job_default_dept_code = department (+) and insv_cover_type_code = 'SUBLABOR'; output_line := ''; output_line := output_line||''; output_line := output_line||''; output_line := output_line|| UTL_TCP.CRLF; bec_mail.write_text (conn => conn, MESSAGE => output_line ); output_line := ''||UTL_TCP.CRLF|| UTL_TCP.CRLF; bec_mail.write_text (conn => conn, MESSAGE => output_line ); end If; if (in_type = 'R' and in_sc = 'Y' and in_vendor = 'N') then output_line := ''; output_line := output_line||''; output_line := output_line|| UTL_TCP.CRLF; bec_mail.write_text (conn => conn, MESSAGE => output_line ); output_line := ''; output_line := output_line||''; output_line := output_line||''; output_line := output_line|| UTL_TCP.CRLF; bec_mail.write_text (conn => conn, MESSAGE => output_line ); FOR sub_region_rec IN sub_region_cursor LOOP output_line := ''; output_line := output_line||''; output_line := output_line||''; output_line := output_line|| UTL_TCP.CRLF; bec_mail.write_text (conn => conn, MESSAGE => output_line ); END LOOP; select sum(insv_amt) into summary_hours from da.insvou, da.scmast, da.jcjob_table, da.uetd_classifier, (select vou_comp_code, vou_num, vou_ven_code, vou_cont_code from da.voucher where vou_cont_code is not null and vou_reg_inv_num = 0 and vou_post_date between in_from_date and in_to_date and vou_comp_code != '62') where insv_vou_num = vou_num and vou_comp_code = scmst_comp_code and vou_ven_code = scmst_ven_code and vou_cont_code = scmst_cont_code and scmst_chg_code = '000' and scmst_comp_code = job_comp_code and scmst_job_code = job_code and job_comp_code = comp_code (+) and job_default_dept_code = department (+) and insv_cover_type_code = 'SUBLABOR'; output_line := ''; output_line := output_line||''; output_line := output_line||''; output_line := output_line|| UTL_TCP.CRLF; bec_mail.write_text (conn => conn, MESSAGE => output_line ); end if; if (in_type = 'J' and in_sc = 'Y' and in_vendor = 'N') then output_line := ''; output_line := output_line||''; output_line := output_line|| UTL_TCP.CRLF; bec_mail.write_text (conn => conn, MESSAGE => output_line ); output_line := ''; output_line := output_line||''; output_line := output_line||''; output_line := output_line||''; output_line := output_line||''; output_line := output_line|| UTL_TCP.CRLF; bec_mail.write_text (conn => conn, MESSAGE => output_line ); FOR sub_job_rec IN sub_job_cursor LOOP output_line := ''; output_line := output_line||''; output_line := output_line||''; output_line := output_line||''; output_line := output_line||''; output_line := output_line|| UTL_TCP.CRLF; bec_mail.write_text (conn => conn, MESSAGE => output_line ); END LOOP; select sum(insv_amt) into summary_hours from da.insvou, da.scmast, da.jcjob_table, da.uetd_classifier, (select vou_comp_code, vou_num, vou_ven_code, vou_cont_code from da.voucher where vou_cont_code is not null and vou_reg_inv_num = 0 and vou_post_date between in_from_date and in_to_date and vou_comp_code != '62') where insv_vou_num = vou_num and vou_comp_code = scmst_comp_code and vou_ven_code = scmst_ven_code and vou_cont_code = scmst_cont_code and scmst_chg_code = '000' and scmst_comp_code = job_comp_code and scmst_job_code = job_code and job_comp_code = comp_code (+) and job_default_dept_code = department (+) and insv_cover_type_code = 'SUBLABOR'; output_line := ''; output_line := output_line||''; output_line := output_line||''; output_line := output_line||''; output_line := output_line||''; output_line := output_line|| UTL_TCP.CRLF; bec_mail.write_text (conn => conn, MESSAGE => output_line ); end if; ----------------------------------- if (in_type = 'O' and in_sc = 'N' and in_vendor = 'Y') then output_line := ''; output_line := ''; output_line := output_line|| UTL_TCP.CRLF; bec_mail.write_text (conn => conn, MESSAGE => output_line ); output_line := ''; output_line := output_line||''; output_line := output_line||''; output_line := output_line||''; output_line := output_line||''; output_line := output_line|| UTL_TCP.CRLF; bec_mail.write_text (conn => conn, MESSAGE => output_line ); FOR sub_office_ven_rec IN sub_office_ven_cursor LOOP output_line := ''; output_line := output_line||''; output_line := output_line||''; output_line := output_line||''; output_line := output_line||''; output_line := output_line|| UTL_TCP.CRLF; bec_mail.write_text (conn => conn, MESSAGE => output_line ); END LOOP; select sum(insv_amt) reg_hours into summary_hours from da.insvou, da.scmast, da.jcjob_table, da.uetd_classifier, (select vou_comp_code, vou_num, vou_ven_code, vou_cont_code from da.voucher where vou_cont_code is not null and vou_reg_inv_num = 0 and vou_post_date between in_from_date and in_to_date and vou_comp_code != '62') where insv_vou_num = vou_num and vou_comp_code = scmst_comp_code and vou_ven_code = scmst_ven_code and vou_cont_code = scmst_cont_code and scmst_chg_code = '000' and scmst_comp_code = job_comp_code and scmst_job_code = job_code and job_comp_code = comp_code (+) and job_default_dept_code = department (+) and insv_cover_type_code = 'SUBLABOR'; output_line := ''; output_line := output_line||''; output_line := output_line||''; output_line := output_line||''; output_line := output_line||''; output_line := output_line|| UTL_TCP.CRLF; bec_mail.write_text (conn => conn, MESSAGE => output_line ); output_line := ''||UTL_TCP.CRLF|| UTL_TCP.CRLF; bec_mail.write_text (conn => conn, MESSAGE => output_line ); end If; if (in_type = 'R' and in_sc = 'N' and in_vendor = 'Y') then output_line := ''; output_line := output_line||''; output_line := output_line|| UTL_TCP.CRLF; bec_mail.write_text (conn => conn, MESSAGE => output_line ); output_line := ''; output_line := output_line||''; output_line := output_line||''; output_line := output_line||''; output_line := output_line||''; output_line := output_line|| UTL_TCP.CRLF; bec_mail.write_text (conn => conn, MESSAGE => output_line ); FOR sub_region_ven_rec IN sub_region_ven_cursor LOOP output_line := ''; output_line := output_line||''; output_line := output_line||''; output_line := output_line||''; output_line := output_line||''; output_line := output_line|| UTL_TCP.CRLF; bec_mail.write_text (conn => conn, MESSAGE => output_line ); END LOOP; select sum(insv_amt) into summary_hours from da.insvou, da.scmast, da.jcjob_table, da.uetd_classifier, (select vou_comp_code, vou_num, vou_ven_code, vou_cont_code from da.voucher where vou_cont_code is not null and vou_reg_inv_num = 0 and vou_post_date between in_from_date and in_to_date and vou_comp_code != '62') where insv_vou_num = vou_num and vou_comp_code = scmst_comp_code and vou_ven_code = scmst_ven_code and vou_cont_code = scmst_cont_code and scmst_chg_code = '000' and scmst_comp_code = job_comp_code and scmst_job_code = job_code and job_comp_code = comp_code (+) and job_default_dept_code = department (+) and insv_cover_type_code = 'SUBLABOR'; output_line := ''; output_line := output_line||''; output_line := output_line||''; output_line := output_line||''; output_line := output_line||''; output_line := output_line|| UTL_TCP.CRLF; bec_mail.write_text (conn => conn, MESSAGE => output_line ); end if; if (in_type = 'J' and in_sc = 'N' and in_vendor = 'Y') then output_line := ''; output_line := output_line||''; output_line := output_line|| UTL_TCP.CRLF; bec_mail.write_text (conn => conn, MESSAGE => output_line ); output_line := ''; output_line := output_line||''; output_line := output_line||''; output_line := output_line||''; output_line := output_line||''; output_line := output_line||''; output_line := output_line||''; output_line := output_line|| UTL_TCP.CRLF; bec_mail.write_text (conn => conn, MESSAGE => output_line ); FOR sub_job_ven_rec IN sub_job_ven_cursor LOOP output_line := ''; output_line := output_line||''; output_line := output_line||''; output_line := output_line||''; output_line := output_line||''; output_line := output_line||''; output_line := output_line||''; output_line := output_line|| UTL_TCP.CRLF; bec_mail.write_text (conn => conn, MESSAGE => output_line ); END LOOP; select sum(insv_amt) into summary_hours from da.insvou, da.scmast, da.jcjob_table, da.uetd_classifier, (select vou_comp_code, vou_num, vou_ven_code, vou_cont_code from da.voucher where vou_cont_code is not null and vou_reg_inv_num = 0 and vou_post_date between in_from_date and in_to_date and vou_comp_code != '62') where insv_vou_num = vou_num and vou_comp_code = scmst_comp_code and vou_ven_code = scmst_ven_code and vou_cont_code = scmst_cont_code and scmst_chg_code = '000' and scmst_comp_code = job_comp_code and scmst_job_code = job_code and job_comp_code = comp_code (+) and job_default_dept_code = department (+) and insv_cover_type_code = 'SUBLABOR'; output_line := ''; output_line := output_line||''; output_line := output_line||''; output_line := output_line||''; output_line := output_line||''; output_line := output_line||''; output_line := output_line||''; output_line := output_line|| UTL_TCP.CRLF; bec_mail.write_text (conn => conn, MESSAGE => output_line ); end if; ----------------------------------- bec_mail.end_attachment (conn =>conn); bec_mail.end_mail (conn => conn ); END; PROCEDURE bec_hdhp_email( in_year IN NUMBER, in_period IN NUMBER) AS -- Procedure generates asset import file and emails it to current user. -- If local db output file is needed, uncomment output_path VARCHAR2 (50); output_filename VARCHAR2 (20); output_line VARCHAR2 (750); -- output_file UTL_FILE.file_type; -- SMTP email variables conn UTL_SMTP.connection; req UTL_HTTP.req; resp UTL_HTTP.resp; DATA RAW (20000); cursor_count number:=0; tab varchar2(2):=CHR(9); CURSOR output_cursor IS select c.emp_no, a.phy_ppr_year, a.phy_ppr_period, a.phy_prn_code, phy_tran_code, phy_amount, ess_split_percent from pyemppayhist a join pyempsalspl b on a.phy_emp_no=b.ess_emp_no join pyemployee_table_tn c on a.phy_emp_no=c.emp_no where phy_ppr_year=in_year and phy_ppr_period=in_period and phy_tran_code in ('415','1290' ) and ess_bank_code='124384877' and phy_amount <> ess_split_percent; -- create a instance of the cursor sal_rec output_cursor%ROWTYPE; BEGIN -- SMTP Email Header section conn := bec_mail.begin_mail (sender => 'CMiC ', recipients => user ||'<'||user||'@beckgroup.com>', subject => sys_context('USERENV','DB_NAME') ||' - '|| 'HDHP Mismatch Report', mime_type => bec_mail.multipart_mime_type ); -- SMTP email text section bec_mail.attach_text (conn => conn, DATA => '
The attached file includes HDHP Mismatch records.
', mime_type => 'text/html' ); -- SMTP email attachement start section bec_mail.begin_attachment (conn => conn, mime_type => 'application/vnd.ms-excel', inline => FALSE, filename => 'HDHPMismatch'||to_char(in_year)||to_char(in_period) ||'.xls', transfer_enc => '' ); output_line :=(''|| UTL_TCP.CRLF ||''|| UTL_TCP.CRLF ||''|| UTL_TCP.CRLF ||''|| UTL_TCP.CRLF ||''|| UTL_TCP.CRLF ||''|| UTL_TCP.CRLF ||'Sheet1'|| UTL_TCP.CRLF ||''|| UTL_TCP.CRLF || ''|| UTL_TCP.CRLF || ''|| UTL_TCP.CRLF || ''|| UTL_TCP.CRLF ||''|| UTL_TCP.CRLF ||''|| UTL_TCP.CRLF || UTL_TCP.CRLF ); bec_mail.write_text (conn => conn, MESSAGE => output_line ); output_line :=('
OfficeRegular Hours
'||office_rec.location||''||office_rec.reg_hours||'
'||'*** Total ***'||''||to_char(summary_hours,'999,999,999.00')||'
RegionRegular Hours
'||region_rec.location||''||region_rec.reg_hours||'
'||'*** Total ***'||''||to_char(summary_hours,'999,999,999.00')||'
CompanyJobJob nameRegular Hours
'||'​'||job_rec.tsh_comp_code||'​'||''||'​'||job_rec.tsh_job_code||'​'||''||job_rec.tsh_job_name||''||job_rec.tsh_reg_hours||'
'||' '||''||' '||''||'*** Total ***'||''||to_char(summary_hours,'999,999,999.00')||'
Sub Hours By Office
OfficeRegular Hours
'||sub_office_rec.location||''||sub_office_rec.reg_hours||'
'||'*** Total ***'||''||to_char(summary_hours,'999,999,999.00')||'
Sub Hours By Region
RegionRegular Hours
'||sub_region_rec.location||''||sub_region_rec.reg_hours||'
'||'*** Total ***'||''||to_char(summary_hours,'999,999,999.00')||'
Sub Hours By Job
CompanyJobJob NameRegular Hours
'||'​'||sub_job_rec.job_comp_code||'​'||''||'​'||sub_job_rec.job_code||'​'||''||sub_job_rec.job_name||''||sub_job_rec.reg_hours||'
'||' '||''||' '||''||'*** Total ***'||''||to_char(summary_hours,'999,999,999.00')||'
Sub Hours By Office/Vendor
OfficeVendorVendor NameRegular Hours
'||sub_office_ven_rec.location||''||sub_office_ven_rec.scmst_ven_code||''||sub_office_ven_rec.bp_name||''||sub_office_ven_rec.reg_hours||'
'||' '||''||' '||''||'*** Total ***'||''||to_char(summary_hours,'999,999,999.00')||'
Sub Hours By Region/Vendor
RegionVendorVendor NameRegular Hours
'||sub_region_ven_rec.location||''||sub_region_ven_rec.scmst_ven_code||''||sub_region_ven_rec.bp_name||''||sub_region_ven_rec.reg_hours||'
'||' '||''||' '||''||'*** Total ***'||''||to_char(summary_hours,'999,999,999.00')||'
Sub Hours By Job/Vendor
CompanyJobJob NameVendorVendor NameRegular Hours
'||'​'||sub_job_ven_rec.job_comp_code||'​'||''||'​'||sub_job_ven_rec.job_code||'​'||''||sub_job_ven_rec.job_name||''||sub_job_ven_rec.scmst_ven_code||''||sub_job_ven_rec.bp_name||''||sub_job_ven_rec.reg_hours||'
'||' '||''||' '||''||' '||''||' '||''||'*** Total ***'||''||to_char(summary_hours,'999,999,999.00')||'
' || UTL_TCP.CRLF ); bec_mail.write_text (conn => conn, MESSAGE => output_line ); output_line :=( ''|| UTL_TCP.CRLF ); bec_mail.write_text (conn => conn, MESSAGE => output_line ); -- Loop thru sal_rec -- First time to gather count of cursor to remove last ctrl feed. FOR sal_rec IN output_cursor LOOP cursor_count:= output_cursor%ROWCOUNT; if cursor_count>64000 then output_line :=('' || UTL_TCP.CRLF ); bec_mail.write_text (conn => conn, MESSAGE => output_line ); goto escape; end if; END LOOP; if cursor_count=0 then output_line :=('' || UTL_TCP.CRLF ); bec_mail.write_text (conn => conn, MESSAGE => output_line ); goto escape; end if; -- Loop thru sal_rec -- Please note the UTL_TCP.CRLF is to place a hard return in email file. FOR sal_rec IN output_cursor LOOP output_line := '' ||'' ||'' ||'' ||'' ||'' ||''; if (output_cursor%ROWCOUNT=cursor_count) THEN output_line:= output_line || ''; ELSE output_line:= output_line || UTL_TCP.CRLF; end if; -- SMTP write one line to attachment bec_mail.write_text (conn => conn, MESSAGE => output_line ); -- UTL_FILE.PUT_LINE (output_file, output_line); END LOOP; <> output_line :=('
EmployeeYearPeriodPay RunTrans CodeAmountSalary Rate
Data exceeds 64000 rows.
No data found.
'||sal_rec.EMP_NO||''|| sal_rec.PHY_PPR_YEAR||''|| sal_rec.PHY_PPR_PERIOD||''|| sal_rec.PHY_PRN_CODE||''|| sal_rec.PHY_TRAN_CODE||''|| to_char(sal_rec.PHY_AMOUNT,00000.00)||''|| to_char(sal_rec.ESS_SPLIT_PERCENT,00000.00)||'
'||UTL_TCP.CRLF||''|| UTL_TCP.CRLF|| UTL_TCP.CRLF ); bec_mail.write_text (conn => conn, MESSAGE => output_line ); bec_mail.end_mail (conn => conn); END; PROCEDURE bec_colorado_wage_rpt is output_line VARCHAR2 (10000):= ''; conn UTL_SMTP.connection; p_job_cust_group varchar2(1); p_mem_chk_detail varchar2(1); p_cust_class_code varchar2(1); CURSOR wage_cursor IS select a.emp_trd_code, a.emp_sui_state, a.emp_sex, a.emp_prn_code, add_months(trunc(sysdate,'mm'),0)+11 report_date, b.phy_actual_pay_date, count(distinct a.emp_no) emp_count, sum(hrs_paid) hrs_paid, sum(state_wages) state_wage from pyemployee_table_tn a join (select phy_emp_no, phy_prn_code, phy_ppr_year, phy_actual_pay_date, sum(nvl(phy_hrs_paid,0)) hrs_paid from pyemppayhist where phy_ppr_year=to_char(add_months(trunc(sysdate,'mm'),0),'yyyy') and phy_pay_amount != 0 group by phy_emp_no, phy_prn_code, phy_ppr_year, phy_actual_pay_date) b on a.emp_no=b.phy_emp_no join (select phy_emp_no, phy_actual_pay_date, sum(phy_gross_earning) state_wages from pyemppayhist where phy_ppr_year=to_char(add_months(trunc(sysdate,'mm'),0),'yyyy') and phy_tran_code in ('STA') group by phy_emp_no, phy_actual_pay_date) c on b.phy_emp_no=c.phy_emp_no and b.phy_actual_pay_date=c.phy_actual_pay_date join pycompayprd d on b.phy_ppr_year=ppr_year and b.phy_prn_code=d.ppr_prn_code and add_months(trunc(sysdate,'mm'),0)+11 between ppr_start_date and ppr_end_date and d.ppr_pay_date=b.phy_actual_pay_date where a.emp_sui_state='CO' --and d.ppr_prn_code in ('BW','WK') --and emp_trd_code='POM3' group by a.emp_trd_code, a.emp_sui_state, a.emp_sex, a.emp_prn_code, add_months(trunc(sysdate,'mm'),0)+11, b.phy_actual_pay_date order by a.emp_prn_code, a.emp_trd_code, a.emp_sex; BEGIN -- SMTP Email Header section conn := bec_mail.begin_mail (sender => 'cmic ', -- recipients => 'henryclark@beckgroup.com', recipients => 'lindadicus@beckgroup.com; henryclark@beckgroup.com', subject => sys_context('USERENV','DB_NAME') ||' - '|| 'Colorado State Wage Report Data', mime_type => bec_mail.multipart_mime_type ); -- SMTP email text section bec_mail.attach_text (conn => conn, DATA => '
The attached file includes the requested state wage data.
', mime_type => 'text/html' ); -- SMTP email attachement start section bec_mail.begin_attachment (conn => conn, mime_type => 'application/vnd.ms-excel', inline => FALSE, filename => 'colorado_state_employee_report_'||to_char(add_months(trunc(sysdate,'mm'),0)+11, 'mmddyy') ||'.xls', transfer_enc => '' ); output_line :=('' ||utl_tcp.crlf ||''|| utl_tcp.crlf ||''|| utl_tcp.crlf ||''||utl_tcp.crlf ||''|| utl_tcp.crlf ||''|| utl_tcp.crlf ||'Sheet1'|| utl_tcp.crlf || ''|| utl_tcp.crlf || ''|| utl_tcp.crlf || ''|| utl_tcp.crlf || ''||utl_tcp.crlf ||''||utl_tcp.crlf ||''||utl_tcp.crlf ||utl_tcp.crlf ); bec_mail.write_text (conn => conn, MESSAGE => output_line ); output_line :=('' ||utl_tcp.crlf); bec_mail.write_text (conn => conn, MESSAGE => output_line ); output_line := ''; output_line := output_line||''; output_line := output_line||''; output_line := output_line||''; output_line := output_line||''; output_line := output_line||''; output_line := output_line||''; output_line := output_line||''; output_line := output_line||''; output_line := output_line||''; output_line := output_line||utl_tcp.crlf; bec_mail.write_text (conn => conn, MESSAGE => output_line ); FOR rec IN wage_cursor LOOP output_line := ''; output_line := output_line||''; output_line := output_line||''; output_line := output_line||''; output_line := output_line||''; output_line := output_line||''; output_line := output_line||''; output_line := output_line||''; output_line := output_line||''; output_line := output_line||''; output_line := output_line||''||utl_tcp.crlf; bec_mail.write_text (conn => conn, MESSAGE => output_line ); END LOOP; output_line :=('
EMP_TRD_CODEEMP_SUI_STATEEMP_SEXEMP_PRN_CODEREPORT_DATEPHY_ACTUAL_PAY_DATEEMP_COUNTHRS_PAIDSTATE_WAGES
'||rec.emp_trd_code||''||rec.emp_sui_state||''||rec.emp_sex||''||rec.emp_prn_code||''||to_char(rec.report_date,'mm/dd/yyyy')||''||to_char(rec.phy_actual_pay_date,'mm/dd/yyyy')||''|| to_char(rec.emp_count,'999,999,990.99')||''|| to_char(rec.hrs_paid,'999,999,990.99')||''|| to_char(rec.state_wage,'999,999,990.99')||'
'||utl_tcp.crlf||''|| utl_tcp.crlf || utl_tcp.crlf); bec_mail.write_text (conn => conn, MESSAGE => output_line ); bec_mail.end_mail (conn => conn); end; PROCEDURE bec_texas_wage_rpt is output_line VARCHAR2 (10000):= ''; conn UTL_SMTP.connection; p_job_cust_group varchar2(1); p_mem_chk_detail varchar2(1); p_cust_class_code varchar2(1); CURSOR wage_cursor IS select a.emp_trd_code, a.emp_sui_state, a.emp_sex, a.emp_prn_code, add_months(trunc(sysdate,'mm'),0)+11 report_date, b.phy_actual_pay_date, count(distinct a.emp_no) emp_count, sum(hrs_paid) hrs_paid, sum(state_wages) state_wage from pyemployee_table_tn a join (select phy_emp_no, phy_prn_code, phy_ppr_year, phy_actual_pay_date, sum(nvl(phy_hrs_paid,0)) hrs_paid from pyemppayhist where phy_ppr_year=to_char(add_months(trunc(sysdate,'mm'),0),'yyyy') and phy_pay_amount != 0 group by phy_emp_no, phy_prn_code, phy_ppr_year, phy_actual_pay_date) b on a.emp_no=b.phy_emp_no join (select phy_emp_no, phy_actual_pay_date, sum(phy_gross_earning) state_wages from pyemppayhist where phy_ppr_year=to_char(add_months(trunc(sysdate,'mm'),0),'yyyy') and phy_tran_code in ('FED') group by phy_emp_no, phy_actual_pay_date) c on b.phy_emp_no=c.phy_emp_no and b.phy_actual_pay_date=c.phy_actual_pay_date join pycompayprd d on b.phy_ppr_year=ppr_year and b.phy_prn_code=d.ppr_prn_code and add_months(trunc(sysdate,'mm'),0)+11 between ppr_start_date and ppr_end_date and d.ppr_pay_date=b.phy_actual_pay_date where a.emp_sui_state='TX' --and d.ppr_prn_code in ('BW','WK') --and emp_trd_code='POM3' group by a.emp_trd_code, a.emp_sui_state, a.emp_sex, a.emp_prn_code, add_months(trunc(sysdate,'mm'),0)+11, b.phy_actual_pay_date order by a.emp_prn_code, a.emp_trd_code, a.emp_sex; BEGIN -- SMTP Email Header section conn := bec_mail.begin_mail (sender => 'cmic ', -- recipients => 'henryclark@beckgroup.com', recipients => 'lindadicus@beckgroup.com; henryclark@beckgroup.com', subject => sys_context('USERENV','DB_NAME') ||' - '|| 'Texas State Wage Report Data', mime_type => bec_mail.multipart_mime_type ); -- SMTP email text section bec_mail.attach_text (conn => conn, DATA => '
The attached file includes the requested state wage data.
', mime_type => 'text/html' ); -- SMTP email attachement start section bec_mail.begin_attachment (conn => conn, mime_type => 'application/vnd.ms-excel', inline => FALSE, filename => 'texas_state_employee_report_'||to_char(add_months(trunc(sysdate,'mm'),0)+11, 'mmddyy') ||'.xls', transfer_enc => '' ); output_line :=('' ||utl_tcp.crlf ||''|| utl_tcp.crlf ||''|| utl_tcp.crlf ||''||utl_tcp.crlf ||''|| utl_tcp.crlf ||''|| utl_tcp.crlf ||'Sheet1'|| utl_tcp.crlf || ''|| utl_tcp.crlf || ''|| utl_tcp.crlf || ''|| utl_tcp.crlf || ''||utl_tcp.crlf ||''||utl_tcp.crlf ||''||utl_tcp.crlf ||utl_tcp.crlf ); bec_mail.write_text (conn => conn, MESSAGE => output_line ); output_line :=('' ||utl_tcp.crlf); bec_mail.write_text (conn => conn, MESSAGE => output_line ); output_line := ''; output_line := output_line||''; output_line := output_line||''; output_line := output_line||''; output_line := output_line||''; output_line := output_line||''; output_line := output_line||''; output_line := output_line||''; output_line := output_line||''; output_line := output_line||''; output_line := output_line||utl_tcp.crlf; bec_mail.write_text (conn => conn, MESSAGE => output_line ); FOR rec IN wage_cursor LOOP output_line := ''; output_line := output_line||''; output_line := output_line||''; output_line := output_line||''; output_line := output_line||''; output_line := output_line||''; output_line := output_line||''; output_line := output_line||''; output_line := output_line||''; output_line := output_line||''; output_line := output_line||''||utl_tcp.crlf; bec_mail.write_text (conn => conn, MESSAGE => output_line ); END LOOP; output_line :=('
EMP_TRD_CODEEMP_SUI_STATEEMP_SEXEMP_PRN_CODEREPORT_DATEPHY_ACTUAL_PAY_DATEEMP_COUNTHRS_PAIDSTATE_WAGES
'||rec.emp_trd_code||''||rec.emp_sui_state||''||rec.emp_sex||''||rec.emp_prn_code||''||to_char(rec.report_date,'mm/dd/yyyy')||''||to_char(rec.phy_actual_pay_date,'mm/dd/yyyy')||''|| to_char(rec.emp_count,'999,999,990.99')||''|| to_char(rec.hrs_paid,'999,999,990.99')||''|| to_char(rec.state_wage,'999,999,990.99')||'
'||utl_tcp.crlf||''|| utl_tcp.crlf || utl_tcp.crlf); bec_mail.write_text (conn => conn, MESSAGE => output_line ); bec_mail.end_mail (conn => conn); end; PROCEDURE finish_sheet (msg IN OUT CLOB) IS msg_line VARCHAR2 (8000); BEGIN msg_line := ' ' || UTL_TCP.crlf; msg_line := msg_line || ' ' || UTL_TCP.crlf; msg_line := msg_line || ' ' || UTL_TCP.crlf; msg_line := msg_line || '
' || UTL_TCP.crlf; msg_line := msg_line || '