1 PACKAGE WIP_WPS_COMMON AUTHID CURRENT_USER AS
2 /* $Header: wipzcoms.pls 115.1 2003/10/13 21:58:59 yulin noship $ */
3
4 WPS_APPLICATION_ID CONSTANT NUMBER := 388;
5
6 TYPE Resource_Type IS RECORD
7 ( resource_id NUMBER,
8 department_id NUMBER,
9 x24_hour_flag NUMBER);
10
11 TYPE Resource_Tbl_Type IS TABLE OF Resource_Type
12 INDEX BY BINARY_INTEGER;
13
14 TYPE Number_Tbl_Type IS TABLE OF NUMBER
15 INDEX BY BINARY_INTEGER;
16
17 TYPE Varchar30_Tbl_Type IS TABLE OF VARCHAR2(30)
18 INDEX BY BINARY_INTEGER;
19
20 FUNCTION Get_Install_Status RETURN VARCHAR2;
21
22 PROCEDURE GetParameters(p_org_id IN NUMBER,
23 x_use_finite_scheduler OUT NOCOPY NUMBER,
24 x_material_constrained OUT NOCOPY NUMBER,
25 x_horizon_length OUT NOCOPY NUMBER);
26
27
28 /*
29 * Procedure that populates the resource availability into
30 * MRP_NET_RESOURCE_AVAIL if not already there.
31 */
32
33 PROCEDURE Populate_Resource_Avails (p_simulation_set IN VARCHAR2,
34 p_organization_id IN NUMBER,
35 p_start_date IN DATE,
36 p_cutoff_date IN DATE,
37 p_wip_entity_id IN NUMBER DEFAULT null,
38 p_errnum OUT NOCOPY NUMBER,
39 p_errmesg OUT NOCOPY VARCHAR2,
40 p_reload IN NUMBER DEFAULT 0);
41
42
43 PROCEDURE Populate_Resource_Avails
44 (p_simulation_set IN VARCHAR2,
45 p_organization_id IN NUMBER,
46 p_start_date IN DATE,
47 p_cutoff_date IN DATE,
48 p_resource_table IN Number_Tbl_Type,
49 p_dept_table IN Number_Tbl_Type,
50 p_24hour_flag_table IN Number_Tbl_Type,
51 p_errnum OUT NOCOPY NUMBER,
52 p_errmesg OUT NOCOPY VARCHAR2,
53 p_reload IN NUMBER DEFAULT 0,
54 p_tbl_size IN NUMBER,
55 p_delete_data IN NUMBER);
56
57
58 PROCEDURE Populate_Res_Instance_Avails
59 (p_simulation_set IN VARCHAR2,
60 p_organization_id IN NUMBER,
61 p_start_date IN DATE,
62 p_cutoff_date IN DATE,
63 p_wip_entity_id IN NUMBER DEFAULT null,
64 p_errnum OUT NOCOPY NUMBER,
65 p_errmesg OUT NOCOPY VARCHAR2,
66 p_reload IN NUMBER DEFAULT 0);
67
68 PROCEDURE Populate_Res_Instance_Avails
69 (p_simulation_set IN VARCHAR2,
70 p_organization_id IN NUMBER,
71 p_start_date IN DATE,
72 p_cutoff_date IN DATE,
73 p_resource_table IN Number_Tbl_Type,
74 p_dept_table IN Number_Tbl_Type,
75 p_24hour_flag_table IN Number_Tbl_Type,
76 p_instance_table IN Number_Tbl_Type,
77 p_serial_num_table IN Varchar30_Tbl_Type,
78 p_errnum OUT NOCOPY NUMBER,
79 p_errmesg OUT NOCOPY VARCHAR2,
80 p_reload IN NUMBER DEFAULT 0,
81 p_tbl_size IN NUMBER,
82 p_delete_data IN NUMBER);
83 /*
84 * Procedure that populates the resource availability into
85 * MRP_NET_RESOURCE_AVAIL if not already there.
86 * Same as Populate_Resource_Avails but for one resource.
87 */
88 PROCEDURE Populate_Individual_Res_Avails (p_simulation_set IN VARCHAR2,
89 p_organization_id IN NUMBER,
90 p_resource_id IN NUMBER,
91 p_start_date IN DATE,
92 p_cutoff_date IN DATE,
93 p_errnum OUT NOCOPY NUMBER,
94 p_errmesg OUT NOCOPY VARCHAR2,
95 p_reload IN NUMBER DEFAULT 0,
96 p_department_id IN NUMBER DEFAULT null);
97
98 PROCEDURE Populate_Individual_Ins_Avails (p_simulation_set IN VARCHAR2,
99 p_organization_id IN NUMBER,
100 p_resource_id IN NUMBER,
101 p_instance_id IN NUMBER,
102 p_serial_number IN VARCHAR2,
103 p_start_date IN DATE,
104 p_cutoff_date IN DATE,
105 p_errnum OUT NOCOPY NUMBER,
106 p_errmesg OUT NOCOPY VARCHAR2,
107 p_reload IN NUMBER DEFAULT 0,
108 p_department_id IN NUMBER DEFAULT null);
109 /*
110 * Wrapper on top of MRP_RHX_RESOURCE_AVAILABILITY.calc_res_avail.
111 * Basically delete the MRP_NET_RESOURCE_AVAIL table only for the date
112 * range specified by p_start_date and p_cutoff_date and for the passed
113 * in simulation_set identifier.
114 */
115 PROCEDURE populate_mrp_avail_resources(p_simulation_set IN varchar2,
116 p_organization_id IN number,
117 p_start_date IN date,
118 p_cutoff_date IN date,
119 p_wip_entity_id IN number);
120
121 PROCEDURE populate_mrp_avail_res_inst
122 (p_simulation_set IN varchar2,
123 p_organization_id IN number,
124 p_start_date IN date,
125 p_cutoff_date IN date,
126 p_wip_entity_id IN number);
127
128
129 /*
130 * Wrapper on top of MRP_RHX_RESOURCE_AVAILABILITY.calc_res_avail.
131 * Basically delete the MRP_NET_RESOURCE_AVAIL table only for the date
132 * range specified by p_start_date and p_cutoff_date and for the passed
133 * in simulation_set identifier.
134 * Same as populate_mrp_avail_resources but for one resource.
135 */
136 PROCEDURE populate_single_mrp_avail_res(p_simulation_set IN varchar2,
137 p_organization_id IN number,
138 p_resource_id IN number,
139 p_start_date IN date,
140 p_cutoff_date IN date,
141 p_department_id IN NUMBER DEFAULT null);
142
143
144 /*
145 * Wrapper on top of MRP_RHX_RESOURCE_AVAILABILITY.calc_res_avail.
146 * Basically delete the MRP_NET_RESOURCE_AVAIL table only for the date
147 * range specified by p_start_date and p_cutoff_date and for the passed
148 * in simulation_set identifier.
149 * Same as populate_mrp_avail_resources but for one resource.
150 */
151 PROCEDURE populate_single_mrp_avail_ins(p_simulation_set IN varchar2,
152 p_organization_id IN number,
153 p_resource_id IN number,
154 p_instance_id IN number,
155 p_serial_number IN varchar2,
156 p_start_date IN date,
157 p_cutoff_date IN date,
158 p_department_id IN NUMBER DEFAULT null);
159
160 /*
161 * Function that checks against the MRP_NET_RESOURCE_AVAIL to see
162 * if the resource availability for the organization is already populated.
163 * If not, returns the p_from_date to the latest date in the table so
164 * that the caller can use the p_date_from and p_date_to to call MRP to
165 * populate the missing data.
166 */
167 FUNCTION resource_info_found_in_mrp(p_simulation_set IN VARCHAR2,
168 p_organization_id IN NUMBER,
169 p_date_from IN OUT NOCOPY DATE,
170 p_date_to IN OUT NOCOPY DATE)
171 RETURN BOOLEAN;
172
173
174 /*
175 * Function that checks against the MRP_NET_RESOURCE_AVAIL to see
176 * if the resource availability for the organization is already populated.
177 * If not, returns the p_from_date to the latest date in the table so
178 * that the caller can use the p_date_from and p_date_to to call MRP to
179 * populate the missing data.
180 * Same as resource_info_found_in_mrp but for one resource.
181 */
182 FUNCTION single_res_info_found_in_mrp(p_simulation_set IN VARCHAR2,
183 p_organization_id IN NUMBER,
184 p_resource_id IN NUMBER,
185 p_date_from IN OUT NOCOPY DATE,
186 p_date_to IN OUT NOCOPY DATE,
187 p_department_id IN NUMBER DEFAULT null)
188 RETURN BOOLEAN;
189
190 /*
191 * Function that checks against the MRP_NET_RESOURCE_AVAIL to see
192 * if the resource availability for the organization is already populated.
193 * If not, returns the p_from_date to the latest date in the table so
194 * that the caller can use the p_date_from and p_date_to to call MRP to
195 * populate the missing data.
196 * Same as resource_info_found_in_mrp but for one resource.
197 */
198 FUNCTION single_ins_info_found_in_mrp(p_simulation_set IN VARCHAR2,
199 p_organization_id IN NUMBER,
200 p_resource_id IN NUMBER,
201 p_instance_id IN NUMBER,
202 p_serial_number IN VARCHAR2,
203 p_date_from IN OUT NOCOPY DATE,
204 p_date_to IN OUT NOCOPY DATE,
205 p_department_id IN NUMBER DEFAULT null)
206 RETURN BOOLEAN;
207
208
209 PROCEDURE INCREMENT_BATCH_SEQ(NUMBER_OF_NEW_BATCHES NUMBER);
210
211 /*
212 * this function is to submit a scheduling request simple version
213 * return request_id
214 */
215 function submit_shopfloor_sched_request
216 (
217 p_org_id IN NUMBER,
218 p_scheduling_mode IN NUMBER,
219 p_direction IN NUMBER,
220 p_use_substiture_resource IN NUMBER,
221 p_entity_type IN NUMBER,
222 p_firm_window_date IN VARCHAR2,
223 x_return_status OUT NOCOPY VARCHAR2,
224 x_msg_count OUT NOCOPY NUMBER,
225 x_msg_data OUT NOCOPY VARCHAR2
226 ) return NUMBER;
227
228 /*
229 * this function is the full version of submitting a concurrent request
230 * return request_id
231 */
232 function submit_scheduling_request
233 (
234 p_org_id IN NUMBER,
235 p_scheduling_mode IN NUMBER,
236 p_wip_entity_id IN NUMBER,
237 p_direction IN NUMBER,
238 p_midpt_operation IN VARCHAR2,
239 p_start_date IN DATE,
240 p_end_date IN DATE,
241 p_horizon_start IN DATE,
242 p_horizon_length IN NUMBER,
243 p_resource_constraint IN NUMBER,
244 p_material_constraint IN NUMBER,
245 p_connect_to_comm IN VARCHAR2,
246 p_ip_address IN VARCHAR2,
247 p_port_number IN NUMBER,
248 p_user_id IN NUMBER,
249 p_ident IN NUMBER,
250 p_use_substiture_resource IN NUMBER,
251 p_chosen_operation IN VARCHAR2,
252 p_chosen_subset_group IN VARCHAR2,
253 p_entity_type IN NUMBER,
254 p_midpt_op_res IN VARCHAR2,
255 p_instance_id IN VARCHAR2,
256 p_serial_number IN VARCHAR2,
257 p_firm_window_date IN VARCHAR2,
258 x_return_status OUT NOCOPY VARCHAR2,
259 x_msg_count OUT NOCOPY NUMBER,
260 x_msg_data OUT NOCOPY VARCHAR2
261 ) return NUMBER;
262
263 /*
264 * this function is to submit a request to launch the scheduler
265 * simple version
266 * return request_id
267 */
268 function submit_launch_sched_request
269 (
270 p_connect_to_comm IN VARCHAR2,
271 p_ip_address IN VARCHAR2,
272 p_port_number IN VARCHAR2,
273 p_user_id IN VARCHAR2,
274 p_ident IN VARCHAR2,
275 x_return_status OUT NOCOPY VARCHAR2,
276 x_msg_count OUT NOCOPY NUMBER,
277 x_msg_data OUT NOCOPY VARCHAR2
278 ) return NUMBER;
279
280 /*
281 * this function querys the scheduling request status
282 * return dev_phase, dev_satus for development comparison
283 * return phase, status for translated text
284 */
285 function get_request_status
286 (
287 p_request_id IN NUMBER,
288 p_app_name IN VARCHAR2 default null,
289 p_program IN VARCHAR2 default null,
290 x_request_id OUT NOCOPY NUMBER,
291 x_phase OUT NOCOPY VARCHAR2,
292 x_status OUT NOCOPY VARCHAR2,
293 x_dev_phase OUT NOCOPY VARCHAR2,
294 x_dev_status OUT NOCOPY VARCHAR2,
295 x_message OUT NOCOPY VARCHAR2
296 ) RETURN VARCHAR2;
297
298 /*
299 * convienient api to get some scheduling options from lookup tables
300 */
301 PROCEDURE get_scheduling_param_options
302 (
303 x_forward OUT NOCOPY VARCHAR2,
304 x_backward OUT NOCOPY VARCHAR2,
308
305 x_yes OUT NOCOPY VARCHAR2,
306 x_no OUT NOCOPY VARCHAR2
307 );
309 /*********************************/
310 /* these are APIs to improve performance on
311 * queries on customer and sales order
312 */
313 function job_has_customer(p_wip_entity_id IN NUMBER, p_cust_name IN VARCHAR2) return VARCHAR2;
314
315 function job_has_sales_order(p_wip_entity_id IN NUMBER, p_so_name IN VARCHAR2) return VARCHAR2;
316
317 function get_cust_so_info(p_wip_entity_id IN NUMBER) return VARCHAR2;
318
319 function cancel_request(request_id in NUMBER,
320 message out NOCOPY VARCHAR2) return number;
321
322 procedure update_scheduling_request_id(p_request_id in NUMBER,
323 p_wip_entity_id_table IN Number_Tbl_Type,
324 p_wip_entity_table_size IN NUMBER,
325 p_organization_id IN NUMBER);
326
327 function get_DiscreteJob_Progress(p_wip_entity_id in NUMBER) return number;
328
329 /***********************************************************/
330 /* This is called when in gantt (UI), user manually moves */
331 /* and operation resource bar. Operation/Job start/end */
332 /* time might be updated if the bar moves out of job/op */
333 /* boundaries. */
334 /* */
335 /* This procedure handles both discrete/osfm jobs */
336 /* For discrete */
337 /* p_entity_type: 1 */
338 /* p_source : 0 */
339 /* For osfm: */
340 /* p_entity_type: 5 */
341 /* p_source : 0 (exeuction) 1 (copy) */
342 /***********************************************************/
343 PROCEDURE update_operation_resource
344 (
345 p_entity_type IN NUMBER,
346 p_source IN NUMBER,
347 p_wip_entity_id IN NUMBER,
348 p_operation_seq_num IN NUMBER,
349 p_resource_seq_num IN NUMBER,
350 p_new_start_date IN DATE,
351 p_new_completion_date IN DATE,
352 x_status OUT NOCOPY VARCHAR2,
353 x_msg_count OUT NOCOPY NUMBER,
354 x_msg_data OUT NOCOPY VARCHAR2
355 );
356
357 /********************************************************/
358 /* Find out the on-hand qty for a component */
359 /* The qty can be either qty in all sub-inv or */
360 /* only in netable sub-inv, based on the profile */
361 /* WIP_REQUIREMENT_ATT_OPTION */
362 /* No reseveration is considered here */
363 /********************************************************/
364 FUNCTION get_component_on_hand
365 (
366 p_organization_id IN NUMBER,
367 p_inventory_item_id IN NUMBER
368 ) return NUMBER;
369
370 JOB_TYPE_DISCRETE CONSTANT INTEGER := 1;
371 JOB_TYPE_WSM CONSTANT INTEGER := 5;
372 WSM_DATA_SOURCE_EXE CONSTANT INTEGER := 0;
373 WSM_DATA_SOURCE_COPY CONSTANT INTEGER := 1;
374
375
376 END WIP_WPS_COMMON;
[Home] [Help]