Print 'Doing pre run Checks to ensure script not already run'; Select * from Script_History where Script_Name='ReleaseJune2025Week5'; if @@ROWCOUNT!=0 BEGIN RAISERROR('THIS SCRIPT HAS ALREADY BEEN RUN ON THIS SERVER, CANNOT CONTINUE, PLEASE CONTACT DEV',11,1); RETURN; END GO insert into SageAccountDetail(AccountName,AccountNumber,AccountType,Vatable,Description) values('GoldCoinsPurchasesOutright','IBR00620','BankTransaction',1,'Gold coin purchases outright'); insert into SageAccountDetail(AccountName,AccountNumber,AccountType,Vatable,Description) values('GoldCoinsPurchasesCSB','IBR17280','BankTransaction',1,'Gold coin purchases CSB'); insert into SystemSetting(SettingKey,SettingCategoryId,SettingValueDecimal,TypeFlag) values('KeylineParAndProjAvailability',2,99.00,3); IF COL_LENGTH('Buyshop_FIC_Seller_Info', 'ApiError') IS NULL BEGIN ALTER TABLE Buyshop_FIC_Seller_Info ADD ApiError VARCHAR(MAX); 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, fic.ApiError 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,'ReleaseJune2025Week5'); IF @@ERROR != 0 BEGIN RAISERROR ('UNABLE TO CONTINUE WITH SCRIPT', 11, 1); RETURN; END GO