Product/Inventory Import


#21

I have tried a number of times and am not sure if it is something with my xml export file or not but I keep getting errors. Looks like it is expecting other data.

The Following Errors Occurred:

1. ERROR: parsing file: ERROR parsing Entity Xml file: org.xml.sax.SAXException: A transaction error occurred reading data
org.xml.sax.SAXException: A transaction error occurred reading data
org.xml.sax.SAXException: Error storing value
org.ofbiz.entity.GenericEntityException: org.ofbiz.entity.GenericEntityException: org.ofbiz.entity.GenericEntityException: Error while inserting: [GenericEntity:ProductFacility][productId,24050-EA(java.lang.String)][facilityId,ScipioShopWarehouse(java.lang.String)][minimumStock,2(java.math.BigDecimal)][reorderQuantity,10(java.math.BigDecimal)][daysToShip,2(java.lang.Long)][lastInventoryCount,null()][lastUpdatedStamp,2018-11-17 02:12:48.936(java.sql.Timestamp)][lastUpdatedTxStamp,2018-11-17 02:12:47.872(java.sql.Timestamp)][createdStamp,2018-11-17 02:12:48.936(java.sql.Timestamp)][createdTxStamp,2018-11-17 02:12:47.872(java.sql.Timestamp)] (SQL Exception while executing the following:INSERT INTO OFBIZ.PRODUCT_FACILITY (PRODUCT_ID, FACILITY_ID, MINIMUM_STOCK, REORDER_QUANTITY, DAYS_TO_SHIP, LAST_INVENTORY_COUNT, LAST_UPDATED_STAMP, LAST_UPDATED_TX_STAMP, CREATED_STAMP, CREATED_TX_STAMP) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?) (INSERT on table 'PRODUCT_FACILITY' caused a violation of foreign key constraint 'PROD_FAC_FAC' for key (ScipioShopWarehouse). The statement has been rolled back.)) (Error while inserting: [GenericEntity:ProductFacility][productId,24050-EA(java.lang.String)][facilityId,ScipioShopWarehouse(java.lang.String)][minimumStock,2(java.math.BigDecimal)][reorderQuantity,10(java.math.BigDecimal)][daysToShip,2(java.lang.Long)][lastInventoryCount,null()][lastUpdatedStamp,2018-11-17 02:12:48.936(java.sql.Timestamp)][lastUpdatedTxStamp,2018-11-17 02:12:47.872(java.sql.Timestamp)][createdStamp,2018-11-17 02:12:48.936(java.sql.Timestamp)][createdTxStamp,2018-11-17 02:12:47.872(java.sql.Timestamp)] (SQL Exception while executing the following:INSERT INTO OFBIZ.PRODUCT_FACILITY (PRODUCT_ID, FACILITY_ID, MINIMUM_STOCK, REORDER_QUANTITY, DAYS_TO_SHIP, LAST_INVENTORY_COUNT, LAST_UPDATED_STAMP, LAST_UPDATED_TX_STAMP, CREATED_STAMP, CREATED_TX_STAMP) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?) (INSERT on table 'PRODUCT_FACILITY' caused a violation of foreign key constraint 'PROD_FAC_FAC' for key (ScipioShopWarehouse). The statement has been rolled back.))) (org.ofbiz.entity.GenericEntityException: Error while inserting: [GenericEntity:ProductFacility][productId,24050-EA(java.lang.String)][facilityId,ScipioShopWarehouse(java.lang.String)][minimumStock,2(java.math.BigDecimal)][reorderQuantity,10(java.math.BigDecimal)][daysToShip,2(java.lang.Long)][lastInventoryCount,null()][lastUpdatedStamp,2018-11-17 02:12:48.936(java.sql.Timestamp)][lastUpdatedTxStamp,2018-11-17 02:12:47.872(java.sql.Timestamp)][createdStamp,2018-11-17 02:12:48.936(java.sql.Timestamp)][createdTxStamp,2018-11-17 02:12:47.872(java.sql.Timestamp)] (SQL Exception while executing the following:INSERT INTO OFBIZ.PRODUCT_FACILITY (PRODUCT_ID, FACILITY_ID, MINIMUM_STOCK, REORDER_QUANTITY, DAYS_TO_SHIP, LAST_INVENTORY_COUNT, LAST_UPDATED_STAMP, LAST_UPDATED_TX_STAMP, CREATED_STAMP, CREATED_TX_STAMP) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?) (INSERT on table 'PRODUCT_FACILITY' caused a violation of foreign key constraint 'PROD_FAC_FAC' for key (ScipioShopWarehouse). The statement has been rolled back.)) (Error while inserting: [GenericEntity:ProductFacility][productId,24050-EA(java.lang.String)][facilityId,ScipioShopWarehouse(java.lang.String)][minimumStock,2(java.math.BigDecimal)][reorderQuantity,10(java.math.BigDecimal)][daysToShip,2(java.lang.Long)][lastInventoryCount,null()][lastUpdatedStamp,2018-11-17 02:12:48.936(java.sql.Timestamp)][lastUpdatedTxStamp,2018-11-17 02:12:47.872(java.sql.Timestamp)][createdStamp,2018-11-17 02:12:48.936(java.sql.Timestamp)][createdTxStamp,2018-11-17 02:12:47.872(java.sql.Timestamp)] (SQL Exception while executing the following:INSERT INTO OFBIZ.PRODUCT_FACILITY (PRODUCT_ID, FACILITY_ID, MINIMUM_STOCK, REORDER_QUANTITY, DAYS_TO_SHIP, LAST_INVENTORY_COUNT, LAST_UPDATED_STAMP, LAST_UPDATED_TX_STAMP, CREATED_STAMP, CREATED_TX_STAMP) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?) (INSERT on table 'PRODUCT_FACILITY' caused a violation of foreign key constraint 'PROD_FAC_FAC' for key (ScipioShopWarehouse). The statement has been rolled back.))))

