1 PACKAGE MST_WB_UTIL AUTHID CURRENT_USER AS
2 /* $Header: MSTWUTLS.pls 120.1 2005/05/27 05:20:38 appldev $ */
3
4 --Bug_Fix for 4394839
5 /**
6 * Returns the value of profile "MST: Operator Company Name"
7 * or 'CUSTOMER', 'SUPPLIER', 'CARRIER'
8 * depending on the company type.
9 */
10 FUNCTION get_company_type ( p_facility_id IN NUMBER )
11 RETURN VARCHAR2;
12
13 /*
14 Returns the format string for the type indicated by the argument "p_format_type"
15 For Numbers: p_format_type = 'NUMBER'
16 */
17 FUNCTION get_format_string ( p_format_type IN VARCHAR2 )
18 RETURN VARCHAR2;
19
20 -- Function# 14.
21 FUNCTION get_total_order_weight( p_plan_id IN NUMBER,
22 p_source_code IN VARCHAR2,
23 p_source_header_number IN VARCHAR2)
24 RETURN NUMBER;
25 -- Function# 15.
26 FUNCTION get_total_order_volume( p_plan_id IN NUMBER,
27 p_source_code IN VARCHAR2,
28 p_source_header_number IN VARCHAR2)
29 RETURN NUMBER;
30 -- Function# 16.
31 FUNCTION get_total_order_pallets(p_plan_id IN NUMBER,
32 p_source_code IN VARCHAR2,
33 p_source_header_number IN VARCHAR2)
34 RETURN NUMBER;
35 -- Function# 17.
36 FUNCTION get_total_order_pieces( p_plan_id IN NUMBER,
37 p_source_code IN VARCHAR2,
38 p_source_header_number IN VARCHAR2)
39 RETURN NUMBER;
40 -- Function# 18.
41 FUNCTION get_total_order_cost ( p_plan_id IN NUMBER,
42 p_source_code IN VARCHAR2,
43 p_source_header_number IN VARCHAR2)
44 RETURN NUMBER;
45
46 -- Function# 26.
47 FUNCTION Get_Trip_Circuity(P_Plan_id IN NUMBER,
48 P_Trip_id IN NUMBER)
49 RETURN NUMBER;
50 -- Function# 27.
51 FUNCTION Get_Trip_Stops(P_Plan_id IN NUMBER,
52 P_Trip_id IN NUMBER)
53 RETURN NUMBER;
54 -- Function# 28.
55 FUNCTION Get_Trip_Orders(P_plan_id IN NUMBER,
56 P_TRIP_ID IN NUMBER )
57 RETURN NUMBER;
58 -- Function# 29.
59 FUNCTION Get_Trip_Det(P_plan_id IN NUMBER,
60 P_Trip_Id IN NUMBER,
61 P_Return_val IN VARCHAR2)
62 RETURN NUMBER;
63 -- Function# 30.
64 FUNCTION Get_Trip_Det(P_plan_id IN NUMBER,
65 P_Trip_Id IN NUMBER,
66 P_Stop_Id IN NUMBER,
67 P_Stop_Type IN VARCHAR2,
68 P_Return_val IN VARCHAR2)
69 RETURN NUMBER;
70 -- Function# 31.
71 FUNCTION Get_STOP_Orders(P_plan_id IN NUMBER,
72 P_TRIP_ID IN NUMBER,
73 P_Stop_Id IN NUMBER )
74 RETURN NUMBER;
75 -- Function# 32.
76 FUNCTION GET_DELIVERY_ORDERS(P_Plan_Id IN NUMBER,
77 P_DELIVERY_ID IN NUMBER,
78 P_Delivery_Flag IN VARCHAR2 )
79 RETURN NUMBER;
80 -- Function# 33.
81 FUNCTION Get_Name(P_Location_id IN NUMBER)
82 RETURN VARCHAR2;
83 -- Function# 34.
84 FUNCTION Get_meaning( p_Lookup_Type IN VARCHAR2,
85 p_Lookup_Code IN VARCHAR2,
86 p_Product IN VARCHAR2)
87 RETURN VARCHAR2;
88
89 FUNCTION Get_Partner_Name(P_PARTY_id IN Number, P_PARTY_type IN Number)
90 RETURN VARCHAR2;
91
92 -- Function# 35.
93 FUNCTION Get_Cont_Move_Distance( p_Plan_Id IN NUMBER,
94 p_cont_move_id IN NUMBER)
95 RETURN NUMBER;
96 -- Function# 36.
97 FUNCTION GET_FIRST_DEPARTURE_DATE(P_PLAN_ID IN NUMBER,
98 P_TRIP_ID IN NUMBER)
99 RETURN DATE;
100 -- Function# 37.
101 FUNCTION GET_LAST_ARRIVAL_DATE(P_PLAN_ID IN NUMBER,
102 P_TRIP_ID IN NUMBER)
103 RETURN DATE;
104 -- Function# 38.
105 FUNCTION Get_Cont_Move_total_loads( p_Plan_Id IN NUMBER,
106 p_cont_move_id IN NUMBER)
107 RETURN NUMBER;
108
109 FUNCTION get_effective_cube_capacity(p_plan_id IN NUMBER,
110 p_trip_id IN NUMBER)
111 RETURN NUMBER;
112 -- Function# 39.
113 FUNCTION GET_VEHICLE_CAPACITY(P_Plan_Id IN NUMBER,
114 P_VEHICLE_TYPE_ID IN NUMBER,
115 P_Ret_Val IN VARCHAR2)
116 RETURN NUMBER;
117 -- Function# 40.
118 FUNCTION ELAPSED_TIME(P_Start_Date IN DATE,
119 P_End_Date IN DATE)
120 RETURN VARCHAR2;
121 -- Function# 41.
122 FUNCTION ELAPSED_TIME(P_End_Date IN DATE,
123 P_Delay IN NUMBER)
124 RETURN VARCHAR2;
125 -- Function# 42.
126 FUNCTION get_threshold_value (p_exception_type IN NUMBER)
127 RETURN VARCHAR2;
128 -- Function# 43.
129 FUNCTION get_facility_owner(P_Facility_id IN NUMBER, P_Delim IN VARCHAR2)
130 RETURN VARCHAR2;
131 -- Function# 44.
132 FUNCTION Get_Contact_name(p_contact_id IN NUMBER)
133 RETURN VARCHAR2;
134 -- Function# 45.
135 FUNCTION get_phone_number(p_contact_id IN NUMBER)
136 RETURN VARCHAR2;
137 FUNCTION get_min_sec(p_hours NUMBER)
138 RETURN VARCHAR2;
139 FUNCTION get_hr_min(p_hours IN NUMBER)
140 RETURN VARCHAR2;
141 FUNCTION get_local_chardt(p_location_id IN NUMBER, p_date IN DATE)
142 RETURN VARCHAR2;
143 FUNCTION get_local_chardtzone(p_location_id IN NUMBER, p_date IN DATE)
144 RETURN VARCHAR2;
145 FUNCTION GET_LEG_NUMBER(P_PLAN_ID IN NUMBER,
146 P_TRIP_ID IN NUMBER,
147 P_STOP_ID IN NUMBER)
148 RETURN NUMBER;
149 FUNCTION GET_TRIP_UTILIZATION(P_PLAN_ID IN NUMBER,
150 P_TRIP_ID IN NUMBER)
151 RETURN NUMBER;
152
153 FUNCTION GET_TRIP_REMAINING_TIME(P_PLAN_ID IN NUMBER,
154 P_TRIP_ID IN NUMBER)
155 RETURN NUMBER;
156 FUNCTION GET_CM_REMAINING_TIME(P_PLAN_ID IN NUMBER,
157 P_CM_ID IN NUMBER)
158 RETURN NUMBER;
159
160 FUNCTION GET_TRIP_TOKENIZED_EXCEPTION(P_PLAN_ID IN NUMBER,
161 P_EXCEPTION_DETAIL_ID IN NUMBER,
162 P_TRIP_ID IN NUMBER,
163 P_LINE_NUM IN NUMBER)
164 RETURN VARCHAR2;
165
166
167 --p_contact_id expects party_id (as defined in hz_parties) as input
168 --p_ret_str_type expects 'NAME', 'EMAIL' or 'PHONE' as input
169 --p_owner_type_id expects owner_type (as defined in wsh_location_owners) as input
170 FUNCTION GET_CONTACT_INFO (P_CONTACT_ID IN NUMBER
171 ,P_RET_STR_TYPE IN VARCHAR2
172 ,P_OWNER_TYPE_ID IN NUMBER DEFAULT NULL)
173 RETURN VARCHAR2;
174
175
176 /**************************USED IN REPORTS***************************/
177
178 function r_get_canonical_number (p_number in number
179 , p_format_mask_ident in number default 1)
180 return varchar2;
181
182 function r_get_company_name(p_location_id in number, p_owner_type in number)
183 return varchar2;
184
185 function r_plan_value (p_plan_id in number)
186 return number;
187
188 function r_plan_alloc_cost (p_plan_id in number)
189 return number;
190 /*
191 function r_total_orders_myfac(p_plan_id in number, p_my_fac_location_id in number, p_mode in varchar2, p_activity_type in varchar2)
192 return number;
193 */
194 function r_total_cost_myfac (p_plan_id in number,p_my_fac_location_id in number, p_mode in varchar2)
195 return number;
196
197 function r_loading_weight_myfac (p_plan_id in number, p_my_fac_location_id in number, p_mode in varchar2)
198 return number;
199
200 function r_loading_cube_myfac (p_plan_id in number, p_my_fac_location_id in number, p_mode in varchar2)
201 return number;
202
203 function r_loading_piece_myfac (p_plan_id in number, p_my_fac_location_id in number, p_mode in varchar2)
204 return number;
205
206 function r_value_myfac(p_plan_id in number, p_facility_id in number)
207 return number;
208
209 function r_total_orders_myfac_general (p_plan_id in number, p_my_fac_location_id in number)
210 return number;
211
212 function r_total_weight_myfac (p_plan_id in number, p_facility_id in number)
213 return number;
214
215 function r_total_cube_myfac (p_plan_id in number, p_facility_id in number)
216 return number;
217
218 function r_total_pieces_myfac (p_plan_id in number, p_facility_id in number)
219 return number;
220
221 function r_total_trans_cost_myfac (p_plan_id in number, p_facility_id in number)
222 return number;
223
224 function r_value_origin(p_plan_id in number, p_origin_id in number)
225 return number;
226
227 function r_get_alloc_cost_origin (p_plan_id in number, p_origin_id in number)
228 return number;
229
230 function r_get_total_orders_origin (p_plan_id in number, p_origin_id in number)
231 return number;
232
233 function r_get_count_stops_origin (p_plan_id in number, p_origin_id in number)
234 return number;
235
236 function r_get_total_weight_origin (p_plan_id in number, p_origin_id in number)
237 return number;
238
239 function r_get_total_volume_origin (p_plan_id in number, p_origin_id in number)
240 return number;
241
242 function r_get_total_pieces_origin (p_plan_id in number, p_origin_id in number)
243 RETURN number;
244
245 FUNCTION r_get_trip_count_origin (p_plan_id in number, p_origin_id in number, p_mode_of_transport in varchar2)
246 RETURN number;
247
248 FUNCTION r_get_cost_origin (p_plan_id in number, p_origin_id in number, p_mode_of_transport in varchar2)
249 RETURN number;
250
251 FUNCTION r_get_count_dtl_origin (p_plan_id in number, p_origin_id in number)
252 RETURN number;
253
254 function r_value_dest(p_plan_id in number, p_dest_id in number)
255 return number;
256
257 function r_get_alloc_cost_dest (p_plan_id in number, p_dest_id in number)
258 return number;
259
260 FUNCTION r_get_total_orders_dest (p_plan_id in number, p_dest_id in number)
261 RETURN number;
262
263 FUNCTION r_get_count_stops_dest (p_plan_id in number, p_dest_id in number)
264 RETURN number;
265
266 FUNCTION r_get_total_weight_dest (p_plan_id in number, p_dest_id in number)
267 RETURN number;
268
269 FUNCTION r_get_total_volume_dest (p_plan_id in number, p_dest_id in number)
270 RETURN number;
271
272 FUNCTION r_get_total_pieces_dest (p_plan_id in number, p_dest_id in number)
273 RETURN number;
274
275 FUNCTION r_get_trip_count_dest (p_plan_id in number, p_dest_id in number, p_mode_of_transport in varchar2)
276 RETURN number;
277
278 FUNCTION r_get_cost_dest (p_plan_id in number, p_dest_id in number, p_mode_of_transport in varchar2)
279 RETURN number;
280
281 FUNCTION r_get_count_dtl_dest (p_plan_id in number, p_dest_id in number)
282 RETURN number;
283
284 function r_value_cust(p_plan_id in number, p_customer_id in number)
285 return number;
286
287 function r_get_alloc_cost_cust (p_plan_id in number, p_customer_id in number)
288 return number;
289
290 FUNCTION r_get_count_stops_cust (p_plan_id in number, p_customer_id in number)
291 RETURN number;
292
293 FUNCTION r_get_trip_count_cust (p_plan_id in number, p_customer_id in number, p_mode_of_transport in varchar2)
294 RETURN number;
295
296 FUNCTION r_get_cost_cust (p_plan_id in number, p_customer_id in number, p_mode_of_transport in varchar2)
297 RETURN number;
298
299 FUNCTION r_get_count_dtl_cust (p_plan_id in number, p_customer_id in number)
300 RETURN number;
301
302 function r_value_supp(p_plan_id in number, p_supplier_id in number)
303 return number;
304
305 function r_get_alloc_cost_supp (p_plan_id in number, p_supplier_id in number)
306 return number;
307
308 FUNCTION r_get_count_stops_supp (p_plan_id in number, p_supplier_id in number)
309 RETURN number;
310
311 FUNCTION r_get_trip_count_supp (p_plan_id in number, p_supplier_id in number, p_mode_of_transport in varchar2)
312 RETURN number;
313
314 FUNCTION r_get_cost_supp (p_plan_id in number, p_supplier_id in number, p_mode_of_transport in varchar2)
315 RETURN number;
316
317 FUNCTION r_get_count_dtl_supp (p_plan_id in number, p_supplier_id in number)
318 RETURN number;
319
320 function r_get_wait_time_at_stop (p_plan_id in number, p_stop_id in number, p_trip_id in number)
321 return varchar2;
322
323 function r_get_prev_carr_detail (p_plan_id in number,p_delivery_id in number,p_trip_id in number, p_stop_location_id in number, p_identifier in varchar2)
324 return number;
325
326 function r_get_prev_stop_seqnum (p_plan_id in number, p_trip_id in number, p_curr_seq_num in number)
327 return number;
328
329 function r_get_prev_trip_detail (p_plan_id in number, p_trip_id in number, p_curr_seq_num in number, p_identifier in varchar2)
330 return number;
331
332 function r_get_pool_loc_detail (ret_type in varchar2, loc_id in number)
333 return varchar2;
334
335 function r_dep_frm_dest(p_plan_id in number, p_trip_id in number, p_stop_location_id in number)
336 return varchar2;
337
338 function r_get_order_cost(p_source_code in varchar2,p_source_header_number in varchar2)
339 return number;
340
341 function r_checkif_orig_ispool (p_plan_id in number,p_delivery_id in number,p_delivery_leg_id in number,p_pick_up_stop_id in number)
342 return varchar2;
343
344 function r_checkif_dest_ispool (p_plan_id in number,p_delivery_id in number,p_delivery_leg_id in number,p_drop_off_stop_id in number)
345 return varchar2;
346
347 PROCEDURE Execute_Report (ERRBUF OUT NOCOPY VARCHAR2
348 ,RETCODE OUT NOCOPY VARCHAR2
349 , request_id out nocopy number
350 , arg1 in number
351 , arg2 in number
352 , arg3 in number
353 , arg4 in number
354 , arg5 in number
355 , arg6 in number
356 , arg7 in varchar2
357 , arg8 in varchar2
358 , arg9 in number
359 , arg10 in number
360 );
361
362 /******************************************************************************/
363
364 FUNCTION GET_COST_WIHTOUT_CM_FOR_TRIPS (P_PLAN_ID IN NUMBER,
365 P_TRIP_ID1 IN NUMBER,
366 P_TRIP_ID2 IN NUMBER)
367 RETURN NUMBER;
368
369 FUNCTION GET_LOAD_TYPE (P_PLAN_ID IN NUMBER,
370 P_TRIP_ID IN NUMBER)
371 RETURN VARCHAR2;
372
373 FUNCTION GET_ACT_TOKENIZED_EXCEPTION(P_PLAN_ID IN NUMBER,
374 P_OUT_REQUEST_DETAIL_ID IN NUMBER,
375 P_EXCEPTION_TYPE IN NUMBER,
376 P_LINE_NUM IN NUMBER)
377 RETURN VARCHAR2;
378
379 FUNCTION adjust_to_server_time(p_datetime IN DATE,
380 p_location_id IN NUMBER,
381 p_facility_id IN NUMBER) RETURN DATE;
382
383
384 FUNCTION convert_time(p_time IN NUMBER,
385 p_uom_from IN VARCHAR2,
386 p_uom_to IN VARCHAR2) RETURN NUMBER;
387
388 /**************FOLLOWING ARE BEING USED FOR PURGE PLAN************************/
389
390 procedure purge_plan (p_err_code OUT NOCOPY VARCHAR2
391 , p_err_buff OUT NOCOPY VARCHAR2
392 , p_plan_id IN NUMBER
393 , p_compile_designator IN VARCHAR2
394 , p_description IN VARCHAR2);
395
396 procedure submit_purge_plan_request ( p_request_id OUT NOCOPY NUMBER
397 , p_plan_id IN NUMBER
398 , p_compile_designator IN VARCHAR2
399 , p_description IN VARCHAR2);
400
401 /******************************************************************************/
402
403 function get_org_id(p_plan_id in number, p_delivery_id in number) return number;
404
405 function get_workflow_status (p_plan_id in number, p_exception_detail_id in number) return varchar2;
406
407 function get_city_code(p_location_id NUMBER) return VARCHAR2;
408
409 procedure Compute_Exception_Counts(p_Plan_Id IN NUMBER, p_Exp_Summary_Where_Clause IN VARCHAR2, p_Exp_Details_Where_Clause IN VARCHAR2);
410
411 PROCEDURE run_dynamic_sql(p_query_string IN VARCHAR2);
412
413 PROCEDURE notify_engine(p_plan_id IN NUMBER,
414 p_object_type IN NUMBER,
415 p_object_id IN NUMBER,
416 p_firm_status IN NUMBER);
417
418 PROCEDURE Update_Del_And_Rel_Trips(p_Plan_Id IN NUMBER,
419 p_Trip_Id IN NUMBER,
420 p_Planned_Flag IN NUMBER,
421 P_Notified OUT NOCOPY NUMBER);
422
423 PROCEDURE Update_Trips_Of_CM(p_Plan_Id IN NUMBER,
424 p_Continuous_Move_Id IN NUMBER,
425 P_Notified OUT NOCOPY NUMBER);
426
427 FUNCTION GET_UOM_CONVERSION_RATE (p_from_uom_code VARCHAR2,
428 p_to_uom_code VARCHAR2,
429 p_org_id NUMBER,
430 p_inventory_item_id NUMBER)
431 RETURN NUMBER;
432
433 /*
434 Determines whether the rule is for Currency or Weight/Volume/Count
435 */
436 FUNCTION get_rule_type ( p_rule_id IN NUMBER )
437 RETURN VARCHAR2;
438
439 FUNCTION get_row_count (p_view_name IN VARCHAR2, p_where_clause in VARCHAR2)
440 RETURN NUMBER;
441
442 END MST_WB_UTIL;
[Home] [Help]