AP TO GL - PREPAY QUERY

AP TO GL - PREPAY QUERY

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,
     
       XDL.UNROUNDED_ENTERED_DR entered_dr,
       XDL.UNROUNDED_ENTERED_CR entered_cr,
       XDL.UNROUNDED_ACCOUNTED_DR accounted_dr,
       XDL.UNROUNDED_ACCOUNTED_CR 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"
FROM ap_invoices_all aia,
ap_invoice_lines_all aila,
ap_invoice_distributions_all aida,
ap_prepay_app_dists apad,
xla_distribution_links xdl,
xla_ae_lines xal,
xla_ae_headers xah,
gl_import_references gir,
gl_je_lines gjl,
gl_je_headers gjh, gl_code_combinations_kfv cc,
    gl_period_statuses gps,
    apps.ap_suppliers aps
where aia.invoice_id=aila.invoice_id
and aila.invoice_id=aida.invoice_id
and aila.line_number=aida.invoice_line_number
--and aia.invoice_id=88734
and aida.line_type_lookup_code='PREPAY'
and aida.invoice_distribution_id= apad.prepay_app_distribution_id
and apad.amount<>0
and apad.prepay_app_dist_id=xdl.source_distribution_id_num_1
and xdl.applied_to_source_id_num_1 in (aila.prepay_invoice_id,aila.invoice_id)
and xdl.ae_header_id=xal.ae_header_id
and xdl.ae_line_num=xal.ae_line_num
and xal.ae_header_id=xah.ae_header_id
and xal.gl_sl_link_id= gir.gl_sl_link_id
and xal.gl_sl_link_table= gir.gl_sl_link_table
    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 gir.je_header_id=gjl.je_header_id
and gir.je_line_num=gjl.je_line_num
and gjl.je_header_id=gjh.je_header_id
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 (    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))
                                              order by 6;