create or replace view v_uniquestockroomitempicklist as
select 1 linetype,
  qgOrder.Code qgOrderCode, s.code stockroomCode, s.name stockroomDescription, sl.rowcode rowCode
, sl.locationcode locationCode, sl.pickingsequence, wokli.codeuniquely UniqueYN, prd.code ProductCode
, (select product_desc.description from product_desc where product_desc.product_id = prd.product_id
  and product_desc.language_id = languages.language_id) productDescription
, sizedef.code SizedefinitionCode, qgReserved.code qgReservedCode
, (select modification.code from modification, workorderlinemodification where workorderlinemodification.modification_id = modification.modification_id
  and wokli.workorderline_id = workorderlinemodification.workorderline_id and workorderlinemodification.sequencenumber=1 ) modificationCodeA
, (select modification.code from modification, workorderlinemodification where workorderlinemodification.modification_id = modification.modification_id
  and wokli.workorderline_id = workorderlinemodification.workorderline_id and workorderlinemodification.sequencenumber=2 ) modificationCodeB
, (select modification.code from modification, workorderlinemodification where workorderlinemodification.modification_id = modification.modification_id
  and wokli.workorderline_id = workorderlinemodification.workorderline_id and workorderlinemodification.sequencenumber=3 ) modificationCodeC
, (sq.qtyunique + sq.qtyuniquewithmodifications) qtyunique, (srg.qtyuniquereserved+srg.qtyuniquewiithmodifreserved) qtyuniquereserved
, sq.qtynonunique , srg.qtynonuniquereserved ,  route.routenumber
, (select day_desc.code from day_desc where day_desc.day_id = route.drivingday_id
  and day_desc.language_id = languages.language_id) DayCode, wok.workordernumber
, wok.requestdate ReqstdDelivery, wokli.quantityordered WorkOrderquantityordered
,  nvl(WRE.QUANTITY, plo.qtypicked) WorkOrderLineReservationQTY
, cu.customernumber, cu.name customerName, we.wearernumber, we.wearersearchname wearerSearchname
, 0 distributionpointnumber,'' distributionpointdescription
, languages.code languageCode
, wok.origin
, prd.product_id
, pl.picklist_id
, pl.picklistnumber
, (select osd.code from sizedefinition_link osd where wokli.sizedefinition_link_id = osd.sizedefinition_link_id)  OrderedSizeCode
, pll.qtytopick qtytopick, pll.qtypicked qtypicked, pll.stocktype stocktype, sl.areacode StockLocAreaCode
from
  workorder wok
    left outer join route on wok.route_id = route.route_id
, weareremployment_link we
, workorderline wokli left outer join workorderlinereservation wre on  wokli.workorderline_id = wre.workorderline_id,
product_link prd, qualitygrade qgReserved, qualitygrade qgOrder
, stockroom s
, stockdefinitionperitem sdi,
  stockreservationpergrade srg
    --left outer
    join (
      stockquantity sq join stockroomlocation sl on sl.stockroomlocation_id = sq.stockroomlocation_id
          ) on srg.stockreservationpergrade_id = sq.stockreservationpergrade_id
, sizedefinition_link sizedef, customer_link cu, languages
, picklist pl, picklistline pll, picklistlineorigin plo, picklistlinelocation pllo
where wok.weareremployment_link_id = we.weareremployment_id
and cu.customer_id = wok.customer_link_id
and wok.workorder_id = wokli.workorder_id
and wokli.requestedgrade_id = qgOrder.Qualitygrade_Id
--and wokli.workorderline_id = wre.workorderline_id
--and wre.stockreservationpergrade_id = srg.stockreservationpergrade_id
and s.stockroom_id = sdi.stockroom_id
and sdi.stockdefinitionperitem_id = srg.stockdefinitionperitem_id
and srg.qualitygrade_id = qgReserved.qualitygrade_id
and sdi.product_id = prd.product_id
and sdi.sizedefinition_id = sizedef.sizedefinition_id
and wok.origin not in (7, 10, 11) --exclude credit notes, mounting and dismounting orders
and sl.locationtype_id in (select lt.locationtype_id from locationtype lt where lt.pickinglocation = 'Y')
and pl.picklist_id = pll.picklist_id and pll.picklistline_id = plo.picklistline_id and pll.picklistline_id=pllo.picklistline_id
and Upper(plo.baselinetable) = 'WORKORDERLINE' and plo.baselinetable_link_id = wokli.workorderline_id and plo.baselinetype = wok.origin
--and wre.stockreservationpergrade_id = pll.stockreservationpergrade_id and wre.stocktype = pll.stocktype
and decode(wokli.codeuniquely, 'N', 1, 'Y', 2, wre.stocktype  ) = pll.stocktype
and wok.processingstockroom_id=s.stockroom_id
and wokli.sizedefinition_link_id=sdi.sizedefinition_id
and wokli.product_link_id=sdi.product_id
and nvl(sdi.customer_id, 0) in (wok.customer_link_id, 0)
and sdi.customerowned = wok.customerowned
and wokli.requestedgrade_id = srg.qualitygrade_id
and pllo.stockroomlocation_id=sl.stockroomlocation_id
union all
select 2 linetype, qgOrder.Code qgOrderCode, s.code stockroomCode, s.name stockroomDescription, sl.rowcode rowCode
, sl.locationcode locationCode, sl.pickingsequence, wokli.codeuniquely UniqueYN, prd.code ProductCode
, (select product_desc.description from product_desc where product_desc.product_id = prd.product_id
  and product_desc.language_id = languages.language_id) productDescription
