* Procedure: import_update_load_tables
* Description: Update the Load Table used in the import process

LOCAL lcLast_Route_Code, lcLast_Vehicle_Code, lcLast_Item_Code, lcLast_Load_Number, ;
lnTotalRecords, lnCurrentRecord, lnPreviousPercent, lnCurrentPercent, lnPackRequest, ;
lnCaseRequest, lnUnitRequest, lnCount, lnPalletize_flag, lnAllow_change_palletize_flag, ;
ldDelivery_ldate, lc10Spaces, lc30Spaces, lnConnection, lcVehicleDeliveryMode, lcRouteType

LOCAL lnTime1, lnTime2, lnTime3, lnTime4, lnTime5, lnTime6, lnTime7, lnTime8, lnTime9, lnTime10, ;
lnTime11, lnTime12, lnTime13, lnTime14, lnTime15, lnTime16, lnTime17, lnTime18, lnTime19, lnTime20, ;
lnTime21, lnTime22, lnTime23, lnTime24, lnTime25, lnTime26, lnTime27, lnTime28, lnTime29, lnTime30, ;
lnTime31, lnTime32, lnTime33, lnTime34, lnTime35, lnTime36
STORE 0 TO lnTime1, lnTime2, lnTime3, lnTime4, lnTime5, lnTime6, lnTime7, lnTime8, lnTime9, lnTime10, ;
lnTime11, lnTime12, lnTime13, lnTime14, lnTime15, lnTime16, lnTime17, lnTime18, lnTime19, lnTime20, ;
lnTime21, lnTime22, lnTime23, lnTime24, lnTime25, lnTime26, lnTime27, lnTime28, lnTime29, lnTime30, ;
lnTime31, lnTime32, lnTime33, lnTime34, lnTime35, lnTime36
lc10Spaces = SPACE(10)
lc30Spaces = SPACE(30)

lnConnection = _screen.oapp.nConnection

IF NOT plAutomatedImport
oProgressWindow.update(0,,,"Update Load Database")
ENDIF

lcLast_Route_Code = lc10Spaces
lcLast_Vehicle_Code = lc30Spaces
lcLast_Item_Code = lc30Spaces
lcLast_Load_Number = " "
STORE 0 TO lnCurrentRecord, lnPreviousPercent, lnCurrentPercent
ldDelivery_ldate = DATE()

SELECT import_data
COUNT TO lnTotalRecords
LOCATE
SCAN
IF import_data.vehicle_code # lcLast_Vehicle_Code
lcSQL = "select vehicle_types.delivery_mode " ;
+ "from vehicle_types inner join vehicles " ;
+ "on vehicle_types.vehicle_type = vehicles.vehicle_type " ;
+ "where vehicles.vehicle_code " ;
+ prepare_for_sqlexec_condition(import_data.vehicle_code)
lnStart = SECONDS()
lnResponse = SQLEXEC(lnConnection, lcSQL, 'vehicles')
lnFinish = SECONDS()
lnTime1 = lnTime1 + lnFinish - lnStart
lcVehicleDeliveryMode = vehicles.delivery_mode
USE IN vehicles
*!* =SEEK(import_data.vehicle_code, 'vehicles_and_vehicle_types_vp', 'pk')
lcLast_Vehicle_Code = import_data.vehicle_code
ENDIF
IF import_data.delivery_ldate # ldDelivery_ldate ;
OR import_data.route_code # lcLast_Route_Code ;
OR import_data.load_number # lcLast_Load_Number
* route_status.route_code not populated yet so lookup route_id in route_defaults.
ldDelivery_ldate = import_data.delivery_ldate
lcLast_Route_Code = import_data.route_code
lcLast_Load_Number = import_data.load_number
lcSQL = "select route_type from routes where route_code " ;
+ prepare_for_sqlexec_condition(import_data.route_code)
lnStart = SECONDS()
lnResponse = SQLEXEC(lnConnection, lcSQL, 'routes')
lnFinish = SECONDS()
lnTime2 = lnTime2 + lnFinish - lnStart
lcRouteType = routes.route_type
USE IN routes
*!* =SEEK(import_data.route_code, 'routes_vp', 'pk')
*!* IF SEEK(DTOC(import_data.delivery_ldate,1) + import_data.route_code ;
*!* + import_data.load_number, 'route_status_vp', 'display')
lcSQL = "select route_status.route_status_id, route_status.loading_type " ;
+ "from route_status inner join routes " ;
+ "on route_status.route_code = routes.route_code " ;
+ "where routes.warehouse_code " ;
+ prepare_for_sqlexec_condition(_screen.oapp.cwarehouse_code) ;
+ " and route_status.delivery_ldate " ;
+ prepare_for_sqlexec_condition(import_data.delivery_ldate) ;
+ " and route_status.route_code " ;
+ prepare_for_sqlexec_condition(import_data.route_code) ;
+ " and route_status.load_number " ;
+ prepare_for_sqlexec_condition(import_data.load_number)
lnStart = SECONDS()
lnResponse = SQLEXEC(lnConnection, lcSQL, 'route_status')
lnFinish = SECONDS()
lnTime3 = lnTime3 + lnFinish - lnStart
SELECT route_status
LOCATE
IF NOT EOF()
IF route_status.loading_type = 'S' AND import_data.request_type = 'P'
lcSQL = "update route_status set loading_type = 'P' " ;
+ "where route_status_id = " + TRANSFORM(route_status.route_status_id)
lnStart = SECONDS()
lnResponse = SQLEXEC(lnConnection, lcSQL)
lnFinish = SECONDS()
lnTime4 = lnTime4 + lnFinish - lnStart
*!* REPLACE route_status_vp.loading_type WITH 'P' ;
*!* IN route_status_vp
ENDIF
ELSE
lnStart = SECONDS()
lnResponse = SQLEXEC(lnConnection, ;
'select route_status_id_seq.nextval from dual', 'sequence')
lnFinish = SECONDS()
lnTime5 = lnTime5 + lnFinish - lnStart
lcSc_vehicle_code = null
*!* IF vehicles_and_vehicle_types_vp.delivery_mode = 'B'
IF lcVehicleDeliveryMode = 'B'
lcLoading_type = 'B'
ELSE
IF import_data.request_type = 'P'
lcLoading_type = 'P'
ELSE
lcLoading_type = 'S'
ENDIF
IF warehouse_load_options_vp.satellite_depot_flag = 1 ;
AND NOT ISNULL(warehouse_load_options_vp.satellite_sc_vehicle_code)
lcSc_vehicle_code = warehouse_load_options_vp.satellite_sc_vehicle_code
ENDIF
ENDIF
DO CASE
CASE _screen.oapp.aWarehouses(_screen.oapp.nCurrentWarehouse, 3) = 'A'
lnVisibleFlag = 1
CASE _screen.oapp.aWarehouses(_screen.oapp.nCurrentWarehouse, 3) = 'B' ;
AND lcLoading_type = 'B'
lnVisibleFlag = 1
CASE _screen.oapp.aWarehouses(_screen.oapp.nCurrentWarehouse, 3) = 'S' ;
AND lcLoading_type # 'B'
lnVisibleFlag = 1
OTHERWISE
lnVisibleFlag = 0
ENDCASE
lcSQL = "select aggregate_by " ;
+ "from warehouse_load_processing where warehouse_code " ;
+ prepare_for_sqlexec_condition(_screen.oapp.cwarehouse_code)
lnStart = SECONDS()
lnResponse = SQLEXEC(lnConnection, lcSQL, 'warehouse_load_processing')
lnFinish = SECONDS()
lnTime6 = lnTime6 + lnFinish - lnStart
lcSQL = "insert into route_status (route_status_id, route_code, delivery_ldate, " ;
+ "load_number, vehicle_code, loading_type, total_cases, percent_cube, " ;
+ "auto_pallet_height, auto_hand_layer_step, sideload_processing_order, " ;
+ "palletize_processing_order, bulk_processing_order, sc_vehicle_code, " ;
+ "sc_bays, route_status, percent_picked, load_config_id, locked_by_user, " ;
+ "lock_timestamp, lock_session_id, palletize_aggregate_by) values (" ;
+ prepare_for_sqlexec(sequence.nextval) + ", " ;
+ prepare_for_sqlexec(import_data.route_code) + ", " ;
+ prepare_for_sqlexec(import_data.delivery_ldate) + ", " ;
+ prepare_for_sqlexec(import_data.load_number) + ", " ;
+ prepare_for_sqlexec(import_data.vehicle_code) + ", " ;
+ prepare_for_sqlexec(lcLoading_type) + ", 0, 0, 0, 0, 0, 0, 0, " ;
+ prepare_for_sqlexec(lcSc_vehicle_code) ;
+ ", 0, 0, 0, null, null, null, null, " ;
+ TRANSFORM(warehouse_load_processing.aggregate_by) + ")"
lnStart = SECONDS()
lnResponse = SQLEXEC(lnConnection, lcSQL)
lnFinish = SECONDS()
lnTime7 = lnTime7 + lnFinish - lnStart
USE IN warehouse_load_processing
*!* INSERT INTO route_status_vp (route_status_id, route_code, delivery_ldate, ;
*!* load_number, vehicle_code, loading_type, problem, ;
*!* total_cases, percent_cube, selected_flag, processed_flag, ;
*!* auto_completed_flag, auto_pallet_height, auto_hand_layer_step, ;
*!* sideload_processing_order, palletize_processing_order, ;
*!* bulk_processing_order, visible_flag, sc_vehicle_code, sc_bays, ;
*!* route_status, percent_picked, load_config_id) ;
*!* VALUES (sequence.nextval, import_data.route_code, ;
*!* import_data.delivery_ldate, import_data.load_number, ;
*!* import_data.vehicle_code, lcLoading_type, null, 0, 0, ;
*!* 0, 0, 0, 0, 0, 0, 0, 0, lnVisibleFlag, lcSc_vehicle_code, 0, 0, 0, null)
lcSQL = "select route_status_id, loading_type " ;
+ "from route_status where route_status_id = " + TRANSFORM(sequence.nextval)
lnStart = SECONDS()
lnResponse = SQLEXEC(lnConnection, lcSQL, 'route_status')
lnFinish = SECONDS()
lnTime8 = lnTime8 + lnFinish - lnStart
USE IN sequence
IF lcRouteType = 'P' AND route_status.loading_type = 'B' ;
OR INLIST(lcRouteType, 'C', 'N')
SELECT auto_process_routes
INSERT INTO auto_process_routes (warehouse_code, route_status_id, ;
route_code, load_number) ;
VALUES (warehouse_load_options_vp.warehouse_code, ;
route_status.route_status_id, import_data.route_code, ;
import_data.load_number)
ENDIF
ENDIF
ELSE
IF route_status.loading_type = 'S' AND import_data.request_type = 'P'
lcSQL = "update route_status set loading_type = 'P' " ;
+ "where route_status_id = " + TRANSFORM(route_status.route_status_id)
lnStart = SECONDS()
lnResponse = SQLEXEC(lnConnection, lcSQL)
lnFinish = SECONDS()
lnTime9 = lnTime9 + lnFinish - lnStart
*!* REPLACE route_status_vp.loading_type WITH 'P' ;
*!* IN route_status_vp
ENDIF
ENDIF

