Scripts
文章平均质量分 79
田攀
tian.pan@qq.com
展开
-
Oracle EBS用户登录情况统计脚本
select distinct d.user_name from apps.fnd_logins a,v$session b, v$process c, apps.fnd_user dwhere b.paddr = c.addrand a.pid=c.pidand a.spid = b.processand d.user_id = a.user_idand (d.user_name =原创 2012-12-15 19:49:51 · 5039 阅读 · 0 评论 -
Script to list Customer (Party), Account, Site data
The Script to list of its customer (party), account, site, address, collector, and other related information. SELECT ---------------------------------------------------------- -- Party I原创 2013-04-03 13:29:42 · 3242 阅读 · 0 评论 -
Scripts about Vendor(Supplier) Information
Use the Below Query to get the Vendor Details.SELECT PO.SEGMENT1 VENDOR_NUMBER,PO.VENDOR_NAME,PO.VENDOR_NAME_ALT,PO.VENDOR_TYPE_LOOKUP_CODE,POS.VENDOR_SITE_CODE,VENDOR_SITE_CODE_ALT,POS.ADDRES原创 2013-04-03 12:23:15 · 3239 阅读 · 1 评论 -
The scripts to find Folder details
SELECT * FROM FND_FOLDERS WHERE object = 'INVTVTXN_RESULTS';SELECT *FROM FND_FOLDER_COLUMNSWHERE folder_id in(SELECT folder_idFROM FND_FOLDERSWHERE object = 'INVTVTXN_RESULTS');TablesFND_FOLDERS store原创 2012-09-06 19:09:04 · 3340 阅读 · 0 评论 -
Using script to submit INV Manager to process MTI/MMTT
1.Using script to submit INV Manager to process MMTT/** When prompted for user_id, resp_id and resp_appl_id, ask ct enter the correct values for * these variables. The values can be obtained by logg原创 2012-07-03 10:46:03 · 5079 阅读 · 0 评论 -
PO Approve Script
EBS PO Approve Scriptdeclare x_po_head_id number ;beginselect po_header_idinto x_po_head_idfrom po_headers_all wheresegment1 = '&ponumber'and org_id = &org_id;update po_headers_allset au原创 2012-03-14 11:33:34 · 2629 阅读 · 0 评论 -
Script to Create/Allocate/Transact Move Order
Creating Move OrderDECLARE x_return_status VARCHAR2 (1); x_msg_data VARCHAR2 (4000); x_msg_count NUMBER; l_user_id NUMBER; l_resp_id NUMBER; l_appl_id NUMBER;原创 2012-11-14 12:12:58 · 12831 阅读 · 0 评论 -
Oracle EBS中查询Profile的各种SQL
1.List E-Business Suite Profile Option Values For All Levelsset long 10000set pagesize 500set linesize 160column SHORT_NAME format a30column NAME format a40column LEVEL_SET format a15column CONT原创 2012-06-11 16:15:52 · 7421 阅读 · 0 评论 -
查询所有ICX Profile的脚本
查询所有ICX相关Profile的SQLset pages 100set line 120column "Option" format 99999999column "Profile Name" format A35column "Profile Value" format A55spool support_profile_value.txtselect a.PROFILE_OPTIO原创 2012-08-30 16:18:10 · 2084 阅读 · 0 评论 -
Using API FND_PROFILE.save to update profile from backend
SyntaxFND_PROFILE.SAVE(, , , , );ExampleSET SERVEROUTPUT ON SIZE 1000000DECLARE l_user_id NUMBER; l_resp_id NUMBER; l原创 2012-12-12 12:15:55 · 8129 阅读 · 0 评论 -
The script to query all Concurrent Programs which used to Purge or Delete
Script Like:SELECT SUBSTR(B.APPLICATION_SHORT_NAME,1,11) "APPLICATION", SUBSTR(A.USER_CONCURRENT_PROGRAM_NAME,1,50) "CONCURRENT PROGRAM NAME", SUBSTR(A.CONCURRENT_PROGRAM_NAME,1,35) "SHO原创 2013-02-19 23:59:50 · 5954 阅读 · 0 评论 -
Diagnostic Script for Concurrent Requests
source:REQCHECK.sql - Diagnostic Script for Concurrent Requests (Doc ID 164978.1)This Script is made available for Diagnosing Common Problems Related to Concurrent Requests.REM #######################原创 2013-03-04 17:06:59 · 2804 阅读 · 0 评论 -
Script – Tablespace Information
Script 1: Tablespace informationSET linesize 150column tablespace_name format a20 heading 'Tablespace'column sumb format 999,999,999column extents format 9999column bytes format 999,999,999,999co原创 2013-03-04 15:33:44 · 2395 阅读 · 0 评论 -
Script – Database structure and file location
1.Control Filesset pagesize 500set linesize 130PromptPrompt Control Files Location >>>>col name format a60 heading "Control Files"select namefrom sys.v_$controlfile/2.Redo Log FilesPromptP原创 2013-03-04 15:23:21 · 2293 阅读 · 0 评论 -
user_id, resp_id and resp_appl_id
Many oracle apps scripts need user_id, resp_id and resp_appl_id as input parameter,so how to get the proper value from your systemFrom Back EndHow to get input parameter user_id?eg.SELECT USER_ID FROM原创 2013-01-21 11:33:40 · 5362 阅读 · 0 评论 -
The script which used to Check GL-PO-AP-INV Period Status
--需要输入set_of_books_id,set_of_books_id可以从GL_SETS_OF_BOOKS查,Demo环境中的Vision Operations (USA)的set_of_books_id是1 SELECT a.period_name, a.period_num, a.gl_status, b.po_原创 2013-02-05 11:47:18 · 4393 阅读 · 0 评论 -
Sample of API FND_PROFILE
1. FND_PROFILE.GET(‘Name of the Profile’, variable name);SELECT fnd_profile.value('PROFILEOPTION') ,fnd_profile.value('MFG_ORGANIZATION_ID') ,fnd_profile.value('ORG_ID') ,fnd_profile原创 2012-07-17 11:13:01 · 5522 阅读 · 0 评论 -
Get Oracle Home & Oracle SID from PLSQL
DECLARE LC$ORACLE_HOME VARCHAR2 (2000); LC$ORACLE_SID VARCHAR2 (256); BEGIN DBMS_SYSTEM.GET_ENV ('ORACLE_HOME', LC$ORACLE_HOME); DBMS_SYSTEM.GET_ENV ('ORACLE_SID', LC$ORACLE_SID);原创 2013-01-28 11:05:33 · 2081 阅读 · 0 评论 -
WIP Useful Scripts
Query to find WIP Job detailsSELECT E.WIP_ENTITY_ID, E.WIP_ENTITY_NAME Job, M.SEGMENT1 ITEM, --M.INVENTORY_ITEM_ID, M.DESCRIPTION Item_Desc, D.DESCRIPTION JOB_DESCRIPTION, M.PRIMARY_UOM_CODE UOM原创 2013-04-09 16:58:23 · 3732 阅读 · 0 评论 -
Order Management Useful Scripts
Listed some useful queries scripts for Oracle Order Management Flow.(For Order Management Detailed Flow, You may check my previous post: Oracle EBS订单的流程(Order->AR): http://blog.csdn.net/pan_tian/artic原创 2013-04-08 12:26:31 · 16957 阅读 · 0 评论 -
Oracle Apps修改用户密码的API
Oracle Apps修改用户密码API方法一:fnd_user_pkg.updateuser -- Change password of TEST_USER to oracle123 (does not ask for reset on first logon)BEGIN fnd_user_pkg.updateuser (x_user_name => 'T原创 2014-08-27 18:23:18 · 5476 阅读 · 0 评论 -
Oracle EBS Webservice(Integration Repository) Scripts
Webservice定义的SQLSELECT FF.TYPE, FF.IREP_METHOD_NAME , FIF.FUNCTION_ID , FF.FUNCTION_NAME , FIC.CLASS_NAME , IREP_NAME , FIF.DESCRIPTION , FIC. * FROM FND_FOR原创 2014-08-12 11:15:38 · 3608 阅读 · 0 评论 -
Users and Responsibilites in Oracle EBS
1.通过User,查找此用户挂的所有职责(Responsibilities)SELECT FU.USER_ID , FU.USER_NAME USER_NAME , FR.RESPONSIBILITY_KEY RESPONSIBILITY , FRT.Responsibility_Name , FA.APPLICATION_NAME原创 2014-04-09 10:56:28 · 3277 阅读 · 0 评论 -
Note:1499475.1 - Inventory Analyzer
Doc ID 1499475.1 - Inventory Analyzer : Health Check For Common Inventory Data Issues, Critical Patches, And Setups 这个Note里边的Inventory Analyzer脚本会从多个维度对Oracle EBS的库存模块做一次检测,让DBA对库存模块系统运行状况有一个全面的了解,最大原创 2014-06-23 18:57:38 · 2011 阅读 · 0 评论 -
[Scripts]一个表中存在但另外一个表中不存在的SQL
一个表中存在但另外一个表中不存在的SQL,两种写法:NOT EXISTSSELECT ipFROM login_log lWHERE NOT EXISTS ( SELECT 1 -- it is mostly irrelevant what you put here FROM ip_location i WHERE l.ip = i.ip );N原创 2014-03-21 16:15:37 · 2173 阅读 · 0 评论 -
Update one table with the data in another table
如何用另外B表中的数据Update A表。Sample,UPDATE T1 SET (C1, C2, C3) = (SELECT C1, C2, C3 FROM T2 WHERE T2.USER_ID = T1.USER_ID) WHERE EXISTS (SELECT * FROM T2 WHERE T2.USER_ID = T1.USER_ID);如果没有"Where E原创 2013-10-08 18:08:51 · 2576 阅读 · 0 评论 -
统计类SQL(按时间)
按年,月,日,季度,周分组统计某张表数据的发生量。--按年度分组统计SELECT TO_CHAR(CREATION_DATE,'YYYY-MM') CREATION_DATE,COUNT(*) FROM MTL_SYSTEM_ITEMS_B GROUP BY TO_CHAR(CREATION_DATE,'YYYY-MM')ORDER BY CREATION_DATE DESC;--按月份分组统原创 2013-08-19 17:57:16 · 3959 阅读 · 0 评论 -
Purchasing Useful Scripts
Relation between Requisition and PO tables,Here is link:PO_DISTRIBUTIONS_ALL =>PO_HEADER_ID, REQ_DISTRIBUTION_IDPO_HEADERS_ALL=>PO_HEADER_ID, SEGMENT1PO_REQ_DISTRIBUTIONS_ALL =>DISTRIBUTION_ID, REQUIS原创 2013-04-09 11:46:23 · 11197 阅读 · 0 评论 -
PO Approve Script
declare x_po_head_id number ;beginselect po_header_idinto x_po_head_idfrom po_headers_all wheresegment1 = '&ponumber'and org_id = &org_id;update po_headers_allset authorization_status =原创 2012-04-06 15:33:52 · 1822 阅读 · 0 评论 -
在Procedure中增加自己的Debug Log
如何在Oracle EBS的PL/SQL Procedure中增加自己的Debug Log,并且不希望这些Log不要跟系统的日志混在一起,可以考虑用下边这个方法create sequence pt_debug_sequenceINCREMENT BY 1 -- 每次加几个START WITH 1 -- 从1开始计数NOMAXVALUE -- 不设置最大值NOCYCLE -- 一直累加,不原创 2012-04-06 16:55:25 · 2963 阅读 · 0 评论 -
Open Inventory Periods Script
某个库存组织下,发现有很多库存期间都未打开,而一个一个的开期间比较麻烦,可以使用这个脚本来批量打开库存期间-----------------------------------------------------------l_org_id为 organization's Organization_ID--eg. select * from mtl_parameters where organ原创 2013-05-16 15:30:55 · 3918 阅读 · 0 评论 -
Scripts in $AD_TOP/sql
$AD_TOP/sql 下有许多非常有用的脚本。Script11.0.x10.7NCA10.7SC/16.1Descriptionadcompsc.plsxxxCompile objects in a given schemaadcpresp.sqlx The script duplicates rows in FND_RESPONSIBILITY in the following way: F原创 2013-05-10 22:48:16 · 3241 阅读 · 0 评论 -
[Script]EBS里查看模块的版本、文件的版本信息
系统版本信息装了哪些模块,以及版本信息select 'Current Application Release: '||ver||' ('||bug||')' "Description" from (select x.* from(select 1 seq, '1939818' bug,'11.5.0' pv,'11.5.6' ver from dualunion select 2,'2123原创 2012-06-27 22:36:57 · 5380 阅读 · 0 评论 -
Sales Order Related Scripts
--Line Ids Associated With This Order NumberSELECT LIN.LINE_ID LINE_ID, TO_CHAR(LIN.line_number) || DECODE(LIN.shipment_number, NULL, NULL, '.' || TO_CHAR(LIN.shipment_number)) || DECODE(LIN.optio原创 2013-05-08 16:32:29 · 7665 阅读 · 0 评论 -
How to check whether the patches have been applied or not
AD_APPLIED_PATCHES contains the list of patches that were directly applied.AD_BUGS contains the list of all the patches that were directly applied or were contained in the patches that were directly a原创 2012-04-25 16:49:32 · 12128 阅读 · 0 评论 -
Descriptive Flex Field List
Query:SELECT CONCATENATED_SEGS_VIEW_NAME,APPLICATION_TABLE_NAME,DESCRIPTIVE_FLEXFIELD_NAME,TITLE,CONTEXT_COLUMN_NAME,DESCRIPTION FROM FND_DESCRIPTIVE_FLEXS_VL WHERE APPLICATION_ID = 401 --Take Invent原创 2013-04-17 17:45:07 · 6055 阅读 · 0 评论 -
Solution for error:APP-PO-14376: Please enter a GL Date within an open purchasing period
接收时报APP-PO-14376的错误,APP-PO-14376: Please enter a GL Date within an open purchasing period.Cause: You provided a GL date that is not within an open purchasing period.Action: Enter a date that is within原创 2013-04-14 20:47:40 · 10099 阅读 · 0 评论 -
BOM API - uses the BOM_BO_PUB.PROCESS_BOM API to create bill header and add components to the bill
CREATE OR REPLACE PROCEDURE SU_PROCESS_BOMAS -- API input variables l_bom_header_rec Bom_Bo_Pub.bom_head_rec_type := Bom_Bo_Pub.g_miss_bom_header_原创 2013-01-28 11:40:46 · 8662 阅读 · 0 评论 -
SQL Query to get Oracle Menus & Functions
Script that queries the Menu structureSELECT LPAD (' ', 6 * (LEVEL - 1)) || menu_entry.entry_sequence SEQUENCE, LPAD (' ', 6 * (LEVEL - 1)) || menu_entry.prompt prompt, menu_原创 2013-01-11 11:34:40 · 6990 阅读 · 0 评论 -
How to use script to get all oracle EBS Form name and corresponding fmb file name
How to use script to get all Oracle EBS Form name and corresponding fmb file nameSELECT fff.function_name, ff.form_name, ffft.user_function_name, fff.function_id原创 2012-08-31 17:09:33 · 10918 阅读 · 0 评论