1 PACKAGE POS_ASN_CREATE_PVT AUTHID CURRENT_USER AS
2 /* $Header: POSVASNS.pls 120.3.12020000.3 2013/02/09 13:54:53 hvutukur ship $*/
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 , p_lot_exp_dt IN DATE DEFAULT NULL -- Added for bug8404937
177 );
178
179 procedure insert_wlpni
180 (p_api_version IN NUMBER
181 , x_return_status OUT NOCOPY VARCHAR2
182 , x_msg_count OUT NOCOPY NUMBER
183 , x_msg_data OUT NOCOPY VARCHAR2
184 , p_po_line_loc_id IN NUMBER
185 , p_license_plate_number IN VARCHAR2
186 , p_LPN_GROUP_ID IN NUMBER
187 , p_PARENT_LICENSE_PLATE_NUMBER IN VARCHAR2
188 );
189
190
191 procedure ValidateSerialRange( p_api_version in number,
192 x_return_status out nocopy varchar2,
193 p_fm_serial_number in varchar2,
194 p_to_serial_number in varchar2,
195 p_quantity in number,
196 p_lot_number in varchar2,
197 p_line_loc_id in number,
198 x_return_code out nocopy varchar2,
199 x_return_msg out nocopy varchar2);
200
201 procedure ValidateLpn( p_api_version in number,
202 x_return_status out nocopy varchar2,
203 p_lpn in varchar2,
204 p_line_loc_id in number,
205 x_return_code out nocopy varchar2,
206 x_return_msg out nocopy varchar2);
207
208 procedure ValidateLot(
209 p_api_version in number,
210 x_return_status out nocopy varchar2,
211 p_lot_number in varchar2,
212 p_line_loc_id in number,
213 p_validation_mode in number,
214
215 p_lot_attribute_category in varchar2,
216 p_c_attributes_tbl in PO_TBL_VARCHAR2000,
217 p_n_attributes_tbl in PO_TBL_NUMBER,
218 p_d_attributes_tbl in PO_TBL_DATE,
219 p_grade_code in varchar2 ,
220 p_origination_date in date ,
221 p_date_code in varchar2,
222 p_status_id in number,
223 p_change_date in date,
224 p_age in number,
225 p_retest_date in date,
226 p_maturity_date in date,
227 p_item_size in number,
228 p_color in varchar2,
229 p_volume in number,
230 p_volume_uom in varchar2,
231 p_place_of_origin in varchar2,
232 p_best_by_date in date,
233 p_length in number,
234 p_length_uom in varchar2,
235 p_recycled_content in number,
236 p_thickness in number,
237 p_thickness_uom in varchar2,
238 p_width in number,
239 p_width_uom in varchar2,
240 p_territory_code in varchar2,
241 p_supplier_lot_number in varchar2,
242 p_vendor_name in varchar2,
243 p_lot_exp_dt in date default null, --Added for bug 8404937
244 x_return_code out nocopy varchar2,
245 x_return_msg out nocopy varchar2,
246 x_is_new_lot out nocopy varchar2);
247
248 procedure findLlsCode(p_line_location_id in number, x_llsCode out nocopy varchar2);
249
250
251 PROCEDURE start_wip_workflow (
252 P_LINE_LOCATION_ID IN NUMBER,
253 P_QUANTITY_T IN NUMBER,
254 P_UNIT_OF_MEASURE_T IN VARCHAR2,
255 P_SHIPPED_DATE IN DATE,
256 P_EXPECTED_RECEIPT_DATE IN DATE,
257 P_PACKING_SLIP_T IN VARCHAR2,
258 P_WAYBILL_AIRBILL_NUM IN VARCHAR2,
259 P_BILL_OF_LADING IN VARCHAR2,
260 P_PACKAGING_CODE IN VARCHAR2,
261 P_NUM_OF_CONTAINERS_T IN NUMBER,
262 p_net_weight IN NUMBER ,
263 p_net_weight_uom IN VARCHAR2 ,
264 p_tar_weight IN NUMBER ,
265 p_tar_weight_uom IN VARCHAR2 ,
266 P_SPECIAL_HANDLING_CODE IN VARCHAR2 ,
267 P_FREIGHT_CARRIER_CODE IN VARCHAR2,
268 P_FREIGHT_TERMS IN VARCHAR2 );
269
270
271
272 FUNCTION get_invoice_qty (
273 p_line_location_id IN NUMBER,
274 p_asn_unit_of_measure IN VARCHAR2,
275 p_item_id IN NUMBER,
276 p_quantity IN NUMBER)
277 RETURN NUMBER;
278
279
280
281 PROCEDURE getShipmentQuantity (
282 p_line_location_id IN NUMBER,
283 p_available_quantity IN OUT nocopy NUMBER,
284 p_tolerable_quantity IN OUT nocopy NUMBER,
285 p_unit_of_measure IN OUT nocopy VARCHAR2);
286
287
288
289 FUNCTION check_wms_install (
290 p_api_version IN NUMBER,
291 x_return_status OUT NOCOPY VARCHAR2)
292 RETURN VARCHAR2;
293
294
295 FUNCTION check_lpnlotserial_valid (
296 p_asn_line_id IN NUMBER,
297 p_lls_code IN VARCHAR2,
298 p_processing_stage IN VARCHAR2)
299 RETURN VARCHAR2;
300
301 /* Inbound Logistics */
302 PROCEDURE validate_ship_from (
303 p_api_version_number IN NUMBER,
304 p_init_msg_list IN VARCHAR2,
305 x_return_status OUT NOCOPY VARCHAR2,
306 p_ship_from_locationId IN NUMBER,
307 p_po_line_id_tbl IN po_tbl_number,
308 p_po_line_loc_id_tbl IN po_tbl_number,
309 x_out_invalid_tbl OUT NOCOPY po_tbl_varchar2000);
310
311 /*Added for bug:13680427*/
312 PROCEDURE get_po_quantity(p_line_location_id IN NUMBER,
313 p_available_quantity IN OUT NOCOPY NUMBER,
314 p_interface_qty_in_po_uom IN OUT NOCOPY NUMBER,
315 p_return_msg out nocopy varchar2,
316 p_return_status OUT NOCOPY VARCHAR2);
317
318 FUNCTION get_po_pending_asn_quantity(p_line_location_id IN NUMBER)
319 RETURN NUMBER;
320
321 FUNCTION get_total_shippedquantity(p_line_location_id IN NUMBER)
322 RETURN NUMBER;
323
324 END POS_ASN_CREATE_PVT;
325
[Home] [Help]