/* Object: StoredProcedure [dbo].[USP_DELETE_WO_FROM_TKT]    Script Date: 12/28/2009 13:34:40 */
/*         This stored procedure will delete a work order in the ServicePoint                 */ 
/*         database, that was created based on a CP order.  The CP ticket's DOC_NO is passed  */

/*         CUSTOM VERSION for Tree Top Nursey that will pre-sched labor based on the sched    */
/*         date and preferred tome slot passed in the doc line profile prompts 03/29/2011     */

USE [SPDBGoesHere]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/* Drop old [USP_DELETE_WO_FROM_TKT] procedure */
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[USP_DELETE_WO_FROM_TKT]') AND type in (N'P', N'PC'))
	DROP PROCEDURE [dbo].[USP_DELETE_WO_FROM_TKT]
GO

CREATE PROCEDURE [dbo].[USP_DELETE_WO_FROM_TKT]
  @Doc_Id		bigint
	as
	begin
		declare
			@strTicketNo		varchar(25),
			@strWO_No		varchar(25)


		/*---------- Fetch values from PS_DOC_HDR ---------------------*/
		select @strTicketNo = TKT_NO
			from [CPDBGoesHere].[dbo].[PS_DOC_HDR]
				where DOC_ID = @Doc_ID

		/*---------- Delete any previously written SP table entries ---------------------*/
		
	                set @strWO_No = null 
			select @strWO_No = WORK_ORDER_NO from WO_LABOR where WORK_ORDER_NO = @strTicketNo
			if @strWO_No is not null
				DELETE from WO_LABOR where WORK_ORDER_NO = @strTicketNo 

	                set @strWO_No = null 
			select @strWO_No = WORK_ORDER_NO from WO_PARTS where WORK_ORDER_NO = @strTicketNo
			if @strWO_No is not null
				DELETE from WO_PARTS where WORK_ORDER_NO = @strTicketNo 

	                set @strWO_No = null 
			select @strWO_No = WORK_ORDER_NO from WO_NOTES where WORK_ORDER_NO = @strTicketNo
			if @strWO_No is not null
				DELETE from WO_NOTES where WORK_ORDER_NO = @strTicketNo 

	                set @strWO_No = null 
			select @strWO_No = WORK_ORDER_NO from WO_TASK where WORK_ORDER_NO = @strTicketNo
			if @strWO_No is not null
				DELETE from WO_TASK where WORK_ORDER_NO = @strTicketNo 

	                set @strWO_No = null 
			select @strWO_No = WORK_ORDER_NO from WORK_ORDER where WORK_ORDER_NO = @strTicketNo
			if @strWO_No is not null
				DELETE from WORK_ORDER where WORK_ORDER_NO = @strTicketNo 
		

	end    	/*-- End of Proc */

GO




/* Object: StoredProcedure [dbo].[USP_INSERT_WO_FROM_TKT]    Script Date: 12/28/2009 13:34:40 */
/*         This stored procedure will call insert a work order into the ServicePoint          */ 
/*         database, created based on a CP order.  The CP ticket's DOC_NO is passed           */

USE [SPDBGoesHere]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/* Drop old [USP_INSERT_WO_FROM_TKT] procedure */
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[USP_INSERT_WO_FROM_TKT]') AND type in (N'P', N'PC'))
	DROP PROCEDURE [dbo].[USP_INSERT_WO_FROM_TKT]
GO