REPLACE import_data.route_status_id WITH route_status.route_status_id IN import_data

IF import_data.item_code # lcLast_Item_Code
lcLast_Item_Code = import_data.item_code
ENDIF

* if allow import additions is 'N' then any routes that are in the route_status
* file for the same date are removed from the import, which means any matching
* routes found should be added or updated
* Check if the item is already in the load request, if it is then add the quantity to
* the load request. Update the bulk orders if a bulk load. Update the palletize customers
* if sideload
*!* SELECT load_request_vp
*!* IF SEEK(STR(import_data.route_status_id,10) + STR(import_data.stop_number,5) ;
*!* + NVL(import_data.customer_code, lc30Spaces) ;
*!* + NVL(import_data.order_number, lc10Spaces) + import_data.pick_type_code ;
*!* + import_data.item_code + import_data.request_type + import_data.order_type ;
*!* + import_data.unit_of_measure, 'load_request_vp', 'ck')
lcSQL = "select load_request_id, route_status_id, stop_number, customer_code, " ;
+ "order_number, pick_type_code from load_request where route_status_id = " ;
+ TRANSFORM(import_data.route_status_id) ;
+ " and stop_number = " + TRANSFORM(import_data.stop_number) ;
+ " and customer_code " + prepare_for_sqlexec_condition(import_data.customer_code) ;
+ " and order_number " + prepare_for_sqlexec_condition(import_data.order_number) ;
+ " and pick_type_code " + prepare_for_sqlexec_condition(import_data.pick_type_code) ;
+ " and item_code " + prepare_for_sqlexec_condition(import_data.item_code) ;
+ " and request_type " + prepare_for_sqlexec_condition(import_data.request_type) ;
+ " and unit_of_measure " + prepare_for_sqlexec_condition(import_data.unit_of_measure)
lnStart = SECONDS()
lnResponse = SQLEXEC(lnConnection, lcSQL, 'load_request')
lnFinish = SECONDS()
lnTime10 = lnTime10 + lnFinish - lnStart
SELECT load_request
LOCATE
IF NOT EOF()
lcSQL = "update load_request set request = request + " ;
+ TRANSFORM(import_data.request) ;
+ " where load_request_id = " + TRANSFORM(load_request.load_request_id)
lnStart = SECONDS()
lnResponse = SQLEXEC(lnConnection, lcSQL)
lnFinish = SECONDS()
lnTime11 = lnTime11 + lnFinish - lnStart
*!* REPLACE load_request_vp.request WITH load_request_vp.request ;
*!* + import_data.request
IF import_data.request_type = 'B'
DO CASE
CASE import_data.unit_of_measure = 'P'
lcSQL = "update bulk_orders set pack_total = pack_total"
CASE import_data.unit_of_measure = 'U'
lcSQL = "update bulk_orders set unit_total = unit_total"
OTHERWISE
lcSQL = "update bulk_orders set case_total = case_total"
ENDCASE
lcSQL = lcSQL + " + " + TRANSFORM(import_data.request) ;
+ " where route_status_id = " + TRANSFORM(import_data.route_status_id) ;
+ " and stop_number = " + TRANSFORM(import_data.stop_number) ;
+ " and customer_code " ;
+ prepare_for_sqlexec_condition(import_data.customer_code) ;
+ " and order_number " ;
+ prepare_for_sqlexec_condition(import_data.order_number) ;
+ " and pick_type_code " ;
+ prepare_for_sqlexec_condition(import_data.pick_type_code)
lnStart = SECONDS()
lnResponse = SQLEXEC(lnConnection, lcSQL)
lnFinish = SECONDS()
lnTime12 = lnTime12 + lnFinish - lnStart
*!* SELECT bulk_orders_vp
*!* =SEEK(STR(import_data.route_status_id,10) + STR(import_data.stop_number,5) ;
*!* + import_data.customer_code + NVL(import_data.order_number, lc10Spaces) ;
*!* + import_data.pick_type_code, 'bulk_orders_vp', 'ck')
*!* DO CASE
*!* CASE import_data.unit_of_measure = 'P'
*!* REPLACE bulk_orders_vp.pack_total WITH bulk_orders_vp.pack_total ;
*!* + import_data.request
*!* CASE import_data.unit_of_measure = 'U'
*!* REPLACE bulk_orders_vp.unit_total WITH bulk_orders_vp.unit_total ;
*!* + import_data.request
*!* OTHERWISE
*!* REPLACE bulk_orders_vp.case_total WITH bulk_orders_vp.case_total ;
*!* + import_data.request
*!* ENDCASE
ELSE
* Only add customers for pre-sold routes to palletize customers
IF lcRouteType # 'C'
DO CASE
CASE import_data.unit_of_measure = 'P'
lcSQL = "update palletize_customers set pack_total = pack_total"
CASE import_data.unit_of_measure = 'U'
lcSQL = "update palletize_customers set unit_total = unit_total"
OTHERWISE
lcSQL = "update palletize_customers set case_total = case_total"
ENDCASE
lcSQL = lcSQL + " + " + TRANSFORM(import_data.request) ;
+ " where route_status_id = " + TRANSFORM(import_data.route_status_id) ;
+ " and stop_number = " + TRANSFORM(import_data.stop_number) ;
+ " and customer_code " ;
+ prepare_for_sqlexec_condition(import_data.customer_code) ;
+ " and pick_type_code " ;
+ prepare_for_sqlexec_condition(import_data.pick_type_code)
lnStart = SECONDS()
lnResponse = SQLEXEC(lnConnection, lcSQL)
lnFinish = SECONDS()
lnTime13 = lnTime13 + lnFinish - lnStart
*!* SELECT palletize_customers_vp
*!* =SEEK(STR(import_data.route_status_id,10) + STR(import_data.stop_number,5) ;
*!* + import_data.customer_code + import_data.pick_type_code, ;
*!* 'palletize_customers_vp', 'ck')
*!* DO CASE
*!* CASE import_data.unit_of_measure = 'P'
*!* REPLACE palletize_customers_vp.pack_total ;
*!* WITH palletize_customers_vp.pack_total + import_data.request
*!* CASE import_data.unit_of_measure = 'U'
*!* REPLACE palletize_customers_vp.unit_total ;
*!* WITH palletize_customers_vp.unit_total + import_data.request
*!* OTHERWISE
*!* REPLACE palletize_customers_vp.case_total ;
*!* WITH palletize_customers_vp.case_total + import_data.request
*!* ENDCASE
ENDIF
ENDIF
ELSE
* if the item not already in the load request file, then add it.
* Add the quantity to the bulk orders if a bulk route.
* Add the quantity to the palletize customers if sideload, also if the load is
* aggregate
lnStart = SECONDS()
lnResponse = SQLEXEC(lnConnection, 'select load_request_id_seq.nextval from dual', ;
'sequence')
lnFinish = SECONDS()
lnTime14 = lnTime14 + lnFinish - lnStart
lnLoadRequestId = sequence.nextval
USE IN sequence

