create or replace view v_deliverynoteappendix as
select 1 linetype,
       coalesce(ih.deltocustomernumber, ih.billtocustomernumber) delnumber,
       ih.street,
       ih.zipcode,
       coalesce(ih.deltocustomername, ih.billtocustomername) delname,
       ih.city,
       ih.county,
       ih.state,
       ih.billtocustomernumber,
       ih.billtocustomername,
       ih.invoicedate,
       ih.invoiceheader_id,
       ih.invoicenumber,
       pn.packingnote_id,
       pn.notenumber,
       pn.deliverydate,
       pn.showoninvoiceappendix,
       pn.origin,
       cons.distributionpointnumber,
       cons.description distributionpointdesc,
       nvl(pnl.customcode,
           (select prd.code
              from product prd
             where pnl.product_id = prd.product_id)) customcode,
       nvl(pnl.customdescription,
           (select product_desc.description
              from product_desc
             where product_desc.product_id = pnl.product_id
               and product_desc.language_id = languages.language_id)) customdesc,
       (select sz.code
          from sizedefinition sz
         where pnl.sizedefinition_id = sz.sizedefinition_id) sizecode,
       pnl.deliveredamount,
       pnl.packingnoteline_id,
       (select sum(packingnotecontainer.grossweight -
                   packingnotecontainer.tareweight)
          from packingnotecontainer
         where pn.packingnote_id = packingnotecontainer.packingnote_id
         group by pn.packingnote_id) weight,
       (select max(packingnotecontainer.weightdecimals)
          from packingnotecontainer
         where pn.packingnote_id = packingnotecontainer.packingnote_id) weightdecimals,
       coalesce(
         pnl.price
         , pack_price.Wash_Price(dep.customer_id, pnl.product_id, pnl.sizedefinition_id, pnl.finishingmethod_id, pn.deliverydate)  
         /*, (select max(l.price)
               from invoicelineproduct ilp, invoiceline l
               where l.invoiceheader_id = ih.invoiceheader_id
               and l.invoiceline_id = ilp.invoiceline_id
               and ilp.product_link_id = pnl.product_id
               and ilp.finishingmethod_id = pnl.finishingmethod_id
               and l.invoicelinetype_id = 2
               and (ilp.sizedefinition_link_id = pnl.sizedefinition_id 
                   or ilp.sizedefinition_link_id =
                  (select sizedefinition.sizedefinition_id
                      from sizedefinition
                      where sizedefinition.code = '*'))*/)price,
       (select min(l.discountpercentage)
          from invoicelineproduct ilp, invoiceline l
         where l.invoiceheader_id = ih.invoiceheader_id
           and l.invoiceline_id = ilp.invoiceline_id
           and ilp.product_link_id = pnl.product_id
           and ilp.finishingmethod_id = pnl.finishingmethod_id
           and l.invoicelinetype_id = 2
           and (ilp.sizedefinition_link_id = pnl.sizedefinition_id or
               ilp.sizedefinition_link_id =
               (select sizedefinition.sizedefinition_id
                   from sizedefinition
                  where sizedefinition.code = '*'))) discountpercentage, -- total, to be calculated in the report!
       currency.code currencycode,
       currency.decimals pricedecimals,
       currency.invoiceamountdecimals invoicedecimals,
       0 pnnotininvoice,
       pn.remark,
       (select ss.value
          from systemsetting ss
         where plant.plant_id = ss.plant_id
           and ss.name = 'WEIGHTUNIT') weightunit,
       languages.code languagecode,
       dep.departmentnumber dep_number,
       dep.description dep_description,
       pn.freeofchargereason_id,
       (select max(ilp.pricefactor)
          from invoicelineproduct ilp, invoiceline l
         where l.invoiceheader_id = ih.invoiceheader_id
           and l.invoiceline_id = ilp.invoiceline_id
           and ilp.product_link_id = pnl.product_id
           and ilp.finishingmethod_id = pnl.finishingmethod_id
           and (ilp.sizedefinition_link_id = pnl.sizedefinition_id or
               ilp.sizedefinition_link_id =
               (select sizedefinition.sizedefinition_id
                   from sizedefinition
                  where sizedefinition.code = '*'))) pricefactor
  from invoiceheader     ih,
       packingnoteline   pnl,
       product p,
       packingnote       pn,
       distributionpoint cons,
       department        dep,
       currency,
       status            statuspn,
       status            statusih,
       businessunit      processbu,
       plant,
       languages
 where ih.invoiceheader_id = pnl.invoiceheader_id
   and ih.documenttype = 1
   and ih.status_id = statusih.status_id
   and statusih.statustype = 'IN'
   and pnl.packingnote_id = pn.packingnote_id
   and pnl.product_id=p.product_id
   and p.typeofmerchandise<> 3
   and pn.status_id = statuspn.status_id
   and statuspn.statustype = 'PN'
   and statuspn.code = '30'
   and pn.distributionpoint_id = cons.distributionpoint_id
   and cons.department_id = dep.department_id
   and ih.currency_id = currency.currency_id
   and pn.processbusinessunit_id = processbu.businessunit_id
   and processbu.plant_id = plant.plant_id
