Stock List

SQL-Vorlage aus der reybex-Standardbibliothek – für BAS23-Exportjobs und Ad-hoc-Tests.

← Alle Beispiel-SQLs

Abfrage

SELECT
	material.id as material_hid,
	material.mat_code as material_matCode,
	(CASE
		WHEN materialtext.id IS NOT NULL THEN materialtext.name
		ELSE material.name
	END) AS materialtext_name,
	(CASE
		WHEN materialtext.id IS NOT NULL THEN materialtext.description
		ELSE material.description
	END) AS materialtext_description,
	material.asin AS asin,
	warehouse.id AS warehouse_hid,
	warehouse.name as warehouse_name,
	stockplace.name AS stockplace_name,
	stockplace.stockplace_code as stockplace_stockplaceCode,
	stockList.specialstock AS stockList_specialstock,
	stockList.batchnum AS stockList_batchnum,
	stockList.expiry_date AS stockList_expiryDate,
	stockList.license_key AS stockList_licenseKey,
	stockList.id AS stockList_id,
	stockList.total as stockList_total,
	stockList.blocked as stockList_blocked,
	stockList.avail as stockList_avail,
	stockList.reserved as stockList_reserved,
	variants.name as variants_name,
	variants.sku as variants_sku,
	unit.name as unit_name,
	group_concat(distinct msStockplace.name) AS stockplaces,
	stockList.variant_code as stockList_variantCode,
	stockList.attribute_code AS stockList_attributeCode,
	stockList.attribute_json AS stockList_attributeJson,
	stockList.attribute_code AS readableAttributeCode,
	unit.id as unit_hid,
	matGroup.name as matGroup_name,
	material.pur_price as material_purPrice,
	material.aquisition_price as material_aquisitionPrice,
	stockList.nve AS stockList_nve,
	variants.ean_code as variants_eanCode,
	(CASE
		WHEN variants.ean_code IS NOT NULL THEN variants.ean_code
		ELSE material.gtin
	END) as material_gtin,
	vendor.name as vendor_name,
	brand.name as brand_name,
	CASE
		WHEN (materialReorderLevel.id is null OR materialReorderLevel.reorder_stock_level is null)
			THEN material.reorder_stock_level
		ELSE materialReorderLevel.reorder_stock_level
	END as materialReorderLevel_reorderStockLevel,
	material.is_lot_enabled as isLotEnabled,
	material.is_serial_management as isSerialManagement,
	material.average_price as material_averagePrice,
	secondUnit.id as secondUnitId,
	CASE
		WHEN secondUnitTrans.name is null THEN secondUnit.name
		ELSE secondUnitTrans.name
	END as secondUnitName,
	CASE
		WHEN secondUnit.id is null THEN 0.00
		ELSE (sum(stockList.total) / materialUnitSecond.exchange_ratio_warehouse_unit / materialUnit.exchange_ratio_warehouse_unit)
	END as secondWarehouseUnitQuantity,
	materialType.name as materialType_name,
	null as purHead_docNumber,
	null as purItem_deliveryDate,
	stockplace.id AS stockplace_hid,
	stockplace.is_negative_stock_checked_against_avail AS stockplace_isNegativeStockCheckedAgainstAvail,
	material.net_weight as materialNetWeight,
	material.gross_weight as materialGrossWeight,
	materialNetWeightUnit.code as materialNetWeightUnitCode,
	materialGrossWeightUnit.code as materialGrossWeightUnitCode,
	material.name as material_name,
	unit.code as unit_code,
	null as deliveredPalletQuantity,
	null as deliveredCartonQuantity,
	null as pieceInOneCarton,
	null as palletUnitCode,
	null as cartonUnitCode,
	null as purItem_price,
	null as secondUnit_total,
	null as secondUnit_avail,
	null as secondUnit_reserved,
	null as secondUnit_exchangeRatio,
	'stockList' as listingType,
	secondUnit.code as secondUnitCode,
	slMetaData.notes as stockListMetaData_notes,
	slMetaData.prod_date as stockListMetaData_prodDate,
	material.note as material_note
FROM
	stock_list AS stockList
LEFT JOIN material AS material ON
	stockList.material_id = material.id
LEFT JOIN customer AS vendor ON
	vendor.id = material.vendor_id
LEFT JOIN material_variant AS variants ON
	variants.material_id = material.id
	AND variants.is_delete = 0
	AND (
		(variants.variant_code = stockList.variant_code
			OR (variants.variant_code IS NULL AND stockList.variant_code IS NULL))
		OR
		(variants.is_not_stockable = 1 AND INSTR(variants.variant_code, stockList.variant_code) > 0)
	)
