USE [LegitimateKZN_QA_SO]
GO

/****** Object:  View [App].[Vw_ApplicationRequestDetails]    Script Date: 2025/11/29 10:41:14 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

ALTER VIEW [App].[Vw_ApplicationRequestDetails]
AS
SELECT        ARD.ApplicationRequestDetailID, ARD.WorkflowInstanceID, WI.ReferenceNumber AS ApplicationNumber, ART.Title AS RequestType, 
						(
							SELECT SUM(Rd.TotalCost)
							FROM App.ReceiptDetails Rd
							WHERE Rd.ApplicationRequestDetailID = Ard.ApplicationRequestDetailID
						) AS Cost,
						 UPPER((CASE WHEN AAD.FirstNames IS NULL 
                         THEN AAD.SurnameOrCompanyName ELSE AAD.FirstNames + ' ' + AAD.SurnameOrCompanyName END)) AS SurnameOrCompanyName, UPPER(AAD.FirstNames) AS FirstNames, UPPER(AAD.TradeName) AS TradeName, 
                         UPPER(CASE WHEN AAD.IDTypeID IN (3, 6) THEN AAD.TradeName ELSE ISNULL(AAD.FirstNames, '') + ' ' + ISNULL(AAD.SurnameOrCompanyName, '') END) AS Member, AAD.VerifiedMemberID, 
                         AAD.IDNumberOrBusinessRegistrationNo, ARD.ApplicationRequestTypeID, ARD.SourceApplicationID, ARD.IsNewAssociationRequired, ARD.IsNewMemberRequired, 
                         ARD.IsNewRouteRequired, ARD.IsExceptionRequired, ARD.IsUpliftmentRequired, ARD.IsAdjustmentRequired, ARD.IsCancellationRequired, GD.GazetteNumber, GD.GazetteDetailID, AD.MeetingDate, AD.AdjudicationDetailID, 
                         ARD.PaymentTypeID, ARD.VehicleCount, UPPER(AVD.VIN) AS ChassisNumber, UPPER(AVD.EngineNumber) AS EngineNumber, UPPER(AVD.VehicleRegistrationNumber) AS VehicleRegNumber, AVD.SeatedCapacity, 
                         Vm.Description AS VehicleMake, ADE.Description AS AdjudicationDecision, ARD.Description AS ApplicationDescription, ARD.IsRenewal, ISNULL(UPPER(A.AssociationName), 'Unknown') AS AssociationName, AMD.ModeID, 
                         ISNULL(AAD.SARSIncomeTaxNumber, AAD.SARSReasonIfNoTaxNumber) AS TaxNumber, ARD.PrecheckDetailID, AVD.IsStillToBeAcquired, ISNULL(Apd.FirstNames, '') + ' ' + ISNULL(Apd.Surname, '') AS ProxyName, ARD.IsCancelled, 
                         ARD.AdjudicationDecisionID, '' AS AdjudicationComment, ARD.AdjudicationException, AAD.TelCell, AAD.TelPhone, ARD.RegionID, ARD.AdjudicationRegionID, UPPER(CASE WHEN Aelh.IDTypeID IN (3, 6) 
                         THEN Aelh.TradeName ELSE ISNULL(Aelh.FirstNames, '') + ' ' + ISNULL(Aelh.SurnameOrCompanyName, '') END) AS ExistingLicenceHolder, Aelh.IDNumberOrBusinessRegistrationNo AS ExistingLicenceHolderID, 
                         ISNULL(R.Description, 'Not Available') AS Region, UPPER(AVDE.VIN) AS ExistingVehicleChassisNumber, UPPER(AVDE.VehicleRegistrationNumber) AS ExistingVehicleRegNumber, WIS.Description AS WorkflowStatus, 
                         ARD.IsAdjudicationBypassed, ARD.IsGazettingBypassed, ARD.IsAssessorApprovalRequired, ARD.IsSeniorManagerApprovalRequired, ARD.HasNewRoute, ISNULL(AEL.OperatingLicenceNumber, 'Not Available') 
                         AS ExistingLicenceNumber, ARD.IsAutoVerificationRequired, ARD.IsLtpsConversion, Apd.IDNumberOrBusinessRegistrationNo AS ProxyIDNumber, AVD.StandingCapacity AS StandingCapacity, CAST(ISNULL(ARD.AdjudicationCount, '0') AS SMALLINT) AS AdjudicationCount,
						 AAD.PhysicalAddress, AAD.PhysicalAddressCode, AAD.PostalAddress, AAD.PostalAddressCode
FROM            App.ApplicationRequestDetails AS ARD INNER JOIN
                         App.ApplicationProxyDetails AS Apd ON ARD.ApplicationRequestDetailID = Apd.ApplicationRequestDetailID INNER JOIN
                         App.ApplicationVehicleDetails AS AVD ON ARD.ApplicationRequestDetailID = AVD.ApplicationRequestDetailID INNER JOIN
                         App.ApplicationExistingVehicleDetails AS AVDE ON AVDE.ApplicationRequestDetailID = ARD.ApplicationRequestDetailID LEFT OUTER JOIN
                         Md.Regions AS R ON ARD.RegionID = R.RegionID LEFT OUTER JOIN
                         Md.VehicleMakes AS Vm ON AVD.VehicleMakeID = Vm.VehicleMakeID INNER JOIN
                         Wf.WorkflowInstances AS WI ON ARD.WorkflowInstanceID = WI.WorkflowInstanceID INNER JOIN
                         Wf.WorkflowInstanceStatus AS WIS ON WI.WorkflowInstanceStatusID = WIS.WorkflowInstanceStatusID INNER JOIN
                         App.ApplicationRequestTypes AS ART ON ARD.ApplicationRequestTypeID = ART.ApplicationRequestTypeID INNER JOIN
                         App.ApplicationApplicantDetails AS AAD ON ARD.ApplicationRequestDetailID = AAD.ApplicationRequestDetailID LEFT OUTER JOIN
                         App.GazetteDetails AS GD ON ARD.GazetteDetailID = GD.GazetteDetailID LEFT OUTER JOIN
                         App.AdjudicationDetails AS AD ON ARD.AdjudicationDetailID = AD.AdjudicationDetailID LEFT OUTER JOIN
                         App.AdjudicationDecisions AS ADE ON ARD.AdjudicationDecisionID = ADE.AdjudicationDecisionID LEFT OUTER JOIN
                         Verified.Associations AS A ON A.AssociationID = AAD.VerifiedAssociationID LEFT OUTER JOIN
                         App.ApplicationModeDetails AS AMD ON AMD.ApplicationRequestDetailID = ARD.ApplicationRequestDetailID LEFT OUTER JOIN
                         App.ApplicationExistingLicences AS AEL ON AEL.ApplicationRequestDetailID = ARD.ApplicationRequestDetailID LEFT OUTER JOIN
                         App.ApplicationExistingLicenceHolders AS Aelh ON ARD.ApplicationRequestDetailID = Aelh.ApplicationRequestDetailID





GO


