Applying and Releasing Sales Order Holds Based on Customer Item Cross-Reference Commodity Code
In Oracle Order Management, business rules often require dynamic control over Sales Order processing. In this case, the customer wanted to automatically apply and release Sales Order holds based on the Commodity Code maintained in the Customer Item Cross-Reference.
This blog explains how we implemented a fully automated hold management solution using standard Oracle APIs, without manual intervention.
Business Requirement
The customer’s requirement was straightforward but critical:
- Apply a Sales Order hold when an item is associated with a specific commodity code (for example: CUSTOMER CHOICE) in the Customer Item Cross-Reference
- Release the hold automatically when the commodity condition no longer exists
- Ensure the logic works for booked and open orders
- Avoid manual hold application or release by users
- Maintain auditability and Oracle support compliance
Key Design Considerations
- Use standard Oracle Order Management APIs
- Apply hold at the Order Header level
- Prevent duplicate holds
- Release holds only when no qualifying lines remain
- Maintain a tracking table for reporting and audit
Solution Overview
The solution was implemented as a concurrent program that performs two main actions:
- Apply Hold – for orders that meet the commodity condition
- Release Hold – for orders where the condition no longer applies
The logic is driven by the Customer Item Cross-Reference commodity code.
High-Level Process Flow
Apply Hold Flow
- Identify open Sales Orders
- Validate Customer Item Cross-Reference commodity code
- Check whether the hold already exists
- Apply the hold using OE_HOLDS_PUB.APPLY_HOLDS
- Update tracking table and flag processed order lines
Release Hold Flow
- Identify orders with the specific hold applied
- Re-validate Customer Item Cross-Reference
- If no qualifying items exist, release the hold
- Release hold using OE_HOLDS_PUB.RELEASE_HOLDS
- Update tracking table and clear processing flags
Technical Implementation Details
Application Context Initialization
The program initializes the Oracle EBS context using profile values:
- User ID
- Responsibility ID
- Application ID
This ensures all API calls run with proper authorization.
Applying Sales Order Hold
Selection Logic
Orders are selected when:
- Order lines are in ENTERED / BOOKED / AWAITING statuses
- Customer Item Cross-Reference exists
- Commodity Code = CUSTOMER CHOICE
- Order creation date ≥ Customer Item Cross-Reference update date
- Hold is not already applied
Hold Application API
OE_HOLDS_PUB.APPLY_HOLDS
Key Inputs
- Order Header ID
- Hold Definition ID
- Validation Level = FULL
- Hold Comment for audit clarity
Post-Processing
- Insert audit records into a custom hold tracking table
- Update OE_ORDER_LINES_ALL.INDUSTRY_ATTRIBUTE29 as a processing flag
- Commit transaction only on success
Releasing Sales Order Hold
Release Validation Logic
Before releasing a hold, the program checks:
- Whether any order lines still meet the commodity condition
- Only releases the hold when no qualifying items exist
Hold Release API
OE_HOLDS_PUB.RELEASE_HOLDS
Key Inputs
- Order Header ID
- Hold ID
- Release Reason Code
- Release Comment
Post-Release Actions
- Insert release record into audit table
- Clear processing flags on order lines
- Commit transaction on success
Sample PL/SQL Code
Below is the complete PL/SQL program used to apply and release Sales Order holds based on Customer Item Cross-Reference commodity code:
/* Formatted on 2026/01/30 19:22 (Formatter Plus v4.8.8) */
DECLARE
g_conc_program_id NUMBER := fnd_profile.VALUE (‘CONC_REQUEST_ID’);
g_resp_id NUMBER := fnd_profile.VALUE (‘RESP_ID’);
g_appl_id NUMBER := fnd_profile.VALUE (‘RESP_APPL_ID’);
g_user_id NUMBER := fnd_profile.VALUE (‘USER_ID’);
l_upp_hold_id NUMBER;
l_hold_found NUMBER;
l_order_tbl oe_holds_pvt.order_tbl_type;
l_return_status VARCHAR2 (5);
l_msg_count NUMBER;
l_msg_data VARCHAR2 (2000);
l_msg_index NUMBER;
l_upp_count NUMBER;
CURSOR c1
IS
SELECT DISTINCT hca.account_number customer_number,
hp.party_name customer_name, ood.organization_code,
ooh.order_number, ooh.header_id
FROM oe_order_headers_all ooh,
oe_order_lines_all ool,
mtl_customer_item_xrefs_v mcix,
mtl_customer_items_all_v mciav,
hz_cust_accounts hca,
hz_parties hp,
org_organization_definitions ood,
mtl_system_items_b msib
WHERE ooh.header_id = ool.header_id
AND ool.flow_status_code IN
(‘AWAITING_SHIPPING’, ‘AWAITING_RECEIPT’, ‘BOOKED’,
‘ENTERED’)
AND mcix.customer_id = ool.sold_to_org_id
AND mcix.inventory_item_id = ool.inventory_item_id
AND industry_attribute29 IS NULL
AND mcix.customer_item_id = mciav.customer_item_id
AND mcix.customer_id = mciav.customer_id
AND mciav.commodity_code = ‘CUSTOMER CHOICE’
AND hca.cust_account_id = ool.sold_to_org_id
AND hca.party_id = hp.party_id
AND ooh.ship_from_org_id = ood.organization_id
AND ool.inventory_item_id = msib.inventory_item_id
AND ool.ship_from_org_id = msib.organization_id
AND TRUNC (ooh.creation_date) >=
TRUNC (mciav.last_update_date)
GROUP BY hca.account_number,
hp.party_name,
ood.organization_code,
ooh.order_number,
ooh.header_id;
CURSOR c2
IS
SELECT DISTINCT hca.account_number customer_number,
hp.party_name customer_name, ood.organization_code,
ooh.order_number, ooh.header_id, hs.hold_id
FROM oe_order_headers_all ooh,
hz_cust_accounts hca,
hz_parties hp,
org_organization_definitions ood,
oe_order_holds_all oh,
oe_hold_sources_all hs,
oe_hold_definitions h
WHERE 1 = 1
AND hca.cust_account_id = ooh.sold_to_org_id
AND hca.party_id = hp.party_id
AND ooh.ship_from_org_id = ood.organization_id
AND ooh.header_id = oh.header_id
AND oh.hold_release_id IS NULL
AND oh.hold_source_id = hs.hold_source_id
AND hs.hold_id = h.hold_id
AND h.type_code = ‘HOLD’
AND h.NAME = ‘Hold Name’
AND oh.released_flag = ‘N’
AND EXISTS (SELECT 1
FROM xxxx_sales_order_hold_tbl temp
WHERE temp.header_id = ooh.header_id)
AND EXISTS (
SELECT 1
FROM oe_order_lines_all ool
WHERE ool.header_id = ooh.header_id
AND ool.flow_status_code IN
(‘AWAITING_SHIPPING’, ‘AWAITING_RECEIPT’,
‘BOOKED’, ‘ENTERED’));
BEGIN
fnd_file.put_line
(fnd_file.LOG,
‘******************************************************************************************************************************************************’
);
fnd_file.put_line (fnd_file.LOG,
‘Program Apply Started ‘
|| TO_CHAR (SYSDATE, ‘DD-MM-YYYY HH24:MI:SS’)
);
BEGIN
SELECT hold_id
INTO l_cust_hold_id
FROM oe_hold_definitions
WHERE NAME = ‘Hold Name’
AND (end_date_active IS NULL OR end_date_active >= TRUNC (SYSDATE));
EXCEPTION
WHEN OTHERS
THEN
l_cust_hold_id := NULL;
END;
fnd_file.put_line (fnd_file.LOG,
‘Hold ID-‘
|| l_cust_hold_id
|| ‘-‘
|| TO_CHAR (SYSDATE, ‘DD-MM-YYYY HH24:MI:SS’)
);
BEGIN
fnd_global.apps_initialize (g_user_id, g_resp_id, g_appl_id);
END;
FOR rec_apply_hold IN c1
LOOP
fnd_file.put_line (fnd_file.LOG,
‘Customer Number#’ || rec_apply_hold.customer_number
);
fnd_file.put_line (fnd_file.LOG,
‘Customer Name#’ || rec_apply_hold.customer_name
);
fnd_file.put_line (fnd_file.LOG,
‘Order Number#’ || rec_apply_hold.order_number
);
fnd_file.put_line (fnd_file.LOG,
‘Header ID#’ || rec_apply_hold.header_id
);
–Check if hold exists on the order line.
BEGIN
SELECT COUNT (*)
INTO l_hold_found
FROM oe_order_holds_all oh,
oe_hold_sources_all hs,
oe_hold_definitions h
WHERE oh.header_id = rec_apply_hold.header_id
AND oh.hold_release_id IS NULL
AND oh.hold_source_id = hs.hold_source_id
AND hs.hold_id = h.hold_id
AND h.type_code = ‘HOLD’
AND h.NAME = ‘Hold Name’
AND oh.released_flag = ‘N’;
END;
fnd_file.put_line (fnd_file.LOG,
‘Hold Already Exists Count’ || l_hold_found
);
IF l_hold_found = 0
THEN
fnd_file.put_line (fnd_file.LOG,
‘Program GoinG to Apply Hold API Part’
|| l_hold_found
);
l_order_tbl (1).header_id := rec_apply_hold.header_id;
oe_holds_pub.apply_holds
(p_api_version => 1.0,
p_init_msg_list => fnd_api.g_false,
p_commit => fnd_api.g_false,
p_validation_level => fnd_api.g_valid_level_full,
p_order_tbl => l_order_tbl,
p_hold_id => l_cust_hold_id,
p_hold_until_date => NULL,
p_hold_comment => ‘Program Automation Applied -Hold’,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data
);
fnd_file.put_line (fnd_file.LOG,
‘API Return Status’ || l_return_status
);
IF l_return_status = fnd_api.g_ret_sts_success
THEN
fnd_file.put_line (fnd_file.LOG,
‘Hold Applied Sucessfully -‘ || l_return_status
);
BEGIN
INSERT INTO xxxx_sales_order_hold_tbl
(customer_number,
customer_name,
organization_code,
order_number,
header_id, request_id,
created_by, created_date, last_updated_by,
last_upadted_date, hold_name, hold_id,
rec_type
)
VALUES (rec_apply_hold.customer_number,
rec_apply_hold.customer_name,
rec_apply_hold.organization_code,
rec_apply_hold.order_number,
rec_apply_hold.header_id, g_conc_program_id,
g_user_id, SYSDATE, g_user_id,
SYSDATE, ‘Hold Name’, l_cust_hold_id,
‘Apply Hold’
);
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line
(fnd_file.LOG,
‘Error In Insert – XXXX_SALES_ORDER_HOLD_TBL-‘
|| SQLERRM
);
END;
BEGIN
UPDATE oe_order_lines_all
SET industry_attribute29 = ‘Y’
WHERE 1 = 1 AND header_id = rec_apply_hold.header_id;
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line
(fnd_file.LOG,
‘Error In Update -OE_ORDER_LINES_ALL- INDUSTRY_ATTRIBUTE29’
);
END;
COMMIT;
ELSE
IF l_msg_count > 0
THEN
FOR x_index IN 1 .. l_msg_count
LOOP
oe_msg_pub.get (p_msg_index => x_index,
p_encoded => fnd_api.g_false,
p_data => l_msg_data,
p_msg_index_out => l_msg_index
);
fnd_file.put_line
(fnd_file.LOG,
‘Error in applying holds for UPP Policy Hold…’
|| l_msg_data
);
END LOOP;
END IF;
END IF;
END IF;
END LOOP;
fnd_file.put_line (fnd_file.LOG,
‘Program Apply Ended ‘
|| TO_CHAR (SYSDATE, ‘DD-MM-YYYY HH24:MI:SS’)
);
fnd_file.put_line
(fnd_file.LOG,
‘******************************************************************************************************************************************************’
);
fnd_file.put_line
(fnd_file.LOG,
‘~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~’
);
fnd_file.put_line (fnd_file.LOG,
‘Program Release Started ‘
|| TO_CHAR (SYSDATE, ‘DD-MM-YYYY HH24:MI:SS’)
);
FOR rec_release_hold IN c2
LOOP
fnd_file.put_line (fnd_file.LOG,
‘Customer Number#’
|| rec_release_hold.customer_number
);
fnd_file.put_line (fnd_file.LOG,
‘Customer Name#’ || rec_release_hold.customer_name
);
fnd_file.put_line (fnd_file.LOG,
‘Order Number#’ || rec_release_hold.order_number
);
fnd_file.put_line (fnd_file.LOG,
‘Header ID#’ || rec_release_hold.header_id
);
fnd_file.put_line (fnd_file.LOG, ‘Hold ID#’ || rec_release_hold.hold_id);
BEGIN
SELECT COUNT (*)
INTO l_upp_count
FROM oe_order_headers_all ooh,
oe_order_lines_all ool,
mtl_customer_item_xrefs_v mcix,
mtl_customer_items_all_v mciav,
hz_cust_accounts hca,
hz_parties hp,
org_organization_definitions ood,
mtl_system_items_b msib
WHERE ooh.header_id = ool.header_id
AND ool.flow_status_code IN
(‘AWAITING_SHIPPING’, ‘AWAITING_RECEIPT’, ‘BOOKED’,
‘ENTERED’)
AND mcix.customer_id = ool.sold_to_org_id
AND mcix.inventory_item_id = ool.inventory_item_id
AND mcix.customer_item_id = mciav.customer_item_id
AND mcix.customer_id = mciav.customer_id
AND mciav.commodity_code = ‘CUSTOMER CHOICE’
AND hca.cust_account_id = ool.sold_to_org_id
AND hca.party_id = hp.party_id
AND ooh.ship_from_org_id = ood.organization_id
AND ool.inventory_item_id = msib.inventory_item_id
AND ool.ship_from_org_id = msib.organization_id
AND ooh.header_id = rec_release_hold.header_id;
END;
fnd_file.put_line (fnd_file.LOG,
‘Customer Item Cross Ref Validation Count ‘
|| l_upp_count
);
IF l_upp_count = 0
THEN
fnd_file.put_line (fnd_file.LOG,
‘Program Going to Relase Hold API Part ‘
);
BEGIN
l_order_tbl (1).header_id := rec_release_hold.header_id;
oe_holds_pub.release_holds
(p_api_version => 1.0,
p_order_tbl => l_order_tbl,
p_hold_id => rec_release_hold.hold_id,
p_release_reason_code => ‘HOLD RELEASE AUTOMATION’,
p_release_comment => ‘Released Through Release Hold API Call’,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data
);
fnd_file.put_line (fnd_file.LOG,
‘Hold Release API Return Status’
|| l_return_status
);
IF l_return_status = fnd_api.g_ret_sts_success
THEN
fnd_file.put_line (fnd_file.LOG,
‘Hold Released Sucessfully -‘
|| l_return_status
);
BEGIN
INSERT INTO xxxx_sales_order_hold_tbl
(customer_number,
customer_name,
organization_code,
order_number,
header_id,
request_id, created_by, created_date,
last_updated_by, last_upadted_date,
hold_name, hold_id,
rec_type
)
VALUES (rec_release_hold.customer_number,
rec_release_hold.customer_name,
rec_release_hold.organization_code,
rec_release_hold.order_number,
rec_release_hold.header_id,
g_conc_program_id, g_user_id, SYSDATE,
g_user_id, SYSDATE,
‘Hold Name’, rec_release_hold.hold_id,
‘Release Hold’
);
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line
(fnd_file.LOG,
‘Error In Insert – XXXX_SALES_ORDER_HOLD_TBL’
);
END;
BEGIN
UPDATE oe_order_lines_all
SET industry_attribute29 = NULL
WHERE 1 = 1 AND header_id = rec_release_hold.header_id;
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line
(fnd_file.LOG,
‘Error In Update -OE_ORDER_LINES_ALL- INDUSTRY_ATTRIBUTE29’
);
END;
COMMIT;
ELSE
IF l_msg_count > 0
THEN
FOR x_index IN 1 .. l_msg_count
LOOP
oe_msg_pub.get (p_msg_index => x_index,
p_encoded => fnd_api.g_false,
p_data => l_msg_data,
p_msg_index_out => l_msg_index
);
fnd_file.put_line
(fnd_file.LOG,
‘Error in applying holds for UPP Policy Hold…’
|| l_msg_data
);
END LOOP;
END IF;
END IF;
END;
ELSE
fnd_file.put_line (fnd_file.LOG,
‘No Hold Release Found ‘
|| TO_CHAR (SYSDATE, ‘DD-MM-YYYY HH24:MI:SS’)
);
END IF;
END LOOP;
fnd_file.put_line (fnd_file.LOG,
‘Program Release Ended ‘
|| TO_CHAR (SYSDATE, ‘DD-MM-YYYY HH24:MI:SS’)
);
fnd_file.put_line
(fnd_file.LOG,
‘~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~’
);
END;
Benefits of This Approach
- Fully automated hold management
- No manual user intervention
- Uses Oracle-supported APIs
- Prevents duplicate holds
- Clear audit trail
- Easy to extend for new commodity codes
Common Use Cases
- Regulatory or compliance-driven order control
- Customer-specific product restrictions
- Conditional order fulfillment logic
Conclusion
By leveraging Customer Item Cross-Reference data and standard Oracle Order Management APIs, we successfully implemented a robust and scalable solution to apply and release Sales Order holds automatically.
This approach ensures:
- Data integrity
- Business rule enforcement
- Minimal operational overhead
If your organization manages complex customer-specific rules, this model provides a clean, supportable, and reusable framework.
