project QadFinancials > class TConCheck > method TechnicalValidation11ARInvoiceData
Description
AR Invoice Data
Parameters
| iiCompanyID | input | integer | |
| iiPeriodID | input | integer | |
| iiSelectedYearPeriod | input | integer | |
| itFromDate | input | date | |
| itToDate | input | date | |
| oiErrorNumbers | output | integer | |
| oiReturnStatus | output | integer | Return status of the method. |
Internal usage
QadFinancials
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.