Item Regulatory Interface

Procedure: Item Regulatory Interface

Objective

The purpose of this document is to outline the process for loading and updating Item Regulatory Information into Oracle using an API, based on data loaded into a staging table.

Scenario

Regulatory information needs to be associated or updated for a list of items in Oracle Inventory. This includes region-specific regulatory codes, compliance data, or classification as per local regulations (e.g., REACH, RoHS, FDA, etc.).

This procedure allows bulk updates through a staging table and an automated PL/SQL process using standard Oracle Inventory APIs or custom API wrappers.

Sample Data to be Prepared and Loaded

ITEM_NUMBER ORG_CODE REGULATORY_CODE REGULATORY_TYPE START_DATE END_DATE STATUS
ABC1 USA ROHS ENVIRONMENTAL 01-JAN-2022 31-DEC-2025 ACTIVE
ABC1 USA FDA SAFETY 01-FEB-2022 ACTIVE
DCG2 USA REACH ENVIRONMENTAL 01-MAR-2022 31-DEC-2024 INACTIVE

This data is to be loaded into a staging table (e.g., ITEM_REGULATORY_STG).

Steps to Execute

  1. Compile the Database Objects

Ensure the following are compiled and valid:

  • Staging Table: ITEM_REGULATORY_STG
  • Main Procedure: LOAD_ITEM_REGULATORY_PROC
  • Supporting functions/packages: (e.g., validation, logging, error handling)
  1. Load Data into Staging Table

Use SQL*Loader, an external file, or manual SQL to load the regulatory data into the staging table.

INSERT INTO ITEM_REGULATORY_STG (

ITEM_NUMBER,

ORG_CODE,

REGULATORY_CODE,

REGULATORY_TYPE,

START_DATE,

END_DATE,

STATUS

) VALUES (

‘ABC1’, ‘USA’, ‘ROHS’, ‘ENVIRONMENTAL’, TO_DATE(’01-JAN-2022′, ‘DD-MON-YYYY’), TO_DATE(’31-DEC-2025′, ‘DD-MON-YYYY’), ‘ACTIVE’

);

 

COMMIT;

Repeat for all records.

  1. Run the Main Procedure

Execute the PL/SQL procedure that reads from the staging table and updates the regulatory information into Oracle Inventory.

BEGIN

LOAD_ITEM_REGULATORY_PROC;

END;

/

This procedure should:

  • Validate the item and organization exist
  • Validate the regulatory code and type
  • Insert/update records into relevant Oracle base tables (e.g., MTL_ITEM_REGULATORY_INFO)
  • Log success/failure messages

Optional Enhancements

  • Add validation flags or status columns in the staging table
  • Include logging mechanism for audit purposes
  • Integrate this process into an automated scheduled job or concurrent request
  • Add notification mechanism for success/failure reports

Notes

  • Ensure proper mapping of REGULATORY_CODE and REGULATORY_TYPE based on your business rules.
  • Handle overlapping date ranges or duplicate entries based on your validation rules.
  • End-dating of regulatory data must be handled cautiously to avoid compliance gaps.

Would you like me to help you create the staging table DDL or a sample PL/SQL

✅ Item Regulatory Interface – Procedure Document

  1. Objective

To process and load item regulatory properties from a spreadsheet into Oracle using the public API GR_ITEM_PROPERTIES_PUB.ITEM_PROPERTIES.

  1. Input File Format (CSV / Excel)
Attribute Name Data Type Length Mandatory Description
Action String 1 Y I = Insert, U = Update, D = Delete
Organization Code String 3 Y Org Code (e.g., USA). Will be converted to organization_id.
Item Number String 40 Y Item Number
Field Name String 5 Y Field_Name_code – field to be updated
Property Id String 6 Y Property_id – regulatory property identifier
Numeric Value Number 15,9 N One of the 4 value fields depending on Property
Alpha Value String 240 N
Phrase Code String 15 N
Date Value Date DD/MM/YYYY N
  1. Staging Table: ITEM_REGULATORY_INTERFACE_STG

