SELECT CMA.InvoiceNum,
       M2M.InvoiceNumber,
       CMA.fsono,
       CMA.fprodcl,
       CMA.fpartno,
       CMA.frev,
       CMA.fcustno,
       CMA.fccompany,
       CMA.ftotprice,
       CMA.fnmatlcost,
       CMA.fnlabcost,
       CMA.fnovhdcost,
       CMA.fnothrcost
  FROM    cheltoncustomizations.dbo.ComantAnalysis1 CMA
       full OUTER JOIN
          (SELECT right(AM.fcinvoice, 5) AS 'InvoiceNumber',
                  AM.finvdate AS 'InvoiceDate',
                  --       SOM.fsono AS 'OrderNumber',
                  AM.fpono AS 'ContractNumber',
                  AI.FPRODCL AS 'ProductType',
                  AI.fpartno AS 'SKUNumber',
                                   /*      AI.frev AS 'ProductDescription',
                        SL.fcustno AS 'CustomerID',
                        AM.fccompany AS 'CustomerName',
                  */
                  SL.fcusrchr1 AS 'CustomerClass',
                  SM.fccity AS 'DeliveryCity',
                  SM.fcstate AS 'DeliveryState',
                  SM.fczip AS 'DeliveryZip',
                  AM.fsalespn AS 'SalesRep',
                                /*
                     ' ' AS 'AccountRepName',
                     ' ' AS 'BranchName',
                     ' ' AS 'BranchNumber',
                     AI.ftotprice AS 'ListPrice',
                     ' ' AS 'TierPrice',
                     AI.ftotprice AS 'InvoicePrice', */
                  (CASE SOM.fterm
                      WHEN 'A' THEN 'NET 30'
                      WHEN 'I' THEN 'NET 60'
                      WHEN 'COD' THEN 'COD'
                      WHEN '0189' THEN 'NET 15'
                      WHEN 'C004' THEN '1% 15, NET 30'
                      WHEN '0191' THEN '1/2% 15, NET 45'
                      WHEN '0170' THEN 'DUE NOW'
                      WHEN '0187' THEN 'NOT A CUSTOMER YET'
                      WHEN 'D' THEN 'NET 45'
                      WHEN 'B' THEN 'NET 10'
                      WHEN 'J' THEN 'PREPAID'
                      WHEN 'G' THEN '1/2% 15, NET 30'
                      WHEN '0192' THEN 'NET 90'
                      ELSE SOM.fterm
                   END)
                     AS 'PaymentTerms',
                               /*
                    ' ' AS 'DeductionAmount',
                    ' ' AS 'DeductionType',
                    ' ' AS 'ExceptionFlag',
                    (SOR.fmatlcost/SOR.forderqty)*AI.fshipqty AS 'MaterialCost',
                    (sor.ftoolcost/SOR.forderqty)*AI.fshipqty AS 'ToolingCost',
                    (sor.flabcost/SOR.forderqty)*AI.fshipqty AS 'LaborCost',
                    (sor.fovhdcost/SOR.forderqty)*AI.fshipqty AS 'OverheadCost',
                    (sor.fsubcost/SOR.forderqty)*AI.fshipqty AS 'SubContractCost',
                    (sor.fothrcost/SOR.forderqty)*AI.fshipqty AS 'OtherCosts',
                    (sor.fsetupcost/SOR.forderqty)*AI.fshipqty AS 'SetUpCost',
                    (SOR.fmatlcost
                   + sor.ftoolcost
                   + sor.flabcost
                   + sor.fovhdcost
                   + sor.fsubcost
                   + sor.fothrcost
                   + sor.fsetupcost)
                       AS 'DirectProductCost',
                    ' ' AS 'FreightCost', */
                  AM.ffreight AS 'FreightCharged'
                   /*' ' AS 'WarrantyCosts',
             ' ' AS 'TechSupport',
             ' ' AS 'OtherOperatingCosts'*/
             FROM                dbo.slcdpm SL
                              INNER JOIN
                                 dbo.armast AM
                              ON AM.fcustno = SL.fcustno
                           INNER JOIN
                              dbo.aritem AI
                           ON AM.fcinvoice = AI.fcinvoice
                        LEFT OUTER JOIN
                           dbo.somast SOM
                        ON left(AI.fsokey, 6) = SOM.fsono
                     LEFT OUTER JOIN
                        dbo.shmast SM
                     ON left(AI.fshipkey, 6) = SM.fshipno
                  LEFT OUTER JOIN
                     dbo.sorels SOR
                  ON AI.fsokey = sor.fsono + sor.finumber + sor.frelease
            WHERE SL.ftype = 'C' AND year(AM.finvdate) >= 2006
              AND rtrim(ltrim(AM.fcinvoice)) LIKE
                     '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]')
          M2M
       ON CMA.InvoiceNum = M2M.InvoiceNumber