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