GL to Subledgers Link (Information Query) - Oracle EBS R12

http://generalledger-gl.blogspot.com/2016/06/gl-and-subledgers-link-information-query.html

GL to Subledgers Link (Information Query)

/*select count(*)

  from */--846187  --19339 --5831
 
 select * from
    (select

 nvl(ael.ENTERED_DR,0) - nvl(ael.ENTERED_CR,0)  Entered  ,  
       nvl(ael.ACCOUNTED_DR , 0) - nvl( ael.ACCOUNTED_CR,0) Accounted,
hdr.JE_HEADER_ID,
               ael.AE_HEADER_ID,
               hdr.PERIOD_NAME Period_Name,
               hdr.DEFAULT_EFFECTIVE_DATE Effective_date,
               hdr.NAME Journal_Name,
               ln.DESCRIPTION GL_Description,
               ael.DESCRIPTION Subledger_Description,
               ln.JE_LINE_NUM GL_Line_Num,
               ael.AE_LINE_NUM Subledger_Line_Number,
               hdr.CURRENCY_CODE Currency,
               ln.ENTERED_DR GL_Entered_DR,
               ln.ENTERED_CR GL_Entered_CR,
               ael.ENTERED_DR SUB_Enterred_DR,
               ael.ENTERED_CR SUB_Enterred_CR,
               ael.ENTERED_DR,
               ael.ENTERED_CR,
              -- hdr.CURRENCY_CONVERSION_RATE Exch_Rate,
              NVL( ael.CURRENCY_CONVERSION_RATE,1) Exch_Rate,
               ael.ACCOUNTED_DR Subledger_Accounted_DT,
               ael.ACCOUNTED_CR Subledger_Accounted_CR,
               null GL_Accounted_DR,
               null GL_Accounted_CR,
              ael.ACCOUNTED_DR ,
              ael.ACCOUNTED_CR,
               hdr.JE_Source,
               hdr.DOC_SEQUENCE_VALUE GL_Doc_Seq_Value,
               glc.user_je_category_name JE_Category,
               fu.USER_NAME JV_Creation_User,
gcc.segment1,gcc.segment2,gcc.segment3, gcc.segment4,gcc.segment5,gcc.segment6,gcc.segment7,gcc.segment8,
               gcc.segment1 || '-' || gcc.segment2 || '-' || gcc.segment3 || '-' ||
               gcc.segment4 || '-' || gcc.segment5 || '-' || gcc.segment6 || '-' ||
               gcc.segment7 || '-' || gcc.segment8 Account_Code_Combination,
               aeh.DOC_SEQUENCE_VALUE Subledger_Doc_Seq_Value,
               (select aia.ATTRIBUTE2
                  from ap_invoices_all aia
                 where aia.INVOICE_ID = ent.source_id_int_1
                   and ent.entity_code = 'AP_INVOICES'
                   and ent.application_id = 200
                   and aia.SET_OF_BOOKS_ID = :Ledger_id) PO_NUMBER,
      (select pv.VENDOR_NAME from ap_invoices_all aia, po_vendors pv

where aia.VENDOR_ID=pv.VENDOR_ID
and aia.INVOICE_ID=ent.source_id_int_1 and ent.entity_code = 'AP_INVOICES'
and ent.application_id =200 and aia.SET_OF_BOOKS_ID=:Ledger_id-- and pv.SET_OF_BOOKS_ID='&Ledger_id'
and ent.ledger_id=:Ledger_id   union
select pv.vendor_name from ap_checks_all aca, po_vendors pv
where aca.CHECK_ID=ent.source_id_int_1
and aca.VENDOR_ID=pv.VENDOR_ID and ent.entity_code = 'AP_PAYMENTS'
and ent.application_id=200 and ent.ledger_id=:Ledger_id) Supplier_Name, --and pv.SET_OF_BOOKS_ID='&Ledger_id'
               
            /*   (select fn.user_name
                  from fnd_user fn, ap_invoices_all ai
                 where ai.CREATED_BY = fn.USER_ID
                   and ai.INVOICE_ID = ent.source_id_int_1
                   and ent.entity_code = 'AP_INVOICES'
                   and ent.application_id = 200
                   and ai.SET_OF_BOOKS_ID = :Ledger_id) APN_USERNAME */

  (select fn.user_name
                  from fnd_user fn, ap_invoices_all ai
                 where ai.CREATED_BY = fn.USER_ID
                   and ai.INVOICE_ID = ent.source_id_int_1
                   and ent.entity_code = 'AP_INVOICES'
                   and ent.application_id = 200
                   and ai.SET_OF_BOOKS_ID = :Ledger_id union
                 select fk.user_name
                  from fnd_user fk, ap_checks_all ac
                 where ac.CREATED_BY = fk.USER_ID
                   and ac.check_ID = ent.source_id_int_1
                   and ent.entity_code = 'AP_PAYMENTS'
                   and ent.application_id = 200
                   and ent.ledger_id = :Ledger_id  ) AP_Creator
                 , NVL(ael.party_id,0) Party_id
          from gl_je_lines                  ln,
               gl_code_combinations         gcc,
               gl_je_headers                hdr,
               gl_import_references         gir,
               xla.xla_transaction_entities ent,
               xla_ae_headers               aeh,
               xla_ae_lines                 ael,
               fnd_user                     fu,
               gl_je_categories             glc
         where 1 = 1
           and hdr.status = 'P'
           and ln.status = 'P'
           and hdr.actual_flag = 'A'
           and ln.je_header_id = hdr.je_header_id
           and ln.period_name = hdr.period_name
           and ln.code_combination_id = gcc.code_combination_id
           and hdr.JE_CATEGORY = glc.je_category_name
           and fu.USER_ID = hdr.CREATED_BY
           and ln.je_header_id = gir.je_header_id
           and ln.je_line_num = gir.je_line_num
           and hdr.je_header_id = gir.je_header_id
           and gir.gl_sl_link_id = ael.gl_sl_link_id
           and gir.gl_sl_link_table = ael.gl_sl_link_table
           and aeh.ae_header_id = ael.ae_header_id
           and aeh.application_id = ael.application_id
           and aeh.period_name = hdr.period_name
           and aeh.entity_id = ent.entity_id
           and hdr.JE_SOURCE not in
               ('Manual', 'Spreadsheet', 'Recurring', 'AutoCopy',
                'Revaluation', 'Closing Journal')
           and ael.LEDGER_ID = :Ledger_id
           and ln.LEDGER_ID = :Ledger_id
   and gcc.SEGMENT2 between NVL(:From_Location,gcc.SEGMENT2) and NVL(:To_Location,gcc.SEGMENT2)
   and  gcc.SEGMENT3 between NVL(:From_Department,gcc.SEGMENT3) and NVL(:To_Department,gcc.SEGMENT3)
   and  gcc.SEGMENT4 between NVL(:From_Account,gcc.SEGMENT4) and NVL(:To_Account,gcc.SEGMENT4)
   and  gcc.SEGMENT5 between NVL(:From_Project,gcc.SEGMENT5) and NVL(:To_Project,gcc.SEGMENT5)
   and  gcc.SEGMENT7 between NVL(:From_Future1,gcc.SEGMENT7) and NVL(:To_Future1,gcc.SEGMENT7)
    and hdr.DEFAULT_EFFECTIVE_DATE between NVL(:From_Date,hdr.DEFAULT_EFFECTIVE_DATE) AND NVL(:To_Date,hdr.DEFAULT_EFFECTIVE_DATE)

     
        union
     
        select
 nvl(ln.ENTERED_DR,0) - nvl(ln.ENTERED_CR,0)  Entered  ,  
       nvl(ln.ACCOUNTED_DR , 0) - nvl( ln.ACCOUNTED_CR,0) Accounted,

hdr.JE_HEADER_ID,
               null,
               hdr.PERIOD_NAME Period_Name,
               hdr.DEFAULT_EFFECTIVE_DATE Effective_date,
               hdr.NAME Journal_Name,
               ln.DESCRIPTION,
               null,
               ln.JE_LINE_NUM,
               null,
               hdr.CURRENCY_CODE Currency,
               ln.ENTERED_DR,
               ln.ENTERED_CR,
               null,
               null,
               ln.ENTERED_DR,
               ln.ENTERED_CR,
               hdr.CURRENCY_CONVERSION_RATE Exch_Rate,
               null,
               null,
               ln.ACCOUNTED_DR,
               ln.ACCOUNTED_CR,
               ln.ACCOUNTED_DR,
               ln.ACCOUNTED_CR,
               hdr.JE_Source,
               hdr.DOC_SEQUENCE_VALUE,
               glc.user_je_category_name JE_Category,
               fu.USER_NAME JV_Creation_User,
gcc.segment1,gcc.segment2,gcc.segment3, gcc.segment4,gcc.segment5,gcc.segment6,gcc.segment7,gcc.segment8,
               gcc.segment1 || '-' || gcc.segment2 || '-' || gcc.segment3 || '-' ||
               gcc.segment4 || '-' || gcc.segment5 || '-' || gcc.segment6 || '-' ||
               gcc.segment7 || '-' || gcc.segment8 Account_Code_Combination,
               null,
               null,
               null,
               null,
               nvl(null,0)
          from gl_je_lines          ln,
               gl_code_combinations gcc,
               gl_je_headers        hdr,
               fnd_user             fu,
               gl_je_categories     glc
         where 1 = 1
           and hdr.status = 'P'
           and ln.status = 'P'
           and hdr.actual_flag = 'A'
           and ln.je_header_id = hdr.je_header_id
           and ln.period_name = hdr.period_name
           and ln.code_combination_id = gcc.code_combination_id
           and hdr.JE_CATEGORY = glc.je_category_name
           and fu.USER_ID = hdr.CREATED_BY
           and hdr.JE_SOURCE in
               ('Manual', 'Spreadsheet', 'Recurring', 'AutoCopy',
                'Revaluation', 'Closing Journal')
           and ln.LEDGER_ID = :Ledger_id
and gcc.SEGMENT2 between NVL(:From_Location,gcc.SEGMENT2) and NVL(:To_Location,gcc.SEGMENT2)
   and  gcc.SEGMENT3 between NVL(:From_Department,gcc.SEGMENT3) and NVL(:To_Department,gcc.SEGMENT3)
   and  gcc.SEGMENT4 between NVL(:From_Account,gcc.SEGMENT4) and NVL(:To_Account,gcc.SEGMENT4)
   and  gcc.SEGMENT5 between NVL(:From_Project,gcc.SEGMENT5) and NVL(:To_Project,gcc.SEGMENT5)
   and  gcc.SEGMENT7 between NVL(:From_Future1,gcc.SEGMENT7) and NVL(:To_Future1,gcc.SEGMENT7)
   and hdr.DEFAULT_EFFECTIVE_DATE between NVL(:From_Date,hdr.DEFAULT_EFFECTIVE_DATE) AND NVL(:To_Date,hdr.DEFAULT_EFFECTIVE_DATE)

        union
     
        select
 nvl(ln.ENTERED_DR,0) - nvl(ln.ENTERED_CR,0)  Entered  ,  
       nvl(ln.ACCOUNTED_DR , 0) - nvl( ln.ACCOUNTED_CR,0) Accounted,

hdr.JE_HEADER_ID,
               null,
               hdr.PERIOD_NAME Period_Name,
               hdr.DEFAULT_EFFECTIVE_DATE Effective_date,
               hdr.NAME Journal_Name,
               ln.DESCRIPTION,
               null,
               ln.JE_LINE_NUM,
               null,
               hdr.CURRENCY_CODE Currency,
               ln.ENTERED_DR,
               ln.ENTERED_CR,
               null,
               null,
               ln.ENTERED_DR,
               ln.ENTERED_CR,
               hdr.CURRENCY_CONVERSION_RATE Exch_Rate,
               null,
               null,
               ln.ACCOUNTED_DR,
               ln.ACCOUNTED_CR,
               ln.ACCOUNTED_DR,
               ln.ACCOUNTED_CR,
               hdr.JE_Source,
               hdr.DOC_SEQUENCE_VALUE,
               glc.user_je_category_name JE_Category,
               fu.USER_NAME JV_Creation_User,
gcc.segment1,gcc.segment2,gcc.segment3, gcc.segment4,gcc.segment5,gcc.segment6,gcc.segment7,gcc.segment8,
               gcc.segment1 || '-' || gcc.segment2 || '-' || gcc.segment3 || '-' ||
               gcc.segment4 || '-' || gcc.segment5 || '-' || gcc.segment6 || '-' ||
               gcc.segment7 || '-' || gcc.segment8 Account_Code_Combination,
               null,
               null,
               null,
               null,
               nvl(null,0)
          from gl_je_lines          ln,
               gl_code_combinations gcc,
               gl_je_headers        hdr,
               fnd_user             fu,
               gl_je_categories     glc
         where 1 = 1
           and hdr.status = 'P'
           and ln.status = 'P'
           and hdr.actual_flag = 'A'
           and ln.je_header_id = hdr.je_header_id
           and ln.period_name = hdr.period_name
           and ln.code_combination_id = gcc.code_combination_id
           and hdr.JE_CATEGORY = glc.je_category_name
           and fu.USER_ID = hdr.CREATED_BY
           and hdr.REVERSED_JE_HEADER_ID is not null
           and hdr.JE_SOURCE not in
               ('Manual', 'Spreadsheet', 'Recurring', 'AutoCopy',
                'Revaluation', 'Closing Journal')
           and ln.LEDGER_ID = :Ledger_id
   and gcc.SEGMENT2 between NVL(:From_Location,gcc.SEGMENT2) and NVL(:To_Location,gcc.SEGMENT2)
   and  gcc.SEGMENT3 between NVL(:From_Department,gcc.SEGMENT3) and NVL(:To_Department,gcc.SEGMENT3)
   and  gcc.SEGMENT4 between NVL(:From_Account,gcc.SEGMENT4) and NVL(:To_Account,gcc.SEGMENT4)
   and  gcc.SEGMENT5 between NVL(:From_Project,gcc.SEGMENT5) and NVL(:To_Project,gcc.SEGMENT5)
   and  gcc.SEGMENT7 between NVL(:From_Future1,gcc.SEGMENT7) and NVL(:To_Future1,gcc.SEGMENT7)
   and hdr.DEFAULT_EFFECTIVE_DATE between NVL(:From_Date,hdr.DEFAULT_EFFECTIVE_DATE) AND NVL(:To_Date,hdr.DEFAULT_EFFECTIVE_DATE)
     
        )
   where 1=1
      and SEGMENT2 between NVL(:From_Location,SEGMENT2) and NVL(:To_Location,SEGMENT2)
  and  SEGMENT3 between NVL(:From_Department,SEGMENT3) and NVL(:To_Department,SEGMENT3)
   and  SEGMENT4 between NVL(:From_Account,SEGMENT4) and NVL(:To_Account,SEGMENT4)
   and SEGMENT5 between NVL(:From_Project,SEGMENT5) and NVL(:To_Project,SEGMENT5)
   and  SEGMENT7 between NVL(:From_Future1,SEGMENT7) and NVL(:To_Future1,SEGMENT7)
   and Effective_date between NVL(:From_Date,Effective_date) AND NVL(:To_Date,Effective_date)
  and PARTY_ID=nvl(:P_Vendor_name,party_id)

