1 PACKAGE inv_transfer_order_pvt AS
2 /* $Header: INVVTROS.pls 120.1.12010000.1 2008/07/24 01:53:04 appldev ship $ */
3
4 -- Global for reference type
5
6 g_ref_type_kanban CONSTANT NUMBER := 1;
7 g_ref_type_minmax CONSTANT NUMBER := 2;
8 g_ref_type_internal_req CONSTANT NUMBER := 3;
9
10 /*Patchset J feature constants added for Healthcare Project*/
11 G_REF_TYPE_REPL_COUNT CONSTANT NUMBER := 10;
12 G_REF_TYPE_REORD_POINT CONSTANT NUMBER := 11;
13
14 -- Added for G-I Merge
15 G_WMS_I_OR_ABOVE BOOLEAN;
16
17 -- Start of Comments
18 -- API name Process_Transfer_Order
19 -- Type Private
20 -- Function
21 --
22 -- Pre-reqs
23 --
24 -- Parameters
25 --
26 -- Version Current version = 1.0
27 -- Initial version = 1.0
28 --
29 -- Notes
30 --
31 -- End of Comments
32
33 PROCEDURE increment_max_line_number;
34
35 PROCEDURE reset_max_line_number;
36
37 FUNCTION get_next_header_id(p_organization_id NUMBER := NULL)
38 RETURN NUMBER;
39
40 FUNCTION unique_order(p_organization_id IN NUMBER, p_request_number IN VARCHAR2)
41 RETURN BOOLEAN;
42
43 FUNCTION unique_line(
44 p_organization_id IN NUMBER
45 , p_header_id IN NUMBER
46 , p_line_number IN NUMBER)
47 RETURN BOOLEAN;
48
49 /*Procedure Get_Reservations(
50 x_return_status OUT VARCHAR2,
51 x_msg_count OUT NUMBER,
52 x_msg_data OUT VARCHAR2,
53 p_source_header_id IN NUMBER,
54 p_source_line_id IN NUMBER,
55 p_source_delivery_id IN NUMBER,
56 p_organization_id IN NUMBER,
57 p_inventory_item_id IN NUMBER,
58 p_subinventory_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
59 p_locator_id IN NUMBER := FND_API.G_MISS_NUM,
60 p_revision IN VARCHAR2 := FND_API.G_MISS_CHAR,
61 p_lot_number IN VARCHAR2 := FND_API.G_MISS_CHAR,
62 p_serial_number IN VARCHAR2 := FND_API.G_MISS_CHAR,
63 x_mtl_reservation_tbl OUT INV_RESERVATION_GLOBAL.MTL_RESERVATION_TBL_TYPE
64 );
65 */
66
67 PROCEDURE process_transfer_order(
68 p_api_version_number IN NUMBER
69 , p_init_msg_list IN VARCHAR2 := fnd_api.g_false
70 , p_commit IN VARCHAR2 := fnd_api.g_false
71 , p_validation_level IN NUMBER := fnd_api.g_valid_level_full
72 , p_control_rec IN inv_globals.control_rec_type := inv_globals.g_miss_control_rec
73 , x_return_status OUT NOCOPY VARCHAR2
74 , x_msg_count OUT NOCOPY NUMBER
75 , x_msg_data OUT NOCOPY VARCHAR2
76 , p_trohdr_rec IN inv_move_order_pub.trohdr_rec_type := inv_move_order_pub.g_miss_trohdr_rec
77 , p_trohdr_val_rec IN inv_move_order_pub.trohdr_val_rec_type := inv_move_order_pub.g_miss_trohdr_val_rec
78 , p_old_trohdr_rec IN inv_move_order_pub.trohdr_rec_type := inv_move_order_pub.g_miss_trohdr_rec
79 , p_trolin_tbl IN inv_move_order_pub.trolin_tbl_type := inv_move_order_pub.g_miss_trolin_tbl
80 , p_trolin_val_tbl IN inv_move_order_pub.trolin_val_tbl_type := inv_move_order_pub.g_miss_trolin_val_tbl
81 , p_old_trolin_tbl IN inv_move_order_pub.trolin_tbl_type := inv_move_order_pub.g_miss_trolin_tbl
82 , x_trohdr_rec IN OUT NOCOPY inv_move_order_pub.trohdr_rec_type
83 , x_trolin_tbl IN OUT NOCOPY inv_move_order_pub.trolin_tbl_type
84 , p_delete_mmtt IN VARCHAR2 DEFAULT 'YES' --Added bug3524130
85 );
86
87 -- Start of Comments
88 -- API name Lock_Transfer_Order
89 -- Type Private
90 -- Function
91 --
92 -- Pre-reqs
93 --
94 -- Parameters
95 --
96 -- Version Current version = 1.0
97 -- Initial version = 1.0
98 --
99 -- Notes
100 --
101 -- End of Comments
102
103 PROCEDURE lock_transfer_order(
104 p_api_version_number IN NUMBER
105 , p_init_msg_list IN VARCHAR2 := fnd_api.g_false
106 , x_return_status OUT NOCOPY VARCHAR2
107 , x_msg_count OUT NOCOPY NUMBER
108 , x_msg_data OUT NOCOPY VARCHAR2
109 , p_trohdr_rec IN inv_move_order_pub.trohdr_rec_type := inv_move_order_pub.g_miss_trohdr_rec
110 , p_trolin_tbl IN inv_move_order_pub.trolin_tbl_type := inv_move_order_pub.g_miss_trolin_tbl
111 , x_trohdr_rec IN OUT NOCOPY inv_move_order_pub.trohdr_rec_type
112 , x_trolin_tbl IN OUT NOCOPY inv_move_order_pub.trolin_tbl_type
113 );
114
115 -- Start of Comments
116 -- API name Get_Transfer_Order
117 -- Type Private
118 -- Function
119 --
120 -- Pre-reqs
121 --
122 -- Parameters
123 --
124 -- Version Current version = 1.0
125 -- Initial version = 1.0
126 --
127 -- Notes
128 --
129 -- End of Comments
130
131 PROCEDURE get_transfer_order(
132 p_api_version_number IN NUMBER
133 , p_init_msg_list IN VARCHAR2 := fnd_api.g_false
134 , x_return_status OUT NOCOPY VARCHAR2
135 , x_msg_count OUT NOCOPY NUMBER
136 , x_msg_data OUT NOCOPY VARCHAR2
137 , p_header_id IN NUMBER
138 , x_trohdr_rec OUT NOCOPY inv_move_order_pub.trohdr_rec_type
139 , x_trolin_tbl OUT NOCOPY inv_move_order_pub.trolin_tbl_type
140 );
141
142 FUNCTION validate_from_subinventory(
143 p_from_subinventory_code IN VARCHAR2
144 , p_organization_id IN NUMBER
145 , p_inventory_item_id IN NUMBER
146 , p_transaction_type_id IN NUMBER
147 , p_restrict_subinventories_code IN NUMBER
148 )
149 RETURN BOOLEAN;
150
151 g_from_sub inv_validate.sub; -- this is used to store the from sub object
152 -- so that it can be used for to sub validation
153
154 FUNCTION validate_to_subinventory(
155 p_to_subinventory_code IN VARCHAR2
156 , p_organization_id IN NUMBER
157 , p_inventory_item_id IN NUMBER
158 , p_transaction_type_id IN NUMBER
159 , p_restrict_subinventories_code IN NUMBER
160 , p_asset_item IN VARCHAR2
161 , p_from_sub_asset IN NUMBER
162 )
163 RETURN BOOLEAN;
164
165 --Update_Txn_Source_Line
166 --
167 -- This procedure updates the move order line indicated by p_line_id
168 -- with a new transaction source line id (p_new_source_line_id).
169 -- It also updates all of the allocation lines with the new source line id.
170 -- This procedure is called from Shipping when the delivery detail is split
171 -- after pick release has occurred, but before pick confirm.
172 PROCEDURE update_txn_source_line(p_line_id IN NUMBER, p_new_source_line_id IN NUMBER);
173
174 -- Name
175 -- PROCEDURE Finalize_Pick_Confirm
176 --
177 -- Purpose
178 -- This procedure is a call back procedure to be called from transaction
179 -- processor after it successfully transact the move order line detail
180 -- to do update move order line, update shipping and update reservation
181 -- Input parameters
182 -- p_transaction_temp_id - move order line detail id
183 -- p_transaction_id - transaction id for each move order line detail
184 -- Output Parameters
185 -- x_return_status - fnd_api.g_ret_sts_error, fnd_api.g_ret_sts_success,
186 -- fnd_api.g_ret_unexp_error
187 -- x_msg-count - number of error messages in the buffer
188 -- x_msg_data - error messages
189 --
190 PROCEDURE finalize_pick_confirm(
191 p_init_msg_list IN VARCHAR2 := fnd_api.g_false
192 , x_return_status OUT NOCOPY VARCHAR2
193 , x_msg_count OUT NOCOPY NUMBER
194 , x_msg_data OUT NOCOPY VARCHAR2
195 , p_transaction_temp_id IN NUMBER
196 , p_transaction_id IN NUMBER
197 ,p_xfr_transaction_id IN NUMBER DEFAULT NULL
198
199 );
200 --Bug 2684668
201 -- Bug 2640757: Fill Kill Enhancement
202 PROCEDURE kill_move_order(
203 x_return_status OUT NOCOPY VARCHAR2
204 , x_msg_count OUT NOCOPY NUMBER
205 , x_msg_data OUT NOCOPY VARCHAR2
206 , p_transaction_header_id NUMBER);
207
208 -- Bug 1620576
209 -- This procedure is called from inltpu to delete the table
210 -- mo_picked_quantity_tbl;
211 -- This procedure is called everytime mmtt records are deleted in
212 -- inltpu.
213 PROCEDURE clear_picked_quantity;
214
215 -- This procedure deletes any unstaged reservations for a sales order if
216 -- there are only staged or shipped line delivery detail lines for that order
217 PROCEDURE clean_reservations(
218 p_source_line_id IN NUMBER
219 , x_return_status OUT NOCOPY VARCHAR2
220 , x_msg_count OUT NOCOPY NUMBER
221 , x_msg_data OUT NOCOPY VARCHAR2);
222
223 -- This procedure is called to update the mtl_transaction_lots_temp table
224 -- before the mmtt record gets updated
225 PROCEDURE update_lots_temp(
226 x_return_status OUT NOCOPY VARCHAR2
227 , x_msg_data OUT NOCOPY VARCHAR2
228 , x_msg_count OUT NOCOPY NUMBER
229 , p_operation VARCHAR2
230 , p_item_id NUMBER
231 , p_org_id NUMBER
232 , p_trx_temp_id NUMBER
233 , p_cancel_qty NUMBER
234 , p_trx_uom VARCHAR2
235 , p_primary_uom VARCHAR2
236 , p_last_updated_by NUMBER
237 , p_last_update_date DATE
238 , p_creation_date DATE
239 , p_created_by NUMBER
240 );
241
242 --Procedure called to update mtl_serial_numbers_temp table
243 --when the sales order quantity changes.
244 -- Bug 2195303
245 PROCEDURE update_serial_temp(
246 x_return_status OUT NOCOPY VARCHAR2
247 , x_msg_data OUT NOCOPY VARCHAR2
248 , x_msg_count OUT NOCOPY NUMBER
249 , p_operation VARCHAR2
250 , p_trx_temp_id NUMBER
251 , p_cancel_qty NUMBER
252 );
253
254 --As part of fix for bug 2867490, we need to create the following procedure
255 --This procedure does the needful to adjust the serial numbers in MSNT
256 --in case of overpicking. It could either update/delete MSNT range
257 --or delete the single serial number from MSNT
258 --INPUT parameters are
259 --p_transaction_temp_id (if lot+serial then serial_txn_temp_id)
260 --p_qty is the picked quantity corresponding to txn_temp_id
261
262 PROCEDURE adjust_serial_numbers_in_MSNT(
263 p_transaction_temp_id IN NUMBER,
264 p_qty IN NUMBER);
265
266 END inv_transfer_order_pvt;