[Home] [Help]
Skip to content
PACKAGE: APPS.WSH_TRIP_CONSOLIDATION
Source
1 PACKAGE WSH_TRIP_CONSOLIDATION AUTHID CURRENT_USER as
2 /* $Header: WSHTRCOS.pls 120.2 2010/08/06 16:06:00 anvarshn 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_client_id IN NUMBER, -- Modified R12.1.1 LSP PROJECT
55 p_ship_from_org_id IN NUMBER,
56 p_customer_id IN VARCHAR2, --RTV changes
57 p_ship_to_location IN VARCHAR2,
58 p_ship_method_code IN VARCHAR2,
59 p_grp_ship_method IN VARCHAR2,
60 p_grp_ship_from IN VARCHAR2,
61 p_max_num_deliveries IN NUMBER DEFAULT 50,
62 x_TotDeliveries OUT NOCOPY NUMBER,
63 x_SuccessDeliv OUT NOCOPY NUMBER,
64 x_Trips OUT NOCOPY NUMBER,
65 x_return_status OUT NOCOPY VARCHAR2
66 );
67
68 PROCEDURE BuildQuery(p_deliv_status IN VARCHAR2,
69 p_pickup_start IN DATE,
70 p_pickup_end IN DATE,
71 p_dropoff_start IN DATE,
72 p_dropoff_end IN DATE,
73 p_client_id IN NUMBER, -- Modified R12.1.1 LSP PROJECT
74 p_ship_from_org_id IN NUMBER,
75 p_customer_id IN VARCHAR2, --RTV changes
76 p_ship_to_location IN NUMBER,
77 p_ship_method_code IN VARCHAR2,
78 x_query OUT NOCOPY VARCHAR2,
79 x_return_status OUT NOCOPY VARCHAR2);
80
81
82 PROCEDURE CreateAssignHashValue(p_grp_ship_from IN VARCHAR2,
83 p_grp_ship_method IN VARCHAR2,
84 x_del_rec IN OUT NOCOPY t_DelivRec,
85 x_HashTable IN OUT NOCOPY HashTable,
86 x_RetSts OUT NOCOPY VARCHAR2,
87 x_UseDeliv OUT NOCOPY VARCHAR2);
88
89
90 FUNCTION FetchDelivery(p_Deliv_ref IN t_Cursor_ref,
91 x_deliv_rec IN OUT NOCOPY t_DelivRec)
92 RETURN BOOLEAN;
93
94
95 PROCEDURE BuildDelivRec(p_DelivRec IN t_DelivRec,
96 x_DelivRec IN OUT NOCOPY WSH_NEW_DELIVERIES_PVT.Delivery_Rec_Type,
97 x_RetSts OUT NOCOPY VARCHAR2);
98
99
100
101 PROCEDURE GroupDelivsIntoTrips( p_DelivGrpRec IN t_DelivGrpRec,
102 x_delOutRec OUT NOCOPY WSH_DELIVERIES_GRP.Delivery_Action_Out_Rec_Type,
103 x_return_status OUT NOCOPY VARCHAR2);
104
105
106 END WSH_TRIP_CONSOLIDATION;