order by EFFECTIVE_DATE

--------------------------------------------------------------------------------------------------------------------------

FINAL

select
je_category ,
VENDOR_ID,
  VENDOR_NAME  ,
  PERIOD_NAME,
DEFAULT_EFFECTIVE_DATE ,
nvl((ENTERED_DR),0)  -nvl((ENTERED_CR),0) Entered,
Exch_Rate,
nvl((ACCOUNTED_DR),0)  -nvl((ACCOUNTED_CR),0) Accounted ,
Voucher_num, Currency,
segment2,segment4,LEDGER_ID, Description, PO_NUMBER
from  (

select
ent.source_id_int_1 ,
(
select    s.VENDOR_ID
from     ap_invoices_all  aia , ap_suppliers s
where    aia.INVOICE_ID = ent.source_id_int_1
and      hdr.je_category = 'Purchase Invoices'
and     aia.VENDOR_ID =  s.VENDOR_ID
UNION
select   s.VENDOR_ID
from    ap_checks_all  aca, ap_suppliers s
where   aca.CHECK_ID   = ent.source_id_int_1
and      hdr.je_category  in  ( 'Reconciled Payments' , 'Payments')
and     aca.VENDOR_ID =  s.VENDOR_ID
) VENDOR_ID,

(
select    s.VENDOR_NAME
from     ap_invoices_all  aia , ap_suppliers s
where    aia.INVOICE_ID = ent.source_id_int_1
and      hdr.je_category = 'Purchase Invoices'
and     aia.VENDOR_ID =  s.VENDOR_ID
UNION
select  s.VENDOR_NAME
from    ap_checks_all  aca, ap_suppliers s
where   aca.CHECK_ID   = ent.source_id_int_1
and     hdr.je_category in  ( 'Reconciled Payments' , 'Payments')
and     aca.VENDOR_ID =  s.VENDOR_ID
) VENDOR_NAME,
(
select    aia.ATTRIBUTE2
from     ap_invoices_all  aia , ap_suppliers s
where    aia.INVOICE_ID = ent.source_id_int_1
and      hdr.je_category = 'Purchase Invoices'
and     aia.VENDOR_ID =  s.VENDOR_ID) PO_NUMBER,
hdr.je_category,
aeh.PERIOD_NAME,
ael.ACCOUNTING_DATE  DEFAULT_EFFECTIVE_DATE,
ael.ENTERED_DR,
ael.ENTERED_CR,
 NVL( ael.CURRENCY_CONVERSION_RATE,1) Exch_Rate,
 ael.ACCOUNTED_DR
 , ael.ACCOUNTED_CR
,gir.subledger_doc_sequence_value   Voucher_num,
ael.CURRENCY_CODE Currency,
gcc.segment1,gcc.segment2,gcc.segment3, gcc.segment4,gcc.segment5,gcc.segment6,gcc.segment7,gcc.segment8,
ael.LEDGER_ID,
nvl(ael.DESCRIPTION,ln.DESCRIPTION) Description
from   gl_je_lines ln  , gl_code_combinations  gcc ,gl_je_headers  hdr  ,  gl_import_references gir
        , xla.xla_transaction_entities ent
        , xla_ae_headers aeh
        , xla_ae_lines ael
where /*ln.period_name =  'JUN-16' --:P_PERIOD  --
and  */ hdr.status = 'P'
and   ln.status = 'P'
and    hdr.actual_flag = 'A'
and   ln.je_header_id = hdr.je_header_id
and   ln.period_name = hdr.period_name
and   ln.code_combination_id = gcc.code_combination_id
and ln.LEDGER_ID=hdr.LEDGER_ID
and hdr.LEDGER_ID=aeh.LEDGER_ID
and aeh.LEDGER_ID=ael.LEDGER_ID
and ent.ledger_id=ael.LEDGER_ID
--and  gcc.segment4  =  '248111100'
--and  gcc.SEGMENT1 =  '01' --:P_SEGMENT1 --
--and  gcc.segment1||'.'||gcc.segment2||'.'||gcc.segment3||'.'||gcc.segment4||'.'||gcc.segment5||'.'||gcc.segment6  = '1.01.000.351010.000000.000'
and   hdr.je_source not  in   ('Manual', 'Spreadsheet', 'Recurring', 'AutoCopy',
                'Revaluation', 'Closing Journal')
--and   hdr.je_category =  'Payments'
and   ln.je_header_id = gir.je_header_id
and   ln.je_line_num =  gir.je_line_num
and   hdr.je_header_id =  gir.je_header_id
and   gir.gl_sl_link_id = ael.gl_sl_link_id
and   gir.gl_sl_link_table = ael.gl_sl_link_table
and   aeh.ae_header_id = ael.ae_header_id
and   aeh.application_id = ael.application_id
and   aeh.period_name =  hdr.period_name
and   aeh.entity_id = ent.entity_id




UNION ALL

select
null  source_id_int_1 ,
0  VENDOR_ID ,
'MANUAL ENTRIES'   VENDOR_NAME ,
null PO_NUMBER,
cat.user_je_category_name  je_category,
h.PERIOD_NAME,
h.DEFAULT_EFFECTIVE_DATE ,
ln.ENTERED_DR,
ln.ENTERED_CR,
h.CURRENCY_CONVERSION_RATE Exch_Rate,
LN.ACCOUNTED_DR ,
LN.ACCOUNTED_CR ,
h.DOC_SEQUENCE_VALUE   Voucher_num,
h.CURRENCY_CODE Currency,
gcc.segment1,gcc.segment2,gcc.segment3, gcc.segment4,gcc.segment5,gcc.segment6,gcc.segment7,gcc.segment8,
h.LEDGER_ID,
ln.DESCRIPTION Description

from     gl_je_headers H , GL_JE_LINES LN , gl_code_combinations gcc , gl_je_categories_v cat
where    H.JE_HEADER_ID = LN.JE_HEADER_ID
and      LN.CODE_COMBINATION_ID =  gcc.CODE_COMBINATION_ID
and      h.JE_CATEGORY  =  cat.je_category_name
--and  ln.period_name = 'JUN-16' --:P_PERIOD  --
and   h.status = 'P'
and   ln.status = 'P'
and    h.actual_flag = 'A'
and   ln.period_name = h.period_name
and   ln.code_combination_id = gcc.code_combination_id
and h.LEDGER_ID=ln.LEDGER_ID
--and  gcc.segment4  =  '248111100'
--and  gcc.SEGMENT1 =  '01' --:P_SEGMENT1 --
--and  gcc.segment1||'.'||gcc.segment2||'.'||gcc.segment3||'.'||gcc.segment4||'.'||gcc.segment5||'.'||gcc.segment6  = '1.01.000.351010.000000.000'
and   h.je_source   in   ('Manual', 'Spreadsheet', 'Recurring', 'AutoCopy',
                'Revaluation', 'Closing Journal')


UNION ALL


select
null  source_id_int_1 ,
0  VENDOR_ID ,
'MANUAL ENTRIES'   VENDOR_NAME ,
null PO_NUMBER,
cat.user_je_category_name  je_category,
h.PERIOD_NAME,
h.DEFAULT_EFFECTIVE_DATE ,
ln.ENTERED_DR,
ln.ENTERED_CR,
h.CURRENCY_CONVERSION_RATE Exch_Rate,
LN.ACCOUNTED_DR ,
LN.ACCOUNTED_CR ,
h.DOC_SEQUENCE_VALUE   Voucher_num,
h.CURRENCY_CODE Currency,
gcc.segment1,gcc.segment2,gcc.segment3, gcc.segment4,gcc.segment5,gcc.segment6,gcc.segment7,gcc.segment8,
h.LEDGER_ID,
ln.DESCRIPTION Description

from     gl_je_headers H , GL_JE_LINES LN , gl_code_combinations gcc , gl_je_categories_v cat
where    H.JE_HEADER_ID = LN.JE_HEADER_ID
and      LN.CODE_COMBINATION_ID =  gcc.CODE_COMBINATION_ID
and      h.JE_CATEGORY  =  cat.je_category_name
--and  ln.period_name = 'JUN-16' --:P_PERIOD  --
and   h.status = 'P'
and   ln.status = 'P'
and    h.actual_flag = 'A'
and   ln.period_name = h.period_name
and   ln.code_combination_id = gcc.code_combination_id
and   H.REVERSED_JE_HEADER_ID  is  not  null
--and  gcc.segment4  =  '248111100'
--and  gcc.SEGMENT1 =  '01' --:P_SEGMENT1 --
--and  gcc.segment1||'.'||gcc.segment2||'.'||gcc.segment3||'.'||gcc.segment4||'.'||gcc.segment5||'.'||gcc.segment6  = '1.01.000.351010.000000.000'
and   h.je_source  not  in   ('Manual', 'Spreadsheet', 'Recurring', 'AutoCopy',
                'Revaluation', 'Closing Journal')
and h.LEDGER_ID=ln.LEDGER_ID

)   A
where  trunc( DEFAULT_EFFECTIVE_DATE)   between   nvl( '&P_FROM_DATE' , trunc( DEFAULT_EFFECTIVE_DATE)  )    and   nvl( '&P_TO_DATE ', trunc( DEFAULT_EFFECTIVE_DATE)  )
    and SEGMENT2 between NVL('&From_Location',SEGMENT2) and NVL('&To_Location',SEGMENT2)
  and  SEGMENT3 between NVL('&From_Department',SEGMENT3) and NVL('&To_Department',SEGMENT3)
   and  SEGMENT4 between NVL('&From_Account',SEGMENT4) and NVL('&To_Account',SEGMENT4)
   and SEGMENT5 between NVL('&From_Project',SEGMENT5) and NVL('&To_Project',SEGMENT5)
   and  SEGMENT7 between NVL('&From_Future1',SEGMENT7) and NVL('&To_Future1',SEGMENT7)
   and ledger_id = '&LEDGER_ID'

