1 PACKAGE BODY Msc_VERTICAL_PLAN_SC AS
2 /* $Header: MSCVERPB.pls 120.3 2012/03/01 11:43:50 skathera ship $ */
3
4 Procedure populate_bucketed_quantity (
5 arg_plan_id IN NUMBER,
6 arg_instance_id IN NUMBER,
7 arg_org_id IN NUMBER,
8 arg_item_id IN NUMBER,
9 arg_cutoff_date IN DATE,
10 arg_bucket_type IN VARCHAR2,
11 p_quantity_string OUT NOCOPY VARCHAR2,
12 p_period_string OUT NOCOPY VARCHAR2,
13 p_period_count OUT NOCOPY NUMBER) IS
14 g_param varchar2(3):=':';
15 -- ----------------------------------------
16 -- This cursor selects the activity for
17 -- current data /*Added decode for PTS and item_type_value in sum for bug 12636440 */
18 -- ----------------------------------------
19 CURSOR mrp_vertical_plan IS
20 SELECT
21 new_due_date new_date,
22 inventory_item_id item_id,
23 organization_id org_id,
24 sum(decode(order_type,
25 70,
26 decode(nvl(produces_to_stock,2),1, quantity_rate,0),92,
27 decode(nvl(produces_to_stock,2),1, quantity_rate,0),
28 decode(NVL(item_type_value,1),1,quantity_rate,0))) new_quantity
29 from msc_vertical_plan_v plan
30 where plan.inventory_item_id = arg_item_id
31 and plan.organization_id = arg_org_id
32 and plan.plan_id = arg_plan_id
33 and plan.sr_instance_id = arg_instance_id
34 -- and plan.new_due_date < arg_cutoff_date
35 GROUP BY
36 new_due_date,
37 organization_id,
38 inventory_item_id
39 ORDER BY
40 1, 2, 3;
41
42 activity_rec mrp_vertical_plan%ROWTYPE;
43
44 TYPE calendar_date IS TABLE OF DATE INDEX BY BINARY_INTEGER;
45 -- TYPE column_number IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
46 TYPE column_char IS TABLE OF VARCHAR2(50) INDEX BY BINARY_INTEGER;
47
48 var_dates calendar_date; -- Holds the start dates of buckets
49 bucket_cells_tab column_number; -- Holds the quantities per bucket
50 v_period column_char; -- holds the period name
51 last_item_id NUMBER := -1;
52 last_org_id NUMBER := -1;
53 last_plan VARCHAR2(10) := '-1';
54 last_org VARCHAR2(10);
55 last_item VARCHAR2(20);
56
57 bucket_counter BINARY_INTEGER := 0; -- Id of the current bucket
58 counter BINARY_INTEGER := 0;
59 g_num_of_buckets NUMBER;
60 -- =============================================================================
61 --
62 -- add_to_plan add the 'quantity' to the correct cell of the current bucket.
63 --
64 -- =============================================================================
65 PROCEDURE add_to_plan(location IN NUMBER,
66 quantity IN NUMBER) IS
67 BEGIN
68 bucket_cells_tab(location) := NVL(bucket_cells_tab(location),0) + quantity;
69
70 END;
71
72
73 PROCEDURE flush_item_plan IS
74 BEGIN
75
76 FOR counter in 1..g_num_of_buckets LOOP
77 IF counter > 1 THEN -- calculate the running total
78
79 bucket_cells_tab(counter) := bucket_cells_tab(counter)+
80 bucket_cells_tab(counter-1);
81 END IF;
82
83 p_quantity_string:= p_quantity_string
84 ||g_param||
85 fnd_number.number_to_canonical(bucket_cells_tab(counter));
86 END LOOP;
87
88 bucket_counter := 2;
89
90 FOR counter IN 1..g_num_of_buckets LOOP
91 bucket_cells_tab(counter) := 0;
92 END LOOP;
93
94 END flush_item_plan;
95
96 PROCEDURE get_bucket_dates IS
97
98 CURSOR day_bucket IS
99 SELECT to_char(mpsd.calendar_date,'DD-MM-RR'),
100 mpsd.calendar_date
101 FROM msc_trading_partners tp,
102 msc_calendar_dates mpsd,
103 msc_plans mp
104 WHERE mpsd.calendar_code = tp.calendar_code
105 and mpsd.sr_instance_id = tp.sr_instance_id
106 and mpsd.exception_set_id = tp.calendar_exception_set_id
107 and mpsd.seq_num is not null
108 and tp.sr_instance_id = arg_instance_id
109 and tp.sr_tp_id = arg_org_id
110 and tp.partner_type =3
111 and mp.plan_id = arg_plan_id
112 and mpsd.calendar_date between mp.data_start_date
113 and mp.cutoff_date
114 -- and rownum <30
115 order by mpsd.calendar_date;
116
117 CURSOR week_bucket IS
118 SELECT to_char(mpsd.week_start_date,'DD-MM-RR'), mpsd.week_start_date
119 FROM msc_trading_partners tp,
120 msc_cal_week_start_dates mpsd,
121 msc_plans mp
122 WHERE mpsd.calendar_code = tp.calendar_code
123 and mpsd.sr_instance_id = tp.sr_instance_id
124 and mpsd.exception_set_id = tp.calendar_exception_set_id
125 and tp.sr_instance_id = nvl(arg_instance_id, tp.sr_instance_id)
126 and tp.sr_tp_id = arg_org_id
127 and tp.partner_type =3
128 and mp.plan_id = arg_plan_id
129 and (mpsd.week_start_date between mp.data_start_date
130 and mp.cutoff_date
131 or mpsd.next_date between mp.data_start_date and
132 mp.cutoff_date)
133 order by mpsd.week_start_date;
134
135 CURSOR period_bucket IS
136 SELECT mpsd.period_name, mpsd.period_start_date
137 FROM msc_trading_partners tp,
138 msc_period_start_dates mpsd,
139 msc_plans mp
140 WHERE mpsd.calendar_code = tp.calendar_code
141 and mpsd.sr_instance_id = tp.sr_instance_id
142 and mpsd.exception_set_id = tp.calendar_exception_set_id
143 and tp.sr_instance_id = nvl(arg_instance_id, tp.sr_instance_id)
144 and tp.sr_tp_id = arg_org_id
145 and tp.partner_type =3
146 and mp.plan_id = arg_plan_id
147 and (mpsd.period_start_date between mp.data_start_date
148 and mp.cutoff_date
149 or mpsd.next_date between mp.data_start_date and
150 mp.cutoff_date)
151 order by mpsd.period_start_date;
152
153 BEGIN
154
155 counter :=1;
156 IF arg_bucket_type = 'DAY' THEN
157 OPEN day_bucket;
158 ELSIF arg_bucket_type = 'WEEK' THEN
159 OPEN week_bucket;
160 ELSIF arg_bucket_type = 'PERIOD' THEN
161 OPEN period_bucket;
162 END IF;
163 LOOP
164 IF arg_bucket_type = 'DAY' THEN
165 FETCH day_bucket into v_period(counter),var_dates(counter);
166 EXIT WHEN day_bucket%NOTFOUND;
167 ELSIF arg_bucket_type = 'WEEK' THEN
168 FETCH week_bucket into v_period(counter),var_dates(counter);
169 EXIT WHEN week_bucket%NOTFOUND;
170 ELSIF arg_bucket_type = 'PERIOD' THEN
171 FETCH period_bucket into v_period(counter),var_dates(counter);
172 EXIT WHEN period_bucket%NOTFOUND;
173 END IF;
174
175 counter := counter +1;
176 END LOOP;
177 IF arg_bucket_type = 'DAY' THEN
178 CLOSE day_bucket;
179 ELSIF arg_bucket_type = 'WEEK' THEN
180 CLOSE week_bucket;
181 ELSIF arg_bucket_type = 'PERIOD' THEN
182 CLOSE period_bucket;
183 END IF;
184 --dbms_output.put_line('counter='||counter);
185 g_num_of_buckets := counter-1;
186 p_period_count := counter-1;
187 END get_bucket_dates;
188
189 -- =============================================================================
190 BEGIN
191
192 get_bucket_dates;
193
194 -- ---------------------------------
195 -- Initialize the bucket cells to 0.
196 -- ---------------------------------
197 FOR counter IN 1..g_num_of_buckets LOOP
198 bucket_cells_tab(counter) := 0;
199 p_period_string := p_period_string ||g_param||v_period(counter);
200 END LOOP;
201
202 -- last_date :=var_dates(g_num_of_buckets);
203
204 bucket_counter := 2;
205
206 OPEN mrp_vertical_plan;
207 LOOP
208 FETCH mrp_vertical_plan INTO activity_rec;
209 IF (mrp_vertical_plan%NOTFOUND) and
210 (mrp_vertical_plan%ROWCOUNT =0) THEN
211 flush_item_plan;
212 END IF;
213
214 IF ((mrp_vertical_plan%NOTFOUND) OR
215 (activity_rec.item_id <> last_item_id) OR
216 ( activity_rec.org_id <> last_org_id)) AND
217 last_item_id <> -1 THEN
218
219 -- --------------------------
220 -- Need to flush the plan for
221 -- the previous item.
222 -- --------------------------
223
224 flush_item_plan;
225
226
227 END IF;
228
229 EXIT WHEN mrp_vertical_plan%NOTFOUND;
230 IF activity_rec.new_date >= var_dates(bucket_counter) THEN
231 WHILE activity_rec.new_date >= var_dates(bucket_counter) AND
232 bucket_counter <= g_num_of_buckets LOOP
233 bucket_counter := bucket_counter + 1;
234 END LOOP;
235 END IF;
236
237 IF activity_rec.new_date < var_dates(bucket_counter) THEN
238 add_to_plan(bucket_counter - 1,
239 activity_rec.new_quantity);
240 END IF;
241
242 last_item_id := activity_rec.item_id;
243 last_org_id := activity_rec.org_id;
244 END LOOP;
245
246 CLOSE mrp_vertical_plan;
247
248 EXCEPTION WHEN NO_DATA_FOUND THEN
249 --dbms_output.put_line('no data found');
250 flush_item_plan;
251 close mrp_vertical_plan;
252 END;
253
254 FUNCTION get_exception_group(l_where varchar2) RETURN column_number IS
255 statement varchar2(4000);
256 TYPE cur IS REF CURSOR;
257 group_cursor cur;
258 exception_group column_number;
259 i number;
260 BEGIN
261 i := 1;
262 OPEN group_cursor FOR l_where;
263 LOOP
264 FETCH group_cursor into exception_group(i);
265 EXIT WHEN group_cursor%NOTFOUND;
266 i := i+1;
267 END LOOP;
268 CLOSE group_cursor;
269
270 return exception_group;
271 END get_exception_group;
272
273 PROCEDURE flush_multi_return ( p_sd_table in msc_vertical_plan_sc.sd_tbl_type) IS
274 BEGIN
275 FORALL j IN 1..p_sd_table.p_query_id.COUNT
276 insert into msc_form_query (query_id, number1,
277 last_update_date, last_updated_by,
278 creation_date, created_by, last_update_login)
279 values (p_sd_table.p_query_id(j),
280 p_sd_table.p_number_1(j),
281 sysdate,-1,
282 sysdate,-1,-1);
283
284 END flush_multi_return;
285
286
287 END Msc_VERTICAL_PLAN_SC;
[Home] [Help]