[Home] [Help]
PACKAGE: APPS.WSH_TRIP_CONSOLIDATION
Source
1 PACKAGE WSH_TRIP_CONSOLIDATION as
2 /* $Header: WSHTRCOS.pls 120.0.12000000.1 2007/01/16 05:51:33 appldev ship $ */
3
4 --
5 G_PKG_NAME CONSTANT VARCHAR2(100) := 'WSH_TRIP_CONSOLIDATION';
6 --
7 TYPE t_DelivRec IS RECORD (
8 DELIVERY_ID NUMBER,
9 ORGANIZATION_ID NUMBER,
10 STATUS_CODE VARCHAR2(2),
11 PLANNED_FLAG VARCHAR2(1),
12 NAME VARCHAR2(30),
13 INITIAL_PICKUP_DATE DATE,
14 INITIAL_PICKUP_LOCATION_ID NUMBER,
15 ULTIMATE_DROPOFF_LOCATION_ID NUMBER,
16 ULTIMATE_DROPOFF_DATE DATE,
17 CUSTOMER_ID NUMBER,
18 INTMED_SHIP_TO_LOCATION_ID NUMBER,
19 SHIP_METHOD_CODE VARCHAR2(30),
20 DELIVERY_TYPE VARCHAR2(30),
21 CARRIER_ID NUMBER,
22 SERVICE_LEVEL VARCHAR2(30),
23 MODE_OF_TRANSPORT VARCHAR2(30),
24 SHIPMENT_DIRECTION VARCHAR2(30),
25 PARTY_ID NUMBER,
26 SHIPPING_CONTROL VARCHAR2(30),
27 IGNORE_FOR_PLANNING VARCHAR2(1),
28 HASH_VALUE NUMBER
29 );
30 --
31 TYPE t_HashRec IS RECORD (HashString VARCHAR2(1000));
32 --
33 TYPE t_DelivGrpRec IS RECORD (
34 deliv_IDTab WSH_UTIL_CORE.ID_TAB_TYPE,
35 max_delivs NUMBER);
36 --
37 TYPE t_Cursor_ref IS REF CURSOR;
38
39 TYPE HashTable IS TABLE OF t_HashRec INDEX BY BINARY_INTEGER;
40 TYPE DelivTable IS TABLE OF t_DelivRec INDEX BY BINARY_INTEGER;
41 --
42 g_HashBase NUMBER := 1;
43 g_HashSize NUMBER := POWER(2, 25);
44 g_BindVarTab WSH_UTIL_CORE.tbl_varchar;
45 g_SuccDelivs NUMBER := 0;
46 g_Trips NUMBER := 0;
47 --
48 PROCEDURE Create_Consolidated_Trips(
49 p_deliv_status IN VARCHAR2,
50 p_pickup_start IN DATE,
51 p_pickup_end IN DATE,
52 p_dropoff_start IN DATE,
53 p_dropoff_end IN DATE,
54 p_ship_from_org_id IN NUMBER,
55 p_customer_id IN NUMBER,
56 p_ship_to_location IN VARCHAR2,
57 p_ship_method_code IN VARCHAR2,
58 p_grp_ship_method IN VARCHAR2,
59 p_grp_ship_from IN VARCHAR2,
60 p_max_num_deliveries IN NUMBER DEFAULT 50,
61 x_TotDeliveries OUT NOCOPY NUMBER,
62 x_SuccessDeliv OUT NOCOPY NUMBER,
63 x_Trips OUT NOCOPY NUMBER,
64 x_return_status OUT NOCOPY VARCHAR2
65 );
66
67 PROCEDURE BuildQuery(p_deliv_status IN VARCHAR2,
68 p_pickup_start IN DATE,
69 p_pickup_end IN DATE,
70 p_dropoff_start IN DATE,
71 p_dropoff_end IN DATE,
72 p_ship_from_org_id IN NUMBER,
73 p_customer_id IN NUMBER,
74 p_ship_to_location IN NUMBER,
75 p_ship_method_code IN VARCHAR2,
76 x_query OUT NOCOPY VARCHAR2,
77 x_return_status OUT NOCOPY VARCHAR2);
78
79
80 PROCEDURE CreateAssignHashValue(p_grp_ship_from IN VARCHAR2,
81 p_grp_ship_method IN VARCHAR2,
82 x_del_rec IN OUT NOCOPY t_DelivRec,
83 x_HashTable IN OUT NOCOPY HashTable,
84 x_RetSts OUT NOCOPY VARCHAR2,
85 x_UseDeliv OUT NOCOPY VARCHAR2);
86
87
88 FUNCTION FetchDelivery(p_Deliv_ref IN t_Cursor_ref,
89 x_deliv_rec IN OUT NOCOPY t_DelivRec)
90 RETURN BOOLEAN;
91
92
93 PROCEDURE BuildDelivRec(p_DelivRec IN t_DelivRec,
94 x_DelivRec IN OUT NOCOPY WSH_NEW_DELIVERIES_PVT.Delivery_Rec_Type,
95 x_RetSts OUT NOCOPY VARCHAR2);
96
97
98
99 PROCEDURE GroupDelivsIntoTrips( p_DelivGrpRec IN t_DelivGrpRec,
100 x_delOutRec OUT NOCOPY WSH_DELIVERIES_GRP.Delivery_Action_Out_Rec_Type,
101 x_return_status OUT NOCOPY VARCHAR2);
102
103
104 END WSH_TRIP_CONSOLIDATION;