1 PACKAGE BODY MSD_PRICE_LIST_PP AS
2 /* $Header: msdplppb.pls 120.1 2011/02/07 07:03:41 rissingh ship $ */
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 instance = p_instance_id
72 and price_list_name = a_price_list_name(i)
73 and sr_product_lvl_pk = a_sr_product_lvl_pk(i)
74 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'))
75 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'));
76
77 END IF;
78
79 /* Update valid flag to 1 for price list, If primary uom flag is set to 'Yes' */
80
81 OPEN c_multi_price_list(p_instance_id, p_price_list);
82 FETCH c_multi_price_list BULK COLLECT INTO a_price_list_name, a_sr_product_lvl_pk, a_start_date, a_end_date;
83 CLOSE c_multi_price_list;
84
85 IF a_price_list_name.exists(1) THEN
86 FORALL i IN a_price_list_name.FIRST..a_price_list_name.LAST
87 update msd_st_price_list
88 set valid_flag = 1
89 where primary_uom_flag = 'Y'
90 and instance = p_instance_id
91 and price_list_name = a_price_list_name(i)
92 and sr_product_lvl_pk = a_sr_product_lvl_pk(i)
93 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'))
94 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'));
95 END IF;
96
97
98 /* Update valid flag to 1 for price list, If the price list has low priority
99 If more than one price list lines has same low priority, then update the first price list row
100 */
101
102 OPEN c_multi_price_list(p_instance_id, p_price_list);
103 FETCH c_multi_price_list BULK COLLECT INTO a_price_list_name, a_sr_product_lvl_pk, a_start_date, a_end_date;
104 CLOSE c_multi_price_list;
105
106 IF a_price_list_name.exists(1) THEN
107 FORALL i IN a_price_list_name.FIRST..a_price_list_name.LAST
108 update msd_st_price_list
109 set valid_flag = 1
110 where instance = p_instance_id
111 and price_list_name = a_price_list_name(i)
112 and sr_product_lvl_pk = a_sr_product_lvl_pk(i)
113 and nvl(priority, -999) = (select nvl(min(b.priority), -999)
114 from msd_st_price_list b
115 where b.price_list_name = a_price_list_name(i)
116 and b.sr_product_lvl_pk = a_sr_product_lvl_pk(i)
117 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'))
118 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'))
119 )
120 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'))
121 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'))
122 and rownum < 2;
123
124 END IF;
125
126 /* Update valid flag to 1 for price list, If only one price list line matches
127 with price list name, item, start date and end date
128 */
129
130
131 OPEN c_single_price_list(p_instance_id, p_price_list);
132 FETCH c_single_price_list BULK COLLECT INTO a_price_list_name, a_sr_product_lvl_pk, a_start_date, a_end_date;
133 CLOSE c_single_price_list;
134
135 IF a_price_list_name.exists(1) THEN
136
137 FORALL i IN a_price_list_name.FIRST..a_price_list_name.LAST
138 update msd_st_price_list
139 set valid_flag = 1
140 where instance = p_instance_id
141 and price_list_name = a_price_list_name(i)
142 and sr_product_lvl_pk = a_sr_product_lvl_pk(i)
143 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'))
144 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'));
145
146 END IF;
147
148 /* delete all dublicate price list lines which is not set to valid flag to 1 */
149
150 delete from msd_st_price_list
151 where instance = p_instance_id
152 and price_list_name like nvl(p_price_list, price_list_name)
153 and nvl(valid_flag,0) <> 1;
154
155 commit;
156
157
158 EXCEPTION
159 WHEN OTHERS THEN
160 fnd_file.put_line(fnd_file.log, 'Errors in Price List Post Processing');
161 fnd_file.put_line(fnd_file.log, substr(SQLERRM, 1, 1000));
162 end ;
163
164 END MSD_PRICE_LIST_PP;
[Home] [Help]