SytelineMaster

Sometimes a user will accidentally type in a wrong number in the starting check number in A/P check printing and posting. If that check number is higher than the last check number used, the system will prompt to void all the check numbers in between. If that prompt is answered "ok", this will result in a large number of voided checks in the Bank Reconciliation and the system will not allow those numbers to be used.

This problem can be fixed in one of two ways.

The first is to go into the bank reconciliation and delete those voided checks one by one until they get back to the correct starting check number.

The second way is to use SQL Query to programmatically delete those voided checks.

/* This script is used to delete a range of check numbers from the glbank table */

declare @commit int, @rows int, @check_num GlCheckNumType

——————————————————————-

/* INSTRUCTIONS: CHANGE THE @commit VARIABLE TO VIEW OR COMMIT CHANGES

AND ENTER A MAX CHECK NUMBER. */

SET @commit = 0 — 0=View; 1=Commit the update changes

SET @check_num = 1055 — Enter highest check number wanted in SL

——————————————————————-

— VIEW CHECK RECORDS THAT WILL BE DELETED

select * from glbank

where check_num > @check_num and type = ‘C’

set @rows=@@rowcount

if @commit=0

begin

if @rows > 0 select [Results]=’Viewing record(s) to be deleted.’

else select [Results]=’No matching records found.’

end

else

begin

— DELETE CHECK RECORDS

delete from glbank

where check_num > @check_num and type = ‘C’

select [Results]= dbo.cstr(@@rowcount) + ‘ Glbank record(s) deleted.’

end

Progress version

for each (database name).glbank where check-num > xxxxxxxxx and type = "C":

display glbank with 2 col.

Run the query. If the checks that display are the ones that need to be deleted, then run the following:

for each (database name).glbank where check-num > xxxxxxxx and type = "C":

delete glbank.

This will reset the starting check number back to the original check.

This is about Progress version of Syteline, on how multi-currency works in Financial Statements.

FINANCIAL STATEMENTS
The exchange rate table (could be local or global) normally used by the database from which the Financial Statement is being printed will be used for the translations. Therefore, unless Current Rate translation is to be used on all accounts, the exchange rates in the Corporate database would best be entered correctly throughout the fiscal year, even if no translations are needed between those currencies until the financial statements are printed. Alternatively, exchanged rates may be entered and back-dated (e.g.,;, for subsidiaries acquired in the middle of the year), assuming a sufficient rate history has been stored correctly elsewhere (e.g.,’, on paper or in the new subsidiary’s system).
FINANCIAL STATEMENT OUTPUT
gl/calc-bal.p:
If None translations is requested, calculate balance as it is done currently.
If Current Rate translations is requested, calculate balance as above, then find current exchange rate and translate and round to the Corporate’s currency.
If End of Period translations is requested, translate each pertot.amt at its end of period exchange rate, and translate the remaining ledger amounts at each one’s end of period exchange rate. Then round the final balance.
If Average Period rate translation is requested, proceed like End of Period, but use the following formula to calculate the average exchange rate for each period.
Avg. Rate = (SUM(Rate * Effective Time))/
( Length of Period)
where Effective Time is the amount of time during which the rate was in effect during the period ( in seconds), and length of Period is also in seconds.
For speed, you may want to calculate, before starting through the sequence lines, the average buying and selling rates for all periods appearing on the report ( if any sequence lines use this method), and save them in a workfile or array for use here.
If Spot Rate translation is requested, DO NOT USE pertot records. Translate each ledger amount at the exchange rate in effect on its Transaction Date. Round the final balance.
We Process all posted transactions and upgrade them to the current exchange rate. For each voucher that has a gain or loss, we post the mount to Accounts payable and the Gain or Loss Account.
FINANCIAL STATEMENTS
Users are able to print Financial Statements in different currencies. Prior to SYMIX 4.0, they could only accomplish this when they were consolidating multiple divisions. The “final” Financial statement would be printed in the currency of the division running the report. The exchange rates used to convert the data were the ones stored in the division running the report. Now in V4.0, users can choose which currency they wish to print their financial statements in. The exchange rates used to print the report are the rates stored in the database that contains the ledger records being processed. The translations are for display purposes only. No posting of any kind takes place & no gain or loss is calculated.
Translations are defined for each line of the financial statement. Users choose to use the Buying or Selling Exchange rate. They choose a translation method: None ( no translations), Spot (historic rate for each transaction), Current ( current exchange rate), Average Period (weighted average exchange rate in effect for the accounting period for each transaction), End of Period (exchange rate in effect at the end of the accounting period for each transaction)

