1 PACKAGE MRP_LINE_SCHEDULE_ALGORITHM AUTHID CURRENT_USER AS
2 /* $Header: MRPLSCHS.pls 115.48 2003/09/12 22:19:45 hwenas ship $ */
3
4 C_EXT_SALES_ORDER CONSTANT NUMBER := 2;
5 C_INT_SALES_ORDER CONSTANT NUMBER := 8;
6 C_PLANNED_ORDER CONSTANT NUMBER := 100;
7 C_YES CONSTANT NUMBER := 1;
8 C_NO CONSTANT NUMBER := 3;
9 C_VAR CONSTANT NUMBER := 1;
10 C_SO_DEMAND CONSTANT NUMBER := 5;
11 C_PO_DEMAND CONSTANT NUMBER := 3;
12 C_CRITERIA_ID CONSTANT NUMBER := 1;
13 C_NO_LEVEL_LOAD CONSTANT NUMBER := 3;
14 C_LEVEL_LOAD CONSTANT NUMBER := 2;
15 C_MIXED_MODEL CONSTANT NUMBER := 1;
16 C_ROUND_TYPE CONSTANT NUMBER := 1;
17 C_USER_DEFINE_NO CONSTANT NUMBER := 2;
18 C_ASC CONSTANT NUMBER := 1;
19 C_DESC CONSTANT NUMBER := 2;
20 C_ITEM CONSTANT NUMBER := 2;
21 C_NORM CONSTANT NUMBER := 1;
22 C_COMPLETE CONSTANT NUMBER := 2;
23 C_DATE_ON CONSTANT NUMBER := 1;
24
25 -- This table holds the NET capacity for each date (indexed by dates) as well
26 -- as the completion time of the last scheduled flow schedule.
27
28 TYPE CapRecTyp IS RECORD (
29 capacity NUMBER);
30
31 TYPE CapTabTyp IS TABLE OF CapRecTyp
32 INDEX BY BINARY_INTEGER;
33
34 -- This table stores the latest time in the day where a flow schedule is
35 -- scheduled, indexed by the date
36
37 TYPE TimeRecTyp IS RECORD (
38 start_completion_time NUMBER,
39 end_completion_time NUMBER);
40
41 TYPE TimeTabTyp IS TABLE OF TimeRecTyp
42 INDEX BY BINARY_INTEGER;
43
44 -- This table stores the highest sequence for each schedule group
45 -- indexed by the schedule group id
46
47 TYPE BuildSeqTyp IS RECORD (
48 buildseq NUMBER);
49
50 TYPE BuildSeqTabTyp IS TABLE OF BuildSeqTyp
51 INDEX BY BINARY_INTEGER;
52
53 -- This table store the order modifier restrictions for each item
54 -- indexed by the item id
55
56 TYPE OrderModTyp IS RECORD (
57 minVal NUMBER,
58 maxVal NUMBER);
59
60 TYPE OrderModTabTyp IS TABLE OF OrderModTyp
61 INDEX BY BINARY_INTEGER;
62
63 -- This table stores for each item, the quantity remaining from the
64 -- newest flow schedule with order modifiers that can be used to
65 -- schedule the original schedules. The table is indexed by the item id.
66 -- The table also stores a column which is a flag indicating
67 -- if at least one flow schedule has been created for this item.
68
69 TYPE ItemQtyTyp IS RECORD (
70 remainQty NUMBER,
71 wip_id NUMBER);
72
73 TYPE ItemQtyTabTyp IS TABLE OF ItemQtyTyp
74 INDEX BY BINARY_INTEGER;
75
76 -- This table stores the total demand and demand ratio for each item.
77 -- The table also stores the round type of the item. 1 - round, 2 - no round.
78
79 TYPE DemandRecTyp IS RECORD (
80 totalDemand NUMBER,
81 roundType NUMBER,
82 sequence NUMBER);
83
84 TYPE DemandTabTyp IS TABLE OF DemandRecTyp
85 INDEX BY BINARY_INTEGER;
86
87 TYPE fs_select_type IS RECORD(
88 wip_entity number,
89 creation_date date,
90 schedule_date date,
91 promise_date date,
92 request_date date,
93 planning_priority number,
94 primary_item_id number,
95 planned_quantity number,
96 schedule_group_id number);
97
98 -- This table stores the relevant columns of the wip flow schedules table.
99 -- This table is used in the mix model algorithm for performance improvement.
100
101 TYPE FlowScheduleTyp IS RECORD (
102 scheduled_flag NUMBER,
103 organization_id NUMBER,
104 last_update_date DATE,
105 last_updated_by NUMBER,
106 creation_date DATE,
107 created_by NUMBER,
108 class_code VARCHAR2(10),
109 line_id NUMBER,
110 primary_item_id NUMBER,
111 scheduled_start_date DATE,
112 planned_quantity NUMBER,
113 quantity_completed NUMBER,
114 scheduled_completion_date DATE,
115 schedule_group_id NUMBER,
116 build_sequence NUMBER,
117 status NUMBER,
118 schedule_number VARCHAR2(30),
119 demand_source_header_id NUMBER,
120 demand_source_line VARCHAR2(30),
121 demand_source_delivery VARCHAR2(30),
122 demand_source_type NUMBER,
123 project_id NUMBER,
124 task_id NUMBER,
125 end_item_unit_number VARCHAR2(30),
126 request_id NUMBER,
127 attribute1 VARCHAR2(150),
128 attribute2 VARCHAR2(150),
129 attribute3 VARCHAR2(150),
130 attribute4 VARCHAR2(150),
131 attribute5 VARCHAR2(150),
132 attribute6 VARCHAR2(150),
133 attribute7 VARCHAR2(150),
134 attribute8 VARCHAR2(150),
135 attribute9 VARCHAR2(150),
136 attribute10 VARCHAR2(150),
137 attribute11 VARCHAR2(150),
138 attribute12 VARCHAR2(150),
139 attribute13 VARCHAR2(150),
140 attribute14 VARCHAR2(150),
141 attribute15 VARCHAR2(150),
142 material_account NUMBER,
143 material_overhead_account NUMBER,
144 resource_account NUMBER,
145 outside_processing_account NUMBER,
146 material_variance_account NUMBER,
147 resource_variance_account NUMBER,
148 outside_proc_var_account NUMBER,
149 std_cost_adjustment_account NUMBER,
150 overhead_account NUMBER,
151 overhead_variance_account NUMBER,
152 bom_revision VARCHAR2(3), /* 2185087 */
153 routing_revision VARCHAR2(3),
154 bom_revision_date DATE,
155 routing_revision_date DATE,
156 alternate_bom_designator VARCHAR2(10),
157 alternate_routing_designator VARCHAR2(10),
158 completion_subinventory VARCHAR2(30),
159 completion_locator_id NUMBER ,
160 demand_class VARCHAR2(30),
161 attribute_category VARCHAR2(30),
162 kanban_card_id NUMBER
163 );
164
165 TYPE FlowScheduleTabTyp IS TABLE OF FlowScheduleTyp
166 INDEX BY BINARY_INTEGER;
167
168
169 -- This table stores the remaining quantity to be allocated for each item
170 -- on the date. The table is indexed by item id
171
172 TYPE ItemAllocTyp IS RECORD (
173 date NUMBER,
174 remainQty NUMBER,
175 complete_flag NUMBER);
176
177 TYPE ItemAllocTabTyp IS TABLE OF ItemAllocTyp
178 INDEX BY BINARY_INTEGER;
179
180 -- This table is used by the mixed model algorithm. It stores the item
181 -- and the total demand for the item, but sorted in a descending order
182 -- with the item of the highest demand to item of the lowest demand.
183
184 TYPE ItemDemandTyp IS RECORD (
185 item NUMBER,
186 qty NUMBER,
187 fixed_lead_time NUMBER,
188 var_lead_time NUMBER);
189
190 TYPE ItemDemandTabTyp IS TABLE OF ItemDemandTyp
191 INDEX BY BINARY_INTEGER;
192
193 TYPE pat_rec IS RECORD (
194 curr_pattern LONG,
195 pos NUMBER);
196
197 TYPE pat_tab_type IS TABLE OF pat_rec
198 INDEX BY BINARY_INTEGER;
199
200 -- variable holds the mode of operation
201 V_MODE NUMBER := C_PLANNED_ORDER;
202
203 -- variable holds the global demand in this scheduling run
204 V_GLOBAL_DEMAND NUMBER := 1;
205
206 -- variable holds the query id used in mrp_form_query table
207 V_QUERY_ID NUMBER := 1;
208
209 -- variable holds the package name used for error messages
210 V_PKG_NAME VARCHAR2(30) := 'MRP_LINE_SCHEDULE_ALGORITHM';
211
212 -- variable holds the error line number used for error messages
213 V_ERROR_LINE NUMBER := 1;
214
215 -- variable holds the procedure name used for error messates
216 V_PROCEDURE_NAME VARCHAR2(30) := 'PROCEDURE NAME';
217
218 FUNCTION Create_Cursor( p_rule_id IN NUMBER,
219 p_org_id IN NUMBER,
220 p_line_id IN NUMBER,
221 p_order IN NUMBER,
222 p_type IN NUMBER,
223 p_item_id IN NUMBER) RETURN INTEGER;
224
225 PROCEDURE calculate_linecap(
226 p_line_id IN NUMBER,
227 p_org_id IN NUMBER,
228 p_flex_tolerance IN NUMBER,
229 p_cap_tab IN OUT NOCOPY CapTabTyp,
230 p_time_tab IN TimeTabTyp,
231 p_schedule_start_date IN NUMBER,
232 p_schedule_start_time IN NUMBER,
233 p_schedule_end_date IN NUMBER,
234 p_schedule_end_time IN NUMBER);
235
236 FUNCTION order_scheduling_rule(
237 p_rule_id IN NUMBER,
238 p_order IN NUMBER) RETURN VARCHAR2;
239
240 PROCEDURE calculate_order_quantities(
241 p_org_id IN NUMBER,
242 p_order_mod_tab IN OUT NOCOPY OrderModTabTyp);
243
244 PROCEDURE calculate_build_sequences(
245 p_org_id IN NUMBER,
246 p_line_id IN NUMBER,
247 p_build_seq_tab IN OUT NOCOPY BuildSeqTabTyp);
248
249 PROCEDURE time_existing_fs(
250 p_org_id IN NUMBER,
251 p_line_id IN NUMBER,
252 p_schedule_start_date IN NUMBER,
253 p_schedule_start_time IN NUMBER,
254 p_schedule_end_date IN NUMBER,
255 p_schedule_end_time IN NUMBER,
256 p_time_tab IN OUT NOCOPY TimeTabTyp);
257
258 PROCEDURE create_po_fs(
259 p_org_id IN NUMBER,
260 p_line_id IN NUMBER,
261 p_rule_id IN NUMBER,
262 p_orderMod_tab IN OUT NOCOPY OrderModTabTyp);
263
264 PROCEDURE rounding_process(
265 p_org_id IN NUMBER,
266 p_line_id IN NUMBER,
267 p_rule_id IN NUMBER,
268 p_demand_tab IN OUT NOCOPY DemandTabTyp);
269
270 PROCEDURE schedule_orders (
271 p_line_id IN NUMBER,
272 p_org_id IN NUMBER,
273 p_rule_id IN NUMBER,
274 p_cap_tab IN CapTabTyp,
275 p_demand_tab IN DemandTabTyp,
276 p_time_tab IN OUT NOCOPY TimeTabTyp);
277
278 PROCEDURE calculate_production_plan(
279 p_org_id IN NUMBER,
280 p_line_id IN NUMBER,
281 p_first_date IN DATE,
282 p_last_date IN DATE,
283 p_cap_tab IN CapTabTyp,
284 p_demand_tab IN OUT NOCOPY DemandTabTyp);
285
286 PROCEDURE schedule_orders_level (
287 p_line_id IN NUMBER,
288 p_org_id IN NUMBER,
289 p_rule_id IN NUMBER,
290 p_cap_tab IN CapTabTyp,
291 p_time_tab IN OUT NOCOPY TimeTabTyp);
292
293 PROCEDURE update_buildseq (
294 p_line_id IN NUMBER,
295 p_org_id IN NUMBER);
296
297 PROCEDURE schedule_mix_model (
298 p_line_id IN NUMBER,
299 p_org_id IN NUMBER,
300 p_rule_id IN NUMBER,
301 p_cap_tab IN CapTabTyp,
302 p_demand_tab IN OUT NOCOPY DemandTabTyp,
303 p_item_demand_tab IN ItemDemandTabTyp,
304 p_time_tab IN OUT NOCOPY TimeTabTyp);
305
306 PROCEDURE Schedule(
307 p_rule_id IN NUMBER,
308 p_line_id IN NUMBER,
309 p_org_id IN NUMBER,
310 p_scheduling_start_date IN DATE,
311 p_scheduling_end_date IN DATE,
312 p_flex_tolerance IN NUMBER,
313 x_return_status OUT NOCOPY VARCHAR2,
314 x_msg_count OUT NOCOPY NUMBER,
315 x_msg_data OUT NOCOPY VARCHAR2);
316
317 FUNCTION mix_model(
318 p_item_demand_tab IN ItemDemandTabTyp)
319 RETURN LONG;
320
321 PROCEDURE calculate_demand (
322 p_line_id IN NUMBER,
323 p_org_id IN NUMBER,
324 p_demand_tab IN OUT NOCOPY DemandTabTyp);
325
326
327 PROCEDURE calculate_demand_mix (
328 p_line_id IN NUMBER,
329 p_org_id IN NUMBER,
330 p_item_demand_tab IN OUT NOCOPY ItemDemandTabTyp);
331
332 FUNCTION calculate_begin_time (
333 p_org_id IN NUMBER,
334 p_completion_date IN DATE,
335 p_lead_time IN NUMBER,
336 p_start_time IN NUMBER,
337 p_end_time IN NUMBER) RETURN DATE;
338
339 FUNCTION calculate_completion_time(
340 p_org_id IN NUMBER,
341 p_item_id IN NUMBER,
342 p_qty IN NUMBER,
343 p_line_id IN NUMBER,
344 p_start_date IN DATE) RETURN DATE;
345
346 END MRP_LINE_SCHEDULE_ALGORITHM;
[Home] [Help]