Can you show how you did that with views?

>* 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