project QadFinancials > class TConCheck > method TechnicalValidation08DHistInvoice
Description
DHist reconciliation with DInvoice
Parameters
| iiCompanyID | input | integer | |
| iiPeriodID | input | integer | |
| iiSelectedYearPeriod | input | integer | |
| itFromDate | input | date | |
| itToDate | input | date | |
| oiErrorNumbers | output | integer | |
| oiReturnStatus | output | integer | Return status of the method. |
Internal usage
QadFinancials
program code (program1/tconcheck.p)
/* ================================================================================= */
/* This is the method for DHist reconciliation with DInvoice */
/* ================================================================================= */
assign oiReturnStatus = -98.
PUT STREAM sTechOut unformatted " " skip.
PUT STREAM sTechOut unformatted vcPrefix2 + "START Validation of DHist reconciliation with DInvoice (" + STRING (itFromDate) + ")" skip.
IPR_AR_Check_HISTWITHINVOICE:
do:
/* init variables */
define buffer bDHist for DHist.
empty temp-table tRebuild2.
/* ====================================================================================== */
/* Main Loop: go through currenty company code */
/* ====================================================================================== */
for each company no-lock where
Company.Company_ID = iiCompanyID :
/* Get sharedset of the company */
for each companysharedset no-lock where companysharedset.company_id = company.company_id,
each sharedset of companysharedset no-lock where Sharedset.SharedSetType = 'DEBTOR':
assign viDebtorSharedSetID = sharedset.sharedset_id.
end. /* end each companysharedset */
for each Debtor no-lock where Debtor.SharedSet_ID = viDebtorSharedSetID
by Debtor.DebtorCode :
if not can-find (first DHist where
DHist.Company_ID = Company.Company_ID and
DHist.Debtor_ID = Debtor.Debtor_ID and
DHist.dHistYearPeriodFrom >= iiSelectedYearPeriod) and
not can-find (first DInvoiceMovement where
DInvoiceMovement.Company_ID = Company.Company_ID and
DInvoiceMovement.Debtor_ID = Debtor.Debtor_ID and
DInvoiceMovement.DInvoiceMovementYearPeriod >= iiSelectedYearPeriod)
then next.
/* *************************************************************************** */
/* PART ONE : check on the movements of the current period */
/* *************************************************************************** */
for each Period of Company where
Period.Periodyearperiod >= iiSelectedYearPeriod and
Period.Periodyearperiod <= iiSelectedYearPeriod
no-lock
by Period.Company_ID
by Period.PeriodYearPeriod :
empty temp-table tDI.
/*************************************************************************************/
/* A.1. Go through all the PostingLines/PostingSaf of this Company/period */
/*************************************************************************************/
for each DInvoiceMovement no-lock where
DInvoiceMovement.Company_ID = Company.Company_ID and
DInvoiceMovement.Debtor_ID = Debtor.Debtor_ID and
DInvoiceMovement.DInvoiceMovementYearPeriod = Period.Periodyearperiod,
first PostingLine of DInvoiceMovement no-lock,
first Posting of PostingLine no-lock,
first Journal of Posting
no-lock :
find tDI where
tDI.tcCompanyCode = Company.CompanyCode and
tDI.tiYearPeriod = (if PostingLine.PostingYearPeriod = ? then 0 else PostingLine.PostingYearPeriod) and
tDI.tiJournal_ID = (if Posting.Journal_ID = ? then 0 else Posting.Journal_ID) and
tDI.tiGL_ID = (if PostingLine.GL_ID = ? then 0 else PostingLine.GL_ID) and
tDI.tiCurrency_ID = (if PostingLine.currency_Id = ? then 0 else PostingLine.currency_Id) and
tDI.tcDebtorCode = Debtor.DebtorCode and
tDI.tiProject_ID = (if PostingLine.Project_ID = ? then 0 else PostingLine.Project_ID) and
tDI.tiCostCentre_ID = (if PostingLine.Costcentre_ID = ? then 0 else PostingLine.Costcentre_ID) and
tDI.tiDivision_ID = (if PostingLine.Division_ID = ? then 0 else PostingLine.Division_ID)
no-error.
if not available tDI
then do:
create tDI.
assign
tDI.tcTYPE = 'TRX ':U
tDI.tcCompanyCode = Company.CompanyCode
tDI.tiYearPeriod = (if PostingLine.PostingYearPeriod = ? then 0 else PostingLine.PostingYearPeriod)
tDI.tiJournal_ID = (if Posting.Journal_ID = ? then 0 else Posting.Journal_ID)
tDI.tiGL_ID = (if PostingLine.GL_ID = ? then 0 else PostingLine.GL_ID)
tDI.tiCurrency_ID = (if PostingLine.currency_Id = ? then 0 else PostingLine.currency_Id)
tDI.tcDebtorCode = Debtor.DebtorCode
tDI.tiProject_ID = (if PostingLine.Project_ID = ? then 0 else PostingLine.Project_ID)
tDI.tiCostCentre_ID = (if PostingLine.Costcentre_ID = ? then 0 else PostingLine.Costcentre_ID)
tDI.tiDivision_ID = (if PostingLine.Division_ID = ? then 0 else PostingLine.Division_ID).
end.
assign tDI.tdTotalPostingLineCreditTC = tDI.tdTotalPostingLineCreditTC + Postingline.PostingLineCreditTC
tDI.tdTotalPostingLineCreditLC = tDI.tdTotalPostingLineCreditLC + Postingline.PostingLineCreditLC
tDI.tdTotalPostingLineCreditCC = tDI.tdTotalPostingLineCreditCC + Postingline.PostingLineCreditCC
tDI.tdTotalPostingLineDebitTC = tDI.tdTotalPostingLineDebitTC + Postingline.PostingLineDebitTC
tDI.tdTotalPostingLineDebitLC = tDI.tdTotalPostingLineDebitLC + Postingline.PostingLineDebitLC
tDI.tdTotalPostingLineDebitCC = tDI.tdTotalPostingLineDebitCC + Postingline.PostingLineDebitCC.
end. /* A.1 for each PostingLine */
/*************************************************************************************/
/* A.2. Go through all the History */
/*************************************************************************************/
for each DHist where
DHist.Company_ID = Company.Company_ID and
DHist.Debtor_ID = Debtor.Debtor_ID and
DHist.dHistYearPeriodFrom = Period.Periodyearperiod
no-lock:
find tDI where
tDI.tcCompanyCode = Company.CompanyCode and
tDI.tiYearPeriod = (if DHist.DHistYearPeriodFrom = ? then 0 else DHist.DHistYearPeriodFrom) and
tDI.tiJournal_ID = (if DHist.Journal_ID = ? then 0 else DHist.Journal_ID) and
tDI.tiCurrency_ID = (if DHist.currency_Id = ? then 0 else DHist.currency_Id) and
tDI.tiGL_ID = (if DHist.GL_ID = ? then 0 else DHist.GL_ID) and
tDI.tcDebtorCode = Debtor.DebtorCode and
tDI.tiProject_ID = (if DHist.Project_ID = ? then 0 else DHist.Project_ID) and
tDI.tiCostCentre_ID = (if DHist.Costcentre_ID = ? then 0 else DHist.Costcentre_ID) and
tDI.tiDivision_ID = (if DHist.Division_ID = ? then 0 else DHist.Division_ID)
no-error.
if not available tDI
then do :
create tDI.
assign
tDI.tcTYPE = 'HIST':U
tDI.tcCompanyCode = Company.CompanyCode
tDI.tiYearPeriod = (if DHist.DHistYearPeriodFrom = ? then 0 else DHist.DHistYearPeriodFrom)
tDI.tiJournal_ID = (if DHist.Journal_ID = ? then 0 else DHist.Journal_ID)
tDI.tiGL_ID = (if DHist.GL_ID = ? then 0 else DHist.GL_ID)
tDI.tiCurrency_ID = (if DHist.currency_Id = ? then 0 else DHist.currency_Id)
tDI.tcDebtorCode = Debtor.DebtorCode
tDI.tiProject_ID = (if DHist.Project_ID = ? then 0 else DHist.Project_ID)
tDI.tiCostCentre_ID = (if DHist.Costcentre_ID = ? then 0 else DHist.Costcentre_ID)
tDI.tiDivision_ID = (if DHist.Division_ID = ? then 0 else DHist.Division_ID).
end. /* if not available tdi */
else assign tDI.tcType = 'BOTH':U.
assign tDI.tdDHistMovementCreditTC = tDI.tdDHistMovementCreditTC + DHist.DHistMovementCreditTC
tDI.tdDHistMovementCreditLC = tDI.tdDHistMovementCreditLC + DHist.DHistMovementCreditLC
tDI.tdDHistMovementCreditCC = tDI.tdDHistMovementCreditCC + DHist.DHistMovementCreditCC
tDI.tdDHistMovementDebitTC = tDI.tdDHistMovementDebitTC + DHist.DHistMovementDebitTC
tDI.tdDHistMovementDebitLC = tDI.tdDHistMovementDebitLC + DHist.DHistMovementDebitLC
tDI.tdDHistMovementDebitCC = tDI.tdDHistMovementDebitCC + DHist.DHistMovementDebitCC.
end. /* A.2 for each dHist */
/*************************************************************************************/
/* A.3 See if there any differences and report it */
/*************************************************************************************/
for each tDI where
tDI.tcCompanyCode = Company.CompanyCode and
tDI.tcDebtorCode = Debtor.DebtorCode
by tDI.tcCompanyCode
by tDI.tcDebtorCode
by tDI.tiYearPeriod :
if tDI.tdTotalPostingLineCreditTC <> tDI.tdDHistMovementCreditTC or
tDI.tdTotalPostingLineCreditLC <> tDI.tdDHistMovementCreditLC or
tDI.tdTotalPostingLineCreditCC <> tDI.tdDHistMovementCreditCC or
tDI.tdTotalPostingLineDebitTC <> tDI.tdDHistMovementDebitTC or
tDI.tdTotalPostingLineDebitLC <> tDI.tdDHistMovementDebitLC or
tDI.tdTotalPostingLineDebitCC <> tDI.tdDHistMovementDebitCC
then do :
/* Create indication rebuild is needed */
find tRebuild2 where
tRebuild2.tcCompanyCode = tDI.tcCompanyCode and
tRebuild2.tcDebtorCode = tDI.tcDebtorCode
no-error.
if not available tRebuild2
then do:
create tRebuild2.
assign tRebuild2.tcCompanyCode = tDI.tcCompanyCode
tRebuild2.tcDebtorCode = tDI.tcDebtorCode
tRebuild2.tiYearPeriodFrom = tDI.tiYearPeriod.
end.
assign oiErrorNumbers = oiErrorNumbers + 1.
/* log error */
PUT STREAM sTechOut unformatted vcPrefix
'ERROR MOVEMENT '
tDI.tcTYPE ' * '
tDI.tcCompanyCode ' * '
tDI.tcDebtorCode format 'x(10)' ' * '
tDI.tiYearPeriod ' * '
'JN:' tDI.tiJournal_ID format '9999999999' ' '
'CU:' tDI.tiCurrency_ID format '9999999999' ' '
'GL:' tDI.tiGL_ID format '9999999999' ' '
'PR:' tDI.tiProject_ID format '9999999999' ' '
'CC:' tDI.tiCostCentre_ID format '9999999999' ' '
'DI:' tDI.tiDivision_ID format '9999999999' ' '
tDI.tdTotalPostingLineDebitTC <> tDI.tdDHistMovementDebitTC format 'yes/no' ' '
tDI.tdTotalPostingLineDebitLC <> tDI.tdDHistMovementDebitLC format 'yes/no' ' '
tDI.tdTotalPostingLineDebitCC <> tDI.tdDHistMovementDebitCC format 'yes/no' ' * '
tDI.tdTotalPostingLineCreditTC <> tDI.tdDHistMovementCreditTC format 'yes/no' ' '
tDI.tdTotalPostingLineCreditLC <> tDI.tdDHistMovementCreditLC format 'yes/no' ' '
tDI.tdTotalPostingLineCreditCC <> tDI.tdDHistMovementCreditCC format 'yes/no' ' '
tDI.tdTotalPostingLineDebitTC ' ' tDI.tdDHistMovementDebitTC ' * '
tDI.tdTotalPostingLineDebitLC ' ' tDI.tdDHistMovementDebitLC ' * '
tDI.tdTotalPostingLineDebitCC ' ' tDI.tdDHistMovementDebitCC ' * '
tDI.tdTotalPostingLineCreditTC ' ' tDI.tdDHistMovementCreditTC ' * '
tDI.tdTotalPostingLineCreditLC ' ' tDI.tdDHistMovementCreditLC ' * '
tDI.tdTotalPostingLineCreditCC ' ' tDI.tdDHistMovementCreditCC ' * '
skip.
end. /* corruption */
end. /* A.3 for each tDI */
end. /* for each Period */
/* *************************************************************************** */
/* PART TWO : check on the balance fields of the current period */
/* *************************************************************************** */
if not can-find (first tRebuild2 where
tRebuild2.tcCompanyCode = Company.CompanyCode and
tRebuild2.tcDebtorCode = Debtor.DebtorCode)
then do:
for each DHist of Company no-lock where
DHist.Debtor_ID = Debtor.Debtor_ID and
DHist.DHistYearPeriodFrom >= iiSelectedYearPeriod
break by DHist.Company_ID
by DHist.Journal_ID
by DHist.Debtor_ID
by DHist.GL_ID
by DHist.Division_ID
by DHist.CostCentre_ID
by DHist.Project_ID
by DHist.Currency_ID
by DHist.DHistYearPeriodFrom:
/* ======================================================== */
/* Initialize when a new combiation is started */
/* ======================================================== */
if first-of (DHist.Currency_ID)
then do:
assign vlSkipCombination = no
vcNewCombination = ">":U.
if iiSelectedYearPeriod <> 0
then find last bDHist where
bDHist.Company_ID = DHist.Company_ID and
bDHist.Debtor_ID = DHist.Debtor_ID and
bDHist.Journal_ID = DHist.Journal_ID and
bDHist.GL_ID = DHist.GL_ID and
bDHist.Currency_ID = DHist.Currency_ID and
bDHist.Division_ID = DHist.Division_ID and
bDHist.Project_ID = DHist.Project_ID and
bDHist.CostCentre_ID = DHist.CostCentre_ID and
bDHist.DHistYearPeriodFrom < DHist.DHistYearPeriodFrom
no-lock no-error.
if iiSelectedYearPeriod <> 0 and available bDHist
then assign vdDHistBalanceCC = bDHist.DHistBalanceCC
vdDHistBalanceLC = bDHist.DHistBalanceLC
vdDHistBalanceTC = bDHist.DHistBalanceTC
vdDHistYearPeriodFromPREV = bDHist.DHistYearPeriodFrom
vdDHistYearPeriodTillPREV = bDHist.DHistYearPeriodTill
vdDHistYearFromPREV = bDHist.DHistYearPeriodFrom / 100.
else assign vdDHistBalanceCC = 0
vdDHistBalanceLC = 0
vdDHistBalanceTC = 0
vdDHistYearPeriodFromPREV = 0
vdDHistYearPeriodTillPREV = 0
vdDHistYearFromPREV = 0.
end. /* first-of */
if vlSkipCombination
then next.
/* ======================================================== */
/* Recalculate the Balances */
/* ======================================================== */
assign vdDHistBalanceTC = vdDHistBalanceTC + DHist.DHistMovementDebitTC - DHist.DHistMovementCreditTC
vdDHistBalanceLC = vdDHistBalanceLC + DHist.DHistMovementDebitLC - DHist.DHistMovementCreditLC
vdDHistBalanceCC = vdDHistBalanceCC + DHist.DHistMovementDebitCC - DHist.DHistMovementCreditCC.
/* ======================================================== */
/* Check if the Balance is OK */
/* ======================================================== */
assign vlBalanceIncorrect = vdDHistBalanceTC <> DHist.DHistBalanceTC or
vdDHistBalanceLC <> DHist.DHistBalanceLC or
vdDHistBalanceCC <> DHist.DHistBalanceCC.
if vlBalanceIncorrect
then do :
find journal of DHist no-lock no-error.
find tRebuild2 where
tRebuild2.tcCompanyCode = Company.CompanyCode and
tRebuild2.tcDebtorCode = Debtor.DebtorCode
no-error.
if not available tRebuild2
then do:
create tREbuild2.
assign tRebuild2.tcCompanyCode = Company.CompanyCode
tRebuild2.tcDebtorCode = Debtor.DebtorCode
tRebuild2.tiYearPeriodFrom = vdDHistYearPeriodFromPREV.
end.
assign vlSkipCombination = yes.
assign oiErrorNumbers = oiErrorNumbers + 1.
/* log error */
PUT STREAM sTechOut unformatted vcPrefix
'ERROR '
'BALANCE '
vcNewCombination format 'x' ' '
vcSelCompanyCode ' * '
Debtor.DebtorCode format 'x(10)' ' * '
DHist.DHistYearPeriodFrom ' * '
DHist.DHistYearPeriodTill ' * '
' JN:' DHist.Journal_ID format '9999999999'
' GL:' DHist.GL_ID format '9999999999'
' DI:' DHist.Division_ID format '9999999999'
' CC:' DHist.CostCentre_ID format '9999999999'
' PR:' DHist.Project_ID format '9999999999'
' CU:' DHist.Currency_ID format '9999999999'
'Error in Balance fields '
vlBalanceIncorrect
skip.
if vcNewCombination <> '' then assign vcNewCombination = '':U.
end.
/* ======================================================== */
/* Check if the Period has no holes */
/* ======================================================== */
if vdDHistYearPeriodTillPREV <> 0 and DHist.DHistYearPeriodFrom <> vdDHistYearPeriodTillPREV + 1
then do :
find journal of DHist no-lock no-error.
find tRebuild2 where
tRebuild2.tcCompanyCode = Company.CompanyCode and
tRebuild2.tcDebtorCode = Debtor.DebtorCode
no-error.
if not available tRebuild2
then do:
create tRebuild2.
assign tRebuild2.tcCompanyCode = Company.CompanyCode
tRebuild2.tcDebtorCode = Debtor.DebtorCode
tRebuild2.tiYearPeriodFrom = vdDHistYearPeriodFromPREV.
end.
assign vlSkipCombination = yes.
assign oiErrorNumbers = oiErrorNumbers + 1.
/* log error */
PUT STREAM sTechOut unformatted vcPrefix
'ERROR '
'PERIOD '
vcNewCombination format 'x' ' '
vcSelCompanyCode ' * '
Debtor.DebtorCode format 'x(10)' ' * '
DHist.DHistYearPeriodFrom ' * '
DHist.DHistYearPeriodTill ' * '
' JN:' DHist.Journal_ID format '9999999999'
' GL:' DHist.GL_ID format '9999999999'
' DI:' DHist.Division_ID format '9999999999'
' CC:' DHist.CostCentre_ID format '9999999999'
' PR:' DHist.Project_ID format '9999999999'
' CU:' DHist.Currency_ID format '9999999999'
'Error: Period not in sequence.' ' ' vdDHistYearPeriodTillPREV
skip.
if vcNewCombination <> '' then assign vcNewCombination = '':U.
end.
/* ======================================================== */
/* Check if the Last records has 999999 as till period */
/* ======================================================== */
if last-of (DHist.Currency_ID) and
DHist.DHistYearPeriodTill <> 999999
then do :
find journal of DHist no-lock no-error.
find tRebuild2 where
tRebuild2.tcCompanyCode = Company.CompanyCode and
tRebuild2.tcDebtorCode = Debtor.DebtorCode
no-error.
if not available tRebuild2
then do:
create tREbuild2.
assign tRebuild2.tcCompanyCode = Company.CompanyCode
tRebuild2.tcDebtorCode = Debtor.DebtorCode
tRebuild2.tiYearPeriodFrom = vdDHistYearPeriodFromPREV.
end.
assign vlSkipCombination = yes.
assign oiErrorNumbers = oiErrorNumbers + 1.
/* log error */
PUT STREAM sTechOut unformatted vcPrefix
'ERROR '
' END-RECORD '
vcNewCombination format 'x' ' '
vcSelCompanyCode ' * '
Debtor.DebtorCode format 'x(10)' ' * '
DHist.DHistYearPeriodFrom ' * '
DHist.DHistYearPeriodTill ' * '
' JN:' DHist.Journal_ID format '9999999999'
' GL:' DHist.GL_ID format '9999999999'
' DI:' DHist.Division_ID format '9999999999'
' CC:' DHist.CostCentre_ID format '9999999999'
' PR:' DHist.Project_ID format '9999999999'
' CU:' DHist.Currency_ID format '9999999999'
'Error: 999999 record is missing.'
skip.
if vcNewCombination <> '' then assign vcNewCombination = '':U.
end.
assign vdDHistYearPeriodFromPREV = DHist.DHistYearPeriodFrom
vdDHistYearPeriodTillPREV = DHist.DHistYearPeriodTill
vdDHistYearFromPREV = vdDHistYearPeriodFromPREV / 100.
end. /* for each dHist */
end. /* NOT CAN FIND ttRebuild2 */
end. /* FOR EACH DEBTOR */
end. /* FOR EACH COMPANY, GL */
end.
PUT STREAM sTechOut unformatted vcPrefix2 + "END Validation DHist reconciliation with DInvoice." skip.
if oiReturnStatus = -98
then assign oiReturnStatus = 0.