select AP_INVOICES_PKG.GET_POSTING_STATUS(aia.INVOICE_ID) = 'Y'--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",
--count(aida.distribution_line_number)
as "Count Invoice Line Number",
--sum(aida.amount) aia.invoice_amount as "Invoice Distribution Amount SUM",
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), hr.name,
aia.description as "Invoice Description",
aia.invoice_amount as "Gross Amount",
aia.total_tax_amount as "Total Sales Tax Amount",
aia.self_assessed_tax_amount as "Total Use Tax Amount",
aia.invoice_amount - nvl(aia.total_tax_amount,0) - nvl(aia.self_assessed_tax_amount,0) as "Invoice Net Amount",
aida.line_type_lookup_code as "Line Type",
aida.amount as "Line Amount",
aba.batch_name,
'20'||SUBSTR(aia.invoice_date,8)
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 apsaia.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 apsaia.last_updated_by = fufu2.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 > sysdate -90to_date('01-SEP-19')
and aida.cancelled_flag is null
and aia.payment_status_flag = 'Y'
and aia.cancelled_by is null
-- and aia.invoice_id = 4992587
; |