union all
select distinct 2 linetype, coalesce(ih.deltocustomernumber, ih.billtocustomernumber) delnumber, ih.street, ih.zipcode,
       coalesce(ih.deltocustomername, ih.billtocustomername) delname, ih.city, ih.county, ih.state,
       ih.billtocustomernumber, ih.billtocustomername, ih.invoicedate, ih.invoiceheader_id,
       ih.invoicenumber, pn.packingnote_id, pn.notenumber, pn.deliverydate, pn.showoninvoiceappendix,
       pn.origin, cons.distributionpointnumber, cons.description distributionpointdesc,
       '' customcode,       '' customdesc,			 '' sizecode,
       0 deliveredamount, 0 packingnoteline_id,
       (select sum(packingnotecontainer.grossweight - packingnotecontainer.tareweight)
         from   packingnotecontainer
         where  pn.packingnote_id = packingnotecontainer.packingnote_id
         group  by pn.packingnote_id) weight,
       pnc.weightdecimals,
       0 price,
       0 discountpercentage,       -- total, to be calculated in the report!
       currency.code currencycode, currency.decimals pricedecimals,
       currency.invoiceamountdecimals invoicedecimals, 0 pnnotininvoice, pn.remark,
       (select ss.value from systemsetting ss where plant.plant_id = ss.plant_id and ss.name = 'WEIGHTUNIT') weightunit,
			 languages.code languagecode, dep.departmentnumber dep_number,
       dep.description dep_description, pn.freeofchargereason_id,
       1 pricefactor
from   invoiceheader ih, packingnote pn, packingnotecontainer pnc, distributionpoint cons, department dep,
       currency, status statuspn, status statusih,
       businessunit processbu, plant, languages
