Launching JDeveloper for a given folder without seeing other projects.

When we are accessing jdeveloper without Admin previleges, we want to open a specific project under our Jdev home, then follow the below steps:

Go to cmd prompt:
set JDEV_USER_DIR=<JdevHone>\<projid>
then
launch jdevloper  <Jdev Bin Install dir>\jdeveloper.exe

example:

set JDEV_USER_DIR=C:\SK\Work\Work\jdev\xxtest
C:\SK\Software\p9879989_R12_GENERIC\jdevbin\jdeveloper.exe

Query to find GL periods for a given legal entity

Query to find GL periods for a given legal entity:

 select gp.period_name
                  from gl_ledger_norm_seg_vals bsv,gl_ledgers gl, gl_periods gp
                 where  1=1
                 AND Bsv.Legal_Entity_Id = :Legal Entity Id
                 And Bsv.Segment_Type_Code = 'B'
                AND sysdate between nvl(bsv.start_date,sysdate-1) and nvl(bsv.end_date,sysdate+1)
                And Gl.Object_Type_Code = 'L'                
                And Gl.Ledger_Category_Code = 'PRIMARY'
                And Gl.Ledger_Id=Bsv.Ledger_Id
                AND gp.period_set_name=gl.period_set_name
                order by gp.start_date
                ;

Derive GL Start and End Dates..

select start_date,end_date
                  from gl_ledger_norm_seg_vals bsv,gl_ledgers gl, gl_periods gp
                 where  1=1
                 AND Bsv.Legal_Entity_Id = :p_Legal_entity_id
                 And Bsv.Segment_Type_Code = 'B'
                AND sysdate between nvl(bsv.start_date,sysdate-1) and nvl(bsv.end_date,sysdate+1)
                And Gl.Object_Type_Code = 'L'                
                And Gl.Ledger_Category_Code = 'PRIMARY'
                And Gl.Ledger_Id=Bsv.Ledger_Id
                AND gp.period_set_name=gl.period_set_name
                and gp.period_name = :p_period_name
                order by gp.start_date

                ;

Query to find legal entity,registration and address details

Query to find legal entity details

SELECT xep.name,                                          -- Legal Entity Name
       xjv.registration_code_le registration_code,        -- Registration Code
       xr.registration_number,                          -- Registration Number
          DECODE (hl.address_line_1, NULL, '', hl.address_line_1 || ', ')
       || DECODE (hl.address_line_2, NULL, '', hl.address_line_2 || ', ')
       || DECODE (hl.address_line_3, NULL, '', hl.address_line_3 || ', ')
       || DECODE (hl.town_or_city, NULL, '', hl.town_or_city || ', ')
       || DECODE (hl.region_2, NULL, '', hl.region_2 || ', ')
       || DECODE (hl.postal_code, NULL, '', hl.postal_code || ', ')
       || DECODE (ftv.territory_short_name,
                  NULL, '',
                  ftv.territory_short_name)
          Address --Address
  FROM xle_registrations xr,
       xle_jurisdictions_vl xjv,
       hr_locations hl,
       fnd_territories_vl ftv,
       xle_entity_profiles xep
 WHERE     1 = 1
       AND xep.legal_entity_id = :legal_entity_id
       AND xr.source_table = 'XLE_ENTITY_PROFILES'
       AND xr.source_id = xep.legal_entity_id
       AND xjv.jurisdiction_id = xr.jurisdiction_id
       AND hl.location_id = xr.location_id
       AND ftv.territory_code = hl.country
       AND SYSDATE BETWEEN NVL (xr.effective_from, SYSDATE - 1)
                       AND NVL (xr.EFFECTIVE_TO, SYSDATE + 1);

Invalid number error when working with DFF attributes..

Invalid number error when working with DFF attributes when using to_number:

When we are working with DFFs, segments can be configured to store numbers but due to user wrong input or some misconfiguration some rows might end with having string with numbers and characters. When we use that attribute in a query and try to compare it to a number or try to convert to a number, the system will throw error. To avoid the error, we can use the following condition in the query:

