1 PACKAGE MST_AGG_PKG AUTHID CURRENT_USER AS
2 /*$Header: MSTAGGQS.pls 115.14 2004/06/09 10:19:51 skakani noship $ */
3
4 TYPE NumList IS TABLE OF NUMBER;
5 P_Delim CONSTANT VARCHAR2(10) := '#';
6 OE CONSTANT VARCHAR2(10) := 'OE';
7 RFC CONSTANT VARCHAR2(10) := 'RFC'; -- Return from Customer
8 PO CONSTANT VARCHAR2(10) := 'PO';
9 RTV CONSTANT VARCHAR2(10) := 'RTV'; -- Return to Vendor
10
11
12 G_Time_delim CONSTANT VARCHAR2(1) := ':';
13 G_delim CONSTANT VARCHAR2(1) := '#';
14
15 ORGANIZATION CONSTANT NUMBER := 1;
16 CUSTOMER CONSTANT NUMBER :=2;
17 CARRIER CONSTANT NUMBER := 3;
18 SUPPLIER CONSTANT NUMBER := 4;
19
20 TRUCK CONSTANT VARCHAR2(5) := 'TRUCK'; -- TRUCK
21 DTL CONSTANT VARCHAR2(3) := 'DTL'; -- Direct TL
22 MTL CONSTANT VARCHAR2(3) := 'MTL'; -- Multistop TL
23 LTL CONSTANT VARCHAR2(3) := 'LTL'; -- LESS THAN TRUCK
24 PARCEL CONSTANT VARCHAR2(6) := 'PARCEL'; -- PARCEL
25 /********************************************************
26 g_num_of_facilities number;
27 p_delim varchar2(10) := '#';
28 OE varchar2(10) := 'OE';
29 RFC varchar2(10) := 'RFC'; -- Return from Customer
30 PO varchar2(10) := 'PO';
31 RTS varchar2(10) := 'RTS'; -- Return to Supplier
32 ******************************************************/
33
34 /* Plan Summary */
35 function get_loads_count(p_plan_id in number)
36 return number;
37
38 function get_trip_count(p_plan_id in number, p_mode_of_transport in varchar2)
39 return number;
40
41 function get_cm_count(p_plan_id in number)
42 return number;
43
44 function get_loads_orders(p_plan_id in number)
45 return number;
46
47 function get_trip_orders(p_plan_id in number, p_mode_of_transport in varchar2)
48 return number;
49
50 function get_cm_orders(p_plan_id in number)
51 return number;
52
53 function get_unassigned_order_count(p_plan_id in number)
54 return number;
55 function get_unassigned_order_weight(p_plan_id in number)
56 return number;
57 function get_unassigned_order_cube(p_plan_id in number)
58 return number;
59 function get_unassigned_order_pieces(p_plan_id in number)
60 return number;
61 function get_unassigned_order_pallets(p_plan_id in number)
62 return number;
63
64 /* All Truckloads Summary */
65 function get_summary_fixed_charge(p_plan_id in number, p_mode in varchar2)
66 return number;
67
68 function get_summary_stop_charge(p_plan_id in number)
69 return number;
70
71 function get_summary_ld_unld_charge(p_plan_id in number)
72 return number;
73
74 function get_summary_layover_charge(p_plan_id in number)
75 return number;
76
77 function get_summary_assessorial_charge(p_plan_id in number, p_mode in varchar2)
78 return number;
79
80 /* TruckLoad Details */
81 FUNCTION Get_alloc_cost_for_delivery(p_plan_id IN NUMBER,
82 p_delivery_id IN NUMBER)
83 RETURN NUMBER;
84
85 /* Vehicle Trip Stop Details */
86 function get_loading_charge(p_plan_id IN NUMBER,
87 p_stop_id in number)
88 return number;
89
90 function get_unloading_charge(p_stop_id in number)
91 return number;
92 -- Loading Weight/Cube/Pallets/Pieces
93 function get_loading_weight(p_stop_id in number)
94 return number;
95 function get_loading_volume(p_stop_id in number)
96 return number;
97
98 function get_loading_pallets(p_stop_id in number)
99 return number;
100
101 function get_loading_pieces(p_stop_id in number)
102 return number;
103 -- Unloading Weight/Cube/Pallets/Pieces
104 function get_unloading_weight(p_stop_id in number)
105 return number;
106
107 function get_unloading_volume(p_stop_id in number)
108 return number;
109
110 function get_unloading_pallets(p_stop_id in number)
111 return number;
112
113 function get_unloading_pieces(p_stop_id in number)
114 return number;
115
116 /* Vehicle Trip Leg Details */
117 -- No aggregation fields:
118 -- On board weight/cube/pallets/pieces can be fetched directly from MST_TRIP_STOPS
119 -- the columns are departure weight/cube/pieces/pallets of the first stop
120
121 -- On board orders:
122 ---- ?????
123
124 -- Distance Charge:
125 ---- ?????
126
127 -- Max weight/cube/pallets for a vehicle
128 /*
129 select
130 item.maximum_load_weight max_load_weight,
131 ??decode() max_load_volume -- based on direct move, pool move, stop numbers, the effective cap could be different
132 pallet_floor_space * pallet_stacking_height max_number_of_pallets
133 from fte_vehicle_types fte, mtl_system_items item
134 where vehicle_type_id = p_vehicle_type_id
135 and fte.organization_id = item.organization_id
136 and fte.inventory_item_id = item.inventory_item_id
137 */
138
139 /* Delivery Details */
140 function get_delivery_cost(p_delivery_id in number)
141 return number;
142
143 /* LTL/Parcel Summary */
144 -- Weight Based/Minimum charges
145 ---- use get_summary_fixed_charge() with appropriate mode and plan_id
146
147 -- Assessorial charges
148 ---- use get_summary_assessorial_charge() with appropriate mode and plan_id
149
150 /* LTL/Parcel Details */
151 -- no aggregate functions, cost/weight/cube/pallets/pieces can be fetched
152 -- directly from base tables in view
153
154
155 /* All Continuous Moves */
156 function get_total_savings(p_plan_id in number)
157 return number;
158
159 function get_percent_of_tl_in_cm(p_plan_id in number)
160 return number;
161
162 /* Continuous Move Details */
163 -- open issues
164
165 /* All Orders */
166 -- number of orders, total costs can be fetched directly from MST_PLANS
167 /* Order Details */
168 function get_order_weight(p_source_code in varchar2,
169 p_source_header_number in varchar2)
170 return number;
171
172 function get_order_cube(p_source_code in varchar2,
173 p_source_header_number in varchar2)
174 return number ;
175
176 function get_order_pallets(p_source_code in varchar2,
177 p_source_header_number in varchar2)
178 return number;
179
180 function get_order_pieces(p_source_code in varchar2,
181 p_source_header_number in varchar2)
182 return number;
183
184 /* Carrier Details */
185
186 function get_carrier_total_cost(p_plan_id in number, p_carrier_id in number)
187 return number;
188
189 function get_carrier_weight(p_plan_id in number, p_carrier_id in number)
190 return number;
191
192 function get_carrier_volume(p_plan_id in number,p_carrier_id in number)
193 return number;
194 function get_carrier_pallets(p_plan_id in number,p_carrier_id in number)
195 return number;
196 function get_carrier_pieces(p_plan_id in number,p_carrier_id in number)
197 return number;
198 function get_carrier_orders(p_plan_id in number,p_carrier_id in number)
199 return number;
200
201 /* Service Details */
202 -- Should we exclude cost for tls in CM for mode = TL?
203 function get_carrier_service_total_cost(p_plan_id in number,
204 p_carrier_id in number, p_mode in varchar2, p_service in varchar2)
205 return number;
206
207 function get_carrier_service_weight(p_plan_id in number, p_carrier_id in number, p_mode in varchar2, p_service in varchar2)
208 return number;
209 function get_carrier_service_volume(p_plan_id in number,p_carrier_id in number, p_mode in varchar2, p_service in varchar2)
210 return number;
211 function get_carrier_service_pallets(p_plan_id in number,p_carrier_id in number, p_mode in varchar2, p_service in varchar2)
212 return number;
213 function get_carrier_service_pieces(p_plan_id in number,p_carrier_id in number, p_mode in varchar2, p_service in varchar2)
214 return number;
215
216 /* Customer Details */
217 function get_total_cost_cust (p_plan_id in number, p_customer_id in number)
218 return number;
219
220 function get_total_weight_cust (p_plan_id in number, p_customer_id in number)
221 return number;
222
223 function get_total_cube_cust (p_plan_id in number, p_customer_id in number)
224 return number;
225
226 function get_total_pallets_cust (p_plan_id in number, p_customer_id in number)
227 return number;
228
229 function get_total_pieces_cust (p_plan_id in number, p_customer_id in number)
230 return number;
231
232 function get_total_orders_cust (p_plan_id in number, p_customer_id in number)
233 return number;
234
235 function get_total_trip_count_partner (p_plan_id in number, p_partner_id in number, p_partner_type in number )
236 return varchar2 ;
237
238 function get_customer_facilities(p_plan_id in number, p_customer_id in number)
239 return NumList;
240
241 function get_num_of_stops_for_tl(p_plan_id in number, p_trip_id in number)
242 return number;
243
244 function get_first_or_last_delivery_leg(p_plan_id in number, p_delivery_id in number, p_type in number)
245 return number ;
246 /* Supplier Details */
247 function get_total_cost_supp (p_plan_id in number, p_supplier_id in number)
248 return number;
249
250 function get_total_weight_supp (p_plan_id in number, p_supplier_id in number)
251 return number;
252
253 function get_total_cube_supp (p_plan_id in number, p_supplier_id in number)
254 return number;
255
256 function get_total_pallets_supp (p_plan_id in number, p_supplier_id in number)
257 return number;
258
259 function get_total_pieces_supp (p_plan_id in number, p_supplier_id in number)
260 return number;
261
262 function get_total_orders_supp (p_plan_id in number, p_supplier_id in number)
263 return number;
264
265 function get_supplier_facilities(p_plan_id in number, p_supplier_id in number)
266 return NumList;
267
268
269 /* Customer/Supplier Facility Details */
270 function get_total_cost_c_s_fac (p_plan_id in number, p_facility_type in number,
271 p_cust_or_supp_id in number, p_location_id in number)
272 return number;
273
274
278
275 function get_total_weight_c_s_fac (p_plan_id in number, p_facility_type in number,
276 p_cust_or_supp_id in number, p_location_id in number)
277 return number;
279 function get_total_cube_c_s_fac (p_plan_id in number,p_facility_type in number,
280 p_cust_or_supp_id in number, p_location_id in number)
281 return number;
282 function get_total_pallets_c_s_fac (p_plan_id in number, p_facility_type in number,
283 p_cust_or_supp_id in number, p_location_id in number)
284 return number;
285 function get_total_pieces_c_s_fac (p_plan_id in number, p_facility_type in number,
286 p_cust_or_supp_id in number, p_location_id in number)
287 return number;
288 function get_total_order_c_s_fac (p_plan_id in number, p_facility_type in number,
289 p_cust_or_supp_id in number,p_location_id in number)
290 return number;
291 -- This function is merged from Sasidhar's package
292 -- the function name has been changed from get_total_direct_tls_facility for consistency
293 FUNCTION get_total_tl_count_c_s_fac
294 (p_plan_id IN NUMBER,
295 p_partner_id IN NUMBER,
296 p_partner_type IN NUMBER,
297 p_location_id IN NUMBER,
298 p_mode_of_transport IN VARCHAR2)
299 RETURN NUMBER;
300 -- This function is merged from Sasidhar's package
301 -- the function name has been changed from get_total_trips_facility for consistency
302 FUNCTION get_total_trips_c_s_fac (p_plan_id IN NUMBER,
303 p_partner_id IN NUMBER,
304 p_partner_type IN NUMBER,
305 p_location_id IN NUMBER,
306 p_mode_of_transport IN VARCHAR2)
307 RETURN NUMBER;
308
309 /* My Facility Details*/
310 -- This function is merged from Sasidhar's package
311 -- the function name has been changed from get_total_trips_Myfacility for consistency
312 FUNCTION get_total_trips_for_myfac( p_plan_id IN NUMBER,
313 P_Fac_Loc_Id IN NUMBER,
314 p_mode_of_transport IN VARCHAR2,
315 p_location_type IN VARCHAR2)
316 RETURN NUMBER;
317
318 -- This function is merged from Sasidhar's package
319 -- the function name has been changed from get_total_orders_Myfacility for consistency
320 -- the implementation logic has also been changed to aggregate on TP orders instead of raw orders
321 FUNCTION get_total_orders_for_myfac( p_plan_id IN NUMBER,
322 p_my_fac_location_id IN NUMBER,
323 p_mode IN VARCHAR2 DEFAULT null,
324 p_activity_type IN VARCHAR2 DEFAULT null)
325 RETURN NUMBER;
326
327 -- loading/unloading/total cost for TL/LTL/Parcel
328 function get_loading_cost_for_myfac (p_plan_id in number, p_my_fac_location_id in number,
329 p_mode in varchar2)
330 return number;
331
332 function get_unloading_cost_for_myfac (p_plan_id in number, p_my_fac_location_id in number,
333 p_mode in varchar2)
334 return number;
335
336 function get_total_cost_for_myfac (p_plan_id in number, p_my_fac_location_id in number,
337 p_mode in varchar2)
338 return number;
339
340 -- loading/unloading weight/cube/pallets/pieces for TL/LTL/Parcels
341
342 function get_loading_weight_for_myfac (p_plan_id in number, p_my_fac_location_id in number,
343 p_mode in varchar2)
344 return number;
345
346 function get_unloading_weight_for_myfac (p_plan_id in number, p_my_fac_location_id in number,
347 p_mode in varchar2)
348 return number;
349
350 function get_loading_cube_for_myfac (p_plan_id in number, p_my_fac_location_id in number,
351 p_mode in varchar2)
352 return number;
353
354 function get_unloading_cube_for_myfac (p_plan_id in number, p_my_fac_location_id in number,
355 p_mode in varchar2)
356 return number;
357
358 function get_loading_pallet_for_myfac (p_plan_id in number, p_my_fac_location_id in number,
359 p_mode in varchar2)
360 return number;
361
362 function get_unloading_pallet_for_myfac (p_plan_id in number, p_my_fac_location_id in number,
363 p_mode in varchar2)
364 return number;
365
366 function get_loading_piece_for_myfac (p_plan_id in number, p_my_fac_location_id in number,
367 p_mode in varchar2)
368 return number;
369
370 function get_unloading_piece_for_myfac (p_plan_id in number, p_my_fac_location_id in number,
371 p_mode in varchar2)
372 return number;
373
374 /* Carrier Details */
375 /* Carrier Facility Details */
376 /* The following functions for Carrier Details and Carrier Facility Details windows were created by Sasidhar */
377 -- Function# 46.
378 FUNCTION get_total_trips_carrier(p_plan_id IN NUMBER,
379 p_carrier_id IN NUMBER)
380 RETURN VARCHAR2;
381 -- Function# 47.
385 -- Function# 48.
382 FUNCTION get_total_cost_carrier(p_plan_id IN NUMBER,
383 p_carrier_id IN NUMBER)
384 RETURN NUMBER;
386 FUNCTION get_total_weight_carr_facility(p_plan_id IN NUMBER,
387 p_carrier_id IN NUMBER,
388 p_location_id IN NUMBER,
389 p_location_type IN VARCHAR2,
390 p_mode_of_transport IN VARCHAR2)
391 RETURN NUMBER;
392 -- Function# 49.
393 FUNCTION get_total_cube_carr_facility(p_plan_id IN NUMBER,
394 p_carrier_id IN NUMBER,
395 p_location_id IN NUMBER,
396 p_location_type IN VARCHAR2,
397 p_mode_of_transport IN VARCHAR2)
398 RETURN NUMBER;
399 -- Function# 50.
400 FUNCTION get_tot_pal_carr_facility(p_plan_id IN NUMBER,
401 p_carrier_id IN NUMBER,
402 p_location_id IN NUMBER,
403 p_location_type IN VARCHAR2,
404 p_mode_of_transport IN VARCHAR2)
405 RETURN NUMBER;
406 -- Function# 51.
407 FUNCTION get_tot_Pieces_carr_facility(p_plan_id IN NUMBER,
408 p_carrier_id IN NUMBER,
409 p_location_id IN NUMBER,
410 p_location_type IN VARCHAR2,
411 p_mode_of_transport IN VARCHAR2)
412 RETURN NUMBER;
413 -- Function# 52.
414 -- the implementation logic has been changed to aggregate on TP orders instead of raw orders
415 FUNCTION get_tot_Orders_carr_facility(p_plan_id IN NUMBER,
416 p_carrier_id IN NUMBER,
417 p_fac_location_id IN NUMBER,
418 p_activity_type IN VARCHAR2,
419 p_mode IN VARCHAR2
420 )
421 RETURN NUMBER;
422 -- Function# 53.
423 FUNCTION get_tot_trips_carr_Facility(p_plan_id IN NUMBER,
424 p_carrier_id IN NUMBER,
425 P_LOCATION_ID IN NUMBER,
426 p_location_type IN VARCHAR2,
427 p_mode_of_transport IN VARCHAR2)
428 RETURN NUMBER;
429 FUNCTION get_direct_tls_carr_facility
430 (p_plan_id IN NUMBER,
431 p_carrier_id IN NUMBER,
432 p_location_id IN NUMBER,
433 p_Location_type IN VARCHAR2,
434 p_mode_of_transport IN VARCHAR2)
435 RETURN NUMBER;
436 FUNCTION get_total_cost_carr_fac(p_plan_id IN NUMBER,
437 p_carrier_id IN NUMBER,
438 p_location_id IN NUMBER,
439 p_mode_of_transport IN VARCHAR2)
440 RETURN NUMBER;
441
442
443 -- Used by view definitions of Delivery Line grids:
444 -- 1. Call this function when source_code = OE for the Destination Company Name:
445 -- p_location_id = MST_DELIVERY_DETAILS.ship_to_location_id
446 -- p_customer_id = MST_DELIVERY_DETAILS.customer_id
447 -- 2. Call this function when source_code = OE for the Origin Company Name:
448 -- p_location_id = MST_DELIVERY_DETAILS.ship_from_location_id
449 -- p_customer_id = null
450
451 function get_owner_name_for_del_line(p_location_id IN number, p_customer_id IN number)
452 return varchar2 ;
453
454 -- Used by view definitions of Delivery Leg grids:
455 -- Call this function to get both the Origin Company Name and the Destination Company Name
456
457 function get_owner_name_for_del_leg(p_location_id IN number,
458 p_plan_id IN NUMBER,
459 p_delivery_id IN number)
460 return varchar2;
461
462 -- Used by view definitions of Trips/Loads/Stops/Trip-legs grids:
463 -- Call this function to get both the Origin Company Name and the Destination Company Name
464
465 function get_owner_name_for_trip(p_location_id IN number)
466 return varchar2;
467
468 -- Utility functions used by release related views
469
470 -- TRIP related functions ------------
471
472 function get_total_trip_weight(p_plan_id in number,
473 p_trip_id in number)
474 return number;
475
476 function get_total_trip_volume(p_plan_id in number,
477 p_trip_id in number)
478 return number;
479
480
481 function get_total_trip_pallets(p_plan_id in number,
482 p_trip_id in number)
483 return number;
484
485
486 function get_total_trip_pieces(p_plan_id in number,
487 p_trip_id in number)
488 return number;
489
490 ---- PLAN related functions -------------
491
492
493 function get_total_direct_tls(p_plan_id in number)
494 return number;
495
496 function get_total_direct_mstop_tls(p_plan_id in number)
497 return number;
498
499 function get_total_tls_in_cms(p_plan_id in number)
500 return number;
501
502 function get_total_excp_in_plan(p_plan_id in number)
503 return number;
504
505 function get_total_excp_in_trip(p_plan_id in number, p_trip_id in number)
509 return number;
506 return number;
507
508 function get_auto_release_trip_count (p_plan_id number, p_mode varchar2)
510
511 function get_released_trip_count (p_plan_id number, p_mode varchar2)
512 return number;
513
514 function get_release_failed_trip_count (p_plan_id number, p_mode varchar2)
515 return number;
516
517 function get_flag_for_rel_trip_count (p_plan_id number, p_mode varchar2)
518 return number;
519
520 function get_not_rel_trip_count (p_plan_id number, p_mode varchar2)
521 return number;
522
523 function get_auto_release_cm_count (p_plan_id number)
524 return number;
525
526 function get_released_cm_count (p_plan_id number)
527 return number;
528
529 function get_rel_failed_cm_count (p_plan_id number)
530 return number;
531
532 function get_flag_for_rel_cm_count (p_plan_id number)
533 return number;
534
535 function get_not_rel_cm_count (p_plan_id number)
536 return number;
537
538 function get_total_excp_in_cm(p_plan_id in number, p_cm_id in number)
539 return number;
540
541 END MST_AGG_PKG;
[Home] [Help]