delimiter //

CREATE PROCEDURE EMAIL_CONTENT_MASTER_PROC
( IN P_EMAIL_CONTENT_MASTER_ID INTEGER(10),
 IN P_EMAIL_TYPE VARCHAR(100),
 IN P_EMAIL_SUBJECT VARCHAR(1000),
 IN P_EMAIL_CONTENT VARCHAR(1000),
 IN P_ATTACHMENT_REQ VARCHAR(1),
 IN P_FREQUENCY INTEGER(10),
 IN P_SEND_TYPE VARCHAR(10),
 IN P_SEND_DAYS  INTEGER(10),
 IN p_CREATED_BY INTEGER(10),
 IN p_MODIFIED_BY INTEGER(10)
)
BEGIN

UPDATE EMAIL_CONTENT_MASTER
SET EMAIL_TYPE = P_EMAIL_TYPE,
EMAIL_SUBJECT= P_EMAIL_SUBJECT,
EMAIL_CONTENT= P_EMAIL_CONTENT,
ATTACHMENT_REQ= P_ATTACHMENT_REQ,
FREQUENCY= P_FREQUENCY,
SEND_TYPE= P_SEND_TYPE,
SEND_DAYS= P_SEND_DAYS,
MODIFIED_ON = now(),
MODIFIED_BY= p_MODIFIED_BY
where EMAIL_CONTENT_MASTER_ID = P_EMAIL_CONTENT_MASTER_ID;

INSERT INTO EMAIL_CONTENT_MASTER
(EMAIL_TYPE,
EMAIL_SUBJECT,
EMAIL_CONTENT,
ATTACHMENT_REQ,
FREQUENCY,
SEND_TYPE,
SEND_DAYS,
CREATED_ON,
MODIFIED_ON,
CREATED_BY,
MODIFIED_BY
)
VALUES
(
P_EMAIL_TYPE,
P_EMAIL_SUBJECT,
P_EMAIL_CONTENT,
P_ATTACHMENT_REQ,
P_FREQUENCY,
P_SEND_TYPE,
P_SEND_DAYS,
NOW(),
NOW(),
p_CREATED_BY,
p_MODIFIED_BY
);

COMMIT;

END//
delimiter;