project QadFinancials > class TConCheck > method BankEntry

Description

Bank entry validation check


Parameters


icFileinputcharacter
ocConCheckLineDetResultoutputcharacter
otConCheckLineDetStartDateoutputdate
oiConCheckLineDetStartTimeoutputinteger
oiConCheckLineDetDurationoutputinteger
iiCompanyIDinputinteger
iiPeriodIDinputinteger
iiSessionIDinputinteger
oiErrorNumbersoutputinteger
ocConCheckLineDetVersionoutputcharacter
icAppVersioninputcharacter
icSumFileinputcharacter
ihTranslationHandleinputhandleTranslation Handle.
oiReturnStatusoutputintegerReturn status of the method.


Internal usage


QadFinancials
method TConCheck.MainEntry


program code (program1/tconcheck.p)

/* ================================================================================= */
/* This is a support utility that checks if invoices linked tot the BankstateAlloc   */
/* are adjusted in the database                                                      */
/* We found customer installations where this was not the case when using CrossCy    */
/* Accounts                                                                          */
/* ================================================================================= */
assign oiReturnStatus          = -98
       viSessionID             = iiSessionID
       ocConCheckLineDetResult = {&CONCHECKRESULT-PASSED}.

define buffer bCompany for Company.
define buffer bPostingLine for PostingLine.

assign vdStartEtime               = etime(no)
       otConCheckLineDetStartDate = now
       oiConCheckLineDetStartTime = time
       oiErrorNumbers             = 0
       ocConCheckLineDetVersion   = icAppVersion + '.1'.

output stream sTechOut TO value(icFile) append.

put stream sTechOut unformatted ' 'skip.
put stream sTechOut unformatted '##### Banking Entry #####' skip.
PUT STREAM sTechOut unformatted 'Version: ' ocConCheckLineDetVersion skip.
put stream sTechOut unformatted 'Validation Start At: '  string(now, '99/99/9999 HH:MM:SS') skip.

