Form personalization Query

SELECT DISTINCT personalize_rule_sequence

    FROM (  SELECT fpt.application_name,

                   ff.form_name source_form_name,

                   fft.user_form_name,    -- fft.description form_description,

                   fff.function_name,

                   ffft.user_function_name,

                   ffft.description function_description,

                   ffcr.SEQUENCE personalize_rule_sequence,

                   ffcr.description personalize_rule_description,

                   DECODE (ffcr.rule_type,  'F', 'Form',  'A', 'Function')

                      personalize_rule_level,

                   ffcr.enabled personalize_rule_enabled,

                   ffcr.trigger_event personalize_rule_event,

                   ffcr.trigger_object,

                   ffcr.condition personalize_rule_condition,

                   DECODE (ffcs.level_id,

                           10, 'Industry',

                           20, 'Site',

                           30, 'Responsibility',

                           40, 'User')

                      context_level,

                   DECODE (ffcs.level_id,

                           10, '',

                           20, '',

                           30, frt.responsibility_name,

                           40, fu.user_name)

                      context_level_value,

                   ffca.SEQUENCE action_sequence,

                   DECODE (ffca.action_type,

                           'P', 'Property',

                           'M', 'Message',

                           'B', 'Builtin',

                           'S', 'Menu',

                           '')

                      action_type,        --  ffca.summary action_description,

                   ffca.enabled action_enabled,

                   DECODE (ffca.LANGUAGE,

                           '*', 'All',

                           'US', 'American English',

                           'AR', 'Arabic')

                      action_language,

                   DECODE (ffca.action_type, 'B', ffca.builtin_type, NULL)

                      action_builtin_type,

                   DECODE (ffca.action_type, 'B', ffca.builtin_arguments, NULL)

                      action_builtin_arguments,

                   ffcr.last_update_date

              FROM fnd_application fp,

                   fnd_application_tl fpt,

                   fnd_form ff,

                   fnd_form_tl fft,

                   fnd_form_functions fff,

                   fnd_form_functions_tl ffft,

                   fnd_form_custom_rules ffcr,

                   fnd_form_custom_scopes ffcs,

                   fnd_responsibility_tl frt,

                   fnd_user fu,

                   fnd_form_custom_actions ffca,

                   fnd_form_custom_prop_list ffcpl

             WHERE                                 ----------------APPLICATION

                  fp   .application_id = fpt.application_id

                   AND fpt.LANGUAGE = 'US'       ------------------------ FORM

                   AND fpt.application_id = ff.application_id

                   AND ff.form_id = fft.form_id

                   AND fft.LANGUAGE = 'US'   ------------------------ FUNCTION

                   AND ff.form_id = fff.form_id

                   AND fff.function_id = ffft.function_id

                   AND ffft.LANGUAGE = 'US' ------------------------ Custom Rule

                   AND ff.form_name = ffcr.form_name

                   AND ffcr.function_name = fff.function_name

                   ------------------------ Custom Scope

                   AND ffcr.ID = ffcs.rule_id

                   AND ffcs.level_value = frt.responsibility_id(+)

                   AND frt.LANGUAGE(+) = 'US'

                   AND ffcs.level_value = fu.user_id(+)

                   ------------------------ Custom Actions

                   AND ffcr.ID = ffca.rule_id

--                   AND DECODE (ffca.action_type, 'P', ffca.property_name, 79) =

--                          ffcpl.property_id

--                   AND DECODE (ffca.action_type, 'P', ffca.object_type, 'ITEM') =

--                          ffcpl.field_type

                   AND ff.form_name = 'GMEBDTED'

          --     AND ffcr.SEQUENCE IN (62, 64, 65, 66)

          ORDER BY fft.application_id,

                   ff.form_name,

                   ffcr.function_name,

                   ffcr.SEQUENCE,

                   ffcs.level_id,

                   ffcs.level_value,

                   ffca.SEQUENCE)

