Hi Tom,

I am facing issue with insert and show the conflicts.its taking to much time for 2000 records its taking time 25 mins we have to implement millions records.can you please help on this.
please find the below procedure.

CREATE OR REPLACE PROCEDURE OCE_APP2.sp_get_bulk_equal_ram1(
out_equal_conflicts OUT sys_refcursor )
/***************************************************************************
* Procedure: sp_get_equal_conflicts
*
* Project: DSG-OCE
*
* Purpose: TO get the equal conflicts for the created overrides.
*
*
* Developer: TCSL
*
* Created Date: 23-JUN-2017
*
* Modification Details:
* Date USER STORY Description
* --------------------------------------------------------------------------
*
***************************************************************************/
IS
IN_CONFLICT_DIRECTION VARCHAR2(4000);
l_Current_Priority VARCHAR2(4000);
l_OVERRIDE_LEVEL VARCHAR2(4000);
l_Dept_Count VARCHAR2(4000);
l_SUB_DEPT_COUNT VARCHAR2(4000);
l_Class_Count VARCHAR2(4000);
l_Sub_Class_Count VARCHAR2(4000);
l_Style_Count number;
l_SKU_Count number;
l_Style_Count_Lower VARCHAR2(4000);
l_SKU_Count_Lower NUMBER;
l_Chain_Count number;
L_STORE_COUNT number;
l_Start_Date VARCHAR2(4000);
l_Program_id VARCHAR2(4000);
l_End_date VARCHAR2(4000);
l_prodWhere clob;
l_prodWhereSS clob;
l_prodWhereSS2 clob;
l_prodWhereTemp clob;
l_orgWhere clob;
l_orgWhere2 clob;
l_conflictDirection clob;
l_includedStatus clob;
l_ssWhere clob;
l_query_str clob;
l_query clob;
in_bulk_count number ;
l_OR_SESSION_ID number;
l_STORE_NUMBER number;
l_CHAIN_NUMBER number;

CURSOR c_data IS
select /*+ parallel(A,4) */ OR_SESSION_ID ,STORE_NUMBER ,CHAIN_NUMBER ,PRIORITY l_Current_Priority,
A.OVERRIDE_LEVEL l_OVERRIDE_LEVEL,
OVERRIDE_START_DATE l_Start_Date,
OVERRIDE_END_DATE l_End_Date,
PROGRAM_ID l_Program_Id,
COUNT(STYLE_NUMBER) over ( partition by OR_SESSION_ID ) l_Style_Count,
COUNT(SKU_NUMBER) over ( partition by OR_SESSION_ID ) l_sku_count,
COUNT(CHAIN_NUMBER) over ( partition by OR_SESSION_ID ) l_chain_count,
COUNT(STORE_NUMBER) over ( partition by OR_SESSION_ID ) l_store_count
from OCE_BULK_PENDING_OVERRIDE A,OCE_OVERRIDE_LEVEL b
where a.OVERRIDE_LEVEL=B.OVERRIDE_LEVEL;
-- type t_bulk_collect_test_tab is table of c_data%rowtype;
-- l_tab t_bulk_collect_test_tab;

BEGIN

execute immediate 'truncate table tmp_bulk_duplicate_conflicts';


-- OPEN l_tab;
-- LOOP
-- FETCH l_tab into l_OR_SESSION_ID ,l_STORE_NUMBER ,l_CHAIN_NUMBER
-- ,l_Current_Priority,l_OVERRIDE_LEVEL,l_Start_Date
-- ,l_End_Date,l_Program_Id,l_Style_Count,l_sku_count,l_chain_count,l_store_count;

for l_tab in c_data loop
-- exit when l_tab%notfound;

