Oracle Workflow to show html table in the Message Notification

1. Create the package, body as attached.

1. Attribute

2. Function, message, attribute under it. 

3. Attribute under message.

4. Using attribute in Message Body

Package: 

CREATE OR REPLACE PACKAGE APPS.XX_BODY_TXN_INFO_DOC_PKG
as
PROCEDURE XX_create_DOC_WF----for action history
(
document_id     IN              VARCHAR2,
display_type    IN              VARCHAR2,
DOCUMENT        IN OUT NOCOPY   VARCHAR2,
DOCUMENT_TYPE   IN OUT NOCOPY   VARCHAR2
);
PROCEDURE XX_DOC_CALL  --for calling
(itemtype in varchar2,
ITEMKEY IN VARCHAR2,
actid in number,
FUNCMODE IN VARCHAR2,
resultout out varchar2) ;

end;

--Package  Body
/

CREATE OR REPLACE PACKAGE BODY APPS.XX_BODY_TXN_INFO_DOC_PKG
as
--for history
PROCEDURE XX_create_DOC_WF
(
document_id     IN              VARCHAR2,
DISPLAY_TYPE    IN              VARCHAR2,
DOCUMENT        IN OUT NOCOPY   VARCHAR2,
document_type   IN OUT NOCOPY   VARCHAR2
)
IS
lv_details             VARCHAR2 (32767);
V_ITEMKEY          VARCHAR2(100);
amount number;

CURSOR CUR_QUALITF
IS


SELECT LINE_NUM,
    ASSET_NUMBER ,
    ENTITY,
    DEPARTMENT,
    UNITS,
    ASSET_DESCRIPTION,
    TAG_NUMBER,
    PO_NUMBER,
    SUPPLIER_NAME,
    ASSET_COST,
    ACCUMULATED_DEP,
    NET_BOOK_VALUE,
    ASSET_LIFE,
    DATE_PLACEDIN_SERVICE,
    ASSET_ID
FROM  XX_TABLE_APPROVALS_L
  WHERE DISPOSAL_ID = document_id
 ORDER BY LINE_NUM ASC;


BEGIN
/* TABLE HEADER*/
lv_details :=      lv_details
--|| '<h4> '
--|| 'Asset Details'
--|| '</H4>'
|| '<table border = "5" BORDERCOLOR="#B9D1EA"> <tr>'
--||'<b><caption align="left" BORDERCOLOR="#B9D1EA">Transaction Details</caption></b>' --if space in caption its coming in next line
|| '<th> '
|| 'Line'
|| '</th>'
|| '<th>'
|| 'Asset Number'
|| '</th>'
|| '</th>'
|| '<th>'
|| 'Entity'
|| '</th>'
|| '<th>'
|| 'Department'
|| '</th>'
|| '<th>'
|| 'Units'
|| '</th>'
|| '<th>'
|| 'Descriptin'
|| '</th>'
|| '<th>'
|| 'Tag Number'
|| '</th>'
|| '<th>'
|| 'PO Number'
|| '</th>'
|| '<th>'
|| 'Supplier Name'
|| '</th>'
|| '<th>'
|| 'Asset Cost'
|| '</th>'
|| '<th>'
|| 'Accumulated Dep'
|| '</th>'
|| '<th>'
|| 'NetBook Value'
|| '</th>'
|| '<th>'
|| 'Asset LIfe'
|| '</th>'
|| '<th>'
|| 'DatePlaceIn Service'
|| '</th>'
;

