[Home] [Help]
PACKAGE BODY: APPS.MSC_DS_SCHEDULE
Source
4 procedure getScheduleSummary(p_plan_id number, p_cat_set_id number, p_recom_days number,
1 PACKAGE BODY msc_ds_schedule AS
2 /* $Header: MSCDSSB.pls 120.0 2005/05/25 18:26:26 appldev noship $ */
3
5 p_late_supply_total out nocopy number, p_res_cons_total out nocopy number,
6 p_mat_cons_total out nocopy number, p_exc_setup_total out nocopy number,
7 p_resched_total out nocopy number, p_release_total out nocopy number) is
8
9 -- 87 Late Supply
10 -- 36 Resource constraint
11 -- 37 Material constraint
12 -- 90 Excessive Setups
13 -- 6,7 Orders to be Rescheduled In" and "Orders to be Rescheduled Out"
14 cursor c_exc_summary is
15 select exception_type, count(*) exc_count
16 from msc_exception_details_v
17 where plan_id = p_plan_id
18 and nvl(category_set_id, p_cat_set_id) = p_cat_set_id
19 and exception_type in (87, 36, 37, 90, 6, 7)
20 group by exception_type;
21
22 cursor c_release is
23 select count(*) rel_count
24 from msc_orders_v
25 where plan_id = p_plan_id
26 and source_table = 'MSC_SUPPLIES'
27 and order_type = 3
28 and nvl(release_status,2) = 2
29 and nvl(category_set_id, p_cat_set_id) = p_cat_set_id
30 and new_order_date <= (select p.plan_start_date + p_recom_days
31 from msc_plans p
32 where p.plan_id = p_plan_id );
33
34 begin
35 p_resched_total := 0;
36 p_release_total := 0;
37
38 for exc_summary_row in c_exc_summary
39 loop
40 if (exc_summary_row.exception_type = 87) then
41 p_late_supply_total := exc_summary_row.exc_count;
42 elsif (exc_summary_row.exception_type = 36) then
43 p_res_cons_total := exc_summary_row.exc_count;
44 elsif (exc_summary_row.exception_type = 37) then
45 p_mat_cons_total := exc_summary_row.exc_count;
46 elsif (exc_summary_row.exception_type = 90) then
47 p_exc_setup_total := exc_summary_row.exc_count;
48 elsif (exc_summary_row.exception_type in (6,7)) then
49 p_resched_total := p_resched_total + exc_summary_row.exc_count;
50 end if;
51 end loop;
52
53 open c_release;
54 fetch c_release into p_release_total;
55 close c_release;
56
57 end getScheduleSummary;
58
59 procedure getLateSupplySummary(p_plan_id number, p_cat_set_id number,
60 p_round_val number, p_total_supply out nocopy number, p_late_supply out nocopy number,
61 p_avg_days_late out nocopy number, p_past_due_supply out nocopy number) is
62
63 cursor c_late_supply is
64 select round(sum(total_supply_count),p_round_val),
65 round(sum(late_supply_count),p_round_val),
66 round(avg(total_days_late),p_round_val)
67 from msc_bis_plan_summary_kpi
68 where plan_id = p_plan_id;
69
70 cursor c_pas_due is
71 select count(*)
72 from msc_exception_details_v
73 where plan_id = p_plan_id
74 and exception_type = 10
75 and nvl(category_set_id, p_cat_set_id) = p_cat_set_id;
76
77 begin
78 open c_late_supply;
79 fetch c_late_supply into p_total_supply, p_late_supply, p_avg_days_late;
80 close c_late_supply;
81
82 open c_pas_due;
83 fetch c_pas_due into p_past_due_supply;
84 close c_pas_due;
85
86 end getLateSupplySummary;
87
88 procedure getLateSupplyDetails(p_plan_id number, p_cat_set_id number,
89 p_round_val number, p_name_data in out nocopy msc_ds_schedule.maxCharTbl) is
90
91 cursor c_late_supplies is
92 select to_date(key_date,'J') detail_date,
93 round(nvl(total_supply_count,0), p_round_val) total_supply_count,
94 round(nvl(late_supply_count,0), p_round_val) late_supply_count,
95 round(nvl(total_days_late,0), p_round_val) total_days_late
96 from msc_bis_plan_summary_kpi
97 where plan_id = p_plan_id
98 order by to_date(key_date,'J');
99
100 i number := 1;
101 j number := 0;
102 k number := 1;
103
104 l_one_record varchar2(300);
105 oneBigRecord maxCharTbl := maxCharTbl(0);
106 l_max_len number;
107 rowCount number;
108
109 begin
110 rowCount := 0;
111 oneBigRecord.delete;
112 oneBigRecord.extend;
113 j := 1;
114 for c_late_supplies_row in c_late_supplies
115 loop
116 rowCount := rowCount + 1;
117
118 l_one_record := to_char(c_late_supplies_row.detail_date, FORMAT_MASK)
119 || FIELD_SEPERATOR ||to_char(c_late_supplies_row.total_supply_count)
120 || FIELD_SEPERATOR ||to_char(c_late_supplies_row.late_supply_count)
121 || FIELD_SEPERATOR ||to_char(c_late_supplies_row.total_days_late);
125 j := j+1;
122
123 l_max_len := nvl(length(oneBigRecord(j)),0) + nvl(length(l_one_record),0);
124 if l_max_len > 30000 then
126 oneBigRecord.extend;
127 end if;
128 if ( oneBigRecord(j) is null ) then
129 oneBigRecord(j) := l_one_record;
130 else
131 oneBigRecord(j) := oneBigRecord(j) || record_seperator ||l_one_record;
132 end if;
133 end loop;
134
135 p_name_data.delete;
136 p_name_data.extend;
137 j := 1;
138 p_name_data(k) := rowCount || record_seperator||oneBigRecord(1);
139
140 for j in 2.. oneBigRecord.count loop
141 p_name_data.extend;
142 k := k+1;
143 p_name_data(k) := oneBigRecord(j);
144 end loop;
145
146 end getLateSupplyDetails;
147
148 procedure getResUtilDetails(p_plan_id number, p_resource_basis number,
149 p_round_val number, p_name_data in out nocopy msc_ds_schedule.maxCharTbl) is
150
151 cursor c_util_details is
152 select mbrs.resource_date detail_date,
153 round(avg(nvl(mbrs.utilization,0)),p_round_val) run_util,
154 round(avg(nvl(mbrs.utilization,0)),p_round_val) setup_util -- pabram..need to change later
155 from msc_bis_res_summary mbrs,
156 msc_department_resources mdr
157 where mbrs.plan_id = mdr.plan_id
158 and mbrs.sr_instance_id = mdr.sr_instance_id
159 and mbrs.organization_id = mdr.organization_id
160 and mbrs.department_id = mdr.department_id
161 and mbrs.resource_id = mdr.resource_id
162 and mbrs.plan_id = p_plan_id
163 group by mbrs.resource_date
164 order by resource_date;
165 i number := 1;
166 j number := 0;
167 k number := 1;
168
169 l_one_record varchar2(300);
170 oneBigRecord maxCharTbl := maxCharTbl(0);
171 l_max_len number;
172 rowCount number;
173
174 begin
175 rowCount := 0;
176 oneBigRecord.delete;
177 oneBigRecord.extend;
178 j := 1;
179 for c_util_details_row in c_util_details
180 loop
181 rowCount := rowCount + 1;
182
183 l_one_record := to_char(c_util_details_row.detail_date, FORMAT_MASK)
184 || FIELD_SEPERATOR ||to_char(c_util_details_row.run_util)
185 || FIELD_SEPERATOR ||to_char(c_util_details_row.setup_util);
186
187 l_max_len := nvl(length(oneBigRecord(j)),0) + nvl(length(l_one_record),0);
188 if l_max_len > 30000 then
189 j := j+1;
190 oneBigRecord.extend;
191 end if;
192 if ( oneBigRecord(j) is null ) then
193 oneBigRecord(j) := l_one_record;
194 else
195 oneBigRecord(j) := oneBigRecord(j) || record_seperator ||l_one_record;
196 end if;
197 end loop;
198
199 p_name_data.delete;
200 p_name_data.extend;
201 j := 1;
202 p_name_data(k) := rowCount || record_seperator||oneBigRecord(1);
203
204 for j in 2.. oneBigRecord.count loop
205 p_name_data.extend;
206 k := k+1;
207 p_name_data(k) := oneBigRecord(j);
208 end loop;
209 end getResUtilDetails;
210
211 procedure getResUtilSummary(p_plan_id number, p_resource_basis number,
212 p_round_val number, p_actual_util out nocopy number, p_setup_util out nocopy number) is
213
214 cursor c_util_summary is
215 select round(avg(nvl(mbrs.utilization,0)),p_round_val) actual_util,
216 round(avg(nvl(mbrs.utilization,0)),p_round_val) setup_util -- pabram..need to change later
217 from msc_bis_res_summary mbrs,
218 msc_department_resources mdr
219 where mbrs.plan_id = mdr.plan_id
220 and mbrs.sr_instance_id = mdr.sr_instance_id
221 and mbrs.organization_id = mdr.organization_id
222 and mbrs.department_id = mdr.department_id
223 and mbrs.resource_id = mdr.resource_id
224 and mbrs.plan_id = p_plan_id
225 and ( ( p_resource_basis = 1 )
226 or ( p_resource_basis = 2 and bottleneck_flag = 1));
227 begin
228 open c_util_summary;
229 fetch c_util_summary into p_actual_util, p_setup_util;
230 close c_util_summary;
231 end getResUtilSummary;
232
233 end msc_ds_schedule;