where  ih.invoiceheader_id = pnc.invoiceheader_id
and    pnc.packingnote_id = pn.packingnote_id
and    ih.documenttype = 1
and    ih.status_id = statusih.status_id
and    statusih.statustype = 'IN'
and    pn.status_id = statuspn.status_id
and    statuspn.statustype = 'PN'
and    statuspn.code = '30'
and    pn.distributionpoint_id = cons.distributionpoint_id
and    cons.department_id = dep.department_id
and    ih.currency_id = currency.currency_id
and    pn.processbusinessunit_id = processbu.businessunit_id
and    processbu.plant_id = plant.plant_id
and not exists (select 1 from packingnoteline pnl where pnl.packingnote_id = pn.packingnote_id)
union all-- sales items
select 3 linetype,
       coalesce(ih.deltocustomernumber, ih.billtocustomernumber) delnumber,
       ih.street,
       ih.zipcode,
       coalesce(ih.deltocustomername, ih.billtocustomername) delname,
       ih.city,
       ih.county,
       ih.state,
       ih.billtocustomernumber,
       ih.billtocustomername,
       ih.invoicedate,
       ih.invoiceheader_id,
       ih.invoicenumber,
       pn.packingnote_id,
       pn.notenumber,
       pn.deliverydate,
       pn.showoninvoiceappendix,
       pn.origin,
       cons.distributionpointnumber,
       cons.description distributionpointdesc,
       nvl(pnl.customcode,
           (select prd.code
              from product prd
             where pnl.product_id = prd.product_id)) customcode,
       nvl(pnl.customdescription,
           (select product_desc.description
              from product_desc
             where product_desc.product_id = pnl.product_id
               and product_desc.language_id = languages.language_id)) customdesc,
       (select sz.code
          from sizedefinition sz
         where pnl.sizedefinition_id = sz.sizedefinition_id) sizecode,
       pnl.deliveredamount,
       pnl.packingnoteline_id,
       (select sum(packingnotecontainer.grossweight -
                   packingnotecontainer.tareweight)
          from packingnotecontainer
         where pn.packingnote_id = packingnotecontainer.packingnote_id
         group by pn.packingnote_id) weight,
       (select max(packingnotecontainer.weightdecimals)
          from packingnotecontainer
         where pn.packingnote_id = packingnotecontainer.packingnote_id) weightdecimals,
       coalesce(
         pnl.price
         , pack_price.Wash_Price(dep.customer_id, pnl.product_id, pnl.sizedefinition_id, pnl.finishingmethod_id, pn.deliverydate)
         /*, (select max(l.price)
             from invoicelineproduct ilp, invoiceline l
             where l.invoiceheader_id = ih.invoiceheader_id
             and l.invoiceline_id = ilp.invoiceline_id
             and ilp.product_link_id = pnl.product_id
             and ilp.finishingmethod_id = pnl.finishingmethod_id
             and l.invoicelinetype_id = 5
             and (ilp.sizedefinition_link_id = pnl.sizedefinition_id or
                  ilp.sizedefinition_link_id =
                  (select sizedefinition.sizedefinition_id
                     from sizedefinition
                     where sizedefinition.code = '*')))*/) price,
       0 discountpercentage, -- total, to be calculated in the report!
       currency.code currencycode,
       currency.decimals pricedecimals,
       currency.invoiceamountdecimals invoicedecimals,
       0 pnnotininvoice,
       pn.remark,
       (select ss.value
          from systemsetting ss
         where plant.plant_id = ss.plant_id
           and ss.name = 'WEIGHTUNIT') weightunit,
       languages.code languagecode,
       dep.departmentnumber dep_number,
       dep.description dep_description,
       pn.freeofchargereason_id,
       (select max(ilp.pricefactor)
          from invoicelineproduct ilp, invoiceline l
         where l.invoiceheader_id = ih.invoiceheader_id
           and l.invoiceline_id = ilp.invoiceline_id
           and ilp.product_link_id = pnl.product_id
           and ilp.finishingmethod_id = pnl.finishingmethod_id
           and (ilp.sizedefinition_link_id = pnl.sizedefinition_id or
               ilp.sizedefinition_link_id =
               (select sizedefinition.sizedefinition_id
                   from sizedefinition
                  where sizedefinition.code = '*'))) pricefactor
  from invoiceheader     ih,
       packingnoteline   pnl,
       product           p,
       packingnote       pn,
       distributionpoint cons,
       department        dep,
       currency,
       status            statuspn,
       status            statusih,
       businessunit      processbu,
       plant,
       languages
 where ih.invoiceheader_id = pnl.invoiceheader_id
   and ih.documenttype = 1
   and ih.status_id = statusih.status_id
   and statusih.statustype = 'IN'
   and pnl.packingnote_id = pn.packingnote_id
   and pnl.product_id=p.product_id
   and p.typeofmerchandise = 3
   and pn.status_id = statuspn.status_id
   and statuspn.statustype = 'PN'
   and statuspn.code = '30'
   and pn.distributionpoint_id = cons.distributionpoint_id
   and cons.department_id = dep.department_id
   and ih.currency_id = currency.currency_id
   and pn.processbusinessunit_id = processbu.businessunit_id
   and processbu.plant_id = plant.plant_id;
/