Overview
The Warehouse Open Lot inbound interface allows loading and updating data in the following database tables:
- HOLDINGDBO.LOT_LEVEL_POSITION
- HOLDINGDBO.POSITION_COST_LOT
- HOLDINGDBO.POSITION_COST_DETAIL (roll up the values)
- HOLDINGDBO.POSITION_DETAIL (roll up the values)
- HOLDINGDBO.POSITION (roll up the values)
Relationship between DB tables LOT_LEVEL_POSITION and POSITION_COST_LOT (1:1):
HOLDINGDBO.POSITION_COST_LOT.POSITION_LOT_ID = HOLDINGDBO.LOT_LEVEL_POSITION.LOT_LEVEL_POSITION
Relationship between DB tables POSITION_COST_DETAIL and POSITION_COST_LOT (1:N – roll up):
HOLDINGDBO.POSITION_COST_DETAIL.POSITION_ID = HOLDINGDBO.POSITION_COST_LOT.POSITION_ID
Relationship between DB tables POSITION_DETAIL and POSITION_COST_LOT (1:N – roll up):
HOLDINGDBO.POSITION_DETAIL.POSITION_ID = HOLDINGDBO.POSITION_COST_LOT.POSITION_ID
Relationship between DB tables POSITION and POSITION_COST_LOT (1:N – roll up):
HOLDINGDBO.POSITION.POSITION_ID = HOLDINGDBO.POSITION_COST_LOT.POSITION_ID
Incoming message format: EagleML (WarehouseOpenLot complex type).
Stream Logic
EagleML Processing Sequence
Warehouse Open Lot is loaded into DB via eagle_ml-2-0_default_in_xml_warehouse_preproc in two steps:
- XSLT transformation. XSLT rule 'eagle_default/in/xml/xslt/wrh_egl_gen_interface3.xsl' is used.
- Loading data into the database. The 'eagle_default/in/xml/xml-warehouse_v2.xml' rule is used.
To load or update data in Warehouse Open Lot database tables send an EagleML message into the eagle_ml-2-0_default_in_xml_warehouse_preproc stream.
See an example of incoming EagleML message
Refer to the data Map Table for Warehouse Open Lot object
CSV Processing Sequence
Warehouse Open Lot is loaded to DB via the following streams:
- eagle_default_in_csv_warehouse - this is PRF stream which consist of two steps transform and load
- eagle_default_in_csv_warehouse_t - transformation CSV to XMLDBAPI request
- csv-wrhs_open_lot_xmldbapi.xml rule
- eagle_ml-2-0_default_in_xml_warehouse_preproc
- XSLT transformation. XSLT rule 'eagle_default/in/xml/xslt/wrh_egl_gen_interface3.xsl' is used.
- Loading data into the database. The 'eagle_default/in/xml/xml-warehouse_v2.xml' rule is used
To load or update data in Warehouse Open Lot tables, send your CSV message to the eagle_default_in_csv_warehouse stream.
Required Elements
The following 5 elements are required in the EagleML message:
- Effective Date: warehouseTransaction/warehouseOpenLot/effectiveDate. Mapped to HOLDINGDBO.LOT_LEVEL_POSITION.EFFECTIVE_DATE
- Entity Id: warehouseTransaction/warehouseOpenLot/entityId (or warehouseTransaction/warehouseOpenLot/xrefAccountId and warehouseTransaction/warehouseOpenLot/xrefAccountIdType). Mapped to HOLDINGDBO.POSITION.ENTITY_ID
- Source Name: warehouseTransaction/warehouseOpenLot/sourceName
- Lot Number: warehouseTransaction/warehouseOpenLot/lotNumber – Mapped to HOLDINGDBO.LOT_LEVEL_POSITION.LOT_NUMBER
- Security Identifiers for resolve the Security Alias (see the section 'Security Resolution Logic'), Security Alias mapped to HOLDINGDBO.LOT_LEVEL_POSITION.SECURITY_ALIAS
Security Resolution Logic
<securityAlias> should not be specified in the incoming EagleML message. Refer to Security Resolution for Warehouse Objects
Entity Resolution Logic
Refer to Entity Resolution for Warehouse Objects
Modes of Data Loading
The Warehouse Open Lot inbound interface has 3 modes for loading data into the DB table
Batch Mode
Single Row Upsert Mode
Roll Up
Note
Roll Up summs up some numeric values from Warehouse Open Lot DB tables and inserts the result values into the fields of the following Position DB tables: Position Cost Detail, Position Detail and Position.
After committing the data into the database through any of 3 modes, one of the rules for Warehouse Open Lot data loading calls a stored procedure to roll up the values in the HOLDINGDBO.LOT_LEVEL_POSITION DB table into the HOLDINGDBO.POSITION_DETAIL, HOLDINGDBO.POSITION_COST_DETAIL and HOLDINGDBO.POSITION DB tables.