Oracle Technical Consultant Information: Concurrent Program Send To Email

1. Execute Packages.

Open new session with putty,

a. Set environment variable

b. Goto the following directory as

cd /opt/oracle/db/tech_st/11.1.0/rdbms/admin

c. connect with sqlplus and execute ‘utlmail.sql’ & ‘prvtmail.plb’ as following

Note: These are oracle standards packages and already exist in above directory and we have to only execute them as following

1. Create customize table. (HR_PAYSLIP_TEMP)

2. Create Directory. (ERP_DIR)

3. Create Following Customize Procedure. (HR_SUBMIT_PAYSLIP )

4. Create Following Customize Procedure. (HR_SEND_MAIL)

5. Create Following Customize Procedure. (HR_SEND_MAIL2)

Defining executables and programs:

· 1.1

· 1.2

· 1.3

When we submit the following request it runs & generates payslips of all employees.

· 2.1

· 2.2

· 2.3

When we submit the following request it attached payslip of respective person and mail it to all/particulars employees.

Priority wise request:

1. IFL - Submit Payslip Request – AFF

2. IFL - Send Email Payslip – AFF

CREATE OR REPLACE DIRECTORY

IFL_ERP_DIR AS

'/home/oracle/opt/d01/PROD/inst/apps/PROD_prodtest/logs/appl/conc/out';

GRANT EXECUTE, READ, WRITE ON DIRECTORY IFL_ERP_DIR TO APPS WITH GRANT OPTION;

GRANT EXECUTE, READ, WRITE ON DIRECTORY IFL_ERP_DIR TO SYSTEM WITH GRANT OPTION;

CREATE TABLE IFL_HR_PAYSLIP_TEMP

(

EMPLOYEE_ID NUMBER,

PERIOD_ID DATE,

REQUEST_ID NUMBER,

EMAIL CHAR(1 BYTE)

)

List of objects:

ERP_DIR directory report output
HR_PAYSLIP_TEMP table
HR_SEND_MAIL procedure
HR_SEND_MAIL2 procedure
HR_SUBMIT_PAYSLIP procedure

DROP DIRECTORY ERP_DIR;

CREATE OR REPLACE DIRECTORY ERP_DIR

AS '/opt/oracle/inst/apps/PROD_proderp/logs/appl/conc/out';

GRANT EXECUTE, READ, WRITE ON DIRECTORY SYS.ERP_DIR TO APPS WITH GRANT OPTION;

-------------------------------------------------------------------

CREATE TABLE FPMS.HR_PAYSLIP_TEMP

(

EMPLOYEE_ID NUMBER,

PERIOD_ID NUMBER,

REQUEST_ID NUMBER,

EMAIL CHAR(1 BYTE)

)

-------------------------------------------------------------------

CREATE OR REPLACE PROCEDURE APPS.HR_SEND_MAIL(asqlcode in out nocopy number,

asqlerrm in out nocopy varchar2,

p_period_id number,

p_employee_id number

)

Created By : AFF

Description : This procedure will run the concurrent request of Salary Slip of employees.

CURSOR CUR_EMP

SELECT emp.employee_id, pt.request_id,

emp.first_name||' '||emp.middle_name||' '||emp.last_name employee_name,

emp.email_add temp_email --'affteam@masgroup.org' TEMP_EMAIL

FROM hr_payslip_temp pt, hr_employee emp

WHERE pt.employee_id = emp.employee_id

AND emp.email_add IS NOT NULL

AND emp.employee_id = NVL (p_employee_id, emp.employee_id)

AND NVL (pt.email, 'X') = 'N';

