1 PACKAGE BODY MSD_PRICE_LIST_PP AS
2 /* $Header: msdplppb.pls 115.1 2004/03/30 12:55:48 sudekuma noship $ */
3
4
5 PROCEDURE price_list_post_process( errbuf OUT NOCOPY VARCHAR2,
6 retcode OUT NOCOPY VARCHAR2,
7 p_instance_id IN VARCHAR2,
8 p_price_list IN VARCHAR2 ) is
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
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'))
21 and b.sr_product_lvl_pk = a.sr_product_lvl_pk
22 and b.valid_flag = 1
23 )
24 group by price_list_name, sr_product_lvl_pk, start_date, end_date
25 having count(*) > 1;
26
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
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
45 a_price_list_name price_list_name_tab;
46 a_sr_product_lvl_pk sr_product_lvl_pk_tab;
47 a_start_date start_date_tab;
48 a_end_date end_date_tab;
49
50 BEGIN
51
52 /* Select all price list information which has more than on price list lines matches
53 with price list name, item, start date and end date
54
55 Update valid flag to 1 for price list, if price list uom matches with item's base uom
56 */
57
58 OPEN c_multi_price_list(p_instance_id, p_price_list);
59 FETCH c_multi_price_list BULK COLLECT INTO a_price_list_name, a_sr_product_lvl_pk, a_start_date, a_end_date;
60 CLOSE c_multi_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)
70 and instance = p_instance_id )
71 and price_list_name = a_price_list_name(i)
72 and sr_product_lvl_pk = a_sr_product_lvl_pk(i)
73 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'))
74 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'));
75
76 END IF;
77
78 /* Update valid flag to 1 for price list, If primary uom flag is set to 'Yes' */
79
80 OPEN c_multi_price_list(p_instance_id, p_price_list);
81 FETCH c_multi_price_list BULK COLLECT INTO a_price_list_name, a_sr_product_lvl_pk, a_start_date, a_end_date;
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)
91 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'))
92 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'));
93 END IF;
94
95
96 /* Update valid flag to 1 for price list, If the price list has low priority
97 If more than one price list lines has same low priority, then update the first price list row
98 */
99
100 OPEN c_multi_price_list(p_instance_id, p_price_list);
101 FETCH c_multi_price_list BULK COLLECT INTO a_price_list_name, a_sr_product_lvl_pk, a_start_date, a_end_date;
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)
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'))
116 )
117 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'))
118 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'))
119 and rownum < 2;
120
121 END IF;
122
123 /* Update valid flag to 1 for price list, If only one price list line matches
124 with price list name, item, start date and end date
125 */
126
127
128 OPEN c_single_price_list(p_instance_id, p_price_list);
129 FETCH c_single_price_list BULK COLLECT INTO a_price_list_name, a_sr_product_lvl_pk, a_start_date, a_end_date;
130 CLOSE c_single_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'))
140 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'));
141
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
151 commit;
152
153
154 EXCEPTION
155 WHEN OTHERS THEN
156 fnd_file.put_line(fnd_file.log, 'Errors in Price List Post Processing');
157 fnd_file.put_line(fnd_file.log, substr(SQLERRM, 1, 1000));
158 end ;
159
160 END MSD_PRICE_LIST_PP;
[Home] [Help]