AP TO GL AND AR TO GL Reconsolation Query

SELECT distinct ps.period_name,
gjl.reference_2, 
TO_CHAR (NULL) po_order_number,
       TO_CHAR (NULL) "Receipt Number",
       TO_DATE (NULL) "Receipt Date",
       TO_CHAR (NULL) "Invoice Number",
       TO_DATE (NULL) "Invoice Date",
       TO_CHAR (NULL) "Vendor Name",
       TO_CHAR (NULL) "Vendor Number",
       je_source "Source",
       gjh.ledger_id "set_of_books_id" ,
        fnd_flex_ext.get_segs('SQLGL','GL#',gscv.chart_of_accounts_id, gscv.code_combination_id) Segment,
        fnd_flex_ext.get_segs('SQLGL','GL#',gscv.chart_of_accounts_id, gscv.code_combination_id) Segment2,
         gjh.currency_code,
       gjl.entered_dr ,                                                                            
        gjl.entered_cr,   
      TO_NUMBER(DECODE ( lr.relationship_type_code, 'BALANCE', NULL, gjl.accounted_dr )) accounted_dr,                     
    TO_NUMBER(DECODE ( lr.relationship_type_code, 'BALANCE', NULL, gjl.accounted_cr )) accounted_cr,
    gjh.JE_HEADER_ID,
      gjl.je_line_num,
    gjl.code_combination_id ,
       gjh.DOC_SEQUENCE_VALUE "Vocher Number",
    gjh.default_effective_date gl_date,  
      gjl.attribute1  "Staff Number",
   gjl.attribute2 "Project",
      gjl.attribute3  "Customer Num/Name" ,
    gjl.DESCRIPTION description,
     null attribute_category,
null project_name,
null task_name,
null expenditure_item_date,
null expenditure_type,
null expenditure_org,
null customer_name,
null bpo_project_name,
null staff_num
  FROM gl_je_lines gjl , gl_je_headers gjh, gl_summary_combinations_v gscv, gl_ledgers,gl_period_statuses ps,gl_je_batches b,
  gl_ledger_relationships lr
 WHERE gscv.code_combination_id =
                                gjl.code_combination_id
   AND gjh.je_header_id=gjl.je_header_id
   AND gjl.period_name       = ps.period_name
   AND ps.ledger_id            = gjl.ledger_id
   AND b.je_batch_id           = gjh.je_batch_id
   AND gjh.ledger_id             = lr.source_ledger_id
   AND lr.source_ledger_id = lr.target_ledger_id
    AND b.actual_flag = 'A'
   AND b.status || '' = 'P'
   AND gjh.currency_code != 'STAT'
  AND je_source NOT IN ('Payables','Cost Management','Consolidation','Receivables')  
   AND gjh.ledger_id = gl_ledgers.ledger_id
 ---  AND gl_ledgers.suspense_allowed_flag = 'P'  
  AND (    NVL (ps.effective_period_num, 0) >= :cf_period_from
        AND (NVL (ps.effective_period_num, 0) <= :cf_period_to)
       )
     AND (    NVL (gscv.segment5, 0) >= :p_account_from
        AND (NVL (gscv.segment5, 0) <= :p_account_to)
       )
     AND (    NVL (gscv.segment1, 0) >=:P_COMPANY_FROM
        AND (NVL (gscv.segment1, 0) <= :P_COMPANY_TO)
       )         
and gjh.Ledger_ID not in ( 1014,1015,1016 )  
AND gjh.currency_code =decode(:p_currency_code,'INR','INR',
                                              'ALL',gjh.currency_code,
                                              'NON INR',decode(gjh.currency_code,'INR','###',gjh.currency_code))

UNION ALL

          
SELECT distinct b.period_name, d.reference_2, aaa.po_order_number, aaa.rcv_receipt_num "Receipt Number",
       aaa.trx_date "Receipt Date", aaa.trx_number_displayed "Invoice Number",
       aaa.accounting_date "Invoice Date", aaa.third_party_name "Vendor Name",
       aaa.third_party_number "Vendor Number", b.je_source "Source",  b.ledger_id "set_of_books_id" ,
        fnd_flex_ext.get_segs('SQLGL','GL#',ccc.chart_of_accounts_id, ccc.code_combination_id) Segment,
        fnd_flex_ext.get_segs('SQLGL','GL#',ccc.chart_of_accounts_id, ccc.code_combination_id)  segment2,
       aaa.currency_code, aaa.entered_dr, aaa.entered_cr, aaa.accounted_dr,
       aaa.accounted_cr, aaa.je_header_id, aaa.je_line_num, aaa.code_combination_id,
       aaa.doc_sequence_value "Vocher Number", aaa.gl_date "GL_Date",
       aaa.attribute1 "Staff Number", aaa.attribute2 "Project",
       aaa.attribute3 "Customer Num/Name", aaa.description "Description" ,null attribute_category,
