SELECT
  JL.CMSJournalNo,
  JL.CMSJournalLineNo,
  JL.CMSInvNo,
  JL.ItemNumber,
  JL.LineAmount,
  JL.CMSLineNo,
  JL.Description,
  CASE JH.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 JH.AccountBookID
  END AS Location,

  -- Debit
  CASE JL.DebitAccount

    -- Revenue
    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

    -- Credit note
    WHEN '1099-CAC'   THEN '109901'  -- C/N ANNUAL SUBSCRIPTION: CANCELLATION
    WHEN '1099-CAD'   THEN '109902'  -- CREDIT NOTES - LATE CANCELLATIONS
    WHEN '1099-CAO'   THEN '109903'  -- C/N ANNUAL SUBSCRIPTION: CHANGED TO MONTHLY
    WHEN '1099-CBA'   THEN '109904'  -- CREDIT NOTES - BARTER AGREEMENTS
    WHEN '1099-CBI'   THEN '109905'  -- C/N ADHOC - WRONG BILLING RE-INVOICED
    WHEN '1099-CC'    THEN '109906'  -- ADHOC: CLIENT CANCELLED TRAINING
    WHEN '1099-CDA'   THEN '109907'  -- C/N DISCOUNT ALLOWED
    WHEN '1099-CRA'   THEN '109909'  -- CREDIT NOTES - CANCELLATIONS / REFUNDS
    WHEN '1099-CRI'   THEN '109910'  -- CREDIT NOTES - WRONG BILLING & RE-INVOICE
    WHEN '1099-CRM'   THEN '109911'  -- CREDIT NOTES - MANUAL DECREASE / INCREASE ADJUSTMENT
    WHEN '1099-CRS'   THEN '109912'  -- C/N ADHOC - WRONG BILLING AS SUBSCRIPTION SERVICE COVER
    WHEN '1099-CRT'   THEN '109913'  -- C/N ADHOC: WRONG BILLING WRONG ACCOUNT
    WHEN '1099-CWP'   THEN '109914'  -- C/N ADHOC - WRONG BILLING WRONG PRICE
    WHEN '1099-PHD'   THEN '109915'  -- C/N SUBSCRIPTION: PAYMENT HOLIDAY BATCH 20
    WHEN '1099-RSF'   THEN '109916'  -- C/N SUBSCRIPTION SERVICE FAILURE
    WHEN '1096-ED'    THEN '109601'  -- CREDIT NOTES ED SUBSCRIPTION
    WHEN '1097-SED'   THEN '109701'  -- CREDIT NOTES SED SUBSCRIPTION
    WHEN '4001-AD'    THEN '400101'  -- C/N BAD DEBT ACTUAL WRITE-OFFS: HANDED OVER
    WHEN '4001-ADNHO' THEN '400102'  -- C/N BAD DEBT ACTUAL WRITE OFFS: NOT HANDED OVER
    ELSE JL.DebitAccount
  END AS DebitAccount,
  CASE JL.DebitBranchRegion
    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 JL.DebitBranchRegion
  END AS DebitBranchRegion,
  CASE JL.DebitProductCategory
    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 '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 'IR'   THEN '57'  -- Labour Law
    WHEN 'LWO'  THEN '62'  -- IR Support
    WHEN 'LLAW' THEN '61'  -- LLAW Support
    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 '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 'CTRL' THEN '106'  -- Merged Product Control
    ELSE JL.DebitProductCategory
  END AS DebitProductCategory,
  CASE JL.DebitTaxType
    WHEN 'ZERORATED'    THEN '3'  -- Zero Rate
    WHEN 'EXEMPTOUTPUT' THEN '5'  -- Exempt
    WHEN 'BADDEBT'      THEN '8'  -- Bad Debts
    ELSE '1'                      -- Standard Rate Output
  END AS DebitTaxType,

  -- Credit
  CASE JL.CreditAccount

    -- Revenue
    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

    -- Credit note
    WHEN '1099-CAC'   THEN '109901'  -- C/N ANNUAL SUBSCRIPTION: CANCELLATION
    WHEN '1099-CAD'   THEN '109902'  -- CREDIT NOTES - LATE CANCELLATIONS
    WHEN '1099-CAO'   THEN '109903'  -- C/N ANNUAL SUBSCRIPTION: CHANGED TO MONTHLY
    WHEN '1099-CBA'   THEN '109904'  -- CREDIT NOTES - BARTER AGREEMENTS
    WHEN '1099-CBI'   THEN '109905'  -- C/N ADHOC - WRONG BILLING RE-INVOICED
    WHEN '1099-CC'    THEN '109906'  -- ADHOC: CLIENT CANCELLED TRAINING
    WHEN '1099-CDA'   THEN '109907'  -- C/N DISCOUNT ALLOWED
    WHEN '1099-CRA'   THEN '109909'  -- CREDIT NOTES - CANCELLATIONS / REFUNDS
    WHEN '1099-CRI'   THEN '109910'  -- CREDIT NOTES - WRONG BILLING & RE-INVOICE
    WHEN '1099-CRM'   THEN '109911'  -- CREDIT NOTES - MANUAL DECREASE / INCREASE ADJUSTMENT
    WHEN '1099-CRS'   THEN '109912'  -- C/N ADHOC - WRONG BILLING AS SUBSCRIPTION SERVICE COVER
    WHEN '1099-CRT'   THEN '109913'  -- C/N ADHOC: WRONG BILLING WRONG ACCOUNT
    WHEN '1099-CWP'   THEN '109914'  -- C/N ADHOC - WRONG BILLING WRONG PRICE
    WHEN '1099-PHD'   THEN '109915'  -- C/N SUBSCRIPTION: PAYMENT HOLIDAY BATCH 20
    WHEN '1099-RSF'   THEN '109916'  -- C/N SUBSCRIPTION SERVICE FAILURE
    WHEN '1096-ED'    THEN '109601'  -- CREDIT NOTES ED SUBSCRIPTION
    WHEN '1097-SED'   THEN '109701'  -- CREDIT NOTES SED SUBSCRIPTION
    WHEN '4001-AD'    THEN '400101'  -- C/N BAD DEBT ACTUAL WRITE-OFFS: HANDED OVER
    WHEN '4001-ADNHO' THEN '400102'  -- C/N BAD DEBT ACTUAL WRITE OFFS: NOT HANDED OVER
    ELSE JL.CreditAccount
  END AS CreditAccount,
  CASE JL.CreditBranchRegion
    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 JL.CreditBranchRegion
  END AS CreditBranchRegion,
  CASE JL.CreditProductCategory
    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 '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 'IR'   THEN '57'  -- Labour Law
    WHEN 'LWO'  THEN '62'  -- IR Support
    WHEN 'LLAW' THEN '61'  -- LLAW Support
    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 '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 'CTRL' THEN '106'  -- Merged Product Control
    ELSE JL.CreditProductCategory
  END AS CreditProductCategory,
  CASE JL.CreditTaxType
    WHEN 'ZERORATED'    THEN '3'  -- Zero Rate
    WHEN 'EXEMPTOUTPUT' THEN '5'  -- Exempt
    WHEN 'BADDEBT'      THEN '8'  -- Bad Debts
    ELSE '1'                      -- Standard Rate Output
  END AS CreditTaxType
FROM
  taManualJournalLine AS JL
    LEFT JOIN taManualJournalHeader AS JH ON (JH.CMSJournalNo = JL.CMSJournalNo)
    LEFT JOIN taInvoiceHeader AS IH ON (IH.CMSInvNo = JH.CMSInvNo)
WHERE
  (JH.AccountBookType = 'Sage') AND
  ((IH.CMSInvNo IS NULL) OR (IH.Updated = 1)) AND
  ((IH.CMSInvNo IS NULL) OR (IH.DocumentNumber <> 'VOID')) AND
  (JH.Updated = 0)
  @pFilter
ORDER BY
  JL.CMSJournalNo,
  JL.CMSJournalLineNo