Disbursing on Issued Policies

Discussions related to custom development with Select.
Post Reply
tmeisinger
Posts: 75
Joined: Fri Apr 24, 2015 10:33 am

Disbursing on Issued Policies

Post by tmeisinger »

I’m attempting to automate the disbursement to our Underwriters each month, by building off of the SoftPro rptdoc.PolicyRegister. My question is when we have a Construction Loan with multiple Disbursement Endorsements, that are issued over multiple remittance periods, how do I find the appropriate Ledger Transactions to disburse? In other words, can I tie an individual Endorsement to a Ledger Transaction? By the way, the Ledger transfer from the Order could be for multiple endorsements that span remittance periods ($12 transfer, but $6 were issued and will be remitted this month, while $6 will be issued and remitted next month). Also, I’m guessing you can’t disburse part of a Ledger Transaction.
Lisa Ennis
Posts: 84
Joined: Thu Sep 11, 2008 1:57 pm

Re: Disbursing on Issued Policies

Post by Lisa Ennis »

Since you are trying to tie this back to disbursements in ProTrust, you may want to use the pt.RevenueDetail table instead. The pt.TitleCharge table joins to the pt.RevenueDetail table (RD.GUID=TC.TransactionID) and has some of the information related to title charges stored in XML that you can extract. Or you can join back to the pfm tables in ProFrom using the pt.RevenueDetail.ChargeSource field. If you go that route, you may need to traverse back to those title tables using the pfm.Entity table.
Lisa Ennis
Senior Report Developer
SoftPro
tmeisinger
Posts: 75
Joined: Fri Apr 24, 2015 10:33 am

Re: Disbursing on Issued Policies

Post by tmeisinger »

Trying to tie back a Ledger Disbursement to Revenue Detail to prepare an Accounting Upload. I thought your rptdoc.RevenueFeeDetailsByOrderID would be a good place to start as I discovered it tied the Revenue Detail back to the Revenue Ledger. Everything was working pretty good until we deleted a Register Posting Transferring Out to LedgerA and then posted it as a Transfer Out to LedgerB. Within the details of the RevenueFeeDetailsByOrderID it shows the LedgerA posting and then two LedgerB postings (a negative and positive). As we Disburse out of LedgerB, it ties back and shows the sum of $0. Here is a snap shot of what I have, am I doing something wrong? Any help would be most appreciated!

Code: Select all

SELECT 
    DT.ID AS DT_ID
  , DT.ReferenceNumber AS DT_Ref
  , DT.Amount AS DT_Amount
  , RL.Name AS RL
  , RL.Kind	 AS RL_K
  , RT.ID AS RT_ID
  , RT.Amount AS RT_Amt
  , OT.ID AS OT_ID
  , OT.Amount AS T_Amt
  , OL.Name AS OL
  , OL.Kind AS OL_K
  , RD.BillCode AS RD_BC
  , RD.Amount AS RD_Amt
FROM
	pt.[Transaction] AS DT																		--DisbursedTransaction 
	INNER JOIN pt.TransactionExtended AS TE ON DT.ID = TE.DisbursedAsID 
	INNER JOIN pt.[Transaction] AS RT ON TE.ID = RT.GUID AND RT.IsCurrent = 1	--Revenue Transaction
	INNER JOIN pt.Ledger AS RL ON RT.LedgerID = RL.ID									--Revenue Ledger
	INNER JOIN pt.[Transaction] AS OT ON RT.RelatedTransactionID = OT.ID			--Order Transaction
	INNER JOIN pt.Ledger AS OL ON OT.LedgerID = OL.ID									--Order Ledger
	INNER JOIN pt.[Transaction] AS RDT ON TE.ID = RDT.GUID AND RDT.IsCurrent = 1
	INNER JOIN pt.[Transaction] AS RDT2 ON RDT2.GUID = RDT.GUID 
	INNER JOIN pt.RevenueDetail AS RD ON RDT2.RelatedTransactionID = RD.TransactionID
WHERE        
	(DT.ID = - 2147197041) 	--Disbursed Transaction ID
AND (OL.Name = '1521596')  	--Order Number
Lisa Ennis
Posts: 84
Joined: Thu Sep 11, 2008 1:57 pm

Re: Disbursing on Issued Policies

Post by Lisa Ennis »

When you deleted the transaction, the records are correct in that you would see the positive amount when it was posted, and then the negative amount when it was deleted. When the transaction is deleted, a new, pending transaction is generated on the ledger. From that point, you have to pick up the new transaction. While the new transaction is pending, you will not have any records for it in the RevenueDetail table. As soon as you post the transaction, it writes the records to the RevenueDetail table as a new transaction with a new transaction id. At that point, it is no longer tied to the original transaction that was deleted.
Lisa Ennis
Senior Report Developer
SoftPro
tmeisinger
Posts: 75
Joined: Fri Apr 24, 2015 10:33 am

Re: Disbursing on Issued Policies

Post by tmeisinger »

Thank you for your reply Lisa, but with the query I posted earlier, I get the following:

Code: Select all

DT_ID			DT_Ref			DT_Amount		RL		RL_K	RT_ID			RT_Amt	OT_ID			T_Amt	OL		OL_K		RD_BC	RD_Amt
-2147194841	160803144459	55678.67		A1607	4		-2147194790	788.65	-2147194789	788.65	1521596	5		TP		688.65
-2147194841	160803144459	55678.67		A1607	4		-2147194790	788.65	-2147194789	788.65	1521596	5		ESC		100.00
-2147194841	160803144459	55678.67		A1607	4		-2147194790	788.65	-2147194789	788.65	1521596	5		ESC		-100.00
-2147194841	160803144459	55678.67		A1607	4		-2147194790	788.65	-2147194789	788.65	1521596	5		TP		-688.65
I'm not sure what I'm missing, but the Joins I have to get from the Disbursed Transaction back to the Order and the Revenue Detail makes it look like the Current Revenue Ledger has the negative amount where the transaction was deleted, not the original Revenue Ledger where the transaction was deleted (which was A1602). If I try to sum the Revenue by Bill Code (RD_BC) for the Disbursed Transaction, I get $0.

Would you please look at my query? I'm struggling a bit getting from Transaction to Transaction to Transaction...

Thanks in advance,
-TM
tmeisinger
Posts: 75
Joined: Fri Apr 24, 2015 10:33 am

Re: Disbursing on Issued Policies

Post by tmeisinger »

If you won't offer assistance on writing queries, then how about an Updated Database Guide?
Post Reply