Oracle Applications Utility Sql Scripts

AIM TECHNICAL DESIGN Oracle Applications Utility SQL Scripts Team:

Technology

Creation Date:

1 July 2009

Created By:

Brendan Furey ([email protected])

Last Updated:

2 July 2009

Control:

19307158.doc

Version:

1.1

Approvals:

Document Control

Change Record Date

Author

Version

Change Reference

01-Jul-2009 02-Jul-2009

BP Furey BP Furey

1.0 1.1

Initial Organisation

Document Control

ii

Contents Document Control............................................................................................... .........ii Change Record........................................................................... ..........................ii Introduction.......................................................................................................... ........4 Technical Overview................................................................................ .....................5 Structure............................................................................................................... .5 SQL Script List.................................................................................. ....................5 Script Header............................................................................................................ ...7 Time and Instance........................................................................................... ......7 FND Concurrent Requests.......................................................................................... .8 Concurrent Requests by Program code.............................................. ..................8 Concurrent Requests Hierarchy................................................ ..........................11 FND Setup: Programs and Request Sets...................................................... ............13 Programs and Parameters................................................................ ..................13 Programs and Request Groups.................................................................... .......13 Request Sets............................................................................................... ........14 FND Setup: Responsibilities and Menus............................................ .......................16 Responsibilities............................................................................. ......................16 Menus.................................................................................................................. 17 FND Flexfields.................................................................................................... .......21 Key Flexfields...................................................................................................... 21 Descriptive Flexfields................................................................................... .......21 Key Flexfield Descriptions................................................................................. ..22 Data Dictionary: Schema Summaries............................................. ..........................24 Tables........................................................................................................ ..........24 Tables and Columns..................................................................................... .......25 Indexes............................................................................................................. ...26 Tables with No Who Columns or No (or Id Only) Unique Indexes......................27 HR: Organizations................................................................................ .....................28 Inventory Organizations...................................................................... ................28 Organizations by Class............................................................... ........................28 References......................................................................................... .......................30 Appendix: Example Outputs............................................................................. .........31

Document Control

iii

Introduction This document lists the code, with some design details, for various utility SQL report scripts (and one PL/SQL script) that I have written over a number of years mainly around Oracle Applications foundation schema (FND). I find them useful for support and development purposes. All scripts have been run against Oracle Applications 11.5.10 (see REF-1), and database version 10g 10.2.0.3.0.

Technical Overview

4

Technical Overview Structure The SQL statements described here are normally run from within a script file, but can also be run directly via SQL*Plus or SQL*Developer etc. I have omitted the SQL*Plus formatting and other commands. Typically a file will follow the structure: •

Global formatting commands (SET PAGESIZE etc.)

•

Parameter statements (DEFINE)

•

SPOOL to file

•

Write start time, database instance etc. (Script Header below)

•

Column formatting commands (COLUMN, BREAK etc.)

•

SQL

•

Write end time

SQL Script List Description Script Header Time and Instance FND Concurrent Requests Concurrent Requests by Program code Concurrent Requests Hierarchy

Notes Start time, database instance etc. It is run at the start of a script to capture timing information, and to avoid doubt at a later date about where the output came from Concurrent requests with user, responsibility, duration, etc., usually for a specified program. It includes the non-null parameter values Concurrent requests with user, responsibility, duration, etc., usually for a specified datetime range. Tree-walk SQL

FND Setup: Programs and Request Sets Programs and Parameters Concurrent programs definitions with parameters and defaults Programs and Request Groups Concurrent programs definitions with request groups and responsibilities Request Sets Request set definitions including defaults, at both program and set level FND Setup: Responsibilities and Menus Responsibilities grouped by their associated profile values for set of Responsibilities books and operating unit, and by application This script lists the menu structure for responsibilities matching an input prefix. There are several parameters that control the detail of the output and other features. In particular the structure can be shown corresponding to what the user sees (with window scrolling) after expanding all in the navigator screen, or can include subfunctions and technical details. Menus This was written initially in 1999 when the Oracle SQL tree walk functionality was much more basic than it is now and could not be used for this purpose. The PL/SQL program uses a recursive procedure call, without which the code would be much more complex, and would be limited to fixed depth menus FND Flexfields Key Flexfields

Key flexfield definitions with contexts, columns and value sets Technical Overview

5

Descriptive Flexfields Key Flexfield Descriptions

Descriptive flexfield definitions with contexts, columns and value sets Translates a concatenated KFF values string into the descriptions from the value sets. KFF structure is read from tables – not hard-coded. It is harder than it seems to do this in a single SQL – the SQL uses nested inline views, with analytic functions, parses the value-string dynamically, and handles parent-child relationships

Data Dictionary: Schema Summaries The SQL statements here are normally run together for a schema and the results saved to a text file, which can then be referred to for table/column/index information. It is often quicker than opening the relevant nodes individually in a TOAD-style interface, and the outputs are ordered and organised for maximum convenience Tables Tables in a schema with summary information on columns, indexes etc Tables and columns in a schema, ordered by table and column names (unlike the DESC command). Columns such as DFF attributes can be Tables and Columns excluded from the list but counted at table level to minimise clutter. The SQL illustrates a number of useful techniques, including analytic functions, inline views, and row-column pivotting Indexes Indexes in a schema, ordered by table, index and column sequence Tables with No Who Columns or No Tables in a schema that have one of a number of characteristics that may (or Id Only) Unique Indexes indicate a design omission or problem, such as absence of Who columns HR: Organizations These are useful for quickly understanding the structure of a company running Oracle Applications Inventory Organizations Inventory organizations (warehouses) Organizations by Class Organizations by class

Technical Overview

6

Script Header Time and Instance This script writes start time, database instance etc. and is run at the start of a script to capture timing information, and to avoid doubt at a later date about where the output came from. Output Fields Name Database Session OS User Machine Time Version

Description Database instance name Session ID Operating system user name Machine ID Date and time Oracle version

Code SELECT /* BP Furey, 1 July 2009 */ 'Start: '||dbs.name "Database", ses.sid "Session", ses.osuser "OS User", ses.machine "Machine", To_Char (SYSDATE,'DD-MON-YYYY HH24:MI:SS') "Time", Replace (Substr(ver.banner, 1, Instr(ver.banner, '64')-4), 'Enterprise Edition Release ', '') "Version" FROM v$database dbs, v$version ver, v$session ses WHERE ver.banner LIKE 'Oracle%' AND audsid = USERENV('sessionid');

Technical Overview

7

FND Concurrent Requests Concurrent Requests by Program code This script lists concurrent requests with user, responsibility, duration, etc., usually for a specified program. It includes the non-null parameter values. If the delimiter parameter is set to Chr(10) parameters are listed on a multi-line basis. Input Parameters Name conc_name user_name start_date end_date exclude delim

Description Program short name User name of request initiator Request start date Request end date Program short name to exclude Parameter delimiter

Output Fields Name Id CP User Status Request Time Start Time Duration Responsibility Parameters

Description Request id + parent request id if there is one Program long name User name of request initiator Phase + Status codes Request time Actual start time Duration in minutes to 1dp Responsibility used to initiate request Non-null parameters separated by a delimiter

Code SELECT /* BP Furey, 1 July 2009 */ req.request_id || CASE req.parent_request_id WHEN -1 THEN NULL ELSE '(->' || req.parent_request_id || ')' END "Id", Substr(prg.user_concurrent_program_name, 1, 50) "CP", usr.user_name "User", req.phase_code || '/' || req.status_code "Status", To_Char(req.request_date, 'dd-mon-yy hh24:mi:ss') "Request Time", To_Char(req.actual_start_date, 'dd-mon-yy hh24:mi:ss') "Start Time", Round(24*60*(Nvl(req.actual_completion_date, SYSDATE)-req.actual_start_date), 2) "Duration", rsp.responsibility_name "Responsibility", CASE WHEN req.argument1 != Chr(0) THEN '1 : ' || req.argument1 || &delim END CASE WHEN req.argument2 != Chr(0) THEN '2 : ' || req.argument2 || &delim END CASE WHEN req.argument3 != Chr(0) THEN '3 : ' || req.argument3 || &delim END CASE WHEN req.argument4 != Chr(0) THEN '4 : ' || req.argument4 || &delim END CASE WHEN req.argument5 != Chr(0) THEN '5 : ' || req.argument5 || &delim END CASE WHEN req.argument6 != Chr(0) THEN '6 : ' || req.argument6 || &delim END CASE WHEN req.argument7 != Chr(0) THEN '7 : ' || req.argument7 || &delim END CASE WHEN req.argument8 != Chr(0) THEN '8 : ' || req.argument8 || &delim END CASE WHEN req.argument9 != Chr(0) THEN '9 : ' || req.argument9 || &delim END CASE WHEN req.argument10 != Chr(0) THEN '10: ' || req.argument10 || &delim END CASE WHEN req.argument11 != Chr(0) THEN '11: ' || req.argument11 || &delim END CASE WHEN req.argument12 != Chr(0) THEN '12: ' || req.argument12 || &delim END CASE WHEN req.argument13 != Chr(0) THEN '13: ' || req.argument13 || &delim END CASE WHEN req.argument14 != Chr(0) THEN '14: ' || req.argument14 || &delim END CASE WHEN req.argument15 != Chr(0) THEN '15: ' || req.argument15 || &delim END CASE WHEN req.argument16 != Chr(0) THEN '16: ' || req.argument16 || &delim END CASE WHEN req.argument17 != Chr(0) THEN '17: ' || req.argument17 || &delim END CASE WHEN req.argument18 != Chr(0) THEN '18: ' || req.argument18 || &delim END CASE WHEN req.argument19 != Chr(0) THEN '19: ' || req.argument19 || &delim END CASE WHEN req.argument20 != Chr(0) THEN '20: ' || req.argument20 || &delim END CASE WHEN req.argument21 != Chr(0) THEN '21: ' || req.argument21 || &delim END

Technical Overview

|| || || || || || || || || || || || || || || || || || || || ||

8

