project QadFinancials > class TConCheck > method DPaymentControlGLBalance
Description
AR payments with GLs of type Customer Payment Account
Parameters
| icFile | input | character | |
| ocConCheckLineDetResult | output | character | |
| otConCheckLineDetStartDate | output | date | |
| oiConCheckLineDetStartTime | output | integer | |
| oiConCheckLineDetDuration | output | integer | |
| iiCompanyID | input | integer | |
| iiPeriodID | input | integer | |
| iiSessionID | input | integer | |
| oiErrorNumbers | output | integer | |
| ocConCheckLineDetVersion | output | character | |
| icAppVersion | input | character | |
| icSumFile | input | character | Summary file |
| ihTranslationHandle | input | handle | |
| oiReturnStatus | output | integer | Return status of the method. |
Internal usage
QadFinancials
program code (program1/tconcheck.p)
/* ================================================================================= */
/* This is the method for AR payments with GLs of type Customer Payment Account */
/* ================================================================================= */
assign oiReturnStatus = -98.
assign vdStartEtime = etime(no)
otConCheckLineDetStartDate = now
oiConCheckLineDetStartTime = time
oiErrorNumbers = 0
ocConCheckLineDetVersion = icAppVersion + '.1'.
/* open logfile stream */
OUTPUT STREAM sTechOut TO value(icFile) APPEND.
OUTPUT STREAM sSumOut TO value(icSumFile) APPEND.
PUT STREAM sTechOut unformatted ' ' skip.
PUT STREAM sTechOut unformatted '##### AR payments with GL #####' skip.
PUT STREAM sTechOut unformatted 'Version: ' ocConCheckLineDetVersion skip.
PUT STREAM sTechOut unformatted 'Validation Start At: ' string(now, '99/99/9999 HH:MM:SS') skip.
MAIN_BLOCK:
do on error undo, throw:
empty temp-table tqReport.
empty temp-table tqGLCurrency.
empty temp-table tqPayment.
define buffer bDDocPostLine for DDocumentPostingLine.
define buffer bPostLine for PostingLine.
define buffer bbank for banknumber.
define buffer bgl for gl.
/* Get CompanyCode by iiCompanyId */
find first Company where
Company.Company_id = iiCompanyID
no-lock no-error.
if iiCompanyID = ? or iiCompanyID = 0 or not available Company
then do:
assign ocConCheckLineDetResult = {&CONCHECKRESULT-FAILED}.
PUT STREAM sTechOut unformatted "**Error** Input parameter CompanyID is null or Company not found." skip.
LEAVE MAIN_BLOCK.
end.
assign vcCompanyCodeLocal = Company.Companycode.
/* Get YearPeriod by iiPeriodID */
find first Period of Company where
Period_id = iiPeriodID
no-lock no-error.
if iiPeriodID = ? or iiPeriodID = 0 or not available Period
then do:
assign ocConCheckLineDetResult = {&CONCHECKRESULT-FAILED}.
PUT STREAM sTechOut unformatted "**Error** Input parameter PeriodID is null or Period not found." skip.
LEAVE MAIN_BLOCK.
end.
assign viSelectedYearPeriod = Period.PeriodYearPeriod.
find first DomainProperty where DomainProperty.Domain_ID = Company.Domain_ID no-lock no-error.
if available DomainProperty then
assign viCompanyLC = DomainProperty.Currency_ID
viCompanyCC = DomainProperty.StatutoryCurrency_ID.
/* now we look for postinglines for payments that have happened in the checked period only */
for each postingline where postingline.company_ID = company.company_ID and
postingline.postingyearperiod = viSelectedYearPeriod no-lock,
each DDocumentPostingLine of postingline no-lock:
find DDocument of DDocumentPostingLine no-lock no-error.
if not available DDocument then do:
<M-32 run GetPostingLineKey
(input postingline.posting_id (iiPostingId),
input-output viErrorsFound (biErrorNumbers),
output vcJournalCodeTmp (ocJournalCode),
output vcPostingVoucherTmp (ocPostingVoucher),
input postingline.postingline_id (iiPostingLineId),
output vlBothFound (olBothFound),
output viFcReturnSuper (oiReturnStatus)) in TConCheck>
if vlBothFound = yes
then assign vcKey = vcCompanyCodeLocal + " " + substring(string(viSelectedYearPeriod,"999999"),1,4,"CHARACTER") + "/" + substring(string(viSelectedYearPeriod,"999999"),5,2,"CHARACTER") + "/" + trim(vcJournalCodeTmp) + "/" + string(vcPostingVoucherTmp,"999999999") + "/" + string(PostingLine.PostingLineSequence).
else next.
create tqReport.
assign tqReport.tcReportText = "Referential integrity error: DDocumentPostingLine without parent DDocument: " + vcKey + " DDocumentPostingLine_ID: " + string(DDocumentPostingLine_ID)
tqReport.tiErrorSeverity = 1.
assign viErrorsFound = viErrorsFound + 1.
next.
end.
find tqPayment where tqPayment.tiPaymentID = DDocument.DDocument_ID no-error.
if available tqPayment then next. /* we already checked this payment */
create tqPayment.
assign tqPayment.tiPaymentID = DDocument.DDocument_ID.
if DDocument.DDocumentstatus = "INITIAL" then next.
/* now check the Allocations */
if DDocument.DDocumentstatus <> "BOUNCED" and
DDocument.DDocumentstatus <> "PAID" and
DDocument.DDocumentstatus <> "VOID" then do:
assign
vdCurrDebitTC = 0.
for each DDocumentInvoiceXref of DDocument no-lock:
find Dinvoice where Dinvoice.Dinvoice_ID = DDocumentInvoiceXref.Dinvoice_ID no-lock no-error.
if not available DInvoice then do:
<M-17 run GetPostingLineKey
(input postingline.posting_id (iiPostingId),
input-output viErrorsFound (biErrorNumbers),
output vcJournalCodeTmp (ocJournalCode),
output vcPostingVoucherTmp (ocPostingVoucher),
input postingline.postingline_id (iiPostingLineId),
output vlBothFound (olBothFound),
output viFcReturnSuper (oiReturnStatus)) in TConCheck>
if vlBothFound = yes
then assign vcKey = " " + vcCompanyCodeLocal + " " + substring(string(viSelectedYearPeriod,"999999"),1,4,"CHARACTER") + "/" + substring(string(viSelectedYearPeriod,"999999"),5,2,"CHARACTER") + "/" + trim(vcJournalCodeTmp) + "/" + string(vcPostingVoucherTmp,"999999999") + "/" + string(PostingLine.PostingLineSequence).
else next.
create tqReport.
assign tqReport.tcReportText = "Referential integrity error: DDocumentInvoiceXref with invalid DInvoice_ID: " + string(DDocumentInvoiceXref.Dinvoice_ID) + vcKey + " DDocumentPostingLine_ID: " + string(DDocumentPostingLine_ID)
tqReport.tiErrorSeverity = 1.
assign viErrorsFound = viErrorsFound + 1.
next.
end.
find journal of Dinvoice no-lock no-error.
if not available Journal then do:
<M-7 run GetPostingLineKey
(input postingline.posting_id (iiPostingId),
input-output viErrorsFound (biErrorNumbers),
output vcJournalCodeTmp (ocJournalCode),
output vcPostingVoucherTmp (ocPostingVoucher),
input postingline.postingline_id (iiPostingLineId),
output vlBothFound (olBothFound),
output viFcReturnSuper (oiReturnStatus)) in TConCheck>
if vlBothFound = yes
then assign vcKey = " " + vcCompanyCodeLocal + " " + substring(string(viSelectedYearPeriod,"999999"),1,4,"CHARACTER") + "/" + substring(string(viSelectedYearPeriod,"999999"),5,2,"CHARACTER") + "/" + trim(vcJournalCodeTmp) + "/" + string(vcPostingVoucherTmp,"999999999") + "/" + string(PostingLine.PostingLineSequence).
else next.
create tqReport.
assign tqReport.tcReportText = "Referential integrity error: DInvoice with invalid Journal_ID: " + string(DInvoice.Journal_ID) + vcKey + " DDocumentPostingLine_ID: " + string(DDocumentPostingLine_ID)
tqReport.tiErrorSeverity = 1.
assign viErrorsFound = viErrorsFound + 1.
next.
end.
assign vdCurrDebitTC = vdCurrDebitTC - DDocumentInvoiceXrefAlloTC.
if (DDocumentInvoiceXrefAlloTC < 0 and
journal.journaltypecode <> "DEBTORCREDITNOTECORRECT") or
journal.journaltypecode = "DEBTORINVOICECORRECT"
then
assign vdCurrDebitTC = vdCurrDebitTC - DDocumentInvoiceXrefDiscTC + DDocumentInvoiceXrefIntTC.
else assign vdCurrDebitTC = vdCurrDebitTC + DDocumentInvoiceXrefDiscTC - DDocumentInvoiceXrefIntTC.
end.
if vdCurrDebitTC <> (DDocument.DDocumentOriginalDebitTC - DDocument.DDocumentOriginalCreditTC) then do:
<M-56 run GetPostingLineKey
(input postingline.posting_id (iiPostingId),
input-output viErrorsFound (biErrorNumbers),
output vcJournalCodeTmp (ocJournalCode),
output vcPostingVoucherTmp (ocPostingVoucher),
input postingline.postingline_id (iiPostingLineId),
output vlBothFound (olBothFound),
output viFcReturnSuper (oiReturnStatus)) in TConCheck>
if vlBothFound = yes
then assign vcKey = vcCompanyCodeLocal + " " + substring(string(viSelectedYearPeriod,"999999"),1,4,"CHARACTER") + "/" + substring(string(viSelectedYearPeriod,"999999"),5,2,"CHARACTER") + "/" + trim(vcJournalCodeTmp) + "/" + string(vcPostingVoucherTmp,"999999999") + "/" + string(PostingLine.PostingLineSequence).
else next.
create tqReport.
assign tqReport.tcReportText = "Detial onconsistency error: Payment Allocation details do not match total payment. DDocument: " + vcKey +
" Calculated Allocations in TC: " + string(vdCurrDebitTC) +
" Payment Total in TC: " + string(DDocument.DDocumentOriginalDebitTC - DDocument.DDocumentOriginalCreditTC)
tqReport.tiErrorSeverity = 1.
assign viDetailInconsistencyError = viDetailInconsistencyError + 1.
end.
end. /* if DDocument.DDocumentstatus <> "BOUNCED" and */
/* now check the Payment balance and status */
assign
vdCurrDebitLC = 0
vdCurrCreditLC = 0
vdCurrDebitTC = 0
vdCurrCreditTC = 0
vdCurrDebitCC = 0
vdCurrCreditCC = 0
vtLastPostingDate = 01/01/1900.
for each bDDocPostLine of DDocument no-lock:
find bPostLine where bPostLine.PostingLine_ID = bDDocPostLine.PostingLine_ID no-lock no-error.
if not available bPostLine then do:
create tqReport.
assign tqReport.tcReportText =
"Integrity Error. Postingline not available for DDocumentPostingLine " + vcCompanyCodeLocal + " " + "Payment: " + DDocument.DDocumentType + "/" +
string(DDocument.DDocumentYear) + "/" + string(DDocument.DDocumentNumber) +
"DDocumentPostingLine_ID" + string(bDDocPostLine.DDocumentPostingLine_ID) + " PostingLine_ID: " + string(bDDocPostLine.PostingLine_ID)
tqReport.tiErrorSeverity = 1.
assign viErrorsFound = viErrorsFound + 1.
next.
end.
/* let's also save the latest postingdate for this item to check the closingdate */
if bPostLine.PostingDate > vtLastPostingDate then do:
assign vtLastPostingDate = bPostLine.PostingDate.
end.
/* accumulate the amounts */
assign
vdCurrDebitLC = vdCurrDebitLC + bPostLine.PostingLineDebitLC
vdCurrCreditLC = vdCurrCreditLC + bPostLine.PostingLineCreditLC
vdCurrDebitTC = vdCurrDebitTC + bPostLine.PostingLineDebitTC
vdCurrCreditTC = vdCurrCreditTC + bPostLine.PostingLineCreditTC
vdCurrDebitCC = vdCurrDebitCC + bPostLine.PostingLineDebitCC
vdCurrCreditCC = vdCurrCreditCC + bPostLine.PostingLineCreditCC.
end.
/* net the calculated balances, because that is also done in the payment postings */
if vdCurrDebitLC > vdCurrCreditLC then do:
assign vdCurrDebitLC = vdCurrDebitLC - vdCurrCreditLC
vdCurrCreditLC = 0.
end.
else do:
assign vdCurrCreditLC = vdCurrCreditLC - vdCurrDebitLC
vdCurrDebitLC = 0.
end.
if vdCurrDebitTC > vdCurrCreditTC then do:
assign vdCurrDebitTC = vdCurrDebitTC - vdCurrCreditTC
vdCurrCreditTC = 0.
end.
else do:
assign vdCurrCreditTC = vdCurrCreditTC - vdCurrDebitTC
vdCurrDebitTC = 0.
end.
if vdCurrDebitCC > vdCurrCreditCC then do:
assign vdCurrDebitCC = vdCurrDebitCC - vdCurrCreditCC
vdCurrCreditCC = 0.
end.
else do:
assign vdCurrCreditCC = vdCurrCreditCC - vdCurrDebitCC
vdCurrDebitCC = 0.
end.
/* we have now the complete balance of the item and we can check it */
if DDocumentIsOpen = true and
(vdCurrDebitLC - vdCurrCreditLC) <> (DDocument.DDocumentOriginalDebitLC - DDocument.DDocumentOriginalCreditLC) then do:
<M-6 run GetPostingLineKey
(input bPostLine.posting_id (iiPostingId),
input-output viErrorsFound (biErrorNumbers),
output vcJournalCodeTmp (ocJournalCode),
output vcPostingVoucherTmp (ocPostingVoucher),
input bPostLine.postingline_id (iiPostingLineId),
output vlBothFound (olBothFound),
output viFcReturnSuper (oiReturnStatus)) in TConCheck>
if vlBothFound = yes
then assign vcKey = vcCompanyCodeLocal + " " + substring(string(viSelectedYearPeriod,"999999"),1,4,"CHARACTER") + "/" + substring(string(viSelectedYearPeriod,"999999"),5,2,"CHARACTER") + "/" + trim(vcJournalCodeTmp) + "/" + string(vcPostingVoucherTmp,"999999999") + "/" + string(PostingLine.PostingLineSequence).
else next.
create tqReport.
assign tqReport.tcReportText = "Detail inconsistency error. Payment is open. BC postings total should be equal to orginal BC. " + "Payment: " + DDocument.DDocumentType + "/" +
string(DDocument.DDocumentYear) + "/" + string(DDocument.DDocumentNumber) +
" Calculated: " + string(vdCurrDebitLC - vdCurrCreditLC) +
" In DDocument record: " + string(DDocument.DDocumentOriginalDebitLC - DDocument.DDocumentOriginalCreditLC)
tqReport.tiErrorSeverity = 1.
assign viDetailInconsistencyError = viDetailInconsistencyError + 1.
end.
if DDocumentIsOpen = true and
(vdCurrDebitTC - vdCurrCreditTC) <> (DDocument.DDocumentOriginalDebitTC - DDocument.DDocumentOriginalCreditTC) then do:
<M-8 run GetPostingLineKey
(input bPostLine.posting_id (iiPostingId),
input-output viErrorsFound (biErrorNumbers),
output vcJournalCodeTmp (ocJournalCode),
output vcPostingVoucherTmp (ocPostingVoucher),
input bPostLine.postingline_id (iiPostingLineId),
output vlBothFound (olBothFound),
output viFcReturnSuper (oiReturnStatus)) in TConCheck>
if vlBothFound = yes
then assign vcKey = vcCompanyCodeLocal + " " + substring(string(viSelectedYearPeriod,"999999"),1,4,"CHARACTER") + "/" + substring(string(viSelectedYearPeriod,"999999"),5,2,"CHARACTER") + "/" + trim(vcJournalCodeTmp) + "/" + string(vcPostingVoucherTmp,"999999999") + "/" + string(PostingLine.PostingLineSequence).
else next.
create tqReport.
assign tqReport.tcReportText = "Detail inconsistency error. Payment is open. TC postings total should be equal to orginal TC. " + "Payment: " + DDocument.DDocumentType + "/" +
string(DDocument.DDocumentYear) + "/" + string(DDocument.DDocumentNumber) +
" Calculated: " + string(vdCurrDebitTC - vdCurrCreditTC) +
" In DDocument record: " + string(DDocument.DDocumentOriginalDebitTC - DDocument.DDocumentOriginalCreditTC)
tqReport.tiErrorSeverity = 1.
assign viDetailInconsistencyError = viDetailInconsistencyError + 1.
end.
if DDocumentIsOpen = true and
(vdCurrDebitCC - vdCurrCreditCC) <> (DDocument.DDocumentOriginalDebitCC - DDocument.DDocumentOriginalCreditCC) then do:
<M-92 run GetPostingLineKey
(input bPostLine.posting_id (iiPostingId),
input-output viErrorsFound (biErrorNumbers),
output vcJournalCodeTmp (ocJournalCode),
output vcPostingVoucherTmp (ocPostingVoucher),
input bPostLine.postingline_id (iiPostingLineId),
output vlBothFound (olBothFound),
output viFcReturnSuper (oiReturnStatus)) in TConCheck>
if vlBothFound = yes
then assign vcKey = vcCompanyCodeLocal + " " + substring(string(viSelectedYearPeriod,"999999"),1,4,"CHARACTER") + "/" + substring(string(viSelectedYearPeriod,"999999"),5,2,"CHARACTER") + "/" + trim(vcJournalCodeTmp) + "/" + string(vcPostingVoucherTmp,"999999999") + "/" + string(PostingLine.PostingLineSequence).
else next.
create tqReport.
assign tqReport.tcReportText = "Detail inconsistency error. Payment is open. SC postings total should be equal to orginal SC. " + "Payment: " + DDocument.DDocumentType + "/" +
string(DDocument.DDocumentYear) + "/" + string(DDocument.DDocumentNumber) +
" Calculated: " + string(vdCurrDebitCC - vdCurrCreditCC) +
" In DDocument record: " + string(DDocument.DDocumentOriginalDebitCC - DDocument.DDocumentOriginalCreditCC)
tqReport.tiErrorSeverity = 1.
assign viDetailInconsistencyError = viDetailInconsistencyError + 1.
end.
if DDocument.DDocumentIsOpen = True and
DDocument.DDocumentClosingDate <> 12/31/9999 then do:
<M-19 run GetPostingLineKey
(input bPostLine.posting_id (iiPostingId),
input-output viErrorsFound (biErrorNumbers),
output vcJournalCodeTmp (ocJournalCode),
output vcPostingVoucherTmp (ocPostingVoucher),
input bPostLine.postingline_id (iiPostingLineId),
output vlBothFound (olBothFound),
output viFcReturnSuper (oiReturnStatus)) in TConCheck>
if vlBothFound = yes
then assign vcKey = vcCompanyCodeLocal + " " + substring(string(viSelectedYearPeriod,"999999"),1,4,"CHARACTER") + "/" + substring(string(viSelectedYearPeriod,"999999"),5,2,"CHARACTER") + "/" + trim(vcJournalCodeTmp) + "/" + string(vcPostingVoucherTmp,"999999999") + "/" + string(PostingLine.PostingLineSequence).
else next.
create tqReport.
assign tqReport.tcReportText = "Detail inconsistency error in Closing Date. Expected 12/31/9999 for OPEN status. " + "Payment: " + DDocument.DDocumentType + "/" +
string(DDocument.DDocumentYear) + "/" + string(DDocument.DDocumentNumber) +
" DDocumentIsOpen: " + string(DDocument.DDocumentIsOpen) +
" DDocumentClosingDate:" + string(DDocument.DDocumentClosingDate)
tqReport.tiErrorSeverity = 1.
assign viDetailInconsistencyError = viDetailInconsistencyError + 1.
end.
if DDocument.DDocumentIsOpen = False and
DDocument.DDocumentClosingDate <> vtLastPostingDate then do:
<M-59 run GetPostingLineKey
(input bPostLine.posting_id (iiPostingId),
input-output viErrorsFound (biErrorNumbers),
output vcJournalCodeTmp (ocJournalCode),
output vcPostingVoucherTmp (ocPostingVoucher),
input bPostLine.postingline_id (iiPostingLineId),
output vlBothFound (olBothFound),
output viFcReturnSuper (oiReturnStatus)) in TConCheck>
if vlBothFound = yes
then assign vcKey = vcCompanyCodeLocal + " " + substring(string(viSelectedYearPeriod,"999999"),1,4,"CHARACTER") + "/" + substring(string(viSelectedYearPeriod,"999999"),5,2,"CHARACTER") + "/" + trim(vcJournalCodeTmp) + "/" + string(vcPostingVoucherTmp,"999999999") + "/" + string(PostingLine.PostingLineSequence).
else next.
create tqReport.
assign tqReport.tcReportText = "Detail inconsistency error in Closing Date. Expected " + string(vtLastPostingDate) + " for closed payment: " + DDocument.DDocumentType + "/" +
string(DDocument.DDocumentYear) + "/" + string(DDocument.DDocumentNumber) +
" DDocumentIsOpen: " + string(DDocument.DDocumentIsOpen) +
" DDocumentClosingDate:" + string(DDocument.DDocumentClosingDate)
tqReport.tiErrorSeverity = 1.
assign viDetailInconsistencyError = viDetailInconsistencyError + 1.
end.
end. /* for each postingline */
empty temp-table tqGLCurrency.
/* now check the GL Balance vs. Open Payments */
for each DDocument where DDocumentClosingDate > period.periodenddate and
DDocument.Company_ID = Company.Company_ID no-lock:
assign vlExistedAtPeriodEnd = no
vcStatusAtPeriodEnd = "".
for each DDocumentPostingLine of DDocument no-lock, each postingline of DDocumentPostingLine no-lock by postingline.postingdate:
if postingline.postingdate <= period.periodenddate then do:
assign vlExistedAtPeriodEnd = yes
vcStatusAtPeriodEnd = DDocumentPostingLine.DDocumentStatus.
end.
else leave.
end.
if vlExistedAtPeriodEnd = no or
vcStatusAtPeriodEnd = "INITIAL" or
vcStatusAtPeriodEnd = "BOUNCED" or
vcStatusAtPeriodEnd = "PAID" or
vcStatusAtPeriodEnd = "VOID" or
vcStatusAtPeriodEnd = "" then next.
find banknumber of DDocument no-lock no-error.
if not available BankNumber then do:
create tqReport.
assign tqReport.tcReportText = "Referential integrity error in DDocument.BankNumber! Missing banknumber: " + string(DDocument.BankNumber_ID) + " Year: " + string(DDocument.DDocumentYear) + " Document type: " + DDocument.DDocumenttype + " Number: " + string(DDocument.DDocumentNumber) + " Entity: " + Company.CompanyCode + " Period " + string(Period.PeriodYearPeriod)
tqReport.tiErrorSeverity = 1.
assign viErrorsFound = viErrorsFound + 1.
next.
end.
find bankpayformat where bankpayformat.bankpayformat_ID = banknumber.bankpayformat_ID no-lock no-error.
if not available bankpayformat then do:
create tqReport.
assign tqReport.tcReportText = "Referential integrity error in BankNumber.Bankpayformat! Missing bankpayformat: " + string(BankNumber.bankpayformat_ID) + " Year: " + string(DDocument.DDocumentYear) + " Document type: " + DDocument.DDocumenttype + " Number: " + string(DDocument.DDocumentNumber) + " Entity: " + Company.CompanyCode + " Period " + string(Period.PeriodYearPeriod)
tqReport.tiErrorSeverity = 1.
assign viErrorsFound = viErrorsFound + 1.
next.
end.
find bbank where bbank.banknumber_ID = bankpayformat.banknumber_ID no-lock no-error.
if not available bBank then do:
create tqReport.
assign tqReport.tcReportText = "Referential integrity error in BankPayFormat.BankNumber! Missing banknumber 2: " + string(bankpayformat.banknumber_ID) + " Year: " + string(DDocument.DDocumentYear) + " Document type: " + DDocument.DDocumenttype + " Number: " + string(DDocument.DDocumentNumber) + " Entity: " + Company.CompanyCode + " Period " + string(Period.PeriodYearPeriod)
tqReport.tiErrorSeverity = 1.
assign viErrorsFound = viErrorsFound + 1.
next.
end.
find gl where gl.gl_ID = bbank.parentobject_ID no-lock no-error.
if not available gl then do:
create tqReport.
assign tqReport.tcReportText = "Referential integrity error in BankNumber.GL! GL: " + string(bbank.parentobject_ID) + " Parent type: " + bbank.bankNumberParentType + " Year: " + string(DDocument.DDocumentYear) + " Document type: " + DDocument.DDocumenttype + " Number: " + string(DDocument.DDocumentNumber) + " Entity: " + Company.CompanyCode + " Period " + string(Period.PeriodYearPeriod)
tqReport.tiErrorSeverity = 1.
assign viErrorsFound = viErrorsFound + 1.
next.
end.
find DDocumentstatus where DDocumentstatus.company_ID = DDocument.company_ID and DDocumentstatus.bankgl_ID = bbank.parentobject_ID and
DDocumentstatus.DDocumentStatusStatus = vcStatusAtPeriodEnd and
DDocumentstatus.DDocumentstatusdoctype = DDocument.DDocumenttype no-lock no-error.
if not available DDocumentstatus then do:
create tqReport.
assign tqReport.tcReportText = "Referential integrity error in DDocumentStatus! Missing status: " + vcStatusAtPeriodEnd + " Document type: " + DDocument.DDocumenttype + " Bank Account: " + GL.GLCode + " Entity: " + Company.CompanyCode + " Period: " + string(Period.PeriodYearPeriod) + " GL Account: " + GL.GLCode
tqReport.tiErrorSeverity = 1.
assign viErrorsFound = viErrorsFound + 1.
next.
end.
find gl where gl.gl_ID = DDocumentstatus.gl_ID no-lock no-error.
if not available gl
then do:
create tqReport.
assign tqReport.tcReportText = "Referential integrity error in DDocumentstatus.GL! GL: " + string(DDocumentstatus.gl_ID) + " Year: " + string(DDocument.DDocumentYear) + " Document type: " + DDocument.DDocumenttype + " Number: " + string(DDocument.DDocumentNumber) + " Entity: " + Company.CompanyCode + " Period " + string(Period.PeriodYearPeriod)
tqReport.tiErrorSeverity = 1
viErrorsFound = viErrorsFound + 1.
end.
find currency where currency.currency_ID = DDocument.currency_ID no-lock no-error.
if not available currency
then do:
create tqReport.
assign tqReport.tcReportText = "Referential integrity error in DDocument.Currency! Currency: " + string(DDocument.currency_ID) + " Year: " + string(DDocument.DDocumentYear) + " Document type: " + DDocument.DDocumenttype + " Number: " + string(DDocument.DDocumentNumber) + " Entity: " + Company.CompanyCode + " Period " + string(Period.PeriodYearPeriod)
tqReport.tiErrorSeverity = 1
viErrorsFound = viErrorsFound + 1.
end.
find tqGLCurrency where tqGLCurrency.tiGLID = ddocumentstatus.gl_ID and
tqGLCurrency.tiCurrencyID = ddocument.currency_ID no-error.
if not available tqGLCurrency then do:
create tqGLCurrency.
assign tqGLCurrency.tiGLID = ddocumentstatus.gl_ID
tqGLCurrency.tiCurrencyID = ddocument.currency_ID.
for first bgl where bgl.gl_id = tqGLCurrency.tiGLID no-lock:
assign tqGLCurrency.tcGLCode = bgl.glcode
tqGLCurrency.tcGLDescription = bgl.gldescription.
end.
for first currency where currency.currency_id = tqGLCurrency.tiCurrencyID no-lock:
assign tqGLCurrency.tcCurrencyCode = currency.currencycode.
end.
end.
assign tqGLCurrency.tdSubBalanceLC = tqGLCurrency.tdSubBalanceLC + DDocument.DDocumentOriginalDebitLC - DDocument.DDocumentOriginalCreditLC
tqGLCurrency.tdSubBalanceTC = tqGLCurrency.tdSubBalanceTC + DDocument.DDocumentOriginalDebitTC - DDocument.DDocumentOriginalCreditTC
tqGLCurrency.tdSubBalanceCC = tqGLCurrency.tdSubBalanceCC + DDocument.DDocumentOriginalDebitCC - DDocument.DDocumentOriginalCreditCC.
end.
for each tqGLCurrency,
each postinghist where postinghist.company_ID = Company.Company_ID and
postingHist.GL_ID = tqGLCurrency.tiGLID and
postinghist.Currency_ID = tqGLCurrency.tiCurrencyID and
postinghistyearperiodfrom <= period.periodyearperiod and
postinghistyearperiodtill >= period.periodyearperiod no-lock:
assign tqGLCurrency.tdBalanceGLLC = tqGLCurrency.tdBalanceGLLC + PostingHist.PostingHistBalanceDebitLC - PostingHist.PostingHistBalanceCreditLC
tqGLCurrency.tdBalanceGLTC = tqGLCurrency.tdBalanceGLTC + PostingHist.PostingHistBalanceDebitTC - PostingHist.PostingHistBalanceCreditTC
tqGLCurrency.tdBalanceGLCC = tqGLCurrency.tdBalanceGLCC + PostingHist.PostingHistBalanceDebitCC - PostingHist.PostingHistBalanceCreditCC.
end.
for each tqGLCurrency:
find currency where currency.currency_ID = tqGLCurrency.tiCurrencyID no-lock no-error.
find gl where gl.gl_ID = tqGLCurrency.tiGLID no-lock no-error.
if available currency then assign vcCurrencyCode = currency.currencycode.
else assign vcCurrencyCode = "Missing":U.
if available GL then assign vcGLCode = GL.GLCode.
else assign vcGLCode = "Missing":U.
/* now we can compare the balances */
if tqGLCurrency.tdSubBalanceLC <> tqGLCurrency.tdBalanceGLLC then do:
find first QPostingLine where QPostingLine.GL_ID = GL.GL_ID and QPostingLine.PeriodYearPeriod <= Period.PeriodYearPeriod and QPostingLine.Currency_ID = currency.currency_ID use-index GL no-lock no-error.
if available QPostingLine then do:
create tqReport.
assign tqReport.tcReportText = "Balance inconsistency error: Unprocessed Hitory Daemon queue causes inconsistency between GL and payments sub-ledger! Entity: " + Company.CompanyCode + " Period " + string(Period.PeriodYearPeriod) + " GL Account: " + vcGLCode + " Currency: " + vcCurrencyCode +
" GL balance in BC: " + string(tqGLCurrency.tdBalanceGLLC) +
" payment sub-ledger in BC: " + string(tqGLCurrency.tdSubBalanceLC)
tqReport.tiErrorSeverity = 0.
assign viBalanceInconsistencyError = viBalanceInconsistencyError + 1.
end.
else do:
create tqReport.
assign tqReport.tcReportText = "Balance inconsistency error: Inconsistency between GL and payments sub-ledger! Entity: " + Company.CompanyCode + " Period " + string(Period.PeriodYearPeriod) + " GL Account: " + vcGLCode + " Currency: " + vcCurrencyCode +
" GL balance in BC: " + string(tqGLCurrency.tdBalanceGLLC) +
" payment sub-ledger in BC: " + string(tqGLCurrency.tdSubBalanceLC)
tqReport.tiErrorSeverity = 0.
assign viBalanceInconsistencyError = viBalanceInconsistencyError + 1.
end.
end.
if tqGLCurrency.tdSubBalanceTC <> tqGLCurrency.tdBalanceGLTC then do:
find first QPostingLine where QPostingLine.GL_ID = GL.GL_ID and QPostingLine.PeriodYearPeriod <= Period.PeriodYearPeriod and QPostingLine.Currency_ID = currency.currency_ID use-index GL no-lock no-error.
if available QPostingLine then do:
create tqReport.
assign tqReport.tcReportText = "Balance inconsistency error: Unprocessed Hitory Daemon queue causes inconsistency between GL and payments sub-ledger! Entity: " + Company.CompanyCode + " Period " + string(Period.PeriodYearPeriod) + " GL Account: " + vcGLCode + " Currency: " + vcCurrencyCode +
" GL balance in TC: " + string(tqGLCurrency.tdBalanceGLTC) +
" payment sub-ledger in TC: " + string(tqGLCurrency.tdSubBalanceTC)
tqReport.tiErrorSeverity = 0.
assign viBalanceInconsistencyError = viBalanceInconsistencyError + 1.
end.
else do:
create tqReport.
assign tqReport.tcReportText = "Balance inconsistency error: Inconsistency between GL and payments sub-ledger! Entity: " + Company.CompanyCode + " Period " + string(Period.PeriodYearPeriod) + " GL Account: " + vcGLCode + " Currency: " + vcCurrencyCode +
" GL balance in TC: " + string(tqGLCurrency.tdBalanceGLTC) +
" payment sub-ledger in TC: " + string(tqGLCurrency.tdSubBalanceTC)
tqReport.tiErrorSeverity = 0.
assign viBalanceInconsistencyError = viBalanceInconsistencyError + 1.
end.
end.
if tqGLCurrency.tdSubBalanceCC <> tqGLCurrency.tdBalanceGLCC then do:
find first QPostingLine where QPostingLine.GL_ID = GL.GL_ID and QPostingLine.PeriodYearPeriod <= Period.PeriodYearPeriod and QPostingLine.Currency_ID = currency.currency_ID use-index GL no-lock no-error.
if available QPostingLine then do:
create tqReport.
assign tqReport.tcReportText = "Balance inconsistency error: Unprocessed Hitory Daemon queue causes inconsistency between GL and payments sub-ledger! Entity: " + Company.CompanyCode + " Period " + string(Period.PeriodYearPeriod) + " GL Account: " + vcGLCode + " Currency: " + vcCurrencyCode +
" GL balance in SC: " + string(tqGLCurrency.tdBalanceGLCC) +
" payment sub-ledger in SC: " + string(tqGLCurrency.tdSubBalanceCC)
tqReport.tiErrorSeverity = 0.
assign viBalanceInconsistencyError = viBalanceInconsistencyError + 1.
end.
else do:
create tqReport.
assign tqReport.tcReportText = "Balance inconsistency error: Inconsistency between GL and payments sub-ledger! Entity: " + Company.CompanyCode + " Period " + string(Period.PeriodYearPeriod) + " GL Account: " + vcGLCode + " Currency: " + vcCurrencyCode +
" GL balance in SC: " + string(tqGLCurrency.tdBalanceGLCC) +
" payment sub-ledger in SC: " + string(tqGLCurrency.tdSubBalanceCC)
tqReport.tiErrorSeverity = 0.
assign viBalanceInconsistencyError = viBalanceInconsistencyError + 1.
end.
end.
end.
end. /* main_block */
if can-find(first tqReport)
then assign ocConCheckLineDetResult = {&CONCHECKRESULT-FAILED}.
else assign ocConCheckLineDetResult = {&CONCHECKRESULT-PASSED}.
/* print output */
for each tqReport use-index ErrorSeverityIndex:
put stream sTechOut unformatted tqReport.tcReportText + "," + string(tqReport.tiErrorSeverity) skip.
assign oiErrorNumbers = oiErrorNumbers + 1.
end.
assign oiConCheckLineDetDuration = etime - vdStartEtime.
/* to format the total duration time */
assign viMilliSecs = oiConCheckLineDetDuration.
assign viSecs = TRUNCATE (oiConCheckLineDetDuration / 1000, 0).
assign viMilliSecs = viMilliSecs mod 1000.
assign vcTotalTime = string(viSecs, 'HH:MM:SS') + '.' + string(viMilliSecs).
put stream sTechOut unformatted 'Execution Duration: ' vcTotalTime skip.
put stream sTechOut unformatted 'Execution Result: ' ocConCheckLineDetResult skip.
put stream sTechOut unformatted '<Error Count:> ' oiErrorNumbers skip.
put stream sTechOut unformatted '**********************************************************************' skip.
output stream sTechOut close.
put stream sSumOut unformatted ' ' skip.
put stream sSumOut unformatted trim({&CONCHECKLINEDETTYPE-ARGL-TR}) skip.
put stream sSumOut unformatted '-------------------' skip.
put stream sSumOut unformatted trim(#T-69'Execution Result':255(510391963)T-69#) + ': ' + ocConCheckLineDetResult skip.
if ocConCheckLineDetResult = {&CONCHECKRESULT-FAILED}
then do:
if viErrorsFound > 0
then put stream sSumOut unformatted
trim(#T-9'Total number of referential integrity errors':150(916604167)T-9#)
': '
viErrorsFound skip.
if viBalanceInconsistencyError > 0
then put stream sSumOut unformatted
trim(#T-4'Total number of balance inconsistency errors':100(282714224)T-4#)
': ' viBalanceInconsistencyError skip.
if viDetailInconsistencyError > 0
then put stream sSumOut unformatted
trim(#T-58'Total number of detail inconsistency errors':150(665310851)T-58#)
': ' viDetailInconsistencyError skip.
put stream sSumOut unformatted
'('
trim(#T-83'Please see details in the file':150(806040074)T-83#) ' ' icFile ')' skip.
end.
if can-find (first tqGLCurrency)
then do:
for each tqGLCurrency break by tqGLCurrency.tiGLID
by tqGLCurrency.tiCurrencyID:
if first-of(tqGLCurrency.tiGLID)
then do:
assign vdGLBalanceLCPerGL = 0
vdGLBalanceCCPerGL = 0
vdSubBalanceCCPerGL = 0
vdSubBalanceLCPerGL = 0
vlDetailPrinted = no.
put stream sSumOut unformatted ' ' skip.
put stream sSumOut unformatted trim(#T-35'GL':20(199)T-35#)
': ':U
tqGLCurrency.tcGLCode
' ':U
tqGLCurrency.tcGLDescription
' ':U skip.
end.
assign vdGLBalanceLCPerGL = vdGLBalanceLCPerGL + tqGLCurrency.tdBalanceGLLC
vdGLBalanceCCPerGL = vdGLBalanceCCPerGL + tqGLCurrency.tdBalanceGLCC
vdSubBalanceLCPerGL = vdSubBalanceLCPerGL + tqGLCurrency.tdSubBalanceLC
vdSubBalanceCCPerGL = vdSubBalanceCCPerGL + tqGLCurrency.tdSubBalanceCC.
if tqGLCurrency.tdBalanceGLTC <> tqGLCurrency.tdSubBalanceTC or
tqGLCurrency.tdBalanceGLLC <> tqGLCurrency.tdSubBalanceLC or
tqGLCurrency.tdBalanceGLCC <> tqGLCurrency.tdSubBalanceCC
then do:
assign vlDetailPrinted = yes.
put stream sSumOut unformatted trim(#T-60'Currency':20(220)T-60#) +
': ':U + tqGLCurrency.tcCurrencyCode at 1
trim(#T-848'GL Balance':20(1017)T-848#) +
'':U to 30
trim(#T-892'Sub-ledger Balance':20(254104290)T-892#) to 60
trim(#T-38'Difference':20(4114)T-38#) to 90 skip.
<M-41 run RoundAmount
(input-output tqGLCurrency.tdBalanceGLTC (bdUnroundedAmount),
input tqGLCurrency.tiCurrencyID (iiCurrencyId),
input ? (icCurrencyCode),
output vcGLBalanceFormat (ocFormatedAmount),
output viFcReturnSuper (oiReturnStatus)) in TConCheck>
<M-46 run RoundAmount
(input-output tqGLCurrency.tdSubBalanceTC (bdUnroundedAmount),
input tqGLCurrency.tiCurrencyID (iiCurrencyId),
input ? (icCurrencyCode),
output vcSubBalanceFormat (ocFormatedAmount),
output viFcReturnSuper (oiReturnStatus)) in TConCheck>
assign vdDiff = tqGLCurrency.tdBalanceGLTC - tqGLCurrency.tdSubBalanceTC.
<M-12 run RoundAmount
(input-output vdDiff (bdUnroundedAmount),
input tqGLCurrency.tiCurrencyID (iiCurrencyId),
input ? (icCurrencyCode),
output vcDiffFormat (ocFormatedAmount),
output viFcReturnSuper (oiReturnStatus)) in TConCheck>
put stream sSumOut unformatted trim(#T-74'TC':3(4029)T-74#) at 1
vcGLBalanceFormat to 30
vcSubBalanceFormat to 60
vcDiffFormat to 90 skip.
<M-75 run RoundAmount
(input-output tqGLCurrency.tdBalanceGLLC (bdUnroundedAmount),
input viCompanyLC (iiCurrencyId),
input ? (icCurrencyCode),
output vcGLBalanceFormat (ocFormatedAmount),
output viFcReturnSuper (oiReturnStatus)) in TConCheck>
<M-52 run RoundAmount
(input-output tqGLCurrency.tdSubBalanceLC (bdUnroundedAmount),
input viCompanyLC (iiCurrencyId),
input ? (icCurrencyCode),
output vcSubBalanceFormat (ocFormatedAmount),
output viFcReturnSuper (oiReturnStatus)) in TConCheck>
assign vdDiff = tqGLCurrency.tdBalanceGLLC - tqGLCurrency.tdSubBalanceLC .
<M-33 run RoundAmount
(input-output vdDiff (bdUnroundedAmount),
input viCompanyLC (iiCurrencyId),
input ? (icCurrencyCode),
output vcDiffFormat (ocFormatedAmount),
output viFcReturnSuper (oiReturnStatus)) in TConCheck>
put stream sSumOut unformatted trim(#T-11'BC':3(4030)T-11#) at 1
vcGLBalanceFormat to 30
vcSubBalanceFormat to 60
vcDiffFormat to 90 skip.
<M-42 run RoundAmount
(input-output tqGLCurrency.tdBalanceGLCC (bdUnroundedAmount),
input viCompanyCC (iiCurrencyId),
input ? (icCurrencyCode),
output vcGLBalanceFormat (ocFormatedAmount),
output viFcReturnSuper (oiReturnStatus)) in TConCheck>
<M-367 run RoundAmount
(input-output tqGLCurrency.tdSubBalanceCC (bdUnroundedAmount),
input viCompanyCC (iiCurrencyId),
input ? (icCurrencyCode),
output vcSubBalanceFormat (ocFormatedAmount),
output viFcReturnSuper (oiReturnStatus)) in TConCheck>
assign vdDiff = tqGLCurrency.tdBalanceGLCC - tqGLCurrency.tdSubBalanceCC.
<M-2 run RoundAmount
(input-output vdDiff (bdUnroundedAmount),
input viCompanyCC (iiCurrencyId),
input ? (icCurrencyCode),
output vcDiffFormat (ocFormatedAmount),
output viFcReturnSuper (oiReturnStatus)) in TConCheck>
put stream sSumOut unformatted trim(#T-28'SC':12(3936)T-28#) at 1
vcGLBalanceFormat to 30
vcSubBalanceFormat to 60
vcDiffFormat to 90 skip.
end.
if last-of(tqGLCurrency.tiGLID)
then do:
assign vdSumGLBalanceCC = vdSumGLBalanceCC + vdGLBalanceCCPerGL
vdSumGLBalanceLC = vdSumGLbalanceLC + vdGLBalanceLCPerGL
vdSumSubBalanceCC = vdSumSubBalanceCC + vdSubBalanceCCPerGL
vdSumSubBalanceLC = vdSumSubBalanceLC + vdSubBalanceLCPerGL.
if vlDetailPrinted then put stream sSumOut unformatted ' ':U skip.
put stream sSumOut unformatted trim(#T-62'All Currencies':50(837972044)T-62#) at 1
trim(#T-773'GL Balance':20(1017)T-773#) to 30
trim(#T-64'Sub-ledger Balance':20(254104290)T-64#) to 60
trim(#T-16'Difference':20(4114)T-16#) to 90 skip.
<M-65 run RoundAmount
(input-output vdGLBalanceLCPerGL (bdUnroundedAmount),
input viCompanyLC (iiCurrencyId),
input ? (icCurrencyCode),
output vcGLBalanceFormat (ocFormatedAmount),
output viFcReturnSuper (oiReturnStatus)) in TConCheck>
<M-73 run RoundAmount
(input-output vdSubBalanceLCPerGL (bdUnroundedAmount),
input viCompanyLC (iiCurrencyId),
input ? (icCurrencyCode),
output vcSubBalanceFormat (ocFormatedAmount),
output viFcReturnSuper (oiReturnStatus)) in TConCheck>
assign vdDiff = vdGLBalanceLCPerGL - vdSubBalanceLCPerGL.
<M-36 run RoundAmount
(input-output vdDiff (bdUnroundedAmount),
input viCompanyLC (iiCurrencyId),
input ? (icCurrencyCode),
output vcDiffFormat (ocFormatedAmount),
output viFcReturnSuper (oiReturnStatus)) in TConCheck>
put stream sSumOut unformatted trim(#T-90'BC':3(4030)T-90#) at 1
vcGLBalanceFormat to 30
vcSubBalanceFormat to 60
vcDiffFormat to 90 skip.
<M-50 run RoundAmount
(input-output vdGLBalanceCCPerGL (bdUnroundedAmount),
input viCompanyCC (iiCurrencyId),
input ? (icCurrencyCode),
output vcGLBalanceFormat (ocFormatedAmount),
output viFcReturnSuper (oiReturnStatus)) in TConCheck>
<M-71 run RoundAmount
(input-output vdSubBalanceCCPerGL (bdUnroundedAmount),
input viCompanyCC (iiCurrencyId),
input ? (icCurrencyCode),
output vcSubBalanceFormat (ocFormatedAmount),
output viFcReturnSuper (oiReturnStatus)) in TConCheck>
assign vdDiff = vdGLBalanceCCPerGL - vdSubBalanceCCPerGL.
<M-54 run RoundAmount
(input-output vdDiff (bdUnroundedAmount),
input viCompanyCC (iiCurrencyId),
input ? (icCurrencyCode),
output vcDiffFormat (ocFormatedAmount),
output viFcReturnSuper (oiReturnStatus)) in TConCheck>
put stream sSumOut unformatted trim(#T-72'SC':12(3936)T-72#) at 1
vcGLBalanceFormat to 30
vcSubBalanceFormat to 60
vcDiffFormat to 90 skip.
end.
end. /* for each tBalanceByGLAP */
put stream sSumOut unformatted ' ' skip.
<M-15 run RoundAmount
(input-output vdSumGLBalanceLC (bdUnroundedAmount),
input viCompanyLC (iiCurrencyId),
input ? (icCurrencyCode),
output vcGLBalanceFormat (ocFormatedAmount),
output viFcReturnSuper (oiReturnStatus)) in TConCheck>
<M-39 run RoundAmount
(input-output vdSumSubBalanceLC (bdUnroundedAmount),
input viCompanyLC (iiCurrencyId),
input ? (icCurrencyCode),
output vcSubBalanceFormat (ocFormatedAmount),
output viFcReturnSuper (oiReturnStatus)) in TConCheck>
assign vdDiff = vdSumGLBalanceLC - vdSumSubBalanceLC.
<M-91 run RoundAmount
(input-output vdDiff (bdUnroundedAmount),
input viCompanyLC (iiCurrencyId),
input ? (icCurrencyCode),
output vcDiffFormat (ocFormatedAmount),
output viFcReturnSuper (oiReturnStatus)) in TConCheck>
put stream sSumOut unformatted trim(#T-1'Total BC':50(330832880)T-1#) at 1
vcGLBalanceFormat to 30
vcSubBalanceFormat to 60
vcDiffFormat to 90 skip.
<M-87 run RoundAmount
(input-output vdSumGLBalanceCC (bdUnroundedAmount),
input viCompanyCC (iiCurrencyId),
input ? (icCurrencyCode),
output vcGLBalanceFormat (ocFormatedAmount),
output viFcReturnSuper (oiReturnStatus)) in TConCheck>
<M-21 run RoundAmount
(input-output vdSumSubBalanceCC (bdUnroundedAmount),
input viCompanyCC (iiCurrencyId),
input ? (icCurrencyCode),
output vcSubBalanceFormat (ocFormatedAmount),
output viFcReturnSuper (oiReturnStatus)) in TConCheck>
assign vdDiff = vdSumGLBalanceCC - vdSumSubBalanceCC.
<M-10 run RoundAmount
(input-output vdDiff (bdUnroundedAmount),
input viCompanyCC (iiCurrencyId),
input ? (icCurrencyCode),
output vcDiffFormat (ocFormatedAmount),
output viFcReturnSuper (oiReturnStatus)) in TConCheck>
put stream sSumOut unformatted #T-57'Total SC':50(601335079)T-57# at 1
vcGLBalanceFormat to 30
vcSubBalanceFormat to 60
vcDiffFormat to 90 skip.
end.
put stream sSumOut unformatted "____________________________________________________________" skip.
output stream sSumOut close.
if oiReturnStatus = -98
then assign oiReturnStatus = 0.