JC00102_INSUPD

-- =============================================
--
-- =============================================
-- 1/1/1900 created
IF EXISTS (SELECT name
    FROM   sysobjects
    WHERE name = N'_4P_JC00102_INSUPD'
    AND    type = 'P')
    DROP PROCEDURE _4P_JC00102_INSUPD
GO

CREATE PROCEDURE _4P_JC00102_INSUPD
-- _4P_JC00102_INSUPD '9B965D39-695F-46D5-85AA-1A9FDEBD5923'

@uuidMasterOrder uniqueidentifier

--select top 5 * from ngboed..masterorders where vchrcompanyidgp = 'stone'
declare @WS_Job_Number varchar(17),
@Project_Number varchar(17) ,
@WS_Job_Name varchar(31),
@Divisions varchar(15) ,
@CUSTNMBR varchar(15),
@vchrShipTo varchar(15),
@vchrBillTo varchar(11),
@Schedule_Start_Date datetime

SELECT @WS_Job_Number = convert(varchar(4), year(dtCreated)) + '-' + convert(varchar(6),mo.intmasternumber) ,
  @WS_Job_Name = subdivisions.vchrSubdivisionName + ' ' + isnull(mo.vchrLotNumber,''),
  --Divisions   ,
  @Divisions = case when mo.blnCommercialJob = 1 then 'COMMERCIAL' ELSE 'RESIDENTIAL' END,
  --CUSTNMBR
  @CUSTNMBR = mo.vchrCustomerNumberGP,
  --Job_Address_Code
  @vchrShipTo = mo.vchrAddressCodeGP,
  --Schedule_Start_Date
  @Schedule_Start_Date = mo.dtRequestedShipDate
from ngboed..masterorders mo with (nolock)
  left join ngboed..subdivisions subdivisions with (nolock) on subdivisions.uuidSubdivision = mo.uuidSubdivJobNameFK
where uuid = @uuidMasterOrder

select @vchrBillTo = prbtadcd
from rm00101 with (nolock)
where custnmbr = @custnmbr

if exists (select ws_job_number from jc00102 where ws_job_number = @ws_job_number ) begin
--already exists, update the job
update jc00102 set
   WS_Job_Name = @WS_Job_Name,
   Divisions = @Divisions,
   CUSTNMBR = @CUSTNMBR,
   Job_Address_Code = @vchrShipTo,
   Schedule_Start_Date = @Schedule_Start_Date
  where WS_Job_Number = @WS_Job_Number
end else begin
-- does not exist, insert job
INSERT INTO jc00102 (WS_Job_Number, WS_Job_Name , Divisions   , CUSTNMBR ,Job_Address_Code ,Schedule_Start_Date)
   values      (@WS_Job_Number, @WS_Job_Name, @Divisions , @CUSTNMBR ,@vchrShipTo       ,@Schedule_Start_Date)
end

if exists (select ws_job_number from jc30002 where ws_job_number = @ws_job_number ) begin
update jc30002 set
  ws_job_name = @WS_Job_Name
  where WS_Job_Number = @WS_Job_Number
end else begin
INSERT INTO jc30002 (WS_Job_Number , WS_Job_Name , WS_Module   )
     values          (@WS_Job_Number , @WS_Job_Name, 'JC'        )
end

--shipto info
select @ws_job_number
if exists (select ws_job_number from jc00106 where ws_job_number = @ws_job_number and ws_job_location_type = 1 ) begin
update jc00106 set
   CUSTNMBR = @CUSTNMBR,
   adrscode = @vchrShipTo,
   WS_Job_Location_Type = 1
  where WS_Job_Number = @WS_Job_Number
   and WS_Job_Location_Type = 1
end else begin
INSERT INTO jc00106 (WS_Job_Number , CUSTNMBR , ADRSCODE , WS_Job_Location_Type )
     values          (@WS_Job_Number , @CUSTNMBR , @vchrShipTo, 1                    )
end

--billto info
if exists (select ws_job_number from jc00106 where ws_job_number = @ws_job_number and ws_job_location_type = 2 ) begin
update jc00106 set
   CUSTNMBR = @CUSTNMBR,
   adrscode = @vchrShipTo,
   WS_Job_Location_Type = 2
  where WS_Job_Number = @WS_Job_Number
   and WS_Job_Location_Type = 2
end else begin
INSERT INTO jc00106 (WS_Job_Number , CUSTNMBR , ADRSCODE , WS_Job_Location_Type )
     values          (@WS_Job_Number , @CUSTNMBR , @vchrBillTo, 2                    )
end

if exists (select ws_job_number from jc00901 where ws_job_number = @ws_job_number ) begin
update jc00901 set
   WS_Job_Name = @WS_Job_Name
  where WS_Job_Number = @WS_Job_Number
end else begin
INSERT INTO jc00901 (WS_Job_Number , WS_Job_Name , WS_Module )
     values          (@WS_Job_Number , @WS_Job_Name , 'JC'       )
end

grant all on _4P_JC00102_INSUPD to public