project QadFinancials > class BAccountingInterface > method FillProDsBalanceSheet

Description

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


Parameters


icEntityListinputcharacterEntity List.
ohBalanceSheetoutputhandleHandle for Balance Sheet Prodataset.
itAccntToDate1inputdateAccount To Date1.
itAccntToDate2inputdateAccount To Date 2.
iiBudgetLevelinputintegerBudget Level.
icReportStructBSinputcharacterReport Struct of Balance Sheet Report.
icLayerCodeinputcharacterLayer Code.
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 later */
empty temp-table tReport.
empty temp-table tBalanceSheet.
empty temp-table tSubBalanceSheet.

/* Do some validation to iiBudgetLevel */
if iiBudgetLevel < 1 or iiBudgetLevel > 8
then do:
    assign oiReturnStatus = -1.
    <M-14 run SetMessage
       (input  #T-1'The budget level must be an integer between 0 and 8 if you want to export a Balance Sheet data file.':200(59831)T-1# (icMessage), 
        input  '':U (icArguments), 
        input  '':U (icFieldName), 
        input  '':U (icFieldValue), 
        input  '':U (icType), 
        input  3 (iiSeverity), 
        input  '':U (icRowid), 
        input  'QadFin-6108':U (icFcMsgNumber), 
        input  '':U (icFcExplanation), 
        input  '':U (icFcIdentification), 
        input  '':U (icFcContext), 
        output viFcReturnSuper (oiReturnStatus)) in BAccountingInterface>
    return.
end. /* if iiBudgetLevel < 1 or iiBudgetLevel > 8 */

/* Start an instance of BGLReport in order to invoke its method */
if viBGLReportID  = 0 or viBGLReportID = ?
then do:
    <I-3 {bFcStartAndOpenInstance
         &CLASS                = "BGLReport"}>
end.
else do:
    <I-4 {bFcStartInstance
         &CLASS                = "BGLReport"}>
end. /* if viBGLReportID  = 0 or viBGLReportID = ? */

/* to create a temp table, due to EDI limitation */
create tReport.
assign tReport.tiReportID = 1.

/* Initialize the integer variable for loop */
assign vii = 1.

/* Start querys */
<Q-9 run CompanyByIDAllInfo  (Start) in BCompany >
<Q-19 run CompanyPropertyByCompany  (Start) in BCompanyProperty >

