Pending batches for the period before closing the period

Pending batches for the period before closing the period
----------------------------------------------------------------------- 

SELECT
 eh.legal_entity_id,
 mp.organization_code,
 gbh.batch_no,
 gbh.actual_start_date,
 GBH.BATCH_CLOSE_DATE,
 DECODE(xah.event_type_code,'MANUAL',xah.event_type_code,'NON-MANUAL')Man_NonMan,
 XAL.ACCOUNTING_CLASS_CODE,
 XAH.Accounting_date,
 XAL.CODE_COMBINATION_ID,
 FND_FLEX_EXT.GET_SEGS('SQLGL','GL#', gcc.chart_of_accounts_id, gcc.code_combination_id) Account,
 TO_CHAR(SUM(NVL(accounted_cr,0))),
 TO_CHAR(SUM(NVL(accounted_dr,0)))
FROM
 XLA_AE_LINES XAL,
 XLA_AE_HEADERS XAH,
 gme_batch_header gbh,
 GMF_XLA_EXTRACT_HEADERS EH,
 GMF_XLA_EXTRACT_LINES EL,
 mtl_parameters mp,
 gl_code_combinations gcc
WHERE xal.ae_header_id = xah.ae_header_id
AND XAL.APPLICATION_ID = XAH.APPLICATION_ID
AND XAL.APPLICATION_ID = 555
AND XAL.code_combination_id = gcc.code_combination_id(+)
AND eh.header_id = el.header_id
AND eh.source_document_id = gbh.batch_id
AND eh.txn_source = 'PM'
AND el.journal_line_type = 'WIP'
AND GBH.BATCH_STATUS = 4
AND XAH.EVENT_ID = EH.EVENT_ID
AND MP.ORGANIZATION_ID = EH.ORGANIZATION_ID
AND EH.LEGAL_ENTITY_ID = 996 --modify with LE
AND MP.ORGANIZATION_CODE = 'PR1' --modify with Organization code
AND ( TO_CHAR(GBH.ACTUAL_START_DATE, 'MON-YYYY') = 'JAN-2014' --Modify the start date
  OR TO_CHAR(GBH.BATCH_CLOSE_DATE,'MON-YYYY') = 'JAN-2014' ) --modify the end date
GROUP BY eh.legal_entity_id,
 mp.organization_code,
 gbh.batch_no,
 gbh.actual_start_date,
 gbh.batch_close_date,
 DECODE(xah.event_type_code,'MANUAL',xah.event_type_code,'NON-MANUAL'),
 XAL.ACCOUNTING_CLASS_CODE,
 XAH.Accounting_date,
 xal.code_combination_id,
 FND_FLEX_EXT.GET_SEGS('SQLGL','GL#', GCC.CHART_OF_ACCOUNTS_ID, GCC.CODE_COMBINATION_ID)
;
 

XLA to GMF eXtract OPM

XLA to GMF eXtract for OPM
---------------------------------
SELECT
   eh.reference_no,
   eh.legal_entity_id,
   eh.ledger_id,
   eh.inventory_item_id,
   eh.organization_id,
   eh.transaction_source_type_id,
   eh.transaction_action_id,
   eh.transaction_type_id,
   eh.entity_code,
   eh.event_class_code,
   eh.event_type_code,
   eh.valuation_cost_type,
   eh.accounted_flag,
   eh.transaction_id,
   xal.code_combination_id,
   xal.ACCOUNTING_CLASS_CODE,
   gcc.concatenated_segments,
   nvl(xal.accounted_dr,0),
   nvl(xal.accounted_cr, 0)
 FROM
    gmf_xla_extract_headers eh,
    xla_ae_headers xah, xla_ae_lines xal,
    gl_code_combinations_kfv  gcc
              WHERE eh.event_id = xah.event_id
              AND xah.ae_header_id = xal.ae_header_id
              AND xah.application_id = 555
              AND eh.transaction_date >= To_Date('01/06/2020 00:00:00','dd-mm-yyyy hh24:mi:ss' )
              AND eh.transaction_date <= To_Date('10/06/2020 23:59:59 ','dd-mm-yyyy hh24:mi:ss' )
              AND xal.code_combination_id = gcc.code_combination_id

MMT -> GMF -> XLA GL

MMT ->  GMF -> XLA GL
----------------------------------------