lcSQL = "insert into load_request (load_request_id, route_status_id, stop_number, " ;
+ "customer_code, order_number, request_type, item_code, request, problem, " ;
+ "aggregate_flag, unit_of_measure, purchase_order_number, " ;
+ "purchase_order_release_num, purchase_order_ldate, invoice_number, " ;
+ "estimated_deliv_ltimestamp, scheduled_deliv_ltimestamp, pick_type_code, " ;
+ "customer_assigned_item, department_number) values (" ;
+ TRANSFORM(lnLoadRequestId) + ", " + TRANSFORM(import_data.route_status_id) ;
+ ", " + TRANSFORM(import_data.stop_number) + ", " ;
+ prepare_for_sqlexec(import_data.customer_code) + ", " ;
+ prepare_for_sqlexec(import_data.order_number) + ", " ;
+ prepare_for_sqlexec(import_data.request_type) + ", " ;
+ prepare_for_sqlexec(import_data.item_code) + ", " ;
+ TRANSFORM(import_data.request) + ", " ;
+ prepare_for_sqlexec(import_data.problem) + ", 0, " ;
+ prepare_for_sqlexec(import_data.unit_of_measure) + ", " ;
+ prepare_for_sqlexec(import_data.purchase_order_number) + ", " ;
+ prepare_for_sqlexec(import_data.purchase_order_release_num) + ", " ;
+ prepare_for_sqlexec(import_data.purchase_order_ldate) + ", " ;
+ prepare_for_sqlexec(import_data.invoice_number) + ", " ;
+ prepare_for_sqlexec(import_data.estimated_deliv_ltimestamp) + ", " ;
+ prepare_for_sqlexec(import_data.scheduled_deliv_ltimestamp) + ", " ;
+ prepare_for_sqlexec(import_data.pick_type_code) + ", " ;
+ prepare_for_sqlexec(import_data.customer_assigned_item) + ", " ;
+ prepare_for_sqlexec(import_data.department_number) + ")"
lnStart = SECONDS()
lnResponse = SQLEXEC(lnConnection, lcSQL)
lnFinish = SECONDS()
lnTime15 = lnTime15 + lnFinish - lnStart
lcSQL = "select route_status_id, stop_number, customer_code, order_number, " ;
+ "pick_type_code from load_request where load_request_id = " ;
+ TRANSFORM(lnLoadRequestId)
lnStart = SECONDS()
lnResponse = SQLEXEC(lnConnection, lcSQL, 'load_request')
lnFinish = SECONDS()
lnTime16 = lnTime16 + lnFinish - lnStart
IF import_data.request_type = 'B'
*!* SELECT bulk_orders_vp
*!* IF SEEK(STR(import_data.route_status_id,10) + STR(import_data.stop_number,5) ;
*!* + import_data.customer_code + NVL(import_data.order_number, lc10Spaces) ;
*!* + import_data.pick_type_code, 'bulk_orders_vp', 'ck')
*!* DO CASE
*!* CASE import_data.unit_of_measure = 'P'
*!* REPLACE bulk_orders_vp.pack_total WITH bulk_orders_vp.pack_total ;
*!* + import_data.request
*!* CASE import_data.unit_of_measure = 'U'
*!* REPLACE bulk_orders_vp.unit_total WITH bulk_orders_vp.unit_total ;
*!* + import_data.request
*!* OTHERWISE
*!* REPLACE bulk_orders_vp.case_total WITH bulk_orders_vp.case_total ;
*!* + import_data.request
*!* ENDCASE
lcSQL = "select bulk_orders_id from bulk_orders where route_status_id = " ;
+ TRANSFORM(import_data.route_status_id) ;
+ " and stop_number = " + TRANSFORM(import_data.stop_number) ;
+ " and customer_code " ;
+ prepare_for_sqlexec_condition(import_data.customer_code) ;
+ " and order_number " ;
+ prepare_for_sqlexec_condition(import_data.order_number) ;
+ " and pick_type_code " ;
+ prepare_for_sqlexec_condition(import_data.pick_type_code)
lnStart = SECONDS()
lnResponse = SQLEXEC(lnConnection, lcSQL, 'bulk_orders')
lnFinish = SECONDS()
lnTime17 = lnTime17 + lnFinish - lnStart
SELECT bulk_orders
LOCATE
IF NOT EOF()
DO CASE
CASE import_data.unit_of_measure = 'P'
lcSQL = "update bulk_orders set pack_total = pack_total"
CASE import_data.unit_of_measure = 'U'
lcSQL = "update bulk_orders set unit_total = unit_total"
OTHERWISE
lcSQL = "update bulk_orders set case_total = case_total"
ENDCASE
lcSQL = lcSQL + " + " + TRANSFORM(import_data.request) ;
+ " where bulk_orders_id = " + TRANSFORM(bulk_orders.bulk_orders_id)
lnStart = SECONDS()
lnResponse = SQLEXEC(lnConnection, lcSQL)
lnFinish = SECONDS()
lnTime18 = lnTime18 + lnFinish - lnStart
ELSE
lcSQL = "select asn_enabled, no_aggr_bulk_flag " ;
+ "from customers where customer_code " ;
+ prepare_for_sqlexec_condition(import_data.customer_code)
lnStart = SECONDS()
lnResponse = SQLEXEC(lnConnection, lcSQL, 'customers')
lnFinish = SECONDS()
lnTime19 = lnTime19 + lnFinish - lnStart
*!* =SEEK(import_data.customer_code, 'customers_vp', 'pk')
*!* SELECT bulk_prebuild_orders_vp
*!* LOCATE FOR bulk_prebuild_orders_vp.warehouse_code ;
*!* = warehouse_load_options_vp.warehouse_code ;
*!* AND bulk_prebuild_orders_vp.delivery_ldate = import_data.delivery_ldate ;
*!* AND bulk_prebuild_orders_vp.customer_code = import_data.customer_code ;
*!* AND isequal(bulk_prebuild_orders_vp.order_number, import_data.order_number) ;
*!* AND NVL(bulk_prebuild_orders_vp.pick_type_code, lc30Spaces) ;
*!* = import_data.pick_type_code
*!* IF FOUND()

