GL TO AP QUERY

select * from
(select (select vendor_name from ap_suppliers where vendor_id=b.vendor_id) supplier,
b.invoice_id,b.INVOICE_NUM,b.voucher_no,b.SOURCE,b.INVOICE_TYPE_LOOKUP_CODE type,a.APPLIED_TO_SOURCE_ID_NUM_1,a.ACCOUNT_CODE,
--a.JE_CATEGORY_NAME,
nvl(sum(a.DR),0)-nvl(sum(a.CR),0) diff,
sum(a.DR) dr,sum(a.CR) cr
from
(
select  xah.ae_header_id,xal.ae_line_num,xdl.APPLIED_TO_SOURCE_ID_NUM_1,gcc.segment1||'.'||gcc.segment2||'.'||gcc.segment3||'.'||gcc.segment4 account_Code,xah.JE_CATEGORY_NAME,xal.ACCOUNTED_DR,xal.ACCOUNTED_cr,
sum(xdl.UNROUNDED_ACCOUNTED_DR) dr,sum(xdl.UNROUNDED_ACCOUNTED_CR) cr--,sum(xal.ACCOUNTED_DR),sum(xal.ACCOUNTED_cr)
from gl_import_references gir,xla_ae_lines xal,xla_ae_headers xah,gl_je_headers gjh,gl_je_lines gjl,gl_code_combinations gcc,
xla_distribution_links xdl--,
--ap_invoices_All aia
--xla.xla_events xev,
--xla.xla_transaction_entities XTE
where
--je_header_id=415326 and je_line_num=3
--and
 gir.GL_SL_LINK_ID=xal.GL_SL_LINK_ID
AND
gir.GL_SL_LINK_TABLE = xal.GL_SL_LINK_TABLE
AND XAH.ae_header_id = XAL.ae_header_id
AND XAH.gl_transfer_status_code= 'Y'
and trunc(gjl.effective_date) <= :to_date
and xal.code_combination_id in (SELECT code_Combination_id from gl_Code_combinations where segment4=:account_code)-- (26332,26347,48810,76059,77278,164197)
and gir.je_header_id=gjh.je_header_id
and gjh.je_header_id=gjl.je_header_id
and gir.je_header_id=gjl.je_header_id
and gir.je_line_num=gjl.je_line_num
and gjl.code_combination_id=gcc.code_combination_id
--AND xah.event_id= xev.event_id
--AND xev.entity_id= xte.entity_id
--AND xah.entity_id= xte.entity_id
and xah.ae_header_id=xdl.ae_header_id
and xal.ae_line_num=xdl.ae_line_num
and xal.ae_header_id=xdl.ae_header_id
--and aia.INVOICE_ID = xte.source_id_int_1(+)
AND gjh.STATUS='P'
AND gjh.Actual_flag='A'
AND XAH.gl_transfer_status_code= 'Y'
--and xdl.APPLIED_TO_SOURCE_ID_NUM_1=aia.invoice_id
group by
xah.ae_header_id,xal.ae_line_num,xdl.APPLIED_TO_SOURCE_ID_NUM_1,gcc.segment1,gcc.segment2,gcc.segment3,gcc.segment4,xah.JE_CATEGORY_NAME,xal.ACCOUNTED_DR,xal.ACCOUNTED_cr
) a,
(select aia.invoice_id,aia.invoice_num,aia.vendor_id,aia.source,aia.invoice_type_lookup_code,aia.DOC_SEQUENCE_VALUE voucher_no from ap_invoices_all aia) b
where
a.APPLIED_TO_SOURCE_ID_NUM_1=b.invoice_id(+)
group by
b.invoice_id,b.INVOICE_NUM,b.vendor_id,b.voucher_no,b.SOURCE,b.INVOICE_TYPE_LOOKUP_CODE,a.APPLIED_TO_SOURCE_ID_NUM_1,a.ACCOUNT_CODE--,a.JE_CATEGORY_NAME
union all
select
null supplier_name,null invoice_id,null INVOICE_NUM,null voucher_no,glh.je_source SOURCE,null  type,null APPLIED_TO_SOURCE_ID_NUM_1,
gcc.segment1||'.'||gcc.segment2||'.'||gcc.segment3||'.'||gcc.segment4 ACCOUNT_CODE,--glh.je_Category JE_CATEGORY_NAME,
nvl(sum(gjl.accounted_dr),0)-nvl(sum(gjl.accounted_cr),0) diff,
sum(gjl.accounted_dr) dr ,
sum(gjl.accounted_cr) CR
from gl_je_headers glh,gl_je_lines gjl,gl_code_combinations gcc  
where
glh.je_header_id=gjl.je_header_id
and
 trunc(gjl.effective_date) <= :to_date
and gjl.code_combination_id in (SELECT code_Combination_id from gl_Code_combinations where segment4=:account_code)
and glh.JE_SOURCE in ('Spreadsheet','Manual')
and gjl.code_Combination_id=gcc.code_combination_id
group by
glh.je_source,--glh.je_Category,
gcc.segment1,gcc.segment2,gcc.segment3,gcc.segment4
)
where
diff<>0
order by
applied_to_source_id_num_1