CREATE OR REPLACE PACKAGE PACK_PRICE is
  /*
    18/11/2009 (502831) HVH New developed.
    08/01/2010          HVH Issue fixed with rent price for changes per week.
                            When exact# of changes was passed for size <> * in some cases wrong price was returned
    12/02/2010          HVH Added Weight Price and Weight Discount
    19/02/2010          HVH Added Rent and Wash Discount + performance improvement on all price search proc's
    22/03/2011          SSB Added Special Charge functions
    29/04/2011          HVH Improved Special Charge procedure and extended return string of SpecialCharge_Details
    21/11/2012          SSB Added Residential Rent functions
    13/02/2013          HVH Added Wash_Price_PT for washprices with specific pricetype (i.e. heavy soil)
                            Also added Loan_Price functions.
    01/03/2013          PVD Update for unrelated loan and changes before for general price determination
    17/09/2013          RVI Update for under/overuse wash prices
    25/02/2014          HVH Return replacement price when inventorybillingtype = 5
    27/02/2016          HVH Extended pricelistcode from 6 to 15 chars in xxx_price_details functions
    4/08/2017           DLA Updated for size billing group
	  11/01/2018          DLA Fix for size billing group
	  15/01/2018          SSB Fix for UK weekly underuse method
  */

  /* ----------------------------------------------------------------------------------------------
  /* General remarks Rent price procedures/functions.

     This procedure and functions return the rent price (and other price details) for a given
     customer, product, size and finishingmethod, that is valid on the date that is passed in the
     parameter pricedate. If this optional parameter is omited, it will return the price valid on the sysdate.

     The procedure and functions can be used in 2 ways:
     a) To retrieve a price for a product list per customer line.
        (so without a specific # of changes or specific delivery frequency)
        For this purpose omit, or pass 0 to chgperwk_or_freq parameter.
        It will try to find the most recent valid price for any number of changes or any frequency.

     b) To retrieve a price for a product with a specific # of changes or specific delivery frequency.
        For this purpose pass a value <> 0 to chgperwk_or_freq parameter.
        For garments with billingmethod 1 a specific number of changes can be passed.
        If there is no price defined for that specific number of changes, it will try
        to find the price for number of changes *
        For delivery frequency based pricing it is possible to pass a specific frequency.
        The frequency is defined in # of weeks:
        Example: for delivery scheme EOW (Every other week) frequency = 2. (weekly=1)
  */

  /* ----------------------------------------------------------------------------------------------
    Procedure Get_Rent_Price_Details

    This is the base procedure used by the Rent_PriceXXX functions.
    This procedure can be used to know all details about a price.
  */
  procedure Get_Rent_Price_Details(customerid             in customer.customer_id%type,
                                   productid              in product.product_id%type,
                                   sizedefinitionid       in sizedefinition.sizedefinition_id%type,
                                   finishingmethodid      in finishingmethod.finishingmethod_id%type,
                                   pricedate              in pricelistpercustomer.dateactive%type,
                                   chgperwk_or_freq       in generalrentprice.changesperweek%type,
                                   price                  out customerrentprice.price%type,
                                   price_found_yn         out varchar2,
                                   price_start_date       out customerrentprice.startdate%type,
                                   result_code            out number,
                                   result_desc            out varchar2,
                                   price_list_type        out varchar2,
                                   price_list_code        out generalpricelist.code%type,
                                   price_list_desc        out generalpricelist.description%type,
                                   changesperwkfoundprice out customerrentprice.changesperweek%type,
                                   price_type_code        out customerrentprice.typeofrent%type,
                                   price_type_desc        out varchar2,
                                   inv_freq_code          out customerrentprice.invoicefrequencynumber%type,
                                   inv_freq_desc          out varchar2,
                                   customerrentpriceid    out customerrentprice.customerrentprice_id%type);

  /* ----------------------------------------------------------------------------------------------
    Function Rent_Price_Details

    Returns all price details, like procedure Get_Rent_Price_Details,
    This function returns them all concatenated in a fixed length return string.

    The return string is composed of the following elements:
    pos  1 -  12  (12): price  format: 99999.99999 ,  spaces when no price found.
    pos  13       ( 1): price returned Y / N
    pos  14 - 21  ( 8): price start date format YYYYMMDD, spaces when no price found.
    pos  22       ( 1): price list type: C=Customer specific, G=General price list, space when no price found.
    pos  23 - 28  ( 6): price list code part 1 char 1-6, spaces when no price found, or when customer specific
    pos  29 - 68  (40): price list description spaces when no price found or 'Customer Specific' when customer specific
    pos  69       ( 1): price result code , see list below.
    pos  70 - 149 (80): price result description
    pos 150       ( 1): price type code
    pos 151 - 180 (30): price type description
    pos 181       ( 1): invoice freq code
    pos 182 - 191 (10): invoice freq description
    pos 192 - 201 (10): found price is defined for this number of changes per week
    pos 202 - 210 ( 9): price list code part 2 char 7-15
    Possible values for price_found_yn + result_code combinations:
    Y 0     Price found, no remark
    Y 1     Multiple prices applicable for different # of changes.
    Y 2     Multiple prices applicable for different frequencies.
    N 1     Price not defined, no particular reason
    N 2     Price not relevant, billing method with wash price only
    N 3     Price not relevant, billing method = 4 piece rate only
    N 4     Price not relevant, defined on wearer level as wearer total price.
    N 5     Price not relevant, defined at wearer function level
    N 8     Error in parameters
    N 9     ORA Error
  */
  function Rent_Price_Details(customerid        in customer.customer_id%type,
                              productid         in product.product_id%type,
                              sizedefinitionid  in sizedefinition.sizedefinition_id%type,
                              finishingmethodid in finishingmethod.finishingmethod_id%type,
                              pricedate         in pricelistpercustomer.dateactive%type default null,
                              chgperwk_or_freq  in generalrentprice.changesperweek%type default 0)
    return varchar2;

  /* ----------------------------------------------------------------------------------------------
     Function Rent_Price
     Use this function in calculations. It will return price 0.00 in case the price could not be found.
  */
  function Rent_Price(customerid        in customer.customer_id%type,
                      productid         in product.product_id%type,
                      sizedefinitionid  in sizedefinition.sizedefinition_id%type,
                      finishingmethodid in finishingmethod.finishingmethod_id%type,
                      pricedate         in pricelistpercustomer.dateactive%type default null,
                      chgperwk_or_freq  in generalrentprice.changesperweek%type default 0)
    return number;

  /* ----------------------------------------------------------------------------------------------
     Function CustomerRent_PriceID
     Use this function in calculations. It will return customer rent price id determined in Get_Rent_price_Details procedure.
  */
  function CustomerRent_PriceID(customerid        in customer.customer_id%type,
                    productid         in product.product_id%type,
                    sizedefinitionid  in sizedefinition.sizedefinition_id%type,
                    finishingmethodid in finishingmethod.finishingmethod_id%type,
                    pricedate         in pricelistpercustomer.dateactive%type default null,
                    chgperwk_or_freq  in generalrentprice.changesperweek%type default 0)
     return number;
  /* ----------------------------------------------------------------------------------------------
     Function Rent_Price_Display
     Use this function for display or print purposes. It will return null in case the price could not be found.
  */
  function Rent_Price_Display(customerid        in customer.customer_id%type,
                              productid         in product.product_id%type,
                              sizedefinitionid  in sizedefinition.sizedefinition_id%type,
                              finishingmethodid in finishingmethod.finishingmethod_id%type,
                              pricedate         in pricelistpercustomer.dateactive%type default null,
                              chgperwk_or_freq  in generalrentprice.changesperweek%type default 0)
    return number;

  /* ---------------------------------------------------------------------------------------------- */
  /* General remarks Wash price procedures/functions.

     This procedure and functions return the wash price (and other price details) for a given
     customer, product, size and finishingmethod, that is valid on the date that is passed in the
     parameter pricedate. If this optional parameter is omited, it will return the price valid on the sysdate.

     The procedure and functions can be used in 2 ways:
     a) To retrieve a price for a product list per customer line.
        (so without indication if the price is for a LOG (Laundry Owned Goods) or COG (Customer Owned Goods) item)
        For this purpose omit, or pass 0 to pricetype parameter.
        It will try to find the most recent valid price for any number of changes or any frequency.

     b) To retrieve a price for a product for which it is known if it is a LOG or COG item.
        Pass 1 for LOG, pass 2 for COG to parameter pricetype

     c) To retrieve a price for a product for for a specific known price type (PT) use the Wash_Price_PT functions
        For example for price type Heavy Soil. Possible price types are described under Get_Wash_Price_Details_PT

  */

  /* ----------------------------------------------------------------------------------------------
    Procedure Get_Wash_Price_Details

    This is the base procedure used by the Wash_PriceXXX functions.
    This procedure can be used to know all details about a price.
  */

  procedure Get_Wash_Price_Details(customerid             in customer.customer_id%type,
                                   productid         in product.product_id%type,
                                   sizedefinitionid  in sizedefinition.sizedefinition_id%type,
                                   finishingmethodid in finishingmethod.finishingmethod_id%type,
                                   pricedate         in pricelistpercustomer.dateactive%type,
                                   pricetype         in number, -- 0=all, 1=LOG, 2=COG
                                   price             out customerwashprice.price%type,
                                   price_found_yn    out varchar2,
                                   price_start_date  out customerwashprice.startdate%type,
                                   result_code       out number,
                                   result_desc       out varchar2,
                                   price_list_type   out varchar2,
                                   price_list_code   out generalpricelist.code%type,
                                   price_list_desc   out generalpricelist.description%type,
                                   price_type_code   out customerwashprice.pricetype%type,
                                   price_type_desc   out varchar2);

  /* ----------------------------------------------------------------------------------------------
    Function Wash_Price_Details

    Returns all price details, like procedure Get_Wash_Price_Details,
    This function returns them all concatenated in a fixed length return string.

    The return string is composed of the following elements:
    pos  1 -  12  (12): price  format: 99999.99999 ,  spaces when no price found.
    pos  13       ( 1): price returned Y / N
    pos  14 - 21  ( 8): price start date format YYYYMMDD, spaces when no price found.
    pos  22       ( 1): price list type: C=Customer specific, G=General price list, space when no price found.
    pos  23 - 28  ( 6): price list code part 1 char 1-6, spaces when no price found, or when customer specific
    pos  29 - 68  (40): price list description spaces when no price found or 'Customer Specific' when customer specific
    pos  69       ( 1): price result code , see list below.
    pos  70 - 149 (80): price result description
    pos 150       ( 1): price type code
    pos 151 - 180 (30): price type description
    pos 181 - 189 ( 9): price list code part 2 char 7-15

    Possible values for price_found_yn + result_code combinations:
    Y 0     Price found, no remark
    Y 1     Distinct prices can be applicable LOG and COG
    N 1     Price not defined, no particular reason
    N 2     Price not relevant, billing method 3 with rent price only
    N 3     Price not relevant, billing method = 1 flat rated rent
    N 4     Price not relevant, billing method = 2 rent based on minimum
    N 5     Price not relevant, disposable / sales only item
    N 6     Price not relevant, billing method = 4 with price for LOG only
    N 7     Price not relevant, billing method = 4 with price for COG only
    N 8     Error in parameters
    N 9     ORA Error
  */
  function Wash_Price_Details(customerid        in customer.customer_id%type,
                              productid         in product.product_id%type,
                              sizedefinitionid  in sizedefinition.sizedefinition_id%type,
                              finishingmethodid in finishingmethod.finishingmethod_id%type,
                              pricedate         in pricelistpercustomer.dateactive%type default null,
                              pricetype         in number default 0)
  return varchar2;

  /* ----------------------------------------------------------------------------------------------
     Function Wash_Price
     Use this function in calculations. It will return price 0.00 in case the price could not be found.
  */
  function Wash_Price(customerid        in customer.customer_id%type,
                      productid         in product.product_id%type,
                      sizedefinitionid  in sizedefinition.sizedefinition_id%type,
                      finishingmethodid in finishingmethod.finishingmethod_id%type,
                      pricedate         in pricelistpercustomer.dateactive%type default null,
                      pricetype         in number default 0)
  return number;

  /* ----------------------------------------------------------------------------------------------
     Function Wash_Price_Display
     Use this function for display or print purposes. It will return null in case the price could not be found.
  */
  function Wash_Price_Display(customerid        in customer.customer_id%type,
                              productid         in product.product_id%type,
                              sizedefinitionid  in sizedefinition.sizedefinition_id%type,
                              finishingmethodid in finishingmethod.finishingmethod_id%type,
                              pricedate         in pricelistpercustomer.dateactive%type default null,
                              pricetype         in number default 0)
  return number;

  /* ----------------------------------------------------------------------------------------------
    Procedure Get_Wash_Price_Details_PT

    This is the base procedure used by the Wash_Price_PT functions.
    This procedure can be used to know all details about a wash price for a specified price type (PT)

    Possible price types are:
    1 = All items
    2 = COG items only
    3 = Price on client delivery note
    4 = Wash part of Split price
    5 = Heavy Soil
  */

  procedure Get_Wash_Price_Details_PT(customerid             in customer.customer_id%type,
                                      productid              in product.product_id%type,
                                      sizedefinitionid       in sizedefinition.sizedefinition_id%type,
                                      finishingmethodid      in finishingmethod.finishingmethod_id%type,
                                      pricedate              in pricelistpercustomer.dateactive%type,
                                      pricetype              in number,
                                      price                  out customerwashprice.price%type,
                                      price_found_yn         out varchar2,
                                      price_start_date       out customerwashprice.startdate%type,
                                      result_code            out number,
                                      result_desc            out varchar2,
                                      price_list_type        out varchar2,
                                      price_list_code        out generalpricelist.code%type,
                                      price_list_desc        out generalpricelist.description%type,
                                      price_type_code        out customerwashprice.pricetype%type,
                                      price_type_desc        out varchar2);

  /* ----------------------------------------------------------------------------------------------
    Function Wash_Price_Details_PT

    Returns wash price for specified price type (PT). See Get_Wash_Price_Details_PT for possible price types.
    Returns all price details, like procedure Get_Wash_Price_Details_PT,
    This function returns them all concatenated in a fixed length return string.

    The return string is composed of the following elements:
    pos  1 -  12  (12): price  format: 99999.99999 ,  spaces when no price found.
    pos  13       ( 1): price returned Y / N
    pos  14 - 21  ( 8): price start date format YYYYMMDD, spaces when no price found.
    pos  22       ( 1): price list type: C=Customer specific, G=General price list, space when no price found.
    pos  23 - 28  ( 6): price list code part 1 char 1-6, spaces when no price found, or when customer specific
    pos  29 - 68  (40): price list description spaces when no price found or 'Customer Specific' when customer specific
    pos  69       ( 1): price result code , see list below.
    pos  70 - 149 (80): price result description
    pos 150       ( 1): price type code
    pos 151 - 180 (30): price type description
    pos 181 - 189 ( 9): price list code part 2 char 7-15

    Possible values for price_found_yn + result_code combinations:
    Y 0     Price found, no remark
    N 1     Price not defined, no particular reason
    N 8     Error in parameters
    N 9     ORA Error
  */
  function Wash_Price_Details_PT(customerid        in customer.customer_id%type,
                                 productid         in product.product_id%type,
                                 sizedefinitionid  in sizedefinition.sizedefinition_id%type,
                                 finishingmethodid in finishingmethod.finishingmethod_id%type,
                                 pricedate         in pricelistpercustomer.dateactive%type default null,
                                 pricetype         in number)
  return varchar2;

  /* ----------------------------------------------------------------------------------------------
     Function Wash_Price_PT
     Returns wash price for specified price type (PT). See Get_Wash_Price_Details_PT for possible price types.
     Use this function in calculations. It will return price 0.00 in case the price could not be found.
  */
  function Wash_Price_PT(customerid        in customer.customer_id%type,
                         productid         in product.product_id%type,
                         sizedefinitionid  in sizedefinition.sizedefinition_id%type,
                         finishingmethodid in finishingmethod.finishingmethod_id%type,
                         pricedate         in pricelistpercustomer.dateactive%type default null,
                         pricetype         in number)
  return number;

  /* ----------------------------------------------------------------------------------------------
     Function Wash_Price_Display_PT
     Returns wash price for specified price type (PT). See Get_Wash_Price_Details_PT for possible price types.
     Use this function for display or print purposes. It will return null in case the price could not be found.
  */
  function Wash_Price_Display_PT(customerid        in customer.customer_id%type,
                                 productid         in product.product_id%type,
                                 sizedefinitionid  in sizedefinition.sizedefinition_id%type,
                                 finishingmethodid in finishingmethod.finishingmethod_id%type,
                                 pricedate         in pricelistpercustomer.dateactive%type default null,
                                 pricetype         in number)
  return number;

  /* ----------------------------------------------------------------------------------------------
     Procedure Get_Replacement_Price_Details

     This is the base procedure used by the Replacement_PriceXXX functions.
     This procedure can be used to know all details about a price.
  */
  procedure Get_Replacement_Price_Details(customerid             in customer.customer_id%type,
                                          productid         in product.product_id%type,
                                          sizedefinitionid  in sizedefinition.sizedefinition_id%type,
                                          finishingmethodid in finishingmethod.finishingmethod_id%type,
                                          pricedate         in pricelistpercustomer.dateactive%type,
                                          price             out customerreplacementprice.price%type,
                                          price_found_yn    out varchar2,
                                          price_start_date  out customerreplacementprice.startdate%type,
                                          result_code       out number,
                                          result_desc       out varchar2,
                                          price_list_type   out varchar2,
                                          price_list_code   out generalpricelist.code%type,
                                          price_list_desc   out generalpricelist.description%type);

  /* ----------------------------------------------------------------------------------------------
    Function Replacement_Price_Details

    Returns all price details, like procedure Get_Replacement_Price_Details,
    This function returns them all concatenated in a fixed length return string.

    The return string is composed of the following elements:
    pos  1 -  12  (12): price  format: 99999.99999 ,  spaces when no price found.
    pos  13       ( 1): price returned Y / N
    pos  14 - 21  ( 8): price start date format YYYYMMDD, spaces when no price found.
    pos  22       ( 1): price list type: C=Customer specific, G=General price list, space when no price found.
    pos  23 - 28  ( 6): price list code part 1 char 1-6, spaces when no price found, or when customer specific
    pos  29 - 68  (40): price list description spaces when no price found or 'Customer Specific' when customer specific
    pos  69       ( 1): price result code , see list below.
    pos  70 - 149 (80): price result description
    pos 150 - 158 ( 9): price list code part 2 char 7-15

    Possible values for price_found_yn + result_code combinations:
    Y 0     Price found, no remark
    N 1     Price not defined, no particular reason
    N 2     Price not relevant, billing method = 4 piece rate only
    N 3     Price not relevant, disposable / sales only item
    N 8     Error in parameters
    N 9     ORA Error
  */
  function Replacement_Price_Details(customerid        in customer.customer_id%type,
                                     productid         in product.product_id%type,
                                     sizedefinitionid  in sizedefinition.sizedefinition_id%type,
                                     finishingmethodid in finishingmethod.finishingmethod_id%type default null,
                                     pricedate         in pricelistpercustomer.dateactive%type default null)
    return varchar2;

  /* ----------------------------------------------------------------------------------------------
     Function Replacement_Price
     Use this function in calculations. It will return price 0.00 in case the price could not be found.
  */
  function Replacement_Price(customerid        in customer.customer_id%type,
                             productid         in product.product_id%type,
                             sizedefinitionid  in sizedefinition.sizedefinition_id%type,
                             finishingmethodid in finishingmethod.finishingmethod_id%type default null,
                             pricedate         in pricelistpercustomer.dateactive%type default null)
    return number;

  /* ----------------------------------------------------------------------------------------------
     Function Replacement_Price_Display
     Use this function for display or print purposes. It will return null in case the price could not be found.
  */
  function Replacement_Price_Display(customerid        in customer.customer_id%type,
                                     productid         in product.product_id%type,
                                     sizedefinitionid  in sizedefinition.sizedefinition_id%type,
                                     finishingmethodid in finishingmethod.finishingmethod_id%type default null,
                                     pricedate         in pricelistpercustomer.dateactive%type default null)
    return number;



  /* ----------------------------------------------------------------------------------------------
     Procedure Get_Sales_Price_Details

     This is the base procedure used by the Sales_PriceXXX functions.
     This procedure can be used to know all details about a price.
  */
  procedure Get_Sales_Price_Details(customerid             in customer.customer_id%type,
                                    productid        in product.product_id%type,
                                    sizedefinitionid in sizedefinition.sizedefinition_id%type,
                                    pricedate        in pricelistpercustomer.dateactive%type,
                                    price            out customersalesprice.price%type,
                                    price_found_yn   out varchar2,
                                    price_start_date out customersalesprice.startdate%type,
                                    result_code      out number,
                                    result_desc      out varchar2,
                                    price_list_type  out varchar2,
                                    price_list_code  out generalpricelist.code%type,
                                    price_list_desc  out generalpricelist.description%type);

  /* ----------------------------------------------------------------------------------------------
    Function Sales_Price_Details

    Returns all price details, like procedure Get_Sales_Price_Details,
    This function returns them all concatenated in a fixed length return string.

    The return string is composed of the following elements:
    pos  1 -  12  (12): price  format: 99999.99999 ,  spaces when no price found.
    pos  13       ( 1): price returned Y / N
    pos  14 - 21  ( 8): price start date format YYYYMMDD, spaces when no price found.
    pos  22       ( 1): price list type: C=Customer specific, G=General price list, space when no price found.
    pos  23 - 28  ( 6): price list code part 1 char 1-6, spaces when no price found, or when customer specific
    pos  29 - 68  (40): price list description spaces when no price found or 'Customer Specific' when customer specific
    pos  69       ( 1): price result code , see list below.
    pos  70 - 149 (80): price result description
    pos 150 - 158 ( 9): price list code part 2 char 7-15

    Possible values for price_found_yn + result_code combinations:
    Y 0     Price found, no remark
    N 1     Price not defined, no particular reason
    N 8     Error in parameters
    N 9     ORA Error
  */
  function Sales_Price_Details(customerid        in customer.customer_id%type,
                               productid        in product.product_id%type,
                               sizedefinitionid in sizedefinition.sizedefinition_id%type,
                               pricedate        in pricelistpercustomer.dateactive%type default null)
    return varchar2;

  /* ----------------------------------------------------------------------------------------------
     Function Sales_Price
     Use this function in calculations. It will return price 0.00 in case the price could not be found.
  */
  function Sales_Price(customerid        in customer.customer_id%type,
                       productid        in product.product_id%type,
                       sizedefinitionid in sizedefinition.sizedefinition_id%type,
                       pricedate        in pricelistpercustomer.dateactive%type default null)
    return number;

  /* ----------------------------------------------------------------------------------------------
     Function Sales_Price_Display
     Use this function for display or print purposes. It will return null in case the price could not be found.
  */
  function Sales_Price_Display(customerid        in customer.customer_id%type,
                               productid        in product.product_id%type,
                               sizedefinitionid in sizedefinition.sizedefinition_id%type,
                               pricedate        in pricelistpercustomer.dateactive%type default null)
    return number;

  /*--------------------------------------------------------------------------------------------------------*/
  procedure Get_Weight_Price_Details(customerid            in customer.customer_id%type,
                                     weighingcategoryid in weighingcategory.weighingcategory_id%type,
                                     soil_yn            in customerweightprice.soil%type,
                                     pricedate          in pricelistpercustomer.dateactive%type,
                                     price              out customerweightprice.price%type,
                                     price_found_yn     out varchar2,
                                     price_start_date   out customerweightprice.startdate%type,
                                     result_code        out number,
                                     result_desc        out varchar2,
                                     price_list_type    out varchar2,
                                     price_list_code    out generalpricelist.code%type,
                                     price_list_desc    out generalpricelist.description%type);

  /* ----------------------------------------------------------------------------------------------
    Function Weight_Price_Details

    Returns all price details, like procedure Get_Weight_Price_Details,
    This function returns them all concatenated in a fixed length return string.
    Parameter soil_yn should be populated with 'Y' for soil weight price or 'N' for clean weight price.

    The return string is composed of the following elements:
    pos  1 -  12  (12): price  format: 99999.99999 ,  spaces when no price found.
    pos  13       ( 1): price returned Y / N
    pos  14 - 21  ( 8): price start date format YYYYMMDD, spaces when no price found.
    pos  22       ( 1): price list type: C=Customer specific, G=General price list, space when no price found.
    pos  23 - 28  ( 6): price list code part 1 char 1-6, spaces when no price found, or when customer specific
    pos  29 - 68  (40): price list description spaces when no price found or 'Customer Specific' when customer specific
    pos  69       ( 1): price result code , see list below.
    pos  70 - 149 (80): price result description
    pos 150 - 158 ( 9): price list code part 2 char 7-15

    Possible values for price_found_yn + result_code combinations:
    Y 0     Price found, no remark
    N 1     Price not defined, no particular reason
    N 2     Price not relevant, weights not invoiced
    N 3     Price not defined, weights invoiced soil, not clean
    N 4     Price not defined, weights invoiced clean, not soil
    N 8     Error in parameters
    N 9     ORA Error
  */

  function Weight_Price_Details(customerid         in customer.customer_id%type,
                                weighingcategoryid in weighingcategory.weighingcategory_id%type,
                                soil_yn            in customerweightprice.soil%type,
                                pricedate          in pricelistpercustomer.dateactive%type default null)
    return varchar2;

  /* ----------------------------------------------------------------------------------------------
     Function Weight_Price
     Use this function in calculations. It will return price 0.00 in case the price could not be found.
  */
  function Weight_Price(customerid         in customer.customer_id%type,
                        weighingcategoryid in weighingcategory.weighingcategory_id%type,
                        soil_yn            in customerweightprice.soil%type,
                        pricedate          in pricelistpercustomer.dateactive%type default null)
    return number;

  /* ----------------------------------------------------------------------------------------------
     Function Weight_Price_Display
     Use this function for display or print purposes. It will return null in case the price could not be found.
  */
  function Weight_Price_Display(customerid         in customer.customer_id%type,
                                weighingcategoryid in weighingcategory.weighingcategory_id%type,
                                soil_yn            in customerweightprice.soil%type,
                                pricedate          in pricelistpercustomer.dateactive%type default null)
    return number;

  /*--------------------------------------------------------------------------------------------------------*/
  procedure Get_SpecCharge_Details(customerid     in customer.customer_id%type,
                                   specialchargeid  in specialcharge.specialcharge_id%type,
                                   pricedate        in pricelistpercustomer.dateactive%type,
                                   price            out customerspecialchargeprice.price%type,
                                   price_found_yn   out varchar2,
                                   price_start_date out customerspecialchargeprice.startdate%type,
                                   result_code      out number,
                                   result_desc      out varchar2,
                                   price_list_type  out varchar2,
                                   price_list_code  out generalpricelist.code%type,
                                   price_list_desc  out generalpricelist.description%type,
                                   specialchargecode      out specialcharge.code%type,
                                   percentage       out customerspecialchargeprice.percentage%type,
                                   minimumamount    out specialcharge.minimumamount%type,
                                   maximumamount    out specialcharge.maximumamount%type);

  /* ----------------------------------------------------------------------------------------------
    Function SpecialCharge_Details

    Returns all price details, like procedure Get_SpecialCharge_Price_Details,
    This function returns them all concatenated in a fixed length return string.

    The return string is composed of the following elements:
    pos  1 -  12  (12): price format: 99999.99999 ,  spaces when no price found or not applicable.
    pos  13       ( 1): price or percentage returned Y / N
    pos  14 - 21  ( 8): price or percentage start date format YYYYMMDD, spaces when no price found.
    pos  22       ( 1): price list type: C=Customer specific, G=General price list, B=Base table price, space when no price found.
    pos  23 - 28  ( 6): price list code part 1 char 1-6, spaces when no price found, or when customer specific
    pos  29 - 68  (40): price list description spaces when no price found or 'Customer Specific' when customer specific
    pos  69 - 74  ( 6): special charge code
    pos  75       ( 1): price result code , see list below.
    pos  76 - 155 (80): price result description
    pos 156 - 162 ( 7): percentage fromat: 999.99, spaces when not found or not applicable.
    pos 163 - 172 (10): minimum amount fromat: 999999.99, spaces when not found or not applicable.
    pos 173 - 182 (10): maximum amount fromat: 999999.99, spaces when not found or not applicable.
    pos 183 - 191 ( 9): price list code part 2 char 7-15

    Possible values for price_found_yn + result_code combinations:
    Y 0     Price or percentage found, no remark
    N 1     Price or percentage not defined, no particular reason
    N 8     Error in parameters
    N 9     ORA Error
  */

  function SpecialCharge_Details(customerid  in customer.customer_id%type,
                                 specialchargeid in specialcharge.specialcharge_id%type,
                                 pricedate       in pricelistpercustomer.dateactive%type default null)
    return varchar2;

  /* ----------------------------------------------------------------------------------------------
     Function SpecialCharge_Price
     Use this function in calculations. It will return price 0.00 in case the price could not be found.
  */
  function SpecialCharge_Price(customerid  in customer.customer_id%type,
                               specialchargeid in specialcharge.specialcharge_id%type,
                               pricedate       in pricelistpercustomer.dateactive%type default null)
    return number;

  /* ----------------------------------------------------------------------------------------------
     Function SpecialCharge_Price_Display
     Use this function for display or print purposes. It will return null in case the price could not be found.
  */
  function SpecialCharge_Price_Display(customerid  in customer.customer_id%type,
                                       specialchargeid in specialcharge.specialcharge_id%type,
                                       pricedate       in pricelistpercustomer.dateactive%type default null)
    return number;
  /* ----------------------------------------------------------------------------------------------
     Function SpecialCharge_Percentage
     Use this function in calculations. It will return percentage 0.00 in case the percentage could not be found.
  */
  function SpecialCharge_Percentage(customerid  in customer.customer_id%type,
                                    specialchargeid in specialcharge.specialcharge_id%type,
                                    pricedate       in pricelistpercustomer.dateactive%type default null)
    return number;

  /* ----------------------------------------------------------------------------------------------
     Function SpecialCharge_Perc_Display
     Use this function for display or print purposes. It will return null in case the percentage could not be found.
  */
  function SpecialCharge_Percentage_Disp(customerid  in customer.customer_id%type,
                                         specialchargeid in specialcharge.specialcharge_id%type,
                                         pricedate       in pricelistpercustomer.dateactive%type default null)
    return number;
  /* ----------------------------------------------------------------------------------------------
     Function SpecialCharge_MinAmount
     Use this function in calculations. It will return MinAmount 0.00 in case the MinAmount could not be found.
  */
  function SpecialCharge_MinAmount(customerid  in customer.customer_id%type,
                                   specialchargeid in specialcharge.specialcharge_id%type,
                                   pricedate       in pricelistpercustomer.dateactive%type default null)
    return number;

  /* ----------------------------------------------------------------------------------------------
     Function SpecialCharge_MinAmount_Disp
     Use this function for display or print purposes. It will return null in case the MinAmount could not be found.
  */
  function SpecialCharge_MinAmount_Disp(customerid  in customer.customer_id%type,
                                        specialchargeid in specialcharge.specialcharge_id%type,
                                        pricedate       in pricelistpercustomer.dateactive%type default null)
    return number;
  /* ----------------------------------------------------------------------------------------------
     Function SpecialCharge_MaxAmount
     Use this function in calculations. It will return MaxAmount 0.00 in case the MaxAmount could not be found.
  */
  function SpecialCharge_MaxAmount(customerid  in customer.customer_id%type,
                                   specialchargeid in specialcharge.specialcharge_id%type,
                                   pricedate       in pricelistpercustomer.dateactive%type default null)
    return number;

  /* ----------------------------------------------------------------------------------------------
     Function SpecialCharge_MaxAmount_Disp
     Use this function for display or print purposes. It will return null in case the MaxAmount could not be found.
  */
  function SpecialCharge_MaxAmount_Disp(customerid  in customer.customer_id%type,
                                        specialchargeid in specialcharge.specialcharge_id%type,
                                        pricedate       in pricelistpercustomer.dateactive%type default null)
    return number;
  /*--------------------------------------------------------------------------------------------------------*/
  procedure Get_Rent_Discount_Details(customerid            in customer.customer_id%type,
                                      productid           in product.product_id%type,
                                      sizedefinitionid    in sizedefinition.sizedefinition_id%type,
                                      finishingmethodid   in finishingmethod.finishingmethod_id%type,
                                      discountdate          in Discountlistpercustomer.dateactive%type,
                                      discount              out customerrentdiscount.Discount%type,
                                      discount_found_yn   out varchar2,
                                      discount_start_date out customerrentdiscount.startdate%type,
                                      result_code         out number,
                                      result_desc         out varchar2,
                                      discount_list_type  out varchar2,
                                      discount_list_code  out generaldiscountlist.code%type,
                                      discount_list_desc  out generaldiscountlist.description%type);

  /* ----------------------------------------------------------------------------------------------
    Function Rent_Discount_Details

    Returns all Discount details, like procedure Get_Rent_Discount_Details,
    This function returns them all concatenated in a fixed length return string.

    The return string is composed of the following elements:
    pos  1 -  12  (12): Discount % format: 99999.99999 ,  spaces when no Discount found. Max 2 decimals
    pos  13       ( 1): Discount returned Y / N
    pos  14 - 21  ( 8): Discount start date format YYYYMMDD, spaces when no Discount found.
    pos  22       ( 1): Discount list type: C=Customer specific, G=General Discount list, space when no Discount found.
    pos  23 - 28  ( 6): Discount list code spaces when no Discount found, or when customer specific
    pos  29 - 68  (40): Discount list description spaces when no Discount found or 'Customer Specific' when customer specific
    pos  69       ( 1): Discount result code , see list below.
    pos  70 - 149 (80): Discount result description

    Possible values for Discount_found_yn + result_code combinations:
    Y 0     Discount found, no remark
    N 1     Discount not defined, no particular reason
    N 2     Discount not relevant, billing method with wash price only
    N 3     Discount not relevant, billing method = 4 piece rate only
    N 4     Discount not applicable, price defined on wearer level as wearer total price.
    N 5     Discount not applicable, price defined at wearer function level
    N 8     Discount in parameters
    N 9     ORA Error
  */

  function Rent_Discount_Details(customerid         in customer.customer_id%type,
                                 productid         in product.product_id%type,
                                 sizedefinitionid  in sizedefinition.sizedefinition_id%type,
                                 finishingmethodid in finishingmethod.finishingmethod_id%type,
                                 Discountdate       in discountlistpercustomer.dateactive%type default null)
    return varchar2;

  /* ----------------------------------------------------------------------------------------------
     Function Wash_Discount
     Use this function in calculations. It will return Discount 0.00 in case the Discount could not be found.
  */

  function Rent_Discount(customerid         in customer.customer_id%type,
                         productid         in product.product_id%type,
                         sizedefinitionid  in sizedefinition.sizedefinition_id%type,
                         finishingmethodid in finishingmethod.finishingmethod_id%type,
                         discountdate      in discountlistpercustomer.dateactive%type default null)
    return number;

  /* ----------------------------------------------------------------------------------------------
     Function Wash_Discount_Display
     Use this function for display or print purposes. It will return null in case the Discount could not be found.
  */

  function Rent_Discount_Display(customerid         in customer.customer_id%type,
                                 productid         in product.product_id%type,
                                 sizedefinitionid  in sizedefinition.sizedefinition_id%type,
                                 finishingmethodid in finishingmethod.finishingmethod_id%type,
                                 discountdate      in discountlistpercustomer.dateactive%type default null)
    return number;


  /*--------------------------------------------------------------------------------------------------------*/
  procedure Get_Wash_Discount_Details(customerid            in customer.customer_id%type,
                                      productid           in product.product_id%type,
                                      sizedefinitionid    in sizedefinition.sizedefinition_id%type,
                                      finishingmethodid   in finishingmethod.finishingmethod_id%type,
                                      discountdate          in Discountlistpercustomer.dateactive%type,
                                      discount              out customerwashdiscount.Discount%type,
                                      discount_found_yn   out varchar2,
                                      discount_start_date out customerwashdiscount.startdate%type,
                                      result_code         out number,
                                      result_desc         out varchar2,
                                      discount_list_type  out varchar2,
                                      discount_list_code  out generaldiscountlist.code%type,
                                      discount_list_desc  out generaldiscountlist.description%type);

  /* ----------------------------------------------------------------------------------------------
    Function Wash_Discount_Details

    Returns all Discount details, like procedure Get_Wash_Discount_Details,
    This function returns them all concatenated in a fixed length return string.

    The return string is composed of the following elements:
    pos  1 -  12  (12): Discount % format: 99999.99999 ,  spaces when no Discount found. Max 2 decimals
    pos  13       ( 1): Discount returned Y / N
    pos  14 - 21  ( 8): Discount start date format YYYYMMDD, spaces when no Discount found.
    pos  22       ( 1): Discount list type: C=Customer specific, G=General Discount list, space when no Discount found.
    pos  23 - 28  ( 6): Discount list code spaces when no Discount found, or when customer specific
    pos  29 - 68  (40): Discount list description spaces when no Discount found or 'Customer Specific' when customer specific
    pos  69       ( 1): Discount result code , see list below.
    pos  70 - 149 (80): Discount result description

    Possible values for Discount_found_yn + result_code combinations:
    Y 0     Discount found, no remark
    N 1     Discount not defined, no particular reason
    N 2     Discount not relevant, billing method 3 with rent price only
    N 3     Discount not defined, billing method = 1 flat rated rent
    N 4     Discount not defined, billing method = 2 rent based on minimum
    N 5     Discount not relevant, disposable / sales only item
    N 8     Error in parameters
    N 9     ORA Error
  */

  function Wash_Discount_Details(customerid         in customer.customer_id%type,
                                 productid         in product.product_id%type,
                                 sizedefinitionid  in sizedefinition.sizedefinition_id%type,
                                 finishingmethodid in finishingmethod.finishingmethod_id%type,
                                 Discountdate       in discountlistpercustomer.dateactive%type default null)
    return varchar2;

  /* ----------------------------------------------------------------------------------------------
     Function Wash_Discount
     Use this function in calculations. It will return Discount 0.00 in case the Discount could not be found.
  */

  function Wash_Discount(customerid         in customer.customer_id%type,
                         productid         in product.product_id%type,
                         sizedefinitionid  in sizedefinition.sizedefinition_id%type,
                         finishingmethodid in finishingmethod.finishingmethod_id%type,
                         discountdate      in discountlistpercustomer.dateactive%type default null)
    return number;

  /* ----------------------------------------------------------------------------------------------
     Function Wash_Discount_Display
     Use this function for display or print purposes. It will return null in case the Discount could not be found.
  */

  function Wash_Discount_Display(customerid         in customer.customer_id%type,
                                 productid         in product.product_id%type,
                                 sizedefinitionid  in sizedefinition.sizedefinition_id%type,
                                 finishingmethodid in finishingmethod.finishingmethod_id%type,
                                 discountdate      in discountlistpercustomer.dateactive%type default null)
    return number;

  /*--------------------------------------------------------------------------------------------------------*/
  procedure Get_Weight_Discount_Details(customerid            in customer.customer_id%type,
                                        weighingcategoryid  in weighingcategory.weighingcategory_id%type,
                                        soil_yn             in customerweightdiscount.soil%type,
                                        discountdate        in discountlistpercustomer.dateactive%type,
                                        discount            out customerweightdiscount.discount%type,
                                        discount_found_yn   out varchar2,
                                        discount_start_date out customerweightdiscount.startdate%type,
                                        result_code         out number,
                                        result_desc         out varchar2,
                                        discount_list_type  out varchar2,
                                        discount_list_code  out generaldiscountlist.code%type,
                                        discount_list_desc  out generaldiscountlist.description%type);

  /* ----------------------------------------------------------------------------------------------
    Function Weight_Discount_Details

    Returns all Discount details, like procedure Get_Weight_Discount_Details,
    This function returns them all concatenated in a fixed length return string.
    Parameter soil_yn should be populated with 'Y' for soil weight Discount or 'N' for clean weight Discount.

    The return string is composed of the following elements:
    pos  1 -  12  (12): Discount % format: 99999.99999 ,  spaces when no Discount found. Max 2 decimals
    pos  13       ( 1): Discount returned Y / N
    pos  14 - 21  ( 8): Discount start date format YYYYMMDD, spaces when no Discount found.
    pos  22       ( 1): Discount list type: C=Customer specific, G=General Discount list, space when no Discount found.
    pos  23 - 28  ( 6): Discount list code spaces when no Discount found, or when customer specific
    pos  29 - 68  (40): Discount list description spaces when no Discount found or 'Customer Specific' when customer specific
    pos  69       ( 1): Discount result code , see list below.
    pos  70 - 149 (80): Discount result description

    Possible values for Discount_found_yn + result_code combinations:
    Y 0     Discount found, no remark
    N 1     Discount not defined, no particular reason
    N 2     Discount not relevant, weights not invoiced
    N 3     Discount not defined, weights invoiced soil, not clean
    N 4     Discount not defined, weights invoiced clean, not soil
    N 8     Error in parameters
    N 9     ORA Error
  */

  function Weight_Discount_Details(customerid         in customer.customer_id%type,
                                   weighingcategoryid in weighingcategory.weighingcategory_id%type,
                                   soil_yn            in customerweightdiscount.soil%type,
                                   Discountdate       in discountlistpercustomer.dateactive%type default null)
    return varchar2;

  /* ----------------------------------------------------------------------------------------------
     Function Weight_Discount
     Use this function in calculations. It will return Discount 0.00 in case the Discount could not be found.
  */
  function Weight_Discount(customerid         in customer.customer_id%type,
                           weighingcategoryid in weighingcategory.weighingcategory_id%type,
                           soil_yn            in customerweightdiscount.soil%type,
                           discountdate       in discountlistpercustomer.dateactive%type default null)
    return number;

  /* ----------------------------------------------------------------------------------------------
     Function Weight_Discount_Display
     Use this function for display or print purposes. It will return null in case the Discount could not be found.
  */
  function Weight_Discount_Display(customerid         in customer.customer_id%type,
                                   weighingcategoryid in weighingcategory.weighingcategory_id%type,
                                   soil_yn            in customerweightdiscount.soil%type,
                                   discountdate       in discountlistpercustomer.dateactive%type default null)
    return number;

