1 PACKAGE POS_ASN_CREATE_PVT AS
2 /* $Header: POSVASNS.pls 120.2 2005/06/29 14:24:07 shgao noship $*/
3
4 /* Inbound Logistics */
5
6 --TYPE t_error_msg_tbl IS TABLE OF VARCHAR2(2000);
7
8
9 PROCEDURE insert_msni (
10 p_api_version IN NUMBER
11 , x_return_status OUT NOCOPY VARCHAR2
12 , x_msg_count OUT NOCOPY NUMBER
13 , x_msg_data OUT NOCOPY VARCHAR2
14 , p_transaction_interface_id IN OUT NOCOPY NUMBER
15 , p_fm_serial_number IN VARCHAR2
16 , p_to_serial_number IN VARCHAR2
17 , p_po_line_loc_id IN NUMBER
18 , p_product_transaction_id IN OUT NOCOPY NUMBER
19 , p_origination_date IN DATE DEFAULT NULL
20 , p_status_id IN NUMBER DEFAULT NULL
21 , p_territory_code IN VARCHAR2 DEFAULT NULL
22 , p_serial_attribute_category IN VARCHAR2 DEFAULT NULL
23 , p_c_attribute1 IN VARCHAR2 DEFAULT NULL
24 , p_c_attribute2 IN VARCHAR2 DEFAULT NULL
25 , p_c_attribute3 IN VARCHAR2 DEFAULT NULL
26 , p_c_attribute4 IN VARCHAR2 DEFAULT NULL
27 , p_c_attribute5 IN VARCHAR2 DEFAULT NULL
28 , p_c_attribute6 IN VARCHAR2 DEFAULT NULL
29 , p_c_attribute7 IN VARCHAR2 DEFAULT NULL
30 , p_c_attribute8 IN VARCHAR2 DEFAULT NULL
31 , p_c_attribute9 IN VARCHAR2 DEFAULT NULL
32 , p_c_attribute10 IN VARCHAR2 DEFAULT NULL
33 , p_c_attribute11 IN VARCHAR2 DEFAULT NULL
34 , p_c_attribute12 IN VARCHAR2 DEFAULT NULL
35 , p_c_attribute13 IN VARCHAR2 DEFAULT NULL
36 , p_c_attribute14 IN VARCHAR2 DEFAULT NULL
37 , p_c_attribute15 IN VARCHAR2 DEFAULT NULL
38 , p_c_attribute16 IN VARCHAR2 DEFAULT NULL
39 , p_c_attribute17 IN VARCHAR2 DEFAULT NULL
40 , p_c_attribute18 IN VARCHAR2 DEFAULT NULL
41 , p_c_attribute19 IN VARCHAR2 DEFAULT NULL
42 , p_c_attribute20 IN VARCHAR2 DEFAULT NULL
43 , p_d_attribute1 IN DATE DEFAULT NULL
44 , p_d_attribute2 IN DATE DEFAULT NULL
45 , p_d_attribute3 IN DATE DEFAULT NULL
46 , p_d_attribute4 IN DATE DEFAULT NULL
47 , p_d_attribute5 IN DATE DEFAULT NULL
48 , p_d_attribute6 IN DATE DEFAULT NULL
49 , p_d_attribute7 IN DATE DEFAULT NULL
50 , p_d_attribute8 IN DATE DEFAULT NULL
51 , p_d_attribute9 IN DATE DEFAULT NULL
52 , p_d_attribute10 IN DATE DEFAULT NULL
53 , p_n_attribute1 IN NUMBER DEFAULT NULL
54 , p_n_attribute2 IN NUMBER DEFAULT NULL
55 , p_n_attribute3 IN NUMBER DEFAULT NULL
56 , p_n_attribute4 IN NUMBER DEFAULT NULL
57 , p_n_attribute5 IN NUMBER DEFAULT NULL
58 , p_n_attribute6 IN NUMBER DEFAULT NULL
59 , p_n_attribute7 IN NUMBER DEFAULT NULL
60 , p_n_attribute8 IN NUMBER DEFAULT NULL
61 , p_n_attribute9 IN NUMBER DEFAULT NULL
62 , p_n_attribute10 IN NUMBER DEFAULT NULL
63 , p_attribute_category IN VARCHAR2 DEFAULT NULL
64 , p_attribute1 IN VARCHAR2 DEFAULT NULL
65 , p_attribute2 IN VARCHAR2 DEFAULT NULL
66 , p_attribute3 IN VARCHAR2 DEFAULT NULL
67 , p_attribute4 IN VARCHAR2 DEFAULT NULL
68 , p_attribute5 IN VARCHAR2 DEFAULT NULL
69 , p_attribute6 IN VARCHAR2 DEFAULT NULL
70 , p_attribute7 IN VARCHAR2 DEFAULT NULL
71 , p_attribute8 IN VARCHAR2 DEFAULT NULL
72 , p_attribute9 IN VARCHAR2 DEFAULT NULL
73 , p_attribute10 IN VARCHAR2 DEFAULT NULL
74 , p_attribute11 IN VARCHAR2 DEFAULT NULL
75 , p_attribute12 IN VARCHAR2 DEFAULT NULL
76 , p_attribute13 IN VARCHAR2 DEFAULT NULL
77 , p_attribute14 IN VARCHAR2 DEFAULT NULL
78 , p_attribute15 IN VARCHAR2 DEFAULT NULL
79 );
80
81 PROCEDURE insert_mtli (
82 p_api_version IN NUMBER
83 , x_return_status OUT NOCOPY VARCHAR2
84 , x_msg_count OUT NOCOPY NUMBER
85 , x_msg_data OUT NOCOPY VARCHAR2
86 , p_transaction_interface_id IN OUT NOCOPY NUMBER
87 , p_lot_number IN VARCHAR2
88 , p_transaction_quantity IN NUMBER
89 , p_transaction_uom IN VARCHAR2
90 , p_po_line_loc_id IN NUMBER
91 , x_serial_transaction_temp_id OUT NOCOPY NUMBER
92 , p_product_transaction_id IN OUT NOCOPY NUMBER
93 , p_vendor_id IN NUMBER DEFAULT NULL
94 , p_grade_code IN VARCHAR2 DEFAULT NULL
95 , p_origination_date IN DATE DEFAULT NULL
96 , p_date_code IN VARCHAR2 DEFAULT NULL
97 , p_status_id IN NUMBER DEFAULT NULL
98 , p_change_date IN DATE DEFAULT NULL
99 , p_age IN NUMBER DEFAULT NULL
100 , p_retest_date IN DATE DEFAULT NULL
101 , p_maturity_date IN DATE DEFAULT NULL
102 , p_item_size IN NUMBER DEFAULT NULL
103 , p_color IN VARCHAR2 DEFAULT NULL
104 , p_volume IN NUMBER DEFAULT NULL
105 , p_volume_uom IN VARCHAR2 DEFAULT NULL
106 , p_place_of_origin IN VARCHAR2 DEFAULT NULL
107 , p_best_by_date IN DATE DEFAULT NULL
108 , p_length IN NUMBER DEFAULT NULL
109 , p_length_uom IN VARCHAR2 DEFAULT NULL
110 , p_recycled_content IN NUMBER DEFAULT NULL
111 , p_thickness IN NUMBER DEFAULT NULL
112 , p_thickness_uom IN VARCHAR2 DEFAULT NULL
113 , p_width IN NUMBER DEFAULT NULL
114 , p_width_uom IN VARCHAR2 DEFAULT NULL
115 , p_curl_wrinkle_fold IN VARCHAR2 DEFAULT NULL
116 , p_supplier_lot_number IN VARCHAR2 DEFAULT NULL
117 , p_territory_code IN VARCHAR2 DEFAULT NULL
118 , p_vendor_name IN VARCHAR2 DEFAULT NULL
119 , p_lot_attribute_category IN VARCHAR2 DEFAULT NULL
120 , p_c_attribute1 IN VARCHAR2 DEFAULT NULL
121 , p_c_attribute2 IN VARCHAR2 DEFAULT NULL
122 , p_c_attribute3 IN VARCHAR2 DEFAULT NULL
123 , p_c_attribute4 IN VARCHAR2 DEFAULT NULL
124 , p_c_attribute5 IN VARCHAR2 DEFAULT NULL
125 , p_c_attribute6 IN VARCHAR2 DEFAULT NULL
126 , p_c_attribute7 IN VARCHAR2 DEFAULT NULL
127 , p_c_attribute8 IN VARCHAR2 DEFAULT NULL
128 , p_c_attribute9 IN VARCHAR2 DEFAULT NULL
129 , p_c_attribute10 IN VARCHAR2 DEFAULT NULL
130 , p_c_attribute11 IN VARCHAR2 DEFAULT NULL
131 , p_c_attribute12 IN VARCHAR2 DEFAULT NULL
132 , p_c_attribute13 IN VARCHAR2 DEFAULT NULL
133 , p_c_attribute14 IN VARCHAR2 DEFAULT NULL
134 , p_c_attribute15 IN VARCHAR2 DEFAULT NULL
135 , p_c_attribute16 IN VARCHAR2 DEFAULT NULL
136 , p_c_attribute17 IN VARCHAR2 DEFAULT NULL
137 , p_c_attribute18 IN VARCHAR2 DEFAULT NULL
138 , p_c_attribute19 IN VARCHAR2 DEFAULT NULL
139 , p_c_attribute20 IN VARCHAR2 DEFAULT NULL
140 , p_d_attribute1 IN DATE DEFAULT NULL
141 , p_d_attribute2 IN DATE DEFAULT NULL
142 , p_d_attribute3 IN DATE DEFAULT NULL
143 , p_d_attribute4 IN DATE DEFAULT NULL
144 , p_d_attribute5 IN DATE DEFAULT NULL
145 , p_d_attribute6 IN DATE DEFAULT NULL
146 , p_d_attribute7 IN DATE DEFAULT NULL
147 , p_d_attribute8 IN DATE DEFAULT NULL
148 , p_d_attribute9 IN DATE DEFAULT NULL
149 , p_d_attribute10 IN DATE DEFAULT NULL
150 , p_n_attribute1 IN NUMBER DEFAULT NULL
151 , p_n_attribute2 IN NUMBER DEFAULT NULL
152 , p_n_attribute3 IN NUMBER DEFAULT NULL
153 , p_n_attribute4 IN NUMBER DEFAULT NULL
154 , p_n_attribute5 IN NUMBER DEFAULT NULL
155 , p_n_attribute6 IN NUMBER DEFAULT NULL
156 , p_n_attribute7 IN NUMBER DEFAULT NULL
157 , p_n_attribute8 IN NUMBER DEFAULT NULL
158 , p_n_attribute9 IN NUMBER DEFAULT NULL
159 , p_n_attribute10 IN NUMBER DEFAULT NULL
160 , p_attribute_category IN VARCHAR2 DEFAULT NULL
161 , p_attribute1 IN VARCHAR2 DEFAULT NULL
162 , p_attribute2 IN VARCHAR2 DEFAULT NULL
163 , p_attribute3 IN VARCHAR2 DEFAULT NULL
164 , p_attribute4 IN VARCHAR2 DEFAULT NULL
165 , p_attribute5 IN VARCHAR2 DEFAULT NULL
166 , p_attribute6 IN VARCHAR2 DEFAULT NULL
167 , p_attribute7 IN VARCHAR2 DEFAULT NULL
168 , p_attribute8 IN VARCHAR2 DEFAULT NULL
169 , p_attribute9 IN VARCHAR2 DEFAULT NULL
170 , p_attribute10 IN VARCHAR2 DEFAULT NULL
171 , p_attribute11 IN VARCHAR2 DEFAULT NULL
172 , p_attribute12 IN VARCHAR2 DEFAULT NULL
173 , p_attribute13 IN VARCHAR2 DEFAULT NULL
174 , p_attribute14 IN VARCHAR2 DEFAULT NULL
175 , p_attribute15 IN VARCHAR2 DEFAULT NULL
176 );
177
178 procedure insert_wlpni
179 (p_api_version IN NUMBER
180 , x_return_status OUT NOCOPY VARCHAR2
181 , x_msg_count OUT NOCOPY NUMBER
182 , x_msg_data OUT NOCOPY VARCHAR2
183 , p_po_line_loc_id IN NUMBER
184 , p_license_plate_number IN VARCHAR2
185 , p_LPN_GROUP_ID IN NUMBER
186 , p_PARENT_LICENSE_PLATE_NUMBER IN VARCHAR2
187 );
188
189
190 procedure ValidateSerialRange( p_api_version in number,
191 x_return_status out nocopy varchar2,
192 p_fm_serial_number in varchar2,
193 p_to_serial_number in varchar2,
194 p_quantity in number,
195 p_lot_number in varchar2,
196 p_line_loc_id in number,
197 x_return_code out nocopy varchar2,
198 x_return_msg out nocopy varchar2);
199
200 procedure ValidateLpn( p_api_version in number,
201 x_return_status out nocopy varchar2,
202 p_lpn in varchar2,
203 p_line_loc_id in number,
204 x_return_code out nocopy varchar2,
205 x_return_msg out nocopy varchar2);
206
207 procedure ValidateLot(
208 p_api_version in number,
209 x_return_status out nocopy varchar2,
210 p_lot_number in varchar2,
211 p_line_loc_id in number,
212 p_validation_mode in number,
213
214 p_lot_attribute_category in varchar2,
215 p_c_attributes_tbl in PO_TBL_VARCHAR2000,
216 p_n_attributes_tbl in PO_TBL_NUMBER,
217 p_d_attributes_tbl in PO_TBL_DATE,
218 p_grade_code in varchar2 ,
219 p_origination_date in date ,
220 p_date_code in varchar2,
221 p_status_id in number,
222 p_change_date in date,
223 p_age in number,
224 p_retest_date in date,
225 p_maturity_date in date,
226 p_item_size in number,
227 p_color in varchar2,
228 p_volume in number,
229 p_volume_uom in varchar2,
230 p_place_of_origin in varchar2,
231 p_best_by_date in date,
232 p_length in number,
233 p_length_uom in varchar2,
234 p_recycled_content in number,
235 p_thickness in number,
236 p_thickness_uom in varchar2,
237 p_width in number,
238 p_width_uom in varchar2,
239 p_territory_code in varchar2,
240 p_supplier_lot_number in varchar2,
241 p_vendor_name in varchar2,
242
243 x_return_code out nocopy varchar2,
244 x_return_msg out nocopy varchar2,
245 x_is_new_lot out nocopy varchar2);
246
247 procedure findLlsCode(p_line_location_id in number, x_llsCode out nocopy varchar2);
248
249
250 PROCEDURE start_wip_workflow (
251 P_LINE_LOCATION_ID IN NUMBER,
252 P_QUANTITY_T IN NUMBER,
253 P_UNIT_OF_MEASURE_T IN VARCHAR2,
254 P_SHIPPED_DATE IN DATE,
255 P_EXPECTED_RECEIPT_DATE IN DATE,
256 P_PACKING_SLIP_T IN VARCHAR2,
257 P_WAYBILL_AIRBILL_NUM IN VARCHAR2,
258 P_BILL_OF_LADING IN VARCHAR2,
259 P_PACKAGING_CODE IN VARCHAR2,
260 P_NUM_OF_CONTAINERS_T IN NUMBER,
261 p_net_weight IN NUMBER ,
262 p_net_weight_uom IN VARCHAR2 ,
263 p_tar_weight IN NUMBER ,
264 p_tar_weight_uom IN VARCHAR2 ,
265 P_SPECIAL_HANDLING_CODE IN VARCHAR2 ,
266 P_FREIGHT_CARRIER_CODE IN VARCHAR2,
267 P_FREIGHT_TERMS IN VARCHAR2 );
268
269
270
271 FUNCTION get_invoice_qty (
272 p_line_location_id IN NUMBER,
273 p_asn_unit_of_measure IN VARCHAR2,
274 p_item_id IN NUMBER,
275 p_quantity IN NUMBER)
276 RETURN NUMBER;
277
278
279
280 PROCEDURE getShipmentQuantity (
281 p_line_location_id IN NUMBER,
282 p_available_quantity IN OUT nocopy NUMBER,
283 p_tolerable_quantity IN OUT nocopy NUMBER,
284 p_unit_of_measure IN OUT nocopy VARCHAR2);
285
286
287
288 FUNCTION check_wms_install (
289 p_api_version IN NUMBER,
290 x_return_status OUT NOCOPY VARCHAR2)
291 RETURN VARCHAR2;
292
293
294 FUNCTION check_lpnlotserial_valid (
295 p_asn_line_id IN NUMBER,
296 p_lls_code IN VARCHAR2,
297 p_processing_stage IN VARCHAR2)
298 RETURN VARCHAR2;
299
300 /* Inbound Logistics */
301 PROCEDURE validate_ship_from (
302 p_api_version_number IN NUMBER,
303 p_init_msg_list IN VARCHAR2,
304 x_return_status OUT NOCOPY VARCHAR2,
305 p_ship_from_locationId IN NUMBER,
306 p_po_line_id_tbl IN po_tbl_number,
307 p_po_line_loc_id_tbl IN po_tbl_number,
308 x_out_invalid_tbl OUT NOCOPY po_tbl_varchar2000);
309
310 END POS_ASN_CREATE_PVT;
311