About the Warehouse Open Lot Inbound Interface

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
  
  • Loading data into the database. The 'eagle_default/in/xml/xml-dbdirect_invar.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 to 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

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.

Additional information about Rollups