SQL & PL/SQL

I want to conver the below script into PL/SQL

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[AuditLogAND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[AuditLog](
[GroupID] [int] NOT NULL,
[ID] [int] NOT NULL,
[TableName] [varchar](25) NOT NULL,
[FieldName] [varchar](25) NOT NULL,
[FieldType] [varchar](25) NULL,
[OldValue] [text] NOT NULL,
[NewValue] [text] NOT NULL,
[ChangedOn] [timestamp] NOT NULL,
[ChangedBy] [int] NOT NULL,
CONSTRAINT [PK_AuditLog_ID] UNIQUE NONCLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[UserRightsAND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[UserRights](
[UserID] [int] NULL,
[MenuName] [varchar](25) NULL,
[IsEnabled] [int] NULL,
CONSTRAINT [PK_UserRights_UserID] UNIQUE NONCLUSTERED
(
[UserID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GradeMasterAND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[GradeMaster](
[ID] [int] NULL,
[Name] [varchar](25) NULL,
[Description] [varchar](25) NULL,
[From] [float] NULL,
[To] [float] NULL,
CONSTRAINT [PK_GradeMaster_ID] UNIQUE NONCLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[CostGroupAND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[CostGroup](
[ID] [int] NOT NULL,
[Name] [varchar](25) NOT NULL,
CONSTRAINT [PK_CostGroup_ID] UNIQUE NONCLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ToIndexAND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
BEGIN
execute dbo.sp_executesql @statement = N'
/*
** Function dbo.ToIndex
*/
CREATE FUNCTION [dbo].[ToIndex](@string_to_index VARCHAR(1024))
RETURNS VARCHAR(1024)
AS
BEGIN
DECLARE @temp VARCHAR(1024)
DECLARE @ivar VARCHAR(1024)
DECLARE @tmps VARCHAR(1)
SET @temp = ''''
SET @ivar = UPPER(@string_to_index)
DECLARE @i INTEGER
SET @i = 1
WHILE (@i <= LEN(@string_to_index))
BEGIN
SET @tmps = SUBSTRING(@ivar, @i, 1)
IF (ASCII(@tmps) >= 48 AND ASCII(@tmps) <= 57) OR (ASCII(@tmps) >= 65 AND ASCII(@tmps) <= 90)
BEGIN
SET @temp = @temp + @tmps
END
SET @i = @i + 1
END
RETURN @temp
END
'
END

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[MasterIDAND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[MasterID](
[TableID] [int] NOT NULL,
[TableName] [varchar](25) NOT NULL,
[NextID] [int] NULL DEFAULT ((1)),
CONSTRAINT [PK_MasterID_TableID] UNIQUE NONCLUSTERED
(
[TableID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[UserListAND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[UserList](
[ID] [int] NOT NULL,
[Name] [varchar](25) NOT NULL,
[Password] [varchar](25) NOT NULL,
[FullName] [varchar](25) NULL,
CONSTRAINT [PK_UserList_ID] UNIQUE NONCLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ProductAND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Product](
[GroupID] [int] NOT NULL,
[ID] [int] NOT NULL,
[Name] [varchar](50) NOT NULL,
[GrossMargin] [float] NULL DEFAULT ((0)),
[RMC] [float] NULL DEFAULT ((0)),
[Packing] [float] NULL DEFAULT ((0)),
[Overhead] [float] NULL DEFAULT ((0)),
[Others] [float] NULL DEFAULT ((0)),
[TotalCost] [float] NULL DEFAULT ((0)),
[DPRate] [float] NULL DEFAULT ((0)),
[CreatedOn] [datetime] NULL DEFAULT (getdate()),
[CreatedBy] [int] NULL DEFAULT ((1)),
[UpdatedOn] [datetime] NULL DEFAULT (getdate()),
[UpdatedBy] [int] NULL DEFAULT ((1)),
CONSTRAINT [PK_Product_GroupID_ID] UNIQUE NONCLUSTERED
(
[GroupID] ASC,
[ID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[RawMaterialAND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[RawMaterial](
[GroupID] [int] NOT NULL,
[ID] [int] NOT NULL,
[Name] [varchar](50) NOT NULL,
[CurPrice] [float] NULL,
[OldPrice] [float] NULL,
[CreatedOn] [datetime] NULL,
[CreatedBy] [int] NULL,
[UpdatedOn] [datetime] NULL,
[UpdatedBy] [int] NULL,
CONSTRAINT [PK_RawMaterial_GroupID_ID] UNIQUE NONCLUSTERED
(
[GroupID] ASC,
[ID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[RecipeAND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Recipe](
[GroupID] [int] NOT NULL,
[ID] [int] NOT NULL,
[WEFDate] [datetime] NOT NULL,
[ProductID] [int] NOT NULL,
[RawMaterialID] [int] NOT NULL,
[Seq] [int] NOT NULL,
[Quantity] [float] NOT NULL,
[RMCost] [float] NOT NULL,
[CreatedOn] [datetime] NULL,
[CreatedBy] [int] NULL,
[UpdatedOn] [datetime] NULL,
[UpdatedBy] [int] NULL,
CONSTRAINT [PK_Recipe_GroupID_Seq_ProductID_RawMaterialID] UNIQUE NONCLUSTERED
(
[GroupID] ASC,
[Seq] ASC,
[ProductID] ASC,
[RawMaterialID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[spCalcGMAND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'-- =============================================
-- Author: Mohammed Haris
-- Create date: 24-12-2006
-- Description: RMC Calculator
-- =============================================
CREATE PROCEDURE [dbo].[spCalcGM]
(
-- Add the parameters for the function here
@GroupID int,
@ProductID int,
@RM_ID int,
@Price int = 0
)
AS
BEGIN
--SET NOCOUNT ON

-- Declare the return variable here
DECLARE @Result float
DECLARE @SumCurPrice0 float
DECLARE @SumCurPrice float
DECLARE @SumOldPrice float
DECLARE @SumQuantity float
DECLARE @SumRMC float
DECLARE @PTotalCost float
DECLARE @PDPRate float

-- Add the T-SQL statements to compute the return value here
SELECT @SumCurPrice0 = SUM(Recipe.Quantity * RawMaterial.CurPrice)
FROM RawMaterial INNER JOIN
Recipe ON RawMaterial.ID = Recipe.RawMaterialID
WHERE (Recipe.ProductID = @ProductID)
AND (Recipe.RawMaterialID <> @RM_ID)
AND (Recipe.GroupID = @GroupID)

/*IF ISNULL(@SumCurPrice0)*/
SET @SumCurPrice = ISNULL(@SumCurPrice0, 0)
/*ELSE
SET @SumCurPrice = @SumCurPrice0*/

IF @Price = 0
SELECT @SumOldPrice = SUM(Recipe.Quantity * RawMaterial.CurPrice)
FROM RawMaterial INNER JOIN
Recipe ON RawMaterial.ID = Recipe.RawMaterialID
WHERE (Recipe.ProductID = @ProductID)
AND (Recipe.RawMaterialID = @RM_ID)
AND (Recipe.GroupID = @GroupID)
ELSE
SELECT @SumOldPrice = SUM(Recipe.Quantity * RawMaterial.OldPrice)
FROM RawMaterial INNER JOIN
Recipe ON RawMaterial.ID = Recipe.RawMaterialID
WHERE (Recipe.ProductID = @ProductID)
AND (Recipe.RawMaterialID = @RM_ID)
AND (Recipe.GroupID = @GroupID)

SELECT @SumQuantity = SUM(Recipe.Quantity)
FROM Recipe
WHERE (Recipe.ProductID = @ProductID)
AND (Recipe.GroupID = @GroupID)

SELECT @SumRMC = (@SumCurPrice + @SumOldPrice) / @SumQuantity

SELECT @PTotalCost = (Product.Packing + Product.Overhead + Product.Others),
@PDPRate = Product.DPRate
FROM Product
WHERE (Product.ID = @ProductID) AND (Product.GroupID = @GroupID)

SELECT @PTotalCost = @PTotalCost + @SumRMC

SELECT @Result = (@PDPRate - @PTotalCost) / @PDPRate

SELECT

@SumCurPrice0 AS SumCurPrice0,
@SumCurPrice AS SumCurPrice,
@SumOldPrice AS SumOldPrice,
@SumQuantity AS SumQuantity,
@SumRMC AS SumRMC

-- Return the result of the function
RETURN @Result
END
'
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_UpdateTotalCostAndGMForAllProductsAND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE
[dbo].[sp_UpdateTotalCostAndGMForAllProducts]
AS
BEGIN
UPDATE Product
SET Product.TotalCost = Product.RMC + Product.Packing + Product.Overhead + Product.Others
UPDATE Product
SET Product.GrossMargin = (Product.DPRate - Product.TotalCost) / Product.DPRate
END
'
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_UpdateTotalCostAndGMForProductAND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE
[dbo].[sp_UpdateTotalCostAndGMForProduct] (@ProductID int)
AS
BEGIN
UPDATE Product
SET Product.TotalCost = Product.RMC + Product.Packing + Product.Overhead + Product.Others
WHERE Product.ID = @ProductID
UPDATE Product
SET Product.GrossMargin = (Product.DPRate - Product.TotalCost) / Product.TotalCost
WHERE Product.ID = @ProductID
END
'
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[CalcGMAND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
BEGIN
execute dbo.sp_executesql @statement = N'-- =============================================
-- Author: Mohammed Haris
-- Create date: 24-12-2006
-- Description: RMC Calculator
-- =============================================
CREATE FUNCTION [dbo].[CalcGM]
(
-- Add the parameters for the function here
@GroupID int,
@ProductID int,
@RM_ID int,
@Price int = 0
)
RETURNS float
AS
BEGIN
--SET NOCOUNT ON

-- Declare the return variable here
DECLARE @Result float
DECLARE @SumCurPrice float
DECLARE @SumOldPrice float
DECLARE @SumQuantity float
DECLARE @SumRMC float
DECLARE @PTotalCost float
DECLARE @PDPRate float

-- Add the T-SQL statements to compute the return value here
SELECT @SumCurPrice = SUM(Recipe.Quantity * RawMaterial.CurPrice)
FROM RawMaterial INNER JOIN
Recipe ON RawMaterial.ID = Recipe.RawMaterialID
WHERE (Recipe.ProductID = @ProductID)
AND (Recipe.RawMaterialID <> @RM_ID)
AND (Recipe.GroupID = @GroupID)

SET @SumCurPrice = ISNULL(@SumCurPrice, 0)

IF @Price = 0
SELECT @SumOldPrice = SUM(Recipe.Quantity * RawMaterial.CurPrice)
FROM RawMaterial INNER JOIN
Recipe ON RawMaterial.ID = Recipe.RawMaterialID
WHERE (Recipe.ProductID = @ProductID)
AND (Recipe.RawMaterialID = @RM_ID)
AND (Recipe.GroupID = @GroupID)
ELSE
SELECT @SumOldPrice = SUM(Recipe.Quantity * RawMaterial.OldPrice)
FROM RawMaterial INNER JOIN
Recipe ON RawMaterial.ID = Recipe.RawMaterialID
WHERE (Recipe.ProductID = @ProductID)
AND (Recipe.RawMaterialID = @RM_ID)
AND (Recipe.GroupID = @GroupID)

SELECT @SumQuantity = SUM(Recipe.Quantity)
FROM Recipe
WHERE (Recipe.ProductID = @ProductID)
AND (Recipe.GroupID = @GroupID)

SELECT @SumRMC = (@SumCurPrice + @SumOldPrice) / @SumQuantity

SELECT @PTotalCost = (Product.Packing + Product.Overhead + Product.Others),
@PDPRate = Product.DPRate
FROM Product
WHERE (Product.ID = @ProductID) AND (Product.GroupID = @GroupID)

SELECT @PTotalCost = @PTotalCost + @SumRMC

SELECT @Result = (@PDPRate - @PTotalCost) / @PDPRate

-- Return the result of the function
RETURN @Result
END



'
END

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_RMCostImplicationAND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[sp_RMCostImplication]
@GroupID int = 1,
@RM_ID int = 1
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
SELECT Product.Name, Product.DPRate, RawMaterial.Name AS RMName,
RawMaterial.CurPrice,
dbo.CalcGM(@GroupID, Product.ID, @RM_ID, 0)*100 AS CurGM,
RawMaterial.OldPrice,
dbo.CalcGM(@GroupID, Product.ID, @RM_ID, 1)*100 AS OldGM
FROM Product INNER JOIN
Recipe ON Product.ID = Recipe.ProductID INNER JOIN
RawMaterial ON Recipe.RawMaterialID = RawMaterial.ID
WHERE (Product.GroupID = @GroupID) AND (RawMaterial.ID = @RM_ID)
ORDER BY Product.Name
END
'
END
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Product_GroupIDAND parent_object_id = OBJECT_ID(N'[dbo].[Product]'))
ALTER TABLE [dbo].[Product] WITH CHECK ADD CONSTRAINT [FK_Product_GroupID] FOREIGN KEY([GroupID])
REFERENCES [dbo].[CostGroup] ([ID])
GO
ALTER TABLE [dbo].[Product] CHECK CONSTRAINT [FK_Product_GroupID]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_RawMaterial_GroupIDAND parent_object_id = OBJECT_ID(N'[dbo].[RawMaterial]'))
ALTER TABLE [dbo].[RawMaterial] WITH CHECK ADD CONSTRAINT [FK_RawMaterial_GroupID] FOREIGN KEY([GroupID])
REFERENCES [dbo].[CostGroup] ([ID])
GO
ALTER TABLE [dbo].[RawMaterial] CHECK CONSTRAINT [FK_RawMaterial_GroupID]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Recipe_GroupID_ProductIDAND parent_object_id = OBJECT_ID(N'[dbo].[Recipe]'))
ALTER TABLE [dbo].[Recipe] WITH CHECK ADD CONSTRAINT [FK_Recipe_GroupID_ProductID] FOREIGN KEY([GroupID], [ProductID])
REFERENCES [dbo].[Product] ([GroupID], [ID])
GO
ALTER TABLE [dbo].[Recipe] CHECK CONSTRAINT [FK_Recipe_GroupID_ProductID]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Recipe_GroupID_RawMaterialIDAND parent_object_id = OBJECT_ID(N'[dbo].[Recipe]'))
ALTER TABLE [dbo].[Recipe] WITH CHECK ADD CONSTRAINT [FK_Recipe_GroupID_RawMaterialID] FOREIGN KEY([GroupID], [RawMaterialID])
REFERENCES [dbo].[RawMaterial] ([GroupID], [ID])
GO
ALTER TABLE [dbo].[Recipe] CHECK CONSTRAINT [FK_Recipe_GroupID_RawMaterialID]

Edited by: yogeshyl on Jul 28, 2009 4:50 PM