1 PACKAGE WMS_DOCK_APPOINTMENTS_PUB AUTHID CURRENT_USER AS
2 /* $Header: WMSDKAPS.pls 120.2 2007/12/21 10:58:24 shikapoo ship $ */
3
4 /*Added new API get_dock_appointment_range which accepts dock appointment attributes
5 and returns a pl/sql table populated with the list of appointments for the
6 given query criteria
7 */
8 --Record used in get_dock_appointment_range procedure
9 --to hold the appointments fetched from wms_dock_appointments_b
10 TYPE dock_appt_rec_tp IS RECORD
11 ( dock_appointment_id NUMBER
12 , start_time DATE
13 , end_time DATE
14 );
15
16 TYPE dock_appt_tb_tp is TABLE OF dock_appt_rec_tp INDEX BY BINARY_INTEGER;
17
18 TYPE DockApptRecType IS RECORD
19 ( Dock_Name VARCHAR2(40), -- OTM will pass this as 'DOCK-inventory_location_id'
20 Trip_Stop_id NUMBER, -- Stop_id for the Pickup Stop from wsh_trip_stops table
21 Organization_id NUMBER, -- Organization_id of the Pickup Stop
22 Start_Time DATE, -- Appointment Start Date and Time
23 End_Time DATE); -- Appointment End Date and Time
24
25 TYPE DockApptTabType IS TABLE OF DockApptRecType INDEX BY BINARY_INTEGER;
26
27 /*
28 ** -------------------------------------------------------------------------
29 ** Fuction: get_trip_stop
30 ** Description: returns trip_stop that is occupying a dock door at a given time
31 ** Output:
32 ** x_return_status
33 ** return status indicating success, error, unexpected error
34 ** x_msg_count
35 ** number of messages in message list
36 ** x_msg_data
37 ** if the number of messages in message list is 1, contains
38 ** message text
39 ** Input:
40 ** p_dock_locator_id
41 **
42 ** Returns:
43 ** number
44 ** --------------------------------------------------------------------------
45 */
46
47 g_dock_appointments_b_rec wms_dock_appointments_b%ROWTYPE;
48 g_dock_appointments_tl_rec wms_dock_appointments_tl%ROWTYPE;
49
50 FUNCTION get_trip_stop
51 (
52 x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2
53 , x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER
54 , x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
55 , p_dock_locator_id IN NUMBER)
56 RETURN NUMBER;
57
58
59 PROCEDURE update_dock_appointment
60 (
61 x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2
62 , x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER
63 , x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
64 , p_dock_appointments_v_rec IN wms_dock_appointments_v%ROWTYPE
65 );
66
67 PROCEDURE update_rep_appointments
68 (
69 x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
70 x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER,
71 x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
72 p_orig_id IN NUMBER,
73 p_dock_appointments_v_rec IN wms_dock_appointments_v%ROWTYPE
74 );
75
76 PROCEDURE insert_dock_appointment
77 (
78 x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2
79 , x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER
80 , x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
81 , p_dock_appointments_v_rec IN wms_dock_appointments_v%ROWTYPE
82 );
83
84 PROCEDURE insert_rep_dock_appointments
85 (
86 x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2
87 , x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER
88 , x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
89 , p_rep_orig_id IN NUMBER
90 , p_dock_appointments_v_rec IN wms_dock_appointments_v%ROWTYPE
91 );
92
93 PROCEDURE delete_dock_appointment
94 (
95 x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2
96 , x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER
97 , x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
98 , p_dock_appointment_id IN NUMBER
99 );
100
101 PROCEDURE delete_rep_dock_appointment
102 (
103 x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2
104 , x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER
105 , x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
106 , p_rep_orig_id IN NUMBER
107 );
108 PROCEDURE LOCK_ROW
109 (
110 x_dock_appointment_id IN NUMBER,
111 x_carrier_code IN VARCHAR2,
112 x_staging_lane_id IN NUMBER,
113 x_trip_stop IN NUMBER,
114 x_rep_start_date IN DATE,
115 x_rep_end_date IN DATE,
116 x_rep_origin IN NUMBER,
117 x_rep_frequency IN NUMBER,
118 x_appointment_status IN NUMBER,
119 x_appointment_type IN NUMBER,
120 x_dock_id IN NUMBER,
121 x_organization_id IN NUMBER,
122 x_start_time IN DATE,
123 x_end_time IN DATE,
124 x_source_type IN NUMBER,
125 x_source_header_id IN NUMBER,
126 x_source_line_id IN NUMBER,
127 x_subject IN VARCHAR2,
128 x_description IN VARCHAR2
129 );
130 PROCEDURE ADD_LANGUAGE;
131
132 /*-----------------------------------------------------------------------------------*
133 API Name :GET_DOCK_APPOINTMENT_RANGE
134 Type :Public
135 Pre-reqs :None
136 Description :Given appointment attributes and time window
137 fetches a list of dock appointments
138 Output Parameters :
139 x_return_status - Retun Status Indicator
140 1.Success -S
141 2.Error -E
142 3.Unexpected Error -U
143 sets the return status as S(success),E(Error),U(Unexpected Error)
144 x_msg_count - Indicates the no of messages in the message list
145 x_msg_data - Stacked messages text
146 x_dock_appt_list - List of all the appointments selected
147 Input Parameters :
148 p_api_version - Current Version of API
149 p_init_msg_list - Indicates if error message list needs to be initialized or not
150 Valid values are FND_API.G_TRUE and FND_API.G_FALSE
151 p_organization_id - Organization Id
152 p_start_date - Start date and time to query dock appointments
153 p_end_date - End date and time to query dock appointments
154 p_appointment_type - Appointment types
155 1. Inbound
156 2. Outbound
157 3. Others
158 p_supplier_id - Supplier Identifier
159 p_supplier_site_id - Supplier site Identifier
160 p_customer_id - Cutomer Identifier
161 p_customer_site_id - Customer site Identifier
162 p_carrier_code - Carrier code for the shipment
163 p_carrier_id - Carrier id for the shipment
164 p_trip_stop_id - Trip stop id of the shipment
165 p_waybill_number - Waybill number of the delivery/ shipment
166 p_bill_of_lading - Bill of lading of the delivery/shipment
167 p_master_bol - Master Bill of Lading of the delivery/shipment
168
169 *-------------------------------------------------------------------------------------*/
170 PROCEDURE get_dock_appointment_range
171 (
172 x_return_status OUT NOCOPY VARCHAR2
173 , x_msg_count OUT NOCOPY NUMBER
174 , x_msg_data OUT NOCOPY VARCHAR2
175 , x_dock_appt_list OUT NOCOPY WMS_DOCK_APPOINTMENTS_PUB.dock_appt_tb_tp
176 , p_api_version IN NUMBER DEFAULT 1.0
177 , p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE
178 , p_organization_id IN NUMBER
179 , p_start_date IN DATE
180 , p_end_date IN DATE
181 , p_appointment_type IN NUMBER DEFAULT NULL
182 , p_supplier_id IN NUMBER DEFAULT NULL
183 , p_supplier_site_id IN NUMBER DEFAULT NULL
184 , p_customer_id IN NUMBER DEFAULT NULL
185 , p_customer_site_id IN NUMBER DEFAULT NULL
186 , p_carrier_code IN VARCHAR2 DEFAULT NULL
187 , p_carrier_id IN VARCHAR2 DEFAULT NULL
188 , p_trip_stop_id IN NUMBER DEFAULT NULL
189 , p_waybill_number IN VARCHAR2 DEFAULT NULL
190 , p_bill_of_lading IN VARCHAR2 DEFAULT NULL
191 , p_master_bol IN VARCHAR2 DEFAULT NULL
192 );
193
194 PROCEDURE OTM_Dock_Appointment
195 (
196 p_dock_appt_tab IN DockApptTabType,
197 x_return_status OUT NOCOPY VARCHAR2,
198 x_msg_count OUT NOCOPY NUMBER,
199 x_msg_data OUT NOCOPY VARCHAR2
200 );
201
202 END WMS_DOCK_APPOINTMENTS_PUB;
[Home] [Help]