INSERT INTO [dbo].[IPCAEDetail]
	   ([IPCode], [TypeNo], [CAENumber], [IPNameNumber], [LastModifiedDate],
		[NameIP], [FirstName], [CAEName], [Status],
		[DummyCAENumber]) --SRX08649
	VALUES
	   (@IPCode, @TypeNo, @CAENumber, @IPNameNumber, GETDATE(),
	   @NameIP, @FirstName, @CAEName, 1, @DummyCAENumber) 

INSERT INTO [Work.WorkContributor]
		(
			WorkCode
			,SetTypeNo
			,TypeNo
			,PerformingShare
			,MechanicalShare
			,LastModifiedDate
			,Status
			,USALicence
			,IPCAEDetailID
			,IntrayPK
			,SourceTypeNo
			,WriterPublisherShare
			,TerritoryCode
			,RegistrationDate
			,SetTypeGroupID
		)
		output inserted.IntrayPK, inserted.WorkContributorID
			into @TempContributor (IntrayID, WorkID)
		SELECT 			
			@WorkCode as WorkCode
			,wc.[SetTypeNo] as SetTypeNo
			,wct.[TypeNo]  as TypeNo
			,wc.[PerformingShare]
			,wc.[MechanicalShare]
			,wc.[LastModifiedDate]
			,wc.[Status]
			,wc.[USALicence]
			,wc.IPCAEDetailID as IPCAEDetailID
			,WorkContributorID as IntrayPK		
			,wc.SourceTypeNo
			,wc.WriterPublisherShare
			,wc.TerritoryCode
			,wc.RegistrationDate
			,wc.SetTypeGroupID
		FROM @WorkContributorsToProceed wc
		inner join WorkContributorTypes wct on
		wc.TypeCode = wct.TypeCode
		order by wc.WorkContributorID 

