Lovin IT

select AP_INVOICES_PKG.GET_POSTING_STATUS(aia.INVOICE_ID) = 'Y'
'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",
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) 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)
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
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 aps.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 aps.last_updated_by = fu.user_id
and aia.creation_date > sysdate -90
and aida.cancelled_flag is null