The Pre-Processor should pick up all Transactions (related to a process organization) for processing where the OPM Costed Flagis not Null, and Create Accounting should set that flag to either 'D' orNull depending on whether the process was run in Draft or Final mode.

Step 1: Create material transaction in table MTL_MATERIAL_TRANSACTIONS- Tables populated:MTL_MATERIAL_TRANSACTIONS- Important columns:MTL_MATERIAL_TRANSACTIONS.OPM_COSTED_FLAG = 'N';Step 2: Run OPM Accounting Pre-Processor- Tables populated:GMF_XLA_EXTRACT_HEADERSGMF_XLA_EXTRACT_LINESXLA_EVENTS- Important columns:GMF_XLA_EXTRACT_HEADERS.ACCOUNTED_FLAG = 'N'GMF_XLA_EXTRACT_HEADERS.ACTUAL_POSTING_DATE IS NULL- Joining fields:MTL_MATERIAL_TRANSACTIONS.TRANSACTION_ID = GMF_XLA_EXTRACT_HEADERS.TRANSACTION_IDGMF_XLA_EXTRACT_HEADERS.EVENT_ID = XLA_EVENTS.EVENT_IDGMF_XLA_EXTRACT_HEADERS.HEADER_ID = GMF_XLA_EXTRACT_LINES.HEADER_IDStep 3: Run Create Accounting (Accounting Program) in DRAFT mode- Tables populated:XLA_AE_HEADERSXLA_AE_LINESXLA_DISTRIBUTION_LINKS- Important columns:XLA_AE_HEADERS.APPLICATION_ID = 555 MTL_MATERIAL_TRANSACTIONS.OPM_COSTED_FLAG = 'D';GMF_XLA_EXTRACT_HEADERS.ACCOUNTED_FLAG = 'D'GMF_XLA_EXTRACT_HEADERS.ACTUAL_POSTING_DATE IS NULL

select xe.* from xla.xla_events xe WHERE XE.EVENT_ID IN (Select event_id from gmf_xla_extract_headers where source_document_id = &batch_id and txn_source = 'PM'); Query 20 - SLA Distribution Links select xte.* from xla_transaction_entities_upg xte where xte.application_id = 555 and xte.entity_id in (select xe.entity_id from xla.xla_events xe WHERE XE.EVENT_ID IN (select EVENT_ID from GMF_XLA_EXTRACT_HEADERS where SOURCE_DOCUMENT_ID = &batch_id and txn_source = 'PM' ) ); Query 21 - SLA Headers SELECT * FROM XLA_AE_HEADERS where application_id = 555 and event_id IN ( Select event_id from gmf_xla_extract_headers where source_document_id =&batch_id and txn_source = 'PM'); Query 22 - SLA Lines SELECT * FROM xla_ae_lines where ae_header_id IN ( SELECT ae_header_id FROM xla_ae_headers where application_id = 555 and event_id IN ( Select event_id from gmf_xla_extract_headers where source_document_id =&batch_id and txn_source = 'PM')); Query 23 - Item Component Class Details select a.* from gl_item_dtl a where a.itemcost_id in (select itemcost_id from gl_item_cst where (inventory_item_id, organization_id,cost_type_id, period_id) IN (select distinct mmt.inventory_item_id, mmt.organization_id,gps.cost_type_id,gps.period_id from gmf_organization_definitions god, gmf_period_statuses gps, gmf_fiscal_policies gfp, cm_mthd_mst mthd, mtl_material_transactions mmt WHERE mmt.transaction_source_type_id     = 5 AND god.organization_id            = mmt.organization_id AND mmt.transaction_source_id      = &&batch_id AND gfp.legal_entity_id            = god.legal_entity_id AND mthd.cost_type_id              = gfp.cost_type_id AND gps.legal_entity_id            = gfp.legal_entity_id AND gps.cost_type_id               = gfp.cost_type_id AND mmt.transaction_date           >= gps.start_date AND mmt.transaction_date           <= gps.end_date))


