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:
505:
506:
507: IF l_srl_tbl.count > 0 THEN
508: FORALL j in l_srl_tbl.FIRST .. l_srl_tbl.LAST
509: UPDATE CSI_INSTANCE_INTERFACE
510: set parallel_worker_id = 1
511: where serial_number = l_srl_tbl(j)
512: AND trunc(source_transaction_date) BETWEEN
513: nvl(l_txn_from_date,trunc(source_transaction_date)) AND
520: -- Get the count of the remaining records and divide that number by the number
521: -- of workers and assign that number to each record.
522: SELECT ceil(count(1)/l_worker_count)
523: INTO x_count
524: FROM csi_instance_interface
525: WHERE trunc(source_transaction_date) BETWEEN
526: nvl(l_txn_from_date,trunc(source_transaction_date)) AND
527: nvl(l_txn_to_date,trunc(source_transaction_date))
528: AND transaction_identifier IS NULL
534: -- loop thru resolve IDs so that we are able to do the uniqueness checks
535:
536: FOR l_count in 1 .. l_worker_count LOOP
537:
538: UPDATE csi_instance_interface
539: SET parallel_worker_id = l_count
540: WHERE rownum <= x_count
541: AND trunc(source_transaction_date) BETWEEN
542: nvl(l_txn_from_date,trunc(source_transaction_date)) AND
859: l_old_error:=to_number(req_data);
860: BEGIN
861: SELECT COUNT(*)
862: INTO l_new_error
863: FROM csi_instance_interface
864: WHERE process_status='E'
865: AND source_system_name = nvl(p_source_system_name,source_system_name); --included for bug5949328
866:
867: EXCEPTION