Great Plains – Asset TSQL Case Statements

Cast(CASE ASSETTYPE
 WHEN 1  THEN 'New'
 WHEN 2  THEN 'Used'
 WHEN 3  THEN 'Leased'
 END as nvarchar(50)) as c_AssetType

Cast(CASE ASSETSTATUS
 WHEN 1  THEN 'Active'
 WHEN 2  THEN 'Deleted'
 WHEN 3  THEN 'Partial Open'
 WHEN 4  THEN 'Retired'
 END as nvarchar(50)) as c_AssetStatus

Cast(CASE PROPTYPE
 WHEN 1  THEN 'Personal'
 WHEN 2  THEN 'Personal, Listed'
 WHEN 3  THEN 'Real'
 WHEN 4  THEN 'Real, Listed'
 WHEN 5 THEN 'Real, Conservation'
 WHEN 6 THEN 'Real, Energy'
 WHEN 7 THEN 'Real, Farms'
 WHEN 8 THEN 'Real, Low Income Housing'
 WHEN 9 THEN 'Amortizable'
 END as nvarchar(50)) as c_PropertyType

Cast(CASE TRANSACCTTYPE
 WHEN 1  THEN 'Reserve'
 WHEN 2  THEN 'Depreciation'
 WHEN 3  THEN 'Cost'
 WHEN 4  THEN 'Proceeds'
 WHEN 5 THEN 'Recognized Gain/Loss'
 WHEN 6 THEN 'Non Recognized Gain/Loss'
 WHEN 7 THEN 'Clearing'
 END as nvarchar(50)) as c_TransactionAccountType

Cast(CASE DEPRECIATIONMETHOD
 WHEN 1 THEN 'Straight-Line Orig Life'
 WHEN 2 THEN 'Straight-Line Rem Life'
 WHEN 3 THEN '125% DB'
 WHEN 4 THEN '150% DB'
 WHEN 5 THEN '175% DB'
 WHEN 6 THEN '200 DB'
 WHEN 7 THEN 'SOY Digits'
 WHEN 8 THEN 'Remaining Life'
 WHEN 9 THEN 'Amortization'
 WHEN 10 THEN 'ACRS Personal'
 WHEN 11 THEN 'ACRS Real'
 WHEN 12 THEN 'ACRS Real MSL'
 WHEN 13 THEN 'ACRS LIH'
 WHEN 14 THEN 'ACRS Foreign Real'
 WHEN 15 THEN 'No Depreciation'
 WHEN 16 THEN 'Declining Balance'
 END as nvarchar(50)) as c_DepreciationMethod

case AVERAGINGCONV
 WHEN 1  THEN 'Half-Year'
 WHEN 2  THEN 'Modified Half-Year'
 WHEN 3  THEN 'Mid-Month (1st)'
 WHEN 4  THEN 'Mid-Month (15th)'
 WHEN 5 THEN 'Mid-Quarter'
 WHEN 6 THEN 'Next Month'
 WHEN 7 THEN 'Full Month'
 WHEN 8 THEN 'Next Year'
 WHEN 9 THEN 'Full Year'
 WHEN 10 THEN 'Full Year All Year'
 WHEN 11 THEN 'None'
 WHEN 12  THEN 'Next Period'
 WHEN 13  THEN 'Full period'
 End as c_AverageAgingConvention

CASE SWITCHOVER
 WHEN 1  THEN 'No switch'
 WHEN 2  THEN 'Straight-Line'
 END as c_SwitchOver

 

Thanks to Victoria Yudin. https://victoriayudin.com/gp-tables/fixed-assets-tables/

I’m just making these so we don’t have to re do this over and over again.

DOCTYPE Case Statement for Great Plains

cast(case a.DOCTYPE when 1 THEN 'Invoice'
 when 2 THEN 'Finance Charge'
 when 3 THEN 'Misc Charge'
 when 4 THEN 'Return'
 when 5 THEN 'Credit Memo'
 when 6 THEN 'Payment' end as nvarchar(50)) as DocType

Thanks to Victoria Yudin for the source data. https://victoriayudin.com/gp-tables/pm-tables/

 

 

DISTTYPE Case Statement for Great Plains

Victoria Yudin has some very useful information on the web but, I need a practical script for SQL. Now you can have it too.

cast(case DISTTYPE when 1  THEN 'Cash'  
WHEN 2  THEN 'Payable'  
WHEN 3  THEN 'Discount Available'
WHEN 4  THEN 'Discount Taken'  
WHEN 5  THEN 'Finance Charge' 
WHEN 6  THEN 'Purchase' 
WHEN 7  THEN 'Trade Disc.'
WHEN 8  THEN 'Misc. Charge' 
WHEN 9  THEN 'Freight' 
WHEN 10  THEN 'Taxes' 
WHEN 11  THEN 'Writeoffs'
WHEN 12  THEN 'Other' 
WHEN 13  THEN 'GST Disc' 
WHEN 14  THEN 'PPS Amount' 
WHEN 16  THEN 'Round'
WHEN 17  THEN 'Realized Gain' 
WHEN 18  THEN 'Relaized Loss' 
WHEN 19  THEN 'Due To'
WHEN 20  THEN 'Due From' end as nvarchar(50)) as DistributionType

Thanks to Victoria Yudin for the source data. https://victoriayudin.com/gp-tables/pm-tables/