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
|
INSERT INTO`t_stock` (
`clef`,
`ref`,
GLOBAL,
`st_doc40`,
`dispo`,
`dispo_instantane`,
`fini_intermediaire_sans_st`,
`lavage_doc50`,
`avant_montage_deja_lave_doc50`,
`depart_doc50`,
`doc50`,
`retour_doc50`,
`fini_sans_st`,
`lavage_doc60`,
`depart_doc60`,
`doc60`,
`retour_doc60`,
`avant_montage_deja_lave`,
`rack_synchro`,
`lavage`,
`module`,
`stock_secu_module`,
`doc70`,
`magasin`,
`magasin_dim`,
`magasin_transit`,
`magasin_consignation`,
`magart`,
`magart_dim`,
`magart_transit`,
`magart_consignation`
)
SELECT`art`.`clef`,
`art`.`ref`,
`stk`.`global`,
`ifnull` (`st`.`st_doc40`, ?) AS `st_doc40`,
`stk`.`dispo`,
`ifnull` (`stk_brut`.`stock_dispo`, ?) AS `dispo_instantane`,
IF (
(
`art`.`module` = ?
AND `art`.`type` != ?
)
OR `art`.`type` = ?,
`stk`.`module1` - `ifnull` (`sst50`.`en_lav`, ?) - `ifnull` (`st`.`st_doc50`, ?) - `ifnull` (`sst50`.`depart`, ?) - `ifnull` (`sst50`.`recue`, ?)
, IF( ( art.module=1 AND art.type!=3 ) OR art.type=4
, stk.module1-IFNULL(sst50.en_lav,0)-IFNULL(st.st_doc50,0)-IFNULL(sst50.depart,0)-IFNULL(sst50.recue,0)
, stk.module1 ) AS fini_intermediaire_sans_st
, IF( ( art.module=1 AND art.type!=3 ) OR art.type=4
, stk.module1-IFNULL(sst50.en_lav,0)-IFNULL(st.st_doc50,0)-IFNULL(sst50.depart,0)-IFNULL(sst50.recue,0) -IFNULL(sst60.en_lav,0)-IFNULL(st.st_doc60,0)-IFNULL(sst60.depart,0)-IFNULL(sst60.recue,0)
, stk.module1 ) AS fini_intermediaire_sans_st
, IFNULL(sst50.en_lav,0) AS lavage_doc50
, IFNULL(sst50.au_lavage,0) AS avant_montage_deja_lave_doc50
, IFNULL(sst50.depart,0) AS depart_doc50
, IFNULL(st.st_doc50,0) AS doc50
, IFNULL(sst50.recue,0) AS retour_doc50
, IF( art.type=4
, stk.fini-stk.module1-IFNULL(sst60.en_lav,0)-IFNULL(st.st_doc60,0)-IFNULL(sst60.depart,0)-IFNULL(sst60.recue,0)-IFNULL(sst60.au_lavage,0)
, stk.fini-stk.module1-IFNULL(sst60.en_lav,0)-IFNULL(st.st_doc60,0)-IFNULL(sst60.depart,0)-IFNULL(sst60.recue,0)-IFNULL(sst60.au_lavage,0) )
AS fini_sans_st
, IF( art.type=4
, stk.fini-stk.module1
, stk.fini-stk.module1 )
AS fini_sans_st
, IFNULL(sst60.en_lav,0) AS lavage_doc60
, IFNULL(sst60.depart,0) AS depart_doc60
, IFNULL(st.st_doc60,0) AS doc60
, IFNULL(sst60.recue,0) AS retour_doc60
, IFNULL(sst60.au_lavage,0) AS avant_montage_deja_lave
, IFNULL( rs.qt, 0 ) AS rack_synchro
, IFNULL( lav.qt, 0 ) AS lavage
, 0 AS stock_secu_module
, IFNULL(st.st_doc70, 0 ) AS doc70
, stk.magasin-IFNULL( dim.magasin, 0 )-IFNULL( transit.magasin, 0 )-IFNULL( cons.magasin, 0 ) AS magasin
, IFNULL( dim.magasin, 0 ) AS magasin_dim
, IFNULL( transit.magasin, 0 ) AS magasin_transit
, IFNULL( cons.magasin, 0 ) AS magasin_consignation
, stk.magart-IFNULL( dim.magart, 0 )-IFNULL( transit.magart, 0 )-IFNULL( cons.magart, 0 ) AS magart
, IFNULL( dim.magart, 0 ) AS magart_dim
, IFNULL( transit.magart, 0 ) AS magart_transit
, IFNULL( cons.magart, 0 ) AS magart_consignation
FROM gds_gen_vtstk AS stk
JOIN gen_def_article AS art ON stk.clef = art.clef
LEFT JOIN gds_stock_brut_uniquement AS stk_brut ON stk_brut.ref = art.ref
LEFT JOIN t_sst AS st ON st.adr = stk.clef
LEFT JOIN t_impregn AS sst50 ON art.ref = sst50.ref
LEFT JOIN t_anod AS sst60 ON art.ref = sst60.ref
LEFT JOIN t_dim AS dim ON dim.ref=art.ref
LEFT JOIN t_transit AS transit ON transit.ref=art.ref
LEFT JOIN t_cons AS cons ON cons.ref=art.ref
LEFT JOIN t_rack_synchro AS rs ON rs.ref=art.ref
LEFT JOIN gds_stk_lav AS lav ON lav.ref=art.ref
WHERE art.ref >0
ORDER BY art.ref |
Partager