CREATE TABLE [dbo].[Work.WorkContributor](
	[WorkContributorID] [int] IDENTITY(1,1) NOT NULL,
	[WorkCode] [int] NOT NULL,
	[SetTypeNo] [int] NOT NULL,
	[TypeNo] [int] NOT NULL,
	[PerformingShare] [decimal](9, 4) NOT NULL,
	[MechanicalShare] [decimal](9, 4) NOT NULL,
	[LastModifiedDate] [datetime] NOT NULL,
	[Status] [int] NOT NULL,
	[USALicence] [int] NULL,
	[IPCAEDetailID] [int] NOT NULL,
	[IntrayPK] [int] NULL,
	[Concurrency] [timestamp] NOT NULL,
	[SourceTypeNo] [int] NULL,
	[WriterPublisherShare] [decimal](9, 4) NULL,
	[SenderRep] [int] NULL,
	[SetTypeGroupID] [int] NOT NULL,
	[TerritoryCode] [int] NULL,
	[RegistrationDate] [datetime] NULL,
 CONSTRAINT [Work.PK_WorkContributor] PRIMARY KEY CLUSTERED 
(
	[WorkContributorID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[Work.WorkContributor] ADD  DEFAULT ((1)) FOR [SetTypeGroupID]
GO

ALTER TABLE [dbo].[Work.WorkContributor]  WITH NOCHECK ADD  CONSTRAINT [FK_Work.WorkContributor_IPCAEDetail] FOREIGN KEY([IPCAEDetailID])
REFERENCES [dbo].[IPCAEDetail] ([IPCAEDetailID])
GO

ALTER TABLE [dbo].[Work.WorkContributor] NOCHECK CONSTRAINT [FK_Work.WorkContributor_IPCAEDetail]
GO

ALTER TABLE [dbo].[Work.WorkContributor]  WITH CHECK ADD  CONSTRAINT [Work.Work_Work.WorkContributor_FK1] FOREIGN KEY([WorkCode])
REFERENCES [dbo].[Work.Work] ([WorkCode])
GO

ALTER TABLE [dbo].[Work.WorkContributor] CHECK CONSTRAINT [Work.Work_Work.WorkContributor_FK1]
GO

CREATE NONCLUSTERED INDEX [idxIPCAEDetailID_Index] ON [dbo].[Work.WorkContributor]
(
	[IPCAEDetailID] ASC
)
INCLUDE ( 	[WorkCode]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [IndexesStorage]

CREATE VIEW [dbo].[WS.IntrayCodeToIPCode]
WITH SCHEMABINDING
AS
SELECT wc.WorkCode, ipcd.IPCode, COUNT_BIG(*) AS TotalNumber
FROM dbo.[Intray.WorkContributor] wc
INNER JOIN dbo.IPCAEDetail ipcd
    ON ipcd.IPCAEDetailID = wc.IPCAEDetailID
    AND ipcd.Status <> 0
WHERE wc.Status<>0
GROUP BY wc.WorkCode, ipcd.IPCode

CREATE TABLE [dbo].[Intray.WorkContributor](
	[WorkContributorID] [int] IDENTITY(1,1) NOT NULL,
	[WorkCode] [int] NOT NULL,
	[SetTypeNo] [int] NOT NULL,
	[TypeCode] [varchar](10) NOT NULL,
	[PerformingShare] [decimal](9, 2) NOT NULL,
	[MechanicalShare] [decimal](9, 2) NOT NULL,
	[Status] [int] NOT NULL,
	[USALicence] [int] NULL,
	[FirstName] [nvarchar](135) NULL,
	[NameIP] [nvarchar](135) NULL,
	[CAEName] [nvarchar](170) NOT NULL,
	[LastModifiedDate] [datetime] NOT NULL,
	[CAENumber] [varchar](9) NULL,
	[OrigIPCode] [int] NULL,
	[Concurrency] [timestamp] NOT NULL,
	[IPCAEDetailID] [int] NULL,
	[OnlineContributorInfo] [xml] NULL,
	[SourceTypeNo] [int] NULL,
	[WriterPublisherShare] [decimal](9, 4) NULL,
	[InterestedPartyNo] [varchar](9) NULL,
	[SenderRep] [int] NULL,
	[TerritoryCode] [int] NULL,
	[RegistrationDate] [datetime] NULL,
	[SetTypeGroupID] [int] NOT NULL,
 CONSTRAINT [Intray.PK_WorkContributor] PRIMARY KEY CLUSTERED 
(
	[WorkContributorID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[Intray.WorkContributor] ADD  DEFAULT ((1)) FOR [SetTypeGroupID]
GO

ALTER TABLE [dbo].[Intray.WorkContributor]  WITH NOCHECK ADD  CONSTRAINT [FK_Intray.WorkContributor_IPCAEDetail] FOREIGN KEY([IPCAEDetailID])
REFERENCES [dbo].[IPCAEDetail] ([IPCAEDetailID])
GO

ALTER TABLE [dbo].[Intray.WorkContributor] NOCHECK CONSTRAINT [FK_Intray.WorkContributor_IPCAEDetail]
GO

ALTER TABLE [dbo].[Intray.WorkContributor]  WITH CHECK ADD  CONSTRAINT [Intray.Work_WorkContributor_FK] FOREIGN KEY([WorkCode])
REFERENCES [dbo].[Intray.Work] ([WorkCode])
GO

ALTER TABLE [dbo].[Intray.WorkContributor] CHECK CONSTRAINT [Intray.Work_WorkContributor_FK]
GO

CREATE UNIQUE CLUSTERED INDEX [PK_WorkCodeIPCode] ON [dbo].[WS.IntrayCodeToIPCode]
(
	[WorkCode] ASC,
	[IPCode] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

CREATE UNIQUE CLUSTERED INDEX [PK_WorkCodeIPCode] ON [dbo].[WS.IntrayCodeToIPCode]
(
	[WorkCode] ASC,
	[IPCode] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]