Category wise Min and Max Document Sequence Values PLSQL Query- Oracle EBS R12

http://generalledger-gl.blogspot.com/2016/06/select-glc.html

Category wise Min and Max Document Sequence Values PLSQL Query

select glc.user_je_category_name, min(gjh.DOC_SEQUENCE_VALUE) from gl_je_headers gjh, gl_je_categories glc
where glc.je_category_name=gjh.JE_CATEGORY --151763
and gjh.DEFAULT_EFFECTIVE_DATE between '01-JUL-2015' and '31-MAY-2016'
and gjh.ACTUAL_FLAG='A'
and gjh.LEDGER_ID=20
group by glc.user_je_category_name, min(gjh.DOC_SEQUENCE_VALUE)

------------------

Main Query

select glc.user_je_category_name,min(gjh.DOC_SEQUENCE_VALUE),max(gjh.DOC_SEQUENCE_VALUE)
from gl_je_headers gjh, gl_je_categories glc
where 1=1
and glc.je_category_name=gjh.JE_CATEGORY
and gjh.DEFAULT_EFFECTIVE_DATE between '01-JUL-2015' and '31-MAY-2016'
and gjh.ACTUAL_FLAG='A'
and gjh.LEDGER_ID=20
group by glc.user_je_category_name

-------------------