/* ----------------------------------------------------------------------------------------------
    Procedure Get_ResidentialRent_Price_Details

    This is the base procedure used by the ResidentialRent_PriceXXX functions.
    This procedure can be used to know all details about a price.
  */
  procedure Get_ResidRent_Price_Details(customerid             in customer.customer_id%type,
                                        productid              in product.product_id%type,
                                        sizedefinitionid       in sizedefinition.sizedefinition_id%type,
                                        finishingmethodid      in finishingmethod.finishingmethod_id%type,
                                        pricedate              in pricelistpercustomer.dateactive%type,
                                        price                  out customerrentprice.price%type,
                                        price_found_yn         out varchar2,
                                        price_start_date       out customerrentprice.startdate%type,
                                        result_code            out number,
                                        result_desc            out varchar2,
                                        price_list_type        out varchar2,
                                        price_list_code        out generalpricelist.code%type,
                                        price_list_desc        out generalpricelist.description%type,
                                        price_type_code        out customerrentprice.typeofrent%type,
                                        price_type_desc        out varchar2);

  /* ----------------------------------------------------------------------------------------------
    Function ResidentialRent_Price_Details

    Returns all price details, like procedure Get_ResidentialRent_Price_Details,
    This function returns them all concatenated in a fixed length return string.

    The return string is composed of the following elements:
    pos  1 -  12  (12): price  format: 99999.99999 ,  spaces when no price found.
    pos  13       ( 1): price returned Y / N
    pos  14 - 21  ( 8): price start date format YYYYMMDD, spaces when no price found.
    pos  22       ( 1): price list type: C=Customer specific, G=General price list, space when no price found.
    pos  23 - 28  ( 6): price list code part 1 char 1-6, spaces when no price found, or when customer specific
    pos  29 - 68  (40): price list description spaces when no price found or 'Customer Specific' when customer specific
    pos  69       ( 1): price result code , see list below.
    pos  70 - 149 (80): price result description
    pos 150       ( 1): price type code
    pos 151 - 180 (30): price type description
    pos 181 - 190 (10): found price is defined for this number of changes per week
    pos 191 - 199 ( 9): price list code part 2 char 7-15

    Possible values for price_found_yn + result_code combinations:
    Y 0     Price found, no remark
    N 1     Price not defined, no particular reason
    N 2     Price not relevant, billing method with wash price only
    N 3     Price not relevant, billing method = 4 piece rate only
    N 4     Price not relevant, defined on wearer level as wearer total price.
    N 5     Price not relevant, defined at wearer function level
    N 8     Error in parameters
    N 9     ORA Error
  */
  function ResidentialRent_Price_Details(customerid        in customer.customer_id%type,
                                         productid         in product.product_id%type,
                                         sizedefinitionid  in sizedefinition.sizedefinition_id%type,
                                         finishingmethodid in finishingmethod.finishingmethod_id%type,
                                         pricedate         in pricelistpercustomer.dateactive%type default null)
  return varchar2;

  /* ----------------------------------------------------------------------------------------------
     Function ResidentialRent_Price
     Use this function in calculations. It will return price 0.00 in case the price could not be found.
  */
  function ResidentialRent_Price(customerid        in customer.customer_id%type,
                                 productid         in product.product_id%type,
                                 sizedefinitionid  in sizedefinition.sizedefinition_id%type,
                                 finishingmethodid in finishingmethod.finishingmethod_id%type,
                                 pricedate         in pricelistpercustomer.dateactive%type default null)
  return number;

  /* ----------------------------------------------------------------------------------------------
     Function ResidentialRent_Price_Display
     Use this function for display or print purposes. It will return null in case the price could not be found.
  */
  function ResidentialRent_Price_Display(customerid        in customer.customer_id%type,
                                         productid         in product.product_id%type,
                                         sizedefinitionid  in sizedefinition.sizedefinition_id%type,
                                         finishingmethodid in finishingmethod.finishingmethod_id%type,
                                         pricedate         in pricelistpercustomer.dateactive%type default null)
  return number;

  /* ----------------------------------------------------------------------------------------------
    Procedure Get_Loan_Price_Details

    This is the base procedure used by the Rent_PriceXXX functions.
    This procedure can be used to know all details about a price.
  */
  procedure Get_Loan_Price_Details(customerid             in customer.customer_id%type,
                                   productid              in product.product_id%type,
                                   sizedefinitionid       in sizedefinition.sizedefinition_id%type,
                                   finishingmethodid      in finishingmethod.finishingmethod_id%type,
                                   pricedate              in pricelistpercustomer.dateactive%type,
                                   price                  out customerrentprice.price%type,
                                   price_found_yn         out varchar2,
                                   price_start_date       out customerrentprice.startdate%type,
                                   result_code            out number,
                                   result_desc            out varchar2,
                                   price_list_type        out varchar2,
                                   price_list_code        out generalpricelist.code%type,
                                   price_list_desc        out generalpricelist.description%type);

  /* ----------------------------------------------------------------------------------------------
    Function Loan_Price_Details

    Returns all price details, like procedure Get_Loan_Price_Details,
    This function returns them all concatenated in a fixed length return string.

    The return string is composed of the following elements:
    pos  1 -  12  (12): price  format: 99999.99999 ,  spaces when no price found.
    pos  13       ( 1): price returned Y / N
    pos  14 - 21  ( 8): price start date format YYYYMMDD, spaces when no price found.
    pos  22       ( 1): price list type: C=Customer specific, G=General price list, space when no price found.
    pos  23 - 28  ( 6): price list code part 1 char 1-6, spaces when no price found, or when customer specific
    pos  29 - 68  (40): price list description spaces when no price found or 'Customer Specific' when customer specific
    pos  69       ( 1): price result code , see list below.
    pos  70 - 149 (80): price result description
    pos 150 - 158 ( 9): price list code part 2 char 7-15

    Possible values for price_found_yn + result_code combinations:
    Y 0     Price found, no remark
    N 1     Price not defined, no particular reason
    N 8     Error in parameters
    N 9     ORA Error
  */
  function Loan_Price_Details(customerid        in customer.customer_id%type,
                              productid         in product.product_id%type,
                              sizedefinitionid  in sizedefinition.sizedefinition_id%type,
                              finishingmethodid in finishingmethod.finishingmethod_id%type,
                              pricedate         in pricelistpercustomer.dateactive%type default null)
  return varchar2;

  /* ----------------------------------------------------------------------------------------------
     Function Loan_Price
     Use this function in calculations. It will return price 0.00 in case the price could not be found.
  */
  function Loan_Price(customerid        in customer.customer_id%type,
                      productid         in product.product_id%type,
                      sizedefinitionid  in sizedefinition.sizedefinition_id%type,
                      finishingmethodid in finishingmethod.finishingmethod_id%type,
                      pricedate         in pricelistpercustomer.dateactive%type default null)
  return number;

  /* ----------------------------------------------------------------------------------------------
     Function Loan_Price_Display
     Use this function for display or print purposes. It will return null in case the price could not be found.
  */
  function Loan_Price_Display(customerid        in customer.customer_id%type,
                              productid         in product.product_id%type,
                              sizedefinitionid  in sizedefinition.sizedefinition_id%type,
                              finishingmethodid in finishingmethod.finishingmethod_id%type,
                              pricedate         in pricelistpercustomer.dateactive%type default null)
  return number;

  /* ----------------------------------------------------------------------------------------------
     Function Budget_Price
     Use this function in calculations. It will return price 0.00 in case the price could not be found.
  */
  function Budget_Price (
       FPcustomerid customerbudgetdefinition.customer_id%type,
       FPcode       customerbudgetdefinition.code%type,
       FPreferencedate   date
       )
  return number;
  
      
  function SizeBillingGroups_Defined return boolean;
  function Customer_Uses_SpecialPrices(customerid in customer.customer_id%type) return boolean;
  function Get_Standard_SzBillingGroup return number;
  function Get_Product_Size_Billinggroup(productid in product.product_id%type,
                                         sizeid    in sizedefinition.sizedefinition_id%type) return number;
  procedure Get_Size_Billing_Groups(customerid                     in customer.customer_id%type,
                                    productid                      in product.product_id%type,
                                    sizedefinitionid               in sizedefinition.sizedefinition_id%type,
                                    lv_standard_sizebillinggroupid out sizebillinggroup.sizebillinggroup_id%type,
                                    lv_specific_sizebillinggroupid out sizebillinggroup.sizebillinggroup_id%type);
end PACK_PRICE;
/
CREATE OR REPLACE PACKAGE BODY PACK_PRICE is

  procedure Get_Rent_Price_Details(customerid             in customer.customer_id%type,
                                  productid              in product.product_id%type,
                                  sizedefinitionid       in sizedefinition.sizedefinition_id%type,
                                  finishingmethodid      in finishingmethod.finishingmethod_id%type,
                                  pricedate              in pricelistpercustomer.dateactive%type,
                                  chgperwk_or_freq       in generalrentprice.changesperweek%type,
                                  price                  out customerrentprice.price%type,
                                  price_found_yn         out varchar2,
                                  price_start_date       out customerrentprice.startdate%type,
                                  result_code            out number,
                                  result_desc            out varchar2,
                                  price_list_type        out varchar2,
                                  price_list_code        out generalpricelist.code%type,
                                  price_list_desc        out generalpricelist.description%type,
                                  changesperwkfoundprice out customerrentprice.changesperweek%type,
                                  price_type_code        out customerrentprice.typeofrent%type,
                                  price_type_desc        out varchar2,
                                  inv_freq_code          out customerrentprice.invoicefrequencynumber%type,
                                  inv_freq_desc          out varchar2,
                                  customerrentpriceid    out customerrentprice.customerrentprice_id%type) is

    price_found                 boolean;
    price_not_relevant          boolean;
    lv_size                     sizedefinition.sizedefinition_id%type;
    lv_finmethod                finishingmethod.finishingmethod_id%type;
    i                           number;
    dummy                       number;
    lv_pricedate                date;
    lv_changesperweek           customerrentprice.changesperweek%type;
    lv_changesperweekfound      customerrentprice.changesperweek%type;
    lv_productlistpercustomerid productlistpercustomer.productlistpercustomer_id%type;
    lv_billingmethod            productlistpercustomer.billingmethod%type;
    lv_priceusage               productlistpercustomer.priceusage%type;
    lv_frequencybased           productlistpercustomer.rentpriceperfrequency%type;
    --PVD added
    lv_minimumturnaround        productlistpercustomer.minimumturnaround%type;
    lv_businessunitid           businessunit.businessunit_id%type;
    lv_underuse_count           number;
    --
    lv_customergarmrentspec     customer.garmentrentspecification%type;
    lv_pricelist_type           varchar2(1);
    lv_invfreqbasedprice        productlistpercustomer.invoicefreqrelatedrentprice%type;
    lv_custsplitinvperactivity  customer.splitinvoicesperactivity%type;
    lv_typeofmerchandize        product.typeofmerchandise%type;
    lv_typeofrent               customerrentprice.typeofrent%type;
    lv_typeofrent_general       customerrentprice.typeofrent%type;
    lv_typeofrent_specific      customerrentprice.typeofrent%type;
    lv_invoice_freq             customerrentprice.invoicefrequencynumber%type;
    lv_productgroup_id          product.productgroup_id%type;
    lv_custrentpriceid          customerrentprice.customerrentprice_id%type;
    lv_price                    customerrentprice.price%type;
    lv_price_start_date         customerrentprice.startdate%type;
    lv_generalpricelistid       generalrentprice.generalpricelist_id%type;
    lv_pricelist_code           generalpricelist.code%type;
    lv_pricelist_desc           generalpricelist.description%type;
    type Tcsr                   is ref cursor;
    Vcsr                        Tcsr;
    VPriceListperCust           Tcsr;
    lv_standard_sizeBillingGroupID sizebillinggroup.sizebillinggroup_id%type;
    lv_specific_sizeBillingGroupID sizebillinggroup.systemuser_id%type;
    function decode_error(errorcode in varchar2) return varchar2 is
      r varchar2(80);
    begin
      select decode(errorcode,
                    'Y0',
                    'Price found',
                    'Y1',
                    'Multiple prices can be applicable for different # of changes',
                    'Y2',
                    'Multiple prices can be applicable for different frequencies',
                    'N1',
                    'Price not defined',
                    'N2',
                    'Price not relevant, billing method 3 with wash price only',
                    'N3',
                    'Price not relevant, billing method = 4 piece rate only',
                    'N4',
                    'Price not relevant, defined at wearer level as wearer total price',
                    'N5',
                    'Price not relevant, defined at wearer function level',
                    'N8',
                    'Error in parameters',
                    'N9',
                    'ORA Error',
                    'Unknown error code')
        into r
        from dual;
      return r;
    end;

    function decode_inv_freq(fpfreq_code in varchar2) return varchar2 is
    begin
      case fpfreq_code
        when '40' then
          return('4 Weekly');
        when '110' then
          return('Monthly');
        else
          return('Weekly');
      end case;
    end;

    function decode_type_of_rent(fptypeofrent in number) return varchar2 is
      r varchar2(30);
    begin
      select decode(fptypeofrent,
                    1,
                    'Price per Piece',
                    2,
                    'Changes per Week',
                    3,
                    'Overuse Price',
                    4,
                    'Frequency Based Price',
                    5,
                    'Underuse Price',
                    6,
                    'Lease Price',
                    7,
                    'Split Price',
                    8,
                    'Price per Invoice Freq.',
                    'Unknown type of rent')
        into r
        from dual;
      return r;
    end;     

  begin -- main rent price
    /* ------------------------ I N I T I A L I S A T I O N ------------------------------------------------------*/
    price_found            := false;
    price_not_relevant     := false;
    price                  := null;
    price_found_yn         := 'N';
    price_start_date       := null;
    result_code            := null;
    result_desc            := null;
    price_list_type        := null;
    price_list_code        := null;
    price_list_desc        := null;
    changesperwkfoundprice := null;
    price_type_code        := null;
    price_type_desc        := null;
    inv_freq_code          := null;
    inv_freq_desc          := null;
    
    get_size_billing_groups(customerid, productid, sizedefinitionid, lv_standard_sizeBillingGroupID, lv_specific_sizeBillingGroupID);
    
    if pricedate is null then
      select sysdate into lv_pricedate from dual;
    else
      lv_pricedate := pricedate;
    end if;
    lv_size           := sizedefinitionid;
    lv_finmethod      := finishingmethodid;
--    lv_changesperweek := chgperwk_or_freq;  HVH 08/01/2010 change moved inside outer loop
    if (customerid is null) OR (productid is null) OR
       (sizedefinitionid is null) OR (finishingmethodid is null) then
      result_code := 8; -- error in parameters
    end if;
    i := 1;
    -- start outer loop on exact match, size * etc.
    /* ############################ O U T E R L O O P ############################################################*/
    while (i < 5) and (result_code is null) and (not price_found) and
          (not price_not_relevant) loop
      open VPriceListperCust for
        Select pricelistpercustomer.generalpricelist_id
          from pricelistpercustomer
         where pricelistpercustomer.customer_id = customerid
           and pricelistpercustomer.pricetype = 2
           and pricelistpercustomer.dateactive <= lv_pricedate
           and pricelistpercustomer.dateinactive >= lv_pricedate
         order by pricelistpercustomer.priority;
      fetch VPriceListperCust
        into lv_generalpricelistid;
      lv_changesperweek           := chgperwk_or_freq;  -- HVH 08/01/2010 moved from outside loop
      -- load billingmethod etc to determine price usage
      lv_productlistpercustomerid := null;
      lv_billingmethod            := null;
      lv_priceusage               := null;
      lv_invoice_freq             := 10; -- default is weekly
      lv_custsplitinvperactivity  := null;
      lv_minimumturnaround        := null;
      open Vcsr for
        select plpc.productlistpercustomer_id,
               plpc.billingmethod,
               plpc.priceusage,
               plpc.invoicefreqrelatedrentprice,
               plpc.rentpriceperfrequency,
               p.typeofmerchandise,
               p.productgroup_id,
               c.garmentrentspecification,
               c.splitinvoicesperactivity,
               --PVD added
               plpc.minimumturnaround,
               C.DEFAULTBUSINESSUNIT_ID
          from productlistpercustomer plpc, product p, customer c
         where plpc.customer_id = customerid
           and plpc.product_id = productid
           and p.product_id = productid
           and c.customer_id = customerid
           and plpc.sizedefinition_id = lv_size
           and plpc.finishingmethod_id = lv_finmethod
           and ((lv_pricedate >= plpc.dateactive and lv_pricedate <= plpc.dateinactive) or i>2)
           -- only filter on active in iteration 3 and 4
         order by plpc.dateactive desc;
      if Vcsr%notfound then
        null;
      else
        fetch Vcsr
          into lv_productlistpercustomerid, lv_billingmethod, lv_priceusage, lv_invfreqbasedprice,
               lv_frequencybased, lv_typeofmerchandize, lv_productgroup_id, lv_customergarmrentspec,
               lv_custsplitinvperactivity, lv_minimumturnaround, lv_businessunitid;
        if lv_billingmethod = 3 and lv_priceusage = 3 then /*wash only*/
          price_not_relevant := true;
          result_code        := 2; -- Price not relevant, billing method with wash price only
        end if;
        if lv_billingmethod = 4 then
          price_not_relevant := true;
          result_code        := 3; -- Price not relevant, billing method = 4 piece rate only
        end if;
        if lv_billingmethod = 1 and lv_typeofmerchandize=2 and lv_customergarmrentspec=5 then -- total wearer price
          price_not_relevant := true;
          result_code        := 4; -- Price not relevant, wearer total price
        end if;
        if lv_billingmethod = 1 and lv_typeofmerchandize=2 and lv_customergarmrentspec=6 then -- wearer function price
          price_not_relevant := true;
          result_code        := 5; -- Price not relevant, wearer function price
        end if;
        if (not price_found) and (not price_not_relevant) then
          lv_typeofrent := 1;
          if lv_billingmethod = 1 and lv_typeofmerchandize = 2 -- changes per week
           then
            lv_typeofrent := 2; -- changes per week
           else
            if lv_billingmethod = 1 and lv_typeofmerchandize <> 2 and lv_frequencybased='Y'
              then lv_typeofrent := 4;  -- frequency based
              else lv_changesperweek := 0;
            end if; -- frequency based=Y
          end if; -- changes/week
          if lv_billingmethod = 3 and lv_priceusage=1 /* split */
            then lv_typeofrent := 7; -- split
          end if;
          if lv_billingmethod = 5
            then lv_typeofrent := 6; -- lease
          end if;
          --PVD add underuse
          if lv_billingmethod = 2 and lv_minimumturnaround <> 0 then
            --check_underuse
            select count(1)
            into   lv_underuse_count
            from   systemsetting st
            join   businessunit bu
            on     bu.plant_id = st.plant_id
            where  st.name = 'UNDERUSEMETHOD'
            and    st.value in ('2', '4')
            and    bu.businessunit_id = lv_businessunitid;
            if lv_underuse_count > 0 then
              --underuse
              lv_typeofrent := 5;
            end if;
          end if;
          --
          if lv_billingmethod = 3 and lv_invfreqbasedprice = 'Y' and lv_custsplitinvperactivity='Y' then
            lv_typeofrent := 8;
            -- determine invoice frequency. Take lowest frequency,(to avoid BU determination) assuming that
            -- an activity for a customer is processed in only 1 plant,
            -- or if in multiple they have the same invoice frequency.
            select min(freq.invoicefrequencynumber) -- take lowest freq.
              into lv_invoice_freq
              from customeractivities ca,
                   invoicefrequency   freq,
                   productgroup       pg
             where ca.customer_id = customerid
               and ca.activity_id = pg.activity_id
               and pg.productgroup_id = lv_productgroup_id
               and freq.invoicefrequency_id = ca.invoicefrequency_id;
            if lv_invoice_freq < 100 and lv_invoice_freq <> 40 then
              lv_invoice_freq := 10;
            end if;
            if lv_invoice_freq > 100 then
              lv_invoice_freq := 110;
            end if;
          end if;
          lv_typeofrent_general := lv_typeofrent;
          lv_typeofrent_specific := lv_typeofrent;
          /*
          if lv_typeofrent_specific = 7 -- split
            then lv_typeofrent_specific := 1;
          end if;
          */
        end if;
      end if;
      close Vcsr;
      -- start inner loop on pricelistpercustomer
      /* --------------------- I N N E R L O O P ----------------------------------------------------------*/
      while (not VPriceListperCust%notfound) and (not price_found) and
            (not price_not_relevant) loop
        --if not price_found then
        if lv_generalpricelistid is not null then
          -- general price list
          begin
            lv_price            := null;
            lv_price_start_date := null;
            lv_pricelist_type   := 'G'; -- general
            if lv_changesperweek = 0 -- no specific changes passed, take most recent active price
             then
              open Vcsr for
                select x.*, rownum
                  from (select grp.price,
                               grp.startdate,
                               grp.changesperweek,
                               g.code,
                               g.description
                          from generalrentprice grp, generalpricelist g
                         where g.generalpricelist_id = lv_generalpricelistid
                           and g.pricetype = 2
                           and g.active = 'Y'
                           and grp.generalpricelist_id = g.generalpricelist_id
                           and grp.product_id = productid
                           and grp.sizedefinition_id = lv_size
                           and grp.finishingmethod_id = lv_finmethod
                           and grp.typeofrent = lv_typeofrent_general
                           and grp.startdate <= lv_pricedate
                           and (grp.invoicefrequencynumber = lv_invoice_freq or lv_typeofrent_general <> 8)
                           and nvl(grp.sizebillinggroup_id, 0) = lv_specific_sizeBillingGroupID
                         order by grp.startdate desc, grp.changesperweek asc) x
