Notes

SELECT * FROM (SELECT
INVOICE.LOGICALREF AS ERP_INVOICE_ID,
ISNULL(INVOICE.FICHENO, '') AS ERP_INVOICE_DOCUMENT_NO,
INVOICE.GUID AS UNIQUE_NO,
'' AS ALIAS,
CASE WHEN LEN(CUSTOMER.TAXNR)>0 THEN CUSTOMER.TAXNR ELSE CUSTOMER.TCKNO END AS REGISTER_NUMBER,
CUSTOMER.DEFINITION_ AS INSTITUTE_NAME,
CUSTOMER.ADDR1 + ' ' + CUSTOMER.ADDR2 AS STREET, '' AS BUILD_NAME,
'' AS BUILD_NO,
CUSTOMER.DISTRICT AS DISTRICT, DETAIL.INVOICELNNO AS SATIRNO,CUSTOMER.CITY AS CITY_NAME,
CUSTOMER.POSTCODE AS ZIP_POSTAL_CODE,
CUSTOMER.TELNRS1 AS PHONE,
CUSTOMER.FAXNR AS FAX,
CUSTOMER.EMAILADDR AS EMAIL,
CUSTOMER.TAXOFFICE AS TAX_OFFICE,
case when CUSTOMER.PROFILEID = 2 then 'TICARIFATURA' when CUSTOMER.PROFILEID = 1 then 'TEMELFATURA' else 'TEMELFATURA' end AS PROFILE_ID,
case when INVOICE.TRCODE = 6 then 'IADE' else 'SATIS' end AS INVOICE_TYPE_CODE,
INVOICE.FICHENO AS INVOICE_NUMBER,
INVOICE.DATE_ AS ISSUE_DATE,
DISPATCH.FICHENO AS DESPATCH_NUMBER,
DISPATCH.DATE_ AS DESPATCH_DATE,
ORD.FICHENO AS ORDER_NUMBER,
ORD.DATE_ ORDER_DATE,
DATEADD(DAY,((STR(SUBSTRING(PAYLINES.DAY_,2,3)))*1),INVOICE.DATE_) AS PAYMENT_DUE_DATE,
CASE WHEN INVOICE.TRCODE=9 THEN ITEMHIZ.LOGICALREF ELSE ITEM.LOGICALREF END AS ITEM_ID,
CASE WHEN DETAIL.LINETYPE <> 4 THEN ITEM.NAME else ITEMHIZ.DEFINITION_ END AS ITEM_NAME,--DETAIL.LINEEXP
--CASE WHEN INVOICE.TRCODE IN (7) THEN 1 WHEN INVOICE.TRCODE IN (8) THEN 0 else 0 END AS REMOVE_TAX_AMOUNT,
DETAIL.AMOUNT AS QUANTITY,
DETAIL.LINENET AS PRICE_AMOUNT,
UNIT.CODE AS UNIT_CODE,
CASE WHEN DETAIL.TRCURR > 0 and DETAIL.TRRATE>0 then DETAIL.PRICE / DETAIL.TRRATE ELSE DETAIL.PRICE end AS QUANTITY_AMOUNT,
DETAIL.VAT AS TAX_PERCENTAGE,
case when DETAIL.TRCURR > 0 and DETAIL.TRRATE>0 then DETAIL.VATAMNT / DETAIL.TRRATE else DETAIL.VATAMNT END as TAX_AMOUNT,
'' AS ITEM_NOTE,
'' AS ITEM_DESCRIPTION,
CASE WHEN INVOICE.TRCODE=9 THEN ITEMHIZ.CODE ELSE ITEM.CODE END AS ITEM_SELLER_DESCRIPTION,
'' as ITEM_MANUFACTURER_DESCRIPTION,
'' as ITEM_BUYER_DESCRIPTION,
'' as MUSTERI_NO,
ISNULL(INVOICE.GENEXP1, '') AS NOTE1,
ISNULL(INVOICE.GENEXP2, '') AS NOTE2,
ISNULL(INVOICE.GENEXP3, '') AS NOTE3,
ISNULL(INVOICE.GENEXP4, '') as NOTE4,
'' as NOTE5,
NULL as ADDITIONAL_COST1,
NULL as ADDITIONAL_COST2,
CASE WHEN DETAIL.LINETYPE=1 THEN 100 ELSE (coalesce((select TOP 1 TMP.DISCPER FROM (select row_number() over(order by (select 0)) rowNum,DISCPER from LG_120_01_STLINE DETAIL_ where DETAIL_.PARENTLNREF=DETAIL.LOGICALREF AND LINETYPE=2) tmp where tmp.rowNum=1),0)) END AS DISCOUNT_PERCENTAGE1,
coalesce((select TOP 1 TMP.DISCPER FROM (select row_number() over(order by (select 0)) rowNum,DISCPER from LG_120_01_STLINE DETAIL_ where DETAIL_.PARENTLNREF=DETAIL.LOGICALREF AND LINETYPE=2) tmp where tmp.rowNum=2),0) AS DISCOUNT_PERCENTAGE2,
0 AS DISCOUNT_PERCENTAGE3,
0 AS DISCOUNT_PERCENTAGE4,
0 AS DISCOUNT_PERCENTAGE5,
0 AS DISCOUNT_PERCENTAGE6,
coalesce((select TOP 1 TMP.TOTAL FROM (select row_number() over(order by (select 0)) rowNum,TOTAL from LG_120_01_STLINE DETAIL_ where DETAIL_.PARENTLNREF=DETAIL.LOGICALREF AND LINETYPE=2) tmp where tmp.rowNum=1),0) as DISCOUNT_AMOUNT1,
coalesce((select TOP 1 TMP.TOTAL FROM (select row_number() over(order by (select 0)) rowNum,TOTAL from LG_120_01_STLINE DETAIL_ where DETAIL_.PARENTLNREF=DETAIL.LOGICALREF AND LINETYPE=2) tmp where tmp.rowNum=2),0) as DISCOUNT_AMOUNT2,
0 as DISCOUNT_AMOUNT3,
0 as DISCOUNT_AMOUNT4,
0 as DISCOUNT_AMOUNT5,
0 as DISCOUNT_AMOUNT6,
coalesce((select TOP 1 TMP.DISCPER FROM (select row_number() over(order by (select 0)) rowNum,DISCPER from LG_120_01_STLINE DETAIL_ where DETAIL_.INVOICEREF=INVOICE.LOGICALREF AND GLOBTRANS=1 AND LINETYPE=2) tmp where tmp.rowNum=1),0) AS DISCOUNT_OVERALL_PERCENTAGE1,
coalesce((select TOP 1 TMP.DISCPER FROM (select row_number() over(order by (select 0)) rowNum,DISCPER from LG_120_01_STLINE DETAIL_ where DETAIL_.INVOICEREF=INVOICE.LOGICALREF AND GLOBTRANS=1 AND LINETYPE=2) tmp where tmp.rowNum=2),0) AS DISCOUNT_OVERALL_PERCENTAGE2,
0 AS DISCOUNT_OVERALL_PERCENTAGE3,
0 AS DISCOUNT_OVERALL_PERCENTAGE4,
0 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,
case when INVOICE.TRCURR > 0 AND INVOICE.TRRATE >0 THEN INVOICE.TOTALDISCOUNTS / INVOICE.TRRATE ELSE INVOICE.TOTALDISCOUNTS END as DISCOUNT_AMOUNT_TOTAL,
case when INVOICE.TRCURR > 0 AND INVOICE.TRRATE >0 then INVOICE.GROSSTOTAL / INVOICE.TRRATE else INVOICE.GROSSTOTAL end as PRICE_AMOUNT_TOTAL,
NULL as TAX_AMOUNT_TOTAL,
case when INVOICE.TRCURR > 0 AND INVOICE.TRRATE >0 then (INVOICE.NETTOTAL / INVOICE.TRRATE) else INVOICE.NETTOTAL end as TOTAL_AMOUNT_INCLUDE_TAX,
case when INVOICE.TRCURR > 0 AND INVOICE.TRRATE >0 then (INVOICE.NETTOTAL / INVOICE.TRRATE) else INVOICE.NETTOTAL end as PAYABLE_AMOUNT,
CASE WHEN DETAIL.VAT=0 then DETAIL.VATEXCEPTREASON ELSE '' END as TAX_EXEMPTION_REASON,
ISNULL(REPLACE((select top 1 CURCODE from L_CURRENCYLIST where CURTYPE = INVOICE.TRCURR ),'TL','TRY') ,'TRY') as DOCUMENT_CURRENCY_CODE,
case when INVOICE.TRCURR = 0 AND INVOICE.TRRATE >0 then 1 else INVOICE.TRRATE end as EXCHANGE_RATE,
CASE WHEN DETAIL.DEDUCTIONPART1>0 AND DETAIL.DEDUCTIONPART2>0 THEN '9015' ELSE '' END as TAX_TYPE_CODE1,
CASE WHEN DETAIL.DEDUCTIONPART1>0 AND DETAIL.DEDUCTIONPART2>0 THEN ((DETAIL.DEDUCTIONPART1*100/DETAIL.DEDUCTIONPART2)) ELSE 0 END as TAX_TYPE_CODE1_PERCENTAGE,
0 as TAX_TYPE_CODE1_AMOUNT
FROM LG_120_01_INVOICE INVOICE
LEFT JOIN LG_120_01_STLINE DETAIL ON INVOICE.LOGICALREF = DETAIL.INVOICEREF
LEFT JOIN LG_120_01_ORFICHE ORD ON ORD.LOGICALREF = DETAIL.ORDFICHEREF
LEFT JOIN LG_120_ITEMS ITEM ON DETAIL.STOCKREF = ITEM.LOGICALREF
LEFT JOIN LG_120_SRVCARD ITEMHIZ ON DETAIL.STOCKREF=ITEMHIZ.LOGICALREF
LEFT JOIN LG_120_CLCARD CUSTOMER ON CUSTOMER.LOGICALREF = INVOICE.CLIENTREF
LEFT JOIN LG_120_01_STFICHE DISPATCH ON DISPATCH.LOGICALREF=DETAIL.STFICHEREF
LEFT JOIN LG_120_UNITSETL UNIT ON DETAIL.USREF=UNIT.UNITSETREF AND DETAIL.UOMREF=UNIT.LOGICALREF
LEFT JOIN LG_120_PAYPLANS PAYMENT ON INVOICE.PAYDEFREF = (CASE WHEN INVOICE.PAYDEFREF=0 THEN ( SELECT TOP 1 PAYMENT.LOGICALREF ) ELSE PAYMENT.LOGICALREF END)
LEFT JOIN LG_120_PAYLINES PAYLINES ON PAYLINES.PAYPLANREF=PAYMENT.LOGICALREF WHERE INVOICE.TRCODE IN (8,9,7,3,6) AND LINETYPE IN (0,1,4) AND INVOICE.CANCELLED=0 ) AS Data WHERE 1=1