Parameters
| oiReturnStatus | output | integer | Return status of the method. |
Internal usage
QadFinancials
program code (program6/bbankstateimport.p)
assign oiReturnStatus = -98.
ASSIGN viCnt = 0
vdtotal = 0
vdtotalcredits = 0
vdtotaldebits = 0
vdtotalMvmtsPerBalance = 0
viLineCount = 0
vlSwift = false
vlIsPrevLine86 = false
viPrevLine86Id = 0.
for each tBankStateImportFileContent:
if left-trim(tBankStateImportFileContent.tcBankStateFileContentLine, '~'':U) begins "940":U
then do:
assign vlSwift = true.
leave.
end.
end.
if not vlSwift
then do:
assign oiReturnStatus = -1
vcmsgReturn = trim(#T-40'The file you specified is not a SWIFT-MT940 structured file.':255(17911)T-40#).
<M-39 run SetMessage (input vcMsgReturn (icMessage),
input '':U (icArguments),
input '':U (icFieldName),
input '':U (icFieldValue),
input 'E':U (icType),
input 3 (iiSeverity),
input '':U (icRowid),
input 'QADFIN-4507':U (icFcMsgNumber),
input '' (icFcExplanation),
input '' (icFcIdentification),
input '' (icFcContext),
output viFcReturnSuper (oiReturnStatus)) in BBankStateImport>
end.
swiftfile:
for each tBankStateImportFileContent :
assign viLineCount = viLineCount + 1
viCnt = viLineCount
vcDocLine = left-trim( tBankStateImportFileContent.tcBankStateFileContentLine,'~'':U).
vcMsgLine = substitute(#T-25'LINE &1':11(12276)T-25#, string(viLineCount)).
IF (NUM-ENTRIES(vcDocLine,":":U) < 3 and not vlIsPrevLine86)
OR vcDocLine = "":U
THEN DO:
NEXT swiftfile.
END.
CASE entry(2,vcDocLine,":":U):
/* HEADER */
when "940":U
then do:
assign vlIsPrevLine86 = false
viPrevLine86Id = 0.
end. /* when "940":U*/
/* Betaling Referentienummer */
when "20":U
then do:
assign vlIsPrevLine86 = false
viPrevLine86Id = 0.
END.
/* rekeningnummer */
when "25":U
then do:
assign vlIsPrevLine86 = false
viPrevLine86Id = 0
vcdummy = trim(substring(vcDocLine,5,35,"CHARACTER":U))
viSequence = 0
vcBankAccountInFile = vcdummy.
if length(vcdummy,"CHARACTER":U) > 3
then do:
assign viDummy = integer(substring(vcdummy,length(vcdummy,"CHARACTER":U) - 2, 3, "CHARACTER":U)) no-error.
if error-status:error
then assign vcDummy = substring(vcDummy,1,length(vcdummy,"CHARACTER":U) - 3,"CHARACTER":U).
end.
if vcBankAccountInFile <> '':U
then do :
/* Get the BankNumber */
<Q-19 run BanknumberByBankNumberCurrency (all) (Read) (NoCache)
(input viCompanyId, (CompanyId)
input vcBankAccountInFile, (Banknumber)
input true, (BankNumberIsActive)
input {&BANKNUMBERPARENTTYPE-GL}, (BankNumberParentType)
input '':U, (CurrencyCode)
input ?, (CurrencyID)
output dataset tqBanknumberByBankNumberCurrency) in BBankNumber >
find first tqBanknumberByBankNumberCurrency no-error.
end.
if vcBankAccountInFile = '':U or not available tqBanknumberByBankNumberCurrency
then do:
assign oiReturnStatus = -1
vcmsgReturn = substitute(trim(#T-41'The bank account number $1 cannot be found.':100(56418)t-41#),vcmsgLine,vcBankAccountInFile).
<M-20 run SetMessage
(input vcMsgReturn (icMessage),
input '':U (icArguments),
input 'vcBankAccountInFile':U (icFieldName),
input vcBankAccountInFile (icFieldValue),
input 'E':U (icType),
input 3 (iiSeverity),
input '':U (icRowid),
input 'QADFIN-3000':U (icFcMsgNumber),
input '':U (icFcExplanation),
input '':U (icFcIdentification),
input '':U (icFcContext),
output viFcReturnSuper (oiReturnStatus)) in BBankStateImport>
end.
else do:
<Q-21 run GLById (all) (Read) (NoCache)
(input viCompanyId, (CompanyId)
input tqBanknumberByBankNumberCurrency.tiParentObject_ID, (GLId)
output dataset tqGLById) in BGL >
find first tqGLById no-lock no-error.
if not available tqGLById then do:
assign oiReturnStatus = -1
vcmsgReturn = substitute(trim(#T-29'&1 position 6: No GL account corresponding to the bank account &2 exists.':255(12284)t-29#), vcmsgLine , "":U).
<M-22 run SetMessage (input vcMsgReturn (icMessage),
input '':U (icArguments),
input '':U (icFieldName),
input '':U (icFieldValue),
input 'E':U (icType),
input 3 (iiSeverity),
input '':U (icRowid),
input 'QADFIN-3001':U (icFcMsgNumber),
input '' (icFcExplanation),
input '' (icFcIdentification),
input '' (icFcContext),
output viFcReturnSuper (oiReturnStatus)) in BBankStateImport>
end.
else assign vcGLCode = tqGLById.tcGLCode
viGLID = tqGLById.tiGL_ID.
end.
/* Get the Currency Field */
<Q-23 run GLByGLIdMatchedCode (all) (Read) (NoCache)
(input viCompanyId, (CompanyId)
input ?, (GLId)
input vcGLCode, (GLCode)
output dataset tqGLByGLIdMatchedCode) in BGL >
find first tqGLByGLIdMatchedCode no-lock no-error.
if available tqGLByGLIdMatchedCode and not tqGLByGLIdMatchedCode.tlGLIsLocalCurrency
THEN assign vcGLCurrencyCode = tqGLByGLIdMatchedCode.tcCurrencyCode
viGLCurrencyId = tqGLByGLIdMatchedCode.tiCurrency_ID.
else assign vcGLCurrencyCode = vcCompanyLC
viGLCurrencyId = viCompanyLCId.
/*MESSAGE BankAccountInFile VIEW-AS ALERT-BOX INFO BUTTONS OK.*/
end. /* when "25":U*/
/* dagafschriftnummer / volgnummer */
when "28":U or WHEN "28C":U
then do:
assign vlIsPrevLine86 = false
viPrevLine86Id = 0
vcDocLine = LEFT-TRIM(vcDocLine,":":U + entry(2,vcDocLine,":":U) + ":":U).
if num-entries(vcDocLine,"/":U) = 2
then assign viBankStateNumber = int(entry(1,vcDocLine,"/":U))
viBankstatelineNumber = int(entry(2,vcDocLine,"/":U)) NO-ERROR.
else do:
assign viBankStateNumber = int(vcDocLine)
viSequence = viSequence + 1
viBankstatelineNumber = viSequence NO-ERROR.
end.
end. /* when "28":U*/
/* boekhoudkunding saldo oud */
when "60F":U or when "60M":U
then do:
/* *******************************************************************/
/* OPENING BALANCE */
/* *******************************************************************/
assign vlIsPrevLine86 = false
viPrevLine86Id = 0.
/* check corresponding closage of the balance, cannot open without */
if vlbalanceOpen
then do:
assign
oiReturnStatus = -1
vcmsgReturn = substitute(trim(#T-30'&1: The previous opening balance has no corresponding closing balance.':255(12282)T-30#),vcmsgLine).
<M-13 run SetMessage (input vcMsgReturn (icMessage),
input '':U (icArguments),
input '':U (icFieldName),
input '':U (icFieldValue),
input 'E':U (icType),
input 3 (iiSeverity),
input '':U (icRowid),
input 'QADFIN-2995':U (icFcMsgNumber),
input '' (icFcExplanation),
input '' (icFcIdentification),
input '' (icFcContext),
output viFcReturnSuper (oiReturnStatus)) in BBankStateImport>
LEAVE swiftfile.
end.
else assign vlbalanceOpen = yes.
/* Sign of the openingsbalance */
assign vcSign = (IF SUBSTRING(vcDocLine, 6,1,"CHARACTER":U) = 'D':U then '-':U else '+':U)
vdopeningBalance = dec(VCsIGN + replace(SUBSTRING(vcDocLine, 16,length(vcDocLine,"CHARACTER":U),"CHARACTER":U),",":U,".":U))
vcCurrencyCode = SUBSTRING(vcDocLine, 13,3,"CHARACTER":U)
viTempBankStateID = viLineCount.
create tApiBankStateBSI.
assign tApiBankStateBSI.Company_ID = viCompanyId
tApiBankStateBSI.BankState_ID = viLineCount
tApiBankStateBSI.BankStateNumber = string(viBankStateNumber)
tApiBankStateBSI.BankStateStatus = {&BANKSTATESTATUS-UNALLOC}
tApiBankStateBSI.BankStateTransactionType = {&BANKSTATETRANSTYPE-BANK}
tApiBankStateBSI.BankStateOpeningBalance = vdopeningBalance
tApiBankStateBSI.tc_Rowid = string(viCnt)
vdtotal = vdtotal + 1.
end. /* when "60F":U*/
/* *******************************************************************/
/* Detail Lines */
/* *******************************************************************/
/* transactie */
when "61":U
then do:
assign vlIsPrevLine86 = false
viPrevLine86Id = 0.
if not available tApiBankStateBSI
then do :
assign oiReturnStatus = -1
vcmsgReturn = substitute(#T-47'&1: This line of type &2 contains information about the header but the header is created in lines with type &3 or &4 and this has to proceed the current line.':255(69599)T-47#,vcmsgLine,"61":U,"60F":U,"60M":U).
<M-46 run SetMessage (input vcMsgReturn (icMessage),
input '':U (icArguments),
input '':U (icFieldName),
input '':U (icFieldValue),
input 'E':U (icType),
input 3 (iiSeverity),
input '':U (icRowid),
input 'QadFin-8184':U (icFcMsgNumber),
input '' (icFcExplanation),
input '' (icFcIdentification),
input '' (icFcContext),
output viFcReturnSuper (oiReturnStatus)) in BBankStateImport>
next.
end. /* if not available tApiBankStateBSI */
/* Format: 6!n[4!n]2a[1!a]15d1!a3!c16x[//16x] */
assign viCnt = viCnt + 1
vcDocLine = LEFT-TRIM(vcDocLine,":61:":U).
if index("RDC":U,substring(vcDocLine,7,1,"CHARACTER":U)) > 0
then assign viInLineCounter = 7. /* Only Value date, No Entry Date specified */
else assign viInLineCounter = 11. /* Both Value date and Entry Date specified */
/* Debit Credit Mark */
if SUBSTRING(vcDocLine, viInLineCounter,1,"CHARACTER":U) = "D":U
or SUBSTRING(vcDocLine, viInLineCounter,2,"CHARACTER":U) = "RD":U
then do:
assign vcSign = '-':U
vlRCRD = SUBSTRING(vcDocLine, viInLineCounter,2,"CHARACTER":U) = "RD":U.
end.
else
if SUBSTRING(vcDocLine, viInLineCounter,1,"CHARACTER":U) = "C":U
or SUBSTRING(vcDocLine, viInLineCounter,2,"CHARACTER":U) = "RC":U
then do:
assign vcSign = '+':U
vlRCRD = SUBSTRING(vcDocLine, viInLineCounter,2,"CHARACTER":U) = "RC":U.
end.
assign viInLineCounter = (if vlRCRD then viInLineCounter + 2 else viInLineCounter + 1).
/* Fund code */
if SUBSTRING(vcDocLine, viInLineCounter,1,"CHARACTER":U) >= 'a':U and
SUBSTRING(vcDocLine, viInLineCounter,1,"CHARACTER":U) <= 'z':U
then assign viInLineCounter = viInLineCounter + 1.
/* Amount */
assign vcAmountInFile = SUBSTRING(vcDocLine, viInLineCounter,15,"CHARACTER":U)
vcAmount = "":U
viLength = length(vcAmountInFile,"CHARACTER":U).
getAmmount:
do viCounter = 1 to viLength :
if index("1234567890.,":U,substring(vcAmountInFile,viCounter,1,"CHARACTER":U)) > 0
then assign vcAmount = vcAmount + substring(vcAmountInFile,viCounter,1,"CHARACTER":U).
else leave getAmmount.
ASSIGN viInLineCounter = viInLineCounter + 1.
end.
ASSIGN vcamount = REPLACE(vcamount,",":U,".":U) .
CASE vcSign :
WHEN "-":U
THEN DO:
assign vdtotaldebits = vdtotaldebits + dec(vcAmount).
END.
WHEN "+":U
THEN DO:
assign vdtotalcredits = vdtotalcredits + dec(vcAmount).
END.
END CASE.
assign vdTotal = vdTotal + dec(vcSign + vcAmount)
vdtotalMvmtsPerBalance = vdtotalMvmtsPerBalance + dec(vcSign + vcAmount).
/* Transaction Type Identification Code */
assign viInLineCounter = viInLineCounter + 4 .
/* Reference fot the Account Owner */
assign vcBankNumberExt = TRIM(SUBSTRING(vcDocLine, viInLineCounter,16,"CHARACTER":U))
viInLineCounter = viInLineCounter + 16.
create tApiBankStateLineBSI.
assign tApiBankStateLineBSI.tc_Rowid = string(viCnt + 1000000)
tApiBankStateLineBSI.tc_ParentRowid = tApiBankStateBSI.tc_Rowid
tApiBankStateLineBSI.Company_ID = viCompanyId
tApiBankStateLineBSI.BankStateLine_ID = viCnt + 1000000
tApiBankStateLineBSI.BankState_ID = viTempBankStateID
tApiBankStateLineBSI.BankStateLineAmountTC = dec(VCsIGN + vcAmount)
tApiBankStateLineBSI.BankStateLineExtOriginalTC = dec(VCsIGN + vcAmount)
tApiBankStateLineBSI.BankStateLineDescription = trim(SUBSTRING(vcDocLine, viInLineCounter,49,"CHARACTER":U))
tApiBankStateLineBSI.BankStateLineNumber = viBankstatelineNumber
viBankstatelineNumber = viBankstatelineNumber + 1
tApiBankStateLineBSI.BankStateLineExtBankNumber = vcBankNumberExt
tApiBankStateLineBSI.BankStateLineExtName = trim(SUBSTRING(vcDocLine, viInLineCounter,49,"CHARACTER":U))
tApiBankStateLineBSI.BankStateLineExtCostCode = vcCostCode
tApiBankStateLineBSI.BankStateLineStatus = {&BANKSTATELINESTATUS-UNALLOC}
tApiBankStateLineBSI.BankStateLineInOut = vcSign
tApiBankStateLineBSI.BankStateLineValueDate = date(SUBSTRING(vcDocLine, 3,4,"CHARACTER":U) + SUBSTRING(vcDocLine, 1,2,"CHARACTER":U))
tApiBankStateLineBSI.BankStateLinePostingDate = vtPostingDate
tApiBankStateBSI.Currency_ID = viGLCurrencyId
tApiBankStateBSI.tcCurrencyCode = vcGLCurrencyCode
tApiBankStateBSI.tcGLCode = vcGLCode
tApiBankStateBSI.GL_ID = viGLID.
end. /* when "61":U*/
/* *******************************************************************/
/* INFORMATION RECORDS */
/* *******************************************************************/
/* Omschrijving */
when "86":U then do:
if not available tApiBankStateLineBSI
then do :
assign oiReturnStatus = 1
vcmsgReturn = substitute(#T-43'&1: This line of type &2 contains information about the lines but the lines are created in lines with type &3 and this has to proceed the current line.':255(69596)T-43#,vcmsgLine,"86":U,"61":U).
<M-42 run SetMessage
(input vcMsgReturn (icMessage),
input '':U (icArguments),
input '':U (icFieldName),
input '':U (icFieldValue),
input 'W':U (icType),
input 3 (iiSeverity),
input '':U (icRowid),
input 'QadFin-8182':U (icFcMsgNumber),
input '' (icFcExplanation),
input '' (icFcIdentification),
input '' (icFcContext),
output viFcReturnSuper (oiReturnStatus)) in BBankStateImport>
end. /* if not available tApiBankStateLineBSI */
else do :
IF LENGTH(tApiBankStateLineBSI.BankStateLineDescription,"CHARACTER":U) < 128
THEN assign tApiBankStateLineBSI.BankStateLineDescription = tApiBankStateLineBSI.BankStateLineDescription + " ":U + trim(SUBSTRING(vcDocLine, 5,65,"CHARACTER":U)).
assign tApiBankStateLineBSI.BankStateLineDescription = substring(tApiBankStateLineBSI.BankStateLineDescription,1,40,"CHARACTER":U).
assign tApiBankStateLineBSI.BankStateLineInformation = trim(entry(3,vcDocLine,":":U))
vlIsPrevLine86 = true
viPrevLine86Id = tApiBankStateLineBSI.BankStateLine_ID.
end. /* if available tApiBankStateLineBSI */
end. /* when "86":U*/
/* *******************************************************************/
/* CLOSING BALANCE */
/* *******************************************************************/
/* boekhoudkundig saldo nieuw */
when "62F":U
OR when "62M":U then do:
assign vlIsPrevLine86 = false
viPrevLine86Id = 0.
if not available tApiBankStateBSI
then do :
assign oiReturnStatus = -1
vcmsgReturn = substitute(#T-45'&1: This line of type &2 or &3 contains information about the header but the header is created in lines with type &4 or &5 and this has to proceed the current line.':255(69598)T-45#,vcmsgLine,"62M":U,"62F":U,"60F":U,"60M":U).
<M-44 run SetMessage (input vcMsgReturn (icMessage),
input '':U (icArguments),
input '':U (icFieldName),
input '':U (icFieldValue),
input 'E':U (icType),
input 3 (iiSeverity),
input '':U (icRowid),
input 'QadFin-8183':U (icFcMsgNumber),
input '' (icFcExplanation),
input '' (icFcIdentification),
input '' (icFcContext),
output viFcReturnSuper (oiReturnStatus)) in BBankStateImport>
next.
end. /* if not available tApiBankStateBSI */
/* Sign of the closingbalance */
assign vcSign = (IF SUBSTRING(vcDocLine, 6,1,"CHARACTER":U) = 'D':U then '-':U else '+':U).
ASSIGN vdClosingBalance = dec(VCsIGN + replace(SUBSTRING(vcDocLine, 16,length(vcDocLine,"CHARACTER":U),"CHARACTER":U),",":U,".":U)).
/* check if total of movements + opening balance = closing balance */
if vdopeningBalance + vdtotalMvmtsPerBalance <> vdClosingBalance
then do:
assign oiReturnStatus = -1
vcmsgReturn = substitute(#T-24'&1 position 43: The closing balance &4 does not equal the opening balance &2 plus activity &3.':255(12342)t-24#,vcmsgLine, string(tApiBankStateBSI.BankStateOpeningBalance) , string(vdtotalMvmtsPerBalance), VCsIGN + SUBSTRING(vcDocLine, 16,15,"CHARACTER":U)).
<M-17 run SetMessage (input vcMsgReturn (icMessage),
input '':U (icArguments),
input '':U (icFieldName),
input '':U (icFieldValue),
input 'E':U (icType),
input 3 (iiSeverity),
input '':U (icRowid),
input 'QADFIN-2997':U (icFcMsgNumber),
input '' (icFcExplanation),
input '' (icFcIdentification),
input '' (icFcContext),
output viFcReturnSuper (oiReturnStatus)) in BBankStateImport>
end.
ASSIGN tApiBankStateBSI.BankStateMovement = vdtotalMvmtsPerBalance
vdtotalMvmtsPerBalance = 0
vlbalanceOpen = NO
tApiBankStateBSI.BankStateClosingBalance = vdClosingBalance.
assign vtPostingDate = date(SUBSTRING(vcDocLine, 9,4,"CHARACTER":U) + SUBSTRING(vcDocLine, 7,2,"CHARACTER":U))
tApiBankStateBSI.BankStatePostingDate = vtPostingDate.
/* Get the Posting Year/Period/Date based on the input date */
<Q-33 run PeriodByStartEndDate (first) (Read) (NoCache)
(input viCompanyId, (CompanyId)
input vtPostingDate, (Date)
output dataset tqPeriodByStartEndDate) in BPeriod >
find first tqPeriodByStartEndDate no-lock no-error.
if available tqPeriodByStartEndDate
then assign viPeriodID = tqPeriodByStartEndDate.tiPeriod_ID
viPeriodPeriod = tqPeriodByStartEndDate.tiPeriodPeriod
viPeriodYear = tqPeriodByStartEndDate.tiPeriodYear
tApiBankStateBSI.BankStateYear = tqPeriodByStartEndDate.tiPeriodYear.
else do:
assign oiReturnStatus = -1
viPeriodID = ?
vcmsgReturn = substitute(trim(#T-35'&1 - No matching GL period found for posting date &2.':255(12344)T-35#), vcmsgLine, vtPostingDate).
<M-34 run SetMessage (input vcMsgReturn (icMessage),
input '':U (icArguments),
input '':U (icFieldName),
input '':U (icFieldValue),
input 'E':U (icType),
input 3 (iiSeverity),
input '':U (icRowid),
input 'QADFIN-3485':U (icFcMsgNumber),
input '' (icFcExplanation),
input '' (icFcIdentification),
input '' (icFcContext),
output viFcReturnSuper (oiReturnStatus)) in BBankStateImport>
end.
/* Check Uniqueness */
<Q-36 assign vlFcQueryRecordsAvailable = BankStateByYear (NoCache)
(input viCompanyId, (CompanyId)
input string(viBankStateNumber), (BankStateNumber)
input tqGLByGLIdMatchedCode.tiGL_ID, (GLID)
input year(vtPostingDate), (BankStateYear)) in BBankEntry >
if vlFcQueryRecordsAvailable <> false
then do:
assign oiReturnStatus = -1
vcmsgReturn = substitute(trim(#T-38'The bank statement number &2 already exists.':255(14950)t-38#),string(viLineCount - 1), string(viBankStateNumber) + '/':U + string(year(vtPostingDate)) ).
<M-37 run SetMessage (input vcMsgReturn (icMessage),
input '':U (icArguments),
input '':U (icFieldName),
input '':U (icFieldValue),
input 'E':U (icType),
input 3 (iiSeverity),
input '':U (icRowid),
input 'QADFIN-3535':U (icFcMsgNumber),
input '' (icFcExplanation),
input '' (icFcIdentification),
input '' (icFcContext),
output viFcReturnSuper (oiReturnStatus)) in BBankStateImport>
end.
assign tApiBankStateBSI.tiPeriodYear = viPeriodYear
tApiBankStateBSI.BankStateYear = viPeriodYear
tApiBankStateBSI.tiPeriodPeriod = viPeriodPeriod
tApiBankStateBSI.Period_ID = viPeriodID.
assign tApiBankStateBSI.Currency_ID = viGLCurrencyId
tApiBankStateBSI.tcCurrencyCode = vcGLCurrencyCode
tApiBankStateBSI.tcGLCode = vcGLCode
tApiBankStateBSI.GL_ID = viGLID.
for each tApiBankStateLineBSI where
tApiBankStateLineBSI.tc_ParentRowid = tApiBankStateBSI.tc_Rowid:
assign tApiBankStateLineBSI.BankStateLinePostingDate = vtPostingDate
tApiBankStateLineBSI.tiPeriodYear = viPeriodYear
tApiBankStateLineBSI.tiPeriodPeriod = viPeriodPeriod
tApiBankStateLineBSI.Period_ID = viPeriodID
tApiBankStateBSI.BankStateYear = viPeriodYear.
end.
end. /* when "62F":U*/
/* valutair saldo per boekdatum */
when "64":U then do:
assign vcSign = (IF SUBSTRING(vcDocLine, 5,1,"CHARACTER":U) = 'D':U then '-':U else '+':U)
vlIsPrevLine86 = false
viPrevLine86Id = 0.
end. /* when "64":U*/
/* voruitliggend valutair saldo */
when "65":U then do:
assign vcSign = (IF SUBSTRING(vcDocLine, 5,1,"CHARACTER":U) = 'D':U then '-':U else '+':U)
vlIsPrevLine86 = false
viPrevLine86Id = 0.
end. /* when "65":U*/
END CASE.
if vlIsPrevLine86 and SUBSTRING(vcDocLine, 1,4,"CHARACTER":U) <> ":86:":U then do:
for first tApiBankStateLineBSI where tApiBankStateLineBSI.BankStateLine_ID = viPrevLine86Id:
assign tApiBankStateLineBSI.BankStateLineInformation = tApiBankStateLineBSI.BankStateLineInformation + vcDocLine.
end.
end.
END.
if oiReturnStatus = -98
then assign oiReturnStatus = 0.