/* HVH I would have found more logical most recent price for smallest number of changes, this would
       then automatically choose size * if defined.
                         order by grp.changesperweek asc, grp.startdate desc) x  */
                 where rownum = 1; -- take first
              fetch Vcsr
                into lv_price, lv_price_start_date, lv_changesperweekfound, lv_pricelist_code, lv_pricelist_desc, dummy;
              close Vcsr;
            else
              -- specific number of changes passed, try that first, if not found try changes * (-1)
              open Vcsr for
                select x.*, rownum
                  from (select grp.price,
                               grp.startdate,
                               grp.changesperweek,
                               g.code,
                               g.description
                          from generalrentprice grp, generalpricelist g
                         where g.generalpricelist_id = lv_generalpricelistid
                           and g.pricetype = 2
                           and g.active = 'Y'
                           and grp.generalpricelist_id = g.generalpricelist_id
                           and grp.product_id = productid
                           and grp.sizedefinition_id = lv_size
                           and grp.finishingmethod_id = lv_finmethod
                           and grp.typeofrent = lv_typeofrent_general
                           and grp.changesperweek = lv_changesperweek
                           and grp.startdate <= lv_pricedate
                           and (grp.invoicefrequencynumber = lv_invoice_freq or lv_typeofrent_general <> 8)
                           and nvl(grp.sizebillinggroup_id, 0) = lv_specific_sizeBillingGroupID
                         order by grp.startdate desc) x
                 where rownum = 1; -- take first
              fetch Vcsr
                into lv_price, lv_price_start_date, lv_changesperweekfound, lv_pricelist_code, lv_pricelist_desc, dummy;
              close Vcsr;
              if lv_price is null and lv_typeofrent_general = 2 then
                -- price for exact number of changes not found$, try changes / week = -1 (*)
                open Vcsr for
                  select x.*, rownum
                    from (select grp.price,
                                 grp.startdate,
                                 grp.changesperweek,
                                 g.code,
                                 g.description
                            from generalrentprice grp, generalpricelist g
                           where g.generalpricelist_id = lv_generalpricelistid
                             and g.pricetype = 2
                             and g.active = 'Y'
                             and grp.generalpricelist_id = g.generalpricelist_id
                             and grp.product_id = productid
                             and grp.sizedefinition_id = lv_size
                             and grp.finishingmethod_id = lv_finmethod
                             and grp.typeofrent = lv_typeofrent_general
                             and grp.changesperweek = -1
                             and (grp.invoicefrequencynumber = lv_invoice_freq or lv_typeofrent_general <> 8)
                             and nvl(grp.sizebillinggroup_id, 0) = lv_specific_sizeBillingGroupID
                           order by grp.startdate desc) x
                   where rownum = 1; -- take first
                fetch Vcsr
                  into lv_price, lv_price_start_date, lv_changesperweekfound, lv_pricelist_code, lv_pricelist_desc, dummy;
                close Vcsr;
              end if; -- lv_price is null
            end if; -- lv_changesperweek = 0
            if lv_price is not null then
              price_found := true;
            end if;
          exception
            when no_data_found then
              null;
            when others then
              null;
          end;
        else  --  generalpricelistid is not null  customer specific pricelist
          begin
            lv_price            := null;
            lv_price_start_date := null;
            lv_pricelist_type   := 'C'; -- customer specific
            if lv_changesperweek = 0 -- no specific changes passed, take most recent active price
             then
              open Vcsr for
                select x.*, rownum
                  from (select cupri.price,
                               cupri.startdate,
                               cupri.changesperweek,
                               cupri.customerrentprice_id
                          from customerrentprice cupri
                         where cupri.productlistpercustomer_id = lv_productlistpercustomerid
                           and cupri.startdate <= lv_pricedate
                           and cupri.typeofrent = lv_typeofrent_specific
                           and (cupri.invoicefrequencynumber = lv_invoice_freq or lv_typeofrent_specific <> 8)
                           and nvl(cupri.sizebillinggroup_id, 0) = lv_specific_sizeBillingGroupID
                         order by cupri.startdate desc, cupri.changesperweek asc) x
/* HVH I would have found more logical most recent price for smallest number of changes, this would
       then automatically choose size * if defined.
                         order by cupri.changesperweek asc, cupri.startdate desc) x  */
                 where rownum = 1; -- take first
              fetch Vcsr
                into lv_price, lv_price_start_date, lv_changesperweekfound, lv_custrentpriceid, dummy;
              close Vcsr;
            else
              -- specific number of changes passed, try that first, if not found try changes * (-1)
              open Vcsr for
                select x.*, rownum
                  from (select cupri.price,
                               cupri.startdate,
                               cupri.changesperweek,
                               cupri.customerrentprice_id
                          from customerrentprice cupri
                         where cupri.productlistpercustomer_id = lv_productlistpercustomerid
                           and cupri.startdate <= lv_pricedate
                           and cupri.typeofrent = lv_typeofrent_specific
                           and cupri.changesperweek = lv_changesperweek
                           and (cupri.invoicefrequencynumber = lv_invoice_freq or lv_typeofrent_specific <> 8)
                           and nvl(cupri.sizebillinggroup_id, 0) = lv_specific_sizeBillingGroupID
                         order by cupri.startdate desc) x
                 where rownum = 1; -- take first
              fetch Vcsr
                into lv_price, lv_price_start_date, lv_changesperweekfound, lv_custrentpriceid, dummy;
              close Vcsr;
              if lv_price is null and lv_typeofrent_specific = 2 then
                -- price for exact number of changes not found$, try changes / week = -1 (*)
                open Vcsr for
                  select x.*, rownum
                    from (select cupri.price,
                                 cupri.startdate,
                                 cupri.changesperweek,
                                 cupri.customerrentprice_id
                            from customerrentprice cupri
                           where cupri.productlistpercustomer_id = lv_productlistpercustomerid
                             and cupri.startdate <= lv_pricedate
                             and cupri.typeofrent = lv_typeofrent_specific
                             and cupri.changesperweek = -1
                             and (cupri.invoicefrequencynumber = lv_invoice_freq or lv_typeofrent_specific <> 8)
                             and nvl(cupri.sizebillinggroup_id, 0) = lv_specific_sizeBillingGroupID
                           order by cupri.startdate desc) x
                   where rownum = 1; -- take first
                fetch Vcsr
                  into lv_price, lv_price_start_date, lv_changesperweekfound, lv_custrentpriceid, dummy;
                close Vcsr;
              end if; -- lv_price is null
            end if; -- lv_changesperweek = 0
            if lv_price is not null then
              price_found := true;
            end if;
          end; -- else
        end if; -- end if generalpricelistid is not null
        fetch VPriceListperCust
          into lv_generalpricelistid; -- continue with next pricelistpercustomer
      end loop; -- end while not VPriceListperCust%notfound
      /* --------------- E N D   I N N E R L O O P ----------------------------------------------------------*/
      if (not price_found) and (not price_not_relevant) then
        /* iterations:
           1 = with plpc active and passed size
           2 = with plpc active and size *.             Skipped when passed size was *
           3 = with plpc inactive and passed size.      Skipped when active plpc was found
           4 = with plpc inactive and size *            Skipped when passed size was *
        */
        i := i + 1;
        if i = 2 then --  select sizedefinition_id for '*' code
          select sizedefinition.sizedefinition_id
            into lv_size
            from sizedefinition
           where sizedefinition.code = '*';
          if lv_size = sizedefinitionid -- in iteration 1 was size * already tried, because size * was passed.
            then i := i + 1;  -- skip iteration 2
          end if;
        end if;
        if i = 3 then -- select original size again and try for an inactive plpc
          if lv_productlistpercustomerid is not null
            then i := 5; -- don't search further
            else lv_size := sizedefinitionid;
          end if;
        end if;
        if i = 4 then --  select sizedefinition_id for '*' code try again for inactive plpc
          select sizedefinition.sizedefinition_id
            into lv_size
            from sizedefinition
           where sizedefinition.code = '*';
          if lv_size = sizedefinitionid -- in iteration 3 was size * already tried, because size * was passed.
            then i := i + 1;  -- skip iteration 4
          end if;
        end if;
      end if;
      if i = 5 and lv_specific_sizeBillingGroupID <> lv_standard_sizeBillingGroupID and lv_specific_sizeBillingGroupID <> 0 then
        i := 1; -- if the price was not found for the productsize.sizebillinggroup, then try for the standard
        lv_specific_sizeBillingGroupID := lv_standard_sizeBillingGroupID;
        lv_size := sizedefinitionid;
      end if;
      if i = 5 and lv_specific_sizeBillingGroupID = lv_standard_sizeBillingGroupID and lv_specific_sizeBillingGroupID <> 0 then
        i := 1; -- if the price was not found for the productsize.sizebillinggroup, then try for the price without sizebillinggroup
        lv_specific_sizeBillingGroupID := 0;
        lv_size := sizedefinitionid;
      end if;
    end loop; -- end while i < 5
    /* ##################### E N D   O U T E R L O O P ############################################################*/
    if price_found then
      price            := lv_price;
      price_found_yn   := 'Y';
      price_start_date := lv_price_start_date;
      customerrentpriceid := lv_custrentpriceid;
      if chgperwk_or_freq=0 then
        case lv_typeofrent
          when 2 then result_code := '1'; -- when typeofrent is chgsperweek return result code about multiple prices possible
          when 4 then result_code := '2'; -- when typeofrent is frequency return result code about multiple prices possible
          else result_code := '0';
        end case;
      else result_code := '0';
      end if;
      result_desc      := decode_error('Y' || to_char(result_code));
      price_list_type  := lv_pricelist_type;
      price_list_code  := lv_pricelist_code;
      if lv_pricelist_type = 'G'
        then price_list_desc := lv_pricelist_desc;
        else price_list_desc := 'Customer Specific.';
      end if;
      if lv_typeofrent in (2,4) then -- changes per week or frequency based
        changesperwkfoundprice := lv_changesperweekfound;
      end if;
      price_type_code := lv_typeofrent;
      price_type_desc := decode_type_of_rent(price_type_code);
      inv_freq_code   := lv_invoice_freq;
      inv_freq_desc   := decode_inv_freq(inv_freq_code);
    else
      if result_code is null then -- no price found
        result_code := 1;
      end if;
      result_desc := decode_error('N' || result_code);
    end if;
  exception
    when no_data_found then
      result_code := 1; -- no price found (often because fin method * not defined)
      result_desc := decode_error('N' || result_code);
    when others then
      result_code := 9;
      result_desc := decode_error('N' || result_code) || sqlerrm; -- ora error
  end Get_Rent_Price_details;

/*--------------------------------------------------------------------------------------------------------*/
function Rent_Price_Details(customerid        in customer.customer_id%type,
                            productid         in product.product_id%type,
                            sizedefinitionid  in sizedefinition.sizedefinition_id%type,
                            finishingmethodid in finishingmethod.finishingmethod_id%type,
                            pricedate         in pricelistpercustomer.dateactive%type default null,
                            chgperwk_or_freq  in generalrentprice.changesperweek%type default 0)
 return varchar2 is
  /* See package header for description of return string.   */

    price                   customerrentprice.price%type;
    price_found_yn          varchar2(1);
    price_start_date        customerrentprice.startdate%type;
    result_code             number;
    result_desc             varchar2(80);
    price_list_type         varchar2(1);
    price_list_code         generalpricelist.code%type;
    price_list_desc         generalpricelist.description%type;
    changesperwkfoundprice  customerrentprice.changesperweek%type;
    price_type_code         customerrentprice.typeofrent%type;
    price_type_desc         varchar2(30);
    inv_freq_code           customerrentprice.invoicefrequencynumber%type;
    inv_freq_desc           varchar2(10);
    customerrentpriceid     customerrentprice.customerrentprice_id%type;

    begin
      Get_Rent_Price_Details(customerid,
                             productid,
                             sizedefinitionid,
                             finishingmethodid,
                             pricedate,
                             chgperwk_or_freq,
                             price,
                             price_found_yn,
                             price_start_date,
                             result_code,
                             result_desc,
                             price_list_type,
                             price_list_code,
                             price_list_desc,
                             changesperwkfoundprice,
                             price_type_code,
                             price_type_desc,
                             inv_freq_code,
                             inv_freq_desc,
                             customerrentpriceid);
      return(rpad(nvl(to_char(price, '999999D9999'), ' '), 12) ||
             price_found_yn ||
             rpad(nvl(to_char(price_start_date, 'YYYYMMDD'),' '), 8) ||
             rpad(nvl(price_list_type, ' '), 1) ||
             rpad(nvl(substr(price_list_code,1,6), ' '), 6) ||  -- part 1  pos 1-6
             rpad(nvl(price_list_desc, ' '), 40) ||
             rpad(nvl(to_char(result_code), ' '), 1) ||
             rpad(nvl(result_desc, ' '), 80) ||
             rpad(nvl(to_char(price_type_code), ' '), 1) ||
             rpad(nvl(price_type_desc, ' '), 30) ||
             rpad(nvl(to_char(inv_freq_code), ' '), 1) ||
             rpad(nvl(inv_freq_desc, ' '), 10) ||
             lpad(nvl(to_char(changesperwkfoundprice), ' '), 10) ||
             rpad(nvl(to_char(customerrentpriceid), ' '), 1) ||
             rpad(nvl(substr(price_list_code,7,9), ' '), 9)  -- part 2  pos 7-15
             );
    end Rent_Price_Details;

/*--------------------------------------------------------------------------------------------------------*/
function Rent_Price(customerid        in customer.customer_id%type,
                    productid         in product.product_id%type,
                    sizedefinitionid  in sizedefinition.sizedefinition_id%type,
                    finishingmethodid in finishingmethod.finishingmethod_id%type,
                    pricedate         in pricelistpercustomer.dateactive%type default null,
                    chgperwk_or_freq  in generalrentprice.changesperweek%type default 0)
 return number is
  /* Returns the price, if not found, returns 0.  For use in calculations.  */

    price                   customerrentprice.price%type;
    price_found_yn          varchar2(1);
    price_start_date        customerrentprice.startdate%type;
    result_code             number;
    result_desc             varchar2(80);
    price_list_type         varchar2(1);
    price_list_code         generalpricelist.code%type;
    price_list_desc         generalpricelist.description%type;
    changesperwkfoundprice  customerrentprice.changesperweek%type;
    price_type_code         customerrentprice.typeofrent%type;
    price_type_desc         varchar2(30);
    inv_freq_code           customerrentprice.invoicefrequencynumber%type;
    inv_freq_desc           varchar2(10);
    customerrentpriceid     customerrentprice.customerrentprice_id%type;

    begin
      Get_Rent_Price_Details(customerid,
                             productid,
                             sizedefinitionid,
                             finishingmethodid,
                             pricedate,
                             chgperwk_or_freq,
                             price,
                             price_found_yn,
                             price_start_date,
                             result_code,
                             result_desc,
                             price_list_type,
                             price_list_code,
                             price_list_desc,
                             changesperwkfoundprice,
                             price_type_code,
                             price_type_desc,
                             inv_freq_code,
                             inv_freq_desc,
                             customerrentpriceid);
      return(nvl(price,0));
    end Rent_Price;

/*--------------------------------------------------------------------------------------------------------*/
function CustomerRent_PriceID(customerid        in customer.customer_id%type,
                    productid         in product.product_id%type,
                    sizedefinitionid  in sizedefinition.sizedefinition_id%type,
                    finishingmethodid in finishingmethod.finishingmethod_id%type,
                    pricedate         in pricelistpercustomer.dateactive%type default null,
                    chgperwk_or_freq  in generalrentprice.changesperweek%type default 0)
 return number is
  /* Returns the price, if not found, returns 0.  For use in calculations.  */

    price                   customerrentprice.price%type;
    price_found_yn          varchar2(1);
    price_start_date        customerrentprice.startdate%type;
    result_code             number;
    result_desc             varchar2(80);
    price_list_type         varchar2(1);
    price_list_code         generalpricelist.code%type;
    price_list_desc         generalpricelist.description%type;
    changesperwkfoundprice  customerrentprice.changesperweek%type;
    price_type_code         customerrentprice.typeofrent%type;
    price_type_desc         varchar2(30);
    inv_freq_code           customerrentprice.invoicefrequencynumber%type;
    inv_freq_desc           varchar2(10);
    customerrentpriceid     customerrentprice.customerrentprice_id%type;

    begin
      Get_Rent_Price_Details(customerid,
                             productid,
                             sizedefinitionid,
                             finishingmethodid,
                             pricedate,
                             chgperwk_or_freq,
                             price,
                             price_found_yn,
                             price_start_date,
                             result_code,
                             result_desc,
                             price_list_type,
                             price_list_code,
                             price_list_desc,
                             changesperwkfoundprice,
                             price_type_code,
                             price_type_desc,
                             inv_freq_code,
                             inv_freq_desc,
                             customerrentpriceid);
      return(nvl(customerrentpriceid,0));
    end CustomerRent_PriceID;

/*--------------------------------------------------------------------------------------------------------*/
function Rent_Price_Display(customerid        in customer.customer_id%type,
                            productid         in product.product_id%type,
                            sizedefinitionid  in sizedefinition.sizedefinition_id%type,
                            finishingmethodid in finishingmethod.finishingmethod_id%type,
                            pricedate         in pricelistpercustomer.dateactive%type default null,
                            chgperwk_or_freq  in generalrentprice.changesperweek%type default 0)
 return number is
  /* Returns the price, if not found, returns null.  For display/print purposes. */

    price                   customerrentprice.price%type;
    price_found_yn          varchar2(1);
    price_start_date        customerrentprice.startdate%type;
    result_code             number;
    result_desc             varchar2(80);
    price_list_type         varchar2(1);
    price_list_code         generalpricelist.code%type;
    price_list_desc         generalpricelist.description%type;
    changesperwkfoundprice  customerrentprice.changesperweek%type;
    price_type_code         customerrentprice.typeofrent%type;
    price_type_desc         varchar2(30);
    inv_freq_code           customerrentprice.invoicefrequencynumber%type;
    inv_freq_desc           varchar2(10);
    customerrentpriceid     customerrentprice.customerrentprice_id%type;

    begin
      Get_Rent_Price_Details(customerid,
                             productid,
                             sizedefinitionid,
                             finishingmethodid,
                             pricedate,
                             chgperwk_or_freq,
                             price,
                             price_found_yn,
                             price_start_date,
                             result_code,
                             result_desc,
                             price_list_type,
                             price_list_code,
                             price_list_desc,
                             changesperwkfoundprice,
                             price_type_code,
                             price_type_desc,
                             inv_freq_code,
                             inv_freq_desc,
                             customerrentpriceid);
      return(price);
    end Rent_Price_Display;

/*--------------------------------------------------------------------------------------------------------*/
  procedure Get_Wash_Price_Details(customerid             in customer.customer_id%type,
                                   productid              in product.product_id%type,
                                   sizedefinitionid       in sizedefinition.sizedefinition_id%type,
                                   finishingmethodid      in finishingmethod.finishingmethod_id%type,
                                   pricedate              in pricelistpercustomer.dateactive%type,
                                   pricetype              in number, -- 0=all, 1=LOG, 2=COG
                                   price                  out customerwashprice.price%type,
                                   price_found_yn         out varchar2,
                                   price_start_date       out customerwashprice.startdate%type,
                                   result_code            out number,
                                   result_desc            out varchar2,
                                   price_list_type        out varchar2,
                                   price_list_code        out generalpricelist.code%type,
                                   price_list_desc        out generalpricelist.description%type,
                                   price_type_code        out customerwashprice.pricetype%type,
                                   price_type_desc        out varchar2) is

    price_found                 boolean;
    price_not_relevant          boolean;
    lv_size                     sizedefinition.sizedefinition_id%type;
    lv_finmethod                finishingmethod.finishingmethod_id%type;
    i                           number;
    dummy                       number;
    lv_pricedate                date;
    lv_productlistpercustomerid productlistpercustomer.productlistpercustomer_id%type;
    lv_billingmethod            productlistpercustomer.billingmethod%type;
    lv_priceusage               productlistpercustomer.priceusage%type;
    lv_chargewashonlycog        productlistpercustomer.chargewashesonlyforcog%type;
    --PVD added
    lv_minimumturnaround        productlistpercustomer.minimumturnaround%type;
    --RVI added
    lv_businessunitid           businessunit.businessunit_id%type;
    lv_underuse_count           number;
    --
    lv_pricelist_type           varchar2(1);
    lv_typeofmerchandize        product.typeofmerchandise%type;
    lv_pricetype                customerwashprice.pricetype%type;
    lv_pricetype_gen_prlist     generalwashprice.pricetype%type;
    lv_found_pricetype          customerwashprice.pricetype%type;
    lv_price                    customerwashprice.price%type;
    lv_price_start_date         customerwashprice.startdate%type;
    lv_generalpricelistid       generalwashprice.generalpricelist_id%type;
    lv_pricelist_code           generalpricelist.code%type;
    lv_pricelist_desc           generalpricelist.description%type;
    type Tcsr                   is ref cursor;
    Vcsr                        Tcsr;
    VPriceListperCust           Tcsr;

    function decode_error(errorcode in varchar2) return varchar2 is
      r varchar2(80);
    begin
      select decode(errorcode,
                    'Y0',
                    'Price found',
                    'Y1',
                    'Distinct prices can be applicable LOG and COG',
                    'N1',
                    'Price not defined',
                    'N2',
                    'Price not relevant, billing method 3 with rent price only',
                    'N3',
                    'Price not relevant, billing method = 1 flat rated rent',
                    'N4',
                    'Price not relevant, billing method = 2 rent based on minimum',
                    'N5',
                    'Price not relevant, disposable / sales only item',
                    'N6',
                    'Price not relevant, billing method = 4 with price for LOG only',
                    'N7',
                    'Price not relevant, billing method = 4 with price for COG only',
                    'N8',
                    'Error in parameters',
                    'N9',
                    'ORA Error',
                    'Unknown error code')
        into r
        from dual;
      return r;
    end;

    function decode_price_type(fppricetype in number) return varchar2 is
      r varchar2(30);
    begin
      select decode(fppricetype,
                    1,
                    'All (COG or LOG)',
                    2,
                    'COG Only',
                    4,
                    'Split Price',
                    'Unknown price type')
        into r
        from dual;
      return r;
    end;

  begin -- main wash price
    /* ------------------------ I N I T I A L I S A T I O N ------------------------------------------------------*/
    price_found            := false;
    price_not_relevant     := false;
    price                  := null;
    price_found_yn         := 'N';
    price_start_date       := null;
    result_code            := null;
    result_desc            := null;
    price_list_type        := null;
    price_list_code        := null;
    price_list_desc        := null;
    price_type_code        := null;
    price_type_desc        := null;
    if pricedate is null then
      select sysdate into lv_pricedate from dual;
    else
      lv_pricedate := pricedate;
    end if;
    lv_size           := sizedefinitionid;
    lv_finmethod      := finishingmethodid;
    lv_pricetype      := 1;
    if (customerid is null) OR (productid is null) OR
       (sizedefinitionid is null) OR (finishingmethodid is null) or (pricetype is null) then
      result_code := 8; -- error in parameters
    end if;
    i := 1;
    -- start outer loop on exact match, size * etc.
    /* ############################ O U T E R L O O P ############################################################*/
    while (i < 5) and (result_code is null) and (not price_found) and
          (not price_not_relevant) loop
      open VPriceListperCust for
        Select pricelistpercustomer.generalpricelist_id
          from pricelistpercustomer
         where pricelistpercustomer.customer_id = customerid
           and pricelistpercustomer.pricetype = 1 -- Wash
           and pricelistpercustomer.dateactive <= lv_pricedate
           and pricelistpercustomer.dateinactive >= lv_pricedate
         order by pricelistpercustomer.priority;
      fetch VPriceListperCust
        into lv_generalpricelistid;
      -- load billingmethod etc to determine price usage
      lv_productlistpercustomerid := null;
      lv_billingmethod            := null;
      lv_priceusage               := null;
      lv_chargewashonlycog        := null;
      lv_typeofmerchandize        := null;
      lv_minimumturnaround        := null;
      open Vcsr for
        select plpc.productlistpercustomer_id,
               plpc.billingmethod,
               plpc.priceusage,
               plpc.chargewashesonlyforcog,
               p.typeofmerchandise,
               --PVD added extra check
               plpc.minimumturnaround,
               -- RVI added
               c.defaultbusinessunit_id
          from productlistpercustomer plpc, product p
               -- RVI added
               , customer c
         where plpc.customer_id = customerid
           and plpc.product_id = productid
           and p.product_id = productid
           -- RVI added
           and c.customer_id = customerid
           --
           and plpc.sizedefinition_id = lv_size
           and plpc.finishingmethod_id = lv_finmethod
           and ((lv_pricedate >= plpc.dateactive and lv_pricedate <= plpc.dateinactive) or i>2)
         order by plpc.dateactive desc;
      if Vcsr%notfound then
        null;
      else
        fetch Vcsr
          into lv_productlistpercustomerid, lv_billingmethod, lv_priceusage, lv_chargewashonlycog, lv_typeofmerchandize, lv_minimumturnaround
          -- RVI added
          , lv_businessunitid
          ---
          ;
        if lv_billingmethod = 3 and lv_priceusage = 2 then /* rent price only*/
          price_not_relevant := true;
          result_code        := 2; -- Price not relevant, billing method 3 with rent price only
        end if;
        if lv_billingmethod = 1 and not (lv_chargewashonlycog=3 and pricetype=2) then
          price_not_relevant := true;
          result_code        := 3; -- Price not relevant, billing method = 1 flat rated rent only
        end if;
/*        if lv_billingmethod = 2 and lv_minimumturnaround = 0 then
          price_not_relevant := true;
          result_code        := 4; -- Price not relevant, billing method = 2 rent based on minimum
        end if;
*/
        -- RVI added
        if lv_billingmethod = 2 then
          -- Determine system setting UNDERUSEMETHOD
          select count(1)
          into   lv_underuse_count
          from   systemsetting st
          join   businessunit bu
          on     bu.plant_id = st.plant_id
          where  st.name = 'UNDERUSEMETHOD'
          and    st.value = '2'
          and    bu.businessunit_id = lv_businessunitid;
          -- Only for UNDERUSEMETHOD = 2 the MinimumTurnaround must be > 0
          if lv_underuse_count > 0 and lv_minimumturnaround = 0 then
            price_not_relevant := true;
            result_code        := 4; -- Price not relevant, billing method = 2 rent based on minimum
          end if;
        end if;
        ----
        if lv_typeofmerchandize = 3 then
          price_not_relevant := true;
          result_code        := 5; -- Price not relevant, disposable or sales only item.
        end if;
        if lv_billingmethod = 4 and lv_typeofmerchandize <> 3 and
           lv_chargewashonlycog = 2 /*LOG Only*/ and pricetype = 2 /*COG Only*/ then
          price_not_relevant := true;
          result_code        := 6; -- Price not relevant, billing method = 4 with for LOG only
        end if;
        if lv_billingmethod = 4 and lv_typeofmerchandize <> 3 and
           lv_chargewashonlycog = 3 /*COG Only*/ and pricetype = 1 /*LOG Only*/ then
          price_not_relevant := true;
          result_code        := 7; -- Price not relevant, billing method = 4 with for COG only
        end if;
        if (not price_found) and (not price_not_relevant) then
          lv_pricetype := 1; -- All
          if pricetype=2 and  -- COG
             lv_billingmethod in (3,4) and
             lv_chargewashonlycog in (1,3) and /* separate price for COG/LOG,  or COG only */
             lv_priceusage <> 1 /* split */ then
            lv_pricetype := 2; -- COG only
          end if;
          if pricetype=0 and  -- all
             lv_billingmethod=4 and
             lv_chargewashonlycog = 3 then /* COG only */
            lv_pricetype := 2; -- COG only
          end if;
          if lv_billingmethod = 1 and lv_chargewashonlycog = 3 and pricetype=2 then
            lv_pricetype := 2; -- COG only
          end if;
        end if;
      end if;
      close Vcsr;
      if lv_billingmethod=3 and lv_priceusage=1 -- split
       then lv_pricetype_gen_prlist := 4;
       else lv_pricetype_gen_prlist := lv_pricetype;
      end if;
      -- start inner loop on pricelistpercustomer
      /* --------------------- I N N E R L O O P ----------------------------------------------------------*/
      while (not VPriceListperCust%notfound) and (not price_found) and
            (not price_not_relevant) loop
        if lv_generalpricelistid is not null then  -- general price list
          begin
            lv_price            := null;
            lv_price_start_date := null;
            lv_pricelist_type   := 'G'; -- general
            if pricetype=0 -- not specified LOG or COG take most recent active price
            and lv_pricetype_gen_prlist <> 4 -- for split price always look for split prices
            and not (lv_billingmethod=4 and lv_chargewashonlycog = 3) -- for COG only look only for COG prices
             then
              open Vcsr for
                select x.*, rownum
                  from (select gwp.price,
                               gwp.startdate,
                               gwp.pricetype,
                               g.code,
                               g.description
                          from generalwashprice gwp, generalpricelist g
                         where g.generalpricelist_id = lv_generalpricelistid
                           and g.pricetype = 1 -- Wash
                           and g.active = 'Y'
                           and gwp.generalpricelist_id = g.generalpricelist_id
                           and gwp.product_id = productid
                           and gwp.sizedefinition_id = lv_size
                           and gwp.finishingmethod_id = lv_finmethod
                           and gwp.startdate <= lv_pricedate
                           and (gwp.pricetype <> 4 or lv_billingmethod <> 4) -- don't select split prices for BM 4
                         order by gwp.startdate desc, gwp.pricetype asc) x
                 where rownum = 1; -- take first
              fetch Vcsr
                into lv_price, lv_price_start_date, lv_found_pricetype, lv_pricelist_code, lv_pricelist_desc, dummy;
              close Vcsr;
            else -- LOG or COG is specifically specified.
              open Vcsr for
                select x.*, rownum
                  from (select gwp.price,
                               gwp.startdate,
                               gwp.pricetype,
                               g.code,
                               g.description
                          from generalwashprice gwp, generalpricelist g
                         where g.generalpricelist_id = lv_generalpricelistid
                           and g.pricetype = 1 -- Wash
                           and g.active = 'Y'
                           and gwp.generalpricelist_id = g.generalpricelist_id
                           and gwp.product_id = productid
                           and gwp.sizedefinition_id = lv_size
                           and gwp.finishingmethod_id = lv_finmethod
                           and gwp.startdate <= lv_pricedate
                           and gwp.pricetype = lv_pricetype_gen_prlist
                         order by gwp.startdate desc) x
                 where rownum = 1; -- take first
              fetch Vcsr
                into lv_price, lv_price_start_date,lv_found_pricetype,lv_pricelist_code, lv_pricelist_desc, dummy;
              close Vcsr;
            end if; -- pricetype = 0
            if lv_price is not null then
              price_found := true;
            end if;
          exception
            when no_data_found then
              null;
            when others then
              null;
          end;
        else  --  generalpricelistid is not null  customer specific pricelist
          begin
            lv_price            := null;
            lv_price_start_date := null;
            lv_pricelist_type   := 'C'; -- customer specific
            if pricetype=0 -- not specified LOG or COG take most recent active price
             then
              open Vcsr for
                select x.*, rownum
                  from (select cupri.price,
                               cupri.startdate,
                               cupri.pricetype
                          from customerwashprice cupri
                         where cupri.productlistpercustomer_id = lv_productlistpercustomerid
                           and cupri.startdate <= lv_pricedate
                           and cupri.pricetype = lv_pricetype
                         order by cupri.startdate desc) x
                 where rownum = 1; -- take first
              fetch Vcsr
                into lv_price, lv_price_start_date, lv_found_pricetype, dummy;
              close Vcsr;
            else -- LOG or COG is specifically specified.
              open Vcsr for
                select x.*, rownum
                  from (select cupri.price,
                               cupri.startdate,
                               cupri.pricetype
                          from customerwashprice cupri
                         where cupri.productlistpercustomer_id = lv_productlistpercustomerid
                           and cupri.startdate <= lv_pricedate
                           and cupri.pricetype = lv_pricetype
                         order by cupri.startdate desc) x
                 where rownum = 1; -- take first
              fetch Vcsr
                into lv_price, lv_price_start_date, lv_found_pricetype, dummy;
              close Vcsr;
            end if;  -- pricetype = 0
            if lv_price is not null then
              price_found := true;
            end if;
          end; -- else
        end if; -- end if generalpricelistid is not null
        fetch VPriceListperCust
          into lv_generalpricelistid; -- continue with next pricelistpercustomer
      end loop; -- end while not VPriceListperCust%notfound
      /* --------------- E N D   I N N E R L O O P ----------------------------------------------------------*/
      close VPriceListperCust;
      if (not price_found) and (not price_not_relevant) then
        /* iterations:
           1 = with plpc active and passed size
           2 = with plpc active and size *.             Skipped when passed size was *
           3 = with plpc inactive and passed size.      Skipped when active plpc was found
           4 = with plpc inactive and size *            Skipped when passed size was *
        */
        i := i + 1;
        if i = 2 then --  select sizedefinition_id for '*' code
          select sizedefinition.sizedefinition_id
            into lv_size
            from sizedefinition
           where sizedefinition.code = '*';
          if lv_size = sizedefinitionid -- in iteration 1 was size * already tried, because size * was passed.
            then i := i + 1;  -- skip iteration 2
          end if;
        end if;
        if i = 3 then -- try again original size but now for inactive plpc
          if lv_productlistpercustomerid is not null
            then i := 5; -- don't search further
            else lv_size := sizedefinitionid;
          end if;
        end if;
        if i = 4 then --  select sizedefinition_id for '*' code and try again for inactive plpc
          select sizedefinition.sizedefinition_id
            into lv_size
            from sizedefinition
           where sizedefinition.code = '*';
          if lv_size = sizedefinitionid -- in iteration 3 was size * already tried, because size * was passed.
            then i := i + 1;  -- skip iteration 4
          end if;
        end if;
      end if;
    end loop; -- end while i < 5
    /* ##################### E N D   O U T E R L O O P ############################################################*/
    if price_found then
      price            := lv_price;
      price_found_yn   := 'Y';
      price_start_date := lv_price_start_date;
      if pricetype=0 and lv_chargewashonlycog=1 -- separate price LOG/COG
        then result_code := '1';
        else result_code := '0';
      end if;
      result_desc      := decode_error('Y' || to_char(result_code));
      price_list_type  := lv_pricelist_type;
      price_list_code  := lv_pricelist_code;
      if lv_pricelist_type = 'G'
        then price_list_desc := lv_pricelist_desc;
        else price_list_desc := 'Customer Specific.';
      end if;
      price_type_code := lv_found_pricetype;
      price_type_desc := decode_price_type(price_type_code);
    else
      if result_code is null then -- no price found
        result_code := 1;
      end if;
      result_desc := decode_error('N' || result_code);
    end if;
  exception
    when no_data_found then
      result_code := 1; -- no price found (often because fin method * not defined)
      result_desc := decode_error('N' || result_code);
    when others then
      result_code := 9;
      result_desc := decode_error('N' || result_code) || sqlerrm; -- ora error
  end Get_Wash_Price_details;


