Hi everyone,
I am doing a customization to generate the total of all related invoices to each LedgerJournalTrans row on the Grid for Accounts Receivable’s payment Journal as attached pic shows:
RIght now, I found the way to find the related specTrans for a certain LedgerJournalTrans row Before the Journal is posted in the following way(both single invoice for the single LedgerJournalTrans or multiple invoices for the single LedgerJournalTrans cases work well) :
while select crossCompany specTrans
where specTrans.SpecCompany == _ledgerJournalTrans.company() &&
specTrans.SpecTableId == _ledgerJournalTrans.TableId &&
specTrans.SpecRecId == _ledgerJournalTrans.RecId
{
custTransOpenLocal = specTrans.custTransOpen();
CoA_Applied += custTransOpenLocal.AmountCur; //accumulates invoice(s)’ total
}
However, when the LedgerJournalTrans is posted, the SpecTrans is wiped out, so if I still want to find all related invoice’s total for the single ledgerJournalTrans record, if the ledgerJournalTrans has only 1 invoice then I can do the following:
while select custTransLocal
where custTransLocal.TransType == LedgerTransType::Payment
join custSettlementLocal
where custSettlementLocal.TransRecId == custTransLocal.RecId
join custTransLocal_Offset
where custSettlementLocal.OffsetRecId == custTransLocal_Offset.RecId
join _ledgerJournalTrans
where _ledgerJournalTrans.Voucher == custTransLocal.Voucher
&& _ledgerJournalTrans.invoice == custTransLocal_Offset.invoice /////because Voucher can be the same on multiple LedgerJournalTrans records, will have to combine the invoice as well here
{
total for the invoice = invoiceAmount from the invoice of (_ledgerJournalTrans.invoice)
}
BUT, what if there are mutiple invoices related to the single LedgerJournalTrans row, there is an asterisk here, how to organize the select statement? I know I am close, but I need another condition, the picture is like:
I have problem when there are multiple invoices included in the LedgerJournalTrans record, as the ledgerJournalTrans.invoice is an “*” now. How should I change?
Any advice is greatly appreciated!