Description
!!!!! THIS METHOD SHOULD ALLWAYS RUN IN SAME SEGMENT AS AdditionalUpdatedCodaStart !!!!!!
Conversion of a CODA file to the required TT for usage in BBankentry Component
Parameters
| oiLineCounter | output | integer | LineCounter |
| ocDocumentLine | output | character | DocumentLine |
| ocLastRecordType | output | character | LastRecordType |
| oiReturnStatus | output | integer | Return status of the method. |
Internal usage
QadFinancials
program code (program6/bbankstateimport.p)
ASSIGN viCnt = 0
vdtotal = 0
vdtotalcredits = 0
vdtotaldebits = 0
vdtotalMvmtsPerBalance = 0
viLineCount = 0.
codafile:
for each tBankStateImportFileContent :
assign viLineCount = viLineCount + 1
oiLineCounter = viLineCount
vcDocLine = left-trim( tBankStateImportFileContent.tcBankStateFileContentLine,'~'':U)
ocDocumentLine = vcDocLine
vcMsgLine = substitute(trim(#T-18'LINE &1':11(12276)T-18#), string(viLineCount)).
IF (viLineCount = 1 AND SUBSTRING(vcDocLine, 1,1,"CHARACTER":U) <> "0":U) OR
length(trim(vcDocLine),"CHARACTER":U) > 128
then do:
assign oiReturnStatus = -1
vcmsgReturn = subst(trim(#T-19'&1 The file you specified is not in CODA format.':255(12277)T-19#),vcmsgLine).
<M-1 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-2971':U (icFcMsgNumber),
input '' (icFcExplanation),
input '' (icFcIdentification),
input '' (icFcContext),
output viFcReturnSuper (oiReturnStatus)) in BBankStateImport>
leave codafile.
end.
if vclastRecordType <> '9':U AND SUBSTRING(vcDocLine, 1,1,"CHARACTER":U) <> '':U
then assign vclastRecordType = SUBSTRING(vcDocLine, 1,1,"CHARACTER":U)
ocLastRecordType = vclastRecordType.
CASE SUBSTRING(vcDocLine, 1,1,"CHARACTER":U):
/* HEADER */
WHEN '0':U
THEN DO:
/* check if the file is coda-file */
if substring(vcDocLine, 15,2,"CHARACTER":U) = '05':U
then ASSIGN vlIsCoda = TRUE.
else DO:
assign oiReturnStatus = -1
vcmsgReturn = substitute(trim(#T-20'&1: The file you specified is not in CODA format.':255(12278)T-20#),vcmsgLine).
<M-2 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-2978':U (icFcMsgNumber),
input '' (icFcExplanation),
input '' (icFcIdentification),
input '' (icFcContext),
output viFcReturnSuper (oiReturnStatus)) in BBankStateImport>
leave codafile.
end. /* End of check if codafile */
if substring(vcDocLine, 17,1,"CHARACTER":U) = 'D':U /* check if is a duplicate coda-file */
then do:
assign oiReturnStatus = -1
vcmsgReturn = subst(trim(#T-21'&1: Duplicate activity found.':255(12279)T-21#),vcmsgLine)
vlIsDuplicate = yes.
<M-3 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-2979':U (icFcMsgNumber),
input '' (icFcExplanation),
input '' (icFcIdentification),
input '' (icFcContext),
output viFcReturnSuper (oiReturnStatus)) in BBankStateImport>
leave codafile.
end.
else assign vlIsDuplicate = no.
/* validate if VATNr is valid VAT number current Company */
<Q-4 run VatNumberForMyCompany (all) (Read) (NoCache)
(input viCompanyId, (CompanyId)
input '':U, (IdentityCountryCode)
output dataset tqVatNumberForMyCompany) in BBusinessRelation >
find first tqVatNumberForMyCompany no-lock no-error.
if available tqVatNumberForMyCompany
then do:
assign vcVAtNumber = (if substring(vcDocLine, 72,11,"CHARACTER":U) = "":U or substring(vcDocLine, 72,11,"CHARACTER":U) = "00000000000":U
then substring(vcDocLine, 63,9,"CHARACTER":U)
else substring(vcDocLine, 74,9,"CHARACTER":U)).
if replace(tqVatNumberForMyCompany.tcVatNumberIdentity,'.':U,'':U) <> string(integer(vcVAtNumber))
then do:
assign oiReturnStatus = -1
vcmsgReturn = subst(trim(#T-31'&1 POSITION 72: The tax number of your entity &2 does not correspond with the tax number in the CODA file &3.':255(17913)T-31#), vcmsgLine, tqVatNumberForMyCompany.tcVatNumberIdentity, vcVAtNumber).
<M-5 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-2980':U (icFcMsgNumber),
input '' (icFcExplanation),
input '' (icFcIdentification),
input '' (icFcContext),
output viFcReturnSuper (oiReturnStatus)) in BBankStateImport>
end.
<Q-6 run CountryByCode (all) (Read) (NoCache)
(input {&BANKNUMBERVALIDATION-BE}, (CountryCode)
output dataset tqCountryByCode) in BCountry >
find first tqCountryByCode no-lock no-error.
if available tqCountryByCode and tqVatNumberForMyCompany.tiIdentityCountry_ID <> tqCountryByCode.tiCountry_ID
then do:
assign oiReturnStatus = -1
vcmsgReturn = substitute(trim(#T-23'&1 POSITION 72: The tax number of your entity is not a Belgian Tax number.':255(17914)T-23#),vcmsgLine).
<M-7 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-2981':U (icFcMsgNumber),
input '' (icFcExplanation),
input '' (icFcIdentification),
input '' (icFcContext),
output viFcReturnSuper (oiReturnStatus)) in BBankStateImport>
end. /* end if available tqCountryByCode */
end. /* End of check valid VAT number */
end. /* WHEN '0':U */
/* OPENING BALANCE */
WHEN '1':U
THEN DO:
/* check corresponding closage of the balance, cannot open without */
if vlbalanceOpen
then do:
assign oiReturnStatus = -1
vcmsgReturn = substitute(trim(#T-24'&1: The previous opening balance has no corresponding closing balance.':255(12282)T-24#),vcmsgLine).
<M-8 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-2982':U (icFcMsgNumber),
input '' (icFcExplanation),
input '' (icFcIdentification),
input '' (icFcContext),
output viFcReturnSuper (oiReturnStatus)) in BBankStateImport>
LEAVE codafile.
end.
else assign vlbalanceOpen = yes.
/* Validate bankaccountNr in GL numeric */
/* Check if the bank account does correspond with company's bankaccount */
<Q-9 run BanknumberByBankNumberCurrency (all) (Read) (NoCache)
(input viCompanyId, (CompanyId)
input substring(vcDocLine,6,12,'CHARACTER':U), (Banknumber)
input true, (BankNumberIsActive)
input {&BANKNUMBERPARENTTYPE-GL}, (BankNumberParentType)
input substring(vcDocLine,19,3,'CHARACTER':U), (CurrencyCode)
input ?, (CurrencyID)
output dataset tqBanknumberByBankNumberCurrency) in BBankNumber >
find first tqBanknumberByBankNumberCurrency no-lock no-error.
if not available tqBanknumberByBankNumberCurrency
then do:
assign oiReturnStatus = -1
vcmsgReturn = substitute(trim(#T-25'&1 position 6: The bank account &2 does not correspond to your own bank account.':255(12283)t-25#), vcmsgLine , string(substring(vcDocLine,6,12,"CHARACTER":U),'999-9999999-99':U)).
<M-10 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-2972':U (icFcMsgNumber),
input '' (icFcExplanation),
input '' (icFcIdentification),
input '' (icFcContext),
output viFcReturnSuper (oiReturnStatus)) in BBankStateImport>
end.
else do:
<Q-11 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-26'&1 position 6: No GL account corresponding to the bank account &2 exists.':255(12284)t-26#), vcmsgLine , string(substring(vcDocLine,6,12,"CHARACTER":U),'999-9999999-99':U)).
<M-12 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-2973':U (icFcMsgNumber),
input '' (icFcExplanation),
input '' (icFcIdentification),
input '' (icFcContext),
output viFcReturnSuper (oiReturnStatus)) in BBankStateImport>
end.
else assign vcGLCode = tqGLById.tcGLCode.
end.
/* Sign of the openingsbalance */
assign vcSign = (if substring(vcDocLine, 43,1,"CHARACTER":U) = '0':U then '+':U else '-':U).
/* CREATION TTBankStatement */
assign viTempBankStateID = viLineCount.
create tApiBankStateBSI.
assign tApiBankStateBSI.Company_ID = viCompanyId
tApiBankStateBSI.BankState_ID = viLineCount
tApiBankStateBSI.BankStateNumber = SUBSTRING(vcDocLine, 3,3,"CHARACTER":U)
tApiBankStateBSI.BankStateStatus = {&BANKSTATESTATUS-UNALLOC}
tApiBankStateBSI.BankStateOpeningBalance = decimal(vcSign + SUBSTRING(vcDocLine, 44,15,"CHARACTER":U)) / 1000
/* BTS 4985 */
tApiBankStateBSI.BankStateTransactionType = {&BANKSTATETRANSTYPE-BANK}
/* BTS 4985 */
tApiBankStateBSI.tc_Rowid = string(viLineCount)
vdtotal = vdtotal + 1.
end. /* When 1 */
/* Detail Lines */
WHEN '2':U
THEN DO:
/* Subprocedure to read the DetailLines */
<M-17 run AdditionalUpdatesCodeStart2 (output viFcReturnSuper (oiReturnStatus)) in BBankStateImport>
END. /* When 2 */
/* INFORMATION RECORDS */
WHEN '3':U
THEN DO:
assign vdtotal = vdtotal + 1.
CASE SUBSTRING(vcDocLine, 2,1,"CHARACTER":U):
WHEN '1':U
THEN DO:
If SUBSTRING(vcDocLine, 40, 1,"CHARACTER":U) = "1":U
then assign tApiBankStateLineBSI.BankStateLineExtTSM = trim(string(trim(SUBSTRING(vcDocLine, 11,105,"CHARACTER":U)),"x(40)":U)).
else assign tApiBankStateLineBSI.BankStateLineInformation = trim(string(trim(SUBSTRING(vcDocLine, 11,105,"CHARACTER":U)),"x(40)":U)).
END. /* When 1 */
WHEN '2':U THEN DO:
assign tApiBankStateLineBSI.BankStateLineInformation = trim(string(tApiBankStateLineBSI.BankStateLineInformation + ' ':U + trim(SUBSTRING(vcDocLine, 11,100,"CHARACTER":U)),"x(40)":U)).
END. /* When 2 */
WHEN '3':U THEN DO:
assign tApiBankStateLineBSI.BankStateLineInformation = trim(string(tApiBankStateLineBSI.BankStateLineInformation + ' ':U + trim(SUBSTRING(vcDocLine, 11,100,"CHARACTER":U)),"x(40)":U)).
END. /* When 3 */
END.
END. /* When 3 */
WHEN '4':U
THEN DO: /* Not handled */
assign vdtotal = vdtotal + 1.
END. /* When 4 */
/* CLOSING BALANCE */
WHEN '8':U
THEN DO:
if available tApiBankStateBSI
then do:
/* BankState Number pos. 4-4 numeric SUBSTRING(vcDocLine, 2,3,"CHARACTER":U) */
/* Sign of the openingsbalance 42 numeric SUBSTRING(vcDocLine, 42,1,"CHARACTER":U) */
assign vdtotal = vdtotal + 1
vcSign = (IF SUBSTRING(vcDocLine, 42,1,"CHARACTER":U) = '0':U then '+':U else '-':U)
/* check if total of movements + opening balance = closing balance */
tApiBankStateBSI.BankStateMovement = vdtotalMvmtsPerBalance.
if tApiBankStateBSI.BankStateOpeningBalance + vdtotalMvmtsPerBalance <> decimal(vcSign + SUBSTRING(vcDocLine, 43,15,"CHARACTER":U)) / 1000
then do:
assign oiReturnStatus = -1
vcmsgReturn = substitute(#T-30'&1 position 43: The closing balance &4 does not equal the opening balance &2 plus activity &3.':255(12342)t-30#,vcmsgLine, string(tApiBankStateBSI.BankStateOpeningBalance) , string(vdtotalMvmtsPerBalance), decimal(vcSign + SUBSTRING(vcDocLine, 43,15,"CHARACTER":U)) / 1000).
<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-2974':U (icFcMsgNumber),
input '' (icFcExplanation),
input '' (icFcIdentification),
input '' (icFcContext),
output viFcReturnSuper (oiReturnStatus)) in BBankStateImport>
end.
assign vdtotalMvmtsPerBalance = 0
vlbalanceOpen = NO
tApiBankStateBSI.BankStateClosingBalance = decimal(vcSign + SUBSTRING(vcDocLine, 43,15,"CHARACTER":U)) / 1000.
end. /* End if available tApiBankStateBSI */
END. /* When 8 */
/* TRAILER RECORD */
WHEN '9':U
THEN DO:
/* Check if he total number of articles in the file does correspond with total in */
/* the trailer record */
IF vdtotal <> int(SUBSTRING(vcDocLine,17,6,"CHARACTER":U))
THEN DO:
assign oiReturnStatus = -1
vcmsgReturn = substitute(trim(#T-27'POSITION 17: The total number of articles in the file &1 differs from the total number of articles in the trailer record &2.':255(12285)T-27#), string(vdtotal) , string(int(SUBSTRING(vcDocLine,17,6,"CHARACTER":U))),vcMsgLine).
<M-14 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-2975':U (icFcMsgNumber),
input '' (icFcExplanation),
input '' (icFcIdentification),
input '' (icFcContext),
output viFcReturnSuper (oiReturnStatus)) in BBankStateImport>
END.
/* Check if he total of debits in the file does correspond with the debits total */
/* in the trailer record */
IF vdtotalDebits <> decimal(SUBSTRING(vcDocLine,23,15,"CHARACTER":U)) / 1000
THEN DO:
assign oiReturnStatus = -1
vcmsgReturn = substitute(trim(#T-28'&3 POSITION 23: The total of debit amounts in the file &1 differs from the total of debit amounts in the trailer record &2.':255(12286)T-28#), string(vdtotalDebits) , string(decimal(SUBSTRING(vcDocLine,23,15,"CHARACTER":U)) / 1000),vcMsgLine).
<M-15 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-2976':U (icFcMsgNumber),
input '' (icFcExplanation),
input '' (icFcIdentification),
input '' (icFcContext),
output viFcReturnSuper (oiReturnStatus)) in BBankStateImport>
END.
/* Check if he total of credits in the file does correspond with the credits total */
/* in the trailer record */
IF vdtotalCredits <> decimal(SUBSTRING(vcDocLine,38,15,"CHARACTER":U)) / 1000
THEN DO:
assign oiReturnStatus = -1
vcmsgReturn = substitute(trim(#T-29'&3 POSITION 38: The total of credit amounts in the file &1 differs from the total of credit amounts in the trailer record &2.':255(12287)T-29#), string(vdtotalCredits) , string(decimal(SUBSTRING(vcDocLine,38,15,"CHARACTER":U)) / 1000),vcMsgLine).
<M-16 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-2977':U (icFcMsgNumber),
input '' (icFcExplanation),
input '' (icFcIdentification),
input '' (icFcContext),
output viFcReturnSuper (oiReturnStatus)) in BBankStateImport>
END.
assign vdtotal = 0
vdtotalcredits = 0
vdtotaldebits = 0.
END. /* When 9 */
END CASE.
END /* read of content */.