project QadFinancials > class TConCheck > method TechnicalValidation03CInvoice
Description
Submethod of TechnicalValidation for CInvoice
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_AP_Check */
/**********************************************************************************/
PUT STREAM sTechOut unformatted " " skip.
PUT STREAM sTechOut unformatted vcPrefix2 + "Start Validation of AP (" + STRING (itFromDate) + ")" skip.
assign viPrevCompanyID = -1.
repeat:
find first CInvoice where CInvoicePostingYearPeriod = iiYearPeriod and
CInvoice.Company_ID > viPrevCompanyID
use-index CyPeriodYearPeriod
no-lock no-error.
if not available CInvoice
then leave.
else do:
assign viPrevCompanyID = CInvoice.Company_ID.
if not can-find (Company of CInvoice)
then do:
for each CInvoice where
CInvoice.Company_ID = viPrevCompanyID and
CInvoicePostingYearPeriod = iiYearPeriod
no-lock:
PUT STREAM sTechOut unformatted vcPrefix + "CInvoice;" + string (CInvoice.CInvoice_ID) + ";CInvoice found with no valid company" skip.
assign oiErrorNumbers = oiErrorNumbers + 1.
end. /* for each CInvoice */
end. /* if */
end. /* else do */
end. /* repeat */
assign viPrevCompanyID = -1.
repeat:
find first CInvoiceMovement where CInvoiceMovement.cinvoicemovementyearperiod = iiYearPeriod and
CInvoiceMovement.Company_ID > viPrevCompanyID
no-lock no-error.
if not available CInvoiceMovement
then leave.
else do:
assign viPrevCompanyID = CInvoiceMovement.Company_ID.
if not can-find (Company of CInvoiceMovement)
then do:
for each CInvoiceMovement where
CInvoiceMovement.Company_ID = viPrevCompanyID and
CInvoiceMovement.cinvoicemovementyearperiod = iiYearPeriod
no-lock:
PUT STREAM sTechOut unformatted vcPrefix + "CInvoiceMovement;" + string (CInvoiceMovement.CInvoiceMovement_ID) + ";CInvoiceMovement found with no valid company" skip.
assign oiErrorNumbers = oiErrorNumbers + 1.
end. /* for each CInvoiceMovement */
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-CREDITOR} then
assign viCreditorSharedSetID = sharedset.sharedset_id.
end. /* end each companysharedset */
/* ==================================================================================== */
/* 1. Do some checking on CInvoice */
/* ==================================================================================== */
for each CInvoice OF company no-lock WHERE
CInvoice.CinvoicePostingYearPeriod = viYearPeriod
AND CInvoice.CInvoiceIsInitialStatus = no :
empty temp-table ttBalanceByGL.
/* AP02 */
if CInvoice.Period_ID <> viPeriodID
then do:
PUT STREAM sTechOut unformatted vcPrefix + "CInvoice;" + string (CInvoice.CInvoice_ID) + "Mismatch in Period." skip.
assign oiErrorNumbers = oiErrorNumbers + 1.
end.
if CInvoice.CInvoicePostingYear <> viPeriodYear or
CInvoice.CInvoicePostingPeriod <> viPeriodPeriod or
CInvoice.CInvoicePostingYearPeriod <> viYearPeriod
then do:
PUT STREAM sTechOut unformatted vcPrefix + "CInvoice;" + string (CInvoice.CInvoice_ID) + ";Mismatch in Period between Cinvoice and Period table" skip.
assign oiErrorNumbers = oiErrorNumbers + 1.
end.
if CInvoice.CInvoicePostingDate < vtPeriodStartDate or
CInvoice.CInvoicePostingDate > vtPeriodEndDate
then do:
PUT STREAM sTechOut unformatted vcPrefix + "CInvoice;" + string (CInvoice.CInvoice_ID) + ";Mismatch Posting date and Period (Start/End)" skip.
assign oiErrorNumbers = oiErrorNumbers + 1.
end.
if CInvoice.creditor_ID = 0 or CInvoice.creditor_ID = ?
then do:
PUT STREAM sTechOut unformatted vcPrefix + "CInvoice;" + string (CInvoice.CInvoice_ID) + ";creditor_id is 0 or ?" skip.
assign oiErrorNumbers = oiErrorNumbers + 1.
end.
/* Check Creditor */
find first creditor where
Creditor.Creditor_id = CInvoice.Creditor_ID no-lock no-error.
if not available Creditor or creditor.sharedset_id <> viCreditorSharedSetID
then do:
PUT STREAM sTechOut unformatted vcPrefix + "CInvoice;" + string (CInvoice.CInvoice_ID) + ";Creditor was not found" skip.
assign oiErrorNumbers = oiErrorNumbers + 1.
end.
/* Check Journal */
find first Journal where
Journal.Journal_id = CInvoice.Journal_ID no-lock no-error.
if not available Journal or journal.sharedset_id <> viJournalSharedSetID
then do:
PUT STREAM sTechOut unformatted vcPrefix + "CInvoice;" + string (CInvoice.CInvoice_ID) + ";Journal was not found" skip.
assign oiErrorNumbers = oiErrorNumbers + 1.
end.
/* Check Division */
if CInvoice.Division_ID <> 0 and CInvoice.Division_ID <> ?
then do :
find first Division where
Division.Division_id = CInvoice.Division_ID no-lock no-error.
if not available Division or division.sharedset_id <> viDivisionSharedSetID
then do:
PUT STREAM sTechOut unformatted vcPrefix + "CInvoice;" + string (CInvoice.CInvoice_ID) + ";Division was not found" skip.
assign oiErrorNumbers = oiErrorNumbers + 1.
end.
end.
/* Check Control GL */
if CInvoice.ControlGL_ID = 0 or CInvoice.ControlGL_ID = ?
then do:
PUT STREAM sTechOut unformatted vcPrefix + "CInvoice;" + string (CInvoice.CInvoice_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 CInvoice.CInvoiceIsopen
THEN ASSIGN vtClosingDate = DATE (12,31,9999).
ELSE ASSIGN vtClosingDate = DATE (01,01,0001).
for each CInvoicemovement of CInvoice no-lock :
find postingline of CInvoicemovement no-lock no-error.
IF NOT AVAILABLE PostingLine
then do :
PUT STREAM sTechOut unformatted vcPrefix + "CInvoicemovement;" + string (CInvoicemovement.CInvoicemovement_ID) + ";No PostingLine found" skip.
assign oiErrorNumbers = oiErrorNumbers + 1.
NEXT.
END.
FIND posting OF PostingLine NO-LOCK no-error.
if not available Posting
then do:
PUT STREAM sTechOut unformatted vcPrefix + "CInvoiceMovement;PostingLine;" + string (CInvoiceMovement.CInvoiceMovement_ID) + string(CInvoiceMovement.PostingLine_ID) + "; No Posting found" skip.
assign oiErrorNumbers = oiErrorNumbers + 1.
next.
end.
if CInvoicemovement.CInvoicemovementtype = "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 + "PostingLine;" + string (PostingLine.PostingLine_ID) + ";GL was not found" skip.
assign oiErrorNumbers = oiErrorNumbers + 1.
end.
else
if gl.glTypecode <> {&GLTYPECODE-CRED}
then do:
PUT STREAM sTechOut unformatted vcPrefix + "CInvoicemovement;" + string (CInvoicemovement.CInvoicemovement_ID) + ";GL has is not a CreditorCONTROL 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 CInvoicemovement.CInvoicemovementtype = "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 CInvoicemovement */
if not vlIsHasMovementInitial
then do:
PUT STREAM sTechOut unformatted vcPrefix + "CInvoice;" + string (CInvoice.CInvoice_ID) + ";CInvoice with no Initial CInvoiceMovement 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 + "CInvoice;" + string (CInvoice.CInvoice_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 <> CInvoice.ControlGL_ID and CInvoice.ControlGL_ID <> 0 and CInvoice.ControlGL_ID <> ?
then do:
PUT STREAM sTechOut unformatted vcPrefix + "CInvoice;" + string (CInvoice.CInvoice_ID) + ";CInvoiceControl 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 <> Cinvoice.CinvoiceOriginalDebitTC OR
vdOrgCreditTC <> Cinvoice.CinvoiceOriginalCreditTC
then do:
PUT STREAM sTechOut unformatted vcPrefix + "Cinvoice/PostingLine;" + string (Cinvoice.Cinvoice_ID) + ";Inconsistency in TC Original amounts; "
+ string (Cinvoice.LastModifiedDate) + " " + Cinvoice.LastModifiedUser skip.
assign oiErrorNumbers = oiErrorNumbers + 1.
end.
if vdOrgDebitLC <> Cinvoice.CinvoiceOriginalDebitLC OR
vdOrgCreditLC <> Cinvoice.CinvoiceOriginalCreditLC
then do:
PUT STREAM sTechOut unformatted vcPrefix + "Cinvoice/PostingLine;" + string (Cinvoice.Cinvoice_ID) + ";Inconsistency in LC Original amounts; "
+ string (Cinvoice.LastModifiedDate) + " " + Cinvoice.LastModifiedUser skip.
assign oiErrorNumbers = oiErrorNumbers + 1.
end.
if vdOrgDebitCC <> Cinvoice.CinvoiceOriginalDebitCC OR
vdOrgCreditCC <> Cinvoice.CinvoiceOriginalCreditCC
then do:
PUT STREAM sTechOut unformatted vcPrefix + "Cinvoice/PostingLine;" + string (Cinvoice.Cinvoice_ID) + ";Inconsistency in CC Original amounts; "
+ string (Cinvoice.LastModifiedDate) + " " + Cinvoice.LastModifiedUser skip.
assign oiErrorNumbers = oiErrorNumbers + 1.
end.
/* Check the Balance debit/credit Amounts in TC LC CC */
if vdBalDebitTC - vdBalCreditTC <> Cinvoice.CinvoiceBalanceDebitTC - Cinvoice.CinvoiceBalanceCreditTC
then do:
PUT STREAM sTechOut unformatted vcPrefix + "Cinvoice/PostingLine;" + string (Cinvoice.Cinvoice_ID) + ";Inconsistency in TC Debit/Credit Balance amounts; "
+ string (Cinvoice.LastModifiedDate) + " " + Cinvoice.LastModifiedUser skip.
assign oiErrorNumbers = oiErrorNumbers + 1.
end.
if vdBalDebitLC - vdBalCreditLC <> Cinvoice.CinvoiceBalanceDebitLC - Cinvoice.CinvoiceBalanceCreditLC
then do:
PUT STREAM sTechOut unformatted vcPrefix + "Cinvoice/PostingLine;" + string (Cinvoice.Cinvoice_ID) + ";Inconsistency in LC Debit/Credit Balance amounts; "
+ string (Cinvoice.LastModifiedDate) + " " + Cinvoice.LastModifiedUser skip.
assign oiErrorNumbers = oiErrorNumbers + 1.
end.
if vdBalDebitCC - vdBalCreditCC <> Cinvoice.CinvoiceBalanceDebitCC - Cinvoice.CinvoiceBalanceCreditCC
then do:
PUT STREAM sTechOut unformatted vcPrefix + "Cinvoice/PostingLine;" + string (Cinvoice.Cinvoice_ID) + ";Inconsistency in CC Debit/Credit Balance amounts; "
+ string (Cinvoice.LastModifiedDate) + " " + Cinvoice.LastModifiedUser skip.
assign oiErrorNumbers = oiErrorNumbers + 1.
end.
/* Check the Balance Amounts in TC LC CC */
if vdBalTC <> Cinvoice.CinvoiceBalanceTC
then do:
PUT STREAM sTechOut unformatted vcPrefix + "Cinvoice/PostingLine;" + string (Cinvoice.Cinvoice_ID) + ";Inconsistency in TC Balance amounts; "
+ string (Cinvoice.LastModifiedDate) + " " + Cinvoice.LastModifiedUser skip.
assign oiErrorNumbers = oiErrorNumbers + 1.
end.
if vdBalLC <> Cinvoice.CinvoiceBalanceLC
then do:
PUT STREAM sTechOut unformatted vcPrefix + "Cinvoice/PostingLine;" + string (Cinvoice.Cinvoice_ID) + ";Inconsistency in LC Balance amounts; "
+ string (Cinvoice.LastModifiedDate) + " " + Cinvoice.LastModifiedUser skip.
assign oiErrorNumbers = oiErrorNumbers + 1.
end.
if vdBalCC <> Cinvoice.CinvoiceBalanceCC
then do:
PUT STREAM sTechOut unformatted vcPrefix + "Cinvoice/PostingLine;" + string (Cinvoice.Cinvoice_ID) + ";Inconsistency in CC Balance amounts; "
+ string (Cinvoice.LastModifiedDate) + " " + Cinvoice.LastModifiedUser skip.
assign oiErrorNumbers = oiErrorNumbers + 1.
end.
if ((vdBalTC = 0 and vdBalLC = 0 and vdBalCC = 0) and CInvoice.CInvoiceIsopen) or
((vdBalTC <> 0 or vdBalLC <> 0 or vdBalCC <> 0) and not CInvoice.CInvoiceIsopen)
then do:
PUT STREAM sTechOut unformatted vcPrefix + "CInvoice;" + string (CInvoice.CInvoice_ID) + ";Mismatch in status and balance fields" skip.
assign oiErrorNumbers = oiErrorNumbers + 1.
end.
if CInvoice.CInvoiceClosingDate <> vtClosingDate
then do:
PUT STREAM sTechOut unformatted vcPrefix + "CInvoice;" + string (CInvoice.CInvoice_ID) + ";CInvoice.CInvoiceClosingDate is not correct" skip.
assign oiErrorNumbers = oiErrorNumbers + 1.
end.
end. /* for each CInvoice no-lock */
/* ======================================== */
/* 2. Do some checking on CInvoiceMovement */
/* ======================================== */
FOR EACH Period OF Company no-lock WHERE
Period.PeriodYearPeriod = viYearPeriod,
EACH CInvoiceMovement OF company no-lock WHERE
CInvoiceMovement.Period_id = Period.Period_ID :
find CInvoice of CInvoiceMovement no-lock no-error.
if not available CInvoice
then do:
PUT STREAM sTechOut unformatted vcPrefix + "CInvoiceMovement;" + string (CInvoiceMovement.CInvoiceMovement_ID) + ";CInvoiceMovement found with no link to CInvoice" skip.
assign oiErrorNumbers = oiErrorNumbers + 1.
end.
else do :
if CInvoice.Creditor_id <> CInvoiceMovement.Creditor_ID
then do:
PUT STREAM sTechOut unformatted vcPrefix + "CInvoiceMovement;" + string (CInvoiceMovement.CInvoiceMovement_ID) + ";Mismatch in Creditor_ID between CInvoice and CInvoiceMovement" skip.
assign oiErrorNumbers = oiErrorNumbers + 1.
end.
if CInvoice.Company_id <> CInvoiceMovement.Company_ID
then do:
PUT STREAM sTechOut unformatted vcPrefix + "CInvoiceMovement;" + string (CInvoiceMovement.CInvoiceMovement_ID) + ";Mismatch in Company_ID between CInvoice and CInvoiceMovement" skip.
assign oiErrorNumbers = oiErrorNumbers + 1.
end.
if CInvoicemovement.CInvoicemovementtype = "INITIAL" and
CInvoice.Period_ID <> CInvoiceMovement.Period_ID
then do:
PUT STREAM sTechOut unformatted vcPrefix + "CInvoiceMovement;" + string (CInvoiceMovement.CInvoiceMovement_ID) + ";Mismatch in Period_ID between CInvoice and CInvoiceMovement" skip.
assign oiErrorNumbers = oiErrorNumbers + 1.
end.
end.
if Period.PeriodyearPeriod <> CInvoiceMovement.CInvoiceMovementYearPeriod
then do:
PUT STREAM sTechOut unformatted vcPrefix + "CInvoiceMovement;" + string (CInvoiceMovement.CInvoiceMovement_ID) + ";Mismatch in CInvoiceMovementYearPeriod between Period and CInvoiceMovement" skip.
assign oiErrorNumbers = oiErrorNumbers + 1.
end.
end. /* for each CInvoiceMovement */
/* ========================================= */
/* 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-CRED} no-lock:
assign vdNetAmountLC = 0
vdNetAmountCC = 0
vdNetAmountTC = 0
vlFound = no.
/* Do some checking on CInvoiceMovement */
for each PostingLine of Company where
PostingLine.GL_ID = GL.GL_ID and
PostingLine.PostingYearPeriod = viYearPeriod
no-lock :
find CinvoiceMovement of PostingLine no-lock no-error.
if not available CinvoiceMovement
then do:
PUT STREAM sTechOut unformatted vcPrefix + "POSTINGLine;" + string (PostingLine.PostingLine_ID) + ";Posting on Creditor Control Account without CInvoiceMovement; " +
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 Supplier Control Account without CInvoiceMovement TC/BC/SC;"
vdNetAmountTC ';'
vdNetAmountLC ';'
vdNetAmountCC
skip.
end. /* if vlFound */
end. /* for each GL */
end. /* if vcPeriodType <> {&PERIODTYPECODE-YEARCLOSING} */
END. /* for each company */
PUT STREAM sTechOut unformatted vcPrefix2 + "END Validation of AP" skip.