LEFT JOIN material_trans AS materialtext ON
	materialtext.material_id = material.id AND materialtext.language = 'en'
LEFT JOIN ltt_unit AS unit ON
	unit.id = material.warehouse_unit_id
LEFT JOIN ltt_unit AS materialNetWeightUnit ON
	materialNetWeightUnit.id = material.netweight_unit_id
LEFT JOIN ltt_unit AS materialGrossWeightUnit ON
	materialGrossWeightUnit.id = material.grossweight_unit_id
LEFT JOIN material_units AS materialUnit ON
	materialUnit.material_id = material.id AND materialUnit.unit_id = unit.id
LEFT JOIN ltt_unit AS secondUnit ON
	secondUnit.id = material.second_warehouse_unit_id
LEFT JOIN ltt_unit_trans AS secondUnitTrans ON
	secondUnitTrans.unit_id = secondUnit.id AND secondUnitTrans.language = 'en'
LEFT JOIN material_units AS materialUnitSecond ON
	materialUnitSecond.material_id = material.id AND materialUnitSecond.unit_id = secondUnit.id
LEFT JOIN ltt_stockplace AS stockplace ON
	stockplace.id = stockList.stockplace_id
LEFT JOIN ltt_warehouse AS warehouse ON
	warehouse.id = stockList.warehouse_id
LEFT JOIN ltt_mat_group AS matGroup ON
	matGroup.id = material.mat_group_id
LEFT JOIN ltt_brand AS brand ON
	brand.id = material.brand_id
LEFT JOIN material_reorder_level AS materialReorderLevel ON
	materialReorderLevel.material_id = material.id
	AND (
		stockList.variant_code = materialReorderLevel.variant_code
		OR (stockList.variant_code IS NULL AND materialReorderLevel.variant_code IS NULL)
	)
	AND (
		stockList.attribute_code = materialReorderLevel.attribute_code
		OR (stockList.attribute_code IS NULL AND materialReorderLevel.attribute_code IS NULL)
	)
	AND (
		stockList.warehouse_id = materialReorderLevel.warehouse_id
		OR (stockList.warehouse_id IS NULL AND materialReorderLevel.warehouse_id IS NULL)
	)
LEFT JOIN ltt_material_type AS materialType ON
	materialType.id = material.material_type_id
LEFT JOIN ltt_mat_status AS matStatus ON
	matStatus.id = material.mat_status_id AND matStatus.is_inactive = 0
LEFT JOIN stock_list_meta_data AS slMetaData ON
	slMetaData.material_id = stockList.material_id
	AND slMetaData.warehouse_id = stockList.warehouse_id
	AND slMetaData.stockplace_id = stockList.stockplace_id
	AND (slMetaData.specialstock = stockList.specialstock OR slMetaData.specialstock IS NULL AND stockList.specialstock IS NULL)
	AND (slMetaData.variant_code = stockList.variant_code OR slMetaData.variant_code IS NULL AND stockList.variant_code IS NULL)
	AND (slMetaData.attribute_code = stockList.attribute_code OR slMetaData.attribute_code IS NULL AND stockList.attribute_code IS NULL)
	AND (slMetaData.license_key = stockList.license_key OR slMetaData.license_key IS NULL AND stockList.license_key IS NULL)
	AND (slMetaData.batchnum = stockList.batchnum OR slMetaData.batchnum IS NULL AND stockList.batchnum IS NULL)
	AND (slMetaData.nve = stockList.nve OR slMetaData.nve IS NULL AND stockList.nve IS NULL)
LEFT JOIN material_stock AS materialStock ON
	materialStock.material_id = material.id
	AND materialStock.warehouse_id = warehouse.id
	AND (materialStock.variant_code = stockList.variant_code OR materialStock.variant_code IS NULL)
	AND materialStock.is_delete = 0
LEFT JOIN ltt_stockplace AS msStockplace ON
	msStockplace.id = materialStock.stockplace_id
WHERE stockList.tenant_id = :tenantId
GROUP BY
	stockList.material_id,
	stockList.warehouse_id,
	stockplace.id,
	stockList.specialstock,
	stockList.batchnum,
	stockList.license_key,
	stockList.variant_code,
	stockList.nve,
	stockList.attribute_code
ORDER BY
	material.mat_code,
	warehouse.id,
	stockplace.pick_order,
	stockplace.stockplace_code,
	stockList.specialstock,
	stockList.batchnum,
	stockList.nve;