Payables & Cash Management

Get Involved. Join the Conversation.

Topic

    Sathya Kannan
    Fusion AP - FND_DOCUMENTS_TL table to link to invoice table?
    Topic posted August 5, 2019 by Sathya KannanBlue Ribbon: 750+ Points, tagged Financials, Payables, Reports 
    45 Views, 5 Comments
    Title:
    Fusion AP - FND_DOCUMENTS_TL table to link to invoice table?
    Summary:
    Need to find the invoices that have attachments
    Content:

    Hi,

    Does anyone know the join between tables AP_INVOICES_ALL and FND_DOCUMENTS_TL?

    We are trying to find out how many invoices have attachments added.

     

    Quick help would be much appreciated.

     

     

    Regards,

    Sathya Kannan

    Comment

     

    • Julien Dubouis

      Hi,

      Isn't the join on invoice_id = document_id ? It seems to give meaningful results on a demo environment : 

      select count (invoice_num) from ap_invoices_all ap left join fnd_documents att on ap.invoice_id = att.document_id

      Thanks
       

      • Sathya Kannan

        Hi,

         

        I ran this sql -> select invoice_num from ap_invoices_all 
        where invoice_type_lookup_code='STANDARD' and invoice_id not in (select document_id from fnd_documents) and it gives me the invoice numbers that have attachments as well. 

         

         

        Regards,

        Sathya

        • Julien Dubouis

          Right, it seems to be on the table FND_ATTACHED_DOCUMENTS (and not FND_DOCUMENTS) and the link is on pk1_value 

          select invoice_num
          from ap_invoices_all ap
          INNER JOIN FND_ATTACHED_DOCUMENTS att on ap.invoice_id = att.pk1_value /*comment if you want to display only invoices without attachments*/
          --LEFT JOIN FND_ATTACHED_DOCUMENTS att on ap.invoice_id = att.pk1_value /*un-comment if you want to display only invoices without attachments*/
          --LEFT JOIN XLE_ENTITY_PROFILES xle on xle.legal_entity_id = ap.legal_entity_id
          where 1=1
          --and att.document_id is null /*un-comment if you want to display only invoices without attachments*/
          --and xle.name = 'France Legal Entity'
          and att.entity_name = 'AP_INVOICES_ALL'

    • Sathya Kannan

       

      Hi,

      I wrote this and it has worked as expected.

      select invoice_num,invoice_id from ap_invoices_all
      where invoice_id not in (select pk1_value from fnd_attached_documents 
      where entity_name = 'AP_INVOICES_ALL' and document_id in (select document_id from fnd_documents_tl)

       

       

      Regards,

      Sathya Kannan