insert trigger on iminvloc

[PhilPPhillips]

Has anyone created a trigger on the table iminvloc that would automatically create item locations for each location defined in imlocfil? Every field would be the same except for the "loc" field.

My users want to create one item location record and have all of the others automatically create.

I'm pretty sure this can be done with a trigger but is there any reason why I shouldn't create location records with a trigger? And if it can be done with a trigger I need some coding help.

Thanks ...

 

[NEmacGuy]

I had a client that requested the same thing. They consigned inventory at many locations. They constantly added new locations. So we added a trigger on Location file that automatically created the IMINVLOC records, with 0's for value data fields (except price and cost).
We wrote a Stored Proc to add the location record. The trigger called the stored proc and passed the location.

 

[dgillz]

The functionality NEmacGuy mentioned already exists inside Macola with no additional coding needed. Go to IM, Processes, Add/Delete Item Locations.

Leave the item number blank for all items, make the action add, and chose each location individually (there is no All option here). After the bottom of the screen is populated with all the locations, hit process and the new IMINVLOC_SQL records are created.

NEmacguy is right: the thing to watch out for is that Macola does not assume anything about the new item location except the unit of measure and description. All thing that are unique to an item location record such as price, cost, primary vendor, lead time, planning order multiple, etc. are either not populated or they are zero.

Finally, to complete the loop you could write code to copy over those desired data elements from the default location to the target locations. I would do this with Flexibility on the close form event on the IMCRELOC screen.

Hey NEmacguy: You going to EPIC?

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

 

[PhilPPhillips]

I am aware of the Add/Delete Item Locations application. However, I don't need locations for every item for every location. My users just want to be able to create locations "on the fly" that will create all the locations they need.

I was able to do this with a trigger that calls a stored procedure.

Here is the trigger.

CREATE TRIGGER run_consignment_trigger on iminvloc_sql 
FOR INSERT
AS

BEGIN
if @@ROWCOUNT =0 
return


declare @item char(15), @loc char(3)
select @item=item_no , @loc=loc  from inserted
If @loc = "89"
begin
exec create_con_loc @item
end
END

[\code]

this is the stored procedure

[code]
CREATE PROCEDURE create_con_loc

@item	char(15)

AS

insert into iminvloc_sql
(item_no, item_filler, loc, status, prev_status, mult_bin_fg, qty_on_hand, qty_allocated, qty_bkord, qty_on_ord, reorder_lvl, ord_up_to_lvl, price,
avg_cost, last_cost, std_cost, prcs_apply_fg, discs_apply_fg, starting_sls_dt, ending_sls_dt, last_sold_dt, sls_price, qty_last_sold,
cycle_count_cd, last_count_dt, tms_cntd_ytd, pct_err_last_cnt, frz_cost, frz_qty, frz_dt, frz_tm, usage_ptd, qty_sld_ptd, qty_scrp_ptd,
sls_ptd, cost_ptd, usage_ytd, qty_sold_ytd, qty_scrp_ytd, qty_returned_ytd, sls_ytd, cost_ytd, prior_year_usage, qty_sold_last_yr,
qty_scrp_last_yr,prior_year_sls, cost_last_yr, recom_min_ord, economic_ord_qty, avg_usage,  po_lead_tm, byr_plnr, doc_to_stk_ld_tm, 
rollup_prc, target_margin, inv_class, po_min, po_max, safety_stk, avg_frcst_error, sum_of_errors, usg_wght_fctr, safety_fctr, usage_filter,
po_mult, active_ords, vend_no, tax_sched, prod_cat, picking_seq, cube_width_uom, cube_length_uom, cube_height_uom, cube_width,
cube_length, cube_height, cube_qty_per, user_def_fld_1, user_def_fld_2, user_def_fld_3, user_def_fld_4, user_def_fld_5, landed_cost_cd,
landed_cost_cd_2, landed_cost_cd_3, landed_cost_cd_4, landed_cost_cd_5, landed_cost_cd_6, landed_cost_cd_7, landed_cost_cd_8,
landed_cost_cd_9, landed_cost_cd_10, loc_qty_fld, tag_qty, tag_cost, tag_frz_dt, filler_0002)

select substring(a.item_no,1,15), b.item_filler, c.loc, status, prev_status, b.mult_bin_fg, qty_on_hand, qty_allocated, qty_bkord, qty_on_ord, reorder_lvl, ord_up_to_lvl, price,
avg_cost, last_cost, std_cost, prcs_apply_fg, discs_apply_fg, starting_sls_dt, ending_sls_dt, last_sold_dt, sls_price, qty_last_sold,
cycle_count_cd, last_count_dt, tms_cntd_ytd, pct_err_last_cnt, frz_cost, frz_qty, b.frz_dt, frz_tm, usage_ptd, qty_sld_ptd, qty_scrp_ptd,
sls_ptd, cost_ptd, usage_ytd, qty_sold_ytd, qty_scrp_ytd, qty_returned_ytd, sls_ytd, cost_ytd, prior_year_usage, qty_sold_last_yr,
qty_scrp_last_yr, prior_year_sls, cost_last_yr, recom_min_ord, economic_ord_qty, avg_usage,  po_lead_tm, b.byr_plnr, doc_to_stk_ld_tm, 
rollup_prc, target_margin, inv_class, po_min, po_max, safety_stk, avg_frcst_error, sum_of_errors, usg_wght_fctr, safety_fctr, usage_filter,
po_mult, active_ords, vend_no, tax_sched, b.prod_cat, picking_seq, b.cube_width_uom, b.cube_length_uom, b.cube_height_uom, b.cube_width,
b.cube_length, b.cube_height, b.cube_qty_per, b.user_def_fld_1, b.user_def_fld_2, b.user_def_fld_3, b.user_def_fld_4, b.user_def_fld_5, b.landed_cost_cd,
b.landed_cost_cd_2, b.landed_cost_cd_3, b.landed_cost_cd_4, b.landed_cost_cd_5, b.landed_cost_cd_6, b.landed_cost_cd_7, b.landed_cost_cd_8,
b.landed_cost_cd_9, b.landed_cost_cd_10, b.loc_qty_fld, b.tag_qty, b.tag_cost, tag_frz_dt, b.filler_0002
from imitmidx_sql as a, iminvloc_sql as b, imlocfil_sql as c
where b.loc=89
and a.item_no=b.item_no
and c.loc like "%V"
and a.item_no=@item

[\code]

 

[dgillz]

Phil,

The add/create item locations process does not make you run this for all items, all locations. So there is still a lot of the functionality you want already built in.

It looks like you have everything well in hand with your trigger though.

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz