EBS 常用PL/SQL代码

1. 检查某个模块的安装状态 (S代表共享安装 I代表完全安装 N代表没有安装）
   select fat.application_name,fa.application_short_name,fpi.status
   from fnd_application_tl fat,fnd_product_installations fpi,fnd_application fa
   where fat.language='US' and fat.application_id=fpi.application_id
   and fat.application_id=fa.application_id
   order by fpi.status
2. OAF

          DECLARE
          BEGIN
                jdr_utils.listdocuments('/oracle/apps/fnd', TRUE);
          END;

 

    3.　序列号

 

-- Create sequence
create sequence CUX.CUS_WIP_LLT_S
minvalue 1
maxvalue 999999999999999999999999999
start with 281
increment by 1
cache 10;

select CUS_WIP_LLT_S.nextval from dual;

select CUS_WIP_LLT_S.CURRVAL from dual

4. 产生明细行行号

       SELECT HEADERID,
             RANK() OVER(PARTITION BY HEADERID ORDER BY LineID) LINENUMBER
       FROM TABLEA

 

5.功能、菜單查詢

select fff.FUNCTION_NAME, fff.USER_FUNCTION_NAME,
       fff.DESCRIPTION,fff.TYPE,WEB_HTML_CALL
from FND_FORM_FUNCTIONS_VL fff
where FUNCTION_NAME like 'CUS%'

 

select fmv.MENU_NAME,fmv.USER_MENU_NAME,fmv.TYPE
from FND_MENUS_VL  fmv
where menu_name like 'CUS%'

select fme.PROMPT,fmv1.MENU_NAME,fff.FUNCTION_NAME
from FND_MENU_ENTRIES_VL  fme,
     FND_MENUS_VL  fmv,
     FND_MENUS_VL  fmv1,    
     FND_FORM_FUNCTIONS_VL fff
where fme.MENU_ID=fmv.MENU_ID
and fme.FUNCTION_ID=fff.FUNCTION_ID(+)  
and fme.SUB_MENU_ID=fmv1.MENU_ID(+)
and fmv.MENU_NAME like 'CUS%'

 

Tom's Handy SQL for the Oracle Applications [ID 731190.1]
		修改时间 21-APR-2010     类型 HOWTO     状态 MODERATED

In this Document
  Goal
  Solution
     1. Database
     1.1 General Objects / Tables / Columns
     1.2 Invalids / Compiling
     1 .3 Bugs and Patching
     1.4 Other Objects
     1.5 Helpful SQL Syntax
     2. Foundation (FND/ATG/AOL)
     2.1 SQL related to Oracle Application Messages
     2.2. Review Oracle Application Programs
     2.3. Users and Responsibilities
     2.4 Set Context / Initialize
     3. Manufacturing
     3.1 Manufacturing General
     3.2. Setup
     3.2.1. Inventory
     3.3. Transactions
     3.3.1. Inventory Transactions
  References

---

Platforms: 1-914CU;

This document is being delivered to you via Oracle Support's Rapid Visibility (RaV) process and therefore has not been subject to an independent technical review.

 

Applies to:

Oracle Inventory Management - Version: 10.7 to 12.0.0 - Release: 10.7 to 12
Information in this document applies to any platform.

Goal

@ To Support:
@

Do you have suggestions on some helpful SQL scripts when working with the Oracle Applications?

Solution

Yes, there are a number of helpful SQL scripts that we will share below. The scripts are segmented by foundation technology scripts (FND/ATG/AOL), then other product areas like manufacturing.  Many of the scripts prompt for information.  None of the scripts do any updates. Note that occassionally table names or columns change overtime. Where possible, the scripts will note if they will work only on a specific release. Otherwise, they should work across 10.7 to R12 though the scripts will be focused on the latest code.

1. Database

1.1 General Objects / Tables / Columns

PROMPT Find Object by Type, Name, Status
select object_name, object_type, owner, status 
  from dba_objects 
 where upper(object_name) like upper('%&object_name%')
   and object_type like upper('%'|| NVL('&object_type', 'TABLE')|| '%')
   and status like upper('%'|| NVL('&Status', '%')|| '%')
order by object_name, object_type;

PROMPT Find table indexes, keys, columns
select index_name,column_position,column_name
from all_ind_columns 
where table_name = '&EnterTableName' 
order by index_name, column_position;

PROMPT Find tables and columns that include a table and/or column name specified.
select table_name, column_name
from  dba_tab_columns
where column_name like NVL(UPPER('&COLUMN_NAME'), column_name)
  and table_name like NVL(UPPER('&TABLE_NAME'), table_name);



 

1.2 Invalids / Compiling

PROMPT Find Invalids
select object_name, object_type, owner, status 
  from dba_objects 
 where status = 'INVALID';

PROMPT Compile Packages
select 'alter package '|| object_name || ' compile '|| decode(object_type, 'PACKAGE', '', 'PACKAGE BODY', 'body')|| ';'
  from dba_objects
 where status = 'INVALID'
and object_type like 'PACK%';



 

1 .3 Bugs and Patching

PROMPT Find specific package version
select text from dba_source
where
   name = upper( '&PackageName')
    and line < 3;

PROMPT Find bugs already installed fixed in your system
select bug_number
  from ad_bugs 
  where bug_number  ='&Bug_Number';

PROMPT Find patches applied
select substr(patch_name,1,12) patch_num 
  from ad_applied_patches
 where patch_name like '%&Patch_Number%';


 

NOTE: A specific bug maybe fixed by multiple patches so it might be good to look for the bug number, instead of the patch number to see if that bug is fixed already on your system. Another way is to look at the file version mentioned in the patch and check if you have that version or higher.

 

1.4 Other Objects

PROMPT Query view details of a specific view.
SELECT VIEW_NAME, TEXT
  FROM DBA_VIEWS 
WHERE VIEW_NAME = UPPER('&VIEW_NAME')
  and owner = 'APPS';

PROMPT Trigger details on a table.
select TABLE_NAME, COLUMN_NAME, trigger_name, trigger_type, TRIGGER_BODY
from dba_TRIGGERS 
WHERE TABLE_NAME like '%&EnterTableName%';



 

1.5 Helpful SQL Syntax

1.5.1 Rowid:
Working with Rowids: rowidtochar(rowid) and chartorowid('AAABUeAAQAAACsjAAg')

1.5.2 Date Columns
a. Converting dates to show date with time: to_char(<date> 'DD-MON-YYYY HH24:MI:SS')
For example: select to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') from dual;
b. Comparing date columns -- Sometimes handy to skip the time portion when searching for dates. You can use the trunc command to strip the time off. This resets the time to midnight.
For example: select to_char(trunc(sysdate), 'DD-MON-YYYY HH24:MI:SS') from dual;

2. Foundation (FND/ATG/AOL)

2.1 SQL related to Oracle Application Messages


PROMPT ATG
PROMPT Find Messages by Message Text
select m.message_name, m.message_text, m.message_number, a.application_short_name
from fnd_new_messages m, fnd_application a
where upper(m.message_text) like upper('%&EnterMessageText%')
and m.language_code = 'US'
and m.application_id = a.application_id;

PROMPT Find Messages by Message Short Name
select m.message_name, m.message_text, m.message_number, a.application_short_name
from fnd_new_messages m, fnd_application a
where m.message_name like '%&EnterMessageName%'
and m.language_code = 'US'
and m.application_id = a.application_id;



 

2.2. Review Oracle Application Programs

PROMPT IDENTIFY CONCURRENT REQUEST FILE
PROMPT From Bug.3211206
PROMPT Use the following query to identify the correct trace file:
PROMPT where "request" is the concurrent request id for the inventory transaction 
PROMPT worker. 
SELECT 'Request id: '||request_id ,
'Trace id: '||oracle_Process_id,
'Trace Flag: '||req.enable_trace,
'Trace Name: 
'||dest.value||'/'||lower(dbnm.value)||'_ora_'||oracle_process_id||'.trc',
'Prog. Name: '||prog.user_concurrent_program_name,
'File Name: '||execname.execution_file_name|| execname.subroutine_name ,
'Status : '||decode(phase_code,'R','Running') 
||'-'||decode(status_code,'R','Normal'),
'SID Serial: '||ses.sid||','|| ses.serial#,
'Module : '||ses.module
from fnd_concurrent_requests req, v$session ses, v$process proc,
v$parameter dest, v$parameter dbnm, fnd_concurrent_programs_vl prog, 
fnd_executables execname
where req.request_id = &request
and req.oracle_process_id=proc.spid(+)
and proc.addr = ses.paddr(+)
and dest.name='user_dump_dest'
and dbnm.name='db_name'
and req.concurrent_program_id = prog.concurrent_program_id
and req.program_application_id = prog.application_id
and prog.application_id = execname.application_id
and prog.executable_id=execname.executable_id;


PROMPT Programs and Managers
PROMPT Provide various output of concurrent managers related to a specific program.
PROMPT In this case using default of Item Supply/Demand program.
SELECT 
fcq.processor_application_id, fcp.concurrent_program_name, 
fr.responsibility_id, fr.responsibility_key, fr.data_group_id, fr.request_group_id, 
fr.application_id, fa.application_short_name, 
fcq.concurrent_queue_id, fcq.CONCURRENT_QUEUE_NAME, 
fcq.MIN_PROCESSES, fcq.TARGET_PROCESSES, fcq.TARGET_NODE, fcq.SLEEP_SECONDS, fcq.CONTROL_CODE, fcq.DIAGNOSTIC_LEVEL, 
fcpr.*
FROM fnd_application fa,
  fnd_concurrent_programs fcp,
  fnd_conc_processor_programs fcpp,
  fnd_responsibility fr,
  fnd_concurrent_queues fcq,
  fnd_concurrent_processes fcpr
WHERE fcq.processor_application_id = fcpp.processor_application_id
 AND fcq.concurrent_processor_id = fcpp.concurrent_processor_id
 AND fcpp.concurrent_program_id = fcp.concurrent_program_id
 AND fcpp.program_application_id = fcp.application_id
 AND fcp.application_id = fa.application_id
 AND fcp.concurrent_program_name = NVL('&EnterProgramShortName', 'INXDSD')
 AND fr.application_id = 401
 AND fr.data_group_id = fcq.data_group_id
 AND fcq.manager_type = '3'
 AND fcpr.concurrent_queue_id = fcq.concurrent_queue_id
 AND fcpr.queue_application_id = fcq.application_id
 -- AND fcpr.process_status_code = 'A'
 AND fcpr.instance_number = userenv('instance')
ORDER BY dbms_random.random;



 

2.3. Users and Responsibilities

PROMPT Basic check for user details
select user_id, user_name, employee_id
from fnd_user
where user_name like '&EnterUserName%';

PROMPT Active Users
select fnd.user_name, icx.responsibility_application_id, icx.responsibility_id, frt.responsibility_name,
icx.session_id, icx.first_connect,
icx.last_connect,
DECODE ((icx.disabled_flag),'N', 'ACTIVE', 'Y', 'INACTIVE') status
from
fnd_user fnd, icx_sessions icx, fnd_responsibility_tl frt
where
fnd.user_id = icx.user_id
and icx.responsibility_id = frt.responsibility_id
and icx.disabled_flag <> 'Y'
and trunc(icx.last_connect) = trunc(sysdate)
order by icx.last_connect;

PROMPT 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 upper('%' || NVL('&EnterRespName', 'INV')|| '%')
and upper(res.RESPONSIBILITY_NAME) NOT like '%AX%'
and upper(res.RESPONSIBILITY_NAME) NOT like '%OPM%'
and grp.responsibility_id = res.responsibility_id
and grp.user_id = usr.user_id;



 

2.4 Set Context / Initialize

Sometimes you want to run queries that reference profile options. To do this, you should first initialize the context of the current session to the user, responsibility and application being used. You can also set the context of the current inventory organization for queries that reference the organization context.

PROMPT Initialize context of profiles, etc.
PROMPT Note you can query the user id, responsibility id,
PROMPT and application id from the FND tables.
execute fnd_global.APPS_INITIALIZE(&UserID, &ResponsibilityID, &ApplicationID);

PROMPT Set organization
PROMPT Note you can query the organization idea from mtl_parameters.
execute fnd_client_info.set_org_context('&OrganizationID');


 

3. Manufacturing

3.1 Manufacturing General


PROMPT MANUFACTURING
PROMPT Find standard codes and their meanings
select
   lookup_type,
   lookup_code,
   substr(meaning, 1, 60) "Meaning"
from mfg_lookups
where
upper(lookup_type) like upper('%'||NVL('&YourType', 'N/A')||'%')
or lookup_type IN('SERIAL_NUM_STATUS', 'MTL_PRIMARY_COST', 'MTL_CC_ENTRY_STATUSES', 'MTL_TXN_REQUEST_STATUS', 'MOVE_ORDER_TYPE')
order by lookup_type, lookup_code;



 

3.2. Setup

3.2.1. Inventory

PROMPT MANUFACTURING: INVENTORY
PROMPT Transaction Type
SELECT
        transaction_type_id,
        transaction_action_id,
        TRANSACTION_SOURCE_TYPE_ID,
        TRANSACTION_TYPE_NAME
FROM
        MTL_TRANSACTION_TYPES
WHERE
        to_char(transaction_type_id) like ('%&YourTransactionTypeID%')
ORDER BY
        transaction_type_id;

PROMPT Transaction Source
select transaction_source_type_id, transaction_source_type_name
from mtl_txn_source_types
where transaction_source_type_id like NVL('&TransSourceID', '%');

PROMPT Stuck Transactions - GroupBy MTI
select transaction_type_id, organization_id, substr(error_code, 1, 30), 
substr(error_explanation, 1, 50), to_char(transaction_date, 'YYYY-MM'), 
count(*) 
from mtl_transactions_interface 
group by transaction_type_id, organization_id, to_char(transaction_date, 'YYYY-MM'), 
substr(error_code, 1, 30), substr(error_explanation, 1, 50);



 

3.3. Transactions

3.3.1. Inventory Transactions

PROMPT Stuck Transactions - GroupBy MMTT
select transaction_type_id, organization_id, substr(error_code, 1, 30), 
substr(error_explanation, 1, 50), to_char(transaction_date, 'YYYY-MM'), 
count(*) 
from mtl_material_transactions_temp
group by transaction_type_id, organization_id, to_char(transaction_date, 'YYYY-MM'), 
substr(error_code, 1, 30), substr(error_explanation, 1, 50);

PROMPT Stuck Transactions - GroupBy Move Order
select transaction_type_id, to_char(transaction_date,'YYYY-MON'), 
decode(transaction_status,2,'Untransacted Move order', transaction_status), 
error_code, error_explanation, 
count(*) 
from mtl_material_transactions_temp
where organization_id = &Org_id
group by transaction_type_id, to_char(transaction_date,'YYYY-MON'), 
decode(transaction_status,2,'Untransacted Move order', transaction_status), 
error_code, error_explanation;

PROMPT Stuck Transactions - GroupBy MMT
select transaction_type_id, organization_id, costed_flag, 
to_char(transaction_date, 'YYYY-MM'), error_code, substr(error_explanation, 1, 50), 
count(*) 
from mtl_material_transactions 
where costed_flag IN ('N','E')
group by transaction_type_id, organization_id, costed_flag, to_char(transaction_date, 'YYYY-MM'), 
error_code, substr(error_explanation, 1, 50);

PROMPT Stuck Transactions Dump - MTI
select transaction_interface_id, inventory_item_id, organization_id, subinventory_code, 
locator_id, revision, transaction_quantity, transaction_date, transaction_type_id, 
transaction_source_id, transfer_subinventory, transfer_locator, 
trx_source_line_id, cost_group_id, process_flag, lock_flag, transaction_mode,
    error_explanation, error_code
from mtl_transactions_interface
order by transaction_source_id, trx_source_line_id;

PROMPT Stuck Transactions Dump - MMTT
select transaction_temp_id, inventory_item_id, organization_id, subinventory_code, locator_id, revision,
    transaction_quantity, transaction_date, transaction_type_id, transaction_source_id, transfer_subinventory,
    transfer_to_location, trx_source_line_id, cost_group_id, process_flag, lock_flag, transaction_mode,
    error_explanation, error_code
from mtl_material_transactions_temp
order by transaction_source_id, trx_source_line_id;

PROMPT Stuck Transactions Dump - MMT
select
transaction_id, inventory_item_id, organization_id, subinventory_code, 
locator_id, revision, transaction_quantity, transaction_date, 
transaction_type_id, transaction_source_id, transfer_subinventory, 
transfer_locator_id, trx_source_line_id, cost_group_id, 
error_explanation, error_code,
from mtl_material_transactions
where costed_flag IN ('N','E')
order by transaction_source_id, trx_source_line_id;