上一篇随笔中写到：业务要求开发一个报表请求，输出指定期间内的客户余额变动明细的数据报表，自己折腾了好久，再加上向技术大佬的跪地请教学习，终于是完成的相关需求的开发实现。呼~~~
但是！但是！但是！一个类似的业务报表需求来了，是要求输出AP里面供应商的余额变动明细数据报表...T_T
一个是AR里面的数据信息，一个又是AP里面的数据信息，现在有了书写AR的经验积累，一开始的时候就确认了，最后的数据输出也是要用到 full outer join 的关联方法的。因为在AP里面的场景没有AR里面的那么多，就只有 AP_INVOICES 的发票场景，以及 AP_PAYMENTS 的发票支付场景，所以构建的SQL会简单一些，但也还是要通过子分类账 sla 的表（xla）去透视关联。。。
首先查验需求中要求输出的科目所对应的日记账数据，发现这部分日记账数据的来源 je_source 同样不仅仅只有从 AP 模块传送的数据，同样有通过 spreadsheet 导入的数据以及其他来源的数据，这样的化，就要和客户报表的数据类似，分为2部分去输出，最后将结果进行 union all 的拼接即可。
首先构建从 AP 传送的数据查询SQL（最初版）：
SLA_AP穿透查询SQL【点击查看代码】
SELECT query_source_net.segment3,
gl_flexfields_pkg.get_description_sql(x_coa_id => 50352,
x_seg_num => 3,
x_seg_val => query_source_net.segment3) segment3_desc,
query_source_net.segment8,
gl_flexfields_pkg.get_description_sql(x_coa_id => 50352,
x_seg_num => 8,
x_seg_val => query_source_net.segment8) segment8_desc,
query_source_net.vendor_num,
query_source_net.vendor_name,
'CNY' currency_code,
nvl(query_source_bal.begin_bal, 0) begin_bal,
query_source_net.net_dr,
query_source_net.net_cr,
(nvl(query_source_bal.begin_bal, 0) + query_source_net.net_dr -
query_source_net.net_cr) end_bal
FROM (SELECT /*+parallel(8)*/
t.segment3,
t.segment8,
t.vendor_num,
t.vendor_name,
'CNY' currency_code,
SUM(t.accounted_dr) net_dr,
SUM(t.accounted_cr) net_cr
FROM (SELECT pv.vendor_name,
pv.segment1 vendor_num,
gcc.segment3,
gcc.segment8,
nvl(xal.accounted_dr, 0) accounted_dr,
nvl(xal.accounted_cr, 0) accounted_cr
FROM ap.ap_invoices_all ai,
apps.po_vendors pv,
xla.xla_transaction_entities xte,
gl.gl_period_statuses gps,
xla.xla_ae_headers xah,
xla.xla_ae_lines xal,
gl.gl_code_combinations gcc
WHERE ai.vendor_id = pv.vendor_id
AND xte.entity_id = xah.entity_id
AND xte.application_id = xah.application_id
AND xah.ae_header_id = xal.ae_header_id
AND xah.application_id = xal.application_id
AND xah.ledger_id = gps.ledger_id
AND xah.period_name = gps.period_name
AND xah.application_id = gps.application_id
AND nvl(ai.org_id, 81) = 81
AND xah.ledger_id = 2021
AND xal.code_combination_id = gcc.code_combination_id
AND gps.effective_period_num BETWEEN g_period_from_num AND
g_period_to_num
AND gcc.segment1 = '0010'
AND (gcc.segment3 LIKE '203%' OR
gcc.segment3 IN
('1150000', '2090100', '1190100', '1160000'))
AND xte.application_id = 200
AND xte.entity_code = 'AP_INVOICES'
AND xte.source_id_int_1 = ai.invoice_id
UNION ALL
SELECT pv.vendor_name,
pv.segment1 vendor_num,
gcc.segment3,
gcc.segment8,
nvl(xal.accounted_dr, 0) accounted_dr,
nvl(xal.accounted_cr, 0) accounted_cr
FROM ap.ap_checks_all ac,
apps.po_vendors pv,
xla.xla_transaction_entities xte,
gl.gl_period_statuses gps,
xla.xla_ae_headers xah,
xla.xla_ae_lines xal,
gl.gl_code_combinations gcc
WHERE ac.vendor_id = pv.vendor_id
AND xte.entity_id = xah.entity_id
AND xte.application_id = xah.application_id
AND xah.ae_header_id = xal.ae_header_id
AND xah.application_id = xal.application_id
AND xah.ledger_id = gps.ledger_id
AND xah.period_name = gps.period_name
AND xah.application_id = gps.application_id
AND nvl(ac.org_id, 81) = 81
AND xah.ledger_id = 2021
AND xal.code_combination_id = gcc.code_combination_id
AND gcc.segment1 = '0010'
AND gps.effective_period_num BETWEEN 20240001 AND 20240001
AND (gcc.segment3 LIKE '203%' OR
gcc.segment3 IN
('1150000', '2090100', '1190100', '1160000'))
AND xte.application_id = 200
AND xte.entity_code = 'AP_PAYMENTS'
AND xte.source_id_int_1 = ac.check_id) t
GROUP BY t.vendor_name, t.vendor_num, t.segment3, t.segment8) query_source_net,
(SELECT aaa.segment3,
aaa.segment8,
aaa.vendor_num,
aaa.vendor_name,
'CNY' currency_code,
nvl(SUM(aaa.beg_bal), 0) begin_bal
FROM (SELECT pv.vendor_name,
pv.segment1 vendor_num,
gcc.segment3,
gcc.segment8,
nvl(xal.accounted_dr, 0) - nvl(xal.accounted_cr, 0) beg_bal
FROM ap.ap_invoices_all aia,
apps.po_vendors pv,
xla.xla_transaction_entities xte,
xla.xla_ae_headers xah,
xla.xla_ae_lines xal,
gl.gl_code_combinations gcc
WHERE aia.invoice_id = xte.source_id_int_1
AND aia.invoice_num = xte.transaction_number
AND aia.vendor_id = pv.vendor_id
AND xte.application_id = xah.application_id
AND xte.entity_id = xah.entity_id
AND xte.entity_code = 'AP_INVOICES'
AND xah.ae_header_id = xal.ae_header_id
AND xah.application_id = xal.application_id
AND xal.code_combination_id = gcc.code_combination_id
AND nvl(aia.org_id, 81) = 81
AND xah.ledger_id = 2021
AND aia.gl_date <
(SELECT s.start_date
FROM gl.gl_period_statuses s
WHERE s.application_id = 200
AND s.ledger_id = 2021
AND s.period_name = '24-Jan')
AND gcc.segment1 = '0010'
AND (gcc.segment3 LIKE '203%' OR
gcc.segment3 IN
('1150000', '2090100', '1190100', '1160000'))
AND gcc.summary_flag = 'N'
UNION ALL
SELECT pv.vendor_name,
pv.segment1 vendor_num,
gcc.segment3,
gcc.segment8,
nvl(ac.amount, 0) beg_bal
FROM ap.ap_checks_all ac,
apps.po_vendors pv,
xla.xla_transaction_entities xte,
xla.xla_ae_headers xah,
xla.xla_ae_lines xal,
gl.gl_code_combinations gcc
WHERE ac.vendor_id = pv.vendor_id
AND xte.source_id_int_1 = ac.check_id
AND xte.entity_code = 'AP_PAYMENTS'
AND xte.application_id = xah.application_id
AND xte.entity_id = xah.entity_id
AND xah.application_id = xal.application_id
AND xah.ae_header_id = xal.ae_header_id
AND xal.code_combination_id = gcc.code_combination_id
AND nvl(ac.org_id, 81) = 81
AND xah.ledger_id = 2021
AND gcc.segment1 = '0010'
AND (gcc.segment3 LIKE '203%' OR
gcc.segment3 IN
('1150000', '2090100', '1190100', '1160000'))
AND gcc.summary_flag = 'N'
AND trunc(ac.check_date) <
(SELECT s.start_date
FROM gl.gl_period_statuses s
WHERE s.application_id = 200
AND s.ledger_id = 2021
AND s.period_name = '24-Jan')) aaa
GROUP BY aaa.vendor_name,
aaa.vendor_num,
aaa.segment3,
aaa.segment8) query_source_bal
WHERE 1 = 1
AND query_source_net.segment3 = query_source_bal.segment3(+)
AND query_source_net.segment8 = query_source_bal.segment8(+)
AND query_source_net.vendor_num = query_source_bal.vendor_num(+)
AND query_source_net.vendor_name = query_source_bal.vendor_name(+);
构建初版其他日记账来源的数据查询SQL：
其他日记账来源查询SQL【点击查看代码】
SELECT /*+parallel(8)*/
oth_net.segment3,
gl_flexfields_pkg.get_description_sql(x_coa_id => 50352,
x_seg_num => 3,
x_seg_val => oth_net.segment3) segment3_desc,
oth_net.segment8,
gl_flexfields_pkg.get_description_sql(x_coa_id => 50352,
x_seg_num => 8,
x_seg_val => oth_net.segment8) segment8_desc,
oth_net.vendor_num,
oth_net.vendor_name,
'CNY' currency_code,
nvl(oth_bal.account_bal, 0) begin_bal,
oth_net.account_dr,
oth_net.account_cr,
(nvl(oth_bal.account_bal, 0) + oth_net.account_dr - oth_net.account_cr) oth_end_bal
FROM (SELECT pv.vendor_name,
pv.segment1 vendor_num,
gcc.segment3,
gcc.segment8,
SUM(nvl(l.accounted_dr, 0)) account_dr,
SUM(nvl(l.accounted_cr, 0)) account_cr
FROM gl.gl_je_headers h,
gl.gl_je_lines l,
gl.gl_period_statuses gps,
gl.gl_code_combinations gcc,
ap.ap_invoices_all aia,
apps.po_vendors pv
WHERE h.ledger_id = 2021
AND h.je_header_id = l.je_header_id
AND h.status = 'P'
AND h.ledger_id = gps.ledger_id
AND h.period_name = gps.period_name
AND h.ledger_id = aia.set_of_books_id(+)
AND gcc.code_combination_id =
aia.accts_pay_code_combination_id(+)
AND aia.vendor_id = pv.vendor_id(+)
AND nvl(aia.org_id, 81) = 81
AND gps.application_id = 200
AND l.code_combination_id = gcc.code_combination_id
AND gps.effective_period_num BETWEEN 20240001 AND 20240001
AND h.je_source <> 'Payables'
AND gcc.segment1 = '0010'
AND (gcc.segment3 LIKE '203%' OR
gcc.segment3 IN ('1150000', '2090100', '1190100', '1160000'))
GROUP BY pv.vendor_name, pv.segment1, gcc.segment3, gcc.segment8) oth_net,
(SELECT pv.vendor_name,
pv.segment1 vendor_num,
gcc.segment3,
gcc.segment8,
SUM(nvl(l.accounted_dr, 0) - nvl(l.accounted_cr, 0)) account_bal
FROM gl.gl_je_headers h,
gl.gl_je_lines l,
gl.gl_period_statuses gps,
gl.gl_code_combinations gcc,
ap.ap_invoices_all aia,
apps.po_vendors pv
WHERE h.ledger_id = 2021
AND h.je_header_id = l.je_header_id
AND h.status = 'P'
AND h.ledger_id = gps.ledger_id
AND h.period_name = gps.period_name
AND h.ledger_id = aia.set_of_books_id(+)
AND gcc.code_combination_id =
aia.accts_pay_code_combination_id(+)
AND aia.vendor_id = pv.vendor_id(+)
AND nvl(aia.org_id, 81) = 81
AND gps.application_id = 200
AND l.code_combination_id = gcc.code_combination_id
AND gps.effective_period_num < 20240001
AND h.je_source <> 'Payables'
AND gcc.segment1 = '0010'
AND (gcc.segment3 LIKE '203%' OR
gcc.segment3 IN ('1150000', '2090100', '1190100', '1160000'))
GROUP BY pv.vendor_name, pv.segment1, gcc.segment3, gcc.segment8) oth_bal
WHERE 1 = 1
AND oth_net.segment3 = oth_bal.segment3(+)
AND oth_net.segment8 = oth_bal.segment8(+)
ORDER BY segment3, segment8, vendor_num;
根据之前的经验，将两串SQL使用全外连接 `full outer join` 进行关联合并，并使用cte表达式对SQL进行优化调整，最后输出的完整代码SQL为：
WITH common_data /*+ MATERIALIZE */
AS
(SELECT gcc.segment3,
gcc.segment8,
xal.accounted_dr,
xal.accounted_cr,
gps.effective_period_num,
xte.entity_code,
xte.source_id_int_1
FROM xla.xla_transaction_entities xte,
gl.gl_period_statuses gps,
xla.xla_ae_headers xah,
xla.xla_ae_lines xal,
gl.gl_code_combinations gcc
WHERE xte.entity_id = xah.entity_id
AND xte.application_id = xah.application_id
AND xah.ae_header_id = xal.ae_header_id
AND xah.application_id = xal.application_id
AND xah.ledger_id = gps.ledger_id
AND xah.period_name = gps.period_name
AND xah.application_id = gps.application_id
AND xah.ledger_id = 2021
AND xal.code_combination_id = gcc.code_combination_id
AND gcc.segment1 = '0010'
AND (gcc.segment3 LIKE '203%' OR
gcc.segment3 IN ('1150000', '2090100', '1190100', '1160000'))
AND xte.application_id = 200),
source_net AS
/*--子模块发生额数据--*/
(SELECT t.segment3,
t.segment8,
t.vendor_num,
t.vendor_name,
'CNY' currency_code,
SUM(t.accounted_dr) net_dr,
SUM(t.accounted_cr) net_cr
FROM (SELECT pv.vendor_name,
pv.segment1 vendor_num,
cd.segment3,
cd.segment8,
nvl(cd.accounted_dr, 0) accounted_dr,
nvl(cd.accounted_cr, 0) accounted_cr
FROM ap.ap_invoices_all ai, apps.po_vendors pv, common_data cd
WHERE ai.vendor_id = pv.vendor_id
AND cd.entity_code = 'AP_INVOICES'
AND cd.source_id_int_1 = ai.invoice_id
AND nvl(ai.org_id, 81) = 81
AND cd.effective_period_num BETWEEN 20240001 AND 20240001
UNION ALL
SELECT pv.vendor_name,
pv.segment1 vendor_num,
cd.segment3,
cd.segment8,
nvl(cd.accounted_dr, 0) accounted_dr,
nvl(cd.accounted_cr, 0) accounted_cr
FROM ap.ap_checks_all ac, apps.po_vendors pv, common_data cd
WHERE ac.vendor_id = pv.vendor_id
AND cd.entity_code = 'AP_PAYMENTS'
AND cd.source_id_int_1 = ac.check_id
AND nvl(ac.org_id, 81) = 81
AND cd.effective_period_num BETWEEN 20240001 AND 20240001) t
GROUP BY t.vendor_name, t.vendor_num, t.segment3, t.segment8),
source_begin AS
/*--子模块期初数据--*/
(SELECT t.segment3,
t.segment8,
t.vendor_num,
t.vendor_name,
'CNY' currency_code,
SUM(nvl(t.beg_bal, 0)) begin_bal
FROM (SELECT pv.vendor_name,
pv.segment1 vendor_num,
cd.segment3,
cd.segment8,
nvl(cd.accounted_dr, 0) - nvl(cd.accounted_cr, 0) beg_bal
FROM ap.ap_invoices_all ai, apps.po_vendors pv, common_data cd
WHERE ai.vendor_id = pv.vendor_id
AND cd.entity_code = 'AP_INVOICES'
AND cd.source_id_int_1 = ai.invoice_id
AND nvl(ai.org_id, 81) = 81
AND cd.effective_period_num < 20240001
UNION ALL
SELECT pv.vendor_name,
pv.segment1 vendor_num,
cd.segment3,
cd.segment8,
nvl(cd.accounted_dr, 0) - nvl(cd.accounted_cr, 0) beg_bal
FROM ap.ap_checks_all ac, apps.po_vendors pv, common_data cd
WHERE ac.vendor_id = pv.vendor_id
AND cd.entity_code = 'AP_PAYMENTS'
AND cd.source_id_int_1 = ac.check_id
AND nvl(ac.org_id, 81) = 81
AND cd.effective_period_num < 20240001) t
GROUP BY t.vendor_name, t.vendor_num, t.segment3, t.segment8),
--日记账数据
gje_net AS
/*--日记账发生额数据--*/
(SELECT gcc.segment3,
gcc.segment8,
SUM(nvl(gjl.accounted_dr, 0)) account_dr,
SUM(nvl(gjl.accounted_cr, 0)) account_cr
FROM gl.gl_je_headers gjh,
gl.gl_je_lines gjl,
gl.gl_period_statuses gps,
gl.gl_code_combinations gcc
WHERE gjh.je_header_id = gjl.je_header_id
AND gjh.ledger_id = gps.ledger_id
AND gjh.period_name = gps.period_name
AND gps.application_id = 101
AND gjl.code_combination_id = gcc.code_combination_id
AND gjh.status = 'P'
AND gjh.je_source <> 'Payables'
AND gjh.ledger_id = 2021
AND gps.effective_period_num BETWEEN 20240001 AND 20240001
AND gcc.segment1 = '0010'
AND (gcc.segment3 LIKE '203%' OR
gcc.segment3 IN ('1150000', '2090100', '1190100', '1160000'))
GROUP BY gcc.segment3, gcc.segment8),
je_begin AS
/*--日记账期初余额*/
(SELECT gcc.segment3,
gcc.segment8,
SUM(nvl(gjl.accounted_dr, 0) - nvl(gjl.accounted_cr, 0)) begin_bal
FROM gl.gl_je_headers gjh,
gl.gl_je_lines gjl,
gl.gl_period_statuses gps,
gl.gl_code_combinations gcc
WHERE gjh.je_header_id = gjl.je_header_id
AND gjh.ledger_id = gps.ledger_id
AND gjh.period_name = gps.period_name
AND gps.application_id = 101
AND gjl.code_combination_id = gcc.code_combination_id
AND gjh.status = 'P'
AND gjh.je_source <> 'Payables'
AND gjh.ledger_id = 2021
AND gps.effective_period_num < 20240001
AND gcc.segment1 = '0010'
AND (gcc.segment3 LIKE '203%' OR
gcc.segment3 IN ('1150000', '2090100', '1190100', '1160000'))
GROUP BY gcc.segment3, gcc.segment8)
--均使用全外连接关联数据
--子模块
SELECT coalesce(sn.segment3, sb.segment3) segment3,
gl_flexfields_pkg.get_description_sql(x_coa_id => 50352,
x_seg_num => 3,
x_seg_val => coalesce(sn.segment3,
sb.segment3)) segment3_desc,
coalesce(sn.segment8, sb.segment8) segment8,
gl_flexfields_pkg.get_description_sql(x_coa_id => 50352,
x_seg_num => 8,
x_seg_val => coalesce(sn.segment8,
sb.segment8)) segment8_desc,
coalesce(sn.vendor_num, sb.vendor_num) vendor_num,
coalesce(sn.vendor_name, sb.vendor_name) vendor_name,
'CNY' currency_code,
SUM(nvl(sb.begin_bal, 0)) begin_bal,
SUM(nvl(sn.net_dr, 0)) net_dr,
SUM(nvl(sn.net_cr, 0)) net_cr,
SUM(nvl(sb.begin_bal, 0) + nvl(sn.net_dr, 0) - nvl(sn.net_cr, 0)) end_bal
FROM source_net sn
FULL OUTER JOIN source_begin sb
ON sn.segment3 = sb.segment3
AND sn.segment8 = sb.segment8
AND sn.vendor_num = sb.vendor_num
GROUP BY coalesce(sn.segment3, sb.segment3),
coalesce(sn.segment8, sb.segment8),
coalesce(sn.vendor_num, sb.vendor_num),
coalesce(sn.vendor_name, sb.vendor_name)
UNION ALL
--GL 模块
SELECT coalesce(jn.segment3, jb.segment3) segment3,
gl_flexfields_pkg.get_description_sql(x_coa_id => 50352,
x_seg_num => 3,
x_seg_val => coalesce(jn.segment3,
jb.segment3)) segment3_desc,
coalesce(jn.segment8, jb.segment8) segment8,
gl_flexfields_pkg.get_description_sql(x_coa_id => 50352,
x_seg_num => 8,
x_seg_val => coalesce(jn.segment8,
jb.segment8)) segment8_desc,
NULL AS vendor_num,
NULL AS vendor_name,
'CNY' currency_code,
SUM(nvl(jb.begin_bal, 0)) begin_bal,
SUM(nvl(jn.account_dr, 0)) account_dr,
SUM(nvl(jn.account_cr, 0)) account_cr,
SUM(nvl(jb.begin_bal, 0) + nvl(jn.account_dr, 0) -
nvl(jn.account_cr, 0)) end_bal
FROM gje_net jn
FULL OUTER JOIN je_begin jb
ON jn.segment3 = jb.segment3
AND jn.segment8 = jb.segment8
GROUP BY coalesce(jn.segment3, jb.segment3),
coalesce(jn.segment8, jb.segment8)
ORDER BY segment3, segment8, vendor_num;
将代码转换部署为EBS系统中的并发请求，运行请求输出对应的数据结果文件，与财务人员提供的对比数据进行核对，完美对上！！！ 最后，也是完整的解决了财务的需求内容。
+++==========================================================================+++
以上内容仅为个人学习记录使用。
如有转载，请附有原文出处链接。
浙公网安备 33010602011771号