IF exists (select * from INFORMATION_SCHEMA.ROUTINES where ROUTINE_NAME = 'dd_ExtenderHeader_INS') begin
DROP proc dd_ExtenderHeader_INS
end
GO
CREATE proc dd_ExtenderHeader_INS
-- dd_ExtenderHeader_INS 'BRS/10779',3,'','','','SOPLINE','Customer Directed Price',321
@Extender_Key_Values_1 varchar(51),
@Extender_Key_Values_2 varchar(51),
@Extender_Key_Values_3 varchar(51),
@Extender_Key_Values_4 varchar(51),
@Extender_Key_Values_5 varchar(51),
@Extender_Window_ID VARCHAR(15), --'INVOICE INFO'
@FIELDNAM varchar(31),
@NewValue varchar(255)
AS
set transaction isolation level read uncommitted
--the insert will be into EXT01101 or EXT01102 (or 03, or 04, but those tables are not implemented
--first, figure out which table
declare @FIELDTYPE INT
declare @FIELD_ID INT
DECLARE @Extender_Record_ID int
declare @dt date
set @dt = getdate()
--get the field id
select @FIELD_ID = dbo.ddf_GetExtenderFieldID2 (@Extender_Window_ID, @FIELDNAM)
--=========================================================================================
--get the field type that we'll be inserting (string, int, etc)
--=========================================================================================
SELECT @FIELDTYPE = FIELDTYPE
FROM EXT20010
WHERE Field_ID = @FIELD_ID
IF @FIELDTYPE is null begin
raiserror('FIELDNAM not found in EXT20010',16,2)
return
end
--=========================================================================================
-- be sure there is a record in EXT01100
--=========================================================================================
select @Extender_Record_ID = e0.Extender_Record_ID
from ext01100 e0
where e0.Extender_Window_ID = @Extender_Window_ID
and e0.Extender_Key_Values_1 = @Extender_Key_Values_1
and e0.Extender_Key_Values_2 = @Extender_Key_Values_2
and e0.Extender_Key_Values_3 = @Extender_Key_Values_3
and e0.Extender_Key_Values_4 = @Extender_Key_Values_4
and e0.Extender_Key_Values_5 = @Extender_Key_Values_5
--todo add the rest of the keys
--insert into EXT01100
if @Extender_Record_ID is null begin
select @Extender_Record_ID = max(Extender_Record_ID) + 1
from ext01100
if @Extender_Record_ID is null begin
set @Extender_Record_ID = 1
end
insert into ext01100 (Extender_Record_ID , Extender_Window_ID , Extender_Key_Values_1 , Extender_Key_Values_2 , Extender_Key_Values_3 , Extender_Key_Values_4 , Extender_Key_Values_5 , NOTEINDX, MODIFDT, MDFUSRID, CREATDDT, CRUSRID)
values (@Extender_Record_ID, @Extender_Window_ID, @Extender_Key_Values_1, @Extender_Key_Values_2, @Extender_Key_Values_3, @Extender_Key_Values_4, @Extender_Key_Values_5, 0 , @dt , '' , @dt , '')
END
--=========================================================================================
--handle strings
--=========================================================================================
if @FIELDTYPE = 2 begin
--select top 5 * from ext01101
--select top 5 * from ext01100
--select * from ext01101
if exists (select 1 from ext01101 where Extender_Record_ID = @Extender_Record_ID and Field_ID = @FIELD_ID) begin
update ext01101 set STRGA255 = @NewValue where Extender_Record_ID = @Extender_Record_ID and Field_ID = @FIELD_ID
end else begin
insert into ext01101 (Extender_Record_ID, Field_ID, STRGA255)
values (@Extender_Record_ID, @FIELD_ID, @NewValue)
end
return
end
--=========================================================================================
--handle dates
--=========================================================================================
if @FIELDTYPE = 3 begin
if exists (select 1 from ext01102 where Extender_Record_ID = @Extender_Record_ID and Field_ID = @FIELD_ID) begin
update ext01102 set DATE1 = @NewValue where Extender_Record_ID = @Extender_Record_ID and Field_ID = @FIELD_ID
end else begin
insert into ext01102 (Extender_Record_ID , Field_ID , DATE1)
values (@Extender_Record_ID, @FIELD_ID, @NewValue)
end
return
end
--=========================================================================================
--handle numbers
--=========================================================================================
if @FIELDTYPE = 4 begin
if exists (select 1 from ext01103 where Extender_Record_ID = @Extender_Record_ID and Field_ID = @FIELD_ID) begin
update ext01103 set total = @NewValue where Extender_Record_ID = @Extender_Record_ID and Field_ID = @FIELD_ID
end else begin
insert into ext01103 (Extender_Record_ID , Field_ID , total, FUNCTAMT)
values (@Extender_Record_ID, @FIELD_ID, @NewValue, 0)
end
return
end
--=========================================================================================
--handle dropdowns, stored as number
--=========================================================================================
if @FIELDTYPE = 6 begin
--likely, the user will give us the text. Look it up in EXT20021
declare @Extender_List_ID int
select @Extender_List_ID = Extender_List_ID
from ext20020
where Extender_List_Desc = @FIELDNAM
if @Extender_List_ID is null begin
raiserror('Invalid dropdown name', 16,2)
return
end
declare @Extender_List_Item_ID int
select @Extender_List_Item_ID = Extender_List_Item_ID
from ext20021
where Extender_List_ID = @Extender_List_ID
and STRNG132 = @NewValue
if exists (select 1 from ext01103 where Extender_Record_ID = @Extender_Record_ID and Field_ID = @FIELD_ID) begin
update ext01103 set TOTAL = @Extender_List_Item_ID where Extender_Record_ID = @Extender_Record_ID and Field_ID = @FIELD_ID
end else begin
insert into ext01103 (Extender_Record_ID, Field_ID, TOTAL)
values (@Extender_Record_ID, @FIELD_ID, @Extender_List_Item_ID)
end
return
end
select 'fieldtype not supported'
GO
GRANT EXEC ON dd_ExtenderHeader_INS TO PUBLIC