JSON Format and Its Usage in Oracle EBS
Oracle E-Business Suite (EBS) is a group of applications that help businesses manage their processes. Connecting EBS with other systems smoothly is important. JSON (JavaScript Object Notation) has become popular for Oracle EBS integrations because it’s lightweight and easy to read, making data exchange simple. However, older versions don’t support JSON natively, so you’ll need some workarounds.
Table of Contents
Inbound Interface Example: Loading Data into Oracle EBS
Insert Oracle Staging table from PL/SQL Record Type
Insert Staging Table from JSON file
Extracting Purchase Order JSON File from Oracle EBS
Why JSON for Oracle EBS?
JSON is a simple text format that’s easy for people to read and write, and machines can process it efficiently. It’s popular because it’s straightforward and works well for data interchange, especially in web apps and APIs. Compared to XML, JSON is smaller and easier to work with, which is why it’s great for modern integrations. In Oracle EBS, JSON is mostly used in RESTful web services, mobile apps, and cloud integrations. However, Oracle R12 doesn’t have built-in JSON functions, so you might need to use string manipulation or process it outside the database.
Common Uses of JSON in Oracle EBS
- REST API Integrations: EBS often uses JSON to talk to other systems via REST APIs. For example, syncing sales order and delivery data with third party WMS system.
- Mobile Apps: JSON makes data exchange faster for mobile apps connected to EBS.
- Data Export and Reporting: JSON is a good way to move data from EBS to analytics tools.
- Custom Web Services: Developers use PL/SQL to build web services in EBS, often using JSON for data exchange.
Inbound Interface Example: Loading Data into Oracle EBS (R12)
Scenario: An 3PL System sends Purchase Receiving Data to Oracle EBS in JSON format.
Sample JSON Payload:
Sample Inbound JSON File
{
"RequestHeader": {
"MessageId": "3PL25040301",
"RequestType": "PRCT",
"RequestDate": "2025-04-03T09:00:00-06:00",
},
"RequestBody": {
"Receipt": [
{
"ReceiptHeader": {
"ReceiptNumber": "RCPT-240402",
"Warehouse": "WSH",
"OrderType": "PO",
"AsnNumber": "1005",
"TransactionNums": 2,
"ReceiptLines": [
{
"PONumber": 1234,
"OrderLineNumber": 1.1,
"ReceivedQuantity": 10,
"ItemNumber": "045839920",
"Subinventory": "FG",
"UOM": "EA",
"TrxType": "RECEIPT",
"TransactionDate": "2025-04-03T09:00:00-06:00",
"ReceiptActionType": "Lot",
"LotNumber": "LT250403-01",
"LotExpDate":""
},
{
"PONumber": 1234,
"OrderLineNumber": 2.1,
"ReceivedQuantity": 20,
"ItemNumber": "045839920",
"Subinventory": "FG",
"UOM": "EA",
"TrxType": "RECEIPT",
"TransactionDate": "2025-04-03T09:00:00-06:00",
"ReceiptActionType": "Lot",
"LotNumber": "LT250403-02",
"LotExpDate":"2026-04-03T09:00:00-06:00"
}
]
}
}
]
}
}- Firstly, Oracle eBS received the receiving JSON file via integration system (like Oracle Intregation Cloud OIC).
- Next Oracle eBS to use PL/SQL procedure to extract the data from JSON and insert into Oracle temporary (staging) table.
- Finally, Oracle eBS api to be used to process the receiving data and create the PO receipt.
Insert Oracle Staging table from PL/SQL Record Type
PL/SQL procedure populate_stg_table to insert data into receiving staging table. The procedure to be called by the OIC intregation (insert_tables procedure).
Populate Staging Table based in PL/SQL REcord Type
PROCEDURE populate_stg_table(p_record_type IN VARCHAR2,
p_rcpt_hdr_rec IN xxpo_rcpt_hdr_stg%ROWTYPE,
p_rcpt_line_rec IN xxpo_rcpt_line_stg%ROWTYPE,
p_rcpt_srn_rec IN xxpo_rcpt_srn_stg%ROWTYPE,
x_identity OUT NUMBER
)
AS
l_rcpt_hdr_rec xxpo_ir_rcpt_hdr_stg%ROWTYPE := p_rcpt_hdr_rec;
l_rcpt_line_rec xxpo_ir_rcpt_line_stg%ROWTYPE := p_rcpt_line_rec;
l_rcpt_srn_rec xxpo_ir_rcpt_srn_stg%ROWTYPE := p_rcpt_srn_rec;
l_identity NUMBER := NULL;
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
g_procedure_name := 'populate_stg_table';
g_error_code := 'SUCCESS';
debug_msg('Start inserting into staging table populate_stg_table');
IF p_record_type = 'H'
THEN
INSERT
INTO xxpo_rcpt_hdr_stg
( message_id
, receipt_number
, warehouse
, order_type
, shipment_number
, inv_type
, receiver
, transaction_count
, record_status
, created_by
, creation_date
, last_updated_by
, last_update_date
, last_update_login
)
VALUES ( l_rcpt_hdr_rec.message_id
,l_rcpt_hdr_rec.receipt_number
,l_rcpt_hdr_rec.warehouse
,l_rcpt_hdr_rec.order_type
,l_rcpt_hdr_rec.shipment_number
,l_rcpt_hdr_rec.inv_type
,l_rcpt_hdr_rec.receiver
,l_rcpt_hdr_rec.transaction_count
,'NEW'
,l_rcpt_hdr_rec.created_by
,l_rcpt_hdr_rec.creation_date
,l_rcpt_hdr_rec.last_updated_by
,l_rcpt_hdr_rec.last_update_date
,l_rcpt_hdr_rec.last_update_login
) RETURNING HEADER_ID INTO l_identity;
ELSIF p_record_type = 'L'
THEN
INSERT
INTO xxpo_rcpt_line_stg
( header_id
, poir_number
, order_type
, order_line_number
, received_quantity
, item_number
, subinventory
, uom
, trx_type
, location_code
, transaction_date
, shipment_line_number
, receipt_action_type
, lot_expiry_date
, lot_number
, record_status
, created_by
, creation_date
, last_updated_by
, last_update_date
, last_update_login
)
VALUES ( l_rcpt_line_rec.header_id
,l_rcpt_line_rec.poir_number
,l_rcpt_line_rec.order_type
,l_rcpt_line_rec.order_line_number
,l_rcpt_line_rec.received_quantity
,l_rcpt_line_rec.item_number
,l_rcpt_line_rec.subinventory
,l_rcpt_line_rec.uom
,l_rcpt_line_rec.trx_type
,l_rcpt_line_rec.location_code
,l_rcpt_line_rec.transaction_date
,l_rcpt_line_rec.shipment_line_number
,l_rcpt_line_rec.receipt_action_type
,l_rcpt_line_rec.lot_expiry_date
,l_rcpt_line_rec.lot_number
,'NEW'
,l_rcpt_line_rec.created_by
,l_rcpt_line_rec.creation_date
,l_rcpt_line_rec.last_updated_by
,l_rcpt_line_rec.last_update_date
,l_rcpt_line_rec.last_update_login
) RETURNING LINE_ID INTO l_identity;
ELSIF p_record_type = 'S'
THEN
INSERT
INTO XXPO_RCPT_SRN_STG
( header_id
, line_id
, order_line_number
, shipment_line_number
, serial_number
, record_status
, created_by
, creation_date
, last_updated_by
, last_update_date
, last_update_login
)
VALUES ( l_rcpt_srn_rec.header_id
,l_rcpt_srn_rec.line_id
,l_rcpt_srn_rec.order_line_number
,l_rcpt_srn_rec.shipment_line_number
,l_rcpt_srn_rec.serial_number
,'NEW'
,l_rcpt_srn_rec.created_by
,l_rcpt_srn_rec.creation_date
,l_rcpt_srn_rec.last_updated_by
,l_rcpt_srn_rec.last_update_date
,l_rcpt_srn_rec.last_update_login
);
ELSE
DBMS_OUTPUT.PUT_LINE('Invalid Record Type');
END IF;
COMMIT;
g_error_code := g_error_code_s;
debug_msg('populate_staging completted');
x_identity := l_identity;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
DBMS_OUTPUT.PUT_LINE(SQLERRM)
END populate_stg_table;Insert Staging Table from JSON file
Oracle EBS gets JSON inbound data from external (3PL WMS) system through OIC. OIC intregation to call PL/SQL procedure(insert_tables) with JSON data to load the receiving data into EBS staging table. This procedure will read the json file and populate the PL/SQL record associated to the staging table. Then it will call populate_stg_table to insert the staging tables.
PL/SQL Insert Staging Table from JSON file
PROCEDURE insert_tables(p_req_hdr_rec IN xx_request_hdr_rec,
p_po_rcpt_hdr IN xxpo_rcpt_hdr_tbl,
x_transaction_id OUT NUMBER,
x_error_code OUT VARCHAR2,
x_error_msg OUT VARCHAR2,
x_req_hdr_rec OUT xx_request_hdr_rec,
x_po_rcpt_hdr OUT xxpo_rcpt_hdr_tbl
)
IS
l_req_hdr_rec xx_request_hdr_rec:= p_req_hdr_rec;
l_req_hdr_out xx_request_hdr_rec:= p_req_hdr_rec;
l_rcpt_hdr_tbl xxpo_rcpt_hdr_tbl := p_po_rcpt_hdr;
l_po_ir_rcpt_hdr xxpo_rcpt_hdr_tbl;
l_error_code VARCHAR2(10) := 'SUCCESS';
l_error_msg VARCHAR2(9999) := NULL;
l_header_id NUMBER;
l_line_id NUMBER;
l_txn_id NUMBER;
l_user_id NUMBER := 6543;
l_line_number NUMBER := -1;
l_shipment_number NUMBER := -1;
l_item VARCHAR2(40);
l_identity NUMBER := NULL;
l_serial_rec VARCHAR2(5) := 'NEW';
l_position NUMBER;
l_hdr_stg_rec xxpo_rcpt_hdr_stg%ROWTYPE;
l_line_stg_rec xxpo_rcpt_line_stg%ROWTYPE;
l_srn_stg_rec xxpo_rcpt_srn_stg%ROWTYPE;
BEGIN
g_error_code := 'SUCCESS';
g_message_id := l_req_hdr_rec.message_id;
debug_msg('Start populating insert_tables', TRUE);
populate_request_header(p_req_hdr_rec => l_req_hdr_rec,
x_transaction_id => l_txn_id,
x_error_code => l_error_code,
x_error_msg => l_error_msg
);
IF l_error_code = 'ERROR'
THEN
x_transaction_id := l_txn_id;
x_error_code := g_error_code_e;
x_error_msg := l_error_msg;
l_req_hdr_rec.error_msg := l_error_msg;
x_req_hdr_rec := l_req_hdr_out;
g_error_code := x_error_code;
debug_msg(l_error_msg);
RETURN;
END IF;
g_error_code := l_error_code;
debug_msg('After Request Header update');
FOR i in l_rcpt_hdr_tbl.FIRST .. l_rcpt_hdr_tbl.LAST
LOOP
--Populating Header Record:
BEGIN
l_hdr_stg_rec.message_id := l_req_hdr_rec.message_id;
l_hdr_stg_rec.receipt_number := l_rcpt_hdr_tbl(i).receipt_number;
l_hdr_stg_rec.warehouse := l_rcpt_hdr_tbl(i).warehouse;
l_hdr_stg_rec.order_type := l_rcpt_hdr_tbl(i).order_type;
l_hdr_stg_rec.shipment_number := l_rcpt_hdr_tbl(i).shipment_number;
l_hdr_stg_rec.inv_type := l_rcpt_hdr_tbl(i).inv_type;
l_hdr_stg_rec.receiver := l_rcpt_hdr_tbl(i).receiver;
l_hdr_stg_rec.transaction_count := l_rcpt_hdr_tbl(i).transaction_count;
l_hdr_stg_rec.record_status := g_error_code_n;
l_hdr_stg_rec.created_by := l_user_id;
l_hdr_stg_rec.creation_date := SYSDATE;
l_hdr_stg_rec.last_updated_by := l_user_id;
l_hdr_stg_rec.last_update_date := SYSDATE;
l_hdr_stg_rec.last_update_login := -1;
g_error_code := g_error_code_s;
debug_msg('Calling populate_stg_table for header');
populate_stg_table(p_record_type => 'H',
p_rcpt_hdr_rec => l_hdr_stg_rec,
x_identity => l_header_id
);
EXCEPTION
WHEN OTHERS THEN
l_error_msg := SQLERRM;
l_error_code := g_error_code_e;
dbms_output.put_line(l_error_msg);
debug_msg(l_error_msg);
END;
Part2: Populate Staging Table from JSON file
PL/SQL Insert Staging Table from JSON file
FOR j in l_rcpt_hdr_tbl(i).xxpo_rcpt_line_tbl.FIRST .. l_rcpt_hdr_tbl(i).xxpo_rcpt_line_tbl.LAST
LOOP
l_error_code := g_error_code_s;
l_error_msg := NULL;
l_item := l_rcpt_hdr_tbl(i).xxpo_rcpt_line_tbl(j).item_number;
l_position := INSTR(l_rcpt_hdr_tbl(i).xxpo_rcpt_line_tbl(j).order_line_number, '.');
IF l_position = 0
THEN
l_line_number:= l_rcpt_hdr_tbl(i).xxpo_rcpt_line_tbl(j).order_line_number;
l_shipment_number := NULL;
ELSE
l_line_number:= SUBSTR(l_rcpt_hdr_tbl(i).xxpo_rcpt_line_tbl(j).order_line_number, 1, l_position-1);
l_shipment_number := SUBSTR(l_rcpt_hdr_tbl(i).xxpo_rcpt_line_tbl(j).order_line_number, l_position +1);
END IF;
--
--Populating line Record:
BEGIN
l_line_stg_rec.header_id := l_header_id;
l_line_stg_rec.poir_number :=
l_rcpt_hdr_tbl(i).xxpo_rcpt_line_tbl(j).poir_number;
l_line_stg_rec.order_type := l_rcpt_hdr_tbl(i).order_type;
l_line_stg_rec.order_line_number := l_line_number;
l_line_stg_rec.received_quantity :=
l_rcpt_hdr_tbl(i).xxpo_rcpt_line_tbl(j).received_quantity;
l_line_stg_rec.item_number :=
l_rcpt_hdr_tbl(i).xxpo_rcpt_line_tbl(j).item_number;
l_line_stg_rec.subinventory :=
l_rcpt_hdr_tbl(i).xxpo_rcpt_line_tbl(j).subinventory;
l_line_stg_rec.uom :=
l_rcpt_hdr_tbl(i).xxpo_rcpt_line_tbl(j).uom;
l_line_stg_rec.trx_type :=
l_rcpt_hdr_tbl(i).xxpo_rcpt_line_tbl(j).trx_type;
l_line_stg_rec.location_code :=
l_rcpt_hdr_tbl(i).xxpo_rcpt_line_tbl(j).location_code;
l_line_stg_rec.transaction_date :=
l_rcpt_hdr_tbl(i).xxpo_rcpt_line_tbl(j).transaction_date;
l_line_stg_rec.shipment_line_number := l_shipment_number;
l_line_stg_rec.receipt_action_type :=
l_rcpt_hdr_tbl(i).xxpo_rcpt_line_tbl(j).receipt_action_type;
l_line_stg_rec.lot_number :=
l_rcpt_hdr_tbl(i).xxpo_rcpt_line_tbl(j).lot_number;
l_line_stg_rec.lot_expiry_date :=
l_rcpt_hdr_tbl(i).xxpo_rcpt_line_tbl(j).lot_expiry_date;
l_line_stg_rec.record_status := g_error_code_n;
l_line_stg_rec.created_by := l_user_id;
l_line_stg_rec.creation_date := SYSDATE;
l_line_stg_rec.last_updated_by := l_user_id;
l_line_stg_rec.last_update_date := SYSDATE;
l_line_stg_rec.last_update_login := l_user_id;
--Inserting Line Record
g_error_code := 'SUCCESS';
debug_msg('Calling populate_stg_table for line');
populate_stg_table(p_record_type => 'L',
p_rcpt_line_rec=> l_line_stg_rec,
x_identity => l_line_id
);
EXCEPTION
WHEN OTHERS THEN
l_error_msg := SQLERRM;
l_error_code := 'ERROR';
dbms_output.put_line(l_error_msg);
debug_msg(l_error_msg);
END;Part3: Insert Staging Table from JSON file
PL/SQL Insert Staging Table from JSON file
IF l_rcpt_hdr_tbl(i).xxpo_rcpt_line_tbl(j).receipt_action_type = g_rcpt_act_type_serial
THEN
FOR k in
l_rcpt_hdr_tbl(i).xxpo_rcpt_line_tbl(j).rcpt_serial_tbl.FIRST .. l_rcpt_hdr_tbl(i).xxpo_rcpt_line_tbl(j).rcpt_serial_tbl.LAST
LOOP
BEGIN
l_serial_rec := 'Y';
l_srn_stg_rec.header_id := l_header_id;
l_srn_stg_rec.line_id := l_line_id;
l_srn_stg_rec.order_line_number := l_line_number;
l_srn_stg_rec.shipment_line_number:= l_shipment_number;
l_srn_stg_rec.serial_number :=
l_rcpt_hdr_tbl(i).xxpo_rcpt_line_tbl(j).rcpt_serial_tbl(k).serial_number;
l_srn_stg_rec.record_status := 'NEW';
l_srn_stg_rec.created_by := l_user_id;
l_srn_stg_rec.creation_date := SYSDATE;
l_srn_stg_rec.last_updated_by := l_user_id;
l_srn_stg_rec.last_update_date := SYSDATE;
l_srn_stg_rec.last_update_login := l_user_id;
--Inserting Serial Record
g_error_code := 'SUCCESS';
debug_msg('Calling populate_stg_table for serial');
populate_stg_table(p_record_type => 'S',
p_rcpt_srn_rec => l_srn_stg_rec,
x_identity => l_identity
);
EXCEPTION
WHEN OTHERS THEN
l_error_msg := SQLERRM;
l_error_code := g_error_code_e;
END;
END LOOP;
END IF;
END LOOP;
END LOOP;
x_transaction_id := l_header_id;
x_error_msg := l_error_msg;
x_error_code := l_error_code;
g_error_code := g_error_code_s;
debug_msg('Procedure insert_tables is complete', true);
EXCEPTION
WHEN OTHERS THEN
x_transaction_id := NULL;
x_error_msg := SQLERRM ||'.
'||dbms_utility.format_error_backtrace
END insert_tables;Extracting Purchase Order JSON File from Oracle EBS
In Oracle EBS R12, you can pull PO details and save them as a JSON string in a CLOB column. You can create JSON format using Oracle function JSON_OBJECT, JSON_ARRAY and JSON_ARRAYAGG.
Purchase Order JSON File from EBS
DECLARE
l_json_file CLOB;
BEGIN
FOR po_rec IN (
SELECT poh.segment1 AS po_number,
poh.org_id,
poh.po_header_id
FROM po_headers_all poh
WHERE poh.last_update_date >= SYSDATE -2
AND poh.authorization_status='APPROVED'
AND org_id=132
) LOOP
SELECT JSON_OBJECT(
'RequestHeader' VALUE JSON_OBJECT(
'MessageId' VALUE '3PL'||TO_CHAR(SYSDATE, 'YYMMDDHH24MISS'),
'RequestType' VALUE 'PRCT',
'RequestDate' VALUE TO_CHAR(SYSDATE, 'YYYY-MM-DD"T"HH24:MI:SS')
),
'RequestBody' VALUE JSON_OBJECT(
'Receipt' VALUE JSON_ARRAY(
JSON_OBJECT(
'ReceiptHeader' VALUE JSON_OBJECT(
'ReceiptNumber' VALUE 'RCPT-' || po_rec.po_number,
'Warehouse' VALUE 'WSH',
'OrderType' VALUE 'PO',
'AsnNumber' VALUE '1005',
'TransactionNums' VALUE (
SELECT COUNT(*)
FROM po_lines_all
WHERE po_header_id = po_rec.po_header_id
AND org_id=po_rec.org_id
),
'ReceiptLines' VALUE (
SELECT JSON_ARRAYAGG(
JSON_OBJECT(
'PONumber' VALUE poh.segment1,
'OrderLineNumber' VALUE pol.line_num,
'ReceivedQuantity' VALUE pol.quantity_ordered,
'ItemNumber' VALUE msi.segment1,
'Subinventory' VALUE 'FG',
'UOM' VALUE pol.unit_meas_lookup_code,
'TrxType' VALUE 'RECEIPT',
'TransactionDate' VALUE TO_CHAR(SYSDATE, 'YYYY-MM-DD"T"HH24:MI:SS'),
'ReceiptActionType' VALUE 'Lot',
'LotNumber' VALUE 'LT' || TO_CHAR(SYSDATE, 'YYMMDD') || '-01',
'LotExpDate' VALUE NULL
) RETURNING CLOB
)
FROM po_headers_all poh,
po_lines_all pol,
po_distributions_all pod,
mtl_system_items_b msi
WHERE poh.po_header_id = pol.po_header_id
AND pol.po_line_id = pod.po_line_id
AND pol.item_id = msi.inventory_item_id
AND pod.destination_organization_id= msi.organization_id
AND poh.segment1 = po_rec.po_number
)
) RETURNING CLOB
)
) RETURNING CLOB
)
RETURNING CLOB
)
INTO l_json_file
FROM dual;
INSERT INTO xxpo_json_export (po_number, json_data)
VALUES (po_rec.po_number, l_json_file);
END LOOP;
END;Tips for Using JSON in EBS (R12)
Optimize Performance: Be careful when using string functions for JSON processing in R12.
Use Application Layer Parsing: It’s easier and faster to handle JSON in the application layer instead of the database.
Validate Data: Check JSON for errors before using it.
Handle Errors: Make sure your code can deal with broken or incomplete JSON.
Secure Data: Always use HTTPS when sending JSON.
Date Formats: Stick to ISO 8601 for date and time to avoid confusion.
Conclusion
JSON makes it easier to integrate Oracle EBS with other systems because it’s simple and works well with modern APIs. For older versions handling JSON might be tricky, but using the application layer instead of the database can make things smoother. Using JSON wisely in both inbound and outbound interfaces can improve data exchange and system flexibility.
