select --aida.*
distinct
'20'||SUBSTR(aia.invoice_date,8) as "Period Year",
aida.period_name,
hr.name as "Operating Unit",
aia.pay_group_lookup_code as "Pay Group",
aps.segment1 as "Supplier Number",
aps.vendor_name "Supplier Name",
aba.batch_id as "Batch ID",
aba.batch_name as "Batch Name",
aia.invoice_date as "Invoice Date",
aia.invoice_num as "Invoice Number",
aia.description as "Invoice Description",
aia.invoice_type_lookup_code "Invoice Type Code",
flv.description as "Invoice Type Description",
aba.invoice_currency_code as "Invoice Currency Code",
gcc.segment1 as "Company",
gcc.segment2 as "Account",
gcc.segment3 as "Department",
gcc.segment1||'.'||gcc.segment2||'.'||gcc.segment3||'.'||gcc.segment4||'.'||gcc.segment5||'.'||gcc.segment6 as "Account String",
aia.invoice_amount as "Invoice Amount",
aia.creation_date "Creation Date",
fu.user_name as "Created By",
aia.last_update_date as "Last Update Date",
fu2.user_name as "Last Updated By",
decode(fad.attached_document_id, null,'N','Y') as "Attachment Flag",
dtl.file_name as "File Name",
AP_INVOICES_PKG.GET_POSTING_STATUS(aia.INVOICE_ID) as "Posting Status"
from AP.AP_INVOICES_ALL aia,
ap.AP_SUPPLIERS aps,
hr_operating_units hr,
AP_INVOICE_DISTRIBUTIONS_ALL aida,
gl_code_combinations gcc,
AP_BATCHES_ALL aba,
fnd_user fu,
fnd_user fu2,
fnd_attached_documents fad,
fnd_documents_tl dtl,
fnd_lookup_values flv
where aia.VENDOR_ID = aps.VENDOR_ID
and aia.ORG_ID = hr.ORGANIZATION_ID
and aia.invoice_id = aida.invoice_id
and aida.dist_code_combination_id = gcc.code_combination_id
and aia.batch_id = aba.batch_id (+)
and aia.created_by = fu.user_id
and aia.invoice_id = fad.pk1_value (+)
AND fad.entity_name (+) = 'AP_INVOICES'
AND fad.document_id = dtl.document_id (+)
and dtl.SOURCE_LANG (+) = 'US'
and aia.last_updated_by = fu2.user_id
and aia.invoice_type_lookup_code = flv.lookup_code
and flv.lookup_type = 'INVOICE TYPE'
AND flv.SOURCE_LANG = 'US'
and aia.creation_date > to_date('01-SEP-18')
and aia.creation_date < to_date('01-JUL-19')
and aida.cancelled_flag is null
and aia.payment_status_flag = 'Y'
and aia.cancelled_by is null
-- and aia.invoice_id = 4992587
; |