oracle query question

DROP TABLE DSS.TAB1 CASCADE CONSTRAINTS;

CREATE TABLE DSS.TAB1
(
  ID                    NUMBER(38)              NOT NULL,
  REG_ID_FROM           NUMBER(38)              NOT NULL,
  REG_ID_TO             NUMBER(38)              NOT NULL,
  WEIGHT_MAXIMUM        NUMBER(6)               NOT NULL,
  ESTIMATED_FREIGHT     NUMBER(10,2),
  PER_POUND_MULTIPLIER  NUMBER(6,2),
  CARRMETH_ID           NUMBER(38),
  PROFS_ID_CMETHTYPE    NUMBER(38)
)
TABLESPACE MISC
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
LOGGING 
NOCOMPRESS 
NOCACHE
MONITORING;

SET DEFINE OFF;
Insert into DSS.TAB1
   (ID, REG_ID_FROM, REG_ID_TO, WEIGHT_MAXIMUM, ESTIMATED_FREIGHT, 
    PER_POUND_MULTIPLIER, CARRMETH_ID, PROFS_ID_CMETHTYPE)
 Values
   (13219125811, 13218967894, 13218967894, 100, NULL, 
    10, 1534, NULL);
Insert into DSS.TAB1
   (ID, REG_ID_FROM, REG_ID_TO, WEIGHT_MAXIMUM, ESTIMATED_FREIGHT, 
    PER_POUND_MULTIPLIER, CARRMETH_ID, PROFS_ID_CMETHTYPE)
 Values
   (13219112356, 13218967894, 13218967894, 100, 150, 
    NULL, NULL, 14258);
Insert into DSS.TAB1
   (ID, REG_ID_FROM, REG_ID_TO, WEIGHT_MAXIMUM, ESTIMATED_FREIGHT, 
    PER_POUND_MULTIPLIER, CARRMETH_ID, PROFS_ID_CMETHTYPE)
 Values
   (13219042831, 13219042603, 13218967894, 100, 900, 
    NULL, 1592, NULL);
Insert into DSS.TAB1
   (ID, REG_ID_FROM, REG_ID_TO, WEIGHT_MAXIMUM, ESTIMATED_FREIGHT, 
    PER_POUND_MULTIPLIER, CARRMETH_ID, PROFS_ID_CMETHTYPE)
 Values
   (13219042759, 13219042603, 13218967894, 100, 276, 
    NULL, NULL, 14264);
Insert into DSS.TAB1
   (ID, REG_ID_FROM, REG_ID_TO, WEIGHT_MAXIMUM, ESTIMATED_FREIGHT, 
    PER_POUND_MULTIPLIER, CARRMETH_ID, PROFS_ID_CMETHTYPE)
 Values
   (13218990529, 13218979268, 13218979264, 100, 276, 
    NULL, NULL, 14260);
COMMIT;

Select all Open in new window

	v_region_count number;
	v_region_count1 number;
Begin
	select count(*) into v_region_count
	  from dss.freight_estimates
	 where weight_maximum = :freight_estimates.weight_maximum
	   AND reg_id_from = :freight_estimates.reg_id_from 
	   and reg_id_to = :freight_estimates.reg_id_to
	   and carrmeth_id = :freight_estimates.carrmeth_id
	   and profs_id_cmethtype is null;
	   
	select count(*) into v_region_count1
	  from dss.freight_estimates
	 where weight_maximum = :freight_estimates.weight_maximum
	   AND reg_id_from = :freight_estimates.reg_id_from 
	   and reg_id_to = :freight_estimates.reg_id_to
	   and carrmeth_id is null
	   and profs_id_cmethtype = :freight_estimates.profs_id_cmethtype;

End;

Select all Open in new window

	    
If v_region_count > 0 then
  	msg_alert('The combination of Maximum Weight,Region From, Region To and Carrier Method already exists','E',true);
ElsIf v_region_count1 > 0 then
  	msg_alert('The combination of Maximum Weight,Region From, Region To and Carrier Type already exists','E',true);
End if;

Select all Open in new window