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

2 Subledger -Line Balances

Subledger -Line Balances
------------------------------
create table xxmgl_je_bal as
--- 3rd step --- Line source suppary and details -------------------------------------------
select segment1,segment6, PERIOD_NAME, JE_SOURCE , 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, ACCOUNTED_DR line_dr, ACCOUNTED_CR  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  --('126502','216500')  --
(select headid from xxprg_bsreport where headname = 'SisterConcernLoan')
--and gcc.segment1='103'
 --and jh.JE_SOURCE<>'Manual'
 -- 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, JE_SOURCE, segment1,segment6
order by PERIOD_YEAR desc, PERIOD_NUM desc


select period_name,segment6, sum(nvl(line_period_bal,0)) line_period_bal-- ,   je_source
 from xxmgl_je_bal where period_name='May-19-20'
group by period_name,  period_year, period_num ,segment6--, je_source
order by period_year desc, period_num desc


select period_name, segment6,sum(nvl(line_period_bal,0)) line_period_bal --   je_source
 from xxmgl_je_bal
group by period_name, segment6, period_year, period_num--je_source
order by period_year desc, period_num desc

AR- Receipt Class, Method and Remittance Bank Account Query

AR- Receipt Method and Remittance Bank Account Query
--------------------------------------------------------------------

select rc.name Receipt_Class, rm.name Receipt_Method, rma.REMIT_BANK_ACCT_USE_ID, bb.BANK_NAME, bb.BANK_BRANCH_NAME,
cba.BANK_ACCOUNT_NAME, cba.BANK_ACCOUNT_NUM, cba.account_owner_org_id ACCT_OWNER_LE_ID ,cba.CURRENCY_CODE,
rma.CASH_CCID, rma.ON_ACCOUNT_CCID, rma.UNAPPLIED_CCID, rma.UNIDENTIFIED_CCID, rma.BANK_CHARGES_CCID , rma.RECEIPT_CLEARING_CCID, rma.REMITTANCE_CCID
, c(rma.CASH_CCID)
from ar_receipt_methods rm, ar_receipt_classes rc, ar_receipt_method_accounts_all rma,
ce_bank_accounts cba, ce_bank_acct_uses_all ba, ce_bank_branches_v bb, gl_code_combinations_kfv c
where rc.RECEIPT_CLASS_ID = rm.RECEIPT_CLASS_ID
and rm.RECEIPT_METHOD_ID = rma.RECEIPT_METHOD_ID
and rma.REMIT_BANK_ACCT_USE_ID = ba.BANK_ACCT_USE_ID
and rma.ORG_ID = ba.ORG_ID
and ba.BANK_ACCOUNT_ID = cba.BANK_ACCOUNT_ID
and cba.BANK_ID = bb.BANK_PARTY_ID
and cba.BANK_BRANCH_ID = bb.BRANCH_PARTY_ID
and rma.CASH_CCID=c.CODE_COMBINATION_ID and c.segment6='220034'