Post date: Nov 06, 2013 1:8:9 PM
--set linesize 700
--set pagesize 32000
--set heading on
--set colsep '~'
--repheader page left '&4 Ledger from &1 to &2~~~~~~~~~~~~~~~~~'
--COLUMN DR1 FORMAT 999,999,999,999,990.99
--COLUMN CR1 FORMAT 999,999,999,999,990.99
--COLUMN USD_DR FORMAT 999,999,999,999,990.99
--COLUMN USD_CR FORMAT 999,999,999,999,990.99
--COLUMN USD_BLNCE FORMAT 999,999,999,999,990.99
--BREAK ON ACC NODUP
select acc ACC
, je_source JE_SOURCE
, je_category JE_CATEGORY
, header_desc HEADER_DESC
, line_desc LINE_DESC
, line_desc_02 LINE_DESC_02
, eff_date EFF_DATE
, cur CUR
, ccid CCID
, dr1 DR1
, cr1 CR1
, usd_dr USD_DR
, usd_cr USD_CR
, usd_blnce USD_BLNCE
, ref_sort REF_SORT
, seg_01 SEG_01
, seg_03 SEG_03
, '.' dummy
from (
(SELECT acc ACC
, je_source JE_SOURCE
, je_category JE_CATEGORY
, h_desc HEADER_DESC
, l_desc LINE_DESC
, line_desc_02 LINE_DESC_02
, eff_date EFF_DATE
, cur CUR
, ccid CCID
, nvl(entered_dr,0) DR1
, nvl(entered_cr,0) CR1
, nvl(accounted_dr,0) USD_DR
, nvl(accounted_cr,0) USD_CR
, nvl(accounted_dr,0)-nvl(accounted_cr,0) USD_BLNCE
, 'M' REF_SORT
, seg_01 SEG_01
, seg_03 SEG_03
, '.' dummy
FROM (
(SELECT DISTINCT GCC.SEGMENT1 seg_01
, GCC.SEGMENT3 seg_03
, GJH.JE_SOURCE je_source
, GJH.JE_CATEGORY je_category
, substr(GJH.DESCRIPTION,1,50) h_desc
, substr(GJL.DESCRIPTION,1,70) l_desc
, RCTLG.GL_DATE eff_date
, substr(GJH.CURRENCY_CODE,1,3) cur
, GCC.code_combination_id ccid
, substr(GCC.SEGMENT1||'.'||GCC.SEGMENT2||'.'||GCC.SEGMENT3||'.'||GCC.SEGMENT4||'.'||GCC.SEGMENT5,1,25) acc
, XAL.ENTERED_DR entered_dr
, XAL.ENTERED_CR entered_cr
, XAL.ACCOUNTED_DR accounted_dr
, XAL.ACCOUNTED_CR accounted_cr
, rcta.trx_number LINE_DESC_02
, 'M' ref_sort
, '.' dummy
, GJL.JE_HEADER_ID
, GJL.JE_LINE_NUM
FROM GL_JE_HEADERS GJH
, GL_JE_LINES GJL
, GL_CODE_COMBINATIONS GCC
, GL_IMPORT_REFERENCES GIR
, xla_ae_lines xal
, xla.xla_distribution_links xdl
, ra_customer_trx_all rcta
, ra_cust_trx_line_gl_dist_all rctlg
WHERE GJH.JE_SOURCE = 'Receivables'
AND GJH.JE_CATEGORY = 'Sales Invoices'
AND GJH.LEDGER_ID = '&3'
AND GJH.POSTED_DATE IS NOT NULL
AND RCTLG.GL_DATE>=to_date('&1','YYYY/MM/DD HH24:MI:SS')
AND RCTLG.GL_DATE<=to_date('&2','YYYY/MM/DD HH24:MI:SS')
AND GJH.JE_HEADER_ID=GJL.JE_HEADER_ID
AND GJH.LEDGER_ID = GJL.LEDGER_ID
AND GJL.JE_HEADER_ID=GIR.JE_HEADER_ID
AND GJL.JE_LINE_NUM=GIR.JE_LINE_NUM
AND GJL.CODE_COMBINATION_ID=GCC.CODE_COMBINATION_ID
AND rcta.customer_trx_id = rctlg.customer_trx_id
AND gir.gl_sl_link_id = xal.gl_sl_link_id
AND xal.ae_header_id = xdl.ae_header_id
AND xal.ae_line_num = xdl.ae_line_num
AND xdl.source_distribution_id_num_1 = rctlg.cust_trx_line_gl_dist_id
)
UNION ALL
(SELECT DISTINCT GCC.SEGMENT1 seg_01
, GCC.SEGMENT3 seg_03
, GJH.JE_SOURCE je_source
, GJH.JE_CATEGORY je_category
, substr(GJH.DESCRIPTION,1,50) h_desc
, substr(GJL.DESCRIPTION,1,70) l_desc
, acrh.GL_DATE eff_date
, substr(GJH.CURRENCY_CODE,1,3) cur
, GCC.code_combination_id ccid
, substr(GCC.SEGMENT1||'.'||GCC.SEGMENT2||'.'||GCC.SEGMENT3||'.'||GCC.SEGMENT4||'.'||GCC.SEGMENT5,1,25) acc
, XAL.ENTERED_DR entered_dr
, XAL.ENTERED_CR entered_cr
, XAL.ACCOUNTED_DR accounted_dr
, XAL.ACCOUNTED_CR accounted_cr
, acra.receipt_number LINE_DESC_02
, 'M' ref_sort
, '.' dummy
, GJL.JE_HEADER_ID
, GJL.JE_LINE_NUM
FROM GL_JE_HEADERS GJH
, GL_JE_LINES GJL
, GL_CODE_COMBINATIONS GCC
, GL_IMPORT_REFERENCES GIR
, xla_ae_lines xal
, ar_cash_receipts_all acra
, xla_events xe
, xla_ae_headers xah
, xla_transaction_entities xte
, ar_cash_receipt_history_all acrh
WHERE GJH.JE_SOURCE = 'Receivables'
AND GJH.JE_CATEGORY = 'Receipts'
AND GJH.LEDGER_ID = '&3'
AND GJH.POSTED_DATE IS NOT NULL
AND acrh.GL_DATE>=to_date('&1','YYYY/MM/DD HH24:MI:SS')
AND acrh.GL_DATE<=to_date('&2','YYYY/MM/DD HH24:MI:SS')
AND GJH.JE_HEADER_ID=GJL.JE_HEADER_ID
AND GJH.LEDGER_ID = GJL.LEDGER_ID
AND GJL.JE_HEADER_ID=GIR.JE_HEADER_ID
AND GJL.JE_LINE_NUM=GIR.JE_LINE_NUM
AND GJL.CODE_COMBINATION_ID=GCC.CODE_COMBINATION_ID
AND gir.gl_sl_link_id = xal.gl_sl_link_id
AND xah.ae_header_id = xal.ae_header_id
AND xah.event_id = xe.event_id
AND xah.application_id = xal.application_id
AND xah.entity_id = xte.entity_id
--AND xe.event_type_code = 'RECP_CREATE'
AND xte.application_id = 222
AND xah.application_id = 222
AND xte.entity_code = 'RECEIPTS'
AND xte.source_id_int_1 = acra.cash_receipt_id
AND acrh.CASH_RECEIPT_ID = acra.cash_receipt_id
)
UNION ALL
(SELECT DISTINCT GCC.SEGMENT1 seg_01
, GCC.SEGMENT3 seg_03
, GJH.JE_SOURCE je_source
, GJH.JE_CATEGORY je_category
, substr(GJH.DESCRIPTION,1,50) h_desc
, substr(GJL.DESCRIPTION,1,70) l_desc
, RCTLG.GL_DATE eff_date
, substr(GJH.CURRENCY_CODE,1,3) cur
, GCC.code_combination_id ccid
, substr(GCC.SEGMENT1||'.'||GCC.SEGMENT2||'.'||GCC.SEGMENT3||'.'||GCC.SEGMENT4||'.'||GCC.SEGMENT5,1,25) acc
, XAL.ENTERED_DR entered_dr
, XAL.ENTERED_CR entered_cr
, XAL.ACCOUNTED_DR accounted_dr
, XAL.ACCOUNTED_CR accounted_cr
, rcta.trx_number LINE_DESC_02
, 'M' ref_sort
, '.' dummy
, GJL.JE_HEADER_ID
, GJL.JE_LINE_NUM
FROM GL_JE_HEADERS GJH
, GL_JE_LINES GJL
, GL_CODE_COMBINATIONS GCC
, GL_IMPORT_REFERENCES GIR
, xla_ae_lines xal
, xla.xla_distribution_links xdl
, ra_customer_trx_all rcta
, ra_cust_trx_line_gl_dist_all rctlg
WHERE GJH.JE_SOURCE = 'Receivables'
AND GJH.JE_CATEGORY = 'Credit Memos'
AND GJH.LEDGER_ID = '&3'
AND GJH.POSTED_DATE IS NOT NULL
AND RCTLG.GL_DATE>=to_date('&1','YYYY/MM/DD HH24:MI:SS')
AND RCTLG.GL_DATE<=to_date('&2','YYYY/MM/DD HH24:MI:SS')
AND GJH.JE_HEADER_ID=GJL.JE_HEADER_ID
AND GJH.LEDGER_ID = GJL.LEDGER_ID
AND GJL.JE_HEADER_ID=GIR.JE_HEADER_ID
AND GJL.JE_LINE_NUM=GIR.JE_LINE_NUM
AND GJL.CODE_COMBINATION_ID=GCC.CODE_COMBINATION_ID
AND rcta.customer_trx_id = rctlg.customer_trx_id
AND gir.gl_sl_link_id = xal.gl_sl_link_id
AND xal.ae_header_id = xdl.ae_header_id
AND xal.ae_line_num = xdl.ae_line_num
AND xdl.source_distribution_id_num_1 = rctlg.cust_trx_line_gl_dist_id
)
UNION ALL
(SELECT DISTINCT GCC.SEGMENT1 seg_01
, GCC.SEGMENT3 seg_03
, GJH.JE_SOURCE je_source
, GJH.JE_CATEGORY je_category
, substr(GJH.DESCRIPTION,1,50) h_desc
, substr(aida.DESCRIPTION,1,70) l_desc
, AIA.GL_DATE eff_date
, substr(GJH.CURRENCY_CODE,1,3) cur
, GCC.CODE_COMBINATION_ID ccid
, substr(GCC.SEGMENT1||'.'||GCC.SEGMENT2||'.'||GCC.SEGMENT3||'.'||GCC.SEGMENT4||'.'||GCC.SEGMENT5,1,25) acc
, XAL.ENTERED_DR entered_dr
, XAL.ENTERED_CR entered_cr
, XAL.ACCOUNTED_DR accounted_dr
, XAL.ACCOUNTED_CR accounted_cr
, AIA.INVOICE_NUM LINE_DESC_02
, 'M' ref_sort
, '.' dummy
, GJL.JE_HEADER_ID
, GJL.JE_LINE_NUM
FROM GL_JE_HEADERS GJH
, GL_JE_LINES GJL
, GL_CODE_COMBINATIONS GCC
, GL_IMPORT_REFERENCES GIR
, ap_invoices_all AIA
, ap_invoice_lines_all aila
, ap.ap_invoice_distributions_all aida
, apps.xla_ae_lines xal
, xla.xla_distribution_links xdl
WHERE GJH.JE_SOURCE = 'Payables'
AND GJH.JE_CATEGORY = 'Purchase Invoices'
AND GJH.LEDGER_ID = '&3'
AND GJH.POSTED_DATE IS NOT NULL
AND AIA.GL_DATE>=to_date('&1','YYYY/MM/DD HH24:MI:SS')
AND AIA.GL_DATE<=to_date('&2','YYYY/MM/DD HH24:MI:SS')
AND GJH.JE_HEADER_ID=GJL.JE_HEADER_ID
AND GJH.LEDGER_ID = GJL.LEDGER_ID
AND GJL.JE_HEADER_ID=GIR.JE_HEADER_ID
AND GJL.JE_LINE_NUM=GIR.JE_LINE_NUM
AND GJL.CODE_COMBINATION_ID=GCC.CODE_COMBINATION_ID
and aia.invoice_id = aila.invoice_id
and aida.invoice_id = aia.invoice_id
and aida.invoice_line_number = aila.line_number
AND gir.gl_sl_link_id = xal.gl_sl_link_id
AND xal.ae_header_id = xdl.ae_header_id
AND xal.ae_line_num = xdl.ae_line_num
AND xdl.source_distribution_id_num_1 = aida.invoice_distribution_id
)
UNION ALL
(SELECT DISTINCT GCC.SEGMENT1 seg_01
, GCC.SEGMENT3 seg_03
, GJH.JE_SOURCE je_source
, GJH.JE_CATEGORY je_category
, substr(GJH.DESCRIPTION,1,50) h_desc
, substr(ACA.DESCRIPTION,1,70) l_desc
, ACA.CHECK_DATE eff_date
, substr(GJH.CURRENCY_CODE,1,3) cur
, GCC.CODE_COMBINATION_ID ccid
, substr(GCC.SEGMENT1||'.'||GCC.SEGMENT2||'.'||GCC.SEGMENT3||'.'||GCC.SEGMENT4||'.'||GCC.SEGMENT5,1,25) acc
, XAL.ENTERED_DR entered_dr
, XAL.ENTERED_CR entered_cr
, XAL.ACCOUNTED_DR accounted_dr
, XAL.ACCOUNTED_CR accounted_cr
, to_char(ACA.CHECK_NUMBER) LINE_DESC_02
, 'M' ref_sort
, '.' dummy
, GJL.JE_HEADER_ID
, GJL.JE_LINE_NUM
FROM GL_JE_HEADERS GJH
, GL_JE_LINES GJL
, GL_CODE_COMBINATIONS GCC
, GL_IMPORT_REFERENCES GIR
, AP_CHECKS_ALL ACA
, ap_payment_history_all apha
, xla_events xe
, xla_ae_headers xah
, xla_ae_lines xal
WHERE GJH.JE_SOURCE = 'Payables'
AND GJH.JE_CATEGORY = 'Payments'
AND GJH.LEDGER_ID = '&3'
AND GJH.POSTED_DATE IS NOT NULL
AND ACA.CHECK_DATE>=to_date('&1','YYYY/MM/DD HH24:MI:SS')
AND ACA.CHECK_DATE<=to_date('&2','YYYY/MM/DD HH24:MI:SS')
and apha.check_id = ACA.check_id
AND xah.ae_header_id = xal.ae_header_id
AND xah.event_id = xe.event_id
AND xah.application_id = xal.application_id
and xe.event_id = apha.accounting_event_id
--AND xe.event_type_code = 'PAYMENT CREATED'
AND gir.gl_sl_link_id = xal.gl_sl_link_id
AND GJH.JE_HEADER_ID=GJL.JE_HEADER_ID
AND GJH.LEDGER_ID = GJL.LEDGER_ID
AND GJL.JE_HEADER_ID=GIR.JE_HEADER_ID
AND GJL.JE_LINE_NUM=GIR.JE_LINE_NUM
AND GJL.CODE_COMBINATION_ID=GCC.CODE_COMBINATION_ID
)
UNION ALL
(SELECT DISTINCT C.segment1 seg_01
, C.segment3 seg_03
, A.JE_SOURCE je_source
, A.JE_CATEGORY je_category
, substr(A.DESCRIPTION,1,50) h_desc
, substr(B.DESCRIPTION,1,50) l_desc
, B.EFFECTIVE_DATE eff_date
, substr(A.CURRENCY_CODE,1,3) cur
, C.code_combination_id ccid
, substr(C.SEGMENT1||'.'||C.SEGMENT2||'.'||C.SEGMENT3||'.'||C.SEGMENT4||'.'||C.SEGMENT5,1,25) acc
, B.ENTERED_DR entered_dr
, B.ENTERED_CR entered_cr
, B.ACCOUNTED_DR accounted_dr
, B.ACCOUNTED_CR accounted_cr
, 'n/a' line_desc_02
, 'M' ref_sort
, '.' dummy
, B.JE_HEADER_ID
, B.JE_LINE_NUM
FROM GL_JE_HEADERS A
, GL_JE_LINES B
, GL_CODE_COMBINATIONS C
WHERE A.JE_SOURCE NOT IN ('Receivables','Payables')
AND A.JE_CATEGORY NOT IN ('Sales Invoices','Receipts','Credit Memos','Payments','Purchase Invoices')
AND A.LEDGER_ID = '&3'
AND A.POSTED_DATE IS NOT NULL
AND B.EFFECTIVE_DATE>=to_date('&1','YYYY/MM/DD HH24:MI:SS')
AND B.EFFECTIVE_DATE<=to_date('&2','YYYY/MM/DD HH24:MI:SS')
AND A.JE_HEADER_ID=B.JE_HEADER_ID
AND A.LEDGER_ID = B.LEDGER_ID
AND B.CODE_COMBINATION_ID=C.CODE_COMBINATION_ID
)
)
)
UNION ALL
select acc1 ACC
, '-' JE_SOURCE
, '-' JE_CATEGORY
, descr HEADER_DESC
, '-' LINE_DESC
, '-' LINE_DESC_02
, CASE
WHEN ref_sort='A' THEN to_date('&1','YYYY/MM/DD HH24:MI:SS')-1
WHEN ref_sort='Z' THEN to_date('&2','YYYY/MM/DD HH24:MI:SS')
ELSE to_date('&2','YYYY/MM/DD HH24:MI:SS')
END EFF_DATE
, '-' CUR
, ccid1 CCID
, 0 DR1
, 0 CR1
, 0 USD_DR
, 0 USD_CR
, blnce USD_BLNCE
, ref_sort REF_SORT
, seg_01 SEG_01
, seg_03 SEG_03
, '.' DUMMY
from (select ccid1 ccid1
, acc1 acc1
, beginning_b blnce
, seg_01 seg_01
, seg_03 seg_03
, 'BEGINNING BALANCE '||acc1 descr
, 'A' ref_sort
from (SELECT nvl(BB.ccid,EB.ccid) ccid1
, EB.ccid ccid2
, nvl(BB.account,EB.account) acc1
, EB.account acc2
, nvl(BB.usd_dr-BB.usd_cr,0) beginning_b
, nvl(EB.usd_dr-EB.usd_cr,0) ending_b
, EB.seg_01 seg_01
, EB.seg_03 seg_03
, '.' dummy
FROM (SELECT ALL C.segment1 seg_01
, C.segment3 seg_03
, C.code_combination_id ccid
, substr(C.SEGMENT1||'.'||C.SEGMENT2||'.'||C.SEGMENT3||'.'||C.SEGMENT4||'.'||C.SEGMENT5,1,25) account
, nvl(sum(B.ACCOUNTED_DR),0) usd_dr
, nvl(sum(B.ACCOUNTED_CR),0) usd_cr
, 'A' ref_sort
, '.' dummy
FROM GL_JE_HEADERS A
, GL_JE_LINES B
, GL_CODE_COMBINATIONS C
WHERE A.LEDGER_ID='&3'
AND A.POSTED_DATE IS NOT NULL
AND B.EFFECTIVE_DATE<=to_date('&1','YYYY/MM/DD HH24:MI:SS')-1
AND A.JE_HEADER_ID=B.JE_HEADER_ID
AND A.LEDGER_ID = B.LEDGER_ID --A.SET_OF_BOOKS_ID=B.SET_OF_BOOKS_ID
AND B.CODE_COMBINATION_ID=C.CODE_COMBINATION_ID
group by C.CODE_COMBINATION_ID
, C.SEGMENT1
, C.SEGMENT2
, C.SEGMENT3
, C.SEGMENT4
, C.SEGMENT5) BB
, (SELECT ALL C.segment1 seg_01
, C.segment3 seg_03
, C.code_combination_id ccid
, substr(C.SEGMENT1||'.'||C.SEGMENT2||'.'||C.SEGMENT3||'.'||C.SEGMENT4||'.'||C.SEGMENT5,1,25) account
, nvl(sum(B.ACCOUNTED_DR),0) usd_dr
, nvl(sum(B.ACCOUNTED_CR),0) usd_cr
, 'A' ref_sort
, '.' dummy
FROM GL_JE_HEADERS A
, GL_JE_LINES B
, GL_CODE_COMBINATIONS C
WHERE A.LEDGER_ID='&3'
AND A.POSTED_DATE IS NOT NULL
AND B.EFFECTIVE_DATE<=to_date('&2','YYYY/MM/DD HH24:MI:SS')
AND A.JE_HEADER_ID=B.JE_HEADER_ID
AND A.LEDGER_ID = B.LEDGER_ID --A.SET_OF_BOOKS_ID=B.SET_OF_BOOKS_ID
AND B.CODE_COMBINATION_ID=C.CODE_COMBINATION_ID
group by C.CODE_COMBINATION_ID
, C.SEGMENT1
, C.SEGMENT2
, C.SEGMENT3
, C.SEGMENT4
, C.SEGMENT5) EB
where EB.ccid = BB.ccid(+)
)
UNION ALL
select ccid1 ccid1
, acc1 acc1
, ending_b blnce
, seg_01 seg_01
, seg_03 seg_03
, 'ENDING BALANCE '||acc1 descr
, 'Z' ref_sort
from (SELECT nvl(BB.ccid,EB.ccid) ccid1
, EB.ccid ccid2
, nvl(BB.account,EB.account) acc1
, EB.account acc2
, nvl(BB.usd_dr-BB.usd_cr,0) beginning_b
, nvl(EB.usd_dr-EB.usd_cr,0) ending_b
, EB.seg_01 seg_01
, EB.seg_03 seg_03
, '.' dummy
FROM (SELECT ALL C.segment1 seg_01
, C.segment3 seg_03
, C.code_combination_id ccid
, substr(C.SEGMENT1||'.'||C.SEGMENT2||'.'||C.SEGMENT3||'.'||C.SEGMENT4||'.'||C.SEGMENT5,1,25) account
, nvl(sum(B.ACCOUNTED_DR),0) usd_dr
, nvl(sum(B.ACCOUNTED_CR),0) usd_cr
, 'A' ref_sort
, '.' dummy
FROM GL_JE_HEADERS A
, GL_JE_LINES B
, GL_CODE_COMBINATIONS C
WHERE A.LEDGER_ID = '&3'
AND A.POSTED_DATE IS NOT NULL
AND B.EFFECTIVE_DATE<=to_date('&1','YYYY/MM/DD HH24:MI:SS')-1
AND A.JE_HEADER_ID=B.JE_HEADER_ID
AND A.LEDGER_ID = B.LEDGER_ID --A.SET_OF_BOOKS_ID=B.SET_OF_BOOKS_ID
AND B.CODE_COMBINATION_ID=C.CODE_COMBINATION_ID
group by C.CODE_COMBINATION_ID
, C.SEGMENT1
, C.SEGMENT2
, C.SEGMENT3
, C.SEGMENT4
, C.SEGMENT5) BB
, (SELECT ALL C.segment1 seg_01
, C.segment3 seg_03
, C.code_combination_id ccid
, substr(C.SEGMENT1||'.'||C.SEGMENT2||'.'||C.SEGMENT3||'.'||C.SEGMENT4||'.'||C.SEGMENT5,1,25) account
, nvl(sum(B.ACCOUNTED_DR),0) usd_dr
, nvl(sum(B.ACCOUNTED_CR),0) usd_cr
, 'A' ref_sort
, '.' dummy
FROM GL_JE_HEADERS A
, GL_JE_LINES B
, GL_CODE_COMBINATIONS C
WHERE A.LEDGER_ID = '&3'
AND A.POSTED_DATE IS NOT NULL
AND B.EFFECTIVE_DATE<=to_date('&2','YYYY/MM/DD HH24:MI:SS')
AND A.JE_HEADER_ID=B.JE_HEADER_ID
AND A.LEDGER_ID = B.LEDGER_ID --A.SET_OF_BOOKS_ID=B.SET_OF_BOOKS_ID
AND B.CODE_COMBINATION_ID=C.CODE_COMBINATION_ID
group by C.CODE_COMBINATION_ID
, C.SEGMENT1
, C.SEGMENT2
, C.SEGMENT3
, C.SEGMENT4
, C.SEGMENT5) EB
where EB.ccid = BB.ccid(+)
)
)
)
order by seg_03, seg_01, acc, ref_sort, je_source, je_category, eff_date
/