GL Journals - Line

Description
Categories: BI Publisher, Financials
Application: General Ledger
Source: Journals - Line (XML)
Short Name: GLYRLJGE_XML
DB package: GL_GLYRLJGE_XMLP_PKG
select fnd_flex_xml_publisher_apis.process_kff_combination_1('p_balance_seg', 'SQLGL', 'GL#', T4.CHART_OF_ACCOUNTS_ID, NULL, T4.CODE_COMBINATION_ID, 'GL_BALANCING', 'Y', 'VALUE')                                      Company,
           L.name                                                           LEDGER_NAME,
           L.currency_code                                            LEDGER_CURRENCY,
          T6.start_date                                                  period_start_date,
          T6.period_name                                              Period,
          T7.user_je_category_name                            Category,
          substr(T7.description,1,50)                            Category_Desc,
          T2.external_reference                                    Entry_Ref,
          T3.name                                                           Batch_Name,
          T3.default_effective_date                              Batch_Date,
          T3.posted_date                                               Posting_Date,
          T2.name                                                           Entry_Name,
          T8.user_je_source_name                                Source,
         T2.currency_code                                              Currency1,
         T2.currency_conversion_rate                           Currency_Rate,
         gdct.user_conversion_type                              Currency_Type,
         T2.doc_sequence_value                                   Doc_Sequence,
                                                                                    T1.effective_date,
         null                                                  C_Flexdata,
         substr(decode(T7.je_category_name,
             'Payments', T1.reference_4,
             'Purchase Invoices', T1.reference_5,
              T2.external_reference),1,15)                       Reference,
         substr(T1.description,1,25)                               Description, 
                                                                                     T1.entered_dr entered_dr,
                                                                                     T1.entered_cr entered_cr, 
                                                                                     T1.accounted_dr ACCOUNTED_dr,
                                                                                     T1.accounted_cr ACCOUNTED_CR, 
	--GL_GLYRLJGE_XMLP_PKG.c_bal_lpromptformula(:C_Bal_Lprompt) C_Bal_Lprompt, 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('c_company_name', 'SQLGL', 'GL#', T4.CHART_OF_ACCOUNTS_ID, NULL, T4.CODE_COMBINATION_ID, 'GL_BALANCING', 'Y', 'DESCRIPTION') C_Company_Name, 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('bal_secure', 'SQLGL', 'GL#', T4.CHART_OF_ACCOUNTS_ID, NULL, T4.CODE_COMBINATION_ID, 'GL_BALANCING', 'Y', 'SECURITY') BAL_SECURE, 
--	GL_GLYRLJGE_XMLP_PKG.c_curr_nameformula(CURRENCY1) C_CURR_NAME, 
GL_GLYRLJGE_XMLP_PKG.c_curr_nameformula(T2.currency_code) C_CURR_NAME, 
--	GL_GLYRLJGE_XMLP_PKG.c_entryformula(DOC_SEQUENCE, ENTRY_NAME) C_Entry, 
GL_GLYRLJGE_XMLP_PKG.c_entryformula(T2.doc_sequence_value, T2.name) C_Entry, 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('c_acc_flexfield', 'SQLGL', 'GL#', T4.CHART_OF_ACCOUNTS_ID, NULL, T4.CODE_COMBINATION_ID, 'ALL', 'Y', 'VALUE') C_Acc_Flexfield, 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('flex_secure', 'SQLGL', 'GL#', T4.CHART_OF_ACCOUNTS_ID, NULL, T4.CODE_COMBINATION_ID, 'ALL', 'Y', 'SECURITY') FLEX_SECURE, 
	--GL_GLYRLJGE_XMLP_PKG.zero_indicatorformula(ACCOUNTED_CR, ACCOUNTED_DR, ENTERED_DR, ENTERED_CR) ZERO_INDICATOR
	GL_GLYRLJGE_XMLP_PKG.zero_indicatorformula(ACCOUNTED_CR, ACCOUNTED_DR, ENTERED_DR, ENTERED_CR) ZERO_INDICATOR,
	GL_GLYRLJGE_XMLP_PKG.g_batchesgroupfilter(t1.accounted_cr,t1.accounted_dr,t1.entered_dr,t1.entered_cr) BATCHES_GF
