根据业务需求，需要输出一个AR模块中对应客户的余额变化表，需要包含对应的COA：segment3，segment8；还有客户信息 party number，party name；以及对应的期初余额，当期借贷方，期末余额。要求最后的余额和发生额数据要和GL中的日记账明细数据以及余额数据核对得上。
如果要和GL中的对应数据核对上，还要拉取对应的客户party number 和 party name 数据，就不能直接取GL_BALANCES表中的数据了，就要从GL通过SLA穿透到AR去获取对应的数据：
SLA-AR穿透代码[点击查看代码]
SELECT t.segment3
,t.segment8
,t.party_number
,t.party_name
,SUM(nvl(t.accounted_dr, 0)) accounted_dr
,SUM(nvl(t.accounted_cr, 0)) accounted_cr
FROM (SELECT /*+parallel(4)*/
gcc.segment3
,gcc.segment8
,hp.party_number
,hp.party_name
,l.accounted_dr
,l.accounted_cr
FROM gl_code_combinations_kfv gcc
,xla.xla_ae_headers h
,xla.xla_ae_lines l
,xla.xla_events e
,xla.xla_transaction_entities te
,ar.ra_customer_trx_all ct
,ar.hz_cust_accounts hca
,ar.hz_parties hp
,gl_period_statuses gps
WHERE h.application_id = l.application_id
AND h.ae_header_id = l.ae_header_id
AND gcc.code_combination_id = l.code_combination_id
AND h.application_id = e.application_id
AND h.event_id = e.event_id
AND h.application_id = te.application_id
AND h.entity_id = te.entity_id
AND h.period_name = gps.period_name
AND gps.ledger_id = h.ledger_id
AND gps.application_id = 101
AND gps.effective_period_num < 20240001
AND ct.bill_to_customer_id(+) = hca.cust_account_id
AND hp.party_id = hca.party_id
AND ct.org_id = 81
AND te.application_id = 222
AND h.ledger_id = 2021
AND gcc.segment1 = '0010'
AND gcc.segment3 IN ('1120100', '1130001', '2040000')
AND gcc.segment8 = '000'
AND te.entity_code = 'TRANSACTIONS'
AND nvl(te.source_id_int_1, (-99)) = ct.customer_trx_id
AND h.gl_transfer_status_code = 'Y'
/*receipt*/
UNION ALL
SELECT gcc.segment3
,gcc.segment8
,hp.party_number
,hp.party_name
,l.accounted_dr
,l.accounted_cr
FROM gl_code_combinations_kfv gcc
,xla.xla_ae_headers h
,xla.xla_ae_lines l
,xla.xla_events e
,xla.xla_transaction_entities te
,ar_cash_receipts_all acr
,ar.hz_cust_accounts hca
,ar.hz_parties hp
,gl_period_statuses gps
WHERE h.application_id = l.application_id
AND h.ae_header_id = l.ae_header_id
AND gcc.code_combination_id = l.code_combination_id
AND h.application_id = e.application_id
AND h.event_id = e.event_id
AND h.application_id = te.application_id
AND h.entity_id = te.entity_id
AND h.period_name = gps.period_name
AND gps.ledger_id = h.ledger_id
AND gps.application_id = 101
AND gps.effective_period_num < 20240001
AND acr.pay_from_customer(+) = hca.cust_account_id
AND hp.party_id = hca.party_id
AND acr.org_id = 81
AND te.application_id = 222
AND h.ledger_id = 2021
AND gcc.segment1 = '0010'
AND gcc.segment3 IN ('1120100', '1130001', '2040000')
AND gcc.segment8 = '000'
AND te.entity_code = 'RECEIPTS'
AND nvl(te.source_id_int_1, (-99)) = acr.cash_receipt_id
AND h.gl_transfer_status_code = 'Y'
UNION ALL
/*ADJUSTMENTS*/
SELECT /*+parallel(4)*/
gcc.segment3
,gcc.segment8
,hp.party_number
,hp.party_name
,nvl(l.accounted_dr, 0)
,nvl(l.accounted_cr, 0)
FROM gl_code_combinations_kfv gcc
,xla.xla_ae_headers h
,xla.xla_ae_lines l
,xla.xla_events e
,xla.xla_transaction_entities te
,ar_adjustments_all adj
,ra_customer_trx_all ct
,ar.hz_cust_accounts hca
,ar.hz_parties hp
,gl_period_statuses gps
WHERE h.application_id = l.application_id
AND gcc.code_combination_id = l.code_combination_id
AND h.ae_header_id = l.ae_header_id
AND h.application_id = e.application_id
AND h.event_id = e.event_id
AND h.application_id = te.application_id
AND h.entity_id = te.entity_id
AND te.application_id = 222
AND h.ledger_id = 2021
AND gcc.segment1 = '0010'
AND gcc.segment3 IN ('1120100', '1130001', '2040000')
AND gcc.segment8 = '000'
AND te.entity_code = 'ADJUSTMENTS'
AND h.period_name = gps.period_name
AND gps.ledger_id = h.ledger_id
AND gps.application_id = 101
AND gps.effective_period_num < 20240001
AND nvl(te.source_id_int_1, (-99)) = adj.adjustment_id
AND adj.customer_trx_id = ct.customer_trx_id
AND ct.org_id = 81
AND ct.bill_to_customer_id(+) = hca.cust_account_id
AND hp.party_id = hca.party_id
AND h.gl_transfer_status_code = 'Y') t
WHERE 1 = 1
AND t.segment3 = '1120100'
GROUP BY t.segment3
,t.segment8
,t.party_number
,t.party_name
ORDER BY t.segment3, t.segment8, t.party_number;
使用结果数据与业务人员提供的日记账数据进行核对，发现有部分科目存在差异。在对该部分的科目日记账数据检查之后，发现是直接在GL中做的账，但是由于这部分数据不是从AR传送过去的，所以不会关联查询到对应的客户 party number 等信息。再次跟业务人员进行确认这部分数据是否需要，如果需要，那这部分数据的客户 party 信息是null。最后确认还是需要的。。。。。。
只能再输出一串直接在GL中录入的AR数据，由于前面已经输出了AR来源的数据，所以在GL中的数据要排除日记账来源是 Receivables 的数据：
日记账其他来源的AR数据[点击查看代码]
SELECT /*+parallel(4)*/
gcc.segment3
,gcc.segment8
,NULL party_number
,NULL party_name
,nvl(gjl.accounted_dr, 0)
,nvl(gjl.accounted_cr, 0)
,gcc.concatenated_segments
,gjh.period_name
FROM gl.gl_je_headers gjh
,gl.gl_je_lines gjl
,gl_code_combinations_kfv gcc
,gl_period_statuses gps
WHERE gjh.je_header_id = gjl.je_header_id
AND gjl.code_combination_id = gcc.code_combination_id
AND gjh.je_source <> 'Receivables'
AND gcc.segment1 = '0010'
AND gjh.ledger_id = 2021
AND gcc.segment3 IN ('1120100', '1130001', '2040000')
AND gcc.segment8 = '000'
AND gjh.period_name = gps.period_name
AND gps.application_id = 101
AND gps.ledger_id = gjh.ledger_id
AND gps.effective_period_num < 20240001
最后将两部分代码 union all 拼接到一起，就是全部的结果数据。
需求中要求输出期初余额，当期发生额，以及期末余额，上面的SQL拼接起来输出的数据是期初的余额，直接将其中的期间条件从 小于 改为 等于，或者是 between ... and ... 就是对应的当期数据了：
当期发生额数据查询SQL[点击查看代码]
SELECT tn.segment3
,tn.segment8
,tn.party_number
,tn.party_name
,SUM(tn.accounted_dr) accounted_dr
,SUM(tn.accounted_cr) accounted_cr
FROM (SELECT /*+parallel(4)*/
gcc.segment3
,gcc.segment8
,hp.party_number
,hp.party_name
,nvl(l.accounted_dr, 0) accounted_dr
,nvl(l.accounted_cr, 0) accounted_cr
FROM gl_code_combinations_kfv gcc
,xla.xla_ae_headers h
,xla.xla_ae_lines l
,xla.xla_events e
,xla.xla_transaction_entities te
,ar.ra_customer_trx_all ct
,ar.hz_cust_accounts hca
,ar.hz_parties hp
,gl_period_statuses gps
WHERE h.application_id = l.application_id
AND h.ae_header_id = l.ae_header_id
AND gcc.code_combination_id = l.code_combination_id
AND h.application_id = e.application_id
AND h.event_id = e.event_id
AND h.application_id = te.application_id
AND h.entity_id = te.entity_id
AND h.period_name = gps.period_name
AND gps.ledger_id = h.ledger_id
AND gps.application_id = 101
AND gps.effective_period_num BETWEEN 20240001 AND 20240001
AND ct.bill_to_customer_id(+) = hca.cust_account_id
AND hp.party_id = hca.party_id
AND ct.org_id = 81
AND te.application_id = 222
AND h.ledger_id = 2021
AND gcc.segment1 = '0010'
AND gcc.segment3 IN ('1130000', '1130001', '2040000')
AND te.entity_code = 'TRANSACTIONS'
AND nvl(te.source_id_int_1, (-99)) = ct.customer_trx_id
AND h.gl_transfer_status_code = 'Y'
/*receipt*/
UNION ALL
SELECT gcc.segment3
,gcc.segment8
,hp.party_number
,hp.party_name
,nvl(l.accounted_dr, 0) accounted_dr
,nvl(l.accounted_cr, 0) accounted_cr
FROM gl_code_combinations_kfv gcc
,xla.xla_ae_headers h
,xla.xla_ae_lines l
,xla.xla_events e
,xla.xla_transaction_entities te
,ar_cash_receipts_all acr
,ar.hz_cust_accounts hca
,ar.hz_parties hp
,gl_period_statuses gps
WHERE h.application_id = l.application_id
AND h.ae_header_id = l.ae_header_id
AND gcc.code_combination_id = l.code_combination_id
AND h.application_id = e.application_id
AND h.event_id = e.event_id
AND h.application_id = te.application_id
AND h.entity_id = te.entity_id
AND h.period_name = gps.period_name
AND gps.ledger_id = h.ledger_id
AND gps.application_id = 101
AND gps.effective_period_num BETWEEN 20240001 AND 20240001
AND acr.pay_from_customer(+) = hca.cust_account_id
AND hp.party_id = hca.party_id
AND acr.org_id = 81
AND te.application_id = 222
AND h.ledger_id = 2021
AND gcc.segment1 = '0010'
AND gcc.segment3 IN ('1130000', '1130001', '2040000')
AND te.entity_code = 'RECEIPTS'
AND nvl(te.source_id_int_1, (-99)) = acr.cash_receipt_id
AND h.gl_transfer_status_code = 'Y'
UNION ALL
/*ADJUSTMENTS*/
SELECT /*+parallel(4)*/
gcc.segment3
,gcc.segment8
,hp.party_number
,hp.party_name
,nvl(l.accounted_dr, 0) accounted_dr
,nvl(l.accounted_cr, 0) accounted_cr
FROM gl_code_combinations_kfv gcc
,xla.xla_ae_headers h
,xla.xla_ae_lines l
,xla.xla_events e
,xla.xla_transaction_entities te
,ar_adjustments_all adj
,ra_customer_trx_all ct
,ar.hz_cust_accounts hca
,ar.hz_parties hp
,gl_period_statuses gps
WHERE h.application_id = l.application_id
AND gcc.code_combination_id = l.code_combination_id
AND h.ae_header_id = l.ae_header_id
AND h.application_id = e.application_id
AND h.event_id = e.event_id
AND h.application_id = te.application_id
AND h.entity_id = te.entity_id
AND te.application_id = 222
AND h.ledger_id = 2021
AND gcc.segment1 = '0010'
AND gcc.segment3 IN ('1130000', '1130001', '2040000')
AND te.entity_code = 'ADJUSTMENTS'
AND h.period_name = gps.period_name
AND gps.ledger_id = h.ledger_id
AND gps.application_id = 101
AND gps.effective_period_num BETWEEN 20240001 AND 20240001
AND nvl(te.source_id_int_1, (-99)) = adj.adjustment_id
AND adj.customer_trx_id = ct.customer_trx_id
AND ct.org_id = 81
AND ct.bill_to_customer_id(+) = hca.cust_account_id
AND hp.party_id = hca.party_id
AND h.gl_transfer_status_code = 'Y'
union all
SELECT /*+parallel(4)*/
gcc.segment3
,gcc.segment8
,NULL party_number
,NULL party_name
,nvl(gjl.accounted_dr, 0) accounted_dr
,nvl(gjl.accounted_cr, 0) accounted_cr
FROM gl.gl_je_headers gjh
,gl.gl_je_lines gjl
,apps.gl_code_combinations_kfv gcc
,gl.gl_period_statuses gps
WHERE gjh.je_header_id = gjl.je_header_id
AND gjl.code_combination_id = gcc.code_combination_id
AND gjh.je_source <> 'Receivables'
AND gcc.segment1 = '0010'
AND gjh.ledger_id = 2021
AND gcc.segment3 IN ('1130000', '1130001', '2040000')
AND gjh.period_name = gps.period_name
AND gps.application_id = 101
AND gps.ledger_id = gjh.ledger_id
AND gps.effective_period_num BETWEEN 20240001 AND 20240001) tn
GROUP BY tn.segment3, tn.segment8, tn.party_number, tn.party_name
查询结果数据与业务数据进行核对，OK，核对成功！
那么此时将两大段SQL进行合并构建成一串SQL是不是就直接数最后数据了呢？是的，想象很美好，现实却很残酷。。。
由于最后输出展示的列为：
| segment3 | segment8 | party_number | party_name | begin_bal | net_dr | net_cr | end_bal |
|---|---|---|---|---|---|---|---|
他要求的结果数据是要左右列，并不是简单的 union all 上下拼接结果数据，所以只能写子查询，并将 SLA 的穿透 SQL 和 GL 中的数据查询 SQL 分开去子查询，最后再将结果 union all 起来。。。好烦。。。
而且经过比对，拉取期初数据的SQL，查询出来的结果客户信息，在查询当期数据的SQL结果中有一部分是不存在的；反之，查询当期数据的SQL结果中的有一部分客户信息在期初的SQL结果中也有一部分是不存在的。
最后只能是通过全外连接 full outer join 去做子查询操作，并借鉴 AI 的优化方法，构建最后的结果查询 SQL：
最终输出SQL[点击查看代码]
--合并
--子分类帐数据
WITH begin_data AS
(SELECT tb.segment3
,tb.segment8
,tb.party_number
,tb.party_name
,SUM(tb.net_bal) begin_bal
FROM (SELECT /*+parallel(4)*/
gcc.segment3
,gcc.segment8
,hp.party_number
,hp.party_name
,nvl(l.accounted_dr, 0) - nvl(l.accounted_cr, 0) net_bal
FROM gl_code_combinations_kfv gcc
,xla.xla_ae_headers h
,xla.xla_ae_lines l
,xla.xla_events e
,xla.xla_transaction_entities te
,ar.ra_customer_trx_all ct
,ar.hz_cust_accounts hca
,ar.hz_parties hp
,gl_period_statuses gps
WHERE h.application_id = l.application_id
AND h.ae_header_id = l.ae_header_id
AND gcc.code_combination_id = l.code_combination_id
AND h.application_id = e.application_id
AND h.event_id = e.event_id
AND h.application_id = te.application_id
AND h.entity_id = te.entity_id
AND h.period_name = gps.period_name
AND gps.ledger_id = h.ledger_id
AND gps.application_id = 101
AND gps.effective_period_num < 20240001
AND ct.bill_to_customer_id(+) = hca.cust_account_id
AND hp.party_id = hca.party_id
AND ct.org_id = 81
AND te.application_id = 222
AND h.ledger_id = 2021
AND gcc.segment1 = '0010'
AND gcc.segment3 IN ('1130000', '1130001', '2040000')
AND te.entity_code = 'TRANSACTIONS'
AND nvl(te.source_id_int_1, (-99)) = ct.customer_trx_id
AND h.gl_transfer_status_code = 'Y'
/*receipt*/
UNION ALL
SELECT gcc.segment3
,gcc.segment8
,hp.party_number
,hp.party_name
,nvl(l.accounted_dr, 0) - nvl(l.accounted_cr, 0) net_bal
FROM gl_code_combinations_kfv gcc
,xla.xla_ae_headers h
,xla.xla_ae_lines l
,xla.xla_events e
,xla.xla_transaction_entities te
,ar_cash_receipts_all acr
,ar.hz_cust_accounts hca
,ar.hz_parties hp
,gl_period_statuses gps
WHERE h.application_id = l.application_id
AND h.ae_header_id = l.ae_header_id
AND gcc.code_combination_id = l.code_combination_id
AND h.application_id = e.application_id
AND h.event_id = e.event_id
AND h.application_id = te.application_id
AND h.entity_id = te.entity_id
AND h.period_name = gps.period_name
AND gps.ledger_id = h.ledger_id
AND gps.application_id = 101
AND gps.effective_period_num < 20240001
AND acr.pay_from_customer(+) = hca.cust_account_id
AND hp.party_id = hca.party_id
AND acr.org_id = 81
AND te.application_id = 222
AND h.ledger_id = 2021
AND gcc.segment1 = '0010'
AND gcc.segment3 IN ('1130000', '1130001', '2040000')
AND te.entity_code = 'RECEIPTS'
AND nvl(te.source_id_int_1, (-99)) = acr.cash_receipt_id
AND h.gl_transfer_status_code = 'Y'
UNION ALL
/*ADJUSTMENTS*/
SELECT /*+parallel(4)*/
gcc.segment3
,gcc.segment8
,hp.party_number
,hp.party_name
,nvl(l.accounted_dr, 0) - nvl(l.accounted_cr, 0) net_bal
FROM gl_code_combinations_kfv gcc
,xla.xla_ae_headers h
,xla.xla_ae_lines l
,xla.xla_events e
,xla.xla_transaction_entities te
,ar_adjustments_all adj
,ra_customer_trx_all ct
,ar.hz_cust_accounts hca
,ar.hz_parties hp
,gl_period_statuses gps
WHERE h.application_id = l.application_id
AND gcc.code_combination_id = l.code_combination_id
AND h.ae_header_id = l.ae_header_id
AND h.application_id = e.application_id
AND h.event_id = e.event_id
AND h.application_id = te.application_id
AND h.entity_id = te.entity_id
AND te.application_id = 222
AND h.ledger_id = 2021
AND gcc.segment1 = '0010'
AND gcc.segment3 IN ('1130000', '1130001', '2040000')
AND te.entity_code = 'ADJUSTMENTS'
AND h.period_name = gps.period_name
AND gps.ledger_id = h.ledger_id
AND gps.application_id = 101
AND gps.effective_period_num < 20240001
AND nvl(te.source_id_int_1, (-99)) = adj.adjustment_id
AND adj.customer_trx_id = ct.customer_trx_id
AND ct.org_id = 81
AND ct.bill_to_customer_id(+) = hca.cust_account_id
AND hp.party_id = hca.party_id
AND h.gl_transfer_status_code = 'Y') tb
GROUP BY tb.segment3, tb.segment8, tb.party_number, tb.party_name),
net_data AS
(SELECT tn.segment3
,tn.segment8
,tn.party_number
,tn.party_name
,SUM(tn.accounted_dr) accounted_dr
,SUM(tn.accounted_cr) accounted_cr
FROM (SELECT /*+parallel(4)*/
gcc.segment3
,gcc.segment8
,hp.party_number
,hp.party_name
,nvl(l.accounted_dr, 0) accounted_dr
,nvl(l.accounted_cr, 0) accounted_cr
FROM gl_code_combinations_kfv gcc
,xla.xla_ae_headers h
,xla.xla_ae_lines l
,xla.xla_events e
,xla.xla_transaction_entities te
,ar.ra_customer_trx_all ct
,ar.hz_cust_accounts hca
,ar.hz_parties hp
,gl_period_statuses gps
WHERE h.application_id = l.application_id
AND h.ae_header_id = l.ae_header_id
AND gcc.code_combination_id = l.code_combination_id
AND h.application_id = e.application_id
AND h.event_id = e.event_id
AND h.application_id = te.application_id
AND h.entity_id = te.entity_id
AND h.period_name = gps.period_name
AND gps.ledger_id = h.ledger_id
AND gps.application_id = 101
AND gps.effective_period_num BETWEEN 20240001 AND 20240001
AND ct.bill_to_customer_id(+) = hca.cust_account_id
AND hp.party_id = hca.party_id
AND ct.org_id = 81
AND te.application_id = 222
AND h.ledger_id = 2021
AND gcc.segment1 = '0010'
AND gcc.segment3 IN ('1130000', '1130001', '2040000')
AND te.entity_code = 'TRANSACTIONS'
AND nvl(te.source_id_int_1, (-99)) = ct.customer_trx_id
AND h.gl_transfer_status_code = 'Y'
/*receipt*/
UNION ALL
SELECT gcc.segment3
,gcc.segment8
,hp.party_number
,hp.party_name
,nvl(l.accounted_dr, 0) accounted_dr
,nvl(l.accounted_cr, 0) accounted_cr
FROM gl_code_combinations_kfv gcc
,xla.xla_ae_headers h
,xla.xla_ae_lines l
,xla.xla_events e
,xla.xla_transaction_entities te
,ar_cash_receipts_all acr
,ar.hz_cust_accounts hca
,ar.hz_parties hp
,gl_period_statuses gps
WHERE h.application_id = l.application_id
AND h.ae_header_id = l.ae_header_id
AND gcc.code_combination_id = l.code_combination_id
AND h.application_id = e.application_id
AND h.event_id = e.event_id
AND h.application_id = te.application_id
AND h.entity_id = te.entity_id
AND h.period_name = gps.period_name
AND gps.ledger_id = h.ledger_id
AND gps.application_id = 101
AND gps.effective_period_num BETWEEN 20240001 AND 20240001
AND acr.pay_from_customer(+) = hca.cust_account_id
AND hp.party_id = hca.party_id
AND acr.org_id = 81
AND te.application_id = 222
AND h.ledger_id = 2021
AND gcc.segment1 = '0010'
AND gcc.segment3 IN ('1130000', '1130001', '2040000')
AND te.entity_code = 'RECEIPTS'
AND nvl(te.source_id_int_1, (-99)) = acr.cash_receipt_id
AND h.gl_transfer_status_code = 'Y'
UNION ALL
/*ADJUSTMENTS*/
SELECT /*+parallel(4)*/
gcc.segment3
,gcc.segment8
,hp.party_number
,hp.party_name
,nvl(l.accounted_dr, 0) accounted_dr
,nvl(l.accounted_cr, 0) accounted_cr
FROM gl_code_combinations_kfv gcc
,xla.xla_ae_headers h
,xla.xla_ae_lines l
,xla.xla_events e
,xla.xla_transaction_entities te
,ar_adjustments_all adj
,ra_customer_trx_all ct
,ar.hz_cust_accounts hca
,ar.hz_parties hp
,gl_period_statuses gps
WHERE h.application_id = l.application_id
AND gcc.code_combination_id = l.code_combination_id
AND h.ae_header_id = l.ae_header_id
AND h.application_id = e.application_id
AND h.event_id = e.event_id
AND h.application_id = te.application_id
AND h.entity_id = te.entity_id
AND te.application_id = 222
AND h.ledger_id = 2021
AND gcc.segment1 = '0010'
AND gcc.segment3 IN ('1130000', '1130001', '2040000')
AND te.entity_code = 'ADJUSTMENTS'
AND h.period_name = gps.period_name
AND gps.ledger_id = h.ledger_id
AND gps.application_id = 101
AND gps.effective_period_num BETWEEN 20240001 AND 20240001
AND nvl(te.source_id_int_1, (-99)) = adj.adjustment_id
AND adj.customer_trx_id = ct.customer_trx_id
AND ct.org_id = 81
AND ct.bill_to_customer_id(+) = hca.cust_account_id
AND hp.party_id = hca.party_id
AND h.gl_transfer_status_code = 'Y') tn
GROUP BY tn.segment3, tn.segment8, tn.party_number, tn.party_name)
SELECT coalesce(b.segment3, n.segment3) segment3
,coalesce(b.segment8, n.segment8) segment8
,coalesce(b.party_number, n.party_number) party_number
,coalesce(b.party_name, n.party_name) party_name
,nvl(b.begin_bal, 0) begin_bal
,nvl(n.accounted_dr, 0) accounted_dr
,nvl(n.accounted_cr, 0) accounted_cr
,(nvl(b.begin_bal, 0) + nvl(n.accounted_dr, 0) -
nvl(n.accounted_cr, 0)) end_bal
FROM begin_data b
FULL OUTER JOIN net_data n
ON b.segment3 = n.segment3
AND b.segment8 = n.segment8
AND b.party_number = n.party_number
UNION ALL
/*日记账数据*/
SELECT je_net.segment3
,je_net.segment8
,je_net.party_number
,je_net.party_name
,nvl(je_begin.net_bal, 0) begin_bal
,nvl(je_net.accounted_dr, 0) accounted_dr
,nvl(je_net.accounted_cr, 0) accounted_cr
,(nvl(je_begin.net_bal, 0) + nvl(je_net.accounted_dr, 0) -
nvl(je_net.accounted_cr, 0)) end_bal
FROM (SELECT /*+parallel(4)*/
gcc.segment3
,gcc.segment8
,NULL party_number
,NULL party_name
,SUM(nvl(gjl.accounted_dr, 0) - nvl(gjl.accounted_cr, 0)) net_bal
FROM gl.gl_je_headers gjh
,gl.gl_je_lines gjl
,apps.gl_code_combinations_kfv gcc
,gl.gl_period_statuses gps
WHERE gjh.je_header_id = gjl.je_header_id
AND gjl.code_combination_id = gcc.code_combination_id
AND gjh.je_source <> 'Receivables'
AND gcc.segment1 = '0010'
AND gjh.ledger_id = 2021
AND gcc.segment3 IN ('1130000', '1130001', '2040000')
AND gjh.period_name = gps.period_name
AND gps.application_id = 101
AND gps.ledger_id = gjh.ledger_id
AND gps.effective_period_num < 20240001
GROUP BY gcc.segment3, gcc.segment8) je_begin
,(SELECT /*+parallel(4)*/
gcc.segment3
,gcc.segment8
,NULL party_number
,NULL party_name
,SUM(nvl(gjl.accounted_dr, 0)) accounted_dr
,SUM(nvl(gjl.accounted_cr, 0)) accounted_cr
FROM gl.gl_je_headers gjh
,gl.gl_je_lines gjl
,apps.gl_code_combinations_kfv gcc
,gl.gl_period_statuses gps
WHERE gjh.je_header_id = gjl.je_header_id
AND gjl.code_combination_id = gcc.code_combination_id
AND gjh.je_source <> 'Receivables'
AND gcc.segment1 = '0010'
AND gjh.ledger_id = 2021
AND gcc.segment3 IN ('1130000', '1130001', '2040000')
AND gjh.period_name = gps.period_name
AND gps.application_id = 101
AND gps.ledger_id = gjh.ledger_id
AND gps.effective_period_num BETWEEN 20240001 AND 20240001
GROUP BY gcc.segment3, gcc.segment8) je_net
WHERE 1 = 1
AND je_net.segment3 = je_begin.segment3
AND je_net.segment8 = je_begin.segment8
ORDER BY segment3, segment8, party_number;
真的是蓝瘦香菇哇 T_T ~~~
后面与业务人员进行数据验证，确认了数据是可以核对上的，说明上述的查询逻辑是正确的，这里对整个代码进行完整的回顾检查，并进行优化调整。优化调整之后的代码为：
WITH common_sla_net AS
/*--SLA当期数据源--*/
(SELECT /*+ MATERIALIZE LEADING(gcc) USE_NL(xal xah xte)*/
gcc.segment3,
gcc.segment8,
nvl(xte.source_id_int_1, (-99)) AS source_id_int_1,
xte.entity_code,
gps.effective_period_num,
nvl(xal.accounted_dr, 0) accounted_dr,
nvl(xal.accounted_cr, 0) accounted_cr
FROM gl.gl_code_combinations gcc,
xla.xla_ae_lines xal,
xla.xla_ae_headers xah,
xla.xla_transaction_entities xte,
gl.gl_period_statuses gps
WHERE gcc.segment3 IN ('1130000', '1130001', '2040000')
AND gcc.segment1 = '0010'
AND gcc.code_combination_id = xal.code_combination_id
AND xah.ae_header_id = xal.ae_header_id
AND xah.application_id = xal.application_id
AND xah.gl_transfer_status_code = 'Y'
AND xah.ledger_id = 2021
AND xte.entity_id = xah.entity_id
AND xte.application_id = xah.application_id
AND xte.application_id = 222
AND gps.application_id = 101
AND gps.ledger_id = xah.ledger_id
AND gps.period_name = xah.period_name
AND gps.effective_period_num BETWEEN 20240001 AND 20240001),
/*--发生额数据--*/
sla_net AS
(SELECT segment3,
segment8,
party_number,
party_name,
SUM(accounted_dr) accounted_dr,
SUM(accounted_cr) accounted_cr
FROM (SELECT csn.segment3,
csn.segment8,
hp.party_number,
hp.party_name,
csn.accounted_dr,
csn.accounted_cr
FROM common_sla_net csn,
ar.ar_cash_receipts_all acr,
ar.hz_cust_accounts hca,
ar.hz_parties hp
WHERE csn.source_id_int_1 = acr.cash_receipt_id
AND csn.entity_code = 'RECEIPTS'
AND acr.org_id = 81
AND acr.pay_from_customer = hca.cust_account_id(+)
AND hca.party_id = hp.party_id(+)
UNION ALL
SELECT csn.segment3,
csn.segment8,
hp.party_number,
hp.party_name,
csn.accounted_dr,
csn.accounted_cr
FROM common_sla_net csn,
ar.ra_customer_trx_all ct,
ar.hz_cust_accounts hca,
ar.hz_parties hp
WHERE csn.source_id_int_1 = ct.customer_trx_id
AND csn.entity_code = 'TRANSACTIONS'
AND ct.org_id = 81
AND ct.bill_to_customer_id = hca.cust_account_id(+)
AND hca.party_id = hp.party_id(+)
UNION ALL
SELECT csn.segment3,
csn.segment8,
hp.party_number,
hp.party_name,
csn.accounted_dr,
csn.accounted_cr
FROM common_sla_net csn,
ar.ar_adjustments_all adj,
ar.ra_customer_trx_all ct,
ar.hz_cust_accounts hca,
ar.hz_parties hp
WHERE csn.source_id_int_1 = adj.adjustment_id
AND csn.entity_code = 'ADJUSTMENTS'
AND adj.customer_trx_id = ct.customer_trx_id
AND ct.org_id = 81
AND ct.bill_to_customer_id = hca.cust_account_id(+)
AND hca.party_id = hp.party_id(+))
GROUP BY segment3, segment8, party_number, party_name),
common_sla_begin AS
/*--SLA期初数据源--*/
(SELECT /*+ MATERIALIZE LEADING(gcc) USE_NL(xal xah xte)*/
gcc.segment3,
gcc.segment8,
nvl(xte.source_id_int_1, (-99)) AS source_id_int_1,
xte.entity_code,
gps.effective_period_num,
nvl(xal.accounted_dr, 0) accounted_dr,
nvl(xal.accounted_cr, 0) accounted_cr
FROM gl.gl_code_combinations gcc,
xla.xla_ae_lines xal,
xla.xla_ae_headers xah,
xla.xla_transaction_entities xte,
gl.gl_period_statuses gps
WHERE gcc.segment3 IN ('1130000', '1130001', '2040000')
AND gcc.segment1 = '0010'
AND gcc.code_combination_id = xal.code_combination_id
AND xah.ae_header_id = xal.ae_header_id
AND xah.application_id = xal.application_id
AND xah.gl_transfer_status_code = 'Y'
AND xah.ledger_id = 2021
AND xte.entity_id = xah.entity_id
AND xte.application_id = xah.application_id
AND xte.application_id = 222
AND gps.application_id = 101
AND gps.ledger_id = xah.ledger_id
AND gps.period_name = xah.period_name
AND gps.effective_period_num < 20240001),
/*--期初数据--*/
sla_begin AS
(SELECT segment3,
segment8,
party_number,
party_name,
SUM(accounted_dr - accounted_cr) begin_bal
FROM ( /*RECEIPTS*/
SELECT csb.segment3,
csb.segment8,
hp.party_number,
hp.party_name,
csb.accounted_dr,
csb.accounted_cr
FROM common_sla_begin csb,
ar.ar_cash_receipts_all acr,
ar.hz_cust_accounts hca,
ar.hz_parties hp
WHERE csb.source_id_int_1 = acr.cash_receipt_id
AND csb.entity_code = 'RECEIPTS'
AND acr.org_id = 81
AND acr.pay_from_customer = hca.cust_account_id(+)
AND hca.party_id = hp.party_id(+)
UNION ALL
/*TRANSACTIONS*/
SELECT csb.segment3,
csb.segment8,
hp.party_number,
hp.party_name,
csb.accounted_dr,
csb.accounted_cr
FROM common_sla_begin csb,
ar.ra_customer_trx_all ct,
ar.hz_cust_accounts hca,
ar.hz_parties hp
WHERE csb.source_id_int_1 = ct.customer_trx_id
AND csb.entity_code = 'TRANSACTIONS'
AND ct.org_id = 81
AND ct.bill_to_customer_id = hca.cust_account_id(+)
AND hca.party_id = hp.party_id(+)
UNION ALL
/*ADJUSTMENTS*/
SELECT csb.segment3,
csb.segment8,
hp.party_number,
hp.party_name,
csb.accounted_dr,
csb.accounted_cr
FROM common_sla_begin csb,
ar.ar_adjustments_all adj,
ar.ra_customer_trx_all ct,
ar.hz_cust_accounts hca,
ar.hz_parties hp
WHERE csb.source_id_int_1 = adj.adjustment_id
AND csb.entity_code = 'ADJUSTMENTS'
AND adj.customer_trx_id = ct.customer_trx_id
AND ct.org_id = 81
AND ct.bill_to_customer_id = hca.cust_account_id(+)
AND hca.party_id = hp.party_id(+))
GROUP BY segment3, segment8, party_number, party_name),
/*--日记账其他来源数据--*/
je_begin AS
(SELECT gcc.segment3,
gcc.segment8,
SUM(nvl(gjl.accounted_dr, 0) - nvl(gjl.accounted_cr, 0)) net_bal
FROM gl.gl_je_headers gjh,
gl.gl_je_lines gjl,
gl.gl_code_combinations gcc,
gl.gl_period_statuses gps
WHERE gjh.je_header_id = gjl.je_header_id
AND gjh.ledger_id = 2021
AND gjh.je_source <> 'Receivables'
AND gcc.code_combination_id = gjl.code_combination_id
AND gcc.segment1 = '0010'
AND gcc.segment3 IN ('1130000', '1130001', '2040000')
AND gps.application_id = 101
AND gps.ledger_id = gjh.ledger_id
AND gps.period_name = gjh.period_name
AND gps.effective_period_num < 20240001
GROUP BY gcc.segment3, gcc.segment8),
je_net AS
(SELECT gcc.segment3,
gcc.segment8,
SUM(nvl(gjl.accounted_dr, 0)) accounted_dr,
SUM(nvl(gjl.accounted_cr, 0)) accounted_cr
FROM gl.gl_je_headers gjh,
gl.gl_je_lines gjl,
gl.gl_code_combinations gcc,
gl.gl_period_statuses gps
WHERE gjh.je_header_id = gjl.je_header_id
AND gjh.je_source <> 'Receivables'
AND gjh.ledger_id = 2021
AND gcc.code_combination_id = gjl.code_combination_id
AND gcc.segment1 = '0010'
AND gcc.segment3 IN ('1130000', '1130001', '2040000')
AND gps.application_id = 101
AND gps.ledger_id = gjh.ledger_id
AND gps.period_name = gjh.period_name
AND gps.effective_period_num BETWEEN 20240001 AND 20240001
GROUP BY gcc.segment3, gcc.segment8)
SELECT /*+parallel(4)*/
coalesce(b.segment3, n.segment3) segment3,
gl_flexfields_pkg.get_description_sql(50352,
3,
coalesce(b.segment3, n.segment3)) segment3_desc,
coalesce(b.segment8, n.segment8) segment8,
gl_flexfields_pkg.get_description_sql(50352,
8,
coalesce(b.segment8, n.segment8)) segment8_desc,
coalesce(b.party_number, n.party_number) party_number,
coalesce(b.party_name, n.party_name) party_name,
'CNY' currency_code,
SUM(nvl(b.begin_bal, 0)) begin_bal,
SUM(nvl(n.accounted_dr, 0)) accounted_dr,
SUM(nvl(n.accounted_cr, 0)) accounted_cr,
SUM(nvl(b.begin_bal, 0) + nvl(n.accounted_dr, 0) - nvl(n.accounted_cr, 0)) end_bal
FROM sla_begin b
FULL OUTER JOIN sla_net n
ON b.segment3 = n.segment3
AND b.segment8 = n.segment8
AND b.party_number = n.party_number
GROUP BY coalesce(b.segment3, n.segment3),
coalesce(b.segment8, n.segment8),
coalesce(b.party_number, n.party_number),
coalesce(b.party_name, n.party_name)
UNION ALL
/*日记账数据*/
SELECT /*+parallel(4)*/
coalesce(jb.segment3, jn.segment3) segment3,
gl_flexfields_pkg.get_description_sql(x_coa_id => 50352,
x_seg_num => 3,
x_seg_val => coalesce(jb.segment3,
jn.segment3)) segment3_desc,
coalesce(jb.segment8, jn.segment8) segment8,
gl_flexfields_pkg.get_description_sql(x_coa_id => 50352,
x_seg_num => 8,
x_seg_val => coalesce(jb.segment8,
jn.segment8)) segment8_desc,
NULL party_number,
NULL party_name,
'CNY' currency_code,
SUM(nvl(jb.net_bal, 0)) begin_bal,
SUM(nvl(jn.accounted_dr, 0)) accounted_dr,
SUM(nvl(jn.accounted_cr, 0)) accounted_cr,
SUM(nvl(jb.net_bal, 0) + nvl(jn.accounted_dr, 0) - nvl(jn.accounted_cr, 0)) end_bal
FROM je_begin jb
FULL OUTER JOIN je_net jn
ON jb.segment3 = jn.segment3
AND jb.segment8 = jn.segment8
GROUP BY coalesce(jb.segment3, jn.segment3),
coalesce(jb.segment8, jn.segment8)
ORDER BY segment3, segment8, party_number;
OK!
完整解决需求内容~
+++==========================================================================+++
以上内容仅为个人学习记录使用。
如有转载，请附有原文出处链接。
浙公网安备 33010602011771号