create or replace view v_cleanroombatchcard_cont as
select 1 line, cbc.cleanroombatch_id, languages.code languagecode, parent_product.code parent_code, dp.distributionpointnumber, p.product_id, ui.sizedefinition_id, ui.finishingmethod_id,
(select pd.description from product_desc pd where pd.product_id=parent_product.product_id and pd.language_id=languages.language_id) parent_desc,
(select max(fc.finishingcode)
from productlistpercustomer plpc join finishingcode fc on
plpc.finishingcode_id= fc.finishingcode_id
where plpc.customer_id=c.customer_id
and plpc.product_id=p.product_id
and (plpc.sizedefinition_id=ui.sizedefinition_id or plpc.sizedefinition_id=(select sd.sizedefinition_id from sizedefinition sd where sd.code='*'))
and plpc.finishingmethod_id=ui.finishingmethod_id
) finishingcode,
 dp.description cp_desc, c.name customername, dp.distributionpoint_id, (select delp.sortaddress from deliverypoint delp where dp.deliverypoint_id=delp.deliverypoint_id) sortaddress,
case when (select count(1)
  from dialogflexfields dff, productlistpercustomer plpc2
 where dff.dialog_id =
       (select d.dialog_id
          from dialogs d
         where d.code = 'ProductListPerCustomer')
   and dff.entity_id = plpc2.productlistpercustomer_id
   and plpc2.customer_id=c.customer_id
   and plpc2.product_id=p.product_id
   and (plpc2.sizedefinition_id=ui.sizedefinition_id or plpc2.sizedefinition_id=(select sd.sizedefinition_id from sizedefinition sd where sd.code='*'))
   and plpc2.finishingmethod_id=ui.finishingmethod_id
) > 1 then 
          (select dff.stringvalue
          from dialogflexfields dff, productlistpercustomer plpc2
          where dff.dialog_id =
          (select d.dialog_id
           from dialogs d
           where d.code = 'ProductListPerCustomer')
           and dff.entity_id = plpc2.productlistpercustomer_id
           and plpc2.customer_id=c.customer_id
           and plpc2.product_id=p.product_id
           and (plpc2.sizedefinition_id=ui.sizedefinition_id or plpc2.sizedefinition_id=(select sd.sizedefinition_id from sizedefinition sd where sd.code='*'))
           and plpc2.finishingmethod_id=ui.finishingmethod_id
           and plpc2.billingmethod = 4
         ) 
  else (select dff.stringvalue
          from dialogflexfields dff, productlistpercustomer plpc2
          where dff.dialog_id =
          (select d.dialog_id
           from dialogs d
           where d.code = 'ProductListPerCustomer')
           and dff.entity_id = plpc2.productlistpercustomer_id
           and plpc2.customer_id=c.customer_id
           and plpc2.product_id=p.product_id
           and (plpc2.sizedefinition_id=ui.sizedefinition_id or plpc2.sizedefinition_id=(select sd.sizedefinition_id from sizedefinition sd where sd.code='*'))
           and plpc2.finishingmethod_id=ui.finishingmethod_id
         ) 
end remarkforpackinginstructions,
cbcust.deliverydate,cbcust.requiredsterilizationdate, COUNT(1) inscan, c.customer_id
from  cleanroombatchcontent cbc
left outer join cleanroombatchcustomer cbcust on cbcust.cleanroombatchcustomer_id=cbc.cleanroombatchcustomer_id
inner join distributionpoint dp on cbcust.distributionpoint_link_id=dp.distributionpoint_id
inner join department dep on dep.department_id=dp.department_id
inner join customer c on c.customer_id=dep.customer_id
inner join uniqueitem ui on cbc.uniqueitem_id =ui.uniqueitem_id
inner join product p on ui.product_id=p.product_id
inner join product parent_product on parent_product.product_id=p.parentproduct_id
cross join languages
where cbc.inscanrecord_id is not null
group by cbc.cleanroombatch_id, dp.deliverypoint_id,parent_product.code, dp.description, c.name, p.product_id, ui.sizedefinition_id, ui.finishingmethod_id, c.customer_id,
dp.distributionpoint_id, ui.product_id, ui.sizedefinition_id, ui.finishingmethod_id,cbcust.deliverydate,cbcust.requiredsterilizationdate, languages.code,languages.language_id, parent_product.product_id,  dp.distributionpointnumber
union all
select 2 line, cbc.cleanroombatch_id, languages.code languagecode, parent_product.code parent_code, dp.distributionpointnumber, p.product_id, ri.sizedefinition_link_id, ri.finishingmethod_id,
(select pd.description from product_desc pd where pd.product_id=parent_product.product_id and pd.language_id=languages.language_id) parent_desc,
 (select max(fc.finishingcode) from productlistpercustomer plpc join finishingcode fc on plpc.finishingcode_id= fc.finishingcode_id
  where plpc.customer_id=c.customer_id and plpc.product_id=p.product_id
  and (plpc.sizedefinition_id=ri.sizedefinition_link_id or plpc.sizedefinition_id=(select sd.sizedefinition_id from sizedefinition sd where sd.code='*'))
  and plpc.finishingmethod_id=ri.finishingmethod_id) finishingcode , dp.description cp_desc, c.name customername, dp.distributionpoint_id,(select delp.sortaddress from deliverypoint delp where dp.deliverypoint_id=delp.deliverypoint_id) sortaddress,