select gjh.DOC_SEQUENCE_VALUE
from gl_je_headers gjh, gl_je_categories glc
where 1=1
and glc.je_category_name=gjh.JE_CATEGORY
and gjh.DEFAULT_EFFECTIVE_DATE between '01-JUL-2015' and '31-MAY-2016'
and gjh.ACTUAL_FLAG='A'
and gjh.LEDGER_ID=20
and glc.user_je_category_name='Purchase Invoices'
order by gjh.DOC_SEQUENCE_VALUE

GL Chart of Accounts Complete Query - Oracle EBS R12

http://generalledger-gl.blogspot.com/2016/06/gl-chart-of-accounts-complete-query.html

GL Chart of Accounts Complete Query

select *  from  gl_ledgers

select  gl_flexfields_pkg.get_coa_name  (50308)  from   dual

--SELECT *  FROM  FND_ID_FLEXS FF WHERE FF.ID_FLEX_NAME  = 'Accounting Flexfield'

--FLEX Structure

SELECT fs.Id_Flex_Code,FS.ID_FLEX_NUM , FS.ID_FLEX_STRUCTURE_CODE

,FS.CROSS_SEGMENT_VALIDATION_FLAG ,

 FS.ID_FLEX_STRUCTURE_NAME

 FROM FND_ID_FLEX_STRUCTURES_VL FS WHERE FS.ID_FLEX_CODE = 'GL#'

 --SEGMENTS

