Why is the CustTransOpen a separate table

(0) Share

Report

Posted on by Sven Joos

811

Hi,

can any one tell me why the CustTransOpen is a separate table and why this information is not stored on the CustTrans table itself?

Is CustTransOpen always one on one with CustTrans, or can you have multiple CustTransOpen records per CustTrans record?

Kind regards,

Sven

*This post is locked for comments

I have the same question (0)

All responses (7)

Answers (0)

Sort by

Pierson Carmichael 400 on at

Like (0)

Report
Copy link

Link copied!

From memory, I believe the CustTransOpen table consists of only invoices with an open balance. I would assume this is done for efficiencies because the CustTrans table could get quite a bit larger.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report
Copy link

Link copied!

you can have multiple custTransOpen records per custTrans. This depends on the payment schedule defined on the customer; one custTransOpen record per due date exists.

Peter

Was this reply helpful? Yes No
Suggested answer

Muhammad Afsar Khan 2,676 on at

Like (0)

Report
Copy link

Link copied!

The reason for seperating the CustTransOpen table from custTrans table is that there is a chance for custTransOpen to have multiple records for the same CustTrans table which is possible in case of Payment schedule on customer invoice. The CustTransOpen table holds the transactions thats is still open/not paid by the customer.

Regards,

-Afsar

mafsarkhan.blogspot.com

Was this reply helpful? Yes No
Rakesh7544 40 on at

Like (0)

Report
Copy link

Link copied!

But how does it create a record in CustTransOpen? I know its based on the open balance. I wanted to see the code how those records are created to troubleshoot an issue. Recently we faced with a situation. We recieved a payment of $480.68 and applied $20 to INV1 and $443.68 to another INV2 . User noticed it as wrong payment amount has been entered and tried to reverse this transaction. Internally it should create one line in custsettlement instead it created 2 positive payment amount in CustSettlemt table. I believe it reversed 2 times for same payment (We see $ +20 2 times and +$463.68 2 times in CustSettlementTable). BTW these 2 records are created at same time, with consecutive recid in custsettlement table. CustTrans.AmountCur is showing $ -480.68 and SettleAmountCur is showing $ +460.68 total balance pending as $ -924.36 . This is because of 2 Lines created in custSettlement. We need to fix this issue.

So before I propose any solution, I wanted to see how and what will trigger to create a record in CustTrans and custTransOpen tables.

Any advise?

Thanks,

Rakesh

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report
Copy link

Link copied!

The separation allows the freedom to control the installments, while custtrans contains the balance of the note. When custTransOpen is excluded , altered with payment process, abatement or change of a transaction which makes it very practical to be treated independently.In addition to making the search faster for open transactions or total balance of the client.

Was this reply helpful? Yes No
Suggested answer

PA-22040759-0 6,194 on at

Like (0)

Report
Copy link

Link copied!

The are two reasons for this to be a separate table:

1. You can split the transaction up into several installments. So you can have several CustTransOpen record per CustTrans record.

2. It has a huge performance benefit when working with the active transactions.

The class CustVoucher would be a good place to start investigations into how these records are generated.

Was this reply helpful? Yes No
Suggested answer

Hossein.K 6,650 on at

Like (0)

Report
Copy link

Link copied!

CustTrans Table: The CustTrans table contains the posted transaction information for the customer.

CustTransOpen Table: The CustTransOpen table contains transaction information when in an unsettled state.

Was this reply helpful? Yes No