null project_name,
null task_name,
null expenditure_item_date,
null expenditure_type,
null expenditure_org,
null customer_name,
null bpo_project_name,
null staff_num
  FROM (SELECT distinct poh.segment1 po_order_number, rsh.receipt_num rcv_receipt_num,
               rct.transaction_date trx_date, api.invoice_num trx_number_displayed,
               rrs.accounting_date accounting_date,
               pov.vendor_name third_party_name,
               pov.segment1 third_party_number, rrs.currency_code,
               rrs.entered_dr, rrs.entered_cr, rrs.accounted_dr,
               rrs.accounted_cr, r.je_header_id, r.je_line_num,
               rrs.code_combination_id, 201 application_id,
               TO_DATE (NULL) gl_date, TO_NUMBER (NULL) doc_sequence_value,
               TO_CHAR (NULL) description, TO_CHAR (NULL) attribute1,
               TO_CHAR (NULL) attribute2, TO_CHAR (NULL) attribute3
               --poh.org_id
          FROM   po_headers_all poh, po_lines_all pl, po_distributions_all pod
, rcv_shipment_lines rsl, rcv_shipment_headers rsh, rcv_transactions rct, rcv_receiving_sub_ledger rrs
, ap_invoice_distributions_all aida,ap_invoices_all api , xla_ae_headers xah , xla_ae_lines xal
,gl_import_references r
,ap_suppliers POV
     WHERE  1=1
AND poh.po_header_id=pl.po_header_id
AND poh.po_header_id=pod.po_header_id
AND poh.po_header_id= rsl.po_header_id(+)
AND rsl.shipment_header_id=rsh.shipment_header_id(+)
AND rsh.shipment_header_id=rct.shipment_header_id(+)
AND pod.po_distribution_id= aida.po_distribution_id(+)
AND api.invoice_id(+)=aida.invoice_id
AND pod.po_distribution_id = rrs.reference3
AND rct.transaction_id = rrs.rcv_transaction_id
AND aida.accounting_event_id=xah.event_id(+)
AND xah.ae_header_id=xal.ae_header_id(+)
AND xal.gl_sl_link_id= r.gl_sl_link_id(+)
and r.reference_8=xal.ae_line_num
AND poh.vendor_id=pov.vendor_id
AND aida.line_type_lookup_code(+)='ACCRUAL'
AND rrs.accounting_line_type='Accrual'
AND xal.accounting_class_code(+)='LIABILITY'
AND pod.destination_type_code='EXPENSE'
           and exists (select 1
                 from ap_invoice_distributions_all aid,ap_invoices_all ai
        where aid.po_distribution_id=pod.po_distribution_id
        AND ai.invoice_id = aid.invoice_id
                       AND ai.vendor_id = pov.vendor_id) )           aaa,
       gl_je_headers  b, gl_je_lines d,gl_je_batches e, gl_Period_statuses ps,
       gl_summary_combinations_v         ccc
 WHERE b.je_header_id = aaa.je_header_id
   AND d.je_line_num = aaa.je_line_num
   AND b.je_header_id=d.je_header_id
   AND d.code_combination_id = ccc.code_combination_id
   AND aaa.code_combination_id = d.code_combination_id
   AND ps.ledger_id            = d.ledger_id
   AND b.actual_flag = 'A'
   AND b.status || '' = 'P'
   AND b.currency_code != 'STAT'
     AND je_source NOT IN ('Payables', 'Purchasing','Consolidation') 
  --AND     aaa.org_id=:P_ORG
  AND (    NVL (ps.effective_period_num, 0) >= :cf_period_from
        AND (NVL (ps.effective_period_num, 0) <= :cf_period_to)
       )
      AND (    NVL (ccc.segment5, 0) >= :p_account_from
        AND (NVL (ccc.segment5, 0) <= :p_account_to)
       )
      
     AND (    NVL (ccc.segment1, 0) >=:P_COMPANY_FROM
        AND (NVL (ccc.segment1, 0) <= :P_COMPANY_TO)
       )        
and  b.ledger_ID not in ( 1014,1015,1016 )
AND b.currency_code  =decode(:p_currency_code,'INR','INR',
                                              'ALL',b.currency_code,
                                              'NON INR',decode(b.currency_code,'INR','###',b.currency_code))

UNION ALL