repeat while vii <= num-entries(icEntityList):
    <Q-2 run CompanyByIDAllInfo (all) (Read) (NoCache)
       (input integer(entry(vii,icEntityList)), (CompanyId)
        output dataset tqCompanyByIDAllInfo) in BCompany >
    find first tqCompanyByIDAllInfo no-error.
    if not available tqCompanyByIDAllInfo
    then next.
    /* Construct the tFilter */
    /*find first tFilter where tFilter.tcBusinessFieldName = 'Division_CANDO':U no-error.
    if available tFilter
    then do:
         assign
            tFilter.tcDataType = 'c':U
            tFilter.tcOperator = ''
            tFilter.tcParameterValue = '?':U.
    end.
    else do:
        create tFilter.
        assign
            tFilter.tcBusinessFieldName = 'Division_CANDO':U
            tFilter.tcDataType = 'c':U
            tFilter.tcOperator = ''
            tFilter.tcParameterValue = '?':U. 
    end.*/
    /*find first tFilter where tFilter.tcBusinessFieldName = 'Layer_CANDO':U no-error.
    if available tFilter
    then do:
        assign
            tFilter.tcDataType = 'c':U
            tFilter.tcOperator = ''
            tFilter.tcParameterValue = icLayerCode.
    end.
    else do:
        create tFilter.
        assign
            tFilter.tcBusinessFieldName = 'Layer_CANDO':U
            tFilter.tcDataType = 'c':U
            tFilter.tcOperator = ''
            tFilter.tcParameterValue = icLayerCode.
    end.*/
    /*find first tFilter where tFilter.tcBusinessFieldName = 'CostCenter_CANDO':U no-error.
    if available tFilter
    then do:
        assign
            tFilter.tcDataType = 'c':U
            tFilter.tcOperator = ''
            tFilter.tcParameterValue = '':U.
    end.
    else do:
        create tFilter.
        assign
            tFilter.tcBusinessFieldName = 'CostCenter_CANDO':U
            tFilter.tcDataType = 'c':U
            tFilter.tcOperator = ''
            tFilter.tcParameterValue = '':U.
    end.*/
    /*find first tFilter where tFilter.tcBusinessFieldName = 'Measure1':U no-error.
    if available tFilter
    then do:
        assign
            tFilter.tcDataType = 'c':U
            tFilter.tcOperator = ''
            tFilter.tcParameterValue = 'Actuals':U.
    end.
    else do:
        create tFilter.
        assign
            tFilter.tcBusinessFieldName = 'Measure1':U
            tFilter.tcDataType = 'c':U
            tFilter.tcOperator = ''
            tFilter.tcParameterValue ='Actuals':U.
    end.
    find first tFilter where tFilter.tcBusinessFieldName = 'Measure1Date':U no-error.
    if available tFilter
    then do:
        assign
            tFilter.tcDataType = 't':U
            tFilter.tcOperator = ''
            tFilter.tcParameterValue = string(year(itAccntToDate1)) + string(month(itAccntToDate1),'99') + string(day(itAccntToDate1),'99').
    end.
    else do:
        create tFilter.
        assign
            tFilter.tcBusinessFieldName = 'Measure1Date':U
            tFilter.tcDataType = 't':U
            tFilter.tcOperator = '='
            tFilter.tcParameterValue = string(year(itAccntToDate1)) + string(month(itAccntToDate1),'99') + string(day(itAccntToDate1),'99').
    end.
    find first tFilter where tFilter.tcBusinessFieldName = 'Measure2':U no-error.
    if available tFilter
    then do:
        assign
            tFilter.tcDataType = 'c':U
            tFilter.tcOperator = ''
            tFilter.tcParameterValue = 'Actuals':U.
    end.
    else do:
        create tFilter.
        assign
            tFilter.tcBusinessFieldName = 'Measure2':U
            tFilter.tcDataType = 'c':U
            tFilter.tcOperator = ''
            tFilter.tcParameterValue = 'Actuals':U.
    end.
    find first tFilter where tFilter.tcBusinessFieldName = 'Measure2Date':U no-error.
    if available tFilter
    then do:
        assign
            tFilter.tcDataType = 'c':U
            tFilter.tcOperator = ''
            tFilter.tcParameterValue = string(year(itAccntToDate2)) + string(month(itAccntToDate2),'99') + string(day(itAccntToDate2),'99').
    end.
    else do:
        create tFilter.
        assign
            tFilter.tcBusinessFieldName = 'Measure2Date':U
            tFilter.tcDataType = 't':U
            tFilter.tcOperator = '='
            tFilter.tcParameterValue = string(year(itAccntToDate2)) + string(month(itAccntToDate2),'99') + string(day(itAccntToDate2),'99').
    end.*/

    find first tFilter where tFilter.tcBusinessFieldName = 'MeasureToDate':U no-error.
    if available tFilter
    then do:
        assign
            tFilter.tcDataType = 't':U
            tFilter.tcOperator = ''
            tFilter.tcParameterValue = string(year(itAccntToDate1)) + string(month(itAccntToDate1),'99') + string(day(itAccntToDate1),'99').
    end.
    else do:
        create tFilter.
        assign
            tFilter.tcBusinessFieldName = 'MeasureToDate':U
            tFilter.tcDataType = 't':U
            tFilter.tcOperator = '='
            tFilter.tcParameterValue = string(year(itAccntToDate1)) + string(month(itAccntToDate1),'99') + string(day(itAccntToDate1),'99').
    end.

    <Q-21 run CompanyPropertyByCompany (all) (Read) (NoCache)
       (input viCompanyId, (CompanyId)
        input ?, (CompanyCode)
        input ?, (CurrencyCode)
        output dataset tqCompanyPropertyByCompany) in BCompanyProperty >
    find first tqCompanyPropertyByCompany no-error.
    if not available tqCompanyPropertyByCompany
    then next.
    find first tFilter where tFilter.tcBusinessFieldName = 'ReportingCurrency':U no-error.
    if available tFilter
    then do:
        assign
            tFilter.tcDataType = 'c':U
            tFilter.tcOperator = ''
            tFilter.tcParameterValue = tqCompanyPropertyByCompany.tcCurrencyCode. /* RMB */
    end.
    else do:
        create tFilter.
        assign
            tFilter.tcBusinessFieldName = 'ReportingCurrency':U
            tFilter.tcDataType = 'c':U
            tFilter.tcOperator = ''
            tFilter.tcParameterValue = tqCompanyPropertyByCompany.tcCurrencyCode. /* RMB */
    end.

    find first tFilter where tFilter.tcBusinessFieldName = 'Company_CANDO':U no-error.
    if available tFilter
    then do:
        assign
            tFilter.tcDataType = 'c':U
            tFilter.tcOperator = ''
            tFilter.tcParameterValue = tqCompanyByIDAllInfo.tcCompanyCode.
    end.
    else do:
        create tFilter.
        assign
            tFilter.tcBusinessFieldName = 'Company_CANDO':U
            tFilter.tcDataType = 'c':U
            tFilter.tcOperator = ''
            tFilter.tcParameterValue = tqCompanyByIDAllInfo.tcCompanyCode.
    end.
    find first tFilter where tFilter.tcBusinessFieldName = 'ReportStructure':U no-error.
    if available tFilter
    then do:
        assign
            tFilter.tcDataType = 'c':U
            tFilter.tcOperator = ''
            tFilter.tcParameterValue = icReportStructBS.
    end.
    else do:
        create tFilter.
        assign
            tFilter.tcBusinessFieldName = 'ReportStructure':U
            tFilter.tcDataType = 'c':U
            tFilter.tcOperator = ''
            tFilter.tcParameterValue = icReportStructBS.
    end.
    find first tFilter where tFilter.tcBusinessFieldName = 'TopicLevel':U no-error.
    if available tFilter
    then do:
        assign
            tFilter.tcDataType = 'i':U
            tFilter.tcOperator = ''
            /*tFilter.tcParameterValue = '8':U.*/
            tFilter.tcParameterValue = string(iiBudgetLevel).
    end.
    else do:
        create tFilter.
        assign
            tFilter.tcBusinessFieldName = 'TopicLevel':U
            tFilter.tcDataType = 'i':U
            tFilter.tcOperator = ''
            /*tFilter.tcParameterValue = '8':U.*/
            tFilter.tcParameterValue = string(iiBudgetLevel).
    end.

    find first tFilter where tFilter.tcBusinessFieldName = 'COACrossRef':U no-error.
    if available tFilter
    then do:
        assign
            tFilter.tcDataType = 'c':U
            tFilter.tcOperator = ''
            tFilter.tcParameterValue = icCOACrossRefCode.
    end.
    else do:
        create tFilter.
        assign
            tFilter.tcBusinessFieldName = 'COACrossRef':U
            tFilter.tcDataType = 'c':U
            tFilter.tcOperator = ''
            tFilter.tcParameterValue = icCOACrossRefCode.
    end.

    /* Call method BalanceSheetForCAI in BGLReport */
    create dataset vhResultSet in widget-pool "non-persistent".
    /*<M-6 run BalanceSheetForCAI
       (input  tFilter (tFilter), 
        input  'EN':U (icLanguageCode), 
        output vhResultSet (ohBalanceSheet), 
        output viFcReturnSuper (oiReturnStatus)) in BGLReport>*/
    <M-31 run ChineseBSForCAI
       (input  tFilter (tFilter), 
        input  'EN':U (icLanguageCode), 
        output vhResultSet (ohBalanceSheet), 
        output viFcReturnSuper (oiReturnStatus)) in BGLReport>
    if viFcReturnSuper <> 0
    then assign oiReturnStatus = viFcReturnSuper.
    if viFcReturnSuper < 0
    then do:
        <I-7 {bFcCloseAndStopInstance
             &CLASS           = "BGLReport"}>             
        return.
    end. /* if viFcReturnSuper < 0 */

    /*
    /* Use the dataset handle vhResultSet to get data */
    create query vhQuery in widget-pool "non-persistent".
    vhQuery:set-buffers(vhResultSet:get-buffer-handle(1)).
    vhQuery:query-prepare('for each ' + vhResultSet:get-buffer-handle(1):name).
    vhQuery:query-open().
    vhQuery:get-first().
    repeat while not vhQuery:query-off-end:
        /* Use temp-table SubBalanceSheet to store data */
        create tSubBalanceSheet.
        assign
            tSubBalanceSheet.tcFDSElementType     = vhResultSet:get-buffer-handle(1):buffer-field('tcFDSElementType'):buffer-value
            tSubBalanceSheet.tdMeasure1           = vhResultSet:get-buffer-handle(1):buffer-field('tdMeasure1'):buffer-value
            tSubBalanceSheet.tdMeasure2           = vhResultSet:get-buffer-handle(1):buffer-field('tdMeasure2'):buffer-value
            tSubBalanceSheet.tiBudgetFDSSeq       = vhResultSet:get-buffer-handle(1):buffer-field('tiBudgetFDSSeq'):buffer-value
            tSubBalanceSheet.tcTopic              = vhResultSet:get-buffer-handle(1):buffer-field('tcTopic' + string(iiBudgetLevel)):buffer-value
            tSubBalanceSheet.tcCompanyDescription = tqCompanyByIDAllInfo.tcCompanyDescription.
        /* Logic of getting tiLineNumber */
        assign vct_FieldName = 'tiTopic' + string(iiBudgetLevel) + 'WBSID'.
        <Q-17 run BudgetWBSByBudgetWBSID (all) (Read) (NoCache)
           (input vhResultSet:get-buffer-handle(1):buffer-field(vct_FieldName):buffer-value, (BudgetWBSID)
            output dataset tqBudgetWBSByBudgetWBSID) in BBudget >
        for first tqBudgetWBSByBudgetWBSID:
            assign tSubBalanceSheet.tiLineNumber = tqBudgetWBSByBudgetWBSID.tiBudgetWBSLineNumber.
        end.
        /* Logic of getting tcParentTopic */
        if iiBudgetLevel = 1
        then assign tSubBalanceSheet.tcParentTopic = ?.
        else assign tSubBalanceSheet.tcParentTopic = vhResultSet:get-buffer-handle(1):buffer-field('tcTopic' + string(iiBudgetLevel - 1)):buffer-value.
        vhQuery:get-next().
    end. /* repeat while not vhQuery:query-off-end: */
    vhQuery:query-close().
    assign vii = vii + 1.
    */

    /* Use the dataset handle vhResultSet to get data */
    create query vhQuery in widget-pool "non-persistent".
    vhQuery:set-buffers(vhResultSet:get-buffer-handle(1)).
    vhQuery:query-prepare('for each ' + vhResultSet:get-buffer-handle(1):name).
    vhQuery:query-open().
    vhQuery:get-first().
    repeat while not vhQuery:query-off-end:
        if vhResultSet:get-buffer-handle(1):buffer-field('tcTopicProperty1'):buffer-value <> '' and
           vhResultSet:get-buffer-handle(1):buffer-field('tcTopicProperty1'):buffer-value <> ?
        then do:
            create tBalanceSheet.
            assign
                tBalanceSheet.tcReportNo             = #T-27'Form AF-01':255(332088423)T-27#
                tBalanceSheet.tcReportEntity         = tqCompanyByIDAllInfo.tcCompanyDescription
                tBalanceSheet.tcReportDate           = string(itAccntToDate1)
                tBalanceSheet.tcCurrency             = #T-28'Yuan':255(591320860)T-28#
                tBalanceSheet.tcContent              = vhResultSet:get-buffer-handle(1):buffer-field('tcTopicProperty1'):buffer-value
                tBalanceSheet.tcLines                = string(vhResultSet:get-buffer-handle(1):buffer-field('tiLineNumber1'):buffer-value)
                tBalanceSheet.tdYearBeginningBalance = round(vhResultSet:get-buffer-handle(1):buffer-field('tdOpeningBalance1'):buffer-value, 2)
                tBalanceSheet.tdBalance              = round(vhResultSet:get-buffer-handle(1):buffer-field('tdClosingBalance1'):buffer-value, 2)
                tBalanceSheet.tiReportID             = 1.
        end.
        if vhResultSet:get-buffer-handle(1):buffer-field('tcTopicProperty2'):buffer-value <> '' and
           vhResultSet:get-buffer-handle(1):buffer-field('tcTopicProperty2'):buffer-value <> ?
        then do:
            create tBalanceSheet.
            assign
                tBalanceSheet.tcReportNo             = #T-29'Form AF-01':255(332088423)T-29#
                tBalanceSheet.tcReportEntity         = tqCompanyByIDAllInfo.tcCompanyDescription
                tBalanceSheet.tcReportDate           = string(itAccntToDate1)
                tBalanceSheet.tcCurrency             = #T-30'Yuan':255(591320860)T-30#
                tBalanceSheet.tcContent              = vhResultSet:get-buffer-handle(1):buffer-field('tcTopicProperty2'):buffer-value
                tBalanceSheet.tcLines                = string(vhResultSet:get-buffer-handle(1):buffer-field('tiLineNumber2'):buffer-value)
                tBalanceSheet.tdYearBeginningBalance = round(vhResultSet:get-buffer-handle(1):buffer-field('tdOpeningBalance2'):buffer-value, 2)
                tBalanceSheet.tdBalance              = round(vhResultSet:get-buffer-handle(1):buffer-field('tdClosingBalance2'):buffer-value, 2)
                tBalanceSheet.tiReportID             = 1.
        end.
        vhQuery:get-next().
    end. /* repeat while not vhQuery:query-off-end: */
    vhQuery:query-close().
    assign vii = vii + 1.

