|
SELECT [DC~582~Cutting diameter~],[DCONMS~500~Connection diameter machine side~],[OAL~512~Overall length~],[LU~655~Usable length~],[PL~675~Point length~],[LS~604~Shank length~],[LCF~656~Length chip flute~],[FTDZ~561~For thread diameter size~Used for standard thread size],[ZEFF~595~Face effective cutting edge count~],[ULDR~676~Usable length diameter ratio~],[CSP~651~Coolant supply property~],[HAND~553~Hand~],[NOF~597~Flute count~],[CCMS~718~Connection code machine side~],[GRDMFG~627~Grade manufacturers designation~],[ApplMat~9011~Applicable Material~],[StaSteel~9012~Stainless Steel~],[CastIron~9013~Cast Iron~],[HardMa~9014~Hard Materials~],[SupTit~9016~Superalloys and titanium~],[CutEdMat~9020~Cutting Edge Material~],[Coating~9030~Coating~],GICAT,GIREM, giic FROM
(SELECT distinct min(GISEQ)GISEQ,count(CASE IBG.ICOC WHEN '' THEN NULL ELSE ICOC END) giic,case @xUnits WHEN 'I' THEN GI.GIDSCA ELSE GI.GIDSCO END AS GIDSCO,SUBSTRING(LTRIM(RTRIM(MIN(isnull(case @xUnits WHEN 'I' THEN GP.GPTGI ELSE GP.GPTGM END,
case GFMANM
WHEN 'TH' THEN case @xUnits WHEN 'I' THEN GP_1.GPTHI ELSE GP_1.GPTHM END
WHEN 'ML' THEN case @xUnits WHEN 'I' THEN GP_1.GPMLI ELSE GP_1.GPMLM END
WHEN 'IS' THEN case @xUnits WHEN 'I' THEN GP_1.GPISI ELSE GP_1.GPISM END
WHEN 'TG' THEN case @xUnits WHEN 'I' THEN GP_1.GPTGI ELSE GP_1.GPTGM END
WHEN 'DR' THEN case @xUnits WHEN 'I' THEN GP_1.GPDRI ELSE GP_1.GPDRM END
WHEN 'IT' THEN case @xUnits WHEN 'I' THEN GP_1.GPITI ELSE GP_1.GPITM END
WHEN 'BO' THEN case @xUnits WHEN 'I' THEN GP_1.GPBOI ELSE GP_1.GPBOM END
WHEN 'RE' THEN case @xUnits WHEN 'I' THEN GP_1.GPDRI ELSE GP_1.GPDRM END
WHEN 'MF' THEN case @xUnits WHEN 'I' THEN GP_1.GPMFI ELSE GP_1.GPMFM END
END
)))) +'~'+ CAST(isnull(gp.gpnum,gp_1.gpnum) as varchar(10))+ '~' + CAST(LTRIM(RTRIM(MAX(ISNULL(GP_TRANS.GPDSC,isnull(GP.GPDSC,GP_1.GPDSC)))))as varchar(50))+'~' + CAST(LTRIM(RTRIM(case @xUnits WHEN 'I' THEN MAX(ISNULL(GFIP_TRANS.GFIREM1, ISNULL(TRREM,GFIP.GFIREM1))) ELSE MAX(ISNULL(GFIP_TRANS.GFIREM, ISNULL(TRREM,GFIP.GFIREM))) END))as varchar(120)) ,1,126) as GIPRGP,
MAX(GI.GICAT)as GICAT,MAX(CASE @xUnits WHEN 'M' THEN ISNULL(GI_TRANS.GIREM, GI.GIREM) ELSE ISNULL(GI_TRANS.GIREM1,GI.GIREM1) END) AS GIREM,
MIN(case gp_1.GPPRTP
WHEN 'N' THEN
CASE PATINDEX('999%',ISNULL(ltrim(STR(CASE @xUnits WHEN 'M' THEN GIPRNM ELSE GIPRNI END,7,CONVERT(INT,case @xUnits WHEN 'I' THEN gp_1.GPDECI ELSE gp_1.GPDECM END))),'-'))
WHEN 0 THEN
CASE @xUnits WHEN 'M' THEN
ISNULL(ltrim(STR(GIPRNM ,7,CONVERT(INT,gp_1.GPDECM))),'-')
ELSE
ISNULL(SUBSTRING(ltrim(STR(GIPRNI,7,CONVERT(INT,gp_1.GPDECI))),PATINDEX('%[^0 ]%',ltrim(STR(GIPRNI ,7,CONVERT(INT,gp_1.GPDECI)))+ ' '), LEN(ltrim(STR(GIPRNI ,7,CONVERT(INT,gp_1.GPDECI))))) ,'-')
END
ELSE
CASE PATINDEX('9999%',ISNULL(ltrim(STR(CASE @xUnits WHEN 'M' THEN GIPRNM ELSE GIPRNI END,7,CONVERT(INT,case @xUnits WHEN 'I' THEN gp_1.GPDECI ELSE gp_1.GPDECM END))),'-'))
WHEN 0 THEN ''
ELSE '-'
END END
ELSE
ISNULL(CASE @xUnits WHEN 'M' THEN REPLACE(REPLACE(GIPRAM,'9999','-'),'999','') ELSE REPLACE(REPLACE(GIPRAI,'9999','-'),'999','') END,'0')END+'~'+CAST(LTRIM(RTRIM(CASE @xUnits WHEN 'M' THEN ISNULL(GIPR_TRANS.GIPRRM, GIPR.GIPRRM) ELSE ISNULL(GIPR_TRANS.GIPRRM1, GIPR.GIPRRM1)END)) as varchar(100)))
VAl
FROM
GI(nolock)LEFT OUTER JOIN GI_TRANS (nolock)ON GI.GICAT=GI_TRANS.GICAT AND GI_TRANS.GICNT=@xLANG
INNER JOIN GF(nolock)ON GF.GFNUM=GI.GIFNUM
INNER JOIN GTMA(nolock)ON GF.GFMANM=GTMA.MANUM INNER JOIN GFIP ON GF.GFNUM=GFIP.GFIPFN
LEFT OUTER JOIN GFIP_TRANS(nolock) ON GFIP.GFIPFN = GFIP_TRANS.GFIPFN AND GFIP.GFIPGP=GFIP_TRANS.GFIPGP AND GFIP_TRANS.GFIPCNT=@xLANG
LEFT OUTER JOIN GP GP_1(nolock)on gfip.GFIPGP=GP_1.GPNUM
outer apply
[fn_SeparateElements](case when GFIP.GFIPGP>499 then cast(GFIP.GFIPGP as nvarchar(50))+cast(','as nvarchar(1))+cast([GFIPISO]as nvarchar(50)) else [GFIPISO] end
)s left join GP (nolock) on GP.gpnum=CASE ISNUMERIC(s.token) WHEN '1' THEN s.token ELSE '0' END
LEFT OUTER JOIN GP_TRANS(nolock) ON GP.GPNUM=GP_TRANS.GPNUM AND GP_TRANS.GPCNT= @xLANG
FULL OUTER JOIN GIPR(nolock) ON GIPR.GIPRGP=GP_1.GPNUM AND GI.GICAT = GIPR.GIPRIT
LEFT OUTER JOIN GIPR_TRANS(nolock) ON GIPR.GIPRIT=GIPR_TRANS.GIPRIT AND GIPR.GIPRGP=GIPR_TRANS.GIPRGP AND GIPR_TRANS.GIPRCNT=@xLANG
left JOIN IBG(nolock)ON GI.GIIC=IBG.ICOC LEFT JOIN TR(nolock)ON GP.GPNUM=TRKEYD AND TRTYP1=@xUnits
WHERE (GFSTYP ='B' OR GFSTYP =@xUnits)
and (GITYP <> @xGITYP) AND (GI.GIMRK<>'F')
AND @xGIDESC=CASE @xUnits WHEN 'I' THEN GI.GIDSCA ELSE GI.GIDSCO END and GIFNUM=@xGIFNUM
GROUP BY case @xUnits WHEN 'I' THEN GI.GIDSCA ELSE GI.GIDSCO END,GIFNUM,GP.GPNUM,GP_1.GPNUM,GFIPISO
)aaa PIVOT( MAX(val) FOR GIPRGP IN(
[DC~582~Cutting diameter~],[DCONMS~500~Connection diameter machine side~],[OAL~512~Overall length~],[LU~655~Usable length~],[PL~675~Point length~],[LS~604~Shank length~],[LCF~656~Length chip flute~],[FTDZ~561~For thread diameter size~Used for standard thread size],[ZEFF~595~Face effective cutting edge count~],[ULDR~676~Usable length diameter ratio~],[CSP~651~Coolant supply property~],[HAND~553~Hand~],[NOF~597~Flute count~],[CCMS~718~Connection code machine side~],[GRDMFG~627~Grade manufacturers designation~],[ApplMat~9011~Applicable Material~],[StaSteel~9012~Stainless Steel~],[CastIron~9013~Cast Iron~],[HardMa~9014~Hard Materials~],[SupTit~9016~Superalloys and titanium~],[CutEdMat~9020~Cutting Edge Material~],[Coating~9030~Coating~]
)
)PivotTable
order by GISEQ OPTION(max_grant_percent =0.7) |