How to see Deleted Document Numbers in GL (GL Doc Seq Audit) - Oracle EBS R12

https://generalledger-gl.blogspot.com/2016/10/how-to-see-deleted-document-numbers-in.htm

How to see Deleted Document Numbers in GL (GL Doc Seq Audit)

select dsa.SET_OF_BOOKS_ID,

       aud.creation_date audit_creation_date,

       

       categories.user_je_category_name category,

       

       aud.doc_sequence_value doc_number,

       

       aud.doc_sequence_id doc_sequence_id,

       

       --decode(headers.name,NULL,:DELETED_MSG,:ENTERED_MSG)     status, --Commented

       

       decode(headers.name, NULL, 'Deleted', '') status,

       

       fu.USER_NAME,

       

       aud.CREATED_BY Creted_by,

       

       headers.name header_name,

       

       headers.currency_code currency_code,

       

       batches.name batch_name,

       

       lookups.description posting_status,

       

       headers.posted_date posted_date,

       

       headers.running_total_dr debits,

       

       headers.running_total_cr credits,

       

       seq.name sequence_name,

       

       seq.doc_sequence_id seq_sequence_id,

       

       seq.db_sequence_name seq_db_name,

       

       seq.initial_value initial_value,

       

       seq.type type

  from gl_lookups lookups,

       

       gl_je_categories categories,

       

       gl_je_batches batches,

       

       gl_je_headers headers,

       

       gl_doc_sequence_audit aud,

       

       fnd_document_sequences seq,

       

       fnd_doc_sequence_assignments dsa,

       

       fnd_user fu

 WHERE lookups.lookup_type(+) = 'MJE_BATCH_STATUS'

      

   AND lookups.lookup_code(+) = headers.status

      

   AND categories.je_category_name(+) = headers.je_category

      

   AND batches.je_batch_id(+) = headers.je_batch_id

      

   AND headers.doc_sequence_value(+) = aud.doc_sequence_value

      

   AND headers.doc_sequence_id(+) = aud.doc_sequence_id

      

   and aud.CREATED_BY = fu.USER_ID

      

   and dsa.DOC_SEQUENCE_ID = seq.DOC_SEQUENCE_ID

      

   and dsa.DOC_SEQUENCE_ASSIGNMENT_ID = aud.DOC_SEQUENCE_ASSIGNMENT_ID

      

      -- and headers.DOC_SEQUENCE_ID=dsa.DOC_SEQUENCE_ID

      

   and headers.name is null

      

      --&C_WHERE_SEC --Added for bug12426736 --Commented

      

      --AND         ((headers.parent_je_header_id IS NULL) OR (NVL(headers.je_from_sla_flag , 'N')= 'Y'))

      

      /* AND aud.doc_sequence_value between

      

      nvl(:P_SEQUENCE_FROM, aud.doc_sequence_value) and

      

      nvl(:P_SEQUENCE_TO, aud.doc_sequence_value)*/ --Commented

      

   AND aud.doc_sequence_id = seq.doc_sequence_id

      

   AND seq.type IN ('A', 'G')

      

   and dsa.SET_OF_BOOKS_ID = NVL('&Ledger_id', dsa.SET_OF_BOOKS_ID)

 ORDER BY seq.name, aud.doc_sequence_value