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
|
select s.reference,s.designation,s.metier,s.famille,s.ssfamille,sum(s.memo),sum(s.inv),s.numinv
from (
select inventaire.inv_i_num as numinv,sku.art_c_ref as reference,art_c_dgn_lng as designation,c1.cm_c_code as metier,c2.cm_c_code as famille,c3.cm_c_code as ssfamille,0 as memo,sum(li_i_qte) as INV
from inventaire.inventaire
inner join inventaire.lig_inv on lig_inv.inv_i_num = inventaire.inv_i_num
inner join torpedo.sku on sku.sku_c_code = lig_inv.sku_c_code
inner join torpedo.article on article.art_c_ref = sku.art_c_ref
inner join torpedo.niv_clfct_mar as c1 on C1.ART_C_REF = ARTICLE.ART_C_REF AND C1.CM_I_NUM = 1 AND C1.CM_I_RANG = 2
inner join torpedo.niv_clfct_mar as c2 on C2.ART_C_REF = ARTICLE.ART_C_REF AND C2.CM_I_NUM = 1 AND C2.CM_I_RANG = 3
left outer join torpedo.niv_clfct_mar as c3 on C3.ART_C_REF = ARTICLE.ART_C_REF AND C3.CM_I_NUM = 1 AND C3.CM_I_RANG = 4
where inventaire.inv_i_num = 313 and article.art_l_actif = TRUE
group by inventaire.inv_i_num,sku.art_c_ref,art_c_dgn_lng,c1.cm_c_code,c2.cm_c_code,c3.cm_c_code
union
select inventaire.inv_i_num as numinv,sku.art_c_ref as reference,art_c_dgn_lng as designation,c1.cm_c_code as metier,c2.cm_c_code as famille,c3.cm_c_code as ssfamille,sum(ssm_i_qte) as memo,0 as INV
from inventaire.inventaire
inner join torpedo.sku_sto_memo on sku_sto_memo.sme_i_num = inventaire.sme_i_num
inner join torpedo.sku on sku.sku_c_code = sku_sto_memo.sku_c_code
inner join torpedo.article on article.art_c_ref = sku.art_c_ref
inner join torpedo.niv_clfct_mar as c1 on C1.ART_C_REF = ARTICLE.ART_C_REF AND C1.CM_I_NUM = 1 AND C1.CM_I_RANG = 2
inner join torpedo.niv_clfct_mar as c2 on C2.ART_C_REF = ARTICLE.ART_C_REF AND C2.CM_I_NUM = 1 AND C2.CM_I_RANG = 3
left outer join torpedo.niv_clfct_mar as c3 on C3.ART_C_REF = ARTICLE.ART_C_REF AND C3.CM_I_NUM = 1 AND C3.CM_I_RANG = 4
where inventaire.inv_i_num = 313 and article.art_l_actif = TRUE
group by inventaire.inv_i_num,sku.art_c_ref,art_c_dgn_lng,c1.cm_c_code,c2.cm_c_code,c3.cm_c_code)
as s
where s.reference not in (select art_c_ref from inventaire.justification where inv_i_num = 313)
group by s.numinv,s.reference,s.designation,s.metier,s.famille,s.ssfamille having sum(s.inv) <> sum(s.memo) |
Partager