here is a much abbreviated copy of the xml file:

<entity-engine-transform-xml template="component://shop/data/templates/commonproduct.ftl">
	<products>
		<product productId="24050-EA" productCategoryId="10004" defaultPrice="28.15" productName="WIX 24050 Fuel Filter" description="WIX 24050 Fuel Filter"/>
		<product productId="24161-EA" productCategoryId="10002" defaultPrice="24.15" productName="WIX 24161 Cabin Air Filter (0)" description="WIX 24161 Cabin Air Filter (0)"/>
		<product productId="24479-EA" productCategoryId="10002" defaultPrice="20.15" productName="WIX 24479 Cabin Air Filter" description="WIX 24479 Cabin Air Filter"/>
		<product productId="24480-EA" productCategoryId="10002" defaultPrice="18.1" productName="WIX 24480 Cabin Air Filter" description="WIX 24480 Cabin Air Filter"/>
		<product productId="24483-EA" productCategoryId="10002" defaultPrice="15.05" productName="WIX 24483 Cabin Air Filter" description="WIX 24483 Cabin Air Filter"/>
		<product productId="24485-EA" productCategoryId="10002" defaultPrice="19.15" productName="WIX 24485 Cabin Air Filter" description="WIX 24485 Cabin Air Filter"/>
		<product productId="24511-EA" productCategoryId="10002" defaultPrice="15.35" productName="WIX 24511 Cabin Air Filter (0)" description="WIX 24511 Cabin Air Filter (0)"/>
		<product productId="24517-EA" productCategoryId="10002" defaultPrice="21.55" productName="WIX 24517 Cabin Air Filter (0)" description="WIX 24517 Cabin Air Filter (0)"/>
	</products>
</entity-engine-transform-xml>

Can special character cause any problem? In scrolling through the xml I found a few lines similar to this:

<product productId="G3270-EA" productCategoryId="10015" defaultPrice="0.95" productName="7.5” AMSOIL Magnetic Decal " description="7.5” AMSOIL Magnetic Decal "/>


#22

Yikes…

no, I fear that this one is on us. I just took a look and the template assumes that you have a Facility configured under the name of “ScipioShopWarehouse” and it will add Inventory Information for the product there. I am not sure why setup didn’t create one for you, but it will have to be more generic regardless. I will create an internal ticket for this.

We got 2 options in the meanwhile. You can either:

  1. Modify the template
  • Copy application/shop/data/templates/commonproduct.ftl to application/shop/data/templates/myproducts.ftl.
  • Change lines 108 - 112
    from