IF l_tab.l_Style_Count > 0 THEN
l_prodWhere := ' AND UPPER(A.STYLE_NUMBER) IN (select distinct style_number from oce_app2.OCE_BULK_PENDING_OVERRIDE where style_number is not null and OR_SESSION_ID=' ||l_tab.OR_SESSION_ID || ') ';
l_prodWhereSS := l_prodWhereSS || l_prodWhere;
l_ssWhere := ' AND UPPER(trim(A.STYLE_NUMBER)) IN (SELECT DISTINCT STYLE_NUMBER FROM oce_app2.OCE_BULK_PENDING_OVERRIDE WHERE style_number is not null and or_session_id = ' || l_tab.or_session_id || ') ';
END IF;
IF l_tab.l_SKU_Count > 0 THEN
l_prodWhere := ' AND A.SKU_NUMBER IN (select distinct sku_number from oce_app2.OCE_BULK_PENDING_OVERRIDE where sku_number is not null and or_session_id=' ||l_tab.or_session_id || ') ';
l_prodWhereSS := l_prodWhereSS || ' AND A.SKU_NUMBER IN (SELECT DISTINCT SKU_NUMBER FROM oce_app2.OCE_BULK_PENDING_OVERRIDE where sku_number is not null ) ';
l_ssWhere := ' AND A.SKU_NUMBER IN (SELECT DISTINCT SKU_NUMBER FROM oce_app2.OCE_BULK_PENDING_OVERRIDE WHERE sku_number is not null and or_session_id = ' || l_tab.or_session_id || ') ';
END IF;
IF l_tab.l_Chain_Count > 0 THEN
IF SUBSTR(l_OVERRIDE_LEVEL, INSTR(l_OVERRIDE_LEVEL, '-')+1 ) = 'STORE' THEN
l_orgWhere := l_orgWhere;
ELSE
l_orgWhere := ' AND A.CHAIN_NUMBER IN ( ' || l_tab.CHAIN_NUMBER || ') ';
END IF;
END IF;
IF l_tab.l_Store_Count > 0 THEN
l_orgWhere :=' AND A.STORE_NUMBER IN ( ' || l_tab.STORE_NUMBER || ') ';
END IF;

