1 PACKAGE BODY MTL_CCEOI_ACTION_PUB AS
2 /* $Header: INVPCCAB.pls 120.1 2005/06/22 05:43:54 appldev ship $ */
3 G_PKG_NAME CONSTANT VARCHAR2(30) := 'MTL_CCEOI_ACTION_PUB';
4
5 procedure mdebug(msg in varchar2)
6 is
7 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
8 begin
9 --dbms_output.put_line(msg);
10 null;
11 end;
12
13
14
15 -- finds interface id of an interface record that is created for processing
16 -- cycle count entry whose id is stored inside mtl_cceoi_var_pvt.G_CYCLE_COUNT_ENTRY_REC.CYCLE_COUNT_ENTRY_ID
17 -- pre: mtl_var_cceoi_pvt.g_cycle_count_entry_rec is filled out by call to
18 -- mtl_cceoi_prcess_pvt.validate_countlistseq
19 -- this function relies on invariant: at any time there shall be no more than
20 -- one interface record that contains pointer to exported cycle count entry
21 -- and at the same time is not marked for deletion and is not processed yet.
22 -- note: we need to get rid of simulation mode because it breaks this rule
23 function find_iface_id return number is
24 cursor l_mcei_csr(p_cce_id NUMBER) is
25 select cc_entry_interface_id
26 from mtl_cc_entries_interface
27 where cycle_count_entry_id = p_cce_id and
28 (delete_flag <> 1 or delete_flag is null
29 OR not (status_flag in (0,1)));
30
31 p_cei_id NUMBER := -1;
32 counter number := 0;
33
34 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
35 begin
36 for c_rec in l_mcei_csr(mtl_cceoi_var_pvt.G_CYCLE_COUNT_ENTRY_REC.CYCLE_COUNT_ENTRY_ID) loop
37 p_cei_id := c_rec.cc_entry_interface_id;
38 counter := counter + 1;
39 end loop;
40
41 if counter <> 1 then
42 raise fnd_api.g_exc_unexpected_error;
43 end if;
44
45 return p_cei_id;
46 end;
47
48 PROCEDURE Unlock_CCI_Row(p_iface_id IN NUMBER, x_return_status OUT NOCOPY VARCHAR2)
49 IS
50 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
51 BEGIN
52 x_return_status := fnd_api.g_ret_sts_success;
53
54 update mtl_cc_entries_interface
55 set
56 lock_flag = 2
57 , last_update_date = sysdate
58 , last_updated_by = MTL_CCEOI_VAR_PVT.G_UserID
59 , last_update_login = MTL_CCEOI_VAR_PVT.G_LoginID
60 , request_id = MTL_CCEOI_VAR_PVT.G_RequestID
61 , program_application_id = MTL_CCEOI_VAR_PVT.G_ProgramAppID
62 , program_id = MTL_CCEOI_VAR_PVT.G_ProgramID
63 , program_update_date = sysdate
64 where
65 cc_entry_interface_id = p_iface_id;
66
67 EXCEPTION
68 WHEN OTHERS THEN
69 x_return_status := fnd_api.g_ret_sts_unexp_error;
70 END;
71
72
73 -- derives SKU and CCE data from cycle_count_entry row into
74 -- the interface row
75 -- pre: p_cce_rec is a copy of valid mtl_cycle_count_entry record
76 PROCEDURE derive_CCE_Info
77 (
78 x_iface_rec IN OUT NOCOPY MTL_CCEOI_VAR_PVT.INV_CCEOI_TYPE,
79 p_cce_rec IN MTL_CYCLE_COUNT_ENTRIES%ROWTYPE )
80 IS
81 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
82 BEGIN
83
84 -- organization
85 x_iface_rec.organization_id := p_cce_rec.organization_id;
86
87 -- derive item id and overwrite item_segments just in case they existed
88 x_iface_rec.inventory_item_id := p_cce_rec.inventory_item_id;
89 x_iface_rec.item_segment1 := null;
90 x_iface_rec.item_segment2 := null;
91 x_iface_rec.item_segment3 := null;
92 x_iface_rec.item_segment4 := null;
93 x_iface_rec.item_segment5 := null;
94 x_iface_rec.item_segment6 := null;
95 x_iface_rec.item_segment7 := null;
96 x_iface_rec.item_segment8 := null;
97 x_iface_rec.item_segment9 := null;
98 x_iface_rec.item_segment10 := null;
99 x_iface_rec.item_segment11 := null;
100 x_iface_rec.item_segment12 := null;
101 x_iface_rec.item_segment13 := null;
102 x_iface_rec.item_segment14 := null;
103 x_iface_rec.item_segment15 := null;
104 x_iface_rec.item_segment16 := null;
105 x_iface_rec.item_segment17 := null;
106 x_iface_rec.item_segment18 := null;
107 x_iface_rec.item_segment19 := null;
108 x_iface_rec.item_segment20 := null;
109
110 -- subinv
111 x_iface_rec.subinventory := p_cce_rec.subinventory;
112
113 -- locator
114 x_iface_rec.locator_id := p_cce_rec.locator_id;
115 x_iface_rec.locator_segment1 := null;
116 x_iface_rec.locator_segment2 := null;
117 x_iface_rec.locator_segment3 := null;
118 x_iface_rec.locator_segment4 := null;
119 x_iface_rec.locator_segment5 := null;
120 x_iface_rec.locator_segment6 := null;
121 x_iface_rec.locator_segment7 := null;
122 x_iface_rec.locator_segment8 := null;
123 x_iface_rec.locator_segment9 := null;
124 x_iface_rec.locator_segment10 := null;
125 x_iface_rec.locator_segment11 := null;
126 x_iface_rec.locator_segment12 := null;
127 x_iface_rec.locator_segment13 := null;
128 x_iface_rec.locator_segment14 := null;
129 x_iface_rec.locator_segment15 := null;
130 x_iface_rec.locator_segment16 := null;
131 x_iface_rec.locator_segment17 := null;
132 x_iface_rec.locator_segment18 := null;
133 x_iface_rec.locator_segment19 := null;
134 x_iface_rec.locator_segment20 := null;
135
136 -- revision
137 x_iface_rec.revision := p_cce_rec.revision;
138
139 -- lot
140 x_iface_rec.lot_number := p_cce_rec.lot_number;
141
142 -- serial number
143 x_iface_rec.serial_number := p_cce_rec.serial_number;
144
145 -- attributes
146 x_iface_rec.attribute_category := p_cce_rec.attribute_category;
147 x_iface_rec.attribute1 := p_cce_rec.attribute1;
148 x_iface_rec.attribute2 := p_cce_rec.attribute2;
149 x_iface_rec.attribute3 := p_cce_rec.attribute3;
150 x_iface_rec.attribute4 := p_cce_rec.attribute4;
151 x_iface_rec.attribute5 := p_cce_rec.attribute5;
152 x_iface_rec.attribute6 := p_cce_rec.attribute6;
153 x_iface_rec.attribute7 := p_cce_rec.attribute7;
154 x_iface_rec.attribute8 := p_cce_rec.attribute8;
155 x_iface_rec.attribute9 := p_cce_rec.attribute9;
156 x_iface_rec.attribute10 := p_cce_rec.attribute10;
157 x_iface_rec.attribute11 := p_cce_rec.attribute11;
158 x_iface_rec.attribute12 := p_cce_rec.attribute12;
159 x_iface_rec.attribute13 := p_cce_rec.attribute13;
160 x_iface_rec.attribute14 := p_cce_rec.attribute14;
161 x_iface_rec.attribute15 := p_cce_rec.attribute15;
162
163 -- account
164 x_iface_rec.adjustment_account_id := mtl_cceoi_var_pvt.g_cycle_count_header_rec.inventory_adjustment_account;
165 x_iface_rec.account_segment1 := null;
166 x_iface_rec.account_segment2 := null;
167 x_iface_rec.account_segment3 := null;
168 x_iface_rec.account_segment4 := null;
169 x_iface_rec.account_segment5 := null;
170 x_iface_rec.account_segment6 := null;
171 x_iface_rec.account_segment7 := null;
172 x_iface_rec.account_segment8 := null;
173 x_iface_rec.account_segment9 := null;
174 x_iface_rec.account_segment10 := null;
175 x_iface_rec.account_segment11 := null;
176 x_iface_rec.account_segment12 := null;
177 x_iface_rec.account_segment13 := null;
178 x_iface_rec.account_segment14 := null;
179 x_iface_rec.account_segment15 := null;
180 x_iface_rec.account_segment16 := null;
181 x_iface_rec.account_segment17 := null;
182 x_iface_rec.account_segment18 := null;
183 x_iface_rec.account_segment19 := null;
184 x_iface_rec.account_segment20 := null;
185 x_iface_rec.account_segment21 := null;
186 x_iface_rec.account_segment22 := null;
187 x_iface_rec.account_segment23 := null;
188 x_iface_rec.account_segment24 := null;
189 x_iface_rec.account_segment25 := null;
190 x_iface_rec.account_segment26 := null;
191 x_iface_rec.account_segment27 := null;
192 x_iface_rec.account_segment28 := null;
193 x_iface_rec.account_segment29 := null;
194 x_iface_rec.account_segment30 := null;
195
196 -- cycle count header id?
197 x_iface_rec.cycle_count_header_id := p_cce_rec.cycle_count_header_id;
198
199 -- cycle count entry id is already derived
200 x_iface_rec.cycle_count_entry_id := p_cce_rec.cycle_count_entry_id;
201
202 END;
203
204 -- this procedure gets called for interface records that are not
205 -- connected to cycle_count_enrty yet, and it makes sure that
206 -- either data in the interface correspond to an existing unexported
207 -- cycle count entry or that the data in the interface does not correspond
208 -- to any existing entries. If the interface record corresponds to some
209 -- open entry in mtl_cycle_count_entries then this entry is going
210 -- to be exported and interface record fields that can be derived from
211 -- cycle_count_entry will be overwritten by data from there.
212 PROCEDURE Enforce_SKU_CountEntry_Match
213 (p_api_version IN NUMBER,
214 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
215 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
216 p_validation_level IN NUMBER DEFAULT FND_API.G_VALID_LEVEL_FULL,
217 x_return_status OUT NOCOPY VARCHAR2 ,
218 x_errorcode OUT NOCOPY NUMBER,
219 x_msg_count OUT NOCOPY NUMBER ,
220 x_msg_data OUT NOCOPY VARCHAR2 ,
221 x_iface_rec IN OUT NOCOPY MTL_CCEOI_VAR_PVT.INV_CCEOI_TYPE)
222 IS
223 l_api_version number := 0.9;
224 l_api_name VARCHAR2(30) := 'Enforce_SKU_CountEntry_Match';
225
226 cursor l_mcce_csr(p_cce_id in number) is
227 select *
228 from mtl_cycle_count_entries
229 where cycle_count_entry_id = p_cce_id
230 for update of export_flag;
231
232 counter number := 0;
233
234 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
235 BEGIN
236 SAVEPOINT Enforce_SKU_CountEntry_Match;
237 --
238 -- Standard Call to check for call compatibility
239 IF NOT FND_API.Compatible_API_Call(l_api_version
240 , p_api_version
241 , l_api_name
242 , G_PKG_NAME) THEN
243 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
244 END IF;
245
246 --
247 -- Initialize message list if p_init_msg_list is set to true
248 IF FND_API.to_Boolean(p_init_msg_list) THEN
249 FND_MSG_PUB.initialize;
250 END IF;
251
252 -- Initialize API return status to success
253 x_return_status := FND_API.G_RET_STS_SUCCESS;
254 x_errorcode := 0;
255 --
256 -- API body
257 --
258
259 if (x_iface_rec.cycle_count_entry_id is null) then
260
261 mtl_cceoi_process_pvt.Validate_CHeader
262 (
263 p_api_version => 0.9,
264 x_msg_count => x_msg_count,
265 x_msg_data => x_msg_data,
266 x_return_status => x_return_status,
267 x_errorcode => x_errorcode,
268 p_cycle_count_header_id => x_iface_rec.cycle_count_header_id,
269 p_cycle_count_header_name => x_iface_rec.cycle_count_header_name
270 );
271
272 if (x_return_status <> fnd_api.g_ret_sts_success) then
273 raise fnd_api.g_exc_error;
274 end if;
275
276 mtl_cceoi_process_pvt.Validate_CountListSeq
277 (
278 p_api_version => 0.9,
279 x_msg_count => x_msg_count,
280 x_msg_data => x_msg_data,
281 x_return_status => x_return_status,
282 x_errorcode => x_errorcode,
283 p_cycle_count_header_id => MTL_CCEOI_VAR_PVT.G_CC_HEADER_ID,
284 p_cycle_count_entry_id => x_iface_rec.cycle_count_entry_id,
285 p_count_list_sequence => x_iface_rec.count_list_sequence,
286 p_organization_id =>
287 MTL_CCEOI_VAR_PVT.G_CYCLE_COUNT_HEADER_REC.organization_id);
288
289 if (x_return_status <> fnd_api.g_ret_sts_success) then
290 raise fnd_api.g_exc_error;
291 end if;
292
293 -- if no record found then stop verification, there is no record
294 -- that we can lock
295 if (x_errorcode in (65, 66)) then
296 x_errorcode := 0;
297 return;
298 end if;
299
300 -- if we're here that means we have found a corresponding entry
301 -- in the cycle count
302 x_iface_rec.cycle_count_entry_id :=
303 mtl_cceoi_var_pvt.g_cycle_count_entry_rec.cycle_count_entry_id;
304 end if;
305
306 for c_rec in l_mcce_csr(x_iface_rec.cycle_count_entry_id) loop
307
308 -- fatal error since entry was exported by someone else
309 if (c_rec.export_flag = 1) then
310 IF (l_debug = 1) THEN
311 mdebug('fatal error: exported by someone else');
312 END IF;
313 FND_MESSAGE.Set_Name('INV', 'INV_CCEOI_ENTRY_EXPORTED');
314 FND_MSG_PUB.Add;
315 x_errorcode := 201;
316 RAISE FND_API.G_EXC_ERROR;
317 end if;
318
319
320 if (not (c_rec.entry_status_code in (1,3))) then
321 FND_MESSAGE.Set_Name('INV', 'INV_CCEOI_ENTRY_STATUS_NA');
322 FND_MSG_PUB.Add;
323 x_errorcode := 202;
324 RAISE FND_API.G_EXC_ERROR;
325 end if;
326
327 -- TODO: print warning about deriving stuff
328
329 derive_CCE_Info(x_iface_rec => x_iface_rec, p_cce_rec => c_rec);
330
331 if (x_iface_rec.action_code <> mtl_cceoi_var_pvt.g_valsim) then
332 IF (l_debug = 1) THEN
333 mdebug('Exporting: ' || to_char(x_iface_rec.cycle_count_entry_id));
334 END IF;
335 mtl_cceoi_process_pvt.Set_CCExport(
336 p_api_version => 0.9,
337 x_msg_data => x_msg_data,
338 x_msg_count => x_msg_count,
339 x_return_status => x_return_status,
340 p_cycle_count_entry_id => x_iface_rec.cycle_count_entry_id,
341 p_export_flag => 1);
342
343 if (x_return_status <> fnd_api.g_ret_sts_success) then
344 raise fnd_api.g_exc_unexpected_error;
345 end if;
346
347 end if;
348
349 counter := counter + 1;
350 end loop;
351
352 -- if user provided cycle_count_entry_id does not correspond
353 -- to anything report an error
354 if (counter = 0) then
355 FND_MESSAGE.Set_Name('INV', 'INV_CCEOI_NO_CCE_WITH_CCEID');
356 FND_MSG_PUB.Add;
357 x_errorcode := 203;
358 raise fnd_api.g_exc_error;
359 end if;
360
361
362 --
363 -- END of API body
364 --
365
366 -- Standard check of p_commit
367 IF FND_API.to_Boolean(p_commit) THEN
368 COMMIT;
369 END IF;
370
371 FND_MSG_PUB.Count_And_Get
372 (p_count => x_msg_count
373 , p_data => x_msg_data);
374
375 EXCEPTION
376 WHEN FND_API.G_EXC_ERROR THEN
377 --
378 ROLLBACK TO Enforce_SKU_CountEntry_Match;
379
380 --
381 x_return_status := FND_API.G_RET_STS_ERROR;
382 --
383 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count
384 , p_data => x_msg_data);
385 --
386 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
387 --
388 ROLLBACK TO Enforce_SKU_CountEntry_Match;
389 --
390 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
391 x_errorcode := -1;
392 --
393 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count
394 , p_data => x_msg_data);
395 --
396 WHEN OTHERS THEN
397 --
398 ROLLBACK TO Enforce_SKU_CountEntry_Match;
399 --
400 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
401 x_errorcode := -1;
402 --
403 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
404 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
405 END IF;
406 --
407 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count
408 , p_data => x_msg_data);
409 END;
410
411
412
413 PROCEDURE Initial_Insert
414 (p_api_version IN NUMBER,
415 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
416 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
417 p_validation_level IN NUMBER DEFAULT FND_API.G_VALID_LEVEL_FULL,
418 x_return_status OUT NOCOPY VARCHAR2 ,
419 x_errorcode OUT NOCOPY NUMBER,
420 x_msg_count OUT NOCOPY NUMBER ,
421 x_msg_data OUT NOCOPY VARCHAR2 ,
422 x_iface_rec IN OUT NOCOPY MTL_CCEOI_VAR_PVT.INV_CCEOI_TYPE)
423 IS
424 -- Start OF comments
425 -- API name : Initial_Insert
426 -- TYPE : Private
427 -- Pre-reqs : None
428
429 -- Parameters:
430 -- IN : p_api_version IN NUMBER (required)
431 -- API Version of this procedure
432 -- p_init_msg_level IN VARCHAR2 (optional)
433 -- DEFAULT = FND_API.G_FALSE,
434 -- p_commit IN VARCHAR2 (optional)
435 -- DEFAULT = FND_API.G_FALSE,
436 -- p_validation_level IN NUMBER (optional)
437 -- DEFAULT = FND_API.G_VALID_LEVEL_FULL,
438 -- OUT : X_return_status OUT NUMBER
439 -- Result of all the operations
440 -- x_msg_count OUT NUMBER,
441 -- x_msg_data OUT VARCHAR2,
442 -- X_ErrorCode OUT NUMBER
443 -- RETURN value OF the Error status
444
445 -- IN OUT: x_iface_rec IN OUT CCEOI_Rec_Type (required)
446 -- complete interface RECORD
447 --
448 -- Version: Current Version 0.9
449 -- Initial version 0.9
450 -- Notes : Attempts to insert and lock passed to it p_iface_rec
451 -- into the mtl_cc_entries_interface table. If the interface record
452 -- corresponds to an existing count request in mtl_cycle_count_entries
453 -- then the count entry will be marked as exported unless it was already
454 -- exported. If count entry is already exported that would mean that it
455 -- is exported by a different interface record in which case interface
456 -- record is not inserted. If count entry os not exported then the SKU,
457 -- attribures, and account info will be derived from it, and that data
458 -- will copied into x_iface_rec and also inserted into a table
459 -- END OF comments
460
461 l_api_version number := 0.9;
462 l_api_name VARCHAR2(30) := 'Initial_Insert';
463
464 CURSOR l_mcei_csr(ccei_id in number) is
465 select *
466 from mtl_cc_entries_interface
467 where cc_entry_interface_id = ccei_id
468 for update of lock_flag;
469
470 counter NUMBER := 0;
471
472 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
473 BEGIN
474
475 SAVEPOINT Initial_Insert;
476 --
477 -- Standard Call to check for call compatibility
478 IF NOT FND_API.Compatible_API_Call(l_api_version
479 , p_api_version
480 , l_api_name
481 , G_PKG_NAME) THEN
482 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
483 END IF;
484
485 --
486 -- Initialize message list if p_init_msg_list is set to true
487 IF FND_API.to_Boolean(p_init_msg_list) THEN
488 FND_MSG_PUB.initialize;
489 END IF;
490
491 -- Initialize API return status to success
492 x_return_status := FND_API.G_RET_STS_SUCCESS;
493 x_errorcode := 0;
494 --
495 -- API body
496 --
497
498 if (x_iface_rec.cc_entry_interface_id is not null) then
499 IF (l_debug = 1) THEN
500 mdebug('cc_entry interface not null');
501 END IF;
502 -- if record is in the interface already
503 for c_rec in l_mcei_csr(x_iface_rec.cc_entry_interface_id) loop
504
505 MTL_CCEOI_VAR_PVT.G_CC_ENTRY_INTERFACE_ID :=
506 x_iface_rec.cc_entry_interface_id;
507
508 if (c_rec.lock_flag = 1) then
509
510 FND_MESSAGE.Set_Name('INV', 'INV_CCEOI_IFACE_ROW_LOCKED');
511 FND_MSG_PUB.Add;
512 x_errorcode := 200;
513 RAISE FND_API.G_EXC_ERROR;
514
515 end if;
516
517 if (c_rec.delete_flag = 1) then
518 FND_MESSAGE.Set_Name('INV', 'INV_CCEOI_IFACE_MARKED_DELETED');
519 FND_MSG_PUB.Add;
520 x_errorcode := 204;
521 RAISE FND_API.G_EXC_ERROR;
522 end if;
523
524 if (c_rec.status_flag in (0, 1)) then
525 FND_MESSAGE.Set_Name('INV', 'INV_CCEOI_IFACE_STATUS_DONE');
526 FND_MSG_PUB.Add;
527 x_errorcode := 206;
528 RAISE FND_API.G_EXC_ERROR;
529 end if;
530
531 if (nvl(c_rec.process_flag,1) = 2) then
532 FND_MESSAGE.Set_Name('INV', 'INV_CCEOI_IFACE_NOT_READY');
533 FND_MSG_PUB.Add;
534 x_errorcode := 207;
535 RAISE FND_API.G_EXC_ERROR;
536 end if;
537
538 -- if interface record is already linked to mtl_cycle_count_entries
539 if (c_rec.cycle_count_entry_id is not null) then
540
541 -- we may want to warn about updating only count info
542 x_iface_rec.cycle_count_entry_id := c_rec.cycle_count_entry_id;
543 x_iface_rec.lock_flag := 1;
544
545 mtl_cceoi_process_pvt.Update_CCIEntry(x_iface_rec,x_return_status);
546
547 if (x_return_status <> fnd_api.g_ret_sts_success) then
548 raise fnd_api.g_exc_unexpected_error;
549 end if;
550
551 -- synchronize interface record in memory with interface record
552 -- in the table
553 -- derive count list sequence from table data in case it
554 -- was null
555 x_iface_rec.count_list_sequence := c_rec.count_list_sequence;
556 x_iface_rec.cycle_count_header_id := c_rec.cycle_count_header_id;
557 x_iface_rec.organization_id := c_rec.organization_id;
558 x_iface_rec.inventory_item_id := c_rec.inventory_item_id;
559 x_iface_rec.subinventory := c_rec.subinventory;
560 x_iface_rec.locator_id := c_rec.locator_id;
561 x_iface_rec.lot_number := c_rec.lot_number;
562 x_iface_rec.serial_number := c_rec.serial_number;
563 x_iface_rec.parent_lpn_id := c_rec.parent_lpn_id;
564 x_iface_rec.outermost_lpn_id := c_rec.outermost_lpn_id;
565 x_iface_rec.cost_group_id := c_rec.cost_group_id;
566
567 else --
568 -- this procedure will try to export corresponding cycle count
569 -- entry and derive id's from there
570 Enforce_SKU_CountEntry_Match(
571 p_api_version=>0.9,
572 x_msg_data => x_msg_data,
573 x_msg_count => x_msg_count,
574 x_errorcode => x_errorcode,
575 x_return_status => x_return_status,
576 x_iface_rec => x_iface_rec);
577
578 if (x_return_status <> fnd_api.g_ret_sts_success) then
579 -- only raise exception and rollback if record cannot be
580 -- inserted into the interface (errors > 200)
581 if (x_errorcode < 0) then
582 raise fnd_api.g_exc_unexpected_error;
583 elsif (x_errorcode >= 200) then
584 raise fnd_api.g_exc_error;
585 else
586 x_errorcode := 0;
587 x_return_status := fnd_api.g_ret_sts_success;
588 end if;
589 end if;
590
591
592 x_iface_rec.valid_flag := 2;--c_rec.valid_flag;
593 x_iface_rec.status_flag := c_rec.status_flag;
594 x_iface_rec.error_flag := c_rec.error_flag;
595 x_iface_rec.lock_flag := 1;
596 x_iface_rec.cycle_count_header_id := mtl_cceoi_var_pvt.g_cc_header_id;
597
598 mtl_cceoi_process_pvt.Update_CCIEntry(x_iface_rec,x_return_status);
599
600 if (x_return_status <> fnd_api.g_ret_sts_success) then
601 raise fnd_api.g_exc_unexpected_error;
602 end if;
603 end if;
604
605
606 counter := counter + 1;
607 end loop;
608 end if;
609
610 if (counter = 0) then
611 if (x_iface_rec.cc_entry_interface_id is null) then
612
613 SELECT MTL_CC_ENTRIES_INTERFACE_S1.nextval
614 INTO x_iface_rec.cc_entry_interface_id
615 FROM dual;
616
617 MTL_CCEOI_VAR_PVT.G_CC_ENTRY_INTERFACE_ID :=
618 x_iface_rec.cc_entry_interface_id;
619 MTL_CCEOI_VAR_PVT.G_REC_IN_SYSTEM := FALSE;
620
621 Enforce_SKU_CountEntry_Match(
622 p_api_version=>0.9,
623 x_msg_data => x_msg_data,
624 x_msg_count => x_msg_count,
625 x_errorcode => x_errorcode,
626 x_return_status => x_return_status,
627 x_iface_rec => x_iface_rec);
628
629 if (x_return_status <> fnd_api.g_ret_sts_success) then
630 -- only raise exception and rollback if record cannot be
631 -- inserted into the interface (errors > 200)
632 -- otherwise just insert record in the interface and let
633 -- errors show up during processing
634 if (x_errorcode < 0) then
635 raise fnd_api.g_exc_unexpected_error;
636 elsif (x_errorcode >= 200) then
637 raise fnd_api.g_exc_error;
638 else
639 x_errorcode := 0;
640 x_return_status := fnd_api.g_ret_sts_success;
641 end if;
642 end if;
643
644 -- mark record to be initially valid, unprocessed, no errors, locked
645 x_iface_rec.valid_flag := 2;
646 -- if header name was supplied then mtl_cceoi_var_pvt.g_cc_header_id
647 -- contains its id, if id was supplied instead of name then
648 -- mtl_cceoi_var_pvt.g_cc_header_id is equal to that
649 -- if no id or header name was supplied then this variable is still
650 -- null
651 IF mtl_cceoi_var_pvt.g_cc_header_id IS NOT NULL THEN
652 x_iface_rec.cycle_count_header_id := mtl_cceoi_var_pvt.g_cc_header_id;
653 END IF;
654 x_iface_rec.status_flag := null;
655 x_iface_rec.error_flag := null;
656 x_iface_rec.lock_flag := 1;
657
658 mtl_cceoi_process_pvt.Insert_CCIEntry(x_iface_rec, x_return_status);
659
660 if (x_return_status <> fnd_api.g_ret_sts_success) then
661 -- some exotic error or one of the non-null columns is missing
662 raise fnd_api.g_exc_unexpected_error;
663 end if;
664
665 else
666 FND_MESSAGE.Set_Name('INV', 'INV_CCEOI_INVALID_CEI_ID');
667 FND_MSG_PUB.Add;
668 x_errorcode := 205;
669 raise fnd_api.g_exc_error;
670 end if;
671 end if;
672
673 --
674 MTL_CCEOI_VAR_PVT.G_REC_IN_SYSTEM := TRUE; -- XXX do not need this anymore
675 --
676 -- END of API body
677 --
678
679 -- Standard check of p_commit
680 IF FND_API.to_Boolean(p_commit) THEN
681 COMMIT;
682 END IF;
683
684 FND_MSG_PUB.Count_And_Get
685 (p_count => x_msg_count
686 , p_data => x_msg_data);
687
688 EXCEPTION
689 WHEN FND_API.G_EXC_ERROR THEN
690 --
691 ROLLBACK TO Initial_Insert;
692
693 --
694 x_return_status := FND_API.G_RET_STS_ERROR;
695 --
696 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count
697 , p_data => x_msg_data);
698 --
699 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
700 --
701 ROLLBACK TO Initial_Insert;
702 --
703 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
704 x_errorcode := -1;
705 --
706 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count
707 , p_data => x_msg_data);
708 --
709 WHEN OTHERS THEN
710 --
711 ROLLBACK TO Initial_Insert;
712 --
713 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
714 x_errorcode := -1;
715 --
716 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
717 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
718 END IF;
719 --
720 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count
721 , p_data => x_msg_data);
722 END;
723
724
725
726 --
727 -- Online processing for one record
728 PROCEDURE Import_CountRequest(
729 p_api_version IN NUMBER ,
730 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
731 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
732 p_validation_level IN NUMBER DEFAULT FND_API.G_VALID_LEVEL_FULL,
733 X_return_status OUT NOCOPY VARCHAR2 ,
734 x_errorcode OUT NOCOPY NUMBER,
735 x_msg_count OUT NOCOPY NUMBER ,
736 x_msg_data OUT NOCOPY VARCHAR2 ,
737 p_interface_rec IN MTL_CCEOI_VAR_PVT.INV_CCEOI_TYPE,
738 x_interface_id OUT NOCOPY NUMBER)
739 IS
740
741 l_return_status VARCHAR2(1);
742 l_result NUMBER;
743
744 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
745 BEGIN
746 -- Start OF comments
747 -- API name : Import_CountRequest
748 -- TYPE : Public
749 -- Pre-reqs : None
750 -- FUNCTION :
751 -- Parameters:
752 -- IN : p_api_version IN NUMBER (required)
753 -- API Version of this procedure
754 -- p_init_msg_level IN VARCHAR2 (optional)
755 -- DEFAULT = FND_API.G_FALSE,
756 -- p_commit IN VARCHAR2 (optional)
757 -- DEFAULT = FND_API.G_FALSE,
758 -- p_validation_level IN NUMBER (optional)
759 -- DEFAULT = FND_API.G_VALID_LEVEL_FULL,
760 -- p_interface_rec IN CCEOI_Rec_Type (required)
761 -- complete interface RECORD
762 -- OUT : X_return_status OUT NUMBER
763 -- Result of all the operations
764 -- x_msg_count OUT NUMBER,
765 -- x_msg_data OUT VARCHAR2,
766 -- X_ErrorCode OUT NUMBER
767 -- RETURN value OF the Error status
768 --
769 -- Version: Current Version 0.9
770 -- Changed
771 -- Previous Version Y.X
772 -- Initial version 0.9
773 -- Notes : Note text
774 -- END OF comments
775 DECLARE
776 L_api_version CONSTANT NUMBER := 0.9;
777 L_api_name CONSTANT VARCHAR2(30) := 'Import_CountRequest';
778 L_CCEOIId NUMBER;
779 L_interface_rec MTL_CCEOI_VAR_PVT.INV_CCEOI_TYPE;
780 L_id NUMBER;
781 BEGIN
782 -- Standard start of API savepoint
783 SAVEPOINT Import_CountRequest_PUB;
784 -- Standard Call to check for call compatibility
785 IF NOT FND_API.Compatible_API_Call(l_api_version
786 , p_api_version
787 , l_api_name
788 , G_PKG_NAME) THEN
789 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
790 END IF;
791 --
792 -- Initialize message list if p_init_msg_list is set to true
793 IF FND_API.to_Boolean(p_init_msg_list) THEN
794 FND_MSG_PUB.initialize;
795 END IF;
796 --
797 -- Initialisize API return status to access
798 x_return_status := FND_API.G_RET_STS_SUCCESS;
799 --
800 -- API body
801 IF (l_debug = 1) THEN
802 mdebug('Process: Import_CountRequest');
803 END IF;
804
805 --reset global variables
806 MTL_CCEOI_PROCESS_PVT.Reset_Global_Vars;
807
808 --
809 L_interface_rec := p_interface_rec;
810
811 --Validate Cost Group
812 IF (L_interface_rec.cost_group_id IS NOT NULL) THEN
813 l_result := INV_VALIDATE.Cost_Group(L_interface_rec.cost_group_id, L_interface_rec.organization_id);
814 IF (l_result = INV_Validate.F) THEN
815 IF (l_debug = 1) THEN
816 mdebug('invalid cost group id or cost group name');
817 END IF;
818 FND_MESSAGE.SET_NAME('INV', 'INV_INT_CSTEXT');
819 FND_MSG_PUB.ADD;
820 RAISE FND_API.G_EXC_ERROR;
821 END IF;
822 END IF;
823
824 --Set Cost Group ID Global variable
825 MTL_CCEOI_VAR_PVT.G_COST_GROUP_ID := p_interface_rec.cost_group_id;
826
827 --Set Cost Group ID Global variable
828 MTL_CCEOI_VAR_PVT.G_COST_GROUP_ID := p_interface_rec.cost_group_id;
829
830 -- check for locks, exports / derive necessary data from cc entry
831 -- so that we do not store junk
832
833 Initial_Insert(
834 p_api_version => 0.9,
835 x_return_status => x_return_status,
836 x_errorcode => x_errorcode,
837 x_msg_count => x_msg_count,
838 x_msg_data => x_msg_data,
839 x_iface_rec => L_interface_rec );
840
841 if (x_errorcode = 201) then
842 l_interface_rec.cc_entry_interface_id := find_iface_id;
843
844 Initial_Insert(
845 p_api_version => 0.9,
846 x_return_status => x_return_status,
847 x_errorcode => x_errorcode,
848 x_msg_count => x_msg_count,
849 x_msg_data => x_msg_data,
850 x_iface_rec => L_interface_rec );
851 end if;
852
853 if (x_return_status <> fnd_api.g_ret_sts_success) then
854 IF (l_debug = 1) THEN
855 mdebug('initial_insert errocode='||x_errorcode);
856 END IF;
857 if (x_return_status = fnd_api.g_ret_sts_unexp_error) then
858 raise FND_API.g_exc_error;
859 else
860 raise fnd_api.g_exc_unexpected_error;
861 end if;
862 end if;
863
864
865 -- IF (p_interface_rec.process_mode = 1) then -- online mode
866 IF
867 P_interface_rec.action_code = MTL_CCEOI_VAR_PVT.G_VALIDATE THEN
868 MTL_CCEOI_ACTION_PVT.Validate_CountRequest(
869 p_api_version => 0.9
870 , p_init_msg_list => FND_API.G_TRUE
871 , x_return_status => x_return_status
872 , x_msg_count => x_msg_count
873 , x_msg_data => x_msg_data
874 , p_interface_rec => L_interface_rec);
875 ELSIF
876 P_interface_rec.action_code = MTL_CCEOI_VAR_PVT.G_CREATE THEN
877 MTL_CCEOI_ACTION_PVT.Create_CountRequest(
878 p_api_version => 0.9
879 , p_init_msg_list => FND_API.G_TRUE
880 , x_return_status => x_return_status
881 , x_msg_count => x_msg_count
882 , x_msg_data => x_msg_data
883 , p_interface_rec => L_interface_rec);
884 ELSIF
885 P_interface_rec.action_code = MTL_CCEOI_VAR_PVT.G_VALSIM THEN
886 MTL_CCEOI_ACTION_PVT.ValSim_CountRequest(
887 p_api_version => 0.9
888 , p_init_msg_list => FND_API.G_TRUE
889 , x_return_status => x_return_status
890 , x_msg_count => x_msg_count
891 , x_msg_data => x_msg_data
892 , p_interface_rec => L_interface_rec);
893 ELSIF
894 P_interface_rec.action_code = MTL_CCEOI_VAR_PVT.G_PROCESS THEN
895
896 MTL_CCEOI_ACTION_PVT.Process_CountRequest(
897 p_api_version => 0.9
898 , p_init_msg_list => FND_API.G_TRUE
899 , x_return_status => x_return_status
900 , x_msg_count => x_msg_count
901 , x_msg_data => x_msg_data
902 , p_interface_rec => L_interface_rec);
903
904 ELSE
905 -- invalid action code
906 -- insert record into the interface table if necessary and
907 -- set an error
908
909
910 FND_MESSAGE.SET_NAME('INV', 'INV_CCEOI_UNKNOWN_ACTION_CODE');
911 FND_MSG_PUB.Add;
912
913 if (MTL_CCEOI_VAR_PVT.G_REC_IN_SYSTEM = FALSE) THEN
914
915 -- this is a really dangerous call since
916 -- some of the mandatory fields may be null in which case
917 -- insert will raise an exception
918 MTL_CCEOI_PROCESS_PVT.Insert_CCIEntry(
919 p_interface_rec => p_interface_rec,
920 x_return_status => x_return_status);
921 end if;
922
923 MTL_CCEOI_PROCESS_PVT.Insert_CCEOIError(
924 p_cc_entry_interface_id =>
925 MTL_CCEOI_VAR_PVT.G_CC_ENTRY_INTERFACE_ID,
926 p_error_column_name => 'ACTION_CODE'
927 , p_error_table_name => 'MTL_CC_ENTRIES_INTERFACE_ID'
928 , p_message_name => 'INV_CCEOI_UNKNOWN_ACTION_CODE');
929
930 x_return_status := FND_API.G_RET_STS_ERROR;
931
932 END IF;
933
934
935
936 IF (l_debug = 1) THEN
937 mdebug('return_status: ' || x_return_status);
938 END IF;
939
940 --
941 -- If the record exists in database and the record is processed successful
942 -- mark it for deletion, and unexport the record from MCCE
943 -- if necessary
944 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
945 -- if (mtl_cceoi_var_pvt.g_rec_in_system) then
946 -- it is unnecessary to mark it for deletion since
947 -- it will be deleted by purge if status_flag in (0,1)
948 /* MTL_CCEOI_PROCESS_PVT.Set_CCEOIFlags(
949 p_api_version => 0.9
950 , p_init_msg_list => FND_API.G_TRUE
951 , x_return_status => x_return_status
952 , x_msg_count => x_msg_count
953 , x_msg_data => x_msg_data
954 , p_cc_entry_interface_id => MTL_CCEOI_VAR_PVT.G_CC_ENTRY_INTERFACE_ID,
955 --=>L_interface_rec.cc_entry_interface_id,
956 p_flags => '$1$$');
957
958 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
959 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
960 END IF;
961 -- end if;
962 */
963
964 -- need to unexport record if it was exported unless it was in
965 -- simulate mode in which case cce record was not exported
966 -- or validate in which case it should stay exported
967 if ((l_interface_rec.action_code not in
968 (mtl_cceoi_var_pvt.g_valsim, mtl_cceoi_var_pvt.g_validate))
969 AND ( MTL_CCEOI_VAR_PVT.G_CYCLE_COUNT_ENTRY_REC.CYCLE_COUNT_ENTRY_ID is not null )) then
970 -- record must have been exported in order
971 -- to be processed, unless it is just validation
972 -- an of unscheduled entry in which case
973 -- no cycle count entry id will exist
974 mtl_cceoi_process_pvt.Set_CCExport(
975 p_api_version => 0.9,
976 x_return_status => x_return_status,
977 x_msg_data => x_msg_data,
978 x_msg_count => x_msg_count,
979 p_cycle_count_entry_id => mtl_cceoi_var_pvt.G_CYCLE_COUNT_ENTRY_REC.CYCLE_COUNT_ENTRY_ID,
980 p_export_flag => null);
981
982 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
983 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
984 END IF;
985
986 end if;
987
988 END IF;
989
990
991 -- END IF;
992 --Before unlocking record, updating the item id, locator id
993 -- into the interface table, which are missing
994 IF MTL_CCEOI_VAR_PVT.G_INVENTORY_ITEM_ID is not null and
995 mtl_cceoi_var_pvt.g_cc_entry_interface_id is not null
996 THEN
997 begin
998 update mtl_cc_entries_interface
999 set inventory_item_id = MTL_CCEOI_VAR_PVT.G_INVENTORY_ITEM_ID,
1000 locator_id = MTL_CCEOI_VAR_PVT.G_LOCATOR_ID
1001 where cc_entry_interface_id = mtl_cceoi_var_pvt.g_cc_entry_interface_id
1002 and inventory_item_id is null;
1003 exception
1004 when others then null;
1005 end;
1006 END IF;
1007 -- no matter whether we were successful or not
1008 -- we want to unlock the interface row
1009 Unlock_CCI_Row(
1010 l_interface_rec.cc_entry_interface_id,
1011 l_return_status);
1012
1013 if (l_return_status <> fnd_api.g_ret_sts_success) then
1014 raise fnd_api.g_exc_unexpected_error;
1015 end if;
1016
1017 -- use a different x_return_status
1018
1019
1020 x_interface_id := mtl_cceoi_var_pvt.g_cc_entry_interface_id;
1021 --
1022 -- END of API body
1023 -- Standard check of p_commit
1024 IF FND_API.to_Boolean(p_commit) THEN
1025 COMMIT;
1026 END IF;
1027 -- Standard call to get message count and if count is 1, get message info
1028 FND_MSG_PUB.Count_And_Get
1029 (p_count => x_msg_count
1030 , p_data => x_msg_data);
1031
1032
1033 EXCEPTION
1034 WHEN FND_API.G_EXC_ERROR THEN
1035 ROLLBACK TO Import_CountRequest_PUB;
1036 x_return_status := FND_API.G_RET_STS_ERROR;
1037 IF (l_debug = 1) THEN
1038 mdebug('Exception: ' || sqlerrm);
1039 END IF;
1040 FND_MSG_PUB.Count_And_Get(
1041 p_count => x_msg_count
1042 , p_data => x_msg_data);
1043
1044
1045 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1046 ROLLBACK TO Import_CountRequest_PUB;
1047 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1048 x_errorcode := -1;
1049 IF (l_debug = 1) THEN
1050 mdebug('Unexp-Exception: ' || sqlerrm);
1051 END IF;
1052 FND_MSG_PUB.Count_And_Get(
1053 p_count => x_msg_count
1054 , p_data => x_msg_data);
1055
1056
1057 WHEN OTHERS THEN
1058 ROLLBACK TO Import_CountRequest_PUB;
1059 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1060
1061 IF (l_debug = 1) THEN
1062 mdebug('Other-Exception: ' || sqlerrm);
1063 END IF;
1064
1065 IF FND_MSG_PUB.Check_Msg_Level(
1066 FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1067 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
1068 END IF;
1069
1070 FND_MSG_PUB.Count_And_Get(
1071 p_count => x_msg_count
1072 , p_data => x_msg_data);
1073 END;
1074
1075 --- temporary adhoc which helps concurrent programs to work properly
1076 --- since the only place where we set this variable is public api
1077 --- function that is not used by concurrent programs
1078 MTL_CCEOI_VAR_PVT.G_REC_IN_SYSTEM := TRUE;
1079
1080 END;
1081
1082
1083
1084 PROCEDURE Process_LPN_CountRequest
1085 (
1086 p_api_version IN NUMBER
1087 , p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE
1088 , p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE
1089 , p_validation_level IN NUMBER DEFAULT FND_API.G_VALID_LEVEL_FULL
1090 , x_return_status OUT NOCOPY VARCHAR2
1091 , x_errorcode OUT NOCOPY NUMBER
1092 , x_msg_count OUT NOCOPY NUMBER
1093 , x_msg_data OUT NOCOPY VARCHAR2
1094 , p_interface_rec IN MTL_CCEOI_VAR_PVT.INV_CCEOI_TYPE
1095 , x_interface_id_list OUT NOCOPY MTL_CCEOI_VAR_PVT.INV_CCEOI_ID_TABLE_TYPE
1096 )
1097 IS
1098 l_api_version NUMBER := 0.9;
1099 L_api_name VARCHAR2(30) := 'Process_LPN_CountRequest';
1100
1101 l_lpn WMS_CONTAINER_PUB.LPN;
1102 l_expl_tbl WMS_CONTAINER_PUB.WMS_Container_Tbl_Type;
1103 l_item_rec MTL_CCEOI_VAR_PVT.INV_CCEOI_TYPE := p_interface_rec;
1104 l_temp_int_id NUMBER;
1105 l_counter NUMBER := 0;
1106 l_unsched_allowed NUMBER;
1107 v_index NUMBER;
1108 l_result NUMBER;
1109 l_sub VARCHAR2(30);
1110 l_loc_id NUMBER;
1111 l_lpn_discrepancy NUMBER := 0;
1112 l_previous_lpn_id NUMBER;
1113 l_in_cc_entries BOOLEAN;
1114
1115 L_success_flag NUMBER;
1116 L_txn_header_id NUMBER;
1117 L_txn_temp_id NUMBER;
1118
1119 e_Invalid_Inputs EXCEPTION;
1120 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1121 BEGIN
1122
1123 -- Standard start of API savepoint
1124 SAVEPOINT Process_LPN_CountRequest;
1125
1126 -- Standard Call to check for call compatibility
1127 IF NOT FND_API.Compatible_API_Call(l_api_version
1128 , p_api_version
1129 , l_api_name
1130 , G_PKG_NAME) THEN
1131 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1132 END IF;
1133
1134 -- Initialize message list if p_init_msg_list is set to true
1135 IF FND_API.to_Boolean(p_init_msg_list) THEN
1136 FND_MSG_PUB.initialize;
1137 END IF;
1138 --
1139 -- Initialisize API return status to access
1140 x_return_status := FND_API.G_RET_STS_SUCCESS;
1141 x_errorcode := 0;
1142
1143 IF (l_debug = 1) THEN
1144 mdebug('Process: Process_LPN_CountRequest ' || to_char(p_interface_rec.cycle_count_header_id));
1145 END IF;
1146
1147 -- Check if WMS is installed
1148 IF NOT WMS_INSTALL.CHECK_INSTALL( x_return_status, x_msg_count, x_msg_data, p_interface_rec.organization_id) THEN
1149 IF (l_debug = 1) THEN
1150 mdebug('wms not installed');
1151 END IF;
1152 FND_MESSAGE.SET_NAME('INV', 'INV_WMS_NOT_INSTALLED_ERROR');
1153 RAISE fnd_api.g_exc_error;
1154 END IF;
1155
1156 --No validation for performance reasons
1157 -- Except for header id and lpn id
1158 mtl_cceoi_process_pvt.Validate_CHeader
1159 (
1160 p_api_version => 0.9,
1161 x_msg_count => x_msg_count,
1162 x_msg_data => x_msg_data,
1163 x_return_status => x_return_status,
1164 x_errorcode => x_errorcode,
1165 p_cycle_count_header_id => p_interface_rec.cycle_count_header_id,
1166 p_cycle_count_header_name => p_interface_rec.cycle_count_header_name
1167 );
1168 IF (x_return_status <> fnd_api.g_ret_sts_success) then
1169 IF (l_debug = 1) THEN
1170 mdebug('invalid header id');
1171 END IF;
1172 FND_MESSAGE.SET_NAME('INV', 'INV_CCEOI_INVALID_HEADER');
1173 raise fnd_api.g_exc_error;
1174 end IF;
1175
1176 -- Validate LPN ID and LPN
1177 IF p_interface_rec.parent_lpn_id IS NOT NULL OR p_interface_rec.parent_lpn IS NOT NULL THEN
1178 l_lpn.lpn_id := p_interface_rec.parent_lpn_id;
1179 l_lpn.license_plate_number := p_interface_rec.parent_lpn;
1180 l_result := WMS_CONTAINER_PUB.Validate_LPN(l_lpn);
1181 IF (l_result = INV_Validate.F) THEN
1182 IF (l_debug = 1) THEN
1183 mdebug('invalid parent lpn id or parent lpn');
1184 END IF;
1185 FND_MESSAGE.SET_NAME('INV', 'INV_WMS_CONT_INVALID_LPN');
1186 FND_MSG_PUB.ADD;
1187 RAISE e_Invalid_Inputs;
1188 END IF;
1189 -- Assing validated values to l_item_rec.
1190 l_item_rec.parent_lpn_id := l_lpn.lpn_id;
1191 l_item_rec.parent_lpn := l_lpn.license_plate_number;
1192
1193 -- Mark Previous lpn id
1194 l_previous_lpn_id := l_lpn.lpn_id;
1195
1196 -- Check for LPN subinventory discrepancy
1197 SELECT subinventory_code, locator_id
1198 INTO l_sub, l_loc_id
1199 FROM WMS_LICENSE_PLATE_NUMBERS
1200 WHERE lpn_id = l_lpn.lpn_id
1201 AND organization_id = p_interface_rec.organization_id;
1202
1203 IF p_interface_rec.subinventory <> l_sub OR p_interface_rec.locator_id <> l_loc_id THEN
1204 -- discrepancy exists
1205 IF (l_debug = 1) THEN
1206 mdebug('location discrepancy found');
1207 END IF;
1208 IF p_interface_rec.parent_lpn_id <> p_interface_rec.outermost_lpn_id THEN
1209 -- error out, this case not supported
1210 FND_MESSAGE.SET_NAME('INV', 'INV_WMS_CC_NESTED_LPN_DISCR');
1211 FND_MSG_PUB.ADD;
1212 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1213 ELSE
1214 -- set lpn discrepancy flag to 1 (yes)
1215 l_lpn_discrepancy := 1;
1216 -- change subinventory and locator
1217 l_item_rec.subinventory := l_sub;
1218 l_item_rec.locator_id := l_loc_id;
1219 END IF;
1220 END IF;
1221 END IF;
1222
1223 -- Set global variables for container adjustment and discrepancies
1224 SELECT container_enabled_flag, container_adjustment_option, container_discrepancy_option
1225 INTO MTL_CCEOI_VAR_PVT.G_CONTAINER_ENABLED_FLAG, MTL_CCEOI_VAR_PVT.G_CONTAINER_ADJUSTMENT_OPTION,
1226 MTL_CCEOI_VAR_PVT.G_CONTAINER_DISCREPANCY_OPTION
1227 FROM MTL_CYCLE_COUNT_HEADERS
1228 WHERE cycle_count_header_id = p_interface_rec.cycle_count_header_id;
1229 -- If either flag is null if container enabled flag is enabled (1), set to default.
1230 IF MTL_CCEOI_VAR_PVT.G_CONTAINER_ENABLED_FLAG = 1 THEN
1231 MTL_CCEOI_VAR_PVT.G_CONTAINER_ADJUSTMENT_OPTION := NVL(MTL_CCEOI_VAR_PVT.G_CONTAINER_ADJUSTMENT_OPTION, 1);
1232 MTL_CCEOI_VAR_PVT.G_CONTAINER_DISCREPANCY_OPTION := NVL(MTL_CCEOI_VAR_PVT.G_CONTAINER_DISCREPANCY_OPTION, 1);
1233 END IF;
1234
1235 --Check id interface record is an item or LPN
1236 IF (l_item_rec.inventory_item_id IS NOT NULL) THEN -- record is not a container
1237
1238 Import_CountRequest(
1239 p_api_version => 0.9,
1240 p_init_msg_list => FND_API.G_TRUE,
1241 p_commit => FND_API.G_TRUE,
1242 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1243 X_return_status => x_return_status,
1244 x_errorcode => x_errorcode,
1245 x_msg_count => x_msg_count,
1246 x_msg_data => x_msg_data,
1247 p_interface_rec => l_item_rec,
1248 x_interface_id => l_temp_int_id);
1249 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1250 IF (l_debug = 1) THEN
1251 mdebug('Import count request error');
1252 END IF;
1253 FND_FILE.PUT_LINE(FND_FILE.LOG, x_msg_data);
1254 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1255 END IF;
1256
1257 -- insert interface id into table
1258 x_interface_id_list(l_counter) := l_temp_int_id;
1259 ELSE
1260 IF (l_debug = 1) THEN
1261 mdebug('record is a container, exploding contents');
1262 END IF;
1263 -- Record is a container, thus needs to be exploded
1264 WMS_CONTAINER_PUB.Explode_LPN(
1265 p_api_version => 1.0,
1266 p_init_msg_list => fnd_api.g_true,
1267 p_commit => fnd_api.g_true,
1268 x_return_status => x_return_status,
1269 x_msg_count => x_msg_count,
1270 x_msg_data => x_msg_data,
1271 p_lpn_id => p_interface_rec.parent_lpn_id,
1272 x_content_tbl => l_expl_tbl);
1273 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1274 IF (l_debug = 1) THEN
1275 mdebug('lpn exlpoder error');
1276 END IF;
1277 FND_FILE.PUT_LINE(FND_FILE.LOG, x_msg_data);
1278 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1279 END IF;
1280
1281 -- Check if Unschedualed Entries are allowed.
1282 SELECT unscheduled_count_entry
1283 INTO l_unsched_allowed
1284 FROM mtl_cycle_count_headers
1285 WHERE cycle_count_header_id = p_interface_rec.cycle_count_header_id;
1286
1287 -- Loop through table for each item.
1288 v_index := l_expl_tbl.FIRST;
1289 WHILE v_index IS NOT NULL LOOP
1290 --Check if record represents an item
1291 IF (l_expl_tbl(v_index).content_type = 1) THEN
1292 -- Check if item exist in cycle count entries table or in the cycle count items
1293 -- table if unschedualed entries are allowed.
1294 IF (l_debug = 1) THEN
1295 mdebug('org id: ' || TO_CHAR(l_expl_tbl(v_index).organization_id));
1296 mdebug('lpn id: ' || TO_CHAR(l_expl_tbl(v_index).parent_lpn_id));
1297 mdebug('item id: ' || TO_CHAR(l_expl_tbl(v_index).content_item_id));
1298 mdebug('cost id: ' || TO_CHAR(l_expl_tbl(v_index).cost_group_id));
1299 mdebug('lot : ' || l_expl_tbl(v_index).lot_number);
1300 mdebug('rev: ' || l_expl_tbl(v_index).revision);
1301 mdebug('serl: ' || l_expl_tbl(v_index).serial_number);
1302 END IF;
1303
1304 -- *** for testing purposes only. remove after testing!!!
1305 /* SELECT cost_group_id
1306 INTO l_expl_tbl(v_index).cost_group_id
1307 FROM mtl_cycle_count_entries
1308 WHERE organization_id = l_expl_tbl(v_index).organization_id
1309 AND inventory_item_id = l_expl_tbl(v_index).content_item_id
1310 AND parent_lpn_id = l_expl_tbl(v_index).parent_lpn_id
1311 AND cycle_count_header_id = p_interface_rec.cycle_count_header_id;*/
1312 --------------------
1313 l_in_cc_entries := MTL_INV_UTIL_GRP.Exists_CC_Entries( l_expl_tbl(v_index).organization_id,
1314 l_expl_tbl(v_index).parent_lpn_id,
1315 l_expl_tbl(v_index).content_item_id,
1316 l_expl_tbl(v_index).cost_group_id,
1317 l_expl_tbl(v_index).lot_number,
1318 l_expl_tbl(v_index).revision,
1319 l_expl_tbl(v_index).serial_number);
1320 IF l_in_cc_entries OR ( l_unsched_allowed = 1
1321 AND MTL_INV_UTIL_GRP.Exists_CC_Items(p_interface_rec.cycle_count_header_id,
1322 l_expl_tbl(v_index).content_item_id) )
1323 THEN
1324
1325 IF l_in_cc_entries THEN
1326 -- Count List sequence number required for processing for schedualed entries
1327 SELECT count_list_sequence
1328 INTO l_item_rec.count_list_sequence
1329 FROM mtl_cycle_count_entries
1330 WHERE organization_id = l_expl_tbl(v_index).organization_id
1331 AND inventory_item_id = l_expl_tbl(v_index).content_item_id
1332 AND parent_lpn_id = l_expl_tbl(v_index).parent_lpn_id
1333 AND NVL(cost_group_id, -1) = NVL(l_expl_tbl(v_index).cost_group_id, -1)
1334 AND cycle_count_header_id = p_interface_rec.cycle_count_header_id;
1335 END IF;
1336
1337 IF l_previous_lpn_id <> l_expl_tbl(v_index).parent_lpn_id THEN
1338 -- validate new lpn_id and get lpn
1339 l_lpn.lpn_id := l_expl_tbl(v_index).parent_lpn_id;
1340 l_lpn.license_plate_number := NULL;
1341
1342 IF (l_debug = 1) THEN
1343 mdebug('validating new parent lpn: ' || TO_CHAR(l_lpn.lpn_id));
1344 END IF;
1345 l_result := WMS_CONTAINER_PUB.Validate_LPN(l_lpn);
1346 IF (l_result = INV_Validate.F) THEN
1347 IF (l_debug = 1) THEN
1348 mdebug('invalid parent lpn id or parent lpn');
1349 END IF;
1350 FND_MESSAGE.SET_NAME('INV', 'INV_WMS_CONT_INVALID_LPN');
1351 FND_MSG_PUB.ADD;
1352 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1353 END IF;
1354
1355 -- put new lpn in record
1356 l_item_rec.parent_lpn_id := l_lpn.lpn_id;
1357 l_item_rec.parent_lpn := l_lpn.license_plate_number;
1358 -- Mark Previous lpn id
1359 l_previous_lpn_id := l_lpn.lpn_id;
1360 END IF;
1361
1362 IF (l_debug = 1) THEN
1363 mdebug('populating: ' || to_char(l_item_rec.count_list_sequence));
1364 END IF;
1365 -- populate item columns in item record
1366 l_item_rec.inventory_item_id := l_expl_tbl(v_index).content_item_id;
1367 l_item_rec.lot_number := l_expl_tbl(v_index).lot_number;
1368 l_item_rec.revision := l_expl_tbl(v_index).revision;
1369 l_item_rec.serial_number := l_expl_tbl(v_index).serial_number;
1370 l_item_rec.parent_lpn_id := l_expl_tbl(v_index).parent_lpn_id;
1371 l_item_rec.count_quantity := l_expl_tbl(v_index).quantity;
1372 l_item_rec.count_uom := l_expl_tbl(v_index).uom;
1373 -- BEGIN INVCONV
1374 l_item_rec.secondary_count_quantity := l_expl_tbl(v_index).sec_quantity;
1375 l_item_rec.secondary_uom := l_expl_tbl(v_index).sec_uom;
1376 -- END INVCONV
1377 l_item_rec.cost_group_id := l_expl_tbl(v_index).cost_group_id;
1378
1379 -- Assign count quantity to global variable
1380 MTL_CCEOI_VAR_PVT.G_LPN_ITEM_SYSTEM_QTY := l_expl_tbl(v_index).quantity;
1381 MTL_CCEOI_VAR_PVT.G_LPN_ITEM_SEC_SYSTEM_QTY := l_expl_tbl(v_index).sec_quantity; -- INVCONV
1382
1383 -- Assign parent_lpn_id to global variable
1384 MTL_CCEOI_VAR_PVT.G_LPN_ID := l_expl_tbl(v_index).parent_lpn_id;
1385
1386 Import_CountRequest(
1387 p_api_version => 0.9,
1388 p_init_msg_list => FND_API.G_TRUE,
1389 p_commit => FND_API.G_TRUE,
1390 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1391 X_return_status => x_return_status,
1392 x_errorcode => x_errorcode,
1393 x_msg_count => x_msg_count,
1394 x_msg_data => x_msg_data,
1395 p_interface_rec => l_item_rec,
1396 x_interface_id => l_temp_int_id);
1397 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1398 IF (l_debug = 1) THEN
1399 mdebug('LPN Import count request error');
1400 END IF;
1401 FND_FILE.PUT_LINE(FND_FILE.LOG, x_msg_data);
1402 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1403 END IF;
1404
1405 -- insert interface id into table
1406 x_interface_id_list(l_counter) := l_temp_int_id;
1407 l_counter := l_counter + 1;
1408 END IF;
1409 END IF;
1410 v_index := l_expl_tbl.NEXT(v_index);
1411 END LOOP;
1412
1413 -- If discrepancy existed in lpn and no approval needed, then do a subinventory trasfer
1414 IF (l_lpn_discrepancy = 1 AND MTL_CCEOI_VAR_PVT.G_CONTAINER_ENABLED_FLAG = 1
1415 AND MTL_CCEOI_VAR_PVT.G_CONTAINER_ADJUSTMENT_OPTION = 1
1416 AND MTL_CCEOI_VAR_PVT.G_CONTAINER_DISCREPANCY_OPTION = 1 )THEN
1417 IF (l_debug = 1) THEN
1418 mdebug('lpn subinventory move in progress');
1419 END IF;
1420
1421 -- Create subinventory transfer information
1422 SELECT mtl_material_transactions_s.nextval
1423 INTO L_txn_header_id
1424 FROM dual;
1425
1426 -- Do a subinventory transfer
1427 L_success_flag := mtl_cc_transact_pkg.cc_transact(
1428 org_id=> p_interface_rec.organization_id
1429 , cc_header_id => p_interface_rec.cycle_count_header_id
1430 , item_id => -1 --l_item_rec.inventory_item_id
1431 , sub => l_sub
1432 , PUOMQty=> 1
1433 , TxnQty=> 1
1434 , TxnUOM=> null
1435 , TxnDate => MTL_CCEOI_VAR_PVT.G_count_date
1436 , TxnAcctId => MTL_CCEOI_VAR_PVT.G_ADJUST_ACCOUNT_ID
1437 , LotNum => NULL
1438 , LotExpDate => NULL
1439 , rev => NULL
1440 , locator_id => l_loc_id
1441 , TxnRef=> p_interface_rec.reference
1442 , ReasonId=> p_interface_rec.transaction_reason_id
1443 , UserId=> MTL_CCEOI_VAR_PVT.G_userid
1444 , cc_entry_id=> p_interface_rec.cycle_count_header_id
1445 , LoginId => MTL_CCEOI_VAR_PVT.G_LoginId
1446 , TxnProcMode => 1
1447 , TxnHeaderId=>L_txn_header_id
1448 , SerialNum=> NULL
1449 , TxnTempId=> NULL
1450 , SerialPrefix=> NULL
1451 , lpn_id => p_interface_rec.outermost_lpn_id
1452 , transfer_sub => p_interface_rec.subinventory
1453 , transfer_loc_id => p_interface_rec.locator_id
1454 , cost_group_id => p_interface_rec.cost_group_id
1455 );
1456 IF NVL(L_txn_header_id, -1) < 0 OR NVL(L_success_flag, -1) < 0 THEN
1457 IF (l_debug = 1) THEN
1458 mdebug('lpn subinventory move failed: ' || to_char(L_success_flag));
1459 END IF;
1460 FND_MESSAGE.SET_NAME('INV', 'INV_ADJ_TXN_FAILED');
1461 APP_EXCEPTION.RAISE_EXCEPTION;
1462 END IF;
1463 END IF;
1464
1465 END IF;
1466
1467 EXCEPTION
1468 WHEN FND_API.G_EXC_ERROR THEN
1469 ROLLBACK TO Process_LPN_CountRequest;
1470 x_return_status := FND_API.G_RET_STS_ERROR;
1471 IF (l_debug = 1) THEN
1472 mdebug('Exception: ' || sqlerrm);
1473 END IF;
1474 FND_MSG_PUB.Count_And_Get(
1475 p_count => x_msg_count
1476 , p_data => x_msg_data);
1477
1478
1479 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1480 ROLLBACK TO Process_LPN_CountRequest;
1481 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1482 x_errorcode := -1;
1483 IF (l_debug = 1) THEN
1484 mdebug('Unexp-Exception: ' || sqlerrm);
1485 END IF;
1486 FND_MSG_PUB.Count_And_Get(
1487 p_count => x_msg_count
1488 , p_data => x_msg_data);
1489
1490 WHEN e_Invalid_Inputs THEN
1491 ROLLBACK TO Process_LPN_CountRequest;
1492 x_return_status := FND_API.G_RET_STS_ERROR;
1493 FND_MSG_PUB.Count_And_Get
1494 ( p_count => x_msg_count,
1495 p_data => x_msg_data
1496 );
1497
1498 WHEN OTHERS THEN
1499 ROLLBACK TO Process_LPN_CountRequest;
1500 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1501 IF (l_debug = 1) THEN
1502 mdebug('Other-Exception: ' || sqlerrm);
1503 END IF;
1504
1505 IF FND_MSG_PUB.Check_Msg_Level(
1506 FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1507 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
1508 END IF;
1509
1510 FND_MSG_PUB.Count_And_Get(
1511 p_count => x_msg_count
1512 , p_data => x_msg_data);
1513 END Process_LPN_CountRequest;
1514
1515
1516
1517 END MTL_CCEOI_ACTION_PUB;
[Home] [Help]