WITH BASE AS (
SELECT
        'Detail' AS columntype  ,
        {JobService}.[JobId] AS JobId,
        {JobService}.[Id] AS JobServiceId,
        {MainService}.[GoldenRecordId] AS MainServiceId,
        {MainService}.[MainServiceName] AS MainServiceName,
        {Service}.[GoldenRecordId] AS ServiceId,
        {Service}.[ServiceName] AS ServiceName,
        {Currency}.[GoldenRecordId] AS CurrencyId,
        {Currency}.[CurrencyCode] AS CurrencyCode,
        {Currency}.[CurrencyCode] + ' - ' + {Currency}.[CurrencyName] AS CurrencyDisplayName  ,
        {JobService}.[ExchangeRate] AS ExchangeRate,
        {Company}.[Id] AS SupplierId,
        {Company}.[Name] AS SupplierName,
        {JobService}.[PDA] AS PDA,
        {JobService}.[ManualPayment] AS ManualPayment,
        '' AS DA_No,
        {JobService}.[CloseEstimate] AS CloseEstimate,
        {JobService}.[ActualAmount] AS Actual,
        ABS({JobService}.[CloseEstimate] - {JobService}.[ActualAmount]) AS Variance,
        {JobService}.[Comments] AS Comments,
        {JobService}.[StatusId] AS StatusId,
        {JobService}.[CreatedBy] AS CreatedBy,
        getdate() AS CreatedOn,

        CASE WHEN EXISTS
        (
        SELECT {JobService_Invoice}.[JobServiceId]
        FROM {JobService_Invoice}
        INNER JOIN {Invoice} ON
        {JobService_Invoice}.[InvoiceId] = {Invoice}.[Id]
        AND
        {JobService_Invoice}.[JobServiceId] =  JobServiceId
        AND
        {Invoice}.[IsSentToU4F] = 1

        )
        THEN 0
        ELSE 1
        END AS isEditable,

        0 AS isSelected,
        1 AS isServiceValid,
        CASE
        WHEN LEFT({Service}.[ServiceCode], 1) = @NotMandatorySuplierCaracter
        THEN 0
        ELSE 1
        END AS isSupplierMandatory

      FROM  {JobService}
INNER JOIN     {Service} ON {JobService}.[ServiceId]        =     {Service}.[GoldenRecordId]
INNER JOIN {MainService} ON    {Service}.[MainServiceId]    = {MainService}.[GoldenRecordId]
 LEFT JOIN     {Company} ON {JobService}.[SupplierId]       =     {Company}.[Id]
 LEFT JOIN    {Currency} ON {JobService}.[CurrencyId]       =    {Currency}.[GoldenRecordId]

     WHERE {JobService}.[JobId] = @JobId
       AND ({JobService}.[SupplierId] = @SupplierSelected OR @SupplierSelected = 0  ))
        ,

TOTAL AS (

SELECT
        'Total'  AS columntype ,
         NULL AS JobId,
         NULL AS JobServiceId,
         NULL AS MainServiceId,
         NULL AS MainServiceName,
         NULL AS ServiceId,
         NULL AS ServiceName,
         NULL AS CurrencyId,
         NULL AS CurrencyCode,
         NULL AS CurrencyDisplayName  ,
         NULL AS ExchangeRate,
         NULL AS SupplierId,
         NULL AS SupplierName,
         (SELECT SUM(PDA) FROM base)  AS PDA,
         NULL AS ManualPayment,
         NULL AS DA_No,
         (SELECT SUM(CloseEstimate) FROM base)  AS CloseEstimate,
         (SELECT SUM(Actual) FROM base)  AS Actual,
         (SELECT SUM(variance) FROM base) AS Variance,
         NULL AS Comments,
         NULL AS StatusId,
         NULL AS CreatedBy,
         NULL AS CreatedOn,
        0 AS isEditable,
        0 AS isSelected,
        1 AS isServiceValid,
        0 AS isSupplierMandatory

        FROM base  WHERE (SELECT COUNT(0) FROM base ) >= 0

      )

SELECT * FROM base
UNION ALL
SELECT * FROM total GROUP BY    columntype ,
JobId,
JobServiceId,
MainServiceId,
MainServiceName,
ServiceId,
ServiceName,
CurrencyId,
CurrencyCode,
CurrencyDisplayName  ,
ExchangeRate,
SupplierId,
SupplierName,
PDA,
ManualPayment,
DA_No,
CloseEstimate,
Actual,
Variance,
Comments,
StatusId,
CreatedBy,
CreatedOn,
isEditable,
isSelected,
isServiceValid,
isSupplierMandatory