case when (select count(1)
  from dialogflexfields dff, productlistpercustomer plpc2
 where dff.dialog_id =
       (select d.dialog_id
          from dialogs d
         where d.code = 'ProductListPerCustomer')
   and dff.entity_id = plpc2.productlistpercustomer_id
   and plpc2.customer_id=c.customer_id
   and plpc2.product_id=ri.product_link_id
   and (plpc2.sizedefinition_id=ri.sizedefinition_link_id or plpc2.sizedefinition_id=(select sd.sizedefinition_id from sizedefinition sd where sd.code='*'))
   and plpc2.finishingmethod_id=ri.finishingmethod_id
) > 1 then 
          (select dff.stringvalue
          from dialogflexfields dff, productlistpercustomer plpc2
          where dff.dialog_id =
          (select d.dialog_id
           from dialogs d
           where d.code = 'ProductListPerCustomer')
           and dff.entity_id = plpc2.productlistpercustomer_id
           and plpc2.customer_id=c.customer_id
           and plpc2.product_id=p.product_id
           and (plpc2.sizedefinition_id=ri.sizedefinition_link_id or plpc2.sizedefinition_id=(select sd.sizedefinition_id from sizedefinition sd where sd.code='*'))
           and plpc2.finishingmethod_id=ri.finishingmethod_id
           and plpc2.billingmethod = 4
         ) 
  else (select dff.stringvalue
          from dialogflexfields dff, productlistpercustomer plpc2
          where dff.dialog_id =
          (select d.dialog_id
           from dialogs d
           where d.code = 'ProductListPerCustomer')
           and dff.entity_id = plpc2.productlistpercustomer_id
           and plpc2.customer_id=c.customer_id
           and plpc2.product_id=p.product_id
           and (plpc2.sizedefinition_id=ri.sizedefinition_link_id or plpc2.sizedefinition_id=(select sd.sizedefinition_id from sizedefinition sd where sd.code='*'))
           and plpc2.finishingmethod_id=ri.finishingmethod_id
         ) 
end remarkforpackinginstructions,
cbcust.deliverydate,cbcust.requiredsterilizationdate, COUNT(1) inscan, c.customer_id
from  cleanroombatchcontent cbc
left outer join cleanroombatchcustomer cbcust on cbcust.cleanroombatchcustomer_id=cbc.cleanroombatchcustomer_id
inner join distributionpoint dp on cbcust.distributionpoint_link_id=dp.distributionpoint_id
inner join department dep on dep.department_id=dp.department_id
inner join customer c on c.customer_id=dep.customer_id
inner join raggeditem ri on cbc.raggeditem_id =ri.raggeditem_id
inner join product p on ri.product_link_id=p.product_id
inner join product parent_product on parent_product.product_id=p.parentproduct_id
left outer join finishingcode fc on fc.finishingcode_id= p.finishingcode_id
cross join languages
where (select cb.batchstatus from cleanroombatch cb where cb.cleanroombatch_id=cbc.cleanroombatch_id)=2
group by cbc.cleanroombatch_id, parent_product.code, dp.description, c.name, p.product_id,
dp.distributionpoint_id,dp.deliverypoint_id, ri.product_link_id, ri.sizedefinition_link_id, ri.finishingmethod_id,cbcust.deliverydate,cbcust.requiredsterilizationdate, languages.code,languages.language_id, parent_product.product_id,  dp.distributionpointnumber, c.customer_id
union all
select 3 line, cbcust.cleanroombatch_id,languages.code languagecode, parent_product.code parent_code, dp.distributionpointnumber, p.product_id, cbcont.sizedefinition_link_id, cbcont.finishingmethod_id,
(select pd.description from product_desc pd where pd.product_id=parent_product.product_id and pd.language_id=languages.language_id) parent_desc,
  (select max(fc.finishingcode) from productlistpercustomer plpc join finishingcode fc on plpc.finishingcode_id= fc.finishingcode_id
   where plpc.customer_id=c.customer_id and plpc.product_id=p.product_id
   and (plpc.sizedefinition_id=cbcont.sizedefinition_link_id or plpc.sizedefinition_id=(select sd.sizedefinition_id from sizedefinition sd where sd.code='*'))
   and plpc.finishingmethod_id=cbcont.finishingmethod_id) finishingcode, dp.description cp_desc, c.name customername,dp.distributionpoint_id,(select delp.sortaddress from deliverypoint delp where dp.deliverypoint_id=delp.deliverypoint_id) sortaddress,
