Stock Head

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

← Alle Beispiel-SQLs

Abfrage

SELECT
  stockHead.id AS stockHeadId,
  stockItem.last_updated AS stockItemLastUpdated,
  stockItem.id AS stockItemId,
  material.gtin AS materialGtin,
  invDocType.code AS invDocTypeCode,
  stockHead.doc_date AS stockHeadDocDate,
  stockItem.description AS stockItemDescription,
  stockItem.date_created AS stockItemCreatedDate,
  createdByUser.surname AS createdBySurname,
  stockItem.is_canceled AS isCanceled,
  material.mat_code AS materialCode,
  material.name AS materialName,
  warehouse.name AS warehouseName,
  variant.name AS variantName,
  variant.id AS variantId,
  variant.is_not_stockable AS isNotStockable,
  variant.sku AS variantSku,
  variant.name AS variantNameDuplicate,
  variant.ean_code AS variantEanCode,
  variant.asin AS variantAsin,
  CASE
    WHEN stockItem.total < 0 THEN -stockItem.entered_quantity
    ELSE stockItem.entered_quantity
  END AS enteredQuantityAdjusted,
  CASE
    WHEN salesCustomer.id IS NULL THEN purchaseCustomer.name
    ELSE salesCustomer.name
  END AS customerName,
  CASE
    WHEN salesCustomer.id IS NULL THEN purchaseCustomer.customer_no
    ELSE salesCustomer.customer_no
  END AS customerNo,
  CASE
    WHEN salesCustomer.id IS NULL THEN purchaseCustomer.id
    ELSE salesCustomer.id
  END AS customerId,
  stockItem.itemnum AS itemNumber,
  CASE
    WHEN stockItem.current_warehouse_stock IS NOT NULL THEN stockItem.current_warehouse_stock + stockItem.avail
    ELSE NULL
  END AS warehouseStockAfter,
  CASE
    WHEN stockItem.current_line_stock IS NOT NULL THEN stockItem.current_line_stock + stockItem.avail
    ELSE NULL
  END AS lineStockAfter,
  material.is_set AS materialIsSet,
  enteredUnit.name AS enteredUnitName,
  stockItem.total AS stockItemTotal,
  warehouseUnit.name AS warehouseUnitName,
  salesDocType.name AS salesDocTypeName,
  salesHead.doc_number AS salesDocNumber,
  salesHead.id AS salesHeadId,
  stockItem.license_key AS licenseKey,
  stockPlace.stockplace_code AS stockPlaceCode,
  stockItem.batchnum AS batchNumber,
  stockItem.nve AS nve,
  stockItem.expiry_date AS expiryDate,
  purchaseDocType.name AS purDocTypeName,
  purchaseHead.doc_number AS purDocNumber,
  purchaseHead.id AS purHeadId,
  material.id AS materialId,
  stockHead.del_note_doc_number AS deliveryNoteNumber,
  stockHead.post_date AS stockHeadPostDate,
  vendorCustomer.name AS vendorName,
  stockHead.doc_number AS stockHeadDocNumber,
  stockHead.container_number AS containerNumber,
  stockHead.is_parked AS isParked,
  stockItem.is_statistics AS isStatistics,
  stockItem.ts AS timestamp,
  stockItem.attribute_code AS attributeCode,
  stockHead.description AS stockHeadDescription,
  CASE
    WHEN purchaseItem.id IS NOT NULL THEN purchaseItem.is_verification_manually_closed
    ELSE stockItem.is_verification_manually_closed
  END AS isVerificationManuallyClosed,
  stockMoveType.name AS stockMoveTypeName,
  pickListHead.doc_number AS pickListDocNumber,
  linkedSalesHead.doc_number AS linkedSalesDocNumber,
  linkedSalesCustomer.name AS linkedSalesCustomerName,
  warehouse.id AS warehouseId,
  stockPlace.name AS stockPlaceName,
  '' AS emptyColumn,
  material.pur_price AS materialPurPrice,
  material.sales_price AS materialSalesPrice,
  material.sales_price_gross AS materialSalesPriceGross,
  material.asin AS materialAsin,
  material.rrp AS materialRrp,
  brand.name AS brandName,
  stockItem.reserved AS reservedQty,
  stockItem.avail AS availableQty,
  stockItem.blocked AS blockedQty,
  CASE
    WHEN materialTypeTrans.name IS NULL THEN materialType.name
    ELSE materialTypeTrans.name
  END AS materialTypeName,
  prodOrder.id AS prodOrderId,
  prodOrder.doc_number AS prodOrderNumber
