Description
BTS 3934 : increase the performance of the current CalculatePaySelLine method.
Parameters
| ilPrepaymentOnly | input | logical | Prepayment Only |
| oiReturnStatus | output | integer | Return status of the method. |
Internal usage
QadFinancials
program code (program3/bpaymentselection.p)
/*======================================================================*/
/*= When this method is run, the instance should allready be available =*/
/*======================================================================*/
assign viAddressTypeHeadOffice_ID = ?.
if not ilPrepaymentOnly
then do:
/*==================*/
/*= CInvoice lines =*/
/*==================*/
assign vcDummy = {&PAYMENTSELECTIONTYPE-INVOICE} + ',':U
+ {&PAYMENTSELECTIONTYPE-INVOICECORR} + ',':U
+ {&PAYMENTSELECTIONTYPE-CREDITNOTE} + ',':U
+ {&PAYMENTSELECTIONTYPE-CREDITNOTECORR} + ',':U
+ {&PAYMENTSELECTIONTYPE-PREPAYMENTINV} + ',':U
+ {&PAYMENTSELECTIONTYPE-ADJUSTMENT}.
/* Run the CInvoice query a PaySel line of that type exists */
if can-find(first tPaySelLine where
tPaySelLine.tc_ParentRowid = tPaySel.tc_Rowid and
(tPaySelLine.PaySelLineObjectType = {&PAYMENTSELECTIONTYPE-INVOICE} or
tPaySelLine.PaySelLineObjectType = {&PAYMENTSELECTIONTYPE-INVOICECORR} or
tPaySelLine.PaySelLineObjectType = {&PAYMENTSELECTIONTYPE-CREDITNOTE} or
tPaySelLine.PaySelLineObjectType = {&PAYMENTSELECTIONTYPE-CREDITNOTECORR} or
tPaySelLine.PaySelLineObjectType = {&PAYMENTSELECTIONTYPE-PREPAYMENTINV} or
tPaySelLine.PaySelLineObjectType = {&PAYMENTSELECTIONTYPE-ADJUSTMENT}) and
tPaySelLine.PaySelLineParentObject_ID > 0)
then do:
if viAddressTypeHeadOffice_ID = ?
then do :
/* Get the AddressType_ID for the HeadOffice Address Type */
<Q-20 run AddressTypePrim (all) (Read) (NoCache)
(input ?, (AddressTypeId)
input {&ADDRESSTYPECODESYSTEM-HEADOFFICE}, (AddressTypeCode)
output dataset tqAddressTypePrim) in BAddressType>
find first tqAddressTypePrim no-error.
assign viAddressTypeHeadOffice_ID = if available tqAddressTypePrim
then tqAddressTypePrim.tiAddressType_ID
else 0.
end. /* if viAddressTypeHeadOffice_ID = ? */
<Q-5 run PaySelLineCInvoiceByPaySelId (all) (Read) (NoCache)
(input {&ADDRESSTYPECODESYSTEM-HEADOFFICE}, (AddressTypeCode)
input '':U, (BusinessRelationCode)
input vcDummy, (ObjectTypeList)
input tPaySel.PaySel_ID, (PaySel_Id)
input ?, (CompanyId)
input viAddressTypeHeadOffice_ID, (AddressType_ID)
output dataset tqPaySelLineCInvoiceByPaySelId) in BPaymentSelection>
end. /* if can-find(first tPaySelLine */
/* =================================================================================================================== *
* Retrieve WHT data for all invoices on the PaymentSelection *
* =================================================================================================================== */
if can-find (first tqPaySelLineCInvoiceByPaySelId where
tqPaySelLineCInvoiceByPaySelId.tiPaySel_ID = tPaySel.PaySel_ID and
tqPaySelLineCInvoiceByPaySelId.tlCInvoiceIsWHT = true)
then do:
<M-40 run CalculatePaySelLineWhtGetData
(input tPaySel.tc_Rowid (icCDocumentRowId),
output tDefaultWHTDataForPaySel (tDefaultWHTDataForPaySel),
output viFcReturnSuper (oiReturnStatus)) in BPaymentSelection>
if viFcReturnSuper < 0 or viFcReturnSuper > 0 and oiReturnStatus = 0 then assign oiReturnStatus = viFcReturnSuper.
if oiReturnStatus < 0 then leave.
end.
for each tPaySelLine where
tPaySelLine.tc_ParentRowid = tPaySel.tc_Rowid and
(tPaySelLine.PaySelLineObjectType = {&PAYMENTSELECTIONTYPE-INVOICE} or
tPaySelLine.PaySelLineObjectType = {&PAYMENTSELECTIONTYPE-INVOICECORR} or
tPaySelLine.PaySelLineObjectType = {&PAYMENTSELECTIONTYPE-CREDITNOTE} or
tPaySelLine.PaySelLineObjectType = {&PAYMENTSELECTIONTYPE-CREDITNOTECORR} or
tPaySelLine.PaySelLineObjectType = {&PAYMENTSELECTIONTYPE-PREPAYMENTINV} or
tPaySelLine.PaySelLineObjectType = {&PAYMENTSELECTIONTYPE-ADJUSTMENT}) and
tPaySelLine.PaySelLineParentObject_ID > 0:
find first tqPaySelLineCInvoiceByPaySelId where
tqPaySelLineCInvoiceByPaySelId.tiPaySelLine_ID = tPaySelLine.PaySelLine_ID no-error.
if not available tqPaySelLineCInvoiceByPaySelId
then next.
assign tPaySelLine.tcBusinessRelationCode = tqPaySelLineCInvoiceByPaySelId.tcBusinessRelationCode
tPaySelLine.tcBusinessRelationName1 = tqPaySelLinecInvoiceByPaySelId.tcBusinessRelationName1
tPaySelLine.tcCreditorCode = tqPaySelLineCInvoiceByPaySelId.tcCreditorCode
tPaySelLine.tlCreditorIsWHT = tqPaySelLineCInvoiceByPaySelId.tlCreditorIsWHT
tPaySelLine.tiMaster_ID = tqPaySelLineCInvoiceByPaySelId.tiCreditor_ID
tPaySelLine.tcDivisionCode = tqPaySelLineCInvoiceByPaySelId.tcDivisionCode
tPaySelLine.tcTSM = tqPaySelLineCInvoiceByPaySelId.tcCInvoiceTSMNumber
tPaySelLine.tcInternalNumber = string(tqPaySelLineCInvoiceByPaySelId.tiPeriodYear) + "/":U +
tqPaySelLineCInvoiceByPaySelId.tcJournalCode + "/":U +
string(tqPaySelLineCInvoiceByPaySelId.tiCInvoiceVoucher,"999999999":U)
tPaySelLine.tcExternalNumber = tqPaySelLineCInvoiceByPaySelId.tcCInvoiceReference
tPaySelLine.ttDiscountDueDate = tqPaySelLineCInvoiceByPaySelId.ttCInvoiceDiscountDueDate
tPaySelLine.tdDiscountPercentage = tqPaySelLineCInvoiceByPaySelId.tdPaymentConditionPercentage
tPaySelLine.ttInvoiceDate = tqPaySelLineCInvoiceByPaySelId.ttCInvoiceDate
tPaySelLine.tiInvoiceCompany_ID = tqPaySelLineCInvoiceByPaySelId.tiCInvoiceCompany_ID
tPaySelLine.tdInvoiceRate = tqPaySelLineCInvoiceByPaySelId.tdCInvoiceExchangeRate
tPaySelLine.tdInvoiceRateScale = tqPaySelLineCInvoiceByPaySelId.tdCinvoiceRateScale
tPaySelLine.ttDueDate = tqPaySelLineCInvoiceByPaySelId.ttCInvoiceDueDate
tPaySelLine.tcInvoiceCurrencyCode = tqPaySelLineCInvoiceByPaySelId.tcCurrencyCode
tPaySelLine.tdCInvoiceHoldAmountTC = tqPaySelLineCInvoiceByPaySelId.tdCInvoiceHoldAmountTC.
<M-19 run GetCInvoiceAmounts
(input tPaySelLine.PaySelLineObjectType (icTransactionObjectType),
input tqPaySelLineCInvoiceByPaySelId.tdCInvoiceOriginalDebitTC (idInvoiceOriginalDebit),
input tqPaySelLineCInvoiceByPaySelId.tdCInvoiceOriginalCreditTC (idInvoiceOriginalCredit),
input tqPaySelLineCInvoiceByPaySelId.tdCInvoiceBalanceDebitTC (idInvoiceBalanceDebit),
input tqPaySelLineCInvoiceByPaySelId.tdCInvoiceBalanceCreditTC (idInvoiceBalanceCredit),
input tqPaySelLineCInvoiceByPaySelId.tdCInvoiceVatBaseDebitTC (idInvoiceVatBaseDebit),
input tqPaySelLineCInvoiceByPaySelId.tdCInvoiceVatBaseCreditTC (idInvoiceVatBaseCredit),
input tqPaySelLineCInvoiceByPaySelId.tdCInvoiceVatDebitTC (idInvoiceVatDebit),
input tqPaySelLineCInvoiceByPaySelId.tdCInvoiceVatCreditTC (idInvoiceVatCredit),
input tqPaySelLineCInvoiceByPaySelId.tdCInvoiceHoldAmountTC (idInvoiceHoldAmount),
output tPaySelLine.tdCInvoiceOriginalTC (odOriginalInvoiceAmount),
output tPaySelLine.tdOriginalInvoiceAmountNetTC (odOriginalInvoiceAmountNet),
output tPaySelLine.tcInvoiceOriginalAmountSign (ocOriginalInvoiceAmountSign),
output tPaySelLine.tcInvoiceCrDt (ocOriginalInvAmntDebitCreditAbbr),
output tPaySelLine.tdInvoiceVatTC (odInvoiceVat),
output tPaySelLine.tdInvoiceVatBaseTC (odInvoiceVatBase),
output tPaySelLine.tdOpenInvoiceAmountTC (odOpenBalInvAmount),
output tPaySelLine.tcInvoiceBalanceCrDt (ocOpenBalInvAmntDebitCreditAbbr),
output tPaySelLine.tdInvoiceBalanceTCSigned (odOpenBalInvAmntSigned),
output tPaySelLine.tcPaymentCrDt (ocPaymentAmntDebitCreditAbbr),
output tPaySelLine.tcDiscountCrDt (ocDiscountAmntDebitCreditAbbr),
output viFcReturnSuper (oiReturnStatus)) in BPaymentSelection>
if viFcReturnSuper < 0 or viFcReturnSuper > 0 and oiReturnStatus = 0 then assign oiReturnStatus = viFcReturnSuper.
if oiReturnStatus < 0 then return.
/* ================================================================================================ *
* In the instance we are keeping all data as to be with Debit/Credit indication *
* which means + Payment, + Discount, + Interest - Invoice, Credit note, Prepayment, New prepayment *
* - Payment, - Discount, - Interest - Invoice Correction, Credit note correction *
* But in the database data have to be saved as real movement value *
* which mens + Payment - Outgoing payment - Invoice, Credit Note Correction, New prepayment *
* - Discount - Outgoing payment - Invoice, Credit Note Correction, New prepayment *
* + Interest - Outgoing payment - Invoice, Credit Note Correction, New prepayment *
* - Payment - Incomming payment - Credit note, Invoice Correction, Existing prep *
* + Discount - Incomming payment - Credit note, Invoice Correction, Existing prep *
* - Interest - Incomming payment - Credit note, Invoice Correction, Existing prep *
* ================================================================================================ */
assign tPaySelLine.PaySelLineDiscountTC = - tPaySelLine.PaySelLineDiscountTC.
if tPaySelLine.tcPaymentCrDt = vcCreditTR
then assign tPaySelLine.PaySelLineAmountTC = - tPaySelLine.PaySelLineAmountTC
tPaySelLine.PaySelLineIntAmtTC = - tPaySelLine.PaySelLineIntAmtTC
tPaySelLine.PaySelLineDiscountTC = - tPaySelLine.PaySelLineDiscountTC.
if tqPaySelLineCInvoiceByPaySelId.tlCInvoiceIsWHT
then do:
/* Retrieve WHT related data for the Supplier invoice */
<M-88 run CalculatePaySelLineWhtAssign (output viFcReturnSuper (oiReturnStatus)) in BPaymentSelection>
if viFcReturnSuper < 0 or viFcReturnSuper > 0 and oiReturnStatus = 0 then assign oiReturnStatus = viFcReturnSuper.
if oiReturnStatus < 0 then return.
/* If the Supplier invoice has multiple bank numbers, calculate correctly WHT for this allocation */
<M-16 run CalculatePaySelLineWhtMultBanks
(input tPaySelLine.tdInitiallyAllocatedWHTAmntTC (idInitiallyAllocatedWHTAmntTC),
input-output tPaySelLine.tdCInvoiceWHTAmtTC (bdCInvoiceAmountWhtTC),
input-output vdDummy (bdCInvoiceBankToPayTC),
output viFcReturnSuper (oiReturnStatus)) in BPaymentSelection>
if viFcReturnSuper < 0 or viFcReturnSuper > 0 and oiReturnStatus = 0 then assign oiReturnStatus = viFcReturnSuper.
if oiReturnStatus < 0 then return.
end. /* if tqPaySelLineCInvoiceByPaySelId.tlCInvoiceIsWHT */
end.
/*==================*/
/*= DInvoice lines =*/
/*==================*/
/* Run the DInvoice query a PaySel line of that type exists */
assign vcDummy = {&PAYMENTSELECTIONTYPE-DEBTORCREDITNOTE} + ',':U
+ {&PAYMENTSELECTIONTYPE-DEBTORCNCORR}.
if can-find (first tPaySelLine where
tPaySelLine.tc_ParentRowid = tPaySel.tc_Rowid and
(tPaySelLine.PaySelLineObjectType = {&PAYMENTSELECTIONTYPE-DEBTORCREDITNOTE} or
tPaySelLine.PaySelLineObjectType = {&PAYMENTSELECTIONTYPE-DEBTORCNCORR}) and
tPaySelLine.PaySelLineParentObject_ID > 0)
then do:
/* Get the AddressType_ID for the HeadOffice Address Type */
if viAddressTypeHeadOffice_ID = ?
then do :
<Q-21 run AddressTypePrim (all) (Read) (NoCache)
(input ?, (AddressTypeId)
input {&ADDRESSTYPECODESYSTEM-HEADOFFICE}, (AddressTypeCode)
output dataset tqAddressTypePrim) in BAddressType>
find first tqAddressTypePrim no-error.
assign viAddressTypeHeadOffice_ID = if available tqAddressTypePrim
then tqAddressTypePrim.tiAddressType_ID
else 0.
end. /* if viAddressTypeHeadOffice_ID = ? */
<Q-7 run PaySelLineDInvoiceByPaySelId (all) (Read) (NoCache)
(input {&ADDRESSTYPECODESYSTEM-HEADOFFICE}, (AddressTypeCode)
input vcDummy, (ObjectTypeList)
input tPaySel.PaySel_ID, (PaySel_Id)
input ?, (CompanyId)
input viAddressTypeHeadOffice_ID, (AddressType_ID)
output dataset tqPaySelLineDInvoiceByPaySelId) in BPaymentSelection>
end. /* if can-find (first tPaySelLine where */
for each tPaySelLine where
tPaySelLine.tc_ParentRowid = tPaySel.tc_Rowid and
(tPaySelLine.PaySelLineObjectType = {&PAYMENTSELECTIONTYPE-DEBTORCREDITNOTE} or
tPaySelLine.PaySelLineObjectType = {&PAYMENTSELECTIONTYPE-DEBTORCNCORR}) and
tPaySelLine.PaySelLineParentObject_ID > 0:
find tqPaySelLineDInvoiceByPaySelId where
tqPaySelLineDInvoiceByPaySelId.tiPaySelLine_ID = tPaySelLine.PaySelLine_ID
no-error.
if not available tqPaySelLineDInvoiceByPaySelId
then next.
assign tPaySelLine.tcBusinessRelationCode = tqPaySelLineDInvoiceByPaySelId.tcBusinessRelationCode
tPaySelLine.tcCreditorCode = tqPaySelLineDInvoiceByPaySelId.tcDebtorCode
tPaySelLine.tiMaster_ID = tqPaySelLineDInvoiceByPaySelId.tiDebtor_ID
tPaySelLine.tcDivisionCode = tqPaySelLineDInvoiceByPaySelId.tcDivisionCode
tPaySelLine.tcTSM = tqPaySelLineDInvoiceByPaySelId.tcDInvoiceTSMNumber
tPaySelLine.tcInternalNumber = string(tqPaySelLineDInvoiceByPaySelId.tiPeriodYear) + "/":U +
tqPaySelLineDInvoiceByPaySelId.tcJournalCode + "/":U +
string(tqPaySelLineDInvoiceByPaySelId.tiDInvoiceVoucher,"999999999":U)
tPaySelLine.ttDiscountDueDate = tqPaySelLineDInvoiceByPaySelId.ttDInvoiceDiscountDueDate
tPaySelLine.tdDiscountPercentage = tqPaySelLineDInvoiceByPaySelId.tdPaymentConditionPercentage
tPaySelLine.ttInvoiceDate = tqPaySelLineDInvoiceByPaySelId.ttDInvoiceDate
tPaySelLine.tiInvoiceCompany_ID = tqPaySelLineDInvoiceByPaySelId.tiCompany_ID
tPaySelLine.tdInvoiceRate = tqPaySelLineDInvoiceByPaySelId.tdDInvoiceExchangeRate
tPaySelLine.tdInvoiceRateScale = tqPaySelLineDInvoiceByPaySelId.tdDinvoiceRateScale
tPaySelLine.ttDueDate = tqPaySelLineDInvoiceByPaySelId.ttDInvoiceDueDate
tPaySelLine.tcInvoiceCurrencyCode = tqPaySelLineDInvoiceByPaySelId.tcCurrencyCode
tPaySelLine.tcInvoiceCrDt = if tqPaySelLineDInvoiceByPaySelId.tdDInvoiceOriginalCreditTC <> 0 and
tqPaySelLineDInvoiceByPaySelId.tdDInvoiceOriginalCreditTC <> ?
then {&CREDITDEBITABBREVIATION-CREDIT}
else {&CREDITDEBITABBREVIATION-DEBIT}
tPaySelLine.tcPaymentCrDt = if tqPaySelLineDInvoiceByPaySelId.tdDInvoiceOriginalCreditTC <> 0 and
tqPaySelLineDInvoiceByPaySelId.tdDInvoiceOriginalCreditTC <> ?
then {&CREDITDEBITABBREVIATION-DEBIT}
else {&CREDITDEBITABBREVIATION-CREDIT}
tPaySelLine.tdOpenInvoiceAmountTC = tqPaySelLineDInvoiceByPaySelId.tdDInvoiceBalanceDebitTC
- tqPaySelLineDInvoiceByPaySelId.tdDInvoiceBalanceCreditTC
tPaySelLine.tdOriginalInvoiceAmountTC = tqPaySelLineDInvoiceByPaySelId.tdDInvoiceOriginalDebitTC
- tqPaySelLineDInvoiceByPaySelId.tdDInvoiceOriginalCreditTC
tPaySelLine.tdOriginalInvoiceAmountNetTC = tqPaySelLineDInvoiceByPaySelId.tdDInvoiceOriginalDebitTC
+ tqPaySelLineDInvoiceByPaySelId.tdDInvoiceVatDebitTC
- tqPaySelLineDInvoiceByPaySelId.tdDInvoiceOriginalCreditTC
- tqPaySelLineDInvoiceByPaySelId.tdDInvoiceVatCreditTC.
end.
end. /* if not ilPrepaymentOnly */
/*==============*/
/*= Prepayment =*/
/*==============*/
/* Run the CInvoice query a PaySel line of that type exists */
if can-find (first tPaySelLine where
tPaySelLine.tc_ParentRowid = tPaySel.tc_Rowid and
tPaySelLine.PaySelLineObjectType = {&PAYMENTSELECTIONTYPE-PREPAYMENT} and
tPaySelLine.PaySelLineParentObject_ID > 0)
then do:
<Q-9 run PaySelLineByPaySelIdPrepayment (all) (Read) (NoCache)
(input {&PAYMENTSELECTIONTYPE-PREPAYMENT}, (ObjectTypeList)
input tPaySel.PaySel_ID, (PaySelId)
input viCompanyId, (CompanyId)
output dataset tqPaySelLineByPaySelIdPrepayment) in BPaymentSelection >
end.
for each tPaySelLine where
tPaySelLine.tc_ParentRowid = tPaySel.tc_Rowid and
tPaySelLine.PaySelLineObjectType = {&PAYMENTSELECTIONTYPE-PREPAYMENT} and
tPaySelLine.PaySelLineParentObject_ID > 0:
assign tPaySelLine.tcPaymentCrDt = {&CREDITDEBITABBREVIATION-DEBIT-TR}.
find first tqPaySelLineByPaySelIdPrepayment where
tqPaySelLineByPaySelIdPrepayment.tiPaySelLine_ID = tPaySelLine.PaySelLine_ID
no-error.
if not available tqPaySelLineByPaySelIdPrepayment
then next.
if not ilPrepaymentOnly
then assign tPaySelLine.tcBusinessRelationCode = tqPaySelLineByPaySelIdPrepayment.tcBusinessRelationCode
tPaySelLine.tcCreditorCode = tqPaySelLineByPaySelIdPrepayment.tcCreditorCode
tPaySelLine.tcInvoiceCurrencyCode = tqPaySelLineByPaySelIdPrepayment.tcCurrencyCode
tPaySelLine.tcExternalNumber = tqPaySelLineByPaySelIdPrepayment.tcPaySelPrepayReferenceText.
assign viPrepayment_ID = viPrepayment_ID - 1.
create tPaySelPrepayRefPS.
assign tPaySelPrepayRefPS.PaySelPrepayAmountToPayTC = tqPaySelLineByPaySelIdPrepayment.tdPaySelPrepayAmountToPayTC
tPaySelPrepayRefPS.PaySelPrepayReferenceText = tqPaySelLineByPaySelIdPrepayment.tcPaySelPrepayReferenceText
tPaySelPrepayRefPS.PaySelPrepay_ID = tqPaySelLineByPaySelIdPrepayment.tiPaySelPrepay_ID
tPaySelPrepayRefPS.tcBusinessRelationCode = tqPaySelLineByPaySelIdPrepayment.tcBusinessRelationCode
tPaySelPrepayRefPS.tcCreditorCode = tqPaySelLineByPaySelIdPrepayment.tcCreditorCode
tPaySelPrepayRefPS.tcCurrencyCode = tqPaySelLineByPaySelIdPrepayment.tcCurrencyCode
tPaySelPrepayRefPS.tc_Rowid = string(viPrepayment_ID).
end.