1 PACKAGE BODY WMS_Cycle_PVT AS
2 /* $Header: WMSCYCCB.pls 120.9 2011/12/05 11:25:29 raminoch ship $ */
3
4 -- Global constant holding the package name
5 G_PKG_NAME CONSTANT VARCHAR2(30) := 'WMS_Cycle_PVT';
6
7 PROCEDURE print_debug(p_err_msg VARCHAR2)
8 IS
9 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
10 BEGIN
11 inv_mobile_helper_functions.tracelog
12 (p_err_msg => p_err_msg,
13 p_module => 'WMS_Cycle_PVT',
14 p_level => 4);
15
16 -- dbms_output.put_line(p_err_msg);
17 END print_debug;
18
19 --Added NOCOPY hint to x_return_status,x_msg_count,x_msg_data to comply
20 --with GSCC File.Sql.39 standard Bug:4410902
21
22 /* Bug 7504490 - Modified the procedure. Added the parameter p_pn_id and p_revision
23 to create CC entries for an LPN when a short pick was for an allocated lpn */
24 PROCEDURE Create_Unscheduled_Counts
25 ( p_api_version IN NUMBER ,
26 p_init_msg_list IN VARCHAR2 := fnd_api.g_false ,
27 p_commit IN VARCHAR2 := fnd_api.g_false ,
28 x_return_status OUT NOCOPY VARCHAR2 ,
29 x_msg_count OUT NOCOPY NUMBER ,
30 x_msg_data OUT NOCOPY VARCHAR2 ,
31 p_organization_id IN NUMBER ,
32 p_subinventory IN VARCHAR2 ,
33 p_locator_id IN NUMBER ,
34 p_inventory_item_id IN NUMBER ,
35 p_lpn_id IN NUMBER ,
36 p_revision IN VARCHAR2,
37 p_cycle_count_header_id IN NUMBER DEFAULT NULL -- For bug # 9751256
38 )
39 -- BUG#2867331 added inventory_item_id
40 IS
41 l_api_name CONSTANT VARCHAR2(30) := 'Create_Unscheduled_Counts';
42 l_api_version CONSTANT NUMBER := 1.0;
43 l_org INV_Validate.ORG;
44 l_sub INV_Validate.SUB;
45 l_locator INV_Validate.LOCATOR;
46 l_result NUMBER;
47 l_cycle_count_schedule_id NUMBER;
48 l_cycle_count_header_id NUMBER;
49 l_unscheduled_count_entry NUMBER;
50 l_req_id NUMBER;
51 l_zero_count_flag NUMBER ; --Bug 5236299
52
53 /* Bug 7504490 - Added the following variables for Calling the API*/
54 l_api_version_int NUMBER := 0.9;
55 l_interface_rec MTL_CCEOI_VAR_PVT.INV_CCEOI_TYPE ;
56 l_interface_id_list MTL_CCEOI_VAR_PVT.INV_CCEOI_ID_TABLE_TYPE ;
57 l_errorcode NUMBER ;
58 l_count NUMBER := 0; --Bug 9874049
59
60 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
61 BEGIN
62 -- Standard Start of API savepoint
63 SAVEPOINT Create_Unscheduled_Counts_PVT;
64 -- Standard call to check for call compatibility.
65 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
66 p_api_version ,
67 l_api_name ,
68 G_PKG_NAME )
69 THEN
70 FND_MESSAGE.SET_NAME('WMS', 'WMS_CONT_INCOMPATIBLE_API_CALL');
71 FND_MSG_PUB.ADD;
72 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
73 END IF;
74 -- Initialize message list if p_init_msg_list is set to TRUE.
75 IF FND_API.to_Boolean( p_init_msg_list ) THEN
76 FND_MSG_PUB.initialize;
77 END IF;
78 -- Initialize API return status to success
79 x_return_status := FND_API.G_RET_STS_SUCCESS;
80
81 -- API body
82 /* Validate all inputs */
83
84 IF (l_debug = 1) THEN
85 print_debug('Calling Create_Unscheduled_Counts_PVT');
86 print_debug('Organization ID: ' || p_organization_id);
87 print_debug('Subinventory: ' || p_subinventory);
88 print_debug('Locator ID: ' || p_locator_id);
89 print_debug('Inventory Item ID: ' || p_inventory_item_id);
90 print_debug('Allocated LPN Id: ' || p_lpn_id);
91 print_debug('Opp Cycle Count Header Id: ' || p_cycle_count_header_id); -- For bug # 9751256
92 END IF;
93 /* Validate Organization ID */
94 l_org.organization_id := p_organization_id;
95 l_result := INV_Validate.Organization(l_org);
96 IF (l_result = INV_Validate.F) THEN
97 FND_MESSAGE.SET_NAME('WMS', 'WMS_CONT_INVALID_ORG');
98 FND_MSG_PUB.ADD;
99 RAISE FND_API.G_EXC_ERROR;
100 END IF;
101
102 /* Validate Subinventory */
103 IF (p_subinventory IS NOT NULL) THEN
104 l_sub.secondary_inventory_name := p_subinventory;
105 l_result := INV_Validate.subinventory(l_sub, l_org);
106 IF (l_result = INV_Validate.F) THEN
107 FND_MESSAGE.SET_NAME('WMS', 'WMS_CONT_INVALID_SUB');
108 FND_MSG_PUB.ADD;
109 RAISE FND_API.G_EXC_ERROR;
110 END IF;
111 END IF;
112
113 /* Validate Locator */
114 IF (p_subinventory IS NOT NULL) THEN
115 IF (l_sub.locator_type IN (2,3)) THEN
116 IF (p_locator_id IS NULL) THEN
117 FND_MESSAGE.SET_NAME('WMS', 'WMS_CONT_MISS_REQ_LOC');
118 FND_MSG_PUB.ADD;
119 RAISE FND_API.G_EXC_ERROR;
120 END IF;
121 l_locator.inventory_location_id := p_locator_id;
122 l_result := INV_Validate.validateLocator(l_locator, l_org, l_sub);
123 IF (l_result = INV_Validate.F) THEN
124 FND_MESSAGE.SET_NAME('WMS', 'WMS_CONT_INVALID_LOC');
125 FND_MSG_PUB.ADD;
126 RAISE FND_API.G_EXC_ERROR;
127 END IF;
128 END IF;
129 END IF;
130
131 /* Validate the default cycle count header */
132 IF (p_cycle_count_header_id IS NOT NULL) THEN -- For bug # 9751256
133 l_cycle_count_header_id := p_cycle_count_header_id;
134 ELSE
135 IF (l_org.default_cyc_count_header_id IS NULL) THEN
136 FND_MESSAGE.SET_NAME('INV', 'INV_CCEOI_INVALID_HEADER');
137 FND_MSG_PUB.ADD;
138 RAISE FND_API.G_EXC_ERROR;
139 ELSE
140 l_cycle_count_header_id := l_org.default_cyc_count_header_id;
141 END IF;
142 END IF;
143
144 /* Check that unscheduled counts are allowed */
145 SELECT unscheduled_count_entry
146 INTO l_unscheduled_count_entry
147 FROM mtl_cycle_count_headers
148 WHERE cycle_count_header_id = l_cycle_count_header_id
149 AND organization_id = p_organization_id;
150 IF (l_unscheduled_count_entry = 2) THEN
151 FND_MESSAGE.SET_NAME('INV', 'INV_CCEOI_NO_UNSCHED_COUNTS');
152 FND_MSG_PUB.ADD;
153 RAISE FND_API.G_EXC_ERROR;
154 END IF;
155 IF (l_debug = 1) THEN
156 print_debug('Finished Validations');
157 END IF;
158
159 /* End of input validation */
160
161 -- Get a new cycle count schedule ID
162 SELECT mtl_cc_schedule_requests_s.NEXTVAL
163 INTO l_cycle_count_schedule_id
164 FROM dual;
165 IF (l_debug = 1) THEN
166 print_debug('CC Schedule ID: ' || l_cycle_count_schedule_id);
167 END IF;
168
169 /* Bug 5236299 - Selecting the zero count flag for the cycle count */
170
171 SELECT NVL(zero_count_flag,2)
172 INTO l_zero_count_flag
173 FROM mtl_cycle_count_headers
174 WHERE cycle_count_header_id = l_cycle_count_header_id
175 AND organization_id = p_organization_id;
176
177 IF (l_debug = 1) THEN
178 print_debug('Zero Count Flag: ' || l_zero_count_flag);
179 END IF;
180
181 /* End of fix for Bug 5236299 */
182
183 /* Bug 7504490 - Checking if the p_lpn_id parameter is NULL. If it is null, call
184 the cycle count generate program to create entries. If it is not null, call the
185 API process_lpn_countrequest of MTL_CCEOI_ACTION_PUB to create the entry for the LPN */
186
187 IF p_lpn_id IS NULL THEN
188 IF (l_debug = 1) THEN
189 print_debug('p_lpn_id is null');
190 END IF;
191
192 -- Insert a new record into MTL_CC_SCHEDULE_REQUESTS
193 IF (l_debug = 1) THEN
194 print_debug('Inserting into table MTL_CC_SCHEDULE_REQUESTS');
195 END IF;
196 INSERT INTO MTL_CC_SCHEDULE_REQUESTS
197 (cycle_count_schedule_id, last_update_date, last_updated_by,
198 creation_date, created_by, cycle_count_header_id,
199 request_source_type, zero_count_flag, schedule_date,
200 schedule_status, subinventory, locator_id,
201 inventory_item_id, revision) -- BUG #2867331 --Bug8537999 Including Revision
202 VALUES
203 (l_cycle_count_schedule_id, SYSDATE, FND_GLOBAL.USER_ID,
204 SYSDATE, FND_GLOBAL.USER_ID, l_cycle_count_header_id,
205 2, l_zero_count_flag, SYSDATE, 1, --Bug 5236299- inserting the value of l_zero_count_flag
206 p_subinventory, p_locator_id,
207 p_inventory_item_id, p_revision); --BUG#2867331 --Bug8537999 Including Revision
208
209 -- We need to commit this insert first before
210 -- we can call the concurrent programs
211 COMMIT;
212
213 -- Call the concurrent program to generate count requests
214 IF (l_debug = 1) THEN
215 print_debug('Calling the generate count requests concurrent program');
216 END IF;
217 l_req_id := fnd_request.submit_request
218 ( application => 'INV',
219 program => 'INCACG',
220 argument1 => '2',
221 argument2 => TO_CHAR(l_cycle_count_header_id),
222 argument3 => TO_CHAR(p_organization_id));
223
224 -- Check for errors
225 IF (l_req_id <= 0 OR l_req_id IS NULL) THEN
226 IF (l_debug = 1) THEN
227 print_debug('Error in calling the generate count requests program');
228 END IF;
229 RAISE FND_API.G_EXC_ERROR;
230 ELSE
231 COMMIT;
232 END IF;
233
234 ELSE -- Else for p_lpn_id is NULL
235
236 IF (l_debug = 1) THEN
237 print_debug('p_lpn_id is not null:' || p_lpn_id);
238 END IF;
239
240 -- Initialisize API return status to access
241 x_return_status := FND_API.G_RET_STS_SUCCESS;
242 l_errorcode := 0;
243
244 IF (l_debug = 1) THEN
245 print_debug('Initializing the fields for the record');
246 END IF;
247
248 l_interface_rec.last_update_date := SYSDATE;
249 l_interface_rec.last_updated_by := MTL_CCEOI_VAR_PVT.G_UserID;
250 l_interface_rec.creation_date := SYSDATE;
251 l_interface_rec.created_by := MTL_CCEOI_VAR_PVT.G_UserID;
252 l_interface_rec.process_mode := 1 ;
253 l_interface_rec.action_code := mtl_cceoi_var_pvt.G_CREATE ;
254 l_interface_rec.cycle_count_header_id := l_cycle_count_header_id ;
255 l_interface_rec.organization_id := p_organization_id ;
256 l_interface_rec.parent_lpn_id := p_lpn_id ;
257 l_interface_rec.inventory_item_id := p_inventory_item_id;
258 l_interface_rec.subinventory := p_subinventory ;
259 l_interface_rec.locator_id := p_locator_id;
260 l_interface_rec.revision := p_revision;
261
262 --13366585
263 IF (l_debug = 1) THEN
264 print_debug('Org: '||p_organization_id);
265 print_debug('Cycle Count Header id: '||l_cycle_count_header_id);
266 print_debug('Sub: '||p_subinventory);
267 print_debug('Locator: '||p_locator_id );
268 print_debug('Item_id: '||p_inventory_item_id);
269 END IF;
270 --13366585
271 --Bug 9874049 Added condiion to check for open cycle count entries and call
272 -- process_lpn_countrequest only if there are no open entries.
273 SELECT COUNT(1)
274 INTO l_count
275 FROM MTL_CYCLE_COUNT_ENTRIES
276 WHERE organization_id = p_organization_id
277 AND subinventory = p_subinventory
278 AND locator_id = p_locator_id
279 AND inventory_item_id = p_inventory_item_id
280 AND NVL(parent_lpn_id,-1) = NVL(p_lpn_id,-1)
281 AND NVL(revision, '@') = NVL(p_revision, '@')
282 AND entry_status_code IN (1,2,3)--Added 2 (Pending Approval) Bug# 13366585
283 AND cycle_count_header_id = l_cycle_count_header_id;
284
285 IF (l_count = 0 ) THEN
286
287 IF (l_debug = 1) THEN
288 print_debug('Calling the API MTL_CCEOI_ACTION_PUB');
289 END IF;
290
291 MTL_CCEOI_ACTION_PUB.process_lpn_countrequest
292 ( p_api_version => l_api_version_int
293 , p_init_msg_list => FND_API.G_TRUE
294 , p_commit => FND_API.G_TRUE
295 , p_validation_level => FND_API.G_VALID_LEVEL_FULL
296 , x_return_status => x_return_status
297 , x_errorcode => l_errorcode
298 , x_msg_count => x_msg_count
299 , x_msg_data => x_msg_data
300 , p_interface_rec => l_interface_rec
301 , x_interface_id_list => l_interface_id_list
302 ) ;
303
304 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
305 IF (l_debug = 1) THEN
306 print_debug('LPN count request error');
307 END IF;
308 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
309 END IF;
310 ENd IF; --end l_count condition
311 END IF; --End of p_lpn_id condition
312
313 -- Call the concurrent program to print
314 -- the cycle count listing report
315 IF (l_debug = 1) THEN
316 print_debug('About to call the cycle count listing report program');
317 END IF;
318 l_req_id := fnd_request.submit_request
319 ( application => 'INV',
320 program => 'INVARCLI',
321 argument1 => TO_CHAR(p_organization_id),
322 argument2 => TO_CHAR(l_cycle_count_header_id),
323 argument3 => TO_CHAR(SYSDATE,'YYYY/MM/DD HH24:MI:SS'), --bug 5944231
324 argument4 => TO_CHAR(SYSDATE,'YYYY/MM/DD HH24:MI:SS'), --bug 5944231
325 argument5 => '2',
326 argument6 => p_subinventory,
327 argument7 => '1',
328 argument8 => '1',
329 argument9 => '1');
330
331 -- Check for errors
332 IF (l_req_id <= 0 OR l_req_id IS NULL) THEN
333 IF (l_debug = 1) THEN
334 print_debug('Errored out when calling the cycle count listing report program');
335 END IF;
336 NULL;
337 ELSE
338 COMMIT;
339 END IF;
340
341 -- End of API body
342 -- Standard check of p_commit.
343 IF FND_API.To_Boolean( p_commit ) THEN
344 COMMIT WORK;
345 END IF;
346 -- Standard call to get message count and if count is 1,
347 -- get message info.
348 FND_MSG_PUB.Count_And_Get
349 ( p_count => x_msg_count,
350 p_data => x_msg_data
351 );
352 IF (l_debug = 1) THEN
353 print_debug('Successfully called the procedure Create_Unscheduled_Counts');
354 END IF;
355
356 EXCEPTION
357 WHEN FND_API.G_EXC_ERROR THEN
358 ROLLBACK TO Create_Unscheduled_Counts_PVT;
359 x_return_status := FND_API.G_RET_STS_ERROR;
360 IF (l_debug = 1) THEN
361 print_debug('Execution error!');
362 END IF;
363 FND_MSG_PUB.Count_And_Get
364 ( p_count => x_msg_count,
365 p_data => x_msg_data
366 );
367 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
368 ROLLBACK TO Create_Unscheduled_Counts_PVT;
369 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
370 IF (l_debug = 1) THEN
371 print_debug('Unexpected error!');
372 END IF;
373 FND_MSG_PUB.Count_And_Get
374 ( p_count => x_msg_count,
375 p_data => x_msg_data
376 );
377 WHEN OTHERS THEN
378 ROLLBACK TO Create_Unscheduled_Counts_PVT;
379 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
380 IF (l_debug = 1) THEN
381 print_debug('Others error!');
382 END IF;
383 IF FND_MSG_PUB.Check_Msg_Level
384 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
385 THEN
386 FND_MSG_PUB.Add_Exc_Msg
387 ( G_PKG_NAME ,
388 l_api_name
389 );
390 END IF;
391 FND_MSG_PUB.Count_And_Get
392 ( p_count => x_msg_count,
393 p_data => x_msg_data
394 );
395
396 END Create_Unscheduled_Counts;
397
398
399 -- End of package
400 END WMS_Cycle_PVT;
[Home] [Help]