create or replace view v_weeklyvaluerental as
select v.line, v.billingmethod, v.customernumber, v.name, v.productcode, v.fmcode, v.prod_desc,
sum(v.quantity) quantity, v.changesperweek, v.price, v.discount 
from (select 1 line, plpc.billingmethod, c.customernumber, c.name, p.code productcode, fm.code fmcode, (select pd.description from product_desc pd where pd.product_id=p.product_id and pd.language_id=l.language_id) prod_desc ,
      sdl.fixedrentquantity quantity, 0 changesperweek, pack_price.Rent_Price(c.customer_id, p.product_id, sd.sizedefinition_id, fm.finishingmethod_id, sysdate) price,
      pack_price.Rent_Discount(c.customer_id, p.product_id, sd.sizedefinition_id, fm.finishingmethod_id, sysdate) discount
      from productlistpercustomer plpc join customer c on plpc.customer_id = c.customer_id
      join product p on plpc.product_id = p.product_id
      join finishingmethod fm on fm.finishingmethod_id = plpc.finishingmethod_id
      join standarddeliveryline sdl on plpc.productlistpercustomer_id = sdl.productlistpercustomer_id
      join sizedefinition sd on sd.sizedefinition_id = plpc.sizedefinition_id
      cross join languages l
      where plpc.dateinactive > sysdate
      and p.typeofmerchandise = 1
      and plpc.billingmethod in (1) -- changes from (1,3) to (1) to see - ref PBR
      and plpc.changesperweek = 'N'
      and sdl.fixedrentquantity <> 0
      and l.code = 'EN_GB'
      and sdl.dateinactive > sysdate) v
group by v.line, v.billingmethod, v.customernumber, v.name, v.productcode, v.fmcode, v.prod_desc, v.changesperweek,v.price,v.discount
union all
select v.line, v.billingmethod, v.customernumber, v.name, v.productcode, v.fmcode, v.prod_desc,
(select sum(rqa.quantity) from rentalqtyadjustment rqa join productlistperdistribpoint plpd on rqa.productlistperdistribpoint_id=plpd.productlistperdistribpoint_id
                                          join distributionpoint dp on plpd.distributionpoint_id=dp.distributionpoint_id
                                          join department dep on dep.department_id=dp.department_id
                                          where dep.customer_id=v.customer_id
                                          and plpd.product_id=v.product_id
                                          and plpd.finishingmethod_id=v.finishingmethod_id
) quantity, v.changesperweek, v.price,v.discount
from (select 2 line,plpc.billingmethod,  c.customernumber, c.name, c.customer_id, p.product_id, p.code productcode, fm.finishingmethod_id, fm.code fmcode,  (select pd.description from product_desc pd where pd.product_id=p.product_id and pd.language_id=l.language_id) prod_desc ,
     0 changesperweek, pack_price.Rent_Price(c.customer_id, p.product_id, sd.sizedefinition_id, fm.finishingmethod_id, sysdate) price,pack_price.Rent_Discount(c.customer_id, p.product_id, sd.sizedefinition_id, fm.finishingmethod_id, sysdate) discount
    from productlistpercustomer plpc join customer c on plpc.customer_id = c.customer_id
    join product p on plpc.product_id = p.product_id
    join finishingmethod fm on fm.finishingmethod_id = plpc.finishingmethod_id
    join standarddeliveryline sdl on plpc.productlistpercustomer_id = sdl.productlistpercustomer_id
    join sizedefinition sd on sd.sizedefinition_id = plpc.sizedefinition_id
    cross join languages l
    where plpc.dateinactive > sysdate
    and sdl.dateinactive > sysdate
    and p.typeofmerchandise = 1
    and plpc.billingmethod in (3) --changes from (1,3) to (3) to see - ref PBR
    and plpc.changesperweek = 'N'
    --and sdl.fixedrentquantity = 0  --line taken out ref PBR
    and l.code = 'EN_GB') v