MAIN_BLOCK:
do on error undo, throw:
    if iiCompanyID = ? or 
       iiCompanyID = 0 
    then do:
       assign ocConCheckLineDetResult = {&CONCHECKRESULT-FAILED}.
       PUT STREAM sTechOut unformatted "**Error** Input parameter CompanyID is null." skip. 
       LEAVE MAIN_BLOCK.
    end.
    
    if iiPeriodID = ? or 
       iiPeriodID = 0 
    then do:
       assign ocConCheckLineDetResult = {&CONCHECKRESULT-FAILED}.
       PUT STREAM sTechOut unformatted "**Error** Input parameter PeriodID is null." skip. 
       LEAVE MAIN_BLOCK.
    end.
    
    /* empty summary output first */
    empty temp-table tBankEntrySum.
    empty temp-table tBankEntrySumLine.        
    for each BankstateAlloc no-lock,
        first Bankstateline of BankstateAlloc no-lock,
        first Bankstate of Bankstateline
        where BankState.Period_ID = iiPeriodID and BankState.Company_ID = iiCompanyID no-lock,
        Company of Bankstate no-lock,        
        GL of Bankstate no-lock,
        first banknumber where ParentObject_ID = gl.gl_id no-lock:
        
        find first tBankEntrySumLine where 
            tBankEntrySumLine.tcBEGLCode = GL.GLCode and
            tBankEntrySumLine.tiBankStateLineId = BankStateLine.BankstateLine_id no-lock no-error.
        if not available tBankEntrySumLine
        then do:
            create tBankEntrySumLine.
            assign tBankEntrySumLine.tcBEGLCode = GL.GLCode
                   tBankEntrySumLine.tiBankStateLineId = BankStateLine.BankstateLine_id
                   tBankEntrySumLine.tlIsBankStateLineOk = yes.  
        end.
                   
        /* ================================================= */
        /* AP Invoice                                        */
        /* ================================================= */
        if BankstateAlloc.cinvoice_id <> 0
        then do :
            find first cinvoice of BankstateAlloc no-lock no-error.
            if cinvoice.cinvoicetype = 'prepayment' then next.
                                                  
            /* ============================================== */
            /* Check if we find a movement of the same amount */
            /* If not, we have a problem !                    */
            /* ============================================== */
            assign vlExactFound = no
                   vlFound      = no
                   vcKeyMov     = ''.
            for each cinvoicemovement of cinvoice no-lock where
                     cinvoicemovement.cinvoicemovementtype = 'movement',
                first PostingLine of CinvoiceMovement no-lock,
                first posting of postingline no-lock,
                first Journal of Posting no-lock,
                first bCompany of Posting no-lock,
                first Currency of PostingLine no-lock:
                
                find first tBankEntrySum where tBankEntrySum.tcBEGLCode = GL.GLCode and tBankEntrySum.tcBECurr = CurrencyCode no-error.
                if not available tBankEntrySum 
                then do:
                    create tBankEntrySum.
                    assign tBankEntrySum.tcBECurr = CurrencyCode
                           tBankEntrySum.tcBEDirection = 'AP'
                           tBankEntrySum.tcBEGLCode = GL.GLCode
                           tBankEntrySum.tcBEGLDesc = GL.GLDescription
                           tBankEntrySum.tcBEGLNo = BankNumber.BankNumber
                           tBankEntrySum.tdBETotalAmountAlloc = 0
                           tBankEntrySum.tdBETotalAmountPosting = 0
                           tBankEntrySum.tdTotalAmountDiff = 0
                           tBankEntrySum.tiBETotalLines = 0
                           tBankEntrySum.tiBETotalLinesErr = 0.                                        
                end. 
                
                assign vlPostingFound = no.
                if(BankStateAlloc.Posting_ID = Posting.Posting_ID or BankStateAlloc.Posting_ID = PostingLine.CrossCompanyPosting_ID) 
                then do:
                    assign vlPostingFound = yes.
                end.    
                else do:
                    for each bPostingLine of Posting no-lock where bPostingLine.crosscompanyposting_id = BankStateAlloc.Posting_ID:
                        assign vlPostingFound = yes.
                    end.
                end.
                
                if vlPostingFound = yes
                then assign tBankEntrySum.tdBETotalAmountAlloc   = tBankEntrySum.tdBETotalAmountAlloc + bankstateallocamounttc
                           tBankEntrySum.tdBETotalAmountPosting = tBankEntrySum.tdBETotalAmountPosting + postingline.postinglinedebittc + postingline.postinglinecredittc
                           tBankEntrySum.tiBETotalLines         = tBankEntrySum.tiBETotalLines + 1.                      
                       
              
                if BankState.BankStatePostingDate = Posting.PostingDate and
                   (postingline.postinglinedebittc = bankstateallocamounttc or
                    postingline.postinglinecredittc = bankstateallocamounttc)
                then assign vlExactFound  = yes
                            vcKeyMov      = bCompany.CompanyCode + "/" +
                                            string (Posting.PostingYear) + "/" +
        				                    Journal.JournalCode + "/" +
    					                    string (Posting.PostingVoucher).
                else
                if (postingline.postinglinedebittc = bankstateallocamounttc or
                    postingline.postinglinecredittc = bankstateallocamounttc)
                then assign vlFound = yes
                            vcKeyMov      = bCompany.CompanyCode + "/" +
                                            string (Posting.PostingYear) + "/" +
        				                    Journal.JournalCode + "/" +
    					                    string (Posting.PostingVoucher).
            end. /* for each cinvoicemovement of cinvoice no-lock where */
    
            /* ============================================== */
            /* Report it                                      */
            /* ============================================== */
            if not vlExactFound and not vlFound
            then do:
                find first cinvoicemovement of CInvoice where 
                           cinvoicemovement.cinvoicemovementtype = 'INITIAL' no-lock no-error.
                find first PostingLine of  cinvoicemovement no-lock no-error.
                find first Posting of PostingLine no-lock no-error.
                find first Journal of Posting no-lock no-error.
                find first bCompany of Posting no-lock no-error.              
                
                
                assign vdTotCI = vdTotCI + bankstateallocamounttc.
                assign oiErrorNumbers = oiErrorNumbers + 1.
                assign tBankEntrySum.tiBETotalLinesErr = tBankEntrySum.tiBETotalLinesErr + 1.
                assign tBankEntrySumLine.tlIsBankStateLineOk = no.
                put stream sTechOut unformatted
                    now 
                    '> AP Invoice;'            
                    company.companycode '/' 
                    bankstate.bankstateyear '/' 
                    bankstate.bankstateNumber '/' 
                    bankstateline.bankstatelinenumber ';' 
                    BankState.BankStatePostingDate ';'
                    bankstatealloc.bankstateallocamounttc ';'
                    bcompany.companycode  '/'
                    posting.postingyearperiod '/'
                    journal.journalcode '/'
                    cinvoice.CInvoiceVoucher ';'
                    cinvoice.cinvoicereference ';'
                    vlExactFound ';'
                    vlFound ';'
                    (if Company.CompanyCode = bCompany.CompanyCode then ';' else 'CrossCy Trx;')
    		        vcKeyMov ';'
                    skip.
                
                assign ocConCheckLineDetResult = {&CONCHECKRESULT-FAILED}.
            end. /* if not vlExactFound and not vlFound */ 
        end. /* if BankstateAlloc.cinvoice_id <> 0 */
                         
        /* ================================================= */
        /* AR Invoice                                        */
        /* ================================================= */
        if bankstatealloc.dinvoice_id <> 0
        then do :
            find first Dinvoice of Bankstatealloc no-lock no-error.
            if Dinvoice.Dinvoicetype = 'prepayment' then next.
            if Dinvoice.Dinvoicetype = 'DEDUCTION' then next.
            
            /* ============================================== */
            /* Check if we find a movement of the same amount */
            /* If not, we have a problem !                    */
            /* ============================================== */
            assign vlExactFound = no
                   vlFound      = no
                   vcKeyMov     = ''.
            for each Dinvoicemovement of Dinvoice no-lock where
                     Dinvoicemovement.Dinvoicemovementtype = 'movement',
                first PostingLine of DinvoiceMovement no-lock,
                first posting of postingline no-lock,
                first journal of posting no-lock,
                first bcompany of posting no-lock,
                first Currency of PostingLine no-lock:
                
                find first tBankEntrySum where tBankEntrySum.tcBEGLCode = GL.GLCode and tBankEntrySum.tcBECurr = CurrencyCode no-lock no-error.
                if not available tBankEntrySum 
                then do:
                    create tBankEntrySum.
                    assign tBankEntrySum.tcBECurr = CurrencyCode
                           tBankEntrySum.tcBEDirection = 'AP'
                           tBankEntrySum.tcBEGLCode = GL.GLCode
                           tBankEntrySum.tcBEGLDesc = GL.GLDescription
                           tBankEntrySum.tcBEGLNo = BankNumber.BankNumber
                           tBankEntrySum.tdBETotalAmountAlloc = 0
                           tBankEntrySum.tdBETotalAmountPosting = 0
                           tBankEntrySum.tdTotalAmountDiff = 0
                           tBankEntrySum.tiBETotalLines = 0
                           tBankEntrySum.tiBETotalLinesErr = 0.                                        
                end.   
                
                assign vlPostingFound = no.
                if(BankStateAlloc.Posting_ID = Posting.Posting_ID or BankStateAlloc.Posting_ID = PostingLine.CrossCompanyPosting_ID) 
                then do:
                    assign vlPostingFound = yes.
                end.    
                else do:
                    for each bPostingLine of Posting no-lock where bPostingLine.crosscompanyposting_id = BankStateAlloc.Posting_ID:
                        assign vlPostingFound = yes.
                    end.
                end.
                
                if vlPostingFound = yes
                then assign tBankEntrySum.tdBETotalAmountAlloc   = tBankEntrySum.tdBETotalAmountAlloc + bankstateallocamounttc
                           tBankEntrySum.tdBETotalAmountPosting = tBankEntrySum.tdBETotalAmountPosting + postingline.postinglinedebittc + postingline.postinglinecredittc
                           tBankEntrySum.tiBETotalLines         = tBankEntrySum.tiBETotalLines + 1. 
                                       
              
                if BankState.BankStatePostingDate = Posting.PostingDate and
                   (postingline.postinglinedebittc = bankstateallocamounttc or
                    postingline.postinglinecredittc = bankstateallocamounttc)
                then assign vlExactFound = yes
                            vcKeyMov      = bCompany.CompanyCode + "/" +
                                            string (Posting.PostingYear) + "/" +
        				                    Journal.JournalCode + "/" +
    					                    string (Posting.PostingVoucher).   
                else
                if (postingline.postinglinedebittc = bankstateallocamounttc or
                    postingline.postinglinecredittc = bankstateallocamounttc)
                then assign vlFound = yes
                            vcKeyMov =  bCompany.CompanyCode + "/" +
                                        string (Posting.PostingYear) + "/" +
        				                Journal.JournalCode + "/" +
    					                string (Posting.PostingVoucher).                           
            end. /* for each Dinvoicemovement of Dinvoice no-lock where */
    
            /* ============================================== */
            /* Report it                                      */
            /* ============================================== */
            if not vlExactFound and not vlFound
            then do:
                assign vdTotDI = vdTotDI + bankstateallocamounttc.
                find first dinvoicemovement of DInvoice where 
                           dinvoicemovement.dinvoicemovementtype = 'INITIAL' no-lock no-error.
                find first PostingLine of  dinvoicemovement no-lock no-error.
                find first Posting of PostingLine no-lock no-error.
                find first Journal of Posting no-lock no-error.
                find first bCompany of Posting no-lock no-error.

                assign 
                    oiErrorNumbers                        = oiErrorNumbers + 1
                    tBankEntrySum.tiBETotalLinesErr       = tBankEntrySum.tiBETotalLinesErr + 1
                    tBankEntrySumLine.tlIsBankStateLineOk = no.
                put stream sTechOut unformatted
                    now
                    '> AR Invoice;'
                    company.companycode '/' 
                    bankstate.bankstateyear '/' 
                    bankstate.bankstateNumber '/' 
                    bankstateline.bankstatelinenumber ';' 
                    BankState.BankStatePostingDate ';'                
                    bankstatealloc.bankstateallocamounttc ';'
                    bcompany.companycode  '/'
                    posting.postingyearperiod '/'
                    journal.journalcode '/'
                    DInvoice.DinvoiceVoucher ';'
                    dinvoice.dinvoiceDItext ';'
                    vlExactFound ';'
                    vlFound ';'
                    (if Company.CompanyCode = bCompany.CompanyCode then ';' else 'CrossCy Trx;')                
    		        vcKeyMov ';'
                    skip.
                    
                assign ocConCheckLineDetResult = {&CONCHECKRESULT-FAILED}.
            end. /* if not vlExactFound and not vlFound */    
        end. /* if bankstatealloc.dinvoice_id <> 0 */
    end. /* for each BankstateAlloc no-lock, */
