project QadFinancials > class TConCheck > method TechnicalValidation04CHistInvoice
Description
CHist reconciliation with CInvoice
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 CHist reconciliation with CInvoice */
/* ================================================================================= */
assign oiReturnStatus = -98.
/* open logfile stream */
PUT STREAM sTechOut unformatted " " skip.
PUT STREAM sTechOut unformatted vcPrefix2 + "START Validation of CHist reconciliation with CInvoice (" + STRING (itFromDate) + ")" skip.
IPR_AP_Check_HISTWITHINVOICE:
do:
/* init variables */
define buffer bCHist for CHist.
empty temp-table tRebuild.
/* ====================================================================================== */
/* 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 = 'Creditor':
assign viCreditorSharedSetID = sharedset.sharedset_id.
end. /* end each companysharedset */
for each Creditor where Creditor.sharedset_id = viCreditorSharedSetID
no-lock
break by Creditor.CreditorCode:
if not can-find (first CHist where
CHist.Company_ID = Company.Company_ID and
CHist.Creditor_ID = Creditor.Creditor_ID and
CHist.CHistYearPeriodFrom >= iiSelectedYearPeriod) and
not can-find (first CInvoiceMovement where
CInvoiceMovement.Company_ID = Company.Company_ID and
CInvoiceMovement.Creditor_ID = Creditor.Creditor_ID and
CInvoiceMovement.CInvoiceMovementYearPeriod >= 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 tCI.
/*************************************************************************************/
/* A.1. Go through all the PostingLines/PostingSaf of this Company/period */
/*************************************************************************************/
for each CInvoiceMovement no-lock where
CInvoiceMovement.Company_ID = Company.Company_ID and
CInvoiceMovement.Creditor_ID = Creditor.Creditor_ID and
CInvoiceMovement.CInvoiceMovementYearPeriod = Period.Periodyearperiod,
first PostingLine of CInvoiceMovement no-lock,
first Posting of PostingLine no-lock,
first Journal of Posting
no-lock :
find tCI where
tCI.tcCompanyCode = Company.CompanyCode and
tCI.tiYearPeriod = (if PostingLine.PostingYearPeriod = ? then 0 else PostingLine.PostingYearPeriod) and
tCI.tiJournal_ID = (if Posting.Journal_ID = ? then 0 else Posting.Journal_ID) and
tCI.tiGL_ID = (if PostingLine.GL_ID = ? then 0 else PostingLine.GL_ID) and
tCI.tiCurrency_ID = (if PostingLine.currency_Id = ? then 0 else PostingLine.currency_Id) and
tCI.tcCreditorCode = Creditor.CreditorCode and
tCI.tiProject_ID = (if PostingLine.Project_ID = ? then 0 else PostingLine.Project_ID) and
tCI.tiCostCentre_ID = (if PostingLine.Costcentre_ID = ? then 0 else PostingLine.Costcentre_ID) and
tCI.tiDivision_ID = (if PostingLine.Division_ID = ? then 0 else PostingLine.Division_ID)
no-error.
if not available tCI
then do:
create tCI.
assign
tCI.tcTYPE = 'TRX ':U
tCI.tcCompanyCode = Company.CompanyCode
tCI.tiYearPeriod = (if PostingLine.PostingYearPeriod = ? then 0 else PostingLine.PostingYearPeriod)
tCI.tiJournal_ID = (if Posting.Journal_ID = ? then 0 else Posting.Journal_ID)
tCI.tiGL_ID = (if PostingLine.GL_ID = ? then 0 else PostingLine.GL_ID)
tCI.tiCurrency_ID = (if PostingLine.currency_Id = ? then 0 else PostingLine.currency_Id)
tCI.tcCreditorCode = Creditor.CreditorCode
tCI.tiProject_ID = (if PostingLine.Project_ID = ? then 0 else PostingLine.Project_ID)
tCI.tiCostCentre_ID = (if PostingLine.Costcentre_ID = ? then 0 else PostingLine.Costcentre_ID)
tCI.tiDivision_ID = (if PostingLine.Division_ID = ? then 0 else PostingLine.Division_ID).
end.
assign tCI.tdTotalPostingLineCreditTC = tCI.tdTotalPostingLineCreditTC + Postingline.PostingLineCreditTC
tCI.tdTotalPostingLineCreditLC = tCI.tdTotalPostingLineCreditLC + Postingline.PostingLineCreditLC
tCI.tdTotalPostingLineCreditCC = tCI.tdTotalPostingLineCreditCC + Postingline.PostingLineCreditCC
tCI.tdTotalPostingLineDebitTC = tCI.tdTotalPostingLineDebitTC + Postingline.PostingLineDebitTC
tCI.tdTotalPostingLineDebitLC = tCI.tdTotalPostingLineDebitLC + Postingline.PostingLineDebitLC
tCI.tdTotalPostingLineDebitCC = tCI.tdTotalPostingLineDebitCC + Postingline.PostingLineDebitCC.
end. /* A.1 for each PostingLine */
/*************************************************************************************/
/* A.2. Go through all the History */
/*************************************************************************************/
for each CHist where
CHist.Company_ID = Company.Company_ID and
CHist.Creditor_ID = Creditor.Creditor_ID and
CHist.CHistYearPeriodFrom = Period.Periodyearperiod
no-lock:
find tCI where
tCI.tcCompanyCode = Company.CompanyCode and
tCI.tiYearPeriod = (if CHist.CHistYearPeriodFrom = ? then 0 else CHist.CHistYearPeriodFrom) and
tCI.tiJournal_ID = (if CHist.Journal_ID = ? then 0 else CHist.Journal_ID) and
tCI.tiCurrency_ID = (if CHist.currency_Id = ? then 0 else CHist.currency_Id) and
tCI.tiGL_ID = (if CHist.GL_ID = ? then 0 else CHist.GL_ID) and
tCI.tcCreditorCode = Creditor.CreditorCode and
tCI.tiProject_ID = (if CHist.Project_ID = ? then 0 else CHist.Project_ID) and
tCI.tiCostCentre_ID = (if CHist.Costcentre_ID = ? then 0 else CHist.Costcentre_ID) and
tCI.tiDivision_ID = (if CHist.Division_ID = ? then 0 else CHist.Division_ID)
no-error.
if not available tCI
then do :
create tCI.
assign
tCI.tcTYPE = 'HIST':U
tCI.tcCompanyCode = Company.CompanyCode
tCI.tiYearPeriod = (if CHist.CHistYearPeriodFrom = ? then 0 else CHist.CHistYearPeriodFrom)
tCI.tiJournal_ID = (if CHist.Journal_ID = ? then 0 else CHist.Journal_ID)
tCI.tiGL_ID = (if CHist.GL_ID = ? then 0 else CHist.GL_ID)
tCI.tiCurrency_ID = (if CHist.currency_Id = ? then 0 else CHist.currency_Id)
tCI.tcCreditorCode = Creditor.CreditorCode
tCI.tiProject_ID = (if CHist.Project_ID = ? then 0 else CHist.Project_ID)
tCI.tiCostCentre_ID = (if CHist.Costcentre_ID = ? then 0 else CHist.Costcentre_ID)
tCI.tiDivision_ID = (if CHist.Division_ID = ? then 0 else CHist.Division_ID).
end. /* if not available ttCI */
else assign tCI.tcType = 'BOTH':U.
assign tCI.tdCHistMovementCreditTC = tCI.tdCHistMovementCreditTC + CHist.CHistMovementCreditTC
tCI.tdCHistMovementCreditLC = tCI.tdCHistMovementCreditLC + CHist.CHistMovementCreditLC
tCI.tdCHistMovementCreditCC = tCI.tdCHistMovementCreditCC + CHist.CHistMovementCreditCC
tCI.tdCHistMovementDebitTC = tCI.tdCHistMovementDebitTC + CHist.CHistMovementDebitTC
tCI.tdCHistMovementDebitLC = tCI.tdCHistMovementDebitLC + CHist.CHistMovementDebitLC
tCI.tdCHistMovementDebitCC = tCI.tdCHistMovementDebitCC + CHist.CHistMovementDebitCC.
end. /* A.2 for each CHist */
/*************************************************************************************/
/* A.3 See if there any differences and report it */
/*************************************************************************************/
for each tCI where
tCI.tcCompanyCode = Company.CompanyCode and
tCI.tcCreditorCode = Creditor.CreditorCode
by tCI.tcCompanyCode
by tCI.tcCreditorCode
by tCI.tiYearPeriod :
if tCI.tdTotalPostingLineCreditTC <> tCI.tdCHistMovementCreditTC or
tCI.tdTotalPostingLineCreditLC <> tCI.tdCHistMovementCreditLC or
tCI.tdTotalPostingLineCreditCC <> tCI.tdCHistMovementCreditCC or
tCI.tdTotalPostingLineDebitTC <> tCI.tdCHistMovementDebitTC or
tCI.tdTotalPostingLineDebitLC <> tCI.tdCHistMovementDebitLC or
tCI.tdTotalPostingLineDebitCC <> tCI.tdCHistMovementDebitCC
then do :
/* Create indication rebuild is needed */
find tRebuild where
tRebuild.tcCompanyCode = tCI.tcCompanyCode and
tRebuild.tcCreditorCode = tCI.tcCreditorCode
no-error.
if not available tRebuild
then do:
create tREbuild.
assign tRebuild.tcCompanyCode = tCI.tcCompanyCode
tRebuild.tcCreditorCode = tCI.tcCreditorCode
tRebuild.tiYearPeriodFrom = tCI.tiYearPeriod.
end.
assign oiErrorNumbers = oiErrorNumbers + 1.
/* log error */
PUT STREAM sTechOut unformatted vcPrefix
'ERROR MOVEMENT '
tCI.tcTYPE ' * '
tCI.tcCompanyCode ' * '
tCI.tcCreditorCode format 'x(10)' ' * '
tCI.tiYearPeriod ' * '
'JN:' tCI.tiJournal_ID format '9999999999' ' '
'CU:' tCI.tiCurrency_ID format '9999999999' ' '
'GL:' tCI.tiGL_ID format '9999999999' ' '
'PR:' tCI.tiProject_ID format '9999999999' ' '
'CC:' tCI.tiCostCentre_ID format '9999999999' ' '
'DI:' tCI.tiDivision_ID format '9999999999' ' '
tCI.tdTotalPostingLineDebitTC <> tCI.tdCHistMovementDebitTC format 'yes/no' ' '
tCI.tdTotalPostingLineDebitLC <> tCI.tdCHistMovementDebitLC format 'yes/no' ' '
tCI.tdTotalPostingLineDebitCC <> tCI.tdCHistMovementDebitCC format 'yes/no' ' * '
tCI.tdTotalPostingLineCreditTC <> tCI.tdCHistMovementCreditTC format 'yes/no' ' '
tCI.tdTotalPostingLineCreditLC <> tCI.tdCHistMovementCreditLC format 'yes/no' ' '
tCI.tdTotalPostingLineCreditCC <> tCI.tdCHistMovementCreditCC format 'yes/no' ' '
tCI.tdTotalPostingLineDebitTC ' ' tCI.tdCHistMovementDebitTC ' * '
tCI.tdTotalPostingLineDebitLC ' ' tCI.tdCHistMovementDebitLC ' * '
tCI.tdTotalPostingLineDebitCC ' ' tCI.tdCHistMovementDebitCC ' * '
tCI.tdTotalPostingLineCreditTC ' ' tCI.tdCHistMovementCreditTC ' * '
tCI.tdTotalPostingLineCreditLC ' ' tCI.tdCHistMovementCreditLC ' * '
tCI.tdTotalPostingLineCreditCC ' ' tCI.tdCHistMovementCreditCC ' * '
skip.
end. /* corruption */
end. /* A.3 for each ttCI */
end. /* for each Period */
/* *************************************************************************** */
/* PART TWO : check on the balance fields of the current period */
/* *************************************************************************** */
if not can-find (first tRebuild where
tRebuild.tcCompanyCode = Company.CompanyCode and
tRebuild.tcCreditorCode = Creditor.CreditorCode)
then do:
for each CHist of Company no-lock where
CHist.Creditor_ID = Creditor.Creditor_ID and
CHist.CHistYearPeriodFrom >= iiSelectedYearPeriod
break by CHist.Company_ID
by CHist.Journal_ID
by CHist.Creditor_ID
by CHist.GL_ID
by CHist.Division_ID
by CHist.CostCentre_ID
by CHist.Project_ID
by CHist.Currency_ID
by CHist.CHistYearPeriodFrom:
/* ======================================================== */
/* Initialize when a new combiation is started */
/* ======================================================== */
if first-of (CHist.Currency_ID)
then do:
assign vlSkipCombination = no
vcNewCombination = ">":U.
if iiSelectedYearPeriod <> 0
then find last bCHist where
bCHist.Company_ID = CHist.Company_ID and
bCHist.Creditor_ID = CHist.Creditor_ID and
bCHist.Journal_ID = CHist.Journal_ID and
bCHist.GL_ID = CHist.GL_ID and
bCHist.Currency_ID = CHist.Currency_ID and
bCHist.Division_ID = CHist.Division_ID and
bCHist.Project_ID = CHist.Project_ID and
bCHist.CostCentre_ID = CHist.CostCentre_ID and
bCHist.CHistYearPeriodFrom < CHist.CHistYearPeriodFrom
no-lock no-error.
if iiSelectedYearPeriod <> 0 and available bCHist
then assign vdCHistBalanceCC = bCHist.CHistBalanceCC
vdCHistBalanceLC = bCHist.CHistBalanceLC
vdCHistBalanceTC = bCHist.CHistBalanceTC
vdCHistYearPeriodFromPREV = bCHist.CHistYearPeriodFrom
vdCHistYearPeriodTillPREV = bCHist.CHistYearPeriodTill
vdCHistYearFromPREV = bCHist.CHistYearPeriodFrom / 100.
else assign vdCHistBalanceCC = 0
vdCHistBalanceLC = 0
vdCHistBalanceTC = 0
vdCHistYearPeriodFromPREV = 0
vdCHistYearPeriodTillPREV = 0
vdCHistYearFromPREV = 0.
end. /* first-of */
if vlSkipCombination
then next.
/* ======================================================== */
/* Recalculate the Balances */
/* ======================================================== */
assign vdCHistBalanceTC = vdCHistBalanceTC + CHist.CHistMovementDebitTC - CHist.CHistMovementCreditTC
vdCHistBalanceLC = vdCHistBalanceLC + CHist.CHistMovementDebitLC - CHist.CHistMovementCreditLC
vdCHistBalanceCC = vdCHistBalanceCC + CHist.CHistMovementDebitCC - CHist.CHistMovementCreditCC.
/* ======================================================== */
/* Check if the Balance is OK */
/* ======================================================== */
assign vlBalanceIncorrect = vdCHistBalanceTC <> CHist.CHistBalanceTC or
vdCHistBalanceLC <> CHist.CHistBalanceLC or
vdCHistBalanceCC <> CHist.CHistBalanceCC.
if vlBalanceIncorrect
then do :
find journal of CHist no-lock no-error.
find tRebuild where
tRebuild.tcCompanyCode = Company.CompanyCode and
tRebuild.tcCreditorCode = Creditor.CreditorCode
no-error.
if not available tRebuild
then do:
create tREbuild.
assign tRebuild.tcCompanyCode = Company.CompanyCode
tRebuild.tcCreditorCode = Creditor.CreditorCode
tRebuild.tiYearPeriodFrom = vdCHistYearPeriodFromPREV.
end.
assign vlSkipCombination = yes.
assign oiErrorNumbers = oiErrorNumbers + 1.
/* log error */
PUT STREAM sTechOut unformatted vcPrefix
'ERROR '
'BALANCE '
vcNewCombination format 'x' ' '
vcSelCompanyCode ' * '
Creditor.CreditorCode format 'x(10)' ' * '
CHist.CHistYearPeriodFrom ' * '
CHist.CHistYearPeriodTill ' * '
' JN:' CHist.Journal_ID format '9999999999'
' GL:' CHist.GL_ID format '9999999999'
' DI:' CHist.Division_ID format '9999999999'
' CC:' CHist.CostCentre_ID format '9999999999'
' PR:' CHist.Project_ID format '9999999999'
' CU:' CHist.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 vdCHistYearPeriodTillPREV <> 0 and CHist.CHistYearPeriodFrom <> vdCHistYearPeriodTillPREV + 1
then do :
find journal of CHist no-lock no-error.
find tRebuild where
tRebuild.tcCompanyCode = Company.CompanyCode and
tRebuild.tcCreditorCode = Creditor.CreditorCode
no-error.
if not available tRebuild
then do:
create tRebuild.
assign tRebuild.tcCompanyCode = Company.CompanyCode
tRebuild.tcCreditorCode = Creditor.CreditorCode
tRebuild.tiYearPeriodFrom = vdCHistYearPeriodFromPREV.
end.
assign vlSkipCombination = yes.
assign oiErrorNumbers = oiErrorNumbers + 1.
/* log error */
PUT STREAM sTechOut unformatted vcPrefix
'ERROR '
'PERIOD '
vcNewCombination format 'x' ' '
vcSelCompanyCode ' * '
Creditor.CreditorCode format 'x(10)' ' * '
CHist.CHistYearPeriodFrom ' * '
CHist.CHistYearPeriodTill ' * '
' JN:' CHist.Journal_ID format '9999999999'
' GL:' CHist.GL_ID format '9999999999'
' DI:' CHist.Division_ID format '9999999999'
' CC:' CHist.CostCentre_ID format '9999999999'
' PR:' CHist.Project_ID format '9999999999'
' CU:' CHist.Currency_ID format '9999999999'
'Error: Period not in sequence.' ' ' vdCHistYearPeriodTillPREV
skip.
if vcNewCombination <> '' then assign vcNewCombination = '':U.
end.
/* ======================================================== */
/* Check if the Last records has 999999 as till period */
/* ======================================================== */
if last-of (CHist.Currency_ID) and
CHist.CHistYearPeriodTill <> 999999
then do :
find journal of CHist no-lock no-error.
find tRebuild where
tRebuild.tcCompanyCode = Company.CompanyCode and
tRebuild.tcCreditorCode = Creditor.CreditorCode
no-error.
if not available tRebuild
then do:
create tREbuild.
assign tRebuild.tcCompanyCode = Company.CompanyCode
tRebuild.tcCreditorCode = Creditor.CreditorCode
tRebuild.tiYearPeriodFrom = vdCHistYearPeriodFromPREV.
end.
assign vlSkipCombination = yes.
assign oiErrorNumbers = oiErrorNumbers + 1.
/* log error */
PUT STREAM sTechOut unformatted vcPrefix
'ERROR '
' END-RECORD '
vcNewCombination format 'x' ' '
vcSelCompanyCode ' * '
Creditor.CreditorCode format 'x(10)' ' * '
CHist.CHistYearPeriodFrom ' * '
CHist.CHistYearPeriodTill ' * '
' JN:' CHist.Journal_ID format '9999999999'
' GL:' CHist.GL_ID format '9999999999'
' DI:' CHist.Division_ID format '9999999999'
' CC:' CHist.CostCentre_ID format '9999999999'
' PR:' CHist.Project_ID format '9999999999'
' CU:' CHist.Currency_ID format '9999999999'
'Error: 999999 record is missing.'
skip.
if vcNewCombination <> '' then assign vcNewCombination = '':U.
end.
assign vdCHistYearPeriodFromPREV = CHist.CHistYearPeriodFrom
vdCHistYearPeriodTillPREV = CHist.CHistYearPeriodTill
vdCHistYearFromPREV = vdCHistYearPeriodFromPREV / 100.
end. /* for each CHist */
end. /* NOT CAN FIND ttRebuild */
end. /* FOR EACH CREDITOR */
end. /* FOR EACH COMPANY, GL */
end.
PUT STREAM sTechOut unformatted vcPrefix2 + "END Validation CHist reconciliation with CInvoice." skip.
if oiReturnStatus = -98
then assign oiReturnStatus = 0.