Convert ms sql query to oracle query

[avihayhatav]

I need to convert this mssql query to oracle query, please assist:

IF OBJECT_ID('tempdb..#P_Filetypes') IS NOT NULL
	DROP TABLE #P_Filetypes
SELECT Id, SpecialPermissions, DocumentAccessPermissions
INTO #P_Filetypes
FROM PERMIT_FILETYPES 
WHERE PermissionOwnerId IN( 163 )
AND ( SiteId = 1 OR SiteId = -101 )

IF OBJECT_ID('tempdb..#P_Folders') IS NOT NULL
	DROP TABLE #P_Folders
SELECT FolderId, SpecialPermissions, DocumentAccessPermissions
INTO #P_Folders
FROM RELAT_FOLDER_PERMISSIONS 
INNER JOIN PERMIT_FOLDERS  ON PermissionId = Id
WHERE PermissionOwnerId IN( 163 )
AND ( SiteId = 1 OR SiteId = -101 ) 
CREATE INDEX IX_F_ObjectId ON #P_Folders( FolderId, DocumentAccessPermissions)

SELECT *
FROM 
(
SELECT DISTINCT D.*, FI.TypeName, FO.FolderName, U.Username CreatorName, FO.ParentId FO_ParentId, FO.ParentName FO_ParentName, FO.RootId, FO.RootName
FROM BL_DOCUMENTS D 
LEFT JOIN PERMIT_DOCUMENTS PD  ON D.PermissionId = PD.Id AND PD.PermissionOwnerId IN( 163 ) AND ( PD.SiteId = 1 OR PD.SiteId = -101 ) AND ( PD.DocumentAccessPermissions IS NULL OR PD.DocumentAccessPermissions NOT BETWEEN 49152 AND 65535 )

INNER JOIN 
(
	SELECT * FROM
	(
	SELECT DISTINCT
	FT.TypeId,
	FT.PermissionId,
	FT.TypeName,
	PFT.DocumentAccessPermissions
	FROM BL_FILETYPES FT 
	INNER JOIN PERMIT_FILETYPES PFT  ON FT.PermissionId = PFT.Id
	WHERE 
	FT.PermissionId IN
	(
		SELECT Id
		FROM BL_FILETYPES 
		INNER JOIN #P_Filetypes ON PermissionId = Id
		WHERE 
		SpecialPermissions BETWEEN 4096 AND 8191
	)
	) FT
	WHERE
	FT.PermissionId NOT IN
	(
		SELECT Id
		FROM BL_FILETYPES 
		INNER JOIN #P_Filetypes ON PermissionId = Id
		WHERE 
		SpecialPermissions BETWEEN 12288 AND 16383
	)
) FI ON D.TypeId = FI.TypeId

INNER JOIN 
(
	SELECT * FROM
	(
	SELECT DISTINCT
	 F.FolderId,
	F.FolderName,
F.ParentId,
(SELECT FolderName FROM BL_FOLDERS WHERE FolderId = F.ParentId) ParentName,
F.RootId,
(SELECT FolderName FROM BL_FOLDERS WHERE FolderId = F.RootId) RootName,
	DocumentAccessPermissions
	FROM BL_FOLDERS F 
	INNER JOIN #P_Folders PF ON F.FolderId = PF.FolderId
	WHERE
	F.FolderId IN
	(
		SELECT FolderId
		FROM #P_Folders
		WHERE 
		SpecialPermissions BETWEEN 4096 AND 8191
	)
	) F
	WHERE
	F.FolderId NOT IN
	(
		SELECT FolderId
		FROM #P_Folders
		WHERE 
		SpecialPermissions BETWEEN 12288 AND 16383
	)
) FO ON D.FolderId = FO.FolderId

LEFT JOIN 
(
	SELECT FolderPermissionId, FiletypePermissionId, DocumentAccessPermissions
	FROM PERMIT_FOLDER_FILETYPE_INTERS PFF_INTERS
	WHERE PermissionOwnerId IN(163)
	AND ( SiteId = 1 OR SiteId = -101 ) 
) PFF_INTERS ON FO.PermissionId = PFF_INTERS.FolderPermissionId AND FI.PermissionId = PFF_INTERS.FiletypePermissionId
INNER JOIN USRMNG_USERS U ON D.CreatorId = U.UserId
WHERE
(
	(
		FI.DocumentAccessPermissions BETWEEN 16384 AND 32767
		OR 
		FO.DocumentAccessPermissions BETWEEN 16384 AND 32767
	)
	AND FI.TypeId NOT IN
	(
		SELECT TypeId
		FROM BL_FILETYPES 
		INNER JOIN #P_Filetypes ON PermissionId = Id
		WHERE 
		DocumentAccessPermissions BETWEEN 49152 AND 65535
	)
	AND FO.FolderId NOT IN
	(
		SELECT FolderId
		FROM #P_Folders
		WHERE 
		DocumentAccessPermissions BETWEEN 49152 AND 65535
	)
)
AND  D.IsCurrent = 1 
AND  D.IsDeleted = 0 
)
ORDER BY DocumentId DESC

[Luuk]

Try to make your SQL readable, an re-post using '[CODE] tags.

I only think that 'OBJECT_ID('tem pdb..#P_Filetyp es')' might not be valid in Oracle, and maybe the '#' as start of a tablename.