/*--------------------------------------------------------------------------------------------------------*/
function Wash_Price_Details(customerid        in customer.customer_id%type,
                            productid         in product.product_id%type,
                            sizedefinitionid  in sizedefinition.sizedefinition_id%type,
                            finishingmethodid in finishingmethod.finishingmethod_id%type,
                            pricedate         in pricelistpercustomer.dateactive%type default null,
                            pricetype         in number default 0)
 return varchar2 is

  /* See package header for description of return string.   */

    price                   customerwashprice.price%type;
    price_found_yn          varchar2(1);
    price_start_date        customerwashprice.startdate%type;
    result_code             number;
    result_desc             varchar2(80);
    price_list_type         varchar2(1);
    price_list_code         generalpricelist.code%type;
    price_list_desc         generalpricelist.description%type;
    price_type_code         customerwashprice.pricetype%type;
    price_type_desc         varchar2(30);


    begin
      Get_Wash_Price_Details(customerid,
                             productid,
                             sizedefinitionid,
                             finishingmethodid,
                             pricedate,
                             pricetype,
                             price,
                             price_found_yn,
                             price_start_date,
                             result_code,
                             result_desc,
                             price_list_type,
                             price_list_code,
                             price_list_desc,
                             price_type_code,
                             price_type_desc);
      return(rpad(nvl(to_char(price, '999999D9999'), ' '), 12) ||
             price_found_yn ||
             rpad(nvl(to_char(price_start_date, 'YYYYMMDD'),' '), 8) ||
             rpad(nvl(price_list_type, ' '), 1) ||
             rpad(nvl(substr(price_list_code,1,6), ' '), 6) ||  -- part 1  pos 1-6
             rpad(nvl(price_list_desc, ' '), 40) ||
             rpad(nvl(to_char(result_code), ' '), 1) ||
             rpad(nvl(result_desc, ' '), 80) ||
             rpad(nvl(to_char(price_type_code), ' '), 1) ||
             rpad(nvl(price_type_desc, ' '), 30) ||
             rpad(nvl(substr(price_list_code,7,9), ' '), 9)  -- part 2  pos 7-15
             );
    end Wash_Price_Details;

/*--------------------------------------------------------------------------------------------------------*/
function Wash_Price(customerid        in customer.customer_id%type,
                    productid         in product.product_id%type,
                    sizedefinitionid  in sizedefinition.sizedefinition_id%type,
                    finishingmethodid in finishingmethod.finishingmethod_id%type,
                    pricedate         in pricelistpercustomer.dateactive%type default null,
                    pricetype         in number default 0)
 return number is
  /* Returns the price, if not found, returns 0.  For use in calculations.  */

    price                   customerwashprice.price%type;
    price_found_yn          varchar2(1);
    price_start_date        customerwashprice.startdate%type;
    result_code             number;
    result_desc             varchar2(80);
    price_list_type         varchar2(1);
    price_list_code         generalpricelist.code%type;
    price_list_desc         generalpricelist.description%type;
    price_type_code         customerwashprice.pricetype%type;
    price_type_desc         varchar2(30);

    begin
      Get_Wash_Price_Details(customerid,
                             productid,
                             sizedefinitionid,
                             finishingmethodid,
                             pricedate,
                             pricetype,
                             price,
                             price_found_yn,
                             price_start_date,
                             result_code,
                             result_desc,
                             price_list_type,
                             price_list_code,
                             price_list_desc,
                             price_type_code,
                             price_type_desc);
      return(nvl(price,0));
    end Wash_Price;

/*--------------------------------------------------------------------------------------------------------*/
function Wash_Price_Display(customerid        in customer.customer_id%type,
                            productid         in product.product_id%type,
                            sizedefinitionid  in sizedefinition.sizedefinition_id%type,
                            finishingmethodid in finishingmethod.finishingmethod_id%type,
                            pricedate         in pricelistpercustomer.dateactive%type default null,
                            pricetype         in number default 0)
return number is
  /* Returns the price, if not found, returns null.  For display/print purposes. */

    price                   customerwashprice.price%type;
    price_found_yn          varchar2(1);
    price_start_date        customerwashprice.startdate%type;
    result_code             number;
    result_desc             varchar2(80);
    price_list_type         varchar2(1);
    price_list_code         generalpricelist.code%type;
    price_list_desc         generalpricelist.description%type;
    price_type_code         customerwashprice.pricetype%type;
    price_type_desc         varchar2(30);

    begin
      Get_Wash_Price_Details(customerid,
                             productid,
                             sizedefinitionid,
                             finishingmethodid,
                             pricedate,
                             pricetype,
                             price,
                             price_found_yn,
                             price_start_date,
                             result_code,
                             result_desc,
                             price_list_type,
                             price_list_code,
                             price_list_desc,
                             price_type_code,
                             price_type_desc);
      return(price);
    end Wash_Price_Display;

/*--------------------------------------------------------------------------------------------------------*/
  procedure Get_Wash_Price_Details_PT(customerid             in customer.customer_id%type,
                                      productid              in product.product_id%type,
                                      sizedefinitionid       in sizedefinition.sizedefinition_id%type,
                                      finishingmethodid      in finishingmethod.finishingmethod_id%type,
                                      pricedate              in pricelistpercustomer.dateactive%type,
                                      pricetype              in number,
                                      price                  out customerwashprice.price%type,
                                      price_found_yn         out varchar2,
                                      price_start_date       out customerwashprice.startdate%type,
                                      result_code            out number,
                                      result_desc            out varchar2,
                                      price_list_type        out varchar2,
                                      price_list_code        out generalpricelist.code%type,
                                      price_list_desc        out generalpricelist.description%type,
                                      price_type_code        out customerwashprice.pricetype%type,
                                      price_type_desc        out varchar2) is

    price_found                 boolean;
--    price_not_relevant          boolean;
    lv_size                     sizedefinition.sizedefinition_id%type;
    lv_finmethod                finishingmethod.finishingmethod_id%type;
    i                           number;
    dummy                       number;
    lv_pricedate                date;
    lv_productlistpercustomerid productlistpercustomer.productlistpercustomer_id%type;
    lv_pricelist_type           varchar2(1);
    lv_pricetype                customerwashprice.pricetype%type;
    lv_pricetype_gen_prlist     generalwashprice.pricetype%type;
    lv_found_pricetype          customerwashprice.pricetype%type;
    lv_price                    customerwashprice.price%type;
    lv_price_start_date         customerwashprice.startdate%type;
    lv_generalpricelistid       generalwashprice.generalpricelist_id%type;
    lv_pricelist_code           generalpricelist.code%type;
    lv_pricelist_desc           generalpricelist.description%type;
    type Tcsr                   is ref cursor;
    Vcsr                        Tcsr;
    VPriceListperCust           Tcsr;

    function decode_error(errorcode in varchar2) return varchar2 is
      r varchar2(80);
    begin
      select decode(errorcode,
                    'Y0',
                    'Price found',
                    'N1',
                    'Price not defined',
                    'N8',
                    'Error in parameters',
                    'N9',
                    'ORA Error',
                    'Unknown error code')
        into r
        from dual;
      return r;
    end;

    function decode_price_type(fppricetype in number) return varchar2 is
      r varchar2(30);
    begin
      select decode(fppricetype,
                    1,
                    'All (COG or LOG)',
                    2,
                    'COG Only',
                    3,
                    'Price on Client Delivery Note',
                    4,
                    'Split Price',
                    5,
                    'Heavy Soil',
                    'Unknown price type')
        into r
        from dual;
      return r;
    end;

  begin -- main wash price for specified price type
    /* ------------------------ I N I T I A L I S A T I O N ------------------------------------------------------*/
    price_found            := false;
    price                  := null;
    price_found_yn         := 'N';
    price_start_date       := null;
    result_code            := null;
    result_desc            := null;
    price_list_type        := null;
    price_list_code        := null;
    price_list_desc        := null;
    price_type_code        := null;
    price_type_desc        := null;
    if pricedate is null then
      select sysdate into lv_pricedate from dual;
    else
      lv_pricedate := pricedate;
    end if;
    lv_size           := sizedefinitionid;
    lv_finmethod      := finishingmethodid;
    lv_pricetype      := pricetype;
    lv_pricetype_gen_prlist := pricetype;
    if (customerid is null) OR (productid is null) OR
       (sizedefinitionid is null) OR (finishingmethodid is null) or (pricetype is null) or (pricetype = 0) then
      result_code := 8; -- error in parameters
    end if;
    i := 1;
    -- start outer loop on exact match, size * etc.
    /* ############################ O U T E R L O O P ############################################################*/
    while (i < 5) and (result_code is null) and (not price_found) loop
      open VPriceListperCust for
        Select pricelistpercustomer.generalpricelist_id
          from pricelistpercustomer
         where pricelistpercustomer.customer_id = customerid
           and pricelistpercustomer.pricetype = 1 -- Wash
           and pricelistpercustomer.dateactive <= lv_pricedate
           and pricelistpercustomer.dateinactive >= lv_pricedate
         order by pricelistpercustomer.priority;
      fetch VPriceListperCust
        into lv_generalpricelistid;
      -- load plpc ID
      lv_productlistpercustomerid := null;
      open Vcsr for
        select plpc.productlistpercustomer_id
          from productlistpercustomer plpc, product p
         where plpc.customer_id = customerid
           and plpc.product_id = productid
           and p.product_id = productid
           and plpc.sizedefinition_id = lv_size
           and plpc.finishingmethod_id = lv_finmethod
           and ((lv_pricedate >= plpc.dateactive and lv_pricedate <= plpc.dateinactive) or i>2)
         order by plpc.dateactive desc;
      if Vcsr%notfound then
        null;
      else
        fetch Vcsr
          into lv_productlistpercustomerid;
      end if;
      close Vcsr;
      -- start inner loop on pricelistpercustomer
      /* --------------------- I N N E R L O O P ----------------------------------------------------------*/
      while (not VPriceListperCust%notfound) and (not price_found) loop
        if lv_generalpricelistid is not null then  -- general price list
          begin
            lv_price            := null;
            lv_price_start_date := null;
            lv_pricelist_type   := 'G'; -- general
            open Vcsr for
              select x.*, rownum
                from (select gwp.price,
                             gwp.startdate,
                             gwp.pricetype,
                             g.code,
                             g.description
                        from generalwashprice gwp, generalpricelist g
                       where g.generalpricelist_id = lv_generalpricelistid
                         and g.pricetype = 1 -- Wash
                         and g.active = 'Y'
                         and gwp.generalpricelist_id = g.generalpricelist_id
                         and gwp.product_id = productid
                         and gwp.sizedefinition_id = lv_size
                         and gwp.finishingmethod_id = lv_finmethod
                         and gwp.startdate <= lv_pricedate
                         and gwp.pricetype = lv_pricetype_gen_prlist
                       order by gwp.startdate desc) x
               where rownum = 1; -- take first
            fetch Vcsr
              into lv_price, lv_price_start_date,lv_found_pricetype,lv_pricelist_code, lv_pricelist_desc, dummy;
            close Vcsr;
            if lv_price is not null then
              price_found := true;
            end if;
          exception
            when no_data_found then
              null;
            when others then
              null;
          end;
        else  --  generalpricelistid is not null  customer specific pricelist
          begin
            lv_price            := null;
            lv_price_start_date := null;
            lv_pricelist_type   := 'C'; -- customer specific
            open Vcsr for
              select x.*, rownum
                from (select cupri.price,
                             cupri.startdate,
                             cupri.pricetype
                        from customerwashprice cupri
                       where cupri.productlistpercustomer_id = lv_productlistpercustomerid
                         and cupri.startdate <= lv_pricedate
                         and cupri.pricetype = lv_pricetype
                       order by cupri.startdate desc) x
               where rownum = 1; -- take first
            fetch Vcsr
              into lv_price, lv_price_start_date, lv_found_pricetype, dummy;
            close Vcsr;
            if lv_price is not null then
              price_found := true;
            end if;
          end; -- else
        end if; -- end if generalpricelistid is not null
        fetch VPriceListperCust
          into lv_generalpricelistid; -- continue with next pricelistpercustomer
      end loop; -- end while not VPriceListperCust%notfound
      /* --------------- E N D   I N N E R L O O P ----------------------------------------------------------*/
      if (not price_found) then
        /* iterations:
           1 = with plpc active and passed size
           2 = with plpc active and size *.             Skipped when passed size was *
           3 = with plpc inactive and passed size.      Skipped when active plpc was found
           4 = with plpc inactive and size *            Skipped when passed size was *
        */
        i := i + 1;
        if i = 2 then --  select sizedefinition_id for '*' code
          select sizedefinition.sizedefinition_id
            into lv_size
            from sizedefinition
           where sizedefinition.code = '*';
          if lv_size = sizedefinitionid -- in iteration 1 was size * already tried, because size * was passed.
            then i := i + 1;  -- skip iteration 2
          end if;
        end if;
        if i = 3 then -- try again original size but now for inactive plpc
          if lv_productlistpercustomerid is not null
            then i := 5; -- don't search further
            else lv_size := sizedefinitionid;
          end if;
        end if;
        if i = 4 then --  select sizedefinition_id for '*' code and try again for inactive plpc
          select sizedefinition.sizedefinition_id
            into lv_size
            from sizedefinition
           where sizedefinition.code = '*';
          if lv_size = sizedefinitionid -- in iteration 3 was size * already tried, because size * was passed.
            then i := i + 1;  -- skip iteration 4
          end if;
        end if;
      end if;
    end loop; -- end while i < 5
    /* ##################### E N D   O U T E R L O O P ############################################################*/
    if price_found then
      price            := lv_price;
      price_found_yn   := 'Y';
      price_start_date := lv_price_start_date;
      result_code      := 0;
      result_desc      := decode_error('Y' || to_char(result_code));
      price_list_type  := lv_pricelist_type;
      price_list_code  := lv_pricelist_code;
      if lv_pricelist_type = 'G'
        then price_list_desc := lv_pricelist_desc;
        else price_list_desc := 'Customer Specific.';
      end if;
      price_type_code := lv_found_pricetype;
      price_type_desc := decode_price_type(price_type_code);
    else
      if result_code is null then -- no price found
        result_code := 1;
      end if;
      result_desc := decode_error('N' || to_char(result_code));
    end if;
  exception
    when no_data_found then
      result_code := 1; -- no price found (often because fin method * not defined)
      result_desc := decode_error('N' || to_char(result_code));
    when others then
      result_code := 9;
      result_desc := decode_error('N' || to_char(result_code)) || sqlerrm; -- ora error
  end Get_Wash_Price_details_PT;


/*--------------------------------------------------------------------------------------------------------*/
function Wash_Price_Details_PT(customerid        in customer.customer_id%type,
                               productid         in product.product_id%type,
                               sizedefinitionid  in sizedefinition.sizedefinition_id%type,
                               finishingmethodid in finishingmethod.finishingmethod_id%type,
                               pricedate         in pricelistpercustomer.dateactive%type default null,
                               pricetype         in number)
 return varchar2 is

  /* See package header for description of return string.   */

    price                   customerwashprice.price%type;
    price_found_yn          varchar2(1);
    price_start_date        customerwashprice.startdate%type;
    result_code             number;
    result_desc             varchar2(80);
    price_list_type         varchar2(1);
    price_list_code         generalpricelist.code%type;
    price_list_desc         generalpricelist.description%type;
    price_type_code         customerwashprice.pricetype%type;
    price_type_desc         varchar2(30);


    begin
      Get_Wash_Price_Details_PT(customerid,
                                productid,
                                sizedefinitionid,
                                finishingmethodid,
                                pricedate,
                                pricetype,
                                price,
                                price_found_yn,
                                price_start_date,
                                result_code,
                                result_desc,
                                price_list_type,
                                price_list_code,
                                price_list_desc,
                                price_type_code,
                                price_type_desc);
      return(rpad(nvl(to_char(price, '999999D9999'), ' '), 12) ||
             price_found_yn ||
             rpad(nvl(to_char(price_start_date, 'YYYYMMDD'),' '), 8) ||
             rpad(nvl(price_list_type, ' '), 1) ||
             rpad(nvl(substr(price_list_code,1,6), ' '), 6) ||  -- part 1  pos 1-6
             rpad(nvl(price_list_desc, ' '), 40) ||
             rpad(nvl(to_char(result_code), ' '), 1) ||
             rpad(nvl(result_desc, ' '), 80) ||
             rpad(nvl(to_char(price_type_code), ' '), 1) ||
             rpad(nvl(price_type_desc, ' '), 30) ||
             rpad(nvl(substr(price_list_code,7,9), ' '), 9)  -- part 2  pos 7-15
             );
    end Wash_Price_Details_PT;

/*--------------------------------------------------------------------------------------------------------*/
function Wash_Price_PT(customerid        in customer.customer_id%type,
                       productid         in product.product_id%type,
                       sizedefinitionid  in sizedefinition.sizedefinition_id%type,
                       finishingmethodid in finishingmethod.finishingmethod_id%type,
                       pricedate         in pricelistpercustomer.dateactive%type default null,
                       pricetype         in number)
 return number is
  /* Returns the wash price for the specified pricetype, if not found, returns 0.  For use in calculations.  */

    price                   customerwashprice.price%type;
    price_found_yn          varchar2(1);
    price_start_date        customerwashprice.startdate%type;
    result_code             number;
    result_desc             varchar2(80);
    price_list_type         varchar2(1);
    price_list_code         generalpricelist.code%type;
    price_list_desc         generalpricelist.description%type;
    price_type_code         customerwashprice.pricetype%type;
    price_type_desc         varchar2(30);

    begin
      Get_Wash_Price_Details_PT(customerid,
                               productid,
                               sizedefinitionid,
                               finishingmethodid,
                               pricedate,
                               pricetype,
                               price,
                               price_found_yn,
                               price_start_date,
                               result_code,
                               result_desc,
                               price_list_type,
                               price_list_code,
                               price_list_desc,
                               price_type_code,
                               price_type_desc);
      return(nvl(price,0));
    end Wash_Price_PT;

/*--------------------------------------------------------------------------------------------------------*/
function Wash_Price_Display_PT(customerid        in customer.customer_id%type,
                               productid         in product.product_id%type,
                               sizedefinitionid  in sizedefinition.sizedefinition_id%type,
                               finishingmethodid in finishingmethod.finishingmethod_id%type,
                               pricedate         in pricelistpercustomer.dateactive%type default null,
                               pricetype         in number)
return number is
  /* Returns the wash price for the specified pricetype, if not found, returns null. For display/print purposes. */

    price                   customerwashprice.price%type;
    price_found_yn          varchar2(1);
    price_start_date        customerwashprice.startdate%type;
    result_code             number;
    result_desc             varchar2(80);
    price_list_type         varchar2(1);
    price_list_code         generalpricelist.code%type;
    price_list_desc         generalpricelist.description%type;
    price_type_code         customerwashprice.pricetype%type;
    price_type_desc         varchar2(30);

    begin
     Get_Wash_Price_Details_PT(customerid,
                               productid,
                               sizedefinitionid,
                               finishingmethodid,
                               pricedate,
                               pricetype,
                               price,
                               price_found_yn,
                               price_start_date,
                               result_code,
                               result_desc,
                               price_list_type,
                               price_list_code,
                               price_list_desc,
                               price_type_code,
                               price_type_desc);
      return(price);
    end Wash_Price_Display_PT;

/*--------------------------------------------------------------------------------------------------------*/
  procedure Get_Replacement_Price_Details(customerid             in customer.customer_id%type,
                                          productid              in product.product_id%type,
                                          sizedefinitionid       in sizedefinition.sizedefinition_id%type,
                                          finishingmethodid      in finishingmethod.finishingmethod_id%type,
                                          pricedate              in pricelistpercustomer.dateactive%type,
                                          price                  out customerreplacementprice.price%type,
                                          price_found_yn         out varchar2,
                                          price_start_date       out customerreplacementprice.startdate%type,
                                          result_code            out number,
                                          result_desc            out varchar2,
                                          price_list_type        out varchar2,
                                          price_list_code        out generalpricelist.code%type,
                                          price_list_desc        out generalpricelist.description%type) is

    price_found                 boolean;
    price_not_relevant          boolean;
    lv_size                     sizedefinition.sizedefinition_id%type;
    lv_finishingmethod          finishingmethod.finishingmethod_id%type;
    i                           number;
    dummy                       number;
    lv_pricedate                date;
    lv_pricelist_type           varchar2(1);
    lv_price                    customerreplacementprice.price%type;
    lv_price_start_date         customerreplacementprice.startdate%type;
    lv_generalpricelistid       generalreplacementprice.generalpricelist_id%type;
    lv_pricelist_code           generalpricelist.code%type;
    lv_pricelist_desc           generalpricelist.description%type;
    lv_productlistpercustomerid productlistpercustomer.productlistpercustomer_id%type;
    lv_billingmethod            productlistpercustomer.billingmethod%type;
    lv_typeofmerchandise        product.typeofmerchandise%type;
    lv_inventorybillingtype     productlistpercustomer.inventorybillingtype%type;
    type Tcsr                   is ref cursor;
    Vcsr                        Tcsr;
    VPriceListperCust           Tcsr;
    lv_standard_sizeBillingGroupID sizebillinggroup.sizebillinggroup_id%type;
    lv_specific_sizeBillingGroupID sizebillinggroup.systemuser_id%type;

    function decode_error(errorcode in varchar2) return varchar2 is
      r varchar2(80);
    begin
      select decode(errorcode,
                    'Y0',
                    'Price found',
                    'N1',
                    'Price not defined',
                    'N2',
                    'Price not relevant, billingmethod is 4 piece rate only',
                    'N3',
                    'Price not relevant, disposable / sales only item',
                    'N8',
                    'Error in parameters',
                    'N9',
                    'ORA Error',
                    'Unknown error code')
        into r
        from dual;
      return r;
    end;

  begin -- main replacement
    /* ------------------------ I N I T I A L I S A T I O N ------------------------------------------------------*/
    price_found            := false;
    price_not_relevant     := false;
    price                  := null;
    price_found_yn         := 'N';
    price_start_date       := null;
    result_code            := null;
    result_desc            := null;
    price_list_type        := null;
    price_list_code        := null;
    price_list_desc        := null;
    
    get_size_billing_groups(customerid, productid, sizedefinitionid, lv_standard_sizeBillingGroupID, lv_specific_sizeBillingGroupID);
    
    if pricedate is null then
      select sysdate into lv_pricedate from dual;
    else
      lv_pricedate := pricedate;
    end if;
    lv_size := sizedefinitionid;
    lv_finishingmethod := finishingmethodid;
    if (customerid is null) OR (productid is null) OR (sizedefinitionid is null) then
      result_code := 8; -- error in parameters
    end if;
    i := 1;
    -- start outer loop on exact match, size * etc.
    /* ############################ O U T E R L O O P ############################################################*/
    while (i < 5) and (result_code is null) and (not price_found) and
          (not price_not_relevant) loop
      open VPriceListperCust for
        Select pricelistpercustomer.generalpricelist_id
          from pricelistpercustomer
         where pricelistpercustomer.customer_id = customerid
           and pricelistpercustomer.pricetype = 3 -- replacement price
           and pricelistpercustomer.dateactive <= lv_pricedate
           and pricelistpercustomer.dateinactive >= lv_pricedate
         order by pricelistpercustomer.priority;
      fetch VPriceListperCust
        into lv_generalpricelistid;
      -- load billingmethod etc to determine price usage
      lv_billingmethod     := null;
      lv_typeofmerchandise := null;
      open Vcsr for
        select plpc.productlistpercustomer_id,
               plpc.billingmethod,
               p.typeofmerchandise,
               plpc.inventorybillingtype
          from productlistpercustomer plpc, product p
         where plpc.customer_id = customerid
           and plpc.product_id = productid
           and p.product_id = productid
           and plpc.sizedefinition_id = lv_size
           and (plpc.finishingmethod_id = lv_finishingmethod or lv_finishingmethod is null)  -- passing null for FM is allowed
           and ((lv_pricedate >= plpc.dateactive and
                 lv_pricedate <= plpc.dateinactive) or i>2)-- only filter on active during 1st 2 iterations
         order by plpc.dateactive desc;
      if Vcsr%notfound then
        null;
      else
        fetch Vcsr
          into lv_productlistpercustomerid, lv_billingmethod, lv_typeofmerchandise, lv_inventorybillingtype;
        if lv_billingmethod = 4 and lv_inventorybillingtype <> 5 then /*piece rate only and no loss charges*/
          price_not_relevant := true;
          result_code        := 2; -- Price not relevant, billing method piece rate only
        end if;
        if lv_typeofmerchandise = 3 then /* disposable / sales only*/
          price_not_relevant := true;
          result_code        := 3; -- Price not relevant, disposable or sales only product
        end if;
      end if;
      close Vcsr;
      -- start inner loop on pricelistpercustomer
      /* --------------------- I N N E R L O O P ----------------------------------------------------------*/
      while (not VPriceListperCust%notfound) and (not price_found) and
            (not price_not_relevant) loop
        --if not price_found then
        if lv_generalpricelistid is not null then
          -- general price list
          begin
            lv_price            := null;
            lv_price_start_date := null;
            lv_pricelist_type   := 'G'; -- general
            open Vcsr for
              select x.*, rownum
                from (select grp.price,
                             grp.startdate,
                             g.code,
                             g.description
                        from generalreplacementprice grp, generalpricelist g
                       where g.generalpricelist_id = lv_generalpricelistid
                         and g.pricetype = 3 -- replacement price
                         and g.active = 'Y'
                         and grp.generalpricelist_id = g.generalpricelist_id
                         and grp.product_id = productid
                         and grp.sizedefinition_id = lv_size
                         and grp.startdate <= lv_pricedate
                         and nvl(grp.sizebillinggroup_id, 0) = lv_specific_sizeBillingGroupID
                       order by grp.startdate desc) x
               where rownum = 1; -- take first
            fetch Vcsr
              into lv_price, lv_price_start_date, lv_pricelist_code, lv_pricelist_desc, dummy;
            close Vcsr;
            if lv_price is not null then
              price_found := true;
            end if;
          exception
            when no_data_found then
              null;
            when others then
              null;
          end;
        else  --  generalpricelistid is not null  customer specific pricelist
          begin
            lv_price            := null;
            lv_price_start_date := null;
            lv_pricelist_type   := 'C'; -- customer specific
            open Vcsr for
              select x.*, rownum
                from (select cupri.price,
                             cupri.startdate
                        from customerreplacementprice cupri
                       where cupri.customer_id = customerid
                         and cupri.product_id = productid
                         and cupri.sizedefinition_id = lv_size
                         and cupri.startdate <= lv_pricedate
                         and nvl(cupri.sizebillinggroup_id, 0) = lv_specific_sizeBillingGroupID
                       order by cupri.startdate desc) x
               where rownum = 1; -- take first
            fetch Vcsr
              into lv_price, lv_price_start_date, dummy;
            close Vcsr;
            if lv_price is not null then
              price_found := true;
            end if;
          end; -- else
        end if; -- end if generalpricelistid is not null
        fetch VPriceListperCust
          into lv_generalpricelistid; -- continue with next pricelistpercustomer
      end loop; -- end while not VPriceListperCust%notfound
      /* --------------- E N D   I N N E R L O O P ----------------------------------------------------------*/
      if (not price_found) and (not price_not_relevant) then
        /* iterations:
           1 = with plpc active and passed size
           2 = with plpc active and size *.             Skipped when passed size was *
           3 = with plpc inactive and passed size.      Skipped when active plpc was found
           4 = with plpc inactive and size *            Skipped when passed size was *
        */
        i := i + 1;
        if i = 2 then --  select sizedefinition_id for '*' code
          select sizedefinition.sizedefinition_id
            into lv_size
            from sizedefinition
           where sizedefinition.code = '*';
          if lv_size = sizedefinitionid -- if in iteration 1 was size * already tried, because size * was passed.
            then i := i + 1;  -- skip iteration 2
          end if;
        end if;
        if i = 3 then -- select original size again and try for an inactive plpc
          if lv_productlistpercustomerid is not null
            then i := 5; -- don't search further
            else lv_size := sizedefinitionid;
          end if;
        end if;
        if i = 4 then --  select sizedefinition_id for '*' code try again for inactive plpc
          select sizedefinition.sizedefinition_id
            into lv_size
            from sizedefinition
           where sizedefinition.code = '*';
          if lv_size = sizedefinitionid -- in iteration 3 was size * already tried, because size * was passed.
            then i := i + 1;  -- skip iteration 4
          end if;
        end if;
      end if;
      if i = 5 and lv_specific_sizeBillingGroupID <> lv_standard_sizeBillingGroupID and lv_specific_sizeBillingGroupID <> 0 then
        i := 1; -- if the price was not found for the productsize.sizebillinggroup, then try for the standard
        lv_specific_sizeBillingGroupID := lv_standard_sizeBillingGroupID;
        lv_size := sizedefinitionid;
      end if;
      if i = 5 and lv_specific_sizeBillingGroupID = lv_standard_sizeBillingGroupID and lv_specific_sizeBillingGroupID <> 0 then
        i := 1; -- if the price was not found for the productsize.sizebillinggroup, then try for the price without sizebillinggroup
        lv_specific_sizeBillingGroupID := 0;
        lv_size := sizedefinitionid;
      end if;
    end loop; -- end while i < 5
    /* ##################### E N D   O U T E R L O O P ############################################################*/
    if price_found then
      price            := lv_price;
      price_found_yn   := 'Y';
      price_start_date := lv_price_start_date;
      result_code      := '0';
      result_desc      := decode_error('Y' || to_char(result_code));
      price_list_type  := lv_pricelist_type;
      price_list_code  := lv_pricelist_code;
      if lv_pricelist_type = 'G'
        then price_list_desc := lv_pricelist_desc;
        else price_list_desc := 'Customer Specific.';
      end if;
    else
      if result_code is null then -- no price found
        result_code := 1;
      end if;
      result_desc := decode_error('N' || result_code);
    end if;
  exception
    when no_data_found then
      result_code := 1; -- no price found (often because fin method * not defined)
      result_desc := decode_error('N' || result_code);
    when others then
      result_code := 9;
      result_desc := decode_error('N' || result_code) || sqlerrm; -- ora error
  end Get_Replacement_Price_details;

