USE [P21]
GO

/****** Object:  StoredProcedure [dbo].[mh_sp_job_price_line_insert]    Script Date: 1/25/2025 1:44:55 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE proc [dbo].[mh_sp_job_price_line_insert] 
as



insert into job_price_line (job_price_line_uid,
job_price_hdr_uid,
inv_mast_uid,
uom,
unit_size,
pricing_method,
source_price,
price,
qty_ordered,
qty_maximum,
other_cost_type_cd,
other_cost_value,
other_cost_source_cd,
other_cost_calc_method_cd,
other_cost_calc_value,
commission_cost_type_cd,
commission_cost_value,
commission_cost_source_cd,
commission_cost_calc_method_cd,
commission_cost_calc_value,
row_status_flag,
date_last_modified,
date_created,
last_maintained_by,
multiplier,
line_no
)

select  
(select max(job_price_line_uid) from job_price_line) + row_number() over (partition by (select 1) order by im.item_id),
mh.job_hdr_uid,
im.inv_mast_uid,
case when mh.item_uom = 'DEFAULT' then im.sales_pricing_unit else mh.item_uom end as uom,
--case when mh.item_uom = 'DEFAULT' then im.sales_pricing_unit_size else  uom.unit_size end as unit_size,
null as unit_size,
221 as pricing_method,
101 as pricing_source,
mh.price,
0.000000000 as qty_ordered,
0.000000000 as qty_max,
222 as other_cost_type_cd,
1.000000000 as other_cost_value,
null as other_cost_source_cd,
211 as other_cost_calc_method_cd,
1.000000000 as other_cost_calc_value,
222 as commission_cost_type_cd,
1.000000000 as commission_cost_value,
null as commisison_cost_source_cd,
211 as commission_cost_calc_method_cd,
1.000000000 as commission_cost_calc_value,
704 as row_status_flag,
getdate() as date_last_modified,
getdate() as date_created,
'admin' as last_maintained_by,
0.000000000 as multiplier,
(select max(line_no) from job_price_line where job_price_hdr_uid = mh.job_hdr_uid) + row_number() over (partition by (select 1) order by im.item_id)
 from mh_tbl_job_lines mh
 inner join p21_view_inv_mast im on mh.item_id = im.item_id
left outer join p21_view_item_uom uom on im.inv_mast_uid = uom.inv_mast_uid and mh.item_uom = uom.unit_of_measure
 left outer join p21_view_job_price_line jpl on mh.job_hdr_uid = jpl.job_price_hdr_uid and mh.item_id = jpl.item_id
 where jpl.item_id is null and (mh.item_uom = 'DEFAULT' or uom.unit_of_measure is not null)






   declare @newcnt as int
  declare @oldcnt as int
  declare @cntint as int
  select @newcnt = max(job_price_line_uid) from job_price_line
SELECT  @oldcnt = counter_num  FROM [p21_view_counter]  where id =  'job_price_line'
 select @cntint = @newcnt - @oldcnt
 select @cntint
 
 
  exec p21_get_counter 'job_price_line', @cntint


  delete from mh_tbl_job_lines

GO


