Check why some remapped project expenses didn't get added to expenses and get associated.
Closed this issue · 4 comments
select * from expenses where account in ('AEV4CLE','AEV4GPE',' AEV1SHE','ANT3SO1')
-- Only ('AEV1SSE') got mapped over.
- does the remapped project expense show up in the source data?
SELECT * FROM dbo.PPS_ExpensesForNon204Projects
WHERE Account IN ('AEV4CLE','AEV4GPE',' AEV1SHE','ANT3SO1')
Chart Account SubAccount PrincipalInvestigator ObjConsol FinanceDocTypeCd DosCd OrgR Org EmployeeID EmployeeName TitleCd RateTypeCd Payrate Amount FringeBenefitSalaryCd AnnualReportCode FTE SFN
3 ANT3SO1 ----- STEWART,CHRISTINE PATRICIA SB06 PAY FTO ANUT NAES 135111128 STEWART,CHRISTINE PATRICIA 3020 % 7277.7500 8733.30 S 440248 0.1000 201
3 ANT3SO1 ----- STEWART,CHRISTINE PATRICIA SB06 SET FTO ANUT NAES 135111128 STEWART,CHRISTINE PATRICIA 3020 % 7277.7500 1455.55 S 440248 0.0167 201
3 ANT3SO1 ----- STEWART,CHRISTINE PATRICIA SB28 PAY FTO ANUT NAES 135111128 STEWART,CHRISTINE PATRICIA 3020 % 7277.7500 553.12 F 440248 0.0000 201
3 ANT3SO1 ----- STEWART,CHRISTINE PATRICIA SB28 PAY VAC ANUT NAES 135111128 STEWART,CHRISTINE PATRICIA 3020 H 41.8261 -602.29 S 440248 0.0000 201
3 AEV4CLE ----- LANGLEY,CHARLES H SB06 SET REG BEVE BEVX 243948262 LANGLEY,CHARLES H 3000 % 20220.5800 6038.20 S 441092 0.0249 201
SELECT * FROM dbo.FIS_ExpensesForNon204Projects
WHERE Account IN ('AEV4CLE','AEV4GPE',' AEV1SHE','ANT3SO1')
3 AEV4CLE ----- LANGLEY,CHARLES H 21006 SUB3 GEC BEVE BEVX 7279.40 201
3 AEV4GPE ----- PATRICELLI,GAIL L 21006 SUB3 GEC BEVE BEVE 0.02 201
3 AEV4GPE ----- PATRICELLI,GAIL L 21006 SUB5 ACHD BEVE BEVE 226.80 201
Data is in both FIS_ExpensesForNon204Projects and PPS_ExpensesForNon204Projects source data.
Data is in this sub query
SELECT t4.ProjectNumber AS Project, t3.ToAccession AS Accession, t1.SFN AS Exp_SFN, t1.OrgR, t1.Org, t1.EmployeeID AS EID,
t1.EmployeeName AS Employee_Name, t1.TitleCd, t1.Chart, t1.Account, t1.SubAccount AS SubAcct, t1.PrincipalInvestigator AS PI_Name, SUM(t1.Amount)
AS Expenses, t2.IsExpired, 1 AS IsAssociated, 0 AS isAssociable, 0 AS IsNonEmpExp, t1.SFN AS Sub_Exp_SFN, SUM(t1.FTE) AS FTE
FROM dbo.PPS_ExpensesForNon204Projects AS t1 LEFT OUTER JOIN
dbo.FFY_SFN_Entries AS t2 ON t1.Chart = t2.Chart AND t1.Account = t2.Account LEFT OUTER JOIN
dbo.ExpiredProjectCrossReference AS t3 ON t2.AccessionNumber = t3.FromAccession LEFT OUTER JOIN
dbo.AllProjectsNew AS t4 ON t3.ToAccession = t4.AccessionNumber AND t4.IsUCD = 1
WHERE (t1.SFN IN ('201', '202', '203', '205')) AND (t2.IsExpired = 1)
GROUP BY t1.Chart, t1.Account, t1.SubAccount, t1.PrincipalInvestigator, t1.OrgR, t1.Org, t1.EmployeeID, t1.EmployeeName, t1.TitleCd, t1.SFN, t2.IsExpired,
t3.ToAccession, t2.AccessionNumber, t4.ProjectNumber
HAVING (SUM(t1.Amount) > 0)
Project Accession Exp_SFN OrgR Org EID Employee_Name TitleCd Chart Account SubAcct PI_Name Expenses IsExpired IsAssociated isAssociable IsNonEmpExp Sub_Exp_SFN FTE
CA-D-EVE-2265-H 1005040 201 BEVE BEVX 026775320 SHAPIRO,ARTHUR M 3000 3 AEV1SHE ----- SHAPIRO,ARTHUR M 3003.00 1 1 0 0 201 0.0148
CA-D-EVE-2266-H 1005267 201 BEVE BEVX 313362097 STRAUSS,SHARON Y 3000 3 AEV1SSE ----- STRAUSS,SHARON Y 2328.44 1 1 0 0 201 0.0131
CA-D-EVE-2268-H 1005369 201 BEVE BEVX 243948262 LANGLEY,CHARLES H 3000 3 AEV4CLE ----- LANGLEY,CHARLES H 6038.20 1 1 0 0 201 0.0249
CA-D-NTR-4543-H 0096031 201 ANUT NAES 135111128 STEWART,CHRISTINE PATRICIA 3020 3 ANT3SO1 ----- STEWART,CHRISTINE PATRICIA 10139.68 1 1 0 0 201 0.1167
-- For some reason account 'AEV1SHE' does not come back, even after I removed the INNER JOIN to FFY_SFN_ENTRIES:
SELECT [Chart]
,[Account]
,[Org]
,[OrgR]
,[Expenses]
,[SFN]
,[IsExpired]
FROM [AD419].[dbo].[AD419Accounts]
WHERE Account = 'AEV1SHE'
Chart Account Org OrgR Expenses SFN IsExpired
3 AEV1SHE BEVX BEVE 3003.00 201 NULL
I had a leading space, and it is actually present:
select * from expenses where account in ('AEV4CLE','AEV4GPE','AEV1SHE','ANT3SO1', 'AEV1SSE')
ExpenseID DataSource OrgR Chart Account SubAcct PI_Name Org EID Employee_Name TitleCd Title_Code_Name Exp_SFN Expenses FTE_SFN FTE isAssociated isAssociable isNonEmpExp Sub_Exp_SFN Staff_Grp_Cd
16962 20x BEVE 3 AEV1SSE NULL STRAUSS,SHARON Y BEVX NULL NULL NULL NULL 201 307.87 244 0.0000 1 0 1 201 Other
16963 20x BEVE 3 AEV4CLE NULL LANGLEY,CHARLES H BEVX NULL NULL NULL NULL 201 7279.40 244 0.0000 1 0 1 201 Other
16964 20x BEVE 3 AEV4GPE NULL PATRICELLI,GAIL L BEVE NULL NULL NULL NULL 201 226.82 244 0.0000 1 0 1 201 Other
17063 20x BEVE 3 AEV1SHE NULL SHAPIRO,ARTHUR M BEVX 026775320 SHAPIRO,ARTHUR M 3000 AGRON AES 201 3003.00 241 0.0148 1 0 0 201 Scientist
17064 20x BEVE 3 AEV1SSE NULL STRAUSS,SHARON Y BEVX 313362097 STRAUSS,SHARON Y 3000 AGRON AES 201 2328.44 241 0.0131 1 0 0 201 Scientist
17065 20x BEVE 3 AEV4CLE NULL LANGLEY,CHARLES H BEVX 243948262 LANGLEY,CHARLES H 3000 AGRON AES 201 6038.20 241 0.0249 1 0 0 201 Scientist
17097 20x ANUT 3 ANT3SO1 NULL STEWART,CHRISTINE PATRICIA NAES 135111128 STEWART,CHRISTINE PATRICIA 3020 ASST AGRON AES 201 10139.68 241 0.1167 1 0 0 201 Scientist
So the reason why only 1 account was showing up previously is because there was an superfluous inner join that would only work if there were some expenses for SFN 201, 202, 203, and 205, and the 1 project to which the expired project was being remapped to had an entry present, and the join worked. In other words, the join worked if there were expenses in the corresponding SFN for the non-expired project to which the expired project was being matched, but it would be omitted if there were none. I could see no reason for the join, so I removed it and now the expenses for the expired projects are showing up under the remapped project's accession numbers.