/*--------------------------------------------------------------------------------------------------------*/
  function Replacement_Price_Details(customerid        in customer.customer_id%type,
                                     productid         in product.product_id%type,
                                     sizedefinitionid  in sizedefinition.sizedefinition_id%type,
                                     finishingmethodid in finishingmethod.finishingmethod_id%type,
                                     pricedate         in pricelistpercustomer.dateactive%type default null)
  return varchar2 is
  /* See package header for description of return string.   */

    price                   customerreplacementprice.price%type;
    price_found_yn          varchar2(1);
    price_start_date        customerreplacementprice.startdate%type;
    result_code             number;
    result_desc             varchar2(80);
    price_list_type         varchar2(1);
    price_list_code         generalpricelist.code%type;
    price_list_desc         generalpricelist.description%type;

    begin
      Get_Replacement_Price_Details(customerid,
                                    productid,
                                    sizedefinitionid,
                                    finishingmethodid,
                                    pricedate,
                                    price,
                                    price_found_yn,
                                    price_start_date,
                                    result_code,
                                    result_desc,
                                    price_list_type,
                                    price_list_code,
                                    price_list_desc);
      return(rpad(nvl(to_char(price, '999999D9999'), ' '), 12) ||
             price_found_yn ||
             rpad(nvl(to_char(price_start_date, 'YYYYMMDD'),' '), 8) ||
             rpad(nvl(price_list_type, ' '), 1) ||
             rpad(nvl(substr(price_list_code,1,6), ' '), 6) ||  -- part 1  pos 1-6
             rpad(nvl(price_list_desc, ' '), 40) ||
             rpad(nvl(to_char(result_code), ' '), 1) ||
             rpad(nvl(result_desc, ' '), 80) ||
             rpad(nvl(substr(price_list_code,7,9), ' '), 9)  -- part 2  pos 7-15
            );
    end Replacement_Price_Details;

/*--------------------------------------------------------------------------------------------------------*/
  function Replacement_Price(customerid        in customer.customer_id%type,
                             productid         in product.product_id%type,
                             sizedefinitionid  in sizedefinition.sizedefinition_id%type,
                             finishingmethodid in finishingmethod.finishingmethod_id%type,
                             pricedate         in pricelistpercustomer.dateactive%type default null)
  return number is
  /* Returns the price, if not found, returns 0.  For use in calculations.  */

    price                   customerreplacementprice.price%type;
    price_found_yn          varchar2(1);
    price_start_date        customerreplacementprice.startdate%type;
    result_code             number;
    result_desc             varchar2(80);
    price_list_type         varchar2(1);
    price_list_code         generalpricelist.code%type;
    price_list_desc         generalpricelist.description%type;

    begin
      Get_Replacement_Price_Details(customerid,
                                    productid,
                                    sizedefinitionid,
                                    finishingmethodid,
                                    pricedate,
                                    price,
                                    price_found_yn,
                                    price_start_date,
                                    result_code,
                                    result_desc,
                                    price_list_type,
                                    price_list_code,
                                    price_list_desc);
      return(nvl(price,0));
    end Replacement_Price;

/*--------------------------------------------------------------------------------------------------------*/
  function Replacement_Price_Display(customerid        in customer.customer_id%type,
                                     productid         in product.product_id%type,
                                     sizedefinitionid  in sizedefinition.sizedefinition_id%type,
                                     finishingmethodid in finishingmethod.finishingmethod_id%type,
                                     pricedate         in pricelistpercustomer.dateactive%type default null)
  return number is
  /* Returns the price, if not found, returns null.  For display/print purposes. */

    price                   customerreplacementprice.price%type;
    price_found_yn          varchar2(1);
    price_start_date        customerreplacementprice.startdate%type;
    result_code             number;
    result_desc             varchar2(80);
    price_list_type         varchar2(1);
    price_list_code         generalpricelist.code%type;
    price_list_desc         generalpricelist.description%type;

    begin
      Get_Replacement_Price_Details(customerid,
                                    productid,
                                    sizedefinitionid,
                                    finishingmethodid,
                                    pricedate,
                                    price,
                                    price_found_yn,
                                    price_start_date,
                                    result_code,
                                    result_desc,
                                    price_list_type,
                                    price_list_code,
                                    price_list_desc);
      return(price);
    end Replacement_Price_Display;



/*--------------------------------------------------------------------------------------------------------*/
  procedure Get_Sales_Price_Details(customerid             in customer.customer_id%type,
                                    productid              in product.product_id%type,
                                    sizedefinitionid       in sizedefinition.sizedefinition_id%type,
                                    pricedate              in pricelistpercustomer.dateactive%type,
                                    price                  out customersalesprice.price%type,
                                    price_found_yn         out varchar2,
                                    price_start_date       out customersalesprice.startdate%type,
                                    result_code            out number,
                                    result_desc            out varchar2,
                                    price_list_type        out varchar2,
                                    price_list_code        out generalpricelist.code%type,
                                    price_list_desc        out generalpricelist.description%type) is

    price_found                 boolean;
    price_not_relevant          boolean;
    lv_size                     sizedefinition.sizedefinition_id%type;
    i                           number;
    dummy                       number;
    lv_pricedate                date;
    lv_pricelist_type           varchar2(1);
    lv_price                    customersalesprice.price%type;
    lv_price_start_date         customersalesprice.startdate%type;
    lv_generalpricelistid       generalsalesprice.generalpricelist_id%type;
    lv_pricelist_code           generalpricelist.code%type;
    lv_pricelist_desc           generalpricelist.description%type;
    type Tcsr                   is ref cursor;
    Vcsr                        Tcsr;
    VPriceListperCust           Tcsr;
    lv_standard_sizeBillingGroupID sizebillinggroup.sizebillinggroup_id%type;
    lv_specific_sizeBillingGroupID sizebillinggroup.systemuser_id%type;

    function decode_error(errorcode in varchar2) return varchar2 is
      r varchar2(80);
    begin
      select decode(errorcode,
                    'Y0',
                    'Price found',
                    'N1',
                    'Price not defined',
                    'N8',
                    'Error in parameters',
                    'N9',
                    'ORA Error',
                    'Unknown error code')
        into r
        from dual;
      return r;
    end;

  begin -- main sales
    /* ------------------------ I N I T I A L I S A T I O N ------------------------------------------------------*/
    price_found            := false;
    price_not_relevant     := false;
    price                  := null;
    price_found_yn         := 'N';
    price_start_date       := null;
    result_code            := null;
    result_desc            := null;
    price_list_type        := null;
    price_list_code        := null;
    price_list_desc        := null;
    
    get_size_billing_groups(customerid, productid, sizedefinitionid, lv_standard_sizeBillingGroupID, lv_specific_sizeBillingGroupID);
    
    if pricedate is null then
      select sysdate into lv_pricedate from dual;
    else
      lv_pricedate := pricedate;
    end if;
    lv_size := sizedefinitionid;
    if (customerid is null) OR (productid is null) OR (sizedefinitionid is null) then
      result_code := 8; -- error in parameters
    end if;
    i := 1;
    -- start outer loop on exact match, size * etc.
    /* ############################ O U T E R L O O P ############################################################*/
    while (i < 3) and (result_code is null) and (not price_found) and
          (not price_not_relevant) loop
      open VPriceListperCust for
        Select pricelistpercustomer.generalpricelist_id
          from pricelistpercustomer
         where pricelistpercustomer.customer_id = customerid
           and pricelistpercustomer.pricetype = 4 -- sales price
           and pricelistpercustomer.dateactive <= lv_pricedate
           and pricelistpercustomer.dateinactive >= lv_pricedate
         order by pricelistpercustomer.priority;
      fetch VPriceListperCust
        into lv_generalpricelistid;
      -- start inner loop on pricelistpercustomer
      /* --------------------- I N N E R L O O P ----------------------------------------------------------*/
      while (not VPriceListperCust%notfound) and (not price_found) and
            (not price_not_relevant) loop
        --if not price_found then
        if lv_generalpricelistid is not null then
          -- general price list
          begin
            lv_price            := null;
            lv_price_start_date := null;
            lv_pricelist_type   := 'G'; -- general
            open Vcsr for
              select x.*, rownum
                from (select gsp.price,
                             gsp.startdate,
                             g.code,
                             g.description
                        from generalsalesprice gsp, generalpricelist g
                       where g.generalpricelist_id = lv_generalpricelistid
                         and g.pricetype = 4 -- sales price
                         and g.active = 'Y'
                         and gsp.generalpricelist_id = g.generalpricelist_id
                         and gsp.product_id = productid
                         and gsp.sizedefinition_id = lv_size
                         and gsp.startdate <= lv_pricedate
                         and nvl(gsp.sizebillinggroup_id, 0) = lv_specific_sizeBillingGroupID
                       order by gsp.startdate desc) x
               where rownum = 1; -- take first
            fetch Vcsr
              into lv_price, lv_price_start_date, lv_pricelist_code, lv_pricelist_desc, dummy;
            close Vcsr;
            if lv_price is not null then
              price_found := true;
            end if;
          exception
            when no_data_found then
              null;
            when others then
              null;
          end;
        else  --  generalpricelistid is not null  customer specific pricelist
          begin
            lv_price            := null;
            lv_price_start_date := null;
            lv_pricelist_type   := 'C'; -- customer specific
            open Vcsr for
              select x.*, rownum
                from (select cupri.price,
                             cupri.startdate
                        from customersalesprice cupri
                       where cupri.customer_id = customerid
                         and cupri.product_id = productid
                         and cupri.sizedefinition_id = lv_size
                         and cupri.startdate <= lv_pricedate
                         and nvl(cupri.sizebillinggroup_id, 0) = lv_specific_sizeBillingGroupID
                       order by cupri.startdate desc) x
               where rownum = 1; -- take first
            fetch Vcsr
              into lv_price, lv_price_start_date, dummy;
            close Vcsr;
            if lv_price is not null then
              price_found := true;
            end if;
          end; -- else
        end if; -- end if generalpricelistid is not null
        fetch VPriceListperCust
          into lv_generalpricelistid; -- continue with next pricelistpercustomer
      end loop; -- end while not VPriceListperCust%notfound
      /* --------------- E N D   I N N E R L O O P ----------------------------------------------------------*/
      if (not price_found) and (not price_not_relevant) then
        /* iterations:  1 = with passed size.
                        2 = with size *  (Skipped when passed size was *)   */
        i := i + 1;
        if i = 2 then --  select sizedefinition_id for '*' code
          select sizedefinition.sizedefinition_id
            into lv_size
            from sizedefinition
           where sizedefinition.code = '*';
          if lv_size = sizedefinitionid -- if in iteration 1 was size * already tried, because size * was passed.
            then i := i + 1;  -- skip iteration 2
          end if;
        end if;
      end if;
      if i = 3 and lv_specific_sizeBillingGroupID <> lv_standard_sizeBillingGroupID and lv_specific_sizeBillingGroupID <> 0 then
        i := 1; -- if the price was not found for the productsize.sizebillinggroup, then try for the standard
        lv_specific_sizeBillingGroupID := lv_standard_sizeBillingGroupID;
        lv_size := sizedefinitionid;
      end if;
      if i = 3 and lv_specific_sizeBillingGroupID = lv_standard_sizeBillingGroupID and lv_specific_sizeBillingGroupID <> 0 then
        i := 1; -- if the price was not found for the productsize.sizebillinggroup, then try for the price without sizebillinggroup
        lv_specific_sizeBillingGroupID := 0;
        lv_size := sizedefinitionid;
      end if;      
    end loop; -- end while i < 3
    /* ##################### E N D   O U T E R L O O P ############################################################*/
    if price_found then
      price            := lv_price;
      price_found_yn   := 'Y';
      price_start_date := lv_price_start_date;
      result_code      := '0';
      result_desc      := decode_error('Y' || to_char(result_code));
      price_list_type  := lv_pricelist_type;
      price_list_code  := lv_pricelist_code;
      if lv_pricelist_type = 'G'
        then price_list_desc := lv_pricelist_desc;
        else price_list_desc := 'Customer Specific.';
      end if;
    else
      if result_code is null then -- no price found
        result_code := 1;
      end if;
      result_desc := decode_error('N' || result_code);
    end if;
  exception
    when no_data_found then
      result_code := 1; -- no price found (often because fin method * not defined)
      result_desc := decode_error('N' || result_code);
    when others then
      result_code := 9;
      result_desc := decode_error('N' || result_code) || sqlerrm; -- ora error
  end Get_Sales_Price_details;

/*--------------------------------------------------------------------------------------------------------*/
  function Sales_Price_Details(customerid        in customer.customer_id%type,
                               productid         in product.product_id%type,
                               sizedefinitionid  in sizedefinition.sizedefinition_id%type,
                               pricedate         in pricelistpercustomer.dateactive%type default null)
  return varchar2 is
  /* See package header for description of return string.   */

    price                   customersalesprice.price%type;
    price_found_yn          varchar2(1);
    price_start_date        customersalesprice.startdate%type;
    result_code             number;
    result_desc             varchar2(80);
    price_list_type         varchar2(1);
    price_list_code         generalpricelist.code%type;
    price_list_desc         generalpricelist.description%type;

    begin
      Get_Sales_Price_Details(customerid,
                              productid,
                              sizedefinitionid,
                              pricedate,
                              price,
                              price_found_yn,
                              price_start_date,
                              result_code,
                              result_desc,
                              price_list_type,
                              price_list_code,
                              price_list_desc);
      return(rpad(nvl(to_char(price, '999999D9999'), ' '), 12) ||
             price_found_yn ||
             rpad(nvl(to_char(price_start_date, 'YYYYMMDD'),' '), 8) ||
             rpad(nvl(price_list_type, ' '), 1) ||
             rpad(nvl(substr(price_list_code,1,6), ' '), 6) ||  -- part 1  pos 1-6
             rpad(nvl(price_list_desc, ' '), 40) ||
             rpad(nvl(to_char(result_code), ' '), 1) ||
             rpad(nvl(result_desc, ' '), 80) ||
             rpad(nvl(substr(price_list_code,7,9), ' '), 9)  -- part 2  pos 7-15
            );
    end Sales_Price_Details;

/*--------------------------------------------------------------------------------------------------------*/
  function Sales_Price(customerid        in customer.customer_id%type,
                       productid         in product.product_id%type,
                       sizedefinitionid  in sizedefinition.sizedefinition_id%type,
                       pricedate         in pricelistpercustomer.dateactive%type default null)
  return number is
  /* Returns the price, if not found, returns 0.  For use in calculations.  */

    price                   customersalesprice.price%type;
    price_found_yn          varchar2(1);
    price_start_date        customersalesprice.startdate%type;
    result_code             number;
    result_desc             varchar2(80);
    price_list_type         varchar2(1);
    price_list_code         generalpricelist.code%type;
    price_list_desc         generalpricelist.description%type;

    begin
      Get_Sales_Price_Details(customerid,
                              productid,
                              sizedefinitionid,
                              pricedate,
                              price,
                              price_found_yn,
                              price_start_date,
                              result_code,
                              result_desc,
                              price_list_type,
                              price_list_code,
                              price_list_desc);
      return(nvl(price,0));
    end Sales_Price;

/*--------------------------------------------------------------------------------------------------------*/
  function Sales_Price_Display(customerid        in customer.customer_id%type,
                               productid         in product.product_id%type,
                               sizedefinitionid  in sizedefinition.sizedefinition_id%type,
                               pricedate         in pricelistpercustomer.dateactive%type default null)
  return number is
  /* Returns the price, if not found, returns null.  For display/print purposes. */

    price                   customersalesprice.price%type;
    price_found_yn          varchar2(1);
    price_start_date        customersalesprice.startdate%type;
    result_code             number;
    result_desc             varchar2(80);
    price_list_type         varchar2(1);
    price_list_code         generalpricelist.code%type;
    price_list_desc         generalpricelist.description%type;

    begin
      Get_Sales_Price_Details(customerid,
                              productid,
                              sizedefinitionid,
                              pricedate,
                              price,
                              price_found_yn,
                              price_start_date,
                              result_code,
                              result_desc,
                              price_list_type,
                              price_list_code,
                              price_list_desc);
      return(price);
    end Sales_Price_Display;

/*--------------------------------------------------------------------------------------------------------*/
  procedure Get_Weight_Price_Details(customerid            in customer.customer_id%type,
                                    weighingcategoryid     in weighingcategory.weighingcategory_id%type ,
                                    soil_yn                in customerweightprice.soil%type,
                                    pricedate              in pricelistpercustomer.dateactive%type,
                                    price                  out customerweightprice.price%type,
                                    price_found_yn         out varchar2,
                                    price_start_date       out customerweightprice.startdate%type,
                                    result_code            out number,
                                    result_desc            out varchar2,
                                    price_list_type        out varchar2,
                                    price_list_code        out generalpricelist.code%type,
                                    price_list_desc        out generalpricelist.description%type) is

    price_found                 boolean;
    price_not_relevant          boolean;
    i                           number;
    dummy                       number;
    lv_pricedate                date;
    lv_pricelist_type           varchar2(1);
    lv_price                    customerweightprice.price%type;
    lv_price_start_date         customerweightprice.startdate%type;
    lv_generalpricelistid       generalweightprice.generalpricelist_id%type;
    lv_pricelist_code           generalpricelist.code%type;
    lv_pricelist_desc           generalpricelist.description%type;
    lv_weighingcatpercustomerid weighingcategpercustomer.weighingcategpercustomer_id%type;
    lv_invoice_method           weighingcategpercustomer.invoicemethod%type;
    type Tcsr                   is ref cursor;
    Vcsr                        Tcsr;
    VPriceListperCust           Tcsr;

    function decode_error(errorcode in varchar2) return varchar2 is
      r varchar2(80);
    begin
      select decode(errorcode,
                    'Y0',
                    'Price found',
                    'N1',
                    'Price not defined',
                    'N2',
                    'Price not relevant, weights not invoiced',
                    'N3',
                    'Price not defined, weights invoiced soil, not clean ',
                    'N4',
                    'Price not defined, weights invoiced clean, not soil ',
                    'N8',
                    'Error in parameters',
                    'N9',
                    'ORA Error',
                    'Unknown error code')
        into r
        from dual;
      return r;
    end;

  begin -- main Weight
    /* ------------------------ I N I T I A L I S A T I O N ------------------------------------------------------*/
    price_found            := false;
    price_not_relevant     := false;
    price                  := null;
    price_found_yn         := 'N';
    price_start_date       := null;
    result_code            := null;
    result_desc            := null;
    price_list_type        := null;
    price_list_code        := null;
    price_list_desc        := null;
    if pricedate is null then
      select sysdate into lv_pricedate from dual;
    else
      lv_pricedate := pricedate;
    end if;
    if (customerid is null) OR (weighingcategoryid is null) OR (soil_yn not in ('Y','N')) then
      result_code := 8; -- error in parameters
    end if;
    i := 1;
    -- start outer loop on exact match, size * etc.
    /* ############################ O U T E R L O O P ############################################################*/
    while (i < 3) and (result_code is null) and (not price_found) and
          (not price_not_relevant) loop
      open VPriceListperCust for
        Select pricelistpercustomer.generalpricelist_id
          from pricelistpercustomer
         where pricelistpercustomer.customer_id = customerid
           and pricelistpercustomer.pricetype = 5  -- this means weight price
           and pricelistpercustomer.dateactive <= lv_pricedate
           and pricelistpercustomer.dateinactive >= lv_pricedate
         order by pricelistpercustomer.priority;
      fetch VPriceListperCust
        into lv_generalpricelistid;
-- load weighingcategpercustomer etc to determine price usage
      lv_weighingcatpercustomerid := null;
      lv_invoice_method           := null;
      open Vcsr for
        select wcpc.weighingcategpercustomer_id,
               wcpc.invoicemethod
          from weighingcategpercustomer wcpc
         where wcpc.customer_id = customerid
           and wcpc.weighingcategory_id = weighingcategoryid
           and ((lv_pricedate >= wcpc.dateactive and lv_pricedate <= wcpc.dateinactive) or i>1)
         order by wcpc.dateactive desc;
      if Vcsr%notfound then
        null;
      else
        fetch Vcsr
          into lv_weighingcatpercustomerid, lv_invoice_method;
        if lv_invoice_method =1 then /* no invoicing based on weight */
          price_not_relevant := true;
          result_code        := 2; -- Price not relevant, customer is not invoiced based on weight
        end if;
        if lv_invoice_method in (2,4) and soil_yn = 'N' then /* configured soil, requested clean */
          price_not_relevant := true;
          result_code        := 3; -- Price not relevant, customer is invoiced based on soil weight
        end if;
        if lv_invoice_method = 3 and soil_yn = 'Y' then /* configured clean, requested soil */
          price_not_relevant := true;
          result_code        := 4; -- Price not relevant, customer is invoiced based on clean weight
        end if;
      end if;
      -- start inner loop on pricelistpercustomer
      /* --------------------- I N N E R L O O P ----------------------------------------------------------*/
      while (not VPriceListperCust%notfound) and (not price_found) and
            (not price_not_relevant) loop
        --if not price_found then
        if lv_generalpricelistid is not null then
          -- general price list
          begin
            lv_price            := null;
            lv_price_start_date := null;
            lv_pricelist_type   := 'G'; -- general
            open Vcsr for
              select x.*, rownum
                from (select gwp.price,
                             gwp.startdate,
                             g.code,
                             g.description
                        from generalweightprice gwp, generalpricelist g
                       where g.generalpricelist_id = lv_generalpricelistid
                         and g.pricetype = 5 -- weight price
                         and g.active = 'Y'
                         and gwp.generalpricelist_id = g.generalpricelist_id
                         and gwp.weighingcategory_id = weighingcategoryid
                         and gwp.soil = soil_yn
                         and gwp.startdate <= lv_pricedate
                       order by gwp.startdate desc) x
               where rownum = 1; -- take first
            fetch Vcsr
              into lv_price, lv_price_start_date, lv_pricelist_code, lv_pricelist_desc, dummy;
            close Vcsr;
            if lv_price is not null then
              price_found := true;
            end if;
          exception
            when no_data_found then
              null;
            when others then
              null;
          end;
        else  --  generalpricelistid is not null  customer specific pricelist
          begin
            lv_price            := null;
            lv_price_start_date := null;
            lv_pricelist_type   := 'C'; -- customer specific
            open Vcsr for
              select x.*, rownum
                from (select cupri.price,
                             cupri.startdate
                        from customerweightprice cupri
                       where cupri.weighingcategpercustomer_id = lv_weighingcatpercustomerid
                         and cupri.soil = soil_yn
                         and cupri.startdate <= lv_pricedate
                       order by cupri.startdate desc) x
               where rownum = 1; -- take first
            fetch Vcsr
              into lv_price, lv_price_start_date, dummy;
            close Vcsr;
            if lv_price is not null then
              price_found := true;
            end if;
          end; -- else
        end if; -- end if generalpricelistid is not null
        fetch VPriceListperCust
          into lv_generalpricelistid; -- continue with next pricelistpercustomer
      end loop; -- end while not VPriceListperCust%notfound
      /* --------------- E N D   I N N E R L O O P ----------------------------------------------------------*/
      if (not price_found) and (not price_not_relevant) then
        /* iterations:  1 = try to find for active weighingcategorypercustomer
                        2 = try to find for any weighingcategorypercustomer */
        i := i + 1;
        if lv_weighingcatpercustomerid is not null then  -- active wcpc already found, so do not perform extra loop
          i := i + 1;
        end if;
      end if;
    end loop; -- end while i < 3
    /* ##################### E N D   O U T E R L O O P ############################################################*/
    if price_found then
      price            := lv_price;
      price_found_yn   := 'Y';
      price_start_date := lv_price_start_date;
      result_code      := '0';
      result_desc      := decode_error('Y' || to_char(result_code));
      price_list_type  := lv_pricelist_type;
      price_list_code  := lv_pricelist_code;
      if lv_pricelist_type = 'G'
        then price_list_desc := lv_pricelist_desc;
        else price_list_desc := 'Customer Specific.';
      end if;
    else
      if result_code is null then -- no price found
        result_code := 1;
      end if;
      result_desc := decode_error('N' || result_code);
    end if;
  exception
    when no_data_found then
      result_code := 1; -- no price found (often because fin method * not defined)
      result_desc := decode_error('N' || result_code);
    when others then
      result_code := 9;
      result_desc := decode_error('N' || result_code) || sqlerrm; -- ora error
  end Get_Weight_Price_details;


/*--------------------------------------------------------------------------------------------------------*/
  function Weight_Price_Details(customerid         in customer.customer_id%type,
                                weighingcategoryid in weighingcategory.weighingcategory_id%type ,
                                soil_yn            in customerweightprice.soil%type,
                                pricedate          in pricelistpercustomer.dateactive%type default null)
  return varchar2 is
  /* See package header for description of return string.   */

    price                   customerweightprice.price%type;
    price_found_yn          varchar2(1);
    price_start_date        customerweightprice.startdate%type;
    result_code             number;
    result_desc             varchar2(80);
    price_list_type         varchar2(1);
    price_list_code         generalpricelist.code%type;
    price_list_desc         generalpricelist.description%type;

    begin
      Get_Weight_Price_Details(customerid,
                              weighingcategoryid,
                              soil_yn,
                              pricedate,
                              price,
                              price_found_yn,
                              price_start_date,
                              result_code,
                              result_desc,
                              price_list_type,
                              price_list_code,
                              price_list_desc);
      return(rpad(nvl(to_char(price, '999999D9999'), ' '), 12) ||
             price_found_yn ||
             rpad(nvl(to_char(price_start_date, 'YYYYMMDD'),' '), 8) ||
             rpad(nvl(price_list_type, ' '), 1) ||
             rpad(nvl(substr(price_list_code,1,6), ' '), 6) ||  -- part 1  pos 1-6
             rpad(nvl(price_list_desc, ' '), 40) ||
             rpad(nvl(to_char(result_code), ' '), 1) ||
             rpad(nvl(result_desc, ' '), 80) ||
             rpad(nvl(substr(price_list_code,7,9), ' '), 9)  -- part 2  pos 7-15
            );
    end Weight_Price_Details;

/*--------------------------------------------------------------------------------------------------------*/
  function Weight_Price(customerid         in customer.customer_id%type,
                        weighingcategoryid in weighingcategory.weighingcategory_id%type ,
                        soil_yn            in customerweightprice.soil%type,
                        pricedate          in pricelistpercustomer.dateactive%type default null)
  return number is
  /* Returns the price, if not found, returns 0.  For use in calculations.  */

    price                   customerweightprice.price%type;
    price_found_yn          varchar2(1);
    price_start_date        customerweightprice.startdate%type;
    result_code             number;
    result_desc             varchar2(80);
    price_list_type         varchar2(1);
    price_list_code         generalpricelist.code%type;
    price_list_desc         generalpricelist.description%type;

    begin
      Get_Weight_Price_Details(customerid,
                              weighingcategoryid,
                              soil_yn,
                              pricedate,
                              price,
                              price_found_yn,
                              price_start_date,
                              result_code,
                              result_desc,
                              price_list_type,
                              price_list_code,
                              price_list_desc);
      return(nvl(price,0));
    end Weight_Price;

/*--------------------------------------------------------------------------------------------------------*/
  function Weight_Price_Display(customerid         in customer.customer_id%type,
                                weighingcategoryid in weighingcategory.weighingcategory_id%type ,
                                soil_yn            in customerweightprice.soil%type,
                                pricedate          in pricelistpercustomer.dateactive%type default null)
  return number is
  /* Returns the price, if not found, returns null.  For display/print purposes. */

    price                   customerweightprice.price%type;
    price_found_yn          varchar2(1);
    price_start_date        customerweightprice.startdate%type;
    result_code             number;
    result_desc             varchar2(80);
    price_list_type         varchar2(1);
    price_list_code         generalpricelist.code%type;
    price_list_desc         generalpricelist.description%type;

    begin
      Get_Weight_Price_Details(customerid,
                              weighingcategoryid,
                              soil_yn,
                              pricedate,
                              price,
                              price_found_yn,
                              price_start_date,
                              result_code,
                              result_desc,
                              price_list_type,
                              price_list_code,
                              price_list_desc);
      return(price);
    end Weight_Price_Display;