CASE CASE CASE CASE CASE CASE CASE CASE CASE CASE CASE CASE CASE CASE CASE CASE CASE CASE CASE CASE CASE CASE CASE CASE CASE CASE CASE CASE CASE CASE CASE CASE CASE CASE CASE CASE CASE CASE CASE CASE CASE CASE CASE CASE CASE CASE CASE CASE CASE CASE CASE CASE CASE CASE CASE CASE CASE CASE CASE CASE CASE CASE CASE CASE CASE CASE CASE CASE CASE CASE CASE CASE CASE CASE CASE CASE CASE CASE

WHEN WHEN WHEN WHEN WHEN WHEN WHEN WHEN WHEN WHEN WHEN WHEN WHEN WHEN WHEN WHEN WHEN WHEN WHEN WHEN WHEN WHEN WHEN WHEN WHEN WHEN WHEN WHEN WHEN WHEN WHEN WHEN WHEN WHEN WHEN WHEN WHEN WHEN WHEN WHEN WHEN WHEN WHEN WHEN WHEN WHEN WHEN WHEN WHEN WHEN WHEN WHEN WHEN WHEN WHEN WHEN WHEN WHEN WHEN WHEN WHEN WHEN WHEN WHEN WHEN WHEN WHEN WHEN WHEN WHEN WHEN WHEN WHEN WHEN WHEN WHEN WHEN WHEN

req.argument22 req.argument23 req.argument24 req.argument25 arg.argument26 arg.argument27 arg.argument28 arg.argument29 arg.argument30 arg.argument31 arg.argument32 arg.argument33 arg.argument34 arg.argument35 arg.argument36 arg.argument37 arg.argument38 arg.argument39 arg.argument40 arg.argument41 arg.argument42 arg.argument43 arg.argument44 arg.argument45 arg.argument46 arg.argument47 arg.argument48 arg.argument49 arg.argument50 arg.argument51 arg.argument52 arg.argument53 arg.argument54 arg.argument55 arg.argument56 arg.argument57 arg.argument58 arg.argument59 arg.argument60 arg.argument61 arg.argument62 arg.argument63 arg.argument64 arg.argument65 arg.argument66 arg.argument67 arg.argument68 arg.argument69 arg.argument70 arg.argument71 arg.argument72 arg.argument73 arg.argument74 arg.argument75 arg.argument76 arg.argument77 arg.argument78 arg.argument79 arg.argument80 arg.argument81 arg.argument82 arg.argument83 arg.argument84 arg.argument85 arg.argument86 arg.argument87 arg.argument88 arg.argument89 arg.argument90 arg.argument91 arg.argument92 arg.argument93 arg.argument94 arg.argument95 arg.argument96 arg.argument97 arg.argument98 arg.argument99

!= != != != != != != != != != != != != != != != != != != != != != != != != != != != != != != != != != != != != != != != != != != != != != != != != != != != != != != != != != != != != != != != != != != != != != != != != != != != != !=

Chr(0) Chr(0) Chr(0) Chr(0) Chr(0) Chr(0) Chr(0) Chr(0) Chr(0) Chr(0) Chr(0) Chr(0) Chr(0) Chr(0) Chr(0) Chr(0) Chr(0) Chr(0) Chr(0) Chr(0) Chr(0) Chr(0) Chr(0) Chr(0) Chr(0) Chr(0) Chr(0) Chr(0) Chr(0) Chr(0) Chr(0) Chr(0) Chr(0) Chr(0) Chr(0) Chr(0) Chr(0) Chr(0) Chr(0) Chr(0) Chr(0) Chr(0) Chr(0) Chr(0) Chr(0) Chr(0) Chr(0) Chr(0) Chr(0) Chr(0) Chr(0) Chr(0) Chr(0) Chr(0) Chr(0) Chr(0) Chr(0) Chr(0) Chr(0) Chr(0) Chr(0) Chr(0) Chr(0) Chr(0) Chr(0) Chr(0) Chr(0) Chr(0) Chr(0) Chr(0) Chr(0) Chr(0) Chr(0) Chr(0) Chr(0) Chr(0) Chr(0) Chr(0)

THEN THEN THEN THEN THEN THEN THEN THEN THEN THEN THEN THEN THEN THEN THEN THEN THEN THEN THEN THEN THEN THEN THEN THEN THEN THEN THEN THEN THEN THEN THEN THEN THEN THEN THEN THEN THEN THEN THEN THEN THEN THEN THEN THEN THEN THEN THEN THEN THEN THEN THEN THEN THEN THEN THEN THEN THEN THEN THEN THEN THEN THEN THEN THEN THEN THEN THEN THEN THEN THEN THEN THEN THEN THEN THEN THEN THEN THEN

'22: '23: '24: '25: '26: '27: '28: '29: '30: '31: '32: '33: '34: '35: '36: '37: '38: '39: '40: '41: '42: '43: '44: '45: '46: '47: '48: '49: '50: '51: '52: '53: '54: '55: '56: '57: '58: '59: '60: '61: '62: '63: '64: '65: '66: '67: '68: '69: '70: '71: '72: '73: '74: '75: '76: '77: '78: '79: '80: '81: '82: '83: '84: '85: '86: '87: '88: '89: '90: '91: '92: '93: '94: '95: '96: '97: '98: '99:

' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' '

|| || || || || || || || || || || || || || || || || || || || || || || || || || || || || || || || || || || || || || || || || || || || || || || || || || || || || || || || || || || || || || || || || || || || || || || || || || || || || ||

req.argument22 req.argument23 req.argument24 req.argument25 arg.argument26 arg.argument27 arg.argument28 arg.argument29 arg.argument30 arg.argument31 arg.argument32 arg.argument33 arg.argument34 arg.argument35 arg.argument36 arg.argument37 arg.argument38 arg.argument39 arg.argument40 arg.argument41 arg.argument42 arg.argument43 arg.argument44 arg.argument45 arg.argument46 arg.argument47 arg.argument48 arg.argument49 arg.argument50 arg.argument51 arg.argument52 arg.argument53 arg.argument54 arg.argument55 arg.argument56 arg.argument57 arg.argument58 arg.argument59 arg.argument60 arg.argument61 arg.argument62 arg.argument63 arg.argument64 arg.argument65 arg.argument66 arg.argument67 arg.argument68 arg.argument69 arg.argument70 arg.argument71 arg.argument72 arg.argument73 arg.argument74 arg.argument75 arg.argument76 arg.argument77 arg.argument78 arg.argument79 arg.argument80 arg.argument81 arg.argument82 arg.argument83 arg.argument84 arg.argument85 arg.argument86 arg.argument87 arg.argument88 arg.argument89 arg.argument90 arg.argument91 arg.argument92 arg.argument93 arg.argument94 arg.argument95 arg.argument96 arg.argument97 arg.argument98 arg.argument99

|| &delim END || || &delim END || || &delim END || || &delim END || || &delim END || || &delim END || || &delim END || || &delim END || || &delim END || || &delim END || || &delim END || || &delim END || || &delim END || || &delim END || || &delim END || || &delim END || || &delim END || || &delim END || || &delim END || || &delim END || || &delim END || || &delim END || || &delim END || || &delim END || || &delim END || || &delim END || || &delim END || || &delim END || || &delim END || || &delim END || || &delim END || || &delim END || || &delim END || || &delim END || || &delim END || || &delim END || || &delim END || || &delim END || || &delim END || || &delim END || || &delim END || || &delim END || || &delim END || || &delim END || || &delim END || || &delim END || || &delim END || || &delim END || || &delim END || || &delim END || || &delim END || || &delim END || || &delim END || || &delim END || || &delim END || || &delim END || || &delim END || || &delim END || || &delim END || || &delim END || || &delim END || || &delim END || || &delim END || || &delim END || || &delim END || || &delim END || || &delim END || || &delim END || || &delim END || || &delim END || || &delim END || || &delim END || || &delim END || || &delim END || || &delim END || || &delim END || || &delim END || END "Parameters"

Technical Overview

9

FROM fnd_concurrent_requests LEFT JOIN fnd_conc_request_arguments ON arg.request_id (+) JOIN fnd_concurrent_programs_vl ON prg.concurrent_program_id AND prg.application_id JOIN fnd_user ON usr.user_id JOIN fnd_responsibility_vl ON rsp.application_id AND rsp.responsibility_id WHERE prg.concurrent_program_name AND prg.concurrent_program_name AND usr.user_name AND req.request_date AND To_Date('&end_date', 'YYYY/MM/DD HH24:MI:SS') AND usr.user_id ORDER BY 1, 2, 3, 4, 5 DESC;

req arg = req.request_id prg = req.concurrent_program_id = req.program_application_id usr = req.requested_by rsp = req.responsibility_application_id = req.responsibility_id LIKE Upper(('&conc_name'||'%')) != '&exclude' LIKE Upper(('&user_name'||'%')) BETWEEN To_Date('&start_date', 'YYYY/MM/DD HH24:MI:SS') = req.requested_by

Technical Overview

10

Concurrent Requests Hierarchy This script lists concurrent requests with user, responsibility, duration, etc., usually for a specified datetime range. It uses a tree-walk (i.e. CONNECT BY) to show any request hierarchies, including request set structures. Child requests are indented, but only up to 4 levels (as the level can be arbitrarily high) Input Parameters Name conc_name user_name start_date end_date exclude_cp1 exclude_ucp1 exclude_ucp2 exclude_ucp3 Output Fields Name Id Parent Id CP Type User Status Start Time Duration Responsibility

Description Program short name User name of request initiator Request start date Request end date Program short name to exclude Program long names to exclude

Description Request id Parent request id if there is one Program long name Request type = Program/Stage/Set User name of request initiator Phase + Status codes Actual start time Duration in minutes to 1dp Responsibility used to initiate request

