APPS.MSD_PRICE_LIST_PP SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 12

select price_list_name, sr_product_lvl_pk, start_date, end_date
from msd_st_price_list a
where instance = p_instance_id
and price_list_name like nvl(p_price_list, price_list_name)
and not exists (select 1 from msd_st_price_list b
                where b.instance = a.instance
                and b.price_list_name = a.price_list_name
                and nvl(b.start_date, to_date('01/01/1000', 'MM/DD/YYYY')) = nvl(a.start_date, to_date('01/01/1000', 'MM/DD/YYYY'))
                and nvl(b.end_date, to_date('01/01/1000', 'MM/DD/YYYY')) = nvl(a.end_date, to_date('01/01/1000', 'MM/DD/YYYY'))
                and b.sr_product_lvl_pk = a.sr_product_lvl_pk
                and b.valid_flag = 1
               )
group by price_list_name, sr_product_lvl_pk, start_date, end_date
having count(*) > 1;

Line: 30

select price_list_name, sr_product_lvl_pk, start_date, end_date
from msd_st_price_list
where instance = p_instance_id
and price_list_name like nvl(p_price_list, price_list_name)
and nvl(valid_flag, 0) <> 1
group by price_list_name, sr_product_lvl_pk, start_date, end_date
having count(*) = 1;

Line: 52

  /* Select all price list information which has more than on price list lines matches
     with price list name, item, start date and end date

     Update valid flag to 1 for price list, if price list uom matches with item's base uom
  */

  OPEN  c_multi_price_list(p_instance_id, p_price_list);

Line: 65

        update msd_st_price_list
        set valid_flag = 1
        where price_list_uom = (select base_uom
                                from msd_item_list_price
                                where sr_item_pk = a_sr_product_lvl_pk(i)
                                and instance = p_instance_id )
        and instance = p_instance_id
        and price_list_name = a_price_list_name(i)
        and sr_product_lvl_pk = a_sr_product_lvl_pk(i)
        and nvl(start_date, to_date('01/01/1000', 'MM/DD/YYYY')) = nvl(a_start_date(i), to_date('01/01/1000', 'MM/DD/YYYY'))
        and nvl(end_date, to_date('01/01/1000', 'MM/DD/YYYY')) = nvl(a_end_date(i), to_date('01/01/1000', 'MM/DD/YYYY'));

Line: 79

   /* Update valid flag to 1 for price list, If primary uom flag is set to 'Yes'  */

   OPEN  c_multi_price_list(p_instance_id, p_price_list);

Line: 87

         update msd_st_price_list
         set valid_flag = 1
         where primary_uom_flag = 'Y'
         and instance = p_instance_id
         and price_list_name = a_price_list_name(i)
         and sr_product_lvl_pk = a_sr_product_lvl_pk(i)
         and nvl(start_date, to_date('01/01/1000', 'MM/DD/YYYY')) = nvl(a_start_date(i), to_date('01/01/1000', 'MM/DD/YYYY'))
         and nvl(end_date, to_date('01/01/1000', 'MM/DD/YYYY')) = nvl(a_end_date(i), to_date('01/01/1000', 'MM/DD/YYYY'));

Line: 98

   /* Update valid flag to 1 for price list, If the price list has low priority
      If more than one price list lines has same low priority, then update the first price list row
   */

   OPEN  c_multi_price_list(p_instance_id, p_price_list);

Line: 108

         update msd_st_price_list
         set valid_flag = 1
         where instance = p_instance_id
               and price_list_name = a_price_list_name(i)
               and sr_product_lvl_pk = a_sr_product_lvl_pk(i)
               and nvl(priority, -999) = (select nvl(min(b.priority), -999)
                                          from msd_st_price_list b
                                          where b.price_list_name = a_price_list_name(i)
                                          and b.sr_product_lvl_pk = a_sr_product_lvl_pk(i)
                                          and nvl(b.start_date, to_date('01/01/1000', 'MM/DD/YYYY')) = nvl(a_start_date(i), to_date('01/01/1000', 'MM/DD/YYYY'))
                                          and nvl(b.end_date, to_date('01/01/1000', 'MM/DD/YYYY')) = nvl(a_end_date(i), to_date('01/01/1000', 'MM/DD/YYYY'))
                                          )
               and nvl(start_date, to_date('01/01/1000', 'MM/DD/YYYY')) = nvl(a_start_date(i), to_date('01/01/1000', 'MM/DD/YYYY'))
               and nvl(end_date, to_date('01/01/1000', 'MM/DD/YYYY')) = nvl(a_end_date(i), to_date('01/01/1000', 'MM/DD/YYYY'))
               and rownum < 2;

Line: 126

  /* Update valid flag to 1 for price list, If only one price list line matches
     with price list name, item, start date and end date
  */


  OPEN  c_single_price_list(p_instance_id, p_price_list);

Line: 138

        update msd_st_price_list
        set valid_flag = 1
        where instance = p_instance_id
        and price_list_name = a_price_list_name(i)
        and sr_product_lvl_pk = a_sr_product_lvl_pk(i)
        and nvl(start_date, to_date('01/01/1000', 'MM/DD/YYYY')) = nvl(a_start_date(i), to_date('01/01/1000', 'MM/DD/YYYY'))
        and nvl(end_date, to_date('01/01/1000', 'MM/DD/YYYY')) = nvl(a_end_date(i), to_date('01/01/1000', 'MM/DD/YYYY'));

Line: 148

   /* delete all dublicate price list lines which is not set to valid flag to 1 */

   delete from msd_st_price_list
   where instance = p_instance_id
   and price_list_name like nvl(p_price_list, price_list_name)
   and nvl(valid_flag,0) <> 1;