* update the aggregate and allow aggregate flags to match those in bulk
* prebuild orders
lcSQL = "select bulk_prebuild_id, aggregate_flag " ;
+ "from bulk_prebuild_orders where warehouse_code " ;
+ prepare_for_sqlexec_condition(warehouse_load_options_vp.warehouse_code) ;
+ " and customer_code " ;
+ prepare_for_sqlexec_condition(import_data.customer_code) ;
+ " and order_number " ;
+ prepare_for_sqlexec_condition(import_data.order_number) ;
+ " and pick_type_code " ;
+ prepare_for_sqlexec_condition(import_data.pick_type_code)
lnStart = SECONDS()
lnResponse = SQLEXEC(lnConnection, lcSQL, 'bulk_prebuild_orders')
lnFinish = SECONDS()
lnTime20 = lnTime20 + lnFinish - lnStart
SELECT bulk_prebuild_orders
LOCATE
IF NOT EOF()
* only update if the bulk prebuild was released to picking or picking
* completed
lcSQL = "select prebuild_status " ;
+ "from bulk_prebuild where bulk_prebuild_id = " ;
+ TRANSFORM(bulk_prebuild_orders.bulk_prebuild_id)
lnStart = SECONDS()
lnResponse = SQLEXEC(lnConnection, lcSQL, 'bulk_prebuild')
lnFinish = SECONDS()
lnTime21 = lnTime21 + lnFinish - lnStart
*!* =SEEK(bulk_prebuild_orders_vp.bulk_prebuild_id, 'bulk_prebuild_vp', 'pk')
IF bulk_prebuild.prebuild_status > 1
lnAllow_aggregate_flag = 0
lnAggregate_flag = bulk_prebuild_orders.aggregate_flag
ELSE
IF warehouse_load_options_vp.asn_enabled_flag = 1 ;
AND customers.asn_enabled = 1
lnAllow_aggregate_flag = 0
lnAggregate_flag = 0
ELSE
lnAllow_aggregate_flag = 1
IF customers.no_aggr_bulk_flag = 1
lnAggregate_flag = 0
ELSE
lnAggregate_flag = 1
ENDIF
ENDIF
ENDIF
USE IN bulk_prebuild
ELSE
* Check if the order exists in pallet item assignment. If it does then
* the bulk order is not allowed to aggregate or be aggregated. Otherwise
* check the warehouse and customer asn flags.
IF NOT EMPTY(import_data.order_number)
lcSQL = "select order_number from pallet_item_assignment " ;
+ "where order_number " ;
+ prepare_for_sqlexec_condition(import_data.order_number)
lnStart = SECONDS()
lnResponse = SQLEXEC(lnConnection, lcSQL, ;
'pallet_item_assignment_order_exists')
lnFinish = SECONDS()
lnTime22 = lnTime22 + lnFinish - lnStart
SELECT pallet_item_assignment_order_exists
LOCATE
IF NOT EOF()
lnAllow_aggregate_flag = 0
lnAggregate_flag = 0
ELSE
IF warehouse_load_options_vp.asn_enabled_flag = 1 ;
AND customers.asn_enabled = 1
lnAllow_aggregate_flag = 0
lnAggregate_flag = 0
ELSE
lnAllow_aggregate_flag = 1
IF customers.no_aggr_bulk_flag = 1
lnAggregate_flag = 0
ELSE
lnAggregate_flag = 1
ENDIF
ENDIF
ENDIF
USE IN pallet_item_assignment_order_exists
ELSE
IF warehouse_load_options_vp.asn_enabled_flag = 1 ;
AND customers.asn_enabled = 1
lnAllow_aggregate_flag = 0
lnAggregate_flag = 0
ELSE
lnAllow_aggregate_flag = 1
IF customers.no_aggr_bulk_flag = 1
lnAggregate_flag = 0
ELSE
lnAggregate_flag = 1
ENDIF
ENDIF
ENDIF
ENDIF
USE IN customers
USE IN bulk_prebuild_orders

