project QadFinancials > class TConCheck > method TechnicalValidation04CHistInvoice

Description

CHist reconciliation with CInvoice


Parameters


iiCompanyIDinputinteger
iiPeriodIDinputinteger
iiSelectedYearPeriodinputinteger
itFromDateinputdate
itToDateinputdate
oiErrorNumbersoutputinteger
oiReturnStatusoutputintegerReturn status of the method.


Internal usage


QadFinancials
method TConCheck.TechnicalValidation


program code (program1/tconcheck.p)

/* ================================================================================= */
/* This is the method for CHist reconciliation with CInvoice                         */
/* ================================================================================= */

assign oiReturnStatus = -98.

/* open logfile stream */
PUT STREAM sTechOut unformatted " " skip. 
PUT STREAM sTechOut unformatted vcPrefix2 + "START Validation of CHist reconciliation with CInvoice (" + STRING (itFromDate) + ")" skip.

IPR_AP_Check_HISTWITHINVOICE: 
do:
    /* init variables */
    define buffer bCHist for CHist.
    empty temp-table tRebuild.
    /* ====================================================================================== */ 
    /* Main Loop: go through currenty company code                                            */
    /* ====================================================================================== */ 
    for each company no-lock where
             Company.Company_ID = iiCompanyID:
        /* Get sharedset of the company */
        for each companysharedset no-lock where companysharedset.company_id = company.company_id,
            each sharedset of companysharedset no-lock where Sharedset.SharedSetType = 'Creditor':
                assign viCreditorSharedSetID = sharedset.sharedset_id.
        end. /* end each companysharedset */

        for each Creditor where Creditor.sharedset_id = viCreditorSharedSetID 
             no-lock
             break by Creditor.CreditorCode:
        
            if not can-find (first CHist where
                                   CHist.Company_ID           = Company.Company_ID   and
                                   CHist.Creditor_ID          = Creditor.Creditor_ID and
                                   CHist.CHistYearPeriodFrom >= iiSelectedYearPeriod)     and
               not can-find (first CInvoiceMovement where
                                   CInvoiceMovement.Company_ID                  = Company.Company_ID and
                                   CInvoiceMovement.Creditor_ID                 = Creditor.Creditor_ID and
                                   CInvoiceMovement.CInvoiceMovementYearPeriod >= iiSelectedYearPeriod)
            then next.

            /* *************************************************************************** */
            /* PART ONE : check on the movements of the current period                     */
            /* *************************************************************************** */ 
            for each Period of Company where
                     Period.Periodyearperiod >= iiSelectedYearPeriod and
                     Period.Periodyearperiod <= iiSelectedYearPeriod
                     no-lock 
                     by Period.Company_ID
                     by Period.PeriodYearPeriod :
    
                empty temp-table tCI.
                
                /*************************************************************************************/                                                         
                /* A.1. Go through all the PostingLines/PostingSaf of this Company/period            */
                /*************************************************************************************/ 
                for each CInvoiceMovement no-lock where
                         CInvoiceMovement.Company_ID                 = Company.Company_ID   and
                         CInvoiceMovement.Creditor_ID                = Creditor.Creditor_ID and
                         CInvoiceMovement.CInvoiceMovementYearPeriod = Period.Periodyearperiod,
                    first PostingLine of CInvoiceMovement no-lock,                 
                    first Posting of PostingLine no-lock,
                    first Journal of Posting
                    no-lock :
                    
                    find tCI where
                         tCI.tcCompanyCode   = Company.CompanyCode and
                         tCI.tiYearPeriod    = (if PostingLine.PostingYearPeriod = ? then 0 else PostingLine.PostingYearPeriod)  and
                         tCI.tiJournal_ID    = (if Posting.Journal_ID = ? then 0 else Posting.Journal_ID) and 
                         tCI.tiGL_ID         = (if PostingLine.GL_ID = ? then 0 else PostingLine.GL_ID) and                  
                         tCI.tiCurrency_ID   = (if PostingLine.currency_Id = ? then 0 else PostingLine.currency_Id) and
                         tCI.tcCreditorCode  = Creditor.CreditorCode and
                         tCI.tiProject_ID    = (if PostingLine.Project_ID = ? then 0 else PostingLine.Project_ID) and
                         tCI.tiCostCentre_ID = (if PostingLine.Costcentre_ID = ? then 0 else PostingLine.Costcentre_ID) and
                         tCI.tiDivision_ID   = (if PostingLine.Division_ID = ? then 0 else PostingLine.Division_ID)
                         no-error. 
                    
                    if not available tCI
                    then do:
                        create tCI.
                        assign  
                          tCI.tcTYPE          = 'TRX ':U
                          tCI.tcCompanyCode   = Company.CompanyCode
                          tCI.tiYearPeriod    = (if PostingLine.PostingYearPeriod = ? then 0 else PostingLine.PostingYearPeriod)
                          tCI.tiJournal_ID    = (if Posting.Journal_ID = ? then 0 else Posting.Journal_ID)
                          tCI.tiGL_ID         = (if PostingLine.GL_ID = ? then 0 else PostingLine.GL_ID)                
                          tCI.tiCurrency_ID   = (if PostingLine.currency_Id = ? then 0 else PostingLine.currency_Id)
                          tCI.tcCreditorCode  = Creditor.CreditorCode
                          tCI.tiProject_ID    = (if PostingLine.Project_ID = ? then 0 else PostingLine.Project_ID)
                          tCI.tiCostCentre_ID = (if PostingLine.Costcentre_ID = ? then 0 else PostingLine.Costcentre_ID)
                          tCI.tiDivision_ID   = (if PostingLine.Division_ID = ? then 0 else PostingLine.Division_ID).
                    end.
    
                    assign  tCI.tdTotalPostingLineCreditTC = tCI.tdTotalPostingLineCreditTC + Postingline.PostingLineCreditTC
                            tCI.tdTotalPostingLineCreditLC = tCI.tdTotalPostingLineCreditLC + Postingline.PostingLineCreditLC            
                            tCI.tdTotalPostingLineCreditCC = tCI.tdTotalPostingLineCreditCC + Postingline.PostingLineCreditCC
                            tCI.tdTotalPostingLineDebitTC  = tCI.tdTotalPostingLineDebitTC  + Postingline.PostingLineDebitTC
                            tCI.tdTotalPostingLineDebitLC  = tCI.tdTotalPostingLineDebitLC  + Postingline.PostingLineDebitLC
                            tCI.tdTotalPostingLineDebitCC  = tCI.tdTotalPostingLineDebitCC  + Postingline.PostingLineDebitCC.
    
                end.  /* A.1 for each PostingLine */
    
                /*************************************************************************************/                                                         
                /* A.2. Go through all the History                                                   */
                /*************************************************************************************/
                for each CHist where
                         CHist.Company_ID          = Company.Company_ID   and
                         CHist.Creditor_ID         = Creditor.Creditor_ID and
                         CHist.CHistYearPeriodFrom = Period.Periodyearperiod
                         no-lock:
    
                    find tCI where
                         tCI.tcCompanyCode   = Company.CompanyCode and
                         tCI.tiYearPeriod    = (if CHist.CHistYearPeriodFrom = ? then 0 else CHist.CHistYearPeriodFrom)  and
                         tCI.tiJournal_ID    = (if CHist.Journal_ID = ? then 0 else CHist.Journal_ID) and 
                         tCI.tiCurrency_ID   = (if CHist.currency_Id = ? then 0 else CHist.currency_Id) and
                         tCI.tiGL_ID         = (if CHist.GL_ID = ? then 0 else CHist.GL_ID) and                   
                         tCI.tcCreditorCode  = Creditor.CreditorCode and
                         tCI.tiProject_ID    = (if CHist.Project_ID = ? then 0 else CHist.Project_ID) and
                         tCI.tiCostCentre_ID = (if CHist.Costcentre_ID = ? then 0 else CHist.Costcentre_ID) and
                         tCI.tiDivision_ID   = (if CHist.Division_ID = ? then 0 else CHist.Division_ID)
                         no-error.
    
                    if not available tCI
                    then do :
                        create tCI.
                        assign    
                          tCI.tcTYPE          = 'HIST':U
                          tCI.tcCompanyCode   = Company.CompanyCode
                          tCI.tiYearPeriod    = (if CHist.CHistYearPeriodFrom = ? then 0 else CHist.CHistYearPeriodFrom)
                          tCI.tiJournal_ID    = (if CHist.Journal_ID = ? then 0 else CHist.Journal_ID)
                          tCI.tiGL_ID         = (if CHist.GL_ID = ? then 0 else CHist.GL_ID)                  
                          tCI.tiCurrency_ID   = (if CHist.currency_Id = ? then 0 else CHist.currency_Id)
                          tCI.tcCreditorCode  = Creditor.CreditorCode
                          tCI.tiProject_ID    = (if CHist.Project_ID = ? then 0 else CHist.Project_ID)
                          tCI.tiCostCentre_ID = (if CHist.Costcentre_ID = ? then 0 else CHist.Costcentre_ID)
                          tCI.tiDivision_ID   = (if CHist.Division_ID = ? then 0 else CHist.Division_ID).
                    end. /* if not available ttCI */
                    else assign tCI.tcType = 'BOTH':U.
    
                    assign  tCI.tdCHistMovementCreditTC = tCI.tdCHistMovementCreditTC + CHist.CHistMovementCreditTC
                            tCI.tdCHistMovementCreditLC = tCI.tdCHistMovementCreditLC + CHist.CHistMovementCreditLC            
                            tCI.tdCHistMovementCreditCC = tCI.tdCHistMovementCreditCC + CHist.CHistMovementCreditCC
                            tCI.tdCHistMovementDebitTC  = tCI.tdCHistMovementDebitTC  + CHist.CHistMovementDebitTC
                            tCI.tdCHistMovementDebitLC  = tCI.tdCHistMovementDebitLC  + CHist.CHistMovementDebitLC
                            tCI.tdCHistMovementDebitCC  = tCI.tdCHistMovementDebitCC  + CHist.CHistMovementDebitCC.
    
                end. /* A.2 for each CHist */
    
                /*************************************************************************************/                                                         
                /* A.3 See if there any differences and report it                                    */
                /*************************************************************************************/
                for each tCI where
                         tCI.tcCompanyCode  = Company.CompanyCode and
                         tCI.tcCreditorCode = Creditor.CreditorCode
                    by tCI.tcCompanyCode 
                    by tCI.tcCreditorCode 
                    by tCI.tiYearPeriod :
                    
                    if tCI.tdTotalPostingLineCreditTC <> tCI.tdCHistMovementCreditTC or
                       tCI.tdTotalPostingLineCreditLC <> tCI.tdCHistMovementCreditLC or
                       tCI.tdTotalPostingLineCreditCC <> tCI.tdCHistMovementCreditCC or
                       tCI.tdTotalPostingLineDebitTC  <> tCI.tdCHistMovementDebitTC  or
                       tCI.tdTotalPostingLineDebitLC  <> tCI.tdCHistMovementDebitLC  or 
                       tCI.tdTotalPostingLineDebitCC  <> tCI.tdCHistMovementDebitCC
                    then do : 
                        /* Create indication rebuild is needed */ 
                        find tRebuild where
                             tRebuild.tcCompanyCode  = tCI.tcCompanyCode and
                             tRebuild.tcCreditorCode = tCI.tcCreditorCode
                             no-error.
                        if not available tRebuild
                        then do:
                            create tREbuild.
                            assign tRebuild.tcCompanyCode    = tCI.tcCompanyCode
                                   tRebuild.tcCreditorCode   = tCI.tcCreditorCode
                                   tRebuild.tiYearPeriodFrom = tCI.tiYearPeriod.
                        end.

                        assign oiErrorNumbers = oiErrorNumbers + 1.
                        /* log error */
                        PUT STREAM sTechOut unformatted vcPrefix
                            'ERROR MOVEMENT '
                            tCI.tcTYPE ' * '
                            tCI.tcCompanyCode  ' * '
                            tCI.tcCreditorCode      format 'x(10)' ' * '                         
                            tCI.tiYearPeriod  ' * ' 
                            'JN:' tCI.tiJournal_ID  format '9999999999' ' ' 
                            'CU:' tCI.tiCurrency_ID  format '9999999999' ' ' 
                            'GL:' tCI.tiGL_ID  format '9999999999' ' '                         
                            'PR:' tCI.tiProject_ID  format '9999999999' ' ' 
                            'CC:' tCI.tiCostCentre_ID format '9999999999' ' ' 
                            'DI:' tCI.tiDivision_ID  format '9999999999' ' ' 
                            tCI.tdTotalPostingLineDebitTC  <> tCI.tdCHistMovementDebitTC  format 'yes/no' ' ' 
                            tCI.tdTotalPostingLineDebitLC  <> tCI.tdCHistMovementDebitLC  format 'yes/no' ' ' 
                            tCI.tdTotalPostingLineDebitCC  <> tCI.tdCHistMovementDebitCC  format 'yes/no' ' * '    
                            tCI.tdTotalPostingLineCreditTC <> tCI.tdCHistMovementCreditTC format 'yes/no' ' ' 
                            tCI.tdTotalPostingLineCreditLC <> tCI.tdCHistMovementCreditLC format 'yes/no' ' ' 
                            tCI.tdTotalPostingLineCreditCC <> tCI.tdCHistMovementCreditCC format 'yes/no' ' '  
                            
                            tCI.tdTotalPostingLineDebitTC  ' ' tCI.tdCHistMovementDebitTC  ' * ' 
                            tCI.tdTotalPostingLineDebitLC  ' ' tCI.tdCHistMovementDebitLC  ' * ' 
                            tCI.tdTotalPostingLineDebitCC  ' ' tCI.tdCHistMovementDebitCC  '  * '
                            tCI.tdTotalPostingLineCreditTC ' ' tCI.tdCHistMovementCreditTC ' * ' 
                            tCI.tdTotalPostingLineCreditLC ' ' tCI.tdCHistMovementCreditLC ' * '      
                            tCI.tdTotalPostingLineCreditCC ' ' tCI.tdCHistMovementCreditCC ' * '        
                            skip.
    
                    end.  /* corruption */   
    
                end. /* A.3 for each ttCI */ 
                
            end. /* for each Period */ 

            /* *************************************************************************** */
            /* PART TWO : check on the balance fields of the current period                */
            /* *************************************************************************** */ 
            if not can-find (first tRebuild where
                                   tRebuild.tcCompanyCode = Company.CompanyCode and
                                   tRebuild.tcCreditorCode = Creditor.CreditorCode)
            then do:
                    
                for each CHist of Company no-lock where
                         CHist.Creditor_ID  = Creditor.Creditor_ID and
                         CHist.CHistYearPeriodFrom >= iiSelectedYearPeriod 
                         break by CHist.Company_ID
                               by CHist.Journal_ID                        
                               by CHist.Creditor_ID                        
                               by CHist.GL_ID                       
                               by CHist.Division_ID               
                               by CHist.CostCentre_ID
                               by CHist.Project_ID                                                      
                               by CHist.Currency_ID
                               by CHist.CHistYearPeriodFrom:
    
                    /* ======================================================== */
                    /* Initialize when a new combiation is started              */
                    /* ======================================================== */
                    if first-of (CHist.Currency_ID)
                    then do:
                        assign vlSkipCombination = no
                               vcNewCombination = ">":U.
    
                        if iiSelectedYearPeriod <> 0
                        then find last  bCHist where
                                        bCHist.Company_ID          = CHist.Company_ID    and
                                        bCHist.Creditor_ID         = CHist.Creditor_ID   and
                                        bCHist.Journal_ID          = CHist.Journal_ID    and
                                        bCHist.GL_ID               = CHist.GL_ID         and                                
                                        bCHist.Currency_ID         = CHist.Currency_ID   and
                                        bCHist.Division_ID         = CHist.Division_ID   and
                                        bCHist.Project_ID          = CHist.Project_ID    and
                                        bCHist.CostCentre_ID       = CHist.CostCentre_ID and
                                        bCHist.CHistYearPeriodFrom < CHist.CHistYearPeriodFrom
                                        no-lock no-error.
    
                         if iiSelectedYearPeriod <> 0 and available bCHist
                         then assign vdCHistBalanceCC = bCHist.CHistBalanceCC
                                     vdCHistBalanceLC = bCHist.CHistBalanceLC
                                     vdCHistBalanceTC = bCHist.CHistBalanceTC
                   
                                     vdCHistYearPeriodFromPREV = bCHist.CHistYearPeriodFrom
                                     vdCHistYearPeriodTillPREV = bCHist.CHistYearPeriodTill
                                     vdCHistYearFromPREV       = bCHist.CHistYearPeriodFrom / 100.
                                      
                         else assign vdCHistBalanceCC = 0
                                     vdCHistBalanceLC = 0
                                     vdCHistBalanceTC = 0
                                     
                                     vdCHistYearPeriodFromPREV = 0
                                     vdCHistYearPeriodTillPREV = 0
                                     vdCHistYearFromPREV       = 0.
                    end. /* first-of */
    
                    if vlSkipCombination
                    then next.
                    
                    /* ======================================================== */
                    /* Recalculate the Balances                                 */
                    /* ======================================================== */
                    assign vdCHistBalanceTC = vdCHistBalanceTC + CHist.CHistMovementDebitTC - CHist.CHistMovementCreditTC
                           vdCHistBalanceLC = vdCHistBalanceLC + CHist.CHistMovementDebitLC - CHist.CHistMovementCreditLC
                           vdCHistBalanceCC = vdCHistBalanceCC + CHist.CHistMovementDebitCC - CHist.CHistMovementCreditCC.
    
                    /* ======================================================== */
                    /* Check if the Balance is OK                               */
                    /* ======================================================== */ 
                    assign vlBalanceIncorrect = vdCHistBalanceTC <> CHist.CHistBalanceTC or
                                                vdCHistBalanceLC <> CHist.CHistBalanceLC or
                                                vdCHistBalanceCC <> CHist.CHistBalanceCC.
    
                    if vlBalanceIncorrect
                    then do :
    
                        find journal of CHist no-lock no-error. 
    
                        find tRebuild where
                             tRebuild.tcCompanyCode = Company.CompanyCode and
                             tRebuild.tcCreditorCode = Creditor.CreditorCode
                             no-error.   
    
                        if not available tRebuild
                        then do:
                            create tREbuild.
                            assign tRebuild.tcCompanyCode    = Company.CompanyCode
                                   tRebuild.tcCreditorCode   = Creditor.CreditorCode
                                   tRebuild.tiYearPeriodFrom = vdCHistYearPeriodFromPREV.
                        end.   
                        assign vlSkipCombination = yes.
    
                        assign oiErrorNumbers = oiErrorNumbers + 1.
                        /* log error */  
                        PUT STREAM sTechOut unformatted vcPrefix 
                            'ERROR '
                            'BALANCE '
                            vcNewCombination format 'x' ' ' 
                            vcSelCompanyCode ' * ' 
                            Creditor.CreditorCode format 'x(10)' ' * '              
                            CHist.CHistYearPeriodFrom ' * ' 
                            CHist.CHistYearPeriodTill ' * '             
                            ' JN:' CHist.Journal_ID  format '9999999999'                      
                            ' GL:' CHist.GL_ID    format '9999999999'
                            ' DI:' CHist.Division_ID  format '9999999999'
                            ' CC:' CHist.CostCentre_ID format '9999999999'
                            ' PR:' CHist.Project_ID format '9999999999'
                            ' CU:' CHist.Currency_ID format '9999999999'
                            'Error in Balance fields '
                            vlBalanceIncorrect 
                            skip.
    
                        if vcNewCombination <> '' then assign vcNewCombination = '':U. 
                    end.
    
                    /* ======================================================== */
                    /* Check if the Period has no holes                         */
                    /* ======================================================== */ 
                    if vdCHistYearPeriodTillPREV <> 0 and CHist.CHistYearPeriodFrom <> vdCHistYearPeriodTillPREV + 1
                    then do :   
                        find journal of CHist no-lock no-error.
            
                        find tRebuild where
                             tRebuild.tcCompanyCode  = Company.CompanyCode and
                             tRebuild.tcCreditorCode = Creditor.CreditorCode
                             no-error.
                        if not available tRebuild
                        then do:      
                            create tRebuild.
                            assign tRebuild.tcCompanyCode    = Company.CompanyCode
                                   tRebuild.tcCreditorCode   = Creditor.CreditorCode
                                   tRebuild.tiYearPeriodFrom = vdCHistYearPeriodFromPREV.       
                        end.
                        assign vlSkipCombination = yes.
                        
                        assign oiErrorNumbers = oiErrorNumbers + 1.
                        /* log error */
                        PUT STREAM sTechOut unformatted vcPrefix 
                            'ERROR '
                            'PERIOD '
                            vcNewCombination format 'x' ' ' 
                            vcSelCompanyCode ' * ' 
                            Creditor.CreditorCode format 'x(10)' ' * '              
                            CHist.CHistYearPeriodFrom ' * ' 
                            CHist.CHistYearPeriodTill ' * '                         
                            ' JN:' CHist.Journal_ID  format '9999999999'                      
                            ' GL:' CHist.GL_ID    format '9999999999'
                            ' DI:' CHist.Division_ID  format '9999999999'
                            ' CC:' CHist.CostCentre_ID format '9999999999'
                            ' PR:' CHist.Project_ID format '9999999999'
                            ' CU:' CHist.Currency_ID format '9999999999'
                            'Error: Period not in sequence.'  ' ' vdCHistYearPeriodTillPREV 
                            skip. 
    
                        if vcNewCombination <> '' then assign vcNewCombination = '':U. 
                    end.
    
                    /* ======================================================== */
                    /* Check if the Last records has 999999 as till period      */
                    /* ======================================================== */  
                    if last-of (CHist.Currency_ID) and 
                       CHist.CHistYearPeriodTill <> 999999
                    then do :
                        
                        find journal of CHist no-lock no-error.
                        
                        find tRebuild where
                             tRebuild.tcCompanyCode = Company.CompanyCode and
                             tRebuild.tcCreditorCode = Creditor.CreditorCode
                             no-error.
    
                        if not available tRebuild
                        then do:
                            create tREbuild.
                            assign tRebuild.tcCompanyCode    = Company.CompanyCode
                                   tRebuild.tcCreditorCode   = Creditor.CreditorCode
                                   tRebuild.tiYearPeriodFrom = vdCHistYearPeriodFromPREV.
                        end.
                        assign vlSkipCombination = yes.
    
                        assign oiErrorNumbers = oiErrorNumbers + 1.
                        /* log error */
                        PUT STREAM sTechOut unformatted vcPrefix 
                            'ERROR '
                            ' END-RECORD '
                            vcNewCombination format 'x' ' ' 
                            vcSelCompanyCode ' * ' 
                            Creditor.CreditorCode format 'x(10)' ' * '              
                            CHist.CHistYearPeriodFrom ' * ' 
                            CHist.CHistYearPeriodTill ' * '             
                            ' JN:' CHist.Journal_ID  format '9999999999'                      
                            ' GL:' CHist.GL_ID    format '9999999999'
                            ' DI:' CHist.Division_ID  format '9999999999'
                            ' CC:' CHist.CostCentre_ID format '9999999999'
                            ' PR:' CHist.Project_ID format '9999999999'
                            ' CU:' CHist.Currency_ID format '9999999999'
                            'Error: 999999 record is missing.'
                            skip. 
                        
                        if vcNewCombination <> '' then assign vcNewCombination = '':U. 
                    end.
                    
                    assign vdCHistYearPeriodFromPREV = CHist.CHistYearPeriodFrom
                           vdCHistYearPeriodTillPREV = CHist.CHistYearPeriodTill
                           vdCHistYearFromPREV       = vdCHistYearPeriodFromPREV / 100.
    
                end. /* for each CHist */
            
            end. /* NOT CAN FIND ttRebuild */
        end. /* FOR EACH CREDITOR */    
    end. /* FOR EACH COMPANY, GL */
end.


PUT STREAM sTechOut unformatted vcPrefix2 + "END Validation CHist reconciliation with CInvoice." skip.

if oiReturnStatus = -98
then assign oiReturnStatus = 0.