end. /* repeat while vii <= num-entries(icEntityList): */
/* Stop querys */
<Q-26 run CompanyByIDAllInfo  (Stop) in BCompany >
<Q-20 run CompanyPropertyByCompany  (Stop) in BCompanyProperty >

/* Stop instance */
<I-8 {bFcCloseAndStopInstance
     &CLASS           = "BGLReport"}>

/*
/* Do more validation to iiBudgetLevel here */
find first tSubBalanceSheet where tSubBalanceSheet.tcFDSElementType = 'GL':U no-error.
if not available tSubBalanceSheet
then do:
    assign oiReturnStatus = -1.
    <M-22 run SetMessage
       (input  #T-3'The budget structure of the Balance Sheet does not have a GL level.':100(65679)T-3# (icMessage), 
        input  '':U (icArguments), 
        input  '':U (icFieldName), 
        input  '':U (icFieldValue), 
        input  '':U (icType), 
        input  3 (iiSeverity), 
        input  '':U (icRowid), 
        input  'QadFin-7274':U (icFcMsgNumber), 
        input  '':U (icFcExplanation), 
        input  '':U (icFcIdentification), 
        input  '':U (icFcContext), 
        output viFcReturnSuper (oiReturnStatus)) in BAccountingInterface>
    return.
end.
else do:
    assign viGLLevel = tSubBalanceSheet.tiBudgetFDSSeq.