/*--------------------------------------------------------------------------------------------------------*/
  procedure Get_SpecCharge_Details(customerid             in customer.customer_id%type,
                                   specialchargeid        in specialcharge.specialcharge_id%type,
                                   pricedate              in pricelistpercustomer.dateactive%type,
                                   price                  out customerspecialchargeprice.price%type,
                                   price_found_yn         out varchar2,
                                   price_start_date       out customerspecialchargeprice.startdate%type,
                                   result_code            out number,
                                   result_desc            out varchar2,
                                   price_list_type        out varchar2,
                                   price_list_code        out generalpricelist.code%type,
                                   price_list_desc        out generalpricelist.description%type,
                                   specialchargecode      out specialcharge.code%type,
                                   percentage             out customerspecialchargeprice.percentage%type,
                                   minimumamount          out specialcharge.minimumamount%type,
                                   maximumamount          out specialcharge.maximumamount%type) is

    price_found                 boolean;
    dummy                       number;
    lv_fixedamount_yn           specialcharge.fixedamount%type;
    lv_fixedamount_str          varchar2(10);
    lv_pricedate                date;
    lv_pricelist_type           varchar2(1);
    lv_price                    customerspecialchargeprice.price%type;
    lv_price_start_date         customerspecialchargeprice.startdate%type;
    lv_generalpricelistid       generalpricelist.generalpricelist_id%type;
    lv_pricelist_code           generalpricelist.code%type;
    lv_pricelist_desc           generalpricelist.description%type;
    lv_percentage               customerspecialchargeprice.percentage%type;
    lv_minimumamount            customerspecialchargeprice.minimumamount%type;
    lv_maximumamount            customerspecialchargeprice.maximumamount%type;
    type Tcsr                   is ref cursor;
    Vcsr                        Tcsr;
    VPriceListperCust           Tcsr;

    function decode_error(errorcode in varchar2, type_str in varchar2 default null) return varchar2 is
      r varchar2(80);
    begin
      select decode(errorcode,
                    'Y0',
                    type_str || ' found',
                    'N1',
                    type_str || ' not defined',
                    'N8',
                    'Error in parameters',
                    'N9',
                    'ORA Error',
                    'Unknown error code')
        into r
        from dual;
      return r;
    end;

  begin -- main SpecialCharge
    /* ------------------------ I N I T I A L I S A T I O N ------------------------------------------------------*/
    price_found            := false;
    price                  := null;
    price_found_yn         := 'N';
    price_start_date       := null;
    result_code            := null;
    result_desc            := null;
    price_list_type        := null;
    price_list_code        := null;
    price_list_desc        := null;
    lv_fixedamount_yn      := null;
    if pricedate is null then
      select sysdate into lv_pricedate from dual;
    else
      lv_pricedate := pricedate;
    end if;
    select c.fixedamount,
           decode(c.fixedamount,'Y','Price','Percentage')
      into lv_fixedamount_yn,
           lv_fixedamount_str
      from specialcharge c
     where c.specialcharge_id = specialchargeid;
    if ((customerid is null) OR (specialchargeid is null) OR (lv_fixedamount_yn is null) ) then
      result_code := 8; -- error in parameters
    end if;
    if result_code is null then
      open VPriceListperCust for
        Select pricelistpercustomer.generalpricelist_id
          from pricelistpercustomer
         where pricelistpercustomer.customer_id = customerid
           and pricelistpercustomer.pricetype = 10  -- this means special charge price
           and pricelistpercustomer.dateactive <= lv_pricedate
           and pricelistpercustomer.dateinactive >= lv_pricedate
         order by pricelistpercustomer.priority;
      fetch VPriceListperCust
        into lv_generalpricelistid;
      -- start loop on pricelistpercustomer
      /* --------------------- L O O P ----------------------------------------------------------*/
      while (not VPriceListperCust%notfound) and (not price_found) loop
        --if not price_found then
        if lv_generalpricelistid is not null then
          -- general price list
          begin
            lv_price            := null;
            lv_price_start_date := null;
            lv_percentage       := null;
            lv_minimumamount    := null;
            lv_maximumamount    := null;
            lv_pricelist_type   := 'G'; -- general
            open Vcsr for
              select x.*, rownum
                from (select gscp.price,
                             gscp.startdate,
                             g.code,
                             g.description,
                             gscp.percentage, gscp.minimumamount, gscp.maximumamount
                        from generalspecialchargeprice gscp, generalpricelist g
                       where g.generalpricelist_id = lv_generalpricelistid
                         and g.pricetype = 10 -- special charge price
                         and g.active = 'Y'
                         and gscp.generalpricelist_id = g.generalpricelist_id
                         and gscp.specialcharge_id = specialchargeid
                         and gscp.startdate <= lv_pricedate
                       order by gscp.startdate desc) x
               where rownum = 1; -- take first
            fetch Vcsr
              into lv_price, lv_price_start_date, lv_pricelist_code, lv_pricelist_desc, lv_percentage, lv_minimumamount, lv_maximumamount, dummy;
            close Vcsr;
            if lv_price is not null then
              price_found := true;
            end if;
          exception
            when no_data_found then
              null;
            when others then
              null;
          end;
        else  --  generalpricelistid is not null  customer specific pricelist
          begin
            lv_price            := null;
            lv_price_start_date := null;
            lv_percentage       := null;
            lv_minimumamount    := null;
            lv_maximumamount    := null;
            lv_pricelist_type   := 'C'; -- customer specific
            open Vcsr for
              select x.*, rownum
                from (select cupri.price,
                             cupri.startdate,
                             cupri.percentage, cupri.minimumamount, cupri.maximumamount
                        from customerspecialchargeprice cupri
                       where cupri.specialcharge_id = specialchargeid
                         and cupri.customer_id = customerid
                         and cupri.startdate <= lv_pricedate
                       order by cupri.startdate desc) x
               where rownum = 1; -- take first
            fetch Vcsr
              into lv_price, lv_price_start_date, lv_percentage, lv_minimumamount, lv_maximumamount, dummy;
            close Vcsr;
            if ((lv_price is not null) or (lv_percentage is not null)) then
              price_found := true;
            end if;
          end; -- else
        end if; -- end if generalpricelistid is not null
        fetch VPriceListperCust
          into lv_generalpricelistid; -- continue with next pricelistpercustomer
      end loop; -- end while not VPriceListperCust%notfound
      /* --------------- E N D   L O O P ----------------------------------------------------------*/
      -- if no price found then take from special charge base table
      if (not price_found) then
        open Vcsr for
              select x.*, rownum
                from (select sc.newamount,
                             sc.startdate,
                             sc.newpercentage, sc.minimumamount, sc.maximumamount
                        from specialcharge sc
                       where sc.specialcharge_id = specialchargeid
                       order by sc.startdate desc) x
               where rownum = 1; -- take first
            fetch Vcsr
              into lv_price, lv_price_start_date, lv_percentage, lv_minimumamount, lv_maximumamount, dummy;
            close Vcsr;
            if ((lv_price is not null) or (lv_percentage is not null)) then
              price_found := true;
              lv_pricelist_type := 'B'; -- base table
            end if;
      end if;
    end if; -- result_code is null
    if price_found then
      if lv_fixedamount_yn ='Y'
       then
         price      := lv_price;
         percentage := null;
       else
         price      := null;
         percentage := lv_percentage;
      end if; -- lv_fixedamount_yn ='Y'
      price_found_yn   := 'Y';
      price_start_date := lv_price_start_date;
      result_code      := '0';
      select s.code
        into specialchargecode
        from specialcharge s
       where s.specialcharge_id = specialchargeid;
      result_desc      := decode_error('Y' || to_char(result_code),lv_fixedamount_str);
      price_list_type  := lv_pricelist_type;
      price_list_code  := lv_pricelist_code;
      minimumamount    := lv_minimumamount;
      maximumamount    := lv_maximumamount;
      case lv_pricelist_type
       when 'G' then price_list_desc := lv_pricelist_desc;
       when 'B' then price_list_desc := 'Base ' || lv_fixedamount_str;
        else price_list_desc := 'Customer Specific.';
      end case;
    else
      if result_code is null then -- no price/percentage found
        result_code := 1;
      end if;
      result_desc := decode_error('N' || result_code,lv_fixedamount_str);
    end if;
  exception
    when no_data_found then
      result_code := 1; -- no price/percentage found
      result_desc := decode_error('N' || result_code,lv_fixedamount_str);
    when others then
      result_code := 9;
      result_desc := decode_error('N' || result_code) || sqlerrm; -- ora error
  end Get_SpecCharge_Details;


/*--------------------------------------------------------------------------------------------------------*/
  function SpecialCharge_Details(customerid  in customer.customer_id%type,
                                 specialchargeid    in specialcharge.specialcharge_id%type,
                                 pricedate          in pricelistpercustomer.dateactive%type default null)
  return varchar2 is
  /* See package header for description of return string.   */

    price                   customerspecialchargeprice.price%type;
    price_found_yn          varchar2(1);
    price_start_date        customerspecialchargeprice.startdate%type;
    result_code             number;
    result_desc             varchar2(80);
    price_list_type         varchar2(1);
    price_list_code         generalpricelist.code%type;
    price_list_desc         generalpricelist.description%type;
    specialchargecode       specialcharge.code%type;
    percentage              customerspecialchargeprice.percentage%type;
    minimumamount           specialcharge.minimumamount%type;
    maximumamount           specialcharge.maximumamount%type;

    begin
      Get_SpecCharge_Details(customerid,
                             specialchargeid,
                             pricedate,
                             price,
                             price_found_yn,
                             price_start_date,
                             result_code,
                             result_desc,
                             price_list_type,
                             price_list_code,
                             price_list_desc,
                             specialchargecode,
                             percentage,
                             minimumamount,
                             maximumamount);
      return(rpad(nvl(to_char(price, '999999D9999'), ' '), 12) ||
             price_found_yn ||
             rpad(nvl(to_char(price_start_date, 'YYYYMMDD'),' '), 8) ||
             rpad(nvl(price_list_type, ' '), 1) ||
             rpad(nvl(substr(price_list_code,1,6), ' '), 6) ||  -- part 1  pos 1-6
             rpad(nvl(price_list_desc, ' '), 40) ||
             rpad(nvl(specialchargecode, ' '), 6) ||
             rpad(nvl(to_char(result_code), ' '), 1) ||
             rpad(nvl(result_desc, ' '), 80) ||
             rpad(nvl(to_char(percentage, '999D99'), ' '), 7) ||
             rpad(nvl(to_char(minimumamount, '999999D99'), ' '), 10) ||
             rpad(nvl(to_char(maximumamount, '999999D99'), ' '), 10) ||
             rpad(nvl(substr(price_list_code,7,9), ' '), 9)  -- part 2  pos 7-15
            );
    end SpecialCharge_Details;

/*--------------------------------------------------------------------------------------------------------*/
  function SpecialCharge_Price(customerid  in customer.customer_id%type,
                        specialchargeid    in specialcharge.specialcharge_id%type,
                        pricedate          in pricelistpercustomer.dateactive%type default null)
  return number is
  /* Returns the price, if not found, returns 0.  For use in calculations.  */

    price                   customerspecialchargeprice.price%type;
    price_found_yn          varchar2(1);
    price_start_date        customerspecialchargeprice.startdate%type;
    result_code             number;
    result_desc             varchar2(80);
    price_list_type         varchar2(1);
    price_list_code         generalpricelist.code%type;
    price_list_desc         generalpricelist.description%type;
    specialchargecode       specialcharge.code%type;
    percentage              customerspecialchargeprice.percentage%type;
    minimumamount           specialcharge.minimumamount%type;
    maximumamount           specialcharge.maximumamount%type;

    begin
      Get_SpecCharge_Details(customerid,
                             specialchargeid,
                             pricedate,
                             price,
                             price_found_yn,
                             price_start_date,
                             result_code,
                             result_desc,
                             price_list_type,
                             price_list_code,
                             price_list_desc,
                             specialchargecode,
                             percentage,
                             minimumamount,
                             maximumamount);
      return(nvl(price,0));
    end SpecialCharge_Price;

/*--------------------------------------------------------------------------------------------------------*/
  function SpecialCharge_Price_Display(customerid  in customer.customer_id%type,
                                specialchargeid    in specialcharge.specialcharge_id%type,
                                pricedate          in pricelistpercustomer.dateactive%type default null)
  return number is
  /* Returns the price, if not found, returns null.  For display/print purposes. */

    price                   customerspecialchargeprice.price%type;
    price_found_yn          varchar2(1);
    price_start_date        customerspecialchargeprice.startdate%type;
    result_code             number;
    result_desc             varchar2(80);
    price_list_type         varchar2(1);
    price_list_code         generalpricelist.code%type;
    price_list_desc         generalpricelist.description%type;
    specialchargecode       specialcharge.code%type;
    percentage              customerspecialchargeprice.percentage%type;
    minimumamount           specialcharge.minimumamount%type;
    maximumamount           specialcharge.maximumamount%type;

    begin
      Get_SpecCharge_Details(customerid,
                             specialchargeid,
                             pricedate,
                             price,
                             price_found_yn,
                             price_start_date,
                             result_code,
                             result_desc,
                             price_list_type,
                             price_list_code,
                             price_list_desc,
                             specialchargecode,
                             percentage,
                             minimumamount,
                             maximumamount);
      return(price);
    end SpecialCharge_Price_Display;

/*--------------------------------------------------------------------------------------------------------*/
  function SpecialCharge_Percentage(customerid  in customer.customer_id%type,
                        specialchargeid    in specialcharge.specialcharge_id%type,
                        pricedate          in pricelistpercustomer.dateactive%type default null)
  return number is
  /* Returns the percentage, if not found, returns 0.  For use in calculations.  */

    price                   customerspecialchargeprice.price%type;
    price_found_yn          varchar2(1);
    price_start_date        customerspecialchargeprice.startdate%type;
    result_code             number;
    result_desc             varchar2(80);
    price_list_type         varchar2(1);
    price_list_code         generalpricelist.code%type;
    price_list_desc         generalpricelist.description%type;
    specialchargecode       specialcharge.code%type;
    percentage              customerspecialchargeprice.percentage%type;
    minimumamount           specialcharge.minimumamount%type;
    maximumamount           specialcharge.maximumamount%type;

    begin
      Get_SpecCharge_Details(customerid,
                             specialchargeid,
                             pricedate,
                             price,
                             price_found_yn,
                             price_start_date,
                             result_code,
                             result_desc,
                             price_list_type,
                             price_list_code,
                             price_list_desc,
                             specialchargecode,
                             percentage,
                             minimumamount,
                             maximumamount);
      return(nvl(percentage,0));
    end SpecialCharge_Percentage;

/*--------------------------------------------------------------------------------------------------------*/
  function SpecialCharge_Percentage_Disp(customerid  in customer.customer_id%type,
                                specialchargeid    in specialcharge.specialcharge_id%type,
                                pricedate          in pricelistpercustomer.dateactive%type default null)
  return number is
  /* Returns the percentage, if not found, returns null.  For display/print purposes. */

    price                   customerspecialchargeprice.price%type;
    price_found_yn          varchar2(1);
    price_start_date        customerspecialchargeprice.startdate%type;
    result_code             number;
    result_desc             varchar2(80);
    price_list_type         varchar2(1);
    price_list_code         generalpricelist.code%type;
    price_list_desc         generalpricelist.description%type;
    specialchargecode       specialcharge.code%type;
    percentage              customerspecialchargeprice.percentage%type;
    minimumamount           specialcharge.minimumamount%type;
    maximumamount           specialcharge.maximumamount%type;

    begin
      Get_SpecCharge_Details(customerid,
                             specialchargeid,
                             pricedate,
                             price,
                             price_found_yn,
                             price_start_date,
                             result_code,
                             result_desc,
                             price_list_type,
                             price_list_code,
                             price_list_desc,
                             specialchargecode,
                             percentage,
                             minimumamount,
                             maximumamount);
      return(percentage);
    end SpecialCharge_Percentage_Disp;
/*--------------------------------------------------------------------------------------------------------*/
  function SpecialCharge_MinAmount(customerid  in customer.customer_id%type,
                        specialchargeid    in specialcharge.specialcharge_id%type,
                        pricedate          in pricelistpercustomer.dateactive%type default null)
  return number is
  /* Returns the MinAmount, if not found, returns 0.  For use in calculations.  */

    price                   customerspecialchargeprice.price%type;
    price_found_yn          varchar2(1);
    price_start_date        customerspecialchargeprice.startdate%type;
    result_code             number;
    result_desc             varchar2(80);
    price_list_type         varchar2(1);
    price_list_code         generalpricelist.code%type;
    price_list_desc         generalpricelist.description%type;
    specialchargecode       specialcharge.code%type;
    percentage              customerspecialchargeprice.percentage%type;
    minimumamount           specialcharge.minimumamount%type;
    maximumamount           specialcharge.maximumamount%type;

    begin
      Get_SpecCharge_Details(customerid,
                             specialchargeid,
                             pricedate,
                             price,
                             price_found_yn,
                             price_start_date,
                             result_code,
                             result_desc,
                             price_list_type,
                             price_list_code,
                             price_list_desc,
                             specialchargecode,
                             percentage,
                             minimumamount,
                             maximumamount);
      return(nvl(minimumamount,0));
    end SpecialCharge_MinAmount;

/*--------------------------------------------------------------------------------------------------------*/
  function SpecialCharge_MinAmount_Disp(customerid  in customer.customer_id%type,
                                specialchargeid    in specialcharge.specialcharge_id%type,
                                pricedate          in pricelistpercustomer.dateactive%type default null)
  return number is
  /* Returns the MinAmount, if not found, returns null.  For display/print purposes. */

    price                   customerspecialchargeprice.price%type;
    price_found_yn          varchar2(1);
    price_start_date        customerspecialchargeprice.startdate%type;
    result_code             number;
    result_desc             varchar2(80);
    price_list_type         varchar2(1);
    price_list_code         generalpricelist.code%type;
    price_list_desc         generalpricelist.description%type;
    specialchargecode       specialcharge.code%type;
    percentage              customerspecialchargeprice.percentage%type;
    minimumamount           specialcharge.minimumamount%type;
    maximumamount           specialcharge.maximumamount%type;

    begin
      Get_SpecCharge_Details(customerid,
                             specialchargeid,
                             pricedate,
                             price,
                             price_found_yn,
                             price_start_date,
                             result_code,
                             result_desc,
                             price_list_type,
                             price_list_code,
                             price_list_desc,
                             specialchargecode,
                             percentage,
                             minimumamount,
                             maximumamount);
      return(minimumamount);
    end SpecialCharge_MinAmount_Disp;
/*--------------------------------------------------------------------------------------------------------*/
  function SpecialCharge_MaxAmount(customerid  in customer.customer_id%type,
                        specialchargeid    in specialcharge.specialcharge_id%type,
                        pricedate          in pricelistpercustomer.dateactive%type default null)
  return number is
  /* Returns the MaxAmount, if not found, returns 0.  For use in calculations.  */

    price                   customerspecialchargeprice.price%type;
    price_found_yn          varchar2(1);
    price_start_date        customerspecialchargeprice.startdate%type;
    result_code             number;
    result_desc             varchar2(80);
    price_list_type         varchar2(1);
    price_list_code         generalpricelist.code%type;
    price_list_desc         generalpricelist.description%type;
    specialchargecode       specialcharge.code%type;
    percentage              customerspecialchargeprice.percentage%type;
    minimumamount           specialcharge.minimumamount%type;
    maximumamount           specialcharge.maximumamount%type;

    begin
      Get_SpecCharge_Details(customerid,
                             specialchargeid,
                             pricedate,
                             price,
                             price_found_yn,
                             price_start_date,
                             result_code,
                             result_desc,
                             price_list_type,
                             price_list_code,
                             price_list_desc,
                             specialchargecode,
                             percentage,
                             minimumamount,
                             maximumamount);
      return(nvl(maximumamount,0));
    end SpecialCharge_MaxAmount;

/*--------------------------------------------------------------------------------------------------------*/
  function SpecialCharge_MaxAmount_Disp(customerid  in customer.customer_id%type,
                                specialchargeid    in specialcharge.specialcharge_id%type,
                                pricedate          in pricelistpercustomer.dateactive%type default null)
  return number is
  /* Returns the MaxAmount, if not found, returns null.  For display/print purposes. */

    price                   customerspecialchargeprice.price%type;
    price_found_yn          varchar2(1);
    price_start_date        customerspecialchargeprice.startdate%type;
    result_code             number;
    result_desc             varchar2(80);
    price_list_type         varchar2(1);
    price_list_code         generalpricelist.code%type;
    price_list_desc         generalpricelist.description%type;
    specialchargecode       specialcharge.code%type;
    percentage              customerspecialchargeprice.percentage%type;
    minimumamount           specialcharge.minimumamount%type;
    maximumamount           specialcharge.maximumamount%type;

    begin
      Get_SpecCharge_Details(customerid,
                             specialchargeid,
                             pricedate,
                             price,
                             price_found_yn,
                             price_start_date,
                             result_code,
                             result_desc,
                             price_list_type,
                             price_list_code,
                             price_list_desc,
                             specialchargecode,
                             percentage,
                             minimumamount,
                             maximumamount);
      return(maximumamount);
    end SpecialCharge_MaxAmount_Disp;

/*--------------------------------------------------------------------------------------------------------*/
  procedure Get_Rent_Discount_Details(customerid             in customer.customer_id%type,
                                      productid              in product.product_id%type,
                                      sizedefinitionid       in sizedefinition.sizedefinition_id%type,
                                      finishingmethodid      in finishingmethod.finishingmethod_id%type,
                                      discountdate           in discountlistpercustomer.dateactive%type,
                                      discount               out customerrentdiscount.discount%type,
                                      discount_found_yn      out varchar2,
                                      discount_start_date    out customerrentdiscount.startdate%type,
                                      result_code            out number,
                                      result_desc            out varchar2,
                                      discount_list_type     out varchar2,
                                      discount_list_code     out generaldiscountlist.code%type,
                                      discount_list_desc     out generaldiscountlist.description%type) is

    discount_found              boolean;
    discount_not_relevant       boolean;
    i                           number;
    dummy                       number;
    lv_discountdate             date;
    lv_discountlist_type        varchar2(1);
    lv_discount                 customerrentdiscount.discount%type;
    lv_discount_start_date      customerrentdiscount.startdate%type;
    lv_generaldiscountlistid    generalrentdiscount.generaldiscountlist_id%type;
    lv_discountlist_code        generaldiscountlist.code%type;
    lv_discountlist_desc        generaldiscountlist.description%type;
    lv_productlistpercustomerid productlistpercustomer.productlistpercustomer_id%type;
    lv_billingmethod            productlistpercustomer.billingmethod%type;
    lv_priceusage               productlistpercustomer.priceusage%type;
    lv_typeofmerchandize        product.typeofmerchandise%type;
    lv_customergarmrentspec     customer.garmentrentspecification%type;
    lv_size                     sizedefinition.sizedefinition_id%type;
    type Tcsr                   is ref cursor;
    Vcsr                        Tcsr;
    VDiscountListperCust        Tcsr;

    function decode_error(errorcode in varchar2) return varchar2 is
      r varchar2(80);
    begin
      select decode(errorcode,
                    'Y0',
                    'Discount found',
                    'N1',
                    'Discount not defined',
                    'N2',
                    'Discount not relevant, billing method 3 with wash price only',
                    'N3',
                    'Discount not relevant, billing method = 4 piece rate only',
                    'N4',
                    'Discount not applicable, price defined at wearer level as wearer total price',
                    'N5',
                    'Discount not applicable, price defined at wearer function level',
                    'N8',
                    'Error in parameters',
                    'N9',
                    'ORA Error',
                    'Unknown error code')
        into r
        from dual;
      return r;
    end;

  begin -- main sales
    /* ------------------------ I N I T I A L I S A T I O N ------------------------------------------------------*/
    discount_found            := false;
    discount_not_relevant     := false;
    discount                  := null;
    discount_found_yn         := 'N';
    discount_start_date       := null;
    result_code               := null;
    result_desc               := null;
    discount_list_type        := null;
    discount_list_code        := null;
    discount_list_desc        := null;
    if discountdate is null then
      select sysdate into lv_discountdate from dual;
    else
      lv_discountdate := discountdate;
    end if;
    if (customerid is null) OR (productid is null) OR
       (sizedefinitionid is null) OR (finishingmethodid is null) then
      result_code := 8; -- error in parameters
    end if;
    lv_size := sizedefinitionid;
    i := 1;
    -- start outer loop on exact match, size * etc.
    /* ############################ O U T E R L O O P ############################################################*/
    while (i < 5) and (result_code is null) and (not discount_found) and
          (not discount_not_relevant) loop
      open VdiscountListperCust for
        Select discountlistpercustomer.generaldiscountlist_id
          from discountlistpercustomer
         where discountlistpercustomer.customer_id = customerid
           and discountlistpercustomer.pricetype = 2  -- this means rent discount
           and discountlistpercustomer.dateactive <= lv_discountdate
           and discountlistpercustomer.dateinactive >= lv_discountdate
         order by discountlistpercustomer.priority;
      fetch VdiscountListperCust
        into lv_generaldiscountlistid;
-- load weighingcategpercustomer etc to determine discount usage
      lv_productlistpercustomerid := null;
      lv_billingmethod            := null;
      lv_typeofmerchandize        := null;
      lv_priceusage               := null;
      lv_customergarmrentspec     := null;
      open Vcsr for
        select plpc.productlistpercustomer_id,
               plpc.billingmethod,
               plpc.priceusage,
               p.typeofmerchandise,
               c.garmentrentspecification
          from productlistpercustomer plpc, product p, customer c
         where plpc.customer_id = customerid
           and plpc.product_id = productid
           and plpc.sizedefinition_id = lv_size
           and plpc.finishingmethod_id = finishingmethodid
           and p.product_id = plpc.product_id
           and c.customer_id = plpc.customer_id
           and ((lv_discountdate >= plpc.dateactive and lv_discountdate <= plpc.dateinactive) or i>1)
         order by plpc.dateactive desc;
      if Vcsr%notfound then
        null;
      else
        fetch Vcsr
          into lv_productlistpercustomerid, lv_billingmethod, lv_priceusage, lv_typeofmerchandize, lv_customergarmrentspec;
        if lv_billingmethod = 3 and lv_priceusage = 3 then /*wash only*/
          discount_not_relevant := true;
          result_code           := 2; -- Price not relevant, billing method 3 with wash price only
        end if;
        if lv_billingmethod = 4 then
          discount_not_relevant := true;
          result_code           := 3; -- Price not relevant, billing method = 4 piece rate only
        end if;
        if lv_billingmethod = 1 and lv_typeofmerchandize=2 and lv_customergarmrentspec=5 then -- total wearer price
          discount_not_relevant := true;
          result_code           := 4; -- Price not relevant, wearer total price
        end if;
        if lv_billingmethod = 1 and lv_typeofmerchandize=2 and lv_customergarmrentspec=6 then -- wearer function price
          discount_not_relevant := true;
          result_code           := 5; -- Price not relevant, wearer function price
        end if;
      end if;
      -- start inner loop on discountlistpercustomer
      /* --------------------- I N N E R L O O P ----------------------------------------------------------*/
      while (not VdiscountListperCust%notfound) and (not discount_found) and
            (not discount_not_relevant) loop
        --if not discount_found then
        if lv_generaldiscountlistid is not null then
          -- general discount list
          begin
            lv_discount            := null;
            lv_discount_start_date := null;
            lv_discountlist_type   := 'G'; -- general
            open Vcsr for
              select x.*, rownum
                from (select grp.discount,
                             grp.startdate,
                             g.code,
                             g.description
                        from generalrentdiscount grp, generaldiscountlist g
                       where g.generaldiscountlist_id = lv_generaldiscountlistid
                         and g.pricetype = 2 -- rent discount
                         and g.active = 'Y'
                         and grp.generaldiscountlist_id = g.generaldiscountlist_id
                         and grp.product_id = productid
                         and grp.sizedefinition_id = lv_size
                         and grp.finishingmethod_id = finishingmethodid
                         and grp.startdate <= lv_discountdate
                       order by grp.startdate desc) x
               where rownum = 1; -- take first
            fetch Vcsr
              into lv_discount, lv_discount_start_date, lv_discountlist_code, lv_discountlist_desc, dummy;
            close Vcsr;
            if lv_discount is not null then
              discount_found := true;
            end if;
          exception
            when no_data_found then
              null;
            when others then
              null;
          end;
        else  --  generaldiscountlistid is not null  customer specific discountlist
          begin
            lv_discount            := null;
            lv_discount_start_date := null;
            lv_discountlist_type   := 'C'; -- customer specific
            open Vcsr for
              select x.*, rownum
                from (select cupri.discount,
                             cupri.startdate
                        from customerrentdiscount cupri
                       where cupri.productlistpercustomer_id = lv_productlistpercustomerid
                         and cupri.startdate <= lv_discountdate
                       order by cupri.startdate desc) x
               where rownum = 1; -- take first
            fetch Vcsr
              into lv_discount, lv_discount_start_date, dummy;
            close Vcsr;
            if lv_discount is not null then
              discount_found := true;
            end if;
          end; -- else
        end if; -- end if generaldiscountlistid is not null
        fetch VdiscountListperCust
          into lv_generaldiscountlistid; -- continue with next discountlistpercustomer
      end loop; -- end while not VdiscountListperCust%notfound
      /* --------------- E N D   I N N E R L O O P ----------------------------------------------------------*/
      if (not discount_found) and (not discount_not_relevant) then
        /* iterations:
           1 = with plpc active and passed size
           2 = with plpc active and size *.             Skipped when passed size was *
           3 = with plpc inactive and passed size.      Skipped when activ plpc was found
           4 = with plpc inactive and size *            Skipped when passed size was *
        */
        i := i + 1;
        if i = 2 then --  select sizedefinition_id for '*' code
          select sizedefinition.sizedefinition_id
            into lv_size
            from sizedefinition
           where sizedefinition.code = '*';
          if lv_size = sizedefinitionid -- in iteration 1 was size * already tried, because size * was passed.
            then i := i + 1;  -- skip iteration 2
          end if;
        end if;
        if i = 3 then -- try again original size but now for inactive plpc
          if lv_productlistpercustomerid is not null
            then i := 5; -- don't search further
            else lv_size := sizedefinitionid;
          end if;
        end if;
        if i = 4 then --  select sizedefinition_id for '*' code and try again for inactive plpc
          select sizedefinition.sizedefinition_id
            into lv_size
            from sizedefinition
           where sizedefinition.code = '*';
          if lv_size = sizedefinitionid -- in iteration 3 was size * already tried, because size * was passed.
            then i := i + 1;  -- skip iteration 4
          end if;
        end if;
      end if;
    end loop; -- end while i < 5
    /* ##################### E N D   O U T E R L O O P ############################################################*/
    if discount_found then
      discount            := lv_discount;
      discount_found_yn   := 'Y';
      discount_start_date := lv_discount_start_date;
      result_code      := '0';
      result_desc      := decode_error('Y' || to_char(result_code));
      discount_list_type  := lv_discountlist_type;
      discount_list_code  := lv_discountlist_code;
      if lv_discountlist_type = 'G'
        then discount_list_desc := lv_discountlist_desc;
        else discount_list_desc := 'Customer Specific.';
      end if;
    else
      if result_code is null then -- no discount found
        result_code := 1;
      end if;
      result_desc := decode_error('N' || result_code);
    end if;
  exception
    when no_data_found then
      result_code := 1; -- no discount found (often because fin method * not defined)
      result_desc := decode_error('N' || result_code);
    when others then
      result_code := 9;
      result_desc := decode_error('N' || result_code) || sqlerrm; -- ora error
  end Get_Rent_Discount_details;


