/*************************************************************************************
** Cost Code Extender Lookup sample
**
** Prepared by:	Rod O'Connor, Briware Solutions
** Posted on:	10-Jan-2014
** Posted to:	http://briwaresolutions.com/blog/
**
*************************************************************************************/

--------------------------------------
-- INITIAL CONVERSION
--------------------------------------

-- This query inserts the Extender "ID Field" value
INSERT EXT00201(Extender_Form_ID, UD_Form_Field_ID, UD_Form_Field_ID_2,PT_UD_Number,STRGA255)
SELECT 'EXTCCLOOKUP', LTRIM(RTRIM(Cost_Code_Alias)), '', -12, ''
FROM JC40202 
WHERE LTRIM(RTRIM(Cost_Code_Alias)) NOT IN(SELECT UD_Form_Field_ID FROM EXT00200 WHERE Extender_Form_ID = 'EXTCCLOOKUP')

-- This query inserts the Extender "Description" value
INSERT EXT00201(Extender_Form_ID, UD_Form_Field_ID, UD_Form_Field_ID_2,PT_UD_Number,STRGA255)
SELECT 'EXTCCLOOKUP', LTRIM(RTRIM(Cost_Code_Alias)), '', -11, LTRIM(RTRIM(Cost_Code_Description))
FROM JC40202 
WHERE LTRIM(RTRIM(Cost_Code_Alias)) NOT IN(SELECT UD_Form_Field_ID FROM EXT00200 WHERE Extender_Form_ID = 'EXTCCLOOKUP')

-- This Query inserts the Extender Form ID's table record
INSERT EXT00200(Extender_Form_ID, UD_Form_Field_ID, UD_Form_Field_ID_2,UD_Form_Field_Desc, CREATDDT, CRUSRID, MDFUSRID, MODIFDT, NOTEINDX)
SELECT 'EXTCCLOOKUP', LTRIM(RTRIM(Cost_Code_Alias)), '', LTRIM(RTRIM(Cost_Code_Description)), MODIFDT, 'CONVERSION', '', MODIFDT, 0
FROM JC40202 
WHERE LTRIM(RTRIM(Cost_Code_Alias)) NOT IN(SELECT UD_Form_Field_ID FROM EXT00200 WHERE Extender_Form_ID = 'EXTCCLOOKUP')

GO

--------------------------------------
-- Ongoing Maintenance SQL Triggers
--------------------------------------

USE TWO
GO

IF EXISTS(SELECT * FROM sysobjects WHERE name = 'bwsJC40202_TR_I')
DROP TRIGGER bwsJC40202_TR_I
GO

CREATE TRIGGER bwsJC40202_TR_I
ON JC40202
FOR INSERT
AS

/*****************************************************************************************************
**					bwsJC40202_TR_I
**	Created By:		Rod O'Connor, Briware Solutions
**	Created Date:	11-Nov-2013
**
**	Comments:		In order to accommodate the Burden Custom application, we created a Cost Code Lookup
**					form using Extender.  This trigger will populate that lookup as new cost codes are
**					added by the users.
**
*****************************************************************************************************/
BEGIN -- TRIGGER

	-- Insert the Keys record
	INSERT EXT00200(Extender_Form_ID, UD_Form_Field_ID, UD_Form_Field_ID_2,UD_Form_Field_Desc, CREATDDT, CRUSRID, MDFUSRID, MODIFDT, NOTEINDX)
	SELECT 'EXTCCLOOKUP', LTRIM(RTRIM(Cost_Code_Alias)), '', LTRIM(RTRIM(m.Cost_Code_Description)), m.MODIFDT, 'TRIGGER', '', m.MODIFDT, 0
	FROM inserted m
	WHERE LTRIM(RTRIM(Cost_Code_Alias)) NOT IN(SELECT UD_Form_Field_ID FROM EXT00200 WHERE Extender_Form_ID = 'EXTCCLOOKUP')

	-- Create the Cost Code ID record
	INSERT EXT00201(Extender_Form_ID, UD_Form_Field_ID, UD_Form_Field_ID_2,PT_UD_Number,STRGA255)
	SELECT 'EXTCCLOOKUP', LTRIM(RTRIM(Cost_Code_Alias)), '', -12, ''		
	FROM inserted i
	WHERE LTRIM(RTRIM(Cost_Code_Alias)) NOT IN(SELECT UD_Form_Field_ID FROM EXT00201 WHERE Extender_Form_ID = 'EXTCCLOOKUP' AND PT_UD_Number = -12)

	-- Create the Description Record
	INSERT EXT00201(Extender_Form_ID, UD_Form_Field_ID, UD_Form_Field_ID_2,PT_UD_Number,STRGA255)
	SELECT 'EXTCCLOOKUP', LTRIM(RTRIM(Cost_Code_Alias)), '', -11, LTRIM(RTRIM(i.Cost_Code_Description))		
	FROM inserted i
	WHERE LTRIM(RTRIM(Cost_Code_Alias)) NOT IN(SELECT UD_Form_Field_ID FROM EXT00201 WHERE Extender_Form_ID = 'EXTCCLOOKUP' AND PT_UD_Number = -11)