SELECT
    mmt.TRX_SOURCE_LINE_ID,
    xe.EVENT_ID,
    '60'  AS INTERNAL_CODE,
    'OPM-INV' AS internal_module,
      GJH.JE_HEADER_ID,
    trunc(mmt.TRANSACTION_DATE) AS DOC_DATE,
    gcc.segment1 AS segment1,
    gcc.segment4 AS segment4,
    aps.vendor_name AS party_name,
    NULL user_name,
    NULL approver_name,
    gcc.code_combination_id,
    aps.vendor_name,
    pha.segment1 po_number,
    gjl.je_line_num AS gl_line_no,
    al.description AS particulars,
    NULL AS doc_no,
    gjh.period_name AS gl_period_name,
    gjh.je_source || ' ' || gjc.user_je_category_name AS gl_source,
    gjh.doc_sequence_value AS gl_doc_no,
    gjh.je_category,
    gjh.status AS gl_jv_status,
    gjh.ledger_id set_of_books_id,
    gjh.default_effective_date AS gl_date,
    gjh.ledger_id,
    gjh.period_name AS gjl_period_name,
    gjc.user_je_category_name || '--' || gbh.batch_no || '--' || msi.description description,
    NVL (gjl.accounted_dr, 0) AS gl_acc_dr,
    NVL (gjl.accounted_cr, 0) AS gl_acc_cr,
    mmt.transaction_id AS doc_id,
    NVL (al.accounted_dr, 0) AS xla_acc_dr,
    NVL (al.accounted_cr, 0) AS xla_acc_cr,
                                                 -- FND_FLEX_EXT.GET_SEGS('SQLGL', 'GL#',gcc.chart_of_accounts_id,gcc.code_combination_id) SEGMENTS,
                                                 -- GL_FLEXFIELDS_PKG.get_concat_description( gcc.chart_of_accounts_id, gcc.code_combination_id) GCC_NAME,
    gbh.batch_no checkbook_id,
    mtt.TRANSACTION_TYPE_NAME,
    msi.description item_description
FROM
        gl_code_combinations gcc,
        gl_je_headers gjh,
        gl_je_lines gjl,
        gl_je_batches gjb,
        gl_je_categories gjc,
        gl_import_references gir,
        gme_batch_header gbh,
        xla_ae_headers xah,
        xla_ae_lines al,
        xla_distribution_links dl,
        xla_events xe,
--        xla_transaction_entities xte,
        gmf_xla_extract_headers geh,
        gmf_xla_extract_lines gel,
        mtl_system_items msi,
        mtl_material_transactions mmt,
        apps.mtl_transaction_types mtt,
        rcv_transactions rt,
        ap_suppliers aps,
        po_headers_all pha
--        CM_ACST_LED CCD  
    WHERE
        gcc.code_combination_id = al.code_combination_id
        AND al.application_id = xe.application_id
        AND xah.event_id = xe.event_id
        --   AND xah.entity_id = xte.entity_id
        AND gjh.je_header_id = gir.je_header_id
        AND gir.je_header_id = gjl.je_header_id
        AND gjh.je_header_id = gjl.je_header_id
        and gjb.GROUP_ID = xah.group_id
        AND UPPER (gjh.je_source) = 'INVENTORY'
        --   AND gjh.je_category IN ('RVAL', 'STEP')
        AND gjh.je_category IN ('RELE')
        AND geh.inventory_item_id = msi.inventory_item_id
        AND geh.organization_id = msi.organization_id
        AND gjh.je_category = gjc.je_category_key
        AND gir.je_line_num = gjl.je_line_num
        AND gbh.batch_id = geh.source_document_id
        and gjb.JE_BATCH_ID = gjh.JE_BATCH_ID
        and gjh.JE_HEADER_ID = 743054
        --   AND GJH.JE_HEADER_ID = 1345430
        AND gjb.je_batch_id = gir.je_batch_id
        AND gir.gl_sl_link_id = al.gl_sl_link_id
        AND gir.gl_sl_link_table = al.gl_sl_link_table
        AND al.ae_header_id = xah.ae_header_id
        AND al.ae_header_id = dl.ae_header_id
        AND al.ae_line_num = dl.ae_line_num
        AND dl.event_id = geh.event_id
        AND dl.application_id = 555
        AND dl.source_distribution_type = geh.entity_code
        AND dl.source_distribution_id_num_1 = gel.line_id
        AND geh.header_id = gel.header_id
        AND geh.event_id = gel.event_id
        and geh.TRANSACTION_ID = mmt.TRANSACTION_ID
        and msi.ORGANIZATION_ID = mmt.ORGANIZATION_ID
        AND msi.inventory_item_id = mmt.inventory_item_id
        and gbh.ORGANIZATION_ID = msi.ORGANIZATION_ID
        and mmt.transaction_source_id = gbh.batch_id
        and mmt.TRANSACTION_TYPE_ID = mtt.TRANSACTION_TYPE_ID