end. /* Main_BLOCK */

if oiErrorNumbers > 0
then assign ocConCheckLineDetResult = {&CONCHECKRESULT-FAILED}.
else if viCount > 0
then assign ocConCheckLineDetResult = {&CONCHECKRESULT-WARNING}.
else assign ocConCheckLineDetResult = {&CONCHECKRESULT-PASSED}.

assign oiConCheckLineDetDuration = etime - vdStartEtime.

/* to format the total duration time */
assign 
    viMilliSecs = oiConCheckLineDetDuration
    viSecs      = TRUNCATE (oiConCheckLineDetDuration / 1000, 0)
    viMilliSecs = viMilliSecs mod 1000
    vcTotalTime = string(viSecs, 'HH:MM:SS')  +  '.'  +  string(viMilliSecs).
put stream sTechOut unformatted 'Execution Duration: ' vcTotalTime skip. 
put stream sTechOut unformatted 'Execution Result: ' ocConCheckLineDetResult skip.
put stream sTechOut  unformatted '<Error Count:> ' oiErrorNumbers skip.
put stream sTechOut unformatted '**********************************************************************' skip.

output stream sTechOut close.


output stream sTechOut TO value(icSumFile) append. 

put stream sTechOut unformatted ' 'skip.
put stream sTechOut unformatted trim(#T-98'Banking Entry':40(4731)T-98#) skip.
put stream sTechOut unformatted '-------------' skip.
put stream sTechOut unformatted trim(#T-5'Execution Result':255(510391963)T-5#) + ': ' + ocConCheckLineDetResult skip.

if oiErrorNumbers > 0 
then do:
    put stream sTechOut unformatted 
                        trim(#T-41'Total number of errors':100(376712702)T-41#)
                        + ': ' + string(oiErrorNumbers) skip.
    put stream sTechOut unformatted '(' trim(#T-82'Please see details in the file':150(806040074)T-82#) ' ' icFile ')' skip.    
end.

for each tBankEntrySum 
    break by tBankEntrySum.tcBEGLCode by tBankEntrySum.tcBECurr:
    
    assign tBankEntrySum.tdTotalAmountDiff = tBankEntrySum.tdBETotalAmountPosting - tBankEntrySum.tdBETotalAmountAlloc.    
    if first-of(tBankEntrySum.tcBEGLCode)
    then do:
    
        assign viGLAllocatedTotal = 0
               viGLAllocatedTotalFail = 0.
        for each tBankEntrySumLine where tBankEntrySumLine.tcBEGLCode = tBankEntrySum.tcBEGLCode:
            assign viGLAllocatedTotal = viGLAllocatedTotal + 1.
            if tBankEntrySumLine.tlIsBankStateLineOk = no
            then assign viGLAllocatedTotalFail = viGLAllocatedTotalFail + 1.  
        end.

        put stream sTechOut unformatted ' 'skip.
        /* Bank Account: 1100 #GL description #Bank Account No */
        put stream sTechOut unformatted 
                                       trim(#T-76'Bank Account':30(888)T-76#) + 
                                       ': ' + string(tBankEntrySum.tcBEGLCode) at 1
                                       trim(#T-54'GL Description':40(1012)T-54#) + 
                                        ': ' + tBankEntrySum.tcBEGLDesc at 30
                                       trim(#T-26'Bank Account No':20(3942)T-26#) + 
                                        ': ' + tBankEntrySum.tcBEGLNo at 80 skip.                                
        /* Total Number of banking entry lines: # */
        put stream sTechOut unformatted trim(#T-55'Total number of banking entry lines':150(334579066)T-55#) + 
                                        ': ' + string(viGLAllocatedTotal) skip.
        /* Number of banking entry lines with inconsistency: # */
        put stream sTechOut unformatted trim(#T-45'Total number of banking entry lines with inconsistencies':150(515380374)T-45#) + 
                                        ': ' + string(viGLAllocatedTotalFail) skip.
        /* Currency | Total Amount Posted | Total Amount Allocated | Total Difference */
        put stream sTechOut unformatted 
                                        trim(#T-71'Currency':20(220)T-71#) at 1 
                                        trim(#T-93'Total Amount Posted':255(712663609)T-93#)  to 40 
                                        trim(#T-70'Total Amount Allocated':100(317822737)T-70#) to 75 
                                        trim(#T-7'Total Difference':100(98885305)T-7#) to 100                                  
                                        skip.                                                  
    end.  
    <M-31 run RoundAmount
       (input-output tBankEntrySum.tdBETotalAmountPosting (bdUnroundedAmount), 
        input  ? (iiCurrencyId), 
        input  tBankEntrySum.tcBECurr (icCurrencyCode), 
        output vcRoundAmount1 (ocFormatedAmount), 
        output viFcReturnSuper (oiReturnStatus)) in TConCheck>
    <M-88 run RoundAmount
       (input-output tBankEntrySum.tdBETotalAmountAlloc (bdUnroundedAmount), 
        input  ? (iiCurrencyId), 
        input  tBankEntrySum.tcBECurr (icCurrencyCode), 
        output vcRoundAmount2 (ocFormatedAmount), 
        output viFcReturnSuper (oiReturnStatus)) in TConCheck>
    <M-44 run RoundAmount
       (input-output tBankEntrySum.tdTotalAmountDiff (bdUnroundedAmount), 
        input  ? (iiCurrencyId), 
        input  tBankEntrySum.tcBECurr (icCurrencyCode), 
        output vcRoundAmount3 (ocFormatedAmount), 
        output viFcReturnSuper (oiReturnStatus)) in TConCheck>        
    put stream sTechOut unformatted tBankEntrySum.tcBECurr to 8
                                    vcRoundAmount1 to 40
                                    vcRoundAmount2 to 75
                                    vcRoundAmount3 to 100 skip.
    
end. /* for each tBankEntrySum */

put stream sTechOut unformatted "____________________________________________________________" skip.
output stream sTechOut close.

if oiReturnStatus = -98
then assign oiReturnStatus = 0.