------------------------------------------------------------------------------------------------------------------------------------------------------------------------ /*Sponsorship - Static General Info Fields Query*/ --Note: Use 'Client_' as prefix in an alias to indicate this field is specific to a client -------------------------------------------------------------------------------------------------------------------------------------------------------------------------- /*====================Testing Parameters===========================*/ --declare @id uniqueidentifier = (select TOP 1 c.kore_contractid from kore_contract c -- where c.kore_name = 10153 --10019 --10150 --10055 --'11353' -- -- kore_startcontractdate is not null -- -- and kore_obligations is not null -- -- and kore_otherobligations is not null -- -- and c.sf_primarycontactid is not null -- -- and c.kore_contractid in (select cl.kore_contractid from kore_contractline cl -- -- inner join kore_property p on p.kore_propertyid = cl.kore_propertyid and p.statecode = 0 -- -- where cl.statecode = 0 -- -- group by cl.kore_contractid -- -- having count(cl.kore_contractid) > 5 -- --) -- ) /*========================Parameters===========================*/ declare @today datetime = DATEADD(hour,-5,GETDATE()) declare @Season uniqueidentifier = (select kore_seasonid from kore_season where kore_default = 1 and statecode = 0) declare @SaleType varchar(20) = (select top 1 cs.kore_saletypename from kore_contractseason cs where cs.kore_contractid = @id and cs.kore_seasonid = @Season and cs.kore_roundid is null) declare @DateFormat varchar(12) = (select top 1 kore_value from kore_setting where kore_name = 'Date Format') declare @Currency uniqueidentifier = (select top 1 isnull(kore_currencyid, '00000000-0000-0000-0000-000000000000') from kore_contract where kore_contractid = @id) declare @ExchangeRate float declare @CurrencySymbol varchar(8) select top 1 @ExchangeRate = isnull(m.kore_rate, 1) , @CurrencySymbol = isnull(c2.kore_currencysymbol, '$') from kore_contract c left join Kore.CurrencyConversionMatrix(@Season) m on c.kore_currencyid = m.kore_fromcurrencyid AND m.kore_tocurrencyid = @Currency left join kore_currency c2 on c2.kore_currencyid = @Currency --select @ExchangeRate, @CurrencySymbol --testing /*========================Temp Tables===========================*/ --Season Revenue-- exec kore.droptemptable '#ContractSeason' --SUM can be query later from this temp table as well. select ROW_NUMBER() over(partition by c.kore_roundidname order by c.kore_seasonidname) as SeasonRow ,c.kore_seasonidname seasonname ,c.kore_roundidname roundname ,CASE WHEN FORMAT(s.kore_start, '%d') IN ( 11, 12, 13 ) THEN CAST(FORMAT(s.kore_start, '%d') AS VARCHAR(10)) + 'th' WHEN FORMAT(s.kore_start, '%d') % 10 = 1 THEN CAST(FORMAT(s.kore_start, '%d') AS VARCHAR(10)) + 'st' WHEN FORMAT(s.kore_start, '%d') % 10 = 2 THEN CAST(FORMAT(s.kore_start, '%d') AS VARCHAR(10)) + 'nd' WHEN FORMAT(s.kore_start, '%d') % 10 = 3 THEN CAST(FORMAT(s.kore_start, '%d') AS VARCHAR(10)) + 'rd' ELSE CAST(FORMAT(s.kore_start, '%d') AS VARCHAR(10)) + 'th' END + ' ' + Format(s.kore_start, 'MMMM') + + ' ' + Format(s.kore_start, 'yyyy') as Ordinary_start ,CASE WHEN FORMAT(s.kore_end, '%d') IN ( 11, 12, 13 ) THEN CAST(FORMAT(s.kore_end, '%d') AS VARCHAR(10)) + 'th' WHEN FORMAT(s.kore_end, '%d') % 10 = 1 THEN CAST(FORMAT(s.kore_end, '%d') AS VARCHAR(10)) + 'st' WHEN FORMAT(s.kore_end, '%d') % 10 = 2 THEN CAST(FORMAT(s.kore_end, '%d') AS VARCHAR(10)) + 'nd' WHEN FORMAT(s.kore_end, '%d') % 10 = 3 THEN CAST(FORMAT(s.kore_end, '%d') AS VARCHAR(10)) + 'rd' ELSE CAST(FORMAT(s.kore_end, '%d') AS VARCHAR(10)) + 'th' END + ' ' + Format(s.kore_end, 'MMMM') + + ' ' + Format(s.kore_end, 'yyyy') as Ordinary_end ,isnull(kore_SeasonGross,0) Gross ,isnull(c.kore_SeasonGross, 0) - (isnull(c.kore_barteramount, 0) + isnull(c.kore_barteramount2, 0)) Cash ,isnull(kore_seasonnet,0) Net ,isnull(kore_agencycommission,0) Agency ,isnull(c.kore_barteramount, 0) Trade ,isnull(kore_seasonvariablecost,0) Cost ,isnull(kore_barteramount,0) Barter1 ,isnull(kore_barteramount2,0) Barter2 ,isnull(kore_agencycommission,0) Commission ,isnull(c.kore_barterdescription, '') BarterNotes ,case when min(kore_saletype) over (partition by kore_contractid order by kore_contractid) = 1 then 'X' else '' end New ,case when min(kore_saletype) over (partition by kore_contractid order by kore_contractid) > 1 then 'X' else '' end Renewal into #ContractSeason from kore_contractseason c left join kore_round r on c.kore_roundid = r.kore_roundid left join kore_season s on c.kore_seasonid = s.kore_seasonid where kore_contractid = @Id and c.statecode = 0 order by c.kore_seasonidname, r.kore_order --select * from #ContractSeason --testing --Aggregated Season Revenue List-- exec kore.droptemptable '#Concat_SeasonList_Temp' SELECT concat( 'Season ', SeasonRow, ': ', Ordinary_start, ' - ', Ordinary_end) season_timelist ,concat( 'Season ', SeasonRow, ': ', Format(Isnull(Gross,0), @CurrencySymbol + '#,##0;(' + @CurrencySymbol + '#,##0)') ) season_revenuelist into #Concat_SeasonList_Temp from #ContractSeason --Billing Notes-- exec kore.droptemptable '#Bill' select max(b.KORE_Remarks) Notes into #Bill from kore_bill b where b.statecode = 0 and b.kore_contractid = @Id and b.kore_seasonid = @Season --Billings-- exec kore.droptemptable '#Billings' select ROW_NUMBER() over(partition by b.kore_roundidname order by b.kore_estimatedinvoicedate) as PaymentRow ,b.kore_seasonidname Season ,b.kore_roundidname RoundName ,b.kore_estimatedamount AmountInvoiced ,b.kore_totalreceived AmountReceived ,b.kore_taxamount TaxAmount ,b.kore_estimatedamount - b.kore_totalreceived AmountOutstanding ,b.kore_estimatedinvoicedate EstimatedInvoiceDate ,b.kore_actualinvoicedate InvoiceDate ,b.kore_paymentduedate DueDate into #Billings from kore_bill b --inner join kore_contractseason cs on cs.kore_contractid = b.kore_contractid and cs.kore_seasonid = b.kore_seasonid and isnull(b.kore_roundid, '00000000-0000-0000-0000-000000000000') = isnull(cs.kore_roundid,'00000000-0000-0000-0000-000000000000') and cs.statecode = 0 and cs.statuscode in (1,2) where b.statecode = 0 and b.kore_contractid = @Id order by b.kore_seasonidname --select * from #Billings --testing --Exclusivity List-- exec kore.droptemptable '#Exclusivities' select dbo.DistinctConcat( kore_exclusivityidname, '; ', 1 ) as ExclusivityOptionList , dbo.DistinctConcat(kore_remarks, '; ', 1 ) as ExlusivityRemarkList , dbo.DistinctConcat(si.Name, '; ', 1 ) AS ExlusivityIndustryList , dbo.DistinctConcat(kore_subindustry, '; ', 1 ) AS ExlusivitySubIndustryList , case when dbo.DistinctConcat(re.kore_parentregionidname, '; ', 1 ) like '%GLOBAL%' then 'GLOBAL' when dbo.DistinctConcat(re.kore_parentregionidname, '; ', 1 ) is null then dbo.DistinctConcat(re.kore_name, '; ', 1 ) else dbo.DistinctConcat(re.kore_parentregionidname, '; ', 1 ) end AS ExlusivityRegionalList into #Exclusivities from kore_contractexclusivityitem cei inner join kore_contract c on c.kore_contractid = cei.kore_contractid left join syncedindustries si on si.sf_industryid = cei.sf_industryid left join kore_kore_contractexclusivityitem_kore_regionbase ceibridge on ceibridge.kore_contractexclusivityitemid = cei.kore_contractexclusivityitemid and cei.statecode = 0 left join kore_region re on ceibridge.kore_regionid = re.kore_regionid where c.kore_contractid = @id and c.statecode = 0 and cei.statecode = 0 --and si.statecode = 0 --select * from #Exclusivities --testing --Inventory-- exec kore.droptemptable '#Inventories' select distinct cl.kore_contractidname as DealNum, p.kore_divisionidname as Divisions, pr.kore_categoryidname as Categories, pr.kore_name as Products, ROW_NUMBER() over (partition by cl.kore_contractidname order by pr.kore_categoryidname, pr.kore_name, cl.kore_propertyidname) as Inventory_row, cl.kore_propertyidname as Inventory, cl.KORE_Quantity as Qty, kore.NumberToWords(cl.KORE_Quantity) Qty_word, cl.kore_notes as LineRemarks, p.kore_remarks as Inventory_Legal_Remarks, rc.kore_remarks as Ratecard_Legal_Remarks Into #Inventories from KORE_contractline cl inner join KORE_property p on p.kore_propertyid = cl.kore_propertyid and p.statecode = 0 inner join kore_product pr on p.kore_productid = pr.kore_productid inner join kore_ratecard rc on rc.kore_ratecardid = cl.kore_ratecardid where cl.statecode = 0 and cl.kore_contractid = @id and pr.kore_categoryidname != 'Miscellaneous' and cl.kore_propertyidname != 'Unallocated' --select * from #Inventories --testing /*========================SELECT STATEMENT===========================*/ SELECT --General Format(@today,'MMMM d, yyyy') as Today ,Format(@today,@DateFormat) as TodayShort ,Format(@today,'yyyy') as CurrentYear ,Format(dateadd(YEAR,1, @today),'yyyy') as NextYear ,(select kore_year from kore_season where kore_default = 1) as CurrentSeason --Deal ,c.kore_name AS DealNumber ,c.kore_description As ContractDescription ,c.kore_contracttypeidname AS DealType ,c.statuscodename AS DealStatus ,c.kore_approvalstatusname AS ApprovalStatus ,@SaleType AS SaleType ,Format(c.kore_startcontractdate,'MMMM d, yyyy') AS StartContractDate ,Format(c.kore_startcontractdate,'MMMM d') AS ContractStartDateOnly ,Format(c.kore_endcontractdate,'MMMM d, yyyy') AS EndContractDate ,Format(c.kore_startcontractdate,@DateFormat) AS StartContractDateShort ,Format(c.kore_endcontractdate,@DateFormat) AS EndContractDateShort ,CONCAT(c.kore_startcontractdate, ' - ', c.kore_endcontractdate) AS DateStarttoEnd ,c.kore_startseasonidname as StartSeason ,c.kore_endseasonidname as EndSeason ,CONCAT(c.kore_startseasonidname, ' - ', c.kore_endseasonidname) AS SeasonStarttoEnd ,left(c.kore_startseasonidname, 4) as StartYear ,right(c.kore_endseasonidname, 2) as EndYear ,(select concat( kore.NumberToWords(count(distinct cs.kore_seasonid)), ' (', count(distinct cs.kore_seasonid), ') ' ) from kore_contractseason cs where cs.statecode = 0 and cs.kore_contractid = c.kore_contractid and cs.kore_roundid is null) NumberOfSeasons , c.KORE_Obligations as Obligations , c.kore_otherobligations as OtherObligations , c.kore_legaltransfernotes as LegalTransferNotes ,(select ExclusivityOptionList from #Exclusivities) as ExclusivityOptionList ,(select ExlusivityRemarkList from #Exclusivities) as ExlusivityRemarkList ,(select ExlusivityIndustryList from #Exclusivities) AS ExclusicityIndustryList ,(select ExlusivityRegionalList from #Exclusivities) AS ExlusivityRegionalList ,(select dbo.DistinctConcat(concat('Option Type: ', o.kore_name, 'Deadline Date: ',co.kore_deadline, 'Option Remarks: ', co.kore_remarks), char(13)+char(10), 1 ) from kore_contractoptionbase co left join kore_optiontypebase o on co.kore_optiontypeid = o.kore_optiontypeid where kore_contractid = @id) as OptionLinesList --Account ,a.Name AS AccountName ,a.kore_legalname AS AccountLegalName ,isnull(a.kore_legalname, a.Name) AS AccountLegalNameorAccountName ,c.kore_legalstatusname AccountLegalStatus ,si.Name AS AccountIndustry ,Isnull(a.MailingStreet, '') AS AccountAddress1 ,Isnull(a.MailingStreet2, '') AS AccountAddress2 ,Isnull(a.city, '') AS AccountCity ,Isnull(a.state, '') AS AccountState ,Isnull(a.Country, '') AS AccountCountry ,Isnull(a.zip, '') AS Accountzip ,Isnull(a.fax, '') AS AccountFax ,Rtrim(Isnull(a.city, '') + ', ' + Isnull(a.state, '') + ', ' + Isnull(a.zip, '')) AS AccountCityStateZIP ,RTRIM( case when isnull(a.MailingStreet, '') <> '' then a.MailingStreet else '' end + case when isnull(a.MailingStreet2, '') <> '' then ', ' + a.MailingStreet2 else '' end) AccountAddressesOnOneRow --Salesperson / Coordinator ,isnull(stuff((SELECT ', '+ Salesperson FROM (select su.Name Salesperson from kore_rosterrelationship rr inner join SyncedUsers su on su.SF_UserId = rr.sf_userid where rr.statecode = 0 and isnull(rr.kore_type, 1) = 1 and rr.kore_contractid = c.kore_contractid group by su.Name ) p2 ORDER BY Salesperson FOR XML PATH(''), TYPE).value('.', 'varchar(max)'),1,1,''), '') AS Salesperson ,isnull(stuff((SELECT ', '+ SalespersonTitle FROM (select su.Title SalespersonTitle from kore_rosterrelationship rr inner join SyncedUsers su on su.SF_UserId = rr.sf_userid where rr.statecode = 0 and isnull(rr.kore_type, 1) = 1 and rr.kore_contractid = c.kore_contractid group by su.Title ) p2 ORDER BY SalespersonTitle FOR XML PATH(''), TYPE).value('.', 'varchar(max)'),1,1,''), '') AS SalespersonTitle ,isnull(stuff((SELECT ', '+ SalespersonPhone FROM (select coalesce(su.MainPhone, su.MobilePhone, '') SalespersonPhone from kore_rosterrelationship rr inner join SyncedUsers su on su.SF_UserId = rr.sf_userid where rr.statecode = 0 and isnull(rr.kore_type, 1) = 1 and rr.kore_contractid = c.kore_contractid group by coalesce(su.MainPhone, su.MobilePhone, '') ) p2 ORDER BY SalespersonPhone FOR XML PATH(''), TYPE).value('.', 'varchar(max)'),1,1,''), '') AS SalespersonPhone ,isnull(stuff((SELECT ', '+ SalespersonEmail FROM (select coalesce(su.Email, '') SalespersonEmail from kore_rosterrelationship rr inner join SyncedUsers su on su.SF_UserId = rr.sf_userid where rr.statecode = 0 and isnull(rr.kore_type, 1) = 1 and rr.kore_contractid = c.kore_contractid group by coalesce(su.Email, '') ) p2 ORDER BY SalespersonEmail FOR XML PATH(''), TYPE).value('.', 'varchar(max)'),1,1,''), '') AS SalespersonEmail ,co.Name Coordinator --Primary Contact ,Rtrim(Isnull(pc.firstname, '') + ' ' + Isnull(pc.lastname, '')) AS PrimaryContact ,RTRIM( case when isnull(pc.addressline1, '') <> '' then pc.addressline1 else '' end + case when isnull(pc.addressline2, '') <> '' then ', ' + pc.addressline2 else '' end) PrimaryAddressesOnOneRow ,Rtrim(Isnull(pc.city, '') + ', ' + Isnull(pc.state, '') + ', ' + Isnull(pc.zip, '')) AS PrimaryContactCityStateZIP ,coalesce(pc.workphone, pc.mobilephone, pc.homephone, '')AS PrimaryContactPhone ,Isnull(pc.email, '') AS PrimaryContactEmail ,isnull(pc.jobtitle,'') as PrimaryContactJobTitle --Billing Contact ,ISNULL(bc.KORE_PrimaryAccountNumber, bc.KORE_AccountNumbers) AS FinanceAccountNumber_ArchticsID ,Rtrim(Isnull(bc.firstname, '') + ' ' + Isnull(bc.lastname, '')) AS BillingContactName ,RTRIM( case when isnull(bc.addressline1, '') <> '' then bc.addressline1 else '' end + case when isnull(bc.addressline2, '') <> '' then ', ' + bc.addressline2 else '' end) BillingAddressesOnOneRow ,Rtrim(Isnull(bc.city, '') + ', ' + Isnull(bc.state, '') + ', ' + Isnull(bc.zip, '')) AS BillingContactCityStateZIP ,coalesce(bc.workphone, bc.mobilephone, bc.homephone, '')AS BillingContactPhone ,Isnull(bc.email, '') AS BillingContactEmail ,isnull(bc.jobtitle,'') as BillingContactJobTitle --Additional Contacts--- ,Isnull(Rtrim(Isnull(bc.firstname, '') + ' ' + Isnull(bc.lastname, '')), Rtrim(Isnull(pc.firstname, '') + ' ' + Isnull(pc.lastname, ''))) Billing_Primary_Contact ,isnull(bc.jobtitle,pc.jobtitle) as Billing_Primary_ContactJobTitle ,isnull( STUFF( ISNULL(', ' + CASE WHEN rtrim(bc.addressline1) = '' THEN NULL ELSE rtrim(bc.addressline1) END, '') + ISNULL(', ' + CASE WHEN rtrim(bc.addressline2)= '' THEN NULL ELSE rtrim(bc.addressline2) END, ''),1,1,''), STUFF( ISNULL(', ' + CASE WHEN rtrim(pc.addressline1) = '' THEN NULL ELSE rtrim(pc.addressline1) END, '') + ISNULL(', ' + CASE WHEN rtrim(pc.addressline2)= '' THEN NULL ELSE rtrim(pc.addressline2) END, ''),1,1,'')) Billing_PrimaryAddressesOnOneRow ,Isnull( STUFF( ISNULL(', ' + CASE WHEN rtrim(bc.City) = '' THEN NULL ELSE rtrim(bc.City) END, '') + ISNULL(', ' + CASE WHEN rtrim(bc.[state]) = '' THEN NULL ELSE rtrim(bc.[state]) END, '') + ISNULL(', ' + CASE WHEN rtrim(bc.zip) = '' THEN NULL ELSE rtrim(bc.zip) END, ''),1,1,''), STUFF( ISNULL(', ' + CASE WHEN rtrim(pc.City) = '' THEN NULL ELSE rtrim(pc.City) END, '') + ISNULL(', ' + CASE WHEN rtrim(pc.[state]) = '' THEN NULL ELSE rtrim(pc.[state]) END, '') + ISNULL(', ' + CASE WHEN rtrim(pc.zip) = '' THEN NULL ELSE rtrim(pc.zip) END, ''),1,1,'')) AS Billing_PrimaryContactCityStateZIP --Total Revenues ,Format(Isnull((select sum(Gross) * @ExchangeRate from #ContractSeason where RoundName = 'Regular Season'),0), @CurrencySymbol + '#,##0;(' + @CurrencySymbol + '#,##0)') AS TotalContractGross ,kore.NumberToWords(Isnull((select sum(Gross) * @ExchangeRate from #ContractSeason where RoundName = 'Regular Season'),0) ) AS TotalContractGross_words ,Format(Isnull((select sum(Gross) * @ExchangeRate from #ContractSeason where RoundName <> 'Regular Season'),0), @CurrencySymbol + '#,##0;(' + @CurrencySymbol + '#,##0)') AS TotalPostSeasonGross ,kore.NumberToWords(Isnull((select sum(Gross) * @ExchangeRate from #ContractSeason where RoundName <> 'Regular Season'),0) ) AS TotalPostSeasonGross_words ,Format(Isnull((select sum(isnull(Gross, 0) - isnull(Trade, 0)) * @ExchangeRate from #ContractSeason where RoundName = 'Regular Season'),0), @CurrencySymbol + '#,##0;(' + @CurrencySymbol + '#,##0)') AS TotalContractCash ,Format(Isnull((select sum(Net) * @ExchangeRate from #ContractSeason where RoundName = 'Regular Season'),0), @CurrencySymbol + '#,##0;(' + @CurrencySymbol + '#,##0)') AS TotalContractNet ,Format(Isnull((select sum(isnull(Gross, 0) - isnull(Trade, 0)) * @ExchangeRate from #ContractSeason where RoundName = 'Regular Season'),0), @CurrencySymbol + '#,##0;(' + @CurrencySymbol + '#,##0)') AS Client_TotalContractNet --adjust as needed ,Format(Isnull((select sum(Trade) * @ExchangeRate from #ContractSeason where RoundName = 'Regular Season'),0), @CurrencySymbol + '#,##0.00;(' + @CurrencySymbol + '#,##0)') AS TotalContractTrade ,Format(Isnull((select sum(Barter1) * @ExchangeRate from #ContractSeason where RoundName = 'Regular Season'),0), @CurrencySymbol + '#,##0;(' + @CurrencySymbol + '#,##0)') AS TotalContractBarter1 ,Format(Isnull((select sum(Commission) * @ExchangeRate from #ContractSeason where RoundName = 'Regular Season'),0), @CurrencySymbol + '#,##0;(' + @CurrencySymbol + '#,##0)') AS TotalContractCommission --First Season Revenues ,case when c.kore_percentageescalation>=1 then CONCAT( format(c.kore_percentageescalation, 'n2'), '%') else format(isnull(c.kore_percentageescalation, 0), 'P') end as EscalationRate ,isnull((select seasonname from #ContractSeason where RoundName = 'Regular Season' and SeasonRow=1),'') AS Season1Name ,isnull((select BarterNotes from #ContractSeason where RoundName = 'Regular Season' and SeasonRow=1),'') AS Season1ContraNote ,Format(Isnull((select Gross * @ExchangeRate from #ContractSeason where RoundName = 'Regular Season' and SeasonRow=1),0), @CurrencySymbol + '#,##0;(' + @CurrencySymbol + '#,##0)') AS Season1Gross ,Format(Isnull((select (isnull(Gross, 0) - isnull(Trade, 0)) * @ExchangeRate from #ContractSeason where RoundName = 'Regular Season' and SeasonRow=1),0), @CurrencySymbol + '#,##0;(' + @CurrencySymbol + '#,##0)') AS Season1Cash ,Format(Isnull((select Net * @ExchangeRate from #ContractSeason where RoundName = 'Regular Season' and SeasonRow=1),0), @CurrencySymbol + '#,##0;(' + @CurrencySymbol + '#,##0)') AS Season1Net ,Format(Isnull((select Trade * @ExchangeRate from #ContractSeason where RoundName = 'Regular Season' and SeasonRow=1),0), @CurrencySymbol + '#,##0;(' + @CurrencySymbol + '#,##0)') AS Season1Trade ,Format(Isnull((select Commission * @ExchangeRate from #ContractSeason where RoundName = 'Regular Season' and SeasonRow=1),0), @CurrencySymbol + '#,##0;(' + @CurrencySymbol + '#,##0)') AS Season1Commission --Consolidated list for season information ,(select dbo.DistinctConcat(season_timelist, CHAR(13) + CHAR(10), 1) from #Concat_SeasonList_Temp ) Season_timelist ,(select dbo.DistinctConcat(season_revenuelist, CHAR(13) + CHAR(10), 1) from #Concat_SeasonList_Temp) Season_revenuelist ,(select dbo.DistinctConcat(concat(seasonname + ': ', Format(Isnull(Gross,0), @CurrencySymbol + '#,##0;(' + @CurrencySymbol + '#,##0)')), '; ', 1) from #ContractSeason) as Season_time_revenuelist --Total Billings Info ,Format(Isnull((select SUM(AmountInvoiced) * @ExchangeRate from #Billings where RoundName = 'Regular Season'),0), @CurrencySymbol + '#,##0;(' + @CurrencySymbol + '#,##0)') AS TotalInvoicedAmt ,Format(Isnull((select SUM(TaxAmount) * @ExchangeRate from #Billings where RoundName = 'Regular Season'),0), @CurrencySymbol + '#,##0;(' + @CurrencySymbol + '#,##0)') AS TotalTaxAmt ,Format(Isnull((select SUM(AmountInvoiced+TaxAmount) * @ExchangeRate from #Billings where RoundName = 'Regular Season'),0), @CurrencySymbol + '#,##0;(' + @CurrencySymbol + '#,##0)') AS Client_TotalInvoiceAndTaxAmt --First Billings Info ,Format(Isnull((select AmountInvoiced * @ExchangeRate from #Billings where RoundName = 'Regular Season' and PaymentRow=1),0), @CurrencySymbol + '#,##0;(' + @CurrencySymbol + '#,##0)') AS Payment1AmountInvoiced ,Format(Isnull((select b.EstimatedInvoiceDate from #Billings b where RoundName = 'Regular Season' and PaymentRow=1),0),@DateFormat) AS Payment1EstimatedInvoiceDateShort ,Format(Isnull((select b.DueDate from #Billings b where RoundName = 'Regular Season' and PaymentRow=1),0),@DateFormat) AS Payment1DueDateShort ,Format(Isnull((select b.DueDate from #Billings b where RoundName = 'Regular Season' and PaymentRow=1),0),'d MMMM, yyyy') AS Payment1DueDate --Consolidated list for billing info ,(select dbo.DistinctConcat(concat( Format(Isnull(AmountInvoiced,0), @CurrencySymbol + '#,##0;(' + @CurrencySymbol + '#,##0)'), ' on or before ' ,Format(DueDate,'MMMM d, yyyy') ), '; ', 0) from #Billings) as Billing_Amount_Date_list --Bill Info ,(select top 1 Notes from #Bill) BillNotes ,stuff(( SELECT ' and '+NAME FROM (select '$' + isnull(convert(varchar,convert(Money, bl.kore_estimatedamount),1),0) + ' on or before ' + DATENAME(MM, bl.KORE_EstimatedInvoiceDate) + ' ' + Convert(nvarchar(10),Convert(int,(DATEPART(dd,bl.kore_estimatedinvoicedate)))) + ', ' + Convert(nvarchar(10),Convert(int,(DATEPART(yy,bl.kore_estimatedinvoicedate))))NAME ,bl.kore_estimatedinvoicedate from Kore_bill bl where bl.kore_contractid = c.kore_contractid ) p2 ORDER BY p2.kore_estimatedinvoicedate FOR XML PATH(''), TYPE).value('.', 'varchar(max)'),1,4,'') AS AmountBeforeBillDateList --Consolidated inventory list ,(select dbo.DistinctConcat(i.Categories, CHAR(13) + CHAR(10), 1) from #Inventories i) Categorylist ,(select dbo.DistinctConcat(i.Products, CHAR(13) + CHAR(10), 1) from #Inventories i) Productlist ,(select dbo.DistinctConcat(concat( Inventory_row, '. ', i.Inventory, ' - ', i.Qty, ' (', i.Qty_word, ') ',case when i.Ratecard_Legal_Remarks is null then '' else CHAR(13) + CHAR(10) + CHAR(149) + ' '+ CHAR(9) + i.Ratecard_Legal_Remarks end ), CHAR(13) + CHAR(10), 1) from #Inventories i) Inventorylist --Agency ,isnull(agency.Name, '') Agency ,RTRIM( case when isnull(agency.MailingStreet, '') <> '' then agency.MailingStreet else '' end + case when isnull(agency.MailingStreet2, '') <> '' then ', ' + agency.MailingStreet2 else '' end) AgencyAddressesOnOneRow ,Rtrim(Isnull(agency.city, '') + ', ' + Isnull(agency.state, '') + ', ' + Isnull(agency.zip, '')) AS AgencyCityStateZIP ,Rtrim(Isnull(agencypc.firstname, '') + ' ' + Isnull(agencypc.lastname, '')) AS AgencyPrimaryContact ,RTRIM( case when isnull(agencypc.addressline1, '') <> '' then agencypc.addressline1 else '' end + case when isnull(agencypc.addressline2, '') <> '' then ', ' + agencypc.addressline2 else '' end) AgencyPrimaryContactAddressesOnOneRow ,Rtrim(Isnull(agencypc.city, '') + ', ' + Isnull(agencypc.state, '') + ', ' + Isnull(agencypc.zip, '')) AS AgencyPrimaryContactCityStateZIP ,Isnull(agencypc.workphone, '')AS AgencyPrimaryContactPhone ,Isnull(agencypc.email, '') AS AgencyPrimaryContactEmail ,isnull(agencypc.jobtitle,'') as AgencyPrimaryContactJobTitle FROM kore_contract c LEFT JOIN syncedcontacts bc ON CASE WHEN c.sf_billingcontactid = '' THEN NULL ELSE c.sf_billingcontactid END = bc.sf_contactid LEFT JOIN syncedcontacts pc ON CASE WHEN c.sf_primarycontactid = '' THEN NULL ELSE c.sf_primarycontactid END = pc.sf_contactid LEFT JOIN syncedaccounts a ON c.sf_accountid = a.sf_accountid left join SyncedAccounts_Extension ae on ae.sf_accountid = a.sf_accountid left join syncedIndustries si on si.SF_IndustryId = a.KOREIndustry LEFT JOIN syncedaccounts agency ON agency.sf_accountid = a.PrimaryAgency LEFT JOIN syncedcontacts agencypc ON CASE WHEN agency.PrimaryContact = '' THEN NULL ELSE agency.PrimaryContact END = agencypc.sf_contactid left join SyncedUsers co on co.SF_UserId = c.sf_coordinatorid WHERE c.kore_contractid = @Id AND c.statecode = 0