API tools faq
paste
Advertisement
trboyden

Item Warehouse Clone Script

trboyden
Feb 28th, 2018
144
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
T-SQL 4.79 KB | None | 0 0
raw download clone embed print report
  1. /**
  2.     ITEM WAREHOUSE CLONE
  3.     This script will copy the records for one warehouse into another warehouse,
  4.     using the values from the original warehouse. Check the field listing to be sure
  5.     it matches the itemwhse schema of you application database. Test against a pilot
  6.     database before using in a production environment. Code provided with no guarantee
  7.     or warranty.
  8. **/
  9.  
  10. -- Set the application database to execute against:
  11. USE CSI_App;
  12.  
  13. DECLARE @whse_A AS WhseType;
  14. DECLARE @whse_B AS WhseType;
  15.  
  16. -- Set your old and new warehouses here:
  17. SET @whse_A ='MAIN';
  18. SET @whse_B = 'NEW';
  19.  
  20. -- Insert the new records:
  21. BEGIN TRANSACTION @CloneItemWarehouse;
  22.  
  23. INSERT INTO [dbo].[itemwhse] (
  24.      [item]
  25.     ,[whse]
  26.     ,[qty_on_hand]
  27.     ,[qty_alloc_co]
  28.     ,[alloc_trn]
  29.     ,[qty_trans]
  30.     ,[qty_ordered]
  31.     ,[qty_mrb]
  32.     ,[qty_sold_ytd]
  33.     ,[qty_pur_ytd]
  34.     ,[trn_in_ytd]
  35.     ,[trn_out_ytd]
  36.     ,[qty_reorder]
  37.     ,[sales_ytd]
  38.     ,[sales_ptd]
  39.     ,[cycle_freq]
  40.     ,[last_cycle]
  41.     ,[cnt_in_proc]
  42.     ,[cycle_flag]
  43.     ,[cycle_type]
  44.     ,[phy_inv_qty]
  45.     ,[phy_inv_dte]
  46.     ,[qty_wip]
  47.     ,[qty_rsvd_co]
  48.     ,[replen_po_num]
  49.     ,[replen_ps_num]
  50.     ,[replen_trn_num]
  51.     ,[NoteExistsFlag]
  52.     ,[RecordDate]
  53.     ,[RowPointer]
  54.     ,[CreatedBy]
  55.     ,[UpdatedBy]
  56.     ,[CreateDate]
  57.     ,[InWorkflow]
  58.     ,[count_sequence]
  59.     ,[last_count_qty_on_hand]
  60.     ,[unit_cost]
  61.     ,[lst_u_cost]
  62.     ,[avg_u_cost]
  63.     ,[asm_setup]
  64.     ,[asm_run]
  65.     ,[asm_matl]
  66.     ,[asm_tool]
  67.     ,[asm_fixture]
  68.     ,[asm_other]
  69.     ,[asm_fixed]
  70.     ,[asm_var]
  71.     ,[asm_outside]
  72.     ,[comp_setup]
  73.     ,[comp_run]
  74.     ,[comp_matl]
  75.     ,[comp_tool]
  76.     ,[comp_fixture]
  77.     ,[comp_other]
  78.     ,[comp_fixed]
  79.     ,[comp_var]
  80.     ,[comp_outside]
  81.     ,[sub_matl]
  82.     ,[cur_u_cost]
  83.     ,[unit_mat_cost]
  84.     ,[unit_duty_cost]
  85.     ,[unit_freight_cost]
  86.     ,[unit_brokerage_cost]
  87.     ,[unit_insurance_cost]
  88.     ,[unit_loc_frt_cost]
  89.     ,[cur_mat_cost]
  90.     ,[cur_duty_cost]
  91.     ,[cur_freight_cost]
  92.     ,[cur_brokerage_cost]
  93.     ,[cur_insurance_cost]
  94.     ,[cur_loc_frt_cost]
  95.     ,[matl_cost]
  96.     ,[lbr_cost]
  97.     ,[fovhd_cost]
  98.     ,[vovhd_cost]
  99.     ,[out_cost]
  100.     ,[cur_matl_cost]
  101.     ,[cur_lbr_cost]
  102.     ,[cur_fovhd_cost]
  103.     ,[cur_vovhd_cost]
  104.     ,[cur_out_cost]
  105.     ,[avg_matl_cost]
  106.     ,[avg_lbr_cost]
  107.     ,[avg_fovhd_cost]
  108.     ,[avg_vovhd_cost]
  109.     ,[avg_out_cost]
  110.     ,[min_consignment_qty]
  111.     ,[max_consignment_qty]
  112.     ,[qty_contained]
  113.     ,[fs_p_m_t_code]
  114.     ,[fs_supply_whse]
  115.     ,[fs_vend_num]
  116.     ,[fs_order_min]
  117.     ,[fs_order_max]
  118.     ,[fs_order_mult]
  119.     ,[fs_days_supply]
  120.     ,[fs_consumable]
  121.     ,[fs_drp_low_level]
  122.     ,[fs_qty_for_rental])
  123. SELECT
  124.      [item]
  125.     ,@whse_B -- Replace the old whse value with the new whse value
  126. -- Zero out, or set to default values, existing values for the current warehouse
  127. -- Keep the values for the fields you want to copy over
  128.     ,0 --[qty_on_hand]
  129.     ,0 --[qty_alloc_co]
  130.     ,0 --[alloc_trn]
  131.     ,0 --[qty_trans]
  132.     ,0 --[qty_ordered]
  133.     ,0 --[qty_mrb]
  134.     ,0 --[qty_sold_ytd]
  135.     ,0 --[qty_pur_ytd]
  136.     ,0 --[trn_in_ytd]
  137.     ,0 --[trn_out_ytd]
  138.     ,0 --[qty_reorder]
  139.     ,0 --[sales_ytd]
  140.     ,0 --[sales_ptd]
  141.     ,[cycle_freq]
  142.     ,NULL --[last_cycle]
  143.     ,0 --[cnt_in_proc]
  144.     ,[cycle_flag]
  145.     ,[cycle_type]
  146.     ,0 --[phy_inv_qty]
  147.     ,NULL --[phy_inv_dte]
  148.     ,0 --[qty_wip]
  149.     ,0 --[qty_rsvd_co]
  150.     ,[replen_po_num]
  151.     ,[replen_ps_num]
  152.     ,[replen_trn_num]
  153.     ,0 --[NoteExistsFlag]
  154.     ,NULL --[RecordDate]
  155.     ,NULL --[RowPointer]
  156.     ,NULL --[CreatedBy]
  157.     ,NULL --[UpdatedBy]
  158.     ,NULL --[CreateDate]
  159.     ,0 --[InWorkflow]
  160.     ,1 --[count_sequence]
  161.     ,0 --[last_count_qty_on_hand]
  162.     ,0 --[unit_cost]
  163.     ,0 --[lst_u_cost]
  164.     ,0 --[avg_u_cost]
  165.     ,0 --[asm_setup]
  166.     ,0 --[asm_run]
  167.     ,0 --[asm_matl]
  168.     ,0 --[asm_tool]
  169.     ,0 --[asm_fixture]
  170.     ,0 --[asm_other]
  171.     ,0 --[asm_fixed]
  172.     ,0 --[asm_var]
  173.     ,0 --[asm_outside]
  174.     ,0 --[comp_setup]
  175.     ,0 --[comp_run]
  176.     ,0 --[comp_matl]
  177.     ,0 --[comp_tool]
  178.     ,0 --[comp_fixture]
  179.     ,0 --[comp_other]
  180.     ,0 --[comp_fixed]
  181.     ,0 --[comp_var]
  182.     ,0 --[comp_outside]
  183.     ,0 --[sub_matl]
  184.     ,0 --[cur_u_cost]
  185.     ,0 --[unit_mat_cost]
  186.     ,0 --[unit_duty_cost]
  187.     ,0 --[unit_freight_cost]
  188.     ,0 --[unit_brokerage_cost]
  189.     ,0 --[unit_insurance_cost]
  190.     ,0 --[unit_loc_frt_cost]
  191.     ,0 --[cur_mat_cost]
  192.     ,0 --[cur_duty_cost]
  193.     ,0 --[cur_freight_cost]
  194.     ,0 --[cur_brokerage_cost]
  195.     ,0 --[cur_insurance_cost]
  196.     ,0 --[cur_loc_frt_cost]
  197.     ,0 --[matl_cost]
  198.     ,0 --[lbr_cost]
  199.     ,0 --[fovhd_cost]
  200.     ,0 --[vovhd_cost]
  201.     ,0 --[out_cost]
  202.     ,0 --[cur_matl_cost]
  203.     ,0 --[cur_lbr_cost]
  204.     ,0 --[cur_fovhd_cost]
  205.     ,0 --[cur_vovhd_cost]
  206.     ,0 --[cur_out_cost]
  207.     ,0 --[avg_matl_cost]
  208.     ,0 --[avg_lbr_cost]
  209.     ,0 --[avg_fovhd_cost]
  210.     ,0 --[avg_vovhd_cost]
  211.     ,0 --[avg_out_cost]
  212.     ,0 --[min_consignment_qty]
  213.     ,0 --[max_consignment_qty]
  214.     ,0 --[qty_contained]
  215.     ,NULL --[fs_p_m_t_code]
  216.     ,NULL --[fs_supply_whse]
  217.     ,[fs_vend_num]
  218.     ,[fs_order_min]
  219.     ,[fs_order_max]
  220.     ,[fs_order_mult]
  221.     ,[fs_days_supply]
  222.     ,0 --[fs_consumable]
  223.     ,-1 --[fs_drp_low_level]
  224.     ,0 --fs_qty_for_rental]
  225. FROM
  226.     [dbo].[itemwhse] (nolock)
  227. WHERE
  228.     whse = @whse_A; -- Filter for records from the old warehouse
  229.  
  230. COMMIT TRANSACTION @CloneItemWarehouse;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
Public Pastes
Advertisement
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!