Adding BP Code (where applicable) to a SAP B1 query

[Keith_M]

Hi all,

I have begun trying to write my first SAP B1 query, which I ultimately want to replace the standard General Ledger report with additional, relevant outputs that will assist my analysis of the data.  So far I have written the following code based on the JDT1 TransTypes that I have seen in use:

SELECT	T0."Account" AS "GL Code",
	T1."AcctName" AS "GL Description",
	CONCAT(CONCAT(T1."AcctCode",' - '),T1."AcctName") AS "GL Code & Description",
	T0."ProfitCode" AS "Department",
	T2."OcrName" AS "Department Name",
	T0."RefDate" AS "Posting Date",
	CASE
	WHEN T0."TransType" = '-3' THEN 'Period Closing Journal'
	WHEN T0."TransType" = '-2' THEN 'Opening Balance Journal'
	WHEN T0."TransType" = '13' THEN 'Sales Invoice'
	WHEN T0."TransType" = '14' THEN 'Sales Credit Note'
	WHEN T0."TransType" = '15' THEN 'Delivery Note'
	WHEN T0."TransType" = '16' THEN 'Customer Return'
	WHEN T0."TransType" = '18' THEN 'Purchase Invoice'
	WHEN T0."TransType" = '19' THEN 'Purchase Credit Note'
	WHEN T0."TransType" = '20' THEN 'Goods Receipt PO'
	WHEN T0."TransType" = '21' THEN 'Supplier Goods Return'
	WHEN T0."TransType" = '24' THEN 'Cash Receipt'
	WHEN T0."TransType" = '30' THEN 'Journal'
	WHEN T0."TransType" = '46' THEN 'Cash Payment'
	WHEN T0."TransType" = '59' THEN 'Goods Receipt'
	WHEN T0."TransType" = '60' THEN 'Goods Issue'
	WHEN T0."TransType" = '67' THEN 'Stock Transfer'
	WHEN T0."TransType" = '162' THEN 'Stock Revaluation'
	WHEN T0."TransType" = '202' THEN 'Production Order'
	WHEN T0."TransType" = '203' THEN 'Customer Down Payment'
	WHEN T0."TransType" = '321' THEN 'Manual Reconciliation Journal'
	WHEN T0."TransType" = '10000071' THEN 'Stock Difference Adjustment'
	WHEN T0."TransType" = '1470000049' THEN 'Asset Capitalisation'
	WHEN T0."TransType" = '1470000071' THEN 'Depreciation Run'
	WHEN T0."TransType" = '1470000094' THEN 'Asset Retirement'
	ELSE CONCAT(T0."TransType",' - PLEASE RESEARCH & UPDATE QUERY')
	END AS "Doc Type",
	CASE
	WHEN T0."TransType" = '-3' THEN 'BC'
	WHEN T0."TransType" = '-2' THEN 'OB'
	WHEN T0."TransType" = '13' THEN 'IN'
	WHEN T0."TransType" = '14' THEN 'CN'
	WHEN T0."TransType" = '15' THEN 'DN'
	WHEN T0."TransType" = '16' THEN 'RE'
	WHEN T0."TransType" = '18' THEN 'PU'
	WHEN T0."TransType" = '19' THEN 'PC'
	WHEN T0."TransType" = '20' THEN 'PD'
	WHEN T0."TransType" = '21' THEN 'PR'
	WHEN T0."TransType" = '24' THEN 'RC'
	WHEN T0."TransType" = '30' THEN 'JE'
	WHEN T0."TransType" = '46' THEN 'PS'
	WHEN T0."TransType" = '59' THEN 'SI'
	WHEN T0."TransType" = '60' THEN 'SO'
	WHEN T0."TransType" = '67' THEN 'IM'
	WHEN T0."TransType" = '162' THEN 'MR'
	WHEN T0."TransType" = '202' THEN 'PW'
	WHEN T0."TransType" = '203' THEN 'DT'
	WHEN T0."TransType" = '321' THEN 'JR'
	WHEN T0."TransType" = '10000071' THEN 'ST'
	WHEN T0."TransType" = '1470000049' THEN 'AC'
	WHEN T0."TransType" = '1470000071' THEN 'DR'
	WHEN T0."TransType" = '1470000094' THEN 'RT'
	ELSE CONCAT(T0."TransType",' - PLEASE RESEARCH & UPDATE QUERY')
	END AS "Doc Prefix",
	CASE
	WHEN T0."TransType" IN('-3','-2','30') THEN T0."BaseRef"
	ELSE T0."Ref1"
	END AS "Doc Ref",
	T0."Debit" - T0."Credit" AS "Debit / (Credit)",
FROM	JDT1 T0
	INNER JOIN OACT T1 ON T0."Account" = T1."AcctCode"
	LEFT JOIN OOCR T2 ON T0."ProfitCode" = T2."OcrCode"
WHERE	T0."RefDate" >=[%0]
	AND T0."RefDate" <=[%1]
SORT BY	T0."Account", T0."RefDate"

This seems to work ok so far, but now I want to add another column to return the BP Code wherever applicable.  Below is an example of the coding I was trying to add:

SELECT	CASE
	WHEN T0."TransType" = '-3' THEN 'N/A'
	WHEN T0."TransType" = '-2' AND SUBSTRING(T0."ShortName",1,1) IN('C','S') THEN T0."ShortName"
	WHEN T0."TransType" = '-2' AND SUBSTRING(T0."ContraAct",1,1) IN('C','S') THEN T0."ContraAct"
	WHEN T0."TransType" = '13' AND T3."DocNum" = T0."Ref1" THEN T3."CardCode"
	WHEN T0."TransType" = '14' AND T4."DocNum" = T0."Ref1" THEN T4."CardCode"
	WHEN T0."TransType" = '15' AND T5."DocNum" = T0."Ref1" THEN T5."CardCode"
	WHEN T0."TransType" = '16' AND T6."DocNum" = T0."Ref1" THEN T6."CardCode"
FROM	LEFT JOIN OINV T3 ON T0."TransType" = '13' AND T0."Ref1" = T3."DocNum"
	LEFT JOIN ORIN T4 ON T0."TransType" = '14' AND T0."Ref1" = T4."DocNum"
	LEFT JOIN ODLN T5 ON T0."TransType" = '15' AND T0."Ref1" = T5."DocNum"
	LEFT JOIN ORDN T6 ON T0."TransType" = '16' AND T0."Ref1" = T6."DocNum"
	ELSE CONCAT(T0."TransType",' - PLEASE RESEARCH & UPDATE QUERY')
	END AS "BP Code"

This is where things start to fall apart, and sometimes my query returns no results, depending on the period selected.  I am sure as a novice I must be going about this all the wrong way.  Can anyone advise why either I am taking the wrong approach, or if this approach is sensible what I am doing wrong?

Many thanks in advance.

Keith