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