Print 'Doing pre run Checks to ensure script not already run'; Select * from Script_History where Script_Name='ReleaseApril2025Week4'; if @@ROWCOUNT!=0 BEGIN RAISERROR('THIS SCRIPT HAS ALREADY BEEN RUN ON THIS SERVER, CANNOT CONTINUE, PLEASE CONTACT DEV',11,1); RETURN; END GO /* ======================== -- Fic Api Request Log -- ======================== */ if object_id('dbo.FicApiRequestLog', 'U') is null begin -- auto-generated definition create table dbo.FicApiRequestLog ( FicApiRequestLogId bigint identity primary key, StoreId bigint not null, SourceDocumentId bigint not null, SourceDocumentType varchar(100) not null, UserId varchar(100), EventType varchar(100), RequestMethod varchar(10), EndPoint varchar(255), RequestPayload varchar(max), ResponsePayload varchar(max), StatusCode int, Success bit not null, ErrorMessage varchar(max), TraceId varchar(100), CreatedAt datetime default getdate() ); create index IX_FicApiRequestLog_SourceDocumentId on dbo.FicApiRequestLog (SourceDocumentId) create index IX_FicApiRequestLog_SourceDocumentType on dbo.FicApiRequestLog (SourceDocumentType) create index IX_FicApiRequestLog_EventType on dbo.FicApiRequestLog (EventType) create index IX_FicApiRequestLog_StoreId on dbo.FicApiRequestLog (StoreId) create index IX_FicApiRequestLog_CreatedAt on dbo.FicApiRequestLog (CreatedAt) end go if object_id('FicApiRequestLog_TR', 'TR') is not null begin drop trigger FicApiRequestLog_TR end go CREATE TRIGGER [FicApiRequestLog_TR] ON [FicApiRequestLog] FOR INSERT, UPDATE, DELETE AS declare @i varchar(max), @d varchar(max) set @i = convert(varchar(max), (select * from inserted for xml auto, BINARY BASE64)) set @d = convert(varchar(max), (select * from deleted for xml auto, BINARY BASE64)) exec LogDMLEvent @TableName = 'FicApiRequestLog', @Deleted = @D, @Inserted = @i; go /* ======================== -- Fic Api Log Error -- ======================== */ if object_id('dbo.FicApiLogError', 'U') is null begin -- auto-generated definition create table dbo.FicApiLogError ( ErrorLogId bigint identity primary key, StoreId bigint not null, SourceDocumentId bigint not null, SourceDocumentType varchar(50) not null, UserId varchar(50), EventType varchar(50) not null, RequestMethod varchar(10), EndPoint varchar(255), RequestPayload varchar(max), ResponsePayload varchar(max), StatusCode int, IsSuccess bit default 0 not null, ErrorMessage varchar(max), TraceId varchar(50), ErrorDescription varchar(max), ErrorProcedure varchar(128), ErrorLine int, ErrorSeverity int, ErrorState int, LoggedDate datetime default getdate() ); create index IX_FicApiLogError_SourceDocumentId on dbo.FicApiLogError (SourceDocumentId) create index IX_FicApiLogError_SourceDocumentType on dbo.FicApiLogError (SourceDocumentType) create index IX_FicApiLogError_EventType on dbo.FicApiLogError (EventType) create index IX_FicApiLogError_StoreId on dbo.FicApiLogError (StoreId) create index IX_FicApiLogError_LoggedDate on dbo.FicApiLogError (LoggedDate) end go if object_id('FicApiLogError_TR', 'TR') is not null begin drop trigger FicApiLogError_TR end go CREATE TRIGGER [FicApiLogError_TR] ON [FicApiLogError] FOR INSERT, UPDATE, DELETE AS declare @i varchar(max), @d varchar(max) set @i = convert(varchar(max), (select * from inserted for xml auto, BINARY BASE64)) set @d = convert(varchar(max), (select * from deleted for xml auto, BINARY BASE64)) exec LogDMLEvent @TableName = 'FicApiLogError', @Deleted = @D, @Inserted = @i; go /* =============================== -- Fic Api Log Request Proc -- =============================== */ if object_id('spLogFicApiRequest', 'P') is not null begin drop procedure spLogFicApiRequest; end go CREATE procedure spLogFicApiRequest( @StoreId bigint, @SourceDocumentId bigint, @SourceDocumentType varchar(100), @UserId varchar(100) = null, @EventType varchar(100), @RequestMethod varchar(max) = null, @EndPoint varchar(100) = null, @RequestPayload varchar(max) = null, @ResponsePayload varchar(max) = null, @StatusCode varchar(50) = null, @IsSuccess bit, @ErrorMessage varchar(max) = null, @TraceId varchar(100) = null, @InsertionResult int output ) as begin set nocount on; begin transaction; begin try insert into FicApiRequestLog(storeid, sourcedocumentid, sourcedocumenttype, userid, eventtype, requestmethod, endpoint, requestpayload, responsepayload, statuscode, success, errormessage, traceid) values (@StoreId, @SourceDocumentId, @SourceDocumentType, @UserId, @EventType, @RequestMethod, @EndPoint, @RequestPayload, @ResponsePayload, @StatusCode, @IsSuccess, @ErrorMessage, @TraceId) -- set success state set @InsertionResult = 0; commit transaction; end try begin catch if @@trancount > 0 rollback transaction; -- log error to internal error table -- Log the error details to FicApiLogError INSERT INTO FicApiLogError (StoreId, SourceDocumentId, SourceDocumentType, UserId, EventType, RequestMethod, EndPoint, RequestPayload, ResponsePayload, StatusCode, IsSuccess, ErrorMessage, TraceId, ErrorDescription, ErrorProcedure, ErrorLine, ErrorSeverity, ErrorState) VALUES (@StoreId, @SourceDocumentId, @SourceDocumentType, @UserId, @EventType, @RequestMethod, @EndPoint, @RequestPayload, @ResponsePayload, @StatusCode, 0, @ErrorMessage, @TraceId, ERROR_MESSAGE(), ERROR_PROCEDURE(), ERROR_LINE(), ERROR_SEVERITY(), ERROR_STATE()); -- set failed state set @InsertionResult = 1; end catch; end; go if object_id('spGetFicSsbReport', 'P') is not null begin drop procedure spGetFicSsbReport end go create procedure spGetFicSsbReport @fromDate datetime, @toDate datetime as begin SELECT bt.Created, bt.Transaction_Number AS TransactionNumber, 'SSB' AS TransactionType, (SELECT SUM(btl.Line_Total) FROM Buyshop_Trans_Line btl WHERE btl.Buyshop_Transaction_ID = bt.Buyshop_Transaction_ID) AS TransactionValue, s.First_Name AS FirstName, s.Surname, s.Gender, it.Description AS IdType, s.ID_Number AS IDNumber, s.Cell_Number AS CellNumber, s.Email_Address AS EmailAddress, s.Address_1 AS Address1, s.Address_2 AS Address2, s.Suburb, c2.Description AS NationalityCountry, c.Description AS ResidentialCountry, s.Postal_Code AS PostalCode, (SELECT IIF(fic.IsSuspicious = 1, 'YES', 'NO')) AS Suspicious, (SELECT IIF(fic.IsSanction = 1, 'YES', 'NO')) AS Sanction, (SELECT IIF(fic.IsAssociate = 1, 'YES', 'NO')) AS Associated, (SELECT IIF(fic.HasCopyOfID = 1, 'YES', 'NO')) AS CopyOfID, l.Description AS LoanRepayment, h.Description AS HairColor, e.Description AS EyeColor, c3.Description AS PlaceOfBirth, fic.PlaceOfEmployment, (SELECT CASE WHEN fic.ResidentialAddressVerification = 1 THEN 'YES' WHEN fic.ResidentialAddressVerification IS NULL THEN NULL ELSE 'NO' END) AS ResidentialAddressVerification, (SELECT CASE WHEN fic.SourceOfFundsVerification = 1 THEN 'YES' WHEN fic.SourceOfFundsVerification IS NULL THEN NULL ELSE 'NO' END) AS SourceOfFundsVerification, f.Description AS TelephoneNumberVerification, -- Consumer Details FicExtract.EnquiryID, FicExtract.EnquiryResultID, FicExtract.Reference, -- First Consumer FicExtract.EntityName, FicExtract.ReasonListed, FicExtract.BestNameScore, FicExtract.ListReferenceNumber, YearOfBirth, DateOfBirth as DOB, Citizenship, SourceOfRecord, -- Second Consumer FicExtract.EntityName_1, FicExtract.ReasonListed_1, FicExtract.BestNameScore_1, FicExtract.ListReferenceNumber_1, YearOfBirth_1, DateOfBirth_1 as DOB_1, Citizenship_1, SourceOfRecord_1 FROM Buyshop_Transaction bt INNER JOIN Buyshop_FIC_Seller_Info fic ON fic.Buyshop_Transaction_ID = bt.Buyshop_Transaction_ID INNER JOIN Seller s ON s.Seller_ID = bt.Seller_ID INNER JOIN Identity_Type it ON it.Identity_Type_ID = s.Identity_Type_ID INNER JOIN Country c ON c.Country_ID = s.CountryId INNER JOIN Country c2 ON c2.Country_ID = s.NationalityId INNER JOIN FICLoanRepaymentOptions l ON l.ID = fic.LoanRepaymentOption LEFT OUTER JOIN HairColor h ON h.HairColorId = fic.HairColor LEFT OUTER JOIN EyeColor e ON e.EyeColorId = fic.EyeColor LEFT OUTER JOIN Country c3 ON c3.Country_ID = fic.PlaceOfBirth LEFT OUTER JOIN FicTelephoneVerification f ON f.FicTelephoneVerificationId = fic.TelephoneNumberVerification -- extract the element of the 'ConsumerDetails' result object outer apply(select top(1) json_query(ResponsePayload, '$.ConsumerDetails') as ConsumerDetails from FicApiRequestLog where SourceDocumentId = bt.Buyshop_Transaction_ID and EventType = 'Consumer Match Exposure Standard' order by CreatedAt desc) as ConsumerDetails_Extract -- extract the first element of the 'SupplierData' result object outer apply(select top(1) json_query(ResponsePayload, '$.SupplierData[0]') as SupplierData_0 from FicApiRequestLog where SourceDocumentId = bt.Buyshop_Transaction_ID and EventType = 'Consumer Exposure Result' order by CreatedAt desc) as SupplierData_0_Extract -- extract the second element of the 'SupplierData' result object outer apply (select top(1) json_query(ResponsePayload, '$.SupplierData[1]') as SupplierData_1 from FicApiRequestLog where SourceDocumentId = bt.Buyshop_Transaction_ID and EventType = 'Consumer Exposure Result' order by CreatedAt desc) as SupplierData_1_Extract /* -- THE FOLLOWING SECTION EXTRACTS THE 'AdditionalInfo' FIELDS OF THE 'SupplierData' OBJECT -- -- These fields are retrieved dynamically from XDS and cannot guarantee that there will be info -- -- The following properties will be extracted: -- -- 1. Year of Birth -- -- 2. Date of Birth -- -- 3. Citizenship -- -- 4. Source of Record -- These values will come from the first element of the 'SupplierData' result */ /* ------------------------------------------ -- Extract Year of Birth -- ------------------------------------------ */ outer apply (select charindex('"Type":"Year of birth","Value":"', SupplierData_0) as StartPos) as Year_of_Birth_Pos1 outer apply (select Year_of_Birth_Pos1.StartPos + LEN('"Type":"Year of birth","Value":"') as ValueStart) as Year_of_Birth_Pos2 outer apply (select charindex('"', SupplierData_0, Year_of_Birth_Pos2.ValueStart) as ValueEnd) as Year_of_Birth_Pos3 outer apply (select substring(SupplierData_0, Year_of_Birth_Pos2.ValueStart, Year_of_Birth_Pos3.ValueEnd - Year_of_Birth_Pos2.ValueStart) as YearOfBirth) as YearOfBirth /* ------------------------------------------ -- Extract Date of Birth -- ------------------------------------------ */ outer apply (select charindex('"Type":"DOB","Value":"', SupplierData_0) as StartPos) as Date_of_Birth_Pos1 outer apply (select Date_of_Birth_Pos1.StartPos + LEN('"Type":"DOB","Value":"') as ValueStart) as Date_of_Birth_Pos2 outer apply (select charindex('"', SupplierData_0, Date_of_Birth_Pos2.ValueStart) as ValueEnd) as Date_of_Birth_Pos3 outer apply (select substring(SupplierData_0, Date_of_Birth_Pos2.ValueStart, Date_of_Birth_Pos3.ValueEnd - Date_of_Birth_Pos2.ValueStart) as DateOfBirth) as DateOfBirth /* ------------------------------------------ -- Extract Citizenship -- ------------------------------------------ */ outer apply (select charindex('"Type":"Citizenship","Value":"', SupplierData_0) as StartPos) as Citizenship_Pos1 outer apply (select Citizenship_Pos1.StartPos + LEN('"Type":"Citizenship","Value":"') as ValueStart) as Citizenship_Pos2 outer apply (select charindex('"', SupplierData_0, Citizenship_Pos2.ValueStart) as ValueEnd) as Citizenship_Pos3 outer apply (select substring(SupplierData_0, Citizenship_Pos2.ValueStart, Citizenship_Pos3.ValueEnd - Citizenship_Pos2.ValueStart) as Citizenship) as Citizenship /* ------------------------------------------ -- Extract Source of Record -- ------------------------------------------ */ outer apply (select charindex('"Type":"Source of record","Value":"', SupplierData_0) as StartPos) as Sor_Po1 outer apply (select Sor_Po1.StartPos + LEN('"Type":"Source of record","Value":"') as ValueStart) as Sor_Po2 outer apply (select charindex('"', SupplierData_0, Sor_Po2.ValueStart) as ValueEnd) as Sor_Po3 outer apply (select substring(SupplierData_0, Sor_Po2.ValueStart, Sor_Po3.ValueEnd - Sor_Po2.ValueStart) as SourceOfRecord) as SourceOfRecord /* -- THE FOLLOWING SECTION EXTRACTS THE 'AdditionalInfo' FIELDS OF THE 'SupplierData' OBJECT -- -- These fields are retrieved dynamically from XDS and cannot guarantee that there will be info -- -- The following properties will be extracted: -- -- 1. Year of Birth -- -- 2. Date of Birth -- -- 3. Citizenship -- -- 4. Source of Record -- These values will come from the second element of the 'SupplierData' result */ /* ------------------------------------------ -- Extract Year of Birth 2 -- ------------------------------------------ */ outer apply (select charindex('"Type":"Year of birth","Value":"', SupplierData_1) as StartPos) as Year_of_Birth_1_Pos1 outer apply (select Year_of_Birth_1_Pos1.StartPos + LEN('"Type":"Year of birth","Value":"') as ValueStart) as Year_of_Birth_1_Pos2 outer apply (select charindex('"', SupplierData_1, Year_of_Birth_1_Pos2.ValueStart) as ValueEnd) as Year_of_Birth_1_Pos3 outer apply (select substring(SupplierData_1, Year_of_Birth_1_Pos2.ValueStart, Year_of_Birth_1_Pos3.ValueEnd - Year_of_Birth_1_Pos2.ValueStart) as YearOfBirth_1) as YearOfBirth_1 /* ------------------------------------------ -- Extract Date of Birth 2-- ------------------------------------------ */ outer apply (select charindex('"Type":"DOB","Value":"', SupplierData_1) as StartPos) as Date_of_Birth_1_Pos1 outer apply (select Date_of_Birth_1_Pos1.StartPos + LEN('"Type":"DOB","Value":"') as ValueStart) as Date_of_Birth_1_Pos2 outer apply (select charindex('"', SupplierData_1, Date_of_Birth_1_Pos2.ValueStart) as ValueEnd) as Date_of_Birth_1_Pos3 outer apply (select substring(SupplierData_1, Date_of_Birth_1_Pos2.ValueStart, Date_of_Birth_1_Pos3.ValueEnd - Date_of_Birth_1_Pos2.ValueStart) as DateOfBirth_1) as DateOfBirth_1 /* ------------------------------------------ -- Extract Citizenship 2 -- ------------------------------------------ */ outer apply (select charindex('"Type":"Citizenship","Value":"', SupplierData_1) as StartPos) as Citizenship_1_Pos1 outer apply (select Citizenship_1_Pos1.StartPos + LEN('"Type":"Citizenship","Value":"') as ValueStart) as Citizenship_1_Pos2 outer apply (select charindex('"', SupplierData_1, Citizenship_1_Pos2.ValueStart) as ValueEnd) as Citizenship_1_Pos3 outer apply (select substring(SupplierData_1, Citizenship_1_Pos2.ValueStart, Citizenship_1_Pos3.ValueEnd - Citizenship_1_Pos2.ValueStart) as Citizenship_1) as Citizenship_1 /* ------------------------------------------ -- Extract Source of Record 2 -- ------------------------------------------ */ outer apply (select charindex('"Type":"Source of record","Value":"', SupplierData_1) as StartPos) as Sor_1_Po1 outer apply (select Sor_1_Po1.StartPos + LEN('"Type":"Source of record","Value":"') as ValueStart) as Sor_1_Po2 outer apply (select charindex('"', SupplierData_1, Sor_1_Po2.ValueStart) as ValueEnd) as Sor_1_Po3 outer apply (select substring(SupplierData_1, Sor_1_Po2.ValueStart, Sor_1_Po3.ValueEnd - Sor_1_Po2.ValueStart) as SourceOfRecord_1) as SourceOfRecord_1 /* ----------------------------------------- -- Extract fields from ConsumerDetails -- ----------------------------------------- */ outer apply (select json_value(ConsumerDetails_Extract.ConsumerDetails, '$.EnquiryID') as EnquiryID, json_value(ConsumerDetails_Extract.ConsumerDetails, '$.EnquiryResultID') as EnquiryResultID, json_value(ConsumerDetails_Extract.ConsumerDetails, '$.Reference') as Reference, /* ------------------------------------------ -- Extract fields from SupplierData[0] -- ------------------------------------------ */ json_value(SupplierData_0_Extract.SupplierData_0, '$.EntityName') as EntityName, json_value(SupplierData_0_Extract.SupplierData_0, '$.ReasonListed') as ReasonListed, json_value(SupplierData_0_Extract.SupplierData_0, '$.BestNameScore') as BestNameScore, json_value(SupplierData_0_Extract.SupplierData_0, '$.ListReferenceNumber') as ListReferenceNumber, /* ------------------------------------------ -- Extract fields from SupplierData[1] -- ------------------------------------------ */ json_value(SupplierData_1_Extract.SupplierData_1, '$.EntityName') as EntityName_1, json_value(SupplierData_1_Extract.SupplierData_1, '$.ReasonListed') as ReasonListed_1, json_value(SupplierData_1_Extract.SupplierData_1, '$.BestNameScore') as BestNameScore_1, json_value(SupplierData_1_Extract.SupplierData_1, '$.ListReferenceNumber') as ListReferenceNumber_1) as FicExtract WHERE Buyshop_Trans_Type_ID = 2 AND Buyshop_Trans_Status_ID IN (4, 6) AND bt.Created >= @fromDate AND bt.Created <= @toDate end go Print 'Insert into script history if everything else is cool'; Insert into Script_History (RunDate,Script_Name) Values (CURRENT_TIMESTAMP,'ReleaseApril2025Week4'); IF @@ERROR != 0 BEGIN RAISERROR ('UNABLE TO CONTINUE WITH SCRIPT', 11, 1); RETURN; END GO