FOR CUR_QUALITF_REC IN CUR_QUALITF
loop
/*TABLE BODY */
lv_details:=        lv_details
|| '<tr>'
|| '<td>'
|| CUR_QUALITF_REC.LINE_NUM
|| '</td>'
|| '<td>'
|| CUR_QUALITF_REC.ASSET_NUMBER
|| '</td>'
|| '<td>'
|| CUR_QUALITF_REC.ENTITY
|| '</td>'
|| '<td>'
|| CUR_QUALITF_REC.DEPARTMENT
|| '</td>'
|| '<td>'
|| CUR_QUALITF_REC.UNITS
|| '</td>'
|| '<td>'
|| CUR_QUALITF_REC.ASSET_DESCRIPTION
|| '</td>'
|| '<td>'
|| CUR_QUALITF_REC.TAG_NUMBER
|| '</td>'
|| '<td>'
|| CUR_QUALITF_REC.PO_NUMBER
|| '</td>'
|| '<td>'
|| CUR_QUALITF_REC.SUPPLIER_NAME
|| '</td>'
|| '<td>'
|| CUR_QUALITF_REC.ASSET_COST
|| '</td>'
|| '<td>'
|| CUR_QUALITF_REC.ACCUMULATED_DEP
|| '</td>'
|| '<td>'
|| CUR_QUALITF_REC.NET_BOOK_VALUE
|| '</td>'
|| '<td>'
|| CUR_QUALITF_REC.ASSET_LIFE
|| '</td>'
|| '<td>'
|| CUR_QUALITF_REC.DATE_PLACEDIN_SERVICE
|| '</td>'
|| '</tr>'
;

--INSERT INTO TESTA VALUES(1,2,3);

end loop;

document :=LV_DETAILS||'</table>';

/*We have to determine document_type which is nothing but the mime type
document_type := 'image/jpg; name=filename.jpg';
Depending on the extension of the document the MIME type is determined. For simplicity
we are hard coding here*/
--—      document_type := 'application/pdf;name=test.pdf' ;  /* This syntax is used for PDF type of attachments */

document_type := 'text/html';

EXCEPTION
WHEN OTHERS
THEN
document := '<H4>Error ' || SQLERRM || '</H4>';

END;

PROCEDURE XX_DOC_CALL
(
itemtype in varchar2,
ITEMKEY IN VARCHAR2,
actid in number,
funcmode in varchar2,
resultout out varchar2
)
IS
V_DOCUMENT_ID CLOB;
v_itemkey  NUMBER;

BEGIN
V_DOCUMENT_ID :='PLSQL:XX_BODY_TXN_INFO_DOC_PKG.XX_create_DOC_WF/' || ITEMKEY;

/*Setting Value to the Document Type Attribute */

wf_engine.setitemattrtext (itemtype      => itemtype,
itemkey       => itemkey,
ANAME         => 'BODY_TXN_DET_INFO',
avalue        => V_DOCUMENT_ID
);

end;
end XX_BODY_TXN_INFO_DOC_PKG;
/

Oracle Workflow Attachments to shown in Notifications

     Create new Attribute XX1 with type document


    Add one more new attribute to the Message  #ATTACHMENTS
        Default value Attribute XX1

While creation/running the workflow add the atribute value as below:

              wf_engine.setItemAttrText(itemtype =>itemtype,
             itemkey =>itemkey,
             aname =>'XXASS_ATTACH',
             avalue=>'FND:entity=XXASSET_DISPOSAL'||'&'||'pk1name=DISPOSAL_ID'||'&'||'pk1value='||crec.header_id
             ) ;
             

The value for 'entity' can be obtained from DATA_OBJECT_CODE in FND_DOCUMENT_ENTITIES.

The value for 'pk1name' can be obtained from PK1_COLUMN in FND_DOCUMENT_ENTITIES.
The value for 'pk1value' can be obtained from PK1_VALUE in FND_ATTACHED_DOCUMENTS.     

Add New Responsibility , Menu Exclusions from others responsibility in Oracle Apps R12

1. Creating New Responsibility

2. Menu Exclusions to copy into new Responsibility from existing. 

 

1.

 

DECLARE

v_rowid varchar2(500);

BEGIN

fnd_responsibility_pkg.insert_row

