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'
)
