Skip to main content

Discoverer Queries to find Report Details


How to find the External Table referred in the Query?
SELECT doc_name,
ba_name,
  folder_name,
  user_name,
  ext_table,
  EUL
FROM
  (SELECT d.DOC_name,
    b.ba_name ba_name,
    o.obj_name folder_name,
    o.sobj_ext_table ext_table,
    e.EXP_name item_name,
    UPPER (USR1.user_name) user_name,
    'EUL4_US' EUL
  FROM eul4_us.eul5_objs o
  LEFT JOIN eul4_us.eul5_expressions e
  ON o.obj_id = e.it_obj_id
  LEFT JOIN eul4_us.eul5_elem_xrefs x
  ON e.exp_id = x.ex_to_id
  LEFT JOIN eul4_us.eul5_documents d
  ON d.doc_id = x.ex_from_id
  LEFT JOIN eul4_us.EUL5_ba_obj_links l
  ON l.bol_obj_id = o.obj_id
  LEFT JOIN eul4_us.EUL5_bas b
  ON b.ba_id = l.bol_ba_id
  LEFT JOIN eul4_us.eul5_eul_users EU1
  ON d.DOC_EU_ID + 0 = EU1.eu_id
  LEFT JOIN applsys.fnd_user USR1
  ON SUBSTR (EU1.eu_username, 2) = TO_CHAR (USR1.user_id)
  WHERE b.ba_name LIKE '%'
  --AND upper(e.exp_name) LIKE upper('%')                        --folder item
  --AND upper(b.ba_name) LIKE upper('%Sample BA Name%') --business area
  AND upper(o.obj_name) LIKE upper('%PPER%')                       --folder
-- AND upper(d.doc_name) LIKE upper('%Payroll%Season%')                        --report name
    /*   and (upper(o.SOBJ_ext_table) like  upper('%XXBLOG_MV%')

    )*/
  AND UPPER (USR1.user_name) = 'BIADMIN'
  )
GROUP BY doc_name,
ba_name,
  folder_name,
  user_name,
  ext_table,
  EUL;

Comments

Popular posts from this blog

How to setup and use AME - Approval Management Engine

Approval Management Engine - AME For Purchase Requisition Approvals Purchase Requisitions can be routed for approval using the AME Approval Management Engine. This whitepaper describes how to setup AME for use with requisition approvals, and shows how a requisition approval list is built based on the AME setup. Approvers in the AME based approver list are assigned to the requisition based on the AME rules setup for the Purchase Requisition Approval transaction. Similar setup can be done for Requester Change Order Approval and for Internal Requisition Approval, although those are not specifically covered in this whitepaper. The screenshots provided are based on 11i.AME.B, and some of the navigation details are specific to 11i.AME.B. However, most of the details provided are applicable to 11i.AME.A and higher including R12. Assign AME Roles and Responsibilities AME responsibilities in 11i.AME.A are assigned directly to the users. However, In R12 or 11i.AME.B and higher, AME respons...

How to compile all INVALID objects in Oracle

There are five ways to recompile invalid objects in schema. DBMS_DDL DBMS_UTILITY UTL_RECOMP UTLRP.SQL Manually Recompile > Best Approach 1. DBMS_DDL This procedure is equivalent to the following SQL statement: ALTER PROCEDUREFUNCTIONPACKAGE [.] COMPILE [BODY] Syntax Exec dbms_ddl.alter_compile ( type , schema, name); Type : Must be either PROCEDURE, FUNCTION, PACKAGE, PACKAGE BODY or TRIGGER. Schema : Database Username Name : Objects name Example SQL> exec dbms_ddl.alter_compile ('PROCEDURE','SCOTT','TEST'); PL/SQL procedure successfully completed. 2. DBMS_UTILITY This procedure compiles all procedures, functions, packages, and triggers in the specified schema. Syntax Exec dbms_utility.compile_schema ( schema,compile all) Schema : Database Username Compile All : Object type ( procedure, function, packages,trigger) Example SQL> exec dbms_utility.compile_schema('SCOTT'); PL/SQL procedure successfully co...

LDT Commands

1. Lookups == ============= FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/aflvmlu.lct XX_CUSTOM_LKP.ldt FND_LOOKUP_TYPE APPLICATION_SHORT_NAME="XXCUST" LOOKUP_TYPE="XX_LOOKUP_TYPE" FNDLOAD apps/apps O Y UPLOAD $FND_TOP/patch/115/import/aflvmlu.lct XX_BLOG_LKP.ldt UPLOAD_MODE=REPLACE CUSTOM_MODE=FORCE 2. Concurrent Program == ============================= FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afcpprog.lct XX_BLOG_CP.ldt PROGRAM APPLICATION_SHORT_NAME="XXCUST" CONCURRENT_PROGRAM_NAME="XX_CONCURRENT_PROGRAM" FNDLOAD apps/apps 0 Y UPLOAD $FND_TOP/patch/115/import/afcpprog.lct XX_BLOG_CP.ldt = WARNING=YES UPLOAD_MODE=REPLACE CUSTOM_MODE=FORCE 3. Profile == ========= FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afscprof.lct XX_BLOG_PRF.ldt PROFILE PROFILE_NAME="XX_PROFILE_NAME" APPLICATION_SHORT_NAME="XXCUST" $FND_TOP/bin/FNDLOAD apps/apps 0 Y UPLOAD $FND_TOP/patch...