[Home] [Help]
PACKAGE BODY: APPS.MTL_CCEOI_CONC_PVT
Source
1 PACKAGE BODY MTL_CCEOI_CONC_PVT AS
2 /* $Header: INVVCCCB.pls 120.1.12020000.2 2012/07/09 08:20:52 asugandh ship $ */
3
4 Current_Error_Code VARCHAR2(30) := NULL;
5 --
6 G_PKG_NAME CONSTANT VARCHAR2(30) := 'MTL_CCEOI_CONC_PVT';
7 --
8 procedure mdebug(msg in varchar2)
9 is
10 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
11 begin
12 -- dbms_output.put_line(msg);
13 null;
14 end;
15
16 procedure inv_cceoi_set_log_file is
17 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
18 begin
19 declare
20 v_db_name VARCHAR2(100);
21 v_log_name VARCHAR2(100);
22 v_db_name VARCHAR2(100);
23 v_st_position number(3);
24 v_end_position number(3);
25 v_w_position number(3);
26
27 begin
28 select INSTR(value,',',1,2),INSTR(value,',',1,3)
29 into v_st_position,v_end_position from v$parameter
30 where upper(name) = 'UTL_FILE_DIR';
31
32 v_w_position := v_end_position - v_st_position - 1;
33
34 select substr(value,v_st_position+1,v_w_position)
35 into v_log_name from v$parameter
36 where upper(name) = 'UTL_FILE_DIR';
37 v_log_name := ltrim(v_log_name);
38 FND_FILE.PUT_NAMES(v_log_name,v_log_name,v_log_name);
39 end;
40 end;
41 --
42 -- Concurrent Program Export
43 PROCEDURE Export_CCEntriesIface(
44 ERRBUF OUT NOCOPY VARCHAR2 ,
45 RETCODE OUT NOCOPY VARCHAR2 ,
46 P_Cycle_Count_Header_Id IN NUMBER ,
47 P_Cycle_Count_Entry_ID IN NUMBER DEFAULT NULL,
48 p_cc_entry_iface_group_id IN NUMBER DEFAULT NULL)
49 IS
50 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
51 BEGIN
52 -- Start OF comments
53 -- API name : Export_CCEntriesIface
54 -- TYPE : Privat
55 -- Pre-reqs :
56 -- FUNCTION :
57 -- The Export concurrent program select all unexported rows
58 -- from the given cycle count. Each row will be inserted INTO
59 -- TABLE MTL_CC_ENTRIES_INTERFACE
60 -- by calling the PRIVAT API procedure MTL_CCEOI_ACTION_PVT.
61 -- Export_CountRequest). IF the p_cycle_count_entry_id parameter
62 -- IS populated only one ROW will be exported.
63 -- Parameters :
64 -- OUT :
65 -- ERRBUF OUT VARCHAR2 (required)
66 -- returns any error message
67 --
68 -- RETCODE OUT VARCHAR2 (required)
69 -- return completion status
70 -- 0 = 'SUCCESS'
71 -- 1 = 'WARNING'
72 -- 2 = 'ERROR'
73 -- IN :
74 -- P_Cycle_Count_Header_Id IN NUMBER (required)
75 -- Cycle Count Header ID
76 --
77 -- P_Cycle_Count_Entry_Id IN NUMBER (optional)
78 -- Default NULL
79 -- Cycle Count Entry ID. IF the parameter IS populated
80 -- this PROCEDURE IS called to process only one record.
81 --
82 -- p_cc_entry_iface_group_id IN NUMBER (optional)
83 -- default NULL
84 -- Cycle COUNT interface group ID FOR worker SET
85 -- processing. IF this Parameter IS populated the calling
86 -- PROCEDURE had selected the value FROM the sequence
87 -- mtl_cceoi_entries_interface_s2
88 --
89 -- Version : Current Version 0.9
90 --
91 -- initial version 0.9
92 -- Notes :
93 -- END OF comments
94 DECLARE
95 --
96 CURSOR L_CC_Header_Records_Csr (cchid number )IS
97 SELECT *
98 FROM mtl_cycle_count_headers
99 WHERE
100 (cycle_count_header_id = cchid);
101 --
102 CURSOR L_CycleCount_Records_Csr(HID NUMBER, CCID IN NUMBER) IS
103 SELECT *
104 FROM mtl_cycle_count_entries
105 WHERE
106 (cycle_count_header_id = hid
107 OR cycle_count_entry_id = ccid)
108 AND NVL(export_flag, 2) = 2
109 AND entry_status_code IN(1, 3);
110 --
111 -- BEGIN INVCONV
112 CURSOR cur_get_item_attr (
113 cp_inventory_item_id NUMBER
114 , cp_organization_id NUMBER
115 ) IS
116 -- tracking_quantity_ind (P-Primary, PS-Primary and Secondary)
117 -- secondary_default_ind (F-Fixed, D-Default, N-No Default)
118 SELECT msi.tracking_quantity_ind
119 , msi.secondary_default_ind
120 , msi.secondary_uom_code
121 , msi.process_costing_enabled_flag
122 , mtp.process_enabled_flag
123 FROM mtl_system_items msi, mtl_parameters mtp
124 WHERE mtp.organization_id = cp_organization_id
125 AND msi.organization_id = mtp.organization_id
126 AND msi.inventory_item_id = cp_inventory_item_id;
127 -- END INVCONV
128
129 L_CCEOIEntry_record MTL_CCEOI_VAR_PVT.INV_CCEOI_TYPE;
130 L_cc_entry_iface_group_id NUMBER;
131 L_rec_counter_pos integer := 0;
132 L_rec_counter_neg integer := 0;
133 L_counter integer := 0;
134 L_return_status VARCHAR2(30);
135 L_errorcode NUMBER := 0;
136 L_msg_count NUMBER;
137 L_msg_data VARCHAR2(100);
138 L_CONC_STATUS BOOLEAN;
139 L_ccHeader_ID NUMBER;
140 L_ccEntry_ID NUMBER;
141 x_serial_count_option NUMBER;
142 x_serial_number_control NUMBER;
143 --
144 BEGIN
145 IF (l_debug = 1) THEN
146 mdebug('Start export ');
147 END IF;
148 RETCODE := 'SUCCESS';
149 --
150 inv_cceoi_set_log_file;
151 --FND_FILE.PUT_NAMES('/sqlcom/log', '/sqlcom/log', '/sqlcom/log');
152 --
153 IF p_cycle_count_entry_id IS NULL THEN
154 -- Validate Input parameter
155 MTL_INV_VALIDATE_GRP.Validate_CountHeader(
156 p_api_version => 0.9,
157 x_return_status => L_return_status,
158 x_msg_count => L_msg_count,
159 x_msg_data => L_msg_data,
160 X_ErrorCode => L_errorcode,
161 p_cycle_count_header_id => p_cycle_count_header_id);
162 --
163 IF L_errorcode <> 0 THEN
164 RETCODE := 'ERROR';
165 ERRBUF := L_msg_data;
166 APP_EXCEPTION.RAISE_EXCEPTION;
167 END IF;
168 END IF;
169 --
170 IF RETCODE = 'SUCCESS'THEN
171 IF p_cc_entry_iface_group_id IS NOT NULL THEN
172 L_cc_entry_iface_group_id :=p_cc_entry_iface_group_id;
173 ELSE
174 SELECT
175 MTL_CC_ENTRIES_INTERFACE_S2.nextval
176 INTO
177 L_cc_entry_iface_group_id
178 FROM
179 dual;
180 END IF;
181 --
182 -- Only one parameter can be used
183 IF P_Cycle_Count_Entry_Id IS NOT NULL THEN
184 L_ccHeader_ID := NULL;
185 L_ccEntry_ID := P_Cycle_Count_Entry_Id;
186 ELSIF
187 P_Cycle_Count_Header_Id IS NOT NULL THEN
188 L_ccHeader_ID := P_Cycle_Count_Header_Id;
189 L_ccEntry_ID := NULL;
190 END IF;
191 --
192 -- Interface Group-ID of the Exports
193 IF (l_debug = 1) THEN
194 mdebug('Export- Before cursor ');
195 END IF;
196
197 FOR c_rec IN L_CycleCount_Records_Csr(L_ccHeader_ID, L_ccEntry_ID) LOOP
198 --
199 -- START: prepare the input parameter FOR the Public API PROCEDURE
200 L_CCEOIEntry_record.Organization_id := c_rec. Organization_id;
201 L_CCEOIEntry_record.cc_entry_interface_group_id :=
202 L_cc_entry_iface_group_id;
203 L_CCEOIEntry_record.Cycle_Count_Header_id := c_rec.
204 Cycle_Count_Header_id;
205 L_CCEOIEntry_record.Count_List_Sequence := c_rec.Count_List_Sequence;
206 L_CCEOIEntry_record.inventory_item_id := c_rec.inventory_item_id;
207 L_CCEOIEntry_record.revision := c_rec.revision;
208 L_CCEOIEntry_record.subinventory := c_rec.subinventory;
209 L_CCEOIEntry_record.locator_id := c_rec.locator_id;
210 L_CCEOIEntry_record.lot_number := c_rec.lot_number;
211 L_CCEOIEntry_record.serial_number := c_rec.serial_number;
212 L_CCEOIEntry_record.system_quantity := c_rec.system_quantity_current;
213 -- BEGIN INVCONV
214 OPEN cur_get_item_attr (c_rec.inventory_item_id, c_rec.organization_id);
215 FETCH cur_get_item_attr
216 INTO MTL_CCEOI_VAR_PVT.g_tracking_quantity_ind,
217 MTL_CCEOI_VAR_PVT.g_secondary_default_ind,
218 MTL_CCEOI_VAR_PVT.g_secondary_uom_code,
219 MTL_CCEOI_VAR_PVT.g_process_costing_enabled_flag,
220 MTL_CCEOI_VAR_PVT.g_process_enabled_flag;
221 CLOSE cur_get_item_attr;
222
223 L_CCEOIEntry_record.secondary_uom := MTL_CCEOI_VAR_PVT.g_secondary_uom_code;
224 L_CCEOIEntry_record.secondary_system_quantity := c_rec.secondary_system_qty_current;
225 -- END INVCONV
226
227 -- This code is added for the bug 2311404 by aapaul
228 if (c_rec.system_quantity_current is null) then
229 select SERIAL_NUMBER_CONTROL_CODE into x_serial_number_control
230 from mtl_system_items
231 where organization_id = c_rec.organization_id
232 and inventory_item_id = c_rec.inventory_item_id;
233 select SERIAL_COUNT_OPTION into x_serial_count_option
234 from mtl_cycle_count_headers
235 where cycle_count_header_id = c_rec.Cycle_Count_Header_id;
236 if c_rec.parent_lpn_id is not null then
237 if c_rec.inventory_item_id is not null then
238 MTL_INV_UTIL_GRP.Get_LPN_Item_SysQty
239 (
240 --p_api_version => 1.0 -- commented for bug 14082014
241 p_api_version => 0.9 -- added for bug 14082014
242 , p_init_msg_lst => fnd_api.g_true
243 , p_commit => fnd_api.g_true
244 , x_return_status => L_return_status
245 , x_msg_count => L_msg_count
246 , x_msg_data => L_msg_data
247 , p_organization_id => c_rec.Organization_id
248 , p_lpn_id => c_rec.parent_lpn_id
249 , p_inventory_item_id => c_rec.inventory_item_id
250 , p_lot_number => c_rec.lot_number
251 , p_revision => c_rec.revision
252 , p_serial_number => c_rec.serial_number
253 , p_cost_group_id => c_rec.cost_group_id
254 , x_lpn_systemqty => L_CCEOIEntry_record.system_quantity
255 , x_lpn_sec_systemqty => L_CCEOIEntry_record.secondary_system_quantity -- INVCONV
256 );
257 -- BEGIN INVCONV
258 IF MTL_CCEOI_VAR_PVT.g_tracking_quantity_ind <> 'PS' THEN
259 L_CCEOIEntry_record.secondary_system_quantity := NULL;
260 END IF;
261 -- END INVCONV
262 else
263 L_CCEOIEntry_record.system_quantity := NULL;
264 L_CCEOIEntry_record.secondary_system_quantity := NULL; -- INVCONV
265 end if;
266 else
267 MTL_INV_UTIL_GRP.Calculate_Systemquantity
268 (
269 p_api_version => 0.9
270 , x_return_status => L_return_status
271 , x_msg_count => L_msg_count
272 , x_msg_data => L_msg_data
273 , p_organization_id => c_rec.organization_id
274 , p_inventory_item_id => c_rec.inventory_item_id
275 , p_subinventory => c_rec.subinventory
276 , p_lot_number => c_rec.lot_number
277 , p_revision => c_rec.revision
278 , p_locator_id => c_rec.locator_id
279 , p_cost_group_id => c_rec.cost_group_id
280 , p_serial_number => c_rec.serial_number
281 , p_serial_number_control => x_serial_number_control
282 , p_serial_count_option => x_serial_count_option
283 , x_system_quantity => L_CCEOIEntry_record.system_quantity
284 , x_sec_system_quantity => L_CCEOIEntry_record.secondary_system_quantity -- INVCONV
285 );
286 -- BEGIN INVCONV
287 IF MTL_CCEOI_VAR_PVT.g_tracking_quantity_ind <> 'PS' THEN
288 L_CCEOIEntry_record.secondary_system_quantity := NULL;
289 END IF;
290 -- END INVCONV
291 end if;
292 end if;
293 L_CCEOIEntry_record.adjustment_account_id :=
294 c_rec.inventory_adjustment_account;
295 L_CCEOIEntry_record.parent_lpn_id := c_rec.parent_lpn_id;
296 L_CCEOIEntry_record.outermost_lpn_id := c_rec.outermost_lpn_id;
297 L_CCEOIEntry_record.cost_group_id := c_rec.cost_group_id;
298 if L_CCEOIEntry_record.adjustment_account_id is null then
299 FOR hc_rec IN L_CC_Header_Records_Csr(c_rec.Cycle_Count_Header_id)
300 LOOP
301 L_CCEOIEntry_record.adjustment_account_id :=
302 hc_rec.inventory_adjustment_account;
303 END LOOP;
304 end if;
305 L_CCEOIEntry_record.reference := c_rec.reference_current;
306 L_CCEOIEntry_record.lock_flag := 2;
307 -- no LOCK
308 L_CCEOIEntry_record.process_flag := 1;
309 -- Ready
310 L_CCEOIEntry_record.process_mode := 3;
311 -- Export
312 L_CCEOIEntry_record.ATTRIBUTE_CATEGORY := c_rec.ATTRIBUTE_CATEGORY;
313 L_CCEOIEntry_record.ATTRIBUTE1 := c_rec.ATTRIBUTE1;
314 L_CCEOIEntry_record.ATTRIBUTE2 := c_rec.ATTRIBUTE2;
315 L_CCEOIEntry_record.ATTRIBUTE3 := c_rec.ATTRIBUTE3;
316 L_CCEOIEntry_record.ATTRIBUTE4 := c_rec.ATTRIBUTE4;
317 L_CCEOIEntry_record.ATTRIBUTE5 := c_rec.ATTRIBUTE5;
318 L_CCEOIEntry_record.ATTRIBUTE6 := c_rec.ATTRIBUTE6;
319 L_CCEOIEntry_record.ATTRIBUTE7 := c_rec.ATTRIBUTE7;
320 L_CCEOIEntry_record.ATTRIBUTE8 := c_rec.ATTRIBUTE8;
321 L_CCEOIEntry_record.ATTRIBUTE9 := c_rec.ATTRIBUTE9;
322 L_CCEOIEntry_record.ATTRIBUTE10 := c_rec.ATTRIBUTE10;
323 L_CCEOIEntry_record.ATTRIBUTE11 := c_rec.ATTRIBUTE11;
324 L_CCEOIEntry_record.ATTRIBUTE12 := c_rec.ATTRIBUTE12;
325 L_CCEOIEntry_record.ATTRIBUTE13 := c_rec.ATTRIBUTE13;
326 L_CCEOIEntry_record.ATTRIBUTE14 := c_rec.ATTRIBUTE14;
327 L_CCEOIEntry_record.ATTRIBUTE15 := c_rec.ATTRIBUTE15;
328 -- Export STATI
329 L_CCEOIEntry_record.VALID_FLAG := 1;
330 L_CCEOIEntry_record.cycle_count_entry_id :=
331 c_rec.cycle_count_entry_id;
332 L_CCEOIEntry_record.action_code := MTL_CCEOI_VAR_PVT.G_PROCESS;
333 L_CCEOIEntry_record.STATUS_FLAG := NULL;
334 -- END: prepare the input parameter FOR the Public API PROCEDURE
335 --
336 IF (l_debug = 1) THEN
337 mdebug('Before call Export_CountRequest export ');
338 END IF;
339
340 MTL_CCEOI_ACTION_PVT.Export_CountRequest(
341 p_api_version => 0.9,
342 X_return_status => L_return_status,
343 x_msg_count => L_msg_count,
344 x_msg_data => L_msg_data,
345 p_interface_rec => L_CCEOIEntry_record);
346 IF (l_debug = 1) THEN
347 mdebug('after call Export_CountRequeSt export ');
348 END IF;
349
350 --
351 -- Error Validation/Replace all NULL statements
352 --
353 IF(L_return_status = FND_API.G_RET_STS_SUCCESS) THEN
354 -- no errors
355 --
356 IF (l_debug = 1) THEN
357 mdebug('success '||L_return_status||'='||FND_API.G_RET_STS_SUCCESS);
358 END IF;
359 L_rec_counter_pos := L_rec_counter_pos + 1;
360 --
361 ELSE
362 IF (l_debug = 1) THEN
363 mdebug('error '||L_return_status );
364 END IF;
365 -- an error IS occured.Write error output
366 --
367 L_rec_counter_neg := L_rec_counter_neg + 1;
368 --
369 -- Write Text according to the error code to ERRBUF
370 ERRBUF := L_msg_data;
371 FND_FILE.PUT_LINE(FND_FILE.LOG, ERRBUF);
372 END IF;
373 L_counter := L_counter + 1;
374 END LOOP;
375 -- COMMIT;
376 END IF;
377 /*
378 -- Due to error it is commented out
379 IF (l_debug = 1) THEN
380 mdebug('debug - 1');
381 END IF;
382 -- How many rows are exported
383 FND_FILE.PUT_LINE(FND_FILE.LOG,
384 'Exported rows ='|| TO_CHAR(L_rec_counter_pos));
385 IF (l_debug = 1) THEN
386 mdebug('debug - 2');
387 END IF;
388 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Not Exported rows ='
389 || TO_CHAR(L_rec_counter_neg));
390 IF (l_debug = 1) THEN
391 mdebug('debug - 3');
392 END IF;
393 --
394 */
395 IF L_counter <> L_rec_counter_pos THEN
396 RETCODE := 'ERROR';
397 L_CONC_STATUS := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR',
398 L_msg_data);
399 ELSIF
400 L_counter = L_rec_counter_pos THEN
401 RETCODE := 'SUCCESS';
402 L_CONC_STATUS := FND_CONCURRENT.SET_COMPLETION_STATUS('NORMAL',
403 L_msg_data);
404 END IF;
405 IF (l_debug = 1) THEN
406 mdebug('debug - 4');
407 END IF;
408 END;
409 END;
410 --
411 -- Concurrent Program Import
412 PROCEDURE Import_CCEntriesIface(
413 ERRBUF OUT NOCOPY VARCHAR2 ,
414 RETCODE OUT NOCOPY VARCHAR2 ,
415 P_Cycle_Count_Header_ID IN NUMBER DEFAULT NULL,
416 P_Number_of_Worker IN NUMBER ,
417 P_Commit_point IN NUMBER DEFAULT 100,
418 P_ErrorReportLev IN NUMBER DEFAULT 2,
419 P_DeleteProcRec IN NUMBER DEFAULT 2)
420 IS
421 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
422 BEGIN
423 -- Start OF comments
424 -- API name : Import_CCEntriesIface
425 -- TYPE : Private
426 -- Pre-reqs :
427 -- FUNCTION:
428 -- Parameters:
429 -- Parameters :
430 -- OUT :
431 -- ERRBUF OUT VARCHAR2 (required)
432 -- returns any error message
433 --
434 -- RETCODE OUT VARCHAR2 (required)
435 -- return completion status
436 -- 0 = 'SUCCESS'
437 -- 1 = 'WARNING'
438 -- 2 = 'ERROR'
439 --
440 -- IN :
441 -- P_Cycle_Count_Header_Id IN NUMBER (optional)
442 -- Default = NULL
443 -- Cycle Count Header ID
444 --
445 -- P_Number_Of_Workers IN NUMBER (required)
446 -- COUNT OF workers
447 --
448 -- P_Commit_point IN NUMBER (optional)
449 -- default = 100
450 -- COMMIT Point FOR the worker process
451 --
452 -- P_ErrorReportLev NUMBER DEFAULT 2 (required - defaulted)
453 -- Error Reporting Level
454 -- 1=Abort on first error. This means the worker aborts at first error
455 -- 2=Process all errors and warnings.
456 --
457 -- P_DeleteProcRec NUMBER DEFAULT 2 (required - defaulted)
458 -- DELETE Processed Record
459 -- 1=DELETE successfully processed rows.
460 -- 2=Do not delete processed rows.
461 --
462 -- Version : Current Version 0.9
463 --
464 -- initial version 0.9
465 --
466 -- Notes :
467 -- END OF comments
468 DECLARE
469 L_MaxNumRows NUMBER;
470 L_NextGroupID NUMBER;
471 L_ErrorText VARCHAR2(2000);
472 L_Condition BOOLEAN := TRUE;
473 L_CountWorker NUMBER;
474 L_CountRows NUMBER;
475 L_CONC_STATUS BOOLEAN;
476 L_return_status VARCHAR2(30);
477 L_msg_count NUMBER;
478 L_msg_data VARCHAR2(240);
479 L_errorcode NUMBER;
480 L_NewReqID NUMBER;
481 --
482 BEGIN
483 --dbms_output.put_line('Begin-Import ');
484
485 RETCODE := 'SUCCESS';
486 --
487 inv_cceoi_set_log_file;
488 --FND_FILE.PUT_NAMES('/sqlcom/log', '/sqlcom/log', '/sqlcom/log');
489 --
490 -- Validate the input parameter before calling another procedures
491 IF p_number_of_worker < 1 THEN
492 RETCODE := 'ERROR';
493 ERRBUF := FND_MESSAGE.GET_STRING('INV', 'INV_CCEOI_WRONG_COUNTOFWORKER');
494 FND_FILE.PUT_LINE(FND_FILE.LOG, ERRBUF);
495 APP_EXCEPTION.RAISE_EXCEPTION;
496 END IF;
497 IF p_commit_point < 1 THEN
498 RETCODE := 'ERROR';
499 ERRBUF := FND_MESSAGE.GET_STRING('INV', 'INV_CCEOI_WRONG_COMMITPOINT');
500 FND_FILE.PUT_LINE(FND_FILE.LOG, ERRBUF);
501 APP_EXCEPTION.RAISE_EXCEPTION;
502 END IF;
503 IF P_ErrorReportLev NOT IN(1, 2) THEN
504 RETCODE := 'ERROR';
505 ERRBUF :=FND_MESSAGE.GET_STRING('INV', 'INV_CCEOI_WRONG_ERRORLEVEL');
506 FND_FILE.PUT_LINE(FND_FILE.LOG, ERRBUF);
507 APP_EXCEPTION.RAISE_EXCEPTION;
508 END IF;
509 IF P_DeleteProcRec NOT IN(1, 2) THEN
510 RETCODE := 'ERROR';
511 ERRBUF :=FND_MESSAGE.GET_STRING('INV', 'INV_CCEOI_WRONG_DELETEREC');
512 FND_FILE.PUT_LINE(FND_FILE.LOG, ERRBUF);
513 APP_EXCEPTION.RAISE_EXCEPTION;
514 END IF;
515 --
516 IF p_cycle_count_header_id IS NOT NULL THEN
517 MTL_INV_VALIDATE_GRP.Validate_CountHeader(
518 p_api_version => 0.9,
519 p_validation_level => 0,
520 -- Only FOR export, no derivation
521 x_return_status => L_return_status,
522 x_msg_count => L_msg_count,
523 x_msg_data => L_msg_data,
524 X_ErrorCode => L_errorcode,
525 p_cycle_count_header_id => p_cycle_count_header_id);
526 --
527 IF L_errorcode <> 0 THEN
528 RETCODE := 'ERROR';
529 ERRBUF := L_msg_data;
530 FND_FILE.PUT_LINE(FND_FILE.LOG, L_msg_data);
531 APP_EXCEPTION.RAISE_EXCEPTION;
532 END IF;
533 END IF;
534 -- END OF VALIDATION
535 --
536 IF RETCODE = 'SUCCESS' THEN
537 --
538 -- Calculate max rows according to the count of worker
539 /* Bug #2650761 - Added NVL(status_flag,4) since when exporting from the form,
540 status flag is NULL */
541 IF p_cycle_count_header_id IS NULL THEN
542 SELECT COUNT(*)
543 INTO L_MaxNumRows
544 FROM MTL_CC_ENTRIES_INTERFACE
545 WHERE NVL(STATUS_FLAG, 4) = 4
546 -- STATUS_FLAG = 4
547 AND NVL(LOCK_FLAG, 2) =2
548 AND NVL(DELETE_FLAG, 2) = 2
549 AND NVL(PROCESS_FLAG, 1) = 1
550 AND NVL(PROCESS_MODE, 3) = 3;
551 ELSE
552 SELECT COUNT(*)
553 INTO L_MaxNumRows
554 FROM MTL_CC_ENTRIES_INTERFACE
555 WHERE CYCLE_COUNT_HEADER_ID = P_CYCLE_COUNT_HEADER_ID
556 AND NVL(STATUS_FLAG, 4) = 4
557 --AND STATUS_FLAG = 4
558 AND NVL(LOCK_FLAG, 2) =2
559 AND NVL(DELETE_FLAG, 2) = 2
560 AND NVL(PROCESS_FLAG, 1) = 1
561 AND NVL(PROCESS_MODE, 3) = 3;
562 END IF;
563
564 --dbms_output.put_line(to_number(L_MaxNumRows)||' will be processed by the Import concurrent program. ');
565 FND_FILE.PUT_LINE(FND_FILE.LOG, to_number(L_MaxNumRows)||' will be processed by the Import concurrent program.');
566 IF L_MaxNumRows = 0 THEN
567 -- No rows retrieved
568 RETCODE := 'WARNING';
569 ERRBUF := 'No rows retrieved.';
570 FND_FILE.PUT_LINE(FND_FILE.LOG, 'No rows retrieved!'||
571 ' Worker do not started.');
572 L_CountWorker := 0;
573 ELSIF L_MaxNumRows <= P_Number_Of_Worker THEN
574 -- only one worker neccessary
575 L_CountWorker := 1;
576 L_CountRows := L_MaxNumRows;
577 ELSE
578 -- share rows to each worker
579 -- e.g.
580 -- P_Count_Of_Worker = 10
581 -- L_MaxNumRows = 1001
582 -- L_CountRows = ROUND(L_MaxNumRows/P_Count_Of_Worker) = 100
583 -- Rows = L_CountRows*P_Count_Of_Worker =
584 -- 1000 (smaller than L_MaxNumRows)
585 -- L_CountRows = 100 + L_MaxNumRows - Rows = 101
586 --
587 L_CountWorker := NVL(P_Number_Of_Worker,1); --4182975
588 --
589 SELECT ROUND(L_MaxNumRows/L_CountWorker)
590 INTO
591 L_CountRows
592 FROM
593 DUAL;
594 --
595 IF(L_CountRows*L_CountWorker<L_MaxNumRows) THEN
596 L_CountRows := L_CountRows + (L_MaxNumRows-(L_CountRows*L_CountWorker));
597 END IF;
598 END IF;
599 --
600 --dbms_output.put_line('Worker');
601 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Worker = '||
602 TO_CHAR(L_CountWorker));
603 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Rows/Worker = '||
604 TO_CHAR(L_CountRows));
605 --
606 -- New Logic to initialize the grpId before assign
607 UPDATE mtl_cc_entries_interface
608 SET
609 cc_entry_interface_group_id = NULL,
610 LAST_UPDATE_DATE = SYSDATE,
611 LAST_UPDATED_BY = MTL_CCEOI_VAR_PVT.G_UserID,
612 LAST_UPDATE_LOGIN = MTL_CCEOI_VAR_PVT.G_LoginID,
613 PROGRAM_APPLICATION_ID = MTL_CCEOI_VAR_PVT.G_ProgramAppID,
614 PROGRAM_ID = MTL_CCEOI_VAR_PVT.G_ProgramID,
615 REQUEST_ID = MTL_CCEOI_VAR_PVT.G_RequestID,
616 PROGRAM_UPDATE_DATE = SYSDATE
617 WHERE
618 CYCLE_COUNT_HEADER_ID = NVL(P_Cycle_Count_Header_Id,CYCLE_COUNT_HEADER_ID)
619 AND NVL(PROCESS_FLAG, 1) = 1
620 AND NVL(LOCK_FLAG, 2) = 2
621 AND NVL(PROCESS_MODE, 3) = 3
622 AND NVL(STATUS_FLAG,4) = 4
623 AND NVL(DELETE_FLAG, 2) = 2;
624 --
625 FOR i IN 1..L_CountWorker LOOP
626 --
627 SELECT MTL_CC_ENTRIES_INTERFACE_S1.NEXTVAL
628 INTO
629 L_NextGroupID
630 FROM
631 DUAL;
632 -- UPDATE the interface entries with the group ID
633 IF p_cycle_count_header_id IS NULL THEN
634 --dbms_output.put_line('Updating with cc header is null');
635 UPDATE mtl_cc_entries_interface
636 SET
637 cc_entry_interface_group_id = L_NextGroupID,
638 LAST_UPDATE_DATE = SYSDATE,
639 LAST_UPDATED_BY = MTL_CCEOI_VAR_PVT.G_UserID,
640 LAST_UPDATE_LOGIN = MTL_CCEOI_VAR_PVT.G_LoginID,
641 PROGRAM_APPLICATION_ID = MTL_CCEOI_VAR_PVT.G_ProgramAppID,
642 PROGRAM_ID = MTL_CCEOI_VAR_PVT.G_ProgramID,
643 REQUEST_ID = MTL_CCEOI_VAR_PVT.G_RequestID,
644 PROGRAM_UPDATE_DATE = SYSDATE
645 WHERE
646 NVL(PROCESS_FLAG, 1) = 1
647 AND NVL(LOCK_FLAG, 2) = 2
648 AND NVL(PROCESS_MODE, 3) = 3
649 AND NVL(STATUS_FLAG,4) = 4
650 AND NVL(DELETE_FLAG, 2) = 2
651 AND cc_entry_interface_group_id IS NULL
652 AND ROWNUM <= L_CountRows;
653 ELSE
654 UPDATE mtl_cc_entries_interface
655 SET
656 cc_entry_interface_group_id = L_NextGroupID,
657 LAST_UPDATE_DATE = SYSDATE,
658 LAST_UPDATED_BY = MTL_CCEOI_VAR_PVT.G_UserID,
659 LAST_UPDATE_LOGIN = MTL_CCEOI_VAR_PVT.G_LoginID,
660 PROGRAM_APPLICATION_ID = MTL_CCEOI_VAR_PVT.G_ProgramAppID,
661 PROGRAM_ID = MTL_CCEOI_VAR_PVT.G_ProgramID,
662 REQUEST_ID = MTL_CCEOI_VAR_PVT.G_RequestID,
663 PROGRAM_UPDATE_DATE = SYSDATE
664 WHERE
665 CYCLE_COUNT_HEADER_ID = P_Cycle_Count_Header_Id
666 AND NVL(PROCESS_FLAG, 1) = 1
667 AND NVL(LOCK_FLAG, 2) = 2
668 AND NVL(PROCESS_MODE, 3) = 3
669 AND NVL(STATUS_FLAG,4) = 4
670 AND NVL(DELETE_FLAG, 2) = 2
671 AND cc_entry_interface_group_id IS NULL
672 AND ROWNUM <= L_CountRows;
673 END IF;
674 --
675 -- Launch a worker request for the current header id.
676 --dbms_output.put_line('Launching Worker');
677 L_NewReqID := FND_REQUEST.SUBMIT_REQUEST(
678 application => 'INV',
679 program => 'MTL_CCEOI_WORKER',
680 description => 'Cycle Count Entries Open Interface Worker',
681 -- start_time => NULL,
682 argument1 => to_char(L_NextGroupID),
683 argument2 => to_char(P_Commit_point),
684 argument3 => to_char(P_ErrorReportLev),
685 argument4 => to_char(P_DeleteProcRec));
686 -- argument5 => chr(0));
687 -- If the new request id = 0, abort because the
688 -- request submission failed, otherwise commit
689 -- the request.
690 IF L_NewReqID = 0 THEN
691 -- Write error to log file and exit.
692 L_ErrorText := FND_MESSAGE.GET;
693 FND_FILE.PUT_LINE(FND_FILE.LOG, L_ErrorText);
694 APP_EXCEPTION.RAISE_EXCEPTION;
695 ELSE
696 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Record set with '||
697 'CC_ENTRY_INTERFACE_GROUP_ID = '||
698 TO_CHAR(L_NextGroupID) || 'created.');
699 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Record set processed'||
700 ' by the worker request '|| TO_CHAR(L_NewReqID));
701 COMMIT;
702 END IF;
703 END LOOP;
704 END IF;
705 IF RETCODE = 'SUCCESS'THEN
706 L_CONC_STATUS := FND_CONCURRENT.SET_COMPLETION_STATUS('NORMAL',
707 NULL);
708 ELSIF
709 RETCODE = 'WARNING'THEN
710 L_CONC_STATUS := FND_CONCURRENT.SET_COMPLETION_STATUS('WARNING',
711 NULL);
712 ELSE
713 L_CONC_STATUS := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR',
714
715 NULL);
716 END IF;
717 END;
718 END;
719 --
720 -- Concurrent program Purge
721 PROCEDURE Purge_CCEntriesIface(
722 ERRBUF OUT NOCOPY VARCHAR2 ,
723 RETCODE OUT NOCOPY VARCHAR2 )
724 IS
725 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
726 BEGIN
727 -- Start OF comments
728 -- API name : Purge_CCEntriesIface
729 -- TYPE : Private
730 -- Pre-reqs :
731 -- FUNCTION:
732 -- purges all records that are marked FOR deletion
733 -- OR successfully processed
734 -- Parameters:
735 -- OUT :
736 -- ERRBUF OUT VARCHAR2 (required)
737 -- returns any error message
738 --
739 -- RETCODE OUT VARCHAR2 (required)
740 -- return completion status
741 -- 0 = 'SUCCESS'
742 -- 1 = 'WARNING'
743 -- 2 = 'ERROR'
744 --
745 -- Version : Current Version 0.9
746 --
747 -- initial version 0.9
748 -- Notes :
749 -- END OF comments
750 DECLARE
751 --
752 -- All successfully processed records without errors
753 -- OR records which marks FOR deletion
754 CURSOR L_Purge_Iface_Csr IS
755 SELECT
756 *
757 FROM
758 MTL_CC_ENTRIES_INTERFACE
759 WHERE
760 (ERROR_FLAG = 2
761 AND STATUS_FLAG IN(0, 1))
762 OR DELETE_FLAG = 1;
763 --
764 L_return_status VARCHAR2(30);
765 L_msg_count NUMBER;
766 L_msg_data VARCHAR2(2000);
767 L_errorcode NUMBER;
768 L_counter NUMBER := 0;
769 L_CONC_STATUS BOOLEAN;
770 L_recs NUMBER := 0;
771 --
772 BEGIN
773 --
774 RETCODE := 'SUCCESS';
775 --
776 inv_cceoi_set_log_file ;
777 --FND_FILE.PUT_NAMES('/sqlcom/log', '/sqlcom/log', '/sqlcom/log');
778 -- Test area
779 begin
780 select count(*)
781 into L_recs
782 FROM
783 MTL_CC_ENTRIES_INTERFACE
784 WHERE
785 (ERROR_FLAG = 2 AND STATUS_FLAG IN (0, 1))
786 OR DELETE_FLAG = 1;
787 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Deleting rows : '||to_char(L_recs));
788 RETCODE := 'SUCCESS';
789 exception
790 when others then null;
791 end;
792 --
793 FOR c_rec IN L_Purge_Iface_Csr LOOP
794 -- IS it an exported RECORD
795 IF c_rec.cycle_count_entry_id IS NOT NULL THEN
796 -- reset the export_flag in the mtl_cycle_count_entries table
797 MTL_CCEOI_PROCESS_PVT.Set_CCExport(
798 p_api_version => 0.9,
799 X_return_status=> L_return_status,
800 x_msg_count => L_msg_count,
801 x_msg_data => L_msg_data,
802 p_cycle_count_entry_id =>
803 c_rec.cycle_count_entry_id,
804 p_export_flag=> 2);
805 --
806 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
807 ERRBUF := L_msg_data;
808 RETCODE:= 'ERROR';
809 ELSE
810 RETCODE := 'SUCCESS';
811 END IF;
812 END IF;
813 --
814 --
815 IF RETCODE = 'SUCCESS'THEN
816 -- DELETE errors
817 MTL_CCEOI_PROCESS_PVT.Delete_CCEOIError(
818 p_cc_entry_interface_id => c_rec.cc_entry_interface_id);
819 --
820 -- DELETE interface RECORD
821 MTL_CCEOI_PROCESS_PVT.Delete_CCIEntry(
822 p_cc_entry_interface_id => c_rec.cc_entry_interface_id);
823 --
824 L_counter := L_counter + 1;
825 END IF;
826 END LOOP;
827 --
828 -- COMMIT;
829 --
830 -- How many rows are deleted
831 FND_FILE.PUT_LINE(FND_FILE.LOG,
832 'Deleted rows ='|| TO_CHAR(L_counter));
833 --
834 IF RETCODE = 'SUCCESS'THEN
835 L_CONC_STATUS := FND_CONCURRENT.SET_COMPLETION_STATUS('NORMAL',
836 L_return_status);
837 ELSIF
838 RETCODE = 'WARNING'THEN
839 L_CONC_STATUS := FND_CONCURRENT.SET_COMPLETION_STATUS('WARNING',
840 L_return_status);
841 ELSE
842 L_CONC_STATUS := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR',
843 L_return_status);
844 END IF;
845 END;
846 END;
847 --
848 -- Worker for record processing
849 PROCEDURE Worker_CCEntriesIface(
850 ERRBUF OUT NOCOPY VARCHAR2,
851 RETCODE OUT NOCOPY VARCHAR2,
852 P_CC_Interface_Group_Id IN NUMBER ,
853 p_commit_point IN NUMBER DEFAULT 100,
854 P_ErrorReportLev IN NUMBER DEFAULT 2,
855 P_DeleteProcRec IN NUMBER DEFAULT 2)
856 IS
857 l_interface_id NUMBER;
858 l_errorcode NUMBER;
859 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
860 BEGIN
861 -- Start OF comments
862 -- API name : Worker_CCEntriesIface
863 -- TYPE : Private
864 -- Pre-reqs :
865 -- FUNCTION:
866 -- processed interface ROW sets AND calls according to the
867 -- action code the apprepriate Private action API procedures
868 -- Parameters :
869 -- OUT :
870 -- ERRBUF OUT VARCHAR2 (required)
871 -- returns any error message
872 --
873 -- RETCODE OUT VARCHAR2 (required)
874 -- return completion status
875 -- 0 = 'SUCCESS'
876 -- 1 = 'WARNING'
877 -- 2 = 'ERROR'
878 --
879 -- IN :
880 -- P_CC_Interface_Group_Id IN NUMBER (required)
881 -- Cycle COUNT Entries interface Group ID FOR RECORD processing
882 --
883 -- P_Commit_point IN NUMBER (required - defaulted)
884 -- default = 100
885 -- COMMIT Point FOR the worker process
886 --
887 -- P_ErrorReportLev NUMBER DEFAULT 2 (required - defaulted)
888 -- Error Reporting Level
889 -- 1=Abort on first error.
890 -- 2=Process all errors and warnings.
891 --
892 -- P_DeleteProcRec NUMBER DEFAULT 2 (required - defaulted)
893 -- DELETE Processed Record
894 -- 1=DELETE successfully processed rows.
895 -- 2=Do not delete processed rows.
896 --
897 -- Version : Current Version 0.9
898 --
899 -- initial version 0.9
900 --
901 -- Notes :
902 -- END OF comments
903 DECLARE
904 --
905 CURSOR L_CCEOI_records_CSR(id IN NUMBER) IS
906 SELECT * FROM MTL_CC_ENTRIES_INTERFACE
907 WHERE
908 CC_ENTRY_INTERFACE_GROUP_ID = id
909 -- AND LOCK_FLAG = 1 -- record will be locked by public API
910 AND NVL(PROCESS_FLAG, 1) = 1
911 AND NVL(PROCESS_MODE, 3) IN(2, 3)
912 AND NVL(STATUS_FLAG,4)=4
913 AND NVL(DELETE_FLAG,2)=2;
914 --
915 L_iface_rec MTL_CCEOI_VAR_PVT.INV_CCEOI_TYPE;
916 L_errorcode NUMBER;
917 L_return_status VARCHAR2(30);
918 L_msg_count NUMBER;
919 L_msg_data VARCHAR2(32000);
920 L_CONC_STATUS BOOLEAN;
921 L_counter integer := 0;
922 --
923 BEGIN
924
925 --
926 inv_cceoi_set_log_file ;
927 ---FND_FILE.PUT_NAMES('/sqlcom/log', '/sqlcom/log', '/sqlcom/log');
928 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Worker');
929 --
930 -- Validate the input parameters
931 IF P_CC_Interface_Group_Id < 1 THEN
932 ERRBUF := FND_MESSAGE.GET_STRING('INV', 'INV_CCEOI_WRONG_GROUPID');
933 FND_FILE.PUT_LINE(FND_FILE.LOG, ERRBUF);
934 APP_EXCEPTION.RAISE_EXCEPTION;
935 END IF;
936 --
937
938 IF p_commit_point < 1 THEN
939 ERRBUF := FND_MESSAGE.GET_STRING('INV', 'INV_CCEOI_WRONG_COMMITPOINT');
940 FND_FILE.PUT_LINE(FND_FILE.LOG, ERRBUF);
941 APP_EXCEPTION.RAISE_EXCEPTION;
942 END IF;
943 --
944
945 IF P_ErrorReportLev NOT IN(1, 2) THEN
946 ERRBUF := FND_MESSAGE.GET_STRING('INV', 'INV_CCEOI_WRONG_ERRORLEVEL');
947 FND_FILE.PUT_LINE(FND_FILE.LOG, ERRBUF);
948 APP_EXCEPTION.RAISE_EXCEPTION;
949 END IF;
950 --
951
952 IF P_DeleteProcRec NOT IN(1, 2) THEN
953 ERRBUF := FND_MESSAGE.GET_STRING('INV', 'INV_CCEOI_WRONG_DELETEREC');
954 FND_FILE.PUT_LINE(FND_FILE.LOG, ERRBUF);
955 APP_EXCEPTION.RAISE_EXCEPTION;
956 END IF;
957
958 -- END OF VALIDATION
959 --
960 -- Before processing lock all records for this worker
961 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Worker: before process lock all recs');
962 UPDATE mtl_cc_entries_interface
963 SET
964 LAST_UPDATE_DATE = SYSDATE,
965 LAST_UPDATED_BY = MTL_CCEOI_VAR_PVT.G_UserID,
966 LAST_UPDATE_LOGIN = MTL_CCEOI_VAR_PVT.G_LoginID,
967 PROGRAM_APPLICATION_ID = MTL_CCEOI_VAR_PVT.G_ProgramAppID,
968 PROGRAM_ID = MTL_CCEOI_VAR_PVT.G_ProgramID,
969 REQUEST_ID = MTL_CCEOI_VAR_PVT.G_RequestID,
970 PROGRAM_UPDATE_DATE = sysdate
971 -- LOCK_FLAG = 1
972 WHERE
973 cc_entry_interface_group_id = P_CC_Interface_Group_ID
974 AND NVL(lock_flag, 2) = 2
975 AND NVL(PROCESS_FLAG, 1) = 1
976 AND NVL(PROCESS_MODE, 3) IN(2, 3);
977 commit;
978
979 --
980 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Worker: After process lock all recs');
981 FOR c_rec IN L_CCEOI_records_CSR(P_CC_Interface_Group_ID) LOOP
982
983 -- Current processed interface RECORD
984 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Worker: Inside Loop-process IR ');
985 MTL_CCEOI_VAR_PVT.G_cc_entry_interface_id :=
986 c_rec.cc_entry_interface_id;
987 -- Defined by suresh
988 MTL_CCEOI_VAR_PVT.G_INVENTORY_ITEM_ID := c_rec.inventory_item_id;
989 MTL_CCEOI_VAR_PVT.G_CC_HEADER_ID := c_rec.cycle_count_header_id;
990 MTL_CCEOI_VAR_PVT.G_COUNT_QUANTITY := c_rec.count_quantity;
991 MTL_CCEOI_VAR_PVT.G_SECONDARY_COUNT_QUANTITY := c_rec.secondary_count_quantity; -- INVCONV
992 MTL_CCEOI_VAR_PVT.G_COUNT_DATE := c_rec.count_date;
993 MTL_CCEOI_VAR_PVT.G_LOCATOR_ID := c_rec.locator_id;
994 MTL_CCEOI_VAR_PVT.G_SUBINVENTORY := c_rec.subinventory;
995 -- Adding by suresh
996 MTL_CCEOI_VAR_PVT.G_SKU_REC.REVISION := c_rec.revision;
997 MTL_CCEOI_VAR_PVT.G_SKU_REC.LOT_NUMBER := c_rec.LOT_NUMBER;
998 MTL_CCEOI_VAR_PVT.G_SKU_REC.SERIAL_NUMBER := c_rec.SERIAL_NUMBER;
999
1000 --p_sku_rec.revision := c_rec.revision;
1001 FND_FILE.PUT_LINE(FND_FILE.LOG, 'System Qty before assign IRec '||to_char(c_rec.system_quantity));
1002 FND_FILE.PUT_LINE(FND_FILE.LOG, 'CCEntry ID '||to_char(c_rec.cycle_count_entry_id));
1003 -- End of definition
1004 --
1005 L_iface_rec.parent_lpn_id := c_rec.parent_lpn_id;
1006 L_iface_rec.outermost_lpn_id := c_rec.outermost_lpn_id;
1007 L_iface_rec.cost_group_id := c_rec.cost_group_id;
1008 L_iface_rec.cc_entry_interface_id := c_rec.cc_entry_interface_id ;
1009 L_iface_rec.organization_id := c_rec.organization_id ;
1010 L_iface_rec.last_update_date := c_rec.last_update_date ;
1011 L_iface_rec.last_updated_by := c_rec.last_updated_by ;
1012 L_iface_rec.creation_date := c_rec.creation_date ;
1013 L_iface_rec.created_by := c_rec.created_by ;
1014 L_iface_rec.last_update_login := c_rec.last_update_login ;
1015 L_iface_rec.cc_entry_interface_group_id := c_rec.cc_entry_interface_group_id ;
1016 L_iface_rec.cycle_count_entry_id := c_rec.cycle_count_entry_id ;
1017 L_iface_rec.action_code := c_rec.action_code ;
1018 L_iface_rec.cycle_count_header_id := c_rec.cycle_count_header_id ;
1019 L_iface_rec.cycle_count_header_name := c_rec.cycle_count_header_name ;
1020 L_iface_rec.count_list_sequence := c_rec.count_list_sequence ;
1021 L_iface_rec.inventory_item_id := c_rec.inventory_item_id ;
1022 L_iface_rec.item_segment1 := c_rec.item_segment1 ;
1023 L_iface_rec.item_segment2 := c_rec.item_segment2 ;
1024 L_iface_rec.item_segment3 := c_rec.item_segment3 ;
1025 L_iface_rec.item_segment4 := c_rec.item_segment4 ;
1026 L_iface_rec.item_segment5 := c_rec.item_segment5 ;
1027 L_iface_rec.item_segment6 := c_rec.item_segment6 ;
1028 L_iface_rec.item_segment7 := c_rec.item_segment7 ;
1029 L_iface_rec.item_segment8 := c_rec.item_segment8 ;
1030 L_iface_rec.item_segment9 := c_rec.item_segment9 ;
1031 L_iface_rec.item_segment10 := c_rec.item_segment10 ;
1032 L_iface_rec.item_segment11 := c_rec.item_segment11 ;
1033 L_iface_rec.item_segment12 := c_rec.item_segment12 ;
1034 L_iface_rec.item_segment13 := c_rec.item_segment13 ;
1035 L_iface_rec.item_segment14 := c_rec.item_segment14 ;
1036 L_iface_rec.item_segment15 := c_rec.item_segment15 ;
1037 L_iface_rec.item_segment16 := c_rec.item_segment16 ;
1038 L_iface_rec.item_segment17 := c_rec.item_segment17 ;
1039 L_iface_rec.item_segment18 := c_rec.item_segment18 ;
1040 L_iface_rec.item_segment19 := c_rec.item_segment19 ;
1041 L_iface_rec.item_segment20 := c_rec.item_segment20 ;
1042 L_iface_rec.revision := c_rec.revision ;
1043 L_iface_rec.subinventory := c_rec.subinventory ;
1044 L_iface_rec.locator_id := c_rec.locator_id ;
1045 L_iface_rec.locator_segment1 := c_rec.locator_segment1 ;
1046 L_iface_rec.locator_segment2 := c_rec.locator_segment2 ;
1047 L_iface_rec.locator_segment3 := c_rec.locator_segment3 ;
1048 L_iface_rec.locator_segment4 := c_rec.locator_segment4 ;
1049 L_iface_rec.locator_segment5 := c_rec.locator_segment5 ;
1050 L_iface_rec.locator_segment6 := c_rec.locator_segment6 ;
1051 L_iface_rec.locator_segment7 := c_rec.locator_segment7 ;
1052 L_iface_rec.locator_segment8 := c_rec.locator_segment8 ;
1053 L_iface_rec.locator_segment9 := c_rec.locator_segment9 ;
1054 L_iface_rec.locator_segment10 := c_rec.locator_segment10 ;
1055 L_iface_rec.locator_segment11 := c_rec.locator_segment11 ;
1056 L_iface_rec.locator_segment12 := c_rec.locator_segment12 ;
1057 L_iface_rec.locator_segment13 := c_rec.locator_segment13 ;
1058 L_iface_rec.locator_segment14 := c_rec.locator_segment14 ;
1059 L_iface_rec.locator_segment15 := c_rec.locator_segment15 ;
1060 L_iface_rec.locator_segment16 := c_rec.locator_segment16 ;
1061 L_iface_rec.locator_segment17 := c_rec.locator_segment17 ;
1062 L_iface_rec.locator_segment18 := c_rec.locator_segment18 ;
1063 L_iface_rec.locator_segment19 := c_rec.locator_segment19 ;
1064 L_iface_rec.locator_segment20 := c_rec.locator_segment20 ;
1065 L_iface_rec.lot_number := c_rec.lot_number ;
1066 L_iface_rec.serial_number := c_rec.serial_number ;
1067 L_iface_rec.primary_uom_quantity := c_rec.primary_uom_quantity ;
1068 L_iface_rec.count_uom := c_rec.count_uom ;
1069 L_iface_rec.count_unit_of_measure := c_rec.count_unit_of_measure ;
1070 L_iface_rec.count_quantity := c_rec.count_quantity ;
1071 L_iface_rec.system_quantity := c_rec.system_quantity ;
1072 L_iface_rec.adjustment_account_id := c_rec.adjustment_account_id ;
1073 L_iface_rec.account_segment1 := c_rec.account_segment1 ;
1074 L_iface_rec.account_segment2 := c_rec.account_segment2 ;
1075 L_iface_rec.account_segment3 := c_rec.account_segment3 ;
1076 L_iface_rec.account_segment4 := c_rec.account_segment4 ;
1077 L_iface_rec.account_segment5 := c_rec.account_segment5 ;
1078 L_iface_rec.account_segment6 := c_rec.account_segment6 ;
1079 L_iface_rec.account_segment7 := c_rec.account_segment7 ;
1080 L_iface_rec.account_segment8 := c_rec.account_segment8 ;
1081 L_iface_rec.account_segment9 := c_rec.account_segment9 ;
1082 L_iface_rec.account_segment10 := c_rec.account_segment10 ;
1083 L_iface_rec.account_segment11 := c_rec.account_segment11 ;
1084 L_iface_rec.account_segment12 := c_rec.account_segment12 ;
1085 L_iface_rec.account_segment13 := c_rec.account_segment13 ;
1086 L_iface_rec.account_segment14 := c_rec.account_segment14 ;
1087 L_iface_rec.account_segment15 := c_rec.account_segment15 ;
1088 L_iface_rec.account_segment16 := c_rec.account_segment16 ;
1089 L_iface_rec.account_segment17 := c_rec.account_segment17 ;
1090 L_iface_rec.account_segment18 := c_rec.account_segment18 ;
1091 L_iface_rec.account_segment19 := c_rec.account_segment19 ;
1092 L_iface_rec.account_segment20 := c_rec.account_segment20 ;
1093 L_iface_rec.account_segment21 := c_rec.account_segment21 ;
1094 L_iface_rec.account_segment22 := c_rec.account_segment22 ;
1095 L_iface_rec.account_segment23 := c_rec.account_segment23 ;
1096 L_iface_rec.account_segment24 := c_rec.account_segment24 ;
1097 L_iface_rec.account_segment25 := c_rec.account_segment25 ;
1098 L_iface_rec.account_segment26 := c_rec.account_segment26 ;
1099 L_iface_rec.account_segment27 := c_rec.account_segment27 ;
1100 L_iface_rec.account_segment28 := c_rec.account_segment28 ;
1101 L_iface_rec.account_segment29 := c_rec.account_segment29 ;
1102 L_iface_rec.account_segment30 := c_rec.account_segment30 ;
1103 L_iface_rec.count_date := c_rec.count_date ;
1104 L_iface_rec.employee_id := c_rec.employee_id ;
1105 L_iface_rec.employee_full_name := c_rec.employee_full_name ;
1106 L_iface_rec.reference := c_rec.reference ;
1107 L_iface_rec.transaction_reason_id := c_rec.transaction_reason_id ;
1108 L_iface_rec.transaction_reason := c_rec.transaction_reason ;
1109 L_iface_rec.request_id := c_rec.request_id ;
1110 L_iface_rec.program_application_id := c_rec.program_application_id ;
1111 L_iface_rec.program_id := c_rec.program_id ;
1112 L_iface_rec.program_update_date := c_rec.program_update_date ;
1113 L_iface_rec.lock_flag := c_rec.lock_flag ;
1114 L_iface_rec.process_flag := c_rec.process_flag ;
1115 L_iface_rec.process_mode := c_rec.process_mode ;
1116 L_iface_rec.valid_flag := c_rec.valid_flag ;
1117 L_iface_rec.delete_flag := c_rec.delete_flag ;
1118 L_iface_rec.status_flag := c_rec.status_flag ;
1119 L_iface_rec.error_flag := c_rec.error_flag ;
1120 L_iface_rec.attribute_category := c_rec.attribute_category ;
1121 L_iface_rec.attribute1 := c_rec.attribute1 ;
1122 L_iface_rec.attribute2 := c_rec.attribute2 ;
1123 L_iface_rec.attribute3 := c_rec.attribute3 ;
1124 L_iface_rec.attribute4 := c_rec.attribute4 ;
1125 L_iface_rec.attribute5 := c_rec.attribute5 ;
1126 L_iface_rec.attribute6 := c_rec.attribute6 ;
1127 L_iface_rec.attribute7 := c_rec.attribute7 ;
1128 L_iface_rec.attribute8 := c_rec.attribute8 ;
1129 L_iface_rec.attribute9 := c_rec.attribute9 ;
1130 L_iface_rec.attribute10 := c_rec.attribute10 ;
1131 L_iface_rec.attribute11 := c_rec.attribute11 ;
1132 L_iface_rec.attribute12 := c_rec.attribute12 ;
1133 L_iface_rec.attribute13 := c_rec.attribute13 ;
1134 L_iface_rec.attribute14 := c_rec.attribute14 ;
1135 L_iface_rec.attribute15 := c_rec.attribute15 ;
1136 L_iface_rec.project_id := c_rec.project_id ;
1137 L_iface_rec.task_id := c_rec.task_id ;
1138 -- BEGIN INVCONV
1139 L_iface_rec.secondary_uom := c_rec.secondary_uom;
1140 L_iface_rec.secondary_unit_of_measure := c_rec.secondary_unit_of_measure;
1141 L_iface_rec.secondary_count_quantity := c_rec.secondary_count_quantity;
1142 L_iface_rec.secondary_system_quantity := c_rec.secondary_system_quantity;
1143 -- END INVCONV
1144
1145
1146 --
1147 --
1148 FND_FILE.PUT_LINE(FND_FILE.LOG,'Worker :CountQty '||to_char(L_iface_rec.count_quantity));
1149 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Worker: Action-'||to_char(c_rec.action_code));
1150
1151 MTL_CCEOI_ACTION_PUB.Import_CountRequest(
1152 p_api_version => 0.9,
1153 p_init_msg_list => FND_API.G_TRUE,
1154 x_return_status => L_return_status,
1155 x_msg_count => L_msg_count,
1156 x_msg_data => L_msg_data,
1157 x_errorcode => l_errorcode,
1158 P_interface_rec => L_iface_rec,
1159 x_interface_id=>l_interface_id);
1160
1161 /* -- switched to calling public API instead of repeating same code
1162 IF c_rec.action_code = 10 THEN
1163 -- Export not supported by the worker
1164 ERRBUF := FND_MESSAGE.GET_STRING('INV',
1165 'INV_CCEOI_UNKNOWN_ACTION_CODE');
1166 FND_FILE.PUT_LINE(FND_FILE.LOG,
1167 ERRBUF);
1168 ELSIF
1169 c_rec.action_code = MTL_CCEOI_VAR_PVT.G_VALIDATE THEN
1170 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Worker: Action-is Vaildate ');
1171 MTL_CCEOI_ACTION_PVT.Validate_CountRequest(
1172 p_api_version => 0.9,
1173 p_init_msg_list => FND_API.G_TRUE,
1174 x_return_status => L_return_status,
1175 x_msg_count => L_msg_count,
1176 x_msg_data => L_msg_data,
1177 P_interface_rec => L_iface_rec);
1178 ELSIF
1179 c_rec.action_code = MTL_CCEOI_VAR_PVT.G_CREATE THEN
1180 MTL_CCEOI_ACTION_PVT.Create_CountRequest(
1181 p_api_version => 0.9,
1182 p_init_msg_list => FND_API.G_TRUE,
1183 x_return_status => L_return_status,
1184 x_msg_count => L_msg_count,
1185 x_msg_data => L_msg_data,
1186 P_interface_rec => L_iface_rec);
1187 ELSIF
1188 c_rec.action_code = MTL_CCEOI_VAR_PVT.G_VALSIM THEN
1189 MTL_CCEOI_ACTION_PVT.ValSim_CountRequest(
1190 p_api_version => 0.9,
1191 p_init_msg_list => FND_API.G_TRUE,
1192 x_return_status => L_return_status,
1193 x_msg_count => L_msg_count,
1194 x_msg_data => L_msg_data,
1195 P_interface_rec => L_iface_rec);
1196 ELSIF
1197 c_rec.action_code = MTL_CCEOI_VAR_PVT.G_PROCESS THEN
1198 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Worker: Action-is process ');
1199 MTL_CCEOI_ACTION_PVT.Process_CountRequest(
1200 p_api_version => 0.9,
1201 p_init_msg_list => FND_API.G_TRUE,
1202 x_return_status => L_return_status,
1203 x_msg_count => L_msg_count,
1204 x_msg_data => L_msg_data,
1205 P_interface_rec => L_iface_rec);
1206 ELSE
1207 --Action code NOT known
1208 ERRBUF :=FND_MESSAGE.GET_STRING('INV',
1209 'INV_CCEOI_UNKNOWN_ACTION_CODE');
1210 FND_FILE.PUT_LINE(FND_FILE.LOG,
1211 ERRBUF);
1212
1213 IF p_errorreportlev = 1 THEN
1214 --
1215 -- Reswitch the Lockflag after failure
1216 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Worker: switch back lockflag-After Failure ');
1217 UPDATE mtl_cc_entries_interface
1218 SET
1219 LAST_UPDATE_DATE = SYSDATE,
1220 LAST_UPDATED_BY = MTL_CCEOI_VAR_PVT.G_UserID,
1221 LAST_UPDATE_LOGIN = MTL_CCEOI_VAR_PVT.G_LoginID,
1222 PROGRAM_APPLICATION_ID = MTL_CCEOI_VAR_PVT.G_ProgramAppID,
1223 PROGRAM_ID = MTL_CCEOI_VAR_PVT.G_ProgramID,
1224 REQUEST_ID = MTL_CCEOI_VAR_PVT.G_RequestID,
1225 PROGRAM_UPDATE_DATE = SYSDATE
1226 -- LOCK_FLAG = 2
1227 WHERE
1228 cc_entry_interface_group_id = P_CC_Interface_Group_Id;
1229 COMMIT;
1230 --
1231 APP_EXCEPTION.RAISE_EXCEPTION;
1232 END IF;
1233 END IF;
1234 */
1235 --
1236 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1237 ERRBUF := L_msg_data;
1238 FND_FILE.PUT_LINE(FND_FILE.LOG, ERRBUF);
1239 --
1240 IF P_ErrorReportLev = 1 THEN
1241 --
1242 -- Reswitch the Lockflag after failure
1243 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Worker: switch back lock-flag -2 ');
1244 UPDATE mtl_cc_entries_interface
1245 SET
1246 LAST_UPDATE_DATE = SYSDATE,
1247 LAST_UPDATED_BY = MTL_CCEOI_VAR_PVT.G_UserID,
1248 LAST_UPDATE_LOGIN = MTL_CCEOI_VAR_PVT.G_LoginID,
1249 PROGRAM_APPLICATION_ID = MTL_CCEOI_VAR_PVT.G_ProgramAppID,
1250 PROGRAM_ID = MTL_CCEOI_VAR_PVT.G_ProgramID,
1251 REQUEST_ID = MTL_CCEOI_VAR_PVT.G_RequestID,
1252 PROGRAM_UPDATE_DATE = SYSDATE
1253 -- LOCK_FLAG = 2
1254 WHERE
1255 cc_entry_interface_group_id = P_CC_Interface_Group_ID;
1256 COMMIT;
1257 --
1258 APP_EXCEPTION.RAISE_EXCEPTION;
1259 END IF;
1260 END IF;
1261
1262 L_counter := L_counter + 1;
1263 IF L_counter = p_commit_point THEN
1264 COMMIT;
1265 L_counter :=0;
1266 END IF;
1267 END LOOP;
1268 commit;
1269 --
1270 -- Reswitch the Lockflag after failure
1271 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Worker: switch back lock-flag -3 ');
1272 UPDATE mtl_cc_entries_interface
1273 SET
1274 LAST_UPDATE_DATE = SYSDATE,
1275 LAST_UPDATED_BY = MTL_CCEOI_VAR_PVT.G_UserID,
1276 LAST_UPDATE_LOGIN = MTL_CCEOI_VAR_PVT.G_LoginID,
1277 PROGRAM_APPLICATION_ID = MTL_CCEOI_VAR_PVT.G_ProgramAppID,
1278 PROGRAM_ID = MTL_CCEOI_VAR_PVT.G_ProgramID,
1279 REQUEST_ID = MTL_CCEOI_VAR_PVT.G_RequestID,
1280 PROGRAM_UPDATE_DATE = SYSDATE
1281 -- LOCK_FLAG = 2
1282 WHERE
1283 cc_entry_interface_group_id = P_CC_Interface_Group_ID;
1284 --
1285 commit;
1286 begin
1287 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Worker: Unexport all of them successfully processed');
1288 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Worker:'||to_char(P_CC_Interface_Group_ID));
1289 Update mtl_cycle_count_entries
1290 set export_flag = 2
1291 where cycle_count_entry_id
1292 in (select cycle_count_entry_id
1293 from mtl_cc_entries_interface where
1294 cc_entry_interface_group_id = P_CC_Interface_Group_ID
1295 and status_flag = 0 );
1296 --
1297 commit;
1298 exception
1299 when others
1300 then
1301 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Worker: Unexport Not updated ');
1302 end;
1303 IF P_DeleteProcRec = 1 THEN
1304 -- DELETE completed interface records
1305 -- All records which are marked FOR deletion delete_flag =1
1306 -- OR status_flag IN (0,1) AND error_flag = 2 will be deleted
1307 Purge_CCEntriesIface(ERRBUF => ERRBUF, RETCODE => RETCODE);
1308 END IF;
1309 /* IF RETCODE = 'SUCCESS'THEN
1310 L_CONC_STATUS := FND_CONCURRENT.SET_COMPLETION_STATUS('NORMAL',
1311 L_return_status);
1312
1313 ELSIF
1314 RETCODE = 'WARNING'THEN
1315 L_CONC_STATUS := FND_CONCURRENT.SET_COMPLETION_STATUS('WARNING',
1316 l_return_status);
1317 ELSE
1318 L_CONC_STATUS := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR',
1319 L_return_status);
1320 END IF;
1321 */
1322 END;
1323 END;
1324 END MTL_CCEOI_CONC_PVT;