CREATE OR REPLACE PROCEDURE public.sp_getsafetystockexportdata(var_storeid int8, var_vendorid int8, var_filename varchar)
	LANGUAGE plpgsql
AS $$	
begin	
	execute 'DROP TABLE IF EXISTS ' || var_filename;
	execute 'CREATE TEMP TABLE ' || var_filename || ' AS 
	SELECT 
		vsii."__$etl_lsn",
		s.storeid,
		s.storenumber,
		s.store,
		v.vendorid,
		v.vendorexportcode,
		v.name,
		vsii.vendoritemcode,
		vsii.vendoritemdescription,
		uom.name AS purchaseunit,
		ii.itemcode,
		ii.description,
		ii.inventoryitemid,
		uom2.name AS inventoryunit,
		sii.uptqty,
		CASE sii.safetystockunit WHEN 1 THEN ''Days''
									WHEN 2 THEN ''Percentage''
									WHEN 3 THEN ''InventoryUnits''
        							ELSE ''Unknown'' END SafetyStockType,
		sii.safetystocknumber,
	   	sii.safetystockautocalc
	FROM 
		vendorstoreinventoryitem vsii
	JOIN 
		storeinventoryitem sii ON vsii.storeid = sii.storeid AND vsii.inventoryitemid = sii.inventoryitemid AND sii.active = 1 AND sii.safetystockunit IS NOT NULL
	JOIN 
		inventoryitem ii ON sii.inventoryitemid = ii.inventoryitemid
	JOIN 
		vendor v ON vsii.vendorid = v.vendorid
	JOIN 
		store s ON vsii.storeid = s.storeid
	LEFT JOIN 
		unitofmeasure uom ON vsii.purchaseunitid = uom.unitofmeasureid 
	LEFT JOIN 
		unitofmeasure uom2 ON ii.baseinventoryunitofmeasureid  = uom2.unitofmeasureid
	WHERE 
		(vsii.storeid = ' || var_storeid || ' OR ' || var_storeid || ' = 0)
		AND (' || var_vendorid || ' = 0 OR vsii.vendorid = ' || var_vendorid || ')
		AND sii.active = 1
		AND sii.safetystockunit IS NOT NULL
		AND (vsii.discontinuedate IS NULL OR vsii.discontinuedate > GETDATE())
	
	ORDER BY v.vendorid, ii.inventoryitemid;';	
END;
$$;