Page 1 of 1
CDFDisbursment and CDFReciept relation to CDFLine
Posted: Tue Feb 14, 2017 5:31 pm
by nathan
Hello!
I am now attempting to relate the CDFDisbursment sql table to the CDFLine table. Ultimately I am only concerned with the BillCodeID from the CDFPayee table later in the joins. Right now I am stuck at the root relating CDFDisbursment to CDFLine. Any insight would be greatly appreciated!
Nathan
Re: CDFDisbursment and CDFReciept relation to CDFLine
Posted: Tue Feb 14, 2017 6:38 pm
by Lisa Ennis
Hope these will help. These are the joins for receipts:
Code: Select all
pfm.CDFReceipt AS R
INNER JOIN pfm.CDF AS CDF ON R.RootId#=CDF.RootId# AND R.CDFId$=CDF.Id#
LEFT OUTER JOIN pfm.CDFPayor AS PR ON R.RootId#=PR.RootId# AND R.Id#=PR.CDFAmountId$
LEFT OUTER JOIN pfm.CDFLine AS CL ON R.RootId#=CL.RootId# AND PR.Id# BETWEEN CL.Id# AND CL.LastId#
LEFT OUTER JOIN pfm.CDFLoanFundingPayor AS LFP ON R.RootId#=LFP.RootId# AND R.Id#=LFP.CDFAmountId$
LEFT OUTER JOIN pfm.Loan AS L ON LFP.RootId#=L.RootId# AND CDF.Id#=L.CDFId$ AND R.Id#=LFP.CDFAmountId$ AND LFP.LoanId$=L.Id#
These are the joins for disbursements:
Code: Select all
pfm.CDFDisbursement AS D
INNER JOIN pfm.CDF AS CDF ON D.RootId#=CDF.RootId# AND D.CDFId$=CDF.Id#
LEFT OUTER JOIN pfm.CDFPayee AS PE ON D.RootId#=PE.RootId# AND D.Id#=PE.CDFAmountId$
LEFT OUTER JOIN pfm.CDFLine AS CL ON D.RootId#=CL.RootId# AND PE.Id# BETWEEN CL.Id# AND CL.LastId#
LEFT OUTER JOIN pfm.CDFLoanFundingPayee AS LFP ON D.RootId#=LFP.RootId# AND D.Id#=LFP.CDFAmountId$
LEFT OUTER JOIN pfm.Loan AS L ON LFP.RootId#=L.RootId# AND CDF.Id#=L.CDFId$ AND D.Id#=LFP.CDFAmountId$ AND LFP.LoanId$=L.Id#