END -- TRIGGER

GO

IF EXISTS(SELECT * FROM sysobjects WHERE name = 'bwsJC40202_TR_U')
DROP TRIGGER bwsJC40202_TR_U
GO

CREATE TRIGGER bwsJC40202_TR_U
ON JC40202
FOR UPDATE
AS

/*****************************************************************************************************
**					bwsJC40202_TR_U
**	Created By:		Rod O'Connor, Briware Solutions
**	Created Date:	12-Nov-2013
**
**	Comments:		In order to accommodate the Burden Custom application, we created a Cost Code Lookup
**					form using Extender.  This trigger will ensure that as users update the descriptions
**					of the cost codes, the lookup is kept up to date.
**
*****************************************************************************************************/
BEGIN -- TRIGGER

	-- Update the Keys record
	UPDATE e
	SET e.UD_Form_Field_Desc = LTRIM(RTRIM(i.Cost_Code_Description))
	FROM EXT00200 e INNER JOIN inserted i ON e.UD_Form_Field_ID = LTRIM(RTRIM(Cost_Code_Alias)) AND e.Extender_Form_ID = 'EXTCCLOOKUP'

	-- Update the Description record
	UPDATE e
	SET e.STRGA255 = LTRIM(RTRIM(i.Cost_Code_Description))
	FROM EXT00201 e INNER JOIN inserted i ON e.UD_Form_Field_ID = LTRIM(RTRIM(Cost_Code_Alias)) AND e.Extender_Form_ID = 'EXTCCLOOKUP' AND e.PT_UD_Number = -11

END -- TRIGGER

GO

IF EXISTS(SELECT * FROM sysobjects WHERE name = 'bwsJC40202_TR_D')
DROP TRIGGER bwsJC40202_TR_D
GO

CREATE TRIGGER bwsJC40202_TR_D
ON JC40202
FOR DELETE
AS

/*****************************************************************************************************
**					bwsJC40202_TR_D
**	Created By:		Rod O'Connor, Briware Solutions
**	Created Date:	12-Nov-2013
**
**	Comments:		In order to accommodate the Burden Custom application, we created a Cost Code Lookup
**					form using Extender.  This trigger will ensure that as users delete any of the cost 
**					codes, the lookup is kept up to date.
**
*****************************************************************************************************/
BEGIN -- TRIGGER

	-- Delete the Keys record
	DELETE EXT00200
	WHERE Extender_Form_ID = 'EXTCCLOOKUP' 
	AND UD_Form_Field_ID IN(SELECT LTRIM(RTRIM(Cost_Code_Alias)) FROM deleted)

	-- Delete the ID and Description records
	DELETE EXT00201
	WHERE Extender_Form_ID = 'EXTCCLOOKUP' 
	AND UD_Form_Field_ID IN(SELECT LTRIM(RTRIM(Cost_Code_Alias)) FROM deleted)

END -- TRIGGER

GO