project QadFinancials > class BBankStateImport > method AdditionalUpdatesCodeStart2

Description

AdditionalUpdatesCodeStart2


Parameters


oiReturnStatusoutputintegerReturn status of the method.


Internal usage


QadFinancials
method BBankStateImport.AdditionalUpdatesCodeStart1


program code (program6/bbankstateimport.p)

/* =============================================================================== */
/* Treat the records starting with '2' - Detail Lines                              */
/* =============================================================================== */
assign vdtotal = vdtotal + 1.

/* Case Item code based */
CASE SUBSTRING(vcDocLine,2,1,"CHARACTER":U):
    WHEN '1':U 
    THEN DO:   
        ASSIGN vlCheck = FALSE.
/* BTS 4985 - JLA */
        if vlImportCODADetailLines = ? 
        then assign vlImportCODADetailLines = false.
        /* If you don't want to load detail lines within a bank statement line, */
        /* skip these detail lines (<> "0000":U)                                */
        if not vlImportCODADetailLines and
           SUBSTRING(vcDocLine,7,4,"CHARACTER":U) <> "0000":U
        then next.
        
        /* If you do want to load detail lines within a bank statement line,    */
        /* skip these main total line (= "0000":U) when detail lines are found  */
        if vlImportCODADetailLines and
           SUBSTRING(vcDocLine,7,4,"CHARACTER":U)  = "0000":U and
           SUBSTRING(vcDocLine,54,1,"CHARACTER":U) = "3":U
        then do:
            /* A detail line has an identical record-id and itemcode,   */
            /* a different detail number and a costcode starting with 8 */
            if can-find(first tBankStateImportFileContent where
                    substring(tBankStateImportFileContent.tcBankStateFileContentLine,1,6,"CHARACTER":U)  = substring(vcDocLine,1,6,"CHARACTER":U) and
                    substring(tBankStateImportFileContent.tcBankStateFileContentLine,7,4,"CHARACTER":U) <> substring(vcDocLine,7,4,"CHARACTER":U) and
                    substring(tBankStateImportFileContent.tcBankStateFileContentLine,54,1,"CHARACTER":U) =  "8":U)
            then next.
        end.   
        /* To be sure we read only the correct detail lines (as tested above) */
        /* we check the currect record again if it's a correct detail line    */
        if vlImportCODADetailLines and
           SUBSTRING(vcDocLine,7,4,"CHARACTER":U)  <> "0000":U and
           SUBSTRING(vcDocLine,54,1,"CHARACTER":U) <> "8":U
        then next.   