CREATE PROCEDURE [dbo].[USP_INSERT_WO_FROM_TKT]
  @Doc_Id		bigint
	as
	begin
		declare
			/*-- Data from CONTROL_INFO --*/
			@InvcCode			varchar(25),
			@LogStatus			varchar(25),
			/*-- Data from SVC_ADDR --*/
			@strMapGrid			varchar(50),
			@strZone			varchar(25),
			@strContact1Name		varchar(50),
			/*-- Data from PS_DOC_CONTACT --*/
            		@SVC_SITE_NAME			varchar(50),
            		@SVC_ADDR_1			varchar(50),
            		@SVC_ADDR_2			varchar(50),
            		@SVC_CITY			varchar(25),
            		@SVC_STATE			varchar(25),
            		@SVC_ZIP_CODE			varchar(25),
            		@SVC_COUNTRY			varchar(25),
            		@CONTACT_1_NAME			varchar(50),
            		@CONTACT_1_PHONE		varchar(50),
			/*-- Data from PS_DOC_LIN	--*/
			@strTaskItemDesc		varchar(50),
			/*-- Data for insert to WO_PARTS --*/	
			@dblNextSeqNo			bigint,
            		@ITEM_NO			varchar(25),
            		@STK_LOC_ID			varchar(25),
            		@DESCR				varchar(50),
            		@QTY_SOLD			bigint,
            		@IS_TXBL			varchar(1),
			/*-- Data for insert to WO_NOTES --*/	
            		@NOTE_ID			varchar(25),
            		@NOTE         			varchar(500),
            		@NOTE_TXT			varchar(500)

		/*---------- Fetch values from CONTROL_INFO ---------------------*/
		select
			@InvcCode = DFLT_INVC_CODE,
			@LogStatus = DFLT_LOGGED_STATUS
				from CONTROL_INFO
				where CTL_KEY = '1'

		/*---------- Fetch values from SVC_ADDRESS ---------------------*/
		select
			@strMapGrid = MAP_GRID,
			@strZone = ZONE,
			@strContact1Name = CONTACT_1
				from SVC_ADDRESS
				where CUSTOMER = (select CUST_NO from [CPDBGoesHere].[dbo].[PS_DOC_HDR]
									where DOC_ID = @Doc_ID)
					and Address_ID = '"MAIN"'

		/*---------- Fetch values from PS_DOC_CONTACT ---------------------*/
        	set @SVC_SITE_NAME = null
        	set @SVC_ADDR_1 = null
        	set @SVC_ADDR_2 = null
        	set @SVC_CITY = null
        	set @SVC_STATE = null
        	set @SVC_ZIP_CODE = null

		select
            	@SVC_SITE_NAME = NAM,
            	@SVC_ADDR_1 = ADRS_1,
            	@SVC_ADDR_2 = ADRS_2,
            	@SVC_CITY = CITY,
            	@SVC_STATE = State,
            	@SVC_ZIP_CODE = ZIP_COD,
            	@SVC_COUNTRY = CNTRY,
            	@CONTACT_1_NAME = CONTCT_1,
            	@CONTACT_1_PHONE = PHONE_1
				from [CPDBGoesHere].[dbo].[PS_DOC_CONTACT]
				where DOC_ID = @Doc_ID 
					and  CONTACT_ID = (select SHIP_TO_CONTACT_ID from [CPDBGoesHere].[dbo].[PS_DOC_HDR]
									where DOC_ID = @Doc_ID)

		/*---------- Fetch values from AR_CUST ---------------------*/
		if @SVC_SITE_NAME is null and
        	   @SVC_ADDR_1 is null and
        	   @SVC_ADDR_2 is null and
        	   @SVC_CITY is null and
        	   @SVC_STATE is null and
        	   @SVC_ZIP_CODE is null				
			begin			
				select
					@SVC_SITE_NAME = NAM,
					@SVC_ADDR_1 = ADRS_1,
					@SVC_ADDR_2 = ADRS_2,
					@SVC_CITY = CITY,
					@SVC_STATE = State,
					@SVC_ZIP_CODE = ZIP_COD,
					@SVC_COUNTRY = CNTRY,
					@CONTACT_1_NAME = @strContact1Name,
					@CONTACT_1_PHONE = PHONE_1
						from [CPDBGoesHere].[dbo].[AR_CUST]
						where CUST_NO = (select CUST_NO from [CPDBGoesHere].[dbo].[PS_DOC_HDR]
										where DOC_ID = @Doc_ID)
			end

		if @SVC_SITE_NAME is null				
			begin			
				select
					@SVC_SITE_NAME = NAM
						from [CPDBGoesHere].[dbo].[AR_CUST]
						where CUST_NO = (select CUST_NO from [CPDBGoesHere].[dbo].[PS_DOC_HDR]
										where DOC_ID = @Doc_ID)
			end

		/*---------- Fetch values from PS_DOC_LIN ---------------------*/
		select
			@strTaskItemDesc = DESCR
				from [CPDBGoesHere].[dbo].[PS_DOC_LIN]
                             /*  old = WHERE (DOC_ID = @Doc_ID and
					CATEG_COD = 'SCHED')  */

                              WHERE (DOC_ID = @Doc_ID

			/* Conditions for trigger to call WO insert proc                     */
			/* Use these here to select the task description                     */
			/* Change or add to these to tailor the selection process as needed  */
			
			/*---- Doc line item_no = "DELIVER" ----*/
			               and ITEM_NO = 'ItemNoGoesHere'

			/*---- Doc line cat = "SCHED" ----*/
			               and CATEG_COD = 'ItemCatGoesHere'
			
			/*---- Doc line subcat = "SUBCAT" ----*/
			               and SUBCAT_COD = 'ItemSubCatGoesHere'
			
			/* end conditions */
                                                           )


		if @strTaskItemDesc is null
			set @strTaskItemDesc = 'Delivery'

		/*---------- Insert WORK_ORDER record ---------------------*/
		insert into WORK_ORDER (
			WORK_ORDER_NO,
			CUSTOMER,
			ADDRESS_ID,
			STATUS,
			OPEN_DATE,
			OPEN_TIME,
			SVC_SITE_NAME,
			SVC_ADDR_1,
			SVC_ADDR_2,
			SVC_CITY,
			SVC_STATE,
			SVC_ZIP_CODE,
            		SVC_COUNTRY,
            		CONTACT_1_NAME,
            		CONTACT_1_PHONE,
			PO_NO,
			MAP_GRID,
			DISPATCH_NOTE,
			TAX_CODE,
			ZONE,
			SALES_REP,
			TERMS_CODE,
			Xfer_To_Tkt_No
			)
		select
			TKT_NO,
			CUST_NO,
			'"MAIN"',
			@LogStatus,
			Convert(Varchar(10), GETDATE(), 101) AS [MM/DD/YYYY],
			Convert(Varchar,GETDATE(),8),
			@SVC_SITE_NAME,
			@SVC_ADDR_1,
			@SVC_ADDR_2,
			@SVC_CITY,
			@SVC_STATE,
			@SVC_ZIP_CODE,
            		@SVC_COUNTRY,
            		@CONTACT_1_NAME,
            		@CONTACT_1_PHONE,
			CUST_PO_NO,
			@strMapGrid,
			'Auto created from POS ticket ' + TKT_NO,
			TAX_COD,
			@strZone,
			SLS_REP,
			TERMS_COD,
			TKT_NO
				from [CPDBGoesHere].[dbo].[PS_DOC_HDR]
					where DOC_ID = @Doc_ID

		/*---------- Insert WO_TASK record ---------------------*/
		insert into WO_TASK (
			WORK_ORDER_NO,
			TASK_SEQ_NO,
			[NAME],
			INVOICING_CODE
			)
		select
			TKT_NO,
			10,
			@strTaskItemDesc,
			@InvcCode
				from [CPDBGoesHere].[dbo].[PS_DOC_HDR]
					where DOC_ID = @Doc_ID

		/*---------- Insert WO_PARTS records ---------------------*/

		set @dblNextSeqNo = 0
		declare curTktLines cursor for
			select ITEM_NO, STK_LOC_ID, DESCR, QTY_SOLD, IS_TXBL
				from [CPDBGoesHere].[dbo].[PS_DOC_LIN]
					where DOC_ID = @Doc_ID
		open curTktLines
		fetch next from curTktLines into @ITEM_NO, @STK_LOC_ID,
						 @DESCR, @QTY_SOLD, @IS_TXBL
		while @@Fetch_Status = 0
		begin
			set @dblNextSeqNo = (@dblNextSeqNo + 10) --increment pts seq no
			insert into WO_PARTS (
                    WORK_ORDER_NO,
                    TASK_SEQ_NO,
                    PTS_SEQ_NO,
                    ITEM_NO,
                    INV_LOC,
                    description,
                    QTY_USED,
                    UNIT_COST,
                    UNIT_PRICE,
                    DISCOUNT_PCT,
                    IS_TAXABLE,
                    XFERRED_FROM_POS
				)
			select
				TKT_NO,
				10,
				@dblNextSeqNo,
				@ITEM_NO, 
				@STK_LOC_ID,
				@DESCR,
				@QTY_SOLD,
                /*cost/price, zeros?  since it's already on the order?*/
				0,
				0,
				0,
				@IS_TXBL,
				'Y'
					from [CPDBGoesHere].[dbo].[PS_DOC_HDR]
						where DOC_ID = @Doc_ID

			fetch next from curTktLines into @ITEM_NO, @STK_LOC_ID,
							 @DESCR, @QTY_SOLD, @IS_TXBL
		end
		close curTktLines
		deallocate curTktLines

		/*---------- Insert WO_NOTES records ---------------------*/

		set @dblNextSeqNo = 0
		declare curTktNotes cursor for
			select NOTE_ID, NOTE, NOTE_TXT
				from [CPDBGoesHere].[dbo].[PS_DOC_NOTE]
					where DOC_ID = @Doc_ID
		open curTktNotes
		fetch next from curTktNotes into @NOTE_ID, @NOTE, @NOTE_TXT
		while @@Fetch_Status = 0
		begin
			set @dblNextSeqNo = (@dblNextSeqNo + 10) --increment pts seq no
			insert into WO_NOTES (
                    WORK_ORDER_NO,
                    NOTE_ID,
                    DESCRIPTION,
                    NOTE_TEXT,
                    RAW_TEXT,
                    LAST_MAINT_DATE,
                    PRT_ON_WO,
                    PRT_ON_INVOICE
				)
			select
				TKT_NO,
				@NOTE_ID,
				left(@NOTE_TXT, 30), 
				@NOTE,
				@NOTE_TXT,
				GETDATE(),
				'Y',
				'N'
					from [CPDBGoesHere].[dbo].[PS_DOC_HDR]
						where DOC_ID = @Doc_ID

			fetch next from curTktNotes into @NOTE_ID, @NOTE, @NOTE_TXT
		end
		close curTktNotes 
		deallocate curTktNotes 



		/*---------- Update the PS_DOC_HDR to indicate transfer -------------------*/
		Update [CPDBGoesHere].[dbo].[PS_DOC_HDR]
			set REF = 'Transferred to ServicePoint ' + 			
						(Convert(Varchar(10), GETDATE(), 101)) +
						' ' +
						(Convert(Varchar,GETDATE(),8))
			where DOC_ID = @Doc_ID
	
	/*--- raiserror 'Service work order created for this ticket.'*/

	end    	/*-- End of Proc */

GO