Convert MS Transact-SQL to Oracle SQL

[MarcoDieleman]

Hello you all,

I have this code that is written in MS Transact-SQL and I need to convert it to work with Oracle. Can anyone please help??? I need the code for a VBscript project.

Thanks!

Here is the code:

======

[code]USE [**********]

-- declare variables
DECLARE @Organization binary(16)
DECLARE @DeliveryRecipient binary(16)
DECLARE @InvoiceRecipient binary(16)
DECLARE @CommMethod binary(16)
DECLARE @MinDate decimal(17,0)
DECLARE @MaxDate decimal(17,0)


-- set the organization guid, please insert the correct code number
SET @Organization = (SELECT [GUID] FROM [ORGANIZATION002] WHERE

 = '9999999')
 
 
-- set the guid for the partner relation type DeliveryRecipient and Invoicerecipient for further use
SET @DeliveryRecipient = (SELECT [GUID] FROM [PARTNERRELATIONTYPE001] WHERE [NAME] = 'Deliveryrecipient')
SET @InvoiceRecipient = (SELECT [GUID] FROM [PARTNERRELATIONTYPE001] WHERE [NAME] = 'Invoicerecipient')
-- set email as communication method
SET @CommMethod = (SELECT [GUID] FROM [COMMUNICATIONMETHOD] WHERE [NAME] = '300')
-- set minimun and maximum date
SET @MinDate = 11231231000000
SET @MaxDate = 47121230000000000
 
 
SELECT
P.[NUMBER001] CustomerNumber,
P.[NAME] CompanyName,
P.[UPDATEINFOXDELETETIME] DeleteTime,
P.[VALIDFROM] validFrom,
P.[VALIDUNTIL] validUntil,
-- customer address
PA.[STREET] Street,
PA.[POSTALCODE] ZipCode,
PA.[CITY] City,
PAC.[DESCRIPTION] Country,
CD.[COMMDATA] EMail,
-- address of the delivery recipient
DRP.[NUMBER001] DeliveryRecipient,
DRPA.[STREET] Street,
DRPA.[POSTALCODE] ZipCode,
DRPA.[CITY] City,
DRPAC.[DESCRIPTION] Country,
DRCD.[COMMDATA] EMail,
-- address of the invoice recipient
IRP.[NUMBER001] InvoiceRecipient,
IRPA.[STREET] Street,
IRPA.[POSTALCODE] ZipCode,
IRPA.[CITY] City,
IRPAC.[DESCRIPTION] Country,
IRCD.[COMMDATA] EMail,
-- tax number 
PTI.[TAXIDENTIFICATIONNUMBER] taxNumber,
PTI.[LOCALTAXIDENTIFICATIONNUMBER] LocalTaxNumber,
-- =============================================
-- web shop access control
--
C.[FALUWEBSHOPUSER] isUser,
C.[FALUWEBSHOPUSERNAME] Username,
C.[FALUWEBSHOPUSERNAME] Password
-- =============================================
FROM [PARTNER001] P
-- just select partners who are customers for the specified organization
JOIN [ORGANIZATIONALUNITPARTNER] OUP 
    ON  OUP.[GUID] = P.[GUID] 
    AND OUP.[ORGANIZATIONALUNIT] = @Organization
JOIN [CUSTOMER002] C 
    ON  C.[GUID] = P.[GUID] 
    AND C.[ORGANIZATIONALUNIT] = @Organization
LEFT OUTER JOIN [ADDRESSDATA] PA 
    ON  PA.[GUID] = P.[ADDRESSDATA]
LEFT OUTER JOIN [COUNTRY001] PAC 
    ON  PAC.[GUID] = PA.[COUNTRY]
LEFT OUTER JOIN [COMMUNICATIONDATA] CD
    ON  CD.[PARTNER] = P.[GUID]
    AND CD.[METHOD] = @CommMethod
LEFT OUTER JOIN [PARTNERACCOUNTINGDATA] PAD
    ON  PAD.[GUID] = P.[GUID] 
    AND PAD.[ORGANIZATIONALUNIT] = @Organization
    AND PAD.[VALIDUNTIL] > @MaxDate
    AND PAD.[UPDATEINFOXDELETETIME] < @MinDate
LEFT OUTER JOIN [PARTNERTAXIDENTIFICATION] PTI
    ON  PTI.[GUID] = PAD.[GUID]
    AND PTI.[VALIDUNTIL] > @MaxDate
    AND PTI.[UPDATEINFOXDELETETIME] < @MinDate
--
-- resolving delivery recipient
LEFT OUTER JOIN [PARTNERRELATION] DR 
    ON  DR.[TYPE001] = @DeliveryRecipient 
    AND DR.[SOURCE] = P.[GUID] 
    AND DR.[SOURCEORGANIZATIONALUNIT] = @Organization 
    AND DR.[VALIDUNTIL] > @MaxDate
LEFT OUTER JOIN [PARTNER001] DRP 
    ON  DRP.[GUID] = DR.[TARGET]
    AND DRP.[VALIDUNTIL] > @MaxDate
LEFT OUTER JOIN [ADDRESSDATA] DRPA 
    ON  DRPA.[GUID] = DRP.[ADDRESSDATA]
LEFT OUTER JOIN [COUNTRY001] DRPAC 
    ON  DRPAC.[GUID] = DRPA.[COUNTRY]
LEFT OUTER JOIN [COMMUNICATIONDATA] DRCD
    ON  DRCD.[PARTNER] = DRP.[GUID]
    AND DRCD.[METHOD] = @CommMethod
--
-- resolving invoice recipient
LEFT OUTER JOIN [PARTNERRELATION] IR 
    ON  IR.[TYPE001] = @InvoiceRecipient 
    AND IR.[SOURCE] = P.[GUID] 
    AND IR.[SOURCEORGANIZATIONALUNIT] = @Organization
    AND IR.[VALIDUNTIL] > @MaxDate
LEFT OUTER JOIN [PARTNER001] IRP 
    ON  IRP.[GUID] = IR.[TARGET]
    AND IRP.[VALIDUNTIL] > @MaxDate
LEFT OUTER JOIN [ADDRESSDATA] IRPA 
    ON  IRPA.[GUID] = IRP.[ADDRESSDATA]
LEFT OUTER JOIN [COUNTRY001] IRPAC 
    ON  IRPAC.[GUID] = IRPA.[COUNTRY]
LEFT OUTER JOIN [COMMUNICATIONDATA] IRCD
    ON  IRCD.[PARTNER] = IRP.[GUID]
    AND IRCD.[METHOD] = @CommMethod
--
WHERE 
    P.[VALIDUNTIL] > @MaxDate 
AND P.[UPDATEINFOXDELETETIME] < @MinDate
AND OUP.[CUSTOMER] = '1'
ORDER BY P.[NUMBER001]

======