使用PL/SQL和ORDS在Oracle EBS中构建生产级供应商360 API
JSONOracle EBSPL/SQLREST API供应商管理数据整合
原文地址: https://apex.oracle.com/pls/apex/r/nidhiblog/nidhi/view-blog-post?p16_id=321
# 使用PL/SQL和ORDS在Oracle EBS中构建生产级供应商360 API
在企业级Oracle EBS环境中，供应商数据分散在多个表中。财务和采购团队需要包含站点、采购订单、发票和付款的完整360度视图。
## 目标
构建基于结构化JSON的Supplier 360 API，并使用PL/SQL通过ORDS公开它。
## 为什么需要供应商360视图？
- 整合供应商信息
- 生成仪表板就绪的JSON输出
- 通过REST(ORDS)公开
- 支持CFO级别报表
- 减少多重查询依赖
## 架构
数据库 → PL/SQL JSON构建器 → ORDS → REST API → 仪表板/外部系统
```sql
CREATE OR REPLACE PACKAGE xxqst_supplier_360_api_pkg AS
FUNCTION get_supplier_360 (
p_supplier_id IN NUMBER
) RETURN CLOB;
END xxqst_supplier_360_api_pkg;
```
```sql
CREATE OR REPLACE PACKAGE BODY xxqst_supplier_360_api_pkg AS
FUNCTION get_supplier_360 (
p_supplier_id IN NUMBER
) RETURN CLOB IS
l_json CLOB;
l_first BOOLEAN := TRUE;
BEGIN
DBMS_LOB.createtemporary(l_json, TRUE);
DBMS_LOB.append(l_json, '[');
FOR s IN (
SELECT vendor_id,
vendor_name,
segment1 supplier_number,
creation_date
FROM ap_suppliers
WHERE vendor_id = p_supplier_id
)
LOOP
IF NOT l_first THEN
DBMS_LOB.append(l_json, ',');
END IF;
l_first := FALSE;
DBMS_LOB.append(l_json, '{');
-------------------------------------------------
-- 供应商头部信息
-------------------------------------------------
DBMS_LOB.append(l_json, '"supplier_header":{');
DBMS_LOB.append(l_json, '"supplier_id":"' || s.vendor_id || '",');
DBMS_LOB.append(l_json, '"supplier_name":"' || REPLACE(s.vendor_name,'"','') || '",');
DBMS_LOB.append(l_json, '"supplier_number":"' || s.supplier_number || '",');
DBMS_LOB.append(l_json, '"creation_date":"' || TO_CHAR(s.creation_date,'YYYY-MM-DD') || '"');
DBMS_LOB.append(l_json, '},');
-------------------------------------------------
-- 站点信息
-------------------------------------------------
DBMS_LOB.append(l_json, '"sites":[');
DECLARE
l_first_site BOOLEAN := TRUE;
BEGIN
FOR site_rec IN (
SELECT vendor_site_id,
vendor_site_code,
city
FROM ap_supplier_sites_all
WHERE vendor_id = s.vendor_id
)
LOOP
IF NOT l_first_site THEN
DBMS_LOB.append(l_json, ',');
END IF;
l_first_site := FALSE;
DBMS_LOB.append(l_json, '{');
DBMS_LOB.append(l_json, '"site_id":"' || site_rec.vendor_site_id || '",');
DBMS_LOB.append(l_json, '"site_code":"' || site_rec.vendor_site_code || '",');
DBMS_LOB.append(l_json, '"city":"' || NVL(site_rec.city,'') || '"');
DBMS_LOB.append(l_json, '}');
END LOOP;
END;
DBMS_LOB.append(l_json, '],');
-------------------------------------------------
-- 采购订单
-------------------------------------------------
DBMS_LOB.append(l_json, '"purchase_orders":[');
DECLARE
l_first_po BOOLEAN := TRUE;
BEGIN
FOR po_rec IN (
SELECT segment1,
creation_date,
NVL(amount_limit,0) amount_limit
FROM po_headers_all
WHERE vendor_id = s.vendor_id
)
LOOP
IF NOT l_first_po THEN
DBMS_LOB.append(l_json, ',');
END IF;
l_first_po := FALSE;
DBMS_LOB.append(l_json, '{');
DBMS_LOB.append(l_json, '"po_number":"' || po_rec.segment1 || '",');
DBMS_LOB.append(l_json, '"po_date":"' || TO_CHAR(po_rec.creation_date,'YYYY-MM-DD') || '",');
DBMS_LOB.append(l_json, '"po_amount":"' || po_rec.amount_limit || '"');
DBMS_LOB.append(l_json, '}');
END LOOP;
END;
DBMS_LOB.append(l_json, '],');
-------------------------------------------------
-- 发票+付款
-------------------------------------------------
DBMS_LOB.append(l_json, '"invoices":[');
DECLARE
l_first_inv BOOLEAN := TRUE;
BEGIN
FOR inv IN (
SELECT invoice_id,
invoice_num,
invoice_amount,
invoice_date
FROM ap_invoices_all
WHERE vendor_id = s.vendor_id
)
LOOP
IF NOT l_first_inv THEN
DBMS_LOB.append(l_json, ',');
END IF;
l_first_inv := FALSE;
DBMS_LOB.append(l_json, '{');
DBMS_LOB.append(l_json, '"invoice_number":"' || inv.invoice_num || '",');
DBMS_LOB.append(l_json, '"invoice_date":"' || TO_CHAR(inv.invoice_date,'YYYY-MM-DD') || '",');
DBMS_LOB.append(l_json, '"invoice_amount":"' || inv.invoice_amount || '",');
DBMS_LOB.append(l_json, '"payments":[');
DECLARE
l_first_pay BOOLEAN := TRUE;
BEGIN
FOR pay IN (
SELECT ac.check_number,
ac.check_date,
aip.amount
FROM ap_invoice_payments_all aip
JOIN ap_checks_all ac
ON ac.check_id = aip.check_id
WHERE aip.invoice_id = inv.invoice_id
)
LOOP
IF NOT l_first_pay THEN
DBMS_LOB.append(l_json, ',');
END IF;
l_first_pay := FALSE;
DBMS_LOB.append(l_json, '{');
DBMS_LOB.append(l_json, '"payment_number":"' || pay.check_number || '",');
DBMS_LOB.append(l_json, '"payment_date":"' || TO_CHAR(pay.check_date,'YYYY-MM-DD') || '",');
DBMS_LOB.append(l_json, '"payment_amount":"' || pay.amount || '"');
DBMS_LOB.append(l_json, '}');
END LOOP;
END;
DBMS_LOB.append(l_json, ']');
DBMS_LOB.append(l_json, '}');
END LOOP;
END;
DBMS_LOB.append(l_json, ']');
DBMS_LOB.append(l_json, '}');
END LOOP;
DBMS_LOB.append(l_json, ']');
RETURN l_json;
END get_supplier_360;
END xxqst_supplier_360_api_pkg;
/
```
## 核心功能
```sql
FUNCTION get_supplier_360 (
p_supplier_id IN NUMBER
) RETURN CLOB;
```
```sql
SELECT JSON_SERIALIZE(
xxqst_supplier_360_api_pkg.get_supplier_360(1001)
RETURNING CLOB PRETTY
) AS formatted_json
FROM dual;
```
该函数使用DBMS_LOB手动构建结构化JSON，以避免4000字符限制和嵌套JSON性能问题。
## 返回的JSON结构
```json
[
{
"supplier_header" :
{
"supplier_id" : "1001",
"supplier_name" : "India TDS Tax Authority",
"supplier_number" : "1001",
"creation_date" : "2024-07-08"
},
"sites" :
[
{
"site_id" : "1001",
"site_code" : "GST_DELHI",
"city" : "Delhi"
}
],
"purchase_orders" :
[
],
"invoices" :
[
{
"invoice_number" : "110001-TDS-SI-10400",
"invoice_date" : "2025-09-01",
"invoice_amount" : "1000",
"payments" :
[
]
}
]
}
]
```
## 数据来源
- 供应商头部 → AP_SUPPLIERS
- 站点 → AP_SUPPLIER_SITES_ALL
- 采购订单 → PO_HEADERS_ALL
- 发票 → AP_INVOICES_ALL
- 付款 → AP_INVOICE_PAYMENTS_ALL + AP_CHECKS_ALL
## 使用方法
SQL测试:
```sql
SELECT xxqst_supplier_360_api_pkg.get_supplier_360(1001)
FROM dual;
```
ORDS处理程序:
```sql
BEGIN
:response := xxqst_supplier_360_api_pkg.get_supplier_360(:supplier_id);
END;
```
通过REST调用:
```
http://server/ords/apps/supplier360/get/1001
```
## 业务价值
- 供应商绩效仪表板
- 未结项跟踪
- PO与发票可见性
- 付款监控
- 外部ERP集成
## 关键学习点
- 始终使用DESC验证EBS表结构
- 避免在别名中使用保留关键字
- 使用DBMS_LOB处理大型JSON输出
- 独立测试每个模块
- Supplier 360 API不仅是一个集成层——它是一个财务可见性引擎
使用Oracle EBS | PL/SQL | ORDS | 企业REST架构开发