Create procedure [dbo].[PMP_CML_AddAttendeeInformationAndAddress]
@PolarisCustomerID AS VARCHAR(50)='',
@Customer_Id AS VARCHAR(50)='',
@FullName AS VARCHAR(100)='',
@Customer_Type AS VARCHAR(50)='',
@NPINumber AS INT=0,
@PolarisAddressID AS VARCHAR(50)='',
@Address_ID AS VARCHAR(100)='',
@AddressLine1 AS VARCHAR(100)='',
@City AS VARCHAR(50)='',
@State AS VARCHAR(50)='',
@PostalCode AS NVARCHAR(50)='',
@createDate AS datetime,
@SourceSystem AS VARCHAR(100),
@Country AS varchar(50)
AS
BEGIN TRAN
--------RecipientType_id,State_Id,Country
DECLARE
@RecipientType_id INT,
@State_Id INT,
@COUNT INT,
@intErrorCode INT,
@AttendeeInfoAttendee_id INT
select @RecipientType_id = RecipientType_id from PMP_RecipientType where RecipientType_Name = @Customer_Type
select @State_Id = State_Id from PMP_StateMaster where State_initials = @State
IF(EXISTS(SELECT A.*,AI.PolarisCustomerID,AI.CustomerID,AI.Fullname_organizationName,AI.RecipientType_id,AI.NPINumber
FROM PMP_AttendeeInformation AS AI INNER JOIN
PMP_Address AS A ON AI.Attendee_Id = A.Attendee_Id
WHERE --AI.PolarisCustomerID = @PolarisCustomerID
AI.CustomerID = @Customer_Id
AND AI.Fullname_organizationName = @FullName
AND AI.RecipientType_id = @RecipientType_id
--AND AI.NPINumber = @NPINumber
--AND A.PolarisAddressID = @PolarisAddressID
--AND A.Attendee_Id = @Customer_Id
AND A.Location_id = @Address_ID
AND A.AddressLine1 = @AddressLine1
AND A.City = @City
AND A.State_Id = @State_Id
AND A.PostalCode = @PostalCode
AND Convert(VARCHAR(10),AI.CreatedDate,121) = Convert(VARCHAR(10),@createDate,121)
AND Convert(VARCHAR(10),A.CreateDate,121) = Convert(VARCHAR(10),@createDate,121)
))
BEGIN
-------No Changes in Existing records of Core Tables
SET @COUNT =1
END
ELSE IF(NOT EXISTS(SELECT A.*,AI.PolarisCustomerID,AI.CustomerID,AI.Fullname_organizationName,AI.RecipientType_id,AI.NPINumber
FROM PMP_AttendeeInformation AS AI INNER JOIN
PMP_Address AS A ON AI.Attendee_Id = A.Attendee_Id
where AI.CustomerID = @Customer_Id
--AND AI.Fullname_organizationName = @FullName
--AND AI.RecipientType_id = @RecipientType_id
--AND AI.NPINumber = @NPINumber
--AND A.PolarisAddressID = @PolarisAddressID
--AND A.Attendee_Id = @Customer_Id
AND A.Location_id = @Address_ID
--AND A.AddressLine1 = @AddressLine1
--AND A.City = @City
--AND A.State_Id = @State_Id
--AND A.PostalCode = @PostalCode
AND Convert(VARCHAR(10),AI.CreatedDate,121) = Convert(VARCHAR(10),@createDate,121)
AND Convert(VARCHAR(10),A.CreateDate,121) = Convert(VARCHAR(10),@createDate,121)
))
BEGIN
INSERT INTO PMP_AttendeeInformation(PolarisCustomerID,CustomerID,Fullname_organizationName,RecipientType_id,NPINumber,Isorg,CreatedDate,Creator)
VALUES(@PolarisCustomerID,@Customer_Id,@FullName,@RecipientType_id,@NPINumber,1,@createDate,@SourceSystem)
select @AttendeeInfoAttendee_id = max(Attendee_Id) from PMP_AttendeeInformation --where CustomerID = @Customer_Id and Fullname_organizationName = @FullName
INSERT INTO PMP_Address(PolarisAddressID,Attendee_Id,Location_id,AddressLine1,City,State_Id,PostalCode,Country,CreateDate,Creator)
VALUES(@PolarisAddressID,@AttendeeInfoAttendee_id,@Address_ID,@AddressLine1,@City,@State_Id,@PostalCode,@Country,@createDate,@SourceSystem)
END
ELSE
BEGIN
------------------------------ Update Record ---------------------------------------
select @AttendeeInfoAttendee_id = max(Attendee_Id) from PMP_AttendeeInformation
UPDATE PMP_AttendeeInformation SET
PolarisCustomerID = @PolarisCustomerID
,Fullname_organizationName = @FullName
,RecipientType_id = @RecipientType_id
,NPINumber = @NPINumber
,Creator = @SourceSystem
WHERE Attendee_Id = @AttendeeInfoAttendee_id
UPDATE PMP_Address SET
PolarisAddressID = @PolarisAddressID
,Location_id = @Address_ID
,AddressLine1 = @AddressLine1
,City = @City
,State_Id = @State_Id
,PostalCode = @PostalCode
,Country = @Country
,Creator = @SourceSystem
WHERE Attendee_Id = @AttendeeInfoAttendee_id
END
SELECT @intErrorCode = @@ERROR
IF (@intErrorCode <> 0) GOTO PROBLEM
COMMIT TRAN
----------------------------------------
PROBLEM:
IF (@intErrorCode <> 0)
BEGIN
---PRINT 'Unexpected error occurred!'
ROLLBACK TRAN
END
@PolarisCustomerID AS VARCHAR(50)='',
@Customer_Id AS VARCHAR(50)='',
@FullName AS VARCHAR(100)='',
@Customer_Type AS VARCHAR(50)='',
@NPINumber AS INT=0,
@PolarisAddressID AS VARCHAR(50)='',
@Address_ID AS VARCHAR(100)='',
@AddressLine1 AS VARCHAR(100)='',
@City AS VARCHAR(50)='',
@State AS VARCHAR(50)='',
@PostalCode AS NVARCHAR(50)='',
@createDate AS datetime,
@SourceSystem AS VARCHAR(100),
@Country AS varchar(50)
AS
BEGIN TRAN
--------RecipientType_id,State_Id,Country
DECLARE
@RecipientType_id INT,
@State_Id INT,
@COUNT INT,
@intErrorCode INT,
@AttendeeInfoAttendee_id INT
select @RecipientType_id = RecipientType_id from PMP_RecipientType where RecipientType_Name = @Customer_Type
select @State_Id = State_Id from PMP_StateMaster where State_initials = @State
IF(EXISTS(SELECT A.*,AI.PolarisCustomerID,AI.CustomerID,AI.Fullname_organizationName,AI.RecipientType_id,AI.NPINumber
FROM PMP_AttendeeInformation AS AI INNER JOIN
PMP_Address AS A ON AI.Attendee_Id = A.Attendee_Id
WHERE --AI.PolarisCustomerID = @PolarisCustomerID
AI.CustomerID = @Customer_Id
AND AI.Fullname_organizationName = @FullName
AND AI.RecipientType_id = @RecipientType_id
--AND AI.NPINumber = @NPINumber
--AND A.PolarisAddressID = @PolarisAddressID
--AND A.Attendee_Id = @Customer_Id
AND A.Location_id = @Address_ID
AND A.AddressLine1 = @AddressLine1
AND A.City = @City
AND A.State_Id = @State_Id
AND A.PostalCode = @PostalCode
AND Convert(VARCHAR(10),AI.CreatedDate,121) = Convert(VARCHAR(10),@createDate,121)
AND Convert(VARCHAR(10),A.CreateDate,121) = Convert(VARCHAR(10),@createDate,121)
))
BEGIN
-------No Changes in Existing records of Core Tables
SET @COUNT =1
END
ELSE IF(NOT EXISTS(SELECT A.*,AI.PolarisCustomerID,AI.CustomerID,AI.Fullname_organizationName,AI.RecipientType_id,AI.NPINumber
FROM PMP_AttendeeInformation AS AI INNER JOIN
PMP_Address AS A ON AI.Attendee_Id = A.Attendee_Id
where AI.CustomerID = @Customer_Id
--AND AI.Fullname_organizationName = @FullName
--AND AI.RecipientType_id = @RecipientType_id
--AND AI.NPINumber = @NPINumber
--AND A.PolarisAddressID = @PolarisAddressID
--AND A.Attendee_Id = @Customer_Id
AND A.Location_id = @Address_ID
--AND A.AddressLine1 = @AddressLine1
--AND A.City = @City
--AND A.State_Id = @State_Id
--AND A.PostalCode = @PostalCode
AND Convert(VARCHAR(10),AI.CreatedDate,121) = Convert(VARCHAR(10),@createDate,121)
AND Convert(VARCHAR(10),A.CreateDate,121) = Convert(VARCHAR(10),@createDate,121)
))
BEGIN
INSERT INTO PMP_AttendeeInformation(PolarisCustomerID,CustomerID,Fullname_organizationName,RecipientType_id,NPINumber,Isorg,CreatedDate,Creator)
VALUES(@PolarisCustomerID,@Customer_Id,@FullName,@RecipientType_id,@NPINumber,1,@createDate,@SourceSystem)
select @AttendeeInfoAttendee_id = max(Attendee_Id) from PMP_AttendeeInformation --where CustomerID = @Customer_Id and Fullname_organizationName = @FullName
INSERT INTO PMP_Address(PolarisAddressID,Attendee_Id,Location_id,AddressLine1,City,State_Id,PostalCode,Country,CreateDate,Creator)
VALUES(@PolarisAddressID,@AttendeeInfoAttendee_id,@Address_ID,@AddressLine1,@City,@State_Id,@PostalCode,@Country,@createDate,@SourceSystem)
END
ELSE
BEGIN
------------------------------ Update Record ---------------------------------------
select @AttendeeInfoAttendee_id = max(Attendee_Id) from PMP_AttendeeInformation
UPDATE PMP_AttendeeInformation SET
PolarisCustomerID = @PolarisCustomerID
,Fullname_organizationName = @FullName
,RecipientType_id = @RecipientType_id
,NPINumber = @NPINumber
,Creator = @SourceSystem
WHERE Attendee_Id = @AttendeeInfoAttendee_id
UPDATE PMP_Address SET
PolarisAddressID = @PolarisAddressID
,Location_id = @Address_ID
,AddressLine1 = @AddressLine1
,City = @City
,State_Id = @State_Id
,PostalCode = @PostalCode
,Country = @Country
,Creator = @SourceSystem
WHERE Attendee_Id = @AttendeeInfoAttendee_id
END
SELECT @intErrorCode = @@ERROR
IF (@intErrorCode <> 0) GOTO PROBLEM
COMMIT TRAN
----------------------------------------
PROBLEM:
IF (@intErrorCode <> 0)
BEGIN
---PRINT 'Unexpected error occurred!'
ROLLBACK TRAN
END
No comments:
Post a Comment