--PO-RECEIPT-GL
select  gps.period_name,
        l.reference_2,
        pha.segment1 po_order_number,
        rsh.receipt_num "Receipt Number",
        rt.transaction_date  "Receipt Date",
        null   "Invoice Number",
        null   "Invoice Date",
        pv.vendor_name "Vendor Name",
        pv.segment1 "Vendor Number",
        h.je_source "Source",
        h.ledger_id "set_of_books_id" ,
        gcc.concatenated_segments Segment,
        gcc.concatenated_segments segment2,
        pha.currency_code,
--       pla.line_num,
--       plla.shipment_num,
--       pla.item_description,
--       pla.unit_price,
--       plla.quantity,
--       plla.quantity_received,
--       plla.quantity_billed,
--       rt.transaction_type,
       --rt.transaction_date,
       xal.entered_dr,
       xal.entered_cr,
       xal.accounted_dr,
       xal.accounted_cr,
       gir.je_header_id,
       gir.je_line_num,
       rrsl.code_combination_id,
       --gps.application_id,
       --
       TO_NUMBER (NULL) "Vocher Number",
TO_DATE (NULL) gl_date,      
                TO_CHAR (NULL)  "Staff Number",
               TO_CHAR (NULL) "Project",
               TO_CHAR (NULL) "Customer Num/Name" ,
               --pha.org_id,
--               TO_CHAR (NULL) description
               rsl.item_description "Description",null attribute_category,
null project_name,
null task_name,
null expenditure_item_date,
null expenditure_type,
null expenditure_org,
null customer_name,
null bpo_project_name,
null staff_num
  from gl.gl_je_headers            h,
       gl.gl_je_lines                l,
       gl_code_combinations_kfv     gcc,
       gl.GL_IMPORT_REFERENCES     gir,
       apps.xla_ae_lines             xal,
       apps.XLA_DISTRIBUTION_LINKS   xdl,
       po.RCV_RECEIVING_SUB_LEDGER rrsl,
       po.rcv_transactions rt,
       po.po_headers_all pha,
       po.po_lines_all pla,
       po.po_line_locations_all plla,
       apps.po_vendors pv,
       rcv_shipment_headers rsh,
       rcv_shipment_lines rsl,
       gl_period_statuses gps
 where 1=1
 --and pha.org_id=:P_ORG
 and h.je_header_id = l.je_header_id
   and l.code_combination_id = gcc.code_combination_id
   --and gcc.segment1 = '111'
     AND (    NVL (gcc.segment5, 0) >=:p_account_from
        AND (NVL (gcc.segment5, 0) <= :p_account_to)
       ) 
      
     AND (    NVL (gcc.segment1, 0) >=:P_COMPANY_FROM
        AND (NVL (gcc.segment1, 0) <= :P_COMPANY_TO)
       )         
   --and gcc.segment2 in ('21200101', '21200201')
   and l.je_header_id = gir.je_header_id
   and l.je_line_num = gir.je_line_num
   and gir.gl_sl_link_id = xal.gl_sl_link_id
   and xal.ae_header_id = xdl.ae_header_id
   and xal.ae_line_num = xdl.ae_line_num
   and xdl.source_distribution_id_num_1 = rrsl.rcv_sub_ledger_id
   and rrsl.rcv_transaction_id = rt.transaction_id
   and rt.po_header_id = pha.po_header_id
   and rt.po_header_id = pla.po_header_id
   and rt.po_line_id = pla.po_line_id
   and pla.po_header_id = plla.po_header_id
   and pla.po_line_id = plla.po_line_id
   and pha.vendor_id = pv.vendor_id
   --and h.ledger_id = 2041--UTSC
   and h.je_source = 'Cost Management'
   and rt.shipment_header_id=rsh.shipment_header_id
   and rsh.shipment_header_id=rsl.shipment_header_id
   and rt.shipment_line_id  =rsl.shipment_line_id
   and l.period_name       = gps.period_name
   and gps.ledger_id            = l.ledger_id
   and gps.application_id       = 101 
  AND (    NVL (gps.effective_period_num, 0) >=:cf_period_from
        AND (NVL (gps.effective_period_num, 0) <=:cf_period_to)
             )   
  
   --and h.je_category = 'Receiving'
   --and h.status = 'P'
   --and h.period_name in ('MAY-12')
and h.ledger_ID not in ( 1014,1015,1016 ) 
AND   h.currency_code != 'STAT'
AND   h.currency_code =decode(:p_currency_code,'INR','INR',
                                              'ALL',h.currency_code,
                                              'NON INR',decode(h.currency_code,'INR','###',h.currency_code))

union all

