[Home] [Help]
PACKAGE: APPS.MSC_GANTT_UTILS
Source
1 PACKAGE MSC_GANTT_UTILS AS
2 /* $Header: MSCGNTUS.pls 120.8 2006/05/03 16:17:24 pabram noship $ */
3
4 -- CONSTANTS +
5 --separators
6 FIELD_SEPERATOR CONSTANT VARCHAR2(5) := '|';
7 RECORD_SEPERATOR CONSTANT VARCHAR2(5) := '&';
8 RESOURCE_SEPERATOR CONSTANT VARCHAR2(5) := '~';
9 FORMAT_MASK CONSTANT VARCHAR2(20) :='MM/DD/YYYY HH24:MI';
10 COMMA_SEPARATOR constant varchar2(20) := ',';
11 COLON_SEPARATOR constant varchar2(20) := ':';
12
13 FIELD_SEPERATOR_ESC CONSTANT VARCHAR2(10) := '%pipe;';
14 RECORD_SEPERATOR_ESC CONSTANT VARCHAR2(10) := '%amp;';
15
16 --view types
17 ORDER_VIEW CONSTANT INTEGER := 0;
18 RES_ACTIVITIES_VIEW CONSTANT INTEGER := 1;
19 RES_HOURS_VIEW CONSTANT INTEGER := 2;
20 RES_UNITS_VIEW CONSTANT INTEGER := 3;
21 SUPPLIER_VIEW CONSTANT INTEGER := 4;
22 DEMAND_VIEW CONSTANT INTEGER := 5;
23
24 --node types
25 END_DEMAND_NODE CONSTANT INTEGER := 0;
26 JOB_NODE CONSTANT INTEGER := 1;
27 OP_NODE CONSTANT INTEGER := 2;
28 RES_NODE CONSTANT INTEGER := 3;
29 -- END_JOB_NODE CONSTANT INTEGER :=4;
30 COPROD_NODE CONSTANT INTEGER := 5;
31 RES_INST_NODE CONSTANT INTEGER := 6;
32
33 --orders view types
34 PEG_ORDERS constant integer := 0;
35 PEG_DOWN constant integer := 1;
36 PEG_UP constant integer := -1;
37 PEG_UP_AND_DOWN constant integer := 2;
38 SHOW_CHILDREN constant integer := 3;
39 SHOW_PARENT constant integer := 4;
40 SHOW_PARENT_CHILDREN constant integer := 5;
41
42 --res row types
43 RES_SETUP_ROW_TYPE constant INTEGER := 0;
44 RES_REQ_ROW_TYPE constant INTEGER := 1;
45 RES_AVAIL_ROW_TYPE constant INTEGER := 2;
46 RES_CHARGES_ROW_TYPE constant INTEGER := 3;
47 RES_ACT_BATCHED_ROW_TYPE constant INTEGER := 4;
48 RES_SETUP_FIXED_ROW_TYPE constant INTEGER := 5;
49 SEGMENT_PEG_ROW_TYPE constant INTEGER := 6;
50 RES_REQ_DISPLAY_ROW_TYPE constant INTEGER := 7; --only for the backend....
51 RES_REQ_SDS_ROW_TYPE constant INTEGER := 9;
52
53 --res row types
54 SUPP_ALL_ROW_TYPE constant INTEGER := 0;
55 SUPP_AVAIL_ROW_TYPE constant INTEGER := 1;
56 SUPP_OVERLOAD_ROW_TYPE constant INTEGER := 2;
57 SUPP_CONSUME_ROW_TYPE constant INTEGER := 3;
58 -- SUPP_NET_CUM_AVAIL_ROW_TYPE constant INTEGER := 5;
59
60 --parent id
61 SUMMARY_DATA constant INTEGER := 1;
62 DETAIL_DATA constant INTEGER := 2;
63
64 --diff peg vs same peg orders view
65 SAME_PEG constant INTEGER := 1;
66 ALL_PEG constant INTEGER := 2;
67
68 --critical vs non critical orders view
69 NON_CRITICAL_PATH constant INTEGER := -1;
70 CRITICAL_PATH constant INTEGER := 1;
71
72 -- res schedule_flag
73 SCHEDULE_FLAG_YES constant integer := 1;
74 SCHEDULE_FLAG_NO constant integer := 2;
75 SCHEDULE_FLAG_PRIOR constant integer := 3;
76 SCHEDULE_FLAG_NEXT constant integer := 4;
77
78 -- res firm types
79 NO_FIRM CONSTANT INTEGER :=0;
80 FIRM_START CONSTANT INTEGER :=1;
81 FIRM_END CONSTANT INTEGER :=2;
82 FIRM_RESOURCE CONSTANT INTEGER :=3;
83 FIRM_START_END CONSTANT INTEGER :=4;
84 FIRM_START_RES CONSTANT INTEGER :=5;
85 FIRM_END_RES CONSTANT INTEGER :=6;
86 FIRM_ALL CONSTANT INTEGER :=7;
87
88 --supply item types
89 ON_HAND CONSTANT INTEGER :=1;
90 BUY_SUPPLY CONSTANT INTEGER :=2;
91 MAKE_SUPPLY CONSTANT INTEGER :=3;
92 TRANSFER_SUPPLY CONSTANT INTEGER :=4;
93
94 --updateSupplies types
95 TOUCH_SUPPLY CONSTANT INTEGER := 1;
96 FIRM_SUPPLY CONSTANT INTEGER := 2;
97 FIRM_ALL_SUPPLY CONSTANT INTEGER := 3;
98
99 --updateMRR types
100 TOUCH_MRR CONSTANT INTEGER := 1;
101 FIRM_MRR CONSTANT INTEGER := 2;
102 FIRM_ALL_MRR CONSTANT INTEGER := 3;
103 MOVE_MRR constant number := 4;
104
105 --filter types
106 FILTER_TYPE_LIST constant integer := 1;
107 FILTER_TYPE_MFQ constant integer := 2;
108 FILTER_TYPE_FOLDER_ID constant integer := 3;
109 FILTER_TYPE_WHERE_STMT constant integer := 4;
110 FILTER_TYPE_AMONG constant integer := 5;
111 FILTER_TYPE_QUERY_ID constant integer := 6;
112
113 --display types for res activities /hours view
114 DISPLAY_NONE constant integer := 1;
115 DISPLAY_LATE constant integer := 2;
116 DISPLAY_EARLY constant integer := 3;
117 DISPLAY_FIRM constant integer := 4;
118 DISPLAY_OVERLOAD constant integer := 5;
119
120 --misc
121 MBP_NULL_VALUE constant integer := -23453;
122 MBP_NULL_VALUE_CHAR constant varchar2(30) := '-23453';
123
124 NULL_SPACE constant varchar2(1):= ' ';
125
126 ROUND_FACTOR INTEGER := 6;
127 SYS_YES constant INTEGER := 1;
128 SYS_NO constant INTEGER := 2;
129
130 ASCEND_ORDER constant INTEGER := 1;
131 DESCEND_ORDER constant INTEGER := 2;
132
133 -- BOM_ITEM_TYPE
134 BOM_ITEM_MODEL constant integer := 1; -- Model, base_item_id is null
135 BOM_ITEM_OPTION constant integer := 2; -- Option class, base_item_id is null
136 BOM_ITEM_PLANNING constant integer := 3; -- Planning, base_item_id is null
137 BOM_ITEM_STANDARD constant integer := 4; -- Standard, base_item_id is not null
138 BOM_ITEM_PF constant integer := 5; -- Product family, base_item_id is null
139
140 -- CONSTANTS -
141
142 -- Data Structures +
143 TYPE char20Tbl IS TABLE OF varchar2(20) index by binary_integer;
144 TYPE char80Tbl IS TABLE OF varchar2(80) index by binary_integer;
145 TYPE numberTbl IS TABLE OF number index by binary_integer;
146 TYPE longCharTbl IS TABLE of varchar2(200) index by binary_integer;
147 TYPE maxCharTbl IS TABLE of varchar2(32000);
148 TYPE date_arr IS TABLE OF date;
149 TYPE number_arr IS TABLE OF number;
150 TYPE char_arr IS TABLE OF varchar2(300);
151
152 TYPE child_rec_type is RECORD (
153 record_count numberTbl,
154 start_date char20Tbl,
155 end_date char20Tbl,
156 name char80Tbl,
157 transaction_id numberTbl,
158 status numberTbl,
159 applied numberTbl,
160 supply_type char80Tbl,
161 instance_id numberTbl,
162 res_firm_flag numberTbl,
163 sup_firm_flag numberTbl,
164 late_flag numberTbl
165 );
166 -- Data Structures -
167
168 -- get functions +
169 function isCriticalSupply(p_plan_id number,
170 p_end_demand_id number, p_transaction_id number,
171 p_inst_id number) Return number;
172
173 function getDmdPriority(p_plan_id number,
174 p_instance_id number, p_transaction_id number) return number;
175
176 function isResChargeable(p_plan_id number,
177 p_instance_id number, p_org_id number,
178 p_dept_id number, p_res_id number,
179 p_res_instance_id number) return number ;
180
181 function isCriticalRes(p_plan_id number,
182 p_end_demand_id number, p_transaction_id number,
183 p_inst_id number, p_operation_seq_id number,
184 p_routing_seq_id number) return number;
185
186 function isSupplyLate(p_plan_id number,
187 p_instance_id number, p_organization_id number,
188 p_inventory_item_id number, p_transaction_id number) return number;
189
190 function getActualStartDate(p_order_type number, p_make_buy_code number,
191 p_org_id number,p_source_org_id number,
192 p_dock_date date, p_wip_start_date date,
193 p_ship_date date, p_schedule_date date,
194 p_source_supp_id number) return varchar2 ;
195
196 function isResConstraint(p_plan_id number,
197 p_instance_id number, p_organization_id number, p_inventory_item_id number,
198 p_department_id number, p_resource_id number,
199 p_transaction_id number) return number;
200
201 function isResOverload(p_plan_id number,
202 p_instance_id number, p_organization_id number, p_inventory_item_id number,
203 p_department_id number, p_resource_id number,
204 p_transaction_id number) return number;
205
206 function isExcpExists(p_plan_id number, p_instance_id number, p_organization_id number,
207 p_department_id number, p_resource_id number, p_exception_type number) return number;
208
209 function getSupplyType(p_order_type number, p_make_buy_code number,
210 p_org_id number,p_source_org_id number) return number;
211
212 function getPlanInfo(p_plan_id number,
213 p_first_date out nocopy date, p_last_date out nocopy date,
214 p_hour_bkt_start_date out nocopy date, p_day_bkt_start_date out nocopy date,
215 p_plan_start_date out nocopy date, p_plan_end_date out nocopy date,
216 p_plan_type out nocopy number) return varchar2;
217
218 function getDisplayType(p_display_type number, p_end_date date,
219 p_ulpsd date, p_res_firm_flag number, p_overload_flag number,
220 p_days_early number, p_days_late number) return number;
221
222 function getResReqType(p_plan_id number,
223 p_schedule_flag number, p_parent_seq_num number, p_setup_id number) return number;
224
225 function getSetupCode(p_plan_id number, p_inst_id number,
226 p_resource_id number, p_setup_id number) return varchar2 ;
227
228 function getResReqStartDate(p_firm_flag number,
229 p_start_date date, p_end_date date,
230 p_firm_start_date date, p_firm_end_date date, p_status number, p_applied number) return date;
231
232 function getResReqEndDate(p_firm_flag number,
233 p_start_date date, p_end_date date,
234 p_firm_start_date date, p_firm_end_date date, p_status number, p_applied number) return date;
235
236 function getDependencyType(p_plan_id number, p_trans_id number, p_inst_id number,
237 p_op_seq_id number, p_op_seq_num number, p_res_seq_num number,
238 c_trans_id number, c_inst_id number,
239 c_op_seq_id number, c_op_seq_num number, c_res_seq_num number) return number;
240
241 procedure getBatchValues(p_plan_id number, p_instance_id number, p_batch_number number,
242 p_res_desc out nocopy varchar2, p_min_capacity out nocopy number, p_max_capacity out nocopy number,
243 p_capacity_used out nocopy number);
244
245 function getTansitionValue(p_plan_id number, p_instance_id number,
246 p_org_id number, p_dept_id number, p_res_id number, p_from_setup_id number,
247 p_to_setup_id number, p_value_code varchar2) return varchar2;
248
249 function getDeptResInstCode(p_plan_id number,
250 p_instance_id number, p_org_id number,
251 p_dept_id number, p_res_id number,
252 p_res_instance_id number, p_serial_number varchar2) RETURN varchar2 ;
253
254 function getOrderNumber(p_plan_id number, p_inst_id number, p_trx_id number, p_disposition_id number,
255 p_order_type number, p_order_number varchar2) return varchar2;
256
257 function getMFQSequence (p_query_id in number default null) Return number;
258
259 function getGanttSequence (p_query_id in number default null) Return number;
260
261 function usingBatchableRes(p_plan_id number,
262 p_transaction_id number, p_instance_id number) return boolean;
263
264 function isBatchable(p_plan_id number, p_inst_id number, p_org_id number,
265 p_dept_id number, p_res_id number ) return number;
266
267
268 function isTimeFenceCrossed(p_plan_id number,
269 p_transaction_id number, p_instance_id number,
270 p_start_date date) RETURN varchar2;
271
272 function getMTQTime(p_transaction_id number,
273 p_plan_id number, p_instance_id number) return number;
274 -- get functions -
275
276 -- prepare Data +
277 procedure prepareResHoursGantt(p_query_id in number, p_plan_id number,
278 p_start_date date, p_end_date date, p_display_type number default null);
279
280 procedure prepareSupplierGantt(p_query_id in number,
281 p_plan_id number, p_start_date date, p_end_date date);
282
283 -- prepare Data -
284
285 -- populate msc_form_query proc's +
286 procedure populateEndPegsMFQ(p_plan_id in number,
287 p_end_demand_id in number, p_query_id in number);
288
289 procedure populateOrdersIntoMFQ(p_inst_id in number,
290 p_trx_id in number, p_query_id in number,
294 -- populate msc_gantt_query / msc_gantt_other_query proc's +
291 p_one_record varchar2 default null);
292 -- populate msc_form_query proc's -
293
295 procedure populateOrdersIntoGantt(p_plan_id number,
296 p_query_id number, p_mfq_query_id number);
297
298 procedure populateRowKeysIntoGantt(p_query_id number,
299 p_index number, p_node_path varchar2, p_node_type number,
300 p_transaction_id number, p_inst_id number, p_org_id number,
301 p_dept_id number default null, p_res_id number default null,
302 p_op_seq_num varchar2 default null, p_op_seq_id number default null,
303 p_op_desc varchar2 default null,
304 p_critical_flag number default null,
305 p_parent_link varchar2 default null,
306 p_node_level number default null);
307
308 procedure populateResDtlIntoGantt(p_query_id number,
309 p_row_type number, p_row_index number,
310 p_start_date date, p_end_date date,
311 p_resource_units number, p_resource_hours number,
312 p_schedule_flag number, p_detail_type number,
313 p_display_type number default null);
314
315 procedure populateResReqGanttNew(p_query_id number, p_start_date date, p_end_date date,
316 p_display_type number default null);
317
318 procedure populateResAvailGantt(p_query_id number,p_start_date date, p_end_date date);
319
320 procedure populateResActGantt(p_query_id number,
321 p_start_date date, p_end_date date,
322 p_batched_res_act number,
323 p_require_data IN out nocopy msc_gantt_utils.maxCharTbl,
324 p_display_type number);
325
326 procedure populateSupplierGantt(p_query_id number, p_plan_id number,
327 p_start_date date, p_end_date date);
328
329 procedure populateResIntoGantt(p_query_id number,
330 p_row_index in out nocopy number, p_one_record in out nocopy varchar2,
331 p_plan_id number,
332 p_inst_id number default null, p_org_id number default null,
333 p_dept_id number default null, p_res_id number default null,
334 p_res_instance_id number default null,
335 p_serial_number varchar2 default null,
336 p_add_nodes number default null);
337
338 procedure populateResIntoGanttFromMfq(p_query_id number,
339 p_list varchar2, p_plan_id number);
340
341 procedure populateSuppIntoGantt(p_query_id number,
342 p_row_index number, p_one_record in out nocopy varchar2, p_plan_id number,
343 p_inst_id number default null, p_org_id number default null,
344 p_item_id number default null, p_supplier_id number default null,
345 p_supplier_site_id number default null);
346
347 procedure populateSuppIntoGanttFromMfq(p_query_id number,
348 p_list varchar2, p_plan_id number);
349
350 procedure populateListIntoGantt(p_query_id number,
351 p_plan_id number, p_list varchar2,
352 p_filter_type number, p_view_type number,
353 p_folder_id number default null);
354
355 -- populate msc_gantt_query / msc_gantt_other_query proc's -
356
357 -- res charges and segmentPegging +
358
359 procedure resCharges(p_query_id number, p_plan_id number,
360 p_trx_list varchar2, p_out_data IN OUT NOCOPY msc_gantt_utils.maxCharTbl);
361
362 --p_trx_list is (inst_id, node_type, trx id),(inst_id, node_type, trx_id)
363 procedure segmentPegging(p_query_id number, p_plan_id number,
364 p_trx_list varchar2, p_out_data IN OUT NOCOPY msc_gantt_utils.maxCharTbl);
365
366 -- res charges and segmentPegging -
367
368 --move.resize validation...alternate.simul resource +
369
370 function insertReqFromAlt(p_plan_id number, p_inst_id number,
371 p_simu_res_trx number,
372 p_alt_res_id number,
373 p_alt_res_hours number,
374 p_alt_res_alt_num number,
375 p_alt_res_basis_type number,
376 p_alt_orig_res_seq_num number) return number;
377
378 procedure updateReqFromAlt(p_plan_id number, p_inst_id number, p_simu_res_trx number,
379 p_alt_res_id number, p_alt_res_hours number, p_alt_res_alt_num number, p_alt_res_basis_type number,
380 p_alt_orig_res_seq_num number);
381
382 procedure updateReqInstFromAlt(p_plan_id number, p_inst_id number, p_simu_res_trx number,
383 p_alt_res_id number, p_alt_res_instance_id number, p_serial_number varchar2,
384 p_alt_res_hours number, p_alt_res_alt_num number,
385 p_alt_orig_res_seq_num number);
386
387 procedure DeleteReqFromAlt(p_plan_id number, p_inst_id number, p_simu_res_trx number);
388
389 function insertReqInstFromAlt(p_plan_id number, p_inst_id number,
390 p_simu_res_inst_trx number,
391 p_alt_res_id number,
392 p_alt_res_instance_id number,
393 p_serial_number varchar2,
394 p_alt_res_hours number,
395 p_alt_res_alt_num number,
396 p_from_node number,
397 p_alt_orig_res_seq_num number) return number;
398
399 procedure DeleteReqInstFromAlt(p_plan_id number,
400 p_inst_id number, p_res_inst_trx number);
401
402 procedure validateTime(p_plan_id number,
403 p_transaction_id number, p_instance_id number,
404 p_start_date date, p_end_date date,
405 p_plan_start_date date, p_plan_end_date date,
406 p_return_status in OUT NOCOPY varchar2,
407 p_out in OUT NOCOPY varchar2,
408 p_node_type number);
409
410 procedure getSimuResource(p_plan_id number,
411 p_transaction_id number, p_instance_id number,
412 p_name OUT NOCOPY varchar2, p_id OUT NOCOPY varchar2,
413 p_node_type number default null);
414
415 procedure getAltResource(p_plan_id number,
416 p_transaction_id number, p_instance_id number,
417 p_name OUT NOCOPY varchar2, p_id OUT NOCOPY varchar2,
418 p_node_type number default null,
419 p_from_form number default null);
420
421 procedure loadAltResource(p_plan_id number, p_transaction_id number,
422 p_instance_id number, p_alt_resource number, p_alt_resource_inst number,
423 p_serial_number varchar2, p_alt_num number,
424 p_node_type number, p_to_node_type number,
425 p_return_trx_id out nocopy number,
429
426 p_return_status OUT NOCOPY varchar2,
427 p_out OUT NOCOPY varchar2
428 );
430 procedure loadAltResourceBatch(p_plan_id number, p_transaction_id number,
431 p_instance_id number, p_alt_resource number, p_alt_resource_inst number,
432 p_serial_number varchar2, p_alt_num number,
433 p_node_type number, p_to_node_type number,
434 p_return_trx_id out nocopy number,
435 p_return_status OUT NOCOPY varchar2,
436 p_out OUT NOCOPY varchar2
437 );
438
439 procedure firmResourcePub(p_plan_id number,
440 p_transaction_id number, p_instance_id number, p_firm_type number,
441 p_return_status OUT NOCOPY varchar2,
442 p_out OUT NOCOPY varchar2,
443 p_node_type number);
444
445 procedure firmResourceSeqPub(p_plan_id number,
446 p_trx_list varchar2, p_firm_type number,
447 p_return_status OUT NOCOPY varchar2,
448 p_out OUT NOCOPY varchar2,
449 p_node_type number);
450
451 procedure firmResourceBatchPub(p_plan_id number,
452 p_transaction_id number, p_instance_id number, p_firm_type number,
453 p_return_status OUT NOCOPY varchar2,
454 p_out OUT NOCOPY varchar2,
455 p_node_type number);
456
457 procedure firmSupplyPub(p_plan_id number,
458 p_transaction_id number, p_instance_id number,
459 p_firm_type number,
460 p_start_date date, p_end_date date,
461 p_plan_start_date date, p_plan_end_date date,
462 p_return_status OUT NOCOPY varchar2,
463 p_out OUT NOCOPY varchar2,
464 p_validate_flag boolean default true,
465 p_node_type number);
466
467 procedure moveResourcePub(p_plan_id number,
468 p_transaction_id number, p_instance_id number,
469 p_start_date date, p_end_date date,
470 p_duration varchar2,
471 p_plan_start_date date, p_plan_end_date date,
472 p_return_status OUT NOCOPY varchar2,
473 p_out OUT NOCOPY varchar2,
474 p_validate_flag boolean,
475 p_res_firm_seq boolean,
476 p_batched_res_act boolean,
477 p_node_type number);
478
479 procedure moveSupplyPub (p_plan_id number,
480 p_supply_id number, p_start_date date, p_end_date date,
481 p_plan_start_date date, p_plan_end_date date,
482 p_return_status OUT NOCOPY varchar2,
483 p_out out NOCOPY varchar2 );
484
485 procedure rescheduleData(p_plan_id number,
486 p_instance_id number, p_transaction_id number,
487 p_plan_end_date date,
488 v_require_data OUT NOCOPY varchar2);
489
490 procedure updateReq(p_plan_id number, p_inst_id number, p_trx_id number,
491 p_firm_type number, p_start_date date, p_end_date date,
492 p_firm_start_date date, p_firm_end_date date,
493 p_update_mode number);
494
495 procedure updateReqInst(p_plan_id number, p_inst_id number, p_trx_id number,
496 p_start_date date, p_end_date date);
497
498 procedure updateSupplies(p_plan_id number,
499 p_trx_id number, p_update_type number,
500 p_firm_type number default null,
501 p_firm_date date default null,
502 p_firm_qty number default null);
503
504 procedure updateReqSimu(p_plan_id number, p_inst_id number, p_trx_id number,
505 p_firm_type number, p_start_date in out nocopy date, p_end_date in out nocopy date,
506 p_firm_start_date date, p_firm_end_date date,
507 p_update_mode number,
508 p_return_status in out nocopy varchar2,
509 p_out in out nocopy varchar2);
510
511 procedure moveOneResource(p_plan_id number,
512 p_transaction_id number, p_instance_id number,
513 p_start_date date, p_end_date date,
514 p_return_status in OUT NOCOPY varchar2,
515 p_out in OUT NOCOPY varchar2,
516 p_node_type number) ;
517
518 procedure rescheduleData(p_plan_id number,
519 p_instance_id number, p_org_id number,
520 p_dept_id number, p_res_id number,
521 p_time varchar2,
522 p_plan_end_date date,
523 v_require_data OUT NOCOPY varchar2);
524
525 --move.resize validation...alternate.simul resource -
526
527 --property Data +
528 procedure getProperty(p_plan_id number, p_instance_id number,
529 p_transaction_id number, p_type number, p_view_type number,
530 p_end_demand_id number,
531 v_pro out NOCOPY varchar2, v_demand out NOCOPY varchar2);
532
533 procedure demandPropertyData( p_plan_id number,
534 p_instance_id number, v_transaction_id number,
535 v_org_id number, p_end_demand_id number,
536 v_demand out NOCOPY varchar2);
537
538 procedure resPropertyData(p_plan_id number,
539 p_transaction_id number, p_instance_id number,
540 p_end_demand_id number,
541 v_job OUT NOCOPY varchar2, v_demand OUT NOCOPY varchar2);
542 --property Data -
543
544
545 -- find and folder list +
546 procedure findRequest(p_plan_id number,
547 p_where varchar2, p_query_id number,
548 p_view_type varchar2 default null,
549 p_filter_type number default null,
550 p_folder_id number default null) ;
551
552 procedure constructSupplyRequest(p_query_id number,
553 p_from_block varchar2, p_plan_id number,
554 p_plan_end_date date, p_where varchar2);
555
556 procedure constructResourceRequest(p_query_id number,
557 p_from_block varchar2, p_plan_id number,
558 p_plan_end_date date, p_where varchar2);
559
560 procedure constructRequest(p_query_id number,
561 p_type varchar2, p_plan_id number, p_plan_end_date date,
562 p_where varchar2, p_from_block varchar2);
563
564 -- find and folder list -
565
566 -- sends to client +
567 procedure sendResourceNames(p_query_id number,
568 p_from_index number, p_to_index number,
569 p_name_data IN OUT NOCOPY msc_gantt_utils.maxCharTbl,
570 p_sort_column varchar2 default null,
571 p_sort_order varchar2 default null);
572
573 procedure sendSupplierNames(p_query_id number,
574 p_from_index number, p_to_index number,
575 p_name_data IN OUT NOCOPY msc_gantt_utils.maxCharTbl,
576 p_sort_column varchar2 default null,
577 p_sort_order varchar2 default null);
578
579 procedure sendSupplierGantt(p_query_id number,
580 p_supp_data IN OUT NOCOPY msc_gantt_utils.maxCharTbl);
581
582 procedure sendResourceGantt(p_query_id number, p_view_type number, p_isBucketed number,
583 p_require_data IN OUT NOCOPY msc_gantt_utils.maxCharTbl,
584 p_avail_data IN OUT NOCOPY msc_gantt_utils.maxCharTbl,
585 p_onlyAvailData boolean, p_display_type number);
586 -- sends to client -
587
588 procedure parseResString(p_one_record in varchar2,
589 p_inst_id out nocopy number, p_org_id out nocopy number,
590 p_dept_id out nocopy number, p_res_id out nocopy number,
591 p_res_instance_id out nocopy number,
592 p_serial_number out nocopy varchar2);
593
594 procedure parseSuppString(p_one_record in varchar2,
595 p_inst_id out nocopy number, p_org_id out nocopy number,
596 p_item_id out nocopy number, p_supplier_id out nocopy number,
597 p_supplier_site_id out nocopy number);
598
599 function countStrOccurence (p_string_in in varchar2,
600 p_substring_in in varchar2) return number;
601
602 procedure getBucketDates(p_start_date date, p_end_date date,
603 v_bkt_start in out nocopy msc_gantt_utils.date_arr,
604 v_bkt_end in out nocopy msc_gantt_utils.date_arr,
605 p_query_id in out nocopy number);
606
607 function getResult(p_query_id number,
608 p_from_index number, p_to_index number, p_plan_id number,
609 p_out_data IN OUT NOCOPY msc_gantt_utils.maxCharTbl,
610 p_node_level number default null,
611 p_sort_node number default null,
612 p_sort_column varchar2 default null,
613 p_sort_order varchar2 default null,
614 p_res_nodes_only varchar2 default null) return number;
615
616 procedure getUserPref(p_pref_id number);
617
618 function getResBatchNodeLabel(p_res_req_type varchar2, p_org varchar2,
619 p_batch_qty varchar2, p_batch_number varchar2, p_batch_util_pct varchar2) return varchar2;
620
621 function getResActResNodeLabel(p_plan_id number, p_inst_id number, p_trx_id number) return varchar2;
622 function getOrderViewResNodeLabel(p_plan_id number, p_inst_id number, p_trx_id number) return varchar2;
623
624 function getOpNodeLabel(p_op_seq varchar2,
625 p_dept varchar2, p_op_desc varchar2, p_plan_id number) return varchar2;
626
627 function getJobNodeLabel(p_item_name varchar2, p_org_code varchar2,
628 p_order_number varchar2, p_order_type varchar2, p_qty number) return varchar2;
629
630 function getResReqUlpsd(p_plan number, p_inst number, p_org number, p_dept number,
631 p_res number, p_supply number, p_op_seq number, p_res_seq number,
632 p_orig_res_seq number, p_parent_seq number) return date;
633
634 --function isResRowInGantt(p_query_id number,
635 -- p_res_instance_id number, p_batchable number, p_ignore_batch_flag number default null) return number;
636
637 function isBtchResWithoutBatch(p_plan_id number, p_instance_id number, p_organization_id number,
638 p_department_id number, p_resource_id number, p_res_inst_id number) return number;
639
640 procedure isResRowInGantt(p_query_id number,
641 p_res_rows out nocopy number,
642 p_res_inst_rows out nocopy number,
643 p_res_batch_rows out nocopy number,
644 p_res_inst_batch_rows out nocopy number,
645 p_batch_flag number default null);
646
647 function isResRowInGantt(p_query_id number, p_plan_id number,
648 p_inst_id number, p_org_id number, p_dept_id number, p_res_id number,
649 p_res_inst_id number, p_serial_number varchar2) return number;
650
651 function isResRowValidforResActView(p_plan number, p_inst number, p_org number,
652 p_dept number, p_res number, p_start_date date, p_end_date date) return number;
653
654 END MSC_GANTT_UTILS;