4. gl_import_references r

 4. gl_import_references r,
--------------------------------------

/* Formatted on 2020/06/21 00:04 (Formatter Plus v4.8.8) */
SELECT                                --reference_5, reference_6, reference_7,
       SUM (NVL (reference_9, 0)) - SUM (NVL (reference_10, 0)) val
  FROM gl_import_references r,
       (SELECT DISTINCT jh.je_header_id, jl.je_line_num
                    /*gcc.segment1, gb.PERIOD_NAME,gcc.segment7, gcc.CODE_COMBINATION_ID ,gcc.segment6, gcc.CONCATENATED_SEGMENTS,  ap.PERIOD_YEAR, ap.PERIOD_NUM,
                   substr(c(gcc.CODE_COMBINATION_ID),88,30) acct_name,
                    (nvl(gb.begin_balance_dr,0)-nvl(gb.begin_balance_cr,0)) opening_bal,
                               (nvl(gb.period_net_dr,0) - nvl(gb.period_net_cr,0)) period_bal,
                   (nvl(gb.begin_balance_dr,0)-nvl(gb.begin_balance_cr,0) + (nvl(gb.period_net_dr,0) - nvl(gb.period_net_cr,0))) sisterconcernloan, jl.DESCRIPTION, LINE_TYPE_CODE, nvl(ACCOUNTED_DR,0) line_dr, nvl(ACCOUNTED_CR,0)  line_cr
                   ,(nvl(ACCOUNTED_DR,0) - nvl(ACCOUNTED_CR,0)) line_bal            , jl.JE_HEADER_ID, jl.JE_LINE_NUM, jh.DESCRIPTION, jh.JE_CATEGORY, decode(jh.JE_SOURCE,'Manual','1. Manual', 'Inventory','2. Inventory', 'Cost Management', '3. Cost Management','Receivables','4. Receivables','Payables','5. Payables','Others') JE_SOURCE , jh.name,jh.DATE_CREATED, u(jh.CREATED_BY) created_user, jh.JE_BATCH_ID,
                                 jh.EXTERNAL_REFERENCE, jh.DOC_SEQUENCE_VALUE
                   */
        FROM            gl_balances gb,
                        gl_code_combinations_kfv gcc,
                        gl_je_lines jl,
                        gl_je_headers jh,
                        gl_period_statuses ap
                  WHERE gb.code_combination_id = gcc.code_combination_id
                    --and gcc.segment1 not in ('128','129')
                    --('127','131','132','140','145','146','701','702','703','704','705','706','707','708')
                    --select distinct ou from xxprgrptuserbu where rptname=:p_rptname and user_name=:p_user_id) -- (p_org_id)
                    AND gb.ledger_id = 2021
                    AND gb.actual_flag = 'A'
                    AND gb.period_name = :p_period_name1         --high period
                    AND gb.currency_code = 'BDT'
                    AND gcc.segment6 IN
                                        -- ('220034')  and gcc.CODE_COMBINATION_ID=916707
                        (SELECT headid
                           FROM xxprg_bsreport
                          WHERE headname = 'SisterConcernLoan')
                    -- and  gcc.segment6 not in  -- ('220034')
                    AND segment6 IN ('126502', '216200')
                                        -- not in ('220034','126505','126506')
--and gcc.segment1='103'
 --and jh.JE_SOURCE<>'Inventory'
 --and (nvl(gb.begin_balance_dr,0)-nvl(gb.begin_balance_cr,0) + (nvl(gb.period_net_dr,0) - nvl(gb.period_net_cr,0))) <>0
-- group by gcc.segment1
                    AND gb.code_combination_id = jl.code_combination_id
                    AND gb.period_name = jl.period_name
                    AND jl.status = 'P'
                    AND gb.period_name = ap.period_name
                    AND ap.application_id = 101
                    AND ap.period_name NOT LIKE 'Adj%'
                    AND ap.effective_period_num >= 20170005
                    AND jl.je_header_id = jh.je_header_id) q
 WHERE r.je_header_id = q.je_header_id AND r.je_line_num = q.je_line_num
HAVING SUM (NVL (reference_9, 0)) - SUM (NVL (reference_10, 0)) <> 0
--GROUP BY reference_5, reference_6, reference_7