select 
gps.period_name,
gjl.reference_2,
(select  distinct pha.segment1  from ap_invoice_lines_all ailla, po_headers_all pha where ailla.invoice_id=aia.invoice_id and ailla.po_header_id=pha.po_header_id) po_order_number,

(select distinct rsh.receipt_num
from rcv_shipment_headers rsh, rcv_shipment_lines rsl,rcv_transactions rt, ap_invoice_lines_all aila
where rsh.shipment_header_id=rsl.shipment_header_id
and rsl.shipment_header_id=rt.shipment_header_id
and rsl.shipment_line_id=rt.shipment_line_id
and rt.transaction_id=(select distinct rcv_transaction_id from ap_invoice_lines_all aila where aila.invoice_id=aia.invoice_id and rownum=1 and rcv_transaction_id is not null) ) "Receipt Number",

(select distinct rt.transaction_date
from rcv_shipment_headers rsh, rcv_shipment_lines rsl,rcv_transactions rt, ap_invoice_lines_all aila
where rsh.shipment_header_id=rsl.shipment_header_id
and rsl.shipment_header_id=rt.shipment_header_id
and rsl.shipment_line_id=rt.shipment_line_id
and rt.transaction_id=(select distinct rcv_transaction_id from ap_invoice_lines_all aila where aila.invoice_id=aia.invoice_id and rownum=1 and rcv_transaction_id is not null)) "Receipt Date",

        aia.invoice_num   "Invoice Number",
        aia.gl_date   "Invoice Date",
        aps.vendor_name "Vendor Name",
        aps.segment1 "Vendor Number",
        gjh.je_source "Source",
        gjh.ledger_id "set_of_books_id" ,
        cc.concatenated_segments Segment,
        cc.concatenated_segments segment2,
        aia.payment_currency_code currency_code,
       xal.entered_dr,
       xal.entered_cr,
       xal.accounted_dr,
       xal.accounted_cr,
       gir.je_header_id,
       gir.je_line_num,
       cc.code_combination_id,
       --gps.application_id,
       --
       aia.doc_sequence_value "Vocher Number"
          ,aia.gl_date "GL_Date"
          ,NULL "Staff Number"
          ,null "Project"
          ,null "Customer Num/Name"
          ,xal.description "Description",
         
          (SELECT distinct  aida.attribute_category FROM ap_invoice_distributions_all aida,xla_distribution_links xdl
where aida.invoice_id=aia.invoice_id
and aida.invoice_distribution_id=xdl.source_distribution_id_num_1
and xdl.ae_header_id=xal.ae_header_id
and xdl.ae_line_num=xal.ae_line_num

) attribute_category,

(SELECT distinct  ppa.name FROM ap_invoice_distributions_all aida,xla_distribution_links xdl,pa_projects_all ppa
where aida.invoice_id=aia.invoice_id
and aida.invoice_distribution_id=xdl.source_distribution_id_num_1
and xdl.ae_header_id=xal.ae_header_id
and xdl.ae_line_num=xal.ae_line_num
and aida.project_id=ppa.project_id
) project_name,

(SELECT distinct  pt.task_name FROM ap_invoice_distributions_all aida,xla_distribution_links xdl,pa_tasks pt
where aida.invoice_id=aia.invoice_id
and aida.invoice_distribution_id=xdl.source_distribution_id_num_1
and xdl.ae_header_id=xal.ae_header_id
and xdl.ae_line_num=xal.ae_line_num
and aida.project_id=pt.project_id
and aida.task_id=pt.task_id
) task_name,
(SELECT distinct  aida.expenditure_item_date FROM ap_invoice_distributions_all aida,xla_distribution_links xdl
where aida.invoice_id=aia.invoice_id
and aida.invoice_distribution_id=xdl.source_distribution_id_num_1
and xdl.ae_header_id=xal.ae_header_id
and xdl.ae_line_num=xal.ae_line_num

) expenditure_item_date,
(SELECT distinct  aida.expenditure_type FROM ap_invoice_distributions_all aida,xla_distribution_links xdl
where aida.invoice_id=aia.invoice_id
and aida.invoice_distribution_id=xdl.source_distribution_id_num_1
and xdl.ae_header_id=xal.ae_header_id
and xdl.ae_line_num=xal.ae_line_num

) expenditure_type,
(SELECT distinct  hou.name FROM ap_invoice_distributions_all aida,xla_distribution_links xdl,hr_operating_units hou
where aida.invoice_id=aia.invoice_id
and aida.invoice_distribution_id=xdl.source_distribution_id_num_1
and xdl.ae_header_id=xal.ae_header_id
and xdl.ae_line_num=xal.ae_line_num
and aida.expenditure_organization_id= hou.organization_id

) expenditure_org,
(SELECT distinct  aida.attribute2 FROM ap_invoice_distributions_all aida,xla_distribution_links xdl
where aida.invoice_id=aia.invoice_id
and aida.invoice_distribution_id=xdl.source_distribution_id_num_1
and xdl.ae_header_id=xal.ae_header_id
and xdl.ae_line_num=xal.ae_line_num

) customer_name,