end.
if iiBudgetLevel > viGLLevel
then do:
     assign oiReturnStatus = -1.
     <M-15 run SetMessage
        (input  #T-2'The budget level cannot be greater than the GL level.':100(59833)T-2# (icMessage), 
         input  '':U (icArguments), 
         input  '':U (icFieldName), 
         input  '':U (icFieldValue), 
         input  '':U (icType), 
         input  3 (iiSeverity), 
         input  '':U (icRowid), 
         input  'QadFin-6109':U (icFcMsgNumber), 
         input  '':U (icFcExplanation), 
         input  '':U (icFcIdentification), 
         input  '':U (icFcContext), 
         output viFcReturnSuper (oiReturnStatus)) in BAccountingInterface>
     return.
end.

/* Structure temp-table tBalanceSheet */
for each tSubBalanceSheet where tSubBalanceSheet.tiBudgetFDSSeq = iiBudgetLevel by tSubBalanceSheet.tcParentTopic:
         if tSubBalanceSheet.tcParentTopic <> ? and tSubBalanceSheet.tcParentTopic <> vct_ParentTopic
         then do:
             assign vct_ParentTopic = tSubBalanceSheet.tcParentTopic.
             create tBalanceSheet.
             assign
                 tBalanceSheet.tcReportNo     = '??01?':U
                 tBalanceSheet.tcReportEntity = tSubBalanceSheet.tcCompanyDescription
                 tBalanceSheet.tcReportDate   = string(itAccntToDate2)
                 tBalanceSheet.tcLines        = '':U
                 tBalanceSheet.tcCurrency     = '?':U
                 tBalanceSheet.tcContent      = tSubBalanceSheet.tcParentTopic + ':':U
                 tBalanceSheet.tdYearBeginningBalance = ?
                 tBalanceSheet.tdBalance      = ?
                 tBalanceSheet.tiReportID     = 1.
         end. /* if tSubBalanceSheet.tcParentTopic <> ? and tSubBalanceSheet.tcParentTopic <> vct_ParentTopic */
         if iiBudgetLevel <> viGLLevel or (iiBudgetLevel = viGLLevel and tSubBalanceSheet.tcFDSElementType = 'TopicTot':U)
         then do:
             create tBalanceSheet.
             assign
                 tBalanceSheet.tcReportNo     = '??01?':U
                 tBalanceSheet.tcReportEntity = tSubBalanceSheet.tcCompanyDescription
                 tBalanceSheet.tcReportDate   = string(itAccntToDate2)
                 tBalanceSheet.tcLines        = string(tSubBalanceSheet.tiLineNumber)
                 tBalanceSheet.tcCurrency     = '?':U
                 tBalanceSheet.tcContent      = tSubBalanceSheet.tcTopic
                 tBalanceSheet.tdYearBeginningBalance = tSubBalanceSheet.tdMeasure1 
                 tBalanceSheet.tdBalance      = tSubBalanceSheet.tdMeasure2
                 tBalanceSheet.tiReportID     = 1.
         end. /* if iiBudgetLevel <> viGLLevel or (iiBudgetLevel = viGLLevel and tSubBalanceSheet.tcFDSElementType = 'TopicTot':U) */
end. /* for each tSubBalanceSheet by tSubBalanceSheet.tcTopic: */

/* Use prodataset to encapsulat the temp-table */
/*
create dataset ohBalanceSheet in widget-pool "non-persistent".
ohBalanceSheet:set-buffers(buffer tReport:handle,buffer tBalanceSheet:handle).
ohBalanceSheet:add-relation(buffer tReport:handle,buffer tBalanceSheet:handle,'tiReportID,tiReportID',?,true ).
ohBalanceSheet:name = {&EXPORTREPORTCODE-Q_ZCFZ}.
*/
*/

create dataset ohBalanceSheet in widget-pool "non-persistent". 
create buffer vhReport for table buffer tReport:handle in widget-pool "non-persistent".
create buffer vhBalanceSheet for table buffer tBalanceSheet:handle in widget-pool "non-persistent".
ohBalanceSheet:set-buffers(vhReport, vhBalanceSheet).
ohBalanceSheet:add-relation(vhReport, vhBalanceSheet, 'tiReportID,tiReportID', ?, true).
ohBalanceSheet:name = {&EXPORTREPORTCODE-Q_ZCFZ}.

if oiReturnStatus = -98
then assign oiReturnStatus = 0.