* Update bulk prebuild and bulk prebuild orders with delivery date, route,
* vehicle and stop information for non-aggregated bulk prebuilds that have been
* released to picking or picking completed.
lcSQL = "select bulk_prebuild_orders.bulk_prebuild_orders_id, " ;
+ "bulk_prebuild_orders.bulk_prebuild_id, " ;
+ "bulk_prebuild_orders.delivery_ldate, bulk_prebuild_orders.route_code, " ;
+ "bulk_prebuild_orders.vehicle_code, bulk_prebuild_orders.stop_number " ;
+ "from bulk_prebuild_orders inner join bulk_prebuild " ;
+ "on bulk_prebuild_orders.bulk_prebuild_id " ;
+ "= bulk_prebuild.bulk_prebuild_id " ;
+ "where bulk_prebuild.prebuild_status > 1 " ;
+ " and bulk_prebuild_orders.warehouse_code " ;
+ prepare_for_sqlexec_condition(warehouse_load_options_vp.warehouse_code) ;
+ " and bulk_prebuild_orders.customer_code " ;
+ prepare_for_sqlexec_condition(import_data.customer_code) ;
+ " and bulk_prebuild_orders.order_number " ;
+ prepare_for_sqlexec_condition(import_data.order_number) ;
+ " and bulk_prebuild_orders.pick_type_code " ;
+ prepare_for_sqlexec_condition(import_data.pick_type_code)
lnStart = SECONDS()
lnResponse = SQLEXEC(lnConnection, lcSQL, 'bulk_prebuild_orders')
lnFinish = SECONDS()
lnTime23 = lnTime23 + lnFinish - lnStart
SELECT bulk_prebuild_orders
INDEX ON STR(bulk_prebuild_orders.bulk_prebuild_id) ;
+ STR(bulk_prebuild_orders.bulk_prebuild_orders_id) TAG ck
lnLastBulkPrebuildID = 0
LOCATE
SCAN
IF lnLastBulkPrebuildID # bulk_prebuild_orders.bulk_prebuild_id
lnLastBulkPrebuildID = bulk_prebuild_orders.bulk_prebuild_id
lcSQL = "select delivery_ldate, route_code, vehicle_code, " ;
+ "stop_number, customer_code, order_number, pick_type_code " ;
+ "from bulk_prebuild where bulk_prebuild_id = " ;
+ TRANSFORM(bulk_prebuild_orders.bulk_prebuild_id)
lnStart = SECONDS()
lnResponse = SQLEXEC(lnConnection, lcSQL, 'bulk_prebuild')
lnFinish = SECONDS()
lnTime24 = lnTime24 + lnFinish - lnStart
SELECT bulk_prebuild
LOCATE
* update the bulk prebuild record and loading records.
IF (bulk_prebuild.delivery_ldate # import_data.delivery_ldate ;
OR NOT isequal(NVL(bulk_prebuild.route_code, lc10Spaces), ;
import_data.route_code) ;
OR NOT isequal(NVL(bulk_prebuild.vehicle_code, lc30Spaces), ;
import_data.vehicle_code) ;
OR bulk_prebuild.stop_number # import_data.stop_number)
llAlreadyLocked = SEEK(bulk_prebuild_orders.bulk_prebuild_id, ;
'bulk_prebuilds_to_lock', 'pk')
IF llAlreadyLocked OR (NOT llAlreadyLocked ;
AND obtain_lock('bulk_prebuild', ;
bulk_prebuild_orders.bulk_prebuild_id, .F.))

INSERT INTO bulk_prebuilds_to_lock (delivery_ldate, ;
route_code, stop_number, customer_code, order_number, ;
pick_type_code, bulk_prebuild_id, locked) ;
VALUES (bulk_prebuild.delivery_ldate, ;
bulk_prebuild.route_code, bulk_prebuild.stop_number, ;
bulk_prebuild.customer_code, bulk_prebuild.order_number, ;
bulk_prebuild.pick_type_code, ;
bulk_prebuild_orders.bulk_prebuild_id, .F.)

lcSQL = "update bulk_prebuild set delivery_ldate = " ;
+ prepare_for_sqlexec(import_data.delivery_ldate) ;
+ ", route_code = " + prepare_for_sqlexec(import_data.route_code) ;
+ ", vehicle_code = " ;
+ prepare_for_sqlexec(import_data.vehicle_code) ;
+ ", stop_number = " + TRANSFORM(import_data.stop_number) ;
+ " where bulk_prebuild_id = " ;
+ TRANSFORM(bulk_prebuild_orders.bulk_prebuild_id)
lnStart = SECONDS()
lnResponse = SQLEXEC(lnConnection, lcSQL)
lnFinish = SECONDS()
lnTime25 = lnTime25 + lnFinish - lnStart
*!* SELECT bulk_prebuild_vp
*!* REPLACE bulk_prebuild_vp.route_code WITH import_data.route_code, ;
*!* bulk_prebuild_vp.vehicle_code WITH import_data.vehicle_code, ;
*!* bulk_prebuild_vp.stop_number WITH import_data.stop_number

lcSQL = "update bulk_prebuild_load_history set stop_number = " ;
+ TRANSFORM(import_data.stop_number) ;
+ " where bulk_prebuild_id = " ;
+ TRANSFORM(bulk_prebuild_orders.bulk_prebuild_id)
lnStart = SECONDS()
lnResponse = SQLEXEC(lnConnection, lcSQL)
lnFinish = SECONDS()
lnTime26 = lnTime26 + lnFinish - lnStart

*!* SELECT bulk_prebuild_load_history_vp
*!* lcSaveOrder = ORDER()
*!* SET ORDER TO bp_id
*!* =SEEK(bulk_prebuild_vp.bulk_prebuild_id, ;
*!* 'bulk_prebuild_load_history_vp', 'bp_id')
*!* SCAN WHILE bulk_prebuild_load_history_vp.bulk_prebuild_id ;
*!* = bulk_prebuild_vp.bulk_prebuild_id
*!* REPLACE bulk_prebuild_load_history_vp.stop_number ;
*!* WITH bulk_prebuild_vp.stop_number
*!* SELECT bulk_prebuild_load_history_vp
*!* ENDSCAN
*!* SELECT bulk_prebuild_load_history_vp
*!* SET ORDER TO &lcSaveOrder
ENDIF
ENDIF
ENDIF

* update the bulk prebuild orders record
IF bulk_prebuild_orders.delivery_ldate # import_data.delivery_ldate ;
OR NOT isequal(NVL(bulk_prebuild_orders.route_code, lc10Spaces), ;
import_data.route_code) ;
OR NOT isequal(NVL(bulk_prebuild_orders.vehicle_code, ;
lc30Spaces), import_data.vehicle_code) ;
OR bulk_prebuild_orders.stop_number # import_data.stop_number
lcSQL = "update bulk_prebuild_orders set delivery_ldate = " ;
+ prepare_for_sqlexec(import_data.delivery_ldate) ;
+ ", route_code = " + prepare_for_sqlexec(import_data.route_code) ;
+ ", vehicle_code = " ;
+ prepare_for_sqlexec(import_data.vehicle_code) ;
+ ", stop_number = " + TRANSFORM(import_data.stop_number) ;
+ " where bulk_prebuild_orders_id = " ;
+ TRANSFORM(bulk_prebuild_orders.bulk_prebuild_orders_id)
lnStart = SECONDS()
lnResponse = SQLEXEC(lnConnection, lcSQL)
lnFinish = SECONDS()
lnTime27 = lnTime27 + lnFinish - lnStart
*!* SELECT bulk_prebuild_orders_vp
*!* REPLACE bulk_prebuild_orders_vp.route_code ;
*!* WITH import_data.route_code, ;
*!* bulk_prebuild_orders_vp.vehicle_code ;
*!* WITH import_data.vehicle_code, ;
*!* bulk_prebuild_orders_vp.stop_number WITH import_data.stop_number
ENDIF
SELECT bulk_prebuild_orders
ENDSCAN
USE IN bulk_prebuild_orders
*!* SELECT bulk_prebuild_orders_vp
*!* LOCATE
*!* SCAN FOR bulk_prebuild_orders_vp.warehouse_code ;
*!* = warehouse_load_options_vp.warehouse_code ;
*!* AND bulk_prebuild_orders_vp.delivery_ldate = import_data.delivery_ldate ;
*!* AND bulk_prebuild_orders_vp.customer_code = import_data.customer_code ;
*!* AND isequal(bulk_prebuild_orders_vp.order_number, import_data.order_number) ;
*!* AND NVL(bulk_prebuild_orders_vp.pick_type_code, lc30Spaces) ;
*!* = import_data.pick_type_code
*!* =SEEK(bulk_prebuild_orders_vp.bulk_prebuild_id, 'bulk_prebuild_vp', 'pk')
*!* IF bulk_prebuild_vp.prebuild_status > 1
*!* * update the bulk prebuild orders record
*!* IF NOT isequal(bulk_prebuild_orders_vp.route_code, ;
*!* import_data.route_code) ;
*!* OR NOT isequal(bulk_prebuild_orders_vp.vehicle_code, ;
*!* import_data.vehicle_code) ;
*!* OR NOT isequal(bulk_prebuild_orders_vp.stop_number, ;
*!* import_data.stop_number)
*!* SELECT bulk_prebuild_orders_vp
*!* REPLACE bulk_prebuild_orders_vp.route_code ;
*!* WITH import_data.route_code, ;
*!* bulk_prebuild_orders_vp.vehicle_code ;
*!* WITH import_data.vehicle_code, ;
*!* bulk_prebuild_orders_vp.stop_number WITH import_data.stop_number
*!* ENDIF
*!* * update the bulk prebuild record and loading records.
*!* IF NOT isequal(NVL(bulk_prebuild_vp.route_code, lc10Spaces), ;
*!* import_data.route_code) ;
*!* OR NOT isequal(NVL(bulk_prebuild_vp.vehicle_code, lc30Spaces), ;
*!* import_data.vehicle_code) ;
*!* OR NOT isequal(bulk_prebuild_vp.stop_number, ;
*!* import_data.stop_number)
*!* SELECT bulk_prebuild_vp
*!* REPLACE bulk_prebuild_vp.route_code WITH import_data.route_code, ;
*!* bulk_prebuild_vp.vehicle_code WITH import_data.vehicle_code, ;
*!* bulk_prebuild_vp.stop_number WITH import_data.stop_number

*!* SELECT bulk_prebuild_load_history_vp
*!* lcSaveOrder = ORDER()
*!* SET ORDER TO bp_id
*!* =SEEK(bulk_prebuild_vp.bulk_prebuild_id, ;
*!* 'bulk_prebuild_load_history_vp', 'bp_id')
*!* SCAN WHILE bulk_prebuild_load_history_vp.bulk_prebuild_id ;
*!* = bulk_prebuild_vp.bulk_prebuild_id
*!* REPLACE bulk_prebuild_load_history_vp.stop_number ;
*!* WITH bulk_prebuild_vp.stop_number
*!* SELECT bulk_prebuild_load_history_vp
*!* ENDSCAN
*!* SELECT bulk_prebuild_load_history_vp
*!* SET ORDER TO &lcSaveOrder
*!* ENDIF
*!* ENDIF
*!* SELECT bulk_prebuild_orders_vp
*!* ENDSCAN
*!* SELECT bulk_prebuild_vp
*!* LOCATE FOR bulk_prebuild_vp.prebuild_status > 1 ;
*!* AND bulk_prebuild_vp.warehouse_code ;
*!* = warehouse_load_options_vp.warehouse_code ;
*!* AND bulk_prebuild_vp.delivery_ldate = import_data.delivery_ldate ;
*!* AND bulk_prebuild_vp.customer_code = import_data.customer_code ;
*!* AND isequal(bulk_prebuild_vp.order_number, import_data.order_number) ;
*!* AND NVL(bulk_prebuild_vp.pick_type_code, lc30Spaces) ;
*!* = import_data.pick_type_code
*!* IF FOUND()
*!* * update the bulk prebuild orders record
*!* SELECT bulk_prebuild_orders_vp
*!* =SEEK(bulk_prebuild_vp.bulk_prebuild_id, 'bulk_prebuild_orders_vp', ;
*!* 'parent')

*!* IF NOT isequal(bulk_prebuild_orders_vp.route_code, ;
*!* import_data.route_code) ;
*!* OR NOT isequal(bulk_prebuild_orders_vp.vehicle_code, ;
*!* import_data.vehicle_code) ;
*!* OR NOT isequal(bulk_prebuild_orders_vp.stop_number, ;
*!* import_data.stop_number)
*!* REPLACE bulk_prebuild_orders_vp.route_code ;
*!* WITH import_data.route_code, ;
*!* bulk_prebuild_orders_vp.vehicle_code ;
*!* WITH import_data.vehicle_code, ;
*!* bulk_prebuild_orders_vp.stop_number WITH import_data.stop_number
*!* ENDIF
*!* * update the bulk prebuild record and loading records.
*!* SELECT bulk_prebuild_vp
*!* IF NOT isequal(NVL(bulk_prebuild_vp.route_code, lc10Spaces), ;
*!* import_data.route_code) ;
*!* OR NOT isequal(NVL(bulk_prebuild_vp.vehicle_code, lc30Spaces), ;
*!* import_data.vehicle_code) ;
*!* OR NOT isequal(bulk_prebuild_vp.stop_number, ;
*!* import_data.stop_number)
*!* REPLACE bulk_prebuild_vp.route_code WITH import_data.route_code, ;
*!* bulk_prebuild_vp.vehicle_code WITH import_data.vehicle_code, ;
*!* bulk_prebuild_vp.stop_number WITH import_data.stop_number

*!* SELECT bulk_prebuild_load_history_vp
*!* lcSaveOrder = ORDER()
*!* SET ORDER TO bp_id
*!* =SEEK(bulk_prebuild_vp.bulk_prebuild_id, ;
*!* 'bulk_prebuild_load_history_vp', 'bp_id')
*!* SCAN WHILE bulk_prebuild_load_history_vp.bulk_prebuild_id ;
*!* = bulk_prebuild_vp.bulk_prebuild_id
*!* REPLACE bulk_prebuild_load_history_vp.stop_number ;
*!* WITH bulk_prebuild_vp.stop_number
*!* SELECT bulk_prebuild_load_history_vp
*!* ENDSCAN
*!* SELECT bulk_prebuild_load_history_vp
*!* SET ORDER TO &lcSaveOrder
*!* ENDIF
*!* ENDIF
* calculate the number of orders for the stop_number + customer that are
* allowed to aggregate. ORders that are part of an aggregated bulk prebuild
* have their aggregate flag set but not their allow aggregate flag. The
* aggregate bulk orders screen should only show orders that can be aggregated.
lcSQL = "select count(1) as order_count from bulk_orders " ;
+ "where route_status_id = " + TRANSFORM(import_data.route_status_id) ;
+ " and stop_number = " + TRANSFORM(import_data.stop_number) ;
+ " and customer_code " ;
+ prepare_for_sqlexec_condition(import_data.customer_code) ;
+ " and pick_type_code " ;
+ prepare_for_sqlexec_condition(import_data.pick_type_code) ;
+ " and allow_aggregate_flag = 1"
lnStart = SECONDS()
lnResponse = SQLEXEC(lnConnection, lcSQL, 'bulk_orders')
lnFinish = SECONDS()
lnTime28 = lnTime28 + lnFinish - lnStart
lnCount = bulk_orders.order_count
USE IN bulk_orders
*!* SELECT bulk_orders_vp
*!* COUNT TO lnCount ;
*!* FOR bulk_orders_vp.route_status_id = import_data.route_status_id ;
*!* AND bulk_orders_vp.stop_number = import_data.stop_number ;
*!* AND bulk_orders_vp.customer_code = import_data.customer_code ;
*!* AND isequal(bulk_orders_vp.pick_type_code, ;
*!* import_data.pick_type_code) ;
*!* AND bulk_orders_vp.allow_aggregate_flag = 1
IF lnAllow_aggregate_flag = 1
lnCount = lnCount + 1
ENDIF
* update all records for the same stop and customer if the count changes.
IF lnAllow_aggregate_flag = 1 AND lnCount > 1
lcSQL = "update bulk_orders set order_count = " + TRANSFORM(lnCount) ;
+ " where route_status_id = " ;
+ TRANSFORM(import_data.route_status_id) ;
+ " and stop_number = " + TRANSFORM(import_data.stop_number) ;
+ " and customer_code " ;
+ prepare_for_sqlexec_condition(import_data.customer_code) ;
+ " and pick_type_code " ;
+ prepare_for_sqlexec_condition(import_data.pick_type_code)
lnStart = SECONDS()
lnResponse = SQLEXEC(lnConnection, lcSQL)
lnFinish = SECONDS()
lnTime29 = lnTime29 + lnFinish - lnStart
*!* REPLACE bulk_orders_vp.order_count WITH lnCount ;
*!* FOR bulk_orders_vp.route_status_id = import_data.route_status_id ;
*!* AND bulk_orders_vp.stop_number = import_data.stop_number ;
*!* AND bulk_orders_vp.customer_code = import_data.customer_code ;
*!* AND isequal(bulk_orders_vp.pick_type_code, ;
*!* import_data.pick_type_code)
ENDIF
lnStart = SECONDS()
lnResponse = SQLEXEC(lnConnection, ;
'select bulk_orders_id_seq.nextval from dual', 'sequence')
lnFinish = SECONDS()
lnTime30 = lnTime30 + lnFinish - lnStart
STORE 0 TO lnPackRequest, lnCaseRequest, lnUnitRequest
DO CASE
CASE import_data.unit_of_measure = 'P'
lnPackRequest = import_data.request
CASE import_data.unit_of_measure = 'U'
lnUnitRequest = import_data.request
OTHERWISE
lnCaseRequest = import_data.request
ENDCASE
lcSQL = "insert into bulk_orders (bulk_orders_id, route_status_id, " ;
+ "stop_number, customer_code, order_number, case_total, order_count, " ;
+ "aggregate_flag, allow_aggregate_flag, released_to_picking_flag, " ;
+ "pack_total, unit_total, pick_type_code) values (" ;
+ TRANSFORM(sequence.nextval) + ", " ;
+ TRANSFORM(load_request.route_status_id) + ", " ;
+ TRANSFORM(load_request.stop_number) + ", " ;
+ prepare_for_sqlexec(load_request.customer_code) + ", " ;
+ prepare_for_sqlexec(load_request.order_number) + ", " ;
+ TRANSFORM(lnCaseRequest) + ", " + TRANSFORM(lnCount) + ", " ;
+ TRANSFORM(lnAggregate_flag) + ", " + TRANSFORM(lnAllow_aggregate_flag) ;
+ ", 0, " + TRANSFORM(lnPackRequest) + ", " + TRANSFORM(lnUnitRequest) ;
+ ", " + prepare_for_sqlexec(load_request.pick_type_code) + ")"
lnStart = SECONDS()
lnResponse = SQLEXEC(lnConnection, lcSQL)
lnFinish = SECONDS()
lnTime31 = lnTime31 + lnFinish - lnStart
*!* INSERT INTO bulk_orders_vp (bulk_orders_id, route_status_id, stop_number, ;
*!* customer_code, order_number, case_total, order_count, ;
*!* aggregate_flag, allow_aggregate_flag, released_to_picking_flag, ;
*!* pack_total, unit_total, pick_type_code) ;
*!* VALUES (sequence.nextval, load_request_vp.route_status_id, ;
*!* load_request_vp.stop_number, load_request_vp.customer_code, ;
*!* load_request_vp.order_number, lnCaseRequest, lnCount, ;
*!* lnAggregate_flag, lnAllow_aggregate_flag, 0, lnPackRequest, ;
*!* lnUnitRequest, load_request_vp.pick_type_code)
USE IN sequence
ENDIF
ELSE && aggregate or palletize - not conventional
IF lcRouteType # 'C'
*!* SELECT palletize_customers_vp
*!* IF SEEK(STR(import_data.route_status_id,10) + STR(import_data.stop_number,5) ;
*!* + import_data.customer_code + import_data.pick_type_code, ;
*!* 'palletize_customers_vp', 'ck')
*!* DO CASE
*!* CASE import_data.unit_of_measure = 'P'
*!* REPLACE palletize_customers_vp.pack_total ;
*!* WITH palletize_customers_vp.pack_total + import_data.request
*!* CASE import_data.unit_of_measure = 'U'
*!* REPLACE palletize_customers_vp.unit_total ;
*!* WITH palletize_customers_vp.unit_total + import_data.request
*!* OTHERWISE
*!* REPLACE palletize_customers_vp.case_total ;
*!* WITH palletize_customers_vp.case_total + import_data.request
*!* ENDCASE
* if customer already in palletize customers, add quantity.
lcSQL = "select palletize_customers_id from palletize_customers " ;
+ "where route_status_id = " + TRANSFORM(import_data.route_status_id) ;
+ " and stop_number = " + TRANSFORM(import_data.stop_number) ;
+ " and customer_code " ;
+ prepare_for_sqlexec_condition(import_data.customer_code) ;
+ " and pick_type_code " ;
+ prepare_for_sqlexec_condition(import_data.pick_type_code)
lnStart = SECONDS()
lnResponse = SQLEXEC(lnConnection, lcSQL, 'palletize_customers')
lnFinish = SECONDS()
lnTime32 = lnTime32 + lnFinish - lnStart
SELECT palletize_customers
LOCATE
IF NOT EOF()
DO CASE
CASE import_data.unit_of_measure = 'P'
lcSQL = "update palletize_customers set pack_total = pack_total"
CASE import_data.unit_of_measure = 'U'
lcSQL = "update palletize_customers set unit_total = unit_total"
OTHERWISE
lcSQL = "update palletize_customers set case_total = case_total"
ENDCASE
lcSQL = lcSQL + " + " + TRANSFORM(import_data.request) ;
+ " where palletize_customers_id = " ;
+ TRANSFORM(palletize_customers.palletize_customers_id)
lnStart = SECONDS()
lnResponse = SQLEXEC(lnConnection, lcSQL)
lnFinish = SECONDS()
lnTime33 = lnTime33 + lnFinish - lnStart
ELSE
*!* =SEEK(import_data.customer_code, 'customers_vp', 'pk')
lcSQL = "select asn_enabled from customers where customer_code " ;
+ prepare_for_sqlexec_condition(import_data.customer_code)
lnStart = SECONDS()
lnResponse = SQLEXEC(lnConnection, lcSQL, 'customers')
lnFinish = SECONDS()
lnTime34 = lnTime34 + lnFinish - lnStart
IF warehouse_load_options_vp.asn_enabled_flag = 1 ;
AND customers.asn_enabled = 1
lnPalletize_flag = 1
lnAllow_change_palletize_flag = 0
ELSE
lnAllow_change_palletize_flag = 1
IF import_data.request_type = 'P'
lnPalletize_flag = 1
ELSE
lnPalletize_flag = 0
ENDIF
ENDIF
USE IN customers
STORE 0 TO lnPackRequest, lnCaseRequest, lnUnitRequest
DO CASE
CASE import_data.unit_of_measure = 'P'
lnPackRequest = import_data.request
CASE import_data.unit_of_measure = 'U'
lnUnitRequest = import_data.request
OTHERWISE
lnCaseRequest = import_data.request
ENDCASE
lnStart = SECONDS()
lnResponse = SQLEXEC(lnConnection, ;
'select palletize_customers_id_seq.nextval from dual', 'sequence')
lnFinish = SECONDS()
lnTime35 = lnTime35 + lnFinish - lnStart
lcSQL = "insert into palletize_customers (route_status_id, " ;
+ "customer_code, stop_number, case_total, palletize_flag, " ;
+ "number_of_pallets, number_of_bays, place_in_dock_bays_flag, " ;
+ "released_to_picking_flag, pack_total, unit_total, " ;
+ "total_request_cases, total_request_packs, total_request_units, " ;
+ "allow_change_palletize_flag, pick_type_code, " ;
+ "palletize_customers_id) values (" ;
+ TRANSFORM(load_request.route_status_id) ;
+ ", " + prepare_for_sqlexec(load_request.customer_code) + ", " ;
+ TRANSFORM(load_request.stop_number) + ", " ;
+ TRANSFORM(lnCaseRequest) + ", " + TRANSFORM(lnPalletize_flag) ;
+ ", null, 0, 0, 0, " + TRANSFORM(lnPackRequest) + ", " ;
+ TRANSFORM(lnUnitRequest) + ", 0, 0, 0, " ;
+ TRANSFORM(lnAllow_change_palletize_flag) + ", " ;
+ prepare_for_sqlexec(import_data.pick_type_code) + ", " ;
+ TRANSFORM(sequence.nextval) + ")"
lnStart = SECONDS()
lnResponse = SQLEXEC(lnConnection, lcSQL)
lnFinish = SECONDS()
lnTime36 = lnTime36 + lnFinish - lnStart
*!* SELECT palletize_customers_vp
*!* INSERT INTO palletize_customers_vp (delivery_ldate, route_code, ;
*!* load_number, route_status_id, customer_code, stop_number, ;
*!* case_total, palletize_flag, number_of_pallets, number_of_bays, ;
*!* place_in_dock_bays_flag, released_to_picking_flag, pack_total, ;
*!* unit_total, total_request_cases, total_request_packs, ;
*!* total_request_units, allow_change_palletize_flag, ;
*!* pick_type_code, palletize_customers_id) ;
*!* VALUES (import_data.delivery_ldate, import_data.route_code, ;
*!* import_data.load_number, load_request_vp.route_status_id, ;
*!* load_request_vp.customer_code, load_request_vp.stop_number, ;
*!* lnCaseRequest, lnPalletize_flag, null, 0, 0, 0, lnPackRequest, ;
*!* lnUnitRequest, 0, 0, 0, lnAllow_change_palletize_flag, ;
*!* import_data.pick_type_code, sequence.nextval)
USE IN sequence
ENDIF
USE IN palletize_customers
ENDIF
ENDIF
USE IN load_request
ENDIF
lnCurrentRecord = lnCurrentRecord + 1
lnCurrentPercent = 100 * lnCurrentRecord / lnTotalRecords
IF lnCurrentPercent > lnPreviousPercent + 1
lnPreviousPercent = lnCurrentPercent
IF NOT plAutomatedImport
oProgressWindow.update(lnCurrentPercent)
ENDIF
ENDIF
SELECT import_data
ENDSCAN

IF USED('route_status')
USE IN route_status
ENDIF

>>I recently switched from using views to using sql pass through. I have an operation that inserts about 6800 records. With the switch from views to sql pass thru the time has increased significantly. The sqlpassthru command that inserts the records inserts each individually and runs for 100 seconds. Is there something I missed when doing this or should I have stayed with views?
>>
>>Jason
>
>Jason;
>
>Post your code.
>
>Tom