/*
	Object:	Trigger [dbo].TR_CREATE_SCHED_WOs    Script Date: 12/28/2009 17:30:03   
			This trigger will call the routine USP_INSERT_WO_FROM_TKT in the 
			ServicePoint database to  create a work order from a CP order after 
			it is saved.
*/

USE [CPDBGoesHere]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET NOCOUNT ON
GO

/* Drop existing [TR_CREATE_SCHED_WOs] trigger */
IF EXISTS (SELECT name FROM sysobjects
        WHERE name = 'TR_CREATE_SCHED_WOs' AND type = 'TR')
   DROP TRIGGER [dbo].[TR_CREATE_SCHED_WOs]
GO
/* End of drop existing [TR_CREATE_SCHED_WOs] trigger */

/*--- CREATE TRIGGER [dbo].[TR_CREATE_SCHED_WOS]*/
CREATE TRIGGER [dbo].[TR_CREATE_SCHED_WOS]
		/*--- ON [dbo].PS_DOC_HDR*/
		/* Audit_log table is used so that the HDR and the LINS will both be 
		   written prior to the trigger being fired */
		ON [dbo].PS_DOC_AUDIT_LOG
	after INSERT
	AS 

		SET NOCOUNT ON

		declare @TDoc_ID bigint,
			@TDoc_Typ char(1),
			@TLink_Doc_No varchar(15),
			@TLogEntry varchar(25),
			@TAudit_Activity char(1)
		
		set @TDoc_ID=0 -- init
		set @TDoc_Typ='' -- init
		set @TLink_Doc_No='' -- init
		set @TLogEntry='' -- init
		set @TAudit_Activity='' -- init
		
		select
                      @TDoc_ID = DOC_ID,
                      @TDoc_Typ = DOC_TYP,
                      @TLink_Doc_No = LINK_DOC_NO,
                      @TLogEntry = LOG_ENTRY,
                      @TAudit_Activity = ACTIV
				FROM INSERTED WHERE
			(
			/* Conditions for trigger to call WO insert proc                     */
			/* Change or add to these to tailor the selection process as needed  */
			
			/*---- Doc type = "O" for order "T" for ticket ----*/
			EXISTS (SELECT TKT_NO FROM [dbo].[PS_DOC_HDR]
                              	WHERE PS_DOC_HDR.DOC_ID = INSERTED.DOC_ID and PS_DOC_HDR.DOC_TYP = 'DocTypeGoesHere')
			
			/*---- Doc line item_no = "DELIVER" ----*/
			and EXISTS (SELECT ITEM_NO FROM [dbo].[PS_DOC_LIN] WHERE PS_DOC_LIN.DOC_ID = INSERTED.DOC_ID and PS_DOC_LIN.ITEM_NO = 'ItemNoGoesHere')

			/*---- Doc line cat = "SCHED" ----*/
			and EXISTS (SELECT ITEM_NO FROM [dbo].[PS_DOC_LIN] WHERE PS_DOC_LIN.DOC_ID = INSERTED.DOC_ID and PS_DOC_LIN.CATEG_COD = 'ItemCatGoesHere')
			
			/*---- Doc line subcat = "SUBCAT" ----*/
			and EXISTS (SELECT ITEM_NO FROM [dbo].[PS_DOC_LIN] WHERE PS_DOC_LIN.DOC_ID = INSERTED.DOC_ID and PS_DOC_LIN.SUBCAT_COD = 'ItemSubCatGoesHere')
			
			/* end conditions */
			)

		/*---- Only proc for "NEW" docs ----*/

		declare @Activity varchar(5)
		/* Check the lst_activity from the hdr_doc_stat table. */
		/* Checking the ACTIV in the inserted table did not seem to work due to the rewrite of all audit_log rows */
		set @Activity = (select LST_DOC_ACTIV from [dbo].[PS_DOC_HDR_DOC_STAT] WHERE PS_DOC_HDR_DOC_STAT.DOC_ID = @TDoc_ID)

		declare @Is_Rel_Tkt char(1)
		set @Is_Rel_Tkt = (select IS_REL_TKT from [dbo].[PS_DOC_HDR] WHERE PS_DOC_HDR.DOC_ID = @TDoc_ID)
		
		/* Checking the @TAudit_Activity for the tkt docs allows us to process tkts. @TDoc_ID check makes sure that we don't proc released order tkts */
                if (@TDoc_ID is not null and @TDoc_Typ = 'O' and @Activity = 'N') or                              /* New Order   */
                      (@TDoc_Typ = 'T' and @TAudit_Activity = 'N' and @TLink_Doc_No is null) or                   /* New ticket = only add   */
                      (@TDoc_Typ = 'T' and @TAudit_Activity = 'E' and @TLogEntry = 'Entered a new ticket' and @Is_Rel_Tkt = 'N')  /* released hold ticket   */
		    begin
			EXEC [SPDBGoesHere].[dbo].[USP_DELETE_WO_FROM_TKT] @Doc_ID=@TDoc_ID
			EXEC [SPDBGoesHere].[dbo].[USP_INSERT_WO_FROM_TKT] @Doc_ID=@TDoc_ID
		    end

		if @TDoc_ID is not null and @TDoc_Typ = 'O' and @Activity = 'E' /* Edits done = delete old and then re-add new   */
		    begin	
			EXEC [SPDBGoesHere].[dbo].[USP_DELETE_WO_FROM_TKT] @Doc_ID=@TDoc_ID
			EXEC [SPDBGoesHere].[dbo].[USP_INSERT_WO_FROM_TKT] @Doc_ID=@TDoc_ID
		    end

	 	if (@TDoc_ID is not null and @Activity = 'C') or @TDoc_Typ = 'V'  /* Cancel order OR Void Ticket = delete wo  */
		    begin
			EXEC [SPDBGoesHere].[dbo].[USP_DELETE_WO_FROM_TKT] @Doc_ID=@TDoc_ID
		    end  


		if @TDoc_ID is not null and @Activity = 'I' /* Re-Instate = delete old (just for safety) and then add new   */
		    begin
			EXEC [SPDBGoesHere].[dbo].[USP_DELETE_WO_FROM_TKT] @Doc_ID=@TDoc_ID
			EXEC [SPDBGoesHere].[dbo].[USP_INSERT_WO_FROM_TKT] @Doc_ID=@TDoc_ID
		    end

/*	SELECT CustomerID 
	FROM inserted A
	WHERE NOT EXISTS (SELECT 'X' FROM pubs.dbo.Cust B
                               WHERE A.CustomerID = B.CustomerID) */
GO