SELECT SGMNT.ID_FLEX_CODE , SGMNT.ID_FLEX_NUM , SGMNT.APPLICATION_COLUMN_NAME , SGMNT.SEGMENT_NAME ,

SGMNT.SEGMENT_NUM , SGMNT.FLEX_VALUE_SET_ID

 FROM  FND_ID_FLEX_SEGMENTS_VL SGMNT 

 WHERE SGMNT.ID_FLEX_CODE =  'GL#' 

AND  SGMNT.ID_FLEX_NUM = 503

--FLEXFIELD qUALIFIERS

SELECT ffqUAL.Id_Flex_Code , ffqUAL.Id_Flex_Num , ffqUAL.Application_Column_Name , ffqUAL.Segment_Attribute_Type,

ffqUAL.Attribute_Value

FROM FND_SEGMENT_ATTRIBUTE_VALUES ffqUAL   WHERE ID_FLEX_CODE =  'GL#'  AND ID_FLEX_NUM = 50308

AND ffqUAL.Attribute_Value = 'Y'

--Value Sets

SELECT  SGMNT.SEGMENT_NAME ,

SGMNT.SEGMENT_NUM  , vs.flex_value_set_id , vs.flex_value_set_name  

,vs.description

 FROM  FND_ID_FLEX_SEGMENTS_VL SGMNT , FND_FLEX_VALUE_SETS vs

 WHERE SGMNT.ID_FLEX_CODE =  'GL#' 

AND  SGMNT.ID_FLEX_NUM = 503

and    SGMNT.Flex_Value_Set_Id = vs.flex_value_set_id

-- Value Set Values

SELECT   vsvls.flex_value , vsvls.description ,vsvls.ENABLED_FLAG , vsvls.SUMMARY_FLAG , 

vsvls.COMPILED_VALUE_ATTRIBUTES

 FROM  FND_ID_FLEX_SEGMENTS_VL SGMNT , FND_FLEX_VALUES_VL  vsvls

 WHERE SGMNT.ID_FLEX_CODE =  'GL#' 

AND  SGMNT.ID_FLEX_NUM = 503

and  SGMNT.FLEX_VALUE_SET_ID = vsvls.flex_value_set_id

and  SGMNT.SEGMENT_NUM = 4