合计小计--CUX_DETAIL_LEDGER_PKG(单层循环小计合计参考包)

CREATE OR REPLACE PACKAGE BODY CUX_DETAIL_LEDGER_PKG IS

   ---输出报表
  PROCEDURE output_report(p_ledger_id     IN NUMBER,
                          p_company       IN VARCHAR2,
                          P_period_name_f IN VARCHAR2,
                          P_period_name_t IN VARCHAR2,
                          P_je_number_f   IN VARCHAR2,
                          p_je_number_t   IN VARCHAR2,
                          p_currency      IN VARCHAR2,
                          P_create_date_f IN VARCHAR2,
                          P_create_date_t IN VARCHAR2,
                          p_coa_ac_f      IN VARCHAR2,
                          p_coa_ac_t      IN VARCHAR2) IS   
  
    CURSOR ac_data /*(p_period_name IN VARCHAR2) */
    IS
      SELECT DISTINCT gcc.concatenated_segments con_seg,
                      cov.description || '.' || ccv.description || '.' ||
                      acv.description || '.' || ICv.description || '.' ||
                      PTv.description || '.' || PJv.description || '.' ||
                      sp1_v.description || '.' || sp2_v.description account_desc
        FROM gl_je_headers          jh,
             gl_je_lines            jl,
             cux.qgl_sequence_value qsv,
             
             gl_code_combinations_kfv gcc,
             fnd_flex_value_sets      cos,
             fnd_flex_values_vl       cov
             
            ,
             fnd_flex_value_sets ccs,
             fnd_flex_values_vl  ccv
             
            ,
             fnd_flex_value_sets acs,
             fnd_flex_values_vl  acv
             
            ,
             fnd_flex_value_sets ICs,
             fnd_flex_values_vl  ICv,
             
             fnd_flex_value_sets PTs,
             fnd_flex_values_vl  PTv
             
            ,
             fnd_flex_value_sets PJs,
             fnd_flex_values_vl  PJv
             
            ,
             fnd_flex_value_sets sp1_s,
             fnd_flex_values_vl  sp1_v
             
            ,
             fnd_flex_value_sets sp2_s,
             fnd_flex_values_vl  sp2_v
      
       WHERE jh.je_header_id = jl.je_header_id
         AND jl.code_combination_id = gcc.code_combination_id
         AND jh.status = 'P'
         AND jh.je_header_id = qsv.je_header_id(+)
            
         AND gcc.segment1 = cov.flex_value
         AND cov.flex_value_set_id = cos.flex_value_set_id
         AND cos.flex_value_set_name = 'EWPT_COA_CO' --公司段
            
         AND gcc.segment2 = ccv.flex_value
         AND ccv.flex_value_set_id = ccs.flex_value_set_id
         AND ccs.flex_value_set_name = 'EWPT_COA_CC' --部门段
            
         AND gcc.segment3 = acv.flex_value
         AND acv.flex_value_set_id = acs.flex_value_set_id
         AND acs.flex_value_set_name = 'EWPT_COA_AC' --科目段
            
         AND gcc.segment4 = ICv.flex_value
         AND ICv.flex_value_set_id = ICs.flex_value_set_id
         AND ICs.flex_value_set_name = 'EWPT_COA_IC' --往来段
            
         AND gcc.segment5 = PTv.flex_value
         AND PTv.flex_value_set_id = PTs.flex_value_set_id
         AND PTs.flex_value_set_name = 'EWPT_COA_PT' --产品门类段
            
         AND gcc.segment6 = PJv.flex_value
         AND PJv.flex_value_set_id = PJs.flex_value_set_id
         AND PJs.flex_value_set_name = 'EWPT_COA_PJ' --项目段
            
         AND gcc.segment7 = sp1_v.flex_value
         AND sp1_v.flex_value_set_id = sp1_s.flex_value_set_id
         AND sp1_s.flex_value_set_name = 'EWPT_COA_SP1' --质量段
            
         AND gcc.segment8 = sp2_v.flex_value
         AND sp2_v.flex_value_set_id = sp2_s.flex_value_set_id
         AND sp2_s.flex_value_set_name = 'EWPT_COA_SP2' --备用段
         --AND nvl(jl.entered_dr, jl.entered_cr) > 0--add by laisuli 20131113--deleted by wrh 20140717:此条件导致部分负金额不显示（14062959，2014-06，10.0.12310301.0.0.0.0.0）
         AND jh.ledger_id = p_ledger_id
         AND gcc.segment1 = p_company
            -- and jl.period_name =p_period_name
         AND jl.period_name >= P_period_name_f
         AND jl.period_name <= P_period_name_t
            
         AND (p_currency IS NULL OR jh.currency_code = p_currency)
         AND jh.default_effective_date >=
             nvl(to_date(P_create_date_f, 'YYYY-MM-DD'),
                 jh.default_effective_date)
         AND jh.default_effective_date <=
             nvl(to_date(P_create_date_t, 'YYYY-MM-DD') + 0.99999,
                 jh.default_effective_date)
            /*and (jh.doc_sequence_value >= P_je_number_f or
                P_je_number_f is null)
            and (jh.doc_sequence_value <= P_je_number_t or
                P_je_number_t is null)*/
         AND (qsv.sequence_value >= P_je_number_f OR P_je_number_f IS NULL)
         AND (qsv.sequence_value <= P_je_number_t OR P_je_number_t IS NULL)
            
         AND (l_segment1_from IS NULL OR gcc.segment1 >= l_segment1_from)
         AND (l_segment1_to IS NULL OR gcc.segment1 <= l_segment1_to)
            
         AND (l_segment2_from IS NULL OR gcc.segment2 >= l_segment2_from)
         AND (l_segment2_to IS NULL OR gcc.segment2 <= l_segment2_to)
            
         AND (l_segment3_from IS NULL OR gcc.segment3 >= l_segment3_from)
         AND (l_segment3_to IS NULL OR gcc.segment3 <= l_segment3_to)
            
         AND (l_segment4_from IS NULL OR gcc.segment4 >= l_segment4_from)
         AND