Make a new page that allows for the review of any missing codes used for FTE Calculation.

[taylorkj]

-- Querry to find missing ConsolidationCodes, DOSCodes, and DocTypeCodes in labor data
--Display how many are missing by individual category, and then display the transactions if found.

-- Look for any missing Consolidation codes:
-- Add any within our ARCS and Orgs:
SELECT t1.* FROM [AnotherLaborTransactions] t1
INNER JOIN (
select distinct ObjConsol from [AnotherLaborTransactions]
EXCEPT
select distinct Obj_Consolidatn_Num from ConsolidationCodes
) t2 ON t1.ObjConsol =t2.ObjConsol
WHERE ExcludedByOrg = 0 AND ExcludedByARC = 0

UNION

-- Add any non-expired, UC Davis 204 accounts
SELECT t1.* FROM [AnotherLaborTransactions] t1
INNER JOIN (
select distinct ObjConsol from [AnotherLaborTransactions]
EXCEPT
select distinct Obj_Consolidatn_Num from ConsolidationCodes
) t2 ON t1.ObjConsol =t2.ObjConsol
INNER JOIN AllAccountsFor204Projects t3 ON t1.Chart = t3.Chart AND
t1.Account = t3.Account AND t3.ExcludedByAccount = 0 AND t3.IsUCD = 1 AND
t3.IsExpired = 0

-- Look for any missing Finance Doc Type Codes:
-- Add any within our ARCS and Orgs:
select t1.* from [AnotherLaborTransactions] t1
INNER JOIN (
select distinct FinanceDocTypeCd from [AnotherLaborTransactions]
EXCEPT
select distinct DocumentType from TransDocTypes
) t2 ON t1.FinanceDocTypeCd = t2.FinanceDocTypeCd
WHERE ExcludedByOrg = 0 AND ExcludedByARC = 0

UNION

-- Add any non-expired, UC Davis 204 accounts
SELECT t1.* FROM [AnotherLaborTransactions] t1
INNER JOIN (
select distinct FinanceDocTypeCd from [AnotherLaborTransactions]
EXCEPT
select distinct DocumentType from TransDocTypes
) t2 ON t1.FinanceDocTypeCd = t2.FinanceDocTypeCd
INNER JOIN AllAccountsFor204Projects t3 ON t1.Chart = t3.Chart AND
t1.Account = t3.Account AND t3.ExcludedByAccount = 0 AND t3.IsUCD = 1 AND
t3.IsExpired = 0

-- Look for any missing Finance DOS Codes:
-- Add any within our ARCS and Orgs:
select t1.* from [AnotherLaborTransactions] t1
INNER JOIN ConsolCodesForFTECalc t2 ON t1.ObjConsol = t2.Obj_Consolidatn_Num
where DosCd IN (
select distinct DosCd from [AnotherLaborTransactions]
EXCEPT
select distinct DOS_Code DosCd from DOS_Codes
)
AND ExcludedByOrg = 0 AND ExcludedByARC = 0

UNION

-- Add any non-expired, UC Davis 204 accounts
select t1.* from [AnotherLaborTransactions] t1
INNER JOIN ConsolCodesForFTECalc t2 ON t1.ObjConsol = t2.Obj_Consolidatn_Num
INNER JOIN AllAccountsFor204Projects t3 ON t1.Chart = t3.Chart AND
t1.Account = t3.Account AND t3.ExcludedByAccount = 0 AND t3.IsUCD = 1 AND
t3.IsExpired = 0
where DosCd IN (
select distinct DosCd from [AnotherLaborTransactions]
EXCEPT
select distinct DOS_Code DosCd from DOS_Codes
)

[taylorkj]

This is related to issue #103.

[taylorkj]

Missing consolidation codes testing:
Use the following SQL to change some consolidation codes to ones that are not already in the system so they will appear in the missing codes section:

UPDATE [AD419].[dbo].[AnotherLaborTransactions]
SET ObjConsol = 'AAAA'
WHERE Chart = '3' AND Account = 'ALWRGAA' AND Org = '1990' AND ObjConsol = 'SUBG'
--(27 row(s) affected)

-- Restore them to the original values, and they should disappear from the missing codes section and a "Zero missing codes" message should appear:
UPDATE [AD419].[dbo].[AnotherLaborTransactions]
SET ObjConsol = 'SUBG'
WHERE Chart = '3' AND Account = 'ALWRGAA' AND Org = '1990' AND ObjConsol = 'AAAA'
--(27 row(s) affected)

[taylorkj]

Missing Trans Doc Types (Doc Type codes) testing:
Use the following SQL to change some consolidation codes to ones that are not already in the system so they will appear in the missing codes section:

UPDATE [AD419].[dbo].[AnotherLaborTransactions]
SET FinanceDocTypeCd = 'DAY'
WHERE Chart = '3' AND Account = 'ALWRGAA' AND Org = '1990' AND FinanceDocTypeCd = 'PAY'
--(1260 row(s) affected)

-- Restore them to the original values, and they should disappear from the missing codes section and a "Zero missing codes" message should appear:

UPDATE [AD419].[dbo].[AnotherLaborTransactions]
SET FinanceDocTypeCd = 'PAY'
WHERE Chart = '3' AND Account = 'ALWRGAA' AND Org = '1990' AND FinanceDocTypeCd = 'DAY'
--(1260 row(s) affected)

[taylorkj]

Missing DOS Codes testing:
Use the following SQL to change some consolidation codes to ones that are not already in the system so they will appear in the missing codes section:

UPDATE [AD419].[dbo].[AnotherLaborTransactions]
SET DosCd = 'RES'
WHERE Chart = '3' AND Account = 'ALWRGAA' AND Org = '1990' AND DosCd = 'REO'
--(484 row(s) affected)

-- Restore them to the original values, and they should disappear from the missing codes section and a "Zero missing codes" message should appear:
UPDATE [AD419].[dbo].[AnotherLaborTransactions]
SET DosCd = 'REO'
WHERE Chart = '3' AND Account = 'ALWRGAA' AND Org = '1990' AND DosCd = 'RES'
-- (484 row(s) affected)

[taylorkj]

Done!

[taylorkj]

Merged.