Account Analysis Report Internal

SELECT  Distinct JH.DESCRIPTION,
         jl.description Line_Desc,
         SUBSTR (jc.user_je_category_name, 1, 26) CATEGORY,
         SUBSTR (jh.je_source, 1, 15) SOURCE,
         TO_CHAR (jl.effective_date, 'DD-MON-YY') Gl_Date,
         jh.NAME Journal_Name,
--         jh.je_batch_id batch,
         SUBSTR (ds.NAME, 1, 13) lsequence,
         jh.doc_sequence_value hnumber,
         decode(xal.ACCOUNTED_DR,null,jl.accounted_dr,xal.ACCOUNTED_DR ) Acc_Dr ,
         decode(xal.ACCOUNTED_CR,null,jl.accounted_Cr,xal.ACCOUNTED_CR ) Acc_Cr,
         decode(xal.ENTERED_DR,null,jl.ENTERED_dr,xal.ENTERED_DR ) Ent_Dr,
         decode(xal.ENTERED_CR,null,jl.ENTERED_Cr,xal.ENTERED_CR ) Ent_Cr,
         jh.external_reference REFERENCE,
         jl.period_name Period_name,
         ir.subledger_doc_sequence_id seq_id,
 ds.NAME Voucher_Category,
         DECODE (ir.subledger_doc_sequence_value ,NULL,JH.DOC_SEQUENCE_VALUE,ir.subledger_doc_sequence_value ) Subled_Seq_No,
--         jh.doc_sequence_id h_seq_id,
         DECODE (xah.DOC_SEQUENCE_VALUE,NULL,JH.DOC_SEQUENCE_VALUE,xah.DOC_SEQUENCE_VALUE) DOC_SEQUENCE_VALUE,
         xal.PARTY_ID,
         xal.code_combination_id,
         gk.concatenated_segments Acct_Code,
         gk.segment6 Natural_Acct,
         gk.segment1,
         gk.segment3,
         gk.segment5,
         gk.segment7,
         decode(fu.DESCRIPTION,null ,fu.USER_NAME,fu.DESCRIPTION)  user_name  ,
         apsu.VENDOR_NAME,
         apsu.SEGMENT1 Vendor_code,
         xal.CURRENCY_CODE Currency,
         xal.CURRENCY_CONVERSION_RATE Rate,
         arc.CUSTOMER_NAME,
arc.CUSTOMER_NUMBER,
         xlate.SOURCE_ID_INT_1 Invoice_ID,
         xlate.ENTITY_CODE,
         xlate.TRANSACTION_NUMBER--,  b.daily_balance
       , ir.gl_sl_link_id
FROM     gl_je_lines jl,
        gl_je_headers jh,
        gl_je_batches jb,
        gl_je_sources js,
        gl_je_categories jc,
        gl_import_references ir,
        XLA_AE_HEADERS XAH,
        XLA_AE_LINES XAL,
        ap_suppliers apsu,
        ar_customers arc,
        fnd_document_sequences ds,
        xla_events xlae,
        xla_transaction_entities xlate,
        fnd_user fu,
        gl_code_combinations_kfv gk
WHERE  jl.status = 'P'
--    AND jl.code_combination_id = :P_ACCT
    and   xal.code_combination_id= gk.code_combination_id
    and   jh.created_by=fu.user_id
    AND jl.ledger_id = :p_set_of_bks_id
    AND jh.status = 'P'
    AND jh.actual_flag = 'A'
    AND jh.je_header_id = jl.je_header_id
--   &C_WHERE_CURR
    AND jb.je_batch_id = jh.je_batch_id
    AND jb.average_journal_flag = 'N'
    AND js.je_source_name = jh.je_source
    AND jc.je_category_name = jh.je_category
    AND ir.je_header_id(+) = jl.je_header_id
    AND ir.je_line_num(+) = jl.je_line_num
    AND ds.doc_sequence_id(+) = ir.subledger_doc_sequence_id
    and XAH.AE_HEADER_ID(+)=XAL.AE_HEADER_ID
    AND XAL.GL_SL_LINK_ID(+)=IR.GL_SL_LINK_ID
    and arc.CUSTOMER_ID(+)=xal.PARTY_ID
    AND XAL.GL_SL_LINK_TABLE(+)=IR.GL_SL_LINK_TABLE
    and xah.EVENT_ID=xlae.EVENT_ID(+)
    and xlae.ENTITY_ID=xlate.ENTITY_ID(+)
    and apsu.VENDOR_ID(+)=xal.PARTY_ID
   -- AND jl.period_name in(:P_START_PERIOD,:P_END_PERIOD)
 and jh.DEFAULT_EFFECTIVE_DATE between :p_from_date  and :p_to_date
    and   gk.segment1 = nvl(:P_Comp,gk.segment1)
    and   gk.segment3 = nvl(:P_Branch,gk.segment3)
    and   gk.segment5 = nvl(:P_Prod,gk.segment5)
    and   gk.segment7=nvl(:P_Acct,gk.segment7)
    and   gk.segment6= nvl(:P_Dept,gk.segment6)