(SELECT distinct  aida.attribute3 FROM ap_invoice_distributions_all aida,xla_distribution_links xdl
where aida.invoice_id=aia.invoice_id
and aida.invoice_distribution_id=xdl.source_distribution_id_num_1
and xdl.ae_header_id=xal.ae_header_id
and xdl.ae_line_num=xal.ae_line_num

) bpo_project_name,

(SELECT distinct  aida.attribute1 FROM ap_invoice_distributions_all aida,xla_distribution_links xdl
where aida.invoice_id=aia.invoice_id
and aida.invoice_distribution_id=xdl.source_distribution_id_num_1
and xdl.ae_header_id=xal.ae_header_id
and xdl.ae_line_num=xal.ae_line_num

) staff_num
FROM apps.ap_invoices_all aia,
    xla.xla_transaction_entities XTE,
    apps.xla_events xev,
    apps.xla_ae_headers XAH,
    apps.xla_ae_lines XAL,
    apps.GL_IMPORT_REFERENCES gir,
    apps.gl_je_headers gjh,
    apps.gl_je_lines  gjl,
    apps.gl_code_combinations_kfv cc,
    apps.ap_suppliers aps,
    gl_period_statuses gps
   
WHERE  aia.INVOICE_ID = xte.source_id_int_1
    and xev.entity_id= xte.entity_id
    and xah.entity_id= xte.entity_id
    and xah.event_id= xev.event_id
    and XAH.ae_header_id = XAL.ae_header_id
    --and XAH.je_category_name = 'Purchase Invoices'
    and XAH.gl_transfer_status_code= 'Y'
    and gjh.STATUS = 'P'
    and XAL.GL_SL_LINK_ID=gir.GL_SL_LINK_ID
    and gir.GL_SL_LINK_TABLE = xal.GL_SL_LINK_TABLE
    and gjl.JE_HEADER_ID=gjh.JE_HEADER_ID
    and gjh.JE_HEADER_ID=gir.JE_HEADER_ID
    and gjl.JE_HEADER_ID=gir.JE_HEADER_ID
    and gir.JE_LINE_NUM=gjl.JE_LINE_NUM
    and cc.CODE_COMBINATION_ID=XAL.CODE_COMBINATION_ID
    and cc.CODE_COMBINATION_ID=gjl.CODE_COMBINATION_ID
    and aia.VENDOR_ID=aps.VENDOR_ID
    and gjh.STATUS='P'
    and gjh.Actual_flag='A'
    and gjh.Ledger_ID not in ( 1014,1015,1016 )
    --and gjl.EFFECTIVE_DATE between to_date(:P_START_DATE) and to_date(:P_END_DATE)
    --and xal.accounting_class_code in ('PREPAID_EXPENSE','ITEM EXPENSE','ACCRUAL','LIABILITY','INTRA')
    --and cc.segment1=111
    AND gjh.ledger_id=gps.ledger_id
  AND  101=gps.application_id      
  and gps.set_of_books_id=gjh.ledger_id
     and gjh.je_source ='Payables'
  and gjh.Ledger_ID not in ( 1014,1015,1016 )
AND gjh.period_name       = gps.period_name
    --and cc.segment5 in (:P_MIN_FLEX,:P_MAX_FLEX)
    and ( nvl(xal.accounted_cr,0)<>0 or nvl(xal.accounted_dr,0)<>0)
   
    --and aia.org_id=:P_ORG
    and xah.je_category_name='Purchase Invoices'
  AND (    NVL (gps.effective_period_num, 0) >=:cf_period_from
        AND (NVL (gps.effective_period_num, 0) <=:cf_period_to)
             ) 
--           and 24116=:p_account_from
--           and 24116=:p_account_to
                AND (    NVL (cc.segment5, 0) >= :p_account_from
        AND (NVL (cc.segment5, 0) <= :p_account_to)
       )
          
     AND (    NVL (cc.segment1, 0) >=:P_COMPANY_FROM
        AND (NVL (cc.segment1, 0) <= :P_COMPANY_TO)
       ) 
       AND aia.payment_currency_code =decode(:p_currency_code,'INR','INR',
                                              'ALL',aia.payment_currency_code,
                                              'NON INR',decode(aia.payment_currency_code,'INR','###',aia.payment_currency_code))
                                             
                                             
                                             
