Description
Bank entry validation check
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 | |
| ihTranslationHandle | input | handle | Translation Handle. |
| oiReturnStatus | output | integer | Return status of the method. |
Internal usage
QadFinancials
program code (program1/tconcheck.p)
/* ================================================================================= */
/* This is a support utility that checks if invoices linked tot the BankstateAlloc */
/* are adjusted in the database */
/* We found customer installations where this was not the case when using CrossCy */
/* Accounts */
/* ================================================================================= */
assign oiReturnStatus = -98
viSessionID = iiSessionID
ocConCheckLineDetResult = {&CONCHECKRESULT-PASSED}.
define buffer bCompany for Company.
define buffer bPostingLine for PostingLine.
assign vdStartEtime = etime(no)
otConCheckLineDetStartDate = now
oiConCheckLineDetStartTime = time
oiErrorNumbers = 0
ocConCheckLineDetVersion = icAppVersion + '.1'.
output stream sTechOut TO value(icFile) append.
put stream sTechOut unformatted ' 'skip.
put stream sTechOut unformatted '##### Banking Entry #####' 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:
if iiCompanyID = ? or
iiCompanyID = 0
then do:
assign ocConCheckLineDetResult = {&CONCHECKRESULT-FAILED}.
PUT STREAM sTechOut unformatted "**Error** Input parameter CompanyID is null." skip.
LEAVE MAIN_BLOCK.
end.
if iiPeriodID = ? or
iiPeriodID = 0
then do:
assign ocConCheckLineDetResult = {&CONCHECKRESULT-FAILED}.
PUT STREAM sTechOut unformatted "**Error** Input parameter PeriodID is null." skip.
LEAVE MAIN_BLOCK.
end.
/* empty summary output first */
empty temp-table tBankEntrySum.
empty temp-table tBankEntrySumLine.
for each BankstateAlloc no-lock,
first Bankstateline of BankstateAlloc no-lock,
first Bankstate of Bankstateline
where BankState.Period_ID = iiPeriodID and BankState.Company_ID = iiCompanyID no-lock,
Company of Bankstate no-lock,
GL of Bankstate no-lock,
first banknumber where ParentObject_ID = gl.gl_id no-lock:
find first tBankEntrySumLine where
tBankEntrySumLine.tcBEGLCode = GL.GLCode and
tBankEntrySumLine.tiBankStateLineId = BankStateLine.BankstateLine_id no-lock no-error.
if not available tBankEntrySumLine
then do:
create tBankEntrySumLine.
assign tBankEntrySumLine.tcBEGLCode = GL.GLCode
tBankEntrySumLine.tiBankStateLineId = BankStateLine.BankstateLine_id
tBankEntrySumLine.tlIsBankStateLineOk = yes.
end.
/* ================================================= */
/* AP Invoice */
/* ================================================= */
if BankstateAlloc.cinvoice_id <> 0
then do :
find first cinvoice of BankstateAlloc no-lock no-error.
if cinvoice.cinvoicetype = 'prepayment' then next.
/* ============================================== */
/* Check if we find a movement of the same amount */
/* If not, we have a problem ! */
/* ============================================== */
assign vlExactFound = no
vlFound = no
vcKeyMov = ''.
for each cinvoicemovement of cinvoice no-lock where
cinvoicemovement.cinvoicemovementtype = 'movement',
first PostingLine of CinvoiceMovement no-lock,
first posting of postingline no-lock,
first Journal of Posting no-lock,
first bCompany of Posting no-lock,
first Currency of PostingLine no-lock:
find first tBankEntrySum where tBankEntrySum.tcBEGLCode = GL.GLCode and tBankEntrySum.tcBECurr = CurrencyCode no-error.
if not available tBankEntrySum
then do:
create tBankEntrySum.
assign tBankEntrySum.tcBECurr = CurrencyCode
tBankEntrySum.tcBEDirection = 'AP'
tBankEntrySum.tcBEGLCode = GL.GLCode
tBankEntrySum.tcBEGLDesc = GL.GLDescription
tBankEntrySum.tcBEGLNo = BankNumber.BankNumber
tBankEntrySum.tdBETotalAmountAlloc = 0
tBankEntrySum.tdBETotalAmountPosting = 0
tBankEntrySum.tdTotalAmountDiff = 0
tBankEntrySum.tiBETotalLines = 0
tBankEntrySum.tiBETotalLinesErr = 0.
end.
assign vlPostingFound = no.
if(BankStateAlloc.Posting_ID = Posting.Posting_ID or BankStateAlloc.Posting_ID = PostingLine.CrossCompanyPosting_ID)
then do:
assign vlPostingFound = yes.
end.
else do:
for each bPostingLine of Posting no-lock where bPostingLine.crosscompanyposting_id = BankStateAlloc.Posting_ID:
assign vlPostingFound = yes.
end.
end.
if vlPostingFound = yes
then assign tBankEntrySum.tdBETotalAmountAlloc = tBankEntrySum.tdBETotalAmountAlloc + bankstateallocamounttc
tBankEntrySum.tdBETotalAmountPosting = tBankEntrySum.tdBETotalAmountPosting + postingline.postinglinedebittc + postingline.postinglinecredittc
tBankEntrySum.tiBETotalLines = tBankEntrySum.tiBETotalLines + 1.
if BankState.BankStatePostingDate = Posting.PostingDate and
(postingline.postinglinedebittc = bankstateallocamounttc or
postingline.postinglinecredittc = bankstateallocamounttc)
then assign vlExactFound = yes
vcKeyMov = bCompany.CompanyCode + "/" +
string (Posting.PostingYear) + "/" +
Journal.JournalCode + "/" +
string (Posting.PostingVoucher).
else
if (postingline.postinglinedebittc = bankstateallocamounttc or
postingline.postinglinecredittc = bankstateallocamounttc)
then assign vlFound = yes
vcKeyMov = bCompany.CompanyCode + "/" +
string (Posting.PostingYear) + "/" +
Journal.JournalCode + "/" +
string (Posting.PostingVoucher).
end. /* for each cinvoicemovement of cinvoice no-lock where */
/* ============================================== */
/* Report it */
/* ============================================== */
if not vlExactFound and not vlFound
then do:
find first cinvoicemovement of CInvoice where
cinvoicemovement.cinvoicemovementtype = 'INITIAL' no-lock no-error.
find first PostingLine of cinvoicemovement no-lock no-error.
find first Posting of PostingLine no-lock no-error.
find first Journal of Posting no-lock no-error.
find first bCompany of Posting no-lock no-error.
assign vdTotCI = vdTotCI + bankstateallocamounttc.
assign oiErrorNumbers = oiErrorNumbers + 1.
assign tBankEntrySum.tiBETotalLinesErr = tBankEntrySum.tiBETotalLinesErr + 1.
assign tBankEntrySumLine.tlIsBankStateLineOk = no.
put stream sTechOut unformatted
now
'> AP Invoice;'
company.companycode '/'
bankstate.bankstateyear '/'
bankstate.bankstateNumber '/'
bankstateline.bankstatelinenumber ';'
BankState.BankStatePostingDate ';'
bankstatealloc.bankstateallocamounttc ';'
bcompany.companycode '/'
posting.postingyearperiod '/'
journal.journalcode '/'
cinvoice.CInvoiceVoucher ';'
cinvoice.cinvoicereference ';'
vlExactFound ';'
vlFound ';'
(if Company.CompanyCode = bCompany.CompanyCode then ';' else 'CrossCy Trx;')
vcKeyMov ';'
skip.
assign ocConCheckLineDetResult = {&CONCHECKRESULT-FAILED}.
end. /* if not vlExactFound and not vlFound */
end. /* if BankstateAlloc.cinvoice_id <> 0 */
/* ================================================= */
/* AR Invoice */
/* ================================================= */
if bankstatealloc.dinvoice_id <> 0
then do :
find first Dinvoice of Bankstatealloc no-lock no-error.
if Dinvoice.Dinvoicetype = 'prepayment' then next.
if Dinvoice.Dinvoicetype = 'DEDUCTION' then next.
/* ============================================== */
/* Check if we find a movement of the same amount */
/* If not, we have a problem ! */
/* ============================================== */
assign vlExactFound = no
vlFound = no
vcKeyMov = ''.
for each Dinvoicemovement of Dinvoice no-lock where
Dinvoicemovement.Dinvoicemovementtype = 'movement',
first PostingLine of DinvoiceMovement no-lock,
first posting of postingline no-lock,
first journal of posting no-lock,
first bcompany of posting no-lock,
first Currency of PostingLine no-lock:
find first tBankEntrySum where tBankEntrySum.tcBEGLCode = GL.GLCode and tBankEntrySum.tcBECurr = CurrencyCode no-lock no-error.
if not available tBankEntrySum
then do:
create tBankEntrySum.
assign tBankEntrySum.tcBECurr = CurrencyCode
tBankEntrySum.tcBEDirection = 'AP'
tBankEntrySum.tcBEGLCode = GL.GLCode
tBankEntrySum.tcBEGLDesc = GL.GLDescription
tBankEntrySum.tcBEGLNo = BankNumber.BankNumber
tBankEntrySum.tdBETotalAmountAlloc = 0
tBankEntrySum.tdBETotalAmountPosting = 0
tBankEntrySum.tdTotalAmountDiff = 0
tBankEntrySum.tiBETotalLines = 0
tBankEntrySum.tiBETotalLinesErr = 0.
end.
assign vlPostingFound = no.
if(BankStateAlloc.Posting_ID = Posting.Posting_ID or BankStateAlloc.Posting_ID = PostingLine.CrossCompanyPosting_ID)
then do:
assign vlPostingFound = yes.
end.
else do:
for each bPostingLine of Posting no-lock where bPostingLine.crosscompanyposting_id = BankStateAlloc.Posting_ID:
assign vlPostingFound = yes.
end.
end.
if vlPostingFound = yes
then assign tBankEntrySum.tdBETotalAmountAlloc = tBankEntrySum.tdBETotalAmountAlloc + bankstateallocamounttc
tBankEntrySum.tdBETotalAmountPosting = tBankEntrySum.tdBETotalAmountPosting + postingline.postinglinedebittc + postingline.postinglinecredittc
tBankEntrySum.tiBETotalLines = tBankEntrySum.tiBETotalLines + 1.
if BankState.BankStatePostingDate = Posting.PostingDate and
(postingline.postinglinedebittc = bankstateallocamounttc or
postingline.postinglinecredittc = bankstateallocamounttc)
then assign vlExactFound = yes
vcKeyMov = bCompany.CompanyCode + "/" +
string (Posting.PostingYear) + "/" +
Journal.JournalCode + "/" +
string (Posting.PostingVoucher).
else
if (postingline.postinglinedebittc = bankstateallocamounttc or
postingline.postinglinecredittc = bankstateallocamounttc)
then assign vlFound = yes
vcKeyMov = bCompany.CompanyCode + "/" +
string (Posting.PostingYear) + "/" +
Journal.JournalCode + "/" +
string (Posting.PostingVoucher).
end. /* for each Dinvoicemovement of Dinvoice no-lock where */
/* ============================================== */
/* Report it */
/* ============================================== */
if not vlExactFound and not vlFound
then do:
assign vdTotDI = vdTotDI + bankstateallocamounttc.
find first dinvoicemovement of DInvoice where
dinvoicemovement.dinvoicemovementtype = 'INITIAL' no-lock no-error.
find first PostingLine of dinvoicemovement no-lock no-error.
find first Posting of PostingLine no-lock no-error.
find first Journal of Posting no-lock no-error.
find first bCompany of Posting no-lock no-error.
assign
oiErrorNumbers = oiErrorNumbers + 1
tBankEntrySum.tiBETotalLinesErr = tBankEntrySum.tiBETotalLinesErr + 1
tBankEntrySumLine.tlIsBankStateLineOk = no.
put stream sTechOut unformatted
now
'> AR Invoice;'
company.companycode '/'
bankstate.bankstateyear '/'
bankstate.bankstateNumber '/'
bankstateline.bankstatelinenumber ';'
BankState.BankStatePostingDate ';'
bankstatealloc.bankstateallocamounttc ';'
bcompany.companycode '/'
posting.postingyearperiod '/'
journal.journalcode '/'
DInvoice.DinvoiceVoucher ';'
dinvoice.dinvoiceDItext ';'
vlExactFound ';'
vlFound ';'
(if Company.CompanyCode = bCompany.CompanyCode then ';' else 'CrossCy Trx;')
vcKeyMov ';'
skip.
assign ocConCheckLineDetResult = {&CONCHECKRESULT-FAILED}.
end. /* if not vlExactFound and not vlFound */
end. /* if bankstatealloc.dinvoice_id <> 0 */
end. /* for each BankstateAlloc no-lock, */
end. /* Main_BLOCK */
if oiErrorNumbers > 0
then assign ocConCheckLineDetResult = {&CONCHECKRESULT-FAILED}.
else if viCount > 0
then assign ocConCheckLineDetResult = {&CONCHECKRESULT-WARNING}.
else assign ocConCheckLineDetResult = {&CONCHECKRESULT-PASSED}.
assign oiConCheckLineDetDuration = etime - vdStartEtime.
/* to format the total duration time */
assign
viMilliSecs = oiConCheckLineDetDuration
viSecs = TRUNCATE (oiConCheckLineDetDuration / 1000, 0)
viMilliSecs = viMilliSecs mod 1000
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.
output stream sTechOut TO value(icSumFile) append.
put stream sTechOut unformatted ' 'skip.
put stream sTechOut unformatted trim(#T-98'Banking Entry':40(4731)T-98#) skip.
put stream sTechOut unformatted '-------------' skip.
put stream sTechOut unformatted trim(#T-5'Execution Result':255(510391963)T-5#) + ': ' + ocConCheckLineDetResult skip.
if oiErrorNumbers > 0
then do:
put stream sTechOut unformatted
trim(#T-41'Total number of errors':100(376712702)T-41#)
+ ': ' + string(oiErrorNumbers) skip.
put stream sTechOut unformatted '(' trim(#T-82'Please see details in the file':150(806040074)T-82#) ' ' icFile ')' skip.
end.
for each tBankEntrySum
break by tBankEntrySum.tcBEGLCode by tBankEntrySum.tcBECurr:
assign tBankEntrySum.tdTotalAmountDiff = tBankEntrySum.tdBETotalAmountPosting - tBankEntrySum.tdBETotalAmountAlloc.
if first-of(tBankEntrySum.tcBEGLCode)
then do:
assign viGLAllocatedTotal = 0
viGLAllocatedTotalFail = 0.
for each tBankEntrySumLine where tBankEntrySumLine.tcBEGLCode = tBankEntrySum.tcBEGLCode:
assign viGLAllocatedTotal = viGLAllocatedTotal + 1.
if tBankEntrySumLine.tlIsBankStateLineOk = no
then assign viGLAllocatedTotalFail = viGLAllocatedTotalFail + 1.
end.
put stream sTechOut unformatted ' 'skip.
/* Bank Account: 1100 #GL description #Bank Account No */
put stream sTechOut unformatted
trim(#T-76'Bank Account':30(888)T-76#) +
': ' + string(tBankEntrySum.tcBEGLCode) at 1
trim(#T-54'GL Description':40(1012)T-54#) +
': ' + tBankEntrySum.tcBEGLDesc at 30
trim(#T-26'Bank Account No':20(3942)T-26#) +
': ' + tBankEntrySum.tcBEGLNo at 80 skip.
/* Total Number of banking entry lines: # */
put stream sTechOut unformatted trim(#T-55'Total number of banking entry lines':150(334579066)T-55#) +
': ' + string(viGLAllocatedTotal) skip.
/* Number of banking entry lines with inconsistency: # */
put stream sTechOut unformatted trim(#T-45'Total number of banking entry lines with inconsistencies':150(515380374)T-45#) +
': ' + string(viGLAllocatedTotalFail) skip.
/* Currency | Total Amount Posted | Total Amount Allocated | Total Difference */
put stream sTechOut unformatted
trim(#T-71'Currency':20(220)T-71#) at 1
trim(#T-93'Total Amount Posted':255(712663609)T-93#) to 40
trim(#T-70'Total Amount Allocated':100(317822737)T-70#) to 75
trim(#T-7'Total Difference':100(98885305)T-7#) to 100
skip.
end.
<M-31 run RoundAmount
(input-output tBankEntrySum.tdBETotalAmountPosting (bdUnroundedAmount),
input ? (iiCurrencyId),
input tBankEntrySum.tcBECurr (icCurrencyCode),
output vcRoundAmount1 (ocFormatedAmount),
output viFcReturnSuper (oiReturnStatus)) in TConCheck>
<M-88 run RoundAmount
(input-output tBankEntrySum.tdBETotalAmountAlloc (bdUnroundedAmount),
input ? (iiCurrencyId),
input tBankEntrySum.tcBECurr (icCurrencyCode),
output vcRoundAmount2 (ocFormatedAmount),
output viFcReturnSuper (oiReturnStatus)) in TConCheck>
<M-44 run RoundAmount
(input-output tBankEntrySum.tdTotalAmountDiff (bdUnroundedAmount),
input ? (iiCurrencyId),
input tBankEntrySum.tcBECurr (icCurrencyCode),
output vcRoundAmount3 (ocFormatedAmount),
output viFcReturnSuper (oiReturnStatus)) in TConCheck>
put stream sTechOut unformatted tBankEntrySum.tcBECurr to 8
vcRoundAmount1 to 40
vcRoundAmount2 to 75
vcRoundAmount3 to 100 skip.
end. /* for each tBankEntrySum */
put stream sTechOut unformatted "____________________________________________________________" skip.
output stream sTechOut close.
if oiReturnStatus = -98
then assign oiReturnStatus = 0.