MYOB Exo – Some Standard Keys

One of the things I struggle with is how hard it can be to find Keys for what various fields in Exo mean.

Behind the cut, scripts for adding keys to:
SALESORD_HIST Event keys
PURCHORD_HIST Event keys
GL_TRANS Source Types keys
Fixed Asset ASSETSTAT key
And a view for translating different types of Accounts. (Technically the Companies view already does this, but it does it via number, not the easier for me G, C and D types.)

if not exists (select 1 from sys.tables where name='X_SALESORD_HIST_EVENT_KEY')
	BEGIN
	CREATE TABLE X_SALESORD_HIST_EVENT_KEY
	(
	 EVENT_TYPE CHAR(1) PRIMARY KEY
	,TypeDesc VARCHAR(30)
	)
	INSERT INTO X_SALESORD_HIST_EVENT_KEY (EVENT_TYPE,TypeDesc)
	VALUES ('N','New Order'),('O','Order Qty'),('D','Document'),('I','Invoice'),('R','Release'),('S','Delivery (Supply)'),('H','History Note')
	END
if not exists (select 1 from sys.tables where name='X_PURCHORD_HIST_EVENT_KEY')
	BEGIN
	CREATE TABLE X_PURCHORD_HIST_EVENT_KEY
	(
	 EVENT_TYPE CHAR(1) PRIMARY KEY
	,TypeDesc VARCHAR(30)
	)
	INSERT INTO X_PURCHORD_HIST_EVENT_KEY (EVENT_TYPE,TypeDesc)
	VALUES ('N','New Order'),('O','Order Qty'),('D','Document'),('I','Invoice'),('R','IWG Receipt'),('C','IWG Cost'),('P','PO Receipt')
	END
if not exists (select 1 from sys.tables where name='X_GL_TRANS_TYPES_KEY')
	BEGIN
	CREATE TABLE X_GL_TRANS_TYPES_KEY
	( SOURCE_TYPE CHAR 
	,SOURCE_NAME VARCHAR(50)) 
	insert into X_GL_TRANS_TYPES_KEY
	VALUES ('a','Asset Register'),('b','Debtor Banking Batches'),('c','Creditors'),('d','Debtors'),('e','GL Utils, End of Year'),('i','Intercompany'),('m','Elimination Transaction'),('j','Job Costing'),('r','Debtor Payment Batch Entry/Auto Reversals'),('v','Inventory'),('x','Foreign Exchange Reconciliation'),('B','Cashbook Entry'),('C','Creditor Payment Processor'),('R','Bank Reconciliation'),('g','GL Transaction Entry')
	END
if not exists (select 1 from sys.tables where name='X_ASSET_STATUS_KEY')
	BEGIN
	CREATE TABLE X_ASSET_STATUS_KEY
	(
	 ASSETSTAT INT PRIMARY KEY
	,StatusDesc VARCHAR(30)
	)
	INSERT INTO X_ASSET_STATUS_KEY (ASSETSTAT,StatusDesc)
	VALUES (0,'Current'),(1,'Sold'),(2,'Disposed')
	END
if not exists (select 1 from sys.views where name='X_ACCNO_TRANSLATOR')
	EXEC
	('CREATE VIEW X_ACCNO_TRANSLATOR 
	--AUTHOR: Will Howard, 2017-11-16
	--PURPOSE: Make Clarity behave better (Allow for easy join between tables.
	AS 
	(
	SELECT ACCNO,NAME,''G'' AS TYPE from GLACCS 
	UNION ALL
	SELECT ACCNO,NAME,''D'' AS TYPE FROM DR_ACCS
	UNION ALL
	SELECT ACCNO,NAME,''C'' AS TYPE FROM CR_ACCS
	)')