union all

SELECT
              gjh.period_name,
              null reference_2,
              null po_order_number,
              null  "Receipt Number",
              null "Receipt Date",
          ent.transaction_number "Invoice Number",
          aia.check_date "Invoice Date",
          av.vendor_name "Vendor Name",
          av.segment1 "Vendor Number",
          gjh.je_source "Source",
          gjh.ledger_id "set_of_books_id" ,
          gcck.concatenated_segments segment,
          gcck.concatenated_segments segment21,
          aia.currency_code currency_code
          ,ael.entered_dr,
          ael.entered_cr,
          ael.accounted_dr,
          ael.accounted_cr,
          gir.je_header_id,
          gir.je_line_num,
          gcck.code_combination_id
          ,gir.subledger_doc_sequence_value "Vocher Number"
          ,aeh.accounting_date "GL_Date"
          ,NULL "Staff Number"
          ,null "Project"
          ,null "Customer Num/Name"
          ,ael.description "Description",
          null attribute_category,
null project_name,
null task_name,
null expenditure_item_date,
null expenditure_type,
null expenditure_org,
null customer_name,
null bpo_project_name,
null staff_num
          FROM
          xla_ae_headers                   aeh
         ,xla_ae_lines                     ael
         ,xla_events                       xle
         ,xla_event_types_tl               xet
         ,xla_transaction_entities         ent
         ,gl_ledgers                       glg
         ,gl_periods                       glp
         ,xla_subledgers                   xls
         ,gl_code_combinations_kfv         gcck
               ,gl_import_references             gir
          ,gl_je_lines                      gjl
          ,gl_je_headers                    gjh
          ,gl_je_batches                    gjb
          --,ap_invoice_distributions_all aid
--          ,ap_invoice_lines_all aila
          ,ap_checks_all aia
          ,gl_period_statuses gps
          ,ap_suppliers av
          --,pa_project_customers_v ppcv
          --,pa_projects_all ppa
           WHERE  1=1
           AND  aeh.ledger_id              = glg.ledger_id
           AND  ael.application_id         =  aeh.application_id
           AND  ael.ae_header_id           =  aeh.ae_header_id
           AND  xle.application_id         =  aeh.application_id
           AND  xle.event_id               =  aeh.event_id
           AND  xet.application_id         =  xle.application_id
           AND  xet.event_type_code        =  xle.event_type_code
           AND  xet.LANGUAGE               =  USERENV('LANG')
           AND  ent.application_id         =  aeh.application_id
           AND  ent.entity_id              =  aeh.entity_id
           AND  glp.period_name            =  aeh.period_name
           AND  glp.period_set_name        =  glg.period_set_name
           AND  xls.application_id         =  aeh.application_id
           AND  gcck.code_combination_id   =  ael.code_combination_id

           AND aeh.accounting_entry_status_code <> 'N'  AND
aeh.application_id = 200 AND aeh.balance_type_code = 'A' AND (
NVL(ael.accounted_cr,0) <> 0
                                       OR NVL(ael.accounted_dr,0) <> 0 )
           AND  gir.gl_sl_link_id(+)       =  ael.gl_sl_link_id
           AND  gir.gl_sl_link_table(+)    =  ael.gl_sl_link_table
           AND  gjl.je_header_id(+)        =  gir.je_header_id
           AND  gjl.je_line_num(+)         =  gir.je_line_num
           AND  gjh.je_header_id(+)        =  gir.je_header_id
           AND  gjb.je_batch_id (+)        =  gir.je_batch_id
           AND  decode(gjh.je_header_id,null,'Y',gjh.je_from_sla_flag) in ('U', 'Y')
           --and ael.accounting_class_code   in ('ITEM EXPENSE','CASH_CLEARING','LIABILITY','PREPAID_EXPENSE','RTAX','INTRA')
 AND ent.source_id_int_1 = aia.check_id
          -- and aia.doc_sequence_value=gir.subledger_doc_sequence_value
           and aia.vendor_id=av.vendor_id
           and gjh.je_source ='Payables'
           AND aeh.je_category_name='Payments'

AND gjh.ledger_id=gps.ledger_id
  AND  101=gps.application_id
  and gps.set_of_books_id=gjh.ledger_id
  and gjh.Ledger_ID not in ( 1014,1015,1016 )
