Description
BTS 3934 - Performance Improvement - Get InitialLoad data from instance tables tPaySelLine in stead of building it completely.
Parameters
| iiPaySel_ID | input | integer | The payment selection ID |
| icPaySelCurrCode | input | character | The payment selection currency code. |
| itPaySelDate | input | date | Payment Selection Date |
| oiReturnStatus | output | integer | Return status of the method. |
Internal usage
QadFinancials
program code (program3/bpaymentselection.p)
/* Load the ProposedLine info from the instance data */
find tPaySel where
tPaySel.PaySel_ID = iiPaySel_ID
no-error.
if not available tPaySel or
not can-find(first tPaySelLine where
tPaySelLine.tc_ParentRowid = tPaySel.tc_Rowid)
then do:
/* If for some reason no dataload was done before this method, calculate the contents */
<M-1 run GetToPayObjectsInitialLoad
(input iiPaySel_ID (iiPaySel_ID),
input icPaySelCurrCode (icPayselCurrCode),
input itPaySelDate (itPaySelDate),
output viFcReturnSuper (oiReturnStatus)) in BPaymentSelection>
return.
end.
/* Retrieve the ProposedLine contents from the PaySelLine instance table */
for each tPaySelLine where
tPaySelLine.PaySel_ID = iiPaySel_ID:
/* Check against the unique (UI) index */
if can-find( tProposedLine where
tProposedLine.tiObject_ID = tPaySelLine.PaySelLineParentObject_ID and
tProposedLine.tiBankNumber_ID = tPaySelLine.BankNumber_ID)
then next.
create tProposedLine.
<M-62 run GetDebitCreditAbbreviation
(input tPaySelLine.PaySelLineObjectType (icTransactionObjectType),
input tPaySelLine.tdOpenInvoiceAmountTC (idOpenBalInvoiceAmount),
output tProposedLine.tcOriginInvAmntSign (ocOriginalInvoiceAmountSign),
output tProposedLine.tcOriginInvAmntDebitCreditAbbr (ocOriginalInvAmntDebitCreditAbbr),
output tProposedLine.tcOpenInvAmntDebitCreditAbbr (ocOpenBalInvAmntDebitCreditAbbr),
output tProposedLine.tcPaymentAmntDebitCreditAbbr (ocPaymentAmntDebitCreditAbbr),
output tProposedLine.tcDiscountAmntDebitCreditAbbr (ocDiscountAmntDebitCreditAbbr),
output viFcReturnSuper (oiReturnStatus)) in BPaymentSelection>
/* Get directly from Instance table */
assign tProposedLine.tc_Status = "":U
tProposedLine.tiProposedLine_ID = viProposedLine_ID
tProposedLine.tiObject_ID = tPaySelLine.PaySelLineParentObject_ID
tProposedLine.tiBankNumber_ID = tPaySelLine.BankNumber_ID
tProposedLine.tiMaster_ID = tPaySelLine.tiMaster_ID
tProposedLine.tcObjectType = tPaySelLine.PaySelLineObjectType
tProposedLine.tcBusinessRelationCode = tPaySelLine.tcBusinessRelationCode
tProposedLine.tcBusinessRelationName1 = tPaySelLine.tcBusinessRelationName1
tProposedLine.tcCreditorCode = tPaySelLine.tcCreditorCode
tProposedLine.tlCreditorIsWHT = tPaySelLine.tlCreditorIsWHT
tProposedLine.tcDivisionCode = tPaySelLine.tcDivisionCode
tProposedLine.tcTSM = tPaySelLine.tcTSM
tProposedLine.tcExternalNumber = tPaySelLine.tcExternalNumber
tProposedLine.tcInternalNumber = tPaySelLine.tcInternalNumber
tProposedLine.ttDiscountDueDate = tPaySelLine.ttDiscountDueDate
tProposedLine.tdDiscountPercentage = tPaySelLine.tdDiscountPercentage
tProposedLine.ttInvoiceDate = tPaySelLine.ttInvoiceDate
tProposedLine.tiInvoiceCompany_ID = tPaySelLine.tiInvoiceCompany_ID
tProposedLine.ttDueDate = tPaySelLine.ttDueDate
tProposedLine.tcInvoiceCurrencyCode = tPaySelLine.tcInvoiceCurrencyCode
tProposedLine.tdInvoiceRate = tPaySelLine.tdInvoiceRate
tProposedLine.tdInvoiceRateScale = tPaySelLine.tdInvoiceRateScale
tProposedLine.tdOriginalInvoiceAmountTC = tPaySelLine.tdCInvoiceOriginalTC
tProposedLine.tdOriginalInvoiceAmountNetTC = tPaySelLine.tdOriginalInvoiceAmountNetTC
tProposedLine.tdOpenInvoiceAmountTC = tPaySelLine.tdOpenInvoiceAmountTC
tProposedLine.tdOpenAmountTCSigned = if tProposedLine.tcOpenInvAmntDebitCreditAbbr = vcCreditTR
then - tProposedLine.tdOpenInvoiceAmountTC
else tProposedLine.tdOpenInvoiceAmountTC
tProposedLine.tdPaymentSignedAmtTC = tPaySelLine.PaySelLineAmountTC
tProposedLine.tdDiscountTC = tPaySelLine.PaySelLineDiscountTC
tProposedLine.tdPaymentAmountTC = tPaySelLine.PaySelLineAmountTC
tProposedLine.tcPaymentAmntBCDebitCreditAbbr = tProposedLine.tcPaymentAmntDebitCreditAbbr
tProposedLine.tdInterestTC = <M-97 RoundAmount
(input abs(tPaySelLine.PaySelLineIntAmtTC) (idUnroundedAmount),
input 0 (iiCurrencyID),
input tPaySelLine.tcInvoiceCurrencyCode (icCurrencyCode)) in BPaymentSelection>
tProposedLine.tlProposedLineIsSelected = true
tProposedLine.tlProposedLineCanBeSelected = true
tProposedLine.tlIsMultiple = false
tProposedLine.tiNumDecTC = 0
tProposedLine.tiAdditionNumber = 0
tProposedLine.tiCInvoiceId = 0
tProposedLine.tiInvoiceSeq = 0
tProposedLine.tdTotalAmountTC = 0
tProposedLine.tdPaymentAmountBC = 0
tProposedLine.tdPaymentAmountSignBC = 0
tProposedLine.tdPaymentAmountBank = 0
tProposedLine.tcOriginInvAmntSign = tPaySelLine.tcInvoiceOriginalAmountSign
tProposedLine.tcOriginInvAmntDebitCreditAbbr = tPaySelLine.tcInvoiceBalanceCrDt
tProposedLine.tcOpenInvAmntDebitCreditAbbr = tPaySelLine.tcInvoiceBalanceCrDt
tProposedLine.tcPaymentAmntDebitCreditAbbr = tPaySelLine.tcPaymentCrDt
tProposedLine.tcDiscountAmntDebitCreditAbbr = tPaySelLine.tcDiscountCrDt
tProposedLine.tdWHTAmtTC = tPaySelLine.PaySelLineWHTAmtTC
tProposedLine.tdWHTAmtLC = tPaySelLine.PaySelLineWHTAmtLC
tProposedLine.tdWHTAmtCC = tPaySelLine.PaySelLineWHTAmtCC
tProposedLine.tdWHTTaxableFeeTC = <M-84 RoundAmount
(input tPaySelLine.tdWHTTaxableFeeTC (idUnroundedAmount),
input 0 (iiCurrencyID),
input tPaySelLine.tcInvoiceCurrencyCode (icCurrencyCode)) in BPaymentSelection>
tProposedLine.tdWHTPerc = tPaySelLine.tdWHTPerc
tProposedLine.tdWHTTCCCExchangeRate = tPaySelLine.tdWHTTCCCExchangeRate
tProposedLine.tdWHTTCCCRateScale = tPaySelLine.tdWHTTCCCRateScale
tProposedLine.tdWHTTCLCExchangeRate = tPaySelLine.tdWHTTCLCExchangeRate
tProposedLine.tdWHTTCLCRateScale = tPaySelLine.tdWHTTCLCRateScale
tProposedLine.tdCInvoiceOriginalTC = <M-82 RoundAmount
(input tPaySelLine.tdCInvoiceOriginalTC (idUnroundedAmount),
input 0 (iiCurrencyID),
input tPaySelLine.tcInvoiceCurrencyCode (icCurrencyCode)) in BPaymentSelection>
tProposedLine.tdInitiallyAllocatedWHTAmntTC = <M-64 RoundAmount
(input tPaySelLine.tdInitiallyAllocatedWHTAmntTC (idUnroundedAmount),
input 0 (iiCurrencyID),
input tPaySelLine.tcInvoiceCurrencyCode (icCurrencyCode)) in BPaymentSelection>
tProposedLine.tdCInvoiceWHTUnpaidAmtTC = <M-79 RoundAmount
(input tPaySelLine.tdCInvoiceWHTUnpaidAmtTC (idUnroundedAmount),
input 0 (iiCurrencyID),
input tPaySelLine.tcInvoiceCurrencyCode (icCurrencyCode)) in BPaymentSelection>
tProposedLine.tdCInvoiceTotWHTTC = <M-15 RoundAmount
(input tPaySelLine.tdCInvoiceTotWHTTC (idUnroundedAmount),
input 0 (iiCurrencyID),
input tPaySelLine.tcInvoiceCurrencyCode (icCurrencyCode)) in BPaymentSelection>
tProposedLine.tdCInvoiceWHTAmtTC = <M-49 RoundAmount
(input tPaySelLine.tdCInvoiceWHTAmtTC (idUnroundedAmount),
input 0 (iiCurrencyID),
input tPaySelLine.tcInvoiceCurrencyCode (icCurrencyCode)) in BPaymentSelection>
tProposedLine.tdTotWHTAmtLC = <M-67 RoundAmount
(input tPaySelLine.tdTotWHTAmtLC (idUnroundedAmount),
input viCompanyLCId (iiCurrencyID),
input vcCompanyLC (icCurrencyCode)) in BPaymentSelection>
tProposedLine.tdTotWHTAmtTC = <M-27 RoundAmount
(input tPaySelLine.tdTotWHTAmtTC (idUnroundedAmount),
input 0 (iiCurrencyID),
input tPaySelLine.tcInvoiceCurrencyCode (icCurrencyCode)) in BPaymentSelection>
tProposedLine.tdCInvoiceTotWHTBaseTC = <M-60 RoundAmount
(input tPaySelLine.tdCInvoiceTotWHTBaseTC (idUnroundedAmount),
input 0 (iiCurrencyID),
input tPaySelLine.tcInvoiceCurrencyCode (icCurrencyCode)) in BPaymentSelection>.
tProposedLine.tlConfirmed = tPaySelLine.PaySelLineIsConfirmed.
tProposedLine.tdCInvoiceDiscountTC = tPaySelLine.PaySelLineDiscountTC.
/*Get the companycode of the supplier invoice */
<Q-96 run CInvoiceForPayment (all) (Read) (NoCache)
(input 0, (CompanyId)
input tProposedLine.tiObject_ID, (CInvoiceId)
output dataset tqCInvoiceForPayment) in BCInvoice >
for first tqCInvoiceForPayment where
tqCInvoiceForPayment.tiCInvoice_ID = tProposedLine.tiObject_ID:
assign tProposedLine.tcInvoiceCompanyCode = tqCInvoiceForPayment.tcCompanyCode
tProposedLine.tlCInvoiceIsDiscTaxAtPaym = tqCInvoiceForPayment.tlCInvoiceIsDiscTaxAtPaym
tProposedLine.tdNonDiscAmtTC = tqCInvoiceForPayment.tdCInvoiceNonDiscAmtTC
tProposedLine.tcObjectType = if vcActivityCode = 'View':U and
tPaySelLine.PaySelLineObjectType = {&PAYMENTSELECTIONTYPE-INVOICE} and
tqCInvoiceForPayment.tcCInvoiceType = {&INVOICETYPE-PREPAYMENT}
then {&PAYMENTSELECTIONTYPE-PREPAYMENT}
else tPaySelLine.PaySelLineObjectType.
end.
/* Process Stages */
assign vdAppliedDefaultWhtTC = 0
vdInvUnpaidWhtTC = tPaySelLine.tdCInvoiceWHTAmtTC.
for each tPaySelLineStage where
tPaySelLineStage.PaySelLine_ID = tPaySelLine.payselline_id
break by tPaySelLineStage.PaySelLine_ID:
<Q-16 run CinvoiceStageByID (all) (Read) (NoCache)
(input tPaySelLineStage.CInvoiceStage_ID, (CinvoiceStageID)
output dataset tqCinvoiceStageByID) in BCInvoice >
find first tqCinvoiceStageByID.
create tProposedLineStage.
assign tProposedLineStage.tiProposedLine_ID = viProposedLine_ID
tProposedLineStage.tdOpenStageAmountTC = tPaySelLineStage.PaySelLineStageOpenAmntTC
tProposedLineStage.tdOriginalStageAmounTC = tPaySelLineStage.PaySelLineStageOrigAmntTC
tProposedLineStage.tdStageDiscountAmountTC = tPaySelLineStage.PaySelLineStageDiscAmntTC
tProposedLineStage.tdStageInterestAmountTC = tPaySelLineStage.PaySelLineStageIntAmtTC
tProposedLineStage.tdStagePaymentAmountTC = tPaySelLineStage.PaySelLineStagePaymAmntTC
tProposedLineStage.tdWHTAmtTC = tPaySelLineStage.PaySelLineStageWHTAmtTC
tProposedLineStage.tiCInvoiceStage_ID = tPaySelLineStage.CInvoiceStage_ID
tProposedLineStage.tiObject_ID = tProposedLine.tiObject_ID
tProposedLineStage.tiBankNumber_ID = tProposedLine.tiBankNumber_ID
tProposedLineStage.ttStageDiscountDueDate = tqCinvoiceStageByID.ttCInvoiceStageDiscDate
tProposedLineStage.tdStageDiscountPercentage = tqCinvoiceStageByID.tdPaymentConditionPercentage.
/* Distribute WHT amount to stages */
if not last-of(tPaySelLineStage.PaySelLine_ID)
then assign tProposedLineStage.tdDefaultWhtAmtTC = vdInvUnpaidWhtTC
* (tqCinvoiceStageByID.tdCInvoiceStageAmountTC - tqCinvoiceStageByID.tdCInvoiceStageAmtAppliedTC)
/ abs(tPaySelLine.tdOpenInvoiceAmountTC)
tProposedLineStage.tdDefaultWhtAmtTC = <M-8 RoundAmount
(input tProposedLineStage.tdDefaultWhtAmtTC (idUnroundedAmount),
input ? (iiCurrencyID),
input tProposedLine.tcInvoiceCurrencyCode (icCurrencyCode)) in BPaymentSelection>.
else assign tProposedLineStage.tdDefaultWhtAmtTC = vdInvUnpaidWhtTC - vdAppliedDefaultWhtTC.
assign vdAppliedDefaultWhtTC = vdAppliedDefaultWhtTC + tProposedLineStage.tdDefaultWhtAmtTC.
/* Calculate discount amount */
if tPaySel.PaySelDate <= tProposedLineStage.ttStageDiscountDueDate
then do:
if tProposedLine.tlCInvoiceIsDiscTaxAtPaym
then assign tProposedLineStage.tdDefaultStageDiscountAmountTC = (tProposedLineStage.tdOpenStageAmountTC -
(tProposedLine.tdNonDiscAmtTC * (tProposedLineStage.tdOpenStageAmountTC / tProposedLineStage.tdOriginalStageAmounTC) * tqCinvoiceStageByID.tdPaymentConditionPercentage / 100)) * /* part of non-discountable amount for the stage */
tProposedLineStage.tdStageDiscountPercentage / 100.
else assign tProposedLineStage.tdDefaultStageDiscountAmountTC = ((tProposedLineStage.tdOpenStageAmountTC * tProposedLine.tdOriginalInvoiceAmountNetTC / tProposedLine.tdOriginalInvoiceAmountTC) - /* net amount of the stage - without taxes */
(tProposedLine.tdNonDiscAmtTC * (tProposedLineStage.tdOpenStageAmountTC / tProposedLineStage.tdOriginalStageAmounTC) * tqCinvoiceStageByID.tdPaymentConditionPercentage / 100)) * /* part of non-discountable amount for the stage */
tProposedLineStage.tdStageDiscountPercentage / 100.
assign tProposedLineStage.tdDefaultStageDiscountAmountTC = <M-40 RoundAmount
(input tProposedLineStage.tdDefaultStageDiscountAmountTC (idUnroundedAmount),
input ? (iiCurrencyID),
input tProposedLine.tcInvoiceCurrencyCode (icCurrencyCode)) in BPaymentSelection>
tProposedLineStage.tdDefaultStagePaymentAmountTC = tProposedLineStage.tdOpenStageAmountTC
- tProposedLineStage.tdDefaultStageDiscountAmountTC
- tProposedLineStage.tdDefaultWhtAmtTC.
end. /* if tPaySel.PaySelDate < tProposedLineStage.ttStageDiscountDueDate */
else assign tProposedLineStage.tdDefaultStageDiscountAmountTC = 0
tProposedLineStage.tdDefaultStagePaymentAmountTC = tProposedLineStage.tdOpenStageAmountTC - tProposedLineStage.tdDefaultWhtAmtTC.
/* Calculate allocated amount */
assign tProposedLineStage.tdStageAllocAmountTC = (tProposedLineStage.tdStagePaymentAmountTC - tProposedLineStage.tdStageInterestAmountTC)
+ tProposedLineStage.tdStageDiscountAmountTC
+ tProposedLineStage.tdWHTAmtTC.
end. /* for each tPaySelLineStage where */
assign viProposedLine_ID = viProposedLine_ID + 1.
<Q-5 run BankNumberByID (all) (Read) (Cache)
(input tPaySelLine.BankNumber_ID, (BankNumber_ID)
input ?, (CompanyId)
output dataset tqBankNumberByID) in BBankNumber >
find first tqBankNumberByID no-error.
if available tqBankNumberByID
then assign tProposedLine.tcBankAccountNumber = if tqBankNumberByID.tcBankNumberFormatted <> "":U and
tqBankNumberByID.tcBankNumberFormatted <> ?
then tqBankNumberByID.tcBankNumberFormatted
else tqBankNumberByID.tcBankNumber
tProposedLine.tcBankAccountExtension = tqBankNumberByID.tcBankNumberExtension
tProposedLine.tcValidationMask = tqBankNumberByID.tcBankNumberValidation.
assign tProposedLine.tcPaymentAmntBCDebitCreditAbbr = tProposedLine.tcPaymentAmntDebitCreditAbbr
tProposedLine.tcWHTAmntDebitCreditAbbr = tProposedLine.tcDiscountAmntDebitCreditAbbr.
<Q-59 run CInvoiceBankByPayFormatType (all) (Read) (NoCache)
(input ?, (CompanyId)
input ?, (PayFormatTypeCode)
input tProposedLine.tiObject_ID, (InvoiceID)
input tPaySelLine.BankNumber_ID, (BankNumberID)
output dataset tqCInvoiceBankByPayFormatType) in BCInvoice>
find first tqCInvoiceBankByPayFormatType where
tqCInvoiceBankByPayFormatType.tiCInvoice_ID = tProposedLine.tiObject_ID and
tqCInvoiceBankByPayFormatType.tiBankNumber_ID = tPaySelLine.BankNumber_ID
no-lock no-error.
assign tProposedLine.tdCInvoiceBankToPayTC = if available tqCInvoiceBankByPayFormatType
then tqCInvoiceBankByPayFormatType.tdCInvoiceBankToPayTC
else 0.
/* Populate Hold Amount in case it's supplier / creditor invoice, invoice corr, credit note, credit note corr. */
if tPaySelLine.PaySelLineObjectType = {&PAYMENTSELECTIONTYPE-INVOICE} or
tPaySelLine.PaySelLineObjectType = {&PAYMENTSELECTIONTYPE-INVOICECORR} or
tPaySelLine.PaySelLineObjectType = {&PAYMENTSELECTIONTYPE-CREDITNOTE} or
tPaySelLine.PaySelLineObjectType = {&PAYMENTSELECTIONTYPE-CREDITNOTECORR}
then do:
assign tProposedLine.tdHoldAmountTC = tPaySelLine.tdCInvoiceHoldAmountTC.
end.
end.