--and jh.je_header_id=116193
--    &WHERE_FLEX
UNION ALL
 select  JH.DESCRIPTION,
         jl.description Line_Desc,
         SUBSTR (jc.user_je_category_name, 1, 26) CATEGORY,
         SUBSTR (jh.je_source, 1, 15) SOURCE,
         TO_CHAR (jl.effective_date, 'DD-MON-YY') Gl_Date,
         jh.NAME Journal_Name,
--         jh.je_batch_id batch,
         Null lsequence,
         jh.doc_sequence_value hnumber,
         jl.accounted_dr Acc_Dr ,
         jl.accounted_Cr Acc_Cr,
         jl.ENTERED_dr Ent_Dr,
         jl.ENTERED_Cr Ent_Cr,
         jh.external_reference REFERENCE,
         jl.period_name Period_name,
         jr.subledger_doc_sequence_id seq_id,
null   Voucher_Category,
         DECODE (jr.subledger_doc_sequence_value ,NULL,JH.DOC_SEQUENCE_VALUE,jr.subledger_doc_sequence_value ) Subled_Seq_No,
 --        jh.doc_sequence_id h_seq_id,
         JH.DOC_SEQUENCE_VALUE DOC_SEQUENCE_VALUE,
         Null PARTY_ID,
         jl.code_combination_id,
         gk.concatenated_segments Acct_Code,
         gk.segment6 Natural_Acct,
         gk.segment1,
         gk.segment3,
         gk.segment5,
         gk.segment7,
         fu.description,
         Null VENDOR_NAME,
null vendor_code,
         jh.CURRENCY_CODE Currency,
         jh.CURRENCY_CONVERSION_RATE Rate,
         Null CUSTOMER_NAME,
null customer_number,
         Null Invoice_ID,
         Null ENTITY_CODE,
         Null TRANSACTION_NUMBER,
         jr.gl_sl_link_id
from  gl_je_headers jh,
      gl_je_lines jl,
      gl_je_batches jb,
      gl_je_sources js ,
      gl_je_categories jc,
      gl_import_references jr,
      gl_code_combinations_kfv gk,
      fnd_user fu
WHERE js.JE_SOURCE_NAME IN('Manual','Spreadsheet','Purchasing India')
and   jl.JE_HEADER_ID=jh.JE_HEADER_ID
and   jh.JE_BATCH_ID=jb.JE_BATCH_ID(+)
and   jh.JE_SOURCE(+)=js.JE_SOURCE_NAME
and   jh.JE_CATEGORY=jc.JE_CATEGORY_NAME(+)
and   jr.JE_HEADER_ID(+)=jl.JE_HEADER_ID
and   jr.JE_LINE_NUM(+)=jl.JE_LINE_NUM
and   jl.CODE_COMBINATION_ID=gk.CODE_COMBINATION_ID
and   jh.CREATED_BY=fu.USER_ID
--and   jh.PERIOD_NAME in(:P_START_PERIOD,:P_END_PERIOD)
 and jh.DEFAULT_EFFECTIVE_DATE between :p_from_date  and :p_to_date
and   jh.STATUS='P'
and   jl.STATUS='P'
--and   jh.ACTUAL_FLAG='Y'
AND   jl.ledger_id = :p_set_of_bks_id
AND   jb.average_journal_flag = 'N'
and   gk.segment1 = nvl(:P_Comp,gk.segment1)
and   gk.segment3 = nvl(:P_Branch,gk.segment3)
and   gk.segment5 = nvl(:P_Prod,gk.segment5)
and   gk.segment7=nvl(:P_Acct,gk.segment7)
and   gk.segment6= nvl(:P_Dept,gk.segment6)
--and jh.je_header_id=116193
--  &WHERE_FLEX
ORDER BY 5