project QadFinancials > class BBankStateImport > method AdditionalUpdatesCodeStart1

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


oiLineCounteroutputintegerLineCounter
ocDocumentLineoutputcharacterDocumentLine
ocLastRecordTypeoutputcharacterLastRecordType
oiReturnStatusoutputintegerReturn status of the method.


Internal usage


QadFinancials
method BBankStateImport.AdditionalUpdatesCodaStart


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 */.