Friday, 19 January 2018

Query to retrieve GL Journal Import

SELECT
       glc.segment1
       || '-'
       || apps.gl_flexfields_pkg.get_description_sql
                                                    (glc.chart_of_accounts_id,
                                                     1,
                                                     glc.segment1
                                                    ) "Company" , 
       to_char(glh.default_effective_date,'DD-MON-YYYY') "Accounting Date"
       ,glh.je_source "Source"
       ,gljc.user_je_category_name "Category"
       ,glh.doc_sequence_value "Document Number"
       ,glh.currency_code "Currency"
        ,NVL(REPLACE(gll.description,chr(9),''),'')"Description"
       ,gll.entered_dr "Dr"
       ,gll.entered_cr "Cr"
       ,glh.created_by "Created by"
       ,glh.creation_date     
  FROM apps.gl_je_headers glh,
       apps.gl_je_lines gll,
       apps.gl_je_categories_tl gljc,
       apps.gl_code_combinations glc
 WHERE glh.je_header_id = gll.je_header_id
   AND gll.code_combination_id = glc.code_combination_id
   --AND glh.je_source = 'Manual'
   AND glh.default_effective_date BETWEEN :lv_from_date AND :lv_to_date
   AND glh.je_category=gljc.je_category_name
   order by doc_sequence_value,default_effective_date,gll.entered_dr,gll.entered_cr;

No comments:

Post a Comment