Notes

SELECT * FROM (SELECT
DESPATCH.LOGICALREF AS ERP_INVOICE_ID,
ISNULL(DESPATCH.FICHENO, '') AS ERP_INVOICE_DOCUMENT_NO,
'' 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,
'TEMELIRSALIYE' AS PROFILE_ID,
'SEVK' AS INVOICE_TYPE_CODE,
DESPATCH.FICHENO AS DESPATCH_NUMBER,
DESPATCH.DATE_ AS ISSUE_DATE,
ORD.FICHENO AS ORDER_NUMBER,
ORD.DATE_ ORDER_DATE,
DATEADD(DAY,((STR(SUBSTRING(PAYLINES.DAY_,2,3)))*1),DESPATCH.DATE_) AS PAYMENT_DUE_DATE,
CASE WHEN DESPATCH.TRCODE=9 THEN ITEMHIZ.LOGICALREF ELSE ITEM.LOGICALREF END AS ITEM_ID,
CASE WHEN DESPATCH.TRCODE=9 THEN ITEMHIZ.DEFINITION_ ELSE ITEM.NAME END AS ITEM_NAME,
DETAIL.AMOUNT AS QUANTITY,
UNIT.CODE AS UNIT_CODE,
DETAIL.PRICE AS QUANTITY_AMOUNT,
DETAIL.VAT AS TAX_PERCENTAGE,
DETAIL.VATAMNT as TAX_AMOUNT,
'' AS ITEM_NOTE,
'' AS ITEM_DESCRIPTION,
CASE WHEN DESPATCH.TRCODE=9 THEN ITEMHIZ.CODE ELSE ITEM.CODE END AS ITEM_SELLER_DESCRIPTION,
'' as ITEM_MANUFACTURER_DESCRIPTION,
ISNULL(DETAIL.LINEEXP,'') as ITEM_BUYER_DESCRIPTION,
'' as MUSTERI_NO,
ISNULL(DESPATCH.GENEXP1, '') AS NOTE1,
ISNULL(DESPATCH.GENEXP2, '') AS NOTE2,
ISNULL(DESPATCH.GENEXP3, '') AS NOTE3,
ISNULL(DESPATCH.GENEXP4, '') as NOTE4,
'' as NOTE5,
'' as TAX_EXEMPTION_REASON,
L_SHPAGENT.NAME + ' ' +L_SHPAGENT.SURNAME AS DRIVER_NAME,

L_SHPAGENT.TCNO AS DRIVER_TCKN,

'' AS CARRIER_PLATE,

'' AS CARRIER_DORSE_PLATE,

L_SHPAGENT.TAXNR AS CARRIER_VKN,

L_SHPAGENT.DEFINITION_ AS CARRIER_NAME,

L_SHPAGENT.ADDR1 as CARRIER_DISTRICT,

L_SHPAGENT.CITY as CARRIER_CITY,
'TRY' as DOCUMENT_CURRENCY_CODE,
0 as ACTUAL_PACKAGE_QUANTITY,
'1' as EXCHANGE_RATE
FROM LG_120_01_STFICHE DESPATCH
LEFT JOIN LG_120_01_STLINE DETAIL ON DESPATCH.LOGICALREF = DETAIL.STFICHEREF
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 = DESPATCH.CLIENTREF
LEFT JOIN L_SHPAGENT (NOLOCK) ON L_SHPAGENT.CODE=DESPATCH.SHPAGNCOD
LEFT JOIN LG_120_UNITSETL UNIT ON DETAIL.USREF=UNIT.UNITSETREF AND DETAIL.UOMREF=UNIT.LOGICALREF
LEFT JOIN LG_120_PAYPLANS PAYMENT ON DESPATCH.PAYDEFREF = (CASE WHEN DESPATCH.PAYDEFREF=0 THEN ( SELECT TOP 1 PAYMENT.LOGICALREF ) ELSE PAYMENT.LOGICALREF END)
LEFT JOIN LG_120_PAYLINES PAYLINES ON PAYLINES.PAYPLANREF=PAYMENT.LOGICALREF
WHERE DESPATCH.TRCODE IN (8,9,6) AND LINETYPE IN (0,1,4) ) AS Data WHERE 1=1