How to find Foreign Keys references to a Table

In order to find all the Foreign Keys that point to a certain Table, you can use the SQL bellow:

SELECT table_name,
  constraint_name,
  status,
  owner
FROM all_constraints
WHERE r_owner          = :owner
AND constraint_type    = 'R'
AND r_constraint_name IN
  (SELECT constraint_name
  FROM all_constraints
  WHERE constraint_type IN ('P', 'U')
  AND table_name         = :table_name
  AND owner              = :owner
  )
ORDER BY table_name,
  constraint_name ;

Add and Remove LOV entries in Forms

When editing LOV definitions, if you want to add or remove entries, you can use the following keyboard shortcuts:

• For deleting a line: ctrl+<
• For adding a new line: ctrl+> (ctrl+shift+<)

Split Comma-Separated List into multiple Rows

WITH t AS (SELECT 'aaaaa,bbbbbb,cccccc' cs_values FROM dual) select regexp_substr(cs_values, '[^,]+', 1, rownum) values_list FROM t CONNECT BY ROWNUM <= LENGTH(regexp_replace(cs_values, '[^,]'))+1 ;

Return Multiple Rows as a Single Comma-Separated Row

You can use the following SQL to return multiple rows as a single row:

select listagg(<column>, ',') within group (order by <column>) as list

FROM <table>

Check if string value is numeric

To test a string for numeric characters, you could use a combination of the LENGTH, TRIM, AND TRANSLATE functions in the following way:

LENGTH(TRIM(TRANSLATE('this_string', ' +-.0123456789', ' ')))

'this_string' is the string value that you are testing.

This function will return a null value if 'the_string' is numeric. It will return a number greater than 0 if 'the_string' contains any non-numeric characters.

Examples:

SQL	Result
LENGTH(TRIM(TRANSLATE('123b', ' +-.0123456789',' ')))	1
LENGTH(TRIM(TRANSLATE('a123b', ' +-.0123456789',' ')))	2
LENGTH(TRIM(TRANSLATE('1256.54', ' +-.0123456789',' ')))	null
LENGTH(TRIM(TRANSLATE ('-56', ' +-.0123456789',' ')))	null

List Responsibilities that can access a form

select rt.responsibility_name, metl.prompt

from fnd_responsibility_tl rt

,    fnd_responsibility r

,    fnd_menus m

,    fnd_menu_entries me

,    fnd_menu_entries_tl metl

,    fnd_form_functions ff

,    fnd_form f

where f.form_id = ff.form_id

and   ff.function_id = me.function_id

and   m.menu_id = me.menu_id

and   m.menu_id = metl.menu_id

and   me.entry_sequence = metl.entry_sequence

and   r.menu_id = m.menu_id

and   r.responsibility_id = rt.responsibility_id

and   metl.language = 'US'

and   f.form_name = 'FORM_NAME'

and   ff.function_name = 'FUNCTION_NAME'

Delete View Objects substitutions

Use jdr_utils.listcustomizations(“your standard VO path”) to find any substitution has been done for that object.

Use jdr_utils.deletedocument(“customization path displayed by listcustomization”) to delete customization.

Don’t forget to commit.