create or replace PROCEDURE  "PROCSEND_BATCH_EMAIL"

(Header in varchar2, fromDate in varchar2, toDate in varchar2, FromSender in varchar2,

 ContentTxt in varchar2)  

as

  

   strSubject varchar2(200) := 'Grace Korean Church Member Information';

 --  strSubject varchar2(200) := Header;

 

   strPtext varchar2(100) := ' ';

   strEmail  varchar2(100);

   strSTITLE  varchar2(40);

 --  strFromEmail varchar2(100) := 'gracechurch@grace.gov';  --'' ;  --

   strFromEmail varchar2(100) := 'Grace Ministies International <gracechurch@grace.gov>';  --'' ;  --

 

    strBody   varchar2(4000);

   emailSentFlag varchar2(3) := 'NO';

   strDistrict varchar2(240);

   strMemberName varchar(30);

--- strChurchSenior varchar2(60) :='Pastor Han, Gi Hong';

 --- appLogon    varchar2(100) :='http://localhost:8081/apex/f?p=130:2:2679667653831772::NO:::;

   Test_count   number := 1;

   Test_Add   number := 0;

   EMAIL_CONTENT_T_SW  number := 0;

   START_EMAIL_OUT_ID  number := 0;

   lno  varchar2(50);

   MemberName  varchar2(200);   

   dirName varchar(100);

begin

     --DBMS_OUTPUT.PUT_LINE('start');

 

  -- IF upper(P_IMEMBER_SK) = 'ALL' then

  --    :P_IMEMBER_SK := '';

--   END IF;

 

      

 for C1 in ( select IMEMBER_SK, STITLE_MAIL, SNATIVE_NM,  SEMAILADDRESS  from  EMAIL_IN_T e, TITLE_T t

where e.STITLE = t.STITLE )

 

  loop

        

    strMemberName := c1.SNATIVE_NM;

    strSTITLE   := c1.STITLE_MAIL;

--     DBMS_OUTPUT.PUT_LINE(c1.SNATIVE_NM  || ' PProcess' );

/* create html body */

                  strBody := '<font face=Arial><table border=0 width=90%><tr><td width=100%

                  colspan=2 height=19>

                  <p align=center><b><font face=Arial>'|| Header ||'

                 </font></b></P></td>

                  </tr>

                  

                  <tr><td width=100% colspan=2 height=10>Date: ' ||

                  to_char(sysdate,'MM/DD/YYYY') || '

                  </td></tr>

                  <tr><td width=100% colspan=2 height=10> </td>

                  </tr>

                  <tr> <td>  

                  '   || 'From: ' || FromSender || ' </td> </tr>              

                 

                  <tr><td width=50% height=43><font face=Arial>

    ' || '¼£·Ò ! '||'  '||  strMemberName || '   ' || strSTITLE|| '´Ô;'||'  <br> '|| '</td>  </tr>   

                                                    

                  <tr><td width=100% height=100 colspan=2> '|| ContentTxt || ' </span></span></td></tr></table></font> ';

/* get email addresses */            

                  strEmail := c1.SEMAILADDRESS;

               if strEmail is null then

                       null;

               else

                    Test_Add  := Test_Add + 1;     --test

                    If Test_count  =  Test_Add then  --test

                   --- GRACE_KOEMAIL (strEmail,strFromEmail,strSubject,' ',strBody,'relay.jangosmtp.net',2525);

                   --- GRACE_KOEMAIL ('pastorpghan@yahoo.com',strFromEmail,strSubject,' ',strBody,'relay.jangosmtp.net',2525);

                   --- GRACE_KOEMAIL ('luvluv916@gmail.com',strFromEmail,strSubject,' ',strBody,'relay.jangosmtp.net',2525);

                   --- GRACE_KOEMAIL ('haikoh4002@yahoo.com',strFromEmail,strSubject,' ',strBody,'relay.jangosmtp.net',2525);

                       emailSentFlag := 'YES';

                    end if;  --test

                 

                  INSERT INTO EMAIL_OUT_T (IMEMBER_SK, STITLE, SNATIVE_NM, SEMAILADDRESS,  HEADER)

                              VALUES (c1.IMEMBER_SK, strSTITLE, strMemberName, strEmail, Header);

 

                  ----Create content table only once-----     

          

                    if EMAIL_CONTENT_T_SW  = 0 then

                        SELECT EMAIL_OUT_SEQ.currval  INTO START_EMAIL_OUT_ID FROM dual;

                        INSERT INTO EMAIL_CONTENT_T (EMAIL_CONTENT_ID, EMAIL_SENDER, HEADER, EMAIL_CONTENT)

                                            VALUES  (START_EMAIL_OUT_ID, FromSender, Header, ContentTxt);

                       EMAIL_CONTENT_T_SW := 1;

                    end if;

                  commit; 

              end if;

          

                   

 --  DBMS_OUTPUT.PUT_LINE('finished');

         end loop;

End PROCSEND_BATCH_EMAIL;