1 PACKAGE WMS_CARTNZN_PUB AUTHID CURRENT_USER AS
2 /* $Header: WMSCRTNS.pls 120.6 2010/02/26 20:38:03 sfulzele ship $*/
3
4
5 -- File : WMSCRTNS.pls
6 -- Content : WMS_CARTNZN_PUB package specification
7 -- Description : WMS cartonization API
8 -- Notes :
9 -- Modified : 09/12/2000 cjandhya created
10
11 -- MOdified : 03/13/2002 cjandhya Added Multilevel Cartonization
12
13 -- API name : cartonize
14 -- Type : group
15 -- Function : populates the cartonization_id, container_item_id columns,
16 -- of rows belonging to a particular move order header id in
17 -- mtl_material_transactions_temp.
18
19 -- Pre-reqs : Those columns won't be populated if the cartonization_id
20 -- for that row is already populated,
21 -- or if values for organization_id, inventory_item_id ,
22 -- primary qunatity, transaction_quantity, transaction_uom,
23 -- trans action_temp_id are not all filled or if there is no
24 -- conversion defined between primary and transaction uoms of
25 -- the item of interest. each item has to be assigned to a
26 -- category of contained_item category set and that category
27 -- should have some container items.
28 -- The lines that can be packed together are identified by the
29 -- carton_grouping_id(MTL_TXN_REQEST_LINES) for the
30 -- move_order_line_id of that line.
31
32
33 -- Parameters :
34 -- p_api_version Standard Input Parameter
35 -- p_init_msg_list Standard Input Parameter
36 -- p_commit Standard Input Parameter
37 -- p_validation_level Standard Input Parameter
38 -- l_out_bound specifies if the call is for outbound process
39 -- org_id organization_id
40 -- l_move_order_header_id header_id for the lines to be cartonized
41
42
43 -- Output Parameters
44 -- x_return_status Standard Output Parameter
45 -- x_msg_count Standard Output Parameter
46 -- x_msg_data Standard Output Parameter
47
48 -- Version
49 -- Currently version is 1.0
50
51
52
53 -- Package constants for different modes in which cartonization is called
54
55 PR_pKG_mode NUMBER := 1;--Pick Release Mode
56 int_bP_pkg_mode NUMBER := 2;--Bulk Pack mode, invoked from interface tables
57 mob_bP_pKG_mode NUMBER := 3;--Bulk Pack mode, invoked from mobile forms
58 prepack_pkg_mode NUMBER := 4;--Prepack mode, invoked by prepack conc prog
59 mfg_pr_pkg_mode NUMBER := 5;--Manufacturing Pick Release Mode
60
61
62 g_org_cartonization_value NUMBER;
63 g_org_cartonize_so_flag VARCHAR2(1) := 'N';
64 g_org_cartonize_mfg_flag VARCHAR2(1) := 'N';
65 g_org_allocate_serial_flag VARCHAR2(1) := 'N';
66 g_default_pick_op_plan_id NUMBER;
67 g_auto_pick_confirm_flag VARCHAR2(1);
68 g_percent_fill_basis VARCHAR2(1) :='W';
69 g_autocreate_delivery_flag VARCHAR2(1);
70 g_cartonize_pick_slip VARCHAR2(1) := 'N'; --WMS High Vol Support
71
72 --Bug 2745834 fix
73 g_wms_pack_hist_seq NUMBER := 1;
74
75 -- start for adding WMS High Vol Support
76 -- Bug 3528061 fix
77 -- Variable storing the allocate_serials_flag for the organization
78 g_allocate_serial_flag VARCHAR2(1) := NULL;
79
80 -- Bug#7168367.This will hold if cartonization is enabled at sublevel or not
81 -- 1 - At org level
82 -- 3 - At sublevel
83 g_sublvlctrl VARCHAR2(1) := '2';
84
85 -- Cartonization package global flags
86 pack_level NUMBER := 0;
87 outbound VARCHAR2(1) := 'N';
88
89 -- Sets the table on which we want to perform the operations insert,
90 -- delete, update etc
91 table_name VARCHAR2(200) := 'mtl_material_transactions_temp';
92
93 TYPE attr_rec IS RECORD
94 ( inventory_item_id NUMBER
95 , gross_weight NUMBER
96 , content_volume NUMBER
97 , gross_weight_uom_code VARCHAR2(3)
98 , content_volume_uom_code VARCHAR2(3)
99 , tare_weight NUMBER
100 , tare_weight_uom_code VARCHAR2(3)
101 );
102
103 TYPE attr_tb IS TABLE OF attr_rec INDEX BY BINARY_INTEGER;
104
105 lpn_attr_table attr_tb;
106 pkg_attr_table attr_tb;
107
108
109
110 --for device integration
111 SUBTYPE mmtt_row IS mtl_material_transactions_temp%ROWTYPE;
112 SUBTYPE wct_row_type IS wms_cartonization_temp%ROWTYPE;
113
114 lpns_generated_tb inv_label.transaction_id_rec_type;
115
116
117
118 TYPE lpn_alloc_flag_rec IS RECORD
119 ( transaction_temp_id mtl_material_transactions_temp.transaction_temp_id%TYPE
120 , lpn_alloc_flag VARCHAR2(1)
121 );
122
123 TYPE lpn_alloc_flag_tb IS TABLE OF lpn_alloc_flag_rec INDEX BY LONG;
124
125 -- TABLE used to store whether transaction_temp_id is fully allocated
126 -- partially allocated, or not allocated
127 t_lpn_alloc_flag_table lpn_alloc_flag_tb;
128
129 -- end for adding WMS High Vol Support
130
131
132 FUNCTION get_lpn_alloc_flag(p_temp_id IN NUMBER) RETURN VARCHAR2;
133
134 PROCEDURE cartonize(
135 p_api_version IN NUMBER,
136 p_init_msg_list IN VARCHAR2 :=fnd_api.g_false,
137 p_commit IN VARCHAR2 :=fnd_api.g_false,
138 p_validation_level IN NUMBER :=fnd_api.g_valid_level_full,
139 x_return_status OUT NOCOPY VARCHAR2,
140 x_msg_count OUT NOCOPY NUMBER,
141 x_msg_data OUT NOCOPY VARCHAR2,
142 p_out_bound IN VARCHAR2 DEFAULT 'Y',
143 p_org_id IN NUMBER,
144 p_move_order_header_id IN NUMBER DEFAULT 0,
145 p_disable_cartonization IN VARCHAR2 DEFAULT 'N',
146 p_transaction_header_id IN NUMBER DEFAULT 0,
147 p_stop_level IN NUMBER DEFAULT -1,
148 p_PACKAGING_mode IN NUMBER DEFAULT 1,
149 p_input_for_bulk IN WMS_BULK_PICK.bulk_input_rec DEFAULT null);
150
151 PROCEDURE UPDATE_MMTT(
152 p_transaction_temp_id IN NUMBER,
153 p_primary_quantity IN NUMBER,
154 p_transaction_quantity IN NUMBER,
155 p_secondary_quantity IN NUMBER DEFAULT NULL, --invconv kkillams
156 p_LPN_string IN VARCHAR2 DEFAULT NULL,
157 p_lpn_id IN NUMBER DEFAULT NULL,
158 p_container_item_id IN NUMBER,
159 p_parent_line_id IN NUMBER := -99999,
160 p_upd_qty_flag IN VARCHAR2 DEFAULT 'Y',
161 x_return_status OUT NOCOPY VARCHAR2,
162 x_msg_count OUT NOCOPY NUMBER,
163 x_msg_data OUT NOCOPY VARCHAR2);
164
165 PROCEDURE INSERT_MMTT(
166 p_transaction_temp_id IN NUMBER,
167 p_primary_quantity IN NUMBER,
168 p_transaction_quantity IN NUMBER,
169 p_secondary_quantity IN NUMBER DEFAULT NULL, --invconv kkillams
170 p_LPN_string IN VARCHAR2 DEFAULT NULL,
171 p_lpn_id IN NUMBER DEFAULT NULL,
172 p_container_item_id IN NUMBER DEFAULT NULL,
173 p_new_txn_hdr_id IN NUMBER DEFAULT NULL,
174 p_new_txn_tmp_id IN NUMBER DEFAULT NULL,
175 p_clpn_id IN NUMBER DEFAULT NULL,
176 p_item_id IN NUMBER DEFAULT NULL,
177 x_return_status OUT NOCOPY VARCHAR2,
178 x_msg_count OUT NOCOPY NUMBER,
179 x_msg_data OUT NOCOPY VARCHAR2);
180
181 PROCEDURE log_event(p_message VARCHAR2);
182
183 PROCEDURE test;
184
185 FUNCTION get_log_flag RETURN VARCHAR2;
186
187 FUNCTION do_cartonization( mohdrid NUMBER,trxhdrid number, outbound VARCHAR2, sublvlctrl VARCHAR2, per_fill VARCHAR2) RETURN NUMBER;
188
189
190 PROCEDURE ins_wct_rows_into_mmtt(
191 p_m_o_h_id IN NUMBER,
192 p_outbound IN VARCHAR2,
193 x_return_status OUT NOCOPY VARCHAR2,
194 x_msg_count OUT NOCOPY NUMBER,
195 x_msg_data OUT NOCOPY VARCHAR2);
196
197 FUNCTION get_lpn_Itemid(P_lpn_id IN NUMBER) return NUMBER;
198
199 FUNCTION get_PACKAGE_Itemid(P_PACKAGE_id IN NUMBER) return NUMBER;
200
201 FUNCTION get_next_package_id RETURN NUMBER;
202
203
204 PROCEDURE get_package_attributes(
205 p_org_id IN NUMBER,
206 p_package_id IN NUMBER,
207 x_inventory_item_id OUT NOCOPY NUMBER,
208 x_gross_weight OUT NOCOPY NUMBER,
209 x_content_volume OUT NOCOPY NUMBER,
210 x_gross_weight_uom_code OUT NOCOPY VARCHAR2,
211 x_content_volume_uom_code OUT NOCOPY VARCHAR2,
212 x_tare_weight OUT NOCOPY NUMBER,
213 x_tare_weight_uom_code OUT NOCOPY VARCHAR2 );
214
215 PROCEDURE get_lpn_attributes(
216 p_lpn_id IN NUMBER,
217 x_inventory_item_id OUT NOCOPY NUMBER,
218 x_gross_weight OUT NOCOPY NUMBER,
219 x_content_volume OUT NOCOPY NUMBER,
220 x_gross_weight_uom_code OUT NOCOPY VARCHAR2,
221 x_content_volume_uom_code OUT NOCOPY VARCHAR2,
222 x_tare_weight OUT NOCOPY NUMBER,
223 x_tare_weight_uom_code OUT NOCOPY VARCHAR2 );
224
225
226 --This procedure inserts records into wms_device_requests table for further
227 -- processing by device integration code.This procedure is called from
228 -- WMSCRTNB.pls and WMSTSKUB.pls
229 PROCEDURE insert_device_request_rec(p_mmtt_row IN mmtt_row);
230
231
232 -- Added for bug 8574452 - FPing 8431970
233 PROCEDURE update_mmtt_qty(
234 p_transaction_temp_id IN NUMBER,
235 p_primary_quantity IN NUMBER,
236 p_transaction_quantity IN NUMBER,
237 x_return_status OUT NOCOPY VARCHAR2,
238 x_msg_count OUT NOCOPY NUMBER,
239 x_msg_data OUT NOCOPY VARCHAR2);
240
241 PROCEDURE cartonize_single_item
242 ( x_return_status OUT NOCOPY VARCHAR2
243 , x_msg_count OUT NOCOPY NUMBER
244 , x_msg_data OUT NOCOPY VARCHAR2
245 , p_out_bound IN VARCHAR2
246 , p_org_id IN NUMBER
247 , p_move_order_header_id IN NUMBER
248 , p_subinventory_name IN VARCHAR2 DEFAULT NULL
249 );
250
251 PROCEDURE cartonize_mixed_item
252 ( x_return_status OUT NOCOPY VARCHAR2
253 , x_msg_count OUT NOCOPY NUMBER
254 , x_msg_data OUT NOCOPY VARCHAR2
255 , p_out_bound IN VARCHAR2
256 , p_org_id IN NUMBER
257 , p_move_order_header_id IN NUMBER
258 , p_transaction_header_id IN NUMBER
259 , p_disable_cartonization IN VARCHAR2 DEFAULT 'N'
260 , p_subinventory_name IN VARCHAR2 DEFAULT NULL
261 , p_stop_level IN NUMBER DEFAULT NULL
262 , p_pack_level IN NUMBER
263 );
264
265
266 PROCEDURE cartonize_pick_slip
267 ( p_org_id IN NUMBER
268 , p_move_order_header_id IN NUMBER
269 , p_subinventory_name IN VARCHAR2
270 , x_return_status OUT NOCOPY VARCHAR2
271 );
272
273
274 PROCEDURE cartonize_customer_logic
275 ( p_org_id IN NUMBER
276 , p_move_order_header_id IN NUMBER
277 , p_subinventory_name IN VARCHAR2
278 , x_return_status OUT NOCOPY VARCHAR2
279 );
280
281
282 PROCEDURE cartonize_default_logic
283 ( p_org_id IN NUMBER
284 , p_move_order_header_id IN NUMBER
285 , p_out_bound IN VARCHAR2
286 , x_return_status OUT NOCOPY VARCHAR2
287 , x_msg_count OUT NOCOPY NUMBER
288 , x_msg_data OUT NOCOPY VARCHAR2
289 );
290
291
292 PROCEDURE insert_ph
293 ( p_move_order_header_id IN NUMBER
294 , p_current_header_id IN NUMBER
295 , x_return_status OUT NOCOPY NUMBER
296 );
297
298
299 PROCEDURE generate_lpns
300 ( p_header_id IN NUMBER
301 , p_organization_id IN NUMBER
302 );
303
304
305 PROCEDURE split_lot_serials (p_organization_id IN NUMBER);
306
307 FUNCTION get_next_header_id RETURN NUMBER;
308
309 FUNCTION get_next_temp_id RETURN NUMBER;
310
311 PROCEDURE insert_ph
312 ( p_orig_header_id IN NUMBER
313 , p_transaction_temp_id IN NUMBER
314 );
315
316
317
318 END WMS_CARTNZN_PUB;
[Home] [Help]