Description
This method is used to generate prodataset for Account Balance Report.
Parameters
| ohAccntBalance | output | handle | Handle for Account Balance Prodataset. |
| icEntityList | input | character | Entity List(selected on User Interface). |
| itAccntDate | input | date | Account Date |
| icCOACrossRefCode | input | character | |
| oiReturnStatus | output | integer | Return status of the method. |
Internal usage
QadFinancials
program code (program3/baccountinginterface.p)
assign oiReturnStatus = -98.
/* Empty temp-tables which will be used next */
empty temp-table tAccntBalance.
empty temp-table tAccntBalanceGroup.
empty temp-table tReport.
empty temp-table tCOACrossRefDetInfo.
/* to create a temp table, due to EDI limitation */
create tReport.
assign tReport.tiReportID = 1.
/* Initialize the integer for loop */
assign vii = 1.
/* Start the querys */
<Q-9 run CompanyPropertyByCompany
(Start) in BCompanyProperty >
<Q-10 run PeriodByStartEndDate
(Start) in BPeriod >
<Q-11 run PostingHistByBalance
(Start) in BPostingHist >
<Q-12 run GLByBankInfo
(Start) in BGL >
<Q-13 run CurrencyById
(Start) in BCurrency >
repeat while vii <= num-entries(icEntityList):
<Q-6 run CompanyPropertyByCompany (all) (Read) (NoCache)
(input integer(entry(vii,icEntityList)), (CompanyId)
input ?, (CompanyCode)
input ?, (CurrencyCode)
output dataset tqCompanyPropertyByCompany) in BCompanyProperty >
<Q-8 run PeriodByStartEndDate (all) (Read) (NoCache)
(input integer(entry(vii,icEntityList)), (CompanyId)
input itAccntDate, (Date)
output dataset tqPeriodByStartEndDate) in BPeriod >
for first tqPeriodByStartEndDate:
find first tqCompanyPropertyByCompany no-error.
<Q-4 run PostingHistByBalance (all) (Read) (NoCache)
(input integer(entry(vii,icEntityList)), (CompanyId)
input ?, (GLID)
input ?, (DivisionID)
input tqPeriodByStartEndDate.tiPeriodYearPeriod, (YearPeriod)
input ?, (JournalID)
input ?, (GLIsBalanceAccount)
input ?, (LayerCando)
input ?, (JournalCando)
input ?, (DivisionCando)
input ?, (CostCentreCando)
input ?, (ProjectCando)
input ?, (Currency)
input ?, (FromICBusinessRelation)
input ?, (ToICBusinessRelation)
input ?, (ExcludeJournalTypeCode)
output dataset tqPostingHistByBalance) in BPostingHist >
for each tqPostingHistByBalance
/* where tqPostingHistByBalance.tiPostingHistYearPeriodFrom = tqPeriodByStartEndDate.tiPeriodYearPeriod */
break by tqPostingHistByBalance.tiGL_ID by tqPostingHistByBalance.tiCurrency_ID by tqPostingHistByBalance.tiPostingHistYearPeriodFrom:
accumulate tqPostingHistByBalance.tdPostingHistBalanceDebitLC (total by tqPostingHistByBalance.tiCurrency_ID).
accumulate tqPostingHistByBalance.tdPostingHistBalanceDebitTC (total by tqPostingHistByBalance.tiCurrency_ID).
accumulate tqPostingHistByBalance.tdPostingHistBalanceCreditLC (total by tqPostingHistByBalance.tiCurrency_ID).
accumulate tqPostingHistByBalance.tdPostingHistBalanceCreditTC (total by tqPostingHistByBalance.tiCurrency_ID).
if tqPostingHistByBalance.tiPostingHistYearPeriodFrom = tqPeriodByStartEndDate.tiPeriodYearPeriod
then do:
accumulate tqPostingHistByBalance.tdPostingHistMovemntDebitLC (total by tqPostingHistByBalance.tiCurrency_ID).
accumulate tqPostingHistByBalance.tdPostingHistMovemntDebitTC (total by tqPostingHistByBalance.tiCurrency_ID).
accumulate tqPostingHistByBalance.tdPostingHistMovemntCreditLC (total by tqPostingHistByBalance.tiCurrency_ID).
accumulate tqPostingHistByBalance.tdPostingHistMovemntCreditTC (total by tqPostingHistByBalance.tiCurrency_ID).
end.
if last-of(tqPostingHistByBalance.tiCurrency_ID)
then do:
<Q-5 run GLByBankInfo (all) (Read) (NoCache)
(input ?, (CompanyId)
input tqPostingHistByBalance.tiGL_ID, (GLID)
input ?, (GLCode)
output dataset tqGLByBankInfo) in BGL >
find first tqGLByBankInfo no-error.
<Q-7 run CurrencyById (all) (Read) (NoCache)
(input tqPostingHistByBalance.tiCurrency_ID, (CurrencyId)
output dataset tqCurrencyById) in BCurrency >
assign vlsameasLC = (tqPostingHistByBalance.tiCurrency_ID = tqCompanyPropertyByCompany.tiCurrency_ID).
find first tqCurrencyById no-error.
if available tqGLByBankInfo and available tqCurrencyById
then do:
create tAccntBalanceGroup.
assign
tAccntBalanceGroup.tcAccountingNo = tqGLByBankInfo.tcGLCode
tAccntBalanceGroup.tcCurrencyCode = tqCurrencyById.tcCurrencyCode
tAccntBalanceGroup.tcSubAccntGroup = ''
tAccntBalanceGroup.tdOpenQty = 0.00
tAccntBalanceGroup.tdDtMoveQty = 0.00
tAccntBalanceGroup.tdCrMoveQty = 0.00
tAccntBalanceGroup.tdEndingQty = 0.00
tAccntBalanceGroup.tcPeriod = string(tqPeriodByStartEndDate.tiPeriodPeriod)
tAccntBalanceGroup.tiReportID = 1.
create tCOACrossRefDetInfo.
assign tCOACrossRefDetInfo.tcGLCode = tqGLByBankInfo.tcGLCode.
if tqPostingHistByBalance.tiPostingHistYearPeriodFrom = tqPeriodByStartEndDate.tiPeriodYearPeriod
then do:
assign
tAccntBalanceGroup.tdOpenBalance = ((accum total by tqPostingHistByBalance.tiCurrency_ID tqPostingHistByBalance.tdPostingHistBalanceDebitLC)
- (accum total by tqPostingHistByBalance.tiCurrency_ID tqPostingHistByBalance.tdPostingHistBalanceCreditLC))
- ((accum total by tqPostingHistByBalance.tiCurrency_ID tqPostingHistByBalance.tdPostingHistMovemntDebitLC)
- (accum total by tqPostingHistByBalance.tiCurrency_ID tqPostingHistByBalance.tdPostingHistMovemntCreditLC))
tAccntBalanceGroup.tdOpenBalanceOfForeign = if (vlsameasLC = no)
then ((accum total by tqPostingHistByBalance.tiCurrency_ID tqPostingHistByBalance.tdPostingHistBalanceDebitTC)
- (accum total by tqPostingHistByBalance.tiCurrency_ID tqPostingHistByBalance.tdPostingHistBalanceCreditTC))
- ((accum total by tqPostingHistByBalance.tiCurrency_ID tqPostingHistByBalance.tdPostingHistMovemntDebitTC) -
(accum total by tqPostingHistByBalance.tiCurrency_ID tqPostingHistByBalance.tdPostingHistMovemntCreditTC))
else 0.00
tAccntBalanceGroup.tdDtMove = accum total by tqPostingHistByBalance.tiCurrency_ID tqPostingHistByBalance.tdPostingHistMovemntDebitLC
tAccntBalanceGroup.tdDtMoveOfForeign = if (vlsameasLC = no)
then accum total by tqPostingHistByBalance.tiCurrency_ID tqPostingHistByBalance.tdPostingHistMovemntDebitTC
else 0.00
tAccntBalanceGroup.tdCrMove = accum total by tqPostingHistByBalance.tiCurrency_ID tqPostingHistByBalance.tdPostingHistMovemntCreditLC
tAccntBalanceGroup.tdCrMoveOfForeign = if(vlsameasLC = no)
then accum total by tqPostingHistByBalance.tiCurrency_ID tqPostingHistByBalance.tdPostingHistMovemntCreditTC
else 0.00
tAccntBalanceGroup.tdEndingBalance = (accum total by tqPostingHistByBalance.tiCurrency_ID tqPostingHistByBalance.tdPostingHistBalanceDebitLC)
- (accum total by tqPostingHistByBalance.tiCurrency_ID tqPostingHistByBalance.tdPostingHistBalanceCreditLC)
tAccntBalanceGroup.tdEndBalanceOfForeign = if (vlsameasLC = no)
then (accum total by tqPostingHistByBalance.tiCurrency_ID tqPostingHistByBalance.tdPostingHistBalanceDebitTC)
- (accum total by tqPostingHistByBalance.tiCurrency_ID tqPostingHistByBalance.tdPostingHistBalanceCreditTC)
else 0.00.
end. /* if tqPostingHistByBalance.tiPostingHistYearPeriodFrom = tqPeriodByStartEndDate.tiPeriodYearPeriod */
else do:
assign
tAccntBalanceGroup.tdOpenBalance = ((accum total by tqPostingHistByBalance.tiCurrency_ID tqPostingHistByBalance.tdPostingHistBalanceDebitLC)
- (accum total by tqPostingHistByBalance.tiCurrency_ID tqPostingHistByBalance.tdPostingHistBalanceCreditLC))
tAccntBalanceGroup.tdOpenBalanceOfForeign = if (vlsameasLC = no)
then ((accum total by tqPostingHistByBalance.tiCurrency_ID tqPostingHistByBalance.tdPostingHistBalanceDebitTC)
- (accum total by tqPostingHistByBalance.tiCurrency_ID tqPostingHistByBalance.tdPostingHistBalanceCreditTC))
else 0.00
tAccntBalanceGroup.tdDtMove = 0.00
tAccntBalanceGroup.tdDtMoveOfForeign = 0.00
tAccntBalanceGroup.tdCrMove = 0.00
tAccntBalanceGroup.tdCrMoveOfForeign = 0.00
tAccntBalanceGroup.tdEndingBalance = (accum total by tqPostingHistByBalance.tiCurrency_ID tqPostingHistByBalance.tdPostingHistBalanceDebitLC)
- (accum total by tqPostingHistByBalance.tiCurrency_ID tqPostingHistByBalance.tdPostingHistBalanceCreditLC)
tAccntBalanceGroup.tdEndBalanceOfForeign = if (vlsameasLC = no)
then (accum total by tqPostingHistByBalance.tiCurrency_ID tqPostingHistByBalance.tdPostingHistBalanceDebitTC)
- (accum total by tqPostingHistByBalance.tiCurrency_ID tqPostingHistByBalance.tdPostingHistBalanceCreditTC)
else 0.00.
end. /* if tqPostingHistByBalance.tiPostingHistYearPeriodFrom = tqPeriodByStartEndDate.tiPeriodYearPeriod */
end. /* if available tqGLByBankInfo and available tqCurrencyById */
end. /* if last-of(tqPostingHistByBalance.tiCurrency_ID) */
end. /* for each tqPostingHistByBalance: */
end. /* for first tqPeriodByStartEndDate: */
assign vii = vii + 1.
end. /* repeat while vii <= num-entries(icEntityList): */
/* Stop the querys */
<Q-14 run CompanyPropertyByCompany
(Stop) in BCompanyProperty >
<Q-15 run PeriodByStartEndDate
(Stop) in BPeriod >
<Q-16 run PostingHistByBalance
(Stop) in BPostingHist >
<Q-17 run GLByBankInfo
(Stop) in BGL >
<Q-18 run CurrencyById
(Stop) in BCurrency >
/* Call the method GetAlternateInfo to do validation and get the Alt code and description. */
assign vhResultTT = temp-table tAccntBalanceGroup:default-buffer-handle.
assign vcAltFieldName = "tcAccountingNo".
<M-19 run GetAlternateInfo
(input vhResultTT (ihResultTT),
input icCOACrossRefCode (icCOACrossRefCode),
input vcAltFieldName (icAltFieldName),
output viFcReturnSuper (oiReturnStatus)) in BAccountingInterface>
if viFcReturnSuper < 0 or (viFcReturnSuper > 0 and oiReturnStatus >= 0)
then do:
assign oiReturnStatus = viFcReturnSuper.
if oiReturnStatus < 0 then return.
end.
/* Group by Alt Account Code and Currency */
if icCOACrossRefCode <> "" and icCOACrossRefCode <> ?
then do:
for each tAccntBalanceGroup break by tAccntBalanceGroup.tcAccountingNo by tAccntBalanceGroup.tcCurrencyCode:
if first-of (tAccntBalanceGroup.tcCurrencyCode)
then do:
create tAccntBalance.
buffer-copy tAccntBalanceGroup to tAccntBalance.
end.
else do:
assign
tAccntBalance.tdOpenBalance = tAccntBalance.tdOpenBalance + tAccntBalanceGroup.tdOpenBalance
tAccntBalance.tdOpenBalanceOfForeign = tAccntBalance.tdOpenBalanceOfForeign + tAccntBalanceGroup.tdOpenBalanceOfForeign
tAccntBalance.tdDtMove = tAccntBalance.tdDtMove + tAccntBalanceGroup.tdDtMove
tAccntBalance.tdDtMoveOfForeign = tAccntBalance.tdDtMoveOfForeign + tAccntBalanceGroup.tdDtMoveOfForeign
tAccntBalance.tdCrMove = tAccntBalance.tdCrMove + tAccntBalanceGroup.tdCrMove
tAccntBalance.tdCrMoveOfForeign = tAccntBalance.tdCrMoveOfForeign + tAccntBalanceGroup.tdCrMoveOfForeign
tAccntBalance.tdEndingBalance = tAccntBalance.tdEndingBalance + tAccntBalanceGroup.tdEndingBalance
tAccntBalance.tdEndBalanceOfForeign = tAccntBalance.tdEndBalanceOfForeign + tAccntBalanceGroup.tdEndBalanceOfForeign.
end.
end.
end.
else do:
for each tAccntBalanceGroup:
create tAccntBalance.
buffer-copy tAccntBalanceGroup to tAccntBalance.
end.
end.
/* Use prodataset to encapsulat the temp-table */
/*
create dataset ohAccntBalance in widget-pool "non-persistent".
ohAccntBalance:set-buffers(buffer tReport:handle,buffer tAccntBalance:handle).
ohAccntBalance:add-relation(buffer tReport:handle,buffer tAccntBalance:handle,'tiReportID,tiReportID',?,true).
assign ohAccntBalance:name = {&EXPORTREPORTCODE-KMYE}.
*/
create dataset ohAccntBalance in widget-pool "non-persistent".
create buffer vhReport for table buffer tReport:handle in widget-pool "non-persistent".
create buffer vhAccntBalance for table buffer tAccntBalance:handle in widget-pool "non-persistent".
ohAccntBalance:set-buffers(vhReport, vhAccntBalance).
ohAccntBalance:add-relation(vhReport, vhAccntBalance, 'tiReportID,tiReportID', ?, true).
ohAccntBalance:name = {&EXPORTREPORTCODE-KMYE}.
/* ohAccntBalance:WRITE-XML("file", "C:\Develop\wht\Client\testKMYE.xml", YES, ?, ?, NO, NO). */
if oiReturnStatus = -98
then assign oiReturnStatus = 0.