/*crlf VARCHAR2(2):= CHR(13) || CHR(10); ----char(2) := chr(10) || chr(13);

v_utl_filehandler UTL_FILE.FILE_TYPE;

v_rawfile RAW(32767);

v_size NUMBER;

v_block NUMBER;

v_boolean BOOLEAN;

v_file_dir varchar2(100);

v_file_name varchar2(100) ;

v_file_name varchar2(100)

v_att_filename varchar2(100);

lv_period varchar2(100);

crlf VARCHAR2(2):= CHR(13) || CHR(10); ----char(2) := chr(10) || chr(13);

REC_EMP CUR_EMP%ROWTYPE;

BEGIN

SELECT period_code

INTO lv_period

FROM hr_payroll_period hpp

WHERE hpp.period_id = p_period_id;

EXCEPTION

WHEN OTHERS

THEN

lv_period := ' ';

END;

OPEN CUR_EMP;

LOOP

FETCH CUR_EMP INTO REC_EMP;

EXIT WHEN CUR_EMP%NOTFOUND;

v_att_filename := 'Salary_Slip'||'.pdf' ;

v_file_dir := '/opt/oracle/inst/apps/PROD_proderp/logs/appl/conc/out'; --

v_utl_filehandler := UTL_FILE.FOPEN('ERP_DIR', 'o'||REC_EMP.REQUEST_ID||'.out', 'r');

UTL_FILE.fgetattr(v_file_dir, v_file_name, v_boolean, v_size, v_block);

dbms_output.PUT_LINE(v_file_name || '--****---' ||v_size ||'--****---'|| v_block);

UTL_FILE.get_raw(v_utl_filehandler, v_rawfile, v_size);

UTL_FILE.FCLOSE(v_utl_filehandler);

EXECUTE IMMEDIATE 'ALTER SESSION SET smtp_out_server = ''localhost''';

----EXECUTE IMMEDIATE 'ALTER SESSION SET smtp_out_server = ''192.168.0.6''';

utl_mail.send_attach_raw( sender => 'hrd@masgroup.org'

,recipients => REC_EMP.TEMP_EMAIL -----'affteam@masgroup.org'

---,cc => 'affteam@masgroup.org'

,subject => 'Salary Slip for the m/o '|| LV_PERIOD ||'.'

,message => '<html><body> '|| CRLF

|| ' Dear '||REC_EMP.EMPLOYEE_NAME ||', ' || CRLF

||' ' || CRLF

|| 'Please find salary slip for the month of '||LV_PERIOD||'. Kindly treat this information as highly Confidential '||'&'||' Private. For any further queries please contact Human Resource Department. ' || CRLF

|| ' Regards' || CRLF

|| ' HRD @ Mas Group' || CRLF || CRLF

|| ' '|| ' ' || CRLF --is newline

|| ' '|| ' ' || CRLF

|| ' '|| 'Note: Please immediately report to HR Personnel, in case the information contained in this document does not directly/indirectly relate to you. ' || CRLF

|| ' </body></html>'

, mime_type => 'text/html; charset=us-ascii'

, priority => 3

, attachment => v_rawfile

, att_inline => TRUE --FALSE

, att_mime_type => 'application/pdf'

, att_filename => v_att_filename

);

hr_send_mail2( 'hrd@masgroup.org',REC_EMP.TEMP_EMAIL,'Salary Slip for the m/o '|| LV_PERIOD ||'.' ,' '|| CRLF

|| 'Dear '||REC_EMP.EMPLOYEE_NAME ||', ' || CRLF

||' ' || CRLF

|| 'Regards' || CRLF||CRLF

|| 'HRD @ Mas Group' || CRLF || CRLF

|| ' '|| ' ' || CRLF --is newline

|| ' '|| ' ' || CRLF

|| 'Note: Please immediately report to HR Personnel, in case the information contained in this document does not directly/indirectly relate to you. ' || CRLF

|| ' ','mail send','','','o'||REC_EMP.REQUEST_ID||'.out',REC_EMP.EMPLOYEE_ID

);

UPDATE hr_payslip_temp pt

SET pt.email = 'Y'

WHERE pt.employee_id = rec_emp.employee_id;

COMMIT;

END LOOP;

CLOSE CUR_EMP;

END;

------------------------------------------------------------------------------------------

CREATE OR REPLACE PROCEDURE APPS.hr_send_mail2( from_name VARCHAR2,

to_names VARCHAR2,

subject VARCHAR2,

message VARCHAR2 DEFAULT NULL,

html_message VARCHAR2 DEFAULT NULL,

cc_names VARCHAR2 DEFAULT NULL,

bcc_names VARCHAR2 DEFAULT NULL,

outfile_request varchar2 default null, ---- to get out file from concurrent_request path

emp_no varchar2 default null ---- to get employee no

)

filename1 VARCHAR2(20) DEFAULT NULL;

filetype1 VARCHAR2(20) DEFAULT 'text/plain';

filename2 VARCHAR2(20) DEFAULT NULL;

filetype2 VARCHAR2(20) DEFAULT 'text/plain';

filename3 VARCHAR2(20) DEFAULT NULL;

filetype3 VARCHAR2(20) DEFAULT 'text/plain';

-- Change the SMTP host name and port number below to your own values,

-- if not localhost on port 25:

smtp_host VARCHAR2(256) := 'localhost'; ----'exchange.masgroup.local';

smtp_port NUMBER := 25;

-- Change the boundary string, if needed, which demarcates boundaries of

-- parts in a multi-part email, and should not appear inside the body of

-- any part of the e-mail:

boundary CONSTANT VARCHAR2(256) := 'CES.Boundary.DACA587499938898';

recipients VARCHAR2(32767);

directory_path VARCHAR2(256);

file_name VARCHAR2(256);

crlf VARCHAR2(2):= CHR(13) || CHR(10);

mesg VARCHAR2(32767);

conn UTL_SMTP.CONNECTION;

TYPE varchar2_table IS TABLE OF VARCHAR2(256) INDEX BY BINARY_INTEGER;

file_array varchar2_table;

type_array varchar2_table;

i BINARY_INTEGER;

file_handle utl_file.file_type;

line VARCHAR2(10000);

bfile_handle BFILE;

bfile_len NUMBER;

pos NUMBER;

read_bytes NUMBER;

data RAW(200);

-- Function to return the next email address in the list of email addresses,

-- separated by either a "," or a ";". From Oracle's demo_mail. The format

-- of mailbox may be in one of these:

-- someone@some-domain

-- "Someone at some domain" <someone@some-domain>

-- Someone at some domain <someone@some-domain>

FUNCTION get_address(addr_list IN OUT VARCHAR2) RETURN VARCHAR2 IS

addr VARCHAR2(256);

i PLS_INTEGER;

FUNCTION lookup_unquoted_char(str IN VARCHAR2,

chrs IN VARCHAR2) RETURN PLS_INTEGER IS

c VARCHAR2(5);

i PLS_INTEGER;

len PLS_INTEGER;

inside_quote BOOLEAN;

BEGIN

inside_quote := FALSE;

i := 1;

len := LENGTH(str);

WHILE (i <= len) LOOP

c := SUBSTR(str, i, 1);

IF (inside_quote) THEN

IF (c = '"') THEN

inside_quote := FALSE;

ELSIF (c = '\') THEN

i := i + 1; -- Skip the quote character

END IF;

GOTO next_char;

END IF;

IF (c = '"') THEN

inside_quote := TRUE;

GOTO next_char;

END IF;

IF (INSTR(chrs, c) >= 1) THEN

RETURN i;

END IF;

<<next_char>>

i := i + 1;

END LOOP;

RETURN 0;

END;

BEGIN

addr_list := LTRIM(addr_list);

i := lookup_unquoted_char(addr_list, ',;');

IF (i >= 1) THEN

addr := SUBSTR(addr_list, 1, i - 1);

addr_list := SUBSTR(addr_list, i + 1);

ELSE

addr := addr_list;

addr_list := '';

END IF;

i := lookup_unquoted_char(addr, '<');

IF (i >= 1) THEN

addr := SUBSTR(addr, i + 1);

i := INSTR(addr, '>');

IF (i >= 1) THEN

addr := SUBSTR(addr, 1, i - 1);

END IF;

RETURN addr;

END;

-- Procedure to split a file pathname into its directory path and file name

-- components.

PROCEDURE split_path_name(file_path IN VARCHAR2, directory_path OUT VARCHAR2,

file_name OUT VARCHAR2) IS

pos NUMBER;

BEGIN

-- Separate the filename from the directory name

pos := INSTR(file_path,'/',-1);

IF pos = 0 THEN

pos := INSTR(file_path,'\',-1);

END IF;

IF pos = 0 THEN

directory_path := NULL;

ELSE

directory_path := SUBSTR(file_path,1,pos - 1);

END IF;

file_name := SUBSTR(file_path,pos + 1);

END;

-- Procedure to append a file's contents to the e-mail

BEGIN

-- Load the three filenames and file (mime) types into an array for

-- easier handling later

---EXECUTE IMMEDIATE 'ALTER SESSION SET smtp_out_server = ''localhost''';

file_array(1) := filename1;

file_array(2) := filename2;

file_array(3) := filename3;

type_array(1) := filetype1;

type_array(2) := filetype2;

type_array(3) := filetype3;

-- Open the SMTP connection and set the From and To e-mail addresses

conn := utl_smtp.open_connection(smtp_host,smtp_port);

utl_smtp.helo(conn,smtp_host);

recipients := from_name;

utl_smtp.mail(conn,get_address(recipients));

recipients := to_names;

WHILE recipients IS NOT NULL LOOP

utl_smtp.rcpt(conn,get_address(recipients));

END LOOP;

recipients := cc_names;

WHILE recipients IS NOT NULL LOOP

utl_smtp.rcpt(conn,get_address(recipients));

END LOOP;

recipients := bcc_names;

WHILE recipients IS NOT NULL LOOP

utl_smtp.rcpt(conn,get_address(recipients));

END LOOP;

utl_smtp.open_data(conn);

-- Build the start of the mail message

mesg := 'Date: ' || TO_CHAR(SYSDATE,'dd Mon yy hh24:mi:ss') || crlf ||

'From: ' || from_name || crlf ||

'Subject: ' || subject || crlf ||

'To: ' || to_names || crlf;

IF cc_names IS NOT NULL THEN

mesg := mesg || 'Cc: ' || cc_names || crlf;

END IF;

IF bcc_names IS NOT NULL THEN

mesg := mesg || 'Bcc: ' || bcc_names || crlf;

END IF;

mesg := mesg || 'Mime-Version: 1.0' || crlf ||

'Content-Type: multipart/mixed; boundary="' || boundary || '"' ||

crlf || crlf ||

'This is a Mime message, which your current mail reader may not' || crlf ||

'understand. Parts of the message will appear as text. If the remainder' || crlf ||

'appears as random characters in the message body, instead of as' || crlf ||

'attachments, then you''ll have to extract these parts and decode them' || crlf ||

'manually.' || crlf || crlf;

utl_smtp.write_data(conn,mesg);

-- Write the text message or message file, if any

IF message IS NOT NULL THEN

mesg := '--' || boundary || crlf ||

'Content-Type: text/plain; name="message.txt"; charset=US-ASCII' ||

crlf ||

'Content-Disposition: inline; filename="message.txt"' || crlf ||

'Content-Transfer-Encoding: 7bit' || crlf || crlf;

utl_smtp.write_data(conn,mesg);

IF SUBSTR(message,1,1) = '/' THEN

split_path_name(message,directory_path,file_name);

utl_smtp.write_data(conn,crlf);

ELSE

utl_smtp.write_data(conn,message || crlf);

END IF;

-- Write the HTML message or message file, if any

-- Append the files

--------------------------------Changes Made On 03-MAY-2010 For the Attachment Of payslip in email-----------------------------------

mesg := crlf || '--' || boundary || crlf;

mesg := mesg || 'Content-Type: ' || 'application/pdf' ||'; name="' ||'Pay Slip Of: '||emp_no|| '"' || crlf || 'Content-Disposition: attachment; filename="' ||emp_no||'.pdf' || '"' || crlf || 'Content-Transfer-Encoding: base64' || crlf || crlf ;

--mesg := mesg || 'Content-Type: application/octet-stream; name="' || '123.pdf'|| '"' || crlf || 'Content-Disposition: attachment; filename="' || '123.pdf' || '"' || crlf || 'Content-Transfer-Encoding: 7bit' || crlf || crlf ;

utl_smtp.write_data(conn,mesg);

BEGIN

--file_handle := utl_file.fopen('TESTDIR','o7079497.txt','r');

bfile_handle := BFILENAME('ERP_DIR',outfile_request); ----TESTPAY IS THE NAME OF DIRECTORY CREATED ON PROD

bfile_len := dbms_lob.getlength(bfile_handle);

pos := 1;

dbms_lob.OPEN(bfile_handle,dbms_lob.lob_readonly);

-- Append the file contents to the end of the message

LOOP

-- If it is a binary file, process it 57 bytes at a time,

-- reading them in with a LOB read, encoding them in BASE64,

-- and writing out the encoded binary string as raw data

IF pos + 57 - 1 > bfile_len THEN

read_bytes := bfile_len - pos + 1;

ELSE

read_bytes := 57;

END IF;

dbms_lob.READ(bfile_handle,read_bytes,pos,data);

utl_smtp.write_raw_data(conn,utl_encode.base64_encode(data));

pos := pos + 57;

IF pos > bfile_len THEN

EXIT;

END IF;

end loop;

dbms_lob.CLOSE(bfile_handle);

----------------------------------------Changes Done til here --------------------------------------------------------------

EXCEPTION

WHEN NO_DATA_FOUND THEN

NULL;

WHEN OTHERS THEN

FND_FILE.PUT_LINE(FND_FILE.OUTPUT, SQLCODE ||' : '|| SQLERRM);

END;

utl_smtp.write_data(conn,crlf);

-- Append the final boundary line

mesg := crlf || '--' || boundary || '--' || crlf;

utl_smtp.write_data(conn,mesg);

-- Close the SMTP connection

utl_smtp.close_data(conn);

utl_smtp.quit(conn);

END;

-------------------------------------------------------------------------------

CREATE OR REPLACE PROCEDURE APPS.HR_SUBMIT_PAYSLIP ( asqlcode in out nocopy number,

asqlerrm in out nocopy varchar2,

p_period_id NUMBER,

p_office NUMBER,

p_employee NUMBER,

p_user_id NUMBER

)

Created By : Asif Javed

Description : This procedure will run the concurrent request of Salary Slip of employees and email to given email id.

--cursor to get specifiic attributes of the employees in specified payroll area

CURSOR CUR_EMP

SELECT emp.employee_id, emp.employee_code

FROM hr_employee emp,

hr_position_type pt,

hr_office ofc,

hr_department dpt,

hr_dept_office dof

WHERE emp.position_type_id = pt.position_type_id

AND emp.office_dept_id = dof.office_dept_id

AND ofc.office_id = dof.office_id

AND emp.email_add IS NOT NULL

AND dpt.department_id = dof.department_id

AND ofc.office_id = NVL (p_office, ofc.office_id)

AND emp.employee_id = NVL (p_employee, emp.employee_id)

AND emp.employee_id IN ( SELECT DISTINCT per.employee_id

FROM hr_payroll_earning_register per

WHERE per.employee_id = emp.employee_id

AND per.period_id = p_period_id);

REC_EMP CUR_EMP%ROWTYPE;

ln_user_id number;

ln_responsibility_id number;

ln_application_id number;

ln_request_id number;

BEGIN

/********************/

