CREATE VIEW V_CDN_STACKCHECK as
select
v.bucode,
v.customernumber,
v.customername,
v.clientnumber,
v.clientname,
v.primaryid,
v.lastoutnote,
v.prod_desc,
(select count(cdn.clientdeliverynotenumber)
from clientdeliverynote cdn  
join  clientdeliverynoteline cdl on cdn.clientdeliverynote_id = cdl.clientdeliverinote_id
join clientdelnotelinescan s on cdl.clientdeliverynoteline_id = s.outscanclientdelnoteline_id
where cdn.clientdeliverynotenumber = v.lastoutnote) id_qty
from(
select b.code bucode,
c.customernumber customernumber,
c.name customername,
k.clientnumber clientnumber,
k.name clientname,
u.primaryid primaryid,
(select max(cdn.clientdeliverynotenumber)
from clientdeliverynote cdn  
join  clientdeliverynoteline cdl on cdn.clientdeliverynote_id = cdl.clientdeliverinote_id
join clientdelnotelinescan s on cdl.clientdeliverynoteline_id = s.outscanclientdelnoteline_id
where s.uniqueresidentialitem_link_id = u.uniqueresidentialitem_id
and s.outscantimestamp > sysdate - 7) lastoutnote,
(select pd.description
from product_desc pd
join uniqueresidentialitemproduct up on up.product_id = pd.product_id
where  pd.language_id = 1
and up.uniqueresidentialitem_id = u.uniqueresidentialitem_id) prod_desc
from uniqueresidentialitem u
join businessunit b
on u.businessunit_id = b.businessunit_id
join client k
on u.client_id = k.client_id
join customer c
on c.customer_id = k.customer_id
where c.active = 'Y'
---- and b.code = 42
and length(u.primaryid) = 24) v
--WHERE v.primaryid='300ED89F3350015A00134218'
