Post date: Apr 21, 2016 3:21:53 AM
--1
select gjh.JE_BATCH_ID, gjh.JE_HEADER_ID, gjh.NAME journal_name, gjb.NAME batch_name,
gjh.je_source SOURCE, gjh.je_category CATEGORY,
gjh.currency_code, gjh.period_name period, gcc.segment4,
gcc.segment1
|| '.'
|| gcc.segment2
|| '.'
|| gcc.segment3
|| '.'
|| gcc.segment4
|| '.'
|| gcc.segment5 Account,
-- || '.'
-- || gcc.segment6
-- || '.'
-- || gcc.segment7
-- || '.'
-- || gcc.segment8 ACCOUNT,
NVL (gjl.entered_dr, 0) debit_valas,
NVL (gjl.entered_cr, 0) credit_valas,
NVL (gjl.accounted_dr, 0) debit_accounted,
NVL (gjl.accounted_cr, 0) credit_accounted,
NVL (gjl.accounted_dr, 0)-NVL (gjl.accounted_cr, 0) Selisih_accounted,
gjl.description
FROM gl_je_headers gjh,
gl_ledgers gll,
gl_je_lines gjl,
gl_je_batches gjb,
gl_code_combinations gcc
WHERE gjh.je_header_id = gjl.je_header_id
AND gjh.je_batch_id = gjb.je_batch_id
and gll.ledger_id = gjl.ledger_id
AND gjl.code_combination_id = gcc.code_combination_id
-- AND gjh.period_name IN ('OCT-13')
-- and gll.short_name='SOBUSWH'
and gcc.segment4='115006'
and gjh.PERIOD_NAME in 'MAR-16'--('JAN-16','FEB-16','MAR-16')
and gjl.LEDGER_ID=2021
and gjh.POSTED_DATE is not null
and gjb.STATUS='P'
order by je_header_id, segment4
--2
/* Formatted on 2016/04/19 16:53 (Formatter Plus v4.8.8) */
SELECT DISTINCT transaction_id,
gjb.NAME nama_batch,
gjh.je_header_id,
gjh.je_source,
gjh.period_name,
gjh.NAME,
xal.ae_header_id,
gjh.description,
xal.code_combination_id,
xal.accounting_class_code,
xal.accounted_dr,
xal.accounted_cr,
gir.gl_sl_link_id,
cidv.transaction_id,
gjh.default_effective_date
FROM xla_ae_lines xal,
gl_import_references gir,
gl_je_headers gjh,
gl_je_batches gjb,
cst_inv_distribution_v cidv,
xla_transaction_entities_upg xteu,
xla_ae_headers xah
WHERE 1 = 1
--and xdl.AE_HEADER_ID=xah.AE_HEADER_ID
AND cidv.transaction_id = xteu.source_id_int_1
AND xteu.entity_id = xah.entity_id
AND gjh.je_batch_id = gjb.je_batch_id
AND xah.ae_header_id = xal.ae_header_id
AND gir.gl_sl_link_id = xal.gl_sl_link_id
AND gir.je_header_id = gjh.je_header_id
AND gjh.ledger_id = 2021
AND xah.event_type_code = 'RMA_RCPT' /*SO_ISSUE'*/--'COGS_RECOGNITION'
and xal.CODE_COMBINATION_ID=1029 --DCOGS account --'SO_ISSUE'
AND xah.accounting_date BETWEEN ('1-Mar-2016') AND ('31-Mar-2016')
ORDER BY cidv.transaction_id, gjh.DEFAULT_EFFECTIVE_DATE
select *
from cst_inv_distribution_v
where 1=1
and transaction_type_id=33 --Sales Order Pick --10008 COGS Recognition
and transaction_date between to_date('1-Mar-2016 00:00:00','DD-MM-YYYY HH24:MI:SS') and to_date('31-Mar-2016 23:59:59','DD-MM-YYYY HH24:MI:SS')
--and reference_account = 1029
select *
from xla_transaction_entities_upg
where source_id_int_1=1058917
select *
from
select *
from xla_events
where entity_id in (
SELECT entity_id
FROM xla_transaction_entities_upg
WHERE 1 = 1
AND source_id_int_1 IN (SELECT transaction_id
FROM cst_inv_distribution_v
WHERE 1 = 1
AND transaction_type_id = 33 --Sales Order Pick --10008 COGS Recognition
AND transaction_date BETWEEN TO_DATE ('1-Mar-2016 00:00:00', 'DD-MM-YYYY HH24:MI:SS') AND TO_DATE ('31-Mar-2016 23:59:59', 'DD-MM-YYYY HH24:MI:SS')
--and reference_account = 1029
)
)
select *
from xla_events
where event_date between ('1-Mar-2016') and ('31-Mar-2016')
and event_type_code='SO_ISSUE'
select *
from xla_ae_headers
where event_type_code='SO_ISSUE'
and accounting_date between ('1-Mar-2016') and ('31-Mar-2016')
/* Formatted on 2016/04/19 16:53 (Formatter Plus v4.8.8) */
SELECT DISTINCT transaction_id,
gjb.NAME nama_batch,
gjh.je_header_id,
gjh.je_source,
gjh.period_name,
gjh.NAME,
xal.ae_header_id,
gjh.description,
xal.code_combination_id,
xal.accounting_class_code,
xal.accounted_dr,
xal.accounted_cr,
gir.gl_sl_link_id,
cidv.transaction_id,
gjh.default_effective_date
FROM xla_ae_lines xal,
gl_import_references gir,
gl_je_headers gjh,
gl_je_batches gjb,
cst_inv_distribution_v cidv,
xla_transaction_entities_upg xteu,
xla_ae_headers xah
WHERE 1 = 1
--and xdl.AE_HEADER_ID=xah.AE_HEADER_ID
AND cidv.transaction_id = xteu.source_id_int_1
AND xteu.entity_id = xah.entity_id
AND gjh.je_batch_id = gjb.je_batch_id
AND xah.ae_header_id = xal.ae_header_id
AND gir.gl_sl_link_id = xal.gl_sl_link_id
AND gir.je_header_id = gjh.je_header_id
AND gjh.ledger_id = 2021
AND xah.event_type_code = 'SO_ISSUE'--'COGS_RECOGNITION' --'SO_ISSUE'
AND xah.accounting_date BETWEEN ('1-Mar-2016') AND ('31-Mar-2016')
ORDER BY cidv.transaction_id, gjh.DEFAULT_EFFECTIVE_DATE
select *
from gl_je_headers
where ledger_id='2021'
select *
from gl_import_references
where gl_sl_link_id=2982467
where gl_sl_link_id in (select gl_sl_link_id
from xla_ae_lines
where ae_header_id in (select ae_header_id
from xla_ae_headers
where event_type_code='SO_ISSUE'
and accounting_date between ('1-Mar-2016') and ('31-Mar-2016')))
select *
from xla_distribution_links
where ae_header_id in (select ae_header_id
from xla_ae_headers
where event_type_code='SO_ISSUE'
and accounting_date between ('1-Mar-2016') and ('31-Mar-2016'))
select *
from xla_events
where event_type_code like 'RMA_RCPT'