Code SELECT /* BP Furey, 1 July 2009 */ CASE WHEN LEVEL < 10 THEN LPad(' ', 2*level-1) ELSE '*** ' || LEVEL || ' ***' END || req.request_id "Id", CASE req.parent_request_id WHEN -1 THEN NULL ELSE req.parent_request_id END "Parent", CASE req.request_type WHEN 'P' THEN Substr(prg.user_concurrent_program_name, 1, 50) ELSE Nvl(req.description, Substr(prg.user_concurrent_program_name, 1, 50)) END "CP", CASE req.request_type WHEN 'P' THEN 'Program' WHEN 'S' THEN 'Stage' WHEN 'M' THEN 'Set' ELSE req.request_type END "Type", usr.user_name "User", req.phase_code || '/' || req.status_code "Status", To_Char(req.actual_start_date, 'dd-mon-yy hh24:mi:ss') "Start Time", Round(24*60*(Nvl(req.actual_completion_date, SYSDATE)-req.actual_start_date), 2) "Duration", rsp.responsibility_name "Responsibility" FROM fnd_concurrent_requests req JOIN fnd_concurrent_programs_vl prg ON prg.concurrent_program_id = req.concurrent_program_id AND prg.application_id = req.program_application_id JOIN fnd_user usr ON usr.user_id = req.requested_by JOIN fnd_responsibility_vl rsp ON rsp.application_id = req.responsibility_application_id

Technical Overview

11

AND rsp.responsibility_id = req.responsibility_id WHERE req.request_date BETWEEN To_Date('&start_date', 'YYYY/MM/DD HH24:MI:SS') AND To_Date('&end_date', 'YYYY/MM/DD HH24:MI:SS') AND prg.concurrent_program_name LIKE Upper(('&conc_name'||'%')) AND prg.concurrent_program_name NOT IN ('&exclude_cp1') AND CASE req.request_type WHEN 'P' THEN Substr(prg.user_concurrent_program_name, 1, 50) ELSE Nvl(req.description, Substr(prg.user_concurrent_program_name, 1, 50)) END NOT IN ('&exclude_ucp1', '&exclude_ucp2', '&exclude_ucp3') AND usr.user_name LIKE Upper(('&user_name'||'%')) CONNECT BY PRIOR req.request_id = req.parent_request_id AND LEVEL < 4 START WITH req.parent_request_id = - 1 ORDER SIBLINGS BY req.request_id;

Technical Overview

12

FND Setup: Programs and Request Sets Programs and Parameters This script lists concurrent programs with their parameters. Input Parameters Name cp

Description Concurrent program code

Output Fields Name App Program Executable Seq Parameter Default Value Set

Description Application short name Concurrent program code Executable name Parameter sequence Parameter name Parameter default Parameter value set name

Code SELECT /* BP Furey, 1 July 2009 */ app.application_short_name prg.concurrent_program_name fex.execution_file_name fcu.column_seq_num fcu.end_user_column_name fcu.default_value fvs.flex_value_set_name FROM fnd_concurrent_programs_vl JOIN fnd_application ON app.application_id LEFT JOIN fnd_executables ON fex.executable_id AND fex.application_id LEFT JOIN fnd_descr_flex_column_usages ON fcu.descriptive_flexfield_name prg.concurrent_program_name AND fcu.descriptive_flex_context_code LEFT JOIN fnd_flex_value_sets ON fvs.flex_value_set_id WHERE prg.concurrent_program_name ORDER BY 1, 2, 3, 4, 5

"App", "Program", "Executable", "Seq", "Parameter", "Default", "Value Set" prg app = prg.application_id fex = prg.executable_id = prg.executable_application_id fcu = '$SRS$.'|| = 'Global Data Elements' fvs = fcu.flex_value_set_id LIKE Upper('&cp'||'%')

Programs and Request Groups This script lists concurrent programs with the request groups to which they have been assigned, and associated responsibilities. It can be useful for finding which responsibility to run a report from. Input Parameters Name cp

Description Concurrent program code

Output Fields Name App Program User Program

Description Application short name Concurrent program code Concurrent program name

Technical Overview

13

#Pars Request Group Responsibility

Number of parameters Request group name Responsibility name

Code SELECT /* BP Furey, 1 July 2009 */ app.application_short_name "App", prg.concurrent_program_name "Program", prg.user_concurrent_program_name "User Program", Nvl(par.n_pars, 0) "#Pars", rgs.request_group_name "Request Group", rsp.responsibility_name "Responsibility" FROM fnd_concurrent_programs_vl prg JOIN fnd_application app ON app.application_id = prg.application_id LEFT JOIN (SELECT fcu.descriptive_flexfield_name, Count(fcu.descriptive_flexfield_name) n_pars FROM fnd_descr_flex_column_usages fcu WHERE fcu.descriptive_flex_context_code = 'Global Data Elements' GROUP BY fcu.descriptive_flexfield_name) par ON par.descriptive_flexfield_name = '$SRS$.' || prg.concurrent_program_name LEFT JOIN (SELECT rgp.request_group_id, rgp.request_group_name, rgu.unit_application_id application_id, rgu.request_unit_id concurrent_program_id FROM fnd_request_groups rgp JOIN fnd_request_group_units rgu ON rgu.application_id = rgp.application_id AND rgu.request_group_id = rgp.request_group_id) rgs ON rgs.application_id = prg.application_id AND rgs.concurrent_program_id = prg.concurrent_program_id LEFT JOIN fnd_responsibility_vl rsp ON rsp.request_group_id = rgs.request_group_id WHERE prg.concurrent_program_name LIKE Upper('&cp'||'%') ORDER BY 1, 2, 3, 4

Request Sets This script lists request sets with their stages, programs and parameters with both program and set level defaults. Input Parameters Name Request Set

Description Request set name

Output Fields Name Seq S Stage Seq P Program Seq A Parameter Default Value Set RS Default

Description Stage sequence Stage name Program sequence Program name Parameter sequence Parameter name Program default Value set name Request set default

Code SELECT /* BP Furey, 1 July 2009 */ stg.display_sequence stg.user_stage_name rsp.sequence fcp.user_concurrent_program_name || ': ' || fcp.concurrent_program_name fcu.column_seq_num

"Seq S", "Stage", "Seq P", "Program", "Seq A",

Technical Overview

14

fcu.end_user_column_name fcu.default_value fvs.flex_value_set_name rsa.default_value FROM fnd_request_sets_vl LEFT JOIN fnd_req_set_stages_form_v ON stg.set_application_id AND stg.request_set_id LEFT JOIN fnd_request_set_programs ON rsp.set_application_id AND rsp.request_set_id AND rsp.request_set_stage_id LEFT JOIN fnd_concurrent_programs_vl ON fcp.concurrent_program_id AND fcp.application_id LEFT JOIN fnd_descr_flex_column_usages ON fcu.descriptive_flexfield_name AND fcu.descriptive_flex_context_code LEFT JOIN fnd_flex_value_sets ON fvs.flex_value_set_id LEFT JOIN fnd_request_set_program_args ON rsa.application_id AND rsa.request_set_id AND rsa.request_set_program_id AND rsa.application_column_name WHERE rst.user_request_set_name ORDER BY 1, 3, 5;

rst stg = rst.application_id = rst.request_set_id rsp = stg.set_application_id = stg.request_set_id = stg.request_set_stage_id fcp = rsp.concurrent_program_id = rsp.program_application_id fcu = '$SRS$.'||fcp.concurrent_program_name = 'Global Data Elements' fvs = fcu.flex_value_set_id rsa = rsp.set_application_id = rsp.request_set_id = rsp.request_set_program_id = fcu.application_column_name LIKE '&rset_name%'

"Parameter", "Default", "Value Set", "RS Default"

Technical Overview

15

FND Setup: Responsibilities and Menus Responsibilities This script lists responsibilities grouped by their associated profile values for set of books and operating unit, and by application. Seeded responsibilities are excluded and it’s parametrised in the version below to run for one or all applications. Input Parameters Name app

Description Application short name

Output Fields Name SOB OU App HUT Name Key Updated By

Description Set of books profile value Operating unit profile value Application short name HR user type profile value Responsibility name Responsibility key Last update date Last update user

Code SELECT /* BP Furey, 1 July 2009 */ Nvl (pov_sob.profile_option_value, 'Xilinx, Def') "SOB", Nvl (hoi_r.attribute1, 'DEF') "OU", app.application_short_name "App", Nvl (pov_hut.profile_option_value, 'DEF') "HUT", rsp.responsibility_name "Name", rsp.responsibility_key "Key", To_Char (rsp.last_update_date, 'dd-Mon-yy hh24:mi:ss') "Updated", usr.user_name || ' (' || usr.description || ')' "By" FROM fnd_responsibility_vl rsp JOIN fnd_application app ON app.application_id = rsp.application_id JOIN fnd_user usr ON usr.user_id = rsp.last_updated_by LEFT JOIN fnd_profile_options_vl pop_sob ON pop_sob.profile_option_name = 'GL_SET_OF_BKS_NAME' LEFT JOIN fnd_profile_option_values pov_sob ON pov_sob.level_value = rsp.responsibility_id AND pov_sob.level_value_application_id = rsp.application_id AND pov_sob.level_id = 10003 AND pov_sob.profile_option_id = pop_sob.profile_option_id LEFT JOIN fnd_profile_options_vl pop ON pop.profile_option_name = 'ORG_ID' LEFT JOIN fnd_profile_option_values pov ON pov.level_value = rsp.responsibility_id AND pov.level_value_application_id = rsp.application_id AND pov.level_id = 10003 AND pov.profile_option_id = pop.profile_option_id LEFT JOIN hr_organization_information hoi_r ON hoi_r.org_information_context = 'Operating Unit Information' AND hoi_r.organization_ID = To_Number(pov.profile_option_value) LEFT JOIN fnd_profile_options_vl pop_hut ON pop_hut.profile_option_name = 'HR_USER_TYPE' LEFT JOIN fnd_profile_option_values pov_hut ON pov_hut.level_value = rsp.responsibility_id AND pov_hut.level_value_application_id = rsp.application_id AND pov_hut.level_id = 10003 AND pov_hut.profile_option_id = pop_hut.profile_option_id WHERE app.application_short_name LIKE '&app' || '%' AND usr.user_name NOT IN ('AUTOINSTALL', 'INITIAL SETUP') ORDER BY 1, 2, 3

Technical Overview

16

