Send Email by SQL/PL (ORACLE EBSR12)

 

NOTE MAKE SURE THE ACL IT WORKING CORRECTLY

CREATE OR REPLACE PROCEDURE send_mail (p_to        IN VARCHAR2,
                                       p_cc        IN VARCHAR2 DEFAULT NULL,
                                       p_bcc       IN VARCHAR2 DEFAULT NULL,
                                       p_from      IN VARCHAR2,
                                       p_subject   IN VARCHAR2,
                                       p_message   IN VARCHAR2,
                                       p_smtp_host IN VARCHAR2,
                                       p_smtp_port IN NUMBER DEFAULT 25)
AS
  l_mail_conn   UTL_SMTP.connection;
  
  PROCEDURE process_recipients(p_mail_conn IN OUT UTL_SMTP.connection,
                               p_list      IN     VARCHAR2)
  AS
    l_tab string_api.t_split_array;
  BEGIN
    IF TRIM(p_list) IS NOT NULL THEN
      l_tab := string_api.split_text(p_list);
      FOR i IN 1 .. l_tab.COUNT LOOP
        UTL_SMTP.rcpt(p_mail_conn, TRIM(l_tab(i)));
      END LOOP;
    END IF;
  END;
BEGIN
  l_mail_conn := UTL_SMTP.open_connection(p_smtp_host, p_smtp_port);
  UTL_SMTP.helo(l_mail_conn, p_smtp_host);
  UTL_SMTP.mail(l_mail_conn, p_from);
  process_recipients(l_mail_conn, p_to);
  process_recipients(l_mail_conn, p_cc);
  process_recipients(l_mail_conn, p_bcc);

  UTL_SMTP.open_data(l_mail_conn);
  
  UTL_SMTP.write_data(l_mail_conn, 'Date: ' || TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS') || UTL_TCP.crlf);
  UTL_SMTP.write_data(l_mail_conn, 'To: ' || p_to || UTL_TCP.crlf);
  IF TRIM(p_cc) IS NOT NULL THEN
    UTL_SMTP.write_data(l_mail_conn, 'CC: ' || REPLACE(p_cc, ',', ';') || UTL_TCP.crlf);
  END IF;
  IF TRIM(p_bcc) IS NOT NULL THEN
    UTL_SMTP.write_data(l_mail_conn, 'BCC: ' || REPLACE(p_bcc, ',', ';') || UTL_TCP.crlf);
  END IF;
  UTL_SMTP.write_data(l_mail_conn, 'From: ' || p_from || UTL_TCP.crlf);
  UTL_SMTP.write_data(l_mail_conn, 'Subject: ' || p_subject || UTL_TCP.crlf);
  UTL_SMTP.write_data(l_mail_conn, 'Reply-To: ' || p_from || UTL_TCP.crlf || UTL_TCP.crlf);
  
  UTL_SMTP.write_data(l_mail_conn, p_message || UTL_TCP.crlf || UTL_TCP.crlf);
  UTL_SMTP.close_data(l_mail_conn);

  UTL_SMTP.quit(l_mail_conn);
EN

query of PR (Purchase Requisition Approval Action History)

 SELECT hou.name ou_name,

hou.short_code ou_short_code, prha.segment1 pr_number, (SELECT pah.action_date FROM po_action_history pah WHERE prha.requisition_header_id = pah.object_id AND pah.object_type_code = 'REQUISITION' AND pah.sequence_num = (SELECT MIN(sequence_num) FROM po_action_history pah1 WHERE pah1.action_code = 'SUBMIT' AND prha.requisition_header_id = pah1.object_id AND pah1.object_type_code= 'REQUISITION' ) ) pr_last_submission_date, prha.creation_date, (SELECT prla.catalog_type FROM po_requisition_lines_all prla WHERE prha.requisition_header_id = prla.requisition_header_id AND ROWNUM < 2 ) pr_type, NVL2((SELECT prla.urgent_flag FROM po_requisition_lines_all prla WHERE prha.requisition_header_id = prla.requisition_header_id AND urgent_flag = 'Y' AND ROWNUM < 2 ),'Y','N' ) urgent_flag, auth_status.meaning, authorization_status pr_status, pah.sequence_num action_seq_num, papf.full_name action_person, pah.action_code, pah.action_date, pah.note, prha.change_pending_flag FROM po_requisition_headers_all prha, fnd_lookup_values_vl auth_status, hr_operating_units hou, po_action_history pah, per_all_people_f papf WHERE prha.org_id = hou.organization_id AND prha.requisition_header_id = pah.object_id AND papf.person_id = pah.employee_id AND SYSDATE BETWEEN papf.effective_start_date AND papf.effective_end_date AND pah.object_type_code = 'REQUISITION' AND auth_status.lookup_type = 'AUTHORIZATION STATUS' AND auth_status.lookup_code = prha.authorization_status;

Translate the oracle workflow process name and description in Arabic (or any other language)

 Before applying the below steps, run the below sql query to check what is the display_name, description in other languages.

ex:

SELECT * FROM WF_ACTIVITIES_TL WHERE ITEM_TYPE='HRSSA'

AND NAME='HR_EIT_VEN_PRC'; --> it is same in english for both US, AR languages

1.  download hrssa.wtf file from the workflow loader and open the file in notepad++ or any editor.

change in the file as below

1.1 LANGUAGE = "AR" in the line 9

1.2 update all the process_names display and description in arabic

1.3 save the file under the /orasb/XXCLONE/apps/apps_st/appl/per/12.0.0/patch/115/import/AR

2. in Putty :  export NLS_LANG=ARABIC_AMERICA.UTF8  

3. WFLOAD apps/apps 0 Y UPLOAD /orasb/DEVAP/apps/apps_st/appl/per/12.0.0/patch/115/import/AR/hrssa.wft

Testing:

run the above query to check the display_name, description changed in to lang.

SELECT * FROM V$NLS_PARAMETERS;

alter session set NLS_LANGUAGE ='ARABIC'

ARABIC.AMERICA.UTF8

FNDLOAD for multi-language(ARABIC - ENGLISH)

FNDLOAD for Arabic and English

we want to move Lookups, Concurrent Programs, Messages etc with their translation also for Multilingual/Territory like English and Arabic.

In such scenario we have to download and upload LDTs for both languages.

Lets suppose we have to move Concurrent Program with both English and Arabic translations.

DOWNLOAD

Before downloading we have to set the export language style like,

For English

*****************************************************************

export NLS_LANG=AMERICAN_AMERICA.AR8MSWIN1256

*****************************************************************

FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afcpprog.lct XXTST_CONCURRENT_PRG_US.ldt PROGRAM APPLICATION_SHORT_NAME="XXTST" CONCURRENT_PROGRAM_NAME="XXTST_CONCURRENT_PRG"

For Arabic

*****************************************************************

export NLS_LANG=ARABIC_AMERICA.AR8MSWIN1256

*****************************************************************

FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afcpprog.lct XXTST_CONCURRENT_PRG_AR.ldt PROGRAM APPLICATION_SHORT_NAME="XXTST" CONCURRENT_PROGRAM_NAME="XXTST_CONCURRENT_PRG"

*****************************************************************

UPLOAD

Similarly for Upload LDTs we have to set the language style.

For English           

set NLS_LANG=AMERICAN_AMERICA.AR8MSWIN1256

FNDLOAD apps/apps O Y UPLOAD $FND_TOP/patch/115/import/afcpprog.lct XX_CONCURRENT_PRG_US.ldt UPLOAD_MODE=REPLACE CUSTOM_MODE=FORCE

*****************************************************************

For Arabic

set NLS_LANG=ARABIC_AMERICA.AR8MSWIN1256

FNDLOAD apps/apps O Y UPLOAD $FND_TOP/patch/115/import/afcpprog.lct XX_CONCURRENT_PRG_AR.ldt UPLOAD_MODE=REPLACE CUSTOM_MODE=FORCE

how to change All HRMS WorkFlow Notifications To Arabic

case

we needs to Download The HR Workflow but instead of English Descriptions and all the Subjects we needs to appears it by Arabic language so instead of Update every Notification Subject for Example we will Download it all using FNDLoad Command as Arabic translated then Upload it when needed aftet

 Solution

Before applying the below steps, run the below sql query to check what is the display_name, description in other languages.

ex:

SELECT * FROM WF_ACTIVITIES_TL WHERE ITEM_TYPE='HRSSA' AND NAME='HR_EIT_VEN_PRC'; --> it is same in english for both US, AR languages

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

Step1:  update nls_lang (regedit then search for NLS of ORACLE) to be AMERICAN_AMERICA.AR8MSWIN1256

Step2 : region language of the local Machine

 

Nav: Control Pannelà RegionàAdministrativeà

Step3 : make sure or alter table of fnd_languages  as per the is Query

select * from wf_languages where INSTALLED_FLAG = 'Y'

Step :4 using putty export script as below

export NLS_LANG='ARABIC_AMERICA.AR8MSWIN1256'

WFLOAD apps/apps 0 Y DOWNLOAD HR.wft HRSSA

Thanks 

Taher Amin 

Mail to :- e.taher.amin@Gmail.com

 

run workflow from backend

 CREATE PROCEDURE xx_activity (

   errbuf            IN OUT NOCOPY   VARCHAR2,
   errcode           IN OUT NOCOPY   INTEGER,
   p_itemtype        IN              VARCHAR2,
   p_activity_name   IN              VARCHAR2
)
AS
   v_errorname      VARCHAR2 (30);
   v_errormsg       VARCHAR2 (2000);
   v_errorstack     VARCHAR2 (32000);
   invalid_action   EXCEPTION;
   PRAGMA EXCEPTION_INIT (invalid_action, -20002);

   CURSOR c1
   IS
      SELECT item_key
        FROM wf_item_activity_statuses
       WHERE item_type = p_itemtype
         AND activity_status = 'NOTIFIED'
         AND process_activity IN (
                SELECT MAX (instance_id)
                  FROM wf_process_activities
                 WHERE activity_item_type = p_itemtype
                   AND activity_name = p_activity_name);
--AND item_key = 1228692;
BEGIN
   FOR c1_rec IN c1
   LOOP
      BEGIN
         fnd_file.put_line (fnd_file.output,
                            'EXECUTING FOR ITEM-KEY: ' || c1_rec.item_key
                           );
         wf_engine.completeactivity (itemtype      => p_itemtype,
                                     itemkey       => c1_rec.item_key,
                                     activity      => p_activity_name,
                                     --'xx_INSUFF_RESPON_BLOCK',
                                     RESULT        => wf_engine.eng_null
                                    );
         COMMIT;
      EXCEPTION
         WHEN invalid_action
         THEN
            wf_core.get_error (v_errorname, v_errormsg, v_errorstack);
            fnd_file.put_line (fnd_file.LOG, 'ITEM-KEY: ' || c1_rec.item_key);
            fnd_file.put_line (fnd_file.LOG, 'ERROR NAME: ' || v_errorname);
            fnd_file.put_line (fnd_file.LOG, 'ERROR MESSAGE: ' || v_errormsg);
            fnd_file.put_line (fnd_file.LOG, 'ERROR STACK: ' || v_errorstack);
      END;
   END LOOP;
END xx_activity;

OAF by SSPatil

 

Partial Page Rendering (PPR) in OAF

PARTIAL PAGE RENDERING (PPR) IN OAF

PPR is a technology that lets you refresh a specific part of a page when you want to make a UI change instead of having to refresh (redraw) the entire page.

Gives users a more responsive and interactive application

Improves application Performance

Steps for Partial Page Rendering:-

First create one workspace and project.

Create the AM.

Create one Page and attach the page AM to it.

Create one Entity object and VO and attach to AM.

Create the controller.

For PartialRendering here I am using my data insert project only.

The structure of my data insert project is like this.

The output of my Data Insert page is like.

For partial Rendering in the above example 

If the position code is “DIRECTOR” it should hide the managerID

For this first make the position code item as message choice.

position-->itemStyle-->messegeChoice

                  DATA-->

piclistViewInstance-->Give the view instance name

                                    PicklistDisplayAttribute-->what we want to display in message choice

                                                                                 the column name of that(Meaning)

                                    PiclistValueAttribute-->what value shuld take.

Next create one PartialVO without any query.

And after that create one transient attriobute.

Right click on PartialVO and select edit.

Click new.

U will get the window for creating new attribute set the following properties.

Name:- ManagerIDRendered

Type:- BOOLEAN.

 Click Apply and ok.

After creating the partialVO.

Select the positionCode item and in the property inspector set the following properties.

Client Action---- >

           Action Type-- > firePartialAction.

           Event --- > positionEvent.

Select the managerID item and set the following properties

managerid-->visual

                        ->Rendered-->${oa.<Partial View Instance Name>.<New Attribute Name>}

                                                ${oa.PartialVO1. ManagerIdRendered}

Create one method in AM java file

  public void Partialvo()

    {

              OAViewObject vo=getPartialVO1();   //we have to create one new partialvo

              if(!vo.isPreparedForExecution())

              {

               vo.executeQuery();

              }

             

              OARow row=(OARow)vo.createRow();

              vo.insertRow(row);

              vo.first().setAttribute("ManagerIdRendered",Boolean.TRUE);

                                                                         

    }

Create the method for hide and show of managerID

 public void showManager(String PositionCode)

    {

       OAViewObject vo=getPartialVO1();

       if(PositionCode.equalsIgnoreCase("DIRECTOR"))

       {

         vo.first().setAttribute("ManagerIdRendered",Boolean.FALSE);

       }

       else

       {

           vo.first().setAttribute("ManagerIdRendered",Boolean.TRUE);

       }

    }

Write the following code in processRequest of the controller.

 OAApplicationModule am=(OAApplicationModule)pageContext.getRootApplicationModule();

 //call the method  we created for creating new row

am.invokeMethod("Partialvo");

Write the following code in the processFormRequest.

   if(eventName.equalsIgnoreCase("positionEvent"))  //the event name that we given in

                                                                                                position column

    {

      String position=pageContext.getParameter("PositionCode"); //the id of the position column

     

      Serializable [] parm={position};

      am.invokeMethod("showManager",parm);//we have to call the method that we created for showing managerid

    }

  }

Run the page to see the output.

If u select the position as director the manegerID should hide.