CREATE VIEW "SBODEMODE"."SBO_SALES_SO" ( "DOCNUM", "LINENUM", "CANCELLED", "ITEMCODE", "CARDCODE", "SALESPERSON", "DOCDATE", "UPDATEDATE", "ORDERVALUE", "ORDERQUANTITY", "OPENORDQUANT", "GROSSBYEPR", "CURRENCY", "BASEUNIT" ) AS SELECT     ORDR."DocNum" as "DOCNUM", RDR1."LineNum" as "LINENUM", ORDR."CANCELED" AS "CANCELLED", RDR1."ItemCode" as "ITEMCODE", 
             ORDR."CardCode" as "CARDCODE",
             TO_int(ORDR."SlpCode") AS "SALESPERSON",

	TO_CHAR(YEAR(ORDR."DocDate")) || CASE LENGTH(MONTH(ORDR."DocDate")) 
              WHEN 1 THEN '0' || TO_CHAR(MONTH(ORDR."DocDate")) ELSE TO_CHAR(MONTH(ORDR."DocDate")) 
              END 
              || CASE LENGTH(EXTRACT(DAY FROM ORDR."DocDate")) WHEN 1 THEN '0' || TO_CHAR(EXTRACT(DAY FROM ORDR."DocDate"))
              ELSE TO_CHAR(EXTRACT(DAY FROM ORDR."DocDate")) END AS "DOCDATE",  

           TO_CHAR(YEAR(ORDR."UpdateDate"))
       || case LENGTH(MONTH(ORDR."UpdateDate"))
          when 1 then '0' || TO_CHAR(MONTH(ORDR."UpdateDate"))
          else TO_CHAR(MONTH(ORDR."UpdateDate"))
         end
      || case LENGTH(EXTRACT(DAY FROM ORDR."UpdateDate"))
          when 1 then '0' || TO_CHAR(EXTRACT(DAY FROM ORDR."UpdateDate"))
          else TO_CHAR(EXTRACT(DAY FROM ORDR."UpdateDate"))
        end  AS "UPDATEDATE",
        
           ROUND ( RDR1."LineTotal" * (1- ORDR."DiscPrcnt"/ 100), 2) AS "ORDERVALUE", 
          
           CASE RDR1."UseBaseUn" 
          WHEN 'Y' THEN RDR1."Quantity" 
          WHEN 'N' THEN RDR1."Quantity" * OITM."NumInSale"
          END  AS "ORDERQUANTITY", 
         
          CASE RDR1."UseBaseUn" 
          WHEN 'Y' THEN RDR1."OpenQty"
          WHEN 'N' THEN RDR1."OpenQty" * OITM."NumInSale"
          END  AS "OPENORDQUANT", 

      RDR1."GrossBuyPr" AS "GROSSBYEPR", 
      CASE (SELECT Top 1 "MainCurncy" FROM "SBODEMODE"."OADM")
          WHEN 'Eur' THEN 'EUR'
          WHEN '$' THEN 'USD'    
          WHEN '€' THEN 'EUR'
          ELSE (SELECT Top 1 "MainCurncy" FROM "SBODEMODE"."OADM") 
      END AS "CURRENCY", 
 
     CASE OITM."SalUnitMsr"
          WHEN 'Centimeter' THEN 'CM'
          WHEN 'CM' THEN 'CM'
          WHEN 'Gram' THEN 'G'
          WHEN 'G' THEN 'G'
          WHEN 'Kilogram' THEN 'KG'
          WHEN 'KG' THEN 'KG'
          WHEN 'Liter' THEN 'L'
          WHEN 'L' THEN 'L'
          WHEN 'Meter' THEN 'M'
          WHEN 'M' THEN 'M'
          WHEN 'Tonnes' THEN 'TO'
          WHEN 'Ton' THEN 'TO'
          WHEN 'TO' THEN 'TO'
          ELSE 'PC'
      End AS "BASEUNIT"

FROM         "SBODEMODE"."RDR1" INNER JOIN
                      "SBODEMODE"."ORDR" ON RDR1."DocEntry" = ORDR."DocEntry" INNER JOIN
                      "SBODEMODE"."OITM" ON RDR1."ItemCode" = OITM."ItemCode" WITH READ ONLY