promotion default query

promotion.sql

select ic.ItemCodeNo
, il.ItemLocalId
, i.ItemNo
, ru.RetailUnitNo
, pil.StartDate
, pil.EndDate
, pp.PromotionPOSNo
, pp.StartDateTime
, pp.EndDateTime
, promotionLineStatus.Code as "promotion line status code"
, pq.PromotionQty
, pa.PromotionAmt
, spil.ModifiedDateTime
, spil.PriceAmt
, promotionstatus.Code as "Promtoion status code"
, p.PromotionId
, pt.TypeNo
, p.CreatedDateTime
, p.ExternalPromotionNo
, p.IsOnPOS
from trio..ItemCode ic
inner join trio..ItemLocal il on (ic.ItemId = il.ItemId )
inner join trio..Item i on (il.ItemId = i.ItemId)
inner join trio..retailunit ru on il.retailunitid = ru.RetailUnitId
left join trio..PromotionItemLocal pil on il.ItemLocalId = pil.ItemLocalId
left join trio..Promotion p on pil.PromotionId = p.PromotionId
left join trio..PromotionPOS pp on p.PromotionId = pp.PromotionId
LEFT JOIN trio..PromotionQuantity pq on (p.PromotionId = pq.PromotionId)
left join trio..PromotionAmount pa on pa.PromotionId = p.PromotionId
left join trio..PromotionType pt on p.PromotionTypeId = pt.PromotionTypeId
left join trio..CommonCodeMaster promotionstatus on p.PromotionStatusCodeId = promotionstatus.CommonCodeMasterId
left join trio..CommonCodeMaster promotionLineStatus on pil.PromotionStatusCodeId = promotionLineStatus.CommonCodeMasterId
left join trio..SalesPriceItemLocal spil on (il.ItemLocalId = spil.ItemLocalId and spil.IsActive = 1)
where 
pil.startdate <= CAST(GETDATE() as date)
and pil.EndDate >= CAST(GETDATE() as date)
and promotionstatus.Code = 'J'
and ru.RetailUnitNo = '24344'
and ic.ItemCodeNo = '5705451006285'
--and p.ExternalPromotionNo = 1019080
order by pil.startdate