🚀 Building a Production-Ready Supplier 360 API in Oracle EBS
In enterprise Oracle EBS environments, supplier data is distributed across multiple tables. Finance and procurement teams require a complete 360-degree view including sites, purchase orders, invoices, and payments.
Objective: Build a structured JSON-based Supplier 360 API using PL/SQL and expose it via ORDS.
🎯 Why Supplier 360?
Consolidate supplier information
Enable dashboard-ready JSON output
Expose via REST (ORDS)
Support CFO-level reporting
Reduce multiple query dependency
🏗 Architecture
Database → PL/SQL JSON Builder → ORDS → REST API → Dashboard / External System
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;
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, '{');
-------------------------------------------------
-- SUPPLIER HEADER
-------------------------------------------------
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, '},');
-------------------------------------------------
-- SITES
-------------------------------------------------
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, '],');
-------------------------------------------------
-- PURCHASE ORDERS
-------------------------------------------------
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, '],');
-------------------------------------------------
-- INVOICES + PAYMENTS
-------------------------------------------------
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;
/
💎 Core Function
FUNCTION get_supplier_360 (
p_supplier_id IN NUMBER
) RETURN CLOB;
SELECT JSON_SERIALIZE(
xxqst_supplier_360_api_pkg.get_supplier_360(1001)
RETURNING CLOB PRETTY
) AS formatted_json
FROM dual;
This function manually builds structured JSON using DBMS_LOB to avoid 4000-character limitations and nested JSON performance issues.
📦 JSON Structure Returned
[
{
"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"
},
{
"site_id" : "23002",
"site_code" : "GST_DELHI",
"city" : "Delhi"
},
{
"site_id" : "23001",
"site_code" : "GST_DELHI",
"city" : "Delhi"
},
{
"site_id" : "64001",
"site_code" : "GST_DELHI",
"city" : "Delhi"
},
{
"site_id" : "166001",
"site_code" : "GST_DELHI",
"city" : "Delhi"
},
{
"site_id" : "166003",
"site_code" : "GST_DELHI",
"city" : "Delhi"
},
{
"site_id" : "166002",
"site_code" : "GST_DELHI",
"city" : "Delhi"
},
{
"site_id" : "8001",
"site_code" : "GST_HYD",
"city" : "Delhi"
},
{
"site_id" : "7001",
"site_code" : "GST_Pune",
"city" : "Delhi"
}
],
"purchase_orders" :
[
],
"invoices" :
[
{
"invoice_number" : "110001-TDS-SI-10400",
"invoice_date" : "2025-09-01",
"invoice_amount" : "1000",
"payments" :
[
]
},
{
"invoice_number" : "11111-TDS-SI-10420",
"invoice_date" : "2025-09-03",
"invoice_amount" : "2000",
"payments" :
[
]
},
{
"invoice_number" : "adv11111-RTN-TDS-CM-10422",
"invoice_date" : "2025-09-03",
"invoice_amount" : "-2000",
"payments" :
[
]
},
{
"invoice_number" : "CAN/adv11111-RTN-TDS-CM-10422",
"invoice_date" : "2025-09-03",
"invoice_amount" : "2000",
"payments" :
[
]
},
{
"invoice_number" : "ERS-13-FEB-25-541-TDS-SI-10320",
"invoice_date" : "2025-02-13",
"invoice_amount" : "300000",
"payments" :
[
]
},
{
"invoice_number" : "ERS-11-FEB-25-522-TDS-SI-10321",
"invoice_date" : "2025-02-11",
"invoice_amount" : "1000000",
"payments" :
[
]
},
{
"invoice_number" : "ERS-06-JAN-25-511-TDS-SI-10322",
"invoice_date" : "2025-01-06",
"invoice_amount" : "150",
"payments" :
[
]
},
{
"invoice_number" : "1123-TDS-SI-10323",
"invoice_date" : "2024-11-28",
"invoice_amount" : "15",
"payments" :
[
]
},
{
"invoice_number" : "01-TDS-SI-10324",
"invoice_date" : "2024-10-24",
"invoice_amount" : "100",
"payments" :
[
]
},
{
"invoice_number" : "USD-2-TDS-SI-10325",
"invoice_date" : "2024-08-09",
"invoice_amount" : "960",
"payments" :
[
]
},
{
"invoice_number" : "TESTSECR-TDS-SI-10326",
"invoice_date" : "2024-07-16",
"invoice_amount" : "100",
"payments" :
[
]
},
{
"invoice_number" : "10010-TDS-SI-10360",
"invoice_date" : "2025-08-31",
"invoice_amount" : "100",
"payments" :
[
]
},
{
"invoice_number" : "adv11111-TDS-SI-10421",
"invoice_date" : "2025-09-03",
"invoice_amount" : "0",
"payments" :
[
]
},
{
"invoice_number" : "100000A1-TDS-SI-10340",
"invoice_date" : "2025-08-29",
"invoice_amount" : "1000",
"payments" :
[
]
},
{
"invoice_number" : "13001-TDS-SI-10380",
"invoice_date" : "2025-09-01",
"invoice_amount" : "1000",
"payments" :
[
]
},
{
"invoice_number" : "1201-TDS-SI-10381",
"invoice_date" : "2025-09-01",
"invoice_amount" : "1000",
"payments" :
[
]
},
{
"invoice_number" : "112-TDS-SI-10460",
"invoice_date" : "2025-09-04",
"invoice_amount" : "300",
"payments" :
[
]
},
{
"invoice_number" : "116-TDS-SI-10480",
"invoice_date" : "2025-09-04",
"invoice_amount" : "100",
"payments" :
[
]
},
{
"invoice_number" : "adv909090-TDS-SI-10560",
"invoice_date" : "2025-09-05",
"invoice_amount" : "100",
"payments" :
[
]
},
{
"invoice_number" : "adv909090-RTN-TDS-CM-10561",
"invoice_date" : "2025-09-05",
"invoice_amount" : "-100",
"payments" :
[
]
},
{
"invoice_number" : "808080-TDS-SI-10580",
"invoice_date" : "2025-09-05",
"invoice_amount" : "1000",
"payments" :
[
]
},
{
"invoice_number" : "adv808080-TDS-SI-10600",
"invoice_date" : "2025-09-05",
"invoice_amount" : "1000",
"payments" :
[
]
},
{
"invoice_number" : "adv808080-RTN-TDS-CM-10601",
"invoice_date" : "2025-09-05",
"invoice_amount" : "-1000",
"payments" :
[
]
},
{
"invoice_number" : "114-RTN-TDS-CM-10520",
"invoice_date" : "2025-09-05",
"invoice_amount" : "-100",
"payments" :
[
]
},
{
"invoice_number" : "pr12121-TDS-SI-10620",
"invoice_date" : "2025-09-05",
"invoice_amount" : "200",
"payments" :
[
]
},
{
"invoice_number" : "114-TDS-SI-10500",
"invoice_date" : "2025-09-04",
"invoice_amount" : "52850",
"payments" :
[
]
},
{
"invoice_number" : "909090-TDS-SI-10540",
"invoice_date" : "2025-09-05",
"invoice_amount" : "100",
"payments" :
[
]
},
{
"invoice_number" : "1911-TDS-SI-10440",
"invoice_date" : "2025-09-04",
"invoice_amount" : "12000",
"payments" :
[
]
},
{
"invoice_number" : "1912-TDS-SI-10441",
"invoice_date" : "2025-09-04",
"invoice_amount" : "100000",
"payments" :
[
]
},
{
"invoice_number" : "17909-TDS-SI-10640",
"invoice_date" : "2025-09-10",
"invoice_amount" : "90000",
"payments" :
[
]
},
{
"invoice_number" : "109876-TDS-SI-10660",
"invoice_date" : "2025-09-11",
"invoice_amount" : "8000",
"payments" :
[
]
},
{
"invoice_number" : "17-SEP-2025-TDS-SI-10740",
"invoice_date" : "2025-09-17",
"invoice_amount" : "0",
"payments" :
[
]
},
{
"invoice_number" : "invrmd01-TDS-SI-10860",
"invoice_date" : "2025-09-23",
"invoice_amount" : "100",
"payments" :
[
]
},
{
"invoice_number" : "invremd01-TDS-SI-10861",
"invoice_date" : "2025-09-23",
"invoice_amount" : "0",
"payments" :
[
]
},
{
"invoice_number" : "@1201",
"invoice_date" : "2025-09-18",
"invoice_amount" : "-500",
"payments" :
[
]
},
{
"invoice_number" : "@75684",
"invoice_date" : "2025-09-18",
"invoice_amount" : "1500",
"payments" :
[
]
},
{
"invoice_number" : "emd-inv01-TDS-SI-10863",
"invoice_date" : "2025-09-23",
"invoice_amount" : "300",
"payments" :
[
]
},
{
"invoice_number" : "0111-TDS-SI-10780",
"invoice_date" : "2025-09-22",
"invoice_amount" : "10000",
"payments" :
[
]
},
{
"invoice_number" : "1954-TDS-SI-10920",
"invoice_date" : "2025-09-26",
"invoice_amount" : "2000",
"payments" :
[
]
},
{
"invoice_number" : "test09-TDS-SI-10980",
"invoice_date" : "2025-10-08",
"invoice_amount" : "1000",
"payments" :
[
]
},
{
"invoice_number" : "656564-TDS-SI-10901",
"invoice_date" : "2025-09-26",
"invoice_amount" : "2500",
"payments" :
[
]
},
{
"invoice_number" : "761208-TDS-SI-10960",
"invoice_date" : "2025-09-29",
"invoice_amount" : "8100",
"payments" :
[
]
},
{
"invoice_number" : "987621-TDS-SI-11000",
"invoice_date" : "2025-10-08",
"invoice_amount" : "1000",
"payments" :
[
]
},
{
"invoice_number" : "ST986-TDS-SI-11020",
"invoice_date" : "2025-10-08",
"invoice_amount" : "1000",
"payments" :
[
]
},
{
"invoice_number" : "ST986-RTN-TDS-CM-11021",
"invoice_date" : "2025-10-08",
"invoice_amount" : "-1000",
"payments" :
[
]
},
{
"invoice_number" : "10011-TDS-SI-10820",
"invoice_date" : "2025-09-23",
"invoice_amount" : "10000",
"payments" :
[
]
},
{
"invoice_number" : "10011A-TDS-SI-10840",
"invoice_date" : "2025-09-23",
"invoice_amount" : "10000",
"payments" :
[
]
},
{
"invoice_number" : "invc01-TDS-SI-10862",
"invoice_date" : "2025-09-23",
"invoice_amount" : "0",
"payments" :
[
]
},
{
"invoice_number" : "SERB1-TDS-SI-10900",
"invoice_date" : "2025-09-26",
"invoice_amount" : "1000",
"payments" :
[
]
},
{
"invoice_number" : "invp01-TDS-SI-10940",
"invoice_date" : "2025-09-29",
"invoice_amount" : "400",
"payments" :
[
]
},
{
"invoice_number" : "ch01-TDS-SI-11040",
"invoice_date" : "2025-10-27",
"invoice_amount" : "100",
"payments" :
[
]
},
{
"invoice_number" : "8909870-RTN-TDS-CM-10702",
"invoice_date" : "2025-09-15",
"invoice_amount" : "-200",
"payments" :
[
]
},
{
"invoice_number" : "inv0999-TDS-SI-10800",
"invoice_date" : "2025-09-22",
"invoice_amount" : "50",
"payments" :
[
]
},
{
"invoice_number" : "101C",
"invoice_date" : "2025-09-18",
"invoice_amount" : "-500",
"payments" :
[
]
},
{
"invoice_number" : "100D",
"invoice_date" : "2025-09-19",
"invoice_amount" : "1",
"payments" :
[
]
},
{
"invoice_number" : "15Test-TDS-SI-10680",
"invoice_date" : "2025-09-15",
"invoice_amount" : "10",
"payments" :
[
]
},
{
"invoice_number" : "@12345678",
"invoice_date" : "2025-09-19",
"invoice_amount" : "500",
"payments" :
[
{
"payment_number" : "800256",
"payment_date" : "2025-09-19",
"payment_amount" : "500"
}
]
},
{
"invoice_number" : "inv002-TDS-SI-10760",
"invoice_date" : "2025-09-22",
"invoice_amount" : "300",
"payments" :
[
]
},
{
"invoice_number" : "10011A-RTN-TDS-CM-10841",
"invoice_date" : "2025-09-23",
"invoice_amount" : "-10000",
"payments" :
[
]
},
{
"invoice_number" : "2332-TDS-SI-10700",
"invoice_date" : "2025-09-15",
"invoice_amount" : "200",
"payments" :
[
]
},
{
"invoice_number" : "8909870-TDS-SI-10701",
"invoice_date" : "2025-09-15",
"invoice_amount" : "1000",
"payments" :
[
]
},
{
"invoice_number" : "1096-TDS-SI-10720",
"invoice_date" : "2025-09-17",
"invoice_amount" : "4000",
"payments" :
[
]
},
{
"invoice_number" : "23489",
"invoice_date" : "2025-09-18",
"invoice_amount" : "-500",
"payments" :
[
]
},
{
"invoice_number" : "45673",
"invoice_date" : "2025-09-19",
"invoice_amount" : "1500",
"payments" :
[
{
"payment_number" : "800252",
"payment_date" : "2025-09-19",
"payment_amount" : "1500"
}
]
},
{
"invoice_number" : "invr001-TDS-SI-10880",
"invoice_date" : "2025-09-25",
"invoice_amount" : "700",
"payments" :
[
]
},
{
"invoice_number" : "1001D",
"invoice_date" : "2025-09-18",
"invoice_amount" : "-500",
"payments" :
[
]
},
{
"invoice_number" : "@123456",
"invoice_date" : "2025-09-18",
"invoice_amount" : "-500",
"payments" :
[
]
},
{
"invoice_number" : "@1234567",
"invoice_date" : "2025-09-18",
"invoice_amount" : "-500",
"payments" :
[
]
},
{
"invoice_number" : "ADV12-TDS-SI-11220",
"invoice_date" : "2026-01-19",
"invoice_amount" : "1000",
"payments" :
[
]
},
{
"invoice_number" : "123400-TDS-SI-11221",
"invoice_date" : "2026-01-19",
"invoice_amount" : "1200",
"payments" :
[
]
},
{
"invoice_number" : "invoice1111-TDS-SI-11180",
"invoice_date" : "2025-11-28",
"invoice_amount" : "10000",
"payments" :
[
]
},
{
"invoice_number" : "ST432-TDS-SI-11261",
"invoice_date" : "2026-01-19",
"invoice_amount" : "1000",
"payments" :
[
]
},
{
"invoice_number" : "ST432-RTN-TDS-CM-11262",
"invoice_date" : "2026-01-19",
"invoice_amount" : "-1000",
"payments" :
[
]
},
{
"invoice_number" : "ST123409-RTN-TDS-CM-11062",
"invoice_date" : "2025-11-05",
"invoice_amount" : "-2000",
"payments" :
[
]
},
{
"invoice_number" : "inv2222-TDS-SI-11100",
"invoice_date" : "2025-11-08",
"invoice_amount" : "0",
"payments" :
[
]
},
{
"invoice_number" : "inv5555-TDS-SI-11140",
"invoice_date" : "2025-11-08",
"invoice_amount" : "555.5",
"payments" :
[
]
},
{
"invoice_number" : "inv6666-TDS-SI-11160",
"invoice_date" : "2025-11-08",
"invoice_amount" : "666.6",
"payments" :
[
]
},
{
"invoice_number" : "1003-TDS-SI-11200",
"invoice_date" : "2026-01-16",
"invoice_amount" : "400",
"payments" :
[
]
},
{
"invoice_number" : "123400-RTN-TDS-CM-11222",
"invoice_date" : "2026-01-19",
"invoice_amount" : "-1000",
"payments" :
[
]
},
{
"invoice_number" : "12300-TDS-SI-11240",
"invoice_date" : "2026-01-19",
"invoice_amount" : "500",
"payments" :
[
]
},
{
"invoice_number" : "3576-TDS-SI-11260",
"invoice_date" : "2026-01-19",
"invoice_amount" : "1000",
"payments" :
[
]
},
{
"invoice_number" : "inv1111-TDS-SI-11080",
"invoice_date" : "2025-11-08",
"invoice_amount" : "111.1",
"payments" :
[
]
},
{
"invoice_number" : "123409-TDS-SI-11060",
"invoice_date" : "2025-11-05",
"invoice_amount" : "2000",
"payments" :
[
]
},
{
"invoice_number" : "ST123409-TDS-SI-11061",
"invoice_date" : "2025-11-05",
"invoice_amount" : "2000",
"payments" :
[
]
},
{
"invoice_number" : "inv44444-TDS-SI-11120",
"invoice_date" : "2025-11-08",
"invoice_amount" : "444.4",
"payments" :
[
]
},
{
"invoice_number" : "STp01-TDS-SI-11361",
"invoice_date" : "2026-02-03",
"invoice_amount" : "1000",
"payments" :
[
]
},
{
"invoice_number" : "try1-TDS-SI-11320",
"invoice_date" : "2026-01-21",
"invoice_amount" : "600",
"payments" :
[
]
},
{
"invoice_number" : "STp01-RTN-TDS-CM-11362",
"invoice_date" : "2026-02-03",
"invoice_amount" : "-1000",
"payments" :
[
]
},
{
"invoice_number" : "PRE02-TDS-SI-11363",
"invoice_date" : "2026-02-03",
"invoice_amount" : "2000",
"payments" :
[
]
},
{
"invoice_number" : "we23-pre-TDS-SI-11364",
"invoice_date" : "2026-02-03",
"invoice_amount" : "500",
"payments" :
[
]
},
{
"invoice_number" : "STp02-TDS-SI-11365",
"invoice_date" : "2026-02-03",
"invoice_amount" : "2000",
"payments" :
[
]
},
{
"invoice_number" : "STp02-RTN-TDS-CM-11366",
"invoice_date" : "2026-02-03",
"invoice_amount" : "-2000",
"payments" :
[
]
},
{
"invoice_number" : "we23-2-TDS-SI-11367",
"invoice_date" : "2026-02-03",
"invoice_amount" : "500",
"payments" :
[
]
},
{
"invoice_number" : "PRE01-TDS-SI-11360",
"invoice_date" : "2026-02-03",
"invoice_amount" : "1000",
"payments" :
[
]
},
{
"invoice_number" : "try3-TDS-SI-11340",
"invoice_date" : "2026-01-21",
"invoice_amount" : "1000",
"payments" :
[
]
},
{
"invoice_number" : "0001-TDS-SI-11341",
"invoice_date" : "2026-01-21",
"invoice_amount" : "10000",
"payments" :
[
]
},
{
"invoice_number" : "try5-TDS-SI-11342",
"invoice_date" : "2026-01-21",
"invoice_amount" : "2000",
"payments" :
[
]
},
{
"invoice_number" : "try6-TDS-SI-11343",
"invoice_date" : "2026-01-21",
"invoice_amount" : "50",
"payments" :
[
]
},
{
"invoice_number" : "try7-TDS-SI-11344",
"invoice_date" : "2026-01-21",
"invoice_amount" : "80",
"payments" :
[
]
},
{
"invoice_number" : "try8-TDS-SI-11345",
"invoice_date" : "2026-01-21",
"invoice_amount" : "3",
"payments" :
[
]
},
{
"invoice_number" : "ACC_01-TDS-SI-11280",
"invoice_date" : "2026-01-20",
"invoice_amount" : "0",
"payments" :
[
]
},
{
"invoice_number" : "ACC_02-TDS-SI-11300",
"invoice_date" : "2026-01-20",
"invoice_amount" : "0",
"payments" :
[
]
},
{
"invoice_number" : "STSER1-TDS-SI-11400",
"invoice_date" : "2026-02-27",
"invoice_amount" : "1500",
"payments" :
[
]
},
{
"invoice_number" : "122-TDS-SI-11380",
"invoice_date" : "2026-02-18",
"invoice_amount" : "12000",
"payments" :
[
]
},
{
"invoice_number" : "223-TDS-SI-11381",
"invoice_date" : "2026-02-18",
"invoice_amount" : "3600",
"payments" :
[
]
}
]
}
]
{
"supplier_header": {
"supplier_id": "...",
"supplier_name": "...",
"supplier_number": "...",
"creation_date": "..."
},
"sites": [...],
"purchase_orders": [...],
"invoices": [
{
"invoice_number": "...",
"invoice_date": "...",
"invoice_amount": "...",
"payments": [...]
}
]
}
📊 Data Sources
Supplier Header → AP_SUPPLIERS
Sites → AP_SUPPLIER_SITES_ALL
Purchase Orders → PO_HEADERS_ALL
Invoices → AP_INVOICES_ALL
Payments → AP_INVOICE_PAYMENTS_ALL + AP_CHECKS_ALL
🚀 How to Use
SQL Test:
SELECT xxqst_supplier_360_api_pkg.get_supplier_360(1001)
FROM dual;
ORDS Handler:
BEGIN
:response := xxqst_supplier_360_api_pkg.get_supplier_360(:supplier_id);
END;
Call via REST:
http://server/ords/apps/supplier360/get/1001
📈 Business Value
🔍 Key Learnings
Always verify EBS table structure using DESC
Avoid reserved keywords in aliases
Use DBMS_LOB for large JSON outputs
Test each module independently
Supplier 360 API is not just an integration layer — it is a financial visibility engine.
Developed using Oracle EBS | PL/SQL | ORDS | Enterprise REST Architecture