project QadFinancials > class TConCheck > method TechnicalValidation11ARInvoiceData

Description

AR Invoice Data


Parameters


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


Internal usage


QadFinancials
method TConCheck.TechnicalValidation


program code (program1/tconcheck.p)

/* ================================================================================= */
/* This method validates if for each each DInvoice which is linked to a SalesOrder a */
/* ih_hist exists and vica versa                                                     */
/*   DInvoice <--> ih_hist                                                           */
/* ================================================================================= */
assign oiReturnStatus = -98.
empty temp-table tNrMstrTemp.

define buffer bihhist for ih_hist.
define buffer bCompany for Company.

PUT STREAM sTechOut unformatted " " skip. 
PUT STREAM sTechOut unformatted vcPrefix2 + "START Validation of AR Invoice Data (" + STRING (itFromDate) + ")" skip.

IPR_ARINVOICE_Check: 
do:
    
    assign viRecordCount1         = 0
           viMissingRecordCount1  = 0
           viMatchingRecordCount1 = 0.

    for each Company no-lock  where 
             Company.company_id = iiCompanyID,
	    each DInvoice of Company no-lock where
             DInvoice.DInvoicePostingYearPeriod = iiSelectedYearPeriod,
        first DInvoiceOpInfo of DInvoice no-lock where
              DInvoiceOpInfoType = {&DINVOICEOPINFOTYPE-SO},
        first Journal of DInvoice no-lock:
        
        assign viRecordCount1 = viRecordCount1 + 1
