1 PACKAGE INV_Pick_Release_PUB AUTHID CURRENT_USER AS
2 /* $Header: INVPPICS.pls 120.10 2010/05/10 20:41:34 mchemban ship $ */
3
4 --Expired lots custom hook
5 g_pick_expired_lots BOOLEAN := FALSE;
6
7 g_pick_release_caller VARCHAR2(20); --Bug9556463
8
9 -------------------------------------------------------------------------------
10 -- Data Types
11 -------------------------------------------------------------------------------
12 --
13 -- The Release Status table will notify the caller as to which Move Order Lines were
14 -- successfully detailed, which ones were only partially detailed, and which ones
15 -- failed to be processed at all.
16 --
17 -- A table of this type will be returned by the Pick Release API to specify the
18 -- status of the records it attempts to release.
19 --
20 -- The records of this table (of type INV_RELEASE_STATUS_REC_TYPE) will consist of
21 -- the following fields:
22 -- mo_line_id => The Move Order Line ID that this status is for.
23 -- return_status => The standard return status for an API. The return_status
24 -- can also be 'P', which designates that the move order line was only
25 -- partially detailed (but otherwise successful).
26
27 TYPE INV_Release_Status_Rec_Type IS RECORD
28 (
29 mo_line_id NUMBER,
30 detail_rec_count NUMBER,
31 return_status VARCHAR2(1)
32 );
33
34
35 TYPE INV_Release_Status_Tbl_Type IS TABLE OF INV_Release_Status_Rec_Type
36 INDEX BY BINARY_INTEGER;
37
38
39 -------------------------------------------------------------------------------
40 -- Procedures and Functions
41 -------------------------------------------------------------------------------
42 PROCEDURE test_sort(p_trolin_tbl IN OUT NOCOPY INV_Move_Order_Pub.Trolin_Tbl_Type);
43
44 --
45 -- Name
46 -- PROCEDURE Pick_Release
47 --
48 -- Purpose
49 -- Pick releases the move order lines passed in.
50 --
51 -- Input Parameters
52 -- p_api_version_number
53 -- API version number (current version is 1.0)
54 -- p_init_msg_list (optional, default FND_API.G_FALSE)
55 -- Valid values: FND_API.G_FALSE or FND_API.G_TRUE.
56 -- if set to FND_API.G_TRUE
57 -- initialize error message list
58 -- if set to FND_API.G_FALSE - not initialize error
59 -- message list
60 -- p_commit (optional, default FND_API.G_FALSE)
61 -- whether or not to commit the changes to database
62 --
63 -- p_mo_line_tbl
64 -- Table of Move Order Line records to pick release
65 -- p_auto_pick_confirm (optional, default 2)
66 -- Overrides org-level parameter for whether to automatically call
67 -- pick confirm after release
68 -- Valid values: 1 (yes) or 2 (no)
69 -- p_grouping_rule_id
70 -- Overrides org-level and Move Order header-level grouping rule for
71 -- generating pick slip numbers
72 -- p_allow_partial_pick
73 -- TRUE if the pick release process should continue after a line fails to
74 -- be detailed completely. FALSE if the process should stop and roll
75 -- back all changes if a line cannot be fully detailed.
76 -- NOTE: Printing pick slips as the lines are detailed is only supported if
77 -- this parameter is TRUE, since a commit must be done before printing.
78 --
79 -- Output Parameters
80 -- x_return_status
81 -- if the pick release process succeeds, the value is
82 -- fnd_api.g_ret_sts_success;
83 -- if there is an expected error, the value is
84 -- fnd_api.g_ret_sts_error;
85 -- if there is an unexpected error, the value is
86 -- fnd_api.g_ret_sts_unexp_error;
87 -- x_msg_count
88 -- if there is one or more errors, the number of error messages
89 -- in the buffer
90 -- x_msg_data
91 -- if there is one and only one error, the error message
92 -- (See fnd_api package for more details about the above output parameters)
93 -- x_pick_release_status
94 -- This output parameter is a table of records (of type
95 -- INV_Release_Status_Tbl_Type) which specifies the pick release status
96 -- for each move order line that is passed in.
97 --
98 PROCEDURE Pick_Release
99 (
100 p_api_version IN NUMBER
101 ,p_init_msg_list IN VARCHAR2 := NULL
102 ,p_commit IN VARCHAR2 := NULL
103 ,x_return_status OUT NOCOPY VARCHAR2
104 ,x_msg_count OUT NOCOPY NUMBER
105 ,x_msg_data OUT NOCOPY VARCHAR2
106 ,p_mo_line_tbl IN INV_Move_Order_PUB.TROLIN_TBL_TYPE
107 ,p_auto_pick_confirm IN NUMBER := NULL
108 ,p_grouping_rule_id IN NUMBER := NULL
109 ,p_allow_partial_pick IN VARCHAR2 DEFAULT fnd_api.g_true
110 ,x_pick_release_status OUT NOCOPY INV_Release_Status_Tbl_Type
111 ,p_plan_tasks IN BOOLEAN := FALSE
112 ,p_skip_cartonization IN BOOLEAN := FALSE
113 ,p_mo_transact_date IN DATE := fnd_api.g_miss_date
114 );
115
116
117 -- Changes for R12 Planned Crossdocking project.
118 -- Added the following three IN OUT parameters similar to the Crossdock Pegging API,
119 -- WMS_XDock_Pegging_Pub.Planned_Cross_Dock. These parameters are needed in case allocation
120 -- mode uses crossdocking (either Prioritize Inventory or Prioritize Crossdock). The parameters
121 -- are also used to keep data in sync with shipping's code in the WSH_PICK_LIST package. This API
122 -- will be overloaded so any caller to Pick_Release with the old signature does not error out.
123 --
124 -- IN OUT parameters:
125 -- p_wsh_release_table Table of valid demand lines to pick release against.
126 -- Assume that all WDD records are for the same org, p_organization_id.
127 -- API will only process WDD lines with released_status of
128 -- 'R' (Ready to Release) or 'B' (Backordered).
129 -- Shipping should pass in WSH_PR_CRITERIA.release_table in the
130 -- WSH_PICK_LIST.Release_Batch API when pick release is run.
131 -- p_trolin_delivery_ids Table of delivery IDs for transactable demand lines.
132 -- Crossdocked lines needs to keep this table updated so crossdocked
133 -- or split WDD lines can be picked up to autocreate/merge deliveries.
134 -- Shipping should pass in local variable 'l_trolin_delivery_ids' from
135 -- the Release_Batch API. This table has a one to one relationship
136 -- with p_del_detail_id and stores the delivery_id for the corresponding
137 -- delivery_detail_id in p_del_detail_id.
138 -- p_del_detail_id Table of delivery detail IDs for transactable demand lines.
139 -- Crossdocked lines needs to keep this table updated so crossdocked
140 -- or split WDD lines can be picked up to autocreate/merge deliveries.
141 -- Shipping should pass in local variable 'l_del_detail_id' from
142 -- the Release_Batch API. This table has a one to one relationship
143 -- with p_trolin_delivery_ids and stores a list of delivery_detail_id
144 -- values for all of the transactable WDD lines in p_wsh_release_table.
145 PROCEDURE Pick_Release
146 (
147 p_api_version IN NUMBER
148 ,p_init_msg_list IN VARCHAR2 := NULL
149 ,p_commit IN VARCHAR2 := NULL
150 ,x_return_status OUT NOCOPY VARCHAR2
151 ,x_msg_count OUT NOCOPY NUMBER
152 ,x_msg_data OUT NOCOPY VARCHAR2
153 ,p_mo_line_tbl IN INV_Move_Order_PUB.TROLIN_TBL_TYPE
154 ,p_auto_pick_confirm IN NUMBER := NULL
155 ,p_grouping_rule_id IN NUMBER := NULL
156 ,p_allow_partial_pick IN VARCHAR2 DEFAULT fnd_api.g_true
157 ,x_pick_release_status OUT NOCOPY INV_Release_Status_Tbl_Type
158 ,p_plan_tasks IN BOOLEAN := FALSE
159 ,p_skip_cartonization IN BOOLEAN := FALSE
160 ,p_wsh_release_table IN OUT NOCOPY WSH_PR_CRITERIA.relRecTabTyp
161 ,p_trolin_delivery_ids IN OUT NOCOPY WSH_UTIL_CORE.Id_Tab_Type
162 ,p_del_detail_id IN OUT NOCOPY WSH_PICK_LIST.DelDetTabTyp
163 ,p_mo_transact_date IN DATE := NULL
164 ,p_dynamic_replenishment IN VARCHAR2 DEFAULT NULL
165 );
166
167
168 --
169 -- Name
170 -- PROCEDURE Reserve_Unconfirmed_Quantity
171 --
172 -- Purpose
173 -- Transfers a reservation on material which is missing or damaged to an
174 -- appropriate demand source.
175 --
176 -- Input Parameters
177 -- p_missing_quantity
178 -- The quantity to be transferred to a Cycle Count reservation, in the primary
179 -- UOM for the item.
180 -- p_organization_id
181 -- The organization in which the reservation(s) should be created
182 -- p_reservation_id
183 -- The reservation to transfer quantity from (not required if demand source
184 -- parameters are given).
185 -- p_demand_source_type_id
186 -- The demand source type ID for the reservation to be transferred
187 -- p_demand_source_header_id
188 -- The demand source header ID for the reservation to be transferred
189 -- p_demand_source_line_id
190 -- The demand source line ID for the reservation to be transferred
191 -- p_inventory_item_id
192 -- The item which is missing or damaged.
193 -- p_subinventory_code
194 -- The subinventory in which the material is missing or damaged.
195 -- p_locator_id
196 -- The locator in which the material is missing or damaged.
197 -- p_revision
198 -- The revision of the item which is missing or damaged.
199 -- p_lot_number
200 -- The lot number of the item which is missing or damaged.
201 --
202 -- Output Parameters
203 -- x_return_status
204 -- if the pick release process succeeds, the value is
205 -- fnd_api.g_ret_sts_success;
206 -- if there is an expected error, the value is
207 -- fnd_api.g_ret_sts_error;
208 -- if there is an unexpected error, the value is
209 -- fnd_api.g_ret_sts_unexp_error;
210 -- x_msg_count
211 -- if there is one or more errors, the number of error messages
212 -- in the buffer
213 -- x_msg_data
214 -- if there is one and only one error, the error message
215 -- (See fnd_api package for more details about the above output parameters)
216 --
217 -- HW INVCONV added p_missing_quantity2
218 PROCEDURE Reserve_Unconfirmed_Quantity
219 (
220 p_api_version IN NUMBER
221 ,p_init_msg_list IN VARCHAR2 DEFAULT fnd_api.g_false
222 ,p_commit IN VARCHAR2 DEFAULT fnd_api.g_false
223 ,x_return_status OUT NOCOPY VARCHAR2
224 ,x_msg_count OUT NOCOPY NUMBER
225 ,x_msg_data OUT NOCOPY VARCHAR2
226 ,p_missing_quantity IN NUMBER
227 ,p_missing_quantity2 IN NUMBER DEFAULT fnd_api.g_miss_num
228 ,p_reservation_id IN NUMBER DEFAULT fnd_api.g_miss_num
229 ,p_demand_source_header_id IN NUMBER DEFAULT fnd_api.g_miss_num
230 ,p_demand_source_line_id IN NUMBER DEFAULT fnd_api.g_miss_num
231 ,p_organization_id IN NUMBER DEFAULT fnd_api.g_miss_num
232 ,p_inventory_item_id IN NUMBER DEFAULT fnd_api.g_miss_num
233 ,p_subinventory_code IN VARCHAR2 DEFAULT fnd_api.g_miss_char
234 ,p_locator_id IN NUMBER DEFAULT fnd_api.g_miss_num
235 ,p_revision IN VARCHAR2 DEFAULT fnd_api.g_miss_char
236 ,p_lot_number IN VARCHAR2 DEFAULT fnd_api.g_miss_char
237 );
238
239 PROCEDURE assign_pick_slip_number(
240 x_return_status OUT NOCOPY VARCHAR2,
241 x_msg_count OUT NOCOPY NUMBER,
242 x_msg_data OUT NOCOPY VARCHAR2,
243 p_move_order_header_id IN NUMBER DEFAULT 0,
244 p_ps_mode IN VARCHAR2,
245 p_grouping_rule_id IN NUMBER,
246 p_allow_partial_pick IN VARCHAR2);
247
248
249 PROCEDURE call_cartonization (
250 p_api_version IN NUMBER
251 ,p_init_msg_list IN VARCHAR2 := NULL
252 ,p_commit IN VARCHAR2 := NULL
253 ,p_validation_level IN NUMBER
254 ,x_return_status OUT NOCOPY VARCHAR2
255 ,x_msg_count OUT NOCOPY NUMBER
256 ,x_msg_data OUT NOCOPY VARCHAR2
257 ,p_out_bound IN VARCHAR2
258 ,p_org_id IN NUMBER
259 ,p_move_order_header_id IN NUMBER
260 ,p_grouping_rule_id IN NUMBER := NULL
261 ,p_allow_partial_pick IN VARCHAR2 DEFAULT fnd_api.g_true
262 );
263
264 /* Bug 7504490 - Added the procedure Reserve_Unconfqty_lpn. This procedure transfers the reservation
265 of the remaining quantity (task qty-picked qty) to cycle count reservation and ensures that the
266 lpn_id is stamped on the reservation if the task was for an allocated lpn. */
267
268 PROCEDURE Reserve_Unconfqty_lpn
269 (
270 p_api_version IN NUMBER
271 ,p_init_msg_list IN VARCHAR2 DEFAULT fnd_api.g_false
272 ,p_commit IN VARCHAR2 DEFAULT fnd_api.g_false
273 ,x_return_status OUT NOCOPY VARCHAR2
274 ,x_msg_count OUT NOCOPY NUMBER
275 ,x_msg_data OUT NOCOPY VARCHAR2
276 ,x_new_rsv_id OUT NOCOPY NUMBER -- bug 8301348
277 ,p_missing_quantity IN NUMBER
278 ,p_secondary_missing_quantity IN NUMBER DEFAULT NULL /*9251210*/
279 ,p_reservation_id IN NUMBER DEFAULT fnd_api.g_miss_num
280 ,p_demand_source_header_id IN NUMBER DEFAULT fnd_api.g_miss_num
281 ,p_demand_source_line_id IN NUMBER DEFAULT fnd_api.g_miss_num
282 ,p_organization_id IN NUMBER DEFAULT fnd_api.g_miss_num
283 ,p_inventory_item_id IN NUMBER DEFAULT fnd_api.g_miss_num
284 ,p_subinventory_code IN VARCHAR2 DEFAULT fnd_api.g_miss_char
285 ,p_locator_id IN NUMBER DEFAULT fnd_api.g_miss_num
286 ,p_revision IN VARCHAR2 DEFAULT fnd_api.g_miss_char
287 ,p_lot_number IN VARCHAR2 DEFAULT fnd_api.g_miss_char
288 ,p_lpn_id IN NUMBER DEFAULT fnd_api.g_miss_num
289 );
290
291
292
293 END INV_Pick_Release_PUB;