Menus This script lists the menu structure for responsibilities matching an input prefix. There are several parameters that control the detail of the output and other features. In particular the structure can be shown corresponding to what the user sees (in segments) after expanding all in the navigator screen, or can include subfunctions and technical details. This was written initially in 1999 when the Oracle SQL tree walk functionality was much more basic than it is now and could not be used for this purpose. This PL/SQL program uses a recursive procedure call, without which the code would be much more complex, and would be limited to fixed depth menus. Input Parameters Name resp_prefix details tech_flag include_subfunctions utl_flag Output Fields Name SOB OU App Resp Menu HR: User Type Menu Item Technical Details Summary

Description Responsibility name prefix Write the menu items Y/N flag Write the technical details such as form file names Y/N flag Include the subfunctions that are not visible to users Y/N flag Write the output to a named file rather than to standard output using UTL_File Y/N flag

Description Set of books profile value Operating unit profile value Application short name Responsibility name Menu name HR user type profile value Menu item prompt Item type: Function/Menu name/Form name Number of items and customised forms (beginning XX)

Code DECLARE

/* BP Furey, 1 July 2009 */

Technical Overview

17

c_utl_file_loc c_utl_file_name c_prof_sob c_prof_ou c_prof_hut

CONSTANT CONSTANT CONSTANT CONSTANT CONSTANT

VARCHAR2(200) VARCHAR2(200) VARCHAR2(200) VARCHAR2(200) VARCHAR2(200)

:= := := := :=

'/usr/tmp'; 'menus.txt'; 'GL_SET_OF_BKS_NAME'; 'ORG_ID'; 'HR_USER_TYPE';

CURSOR c_rsp IS SELECT Nvl (Nvl (pov_sob.profile_option_value, pov_sob_s.profile_option_value), '(default)') sob, Nvl (hoi_r.attribute1, '(default)') ou, app.application_short_name app, Nvl(pov_hut.profile_option_value, '(null)') hut, rsp.menu_id, rst.responsibility_name, rsp.responsibility_id, rsp.responsibility_key, rsp.application_id, mnu.menu_name FROM fnd_responsibility_tl rst JOIN fnd_responsibility rsp ON rsp.responsibility_id = rst.responsibility_id AND rsp.application_id = rst.application_id JOIN fnd_application app ON app.application_id = rsp.application_id JOIN fnd_menus mnu ON mnu.menu_id = rsp.menu_id LEFT JOIN fnd_profile_options_vl pop_sob ON pop_sob.profile_option_name = c_prof_sob LEFT JOIN fnd_profile_option_values pov_sob ON pov_sob.level_value = rsp.responsibility_id AND pov_sob.level_value_application_id = rsp.application_id AND pov_sob.level_id = 10003 AND pov_sob.profile_option_id = pop_sob.profile_option_id LEFT JOIN fnd_profile_option_values pov_sob_s ON pov_sob_s.level_id = 10001 AND pov_sob_s.profile_option_id = pop_sob.profile_option_id LEFT JOIN fnd_profile_options_vl pop ON pop.profile_option_name = c_prof_ou LEFT JOIN fnd_profile_option_values pov ON pov.level_value = rsp.responsibility_id AND pov.level_value_application_id = rsp.application_id AND pov.level_id = 10003 AND pov.profile_option_id = pop.profile_option_id LEFT JOIN fnd_profile_option_values pov_s ON pov_s.level_id = 10001 AND pov_s.profile_option_id = pop.profile_option_id LEFT JOIN hr_organization_information hoi_r ON hoi_r.org_information_context = 'Operating Unit Information' AND hoi_r.organization_id = To_Number (Nvl(pov.profile_option_value, pov_s.profile_option_value)) LEFT JOIN fnd_profile_options_vl pop_hut ON pop_hut.profile_option_name = c_prof_hut LEFT JOIN fnd_profile_option_values pov_hut ON pov_hut.level_value = rsp.responsibility_id AND pov_hut.level_value_application_id = rsp.application_id AND pov_hut.level_id = 10003 AND pov_hut.profile_option_id = pop_hut.profile_option_id WHERE rst.responsibility_name LIKE '&resp_prefix'||'%' ORDER BY 1, 2, 3, 6; g_file_ptr g_sob g_ou g_app

UTL_File.file_type; VARCHAR2(100) := 'NONE'; VARCHAR2(100) := 'NONE'; VARCHAR2(100) := 'NONE';

g_include_subfunctions g_level l_menu n_menus n_menus_vi t_n_menus t_n_menus_vi n_resp do_detail do_tech is_utl

BOOLEAN := INTEGER := INTEGER; INTEGER; INTEGER; INTEGER := INTEGER := INTEGER := BOOLEAN := BOOLEAN := BOOLEAN :=

'&include_subfunctions' = 'Y'; 0;

0; 0; 0; Upper('&details') = 'Y'; Upper('&tech_flag') = 'Y'; Upper('&utl_flag') = 'Y';

PROCEDURE Write_Out (p_line VARCHAR2) IS BEGIN

Technical Overview

18

IF is_utl THEN UTL_File.Put_Line (g_file_ptr, p_line); ELSE DBMS_Output.Put_Line (p_line); END IF; END Write_Out; PROCEDURE Write_One_Level (p_menu_id NUMBER, p_responsibility_id NUMBER, p_application_id NUMBER) IS CURSOR c_menu IS SELECT Nvl(met.prompt, '('||fun.user_function_name||')') mname, mne.entry_sequence mno, mne.sub_menu_id, app.application_short_name, Nvl(fun.function_name, mnu.menu_name) function, frm.form_name form, mne.function_id, mne.entry_sequence, Decode( mne.sub_menu_id, NULL, Nvl(fun.type, 'NULL'), 'Menu') type FROM fnd_menu_entries mne, fnd_menu_entries_tl met, fnd_form_functions_vl fun, fnd_application app, fnd_form frm, fnd_menus mnu WHERE mne.menu_id = p_menu_id AND met.entry_sequence = mne.entry_sequence AND mne.grant_flag = 'Y' AND met.menu_id = mne.menu_id AND fun.function_id (+) = mne.function_id AND ((Nvl(fun.type,'x') != 'SUBFUNCTION' -- SUBFUNCTION entries don't appear on menu AND met.prompt IS NOT NULL -- null prompt entries don't appear on menu ) OR '&include_subfunctions' = 'Y') AND frm.form_id (+) = fun.form_id AND app.application_id (+)= Nvl(fun.application_id,0) AND frm.application_id (+)= Nvl(fun.application_id,0) AND mnu.menu_id (+) = mne.sub_menu_id AND NOT EXISTS ( SELECT 'Exclusion' FROM fnd_resp_functions frf WHERE frf.application_id = p_application_id AND frf.responsibility_id = p_responsibility_id AND (frf.action_id = mne.sub_menu_id OR frf.action_id = mne.function_id) ) AND (mne.function_id IS NOT NULL OR EXISTS (SELECT 'Submenu has an item' FROM fnd_menu_entries fmes, fnd_menu_entries_tl mets, fnd_form_functions funs WHERE fmes.menu_id = mne.sub_menu_id AND fmes.grant_flag = 'Y' AND mets.entry_sequence = fmes.entry_sequence AND mets.menu_id = fmes.menu_id AND funs.function_id (+) = fmes.function_id AND ( (Nvl(funs.type,'x') != 'SUBFUNCTION' AND mets.prompt IS NOT NULL ) OR '&include_subfunctions' = 'Y') AND NOT EXISTS (SELECT 'Exclusion' FROM fnd_resp_functions frfs WHERE frfs.application_id = p_application_id AND frfs.responsibility_id = p_responsibility_id AND (frfs.action_id = fmes.sub_menu_id OR frfs.action_id = fmes.function_id) ) ) ) ORDER BY 2; l_tech_bit VARCHAR2(255); FUNCTION Is_Customized (p_application_short_name VARCHAR2) RETURN BOOLEAN IS BEGIN RETURN p_application_short_name LIKE 'XX%'; END Is_Customized; BEGIN g_level := g_level + 1; FOR r_menu IN c_menu LOOP IF do_detail OR Is_Customized (r_menu.application_short_name) THEN IF do_tech THEN

Technical Overview

19

l_tech_bit := r_menu.type||':'||r_menu.function||'/'||r_menu.form; END IF; Write_Out (RPad(RPad(RPad ('.', 2*g_level, ' ')||r_menu.mname,60)||l_tech_bit, 115)); END IF; n_menus := n_menus + 1; IF Is_Customized (r_menu.application_short_name) THEN n_menus_vi := n_menus_vi + 1; END IF; IF r_menu.sub_menu_id IS NOT NULL THEN Write_One_Level (r_menu.sub_menu_id, p_responsibility_id, p_application_id); END IF; END LOOP; g_level := g_level - 1; END Write_One_Level; PROCEDURE Print_Group_Header (p_label VARCHAR2, p_level PLS_INTEGER, p_text VARCHAR2, p_text_old IN OUT VARCHAR2) IS BEGIN IF p_text != p_text_old THEN p_text_old := p_text; Write_Out ('****'); Write_Out (RPad ('*', p_level, ' ') || p_label ||': ' || p_text); END IF; END Print_Group_Header; BEGIN IF is_utl THEN g_file_ptr := UTL_File.FOpen (c_utl_file_loc, c_utl_file_name, 'w', 6000); END IF; FOR r_rsp IN c_rsp LOOP Print_Group_Header ('SOB', 3, r_rsp.sob, g_sob); Print_Group_Header ('OU', 6, r_rsp.ou, g_ou); Print_Group_Header ('App', 9, r_rsp.app, g_app); n_resp := n_resp + 1; Write_Out ('****'); Write_Out ('Resp: ' || r_rsp.responsibility_name || '/' ||r_rsp.responsibility_key || ', Menu: ' || r_rsp.menu_name || ', OU: ' || r_rsp.ou || ', HR: User Type: ' || r_rsp.hut); Write_Out ('****'); n_menus := 0; n_menus_vi := 0; Write_One_Level (r_rsp.menu_id, r_rsp.responsibility_id, r_rsp.application_id); Write_Out ('----'); Write_Out ('Menu: '||r_rsp.responsibility_name||' has '||To_Char(n_menus)||' items including '|| To_Char(n_menus_vi)||' customized forms'); Write_Out ('----'); t_n_menus := t_n_menus + n_menus; t_n_menus_vi := t_n_menus_vi + n_menus_vi; END LOOP; Write_Out ('Total for '||To_Char(n_resp)||' responsibilities: '||To_Char(t_n_menus)||' items including '||To_Char(t_n_menus_vi)||' customized forms'); IF is_utl THEN UTL_File.FClose (g_file_ptr); END IF; END;

