project QadFinancials > class TConCheck > method TechnicalValidation08DHistInvoice

Description

DHist reconciliation with DInvoice


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 DHist reconciliation with DInvoice                         */
/* ================================================================================= */

assign oiReturnStatus = -98.


PUT STREAM sTechOut unformatted " " skip. 
PUT STREAM sTechOut unformatted vcPrefix2 + "START Validation of DHist reconciliation with DInvoice (" + STRING (itFromDate) + ")" skip.

IPR_AR_Check_HISTWITHINVOICE:
do:
    
    /* init variables */
    define buffer bDHist for DHist.
    empty temp-table tRebuild2.

    /* ====================================================================================== */ 
    /* 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 = 'DEBTOR':
                assign viDebtorSharedSetID = sharedset.sharedset_id.
        end. /* end each companysharedset */
        for each Debtor no-lock where Debtor.SharedSet_ID = viDebtorSharedSetID
                     by Debtor.DebtorCode :
            if not can-find (first DHist where
                                   DHist.Company_ID           = Company.Company_ID and
                                   DHist.Debtor_ID            = Debtor.Debtor_ID   and
                                   DHist.dHistYearPeriodFrom >= iiSelectedYearPeriod)   and
               not can-find (first DInvoiceMovement where
                                   DInvoiceMovement.Company_ID                  = Company.Company_ID and
                                   DInvoiceMovement.Debtor_ID                   = Debtor.Debtor_ID   and
                                   DInvoiceMovement.DInvoiceMovementYearPeriod >= 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 tDI.
                
                /*************************************************************************************/                                                         
                /* A.1. Go through all the PostingLines/PostingSaf of this Company/period            */
                /*************************************************************************************/ 
                for each DInvoiceMovement no-lock where
                         DInvoiceMovement.Company_ID                 = Company.Company_ID   and
                         DInvoiceMovement.Debtor_ID                  = Debtor.Debtor_ID and
                         DInvoiceMovement.DInvoiceMovementYearPeriod = Period.Periodyearperiod,
                    first PostingLine of DInvoiceMovement no-lock,                 
                    first Posting of PostingLine no-lock,
                    first Journal of Posting
                    no-lock :
                    
                    find tDI where
                         tDI.tcCompanyCode   = Company.CompanyCode and
                         tDI.tiYearPeriod    = (if PostingLine.PostingYearPeriod = ? then 0 else PostingLine.PostingYearPeriod)  and
                         tDI.tiJournal_ID    = (if Posting.Journal_ID = ? then 0 else Posting.Journal_ID) and 
                         tDI.tiGL_ID         = (if PostingLine.GL_ID = ? then 0 else PostingLine.GL_ID) and                  
                         tDI.tiCurrency_ID   = (if PostingLine.currency_Id = ? then 0 else PostingLine.currency_Id) and
                         tDI.tcDebtorCode    = Debtor.DebtorCode and
                         tDI.tiProject_ID    = (if PostingLine.Project_ID = ? then 0 else PostingLine.Project_ID) and
                         tDI.tiCostCentre_ID = (if PostingLine.Costcentre_ID = ? then 0 else PostingLine.Costcentre_ID) and
                         tDI.tiDivision_ID   = (if PostingLine.Division_ID = ? then 0 else PostingLine.Division_ID)
                         no-error. 
                    
                    if not available tDI
                    then do:
                        create tDI.
                        assign  
                          tDI.tcTYPE          = 'TRX ':U
                          tDI.tcCompanyCode   = Company.CompanyCode
                          tDI.tiYearPeriod    = (if PostingLine.PostingYearPeriod = ? then 0 else PostingLine.PostingYearPeriod)
                          tDI.tiJournal_ID    = (if Posting.Journal_ID = ? then 0 else Posting.Journal_ID)
                          tDI.tiGL_ID         = (if PostingLine.GL_ID = ? then 0 else PostingLine.GL_ID)                 
                          tDI.tiCurrency_ID   = (if PostingLine.currency_Id = ? then 0 else PostingLine.currency_Id)
                          tDI.tcDebtorCode    = Debtor.DebtorCode
                          tDI.tiProject_ID    = (if PostingLine.Project_ID = ? then 0 else PostingLine.Project_ID)
                          tDI.tiCostCentre_ID = (if PostingLine.Costcentre_ID = ? then 0 else PostingLine.Costcentre_ID)
                          tDI.tiDivision_ID   = (if PostingLine.Division_ID = ? then 0 else PostingLine.Division_ID).
                    end.
    
                    assign  tDI.tdTotalPostingLineCreditTC = tDI.tdTotalPostingLineCreditTC + Postingline.PostingLineCreditTC
                            tDI.tdTotalPostingLineCreditLC = tDI.tdTotalPostingLineCreditLC + Postingline.PostingLineCreditLC            
                            tDI.tdTotalPostingLineCreditCC = tDI.tdTotalPostingLineCreditCC + Postingline.PostingLineCreditCC
                            tDI.tdTotalPostingLineDebitTC  = tDI.tdTotalPostingLineDebitTC  + Postingline.PostingLineDebitTC
                            tDI.tdTotalPostingLineDebitLC  = tDI.tdTotalPostingLineDebitLC  + Postingline.PostingLineDebitLC
                            tDI.tdTotalPostingLineDebitCC  = tDI.tdTotalPostingLineDebitCC  + Postingline.PostingLineDebitCC.
    
                end.  /* A.1 for each PostingLine */
    
                /*************************************************************************************/                                                         
                /* A.2. Go through all the History                                                   */
                /*************************************************************************************/
                for each DHist where
                         DHist.Company_ID          = Company.Company_ID   and
                         DHist.Debtor_ID           = Debtor.Debtor_ID and
                         DHist.dHistYearPeriodFrom = Period.Periodyearperiod
                         no-lock:
    
                    find tDI where
                         tDI.tcCompanyCode   = Company.CompanyCode and
                         tDI.tiYearPeriod    = (if DHist.DHistYearPeriodFrom = ? then 0 else DHist.DHistYearPeriodFrom)  and
                         tDI.tiJournal_ID    = (if DHist.Journal_ID = ? then 0 else DHist.Journal_ID) and 
                         tDI.tiCurrency_ID   = (if DHist.currency_Id = ? then 0 else DHist.currency_Id) and
                         tDI.tiGL_ID         = (if DHist.GL_ID = ? then 0 else DHist.GL_ID) and                   
                         tDI.tcDebtorCode    = Debtor.DebtorCode and
                         tDI.tiProject_ID    = (if DHist.Project_ID = ? then 0 else DHist.Project_ID) and
                         tDI.tiCostCentre_ID = (if DHist.Costcentre_ID = ? then 0 else DHist.Costcentre_ID) and
                         tDI.tiDivision_ID   = (if DHist.Division_ID = ? then 0 else DHist.Division_ID)
                         no-error.
    
                    if not available tDI
                    then do :
                        create tDI.
                        assign    
                          tDI.tcTYPE          = 'HIST':U
                          tDI.tcCompanyCode   = Company.CompanyCode
                          tDI.tiYearPeriod    = (if DHist.DHistYearPeriodFrom = ? then 0 else DHist.DHistYearPeriodFrom)
                          tDI.tiJournal_ID    = (if DHist.Journal_ID = ? then 0 else DHist.Journal_ID)
                          tDI.tiGL_ID         = (if DHist.GL_ID = ? then 0 else DHist.GL_ID)                  
                          tDI.tiCurrency_ID   = (if DHist.currency_Id = ? then 0 else DHist.currency_Id)
                          tDI.tcDebtorCode    = Debtor.DebtorCode
                          tDI.tiProject_ID    = (if DHist.Project_ID = ? then 0 else DHist.Project_ID)
                          tDI.tiCostCentre_ID = (if DHist.Costcentre_ID = ? then 0 else DHist.Costcentre_ID)
                          tDI.tiDivision_ID   = (if DHist.Division_ID = ? then 0 else DHist.Division_ID).
                    end. /* if not available tdi */
                    else assign tDI.tcType = 'BOTH':U.
    
                    assign  tDI.tdDHistMovementCreditTC = tDI.tdDHistMovementCreditTC + DHist.DHistMovementCreditTC
                            tDI.tdDHistMovementCreditLC = tDI.tdDHistMovementCreditLC + DHist.DHistMovementCreditLC            
                            tDI.tdDHistMovementCreditCC = tDI.tdDHistMovementCreditCC + DHist.DHistMovementCreditCC
                            tDI.tdDHistMovementDebitTC  = tDI.tdDHistMovementDebitTC  + DHist.DHistMovementDebitTC
                            tDI.tdDHistMovementDebitLC  = tDI.tdDHistMovementDebitLC  + DHist.DHistMovementDebitLC
                            tDI.tdDHistMovementDebitCC  = tDI.tdDHistMovementDebitCC  + DHist.DHistMovementDebitCC.
    
                end. /* A.2 for each dHist */
    
                /*************************************************************************************/                                                         
                /* A.3 See if there any differences and report it                                    */
                /*************************************************************************************/
                for each tDI where
                         tDI.tcCompanyCode  = Company.CompanyCode and
                         tDI.tcDebtorCode   = Debtor.DebtorCode
                    by tDI.tcCompanyCode 
                    by tDI.tcDebtorCode
                    by tDI.tiYearPeriod :
                    
                    if tDI.tdTotalPostingLineCreditTC <> tDI.tdDHistMovementCreditTC or
                       tDI.tdTotalPostingLineCreditLC <> tDI.tdDHistMovementCreditLC or
                       tDI.tdTotalPostingLineCreditCC <> tDI.tdDHistMovementCreditCC or
                       tDI.tdTotalPostingLineDebitTC  <> tDI.tdDHistMovementDebitTC  or
                       tDI.tdTotalPostingLineDebitLC  <> tDI.tdDHistMovementDebitLC  or 
                       tDI.tdTotalPostingLineDebitCC  <> tDI.tdDHistMovementDebitCC
                    then do : 
                        /* Create indication rebuild is needed */ 
                        find tRebuild2 where
                             tRebuild2.tcCompanyCode  = tDI.tcCompanyCode and
                             tRebuild2.tcDebtorCode   = tDI.tcDebtorCode
                             no-error.
                        if not available tRebuild2
                        then do:
                            create tRebuild2.
                            assign tRebuild2.tcCompanyCode    = tDI.tcCompanyCode
                                   tRebuild2.tcDebtorCode     = tDI.tcDebtorCode
                                   tRebuild2.tiYearPeriodFrom = tDI.tiYearPeriod.
                        end.
                        

                        assign oiErrorNumbers = oiErrorNumbers + 1.
                        /* log error */
                        PUT STREAM sTechOut unformatted vcPrefix 
                            'ERROR MOVEMENT '
                            tDI.tcTYPE ' * '
                            tDI.tcCompanyCode  ' * '
                            tDI.tcDebtorCode      format 'x(10)' ' * '                         
                            tDI.tiYearPeriod  ' * ' 
                            'JN:' tDI.tiJournal_ID  format '9999999999' ' ' 
                            'CU:' tDI.tiCurrency_ID  format '9999999999' ' ' 
                            'GL:' tDI.tiGL_ID  format '9999999999' ' '                         
                            'PR:' tDI.tiProject_ID  format '9999999999' ' ' 
                            'CC:' tDI.tiCostCentre_ID format '9999999999' ' ' 
                            'DI:' tDI.tiDivision_ID  format '9999999999' ' ' 
                            tDI.tdTotalPostingLineDebitTC  <> tDI.tdDHistMovementDebitTC  format 'yes/no' ' ' 
                            tDI.tdTotalPostingLineDebitLC  <> tDI.tdDHistMovementDebitLC  format 'yes/no' ' ' 
                            tDI.tdTotalPostingLineDebitCC  <> tDI.tdDHistMovementDebitCC  format 'yes/no' ' * '    
                            tDI.tdTotalPostingLineCreditTC <> tDI.tdDHistMovementCreditTC format 'yes/no' ' ' 
                            tDI.tdTotalPostingLineCreditLC <> tDI.tdDHistMovementCreditLC format 'yes/no' ' ' 
                            tDI.tdTotalPostingLineCreditCC <> tDI.tdDHistMovementCreditCC format 'yes/no' ' '  
                            
                            tDI.tdTotalPostingLineDebitTC  ' ' tDI.tdDHistMovementDebitTC  ' * ' 
                            tDI.tdTotalPostingLineDebitLC  ' ' tDI.tdDHistMovementDebitLC  ' * ' 
                            tDI.tdTotalPostingLineDebitCC  ' ' tDI.tdDHistMovementDebitCC  '  * '
                            tDI.tdTotalPostingLineCreditTC ' ' tDI.tdDHistMovementCreditTC ' * ' 
                            tDI.tdTotalPostingLineCreditLC ' ' tDI.tdDHistMovementCreditLC ' * '      
                            tDI.tdTotalPostingLineCreditCC ' ' tDI.tdDHistMovementCreditCC ' * '        
                            skip.
    
                    end.  /* corruption */   
    
                end. /* A.3 for each tDI */ 
                
            end. /* for each Period */  

            /* *************************************************************************** */
            /* PART TWO : check on the balance fields of the current period                */
            /* *************************************************************************** */ 
            if not can-find (first tRebuild2 where
                                   tRebuild2.tcCompanyCode = Company.CompanyCode and
                                   tRebuild2.tcDebtorCode  = Debtor.DebtorCode)
            then do:
    
                for each DHist of Company no-lock where
                         DHist.Debtor_ID  = Debtor.Debtor_ID and
                         DHist.DHistYearPeriodFrom >= iiSelectedYearPeriod 
                         break by DHist.Company_ID
                               by DHist.Journal_ID                        
                               by DHist.Debtor_ID                       
                               by DHist.GL_ID                       
                               by DHist.Division_ID               
                               by DHist.CostCentre_ID
                               by DHist.Project_ID                                                      
                               by DHist.Currency_ID
                               by DHist.DHistYearPeriodFrom:
    
                    /* ======================================================== */
                    /* Initialize when a new combiation is started              */
                    /* ======================================================== */
                    if first-of (DHist.Currency_ID)
                    then do:
                        assign vlSkipCombination = no
                               vcNewCombination = ">":U.
    
                        if iiSelectedYearPeriod <> 0
                        then find last  bDHist where
                                        bDHist.Company_ID          = DHist.Company_ID    and
                                        bDHist.Debtor_ID           = DHist.Debtor_ID     and
                                        bDHist.Journal_ID          = DHist.Journal_ID    and
                                        bDHist.GL_ID               = DHist.GL_ID         and                                
                                        bDHist.Currency_ID         = DHist.Currency_ID   and
                                        bDHist.Division_ID         = DHist.Division_ID   and
                                        bDHist.Project_ID          = DHist.Project_ID    and
                                        bDHist.CostCentre_ID       = DHist.CostCentre_ID and
                                        bDHist.DHistYearPeriodFrom < DHist.DHistYearPeriodFrom
                                        no-lock no-error.
    
                         if iiSelectedYearPeriod <> 0 and available bDHist
                         then assign vdDHistBalanceCC = bDHist.DHistBalanceCC
                                     vdDHistBalanceLC = bDHist.DHistBalanceLC
                                     vdDHistBalanceTC = bDHist.DHistBalanceTC
                   
                                     vdDHistYearPeriodFromPREV = bDHist.DHistYearPeriodFrom
                                     vdDHistYearPeriodTillPREV = bDHist.DHistYearPeriodTill
                                     vdDHistYearFromPREV       = bDHist.DHistYearPeriodFrom / 100.
                                      
                         else assign vdDHistBalanceCC = 0
                                     vdDHistBalanceLC = 0
                                     vdDHistBalanceTC = 0
                                     
                                     vdDHistYearPeriodFromPREV = 0
                                     vdDHistYearPeriodTillPREV = 0
                                     vdDHistYearFromPREV       = 0.
                    end. /* first-of */
    
                    if vlSkipCombination
                    then next.
                    
                    /* ======================================================== */
                    /* Recalculate the Balances                                 */
                    /* ======================================================== */
                    assign vdDHistBalanceTC = vdDHistBalanceTC + DHist.DHistMovementDebitTC - DHist.DHistMovementCreditTC
                           vdDHistBalanceLC = vdDHistBalanceLC + DHist.DHistMovementDebitLC - DHist.DHistMovementCreditLC
                           vdDHistBalanceCC = vdDHistBalanceCC + DHist.DHistMovementDebitCC - DHist.DHistMovementCreditCC.
    
                    /* ======================================================== */
                    /* Check if the Balance is OK                               */
                    /* ======================================================== */ 
                    assign vlBalanceIncorrect = vdDHistBalanceTC <> DHist.DHistBalanceTC or
                                                vdDHistBalanceLC <> DHist.DHistBalanceLC or
                                                vdDHistBalanceCC <> DHist.DHistBalanceCC.
    
                    if vlBalanceIncorrect
                    then do :
    
                        find journal of DHist no-lock no-error. 
    
                        find tRebuild2 where
                             tRebuild2.tcCompanyCode = Company.CompanyCode and
                             tRebuild2.tcDebtorCode  = Debtor.DebtorCode
                             no-error.   
    
                        if not available tRebuild2
                        then do:
                            create tREbuild2.
                            assign tRebuild2.tcCompanyCode    = Company.CompanyCode
                                   tRebuild2.tcDebtorCode     = Debtor.DebtorCode
                                   tRebuild2.tiYearPeriodFrom = vdDHistYearPeriodFromPREV.
                        end.   
                        assign vlSkipCombination = yes.
    

                        assign oiErrorNumbers = oiErrorNumbers + 1.
                        /* log error */  
                        PUT STREAM sTechOut unformatted vcPrefix 
                            'ERROR '
                            'BALANCE '
                            vcNewCombination format 'x' ' ' 
                            vcSelCompanyCode ' * ' 
                            Debtor.DebtorCode format 'x(10)' ' * '              
                            DHist.DHistYearPeriodFrom ' * ' 
                            DHist.DHistYearPeriodTill ' * '             
                            ' JN:' DHist.Journal_ID  format '9999999999'                      
                            ' GL:' DHist.GL_ID    format '9999999999'
                            ' DI:' DHist.Division_ID  format '9999999999'
                            ' CC:' DHist.CostCentre_ID format '9999999999'
                            ' PR:' DHist.Project_ID format '9999999999'
                            ' CU:' DHist.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 vdDHistYearPeriodTillPREV <> 0 and DHist.DHistYearPeriodFrom <> vdDHistYearPeriodTillPREV + 1
                    then do :   
                        find journal of DHist no-lock no-error.
            
                        find tRebuild2 where
                             tRebuild2.tcCompanyCode  = Company.CompanyCode and
                             tRebuild2.tcDebtorCode   = Debtor.DebtorCode
                             no-error.
                        if not available tRebuild2
                        then do:      
                            create tRebuild2.
                            assign tRebuild2.tcCompanyCode    = Company.CompanyCode
                                   tRebuild2.tcDebtorCode     = Debtor.DebtorCode
                                   tRebuild2.tiYearPeriodFrom = vdDHistYearPeriodFromPREV.       
                        end.
                        assign vlSkipCombination = yes.
                        
                        assign oiErrorNumbers = oiErrorNumbers + 1.
                        /* log error */
                        PUT STREAM sTechOut unformatted vcPrefix 
                            'ERROR '
                            'PERIOD '
                            vcNewCombination format 'x' ' ' 
                            vcSelCompanyCode ' * ' 
                            Debtor.DebtorCode format 'x(10)' ' * '              
                            DHist.DHistYearPeriodFrom ' * ' 
                            DHist.DHistYearPeriodTill ' * '                         
                            ' JN:' DHist.Journal_ID  format '9999999999'                      
                            ' GL:' DHist.GL_ID    format '9999999999'
                            ' DI:' DHist.Division_ID  format '9999999999'
                            ' CC:' DHist.CostCentre_ID format '9999999999'
                            ' PR:' DHist.Project_ID format '9999999999'
                            ' CU:' DHist.Currency_ID format '9999999999'
                            'Error: Period not in sequence.'  ' ' vdDHistYearPeriodTillPREV 
                            skip. 
    
                        if vcNewCombination <> '' then assign vcNewCombination = '':U. 
                    end.
    
                    /* ======================================================== */
                    /* Check if the Last records has 999999 as till period      */
                    /* ======================================================== */  
                    if last-of (DHist.Currency_ID) and 
                       DHist.DHistYearPeriodTill <> 999999
                    then do :
                        
                        find journal of DHist no-lock no-error.
                        
                        find tRebuild2 where
                             tRebuild2.tcCompanyCode = Company.CompanyCode and
                             tRebuild2.tcDebtorCode  = Debtor.DebtorCode
                             no-error.
    
                        if not available tRebuild2
                        then do:
                            create tREbuild2.
                            assign tRebuild2.tcCompanyCode    = Company.CompanyCode
                                   tRebuild2.tcDebtorCode     = Debtor.DebtorCode
                                   tRebuild2.tiYearPeriodFrom = vdDHistYearPeriodFromPREV.
                        end.
                        assign vlSkipCombination = yes.
    
                        assign oiErrorNumbers = oiErrorNumbers + 1.
                        /* log error */
                        PUT STREAM sTechOut unformatted vcPrefix 
                            'ERROR '
                            ' END-RECORD '
                            vcNewCombination format 'x' ' ' 
                            vcSelCompanyCode ' * ' 
                            Debtor.DebtorCode format 'x(10)' ' * '              
                            DHist.DHistYearPeriodFrom ' * ' 
                            DHist.DHistYearPeriodTill ' * '             
                            ' JN:' DHist.Journal_ID  format '9999999999'                      
                            ' GL:' DHist.GL_ID    format '9999999999'
                            ' DI:' DHist.Division_ID  format '9999999999'
                            ' CC:' DHist.CostCentre_ID format '9999999999'
                            ' PR:' DHist.Project_ID format '9999999999'
                            ' CU:' DHist.Currency_ID format '9999999999'
                            'Error: 999999 record is missing.'
                            skip. 
                        
                        if vcNewCombination <> '' then assign vcNewCombination = '':U. 
                    end.
                    
                    assign vdDHistYearPeriodFromPREV = DHist.DHistYearPeriodFrom
                           vdDHistYearPeriodTillPREV = DHist.DHistYearPeriodTill
                           vdDHistYearFromPREV       = vdDHistYearPeriodFromPREV / 100.
    
                end. /* for each dHist */
            
            end. /* NOT CAN FIND ttRebuild2 */
        end. /* FOR EACH DEBTOR */    
    end. /* FOR EACH COMPANY, GL */
    
end.

PUT STREAM sTechOut unformatted vcPrefix2 + "END Validation DHist reconciliation with DInvoice." skip.

if oiReturnStatus = -98
then assign oiReturnStatus = 0.