This chapter contains this topic:
The Effective Thru Date Update program (P4028) is a DREAM Writer that allows you to perform a mass update of the date in the Effective Thru Date field in all records where the Effective Thru Date matches a specific date.
Many records include an Effective Thru Date field. If you do not enter a date when you add a record, the system calculates the effective thru date from the default value in the Data Dictionary for the Century Change Year (#CYR) and enters it in the record. For example, if the Century Change Year is 17, and you do not enter a date, the system enters 12/31/2017 as the effective thru date.
When you change the default value for the Century Change Year, you can use this program to recalculate the effective thru date in records that contain the default value from the Century Change Year.
Prior to running this program, you must determine the existing effective thru date that you want to update. When you enter this date in the processing option, the program selects only those records with an effective thru date that matches your date.
The process flow for the program is as follows:
Examines the files from User Defined Codes (UDC) 40/EF.
Retrieves the list of fields for each file.
Verifies the data item for that field is in UDC 40/EI for each file.
Builds and executes an SQL statement to update all records in the file when the value in the field matches the date you want to update.
Adds the number of records it updates for the file to the report.
The files the program processes are in UDC 40/EF. If you want to exclude any files from the update, enter 1 in the Special Handling Code field in the UDC table and the program disregards that file.
You can include a file with fields to update by adding it to UDC 40/EF. Ensure you add the data item for the field that you want to update to UDC 40/EI. In addition, you must designate the file in the Software Versions Repository Master (F9801) as a Physical File by entering PF in the Function Code field. You do not need to define the file in the program or recompile the program.
The program recognizes a field as being an Effective Thru Date field when the corresponding data item is in UDC 40/EI. For example, the Base Price File (F4106) contains the Expired Date (BPEXDJ) field. The program updates this field as an Effective Thru Date field as long as the data item EXDJ exists in UDC 40/EI. If you want to exclude any fields from the update, enter 1 in the Special Handling Code of the UDC for the data item and the program disregards that field.
The program generates a report listing the number of records in each file that it updates.
When the program executes the SQL statement and it fails to update a record in a file, the program ceases without updating any remaining records in that file and the program progresses to the next file. The main conditions under which this occurs are:
Record lock
Duplicate key error
If an error causes the program to update only part of a file, the program:
Issues a break message regarding an SQL error
Generates a job log
Prints an error message on the report for that file
Review the job log for information about the cause of the error. The break message also provides an SQLSTATE code that you can use to locate more information about the SQL error.
After reviewing and correcting the condition causing the SQL error, simply rerun the program to finish updating the records.
A duplicate key error can occur because the field the program is updating is part of a unique key and updating the field to a new date creates two records with the same key. The job log contains information about the record causing the error. You must manually correct the record before the program can update the balance of the records.
From Inventory Management (G41), enter 27
From Inventory Management Advanced and Technical Operations (G4131), choose Global Updates and Purges
From Global Updates and Purges (G41311), choose Update Effective Thru Dates