Firma numarası olarak 400 kullandım. Burası kendi firma numaranıza göre güncellenmesi gerekir.
Kod: Tümünü seç
WITH CRDACREF AS (
SELECT
CARDREF,
TYP,
ACCOUNTREF
FROM LG_400_CRDACREF
WHERE TRCODE = 1
),
PIVOTED AS (
SELECT
CARDREF,
[1] AS Alım,
[2] AS DigerGirisler,
[3] AS Satış,
[4] AS DiğerÇıkışlar,
[5] AS Sarf,
[6] AS Fire,
[7] AS Üretim,
[10] AS Alımİade,
[11] AS Satışİade,
[12] AS SatınAlmaİndirim,
[13] AS Satışİndirim,
[14] AS SatınalmaMasrafları,
[15] AS SatışMasrafları,
[16] AS AlınanPromosyonlar,
[17] AS VerilenPromosyonlar,
[18] AS PromosyonKdvGiderleri,
[95] AS SayımFazlası,
[96] AS SayımEksiği,
[99] AS SMM,
[110] AS FiyatFarkı,
[111] AS MiktarFarkı,
[119] AS Yansıtma,
[135] AS İndirilenEkVergi,
[136] AS SatınAlmaFiyatFarkı,
[137] AS SatışFiyatFarkı,
[143] AS YurtDışıSatışlar,
[162] AS İhraçKayıtlıSatışlar,
[172] AS İhraçKayıtlıAlımlar,
[199] AS SMMTFRS
FROM (
SELECT
CARDREF,
TYP,
ACCOUNTREF
FROM CRDACREF
) AS SourceTable
PIVOT (
MAX(ACCOUNTREF)
FOR TYP IN ([1], [2], [3], [4], [5], [6], [7], [10], [11], [12],
[13], [14], [15], [16], [17], [18], [95], [96], [99], [110], [111],
[119], [135], [136], [137], [143], [162], [172], [199])
) AS PivotTable
),
EMUHACC AS (
SELECT
LOGICALREF,
CODE
FROM LG_400_EMUHACC
)
SELECT
I.CODE AS MalzemeKodu,
I.NAME AS MalzemeAdı,
ISNULL(E1.CODE, '') AS Alım,
ISNULL(E2.CODE, '') AS DigerGirisler,
ISNULL(E3.CODE, '') AS Satış,
ISNULL(E4.CODE, '') AS DiğerÇıkışlar,
ISNULL(E5.CODE, '') AS Sarf,
ISNULL(E6.CODE, '') AS Fire,
ISNULL(E7.CODE, '') AS Üretim,
ISNULL(E10.CODE, '') AS Alımİade,
ISNULL(E11.CODE, '') AS Satışİade,
ISNULL(E12.CODE, '') AS SatınAlmaİndirim,
ISNULL(E13.CODE, '') AS Satışİndirim,
ISNULL(E14.CODE, '') AS SatınalmaMasrafları,
ISNULL(E15.CODE, '') AS SatışMasrafları,
ISNULL(E16.CODE, '') AS AlınanPromosyonlar,
ISNULL(E17.CODE, '') AS VerilenPromosyonlar,
ISNULL(E18.CODE, '') AS PromosyonKdvGiderleri,
ISNULL(E95.CODE, '') AS SayımFazlası,
ISNULL(E96.CODE, '') AS SayımEksiği,
ISNULL(E99.CODE, '') AS SMM,
ISNULL(E110.CODE, '') AS FiyatFarkı,
ISNULL(E111.CODE, '') AS MiktarFarkı,
ISNULL(E119.CODE, '') AS Yansıtma,
ISNULL(E135.CODE, '') AS İndirilenEkVergi,
ISNULL(E136.CODE, '') AS SatınAlmaFiyatFarkı,
ISNULL(E137.CODE, '') AS SatışFiyatFarkı,
ISNULL(E143.CODE, '') AS YurtDışıSatışlar,
ISNULL(E162.CODE, '') AS İhraçKayıtlıSatışlar,
ISNULL(E172.CODE, '') AS İhraçKayıtlıAlımlar,
ISNULL(E199.CODE, '') AS SMMTFRS
FROM LG_400_ITEMS I
LEFT JOIN PIVOTED P ON P.CARDREF = I.LOGICALREF
LEFT JOIN EMUHACC E1 ON E1.LOGICALREF = P.Alım
LEFT JOIN EMUHACC E2 ON E2.LOGICALREF = P.DigerGirisler
LEFT JOIN EMUHACC E3 ON E3.LOGICALREF = P.Satış
LEFT JOIN EMUHACC E4 ON E4.LOGICALREF = P.DiğerÇıkışlar
LEFT JOIN EMUHACC E5 ON E5.LOGICALREF = P.Sarf
LEFT JOIN EMUHACC E6 ON E6.LOGICALREF = P.Fire
LEFT JOIN EMUHACC E7 ON E7.LOGICALREF = P.Üretim
LEFT JOIN EMUHACC E10 ON E10.LOGICALREF = P.Alımİade
LEFT JOIN EMUHACC E11 ON E11.LOGICALREF = P.Satışİade
LEFT JOIN EMUHACC E12 ON E12.LOGICALREF = P.SatınAlmaİndirim
LEFT JOIN EMUHACC E13 ON E13.LOGICALREF = P.Satışİndirim
LEFT JOIN EMUHACC E14 ON E14.LOGICALREF = P.SatınalmaMasrafları
LEFT JOIN EMUHACC E15 ON E15.LOGICALREF = P.SatışMasrafları
LEFT JOIN EMUHACC E16 ON E16.LOGICALREF = P.AlınanPromosyonlar
LEFT JOIN EMUHACC E17 ON E17.LOGICALREF = P.VerilenPromosyonlar
LEFT JOIN EMUHACC E18 ON E18.LOGICALREF = P.PromosyonKdvGiderleri
LEFT JOIN EMUHACC E95 ON E95.LOGICALREF = P.SayımFazlası
LEFT JOIN EMUHACC E96 ON E96.LOGICALREF = P.SayımEksiği
LEFT JOIN EMUHACC E99 ON E99.LOGICALREF = P.SMM
LEFT JOIN EMUHACC E110 ON E110.LOGICALREF = P.FiyatFarkı
LEFT JOIN EMUHACC E111 ON E111.LOGICALREF = P.MiktarFarkı
LEFT JOIN EMUHACC E119 ON E119.LOGICALREF = P.Yansıtma
LEFT JOIN EMUHACC E135 ON E135.LOGICALREF = P.İndirilenEkVergi
LEFT JOIN EMUHACC E136 ON E136.LOGICALREF = P.SatınAlmaFiyatFarkı
LEFT JOIN EMUHACC E137 ON E137.LOGICALREF = P.SatışFiyatFarkı
LEFT JOIN EMUHACC E143 ON E143.LOGICALREF = P.YurtDışıSatışlar
LEFT JOIN EMUHACC E162 ON E162.LOGICALREF = P.İhraçKayıtlıSatışlar
LEFT JOIN EMUHACC E172 ON E172.LOGICALREF = P.İhraçKayıtlıAlımlar
LEFT JOIN EMUHACC E199 ON E199.LOGICALREF = P.SMMTFRS