1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238
|
CREATE PROCEDURE dbo.PS_expRapportExpeditionProduit_sel (
@expBonBegin varchar(10) = '',
@expBonEnd varchar(10) = '',
@expPOBegin varchar(15) = '',
@expPOEnd varchar(15) = '',
@lotCodeBegin varchar(20) = '',
@lotCodeEnd varchar(20) = '',
@lotlblNoBegin varchar(10) = '',
@lotlblNoEnd varchar(10) = '',
@lotSchBegin varchar(20) = '',
@lotSchEnd varchar(20) = '',
@expDateBegin varchar(20) = '',
@expDateEnd varchar(20) = '',
@prdCodeBegin varchar(20) = '',
@prdCodeEnd varchar(20) = '',
@wrhCodeBegin varchar(20) = '',
@wrhCodeEnd varchar(20) = '',
@supCodeBegin varchar(20) = '',
@supCodeEnd varchar(20) = '',
@brdCodeBegin varchar(20) = '',
@brdCodeEnd varchar(20) = '',
@cntCodeBegin varchar(20) = '',
@cntCodeEnd varchar(20) = '',
@palCodeBegin varchar(20) = '',
@palCodeEnd varchar(20) = '',
@cliCodeBegin varchar(20) = '',
@cliCodeEnd varchar(20) = '',
@ReportData varchar(20) = '1',
@recRecetteBegin varchar(20) = '',
@recRecetteEnd varchar(20) = ''
) AS
SET NoCount ON
DECLARE @Query nvarchar(4000),
@Where bit,
@DateBegin varchar(20),
@DateEnd varchar(20)
SET @Where = 0
SET @Query = 'SELECT TB_expExpeditionLot.expBon, TB_expExpeditionLot.expPO, TB_expExpeditionLot.expPickupNo,
TB_lotLot.lotCode, TB_lotLot.lotLabelNo, TB_lotLot.lotSchedule, TB_palPaletteType.palCode, TB_palPaletteType.palPoid,
TB_cntContenantType.cntCode, TB_cntContenantType.cntPoid, TB_brdBrand.brdCode, TB_wrhWareHouse.wrhCode,
TB_supSupplier.supCode, TB_prdProduct.prdCode, TB_prdProduct.prdDescription, SUM(VW_lotNetWeight.lotNetWeight) AS lotNetWeight,
TB_expExpeditionLot.expDate, TB_expExpeditionLot.expWeight, TB_expExpeditionLot.expQty, TB_cliClient.cliCode,
TB_cliClient.cliDescription, TB_recReceptionLot.recDate, TB_expExpeditionLot.expWithPallet
FROM TB_recReceptionLot RIGHT OUTER JOIN
TB_lotLot ON TB_recReceptionLot.lotID = TB_lotLot.lotID RIGHT OUTER JOIN
TB_cliClient RIGHT OUTER JOIN
TB_expExpeditionLot ON TB_cliClient.cliID = TB_expExpeditionLot.cliID ON
TB_lotLot.lotID = TB_expExpeditionLot.lotID LEFT OUTER JOIN
TB_brdBrand ON TB_lotLot.brdID = TB_brdBrand.brdID LEFT OUTER JOIN
TB_cntContenantType ON TB_lotLot.cntID = TB_cntContenantType.cntID LEFT OUTER JOIN
TB_prdProduct ON TB_lotLot.prdID = TB_prdProduct.prdID LEFT OUTER JOIN
TB_wrhWareHouse ON TB_lotLot.wrhID = TB_wrhWareHouse.wrhID LEFT OUTER JOIN
TB_supSupplier ON TB_lotLot.supID = TB_supSupplier.supID LEFT OUTER JOIN
TB_palPaletteType ON TB_lotLot.palID = TB_palPaletteType.palID INNER JOIN
VW_lotNetWeight ON dbo.TB_lotLot.lotID = VW_lotNetWeight.lotID '
IF @expBonBegin <> '' AND @expBonEnd <> '' BEGIN
SET @Query = @Query + 'WHERE (TB_expExpeditionLot.expBon >= ' + @expBonBegin + ' AND TB_expExpeditionLot.expBon <= ' + @expBonEnd + ' ) '
SET @Where = 1
END
-- # de PO
IF @expPOBegin <> '' AND @expPOEnd <> '' BEGIN
IF @Where = 1
SET @Query = @Query + ' AND (TB_expExpeditionLot.expPO >= ''' + @expPOBegin + ''' AND TB_expExpeditionLot.expPO <= ''' + @expPOEnd + ''' ) '
IF @Where = 0 BEGIN
SET @Query = @Query + ' WHERE (TB_expExpeditionLot.expPO >= ''' + @expPOBegin + ''' AND TB_expExpeditionLot.expPO <= ''' + @expPOEnd + ''' ) '
SET @Where = 1
END
END
-- # de Lot
IF @lotCodeBegin <> '' AND @lotCodeEnd <> '' BEGIN
IF @Where = 1
SET @Query = @Query + ' AND (TB_lotLot.lotCode >= ''' + @lotCodeBegin + ''' AND TB_lotLot.lotCode <= ''' + @lotCodeEnd + ''' ) '
IF @Where = 0 BEGIN
SET @Query = @Query + ' WHERE (TB_lotLot.lotCode >= ''' + @lotCodeBegin + ''' AND TB_lotLot.lotCode <= ''' + @lotCodeEnd + ''' ) '
SET @Where = 1
END
END
-- # d'étiquette de Lot
IF @lotlblNoBegin <> '' AND @lotlblNoEnd <> '' BEGIN
IF @Where = 1
SET @Query = @Query + ' AND (TB_lotLot.lotLabelNo >= ''' + @lotlblNoBegin + ''' AND TB_lotLot.lotLabelNo <= ''' + @lotlblNoEnd + ''' ) '
IF @Where = 0 BEGIN
SET @Query = @Query + ' WHERE (TB_lotLot.lotLabelNo >= ''' + @lotlblNoBegin + ''' AND TB_lotLot.lotLabelNo <= ''' + @lotlblNoEnd + ''' ) '
SET @Where = 1
END
END
-- Cédule du lot
IF @lotSchBegin <> '' AND @lotSchEnd <> '' BEGIN
IF @Where = 1
SET @Query = @Query + ' AND (TB_lotLot.lotSchedule >= ''' + @lotSchBegin + ''' AND TB_lotLot.lotSchedule <= ''' + @lotSchEnd + ''' ) '
IF @Where = 0 BEGIN
SET @Query = @Query + ' WHERE (TB_lotLot.lotSchedule >= ''' + @lotSchBegin + ''' AND TB_lotLot.lotSchedule <= ''' + @lotSchEnd + ''' ) '
SET @Where = 1
END
END
-- Date de réception
IF @expDateBegin <> '' AND @expDateEnd <> '' BEGIN
--SET @DateBegin =CAST(@expDateBegin as varchar(20))
--SET @DateEnd = CAST(@expDateEnd as varchar(20))
IF @Where = 1
SET @Query = @Query + ' AND (TB_expExpeditionLot.expDate >= ''' + @expDateBegin + ''' AND TB_expExpeditionLot.expDate <= ''' + @expDateEnd + ''' ) '
IF @Where = 0 BEGIN
SET @Query = @Query + ' WHERE (TB_expExpeditionLot.expDate >= ''' + @expDateBegin + ''' AND TB_expExpeditionLot.expDate <= ''' + @expDateEnd + ''' ) '
SET @Where = 1
END
END
-- Product
IF @prdCodeBegin <> '' AND @prdCodeEnd <> '' BEGIN
IF @Where = 1
SET @Query = @Query + ' AND (TB_prdProduct.prdCode >= ''' + @prdCodeBegin + ''' AND TB_prdProduct.prdCode <= ''' + @prdCodeEnd + ''' ) '
IF @Where = 0 BEGIN
SET @Query = @Query + ' WHERE (TB_prdProduct.prdCode >= ''' + @prdCodeBegin + ''' AND TB_prdProduct.prdCode <= ''' + @prdCodeEnd + ''' ) '
SET @Where = 1
END
END
-- Warehouse
IF @wrhCodeBegin <> '' AND @wrhCodeEnd <> '' BEGIN
IF @Where = 1
SET @Query = @Query + ' AND (TB_wrhWarehouse.wrhCode >= ''' + @wrhCodeBegin + ''' AND TB_wrhWarehouse.wrhCode <= ''' + @wrhCodeEnd + ''' ) '
IF @Where = 0 BEGIN
SET @Query = @Query + ' WHERE (TB_wrhWarehouse.wrhCode >= ''' + @wrhCodeBegin + ''' AND TB_wrhWarehouse.wrhCode <= ''' + @wrhCodeEnd + ''' ) '
SET @Where = 1
END
END
-- Supplier
IF @supCodeBegin <> '' AND @supCodeEnd <> '' BEGIN
IF @Where = 1
SET @Query = @Query + ' AND (TB_supSupplier.supCode >= ''' + @supCodeBegin + ''' AND TB_supSupplier.supCode <= ''' + @supCodeEnd + ''' ) '
IF @Where = 0 BEGIN
SET @Query = @Query + ' WHERE (TB_supSupplier.supCode >= ''' + @supCodeBegin + ''' AND TB_supSupplier.supCode <= ''' + @supCodeEnd + ''' ) '
SET @Where = 1
END
END
-- Brand
IF @brdCodeBegin <> '' AND @brdCodeEnd <> '' BEGIN
IF @Where = 1
SET @Query = @Query + ' AND (TB_brdBrand.brdCode >= ''' + @brdCodeBegin + ''' AND TB_brdBrand.brdCode <= ''' + @brdCodeEnd + ''' ) '
IF @Where = 0 BEGIN
SET @Query = @Query + ' WHERE (TB_brdBrand.brdCode >= ''' + @brdCodeBegin + ''' AND TB_brdBrand.brdCode <= ''' + @brdCodeEnd + ''' ) '
SET @Where = 1
END
END
-- Contenant
IF @cntCodeBegin <> '' AND @cntCodeEnd <> '' BEGIN
IF @Where = 1
SET @Query = @Query + ' AND (TB_cntContenantType.cntCode >= ''' + @cntCodeBegin + ''' AND TB_cntContenantType.cntCode <= ''' + @cntCodeEnd + ''' ) '
IF @Where = 0 BEGIN
SET @Query = @Query + ' WHERE (TB_cntContenantType.cntCode >= ''' + @cntCodeBegin + ''' AND TB_cntContenantType.cntCode <= ''' + @cntCodeEnd + ''' ) '
SET @Where = 1
END
END
-- Palette
IF @palCodeBegin <> '' AND @palCodeEnd <> '' BEGIN
IF @Where = 1
SET @Query = @Query + ' AND (TB_palPaletteType.palCode >= ''' + @palCodeBegin + ''' AND TB_palPaletteType.palCode <= ''' + @palCodeEnd + ''' ) '
IF @Where = 0 BEGIN
SET @Query = @Query + ' WHERE (TB_palPaletteType.palCode >= ''' + @palCodeBegin + ''' AND TB_palPaletteType.palCode <= ''' + @palCodeEnd + ''' ) '
SET @Where = 1
END
END
-- Client
IF @cliCodeBegin <> '' AND @cliCodeEnd <> '' BEGIN
IF @Where = 1
SET @Query = @Query + ' AND (TB_cliClient.cliCode >= ''' + @cliCodeBegin + ''' AND TB_cliClient.cliCode <= ''' + @cliCodeEnd + ''' ) '
IF @Where = 0 BEGIN
SET @Query = @Query + ' WHERE (TB_cliClient.cliCode >= ''' + @cliCodeBegin + ''' AND TB_cliClient.cliCode <= ''' + @cliCodeEnd + ''' ) '
SET @Where = 1
END
END
-- Recette
IF @recRecetteBegin <> '' AND @recRecetteEnd <> '' BEGIN
IF @Where = 1
SET @Query = @Query + ' AND (TB_expExpeditionLot.expRecette >= ''' + @recRecetteBegin + ''' AND TB_expExpeditionLot.expRecette <= ''' + @recRecetteEnd + ''' ) '
IF @Where = 0 BEGIN
SET @Query = @Query + ' WHERE (TB_expExpeditionLot.expRecette >= ''' + @recRecetteBegin + ''' AND TB_expExpeditionLot.expRecette <= ''' + @recRecetteEnd + ''' ) '
SET @Where = 1
END
END
--Selon les data demandés
IF @Where = 1 BEGIN
IF @ReportData = '1'
SET @Query = @Query + ' AND ( (TB_expExpeditionLot.expRecette IS NULL) OR (Len (TB_expExpeditionLot.expRecette)=0) ) '
IF @ReportData = '2'
SET @Query = @Query + ' AND (NOT TB_expExpeditionLot.expRecette IS NULL) '
END
ELSE
BEGIN
IF @ReportData = '1'
SET @Query = @Query + ' WHERE ( (TB_expExpeditionLot.expRecette IS NULL) OR (Len (TB_expExpeditionLot.expRecette)=0) ) '
IF @ReportData = '2'
SET @Query = @Query + ' WHERE (NOT TB_expExpeditionLot.expRecette IS NULL) '
SET @Where = 1
END
IF @Where = 1
SET @Query = @Query + ' AND (TB_expExpeditionLot.expBon NOT LIKE ''Tra%'') '
IF @Where = 0
SET @Query = @Query + ' WHERE (TB_expExpeditionLot.expBon NOT LIKE ''Tra%'') '
SET @Query = @Query + ' GROUP BY TB_expExpeditionLot.expBon, TB_expExpeditionLot.expPO, TB_expExpeditionLot.expPickupNo,
TB_lotLot.lotCode, TB_lotLot.lotID, TB_lotLot.lotLabelNo, TB_lotLot.lotSchedule, TB_palPaletteType.palCode, TB_palPaletteType.palPoid,
TB_cntContenantType.cntCode, TB_cntContenantType.cntPoid, TB_brdBrand.brdCode, TB_wrhWareHouse.wrhCode,
TB_supSupplier.supCode, TB_prdProduct.prdCode, TB_prdProduct.prdDescription, TB_expExpeditionLot.expDate,
TB_expExpeditionLot.expWeight, TB_expExpeditionLot.expQty, TB_cliClient.cliCode,
TB_cliClient.cliDescription, TB_recReceptionLot.recDate, TB_expExpeditionLot.expWithPallet'
-- ORDER BY TB_prdProduct.prdCode, TB_cliClient.cliCode ,TB_expExpeditionLot.expBon'
EXEC sp_ExecuteSQL @Query
Set NoCount OFF
GO |
Partager