[Home] [Help]
PACKAGE BODY: APPS.MTL_CCEOI_ACTION_PVT
Source
1 PACKAGE BODY MTL_CCEOI_ACTION_PVT AS
2 /* $Header: INVVCCAB.pls 120.2.12020000.4 2012/12/19 10:47:56 agembali ship $ */
3 G_PKG_NAME CONSTANT VARCHAR2(30) := 'MTL_CCEOI_ACTION_PVT';
4
5
6 procedure mdebug(msg in varchar2)
7 is
8 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
9 begin
10 --dbms_output.put_line(msg);
11 null;
12 end;
13 --Added NOCOPY hint to x_return_status,x_msg_count,x_msg_data OUT
14 --parameters to comply with GSCC File.Sql.39 standard. Bug:4410902
15 PROCEDURE Process_Error(
16 p_api_version IN NUMBER,
17 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
18 p_commit IN VARCHAR2 := FND_API.G_FALSE,
19 p_interface_rec IN MTL_CCEOI_VAR_PVT.INV_CCEOI_TYPE,
20 p_message_name IN MTL_CC_INTERFACE_ERRORS.ERROR_MESSAGE%TYPE,
21 p_error_column_name IN MTL_CC_INTERFACE_ERRORS.ERROR_COLUMN_NAME%TYPE,
22 p_error_table_name IN MTL_CC_INTERFACE_ERRORS.ERROR_TABLE_NAME%TYPE,
23 p_flags IN VARCHAR2,
24 x_return_status OUT NOCOPY VARCHAR2,
25 x_msg_count OUT NOCOPY NUMBER ,
26 x_msg_data OUT NOCOPY VARCHAR2 )
27 IS
28 L_api_version CONSTANT NUMBER := 0.9;
29 L_api_name CONSTANT VARCHAR2(30) := 'Process_Error';
30 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
31 BEGIN
32
33 -- Standard start of API savepoint
34 SAVEPOINT Process_Error;
35 --
36 -- Standard Call to check for call compatibility
37 IF NOT FND_API.Compatible_API_Call(l_api_version
38 , p_api_version
39 , l_api_name
40 , G_PKG_NAME) THEN
41 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
42 END IF;
43
44 --
45 -- Initialize message list if p_init_msg_list is set to true
46 IF FND_API.to_Boolean(p_init_msg_list) THEN
47 FND_MSG_PUB.initialize;
48 END IF;
49
50 -- Initialize API return status to success
54 --
51 x_return_status := FND_API.G_RET_STS_SUCCESS;
52 --
53 -- API body
55
56 FND_MESSAGE.SET_NAME('INV', p_message_name);
57 FND_MSG_PUB.Add;
58
59 IF MTL_CCEOI_VAR_PVT.G_REC_IN_SYSTEM = FALSE THEN
60
61 MTL_CCEOI_PROCESS_PVT.Insert_CCIEntry(
62 p_interface_rec=> p_interface_rec,
63 x_return_status => x_return_status);
64
65 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
66 -- TODO: post FND message about not being able to create
67 -- a cycle count entry
68 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
69 END IF;
70 END IF;
71
72 --
73 -- SET the flags
74 MTL_CCEOI_PROCESS_PVT.Set_CCEOIFlags(
75 p_api_version => 0.9,
76 x_return_status => x_return_status,
77 x_msg_count => x_msg_count,
78 x_msg_data => x_msg_data,
79 p_cc_entry_interface_id => MTL_CCEOI_VAR_PVT.G_CC_ENTRY_INTERFACE_ID,
80 p_flags => p_flags);
81
82 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
83 -- TODO: post FND message about not being able to set flags
84 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
85 END IF;
86
87 --
88 -- Write INTO Error TABLE
89 MTL_CCEOI_PROCESS_PVT.Insert_CCEOIError(
90 p_cc_entry_interface_id => MTL_CCEOI_VAR_PVT.G_CC_ENTRY_INTERFACE_ID,
91 p_error_column_name => p_error_column_name,
92 p_error_table_name => p_error_table_name,
93 p_message_name => p_message_name );
94 --
95
96 --
97 -- END of API body
98 --
99
100 -- Standard check of p_commit
101 IF FND_API.to_Boolean(p_commit) THEN
102 COMMIT;
103 END IF;
104
105 FND_MSG_PUB.Count_And_Get
106 (p_count => x_msg_count
107 , p_data => x_msg_data);
108
109 EXCEPTION
110 WHEN FND_API.G_EXC_ERROR THEN
111 --
112 ROLLBACK TO Process_Error;
113 --
114 x_return_status := FND_API.G_RET_STS_ERROR;
115 --
116 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count
117 , p_data => x_msg_data);
118 --
119 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
120 --
121 ROLLBACK TO Process_Error;
122 --
123 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
124 --
125 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count
126 , p_data => x_msg_data);
127 --
128 WHEN OTHERS THEN
129 --
130 ROLLBACK TO Process_Error;
131 --
132 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
133 --
134 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
135 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
136 END IF;
137 --
138 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count
139 , p_data => x_msg_data);
140
141 END Process_Error;
142
143
144 --
145 -- Insert the given row into the interface table.
146 --Added NOCOPY hint to x_return_status,x_msg_count,x_msg_data OUT
147 --parameters to comply with GSCC File.Sql.39 standard. Bug:4410902
148 PROCEDURE Export_CountRequest(
149 p_api_version IN NUMBER ,
150 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
151 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
152 p_validation_level IN NUMBER DEFAULT FND_API.G_VALID_LEVEL_FULL,
153 x_return_status OUT NOCOPY VARCHAR2 ,
154 x_msg_count OUT NOCOPY NUMBER ,
155 x_msg_data OUT NOCOPY VARCHAR2 ,
156 p_interface_rec IN MTL_CCEOI_VAR_PVT.INV_CCEOI_TYPE )
157 IS
158 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
159 BEGIN
160 -- Start OF comments
161 -- API name : Export_CountRequest
162 -- TYPE : Private
163 -- Pre-reqs : None
164 -- FUNCTION :
165 -- INSERT the interface RECORD INTO the MTL_CC_ENTRIES_INTERFACE
166 -- TABLE AND locks the original RECORD IN the TABLE
167 -- MTL_CYCLE_COUNT_ENTRIES
168 -- Parameters:
169 -- IN : p_api_version IN NUMBER (required)
170 -- API Version of this procedure
171 -- p_init_msg_level IN VARCHAR2 (optional)
172 -- DEFAULT = FND_API.G_FALSE,
173 -- p_commit IN VARCHAR2 (optional)
174 -- DEFAULT = FND_API.G_FALSE,
175 -- p_validation_level IN NUMBER (optional)
176 -- DEFAULT = FND_API.G_VALID_LEVEL_FULL,
177 -- p_interface_rec IN CCEOI_Rec_Type (required)
178 -- complete interface RECORD
179 -- OUT : X_return_status OUT NUMBER
180 -- Result of all the operations
181 -- x_msg_count OUT NUMBER,
182 -- x_msg_data OUT VARCHAR2,
183 -- X_ErrorCode OUT NUMBER
184 -- RETURN value OF the Error status
185 -- Version: Current Version 0.9
186 -- Changed : Nothing
187 -- No Previous Version 0.0
188 -- Initial version 0.9
189 -- Notes : Note text
190 -- END OF comments
191 DECLARE
192 -- FOR export
193 L_EXPORT_FLAG NUMBER := 1;
194 --
195 L_return_status VARCHAR2(30);
196 L_msg_count NUMBER;
197 L_msg_data VARCHAR2(240);
198 --
199 -- FOR this API PROCEDURE
200 L_api_version CONSTANT NUMBER := 0.9;
201 L_api_name CONSTANT VARCHAR2(30) := 'Export_CountRequest';
202 BEGIN
206 -- Standard Call to check for call compatibility
203 -- Standard start of API savepoint
204 SAVEPOINT Export_CountRequest_PVT;
205 --
207 IF NOT FND_API.Compatible_API_Call(l_api_version
208 , p_api_version
209 , l_api_name
210 , G_PKG_NAME) THEN
211 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
212 END IF;
213 --
214 -- Initialize message list if p_init_msg_list is set to true
215 IF FND_API.to_Boolean(p_init_msg_list) THEN
216 FND_MSG_PUB.initialize;
217 END IF;
218 --
219 -- Initialisize API return status to access
220 x_return_status := FND_API.G_RET_STS_SUCCESS;
221 --
222 -- API body
223 --
224 -- Delete all error records first
225 MTL_CCEOI_PROCESS_PVT.Delete_CCEOIError(
226 p_interface_rec.cc_entry_interface_id);
227 --
228 -- INSERT the RECORD INTO the TABLE MTL_CC_ENTRIES_INTERFACE
229 MTL_CCEOI_PROCESS_PVT.Insert_CCIEntry(
230 p_interface_rec=> p_interface_rec,
231 x_return_status => L_return_status);
232 --
233 -- Set EXPORT FLAG= 1 in MTL_CYCLE_COUNT_ENTRIES
234 MTL_CCEOI_PROCESS_PVT.Set_CCExport(
235 p_api_version => 0.9,
236 X_return_status=> L_return_status,
237 x_msg_count => L_msg_count,
238 x_msg_data => L_msg_data,
239 p_cycle_count_entry_id =>
240 p_interface_rec.cycle_count_entry_id,
241 p_export_flag=> L_export_flag);
242 --
243 x_msg_count := L_msg_count;
244 x_msg_data := L_msg_data;
245 x_return_status := L_return_status;
246 --
247 -- END of API body
248 -- Standard check of p_commit
249 IF FND_API.to_Boolean(p_commit) THEN
250 COMMIT;
251 END IF;
252 -- Standard call to get message count and if count is 1, get message info
253 FND_MSG_PUB.Count_And_Get
254 (p_count => x_msg_count
255 , p_data => x_msg_data);
256 EXCEPTION
257 WHEN FND_API.G_EXC_ERROR THEN
258 --
259 ROLLBACK TO Export_CountRequest_PVT;
260 --
261 x_return_status := FND_API.G_RET_STS_ERROR;
262 --
263 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count
264 , p_data => x_msg_data);
265 --
266 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
267 --
268 ROLLBACK TO Export_CountRequest_PVT;
269 --
270 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
271 --
272 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count
273 , p_data => x_msg_data);
274 --
275 WHEN OTHERS THEN
276 --
277 ROLLBACK TO Export_CountRequest_PVT;
278 --
279 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
280 --
281 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
282 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
283 END IF;
284 --
285 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count
286 , p_data => x_msg_data);
287 END;
288 END;
289
290 --
291 -- Create unscheduled count requests
292 --Added NOCOPY hint to x_return_status,x_msg_count,x_msg_data OUT
293 --parameters to comply with GSCC File.Sql.39 standard. Bug:4410902
294 PROCEDURE Create_CountRequest(
295 p_api_version IN NUMBER ,
296 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
297 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
298 p_validation_level IN NUMBER DEFAULT FND_API.G_VALID_LEVEL_FULL,
299 x_return_status OUT NOCOPY VARCHAR2 ,
300 x_msg_count OUT NOCOPY NUMBER ,
301 x_msg_data OUT NOCOPY VARCHAR2 ,
302 p_simulate IN VARCHAR2 DEFAULT FND_API.G_FALSE,
303 p_interface_rec IN MTL_CCEOI_VAR_PVT.INV_CCEOI_TYPE )
304 IS
305 L_api_version CONSTANT NUMBER := 0.9;
306 L_api_name CONSTANT VARCHAR2(30) := 'Create_CountRequest';
307 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
308 BEGIN
309 -- Start OF comments
310 -- API name : Create_CountRequest
311 -- TYPE : Private
312 -- Pre-reqs : None
313 -- FUNCTION :
314 -- This PROCEDURE creates COUNT requests INTO TABLE
315 -- mtl_cycle_count_entries only IF unscheduled request
316 -- are allowed.
317 -- Parameters:
318 -- IN :
319 -- p_api_version IN NUMBER (required)
320 -- API Version of this procedure
321 --
322 -- p_init_msg_l IN VARCHAR2 (optional)
323 -- DEFAULT = FND_API.G_FALSE,
324 --
325 -- p_commit IN VARCHAR2 (optional)
326 -- DEFAULT = FND_API.G_FALSE
327 --
328 -- p_validation_level IN NUMBER (optional)
329 -- DEFAULT = FND_API.G_VALID_LEVEL_FULL
330 -- currently unused
331 --
332 -- p_simulate IN VARCHAR2 (optional)
333 -- determines whether to do do actual processing or just simulate it
334 -- DEFAULT = FND_API.G_FALSE
335 -- create record in mtl_cycle_count_entries
336 -- FND_API.G_TRUE
337 -- do not insert records into mtl_cycle_count_entries
338 --
339 --
340 -- p_interface_rec MTL_CC_ENTRIES_INTERFACE%ROWTYPE (required)
341 -- the interface RECORD
342 --
343 -- OUT :
344 -- X_return_status OUT NUMBER
345 -- Result of all the operations
346 --
350 --
347 -- x_msg_count OUT NUMBER,
348 --
349 -- x_msg_data OUT VARCHAR2,
351 -- Version: Current Version 0.9
352 -- Changed : Nothing
353 -- No Previous Version 0.0
354 -- Initial version 0.9
355 -- Notes : Note text
356 -- END OF comments
357
358 --
359 -- L_errorcode NUMBER := 0;
360 -- L_return_status VARCHAR2(1);
361 -- L_msg_count NUMBER;
362 -- L_msg_data VARCHAR2(240);
363
364 -- Standard start of API savepoint
365 SAVEPOINT Create_CountRequest;
366 --
367 -- Standard Call to check for call compatibility
368 IF NOT FND_API.Compatible_API_Call(l_api_version
369 , p_api_version
370 , l_api_name
371 , G_PKG_NAME) THEN
372 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
373 END IF;
374 --
375 -- Initialize message list if p_init_msg_list is set to true
376 IF FND_API.to_Boolean(p_init_msg_list) THEN
377 FND_MSG_PUB.initialize;
378 END IF;
379 --
380 -- Initialisize API return status to access
381 x_return_status := FND_API.G_RET_STS_SUCCESS;
382 --
383 -- API body
384 --
385
386 IF (l_debug = 1) THEN
387 MDEBUG( 'Create_CountRequest');
388 END IF;
389
390 -- Delete all error records first
391 MTL_CCEOI_PROCESS_PVT.Delete_CCEOIError(
392 p_interface_rec.cc_entry_interface_id);
393
394
395 MTL_CCEOI_ACTION_PVT.Validate_CountRequest
396 (p_api_version => 0.9
397 , x_return_status => x_return_status
398 , x_msg_count => x_msg_count
399 , x_msg_data => x_msg_data
400 , p_interface_rec => p_interface_rec
401 );
402
403 IF (x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
404
405 --
406 IF (l_debug = 1) THEN
407 MDEBUG( 'Process: Begin Unsch? '||to_char(MTL_CCEOI_VAR_PVT.G_CYCLE_COUNT_HEADER_REC.UNSCHEDULED_COUNT_ENTRY));
408 END IF;
409
410 -- Unscheduled entries NOT allowed
411 IF MTL_CCEOI_VAR_PVT.G_CYCLE_COUNT_HEADER_REC.UNSCHEDULED_COUNT_ENTRY=2 THEN
412 --
413 IF (l_debug = 1) THEN
414 MDEBUG( 'Process: No Unsch ');
415 END IF;
416 Process_Error(
417 p_api_version => 0.9,
418 p_interface_rec => p_interface_rec,
419 p_message_name => 'INV_CCEOI_NO_UNSCHD_COUNTS',
420 p_error_column_name => 'UNSCHEDULED_COUNT_ENTRY',
421 p_error_table_name => 'MTL_CYCLE_COUNT_HEADERS',
422 p_flags => '1$2$',
423 x_return_status => x_return_status,
424 x_msg_count => x_msg_count,
425 x_msg_data => x_msg_data);
426 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
427 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
428 END IF;
429
430 x_return_status := FND_API.G_RET_STS_ERROR;
431 RAISE FND_API.G_EXC_ERROR;
432 --
433 -- Validation ok AND OPEN request exists
434 ELSIF MTL_CCEOI_VAR_PVT.G_OPEN_REQUEST = TRUE THEN
435 --
436
437 IF (l_debug = 1) THEN
438 MDEBUG( 'Process: No Unsch -Open Request ');
439 END IF;
440 Process_Error(
441 p_api_version => 0.9,
442 p_interface_rec => p_interface_rec,
443 p_message_name => 'INV_CCEOI_COUNT_REQ_EXISTS',
444 p_error_column_name => 'CYCLE_COUNT_ENTRY_ID',
445 p_error_table_name => 'MTL_CYCLE_COUNT_ENTRIES',
446 p_flags => '1$2$',
447 x_return_status => x_return_status,
448 x_msg_count => x_msg_count,
449 x_msg_data => x_msg_data);
450 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
451 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
452 END IF;
453
454 x_return_status := FND_API.G_RET_STS_ERROR;
455 --
456 -- all ok. It IS an unscheduled entry, AND the information IS correct
457 ELSE
458 IF (l_debug = 1) THEN
459 MDEBUG( 'Create_CountRequest: Inserting CC Request');
460 END IF;
461
462 -- set unscheduled count type code
463 MTL_CCEOI_VAR_PVT.G_CYCLE_COUNT_ENTRY_REC.COUNT_TYPE_CODE := 2;
464
465 -- create count request if not in simulation mode
466 IF (p_simulate = FND_API.G_FALSE) THEN
467
468
469 -- insert count request into mtl_cycle_count_entries
470 MTL_CCEOI_PROCESS_PVT.Insert_CCEntry(
471 p_interface_rec=> p_interface_rec);
472
473 declare
474 l_interface_rec MTL_CCEOI_VAR_PVT.INV_CCEOI_TYPE :=
475 p_interface_rec;
476 l_cycle_count_entry_id number :=
477 MTL_CCEOI_VAR_PVT.G_CYCLE_COUNT_ENTRY_REC.CYCLE_COUNT_ENTRY_ID;
478 begin
479
480 -- make sure we save id's inside interface record because
481 -- open interface forms only works with id's
482 l_interface_rec.inventory_item_id :=
483 MTL_CCEOI_VAR_PVT.G_INVENTORY_ITEM_ID;
484 l_interface_rec.locator_id := MTL_CCEOI_VAR_PVT.G_LOCATOR_ID;
485
486 l_interface_rec.cycle_count_header_id :=
487 MTL_CCEOI_VAR_PVT.G_CC_HEADER_ID;
488
489 l_interface_rec.count_list_sequence :=
490 nvl(p_interface_rec.COUNT_LIST_SEQUENCE,
491 MTL_CCEOI_VAR_PVT.G_Seq_No);
492
493 l_interface_rec.cycle_count_entry_id := null;
494
495 -- update CCI entry with cycle count entry id
496 -- from that point no SKU changes can be made
497 MTL_CCEOI_PROCESS_PVT.Update_CCIEntry(l_interface_rec,
498 x_return_status);
499
500 if (x_return_status <> fnd_api.g_ret_sts_success) then
501 raise fnd_api.g_exc_unexpected_error;
502 end if;
503
504 IF (l_debug = 1) THEN
508 update mtl_cc_entries_interface
505 mdebug('updating ccei_id='||l_interface_rec.cc_entry_interface_id||' with cce_id='|| l_cycle_count_entry_id);
506 END IF;
507 -- make additional changes to interface rec sku impossible
509 set
510 cycle_count_entry_id =
511 l_cycle_count_entry_id
512 where cc_entry_interface_id =
513 l_interface_rec.cc_entry_interface_id;
514 end;
515
516 END IF;
517
518 IF MTL_CCEOI_VAR_PVT.G_REC_IN_SYSTEM = TRUE THEN
519 MTL_CCEOI_PROCESS_PVT.Set_CCEOIFlags(
520 p_api_version => 0.9,
521 x_return_status => x_return_status,
522 x_msg_count => x_msg_count,
523 x_msg_data => x_msg_data,
524 p_cc_entry_interface_id => MTL_CCEOI_VAR_PVT.G_CC_ENTRY_INTERFACE_ID,
525 p_flags => '2$0$');
526 -- the flag means no errors, successful processed
527 END IF; -- if record is in system
528 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
529 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
530 END IF;
531
532 IF (l_debug = 1) THEN
533 MDEBUG( 'Process: End of Unsch ');
534 END IF;
535 END IF;
536 END IF;
537
538 --
539 -- END of API body
540 -- Standard check of p_commit
541 IF FND_API.to_Boolean(p_commit) THEN
542 COMMIT;
543 END IF;
544 -- Standard call to get message count and if count is 1, get message info
545 FND_MSG_PUB.Count_And_Get
546 (p_count => x_msg_count
547 , p_data => x_msg_data);
548
549 EXCEPTION
550 WHEN FND_API.G_EXC_ERROR THEN
551 --
552 ROLLBACK TO Create_CountRequest;
553 --
554 x_return_status := FND_API.G_RET_STS_ERROR;
555 --
556 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count
557 , p_data => x_msg_data);
558 --
559 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
560 --
561 ROLLBACK TO Create_CountRequest;
562 --
563 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
564 --
565 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count
566 , p_data => x_msg_data);
567 --
568 WHEN OTHERS THEN
569 --
570 ROLLBACK TO Create_CountRequest;
571 --
572 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
573 --
574 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
575 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
576 END IF;
577 --
578 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count
579 , p_data => x_msg_data);
580
581 END Create_CountRequest;
582
583
584
585 --
586 -- processed count request from the interface table
587 --Added NOCOPY hint to x_return_status,x_msg_count,x_msg_data OUT
588 --parameters to comply with GSCC File.Sql.39 standard. Bug:4410902
589 PROCEDURE Process_CountRequest
590 (
591 p_api_version IN NUMBER ,
592 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
593 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
594 p_validation_level IN NUMBER DEFAULT FND_API.G_VALID_LEVEL_FULL,
595 x_return_status OUT NOCOPY VARCHAR2 ,
596 x_msg_count OUT NOCOPY NUMBER ,
597 x_msg_data OUT NOCOPY VARCHAR2 ,
598 p_simulate IN VARCHAR2 DEFAULT FND_API.G_FALSE,
599 p_interface_rec IN MTL_CCEOI_VAR_PVT.INV_CCEOI_TYPE )
600 IS
601 L_error_code NUMBER;
602 --
603 L_api_version CONSTANT NUMBER := 0.9;
604 L_err_count NUMBER := 0;
605 L_api_name CONSTANT VARCHAR2(30) := 'Process_CountRequest';
606
607 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
608 BEGIN
609 -- Start OF comments
610 -- API name : Process_CountRequest
611 -- TYPE : Private
612 -- Pre-reqs : None
613 -- FUNCTION :
614 -- Parameters:
615 -- IN :
616 -- p_api_version IN NUMBER (required)
617 -- API Version of this procedure
618 --
619 -- p_init_msg_list IN VARCHAR2 (optional)
620 -- DEFAULT = FND_API.G_FALSE,
621 --
622 -- p_commit IN VARCHAR2 (optional)
623 -- DEFAULT = FND_API.G_FALSE
624 --
625 -- p_validation_level IN NUMBER (optional)
626 -- DEFAULT = FND_API.G_VALID_LEVEL_FULL
627 -- currently unused
628 --
629 -- p_simulate IN VARCHAR2 (optional)
630 -- determines whether to do do actual processing or just simulate it
631 -- DEFAULT = FND_API.G_FALSE
632 -- - update processed info in mtl_cycle_count_entries (and any
633 -- other table necessary)
634 -- FND_API.G_TRUE
635 -- - do not insert record into mtl_cycle_count_entries
636 --
637 -- OUT :
638 -- X_return_status OUT NUMBER
639 -- Result of all the operations
640 --
641 -- x_msg_count OUT NUMBER,
642 --
643 -- x_msg_data OUT VARCHAR2,
644 --
645 -- Version: Current Version 0.9
646 -- Changed : Nothing
647 -- No Previous Version 0.0
648 -- Initial version 0.9
649 -- Notes : Note text
650 -- END OF comments
651
652 --MDEBUG( 'Process: CCEId '||to_char(MTL_CCEOI_VAR_PVT.G_CYCLE_COUNT_ENTRY_REC.CYCLE_COUNT_ENTRY_ID));
653 -- Standard start of API savepoint
654 SAVEPOINT Process_CountRequest;
655 --
656 -- Standard Call to check for call compatibility
657 IF NOT FND_API.Compatible_API_Call(l_api_version
658 , p_api_version
659 , l_api_name
660 , G_PKG_NAME) THEN
661 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
662 END IF;
663 --
667 END IF;
664 -- Initialize message list if p_init_msg_list is set to true
665 IF FND_API.to_Boolean(p_init_msg_list) THEN
666 FND_MSG_PUB.initialize;
668 --
669 -- Initialisize API return status to access
670 x_return_status := FND_API.G_RET_STS_SUCCESS;
671 --
672 -- API body
673 --
674
675 IF (l_debug = 1) THEN
676 MDEBUG( 'Process: Process count request ');
677 END IF;
678
679 IF (l_debug = 1) THEN
680 MDEBUG( 'Count Qty '||to_char(p_interface_rec.count_quantity));
681 MDEBUG( 'Level number '||to_char(FND_API.G_VALID_LEVEL_FULL));
682 END IF;
683
684 IF (l_debug = 1) THEN
685 MDEBUG( 'Process: Delete all errors ');
686 END IF;
687 -- Delete all error records first
688 MTL_CCEOI_PROCESS_PVT.Delete_CCEOIError(
689 p_interface_rec.cc_entry_interface_id);
690
691 IF (l_debug = 1) THEN
692 MDEBUG( 'Process: Validate countrequest ');
693 END IF;
694 MTL_CCEOI_ACTION_PVT.Validate_CountRequest
695 (p_api_version => 0.9
696 , x_return_status => x_return_status
697 , x_msg_count => x_msg_count
698 , x_msg_data => x_msg_data
699 , p_validation_level => 1 -- Validate for processing
700 , p_interface_rec => p_interface_rec
701 );
702
703 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
704 IF MTL_CCEOI_VAR_PVT.G_OPEN_REQUEST = FALSE
705 AND MTL_CCEOI_VAR_PVT.G_CYCLE_COUNT_HEADER_REC.UNSCHEDULED_COUNT_ENTRY=2
706 THEN
707 IF (l_debug = 1) THEN
708 MDEBUG('Process: After Validate in Process NOSCHED');
709 END IF;
710 Process_Error(
711 p_api_version => 0.9,
712 p_interface_rec => p_interface_rec,
713 p_message_name => 'INV_CCEOI_NO_UNSCHD_COUNTS',
714 p_error_column_name => 'UNSCHEDULED_COUNT_ENTRY',
715 p_error_table_name => 'MTL_CYCLE_COUNT_HEADERS',
716 p_flags => '1$2$',
717 x_return_status => x_return_status,
718 x_msg_count => x_msg_count,
719 x_msg_data => x_msg_data);
720 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
721 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
722 END IF;
723 RAISE FND_API.G_EXC_ERROR;
724
725 ELSIF MTL_CCEOI_VAR_PVT.G_OPEN_REQUEST = FALSE AND
726 MTL_CCEOI_VAR_PVT.G_CYCLE_COUNT_HEADER_REC.UNSCHEDULED_COUNT_ENTRY=1
727 THEN
728
729 IF (l_debug = 1) THEN
730 MDEBUG('Process : Create Unscheduled Entries');
731 END IF;
732 -- Create all unscheduled entries
733 MTL_CCEOI_ACTION_PVT.Create_CountRequest(
734 p_api_version => 0.9,
735 p_simulate => p_simulate,
736 x_return_status => x_return_status,
737 x_msg_count => x_msg_count,
738 x_msg_data => x_msg_data,
739 p_interface_rec => p_interface_rec );
740
741 --
742 END IF;
743
744 IF (x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
745
746 -- process the data
747 MTL_CCEOI_PROCESS_PVT.Process_Data
748 (p_api_version => 0.9
749 , p_simulate => p_simulate
750 , x_return_status => x_return_status
751 , x_msg_count => x_msg_count
752 , x_errorcode => l_error_code
753 , x_msg_data => x_msg_data
754 , p_interface_rec => p_interface_rec);
755
756 IF (l_debug = 1) THEN
757 MDEBUG('Process : After process Data '||x_return_status);
758 END IF;
759
760 IF (x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
761
762 -- count errors (left over from previous code which
763 -- had to count errors even in successful case because
764 -- of poor error handling that would lose error status
765 -- after insertion of errors
766 -- XXX this whole thing will have to go once
767 -- we verify that return status is working ok
768 L_err_count := 0;
769 BEGIN
770 select count(*)
771 into L_err_count
772 from mtl_cc_interface_errors
773 where cc_entry_interface_id =
774 p_interface_rec.cc_entry_interface_id;
775
776 EXCEPTION
777 WHEN OTHERS THEN
778 L_err_count := 0;
779 END;
780
781 IF (l_err_count <> 0) THEN
782 IF (l_debug = 1) THEN
783 mdebug('Return status success while there are errors in the table');
784 END IF;
785 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
786 /*
787 IF MTL_CCEOI_VAR_PVT.G_REC_IN_SYSTEM = TRUE THEN
788 MTL_CCEOI_PROCESS_PVT.Set_CCEOIFlags(
789 p_api_version => 0.9,
790 x_return_status => x_return_status,
791 x_msg_count => x_msg_count,
792 x_msg_data => x_msg_data,
793 p_cc_entry_interface_id =>
794 MTL_CCEOI_VAR_PVT.G_CC_ENTRY_INTERFACE_ID,
795 p_flags => '1$22');
796
797 END IF;
798
799 x_return_status := FND_API.G_RET_STS_ERROR;
800 */
801 ELSE
802 IF MTL_CCEOI_VAR_PVT.G_CC_ENTRY_REC_TMP.ENTRY_STATUS_CODE = 3
803 THEN
804 -- Added by suresh
805 IF MTL_CCEOI_VAR_PVT.G_REC_IN_SYSTEM = TRUE THEN
806 MTL_CCEOI_PROCESS_PVT.Set_CCEOIFlags(
807 p_api_version => 0.9,
808 x_return_status => x_return_status,
809 x_msg_count => x_msg_count,
810 x_msg_data => x_msg_data,
811 p_cc_entry_interface_id =>
812 MTL_CCEOI_VAR_PVT.G_CC_ENTRY_INTERFACE_ID,
813 p_flags => '2$3$');
814 END IF;
815
816 ELSE
817
818 IF MTL_CCEOI_VAR_PVT.G_REC_IN_SYSTEM = TRUE THEN
819 MTL_CCEOI_PROCESS_PVT.Set_CCEOIFlags(
820 p_api_version => 0.9,
821 x_return_status => x_return_status,
822 x_msg_count => x_msg_count,
826 p_flags => '2$0$');
823 x_msg_data => x_msg_data,
824 p_cc_entry_interface_id=>
825 MTL_CCEOI_VAR_PVT.G_CC_ENTRY_INTERFACE_ID,
827 END IF;
828
829 END IF;
830
831 IF (p_simulate = FND_API.G_FALSE) THEN
832
833 -- XXX this is leftover from previous poor error
834 -- processing. This line should go
835 IF MTL_CCEOI_VAR_PVT.G_CYCLE_COUNT_ENTRY_REC.CYCLE_COUNT_ENTRY_ID is NULL THEN
836 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
837 END IF;
838
839 MTL_CCEOI_PROCESS_PVT.Update_CCEntry(
840 MTL_CCEOI_VAR_PVT.G_CYCLE_COUNT_ENTRY_REC.CYCLE_COUNT_ENTRY_ID);
841
842 END IF;
843
844 END IF;
845
846 ELSE
847
848 IF (l_error_code = 70) THEN
849 Process_Error(
850 p_api_version => 0.9,
851 p_interface_rec => p_interface_rec,
852 p_message_name => 'INV_SERIAL_UNAVAILABLE',
853 p_error_column_name => 'SERIAL_NUMBER',
854 p_error_table_name => 'MTL_CC_ENTRIES_INTERFACE',
855 p_flags => '1$2$',
856 x_return_status => x_return_status,
857 x_msg_count => x_msg_count,
858 x_msg_data => x_msg_data);
859
860 RAISE FND_API.G_EXC_ERROR;
861 ELSE
862 -- XXX this is clearly a strange message
863 Process_Error(
864 p_api_version => 0.9,
865 p_interface_rec => p_interface_rec,
866 p_message_name => 'UNEXPECTED ERROR',
867 p_error_column_name => 'UNEXPECTED ERROR',
868 p_error_table_name => 'MTL_CC_ENTRIES_INTERFACE',
869 p_flags => '1$2$',
870 x_return_status => x_return_status,
871 x_msg_count => x_msg_count,
872 x_msg_data => x_msg_data);
873
874 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
875 END IF; -- error_code of unsuccessful process_data
876 END IF; -- process_data return status
877
878 END IF; -- x_return_status = true/create's return status
879
880 END IF; -- validation's return status
881
882 --
883 -- END of API body
884 -- Standard check of p_commit
885 IF FND_API.to_Boolean(p_commit) THEN
886 COMMIT;
887 END IF;
888 -- Standard call to get message count and if count is 1, get message info
889 FND_MSG_PUB.Count_And_Get
890 (p_count => x_msg_count
891 , p_data => x_msg_data);
892
893 EXCEPTION
894 WHEN FND_API.G_EXC_ERROR THEN
895 --
896 x_return_status := FND_API.G_RET_STS_ERROR;
897 --
898 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count
899 , p_data => x_msg_data);
900 --
901 IF (l_debug = 1) THEN
902 MDEBUG('Process : Exc Err' || sqlerrm);
903 END IF;
904
905 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
906 --
907 --
908 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
909 --
910 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count
911 , p_data => x_msg_data);
912 --
913 IF (l_debug = 1) THEN
914 MDEBUG('Process : Exc Unexp Err ' || sqlerrm);
915 END IF;
916
917 WHEN OTHERS THEN
918 --
919 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
920 --
921 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
922 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
923 END IF;
924 --
925 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count
926 , p_data => x_msg_data);
927 IF (l_debug = 1) THEN
928 MDEBUG('Process_CountRequest : Exc_Others ' || sqlerrm);
929 END IF;
930
931 END Process_CountRequest;
932
933 -- Validate the records in the interface table
934 --Added NOCOPY hint to x_return_status,x_msg_count,x_msg_data OUT
935 --parameters to comply with GSCC File.Sql.39 standard. Bug:4410902
936 PROCEDURE Validate_CountRequest(
937 p_api_version IN NUMBER ,
938 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
939 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
940 p_validation_level IN NUMBER DEFAULT FND_API.G_VALID_LEVEL_NONE,
941 x_return_status OUT NOCOPY VARCHAR2 ,
942 x_msg_count OUT NOCOPY NUMBER ,
943 x_msg_data OUT NOCOPY VARCHAR2 ,
944 p_interface_rec IN MTL_CCEOI_VAR_PVT.INV_CCEOI_TYPE )
945 IS
946 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
947 BEGIN
948 -- Start OF comments
949 -- API name : Validate_CountRequest
950 -- TYPE : Private
951 -- Pre-reqs : None
952 -- FUNCTION :
953 -- Validates the COUNT request information OF the given
954 -- interface RECORD
955 -- Parameters:
956 -- IN :
957 -- p_api_version IN NUMBER (required)
958 -- API Version of this procedure
959 --
960 -- p_init_msg_list IN VARCHAR2 (optional)
961 -- DEFAULT = FND_API.G_FALSE,
962 --
963 -- p_commit IN VARCHAR2 (optional)
964 -- DEFAULT = FND_API.G_FALSE
965 --
966 -- p_validation_level IN NUMBER DEFAULT 0 (optional- defaulted)
967 -- 0 = FND_API.G_VALID_LEVEL_NONE (no processing),
968 -- 1 = Validate FOR processing
969 --
970 --
971 -- p_interface_rec IN MTL_CC_ENTRIES_INTERFACE%ROWTYPE (required)
972 -- Cycle COUNT entries interface RECORD
973 --
974 -- OUT :
975 -- X_return_status OUT NUMBER
976 -- Result of all the operations
977 --
978 -- x_msg_count OUT NUMBER,
979 --
980 -- x_msg_data OUT VARCHAR2,
981 --
985
982 -- RETURN value OF the Error status
983 -- 0 = successful
984 -- Version: Current Version 0.9
986 -- Changed : Nothing
987 -- No Previous Version 0.0
988 -- Initial version 0.9
989 -- Notes : Note text
990 -- END OF comments
991 DECLARE
992 L_inventory_rec MTL_CCEOI_VAR_PVT.Inv_Item_rec_type;
993 L_sku_rec MTL_CCEOI_VAR_PVT.INV_SKU_REC_TYPE;
994 L_locator_rec MTL_CCEOI_VAR_PVT.INV_LOCATOR_REC_TYPE;
995 L_subinventory MTL_CYCLE_COUNT_ENTRIES.SUBINVENTORY%TYPE;
996 l_derivable_item_sku BOOLEAN := TRUE;
997 L_errorcode NUMBER := 0;
998 L_return_status VARCHAR2(30);
999
1000 l_count NUMBER :=0; -- bug 14828280
1001
1002 --
1003 L_api_version CONSTANT NUMBER := 0.9;
1004 L_api_name CONSTANT VARCHAR2(30) := 'Validate_CountRequest';
1005 l_simulate varchar2(1);
1006 l_same_seq_not_closed_entries NUMBER; -- to count open count_list_seq
1007 -- so that we do not attempt to create count_list_sequence with already
1008 -- existing number
1009
1010 -- BEGIN INVCONV
1011 CURSOR cur_get_item_attr (
1012 cp_inventory_item_id NUMBER
1013 , cp_organization_id NUMBER
1014 ) IS
1015 -- tracking_quantity_ind (P-Primary, PS-Primary and Secondary)
1016 -- secondary_default_ind (F-Fixed, D-Default, N-No Default)
1017 SELECT msi.tracking_quantity_ind
1018 , msi.secondary_default_ind
1019 , msi.secondary_uom_code
1020 , msi.process_costing_enabled_flag
1021 , mtp.process_enabled_flag
1022 FROM mtl_system_items msi, mtl_parameters mtp
1023 WHERE mtp.organization_id = cp_organization_id
1024 AND msi.organization_id = mtp.organization_id
1025 AND msi.inventory_item_id = cp_inventory_item_id;
1026 -- END INVCONV
1027
1028 BEGIN
1029 -- Standard start of API savepoint
1030 SAVEPOINT Validate_CountRequest;
1031 --
1032 -- Standard Call to check for call compatibility
1033 IF NOT FND_API.Compatible_API_Call(l_api_version
1034 , p_api_version
1035 , l_api_name
1036 , G_PKG_NAME) THEN
1037 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1038 END IF;
1039 --
1040 -- Initialize message list if p_init_msg_list is set to true
1041 IF FND_API.to_Boolean(p_init_msg_list) THEN
1042 FND_MSG_PUB.initialize;
1043 END IF;
1044 --
1045 -- Initialisize API return status to access
1046 L_return_status := FND_API.G_RET_STS_SUCCESS;
1047 --
1048 -- API body
1049 --
1050
1051 -- determine whether this is a simulation run in which we
1052 -- shall not modify non-interface related tables
1053 if (p_interface_rec.action_code = mtl_cceoi_var_pvt.g_valsim) then
1054 l_simulate := fnd_api.g_true;
1055 else
1056 l_simulate := fnd_api.g_false;
1057 end if;
1058
1059 -- Delete all error records first
1060 MTL_CCEOI_PROCESS_PVT.Delete_CCEOIError(
1061 p_interface_rec.cc_entry_interface_id);
1062
1063 IF (l_debug = 1) THEN
1064 mdebug('Validate_CountRequest: Deleted errors');
1065 END IF;
1066 --
1067 IF p_interface_rec.cc_entry_interface_id IS NULL THEN
1068 L_errorcode := 21;
1069 FND_MESSAGE.SET_NAME('INV', 'INV_CCEOI_NO_IFACE_ID');
1070 FND_MSG_PUB.Add;
1071 RAISE FND_API.G_EXC_ERROR;
1072 END IF;
1073
1074 -- Current processed interface RECORD
1075 MTL_CCEOI_VAR_PVT.G_CC_ENTRY_INTERFACE_ID :=
1076 p_interface_rec.cc_entry_interface_id;
1077 --
1078 IF (l_debug = 1) THEN
1079 MDEBUG( 'G_CC_ENTRY_INTERFACE_ID ='|| MTL_CCEOI_VAR_PVT.G_CC_ENTRY_INTERFACE_ID);
1080 END IF;
1081
1082
1083
1084 IF (l_debug = 1) THEN
1085 MDEBUG( 'Validation Level ='|| to_char(p_validation_level));
1086 END IF;
1087 --mdebug('Check Cycle Count Header: ' || to_char(p_interface_rec.cycle_count_header_id) || '.');
1088
1089 -- Check the cycle COUNT header Process Step 1
1090 MTL_CCEOI_PROCESS_PVT.Validate_CHeader(
1091 p_api_version => 0.9,
1092 x_return_status => L_return_status,
1093 x_msg_count => x_msg_count,
1094 x_msg_data => x_msg_data,
1095 X_ErrorCode => L_errorcode,
1096 p_cycle_count_header_id => p_interface_rec.cycle_count_header_id,
1097 p_cycle_count_header_name => p_interface_rec.cycle_count_header_name
1098 );
1099 IF (l_debug = 1) THEN
1100 mdebug('Return CHeader='||L_return_status);
1101 END IF;
1102 IF L_errorcode <>0 AND L_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1103 GOTO end_VALIDATE;
1104 IF (l_debug = 1) THEN
1105 mdebug('Cycle Count Header Error');
1106 END IF;
1107 END IF;
1108 --
1109 IF (l_debug = 1) THEN
1110 MDEBUG( 'Sequence Count List');
1111 END IF;
1112 -- Check the COUNT list sequence Step 2
1113 MTL_CCEOI_PROCESS_PVT.Validate_CountListSeq(
1114 p_api_version => 0.9,
1115 x_return_status => L_return_status,
1116 x_msg_count => x_msg_count,
1117 x_msg_data => x_msg_data,
1118 X_ErrorCode => L_errorcode,
1119 -- p_cycle_count_header_id => p_interface_rec.cycle_count_header_id,
1120 p_cycle_count_header_id => MTL_CCEOI_VAR_PVT.G_CC_HEADER_ID,
1124 MTL_CCEOI_VAR_PVT.G_CYCLE_COUNT_HEADER_REC.ORGANIZATION_ID
1121 p_cycle_count_entry_id => p_interface_rec.cycle_count_entry_id,
1122 p_count_list_sequence => p_interface_rec.count_list_sequence,
1123 p_organization_id =>
1125 -- p_organization_id => p_interface_rec.organization_id
1126 );
1127 --
1128 IF (l_debug = 1) THEN
1129 mdebug('Return Sequence Count List='||L_return_status);
1130 END IF;
1131
1132 -- this is added for unschedule entry
1133 MTL_CCEOI_VAR_PVT.G_Seq_No := p_interface_rec.count_list_sequence ;
1134 IF L_errorcode = 65 then -- NEW NULL count_list_sequence
1135 begin
1136 l_derivable_item_sku := FALSE;
1137
1138
1139 select next_user_count_sequence
1140 into MTL_CCEOI_VAR_PVT.G_Seq_No
1141 FROM mtl_cycle_count_headers where
1142 cycle_count_header_id = MTL_CCEOI_VAR_PVT.G_CC_HEADER_ID;
1143
1144
1145 -- the following piece of code makes sure that count list sequence
1146 -- which we just derived is not conflicting with another already
1147 -- existing count list sequence that is still not closed
1148 -- (not counted, counted, waiting for approval, marked for recount)
1149 l_same_seq_not_closed_entries := 1;
1150 MTL_CCEOI_VAR_PVT.G_Seq_No := MTL_CCEOI_VAR_PVT.G_Seq_No - 1;
1151
1152 while (l_same_seq_not_closed_entries <> 0) loop
1153 MTL_CCEOI_VAR_PVT.G_Seq_No := MTL_CCEOI_VAR_PVT.G_Seq_No + 1;
1154 select count(*)
1155 into l_same_seq_not_closed_entries
1156 from mtl_cycle_count_entries
1157 where count_list_sequence = MTL_CCEOI_VAR_PVT.G_Seq_No
1158 and cycle_count_header_id = MTL_CCEOI_VAR_PVT.G_CC_HEADER_ID
1159 and entry_status_code not in (4, 5);
1160 IF (l_debug = 1) THEN
1161 mdebug(l_same_seq_not_closed_entries||' not processed requests found for count list sequence=' || MTL_CCEOI_VAR_PVT.G_Seq_No);
1162 END IF;
1163 end loop;
1164
1165 --
1166 IF (l_debug = 1) THEN
1167 MDEBUG( 'Create New Sequence ='||to_char(MTL_CCEOI_VAR_PVT.G_Seq_No));
1168 END IF;
1169 --
1170 update mtl_cycle_count_headers
1171 set next_user_count_sequence = MTL_CCEOI_VAR_PVT.G_Seq_No + 1
1172 where cycle_count_header_id = p_interface_rec.cycle_count_header_id;
1173 L_errorcode := 0;
1174 L_return_status := FND_API.G_RET_STS_SUCCESS ;
1175 exception
1176 when others then null;
1177 end;
1178 ELSIF L_errorcode = 66 then -- new count_list_sequence not found in CCE
1179 L_errorcode := 0;
1180 L_return_status := FND_API.G_RET_STS_SUCCESS ;
1181 IF (l_debug = 1) THEN
1182 MDEBUG( 'Existing New Sequence ='||to_char(MTL_CCEOI_VAR_PVT.G_Seq_No));
1183 END IF;
1184 l_derivable_item_sku := FALSE;
1185 END IF;
1186 IF (l_debug = 1) THEN
1187 MDEBUG( 'New Sequence ='||to_char(MTL_CCEOI_VAR_PVT.G_Seq_No));
1188 END IF;
1189 -- Else condition is introduced by suresh to errored out with correct status
1190 IF L_errorcode <>0 AND L_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1191 IF (l_debug = 1) THEN
1192 mdebug('Error Sequence Count List');
1193 END IF;
1194 GOTO end_VALIDATE;
1195 ELSE
1196 IF l_derivable_item_sku = FALSE THEN
1197
1198 --
1199 -- Item information
1200 L_inventory_rec.INVENTORY_ITEM_ID :=
1201 p_interface_rec.INVENTORY_ITEM_ID;
1202 L_inventory_rec.ITEM_SEGMENT1 := p_interface_rec.ITEM_SEGMENT1;
1203 L_inventory_rec.ITEM_SEGMENT2 := p_interface_rec.ITEM_SEGMENT2;
1204 L_inventory_rec.ITEM_SEGMENT3 := p_interface_rec.ITEM_SEGMENT3;
1205 L_inventory_rec.ITEM_SEGMENT4 := p_interface_rec.ITEM_SEGMENT4;
1206 L_inventory_rec.ITEM_SEGMENT5 := p_interface_rec.ITEM_SEGMENT5;
1207 L_inventory_rec.ITEM_SEGMENT6 := p_interface_rec.ITEM_SEGMENT6;
1208 L_inventory_rec.ITEM_SEGMENT7 := p_interface_rec.ITEM_SEGMENT7;
1209 L_inventory_rec.ITEM_SEGMENT8 := p_interface_rec.ITEM_SEGMENT8;
1210 L_inventory_rec.ITEM_SEGMENT9 := p_interface_rec.ITEM_SEGMENT9;
1211 L_inventory_rec.ITEM_SEGMENT10 := p_interface_rec.ITEM_SEGMENT10;
1212 L_inventory_rec.ITEM_SEGMENT11 := p_interface_rec.ITEM_SEGMENT11;
1213 L_inventory_rec.ITEM_SEGMENT12 := p_interface_rec.ITEM_SEGMENT12;
1214 L_inventory_rec.ITEM_SEGMENT13 := p_interface_rec.ITEM_SEGMENT13;
1215 L_inventory_rec.ITEM_SEGMENT14 := p_interface_rec.ITEM_SEGMENT14;
1216 L_inventory_rec.ITEM_SEGMENT15 := p_interface_rec.ITEM_SEGMENT15;
1217 L_inventory_rec.ITEM_SEGMENT16 := p_interface_rec.ITEM_SEGMENT16;
1218 L_inventory_rec.ITEM_SEGMENT17 := p_interface_rec.ITEM_SEGMENT17;
1219 L_inventory_rec.ITEM_SEGMENT18 := p_interface_rec.ITEM_SEGMENT18;
1220 L_inventory_rec.ITEM_SEGMENT19 := p_interface_rec.ITEM_SEGMENT19;
1221 L_inventory_rec.ITEM_SEGMENT20 := p_interface_rec.ITEM_SEGMENT20;
1222 --
1223 -- SKU information
1224 L_sku_rec.REVISION := p_interface_rec.REVISION;
1225 L_sku_rec.LOT_NUMBER := p_interface_rec.LOT_NUMBER;
1226 L_sku_rec.SERIAL_NUMBER := p_interface_rec.SERIAL_NUMBER;
1227 --
1228 -- Locator information
1229 L_LOCATOR_REC.LOCATOR_ID := P_INTERFACE_REC.LOCATOR_ID;
1230 L_LOCATOR_REC.LOCATOR_SEGMENT1 := P_INTERFACE_REC.LOCATOR_SEGMENT1;
1231 L_LOCATOR_REC.LOCATOR_SEGMENT2 := P_INTERFACE_REC.LOCATOR_SEGMENT2;
1232 L_LOCATOR_REC.LOCATOR_SEGMENT3 := P_INTERFACE_REC.LOCATOR_SEGMENT3;
1233 L_LOCATOR_REC.LOCATOR_SEGMENT4 := P_INTERFACE_REC.LOCATOR_SEGMENT4;
1237 L_LOCATOR_REC.LOCATOR_SEGMENT8 := P_INTERFACE_REC.LOCATOR_SEGMENT8;
1234 L_LOCATOR_REC.LOCATOR_SEGMENT5 := P_INTERFACE_REC.LOCATOR_SEGMENT5;
1235 L_LOCATOR_REC.LOCATOR_SEGMENT6 := P_INTERFACE_REC.LOCATOR_SEGMENT6;
1236 L_LOCATOR_REC.LOCATOR_SEGMENT7 := P_INTERFACE_REC.LOCATOR_SEGMENT7;
1238 L_LOCATOR_REC.LOCATOR_SEGMENT9 := P_INTERFACE_REC.LOCATOR_SEGMENT9;
1239 L_LOCATOR_REC.LOCATOR_SEGMENT10 := P_INTERFACE_REC.LOCATOR_SEGMENT10;
1240 L_LOCATOR_REC.LOCATOR_SEGMENT11 := P_INTERFACE_REC.LOCATOR_SEGMENT11;
1241 L_LOCATOR_REC.LOCATOR_SEGMENT12 := P_INTERFACE_REC.LOCATOR_SEGMENT12;
1242 L_LOCATOR_REC.LOCATOR_SEGMENT13 := P_INTERFACE_REC.LOCATOR_SEGMENT13;
1243 L_LOCATOR_REC.LOCATOR_SEGMENT14 := P_INTERFACE_REC.LOCATOR_SEGMENT14;
1244 L_LOCATOR_REC.LOCATOR_SEGMENT15 := P_INTERFACE_REC.LOCATOR_SEGMENT15;
1245 L_LOCATOR_REC.LOCATOR_SEGMENT16 := P_INTERFACE_REC.LOCATOR_SEGMENT16;
1246 L_LOCATOR_REC.LOCATOR_SEGMENT17 := P_INTERFACE_REC.LOCATOR_SEGMENT17;
1247 L_LOCATOR_REC.LOCATOR_SEGMENT18 := P_INTERFACE_REC.LOCATOR_SEGMENT18;
1248 L_LOCATOR_REC.LOCATOR_SEGMENT19 := P_INTERFACE_REC.LOCATOR_SEGMENT19;
1249 L_LOCATOR_REC.LOCATOR_SEGMENT20 := P_INTERFACE_REC.LOCATOR_SEGMENT20;
1250
1251 -- subinventory info
1252 L_subinventory := p_interface_rec.subinventory;
1253
1254 ELSE -- if can derive item and sku info
1255
1256 IF (l_debug = 1) THEN
1257 MDEBUG('Validate_CountRequest: Derived item id: ' || L_INVENTORY_REC.inventory_item_id);
1258 END IF;
1259
1260 l_inventory_rec.inventory_item_id :=
1261 MTL_CCEOI_VAR_PVT.G_CYCLE_COUNT_ENTRY_REC.inventory_item_id;
1262
1263 l_sku_rec.revision :=
1264 MTL_CCEOI_VAR_PVT.G_CYCLE_COUNT_ENTRY_REC.REVISION;
1265
1266 l_sku_rec.lot_number :=
1267 MTL_CCEOI_VAR_PVT.G_CYCLE_COUNT_ENTRY_REC.LOT_NUMBER;
1268
1269 l_sku_rec.serial_number :=
1270 MTL_CCEOI_VAR_PVT.G_CYCLE_COUNT_ENTRY_REC.SERIAL_NUMBER;
1271
1272 l_locator_rec.locator_id :=
1273 MTL_CCEOI_VAR_PVT.G_CYCLE_COUNT_ENTRY_REC.locator_id;
1274
1275 l_subinventory :=
1276 MTL_CCEOI_VAR_PVT.G_CYCLE_COUNT_ENTRY_REC.subinventory;
1277
1278 END IF; -- IF l_derivable_item_sku = FALSE
1279
1280
1281 --
1282 IF (l_debug = 1) THEN
1283 mdebug('Check ITEM SKU');
1284 END IF;
1285 -- Check the item AND SKU information Step 3
1286 MTL_CCEOI_PROCESS_PVT.Validate_ItemSKU(
1287 p_api_version => 0.9,
1288 x_return_status => L_return_status,
1289 x_msg_count => x_msg_count,
1290 x_msg_data => x_msg_data,
1291 x_ErrorCode => L_errorcode,
1292 p_cycle_count_header_id => mtl_cceoi_var_pvt.g_cc_header_id,
1293 p_inventory_item_rec => L_inventory_rec,
1294 p_sku_rec => L_sku_rec,
1295 p_subinventory => L_subinventory,
1296 p_locator_rec => L_locator_rec,
1297 p_organization_id =>
1298 MTL_CCEOI_VAR_PVT.G_CYCLE_COUNT_HEADER_REC.ORGANIZATION_ID,
1299 p_simulate => l_simulate
1300 -- p_organization_id => p_interface_rec.organization_id
1301 );
1302 --
1303 IF L_errorcode<>0 AND L_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1304 IF (l_debug = 1) THEN
1305 MDEBUG( 'Error ItemSKU');
1306 END IF;
1307
1308 GOTO end_VALIDATE;
1309 END IF;
1310 END IF;
1311 --
1312 IF (l_debug = 1) THEN
1313 MDEBUG( 'Just before Validate for processing');
1314 MDEBUG( 'validation_level='||p_validation_level);
1315 END IF;
1316
1317 -- BEGIN INVCONV
1318 OPEN cur_get_item_attr
1319 (
1320 MTL_CCEOI_VAR_PVT.G_INVENTORY_ITEM_ID,
1321 MTL_CCEOI_VAR_PVT.G_CYCLE_COUNT_HEADER_REC.ORGANIZATION_ID
1322 );
1323
1324 FETCH cur_get_item_attr
1325 INTO MTL_CCEOI_VAR_PVT.g_tracking_quantity_ind,
1326 MTL_CCEOI_VAR_PVT.g_secondary_default_ind,
1327 MTL_CCEOI_VAR_PVT.g_secondary_uom_code,
1328 MTL_CCEOI_VAR_PVT.g_process_costing_enabled_flag,
1329 MTL_CCEOI_VAR_PVT.g_process_enabled_flag;
1330
1331 CLOSE cur_get_item_attr;
1332 -- END INVCONV
1333
1334 IF p_validation_level = 1 THEN
1335 --
1336 IF (l_debug = 1) THEN
1337 mdebug('Valdite for processing');
1338 END IF;
1339
1340 /*mdebug('UOMQuantity');
1341 IF (l_debug = 1) THEN
1342 mdebug('msg_data'||x_msg_data);
1343 mdebug('return'||L_return_status);
1344 mdebug('count'||to_char(x_msg_count));
1345 mdebug('L_errorcode'||to_char(L_errorcode));
1346 mdebug('PrQTY'||to_char(p_interface_rec.primary_uom_quantity));
1347 mdebug('count_uom'||p_interface_rec.count_uom);
1348 mdebug('count_unit_of_measure'||p_interface_rec.count_unit_of_measure);
1349 mdebug('ORGANIZATION_ID'||to_char(MTL_CCEOI_VAR_PVT.G_CYCLE_COUNT_HEADER_REC.ORGANIZATION_ID));
1350 mdebug('NVENTORY_ITEM_ID'||to_char(MTL_CCEOI_VAR_PVT.G_INVENTORY_ITEM_ID));
1351 mdebug('count_quantity'||to_char(p_interface_rec.count_quantity));
1352 mdebug('SERIAL_NUMBER'||MTL_CCEOI_VAR_PVT.G_SKU_REC.SERIAL_NUMBER);
1353 mdebug('subinventory'||p_interface_rec.subinventory);
1354 mdebug('REVISION'||MTL_CCEOI_VAR_PVT.G_SKU_REC.REVISION);
1355 mdebug('LOT_NUMBER'||MTL_CCEOI_VAR_PVT.G_SKU_REC.LOT_NUMBER);
1356 mdebug('system_quantity'||to_char(p_interface_rec.system_quantity));
1357 END IF;
1358 */ -- Check the UOM AND quantity information Step 4
1359 MTL_CCEOI_PROCESS_PVT.Validate_UOMQuantity(
1360 p_api_version => 0.9,
1361 x_return_status => L_return_status,
1365 p_primary_uom_quantity=>p_interface_rec.primary_uom_quantity,
1362 x_msg_count => x_msg_count,
1363 x_msg_data => x_msg_data,
1364 x_ErrorCode => L_errorcode,
1366 p_count_uom=> p_interface_rec.count_uom,
1367 p_count_unit_of_measure=>p_interface_rec.count_unit_of_measure,
1368 p_organization_id=>
1369 MTL_CCEOI_VAR_PVT.G_CYCLE_COUNT_HEADER_REC.ORGANIZATION_ID,
1370 p_inventory_item_id=> MTL_CCEOI_VAR_PVT.G_INVENTORY_ITEM_ID,
1371 p_count_quantity=>p_interface_rec.count_quantity,
1372 p_serial_number => MTL_CCEOI_VAR_PVT.G_SKU_REC.SERIAL_NUMBER,
1373 p_subinventory => MTL_CCEOI_VAR_PVT.G_SUBINVENTORY,
1374 p_revision => MTL_CCEOI_VAR_PVT.G_SKU_REC.REVISION,
1375 p_lot_number => MTL_CCEOI_VAR_PVT.G_SKU_REC.LOT_NUMBER,
1376 p_system_quantity => p_interface_rec.system_quantity,
1377 p_secondary_system_quantity => p_interface_rec.secondary_system_quantity -- INVCONV
1378 );
1379 --
1380 IF (l_debug = 1) THEN
1381 mdebug('UOMQunatity Error ='||to_char(L_errorcode));
1382 mdebug('Errortext ='||x_msg_data);
1383 END IF;
1384
1385 IF L_errorcode <>0 AND L_return_status <> FND_API.G_RET_STS_SUCCESS
1386 THEN
1387 IF (l_debug = 1) THEN
1388 mdebug('Error UOM Quantity');
1389 END IF;
1390 GOTO end_VALIDATE;
1391 END IF;
1392
1393 -- BEGIN INVCONV
1394 IF (l_debug = 1) THEN
1395 mdebug('Validate Secondary UOM and Quantity');
1396 END IF;
1397
1398 MTL_CCEOI_PROCESS_PVT.Validate_SecondaryUOMQty(
1399 p_api_version => 0.9
1400 , x_return_status => l_return_status
1401 , x_msg_count => x_msg_count
1402 , x_msg_data => x_msg_data
1403 , x_errorcode => l_errorcode
1404 , p_organization_id => MTL_CCEOI_VAR_PVT.G_CYCLE_COUNT_HEADER_REC.ORGANIZATION_ID
1405 , p_inventory_item_id => MTL_CCEOI_VAR_PVT.G_INVENTORY_ITEM_ID
1406 , p_serial_number => MTL_CCEOI_VAR_PVT.G_SKU_REC.SERIAL_NUMBER
1407 , p_subinventory => MTL_CCEOI_VAR_PVT.G_SUBINVENTORY
1408 , p_revision => MTL_CCEOI_VAR_PVT.G_SKU_REC.REVISION
1409 , p_lot_number => MTL_CCEOI_VAR_PVT.G_SKU_REC.LOT_NUMBER
1410 , p_secondary_uom => p_interface_rec.secondary_uom
1411 , p_secondary_unit_of_measure => p_interface_rec.secondary_unit_of_measure
1412 , p_secondary_count_quantity => p_interface_rec.secondary_count_quantity
1413 , p_secondary_system_quantity => p_interface_rec.secondary_system_quantity
1414 , p_tracking_quantity_ind => MTL_CCEOI_VAR_PVT.G_TRACKING_QUANTITY_IND
1415 , p_secondary_default_ind => MTL_CCEOI_VAR_PVT.G_SECONDARY_DEFAULT_IND
1416 );
1417
1418 IF (l_debug = 1) THEN
1419 mdebug('Secondary UOM Quantity Error = '||to_char(L_errorcode));
1420 mdebug('Errortext = '||x_msg_data);
1421 END IF;
1422
1423 IF L_errorcode <>0 AND L_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1424 IF (l_debug = 1) THEN
1425 mdebug('Error Secondary UOM Quantity');
1426 END IF;
1427 GOTO end_VALIDATE;
1428 END IF;
1429 -- END INVCONV
1430
1431
1432 IF (L_DEBUG = 1) THEN
1433 mdebug(' Validation for transaction reason id ');
1434 END IF;
1435
1436 -- bug 14828280
1437
1438 if P_INTERFACE_REC.TRANSACTION_REASON_ID is not null then
1439
1440 SELECT COUNT(*)
1441 into l_count
1442 FROM MTL_TRANSACTION_REASONS
1443 WHERE REASON_ID = P_INTERFACE_REC.TRANSACTION_REASON_ID
1444 and NVL(DISABLE_DATE, SYSDATE+1) > SYSDATE
1445 AND (NVL(fnd_profile.value_wnps('INV_TRANS_REASON_SECURITY'), 'N') = 'N')
1446 OR reason_id IN
1447 (SELECT reason_id
1448 FROM mtl_trans_reason_security
1449 WHERE (responsibility_id = fnd_global.resp_id
1450 OR NVL(RESPONSIBILITY_ID, -1) = -1)
1451 );
1452
1453 IF (L_DEBUG = 1) THEN
1454 MDEBUG(' L_COUNT: '||L_COUNT);
1455 END IF;
1456 IF L_COUNT = 0 THEN
1457
1458 FND_MESSAGE.SET_NAME('INV', 'INV_INT_REACODE');
1459 FND_MSG_PUB.Add;
1460
1461 l_return_status := FND_API.G_RET_STS_ERROR;
1462
1463 MTL_CCEOI_PROCESS_PVT.Insert_CCEOIError(
1464 P_CC_ENTRY_INTERFACE_ID => MTL_CCEOI_VAR_PVT.G_CC_ENTRY_INTERFACE_ID
1465 , P_ERROR_COLUMN_NAME => 'TRANSACTION_REASON_ID'
1466 , p_error_table_name => 'MTL_CC_ENTRIES_INTERFACE'
1467 , p_message_name => 'INV_INT_REACODE'
1468 );
1469 GOTO end_VALIDATE;
1470
1471 END IF;
1472
1473 END IF;
1474
1475 -- end of bug 14828280
1476
1477 --
1478 IF (l_debug = 1) THEN
1479 mdebug('Count Date' || to_char(p_interface_rec.employee_id));
1480 END IF;
1481 -- Check the counter AND DATE information Step 5
1482 MTL_CCEOI_PROCESS_PVT.Validate_CDate_Counter(
1483 p_api_version => 0.9,
1484 x_return_status => L_return_status,
1485 x_msg_count => x_msg_count,
1486 x_msg_data => x_msg_data,
1487 x_ErrorCode => L_errorcode,
1488 p_count_date => p_interface_rec.count_date,
1489 p_employee_id => p_interface_rec.employee_id,
1490 p_employee_name => p_interface_rec.employee_full_name
1491 );
1492 --
1496 MDEBUG( 'Error Count Date '||to_char(l_errorcode));
1493 IF L_errorcode <>0 AND L_return_status <> FND_API.G_RET_STS_SUCCESS
1494 THEN
1495 IF (l_debug = 1) THEN
1497 END IF;
1498 GOTO end_VALIDATE;
1499 END IF;
1500 END IF;
1501 --
1502 -- if procedures returned successfully marked for deletion
1503 -- only if action code = Validate
1504 --
1505 <<end_VALIDATE>>
1506 x_return_status := L_return_status;
1507 IF (l_debug = 1) THEN
1508 MDEBUG( 'Return= '||x_return_status);
1509 MDEBUG( 'Validation_level ='||to_number(p_validation_level));
1510 END IF;
1511
1512 --
1513 -- UPDATE the interface TABLE flags
1514 IF L_return_status = FND_API.G_RET_STS_SUCCESS
1515 AND p_validation_level=1 THEN
1516 IF MTL_CCEOI_VAR_PVT.G_REC_IN_SYSTEM = TRUE THEN
1517 MTL_CCEOI_PROCESS_PVT.Set_CCEOIFlags(
1518 p_api_version => 0.9,
1519 x_return_status => L_return_status,
1520 x_msg_count => x_msg_count,
1521 x_msg_data => x_msg_data,
1522 p_cc_entry_interface_id => MTL_CCEOI_VAR_PVT.G_CC_ENTRY_INTERFACE_ID, --p_interface_rec.cc_entry_interface_id,
1523 p_flags => '2$51');
1524 END IF; -- record is stored in the interface
1525 IF (l_debug = 1) THEN
1526 MDEBUG( 'Successfully Validated ');
1527 END IF;
1528 -- the flag means no errors, successful validated, valid
1529 ELSIF
1530 L_return_status = FND_API.G_RET_STS_SUCCESS
1531 AND p_validation_level=0 THEN
1532 IF MTL_CCEOI_VAR_PVT.G_REC_IN_SYSTEM = TRUE THEN
1533 MTL_CCEOI_PROCESS_PVT.Set_CCEOIFlags(
1534 p_api_version => 0.9,
1535 x_return_status => L_return_status,
1536 x_msg_count => x_msg_count,
1537 x_msg_data => x_msg_data,
1538 p_cc_entry_interface_id => MTL_CCEOI_VAR_PVT.G_CC_ENTRY_INTERFACE_ID, --p_interface_rec.cc_entry_interface_id,
1539 p_flags => '2$51');
1540 -- the flag means no errors,marked FOR deletion,
1541 -- successful validated, valid
1542 END IF;
1543 IF (l_debug = 1) THEN
1544 MDEBUG( 'Successfully Validated-');
1545 END IF;
1546 ELSE
1547 -- IF error AND online, INSERT the interface RECORD
1548 IF MTL_CCEOI_VAR_PVT.G_REC_IN_SYSTEM = FALSE THEN
1549
1550 IF (l_debug = 1) THEN
1551 MDEBUG('Inserting interface entry');
1552 END IF;
1553 MTL_CCEOI_PROCESS_PVT.Insert_CCIEntry(
1554 p_interface_rec => p_interface_rec,
1555 x_return_status => L_return_status);
1556 END IF;
1557 --
1558 -- SET the flags
1559 MTL_CCEOI_PROCESS_PVT.Set_CCEOIFlags(
1560 p_api_version => 0.9,
1561 x_return_status => L_return_status,
1562 x_msg_count => x_msg_count,
1563 x_msg_data => x_msg_data,
1564 p_cc_entry_interface_id => MTL_CCEOI_VAR_PVT.G_CC_ENTRY_INTERFACE_ID, --p_interface_rec.cc_entry_interface_id,
1565 p_flags => '1$22');
1566 IF (l_debug = 1) THEN
1567 MDEBUG( 'Processed with errors ');
1568 END IF;
1569 -- the flag means errors, processed with errors, not valid
1570 END IF;
1571 --
1572 -- END of API body
1573 -- Standard check of p_commit
1574 IF FND_API.to_Boolean(p_commit) THEN
1575 COMMIT;
1576 END IF;
1577 -- Standard call to get message count and if count is 1, get message info
1578 FND_MSG_PUB.Count_And_Get
1579 (p_count => x_msg_count
1580 , p_data => x_msg_data);
1581 EXCEPTION
1582 WHEN FND_API.G_EXC_ERROR THEN
1583 --
1584 --ROLLBACK TO Validate_CountRequest;
1585 --
1586 IF (l_debug = 1) THEN
1587 mdebug('Validate_CountRequest: Error' || sqlerrm);
1588 END IF;
1589 x_return_status := FND_API.G_RET_STS_ERROR;
1590 --
1591 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count
1592 , p_data => x_msg_data);
1593 --
1594 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1595 --
1596 --ROLLBACK TO Validate_CountRequest;
1597 --
1598 IF (l_debug = 1) THEN
1599 mdebug('Validate_CountRequest: Error' || sqlerrm);
1600 END IF;
1601 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1602 --
1603 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count
1604 , p_data => x_msg_data);
1605 --
1606 WHEN OTHERS THEN
1607 --
1608 --ROLLBACK TO Validate_CountRequest;
1609 --
1610 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1611 --
1612 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1613 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
1614 END IF;
1615 --
1616 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count
1617 , p_data => x_msg_data);
1618 END;
1619 END;
1620 --
1621 -- validates and simulates records from interface table
1622 --Added NOCOPY hint to x_return_status,x_msg_count,x_msg_data OUT
1623 --parameters to comply with GSCC File.Sql.39 standard. Bug:4410902
1624 Procedure ValSim_CountRequest(
1625 p_api_version IN NUMBER ,
1626 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
1627 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
1628 p_validation_level IN NUMBER DEFAULT FND_API.G_VALID_LEVEL_FULL,
1629 x_return_status OUT NOCOPY VARCHAR2 ,
1630 x_msg_count OUT NOCOPY NUMBER ,
1631 x_msg_data OUT NOCOPY VARCHAR2 ,
1632 p_interface_rec IN MTL_CCEOI_VAR_PVT.INV_CCEOI_TYPE )
1633 IS
1634 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1638 -- TYPE : Private
1635 BEGIN
1636 -- Start OF comments
1637 -- API name : ValSim_CountRequest
1639 -- Pre-reqs : None
1640 -- FUNCTION :
1641 -- Parameters:
1642 -- IN :
1643 -- p_api_version IN NUMBER (required)
1644 -- API Version of this procedure
1645 --
1646 -- p_init_msg_list IN VARCHAR2 (optional)
1647 -- DEFAULT = FND_API.G_FALSE,
1648 --
1649 -- p_commit IN VARCHAR2 (optional)
1650 -- DEFAULT = FND_API.G_FALSE
1651 --
1652 -- p_validation_level IN NUMBER (optional)
1653 -- DEFAULT = FND_API.G_VALID_LEVEL_FULL,
1654 --
1655 --
1656 -- OUT :
1657 -- X_return_status OUT NUMBER
1658 -- Result of all the operations
1659 --
1660 -- x_msg_count OUT NUMBER,
1661 --
1662 -- x_msg_data OUT VARCHAR2,
1663 --
1664 -- Version: Current Version 0.9
1665 -- Changed : Nothing
1666 -- No Previous Version 0.0
1667 -- Initial version 0.9
1668 -- Notes : Note text
1669 -- END OF comments
1670 DECLARE
1671 L_return_status VARCHAR2(30);
1672 --
1673 L_api_version CONSTANT NUMBER := 0.9;
1674 L_api_name CONSTANT VARCHAR2(30) := 'ValSim_CountRequest';
1675 BEGIN
1676 -- Standard start of API savepoint
1677 SAVEPOINT ValSim_CountRequest;
1678 --
1679 -- Standard Call to check for call compatibility
1680 IF NOT FND_API.Compatible_API_Call(l_api_version
1681 , p_api_version
1682 , l_api_name
1683 , G_PKG_NAME) THEN
1684 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1685 END IF;
1686 --
1687 -- Initialize message list if p_init_msg_list is set to true
1688 IF FND_API.to_Boolean(p_init_msg_list) THEN
1689 FND_MSG_PUB.initialize;
1690 END IF;
1691 --
1692 -- Initialisize API return status to access
1693 L_return_status := FND_API.G_RET_STS_SUCCESS;
1694 --
1695 -- API body
1696 --
1697 -- Delete all error records first
1698 MTL_CCEOI_PROCESS_PVT.Delete_CCEOIError(
1699 p_interface_rec.cc_entry_interface_id);
1700 --
1701 MTL_CCEOI_ACTION_PVT.Process_CountRequest
1702 -- Prozedur
1703 (p_api_version => 0.9
1704 , p_validation_level => 1
1705 -- withoutsaving
1706 , x_return_status => L_return_status
1707 , x_msg_count => x_msg_count
1708 , x_msg_data => x_msg_data
1709 , p_interface_rec => p_interface_rec
1710 );
1711 --
1712 IF L_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1713 --
1714 -- If online mode insert interface record first
1715 IF MTL_CCEOI_VAR_PVT.G_REC_IN_SYSTEM = FALSE THEN
1716 MTL_CCEOI_PROCESS_PVT.Insert_CCIEntry(
1717 p_interface_rec => p_interface_rec,
1718 x_return_status => L_return_status);
1719 END IF;
1720 --
1721 MTL_CCEOI_PROCESS_PVT.Set_CCEOIFlags
1722 -- Prozedur
1723 (p_api_version => 0.9
1724 , x_return_status => L_return_status
1725 , x_msg_count => x_msg_count
1726 , x_msg_data => x_msg_data
1727 , p_cc_entry_interface_id => MTL_CCEOI_VAR_PVT.G_CC_ENTRY_INTERFACE_ID, --p_interface_rec.cc_entry_interface_id
1728 p_flags => '1$2$');
1729 -- error, processed with errors
1730 --
1731 ELSE
1732 IF MTL_CCEOI_VAR_PVT.G_REC_IN_SYSTEM = TRUE THEN
1733 MTL_CCEOI_PROCESS_PVT.Set_CCEOIFlags
1734 (p_api_version => 0.9
1735 , x_return_status => L_return_status
1736 , x_msg_count => x_msg_count
1737 , x_msg_data => x_msg_data
1738 , p_cc_entry_interface_id => MTL_CCEOI_VAR_PVT.G_CC_ENTRY_INTERFACE_ID, --p_interface_rec.cc_entry_interface_id
1739 p_flags => '2$6$');
1740 -- no error, succesfully validated and simulated
1741 END IF;
1742 END IF;
1743 --
1744 x_return_status := L_return_status;
1745 --
1746 -- END of API body
1747 -- Standard check of p_commit
1748 IF FND_API.to_Boolean(p_commit) THEN
1749 COMMIT;
1750 END IF;
1751 -- Standard call to get message count and if count is 1, get message info
1752 FND_MSG_PUB.Count_And_Get
1753 (p_count => x_msg_count
1754 , p_data => x_msg_data);
1755 EXCEPTION
1756 WHEN FND_API.G_EXC_ERROR THEN
1757 --
1758 ROLLBACK TO ValSim_CountRequest;
1759 --
1760 x_return_status := FND_API.G_RET_STS_ERROR;
1761 --
1762 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count
1763 , p_data => x_msg_data);
1764 --
1765 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1766 --
1767 ROLLBACK TO ValSim_CountRequest;
1768 --
1769 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1770 --
1771 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count
1772 , p_data => x_msg_data);
1773 --
1774 WHEN OTHERS THEN
1775 --
1776 ROLLBACK TO ValSim_CountRequest;
1777 --
1778 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1779 --
1780 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1781 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
1782 END IF;
1783 --
1784 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count
1785 , p_data => x_msg_data);
1786 END;
1790 --Added NOCOPY hint to x_return_status,x_msg_count,x_msg_data OUT
1787 END;
1788 --
1789 -- Updated or inserted the interface record table
1791 --parameters to comply with GSCC File.Sql.39 standard. Bug:4410902
1792 PROCEDURE Update_Insert_CountRequest(
1793 p_api_version IN NUMBER ,
1794 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
1795 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
1796 p_validation_level IN NUMBER DEFAULT FND_API.G_VALID_LEVEL_FULL,
1797 x_return_status OUT NOCOPY VARCHAR2 ,
1798 x_msg_count OUT NOCOPY NUMBER ,
1799 x_msg_data OUT NOCOPY VARCHAR2 ,
1800 p_interface_rec IN MTL_CCEOI_VAR_PVT.INV_CCEOI_TYPE )
1801 IS
1802 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1803 BEGIN
1804 -- Start OF comments
1805 -- API name : UpdateInsert_CountRequest
1806 -- TYPE : Private
1807 -- Pre-reqs : None
1808 -- FUNCTION :
1809 -- This PROCEDURE IS CALLED FROM Import Public API.
1810 -- FOR Background mode it IS possible to INSERT OR UPDATE
1811 -- the interface TABLE
1812 -- Parameters:
1813 -- IN :
1814 -- p_api_version IN NUMBER (required)
1815 -- API Version of this procedure
1816 --
1817 -- p_init_msg_l IN VARCHAR2 (optional)
1818 -- DEFAULT = FND_API.G_FALSE,
1819 --
1820 -- p_commit IN VARCHAR2 (optional)
1821 -- DEFAULT = FND_API.G_FALSE
1822 --
1823 -- p_validation_level IN NUMBER (optional)
1824 -- DEFAULT = FND_API.G_VALID_LEVEL_FULL,
1825 --
1826 -- p_interface_rec MTL_CC_ENTRIES_INTERFACE%ROWTYPE (required)
1827 -- the interface RECORD
1828 --
1829 -- OUT :
1830 -- X_return_status OUT NUMBER
1831 -- Result of all the operations
1832 --
1833 -- x_msg_count OUT NUMBER,
1834 --
1835 -- x_msg_data OUT VARCHAR2,
1836 --
1837 -- Version: Current Version 0.9
1838 -- Changed : Nothing
1839 -- No Previous Version 0.0
1840 -- Initial version 0.9
1841 -- Notes : Note text
1842 -- END OF comments
1843 DECLARE
1844 --
1845 L_dummy NUMBER := TO_NUMBER(NULL);
1846 L_return_status VARCHAR2(30);
1847 L_msg_count NUMBER;
1848 L_msg_data VARCHAR2(100);
1849 --
1850 L_api_version CONSTANT NUMBER := 0.9;
1851 L_api_name CONSTANT VARCHAR2(30) := 'UpdateInsert_CountRequest';
1852 BEGIN
1853 -- Standard start of API savepoint
1854 SAVEPOINT UpdateInsert_CountRequest;
1855 --
1856 -- Standard Call to check for call compatibility
1857 IF NOT FND_API.Compatible_API_Call(l_api_version
1858 , p_api_version
1859 , l_api_name
1860 , G_PKG_NAME) THEN
1861 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1862 END IF;
1863 --
1864 -- Initialize message list if p_init_msg_list is set to true
1865 IF FND_API.to_Boolean(p_init_msg_list) THEN
1866 FND_MSG_PUB.initialize;
1867 END IF;
1868 --
1869 -- Initialisize API return status to access
1870 L_return_status := FND_API.G_RET_STS_SUCCESS;
1871 --
1872 -- API body
1873 --
1874 -- If the validation error out, because the values
1875 -- are wrong the system quantity is updated to NULL
1876 MTL_CCEOI_ACTION_PVT.Validate_CountRequest(
1877 p_api_version => 0.9,
1878 x_msg_count => L_msg_count,
1879 x_msg_data => L_msg_data,
1880 x_return_status => x_return_status,
1881 p_interface_rec => p_interface_rec);
1882 --
1883 BEGIN
1884 SELECT cc_entry_interface_id
1885 INTO
1886 L_dummy
1887 FROM
1888 mtl_cc_entries_interface
1889 WHERE
1890 cc_entry_interface_id =
1891 p_interface_rec.cc_entry_interface_id;
1892 --
1893 --
1894 MTL_CCEOI_PROCESS_PVT.Update_CCIEntry(
1895 p_interface_rec => p_interface_rec
1896 , x_return_status => L_return_status);
1897 --
1898 IF L_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1899 FND_MESSAGE.SET_NAME('INV', 'INV_CCEOI_UPDATE_FAILED');
1900 FND_MSG_PUB.Add;
1901 RAISE FND_API.G_EXC_ERROR;
1902 END IF;
1903 --
1904 EXCEPTION
1905 WHEN NO_DATA_FOUND THEN
1906 IF (l_debug = 1) THEN
1907 Mdebug('Before Insert_CCIEntry');
1908 END IF;
1909
1910 MTL_CCEOI_PROCESS_PVT.Insert_CCIEntry(
1911 p_interface_rec => p_interface_rec
1912 , x_return_status => L_return_status);
1913 --
1914 IF (l_debug = 1) THEN
1915 Mdebug('After Insert_CCIEntry');
1916 END IF;
1917 IF L_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1918 FND_MESSAGE.SET_NAME('INV', 'INV_CCEOI_INSERT_FAILED');
1919 FND_MSG_PUB.Add;
1920 RAISE FND_API.G_EXC_ERROR;
1921 END IF;
1922 END;
1923 --
1924 x_return_status := L_return_status;
1925 --
1926 -- END of API body
1927 -- Standard check of p_commit
1928 IF FND_API.to_Boolean(p_commit) THEN
1929 COMMIT;
1930 END IF;
1931 -- Standard call to get message count and if count is 1, get message info
1932 FND_MSG_PUB.Count_And_Get
1933 (p_count => x_msg_count
1934 , p_data => x_msg_data);
1935 EXCEPTION WHEN FND_API.G_EXC_ERROR THEN
1936 --
1937 x_return_status := FND_API.G_RET_STS_ERROR;
1938 --
1939 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count
1940 , p_data => x_msg_data);
1941 --
1942 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1943 --
1944 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1945 --
1946 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count
1947 , p_data => x_msg_data);
1948 --
1949 WHEN OTHERS THEN
1950 --
1951 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1952 --
1953 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1954 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
1955 END IF;
1956 --
1957 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count
1958 , p_data => x_msg_data); END;
1959 END;
1960 END MTL_CCEOI_ACTION_PVT;