CREATE OR REPLACE PACKAGE PACK_TRADE is
  --Version 2.0 PACK_TRADE
  /* ----------------------------------------------------------------------------------------------
     Function GetSalesOrderLinesTotalAmount
     Use this function to calculate accumulated value of all sales order lines, no discounts
  */
  function GetSalesOrderLinesTotalAmount(SalesNote_ID       workorder.workorder_id%type)
  return number;

  /* ----------------------------------------------------------------------------------------------
     Function GetSalesOrderLinesTotalNetAmount
     Use this function to calculate accumulated value of all sales order lines.
  */

  function GetSalesOrderLinesTotalNetAmt(SalesNote_ID       workorder.workorder_id%type)
  return number;


  /* ----------------------------------------------------------------------------------------------
     Function GetSalesOrderLinesEmblemTotalAmount
     Use this function to calculate accumulated value of all emblem lines no discount.
  */

  function GetSalesOrderLinesEmblTotalAmt(SalesNote_ID       workorder.workorder_id%type)
  return number;

  /* ----------------------------------------------------------------------------------------------
     Function GetSalesOrderLinesEmblemTotalNetAmount
     Use this function to calculate accumulated value of all emblem lines. Take into account system setting EMBLEMONSALESNOTE
  */

  function GetSalesOrdLinesEmbTotNetAmt (SalesNote_ID       workorder.workorder_id%type,
                                         param_plant_ID     plant.plant_id%type)
  return number;

  /* ----------------------------------------------------------------------------------------------
     Function GetSalesOrderLinesTotalDiscount
     Use this function to calculate accumulated discount value of all sales order lines
  */
  function GetSalesOrdLinesTotalDisc(SalesNote_ID       workorder.workorder_id%type)
  return number;

  /* ----------------------------------------------------------------------------------------------
     Function GetSalesOrderLinesEmblemTotalDiscount
     Use this function to calculate ccumulated discount value of all emblem lines. Take into account system setting EMBLEMONSALESNOTE
  */
  function GetSalesOrdLinesEmbTotDisc(SalesNote_ID       workorder.workorder_id%type,
                                      param_plant_ID     plant.plant_id%type)
  return number;

  /* ----------------------------------------------------------------------------------------------
     Function GetSalesOrderTheoreticalWeight
     Use this function to calculate total theoretical weight
  */
  function GetSalesOrderTheoreticalWeight(SalesNote_ID    workorder.workorder_id%type)
  return number;

  /* ----------------------------------------------------------------------------------------------
     Function GetDeliveryTermUnitPrice
     Use this function to calculate the actual price that is active at ?price date? for the delivery term of the sales note (DeliveryTermPrice.UnitPrice)
  */
  function GetDeliveryTermUnitPrice(DeliveryTermID       deliveryterm.deliveryterm_id%type,
                                    Weight                number,
                                    PriceDate             date)
  return number;

  /* ----------------------------------------------------------------------------------------------
     Function GetSalesOrderTransportCosts
     Use this function to calculate Postage / Transport costs
  */
  function GetSalesOrderTransportCosts(SalesNote_ID       workorder.workorder_id%type,
                                       param_plant_ID     plant.plant_id%type)
  return number;

  /* ----------------------------------------------------------------------------------------------
     Function GetSalesOrderTotalVAT
     Use this function to calculate the VAT
  */
  function GetSalesOrderTotalVAT(SalesNote_ID       workorder.workorder_id%type,
                                 param_plant_ID     plant.plant_id%type)
  return number;

  /* ----------------------------------------------------------------------------------------------
     Function GetSalesOrderTotalNetAmount
     Use this function to calculate Subtotal 2
  */
  function GetSalesOrderTotalNetAmount(SalesNote_ID       workorder.workorder_id%type,
                                       param_plant_ID     plant.plant_id%type)
  return number;

  /* ----------------------------------------------------------------------------------------------
     Function GetSalesOrderTotalGrossAmount
     Use this function to calculate Gross order amount
  */
  function GetSalesOrderTotalGrossAmount(SalesNote_ID       workorder.workorder_id%type,
                                         param_plant_ID     plant.plant_id%type)
  return number;

  /* ----------------------------------------------------------------------------------------------
     Function GetSubTotal1
     Use this function to calculate M_SubTotal1
  */
  function GetSubTotal1(SalesNote_ID       workorder.workorder_id%type,
                        param_plant_ID     plant.plant_id%type)
  return number;

  /* ----------------------------------------------------------------------------------------------
     Function GetSubTotal2
     Use this function to calculate M_SubTotal2
  */
  function GetSubTotal2(SalesNote_ID       workorder.workorder_id%type,
                        param_plant_ID     plant.plant_id%type)
  return number;

  /* ----------------------------------------------------------------------------------------------
     Function GetSalesOrderGODAmount
     Use this function to calculate Sales Order General Order Discount Amount
  */
  function GetSalesOrderGODAmount(SalesNote_ID       workorder.workorder_id%type,
                                  param_plant_ID     plant.plant_id%type)
  return number;