, sizedef.code SizedefinitionCode, qgReserved.code qgReservedCode
, (select modification.code from modification, workorderlinemodification where workorderlinemodification.modification_id = modification.modification_id
  and wokli.workorderline_id = workorderlinemodification.workorderline_id and workorderlinemodification.sequencenumber=1 ) modificationCodeA
, (select modification.code from modification, workorderlinemodification where workorderlinemodification.modification_id = modification.modification_id
  and wokli.workorderline_id = workorderlinemodification.workorderline_id and workorderlinemodification.sequencenumber=2 ) modificationCodeB
, (select modification.code from modification, workorderlinemodification where workorderlinemodification.modification_id = modification.modification_id
  and wokli.workorderline_id = workorderlinemodification.workorderline_id and workorderlinemodification.sequencenumber=3 ) modificationCodeC
, (sq.qtyunique + sq.qtyuniquewithmodifications) qtyunique, (srg.qtyuniquereserved+srg.qtyuniquewiithmodifreserved) qtyuniquereserved
, sq.qtynonunique , srg.qtynonuniquereserved ,  route.routenumber
, (select day_desc.code from day_desc where day_desc.day_id = route.drivingday_id
  and day_desc.language_id = languages.language_id) DayCode, wok.workordernumber
, wok.requestdate ReqstdDelivery, wokli.quantityordered WorkOrderquantityordered
,  nvl(WRE.QUANTITY, plo.qtypicked) WorkOrderLineReservationQTY
,(select c.customernumber from customer_link c where c.customer_link_id = wok.customer_link_id) customernumber,
(select c.name from customer_link c where c.customer_link_id = wok.customer_link_id) customerName
, 0 wearernumber, '' wearerSearchname,
(select d.distributionpointnumber from distributionpoint_link d where d.distributionpoint_link_id = wok.distributionpoint_link_id) distributionpointnumber,
(select d.description from distributionpoint_link d where d.distributionpoint_link_id = wok.distributionpoint_link_id) distributionpointdescription
, languages.code languageCode
, wok.origin
, prd.product_id
, pl.picklist_id
, pl.picklistnumber
, (select osd.code from sizedefinition_link osd where wokli.sizedefinition_link_id = osd.sizedefinition_link_id)  OrderedSizeCode
, pll.qtytopick qtytopick, pll.qtypicked qtypicked,pll.stocktype stocktype, sl.areacode StockLocAreaCode
from
  workorder wok
    left outer join route on wok.route_id = route.route_id
, workorderline wokli left outer join workorderlinereservation wre on  wokli.workorderline_id = wre.workorderline_id, product_link prd, qualitygrade qgReserved, qualitygrade qgOrder
, stockroom s
, stockdefinitionperitem sdi,
  stockreservationpergrade srg
    --left outer
    join (
      stockquantity sq join stockroomlocation sl on sl.stockroomlocation_id = sq.stockroomlocation_id
          ) on srg.stockreservationpergrade_id = sq.stockreservationpergrade_id
, sizedefinition_link sizedef, languages
, picklist pl, picklistline pll, picklistlineorigin plo, picklistlinelocation pllo
where wok.weareremployment_link_id is null
and wok.workorder_id = wokli.workorder_id
and wokli.requestedgrade_id = qgOrder.Qualitygrade_Id
--and wokli.workorderline_id = wre.workorderline_id
--and wre.stockreservationpergrade_id = srg.stockreservationpergrade_id
and s.stockroom_id = sdi.stockroom_id
and sdi.stockdefinitionperitem_id = srg.stockdefinitionperitem_id
and srg.qualitygrade_id = qgReserved.qualitygrade_id
and sdi.product_id = prd.product_id
and sdi.sizedefinition_id = sizedef.sizedefinition_id
and wok.origin not in (7, 10, 11) --exclude credit notes, mounting and dismounting orders
and sl.locationtype_id in (select lt.locationtype_id from locationtype lt where lt.pickinglocation = 'Y')
and pl.picklist_id = pll.picklist_id and pll.picklistline_id = plo.picklistline_id and pll.picklistline_id=pllo.picklistline_id
and Upper(plo.baselinetable) = 'WORKORDERLINE' and plo.baselinetable_link_id = wokli.workorderline_id and plo.baselinetype = wok.origin
--and wre.stockreservationpergrade_id = pll.stockreservationpergrade_id and wre.stocktype = pll.stocktype
and decode(wokli.codeuniquely, 'N', 1, 'Y', 2, wre.stocktype  ) = pll.stocktype
and wok.processingstockroom_id=s.stockroom_id
and wokli.sizedefinition_link_id=sdi.sizedefinition_id
and wokli.product_link_id=sdi.product_id
and nvl(sdi.customer_id, 0) in (wok.customer_link_id, 0)
and sdi.customerowned = wok.customerowned
and wokli.requestedgrade_id = srg.qualitygrade_id
and pllo.stockroomlocation_id=sl.stockroomlocation_id
;
/
