Embedded Files
If you are creating a Scheduled Job for this report, empty reports are a known issue that will not be solved. However, the workaround is to use Iteration SQL to prevent the execution of the job. It requires this SQL statement:
DECLARE @StartArrival DATETIME = CAST(DATEADD(d, -15, GETUTCDATE()) AS date);
DECLARE @FinishArrival DATETIME = CAST(DATEADD(m, 12, GETUTCDATE()) AS date);
WITH PartyList AS (
SELECT PartyId FROM Party WHERE PartyId = @PartyId
UNION SELECT FromPartyId FROM PartyRelation WHERE ToPartyId = @PartyId
UNION SELECT ToPartyId FROM PartyRelation WHERE FromPartyId = @PartyId
)
SELECT count(*)
FROM dbo.Agreement A
JOIN dbo.AgreementType AT ON AT.AgreementTypeId = A.AgreementTypeId AND AT.Mnemonic IN ('SORDER','SCONTRACT')
JOIN dbo.AgreementItem AI ON AI.AgreementId = A.AgreementId
JOIN dbo.ViewItem IT ON IT.ItemId = AI.ItemId AND IT.LocaleCode = 'en-US'
JOIN dbo.Party P ON P.PartyId = A.Buyer_PartyId
JOIN dbo.AgreementItemAssignment AIA ON AIA.AgreementItemId = AI.AgreementItemId
JOIN dbo.Shipment S ON S.ShipmentId = AIA.ShipmentId
JOIN dbo.Booking B ON B.ShipmentId = S.ShipmentId
WHERE B.PlannedArrival BETWEEN @StartArrival AND @FinishArrival + 0.99999
AND (A.Buyer_PartyId IN (SELECT PartyId FROM PartyList)
OR A.ShipTo_PartyId IN (SELECT PartyId FROM PartyList))
AND (S.Shipper_PartyId = A.Seller_PartyId
OR S.Shipper_PartyId IN (SELECT PartyId FROM PartyList))
HAVING COUNT(*) > 0
Another way to do this is to create a single scheduled job which sends the report to every party that has shipments which match the default criteria. This gives you @PartyId, @SellerName, @SellerEmail, @SellerContactName, @SellerContactEmail, @BuyerName, @BuyerEmail, @BuyerContactName, @BuyerContactEmail, @BuyerRelatedEmails
declare
@StartDate date = cast(dateadd(d, -15, getutcdate()) as date)
, @EndDate date = cast(dateadd(m, 12, getutcdate()) as date);
with bookings as ( -- these are the parties with active shipment advice
select distinct
A.Seller_PartyId,
A.Buyer_PartyId,
PS.Name as SellerName,
PS.Email as SellerEmail,
PSC.Name as SellerContactName,
PSC.Email as SellerContactEmail,
PB.Name as BuyerName,
PB.Email as BuyerEmail,
PBC.Name as BuyerContactName,
PBC.Email as BuyerContactEmail
from dbo.Agreement A
join dbo.AgreementType at on at.AgreementTypeId = A.AgreementTypeId and at.Mnemonic in ('SORDER')
join dbo.Party PB on PB.PartyId = A.Buyer_PartyId
join dbo.Party PS on PS.PartyId = A.Seller_PartyId
left join dbo.Party PSC on PSC.PartyId = A.SellerContact_PartyId and PSC.IsTransactionOptedIn = 1
left join dbo.Party PBC on PBC.PartyId = A.BuyerContact_PartyId and PBC.IsTransactionOptedIn = 1
join dbo.AgreementItem AI on AI.AgreementId = A.AgreementId
join dbo.AgreementItemAssignment AIA on AIA.AgreementItemId = AI.AgreementItemId
join dbo.Shipment S on S.ShipmentId = AIA.ShipmentId
join dbo.Booking B on B.ShipmentId = S.ShipmentId
where B.PlannedArrival between @StartDate and @EndDate
),
emails as -- these are related parties
(
select distinct
b.*, trim(PP.Email) as Email
from bookings b
left join dbo.PartyRelation pr on pr.ToPartyId in (
case when pr.FromPartyId = b.Buyer_PartyId then pr.ToPartyId
when pr.ToPartyId = b.Buyer_PartyId then pr.FromPartyId
end
)
left join dbo.Party PP on PP.PartyId = pr.ToPartyId
where PP.Email is not null and trim(PP.Email) != '' and PP.IsTransactionOptedIn = 1
)
select -- combining them into a single list
P.Name as BuyerName,
b.Buyer_PartyId as PartyId,
b.SellerName,
b.SellerEmail,
b.SellerContactName,
b.SellerContactEmail,
b.BuyerName,
b.BuyerEmail,
b.BuyerContactName,
b.BuyerContactEmail,
STRING_AGG(e.Email, ',') as BuyerRelatedEmails
from bookings b
join dbo.Party P on P.Partyid = b.Buyer_PartyId
left join emails e on e.Buyer_PartyId = b.Buyer_PartyId
where
(b.BuyerEmail is not null and trim(b.BuyerEmail) != '') or
(b.BuyerContactEmail is not null and trim(b.BuyerContactEmail) != '')
group by
P.Name,
b.Buyer_PartyId,
b.Seller_PartyId,
b.SellerName,
b.SellerEmail,
b.SellerContactName,
b.SellerContactEmail,
b.BuyerName,
b.BuyerEmail,
b.BuyerContactName,
b.BuyerContactEmail
Report abuse