case when (select count(1)
  from dialogflexfields dff, productlistpercustomer plpc2
 where dff.dialog_id =
       (select d.dialog_id
          from dialogs d
         where d.code = 'ProductListPerCustomer')
   and dff.entity_id = plpc2.productlistpercustomer_id
   and plpc2.customer_id=c.customer_id
   and plpc2.product_id=cbcont.product_link_id
   and (plpc2.sizedefinition_id=cbcont.sizedefinition_link_id or plpc2.sizedefinition_id=(select sd.sizedefinition_id from sizedefinition sd where sd.code='*'))
   and plpc2.finishingmethod_id=cbcont.finishingmethod_id
) > 1 then 
          (select dff.stringvalue
          from dialogflexfields dff, productlistpercustomer plpc2
          where dff.dialog_id =
          (select d.dialog_id
           from dialogs d
           where d.code = 'ProductListPerCustomer')
           and dff.entity_id = plpc2.productlistpercustomer_id
           and plpc2.customer_id=c.customer_id
           and plpc2.product_id=p.product_id
           and (plpc2.sizedefinition_id=cbcont.sizedefinition_link_id or plpc2.sizedefinition_id=(select sd.sizedefinition_id from sizedefinition sd where sd.code='*'))
           and plpc2.finishingmethod_id=cbcont.finishingmethod_id
           and plpc2.billingmethod = 4
         ) 
  else (select dff.stringvalue
          from dialogflexfields dff, productlistpercustomer plpc2
          where dff.dialog_id =
          (select d.dialog_id
           from dialogs d
           where d.code = 'ProductListPerCustomer')
           and dff.entity_id = plpc2.productlistpercustomer_id
           and plpc2.customer_id=c.customer_id
           and plpc2.product_id=p.product_id
           and (plpc2.sizedefinition_id=cbcont.sizedefinition_link_id or plpc2.sizedefinition_id=(select sd.sizedefinition_id from sizedefinition sd where sd.code='*'))
           and plpc2.finishingmethod_id=cbcont.finishingmethod_id
         ) 
end remarkforpackinginstructions,
cbcust.deliverydate,cbcust.requiredsterilizationdate, SUM(cbcont.QUANTITY) inscan, c.customer_id
from CLEANROOMBATCHCOUNTCONTENT  cbcont
inner join cleanroombatchcustomer cbcust on cbcust.cleanroombatchcustomer_id=cbcont.cleanroombatchcustomer_id
inner join distributionpoint dp on cbcust.distributionpoint_link_id=dp.distributionpoint_id
inner join department dep on dep.department_id=dp.department_id
inner join customer c on c.customer_id=dep.customer_id
inner join product p on cbcont.product_link_id=p.product_id
inner join product parent_product on parent_product.product_id=p.parentproduct_id
left outer join finishingcode fc on fc.finishingcode_id= p.finishingcode_id
cross join languages
group by cbcust.cleanroombatch_id,dp.deliverypoint_id, parent_product.code , dp.description, c.name,
dp.distributionpoint_id, cbcont.product_link_id, cbcont.sizedefinition_link_id, cbcont.finishingmethod_id,cbcust.deliverydate,cbcust.requiredsterilizationdate, languages.code, languages.language_id, parent_product.product_id,  dp.distributionpointnumber
,p.product_id, cbcont.sizedefinition_link_id, cbcont.finishingmethod_id, c.customer_id;
/