<ProductFacility productId="${productId}" facilityId=“ScipioShopWarehouse” minimumStock=“2” reorderQuantity=“10” daysToShip=“2”/>
<ProductFacilityLocation productId="${productId}" facilityId=“ScipioShopWarehouse” locationSeqId=“TLTLTLUL02” minimumStock=“2” moveQuantity=“20”/>
<InventoryItem facilityId=“ScipioShopWarehouse” locationSeqId=“TLTLTLUL02” datetimeReceived=“2008-08-01 08:00:00.000”
inventoryItemId="${inventoryItemId}" inventoryItemTypeId=“NON_SERIAL_INV_ITEM” productId="${productId}" ownerPartyId=“Company” currencyUomId=“USD” unitCost=“3.0”/>
<InventoryItemDetail inventoryItemId="${inventoryItemId}" inventoryItemDetailSeqId=“0001” effectiveDate=“2001-05-13 12:00:00.0” availableToPromiseDiff="${inventoryQuantity}" quantityOnHandDiff="${inventoryQuantity}" accountingQuantityDiff="${inventoryQuantity}"/>

to

<#--<ProductFacility productId="${productId}" facilityId="ScipioShopWarehouse" minimumStock="2" reorderQuantity="10" daysToShip="2"/>
<ProductFacilityLocation productId="${productId}" facilityId="ScipioShopWarehouse" locationSeqId="TLTLTLUL02" minimumStock="2" moveQuantity="20"/>
<InventoryItem facilityId="ScipioShopWarehouse" locationSeqId="TLTLTLUL02" datetimeReceived="2008-08-01 08:00:00.000"
    inventoryItemId="${inventoryItemId}" inventoryItemTypeId="NON_SERIAL_INV_ITEM" productId="${productId}" ownerPartyId="Company" currencyUomId="USD" unitCost="3.0"/>
<InventoryItemDetail inventoryItemId="${inventoryItemId}" inventoryItemDetailSeqId="0001" effectiveDate="2001-05-13 12:00:00.0" availableToPromiseDiff="${inventoryQuantity}" quantityOnHandDiff="${inventoryQuantity}" accountingQuantityDiff="${inventoryQuantity}"/>
-->
  • In your script change <entity-engine-transform-xml template=“component://shop/data/templates/commonproduct.ftl”> to <entity-engine-transform-xml template=“component://shop/data/templates/myproduct.ftl”>

      <Facility facilityId="ScipioShopWarehouse"/>
      <ProductStoreFacility productStoreId="ScipioShop" facilityId="ScipioShopWarehouse" fromDate="2001-05-13 12:00:00.0"/>
    

Or

  1. Add the facility to your own setup and ignore that there is inventory config being made. Import

     <entity-engine-xml><Facility facilityId="ScipioShopWarehouse"/> <entity-engine-xml>
    

And retry in both cases. I didn’t try this on my own, as it is specific to your data now, but I will guide you through the process…


#24

I had some difficulty due to long names/descriptions but figured it out and got it to upload!! Many thanks.

So when I figure out the other prices, do I import it the same way?


#25

Phew. Glad we got through that :slight_smile:

It all depends on the kind of prices you want to add. The template allows you to set:

listPrice, defaultPrice & minimumOrderPrice

You can set any of these three alongside the other info and it will be imported just fine.

However, for more complex prices or those including VAT, you will have to either adopt the template further or work with the entity files again (just search for ProductPrice in *Demo.xml files).

I should also mention: There are addons (like the german accounting standard SKR04: https://shop.scipioerp.com/products/ADDONS/p_ADDON_ACCT_DE_SKR04) available for purchase which offer you better examples for this specific case, but price calculation / setting prices in general, works fine even for those kind of prices ootb. The addons are also included in an Enterprise License from us.


#26

Here is what I am wanting as these pricing schemes are detailed by this particular vendor/distributor…
Cost/wholesale price
MSRP (regular) price
Preferred Customer price

How do these correlate to listPrice, defaultPrice, minimumOrderPrice?

image

Currently what I imported was my Cost/wholesale as the defaultPrice thinking that was base/wholesale.

Also, I get the error below when I go to the shop. Not a deal breaker atm as I have an existing webpage but…

image


#27

This how your prices correlate with the price types defined in ScipioERP:

  • Cost/wholesale price -> Wholesale Price
  • MSRP (regular) price -> Default Price
  • Preferred Customer price -> List Price

If you don’t mind, can you open a different ticket for the error you are facing? it will be also helpful if you can post the entire log or the error portion.