/*               vcInvoiceNbr    = string(DInvoicePostingYear, "9999")
                                + "/" + trim(string(JournalCode, "x(8)"))
                                + string(DInvoiceVoucher, "9999999999")*/
               vcJournalCode  = trim(string(JournalCode, "x(8)"))
               vcInvoiceNbr   = "":U
               viCount = 0.
        find Domains of Company no-lock no-error.

        find first tNrMstrTemp where tNrMstrTemp.tcJournalCode = vcJournalCode and
                                     tNrMstrTemp.tcDomainCode = DomainCode no-error.
        if available tNrMstrTemp
        then do:
            assign vcNumRangeSegVal     = tNrMstrTemp.tcNrSegValue
                   vcNumRangeSegType    = tNrMstrTemp.tcNrSegType
                   vcNumRangeSegFormat  = tNrMstrTemp.tcNrSegFormat.
        end.
        else do:
            /* Get Invoice Number */
            CREATE BUFFER vhNrMstrBuffer FOR TABLE "nr_mstr".
            CREATE QUERY vhNrMstrQuery.
            vhNrMstrQuery:SET-BUFFERS(vhNrMstrBuffer).
    
            vhNrMstrQuery:QUERY-PREPARE("FOR EACH nr_mstr where nr_mstr.nr_domain = " + quoter(DomainCode) + " AND
                                                                nr_mstr.nr_seqid  = " + quoter(vcJournalCode) + " NO-LOCK").
    
            vhNrMstrQuery:QUERY-OPEN().
            vhNrMstrQuery:GET-FIRST().
    
            IF NOT vhNrMstrQuery:QUERY-OFF-END THEN DO:
                assign vcNumRangeSegVal     = vhNrMstrBuffer::nr_seg_value
                       vcNumRangeSegType    = vhNrMstrBuffer::nr_seg_type
                       vcNumRangeSegFormat  = vhNrMstrBuffer::nr_seg_format.
            END. /*  for each nr_mstr where nr_mstr.ih_domain = DomainCode */
    
            vhNrMstrQuery:QUERY-CLOSE().
            DELETE OBJECt vhNrMstrBuffer no-error.
            DELETE OBJECT vhNrMstrQuery no-error.

            create tNrMstrTemp.
            assign tNrMstrTemp.tcJournalCode = vcJournalCode
                   tNrMstrTemp.tcNrSegValue = vcNumRangeSegVal
                   tNrMstrTemp.tcNrSegType = vcNumRangeSegType
                   tNrMstrTemp.tcNrSegFormat = vcNumRangeSegFormat.
        end.

        do viDataNbr = 1 to num-entries(vcNumRangeSegVal):
           assign vcSegType   = trim(entry(viDataNbr, vcNumRangeSegType))
                  vcSegValue  = trim(entry(viDataNbr, vcNumRangeSegVal))
                  vcSegFormat = trim(entry(viDataNbr, vcNumRangeSegFormat)).
           case vcSegType:         
              /* Fixed Seperator or Daybook*/
              when "4" then
                 assign vcInvoiceNbr = vcInvoiceNbr + vcSegValue.
              /* Fiscal - Year */
              when "3"
              then do:
                if vcSegFormat = "Y2" and length(vcSegValue, "character") = 2
                then assign vcInvoiceNbr = 
                        vcInvoiceNbr + string(DInvoicePostingYear - truncate((DInvoicePostingYear / 100 ),0) * 100).
                else if vcSegFormat = "Y1" and length(vcSegValue, "character") = 1
                then assign vcInvoiceNbr = 
                        vcInvoiceNbr + string(DInvoicePostingYear - truncate((DInvoicePostingYear / 10 ),0) * 10).
                else assign vcInvoiceNbr = vcInvoiceNbr + string(DInvoicePostingYear, "9999").
              end.
             
              /* Date - Year */
              when "2"
              then assign vcInvoiceNbr = vcInvoiceNbr + string(DInvoicePostingYear, "9999").
             
              /* Int - Voucher */
              when "1"
              then do:                                   
                 assign vcInvoiceNbr = vcInvoiceNbr + string(DInvoiceVoucher, vcSegFormat).                  
              end.
           end.  
        end. /* End do viDataNbr = 1 */

        CREATE BUFFER vhIhHistBuffer FOR TABLE "ih_hist".
        CREATE BUFFER vhSiMstrBuffer FOR TABLE "si_mstr".
        CREATE QUERY vhIhHistQuery.
                              
        vhIhHistQuery:SET-BUFFERS(vhIhHistBuffer,vhSiMstrBuffer).

        vhIhHistQuery:QUERY-PREPARE("FOR EACH ih_hist no-lock where 
                                              ih_hist.ih_domain = " + quoter(Domains.DomainCode) + " and
                                              ih_hist.ih_inv_nbr = " + quoter(vcInvoiceNbr) + " and
                                              ih_hist.ih_nbr = " + quoter(DInvoiceOpInfo.DInvoiceOpInfoCode) + ",
                                     FIRST si_mstr where 
                                           si_mstr.si_site = ih_hist.ih_site no-lock").
        vhIhHistQuery:QUERY-OPEN().
        vhIhHistQuery:GET-FIRST().
        DO WHILE NOT vhIhHistQuery:QUERY-OFF-END:
            for first bcompany where 
                      bCompany.CompanyCode = vhSiMstrBuffer::si_entity and
                      bCompany.Company_ID  = iiCompanyID 
                      no-lock:
                assign viCount = viCount  + 1.
            end. 
            vhIhHistQuery:GET-NEXT().
        END. /*  for each ih_hist where ih_hist.ih_domain = DomainCode */

        vhIhHistQuery:QUERY-CLOSE().
        DELETE OBJECT vhSiMstrBuffer no-error.
        DELETE OBJECt vhIhHistBuffer no-error.
        DELETE OBJECT vhIhHistQuery no-error.

          /* Check to see if Invoice History has been archived */
        if viCount = 0 
        then do:
    
            CREATE BUFFER vhIhHistBuffer FOR TABLE "ih_hist".
            CREATE QUERY vhIhHistQuery.
            vhIhHistQuery:SET-BUFFERS(vhIhHistBuffer).
            
            vhIhHistQuery:QUERY-PREPARE("FOR EACH ih_hist where 
                                                  ih_hist.ih_domain    = " + quoter(DomainCode) + " AND
                                                  ih_hist.ih_eff_date <= " + string(DInvoicePostingDate) + 
                                                  " NO-LOCK").
            
            vhIhHistQuery:QUERY-OPEN().
            vhIhHistQuery:GET-FIRST().
            IF NOT vhIhHistQuery:QUERY-OFF-END 
            THEN DO:
                     viMissingRecordCount1 = viMissingRecordCount1 + 1.
                     assign oiErrorNumbers = oiErrorNumbers + 1.
                     PUT STREAM sTechOut unformatted vcPrefix + 
                                  "** (0010) No ih_hist table record exists for DInvoiceOpInfoCode = " + DInvoiceOpInfoCode
                                 + " AND DInvoicePostingYear = " + string(DInvoicePostingYear)
                                 + " AND JournalCode = " + JournalCode
                                 + " AND DInvoiceVoucher = " + string(DInvoiceVoucher)
                                 + " AND DInvoiceDate = " + string(DInvoiceDate)
                                 + " AND DomainCode = " + DomainCode skip. 
            end.  /* if viCount = 0 */
            vhIhHistQuery:QUERY-CLOSE.
            DELETE OBJECT vhIhHistBuffer no-error.
            DELETE OBJECT vhIhHistQuery no-error.
        end.    
        else do:
            viMatchingRecordCount1 = viMatchingRecordCount1  + viCount.
            if viCount > 1 then do:
                assign oiErrorNumbers = oiErrorNumbers + 1.
                PUT STREAM sTechOut unformatted vcPrefix + 
                              " Duplicate records found: " + string(viCount)
                            + " ih_hist records exist for DInvoiceOpInfoCode = " + DInvoiceOpInfoCode
                            + " AND DInvoicePostingYear = " + string(DInvoicePostingYear)
                            + " AND JournalCode = " + JournalCode
                            + " AND DInvoiceVoucher = " + string(DInvoiceVoucher)
                            + " AND DInvoiceDate = " + string(DInvoiceDate)
                            + " AND DomainCode = " + DomainCode skip.
            end.
        end.
    end. /* for each Company no-lock */
    CREATE BUFFER vhIhHistBuffer FOR TABLE "ih_hist".
    CREATE BUFFER vhSiMstrBuffer FOR TABLE "si_mstr".
    CREATE BUFFER vhDomMstrBuffer FOR TABLE "dom_mstr".
    CREATE BUFFER vhCompanyBuffer FOR TABLE "Company".
    CREATE BUFFER vhNrMstrBuffer FOR TABLE "nr_mstr".

    CREATE QUERY vhIhHistQuery.
    vhIhHistQuery:SET-BUFFERS(vhDomMstrBuffer,vhIhHistBuffer,vhNrMstrBuffer,vhSiMstrBuffer,vhCompanyBuffer).
    vhIhHistQuery:QUERY-PREPARE("FOR EACH dom_mstr no-lock,
                                 EACH ih_hist no-lock where ih_hist.ih_domain = dom_mstr.dom_domain AND
                                                             ih_hist.ih_eff_date >= " + string(itFromDate) + " AND
                                                             ih_hist.ih_eff_date <= " + string(itToDate) + ",
                                 EACH nr_mstr where nr_mstr.nr_domain  = ih_hist.ih_domain AND
                                                     nr_mstr.nr_seqid  = ih_hist.ih_daybook,
                                 FIRST si_mstr no-lock where si_mstr.si_site = ih_hist.ih_site, 
                                 FIRST Company where Company.CompanyCode = si_mstr.si_entity and 
                                                     Company.Company_ID = " + string(iiCompanyID) + " NO-LOCK").
    vhIhHistQuery:QUERY-OPEN().
    vhIhHistQuery:GET-FIRST().
    DO WHILE NOT vhIhHistQuery:QUERY-OFF-END:
        /*Parse ih_inv_nbr into Posting Year, Journal Code and Voucher */
        /*Format is YYYY/JJJJJJJJ999999999 
          However the JournalCode can be vary in size up to a maximum of 8 characters
          so in the code below this is taken into account by considering the overall
          length of the string*/
        assign  viLength = length(vhIhHistBuffer::ih_inv_nbr,"character") 
/*               viPostingYear = integer(substring(vhIhHistBuffer::ih_inv_nbr, 1, 4,"character"))
               vcJournalCode = substring(vhIhHistBuffer::ih_inv_nbr, 6, viLength - 14,"character")
               viVoucher = integer(substring(vhIhHistBuffer::ih_inv_nbr, viLength - 8, 9,"character")) */
               viCount = 0
               viRecordCount2 = viRecordCount2 + 1
               vcInvoiceNbr         = vhIhHistBuffer::ih_inv_nbr
               vcJournalCode        = vhIhHistBuffer::ih_daybook
               viPostingYear        = Year(vhIhHistBuffer::ih_inv_date)
               vcNumRangeSegVal     = vhNrMstrBuffer::nr_seg_value
               vcNumRangeSegType    = vhNrMstrBuffer::nr_seg_type
               viInvNbrPos          = 0.

        do viDataNbr = 1 to num-entries(vcNumRangeSegVal):
            assign vcSegType   = trim(entry(viDataNbr, vcNumRangeSegType))
                   vcSegValue  = trim(entry(viDataNbr, vcNumRangeSegVal)).
            case vcSegType:
                /* Fixed Seperator or Daybook*/
                when "4" then
                   viInvNbrPos = viInvNbrPos + length(vcSegValue, "CHARACTER").
                /* Fiscal - Year */
                when "3" then
                    viInvNbrPos = viInvNbrPos + length(vcSegValue, "CHARACTER").
                /* Date - Year */
                when "2" then
                    viInvNbrPos = viInvNbrPos + length(vcSegValue, "CHARACTER").
                 
                /* Int - Voucher */
                when "1"
                then do:                                   
                    assign viVoucher = int(substring(vcInvoiceNbr,
                                                     viInvNbrPos + 1,
                                                     length(vcSegValue, "CHARACTER"),
                                                     "CHARACTER")) no-error.
                end.
            end. 
        end.

        for each DInvoice no-lock
            where DInvoicePostingYear = viPostingYear
              and DInvoiceVoucher = viVoucher,
            first DInvoiceOpInfo of DInvoice no-lock
            where DInvoiceOpInfoType = "SALESORDER"
              and DInvoiceOpInfoCode = vhIhHistBuffer::ih_nbr,
            first Journal of DInvoice no-lock
            where JournalCode = vcJournalCode,
            first bCompany of DInvoice
            where bCompany.Company_ID = iiCompanyID no-lock,
            first Domains of bCompany no-lock
            where DomainCode = vhIhHistBuffer::ih_domain:
            assign viCount = viCount + 1.
        end.
        if viCount = 0 
        then do:
             viMissingRecordCount2 = viMissingRecordCount2 + 1.
             assign oiErrorNumbers = oiErrorNumbers + 1.
             PUT STREAM sTechOut unformatted vcPrefix + 
                         "** (0011) No DInvoice table record exists for ih_inv_nbr = " + vhIhHistBuffer::ih_inv_nbr
                         + " and Domain = " + vhIhHistBuffer::ih_domain
                         + " and Sales Order Number = " + vhIhHistBuffer::ih_nbr
                         + " and Customer = " + vhIhHistBuffer::ih_cust
                         + " and Invoice Date = " + string(vhIhHistBuffer::ih_inv_date) skip.
        end.
        else do:
            assign viMatchingRecordCount2 = viMatchingRecordCount2  + viCount.
            if viCount > 1 
            then do:
                 assign oiErrorNumbers = oiErrorNumbers + 1.
                 PUT STREAM sTechOut unformatted vcPrefix + 
                             "Duplicate records found: " + string(viCount)
                             + " DInvoice records exist for ih_inv_nbr = " + vhIhHistBuffer::ih_inv_nbr
                             + " and Domain = " + vhIhHistBuffer::ih_domain
                             + " and Sales Order Number = " + vhIhHistBuffer::ih_nbr
                             + " and Customer = " + vhIhHistBuffer::ih_cust
                             + " and Invoice Date = " + string(vhIhHistBuffer::ih_inv_date) skip.
            end.
        end.
        vhIhHistQuery:GET-NEXT().
    end. /* for each dom_mstr no-lock */

    vhIhHistQuery:QUERY-CLOSE().
    DELETE OBJECT vhIhHistBuffer no-error.
    DELETE OBJECT vhSiMstrBuffer no-error.
    DELETE OBJECT vhDomMstrBuffer no-error.
    DELETE OBJECT vhCompanyBuffer no-error.
    DELETE OBJECT vhNrMstrBuffer no-error.
    DELETE OBJECT vhIhHistQuery no-error.
    

    /*************************************************************************/
    /* Log summary results                                                  */
    /************************************************************************/
    PUT STREAM sTechOut unformatted vcPrefix + "Summary results:" skip.
    PUT STREAM sTechOut unformatted vcPrefix + "Total number of records in Financials table DInvoice = " + string(viRecordCount1) skip.
    PUT STREAM sTechOut unformatted vcPrefix + "Total number of records in table dom_mstr found in ih_hist = "  + string(viRecordCount2) skip.
    PUT STREAM sTechOut unformatted vcPrefix + "Total number of records in table DInvoice not found in ih_hist = "  + string(viMissingRecordCount1) skip.
    PUT STREAM sTechOut unformatted vcPrefix + "Total number of records in table DInvoice not found in ih_inv_nbr = "  + string(viMissingRecordCount2) skip. 
    PUT STREAM sTechOut unformatted vcPrefix + "Total number of records in table DInvoice found in ih_hist = "  + string(viMatchingRecordCount1) skip.
    PUT STREAM sTechOut unformatted vcPrefix + "Total number of records in table DInvoice found in ih_inv_nbr = "  + string(viMatchingRecordCount2) skip.

end. /* IPR_ARINVOICE_Check */

PUT STREAM sTechOut unformatted vcPrefix2 + "END Validation AR Invoice Data." skip.

if oiReturnStatus = -98
then assign oiReturnStatus = 0.