[Home] [Help]
26: csii.instance_id,
27: csii.quantity ,
28: csii.location_id ,
29: csii.location_type_code
30: FROM csi_instance_interface csii
31: WHERE process_status = 'R'
32: AND source_system_name = nvl(p_source_system_name,source_system_name)
33: AND EXISTS ( SELECT 1 FROM csi_i_asset_interface csiai
34: WHERE csiai.inst_interface_id = csii.inst_interface_id )
43: BEGIN
44:
45: IF csi_gen_utility_pvt.is_eib_installed = 'Y' THEN
46:
47: UPDATE csi_instance_interface
48: SET syncup_family = NULL
49: WHERE source_system_name = nvl(p_source_system_name,source_system_name)
50: AND syncup_family is not null
51: AND process_status = 'R';
129: l_process_status := NULL;
130: END IF;
131:
132: FORALL i_asst IN l_inst_intr_tbl.FIRST..l_inst_intr_tbl.LAST
133: UPDATE csi_instance_interface csii
134: SET syncup_family = l_syncup_family_seq,
135: process_status = NVL(l_process_status ,csii.process_status),
136: error_text = DECODE(l_process_status,'E',l_Asset_Error_text,
137: error_text)
176: l_debug_level NUMBER := to_number(nvl(fnd_profile.value('CSI_DEBUG_LEVEL'), '0'));
177:
178: CURSOR purge_cur is
179: SELECT inst_interface_id
180: FROM csi_instance_interface
181: WHERE process_status='P'
182: AND source_system_name = nvl(p_source_system_name,source_system_name); --included for bug5949328
183:
184:
190: -- x_return_status := l_fnd_success;
191:
192: IF p_reprocess_option ='ALL'
193: THEN
194: UPDATE csi_instance_interface cii
195: SET process_status = 'R'
196: WHERE (NVL(cii.batch_name,'$CSI_NULL_VALUE$')=NVL(p_batch_name,cii.batch_name)
197: OR NVL(cii.batch_name,'$CSI_NULL_VALUE$')=NVL(p_batch_name,'$CSI_NULL_VALUE$'))
198: AND cii.source_system_name = p_source_system_name
209: inst_intf_id_del
210: LIMIT max_buffer_size;
211:
212: FORALL i1 IN 1 .. inst_intf_id_del.COUNT
213: DELETE FROM CSI_INSTANCE_INTERFACE
214: WHERE inst_interface_id=inst_intf_id_del(i1)
215: AND source_system_name = nvl(p_source_system_name,source_system_name); --included for bug5949328
216:
217:
329: l_dummy NUMBER;
330: l_debug_level NUMBER := to_number(nvl(fnd_profile.value('CSI_DEBUG_LEVEL'), '0'));
331: cursor c_id (pc_worker_id IN NUMBER) is
332: SELECT inst_interface_id,parallel_worker_id
333: FROM csi_instance_interface
334: WHERE parallel_worker_id = pc_worker_id
335: AND process_status = 'R'
336: AND source_system_name = nvl(p_source_system_name,source_system_name);
337:
336: AND source_system_name = nvl(p_source_system_name,source_system_name);
337:
338: CURSOR candidates_exist_cur IS
339: SELECT distinct parallel_worker_id
340: FROM csi_instance_interface
341: WHERE process_status = 'R'
342: AND source_system_name = nvl(p_source_system_name,source_system_name) --included for bug5949328
343: AND parallel_worker_id IS NOT NULL
344: AND transaction_identifier IS NULL;
344: AND transaction_identifier IS NULL;
345:
346: CURSOR SRL_CUR IS
347: select serial_number
348: from csi_instance_interface
349: where source_system_name = nvl(p_source_system_name,source_system_name)
350: and serial_number is not null
351: and process_status = 'R'
352: group by serial_number
382:
383:
384: CURSOR purge_cur IS
385: SELECT inst_interface_id
386: FROM csi_instance_interface
387: WHERE process_status='P'
388: AND source_system_name = nvl(p_source_system_name,source_system_name); --included for bug5949328
389:
390: TYPE NumTabType IS VARRAY(10000) OF NUMBER;
401: -- Since req_data is null, I'll assume this is the first run.
402: BEGIN
403: SELECT COUNT(*)
404: INTO l_old_error
405: FROM csi_instance_interface
406: WHERE process_status='E'
407: AND source_system_name = nvl(p_source_system_name,source_system_name); --included for bug5949328
408:
409: EXCEPTION
425: inst_intf_id_del
426: LIMIT max_buffer_size;
427:
428: FORALL i1 IN 1 .. inst_intf_id_del.COUNT
429: DELETE FROM CSI_INSTANCE_INTERFACE
430: WHERE inst_interface_id=inst_intf_id_del(i1)
431: AND source_system_name = nvl(p_source_system_name,source_system_name); --included for bug5949328;
432:
433:
501: END LOOP;
502: --
503: IF l_srl_tbl.count > 0 THEN
504: FORALL j in l_srl_tbl.FIRST .. l_srl_tbl.LAST
505: UPDATE CSI_INSTANCE_INTERFACE
506: set parallel_worker_id = 1
507: where serial_number = l_srl_tbl(j)
508: and source_system_name = nvl(p_source_system_name,source_system_name)
509: and process_status = 'R';
515: l_txn_from_date := to_date(p_txn_from_date, 'YYYY/MM/DD HH24:MI:SS');
516: l_txn_to_date := to_date(p_txn_to_date, 'YYYY/MM/DD HH24:MI:SS');
517: SELECT ceil(count(1)/l_worker_count)
518: INTO x_count
519: FROM csi_instance_interface
520: WHERE trunc(source_transaction_date) BETWEEN
521: nvl(l_txn_from_date,trunc(source_transaction_date)) AND
522: nvl(l_txn_to_date,trunc(source_transaction_date))
523: AND transaction_identifier IS NULL
529: -- loop thru resolve IDs so that we are able to do the uniqueness checks
530:
531: FOR l_count in 1 .. l_worker_count LOOP
532:
533: UPDATE csi_instance_interface
534: SET parallel_worker_id = l_count
535: WHERE rownum <= x_count
536: AND parallel_worker_id = -1
537: AND source_system_name = nvl(p_source_system_name,source_system_name) --Added for bug 3621991
851: l_old_error:=to_number(req_data);
852: BEGIN
853: SELECT COUNT(*)
854: INTO l_new_error
855: FROM csi_instance_interface
856: WHERE process_status='E'
857: AND source_system_name = nvl(p_source_system_name,source_system_name); --included for bug5949328
858:
859: EXCEPTION