( x_rowid => v_rowid,

x_responsibility_id => fnd_responsibility_s.NEXTVAL,

x_application_id => 201, -- Cash Management

x_web_host_name => NULL,

x_web_agent_name => NULL,

x_data_group_application_id => 201, -- Cash Management

x_data_group_id => 0, -- Standard

x_menu_id => 68071, -- CE_CASH_MANAGER

x_start_date => SYSDATE,

x_end_date => NULL,

x_group_application_id => 201,

x_request_group_id => 129,

x_version => 4,

x_responsibility_key => 'TEST_RESPONSIBILITY2',

x_responsibility_name => 'Test Responsibility2',

x_description => 'Responsibility for testing',

x_creation_date => SYSDATE,

x_created_by => -1,

x_last_update_date => SYSDATE,

x_last_updated_by => -1,

x_last_update_login => -1

);

EXCEPTION WHEN OTHERS THEN

DBMS_OUTPUT.PUT_LINE(SQLERRM);

END;

commit;

2. Adding menu exclusions..

DECLARE

v_resp_id number := 52634; --pass here the new responsibility_id

   CURSOR c1

   IS

   

     SELECT

application_id, responsibility_id, action_id,

                      rule_type,

                      last_updated_by, last_update_date, creation_date,

                      created_by, last_update_login

                      FROM fnd_resp_functions

                      WHERE RESPONSIBILITY_ID = 50626; --getting all the menu exlusions from existing

                      

BEGIN

   FOR i IN c1

   LOOP

      BEGIN

         DBMS_OUTPUT.put_line ('Processing');

         INSERT INTO fnd_resp_functions

                     (application_id, responsibility_id, action_id,

                      rule_type,

                      last_updated_by, last_update_date, creation_date,

                      created_by, last_update_login

                     )

              VALUES (i.application_id, v_resp_id, i.action_id,

                      i.rule_type,

                      2605, SYSDATE, SYSDATE,

                      2605, -1

                     );

      EXCEPTION

         WHEN OTHERS

         THEN

            DBMS_OUTPUT.put_line (SQLERRM);

      END;

   END LOOP;

EXCEPTION

   WHEN OTHERS

   THEN

      DBMS_OUTPUT.put_line ('Completed');

END; 

Update fnd_user all user passwords in clone Oracle Apps r12

DECLARE
l_unenc_pwd VARCHAR2(1000);
CURSOR c1 IS
SELECT *

FROM fnd_user fu 

WHERE fu.user_name='MOGALAFZAL'--comment this line to get all users.

;
BEGIN
FOR I IN c1 LOOP
l_unenc_pwd:=dbms_random.string('x',6);
fnd_user_pkg.UpdateUser(x_user_name=>'001C.ARNAUD@GMAIL.COM'
                       ,x_owner=>'SEED'
                       ,x_unencrypted_password=>l_unenc_pwd--'12345'

                       ,x_password_date=>to_date('2', 'J')
                       ,x_user_guid=>I.user_GUID
                       );
COMMIT;                      
END LOOP;                                           
END;

Other useful random:
select TRUNC(DBMS_RANDOM.value(1,10)) from dual;

Positive Integers:
select abs(dbms_random.random) from dual --> for updating mobile number, EID

Updating Translations for lookups in Oracle Apps R12

The below code with update the Transalation of Lookups.
Example: Arabic Translation.

First you need to upload lookups normally using the below link.
https://mogalafzal.blogspot.com/2020/05/uploading-lookups-in-oracle-apps-r12.html


/*
1. set the language you want to update.

alter session set NLS_LANGUAGE ='ARABIC';--'ARABIC' AMERICAN

select userenv('lang') from dual

COMMIT

2. custom interface table.

    CREATE TABLE XXLOOKUP_CODES_AR
    (LOOKUP_CODE VARCHAR2(30),
     MEANING_EN VARCHAR2(240),
     MEANING_AR VARCHAR2(240),
     DESCRIPTION_EN VARCHAR2(240),
     DESCRIPTION_AR VARCHAR2(240),
     ATTRIBUTE1 VARCHAR2(240),
     ATTRIBUTE2 VARCHAR2(240))
   
     alter table xxlookup_codes_ar
add (lkp_err_msg varchar2(240))

update xxlookup_codes_ar
set lkp_process_flag_ar = 'N';

SELECT * FROM xxlookup_codes_ar

SELECT * FROM FND_LOOKUP_VALUES_VL
WHERE LOOKUP_TYPE = 'TESTA'

*/