Technical Overview

20

FND Flexfields Key Flexfields This script lists key flexfield definitions with contexts, columns and value sets. Input Parameters Name TITLE

Description Key flexfield title

Output Fields Name context title usercol colname value_set

Description Key flexfield context Key flexfield title User column name Column name Value set name

Code SELECT /* BP Furey, 1 July 2009 */ str.id_flex_structure_code str.description seg.segment_name seg.application_column_name fvs.flex_value_set_name FROM fnd_id_flexs JOIN fnd_id_flex_structures_vl ON str.application_id AND str.id_flex_code JOIN fnd_id_flex_segments_vl ON seg.application_id AND seg.id_flex_num AND seg.id_flex_code JOIN fnd_flex_value_sets ON fvs.flex_value_set_id WHERE flx.description ORDER BY str.id_flex_num, seg.segment_num;

context, title, usercol, colname, value_set flx str = flx.application_id = flx.id_flex_code seg = str.application_id = str.id_flex_num = str.id_flex_code fvs = seg.flex_value_set_id LIKE '&TITLE' || '%'

Descriptive Flexfields This script lists descriptive flexfield definitions with contexts, columns and value sets. Input Parameters Name TITLE

Description Descriptive flexfield title

Output Fields Name context colname usercol form_left_prompt

Description Descriptive flexfield context Column name User column name Form prompt

Code SELECT /* BP Furey, 1 July 2009 */ dfc.descriptive_flex_context_code

context,

Technical Overview

21

fcu.application_column_name colname, fcu.end_user_column_name usercol, fcu.form_left_prompt FROM fnd_descriptive_flexs_vl flx JOIN fnd_descr_flex_contexts_vl dfc ON dfc.application_id = flx.application_id AND dfc.descriptive_flexfield_name = flx.descriptive_flexfield_name JOIN fnd_descr_flex_col_usage_vl fcu ON fcu.application_id = dfc.application_id AND fcu.descriptive_flexfield_name = dfc.descriptive_flexfield_name AND fcu.descriptive_flex_context_code = dfc.descriptive_flex_context_code WHERE flx.title LIKE '&TITLE' || '%' ORDER BY dfc.descriptive_flex_context_code, fcu.column_seq_num;

Key Flexfield Descriptions This script translates a concatenated KFF values string into the descriptions from the value sets. KFF structure is read from tables – not hard-coded. This is harder than it seems to do in a single SQL – the SQL uses nested inline views, with analytic functions, parses the value-string dynamically, and handles parent-child relationships. Input Parameters Name account TITLE

Description KFF value string Descriptive flexfield title

Output Fields Name # Segment Value Description

Description Segment number Segment name Segment value Segment description

Code SELECT /* BP Furey, 1 July 2009 */ ilv1.ind "#", ilv1.segment_name "Segment", ilv1.seg_value "Value", fvl.description "Description" FROM ( SELECT ilv0.ind, ilv0.segment_name, Substr ('&account', CASE ilv0.ind WHEN 1 THEN 1 ELSE Instr ('&account', '-', 1, ilv0.ind-1) + 1 END, CASE Instr ('&account', '-', 1, ilv0.ind) WHEN 0 THEN Length ('&account') ELSE Instr ('&account', '-', 1, ilv0.ind) - 1 END CASE ilv0.ind WHEN 1 THEN 0 ELSE Instr ('&account', '-', 1, ilv0.ind-1) END ) seg_value, Min ( CASE WHEN ilv0.parent_flex_value_set_id IS NULL THEN Substr ('&account', CASE ilv0.ind WHEN 1 THEN 1 ELSE Instr ('&account', '-', 1, ilv0.ind-1) + 1 END, CASE Instr ('&account', '-', 1, ilv0.ind) WHEN 0 THEN Length ('&account') ELSE Instr ('&account', '-', 1, ilv0.ind) - 1 END -

Technical Overview

22

CASE ilv0.ind WHEN 1 THEN 0 ELSE Instr ('&account', '-', 1, ilv0.ind-1) END

FROM ( SELECT

) END) OVER (PARTITION BY Nvl (ilv0.parent_flex_value_set_id, ilv0.flex_value_set_id)) seg_value_p, ilv0.flex_value_set_id, ilv0.parent_flex_value_set_id

seg.segment_name, seg.flex_value_set_id, fvs.parent_flex_value_set_id, Row_Number() OVER (ORDER BY seg.segment_num) ind FROM fnd_id_flexs flx JOIN fnd_id_flex_structures_vl str ON str.application_id = flx.application_id AND str.id_flex_code = flx.id_flex_code JOIN fnd_id_flex_segments_vl seg ON seg.application_id = str.application_id AND seg.id_flex_num = str.id_flex_num AND seg.id_flex_code = str.id_flex_code JOIN fnd_flex_value_sets fvs ON fvs.flex_value_set_id = seg.flex_value_set_id WHERE flx.description = '&TITLE' ) ilv0 ) ilv1 JOIN fnd_flex_values_vl fvl ON fvl.flex_value_set_id = ilv1.flex_value_set_id AND fvl.flex_value = ilv1.seg_value AND (fvl.parent_flex_value_low = ilv1.seg_value_p OR ilv1.parent_flex_value_set_id IS NULL) ORDER BY ilv1.ind;

Technical Overview

23

Data Dictionary: Schema Summaries The SQL statements here are normally run together for a schema and the results saved to a text file, which can then be referred to for table/column/index information. It is often quicker than opening the relevant nodes individually in a TOAD-style interface, and the outputs are ordered and organised for maximum convenience.

Tables This script lists tables in a schema with summary information on columns, indexes etc. Input Parameters Name tab nottab1 nottab2 owner

Description Table prefix Table string to exclude, 1 Table string to exclude, 2 Table owner

Output Fields Name TABLE_NAME Who? Attrs Indexes Cols

Description Table name Yes if Who columns included Number of excluded columns Number of indexes Number of non-excluded columns

Code SELECT /* BP Furey, 1 July 2009 */ atc.table_name, Nvl2 (atc_w.column_name, 'Yes', NULL) "Who?", CASE att.n_att WHEN 0 THEN To_Number(NULL) ELSE att.n_att END "Attrs", ind_tot.n_ind "Indexes", Count(atc.column_name) "Cols" FROM all_tab_columns atc JOIN all_tables atb ON atb.table_name = atc.table_name AND atb.owner = atc.owner -- Join index count LEFT JOIN (SELECT i.table_owner, i.table_name, Count (i.index_name) n_ind FROM all_indexes i GROUP BY i.table_owner, i.table_name) ind_tot ON ind_tot.table_name = atb.table_name AND ind_tot.table_owner = atb.owner -- Join attribute count LEFT JOIN (SELECT atc_att.table_name, atc_att.owner, Count(atc_att.column_name) n_att FROM all_tab_columns atc_att WHERE atc_att.column_name LIKE '%¬col%' GROUP BY atc_att.table_name, atc_att.owner) att ON att.table_name = atb.table_name AND att.owner = atb.owner LEFT JOIN all_tab_columns atc_w -- Join the Who column if it exists ON atc_w.table_name = atb.table_name AND atc_w.owner = atb.owner AND atc_w.column_name = 'CREATED_BY' -- Join index with its sequence for table WHERE atc.table_name LIKE Upper('&tab'||'%') AND atc.table_name NOT LIKE '%$%' AND atb.table_name NOT LIKE '%¬tab1%' AND atb.table_name NOT LIKE '%¬tab2%' AND atb.owner LIKE Upper('&owner') GROUP BY atc.table_name, Nvl2 (atc_w.column_name, 'Yes', NULL), CASE att.n_att WHEN 0 THEN To_Number(NULL) ELSE att.n_att END, ind_tot.n_ind ORDER BY 1;

Technical Overview

24

Tables and Columns This script lists tables and columns in a schema, ordered by table and column names (unlike the DESC command). Columns such as DFF attributes can be excluded from the list but counted at table level to minimise clutter. The SQL illustrates a number of useful techniques, including analytic functions, inline views, and row-column pivotting. Input Parameters Name tab nottab1 nottab2 owner notcol Output Fields Name TABLE_NAME Who? Attrs Indexes COLUMN_NAME DATA_TYPE Length Index Pos

Description Table prefix Table string to exclude, 1 Table string to exclude, 2 Table owner Column string to exclude (e.g ATTRIBUTE to exclude DFFs)

Description Table name Yes if Who columns included Number of excluded columns Number of indexes Column name Column data type Column length Sequence of column in index i where i denotes the character position within the output field

