APPS.MSD_PRICE_LIST_PP dependencies on MSD_ST_PRICE_LIST

Line 13: from msd_st_price_list a

9:
10: CURSOR c_multi_price_list (p_instance_id in varchar2,
11: p_price_list in varchar2 ) IS
12: select price_list_name, sr_product_lvl_pk, start_date, end_date
13: from msd_st_price_list a
14: where instance = p_instance_id
15: and price_list_name like nvl(p_price_list, price_list_name)
16: and not exists (select 1 from msd_st_price_list b
17: where b.instance = a.instance

Line 16: and not exists (select 1 from msd_st_price_list b

12: select price_list_name, sr_product_lvl_pk, start_date, end_date
13: from msd_st_price_list a
14: where instance = p_instance_id
15: and price_list_name like nvl(p_price_list, price_list_name)
16: and not exists (select 1 from msd_st_price_list b
17: where b.instance = a.instance
18: and b.price_list_name = a.price_list_name
19: 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'))
20: 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'))

Line 31: from msd_st_price_list

27:
28: CURSOR c_single_price_list (p_instance_id in varchar2,
29: p_price_list in varchar2 ) IS
30: select price_list_name, sr_product_lvl_pk, start_date, end_date
31: from msd_st_price_list
32: where instance = p_instance_id
33: and price_list_name like nvl(p_price_list, price_list_name)
34: and nvl(valid_flag, 0) <> 1
35: group by price_list_name, sr_product_lvl_pk, start_date, end_date

Line 39: type price_list_name_tab is table of msd_st_price_list.price_list_name%type;

35: group by price_list_name, sr_product_lvl_pk, start_date, end_date
36: having count(*) = 1;
37:
38:
39: type price_list_name_tab is table of msd_st_price_list.price_list_name%type;
40: TYPE sr_product_lvl_pk_tab is table of msd_st_price_list.sr_product_lvl_pk%TYPE;
41: type start_date_tab is table of msd_st_price_list.start_date%type;
42: type end_date_tab is table of msd_st_price_list.end_date%type;
43:

Line 40: TYPE sr_product_lvl_pk_tab is table of msd_st_price_list.sr_product_lvl_pk%TYPE;

36: having count(*) = 1;
37:
38:
39: type price_list_name_tab is table of msd_st_price_list.price_list_name%type;
40: TYPE sr_product_lvl_pk_tab is table of msd_st_price_list.sr_product_lvl_pk%TYPE;
41: type start_date_tab is table of msd_st_price_list.start_date%type;
42: type end_date_tab is table of msd_st_price_list.end_date%type;
43:
44:

Line 41: type start_date_tab is table of msd_st_price_list.start_date%type;

37:
38:
39: type price_list_name_tab is table of msd_st_price_list.price_list_name%type;
40: TYPE sr_product_lvl_pk_tab is table of msd_st_price_list.sr_product_lvl_pk%TYPE;
41: type start_date_tab is table of msd_st_price_list.start_date%type;
42: type end_date_tab is table of msd_st_price_list.end_date%type;
43:
44:
45: a_price_list_name price_list_name_tab;

Line 42: type end_date_tab is table of msd_st_price_list.end_date%type;

38:
39: type price_list_name_tab is table of msd_st_price_list.price_list_name%type;
40: TYPE sr_product_lvl_pk_tab is table of msd_st_price_list.sr_product_lvl_pk%TYPE;
41: type start_date_tab is table of msd_st_price_list.start_date%type;
42: type end_date_tab is table of msd_st_price_list.end_date%type;
43:
44:
45: a_price_list_name price_list_name_tab;
46: a_sr_product_lvl_pk sr_product_lvl_pk_tab;

Line 65: update msd_st_price_list

61:
62: IF a_price_list_name.exists(1) THEN
63:
64: FORALL i IN a_price_list_name.FIRST..a_price_list_name.LAST
65: update msd_st_price_list
66: set valid_flag = 1
67: where price_list_uom = (select base_uom
68: from msd_item_list_price
69: where sr_item_pk = a_sr_product_lvl_pk(i)

Line 86: update msd_st_price_list

82: CLOSE c_multi_price_list;
83:
84: IF a_price_list_name.exists(1) THEN
85: FORALL i IN a_price_list_name.FIRST..a_price_list_name.LAST
86: update msd_st_price_list
87: set valid_flag = 1
88: where primary_uom_flag = 'Y'
89: and price_list_name = a_price_list_name(i)
90: and sr_product_lvl_pk = a_sr_product_lvl_pk(i)

Line 106: update msd_st_price_list

102: CLOSE c_multi_price_list;
103:
104: IF a_price_list_name.exists(1) THEN
105: FORALL i IN a_price_list_name.FIRST..a_price_list_name.LAST
106: update msd_st_price_list
107: set valid_flag = 1
108: where price_list_name = a_price_list_name(i)
109: and sr_product_lvl_pk = a_sr_product_lvl_pk(i)
110: and nvl(priority, -999) = (select nvl(min(b.priority), -999)

Line 111: from msd_st_price_list b

107: set valid_flag = 1
108: where price_list_name = a_price_list_name(i)
109: and sr_product_lvl_pk = a_sr_product_lvl_pk(i)
110: and nvl(priority, -999) = (select nvl(min(b.priority), -999)
111: from msd_st_price_list b
112: where b.price_list_name = a_price_list_name(i)
113: and b.sr_product_lvl_pk = a_sr_product_lvl_pk(i)
114: 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'))
115: 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'))

Line 135: update msd_st_price_list

131:
132: IF a_price_list_name.exists(1) THEN
133:
134: FORALL i IN a_price_list_name.FIRST..a_price_list_name.LAST
135: update msd_st_price_list
136: set valid_flag = 1
137: where price_list_name = a_price_list_name(i)
138: and sr_product_lvl_pk = a_sr_product_lvl_pk(i)
139: 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'))

Line 146: delete from msd_st_price_list

142: END IF;
143:
144: /* delete all dublicate price list lines which is not set to valid flag to 1 */
145:
146: delete from msd_st_price_list
147: where instance = p_instance_id
148: and price_list_name like nvl(p_price_list, price_list_name)
149: and nvl(valid_flag,0) <> 1;
150: