3. JE SOURCES-

3. JE SOURCES-
------------------------------


--- 3rd step --- Line source suppary and details -------------------------------------------
select PERIOD_NAME,segment6, sum(nvl(line_bal,0)) line_period_bal, PERIOD_YEAR, PERIOD_NUM from (
select 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 in ('146','132','140')
 --('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 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
) group by PERIOD_YEAR, PERIOD_NUM,PERIOD_NAME,segment6-- , CODE_COMBINATION_ID
order by PERIOD_YEAR desc, PERIOD_NUM desc --,CODE_COMBINATION_ID