[Home] [Help]
PACKAGE: APPS.WMS_SHIPPING_INTERFACE_GRP
Source
1 PACKAGE WMS_SHIPPING_INTERFACE_GRP AUTHID CURRENT_USER AS
2 /* $Header: WMSGINTS.pls 120.3 2011/05/13 23:55:48 adyeh ship $ */
3
4 g_action_assign_dlvy_trip CONSTANT VARCHAR2(30) := 'ASSIGN_DLVY_TRIP';
5 g_action_unassign_dlvy_trip CONSTANT VARCHAR2(30) := 'UNASSIGN_DLVY_TRIP';
6
7 g_action_unassign_delivery CONSTANT VARCHAR2(20) := 'UNASSIGN_DELIVERY';
8 g_action_ship_confirm CONSTANT VARCHAR2(20) := 'SHIP_CONFIRM';
9 g_action_edit_shipped_qty CONSTANT VARCHAR2(20) := 'EDIT_SHIPPED_QTY';
10 g_action_validate_sec_qty CONSTANT VARCHAR2(20) := 'VALIDATE_SEC_QTY';
11 g_action_update CONSTANT VARCHAR2(20) := 'UPDATE';
12 g_action_plan_delivery CONSTANT VARCHAR2(30) := 'INCLUDE_DELIVERY_FOR_PLANNING';
13
14
15 g_false CONSTANT VARCHAR2(1) := fnd_api.g_false;
16 g_true CONSTANT VARCHAR2(1) := fnd_api.g_true;
17 g_full_validation CONSTANT NUMBER := fnd_api.g_valid_level_full;
18 TYPE delivery_detail_tbl_rec IS RECORD
19 (DELIVERY_DETAIL_ID NUMBER
20 ,SOURCE_CODE VARCHAR2(30)
21 ,SOURCE_HEADER_ID NUMBER
22 ,SOURCE_LINE_ID NUMBER
23 ,SOURCE_HEADER_NUMBER VARCHAR2(150)
24 ,SOURCE_HEADER_TYPE_ID NUMBER
25 ,SOURCE_HEADER_TYPE_NAME VARCHAR2(240)
26 ,CUST_PO_NUMBER VARCHAR2(50)
27 ,CUSTOMER_ID NUMBER
28 ,SOLD_TO_CONTACT_ID NUMBER
29 ,INVENTORY_ITEM_ID NUMBER
30 ,ITEM_DESCRIPTION VARCHAR2(250)
31 ,SHIP_SET_ID NUMBER
32 ,ARRIVAL_SET_ID NUMBER
33 ,TOP_MODEL_LINE_ID NUMBER
34 ,ATO_LINE_ID NUMBER
35 ,HOLD_CODE VARCHAR2(1)
36 ,SHIP_MODEL_COMPLETE_FLAG VARCHAR2(1)
37 ,HAZARD_CLASS_ID NUMBER
38 ,COUNTRY_OF_ORIGIN VARCHAR2(50)
39 ,CLASSIFICATION VARCHAR2(30)
40 ,SHIP_FROM_LOCATION_ID NUMBER
41 ,ORGANIZATION_ID NUMBER
42 ,SHIP_TO_LOCATION_ID NUMBER
43 ,SHIP_TO_CONTACT_ID NUMBER
44 ,SHIP_TO_SITE_USE_ID NUMBER
45 ,DELIVER_TO_LOCATION_ID NUMBER
46 ,DELIVER_TO_CONTACT_ID NUMBER
47 ,DELIVER_TO_SITE_USE_ID NUMBER
48 ,INTMED_SHIP_TO_LOCATION_ID NUMBER
49 ,INTMED_SHIP_TO_CONTACT_ID NUMBER
50 ,SHIP_TOLERANCE_ABOVE NUMBER
51 ,SHIP_TOLERANCE_BELOW NUMBER
52 ,SRC_REQUESTED_QUANTITY NUMBER
53 ,SRC_REQUESTED_QUANTITY_UOM VARCHAR2(3)
54 ,CANCELLED_QUANTITY NUMBER
55 ,REQUESTED_QUANTITY NUMBER
56 ,REQUESTED_QUANTITY_UOM VARCHAR2(3)
57 ,SHIPPED_QUANTITY NUMBER
58 ,DELIVERED_QUANTITY NUMBER
59 ,QUALITY_CONTROL_QUANTITY NUMBER
60 ,CYCLE_COUNT_QUANTITY NUMBER
61 ,MOVE_ORDER_LINE_ID NUMBER
62 ,SUBINVENTORY VARCHAR2(10)
63 ,REVISION VARCHAR2(3)
64 -- Increased lot size to 80 Char - Mercy Thomas - B4625329
65 ,LOT_NUMBER VARCHAR2(80)
66 ,RELEASED_STATUS VARCHAR2(1)
67 ,CUSTOMER_REQUESTED_LOT_FLAG VARCHAR2(1)
68 ,SERIAL_NUMBER VARCHAR2(30)
69 ,LOCATOR_ID NUMBER
70 ,DATE_REQUESTED DATE
71 ,DATE_SCHEDULED DATE
72 ,MASTER_CONTAINER_ITEM_ID NUMBER
73 ,DETAIL_CONTAINER_ITEM_ID NUMBER
74 ,LOAD_SEQ_NUMBER NUMBER
75 ,SHIP_METHOD_CODE VARCHAR2(30)
76 ,CARRIER_ID NUMBER
77 ,FREIGHT_TERMS_CODE VARCHAR2(30)
78 ,SHIPMENT_PRIORITY_CODE VARCHAR2(30)
79 ,FOB_CODE VARCHAR2(30)
80 ,CUSTOMER_ITEM_ID NUMBER
81 ,DEP_PLAN_REQUIRED_FLAG VARCHAR2(1)
82 ,CUSTOMER_PROD_SEQ VARCHAR2(50)
83 ,CUSTOMER_DOCK_CODE VARCHAR2(50)
84 ,CUSTOMER_PRODUCTION_LINE VARCHAR2(50)
85 ,CUSTOMER_JOB VARCHAR2(50)
86 ,NET_WEIGHT NUMBER
87 ,CUST_MODEL_SERIAL_NUMBER VARCHAR2(50)
88 ,WEIGHT_UOM_CODE VARCHAR2(3)
89 ,VOLUME NUMBER
90 ,VOLUME_UOM_CODE VARCHAR2(3)
91 ,SHIPPING_INSTRUCTIONS VARCHAR2(2000)
92 ,PACKING_INSTRUCTIONS VARCHAR2(2000)
93 ,PROJECT_ID NUMBER
94 ,TASK_ID NUMBER
95 ,ORG_ID NUMBER
96 ,OE_INTERFACED_FLAG VARCHAR2(1)
97 ,MVT_STAT_STATUS VARCHAR2(30)
98 ,TRACKING_NUMBER VARCHAR2(30)
99 ,TRANSACTION_TEMP_ID NUMBER
100 ,TP_ATTRIBUTE_CATEGORY VARCHAR2(240)
101 ,TP_ATTRIBUTE1 VARCHAR2(240)
102 ,TP_ATTRIBUTE2 VARCHAR2(240)
103 ,TP_ATTRIBUTE3 VARCHAR2(240)
104 ,TP_ATTRIBUTE4 VARCHAR2(240)
105 ,TP_ATTRIBUTE5 VARCHAR2(240)
106 ,TP_ATTRIBUTE6 VARCHAR2(240)
107 ,TP_ATTRIBUTE7 VARCHAR2(240)
108 ,TP_ATTRIBUTE8 VARCHAR2(240)
109 ,TP_ATTRIBUTE9 VARCHAR2(240)
110 ,TP_ATTRIBUTE10 VARCHAR2(240)
111 ,TP_ATTRIBUTE11 VARCHAR2(240)
112 ,TP_ATTRIBUTE12 VARCHAR2(240)
113 ,TP_ATTRIBUTE13 VARCHAR2(240)
114 ,TP_ATTRIBUTE14 VARCHAR2(240)
115 ,TP_ATTRIBUTE15 VARCHAR2(240)
116 ,ATTRIBUTE_CATEGORY VARCHAR2(150)
117 ,ATTRIBUTE1 VARCHAR2(150)
118 ,ATTRIBUTE2 VARCHAR2(150)
119 ,ATTRIBUTE3 VARCHAR2(150)
120 ,ATTRIBUTE4 VARCHAR2(150)
121 ,ATTRIBUTE5 VARCHAR2(150)
122 ,ATTRIBUTE6 VARCHAR2(150)
123 ,ATTRIBUTE7 VARCHAR2(150)
124 ,ATTRIBUTE8 VARCHAR2(150)
125 ,ATTRIBUTE9 VARCHAR2(150)
126 ,ATTRIBUTE10 VARCHAR2(150)
127 ,ATTRIBUTE11 VARCHAR2(150)
128 ,ATTRIBUTE12 VARCHAR2(150)
129 ,ATTRIBUTE13 VARCHAR2(150)
130 ,ATTRIBUTE14 VARCHAR2(150)
131 ,ATTRIBUTE15 VARCHAR2(150)
132 ,CREATION_DATE DATE
133 ,CREATED_BY NUMBER
134 ,LAST_UPDATE_DATE DATE
135 ,LAST_UPDATED_BY NUMBER
136 ,LAST_UPDATE_LOGIN NUMBER
137 ,PROGRAM_APPLICATION_ID NUMBER
138 ,PROGRAM_ID NUMBER
139 ,PROGRAM_UPDATE_DATE DATE
140 ,REQUEST_ID NUMBER
141 ,MOVEMENT_ID NUMBER
142 ,SPLIT_FROM_DELIVERY_DETAIL_ID NUMBER
143 ,INV_INTERFACED_FLAG VARCHAR2(1)
144 ,SOURCE_LINE_NUMBER VARCHAR2(150)
145 ,SEAL_CODE VARCHAR2(30)
146 ,MINIMUM_FILL_PERCENT NUMBER
147 ,MAXIMUM_VOLUME NUMBER
148 ,MAXIMUM_LOAD_WEIGHT NUMBER
149 ,MASTER_SERIAL_NUMBER VARCHAR2(30)
150 ,GROSS_WEIGHT NUMBER
151 ,FILL_PERCENT NUMBER
152 ,CONTAINER_NAME VARCHAR2(30)
153 ,CONTAINER_TYPE_CODE VARCHAR2(30)
154 ,CONTAINER_FLAG VARCHAR2(1)
155 ,PREFERRED_GRADE VARCHAR2(4)
156 ,SRC_REQUESTED_QUANTITY2 NUMBER
157 ,SRC_REQUESTED_QUANTITY_UOM2 VARCHAR2(3)
158 ,REQUESTED_QUANTITY2 NUMBER
159 ,SHIPPED_QUANTITY2 NUMBER
160 ,DELIVERED_QUANTITY2 NUMBER
161 ,CANCELLED_QUANTITY2 NUMBER
162 ,QUALITY_CONTROL_QUANTITY2 NUMBER
163 ,CYCLE_COUNT_QUANTITY2 NUMBER
164 ,REQUESTED_QUANTITY_UOM2 VARCHAR2(3)
165 -- Increased lot size to 80 Char - Mercy Thomas - B4625329
166 ,SUBLOT_NUMBER VARCHAR2(80)
167 ,UNIT_PRICE NUMBER
168 ,CURRENCY_CODE VARCHAR2(15)
169 ,UNIT_NUMBER VARCHAR2(30)
170 ,FREIGHT_CLASS_CAT_ID NUMBER
171 ,COMMODITY_CODE_CAT_ID NUMBER
172 ,LPN_ID NUMBER
173 ,INSPECTION_FLAG VARCHAR2(1)
174 ,ORIGINAL_SUBINVENTORY VARCHAR2(10)
175 ,PICKABLE_FLAG VARCHAR2(1)
176 ,TO_SERIAL_NUMBER VARCHAR2(30)
177 ,PICKED_QUANTITY NUMBER
178 ,PICKED_QUANTITY2 NUMBER
179 ,RECEIVED_QUANTITY NUMBER
180 ,RECEIVED_QUANTITY2 NUMBER
181 ,SOURCE_LINE_SET_ID NUMBER
182 ,BATCH_ID NUMBER
183 ,LINE_DIRECTION VARCHAR2(30)
184 ,r_message_appl VARCHAR2(3)
185 ,r_message_code VARCHAR2(30)
186 ,r_message_token VARCHAR2(30)
187 ,r_message_type VARCHAR2(1)
188 ,r_message_text VARCHAR2(2000)
189 ,return_status VARCHAR2(10));
190
191 TYPE g_delivery_detail_tbl IS TABLE OF delivery_detail_tbl_rec INDEX BY BINARY_INTEGER;
192
193 TYPE dlvy_trip_tbl_rec IS RECORD
194 (delivery_id NUMBER
195 ,trip_id NUMBER
196 ,r_message_appl VARCHAR2(3)
197 ,r_message_code VARCHAR2(30)
198 ,r_message_token VARCHAR2(30) --value of the token
199 ,r_message_token_name VARCHAR2(30)
200 ,r_message_type VARCHAR2(1)
201 ,r_message_text VARCHAR2(2000)
202 ,return_status VARCHAR2(10));
203
204 TYPE g_dlvy_trip_tbl IS TABLE OF dlvy_trip_tbl_rec INDEX BY BINARY_INTEGER;
205
206 TYPE serial_number_tbl_rec IS RECORD
207 (inventory_item_id NUMBER
208 ,serial_number VARCHAR2(30)
209 ,current_organization_id NUMBER
210 ,lpn_id NUMBER
211 ,return_status VARCHAR2(10) --Currently not use
212 );
213
214 TYPE g_serial_number_tbl IS TABLE OF serial_number_tbl_rec INDEX BY BINARY_INTEGER;
215
216 PROCEDURE process_delivery_details
217 (p_api_version IN NUMBER,
218 p_init_msg_list IN VARCHAR2 := wms_shipping_interface_grp.g_false,
219 p_commit IN VARCHAR2 := wms_shipping_interface_grp.g_false,
220 p_validation_level IN NUMBER := wms_shipping_interface_grp.g_full_validation,
221 p_action IN VARCHAR2,
222 p_delivery_detail_tbl IN OUT NOCOPY wms_shipping_interface_grp.g_delivery_detail_tbl,
223 x_return_status OUT NOCOPY VARCHAR2,
224 x_msg_count OUT NOCOPY NUMBER,
225 x_msg_data OUT NOCOPY VARCHAR2);
226
227 PROCEDURE process_delivery_trip
228 (p_api_version IN NUMBER
229 ,p_init_msg_list IN VARCHAR2 := wms_shipping_interface_grp.g_false
230 ,p_commit IN VARCHAR2 := wms_shipping_interface_grp.g_false
231 ,p_validation_level IN NUMBER := wms_shipping_interface_grp.g_full_validation
232 ,p_action IN VARCHAR2
233 ,p_dlvy_trip_tbl IN OUT nocopy wms_shipping_interface_grp.g_dlvy_trip_tbl
234 ,x_return_status OUT nocopy VARCHAR2
235 ,x_msg_count OUT nocopy NUMBER
236 ,x_msg_data OUT nocopy VARCHAR2);
237
238 PROCEDURE process_serial_number
239 (p_api_version IN NUMBER
240 ,p_init_msg_list IN VARCHAR2 := wms_shipping_interface_grp.g_false
241 ,p_commit IN VARCHAR2 := wms_shipping_interface_grp.g_false
242 ,p_validation_level IN NUMBER := wms_shipping_interface_grp.g_full_validation
243 ,p_action IN VARCHAR2
244 ,p_serial_number_tbl IN OUT nocopy wms_shipping_interface_grp.g_serial_number_tbl
245 ,x_return_status OUT nocopy VARCHAR2
249 /* Added the following API, which will be called by WSH
246 ,x_msg_count OUT nocopy NUMBER
247 ,x_msg_data OUT nocopy VARCHAR2);
248
250 with p_action as 'INCLUDE_DELIVERY_FOR_PLANNING', when
251 the delivery is not assigned to any trip, to validate whether
252 any LPN associated with this delivery is already loaded to dock door
253 */
254
255 PROCEDURE process_deliveries
256 (p_api_version IN NUMBER
257 ,p_init_msg_list IN VARCHAR2 := Wms_Shipping_Interface_Grp.g_false
258 ,p_commit IN VARCHAR2 := Wms_Shipping_Interface_Grp.g_false
259 ,p_validation_level IN NUMBER := Wms_Shipping_Interface_Grp.g_full_validation
260 ,p_action IN VARCHAR2
261 ,x_dlvy_trip_tbl IN OUT nocopy Wms_Shipping_Interface_Grp.g_dlvy_trip_tbl
262 ,x_return_status OUT nocopy VARCHAR2
263 ,x_msg_count OUT nocopy NUMBER
264 ,x_msg_data OUT nocopy VARCHAR2);
265
266 function wave_line_exists(p_delivery_detail_id in number) return varchar2;
267 procedure split_wave_line(p_orig_delivery_detail_id in number,
268 p_new_delivery_detail_id in number default null,
269 x_return_status out nocopy varchar2,
270 x_msg_count out nocopy number,
271 x_msg_data out nocopy varchar2);
272
273
274
275
276
277 END WMS_SHIPPING_INTERFACE_GRP;