process/import/convert-account-codes.p

   1 &ANALYZE-SUSPEND _VERSION-NUMBER UIB_v9r12
   2 &ANALYZE-RESUME
   3 &ANALYZE-SUSPEND _UIB-CODE-BLOCK _CUSTOM _DEFINITIONS Procedure
   4 /*------------------------------------------------------------------------
   5     Purpose     : Convert ledger codes from one set of numbers to another
   6
   7     Author(s)   : Andrew McMillan
   8     Created     : 27th September 2005
   9     Notes       :
  10   ----------------------------------------------------------------------*/
  11
  12 /* ***************************  Definitions  ************************** */
  13
  14 DEFINE TEMP-TABLE conversion
  15     FIELD old-code LIKE ChartOfAccount.AccountCode
  16     FIELD new-code LIKE ChartOfAccount.AccountCode
  17     FIELD new-name LIKE ChartOfAccount.Name
  18     FIELD converted AS LOGICAL
  19     FIELD in-line-no AS INT
  20     INDEX xpk-old old-code ASCENDING
  21     INDEX xak-new new-code ASCENDING.
  22
  23 DEF VAR pass-no AS INT INITIAL 0.
  24 DEF BUFFER other-conv FOR conversion.
  25
  26 /* _UIB-CODE-BLOCK-END */
  27 &ANALYZE-RESUME
  28
  29
  30 &ANALYZE-SUSPEND _UIB-PREPROCESSOR-BLOCK
  31
  32 /* ********************  Preprocessor Definitions  ******************** */
  33
  34 &Scoped-define PROCEDURE-TYPE Procedure
  35 &Scoped-define DB-AWARE no
  36
  37
  38
  39 /* _UIB-PREPROCESSOR-BLOCK-END */
  40 &ANALYZE-RESUME
  41
  42
  43
  44 /* *********************** Procedure Settings ************************ */
  45
  46 &ANALYZE-SUSPEND _PROCEDURE-SETTINGS
  47 /* Settings for THIS-PROCEDURE
  48    Type: Procedure
  49    Allow:
  50    Frames: 0
  51    Add Fields to: Neither
  52    Other Settings: CODE-ONLY COMPILE
  53  */
  54 &ANALYZE-RESUME _END-PROCEDURE-SETTINGS
  55
  56 /* *************************  Create Window  ************************** */
  57
  58 &ANALYZE-SUSPEND _CREATE-WINDOW
  59 /* DESIGN Window definition (used by the UIB)
  60   CREATE WINDOW Procedure ASSIGN
  61          HEIGHT             = 3.15
  62          WIDTH              = 33.57.
  63 /* END WINDOW DEFINITION */
  64                                                                         */
  65 &ANALYZE-RESUME
  66
  67
  68
  69
  70 &ANALYZE-SUSPEND _UIB-CODE-BLOCK _CUSTOM _MAIN-BLOCK Procedure
  71
  72
  73 /* ***************************  Main Block  *************************** */
  74 OUTPUT TO TERMINAL UNBUFFERED NO-CONVERT.
  75 RUN load-temp-table.
  76 PUT UNFORMATTED "Temp Table Loaded" SKIP.
  77 RUN clean-chartofaccount.
  78
  79 REPEAT WHILE CAN-FIND( FIRST conversion WHERE NOT(converted) ) AND pass-no < 20:
  80   pass-no = pass-no + 1.
  81   FOR EACH conversion WHERE NOT(converted)
  82                         AND NOT CAN-FIND( FIRST other-conv WHERE other-conv.old-code = conversion.new-code )
  83                         BY new-code DESC:
  84     RUN do-conversion( conversion.old-code, conversion.new-code, conversion.new-name ).
  85   END.
  86 END.
  87
  88 RUN clean-chartofaccount.
  89
  90 FOR EACH conversion WHERE NOT(conversion.converted):
  91   PUT UNFORMATTED "Could not convert " + STRING( conversion.old-code, "9999.99" ) SKIP.
  92 END.
  93
  94 PUT UNFORMATTED "Completed processing in " + TRIM(STRING(pass-no)) + " passes" SKIP.
  95
  96 OUTPUT CLOSE.
  97
  98 /* _UIB-CODE-BLOCK-END */
  99 &ANALYZE-RESUME
 100
 101
 102 /* **********************  Internal Procedures  *********************** */
 103
 104 &IF DEFINED(EXCLUDE-clean-chartofaccount) = 0 &THEN
 105
 106 &ANALYZE-SUSPEND _UIB-CODE-BLOCK _PROCEDURE clean-chartofaccount Procedure
 107 PROCEDURE clean-chartofaccount :
 108 /*------------------------------------------------------------------------------
 109   Purpose:
 110 ------------------------------------------------------------------------------*/
 111
 112 FOR EACH ChartOfAccount WHERE NOT CAN-FIND( FIRST Accttran OF ChartOfAccount ):
 113   DELETE ChartOfAccount.
 114 END.
 115
 116 END PROCEDURE.
 117
 118 /* _UIB-CODE-BLOCK-END */
 119 &ANALYZE-RESUME
 120
 121 &ENDIF
 122
 123 &IF DEFINED(EXCLUDE-do-conversion) = 0 &THEN
 124
 125 &ANALYZE-SUSPEND _UIB-CODE-BLOCK _PROCEDURE do-conversion Procedure
 126 PROCEDURE do-conversion :
 127 /*------------------------------------------------------------------------------
 128   Purpose:     Convert all instances of one accountcode to another
 129   Notes:       We also try and rename the account on the way through...
 130 ------------------------------------------------------------------------------*/
 131 DEF INPUT PARAMETER what-is AS DECIMAL NO-UNDO.
 132 DEF INPUT PARAMETER what-shall-be AS DECIMAL NO-UNDO.
 133 DEF INPUT PARAMETER new-account-name AS CHAR NO-UNDO.
 134
 135 IF NOT CAN-FIND( FIRST ChartOfAccount WHERE AccountCode = what-shall-be ) THEN DO:
 136   FOR EACH ChartOfAccount WHERE ChartOfAccount.AccountCode = what-is:
 137     ChartOfAccount.AccountCode = what-shall-be.
 138   END.
 139 END.
 140 FIND FIRST ChartOfAccount WHERE AccountCode = what-shall-be NO-ERROR.
 141 IF NOT AVAILABLE(ChartOfAccount) THEN DO:
 142   CREATE ChartOfAccount.
 143   ChartOfAccount.AccountCode = what-shall-be.
 144   ChartOfAccount.AccountGroupCode = "TEMP".
 145   ChartOfAccount.UpdateTo = "L".
 146 END.
 147 ChartOfAccount.Name = new-account-name.
 148
 149 FOR EACH AcctTran WHERE AcctTran.AccountCode = what-is: AcctTran.AccountCode = what-shall-be. END.
 150 FOR EACH VoucherLine WHERE VoucherLine.AccountCode = what-is: VoucherLine.AccountCode = what-shall-be. END.
 151 FOR EACH Voucher WHERE Voucher.AccountCode = what-is: Voucher.AccountCode = what-shall-be. END.
 152 FOR EACH InvoiceLine WHERE InvoiceLine.AccountCode = what-is: InvoiceLine.AccountCode = what-shall-be. END.
 153 FOR EACH AreaType WHERE AreaType.AccountCode = what-is: AreaType.AccountCode = what-shall-be. END.
 154 FOR EACH AssetType WHERE AssetType.AccountCode = what-is: AssetType.AccountCode = what-shall-be. END.
 155 FOR EACH BankAccount WHERE BankAccount.AccountCode = what-is: BankAccount.AccountCode = what-shall-be. END.
 156 FOR EACH BankImportException WHERE BankImportException.AccountCode = what-is: BankImportException.AccountCode = what-shall-be. END.
 157 FOR EACH CashFlow WHERE CashFlow.AccountCode = what-is: CashFlow.AccountCode = what-shall-be. END.
 158 FOR EACH ClosingGroup WHERE ClosingGroup.AccountCode = what-is: ClosingGroup.AccountCode = what-shall-be. END.
 159 FOR EACH EntityListMember WHERE EntityListMember.AccountCode = what-is: EntityListMember.AccountCode = what-shall-be. END.
 160 FOR EACH NewAcctTrans WHERE NewAcctTrans.AccountCode = what-is: NewAcctTrans.AccountCode = what-shall-be. END.
 161 FOR EACH OfficeControlAccount WHERE OfficeControlAccount.AccountCode = what-is: OfficeControlAccount.AccountCode = what-shall-be. END.
 162 FOR EACH Order WHERE Order.AccountCode = what-is: Order.AccountCode = what-shall-be. END.
 163 FOR EACH Project WHERE Project.EntityAccount = what-is: Project.EntityAccount = what-shall-be. END.
 164 FOR EACH ProjectBudget WHERE ProjectBudget.AccountCode = what-is: ProjectBudget.AccountCode = what-shall-be. END.
 165 FOR EACH ProjectBudget WHERE ProjectBudget.EntityAccount = what-is: ProjectBudget.EntityAccount = what-shall-be. END.
 166 FOR EACH PropertyOutgoing WHERE PropertyOutgoing.AccountCode = what-is: PropertyOutgoing.AccountCode = what-shall-be. END.
 167 FOR EACH PropForecast WHERE PropForecast.AccountCode = what-is: PropForecast.AccountCode = what-shall-be. END.
 168 FOR EACH RentCharge WHERE RentCharge.AccountCode = what-is: RentCharge.AccountCode = what-shall-be. END.
 169 FOR EACH RentChargeType WHERE RentChargeType.AccountCode = what-is: RentChargeType.AccountCode = what-shall-be. END.
 170 FOR EACH ServiceType WHERE ServiceType.AccountCode = what-is: ServiceType.AccountCode = what-shall-be. END.
 171 FOR EACH SupplyMeter WHERE SupplyMeter.AccountCode = what-is: SupplyMeter.AccountCode = what-shall-be. END.
 172 FOR EACH TenancyOutgoing WHERE TenancyOutgoing.AccountCode = what-is: TenancyOutgoing.AccountCode = what-shall-be. END.
 173 FOR EACH VariationFlow WHERE VariationFlow.AccountCode = what-is: VariationFlow.AccountCode = what-shall-be. END.
 174
 175 conversion.converted = true.
 176
 177 PUT UNFORMATTED conversion.old-code " => " conversion.new-code " " conversion.new-name SKIP.
 178
 179 END PROCEDURE.
 180
 181 /* _UIB-CODE-BLOCK-END */
 182 &ANALYZE-RESUME
 183
 184 &ENDIF
 185
 186 &IF DEFINED(EXCLUDE-load-temp-table) = 0 &THEN
 187
 188 &ANALYZE-SUSPEND _UIB-CODE-BLOCK _PROCEDURE load-temp-table Procedure
 189 PROCEDURE load-temp-table :
 190 /*------------------------------------------------------------------------------
 191   Purpose:     Load the temp table from a file
 192 ------------------------------------------------------------------------------*/
 193 DEF VAR line-no AS INT NO-UNDO INITIAL 0.
 194
 195 INPUT FROM new-codes.csv.
 196
 197 REPEAT ON ENDKEY UNDO, LEAVE:
 198   line-no = line-no + 1.
 199   CREATE conversion.
 200   IMPORT DELIMITER ","
 201     old-code ^
 202     new-code
 203     new-name
 204     NO-ERROR.
 205   IF old-code = ? OR new-code = ? THEN NEXT.
 206   ASSIGN
 207     in-line-no = line-no
 208     converted  = No.
 209 END.
 210
 211 INPUT CLOSE.
 212
 213 FOR EACH conversion WHERE old-code EQ ? OR new-code EQ ?
 214                        OR old-code EQ 0 OR new-code EQ 0
 215                        OR old-code EQ new-code:
 216   DELETE conversion.
 217 END.
 218
 219 END PROCEDURE.
 220
 221 /* _UIB-CODE-BLOCK-END */
 222 &ANALYZE-RESUME
 223
 224 &ENDIF
 225