Description
This method is used to generate prodataset for Balance Sheet Report.
Parameters
| icEntityList | input | character | Entity List. |
| ohBalanceSheet | output | handle | Handle for Balance Sheet Prodataset. |
| itAccntToDate1 | input | date | Account To Date1. |
| itAccntToDate2 | input | date | Account To Date 2. |
| iiBudgetLevel | input | integer | Budget Level. |
| icReportStructBS | input | character | Report Struct of Balance Sheet Report. |
| icLayerCode | input | character | Layer Code. |
| 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 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.