/*--------------------------------------------------------------------------------------------------------*/
  function Rent_Discount_Details(customerid         in customer.customer_id%type,
                                 productid          in product.product_id%type,
                                 sizedefinitionid   in sizedefinition.sizedefinition_id%type,
                                 finishingmethodid  in finishingmethod.finishingmethod_id%type,
                                 discountdate       in discountlistpercustomer.dateactive%type default null)
  return varchar2 is
  /* See package header for description of return string.   */

    discount                   customerweightdiscount.discount%type;
    discount_found_yn          varchar2(1);
    discount_start_date        customerweightdiscount.startdate%type;
    result_code                number;
    result_desc                varchar2(80);
    discount_list_type         varchar2(1);
    discount_list_code         generaldiscountlist.code%type;
    discount_list_desc         generaldiscountlist.description%type;

    begin
      Get_Rent_Discount_Details(customerid,
                                productid,
                                sizedefinitionid,
                                finishingmethodid,
                                discountdate,
                                discount,
                                discount_found_yn,
                                discount_start_date,
                                result_code,
                                result_desc,
                                discount_list_type,
                                discount_list_code,
                                discount_list_desc);
      return(rpad(nvl(to_char(discount, '999999D9999'), ' '), 12) ||
             discount_found_yn ||
             rpad(nvl(to_char(discount_start_date, 'YYYYMMDD'),' '), 8) ||
             rpad(nvl(discount_list_type, ' '), 1) ||
             rpad(nvl(discount_list_code, ' '), 6) ||
             rpad(nvl(discount_list_desc, ' '), 40) ||
             rpad(nvl(to_char(result_code), ' '), 1) ||
             rpad(nvl(result_desc, ' '), 80)
            );
    end Rent_Discount_Details;

/*--------------------------------------------------------------------------------------------------------*/
  function Rent_Discount(customerid         in customer.customer_id%type,
                         productid          in product.product_id%type,
                         sizedefinitionid   in sizedefinition.sizedefinition_id%type,
                         finishingmethodid  in finishingmethod.finishingmethod_id%type,
                         discountdate       in discountlistpercustomer.dateactive%type default null)
  return number is
  /* Returns the discount, if not found, returns 0.  For use in calculations.  */

    discount                   customerweightdiscount.discount%type;
    discount_found_yn          varchar2(1);
    discount_start_date        customerweightdiscount.startdate%type;
    result_code                number;
    result_desc                varchar2(80);
    discount_list_type         varchar2(1);
    discount_list_code         generaldiscountlist.code%type;
    discount_list_desc         generaldiscountlist.description%type;

    begin
      Get_Rent_discount_Details(customerid,
                                productid,
                                sizedefinitionid,
                                finishingmethodid,
                                discountdate,
                                discount,
                                discount_found_yn,
                                discount_start_date,
                                result_code,
                                result_desc,
                                discount_list_type,
                                discount_list_code,
                                discount_list_desc);
      return(nvl(discount,0));
    end Rent_Discount;

/*--------------------------------------------------------------------------------------------------------*/
  function Rent_Discount_Display(customerid         in customer.customer_id%type,
                                 productid          in product.product_id%type,
                                 sizedefinitionid   in sizedefinition.sizedefinition_id%type,
                                 finishingmethodid  in finishingmethod.finishingmethod_id%type,
                                 discountdate       in discountlistpercustomer.dateactive%type default null)
  return number is
  /* Returns the discount, if not found, returns null.  For display/print purposes. */

    discount                   customerweightdiscount.discount%type;
    discount_found_yn          varchar2(1);
    discount_start_date        customerweightdiscount.startdate%type;
    result_code                number;
    result_desc                varchar2(80);
    discount_list_type         varchar2(1);
    discount_list_code         generaldiscountlist.code%type;
    discount_list_desc         generaldiscountlist.description%type;

    begin
      Get_Rent_Discount_Details(customerid,
                                productid,
                                sizedefinitionid,
                                finishingmethodid,
                                discountdate,
                                discount,
                                discount_found_yn,
                                discount_start_date,
                                result_code,
                                result_desc,
                                discount_list_type,
                                discount_list_code,
                                discount_list_desc);
      return(discount);
    end Rent_Discount_Display;


/*--------------------------------------------------------------------------------------------------------*/
  procedure Get_Wash_Discount_Details(customerid             in customer.customer_id%type,
                                      productid              in product.product_id%type,
                                      sizedefinitionid       in sizedefinition.sizedefinition_id%type,
                                      finishingmethodid      in finishingmethod.finishingmethod_id%type,
                                      discountdate           in discountlistpercustomer.dateactive%type,
                                      discount               out customerwashdiscount.discount%type,
                                      discount_found_yn      out varchar2,
                                      discount_start_date    out customerwashdiscount.startdate%type,
                                      result_code            out number,
                                      result_desc            out varchar2,
                                      discount_list_type     out varchar2,
                                      discount_list_code     out generaldiscountlist.code%type,
                                      discount_list_desc     out generaldiscountlist.description%type) is

    discount_found              boolean;
    discount_not_relevant       boolean;
    i                           number;
    dummy                       number;
    lv_discountdate             date;
    lv_discountlist_type        varchar2(1);
    lv_discount                 customerwashdiscount.discount%type;
    lv_discount_start_date      customerwashdiscount.startdate%type;
    lv_generaldiscountlistid    generalwashdiscount.generaldiscountlist_id%type;
    lv_discountlist_code        generaldiscountlist.code%type;
    lv_discountlist_desc        generaldiscountlist.description%type;
    lv_productlistpercustomerid productlistpercustomer.productlistpercustomer_id%type;
    lv_billingmethod            productlistpercustomer.billingmethod%type;
    lv_priceusage               productlistpercustomer.priceusage%type;
    lv_typeofmerchandize        product.typeofmerchandise%type;
    lv_size                     sizedefinition.sizedefinition_id%type;
    type Tcsr                   is ref cursor;
    Vcsr                        Tcsr;
    VDiscountListperCust        Tcsr;

    function decode_error(errorcode in varchar2) return varchar2 is
      r varchar2(80);
    begin
      select decode(errorcode,
                    'Y0',
                    'Discount found',
                    'N1',
                    'Discount not defined',
                    'N2',
                    'Discount not relevant, billing method 3 with rent price only',
                    'N3',
                    'Discount not relevant, billing method = 1 flat rated rent',
                    'N4',
                    'Discount not relevant, billing method = 2 rent based on minimum',
                    'N5',
                    'Discount not relevant, disposable / sales only item',
                    'N8',
                    'Error in parameters',
                    'N9',
                    'ORA Error',
                    'Unknown error code')
        into r
        from dual;
      return r;
    end;

  begin -- main sales
    /* ------------------------ I N I T I A L I S A T I O N ------------------------------------------------------*/
    discount_found            := false;
    discount_not_relevant     := false;
    discount                  := null;
    discount_found_yn         := 'N';
    discount_start_date       := null;
    result_code               := null;
    result_desc               := null;
    discount_list_type        := null;
    discount_list_code        := null;
    discount_list_desc        := null;
    if discountdate is null then
      select sysdate into lv_discountdate from dual;
    else
      lv_discountdate := discountdate;
    end if;
    if (customerid is null) OR (productid is null) OR
       (sizedefinitionid is null) OR (finishingmethodid is null) then
      result_code := 8; -- error in parameters
    end if;
    lv_size := sizedefinitionid;
    i := 1;
    -- start outer loop on exact match, size * etc.
    /* ############################ O U T E R L O O P ############################################################*/
    while (i < 5) and (result_code is null) and (not discount_found) and
          (not discount_not_relevant) loop
      open VdiscountListperCust for
        Select discountlistpercustomer.generaldiscountlist_id
          from discountlistpercustomer
         where discountlistpercustomer.customer_id = customerid
           and discountlistpercustomer.pricetype = 1  -- this means wash discount
           and discountlistpercustomer.dateactive <= lv_discountdate
           and discountlistpercustomer.dateinactive >= lv_discountdate
         order by discountlistpercustomer.priority;
      fetch VdiscountListperCust
        into lv_generaldiscountlistid;
-- load weighingcategpercustomer etc to determine discount usage
      lv_productlistpercustomerid := null;
      lv_billingmethod            := null;
      lv_typeofmerchandize        := null;
      lv_priceusage               := null;
      open Vcsr for
        select plpc.productlistpercustomer_id,
               plpc.billingmethod,
               plpc.priceusage,
               p.typeofmerchandise
          from productlistpercustomer plpc, product p
         where plpc.customer_id = customerid
           and plpc.product_id = productid
           and plpc.sizedefinition_id = lv_size
           and plpc.finishingmethod_id = finishingmethodid
           and p.product_id = plpc.product_id
           and ((lv_discountdate >= plpc.dateactive and lv_discountdate <= plpc.dateinactive) or i>1)
         order by plpc.dateactive desc;
      if Vcsr%notfound then
        null;
      else
        fetch Vcsr
          into lv_productlistpercustomerid, lv_billingmethod, lv_priceusage, lv_typeofmerchandize;
        if lv_billingmethod = 3 and lv_priceusage = 2 then /* rent price only*/
          discount_not_relevant := true;
          result_code           := 2; -- Price not relevant, billing method 3 with rent price only
        end if;
        if lv_billingmethod = 1 /*and pricetype<>2*/ then
          discount_not_relevant := true;
          result_code           := 3; -- Price not relevant, billing method = 1 flat rated rent only
        end if;
        if lv_billingmethod = 2 then
          discount_not_relevant := true;
          result_code           := 4; -- Price not relevant, billing method = 2 rent based on minimum
        end if;
        if lv_typeofmerchandize = 3 then
          discount_not_relevant := true;
          result_code           := 5; -- Price not relevant, disposable or sales only item.
        end if;
      end if;
      -- start inner loop on discountlistpercustomer
      /* --------------------- I N N E R L O O P ----------------------------------------------------------*/
      while (not VdiscountListperCust%notfound) and (not discount_found) and
            (not discount_not_relevant) loop
        --if not discount_found then
        if lv_generaldiscountlistid is not null then
          -- general discount list
          begin
            lv_discount            := null;
            lv_discount_start_date := null;
            lv_discountlist_type   := 'G'; -- general
            open Vcsr for
              select x.*, rownum
                from (select gwp.discount,
                             gwp.startdate,
                             g.code,
                             g.description
                        from generalwashdiscount gwp, generaldiscountlist g
                       where g.generaldiscountlist_id = lv_generaldiscountlistid
                         and g.pricetype = 1 -- wash discount
                         and g.active = 'Y'
                         and gwp.generaldiscountlist_id = g.generaldiscountlist_id
                         and gwp.product_id = productid
                         and gwp.sizedefinition_id = lv_size
                         and gwp.finishingmethod_id = finishingmethodid
                         and gwp.startdate <= lv_discountdate
                       order by gwp.startdate desc) x
               where rownum = 1; -- take first
            fetch Vcsr
              into lv_discount, lv_discount_start_date, lv_discountlist_code, lv_discountlist_desc, dummy;
            close Vcsr;
            if lv_discount is not null then
              discount_found := true;
            end if;
          exception
            when no_data_found then
              null;
            when others then
              null;
          end;
        else  --  generaldiscountlistid is not null  customer specific discountlist
          begin
            lv_discount            := null;
            lv_discount_start_date := null;
            lv_discountlist_type   := 'C'; -- customer specific
            open Vcsr for
              select x.*, rownum
                from (select cupri.discount,
                             cupri.startdate
                        from customerwashdiscount cupri
                       where cupri.productlistpercustomer_id = lv_productlistpercustomerid
                         and cupri.startdate <= lv_discountdate
                       order by cupri.startdate desc) x
               where rownum = 1; -- take first
            fetch Vcsr
              into lv_discount, lv_discount_start_date, dummy;
            close Vcsr;
            if lv_discount is not null then
              discount_found := true;
            end if;
          end; -- else
        end if; -- end if generaldiscountlistid is not null
        fetch VdiscountListperCust
          into lv_generaldiscountlistid; -- continue with next discountlistpercustomer
      end loop; -- end while not VdiscountListperCust%notfound
      /* --------------- E N D   I N N E R L O O P ----------------------------------------------------------*/
      if (not discount_found) and (not discount_not_relevant) then
        /* iterations:
           1 = with plpc active and passed size
           2 = with plpc active and size *.             Skipped when passed size was *
           3 = with plpc inactive and passed size.      Skipped when activ plpc was found
           4 = with plpc inactive and size *            Skipped when passed size was *
        */
        i := i + 1;
        if i = 2 then --  select sizedefinition_id for '*' code
          select sizedefinition.sizedefinition_id
            into lv_size
            from sizedefinition
           where sizedefinition.code = '*';
          if lv_size = sizedefinitionid -- in iteration 1 was size * already tried, because size * was passed.
            then i := i + 1;  -- skip iteration 2
          end if;
        end if;
        if i = 3 then -- try again original size but now for inactive plpc
          if lv_productlistpercustomerid is not null
            then i := 5; -- don't search further
            else lv_size := sizedefinitionid;
          end if;
        end if;
        if i = 4 then --  select sizedefinition_id for '*' code and try again for inactive plpc
          select sizedefinition.sizedefinition_id
            into lv_size
            from sizedefinition
           where sizedefinition.code = '*';
          if lv_size = sizedefinitionid -- in iteration 3 was size * already tried, because size * was passed.
            then i := i + 1;  -- skip iteration 4
          end if;
        end if;
      end if;
    end loop; -- end while i < 5
    /* ##################### E N D   O U T E R L O O P ############################################################*/
    if discount_found then
      discount            := lv_discount;
      discount_found_yn   := 'Y';
      discount_start_date := lv_discount_start_date;
      result_code      := '0';
      result_desc      := decode_error('Y' || to_char(result_code));
      discount_list_type  := lv_discountlist_type;
      discount_list_code  := lv_discountlist_code;
      if lv_discountlist_type = 'G'
        then discount_list_desc := lv_discountlist_desc;
        else discount_list_desc := 'Customer Specific.';
      end if;
    else
      if result_code is null then -- no discount found
        result_code := 1;
      end if;
      result_desc := decode_error('N' || result_code);
    end if;
  exception
    when no_data_found then
      result_code := 1; -- no discount found (often because fin method * not defined)
      result_desc := decode_error('N' || result_code);
    when others then
      result_code := 9;
      result_desc := decode_error('N' || result_code) || sqlerrm; -- ora error
  end Get_Wash_Discount_details;


/*--------------------------------------------------------------------------------------------------------*/
  function Wash_Discount_Details(customerid         in customer.customer_id%type,
                                 productid          in product.product_id%type,
                                 sizedefinitionid   in sizedefinition.sizedefinition_id%type,
                                 finishingmethodid  in finishingmethod.finishingmethod_id%type,
                                 discountdate       in discountlistpercustomer.dateactive%type default null)
  return varchar2 is
  /* See package header for description of return string.   */

    discount                   customerweightdiscount.discount%type;
    discount_found_yn          varchar2(1);
    discount_start_date        customerweightdiscount.startdate%type;
    result_code                number;
    result_desc                varchar2(80);
    discount_list_type         varchar2(1);
    discount_list_code         generaldiscountlist.code%type;
    discount_list_desc         generaldiscountlist.description%type;

    begin
      Get_Wash_Discount_Details(customerid,
                                productid,
                                sizedefinitionid,
                                finishingmethodid,
                                discountdate,
                                discount,
                                discount_found_yn,
                                discount_start_date,
                                result_code,
                                result_desc,
                                discount_list_type,
                                discount_list_code,
                                discount_list_desc);
      return(rpad(nvl(to_char(discount, '999999D9999'), ' '), 12) ||
             discount_found_yn ||
             rpad(nvl(to_char(discount_start_date, 'YYYYMMDD'),' '), 8) ||
             rpad(nvl(discount_list_type, ' '), 1) ||
             rpad(nvl(discount_list_code, ' '), 6) ||
             rpad(nvl(discount_list_desc, ' '), 40) ||
             rpad(nvl(to_char(result_code), ' '), 1) ||
             rpad(nvl(result_desc, ' '), 80)
            );
    end Wash_Discount_Details;

/*--------------------------------------------------------------------------------------------------------*/
  function Wash_Discount(customerid         in customer.customer_id%type,
                         productid          in product.product_id%type,
                         sizedefinitionid   in sizedefinition.sizedefinition_id%type,
                         finishingmethodid  in finishingmethod.finishingmethod_id%type,
                         discountdate       in discountlistpercustomer.dateactive%type default null)
  return number is
  /* Returns the discount, if not found, returns 0.  For use in calculations.  */

    discount                   customerweightdiscount.discount%type;
    discount_found_yn          varchar2(1);
    discount_start_date        customerweightdiscount.startdate%type;
    result_code                number;
    result_desc                varchar2(80);
    discount_list_type         varchar2(1);
    discount_list_code         generaldiscountlist.code%type;
    discount_list_desc         generaldiscountlist.description%type;

    begin
      Get_Wash_discount_Details(customerid,
                                productid,
                                sizedefinitionid,
                                finishingmethodid,
                                discountdate,
                                discount,
                                discount_found_yn,
                                discount_start_date,
                                result_code,
                                result_desc,
                                discount_list_type,
                                discount_list_code,
                                discount_list_desc);
      return(nvl(discount,0));
    end Wash_Discount;

/*--------------------------------------------------------------------------------------------------------*/
  function Wash_Discount_Display(customerid         in customer.customer_id%type,
                                 productid          in product.product_id%type,
                                 sizedefinitionid   in sizedefinition.sizedefinition_id%type,
                                 finishingmethodid  in finishingmethod.finishingmethod_id%type,
                                 discountdate       in discountlistpercustomer.dateactive%type default null)
  return number is
  /* Returns the discount, if not found, returns null.  For display/print purposes. */

    discount                   customerweightdiscount.discount%type;
    discount_found_yn          varchar2(1);
    discount_start_date        customerweightdiscount.startdate%type;
    result_code                number;
    result_desc                varchar2(80);
    discount_list_type         varchar2(1);
    discount_list_code         generaldiscountlist.code%type;
    discount_list_desc         generaldiscountlist.description%type;

    begin
      Get_Wash_Discount_Details(customerid,
                                productid,
                                sizedefinitionid,
                                finishingmethodid,
                                discountdate,
                                discount,
                                discount_found_yn,
                                discount_start_date,
                                result_code,
                                result_desc,
                                discount_list_type,
                                discount_list_code,
                                discount_list_desc);
      return(discount);
    end Wash_Discount_Display;

/*--------------------------------------------------------------------------------------------------------*/
  procedure Get_Weight_Discount_Details(customerid             in customer.customer_id%type,
                                        weighingcategoryid     in weighingcategory.weighingcategory_id%type ,
                                        soil_yn                in customerweightdiscount.soil%type,
                                        discountdate           in discountlistpercustomer.dateactive%type,
                                        discount               out customerweightdiscount.discount%type,
                                        discount_found_yn      out varchar2,
                                        discount_start_date    out customerweightdiscount.startdate%type,
                                        result_code            out number,
                                        result_desc            out varchar2,
                                        discount_list_type     out varchar2,
                                        discount_list_code     out generaldiscountlist.code%type,
                                        discount_list_desc     out generaldiscountlist.description%type) is

    discount_found              boolean;
    discount_not_relevant       boolean;
    i                           number;
    dummy                       number;
    lv_discountdate             date;
    lv_discountlist_type        varchar2(1);
    lv_discount                 customerweightdiscount.discount%type;
    lv_discount_start_date      customerweightdiscount.startdate%type;
    lv_generaldiscountlistid    generalwashdiscount.generaldiscountlist_id%type;
    lv_discountlist_code        generaldiscountlist.code%type;
    lv_discountlist_desc        generaldiscountlist.description%type;
    lv_weighingcatpercustomerid weighingcategpercustomer.weighingcategpercustomer_id%type;
    lv_invoice_method           weighingcategpercustomer.invoicemethod%type;
    type Tcsr                   is ref cursor;
    Vcsr                        Tcsr;
    VDiscountListperCust        Tcsr;

    function decode_error(errorcode in varchar2) return varchar2 is
      r varchar2(80);
    begin
      select decode(errorcode,
                    'Y0',
                    'Discount found',
                    'N1',
                    'Discount not defined',
                    'N2',
                    'Discount not relevant, weights not invoiced',
                    'N3',
                    'Discount not defined, weights invoiced soil, not clean ',
                    'N4',
                    'Discount not defined, weights invoiced clean, not soil ',
                    'N8',
                    'Error in parameters',
                    'N9',
                    'ORA Error',
                    'Unknown error code')
        into r
        from dual;
      return r;
    end;

  begin -- main sales
    /* ------------------------ I N I T I A L I S A T I O N ------------------------------------------------------*/
    discount_found            := false;
    discount_not_relevant     := false;
    discount                  := null;
    discount_found_yn         := 'N';
    discount_start_date       := null;
    result_code               := null;
    result_desc               := null;
    discount_list_type        := null;
    discount_list_code        := null;
    discount_list_desc        := null;
    if discountdate is null then
      select sysdate into lv_discountdate from dual;
    else
      lv_discountdate := discountdate;
    end if;
    if (customerid is null) OR (weighingcategoryid is null) OR (soil_yn not in ('Y','N')) then
      result_code := 8; -- error in parameters
    end if;
    i := 1;
    -- start outer loop on exact match, size * etc.
    /* ############################ O U T E R L O O P ############################################################*/
    while (i < 3) and (result_code is null) and (not discount_found) and
          (not discount_not_relevant) loop
      open VdiscountListperCust for
        Select discountlistpercustomer.generaldiscountlist_id
          from discountlistpercustomer
         where discountlistpercustomer.customer_id = customerid
           and discountlistpercustomer.pricetype = 5  -- this means weight discount
           and discountlistpercustomer.dateactive <= lv_discountdate
           and discountlistpercustomer.dateinactive >= lv_discountdate
         order by discountlistpercustomer.priority;
      fetch VdiscountListperCust
        into lv_generaldiscountlistid;
-- load weighingcategpercustomer etc to determine discount usage
      lv_weighingcatpercustomerid := null;
      lv_invoice_method           := null;
      open Vcsr for
        select wcpc.weighingcategpercustomer_id,
               wcpc.invoicemethod
          from weighingcategpercustomer wcpc
         where wcpc.customer_id = customerid
           and wcpc.weighingcategory_id = weighingcategoryid
           and ((lv_discountdate >= wcpc.dateactive and lv_discountdate <= wcpc.dateinactive) or i>1)
         order by wcpc.dateactive desc;
      if Vcsr%notfound then
        null;
      else
        fetch Vcsr
          into lv_weighingcatpercustomerid, lv_invoice_method;
        if lv_invoice_method =1 then /* no invoicing based on weight */
          discount_not_relevant := true;
          result_code        := 2; -- discount not relevant, customer is not invoiced based on weight
        end if;
        if lv_invoice_method in (2,4) and soil_yn = 'N' then /* configured soil, requested clean */
          discount_not_relevant := true;
          result_code        := 3; -- discount not relevant, customer is invoiced based on soil weight
        end if;
        if lv_invoice_method = 3 and soil_yn = 'Y' then /* configured clean, requested soil */
          discount_not_relevant := true;
          result_code        := 4; -- discount not relevant, customer is invoiced based on clean weight
        end if;
      end if;
      -- start inner loop on discountlistpercustomer
      /* --------------------- I N N E R L O O P ----------------------------------------------------------*/
      while (not VdiscountListperCust%notfound) and (not discount_found) and
            (not discount_not_relevant) loop
        --if not discount_found then
        if lv_generaldiscountlistid is not null then
          -- general discount list
          begin
            lv_discount            := null;
            lv_discount_start_date := null;
            lv_discountlist_type   := 'G'; -- general
            open Vcsr for
              select x.*, rownum
                from (select gwp.discount,
                             gwp.startdate,
                             g.code,
                             g.description
                        from generalweightdiscount gwp, generaldiscountlist g
                       where g.generaldiscountlist_id = lv_generaldiscountlistid
                         and g.pricetype = 5 -- weight discount
                         and g.active = 'Y'
                         and gwp.generaldiscountlist_id = g.generaldiscountlist_id
                         and gwp.weighingcategory_id = weighingcategoryid
                         and gwp.soil = soil_yn
                         and gwp.startdate <= lv_discountdate
                       order by gwp.startdate desc) x
               where rownum = 1; -- take first
            fetch Vcsr
              into lv_discount, lv_discount_start_date, lv_discountlist_code, lv_discountlist_desc, dummy;
            close Vcsr;
            if lv_discount is not null then
              discount_found := true;
            end if;
          exception
            when no_data_found then
              null;
            when others then
              null;
          end;
        else  --  generaldiscountlistid is not null  customer specific discountlist
          begin
            lv_discount            := null;
            lv_discount_start_date := null;
            lv_discountlist_type   := 'C'; -- customer specific
            open Vcsr for
              select x.*, rownum
                from (select cupri.discount,
                             cupri.startdate
                        from customerweightdiscount cupri
                       where cupri.weighingcategpercustomer_id = lv_weighingcatpercustomerid
                         and cupri.soil = soil_yn
                         and cupri.startdate <= lv_discountdate
                       order by cupri.startdate desc) x
               where rownum = 1; -- take first
            fetch Vcsr
              into lv_discount, lv_discount_start_date, dummy;
            close Vcsr;
            if lv_discount is not null then
              discount_found := true;
            end if;
          end; -- else
        end if; -- end if generaldiscountlistid is not null
        fetch VdiscountListperCust
          into lv_generaldiscountlistid; -- continue with next discountlistpercustomer
      end loop; -- end while not VdiscountListperCust%notfound
      /* --------------- E N D   I N N E R L O O P ----------------------------------------------------------*/
      if (not discount_found) and (not discount_not_relevant) then
        /* iterations:  1 = try to find for active weighingcategorypercustomer
                        2 = try to find for any weighingcategorypercustomer */
        i := i + 1;
        if lv_weighingcatpercustomerid is not null then  -- active wcpc already found, so do not perform extra loop
          i := i + 1;
        end if;
      end if;
    end loop; -- end while i < 3
    /* ##################### E N D   O U T E R L O O P ############################################################*/
    if discount_found then
      discount            := lv_discount;
      discount_found_yn   := 'Y';
      discount_start_date := lv_discount_start_date;
      result_code      := '0';
      result_desc      := decode_error('Y' || to_char(result_code));
      discount_list_type  := lv_discountlist_type;
      discount_list_code  := lv_discountlist_code;
      if lv_discountlist_type = 'G'
        then discount_list_desc := lv_discountlist_desc;
        else discount_list_desc := 'Customer Specific.';
      end if;
    else
      if result_code is null then -- no discount found
        result_code := 1;
      end if;
      result_desc := decode_error('N' || result_code);
    end if;
  exception
    when no_data_found then
      result_code := 1; -- no discount found (often because fin method * not defined)
      result_desc := decode_error('N' || result_code);
    when others then
      result_code := 9;
      result_desc := decode_error('N' || result_code) || sqlerrm; -- ora error
  end Get_Weight_Discount_details;


/*--------------------------------------------------------------------------------------------------------*/
  function Weight_Discount_Details(customerid         in customer.customer_id%type,
                                   weighingcategoryid in weighingcategory.weighingcategory_id%type ,
                                   soil_yn            in customerweightdiscount.soil%type,
                                   discountdate       in discountlistpercustomer.dateactive%type default null)
  return varchar2 is
  /* See package header for description of return string.   */

    discount                   customerweightdiscount.discount%type;
    discount_found_yn          varchar2(1);
    discount_start_date        customerweightdiscount.startdate%type;
    result_code                number;
    result_desc                varchar2(80);
    discount_list_type         varchar2(1);
    discount_list_code         generaldiscountlist.code%type;
    discount_list_desc         generaldiscountlist.description%type;

    begin
      Get_Weight_Discount_Details(customerid,
                                  weighingcategoryid,
                                  soil_yn,
                                  discountdate,
                                  discount,
                                  discount_found_yn,
                                  discount_start_date,
                                  result_code,
                                  result_desc,
                                  discount_list_type,
                                  discount_list_code,
                                  discount_list_desc);
      return(rpad(nvl(to_char(discount, '999999D9999'), ' '), 12) ||
             discount_found_yn ||
             rpad(nvl(to_char(discount_start_date, 'YYYYMMDD'),' '), 8) ||
             rpad(nvl(discount_list_type, ' '), 1) ||
             rpad(nvl(discount_list_code, ' '), 6) ||
             rpad(nvl(discount_list_desc, ' '), 40) ||
             rpad(nvl(to_char(result_code), ' '), 1) ||
             rpad(nvl(result_desc, ' '), 80)
            );
    end Weight_Discount_Details;

/*--------------------------------------------------------------------------------------------------------*/
  function Weight_Discount(customerid         in customer.customer_id%type,
                           weighingcategoryid in weighingcategory.weighingcategory_id%type ,
                           soil_yn            in customerweightdiscount.soil%type,
                           discountdate       in discountlistpercustomer.dateactive%type default null)
  return number is
  /* Returns the discount, if not found, returns 0.  For use in calculations.  */

    discount                   customerweightdiscount.discount%type;
    discount_found_yn          varchar2(1);
    discount_start_date        customerweightdiscount.startdate%type;
    result_code                number;
    result_desc                varchar2(80);
    discount_list_type         varchar2(1);
    discount_list_code         generaldiscountlist.code%type;
    discount_list_desc         generaldiscountlist.description%type;

    begin
      Get_Weight_discount_Details(customerid,
                                  weighingcategoryid,
                                  soil_yn,
                                  discountdate,
                                  discount,
                                  discount_found_yn,
                                  discount_start_date,
                                  result_code,
                                  result_desc,
                                  discount_list_type,
                                  discount_list_code,
                                  discount_list_desc);
      return(nvl(discount,0));
    end Weight_Discount;

/*--------------------------------------------------------------------------------------------------------*/
  function Weight_Discount_Display(customerid         in customer.customer_id%type,
                                   weighingcategoryid in weighingcategory.weighingcategory_id%type ,
                                   soil_yn            in customerweightdiscount.soil%type,
                                   discountdate       in discountlistpercustomer.dateactive%type default null)
  return number is
  /* Returns the discount, if not found, returns null.  For display/print purposes. */

    discount                   customerweightdiscount.discount%type;
    discount_found_yn          varchar2(1);
    discount_start_date        customerweightdiscount.startdate%type;
    result_code                number;
    result_desc                varchar2(80);
    discount_list_type         varchar2(1);
    discount_list_code         generaldiscountlist.code%type;
    discount_list_desc         generaldiscountlist.description%type;

    begin
      Get_Weight_Discount_Details(customerid,
                                  weighingcategoryid,
                                  soil_yn,
                                  discountdate,
                                  discount,
                                  discount_found_yn,
                                  discount_start_date,
                                  result_code,
                                  result_desc,
                                  discount_list_type,
                                  discount_list_code,
                                  discount_list_desc);
      return(discount);
    end Weight_Discount_Display;