DELETE FROM HR_PAYSLIP_TEMP;

COMMIT;

/********************/

--- Get the initialization parameters

BEGIN

SELECT user_id, responsibility_id, responsibility_application_id

INTO ln_user_id, ln_responsibility_id, ln_application_id

FROM fnd_user_resp_groups

WHERE responsibility_id = 50826

AND user_id = p_user_id;

Fnd_Global.apps_initialize(LN_USER_ID,LN_RESPONSIBILITY_ID,LN_APPLICATION_ID);

EXCEPTION

WHEN OTHERS THEN

NULL;

END;

--- Get the initialization parameters

Fnd_Global.apps_initialize(LN_USER_ID,LN_RESPONSIBILITY_ID,LN_APPLICATION_ID);

OPEN CUR_EMP;

--loop for employees

LOOP

FETCH CUR_EMP INTO REC_EMP;

EXIT WHEN CUR_EMP%NOTFOUND;

/**************************************************************************/

-- Run Concurrent Request for the Salary Slip of each employee

/**************************************************************************/

LN_REQUEST_ID:=fnd_request.submit_request('FPMS','HTL_PAY_SLIP','',

'',NULL,

P_PERIOD_ID,'',REC_EMP.EMPLOYEE_ID,'','','','','','','',

'','','','','','','','','','',

'','','','','','','','','',''

);

/**************************************************************************/

-- Run Concurrent Request for the Salary Slip of each employee

/**************************************************************************/

INSERT INTO hr_payslip_temp

(employee_id, period_id, request_id, email)

VALUES (rec_emp.employee_id, p_period_id, ln_request_id, 'N');

COMMIT;

END LOOP;

CLOSE CUR_EMP;

EXCEPTION

WHEN OTHERS THEN

NULL;

END;

Oracle Technical Consultant Information

Thursday 13 October 2016

Concurrent Program Send To Email

1 comment:

Blog Archive