Differences
This shows you the differences between two versions of the page.
— |
control_sql_-_direct_cost_from_bills_details [2019/01/27 11:28] (current) |
||
---|---|---|---|
Line 1: | Line 1: | ||
+ | ====== | ||
+ | |||
+ | |||
+ | |||
+ | ===== Explanation of SQL ===== | ||
+ | |||
+ | |||
+ | |||
+ | This query provides a breakdown of the ins and outs for the Direct Cost from Bills GL Account. | ||
+ | |||
+ | |||
+ | |||
+ | ===== Risk of Data Corruption if Run Improperly ===== | ||
+ | |||
+ | **None**. | ||
+ | |||
+ | |||
+ | |||
+ | ===== SQL ===== | ||
+ | |||
+ | |||
+ | |||
+ | <code sql> | ||
+ | select *, | ||
+ | | ||
+ | | ||
+ | | ||
+ | | ||
+ | | ||
+ | | ||
+ | | ||
+ | | ||
+ | | ||
+ | | ||
+ | from | ||
+ | ( | ||
+ | select GL.ID as GLID, | ||
+ | | ||
+ | | ||
+ | case TransDetailClassTypeID | ||
+ | when 11100 then ' | ||
+ | when 10100 then ' | ||
+ | else NULL | ||
+ | end as Source, | ||
+ | case TransDetailClassTypeID | ||
+ | when 11100 then (select BillNumber from TransHeader where ID = GL.TransactionID) | ||
+ | when 10100 then (select OrderNumber from TransHeader where ID = GL.TransactionID) | ||
+ | else NULL | ||
+ | end as TransHeaderNumber, | ||
+ | case TransDetailClassTypeID | ||
+ | when 11100 then (select LineItemNumber | ||
+ | when 10100 then (select LineItemNumber from TransDetail where ID = GL.TransDetailID) | ||
+ | else NULL | ||
+ | end as LineItem, | ||
+ | CASE WHEN EntryType=1 THEN 1 ELSE 0 END IsUserEntered, | ||
+ | | ||
+ | When (TransPartID is null and TransDetailClassTypeID = 11100) | ||
+ | then (select AttachedOrderPartID from VendorTransDetail where ID = TransDetailID) | ||
+ | else TransPartID | ||
+ | end as TransPartID, | ||
+ | | ||
+ | | ||
+ | | ||
+ | | ||
+ | | ||
+ | from GL | ||
+ | where GLAccountID = 15 -- Direct Cost from Bills Account | ||
+ | ) Temp | ||
+ | order by EntryDateTime, | ||
+ | </ | ||
+ | |||
+ | |||
+ | |||
+ | ===== Version Information ===== | ||
+ | * Entered : 5/2012 | ||
+ | * Version : 4.6+ | ||
+ | |||
+ | |||
+ | |||