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