end PACK_TRADE;
/
CREATE OR REPLACE PACKAGE BODY PACK_TRADE is

 ------------------------------------------------------------------------------------

  function GetSalesOrderLinesTotalAmount(SalesNote_ID         in workorder.workorder_id%type)
  return number is
     M_SalesOrderLineTotal      number;

  begin
     select case when wo.origin = 7
            then nvl(sum (wol.salesprice * wol.quantitytobecredited),0)
            else nvl(sum (wol.salesprice * (wol.quantityordered-wol.quantitycancelled)),0)
            end SalesOrderLineTotal
      into  M_SalesOrderLineTotal
      from workorderline wol, workorder wo
     where wol.workorder_id = wo.workorder_id and wo.workorder_id = SalesNote_ID
     group by wo.origin;

    return (M_SalesOrderLineTotal);
    end GetSalesOrderLinesTotalAmount;

 ---------------------------------------------------------------------------------------

  function GetSalesOrderLinesTotalNetAmt(SalesNote_ID         in workorder.workorder_id%type)
    return number is
     M_SalesOrderLineNetTotal      number;

  begin
     select case when wo.origin = 7
            then nvl(sum (wol.salesprice * wol.quantitytobecredited*(100 - wol.discountpercentage)/100),0)
            else nvl(sum (wol.salesprice * (wol.quantityordered-wol.quantitycancelled)*(100 - wol.discountpercentage)/100),0)
            end SalesOrderLineNetTotal
      into  M_SalesOrderLineNetTotal
      from workorderline wol, workorder wo
     where wol.workorder_id = wo.workorder_id and wo.workorder_id = SalesNote_ID
     group by wo.origin;

         return (M_SalesOrderLineNetTotal);
    end GetSalesOrderLinesTotalNetAmt;

 ----------------------------------------------------------------------------------------

  function GetSalesOrderLinesEmblTotalAmt(SalesNote_ID         in workorder.workorder_id%type)
    return number is
     M_SalesOrderEmblemLineTotal      number;

  begin

      select nvl(sum (wole.salesprice * wole.qtyrequested),0)
      into  M_SalesOrderEmblemLineTotal
      from workorderline wol, workorderlineemblem wole
      where wole.workorderline_id=wol.workorderline_id
      and wole.product_id is not null
      and wole.sizedefinition_id is not null
      and wol.workorder_id = SalesNote_ID;

    return (M_SalesOrderEmblemLineTotal);
  end GetSalesOrderLinesEmblTotalAmt;

  ------------------------------------------------------------------------------------------

  function GetSalesOrdLinesEmbTotNetAmt(SalesNote_ID         in workorder.workorder_id%type,
                                        param_plant_ID       in plant.plant_id%type)
    return number is
     M_SalesOrderEmblemLineNetTotal      number;
     value_EMBLEMONSALESNOTE             number;
  begin

   select nvl(ss.value, ss.factorydefault)
      into value_EMBLEMONSALESNOTE
      from plant p, systemsetting ss
     where p.plant_id = param_plant_ID
       and p.plant_id = ss.plant_id
       and ss.name = 'EMBLEMSONSALESNOTE';

       if (value_EMBLEMONSALESNOTE = 0) then

            select nvl(sum (wole.salesprice * wole.qtyrequested),0)
            into  M_SalesOrderEmblemLineNetTotal
            from workorderline wol, workorderlineemblem wole
            where wole.workorderline_id=wol.workorderline_id
            and wole.product_id is not null
            and wole.sizedefinition_id is not null
            and wol.workorder_id = SalesNote_ID;

     else

          select nvl(sum (wole.salesprice * wole.qtyrequested * (100 - wol.discountpercentage)/100),0)
          into  M_SalesOrderEmblemLineNetTotal
          from workorderline wol, workorderlineemblem wole
          where wole.workorderline_id=wol.workorderline_id
          and wole.product_id is not null
          and wole.sizedefinition_id is not null
          and wol.workorder_id = SalesNote_ID;

      end if;

      return (M_SalesOrderEmblemLineNetTotal);
  end GetSalesOrdLinesEmbTotNetAmt;
 ---------------------------------------------------------------------------------------------

  function GetSalesOrdLinesTotalDisc(SalesNote_ID         in workorder.workorder_id%type)
    return number is
     M_SalesOrderLineDiscount      number;

  begin

     select case when wo.origin = 7
            then nvl(sum (wol.salesprice * wol.quantitytobecredited * (wol.discountpercentage)/100),0)
            else nvl(sum (wol.salesprice * (wol.quantityordered - wol.quantitycancelled) * (wol.discountpercentage)/100),0)
            end SalesOrderLineDiscount
     into M_SalesOrderLineDiscount
     from workorderline wol, workorder wo
     where wol.workorder_id = wo.workorder_id and wo.workorder_id = SalesNote_ID
     group by wo.origin;

       return (M_SalesOrderLineDiscount);
    end GetSalesOrdLinesTotalDisc;

   ---------------------------------------------------------------------------------------------

  function GetSalesOrdLinesEmbTotDisc(SalesNote_ID         in workorder.workorder_id%type,
                                      param_plant_ID       in plant.plant_id%type)
    return number is
     M_SalesOrderEmblemLineDiscount      number;
     value_EMBLEMONSALESNOTE             number;
  begin

       select nvl(ss.value, ss.factorydefault)
      into value_EMBLEMONSALESNOTE
      from plant p, systemsetting ss
     where p.plant_id = param_plant_ID
       and p.plant_id = ss.plant_id
       and ss.name = 'EMBLEMSONSALESNOTE';

       if (value_EMBLEMONSALESNOTE = 0) then
         M_SalesOrderEmblemLineDiscount := 0;

       else

        select nvl(sum (wole.salesprice * wole.qtyrequested * (wol.discountpercentage/100)),0)
        into M_SalesOrderEmblemLineDiscount
        from workorderline wol, workorderlineemblem wole
        where   wole.workorderline_id=wol.workorderline_id
        and wole.product_id is not null
        and wole.sizedefinition_id is not null
        and wol.workorder_id=SalesNote_ID;

    end if;
       return (M_SalesOrderEmblemLineDiscount);
    end GetSalesOrdLinesEmbTotDisc;

   ---------------------------------------------------------------------------------------------
  function GetSalesOrderTheoreticalWeight(SalesNote_ID in workorder.workorder_id%type)
    return number is

     M_TheoreticalWeight                 number;
     calcweight                          number;
     calcemblemweight                    number;
     origin                    workorder.origin%type;
  begin

  select wo.origin into origin
    from workorder wo
   where wo.workorder_id = SalesNote_ID;

    select
      case when origin = 7  then
            nvl(sum (wol.quantitytobecredited * decode(ps.theoreticalweight, 0, p.theoreticalweight, ps.theoreticalweight)),0)
      else
            nvl(sum ((wol.quantityordered - wol.quantitycancelled) * decode(ps.theoreticalweight, 0, p.theoreticalweight, ps.theoreticalweight)),0)
     end  thweight
    into calcweight
    from workorderline wol, product p, productsize ps
    where p.product_id=wol.product_link_id
    and ps.product_id=wol.product_link_id
    and ps.sizedefinition_id=wol.sizedefinition_link_id
    and wol.workorder_id = SalesNote_ID;

    select nvl(sum (wole.qtyrequested * decode(ps.theoreticalweight, 0, p.theoreticalweight, ps.theoreticalweight)),0)
    into calcemblemweight
    from workorderline wol, workorderlineemblem wole, product p, productsize ps
    where wole.workorderline_id=wol.workorderline_id
    and p.product_id=wole.product_id
    and ps.product_id=wole.product_id
    and ps.product_id=wole.product_id
    and ps.sizedefinition_id=wole.sizedefinition_id
    and wol.workorder_id=SalesNote_ID;

    M_TheoreticalWeight := calcweight + calcemblemweight;
     return (M_TheoreticalWeight);
  end GetSalesOrderTheoreticalWeight;

  ---------------------------------------------------------------------------------------------
    function GetDeliveryTermUnitPrice(DeliveryTermID       deliveryterm.deliveryterm_id%type,
                                    Weight               number,
                                    PriceDate            date)
  return number is

  UnitPrice                     number;

  begin
    begin
      select *
      into UnitPrice from
      (select s1.unitprice
        from deliverytermprice s1
       where s1.deliveryterm_id = DeliveryTermID
         and s1.fromweight = (select max(s2.fromweight) from  deliverytermprice s2 where s2.startdate <= PriceDate and s2.fromweight <= Weight
                            and s2.deliveryterm_id=DeliveryTermID)
         and s1.startdate <= PriceDate
        order by s1.startdate desc
        )
        -- to make sure that only the most recent price is returned
        where
            rownum = 1;
      exception
           when no_data_found then
           UnitPrice := 0;
      end;
    return (UnitPrice);
  end GetDeliveryTermUnitPrice;

  ---------------------------------------------------------------------------------------------

  function GetSalesOrderTransportCosts(SalesNote_ID         in workorder.workorder_id%type,
                                        param_plant_ID       in plant.plant_id%type)
  return number is

     M_TransportCosts                    number;
     M_SubTotal2                         number;
     M_MaxNetOrderValue                  number;
     M_DeliveryTermUnitPrice             number;
     M_DeliveryTerm_ID                   number;
     M_TheoreticalWeight                 number;

  begin
    -- determine sales order amount minus sales order line discounts
    M_SubTotal2 := GetSubTotal2(SalesNote_ID, param_plant_ID);
    -- determine delivery term, registered weight, transport costs and max order value
    select wo.deliveryterm_id, wo.theoreticalweight, wo.transportcosts, dt.maxnetordervalue
    into M_DeliveryTerm_ID, M_TheoreticalWeight, M_TransportCosts, M_MaxNetOrderValue
    from workorder wo, deliveryterm dt
    where wo.deliveryterm_id(+) = dt.deliveryterm_id
    and wo.workorder_id = SalesNote_ID;
    -- determine theoretical weight when no weight is registered only
    If (M_TheoreticalWeight is null) Then
      M_TheoreticalWeight := GetSalesOrderTheoreticalWeight(SalesNote_ID);
    end if;
    -- calculate transport costs only when not stored yet for order
    if (M_TransportCosts is null) Then
      if (M_SubTotal2 > M_MaxNetOrderValue) then
        M_TransportCosts := 0;
      else
        -- determine applicable unit price using dedicated method
        M_DeliveryTermUnitPrice :=  GetDeliveryTermUnitPrice(M_DeliveryTerm_ID, M_TheoreticalWeight, sysdate);
        M_TransportCosts := M_DeliveryTermUnitPrice;
      end if;
    end if;
    return (M_TransportCosts);
  end GetSalesOrderTransportCosts;

  ---------------------------------------------------------------------------------------------

  function GetSalesOrderTotalVAT (SalesNote_ID         in workorder.workorder_id%type,
                                  param_plant_ID       in plant.plant_id%type)
    return number is
    -- declare cursor to calculate total VAT for sales order
    -- RVI 20171214 begin
    -- Use m_referencedate instead of SYSDATE to determine applicable VAT rate
    --
    --cursor taxcalc is
    cursor taxcalc(m_referencedate date) is
      select sum(v4.rate_vatamount) totalvatamount
        from (select v3.vatcode, v3.vatrate, sum(vatamount) rate_vatamount
                -- WorkOrderLines
                from (select 'WOL' linetype,
                             tg.code vatcode,
                             case
                               when (tg.startdatetaxrate <= trunc(m_referencedate)) then
                                ((tg.taxrate / 100) * v2.netamount)
                               else
                                ((tg.oldtaxrate / 100) * v2.netamount)
                             end vatamount,
                             case
                               when (tg.startdatetaxrate <= trunc(m_referencedate)) then
                                tg.taxrate
                               else
                                tg.oldtaxrate
                             end vatrate
                        from (select v1.taxgroup_id,
                                     sum((v1.wol_ordered * v1.salesprice *
                                         (100 - v1.wol_discount) / 100) *
                                         ((100 - v1.wo_discount) / 100)) netamount
                                from (select wol.product_link_id,
                                             wol.salesprice,
                                             wol.discountpercentage wol_discount,
                                             case
                                               when (wo.origin = 7) then
                                                wol.quantitytobecredited
                                               else
                                                (wol.quantityordered -
                                                wol.quantitycancelled)
                                             end wol_ordered,
                                             wo.discountpercentage wo_discount,
                                             coalesce(
                                                 -- specific customer / product tax group
                                                 (select cpd.taxgroup_id
                                                   from customerproducttaxdef cpd
                                                  where cpd.customer_id =
                                                        wo.customer_link_id
                                                    and cpd.product_id =
                                                        wol.product_link_id
                                                    and cpd.type = 4),
                                                 -- specific product tax group
                                                 (select pt.taxgroup_id
                                                    from producttax pt
                                                   where pt.country_id = c.country_id
                                                     and pt.product_id =
                                                         wol.product_link_id),
                                                 -- specific invoice line (sub) type tax group
                                                 (select nvl(ill.taxgroup_id, tg.taxgroup_id)
                                                         from invoicelinesubtype ilst
                                                              left outer join turnovergroup tg
                                                          on ilst.turnovergroup_id=tg.turnovergroup_id
                                                          , invoicelinesubtypetax ill
                                                        where ilst.invoicelinetype_id = 5
                                                        and ilst.invoicelinesubtype_id =
                                                              ill.invoicelinesubtype_id
                                                          and ill.country_id =  c.country_id
                                                          and ((ilst.subtype = 17 and
                                                              wo.origin = 6) or
                                                              (wo.origin = 7 and
                                                              ilst.subtype = 18))),
                                                      -- specific invoice line  type tax group
                                                      (select nvl(iltt.taxgroup_id,
                                                                  iltg.taxgroup_id)
                                                         from  invoicelinetypetax iltt, invoicelinetype    ilt left outer join
                                                              turnovergroup      iltg on ilt.turnovergroup_id =
                                                              iltg.turnovergroup_id
                                                        where ilt.invoicelinetype_id =
                                                              iltt.invoicelinetype_id
                                                               and iltt.invoicelinetype_id = 5
                                                          and iltt.country_id =
                                                              c.country_id)) taxgroup_id
                                        from workorder     wo,
                                             customer      c,
                                             workorderline wol
                                       where c.customer_id = wo.customer_link_id
                                         and wol.workorder_id = wo.workorder_id
                                         and wo.workorder_id = SalesNote_ID) v1
                               group by v1.taxgroup_id) v2,
                             taxgroup tg

                       where tg.taxgroup_id = v2.taxgroup_id
                      UNION
                -- WorkOrderLineEmblem lines
                      select 'WOLE' linetype,
                             tg.code vatcode,
                             case
                               when (tg.startdatetaxrate <= trunc(m_referencedate)) then
                                ((tg.taxrate / 100) * v2.netamount)
                               else
                                ((tg.oldtaxrate / 100) * v2.netamount)
                             end vatamount,
                             case
                               when (tg.startdatetaxrate <= trunc(m_referencedate)) then
                                tg.taxrate
                               else
                                tg.oldtaxrate
                             end vatrate
                        from (select v1.taxgroup_id,
                                     v1.emblemonsalesnote,
                                     case
                                       when v1.emblemonsalesnote = 1 then
                                        sum((v1.wole_ordered * v1.salesprice *
                                            (100 - v1.wol_discount) / 100) *
                                            ((100 - v1.wo_discount) / 100))
                                       else
                                        sum((v1.wole_ordered * v1.salesprice *
                                            (100 - v1.wo_discount) / 100))
                                     end netamount
                                from (select wole.product_id,
                                             wole.salesprice,
                                             wol.discountpercentage wol_discount,
                                             wole.qtyrequested wole_ordered,
                                             0 quantitytobecredited,
                                             wo.discountpercentage wo_discount,
                                             s.value emblemonsalesnote,
                                             coalesce(
                                                 -- specific customer / product tax group
                                                 (select cpd.taxgroup_id
                                                   from customerproducttaxdef cpd
                                                  where cpd.customer_id =
                                                        wo.customer_link_id
                                                    and cpd.product_id =
                                                        wole.product_id
                                                    and cpd.type = 4),
                                                 -- specific product tax group
                                                 (select pt.taxgroup_id
                                                    from producttax pt
                                                   where pt.country_id = c.country_id
                                                     and pt.product_id =
                                                         wole.product_id),

                                                 -- specific invoice line (sub) type tax group
                                                 (select nvl(ill.taxgroup_id, tg.taxgroup_id)
                                                         from invoicelinesubtype ilst
                                                              left outer join turnovergroup tg
                                                          on ilst.turnovergroup_id=tg.turnovergroup_id
                                                          , invoicelinesubtypetax ill
                                                        where ilst.invoicelinetype_id = 5
                                                        and ilst.invoicelinesubtype_id =
                                                              ill.invoicelinesubtype_id
                                                          and ill.country_id =  c.country_id
                                                          and ((ilst.subtype = 17 and
                                                              wo.origin = 6) or
                                                              (wo.origin = 7 and
                                                              ilst.subtype = 18))),
                                                      -- specific invoice line  type tax group
                                                      (select nvl(iltt.taxgroup_id,
                                                                  iltg.taxgroup_id)
                                                         from invoicelinetype    ilt,
                                                              invoicelinetypetax iltt,
                                                              turnovergroup      iltg
                                                        where ilt.invoicelinetype_id =
                                                              iltt.invoicelinetype_id
                                                          and iltg.turnovergroup_id =
                                                              ilt.turnovergroup_id
                                                          and iltt.invoicelinetype_id = 5
                                                          and iltt.country_id =
                                                              c.country_id)) taxgroup_id
                                        from workorder           wo,
                                             customer            c,
                                             workorderline       wol,
                                             workorderlineemblem wole,
                                             systemsetting       s
                                       where c.customer_id = wo.customer_link_id
                                         and wol.workorder_id = wo.workorder_id
                                         and wole.workorderline_id =
                                             wol.workorderline_id
                                         and wole.product_id is not null
                                         and wole.sizedefinition_id is not null
                                         and s.plant_id = param_plant_id
                                         and s.name = 'EMBLEMSONSALESNOTE'
                                         and wo.workorder_id = SalesNote_ID) v1
                               group by v1.taxgroup_id, v1.emblemonsalesnote) v2,
                             taxgroup tg

                       where tg.taxgroup_id = v2.taxgroup_id
                      UNION
                -- Transport Costs
                      select 'WOTPC' linetype,
                             tg.code vatcode,
                             case
                               when (tg.startdatetaxrate <= trunc(m_referencedate)) then
                                ((tg.taxrate / 100) * v1.transportcosts)
                               else
                                ((tg.oldtaxrate / 100) * v1.transportcosts)
                             end vatamount,
                             case
                               when (tg.startdatetaxrate <= trunc(m_referencedate)) then
                                tg.taxrate
                               else
                                tg.oldtaxrate
                             end vatrate
                        from (select wo.transportcosts,
                                     (select case
                                               when ilst.taxgroup_id is not null then
                                                ilst.taxgroup_id
                                               when ilsg.taxgroup_id is not null then
                                                ilsg.taxgroup_id
                                               when iltt.taxgroup_id is not null then
                                                iltt.taxgroup_id
                                               else
                                                iltg.taxgroup_id
                                             end taxgroup_id
                                        from invoicelinetype       ilt,
                                             invoicelinetypetax    iltt,
                                             invoicelinesubtype    ils,
                                             invoicelinesubtypetax ilst,
                                             turnovergroup         iltg,
                                             turnovergroup         ilsg
                                       where iltg.turnovergroup_id =
                                             ilt.turnovergroup_id
                                         and iltt.invoicelinetype_id(+) =
                                             ilt.invoicelinetype_id
                                         and iltt.country_id(+) = c.country_id
                                         and ils.invoicelinetype_id(+) =
                                             ilt.invoicelinetype_id
                                         and ilsg.turnovergroup_id =
                                             ils.turnovergroup_id
                                         and ilst.invoicelinesubtype_id(+) =
                                             ils.invoicelinesubtype_id
                                         and ilst.country_id(+) = c.country_id
                                         and ilt.invoicelinetype_id = 16
                                         and ils.subtype = 2) taxgroup_id

                                from workorder wo, customer c
                               where c.customer_id = wo.customer_link_id
                                 and wo.workorder_id = SalesNote_ID) v1,
                             taxgroup tg

                       where tg.taxgroup_id = v1.taxgroup_id) v3
               group by v3.vatcode, v3.vatrate) v4;

    -- declare variables
    m_vatamount      number;
    customertaxable  customer.taxable%type;
    m_referencedate  date;
  begin
    m_vatamount := 0;
    --determine if the customer is taxable or not.
    select cu.taxable,
      case when wo.completiondate >= to_date('31-12-2099','dd-mm-yyyy') then sysdate
        else wo.completiondate
       end 
      into  customertaxable, m_referencedate
      from workorder wo, customer cu
     where wo.customer_link_id = cu.customer_id
       and wo.workorder_id = SalesNote_ID;

    if (customertaxable = 'Y') then
      begin
        for c_ in taxcalc(m_referencedate) loop
         m_vatamount := c_.totalvatamount;
        end loop;
      end;
    else
      m_vatamount := 0;
    end if;
    return(m_vatamount);
  end GetSalesOrderTotalVAT;

  ---------------------------------------------------------------------------------------------

  function GetSalesOrderTotalNetAmount(SalesNote_ID         in workorder.workorder_id%type,
                                       param_plant_ID       in plant.plant_id%type)
    return number is
     M_NetOrderAmount      number;
     transportcosts        number;
  begin
    --determine the transport cost from the database
   select wo.transportcosts
     into  transportcosts
     from workorder wo
    where wo.workorder_id = SalesNote_ID;

     M_NetOrderAmount := GetSubTotal2(SalesNote_ID, param_plant_ID) + transportcosts;
       return (M_NetOrderAmount);

  end GetSalesOrderTotalNetAmount;

  function GetSalesOrderTotalGrossAmount(SalesNote_ID         in workorder.workorder_id%type,
                                         param_plant_ID       in plant.plant_id%type)
    return number is
     M_GrossOrderAmount      number;

  begin

     M_GrossOrderAmount := GetSalesOrderTotalNetAmount(SalesNote_ID, param_plant_ID) + GetSalesOrderTotalVAT(SalesNote_ID,param_plant_ID);
       return (M_GrossOrderAmount);
    end GetSalesOrderTotalGrossAmount;

  function GetSubTotal1(SalesNote_ID         in workorder.workorder_id%type,
                        param_plant_ID       in plant.plant_id%type)
    return number is
       M_SubTotal1                    number;

  begin

    M_SubTotal1 := GetSalesOrderLinesTotalAmount(SalesNote_ID) -
                   GetSalesOrdLinesTotalDisc(SalesNote_ID) +
                   GetSalesOrderLinesEmblTotalAmt(SalesNote_ID) -
                   GetSalesOrdLinesEmbTotDisc(SalesNote_ID, param_plant_ID);
       return (M_SubTotal1);
    end GetSubTotal1;

  function GetSubTotal2(SalesNote_ID         in workorder.workorder_id%type,
                        param_plant_ID       in plant.plant_id%type)
    return number is
       M_SubTotal2                    number;

  begin

   M_SubTotal2 := GetSubTotal1(SalesNote_ID, param_plant_ID) - GetSalesOrderGODAmount(SalesNote_ID, param_plant_ID);
       return (M_SubTotal2);
    end GetSubTotal2;

  function GetSalesOrderGODAmount(SalesNote_ID         in workorder.workorder_id%type,
                                  param_plant_ID       in plant.plant_id%type)
    return number is
     M_GeneralOrderDiscount      number;
     M_DiscPercentage            number;

  begin

    select wo.discountpercentage
    into M_DiscPercentage
    from workorder wo
    where wo.workorder_id = SalesNote_ID;

    M_GeneralOrderDiscount := (GetSubTotal1(SalesNote_ID, param_plant_ID) * (M_DiscPercentage/100));
       return (M_GeneralOrderDiscount);
  end GetSalesOrderGODAmount;

  END PACK_TRADE;
/
