Follow us: Subscribe via RSS Feed Connect on YouTube Connect on YouTube


Sunday, 5 February 2017

Query To Get The Details Which Are Posted To GL Through Payables

SELECT   gld.short_name "BOOK", gls.user_je_source_name "SOURCE",
         gls.user_je_source_name, glc.user_je_category_name "CATEGORY",
         gjb.NAME "BATCH NAME", gjh.NAME "JOURNAL NAME",
         gjh.currency_code "CURRENCY", gjl.je_line_num "JRNL LINE#",
         gjl.effective_date "ACCOUNTING DATE", gjh.period_name "PERIOD",
         gjh.date_created "CREATED DATE",
         || '-'
         || gcc.segment2
         || '-'
         || gcc.segment3
         || '-'
         || gcc.segment4 "ACCOUNT COMBINATION",
         gcc.segment1, gcc.segment2, gcc.segment3, gcc.segment4,
         gjl.entered_dr, gjl.entered_cr, gjl.accounted_dr, gjl.accounted_cr,
         gjh.currency_conversion_rate "CONV RATE",
         gjh.currency_conversion_date "CONV DATE",
         gjh.currency_conversion_type "CONV TYPE", gjl.description,
         gjl.reference_1 "AP VAND NAME", gjl.reference_2 "AP INV_ID",
         gjl.reference_3 "AP INV LINE#CHEQUEID", gjl.reference_4 "AP PAYDOC#",
         gjl.reference_5 "AP INVOICE #", gjl.reference_6 "AP ACCOUNTING TYPE",
         gjl.reference_7 "AP SOURCE ID", gjl.reference_8 "AP NA",
         gjl.reference_9 "AP DOCUMENT ID", gjl.reference_10 "AP LINE TYPE"
    FROM gl_je_batches gjb,
         gl_je_headers gjh,
         gl_je_lines gjl,
         gl_code_combinations gcc,
         gl_ledgers gld,
         gl_je_sources_tl gls,
         gl_je_categories_tl glc
   WHERE gjh.je_header_id = gjl.je_header_id
     AND gjb.je_batch_id = gjh.je_batch_id
     AND gcc.code_combination_id = gjl.code_combination_id
     AND gls.je_source_name = gjh.je_source
     AND glc.je_category_name = gjh.je_category
     AND gld.ledger_id = gjh.ledger_id
     AND gjh.period_name = 'DEC-04'
     AND gcc.segment1 = '0101'
     AND gcc.segment2 = '0202'
     AND gcc.segment3 = '53010'
     AND gcc.segment4 = '60002'

ORDER BY 1, 2, 3, 4, 5, 7;
Share this article :


Post a Comment