Disbursing on Issued Policies
-
- Posts: 75
- Joined: Fri Apr 24, 2015 10:33 am
Disbursing on Issued Policies
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.
-
- Posts: 84
- Joined: Thu Sep 11, 2008 1:57 pm
Re: Disbursing on Issued Policies
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
Senior Report Developer
SoftPro
-
- Posts: 75
- Joined: Fri Apr 24, 2015 10:33 am
Re: Disbursing on Issued Policies
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
-
- Posts: 84
- Joined: Thu Sep 11, 2008 1:57 pm
Re: Disbursing on Issued Policies
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
Senior Report Developer
SoftPro
-
- Posts: 75
- Joined: Fri Apr 24, 2015 10:33 am
Re: Disbursing on Issued Policies
Thank you for your reply Lisa, but with the query I posted earlier, I get the following:
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
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
Would you please look at my query? I'm struggling a bit getting from Transaction to Transaction to Transaction...
Thanks in advance,
-TM
-
- Posts: 75
- Joined: Fri Apr 24, 2015 10:33 am
Re: Disbursing on Issued Policies
If you won't offer assistance on writing queries, then how about an Updated Database Guide?