Hi Tom,

I am a reader of your great topics. I found great help from this site.

It's first time i am writing because it's production issue.

We ate geting unique constraints issue. The simple query are as below that extract the data with diffrent condition and insert into table.

When is execute the same query with the voilated data then no record return.

Insertion Query
---------------
--1
INSERT INTO vin_core_supcomm_map_smry
(supcom_sup_code,
supcom_part_number,
supcom_comm_code,
supcom_comments,
supcom_mod_user,
supcom_mod_date,
supcom_map_id,
supcom_reg_code,
supcom_prdline_code,
sub_commodity_code)
(SELECT vcsm.supcom_sup_code,
vcsm.supcom_part_number,
vcsm.supcom_comm_code,
vcsm.supcom_comments,
vcsm.supcom_mod_user,
SYSTIMESTAMP,
vcsm.supcom_map_id,
vcpm.productline_region,
vcpm.productline_code,
vcsm.sub_commodity_code
FROM vin_core_supcomm_map vcsm
CROSS JOIN vin_core_prdline_mst vcpm
WHERE (supcom_sup_code = v_supplier_code or v_supplier_code = '*')
AND supcom_part_number <> '*'
AND NOT EXISTS
(SELECT 1
FROM vin_core_supcomm_map_smry vcsms
WHERE vcsm.supcom_sup_code = vcsms.supcom_sup_code
AND vcsm.supcom_part_number = vcsms.supcom_part_number
AND vcsms.supcom_reg_code = vcpm.productline_region
AND vcsms.supcom_prdline_code = vcpm.productline_code));

--2
-- for specific region and specific productline , insert that record directly.
INSERT INTO vin_core_supcomm_map_smry
(supcom_sup_code,
supcom_part_number,
supcom_comm_code,
supcom_comments,
supcom_mod_user,
supcom_mod_date,
supcom_map_id,
supcom_reg_code,
supcom_prdline_code,
sub_commodity_code)
(SELECT vcsm.supcom_sup_code,
vcsm.supcom_part_number,
vcsm.supcom_comm_code,
vcsm.supcom_comments,
vcsm.supcom_mod_user,
SYSTIMESTAMP,
vcsm.supcom_map_id,
vcsm.supcom_reg_code,
vcsm.supcom_prdline_code,
vcsm.sub_commodity_code
FROM vin_core_supcomm_map vcsm
WHERE (supcom_sup_code = v_supplier_code or v_supplier_code = '*')
AND vcsm.supcom_part_number = '*'
AND vcsm.supcom_reg_code <> '*'
AND vcsm.supcom_prdline_code <> '*'
AND NOT EXISTS
(SELECT 1
FROM vin_core_supcomm_map_smry vcsms
WHERE vcsm.supcom_sup_code = vcsms.supcom_sup_code
AND vcsm.supcom_part_number = vcsms.supcom_part_number
AND vcsm.supcom_reg_code = vcsms.supcom_reg_code
AND vcsm.supcom_prdline_code = vcsms.supcom_prdline_code));

--3
-- for specific region and all productlines , insert records for all the possible productline
INSERT INTO vin_core_supcomm_map_smry
(supcom_sup_code,
supcom_part_number,
supcom_comm_code,
supcom_comments,
supcom_mod_user,
supcom_mod_date,
supcom_map_id,
supcom_reg_code,
supcom_prdline_code,
sub_commodity_code)
(SELECT vcsm.supcom_sup_code,
vcsm.supcom_part_number,
vcsm.supcom_comm_code,
vcsm.supcom_comments,
vcsm.supcom_mod_user,
SYSTIMESTAMP,
vcsm.supcom_map_id,
vcpm.productline_region,
vcpm.productline_code,
vcsm.sub_commodity_code
FROM vin_core_supcomm_map vcsm
CROSS JOIN vin_core_prdline_mst vcpm
WHERE (supcom_sup_code = v_supplier_code or v_supplier_code = '*')
AND vcsm.supcom_part_number = '*'
AND vcsm.supcom_reg_code <> '*'
AND vcsm.supcom_prdline_code = '*'
AND vcpm.productline_region = vcsm.supcom_reg_code
AND NOT EXISTS
(SELECT 1
FROM vin_core_supcomm_map_smry vcsms
WHERE vcsm.supcom_sup_code = vcsms.supcom_sup_code
AND vcsm.supcom_part_number = vcsms.supcom_part_number
AND vcsms.supcom_reg_code = vcpm.productline_region
AND vcsms.supcom_prdline_code = vcpm.productline_code));

--4
-- for all region and all productline , insert the records for all the region
INSERT INTO vin_core_supcomm_map_smry
(supcom_sup_code,
supcom_part_number,
supcom_comm_code,
supcom_comments,
supcom_mod_user,
supcom_mod_date,
supcom_map_id,
supcom_reg_code,
supcom_prdline_code,
sub_commodity_code)
(SELECT vcsm.supcom_sup_code,
vcsm.supcom_part_number,
vcsm.supcom_comm_code,
vcsm.supcom_comments,
vcsm.supcom_mod_user,
SYSTIMESTAMP,
vcsm.supcom_map_id,
vcpm.productline_region,
vcpm.productline_code,
vcsm.sub_commodity_code
FROM vin_core_supcomm_map vcsm
CROSS JOIN vin_core_prdline_mst vcpm
WHERE (supcom_sup_code = v_supplier_code OR v_supplier_code = '*')
AND vcsm.supcom_part_number = '*'
AND vcsm.supcom_reg_code = '*'
AND vcsm.supcom_prdline_code = '*'
AND NOT EXISTS
(SELECT 1
FROM vin_core_supcomm_map_smry vcsms
WHERE vcsm.supcom_sup_code = vcsms.supcom_sup_code
AND vcsm.supcom_part_number = vcsms.supcom_part_number
AND vcsms.supcom_reg_code = vcpm.productline_region
AND vcsms.supcom_prdline_code = vcpm.productline_code));

The Primary key and unique index
--------------------------------
CREATE UNIQUE INDEX VINLOAD.VIN_CORE_SUPCOMM_MAP_SMRY_PK1 ON VINLOAD.VIN_CORE_SUPCOMM_MAP_SMRY
(SUPCOM_SUP_CODE, SUPCOM_PART_NUMBER, SUPCOM_PRDLINE_CODE)

CREATE UNIQUE INDEX VINLOAD.VIN_CORE_SUPCOMM_MAP_SMRY_UQ1 ON VINLOAD.VIN_CORE_SUPCOMM_MAP_SMRY
(SUPCOM_SUP_CODE, SUPCOM_PART_NUMBER, SUPCOM_PRDLINE_CODE, SUPCOM_REG_CODE)

CREATE UNIQUE INDEX VINLOAD.VIN_CORE_SUPCOMM_MAP_SMRY_UQ2 ON VINLOAD.VIN_CORE_SUPCOMM_MAP_SMRY
(SUPCOM_SUP_CODE, SUPCOM_PART_NUMBER, SUPCOM_PRDLINE_CODE, SUPCOM_REG_CODE, SUPCOM_COMM_CODE)


When this SP is executed it logs handred of unique constraint error but if execute the query in separate session then no record found.

If i use "LOG ERRORS REJECT LIMIT UNLIMITED" then it may possibility we lose some record to insert ?

Your kind help can correct if something is missing .

Thanks,
Vipul