Core Financialspublic

General Ledger & Intercompany

Get Involved. Join the Conversation.

This is a public Forum  public

Topic

    Kevin Johnson
    Journal AttachmentsAnswered
    Topic posted August 30, 2017 by Kevin JohnsonBronze Medal: 1,250+ Points, last edited January 30, 2019, tagged Financials, General Ledger, Reports 
    405 Views, 6 Comments
    Title:
    Journal Attachments
    Summary:
    Need to find Journal entries with Attachments vs. ones with out
    Content:

    Hi Everyone,

    Does any one know a way to identify which General Ledger Journal entries have an attachment attached vs. journal entries that do not have an attachment? We do not want to go into each journal entry individually. We tried through Manage Journals and OTBI and could not find any thing that would allow us to separate the journals in this manner. We could be missing something?

     

    Thanks,

    Kevin 

    Best Comment

    Alexey Shtrakhov

    like this you can find ap invoice attachments. So similar way you can search from the same ucm related tables the journal attachments:

    SELECT   fd.*
    FROM fnd_attached_documents fad,
    fnd_documents_vl fd
    WHERE fd.document_id              = fad.document_id
    AND fad.pk1_value =   to_char((select invoice_id from ap_invoices_all where invoice_num = '2616A2'))
    AND fad.ENTITY_NAME in ('AP_INVOICES_ALL')
    ;
     
    alexey

    Comment

     

    • Alexey Shtrakhov

      like this you can find ap invoice attachments. So similar way you can search from the same ucm related tables the journal attachments:

      SELECT   fd.*
      FROM fnd_attached_documents fad,
      fnd_documents_vl fd
      WHERE fd.document_id              = fad.document_id
      AND fad.pk1_value =   to_char((select invoice_id from ap_invoices_all where invoice_num = '2616A2'))
      AND fad.ENTITY_NAME in ('AP_INVOICES_ALL')
      ;
       
      alexey
    • Jennifer Bruce

      Hi

       

      I'm trying to achieve the same thing - getting a report out of BIP which shows basic journal batch and header information (posting status being 'unposted', user who created it, journal name, accounting period, journal description, and the total debit value of the journal) as well as whether or not the journal has an attachment to it.

       

      As a result of a very poor external audit, we are having to manually review every single journal that is entered into the system prior to it being posted.  All journals without an attachment will straight away be being rejected by the review panel.  Long term we are looking at implemented the journal approval functionality within the system, but a report showing unposted journals and whether or not they have an attachment is wanted as an interim measure.

       

      By extracting data from GL_JE_HEADERS and GL_JE_BATCHES I can get the journal side of the information I want, and using the code in Alexey's example, I can get some of the attachment information (changing ENTITY_NAME to be 'JournalBatch'), but I am struggling to link the two - is anyone able to provide a bit more information as to how to get the link between the journal side and the attachment side in order to create the data model?

       

      Thanks in advance

      Jenny

      • Jamie Morris

        Did you ever find a solution to joining the query above from the ucm to the journal headers tables?  I'm struggling with the same thing at the moment.

        Thanks,

        Jamie

        • Kevin Johnson

          Try this... Hopefully it is what you need...

           

          SELECT  CASE WHEN fad.ENTITY_NAME = 'JournalBatch' THEN 'Batch' ELSE 'Header' END "Type", fd.creation_date, fd.created_by, fd.title, fd.file_name, fad.pk1_value, fad.category_name, glb.je_batch_id, glb.je_batch_id "Batch2"
          FROM fnd_attached_documents fad, 
          fnd_documents_vl fd,  gl_je_batches glb
          WHERE fd.document_id              = fad.document_id 
          and glb.je_batch_id = fad.pk1_value
          and fad.category_name like ('GL_JE_BATCHES')
          UNION 
          SELECT  CASE WHEN fad.ENTITY_NAME = 'JournalBatch' THEN 'Batch' ELSE 'Header' END "Type", fd.creation_date, fd.created_by, fd.title, fd.file_name, fad.pk1_value, fad.category_name, gjh.je_header_id, gjb.je_batch_id
          FROM fnd_attached_documents fad, 
          fnd_documents_vl fd,  gl_je_headers gjh, gl_je_batches gjb
          WHERE fd.document_id              = fad.document_id 
          and gjh.je_header_id = fad.pk1_value
          and gjh.je_batch_id = gjb.je_batch_id
          and fad.category_name like ('GL_JE_HEADER%')

    • Kevin Johnson

      We did... it was done by a consultant. I am looking for the report in our system. I will upload the SQL statement once I find it. The report shows the File name of the attachment. If there is no attachment, it is blank. Is this the information you are looking for?

    • angela ahrens

      did we get sql on showing manual journal entries with missing attachments?

     

    community forum