Query to get Invoice Header Details

 select invoice_num,

       INVOICE_DATE,

   supplier_name,

   supplier_number,

   business_unit,

   inv_currency,

   INVOICE_AMOUNT,

   conversion_rate_gbp*INVOICE_AMOUNT as inv_amt_gbp,

   AMOUNT_PAID,

   conversion_rate_gbp*AMOUNT_PAID inv_amt_paid_gbp,

   inv_pay_status,

   PAYMENT_DATE

FROM (  

select distinct aia.invoice_num, --

       aia.INVOICE_AMOUNT,

   aia.SOURCE,

   aia.AMOUNT_PAID,

   aia.INVOICE_CURRENCY_CODE inv_currency, --

   aia.INVOICE_TYPE_LOOKUP_CODE,

   to_char(aia.INVOICE_DATE, 'dd-Mon-yy') INVOICE_DATE, -- 

   aia.TAXATION_COUNTRY country,

   supp.vendor_name supplier_name, --

   supp.segment1 supplier_number, --

   hao.NAME business_unit, -- 

   att.NAME payment_terms,

   case 

when aia.INVOICE_CURRENCY_CODE = 'GBP'

  THEN 1

else 

  TRUNC((select conversion_rate

   from gl_daily_rates

   where 1=1

   and from_currency = aia.INVOICE_CURRENCY_CODE

   and to_currency = 'GBP'

   and trunc(conversion_date) = trunc(ipa.PAYMENT_DATE)/*(SELECT MAX(conversion_date) 

                                 from gl_daily_rates 

where 1=1

and from_currency = aia.INVOICE_CURRENCY_CODE

and conversion_type = 'Spot'

and to_currency = 'GBP')*/

   and conversion_type = 'Spot'

   ),10)

end conversion_rate_gbp ,

        CASE 

      WHEN aia.PAYMENT_STATUS_FLAG = 'N'

         THEN 'Not Paid'

         WHEN aia.PAYMENT_STATUS_FLAG = 'P'  

THEN 'Partially Paid'

         ELSE 'Fully Paid'  

        END inv_pay_status,

to_char(ipa.PAYMENT_DATE, 'dd-Mon-yyyy') PAYMENT_DATE

        /*inv_pay.amount inv_amt_paid,

        inv_pay.payment_num,

        inv_pay.period_name,

        inv_pay.accounting_date,

        inv_pay.posted_flag,

        inv_pay.creation_date */

from AP_INVOICES_ALL aia,

POZ_SUPPLIERS_V supp,

AP_TERMS_TL att,

HR_ALL_ORGANIZATION_UNITS hao,

AP_INVOICE_PAYMENTS_ALL inv_pay,

ap_checks_all aca,

iby_payments_all ipa

where 1=1

and aia.party_id = supp.party_id

and aia.org_id = hao.ORGANIZATION_ID

and aia.TERMS_ID = att.TERM_ID

and att.LANGUAGE = 'US'

and aia.PAYMENT_STATUS_FLAG != 'N'

--and aia.PAYMENT_STATUS_FLAG = 'P'  --

and inv_pay.invoice_id = aia.invoice_id

and aca.check_id = inv_pay.check_id

and aca.STATUS_LOOKUP_CODE = 'CLEARED'  --  temp to be tested

and ipa.payment_id = aca.payment_id

and ipa.PAYMENT_DATE between :p_start_date and :p_end_date

--and aia.INVOICE_TYPE_LOOKUP_CODE = 'STANDARD'

--and aia.invoice_num in ('ERS-2021-10-11-28038','4154615465','9442040', '8185047')

--'ERS-2022-06-29-115421'   --'51579578'  --'2203929689'  --'0000002923'  'ERS-2021-10-11-28038'

--AND LINE_SOURCE = 'ETAX'

)