insert into temp values( ' INSERT INTO /*+ parallel(TMP_BULK_DUPLICATE_CONFLICTS,4) */ OCE_APP2.TMP_BULK_DUPLICATE_CONFLICTS (EXISTINGOVERRIDEID,
OverrideID
, Eligibility
, OverrideLevel
,PRIORITY
, DESCRIPTION
, OrganizationalLevel
, OverrideReason
, StartDate
,EndDate
, OVERRIDE_STATUS
,CreatedBy
, CreatedDate
,SS
, MinOH
,sku_number
,style_number
,override_comments
,Unallocatable,
program_id,CHAIN_NUMBER) select override_id EXISTINGOVERRIDEID,
or_session_id OverrideID
,eligibility Eligibility
,override_level OverrideLevel
,PRIORITY
, DESCRIPTION
,ORG_DESCRIPTION OrganizationalLevel
,override_reason OverrideReason
,override_start_date StartDate
,override_end_date EndDate
, OVERRIDE_STATUS
,created_by CreatedBy
,created_date CreatedDate
,safety_stock SS
,min_oh MinOH
,sku_number
,style_number
,override_comments
,decode(:Program_Id,1,unalloc,null) Unallocatable,
:Program_Id Program_Id,
CHAIN_NUMBER
from
(
select /*+ parallel(a,4) */ :or_session_id or_session_id,
a.override_id
,A.DEPARTMENT_NUMBER
,A.SUB_DEPARTMENT_NUMBER
,A.CLASS_NUMBER
,A.SUB_CLASS_NUMBER
,A.STYLE_NUMBER
,A.SKU_NUMBER
,A.CHAIN_NUMBER
,A.REGION_NUMBER
,A.DISTRICT_NUMBER
,A.STORE_NUMBER
,CASE
WHEN trim(a.eligibility) = ''Y'' then ''ON''
WHEN trim(A.ELIGIBILITY) = ''N'' then ''OFF''
end eligibility
,a.override_level
,B.PRIORITY
,case
WHEN substr(a.override_level, 0, INSTR(a.override_level, ''-'')-1) =
''DEPT'' THEN A.DEPARTMENT_NUMBER || '' ''
WHEN substr(a.override_level, 0, INSTR(a.override_level, ''-'')-1) = ''SUBDEPT'' THEN A.DEPARTMENT_NUMBER || ''.'' || A.SUB_DEPARTMENT_NUMBER || '' ''
WHEN substr(a.override_level, 0, INSTR(a.override_level, ''-'')-1) = ''CLASS'' THEN A.DEPARTMENT_NUMBER || ''.'' || A.SUB_DEPARTMENT_NUMBER || ''.'' || A.CLASS_NUMBER || '' ''
WHEN substr(a.override_level, 0, INSTR(a.override_level, ''-'')-1) = ''SUBCLASS'' THEN A.DEPARTMENT_NUMBER || ''.'' || A.SUB_DEPARTMENT_NUMBER || ''.'' || A.CLASS_NUMBER || ''.'' || A.SUB_CLASS_NUMBER || '' ''
WHEN substr(a.override_level, 0, INSTR(a.override_level, ''-'')-1) = ''STYLE'' THEN A.STYLE_NUMBER || '' ''
WHEN substr(a.override_level, 0, INSTR(a.override_level, ''-'')-1) = ''SKU'' THEN A.SKU_NUMBER || '' ''
END DESCRIPTION
,CASE
WHEN SUBSTR(a.override_level, INSTR(a.override_level, ''-'')+1 ) = ''CHAIN'' THEN ''CHAIN - '' || A.CHAIN_NUMBER
WHEN SUBSTR(a.override_level, INSTR(a.override_level, ''-'')+1 ) = ''STORE'' THEN ''STORE - '' || A.STORE_NUMBER
END ORG_DESCRIPTION
,a.override_reason
,a.override_start_date
,a.override_end_date
,case
WHEN A.OVERRIDE_STATUS_ID = 1 THEN ''FUTURE''
WHEN A.OVERRIDE_STATUS_ID = 2 THEN ''PENDING''
WHEN A.OVERRIDE_STATUS_ID = 3 THEN ''PROCESSED''
END OVERRIDE_STATUS
,a.created_by
,a.created_date
,a.safety_stock
,a.min_oh
,c.min_oh unalloc
,a.override_comments
FROM oce_app2.OCE_OVERRIDE A, oce_app2.OCE_OVERRIDE_LEVEL B, oce_app2.OCE_OVERRIDE C WHERE
A.OVERRIDE_ID = C.OVERRIDE_REF_ID(+) AND A.OVERRIDE_LEVEL = B.OVERRIDE_LEVEL
and A.CREATED_DATE<(select distinct max(created_date) from OCE_BULK_PENDING_OVERRIDE)
AND A.OVERRIDE_STATUS_ID IN (1, 2, 3)
AND A.PROGRAM_ID =:program_id
AND
(
(A.OVERRIDE_START_DATE <= :OVERRIDE_START_DATE AND A.OVERRIDE_END_DATE BETWEEN :OVERRIDE_START_DATE AND :OVERRIDE_END_DATE)
OR
(A.OVERRIDE_START_DATE BETWEEN :OVERRIDE_START_DATE AND :OVERRIDE_END_DATE)
OR
(A.OVERRIDE_END_DATE BETWEEN :OVERRIDE_START_DATE AND :OVERRIDE_END_DATE)
OR
(A.OVERRIDE_END_DATE = 99999999)
OR
(:OVERRIDE_START_DATE BETWEEN A.OVERRIDE_START_DATE AND A.OVERRIDE_END_DATE)
OR
(:OVERRIDE_END_DATE BETWEEN A.OVERRIDE_START_DATE AND A.OVERRIDE_END_DATE)
)
AND B.PRIORITY =: PRIORITY
' || l_orgWhere || '
) a
where 1 = 1
' || l_prodWhere || '
ORDER BY PRIORITY desc, description ');
insert into temp values(l_query);
commit;

