[Home] [Help]
PACKAGE BODY: APPS.MTL_CCEOI_ACTION_PVT
Source
1 PACKAGE BODY MTL_CCEOI_ACTION_PVT AS
2 /* $Header: INVVCCAB.pls 120.2 2005/06/22 09:50:38 appldev 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
51 x_return_status := FND_API.G_RET_STS_SUCCESS;
52 --
53 -- API body
54 --
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
203 -- Standard start of API savepoint
204 SAVEPOINT Export_CountRequest_PVT;
205 --
206 -- Standard Call to check for call compatibility
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 --
347 -- x_msg_count OUT NUMBER,
348 --
349 -- x_msg_data OUT VARCHAR2,
350 --
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);
429
426 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
427 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
428 END IF;
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
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
508 update mtl_cc_entries_interface
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 --
577 --
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;
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 --
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;
667 END IF;
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,
722 END IF;
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;
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,
823 x_msg_data => x_msg_data,
824 p_cc_entry_interface_id=>
825 MTL_CCEOI_VAR_PVT.G_CC_ENTRY_INTERFACE_ID,
826 p_flags => '2$0$');
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$',
873
870 x_return_status => x_return_status,
871 x_msg_count => x_msg_count,
872 x_msg_data => x_msg_data);
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 --
982 -- RETURN value OF the Error status
983 -- 0 = successful
984 -- Version: Current Version 0.9
985
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_api_version CONSTANT NUMBER := 0.9;
1001 L_api_name CONSTANT VARCHAR2(30) := 'Validate_CountRequest';
1002 l_simulate varchar2(1);
1003 l_same_seq_not_closed_entries NUMBER; -- to count open count_list_seq
1004 -- so that we do not attempt to create count_list_sequence with already
1005 -- existing number
1006
1007 -- BEGIN INVCONV
1008 CURSOR cur_get_item_attr (
1009 cp_inventory_item_id NUMBER
1013 -- secondary_default_ind (F-Fixed, D-Default, N-No Default)
1010 , cp_organization_id NUMBER
1011 ) IS
1012 -- tracking_quantity_ind (P-Primary, PS-Primary and Secondary)
1014 SELECT msi.tracking_quantity_ind
1015 , msi.secondary_default_ind
1016 , msi.secondary_uom_code
1017 , msi.process_costing_enabled_flag
1018 , mtp.process_enabled_flag
1019 FROM mtl_system_items msi, mtl_parameters mtp
1020 WHERE mtp.organization_id = cp_organization_id
1021 AND msi.organization_id = mtp.organization_id
1022 AND msi.inventory_item_id = cp_inventory_item_id;
1023 -- END INVCONV
1024
1025 BEGIN
1026 -- Standard start of API savepoint
1027 SAVEPOINT Validate_CountRequest;
1028 --
1029 -- Standard Call to check for call compatibility
1030 IF NOT FND_API.Compatible_API_Call(l_api_version
1031 , p_api_version
1032 , l_api_name
1033 , G_PKG_NAME) THEN
1034 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1035 END IF;
1036 --
1037 -- Initialize message list if p_init_msg_list is set to true
1038 IF FND_API.to_Boolean(p_init_msg_list) THEN
1039 FND_MSG_PUB.initialize;
1040 END IF;
1041 --
1042 -- Initialisize API return status to access
1043 L_return_status := FND_API.G_RET_STS_SUCCESS;
1044 --
1045 -- API body
1046 --
1047
1048 -- determine whether this is a simulation run in which we
1049 -- shall not modify non-interface related tables
1050 if (p_interface_rec.action_code = mtl_cceoi_var_pvt.g_valsim) then
1051 l_simulate := fnd_api.g_true;
1052 else
1053 l_simulate := fnd_api.g_false;
1054 end if;
1055
1056 -- Delete all error records first
1057 MTL_CCEOI_PROCESS_PVT.Delete_CCEOIError(
1058 p_interface_rec.cc_entry_interface_id);
1059
1060 IF (l_debug = 1) THEN
1061 mdebug('Validate_CountRequest: Deleted errors');
1062 END IF;
1063 --
1064 IF p_interface_rec.cc_entry_interface_id IS NULL THEN
1065 L_errorcode := 21;
1066 FND_MESSAGE.SET_NAME('INV', 'INV_CCEOI_NO_IFACE_ID');
1067 FND_MSG_PUB.Add;
1068 RAISE FND_API.G_EXC_ERROR;
1069 END IF;
1070
1071 -- Current processed interface RECORD
1072 MTL_CCEOI_VAR_PVT.G_CC_ENTRY_INTERFACE_ID :=
1073 p_interface_rec.cc_entry_interface_id;
1074 --
1075 IF (l_debug = 1) THEN
1076 MDEBUG( 'G_CC_ENTRY_INTERFACE_ID ='|| MTL_CCEOI_VAR_PVT.G_CC_ENTRY_INTERFACE_ID);
1077 END IF;
1078
1079
1080
1081 IF (l_debug = 1) THEN
1082 MDEBUG( 'Validation Level ='|| to_char(p_validation_level));
1083 END IF;
1084 --mdebug('Check Cycle Count Header: ' || to_char(p_interface_rec.cycle_count_header_id) || '.');
1085
1086 -- Check the cycle COUNT header Process Step 1
1087 MTL_CCEOI_PROCESS_PVT.Validate_CHeader(
1088 p_api_version => 0.9,
1089 x_return_status => L_return_status,
1090 x_msg_count => x_msg_count,
1091 x_msg_data => x_msg_data,
1092 X_ErrorCode => L_errorcode,
1093 p_cycle_count_header_id => p_interface_rec.cycle_count_header_id,
1094 p_cycle_count_header_name => p_interface_rec.cycle_count_header_name
1095 );
1096 IF (l_debug = 1) THEN
1097 mdebug('Return CHeader='||L_return_status);
1098 END IF;
1099 IF L_errorcode <>0 AND L_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1100 GOTO end_VALIDATE;
1101 IF (l_debug = 1) THEN
1102 mdebug('Cycle Count Header Error');
1103 END IF;
1104 END IF;
1105 --
1106 IF (l_debug = 1) THEN
1107 MDEBUG( 'Sequence Count List');
1108 END IF;
1109 -- Check the COUNT list sequence Step 2
1110 MTL_CCEOI_PROCESS_PVT.Validate_CountListSeq(
1111 p_api_version => 0.9,
1112 x_return_status => L_return_status,
1113 x_msg_count => x_msg_count,
1114 x_msg_data => x_msg_data,
1115 X_ErrorCode => L_errorcode,
1116 -- p_cycle_count_header_id => p_interface_rec.cycle_count_header_id,
1117 p_cycle_count_header_id => MTL_CCEOI_VAR_PVT.G_CC_HEADER_ID,
1118 p_cycle_count_entry_id => p_interface_rec.cycle_count_entry_id,
1119 p_count_list_sequence => p_interface_rec.count_list_sequence,
1120 p_organization_id =>
1121 MTL_CCEOI_VAR_PVT.G_CYCLE_COUNT_HEADER_REC.ORGANIZATION_ID
1122 -- p_organization_id => p_interface_rec.organization_id
1123 );
1124 --
1125 IF (l_debug = 1) THEN
1126 mdebug('Return Sequence Count List='||L_return_status);
1127 END IF;
1128
1129 -- this is added for unschedule entry
1130 MTL_CCEOI_VAR_PVT.G_Seq_No := p_interface_rec.count_list_sequence ;
1131 IF L_errorcode = 65 then -- NEW NULL count_list_sequence
1132 begin
1133 l_derivable_item_sku := FALSE;
1134
1135
1136 select next_user_count_sequence
1137 into MTL_CCEOI_VAR_PVT.G_Seq_No
1138 FROM mtl_cycle_count_headers where
1139 cycle_count_header_id = MTL_CCEOI_VAR_PVT.G_CC_HEADER_ID;
1140
1141
1145 -- (not counted, counted, waiting for approval, marked for recount)
1142 -- the following piece of code makes sure that count list sequence
1143 -- which we just derived is not conflicting with another already
1144 -- existing count list sequence that is still not closed
1146 l_same_seq_not_closed_entries := 1;
1147 MTL_CCEOI_VAR_PVT.G_Seq_No := MTL_CCEOI_VAR_PVT.G_Seq_No - 1;
1148
1149 while (l_same_seq_not_closed_entries <> 0) loop
1150 MTL_CCEOI_VAR_PVT.G_Seq_No := MTL_CCEOI_VAR_PVT.G_Seq_No + 1;
1151 select count(*)
1152 into l_same_seq_not_closed_entries
1153 from mtl_cycle_count_entries
1154 where count_list_sequence = MTL_CCEOI_VAR_PVT.G_Seq_No
1155 and cycle_count_header_id = MTL_CCEOI_VAR_PVT.G_CC_HEADER_ID
1156 and entry_status_code not in (4, 5);
1157 IF (l_debug = 1) THEN
1158 mdebug(l_same_seq_not_closed_entries||' not processed requests found for count list sequence=' || MTL_CCEOI_VAR_PVT.G_Seq_No);
1159 END IF;
1160 end loop;
1161
1162 --
1163 IF (l_debug = 1) THEN
1164 MDEBUG( 'Create New Sequence ='||to_char(MTL_CCEOI_VAR_PVT.G_Seq_No));
1165 END IF;
1166 --
1167 update mtl_cycle_count_headers
1168 set next_user_count_sequence = MTL_CCEOI_VAR_PVT.G_Seq_No + 1
1169 where cycle_count_header_id = p_interface_rec.cycle_count_header_id;
1170 L_errorcode := 0;
1171 L_return_status := FND_API.G_RET_STS_SUCCESS ;
1172 exception
1173 when others then null;
1174 end;
1175 ELSIF L_errorcode = 66 then -- new count_list_sequence not found in CCE
1176 L_errorcode := 0;
1177 L_return_status := FND_API.G_RET_STS_SUCCESS ;
1178 IF (l_debug = 1) THEN
1179 MDEBUG( 'Existing New Sequence ='||to_char(MTL_CCEOI_VAR_PVT.G_Seq_No));
1180 END IF;
1181 l_derivable_item_sku := FALSE;
1182 END IF;
1183 IF (l_debug = 1) THEN
1184 MDEBUG( 'New Sequence ='||to_char(MTL_CCEOI_VAR_PVT.G_Seq_No));
1185 END IF;
1186 -- Else condition is introduced by suresh to errored out with correct status
1187 IF L_errorcode <>0 AND L_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1188 IF (l_debug = 1) THEN
1189 mdebug('Error Sequence Count List');
1190 END IF;
1191 GOTO end_VALIDATE;
1192 ELSE
1193 IF l_derivable_item_sku = FALSE THEN
1194
1195 --
1196 -- Item information
1197 L_inventory_rec.INVENTORY_ITEM_ID :=
1198 p_interface_rec.INVENTORY_ITEM_ID;
1199 L_inventory_rec.ITEM_SEGMENT1 := p_interface_rec.ITEM_SEGMENT1;
1200 L_inventory_rec.ITEM_SEGMENT2 := p_interface_rec.ITEM_SEGMENT2;
1201 L_inventory_rec.ITEM_SEGMENT3 := p_interface_rec.ITEM_SEGMENT3;
1202 L_inventory_rec.ITEM_SEGMENT4 := p_interface_rec.ITEM_SEGMENT4;
1203 L_inventory_rec.ITEM_SEGMENT5 := p_interface_rec.ITEM_SEGMENT5;
1204 L_inventory_rec.ITEM_SEGMENT6 := p_interface_rec.ITEM_SEGMENT6;
1205 L_inventory_rec.ITEM_SEGMENT7 := p_interface_rec.ITEM_SEGMENT7;
1206 L_inventory_rec.ITEM_SEGMENT8 := p_interface_rec.ITEM_SEGMENT8;
1207 L_inventory_rec.ITEM_SEGMENT9 := p_interface_rec.ITEM_SEGMENT9;
1208 L_inventory_rec.ITEM_SEGMENT10 := p_interface_rec.ITEM_SEGMENT10;
1209 L_inventory_rec.ITEM_SEGMENT11 := p_interface_rec.ITEM_SEGMENT11;
1210 L_inventory_rec.ITEM_SEGMENT12 := p_interface_rec.ITEM_SEGMENT12;
1211 L_inventory_rec.ITEM_SEGMENT13 := p_interface_rec.ITEM_SEGMENT13;
1212 L_inventory_rec.ITEM_SEGMENT14 := p_interface_rec.ITEM_SEGMENT14;
1213 L_inventory_rec.ITEM_SEGMENT15 := p_interface_rec.ITEM_SEGMENT15;
1214 L_inventory_rec.ITEM_SEGMENT16 := p_interface_rec.ITEM_SEGMENT16;
1215 L_inventory_rec.ITEM_SEGMENT17 := p_interface_rec.ITEM_SEGMENT17;
1216 L_inventory_rec.ITEM_SEGMENT18 := p_interface_rec.ITEM_SEGMENT18;
1217 L_inventory_rec.ITEM_SEGMENT19 := p_interface_rec.ITEM_SEGMENT19;
1218 L_inventory_rec.ITEM_SEGMENT20 := p_interface_rec.ITEM_SEGMENT20;
1219 --
1220 -- SKU information
1221 L_sku_rec.REVISION := p_interface_rec.REVISION;
1222 L_sku_rec.LOT_NUMBER := p_interface_rec.LOT_NUMBER;
1223 L_sku_rec.SERIAL_NUMBER := p_interface_rec.SERIAL_NUMBER;
1224 --
1225 -- Locator information
1226 L_LOCATOR_REC.LOCATOR_ID := P_INTERFACE_REC.LOCATOR_ID;
1227 L_LOCATOR_REC.LOCATOR_SEGMENT1 := P_INTERFACE_REC.LOCATOR_SEGMENT1;
1228 L_LOCATOR_REC.LOCATOR_SEGMENT2 := P_INTERFACE_REC.LOCATOR_SEGMENT2;
1229 L_LOCATOR_REC.LOCATOR_SEGMENT3 := P_INTERFACE_REC.LOCATOR_SEGMENT3;
1230 L_LOCATOR_REC.LOCATOR_SEGMENT4 := P_INTERFACE_REC.LOCATOR_SEGMENT4;
1231 L_LOCATOR_REC.LOCATOR_SEGMENT5 := P_INTERFACE_REC.LOCATOR_SEGMENT5;
1232 L_LOCATOR_REC.LOCATOR_SEGMENT6 := P_INTERFACE_REC.LOCATOR_SEGMENT6;
1233 L_LOCATOR_REC.LOCATOR_SEGMENT7 := P_INTERFACE_REC.LOCATOR_SEGMENT7;
1234 L_LOCATOR_REC.LOCATOR_SEGMENT8 := P_INTERFACE_REC.LOCATOR_SEGMENT8;
1235 L_LOCATOR_REC.LOCATOR_SEGMENT9 := P_INTERFACE_REC.LOCATOR_SEGMENT9;
1236 L_LOCATOR_REC.LOCATOR_SEGMENT10 := P_INTERFACE_REC.LOCATOR_SEGMENT10;
1237 L_LOCATOR_REC.LOCATOR_SEGMENT11 := P_INTERFACE_REC.LOCATOR_SEGMENT11;
1238 L_LOCATOR_REC.LOCATOR_SEGMENT12 := P_INTERFACE_REC.LOCATOR_SEGMENT12;
1242 L_LOCATOR_REC.LOCATOR_SEGMENT16 := P_INTERFACE_REC.LOCATOR_SEGMENT16;
1239 L_LOCATOR_REC.LOCATOR_SEGMENT13 := P_INTERFACE_REC.LOCATOR_SEGMENT13;
1240 L_LOCATOR_REC.LOCATOR_SEGMENT14 := P_INTERFACE_REC.LOCATOR_SEGMENT14;
1241 L_LOCATOR_REC.LOCATOR_SEGMENT15 := P_INTERFACE_REC.LOCATOR_SEGMENT15;
1243 L_LOCATOR_REC.LOCATOR_SEGMENT17 := P_INTERFACE_REC.LOCATOR_SEGMENT17;
1244 L_LOCATOR_REC.LOCATOR_SEGMENT18 := P_INTERFACE_REC.LOCATOR_SEGMENT18;
1245 L_LOCATOR_REC.LOCATOR_SEGMENT19 := P_INTERFACE_REC.LOCATOR_SEGMENT19;
1246 L_LOCATOR_REC.LOCATOR_SEGMENT20 := P_INTERFACE_REC.LOCATOR_SEGMENT20;
1247
1248 -- subinventory info
1249 L_subinventory := p_interface_rec.subinventory;
1250
1251 ELSE -- if can derive item and sku info
1252
1253 IF (l_debug = 1) THEN
1254 MDEBUG('Validate_CountRequest: Derived item id: ' || L_INVENTORY_REC.inventory_item_id);
1255 END IF;
1256
1257 l_inventory_rec.inventory_item_id :=
1258 MTL_CCEOI_VAR_PVT.G_CYCLE_COUNT_ENTRY_REC.inventory_item_id;
1259
1260 l_sku_rec.revision :=
1261 MTL_CCEOI_VAR_PVT.G_CYCLE_COUNT_ENTRY_REC.REVISION;
1262
1263 l_sku_rec.lot_number :=
1264 MTL_CCEOI_VAR_PVT.G_CYCLE_COUNT_ENTRY_REC.LOT_NUMBER;
1265
1266 l_sku_rec.serial_number :=
1267 MTL_CCEOI_VAR_PVT.G_CYCLE_COUNT_ENTRY_REC.SERIAL_NUMBER;
1268
1269 l_locator_rec.locator_id :=
1270 MTL_CCEOI_VAR_PVT.G_CYCLE_COUNT_ENTRY_REC.locator_id;
1271
1272 l_subinventory :=
1273 MTL_CCEOI_VAR_PVT.G_CYCLE_COUNT_ENTRY_REC.subinventory;
1274
1275 END IF; -- IF l_derivable_item_sku = FALSE
1276
1277
1278 --
1279 IF (l_debug = 1) THEN
1280 mdebug('Check ITEM SKU');
1281 END IF;
1282 -- Check the item AND SKU information Step 3
1283 MTL_CCEOI_PROCESS_PVT.Validate_ItemSKU(
1284 p_api_version => 0.9,
1285 x_return_status => L_return_status,
1286 x_msg_count => x_msg_count,
1287 x_msg_data => x_msg_data,
1288 x_ErrorCode => L_errorcode,
1289 p_cycle_count_header_id => mtl_cceoi_var_pvt.g_cc_header_id,
1290 p_inventory_item_rec => L_inventory_rec,
1291 p_sku_rec => L_sku_rec,
1292 p_subinventory => L_subinventory,
1293 p_locator_rec => L_locator_rec,
1294 p_organization_id =>
1295 MTL_CCEOI_VAR_PVT.G_CYCLE_COUNT_HEADER_REC.ORGANIZATION_ID,
1296 p_simulate => l_simulate
1297 -- p_organization_id => p_interface_rec.organization_id
1298 );
1299 --
1300 IF L_errorcode<>0 AND L_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1301 IF (l_debug = 1) THEN
1302 MDEBUG( 'Error ItemSKU');
1303 END IF;
1304
1305 GOTO end_VALIDATE;
1306 END IF;
1307 END IF;
1308 --
1309 IF (l_debug = 1) THEN
1310 MDEBUG( 'Just before Validate for processing');
1311 MDEBUG( 'validation_level='||p_validation_level);
1312 END IF;
1313
1314 -- BEGIN INVCONV
1315 OPEN cur_get_item_attr
1316 (
1317 MTL_CCEOI_VAR_PVT.G_INVENTORY_ITEM_ID,
1318 MTL_CCEOI_VAR_PVT.G_CYCLE_COUNT_HEADER_REC.ORGANIZATION_ID
1319 );
1320
1321 FETCH cur_get_item_attr
1322 INTO MTL_CCEOI_VAR_PVT.g_tracking_quantity_ind,
1323 MTL_CCEOI_VAR_PVT.g_secondary_default_ind,
1324 MTL_CCEOI_VAR_PVT.g_secondary_uom_code,
1325 MTL_CCEOI_VAR_PVT.g_process_costing_enabled_flag,
1326 MTL_CCEOI_VAR_PVT.g_process_enabled_flag;
1327
1328 CLOSE cur_get_item_attr;
1329 -- END INVCONV
1330
1331 IF p_validation_level = 1 THEN
1332 --
1333 IF (l_debug = 1) THEN
1334 mdebug('Valdite for processing');
1335 END IF;
1336
1337 /*mdebug('UOMQuantity');
1338 IF (l_debug = 1) THEN
1339 mdebug('msg_data'||x_msg_data);
1340 mdebug('return'||L_return_status);
1341 mdebug('count'||to_char(x_msg_count));
1342 mdebug('L_errorcode'||to_char(L_errorcode));
1343 mdebug('PrQTY'||to_char(p_interface_rec.primary_uom_quantity));
1344 mdebug('count_uom'||p_interface_rec.count_uom);
1345 mdebug('count_unit_of_measure'||p_interface_rec.count_unit_of_measure);
1346 mdebug('ORGANIZATION_ID'||to_char(MTL_CCEOI_VAR_PVT.G_CYCLE_COUNT_HEADER_REC.ORGANIZATION_ID));
1347 mdebug('NVENTORY_ITEM_ID'||to_char(MTL_CCEOI_VAR_PVT.G_INVENTORY_ITEM_ID));
1348 mdebug('count_quantity'||to_char(p_interface_rec.count_quantity));
1349 mdebug('SERIAL_NUMBER'||MTL_CCEOI_VAR_PVT.G_SKU_REC.SERIAL_NUMBER);
1350 mdebug('subinventory'||p_interface_rec.subinventory);
1351 mdebug('REVISION'||MTL_CCEOI_VAR_PVT.G_SKU_REC.REVISION);
1352 mdebug('LOT_NUMBER'||MTL_CCEOI_VAR_PVT.G_SKU_REC.LOT_NUMBER);
1353 mdebug('system_quantity'||to_char(p_interface_rec.system_quantity));
1354 END IF;
1355 */ -- Check the UOM AND quantity information Step 4
1356 MTL_CCEOI_PROCESS_PVT.Validate_UOMQuantity(
1357 p_api_version => 0.9,
1358 x_return_status => L_return_status,
1359 x_msg_count => x_msg_count,
1360 x_msg_data => x_msg_data,
1361 x_ErrorCode => L_errorcode,
1362 p_primary_uom_quantity=>p_interface_rec.primary_uom_quantity,
1366 MTL_CCEOI_VAR_PVT.G_CYCLE_COUNT_HEADER_REC.ORGANIZATION_ID,
1363 p_count_uom=> p_interface_rec.count_uom,
1364 p_count_unit_of_measure=>p_interface_rec.count_unit_of_measure,
1365 p_organization_id=>
1367 p_inventory_item_id=> MTL_CCEOI_VAR_PVT.G_INVENTORY_ITEM_ID,
1368 p_count_quantity=>p_interface_rec.count_quantity,
1369 p_serial_number => MTL_CCEOI_VAR_PVT.G_SKU_REC.SERIAL_NUMBER,
1370 p_subinventory => MTL_CCEOI_VAR_PVT.G_SUBINVENTORY,
1371 p_revision => MTL_CCEOI_VAR_PVT.G_SKU_REC.REVISION,
1372 p_lot_number => MTL_CCEOI_VAR_PVT.G_SKU_REC.LOT_NUMBER,
1373 p_system_quantity => p_interface_rec.system_quantity,
1374 p_secondary_system_quantity => p_interface_rec.secondary_system_quantity -- INVCONV
1375 );
1376 --
1377 IF (l_debug = 1) THEN
1378 mdebug('UOMQunatity Error ='||to_char(L_errorcode));
1379 mdebug('Errortext ='||x_msg_data);
1380 END IF;
1381
1382 IF L_errorcode <>0 AND L_return_status <> FND_API.G_RET_STS_SUCCESS
1383 THEN
1384 IF (l_debug = 1) THEN
1385 mdebug('Error UOM Quantity');
1386 END IF;
1387 GOTO end_VALIDATE;
1388 END IF;
1389
1390 -- BEGIN INVCONV
1391 IF (l_debug = 1) THEN
1392 mdebug('Validate Secondary UOM and Quantity');
1393 END IF;
1394
1395 MTL_CCEOI_PROCESS_PVT.Validate_SecondaryUOMQty(
1396 p_api_version => 0.9
1397 , x_return_status => l_return_status
1398 , x_msg_count => x_msg_count
1399 , x_msg_data => x_msg_data
1400 , x_errorcode => l_errorcode
1401 , p_organization_id => MTL_CCEOI_VAR_PVT.G_CYCLE_COUNT_HEADER_REC.ORGANIZATION_ID
1402 , p_inventory_item_id => MTL_CCEOI_VAR_PVT.G_INVENTORY_ITEM_ID
1403 , p_serial_number => MTL_CCEOI_VAR_PVT.G_SKU_REC.SERIAL_NUMBER
1404 , p_subinventory => MTL_CCEOI_VAR_PVT.G_SUBINVENTORY
1405 , p_revision => MTL_CCEOI_VAR_PVT.G_SKU_REC.REVISION
1406 , p_lot_number => MTL_CCEOI_VAR_PVT.G_SKU_REC.LOT_NUMBER
1407 , p_secondary_uom => p_interface_rec.secondary_uom
1408 , p_secondary_unit_of_measure => p_interface_rec.secondary_unit_of_measure
1409 , p_secondary_count_quantity => p_interface_rec.secondary_count_quantity
1410 , p_secondary_system_quantity => p_interface_rec.secondary_system_quantity
1411 , p_tracking_quantity_ind => MTL_CCEOI_VAR_PVT.G_TRACKING_QUANTITY_IND
1412 , p_secondary_default_ind => MTL_CCEOI_VAR_PVT.G_SECONDARY_DEFAULT_IND
1413 );
1414
1415 IF (l_debug = 1) THEN
1416 mdebug('Secondary UOM Quantity Error = '||to_char(L_errorcode));
1417 mdebug('Errortext = '||x_msg_data);
1418 END IF;
1419
1420 IF L_errorcode <>0 AND L_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1421 IF (l_debug = 1) THEN
1422 mdebug('Error Secondary UOM Quantity');
1423 END IF;
1424 GOTO end_VALIDATE;
1425 END IF;
1426 -- END INVCONV
1427
1428 --
1429 IF (l_debug = 1) THEN
1430 mdebug('Count Date' || to_char(p_interface_rec.employee_id));
1431 END IF;
1432 -- Check the counter AND DATE information Step 5
1433 MTL_CCEOI_PROCESS_PVT.Validate_CDate_Counter(
1434 p_api_version => 0.9,
1435 x_return_status => L_return_status,
1436 x_msg_count => x_msg_count,
1437 x_msg_data => x_msg_data,
1438 x_ErrorCode => L_errorcode,
1439 p_count_date => p_interface_rec.count_date,
1440 p_employee_id => p_interface_rec.employee_id,
1441 p_employee_name => p_interface_rec.employee_full_name
1442 );
1443 --
1444 IF L_errorcode <>0 AND L_return_status <> FND_API.G_RET_STS_SUCCESS
1445 THEN
1446 IF (l_debug = 1) THEN
1447 MDEBUG( 'Error Count Date '||to_char(l_errorcode));
1448 END IF;
1449 GOTO end_VALIDATE;
1450 END IF;
1451 END IF;
1452 --
1453 -- if procedures returned successfully marked for deletion
1454 -- only if action code = Validate
1455 --
1456 <<end_VALIDATE>>
1457 x_return_status := L_return_status;
1458 IF (l_debug = 1) THEN
1459 MDEBUG( 'Return= '||x_return_status);
1460 MDEBUG( 'Validation_level ='||to_number(p_validation_level));
1461 END IF;
1462
1463 --
1464 -- UPDATE the interface TABLE flags
1465 IF L_return_status = FND_API.G_RET_STS_SUCCESS
1466 AND p_validation_level=1 THEN
1467 IF MTL_CCEOI_VAR_PVT.G_REC_IN_SYSTEM = TRUE THEN
1468 MTL_CCEOI_PROCESS_PVT.Set_CCEOIFlags(
1469 p_api_version => 0.9,
1470 x_return_status => L_return_status,
1471 x_msg_count => x_msg_count,
1472 x_msg_data => x_msg_data,
1473 p_cc_entry_interface_id => MTL_CCEOI_VAR_PVT.G_CC_ENTRY_INTERFACE_ID, --p_interface_rec.cc_entry_interface_id,
1474 p_flags => '2$51');
1475 END IF; -- record is stored in the interface
1479 -- the flag means no errors, successful validated, valid
1476 IF (l_debug = 1) THEN
1477 MDEBUG( 'Successfully Validated ');
1478 END IF;
1480 ELSIF
1481 L_return_status = FND_API.G_RET_STS_SUCCESS
1482 AND p_validation_level=0 THEN
1483 IF MTL_CCEOI_VAR_PVT.G_REC_IN_SYSTEM = TRUE THEN
1484 MTL_CCEOI_PROCESS_PVT.Set_CCEOIFlags(
1485 p_api_version => 0.9,
1486 x_return_status => L_return_status,
1487 x_msg_count => x_msg_count,
1488 x_msg_data => x_msg_data,
1489 p_cc_entry_interface_id => MTL_CCEOI_VAR_PVT.G_CC_ENTRY_INTERFACE_ID, --p_interface_rec.cc_entry_interface_id,
1490 p_flags => '2$51');
1491 -- the flag means no errors,marked FOR deletion,
1492 -- successful validated, valid
1493 END IF;
1494 IF (l_debug = 1) THEN
1495 MDEBUG( 'Successfully Validated-');
1496 END IF;
1497 ELSE
1498 -- IF error AND online, INSERT the interface RECORD
1499 IF MTL_CCEOI_VAR_PVT.G_REC_IN_SYSTEM = FALSE THEN
1500
1501 IF (l_debug = 1) THEN
1502 MDEBUG('Inserting interface entry');
1503 END IF;
1504 MTL_CCEOI_PROCESS_PVT.Insert_CCIEntry(
1505 p_interface_rec => p_interface_rec,
1506 x_return_status => L_return_status);
1507 END IF;
1508 --
1509 -- SET the flags
1510 MTL_CCEOI_PROCESS_PVT.Set_CCEOIFlags(
1511 p_api_version => 0.9,
1512 x_return_status => L_return_status,
1513 x_msg_count => x_msg_count,
1514 x_msg_data => x_msg_data,
1515 p_cc_entry_interface_id => MTL_CCEOI_VAR_PVT.G_CC_ENTRY_INTERFACE_ID, --p_interface_rec.cc_entry_interface_id,
1516 p_flags => '1$22');
1517 IF (l_debug = 1) THEN
1518 MDEBUG( 'Processed with errors ');
1519 END IF;
1520 -- the flag means errors, processed with errors, not valid
1521 END IF;
1522 --
1523 -- END of API body
1524 -- Standard check of p_commit
1525 IF FND_API.to_Boolean(p_commit) THEN
1526 COMMIT;
1527 END IF;
1528 -- Standard call to get message count and if count is 1, get message info
1529 FND_MSG_PUB.Count_And_Get
1530 (p_count => x_msg_count
1531 , p_data => x_msg_data);
1532 EXCEPTION
1533 WHEN FND_API.G_EXC_ERROR THEN
1534 --
1535 --ROLLBACK TO Validate_CountRequest;
1536 --
1537 IF (l_debug = 1) THEN
1538 mdebug('Validate_CountRequest: Error' || sqlerrm);
1539 END IF;
1540 x_return_status := FND_API.G_RET_STS_ERROR;
1541 --
1542 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count
1543 , p_data => x_msg_data);
1544 --
1545 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1546 --
1547 --ROLLBACK TO Validate_CountRequest;
1548 --
1549 IF (l_debug = 1) THEN
1550 mdebug('Validate_CountRequest: Error' || sqlerrm);
1551 END IF;
1552 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1553 --
1554 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count
1555 , p_data => x_msg_data);
1556 --
1557 WHEN OTHERS THEN
1558 --
1559 --ROLLBACK TO Validate_CountRequest;
1560 --
1561 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1562 --
1563 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1564 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
1565 END IF;
1566 --
1567 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count
1568 , p_data => x_msg_data);
1569 END;
1570 END;
1571 --
1572 -- validates and simulates records from interface table
1573 --Added NOCOPY hint to x_return_status,x_msg_count,x_msg_data OUT
1574 --parameters to comply with GSCC File.Sql.39 standard. Bug:4410902
1575 Procedure ValSim_CountRequest(
1576 p_api_version IN NUMBER ,
1577 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
1578 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
1579 p_validation_level IN NUMBER DEFAULT FND_API.G_VALID_LEVEL_FULL,
1580 x_return_status OUT NOCOPY VARCHAR2 ,
1581 x_msg_count OUT NOCOPY NUMBER ,
1582 x_msg_data OUT NOCOPY VARCHAR2 ,
1583 p_interface_rec IN MTL_CCEOI_VAR_PVT.INV_CCEOI_TYPE )
1584 IS
1585 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1586 BEGIN
1587 -- Start OF comments
1588 -- API name : ValSim_CountRequest
1589 -- TYPE : Private
1590 -- Pre-reqs : None
1591 -- FUNCTION :
1592 -- Parameters:
1593 -- IN :
1594 -- p_api_version IN NUMBER (required)
1595 -- API Version of this procedure
1596 --
1597 -- p_init_msg_list IN VARCHAR2 (optional)
1598 -- DEFAULT = FND_API.G_FALSE,
1599 --
1600 -- p_commit IN VARCHAR2 (optional)
1601 -- DEFAULT = FND_API.G_FALSE
1602 --
1603 -- p_validation_level IN NUMBER (optional)
1604 -- DEFAULT = FND_API.G_VALID_LEVEL_FULL,
1605 --
1606 --
1607 -- OUT :
1611 -- x_msg_count OUT NUMBER,
1608 -- X_return_status OUT NUMBER
1609 -- Result of all the operations
1610 --
1612 --
1613 -- x_msg_data OUT VARCHAR2,
1614 --
1615 -- Version: Current Version 0.9
1616 -- Changed : Nothing
1617 -- No Previous Version 0.0
1618 -- Initial version 0.9
1619 -- Notes : Note text
1620 -- END OF comments
1621 DECLARE
1622 L_return_status VARCHAR2(30);
1623 --
1624 L_api_version CONSTANT NUMBER := 0.9;
1625 L_api_name CONSTANT VARCHAR2(30) := 'ValSim_CountRequest';
1626 BEGIN
1627 -- Standard start of API savepoint
1628 SAVEPOINT ValSim_CountRequest;
1629 --
1630 -- Standard Call to check for call compatibility
1631 IF NOT FND_API.Compatible_API_Call(l_api_version
1632 , p_api_version
1633 , l_api_name
1634 , G_PKG_NAME) THEN
1635 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1636 END IF;
1637 --
1638 -- Initialize message list if p_init_msg_list is set to true
1639 IF FND_API.to_Boolean(p_init_msg_list) THEN
1640 FND_MSG_PUB.initialize;
1641 END IF;
1642 --
1643 -- Initialisize API return status to access
1644 L_return_status := FND_API.G_RET_STS_SUCCESS;
1645 --
1646 -- API body
1647 --
1648 -- Delete all error records first
1649 MTL_CCEOI_PROCESS_PVT.Delete_CCEOIError(
1650 p_interface_rec.cc_entry_interface_id);
1651 --
1652 MTL_CCEOI_ACTION_PVT.Process_CountRequest
1653 -- Prozedur
1654 (p_api_version => 0.9
1655 , p_validation_level => 1
1656 -- withoutsaving
1657 , x_return_status => L_return_status
1658 , x_msg_count => x_msg_count
1659 , x_msg_data => x_msg_data
1660 , p_interface_rec => p_interface_rec
1661 );
1662 --
1663 IF L_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1664 --
1665 -- If online mode insert interface record first
1666 IF MTL_CCEOI_VAR_PVT.G_REC_IN_SYSTEM = FALSE THEN
1667 MTL_CCEOI_PROCESS_PVT.Insert_CCIEntry(
1668 p_interface_rec => p_interface_rec,
1669 x_return_status => L_return_status);
1670 END IF;
1671 --
1672 MTL_CCEOI_PROCESS_PVT.Set_CCEOIFlags
1673 -- Prozedur
1674 (p_api_version => 0.9
1675 , x_return_status => L_return_status
1676 , x_msg_count => x_msg_count
1677 , x_msg_data => x_msg_data
1678 , p_cc_entry_interface_id => MTL_CCEOI_VAR_PVT.G_CC_ENTRY_INTERFACE_ID, --p_interface_rec.cc_entry_interface_id
1679 p_flags => '1$2$');
1680 -- error, processed with errors
1681 --
1682 ELSE
1683 IF MTL_CCEOI_VAR_PVT.G_REC_IN_SYSTEM = TRUE THEN
1684 MTL_CCEOI_PROCESS_PVT.Set_CCEOIFlags
1685 (p_api_version => 0.9
1686 , x_return_status => L_return_status
1687 , x_msg_count => x_msg_count
1688 , x_msg_data => x_msg_data
1689 , p_cc_entry_interface_id => MTL_CCEOI_VAR_PVT.G_CC_ENTRY_INTERFACE_ID, --p_interface_rec.cc_entry_interface_id
1690 p_flags => '2$6$');
1691 -- no error, succesfully validated and simulated
1692 END IF;
1693 END IF;
1694 --
1695 x_return_status := L_return_status;
1696 --
1697 -- END of API body
1698 -- Standard check of p_commit
1699 IF FND_API.to_Boolean(p_commit) THEN
1700 COMMIT;
1701 END IF;
1702 -- Standard call to get message count and if count is 1, get message info
1703 FND_MSG_PUB.Count_And_Get
1704 (p_count => x_msg_count
1705 , p_data => x_msg_data);
1706 EXCEPTION
1707 WHEN FND_API.G_EXC_ERROR THEN
1708 --
1709 ROLLBACK TO ValSim_CountRequest;
1713 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count
1710 --
1711 x_return_status := FND_API.G_RET_STS_ERROR;
1712 --
1714 , p_data => x_msg_data);
1715 --
1716 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1717 --
1718 ROLLBACK TO ValSim_CountRequest;
1719 --
1720 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1721 --
1722 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count
1723 , p_data => x_msg_data);
1724 --
1725 WHEN OTHERS THEN
1726 --
1727 ROLLBACK TO ValSim_CountRequest;
1728 --
1729 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1730 --
1731 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1732 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
1733 END IF;
1734 --
1735 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count
1736 , p_data => x_msg_data);
1737 END;
1738 END;
1739 --
1740 -- Updated or inserted the interface record table
1741 --Added NOCOPY hint to x_return_status,x_msg_count,x_msg_data OUT
1742 --parameters to comply with GSCC File.Sql.39 standard. Bug:4410902
1743 PROCEDURE Update_Insert_CountRequest(
1744 p_api_version IN NUMBER ,
1745 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
1746 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
1747 p_validation_level IN NUMBER DEFAULT FND_API.G_VALID_LEVEL_FULL,
1748 x_return_status OUT NOCOPY VARCHAR2 ,
1749 x_msg_count OUT NOCOPY NUMBER ,
1750 x_msg_data OUT NOCOPY VARCHAR2 ,
1751 p_interface_rec IN MTL_CCEOI_VAR_PVT.INV_CCEOI_TYPE )
1752 IS
1753 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1754 BEGIN
1755 -- Start OF comments
1756 -- API name : UpdateInsert_CountRequest
1757 -- TYPE : Private
1758 -- Pre-reqs : None
1759 -- FUNCTION :
1760 -- This PROCEDURE IS CALLED FROM Import Public API.
1761 -- FOR Background mode it IS possible to INSERT OR UPDATE
1762 -- the interface TABLE
1763 -- Parameters:
1764 -- IN :
1765 -- p_api_version IN NUMBER (required)
1766 -- API Version of this procedure
1767 --
1768 -- p_init_msg_l IN VARCHAR2 (optional)
1769 -- DEFAULT = FND_API.G_FALSE,
1770 --
1771 -- p_commit IN VARCHAR2 (optional)
1772 -- DEFAULT = FND_API.G_FALSE
1773 --
1774 -- p_validation_level IN NUMBER (optional)
1775 -- DEFAULT = FND_API.G_VALID_LEVEL_FULL,
1776 --
1777 -- p_interface_rec MTL_CC_ENTRIES_INTERFACE%ROWTYPE (required)
1778 -- the interface RECORD
1779 --
1780 -- OUT :
1781 -- X_return_status OUT NUMBER
1782 -- Result of all the operations
1783 --
1784 -- x_msg_count OUT NUMBER,
1785 --
1786 -- x_msg_data OUT VARCHAR2,
1787 --
1788 -- Version: Current Version 0.9
1789 -- Changed : Nothing
1790 -- No Previous Version 0.0
1791 -- Initial version 0.9
1792 -- Notes : Note text
1793 -- END OF comments
1794 DECLARE
1795 --
1796 L_dummy NUMBER := TO_NUMBER(NULL);
1797 L_return_status VARCHAR2(30);
1798 L_msg_count NUMBER;
1799 L_msg_data VARCHAR2(100);
1800 --
1801 L_api_version CONSTANT NUMBER := 0.9;
1802 L_api_name CONSTANT VARCHAR2(30) := 'UpdateInsert_CountRequest';
1803 BEGIN
1804 -- Standard start of API savepoint
1805 SAVEPOINT UpdateInsert_CountRequest;
1806 --
1807 -- Standard Call to check for call compatibility
1808 IF NOT FND_API.Compatible_API_Call(l_api_version
1809 , p_api_version
1810 , l_api_name
1811 , G_PKG_NAME) THEN
1812 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1813 END IF;
1814 --
1815 -- Initialize message list if p_init_msg_list is set to true
1816 IF FND_API.to_Boolean(p_init_msg_list) THEN
1817 FND_MSG_PUB.initialize;
1818 END IF;
1819 --
1820 -- Initialisize API return status to access
1821 L_return_status := FND_API.G_RET_STS_SUCCESS;
1822 --
1823 -- API body
1824 --
1825 -- If the validation error out, because the values
1826 -- are wrong the system quantity is updated to NULL
1827 MTL_CCEOI_ACTION_PVT.Validate_CountRequest(
1828 p_api_version => 0.9,
1829 x_msg_count => L_msg_count,
1830 x_msg_data => L_msg_data,
1831 x_return_status => x_return_status,
1832 p_interface_rec => p_interface_rec);
1833 --
1834 BEGIN
1835 SELECT cc_entry_interface_id
1836 INTO
1837 L_dummy
1838 FROM
1839 mtl_cc_entries_interface
1840 WHERE
1841 cc_entry_interface_id =
1842 p_interface_rec.cc_entry_interface_id;
1843 --
1844 --
1845 MTL_CCEOI_PROCESS_PVT.Update_CCIEntry(
1846 p_interface_rec => p_interface_rec
1847 , x_return_status => L_return_status);
1848 --
1849 IF L_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1850 FND_MESSAGE.SET_NAME('INV', 'INV_CCEOI_UPDATE_FAILED');
1851 FND_MSG_PUB.Add;
1852 RAISE FND_API.G_EXC_ERROR;
1853 END IF;
1854 --
1855 EXCEPTION
1856 WHEN NO_DATA_FOUND THEN
1857 IF (l_debug = 1) THEN
1858 Mdebug('Before Insert_CCIEntry');
1859 END IF;
1860
1861 MTL_CCEOI_PROCESS_PVT.Insert_CCIEntry(
1862 p_interface_rec => p_interface_rec
1863 , x_return_status => L_return_status);
1864 --
1865 IF (l_debug = 1) THEN
1866 Mdebug('After Insert_CCIEntry');
1867 END IF;
1868 IF L_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1869 FND_MESSAGE.SET_NAME('INV', 'INV_CCEOI_INSERT_FAILED');
1870 FND_MSG_PUB.Add;
1871 RAISE FND_API.G_EXC_ERROR;
1872 END IF;
1873 END;
1874 --
1875 x_return_status := L_return_status;
1876 --
1877 -- END of API body
1878 -- Standard check of p_commit
1879 IF FND_API.to_Boolean(p_commit) THEN
1880 COMMIT;
1881 END IF;
1882 -- Standard call to get message count and if count is 1, get message info
1883 FND_MSG_PUB.Count_And_Get
1884 (p_count => x_msg_count
1885 , p_data => x_msg_data);
1886 EXCEPTION WHEN FND_API.G_EXC_ERROR THEN
1887 --
1888 x_return_status := FND_API.G_RET_STS_ERROR;
1889 --
1890 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count
1891 , p_data => x_msg_data);
1892 --
1893 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1894 --
1895 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1896 --
1897 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count
1898 , p_data => x_msg_data);
1899 --
1900 WHEN OTHERS THEN
1901 --
1902 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1903 --
1904 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1905 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
1906 END IF;
1907 --
1908 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count
1909 , p_data => x_msg_data); END;
1910 END;
1911 END MTL_CCEOI_ACTION_PVT;