CREATE TABLE ITEM_REGULATORY_INTERFACE_STG (

ACTION                VARCHAR2(1)    NOT NULL,

ORG_CODE              VARCHAR2(3)    NOT NULL,

ITEM_NUMBER           VARCHAR2(40)   NOT NULL,

FIELD_NAME_CODE       VARCHAR2(5)    NOT NULL,

PROPERTY_ID           VARCHAR2(6)    NOT NULL,

NUMERIC_VALUE         NUMBER(15,9),

ALPHA_VALUE           VARCHAR2(240),

PHRASE_CODE           VARCHAR2(15),

DATE_VALUE            DATE,

LOAD_STATUS           VARCHAR2(10),

ERROR_MESSAGE         VARCHAR2(4000),

LOAD_DATE             DATE DEFAULT SYSDATE

);

  1. PL/SQL Wrapper Procedure Logic (LOAD_ITEM_REGULATORY_PROC)

✔ Steps the Procedure Should Perform:

  1. Loop through each record in the staging table where LOAD_STATUS is NULL.
  2. Resolve organization_id using ORG_CODE.
  3. Construct the p_item_properties_tab parameter required by the API.
  4. Call GR_ITEM_PROPERTIES_PUB.ITEM_PROPERTIES.
  5. Update each record in the staging table with:
    • Success/Fail
    • Error message (if any)

🧩 Sample Pseudocode:

DECLARE

CURSOR c_items IS

SELECT * FROM ITEM_REGULATORY_INTERFACE_STG

WHERE LOAD_STATUS IS NULL;

 

l_item_properties_tab GR_ITEM_PROPERTIES_PUB.ITEM_PROPERTIES_TAB_TYPE;

l_return_status       VARCHAR2(1);

l_msg_count           NUMBER;

l_msg_data            VARCHAR2(4000);

l_org_id              NUMBER;

i                     PLS_INTEGER := 1;

BEGIN

FOR rec IN c_items LOOP

BEGIN

— Get org_id from org_code

SELECT organization_id INTO l_org_id

FROM org_organization_definitions

WHERE organization_code = rec.org_code;

 

— Build item property record

l_item_properties_tab(i).action_code     := rec.action;

l_item_properties_tab(i).organization_id := l_org_id;

l_item_properties_tab(i).item_number     := rec.item_number;

l_item_properties_tab(i).field_name_code := rec.field_name_code;

l_item_properties_tab(i).property_id     := rec.property_id;

l_item_properties_tab(i).numeric_value   := rec.numeric_value;

l_item_properties_tab(i).alpha_value     := rec.alpha_value;

l_item_properties_tab(i).phrase_code     := rec.phrase_code;

l_item_properties_tab(i).date_value      := rec.date_value;

 

— Call API

GR_ITEM_PROPERTIES_PUB.ITEM_PROPERTIES(

p_item_properties_tab => l_item_properties_tab,

x_return_status       => l_return_status,

x_msg_count           => l_msg_count,

x_msg_data            => l_msg_data

);

 

— Log success/failure

UPDATE ITEM_REGULATORY_INTERFACE_STG

SET LOAD_STATUS = CASE

WHEN l_return_status = ‘S’ THEN ‘SUCCESS’

ELSE ‘FAIL’

END,

ERROR_MESSAGE = l_msg_data

WHERE ROWID = rec.ROWID;

 

EXCEPTION

WHEN OTHERS THEN

UPDATE ITEM_REGULATORY_INTERFACE_STG

SET LOAD_STATUS = ‘FAIL’,

ERROR_MESSAGE = SQLERRM

WHERE ROWID = rec.ROWID;

END;

END LOOP;

COMMIT;

END;

  1. Output: Comma-Delimited Report

After the procedure is run, generate a CSV report from the staging table:

Action Org Code Item Number Field Name Property Id Value Used Status Error Message
I USA ABC1 ROHS 101001 1.00 SUCCESS
U USA ABC1 FDA 102002 2024-01-01 FAIL Invalid date format
D USA DCG2 REACH 103003 NULL SUCCESS

✅ Execution Steps

  1. Load spreadsheet data into ITEM_REGULATORY_INTERFACE_STG (using SQL*Loader or script).
  2. Run the wrapper procedure:

BEGIN

LOAD_ITEM_REGULATORY_PROC;

END;

/

  1. Generate a report from the staging table.
  2. Review failures, fix data if needed, and rerun.

 

Recent Posts