DECLARE
   CURSOR get_lookup_details
   IS
 
      SELECT ltype.application_id,
             ltype.customization_level,
             ltype.creation_date,
             ltype.created_by,
             ltype.last_update_date,
             ltype.last_updated_by,
             ltype.last_update_login,
             tl.lookup_type,
             tl.security_group_id,
             tl.view_application_id,
             tl.description,
             tl.meaning
        FROM fnd_lookup_types_tl tl, fnd_lookup_types ltype
       WHERE     ltype.lookup_type = 'FFZA_LICENSE_TYPE'
             AND ltype.lookup_type = tl.lookup_type
             AND language = 'US';

   CURSOR get_value
   IS
 

      SELECT DISTINCT
             lookup_code,
             meaning_en,
             meaning_ar,
             description_en, description_ar,lkp_err_msg,lkp_process_flag_ar
        FROM xxlookup_codes_ar
       WHERE     lkp_process_flag_ar = 'N'   
       and lookup_code <> '58'
       --AND LENGTH(substr(meaning_ar,1,80)) > 60
     
       ;

   l_err_msg   VARCHAR2 (1000) := NULL;
   l_db_nls_language       VARCHAR2 (500) := NULL;
   l_db_nls_language2 varchar2(30) := 'ARABIC';
   L_USERNAME VARCHAR2(30) := 'EG_AFZAL'; --USERNAME
BEGIN
   FOR i IN get_lookup_details
   LOOP
      FOR j IN get_value
      LOOP
         BEGIN
            fnd_lookup_values_pkg.translate_row (
               x_lookup_type           => i.lookup_type,
               x_security_group_id     => i.security_group_id,
               x_view_application_id   => i.view_application_id,
               x_owner                 => L_USERNAME, --> User NAME
               x_meaning               => j.meaning_ar,
               x_description           => j.description_ar,
               x_lookup_code           => j.lookup_code,
               X_LAST_UPDATE_DATE      => NULL,--TO_DATE('4/22/2020 9:20:58 PM'),
               X_CUSTOM_MODE            => 'FORCE');

            UPDATE xxlookup_codes_ar --> Stagging Table to Log Status
               SET lkp_process_flag_ar = 'Y', lkp_err_msg = NULL
             WHERE lookup_code = j.lookup_code;

            COMMIT;
         EXCEPTION
            WHEN OTHERS
            THEN
               l_err_msg := SQLERRM;

               UPDATE xxlookup_codes_ar
                  SET lkp_process_flag_ar = 'N', lkp_err_msg = l_err_msg
                 WHERE lookup_code = j.lookup_code;

               COMMIT;
         END;
      END LOOP;
   END LOOP;
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line ('Main Exception: ' || SQLERRM);
END;

Uploading Lookups in Oracle Apps R12 (English Only)

custom interface table.

    CREATE TABLE XXLOOKUP_CODES_AR
    (LOOKUP_CODE VARCHAR2(30),
     MEANING_EN VARCHAR2(240),
     MEANING_AR VARCHAR2(240),
     DESCRIPTION_EN VARCHAR2(240),
     DESCRIPTION_AR VARCHAR2(240),
     ATTRIBUTE1 VARCHAR2(240),
     ATTRIBUTE2 VARCHAR2(240))
   
     alter table xxlookup_codes_ar
add (lkp_err_msg varchar2(240))

update xxlookup_codes_ar
set lkp_process_flag_ar = 'N';

SELECT * FROM xxlookup_codes_ar



alter session set NLS_LANGUAGE ='AMERICAN';--'ARABIC' AMERICAN