AND gjh.period_name       = gps.period_name

  --and aia.org_id=:P_ORG

  AND (    NVL (gps.effective_period_num, 0) >=:cf_period_from
        AND (NVL (gps.effective_period_num, 0) <=:cf_period_to)
             )

                AND (    NVL (gcck.segment5, 0) >= :p_account_from
        AND (NVL (gcck.segment5, 0) <= :p_account_to)
       )

     AND (    NVL (gcck.segment1, 0) >=:P_COMPANY_FROM
        AND (NVL (gcck.segment1, 0) <= :P_COMPANY_TO)
       )
       AND aia.currency_code =decode(:p_currency_code,'INR','INR',
                                              'ALL',aia.currency_code,
                                              'NON INR',decode(aia.currency_code,'INR','###',aia.currency_code))
                                             
                                             
                                             
union all

 SELECT     gjh.period_name,
             null reference_2,
             null po_order_number,
             case
             WHEN (gjh.je_category in ('Receipts','Misc Receipts')) THEN
             xte.transaction_number
             ELSE
               NULL
              END     "Receipt Number",
              case
             WHEN (gjh.je_category in ('Receipts','Misc Receipts')) THEN
             xah.accounting_date
             ELSE
               NULL
              END     "Receipt Date",
              case
              WHEN (gjh.je_category not in ('Receipts','Misc Receipts')) THEN
              xte.transaction_number  ELSE
             NULL
             END    "Invoice Number",
             case
              WHEN (gjh.je_category not in ('Receipts','Misc Receipts')) THEN
               xah.accounting_date
               ELSE
             NULL
             END    "Invoice Date",
          CASE
              WHEN xal.party_type_code = 'S' THEN
                 (SELECT         aps.segment1
                        ||'|'||aps.vendor_name
                        ||'|'||hzp.jgzz_fiscal_code
                        ||'|'||hzp.tax_reference
                        ||'|'||hps.party_site_number
                        ||'|'||hps.party_site_name
                        ||'|'||NULL
                   FROM  ap_suppliers          aps
                        ,ap_supplier_sites_all apss
                        ,hz_parties            hzp
                        ,hz_party_sites        hps
                        ,xla_ae_lines          ael2
                  WHERE  aps.vendor_id          = ael2.party_id
                    AND  hzp.party_id           = aps.party_id
                    AND  apss.vendor_site_id(+) = ael2.party_site_id
                    AND  hps.party_site_id(+)   = apss.party_site_id
                    AND  ael2.application_id    = xal.application_id
                    AND  ael2.ae_header_id      = xal.ae_header_id
                    AND  ael2.ae_line_num       = xal.ae_line_num )
              WHEN (xal.party_type_code = 'C' and xal.party_id is not null) THEN
                 (SELECT         --hca.account_number||'|'||
                 hzp.party_name
                        --||'|'||hzp.jgzz_fiscal_code
                        --||'|'||hzp.tax_reference
                        --||'|'||hps.party_site_number
                        --||'|'||hps.party_site_name
                        --||'|'||hzcu.tax_reference
                   FROM  hz_cust_accounts        hca
                        ,hz_cust_acct_sites_all  hcas
                        ,hz_cust_site_uses_all   hzcu
                        ,hz_parties              hzp
                        ,hz_party_sites          hps
                        ,xla_ae_lines            ael2
                  WHERE  hca.cust_account_id       = ael2.party_id
                    AND  hzp.party_id              = hca.party_id
                    AND  hzcu.site_use_id(+)       = ael2.party_site_id
                    AND  hcas.cust_acct_site_id(+) = hzcu.cust_acct_site_id
                    AND  hps.party_site_id(+)      = hcas.party_site_id
                    AND  ael2.application_id       = xal.application_id
                    AND  ael2.ae_header_id         = xal.ae_header_id
                    AND  ael2.ae_line_num          = xal.ae_line_num )
              ELSE
                NULL
              END       "Vendor Name",
              case
              WHEN (xal.party_type_code = 'C' and xal.party_id is not null) THEN
                 (SELECT         hca.account_number
                 --||'|'||  hzp.party_name
                        --||'|'||hzp.jgzz_fiscal_code
                        --||'|'||hzp.tax_reference
                        --||'|'||hps.party_site_number
                        --||'|'||hps.party_site_name
                        --||'|'||hzcu.tax_reference
                   FROM  hz_cust_accounts        hca
                        ,hz_cust_acct_sites_all  hcas
                        ,hz_cust_site_uses_all   hzcu
                        ,hz_parties              hzp
                        ,hz_party_sites          hps
                        ,xla_ae_lines            ael2
                  WHERE  hca.cust_account_id       = ael2.party_id
                    AND  hzp.party_id              = hca.party_id
                    AND  hzcu.site_use_id(+)       = ael2.party_site_id
                    AND  hcas.cust_acct_site_id(+) = hzcu.cust_acct_site_id
                    AND  hps.party_site_id(+)      = hcas.party_site_id
                    AND  ael2.application_id       = xal.application_id
                    AND  ael2.ae_header_id         = xal.ae_header_id
                    AND  ael2.ae_line_num          = xal.ae_line_num )
              ELSE
                NULL
              END       "Vendor Number",
              gjh.je_source"Source",
              gjh.ledger_id "set_of_books_id" ,
                        cc.concatenated_segments segment,
            cc.concatenated_segments segment21,
              xal.currency_code currency_code,
              xal.entered_dr,xal.entered_cr,
              xal.accounted_dr,xal.accounted_cr,
              gir.je_header_id,
            gir.je_line_num,
            cc.code_combination_id
            ,gir.subledger_doc_sequence_value "Vocher Number"
            ,xah.accounting_date "GL_Date"
            ,NULL "Staff Number"
            ,null "Project"
            ,null "Customer Num/Name"
            ,xah.description "Description",null attribute_category,
