SELECT
  IL.CMSInvNo,
  IL.CMSLineNo,
  IL.DocumentNumber,
  IH.AppointmentID,
  CASE IH.AccountBookID
    WHEN 1 THEN '2'  -- LC--Labournet Central (Pty) Ltd
    WHEN 2 THEN '7'  -- SSI--Sean Snyman Incorporated
    WHEN 3 THEN '6'  -- LPS--Labournet Payment Solutions (Pty) Ltd
    WHEN 6 THEN '8'  -- PSI--Psiber Solutions (Pty) Ltd
    WHEN 7 THEN '3'  -- JVEC--Labournet Eastern Cape (Pty) Ltd
    WHEN 8 THEN '4'  -- JVR--Labournet Rustenburg (Pty) Ltd
    WHEN 9 THEN '5'  -- JVSD--Strauss Daly Labournet (Pty) Ltd
    ELSE IH.AccountBookID
  END AS Location,
  CASE IL.GLAccount
    WHEN '0001-CTRL' THEN '000101'  -- SUBSCRIPTION REVENUE CONTROL
    WHEN '1001-RT'   THEN '100103'  -- REVENUE SUBSCRIPTIONS
    WHEN '1001-RA'   THEN '100101'  -- REVENUE SUBSCRIPTION ANNUAL
    WHEN '1002-AD'   THEN '100201'  -- ADHOC REVENUE
    WHEN '1004-ATT'  THEN '100401'  -- ADHOC TT
    WHEN '1005-ATD'  THEN '100501'  -- ADHOC TD
    WHEN '1006-BUR'  THEN '100603'  -- REVENUE SAAS PSIBER
    WHEN '1006-SLP'  THEN '100604'  -- REVENUE SAAS PSIBER
    WHEN '9782-CL'   THEN '800032'  -- DEFERRED REVENUE (NEW)
    WHEN '1000-INT'  THEN '100001'  -- INTERNAL REVENUE
    WHEN '4699-INT'  THEN '469901'  -- OTHER EXPENSES - INTERNAL SERVICE COSTS
    ELSE IL.GLAccount
  END AS GLAccount,
  IL.Description,
  CASE IL.BranchRegion
    WHEN 'BAL' THEN '24'  -- Ballito
    WHEN 'BET' THEN '15'  -- Bethlehem
    WHEN 'BFN' THEN '16'  -- Bloemfontein
    WHEN 'BTS' THEN '31'  -- Brits
    WHEN 'CPT' THEN '37'  -- Cape Town
    WHEN 'DUR' THEN '25'  -- Durban
    WHEN 'ELN' THEN '10'  -- East London
    WHEN 'GEO' THEN '38'  -- George
    WHEN 'HDS' THEN '29'  -- Hoedsptruit
    WHEN 'HOF' THEN '9'   -- Head Office
    WHEN 'JNO' THEN '21'  -- Johannesburg
    WHEN 'KIM' THEN '17'  -- Kimberly
    WHEN 'KTH' THEN '18'  -- Kathu
    WHEN 'NAT' THEN '9'   -- Head Office (repeat)
    WHEN 'NLS' THEN '30'  -- Nelspruit
    WHEN 'OUT' THEN '9'   -- Head Office (repeat)
    WHEN 'PEL' THEN '13'  -- Port Elizabeth
    WHEN 'PLK' THEN '34'  -- Polokwane
    WHEN 'PMB' THEN '26'  -- Pietermaritzburg
    WHEN 'PRS' THEN '9'   -- Head Office (repeat)
    WHEN 'PSI' THEN '43'  -- Psiber
    WHEN 'PSL' THEN '43'  -- Psiber (repeat)
    WHEN 'PST' THEN '22'  -- Potchefstroom
    WHEN 'PTA' THEN '35'  -- Pretoria
    WHEN 'RNB' THEN '21'  -- Johannesburg (repeat)
    WHEN 'RUS' THEN '32'  -- Rustenburg
    WHEN 'SDT' THEN '21'  -- Johannesburg (repeat)
    WHEN 'SNY' THEN '9'   -- Head Office (repeat)
    WHEN 'TBZ' THEN '33'  -- Thabazimbi
    WHEN 'WTB' THEN '36'  -- Witbank
    WHEN 'ZUL' THEN '28'  -- Zululand
    ELSE IL.BranchRegion
  END AS Department,
  CASE IL.ProductCategory
