EBS : Oracle Apps GL Important Query

 Oracle APPS GL Query

• Query to get GL Daily rates

select * from apps.gl_daily_rates

where trunc(conversion_date) ='22-MAR-2023'

and from_currency='EUR' and TO_CURRENCY ='USD'

and conversion_type ='1002'

order by 3 desc;

EBS : Oracle PLSQL : File Handling using UTL

 File Handling using UTL in Oracle PLSQL

• How to check file avilabilty in the directory

UTL_FILE.fgetattr(v_dir_path, v_file_name, v_exists, v_f_len, v_bsize);

• remove the existing file from the directory 

 UTL_FILE.fremove (v_location,  v_file_name )

ORACLE PLSQL : Nested Block Example

 PLSQL : Nested Block Example

declare

l_main_block varchar2(100) := 'MAIN_BLOCK';

begin

    dbms_output.put_line('Main Block:'||l_main_block);

    dbms_output.put_line('Nested Block inside min Block:'||l_nested_block);

 

  ----------------------------Nested block---------------

 

  declare

  l_nested_block varchar2(100) := 'NESTED_BLOCK';

  begin

      dbms_output.put_line('nested Block:'||l_nested_block); --to write another business logic

      dbms_output.put_line('Main Block inside nested Block:'||l_main_block);

  end;   

  ---------------------------end -Nested block---------------

 

exception when others then

 dbms_output.put_line('Error:'||SQLERRM);

end;

EBS: API to get GL Account Code Description : GL

  API to get GL Account Code Description

APPS.GL_FLEXFIELDS_PKG.GET_DESCRIPTION_SQL (
                  GCC.CHART_OF_ACCOUNTS_ID,
                  2,
                  GCC.SEGMENT2
               )

EBS :Query to find Concurrent Program Responsibility name

 ---------Query to find Conccurrent Program Responsibility name—

SELECT fav.APPLICATION_NAME,frq.REQUEST_GROUP_NAME,request_unit_id,frq.*

  FROM fnd_request_group_units frqu, fnd_request_groups frq, fnd_application_vl fav, fnd_concurrent_programs

WHERE 1=1--request_unit_id = 183400  ---conc prog id/request setid

and frqu.request_group_id=frq.request_group_id

and request_unit_id =CONCURRENT_PROGRAM_ID

and CONCURRENT_PROGRAM_NAME ='STATMENT_EMAIL'

and frq.application_id = fav.APPLICATION_ID

EBS : How to remove leading zero in Excel Output report (Oracle BI Publisher Report)

EBS : How to remove leading zero in Excel Output report (Oracle BI Publisher Report)

Step1: Go to RTF Template of that particular fields

Step2: Add below code in that fields

"<fo:bidi-override direction="ltr" unicode-bidi="bidi-override"><?ITEM_CODE?></fo:bidi-override>"

EBS : AP Query : Oracle APPS

Oracle APPS AP Useful Query

Query to get List of Invoice on Hold

SELECT

    aiv.vendor_name,    aiv.vendor_number,       aiv.invoice_num,      aiv.gl_date,       aiv.terms_date,

    aiv.terms_name,      ahv.hold_date,    ahv.release_date 

FROM    apps.ap_invoices_v aiv,apps.ap_holds_v ahv

WHERE

    1=1

    and aiv.invoice_id=ahv.invoice_id

  --  and aiv.invoice_id=apsa.invoice_id

  and   HOLD_LOOKUP_CODE   like 'QTY ORD%' and RELEASE_REASON is null

order by hold_date desc;

EBS : How to read XML File using SQL Query: Oracle APPS

 EBS : How to read XML File using SQL Query: Oracle APPS

Preparing a Nested Query to read XML FILE from Appl Server.

