custtable.CUSTGROUP [sales type],
salestable.SALESSTATUS [Order Status],
convert(date,salestable.CREATEDDATETIME) [Date],
THEN convert(varchar(4), YEAR(salestable.CREATEDDATETIME)-1) + '-' + convert(varchar(4), YEAR(salestable.CREATEDDATETIME)%100)
ELSE convert(varchar(4),YEAR(salestable.CREATEDDATETIME))+ '-' + convert(varchar(4),(YEAR(salestable.CREATEDDATETIME)%100)+1)END AS Fiscal_Year,
salestable.INVENTSITEID [Site/Location],
salestable.INVENTLOCATIONID [Warehouse],
salestable.SALESID [Sales ID],
salesline.LINEAMOUNT ,
salesline.CURRENCYCODE [Currency],
when salesline.CURRENCYCODE = 'INR' then salesline.LINEAMOUNT
when salesline.CURRENCYCODE = 'SGD' then salesline.LINEAMOUNT * 54
when salesline.CURRENCYCODE = 'USD' then salesline.LINEAMOUNT * 72 end [Amount],
dirpartytable.NAME [Customer],
LOGISTICSPOSTALADDRESS.DISTRICTNAME [District]
left join SALESTABLE salestable on custtable.ACCOUNTNUM = salestable.CUSTACCOUNT
left join DIRPARTYTABLE dirpartytable on custtable.PARTY = dirpartytable.RECID
left join SALESLINE salesline on salestable.SALESID = salesline.SALESID
--left join dirpartylocation dirpartylocation on custtable.PARTY = dirpartylocation.PARTY
left join LOGISTICSPOSTALADDRESS logisticspostaladdress on salestable.DELIVERYPOSTALADDRESS = logisticspostaladdress.RECID
left join LOGISTICSADDRESSCOUNTRYREGIONTRANSLATION logisticsaddresscountryregiontranslation on logisticspostaladdress.COUNTRYREGIONID = logisticsaddresscountryregiontranslation.COUNTRYREGIONID
SALESTABLE.DOCUMENTSTATUS in (3)