Please Optimise the following

hi
Can anybody help me for optimising the following query so that i have very less no. of select statements  (Possibly 1)

The Situation is

CREATE TABLE [dbo].[DocumentOwners] (
      [DocumentOwnerID] [int] IDENTITY (1, 1) NOT NULL ,
      [DocumentID] [int] NULL ,
      [TPCompanyID] [int] NULL ,
      [ForwardUserID] [int] NULL ,
      [TPUserID] [int] NULL ,
      [AssignedDate] [datetime] NULL ,
      [Comments] [varchar] (256) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[OTNDocument] (
      [DocumentID] [int] IDENTITY (1, 1) NOT NULL ,
      [SenderID] [int] NOT NULL ,
      [DocumentType] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [DocumentDate] [datetime] NULL ,
      [TPUser] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [TPDocumentID] [int] NULL ,
      [DocumentInserted] [bit] NOT NULL ,
      [Archived] [bit] NOT NULL ,
      [DocumentOwnerID] [int] NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[OTNRecipients] (
      [OTNRecipientID] [int] IDENTITY (1, 1) NOT NULL ,
      [DocumentID] [int] NOT NULL ,
      [TPCompanyID] [int] NOT NULL ,
      [AcknowledgementDate] [datetime] NULL ,
      [AcknowledgedBy] [varchar] (96) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [AckDeliveryStatus] [bit] NULL ,
      [DeliveryDate] [datetime] NULL ,
      [ResponseDate] [datetime] NULL ,
      [RespondedBy] [varchar] (96) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [RespDeliveryStatus] [bit] NULL ,
      [DocumentProcessed] [bit] NOT NULL ,
      [Archived] [bit] NOT NULL ,
      [Quoted] [bit] NOT NULL ,
      [DocumentOwnerID] [int] NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[PDOTPLink] (
      [PDOTPLinkID] [int] IDENTITY (1, 1) NOT NULL ,
      [TPCompanyID] [int] NOT NULL ,
      [PDOVendorCode] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [Status] [int] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[PDOVendorCodeUser] (
      [VendorCodeUserID] [int] IDENTITY (1, 1) NOT NULL ,
      [PDOTPLinkID] [int] NOT NULL ,
      [TPUserID] [int] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[PDO_RFQ] (
      [DocumentID] [int] NOT NULL ,
      [CustomerRFQID] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [CollNumber] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [CustomerReferenceCode] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [CustomerCode] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [CustomerName] [varchar] (96) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [CustomerDivisionCode] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [CustomerDivisionName] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [CustomerDivisionContact] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [CustomerAddress] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [CustomerPhone] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [CustomerFax] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [CustomerEmail] [varchar] (96) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [VendorCode] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [VendorName] [varchar] (96) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [VendorContact] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [VendorAddress] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [VendorPhone] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [VendorFax] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [VendorEmail] [varchar] (96) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [RFQDate] [datetime] NULL ,
      [RFQClosingDate] [datetime] NULL ,
      [SubmissionPlace] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [RFQValidity] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [RFQType] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [AttachedDocuments] [varchar] (1024) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [QuoteInCurrency] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [DeliveryStartDate] [datetime] NULL ,
      [DeliveryEndDate] [datetime] NULL ,
      [DeliveryLocation] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [Instructions] [varchar] (1024) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [TermsAndClauses] [varchar] (2048) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [AdditionalSpecifications] [varchar] (2048) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [ResponseRequired] [bit] NOT NULL ,
      [AcknowledgementRequired] [bit] NOT NULL ,
      [MTB] [bit] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[RFQ] (
      [DocumentID] [int] NOT NULL ,
      [CustomerRFQID] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [CustomerReferenceCode] [nvarchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [CustomerCode] [nvarchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [CustomerName] [nvarchar] (96) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [CustomerDivisionCode] [nvarchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [CustomerDivisionName] [nvarchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [CustomerDivisionContact] [nvarchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [CustomerAddress] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [CustomerPhone] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [CustomerFax] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [CustomerEmail] [varchar] (96) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [VendorCode] [nvarchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [VendorName] [nvarchar] (96) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [VendorContact] [nvarchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [VendorAddress] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [VendorPhone] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [VendorFax] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [VendorEmail] [varchar] (96) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [RFQDate] [datetime] NULL ,
      [RFQClosingDate] [datetime] NULL ,
      [SubmissionPlace] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [RFQValidity] [nvarchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [RFQType] [nvarchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [AttachedDocuments] [varchar] (1024) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [QuoteInCurrency] [nvarchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [DeliveryStartDate] [datetime] NULL ,
      [DeliveryEndDate] [datetime] NULL ,
      [DeliveryLocation] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [Instructions] [nvarchar] (1024) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [TermsAndClauses] [nvarchar] (2048) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [AdditionalSpecifications] [nvarchar] (2048) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [ResponseRequired] [bit] NOT NULL ,
      [AcknowledgementRequired] [bit] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[TPCompany] (
      [TPCompanyID] [int] IDENTITY (1, 1) NOT NULL ,
      [TPID] [int] NULL ,
      [CRNo] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [OTNCompanyID] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [CompanyName] [varchar] (96) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [PartOfGroup] [bit] NOT NULL ,
      [POBox] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [City] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [PostalCode] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [Country] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [Location] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [PhoneNos] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [FaxNos] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [Email] [varchar] (96) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [Website] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [ContactPerson] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [OTNContact] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [ProfileDescription] [varchar] (1024) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [JoinedOn] [datetime] NULL ,
      [Status] [int] NULL ,
      [PEIE] [bit] NOT NULL ,
      [OPAL] [bit] NOT NULL ,
      [TermsAndConditions] [varchar] (4096) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [TPLogo] [varchar] (96) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [Hub] [bit] NOT NULL ,
      [SMSGSMNo] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [DocumentForwardRule] [int] NULL ,
      [SchemeID] [int] NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[TPHub] (
      [HubID] [int] IDENTITY (1, 1) NOT NULL ,
      [HubCode] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
      [TPCompanyID] [int] NOT NULL ,
      [Logo] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [SmallLogo] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [Icon] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [Banner] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [ResourcePath] [varchar] (96) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[TPUser] (
      [TPUserID] [int] IDENTITY (1, 1) NOT NULL ,
      [Username] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [UserPassword] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [FullName] [varchar] (96) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [Division] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [Email] [varchar] (96) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [TPCompanyID] [int] NULL ,
      [AccessLevel] [int] NULL ,
      [LastLogin] [datetime] NULL ,
      [LastLogout] [datetime] NULL ,
      [LogonCount] [int] NULL ,
      [Status] [int] NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[DocumentOwners] WITH NOCHECK ADD
      CONSTRAINT [PK_DocumentOwners] PRIMARY KEY  CLUSTERED
      (
            [DocumentOwnerID]
      )  ON [PRIMARY]
GO

ALTER TABLE [dbo].[OTNDocument] WITH NOCHECK ADD
      CONSTRAINT [PK_TPMessage] PRIMARY KEY  CLUSTERED
      (
            [DocumentID]
      )  ON [PRIMARY]
GO

ALTER TABLE [dbo].[OTNRecipients] WITH NOCHECK ADD
      CONSTRAINT [PK_OTNRecipients] PRIMARY KEY  CLUSTERED
      (
            [OTNRecipientID]
      )  ON [PRIMARY]
GO

ALTER TABLE [dbo].[PDOTPLink] WITH NOCHECK ADD
      CONSTRAINT [PK_PDOTPLink] PRIMARY KEY  CLUSTERED
      (
            [PDOTPLinkID]
      )  ON [PRIMARY]
GO

ALTER TABLE [dbo].[PDOVendorCodeUser] WITH NOCHECK ADD
      CONSTRAINT [PK_PDOVendorCodeUser] PRIMARY KEY  CLUSTERED
      (
            [VendorCodeUserID]
      )  ON [PRIMARY]
GO

ALTER TABLE [dbo].[PDO_RFQ] WITH NOCHECK ADD
      CONSTRAINT [PK_PDO_RFQ] PRIMARY KEY  CLUSTERED
      (
            [DocumentID]
      )  ON [PRIMARY]
GO

ALTER TABLE [dbo].[RFQ] WITH NOCHECK ADD
      CONSTRAINT [PK_RFQ] PRIMARY KEY  CLUSTERED
      (
            [DocumentID]
      )  ON [PRIMARY]
GO

ALTER TABLE [dbo].[TPCompany] WITH NOCHECK ADD
      CONSTRAINT [PK_TPCompany] PRIMARY KEY  CLUSTERED
      (
            [TPCompanyID]
      )  ON [PRIMARY]
GO

ALTER TABLE [dbo].[TPHub] WITH NOCHECK ADD
      CONSTRAINT [PK_TPHub] PRIMARY KEY  CLUSTERED
      (
            [HubID]
      )  ON [PRIMARY]
GO

ALTER TABLE [dbo].[TPUser] WITH NOCHECK ADD
      CONSTRAINT [aaaaaTPUser_PK] PRIMARY KEY  CLUSTERED
      (
            [TPUserID]
      )  ON [PRIMARY]
GO

ALTER TABLE [dbo].[OTNDocument] WITH NOCHECK ADD
      CONSTRAINT [DF_OTNDocument_DocumentInserted] DEFAULT (0) FOR [DocumentInserted],
      CONSTRAINT [DF_OTNDocument_Archived] DEFAULT (0) FOR [Archived]
GO

ALTER TABLE [dbo].[OTNRecipients] WITH NOCHECK ADD
      CONSTRAINT [DF_OTNRecipients_AckDeliveryStatus] DEFAULT (0) FOR [AckDeliveryStatus],
      CONSTRAINT [DF_OTNRecipients_RespDeliveryStatus] DEFAULT (0) FOR [RespDeliveryStatus],
      CONSTRAINT [DF_OTNRecipients_DocumentProcessed] DEFAULT (0) FOR [DocumentProcessed],
      CONSTRAINT [DF_OTNRecipients_Archived] DEFAULT (0) FOR [Archived],
      CONSTRAINT [DF_OTNRecipients_Quoted] DEFAULT (0) FOR [Quoted],
      CONSTRAINT [IX_OTNRecipients] UNIQUE  NONCLUSTERED
      (
            [DocumentID],
            [TPCompanyID]
      )  ON [PRIMARY]
GO

ALTER TABLE [dbo].[PDOTPLink] WITH NOCHECK ADD
      CONSTRAINT [DF_PDOTPLink_Status] DEFAULT (0) FOR [Status]
GO

ALTER TABLE [dbo].[PDO_RFQ] WITH NOCHECK ADD
      CONSTRAINT [DF_PDO_RFQ_ResponseRequired] DEFAULT (0) FOR [ResponseRequired],
      CONSTRAINT [DF_PDO_RFQ_AcknowledgementRequired] DEFAULT (0) FOR [AcknowledgementRequired],
      CONSTRAINT [DF_PDO_RFQ_MTB] DEFAULT (0) FOR [MTB]
GO

ALTER TABLE [dbo].[RFQ] WITH NOCHECK ADD
      CONSTRAINT [DF_RFQ_ResponseRequired] DEFAULT (0) FOR [ResponseRequired],
      CONSTRAINT [DF_RFQ_AcknowledgementRequired] DEFAULT (0) FOR [AcknowledgementRequired]
GO

ALTER TABLE [dbo].[TPCompany] WITH NOCHECK ADD
      CONSTRAINT [DF__TPCompany__TPID__1A14E395] DEFAULT (0) FOR [TPID],
      CONSTRAINT [DF_TPCompany_PartOfGroup] DEFAULT (0) FOR [PartOfGroup],
      CONSTRAINT [DF__TPCompany__Statu__1BFD2C07] DEFAULT (0) FOR [Status],
      CONSTRAINT [DF_TPCompany_PEIE] DEFAULT (0) FOR [PEIE],
      CONSTRAINT [DF_TPCompany_OPAL] DEFAULT (0) FOR [OPAL],
      CONSTRAINT [DF_TPCompany_Hub] DEFAULT (0) FOR [Hub],
      CONSTRAINT [DF_TPCompany_DocumentForwardRule] DEFAULT (0) FOR [DocumentForwardRule],
      CONSTRAINT [DF_TPCompany_SchemeID] DEFAULT (0) FOR [SchemeID]
GO

ALTER TABLE [dbo].[TPUser] WITH NOCHECK ADD
      CONSTRAINT [DF__TPUser__TPCompan__31EC6D26] DEFAULT (0) FOR [TPCompanyID],
      CONSTRAINT [DF__TPUser__AccessLe__32E0915F] DEFAULT (0) FOR [AccessLevel],
      CONSTRAINT [DF__TPUser__LogonCou__33D4B598] DEFAULT (0) FOR [LogonCount],
      CONSTRAINT [DF__TPUser__Status__34C8D9D1] DEFAULT (0) FOR [Status]
GO

 CREATE  INDEX [IX_DocumentOwners] ON [dbo].[DocumentOwners]([DocumentID], [TPCompanyID]) ON [PRIMARY]
GO

 CREATE  INDEX [EOS_DocumentOwners_TPCompanyID] ON [dbo].[DocumentOwners]([TPCompanyID]) WITH  FILLFACTOR = 80 ON [PRIMARY]
GO

 CREATE  INDEX [EOS_DocumentOwners_TPUserID] ON [dbo].[DocumentOwners]([TPUserID]) WITH  FILLFACTOR = 80 ON [PRIMARY]
GO

 CREATE  INDEX [IX_OTNDocument] ON [dbo].[OTNDocument]([SenderID]) ON [PRIMARY]
GO

 CREATE  INDEX [IX_OTNDocument_Temp] ON [dbo].[OTNDocument]([DocumentType]) ON [PRIMARY]
GO

/****** The index created by the following statement is for internal use only. ******/
/****** It is not a real index but exists as statistics only. ******/
if (@@microsoftversion > 0x07000000 )
EXEC ('CREATE STATISTICS [hind_261575970_3A_1A] ON [dbo].[OTNDocument] ([DocumentType], [DocumentID]) ')
GO

 CREATE  INDEX [OTNDocument12] ON [dbo].[OTNDocument]([DocumentID], [SenderID], [DocumentType], [DocumentDate]) ON [PRIMARY]
GO

 CREATE  INDEX [EOS_OTNDocument_DocDate] ON [dbo].[OTNDocument]([DocumentDate] DESC ) WITH  FILLFACTOR = 65 ON [PRIMARY]
GO

 CREATE  INDEX [EOS_OTNDocument_DocType] ON [dbo].[OTNDocument]([DocumentType]) ON [PRIMARY]
GO

 CREATE  INDEX [EOS_OTNDocument_Sender] ON [dbo].[OTNDocument]([SenderID]) WITH  FILLFACTOR = 80 ON [PRIMARY]
GO

 CREATE  INDEX [EOS_OTNDocument_DocOwner] ON [dbo].[OTNDocument]([DocumentOwnerID]) WITH  FILLFACTOR = 80 ON [PRIMARY]
GO

 CREATE  INDEX [IX_OTNRecipents_TPcompanyID] ON [dbo].[OTNRecipients]([TPCompanyID]) ON [PRIMARY]
GO

 CREATE  INDEX [IX_OTNRecipients_DocumentId] ON [dbo].[OTNRecipients]([DocumentID]) ON [PRIMARY]
GO

 CREATE  INDEX [EOS_OTNRecipients_DocOwner] ON [dbo].[OTNRecipients]([DocumentOwnerID]) WITH  FILLFACTOR = 80 ON [PRIMARY]
GO

 CREATE  UNIQUE  INDEX [IX_PDOTPLink_PDOVendorCode_UNIQUE] ON [dbo].[PDOTPLink]([PDOVendorCode]) ON [PRIMARY]
GO

 CREATE  INDEX [EOS_PDOVendorCodeUser_TPUserID] ON [dbo].[PDOVendorCodeUser]([TPUserID]) WITH  FILLFACTOR = 80 ON [PRIMARY]
GO

 CREATE  INDEX [IX_PDO_RFQ] ON [dbo].[PDO_RFQ]([CollNumber]) ON [PRIMARY]
GO

 CREATE  INDEX [EOS_PDO_RFQ_RFQCloseDate] ON [dbo].[PDO_RFQ]([RFQClosingDate] DESC ) WITH  FILLFACTOR = 65 ON [PRIMARY]
GO

 CREATE  INDEX [EOS_PDO_RFQ_VendorCode] ON [dbo].[PDO_RFQ]([VendorCode]) WITH  FILLFACTOR = 80 ON [PRIMARY]
GO

 CREATE  INDEX [EOS_PDO_RFQ_CustomerDivisionCode] ON [dbo].[PDO_RFQ]([CustomerDivisionCode]) WITH  FILLFACTOR = 90 ON [PRIMARY]
GO

 CREATE  INDEX [IX_RFQ_Temp] ON [dbo].[RFQ]([DocumentID], [CustomerRFQID], [RFQClosingDate]) ON [PRIMARY]
GO

 CREATE  INDEX [EOS_RFQ_RFQCloseDate] ON [dbo].[RFQ]([RFQClosingDate] DESC ) WITH  FILLFACTOR = 65 ON [PRIMARY]
GO

 CREATE  INDEX [TPID] ON [dbo].[TPCompany]([TPID]) ON [PRIMARY]
GO

 CREATE  INDEX [IX_OTNCompanyID] ON [dbo].[TPCompany]([OTNCompanyID]) ON [PRIMARY]
GO

 CREATE  UNIQUE  INDEX [IX_TPHub_Unique] ON [dbo].[TPHub]([HubCode]) ON [PRIMARY]
GO

 CREATE  INDEX [TPCompanyID] ON [dbo].[TPUser]([TPCompanyID]) ON [PRIMARY]
GO

 CREATE  INDEX [UserID] ON [dbo].[TPUser]([TPUserID]) ON [PRIMARY]
GO

ALTER TABLE [dbo].[DocumentOwners] ADD
      CONSTRAINT [FK_DocumentOwners_OTNDocument] FOREIGN KEY
      (
            [DocumentID]
      ) REFERENCES [dbo].[OTNDocument] (
            [DocumentID]
      ),
      CONSTRAINT [FK_DocumentOwners_TPCompany] FOREIGN KEY
      (
            [TPCompanyID]
      ) REFERENCES [dbo].[TPCompany] (
            [TPCompanyID]
      ),
      CONSTRAINT [FK_DocumentOwners_TPUser] FOREIGN KEY
      (
            [TPUserID]
      ) REFERENCES [dbo].[TPUser] (
            [TPUserID]
      )
GO

ALTER TABLE [dbo].[OTNDocument] ADD
      CONSTRAINT [FK_OTNDocument_TPCompany] FOREIGN KEY
      (
            [SenderID]
      ) REFERENCES [dbo].[TPCompany] (
            [TPCompanyID]
      )
GO

ALTER TABLE [dbo].[OTNRecipients] ADD
      CONSTRAINT [FK_OTNRecipients_OTNDocument] FOREIGN KEY
      (
            [DocumentID]
      ) REFERENCES [dbo].[OTNDocument] (
            [DocumentID]
      ),
      CONSTRAINT [FK_OTNRecipients_TPCompany] FOREIGN KEY
      (
            [TPCompanyID]
      ) REFERENCES [dbo].[TPCompany] (
            [TPCompanyID]
      )
GO

ALTER TABLE [dbo].[PDOTPLink] ADD
      CONSTRAINT [FK_PDOTPLink_TPCompany] FOREIGN KEY
      (
            [TPCompanyID]
      ) REFERENCES [dbo].[TPCompany] (
            [TPCompanyID]
      )
GO

ALTER TABLE [dbo].[PDOVendorCodeUser] ADD
      CONSTRAINT [FK_PDOVendorCodeUser_PDOTPLink] FOREIGN KEY
      (
            [PDOTPLinkID]
      ) REFERENCES [dbo].[PDOTPLink] (
            [PDOTPLinkID]
      ),
      CONSTRAINT [FK_PDOVendorCodeUser_TPUser] FOREIGN KEY
      (
            [TPUserID]
      ) REFERENCES [dbo].[TPUser] (
            [TPUserID]
      )
GO

ALTER TABLE [dbo].[PDO_RFQ] ADD
      CONSTRAINT [FK_PDO_RFQ_OTNDocument] FOREIGN KEY
      (
            [DocumentID]
      ) REFERENCES [dbo].[OTNDocument] (
            [DocumentID]
      )
GO

ALTER TABLE [dbo].[RFQ] ADD
      CONSTRAINT [FK_RFQ_OTNDocument] FOREIGN KEY
      (
            [DocumentID]
      ) REFERENCES [dbo].[OTNDocument] (
            [DocumentID]
      )
GO

ALTER TABLE [dbo].[TPCompany] ADD
      CONSTRAINT [TPCompany_FK00] FOREIGN KEY
      (
            [TPID]
      ) REFERENCES [dbo].[TPMaster] (
            [TPID]
      )
GO

ALTER TABLE [dbo].[TPHub] ADD
      CONSTRAINT [FK_TPHub_TPCompany] FOREIGN KEY
      (
            [TPCompanyID]
      ) REFERENCES [dbo].[TPCompany] (
            [TPCompanyID]
      )
GO

ALTER TABLE [dbo].[TPUser] ADD
      CONSTRAINT [FK_TPUser_TPCompany] FOREIGN KEY
      (
            [TPCompanyID]
      ) REFERENCES [dbo].[TPCompany] (
            [TPCompanyID]
      )
GO







SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

Alter  PROCEDURE GetUserDocumentCount(
@TPCompanyID integer,
@TPUserID integer
)
AS
/*
Author:
Date: 13/01/2004
The modified Version of GetUserDocumentCount_Old
*/
Begin
Declare @newRFQs int
Declare @totRFQs int
Declare @newQuotes int
Declare @totQuotes int
Declare @newPOs int
Declare @totPOs int
Declare @newSEs int
Declare @totSEs int
Declare @RFQsExpiringToday int
Declare @RFQsExpired int
Declare @RFQsResponded int
Declare @RFQsNotResponded int
Declare @RFQsSentToday int
Declare @totRFQsSent int
Declare @QuotesSentToday int
Declare @totQuotesSent int
Declare @POsSentToday int
Declare @totPOsSent int
Declare @Today datetime
Declare @Now datetime
Declare @DateToday char(10)
DECLARE @DocumentForwardRule int
DECLARE @IsAdministrator bit
-- To set the dateformat to DMY
      EXEC SetDateFormat
-- To Get the Todays date values
      SELECT @Today = convert(datetime, convert(char(10), getdate(), 103), 103) + ' 23:59:59'
      SELECT @Now = getdate()
      SELECT @DateToday = Convert(Char(10),GetDate(),103)
--Get the DocumentForward Rule for the Company  (0 - No Forwarding(Default), 1- Free Forwarding, 2 - Forced Forwarding)
      SELECT @DocumentForwardRule = DocumentForwardRule
      FROM TPCompany
      WHERE TPCompanyID = @TPCompanyID
--Check the AccessLevel of the User. For an administrator Accesslevel = 2
      SELECT @IsAdministrator = CASE AccessLevel
                              WHEN 2 THEN 1
                              ELSE 0
                             END
      FROM TPUser
      Where TPUserID = @TPUserID
-- Get the count of new RFQ

------------------------------------------------------------------------------------------------------------------------
SELECT
       @RFQsExpiringToday = IsNull(SUM(CASE WHEN RFQ.RFQClosingDate between @Now  and @Today  /*AND D.DocumentType = 'RFQ' */ THEN 1 ELSE 0 END),0)  --OK
      ,@RFQsExpired = IsNull(SUM(CASE WHEN RFQ.RFQClosingDate < @Now THEN 1 ELSE 0 END),0)
FROM RFQ
INNER JOIN OTNRecipients R ON RFQ.DocumentID = R.DocumentID
WHERE R.TPCompanyID = @TPCompanyID
      
      --Document OwnershipRules
      AND (@DocumentForwardRule = 0
            OR @IsAdministrator = 1
            OR (@DocumentForwardRule = 1 AND R.DocumentOwnerID IS NULL)
            OR @TPUserID IN (SELECT TPUserID
                        FROM DocumentOwners
                        WHERE DocumentID = R.DocumentID
                        AND TPCompanyID = @TPCompanyID))

----------------------------------------------------------------------------------------------------------------------

SELECT
       @newRFQs      = IsNull(SUM(CASE WHEN D.DocumentType = 'RFQ' AND R.DocumentProcessed = 0 THEN 1 ELSE 0 END),0) --ok
       ,@totRFQs      = IsNull(SUM(CASE WHEN D.DocumentType = 'RFQ' THEN 1 ELSE 0 END),0) --OK
      ,@newQuotes      = IsNull(SUM(CASE WHEN D.DocumentType = 'QUOTE' AND R.DocumentProcessed = 0 THEN 1 ELSE 0 END),0) --ok
       ,@totQuotes      = IsNull(SUM(CASE WHEN D.DocumentType = 'QUOTE' THEN 1 ELSE 0 END),0) -- OK
      ,@newPOs       = IsNull(SUM(CASE WHEN D.DocumentType = 'PO' AND R.DocumentProcessed = 0 THEN 1 ELSE 0 END),0) --OK
      ,@totPOs      = IsNull(SUM(CASE WHEN D.DocumentType = 'PO' THEN 1 ELSE 0 END),0)   --OK
      ,@RFQsResponded = IsNull(SUM(CASE WHEN D.DocumentType = 'RFQ'  AND R.ResponseDate is not NULL THEN 1 ELSE 0 END),0)  --OK
      ,@RFQsNotResponded = IsNull(SUM(CASE WHEN D.DocumentType = 'RFQ' AND R.ResponseDate is NULL THEN 1 ELSE 0 END),0)   --OK
FROM
OTNRecipients R INNER JOIN OTNDocument D ON R.DocumentID = D.DocumentID
WHERE R.TPCompanyID  = @TPCompanyID
            --Document OwnershipRules
AND (@DocumentForwardRule = 0
      OR @IsAdministrator = 1
      OR (@DocumentForwardRule = 1 AND R.DocumentOwnerID IS NULL)
      OR @TPUserID IN (SELECT TPUserID
                  FROM DocumentOwners
                  WHERE DocumentID = R.DocumentID
                  AND TPCompanyID = @TPCompanyID))
-----------------------------------------------------------------------------------------------------
SELECT
       @newRFQs      = IsNull(SUM(CASE WHEN  D.DocumentType = 'PDO_RFQ' AND R.DocumentProcessed = 0 THEN 1 ELSE 0 END),0)+@newRFQs  --OK
      ,@totRFQs      = IsNull(SUM(CASE WHEN  D.DocumentType = 'PDO_RFQ' THEN 1 ELSE 0 END),0)+ @totRFQs       --OK
      ,@RFQsResponded = ISNull(SUM(CASE WHEN  D.DocumentType = 'PDO_RFQ'  AND R.ResponseDate is not Null THEN 1 ELSE 0 END),0)+@RFQsResponded   --OK
      ,@RFQsNotResponded = IsNull(SUM(CASE WHEN D.DocumentType = 'PDO_RFQ'  AND R.ResponseDate is Null THEN 1 ELSE 0 END),0)+ @RFQsNotResponded  --OK

      ,@RFQsExpiringToday = IsNull(SUM(CASE WHEN RFQ.RFQClosingDate between @Now  and @Today  /*AND D.DocumentType = 'RFQ' */ THEN 1 ELSE 0 END),0) + @RFQsExpiringToday --OK
      ,@RFQsExpired          = IsNull(SUM(CASE WHEN RFQ.RFQClosingDate < @Now /* AND D.DocumentType = 'PDO_RFQ' */ THEN 1 ELSE 0 END),0)+@RFQsExpired  --OK

FROM
OTNRecipients R INNER JOIN OTNDocument D ON R.DocumentID = D.DocumentID
INNER JOIN PDO_RFQ RFQ ON R.DocumentID = RFQ.DocumentID
WHERE R.TPCompanyID = @TPCompanyID
      --VendorCode Rules
      AND  RFQ.VendorCode IN (
            SELECT DISTINCT PTL.PDOVendorcode
            FROM PDOTPLink PTL,PDOVendorCodeUser PVCU
            WHERE (PTL.Status=1 AND ((PVCU.PDOTPLinkID = PTL.PDOTPLinkID AND PVCU.TPUserID = @TPUserID) OR
            (PTL.TPCompanyID = @TPCompanyID
            AND PTL.PDOTPLinkID NOT IN (SELECT PDOTPLinkID FROM  PDOVendorCodeUser)))))
      --Document OwnershipRules
      AND (@DocumentForwardRule = 0
            OR @IsAdministrator = 1
            OR (@DocumentForwardRule = 1 AND R.DocumentOwnerID IS NULL)
            OR @TPUserID IN (SELECT TPUserID
                        FROM DocumentOwners
                        WHERE DocumentID = R.DocumentID
                        AND TPCompanyID = @TPCompanyID))      
----------------------------------------------------------------------------------------------------------------------

SELECT
       @newSEs      = IsNull(SUM(CASE WHEN  D.DocumentType = 'PDO_SE' AND R.DocumentProcessed = 0 THEN 1 ELSE 0 END),0)  --OK
      ,@totSEs      = IsNull(SUM(CASE WHEN  D.DocumentType = 'PDO_SE' THEN 1 ELSE 0 END),0) --OK
FROM OTNRecipients R
INNER JOIN OTNDocument D ON R.DocumentID = D.DocumentID
INNER JOIN PDO_ServiceEntry SE ON R.DocumentID = SE.DocumentID
WHERE R.TPCompanyID = @TPCompanyID
      --VendorCode Rules
      AND  SE.VendorCode IN (
            SELECT DISTINCT PTL.PDOVendorcode
            FROM PDOTPLink PTL,PDOVendorCodeUser PVCU
            WHERE (PTL.Status=1 AND ((PVCU.PDOTPLinkID = PTL.PDOTPLinkID AND PVCU.TPUserID = @TPUserID) OR
            (PTL.TPCompanyID = @TPCompanyID
            AND PTL.PDOTPLinkID NOT IN (SELECT PDOTPLinkID FROM  PDOVendorCodeUser)))))
      --Document OwnershipRules
      AND (@DocumentForwardRule = 0
            OR @IsAdministrator = 1
            OR (@DocumentForwardRule = 1 AND R.DocumentOwnerID IS NULL)
            OR @TPUserID IN (SELECT TPUserID
                        FROM DocumentOwners
                        WHERE DocumentID = R.DocumentID
                        AND TPCompanyID = @TPCompanyID))      


-------------------------------------------------------------------------------------------------------------------

SELECT
       @newPOs       = IsNull(SUM(CASE WHEN  D.DocumentType = 'PDO_PO' AND R.DocumentProcessed = 0 THEN 1 ELSE 0 END),0)+@newPOs   --OK
      ,@totPOs      = @totPOs+IsNull(SUM(CASE WHEN  D.DocumentType = 'PDO_PO' THEN 1 ELSE 0 END),0) --OK
FROM OTNRecipients R
INNER JOIN OTNDocument D ON R.DocumentID = D.DocumentID
INNER JOIN PDO_PO PO ON R.DocumentID = PO.DocumentID
WHERE R.TPCompanyID = @TPCompanyID
      --VendorCode Rules
      AND  PO.VendorCode IN (
            SELECT DISTINCT PTL.PDOVendorcode
            FROM PDOTPLink PTL,PDOVendorCodeUser PVCU
            WHERE (PTL.Status=1 AND ((PVCU.PDOTPLinkID = PTL.PDOTPLinkID AND PVCU.TPUserID = @TPUserID) OR
            (PTL.TPCompanyID = @TPCompanyID
            AND PTL.PDOTPLinkID NOT IN (SELECT PDOTPLinkID FROM  PDOVendorCodeUser)))))
      --Document OwnershipRules
      AND (@DocumentForwardRule = 0
            OR @IsAdministrator = 1
            OR (@DocumentForwardRule = 1 AND R.DocumentOwnerID IS NULL)
            OR @TPUserID IN (SELECT TPUserID
                        FROM DocumentOwners
                        WHERE DocumentID = R.DocumentID
                        AND TPCompanyID = @TPCompanyID))      


------------------------------------------------------------------------------------------------------------------------


-- SELECT
--        @RFQsExpiringToday = IsNull(SUM(CASE WHEN PDO_RFQ.RFQClosingDate between @Now  and @Today  /*AND D.DocumentType = 'RFQ' */ THEN 1 ELSE 0 END),0) + @RFQsExpiringToday --OK
--       ,@RFQsExpired          = IsNull(SUM(CASE WHEN PDO_RFQ.RFQClosingDate < @Now /* AND D.DocumentType = 'PDO_RFQ' */ THEN 1 ELSE 0 END),0)+@RFQsExpired  --OK
--
-- FROM PDO_RFQ
-- INNER JOIN OTNRecipients R ON PDO_RFQ.DocumentID = R.DocumentID
-- WHERE R.TPCompanyID = @TPCompanyID
--       --VendorCode Rules
--       AND  VendorCode IN (
--             SELECT DISTINCT PTL.PDOVendorcode
--             FROM PDOTPLink PTL,PDOVendorCodeUser PVCU
--             WHERE (PTL.Status=1 AND ((PVCU.PDOTPLinkID = PTL.PDOTPLinkID AND PVCU.TPUserID = @TPUserID) OR
--             (PTL.TPCompanyID = @TPCompanyID
--             AND PTL.PDOTPLinkID NOT IN (SELECT PDOTPLinkID FROM  PDOVendorCodeUser)))))
--       --Document OwnershipRules
--       AND (@DocumentForwardRule = 0
--             OR @IsAdministrator = 1
--             OR (@DocumentForwardRule = 1 AND R.DocumentOwnerID IS NULL)
--             OR @TPUserID IN (SELECT TPUserID
--                         FROM DocumentOwners
--                         WHERE DocumentID = R.DocumentID
--                         AND TPCompanyID = @TPCompanyID))
------------------------------------------------------------------------------------------------------------------------
SELECT  
       @RFQsSentToday = IsNull(SUM(CASE WHEN D.DocumentType = 'RFQ' AND Convert(Char(10),D.DocumentDate,103) = @DateToday THEN 1 ELSE 0 END),0) --OK
      ,@totRFQsSent      = IsNull(SUM(CASE WHEN D.DocumentType = 'RFQ' THEN 1 ELSE 0 END),0) --OK
      ,@QuotesSentToday=IsNull(SUM(CASE WHEN D.DocumentType   = 'QUOTE' AND Convert(Char(10),D.DocumentDate,103) = @DateToday THEN 1 ELSE 0 END),0)--OK
      ,@totQuotesSent      = IsNull(SUM(CASE WHEN D.DocumentType   = 'QUOTE' THEN 1 ELSE 0 END),0)--OK
      ,@POsSentToday      = IsNull(SUM(CASE WHEN D.DocumentType = 'PO' AND Convert(Char(10),D.DocumentDate,103) = @DateToday THEN 1 ELSE 0 END),0) --OK
      ,@totPOsSent      = IsNull(SUM(CASE WHEN D.DocumentType = 'PO' THEN 1 ELSE 0 END),0) --OK
FROM OTNDocument D
WHERE D.SenderID = @TPCompanyID
      --Document OwnershipRules
AND (@DocumentForwardRule = 0
      OR @IsAdministrator = 1
      OR (@DocumentForwardRule = 1 AND D.DocumentOwnerID IS NULL)
            OR @TPUserID IN (SELECT TPUserID
                        FROM DocumentOwners
                        WHERE DocumentID = D.DocumentID
                        AND TPCompanyID = @TPCompanyID))

-------------------------------------------------------------------------------------------------------------------------

SELECT
       @QuotesSentToday = IsNULL(SUM(CASE WHEN D.DocumentType   = 'PDO_QUOTE' AND Convert(Char(10),D.DocumentDate,103) = @DateToday THEN 1 ELSE 0 END),0)+@QuotesSentToday
      ,@totQuotesSent   = IsNull(SUM(CASE WHEN D.DocumentType   = 'PDO_QUOTE' THEN 1 ELSE 0 END),0)+@totQuotesSent

FROM OTNDocument D
INNER JOIN PDO_Quote Q ON D.DocumentID = Q.DocumentID
WHERE D.SenderID = @TPCompanyID
      
      --VendorCode Rules
      AND  Q.VendorCode IN (
            SELECT DISTINCT PTL.PDOVendorcode
            FROM PDOTPLink PTL,PDOVendorCodeUser PVCU
            WHERE (PTL.Status=1 AND ((PVCU.PDOTPLinkID = PTL.PDOTPLinkID AND PVCU.TPUserID = @TPUserID) OR
            (PTL.TPCompanyID = @TPCompanyID
            AND PTL.PDOTPLinkID NOT IN (SELECT PDOTPLinkID FROM  PDOVendorCodeUser)))))
            --Document OwnershipRules
            AND (@DocumentForwardRule = 0
                  OR @IsAdministrator = 1
                  OR (@DocumentForwardRule = 1 AND D.DocumentOwnerID IS NULL)
                  OR @TPUserID IN (SELECT TPUserID
                              FROM DocumentOwners
                              WHERE DocumentID = D.DocumentID
                              AND TPCompanyID = @TPCompanyID))
-------------------------------------------------------------------------------------------------------------------------

      SELECT @newRFQs as NewRFQs,
            @totRFQs as TotalRFQs,
            @newQuotes as NewQuotes,
            @totQuotes as TotalQuotes,
            @newPOs as NewPOs,
            @totPOs as TotalPOs,
            @newSEs as NewSEs,
            @totSEs as TotalSEs,
            @RFQsExpiringToday as RFQsExpiringToday,
            @RFQsExpired as RFQsExpired,
            @RFQsResponded as RFQsResponded,
            @RFQsNotResponded as RFQsNotResponded,
            @RFQsSentToday as RFQsSentToday,
            @totRFQsSent as TotalRFQsSent,
            @QuotesSentToday as QuotesSentToday,
            @totQuotesSent as TotalQuotesSent,
            @POsSentToday as POsSentToday,
            @totPOsSent as TotalPOsSent
End

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO


Start Free Trial