Lovin IT

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-19')
    and aida.cancelled_flag is null
    and aia.payment_status_flag = 'Y'
    and aia.cancelled_by is null
--    and aia.invoice_id = 4992587
    ;