ITPub博客

1.通过User，查找此用户挂的所有职责（Responsibilities）SELECT FU.USER_ID ,       FU.USER_NAME          USER_NAME ,       FR.RESPONSIBILITY_KEY RESPONSIBILITY ,       FRT.Responsibility_Name ,       FA.APPLICATION_NAME   APPLICATION  FROM FND_USER   &nbs

一个表中存在但另外一个表中不存在的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   );NOT INSELECT ipFROM   login_logWHERE  ip NOT IN (   SELECT DISTINCT ip   FROM &nbsp

按年，月，日，季度，周分组统计某张表数据的发生量。--按年度分组统计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;--按月份分组统计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;--按日分组统计SELECT TO_CHAR(CREATI

$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: Find data_group_id dg_id for the given data_group_name.For each row with data_group_id 0 in FND_RESPONSIBILITY, look fora corresponding row with data_group_id dg_id, with the same application_id,and responsibility_name that only differs in the given suffix string s

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, D.START_QUANTITY Start_Quantity, D.JOB_TYPE Job_Type, D.COMPLETION_SUBINVENTORY Subinventory, D.COMPLETION_LOCATOR_ID Compl_Loc_Id, D.BOM_REVISION BOM_Revision, D.BOM_REVISION_DATE BOM_REVISION_DATE, SUBSTR(TO_CHAR(D.BOM_REVISION_DATE,\'HH24:MI\'),1,5) BOM_TIME, D.SCHEDULED_START_DATE Sc

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, REQUISITION_LINE_IDPO_REQUISITION_LINES_ALL =>REQUISITION_LINE_IDPO_REQUISITION_HEADERS_ALL =>REQUISITION_HEADER_ID, REQUISITION_LINE_ID, SEGMENT1What you have to make a join on PO_DISTRIBUTIONS_ALL (REQ_DISTRIBUTION_ID) and PO_REQ_DISTRIBUTIONS_ALL (DISTRIBUTION_ID) to see if there is a

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.ADDRESS_LINE1 ,POS.ADDRESS_LINE2 ,POS.ADDRESS_LINE3 ,POS.ADDRESS_LINE4 ,POS.CITY ,POS.COUNTRY ,POS.ZIP ,POS.COUNTY ,POS.STATE ,POS.INVOICE_CURRENCY_CODE ,POS.PAYMENT_CURRENCY_CODE ,POS.PAYMENT_METHOD_LOOKUP_CODE ,POS.PAY_GROUP_LOOKUP_CODE ,HR.NAME ,INACTIVE_DATE ,GLCC.CONCATENATED_SEGMENTS LIABILITY_ACC

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 ######################################################################### REM ## Purpose: Diagnostic Script for Concurrent Requests REM ## Author: Brian Kerr REM ## Email: brian.kerr@oracle.com REM ## Filename: reqcheck.sql REM ## Cert: 10.7, 11, 11.5, 12 REM ## Note: REM ## Usage: sqlplus apps/ @reqcheck.sql REM ## Out

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) "SHORT NAME" FROM APPS.FND_CONCURRENT_PROGRAMS_VL A, APPS.FND_APPLICATION_VL B WHERE A.APPLICATION_ID=B.APPLICATION_ID AND A.ENABLED_FLAG=\'Y\' AND (Upper(A.USER_CONCURRENT_PROGRAM_NAME) LIKE Upper(\'%purge%\') OR Upper(A.USER_CONCURRENT_PROGRAM_NAME) LIKE Upper(\'%delete

CREATE OR REPLACE PROCEDURE SU_PROCESS_BOM AS -- API input variables l_bom_header_rec Bom_Bo_Pub.bom_head_rec_type := Bom_Bo_Pub.g_miss_bom_header_rec; l_bom_revision_tbl Bom_Bo_Pub.bom_revision_tbl_type := Bom_Bo_Pub.g_miss_bom_revision_tbl; l_bom_component_tbl Bom_Bo_Pub.bom_comps_tbl_type := Bom_Bo_Pub.g_miss_bom_component_tbl; l_bom_ref_designator_tbl Bom_Bo_Pub.bom_ref

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);        -- Oracle Home     DBMS_OUTPUT.PUT_LINE (\'ORACLE_HOME = \' || LC$ORACLE_HOME);        -- Oracle SID     DBMS_OUTPUT.PUT_LINE (

DECLAREp_userid NUMBER;p_respid NUMBER;p_applid NUMBER;x_resp_name VARCHAR2(50);BEGIN  SELECT user_id   INTO p_userid FROM fnd_user   WHERE user_name =\'MFG\';   SELECT responsibility_id   INTO p_respid FROM fnd_responsibility_tl  WHERE responsibility_name = \'Purchasing, Vision Operations (USA)\';   p_applid := 201;   -- Puchasing Module  fnd_global.apps_initialize(p_userid, p_respid, p_applid);  SELECT fnd_profile.value(\'RESP_NAME\') IN

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 FND_USER WHERE USER_NAME = \'MFG\';How to get input parameter resp_id?eg.SELECT RESPONSIBILITY_ID FROM FND_RESPONSIBILITY_TL WHERE RESPONSIBILITY_NAME = \'Inventory\';How to get input parameter resp_appl_id?eg.SELECT APPLICATION_ID FROM FND_APPLICATION WHERE APPLICATION_SHORT_NAME  = \'INV\'

Script that queries the Menu structureSELECT LPAD (\' \', 6 * (LEVEL - 1)) || menu_entry.entry_sequence SEQUENCE, LPAD (\' \', 6 * (LEVEL - 1)) || menu_entry.prompt prompt, menu_entry.grant_flag grant_flag, DECODE (menu_entry.sub_menu_id, NULL, \'FUNCTION\', DECODE (menu_entry.function_id, NULL, \'SUBMENU\', \'BOTH\') ) TYPE, menu2.user_menu_name, func2.user_function_name FROM fnd_menu

Get APPL_TOPselect name,       node_id,       path,       shared,       created_by,       creation_date,       last_updated_by,       last_update_date,       FILE_SYSTEM_GUID,       appl_top_guidFROM APPLSYS.FND_APPL_TOPS;Get &l

SELECT PAAF.SUPERVISOR_ID MGR_ID,      PJ.JOB_ID ,      PAPF.FULL_NAME,      PJ.NAME,      PAPF.PERSON_ID PER_IDFROM PER_PEOPLE_X PAPF,      PER_ASSIGNMENTS_X PAAF,      PER_JOBS PJWHERE PAPF.PERSON_ID = PAAF.PERSON_IDAND PAAF.JOB_ID      = PJ.JOB_IDSTART WITH PAPF.PERSON_ID   = 57--Smith,Jonathan(MFG)CONNECT BY

select distinct d.user_name from apps.fnd_logins a,v$session b, v$process c, apps.fnd_user d where b.paddr = c.addr and a.pid=c.pid and a.spid = b.process and d.user_id = a.user_id and (d.user_name = \'USER_NAME\' OR 1=1);

SyntaxFND_PROFILE.SAVE(<Profile_Option_Name>,                  <Profile_Option_Value>,                  <Level SITE/APPL/RESP/USER>,                  <Level_Value>,            &nb

SELECT fu.user_name,frt.RESPONSIBILITY_NAME, fu.user_id,furg.responsibility_id FROM fnd_user_resp_groups furg, FND_RESPONSIBILITY fr, fnd_responsibility_tl frt, fnd_user fu WHERE fu.user_name =upper(\'ptian\') AND fu.user_id = furg.user_id AND furg.responsibility_id = fr.RESPONSIBILITY_ID AND frt.responsibility_id = fr.RESPONSIBILITY_ID ORDER BY 1

begin fnd_user_pkg.addresp( ‘&User_Name’, /*Application User Name */ ‘&Responsablity_Application_Short_Name’, /*get from Query Below */ ‘&Responsibility_Key’,/*get from Query Below */ ‘&Security_Group’, /* Most of cases it is ‘STANDARD’ so you can hard code it */ ‘&Description’, /* Any comments you want */ ‘&Start_Date’, /* Sysdate From Today */ ‘&End_Date’ ); /* Sysdate + 365 Ri