Notes

SELECT top 100 * FROM (SELECT
INVOICE.InvoiceHeaderID AS ERP_INVOICE_ID,
ISNULL(INVOICE.InvoiceNumber, '') AS ERP_INVOICE_DOCUMENT_NO,
'' AS UNIQUE_NO,
'' AS ALIAS,
case when len(CUSTOMER.TaxNumber)>0 then CUSTOMER.TaxNumber else CUSTOMER.IdentityNum end AS REGISTER_NUMBER,--vkn no
ISNULL(CUSDETAIL.CurrAccDescription,'') AS INSTITUTE_NAME,--müşteri adı
ISNULL(ADRESS.Address,'') AS STREET,--adres
ISNULL(ADRESS.BuildingName,'') AS BUILD_NAME,--bina adı
ISNULL(ADRESS.BuildingNum,'') AS BUILD_NO,--bina no
ISNULL(DISTRICT.DistrictDescription,'') AS DISTRICT,--mahalles
ISNULL(CITY.CityDescription,'') AS CITY_NAME,--şehir
ISNULL(ADRESS.ZipCode,'') AS ZIP_POSTAL_CODE,--posta kodu
(SELECT CommAddress FROM prCurrAccCommunication C where C.CurrAccCode=INVOICE.CurrAccCode and CommunicationTypeCode=1) AS PHONE,--telefon
(SELECT CommAddress FROM prCurrAccCommunication C where C.CurrAccCode=INVOICE.CurrAccCode and CommunicationTypeCode=2) AS FAX,--fax
'' AS EMAIL,--mail
TAXOFFICE.TaxOfficeDescription AS TAX_OFFICE,
'EARSIVFATURA' AS PROFILE_ID,
'SATIS' AS INVOICE_TYPE_CODE,
--INVOICE.Series + cast(year(INVOICE.InvoiceDate) as nvarchar) + substring('000000000', 1, abs(9-len(INVOICE.SeriesNumber))) + cast(INVOICE.SeriesNumber as nvarchar) AS INVOICE_NUMBER,
--'ABC2020000000001' AS INVOICE_NUMBER,
'GIB'+ cast(year(INVOICE.InvoiceDate) as nvarchar) + substring('000000000', 1, abs(9-len(INVOICE.SeriesNumber))) + cast(INVOICE.SeriesNumber as nvarchar) + '0' AS INVOICE_NUMBER,
CAST(INVOICE.InvoiceDate AS nvarchar)+' '+cast(INVOICE.InvoiceTime as nvarchar) AS ISSUE_DATE,
'' AS DESPATCH_NUMBER,
null AS DESPATCH_DATE,
'' AS ORDER_NUMBER,
null AS ORDER_DATE,
null AS PAYMENT_DUE_DATE,--case when len(CHH.cha_vade)<4 then dateadd(day,convert(int, CHH.cha_vade) * -1 ,getdate()) else null end AS PAYMENT_DUE_DATE,
'' AS ITEM_ID,
ITEMDESC.ItemDescription+' '+ITEMCOLOR.ColorDescription AS ITEM_NAME,
CASE WHEN DETAIL.Qty1>0 THEN DETAIL.Qty1 ELSE DETAIL.Qty1*-1 END AS QUANTITY,
ITEM.UnitOfMeasureCode1 AS UNIT_CODE,
coalesce(DETAIL.price,0) AS QUANTITY_AMOUNT,
DETAIL.LDisRate1 as DISCOUNT_PERCENTAGE1,
DETAIL.LDisRate2 as DISCOUNT_PERCENTAGE2,
DETAIL.LDisRate3 as DISCOUNT_PERCENTAGE3,
DETAIL.LDisRate4 as DISCOUNT_PERCENTAGE4,
DETAIL.LDisRate5 as DISCOUNT_PERCENTAGE5,
0 as DISCOUNT_PERCENTAGE6,
(SELECT COALESCE(C.TDiscount1,0) FROM trInvoiceLineCurrency C WHERE C.InvoiceLineID=DETAIL.InvoiceLineID) AS DISCOUNT_AMOUNT1,
(SELECT COALESCE(C.TDiscount2,0) FROM trInvoiceLineCurrency C WHERE C.InvoiceLineID=DETAIL.InvoiceLineID) AS DISCOUNT_AMOUNT2,
(SELECT COALESCE(C.TDiscount3,0) FROM trInvoiceLineCurrency C WHERE C.InvoiceLineID=DETAIL.InvoiceLineID) AS DISCOUNT_AMOUNT3,
(SELECT COALESCE(C.TDiscount4,0) FROM trInvoiceLineCurrency C WHERE C.InvoiceLineID=DETAIL.InvoiceLineID) AS DISCOUNT_AMOUNT4,
(SELECT COALESCE(C.TDiscount5,0) FROM trInvoiceLineCurrency C WHERE C.InvoiceLineID=DETAIL.InvoiceLineID) AS DISCOUNT_AMOUNT5,
0 AS DISCOUNT_AMOUNT6,
INVOICE.TDisRate1 as DISCOUNT_OVERALL_PERCENTAGE1,
INVOICE.TDisRate2 as DISCOUNT_OVERALL_PERCENTAGE2,
INVOICE.TDisRate3 as DISCOUNT_OVERALL_PERCENTAGE3,
INVOICE.TDisRate4 as DISCOUNT_OVERALL_PERCENTAGE4,
INVOICE.TDisRate5 as DISCOUNT_OVERALL_PERCENTAGE5,
0 as DISCOUNT_OVERALL_PERCENTAGE6,
0 as DISCOUNT_OVERALL_AMOUNT1,
0 as DISCOUNT_OVERALL_AMOUNT2,
0 as DISCOUNT_OVERALL_AMOUNT3,
0 as DISCOUNT_OVERALL_AMOUNT4,
0 as DISCOUNT_OVERALL_AMOUNT5,
0 as DISCOUNT_OVERALL_AMOUNT6,
DETAIL.VatRate AS TAX_PERCENTAGE,
(SELECT COALESCE(Vat,0) FROM trInvoiceLineCurrency C WHERE C.InvoiceLineID=DETAIL.InvoiceLineID) AS TAX_AMOUNT,
'' AS ITEM_NOTE,
'' AS ITEM_DESCRIPTION,
ITEMDESC.ItemDescription+' '+ITEMCOLOR.ColorDescription AS ITEM_SELLER_DESCRIPTION,
'' AS ITEM_MANUFACTURER_DESCRIPTION,
ISNULL(DETAIL.ITEMCODE,'') AS ITEM_BUYER_DESCRIPTION,
'Referans NO: ' + ' ' +INVOICE.InvoiceNumber AS NOTE1,
'' AS NOTE2,
'' AS NOTE3,
'' AS NOTE4,
'' AS NOTE5,
'' AS NOTE6,
'' AS NOTE7,
'' AS NOTE8,
'' AS NOTE9,
'' AS NOTE10,
(select SUM(LDiscount1)+SUM(LDiscount2)+SUM(LDiscount3)+SUM(LDiscount4)+SUM(LDiscount5)+
SUM(TDiscount1)+SUM(TDiscount2)+SUM(TDiscount3)+SUM(TDiscount4)+SUM(TDiscount5) from trInvoiceLineCurrency where InvoiceLineID IN (SELECT InvoiceLineID FROM trInvoiceLine
WHERE InvoiceHeaderID=INVOICE.InvoiceHeaderID)) as DISCOUNT_AMOUNT_TOTAL,
(SELECT SUM(L.Price) FROM trInvoiceLine L WHERE L.InvoiceHeaderID=INVOICE.InvoiceHeaderID) as PRICE_AMOUNT_TOTAL,
(select SUM(Vat) from trInvoiceLineCurrency where InvoiceLineID IN (SELECT InvoiceLineID FROM trInvoiceLine
WHERE InvoiceHeaderID=INVOICE.InvoiceHeaderID)) as TAX_AMOUNT_TOTAL,
--(SELECT SUM(CH1.cha_meblag) from CARI_HESAP_HAREKETLERI CH1 where CH1.cha_kod=CHH.cha_kod and CH1.cha_evrakno_seri=CHH.cha_evrakno_seri and CH1.cha_evrakno_sira=CHH.cha_evrakno_sira and CH1.cha_tarihi=CHH.cha_tarihi ) as TOTAL_AMOUNT_INCLUDE_TAX,
INVOICE.OfficeCode AS CODE,
CURR.Price AS PRICE_AMOUNT,
--CURR.VAT AS TAX_AMOUNT_TOTAL,
CURR.NetAmount as PAYABLE_AMOUNT,
CURR.NetAmount As TOTAL_AMOUNT_INCLUDE_TAX,
--(SELECT SUM(L.Price)-((SUM(L.Price)*(CASE WHEN INVOICE.TDisRate1>0 THEN INVOICE.TDisRate1 WHEN INVOICE.TDisRate2> 0 THEN INVOICE.TDisRate2
--WHEN INVOICE.TDisRate3>0 THEN INVOICE.TDisRate3 WHEN INVOICE.TDisRate4>0 THEN INVOICE.TDisRate4 WHEN INVOICE.TDisRate5>0 THEN INVOICE.TDisRate5 END)
--)/100) FROM trInvoiceLine L WHERE L.InvoiceHeaderID=INVOICE.InvoiceHeaderID)as PAYABLE_AMOUNT,
--(SELECT SUM(L.Price)-((SUM(L.Price)*(CASE WHEN INVOICE.TDisRate1>0 THEN INVOICE.TDisRate1 WHEN INVOICE.TDisRate2> 0 THEN INVOICE.TDisRate2
--WHEN INVOICE.TDisRate3>0 THEN INVOICE.TDisRate3 WHEN INVOICE.TDisRate4>0 THEN INVOICE.TDisRate4 WHEN INVOICE.TDisRate5>0 THEN INVOICE.TDisRate5 END)
--)/100) FROM trInvoiceLine L WHERE L.InvoiceHeaderID=INVOICE.InvoiceHeaderID) as TOTAL_AMOUNT_INCLUDE_TAX,
CASE WHEN INVOICE.TaxExemptionCode>0 THEN 'Muaf' else '' end as TAX_EXEMPTION_REASON,
INVOICE.TaxExemptionCode as TAX_EXEMPTION_REASON_CODE,
INVOICE.DocCurrencyCode as DOCUMENT_CURRENCY_CODE,
INVOICE.ExchangeRate as EXCHANGE_RATE,
'' as TAX_TYPE_CODE1,
INVOICE.CompanyCode as CC,

INVOICE.IsReturn as Devir,
0 as TAX_TYPE_CODE1_PERCENTAGE,
0 as TAX_TYPE_CODE1_AMOUNT

FROM trInvoiceHeader INVOICE
LEFT JOIN trInvoiceLine DETAIL ON INVOICE.InvoiceHeaderID=DETAIL.InvoiceHeaderID
LEFT JOIN trInvoiceLineCurrency CURR ON DETAIL.InvoiceLineID= CURR.InvoiceLineID
LEFT JOIN cdCurrAcc CUSTOMER ON INVOICE.CurrAccCode=CUSTOMER.CurrAccCode
LEFT JOIN cdCurrAccDesc CUSDETAIL ON CUSTOMER.CurrAccCode=CUSDETAIL.CurrAccCode
LEFT JOIN prCurrAccPostalAddress ADRESS ON ADRESS.CurrAccCode=CUSTOMER.CurrAccCode and ADRESS.AddressTypeCode='1'
LEFT JOIN cdDistrictDesc DISTRICT ON DISTRICT.DistrictCode=ADRESS.DistrictCode
LEFT JOIN cdCityDesc CITY ON CITY.CityCode=ADRESS.CityCode
LEFT JOIN cdTaxOfficeDesc TAXOFFICE ON TAXOFFICE.TaxOfficeCode=CUSTOMER.TaxOfficeCode
LEFT JOIN cdItem ITEM ON ITEM.ItemCode=DETAIL.ItemCode
LEFT JOIN cdItemDesc ITEMDESC ON ITEMDESC.ItemCode=DETAIL.ItemCode
LEFT JOIN cdColorDesc ITEMCOLOR ON ITEMCOLOR.ColorCode=DETAIL.ColorCode
) AS Data WHERE 1=1 AND Data.CODE='F801' AND Data.CC='8' AND Data.ERP_INVOICE_DOCUMENT_NO LIKE '8-R%' AND Data.Devir='0'