project QadFinancials > class BAccountingInterface > method FillProDsAccntBalance

Description

This method is used to generate prodataset for Account Balance Report.


Parameters


ohAccntBalanceoutputhandleHandle for Account Balance Prodataset.
icEntityListinputcharacterEntity List(selected on User Interface).
itAccntDateinputdateAccount Date
icCOACrossRefCodeinputcharacter
oiReturnStatusoutputintegerReturn status of the method.


Internal usage


QadFinancials
method BAccountingInterface.BulidProDataSet


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.