/*************************************************************************************************** VB and SQL Change Notes **************************************************************************************************** DO A SEARCH OF CONSOL for / - illegel characters added by Merge ****************************************************************************************************/ exec upd_config 'CURRENT_WMS_VERSION', 'Required version of CRiSTAL WMS exe', '5.401.908.076', '{SYSTEM}', 'SYSTEM', 'SYSTEM', 'FREE', 'Y', 'N' exec upd_config 'EXEVERSION', 'Optimized EXE Build', '5.401.908.076', '{SYSTEM}', 'SYSTEM', 'SYSTEM', 'FREE', 'Y', 'N' EXEC system_update_history_update NULL, '5.401.908.080', '5.401.908.080', 'CONSOL UPDATE', '{SYSTEM}' /*************************************************************************************************** Utity Procedures *************************************************************************************************** 1. Util_CleanProductIDDupplication - CREATE DATE: 08 Mar 2020 To clear duplicated Product ID from Product Definition: - PRODUCT MASTER - PRODUCT UOM - PRODUCT ZONING Process: Select DISTINCT of duplicated records. Extract 1 of the ROW ID DELETE duplicated records other than the extracted ROW ID 2. util_delete_client_product - CREATE DATE: 06 Aug 2008 DELETE data related to PRODUCT CODE and the CODE itself Transaction data must first be cleared using Clearing Client Transaction 3. util_DatabaseConvert2MasterClient - CREATE DATE: 09 Nov 2019 Convert database to Master Client Control - namely enable all client to share Product ID, Customer/Delivery Destination as defined under the assigned Master Client. NOTE: Once a client is designated as Master Client, it cannot be INACTIVE or re-code. This is a wrapper that call SQL_ConvertToSlaveClient to delete 'master data' of individual client and set it to 'Slave Client' of the specified Master Client. MASTER CLIENT is designed for Licensed Warehouse operations as product are common to all Clients. This help to reduce diskspace/ database size requirement. 4. SQL_ConvertToSlaveClient - CREATE DATE: 06 May 2018 Convert specified Client to Slave Client 1. Update Master Client 2. Delete all Product related data 3. Delete Customer related data 4. Delete Supplier related Data This is called by util_DatabaseConvert2MasterClient as the processing routine. 5. auto_confirm_task - CREATE DATE : 01 Dec 2014 Confirm by CLIENT all tasks in STOCK MOVEMENTS Input parameter: - MAKER - CLIENT 6. SQL_DeleteWMSImportReceipt - CREATE DATE : 20 Apr 2020 Written clear erroneously uploaded opening stock via STKRECEIPT excel templates Utility designed to faciliate import of transactions history from other system with UTIL_SalesOrdersOpenBatchProcess 7. UTIL_SalesOrdersOpenBatchProcess - CREATE DATE : 20 Apr 2020 Designed to batch process Sales Orders imported via SO or SO2 excel templates. Utility designed to faciliate import of transactions history from other system with SQL_DeleteWMSImportReceipt 8. sql_PayableReversalBatch - CREATE DATE : 30 May 2020 Written to batch reverse posted Account Payable that are in 'WAITING' This is to facilitate testing of WMS IMPORTS' APAYABLE *************************************************************************************************** UDATE HISTORY *************************************************************************************************** 09 Jan 2007 - DL : Update EXE version 5.395.2t - 21 Oct 2008 - DL 5.395.2w - 15 Feb 2009 - DL 5.395.3 - 26 Feb 2009 - DL - Deployed : Logwin.EU 5.395.3c - 01 Jul 2009 - DL : Trident, Logwin SG TR, Logwin EU TEST 5.395.3c - 11 Aug 2009 - DL : Logwin TEST / EU 5.395.3d - 14 Aug 2009 - DL : Deployed Trident, Logwin - EU, SG, ZA, DB 5.395.4 - 23 Nov 2009 - DL : This version incorporate patches - Receipt Detail when invalid item is input, the last line in the detail grid is duplicated - Menu - Favorites group result in second menu option no functioning - Location zoning - enhanced to allow toggle blocking of locations - VA Activity update - block updting of activities that are closed - Merge System Configuration, System Parameter, UDF Field Names, Reference Numbers Maintenance, System Log (Audit Trail) Maintenance - Introduction of Pick Zone TRansfer to facilitate FMCG operation - Resigned Kitting as Single Form function - Database backup function - Miscellaneous Maintenance Tools - Incorporate auto-email of receipt - Configurbable at Client UDF - EMAIL_RECEIPT_PROCEDURE - To activate, specific the Stored Procedure - email_receipt_get - despatch_grid.record_no - need to disable IDENTITY and and row_id (with IDENTITY) - add procedure next_dg_record_no to generate number - modified to avoid error from RESEED of the record_no - Incorporate serial number audit trail on product_serial_movements Following affected and modified - close putaway task - close pick task - generate do - reverse trx receipt - reverse trx sales_order - reverse trx do **************************************************************************************************** 1. 22 Nov 2019 - DL : Standardise BACKDATE STOCK computation to procedure acc_stock_net_compute by incorporating procedure stock_selected_date_compute to eliminate discrepancy between Billing and Reports calculation Following procedures are affected and modified: 1. rpt_billable_summary 2. rpt_licence_storage 3. rpt_required_date_stock_by_product_group 4. rpt_required_date_stock_ZERO_GST 5. rpt_required_date_stock_item 6. rpt_required_date_stock - Renamed various backdate stock reports. 2. 29 Nov 2019 - DL : Procedure rpt_stockmove_summary - debug to incorporate operations process of Techno Associe that include grade changes delayed receipt entries and delayed sales orders update. 3. 08 Dec 2019 DL : Added procedure ExcelExportProductWIData to export Product definition data under WMS Exports to facilitate WMS Imports by generate data in Excel format in accordance to Imports requirement Added following functions to facilitate this procedure: 1. fnProduct_DataValue - retrieve PRODUCT DATA string value 2. fnProductPriceTypeCurrency - retrieve specific price currency 3. fnProductPriceByType - retrieve specific price by type - RETAIL, FOB, LANDED... Rename orignal price type functions to reflect its purposes as follows 1. fnProductPriceCustomer 2. fnProductPriceCurrencyCustomer 4. 10 Dec 2019 DL : frmLabels - Layout modified with update layout control in Form_Resize Debug UOM selection and Item ID selection. Procedures updated: 1. label_utility_get - do not deploy to 5.401.908.66 and EARLIER 5. 10 Dec 2019 - DL : Email Schedule - Debug email_schedule_next_due_update - error in updating next due date. - email_schedule_due_get - incorporate ORDER BY on next_due - Disable change of EMAIL CODE in a defined job - require user to user and add new if change required - Incorporate cmdClear when Client is changed 15 Dec 2019 - DL : Added dbo.fnColumnWidth(, ) to return / retrieve a field width to facilitate limiting an input parameter to prevent truncation error when inserting / updating a field. 21 DEc 2019 - DL : Receipt Check In - Enabled PO Number to be specified even though there is no PO lines data - this is to cater to operation where PO number need to be captured but there is no PO data. - DAP Procedure modified: 1. item_code_help 2. ReceiptDetailUpdate 23 Dec 2019 - DL : Added email schedule options as follows: 1. EMAIL_WHSELOCATIONOVERLOAD - This report on locations when weight of stock in llocations exceed the load capactity of location 2. EMAIL_WHSEPENDRECEIPTCHECKIN - Report on Check In on RF devices that are not completed / putaway - Also report on BLOCKED stock. 25 Dec 2019 - DL : Delivery Orders | Cancel Sales Order Patch and Revamp SQL procedures: cancel_despatch_so and its sub routines: 1. SubUploadReceipt of version 400.03 2. ReceiptMasterUpdate2 3. receipt_checkin Patch VB - SO List grid display not refreshed correctly. Process: 1. Create Receipt from the Delivery Detail with SubUploadReceipt a. If the Qty of a record is greater than defined Pallet LUOM Qty, the reord will auto split in accordance to the PALLET LUOM QTY 2. Set Delivery Details.DELIVERY = CANCELLED + sales order and status = 'CANCELLED' 3. Execute RECEIPT CHECKIN to generate PUTAWAY tasks 4. Users to putaway and confirm via Warehouse Tasks 28 Dec 2019 - DL : Report procedure - rpt_stockmove_summary - originally scheduled for redundancy: Enhnahced to return stock attributes for movements. This allow us to use it for furture enhancement of report template when requied to show stock attributes. 29 Dec 2019 - DL : Modified VB to display Users'Company per defined in User Profile. This is to facilitate Multi-Tenants version deployment. 12 Jan 2020 - DL : Enhance WI IMPORTS wi_upload_client - Add STREET3 and BILLING_STREET3 - Add REMARK to Client Master and incorporated in wi_upload_client - Client Profile updated to enable input of Remark 14 Jan 2020 - DL : Modified STKSERIAL - changing Receipt, Expiry and Production Date input parameters from DATE to NVARCHAR as upload reture -217... error when the date input is not in ISO format/ Do conversion to DATE if ISDATE() check is positive. 15 Jan 2020 - DL : Modified RPT WORKS ORDER6 to return RETAIL Price and Currency - required by DPEX HKG's James 29 Jan 2020 - DL : Enhanced sub routine procedure upload_stock_receipt_routine by incorporating Stock control Attributes check - validate_item_attributes - to ensure required attributes are provided. This synchronise the behauior with Receipt Check In function. Affected WMS Imports template procedure: 1. wi_upload_stock_receipt 2. wi_StockBalanceReset 29 Jan 2020 - DL : Release WMS Imports template STKRESET - which calll procedure wi_StockBalanceReset Template is designed to enable users to update a Client Stock Balance via Excel template after a physical count without using the Stock Count funtion. The process of the templates allows a new stock balance to be initialise while retenting the transactions history. The process: 1. Zeroise existing stock balance - This is done by adjusting existing to 0 (zero) 2. Update the excel spreadsheet record as the new stock This is requested by Numajiri where although stock balance are accurate, the pallet location record are incorrect. This template allow them to peroform a housekeeping, record the pallet location and reset the pallet location records. 03 Feb 2020 - DL : Enhanced frmMenuLogo to retrieve and display Company Logo from ProductDocImage based UserID's assigned Conpany. If not defined or Logo not uploaded, display CRiSTAL. Build 5.401.908.069 10 Feb 2020 - DL : Enhanced frmWMSImport - enabled creation of WMS Imports Excel template when template is not available from template URL - template is created and saved in C:\Template\WMSImport. As the ExportEXCEL requires to have a row, the template is created with a row. The rows are to be deleted first before use. Enabled create function in WMS Exports as option 'WMS Imports Excel template' for EXE version earlier than 5.401.908.069. User need to know and specify the WMS Import template ID and save file accordingly Build 50401.908.070 =================== 13 Feb 2020 - DL : frmUserProfile & frmLogin - Enhanced User login by encrypting login userid when submitting to database by introducing UserKey which is VB Cipher_Code() encryption of the User ID Procedures modified: 1. UserProfileUpdate.sql 2. login_check.sql 3. check_login Added command button cmdUserKey in frmUserProfile. It is visible only if the login user have Developer permission. Click cmdmUserKey batch generate and update USER PRODILE.UserKey field. Build 5.401.908.071 =================== 20 Feb 2020 - DL : Bug patch frmQueryItem to correct popagate error for Attribute and Stock List which display wrony data in Item No combobox. 20 Feb 2020 - DL : Enhanced WMS Imports by added upload of Client Billable Services setup using procedure 'ClientBillableServicesUPDATE'. Excel Template: 'BILLSERVICE' 22 Feb 2020 - DL : Enhanced Item Inventory Query - frmQueryItem adding following funtion: 1. Custom Permit No Query - Transactions - Summary - Change to frmPopupHelp_grid_get facilitate search of Custom Permit No - Enhanced GridboxTransactionSummaryGet and gridbox_item_movement_get to return resultset search by Custom Permit No 2. Location Stock Query Added above tab option in app_form_control_update 24 Feb 2020 - DL : Enhanced control on Device Registry by incorporate Hashkey check to ensure not backdoor adding of Device ID Procedure modified: 1. device_registry_update 2. device_registry_get Build 5.401.908.072 =================== 27 Feb 2020 - DL : frmQueryItem - Patched Serial Numbers Query option - fraSerial not displyed when SERIAL option is selected Incorporate into frmQueryItem procedures Exclusion of RM.STATUS = CANCELLED 03 Mar 2020 - DL : frmQueryItem - enhancement to incorporate query: - Custom Permit Transaction - Custom Permit Summary - Location stock Build 5.401.908.073 =================== 04 Mar 2020 - DL : Enhanced procedure UPD SUPPLIERS to auto update TRADE PARTNER table to faciltate Accounts Payable fuction. The update auto enable Supplier as CREDITOR which is editable in Company Profile | Partners 05 Mar 2020 - DL : WMS IMPORTS Enable list of option to be user choice to sort by - Code - Description Modified procedure get_report_procedure ---------------------------------------------------------------------------------- Modified User Interface to show synopsis of import option. Subpress display of procedure name if access is less than 50 ---------------------------------------------------------------------------------- STKRECEIPT and SO2 Incorporate unload of Custom Permit No per request by J&R Logistics Roy to meet Custom House requirement 08 Mar 2020 - DL : system_report_master_update Block from SITE REPORTS from using same name as system reports to minimise confusion 09 Mar 2020 - DL: client_help_get Debug - patch sub-string name matching 20 Mar 2020 - DL : frmReceipt Added function to input multiple pallets (with same quantity) of an Item No with single entry Auto generate multiple pallets records. 20 Mar 2020 - DL : wi_product_dutiable_update Correction of misspelled input parameter @brans - should be @Brand Incorporate VALIDATE_ITEM_NO to prevent erroneous messages 21 Mar 2020 - DL : SalesOrderUpdate2 : Enhanced messaging of Delivery Date bing earlier than oldeest stock This is to prevent integrity issue when reporting transactions by schedule date 21 Mar 2020 - DL : wi_upload_so2 Auto assign LINE NO if not defined in Excel spreadsheet. Note if file uploaded multiple, detial will simplly increase as new line Incorporate 1. create Sales Order with user configureable Client Specific SO Status 2. Product ID validation - this is to facilitate default of despatch which is based on Product Zone. 25 Mar 2020 - DL : frmReceipt Enhanced to enable to define new Product ID (Item No) for non attribute control product using wi_upload_product_basic procedure. Input parameters limited to: - Client - Item No - Item Name - Product Class - Product Group - Billing Group - Unit UOM - Unit Height - Unit Depth - Unit Width - Unit Weight - Whole (Pack / Carton) Quantity (including 1) (Storage) Zone is default to Zone defined for existing product of Client 28 Mar 2020 - DL : GET_UOM Tighten control on StorageUOMFlag - If 'Y' return only Storage UOM such as PALLET, CONTAINER - If not specified or 'N', return onlu non-storage (pick or issue) UOM This is to facilitate enabling user to define UOM form UNIT TO PALLET as we use the StorageUOMFlag tp terminate 'next level' Build 5.401.908.075 31 Mar2020 - DL : VB and TSQL Updated Pallet Numbering comtrol when system is configured to use Laction Address As PALLET ID - modified GET PATE NO input parameters to include Pallet and Location: - perform check whether Location is unique to system or multiple warehouses use same address. - if unique, use Location Address as Pallet ID otherwise prefix with Warehouse. - VB - Remane GET PALLET LABEL to NextPalletID - enhanced to in incorporate Optional Parameters Warehouse and Location - Thus moving check and control to SQL procedure 31 Mar 2020 - DL : frmProductUOM - enable option to define UOM from UNIT level first Build 5.401.908.075 12 Apr 2020 - DL : Merged display/list of Stock Attributes and UDFs into 1 grid to simplify and ease users entry and redure the need to switch frame when input them in following forms: 1. frmPOEntry 2. frmMRA 3. frmRCEntry 4. frmSOEntry OrderLineItemAttributeGET - enhanced to incorporated UDFs from procedure UDFGridGet - the detail's UDFs. Build 5.401.908.076 22 Apr 2020 - DL : rpt_product_id_inventory Update to incorporate Master/Slave Client control 25 Apr 2020 - DL : Revise frmPOEntry logic to based on Sales Order Entry to make it behave similarly Build 5.401.908.077/78 05 May 2020 - DL : Patch PO to display REMARK that is input manuallly or uploaded. 11 May 2020 - DL : Added HSqty field to RECEIPT DETAIL AND SALES_DETAILS to facilitate generating of Customs Declaration excel spreadsheet for upload to TradeWeb This is updated by Sales Order Entry and Receipt Checkin / ASN. Computation of HSqty is handled by fnHSQty SQL function 15 May 2020 - DL : Enhance grid control for item attributes and UDFs entry - combine them into 1 grid. Added tab button to hide fields that users don't want to see. 17 May 2020 - DL : Update of Accounts Payable Entry to record GST Rate and auto compute GST amount. Build 5.401.908.079 20 May 2020 - DL : Debug - synchromizae sales_order_stock_available_check logic with get_pick_location invoke 'CLIENT_PARAMETER' | 'CONTROL_DATE_MATCH' on Expiry Date and Production Date/ 22 May 2020 - DL : Simplified maintenance of TRANSACTION CODES enbling defined code to be ebable as AR, AP or both 24 May 2020 - DL : Enable entry of GST % in Acciunt Payable Entry to auto compute GST amount - manual entry unchanged. Build 5.401.908.080 28 May 2020 - DL : Enabled WMS Imports to upload (APAYABLE) Accounts Payable data 30 May 2020 - DL : Normalization on Bank data. Streamline maintenance of Company's Bank Code, Name, Account No, Swift Code in ACC BANK ACCOUNT Table. Drop parameter 'COMPANY PARAMETER' | 'BANKER', 'BANK ACCOUNT', 'BANKSWIFT' from Client Parameter Tbl 05 Jun 2020 - DL : Dropped field SALES DETAILS.DISPLAYED STATUS and replace with dbo.fnSOLineDisplayStatus() to normalise the update process and prevent discrepancy. 07 Jun 2020 - DL : Debug AUTO UPLOAD MAIN PROCEDURE | Sales Orders2 and eCom - release complete uploaded sales orders not change from 'UPLOADING to DATA ENTRY / WAITING. Error in subquery statement. Added SessionID to Sales Orders table to facilate drop of subquery and use SessionID to filter the change. Followinf procedures modified: 1. auto_upload_main_procedure 2. auto_upload_sales_order2 3. auto_upload_sales_order_ecom Also replace table variables to use temp table in auto_upload_main_procedure and incorporate indexes on temp tables 15 Jun 2020 - DL : Enhanced VB Item Inventory Query Replaced Attributes query input textboxes to Gridbox - this is to enable adding of new query attributes without need to modify EXE Same is done on the attribute report option. 16 Jun 2020 - DL : Procedure: pick_sales_order Synchronise logic with procedure sales_order_stock_available_check - Namely, ignore attributes that is not flagged as required in Product Master even though it may be specified The procedure ItemStockAvail that return the QTY AVAIL is not changed as it check stock based on the input parameters. This rectify issue that arised due to interface uploaded orders which does not filter data as the Sales Order Entry - namely allow attributes input only when enabled. ***************************************************************************************************/