14 Replies Latest reply on Jun 19, 2014 4:32 AM by Sruthi Tamiri

    Email address in apex through front end apex

    Sruthi Tamiri

      Hi Team,

       

      In our existing application there is LOV list with name "Emp name", we are fetching the list from particular table to show same in drop down list.

      For example the Emp names contains list as

       

      "Giri"

      "Swathi"

      "Prakash"

      "Senthil"

       

      when ever we select particular associate then i need to have redirect to that particular Distribution list"

      i.,e from above mention list if i had selected that particulat assocaite as "Prakash" then i need to send to "DL" list.

      How we can work on this request.

       

      Regard,

      Sruthitamiri!!


        • 1. Re: Email address in apex through front end apex
          jwellsnh

          HI Sruthitamiri,

           

          Sruthitamiri said:

          when ever we select particular associate then i need to have redirect to that particular Distribution list"

          i.,e from above mention list if i had selected that particulat assocaite as "Prakash" then i need to send to "DL" list.

          How we can work on this request.

          You will need a page process to send out an email to the distribution list assuming that you have a table that defines distribution lists by associate.

           

          I had a similar need and used this code which executed via Submit.  You will need to substitute with your tables but the general design should help you out.

           

          DECLARE
             v_to                VARCHAR2 (255);
             v_from              VARCHAR2 (100);
             v_subject           VARCHAR2 (255);
             v_harvest_request   NUMBER;
             v_url               VARCHAR2 (100);
             v_user              VARCHAR2 (50);
             l_body              CLOB;
             l_body_html         CLOB;
          BEGIN
             -- Send email to Harvest Item Admins copy logged in User
          
             -- Capture Harvest Item Request number
             SELECT harvest_item_tbl.harvest_item_seq
               INTO v_harvest_request
               FROM harvest_item_tbl
              WHERE harvest_item_tbl.harvest_item_seq = :p481_harvest_item_seq;
          
             -- Set CurrentUser
             SELECT NVL (v ('app_user'), USER) INTO v_user FROM DUAL;
          
             -- Set Email Subject
             SELECT    'Harvest Service Request for '
                    || harvest_project_tbl.project_nme
                    || '---'
                    || harvest_broker_tbl.broker_nme
               INTO v_subject
               FROM harvest_item_tbl, harvest_project_tbl, harvest_broker_tbl
              WHERE     harvest_item_tbl.harvest_item_seq = :p481_harvest_item_seq
                    AND harvest_item_tbl.harvest_project_seq =
                           harvest_project_tbl.harvest_project_seq
                    AND harvest_item_tbl.harvest_broker_seq =
                           harvest_broker_tbl.harvest_broker_seq;
          
             -- Capture email address of sender
             SELECT LOWER (person_tbl.email_address_txt)
               INTO v_from
               FROM person_tbl
              WHERE person_tbl.sam_account_nme = v_user;
          
             -- Get email addresses of Harvest Item Administrators
             WITH admins
                  AS (SELECT person_tbl.email_address_txt,
                             ROW_NUMBER () OVER (ORDER BY email_address_txt) rn,
                             COUNT (*) OVER () cnt
                        FROM harvest_admin_tbl, person_tbl
                       WHERE     harvest_admin_tbl.active_ind = 'Y' -- Use active Admins only
                             AND harvest_admin_tbl.internal_employee_id =
                                    person_tbl.employee_ghrs_id)
                 -- Concatenate email addresses into one string
                 SELECT LTRIM (SYS_CONNECT_BY_PATH (email_address_txt, ','), ',') catvalues
                   INTO v_to
                   FROM admins
                  WHERE rn = cnt
             START WITH rn = 1
             CONNECT BY PRIOR rn = rn - 1;
          
          
             l_body_html := '.....Build your email body from here forward';
          END;
          

           

          Jeff

          • 2. Re: Email address in apex through front end apex
            Sruthi Tamiri

            Hi Jeff,

             

              Thanks a lot for your reponse, i am trying to understand and preparing the same for mine. Could we request you to provide us table staructure also which helps to me create from my end.

             

            Regards,

            Sruthitamiri!!

            • 3. Re: Email address in apex through front end apex
              jwellsnh

              Hi Sruthitamiri,

               

              I am not at work right now but will be later on.  Here is the table structure for my PERSON_TBL;

              CREATE TABLE "PERSON_TBL" 
                ( "PERSON_SEQ" NUMBER(5,0) NOT NULL ENABLE, 
              "EMPLOYEE_GHRS_ID" NUMBER(10,0), 
              "LAST_NME" VARCHAR2(25) NOT NULL ENABLE, 
              "FIRST_NME" VARCHAR2(20) NOT NULL ENABLE, 
              "NOTES_COMMON_NME" VARCHAR2(255), 
              "EMAIL_ADDRESS_TXT" VARCHAR2(255), 
              "LST_UPDT_DT" DATE NOT NULL ENABLE, 
              "LST_UPDT_ID" VARCHAR2(30) NOT NULL ENABLE, 
              "CREATION_DT" DATE NOT NULL ENABLE, 
              "CREATION_ID" VARCHAR2(30) NOT NULL ENABLE, 
              "SAM_ACCOUNT_NME" VARCHAR2(255), 
              "APPL_ACCESS_BLOCKED_IND" VARCHAR2(1), 
              "AUTHENT_METHOD_CDE" VARCHAR2(6), 
              "MIDDLE_NME" VARCHAR2(20), 
              PRIMARY KEY ("PERSON_SEQ") ENABLE
                )
              /
              

              The HARVET_ADMIN_TBL I believe is very simplistic and contains pretty much an ACTIVE_IND set to 'Y' for active administrators, our employee id, EMPLOYEE_GHRS_ID and most likely First and Last name.  As seen above, our PERSON_TBL is the source of the email address.

               

              Later on I will include the structure for the HARVEST_ADMIN_TBL and others if needed.

               

              Jeff

              • 4. Re: Email address in apex through front end apex
                Sruthi Tamiri

                Hi Jeff,

                 

                  Thanks a lot for your table structure, here the things have done from our end. In my form there is complex part exists which i am not able to get clear idea how to handle the scenrio, if you can help me on this it would be great.

                 

                1. As said earlier i have list item as "Sector" and with button as "Add Another"

                2. When ever user click on Add another a new row will be created with sector2.

                3. We are generating upto seven sectors when click on "Add Another" and items were created as "P1_ENG_SCT to P1_ENG_SCT7.

                4. When user selects sector list we have sub groups as "Oil" , "Petrol" & "gas".

                5. Now when user selcts "Oil" then it goes to particular Oil Distribution list"

                6. User can select all seven sectors as "Oil" in this case it goes to one Oil DL list.

                7.User can select two combination as first as "Oil"  [P1_ENG_SCT] & second one [P1_ENG_SCT1] is "Petrol" then it should goes to two different DL list.

                8. As per existsing logic team had hard coded in our application list this

                 

                if (

                    upper('Oil')=upper(:P1_ENG_SCT)  or

                    upper('Oil')=upper(:P1_ENG_SCT2) or

                    upper('Oil')=upper(:P1_ENG_SCT3) or

                    upper('Oil')=upper(:P1_ENG_SCT4) or

                    upper('Oil')=upper(:P1_ENG_SCT5) or

                    upper('Oil')=upper(:P1_ENG_SCT6) or

                    upper('Oil')=upper(:P1_ENG_SCT7)

                   

                ) then

                if  lv_to_email_id is not null then

                   

                       lv_to_email_id :=lv_to_email_id ||','|| 'Oil@xyz.com';          

                   else

                       lv_to_email_id :='Oil@xyz.com';


                if (

                    upper('Petrol')=upper(:P1_ENG_SCT)  or

                    upper('Petrol')=upper(:P1_ENG_SCT2) or

                    upper('Petrol')=upper(:P1_ENG_SCT3) or

                    upper('Petrol')=upper(:P1_ENG_SCT4) or

                    upper('Petrol')=upper(:P1_ENG_SCT5) or

                    upper('Petrol')=upper(:P1_ENG_SCT6) or

                    upper('Petrol')=upper(:P1_ENG_SCT7)

                   

                ) then

                if  lv_to_email_id is not null then

                   

                       lv_to_email_id :=lv_to_email_id ||','|| 'Petrol@xyz.com';          

                   else

                       lv_to_email_id :='Petrol@xyz.com';

                 

                Now what i have done is i had created table like this


                CREATE TABLE ME_MAIN_EMAIL_DL

                (

                  MAIL_SEQ_ID      NUMBER, -> Here i am storing numberic values as "1" for all this.

                  EMAIL_GROUP      VARCHAR2(1000 BYTE), ->here i am storing "Sector" for all this.

                  EMAIL_SUB_GROUP  VARCHAR2(1000 BYTE), ->Here i am storing "Oil", "petrol", "power" and so on if any exists

                  ITEMS            VARCHAR2(1000 BYTE), ->here i am storing items names as ":P1_ENG_SCT to ":P1_ENG_SCT7"

                  EMAIL_ADDRESS    VARCHAR2(1000 BYTE), ->here i am capturing as " email address i.,e "Oil@xyz.com for oil sub group and "petrol@xyz.com for petrol sub group & soon

                  ACTIVE_FLAG      VARCHAR2(1 BYTE)             DEFAULT 'Y',

                  TIME_STAMP       TIMESTAMP(6)                 DEFAULT SYSDATE               NOT NULL,

                  MODIFIED_BY      VARCHAR2(30 BYTE)            DEFAULT substr(USER,1,30)     NOT NULL,

                  MODIFIED_DATE    DATE                         DEFAULT SYSDATE               NOT NULL,

                  INACTIVE_DATE    DATE,

                  CREATED_BY       VARCHAR2(30 BYTE)

                )

                 

                My process contains as shown below after i had modified part of code is, [in this case i had selcted only one sector i.,e Oil, so it should genearte only one email id, but it got genearted seven times since data exists in table. Where there is hard coded i had used tables structure now.

                 

                 

                FOR rec IN ( select EMAIL_ADDRESS

                            from ME_Main_Email_DL

                              where active_flag='Y'

                            and items is not null )

                 

                 

                and here is full code

                 

                declare
                 
                  lv_to_email_id varchar2(4000);
                  lv_from_email_id varchar(1000):=lower(replace((:APP_USER),'_','.'))||'@gmail.com';
                  l_body_html clob;
                 
                  lv_eng_type varchar2(4000):='';
                  lv_ind_part VARCHAR2(32767) := NULL;
                  lv_ind_part_num number(1):=1;
                  lv_platts_lead_num number(1):=1;
                  lv_platts_lead VARCHAR2(32767) := NULL;
                  lv_Act_Item_Tot_Cnt number(1):=1;
                  lv_Act_Item VARCHAR2(32767) := NULL; 
                 
                  lv_Engagement varchar2(32767);
                  lv_Methodology varchar2(32767);
                  lv_Regulatory varchar2(32767);
                  lv_Notes_Commercail varchar2(32767);

                begin
                lv_eng_type:=:P1_ENG_ENT;
                     if(ltrim(rtrim(lv_eng_type)) is not null) then
                        lv_eng_type:= '/('||:P1_ENG_ENT ||')';
                     end if;  
                --New POC On Email address dynamic --

                   FOR rec IN (
                            select EMAIL_ADDRESS
                            from ME_Main_Email_DL
                            where active_flag='Y'
                            and items is not null )
                Loop

                begin
                     FOR cur_rec IN (
                select PARTICIPANT
                       from me_main_part
                       where PARTICIPANT_TYPE in('OTHER PARTICIPANT')
                       and ENG_ID=:P1_DISPLAY_EID)
                Loop

                if lv_ind_part_num =1 then
                      lv_ind_part := cur_rec.PARTICIPANT;
                      lv_ind_part_num :=2;
                else
                      lv_ind_part := lv_ind_part || ';'|| cur_rec.PARTICIPANT;
                end if;

                end loop;
                end;


                begin
                     FOR cur_rec_lead IN (
                select PARTICIPANT
                       from me_main_part
                       where PARTICIPANT_TYPE in('PLATTS PARTICIPANT')
                       and ENG_ID=:P1_DISPLAY_EID)
                Loop

                if  lv_platts_lead_num =1 then
                    lv_platts_lead := cur_rec_lead.PARTICIPANT;
                    lv_platts_lead_num :=2;
                else
                    lv_platts_lead := lv_platts_lead || ';'|| cur_rec_lead.PARTICIPANT;
                end if;

                end loop;
                end;

                begin
                     FOR cur_rec_item IN (
                select action_item
                       from me_main_act_item
                        where ENG_ID=:P1_DISPLAY_EID)
                Loop

                if lv_Act_Item_Tot_Cnt=1 then
                   lv_Act_Item := '<b> Action Item 1:</b>'||'   '||cur_rec_item.action_item;
                else
                    lv_Act_Item := lv_Act_Item||chr(13)||'<b>Action Item</b>'||' '||'<b>'||lv_Act_Item_Tot_Cnt||':'||'</b>'||'   '||cur_rec_item.action_item;
                end if;

                lv_Act_Item_Tot_Cnt := lv_Act_Item_Tot_Cnt+1;
                end loop;
                end;


                IF :P1_ENG_NOTES IS NOT NULL THEN
                    lv_Engagement := '<p><b>Eng Notes:</b> '||replace(:P1_ENG_NOTES, CHR(13), '<BR>')||'</p> ';
                END IF;

                if :P1_ENG_MTH_FEED is not null then
                   lv_Methodology := '<p><b>Methodology related Feedback:</b> '||replace(:P1_ENG_MTH_FEED, CHR(13), '<BR>')||'</p> ';
                end if;

                if :P1_MKT_REG_FEED is not null then
                    lv_Regulatory := '<p><b>Regulatory related Feedback:</b>  '||replace(:P1_MKT_REG_FEED, CHR(13), '<BR>')||'</p> ';
                end if;

                if :P1_ENG_COMM_NOTES is not null then
                    lv_Notes_Commercail := '<p><b>Notes for Commercial:</b>  '||replace(:P1_ENG_COMM_NOTES, CHR(13), '<BR>')||'</p> ';
                end if;


                    if rec.EMAIL_ADDRESS is not null then
                       l_body_html := '<p><b>Who:</b>  <b>Industry Participant:</b>'||:P1_ENG_OTHER||', <b>Company Name : </b>'||:P1_ENG_SUBARY||', <b>Other Participant:</b>'||lv_ind_part||'</p> '||
                                 '<p><b>Where:</b> <b>Country:</b> '||:P1_ENG_CTRY ||',<b>City : </b>'||:P1_ENG_CITY ||'</p> '||                
                                 '<p><b>When:</b> <b>Engagement Date:</b> ' ||:P1_ENG_DATE  ||'</p> '||
                                 '<p><b>Platts:</b> <b>Platts Lead:</b>'||:P1_ENG_LEAD1||', <b>Platts Participant:</b>'||lv_platts_lead||'</p> '||
                                  lv_Engagement||
                                  lv_Methodology||
                                  lv_Regulatory||
                                  lv_Notes_Commercail||
                                 '<p>'||replace(lv_Act_Item, CHR(13), '<BR>')||'</p>'||               
                                 '<a href="http://'||OWA_UTIL.GET_CGI_ENV('HTTP_HOST')||                 
                                  OWA_UTIL.GET_CGI_ENV('SCRIPT_NAME')||'/f?p='||:APP_ID||':1:::::P1_CHECK,P1_BUTTONHIDD,P1_DISPLAY_EID:N,UpdateMode,&P1_DISPLAY_EID.'||                               
                                  '">Click here to view '||
                                  '</a></p>';   
                    HTMLDB_MAIL.SEND(     
                    P_TO        => rec.EMAIL_ADDRESS,
                    P_FROM      => lv_from_email_id,   
                    P_BODY      => l_body_html,
                    P_BODY_HTML => l_body_html,
                    P_SUBJ        => 'Meeting   ('||:P1_DISPLAY_EID||'):('||:P1_ENG_SUBARY||')'|| lv_eng_type);     
                  end if;
                  end loop;
                end;

                 

                 

                Regards,

                Sruthitamiri!!

                 

                 


                 

                 

                 

                 


                • 5. Re: Email address in apex through front end apex
                  jwellsnh

                  HI Sruthitamiri,

                   

                  Thanks for all of the information which may have been Too Much Information (TMI).  What I cannot determine in the code you provided is where or what an associate is and how it is linked to your ME_MAIN_EMAIL_DL table.

                   

                  1. Please comment your code since I am not familiar with your subject area or your database.  Also please format your by using Advanced Editing and insert it as SQL.
                  2. Also explain how an associate is brought into the picture and include your associate/person table structure.
                    • Possibly is you data structured so there is not what I assumed, that an associate belongs to a distribution list?

                   

                  Here is my HARVEST_ADMIN_TBL structure:

                  CREATE TABLE LISDEV.HARVEST_ADMIN_TBL
                  (
                    HARVEST_ADMIN_SEQ     NUMBER(3)               NOT NULL,
                    ACTIVE_IND            VARCHAR2(1 BYTE),
                    CREATION_ID           VARCHAR2(50 BYTE),
                    CREATION_DT           DATE,
                    LST_UPDT_ID           VARCHAR2(50 BYTE),
                    LST_UPDT_DT           DATE,
                    INTERNAL_EMPLOYEE_ID  NUMBER
                  )
                  

                   

                  Jeff

                  • 6. Re: Email address in apex through front end apex
                    Sruthi Tamiri

                    Hi Jeff,

                     

                      Initally i had tried to commit the code in your format, but i am not able to find option, could you plese let me know how i can post like you, so that i will try the same.

                     

                    Regards,

                    Sruthitamiri

                    • 7. Re: Email address in apex through front end apex
                      jwellsnh

                      Sruthitamiri,

                       

                      When replying, in the upper right hand corner there is a toggle labeled, Use advanced editor.  Clicking on that will alter the tool bar for the post and provide additional options for formatting.  Note the >> next to the Emoticons control.  This is an Insert control, click on that and then select SQL (or whatever language you are pasting in) under Syntax Highlighting.

                       

                      Jeff

                      • 8. Re: Email address in apex through front end apex
                        Sruthi Tamiri

                        Thanks Jeff for this!!

                         

                        1. The piece of code we had modified is that

                         

                        FOR rec IN (
                                    select EMAIL_ADDRESS 
                                    from ME_Main_Email_DL
                                    where active_flag='Y'
                                    and items is not null )
                        Loop
                        

                         

                        2. Initally it was hard coded like this

                         

                        if (
                            upper('Oil')=upper(:P1_ENG_SCT)  or
                            upper('Oil')=upper(:P1_ENG_SCT2) or
                            upper('Oil')=upper(:P1_ENG_SCT3) or
                            upper('Oil')=upper(:P1_ENG_SCT4) or
                            upper('Oil')=upper(:P1_ENG_SCT5) or
                            upper('Oil')=upper(:P1_ENG_SCT6) or
                            upper('Oil')=upper(:P1_ENG_SCT7) 
                            
                         ) then 
                        if  lv_to_email_id is not null then 
                            
                               lv_to_email_id :=lv_to_email_id ||','|| 'Oil@xyz.com';           
                           else
                               lv_to_email_id :='Oil@xyz.com'; 
                               
                           
                        end if;  
                        end if;
                        if (
                            upper('Petrol')=upper(:P1_ENG_SCT)  or
                            upper('Petrol')=upper(:P1_ENG_SCT2) or
                            upper('Petrol')=upper(:P1_ENG_SCT3) or
                            upper('Petrol')=upper(:P1_ENG_SCT4) or
                            upper('Petrol')=upper(:P1_ENG_SCT5) or
                            upper('Petrol')=upper(:P1_ENG_SCT6) or
                            upper('Petrol')=upper(:P1_ENG_SCT7) 
                            
                         ) then 
                        if  lv_to_email_id is not null then 
                            
                               lv_to_email_id :=lv_to_email_id ||','|| 'Petrol@xyz.ocm';   
                           else
                               lv_to_email_id :='Petrol@xyz.com';  
                               
                           
                        end if;  
                        end if;
                        

                         

                        3. So the overall process is like this after i made changes to dynamically

                        declare
                          
                          lv_to_email_id varchar2(4000);
                          lv_from_email_id varchar(1000):=lower(replace((:APP_USER),'_','.'))||'@gmail.com';
                          l_body_html clob;
                          
                          lv_eng_type varchar2(4000):='';
                          lv_ind_part VARCHAR2(32767) := NULL;
                          lv_ind_part_num number(1):=1;
                          lv_platts_lead_num number(1):=1;
                          lv_platts_lead VARCHAR2(32767) := NULL;
                          lv_Act_Item_Tot_Cnt number(1):=1;
                          lv_Act_Item VARCHAR2(32767) := NULL;  
                          
                          lv_Engagement varchar2(32767);
                          lv_Methodology varchar2(32767);
                          lv_Regulatory varchar2(32767); 
                          lv_Notes_Commercail varchar2(32767);
                         
                        begin
                        lv_eng_type:=:P1_ENG_ENT; 
                             if(ltrim(rtrim(lv_eng_type)) is not null) then
                                lv_eng_type:= '/('||:P1_ENG_ENT ||')';
                             end if;   
                        --New POC On Email address dynamic --
                           FOR rec IN (
                                    select EMAIL_ADDRESS 
                                    from ME_Main_Email_DL
                                    where active_flag='Y'
                                    and items is not null )
                        Loop 
                        begin
                             FOR cur_rec IN (
                        select PARTICIPANT 
                               from me_main_part
                               where PARTICIPANT_TYPE in('OTHER PARTICIPANT')
                               and ENG_ID=:P1_DISPLAY_EID)
                        Loop
                        if lv_ind_part_num =1 then 
                              lv_ind_part := cur_rec.PARTICIPANT;
                              lv_ind_part_num :=2;
                        else
                              lv_ind_part := lv_ind_part || ';'|| cur_rec.PARTICIPANT;
                        end if;
                        end loop;
                        end;
                        
                        begin
                             FOR cur_rec_lead IN (
                        select PARTICIPANT 
                               from me_main_part
                               where PARTICIPANT_TYPE in('PLATTS PARTICIPANT')
                               and ENG_ID=:P1_DISPLAY_EID)
                        Loop
                        if  lv_platts_lead_num =1 then 
                            lv_platts_lead := cur_rec_lead.PARTICIPANT;
                            lv_platts_lead_num :=2;
                        else
                            lv_platts_lead := lv_platts_lead || ';'|| cur_rec_lead.PARTICIPANT;
                        end if;
                        end loop;
                        end;
                        begin
                             FOR cur_rec_item IN (
                        select action_item 
                               from me_main_act_item
                                where ENG_ID=:P1_DISPLAY_EID)
                        Loop
                        if lv_Act_Item_Tot_Cnt=1 then
                           lv_Act_Item := '<b> Action Item 1:</b>'||'   '||cur_rec_item.action_item;
                        else
                            lv_Act_Item := lv_Act_Item||chr(13)||'<b>Action Item</b>'||' '||'<b>'||lv_Act_Item_Tot_Cnt||':'||'</b>'||'   '||cur_rec_item.action_item;
                        end if;
                        lv_Act_Item_Tot_Cnt := lv_Act_Item_Tot_Cnt+1;
                        end loop;
                        end;
                        
                        IF :P1_ENG_NOTES IS NOT NULL THEN
                            lv_Engagement := '<p><b>Eng Notes:</b> '||replace(:P1_ENG_NOTES, CHR(13), '<BR>')||'</p> ';
                        END IF;
                        if :P1_ENG_MTH_FEED is not null then
                           lv_Methodology := '<p><b>Methodology related Feedback:</b> '||replace(:P1_ENG_MTH_FEED, CHR(13), '<BR>')||'</p> ';
                        end if;
                        if :P1_MKT_REG_FEED is not null then
                            lv_Regulatory := '<p><b>Regulatory related Feedback:</b>  '||replace(:P1_MKT_REG_FEED, CHR(13), '<BR>')||'</p> ';
                        end if;
                        if :P1_ENG_COMM_NOTES is not null then
                            lv_Notes_Commercail := '<p><b>Notes for Commercial:</b>  '||replace(:P1_ENG_COMM_NOTES, CHR(13), '<BR>')||'</p> ';
                        end if;
                        
                            if rec.EMAIL_ADDRESS is not null then
                               l_body_html := '<p><b>Who:</b>  <b>Industry Participant:</b>'||:P1_ENG_OTHER||', <b>Company Name : </b>'||:P1_ENG_SUBARY||', <b>Other Participant:</b>'||lv_ind_part||'</p> '||
                                         '<p><b>Where:</b> <b>Country:</b> '||:P1_ENG_CTRY ||',<b>City : </b>'||:P1_ENG_CITY ||'</p> '||                 
                                         '<p><b>When:</b> <b>Engagement Date:</b> ' ||:P1_ENG_DATE  ||'</p> '||
                                         '<p><b>Platts:</b> <b>Platts Lead:</b>'||:P1_ENG_LEAD1||', <b>Platts Participant:</b>'||lv_platts_lead||'</p> '||
                                          lv_Engagement||
                                          lv_Methodology||
                                          lv_Regulatory||
                                          lv_Notes_Commercail||
                                         '<p>'||replace(lv_Act_Item, CHR(13), '<BR>')||'</p>'||                
                                         '<a href="http://'||OWA_UTIL.GET_CGI_ENV('HTTP_HOST')||                  
                                          OWA_UTIL.GET_CGI_ENV('SCRIPT_NAME')||'/f?p='||:APP_ID||':1:::::P1_CHECK,P1_BUTTONHIDD,P1_DISPLAY_EID:N,UpdateMode,&P1_DISPLAY_EID.'||                                
                                          '">Click here to view '|| 
                                          '</a></p>';    
                            HTMLDB_MAIL.SEND(      
                            P_TO        => rec.EMAIL_ADDRESS,
                            P_FROM      => lv_from_email_id,    
                            P_BODY      => l_body_html,
                            P_BODY_HTML => l_body_html,
                            P_SUBJ        => 'Meeting   ('||:P1_DISPLAY_EID||'):('||:P1_ENG_SUBARY||')'|| lv_eng_type);      
                          end if;
                          end loop;
                        end;
                        
                        

                         

                        4. We do not have any assocaite table, we are logon uisng App_user which we are capturing this in our from address.

                         

                        Sruthitamiri!!

                        • 9. Re: Email address in apex through front end apex
                          jwellsnh

                          Here are the parts of this thread that are confusing to me:

                          You said at the start of this thread:

                          "when ever we select particular associate then i need to have redirect to that particular Distribution list"

                           

                          Now you say:

                          "We do not have any assocaite table, we are logon uisng App_user which we are capturing this in our from address."

                          So I have to assume that the logged on user is the "associate".

                           

                          You said earlier:

                          "My process contains as shown below after i had modified part of code is, [in this case i had selcted only one sector i.,e Oil, so it should genearte only one email id, but it got genearted seven times since data exists in table. Where there is hard coded i had used tables structure now."

                           

                          Is the above not attributed to this code below which appears to generating your To: address.  Are there not 7 rows in that table with an active_flag =- 'Y'?

                             FOR rec IN (  
                                      select EMAIL_ADDRESS   
                                      from ME_Main_Email_DL  
                                      where active_flag='Y'  
                                      and items is not null )  
                          Loop   
                          
                          

                           

                          So I assume the missing logic is how do you link a logged in user with a distribution list.  Am I correct in that assumption?

                           

                          Jeff

                          • 10. Re: Email address in apex through front end apex
                            Sruthi Tamiri

                            Yes Jeff, you are right the logon user is asscoiate and yes have 7 rows in table with active flasg as "Y", but from from end i had selcted only P1_ENG_SCT then only one mail has to trigger

                             

                            Sruthitamiri!!

                            • 11. Re: Email address in apex through front end apex
                              jwellsnh

                              Could you rephrase this part?  It is not clear what you are saying.

                              but from from end i had selcted only P1_ENG_SCT then only one mail has to trigger

                              Jeff

                              • 12. Re: Email address in apex through front end apex
                                Sruthi Tamiri

                                Sure Jeff,

                                 

                                 

                                 

                                1. In our application we have seven items were created as "P1_ENG_SCT" to "P1_ENG_SCT7"
                                2. By default when our application loads to end user will show only "P1_ENG_SCT" and others will be hidden uisng java script validations it was created.
                                3. The others i.,e ["P1_ENG_SCT2 to P1_ENG_SCT7] can be visible when user clicks on "Add Another" button.
                                4. So when user clicks on Add Another button a new row will be visible where we can select the different sector value or same value which he had selected for P1_ENG_SCT.
                                5. So the existing code shows as, it means either of seven sectors were selected it will send to only once to that particular group as shown down.
                                6. After re-frame the changes to our code by selecting from data base the mail is triggering for multiple times.

                                   
                                

                                 

                                 

                                 

                                if (
                                    upper('Oil')=upper(:P1_ENG_SCT)  or
                                    upper('Oil')=upper(:P1_ENG_SCT2) or
                                    upper('Oil')=upper(:P1_ENG_SCT3) or
                                    upper('Oil')=upper(:P1_ENG_SCT4) or
                                    upper('Oil')=upper(:P1_ENG_SCT5) or
                                    upper('Oil')=upper(:P1_ENG_SCT6) or
                                    upper('Oil')=upper(:P1_ENG_SCT7) 
                                    
                                 ) then 
                                if  lv_to_email_id is not null then 
                                    
                                       lv_to_email_id :=lv_to_email_id ||','|| 'oil@xyz.com';           
                                   else
                                       lv_to_email_id :='oil@xyz.com';
                                
                                

                                 

                                Regards,

                                Sruthitamiri

                                • 13. Re: Email address in apex through front end apex
                                  jwellsnh

                                  Hi Sruthitamiri,

                                   

                                  Is there a process, other than your email process, that is writing or updating rows to the table, ME_MAIN_EMAIL_DL, relative to ITEMS?

                                   

                                  Also, where is this code firing?

                                  if (  
                                      upper('Oil')=upper(:P1_ENG_SCT)  or  
                                      upper('Oil')=upper(:P1_ENG_SCT2) or  
                                      upper('Oil')=upper(:P1_ENG_SCT3) or  
                                      upper('Oil')=upper(:P1_ENG_SCT4) or  
                                      upper('Oil')=upper(:P1_ENG_SCT5) or  
                                      upper('Oil')=upper(:P1_ENG_SCT6) or  
                                      upper('Oil')=upper(:P1_ENG_SCT7)   
                                        
                                   ) then   
                                  if  lv_to_email_id is not null then   
                                        
                                         lv_to_email_id :=lv_to_email_id ||','|| 'oil@xyz.com';             
                                     else  
                                         lv_to_email_id :='oil@xyz.com';  
                                  
                                  

                                   

                                  Jeff

                                  • 14. Re: Email address in apex through front end apex
                                    Sruthi Tamiri

                                    Hi Jeff,

                                    No, we do not have any process to update that table, we have to insert manually. The reason we have created table is to fetch from the data base and the above code will be remove since it was hard coded in our application and we are trying to process using table concept. For reference we had provided the above code.

                                     

                                    Sruthitamiri