1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
|
create view QRY_Test8
as
select 'REGROUP0' as Regroup,qry.fps_codefamugine,cast(origine as varchar(20)) as origine, qry.HYF_Code, ISA_PORDHFAM,ISA_PRODHLINE,ISA_PRODHGRADE,ISA_PLANVENTES,ISA_PLANT,ISA_ORDERTYPE, ISA_CONTRAT, ISA_DIAMETRE, 0 as SalesEuroQ1,0 as SalesEuroQ2,0 as SalesEuroQ3,0 as SalesEuroQ4,0 as SalesEuroQ5,0 as SalesEuroQ6,0 as SalesEuroQ7,0 as SalesEuroQ8,0 as SalesEuroQ9,0 as SalesEuroQ10,0 as SalesEuroQ11,0 as SalesEuroQ12, P1*-1 as COGS_EuroQ1, P2*-1 as COGS_EuroQ2, P3*-1 as COGS_EuroQ3, P4*-1 as COGS_EuroQ4, P5*-1 as COGS_EuroQ5, P6*-1 as COGS_EuroQ6, P7*-1 as COGS_EuroQ7, P8*-1 as COGS_EuroQ8, P9*-1 as COGS_EuroQ9, P10*-1 as COGS_EuroQ10, P11*-1 as COGS_EuroQ11, P12*-1 as COGS_EuroQ12,0 as Tonnes_Q1,0 as Tonnes_Q2,0 as Tonnes_Q3,0 as Tonnes_Q4,0 as Tonnes_Q5,0 as Tonnes_Q6,0 as Tonnes_Q7,0 as Tonnes_Q8, 0 as Tonnes_Q9, 0 as Tonnes_Q10, 0 as Tonnes_Q11, 0 as Tonnes_Q12,vbr_num,dbo.f_Cus_Code(ISA_SLDTONO) as CUS_SAPCODE,0 as ISA_PVDQ1,0 as ISA_SalesUSDQ1,0 as ISA_PVDQ2,0 as ISA_SalesUSDQ2,0 as ISA_PVDQ3,0 as ISA_SalesUSDQ3,0 as ISA_PVDQ4,0 as ISA_SalesUSDQ4,0 as ISA_PVDQ5,0 as ISA_SalesUSDQ5,0 as ISA_PVDQ6,0 as ISA_SalesUSDQ6,0 as ISA_PVDQ7,0 as ISA_SalesUSDQ7,0 as ISA_PVDQ8,0 as ISA_SalesUSDQ8,0 as ISA_PVDQ9,0 as ISA_SalesUSDQ9,0 as ISA_PVDQ10,0 as ISA_SalesUSDQ10,0 as ISA_PVDQ11,0 as ISA_SalesUSDQ11,0 as ISA_PVDQ12,0 as ISA_SalesUSDQ12, ISA_ID, CUS.CUS_SAPDesc, CUS.CUS_Forecast, CUS.CUS_Trade, CUS.CUS_Country, LPS.LPS_CodeLigne, lps.LPS_LibLigneUgine, FPS.FPS_CodeFamDiam, FPS.FPS_LibFamDim
from qry_hmatxs qry
left outer join tblCustomer_CUS CUS on dbo.f_Cus_Code(ISA_SLDTONO)=CUS.CUS_sapcode
left outer join tblLgProdSavoie_LPS LPS on left(FPS_Codefamugine,4)=LPS.LPS_CodeLigne
left outer join tblFamProdSavoie_FPS FPS on QRY.fps_CodeFamUgine=FPS.fps_CodeFamUgine
where origine is not null
union all
select 'REGROUP1' as Regroup, qry.fps_codefamugine,origine, qry.HYF_Code, ISA_PORDHFAM,ISA_PRODHLINE,ISA_PRODHGRADE,ISA_PLANVENTES,ISA_PLANT,ISA_ORDERTYPE,ISA_CONTRAT, ISA_DIAMETRE, 0,0,0,0,0,0,0,0,0,0,0,0,P1*-1, P2*-1, P3*-1, P4*-1, P5*-1, P6*-1, P7*-1, P8*-1, P9*-1, P10*-1, P11*-1,P12*-1,0 as Q1,0 as Q2,0 as Q3,0 as Q4,0 as Q5,0 as Q6,0 as Q7,0 as Q8, 0 as Q9, 0 as Q10, 0 as Q11, 0 as Q12,vbr_num ,dbo.f_Cus_Code(ISA_SLDTONO) as CUS_SAPCODE,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0, ISA_ID , CUS.CUS_SAPDesc, CUS.CUS_Forecast, CUS.CUS_Trade, CUS.CUS_Country, LPS.LPS_CodeLigne, lps.LPS_LibLigneUgine, FPS.FPS_CodeFamDiam, FPS.FPS_LibFamDim
from qry_soustraitance qry
left outer join tblCustomer_CUS CUS on dbo.f_Cus_Code(ISA_SLDTONO)=CUS.CUS_sapcode
left outer join tblLgProdSavoie_LPS LPS on left(FPS_Codefamugine,4)=LPS.LPS_CodeLigne
left outer join tblFamProdSavoie_FPS FPS on QRY.fps_CodeFamUgine=FPS.fps_CodeFamUgine
where origine is not null
go |
