process/report/cashflow.p

   1 &ANALYZE-SUSPEND _VERSION-NUMBER UIB_v8r12
   2 &ANALYZE-RESUME
   3 &ANALYZE-SUSPEND _UIB-CODE-BLOCK _CUSTOM _DEFINITIONS Procedure
   4 /*--------------------------------------------------------------------------
   5   ------------------------------------------------------------------------*/
   6
   7 &SCOP VALID-FIELDS "Budget,Revised,Actual,BudgetVariance,RevisedVariance,Actual2"
   8
   9 DEF INPUT PARAMETER report-options AS CHAR NO-UNDO.
  10 DEF VAR project-1     LIKE Project.ProjectCode NO-UNDO.
  11 DEF VAR project-n     LIKE Project.ProjectCode NO-UNDO.
  12 DEF VAR hierarchy-1      AS INT NO-UNDO.
  13 DEF VAR hierarchy-n      AS INT NO-UNDO.
  14 DEF VAR field-name       AS CHAR NO-UNDO.
  15 DEF VAR sort-by          AS CHAR NO-UNDO INITIAL "Code".
  16 DEF VAR include-awaiting AS LOGI NO-UNDO INITIAL No.
  17 DEF VAR as-at-month      AS INT NO-UNDO.
  18 DEF VAR first-month      AS INT NO-UNDO INITIAL ?.
  19 RUN parse-parameters.
  20
  21 DEF VAR no-months   AS INT NO-UNDO.
  22 DEF VAR current-row AS INT NO-UNDO INITIAL 0.
  23 DEF VAR delim       AS CHAR NO-UNDO INIT "|".
  24
  25 /* each total line for a hierarchy level is a new total-row */
  26 DEF VAR grand-rows  AS CHAR INITIAL "".
  27 DEF VAR total-rows  AS CHAR INITIAL "".
  28 DEF VAR entity-rows  AS CHAR INITIAL "".
  29 DEF VAR hierarchy-depth AS INT NO-UNDO INITIAL 0.
  30
  31 DEF TEMP-TABLE MonthAccount NO-UNDO
  32   FIELD MonthCode   LIKE Month.MonthCode
  33   FIELD AccountCode LIKE ChartOfAccount.AccountCode
  34   FIELD Amount      AS DEC
  35
  36   INDEX MonthAccount IS UNIQUE PRIMARY
  37     AccountCode ASCENDING
  38     MonthCode   ASCENDING.
  39
  40 DEF TEMP-TABLE MonthBucket NO-UNDO
  41   FIELD MonthCode LIKE Month.MonthCode
  42   FIELD MonthName LIKE Month.MonthName
  43   FIELD MonthStart AS DATE
  44   FIELD MonthEnd   AS DATE
  45
  46   INDEX MonthCode IS UNIQUE PRIMARY
  47     MonthCode.
  48
  49 DEF TEMP-TABLE AccountBucket NO-UNDO
  50   FIELD AccountCode LIKE ChartOfAccount.AccountCode
  51   FIELD AccountName LIKE ChartOfAccount.Name
  52   FIELD ToDate      AS DEC
  53
  54   INDEX AccountCode IS UNIQUE PRIMARY
  55     AccountCode.
  56
  57 /* _UIB-CODE-BLOCK-END */
  58 &ANALYZE-RESUME
  59
  60
  61 &ANALYZE-SUSPEND _UIB-PREPROCESSOR-BLOCK
  62
  63 /* ********************  Preprocessor Definitions  ******************** */
  64
  65 &Scoped-define PROCEDURE-TYPE Procedure
  66 &Scoped-define DB-AWARE no
  67
  68
  69
  70 /* _UIB-PREPROCESSOR-BLOCK-END */
  71 &ANALYZE-RESUME
  72
  73
  74 /* ************************  Function Prototypes ********************** */
  75
  76 &IF DEFINED(EXCLUDE-get-amount) = 0 &THEN
  77
  78 &ANALYZE-SUSPEND _UIB-CODE-BLOCK _FUNCTION-FORWARD get-amount Procedure
  79 FUNCTION get-amount RETURNS DECIMAL
  80   ( /* parameter-definitions */ )  FORWARD.
  81
  82 /* _UIB-CODE-BLOCK-END */
  83 &ANALYZE-RESUME
  84
  85 &ENDIF
  86
  87 &IF DEFINED(EXCLUDE-get-earliest-month) = 0 &THEN
  88
  89 &ANALYZE-SUSPEND _UIB-CODE-BLOCK _FUNCTION-FORWARD get-earliest-month Procedure
  90 FUNCTION get-earliest-month RETURNS INTEGER
  91   ( /* parameter-definitions */ )  FORWARD.
  92
  93 /* _UIB-CODE-BLOCK-END */
  94 &ANALYZE-RESUME
  95
  96 &ENDIF
  97
  98
  99 /* *********************** Procedure Settings ************************ */
 100
 101 &ANALYZE-SUSPEND _PROCEDURE-SETTINGS
 102 /* Settings for THIS-PROCEDURE
 103    Type: Procedure
 104    Allow:
 105    Frames: 0
 106    Add Fields to: Neither
 107    Other Settings: CODE-ONLY COMPILE
 108  */
 109 &ANALYZE-RESUME _END-PROCEDURE-SETTINGS
 110
 111 /* *************************  Create Window  ************************** */
 112
 113 &ANALYZE-SUSPEND _CREATE-WINDOW
 114 /* DESIGN Window definition (used by the UIB)
 115   CREATE WINDOW Procedure ASSIGN
 116          HEIGHT             = 10.8
 117          WIDTH              = 34.43.
 118 /* END WINDOW DEFINITION */
 119                                                                         */
 120 &ANALYZE-RESUME
 121
 122 &ANALYZE-SUSPEND _UIB-CODE-BLOCK _CUSTOM _INCLUDED-LIB Procedure
 123 /* ************************* Included-Libraries *********************** */
 124
 125 {inc/method/m-excel.i}
 126
 127 /* _UIB-CODE-BLOCK-END */
 128 &ANALYZE-RESUME
 129
 130
 131
 132
 133
 134 &ANALYZE-SUSPEND _UIB-CODE-BLOCK _CUSTOM _MAIN-BLOCK Procedure
 135
 136
 137 /* ***************************  Main Block  *************************** */
 138
 139 RUN begin-spreadsheet.
 140 IF RETURN-VALUE = "FAIL" THEN DO:
 141   RETURN.
 142 END.
 143
 144 RUN cash-flow-report.
 145
 146 RUN end-spreadsheet.
 147
 148 /* _UIB-CODE-BLOCK-END */
 149 &ANALYZE-RESUME
 150
 151
 152 /* **********************  Internal Procedures  *********************** */
 153
 154 &IF DEFINED(EXCLUDE-begin-spreadsheet) = 0 &THEN
 155
 156 &ANALYZE-SUSPEND _UIB-CODE-BLOCK _PROCEDURE begin-spreadsheet Procedure
 157 PROCEDURE begin-spreadsheet :
 158 /*------------------------------------------------------------------------------
 159   Purpose:
 160 ------------------------------------------------------------------------------*/
 161
 162   start-excel().
 163
 164   IF NOT VALID-HANDLE( chExcelApplication ) THEN DO:
 165     RETURN "FAIL".
 166   END.
 167
 168   create-workbook().
 169   select-sheet(1).
 170
 171   RUN setup-workbook.
 172
 173   /* column widths */
 174   chWorkSheet:Columns("A"):ColumnWidth = 8.
 175   chWorkSheet:Columns("B"):ColumnWidth = 30.
 176   chWorkSheet:Columns("C:AZ"):ColumnWidth = 14.
 177
 178   /* alignment */
 179   chWorkSheet:Columns("C:AZ"):HorizontalAlignment = {&xlAlignRight}.
 180
 181   /* cell formats */
 182   chWorkSheet:Range("A:A"):NumberFormat = "0000.00" .
 183   chWorkSheet:Range("C:AZ"):NumberFormat = "#,##0.00;[Red]-#,##0.00" .
 184
 185   set-excel-visible(Yes).
 186   set-fast-mode(Yes).
 187
 188 END PROCEDURE.
 189
 190 /* _UIB-CODE-BLOCK-END */
 191 &ANALYZE-RESUME
 192
 193 &ENDIF
 194
 195 &IF DEFINED(EXCLUDE-cash-flow-report) = 0 &THEN
 196
 197 &ANALYZE-SUSPEND _UIB-CODE-BLOCK _PROCEDURE cash-flow-report Procedure
 198 PROCEDURE cash-flow-report :
 199 /*------------------------------------------------------------------------------
 200   Purpose:
 201 ------------------------------------------------------------------------------*/
 202 DEF VAR month-list AS CHAR NO-UNDO.
 203
 204   /* Header Line */
 205   no-months = 0.
 206   FOR EACH Month WHERE Month.MonthCode >= first-month AND Month.MonthCode <= as-at-month NO-LOCK:
 207     month-list = month-list + delim + "'" + Month.MonthName + " " + SUBSTR( STRING( YEAR( Month.EndDate ) ), 3 ).
 208     no-months = no-months + 1.
 209   END.
 210   RUN output-line( "Account" + delim + "Name" + delim + "Total" + month-list ).
 211   chWorkSheet:Range(STRING(current-row) + ":" + STRING(current-row)):Font:Name = "Arial".
 212   chWorkSheet:Range(STRING(current-row) + ":" + STRING(current-row)):Font:Size = 9.
 213
 214
 215   DEF BUFFER pj FOR Project.
 216
 217   IF sort-by = 'Type' THEN DO:
 218     FOR EACH ProjectType NO-LOCK:
 219       FOR EACH pj NO-LOCK WHERE pj.ProjectCode >= project-1
 220                           AND pj.ProjectCode <= project-n
 221                           AND pj.ProjectType = ProjectType.ProjectType :
 222         RUN each-project( pj.ProjectCode ).
 223       END.
 224       RUN totals-line( grand-rows, "Total of " + ProjectType.Description + " Projects").
 225     END.
 226
 227   END.
 228   ELSE IF sort-by = 'Parent' THEN DO:
 229     DEF VAR last-entity AS CHAR NO-UNDO INITIAL "".
 230     FOR EACH pj NO-LOCK WHERE pj.ProjectCode >= project-1
 231                           AND pj.ProjectCode <= project-n
 232                       BY pj.EntityType BY pj.EntityCode BY pj.EntityAccount :
 233       IF pj.EntityType + STRING(pj.EntityCode) <> last-entity THEN DO:
 234         IF last-entity <> "" THEN DO:
 235           RUN totals-line( entity-rows, "Total For " + last-entity ).
 236           entity-rows = "".
 237         END.
 238         last-entity = pj.EntityType + STRING(pj.EntityCode) .
 239       END.
 240       RUN each-project( pj.ProjectCode ).
 241     END.
 242     RUN totals-line( entity-rows, "Total For " + last-entity ).
 243     RUN totals-line( grand-rows, "Total of Project Range" ).
 244   END.
 245   ELSE DO:
 246     FOR EACH pj NO-LOCK WHERE pj.ProjectCode >= project-1
 247                           AND pj.ProjectCode <= project-n:
 248       RUN each-project( pj.ProjectCode ).
 249     END.
 250
 251     RUN totals-line( grand-rows, "Total of Project Range" ).
 252   END.
 253
 254 END PROCEDURE.
 255
 256 /* _UIB-CODE-BLOCK-END */
 257 &ANALYZE-RESUME
 258
 259 &ENDIF
 260
 261 &IF DEFINED(EXCLUDE-clear-buckets) = 0 &THEN
 262
 263 &ANALYZE-SUSPEND _UIB-CODE-BLOCK _PROCEDURE clear-buckets Procedure
 264 PROCEDURE clear-buckets :
 265 /*------------------------------------------------------------------------------
 266   Purpose:
 267 ------------------------------------------------------------------------------*/
 268   FOR EACH AccountBucket:
 269     DELETE AccountBucket.
 270   END.
 271   FOR EACH MonthAccount:
 272     DELETE MonthAccount.
 273   END.
 274 END PROCEDURE.
 275
 276 /* _UIB-CODE-BLOCK-END */
 277 &ANALYZE-RESUME
 278
 279 &ENDIF
 280
 281 &IF DEFINED(EXCLUDE-each-project) = 0 &THEN
 282
 283 &ANALYZE-SUSPEND _UIB-CODE-BLOCK _PROCEDURE each-project Procedure
 284 PROCEDURE each-project :
 285 /*------------------------------------------------------------------------------
 286   Purpose:
 287 ------------------------------------------------------------------------------*/
 288 DEF INPUT PARAMETER pj-code AS INT NO-UNDO.
 289 DEF VAR this-amount AS DEC NO-UNDO.
 290 DEF BUFFER pj FOR Project.
 291
 292 DEF VAR save-totals AS CHAR NO-UNDO.
 293
 294   IF hierarchy-depth >= hierarchy-1 AND hierarchy-depth <= hierarchy-n THEN DO:
 295     RUN clear-buckets.
 296     FIND Project WHERE Project.ProjectCode = pj-code NO-LOCK.
 297     FOR EACH ProjectBudget OF Project NO-LOCK:
 298       RUN get-account-bucket.
 299       FOR EACH AccountBalance WHERE AccountBalance.EntityType  = "J"
 300                               AND AccountBalance.EntityCode  = Project.ProjectCode
 301                               AND AccountBalance.AccountCode = ProjectBudget.AccountCode
 302                               AND AccountBalance.MonthCode >= first-month
 303                               AND AccountBalance.MonthCode <= as-at-month
 304                               AND (AccountBalance.Balance <> 0.0
 305                                       OR AccountBalance.Budget <> 0.0
 306                                       OR AccountBalance.RevisedBudget <> 0.0 ) :
 307         this-amount = get-amount().
 308         IF this-amount = 0 THEN NEXT.
 309
 310         RUN get-month-bucket.
 311         RUN get-month-account.
 312         RUN update-month-account( this-amount ).
 313       END.
 314     END.
 315
 316     IF CAN-FIND( FIRST MonthAccount ) THEN RUN send-to-spreadsheet.
 317   END.
 318
 319   save-totals = total-rows.
 320   total-rows = "".
 321   hierarchy-depth = hierarchy-depth + 1.
 322   FOR EACH pj NO-LOCK WHERE pj.EntityType = "J"
 323                         AND pj.EntityCode = pj-code:
 324     RUN each-project( pj.ProjectCode ).
 325   END.
 326   RUN totals-line( total-rows, "Sub-projects of Project " + STRING(pj-code,"99999") ).
 327   total-rows = save-totals.
 328   hierarchy-depth = hierarchy-depth - 1.
 329
 330 END PROCEDURE.
 331
 332 /* _UIB-CODE-BLOCK-END */
 333 &ANALYZE-RESUME
 334
 335 &ENDIF
 336
 337 &IF DEFINED(EXCLUDE-end-spreadsheet) = 0 &THEN
 338
 339 &ANALYZE-SUSPEND _UIB-CODE-BLOCK _PROCEDURE end-spreadsheet Procedure
 340 PROCEDURE end-spreadsheet :
 341 /*------------------------------------------------------------------------------
 342   Purpose:
 343 ------------------------------------------------------------------------------*/
 344   set-fast-mode(No).
 345   release-excel().
 346
 347 END PROCEDURE.
 348
 349 /* _UIB-CODE-BLOCK-END */
 350 &ANALYZE-RESUME
 351
 352 &ENDIF
 353
 354 &IF DEFINED(EXCLUDE-get-account-bucket) = 0 &THEN
 355
 356 &ANALYZE-SUSPEND _UIB-CODE-BLOCK _PROCEDURE get-account-bucket Procedure
 357 PROCEDURE get-account-bucket :
 358 /*------------------------------------------------------------------------------
 359   Purpose:
 360   Parameters:  <none>
 361   Notes:
 362 ------------------------------------------------------------------------------*/
 363
 364   FIND FIRST AccountBucket WHERE AccountBucket.AccountCode = ProjectBudget.AccountCode
 365     NO-LOCK NO-ERROR.
 366
 367   IF NOT AVAILABLE AccountBucket THEN
 368   DO:
 369     CREATE AccountBucket.
 370     AccountBucket.AccountCode = ProjectBudget.AccountCode.
 371     AccountBucket.AccountName = ProjectBudget.Description.
 372   END.
 373
 374 END PROCEDURE.
 375
 376 /* _UIB-CODE-BLOCK-END */
 377 &ANALYZE-RESUME
 378
 379 &ENDIF
 380
 381 &IF DEFINED(EXCLUDE-get-actual) = 0 &THEN
 382
 383 &ANALYZE-SUSPEND _UIB-CODE-BLOCK _PROCEDURE get-actual Procedure
 384 PROCEDURE get-actual :
 385 /*------------------------------------------------------------------------------
 386   Purpose:
 387 ------------------------------------------------------------------------------*/
 388 DEF OUTPUT PARAMETER actual AS DEC NO-UNDO.
 389
 390   actual = AccountBalance.Balance.
 391   IF NOT include-awaiting THEN RETURN.
 392
 393   FOR EACH Order NO-LOCK WHERE
 394     Order.ProjectCode = ProjectBudget.ProjectCode AND
 395     Order.AccountCode = ProjectBudget.AccountCode AND
 396     Order.OrderDate >= MonthBucket.MonthStart AND
 397     Order.OrderDate <= MonthBucket.MonthEnd:
 398
 399     FIND FIRST Voucher WHERE
 400       Voucher.ProjectCode = ProjectBudget.ProjectCode AND
 401       Voucher.OrderCode   = Order.OrderCode
 402       NO-LOCK NO-ERROR.
 403
 404     IF AVAILABLE Voucher AND LOOKUP( Voucher.VoucherStatus, "U,H" ) <> 0 THEN
 405       actual = actual + ( Voucher.GoodsValue + Voucher.TaxValue ).
 406
 407   END.
 408
 409 END PROCEDURE.
 410
 411 /* _UIB-CODE-BLOCK-END */
 412 &ANALYZE-RESUME
 413
 414 &ENDIF
 415
 416 &IF DEFINED(EXCLUDE-get-month-account) = 0 &THEN
 417
 418 &ANALYZE-SUSPEND _UIB-CODE-BLOCK _PROCEDURE get-month-account Procedure
 419 PROCEDURE get-month-account :
 420 /*------------------------------------------------------------------------------
 421   Purpose:
 422   Parameters:  <none>
 423   Notes:
 424 ------------------------------------------------------------------------------*/
 425
 426   FIND FIRST MonthAccount WHERE
 427     MonthAccount.MonthCode   = MonthBucket.MonthCode AND
 428     MonthAccount.AccountCode = AccountBalance.AccountCode
 429     NO-LOCK NO-ERROR.
 430
 431   IF NOT AVAILABLE MonthAccount THEN
 432   DO:
 433     CREATE MonthAccount.
 434     ASSIGN
 435       MonthAccount.MonthCode   = MonthBucket.MonthCode
 436       MonthAccount.AccountCode = AccountBalance.AccountCode.
 437   END.
 438
 439 END PROCEDURE.
 440
 441 /* _UIB-CODE-BLOCK-END */
 442 &ANALYZE-RESUME
 443
 444 &ENDIF
 445
 446 &IF DEFINED(EXCLUDE-get-month-bucket) = 0 &THEN
 447
 448 &ANALYZE-SUSPEND _UIB-CODE-BLOCK _PROCEDURE get-month-bucket Procedure
 449 PROCEDURE get-month-bucket :
 450 /*------------------------------------------------------------------------------
 451   Purpose:
 452 ------------------------------------------------------------------------------*/
 453
 454   FIND FIRST MonthBucket WHERE MonthBucket.MonthCode = AccountBalance.MonthCode
 455     NO-LOCK NO-ERROR.
 456
 457   IF NOT AVAILABLE MonthBucket THEN DO:
 458     FIND FIRST Month WHERE Month.MonthCode = AccountBalance.MonthCode NO-LOCK NO-ERROR.
 459     IF NOT AVAILABLE(Month) THEN DO:
 460       MESSAGE "Cannot find month" AccountBalance.MonthCode "for account " + AccountBalance.EntityType + STRING(AccountBalance.EntityCode,"99999") + "/" + STRING(AccountBalance.AccountCode,"9999.99").
 461     END.
 462
 463     CREATE MonthBucket.
 464     MonthBucket.MonthCode  = AccountBalance.MonthCode.
 465
 466     IF AVAILABLE(Month) THEN ASSIGN
 467       MonthBucket.MonthStart = Month.StartDate
 468       MonthBucket.MonthEnd   = Month.EndDate.
 469   END.
 470
 471 END PROCEDURE.
 472
 473 /* _UIB-CODE-BLOCK-END */
 474 &ANALYZE-RESUME
 475
 476 &ENDIF
 477
 478 &IF DEFINED(EXCLUDE-output-line) = 0 &THEN
 479
 480 &ANALYZE-SUSPEND _UIB-CODE-BLOCK _PROCEDURE output-line Procedure
 481 PROCEDURE output-line :
 482 /*------------------------------------------------------------------------------
 483   Purpose:
 484 ------------------------------------------------------------------------------*/
 485 DEF INPUT PARAMETER detail AS CHAR NO-UNDO.
 486
 487 DEF VAR i AS INT NO-UNDO.
 488
 489   current-row = current-row + 1.
 490   DO i = 1 TO NUM-ENTRIES( detail, delim ):
 491     chWorkSheet:Cells(current-row,i):Value = ENTRY( i, detail, delim ).
 492   END.
 493
 494 END PROCEDURE.
 495
 496 /* _UIB-CODE-BLOCK-END */
 497 &ANALYZE-RESUME
 498
 499 &ENDIF
 500
 501 &IF DEFINED(EXCLUDE-parse-parameters) = 0 &THEN
 502
 503 &ANALYZE-SUSPEND _UIB-CODE-BLOCK _PROCEDURE parse-parameters Procedure
 504 PROCEDURE parse-parameters :
 505 /*------------------------------------------------------------------------------
 506   Purpose:
 507 ------------------------------------------------------------------------------*/
 508 DEF VAR i           AS INT NO-UNDO.
 509 DEF VAR token       AS CHAR NO-UNDO.
 510
 511 {inc/showopts.i "report-options"}
 512
 513   DO i = 1 TO NUM-ENTRIES( report-options, "~n" ):
 514     token = ENTRY( i, report-options, "~n" ).
 515
 516     CASE( ENTRY( 1, token ) ):
 517       WHEN "ProjectRange" THEN ASSIGN
 518         project-1 = INT( ENTRY(2,token) )
 519         project-n = INT( ENTRY(3,token) ).
 520
 521       WHEN "ReportOn" THEN          field-name = ENTRY(2,token).
 522       WHEN "HierarchyLevels" THEN ASSIGN
 523         hierarchy-1 = INT( ENTRY(2,token) )
 524         hierarchy-n = INT( ENTRY(3,token) ).
 525       WHEN "AwaitingApproval" THEN  include-awaiting = Yes.
 526       WHEN "MonthTo" THEN           as-at-month = INT(ENTRY(2,token)).
 527       WHEN "MonthFrom" THEN         first-month = INT(ENTRY(2,token)).
 528       WHEN "Sort" THEN              sort-by = ENTRY(2,token).
 529     END CASE.
 530   END.
 531
 532   IF project-n < project-1 THEN project-n = project-1.
 533
 534   IF first-month = ? THEN first-month = get-earliest-month().
 535
 536 END PROCEDURE.
 537
 538 /* _UIB-CODE-BLOCK-END */
 539 &ANALYZE-RESUME
 540
 541 &ENDIF
 542
 543 &IF DEFINED(EXCLUDE-send-to-spreadsheet) = 0 &THEN
 544
 545 &ANALYZE-SUSPEND _UIB-CODE-BLOCK _PROCEDURE send-to-spreadsheet Procedure
 546 PROCEDURE send-to-spreadsheet :
 547 /*------------------------------------------------------------------------------
 548   Purpose:
 549 ------------------------------------------------------------------------------*/
 550 DEF VAR non-zero   AS LOGI NO-UNDO.
 551 DEF VAR top-row    AS INT NO-UNDO.
 552 DEF VAR dtl        AS CHAR NO-UNDO.
 553 DEF VAR c1         AS CHAR NO-UNDO.
 554 DEF VAR c2         AS CHAR NO-UNDO.
 555 DEF VAR r1         AS CHAR NO-UNDO.
 556 DEF VAR r2         AS CHAR NO-UNDO.
 557 DEF VAR i          AS INT NO-UNDO.
 558
 559   current-row = current-row + 1.
 560   IF sort-by = 'Parent' THEN DO:
 561     chWorkSheet:Cells(current-row,1):Value = Project.EntityType + TRIM(STRING(Project.EntityCode,">>>>>>9")).
 562     chWorkSheet:Cells(current-row,2):Value = STRING(Project.ProjectCode,"99999") + " - " + Project.Name .
 563   END.
 564   ELSE DO:
 565     chWorkSheet:Cells(current-row,1):Value = STRING(Project.ProjectCode,"99999") + " - " + Project.Name .
 566   END.
 567   chWorkSheet:Range(STRING(current-row) + ":" + STRING(current-row)):Font:Name = "Arial".
 568   chWorkSheet:Range(STRING(current-row) + ":" + STRING(current-row)):Font:Size = 11.
 569   chWorkSheet:Range(STRING(current-row) + ":" + STRING(current-row)):Font:Bold = Yes.
 570   chWorkSheet:Range(STRING(current-row) + ":" + STRING(current-row)):HorizontalAlignment = {&xlAlignLeft}.
 571
 572   /* Body */
 573   r1 = STRING( current-row + 1 ).
 574   top-row = current-row.
 575   FOR EACH AccountBucket NO-LOCK:
 576     non-zero = No.
 577     dtl = STRING(AccountBucket.AccountCode) + delim + AccountBucket.AccountName.
 578     r2 = STRING(current-row + 1).
 579     c1 = "D".
 580     c2 = int-to-column( no-months + 3 ).
 581     dtl = dtl + delim + "=SUM(" + c1 + r2 + ":" + c2 + r2 + ")".
 582
 583     FOR EACH Month WHERE Month.MonthCode >= first-month AND Month.MonthCode <= as-at-month NO-LOCK:
 584       FIND FIRST MonthAccount WHERE
 585         MonthAccount.AccountCode = AccountBucket.AccountCode AND
 586         MonthAccount.MonthCode   = Month.MonthCode NO-LOCK NO-ERROR.
 587
 588       dtl = dtl + delim.
 589       IF AVAILABLE MonthAccount THEN DO:
 590         dtl = dtl + STRING( MonthAccount.Amount ).
 591         IF MonthAccount.Amount <> 0 THEN non-zero = Yes.
 592       END.
 593       ELSE
 594         dtl = dtl + "0".
 595
 596     END.
 597     IF non-zero THEN RUN output-line( dtl ).
 598   END.
 599
 600   IF top-row <> current-row THEN DO:
 601     c1 = "C".
 602     r2 = STRING( current-row ).
 603     dtl = delim + "Totals".
 604     DO i = 0 TO no-months:
 605       c2 = int-to-column( i + 3 ).
 606       dtl = dtl + delim + "=SUM(" + c2 + r1 + ":" + c2 + r2 + ")".
 607     END.
 608     RUN output-line( dtl ).
 609     r2 = STRING(current-row).
 610     IF hierarchy-depth > 0 THEN DO:
 611       total-rows = total-rows + "+X" + r2.
 612     END.
 613     ELSE DO:
 614       entity-rows = entity-rows + "+X" + r2.
 615       grand-rows = grand-rows + "+X" + r2.
 616     END.
 617     chWorkSheet:Range(c1 + r2 + ":" + c2 + r2 ):Borders({&xlEdgeTop}):LineStyle = {&xlContinuous} .
 618   END.
 619
 620   current-row = current-row + 2.
 621
 622 END PROCEDURE.
 623
 624 /* _UIB-CODE-BLOCK-END */
 625 &ANALYZE-RESUME
 626
 627 &ENDIF
 628
 629 &IF DEFINED(EXCLUDE-setup-workbook) = 0 &THEN
 630
 631 &ANALYZE-SUSPEND _UIB-CODE-BLOCK _PROCEDURE setup-workbook Procedure
 632 PROCEDURE setup-workbook :
 633 /*------------------------------------------------------------------------------
 634   Purpose:
 635 ------------------------------------------------------------------------------*/
 636   chWorkSheet:PageSetup:Orientation = {&xlLandscape}.
 637   chWorkSheet:PageSetup:LeftMargin = 24.
 638   chWorkSheet:PageSetup:RightMargin = 24.
 639   chWorkSheet:PageSetup:TopMargin = 60.
 640   chWorkSheet:PageSetup:BottomMargin = 28.
 641   chWorkSheet:PageSetup:FooterMargin = 20.
 642   chWorkSheet:PageSetup:PrintTitleColumns = "A:B".
 643   chWorkSheet:PageSetup:PrintTitleRows = "1:1".
 644
 645   chWorkSheet:PageSetup:CenterHeader = "&12&B" + field-name + " Project Cashflows".
 646   chWorkSheet:PageSetup:LeftFooter = "".
 647   chWorkSheet:PageSetup:CenterFooter = "&07Page &P".
 648   chWorkSheet:PageSetup:RightFooter = "&07Generated at " + STRING( TIME, "HH:MM:SS") + " on " + STRING( TODAY, "99/99/9999").
 649
 650   chWorkBook:Styles("Normal"):Font:Size = 8.
 651   chWorkBook:Styles("Normal"):Font:Name = "Times New Roman".
 652
 653   chWorkBook:Styles:Add("Heading-1").
 654   chWorkBook:Styles("Heading-1"):Font:Size = 11.
 655   chWorkBook:Styles("Heading-1"):Font:Name = "Arial".
 656   chWorkBook:Styles("Heading-1"):Font:Bold = True.
 657
 658   chWorkBook:Styles:Add("Heading-2").
 659   chWorkBook:Styles("Heading-2"):Font:Name = "Arial".
 660   chWorkBook:Styles("Heading-2"):Font:Bold = True.
 661
 662   chWorkBook:Styles:Add("Prop-Total").
 663   chWorkBook:Styles("Prop-Total"):Font:Name = "Arial".
 664   chWorkBook:Styles("Prop-Total"):Font:Bold = True.
 665
 666 END PROCEDURE.
 667
 668 /* _UIB-CODE-BLOCK-END */
 669 &ANALYZE-RESUME
 670
 671 &ENDIF
 672
 673 &IF DEFINED(EXCLUDE-totals-line) = 0 &THEN
 674
 675 &ANALYZE-SUSPEND _UIB-CODE-BLOCK _PROCEDURE totals-line Procedure
 676 PROCEDURE totals-line :
 677 /*------------------------------------------------------------------------------
 678   Purpose:
 679 ------------------------------------------------------------------------------*/
 680 DEF INPUT PARAMETER sum-lines AS CHAR NO-UNDO.
 681 DEF INPUT PARAMETER description AS CHAR NO-UNDO.
 682
 683 DEF VAR dtl        AS CHAR NO-UNDO.
 684 DEF VAR c1         AS CHAR NO-UNDO.
 685 DEF VAR c2         AS CHAR NO-UNDO.
 686 DEF VAR r1         AS CHAR NO-UNDO.
 687 DEF VAR i          AS INT NO-UNDO.
 688
 689   sum-lines = SUBSTRING( sum-lines, 2).
 690   IF sum-lines = "" THEN RETURN.
 691
 692   r1 = STRING(current-row).
 693   current-row = current-row - 1.
 694   c1 = "C".
 695   dtl = delim + description.
 696   DO i = 0 TO no-months:
 697     c2 = int-to-column( i + 3 ).
 698     dtl = dtl + delim + "=" + REPLACE( sum-lines, "X", c2).
 699   END.
 700   RUN output-line( dtl ).
 701   chWorkSheet:Range(c1 + r1 + ":" + c2 + r1 ):Borders({&xlEdgeTop}):LineStyle = {&xlContinuous} .
 702
 703   current-row = current-row + 1.
 704
 705 END PROCEDURE.
 706
 707 /* _UIB-CODE-BLOCK-END */
 708 &ANALYZE-RESUME
 709
 710 &ENDIF
 711
 712 &IF DEFINED(EXCLUDE-update-month-account) = 0 &THEN
 713
 714 &ANALYZE-SUSPEND _UIB-CODE-BLOCK _PROCEDURE update-month-account Procedure
 715 PROCEDURE update-month-account :
 716 /*------------------------------------------------------------------------------
 717   Purpose:
 718 ------------------------------------------------------------------------------*/
 719 DEF INPUT PARAMETER amount AS DEC NO-UNDO.
 720
 721   MonthAccount.Amount = MonthAccount.Amount + amount.
 722
 723 END PROCEDURE.
 724
 725 /* _UIB-CODE-BLOCK-END */
 726 &ANALYZE-RESUME
 727
 728 &ENDIF
 729
 730 /* ************************  Function Implementations ***************** */
 731
 732 &IF DEFINED(EXCLUDE-get-amount) = 0 &THEN
 733
 734 &ANALYZE-SUSPEND _UIB-CODE-BLOCK _FUNCTION get-amount Procedure
 735 FUNCTION get-amount RETURNS DECIMAL
 736   ( /* parameter-definitions */ ) :
 737 /*------------------------------------------------------------------------------
 738   Purpose:
 739     Notes:
 740 ------------------------------------------------------------------------------*/
 741 DEF VAR amount AS DEC NO-UNDO.
 742
 743   CASE field-name:
 744
 745     WHEN "Budget"          THEN amount = AccountBalance.Budget.
 746     WHEN "Revised"         THEN amount = AccountBalance.Revised.
 747     OTHERWISE DO:
 748       DEF VAR actual AS DEC NO-UNDO.
 749       RUN get-actual( OUTPUT actual ).
 750       CASE field-name:
 751         WHEN "Actual"          THEN amount = actual.
 752         WHEN "BudgetVariance"  THEN amount = AccountBalance.Budget - actual.
 753         WHEN "RevisedVariance" THEN amount = AccountBalance.Revised - actual.
 754       END CASE.
 755     END.
 756
 757   END CASE.
 758
 759   RETURN amount.
 760
 761 END FUNCTION.
 762
 763 /* _UIB-CODE-BLOCK-END */
 764 &ANALYZE-RESUME
 765
 766 &ENDIF
 767
 768 &IF DEFINED(EXCLUDE-get-earliest-month) = 0 &THEN
 769
 770 &ANALYZE-SUSPEND _UIB-CODE-BLOCK _FUNCTION get-earliest-month Procedure
 771 FUNCTION get-earliest-month RETURNS INTEGER
 772   ( /* parameter-definitions */ ) :
 773 /*------------------------------------------------------------------------------
 774   Purpose:
 775     Notes:
 776 ------------------------------------------------------------------------------*/
 777 DEF VAR earliest-date AS DATE NO-UNDO INITIAL TODAY.
 778
 779   FOR EACH Project NO-LOCK WHERE Project.ProjectCode >= project-1
 780                            AND Project.ProjectCode <= project-n:
 781     IF Project.StartDate < earliest-date THEN earliest-date = Project.StartDate .
 782   END.
 783
 784   FIND Month WHERE Month.StartDate <= earliest-date AND Month.EndDate >= earliest-date NO-LOCK NO-ERROR.
 785   IF NOT AVAILABLE(Month) THEN FIND FIRST Month NO-LOCK.
 786   RETURN Month.MonthCode .
 787
 788 END FUNCTION.
 789
 790 /* _UIB-CODE-BLOCK-END */
 791 &ANALYZE-RESUME
 792
 793 &ENDIF
 794