execute immediate ' INSERT INTO /*+ parallel(TMP_BULK_DUPLICATE_CONFLICTS,4) */ OCE_APP2.TMP_BULK_DUPLICATE_CONFLICTS (EXISTINGOVERRIDEID,
OverrideID
, Eligibility
, OverrideLevel
,PRIORITY
, DESCRIPTION
, OrganizationalLevel
, OverrideReason
, StartDate
,EndDate
, OVERRIDE_STATUS
,CreatedBy
, CreatedDate
,SS
, MinOH
,sku_number
,style_number
,override_comments
,Unallocatable,
program_id,CHAIN_NUMBER) select override_id EXISTINGOVERRIDEID,
or_session_id OverrideID
,eligibility Eligibility
,override_level OverrideLevel
,PRIORITY
, DESCRIPTION
,ORG_DESCRIPTION OrganizationalLevel
,override_reason OverrideReason
,override_start_date StartDate
,override_end_date EndDate
, OVERRIDE_STATUS
,created_by CreatedBy
,created_date CreatedDate
,safety_stock SS
,min_oh MinOH
,sku_number
,style_number
,override_comments
,decode(:Program_Id,1,unalloc,null) Unallocatable,
:Program_Id Program_Id,
CHAIN_NUMBER
from
(
select /*+ parallel(a,4) */ :or_session_id or_session_id,
a.override_id
,A.DEPARTMENT_NUMBER
,A.SUB_DEPARTMENT_NUMBER
,A.CLASS_NUMBER
,A.SUB_CLASS_NUMBER
,A.STYLE_NUMBER
,A.SKU_NUMBER
,A.CHAIN_NUMBER
,A.REGION_NUMBER
,A.DISTRICT_NUMBER
,A.STORE_NUMBER
,CASE
WHEN trim(a.eligibility) = ''Y'' then ''ON''
WHEN trim(A.ELIGIBILITY) = ''N'' then ''OFF''
end eligibility
,a.override_level
,B.PRIORITY
,case
WHEN substr(a.override_level, 0, INSTR(a.override_level, ''-'')-1) =
''DEPT'' THEN A.DEPARTMENT_NUMBER || '' ''
WHEN substr(a.override_level, 0, INSTR(a.override_level, ''-'')-1) = ''SUBDEPT'' THEN A.DEPARTMENT_NUMBER || ''.'' || A.SUB_DEPARTMENT_NUMBER || '' ''
WHEN substr(a.override_level, 0, INSTR(a.override_level, ''-'')-1) = ''CLASS'' THEN A.DEPARTMENT_NUMBER || ''.'' || A.SUB_DEPARTMENT_NUMBER || ''.'' || A.CLASS_NUMBER || '' ''
WHEN substr(a.override_level, 0, INSTR(a.override_level, ''-'')-1) = ''SUBCLASS'' THEN A.DEPARTMENT_NUMBER || ''.'' || A.SUB_DEPARTMENT_NUMBER || ''.'' || A.CLASS_NUMBER || ''.'' || A.SUB_CLASS_NUMBER || '' ''
WHEN substr(a.override_level, 0, INSTR(a.override_level, ''-'')-1) = ''STYLE'' THEN A.STYLE_NUMBER || '' ''
WHEN substr(a.override_level, 0, INSTR(a.override_level, ''-'')-1) = ''SKU'' THEN A.SKU_NUMBER || '' ''
END DESCRIPTION
,CASE
WHEN SUBSTR(a.override_level, INSTR(a.override_level, ''-'')+1 ) = ''CHAIN'' THEN ''CHAIN - '' || A.CHAIN_NUMBER
WHEN SUBSTR(a.override_level, INSTR(a.override_level, ''-'')+1 ) = ''STORE'' THEN ''STORE - '' || A.STORE_NUMBER
END ORG_DESCRIPTION
,a.override_reason
,a.override_start_date
,a.override_end_date
,case
WHEN A.OVERRIDE_STATUS_ID = 1 THEN ''FUTURE''
WHEN A.OVERRIDE_STATUS_ID = 2 THEN ''PENDING''
WHEN A.OVERRIDE_STATUS_ID = 3 THEN ''PROCESSED''
END OVERRIDE_STATUS
,a.created_by
,a.created_date
,a.safety_stock
,a.min_oh
,c.min_oh unalloc
,a.override_comments
FROM oce_app2.OCE_OVERRIDE A, oce_app2.OCE_OVERRIDE_LEVEL B, oce_app2.OCE_OVERRIDE C WHERE
A.OVERRIDE_ID = C.OVERRIDE_REF_ID(+) AND A.OVERRIDE_LEVEL = B.OVERRIDE_LEVEL
and A.CREATED_DATE<(select distinct max(created_date) from OCE_BULK_PENDING_OVERRIDE)
AND A.OVERRIDE_STATUS_ID IN (1, 2, 3)
AND A.PROGRAM_ID =:program_id
AND
(
(A.OVERRIDE_START_DATE <= :OVERRIDE_START_DATE AND A.OVERRIDE_END_DATE BETWEEN :OVERRIDE_START_DATE AND :OVERRIDE_END_DATE)
OR
(A.OVERRIDE_START_DATE BETWEEN :OVERRIDE_START_DATE AND :OVERRIDE_END_DATE)
OR
(A.OVERRIDE_END_DATE BETWEEN :OVERRIDE_START_DATE AND :OVERRIDE_END_DATE)
OR
(A.OVERRIDE_END_DATE = 99999999)
OR
(:OVERRIDE_START_DATE BETWEEN A.OVERRIDE_START_DATE AND A.OVERRIDE_END_DATE)
OR
(:OVERRIDE_END_DATE BETWEEN A.OVERRIDE_START_DATE AND A.OVERRIDE_END_DATE)
)
AND B.PRIORITY =: PRIORITY
' || l_orgWhere || '
) a
where 1 = 1
' || l_prodWhere || '
ORDER BY PRIORITY desc, description '
using l_tab.l_Program_Id,l_tab.l_Program_Id, l_tab.OR_SESSION_ID, l_tab.l_Program_Id
,l_tab.l_Start_Date ,l_tab.l_Start_Date,l_tab.l_End_Date ,l_tab.l_Start_Date,l_tab.l_End_Date,l_tab.l_Start_Date,l_tab.l_End_Date
,l_tab.l_Start_Date,l_tab.l_End_Date,l_tab.l_Current_Priority ;