ORDER BY 1

--- 217

How to find the password of a User in Oracle Apps R12

CREATE FUNCTION apps.XX_decrypt_get_pwd (in_chr_key             IN VARCHAR2,
                                      in_chr_encrypted_pin   IN VARCHAR2)
   RETURN VARCHAR2
AS
   LANGUAGE JAVA
   NAME 'oracle.apps.fnd.security.WebSessionManagerProc.decrypt(java.lang.String,java.lang.String) return java.lang.String' ;
 

 SELECT usr.user_name,
      Xx_decrypt_get_pwd (
          (SELECT (SELECT xx_decrypt_get_pwd (
                             fnd_web_sec.get_guest_username_pwd,
                             usertable.encrypted_foundation_password)
                     FROM DUAL)
                     AS apps_password
             FROM fnd_user usertable
            WHERE usertable.user_name =
                     (SELECT SUBSTR (
                                fnd_web_sec.get_guest_username_pwd,
                                1,
                                  INSTR (fnd_web_sec.get_guest_username_pwd,
                                         '/')
                                - 1)
                        FROM DUAL)),
          usr.encrypted_user_password)
          PASSWORD
  FROM fnd_user usr
 WHERE usr.user_name = 'user_name';

How to delete datadefinition and Template

Script for deleting the datadefinition
————————————————>
BEGIN
XDO_DS_DEFINITIONS_PKG.DELETE_ROW(‘XX’,’XXHR_EUREH_GEOS_ERROR_REPORT’);
Commit;
END;
——————————————————–
——————————————————–
script for deleting the template
——————————————————>
BEGIN
XDO_TEMPLATES_PKG.DELETE_ROW(‘XX’,’XXHR_EUREH_GEOS_ERROR_REPORT’);
Commit;
END;

OR

declare
begin
delete   from XDO_TEMPLATES_B        where template_code    = <template_code>
delete   from XDO_TEMPLATES_TL       where template_code    = <template_code>
delete   from xdo_lobs               where lob_code         = <template_code>
delete   from XDO_DS_DEFINITIONS_TL  where data_source_code = <def_code>
delete   from XDO_DS_DEFINITIONS_b   where data_source_code = <def_code>
end;

Commit;

query to find users who have a responsibility

  SELECT usr.user_id,
         usr.user_name,
         res.RESPONSIBILITY_ID,
         res.RESPONSIBILITY_NAME
    FROM apps.FND_USER usr,
         apps.FND_RESPONSIBILITY_TL res,
         apps.FND_USER_RESP_GROUPS grp
   WHERE     (   UPPER (res.RESPONSIBILITY_NAME) LIKE '%SUZ%'
              OR UPPER (res.RESPONSIBILITY_NAME) LIKE '%NANJING%'
              OR UPPER (res.RESPONSIBILITY_NAME) LIKE '%SZ%')
         AND grp.responsibility_id = res.responsibility_id
         AND grp.user_id = usr.user_id
ORDER BY 2

Batch Status in OPM

select * from fnd_lookup_values where lookup_type  like 'BATCH_STATUS' AND LANGUAGE ='US'

How to attach concurrent program to request group using pl/sql script.

DECLARE

BEGIN

FND_PROGRAM.add_to_group

    (

     PROGRAM_SHORT_NAME  =>'XXHLFUSIONPHLFIINT'

    ,PROGRAM_APPLICATION =>'XXHL'

    ,REQUEST_GROUP       => 'US SHRMS Reports & Processes'

    ,GROUP_APPLICATION   =>'PER'

    ) ;   

  commit;

exception

    when others then

           dbms_output.put_line('Object already exists');

END;

/

Converter number to words in RTF level

<?xdoxslt:toWordsAmt(sum(AMOUNT))?>

<?xdoxslt:toWordsAmt(12345.98)?>


https://blogs.oracle.com/xmlpublisher/entry/numbers_to_words_update