Wednesday, 16 March 2016

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 ;

Tuesday, 8 March 2016

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'

Thursday, 24 October 2013

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.