end loop;
commit;
update oce_override set OVERRIDE_STATUS_ID=5 where sku_number||style_number in(
select oo.sku_number||oo.style_number from TMP_BULK_DUPLICATE_CONFLICTS tbdc,oce_override oo
where (TBDC.SKU_NUMBER=OO.SKU_NUMBER
or TBDC.STYLE_NUMBER=OO.STYLE_NUMBER))
and CREATED_DATE<(select distinct max(created_date) from OCE_BULK_PENDING_OVERRIDE);
commit;

delete from TMP_BULK_DUPLICATE_CONFLICTS where OVERRIDEID in (select EXISTINGOVERRIDEID from TMP_BULK_DUPLICATE_CONFLICTS);
commit;

Delete From TMP_BULK_DUPLICATE_CONFLICTS T1
Where T1.Rowid >
Any (Select T2.Rowid
From TMP_BULK_DUPLICATE_CONFLICTS T2
Where T1.Description = T2.Description
And T1.Organizationallevel = T2.Organizationallevel
and t1.PROGRAM_ID=t2.PROGRAM_ID
and t1.chain_number=t2.chain_number);

Commit;

open out_equal_conflicts for select OverrideID
,EXISTINGOVERRIDEID
,case when program_id=1 then 'SFS'
when program_id=3 then 'ISA'
when program_id=4 then 'BOPIS' end programname
,case when CHAIN_NUMBER=1 then 'DSG'
when CHAIN_NUMBER=3 then 'GG'
when CHAIN_NUMBER=7 then 'FS' end Banner
, Eligibility
, OverrideLevel
--,PRIORITY
, DESCRIPTION
, OrganizationalLevel
, OverrideReason
, StartDate
, EndDate
, OVERRIDE_STATUS STATUS
, SS
, MinOH
--,sku_number
--,style_number
,override_comments
,Unallocatable
, CreatedBy
, CreatedDate from TMP_BULK_DUPLICATE_CONFLICTS;

END ;
/