null project_name,
null task_name,
null expenditure_item_date,
null expenditure_type,
null expenditure_org,
null customer_name,
null bpo_project_name,
null staff_num
 
  FROM xla.xla_transaction_entities XTE,
      apps.xla_events xev,
      apps.xla_ae_headers XAH,
      apps.xla_ae_lines XAL,
      apps.GL_IMPORT_REFERENCES gir,
      apps.gl_je_headers gjh,
      apps.gl_je_lines  gjl,
      apps.gl_code_combinations_kfv cc,
      --apps.ap_suppliers aps,
      gl_period_statuses gps
     
     
  WHERE  1=1--aia.INVOICE_ID = xte.source_id_int_1
      and xev.entity_id= xte.entity_id
      and xah.entity_id= xte.entity_id
      and xah.event_id= xev.event_id
      and XAH.ae_header_id = XAL.ae_header_id
      --and XAH.je_category_name = 'Purchase Invoices'
      and XAH.gl_transfer_status_code= 'Y'
      and gjh.STATUS = 'P'
      and XAL.GL_SL_LINK_ID=gir.GL_SL_LINK_ID
      and gir.GL_SL_LINK_TABLE = xal.GL_SL_LINK_TABLE
      and gjl.JE_HEADER_ID=gjh.JE_HEADER_ID
      and gjh.JE_HEADER_ID=gir.JE_HEADER_ID
      and gjl.JE_HEADER_ID=gir.JE_HEADER_ID
      and gir.JE_LINE_NUM=gjl.JE_LINE_NUM
      and cc.CODE_COMBINATION_ID=XAL.CODE_COMBINATION_ID
      and cc.CODE_COMBINATION_ID=gjl.CODE_COMBINATION_ID
      --and aia.VENDOR_ID=aps.VENDOR_ID
      and gjh.STATUS='P'
      and gjh.Actual_flag='A'
      and gjh.Ledger_ID not in ( 1014,1015,1016 )
      --and gjl.EFFECTIVE_DATE between to_date(:P_START_DATE) and to_date(:P_END_DATE)
      --and xal.accounting_class_code in ('PREPAID_EXPENSE','ITEM EXPENSE','ACCRUAL','LIABILITY','INTRA')
      --and cc.segment1=111
      AND gjh.ledger_id=gps.ledger_id
    AND  101=gps.application_id      
    and gps.set_of_books_id=gjh.ledger_id
       and gjh.je_source ='Receivables'
    and gjh.Ledger_ID not in ( 1014,1015,1016 )
    --and xal.entered_dr='136547.55'
  AND gjh.period_name       = gps.period_name
      --and cc.segment5 in (:P_MIN_FLEX,:P_MAX_FLEX)
      and ( nvl(xal.accounted_cr,0)<>0 or nvl(xal.accounted_dr,0)<>0)
     
      --and aia.org_id=:P_ORG
      --and xah.je_category_name='Purchase Invoices'
    AND (    NVL (gps.effective_period_num, 0) >=:cf_period_from
          AND (NVL (gps.effective_period_num, 0) <=:cf_period_to)
               )
                   AND (    NVL (cc.segment5, 0) >= :p_account_from
          AND (NVL (cc.segment5, 0) <= :p_account_to)
         )
            
       AND (    NVL (cc.segment1, 0) >=:P_COMPANY_FROM
          AND (NVL (cc.segment1, 0) <= :P_COMPANY_TO)
         )
         --and xte.transaction_number='101914 - ZPF TDS April 12'
         AND xal.currency_code =decode(:p_currency_code,'INR','INR',
                                                'ALL',xal.currency_code,
                                                'NON INR',decode(xal.currency_code,'INR','###',xal.currency_code));