select invoice_num,
INVOICE_DATE,
supplier_name,
supplier_number,
business_unit,
inv_currency,
country_code,
INVOICE_AMOUNT,
round(conversion_rate_gbp*INVOICE_AMOUNT,2) as inv_amt_gbp,
AMOUNT_PAID ,
round(conversion_rate_gbp*AMOUNT_PAID,2) inv_amt_paid_gbp ,
inv_pay_status,
PAYMENT_DATE,
LINE_NUMBER,
line_type,
line_description,
nominal,
nominal_desc,
bu_code,
bu_desc,
line_amount ,
tax_amount,
invoice_key,
Inv_posted_date,
PO_NUMBER,
gl_code_combination,
SUPPLIER_SITE,
round(line_amount*conversion_rate_gbp,2) line_amt_gbp,
round(tax_amount*conversion_rate_gbp,2) tax_amt_gbp
FROM (
select distinct aia.invoice_num, --
aia.INVOICE_AMOUNT,
aia.invoice_id invoice_key,
TO_CHAR(ail.ACCOUNTING_DATE ,'DD-MON-YY','nls_date_language=american')as Inv_posted_date,
(SELECT pha.SEGMENT1
FROM po_headers_all pha
WHERE 1=1
and pha.vendor_id = supp.vendor_id
and pha.po_header_id = aia.po_header_id
)PO_NUMBER,
(gcc.SEGMENT1||'.'||
gcc.SEGMENT2||'.'||
gcc.SEGMENT3||'.'||
gcc.SEGMENT4||'.'||
gcc.SEGMENT5||'.'||
gcc.SEGMENT6||'.'||
gcc.SEGMENT7||'.'||
gcc.SEGMENT8||'.'||
gcc.SEGMENT9||'.'||
gcc.SEGMENT10 ) gl_code_combination,
(SELECT PSSAM.VENDOR_SITE_CODE
FROM POZ_SUPPLIER_SITES_ALL_M PSSAM
WHERE PSSAM.vendor_id = supp.vendor_id AND ROWNUM < 2) AS SUPPLIER_SITE,
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,
ail.LINE_NUMBER,
ail.LINE_TYPE_LOOKUP_CODE,
(select DESCRIPTION
from fnd_lookup_values
where 1=1
and lookup_type = 'INVOICE LINE TYPE'
and LOOKUP_CODE = ail.LINE_TYPE_LOOKUP_CODE
and ENABLED_FLAG = 'Y'
) line_type,
ail.DESCRIPTION as line_description,
ail.LINE_SOURCE,
ail.ITEM_DESCRIPTION,
aida.AMOUNT as line_amount,
(SELECT TAX_AMT from zx_lines zxl WHERE zxl.TRX_ID = ail.invoice_id AND zxl.TRX_LINE_NUMBER = ail.LINE_NUMBER AND zxl.entity_code='AP_INVOICES' AND rownum<2) as tax_amount,
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) = (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'
),2)
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-yy') PAYMENT_DATE ,
gcc.segment1,
gcc.segment2 bu_code,
(SELECT GL_FLEXFIELDS_PKG.get_description_sql(GC.CHART_OF_ACCOUNTS_ID,2,
GC.segment2)
FROM GL_CODE_COMBINATIONS GC
WHERE gc.CODE_COMBINATION_ID = gcc.CODE_COMBINATION_ID ) bu_desc,
gcc.segment3,
gcc.segment4 nominal,
(SELECT GL_FLEXFIELDS_PKG.get_description_sql(GC.CHART_OF_ACCOUNTS_ID,4,
GC.segment4) DESCRIPTION
FROM GL_CODE_COMBINATIONS GC
WHERE gc.CODE_COMBINATION_ID = gcc.CODE_COMBINATION_ID ) nominal_desc,
gcc.segment5,
gcc.segment6,
gcc.segment7,
gcc.segment8,
gcc.segment9 country_code,
gcc.segment10
/*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,
AP_INVOICE_LINES_ALL ail,
zx_lines zxl,
AP_INVOICE_DISTRIBUTIONS_ALL aid,
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,
gl_code_combinations gcc,
AP_INVOICE_DISTRIBUTIONS_ALL aida
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 aia.invoice_id = ail.invoice_id
and aia.invoice_id = aida.invoice_id
and zxl.TRX_ID = ail.invoice_id
and zxl.TRX_LINE_NUMBER = ail.LINE_NUMBER
and zxl.entity_code='AP_INVOICES'
and ail.LINE_TYPE_LOOKUP_CODE = 'ITEM'
and ail.invoice_id = aid.invoice_id
and ail.line_number = aid.INVOICE_LINE_NUMBER
and aid.DIST_CODE_COMBINATION_ID = gcc.code_combination_id
and aida.DIST_CODE_COMBINATION_ID = gcc.code_combination_id
and aia.invoice_id = aid.invoice_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 aida.FINAL_MATCH_FLAG = 'N'
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 ail.ACCOUNTING_DATE between :p_start_date and :p_end_date
----and gcc.segment2=1340
--and gcc.segment2=1274
--and aida.AMOUNT = 120
--and aia.invoice_num='50462'
)
order by invoice_num, LINE_NUMBER