Description
Assign the Statutory Currency amounts based on the amounts in TC and the exchange rate between TC and the newly entered SC - update History.
Parameters
| icDomainCode | input | character | |
| ocErrorMessage | output | character | |
| oiReturnStatus | output | integer | Return status of the method. |
Internal usage
QadFinancials
program code (program1/tsetstatutorycurrency.p)
assign oiReturnStatus = -98
viLocalReturnStatus = 0.
HISTBLOCK:
do:
/* Get DomainProperty */
find Domains where
Domains.DomainCode = icDomainCode
no-lock no-error.
if not available Domains
then do:
assign viLocalReturnStatus = -800
ocErrorMessage = substitute("Unable to get the Domain with code &1", icDomainCode).
leave HISTBLOCK.
end. /* if not available Domains */
find DomainProperty where
DomainProperty.Domain_ID = Domains.Domain_ID
no-lock no-error.
if not available DomainProperty
then do:
assign viLocalReturnStatus = -801
ocErrorMessage = substitute("Unable to get the DomainProperty for code &1", icDomainCode).
leave HISTBLOCK.
end. /* if not available DomainProperty */
/* ================================= */
/* Go through each company of domain */
/* ================================= */
for each company where
company.domain_id = DomainProperty.Domain_ID
no-lock:
/* ===================================== */
/* STEP 1: PostingHist */
/* 1a- Reset all CC fields */
/* 1b- Movements fields */
/* 1c- Balance fields */
/* ===================================== */
assign viPL# = 0
viPS# = 0.
/* ====================================== */
/* 1a- Reset all CC values in PostingHist */
/* ====================================== */
for each PostingHist of company exclusive-lock Transaction:
assign PostingHist.PostingHistMovemntCreditCC = 0
PostingHist.PostingHistMovemntDebitCC = 0
PostingHist.PostingHistBalanceCreditCC = 0
PostingHist.PostingHistBalanceDebitCC = 0
PostingHist.PostingHistYTDCreditCC = 0
PostingHist.PostingHistYTDDebitCC = 0.
end. /* for each PostingHist of company Transaction: */
/* === Logging === */
output to value(vcLogFile) append.
put unformatted
STRING(TIME,"HH:MM:SS")
" HIST"
" PostingHist of entity " company.CompanyCode " was reset."
skip.
output close.
/* ====================================================================== */
/* 1b- Read the transactions and update the CC movement fields */
/* ====================================================================== */
for each CompanySharedSet of Company no-lock,
each SharedSet of CompanySharedSet no-lock where
SharedSet.SharedSetType = {&SHAREDSETTYPE-JOURNAL},
each Journal of SharedSet no-lock,
each Posting no-lock where
Posting.Company_ID = Company.Company_ID and
Posting.Journal_ID = Journal.Journal_ID:
POSTINGLINEBLOCK:
for each PostingLine where
PostingLine.Posting_ID = Posting.Posting_ID
no-lock
transaction:
assign viPL# = viPL# + 1.
/* ============================= */
/* Get the PostingSAFCombined_ID */
/* ============================= */
assign viSAF1ID = 0
viSAF2ID = 0
viSAF3ID = 0
viSAF4ID = 0
viSAF5ID = 0
viSAFStructureID = 0
vcSAFParentType = ''
viPostingSafCombinedID = 0.
for each PostingSaf where
PostingSaf.PostingLine_ID = PostingLine.PostingLine_ID
no-lock:
assign viPS# = viPS# + 1.
if PostingSaf.PostingSafInputSequence = 1 then assign viSAF1ID = PostingSaf.Saf_ID.
if PostingSaf.PostingSafInputSequence = 2 then assign viSAF2ID = PostingSaf.Saf_ID.
if PostingSaf.PostingSafInputSequence = 3 then assign viSAF3ID = PostingSaf.Saf_ID.
if PostingSaf.PostingSafInputSequence = 4 then assign viSAF4ID = PostingSaf.Saf_ID.
if PostingSaf.PostingSafInputSequence = 5 then assign viSAF5ID = PostingSaf.Saf_ID.
assign viSAFStructureID = PostingSaf.SafStructure_ID
vcSAFParentType = PostingSaf.PostingSafParentType.
end. /* for each PostingSaf where */
if vcSAFParentType <> ''
then do:
if vcSAFParentType = {&POSTINGSAFPARENTTYPE-GL}
then do :
for each PostingSafCombined where
PostingSafCombined.GLSafStructure_ID = viSAFStructureID and
PostingSafCombined.GL1Saf_ID = viSAF1ID and
PostingSafCombined.GL2Saf_ID = viSAF2ID and
PostingSafCombined.GL3Saf_ID = viSAF3ID and
PostingSafCombined.GL4Saf_ID = viSAF4ID and
PostingSafCombined.GL5Saf_ID = viSAF5ID
no-lock :
assign viPostingSafCombinedID = PostingSafCombined.PostingSafCombined_ID.
leave.
end. /* for each PostingSafCombined */
end.
else
if vcSAFParentType = {&POSTINGSAFPARENTTYPE-COSTCENTRE}
then do :
for each PostingSafCombined where
PostingSafCombined.CostCentreSafStructure_ID = viSAFStructureID and
PostingSafCombined.CostCentre1Saf_ID = viSAF1ID and
PostingSafCombined.CostCentre2Saf_ID = viSAF2ID and
PostingSafCombined.CostCentre3Saf_ID = viSAF3ID and
PostingSafCombined.CostCentre4Saf_ID = viSAF4ID and
PostingSafCombined.CostCentre5Saf_ID = viSAF5ID
no-lock :
assign viPostingSafCombinedID = PostingSafCombined.PostingSafCombined_ID.
leave.
end. /* for each PostingSafCombined */
end.
if vcSAFParentType = {&POSTINGSAFPARENTTYPE-PROJECT}
then do :
for each PostingSafCombined where
PostingSafCombined.ProjectSafStructure_ID = viSAFStructureID and
PostingSafCombined.Project1Saf_ID = viSAF1ID and
PostingSafCombined.Project2Saf_ID = viSAF2ID and
PostingSafCombined.Project3Saf_ID = viSAF3ID and
PostingSafCombined.Project4Saf_ID = viSAF4ID and
PostingSafCombined.Project5Saf_ID = viSAF5ID
no-lock :
assign viPostingSafCombinedID = PostingSafCombined.PostingSafCombined_ID.
leave.
end. /* for each PostingSafCombined */
end.
if viPostingSafCombinedID = 0
then do:
assign viLocalReturnStatus = -801
ocErrorMessage = "Unable to get PostingSafCombined for PostingLineID: " + string (PostingLine.PostingLine_ID).
leave HISTBLOCK.
end.
end. /* if vcSAFParentType <> '' */
/* ============================================================================================ */
/* Update the corresponding PostingHist. We use a for each although only 1 record will be found */
/* first we try to replace any ? by 0. If not found we use the ? */
/* ============================================================================================ */
assign viPostingHistCnt = 0.
for each PostingHist where
PostingHist.Company_ID = Posting.Company_ID and
PostingHist.Journal_ID = Posting.Journal_ID and
PostingHist.Layer_ID = Journal.Layer_ID and
PostingHist.GL_ID = PostingLine.GL_ID and
PostingHist.Division_ID = (if PostingLine.Division_ID = ?
then 0
else PostingLine.Division_ID) and
PostingHist.Project_ID = (if PostingLine.Project_ID = ?
then 0
else PostingLine.Project_ID) and
PostingHist.CostCentre_ID = (if PostingLine.CostCentre_ID = ?
then 0
else PostingLine.CostCentre_ID) and
PostingHist.Currency_ID = PostingLine.Currency_ID and
PostingHist.IntercoBusinessRelation_ID = (if PostingLine.IntercoBusinessRelation_ID = ?
then 0
else PostingLine.IntercoBusinessRelation_ID) and
PostingHist.PostingSafCombined_ID = viPostingSafCombinedID and
PostingHist.PostingHistYearPeriodFrom = Posting.PostingYearPeriod
exclusive-lock:
assign viPostingHistCnt = viPostingHistCnt + 1
PostingHist.PostingHistMovemntDebitCC = PostingHist.PostingHistMovemntDebitCC + PostingLine.PostingLineDebitCC
PostingHist.PostingHistMovemntCreditCC = PostingHist.PostingHistMovemntCreditCC + PostingLine.PostingLineCreditCC.
/* We should find exactly one PostingHist record. To be sure, we leave the for each so max 1 recor is updated */
leave.
end. /* for each PostingHist */
if viPostingHistCnt <> 1
then do:
for each PostingHist where
PostingHist.Company_ID = Posting.Company_ID and
PostingHist.Journal_ID = Posting.Journal_ID and
PostingHist.Layer_ID = Journal.Layer_ID and
PostingHist.GL_ID = PostingLine.GL_ID and
PostingHist.Division_ID = PostingLine.Division_ID and
PostingHist.Project_ID = PostingLine.Project_ID and
PostingHist.CostCentre_ID = PostingLine.CostCentre_ID and
PostingHist.Currency_ID = PostingLine.Currency_ID and
PostingHist.IntercoBusinessRelation_ID = PostingLine.IntercoBusinessRelation_ID and
PostingHist.PostingSafCombined_ID = viPostingSafCombinedID and
PostingHist.PostingHistYearPeriodFrom = Posting.PostingYearPeriod
exclusive-lock:
assign viPostingHistCnt = viPostingHistCnt + 1
PostingHist.PostingHistMovemntDebitCC = PostingHist.PostingHistMovemntDebitCC + PostingLine.PostingLineDebitCC
PostingHist.PostingHistMovemntCreditCC = PostingHist.PostingHistMovemntCreditCC + PostingLine.PostingLineCreditCC.
/* We should find exactly one PostingHist record. To be sure, we leave the for each so max 1 recor is updated */
leave.
end. /* for each PostingHist */
end. /* viPostingHistCnt <> 1 */
if viPostingHistCnt <> 1
then do:
find GL of PostingLine no-lock.
put unformatted
STRING(TIME,"HH:MM:SS")
" ** ERROR: No PostingHist found for account: " GL.GLCode "."
" Please Rebuild account balances of this account."
" PostingLine_ID=" PostingLine.PostingLine_ID
skip.
output close.
end.
end. /* for each PostingLine */
end. /* for each Posting */
/* === Logging === */
output to value(vcLogFile) append.
put unformatted
STRING(TIME,"HH:MM:SS")
" HIST"
" Movements fields of PostingHist of entity " company.CompanyCode " were updated."
" (PostingLine#=" viPL# " PostingSAF#=" viPS# ")"
skip.
output close.
/* ====================================== */
/* 1c- Calculate the Balances */
/* ====================================== */
for each PostingHist of company
exclusive-lock transaction
break by PostingHist.Company_ID
by PostingHist.Journal_ID
by PostingHist.Layer_ID
by PostingHist.GL_ID
by PostingHist.Division_ID
by PostingHist.Project_ID
by PostingHist.CostCentre_ID
by PostingHist.Currency_ID
by PostingHist.IntercoBusinessRelation_ID
by PostingHist.PostingSafCombined_ID
by PostingHist.PostingHistYearPeriodFrom:
/* Reset when we start a new combination */
if first-of (PostingHist.PostingSafCombined_ID)
then do:
assign vdBalanceCreditCC = 0
vdBalanceDebitCC = 0
vdYTDCreditCC = 0
vdYTDDebitCC = 0
viCachedYear = 0.
end.
/* Reset the YTD values when a new year has started */
if viCachedYear <> 0 and integer(truncate(PostingHist.PostingHistYearPeriodFrom / 100, 0)) > viCachedYear
then assign vdYTDCreditCC = 0
vdYTDDebitCC = 0.
/* Keep it all in local data items */
assign vdBalanceDebitCC = vdBalanceDebitCC + PostingHist.PostingHistMovemntDebitCC
vdBalanceCreditCC = vdBalanceCreditCC + PostingHist.PostingHistMovemntCreditCC
vdYTDDebitCC = vdYTDDebitCC + PostingHist.PostingHistMovemntDebitCC
vdYTDCreditCC = vdYTDCreditCC + PostingHist.PostingHistMovemntCreditCC
viCachedYear = integer(truncate(PostingHist.PostingHistYearPeriodFrom / 100, 0)).
/* assign the DB values now */
assign PostingHist.PostingHistBalanceCreditCC = vdBalanceCreditCC
PostingHist.PostingHistBalanceDebitCC = vdBalanceDebitCC
PostingHist.PostingHistYTDCreditCC = vdYTDCreditCC
PostingHist.PostingHistYTDDebitCC = vdYTDDebitCC.
end. /* for each PostingHist */
/* === Logging === */
output to value(vcLogFile) append.
put unformatted
STRING(TIME,"HH:MM:SS")
" HIST"
" Balance fields of PostingHist of entity " company.CompanyCode " were updated."
skip .
output close.
/* ======== */
/* CBalance */
/* ======== */
for each CBalance where
CBalance.Company_ID = Company.Company_ID exclusive-lock Transaction:
/* CHist */
for each CHist where
CHist.CBalance_ID = CBalance.CBalance_ID
exclusive-lock:
/* Clear CC amounts */
assign CHist.CHistBalanceCC = 0
CHist.CHistGrossCredNoteCC = 0
CHist.CHistGrossInvoiceCC = 0
CHist.CHistMovementCreditCC = 0
CHist.CHistMovementDebitCC = 0
CHist.CHistNettoCredNoteCC = 0
CHist.CHistNettoInvoiceCC = 0.
for each CInvoiceMovement where
CInvoiceMovement.Company_ID = CHist.Company_ID and
CInvoiceMovement.Creditor_ID = CHist.Creditor_ID and
CInvoiceMovement.CInvoiceMovementYearPeriod >= CHist.CHistYearPeriodFrom and
CInvoiceMovement.CInvoiceMovementYearPeriod <= CHist.CHistYearPeriodTill
no-lock:
find CInvoice where
CInvoice.CInvoice_ID = CInvoiceMovement.CInvoice_ID and
CInvoice.Journal_ID = CHist.Journal_ID
no-lock no-error.
if not available CInvoice
then next.
find PostingLine where
PostingLine.PostingLine_ID = CInvoiceMovement.PostingLine_ID and
PostingLine.Currency_ID = CHist.Currency_ID and
PostingLine.GL_ID = CHist.GL_ID and
PostingLine.Division_ID = CHist.Division_ID and
PostingLine.Project_ID = CHist.Project_ID and
PostingLine.CostCentre_ID = CHist.CostCentre_ID
no-lock no-error.
if available PostingLine
then do:
/* Update Movement and Balances */
assign CHist.CHistBalanceCC = CHist.CHistBalanceCC + PostingLine.PostingLineDebitCC - PostingLine.PostingLineCreditCC
CHist.CHistMovementDebitCC = CHist.CHistMovementDebitCC + PostingLine.PostingLineDebitCC
CHist.CHistMovementCreditCC = CHist.CHistMovementCreditCC + PostingLine.PostingLineCreditCC
CBalance.CBalanceCC = CBalance.CBalanceCC + PostingLine.PostingLineDebitCC - PostingLine.PostingLineCreditCC.
/* Update Gross and Net Amounts */
if CInvoiceMovement.CInvoiceMovementType = {&MOVEMENTTYPE-INITIAL} and
(CInvoice.CInvoiceType = {&INVOICETYPE-INVOICE} or CInvoice.CInvoiceType = {&INVOICETYPE-CREDITNOTE})
then do:
if CInvoice.CInvoicePostingdate = CInvoice.CInvoiceTaxPointDate
then do :
assign vdVatCreditCC = CInvoice.CInvoiceVatCreditCC
vdVatDebitCC = CInvoice.CInvoiceVatDebitCC.
end.
else do:
/* Get Exchange Rates */
<M-1 run GetExchangeRate
(input CInvoice.CInvoiceCurrency_ID (iiFromCurrencyID),
input viNewSCID (iiToCurrencyID),
input viSCExchangeRateTypeID (iiExchangeRateTypeID),
input CInvoice.CInvoiceTaxPointDate (itDate),
input viExchangeRateSharedSetID (iiSharedSetID),
output vdRate (odExchangeRate),
output vdScale (odExchangeRateScale),
output ocErrorMessage (ocErrorMessage),
output viFcReturnSuper (oiReturnStatus)) in TSetStatutoryCurrency>
if viFcReturnSuper <> 0
then assign viLocalReturnStatus = viFcReturnSuper.
if viFcReturnSuper < 0
then leave HISTBLOCK.
assign vdVatCreditCC = CInvoice.CInvoiceVatCreditTC * vdRate * vdScale
vdVatDebitCC = CInvoice.CInvoiceVatDebitTC * vdRate * vdScale.
end.
if CInvoice.CInvoiceType = {&INVOICETYPE-INVOICE}
then assign CHist.CHistGrossInvoiceCC = CHist.CHistGrossInvoiceCC + PostingLine.PostingLineCreditCC
CHist.CHistNettoInvoiceCC = CHist.CHistNettoInvoiceCC + PostingLine.PostingLineCreditCC - vdVatCreditCC.
if CInvoice.CInvoiceType = {&INVOICETYPE-CREDITNOTE}
then assign CHist.CHistGrossCredNoteCC = CHist.CHistGrossCredNoteCC + PostingLine.PostingLineDebitCC
CHist.CHistNettoCredNoteCC = CHist.CHistNettoCredNoteCC + PostingLine.PostingLineDebitCC + vdVatDebitCC.
end. /* if CInvoiceMovement.CInvoiceMovementType = {&MOVEMENTTYPE-INITIAL} */
end. /* if available PostingLine */
end. /* CInvoiceMovement */
end. /* CHist */
end. /* CBalance */
/* === Logging === */
output to value(vcLogFile) append.
put unformatted
STRING(TIME,"HH:MM:SS")
" HIST"
" CHist and CBalance of entity " company.CompanyCode " were updated."
skip .
output close.
/* ======== */
/* DBalance */
/* ======== */
for each DBalance where
DBalance.Company_ID = Company.Company_ID exclusive-lock Transaction:
/* DHist */
for each DHist where
DHist.DBalance_ID = DBalance.DBalance_ID
exclusive-lock:
/* Clear CC amounts */
assign DHist.DHistBalanceCC = 0
DHist.DHistGrossCredNoteCC = 0
DHist.DHistGrossInvoiceCC = 0
DHist.DHistMovementCreditCC = 0
DHist.DHistMovementDebitCC = 0
DHist.DHistNettoCredNoteCC = 0
DHist.DHistNettoInvoiceCC = 0.
for each DInvoiceMovement where
DInvoiceMovement.Company_ID = DHist.Company_ID and
DInvoiceMovement.Debtor_ID = DHist.Debtor_ID and
DInvoiceMovement.DInvoiceMovementYearPeriod >= DHist.DHistYearPeriodFrom and
DInvoiceMovement.DInvoiceMovementYearPeriod <= DHist.DHistYearPeriodTill
no-lock:
find DInvoice where
DInvoice.DInvoice_ID = DInvoiceMovement.DInvoice_ID and
DInvoice.Journal_ID = DHist.Journal_ID
no-lock no-error.
if not available DInvoice
then next.
find PostingLine where
PostingLine.PostingLine_ID = DInvoiceMovement.PostingLine_ID and
PostingLine.Currency_ID = DHist.Currency_ID and
PostingLine.GL_ID = DHist.GL_ID and
PostingLine.Division_ID = DHist.Division_ID and
PostingLine.Project_ID = DHist.Project_ID and
PostingLine.CostCentre_ID = DHist.CostCentre_ID
no-lock no-error.
if available PostingLine
then do:
/* Update Movement and Balances */
assign DHist.DHistBalanceCC = DHist.DHistBalanceCC + PostingLine.PostingLineDebitCC - PostingLine.PostingLineCreditCC
DHist.DHistMovementDebitCC = DHist.DHistMovementDebitCC + PostingLine.PostingLineDebitCC
DHist.DHistMovementCreditCC = DHist.DHistMovementCreditCC + PostingLine.PostingLineCreditCC
DBalance.DBalanceCC = DBalance.DBalanceCC + PostingLine.PostingLineDebitCC - PostingLine.PostingLineCreditCC.
/* Update Gross and Net Amounts */
if DInvoiceMovement.DInvoiceMovementType = {&MOVEMENTTYPE-INITIAL} and
(DInvoice.DInvoiceType = {&INVOICETYPE-INVOICE} or DInvoice.DInvoiceType = {&INVOICETYPE-CREDITNOTE})
then do:
if DInvoice.DInvoicePostingdate = DInvoice.DInvoiceTaxPointDate
then do :
assign vdVatCreditCC = DInvoice.DInvoiceVatCreditCC
vdVatDebitCC = DInvoice.DInvoiceVatDebitCC.
end.
else do:
/* Get Exchange Rates */
<M-2 run GetExchangeRate
(input DInvoice.DInvoiceCurrency_ID (iiFromCurrencyID),
input viNewSCID (iiToCurrencyID),
input viSCExchangeRateTypeID (iiExchangeRateTypeID),
input DInvoice.DInvoiceTaxPointDate (itDate),
input viExchangeRateSharedSetID (iiSharedSetID),
output vdRate (odExchangeRate),
output vdScale (odExchangeRateScale),
output ocErrorMessage (ocErrorMessage),
output viFcReturnSuper (oiReturnStatus)) in TSetStatutoryCurrency>
if viFcReturnSuper <> 0
then assign viLocalReturnStatus = viFcReturnSuper.
if viFcReturnSuper < 0
then leave HISTBLOCK.
assign vdVatCreditCC = DInvoice.DInvoiceVatCreditTC * vdRate * vdScale
vdVatDebitCC = DInvoice.DInvoiceVatDebitTC * vdRate * vdScale.
end.
if DInvoice.DInvoiceType = {&INVOICETYPE-INVOICE}
then assign DHist.DHistGrossInvoiceCC = DHist.DHistGrossInvoiceCC + PostingLine.PostingLineCreditCC
DHist.DHistNettoInvoiceCC = DHist.DHistNettoInvoiceCC + PostingLine.PostingLineCreditCC - vdVatCreditCC.
if DInvoice.DInvoiceType = {&INVOICETYPE-CREDITNOTE}
then assign DHist.DHistGrossCredNoteCC = DHist.DHistGrossCredNoteCC + PostingLine.PostingLineDebitCC
DHist.DHistNettoCredNoteCC = DHist.DHistNettoCredNoteCC + PostingLine.PostingLineDebitCC + vdVatDebitCC.
end. /* if DInvoiceMovement.DInvoiceMovementType = {&MOVEMENTTYPE-INITIAL} */
end. /* if available PostingLine */
end. /* DInvoiceMovement */
end. /* DHist */
end. /* DBalance */
/* === Logging === */
output to value(vcLogFile) append.
put unformatted
STRING(TIME,"HH:MM:SS")
" HIST"
" DHist and DBalance of entity " company.CompanyCode " were updated."
skip .
output close.
end. /* for each company where */
end. /* HISTBLOCK */
assign oiReturnStatus = viLocalReturnStatus.