Code SELECT /* BP Furey, 1 July 2009 */ atc.table_name, Nvl2 (atc_w.column_name, 'Yes', NULL) "Who?", CASE att.n_att WHEN 0 THEN To_Number(NULL) ELSE att.n_att END "Attrs", ind_tot.n_ind "Inde xes", Lower (atc.column_name)|| CASE atc.nullable WHEN 'N' THEN '*' END column_name, atc.data_type, atc.data_length "Length", Max(CASE ind.rn WHEN 1THEN To_Char(aic.column_position) ELSE ' ' END) || Max(CASE ind.rn WHEN 2THEN To_Char(aic.column_position) ELSE ' ' END) || Max(CASE ind.rn WHEN 3THEN To_Char(aic.column_position) ELSE ' ' END) || Max(CASE ind.rn WHEN 4THEN To_Char(aic.column_position) ELSE ' ' END) || Max(CASE ind.rn WHEN 5THEN To_Char(aic.column_position) ELSE ' ' END) || Max(CASE ind.rn WHEN 6THEN To_Char(aic.column_position) ELSE ' ' END) || Max(CASE ind.rn WHEN 7THEN To_Char(aic.column_position) ELSE ' ' END) || Max(CASE ind.rn WHEN 8THEN To_Char(aic.column_position) ELSE ' ' END) || Max(CASE ind.rn WHEN 9THEN To_Char(aic.column_position) ELSE ' ' END) "Index Pos" FROM all_tab_columns atc JOIN all_tables atb ON atb.table_name = atc.table_name AND atb.owner = atc.owner -- Join index count LEFT JOIN (SELECT i.table_owner, i.table_name, Count (i.index_name) n_ind FROM all_indexes i GROUP BY i.table_owner, i.table_name) ind_tot ON ind_tot.table_name = atb.table_name AND ind_tot.table_owner = atb.owner -Join attribute count LEFT JOIN (SELECT atc_att.table_name, atc_att.owner, Count(atc_att.column_name) n_att FROM all_tab_columns atc_att WHERE atc_att.column_name LIKE '%¬col%' GROUP BY atc_att.table_name, atc_att.owner) att ON att.table_name = atb.table_name AND att.owner = atb.owner LEFT JOIN all_tab_columns atc_w -- Join the Who column if it exists ON atc_w.table_name = atb.table_name AND atc_w.owner = atb.owner

Technical Overview

25

AND atc_w.column_name = 'CREATED_BY' -- Join index with its sequence for table LEFT JOIN (SELECT Row_Number () OVER (PARTITION BY i.table_name, i.table_owner ORDER BY i.table_name, i.table_owner, i.index_name) rn, i.table_name, i.table_owner, i.owner, i.index_name FROM all_indexes i) ind ON ind.table_name = atb.table_name AND ind.table_owner = atb.owner LEFT JOIN all_ind_columns aic -- Join columns for the index ON aic.index_owner = ind.owner AND aic.index_name = ind.index_name AND aic.column_name = atc.column_name WHERE atc.column_name NOT LIKE '%¬col%' AND atc.table_name LIKE Upper('&tab'||'%') AND atc.table_name NOT LIKE '%$%' AND atb.table_name NOT LIKE '%¬tab1%' AND atb.table_name NOT LIKE '%¬tab2%' AND atb.owner LIKE Upper('&owner') AND atc.column_name NOT IN ('CREATED_BY', 'CREATION_DATE', 'LAST_UPDATED_BY', 'LAST_UPDATE_DATE') GROUP BY atc.table_name, Nvl2 (atc_w.column_name, 'Yes', NULL), Lower (atc.column_name)||CASE atc.nullable WHEN 'N' THEN '*' END, CASE att.n_att WHEN 0 THEN To_Number(NULL) ELSE att.n_att END, ind_tot.n_ind, atc.data_type, atc.data_length ORDER BY 1, 2;

Indexes This script lists indexes in a schema, ordered by table, index and column sequence. Input Parameters Name tab nottab1 nottab2 owner

Description Table prefix Table string to exclude, 1 Table string to exclude, 2 Table owner

Output Fields Name TABLE_NAME INDEX_NAME Seq COLUMN_NAME

Description Table name Index name Sequence of column in index Column name

Code SELECT /* BP Furey, 1 July 2009 */ atb.table_name, ind.index_name||CASE ind.uniqueness WHEN 'UNIQUE' THEN '*' END index_name, aic.column_position "Seq", Lower(aic.column_name) column_name FROM all_tables atb LEFT JOIN all_indexes ind ON ind.table_name = atb.table_name AND ind.table_owner = atb.owner LEFT JOIN all_ind_columns aic ON aic.index_name = ind.index_name AND aic.index_owner = ind.owner WHERE atb.table_name LIKE Upper('&tab'||'%') AND atb.table_name NOT LIKE '%$%' AND atb.table_name NOT LIKE '%¬tab1%' AND atb.table_name NOT LIKE '%¬tab2%' AND atb.owner LIKE Upper('&owner') ORDER BY 1, 2, 3;

Technical Overview

26

Tables with No Who Columns or No (or Id Only) Unique Indexes This script lists tables in a schema that have one of a number of characteristics that may indicate a design omission or problem, such as absence of Who columns.. Input Parameters Name tab nottab1 nottab2 owner

Description Table prefix Table string to exclude, 1 Table string to exclude, 2 Table owner

Output Fields Name TABLE_NAME Who? No UID? Id UID?

Description Table name X if Who columns included X if no unique index X if only unique index on id columns

Code SELECT /* BP Furey, 1 July 2009 */ atb.table_name, CASE WHEN atc_w.column_name IS NULL THEN 'X' END "Who?", CASE WHEN uni.maxind IS NULL THEN 'X' END "No UID?", CASE WHEN uni.maxind = 1 THEN 'X' END "Id UID?" FROM all_tables atb LEFT JOIN all_tab_columns atc_w ON atc_w.table_name = atb.table_name AND atc_w.owner = atb.owner AND atc_w.column_name = 'CREATED_BY' LEFT JOIN (SELECT ind.table_name, ind.table_owner, Max(CASE WHEN Substr(aic.column_name, Length(aic.column_name)-2) IS NULL THEN 0 WHEN Substr(aic.column_name, Length(aic.column_name)-2) = '_ID' THEN 1 ELSE 2 END) maxind FROM all_indexes ind LEFT JOIN all_ind_columns aic ON aic.index_name = ind.index_name AND aic.index_owner = ind.owner AND aic.column_position = 1 WHERE ind.uniqueness = 'UNIQUE' GROUP BY ind.table_name, ind.table_owner) uni ON uni.table_name = atb.table_name AND uni.table_owner = atb.owner WHERE atb.table_name LIKE Upper('&tab'||'%') AND atb.table_name NOT LIKE '%$%' AND atb.table_name NOT LIKE '%¬tab1%' AND atb.table_name NOT LIKE '%¬tab2%' AND atb.owner LIKE Upper('&owner') AND (atc_w.column_name IS NULL OR uni.maxind IN (0, 1)) ORDER BY 1;

Technical Overview

27

HR: Organizations The SQL statements here are normally run together for a schema and the results saved to a text file, which can then be referred to for table/column/index information. It is often quicker than opening the relevant nodes individually in a TOAD-style interface, and the outputs are ordered and organised for maximum convenience.

Inventory Organizations This script lists inventory organizations. Output Fields Name Inv Org Owner Master

Description Organization name, with id and warehouse code appended Organization name of owning operating unit with id appended Organization name of master inventory organization with id appended

Code SELECT u.name || ': '|| u.organization_id || ' (' || p.organization_code || ')' "Inv Org", uh.name || ': '|| h.org_information3 "Owner", um.name || ': '|| um.organization_id "Master" FROM hr_all_organization_units u JOIN mtl_parameters p ON p.organization_id = u.organization_id JOIN hr_all_organization_units um ON um.organization_id = p.master_organization_id JOIN hr_organization_information h ON h.organization_id = u.organization_id AND h.org_information_context = 'Accounting Information' JOIN hr_all_organization_units uh ON uh.organization_id = h.org_information3 ORDER BY 1;

Organizations by Class This script lists organizations by class. Output Fields Name Class Org Id Code LE Address

Description Organization class Organization name Organization id Organization code Organization name of owning legal entity Organization address

Code SELECT h.org_information1 "Class", u.name "Org", u.organization_id u_le.name "LE", CASE WHEN loc.address_line_1 IS NULL THEN NULL ELSE loc.address_line_1 ||',' || loc.address_line_2 ||',' || loc.address_line_3 ||',' || loc.town_or_city ||',' || loc.postal_code ||',' || loc.country ||' TEL: ' || loc.telephone_number_1 ||' FAX: ' || loc.telephone_number_2 END "Address" FROM hr_all_organization_units u

"Id", h_ou.attribute1 "Code",

Technical Overview

28

LEFT JOIN hr_organization_information ON h.organization_id AND h.org_information_context LEFT JOIN hr_organization_information ON h_ou.organization_id AND h_ou.org_information_context LEFT JOIN hr_all_organization_units ON u_le.organization_id LEFT JOIN hr_locations_all ON loc.location_id ORDER BY 1, 2;

h = u.organization_id = 'CLASS' h_ou = u.organization_id = 'Operating Unit Information' u_le = To_Number(h_ou.org_information2) loc = u.location_id

Technical Overview

29

References REF

Document

REF-1

Oracle, eTRM, R11.5.10

Location https://etrm.oracle.com/pls/trm11510/etr m_search.search

Technical Overview

30

Appendix: Example Outputs Time and Instance Database Session OS User Machine Time Version -------------------- -------- ---------- -------------------- -------------------- -----------------------------Start: MINDD2 1868 bfurey XXXX\XIR-ITCON-D7 30-JUN-2009 09:03:38 Oracle Database 10g 10.2.0.3.0

Concurrent Requests by Program Code Id CP User Sta Request Time Start Time Duration Responsibility Parameters --------- ------------- --------------- --- ------------------ ------------------ -------- ---------------- ----------------------40778068 Invoice Print AUSER C/C 22-jun-09 12:37:24 22-jun-09 12:37:25 0.2 Receivables User 1 : BATCH 2 : 2008/08/02 00:00:00 5 : 376487 ...

Concurrent Requests Hierarchy Id Parent CP ------------------------- --------- ------------------------------40886460 EDI Invoice Process Set 40886462 40886460 Process Subscription Message 40886463 40886462 EDI Invoice Process 40886475 40886460 Generate Error Reports 40886476 40886475 EDI Invoice Error Reports ...

Type ------Set Stage Program Stage Program

User Sta Start Time Duration Responsibility --------------- --- ------------------ -------- ----------------AUSER C/C 26-jun-09 10:26:57 1.3 Payables User AUSER C/C 26-jun-09 10:26:58 1.2 Payables User AUSER C/C 26-jun-09 10:27:37 0.1 Payables User AUSER C/C 26-jun-09 10:28:08 0.1 Payables User AUSER C/C 26-jun-09 10:28:09 0.1 Payables User