--    WHEN 'ADH' THEN ''  -- Adhoc
--    WHEN 'AC' THEN ''   -- Academy
    WHEN 'AFC'  THEN '89'  -- Affiliate Commisions
    WHEN 'AFTL' THEN '75'  -- Affiliate Training Lite
    WHEN 'AFTP' THEN '76'  -- Affiliate Training Premium
    WHEN 'BEE'  THEN '77'  -- B-BBEE Consulting
    WHEN 'CIMP' THEN '63'  -- Central Implementation
    WHEN 'CGOV' THEN '49'  -- CGOV Open Cover
    WHEN 'CLAW' THEN '50'  -- Commercial Law
--    WHEN 'CA' THEN ''  -- Corporate Account Management
    WHEN 'SE'   THEN '83'  -- Employment Equity
    WHEN 'OHS'  THEN '54'  -- Health and Safety
    WHEN 'HSF'  THEN '64'  -- Hosting Fee
    WHEN 'HR'   THEN '79'  -- Skills Developemnt
    WHEN 'IMP'  THEN '65'  -- Implementation
--    WHEN 'OTH' THEN ''  -- Internal
    WHEN 'IR'   THEN '57'  -- Labour Law
--    WHEN 'IRH' THEN ''  -- IR Helpdesk
    WHEN 'LWO'  THEN '62'  -- IR Support
    WHEN 'LLAW' THEN '61'  -- LLAW Support
--    WHEN 'CEN' THEN ''  -- LabourNet
--    WHEN 'LS' THEN ''  -- Learnership
    WHEN 'LIT'  THEN '59'  -- Litigation
    WHEN 'OUT'  THEN '67'  -- Outsource
    WHEN 'OUTS' THEN '68'  -- Outsource Software
    WHEN 'PAY'  THEN '69'  -- Payroll
    WHEN 'PAYS' THEN '71'  -- Payroll Software
    WHEN 'POP'  THEN '51'  -- POPIA
--    WHEN 'REC' THEN ''  -- Recruitment
    WHEN 'SAAS' THEN '82'  -- SaaS                    WRONG!!!!
    WHEN 'SAAST' THEN '82'  -- SaaS Training
    WHEN 'SE'   THEN '83'  -- Skills Development
    WHEN 'TRN'  THEN '84'  -- Training
    WHEN 'TRN-ME' THEN '85'  -- Training Mall External
    WHEN 'TRN-MI' THEN '85'  -- Training Mall Internal
--    WHEN 'VAP-ME' THEN ''  -- VAP Mall External
--    WHEN 'VAP-MI' THEN ''  -- VAP Mall Internal
    WHEN 'CTRL' THEN '106'  -- Merged Product Control
    ELSE IL.ProductCategory
  END AS Class,
  CASE IH.TaxTypeID
    WHEN  6 THEN '3'  -- Zero Rate
    WHEN  7 THEN '5'  -- Exempt
    WHEN 17 THEN '8'  -- Bad Debts
    ELSE '1'          -- Standard Rate Output
  END AS TaxKey,
  IL.TaxRate AS VATRate,
  (IL.TaxRate * 100.0) AS VATRatePercentage,
  IL.LineQuantity,
  IL.UnitPriceExcl,
  IL.UnitPriceIncl,
  IL.ExclusivePrice,
  (IL.ExclusivePrice * IL.TaxRate) AS VAT,
  IL.InclusivePrice
FROM
  taInvoiceLine AS IL
    INNER JOIN taInvoiceHeader AS IH ON (IH.CMSInvNo = IL.CMSInvNo)
WHERE
  1
  @pCMSInvNo
ORDER BY
  IL.CMSInvNo,
  IL.CMSLineNo