--        and ccd.TRANSLINE_ID = mmt.TRX_SOURCE_LINE_ID-
--        and mmt.ORGANIZATION_ID = ccd.ORGANIZATION_ID
--        and mmt.INVENTORY_ITEM_ID = ccd.INVENTORY_ITEM_ID
--       AND gjh.default_effective_date BETWEEN :mFDATE AND :mTODATE
      AND mmt.rcv_transaction_id = rt.transaction_id(+)
       --- AND rt.transaction_type(+) = 'DELIVER'
       AND rt.vendor_id = aps.vendor_id(+)
       AND rt.po_header_id = pha.po_header_id(+)

link from xla.xla_events xe to mf_xla_extract_headers

select xe.* from xla.xla_events xe WHERE XE.EVENT_ID IN  
(Select event_id from gmf_xla_extract_headers where source_document_id = &batch_id and txn_source = 'PM');  

Query 20 - SLA Distribution Links
 select xte.* from xla_transaction_entities_upg xte where xte.application_id = 555 and xte.entity_id in (select xe.entity_id from xla.xla_events xe WHERE XE.EVENT_ID IN (select EVENT_ID from GMF_XLA_EXTRACT_HEADERS where SOURCE_DOCUMENT_ID = &batch_id and txn_source = 'PM' ) );  

Query 21 - SLA Headers
 SELECT * FROM XLA_AE_HEADERS where application_id = 555 and event_id IN ( Select event_id from gmf_xla_extract_headers where source_document_id =&batch_id and txn_source = 'PM');  

Query 22 - SLA Lines  
SELECT * FROM xla_ae_lines where ae_header_id IN ( SELECT ae_header_id FROM xla_ae_headers where application_id = 555 and event_id IN ( Select event_id from gmf_xla_extract_headers where source_document_id =&batch_id and txn_source = 'PM'));  

Query 23 - Item Component Class Details
select a.* from gl_item_dtl a where a.itemcost_id in (select itemcost_id from gl_item_cst where (inventory_item_id, organization_id,cost_type_id, period_id) IN (select distinct mmt.inventory_item_id, mmt.organization_id,gps.cost_type_id,gps.period_id from gmf_organization_definitions god, gmf_period_statuses gps, gmf_fiscal_policies gfp, cm_mthd_mst mthd, mtl_material_transactions mmt WHERE mmt.transaction_source_type_id = 5 AND god.organization_id = mmt.organization_id AND mmt.transaction_source_id = &&batch_id AND gfp.legal_entity_id = god.legal_entity_id AND mthd.cost_type_id = gfp.cost_type_id AND gps.legal_entity_id = gfp.legal_entity_id AND gps.cost_type_id = gfp.cost_type_id AND mmt.transaction_date >= gps.start_date AND mmt.transaction_date <= gps.end_date));

GL to RCV link

SELECT b.NAME                   je_batch_name,

b.description                   je_batch_description,

b.running_total_accounted_dr    je_batch_total_dr,

b.running_total_accounted_cr    je_batch_total_cr,

b.status                        je_batch_status,

b.default_effective_date        je_batch_effective_date,

b.default_period_name           je_batch_period_name,

b.creation_date                 je_batch_creation_date,

u.user_name                     je_batch_created_by,

h.je_category                   je_header_category,

h.je_source                     je_header_source,

h.period_name                   je_header_period_name,

h.NAME                          je_header_journal_name,

h.status                        je_header_journal_status,

h.creation_date                 je_header_created_date,

u1.user_name                    je_header_created_by,

h.description                   je_header_description,

h.running_total_accounted_dr    je_header_total_acctd_dr,

h.running_total_accounted_cr    je_header_total_acctd_cr,

l.je_line_num                   je_lines_line_number,

l.ledger_id                     je_lines_ledger_id,

glcc.concatenated_segments      je_lines_ACCOUNT,

l.entered_dr                    je_lines_entered_dr,

l.entered_cr                    je_lines_entered_cr,

l.accounted_dr                  je_lines_accounted_dr,

l.accounted_cr                  je_lines_accounted_cr,