Payroll Generation: pr/pr-trxg.p, main logic in pr/pr-trxg1.p

Payroll record prlog can be generated from 4 differ sources:

1. Manufacturing transaction jobtran.  The Generate Payroll From field on the Employee Master must also be set to M. The summarized transactions include: WC Labor, WC Machine Time and Job Labor.  prlog.posted-from = “M”.
2. Manufacturing Project transaction projtran.  The Generate Payroll From field on the Employee Master must also be set to M.  prlog.posted-from = “M”.
3. Time and Attendance record timeatt.  The PR From field must also be set to T. Total Hours are calculated on the time between clock-in and clock-out with all break times subtracted out. After each time & attendance transaction is summed to the Payroll Transaction record, the time & attendance transaction status is changed from Unposted to Posted.  prlog.posted-from = “T”.
4. Attendance transaction prhrs.  When the reason code is set to Vacation, Holiday, or Sick, the record is treated as if it were the Vacation, Holiday, or Sick record entered into the Payroll Hours file.  prlog.posted-from = “P”.  Noted that prhrs record got deleted after the process (prlog created.)
5. empabsence.  prlog.posted-from = “X”. 
   

Pay Rate calculation.   pr/calcrate.p

Example:        run pr/calcrate.p (recid(employee), prlog.hr-type, true,
prlog.shift, prlog.work-date, FALSE, OUTPUT prlog.pay-rate).

You may need to scan your database for bad records and bad blocks if you receive an error message which points  to data corruption (such as 1124 and 16).


The database repair utility is a function of PROUTIL and the _proutil executable. It is executed like any other  PROUTIL function, however there are a number of prerequisites to using the utility. Make sure you have done the following before using the utility:

– Always have a current backup of the database.
– The server must be down and the database not in use.
– If possible, bring the database into sync by having a normal shutdown where there are no incomplete  transactions to be backed out.
– Truncate the before-image (BI) file.
Do not skip this last step. You must attempt to truncate the BI file. Only if there is BI corruption will you  receive an error during the truncation, and then you can decide whether or not to skip recovery and use the  force access (-F) option to start dbrpr. The -F option bypasses the transaction recovery and that can compromise database integrity.

Use the following command to execute the database repair utility:

    proutil <dbname> -C dbrpr

On VMS, use the command:

    PROGRESS/UTIL=DBRPR <database name>

After the command, the version of your Progress software is displayed, followed by the main menu:

               DATABASE REPAIR MENU
               --------------------

          1. Database Scan Menu
          2. 
          3. 
          4. Dump Block
          5. 
          6. 
          7. 
          8. 

          Q. Quit

          Choice: _

The only options of interest to this discussion are 1, 4, and Q.

Option 1, Database Scan Menu:

This option allows you to test the format of database blocks and records. Once you have selected option 1 the following menu appears:

               DATABASE SCAN MENU
               ------------------

          1. Report Bad Blocks
          2. 
          3. 
          4. Report Bad Records
          5. 
          6. 
          7. 
          8. 
          9. 
          A. Apply scan to all areas    <---- v9 only

          G. GO


          Choice: _

The menu allows you to select as many options as you want, then runs all the options together.
In order to select an option, enter the option number and the press the ENTER key; the menu then reappears with the word “ON” to the left of the option you activated. To deselect an option, just enter the option number again.

Of the choices displayed in the ‘Database Scan Menu’, only 1 and 4 are of interest to database scans.
Option 1 checks for bad blocks by making sure that some data in the block header is consistent.
Option 4 carries out a more in-depth check by verifying the format of each record.
When database corruption is suspected, it is best to activate both of these options, so that a complete  database scan is carried out.

In version 9, option A is available too, and you should select it to make sure that your scan is run across the  whole database, rather than only on the current storage area (by default the Schema Area).

When all the options you want to run are marked, enter G (go) to execute the selected items.
You will be asked the following question:

          Scan Backward (Yes/No)? _

Answer “n” for best performance. By scanning forward, Progress can take advantage of some OS and hardware optimization for sequential reads.