select Hdrxml.TRX_ID, Hdrxml.BILL_TO_NAME, Hdrxml.TRX_NUMBER,Hdrxml.G_LINE,linexml.*

                            from

                            (SELECT XMLTYPE(bfilename('XX_AR_INV_OUT', 'o87864385Copy.out'), nls_charset_id('UTF8')) xml_data

                             FROM dual) a, xmltable('/XX_AR_INV_XML_GEN/LIST_G_INV/G_INV' passing a.xml_data

                              columns TRX_ID NUMBER path 'TRX_ID',

                                      BILL_TO_NAME VARCHAR2(1000) path 'BILL_TO_NAME',

                                      TRX_NUMBER VARCHAR2(1000) path 'TRX_NUMBER' ,

                                      G_LINE XMLTYPE PATH 'LIST_G_LINE/G_LINE'

                                     ) Hdrxml,

                               xmltable('/G_LINE' passing Hdrxml.G_LINE

                              columns LINE_NUMBER NUMBER path 'LINE_NUMBER',

                                      ITEM_NUMBER VARCHAR2(1000) path 'ITEM_NUMBER',

                                      QUANTITY_SHIPPED VARCHAR2(1000) path 'QUANTITY_SHIPPED',

                                      UNIT_PRICE  VARCHAR2(1000) path 'UNIT_PRICE',

                                      UOM_CODE    VARCHAR2(1000) path 'UOM_CODE'

                                     ) linexml       

                                 ;   

Oracle SQL Query Video

 

Oracle SQL Query useful Tutorial Video

https://www.youtube.com/watch?v=0OmNC-_Khrw

Oracle SQL : Live Example : LISTAGG,DECODE,CASE,UNION,GROUP, HAVING

 Oracle SQL : Live Example

----Converting multiple columns into single using  LISTAGG Function--

select distinct aia.invoice_num,LISTAGG( line_number,',') WITHIN GROUP( ORDER BY line_number ) AS line_num from ap_invoices_all aia, ap_invoice_lines_all aila

where aia.invoice_id=aila.invoice_id

and aia.org_id=aila.org_id

group by aia.invoice_num;

---------------- Group by having clause-----------------------------------------------

select 'A' BLOCK ,aia.invoice_num,LISTAGG( line_number,',') WITHIN GROUP( ORDER BY line_number ) AS line_num

from ap_invoices_all aia, ap_invoice_lines_all aila

where aia.invoice_id=aila.invoice_id

and aia.org_id=aila.org_id

group by aia.invoice_num

having count(aila.line_number) < 3

---------------------Union ALL -------------

select 'A' BLOCK ,

aia.invoice_num--,LISTAGG( line_number,',') WITHIN GROUP( ORDER BY line_number ) AS line_num

from ap_invoices_all aia, ap_invoice_lines_all aila

where aia.invoice_id=aila.invoice_id

and aia.org_id=aila.org_id

and aia.invoice_num ='ERS-36577'

--and rownum <5

--group by aia.invoice_num;

--having count(aila.line_number) < 3

Union all

select 'B' BLOCK ,

aia.invoice_num--LISTAGG( line_number,',') WITHIN GROUP( ORDER BY line_number ) AS line_num

from ap_invoices_all aia, ap_invoice_lines_all aila

where aia.invoice_id=aila.invoice_id

and aia.org_id=aila.org_id

and aia.invoice_num ='ERS-36577'

--group by aia.invoice_num

--having count(aila.line_number) > 10;

-----------------------Decode , Case, ----------------------------------------

select gcck.concatenated_segments,

--gcck.SEGMENT1||'-'||gcck.SEGMENT2||'-'||gcck.SEGMENT3||'-'||gcck.SEGMENT4|| '-'||gcck.SEGMENT5||'-'||gcck.SEGMENT6||'-'||gcck.SEGMENT7||'-'||gcck.SEGMENT8||'-'||gcck.SEGMENT9 "ACCOUNT",

 gcck.SEGMENT3,

 case  when gcck.SEGMENT3 in  ( 5250,1110,1590,4150)then

 'ABC'

 ELSE

 NULL

 end SOB_ITP,

 decode(gcck.SEGMENT3,5250,'abc',1110,'xyz',null)

 from gl_code_combinations_kfv gcck;

 where gcck.SEGMENT3 != '5250';

 ---------Nested Case Statement---------

 select gcck.concatenated_segments, gcck.SEGMENT3,

 case  when gcck.SEGMENT2 in  ( '520','170','510')then

   case when gcck.SEGMENT3 in  ( '5250','1590')then

   'NESTED CASE'

   else

   'NESTED ELSE'

   end

 ELSE

 NULL

 end SOB_ITP

 from gl_code_combinations_kfv gcck;

 where gcck.SEGMENT3 != '5250';

EBS : Oracle :SQL Query Question

Oracle Apps SQL Query Requirements 

1) write a query to display po_no,po_line_num,po_shipment_number, po_distribution num and  charge account code

2) Write query to display code combination segment against charge_account ccid of po _distributions