/* BTS 4985 - JLA */
        
        assign viCnt         = viCnt + 1
               vtPostingDate = date(SUBSTRING(vcDocLine, 118,2,"CHARACTER":U) + 
                                    SUBSTRING(vcDocLine, 116,2,"CHARACTER":U) + 
                                    SUBSTRING(vcDocLine, 120,2,"CHARACTER":U)). 

        if available tApiBankStateBSI
        then assign vcBankStateNumber = tApiBankStateBSI.BankStateNumber.    

        /* Get the Posting Year/Period/Date based on the input date */
        <Q-1 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.
        else do:
            assign oiReturnStatus  = -1
                   viPeriodID      = ?
                   vcmsgReturn     = substitute(trim(#T-7'&1 - No matching GL period found for posting date &2.':255(12344)T-7#), vcmsgLine, vtPostingDate).
            <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-2983':U (icFcMsgNumber),
                                 input  '' (icFcExplanation),
                                 input  '' (icFcIdentification),
                                 input  '' (icFcContext),
                                 output viFcReturnSuper (oiReturnStatus)) in BBankStateImport>
        end.                

        /* Get the Currency Field */
        <Q-3 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.

        /* Check Uniqueness */
        /* use the year of the posting date, because it must not be in a posting period, it's just the calendar year */
        assign viBankStateYear = year(vtPostingDate).
        <Q-4 assign vlFcQueryRecordsAvailable = BankStateByYear (NoCache)
           (input viCompanyId, (CompanyId)
            input vcBankStateNumber, (BankStateNumber)
            input tqGLByGLIdMatchedCode.tiGL_ID, (GLID)
            input viBankStateYear, (BankStateYear)) in BBankEntry >
        if vlFcQueryRecordsAvailable <> false
        then do:
            assign oiReturnStatus  = -1
                   vcmsgReturn     = substitute(trim(#T-8'LINE &1 POSITION 3: Bank statement number &2 already exists for this period.':255(13892)T-8#),string(viLineCount - 1), vcBankStateNumber + '/':U + string(year(vtPostingDate))).                   
            <M-6 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-2984':U (icFcMsgNumber),
                 input  '' (icFcExplanation),
                 input  '' (icFcIdentification),
                 input  '' (icFcContext),
                 output viFcReturnSuper (oiReturnStatus)) in BBankStateImport>
        end.                       

        /* 0 = credit , but debit (+)  for SC5 / 1 = debit  , but credit(-)  for SC5 */
        assign vcSign = (IF SUBSTRING(vcDocLine, 32,1,"CHARACTER":U) = '0':U then '+':U else '-':U)
               vcCostCode = SUBSTRING(vcDocLine, 54,8,"CHARACTER":U).
        
        /* find the right Period using the postingdate */               
        assign vcBankStateLineDescr = trim(replace(SUBSTRING(vcDocLine,63,53,"CHARACTER":U),'  ':U,' ':U)) + " ":U.

        IF substring(vcDocLine,128,1,"CHARACTER":U) = "0":U AND vcBankStateLineDescr = "":U 
        then assign vcBankStateLineDescr = trim(substring(vcDocLine,11,13,"CHARACTER":U) + '/':U + substring(vcDocLine,24,8,"CHARACTER":U)).

        /* If not description specified, we force one as it is a mandatory field */                                                    
        if vcBankStateLineDescr = '':U
        then assign vcBankStateLineDescr = "<X>":U.
        
        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.Period_ID                  = viPeriodID                       
               tApiBankStateLineBSI.BankStateLineDescription   = substring(vcBankStateLineDescr,1,(if length(vcBankStateLineDescr,"CHARACTER":U) > 40 then 40 else length(vcBankStateLineDescr,"CHARACTER":U)),"CHARACTER":U)
               tApiBankStateLineBSI.BankStateLineAmountTC      = decimal(vcSign + SUBSTRING(vcDocLine, 33,15,"CHARACTER":U)) / 1000                       
               tApiBankStateLineBSI.BankStateLineNumber        = int(SUBSTRING(vcDocLine, 3,4,"CHARACTER":U))   
               tApiBankStateLineBSI.BankStateLineExtCostCode   = vcCostCode
               tApiBankStateLineBSI.BankStateLineStatus        = {&BANKSTATELINESTATUS-UNALLOC}                         
               tApiBankStateLineBSI.BankStateLineInOut         = vcSign 
               tApiBankStateLineBSI.BankStateLineValueDate     = date(SUBSTRING(vcDocLine,  50,2,"CHARACTER":U) + SUBSTRING(vcDocLine,  48,2,"CHARACTER":U) + SUBSTRING(vcDocLine,  52,2,"CHARACTER":U))
               tApiBankStateLineBSI.BankStateLinePostingDate   = vtPostingDate
               tApiBankStateLineBSI.tiPeriodYear               = viPeriodYear
               tApiBankStateLineBSI.tiPeriodPeriod             = viPeriodPeriod                
               tApiBankStateBSI.tiPeriodYear                   = viPeriodYear
               tApiBankStateBSI.tiPeriodPeriod                 = viPeriodPeriod
               tApiBankStateBSI.Currency_ID                    = viGLCurrencyId   
               tApiBankStateBSI.tcCurrencyCode                 = vcGLCurrencyCode 
               tApiBankStateBSI.tcGLCode                       = vcGLCode
               tApiBankStateBSI.BankStateYear                  = viBankStateYear
               tApiBankStateBSI.BankStatePostingDate           = vtPostingDate.

        IF SUBSTRING(vcDocLine, 32,1,"CHARACTER":U) = '0':U
        THEN assign vdtotalcredits = vdtotalcredits + decimal(SUBSTRING(vcDocLine, 33,15,"CHARACTER":U)) / 1000.
        ELSE assign vdtotaldebits  = vdtotaldebits  + decimal(SUBSTRING(vcDocLine, 33,15,"CHARACTER":U)) / 1000.                   

        assign vdtotalMvmtsPerBalance = vdtotalMvmtsPerBalance + decimal(vcSign + SUBSTRING(vcDocLine, 33,15,"CHARACTER":U)) / 1000.

        /* Structured Message - */
        if substring(vcDocLine,62,4,"CHARACTER":U) = "1101":U
        then assign tApiBankStateLineBSI.BankStateLineExtTSM      = SUBSTRING(vcDocLine,66,12,"CHARACTER":U).
    END. /* When 1 */

    WHEN '2':U 
    THEN DO:
        IF SUBSTRING(vcDocLine, 7,4,"CHARACTER":U) = "0000":U 
        THEN assign vcBankStateLineDescr                           = vcBankStateLineDescr + trim(replace(SUBSTRING(vcDocLine,11,53,"CHARACTER":U),'  ':U,' ':U)) + " ":U  
                    tApiBankStateLineBSI.BankStateLineDescription  = substring(vcBankStateLineDescr,1,(if length(vcBankStateLineDescr,"CHARACTER":U) > 40 then 40 else length(vcBankStateLineDescr,"CHARACTER":U)),"CHARACTER":U)
                    tApiBankStateLineBSI.BankStateLineExtReference = SUBSTRING(vcDocLine, 64,26,"CHARACTER":U).
                    
        if decimal(vcSign + SUBSTRING(vcDocLine, 93, 15,"CHARACTER":U)) / 1000 <> 0
        then assign tApiBankStateLineBSI.BankStateLineExtOriginalTC  = decimal(vcSign + SUBSTRING(vcDocLine, 93, 15,"CHARACTER":U)) / 1000
                    tApiBankStateLineBSI.BankStateLineExtCurrency    = SUBSTRING(vcDocLine, 90, 3,"CHARACTER":U).
    END. /* When 2 */

    WHEN '3':U 
    THEN DO:
        IF SUBSTRING(vcDocLine, 7,4,"CHARACTER":U) = "0000":U 
        THEN do :
            assign viziplength = 0.

            /* if first 4 positions are numeric, we assume it it the ZIP */
            if SUBSTRING(vcDocLine,100,1,"CHARACTER":U) >= "0":U and SUBSTRING(vcDocLine,100,1,"CHARACTER":U) <= "9":U    and
               SUBSTRING(vcDocLine,101,1,"CHARACTER":U) >= "0":U and SUBSTRING(vcDocLine,101,1,"CHARACTER":U) <= "9":U    and 
               SUBSTRING(vcDocLine,102,1,"CHARACTER":U) >= "0":U and SUBSTRING(vcDocLine,102,1,"CHARACTER":U) <= "9":U    and
               SUBSTRING(vcDocLine,103,1,"CHARACTER":U) >= "0":U and SUBSTRING(vcDocLine,103,1,"CHARACTER":U) <= "9":U  
            then assign tApiBankStateLineBSI.BankStateLineExtZip = SUBSTRING(vcDocLine,100,4,"CHARACTER":U)
                        viziplength                              = if SUBSTRING(vcDocLine,103,1,"CHARACTER":U) = " ":U 
                                                                   then 5
                                                                   else 4.

            assign tApiBankStateLineBSI.BankStateLineExtBankNumber  = if decimal(SUBSTRING(vcDocLine, 11,12,"CHARACTER":U)) > 0 then SUBSTRING(vcDocLine, 11,12,"CHARACTER":U) else "":U
                   tApiBankStateLineBSI.BankStateLineExtName        = SUBSTRING(vcDocLine, 48,26,"CHARACTER":U)
                   tApiBankStateLineBSI.BankStateLineExtAddress     = SUBSTRING(vcDocLine, 74,26,"CHARACTER":U)
                   tApiBankStateLineBSI.BankStateLineExtCity        = SUBSTRING(vcDocLine,100 + viziplength ,26 - viziplength,"CHARACTER":U).
        end.

        if vcBankStateLineDescr = "":U or length(tApiBankStateLineBSI.BankStateLineDescription,"CHARACTER":U) < 40
        then assign tApiBankStateLineBSI.BankStateLineDescription = tApiBankStateLineBSI.BankStateLineDescription + SUBSTRING(vcDocLine, 48,26,"CHARACTER":U).

        if length(tApiBankStateLineBSI.BankStateLineDescription,"CHARACTER":U) > 40
        then tApiBankStateLineBSI.BankStateLineDescription = substring(tApiBankStateLineBSI.BankStateLineDescription,1,40,"CHARACTER":U).
    END. /* When 3 */         
END CASE.