247211
247211-SS-7B6L2V Control Order Date Creation
Copyright OBS Logistics © 2008
The information contained herein is the property of OBS Logistics and is supplied without liability for errors or omissions. No part may be reproduced or used except as authorised by contract or other written permission. The copyright and foregoing restriction on reproduction and use extend to all media in which the information may be embodied
Functional Overview
Client Requirement
A new system parameter should be introduced called ORD_FUTURE_LIMIT_DAYS which will control the number of days in the future from the current date for which an order can be created. This should be configurable by customer at database level. If this is set to 20 and a user tries to create an order for 25 days in the future an error message should be displayed telling the user that this cannot be done as the max value for creating orders in the future has been exceeded, possibly telling them what the value is.
There is an existing parameter called LBO_FUTURE_ORDER_LIMIT_DAYS which may possibly provide some code that can be re-used or copied. Dave can this limit be set to 365 days from the date of creation / receipt of orders
Solution
Create a new record in the ADM_USER_PARAM table called for a parameter called ORD_FUTURE_LIMIT_DAYS. This parameter must be modifiable by the user in the Administration → System → Parameters screen. The value of this parameter will be 365.
In the orders form , when adding a new order record and the schedule date is populated, a function will run which adds the ORD_FUTURE_LIMIT_DAYS parameter value to todays date and compares the value with the schedule date. If the schedule date is greater, then an error is displayed which will tell the user the maximum schedule date allowed – todays date + the value of the ORD_FUTURE_LIMIT_DAYS
This check must be applied to all areas where orders are created:
1. Bookings When the user selects Create TI’s, a pop up screen is displayed where the user is able to enter a schedule , at this point, the schedule check will be run.
2. Manual Keying The orders screen as described above, when a new order is created the schedule check will be launched to confirm that the order is created within the limits of the parameter.
3. CSV Imports The IMP package runs a function called Process_TI_Order where the order records to be imported are processed. Within this function we would include a cursor which will loop through each order record and perform the schedule check. If there is an error with the schedule date this will be handled by the package in the same way that existing errors are handled.
The IMP package also calls a function Process Bookings where booking records are processed before being imported into the system. A cursor will be created to check the schedule name on each booking record. As above, errors will be handled in the same way that existing errors are handled in the package.
4. Change Schedule Date This is current development where users will be able to change the schedule date of a planned trip. This check will be added to the functionality.
5. Templates In the templates screen, when the user selects to generate orders from a template, a pop screen appears where the user must enter the schedule and then select generate again. At this point , before the order is generated , the schedule check will be run.
Scope
This change will be applied to system version 10.5.0 on CONTST and once approved CONPRD.
Data
New record created in the ADM_SYSTEM_PARAMETER table called ORD_FUTURE_LIMIT_ DAYS. This will be created with a value of 365. This value will be updatable by the user using the parameters screen.
Functional Description
This development is based on three requirements.
• A new system parameter is available for limiting the creation of orders in the future • This parameter can be amended by the customer • The parameter will be used to validate the schedule name of all new orders. This includes orders added manually via the order screen, orders imported via CSV files, orders created via bookings and orders created from templates.
Adding the New Record
Insert into ADM_SYSTEM_PARAM (PARAM_NAME, VALUE, DATA_TYPE, MAX_LENGTH, DISPLAY, USER_MODIFIABLE, DESCRIPTION) Values (‘ORD_FUTURE_LIMIT_DAYS’ , ‘365’,’N’,5,’Y’,’Y’,’Maximum Number of days in the future that an order can be created’)
Validating the Schedule Date
Form Validation
For instances when the schedule date is added via a form, by user input, when-validate triggers will be used to capture the schedule date and convert the information to s date format.
The check will then be run, which will add the parameter value(ORD_FUTURE_LIMIT_DAYS) to the system date and then compare this new date with the schedule date. When a number is added to a date, the system defaults the value to be in days.
If the schedule date is greater, an error will be issued and the value of the maximum date allowed will be displayed.
Form Validation is relevant to manual orders, bookings and order templates.
For Order templates, the schedule date validation will be added to the when-validate-item trigger on GENERATE_ORDERS.sched_date.
For Bookings, the schedule date validation will be added to the when-list-changed trigger on CREATE_ORDERS.sched_name.
For Manual Entry, the schedule date validation will be added to a new when-validate-item trigger on SCH_ORD_UPD.sched_name.
Import Validation
For imports, the schedule date has already been assigned and we cannot validate the value at the point of data entry. For imports the validation will be carried out within the IMP package which controls data imports.
There are two imports which are relevant to the Schedule Name and they are the Bookings import and the Orders import. These two imports are validated in two functions within the IMP package.
The functions are Process_TI_Orders and Process_Bookings. Within both functions, the import data is validated one row at a time, schedule name is extracted from the import file and basic validation is applied (IsNull)
At the point where the schedule name is extracted, we will check the value of the sched_name against the current date + ord_future_limit_days. In the declarations of each function we will include a cursor to extract the system date and a cursor to extract the value of ord_future_limit. The cursors will be fetched at the beginning of the functions.
Date Variables
V_SCHED_DATE := TO_DATE(:SCH_ORD_UP.SCHED_NAME, DD/MM/YY)
Cursor CURRENT is Select current_date from dual;
V_current date;
Open CURRENT; Fetch CURRENT into v_current
Cursor ORD_FUTURE_LIMIT is Select value from ADM_SYSTEM_PARAM Where param_name = ‘ORD_FUTURE_LIMIT_DAYS’
v_days integer;
Open ORD_FUTURE_LIMIT Fetch ORD_FUTURE_LIMIT into v_days;
V_MAX_DATE := v_current_date + v_days;
If v_sched_date > v_max_date
Message ( ‘You have exceeded the maximum number of days in the future that an order can be created. Maximum date is ‘|| v_max_date);
End if;
References
| EST-247211 SS-7B6L2V Control Order Date Entry.doc | |||
| NW-73ANMM.pdf / NW-73ANMM Estimate.doc |
Glossary
Not Available
Document History
| Initial version | ||||
| Reviewed and Issued | ||||
| Change to Scope | ||||
| Reviewed Changes and Issued |
Authorised By
| Matt Crisford | Development Manager | |
| Suk Sandhu | TMSCC MTS Product Manager |