Programs and Parameters App Program Executable Seq Parameter ------ ------------------------------ ------------ ---- ------------------------XXAR XXARINVPRI XXARINVPRI 1 Print Options 10 Cutoff Date 70 Invoice Number Low 75 Invoice Number High 100 Batch Name 110 Customer Trx Id 120 Date Low 130 Date High 140 Customer Name 150 Debug Flag

Default -------------------BATCH 2008/08/02 00:00:00

Value Set ------------------------XXAR_PRINT_CHOICE AR_SRS_DATE_LOW_STANDARD XXAR_TRX_NO XXAR_TRX_NO XXAR_RAXINV_BATCH XXAR_TRX_ID AR_SRS_DATE_LOW_STANDARD AR_SRS_DATE_HIGH_STANDARD AR_CUSTOMER_NAME_ID 50 chars

Programs and Request Groups App Program User Program #Pars Request Group Responsibility ------ -------------------- ----------------------- ----- ---------------- -------------------XXAR XXARINVPRI Xilinx Invoice Print 10 Receivables All Receivables Manager Receivables User ...

Request Sets Seq S Stage Seq P Program Seq A ----- ------------------------------ ----- ----------------------------------- ----10 XXONT Pick Release (10) 10 XXONT Pick Release: XXONTPR 10 20 30 40 50 60 70

Parameter ------------------------WareHouse No of Days(CFD) Order Reference Item Customer name Ship Via Ship Priority (Rev / Drop Ship)

Default -------X01 1

Value Set RS Def ------------------------------ -----INV_SRS_INCIPS_ORG X30 10/Number XXONT_PICK_RELEASE_LINEID INV_SRS_ITEM AR_CUSTOMER_NAME_ID OM: Shipping Method XXONT Shipment Priority Code

Technical Overview

31

20 XXWMS TASK PRIORITY PKG (20)

10 XXWMS TASK PRIORITY PKG: XXWMSTPP

80 No of Days(ESD) 10 ORGANIZATION_ID

2

10/Number XXONTTPP_ORG_VSET

2745

Responsibilities SOB OU App HUT ------------ --- ------ -------ASOB, Def DEF ONT DEF DEF XAP ONT DEF DEF DEF DEF ...

Name --------------------------------XSJ Customer Service Discoverer XSJ OM Discoverer XAP Order Entry Standard User XAP OE Inquiry/Reports User XAP Marketing User XAP OM IT Support

Key -------------------------XLX_CUST_SERV_DISCO XLX_OM_DISCO XXOM_XAP_OE_STD_USER XXOM_XAP_OE_INQ_RPTS_USER XXOM_XAP_MARKETING_USER XXOM_XAP_IT_SUPPORT

Updated -----------------23-May-09 05:13:08 23-May-09 05:27:01 23-May-09 00:00:00 23-May-09 00:00:00 23-May-09 00:00:00 23-May-09 00:00:00

By -------------------------------AUSER (A User) AUSER (A User) SYSADMIN (System Administrator) SYSADMIN (System Administrator) SYSADMIN (System Administrator) SYSADMIN (System Administrator)

Menus Start: MINDD2 1868 bfurey XXXX\XIR-ITCON-D7 **** * SOB: A SOB **** * OU: XIR **** * App: AR **** Resp: XIR Receivables User/XXAR_XIR_RECEIVABLES_USER, Menu: **** . Transactions . Batches . Batches Summary . Transactions . Transactions Summary . Credit Transactions . Copy Transactions . Document Transfer Summary . Receipts . Batches . Batches Summary . Receipts . Receipts Summary . Remittances . Remittances Summary . Clear/Risk Eliminate . Lockbox . Transmission History . Maintain Transmission Data . Alternate Name Matches . Schedule Automatic Receipts and Remittances . Alternate Name Matches . Collections . Customer Calls . Customer Accounts . Account Overview . Aging . Correspondence . Account Details . Transaction Overview . Scheduler . iReceivables . Customers . Standard

30-JUN-2009 09:18:45 Oracle Database 10g 10.2.0.3.0

XXAR_RECEIVABLES_USER, OU: XIR, HR: User Type: (null) Menu:AR_TRANSACTIONS_GUI/ Menu:AR_ARXTWMAI_BATCH/ARXTWMAI Menu:AR_ARXTWMAI_BATCH_SUMMARY/ARXTWMAI Menu:AR_ARXTWMAI_HEADER/ARXTWMAI Menu:AR_ARXTWMAI_SUMMARY/ARXTWMAI Menu:AR_ARXTWMAI_CREDIT_MEMO/ARXTWMAI Menu:AR_ARXTWMAI_COPY_FORM/ARXTWMAI JSP:AR_DOC_TRS_SUMMARY_PG/ Menu:AR_RECEIPTS_GUI/ Menu:AR_ARXRWMAI_BATCH/ARXRWMAI Menu:AR_ARXRWMAI_BATCH_SUMMARY/ARXRWMAI Menu:AR_ARXRWMAI_HEADER/ARXRWMAI Menu:AR_ARXRWMAI_SUMMARY/ARXRWMAI Menu:AR_ARXRWMAI_REMITTANCES/ARXRWMAI Menu:AR_ARXRWMAI_REMIT_SUMMARY/ARXRWMAI FORM:AR_FNDRSRUN_AUTO_CLEAR/FNDRSRUN Menu:AR_RECEIPTS_LBOX_GUI/ FORM:AR_ARXLBHIS/ARXLBHIS FORM:AR_ARXLPIM/ARXLPIM FORM:AR_ARXCUANM/ARXCUANM FORM:AR_RCPT_PROC/FNDRSRUN FORM:AR_ARXCUANM/ARXCUANM Menu:AR_COLLECTIONS_GUI/ Menu:AR_ARXCWMAI_ECC/ARXCWMAI Menu:AR_ARXCWMAI_MAI/ARXCWMAI Menu:AR_ARXCWMAI_SUM/ARXCWMAI Menu:AR_ARXCWMAI_AGE/ARXCWMAI Menu:AR_ARXCWMAI_COR/ARXCWMAI Menu:AR_ARXCWMAI_QIT/ARXCWMAI Menu:AR_ARXCWMAI_QIL/ARXCWMAI Menu:AR_ARXCWMAI_SCH/ARXCWMAI JSP:ARI_INTERNAL_PAGE/ Menu:AR_CUSTOMERS_VIEW/ Menu:AR_ARXCUDCI_STD_VIEW/ARXCUDCI Technical Overview

32

. Quick Menu:AR_ARXCUDCI_QUICK_VIEW/ARXCUDCI . Summary Menu:AR_ARXCUDCI_GATEWAY_VIEW/ARXCUDCI . Profile Classes FORM:AR_ARXMACPC_VIEW/ARXMACPC . Reports Menu:AR_REPORTS_GUI/ . Accounting FORM:AR_FNDRSRUN_ACC_RPTS/FNDRSRUN . Collections FORM:AR_FNDRSRUN_COLLECTION_RPTS/FNDRSRUN . Listing FORM:AR_FNDRSRUN_LISTING_RPTS/FNDRSRUN . Tax FORM:AR_FNDRSRUN_TAX_RPTS/FNDRSRUN . Other FORM:AR_FNDRSRUN_OTHER_RPTS/FNDRSRUN . Print Documents Menu:AR_PRINT_GUI/ . Invoices FORM:AR_FNDRSRUN_INVOICES_PRINT/FNDRSRUN . Dunning Letters FORM:AR_FNDRSRUN_DUNNING_PRINT/FNDRSRUN . Statements FORM:AR_ARXSUPST/ARXSUPST . Consolidated Billing Invoices FORM:AR_FNDSRUN_CONSINV/FNDRSRUN . Document Transfer Summary JSP:AR_DOC_TRS_SUMMARY_PG/ . Interfaces Menu:AR_INTERFACE_GUI/ . AutoInvoice FORM:AR_FNDRSRUN_AUTOINVOICE/FNDRSRUN . Customer FORM:AR_FNDRSRUN_CUST_INTERFACE/FNDRSRUN . Tax Rate FORM:AR_FNDRSRUN_TAX_INTERFACE/FNDRSRUN . Lockbox FORM:AR_ARXLBSUB/ARXLBSUB . General Ledger FORM:AR_FNDRSRUN_ARGLTP/FNDRSRUN . Control Menu:AR_CONTROL_GUI/ . Requests Menu:FND_REPORT4.0/ . Run FORM:FND_FNDRSRUN/FNDRSRUN . Set FORM:FND_FNDRSSET_USER/FNDRSSET . Profile Options FORM:FND_FNDPOMSV/FNDPOMSV . Concurrent FORM:FND_FNDCPQCR_USER/FNDRSRUN . Accounting Menu:AR_CONTROL_ACC_GUI/ . Revenue Recognition FORM:AR_FNDRSRUN_REVREC/FNDRSRUN . Open/Close Periods FORM:AR_ARXSUMPS/ARXSUMPS . Revenue Accounting FORM:AR_ARXRAATR/ARXRAATR . Correct Invalid GL Accounts FORM:AR_ARXGLCOR_CORRECT/ARXGLCOR . Adjustments Menu:AR_CONTROL_ADJ_GUI/ . Create AutoAdjustments FORM:AR_ARXSUBAA/ARXSUBAA . AutoInvoice Menu:AR_AUTOINVOICE_ERRORS_GUI/ . Interface Lines FORM:AR_ARXAIEXP_LINES/ARXAIEXP . Interface Exceptions FORM:AR_ARXAIEXP_ERRORS/ARXAIEXP . Receipts Menu:AR_CONTROL_RCPT_GUI/ . Automatic Receipts FORM:AR_RCPT_PROC/FNDRSRUN ---Menu: XIR Receivables User has 73 items including 0 customized forms ---Total for 1 responsibilities: 73 items including 0 customized forms End: MINDD2 30-JUN-2009 09:18:53

Key Flexfields CONTEXT TITLE USERCOL ------------------------------ ------------------------------ -----------------------------ACCOUNTING_FLEXFIELD Accounting Flexfield Company Product Line Department Account Subaccount Project

