project QadFinancials > class TFRWTreeView > method GetFRWTreeLevel2NodeCompleteAncodeElemdet
Parameters
tAnCode | input-output | temp-table | |
icTargetChart | input | character | |
oiReturnStatus | output | integer | Return status of the method. |
Internal usage
QadFinancials
program code (program1/tfrwtreeview.p)
define buffer bfrwancodeelemdet for frwancodeelemdet.
define buffer bjournal for journal.
for each tancode:
for each frwancode where frwancode.frwancodecode = tancode.tcancode and frwancode.frwancodefrwchartcode = ictargetchart no-lock,
each frwancodeelem of frwancode no-lock:
/*
if FRWAnCodeElem.FRWAnCodeElemCOAType = "Company" then assign vlNeedCompany = yes.
if FRWAnCodeElem.FRWAnCodeElemCOAType = "GL" then assign vlNeedGL = yes.
if FRWAnCodeElem.FRWAnCodeElemCOAType = "Division" then assign vlNeedDivision = yes.
if FRWAnCodeElem.FRWAnCodeElemCOAType = "CostCentre" then assign vlNeedCostCentre = yes.
if FRWAnCodeElem.FRWAnCodeElemCOAType = "Project" then assign vlNeedProject = yes.
if FRWAnCodeElem.FRWAnCodeElemCOAType = "Journal" then assign vlNeedJournal = yes.
if FRWAnCodeElem.FRWAnCodeElemCOAType = "Currency" then assign vlNeedCurrency = yes.
if FRWAnCodeElem.FRWAnCodeElemCOAType = "Layer" then assign vlNeedLayer = yes.
if FRWAnCodeElem.FRWAnCodeElemCOAType = "Interco" then assign vlNeedInterco = yes.
if FRWAnCodeElem.FRWAnCodeElemCOAType begins "SAF" then assign vlNeedSAF = yes.
*/
if FRWAnCodeElem.FRWAnCodeElemCOAType = "Saf1" then assign viSAFConceptID = viSAF1ConceptID.
if FRWAnCodeElem.FRWAnCodeElemCOAType = "Saf2" then assign viSAFConceptID = viSAF2ConceptID.
if FRWAnCodeElem.FRWAnCodeElemCOAType = "Saf3" then assign viSAFConceptID = viSAF3ConceptID.
if FRWAnCodeElem.FRWAnCodeElemCOAType = "Saf4" then assign viSAFConceptID = viSAF4ConceptID.
if FRWAnCodeElem.FRWAnCodeElemCOAType = "Saf5" then assign viSAFConceptID = viSAF5ConceptID.
if FRWAnCodeElem.FRWAnCodeElemCOAType = "Saf6" then assign viSAFConceptID = viSAF6ConceptID.
if FRWAnCodeElem.FRWAnCodeElemCOAType = "Saf7" then assign viSAFConceptID = viSAF7ConceptID.
if FRWAnCodeElem.FRWAnCodeElemCOAType = "Saf8" then assign viSAFConceptID = viSAF8ConceptID.
if FRWAnCodeElem.FRWAnCodeElemCOAType = "Saf9" then assign viSAFConceptID = viSAF9ConceptID.
if FRWAnCodeElem.FRWAnCodeElemCOAType = "Saf10" then assign viSAFConceptID = viSAF10ConceptID.
if frwancodeelem.frwancodeelemmask <> "" then
do:
if frwancodeelem.frwancodeelemcoatype = "company" then
do:
for each company where company.companycode begins frwancodeelem.frwancodeelemmask no-lock,
each frwcompany where frwcompany.frwcompanyfrwchartcode = ictargetchart and
frwcompany.frwcompanycode = company.companycode no-lock:
if not can-find(first frwancodeelemdet of frwancodeelem where frwancodeelemdet.frwancodeelemdetvalue = company.company_id) then
do:
create frwancodeelemdet.
assign
frwancodeelemdet.frwancodeelemdet_id = next-value(objectnumber)
frwancodeelemdet.frwancodeelem_id = frwancodeelem.frwancodeelem_id
frwancodeelemdet.frwancodeelemdetvalue = company.company_id
frwancodeelemdet.frwancodeelemdetcode = company.companycode.
end.
end.
end. /* if "Company" */
if frwancodeelem.frwancodeelemcoatype = "gl" then
do:
for each gl where gl.sharedset_id = vitargglsetid and
gl.glcode begins frwancodeelem.frwancodeelemmask no-lock:
if not can-find(first frwancodeelemdet of frwancodeelem where frwancodeelemdet.frwancodeelemdetvalue = gl.gl_id) then
do:
create frwancodeelemdet.
assign
frwancodeelemdet.frwancodeelemdet_id = next-value(objectnumber)
frwancodeelemdet.frwancodeelem_id = frwancodeelem.frwancodeelem_id
frwancodeelemdet.frwancodeelemdetvalue = gl.gl_id
frwancodeelemdet.frwancodeelemdetcode = gl.glcode.
end.
end.
end. /* if "GL" */
if frwancodeelem.frwancodeelemcoatype = "division" then
do:
for each division where division.sharedset_id = vitargsubaccountsetid and
division.divisioncode begins frwancodeelem.frwancodeelemmask no-lock:
if not can-find(first frwancodeelemdet of frwancodeelem where frwancodeelemdet.frwancodeelemdetvalue = division.division_id) then
do:
create frwancodeelemdet.
assign
frwancodeelemdet.frwancodeelemdet_id = next-value(objectnumber)
frwancodeelemdet.frwancodeelem_id = frwancodeelem.frwancodeelem_id
frwancodeelemdet.frwancodeelemdetvalue = division.division_id
frwancodeelemdet.frwancodeelemdetcode = division.divisioncode.
end.
end.
end. /* if "Division" */
if frwancodeelem.frwancodeelemcoatype = "costcentre" then
do:
for each costcentre where costcentre.sharedset_id = vitargcostcentersetid and
costcentre.costcentrecode begins frwancodeelem.frwancodeelemmask no-lock:
if not can-find(first frwancodeelemdet of frwancodeelem where frwancodeelemdet.frwancodeelemdetvalue = costcentre.costcentre_id) then
do:
create frwancodeelemdet.
assign
frwancodeelemdet.frwancodeelemdet_id = next-value(objectnumber)
frwancodeelemdet.frwancodeelem_id = frwancodeelem.frwancodeelem_id
frwancodeelemdet.frwancodeelemdetvalue = costcentre.costcentre_id
frwancodeelemdet.frwancodeelemdetcode = costcentre.costcentrecode.
end.
end.
end. /* if "CostCentre" */
if frwancodeelem.frwancodeelemcoatype = "project" then
do:
for each qaddb.project where qaddb.project.sharedset_id = vitargprojectsetid and
qaddb.project.projectcode begins frwancodeelem.frwancodeelemmask no-lock:
if not can-find(first frwancodeelemdet of frwancodeelem where frwancodeelemdet.frwancodeelemdetvalue = qaddb.project.project_id) then
do:
create frwancodeelemdet.
assign
frwancodeelemdet.frwancodeelemdet_id = next-value(objectnumber)
frwancodeelemdet.frwancodeelem_id = frwancodeelem.frwancodeelem_id
frwancodeelemdet.frwancodeelemdetvalue = qaddb.project.project_id
frwancodeelemdet.frwancodeelemdetcode = qaddb.project.projectcode.
end.
end.
end. /* if "Project" */
if frwancodeelem.frwancodeelemcoatype = "layer" then
do:
for each layer where layer.layercode begins frwancodeelem.frwancodeelemmask no-lock:
if not can-find(first frwancodeelemdet of frwancodeelem where frwancodeelemdet.frwancodeelemdetvalue = layer.layer_id) then
do:
create frwancodeelemdet.
assign
frwancodeelemdet.frwancodeelemdet_id = next-value(objectnumber)
frwancodeelemdet.frwancodeelem_id = frwancodeelem.frwancodeelem_id
frwancodeelemdet.frwancodeelemdetvalue = layer.layer_id
frwancodeelemdet.frwancodeelemdetcode = layer.layercode.
end.
end.
end. /* if "Layer" */
if frwancodeelem.frwancodeelemcoatype = "journal" then
do:
for each journal where journal.journalcode begins frwancodeelem.frwancodeelemmask no-lock:
if not can-find(first frwancodeelemdet of frwancodeelem where frwancodeelemdet.frwancodeelemdetvalue = journal.journal_id) then
do:
create frwancodeelemdet.
assign
frwancodeelemdet.frwancodeelemdet_id = next-value(objectnumber)
frwancodeelemdet.frwancodeelem_id = frwancodeelem.frwancodeelem_id
frwancodeelemdet.frwancodeelemdetvalue = journal.journal_id
frwancodeelemdet.frwancodeelemdetcode = journal.journalcode.
end.
end.
end. /* if "Journal" */
if frwancodeelem.frwancodeelemcoatype = "currency" then
do:
for each currency where currency.currencycode begins frwancodeelem.frwancodeelemmask no-lock:
if not can-find(first frwancodeelemdet of frwancodeelem where frwancodeelemdet.frwancodeelemdetvalue = currency.currency_id) then
do:
create frwancodeelemdet.
assign
frwancodeelemdet.frwancodeelemdet_id = next-value(objectnumber)
frwancodeelemdet.frwancodeelem_id = frwancodeelem.frwancodeelem_id
frwancodeelemdet.frwancodeelemdetvalue = currency.currency_id
frwancodeelemdet.frwancodeelemdetcode = currency.currencycode.
end.
end.
end. /* if "Currency" */
if frwancodeelem.frwancodeelemcoatype = "interco" then
do:
for each BusinessRelation where BusinessRelation.BusinessRelationICCode begins FRWAnCodeElem.FRWAnCodeElemMask no-lock:
/* Changed the logic here because multiply business relations can exist with the same IC code. */
/* FRWAnCodeElemDet should only contain 1 record for each IC code, otherwise the iprRetrieveCubeDim goes wrong */
find first FRWAnCodeElemDet of FRWAnCodeElem where FRWAnCodeElemDet.FRWAnCodeElemDetCode = BusinessRelation.BusinessRelationICCode no-lock no-error.
if not available FRWAnCodeElemDet then
do:
create FRWAnCodeElemDet.
assign
FRWAnCodeElemDet.FRWAnCodeElemDet_ID = next-value(ObjectNumber)
FRWAnCodeElemDet.FRWAnCodeElem_ID = FRWAnCodeElem.FRWAnCodeElem_ID
FRWAnCodeElemDet.FRWAnCodeElemDetValue = BusinessRelation.BusinessRelation_ID
FRWAnCodeElemDet.FRWAnCodeElemDetCode = BusinessRelation.BusinessRelationICCode.
end.
/* now delete the duplicate codes if those exist */
assign viElemDetID = FRWAnCodeElemDet.FRWAnCodeElemDet_ID.
for each FRWAnCodeElemDet of FRWAnCodeElem where FRWAnCodeElemDet.FRWAnCodeElemDetCode = BusinessRelation.BusinessRelationICCode and
FRWAnCodeElemDet.FRWAnCodeElemDet_ID <> viElemDetID exclusive-lock:
delete FRWAnCodeElemDet.
end.
end.
end. /* if "Interco" */
if frwancodeelem.frwancodeelemcoatype = "saf1" or
frwancodeelem.frwancodeelemcoatype = "saf2" or
frwancodeelem.frwancodeelemcoatype = "saf3" or
frwancodeelem.frwancodeelemcoatype = "saf4" or
frwancodeelem.frwancodeelemcoatype = "saf5" or
frwancodeelem.frwancodeelemcoatype = "saf6" or
frwancodeelem.frwancodeelemcoatype = "saf7" or
frwancodeelem.frwancodeelemcoatype = "saf8" or
frwancodeelem.frwancodeelemcoatype = "saf9" or
frwancodeelem.frwancodeelemcoatype = "saf10"
then
do:
for each saf where saf.safconcept_id = visafconceptid and saf.safcode begins frwancodeelem.frwancodeelemmask no-lock:
if not can-find(first frwancodeelemdet of frwancodeelem where frwancodeelemdet.frwancodeelemdetvalue = saf.saf_id) then
do:
create frwancodeelemdet.
assign
frwancodeelemdet.frwancodeelemdet_id = next-value(objectnumber)
frwancodeelemdet.frwancodeelem_id = frwancodeelem.frwancodeelem_id
frwancodeelemdet.frwancodeelemdetvalue = saf.saf_id
frwancodeelemdet.frwancodeelemdetcode = saf.safcode.
end.
end.
end. /* if "Saf" */
end. /* FRWAnCodeElem with begins Mask value */
if frwancodeelem.frwancodeelemmaskrange1 <> "" or
frwancodeelem.frwancodeelemmaskrange2 <> "" then
do:
if frwancodeelem.frwancodeelemcoatype = "company" then
do:
for each company where company.companycode >= frwancodeelem.frwancodeelemmaskrange1 and
company.companycode <= frwancodeelem.frwancodeelemmaskrange2 no-lock,
each frwcompany where frwcompany.frwcompanyfrwchartcode = ictargetchart and
frwcompany.frwcompanycode = company.companycode no-lock:
if not can-find(first frwancodeelemdet of frwancodeelem where frwancodeelemdet.frwancodeelemdetvalue = company.company_id) then
do:
create frwancodeelemdet.
assign
frwancodeelemdet.frwancodeelemdet_id = next-value(objectnumber)
frwancodeelemdet.frwancodeelem_id = frwancodeelem.frwancodeelem_id
frwancodeelemdet.frwancodeelemdetvalue = company.company_id
frwancodeelemdet.frwancodeelemdetcode = company.companycode.
end.
end.
end. /* if "Company" */
if frwancodeelem.frwancodeelemcoatype = "gl" then
do:
for each gl where gl.sharedset_id = vitargglsetid and
gl.glcode >= frwancodeelem.frwancodeelemmaskrange1 and
gl.glcode <= frwancodeelem.frwancodeelemmaskrange2 no-lock:
if not can-find(first frwancodeelemdet of frwancodeelem where frwancodeelemdet.frwancodeelemdetvalue = gl.gl_id) then
do:
create frwancodeelemdet.
assign
frwancodeelemdet.frwancodeelemdet_id = next-value(objectnumber)
frwancodeelemdet.frwancodeelem_id = frwancodeelem.frwancodeelem_id
frwancodeelemdet.frwancodeelemdetvalue = gl.gl_id
frwancodeelemdet.frwancodeelemdetcode = gl.glcode.
end.
end.
end. /* if "GL" */
if frwancodeelem.frwancodeelemcoatype = "division" then
do:
for each division where division.sharedset_id = vitargsubaccountsetid and
division.divisioncode >= frwancodeelem.frwancodeelemmaskrange1 and
division.divisioncode <= frwancodeelem.frwancodeelemmaskrange2 no-lock:
if not can-find(first frwancodeelemdet of frwancodeelem where frwancodeelemdet.frwancodeelemdetvalue = division.division_id) then
do:
create frwancodeelemdet.
assign
frwancodeelemdet.frwancodeelemdet_id = next-value(objectnumber)
frwancodeelemdet.frwancodeelem_id = frwancodeelem.frwancodeelem_id
frwancodeelemdet.frwancodeelemdetvalue = division.division_id
frwancodeelemdet.frwancodeelemdetcode = division.divisioncode.
end.
end.
end. /* if "Division" */
if frwancodeelem.frwancodeelemcoatype = "costcentre" then
do:
for each costcentre where costcentre.sharedset_id = vitargcostcentersetid and
costcentre.costcentrecode >= frwancodeelem.frwancodeelemmaskrange1 and
costcentre.costcentrecode <= frwancodeelem.frwancodeelemmaskrange2 no-lock:
if not can-find(first frwancodeelemdet of frwancodeelem where frwancodeelemdet.frwancodeelemdetvalue = costcentre.costcentre_id) then
do:
create frwancodeelemdet.
assign
frwancodeelemdet.frwancodeelemdet_id = next-value(objectnumber)
frwancodeelemdet.frwancodeelem_id = frwancodeelem.frwancodeelem_id
frwancodeelemdet.frwancodeelemdetvalue = costcentre.costcentre_id
frwancodeelemdet.frwancodeelemdetcode = costcentre.costcentrecode.
end.
end.
end. /* if "CostCentre" */
if frwancodeelem.frwancodeelemcoatype = "project" then
do:
for each project where project.sharedset_id = vitargprojectsetid and
project.projectcode >= frwancodeelem.frwancodeelemmaskrange1 and
project.projectcode <= frwancodeelem.frwancodeelemmaskrange2 no-lock:
if not can-find(first frwancodeelemdet of frwancodeelem where frwancodeelemdet.frwancodeelemdetvalue = project.project_id) then
do:
create frwancodeelemdet.
assign
frwancodeelemdet.frwancodeelemdet_id = next-value(objectnumber)
frwancodeelemdet.frwancodeelem_id = frwancodeelem.frwancodeelem_id
frwancodeelemdet.frwancodeelemdetvalue = project.project_id
frwancodeelemdet.frwancodeelemdetcode = project.projectcode.
end.
end.
end. /* if "Project" */
if frwancodeelem.frwancodeelemcoatype = "layer" then
do:
for each layer where layer.layercode >= frwancodeelem.frwancodeelemmaskrange1 and
layer.layercode <= frwancodeelem.frwancodeelemmaskrange2 no-lock:
if not can-find(first frwancodeelemdet of frwancodeelem where frwancodeelemdet.frwancodeelemdetvalue = layer.layer_id) then
do:
create frwancodeelemdet.
assign
frwancodeelemdet.frwancodeelemdet_id = next-value(objectnumber)
frwancodeelemdet.frwancodeelem_id = frwancodeelem.frwancodeelem_id
frwancodeelemdet.frwancodeelemdetvalue = layer.layer_id
frwancodeelemdet.frwancodeelemdetcode = layer.layercode.
end.
end.
end. /* if "Layer" */
if frwancodeelem.frwancodeelemcoatype = "journal" then
do:
for each journal where journal.journalcode >= frwancodeelem.frwancodeelemmaskrange1 and
journal.journalcode <= frwancodeelem.frwancodeelemmaskrange2 no-lock:
if not can-find(first frwancodeelemdet of frwancodeelem where frwancodeelemdet.frwancodeelemdetvalue = journal.journal_id) then
do:
create frwancodeelemdet.
assign
frwancodeelemdet.frwancodeelemdet_id = next-value(objectnumber)
frwancodeelemdet.frwancodeelem_id = frwancodeelem.frwancodeelem_id
frwancodeelemdet.frwancodeelemdetvalue = journal.journal_id
frwancodeelemdet.frwancodeelemdetcode = journal.journalcode.
end.
end.
end. /* if "Journal" */
if frwancodeelem.frwancodeelemcoatype = "currency" then
do:
for each currency where currency.currencycode >= frwancodeelem.frwancodeelemmaskrange1 and
currency.currencycode <= frwancodeelem.frwancodeelemmaskrange2 no-lock:
if not can-find(first frwancodeelemdet of frwancodeelem where frwancodeelemdet.frwancodeelemdetvalue = currency.currency_id) then
do:
create frwancodeelemdet.
assign
frwancodeelemdet.frwancodeelemdet_id = next-value(objectnumber)
frwancodeelemdet.frwancodeelem_id = frwancodeelem.frwancodeelem_id
frwancodeelemdet.frwancodeelemdetvalue = currency.currency_id
frwancodeelemdet.frwancodeelemdetcode = currency.currencycode.
end.
end.
end. /* if "Currency" */
if frwancodeelem.frwancodeelemcoatype = "interco" then
do:
for each BusinessRelation where BusinessRelation.BusinessRelationICCode >= FRWAnCodeElem.FRWAnCodeElemMaskRange1 and
BusinessRelation.BusinessRelationICCode <= FRWAnCodeElem.FRWAnCodeElemMaskRange2 no-lock:
/* Changed the logic here because multiply business relations can exist with the same IC code. */
/* FRWAnCodeElemDet should only contain 1 record for each IC code, otherwise the iprRetrieveCubeDim goes wrong */
find first FRWAnCodeElemDet of FRWAnCodeElem where FRWAnCodeElemDet.FRWAnCodeElemDetCode = BusinessRelation.BusinessRelationICCode no-lock no-error.
if not available FRWAnCodeElemDet then
do:
create FRWAnCodeElemDet.
assign
FRWAnCodeElemDet.FRWAnCodeElemDet_ID = next-value(ObjectNumber)
FRWAnCodeElemDet.FRWAnCodeElem_ID = FRWAnCodeElem.FRWAnCodeElem_ID
FRWAnCodeElemDet.FRWAnCodeElemDetValue = BusinessRelation.BusinessRelation_ID
FRWAnCodeElemDet.FRWAnCodeElemDetCode = BusinessRelation.BusinessRelationICCode.
end.
/* now delete the duplicate codes if those exist */
assign viElemDetID = FRWAnCodeElemDet.FRWAnCodeElemDet_ID.
for each FRWAnCodeElemDet of FRWAnCodeElem where FRWAnCodeElemDet.FRWAnCodeElemDetCode = BusinessRelation.BusinessRelationICCode and
FRWAnCodeElemDet.FRWAnCodeElemDet_ID <> viElemDetID exclusive-lock:
delete FRWAnCodeElemDet.
end.
end.
end. /* if "Interco" */
if frwancodeelem.frwancodeelemcoatype = "saf1" or
frwancodeelem.frwancodeelemcoatype = "saf2" or
frwancodeelem.frwancodeelemcoatype = "saf3" or
frwancodeelem.frwancodeelemcoatype = "saf4" or
frwancodeelem.frwancodeelemcoatype = "saf5" or
frwancodeelem.frwancodeelemcoatype = "saf6" or
frwancodeelem.frwancodeelemcoatype = "saf7" or
frwancodeelem.frwancodeelemcoatype = "saf8" or
frwancodeelem.frwancodeelemcoatype = "saf9" or
frwancodeelem.frwancodeelemcoatype = "saf10"
then
do:
for each saf where saf.safconcept_id = visafconceptid and
saf.safcode >= frwancodeelem.frwancodeelemmaskrange1 and
saf.safcode <= frwancodeelem.frwancodeelemmaskrange2 no-lock:
if not can-find(first frwancodeelemdet of frwancodeelem where frwancodeelemdet.frwancodeelemdetvalue = saf.saf_id) then
do:
create frwancodeelemdet.
assign
frwancodeelemdet.frwancodeelemdet_id = next-value(objectnumber)
frwancodeelemdet.frwancodeelem_id = frwancodeelem.frwancodeelem_id
frwancodeelemdet.frwancodeelemdetvalue = saf.saf_id
frwancodeelemdet.frwancodeelemdetcode = saf.safcode.
end.
end.
end. /* if "Saf" */
end. /* FRWAnCodeElem with Range1 - Range2 values */
/* now we still need to treat 1 special case: Daybooks can exist with the same code and different ID's in the cube, because there is no COA cross ref and
the original source domains daybook code and ID are copied directly in the cube without translation.
if the user creates an Analysis Code and selected daybooks using the list elements, let's say 'JE' then all daybook records with JE should be in the
FRWAnCodeElemDet details. But is a new daybook JE is created later in another shared set without running the Analysis Code maintenance again,
then that will be missing and needs to be added */
if frwancodeelem.frwancodeelemcoatype = "journal" and
frwancodeelem.frwancodeelemmask = "" and
frwancodeelem.frwancodeelemmaskrange1 = "" and
frwancodeelem.frwancodeelemmaskrange2 = ""
then do:
for each frwancodeelemdet of frwancodeelem no-lock:
find first journal where journal.journalcode = frwancodeelemdet.frwancodeelemdetcode no-lock no-error.
if available journal then do:
for each bjournal where bjournal.journalcode = journal.journalcode no-lock:
if not can-find(first bfrwancodeelemdet of frwancodeelem where bfrwancodeelemdet.frwancodeelemdetvalue = bjournal.journal_id) then
do:
create bfrwancodeelemdet.
assign
bfrwancodeelemdet.frwancodeelemdet_id = next-value(objectnumber)
bfrwancodeelemdet.frwancodeelem_id = frwancodeelem.frwancodeelem_id
bfrwancodeelemdet.frwancodeelemdetvalue = bjournal.journal_id
bfrwancodeelemdet.frwancodeelemdetcode = bjournal.journalcode.
end.
end.
end.
end.
end.
end. /* for each FRWAnCode */
end. /* for each tqAnCode */