process/export/export-balances.p

   1 &ANALYZE-SUSPEND _VERSION-NUMBER UIB_v8r12
   2 &ANALYZE-RESUME
   3 &ANALYZE-SUSPEND _UIB-CODE-BLOCK _CUSTOM _DEFINITIONS Procedure
   4 /*--------------------------------------------------------------------------
   5     File        : process/export/export-balances.p
   6     Purpose     : Export account balance information to a spreadsheet
   7
   8     Author(s)   : Andrew McMillan
   9     Created     : 4/11/98
  10     Notes       :
  11   ------------------------------------------------------------------------*/
  12 DEF INPUT PARAMETER report-options AS CHAR NO-UNDO.
  13
  14 DEF VAR debug AS LOGI NO-UNDO INITIAL No.
  15 DEF VAR output-file-name AS CHAR NO-UNDO.
  16 DEF VAR detailed AS LOGI NO-UNDO INITIAL No.
  17 DEF VAR merged AS LOGI NO-UNDO INITIAL No.
  18 DEF VAR month-1 AS INT NO-UNDO INITIAL ?.
  19 DEF VAR month-n AS INT NO-UNDO INITIAL ?.
  20 DEF VAR entity-type AS CHAR NO-UNDO INITIAL "L".
  21 DEF VAR entity-1 AS INT NO-UNDO INITIAL 0.
  22 DEF VAR entity-n AS INT NO-UNDO INITIAL 999999.
  23 DEF VAR account-1 AS DEC NO-UNDO INITIAL 0.
  24 DEF VAR account-n AS DEC NO-UNDO INITIAL 99999.
  25 DEF VAR account-groups AS CHAR NO-UNDO INITIAL ?.
  26 DEF VAR entity-columns AS LOGI NO-UNDO INITIAL No.
  27 DEF VAR no-groups AS INT NO-UNDO INITIAL 0.
  28 RUN parse-parameters.
  29
  30 {inc/ofc-this.i}
  31
  32 DEF TEMP-TABLE Acct NO-UNDO
  33     FIELD Seq AS DEC
  34     FIELD Code AS CHAR
  35     FIELD Name AS CHAR
  36     INDEX XPKAcct IS UNIQUE PRIMARY Seq Code.
  37
  38 DEF TEMP-TABLE Entity NO-UNDO
  39     FIELD Code AS INT
  40     FIELD Name AS CHAR
  41     INDEX XPKEntity IS UNIQUE PRIMARY Code.
  42
  43 DEF TEMP-TABLE Bal NO-UNDO
  44     FIELD Code AS INT
  45     FIELD Seq AS DEC
  46     FIELD Bal AS DEC
  47     INDEX XPKBal IS UNIQUE PRIMARY Code Seq.
  48
  49 /* _UIB-CODE-BLOCK-END */
  50 &ANALYZE-RESUME
  51
  52
  53 &ANALYZE-SUSPEND _UIB-PREPROCESSOR-BLOCK
  54
  55 /* ********************  Preprocessor Definitions  ******************** */
  56
  57 &Scoped-define PROCEDURE-TYPE Procedure
  58
  59
  60
  61 /* _UIB-PREPROCESSOR-BLOCK-END */
  62 &ANALYZE-RESUME
  63
  64
  65 /* ************************  Function Prototypes ********************** */
  66
  67 &ANALYZE-SUSPEND _UIB-CODE-BLOCK _FUNCTION-FORWARD balance-from-balances Procedure
  68 FUNCTION balance-from-balances RETURNS DECIMAL
  69   ( INPUT et AS CHAR, INPUT ec AS INT, INPUT ac AS DEC, INPUT m-1 AS INT, INPUT m-n AS INT )  FORWARD.
  70
  71 /* _UIB-CODE-BLOCK-END */
  72 &ANALYZE-RESUME
  73
  74 &ANALYZE-SUSPEND _UIB-CODE-BLOCK _FUNCTION-FORWARD balance-from-summary Procedure
  75 FUNCTION balance-from-summary RETURNS DECIMAL
  76   ( INPUT et AS CHAR, INPUT ec AS INT, INPUT ac AS DEC, INPUT mnth AS INT )  FORWARD.
  77
  78 /* _UIB-CODE-BLOCK-END */
  79 &ANALYZE-RESUME
  80
  81
  82 /* *********************** Procedure Settings ************************ */
  83
  84 &ANALYZE-SUSPEND _PROCEDURE-SETTINGS
  85 /* Settings for THIS-PROCEDURE
  86    Type: Procedure
  87    Allow:
  88    Frames: 0
  89    Add Fields to: Neither
  90    Other Settings: CODE-ONLY COMPILE
  91  */
  92 &ANALYZE-RESUME _END-PROCEDURE-SETTINGS
  93
  94 /* *************************  Create Window  ************************** */
  95
  96 &ANALYZE-SUSPEND _CREATE-WINDOW
  97 /* DESIGN Window definition (used by the UIB)
  98   CREATE WINDOW Procedure ASSIGN
  99          HEIGHT             = .33
 100          WIDTH              = 42.14.
 101 /* END WINDOW DEFINITION */
 102                                                                         */
 103 &ANALYZE-RESUME
 104
 105
 106
 107 &ANALYZE-SUSPEND _UIB-CODE-BLOCK _CUSTOM _INCLUDED-LIB Procedure
 108 /* ************************* Included-Libraries *********************** */
 109
 110 {inc/date.i}
 111
 112 /* _UIB-CODE-BLOCK-END */
 113 &ANALYZE-RESUME
 114
 115
 116
 117 &ANALYZE-SUSPEND _UIB-CODE-BLOCK _CUSTOM _MAIN-BLOCK Procedure
 118
 119
 120 /* ***************************  Main Block  *************************** */
 121
 122 OUTPUT TO VALUE( output-file-name ).
 123
 124 RUN build-balances.
 125 IF entity-columns THEN
 126   RUN export-entity-columns.
 127 ELSE
 128   RUN export-account-columns.
 129
 130 OUTPUT CLOSE.
 131
 132 MESSAGE "Export of account balances complete"
 133         VIEW-AS ALERT-BOX INFORMATION
 134         TITLE "Export Complete".
 135
 136 /* _UIB-CODE-BLOCK-END */
 137 &ANALYZE-RESUME
 138
 139
 140 /* **********************  Internal Procedures  *********************** */
 141
 142 &ANALYZE-SUSPEND _UIB-CODE-BLOCK _PROCEDURE build-balances Procedure
 143 PROCEDURE build-balances :
 144 /*------------------------------------------------------------------------------
 145   Purpose:
 146 ------------------------------------------------------------------------------*/
 147   CASE entity-type:
 148     WHEN "L" THEN   RUN company-range.
 149     WHEN "P" THEN   RUN property-range.
 150 /*    WHEN "T" THEN   RUN tenant-range. */
 151   END CASE.
 152
 153 END PROCEDURE.
 154
 155 /* _UIB-CODE-BLOCK-END */
 156 &ANALYZE-RESUME
 157
 158
 159 &ANALYZE-SUSPEND _UIB-CODE-BLOCK _PROCEDURE company-range Procedure
 160 PROCEDURE company-range :
 161 /*------------------------------------------------------------------------------
 162   Purpose:
 163 ------------------------------------------------------------------------------*/
 164   FOR EACH Company WHERE Company.CompanyCode >= entity-1
 165                     AND Company.CompanyCode <= entity-n NO-LOCK:
 166     RUN each-entity( Company.CompanyCode, Company.ShortName ).
 167   END.
 168 END PROCEDURE.
 169
 170 /* _UIB-CODE-BLOCK-END */
 171 &ANALYZE-RESUME
 172
 173
 174 &ANALYZE-SUSPEND _UIB-CODE-BLOCK _PROCEDURE each-account Procedure
 175 PROCEDURE each-account :
 176 /*------------------------------------------------------------------------------
 177   Purpose:
 178 ------------------------------------------------------------------------------*/
 179 DEF INPUT PARAMETER ec      AS INT NO-UNDO.
 180 DEF INPUT PARAMETER e-name  AS CHAR NO-UNDO.
 181 DEF INPUT PARAMETER ac      AS DEC NO-UNDO.
 182 DEF INPUT PARAMETER seq     AS DEC NO-UNDO.
 183 DEF INPUT PARAMETER code    AS CHAR NO-UNDO.
 184
 185 DEF VAR act-bal AS DEC NO-UNDO.
 186
 187   IF AccountGroup.GroupType = "B" THEN
 188     act-bal = balance-from-summary( entity-type, ec, ac, month-n).
 189   ELSE
 190     act-bal = balance-from-balances( entity-type, ec, ac, month-1, month-n).
 191
 192   IF act-bal = 0 THEN RETURN.
 193
 194   FIND Acct WHERE Acct.Seq = seq AND Acct.Code = code NO-LOCK NO-ERROR.
 195   IF NOT AVAILABLE(Acct) THEN DO:
 196     CREATE Acct.
 197     Acct.Seq  = Seq.
 198     Acct.Code = code.
 199     Acct.Name = (IF account-groups = ? OR detailed THEN ChartOfAccount.Name ELSE AccountGroup.Name).
 200   END.
 201
 202   FIND Entity WHERE Entity.Code = ec NO-LOCK NO-ERROR.
 203   IF NOT AVAILABLE(Entity) THEN DO:
 204     CREATE Entity.
 205     Entity.Code = ec.
 206     Entity.Name = e-name.
 207   END.
 208
 209   FIND Bal WHERE Bal.Code = Entity.Code AND Bal.Seq = Acct.Seq NO-ERROR.
 210   IF NOT AVAILABLE(Bal) THEN DO:
 211     CREATE Bal.
 212     Bal.Code = Entity.Code.
 213     Bal.Seq  = Acct.Seq.
 214   END.
 215
 216   Bal.Bal = Bal.Bal + act-bal.
 217
 218 END PROCEDURE.
 219
 220 /* _UIB-CODE-BLOCK-END */
 221 &ANALYZE-RESUME
 222
 223
 224 &ANALYZE-SUSPEND _UIB-CODE-BLOCK _PROCEDURE each-entity Procedure
 225 PROCEDURE each-entity :
 226 /*------------------------------------------------------------------------------
 227   Purpose:
 228 ------------------------------------------------------------------------------*/
 229 DEF INPUT PARAMETER ec AS INT NO-UNDO.
 230 DEF INPUT PARAMETER e-name AS CHAR NO-UNDO.
 231
 232 DEF VAR i AS INT NO-UNDO.
 233 DEF VAR ac-group AS CHAR NO-UNDO.
 234
 235   IF debug THEN
 236     PUT UNFORMATTED "Entity " + entity-type STRING(ec,"99999") " - " e-name SKIP.
 237
 238   IF account-groups = ? THEN DO:
 239     FOR EACH ChartOfAccount WHERE ChartOfAccount.AccountCode >= account-1
 240                             AND ChartOfAccount.AccountCode <= account-n NO-LOCK,
 241                 FIRST AccountGroup OF ChartOfAccount NO-LOCK:
 242     IF debug THEN
 243       PUT UNFORMATTED "Account: " STRING(ChartOfAccount.AccountCode,"9999.99")
 244                     + " - " ChartOfAccount.Name SKIP.
 245       RUN each-account( ec, e-name, ChartOfAccount.AccountCode, ChartOfAccount.AccountCode, STRING(ChartOfAccount.AccountCode,"9999.99") ).
 246     END.
 247   END.
 248   ELSE DO:
 249     DO i = 1 TO no-groups:
 250       ac-group = ENTRY( i, account-groups ).
 251       FIND AccountGroup WHERE AccountGroup.AccountGroupCode = ac-group NO-LOCK NO-ERROR.
 252       FOR EACH ChartOfAccount OF AccountGroup NO-LOCK:
 253         IF debug THEN
 254           PUT UNFORMATTED "Account: " STRING(ChartOfAccount.AccountCode,"9999.99")
 255                         + "  Group: " AccountGroup.AccountGroupCode SKIP.
 256         IF merged THEN
 257           RUN each-account( ec, e-name, ChartOfAccount.AccountCode, INT(ChartOfAccount.AccountCode), STRING(INT(ChartOfAccount.AccountCode),"9999.99") ).
 258         ELSE IF detailed THEN
 259           RUN each-account( ec, e-name, ChartOfAccount.AccountCode, ChartOfAccount.AccountCode, STRING(ChartOfAccount.AccountCode,"9999.99") ).
 260         ELSE
 261           RUN each-account( ec, e-name, ChartOfAccount.AccountCode,
 262                           AccountGroup.SequenceCode, AccountGroup.AccountGroupCode ).
 263       END.
 264     END.
 265   END.
 266
 267 END PROCEDURE.
 268
 269 /* _UIB-CODE-BLOCK-END */
 270 &ANALYZE-RESUME
 271
 272
 273 &ANALYZE-SUSPEND _UIB-CODE-BLOCK _PROCEDURE export-account-columns Procedure
 274 PROCEDURE export-account-columns :
 275 /*------------------------------------------------------------------------------
 276   Purpose:
 277 ------------------------------------------------------------------------------*/
 278 DEF VAR amount AS DEC NO-UNDO.
 279
 280   PUT UNFORMATTED ','.
 281   FOR EACH Acct:
 282     PUT UNFORMATTED "," + STRING( Acct.Code).
 283   END.
 284   PUT SKIP.
 285
 286   PUT UNFORMATTED '"Entity","Name"'.
 287   FOR EACH Acct:
 288     PUT UNFORMATTED ',"' + STRING( Acct.Name) + '"'.
 289   END.
 290   PUT SKIP.
 291
 292   FOR EACH Entity:
 293     PUT UNFORMATTED STRING(Entity.Code) + ',"' + STRING(Entity.Name) + '"'.
 294     FOR EACH Acct:
 295       FIND Bal WHERE Bal.Code = Entity.Code AND Bal.Seq = Acct.Seq NO-ERROR.
 296       IF AVAILABLE(Bal) THEN
 297         PUT UNFORMATTED "," Bal.Bal.
 298       ELSE
 299         PUT UNFORMATTED ",0".
 300     END.
 301     PUT SKIP.
 302   END.
 303 END PROCEDURE.
 304
 305 /* _UIB-CODE-BLOCK-END */
 306 &ANALYZE-RESUME
 307
 308
 309 &ANALYZE-SUSPEND _UIB-CODE-BLOCK _PROCEDURE export-entity-columns Procedure
 310 PROCEDURE export-entity-columns :
 311 /*------------------------------------------------------------------------------
 312   Purpose:
 313 ------------------------------------------------------------------------------*/
 314 DEF VAR amount AS DEC NO-UNDO.
 315
 316   PUT UNFORMATTED ','.
 317   FOR EACH Entity:
 318     PUT UNFORMATTED "," + STRING( Entity.Code).
 319   END.
 320   PUT SKIP.
 321
 322   PUT UNFORMATTED '"Account","Name"'.
 323   FOR EACH Entity:
 324     PUT UNFORMATTED ',"' + STRING( Entity.Name) + '"'.
 325   END.
 326   PUT SKIP.
 327
 328   FOR EACH Acct:
 329     PUT UNFORMATTED STRING(Acct.Code) + ',"' + STRING(Acct.Name) + '"'.
 330     FOR EACH Entity:
 331       FIND Bal WHERE Bal.Code = Entity.Code AND Bal.Seq = Acct.Seq NO-ERROR.
 332       IF AVAILABLE(Bal) THEN
 333         PUT UNFORMATTED "," Bal.Bal.
 334       ELSE
 335         PUT UNFORMATTED ",0".
 336     END.
 337     PUT SKIP.
 338   END.
 339 END PROCEDURE.
 340
 341 /* _UIB-CODE-BLOCK-END */
 342 &ANALYZE-RESUME
 343
 344
 345 &ANALYZE-SUSPEND _UIB-CODE-BLOCK _PROCEDURE export-header Procedure
 346 PROCEDURE export-header :
 347 /*------------------------------------------------------------------------------
 348   Purpose:  Print some information at the beginning of the export
 349 ------------------------------------------------------------------------------*/
 350
 351 END PROCEDURE.
 352
 353 /* _UIB-CODE-BLOCK-END */
 354 &ANALYZE-RESUME
 355
 356
 357 &ANALYZE-SUSPEND _UIB-CODE-BLOCK _PROCEDURE parse-parameters Procedure
 358 PROCEDURE parse-parameters :
 359 /*------------------------------------------------------------------------------
 360   Purpose:
 361 ------------------------------------------------------------------------------*/
 362 DEF VAR i           AS INT NO-UNDO.
 363 DEF VAR token       AS CHAR NO-UNDO.
 364 DEF VAR fin-year    AS INT NO-UNDO.
 365
 366 {inc/showopts.i "report-options"}.
 367
 368   DO i = 1 TO NUM-ENTRIES( report-options, "~n" ):
 369
 370     token = ENTRY( i, report-options, "~n" ).
 371     CASE( ENTRY( 1, token ) ):
 372       WHEN "Detailed" THEN      detailed = Yes.
 373       WHEN "MergeAccounts" THEN merged = Yes.
 374       WHEN "FileName" THEN      output-file-name = ENTRY( 2, token ).
 375       WHEN "Columns" THEN       entity-columns = (ENTRY( 2, token) = "Entities").
 376       WHEN "EntityType" THEN    entity-type = ENTRY( 2, token).
 377
 378       WHEN "EntityRange" THEN ASSIGN
 379         entity-1 = INT( ENTRY( 2, token) )
 380         entity-n = INT( ENTRY( 3, token) ) .
 381
 382       WHEN "AccountRange" THEN ASSIGN
 383         account-1 = DEC( ENTRY( 2, token) )
 384         account-n = DEC( ENTRY( 3, token) ) .
 385
 386       WHEN "AccountGroups" THEN ASSIGN
 387         account-groups = SUBSTRING( token, INDEX(token,",") + 1 ).
 388
 389       WHEN "ToMonth" THEN DO:
 390         month-n = INT( ENTRY( 2, token)).
 391         FIND Month WHERE Month.MonthCode = month-n NO-LOCK NO-ERROR.
 392         fin-year = Month.FinancialYearCode.
 393         FIND FIRST Month WHERE Month.FinancialYearCode = fin-year NO-LOCK NO-ERROR.
 394         month-1 = Month.MonthCode.
 395       END.
 396
 397       WHEN "Period" THEN ASSIGN
 398         month-1 = INT( ENTRY( 2, token))
 399         month-n = INT( ENTRY( 3, token)).
 400
 401     END CASE.
 402   END.
 403
 404   no-groups = NUM-ENTRIES( account-groups ).
 405
 406 END PROCEDURE.
 407
 408 /* _UIB-CODE-BLOCK-END */
 409 &ANALYZE-RESUME
 410
 411
 412 &ANALYZE-SUSPEND _UIB-CODE-BLOCK _PROCEDURE property-range Procedure
 413 PROCEDURE property-range :
 414 /*------------------------------------------------------------------------------
 415   Purpose:
 416 ------------------------------------------------------------------------------*/
 417   FOR EACH Property WHERE Property.PropertyCode >= entity-1
 418                     AND Property.PropertyCode <= entity-n NO-LOCK:
 419     RUN each-entity( Property.PropertyCode, Property.Name ).
 420   END.
 421 END PROCEDURE.
 422
 423 /* _UIB-CODE-BLOCK-END */
 424 &ANALYZE-RESUME
 425
 426
 427 &ANALYZE-SUSPEND _UIB-CODE-BLOCK _PROCEDURE tenant-range Procedure
 428 PROCEDURE tenant-range :
 429 /*------------------------------------------------------------------------------
 430   Purpose:
 431 ------------------------------------------------------------------------------*/
 432   FOR EACH Tenant WHERE Tenant.Active AND Tenant.TenantCode >= entity-1
 433                     AND Tenant.TenantCode <= entity-n NO-LOCK:
 434     RUN each-entity( Tenant.TenantCode, Tenant.Name ).
 435   END.
 436 END PROCEDURE.
 437
 438 /* _UIB-CODE-BLOCK-END */
 439 &ANALYZE-RESUME
 440
 441
 442 /* ************************  Function Implementations ***************** */
 443
 444 &ANALYZE-SUSPEND _UIB-CODE-BLOCK _FUNCTION balance-from-balances Procedure
 445 FUNCTION balance-from-balances RETURNS DECIMAL
 446   ( INPUT et AS CHAR, INPUT ec AS INT, INPUT ac AS DEC, INPUT m-1 AS INT, INPUT m-n AS INT ) :
 447 /*------------------------------------------------------------------------------
 448   Purpose: Calculate balance at a particular date by adding a range of months
 449     Notes: Applies to I&E (P&L) accounts
 450 ------------------------------------------------------------------------------*/
 451 DEF VAR bal AS DEC NO-UNDO INITIAL 0.00 .
 452
 453   FOR EACH AccountBalance WHERE AccountBalance.EntityType = et
 454                 AND AccountBalance.EntityCode = ec
 455                 AND AccountBalance.AccountCode = ac
 456                 AND AccountBalance.MonthCode >= m-1
 457                 AND AccountBalance.MonthCode <= m-n NO-LOCK:
 458     bal = bal + AccountBalance.Balance.
 459   END.
 460
 461   RETURN bal.
 462
 463 END FUNCTION.
 464
 465 /* _UIB-CODE-BLOCK-END */
 466 &ANALYZE-RESUME
 467
 468
 469 &ANALYZE-SUSPEND _UIB-CODE-BLOCK _FUNCTION balance-from-summary Procedure
 470 FUNCTION balance-from-summary RETURNS DECIMAL
 471   ( INPUT et AS CHAR, INPUT ec AS INT, INPUT ac AS DEC, INPUT mnth AS INT ) :
 472 /*------------------------------------------------------------------------------
 473   Purpose: Calculate balance at a particular date by subtracting backwards
 474     Notes: Applies to balance sheet accounts
 475 ------------------------------------------------------------------------------*/
 476 DEF VAR bal AS DEC NO-UNDO INITIAL 0.00 .
 477
 478   FIND AccountSummary WHERE AccountSummary.EntityType = et
 479                 AND AccountSummary.EntityCode = ec
 480                 AND AccountSummary.AccountCode = ac NO-LOCK NO-ERROR.
 481   IF AVAILABLE(AccountSummary) THEN bal = AccountSummary.Balance.
 482
 483   FOR EACH AccountBalance WHERE AccountBalance.EntityType = et
 484                 AND AccountBalance.EntityCode = ec
 485                 AND AccountBalance.AccountCode = ac
 486                 AND AccountBalance.MonthCode > mnth NO-LOCK:
 487     bal = bal - AccountBalance.Balance.
 488   END.
 489
 490   RETURN bal.
 491
 492 END FUNCTION.
 493
 494 /* _UIB-CODE-BLOCK-END */
 495 &ANALYZE-RESUME
 496
 497