COLNAME --------------SEGMENT1 SEGMENT2 SEGMENT3 SEGMENT4 SEGMENT5 SEGMENT6

VALUE_SET -----------------------------XXGL_Company XXGL_Product_Line XXGL_Department XXGL_Account XXGL_Subaccount XXGL_Project

Descriptive Flexfields CONTEXT COLNAME USERCOL FORM_LEFT_PROMPT ------------------------------ ------------------------------ ------------------------------ ---------------------------------------CLAIM INTERFACE_LINE_ATTRIBUTE1 Claim Number Claim Number Technical Overview

33

INTERFACE_LINE_ATTRIBUTE2 INTERFACE_LINE_ATTRIBUTE3 INTERFACE_LINE_ATTRIBUTE4 INTERFACE_LINE_ATTRIBUTE5 INTERFACE_LINE_ATTRIBUTE6 INTERFACE_LINE_ATTRIBUTE7 INTERFACE_LINE_ATTRIBUTE1 INTERFACE_LINE_ATTRIBUTE2 INTERFACE_LINE_ATTRIBUTE3 INTERFACE_LINE_ATTRIBUTE4 INTERFACE_LINE_ATTRIBUTE5 INTERFACE_LINE_ATTRIBUTE6 INTERFACE_LINE_ATTRIBUTE7 INTERFACE_LINE_ATTRIBUTE8 INTERFACE_LINE_ATTRIBUTE9 INTERFACE_LINE_ATTRIBUTE10

CONTRACTS

...

Claim Id Claim Line Id Earnings Attached Flag Customer Reference Customer Reason Claim Reason Contract Id Service Id Contract Number Billing Id Contract Type Id Contract Group Id Transaction Start Date Transaction End Date Transaction Amount Service Transaction ID

Claim Id Claim Line Id Earnings Attached Flag Customer Reference Customer Reason Claim Reason Contract Id Service Id Contract Number Billing Id Contract Type Id Contract Group Id Transaction Start Date Transaction End Date Transaction Amount Service Transaction ID

Key Flexfield Descriptions General Ledger Accounting Flexfield Values # --1 2 3 4 5 6

Segment --------------Company Product Line Department Account Subaccount Project

Value ---------01 00 2100 6930 00 0000

Description -----------------------------Xilinx, USA None Marcom Mgmt Internal Consumption Channel Marketing None

Tables Database Session OS User Machine Time Version -------------------- -------- ---------- -------------------- -------------------- -----------------------------Start: ERPCOP 2047 bfurey XXXX\XIR-ITCON-D7 01-JUL-2009 04:01:46 Oracle Database 10g 10.2.0.3.0 AR "Schema", RA "Prefix" Tables Summary (excluding like 'RISKXX', 'INCIDXXX', '$' ) TABLE_NAME -----------------------------RA_ACCOUNT_COMBINATIONS RA_ACCOUNT_DEFAULTS_ALL RA_ACCOUNT_DEFAULT_SEGMENTS RA_ADDRESSES_ALL RA_BATCHES_ALL RA_BATCH_SOURCES_ALL RA_CM_REQUESTS_ALL RA_CM_REQUEST_LINES_ALL RA_CONTACTS RA_CONTACT_PHONES_INT_ALL RA_CONTACT_ROLES RA_CUSTOMERS RA_CUSTOMERS_INTERFACE_ALL RA_CUSTOMER_BANKS_INT_ALL RA_CUSTOMER_MERGES RA_CUSTOMER_MERGE_HEADERS RA_CUSTOMER_PROFILES_INT_ALL RA_CUSTOMER_RELATIONSHIPS_ALL

Who? Attrs Indexes Cols ----- ----- ------- ---Yes 16 2 25 Yes 16 1 24 Yes 16 2 27 Yes 37 9 97 Yes 16 5 47 Yes 37 2 94 Yes 1 24 Yes 1 11 Yes 26 9 69 Yes 42 4 71 Yes 16 2 30 Yes 37 7 117 Yes 129 6 203 Yes 67 6 107 Yes 16 6 59 Yes 16 2 45 Yes 73 2 129 Yes 16 3 32 Technical Overview

34

RA_CUSTOMER_TRX_ALL RA_CUSTOMER_TRX_LINES_ALL RA_CUST_PAY_METHOD_INT_ALL RA_CUST_RECEIPT_METHODS RA_CUST_TRX_LINE_GL_DIST_ALL RA_CUST_TRX_LINE_SALESREPS_ALL RA_CUST_TRX_TYPES_ALL RA_GROUPING_RULES RA_GROUPING_TRX_TYPES RA_GROUP_BYS RA_GROUP_BY_COLUMNS RA_INTERFACE_DISTRIBUTIONS_ALL RA_INTERFACE_ERRORS_ALL RA_INTERFACE_LINES_ALL RA_INTERFACE_SALESCREDITS_ALL RA_ITEM_EXCEPTION_RATES RA_LINE_ORDERING_RULES RA_LINE_ORDER_BYS RA_LINE_ORDER_BY_COLUMNS RA_MC_BATCHES RA_MC_CUSTOMER_TRX RA_MC_CUSTOMER_TRX_LINES RA_MC_TRX_LINE_GL_DIST RA_PHONES RA_RECUR_INTERIM RA_REMIT_TOS_ALL RA_RULES RA_RULE_SCHEDULES RA_SALESREPS_ALL_OLD RA_SALESREP_TERRITORIES RA_SITE_USES_ALL RA_TAX_EXEMPTIONS_ALL RA_TERMS_B RA_TERMS_LINES RA_TERMS_LINES_DISCOUNTS RA_TERMS_TL RA_TERRITORIES

Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes

62 52 16 16 16 16 37 16 16 16

Yes Yes Yes Yes Yes Yes

127 31 16 16 16

31

Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes

16

21 16 2 2 8 6 1 2 1 1 1 4 2 9 2 2 2 1 1 1 1 1 3 6

16 16 16 16 16 47 16 16 16 16 16

1 1 1 5 1 3 5 1 1 1 2 1

173 133 31 32 59 40 79 27 24 23 10 109 8 305 49 48 26 25 8 5 5 8 10 36 14 28 29 25 46 30 114 60 33 28 29 10 49

55 rows selected.

Tables and Columns Tables and Columns (omitting Who and %ATTRIBUTE%) TABLE_NAME Who? Attrs Indexes COLUMN_NAME ------------------------------ ----- ----- ------- -----------------------------RA_ACCOUNT_COMBINATIONS Yes 16 2 account_combination_id* code_combination_id* last_update_login value1 value2 RA_ACCOUNT_DEFAULTS_ALL Yes 16 1 gl_default_id* last_update_login org_id type* ...

DATA_TYPE Length Index Pos --------- ------ ---------NUMBER 22 1 NUMBER 22 NUMBER 22 VARCHAR2 30 1 VARCHAR2 30 2 NUMBER 22 1 NUMBER 22 NUMBER 22 VARCHAR2 20

Indexes Indexes Technical Overview

35

TABLE_NAME INDEX_NAME Seq COLUMN_NAME ------------------------------ ------------------------------- ---- -----------------------------RA_ACCOUNT_COMBINATIONS RA_ACCOUNT_COMBINATIONS_U1* 1 account_combination_id RA_ACCOUNT_COMBINATIONS_U2* 1 value1 2 value2 RA_ACCOUNT_DEFAULTS_ALL RA_ACCOUNT_DEFAULTS_U1* 1 gl_default_id RA_ACCOUNT_DEFAULT_SEGMENTS RA_ACCOUNT_DEFAULT_SEGMENTS_N1 1 gl_default_id RA_ACCOUNT_DEFAULT_SEGMENTS_U1* 1 gl_default_segment_id ...

Tables with No Who Columns or No (or Id Only) Unique Indexes Tables with no Who columns / No unique indexes/ Id only unique indexes TABLE_NAME -----------------------------RA_ACCOUNT_DEFAULTS_ALL RA_ACCOUNT_DEFAULT_SEGMENTS RA_BATCHES_ALL RA_CM_REQUESTS_ALL RA_CM_REQUEST_LINES_ALL RA_CONTACT_ROLES RA_CUSTOMER_MERGES RA_CUSTOMER_MERGE_HEADERS RA_CUSTOMER_TRX_ALL RA_CUSTOMER_TRX_LINES_ALL RA_CUST_RECEIPT_METHODS RA_CUST_TRX_LINE_GL_DIST_ALL RA_CUST_TRX_LINE_SALESREPS_ALL RA_CUST_TRX_TYPES_ALL RA_GROUPING_TRX_TYPES RA_GROUP_BYS RA_GROUP_BY_COLUMNS RA_INTERFACE_DISTRIBUTIONS_ALL RA_INTERFACE_ERRORS_ALL RA_INTERFACE_LINES_ALL RA_INTERFACE_SALESCREDITS_ALL RA_ITEM_EXCEPTION_RATES RA_LINE_ORDER_BYS RA_LINE_ORDER_BY_COLUMNS RA_MC_BATCHES RA_MC_CUSTOMER_TRX RA_MC_CUSTOMER_TRX_LINES RA_MC_TRX_LINE_GL_DIST RA_RULES RA_RULE_SCHEDULES RA_SALESREPS_ALL_OLD RA_SALESREP_TERRITORIES RA_SITE_USES_ALL RA_TAX_EXEMPTIONS_ALL RA_TERMS_B RA_TERMS_LINES RA_TERMS_LINES_DISCOUNTS RA_TERMS_TL RA_TERRITORIES

Who? No UID? Id UID? ----- ------- ------X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X

39 rows selected.

Inventory Organizations Inv Org

Owner

Master Technical Overview

36

(Headins only for privacy) ------------------------------------------------------- --------------------------------------------- ---------------------------------------------

Organizations by Class Class -----------------------------BOM_ECOD FA_ORG HR_LEGAL HR_ORG INV MRP OPERATING_UNIT OTA_TC WIP

Org Id Code LE Address -------------------------------------------------- ----- ---- -------------------- -----------------------------------(Fields removed for privacy) ... ... ... ... ... ... ... ...

Technical Overview

37