project QadFinancials > class TConCheck > method TechnicalValidation07DInvoice
Description
Submethod of TechnicalValidation for checking DInvoice
Parameters
| iiCompanyID | input | integer | |
| itFromDate | input | date | |
| itToDate | input | date | |
| oiErrorNumbers | output | integer | |
| iiYearPeriod | input | integer | |
| oiReturnStatus | output | integer | Return status of the method. |
Internal usage
QadFinancials
program code (program1/tconcheck.p)
/**********************************************************************************/
/* IPR_AR_CHECK */
/**********************************************************************************/
PUT STREAM sTechOut unformatted " " skip.
PUT STREAM sTechOut unformatted vcPrefix2 + "Start Validation of AR (" + STRING (itFromDate) + ") " skip.
DEF BUFFER bperiod FOR period.
DEF BUFFER bDInvoice for DInvoice.
assign viPrevCompanyID = -1.
repeat:
find first DInvoice where DInvoicePostingYearPeriod = iiYearPeriod and
DInvoice.Company_ID > viPrevCompanyID
use-index CyPeriodYearPeriod
no-lock no-error.
if not available DInvoice
then leave.
else do:
assign viPrevCompanyID = DInvoice.Company_ID.
if not can-find (Company of DInvoice)
then do:
for each DInvoice where
DInvoice.Company_ID = viPrevCompanyID and
DInvoicePostingYearPeriod = iiYearPeriod
no-lock:
PUT STREAM sTechOut unformatted vcPrefix + "DInvoice;" + string (DInvoice.DInvoice_ID) + ";DInvoice found with no valid company" skip.
assign oiErrorNumbers = oiErrorNumbers + 1.
end. /* for each DInvoice */
end. /* if */
end. /* else do */
end. /* repeat */
assign viPrevCompanyID = -1.
repeat:
find first DInvoiceMovement where DInvoiceMovement.DInvoicemovementyearperiod = iiYearPeriod and
DInvoiceMovement.Company_ID > viPrevCompanyID
no-lock no-error.
if not available DInvoiceMovement
then leave.
else do:
assign viPrevCompanyID = DInvoiceMovement.Company_ID.
if not can-find (Company of DInvoiceMovement)
then do:
for each DInvoiceMovement where
DInvoiceMovement.Company_ID = viPrevCompanyID and
DInvoiceMovement.DInvoicemovementyearperiod = iiYearPeriod
no-lock:
PUT STREAM sTechOut unformatted vcPrefix + "DInvoiceMovement;" + string (DInvoiceMovement.DInvoiceMovement_ID) + ";DInvoiceMovement found with no valid company" skip.
assign oiErrorNumbers = oiErrorNumbers + 1.
end. /* for each DInvoiceMovement */
end. /* if */
end. /* else do */
end. /* repeat */
/* ======================================= */
/* Main loop on Company */
/* ======================================= */
for each Company no-lock where
Company.Company_ID = iiCompanyID:
ASSIGN viYearPeriod = 0.
FOR FIRST period OF Company WHERE
period.PeriodStartDate <= itFromDate AND
Period.PeriodEndDate >= itFromDate
NO-LOCK
BY Period.PeriodYearPeriod :
ASSIGN viYearPeriod = Period.PeriodYearPeriod
viPeriodID = Period.Period_ID
viPeriodYear = Period.PeriodYear
viPeriodPeriod = Period.PeriodPeriod
vtPeriodStartDate = Period.PeriodStartDate
vtPeriodEndDate = Period.PeriodEndDate
vcPeriodType = Period.PeriodType.
END.
if not available Period
then next.
/* Get sharedset of the company */
for each companysharedset no-lock where companysharedset.company_id = company.company_id,
each sharedset of companysharedset no-lock:
if sharedsettype = {&SHAREDSETTYPE-GL} then
assign viGLShareSetID = sharedset.sharedset_id.
if sharedsettype = {&SHAREDSETTYPE-DIVISION} then
assign viDivisionSharedSetID = sharedset.sharedset_id.
if sharedsettype = {&SHAREDSETTYPE-COSTCENTRE} then
assign viCostCenterSharedSetID = sharedset.sharedset_id.
if sharedsettype = {&SHAREDSETTYPE-PROJECT} then
assign viProjectSharedSetID = sharedset.sharedset_id.
if sharedsettype = {&SHAREDSETTYPE-JOURNAL} then
assign viJournalSharedSetID = sharedset.sharedset_id.
if sharedsettype = {&SHAREDSETTYPE-DEBTOR} then
assign viDebtorSharedSetID = sharedset.sharedset_id.
end. /* end each companysharedset */
/* ====================================================== */
/* 1. Do some checking on DInvoice */
/* ====================================================== */
for each DInvoice OF company NO-LOCK WHERE
DInvoice.DinvoicePostingYearPeriod = viYearPeriod :
empty temp-table ttBalanceByGL.
/* AP02 */
if DInvoice.Period_ID <> viPeriodID
then do:
PUT STREAM sTechOut unformatted vcPrefix + "DInvoice;" + string (DInvoice.DInvoice_ID) + "Mismatch in Period." skip.
assign oiErrorNumbers = oiErrorNumbers + 1.
end.
if DInvoice.DInvoicePostingYear <> viPeriodYear or
DInvoice.DInvoicePostingPeriod <> viPeriodPeriod or
DInvoice.DInvoicePostingYearPeriod <> viYearPeriod
then do:
PUT STREAM sTechOut unformatted vcPrefix + "DInvoice;" + string (DInvoice.DInvoice_ID) + ";Mismatch in Period between DInvoice and Period table" skip.
assign oiErrorNumbers = oiErrorNumbers + 1.
end.
if DInvoice.DInvoicePostingDate < vtPeriodStartDate or
DInvoice.DInvoicePostingDate > vtPeriodEndDate
then do:
PUT STREAM sTechOut unformatted vcPrefix + "DInvoice;" + string (DInvoice.DInvoice_ID) + ";Mismatch Posting date and Period (Start/End)" skip.
assign oiErrorNumbers = oiErrorNumbers + 1.
end.
if DInvoice.debtor_ID = 0 or DInvoice.debtor_ID = ?
then do:
PUT STREAM sTechOut unformatted vcPrefix + "DInvoice;" + string (DInvoice.DInvoice_ID) + ";debtor_id is 0 or ?" skip.
assign oiErrorNumbers = oiErrorNumbers + 1.
end.
/* Check debtor */
find first debtor where
debtor.debtor_id = DInvoice.debtor_ID no-lock no-error.
if not available debtor or debtor.sharedset_id <> viDebtorSharedSetID
then do:
PUT STREAM sTechOut unformatted vcPrefix + "DInvoice;" + string (DInvoice.DInvoice_ID) + ";debtor was not found" skip.
assign oiErrorNumbers = oiErrorNumbers + 1.
end.
/* Check Journal */
find first Journal where
Journal.Journal_id = DInvoice.Journal_ID no-lock no-error.
if not available Journal or journal.sharedset_id <> viJournalSharedSetID
then do:
PUT STREAM sTechOut unformatted vcPrefix + "DInvoice;" + string (DInvoice.DInvoice_ID) + ";Journal was not found" skip.
assign oiErrorNumbers = oiErrorNumbers + 1.
end.
/* Check Division */
if DInvoice.Division_ID <> 0 and DInvoice.Division_ID <> ?
then do :
find first Division where
Division.Division_id = DInvoice.Division_ID no-lock no-error.
if not available Division or division.sharedset_id <> viDivisionSharedSetID
then do:
PUT STREAM sTechOut unformatted vcPrefix + "DInvoice;" + string (DInvoice.DInvoice_ID) + ";Division was not found" skip.
assign oiErrorNumbers = oiErrorNumbers + 1.
end.
end.
/* Check Control GL */
if Dinvoice.ControlGL_ID = 0 or Dinvoice.ControlGL_ID = ?
then do:
PUT STREAM sTechOut unformatted vcPrefix + "Dinvoice;" + string (Dinvoice.Dinvoice_ID) + ";Control Account not assigned." skip.
assign oiErrorNumbers = oiErrorNumbers + 1.
end.
assign vdOrgDebitTC = 0
vdOrgCreditTC = 0
vdBalDebitTC = 0
vdBalCreditTC = 0
vdBalTC = 0
vdOrgDebitLC = 0
vdOrgCreditLC = 0
vdBalDebitLC = 0
vdBalCreditLC = 0
vdBalLC = 0
vdOrgDebitCC = 0
vdOrgCreditCC = 0
vdBalDebitCC = 0
vdBalCreditCC = 0
vdBalCC = 0
vlFirst = yes
vlIsHasMovementInitial = no.
IF DInvoice.DInvoiceIsopen
THEN ASSIGN vtClosingDate = DATE (12,31,9999).
ELSE ASSIGN vtClosingDate = DATE (01,01,0001).
for each DInvoicemovement of DInvoice no-lock :
find postingline of DInvoicemovement no-lock no-error.
IF NOT AVAILABLE PostingLine
then do :
PUT STREAM sTechOut unformatted vcPrefix + "DInvoicemovement;" + string (DInvoicemovement.DInvoicemovement_ID) + ";No PostingLine found" skip.
assign oiErrorNumbers = oiErrorNumbers + 1.
NEXT.
END.
FIND posting OF PostingLine NO-LOCK no-error.
if DInvoicemovement.DInvoicemovementtype = "INITIAL" then
assign vlIsHasMovementInitial = yes.
/* Check GL */
find first GL where
GL.GL_id = PostingLine.GL_ID no-lock no-error.
if not available GL or gl.sharedset_id <> viGLShareSetID
then do:
PUT STREAM sTechOut unformatted vcPrefix + "DInvoicemovement;" + string (DInvoicemovement.DInvoicemovement_ID) + ";No or wrong GL is used" skip.
assign oiErrorNumbers = oiErrorNumbers + 1.
end.
else
if gl.glTypeCode <> {&GLTYPECODE-DEBT}
then do:
PUT STREAM sTechOut unformatted vcPrefix + "DInvoicemovement;" + string (DInvoicemovement.DInvoicemovement_ID) + ";GL has is not a DEBTORCONTROL account." skip.
assign oiErrorNumbers = oiErrorNumbers + 1.
end.
find ttBalanceByGL where
ttBalanceByGL.tiControlGL_ID = PostingLine.gl_id
no-error.
if not available ttBalanceByGL
then do:
create ttBalanceByGL.
assign ttBalanceByGL.tiControlGL_ID = PostingLine.gl_id.
end.
assign ttBalanceByGL.tdInvoicebalancelc = ttBalanceByGL.tdInvoicebalancelc + postingline.postinglineDebitLC - postingline.postinglineCreditLC.
if DInvoicemovement.DInvoicemovementtype = "INITIAL"
then assign vdOrgDebitTC = vdOrgDebitTC + postingline.postinglineDebitTC
vdOrgDebitLC = vdOrgDebitLC + postingline.postinglineDebitLC
vdOrgDebitCC = vdOrgDebitCC + postingline.postinglineDebitCC
vdOrgCreditTC = vdOrgCreditTC + postingline.postinglineCreditTC
vdOrgCreditLC = vdOrgCreditLC + postingline.postinglineCreditLC
vdOrgCreditCC = vdOrgCreditCC + postingline.postinglineCreditCC.
assign vdBalDebitTC = vdBalDebitTC + postingline.postinglineDebitTC
vdBalDebitLC = vdBalDebitLC + postingline.postinglineDebitLC
vdBalDebitCC = vdBalDebitCC + postingline.postinglineDebitCC
vdBalCreditTC = vdBalCreditTC + postingline.postinglineCreditTC
vdBalCreditLC = vdBalCreditLC + postingline.postinglineCreditLC
vdBalCreditCC = vdBalCreditCC + postingline.postinglineCreditCC.
IF Posting.PostingDate > vtClosingDate
THEN ASSIGN vtClosingDate = Posting.PostingDate.
end. /* for each DInvoicemovement */
if not vlIsHasMovementInitial
then do:
PUT STREAM sTechOut unformatted vcPrefix + "DInvoice;" + string (DInvoice.DInvoice_ID) + ";DInvoice with no Initial DInvoiceMovement found" skip.
assign oiErrorNumbers = oiErrorNumbers + 1.
end.
assign vdBalTC = vdBalDebitTC - vdBalCreditTC
vdBalLC = vdBalDebitLC - vdBalCreditLC
vdBalCC = vdBalDebitCC - vdBalCreditCC.
if vdBallc <> 0
then do :
find ttBalanceByGL where
ttBalanceByGL.tdInvoicebalancelc > 0
no-error.
if ambiguous ttBalanceByGL
then do:
PUT STREAM sTechOut unformatted vcPrefix + "Dinvoice;" + string (Dinvoice.Dinvoice_ID) + ";There is a balance on more than 1 control account" skip.
assign oiErrorNumbers = oiErrorNumbers + 1.
end.
else
if available ttBalanceByGL and ttBalanceByGL.tiControlGL_ID <> Dinvoice.ControlGL_ID and Dinvoice.ControlGL_ID <> 0 and Dinvoice.ControlGL_ID <> ?
then do:
PUT STREAM sTechOut unformatted vcPrefix + "Dinvoice;" + string (Dinvoice.Dinvoice_ID) + ";DinvoiceControl Account is not correct" skip.
assign oiErrorNumbers = oiErrorNumbers + 1.
end.
end.
if vdBalDebitTC > vdBalCreditTC
then assign vdBalDebitTC = (vdBalDebitTC - vdBalCreditTC)
vdBalCreditTC = 0.
else assign vdBalCreditTC = vdBalCreditTC - vdBalDebitTC
vdBalDebitTC = 0.
if vdBalDebitLC > vdBalCreditLC
then assign vdBalDebitLC = (vdBalDebitLC - vdBalCreditLC)
vdBalCreditLC = 0.
else assign vdBalCreditLC = - (vdBalDebitLC - vdBalCreditLC)
vdBalDebitLC = 0.
if vdBalDebitCC > vdBalCreditCC
then assign vdBalDebitCC = (vdBalDebitCC - vdBalCreditCC)
vdBalCreditCC = 0.
else assign vdBalCreditCC = - (vdBalDebitCC - vdBalCreditCC)
vdBalDebitCC = 0.
/* Check the Original debit/credit Amounts in TC LC CC */
if vdOrgDebitTC <> DInvoice.DInvoiceOriginalDebitTC OR
vdOrgCreditTC <> DInvoice.DInvoiceOriginalCreditTC
then do:
PUT STREAM sTechOut unformatted vcPrefix + "Dinvoice/PostingLine;" + string (Dinvoice.Dinvoice_ID) + ";Inconsistency in TC Original amounts; "
+ string (Dinvoice.LastModifiedDate) + " " + Dinvoice.LastModifiedUser skip.
assign oiErrorNumbers = oiErrorNumbers + 1.
end.
if vdOrgDebitLC <> DInvoice.DInvoiceOriginalDebitLC OR
vdOrgCreditLC <> DInvoice.DInvoiceOriginalCreditLC
then do:
PUT STREAM sTechOut unformatted vcPrefix + "Dinvoice/PostingLine;" + string (Dinvoice.Dinvoice_ID) + ";Inconsistency in LC Original amounts; "
+ string (Dinvoice.LastModifiedDate) + " " + Dinvoice.LastModifiedUser skip.
assign oiErrorNumbers = oiErrorNumbers + 1.
end.
if vdOrgDebitCC <> DInvoice.DInvoiceOriginalDebitCC OR
vdOrgCreditCC <> DInvoice.DInvoiceOriginalCreditCC
then do:
PUT STREAM sTechOut unformatted vcPrefix + "Dinvoice/PostingLine;" + string (Dinvoice.Dinvoice_ID) + ";Inconsistency in CC Original amounts; "
+ string (Dinvoice.LastModifiedDate) + " " + Dinvoice.LastModifiedUser skip.
assign oiErrorNumbers = oiErrorNumbers + 1.
end.
/* Check the Balance debit/credit Amounts in TC LC CC */
if vdBalDebitTC - vdBalCreditTC <> Dinvoice.DinvoiceBalanceDebitTC - Dinvoice.DinvoiceBalanceCreditTC
then do:
PUT STREAM sTechOut unformatted vcPrefix + "Dinvoice/PostingLine;" + string (Dinvoice.Dinvoice_ID) + ";Inconsistency in TC Debit/Credit Balance amounts; "
+ string (Dinvoice.LastModifiedDate) + " " + Dinvoice.LastModifiedUser skip.
assign oiErrorNumbers = oiErrorNumbers + 1.
end.
if vdBalDebitLC - vdBalCreditLC <> Dinvoice.DinvoiceBalanceDebitLC - Dinvoice.DinvoiceBalanceCreditLC
then do:
PUT STREAM sTechOut unformatted vcPrefix + "Dinvoice/PostingLine;" + string (Dinvoice.Dinvoice_ID) + ";Inconsistency in LC Debit/Credit Balance amounts; "
+ string (Dinvoice.LastModifiedDate) + " " + Dinvoice.LastModifiedUser skip.
assign oiErrorNumbers = oiErrorNumbers + 1.
end.
if vdBalDebitCC - vdBalCreditCC <> Dinvoice.DinvoiceBalanceDebitCC - Dinvoice.DinvoiceBalanceCreditCC
then do:
PUT STREAM sTechOut unformatted vcPrefix + "Dinvoice/PostingLine;" + string (Dinvoice.Dinvoice_ID) + ";Inconsistency in CC Debit/Credit Balance amounts; "
+ string (Dinvoice.LastModifiedDate) + " " + Dinvoice.LastModifiedUser skip.
assign oiErrorNumbers = oiErrorNumbers + 1.
end.
/* Check the Balance Amounts in TC LC CC */
if vdBalTC <> DInvoice.DInvoiceBalanceTC
then do:
PUT STREAM sTechOut unformatted vcPrefix + "Dinvoice/PostingLine;" + string (Dinvoice.Dinvoice_ID) + ";Inconsistency in TC Balance amounts; "
+ string (Dinvoice.LastModifiedDate) + " " + Dinvoice.LastModifiedUser skip.
assign oiErrorNumbers = oiErrorNumbers + 1.
end.
if vdBalLC <> DInvoice.DInvoiceBalanceLC
then do:
PUT STREAM sTechOut unformatted vcPrefix + "Dinvoice/PostingLine;" + string (Dinvoice.Dinvoice_ID) + ";Inconsistency in LC Balance amounts; "
+ string (Dinvoice.LastModifiedDate) + " " + Dinvoice.LastModifiedUser skip.
assign oiErrorNumbers = oiErrorNumbers + 1.
end.
if vdBalCC <> DInvoice.DInvoiceBalanceCC
then do:
PUT STREAM sTechOut unformatted vcPrefix + "Dinvoice/PostingLine;" + string (Dinvoice.Dinvoice_ID) + ";Inconsistency in CC Balance amounts; "
+ string (Dinvoice.LastModifiedDate) + " " + Dinvoice.LastModifiedUser skip.
assign oiErrorNumbers = oiErrorNumbers + 1.
end.
if ((vdBalTC = 0 and vdBalLC = 0 and vdBalCC = 0) and DInvoice.DInvoiceIsopen) or
((vdBalTC <> 0 or vdBalLC <> 0 or vdBalCC <> 0) and not DInvoice.DInvoiceIsopen)
then do:
PUT STREAM sTechOut unformatted vcPrefix + "Dinvoice;" + string (Dinvoice.Dinvoice_ID) + ";Mismatch in status and balance fields" skip.
assign oiErrorNumbers = oiErrorNumbers + 1.
end.
if DInvoice.DInvoiceClosingDate <> vtClosingDate
then do:
PUT STREAM sTechOut unformatted vcPrefix + "Dinvoice;" + string (Dinvoice.Dinvoice_ID) + ";DInvoice.DInvoiceClosingDate is not correct" skip.
assign oiErrorNumbers = oiErrorNumbers + 1.
end.
end. /* for each DInvoice no-lock */
/* ====================================================== */
/* 2. Do some checking on DInvoiceMovement */
/* ====================================================== */
FOR EACH Period OF Company no-lock WHERE
Period.PeriodYearPeriod = viYearPeriod,
EACH DInvoiceMovement OF company no-lock WHERE
DInvoiceMovement.Period_id = Period.Period_ID :
find Dinvoice of DinvoiceMovement no-lock no-error.
if not available DInvoice
then do:
PUT STREAM sTechOut unformatted vcPrefix + "DInvoiceMovement;" + string (DInvoiceMovement.DInvoiceMovement_ID) + ";DinvoiceMovement found with no link to DINVOICE" skip.
assign oiErrorNumbers = oiErrorNumbers + 1.
end.
else do :
if DInvoice.Debtor_id <> DInvoiceMovement.Debtor_ID
then do:
PUT STREAM sTechOut unformatted vcPrefix + "DInvoiceMovement;" + string (DInvoiceMovement.DInvoiceMovement_ID) + ";Mismatch in Debtor_ID between DInvoice and DInvoiceMovement" skip.
assign oiErrorNumbers = oiErrorNumbers + 1.
end.
if DInvoice.Company_id <> DInvoiceMovement.Company_ID
then do:
PUT STREAM sTechOut unformatted vcPrefix + "DInvoiceMovement;" + string (DInvoiceMovement.DInvoiceMovement_ID) + ";Mismatch in Company_ID between DInvoice and DInvoiceMovement" skip.
assign oiErrorNumbers = oiErrorNumbers + 1.
end.
if DInvoicemovement.DInvoicemovementtype = "INITIAL" and
DInvoice.Period_ID <> DinvoiceMovement.Period_ID
then do:
PUT STREAM sTechOut unformatted vcPrefix + "DInvoiceMovement;" + string (DInvoiceMovement.DInvoiceMovement_ID) + ";Mismatch in Period_ID between DInvoice and DInvoiceMovement" skip.
assign oiErrorNumbers = oiErrorNumbers + 1.
end.
end.
if Period.PeriodyearPeriod <> DInvoiceMovement.DInvoiceMovementYearPeriod
then do:
PUT STREAM sTechOut unformatted vcPrefix + "DInvoiceMovement;" + string (DInvoiceMovement.DInvoiceMovement_ID) + ";Mismatch in DInvoiceMovementYearPeriod between Period and DInvoiceMovement" skip.
assign oiErrorNumbers = oiErrorNumbers + 1.
end.
end. /* for each DInvoiceMovement */
/* ====================================================== */
/* 3. check Posting line on control account */
/* ====================================================== */
if vcPeriodType <> {&PERIODTYPECODE-YEARCLOSING}
then do:
for each GL fields (GL_ID GLCode GlTypeCode) where
GL.SharedSet_ID = viGLShareSetID and
Gl.GlTypeCode = {&GLTYPECODE-DEBT}
no-lock:
assign vdNetAmountLC = 0
vdNetAmountCC = 0
vdNetAmountTC = 0
vlFound = no.
/* Do some checking on DInvoiceMovement */
FOR each PostingLine of Company where
PostingLine.GL_ID = GL.GL_ID and
PostingLine.PostingYearPeriod = viYearPeriod
no-lock :
find dinvoiceMovement of PostingLine no-lock no-error.
if not available dinvoiceMovement
then do:
find Posting of PostingLine no-lock no-error.
find journal of posting no-lock no-error.
PUT STREAM sTechOut unformatted vcPrefix + "POSTINGLine;" + string (PostingLine.PostingLine_ID) + ";Posting on Debtor Control Account without DInvoiceMovement;"
+ string (postingLine.LastModifiedDate) + ' ' + postingLine.LastModifiedUser skip.
assign vlFound = yes
vdNetAmountTC = vdNetAmountTC + PostingLine.PostingLineDebitTC - PostingLine.PostingLineCreditTC
vdNetAmountLC = vdNetAmountLC + PostingLine.PostingLineDebitLC - PostingLine.PostingLineCreditLC
vdNetAmountCC = vdNetAmountCC + PostingLine.PostingLineDebitCC - PostingLine.PostingLineCreditCC.
end. /* if not available dinvoiceMovement */
end. /* FOR each PostingLine */
if vlFound
then do :
if vdNetAmountTC <> 0 or vdNetAmountLC <> 0 or vdNetAmountCC <> 0
then assign oiErrorNumbers = oiErrorNumbers + 1.
PUT STREAM sTechOut unformatted vcPrefix
"GL;"
string (GL.GL_ID) ";"
"Net Amount of Postings on Debtor Control Account without DInvoiceMovement TC/BC/SC;"
vdNetAmountTC ';'
vdNetAmountLC ';'
vdNetAmountCC
skip.
end. /* if vlFound */
end. /* for each GL */
end. /* vcPeriodType <> {&PERIODTYPECODE-YEARCLOSING} */
END. /* for each Company */
PUT STREAM sTechOut unformatted vcPrefix2 + "END Validation of AR" skip.