CDFDisbursment and CDFReciept relation to CDFLine

Discussions concerning general integration topics.

Moderator: Phil Barton

Post Reply
nathan
Posts: 3
Joined: Thu Jul 28, 2016 9:00 am

CDFDisbursment and CDFReciept relation to CDFLine

Post 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
Lisa Ennis
Posts: 84
Joined: Thu Sep 11, 2008 1:57 pm

Re: CDFDisbursment and CDFReciept relation to CDFLine

Post 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#
Lisa Ennis
Senior Report Developer
SoftPro
Post Reply