FROM
  stock_item stockItem
INNER JOIN stock_head stockHead ON stockItem.stock_head_id = stockHead.id
LEFT JOIN material material ON stockItem.material_id = material.id
LEFT JOIN ltt_warehouse warehouse ON stockItem.warehouse_id = warehouse.id
LEFT JOIN ltt_unit warehouseUnit ON material.warehouse_unit_id = warehouseUnit.id
LEFT JOIN ltt_brand brand ON material.brand_id = brand.id
LEFT JOIN material_variant variant ON material.id = variant.material_id
  AND 17954 = variant.tenant_id
  AND variant.is_delete = 0
  AND (
    stockItem.variant_code IS NULL
    OR variant.variant_code = stockItem.variant_code
    OR (variant.is_not_stockable = 1 AND INSTR(variant.variant_code, stockItem.variant_code) > 0)
  )
LEFT JOIN sales_item salesItem ON stockItem.sales_item_id = salesItem.id
LEFT JOIN ltt_unit enteredUnit ON stockItem.entered_unit_id = enteredUnit.id
LEFT JOIN user createdByUser ON stockItem.created_by_id = createdByUser.id
LEFT JOIN sales_head salesHead ON salesItem.sales_head_id = salesHead.id
LEFT JOIN customer salesCustomer ON salesHead.customer_id = salesCustomer.id
LEFT JOIN ltt_sales_doc_type salesDocType ON salesHead.doc_type_id = salesDocType.id AND salesHead.tenant_id = salesDocType.tenant_id
LEFT JOIN pur_item purchaseItem ON stockItem.pur_item_id = purchaseItem.id
LEFT JOIN pur_head purchaseHead ON purchaseItem.pur_head_id = purchaseHead.id AND purchaseHead.tenant_id = purchaseItem.tenant_id
LEFT JOIN customer purchaseCustomer ON purchaseHead.vendor_id = purchaseCustomer.id AND purchaseCustomer.tenant_id = purchaseItem.tenant_id
LEFT JOIN ltt_stockplace stockPlace ON stockItem.stockplace_id = stockPlace.id
LEFT JOIN ltt_pur_doc_type purchaseDocType ON purchaseHead.pur_doc_type_id = purchaseDocType.id
LEFT JOIN ltt_inv_doc_type invDocType ON stockHead.inv_doc_type_id = invDocType.id
LEFT JOIN ltt_stock_move_typ stockMoveType ON stockItem.stock_move_type_id = stockMoveType.id
LEFT JOIN pick_list_item pickListItem ON stockItem.pick_list_item_id = pickListItem.id
LEFT JOIN pick_list_head pickListHead ON pickListItem.pick_list_head_id = pickListHead.id
LEFT JOIN sales_head linkedSalesHead ON pickListItem.sales_head_id = linkedSalesHead.id
LEFT JOIN customer linkedSalesCustomer ON linkedSalesHead.customer_id = linkedSalesCustomer.id
LEFT JOIN customer vendorCustomer ON stockHead.vendor_id = vendorCustomer.id
LEFT JOIN ltt_material_type materialType ON material.material_type_id = materialType.id
LEFT JOIN ltt_material_type_trans materialTypeTrans ON materialType.id = materialTypeTrans.material_type_id
  AND 17954 = materialTypeTrans.tenant_id AND materialTypeTrans.language = 'en'
LEFT JOIN prod_order_component prodOrderComponent ON stockItem.prod_order_component_id = prodOrderComponent.id
LEFT JOIN prod_order prodOrder ON stockItem.prod_order_id = prodOrder.id
WHERE
  17954 = stockItem.tenant_id
  AND stockItem.reserved = 0
GROUP BY
  stockItem.id
ORDER BY
  stockItem.id DESC;