114 lines
4.1 KiB
MySQL
114 lines
4.1 KiB
MySQL
|
CREATE OR REPLACE DEFINER=`root`@`localhost`
|
||
|
|
SQL SECURITY DEFINER
|
||
|
|
VIEW `edi`.`supplyOffer`
|
||
|
|
AS SELECT `sr`.`vmpID` AS `vmpID`,
|
||
|
|
`di`.`ID` AS `diId`,
|
||
|
|
`sr`.`ID` AS `srId`,
|
||
|
|
`sr`.`Item_ArticleCode` AS `Item_ArticleCode`,
|
||
|
|
`sr`.`VBNOmschrijving` AS `product_name`,
|
||
|
|
`s`.`company_name` AS `company_name`,
|
||
|
|
cast(`sr`.`Price` AS decimal(10, 3)) AS `Price`,
|
||
|
|
`sr`.`Quality` AS `Quality`,
|
||
|
|
`sr`.`s1` AS `s1`,
|
||
|
|
`sr`.`s2` AS `s2`,
|
||
|
|
`sr`.`s3` AS `s3`,
|
||
|
|
`sr`.`s4` AS `s4`,
|
||
|
|
`sr`.`s5` AS `s5`,
|
||
|
|
`sr`.`s6` AS `s6`,
|
||
|
|
`sr`.`NumberOfUnits` AS `NumberOfUnits`,
|
||
|
|
least(
|
||
|
|
IF(
|
||
|
|
`sr`.`EmbalageCode` = 800,
|
||
|
|
`sr`.`EmbalageCode` * 10 + `sr`.`NumberOfItemsPerCask`,
|
||
|
|
`sr`.`EmbalageCode`
|
||
|
|
),
|
||
|
|
IFNULL(`idt`.`bucket_id`, '999')
|
||
|
|
) AS `EmbalageCode`,
|
||
|
|
`di`.`LatestDeliveryDateTime` AS `LatestDeliveryDateTime`,
|
||
|
|
`di`.`EarliestDespatchDateTime` AS `EarliestDespatchDateTime`,
|
||
|
|
`di`.`FirstOrderDateTime` AS `FirstOrderDateTime`,
|
||
|
|
`di`.`LatestOrderDateTime` AS `LatestOrderDateTime`,
|
||
|
|
`sr`.`NumberOfItemsPerCask` AS `NumberOfItemsPerCask`,
|
||
|
|
`sr`.`NumberOfLayersPerTrolley` AS `NumberOfLayersPerTrolley`,
|
||
|
|
`sr`.`MinimumNumberToOrder` AS `MinimumNumberToOrder`,
|
||
|
|
`sr`.`MaximumNumberToOrder` AS `MaximumNumberToOrder`,
|
||
|
|
`sr`.`IncrementalOrderableQuantity` AS `IncrementalOrderableQuantity`,
|
||
|
|
`sr`.`PackingPrice` AS `PackingPrice`,
|
||
|
|
`sr`.`MarketPlaceID` AS `MarketPlaceID`,
|
||
|
|
`sr`.`PictureReference` AS `PictureReference`,
|
||
|
|
`sr`.`updated` AS `supplyResponseUpdated`,
|
||
|
|
`i`.`group_id` AS `group_id`,
|
||
|
|
`mp`.`name` AS `marketPlace`,
|
||
|
|
`di`.`DeliveryPrice` AS `DeliveryPrice`,
|
||
|
|
`di`.`ChargeAmount` AS `ChargeAmount`,
|
||
|
|
`di`.`MinimumQuantity` AS `MinimumQuantity`,
|
||
|
|
`di`.`MaximumQuantity Integer` AS `MaximumQuantity`,
|
||
|
|
cast(
|
||
|
|
`sr`.`MinimumNumberToOrder` * CASE
|
||
|
|
`sr`.`MinimumOrderUnitType`
|
||
|
|
WHEN 1 THEN 1
|
||
|
|
WHEN 2 THEN `sr`.`NumberOfItemsPerCask` / `sr`.`NumberBunchesPerCask`
|
||
|
|
WHEN 3 THEN `sr`.`NumberOfItemsPerCask`
|
||
|
|
WHEN 4 THEN floor(128 * 56 / (`b`.`x_size` * `b`.`y_size`)) * `sr`.`NumberOfItemsPerCask`
|
||
|
|
WHEN 5 THEN floor(128 * 56 / (`b`.`x_size` * `b`.`y_size`)) * `sr`.`NumberOfItemsPerCask` * `sr`.`NumberOfLayersPerTrolley`
|
||
|
|
END AS decimal(10, 0)
|
||
|
|
) AS `OrderUnit`,
|
||
|
|
cast(
|
||
|
|
`sr`.`IncrementalOrderableQuantity` * CASE
|
||
|
|
`sr`.`IncrementalOrderableQuantityType`
|
||
|
|
WHEN 1 THEN 1
|
||
|
|
WHEN 2 THEN `sr`.`NumberOfItemsPerCask` / `sr`.`NumberBunchesPerCask`
|
||
|
|
WHEN 3 THEN `sr`.`NumberOfItemsPerCask`
|
||
|
|
WHEN 4 THEN floor(128 * 56 / (`b`.`x_size` * `b`.`y_size`)) * `sr`.`NumberOfItemsPerCask`
|
||
|
|
WHEN 5 THEN floor(128 * 56 / (`b`.`x_size` * `b`.`y_size`)) * `sr`.`NumberOfItemsPerCask` * `sr`.`NumberOfLayersPerTrolley`
|
||
|
|
END AS decimal(10, 0)
|
||
|
|
) AS `IncrementalOrderUnit`,
|
||
|
|
`mp`.`isEarlyBird` AS `isEarlyBird`,
|
||
|
|
`v`.`isVNHSupplier` AS `isVNHSupplier`,
|
||
|
|
`igo`.`expenseFk` AS `expenseFk`,
|
||
|
|
`igo`.`intrastatFk` AS `intrastatFk`,
|
||
|
|
`igo`.`originFk` AS `originFk`,
|
||
|
|
IFNULL(`idt`.`itemTypeFk`, `igo`.`itemTypeFk`) AS `itemTypeFk`
|
||
|
|
FROM (
|
||
|
|
(
|
||
|
|
(
|
||
|
|
(
|
||
|
|
(
|
||
|
|
(
|
||
|
|
(
|
||
|
|
(
|
||
|
|
(
|
||
|
|
`edi`.`deliveryInformation` `di`
|
||
|
|
JOIN `edi`.`supplyResponse` `sr` ON(`sr`.`ID` = `di`.`supplyResponseID`)
|
||
|
|
)
|
||
|
|
LEFT JOIN `edi`.`supplier` `s` ON(`s`.`glnAddressCode` = `sr`.`SupplierGLN`)
|
||
|
|
)
|
||
|
|
JOIN `edi`.`bucket` `b` ON(`b`.`bucket_id` = `sr`.`EmbalageCode`)
|
||
|
|
)
|
||
|
|
JOIN `edi`.`item` `i` ON(`i`.`id` = `sr`.`Item_ArticleCode`)
|
||
|
|
)
|
||
|
|
JOIN `edi`.`marketPlace` `mp` ON(`mp`.`id` = `sr`.`MarketPlaceID`)
|
||
|
|
)
|
||
|
|
JOIN `vn`.`floramondoConfig` `fm`
|
||
|
|
)
|
||
|
|
LEFT JOIN `edi`.`item_defaultType` `idt` ON(`idt`.`item_id` = `i`.`id`)
|
||
|
|
)
|
||
|
|
JOIN `edi`.`VMPSettings` `v` ON(`v`.`VMPID` = `sr`.`vmpID`)
|
||
|
|
)
|
||
|
|
JOIN `edi`.`item_groupToOffer` `igo` ON(`igo`.`group_code` = `i`.`group_id`)
|
||
|
|
)
|
||
|
|
WHERE `v`.`isBlocked` = 0
|
||
|
|
AND `s`.`isBanned` = 0
|
||
|
|
AND `sr`.`NumberOfUnits` > 0
|
||
|
|
AND CURRENT_TIMESTAMP() BETWEEN `di`.`FirstOrderDateTime` AND `di`.`LatestOrderDateTime`
|
||
|
|
AND `mp`.`isOffered` = 1
|
||
|
|
AND `di`.`LatestDeliveryDateTime` BETWEEN cast(`fm`.`nextLanded` AS date)
|
||
|
|
AND concat(
|
||
|
|
cast(`fm`.`nextLanded` AS date),
|
||
|
|
' ',
|
||
|
|
`fm`.`MaxLatestDeliveryHour`
|
||
|
|
)
|
||
|
|
AND `sr`.`NumberOfItemsPerCask` > 1
|
||
|
|
GROUP BY `sr`.`ID`
|
||
|
|
HAVING `EmbalageCode` <> 999
|