l.description                   je_lines_description,

glcc1.concatenated_segments     xla_lines_account,

xlal.accounting_class_code      xla_lines_acct_class_code,

xlal.accounted_dr               xla_lines_accounted_dr,

xlal.accounted_cr               xla_lines_accounted_cr,

xlal.description                xla_lines_description,

xlal.accounting_date            xla_lines_accounting_date,

xlate.entity_code               xla_trx_entity_code,

xlate.source_id_int_1           xla_trx_source_id_int_1,

xlate.source_id_int_2           xla_trx_source_id_int_2,

xlate.source_id_int_3           xla_trx_source_id_int_3,

xlate.security_id_int_1         xla_trx_security_id_int_1,

xlate.security_id_int_2         xla_trx_security_id_int_2,

xlate.transaction_number        xla_trx_transaction_number,

rcvt.transaction_type           rcv_trx_transaction_type,

rcvt.transaction_date           rcv_trx_transaction_date,

rcvt.quantity                   rcv_trx_quantity,

rcvt.shipment_header_id         rcv_trx_shipment_header_id,

rcvt.shipment_line_id           rcv_trx_shipment_line_id,

rcvt.destination_type_code      rcv_trx_destination_type_code,

rcvt.po_header_id               rcv_trx_po_header_id,

rcvt.po_line_id                 rcv_trx_po_line_id,

rcvt.po_line_location_id        rcv_trx_po_line_location_id,

rcvt.po_distribution_id         rcv_trx_po_distribution_id,

rcvt.vendor_id                  rcv_trx_vendor_id,

rcvt.vendor_site_id             rcv_trx_vendor_site_id

FROM                           

gl_je_batches                   b,

gl_je_headers                   h,

gl_je_lines                     l,

fnd_user                        u,

fnd_user                        u1,

gl_code_combinations_kfv        glcc,

gl_code_combinations_kfv        glcc1,

gl_import_references            gir,

xla_ae_lines                    xlal,

xla_ae_headers                  xlah,

xla_events                      xlae,

xla.xla_transaction_entities    xlate,

rcv_transactions                rcvt

WHERE

b.created_by                    = u.user_id

AND h.created_by                = u1.user_id

AND b.je_batch_id               = h.je_batch_id

AND h.je_header_id              = l.je_header_id

AND l.code_combination_id       = glcc.code_combination_id

AND l.je_header_id              = gir.je_header_id

AND l.je_line_num               = gir.je_line_num

AND gir.gl_sl_link_table        = xlal.gl_sl_link_table

AND gir.gl_sl_link_id           = xlal.gl_sl_link_id

AND xlal.application_id         = xlah.application_id

AND xlal.ae_header_id           = xlah.ae_header_id

AND xlal.code_combination_id    = glcc1.code_combination_id

AND xlah.application_id         = xlae.application_id

AND xlah.event_id               = xlae.event_id

AND xlae.application_id         = xlate.application_id

AND xlae.entity_id              = xlate.entity_id

AND xlate.source_id_int_1       = rcvt.transaction_id

AND h.je_category               = 'Receiving'

AND b.default_period_name       = '01_APR-2009'

ORDER BY h.je_category;

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

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
 

1. GL Balances

1.  GL Balances
-------------------------------------
-- 2nd check with and without zero --
--------------------
select gb.PERIOD_NAME, sum(nvl(gb.begin_balance_dr,0)-nvl(gb.begin_balance_cr,0)) opening_bal,
            sum(nvl(gb.period_net_dr,0) - nvl(gb.period_net_cr,0)) period_bal,
sum(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
from gl_balances gb,
gl_code_combinations_kfv gcc, gl_period_statuses ap --, gl_je_lines jl
where gb.code_combination_id = gcc.code_combination_id
and gb.ledger_id             = 2021
and gb.actual_flag           = 'A'
and gb.currency_code         = 'BDT'
and  gcc.segment6 in -- ('126502','216500') --
 (select headid from xxprg_bsreport where headname = 'SisterConcernLoan')
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 (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 ap.PERIOD_YEAR, ap.PERIOD_NUM, gb.PERIOD_NAME  --, gcc.segment6, gcc.segment7
 order by ap.PERIOD_YEAR desc, ap.PERIOD_NUM desc,  gb.PERIOD_NAME  --, gcc.segment6, gcc.segment7