JL Colombian Account / Third Party - draft

Description
Categories: BI Publisher
Application: Latin America Localizations
Source: Colombian Account / Third Party Report (XML) - Not Supported: Reserved For Future Use
Short Name: JLCOGLAN_XML
DB package: JL_JLCOGLAN_XMLP_PKG
SELECT  bal.account_code ACCOUNT,
      acc.description DESCRIPTION,
        bal.nit_id NIT,
        nit.nit NIT_NUM,
        bal.period_name PERIOD,
                  glc.user_je_category_name CATEGORY,
     trx.je_batch_id BATCH,
      trx.je_header_id HEADER,
      trx.document_number  DOCUMENT,
      jeh.doc_sequence_value DOC_SEQ_VALUE,
      trx.accounting_date ADATE,
      nvl(trx.accounted_dr,0) DEBITS,
      nvl(trx.accounted_cr,0) CREDITS,
      nvl(trx.entered_dr,0) ODEBITS,
      nvl(trx.entered_cr,0) OCREDITS,
      bal.currency_code CURRENCY1,
                  bal.currency_code CURRENCY2,
        cc.code_combination_id CCID,
      nit.name NAME,
      jeh.name JOURNAL_NAME,
      jeb.name BATCH_NAME, 
	JL_JLCOGLAN_XMLP_PKG.cf_balformula(:CS_DEBITS, :CS_CREDITS, :C_BAL_SUM) CF_BAL, 
	JL_JLCOGLAN_XMLP_PKG.cf_obalformula(:CS_ODEBITS, :CS_OCREDITS) CF_OBAL, 
	JL_JLCOGLAN_XMLP_PKG.c_ccidformula(bal.nit_id, bal.account_code) C_CCID
FROM    jl_co_gl_trx trx,
        gl_code_combinations cc,
        jl_co_gl_nit_accts_v acc,
        jl_co_gl_nits nit,
        gl_je_headers jeh,
        gl_je_batches jeb,
        gl_je_categories glc,
        jl_co_gl_balances bal
WHERE   bal.set_of_books_id = :P_SET_OF_BOOKS_ID
AND     jeh.je_header_id(+) = trx.je_header_id
AND     jeb.je_batch_id(+) = trx.je_batch_id
AND     &WHERE_FLEX
AND     cc.chart_of_accounts_id = :STRUCT_NUM
AND     cc.chart_of_accounts_id = acc.chart_of_accounts_id
AND            cc.code_combination_id = bal.code_combination_id
AND           trx.category = jeh.je_category(+)
AND           jeh.je_category = glc.je_category_name(+)
AND            to_number(to_char(bal.period_year)||LPAD(to_char(bal.period_num),2,'0'))
                   BETWEEN to_number(to_char(:C_PYEAR)||LPAD(to_char(:C_PNUM),2,'0'))
AND           to_number(to_char(:C_PYEAR1)||LPAD(to_char(:C_PNUM1),2,'0'))
AND     acc.account_code = bal.account_code
AND     nit.nit_id = bal.nit_id
AND          bal.code_combination_id = trx.code_combination_id(+)
AND          bal.period_name   = trx.period_name(+)
AND         bal.nit_id = trx.nit_id(+)
ORDER BY        trx.account_code,trx.nit_id,cc.code_combination_id,trx.accounting_date
Parameter Name SQL text Validation
Chart Of Accounts ID
 
LOV Oracle
GL_SET_OF_BOOKS_ID
 
Number
REPORTING_LEVEL
 
LOV Oracle
Account To
 
Account From
 
Ending Period
 
LOV Oracle
Starting Period
 
LOV Oracle
Compnay Name
 
LOV Oracle