group by v.line, v.productcode, v.customernumber, v.name, v.productcode, v.product_id, v.customer_id, v.finishingmethod_id, v.billingmethod,v.fmcode, v.prod_desc, v.changesperweek, v.price,v.discount
union all
select v.line, v.billingmethod , v.customernumber, v.name, v.productcode, v.fmcode, v.prod_desc,
count(distinct(v.wearerinventory_id)), v.changesperweek, v.price, v.discount
from (select 3 line, plpc.billingmethod, c.customernumber, c.name, p.code productcode, fm.code fmcode, (select pd.description from product_desc pd where pd.product_id=p.product_id and pd.language_id=l.language_id) prod_desc ,
      wi.wearerinventory_id, wi.changesperweek, pack_price.Rent_Price(c.customer_id, p.product_id, sd.sizedefinition_id, fm.finishingmethod_id, sysdate, wi.changesperweek) price
      , pack_price.Rent_Discount(c.customer_id, p.product_id, sd.sizedefinition_id, fm.finishingmethod_id, sysdate) discount
      from wearerinventory wi
      join weareremployment we on wi.weareremployment_id = we.weareremployment_id
      join wearer w on we.wearer_id = w.wearer_id
      join customer c on w.customer_id = c.customer_id
      join product p on p.product_id = wi.product_id
      join productlistpercustomer plpc on (wi.product_id = plpc.product_id and wi.finishingmethod_id = plpc.finishingmethod_id and c.customer_id = plpc.customer_id)
      join sizedefinition sd on plpc.sizedefinition_id = sd.sizedefinition_id
      join finishingmethod fm on plpc.finishingmethod_id = fm.finishingmethod_id
      cross join languages l
      where plpc.dateinactive > sysdate
      and wi.dateinactive > sysdate  -- added wearer inventory inactive date check
      and plpc.billingmethod = 1
      and p.typeofmerchandise = 2
      and plpc.changesperweek = 'Y'
      and l.code = 'EN_GB') v
group by v.line, v.billingmethod, v.customernumber, v.name, v.productcode, v.fmcode, v.prod_desc, v.changesperweek, v.price, v.discount
union all
select v.line, v.billingmethod, v.customernumber,v.name,v.productcode,v.fmcode,  v.prod_desc,
count(distinct v.uniqueitem_id) quantity, v.changesperweek , v.price, v.discount
from (select 4 line, plpc.billingmethod, c.customernumber, c.name, p.code productcode,fm.code fmcode,  (select pd.description from product_desc pd where pd.product_id=p.product_id and pd.language_id=l.language_id) prod_desc,
      ui.uniqueitem_id, 0 changesperweek, pack_price.Rent_Price(c.customer_id, p.product_id, sd.sizedefinition_id, fm.finishingmethod_id, sysdate) price,
      pack_price.Rent_Discount(c.customer_id, p.product_id, sd.sizedefinition_id, fm.finishingmethod_id, sysdate) discount
      from uniqueitem ui join uniqueitemnonpool uinp on ui.uniqueitem_id = uinp.uniqueitem_id
      join status s on ui.status_id = s.status_id
      join weareremployment we on uinp.weareremployment_id = we.weareremployment_id
      join wearerinventory wi on (wi.weareremployment_id = we.weareremployment_id and wi.sizedefinition_id = ui.sizedefinition_id and wi.finishingmethod_id=ui.finishingmethod_id)
      join wearer w on we.wearer_id = w.wearer_id
      join customer c on c.customer_id = w.customer_id
      join productlistpercustomer plpc on (wi.product_id = plpc.product_id and wi.finishingmethod_id = plpc.finishingmethod_id and plpc.customer_id=c.customer_id)
      join product p on p.product_id = ui.product_id
      join finishingmethod fm on fm.finishingmethod_id = plpc.finishingmethod_id
      join sizedefinition sd on plpc.sizedefinition_id = sd.sizedefinition_id
      cross join languages l
      where
      plpc.dateinactive > sysdate
      and wi.dateinactive > sysdate  -- added wearer inventory inactive date check
      and plpc.billingmethod = 3
      and ui.status_id = 26
      and l.code = 'EN_GB') v
group by v.line, v.billingmethod, v.customernumber, v.name, v.productcode, v.fmcode, v.prod_desc, v.changesperweek, v.price, v.discount
;
/