select * from po_headers_all
where org_id=101
and nvl(length(regexp_replace(attribute5,'^[0-9]+', ''),0)=0;


to find invalid rows you can use below.

nvl(length(regexp_replace(attribute5,'^[0-9]+', ''),0)>0;

API and Query to get AP Invoice Status

API and Query to get AP Invoice Status:

1.  Get Invoice status from API
SELECT ai.invoice_num,
       ai.invoice_amount,
       AP_INVOICES_PKG.GET_APPROVAL_STATUS (ai.invoice_id,
                                            ai.invoice_amount,
                                            ai.payment_status_flag,
                                            ai.invoice_type_lookup_code)
          inv_status
  FROM ap_invoices_all ai
 WHERE invoice_num = '<inv number>' AND org_id = <org id>;



2. Get actual meaning from below query from lookup:
  SELECT lookup_type,
         LOOKUP_CODE,
         MEANING,
         DESCRIPTION,
         TAG,
         START_DATE_ACTIVE,
         END_DATE_ACTIVE,
         ENABLED_FLAG
    FROM FND_LOOKUP_VALUES_VL
   WHERE     (   NVL ('', territory_code) = territory_code
              OR territory_code IS NULL)
         AND (VIEW_APPLICATION_ID = 200)
         AND (SECURITY_GROUP_ID = 0)
         AND lookup_Type = 'NLS TRANSLATION'
         AND LOOKUP_CODE =  '<Inv_status from above query> ' ---'NEVER APPROVED'
ORDER BY LOOKUP_CODE;

Useful Queries

1. Print Date and time using sysdate:
    SELECT TO_CHAR (SYSDATE, 'DD-MM-YY HH:MI:SS') FROM DUAL;

2. Print Numbers from 1 to 10.
 SELECT ROWNUM r
      FROM DUAL
CONNECT BY ROWNUM <= 10;

3. Kill Session
alter system kill session '1266,54057'; --'sid, serial#'

4. Gather Schema stats:
begin
DBMS_STATS.GATHER_TABLE_STATS('AP','AP_SUPPLIER_SITES_ALL');
end;
/

5. Remove special  (control chars) characeters from the string

SELECT REGEXP_REPLACE(colx, '[[:cntrl:]]', ' ') FROM xx_tab;

6. REPLACE ONE OR MORE COMMAS WITH SINGLE COMMA AND SPACE

select regexp_replace(' Address Line1,,Addressline3,,Postal Code,,57000  ',',{1,}', ', ') from dual;

Useful data dictionary views

NLS_DATABASE_PARAMETERS --> lists permanent NLS parameters of the database.NLS_INSTANCE_PARAMETERS lists NLS parameters of the instance.
There is one row for each component.You can query any of DBA_*, ALL_*,USER_*.In the below table I am listing only USER_

Dictionary Name	Details
USER_OBJECTS	Stores details about all the objects in a schema – Object Types – Table, View, Synonym, Trigger, Sequence, Procedure, Function, Package, Package Body, Trigger, Index, Database Link, Type, polcies, Rules (VPD policy rules)
USER_TABLES	Table – Table Space, Disk block allocation details
USER_TAB_COLS	Column of table
USER_VIEWS	Views data dictionary – You can get the view text
USER_SEQUENCES	Sequence information – Seq min, max, increment, etc.
user_synonyms	Synonyms – Reference object details
User_triggers	Stores the all the trigger in the schema. – You can get the trigger text and trigger table etc.
User_trigger_cols	Trigger column details
User_source	Stores all the source code – You can get code for trigger, procedure, package, package body, type body, type, function, procedure.
User_procedures	Lists all the procedure – You can get a list of procedure for a given package.
User_arguments	List of all the parameters for a given procedure. It includes package procedures as well.
User_indexes	List of Indexes ex: select * from user_indexes where table_name=''
user_ind_columns	Index column names
user_dependencies	Dependencies  select * from user_dependencies                  where referenced_name='GL_LEDGER_NORM_SEG_VALS'                  and name not like 'XX%'                 and referenced_type='SYNONYM' and referenced_owner='APPS';