DECLARE
   V_ERROR  VARCHAR2(4000);
   CURSOR get_lookup_details
   IS
 
      SELECT ltype.application_id,
             ltype.customization_level,
             ltype.creation_date,
             ltype.created_by,
             ltype.last_update_date,
             ltype.last_updated_by,
             ltype.last_update_login,
             tl.lookup_type,
             tl.security_group_id,
             tl.view_application_id,
             tl.description,
             tl.meaning
        FROM fnd_lookup_types_tl tl, fnd_lookup_types ltype
       WHERE     ltype.lookup_type = 'FFZA_LICENSE_TYPE'
             AND ltype.lookup_type = tl.lookup_type
             AND language = 'US';

   CURSOR get_country
   IS
 
      SELECT DISTINCT
             lookup_code,
             meaning_en,
             meaning_ar,
             description_en, description_ar
        FROM xxlookup_codes_ar
       WHERE     lkp_process_flag_ar = 'N'     
       and lookup_code <> '58'
       ;
     
   
   l_rowid   VARCHAR2 (100) := 0;
BEGIN
   FOR i IN get_lookup_details
   LOOP
      FOR j IN get_country
      LOOP
         l_rowid := NULL;
         BEGIN
            fnd_lookup_values_pkg.insert_row (
               x_rowid                 => l_rowid,
               x_lookup_type           => i.lookup_type,
               x_security_group_id     => i.security_group_id,
               x_view_application_id   => i.view_application_id,
               x_lookup_code           => j.lookup_code,
               x_tag                   => '',
               x_attribute_category    => NULL,
               x_attribute1            => NULL,
               x_attribute2            => NULL,
               x_attribute3            => NULL,
               x_attribute4            => NULL,
               x_enabled_flag          => 'Y',
               x_start_date_active     =>NULL,
               x_end_date_active       => NULL,
               x_territory_code        => NULL,
               x_attribute5            => NULL,
               x_attribute6            => NULL,
               x_attribute7            => NULL,
               x_attribute8            => NULL,
               x_attribute9            => NULL,
               x_attribute10           => NULL,
               x_attribute11           => NULL,
               x_attribute12           => NULL,
               x_attribute13           => NULL,
               x_attribute14           => NULL,
               x_attribute15           => NULL,
               x_meaning               => j.meaning_en,
               x_description           => j.description_en,--j.B,
               x_creation_date         => SYSDATE,
               x_created_by            => i.created_by,
               x_last_update_date      => i.last_update_date,
               x_last_updated_by       => i.last_updated_by,
               x_last_update_login     => i.last_update_login);

            COMMIT;

          -- update XX_LOOKUP_VALUES set upload_status= 'DONE' WHERE LOOKUP_CODE=J.LOOKUP_CODE;
         EXCEPTION
            WHEN OTHERS
            THEN
             V_ERROR := (SQLERRM);
             DBMS_OUTPUT.PUT_LINE(V_ERROR);
              --   update XX_LOOKUP_VALUES set upload_status= 'Inner Exception: ' || V_ERROR WHERE LOOKUP_CODE=J.LOOKUP_CODE;
         END;
      END LOOP;
   END LOOP;
--EXCEPTION
--   WHEN OTHERS
--   THEN
--      update XX_COUNTRY set upload_status='Main Exception: ' || V_ERROR  WHERE LOOKUP_CODE=J.LOOKUP_CODE;
END;

Oracle Report using profile security Apps r12

To Initialize Apps in Oracle Report: BELOW 3 steps mandatory(3.1 to 3.3) and use the secured tables in query.
3.1. Before report trigger
 
  srw.USER_EXIT ('FND SRWINIT');
fnd_global.apps_initialize(FND_PROFILE.VALUE('USER_ID'),FND_PROFILE.VALUE('RESP_ID'),FND_PROFILE.VALUE('RESP_APPL_ID'));
  mo_global.init ('S');

3.2. After Report Trigger

SRW.USER_EXIT('FND SRWEXIT');

3.3 Add user parameter

P_CONC_REQUEST_ID  datatype - Number, Width - 200