Following is a sample output from dbrpr against a copy of the version 8 sports database.

Scanning E:\test\test.db
From dbkey 32 to dbkey 3648

Can't find Blk 128 (4096)


Total Records:  1870
Continues:      2
# Dumped:       0

The report shows that the scan cannot find a block 128. At this point, you should contact Progress Technical Support for information on how to approach the problem.

Following is another sample:

Scanning E:\test\test.db
From dbkey 32 to dbkey 3648

Bad File Number:-6 Len:255 dbkey:995


Total Records:  1870
Continues:      2
# Dumped:       0

The record scan detected a bad file number in a record. This is a case where the actual record with RECID 995 is damaged.

You may want to capture the output from dbrpr for your own analysis, or in order to forward it to Tech Support. In this case you should proceed as follows.

– Generate a text file which contains the input that must be fed to dbrpr in order to carry out the scan you  need.
For example, in version 9, to run a full scan on all storage areas, generate a file like:
1 # Select the Database Scan Menu
1 # Activate the ‘Report Bad Blocks’ option
4 # Activate the ‘Report Bad Records’ option
A # Activate the ‘Apply scan to all areas’ option
G # Select GO and start the scan
N # Answer No to ‘Scan Backward (Yes/No)?’
Q # Select Quit once dbrpr returns to the main menu

All lines must be terminated with a carriage return. Beware that the text after and including the hash signs  (#) are comments and should not be included in the text file.
– Once the file is finalized, run:

    proutil <dbname> -C dbrpr < [your file] 2>&1 > dbrpr.log

All the output from dbrpr will go to dbrpr.log.

Option 4, Dump Block.

While you are at the main menu, there might be another piece of data that a Progress Technical Support Engineer  needs to help you; hex dumps. You might be asked to dump a block for analysis by technical support.

Follow these steps to dump a database block using dbrpr:

– From the main menu select Option 4, Dump Block. The following prompt will appear:

          Enter dbkey:

– Enter the dbkey. The block is dumped to a file called <dbkey>.dmp.
– Select Quit from the main menu to exit the database repair menu.

1) To move around the db extent to differ location

• Bring down the DB and make a backup of the DB.
  
• Copy the db extent file, such as .b1, .b2 or .d1, .d2, to a new location
• Edit the .st file to point to the new location
• prostrct repair db-name [.st description file],  This will update the .db with the .st with new file location
• prostrct list db-name, verify the change and insure .db, .st consistent

2) To Add a new extend

• Create a new.st file that only contain the new extent information.
• prostrct add db-name new.st
• This will turn the last flexible extent to fixed extent with the current file size. 
  
• If want to make the file size of all fixed extent consistent, edit the .st file, then use procopy to copy the DB from a backup.

3) Another way to do all of above, like changing the location of extent and changing the file size for fixed extent.

• Edit the .st file with what ever change you want
• Use procopy to copy the DB from a backup to rebuild the new DB.

The MRP Generation algorithm
– SL6 and lower (Progress versions)Regenerates the m-day calendar if it requires regeneration
If full regeneration being run:
Delete all requirement records for all items (mrp table)
Delete all transfer order receipts for all items (rcpts table, ref-type “T”)
If lasttran counter for PLNs is > 9000000:
Reset it to 1
Delete all planned orders for all items (rcpts table, ref-type “N”)
End
End

Reads through item master by low level code, by item:
If type is Other and net change is on, turn it off and skip to next item
Turn on the I/M net change flag for all current materials of the item
Get the following from item’s product code (if not ?) or planning parms:
Forecast look ahead and behind
Job reschedule tolerance factors
PO reschedule tolerance factors

Generate independent requirements:
Delete forecasts older than (today – forecast look behind)
Consume forecasts with customer orders
Create requirements for:
Outstanding forecasts
All unreserved COs
Transfer orders where
If “Whse Considered For” = Single
From Whse = default warehouse
If “Whse Considered For” = All
From Site is the current site
Project resources
End – Generate independent requirements

Create receipt records for transfer orders where:
If “Whse Considered For” = Single
To Whse = default warehouse
If “Whse Considered For” = All
To Site is the current site