/*--------------------------------------------------------------------------------------------------------*/
procedure Get_ResidRent_Price_Details(customerid             in customer.customer_id%type,
                                  productid              in product.product_id%type,
                                  sizedefinitionid       in sizedefinition.sizedefinition_id%type,
                                  finishingmethodid      in finishingmethod.finishingmethod_id%type,
                                  pricedate              in pricelistpercustomer.dateactive%type,
                                  price                  out customerrentprice.price%type,
                                  price_found_yn         out varchar2,
                                  price_start_date       out customerrentprice.startdate%type,
                                  result_code            out number,
                                  result_desc            out varchar2,
                                  price_list_type        out varchar2,
                                  price_list_code        out generalpricelist.code%type,
                                  price_list_desc        out generalpricelist.description%type,
                                  price_type_code        out customerrentprice.typeofrent%type,
                                  price_type_desc        out varchar2) is

    price_found                 boolean;
    price_not_relevant          boolean;
    lv_size                     sizedefinition.sizedefinition_id%type;
    lv_finmethod                finishingmethod.finishingmethod_id%type;
    i                           number;
    dummy                       number;
    lv_pricedate                date;
    lv_residentialrentid        residentialrent.residentialrent_id%type;
    lv_customergarmrentspec     customer.garmentrentspecification%type;
    lv_pricelist_type           varchar2(1);
    lv_custsplitinvperactivity  customer.splitinvoicesperactivity%type;
    lv_typeofmerchandize        product.typeofmerchandise%type;
    lv_typeofrent               generalrentprice.typeofrent%type;
    lv_typeofrent_general       generalrentprice.typeofrent%type;
    lv_typeofrent_specific      generalrentprice.typeofrent%type;
    lv_productgroup_id          product.productgroup_id%type;
    lv_price                    customerresidentialrentprice.price%type;
    lv_price_start_date         customerresidentialrentprice.startdate%type;
    lv_generalpricelistid       generalrentprice.generalpricelist_id%type;
    lv_pricelist_code           generalpricelist.code%type;
    lv_pricelist_desc           generalpricelist.description%type;
    type Tcsr                   is ref cursor;
    Vcsr                        Tcsr;
    VPriceListperCust           Tcsr;

    function decode_error(errorcode in varchar2) return varchar2 is
      r varchar2(80);
    begin
      select decode(errorcode,
                    'Y0',
                    'Price found',
                    'N1',
                    'Price not defined',
                    'N2',
                    'Price not relevant, billing method 3 with wash price only',
                    'N3',
                    'Price not relevant, billing method = 4 piece rate only',
                    'N4',
                    'Price not relevant, defined at wearer level as wearer total price',
                    'N5',
                    'Price not relevant, defined at wearer function level',
                    'N8',
                    'Error in parameters',
                    'N9',
                    'ORA Error',
                    'Unknown error code')
        into r
        from dual;
      return r;
    end;

    function decode_type_of_rent(fptypeofrent in number) return varchar2 is
      r varchar2(30);
    begin
      select decode(fptypeofrent,
                    9,
                    'Residential Rent',
                    'Unknown type of rent')
        into r
        from dual;
      return r;
    end;

  begin -- main rent price
    /* ------------------------ I N I T I A L I S A T I O N ------------------------------------------------------*/
    price_found            := false;
    price_not_relevant     := false;
    price                  := null;
    price_found_yn         := 'N';
    price_start_date       := null;
    result_code            := null;
    result_desc            := null;
    price_list_type        := null;
    price_list_code        := null;
    price_list_desc        := null;
    price_type_code        := null;
    price_type_desc        := null;
    if pricedate is null then
      select sysdate into lv_pricedate from dual;
    else
      lv_pricedate := pricedate;
    end if;
    lv_size           := sizedefinitionid;
    lv_finmethod      := finishingmethodid;
    if (customerid is null) OR (productid is null) OR
       (sizedefinitionid is null) OR (finishingmethodid is null) then
      result_code := 8; -- error in parameters
    end if;
    i := 1;
    -- start outer loop on exact match, size * etc.
    /* ############################ O U T E R L O O P ############################################################*/
    while (i < 5) and (result_code is null) and (not price_found) and
          (not price_not_relevant) loop
      open VPriceListperCust for
        Select pricelistpercustomer.generalpricelist_id
          from pricelistpercustomer
         where pricelistpercustomer.customer_id = customerid
           and pricelistpercustomer.pricetype = 2
           and pricelistpercustomer.dateactive <= lv_pricedate
           and pricelistpercustomer.dateinactive >= lv_pricedate
         order by pricelistpercustomer.priority;
      fetch VPriceListperCust
        into lv_generalpricelistid;
      lv_residentialrentid := null;
      lv_custsplitinvperactivity  := null;
      open Vcsr for
        select rr.residentialrent_id,
               p.typeofmerchandise,
               p.productgroup_id,
               c.garmentrentspecification,
               c.splitinvoicesperactivity
          from residentialrent rr, product p, customer c--productlistpercustomer plpc
         where rr.customer_id = customerid
           and rr.product_id = productid
           and p.product_id = productid
           and c.customer_id = customerid
           and rr.sizedefinition_id = lv_size
           and rr.finishingmethod_id = lv_finmethod
           and ((lv_pricedate >= rr.startdate and lv_pricedate <= rr.enddate) or i > 2)
           -- only filter on active in iteration 3 and 4
         order by rr.startdate desc;
      if Vcsr%notfound then
        null;
      else
        fetch Vcsr
          into lv_residentialrentid,
               lv_typeofmerchandize, lv_productgroup_id, lv_customergarmrentspec,
               lv_custsplitinvperactivity;
          lv_typeofrent := 9;
          lv_typeofrent_general := lv_typeofrent;
          lv_typeofrent_specific := lv_typeofrent;
      end if;
      close Vcsr;
      -- start inner loop on pricelistpercustomer
      /* --------------------- I N N E R L O O P ----------------------------------------------------------*/
      while (not VPriceListperCust%notfound) and (not price_found) and
            (not price_not_relevant) loop
        --if not price_found then
        if lv_generalpricelistid is not null then
          -- general price list
          begin
            lv_price            := null;
            lv_price_start_date := null;
            lv_pricelist_type   := 'G'; -- general
              open Vcsr for
                select x.*, rownum
                  from (select grp.price,
                               grp.startdate,
                               g.code,
                               g.description
                          from generalrentprice grp, generalpricelist g
                         where g.generalpricelist_id = lv_generalpricelistid
                           and g.pricetype = 2
                           and g.active = 'Y'
                           and grp.generalpricelist_id = g.generalpricelist_id
                           and grp.product_id = productid
                           and grp.sizedefinition_id = lv_size
                           and grp.finishingmethod_id = lv_finmethod
                           and grp.startdate <= lv_pricedate
                           and grp.typeofrent = lv_typeofrent_general
                         order by grp.startdate desc) x
                 where rownum = 1; -- take first
              fetch Vcsr
                into lv_price, lv_price_start_date, lv_pricelist_code, lv_pricelist_desc, dummy;
              close Vcsr;
            if lv_price is not null then
              price_found := true;
            end if;
          exception
            when no_data_found then
              null;
            when others then
              null;
          end;
        else  --  generalpricelistid is not null  customer specific pricelist
          begin
            lv_price            := null;
            lv_price_start_date := null;
            lv_pricelist_type   := 'C'; -- customer specific
            if lv_price is not null then
              price_found := true;
            end if;
          end; -- else
        end if; -- end if generalpricelistid is not null
        fetch VPriceListperCust
          into lv_generalpricelistid; -- continue with next pricelistpercustomer
      end loop; -- end while not VPriceListperCust%notfound
      /* --------------- E N D   I N N E R L O O P ----------------------------------------------------------*/
      if (not price_found) and (not price_not_relevant) then
        /* iterations:
           1 = with plpc active and passed size
           2 = with plpc active and size *.             Skipped when passed size was *
           3 = with plpc inactive and passed size.      Skipped when active plpc was found
           4 = with plpc inactive and size *            Skipped when passed size was *
        */
        i := i + 1;
        if i = 2 then --  select sizedefinition_id for '*' code
          select sizedefinition.sizedefinition_id
            into lv_size
            from sizedefinition
           where sizedefinition.code = '*';
          if lv_size = sizedefinitionid -- in iteration 1 was size * already tried, because size * was passed.
            then i := i + 1;  -- skip iteration 2
          end if;
        end if;
        if i = 3 then -- select original size again and try for an inactive plpc
          if lv_residentialrentid is not null
            then i := 5; -- don't search further
            else lv_size := sizedefinitionid;
          end if;
        end if;
        if i = 4 then --  select sizedefinition_id for '*' code try again for inactive plpc
          select sizedefinition.sizedefinition_id
            into lv_size
            from sizedefinition
           where sizedefinition.code = '*';
          if lv_size = sizedefinitionid -- in iteration 3 was size * already tried, because size * was passed.
            then i := i + 1;  -- skip iteration 4
          end if;
        end if;
      end if;
    end loop; -- end while i < 5
    /* ##################### E N D   O U T E R L O O P ############################################################*/
    if not price_found then
              open Vcsr for
                select x.*, rownum
                  from (select cupri.price,
                               cupri.startdate
                          from customerresidentialrentprice cupri
                         where cupri.customer_id = customerid
                               and cupri.product_id = productid
                               and cupri.sizedefinition_id = sizedefinitionid
                               and cupri.finishingmethod_id = finishingmethodid
                               and cupri.startdate <= lv_pricedate
                         order by cupri.startdate desc) x
                 where rownum = 1; -- take first
              fetch Vcsr
                into lv_price, lv_price_start_date, dummy;
              close Vcsr;
            if lv_price is not null then
              price_found := true;
            end if;
    end if;
    if price_found then
      price            := lv_price;
      price_found_yn   := 'Y';
      price_start_date := lv_price_start_date;
      result_desc      := decode_error('Y' || to_char(result_code));
      price_list_type  := lv_pricelist_type;
      price_list_code  := lv_pricelist_code;
      if lv_pricelist_type = 'G'
        then price_list_desc := lv_pricelist_desc;
        else price_list_desc := 'Customer Specific.';
      end if;
      price_type_code := lv_typeofrent;
      price_type_desc := decode_type_of_rent(price_type_code);
    else
      if result_code is null then -- no price found
        result_code := 1;
      end if;
      result_desc := decode_error('N' || result_code);
    end if;
  exception
    when no_data_found then
      result_code := 1; -- no price found (often because fin method * not defined)
      result_desc := decode_error('N' || result_code);
    when others then
      result_code := 9;
      result_desc := decode_error('N' || result_code) || sqlerrm; -- ora error
  end Get_ResidRent_Price_Details;

/*--------------------------------------------------------------------------------------------------------*/
function ResidentialRent_Price_Details(customerid        in customer.customer_id%type,
                            productid         in product.product_id%type,
                            sizedefinitionid  in sizedefinition.sizedefinition_id%type,
                            finishingmethodid in finishingmethod.finishingmethod_id%type,
                            pricedate         in pricelistpercustomer.dateactive%type default null)
 return varchar2 is
  /* See package header for description of return string.   */

    price                   customerrentprice.price%type;
    price_found_yn          varchar2(1);
    price_start_date        customerrentprice.startdate%type;
    result_code             number;
    result_desc             varchar2(100);
    price_list_type         varchar2(1);
    price_list_code         generalpricelist.code%type;
    price_list_desc         generalpricelist.description%type;
    price_type_code         customerrentprice.typeofrent%type;
    price_type_desc         varchar2(30);

    begin
      Get_ResidRent_Price_Details(customerid,
                             productid,
                             sizedefinitionid,
                             finishingmethodid,
                             pricedate,
                             price,
                             price_found_yn,
                             price_start_date,
                             result_code,
                             result_desc,
                             price_list_type,
                             price_list_code,
                             price_list_desc,
                             price_type_code,
                             price_type_desc);
      return(rpad(nvl(to_char(price, '999999D9999'), ' '), 12) ||
             price_found_yn ||
             rpad(nvl(to_char(price_start_date, 'YYYYMMDD'),' '), 8) ||
             rpad(nvl(price_list_type, ' '), 1) ||
             rpad(nvl(substr(price_list_code,1,6), ' '), 6) ||  -- part 1  pos 1-6
             rpad(nvl(price_list_desc, ' '), 40) ||
             rpad(nvl(to_char(result_code), ' '), 1) ||
             rpad(nvl(result_desc, ' '), 80) ||
             rpad(nvl(to_char(price_type_code), ' '), 1) ||
             rpad(nvl(price_type_desc, ' '), 30) ||
             rpad(nvl(substr(price_list_code,7,9), ' '), 9)  -- part 2  pos 7-15
             );
    end ResidentialRent_Price_Details;

/*--------------------------------------------------------------------------------------------------------*/
function ResidentialRent_Price(customerid        in customer.customer_id%type,
                    productid         in product.product_id%type,
                    sizedefinitionid  in sizedefinition.sizedefinition_id%type,
                    finishingmethodid in finishingmethod.finishingmethod_id%type,
                    pricedate         in pricelistpercustomer.dateactive%type default null)
 return number is
  /* Returns the price, if not found, returns 0.  For use in calculations.  */

    price                   customerrentprice.price%type;
    price_found_yn          varchar2(1);
    price_start_date        customerrentprice.startdate%type;
    result_code             number;
    result_desc             varchar2(100);
    price_list_type         varchar2(1);
    price_list_code         generalpricelist.code%type;
    price_list_desc         generalpricelist.description%type;
    price_type_code         customerrentprice.typeofrent%type;
    price_type_desc         varchar2(30);

    begin
      Get_ResidRent_Price_Details(customerid,
                             productid,
                             sizedefinitionid,
                             finishingmethodid,
                             pricedate,
                             price,
                             price_found_yn,
                             price_start_date,
                             result_code,
                             result_desc,
                             price_list_type,
                             price_list_code,
                             price_list_desc,
                             price_type_code,
                             price_type_desc);
      return(nvl(price,0));
    end ResidentialRent_Price;

/*--------------------------------------------------------------------------------------------------------*/
function ResidentialRent_Price_Display(customerid        in customer.customer_id%type,
                            productid         in product.product_id%type,
                            sizedefinitionid  in sizedefinition.sizedefinition_id%type,
                            finishingmethodid in finishingmethod.finishingmethod_id%type,
                            pricedate         in pricelistpercustomer.dateactive%type default null)
 return number is
  /* Returns the price, if not found, returns null.  For display/print purposes. */

    price                   customerrentprice.price%type;
    price_found_yn          varchar2(1);
    price_start_date        customerrentprice.startdate%type;
    result_code             number;
    result_desc             varchar2(100);
    price_list_type         varchar2(1);
    price_list_code         generalpricelist.code%type;
    price_list_desc         generalpricelist.description%type;
    price_type_code         customerrentprice.typeofrent%type;
    price_type_desc         varchar2(30);

    begin
      Get_ResidRent_Price_Details(customerid,
                             productid,
                             sizedefinitionid,
                             finishingmethodid,
                             pricedate,
                             price,
                             price_found_yn,
                             price_start_date,
                             result_code,
                             result_desc,
                             price_list_type,
                             price_list_code,
                             price_list_desc,
                             price_type_code,
                             price_type_desc);
      return(price);
    end ResidentialRent_Price_Display;


/*--------------------------------------------------------------------------------------------------------*/
  procedure Get_Loan_Price_Details(customerid             in customer.customer_id%type,
                                   productid              in product.product_id%type,
                                   sizedefinitionid       in sizedefinition.sizedefinition_id%type,
                                   finishingmethodid      in finishingmethod.finishingmethod_id%type,
                                   pricedate              in pricelistpercustomer.dateactive%type,
                                   price                  out customerrentprice.price%type,
                                   price_found_yn         out varchar2,
                                   price_start_date       out customerrentprice.startdate%type,
                                   result_code            out number,
                                   result_desc            out varchar2,
                                   price_list_type        out varchar2,
                                   price_list_code        out generalpricelist.code%type,
                                   price_list_desc        out generalpricelist.description%type) is

    price_found                 boolean;
    price_not_relevant          boolean;
    lv_size                     sizedefinition.sizedefinition_id%type;
    lv_finmethod                finishingmethod.finishingmethod_id%type;
    i                           number;
    dummy                       number;
    lv_pricedate                date;
    lv_productlistpercustomerid productlistpercustomer.productlistpercustomer_id%type;
    lv_pricelist_type           varchar2(1);
    lv_typeofrent               customerrentprice.typeofrent%type;
    lv_price                    customerrentprice.price%type;
    lv_price_start_date         customerrentprice.startdate%type;
    lv_generalpricelistid       generalrentprice.generalpricelist_id%type;
    lv_pricelist_code           generalpricelist.code%type;
    lv_pricelist_desc           generalpricelist.description%type;
    type Tcsr                   is ref cursor;
    Vcsr                        Tcsr;
    VPriceListperCust           Tcsr;

    function decode_error(errorcode in varchar2) return varchar2 is
      r varchar2(80);
    begin
      select decode(errorcode,
                    'Y0',
                    'Price found',
                    'N1',
                    'Price not defined',
                    'N8',
                    'Error in parameters',
                    'N9',
                    'ORA Error',
                    'Unknown error code')
        into r
        from dual;
      return r;
    end;

  begin -- main loan price
    /* ------------------------ I N I T I A L I S A T I O N ------------------------------------------------------*/
    lv_typeofrent          := 10; -- Loan
    price_found            := false;
    price_not_relevant     := false;
    price                  := null;
    price_found_yn         := 'N';
    price_start_date       := null;
    result_code            := null;
    result_desc            := null;
    price_list_type        := null;
    price_list_code        := null;
    price_list_desc        := null;
    if pricedate is null then
      select sysdate into lv_pricedate from dual;
    else
      lv_pricedate := pricedate;
    end if;
    lv_size           := sizedefinitionid;
    lv_finmethod      := finishingmethodid;
    if (customerid is null) OR (productid is null) OR
       (sizedefinitionid is null) OR (finishingmethodid is null) then
      result_code := 8; -- error in parameters
    end if;
    i := 1;
    -- start outer loop on exact match, size * etc.
    /* ############################ O U T E R L O O P ############################################################*/
    while (i < 5) and (result_code is null) and (not price_found) and
          (not price_not_relevant) loop
      open VPriceListperCust for
        Select pricelistpercustomer.generalpricelist_id
          from pricelistpercustomer
         where pricelistpercustomer.customer_id = customerid
           and pricelistpercustomer.pricetype = 2
           and pricelistpercustomer.dateactive <= lv_pricedate
           and pricelistpercustomer.dateinactive >= lv_pricedate
         order by pricelistpercustomer.priority;
      fetch VPriceListperCust
        into lv_generalpricelistid;
      -- load plpc
      lv_productlistpercustomerid := null;
      open Vcsr for
        select plpc.productlistpercustomer_id
          from productlistpercustomer plpc
         where plpc.customer_id = customerid
           and plpc.product_id = productid
           and plpc.sizedefinition_id = lv_size
           and plpc.finishingmethod_id = lv_finmethod
           and ((lv_pricedate >= plpc.dateactive and lv_pricedate <= plpc.dateinactive) or i>2)
           -- only filter on active in iteration 3 and 4
         order by plpc.dateactive desc;
      if Vcsr%notfound then
        null;
      else
        fetch Vcsr
          into lv_productlistpercustomerid;
      end if;
      close Vcsr;
      -- start inner loop on pricelistpercustomer
      /* --------------------- I N N E R L O O P ----------------------------------------------------------*/
      while (not VPriceListperCust%notfound) and (not price_found) and
            (not price_not_relevant) loop
        --if not price_found then
        if lv_generalpricelistid is not null then
          -- general price list
          begin
            lv_price            := null;
            lv_price_start_date := null;
            lv_pricelist_type   := 'G'; -- general
            open Vcsr for
              select x.*, rownum
                from (select grp.price,
                             grp.startdate,
                             g.code,
                             g.description
                        from generalrentprice grp, generalpricelist g
                       where g.generalpricelist_id = lv_generalpricelistid
                         and g.pricetype = 2
                         and g.active = 'Y'
                         and grp.generalpricelist_id = g.generalpricelist_id
                         and grp.product_id = productid
                         and grp.sizedefinition_id = lv_size
                         and grp.finishingmethod_id = lv_finmethod
                         and grp.typeofrent = lv_typeofrent
                         and grp.startdate <= lv_pricedate
                       order by grp.startdate desc) x
               where rownum = 1; -- take first
            fetch Vcsr
              into lv_price, lv_price_start_date, lv_pricelist_code, lv_pricelist_desc, dummy;
            close Vcsr;
            if lv_price is not null then
              price_found := true;
            end if;
          exception
            when no_data_found then
              null;
            when others then
              null;
          end;
        else  --  generalpricelistid is not null  customer specific pricelist
          begin
            lv_price            := null;
            lv_price_start_date := null;
            lv_pricelist_type   := 'C'; -- customer specific
            open Vcsr for
              select x.*, rownum
                from (select cupri.price,
                             cupri.startdate
                        from customerrentprice cupri
                       where cupri.productlistpercustomer_id = lv_productlistpercustomerid
                         and cupri.startdate <= lv_pricedate
                         and cupri.typeofrent = lv_typeofrent
                       order by cupri.startdate desc) x
               where rownum = 1; -- take first
            fetch Vcsr
              into lv_price, lv_price_start_date, dummy;
            close Vcsr;

            if lv_price is not null then
              price_found := true;
            end if;
          end; -- else
        end if; -- end if generalpricelistid is not null
        fetch VPriceListperCust
          into lv_generalpricelistid; -- continue with next pricelistpercustomer
      end loop; -- end while not VPriceListperCust%notfound
      /* --------------- E N D   I N N E R L O O P ----------------------------------------------------------*/
      if (not price_found) and (not price_not_relevant) then
        /* iterations:
           1 = with plpc active and passed size
           2 = with plpc active and size *.             Skipped when passed size was *
           3 = with plpc inactive and passed size.      Skipped when active plpc was found
           4 = with plpc inactive and size *            Skipped when passed size was *
        */
        i := i + 1;
        if i = 2 then --  select sizedefinition_id for '*' code
          select sizedefinition.sizedefinition_id
            into lv_size
            from sizedefinition
           where sizedefinition.code = '*';
          if lv_size = sizedefinitionid -- in iteration 1 was size * already tried, because size * was passed.
            then i := i + 1;  -- skip iteration 2
          end if;
        end if;
        if i = 3 then -- select original size again and try for an inactive plpc
          if lv_productlistpercustomerid is not null
            then i := 5; -- don't search further
            else lv_size := sizedefinitionid;
          end if;
        end if;
        if i = 4 then --  select sizedefinition_id for '*' code try again for inactive plpc
          select sizedefinition.sizedefinition_id
            into lv_size
            from sizedefinition
           where sizedefinition.code = '*';
          if lv_size = sizedefinitionid -- in iteration 3 was size * already tried, because size * was passed.
            then i := i + 1;  -- skip iteration 4
          end if;
        end if;
      end if;
    end loop; -- end while i < 5
    /* ##################### E N D   O U T E R L O O P ############################################################*/
    if price_found then
      price            := lv_price;
      price_found_yn   := 'Y';
      price_start_date := lv_price_start_date;
      result_code      := 0;
      result_desc      := decode_error('Y' || to_char(result_code));
      price_list_type  := lv_pricelist_type;
      price_list_code  := lv_pricelist_code;
      if lv_pricelist_type = 'G'
        then price_list_desc := lv_pricelist_desc;
        else price_list_desc := 'Customer Specific.';
      end if;
    else
      if result_code is null then -- no price found
        result_code := 1;
      end if;
      result_desc := decode_error('N' || result_code);
    end if;
  exception
    when no_data_found then
      result_code := 1; -- no price found (often because fin method * not defined)
      result_desc := decode_error('N' || result_code);
    when others then
      result_code := 9;
      result_desc := decode_error('N' || result_code) || sqlerrm; -- ora error
  end Get_Loan_Price_details;

/*--------------------------------------------------------------------------------------------------------*/
function Loan_Price_Details(customerid        in customer.customer_id%type,
                            productid         in product.product_id%type,
                            sizedefinitionid  in sizedefinition.sizedefinition_id%type,
                            finishingmethodid in finishingmethod.finishingmethod_id%type,
                            pricedate         in pricelistpercustomer.dateactive%type default null)
 return varchar2 is
  /* See package header for description of return string.   */

    price                   customerrentprice.price%type;
    price_found_yn          varchar2(1);
    price_start_date        customerrentprice.startdate%type;
    result_code             number;
    result_desc             varchar2(80);
    price_list_type         varchar2(1);
    price_list_code         generalpricelist.code%type;
    price_list_desc         generalpricelist.description%type;

    begin
      Get_Loan_Price_Details(customerid,
                             productid,
                             sizedefinitionid,
                             finishingmethodid,
                             pricedate,
                             price,
                             price_found_yn,
                             price_start_date,
                             result_code,
                             result_desc,
                             price_list_type,
                             price_list_code,
                             price_list_desc);
      return(rpad(nvl(to_char(price, '999999D9999'), ' '), 12) ||
             price_found_yn ||
             rpad(nvl(to_char(price_start_date, 'YYYYMMDD'),' '), 8) ||
             rpad(nvl(price_list_type, ' '), 1) ||
             rpad(nvl(substr(price_list_code,1,6), ' '), 6) ||  -- part 1  pos 1-6
             rpad(nvl(price_list_desc, ' '), 40) ||
             rpad(nvl(to_char(result_code), ' '), 1) ||
             rpad(nvl(result_desc, ' '), 80) ||
             rpad(nvl(substr(price_list_code,7,9), ' '), 9)  -- part 2  pos 7-15
             );
    end Loan_Price_Details;

/*--------------------------------------------------------------------------------------------------------*/
function Loan_Price(customerid        in customer.customer_id%type,
                    productid         in product.product_id%type,
                    sizedefinitionid  in sizedefinition.sizedefinition_id%type,
                    finishingmethodid in finishingmethod.finishingmethod_id%type,
                    pricedate         in pricelistpercustomer.dateactive%type default null)
 return number is
  /* Returns the loan price, if not found, returns 0.  For use in calculations.  */

    price                   customerrentprice.price%type;
    price_found_yn          varchar2(1);
    price_start_date        customerrentprice.startdate%type;
    result_code             number;
    result_desc             varchar2(80);
    price_list_type         varchar2(1);
    price_list_code         generalpricelist.code%type;
    price_list_desc         generalpricelist.description%type;

    begin
      Get_Loan_Price_Details(customerid,
                             productid,
                             sizedefinitionid,
                             finishingmethodid,
                             pricedate,
                             price,
                             price_found_yn,
                             price_start_date,
                             result_code,
                             result_desc,
                             price_list_type,
                             price_list_code,
                             price_list_desc);
      return(nvl(price,0));
    end Loan_Price;

/*--------------------------------------------------------------------------------------------------------*/
function Loan_Price_Display(customerid        in customer.customer_id%type,
                            productid         in product.product_id%type,
                            sizedefinitionid  in sizedefinition.sizedefinition_id%type,
                            finishingmethodid in finishingmethod.finishingmethod_id%type,
                            pricedate         in pricelistpercustomer.dateactive%type default null)
 return number is
  /* Returns the loan price, if not found, returns null.  For display/print purposes. */

    price                   customerrentprice.price%type;
    price_found_yn          varchar2(1);
    price_start_date        customerrentprice.startdate%type;
    result_code             number;
    result_desc             varchar2(80);
    price_list_type         varchar2(1);
    price_list_code         generalpricelist.code%type;
    price_list_desc         generalpricelist.description%type;

    begin
      Get_Loan_Price_Details(customerid,
                             productid,
                             sizedefinitionid,
                             finishingmethodid,
                             pricedate,
                             price,
                             price_found_yn,
                             price_start_date,
                             result_code,
                             result_desc,
                             price_list_type,
                             price_list_code,
                             price_list_desc);
      return(price);
    end Loan_Price_Display;

/*--------------------------------------------------------------------------------------------------------*/
function Budget_Price (
       FPcustomerid customerbudgetdefinition.customer_id%type,
       FPcode       customerbudgetdefinition.code%type,
       FPreferencedate   date
       ) return number is
  /* Returns the budget price, if not found, returns 0.  For use in calculations.  */
  vcustbudgetdefid customerbudgetdefinition.customerbudgetdefinition_id%type;
  vprice number;

begin

  select cbd.customerbudgetdefinition_id
    into vcustbudgetdefid
    from customerbudgetdefinition cbd
   where cbd.customer_id = FPcustomerid
     and cbd.code = FPcode;

  begin
  select cbp.price
    into vprice
    from customerbudgetprice cbp
   where cbp.customerbudgetdefinition_id = vcustbudgetdefid
     and cbp.startdate <= FPreferencedate
     and cbp.startdate =
         (select max(cbp.startdate)
            from customerbudgetprice cbp
           where cbp.customerbudgetdefinition_id = vcustbudgetdefid
             and cbp.startdate <= FPreferencedate);

  return vprice;
  exception
    when no_data_found then return 0;
  end;
end Budget_Price;

    
  function sizebillinggroups_defined return boolean is
    no_sizebillinggroups number;
  begin
    select count(1) into no_sizebillinggroups from sizebillinggroup;
    return no_sizebillinggroups > 0;
  end sizebillinggroups_defined;
  
  function customer_uses_specialprices(customerid in customer.customer_id%type)
    return boolean is
    use_special_prices customer.useprodsizesurchargepricing%type;
  begin
    select c.useprodsizesurchargepricing
      into use_special_prices
      from customer c
     where c.customer_id = customerid;
    return use_special_prices = 'Y';
  exception
    when no_data_found then
      return false;
  end customer_uses_specialprices;
  
  function get_standard_szbillinggroup return number is
    standard_sizebillinggroupid sizebillinggroup.sizebillinggroup_id%type;
  begin
    select sz.sizebillinggroup_id
      into standard_sizebillinggroupid
      from sizebillinggroup sz
     where sz.standard = 'Y';
    return standard_sizebillinggroupid;
  exception
    when no_data_found then
      return 0;
  end get_standard_szbillinggroup;
  
  function get_product_size_billinggroup(productid in product.product_id%type,
                                         sizeid    in sizedefinition.sizedefinition_id%type)
    return number is
    sizebillinggroupid sizebillinggroup.sizebillinggroup_id%type;
  begin
    select ps.sizebillinggroup_id
      into sizebillinggroupid
      from productsize ps
     where ps.product_id = productid
       and ps.sizedefinition_id = sizeid;
    return nvl(sizebillinggroupid, 0);
  exception
    when no_data_found then
      begin
        select sd.sizebillinggroup_id
          into sizebillinggroupid
          from sizedefinition sd
         where sd.sizedefinition_id = sizeid;
        return sizebillinggroupid;
      exception
        when no_data_found then
          return 0;
      end;
  end get_product_size_billinggroup;
  
  procedure get_size_billing_groups(customerid                     in customer.customer_id%type,
                                    productid                      in product.product_id%type,
                                    sizedefinitionid               in sizedefinition.sizedefinition_id%type,
                                    lv_standard_sizebillinggroupid out sizebillinggroup.sizebillinggroup_id%type,
                                    lv_specific_sizebillinggroupid out sizebillinggroup.sizebillinggroup_id%type) is
    lv_checksizebillinggroup    boolean;
    lv_customer_uses_sz_billing boolean;
  begin
    lv_checksizebillinggroup := sizebillinggroups_defined();
    if lv_checksizebillinggroup then
      lv_customer_uses_sz_billing    := customer_uses_specialprices(customerid);
      lv_standard_sizebillinggroupid := get_standard_szbillinggroup();
      if lv_customer_uses_sz_billing then
        lv_specific_sizebillinggroupid := get_product_size_billinggroup(productid,
                                                                        sizedefinitionid);
      else
        lv_specific_sizebillinggroupid := lv_standard_sizebillinggroupid;
      end if;
    else
      lv_standard_sizebillinggroupid := 0;
      lv_specific_sizebillinggroupid := 0;
    end if;
  end get_size_billing_groups;

END PACK_PRICE;
/
