1 PACKAGE BODY inv_ppengine_pvt AS
2 /* $Header: INVVPPEB.pls 120.1.12010000.2 2009/08/03 12:21:15 mitgupta ship $ */
3 --
4 -- File : INVVPPEB.pls
5 -- Content : INV_PPEngine_PVT package body
6 -- Description : Pick / put away engine private API's
7 -- Notes :
8 -- Modified : 30/10/98 ckuenzel created
9 -- 02/08/99 mzeckzer changed
10 -- 04/05/99 bitang changed
11 -- 07/28/99 bitang changed
12 -- 07/31/99 bitang moved some procedures to inv_pp_util_pvt
13 -- packages
14 -- 07/30/03 grao changed
15 --
16 g_pkg_name constant varchar2(30) := 'INV_PPEngine_PVT';
17 --
18 --
19 -- API name : Create_Suggestions
20 -- Type : Private
21 -- Function : Creates pick and/or put away suggestions
22 -- The program will use WMS pick/put rules/strategies
23 -- if Oracle WMS is installed; otherwise, rules in
24 -- mtl_picking_rules will be used.
25 --
26 -- Notes
27 -- 1. Integration with reservations
28 -- If table p_reservations passed by the calling is not empty, the
29 -- engine will detailing based on a combination of the info in the
30 -- move order line (the record that represents detailing request),
31 -- and the info in p_reservations. For example, a sales order line
32 -- can have two reservations, one for revision A in quantity of 10,
33 -- and one for revision B in quantity of 5, and the line quantity
34 -- can be 15; so when the pick release api calls the engine
35 -- p_reservations will have two records of the reservations. So
36 -- if the move order line based on the sales order line does not
37 -- specify a revision, the engine will merge the information from
38 -- move order line and p_reservations to create the input for
39 -- detailing as two records, one for revision A, and one for revision
40 -- B. Please see documentation for the pick release API for more
41 -- details.
42 --
43 -- 2. Serial Number Detailing in Picking
44 -- Currently the serial number detailing is quite simple. If the caller
45 -- gives a range (start, and end) serial numbers in the move order line
46 -- and pass p_suggest_serial as fnd_api.true, the engine will filter
47 -- the locations found from a rule, and suggest unused serial numbers
48 -- in the locator. If p_suggest_serial is passed as fnd_api.g_false
49 -- (default), the engine will not give serial numbers in the output.
50 --
51 -- Input Parameters
52 -- p_api_version_number standard input parameter
53 -- p_init_msg_lst standard input parameter
54 -- p_commit standard input parameter
55 -- p_validation_level standard input parameter
56 -- p_transaction_temp_id equals to the move order line id
57 -- for the detailing request
58 -- p_reservations reservations for the demand source
59 -- as the transaction source
60 -- in the move order line.
61 -- p_suggest_serial whether or not the engine should suggest
62 -- serial numbers in the detailing
63 -- p_quick_pick_flag This flag is used to call quick pick functionality for the Inventory Move
64 -- If the value is 'Y' in Patch set 'J' onwords, Picking rule validation will
65 -- not be called. The Default Value is 'N'
66 --
67 -- Output Parameters
68 -- x_return_status standard output parameters
69 -- x_msg_count standard output parameters
70 -- x_msg_data standard output parameters
71 --
72 -- Version : Current version 1.0
73
74 -- Create_suggestions api is overloaded for Performance bug fix 5264987
75 -- Added a new IN Parameter p_organization_id
76 --
77 -- Bug8757642. Added p_wave_simulation_mode with default vale 'N' for WavePlanning Project.
78 -- This project is available only in for R121 and mainline. To retain dual maintenance INV code changes are made in branchline, however it will not affect any existing flow.
79 PROCEDURE create_suggestions
80 (
81 p_api_version IN NUMBER,
82 p_init_msg_list IN VARCHAR2 DEFAULT fnd_api.g_false,
83 p_commit IN VARCHAR2 DEFAULT fnd_api.g_false,
84 p_validation_level IN NUMBER DEFAULT fnd_api.g_valid_level_none,
85 x_return_status OUT NOCOPY VARCHAR2,
86 x_msg_count OUT NOCOPY NUMBER,
87 x_msg_data OUT NOCOPY VARCHAR2,
88 p_transaction_temp_id IN NUMBER,
89 p_reservations IN inv_reservation_global.mtl_reservation_tbl_type,
90 p_suggest_serial IN VARCHAR2 DEFAULT fnd_api.g_false,
91 p_plan_tasks IN BOOLEAN,
92 p_quick_pick_flag IN VARCHAR2 DEFAULT 'N',
93 p_organization_id IN NUMBER,
94 p_wave_simulation_mode IN VARCHAR2 DEFAULT 'N'
95 ) is
96 l_api_version CONSTANT NUMBER := 1.0;
97 l_api_name CONSTANT VARCHAR2(30) := 'Create_Suggestions';
98 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
99 l_msg_count NUMBER;
100 l_msg_data VARCHAR2(2000);
101 l_wms_installed BOOLEAN;
102 l_org_id NUMBER;
103 l_return_value BOOLEAN := TRUE; -- for Bug #3153166
104
105 -- Rules J Project Variables
106
107 l_current_release_level NUMBER := INV_CONTROL.G_CURRENT_RELEASE_LEVEL;
108 l_j_release_level NUMBER := 110510;
109 l_k_release_level NUMBER := 110511;
110
111 ---
112 BEGIN
113 --
114 -- debugging section
115 -- can be commented out for final code
116 IF inv_pp_debug.is_debug_mode THEN
117 inv_pp_debug.send_message_to_pipe(' '); -- new line
118 inv_pp_debug.send_message_to_pipe
119 ('********************** Pick and Put Away Engine Testing Trace **********************');
120 inv_pp_debug.send_message_to_pipe('enter '||g_pkg_name||'.'||l_api_name);
121 END IF;
122 -- end of debugging section
123 --
124 -- Standard Call to check for call compatibility
125 IF NOT fnd_api.Compatible_API_Call(l_api_version
126 , p_api_version
127 , l_api_name
128 , g_pkg_name) THEN
129 RAISE fnd_api.g_exc_unexpected_error;
130 END IF;
131 --
132 -- Initialize message list if p_init_msg_list is set to true
133 IF fnd_api.to_boolean(p_init_msg_list) THEN
134 fnd_msg_pub.initialize;
135 END IF;
136 --
137 -- Initialisize API return status to access
138 x_return_status := fnd_api.g_ret_sts_success;
139 --
140 -- Detailing starts here
141 --
142 -- Fix 5264987 : Query DB only if p_organization_id is not passed/-9999
143 l_org_id := p_organization_id;
144
145 IF l_org_id = -9999 THEN
146 --get organization id so we can find out if wms is installed;
147 SELECT organization_id
148 INTO l_org_id
149 FROM mtl_txn_request_lines
150 WHERE line_id = p_transaction_temp_id;
151 END IF;
152 -------------------------------------------------------------------
153 -- Important:
154 -- If Oracle WMS is not installed, the program will
155 -- call inv_autodetail to use pick rules in mtl_picking_rules;
156 -- otherwise call wms_engine_pvt to detail using wms rules/strategies
157 -------------------------------------------------------------------
158 --
159 --for Bug#3153166: Performace Issue, Will Check the cache before
160 --calling wms_install.check_install, to check wms installed
161 --or not
162 /*l_wms_installed :=
163 wms_install.check_install(
164 x_return_status => l_return_status,
165 x_msg_count => l_msg_count,
166 x_msg_data => l_msg_data,
167 p_organization_id => l_org_id); */
168 l_return_value := INV_CACHE.set_wms_installed(l_org_id);
169 If NOT l_return_value Then
170 RAISE fnd_api.g_exc_unexpected_error;
171 End If;
172 l_wms_installed := INV_CACHE.wms_installed;
173 --End of Changes for Bug#3153166
174
175 gmi_reservation_util.println('getting crreate_sugg');
176 /* as part of inventory convergence, all allocations will converge
177 * in common WMS create_suggenstions logic after 11.5.11
178 */
179 IF l_wms_installed = FALSE
180 AND (l_current_release_level < l_k_release_level )
181 THEN
182 gmi_reservation_util.println('calling inv crreate_sugg');
183 inv_autodetail.create_suggestions
184 ( p_api_version => 1.0,
185 p_init_msg_list => fnd_api.g_false,
186 p_commit => fnd_api.g_false,
187 p_validation_level => p_validation_level,
188 x_return_status => l_return_status,
189 x_msg_count => l_msg_count,
190 x_msg_data => l_msg_data,
191 p_transaction_temp_id => p_transaction_temp_id,
192 p_reservations => p_reservations,
193 p_suggest_serial => p_suggest_serial
194 );
195 ELSE
196 IF (l_current_release_level >= l_j_release_level ) THEN
197 gmi_reservation_util.println(' calling >=J wms create_sugg, 11511 is included');
198 wms_engine_pvt.create_suggestions
199 ( p_api_version => 1.0,
200 p_init_msg_list => fnd_api.g_false,
201 p_commit => fnd_api.g_false,
202 p_validation_level => p_validation_level,
203 x_return_status => l_return_status,
204 x_msg_count => l_msg_count,
205 x_msg_data => l_msg_data,
206 p_transaction_temp_id => p_transaction_temp_id,
207 p_reservations => p_reservations,
208 p_suggest_serial => p_suggest_serial,
209 p_plan_tasks => p_plan_tasks,
210 p_quick_pick_flag => p_quick_pick_flag,
211 p_wave_simulation_mode => p_wave_simulation_mode
212 );
213 ELSE
214 gmi_reservation_util.println(' calling <J wms create_sugg');
215 wms_engine_pvt.create_suggestions
216 ( p_api_version => 1.0,
217 p_init_msg_list => fnd_api.g_false,
218 p_commit => fnd_api.g_false,
219 p_validation_level => p_validation_level,
220 x_return_status => l_return_status,
221 x_msg_count => l_msg_count,
222 x_msg_data => l_msg_data,
223 p_transaction_temp_id => p_transaction_temp_id,
224 p_reservations => p_reservations,
225 p_suggest_serial => p_suggest_serial,
226 p_plan_tasks => p_plan_tasks
227 );
228 END IF;
229 END IF;
230
231 IF l_return_status = fnd_api.g_ret_sts_error THEN
232 RAISE fnd_api.g_exc_error;
233 END IF;
234 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
235 RAISE fnd_api.g_exc_unexpected_error;
236 END IF;
237 -- Standard check of p_commit
238 IF fnd_api.to_boolean(p_commit) THEN
239 COMMIT;
240 END IF;
241 --
242 -- debugging section
243 -- can be commented out for final code
244 IF inv_pp_debug.is_debug_mode THEN
245 inv_pp_debug.send_message_to_pipe('exit '||g_pkg_name||'.'||l_api_name);
246 END IF;
247 -- end of debugging section
248 --
249 EXCEPTION
250 WHEN fnd_api.g_exc_error THEN
251 x_return_status := fnd_api.g_ret_sts_error;
252 fnd_msg_pub.Count_And_Get
253 ( p_count => x_msg_count
254 ,p_data => x_msg_data);
255 --
256 WHEN fnd_api.g_exc_unexpected_error THEN
257 x_return_status := fnd_api.g_ret_sts_unexp_error;
258 fnd_msg_pub.Count_And_Get
259 ( p_count => x_msg_count
260 ,p_data => x_msg_data);
261 --
262 WHEN OTHERS THEN
263 x_return_status := fnd_api.g_ret_sts_unexp_error;
264 IF fnd_msg_pub.Check_Msg_Level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
265 fnd_msg_pub.Add_Exc_Msg(g_pkg_name, l_api_name);
266 END IF;
267 fnd_msg_pub.count_and_get
268 ( p_count => x_msg_count
269 ,p_data => x_msg_data);
270 END create_suggestions;
271
272 -- Create_suggestions api is overloaded for Performance bug fix 5264987
273 PROCEDURE create_suggestions
274 (
275 p_api_version IN NUMBER,
276 p_init_msg_list IN VARCHAR2 DEFAULT fnd_api.g_false,
277 p_commit IN VARCHAR2 DEFAULT fnd_api.g_false,
278 p_validation_level IN NUMBER DEFAULT fnd_api.g_valid_level_none,
279 x_return_status OUT NOCOPY VARCHAR2,
280 x_msg_count OUT NOCOPY NUMBER,
281 x_msg_data OUT NOCOPY VARCHAR2,
282 p_transaction_temp_id IN NUMBER,
283 p_reservations IN inv_reservation_global.mtl_reservation_tbl_type,
284 p_suggest_serial IN VARCHAR2 DEFAULT fnd_api.g_false,
285 p_plan_tasks IN BOOLEAN,
286 p_quick_pick_flag IN VARCHAR2 DEFAULT 'N',
287 p_wave_simulation_mode IN VARCHAR2 DEFAULT 'N'
288 ) is
289 l_organization_id NUMBER := -9999;
290
291 BEGIN
292 create_suggestions(
293 p_api_version => p_api_version
294 ,p_init_msg_list => p_init_msg_list
295 ,p_commit => p_commit
296 ,p_validation_level => p_validation_level
297 ,x_return_status => x_return_status
298 ,x_msg_count => x_msg_count
299 ,x_msg_data => x_msg_data
300 ,p_transaction_temp_id=> p_transaction_temp_id
301 ,p_reservations => p_reservations
302 ,p_suggest_serial => p_suggest_serial
303 ,p_plan_tasks => p_plan_tasks
304 ,p_quick_pick_flag => p_quick_pick_flag
305 ,p_organization_id => l_organization_id
306 ,p_wave_simulation_mode => p_wave_simulation_mode);
307 END create_suggestions;
308 --
309 end inv_ppengine_pvt;