Delete all of the item’s exception messages
Calculate beginning projected on hand (nettable on hand – qty reserved)
Generate applicable exceptions regarding beginning on hand:
On Hand below Safety Stock
Initial Qty. On Hand negative

If beginning on hand below safety stock and there are no past due requirements:
Look for first receipt for the item (job, PO, PS, transfer order, PO req)
Generate exception if appropriate:
Reschedule Receipt
Sched. Rcpt. past due
If no receipts or not enough to cover quantity below safety stock:
Create planned order (PLN) in temporary table
End

Read through all requirements for the item:
If requirement source is x-reffed, find x-reffed receipt (po, job, etc.)
If x-ref is valid, skip to next requirement
Generate error (then continue on) if:
Receipt is not cross referenced back
Receipt has a status of complete
End.
Generate exception if appropriate: Date outside shop calendar

If not an MPS item:
Generate exception if appropriate: Requirement past due
If requirement source is x-reffed, find x-reffed receipt then
Generate appropriate exception:
X-ref Rcpt: Resched
X-ref Rcpt: Qty
Sched. Rcpt. past due

If requirement is a forecast and “Use CO, Fcst or Both” is Forecast
Deduct full forecast amount from POH
Else deduct outstanding (i.e. not consumed by CO) amount from POH
Look for next receipt for item
If found, generate exception if appropriate:
Reschedule Receipt (if outside PO and job reschedule tolerance factors)
Sched. Rcpt. past due
If no more receipts or not enough to cover quantity below safety stock:
Create planned order (PLN) in temporary table
End – not an MPS item
End – read through all requirements

Read through all existing planned orders for the item (from the last MRP run)
Match them with temp table PLNs created in this MRP run
If old PLN and new PLN match up, set date and quantity to new record’s values
If old PLN exists and new record does not, it is no longer needed so it is deleted
If old PLN does not exist for new PLN, create a new real planned order
End

Read through all non-PLN receipts (jobs, PO, etc.)
Generate exception if appropriate: Sched. Rcpt. not needed

Pass all requirements to children:
Pass PLNs to current BOM, creating PPLN requirements
Pass job receipts to job BOM if exists or current BOM if not, creating PJOB requirements
Pass PS receipts to PS BOM if exists or current BOM if not, creating PPS requirements
Turning on each child’s net change flag
End

Turn off the item’s net change flag
Set the item’s Last Generation date

If the item’s Source is Transferred, it’s not an MPS item and its Supply Site is not blank
Set the flag that indicates planned transfer orders need passed for the item

End – read through item master

Turn off the behind-the-scenes flag that indicates a full regeneration must be done

If an error occurred (invalid cross reference detected):
Display “MRP Processor was not successful”
Else
Look for item master records with their net change flag enabled
If there are any (none should be) display:
“WARNING – Low Level codes improperly set”
“MRP Generation is not complete – re-run net change”
If not being run in the background:
Ask if the Item With Net Change Flag report should be run.
End. else
Display “MRP Processor was successful”
End

If multi-site and “Post Planned TOs” = Auto:
If not being run in the background
Display “Put Planned TRN Requirements will be performed”
Read through all transferred items for which the pass planned transfers flag is enabled:
Pass all PLNs for the item to the item’s Supply Site as TPLN requirements
Set the net change flag for the item in the supply site
display success or failure message for “Put Planned TRN Requirements”
End

Delete all records from the material planner workbench that were created from PLNs

1) For the original DB, dump the .df file.  Can be done by

• go to Progress -> Data Administration -> Admin -> Dump Data and Definitions -> Data Definitions; or
• Syteline admin -> Admin -> Database maintenance -> Dump Data Definitions.

2) Create a new DB from Progress empty DB: Progress -> Data Administration -> Database -> Create.  Make sure create from correct code page empty db. For example, if original DB is GB2312, then use the empty db from prolang\sch\gb2312.

3) Load the .df file to new DB. Progress -> Data Administration -> Admin -> Load Data and Definitions -> Data Definitions

4) Compile code against to this new DB, the .r code should be comparable with original DB.

Copy DB in Windows
1) Command prompt, CD to DB directory.
2) %DLC%\bin\procopy oldname newname.  newname can be full path like F:\db\shimadzu\shimadzu
3) This will copy over the .db, .b1, .d1, .st and .lg.  Then manually copy over any .pf and update .pf accordingly.