1 PACKAGE BODY inv_ppengine_pvt AS
2 /* $Header: INVVPPEB.pls 120.1 2006/06/28 11:26:05 bradha noship $ */
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 PROCEDURE create_suggestions
78 (
79 p_api_version IN NUMBER,
80 p_init_msg_list IN VARCHAR2 DEFAULT fnd_api.g_false,
81 p_commit IN VARCHAR2 DEFAULT fnd_api.g_false,
82 p_validation_level IN NUMBER DEFAULT fnd_api.g_valid_level_none,
83 x_return_status OUT NOCOPY VARCHAR2,
84 x_msg_count OUT NOCOPY NUMBER,
85 x_msg_data OUT NOCOPY VARCHAR2,
86 p_transaction_temp_id IN NUMBER,
87 p_reservations IN inv_reservation_global.mtl_reservation_tbl_type,
88 p_suggest_serial IN VARCHAR2 DEFAULT fnd_api.g_false,
89 p_plan_tasks IN BOOLEAN,
90 p_quick_pick_flag IN VARCHAR2 DEFAULT 'N',
91 p_organization_id IN NUMBER
92 ) is
93 l_api_version CONSTANT NUMBER := 1.0;
94 l_api_name CONSTANT VARCHAR2(30) := 'Create_Suggestions';
95 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
96 l_msg_count NUMBER;
97 l_msg_data VARCHAR2(2000);
98 l_wms_installed BOOLEAN;
99 l_org_id NUMBER;
100 l_return_value BOOLEAN := TRUE; -- for Bug #3153166
101
102 -- Rules J Project Variables
103
104 l_current_release_level NUMBER := INV_CONTROL.G_CURRENT_RELEASE_LEVEL;
105 l_j_release_level NUMBER := 110510;
106 l_k_release_level NUMBER := 110511;
107
108 ---
109 BEGIN
110 --
111 -- debugging section
112 -- can be commented out for final code
113 IF inv_pp_debug.is_debug_mode THEN
114 inv_pp_debug.send_message_to_pipe(' '); -- new line
115 inv_pp_debug.send_message_to_pipe
116 ('********************** Pick and Put Away Engine Testing Trace **********************');
117 inv_pp_debug.send_message_to_pipe('enter '||g_pkg_name||'.'||l_api_name);
118 END IF;
119 -- end of debugging section
120 --
121 -- Standard Call to check for call compatibility
122 IF NOT fnd_api.Compatible_API_Call(l_api_version
123 , p_api_version
124 , l_api_name
125 , g_pkg_name) THEN
126 RAISE fnd_api.g_exc_unexpected_error;
127 END IF;
128 --
129 -- Initialize message list if p_init_msg_list is set to true
130 IF fnd_api.to_boolean(p_init_msg_list) THEN
131 fnd_msg_pub.initialize;
132 END IF;
133 --
134 -- Initialisize API return status to access
135 x_return_status := fnd_api.g_ret_sts_success;
136 --
137 -- Detailing starts here
138 --
139 -- Fix 5264987 : Query DB only if p_organization_id is not passed/-9999
140 l_org_id := p_organization_id;
141
142 IF l_org_id = -9999 THEN
143 --get organization id so we can find out if wms is installed;
144 SELECT organization_id
145 INTO l_org_id
146 FROM mtl_txn_request_lines
147 WHERE line_id = p_transaction_temp_id;
148 END IF;
149 -------------------------------------------------------------------
150 -- Important:
151 -- If Oracle WMS is not installed, the program will
152 -- call inv_autodetail to use pick rules in mtl_picking_rules;
153 -- otherwise call wms_engine_pvt to detail using wms rules/strategies
154 -------------------------------------------------------------------
155 --
156 --for Bug#3153166: Performace Issue, Will Check the cache before
157 --calling wms_install.check_install, to check wms installed
158 --or not
159 /*l_wms_installed :=
160 wms_install.check_install(
161 x_return_status => l_return_status,
162 x_msg_count => l_msg_count,
163 x_msg_data => l_msg_data,
164 p_organization_id => l_org_id); */
165 l_return_value := INV_CACHE.set_wms_installed(l_org_id);
166 If NOT l_return_value Then
167 RAISE fnd_api.g_exc_unexpected_error;
168 End If;
169 l_wms_installed := INV_CACHE.wms_installed;
170 --End of Changes for Bug#3153166
171
172 gmi_reservation_util.println('getting crreate_sugg');
173 /* as part of inventory convergence, all allocations will converge
174 * in common WMS create_suggenstions logic after 11.5.11
175 */
176 IF l_wms_installed = FALSE
177 AND (l_current_release_level < l_k_release_level )
178 THEN
179 gmi_reservation_util.println('calling inv crreate_sugg');
180 inv_autodetail.create_suggestions
181 ( p_api_version => 1.0,
182 p_init_msg_list => fnd_api.g_false,
183 p_commit => fnd_api.g_false,
184 p_validation_level => p_validation_level,
185 x_return_status => l_return_status,
186 x_msg_count => l_msg_count,
187 x_msg_data => l_msg_data,
188 p_transaction_temp_id => p_transaction_temp_id,
189 p_reservations => p_reservations,
190 p_suggest_serial => p_suggest_serial
191 );
192 ELSE
193 IF (l_current_release_level >= l_j_release_level ) THEN
194 gmi_reservation_util.println(' calling >=J wms create_sugg, 11511 is included');
195 wms_engine_pvt.create_suggestions
196 ( p_api_version => 1.0,
197 p_init_msg_list => fnd_api.g_false,
198 p_commit => fnd_api.g_false,
199 p_validation_level => p_validation_level,
200 x_return_status => l_return_status,
201 x_msg_count => l_msg_count,
202 x_msg_data => l_msg_data,
203 p_transaction_temp_id => p_transaction_temp_id,
204 p_reservations => p_reservations,
205 p_suggest_serial => p_suggest_serial,
206 p_plan_tasks => p_plan_tasks,
207 p_quick_pick_flag => p_quick_pick_flag
208 );
209 ELSE
210 gmi_reservation_util.println(' calling <J wms create_sugg');
211 wms_engine_pvt.create_suggestions
212 ( p_api_version => 1.0,
213 p_init_msg_list => fnd_api.g_false,
214 p_commit => fnd_api.g_false,
215 p_validation_level => p_validation_level,
216 x_return_status => l_return_status,
217 x_msg_count => l_msg_count,
218 x_msg_data => l_msg_data,
219 p_transaction_temp_id => p_transaction_temp_id,
220 p_reservations => p_reservations,
221 p_suggest_serial => p_suggest_serial,
222 p_plan_tasks => p_plan_tasks
223 );
224 END IF;
225 END IF;
226
227 IF l_return_status = fnd_api.g_ret_sts_error THEN
228 RAISE fnd_api.g_exc_error;
229 END IF;
230 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
231 RAISE fnd_api.g_exc_unexpected_error;
232 END IF;
233 -- Standard check of p_commit
234 IF fnd_api.to_boolean(p_commit) THEN
235 COMMIT;
236 END IF;
237 --
238 -- debugging section
239 -- can be commented out for final code
240 IF inv_pp_debug.is_debug_mode THEN
241 inv_pp_debug.send_message_to_pipe('exit '||g_pkg_name||'.'||l_api_name);
242 END IF;
243 -- end of debugging section
244 --
245 EXCEPTION
246 WHEN fnd_api.g_exc_error THEN
247 x_return_status := fnd_api.g_ret_sts_error;
248 fnd_msg_pub.Count_And_Get
249 ( p_count => x_msg_count
250 ,p_data => x_msg_data);
251 --
252 WHEN fnd_api.g_exc_unexpected_error THEN
253 x_return_status := fnd_api.g_ret_sts_unexp_error;
254 fnd_msg_pub.Count_And_Get
255 ( p_count => x_msg_count
256 ,p_data => x_msg_data);
257 --
258 WHEN OTHERS THEN
259 x_return_status := fnd_api.g_ret_sts_unexp_error;
260 IF fnd_msg_pub.Check_Msg_Level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
261 fnd_msg_pub.Add_Exc_Msg(g_pkg_name, l_api_name);
262 END IF;
263 fnd_msg_pub.count_and_get
264 ( p_count => x_msg_count
265 ,p_data => x_msg_data);
266 END create_suggestions;
267
268 -- Create_suggestions api is overloaded for Performance bug fix 5264987
269 PROCEDURE create_suggestions
270 (
271 p_api_version IN NUMBER,
272 p_init_msg_list IN VARCHAR2 DEFAULT fnd_api.g_false,
273 p_commit IN VARCHAR2 DEFAULT fnd_api.g_false,
274 p_validation_level IN NUMBER DEFAULT fnd_api.g_valid_level_none,
275 x_return_status OUT NOCOPY VARCHAR2,
276 x_msg_count OUT NOCOPY NUMBER,
277 x_msg_data OUT NOCOPY VARCHAR2,
278 p_transaction_temp_id IN NUMBER,
279 p_reservations IN inv_reservation_global.mtl_reservation_tbl_type,
280 p_suggest_serial IN VARCHAR2 DEFAULT fnd_api.g_false,
281 p_plan_tasks IN BOOLEAN,
282 p_quick_pick_flag IN VARCHAR2 DEFAULT 'N'
283 ) is
284 l_organization_id NUMBER := -9999;
285
286 BEGIN
287 create_suggestions(
288 p_api_version => p_api_version
289 ,p_init_msg_list => p_init_msg_list
290 ,p_commit => p_commit
291 ,p_validation_level => p_validation_level
292 ,x_return_status => x_return_status
293 ,x_msg_count => x_msg_count
294 ,x_msg_data => x_msg_data
295 ,p_transaction_temp_id=> p_transaction_temp_id
296 ,p_reservations => p_reservations
297 ,p_suggest_serial => p_suggest_serial
298 ,p_plan_tasks => p_plan_tasks
299 ,p_quick_pick_flag => p_quick_pick_flag
300 ,p_organization_id => l_organization_id);
301 END create_suggestions;
302 --
303 end inv_ppengine_pvt;