from gl_period_statuses T6,
         gl_je_headers T2,
         gl_je_lines T1,
         gl_code_combinations T4,
         gl_je_batches T3,
         gl_je_categories T7,
         gl_je_sources T8,
         gl_daily_conversion_types gdct,
         &C_LEDGER_FROM
         gl_ledgers L
 where  T6.application_id = 101
     and  T6.ledger_id = T2.ledger_id
     and  (    :C_START_DATE  between T6.start_date and T6.end_date
              or :C_END_DATE     between T6.start_date and T6.end_date
              or (       T6.start_date >= :C_START_DATE
                    and T6.end_date <= :C_END_DATE
                   )
              )
  and  T2.status = 'P'
  and  T2.actual_flag = 'A'
  and  T2.period_name = T6.period_name
  and  T1.je_header_id = T2.je_header_id+0
  and  T1.status = 'P'
  and  T1.effective_date between :C_START_DATE and :C_END_DATE
  and  T4.code_combination_id = T1.code_combination_id+0
  and  T4.summary_flag = 'N'
  and  T4.template_id IS NULL
  and  T4.chart_of_accounts_id = :C_CHART_OF_ACCTS_ID  
  and  T3.je_batch_id = decode(T4.code_combination_id, NULL, T2.je_batch_id, T2.je_batch_id)
  and  T7.je_category_name = decode(T4.code_combination_id, NULL, T2.je_category, T2.je_category)
  and  T8.je_source_name = decode(T4.code_combination_id, NULL, T2.je_source, T2.je_source)
  and  gdct.conversion_type = decode(T4.code_combination_id, NULL, T2.currency_conversion_type,
       T2.currency_conversion_type)
&C_LEDGER_WHERE
&C_WHERE_PERIOD
and &C_WHERE_LEX
&C_WHERE
&C_ACCESS_WHERE
  and  T2.ledger_id = L.LEDGER_ID
  AND DECODE(TO_NUMBER((SELECT GP1.PERIOD_YEAR
      FROM GL_PERIODS GP1
      WHERE GP1.PERIOD_SET_NAME = L.PERIOD_SET_NAME
      AND GP1.PERIOD_TYPE = L.ACCOUNTED_PERIOD_TYPE
      and trunc(:P_END_PERIOD) between GP1.start_date and GP1.end_date
      and GP1.adjustment_period_flag like decode(:P_ADJUSTMENT_PERIODS,'N','N','%')
      and    rownum = 1)) -
      TO_NUMBER((SELECT GP2.PERIOD_YEAR
      FROM GL_PERIODS GP2
      WHERE GP2.PERIOD_SET_NAME = L.PERIOD_SET_NAME
      AND GP2.PERIOD_TYPE = L.ACCOUNTED_PERIOD_TYPE
      and trunc(:P_START_PERIOD) between GP2.start_date and GP2.end_date
      and GP2.adjustment_period_flag like decode(:P_ADJUSTMENT_PERIODS,'N','N','%')
      and rownum = 1)), 0, 'N', DECODE(:P_ADJUSTMENT_PERIODS, 'N', 'Y', 'N')) != 'Y'
--order by  fnd_flex_xml_publisher_apis.process_kff_combination_1('p_balance_seg', 'SQLGL', 'GL#', T4.CHART_OF_ACCOUNTS_ID, NULL, T4.CODE_COMBINATION_ID, 'GL_BALANCING', 'Y', 'VALUE'), T6.start_date, T7.user_je_category_name, T3.name,
       --   T2.name, trunc(T1.effective_date), &P_ORDERBY_ALL, T1.je_line_num
		  order by LEDGER_NAME,fnd_flex_xml_publisher_apis.process_kff_combination_1('p_balance_seg', 'SQLGL', 'GL#', T4.CHART_OF_ACCOUNTS_ID, NULL, T4.CODE_COMBINATION_ID, 'GL_BALANCING', 'Y', 'VALUE'), T6.start_date, T6.period_name,Category_Desc,Category,
		   T3.name ,Batch_Date,Posting_Date,Entry_Name,DOC_SEQUENCE,SOURCE,ENTRY_REF,CURRENCY_RATE,
		   CURRENCY_TYPE,EFFECTIVE_DATE,fnd_flex_xml_publisher_apis.process_kff_combination_1('c_acc_flexfield', 'SQLGL', 'GL#', T4.CHART_OF_ACCOUNTS_ID, NULL, T4.CODE_COMBINATION_ID, 'ALL', 'Y', 'VALUE'),REFERENCE,DESCRIPTION,ENTERED_DR,ENTERED_CR,ACCOUNTED_DR,ACCOUNTED_CR,&P_ORDERBY_ALL
Parameter NameSQL textValidation
Chart of Accounts Id
 
Number
Data Access Set Id
 
Number
Adjustment Periods
 
LOV Oracle
Company
 
Category
 
LOV Oracle
End Date
 
Date
Begin Date
 
Date
Currency
 
LOV Oracle
Ledger/Ledger Set
 
LOV Oracle